Results for “tp_location_code_ext”

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

Overview

WSH_CUSTOMER_LOCATIONS_V is a shipping-execution view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a consolidated, denormalized picture of customer locations that have been registered as shipping locations, joining Oracle Shipping Execution location data in WSH_LOCATIONS to the Oracle Trading Community Architecture (TCA) customer model in the HZ tables. The view is read-only and is intended primarily for inquiry, reporting, and integration rather than transactional processing.

It serves as the customer-facing counterpart to the internal shipping location tables, exposing the ship-to and bill-to style locations associated with a customer account, site use, and party site. Because it resolves the foreign keys between shipping, customer, and party entities, it is frequently used in outbound interfaces, BI Publisher reports, and custom forms that must display a human-readable customer location without traversing the TCA schema manually. The view is restricted to locations whose LOCATION_SOURCE_CODE is 'HZ', meaning only locations originating from the TCA model appear.

Underlying Base Objects

The view is defined over six base objects, all accessed through APPS synonyms:

• WSH_LOCATIONS — the driving shipping location table, supplying WSH_LOCATION_ID, UI_LOCATION_CODE, address lines, city, postal code, country, state, province, county, SOURCE_LOCATION_ID, and INACTIVE_DATE.
• HZ_PARTIES — supplies the party name (truncated to 50 characters) and DUNS_NUMBER.
• HZ_CUST_ACCOUNTS — supplies CUST_ACCOUNT_ID, the customer name/number, and the account STATUS.
• HZ_CUST_ACCT_SITES_ALL — supplies the account site, addressed via CUST_ACCT_SITE_ID, TP_HEADER_ID, ORIG_SYSTEM_REFERENCE, ECE_TP_LOCATION_CODE, ORG_ID, and site status.
• HZ_CUST_SITE_USES_ALL — supplies SITE_USE_ID, SITE_USE_CODE, LOCATION, GSA_INDICATOR, STATUS, and PRIMARY_FLAG.
• HZ_PARTY_SITES — supplies PARTY_SITE_NUMBER and links the party to the shipping location.

The joins enforce consistency across the party, account, site, and site-use hierarchy. The ORG_ID join uses NVL on both sides (defaulting to -999) so that records with null organization identifiers still align between the account site and site use tables.

Key Columns

• WSH_LOCATION_ID — primary identifier of the shipping location.
• UI_LOCATION_CODE and UI_SHORT_LOCATION_CODE — display codes; the short code concatenates LOCATION_CODE with city or the first 60 bytes of ADDRESS1.
• ADDRESS1–ADDRESS4, CITY, STATE, PROVINCE, COUNTY, POSTAL_CODE, COUNTRY — the full postal address.
• CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_NUMBER, CUSTOMER_STATUS — the customer account context.
• ADDRESS_ID, SITE_USE_ID, SITE_USE_CODE, PRIMARY_FLAG, SITE_USE_STATUS — the account site and site-use attributes.
• ORG_ID — the operating unit context, essential for multi-org reporting.
• INACTIVE_DATE, CUST_ACCT_SITE_STATUS — lifecycle and status indicators.

Common Use Cases and Queries

Typical usage includes locating valid ship-to addresses for a customer, driving shipping labels and manifests, and populating LOVs. The following query lists active primary ship-to locations for a customer:

• SELECT wsh_location_id, ui_location_code, customer_name, address1, city, state, postal_code FROM wsh_customer_locations_v WHERE customer_id = :p_customer_id AND site_use_code = 'SHIP_TO' AND primary_flag = 'Y' AND inactive_date IS NULL;
• Reporting by operating unit: SELECT org_id, COUNT(*) FROM wsh_customer_locations_v GROUP BY org_id;
• Integration lookups by external reference: SELECT wsh_location_id, orig_system_reference FROM wsh_customer_locations_v WHERE orig_system_reference = :p_ref;