Search Results item_pk




Overview
Explore in AI Chat

Overview

APPS.MSD_PRICE_LIST_V is a reporting view in the Oracle E-Business Suite Advanced Pricing / ETRM (Enterprise Territory and Pricing Management) schema. It exposes the dimensional structure of price list records by resolving the surrogate level keys stored on the MSD_PRICE_LIST staging table into their descriptive level identifiers and primary keys held in MSD_LEVEL_VALUES. The view is owned by the APPS schema and is typically consumed by pricing analytics, territory and pricing integration jobs, and downstream reporting that requires a denormalized, human-readable rendering of price list dimensions.

Because the underlying MSD_PRICE_LIST object is populated by the pricing refresh and dimension-resolution processes, MSD_PRICE_LIST_V is most useful in contexts where price list rows must be joined to organization, product, geography, sales channel, sales representative, and demand class hierarchies. It is a read-only construct; it performs no DML and is intended strictly for query access.

Underlying Base Objects

The view is defined over two documented base objects, both referenced through synonyms in the APPS schema:

All joins are equijoins on INSTANCE, SR_LEVEL_PK, and LEVEL_ID. With the exception of the demand class join (DCS_PK), which uses the outer-join (+) operator and is therefore optional, every dimension join is mandatory. A row is returned only when matching level value rows exist for organization, product, sales channel, sales representative, and geography; demand class may be null. Note that the customer level join present in older source variants is commented out in the documented view text, so customer dimension data is not exposed.

Key Columns

  • ORG_PK.LEVEL_ID / INV_ORG_PK — the organization level identifier and resolved primary key; the inventory organization dimension.
  • ITEM_PK.LEVEL_ID / ITEM_PK — the product level identifier and resolved product primary key.
  • SALES_CHANNEL_PK, SALES_REP_PK, GEO_PK — level identifiers and resolved primary keys for sales channel, sales representative, and geography.
  • DCS_PK.LEVEL_ID / DCS_PK.LEVEL_PK — the demand class dimension, nullable due to the outer join.
  • USER_DEFINED1_LVL_ID, USER_DEFINED2_LVL_ID — level identifiers for configurable user-defined dimensions; their PK columns are returned as TO_NUMBER(NULL) placeholders.
  • PRICE_LIST, START_DATE, END_DATE, PRICE, PRIORITY — the commercial attributes of the price list record.
  • LAST_REFRESH_NUM, CREATED_BY_REFRESH_NUM, ACTION_CODE — refresh and change-tracking metadata used by pricing synchronization processes.
  • COST_FLAG — a literal constant '0', indicating the row represents a selling price list rather than a cost list.

Common Use Cases and Queries

Typical uses include validating which price list records resolved successfully across all mandatory dimensions, auditing refresh cycles, and feeding pricing data to external systems. A basic query retrieving active price list entries with their organization and product identifiers might read:

  • SELECT price_list, inv_org_pk, item_pk, price, start_date, end_date, priority FROM apps.msd_price_list_v WHERE TRUNC(SYSDATE) BETWEEN start_date AND end_date;
  • SELECT price_list, COUNT(*) FROM apps.msd_price_list_v GROUP BY price_list; — price list row counts by list.
  • SELECT * FROM apps.msd_price_list_v WHERE last_refresh_num > created_by_refresh_num; — rows changed in the latest refresh.

Because all mandatory joins must succeed, the view is also an effective diagnostic for incomplete dimension resolution: records absent from the view correspond to MSD_PRICE_LIST rows whose organization, product, channel, representative, or geography level values could not be matched.