Search Results hz_industrial_reference_u1

Overview

The AR.HZ_INDUSTRIAL_REFERENCE table is a Trading Community Architecture (TCA) registry object that stores external industry identifiers assigned to a party by an industrial association or regulatory group. A canonical example is a SWIFT code issued by the Society for Worldwide Interbank Financial Telecommunications, but the table is generic enough to hold any recognized industry reference such as a Dun & Bradstreet number, a broker-dealer license, or a chamber-of-commerce registration. The table is owned by the AR schema, is marked VALID in ETRM 12.2.2, and is stored in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10 and 17 documented columns.

The object exposes two physical indexes, both in APPS_TS_TX_IDX: the unique index HZ_INDUSTRIAL_REFERENCE_U1 on INDUSTRY_REFERENCE_ID (which is also the primary key, HZ_INDUSTRIAL_REFERENCE_PK), and the non-unique index HZ_INDUSTRIAL_REFERENCE_N1 on PARTY_ID. The single documented foreign key, PARTY_ID → HZ_PARTIES, establishes this table as a child of the party entity. The metadata's heuristic Data Vault classification is satellite-leaning, meaning the object is best modeled as a satellite attached to a hub (HZ_PARTIES) rather than as an independent hub or a link, since its natural business key is the surrogate INDUSTRY_REFERENCE_ID and its descriptive attributes hang off PARTY_ID.

Key Information Stored

The surrogate primary key is INDUSTRY_REFERENCE_ID (NUMBER(15), mandatory), which is the unique business-key candidate surfaced by HZ_INDUSTRIAL_REFERENCE_U1. The remaining significant attributes are:

• PARTY_ID — mandatory foreign key to HZ_PARTIES; identifies the party to which the reference belongs and is the join path for all reporting.
• INDUSTRY_REFERENCE — VARCHAR2(60); the actual number or name of the industrial reference (e.g., the SWIFT code).
• ISSUED_BY_AUTHORITY — VARCHAR2(60); the body that issued the reference.
• NAME_OF_REFERENCE — VARCHAR2(240); a descriptive label for the reference.
• RECOGNIZED_AS_OF_DATE — DATE; when the reference became officially recognized.
• STATUS — VARCHAR2(30); the current state of the reference record.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard who-columns; the created/updated-by fields are foreign keys to FND_USER.USER_ID.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent-program who-columns; REQUEST_ID references FND_CONCURRENT_REQUESTS.
• WH_UPDATE_DATE — warehouse update timestamp used by EBS reporting extracts.

The distinction matters: INDUSTRY_REFERENCE is the human-recognizable business value, while INDUSTRY_REFERENCE_ID is the stable surrogate used by all downstream relationships.

Common Use Cases and Queries

Typical usage centers on enriching party profiles with regulatory identifiers, validating reference uniqueness across a party hierarchy, and feeding downstream compliance or payments systems. A common pattern retrieves all references for a given party:

• SELECT ir.industry_reference, ir.issued_by_authority, ir.recognized_as_of_date FROM ar.hz_industrial_reference ir WHERE ir.party_id = :p_party_id AND ir.status = 'A';
• Joining to the party master: SELECT p.party_name, ir.industry_reference FROM ar.hz_parties p, ar.hz_industrial_reference ir WHERE p.party_id = ir.party_id AND ir.issued_by_authority = 'SWIFT';
• Audit query using the concurrent who-columns: WHERE ir.last_update_date >= :p_since_date ORDER BY ir.last_update_date.

Because HZ_INDUSTRIAL_REFERENCE_N1 covers PARTY_ID, lookups by party are indexed and efficient. Reports frequently denormalize ISSUED_BY_AUTHORITY and NAME_OF_REFERENCE for regulatory filings, KYC extracts, and supplier master data cleansing.

Related Objects

The table participates in the broader TCA party model. The most significant related objects are:

• AR.HZ_PARTIES — the hub; joined via HZ_INDUSTRIAL_REFERENCE.PARTY_ID = HZ_PARTIES.PARTY_ID (the documented foreign key).
• AR.HZ_PARTY_SITES and AR.HZ_LOCATIONS — provide the site and location context for a party's operational footprint.
• AR.HZ_CUST_ACCOUNTS — links parties to receivable accounts for financial reporting.
• AR.HZ_ORGANIZATION_PROFILES — stores organization-level attributes alongside industry references.
• APPLSYS.FND_USER — referenced by CREATED_BY and LAST_UPDATED_BY who-columns.
• APPLSYS.FND_CONCURRENT_REQUESTS — referenced by REQUEST_ID for concurrent-program lineage.
• AR.HZ_PARTY_SITE_USES and the public TCA APIs (e.g., HZ_PARTY_BO_API) — used to create and maintain party reference data programmatically.

Together these objects form the EBS 12.1.1/12.2.2 trading-community backbone in which industrial references are attached to their owning party.