Search Results destination_postal_code

Overview

WSH_DSNO_DELIVERIES_V is an APPS-owned, VALID database view in the Oracle E-Business Suite shipping schema (WSH — Shipping Execution). It belongs to the Delivery Server / Delivery Server Notification (DSNO) integration layer, which extracts delivery and trip information from the shipping tables and formats it for outbound Electronic Data Interchange (EDI) or other external trading-partner message types. In the ETRM documentation for 12.2.2, the view is catalogued with its full DB object metadata, including the DDL definition and the list of referenced base objects. It serves as a denormalized, trading-partner-oriented staging point: rather than exposing the transactional delivery rows directly, it joins delivery headers, delivery legs, trip stops, locations, and ECE trading-partner configuration into a single flat structure keyed by document and trading-partner identifiers.

The view's role in reporting and integration is therefore primarily outbound messaging and interface validation, not operational shipping querying. It is one of a family of WSH views consumed by the Delivery Server and the ECE (E-Commerce Gateway) framework.

Underlying Base Objects

The 12.2.2 metadata documents a substantial set of referenced objects. Delivery data is sourced from WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_LEGS, WSH_DELIVERY_ASSIGNMENTS_V, WSH_DOCUMENT_INSTANCES, and WSH_TRIPS / WSH_TRIP_STOPS. Shipping configuration comes from WSH_CARRIER_SERVICES, WSH_LOCATIONS, and MTL_PARAMETERS. Trading-partner and communication data comes from ECE_TP_HEADERS and ECE_TP_DETAILS, resolved through WSH_ECE_VIEWS_DEF. Customer and party data is drawn from HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_PARTIES, and HZ_PARTY_SITES. Address and organizational context derive from HR_LOCATIONS_ALL and HR_ORGANIZATION_UNITS, with HR_SECURITY and HR_GENERAL providing security and utility functions. The view text applies ORDERED and USE_NL hints with DISTINCT, indicating performance-sensitive joins over high-volume delivery rows.

Key Columns

• DOCUMENT_ID / TRANSACTION_TYPE — the delivery identifier and the derived transaction type; the principal keys for downstream message construction.
• COMMUNICATION_METHOD, TRANSACTION_DATE — constants ('EDI', SYSDATE) stamped onto the outbound record.
• TP_HEADER_ID / TP_DETAIL_ID — trading-partner header and detail identifiers from ECE_TP_HEADERS and ECE_TP_DETAILS.
• DOCUMENT_CODE, TP_TEST_FLAG, TP_DOCUMENT_TYPE, TP_CODE — document and partner classification fields used for routing and testing.
• TP_LOCATION_CODE_EXT and the ADDRESS/contact columns — location code, contact name, address lines, city, state, province, country, postal code, phone, fax, and email.
• TP_HEADER_ATTRIBUTE1–15 and TP_DETAIL_ATTRIBUTE1–5 — descriptive-flexfield values carried through from the trading-partner setup.
• ORGANIZATION_ID, DOCUMENT_STANDARD — owning inventory organization and document standard.

Notably, the view does not expose a dedicated ACTUAL_SHIP_DATE column in the documented DDL excerpt; shipment dates for external reporting are typically obtained from the underlying delivery-leg or delivery-detail objects (for example WSH_DELIVERY_LEGS.ACTUAL_DEPARTURE_DATE) rather than from this DSNO view. Users searching for "actual_ship_date" should be aware of this distinction.

Common Use Cases and Queries

The view is used to generate outbound EDI delivery notifications, to reconcile trading-partner configuration against actual shipments, and to troubleshoot Delivery Server messages. A representative query is:

SELECT document_id,
       transaction_type,
       tp_code,
       document_code,
       tp_document_type,
       organization_id
FROM   apps.wsh_dsno_deliveries_v
WHERE  organization_id = :org_id
AND    transaction_date >= TRUNC(SYSDATE) - 7;

To retrieve shipment dates for external reporting, join the view to delivery legs or details:

SELECT d.document_id,
       l.actual_departure_date
FROM   apps.wsh_dsno_deliveries_v d,
       apps.wsh_delivery_legs l
WHERE  d.document_id = l.delivery_id;

Additional uses include auditing which trading partners received notification for a given delivery, validating that ECE trading-partner attributes are populated before message generation, and comparing DSNO output against the operational delivery record. Because the definition carries DISTINCT and nested-loop hints, queries should be filtered by organization or delivery identifier to avoid full scans.