Results for “hz_cust_acct_relate_all”

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

Overview

HZ_CUST_ACCT_RELATE_ALL is a transactional and reference table owned by the AR (Receivables) schema in Oracle E-Business Suite, holding the defined relationships between customer accounts. It is the central store for the model that describes how one customer account relates to another — for example, a parent/child hierarchy, a bill-to relationship, or a ship-to relationship. Because it is an "_ALL" table, it is partitioned by operating unit through the ORG_ID column, though the table itself is maintained in the AR schema and is exposed through the Trading Community Architecture (TCA) model that underpins Receivables, Order Management, and related modules.

Under the heuristic Data Vault classification derived from its foreign key structure, this object behaves as a link table. It does not describe an entity in its own right; instead it resolves a many-to-many association between two customer accounts, each of which is a hub. Modeling it as a link is a reasonable suggestion, since both its foreign keys point back to HZ_CUST_ACCOUNTS.

Key Information Stored

Each row represents one directional relationship between two accounts, identified by a surrogate primary key and validated by a unique index on the same column.

• CUST_ACCT_RELATE_ID — surrogate primary key, backed by HZ_CUST_ACCT_RELATE_PK and the unique index HZ_CUST_ACCT_RELATE_U1. This is the single documented business-key candidate.
• CUST_ACCOUNT_ID — foreign key to HZ_CUST_ACCOUNTS, identifying the source (subject) account.
• RELATED_CUST_ACCOUNT_ID — foreign key to HZ_CUST_ACCOUNTS, identifying the target (related) account.
• RELATIONSHIP_TYPE — the classification of the association between the two accounts.
• BILL_TO_FLAG and SHIP_TO_FLAG — indicators governing whether the related account is usable as a bill-to or ship-to destination.
• CUSTOMER_RECIPROCAL_FLAG — indicates whether the relationship applies in both directions.
• STATUS — the active/inactive state of the relationship record.
• COMMENTS — free-text notes describing the relationship.
• ORG_ID — the operating unit that owns the row in the multi-org context.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the TCA API layer.
• CREATED_BY_MODULE and APPLICATION_ID — provenance columns recording which module created the record.
• LAST_UPDATE_DATE / LAST_UPDATED_BY / CREATION_DATE / CREATED_BY — standard audit columns required for concurrent program and interface integrity.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the DFF (descriptive flexfield) columns for client-specific extensions.

Common Use Cases and Queries

Typical scenarios include customer hierarchy reporting, resolving bill-to/ship-to defaults during order entry, and validating related-account mappings loaded through TCA interfaces. A basic query to enumerate all relationships for a given account:

• SELECT r.cust_acct_relate_id, r.cust_account_id, r.related_cust_account_id, r.relationship_type, r.status FROM hz_cust_acct_relate_all r WHERE r.cust_account_id = :acct_id;

A join to resolve account names for both ends of the relationship:

• SELECT a.account_number src_acct, b.account_number rel_acct, r.relationship_type FROM hz_cust_acct_relate_all r, hz_cust_accounts a, hz_cust_accounts b WHERE r.cust_account_id = a.cust_account_id AND r.related_cust_account_id = b.cust_account_id AND r.org_id = :org_id;

Because BILL_TO_FLAG and SHIP_TO_FLAG drive downstream defaults, these columns are frequently filtered in order-capture and invoicing reports. The STATUS column should generally be constrained to active records in production reporting.

Related Objects

The most significant related objects, per the documented FK metadata, are:

• HZ_CUST_ACCOUNTS — referenced twice, via CUST_ACCOUNT_ID and RELATED_CUST_ACCOUNT_ID; this is the primary parent entity.
• HZ_CUST_ACCT_RELATE_ALL_M — a child table that references CUST_ACCT_RELATE_ID, holding additional relationship attributes.
• HZ_CUST_ACCT_RELATE_PK and HZ_CUST_ACCT_RELATE_U1 — the primary key and unique index enforcing row identity.
• The TCA public APIs (for example the Customer Account relationship APIs in the HZ schema) that insert, update, and validate rows in this table, and should be used in preference to direct DML.

Consumers should treat this table as the authoritative source for account-to-account relationships and always route changes through the supported TCA API layer.