Results for “pqh_ref_templates_uk”

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

Overview

HR.PQH_REF_TEMPLATES is a reference and dependency table within the Oracle E-Business Suite HR (Human Resources) schema, belonging to the Public Sector/HR template engine component of Oracle EBS 12.1.1 and 12.2.2. Its documented purpose is to list the copy and reference templates associated with a given template. In practice, the table records the lineage relationships that exist between template definitions stored in HR.PQH_TEMPLATES, capturing both the template that serves as the origin (base) and the template that acts as the immediate ancestor (parent). This makes the object a structural dependency table used when template hierarchies are copied, referenced, or versioned.

The metadata characterizes this object as a link entity under a heuristic Data Vault classification. This is consistent with its design: it holds foreign keys pointing to PQH_TEMPLATES on two separate columns, and it carries minimal descriptive attributes beyond the relationship itself. From a dimensional modeling perspective it is best treated as an association or bridge table rather than a transactional fact.

Key Information Stored

• REF_TEMPLATE_ID — the surrogate primary key (NUMBER(15)), uniquely identifying each reference/copy relationship record through the PQH_REF_TEMPLATES_PK index.
• BASE_TEMPLATE_ID — the identifier of the base (origin) template; participates in the business-key constraint PQH_REF_TEMPLATES_UK.
• PARENT_TEMPLATE_ID — the identifier of the parent template; the second column in the PQH_REF_TEMPLATES_UK unique key.
• REFERENCE_TYPE_CD — a VARCHAR2(30) code indicating whether the row represents a referenced or copied relationship between templates.
• OBJECT_VERSION_NUMBER — the optimistic locking version used by the ORM layer to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard Who columns providing audit and multi-user traceability.

The documented business-key candidate is the composite unique index PQH_REF_TEMPLATES_UK over (PARENT_TEMPLATE_ID, BASE_TEMPLATE_ID), which is distinct from the surrogate REF_TEMPLATE_ID primary key. In the 12.2.2 physical schema the unique indexes are extended with ZD_EDITION_NAME, reflecting the Edition-Based Redefinition (EBR) model introduced in Release 12.2.

Common Use Cases and Queries

Typical use cases involve template lineage reporting, migration validation, and resolving which templates ultimately descend from a given base. The following pattern retrieves all reference records and joins to the template definitions:

• List all relationships: SELECT REF_TEMPLATE_ID, BASE_TEMPLATE_ID, PARENT_TEMPLATE_ID, REFERENCE_TYPE_CD FROM HR.PQH_REF_TEMPLATES;
• Resolve template lineage by joining base and parent identifiers back to PQH_TEMPLATES.
• Filter by reference type to isolate copied versus referenced templates for audit purposes.
• Detect orphaned relationships where BASE_TEMPLATE_ID or PARENT_TEMPLATE_ID has no matching template row.

Related Objects

• HR.PQH_TEMPLATES — the primary referenced table; joined via PARENT_TEMPLATE_ID and BASE_TEMPLATE_ID through foreign keys PQH_REF_TEMPLATES_FK2 and the FK on PARENT_TEMPLATE_ID.
• APPS.PQH_REF_TEMPLATES — the Apps synonym view exposing the table to the application layer.
• APPS.PQH_REF_TEMPLATES_WHO — the Who-column view used by Oracle Forms and OAF pages.
• AR_BPA_TEMPLATES_B.REF_TEMPLATE_ID — an inbound reference from the Oracle Advanced Collections / BPA templates base table, indicating shared template usage across modules.
• PQH_REF_TEMPLATES_PK, PQH_REF_TEMPLATES_UK, PQH_REF_TEMPLATES_FK2 — supporting indexes on REF_TEMPLATE_ID, the parent/base composite, and BASE_TEMPLATE_ID respectively.