Search Results psp_er_approvals




Overview
Explore in AI Chat

Overview

The PSP_ER_APPROVALS table is a core data object within the Oracle E-Business Suite (EBS) Labor Distribution module (PSP). It serves as the system of record for capturing the formal certification of Effort Reports by supervisors. In the context of EBS 12.1.1 and 12.2.2, Effort Reporting is a critical compliance process, particularly for organizations subject to regulatory requirements like those from the U.S. federal government (e.g., OMB Uniform Guidance). This table directly supports the workflow where a supervisor reviews and certifies the effort distribution of employees, thereby validating the accuracy of labor charges applied to projects and grants. Its existence is essential for maintaining a complete audit trail of the approval chain.

Key Information Stored

The table's structure is designed to uniquely identify each approval action within the context of a specific effort report version. The primary key is a composite of three columns: EFFORT_REPORT_ID, VERSION_NUM, and APPROVING_PERSON_ID. This design allows the table to store a history of approvals for each version of an effort report, accommodating scenarios where multiple supervisors may be involved. The APPROVING_PERSON_ID column stores the identifier (typically corresponding to a record in PER_ALL_PEOPLE_F) of the certifying supervisor. Crucially, the table also stores the supervisor's PASSWORD at the time of certification, which is a security and audit feature to authenticate the approval action. The foreign key relationship to PSP_EFFORT_REPORTS on EFFORT_REPORT_ID and VERSION_NUM ensures data integrity, linking each approval directly to the specific report version being certified.

Common Use Cases and Queries

The primary use case is tracking and reporting on effort report certification status. Common queries involve identifying reports pending a specific supervisor's approval, generating audit reports of all certifications within a date range, or listing the approval history for a given report. For example, to find all effort reports certified by a particular person, one might query:

  • SELECT er.effort_report_id, er.version_num, er.employee_id FROM psp_er_approvals app, psp_effort_reports er WHERE app.approving_person_id = &person_id AND app.effort_report_id = er.effort_report_id AND app.version_num = er.version_num;

Another critical reporting use case is verifying compliance by confirming that every finalized effort report has at least one corresponding record in PSP_ER_APPROVALS, proving it was supervisor-certified. Integration with the EBS workflow and notifications also depends on this table to trigger subsequent process steps upon successful certification.

Related Objects

PSP_ER_APPROVALS has a direct and mandatory relationship with the PSP_EFFORT_REPORTS table, which holds the header and core data for each effort report. The foreign key constraint enforces this link. The APPROVING_PERSON_ID column typically relates to the PER_ALL_PEOPLE_F table in the HRMS schema to retrieve the supervisor's name and details. In application logic, this table is accessed by the Effort Reporting and Labor Distribution forms and concurrent processes. It is also a key source for standard and custom compliance audit reports within the Labor Distribution module. Developers and DBAs should note that direct manipulation of this table, especially the password column, is strongly discouraged; interactions should occur through the standard EBS application interfaces to maintain audit validity.