Search Results cust_acct_site_status




Overview
Explore in AI Chat

Overview

WSH_CUSTOMER_SITES_V is a shipping-execution view owned by the APPS schema in Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. The view consolidates customer account, party, party-site, and site-use attributes into a single denormalized row per customer account site use. Its primary function is to provide Shipping Execution, and any dependent reporting or integration layer, with a flat, pre-joined representation of a ship-to or bill-to address without requiring the caller to traverse the multiple HZ tables that constitute the Trading Community Architecture (TCA) customer model.

Rather than storing data itself, the view exposes the joined result set of five TCA entities. It is commonly referenced by shipping, order management, and custom reporting code where a concise customer address identifier and description are required alongside the internal identifiers used by the shipping modules.

Underlying Base Objects

Per the documented metadata, WSH_CUSTOMER_SITES_V is defined over five base objects, each appearing as a SYNONYM in the APPS schema:

Joins are established on CUST_ACCOUNT_ID, PARTY_ID, PARTY_SITE_ID, and CUST_ACCT_SITE_ID. Notably, the join between site uses and account sites enforces multi-org consistency through NVL(HCSU.ORG_ID, -999) = NVL(HCAS.ORG_ID, -999), so only site uses whose org context matches the parent account site are returned.

Key Columns

  • CUSTOMER_ID / CUSTOMER_NUMBER / CUSTOMER_NAME — the customer account identifier, account number, and party name (truncated to 50 bytes).
  • ADDRESS_ID — the CUST_ACCT_SITE_ID, the key identifier for the customer address.
  • SITE_USE_ID and SITE_USE_CODE — the site-use surrogate key and its purpose code (for example, SHIP_TO, BILL_TO).
  • PRIMARY_FLAG — indicates whether the site use is the primary one for its purpose.
  • ORG_ID — the operating unit context carried from the account site, essential for multi-org filtering.
  • Status columns (CUSTOMER_STATUS, CUST_ACCT_SITE_STATUS, SITE_USE_STATUS) — enable active/inactive filtering at each level of the hierarchy.
  • DUNS_NUMBER, PARTY_SITE_NUMBER, LOCATION_ID, TP_HEADER_ID, ORIG_SYSTEM_REFERENCE, TP_LOCATION_CODE_EXT, GSA_INDICATOR — supporting identifiers and flags used in EDI/trading-partner and location processing.

Common Use Cases and Queries

The view is typically queried to resolve a ship-to address for a sales order or delivery, to populate shipping documentation, or to validate that a customer address is active before a shipment is confirmed. Multi-org environments must filter on ORG_ID. A representative query selecting active primary ship-to sites is shown below.

SELECT customer_id, customer_number, customer_name, address_id, site_use_id, site_use_code, location, primary_flag, org_id FROM wsh_customer_sites_v WHERE site_use_code = 'SHIP_TO' AND site_use_status = 'A' AND cust_acct_site_status = 'A' AND customer_status = 'A' AND org_id = :p_org_id ORDER BY customer_name;

Because the view unions no data and performs inner joins, it returns only customer accounts that have a matching party site and account site use; customers without an address at any level are excluded. This characteristic makes the view suitable for shipping lookups, where an address must exist, but unsuitable as a master customer listing.