Results for “hz_references”

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

Overview

HZ_REFERENCES is a table in the AR (Oracle Receivables) schema that stores references given for parties within the Oracle E-Business Suite Trading Community Architecture (TCA) model. Each row captures a reference relationship in which one party (the commenting party) provides a reference or testimonial regarding another party (the referenced party). The table is documented as VALID under Oracle EBS 12.1.1 and 12.2.2, and its documented physical schema in the ETRM 12.2.2 repository contains 18 columns.

Because the table holds a unique identifier for each reference and carries two foreign keys into HZ_PARTIES, a Data Vault classification heuristic mined from its foreign key structure labels it as hub-leaning. In Data Vault modeling terms, this suggests treating REFERENCE_ID as a hub key representing a business concept (a party reference), with the two party references modeled as links to the HZ_PARTIES hub. Attribute columns such as COMMENTS, RATING, and REFERENCE_DATE behave as descriptive satellite data attached to that hub.

Key Information Stored

The table's surrogate primary key is REFERENCE_ID, defined by the constraint HZ_REFERENCES_PK. The unique index HZ_REFERENCES_U1 also exists on REFERENCE_ID, marking it as the documented business-key candidate. The most significant business columns include:

• REFERENCED_PARTY_ID — foreign key to HZ_PARTIES; identifies the party being referenced.
• COMMENTING_PARTY_ID — foreign key to HZ_PARTIES; identifies the party providing the reference.
• EXTERNAL_ACCOUNT_NUMBER — external account or reference identifier associated with the relationship.
• COMMENTS — free-text narrative describing the reference.
• RATING — the rating assigned to the reference.
• REFERENCE_DATE — the date the reference was given.
• STATUS — the current status of the reference record.

Standard TCA audit columns are also present, including CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN. The concurrent-program context columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE record the request that last populated the row, and WH_UPDATE_DATE supports warehouse/ETL tracking.

Common Use Cases and Queries

Typical scenarios include retrieving all references for a given party, rating reports, and identifying references associated with specific sites, contacts, or deliverables. A standard lookup joins the two party columns back to HZ_PARTIES:

• References given for a party: SELECT r.reference_id, r.rating, r.reference_date, r.status FROM hz_references r WHERE r.referenced_party_id = :party_id;
• Commenting party detail: SELECT cp.party_name, r.comments FROM hz_references r, hz_parties cp WHERE r.commenting_party_id = cp.party_id AND r.reference_id = :id;
• Supporting detail by site, contact, or deliverable: join HZ_REFERENCES to AS_REF_SITES, AS_REF_CONTACTS, or AS_REF_DELIVERABLES on REFERENCE_ID.
• Warehouse/audit extraction: filter on LAST_UPDATE_DATE or WH_UPDATE_DATE for incremental loads.

Related Objects

The table participates in a hub-and-detail relationship pattern. It references HZ_PARTIES through two columns, REFERENCED_PARTY_ID and COMMENTING_PARTY_ID. Several dependent tables carry the REFERENCE_ID foreign key back to this table, forming the reference's child detail:

• AS_REF_CONTACTS — join on REFERENCE_ID; contacts tied to the reference.
• AS_REF_DELIVERABLES — join on REFERENCE_ID; deliverables associated with the reference.
• AS_REF_RESTRICTIONS — join on REFERENCE_ID; usage restrictions on the reference.
• AS_REF_SHORT_NOTES — join on REFERENCE_ID; short notes attached to the reference.
• AS_REF_SITES — join on REFERENCE_ID; sites associated with the reference.

These relationships confirm that HZ_REFERENCES functions as the parent reference entity, while the AS_REF_* tables supply descriptive child records keyed by REFERENCE_ID.