Results for “product_lvl_pk”

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

Overview

MSD_PRICE_LIST_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the MSD – Demand Planning product family. Its documented purpose is to expose price list information in a form that is directly consumable by demand planning, reporting, and integration processes. In EBS 12.1.1 and 12.2.2, the object is registered with a status of VALID, and it is classified as a VIEW rather than a table, meaning it holds no data of its own and derives all results at runtime from its underlying base objects.

The view serves as a denormalized presentation layer over the price list staging data used by Demand Planning. Because it resolves surrogate level identifiers into their corresponding level values, consumers of the view are not required to join the level-values infrastructure themselves. This makes the view suitable for ad hoc reporting, custom concurrent programs, and downstream extracts that must reconcile price list records against organization, product, sales channel, sales representative, and geography dimensions.

Underlying Base Objects

According to the ETRM metadata for 12.2.2, MSD_PRICE_LIST_V is defined over two referenced base objects, both exposed to APPS through synonyms: MSD_PRICE_LIST and MSD_LEVEL_VALUES. MSD_PRICE_LIST is the driving table and supplies the price list attributes, including PRICE_LIST_NAME, START_DATE, END_DATE, PRICE, PRIORITY, LAST_REFRESH_NUM, CREATED_BY_REFRESH_NUM, and ACTION_CODE. MSD_LEVEL_VALUES supplies the decoded level and level primary key values for each dimensional attribute.

The join is performed five times against MSD_LEVEL_VALUES — once each for organization, product, sales channel, sales representative, and geography. Each join correlates on INSTANCE, the stored level primary key (SR_*_LVL_PK), and the level identifier column stored on MSD_PRICE_LIST, ensuring that the decoded value aligns with the same planning instance and level definition as the source price list row. The view text also references customer and user-defined level columns; the customer join is commented out in the shipped definition, and USER_DEFINED1_PK and USER_DEFINED2_PK are returned as NULL while their corresponding level identifiers are preserved.

Key Columns

• ORGANIZATION_LVL_ID / ORGANIZATION_LVL_PK – Level identifier and decoded primary key for the inventory organization dimension.
• PRODUCT_LVL_ID / PRODUCT_LVL_PK – Level identifier and decoded primary key for the item or product dimension.
• SALESCHANNEL_LVL_ID / SALESCHANNEL_LVL_PK – Level identifier and decoded value for the sales channel dimension.
• SALES_REP_LVL_ID / SALES_REP_LVL_PK – Level identifier and decoded value for the sales representative dimension.
• GEOGRAPHY_LVL_ID / GEOGRAPHY_LVL_PK – Level identifier and decoded value for the geography dimension.
• USER_DEFINED1_LVL_ID / USER_DEFINED1_LVL_PK and the corresponding USER_DEFINED2 pair – Descriptive flexfield level identifiers; the PK columns are returned as NULL in the shipped view.
• PRICE_LIST, START_DATE, END_DATE, PRICE, PRIORITY – Core commercial attributes of the price list entry, including its validity window and evaluation precedence.
• LAST_REFRESH_NUM, CREATED_BY_REFRESH_NUM, ACTION_CODE – Refresh and change-tracking attributes used by the Demand Planning collection and refresh cycle.

Common Use Cases and Queries

The view is typically queried to validate price list collection results, to reconcile price list coverage against planning dimensions, and to feed custom reports or extracts. A representative query filtering on a specific organization and price list name is shown below.

SELECT price_list, start_date, end_date, price, priority
FROM apps.msd_price_list_v
WHERE organization_lvl_pk = :org_pk
AND price_list = :price_list_name
AND SYSDATE BETWEEN start_date AND end_date;

A second common pattern aggregates price list entries by sales channel and geography for coverage analysis:

SELECT saleschannel_lvl_pk, geography_lvl_pk, COUNT(*) entry_count
FROM apps.msd_price_list_v
WHERE action_code IS NULL
GROUP BY saleschannel_lvl_pk, geography_lvl_pk;

• Validating that price list records collected into Demand Planning resolve correctly across all dimensional levels.
• Identifying price lists with overlapping validity windows or conflicting priorities for the same dimensional combination.
• Reviewing ACTION_CODE and refresh columns to confirm which rows were created or updated during the most recent collection run.
• Building custom operational reports that require decoded organization, product, and geography values without direct queries against MSD_LEVEL_VALUES.