Results for “tp_city”

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

Overview

CLN_XML_DELIVERIES_V is an Oracle E-Business Suite (EBS) 12.1.1 / 12.2.2 database view owned by the APPS schema, with a status of VALID. It belongs to the CLN product family — Supply Chain Trading Connector for RosettaNet — which provides the messaging and XML translation layer that allows EBS to exchange standardized business documents with trading partners via the RosettaNet framework. This particular view assembles the deliveries section of the OAG SHOW_SHIPMENT_005 XML payload, the outbound message used to communicate shipment and delivery details to a partner.

In ETRM/connector terms, the view acts as a denormalized, EDI/XML-ready projection of the underlying Oracle Shipping (WSH) and Trading Community (HZ) data. Rather than emitting raw internal IDs and codes, it resolves and formats the values needed by the OAG schema, including location codes, contact names, addresses, carrier/transport details, and shipper waybill references. It is consumed by the RosettaNet connector when building the SHOW_SHIPMENT_005 message, and is equally usable for ad-hoc queries and custom reports that need shipment delivery detail in an external-facing layout.

Because the view embeds calls to the WSH_ECE_VIEWS_DEF and WSH_UTIL_CORE packages and references location, address, and party data, it is read-only in nature and intended for querying, not for transactional manipulation.

Underlying Base Objects

The view is defined over a set of shipping, location, and party synonyms, joined to resolve the delivery header, its stops, contacts, and destination/shipper addresses. Documented base objects include:

• WSH_NEW_DELIVERIES — the delivery header, supplying document code, delivery ID, waybill, freight terms, ship method, LPN count, pickup/dropoff dates, and the ATTRIBUTE1–15 columns.
• WSH_DELIVERY_ASSIGNMENTS and WSH_DOCUMENT_INSTANCES — provide sequence references used for bill of lading and packing slip numbers.
• WSH_DELIVERY_LEGS, WSH_TRIPS, WSH_TRIP_STOPS — supply transport/planned departure dates and trip-stop context.
• WSH_LOCATIONS and HR_LOCATIONS_ALL — resolve trading-partner location codes and addresses (TP_DESCRIPTION, TP_ADDRESS, TP_LOCATION_CODE_EXT).
• HZ_LOCATIONS, HZ_PARTIES, HZ_PARTY_SITES, HZ_RELATIONSHIPS, HZ_CUST_ACCOUNTS, HZ_CUST_ACCOUNT_ROLES, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL — the Trading Community model, used to derive destination name, address and contact parties.
• WSH_ECE_VIEWS_DEF (package) — provides helper functions such as GET_CONT_AREA_CODE and GET_CONT_PHONE_NUMBER for contact telephone formatting.
• WSH_UTIL_CORE (package) — utility routines used in the join logic.

Key Columns

Columns are aliased to match the OAG SHOW_SHIPMENT_005 field names. Note that carrier_name_int, the term that prompted this search, is not exposed as a discrete column in this view's SELECT list; carrier/transport context is instead reflected through the ship method, freight terms, trip, and leg columns below.

• DOCUMENT_CODE (WND.NAME) — delivery name/document reference.
• DELIVERY_ID — internal delivery identifier, also aliased as SHIPPER_NUMBER.
• DESTINATION_CODE_INT, DESTINATION_EDI_LOC_CODE — destination location and EDI location codes.
• DESTINATION_NAME, DESTINATION_ADDRESS1–3, DESTINATION_CITY/STATE/POSTAL_CODE/COUNTRY_INT — resolved destination address.
• DESTINATION_CONTACT_LAST_NAME / FIRST_NAME, DESTINATION_AREA_CODE, DESTINATION_TELEPHONE — destination contact detail.
• WAYBILL_NUMBER, BILL_OF_LADING, PACKING_SLIP_NUMBER — transport/logistics references.
• FREIGHT_TERMS_CODE_INT, SHIP_METHOD_CODE_INT, NUMBER_OF_LPN — shipping method and load-unit counts.
• ULTIMATE_DROPOFF_DATE, INITIAL_PICKUP_DATE, PLANNED_DEPARTURE_DATE — milestone dates.
• ATTRIBUTE1–15 — flex/descriptive attributes passed through from the delivery.
• TP_DESCRIPTION, TP_LOCATION_CODE_EXT, TP_ADDRESS1–3 — trading-partner location block.

Common Use Cases and Queries

Typical uses include troubleshooting outgoing SHOW_SHIPMENT_005 messages, validating destination and trading-partner codes, and building shipment reconciliation reports.

• Retrieve delivery details for a specific delivery ID and validate the destination address and contact.
• Confirm transport references (waybill, bill of lading, packing slip) for a delivery before the XML message is generated.
• Audit trading-partner location codes resolved by the connector.

Sample query:

SELECT document_code, delivery_id, shipper_number, waybill_number, bill_of_lading, destination_name, destination_city, destination_country_int, ship_method_code_int, planned_departure_date FROM apps.cln_xml_deliveries_v WHERE delivery_id = :p_delivery_id;

Because the view applies DISTINCT and joins across many transactional and reference tables, queries should be filtered by delivery ID or date to avoid excessive result sets.