Results for “gr_related_items_xml”

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

Overview

GR_RELATED_ITEMS_XML is a read-only database view owned by the APPS schema in Oracle E-Business Suite, registered under the GR product family — Process Manufacturing Regulatory Management. Its documented status is VALID in both EBS 12.1.1 and 12.2.2. The view exposes master item associations alongside the primary inventory item and its associated related inventory items, returning a denormalized, XML-consumer-friendly result set keyed by organization and inventory item identifier.

Functionally, the view acts as a reporting and integration surface for the item relationship data maintained in Oracle Inventory. It joins the related-items intersection table to the item key flexfield view twice, once for the driving item and once for the associated item, so that both sides of a relationship are resolved to concatenated segment values and descriptions in a single query. The "_XML" suffix indicates that the object is intended for use by XML-based extraction, BI Publisher data templates, or interface programs that publish item association content in structured form, rather than for online transaction entry.

Underlying Base Objects

The view is defined over two referenced base objects, both accessed through APPS synonyms:

• MTL_RELATED_ITEMS — the Oracle Inventory table that stores item-to-item relationships, including the organization, the inventory item, the related item, and the relationship type.
• MTL_SYSTEM_ITEMS_KFV — the key flexfield view over MTL_SYSTEM_ITEMS, which resolves the item key flexfield into the CONCATENATED_SEGMENTS column and supplies DESCRIPTION.

MTL_SYSTEM_ITEMS_KFV is joined twice: once as alias G against R.INVENTORY_ITEM_ID and R.ORGANIZATION_ID, and once as alias M against R.RELATED_ITEM_ID and R.ORGANIZATION_ID. Both joins are enforced on organization identifier as well as item identifier, ensuring that items are matched only within the same inventory organization. The view text applies a fixed predicate, R.RELATIONSHIP_TYPE_ID = 19, which restricts output to a single relationship type seeded or configured for this purpose in Process Manufacturing Regulatory Management. No outer joins or filters on item status, enabled flags, or organization are present in the documented view text.

Key Columns

• ORGNID — organization identifier, sourced from R.ORGANIZATION_ID. Identifies the inventory organization in which the relationship is defined.
• ITEMID — the driving inventory item identifier, sourced from R.INVENTORY_ITEM_ID.
• ITEMCODE — the concatenated key flexfield segments of the driving item, from G.CONCATENATED_SEGMENTS.
• CODE — the concatenated key flexfield segments of the related item, from M.CONCATENATED_SEGMENTS.
• NAME — the description of the related item, from M.DESCRIPTION.

Note that no column exposes the relationship type, the related item identifier, or the driving item description; consumers requiring those values must query the base tables directly.

Common Use Cases and Queries

Typical uses include feeding item association data to regulatory submission extracts, BI Publisher reports listing master item associations per organization, and reconciliation of related-item setups before data migration or upgrade. A representative query returning all associations in an organization:

• SELECT orgnid, itemid, itemcode, code, name FROM apps.gr_related_items_xml WHERE orgnid = :organization_id ORDER BY itemcode, code;
• SELECT itemcode, code, name FROM apps.gr_related_items_xml WHERE itemcode = :item_segment_value;

Because the view is a simple join with a hard-coded relationship type filter, it performs adequately when the driving predicate is on ORGNID or ITEMID. For set-based extracts over large organizations, joining the view to MTL_SYSTEM_ITEMS_KFV or MTL_PARAMETERS is unnecessary, since ORGNID is already present; adding MTL_PARAMETERS only when the organization code itself is required. As with all APPS views, no DML should be issued against GR_RELATED_ITEMS_XML.