Results for “msc_sup_dem_transactions_v”

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

Overview

MSC_SUP_DEM_TRANSACTIONS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the MSC (Advanced Supply Chain Planning) product family. It exposes a consolidated list of supplier-to-customer demand and supply relationships derived from the planning engine's internal staging structures, restricted to a specific planning context (PLAN_ID = -1). The view is designed to surface transactional pairings between a publisher entity and its counterpart — customer and supplier identifiers together with the associated sites and inventory item — so that downstream reporting, integration, and planning diagnostics can resolve who is supplying whom for a given item.

Its primary role is as a reporting and integration convenience layer. Rather than forcing consumers to interpret the ORDER_TYPE semantics embedded in MSC_SUP_DEM_ENTRIES_UI_V, the view normalizes two disjoint sets of order types into a single, direction-consistent projection. Order types 9, 13, 16, 19, and 20 are treated as demand-direction rows where PUBLISHER_ID and PUBLISHER_SITE_ID map to CUSTOMER_ID and CUSTOMER_SITE_ID, while order type 15 represents the reverse direction. Both halves are combined with UNION, and DISTINCT is applied to eliminate duplicate tuples generated by the underlying planning structures.

Underlying Base Objects

The view is defined over MSC_SUP_DEM_ENTRIES_UI_V, a UI-oriented view that carries the raw publisher, supplier, item, and order-type columns used by the Advanced Supply Chain Planning workbench. The definition filters on PLAN_ID = -1, indicating a design-time or unplanned context, and partitions rows by PUBLISHER_ORDER_TYPE. Metadata also references FND_GLOBAL and MSC_X_UTIL as dependent objects, which are standard MSC utilities used for session and planning context resolution. Because both halves of the UNION project from the same source view with renamed columns, the join semantics are implicit rather than driven by explicit foreign keys; the view is therefore best understood as a filtered and pivoted projection of a single base view rather than a multi-table join.

Key Columns

• CUSTOMER_ID — The party identifier of the customer in the supply-demand pairing. Populated from PUBLISHER_ID for order types 9, 13, 16, 19, and 20, and from the CUSTOMER_ID column for order type 15.
• CUSTOMER_SITE_ID — The customer site associated with CUSTOMER_ID, drawn from PUBLISHER_SITE_ID in the demand-direction branch. This is the column most commonly referenced in site-level qualification, since the user search term "customer_site_id" resolves directly here.
• SUPPLIER_ID — The party identifier of the supplier in the pairing, sourced from CUSTOMER_ID in the demand branch and from PUBLISHER_ID for order type 15.
• SUPPLIER_SITE_ID — The supplier site corresponding to SUPPLIER_ID.
• INVENTORY_ITEM_ID — The inventory item for which the supply-demand relationship is reported; carries through unchanged from the base view.

Common Use Cases and Queries

Typical use cases include identifying which customer sites are linked to which supplier sites for a given item, validating sourcing relationships prior to planning runs, and feeding external analytics or ETL pipelines with normalized pairings. A basic lookup by customer site is straightforward:

SELECT customer_id, customer_site_id, supplier_id, supplier_site_id, inventory_item_id FROM apps.msc_sup_dem_transactions_v WHERE customer_site_id = :p_site_id;

To retrieve the full set of suppliers for a specific item, filter on INVENTORY_ITEM_ID and order by supplier:

SELECT DISTINCT supplier_id, supplier_site_id FROM apps.msc_sup_dem_transactions_v WHERE inventory_item_id = :p_item_id ORDER BY supplier_id;

Because the view does not expose PLAN_ID or ORDER_TYPE, callers cannot further narrow the specific order type at the view layer; any such distinction must be made against MSC_SUP_DEM_ENTRIES_UI_V directly. Reports should also account for the PLAN_ID = -1 constraint, which limits results to the design-time context and excludes planned-run data.