Search Results contact_area_code




Overview
Explore in AI Chat

Overview

WSH_OPSM_ASN_DELIVERIES_V is a Shipping Execution (WSH) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes Advance Shipment Notice (ASN) delivery information in an outbound-facing, integration-ready format, intended for consumption by external systems, transportation management platforms, or the Oracle Transportation Management (OTM) / Order Promising and Shipping Management (OPSM) interfaces. The name prefix "OPSM" indicates its role in the OPSM data exchange layer, while the WSH naming ties it to the Shipping Execution product.

The view consolidates warehouse (ship-from) and destination (ship-to) details into a single flattened row per delivery, including addresses, contacts, and telephone details. Its columns are aliased with suffixes such as "_INT" (internal) and destination-oriented names, which is characteristic of views designed for outbound XML or flat-file message generation rather than interactive reporting. The view is defined with a DISTINCT and a --+ RULE hint, reflecting its original design for rule-based (pre-CBO) query optimization.

Underlying Base Objects

The view is defined over a substantial set of Shipping, Human Resources, and Trading Community (HZ) base objects. Documented referenced objects include:

The inclusion of AR_CUSTOMERS_ACTIVE_V is directly relevant to searches for that object: the ASN delivery view resolves ship-to and customer context through AR_CUSTOMERS_ACTIVE_V, which surfaces active customer accounts. HR_SECURITY is applied to restrict people-based contact data according to the running user's security profile.

Key Columns

  • TRANSACTION_DATE — populated as SYSDATE, providing the message generation timestamp.
  • DOCUMENT_CODE / DELIVERY_NAME / DELIVERY_ID — identify the delivery (ASN document) and its organization.
  • ORGANIZATION_ID / ORGANIZATION_CODE — warehouse organization identity.
  • WAREHOUSE_* columns — warehouse location ID, name, and address components (ADDRESS1–4, CITY, STATE, POSTAL_CODE, COUNTRY).
  • WAREHOUSE_CONTACT_* — warehouse contact name, email, and phone (from PER_ALL_PEOPLE_F plus PER_PHONES where PHONE_TYPE = 'W1').
  • DESTINATION_* columns — ship-to party name (truncated via SUBSTRB to 50 chars), address, city, state, province, country, and postal code.
  • DESTINATION_CONT_* columns — destination contact ID, name, email, phone number, area code, and country code.
  • DESTINATION_CODE_INT — internal location code for the destination.

Common Use Cases and Queries

Typical scenarios include generating ASN messages for external logistics providers, reconciling warehouse-to-customer shipments, and validating contact/address data integrity before integration. Because the view resolves AR_CUSTOMERS_ACTIVE_V and HZ objects, it is also useful when investigating customer ship-to setups referenced by deliveries.

Sample query:

SELECT delivery_name, organization_code, warehouse_name, destination_name, destination_city, destination_country_int, destination_cont_name, destination_cont_phone_number
FROM apps.wsh_opsm_asn_deliveries_v
WHERE organization_id = :p_org_id;

A second common pattern filters by a specific delivery for message debugging:

SELECT delivery_id, document_code, destination_name, destination_address1, destination_postal_code
FROM apps.wsh_opsm_asn_deliveries_v
WHERE delivery_id = :p_delivery_id;

Because the query carries a DISTINCT and a RULE hint, performance is generally acceptable for single-delivery lookups but should be assessed against delivery volumes when used in set-based extracts.