Search Results msd_item_list_price




Overview
Explore in AI Chat

Overview

MSD_ITEM_LIST_PRICE is a fact table owned by the MSD schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2, delivered as part of the Demand Planning module. It stores the list price, base unit of measure, and average discount for each product participating in demand planning calculations. Because demand planning relies on historical shipments and bookings expressed in both units and revenue, this table supplies the price and discount reference data required to convert between quantity-based and value-based demand signals. The table is populated and maintained by the Demand Planning data collection and refresh programs rather than by interactive user entry, and it is classified as VALID in the ETRM repository.

Under the heuristic Data Vault classification mined from the foreign key structure, MSD_ITEM_LIST_PRICE is treated as a standalone object, i.e. it is not modeled as part of a hub-link-satellite constellation. In practice this reflects the fact that the table behaves as a denormalized fact or reference-fact structure keyed on item and instance, rather than as a normalized transactional entity. Modelers integrating this table into a warehouse should therefore treat it as a fact-like source rather than attempting to decompose it into hubs and links.

Key Information Stored

The table contains 20 documented columns. The most significant are:

Common Use Cases and Queries

Typical usage involves reporting on price and discount behavior for demand planning items, validating data collection refreshes, and joining to inventory and item master data for revenue-based demand analysis.

Example: retrieve current list price and discount per item for the latest instance.

SELECT item, base_uom, list_price, avg_discount
FROM   msd.msd_item_list_price
WHERE  instance = (SELECT MAX(instance) FROM msd.msd_item_list_price)
ORDER BY item;

Example: derive net price and check refresh metadata.

SELECT item,
       list_price,
       list_price * (1 - avg_discount/100) AS net_price,
       last_refresh_num,
       action_code
FROM   msd.msd_item_list_price
WHERE  instance = :p_instance
AND    item = :p_item;

Common reporting scenarios include price erosion trending across refresh cycles (grouping by LAST_REFRESH_NUM), discount variance by ITEM_TYPE_ID, and reconciliation of demand plan revenue against list price multiplied by planned quantity.

Related Objects

  • CZ_ITEM_TYPES — referenced by MSD_ITEM_LIST_PRICE.ITEM_TYPE_ID; join on item_type_id to resolve item type descriptions.
  • MSD_ITEM / item master equivalents in the MSD staging schema — join on SR_ITEM_PK or ITEM to obtain descriptive item attributes.
  • MSD_ITEM_COST — companion fact table holding cost rather than price, frequently used alongside this table for margin analysis.
  • MSD_INSTANCES — provides context for the INSTANCE value and refresh cycle definitions.
  • MSD_HISTORICAL_SHIPMENTS — quantity and revenue actuals reconciled against list price and discount.
  • MSD_FORECAST — consumes item prices to value forecast demand.
  • Demand Planning concurrent programs and collection APIs — populate and refresh MSD_ITEM_LIST_PRICE during data collection.