Results for “msd_shipment_data”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MSD_SHIPMENT_DATA is a fact table in the MSD (Demand Planning) product module of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It stores shipment information that serves as the historical demand signal consumed by Oracle Demand Planning and related supply chain planning engines. Records in this table represent shipped quantities and monetary amounts tied to an item, customer, and shipping organization at a point in time, making it the primary source of "actual demand" used to seed statistical forecasts and to calculate forecast accuracy against shipments.

From a dimensional modeling perspective, the mined foreign-key structure classifies this object as satellite-leaning in Data Vault terms. This is a modeling suggestion rather than a delivered constraint: the table behaves as a descriptive satellite surrounding the level-value dimension members, capturing measurable facts (quantity, amount, dates) that change over time. The single documented foreign key, MSD_SHIPMENT_DATA.SR_ITEM_PK referencing MSD_LEVEL_VALUES, anchors the fact rows to the item dimension level.

Key Information Stored

The table contains 41 columns in the documented 12.2.2 physical schema, owned by the MSD schema. The most significant columns fall into three groups.

• Business keys and dimensions: INSTANCE, INV_ORG, ITEM, CUSTOMER, SALES_CHANNEL, SALES_REP, and SHIP_TO_LOC identify the party, product, organization, and channel involved in the shipment. USER_DEFINED1 and USER_DEFINED2 carry configurable planning attributes.
• Surrogate keys: SR_INV_ORG_PK, SR_ITEM_PK, SR_CUSTOMER_PK, SR_SALES_CHANNEL_PK, SR_SALES_REP_PK, SR_SHIP_TO_LOC_PK, SR_USER_DEFINED1_PK, SR_USER_DEFINED2_PK, SR_ORIGINAL_ITEM_PK, SR_PARENT_ITEM_PK, and SR_DEMAND_CLASS_PK resolve to dimension members, principally in MSD_LEVEL_VALUES. The only documented foreign key is SR_ITEM_PK, making it the primary documented business-key join path; the remaining SR_* columns are dimension references resolved by the planning engine.
• Measures and dates: QTY_SHIPPED and AMOUNT are the quantitative and value facts. BOOKED_DATE, REQUESTED_DATE, PROMISED_DATE, SHIPPED_DATE, SCHEDULED_SHIP_DATE, and SCHEDULED_ARRIVAL_DATE provide the temporal axis used for time-phased demand buckets. ACTION_CODE, LAST_REFRESH_NUM, and CREATED_BY_REFRESH_NUM support incremental refresh processing, while ORDER_SOURCE_ID identifies the originating order stream.

Standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and concurrent program identifiers (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) are present for traceability.

Common Use Cases and Queries

Typical uses include shipment history reporting, forecast-versus-actual variance analysis, and extraction of demand history into planning collections. A simple shipment history query joins the fact table to the item dimension:

• SELECT s.ITEM, s.CUSTOMER, s.SHIPPED_DATE, s.QTY_SHIPPED, s.AMOUNT FROM MSD.MSD_SHIPMENT_DATA s WHERE s.INSTANCE = :instance AND s.SHIPPED_DATE BETWEEN :from_date AND :to_date;
• Aggregation by channel or sales representative for period-over-period shipment trend reporting.
• Refresh reconciliation using LAST_REFRESH_NUM and PROGRAM_ID to identify rows produced by a specific planning run.
• Joining SR_ITEM_PK to MSD_LEVEL_VALUES to pivot shipments against the item hierarchy for category-level reporting.

Related Objects

• MSD_LEVEL_VALUES — referenced via MSD_SHIPMENT_DATA.SR_ITEM_PK; the documented foreign key target supplying item dimension members.
• MSD_INVENTORY_DATA and other MSD fact tables — sibling planning facts sharing the same dimension surrogate-key conventions.
• MSD_CUSTOMER_SITE and customer-level dimension structures resolved through SR_CUSTOMER_PK and SR_SHIP_TO_LOC_PK.
• MSD_SALES_CHANNEL and sales-representative level tables resolved through SR_SALES_CHANNEL_PK and SR_SALES_REP_PK.
• MSD_DP_POPULATE / planning collection programs — concurrent processes that populate and refresh this fact table.