Definition
Overview
Section titled “Overview”mart_ordering is an ordering-item warehouse mart used for operational ordering analytics in Community Pharmacy. It combines order item details with pharmacy, wholesaler, product-pack, and order status context, and is built incrementally with deduplication controls. Duplicate records are filtered from the mart and routed to the quarantine model.
Key Data Fields
Section titled “Key Data Fields”| Field | Description |
|---|---|
| order_history_item_id | Unique identifier for the order history item. |
| order_history_id | Unique identifier for the order history event. |
| pack_code_id | Local identifier of the ordered ProScript Connect pack. |
| order_quantity | Quantity requested on the order item. |
| received_quantity | Quantity received against the order item. |
| order_status | Verbose order-level status (for example Sending, Failed, Sent, Completed). |
| order_item_status | Verbose order-item status (for example None, Accepted, Rejected). |
| order_reference | Order reference generated from order history and wholesaler identifiers. |
Source
Section titled “Source”The lineage for this model is: psc ordering source tables -> stg_order_history_item and stg_order_history -> ordering -> mart_ordering.
The upstream ordering model also joins stg_wholesaler, active_pharmacy, dim_product_pack, stg_order_history_status, and stg_order_history_item_status to enrich item-level ordering records.
mart_ordering is materialized as incremental with merge strategy and unique key (organisation, order_history_item_id). Incremental updates are driven by the mart incremental filter and row hash comparison (row_id), so changed or new records are inserted/updated while unchanged rows are skipped.