Search Results rparent_level

Overview

APPS.WSH_OPSM_ASN_ITEM_GENEALOGY_V is a reporting and integration view within the Oracle E-Business Suite Warehouse Management (WSH) and Order Processing / Shipping Management (OPSM) modules. It exposes the item genealogy hierarchy associated with Advance Shipping Notice (ASN) delivery details, allowing consumers to traverse parent-child relationships between lots, serial numbers, and inventory items that flow through a shipment. In Oracle EBS 12.1.1 and 12.2.2, this view serves as the presentation layer over the packaged genealogy logic held in WSH_OPSM_ASN_ITEM_GENEALOGY, translating a pipelined table function into a relational result set that can be queried with standard SQL.

The view is particularly relevant to users searching on opsm_integrated_flag, which is exposed here as a renamed alias. The flag communicates whether a given genealogy record has been integrated with the OPSM (Oracle Process / Product Serialization Management) tracking substrate, giving downstream reporting and external integrations a straightforward indicator of consolidation status.

Underlying Base Objects

The documented metadata identifies three base objects on which the view depends:

• WSH_OPSM_ASN_ITEM_DETAILS_V (VIEW) — Supplies the driving rowset, aliased as ITEMD, providing GENEALOGY_OBJECT_ID, DELIVERY_ID, DELIVERY_DETAIL_ID, ORGANIZATION_ID, and the FROM_SERIAL_NUMBER / TO_SERIAL_NUMBER range boundaries.
• WSH_OPSM_ASN_ITEM_GENEALOGY (PACKAGE) — Contributes the pipelined function get_genealogy, invoked via the TABLE() operator, which expands the supplied range into the flattened parent-child genealogy rows aliased as ITEMG.
• SYS_PLSQL_34FF91EB_306_1 (TYPE) — The system-generated SQL type that defines the row shape returned by the pipelined function and consumed during the correlated TABLE() join.

The view is therefore a lightweight wrapper: it joins the ASN item detail view to the pipelined genealogy function and renames the CROSS_REFERENCE column to OPSM_INTEGRATED_FLAG for clarity in reporting contexts.

Key Columns

• OPSM_INTEGRATED_FLAG — Aliased from ITEMG.CROSS_REFERENCE; indicates OPSM integration status for the genealogy node.
• GENEALOGY_OBJECT_ID — Identifier linking the detail row to the genealogy structure.
• DELIVERY_ID / DELIVERY_DETAIL_ID — Shipment and shipment-line identifiers anchoring the record within the ASN.
• ITEM_NUMBER, ITEM_UOM, INVENTORY_ITEM_ID — Item identity and unit of measure.
• SERIAL_NUMBER, LOT_NUMBER — The specific serial or lot controlled instance represented by the row.
• RLEVEL / RPARENT_LEVEL — Recursion depth of the node and its parent, enabling hierarchy reconstruction.
• PARENT_LOT_NUMBER, PARENT_SERIAL_NUMBER, PARENT_INVENTORY_ITEM_ID — Parent references for upward traversal.
• ORIGINATION_DATE, BEST_BY_DATE, RETEST_DATE, EXPIRATION_DATE — Lifecycle dates propagated through the genealogy.
• LOT_CONTROL_CODE, SERIAL_NUMBER_CONTROL_CODE, SERIAL_TYPE, JOB_NAME, ORGANIZATION_CODE — Control attributes and context for the node.

Common Use Cases and Queries

Typical scenarios include tracing the full lot/serial ancestry of items shipped on an ASN, auditing which genealogy records have been OPSM-integrated, and feeding serialization or compliance reporting.

Example — list integration status for a delivery:

SELECT delivery_id,
       delivery_detail_id,
       item_number,
       serial_number,
       lot_number,
       rlevel,
       opsm_integrated_flag
FROM   apps.wsh_opsm_asn_item_genealogy_v
WHERE  delivery_id = :p_delivery_id
ORDER  BY rlevel, serial_number;

Example — isolate nodes pending integration:

SELECT delivery_id, serial_number, lot_number
FROM   apps.wsh_opsm_asn_item_genealogy_v
WHERE  opsm_integrated_flag IS NULL
   OR  opsm_integrated_flag = 'N';