Results for “ship_to_loc_pk”

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

Overview

MSD_OPPORTUNITY_DATA_V is a PL/SQL view owned by the APPS schema and shipped as part of the MSD – Demand Planning module in Oracle E-Business Suite 12.1.1 and 12.2.2. It is not a conventional functional view; it is a user-extensible interface view that exposes Opportunity History data stored in the Advanced Supply Chain Planning / Demand Planning staging tables. The view is defined over MSD_CS_DATA and translates the generic, positional ATTRIBUTE_n columns of the staging table into meaningful planning dimensions such as inventory organization, item, customer, sales channel, sales representative, ship-to location, quantity, amount, and end date.

The defining characteristic of this view is that it is not stripped by demand plan ID. Unlike many MSD views that are automatically filtered for a specific plan, MSD_OPPORTUNITY_DATA_V returns the complete set of opportunity data. This design allows customers to create a custom view that further restricts the rows, then pass that custom view name as a parameter to a demand plan. The view therefore acts as a template that controls which opportunity records feed the planning engine.

Underlying Base Objects

The view joins the synonym MSD_CS_DATA (aliased MBD) to five instances of the synonym MSD_LEVEL_VALUES (aliased ORG_LVL, ITM_LVL, CUS_LVL, CHN_LVL, REP_LVL, and GEO_LVL). A subquery against MSD_CS_DEFINITIONS restricts rows to the correct data collection definition. MSD_CS_DATA stores opportunity and consumption history in a flattened form; MSD_LEVEL_VALUES holds the dimension member values and surrogate keys that decode the internal level identifiers; MSD_CS_DEFINITIONS defines each collection program and its identifiers.

Each dimension join is driven by the instance identifier MBD.ATTRIBUTE_1 matched to INSTANCE, followed by the appropriate ATTRIBUTE_n pair matched to LEVEL_ID and SR_LEVEL_PK. This pattern decodes the raw stored keys into human-readable level values while preserving the surrogate primary keys required by the planning engine.

Key Columns

• INV_ORG / INV_ORG_PK – Inventory organization name and its surrogate key, derived from LEVEL_VALUE and LEVEL_PK. This is the column typically referenced when users search on "inv_org".
• ITEM / ITEM_PK – Planning item and its key.
• CUSTOMER / CUSTOMER_PK – Customer dimension value.
• SALES_CHANNEL / SALES_CHANNEL_PK – Sales channel dimension.
• SALES_REP / SALES_REP_PK – Sales representative dimension.
• SHIP_TO_LOC / SHIP_TO_LOC_PK – Ship-to geography dimension.
• USER_DEFINED1, USER_DEFINED2 – Free-form attributes (ATTRIBUTE_28, ATTRIBUTE_32) for customer-defined groupings; their PK columns return NULL.
• QUANTITY – Numeric opportunity quantity, cast from ATTRIBUTE_41.
• AMOUNT – Numeric opportunity amount, cast from ATTRIBUTE_42.
• TIME_LVL_ID – Time bucket identifier from ATTRIBUTE_34.
• END_DATE – Opportunity end date, converted from ATTRIBUTE_43 using the YYYY/MM/DD mask.

Common Use Cases and Queries

The principal use case is to build a restriction view for a demand plan. A planner creates a custom view that selects from MSD_OPPORTUNITY_DATA_V with a WHERE clause limiting data by inventory organization, customer, or date range, then supplies that view name as a parameter when defining the demand plan. The following examples illustrate typical access patterns.

To review all opportunities for a specific inventory organization:

SELECT INV_ORG, ITEM, CUSTOMER, QUANTITY, AMOUNT, END_DATE
FROM APPS.MSD_OPPORTUNITY_DATA_V
WHERE INV_ORG = :p_org;

To aggregate opportunity demand by organization and item within a date window:

SELECT INV_ORG, ITEM, SUM(QUANTITY) TOTAL_QTY, SUM(AMOUNT) TOTAL_AMT
FROM APPS.MSD_OPPORTUNITY_DATA_V
WHERE END_DATE BETWEEN :start_date AND :end_date
GROUP BY INV_ORG, ITEM;

Because the view is unstripped by plan ID, queries against it may return rows belonging to multiple data collections. Analysts should join or filter on criteria consistent with MSD_CS_DEFINITIONS when plan-specific isolation is required. The view is read-only and intended for query and parameterization rather than direct DML.