Data Mapping 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)	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	Counts as a stockout if = 0 Opening Balance
totalConsumedQuantity	total_consumed_quantity	Consumption Report Logistics Summary Report	Reported Consumption Consumption
averageConsumption	average_consumption	Stock Status Summary Report Stock Imbalance Report	Stock level / stock status calculation in popup Stock Status & AMC
adjustedConsumption	adjusted_consumption	Consumption Report	Adjusted Consumption
totalLossesAndAdjustments	total_losses_and_adjustments			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	Count as stockout if = 0 Closing Balance Closing Balance
totalStockoutDays	total_stockout_days	Stock Status Summary Report	Count as stockout if > 0
maxPeriodsOfStock	max_periods_of_stock	Stock Status Summary Report Stock Imbalance Report	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.
requestedQuantity	requested_quantity	Logistics Summary Report	Reorder Amount
approvedQuantity	approved_quantity	Stock Imbalance Report Order & Item Fill Rate Report	Order Quantity Quantity Ordered
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	Approved Total Cost
totalReceivedQuantity	total_received_quantity	Order & Item Fill Rate Report	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