Search Results hz_party_sites_n3

Overview

HZ_PARTY_SITES 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 records the relationship between a party (a person or organization defined in HZ_PARTIES) and a location (a physical address defined in HZ_LOCATIONS). In effect, it materializes the statement "this party uses this address," together with attributes that are meaningful only at that intersection, such as mailstop, addressee, and identifying-address flags.

The table sits at the center of customer and supplier address management. A single party may own many party sites, and a single location may be shared by many parties; for example, 500 Oracle Parkway could be a party site for Oracle Corporation and be reused across multiple customer accounts belonging to that party. Downstream objects such as HZ_CUST_ACCT_SITES_ALL and HZ_PARTY_SITE_USES attach business purpose (bill-to, ship-to, etc.) to the party site, while transaction, order, service, and tax tables reference the party site identifier directly.

Mined from its foreign-key topology, the heuristic Data Vault classification for this object is a hub. It anchors two natural business keys — the party and the location — and is referenced by a very large number of dependent tables. Teams building a warehouse model may therefore treat HZ_PARTY_SITES as a hub keyed on PARTY_SITE_ID, with the intersection of PARTY_ID and LOCATION_ID as a candidate link.

Key Information Stored

The table contains 75 documented columns. The most significant are listed below; several unused attribute and global-attribute columns are omitted deliberately.

• PARTY_SITE_ID (NUMBER(15), mandatory) — surrogate primary key, enforced by HZ_PARTY_SITES_PK and the unique index HZ_PARTY_SITES_U1. This is the value carried by nearly every downstream foreign key.
• PARTY_SITE_NUMBER (VARCHAR2(30)) — the user-visible business identifier for the address, enforced by the second unique index, HZ_PARTY_SITES_U2. This is the object the user searched for, and it is the natural key most often quoted in interfaces and reports.
• PARTY_ID (NUMBER(15)) — foreign key to HZ_PARTIES; identifies the owning party.
• LOCATION_ID (NUMBER(15)) — foreign key to HZ_LOCATIONS; identifies the underlying address.
• PARTY_SITE_NAME — a descriptive name for the site, typically shown alongside the address.
• ADDRESSEE and MAILSTOP — location-specific delivery information that cannot be stored on the party or the location alone.
• IDENTIFYING_ADDRESS_FLAG — indicates whether the site is used as an identifying address for the party; indexed via HZ_PARTY_SITES_N4 together with PARTY_ID.
• ORIG_SYSTEM_REFERENCE — the legacy or external system identifier, indexed by HZ_PARTY_SITES_N3 and central to data migration and duplicate resolution.
• GLOBAL_LOCATION_NUMBER and DUNS_NUMBER_C — globally unique address and D-U-N-S identifiers used for data quality matching.
• START_DATE_ACTIVE / END_DATE_ACTIVE — effective dating for the site relationship.
• STATUS, LANGUAGE, REGION, and OBJECT_VERSION_NUMBER — lifecycle, locale, geographic grouping, and the standard OLTP optimistic-locking column.
• LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Who audit columns; REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID record the concurrent program that last touched the row.

Common Use Cases and Queries

Party sites are foundational to Receivables, Order Management, Advanced Pricing, Service, iStore, and India localization (JAI) tax reporting. Typical operations include retrieving a party's identifying address, resolving a ship-to or bill-to for an order, validating a supplier site, and reconciling customer master data.

Locating a site by its business key is a common pattern:

• SELECT party_site_id, party_id, location_id, status FROM hz_party_sites WHERE party_site_number = :p_site_number;
• Joining to the address: SELECT ps.party_site_number, l.address1, l.city FROM hz_party_sites ps, hz_locations l WHERE ps.location_id = l.location_id AND ps.party_id = :p_party_id;
• Finding the identifying address for a party: SELECT party_site_id FROM hz_party_sites WHERE party_id = :p_party_id AND identifying_address_flag = 'Y';
• Data-quality and deduplication reporting using ORIG_SYSTEM_REFERENCE, GLOBAL_LOCATION_NUMBER, or DUNS_NUMBER_C to surface duplicate sites.

Because three indexes (N2 on LOCATION_ID, N3 on ORIG_SYSTEM_REFERENCE, N4 on PARTY_ID, N5 on GLOBAL_LOCATION_NUMBER) cover the primary access paths, queries should filter on those columns rather than performing full-table scans on a table that routinely holds millions of rows in large implementations.

Related Objects

The following are the most significant dependencies, drawn from the documented foreign-key graph.

• HZ_PARTIES — joined on HZ_PARTY_SITES.PARTY_ID = HZ_PARTIES.PARTY_ID; the party master.
• HZ_LOCATIONS — joined on HZ_PARTY_SITES.LOCATION_ID = HZ_LOCATIONS.LOCATION_ID; the address master.
• HZ_CUST_ACCT_SITES_ALL — references HZ_PARTY_SITES.PARTY_SITE_ID; links a party site to a customer account site.
• HZ_PARTY_SITE_USES — references PARTY_SITE_ID; assigns business purpose such as bill-to or ship-to.
• HZ_PARTY_SITES_EXT_B and HZ_PARTY_SITES_EXT_TL — the extension and translated extension tables keyed by PARTY_SITE_ID.
• HZ_ORG_CONTACTS — references PARTY_SITE_ID to associate a contact with an organization site.
• HZ_CONTACT_POINTS — references OWNER_TABLE_ID, allowing phones, e-mail, and URLs to be attached to a party site.
• OE_HEADERS_IFACE_ALL and OE_LINES_IFACE_ALL — order import interface columns such as SHIP_TO_PARTY_SITE_ID and INVOICE_TO_PARTY_SITE_ID.
• AP_INVOICE_PAYMENTS_ALL — references INVOICING_PARTY_SITE_ID for supplier payment routing.
• CS_CUSTOMER_PRODUCTS_ALL and CSI_ITEM_INSTANCES — service and install-base references via INSTALL_SITE_USE_ID and LOCATION_ID.

These relationships confirm that HZ_PARTY_SITES is a high-fan-out hub: changes to party site records cascade into receivables, order management, purchasing, service, and statutory reporting processes, and any data correction should be evaluated against the full dependency list before being applied.