Search Results owner_orgn_code

Overview

GMD_ITEM_SUBSTITUTION_HDR_VL is a seeded APPS view within the Oracle E-Business Suite Process Manufacturing Product Development (GMD) module. It exposes item substitution header information maintained in Oracle Process Manufacturing (OPM) Product Development, allowing substitution rules to be queried alongside translated descriptive text. The object follows the standard EBS "_VL" convention, denoting a view that joins a "_B" base table with a "_TL" translation table and filters rows by the session language via USERENV('LANG'). This design supports multi-language installations, where substitution descriptions are stored per-language while core substitution attributes remain language-independent. In Oracle EBS 12.1.1 and 12.2.2 the view is registered in the APPS schema with a VALID status and is treated as a reporting and integration surface rather than a transactional entity. It is commonly consumed by concurrent programs, OAF-based pages, and custom integrations requiring substitution headers, and queries against it reflect the effective description for the language of the connected session.

Underlying Base Objects

The view is defined over two documented base objects:

• GMD_ITEM_SUBSTITUTION_HDR_B (SYNONYM) — the base table holding substitution header records, including identifiers, status, dates, quantity, and descriptive flexfield attributes.
• GMD_ITEM_SUBSTITUTION_HDR_TL (SYNONYM) — the translation table holding language-specific substitution descriptions.

The join condition is ITSUB.SUBSTITUTION_ID = ITSUBT.SUBSTITUTION_ID AND ITSUBT.LANGUAGE = USERENV('LANG'). Because the view references the _B table, it is language-independent for most columns; only SUBSTITUTION_DESCRIPTION is drawn from the _TL table. The presence of ROWID from the base header table supports row-level identification and is useful when building updatable or DML-oriented logic. Column mappings such as ORIGINAL_ITEM_ID and ORIGINAL_INVENTORY_ITEM_ID coexist, reflecting different item identification conventions used across OPM releases.

Key Columns

• SUBSTITUTION_ID — Primary identifier for the substitution header; used to join to detail and other substitution objects.
• SUBSTITUTION_NAME — User-defined name of the substitution rule.
• SUBSTITUTION_DESCRIPTION — Language-specific description from the _TL table.
• SUBSTITUTION_VERSION and SUBSTITUTION_STATUS — Version and lifecycle status of the header record.
• ORIGINAL_ITEM_ID / ORIGINAL_INVENTORY_ITEM_ID — Identifiers of the item being substituted.
• ORIGINAL_QTY — The quantity of the original item for which the substitution applies; directly relevant to users searching for this term, as it defines the basis quantity against which replacement items are evaluated.
• PREFERENCE — Ordering or priority indicator for the substitution.
• START_DATE / END_DATE — Effective date range for the substitution.
• OWNER_ORGN_CODE / OWNER_ORGANIZATION_ID — Owning organization context.
• REPLACEMENT_UOM_TYPE and ORIGINAL_UOM — Unit of measure attributes governing quantity interpretation.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1..30 — Descriptive flexfield columns for customer-defined extensions.
• Audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) — Standard EBS who-columns.

Common Use Cases and Queries

The view is typically used to report substitution headers in the session language, validate the original quantity and UOM for a substitution, and drive integrations that require the effective description. A representative query retrieving active substitutions with quantity detail:

• SELECT substitution_id, substitution_name, substitution_description, substitution_status, original_item_id, original_qty, original_uom, preference, start_date, end_date
• FROM apps.gmd_item_substitution_hdr_vl
• WHERE substitution_status = 'ACTIVE'
• AND SYSDATE BETWEEN start_date AND NVL(end_date, SYSDATE + 1);

Because ORIGINAL_QTY is a header-level quantity, analysts commonly join to detail substitution tables to compare original and replacement quantities per line. Filtering on ORIGINAL_UOM alongside ORIGINAL_QTY avoids mixing quantities expressed in different units. The view should be queried directly rather than through base tables when localized descriptions are required, ensuring the join to the translation table applies the correct language filter.