Search Results pqh_ref_templates_fk2

Overview

The HR.PQH_REF_TEMPLATES table is a core data structure within the Oracle E-Business Suite (EBS) Human Resources (HR) module, specifically supporting the Oracle HRMS Advanced Global Human Resources functionality. Its primary role is to manage the hierarchical relationships between document templates, specifically tracking copy and reference associations. In the context of EBS 12.1.1 and 12.2.2, this table enables the system to maintain data integrity and lineage when templates are duplicated or referenced across different organizational contexts, a common requirement for global enterprises managing standardized yet localized HR processes and documents.

Key Information Stored

The table stores metadata that defines the relationship between a source template and its derivative. The key columns are:

• REF_TEMPLATE_ID: The primary key uniquely identifying each relationship record.
• BASE_TEMPLATE_ID: The identifier for the original or source template. This column is part of a foreign key relationship to the PQH_TEMPLATES table.
• PARENT_TEMPLATE_ID: The identifier for the template that is either a copy of or references the base template. This column also has a foreign key relationship to PQH_TEMPLATES. Together with BASE_TEMPLATE_ID, it forms the unique key (PQH_REF_TEMPLATES_UK), ensuring a specific relationship is recorded only once.
• REFERENCE_TYPE_CD: A critical code column that indicates the nature of the relationship—whether the parent template is a 'Referenced' version or a 'Copied' version of the base template.
• OBJECT_VERSION_NUMBER: Supports the Oracle Applications Framework (OAF) and Application Development Framework (ADF) for enforcing optimistic locking to prevent data overwrites.
• Standard WHO Columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN): Audit columns tracking the creation and modification history of each record.

Common Use Cases and Queries

This table is essential for reporting and auditing template lineage and for troubleshooting configuration issues. A common scenario involves identifying all templates derived from a specific master template to assess the impact of a proposed change. The following query pattern is fundamental:

SELECT parent.template_name AS "Derived Template",
base.template_name AS "Source Template",
rft.reference_type_cd AS "Relationship Type"
FROM hr.pqh_ref_templates rft,
hr.pqh_templates parent,
hr.pqh_templates base
WHERE rft.parent_template_id = parent.template_id
AND rft.base_template_id = base.template_id
AND base.template_name = 'MASTER_PAY_POLICY';

Another critical use case is data validation, ensuring no circular references or orphaned template relationships exist. Administrators may query for records where the PARENT_TEMPLATE_ID and BASE_TEMPLATE_ID are identical, which would indicate a data integrity issue. The table is typically accessed indirectly by seeded application logic when creating or modifying templates via the standard EBS user interface.

Related Objects

The HR.PQH_REF_TEMPLATES table has defined dependencies within the HR schema, primarily with the PQH_TEMPLATES table, which stores the core template definitions. It is referenced by two foreign key constraints (PQH_REF_TEMPLATES_FK2 and another implicit constraint) on the BASE_TEMPLATE_ID and PARENT_TEMPLATE_ID columns, both pointing to PQH_TEMPLATES.TEMPLATE_ID. This enforces referential integrity, ensuring that relationship records only point to valid templates. Furthermore, the metadata indicates the existence of a database view named PQH_REF_TEMPLATES_WHO, which likely provides a convenient, pre-joined view of the table data along with descriptive information from related entities, commonly used for reporting purposes.