Search Results genealogy_object_id

Overview

APPS.WSH_OPSM_ASN_ITEM_DETAILS_V is a shipping-execution view owned by the APPS schema in Oracle E-Business Suite, registered under FND Design Data WSH.WSH_OPSM_ASN_ITEM_DETAILS_V and documented as VALID in both 12.1.1 and 12.2.2. It exposes the item-level detail of an Advance Shipping Notice (ASN) as consumed by Oracle's OPSM (Outbound Product Serialization and Marking) and genealogy flows. In practical terms, the view flattens delivery-detail, container, lot, serial, and date information into a single row per shipped item so that downstream packages do not have to re-join the delivery model.

Its significance is primarily internal rather than end-user facing. The view is a supporting access layer for WSH_OPSM_ASN_BE_PKG (the ASN business-event package), WSH_OPSM_ASN_ITEM_GENEALOGY, and WSH_OPSM_ASN_ITEM_GENEALOGY_V. Because those objects drive ASN generation and genealogy reporting, the view functions as the read interface between the delivery/serial-number tables and OPSM processing logic.

Underlying Base Objects

The ETRM dependency list identifies the following referenced objects, each accessed through an APPS synonym: MTL_LOT_NUMBERS, MTL_PARAMETERS, MTL_SERIAL_NUMBERS_TEMP, WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERY_DETAILS, and WSH_SERIAL_NUMBERS. In functional terms, WSH_DELIVERY_DETAILS supplies the delivery line and organization context; WSH_DELIVERY_ASSIGNMENTS and WSH_SERIAL_NUMBERS provide container and serial assignment data; MTL_LOT_NUMBERS contributes lot attributes such as parent lot and the various date fields; MTL_PARAMETERS supplies organization-level lot/serial control settings; and MTL_SERIAL_NUMBERS_TEMP supports transient serial handling during the ASN build.

Because the referenced entries are synonyms rather than base tables, the view resolves them through the APPS synonym layer, which is a common EBS pattern for cross-module views. The dependency metadata also confirms the view is consumed only by the WSH OPSM objects listed above, reinforcing that it is a purpose-built view rather than a general reporting interface. No public API or OAF page is documented as referencing it directly.

Key Columns

The view exposes twenty columns. The delivery keys — DELIVERY_ID, ORGANIZATION_ID, DELIVERY_ASSIGNMENT_ID, DELIVERY_DETAIL_ID, CONTAINER_INSTANCE_ID, MASTER_CONTAINER_ITEM_ID, and DETAIL_CONTAINER_ITEM_ID — establish the shipping and container hierarchy. LOAD_SEQUENCE_NUMBER provides the planned loading order.

• GENEALOGY_OBJECT_ID (NUMBER) — the column most often searched by users; it is the linking identifier that ties an ASN item line to a genealogy record, and it is the primary reason this view exists in the OPSM ASN flow.
• LOT_NUMBER and PARENT_LOT_NUMBER (VARCHAR2 80) — the lot under shipment and, where applicable, its parent lot for traceability.
• SHIPPED_QUANTITY and SHIPPING_QUANTITY_UOM — quantity shipped and its unit of measure.
• ORIGINATION_DATE, BEST_BY_DATE, RETEST_DATE, EXPIRATION_DATE — lot/serial dating attributes used for shelf-life and quality compliance on the ASN.
• FROM_SERIAL_NUMBER and TO_SERIAL_NUMBER (VARCHAR2 30) — the serial range represented by the row.
• ORGANIZATION_CODE — the human-readable inventory organization identifier.

Common Use Cases and Queries

The view is used to list ASN item detail for a delivery, to reconcile shipped lot/serial information against genealogy records, and to feed ASN business events. A typical query retrieves the genealogy linkage for a delivery:

SELECT delivery_id, delivery_detail_id, lot_number, from_serial_number, to_serial_number, genealogy_object_id FROM apps.wsh_opsm_asn_item_details_v WHERE delivery_id = :p_delivery_id;

A second pattern isolates genealogy objects for a specific organization:

SELECT organization_code, lot_number, genealogy_object_id, shipped_quantity FROM apps.wsh_opsm_asn_item_details_v WHERE genealogy_object_id IS NOT NULL ORDER BY organization_code, lot_number;

Because the view is tied to serialization and genealogy processing, queries should filter on delivery or organization keys and restrict NULL genealogy identifiers where a genealogy-specific result is required. Users should note that most columns are not marked mandatory, so outer joins in the underlying definition can produce partially populated rows; results should be validated against WSH_DELIVERY_DETAILS when full delivery context is needed.