Search Results parent_sort_code

Overview

BOM_IMPL_INQUIRY_V is an APPS-owned database view in the Oracle E-Bills of Material (BOM) module, documented in ETRM for both 12.1.1 and 12.2.2 with a status of VALID. Its stated purpose is to expose item usage information: specifically, the exploded "where-used" relationship between a parent assembly and the components consumed by it, together with effectivity, revision, and implementation status attributes. The view is the query-side artifact of the Bills of Material "Implemented Items" inquiry, presenting the flattened result of the BOM explosion rather than raw base table rows.

The view is populated from BOM_SMALL_IMPL_TEMP, a session-scoped temporary structure that BOM's implementation and inquiry logic writes into before the inquiry screen renders results. Because of this design, BOM_IMPL_INQUIRY_V is meaningful primarily within a session where that temporary data has been seeded; it is not a standalone, always-populated reporting object. The view text also invokes BOM_GLOBALS, INV_MEANING_SEL, FND_GLOBAL, HR_GENERAL, and HR_SECURITY, which applies organization-level security and concatenated flexfield descriptions to the returned rows.

Underlying Base Objects

The documented referenced objects are: BOM_GLOBALS (PACKAGE), BOM_SMALL_IMPL_TEMP (SYNONYM), FND_GLOBAL (PACKAGE), FND_LOOKUP_VALUES (SYNONYM), HR_GENERAL (PACKAGE), HR_ORGANIZATION_UNITS (VIEW), HR_SECURITY (PACKAGE), INV_MEANING_SEL (PACKAGE), MFG_LOOKUPS (VIEW), MTL_ITEM_REVISIONS_B (SYNONYM), MTL_PARAMETERS (SYNONYM), and MTL_SYSTEM_ITEMS_B_KFV (VIEW).

• BOM_SMALL_IMPL_TEMP (T) — the driving structure holding the exploded parent/component pairs, quantities, operation sequence numbers, and end-item unit number ranges.
• MTL_SYSTEM_ITEMS_B_KFV (F, M) — key flexfield views supplying the parent item (F) and the component item (M) segments and their concatenated descriptions.
• MTL_ITEM_REVISIONS_B (MIR) — joined using MTL_ITEM_REVISIONS_N1 to return revision, revision label, lifecycle, and current phase identifiers.
• MTL_PARAMETERS (MP) and HR_ORGANIZATION_UNITS (HOU) — supply organization code and organization name.
• MFG_LOOKUPS (ML2, LU2) and FND_LOOKUP_VALUES (LU) — decode item type, basis type, and effectivity control meaning.
• Packages — BOM_GLOBALS.GET_CONCAT_SEGS, INV_MEANING_SEL.C_ITEM_DESCRIPTION, FND_GLOBAL, HR_GENERAL, and HR_SECURITY handle descriptions and security.

Key Columns

• ORGANIZATION_CODE / ORG_NAME — organization identifiers from MTL_PARAMETERS and HR_ORGANIZATION_UNITS.
• PARENT_ITEM_ID / CURRENT_ITEM_ID / LOWEST_ITEM_ID — the assembly and its component within the exploded structure.
• COMPONENT_QUANTITY — quantity of the component per assembly.
• ALTERNATE_DESIGNATOR, OPERATION_SEQ_NUM, CHANGE_NOTICE — bill routing, alternates, and ECO context.
• REVISION / REVISION_LABEL, LIFECYCLE_ID, CURRENT_PHASE_ID — revision and lifecycle state of the component.
• FROM_END_ITEM_UNIT_NUMBER / TO_END_ITEM_UNIT_NUMBER — the end-item unit number range over which the component applies.
• DISABLED_FLAG — derived; equals 2 when FROM_END_ITEM_UNIT_NUMBER is null, otherwise 1 when DISABLE_DATE is set.
• COMPONENT_EFFECTIVE_FROM_DATE / COMPONENT_EFFECTIVE_TO_DATE — derived from EFFECTIVITY_DATE and DISABLE_DATE where no unit-number range applies.
• IMPLEMENTED_FLAG, BASIS_TYPE, ITEM_TYPE — implementation status and type decodes.

The ML2 join keys on the MTL_EFFECTIVITY_CONTROL lookup type (LOOKUP_TYPE = 'MTL_EFFECTIVITY_CONTROL', outer-joined), which is the effectivity control that governs whether the date-based or unit-number-based columns are populated.

Common Use Cases and Queries

The primary use case is the BOM Implemented Items / where-used inquiry, where a planner reviews which assemblies consume a given component and when that usage is effective. A representative query is:

SELECT organization_code, parent_item_id, current_item_id,
       component_quantity, revision, disabled_flag,
       component_effective_from_date, component_effective_to_date
FROM   apps.bom_impl_inquiry_v
WHERE  organization_id = :org_id
ORDER  BY current_level, sort_code;

Typical scenarios include auditing components flagged as disabled or not yet implemented, comparing date-based versus unit-number-based effectivity (driven by MTL_EFFECTIVITY_CONTROL), and reconciling exploded usage against MTL_ITEM_REVISIONS_B lifecycle states. Because the view depends on BOM_SMALL_IMPL_TEMP being populated for the current session, ad-hoc SQL should generally be executed after invoking the BOM inquiry logic, or against a session that has seeded that temporary structure; queries issued independently may return no rows.