Requisition-Based Report Queries

Owned by Clay Crosby (Unlicensed)
Last updated: Nov 12, 2018Version comment

NOTE: the contents of this page are out of date and will be updated as the queries are revised based on Muhammad's feedback

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 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 DISTINCT ON (li.requisition_line_item_id) 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.adjusted_consumption, 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,
saf.test_facility_name, saf.test_username, saf.test_program_id,
SUM(li.stock_on_hand) as closing_balance,
SUM(li.average_consumption) as AMC,
SUM(li.total_consumed_quantity) as Consumption,
SUM(li.adjusted_consumption) as adjusted_consumption,
SUM(li.approved_quantity) as order_quantity,
CASE WHEN (SUM(li.stock_on_hand) = 0 OR SUM(li.total_stockout_days) > 0 OR SUM(li.beginning_balance) = 0 OR SUM(li.max_periods_of_stock) = 0) THEN 1 ELSE 0 END as combined_stockout,
CASE
WHEN SUM(li.max_periods_of_stock) > 6 THEN 'Overstocked'
WHEN SUM(li.max_periods_of_stock) < 3 AND (SUM(li.stock_on_hand) = 0 OR SUM(li.total_stockout_days) > 0 OR SUM(li.beginning_balance) = 0 OR SUM(li.max_periods_of_stock) = 0) THEN 'Stocked Out'
WHEN SUM(li.max_periods_of_stock) < 3 AND SUM(li.max_periods_of_stock) > 0 AND NOT(SUM(li.stock_on_hand) = 0 OR SUM(li.total_stockout_days) > 0 OR SUM(li.beginning_balance) = 0 OR SUM(li.max_periods_of_stock) = 0) THEN 'Understocked'
WHEN SUM(li.max_periods_of_stock) = 0 AND NOT(SUM(li.stock_on_hand) = 0 OR SUM(li.total_stockout_days) > 0 OR SUM(li.beginning_balance) = 0 OR SUM(li.max_periods_of_stock) = 0) THEN 'Unknown'
ELSE 'Adequately stocked' END as stock_status
FROM requisitions r JOIN requisition_line_item li ON r.id::VARCHAR = li_req_id
JOIN requisitions_status_history sh ON r.id::VARCHAR = status_req_id
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'
GROUP BY r.id, r.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_line_item_id, 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.adjusted_consumption, 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,
status_req_id, req_status, sh.author_id, status_date,
saf.test_facility_name, saf.test_username, saf.test_program_id
ORDER BY li.requisition_line_item_id, r.modified_date DESC NULLS LAST
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