Search Results hz_certifications_pk

Overview

HZ_CERTIFICATIONS is a Receivables (AR) module table that stores certifications granted to parties. It resides in the AR schema and is classified as VALID in the ETRM 12.2.2 physical repository. Certification records captured here represent formal credentials, licenses, qualifications, or authorized designations that a party (such as a customer, supplier, or contact) has obtained and that are tracked within the Trading Community Architecture (TCA) registry. The table forms part of the broader HZ (Trading Community) data model, which underpins party, account, and location management across Oracle E-Business Suite.

Based on the mined foreign key structure, the heuristic Data Vault classification for this object is satellite-leaning. This classification suggests modeling HZ_CERTIFICATIONS as a satellite attached to the HZ_PARTIES hub, since it carries descriptive, time-bound attributes about a party rather than acting as an independent hub or as a pure link between two hubs. The PARTY_ID foreign key anchors it firmly to the party entity, while the certification-specific attributes (expiry, grade, issuing authority) provide the contextual detail typical of a satellite structure.

Key Information Stored

The table contains 19 documented columns. The most significant are:

• CERTIFICATION_ID — Surrogate primary key defined by HZ_CERTIFICATIONS_PK and reinforced by the unique index HZ_CERTIFICATIONS_U1. Serves as the sole business-key candidate in the documented metadata.
• PARTY_ID — Foreign key to HZ_PARTIES, identifying the party to whom the certification has been granted. This is the principal linkage column.
• CERTIFICATION_NAME — Descriptive name of the certification held.
• CURRENT_STATUS — Status flag indicating the current state of the certification record.
• GRADE — Grade or level associated with the certification.
• ISSUED_BY_AUTHORITY — The authority or body that issued the certification.
• ISSUED_ON_DATE — Date the certification was granted.
• EXPIRES_ON_DATE — Date the certification lapses, enabling renewal tracking.
• STATUS — Additional status attribute distinct from CURRENT_STATUS.
• CREATED_BY, CREATION_DATE — Standard audit fields capturing record creation.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Audit trail for record modification.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program context columns.
• WH_UPDATE_DATE — Warehouse update date, supporting downstream data warehouse extraction.

The WHO columns (CREATED_BY, CREATION_DATE, etc.) follow the standard Oracle Applications audit convention and are essential for reconciliation and audit reporting.

Common Use Cases and Queries

Typical scenarios include verifying that a party holds a valid, unexpired certification, preparing compliance or regulatory reports, and driving renewal notifications. A representative query to retrieve all certifications for a given party follows:

• SELECT c.certification_id, c.certification_name, c.current_status, c.issued_by_authority, c.issued_on_date, c.expires_on_date FROM hz_certifications c WHERE c.party_id = :party_id ORDER BY c.expires_on_date DESC;
• Expiry monitoring: filter on expires_on_date against SYSDATE to identify certifications nearing lapse.
• Authoritative source reporting: group by issued_by_authority and grade to summarize certification distribution.
• Audit reconciliation: join on the WHO columns to trace changes made by specific users or concurrent programs.

Related Objects

• HZ_PARTIES — The primary referenced table; joined via HZ_CERTIFICATIONS.PARTY_ID = HZ_PARTIES.PARTY_ID. This is the sole documented foreign key relationship and the central anchor for certification data.
• HZ_PARTY_SITES — Frequently joined to resolve party-level location context.
• HZ_CUST_ACCOUNTS — Links the party to customer account records, useful for account-level compliance reporting.
• HZ_PERSON_PROFILES and HZ_ORGANIZATION_PROFILES — Provide person and organization detail for the party holding the certification.
• HZ_CONTACT_POINTS — Supplies contact data associated with the certified party.
• AR_CUSTOMERS and related AR views — Surface customer-facing certification information in Receivables reporting.

Together these objects allow certification data to be enriched with party, account, and contact context, supporting both operational verification and analytical reporting across the TCA model.