Search Results parent_container_instance_id




Overview
Explore in AI Chat

Overview

The view WSH_DSNO_UNIQUE_CONTAINERS_V is an APPS-owned database view within the Oracle Shipping Execution (WSH) module. It exists in both Oracle E-Business Suite 12.1.1 and 12.2.2 and carries a VALID status in the data dictionary. Its purpose is to expose the hierarchical relationships between containers (LPNs and nested container structures) that are assigned to deliveries in Shipping Execution.

The view is closely associated with the Distributed Shipping Notification / Delivery Notification (DSNO) functionality used to communicate outbound shipment content to external systems and trading partners. Rather than presenting every delivery detail assignment, the view filters to container-level records and returns a single, de-duplicated row per container instance. It surfaces a self-referencing parent-child hierarchy so that nested packaging structures can be reconstructed by a consumer.

Because it is a view and not a table, it holds no data of its own; it is a query-time projection over the delivery assignment and delivery detail tables. This makes it a convenient read-only interface for reporting, interface programs, and integration extracts that need a container-centric rather than line-centric view of a shipment.

Underlying Base Objects

The documented base objects referenced by the view are:

  • WSH_DELIVERY_ASSIGNMENTS (accessed via a SYNONYM) — aliased in the view text as WDA; supplies the delivery assignment rows that link delivery details to deliveries.
  • WSH_DELIVERY_DETAILS (accessed via a SYNONYM) — aliased in the view text as WDD; supplies the container flag and detail attributes used to qualify container records.

The view text selects from WSH_DELIVERY_ASSIGNMENTS and, for each assignment, correlates to WSH_DELIVERY_DETAILS through a scalar subquery that requires CONTAINER_FLAG = 'Y'. The outer query further restricts to ACTIVE_FLAG = 'Y' assignments.

The hierarchical clause is the distinguishing feature: the query uses START WITH PARENT_DELIVERY_DETAIL_ID IS NULL and CONNECT BY PRIOR DELIVERY_DETAIL_ID = PARENT_DELIVERY_DETAIL_ID. This walks the container tree from its top-level (root) containers downward through nested children, producing one row per container instance in the hierarchy.

Key Columns

  • CONTAINER_INSTANCE_ID — This is the column the user searched for. It is derived from DELIVERY_DETAIL_ID in WSH_DELIVERY_ASSIGNMENTS and therefore identifies the container instance (the delivery detail acting as a container) within the shipment hierarchy.
  • PARENT_CONTAINER_INSTANCE_ID — Derived from PARENT_DELIVERY_DETAIL_ID; identifies the immediate parent container of the current row. For root-level containers this value is NULL, which is precisely the condition used in the START WITH clause.
  • DELIVERY_ID — The delivery to which the container is assigned, taken from WSH_DELIVERY_ASSIGNMENTS. It anchors the container to its parent delivery record.

Collectively these three columns allow a caller to identify each container, locate its parent, and determine the delivery it belongs to, while the underlying query guarantees only active, container-flagged records are returned.

Common Use Cases and Queries

Typical scenarios include reconstructing nested packaging for shipment notifications, reporting on container counts per delivery, and feeding downstream integrations that require container-level rather than line-level detail. A basic listing of unique containers for a delivery:

  • SELECT container_instance_id, parent_container_instance_id, delivery_id FROM apps.wsh_dsno_unique_containers_v WHERE delivery_id = :p_delivery_id;

Retrieving only root-level containers (those with no parent):

  • SELECT container_instance_id, delivery_id FROM apps.wsh_dsno_unique_containers_v WHERE parent_container_instance_id IS NULL AND delivery_id = :p_delivery_id;

Identifying all children of a given container:

  • SELECT container_instance_id FROM apps.wsh_dsno_unique_containers_v WHERE parent_container_instance_id = :p_container_instance_id;

Because the view already applies the active and container-flag filters and resolves the hierarchy, these queries return clean, hierarchy-aware container data suitable for reporting and outbound shipping interfaces in both 12.1.1 and 12.2.2.