NOTE: the contents of this page are out of date and will be updated as the queries are revised based on Muhammad's feedback
...
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
...