Search Results invoice_province_int




Overview
Explore in AI Chat

Overview

The APPS.WSH_OPSM_ASN_ORDERS_SPECIFIC_V view is a shipping-execution reporting object within the Oracle E-Business Suite Order Management and Shipping Execution modules. It belongs to the WSH (Shipping Execution) schema and is registered under the FND Design Data reference WSH.WSH_OPSM_ASN_ORDERS_SPECIFIC_V. The view exposes order-specific invoice and ship-to information in a denormalised, presentation-ready format intended for Advance Shipment Notice (ASN) generation, outbound order extracts, and integration with external logistics or e-commerce systems.

The object is a read-only view with a status of VALID in both Oracle EBS 12.1.1 and 12.2.2. Because it consolidates data from Order Management, Trading Community Architecture (TCA), and reference lookup sources, it eliminates the need for integration developers to hand-code multi-table joins across the HZ and OE schemas. It is commonly consumed by a single flat SELECT ... FROM APPS.WSH_OPSM_ASN_ORDERS_SPECIFIC_V construct, as reflected in the documented query text.

Underlying Base Objects

The view is defined over a set of synonym-backed base objects. The transactional backbone consists of OE_ORDER_HEADERS_ALL (order header attributes such as order number and order category) and OE_ORDER_LINES_ALL (line-level detail), together with OE_TRANSACTION_TYPES_TL for translated order type names. Invoice-to and ship-to party information is sourced from the TCA tables HZ_PARTIES, HZ_CUST_ACCOUNTS, HZ_CUST_ACCOUNT_ROLES, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, and HZ_PARTY_SITES. Address details derive from HZ_LOCATIONS.

Contact data is assembled from HZ_ORG_CONTACTS, HZ_CONTACT_POINTS, and HZ_RELATIONSHIPS. Language-dependent values such as translated order type descriptions are resolved through FND_LANGUAGES. The joins across these tables allow the view to render both internal integer code values and descriptive text side by side.

Key Columns

The view exposes identifiers and descriptive attributes. SOURCE_CODE and SOURCE_HEADER_ID indicate the originating source system and header identifier, supporting multi-source order feeds. SALES_ORDER_NUMBER and SALES_ORDER_DATE identify the commercial order. ORDER_TYPE_INT and ORDER_CATEGORY_CODE classify the order.

Invoice-related columns include INVOICE_TO_ORG_ID, INVOICE_TO_CONTACT_ID, INVOICE_NAME, INVOICE_ADDRESS1 through INVOICE_ADDRESS4, INVOICE_CITY, INVOICE_POSTAL_CODE, INVOICE_COUNTRY_INT, INVOICE_STATE_INT, INVOICE_PROVINCE_INT, and INVOICE_COUNTY. The INVOICE_STATE_INT column is the state or province attribute referenced by searches for "invoice_state_int"; despite the _INT suffix, it is a VARCHAR2(60) holding the value passed through from the TCA location context. Contact columns (INVOICE_CONT_NAME, INVOICE_CONT_JOB_TITLE, INVOICE_CONT_EMAIL_ADDRESS, INVOICE_CONT_COUNTRY_CODE, INVOICE_CONT_AREA_CODE, INVOICE_CONT_PHONE_NUMBER, INVOICE_CONT_PHONE_EXTN) supply the full contact block. SHIP_TO_CONTACT_ID and INVOICE_ADDRESS_ID provide foreign key references back to TCA records.

Common Use Cases and Queries

Typical scenarios include generating ASN payloads for EDI 856 transmission, producing shipping documents with invoice-to address blocks, and reconciling order headers against TCA party data. A common query returns orders filtered by state:

  • SELECT sales_order_number, invoice_name, invoice_city, invoice_state_int FROM apps.wsh_opsm_asn_orders_specific_v WHERE invoice_state_int = 'CA';
  • SELECT source_code, source_header_id, sales_order_number, order_type_int FROM apps.wsh_opsm_asn_orders_specific_v WHERE sales_order_date >= SYSDATE - 30;
  • SELECT invoice_name, invoice_cont_name, invoice_cont_email_address FROM apps.wsh_opsm_asn_orders_specific_v WHERE invoice_to_org_id = :p_org_id;

Because the view already resolves TCA and OE joins, integration code should query it directly rather than reconstructing the underlying relationships, ensuring consistent results with the shipping-execution presentation layer.