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 APIColumn NameComment
idrequisition_id
createdDatecreated_date
modifiedDatemodified_date
facility.idfacility_id
facility.codefacility_code
facility.namefacility_name
facility.activefacility_active_status
facility.geographicZone.id WHERE facility.geographicZone.level = districtdistrict_idThe hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.code WHERE facility.geographicZone.level = districtdistrict_codeThe hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.name WHERE facility.geographicZone.level = districtdistrict_nameThe hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.id WHERE facility.geographicZone.level = regionregion_idThe hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.code WHERE facility.geographicZone.level = regionregion_codeThe hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.name WHERE facility.geographicZone.level = regionregion_nameThe hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.id WHERE facility.geographicZone.level = countrycountry_idThe hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.code WHERE facility.geographicZone.level = countrycountry_codeThe hierarchy will need to be adjusted manually based on the specific implementation
facility.geographicZone.name WHERE facility.geographicZone.level = countrycountry_nameThe hierarchy will need to be adjusted manually based on the specific implementation
facility.type.idfacility_type_id
facility.type.codefacility_type_code
facility.type.namefacility_type_name
facility.operator.idfacility_operator_id
facility.operator.codefacility_operator_code
facility.operator.namefacility_operator_name
program.idprogram_id
program.codeprogram_code
program.nameprogram_name
program.activeprogram_active_status
processingPeriod.idprocessing_period_id
processingPeriod.nameprocessing_period_name
processingPeriod.startDateprocessing_period_start_date
processingPeriod.endDateprocessing_period_end_date
processingPeriod.processingSchedule.idprocessing_schedule_id
processingPeriod.processingSchedule.codeprocessing_schedule_code
processingPeriod.processingSchedule.nameprocessing_schedule_name
emergencyemergency_status
supplyingFacilitysupplying_facility
supervisoryNodesupervisory_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 APIColumn NameeLMIS Report(s)Superset Dashboard(s)Calculation(s) or Column(s) in eLMIS Report(s)Comment
requisitionLineItems.idrequisition_line_item_id



id

requisition_id





orderable.idorderable_id



orderable.productCodeproduct_code



orderable.fullProductNamefull_product_name


Orderable name
orderable.commodityTypeIdentifiercommodity_type_id



orderable.identifiers.tradeItemtrade_item_id


Used to join to Reference Data to find trade item name, etc.
beginningBalancebeginning_balanceStock Status Summary Report

Logistics Summary Report
Consumption dashboard

Counts as a stockout if = 0


Opening Balance


totalConsumedQuantitytotal_consumed_quantity

Consumption Report

Logistics Summary Report

Consumption dashboard

Reported Consumption

Consumption


averageConsumptionaverage_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


adjustedConsumptionadjusted_consumptionConsumption Report

Consumption dashboard

Adjusted Consumption
totalLossesAndAdjustmentstotal_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.
stockOnHandstock_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
totalStockoutDaystotal_stockout_daysStock Status Summary ReportStockouts dashboardCount as stockout if > 0
maxPeriodsOfStockmax_periods_of_stock

Stock Status Summary Report

Stock Imbalance Report

Stock Status dashboard

Count as stockout if = 0


MOS


calculatedOrderQuantitycalculated_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)

requestedQuantityrequested_quantityLogistics Summary Report

Orders dashboard

Consumption dashboard

Reorder Amount
approvedQuantityapproved_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
packsToShippacks_to_ship


Used in Order Summary Report but this report is not coming from the reporting stack. Including for potential future need.
pricePerPackprice_per_pack


Used in Order Summary Report but this report is not coming from the reporting stack. Including for potential future need.
totalCosttotal_costTotal Cost of OrdersOrders dashboardApproved Total Cost
totalReceivedQuantitytotal_received_quantityOrder & Item Fill Rate ReportOrders dashboardQuantity Received

requisition_status_history

Field Name in APIColumn NameComment
idrequisition_idKey used to join back to requisitions and requisition_line_items tables
statusHistory.createdDatecreated_date
statusHistory.statusstatus
statusHistory,authorIdauthor_idPerson who changed the status

requisition_adjustment_lines

Field Name in APIColumn NameComment
requisitionLineItems.idrequisition_line_item_idKey used to join back to requisition_line_items table
requisitionLineItems.stockAdjustments.reasonIdreason_idKey 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.quantityquantity

OpenLMIS: the global initiative for powerful LMIS software