Requisition-based Indicator Queries

Owned by Clay Crosby (Unlicensed)
Last updated: May 07, 2019
3 min read

We wrote 3 queries for the requisition-based reports. They join data across the 4 tables mentioned on the Data Mapping page. The queries and charts are documented below and can also be found in GitHub here.

Queries

Stock and Consumption

We used the following query to generate a materialized view because it was too heavy of a load for Superset to run on the fly. This view will refresh nightly as requisition data is refreshed.

CREATE MATERIALIZED VIEW requsition_stock_and_consumption AS

SELECT li.requisition_line_item_id, r.id,
r.created_date as req_created_date, r.modified_date, r.emergency_status, r.supplying_facility,
r.supervisory_node, r.facility_id, r.facility_code, r.facility_name, r.facilty_active_status,
r.district_id, r.district_code, r.district_name, r.region_id, r.region_code, r.region_name,
r.country_id, r.country_code, r.country_name, r.facility_type_id, r.facility_type_code,
r.facility_type_name, r.facility_operator_id, r.facility_operator_code, r.facility_operator_name,
r.program_id, r.program_code, r.program_name, r.program_active_status, r.processing_period_id,
r.processing_period_name, r.processing_period_startdate, r.processing_period_enddate,
r.processing_schedule_id, r.processing_schedule_code, r.processing_schedule_name,
li.requisition_id as li_req_id, li.orderable_id, li.product_code, li.full_product_name,
li.trade_item_id, li.beginning_balance, li.total_consumed_quantity, li.average_consumption,
li.total_losses_and_adjustments, li.stock_on_hand, li.total_stockout_days, li.max_periods_of_stock,
li.calculated_order_quantity, li.requested_quantity, li.approved_quantity, li.packs_to_ship,
li.price_per_pack, li.total_cost, li.total_received_quantity, sh.requisition_id as status_req_id,
sh.status as req_status, sh.author_id, sh.created_date as status_date, fa.facility, fa.program, fa.username,
li.closing_balance, li.AMC, li.Consumption, li.adjusted_consumption,
li.order_quantity, f.status as facility_status, rd.due_days, rd.late_days,
li.combined_stockout, li.stock_status
FROM requisitions r
LEFT JOIN requisitions_status_history sh ON r.id::VARCHAR = sh.requisition_id
LEFT JOIN reporting_dates rd ON r.country_name = rd.country
LEFT JOIN facilities f ON r.facility_id::VARCHAR = f.id::VARCHAR
LEFT JOIN facility_access fa ON fa.facility = f.id::VARCHAR AND fa.program = r.program_id
LEFT JOIN (SELECT DISTINCT(requisition_line_item_id), requisition_id,
orderable_id, product_code, full_product_name,
trade_item_id, beginning_balance, total_consumed_quantity, average_consumption,
total_losses_and_adjustments, stock_on_hand, total_stockout_days, max_periods_of_stock,
calculated_order_quantity, requested_quantity, approved_quantity, packs_to_ship,
price_per_pack, total_cost, total_received_quantity,
SUM(stock_on_hand) as closing_balance,
SUM(average_consumption) as AMC,
SUM(total_consumed_quantity) as Consumption,
SUM(adjusted_consumption) as adjusted_consumption,
SUM(approved_quantity) as order_quantity,
CASE
WHEN (SUM(stock_on_hand) = 0 OR SUM(total_stockout_days) > 0 OR SUM(beginning_balance) = 0 OR SUM(max_periods_of_stock) = 0)
THEN 1 ELSE 0 END as combined_stockout,
CASE
WHEN SUM(max_periods_of_stock) > 6 THEN 'Overstocked'
WHEN SUM(max_periods_of_stock) < 3 AND (SUM(stock_on_hand) = 0 OR SUM(total_stockout_days) > 0 OR SUM(beginning_balance) = 0 OR SUM(max_periods_of_stock) = 0) THEN 'Stocked Out'
WHEN SUM(max_periods_of_stock) < 3 AND SUM(max_periods_of_stock) > 0 AND NOT(SUM(stock_on_hand) = 0 OR SUM(total_stockout_days) > 0 OR SUM(beginning_balance) = 0 OR SUM(max_periods_of_stock) = 0) THEN 'Understocked'
WHEN SUM(max_periods_of_stock) = 0 AND NOT(SUM(stock_on_hand) = 0 OR SUM(total_stockout_days) > 0 OR SUM(beginning_balance) = 0 OR SUM(max_periods_of_stock) = 0) THEN 'Unknown'
ELSE 'Adequately stocked' END as stock_status
FROM requisition_line_item
GROUP BY requisition_line_item_id, requisition_id, orderable_id, product_code, full_product_name,
trade_item_id, beginning_balance, total_consumed_quantity, average_consumption,
total_losses_and_adjustments, stock_on_hand, total_stockout_days, max_periods_of_stock,
calculated_order_quantity, requested_quantity, approved_quantity, packs_to_ship,
price_per_pack, total_cost, total_received_quantity) li ON r.id::VARCHAR = li.requisition_id

