Results for “msd_event_products_v”

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

Overview

MSD_EVENT_PRODUCTS_V is a public Oracle EBS view owned by the APPS schema and defined over Demand Planning (MSD) event data. It is documented as a child view to MSD_EVENTS_V, meaning it exposes the product-level detail that complements the event header information. Whereas MSD_EVENTS_V describes the promotion or event itself, MSD_EVENT_PRODUCTS_V records every product for which a given promotion is run, together with the start and end date applicable to each product within that event. In release 12.1.1 and 12.2.2 the object is stored as a standard view (not a synonym-targeted table), and its status is VALID, so it can be queried directly from PL/SQL, reports, and integrations. The view normalizes the underlying transactional rows so that consumers obtain enumerated product level information (the level name and level value) alongside the surrogate keys stored on the base table. This makes it suitable for reporting, data extraction, and programmatic access where callers need promotion-to-product assignments in a ready-to-display form rather than resolving level identifiers themselves.

Underlying Base Objects

The view is defined over four referenced base objects, each accessed through an APPS synonym: MSD_EVENTS, MSD_EVENT_PRODUCTS, MSD_LEVELS, and MSD_LEVEL_VALUES. The join logic links MSD_EVENTS to MSD_EVENT_PRODUCTS on EVENT_ID, so only products belonging to a valid event are returned. MSD_LEVELS is joined on PRODUCT_LVL_ID = LEVEL_ID with the filter DIMENSION_CODE = 'PRD', restricting results to the Product dimension. MSD_LEVEL_VALUES (aliased ITEM) is joined on both SR_PRODUCT_LVL_PK = SR_PRODUCT_LVL_PK and LEVEL_ID = PRODUCT_LVL_ID, and additionally on INSTANCE = INSTANCE, preserving instance scoping. This four-way join resolves the abstract level keys on MSD_EVENT_PRODUCTS into meaningful product level names and values, while retaining the event/product association. Because the base objects are exposed as synonyms, the view remains schema-qualified as APPS.MSD_EVENT_PRODUCTS_V for consumers.

Key Columns

• ROW_ID — the ROWID of the MSD_EVENT_PRODUCTS row; useful for unique identification and updates.
• INSTANCE, EVENT_ID, SEQ_ID — instance scope, the parent event identifier, and the sequence of the product within the event.
• PRODUCT_LVL_ID, PRODUCT_LVL_NAME, PRODUCT_LVL_VAL — the product level identifier, its resolved name (from MSD_LEVELS.LEVEL_NAME), and the level value (from MSD_LEVEL_VALUES.LEVEL_VALUE).
• PRODUCT_LVL_PK, SR_PRODUCT_LVL_PK — surrogate keys for the product level value, aligning the event product record to the level value definition.
• START_TIME, END_TIME — the effective start and end of the promotion for that product.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.
• Concurrent request columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical uses include reporting active promotions by product, validating product assignments before loading demand plans, and extracting event/product pairs for downstream planning or analytics. The audit and request columns support auditing and lineage tracing.

SELECT event_id, product_lvl_name, product_lvl_val,
       start_time, end_time
FROM   apps.msd_event_products_v
WHERE  event_id = :p_event_id
ORDER  BY seq_id;

To find currently active promotions for a product value:

SELECT event_id, product_lvl_val, start_time, end_time
FROM   apps.msd_event_products_v
WHERE  product_lvl_val = :p_product
AND    TRUNC(SYSDATE) BETWEEN TRUNC(start_time) AND TRUNC(end_time);