Results for “sales_order_date”

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

Overview

WSH_OPSM_ASN_ORDERS_SPECIFIC_V is an APPS-owned database view within the Oracle E-Business Suite Shipping Execution (WSH) module, documented as VALID in ETRM for release 12.1.1 and 12.2.2. The view supplies the Oracle Order Management ("OE") source feed used by the Oracle Process Manufacturing / Shipping Execution ASN (Advanced Shipping Notice) reporting and integration layer, specifically the "orders specific" extract consumed by OPSM ASN processing. It flattens the header, line, and Trading Community Architecture (TCA) party/contact model into a single denormalized row per qualifying order line, exposing invoice-to identity, address, and contact attributes alongside sales order identifiers. Because invoice contact detail is carried directly on the row, the view is the natural access point for consumers searching on attributes such as invoice_cont_area_code and its sibling attributes (INVOICE_CONT_NAME, INVOICE_CONT_PHONE_NUMBER, INVOICE_CONT_EMAIL_ADDRESS). Each row is qualified with a literal SOURCE_CODE of 'OE' to distinguish this source from other ASN order feeds.

Underlying Base Objects

The view is defined over fourteen documented base objects, all referenced through APPS synonyms: FND_LANGUAGES, HZ_CONTACT_POINTS, HZ_CUST_ACCOUNTS, HZ_CUST_ACCOUNT_ROLES, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_LOCATIONS, HZ_ORG_CONTACTS, HZ_PARTIES, HZ_PARTY_SITES, HZ_RELATIONSHIPS, OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, and OE_TRANSACTION_TYPES_TL. The driving joins are from OE_ORDER_HEADERS_ALL to OE_ORDER_LINES_ALL (outer-joined on HEADER_ID) to OE_TRANSACTION_TYPES_TL, restricted to the base language via FND_LANGUAGES with INSTALLED_FLAG = 'B'. Customer-side attributes are resolved through HZ_CUST_SITE_USES_ALL and HZ_CUST_ACCT_SITES_ALL, then through HZ_PARTY_SITES, HZ_LOCATIONS, and HZ_PARTIES. Contact data is assembled from HZ_CUST_ACCOUNT_ROLES, HZ_RELATIONSHIPS, HZ_ORG_CONTACTS, and two aliased instances of HZ_CONTACT_POINTS. Most customer joins are outer joins, so an order line is not dropped when party or contact detail is incomplete. The view uses /*+FIRST_ROWS*/ and SELECT DISTINCT, which has implications for execution plans on large volumes.

Key Columns

• SOURCE_CODE — constant 'OE', identifying the originating application.
• SOURCE_HEADER_ID, SALES_ORDER_NUMBER, SALES_ORDER_DATE, ORDER_TYPE_INT, ORDER_CATEGORY_CODE — order identity and classification.
• INVOICE_TO_ORG_ID, INVOICE_TO_CONTACT_ID, INVOICE_ADDRESS_ID — TCA identifiers for the invoice-to organization, contact, and customer account site.
• INVOICE_NAME, INVOICE_CODE_INT — invoice-to party name and site-use location code.
• INVOICE_ADDRESS1..4, INVOICE_CITY, INVOICE_POSTAL_CODE, INVOICE_STATE_INT, INVOICE_PROVINCE_INT, INVOICE_COUNTY, INVOICE_COUNTRY_INT — formatted invoice address attributes.
• INVOICE_CONT_NAME, INVOICE_CONT_JOB_TITLE, INVOICE_CONT_EMAIL_ADDRESS — assembled contact name and role detail.
• INVOICE_CONT_COUNTRY_CODE, INVOICE_CONT_AREA_CODE, INVOICE_CONT_PHONE_NUMBER, INVOICE_CONT_PHONE_EXTN — the decomposed telephone attributes. The column INVOICE_CONT_AREA_CODE is the object most commonly retrieved when users search on the term "invoice_cont_area_code".

Common Use Cases and Queries

Typical uses include populating ASN or shipping documentation with invoice-to contact details, validating that invoice contacts and phone attributes are maintained in TCA, and diagnosing shipping interfaces that fail for want of complete party data. A representative query retrieving the invoice contact area code for a given order is:

• SELECT sales_order_number, invoice_cont_name, invoice_cont_area_code, invoice_cont_phone_number FROM wsh_opsm_asn_orders_specific_v WHERE sales_order_number = :order_number;
• SELECT sales_order_number, invoice_name, invoice_city, invoice_cont_area_code FROM wsh_opsm_asn_orders_specific_v WHERE invoice_cont_area_code IS NOT NULL;
• Join the view to OE_ORDER_HEADERS_ALL or WSH_DELIVERY_DETAILS on SOURCE_HEADER_ID to align ASN extract rows with delivery assignments.

Note that the DISTINCT and outer joins can make the view costly at high cardinality; filtering by SALES_ORDER_NUMBER or SOURCE_HEADER_ID is recommended over full scans.