Results for “msc_item_cross_refs_v”

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

Overview

MSC_ITEM_CROSS_REFS_V is a reporting and integration view within the Oracle Advanced Supply Chain Planning (MSC) product family, delivered as part of Oracle E-Business Suite 12.1.1 and 12.2.2. The view exposes cross-reference relationships between item definitions used by supply chain planning and the broader item master. Its principal purpose is to present bilateral (symmetric) item equivalence information in a flattened, query-friendly format, so that planning engines, concurrent programs, and custom reports can resolve an item to its identical counterpart without needing to navigate the normalized base table directly.

The view is particularly relevant to the search term ref_item_number, which corresponds to the REF_ITEM_NUMBER column. This column surfaces the party item number of the referenced (identical) item, allowing a report to display both the primary item and its cross-referenced equivalent side by side.

Underlying Base Objects

The view is defined over three documented objects in the IPD (Internet Procurement/Item Data) schema:

• IPD_ITEM_CROSS_REFS_B — the base table holding the cross-reference relationship, including ITEM_ID, REF_ITEM_ID, and REF_TYPE.
• IPD_ITEMS_V (aliased twice as IT1 and IT2) — a view supplying item descriptive attributes such as PARTY_ITEM_NUMBER, SHORT_DESCRIPTION, LONG_DESCRIPTION, PARTY_ID, and PARTY_NAME.

Both branches of the UNION restrict to REF_TYPE = 'IDENTICAL', meaning the view only reports items that are treated as exact equivalents rather than substitutes or alternates. The ETRM metadata lists no directly referenced base tables beyond these, and notes that the view is not implemented in every database — availability depends on whether the Advanced Supply Chain Planning module and its item cross-reference data structures are installed.

Key Columns

• ITEM_ID — internal identifier of the primary item.
• ITEM_NUMBER — party item number of the primary item.
• SHORT_DESCRIPTION / LONG_DESCRIPTION — descriptive text for the primary item.
• PARTY_ID / PARTY_NAME — owning party (typically the inventory organization or operating unit context) of the primary item.
• REF_ITEM_ID — internal identifier of the cross-referenced (identical) item.
• REF_ITEM_NUMBER — party item number of the cross-referenced item; this is the column matched by the user's search term.
• REF_SHORT_DESCRIPTION / REF_LONG_DESCRIPTION — descriptive text for the cross-referenced item.
• REF_PARTY_ID / REF_PARTY_NAME — owning party of the cross-referenced item.

Because the view is a UNION of two symmetric selects, each relational pair appears in both directions: once with the item in the primary position and once with it in the referenced position. This bidirectional design is important when writing queries to avoid double counting.

Common Use Cases and Queries

Typical uses include item equivalence reporting, deduplication analysis, and resolving planning output against alternate item numbering schemes. A basic lookup for a known item number follows:

• Retrieve all identical items associated with a given item number.
• List cross-reference pairs for a specific party or organization.
• Populate an integration extract mapping internal item numbers to party item numbers.

Sample SQL:

SELECT ITEM_NUMBER, REF_ITEM_NUMBER, REF_SHORT_DESCRIPTION, REF_PARTY_NAME FROM MSC_ITEM_CROSS_REFS_V WHERE ITEM_NUMBER = :p_item_number;

To list distinct pairs once, filter on one side, for example WHERE ITEM_ID < REF_ITEM_ID, which suppresses the mirrored row produced by the UNION. All queries should be scoped with appropriate party or organization filters, since the view spans multiple parties by design.