WITH DATA;

Reporting Rate and Timeliness


SELECT DISTINCT ON(r.id) r.id, r.created_date, r.modified_date, r.emergency_status, r.facility_name, r.district_name, r.region_name, r.country_name, r.facility_type_name, r.facility_operator_name, r.program_name, r.program_id, r.program_active_status, r.processing_period_name, r.processing_period_startdate, r.processing_period_enddate, r.processing_schedule_name,
sh.requisition_id, sh.status as statushistory_status, sh.created_date,
rd.due_days, rd.late_days, rd.country,
f.id, f.status as facility_status,
sp.programid, sp.startdate,
rgm.requisitiongroupid,
rgps.processingscheduleid,
saf.test_facility_name, saf.test_username, saf.test_program_id,
CASE
WHEN sh.created_date <= (r.processing_period_enddate::DATE + rd.due_days::INT) AND sh.status = 'AUTHORIZED' THEN 'On time'
WHEN sh.created_date > (r.processing_period_enddate::DATE + rd.due_days::INT + rd.late_days::INT) AND sh.status = 'AUTHORIZED' THEN 'Unscheduled'
WHEN sh.created_date < (r.processing_period_enddate::DATE + rd.due_days::INT + rd.late_days::INT) AND sh.created_date >= (r.processing_period_enddate::DATE + rd.due_days::INT) AND sh.status = 'AUTHORIZED' THEN 'Late'
ELSE 'Did not report' END as reporting_timeliness,
COUNT(DISTINCT r.facility_id) as expected_facilities
FROM requisitions r
JOIN requisitions_status_history sh ON r.id::VARCHAR = sh.requisition_id
JOIN reporting_dates rd ON r.country_name = rd.country
JOIN facilities f ON r.facility_id::VARCHAR = f.id::VARCHAR
JOIN supported_programs sp ON sp.facilityid = f.id
JOIN requisition_group_members rgm ON rgm.facilityid = f.id
JOIN requisition_group_program_schedules rgps ON rgps.requisitionGroupId = rgm.requisitionGroupId AND rgps.programId = sp.programId
WHERE sh.status NOT IN ('RELEASED', 'APPROVED') AND saf.test_username = '{{ current_username() }}' AND saf.test_username IS NOT 'admin' AND saf.test_username IS NOT 'administrator'
GROUP BY r.id, r.created_date, r.modified_date, r.emergency_status, r.facility_name, r.district_name, r.region_name, r.country_name, r.facility_type_name, r.facility_operator_name, r.program_name, r.program_id, r.program_active_status, r.processing_period_name, r.processing_period_startdate, r.processing_period_enddate, r.processing_schedule_name,
sh.requisition_id, statushistory_status, sh.created_date,
rd.due_days, rd.late_days, rd.country,
f.id, facility_status,
sp.programid, sp.startdate,
rgm.requisitiongroupid,
rgps.processingscheduleid,
saf.test_facility_name, saf.test_username, saf.test_program_id
ORDER BY r.id, sh.created_date DESC NULLS LAST
;

Adjustments

SELECT DISTINCT ON (li.requisition_line_item_id) li.requisition_line_item_id, r.id AS requisition_id, r.created_date, r.modified_date, r.emergency_status, f.name AS supplying_facility_name, r.supervisory_node, r.facility_name, r.facility_type_name, r.facility_operator_name, r.facilty_active_status, r.district_name, r.region_name, r.country_name, r.program_name, r.program_active_status, r.processing_period_name, li.orderable_id, li.product_code, li.full_product_name, li.trade_item_id, li.total_losses_and_adjustments, sh.status, sh.author_id, sh.created_date AS status_history_created_date,
al.id AS adjustment_lines_id, sar.name AS stock_adjustment_reason, al.quantity,
saf.test_facility_name, saf.test_username, saf.test_program_id
FROM requisitions r JOIN requisition_line_item li ON r.id::VARCHAR = li.requisition_id
LEFT JOIN requisitions_status_history sh ON r.id::VARCHAR = sh.requisition_id
LEFT JOIN requisitions_adjustment_lines al ON li.requisition_line_item_id::VARCHAR = al.requisition_line_item_id
LEFT JOIN facilities f ON r.supplying_facility = f.id::VARCHAR
LEFT JOIN stock_adjustment_reasons sar ON sar.id = al.reasonid
JOIN sample_allowed_facilities saf ON saf.test_facility_name = r.facility_name AND saf.test_program_id = r.program_id
WHERE saf.test_username = '{{ current_username() }}' AND saf.test_username IS NOT 'admin' AND saf.test_username IS NOT 'administrator' AND sh.status IS NOT NULL AND sh.status NOT IN ('SKIPPED', 'INITIATED')
ORDER BY li.requisition_line_item_id, r.modified_date DESC NULLS LAST
;

Charts

The charts can be found at this URL: https://superset.ona.io/superset/dashboard/59/ . Using the following credentials:

  • Username: OpenLMIS
  • Password: demo


OpenLMIS: the global initiative for powerful LMIS software