Search Results hz_certifications_u1

Overview

The AR.HZ_CERTIFICATIONS table stores information about accreditations and certifications awarded to a party, typically following formal evaluation by an awarding organization. A representative example documented in the ETRM metadata is ISO 9000 certification, awarded after an evaluation confirms that an organization adheres to the quality standards established by the International Organization for Standardization. The table resides in the AR schema, is registered under FND Design Data as AR.HZ_CERTIFICATIONS, and carries a status of VALID in both Oracle EBS 12.1.1 and 12.2.2.

Physically, the table is stored in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes reside in APPS_TS_TX_IDX. The documented schema contains 19 columns. From a Data Vault modeling perspective, the metadata's heuristic classification identifies this object as satellite-leaning: the certification attributes describe characteristics of a party rather than defining a standalone business entity. In a Data Vault design, this table would most naturally model as a satellite attached to the HZ_PARTIES hub, keyed by PARTY_ID, with certification attributes such as status, grade, and expiry carried as descriptive satellite columns.

Key Information Stored

The surrogate primary key is CERTIFICATION_ID, a NUMBER(15) column and the single column behind the unique index HZ_CERTIFICATIONS_U1. In Data Vault terms this column behaves as a physical surrogate rather than a natural business key; the unique index is the documented business-key candidate for this table. The most significant columns are:

• CERTIFICATION_ID — Certification identifier; primary key and target of the HZ_CERTIFICATIONS_U1 unique index.
• CERTIFICATION_NAME — Name of the certification, such as ISO9000 or GMP001 (VARCHAR2 240).
• CURRENT_STATUS — Status of the certification, for example applied for, granted, or expired (VARCHAR2 30).
• PARTY_ID — Unique identifier of the party; foreign key to HZ_PARTIES, indexed non-uniquely by HZ_CERTIFICATIONS_N1.
• ISSUED_BY_AUTHORITY — Name of the certifying authority (VARCHAR2 60).
• ISSUED_ON_DATE — Date the certification was issued.
• EXPIRES_ON_DATE — Date the certification expires.
• GRADE — Level or grade assigned by the issuing authority, such as founding member, gold, fully certified, or partially certified (VARCHAR2 30).
• STATUS — Record-level status indicator, distinct from the certification's own CURRENT_STATUS.
• Standard WHO columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN.
• Concurrent program context: REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE, plus the warehouse marker WH_UPDATE_DATE.

Common Use Cases and Queries

Typical reporting scenarios include identifying parties whose certifications are approaching expiry, auditing which authorities have issued certifications, and segmenting parties by certification grade. A common pattern joins the table back to HZ_PARTIES to resolve the party name:

• Expiring certifications: SELECT c.certification_id, c.certification_name, c.expires_on_date, p.party_name FROM hz_certifications c, hz_parties p WHERE c.party_id = p.party_id AND c.expires_on_date BETWEEN SYSDATE AND SYSDATE+90 AND c.current_status = 'GRANTED';
• Certifications held by a given party: SELECT certification_name, issued_by_authority, grade, issued_on_date, expires_on_date FROM hz_certifications WHERE party_id = :party_id ORDER BY issued_on_date DESC;
• Authority-level reporting: SELECT issued_by_authority, COUNT(*) FROM hz_certifications WHERE current_status = 'GRANTED' GROUP BY issued_by_authority;

Because PARTY_ID is indexed by HZ_CERTIFICATIONS_N1, queries filtering on a specific party are efficient. Queries that filter only on certification status or expiry date may require additional predicates or indexes depending on data volume.

Related Objects

• HZ_PARTIES — Parent entity; joined via HZ_CERTIFICATIONS.PARTY_ID = HZ_PARTIES.PARTY_ID, the only documented foreign key.
• HZ_PARTY_SITES and other HZ_ party child tables — Sibling satellites sharing the same PARTY_ID parent, useful for consolidated party profiles.
• FND_USER — Referenced by CREATED_BY and LAST_UPDATED_BY for audit traceability.
• FND_LOGINS — Referenced by LAST_UPDATE_LOGIN to capture the operating-system login of the last updater.
• FND_CONCURRENT_REQUESTS — Linked through REQUEST_ID and PROGRAM_ID for the concurrent program that created or updated a row.
• Oracle TCA (Trading Community Architecture) party maintenance APIs — which programmatically insert and maintain certification records on behalf of external applications.