Search Results cs_cp_audit




Overview
Explore in AI Chat

Overview

HZ_CUST_ACCT_SITES_ALL is the Receivables (AR) customer account site table in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores every customer account site across all operating units, making it the transactional address layer that links a customer account (HZ_CUST_ACCOUNTS) to a physical location (HZ_PARTY_SITES). Each row represents one address assigned to one customer account and is qualified by ORG_ID, reflecting its multi-org design.

In the Trading Community Architecture (TCA) model, this table sits between party-level address data and the operational site uses that drive transactions. Order Management, Receivables, Advanced Pricing, Service, and Projects all resolve a bill-to, ship-to, or remit-to location through this table. From a Data Vault modeling perspective, the heuristic classification is a hub: CUST_ACCT_SITE_ID is the durable business key, with HZ_CUST_ACCOUNTS, HZ_PARTY_SITES, and RA_TERRITORIES acting as referenced hubs or dimensions.

Key Information Stored

The surrogate primary key is CUST_ACCT_SITE_ID, enforced by HZ_CUST_ACCT_SITES_PK. Business-key candidates are defined by three unique indexes: HZ_CUST_ACCT_SITES_U1 (CUST_ACCT_SITE_ID), HZ_CUST_ACCT_SITES_U2 (ORIG_SYSTEM_REFERENCE, ORG_ID), and HZ_CUST_ACCT_SITES_U3 (TP_HEADER_ID).

  • CUST_ACCT_SITE_ID — surrogate primary key for the site.
  • CUST_ACCOUNT_ID — foreign key to HZ_CUST_ACCOUNTS, the owning customer account.
  • PARTY_SITE_ID — foreign key to HZ_PARTY_SITES, the underlying party address.
  • TERRITORY_ID — foreign key to RA_TERRITORIES, the associated territory.
  • ORG_ID — operating unit that owns the site row (multi-org partitioning).
  • ORIG_SYSTEM_REFERENCE — source-system identifier for imported or legacy sites (U2).
  • TP_HEADER_ID — trading-partner header identifier (U3).
  • STATUS — active/inactive state of the site.
  • BILL_TO_FLAG, SHIP_TO_FLAG, MARKET_FLAG, KEY_ACCOUNT_FLAG — usage indicator flags.
  • CUSTOMER_CATEGORY_CODE — customer classification at the site level.
  • ADDRESS_TEXT, TERRITORY, TRANSLATED_CUSTOMER_NAME — descriptive address and territory attributes.
  • LANGUAGE — language of the address text.
  • OBJECT_VERSION_NUMBER — optimistic locking control column.

Common Use Cases and Queries

The table is most commonly queried to resolve the site-level addresses used on a transaction. Because Order Management header acknowledgements reference it directly (OE_HEADER_ACKS.SHIP_TO_ADDRESS_ID, SOLD_TO_ADDRESS_ID, and INVOICE_ADDRESS_ID all point to CUST_ACCT_SITE_ID), a typical query joins OE_HEADER_ACKS to this table to obtain the actual ship-to, sold-to, and invoice address for an acknowledgement.

Representative patterns include:

  • Listing all sites for a customer: SELECT cust_acct_site_id, party_site_id, status FROM hz_cust_acct_sites_all WHERE cust_account_id = :p_account AND org_id = :p_org;
  • Identifying bill-to sites: filter on BILL_TO_FLAG = 'Y'.
  • Resolving addresses on a Receivables transaction by joining RA_CUSTOMER_TRX_ALL.BILL_TO_ADDRESS_ID or SHIP_TO_ADDRESS_ID to CUST_ACCT_SITE_ID and then to HZ_PARTY_SITES for the location details.
  • Cross-validating Order Management header acknowledgements against TCA site data using OE_HEADER_ACKS.
  • Reporting site usage across operating units by grouping on ORG_ID and STATUS.

Related Objects

HZ_CUST_ACCT_SITES_ALL is a heavily referenced hub. The most significant dependent objects and join columns are: