Results for “cln_xml_deliveries2_v”

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

Overview

CLN_XML_DELIVERIES2_V is a PL/SQL view owned by the APPS schema within Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the CLN product family — Supply Chain Trading Connector for RosettaNet — which provides the outbound and inbound XML messaging infrastructure used to exchange business documents with trading partners over RosettaNet PIPs. This particular view supplies the deliveries section of the SHIPMENT entity in the OAG SHOW_SHIPMENT_005 XML payload. In practice, the RosettaNet connector queries the view at runtime and maps its columns to the corresponding XML tags, so the view acts as a denormalized, message-shaped projection of Oracle Shipping and Oracle Receivables/HZ data rather than as a general-purpose operational view.

Underlying Base Objects

The view is defined over a substantial set of Shipping (WSH) and Trading Community (HZ/HR) synonyms plus two helper packages. The Shipping tables include WSH_NEW_DELIVERIES, which supplies the delivery header attributes (waybill, freight terms, ship method, LPN count, pickup and dropoff dates) and WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERY_LEGS, WSH_TRIPS, and WSH_TRIP_STOPS, which provide routing, trip, and planned-departure information. Document references such as the bill of lading and packing slip numbers are derived from WSH_DOCUMENT_INSTANCES. Location and address details for the trading-partner side come from WSH_LOCATIONS and HR_LOCATIONS_ALL, while the ship-to customer data is resolved through the HZ model: HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_PARTY_SITES, HZ_PARTIES, HZ_LOCATIONS, HZ_CUST_ACCOUNT_ROLES, and HZ_RELATIONSHIPS. Two packages are referenced: WSH_ECE_VIEWS_DEF, which formats contact area codes and telephone numbers, and WSH_UTIL_CORE, used for supporting conversion logic. The DISTINCT keyword in the SELECT list collapses row duplication produced by the multi-table joins.

Key Columns

The view exposes delivery identification and address columns such as DOCUMENT_CODE (from WSH_NEW_DELIVERIES.NAME), DELIVERY_ID, SHIPPER_NUMBER, WAYBILL_NUMBER, BILL_OF_LADING, and PACKING_SLIP_NUMBER. Destination information is projected through DESTINATION_CODE_INT, DESTINATION_EDI_LOC_CODE, DESTINATION_NAME, DESTINATION_ADDRESS1 through DESTINATION_ADDRESS3, DESTINATION_CITY, DESTINATION_STATE, DESTINATION_POSTAL_CODE, and DESTINATION_COUNTRY_INT, together with contact attributes DESTINATION_CONTACT_FIRST_NAME, DESTINATION_CONTACT_LAST_NAME, DESTINATION_AREA_CODE, and DESTINATION_TELEPHONE. Logistics attributes include NUMBER_OF_LPN, FREIGHT_TERMS_CODE_INT, SHIP_METHOD_CODE_INT, ULTIMATE_DROPOFF_DATE, INITIAL_PICKUP_DATE, and PLANNED_DEPARTURE_DATE. Trading-partner columns (TP_DESCRIPTION, TP_LOCATION_CODE_EXT, TP_ADDRESS1–TP_ADDRESS3) carry the partner-side location and address used to complete the SHIPMENT payload. The fifteen generic ATTRIBUTE1–ATTRIBUTE15 columns, along with ATTRIBUTE_CATEGORY, expose the descriptive-flexfield context of the delivery record for XML extension segments. Notably, an explicit pack_type column is not present in the documented projection; packaging content is represented indirectly through NUMBER_OF_LPN and the document-instance identifiers.

Common Use Cases and Queries

The primary consumer of this view is the RosettaNet connector when constructing the OAG SHOW_SHIPMENT_005 message for an outbound shipment notification. Analysts also use it for shipment message troubleshooting and for reconciliation of delivery data sent to partners. A representative query follows:

• SELECT document_code, delivery_id, waybill_number, destination_name, destination_city, ship_method_code_int, number_of_lpn, planned_departure_date FROM apps.cln_xml_deliveries2_v WHERE delivery_id = :p_delivery_id; — retrieves the message-level attributes for a single delivery.
• SELECT tp_description, tp_location_code_ext, tp_address1, tp_city FROM apps.cln_xml_deliveries2_v WHERE document_code = :p_doc; — inspects the trading-partner location data that populates the partner segment of the payload.
• SELECT attribute_category, attribute1, attribute2 FROM apps.cln_xml_deliveries2_v WHERE waybill_number = :p_waybill; — exposes flexfield context used in XML extension elements.

Because the view is DISTINCT and built on multiple HZ and WSH joins, queries should always be filtered by delivery identifier or document code to avoid full scans across the joined synonym set.