Results for “pqh_ref_templates”

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

Overview

PQH_REF_TEMPLATES is a transactional configuration table owned by the HR schema within the Public Sector HR (PQH) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It maintains the relationship definitions that govern how templates copy from or reference other templates. In the context of Oracle EBS, this table acts as an association registry: rather than storing template content itself, it records which templates are linked to which parent or base templates, thereby driving inheritance, copy behavior, and reference resolution during template instantiation.

Heuristic Data Vault classification mined from the foreign key structure identifies this object as a link table. This is a modeling suggestion rather than a documented designation. The classification is supported by the table's role as a connector between template entities: the primary key is a surrogate identifier, while the two unique indexes expose business-key combinations that describe relationships among templates rather than describing a single entity's attributes.

Key Information Stored

The documented physical schema comprises 11 columns. The most significant are:

• REF_TEMPLATE_ID — the surrogate primary key (PQH_REF_TEMPLATES_PK), uniquely identifying each copy or reference template record.
• BASE_TEMPLATE_ID — a foreign key to PQH_TEMPLATES identifying the base template that provides the source definition.
• PARENT_TEMPLATE_ID — a foreign key to PQH_TEMPLATES identifying the parent template in the reference hierarchy.
• REFERENCE_TYPE_CD — the code describing the nature of the copy or reference relationship.
• OBJECT_VERSION_NUMBER — optimistic locking column used to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — audit columns recording who last modified the row and when.
• CREATED_BY, CREATION_DATE — audit columns recording row creation.
• ZD_EDITION_NAME — editioning column supporting Online Patching in Release 12.2, participating in both unique indexes.

Two unique indexes serve as business-key candidates: PQH_REF_TEMPLATES_PK spans (REF_TEMPLATE_ID, ZD_EDITION_NAME), while PQH_REF_TEMPLATES_UK spans (PARENT_TEMPLATE_ID, BASE_TEMPLATE_ID, ZD_EDITION_NAME). The surrogate key is REF_TEMPLATE_ID; the business key is the parent/base template pairing, which enforces that a given combination of parent and base template appears only once per edition.

Common Use Cases and Queries

Typical usage involves resolving which templates reference a given base template, tracing the copy lineage of a public sector HR template, and validating that duplicate relationships have not been introduced prior to deployment across editions.

• Identifying all template relationships for a base template:
  SELECT ref_template_id, parent_template_id, reference_type_cd FROM pqh_ref_templates WHERE base_template_id = :base_id;
• Resolving parent template names by joining to PQH_TEMPLATES:
  SELECT rt.ref_template_id, t.template_name FROM pqh_ref_templates rt, pqh_templates t WHERE rt.parent_template_id = t.template_id;
• Auditing recently modified relationships using the audit columns:
  SELECT * FROM pqh_ref_templates WHERE last_update_date > SYSDATE - 30;
• Reporting on downstream consumers, joining to AR_BPA_TEMPLATES_B on REF_TEMPLATE_ID to determine which BPA templates depend on a given reference template.

Because the table is edition-enabled, queries in Release 12.2 should account for ZD_EDITION_NAME when comparing rows across editions or when validating uniqueness.

Related Objects

The following objects are most significant to PQH_REF_TEMPLATES:

• PQH_TEMPLATES — referenced twice via PQH_REF_TEMPLATES.PARENT_TEMPLATE_ID and PQH_REF_TEMPLATES.BASE_TEMPLATE_ID; the principal parent entity.
• AR_BPA_TEMPLATES_B — references this table through REF_TEMPLATE_ID, making it a downstream consumer of the reference template definitions.
• PQH_REF_TEMPLATES_PK — primary key index on (REF_TEMPLATE_ID, ZD_EDITION_NAME).
• PQH_REF_TEMPLATES_UK — unique index on (PARENT_TEMPLATE_ID, BASE_TEMPLATE_ID, ZD_EDITION_NAME), enforcing business-key uniqueness.

Together these objects form the template relationship model, with PQH_TEMPLATES supplying the entity definitions and AR_BPA_TEMPLATES_B consuming the resulting linkage.