Search Results pqh_templates

Overview

The PQH_TEMPLATES table is a core data security object within the Oracle E-Business Suite Public Sector Human Resources (PQH) module. It serves as the master repository for defining templates that govern position control and transaction security. In the context of a public sector organization, these templates are instrumental in enforcing complex business rules and data access controls for HR transactions, such as position creation, funding changes, and employee assignments. The table's primary role is to establish a reusable framework of security and validation rules that can be associated with specific transaction categories, user roles, and organizational hierarchies, thereby ensuring data integrity and compliance with legislative or policy mandates.

Key Information Stored

The table's structure centers on the unique identification and categorization of each security template. The primary key, TEMPLATE_ID, is a unique system-generated identifier. A critical business key is the SHORT_NAME column, which must also be unique, providing a meaningful code for application logic and reporting. A fundamental column is TRANSACTION_CATEGORY_ID, a foreign key to PQH_TRANSACTION_CATEGORIES, which links the template to a specific type of HR action (e.g., "Create Position," "Abolish Position"). Other typical columns, inferred from standard practice and the related objects, would include attributes for the template's effective start and end dates, a descriptive name, and potentially a status flag to control active usage. The table enables a hierarchical template structure, as evidenced by the foreign key relationships from PQH_REF_TEMPLATES for PARENT_TEMPLATE_ID and BASE_TEMPLATE_ID.

Common Use Cases and Queries

A primary use case involves determining which security templates are applicable for a given transaction category during a position management process. System administrators may query the table to audit or maintain template definitions. Common reporting needs include listing all active templates with their associated transaction categories. Sample SQL patterns include retrieving template details for a specific short name or transaction category:

• SELECT template_id, short_name, transaction_category_id FROM pqh_templates WHERE short_name = 'FUND_SECURITY_TMPL';
• SELECT t.short_name, t.template_id, tc.name FROM pqh_templates t, pqh_transaction_categories tc WHERE t.transaction_category_id = tc.transaction_category_id AND SYSDATE BETWEEN t.effective_start_date AND NVL(t.effective_end_date, SYSDATE);

Another critical scenario is validating user access; the system will join PQH_TEMPLATES with PQH_ROLE_TEMPLATES and user role assignments to determine if a user is authorized to perform a specific transaction under a given template's rules.

Related Objects

The PQH_TEMPLATES table is a central hub in the position control security schema. Key related objects, as defined by its foreign key dependencies, include:

• PQH_TRANSACTION_CATEGORIES: The master table for transaction types, categorizing what business action a template secures.
• PQH_REF_TEMPLATES: References this table twice to establish parent-child and base template relationships, enabling template inheritance and hierarchy.
• PQH_ROLE_TEMPLATES: Associates security templates with user roles, defining which roles can use which templates.
• PQH_TEMPLATE_ATTRIBUTES: Stores the detailed security rules and attribute-level validations defined for a specific template.
• PQH_TRANSACTION_TEMPLATES: Likely links specific transaction instances or definitions to the governing template.

These relationships collectively form a comprehensive security model where templates define rules, which are then applied to transactions based on user roles and organizational context.