Search Results msd_dp_price_lists




Overview
Explore in AI Chat

Overview

MSD_DP_PRICE_LISTS is a Demand Planning (MSD) table in Oracle E-Business Suite that stores the price lists consumed by the Demand Planning application. In the EBS 12.1.1 and 12.2.2 data models it resides in the MSD schema and is documented in ETRM as VALID. The table associates a named price list with a specific demand plan, providing the pricing reference data that Demand Planning uses for revenue, margin and demand-shaping calculations.

The ETRM relationship data classifies this object heuristically as standalone — that is, it carries no documented foreign-key dependencies to other tables within the mined FK structure, and no dependent children are recorded. In Data Vault terms this suggests modelling the object as its own hub (or a small hub-and-satellite pairing), keyed on the natural business key, rather than as a link between two other entities. Because the classification is heuristic, the modelling suggestion should be validated against the actual ETL lineage before being adopted.

Key Information Stored

The table carries 15 documented columns. The most significant are:

Two unique indexes define business-key candidates: MSD_DP_PRICE_LISTS_U1 on (DEMAND_PLAN_ID, PRICE_LIST_NAME, ZD_EDITION_NAME), which is the true natural key, and MSD_DP_PRICE_LISTS_U2 on (DP_PRICE_LIST_ID, ZD_EDITION_NAME), which supports edition-scoped access by surrogate key. Note that both candidates include ZD_EDITION_NAME, so no query should treat a price-list name as unique across editions.

Common Use Cases and Queries

Typical reporting scenarios include listing the price lists defined for a given demand plan, reconciling price lists between seed and user-defined content, and joining plan-level pricing back to demand plan definitions.

SELECT dp_price_list_id, price_list_name, demand_plan_id
FROM   msd.msd_dp_price_lists
WHERE  demand_plan_id = :p_demand_plan_id
AND    zd_edition_name = :p_edition;

Where two plans share a price-list name, the U1 index makes the duplicate easy to locate:

SELECT price_list_name, COUNT(*)
FROM   msd.msd_dp_price_lists
WHERE  zd_edition_name = :p_edition
GROUP BY price_list_name
HAVING COUNT(*) > 1;

Audit and purge routines can rely on REQUEST_ID, PROGRAM_ID and LAST_UPDATE_DATE to trace which concurrent program created or refreshed a given row, and on DELETEABLE_FLAG to decide eligibility for cleanup. Because the table is edition-scoped, every query should filter on ZD_EDITION_NAME to avoid cross-edition duplication.

Related Objects

ETRM records no foreign keys for this object, so the relationships below are inferred from the documented column names and standard MSD conventions rather than from enforced constraints:

  • MSD_DEMAND_PLANS (or the equivalent demand-plan header table) — joined via DEMAND_PLAN_ID to resolve plan names and status.
  • MSD_DP_PLAN_PRICE_LISTS and related MSD_DP_% price-list child tables — joined via DP_PRICE_LIST_ID to retrieve the price-list detail lines.
  • FND_CONCURRENT_REQUESTS — joined on REQUEST_ID for concurrent-program audit reporting.
  • FND_APPLICATION and FND_CONCURRENT_PROGRAMS — joined on PROGRAM_APPLICATION_ID and PROGRAM_ID to name the originating process.
  • FND_USER — joined on CREATED_BY and LAST_UPDATED_BY for WHO-column auditing.

No public PL/SQL API specific to this table is documented in ETRM; price-list maintenance is performed through the Demand Planning application and its concurrent programs.