Search Results psp_report_templates_h




Overview
Explore in AI Chat

Overview

PSP.PSP_REPORT_TEMPLATES_H is a history (audit) table within the PSP – Labor Distribution module of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. Its documented purpose is to store historical details of Effort report templates. Where the operational template definition is maintained in the base template table, the _H table preserves each version of that definition, capturing the template's header attributes at the point of a change or submission event. The table is owned by the PSP schema and is classified as VALID in the ETRM data dictionary.

The table carries 74 documented columns, providing a wide horizontal structure that combines template metadata, up to twenty parameter name/value pairs, approval and workflow attributes, and standard Oracle EBS audit columns. Based on its foreign key topology—a single primary key driven by REQUEST_ID with dependent detail rows keyed on the same column—the heuristic Data Vault classification is hub-leaning. In Data Vault modeling terms, this object behaves as a hub that anchors the history of a template request, with PSP_REPORT_TEMPLATE_DETAILS_H acting as the associated satellite that supplies the line-level content.

Key Information Stored

The surrogate primary key is defined by constraint PSP_REPORT_TEMPLATES_H_PK on the REQUEST_ID column. A secondary unique index, PSP_REPORT_TEMPLATES_H_U1, also spans REQUEST_ID, confirming it as the business-key candidate that uniquely identifies each historical template request record. TEMPLATE_ID and TEMPLATE_NAME link the history row back to the originating template identity.

Common Use Cases and Queries

Typical usage centers on version tracking and audit reporting for Effort report templates. Analysts query the table to reconstruct what a template looked like when a given payroll action was processed, or to compare successive parameter configurations. A common join pattern retrieves header and detail history together:

  • Audit reconstruction: SELECT h.TEMPLATE_NAME, h.SUBMISSION_DATE, h.APPROVAL_TYPE FROM PSP_REPORT_TEMPLATES_H h WHERE h.TEMPLATE_ID = :template_id ORDER BY h.SUBMISSION_DATE;
  • Detail linkage: SELECT h.REQUEST_ID, d.* FROM PSP_REPORT_TEMPLATES_H h, PSP_REPORT_TEMPLATE_DETAILS_H d WHERE h.REQUEST_ID = d.REQUEST_ID;
  • Cadence analysis: join PERIOD_FREQUENCY_ID to PSP_REPORT_PERIOD_FREQUENCY_B to report templates by reporting frequency.
  • Payroll action history: join PAYROLL_ACTION_ID to PAY_PAYROLL_ACTIONS to trace templates used per action.

Related Objects

  • PSP_REPORT_TEMPLATE_DETAILS_H — Child history detail table; references this table via REQUEST_ID.
  • PSP_REPORT_PERIOD_FREQUENCY_B — Referenced by PERIOD_FREQUENCY_ID.
  • PAY_PAYROLL_ACTIONS — Referenced by PAYROLL_ACTION_ID.
  • PSP_REPORT_TEMPLATES_B / _TL — Base and translated template definitions corresponding to these history rows.