Results for “genealogy_object_id”

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

Overview

The WSH_OPSM_ASN_ITEM_GENEALOGY_V view is a Shipping Execution (WSH) reporting object owned by the APPS schema. It exposes Advance Ship Notice (ASN) item genealogy information — the parent-child relationships between lots, serial numbers, and inventory items — in a flattened, query-friendly format. In Oracle EBS 12.1.1 and 12.2.2, this view supports supply chain visibility and integration scenarios where downstream systems (or OPSM-related processes) need to trace the lineage of shipped items across deliveries, delivery details, and organizations.

Internally, the view resolves genealogy by invoking a pipelined table function, WSH_OPSM_ASN_ITEM_GENEALOGY.GET_GENEALOGY, which returns rows for each genealogical object associated with an ASN item. This design allows the view to abstract the recursive or hierarchical traversal of item relationships behind a standard relational interface, making it accessible to reports, concurrent programs, and integrations without requiring callers to understand the underlying package logic.

The view is particularly relevant when troubleshooting or reporting on the OPSM_INTEGRATED_FLAG column (aliased from ITEMG.CROSS_REFERENCE), which indicates integration linkage for the item within the ASN genealogy context.

Underlying Base Objects

The ETRM 12.2.2 metadata documents the following referenced base objects for this view:

• WSH_OPSM_ASN_ITEM_DETAILS_V (VIEW) — The source of item-level detail. The view selects from an inline subquery over this view (ITEMD), supplying GENEALOGY_OBJECT_ID, DELIVERY_ID, DELIVERY_DETAIL_ID, ORGANIZATION_ID, and the FROM_SERIAL_NUMBER/TO_SERIAL_NUMBER range used as function input.
• WSH_OPSM_ASN_ITEM_GENEALOGY (PACKAGE) — Provides the pipelined function GET_GENEALOGY, which generates the genealogy rows joined to each item detail record.
• SYS_PLSQL_34FF91EB_306_1 (TYPE) — The system-generated PL/SQL type that defines the shape of the collection returned by the GET_GENEALOGY function and consumed in the TABLE(...) clause.

The view therefore represents a join between item details and the dynamically generated genealogy set, keyed on the item detail's genealogy object, delivery, and organization context.

Key Columns

The view projects a combination of item-detail columns (from the ITEMD alias) and genealogy columns (from the ITEMG alias). Notable columns include:

• ITEM_NUMBER, ITEM_UOM — Inventory item identifier and primary unit of measure.
• LOT_NUMBER, SERIAL_NUMBER — Lot and serial identifiers of the genealogical item, with PARENT_LOT_NUMBER and PARENT_SERIAL_NUMBER establishing the parent side of the relationship.
• INVENTORY_ITEM_ID, PARENT_INVENTORY_ITEM_ID — Numeric keys for the item and its parent.
• DELIVERY_ID, DELIVERY_DETAIL_ID — Links to the shipping delivery and its detail line.
• GENEALOGY_OBJECT_ID, ORGANIZATION_ID, ORGANIZATION_CODE — Genealogy object identifier and inventory organization context.
• RLEVEL, RPARENT_LEVEL — Hierarchy level of the item and its parent within the genealogy tree.
• OPSM_INTEGRATED_FLAG — Aliased from CROSS_REFERENCE; the integration indicator of interest.
• Date and control attributes: ORIGINATION_DATE, BEST_BY_DATE, RETEST_DATE, EXPIRATION_DATE, LOT_CONTROL_CODE, SERIAL_NUMBER_CONTROL_CODE, SERIAL_TYPE, JOB_NAME.

Common Use Cases and Queries

Typical uses include ASN genealogy reporting, traceability and recall investigations, and OPSM integration validation. A representative query filtering on the integration flag is shown below:

• Retrieve genealogy for a delivery where items are OPSM-integrated:

  SELECT item_number, item_uom, lot_number, serial_number,
         parent_lot_number, parent_serial_number,
         delivery_id, delivery_detail_id,
         rlevel, rparent_level, opsm_integrated_flag
  FROM   apps.wsh_opsm_asn_item_genealogy_v
  WHERE  delivery_id = :p_delivery_id
  AND    opsm_integrated_flag = 'Y';

• Trace full parentage for a given serial number:

  SELECT item_number, lot_number, serial_number,
         parent_lot_number, parent_serial_number, rlevel
  FROM   apps.wsh_opsm_asn_item_genealogy_v
  WHERE  serial_number = :p_serial_number
  ORDER  BY rlevel;

• Audit lot/serial control attributes alongside genealogy:

  SELECT item_number, lot_number, serial_number,
         lot_control_code, serial_number_control_code,
         expiration_date, best_by_date
  FROM   apps.wsh_opsm_asn_item_genealogy_v
  WHERE  organization_code = :p_org;

Because the view is a pipelined-function-backed construct, queries benefit from filtering on DELIVERY_ID or GENEALOGY_OBJECT_ID to constrain the function's input set and improve performance.