Search Results hz_cust_acct_sites_u1

Overview

HZ_CUST_ACCT_SITES_ALL is a core Trading Community Architecture (TCA) table owned by the AR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores all customer account sites across all operating units. A customer account site is an address, associated with a customer account, at which the deploying company conducts business with its customers. A single customer account may have multiple customer account sites, and those sites may belong to multiple operating units, with the ORG_ID column providing the multi-org partitioning. The table is registered in FND Design Data as AR.HZ_CUST_ACCT_SITES_ALL and is physically stored in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10.

From a dimensional modeling perspective, the mined Data Vault classification heuristic identifies this table as a hub. That classification is a modeling suggestion: the table carries a single-column surrogate primary key, HZ_CUST_ACCT_SITES_PK on CUST_ACCT_SITE_ID, and participates in a dense web of foreign-key relationships, which is characteristic of a hub entity in a Data Vault design.

Key Information Stored

The table defines 76 documented columns. The most significant are listed below.

• CUST_ACCT_SITE_ID — NUMBER(15), the surrogate primary key of the table and the single column of unique index HZ_CUST_ACCT_SITES_U1. This is the identifier referenced by nearly every downstream consumer of account-site data.
• CUST_ACCOUNT_ID — NUMBER(15), foreign key to HZ_CUST_ACCOUNTS, identifying the customer account to which the site belongs.
• PARTY_SITE_ID — NUMBER(15), foreign key to HZ_PARTY_SITES, linking the account site to the underlying party site address record.
• ORIG_SYSTEM_REFERENCE and ORG_ID — together form unique index HZ_CUST_ACCT_SITES_U2, providing the business-key candidate for the source-system reference within an operating unit.
• TP_HEADER_ID — unique index HZ_CUST_ACCT_SITES_U3, used in third-party and EDI-related processing.
• STATUS — lifecycle state of the account site; heavily used in conjunction with CUST_ACCOUNT_ID in the non-unique indexes N2 and N3.
• BILL_TO_FLAG, SHIP_TO_FLAG, and MARKET_FLAG — role indicators that, with CUST_ACCOUNT_ID and STATUS, drive indexes HZ_CUST_ACCT_SITES_N2 and HZ_CUST_ACCT_SITES_N3.
• ECE_TP_LOCATION_CODE — indexed by non-unique index HZ_CUST_ACCT_SITES_N4, supporting EDI and e-commerce location identification.
• TERRITORY_ID — foreign key to RA_TERRITORIES, along with TERRITORY and SERVICE_TERRITORY_ID for territory assignment.
• CUSTOMER_CATEGORY_CODE, KEY_ACCOUNT_FLAG, PRIMARY_SPECIALIST_ID, and SECONDARY_SPECIALIST_ID — classification and assignment attributes.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the TCA APIs.
• Standard WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — plus the ATTRIBUTE1–20 and GLOBAL_ATTRIBUTE1–20 descriptor flexfields.

Common Use Cases and Queries

Typical reporting and integration scenarios resolve a customer account to its sites, or resolve a site to the bill-to and ship-to roles used by downstream transactions. A representative query joining the account and party-site layers is:

• SELECT cas.cust_acct_site_id, cas.cust_account_id, cas.party_site_id, cas.status, cas.bill_to_flag, cas.ship_to_flag FROM hz_cust_acct_sites_all cas WHERE cas.cust_account_id = :cust_account_id AND cas.status = 'A';

Because ORG_ID participates in the business key, multi-org aware queries should filter or join through the operating unit context. Common reporting use cases include identifying valid bill-to sites for invoicing, locating ship-to sites for order entry, auditing sites in a given territory, and validating ORIG_SYSTEM_REFERENCE uniqueness before loading interface data such as RA_INTERFACE_LINES_ALL. The non-unique indexes on CUST_ACCOUNT_ID with STATUS and the BILL_TO_FLAG or SHIP_TO_FLAG columns exist specifically to support these access paths efficiently.

Related Objects

This table is a hub referenced by a large number of EBS objects; the most significant are listed below.

• HZ_CUST_ACCOUNTS — parent account table, joined on CUST_ACCOUNT_ID.
• HZ_PARTY_SITES — resolved via PARTY_SITE_ID for address details.
• HZ_CUST_SITE_USES_ALL — the site-use extension table, joined on CUST_ACCT_SITE_ID; it carries the bill-to and ship-to use assignments for each account site.
• HZ_CUST_ACCOUNT_ROLES and HZ_CUST_CONTACT_POINTS — reference CUST_ACCT_SITE_ID for role and contact-point associations.
• RA_CUSTOMER_TRX_ALL — Transaction table referencing BILL_TO_ADDRESS_ID, SHIP_TO_ADDRESS_ID, and REMIT_TO_ADDRESS_ID.
• RA_REMIT_TOS_ALL and AR_CORRESPONDENCES_ALL — remit-to and correspondence address consumers.
• OE_HEADER_ACKS and OE_LINE_ACKS — Order Management acknowledgements referencing SHIP_TO_ADDRESS_ID and INVOICE_ADDRESS_ID.
• RA_INTERFACE_LINES_ALL — AutoInvoice interface referencing ORIG_SYSTEM_BILL_ADDRESS_ID and ORIG_SYSTEM_SHIP_ADDRESS_ID.
• ZX_LINES_DET_FACTORS — E-Business Tax detail factors referencing BILL_THIRD_PTY_ACCT_SITE_ID and SHIP_THIRD_PTY_ACCT_SITE_ID.
• RA_TERRITORIES — referenced through TERRITORY_ID for territory assignment data.

Programmatic maintenance of this table should be performed through the supported TCA public APIs rather than direct DML, to preserve the integrity of the hub and its dependent link and satellite structures.