Results for “bom_reference_designators_v”

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

Overview

BOM_REFERENCE_DESIGNATORS_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes component reference designator data from Bills of Material, presenting the electrical or mechanical designators (such as R12, C34, U7) that engineers assign to components placed on an assembly. Reference designators are stored against the component assignment on a bill, and this view flattens the association between a reference designator and the specific bill component it belongs to, returning only the currently effective row for each designator at a point in time.

The view is read-only and is intended for inquiry, reporting, and integration rather than transaction entry. Because it resolves effectivity internally, consumers do not need to reconstruct the effectivity logic themselves when querying current designator assignments.

Underlying Base Objects

The view is defined over two documented objects: BOM_INVENTORY_COMPONENTS (itself a view) and BOM_REFERENCE_DESIGNATORS (referenced through a synonym). The join key is COMPONENT_SEQUENCE_ID, which uniquely identifies a component occurrence on a bill and links the designator to its parent component record. The view aliases BOM_INVENTORY_COMPONENTS twice (BIC1 and BIC2) to reconcile component identity across effectivity states using the DECODE on IMPLEMENTATION_DATE and OLD_COMPONENT_SEQUENCE_ID. It filters out rows where ACD_TYPE is 3 (the change-cancelled designation) via NVL(ACD_TYPE,1) <> 3, ensuring only live designators are returned. A correlated subquery selects the MAX(EFFECTIVITY_DATE) for each designator, so the view yields the most recent effective occurrence.

Key Columns

• ROW_ID — the ROWID of the source BOM_REFERENCE_DESIGNATORS row, useful for drills and uniqueness.
• COMPONENT_SEQUENCE_ID — the component occurrence identifier connecting the designator to its bill component; the primary correlation key.
• COMPONENT_REFERENCE_DESIGNATOR — the actual designator value (e.g., R12, C34).
• IMPLEMENTATION_DATE — the date the component implementation takes effect, used to resolve which component version applies.
• REF_DESIGNATOR_COMMENT — free-text annotation on the designator.
• ACD_TYPE — the change type indicator governing whether the designator is active.
• CHANGE_NOTICE — the ECO or change notice associated with the designator.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — descriptive flexfield columns for customer-defined extensions.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and REQUEST_ID/PROGRAM_ID/PROGRAM_APPLICATION_ID/PROGRAM_UPDATE_DATE for concurrent program traceability.

Common Use Cases and Queries

Typical uses include BOM designator reports, component placement extracts for manufacturing or CAD integration, and reconciliation of designators against engineering change orders. The following query returns all current designators for a given component sequence:

SELECT component_reference_designator, implementation_date, change_notice, ref_designator_comment
FROM apps.bom_reference_designators_v
WHERE component_sequence_id = :p_component_sequence_id;

To list designators tied to a specific change notice:

SELECT component_sequence_id, component_reference_designator, change_notice
FROM apps.bom_reference_designators_v
WHERE change_notice = :p_change_notice;

Because COMPONENT_SEQUENCE_ID is the driving key, joins to BOM_INVENTORY_COMPONENTS or BOM_BILL_OF_MATERIALS should use that column, and callers should filter by IMPLEMENTATION_DATE to select a specific effectivity point.