Data Model for Requisition-based Reports

This page holds the map of data elements on a requisition available through the "requisitions" API to the table schemas in Postgres and the 12 critical eLMIS reports.

Postgres will contain 4 tables of requisition data: requisitions, requisition line items, requisition status history, and requisition adjustment line items. Indicator values will predominantly come from data elements in requisition_line_items, although groupings and filters (around status) will be achieved by joining to other tables.

Postgres Tables

requisitions

Field Name in API	Column Name	Comment
id	requisition_id
createdDate	created_date
modifiedDate	modified_date
facility.id	facility_id
facility.code	facility_code
facility.name	facility_name
facility.active	facility_active_status
facility.geographicZone.id WHERE facility.geographicZone.level = district	district_id	The hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.code WHERE facility.geographicZone.level = district	district_code	The hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.name WHERE facility.geographicZone.level = district	district_name	The hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.id WHERE facility.geographicZone.level = region	region_id	The hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.code WHERE facility.geographicZone.level = region	region_code	The hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.name WHERE facility.geographicZone.level = region	region_name	The hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.id WHERE facility.geographicZone.level = country	country_id	The hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.code WHERE facility.geographicZone.level = country	country_code	The hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.name WHERE facility.geographicZone.level = country	country_name	The hierarchy will need to be adjusted manually based on the specific implementation
facility.type.id	facility_type_id
facility.type.code	facility_type_code
facility.type.name	facility_type_name
facility.operator.id	facility_operator_id
facility.operator.code	facility_operator_code
facility.operator.name	facility_operator_name
program.id	program_id
program.code	program_code
program.name	program_name
program.active	program_active_status
processingPeriod.id	processing_period_id
processingPeriod.name	processing_period_name
processingPeriod.startDate	processing_period_start_date
processingPeriod.endDate	processing_period_end_date
processingPeriod.processingSchedule.id	processing_schedule_id
processingPeriod.processingSchedule.code	processing_schedule_code
processingPeriod.processingSchedule.name	processing_schedule_name
emergency	emergency_status
supplyingFacility	supplying_facility
supervisoryNode	supervisory_node

requisition_line_items

Except for the requisition_id, all fields are in the requisitionLineItems nested list. You can find information on how these fields are calculated in OpenLMIS here: Requisition Template#ColumnDependencies. Note that

Field Name in API	Column Name	eLMIS Report(s)	Superset Dashboard(s)	Calculation(s) or Column(s) in eLMIS Report(s)	Comment
requisitionLineItems.id	requisition_line_item_id
id	requisition_id
orderable.id	orderable_id
orderable.productCode	product_code
orderable.fullProductName	full_product_name				Orderable name
orderable.commodityTypeIdentifier	commodity_type_id
orderable.identifiers.tradeItem	trade_item_id				Used to join to Reference Data to find trade item name, etc.
beginningBalance	beginning_balance	Stock Status Summary Report Logistics Summary Report	Consumption dashboard	Counts as a stockout if = 0 Opening Balance
totalConsumedQuantity	total_consumed_quantity	Consumption Report Logistics Summary Report	Consumption dashboard	Reported Consumption Consumption
averageConsumption	average_consumption	Stock Status Summary Report Stock Imbalance Report	Not included but could be easily added (it is in the data model)	Stock level / stock status calculation in popup Stock Status & AMC
adjustedConsumption	adjusted_consumption	Consumption Report	Consumption dashboard	Adjusted Consumption
totalLossesAndAdjustments	total_losses_and_adjustments		Adjustments dashboard Consumption dashboard		We need to use the quantities on the adjustment reason lines for the line-level detail in the Adjustment Summary Report. Including this in case necessary for totals in the future.
stockOnHand	stock_on_hand	Stock Status Summary Report Stock Imbalance Report Logistics Summary Report	Consumption dashboard	Count as stockout if = 0 Closing Balance Closing Balance	Could be easily added to Stock Status dashboard in Superset, although it was omitted due to feedback that Periods of Stock was more pertinent as stock on hand is relative to the expected stock level for that product, facility, and program
totalStockoutDays	total_stockout_days	Stock Status Summary Report	Stockouts dashboard	Count as stockout if > 0
maxPeriodsOfStock	max_periods_of_stock	Stock Status Summary Report Stock Imbalance Report	Stock Status dashboard	Count as stockout if = 0 MOS
calculatedOrderQuantity	calculated_order_quantity				System determined amount to reorder based on consumption and current stock level. Not necessary for eLMIS reports, but could be useful. Omitted from Superset reports in favor of the actual order quantity (requested_quantity)
requestedQuantity	requested_quantity	Logistics Summary Report	Orders dashboard Consumption dashboard	Reorder Amount
approvedQuantity	approved_quantity	Stock Imbalance Report Order & Item Fill Rate Report		Order Quantity Quantity Ordered	Excluded from Superset, but could be easily added as it is in the data model. For Order Item & Fill Rate report, we used requested_quantity and total_received_quantity
packsToShip	packs_to_ship				Used in Order Summary Report but this report is not coming from the reporting stack. Including for potential future need.
pricePerPack	price_per_pack				Used in Order Summary Report but this report is not coming from the reporting stack. Including for potential future need.
totalCost	total_cost	Total Cost of Orders	Orders dashboard	Approved Total Cost
totalReceivedQuantity	total_received_quantity	Order & Item Fill Rate Report	Orders dashboard	Quantity Received

requisition_status_history

Field Name in API	Column Name	Comment
id	requisition_id	Key used to join back to requisitions and requisition_line_items tables
statusHistory.createdDate	created_date
statusHistory.status	status
statusHistory,authorId	author_id	Person who changed the status

requisition_adjustment_lines

Field Name in API	Column Name	Comment
requisitionLineItems.id	requisition_line_item_id	Key used to join back to requisition_line_items table
requisitionLineItems.stockAdjustments.reasonId	reason_id	Key used to join to Reference Data tables to fetch the reason name, description, type, and category for use in the Adjustment Summary Report
requisitionLineItems.stockAdjustments.quantity	quantity