Search Results psp_eff_reports




Overview
Explore in AI Chat

Overview

PSP_EFF_REPORTS is a core transactional table in the Oracle EBS Labor Distribution (PSP) module. It stores the header-level definition of effort reports — the periodic statements that allocate an employee's payroll costs across the grants, projects, and accounts against which the individual actually worked. Each row represents one effort report for a specific employee and reporting period, capturing the reporting window through START_DATE and END_DATE, the employee identity, the reporting status, and the currency and set of books under which the effort is being recorded. In EBS 12.1.1 and 12.2.2, the object resides in the PSP schema and is documented as a VALID table with 25 columns.

The metadata classifies PSP_EFF_REPORTS heuristically as standalone in Data Vault terms. This is a modeling suggestion rather than a schema fact: the effort report acts as an independent hub-like anchor, with its associated distribution, pre-generation, and adjustment records represented separately and joined back through the effort-report identifier. Practitioners modeling downstream reporting on top of PSP should treat PSP_EFF_REPORTS as the anchor for effort-report facts rather than as a satellite of another entity.

Key Information Stored

The surrogate primary key is EFFORT_REPORT_ID, enforced by the PSP_EFF_REPORT_PK constraint. A unique index, PSP_EFF_REPORTS_U1, is defined on EFFORT_REPORT_ID, making it the documented business-key candidate. The remaining significant columns fall into several groups:

Common Use Cases and Queries

Typical reporting targets include open effort reports by status, effort reports for a given period, and reports awaiting re-generation. A representative query filters by status and date window:

  • SELECT EFFORT_REPORT_ID, PERSON_ID, FULL_NAME, START_DATE, END_DATE, STATUS_CODE FROM PSP_EFF_REPORTS WHERE STATUS_CODE = 'OPEN' AND END_DATE BETWEEN :start AND :end;
  • Joining to PSP_PRE_GEN_DIST_LINES_HISTORY on EFFORT_REPORT_ID to reconcile generated pre-distribution lines against the header.
  • Joining to PSP_DISTRIBUTION_LINES_ARCH on EFFORT_REPORT_ID to retrieve archived distribution detail for a completed period.
  • Joining to PSP_ADJUSTMENT_LINES_ARCH on EFFORT_REPORT_ID to identify adjustments applied to a specific effort report.

Because the object is standalone, most reporting joins flow from child distribution tables back to this anchor.

Related Objects

Four documented foreign-key relationships reference PSP_EFF_REPORTS through EFFORT_REPORT_ID. These are the principal dependent objects:

  • PSP_PRE_GEN_DIST_LINES_HISTORY — historical pre-generation distribution lines linked by EFFORT_REPORT_ID.
  • PSP_DISTRIBUTION_LINES_ARCH — archived distribution lines linked by EFFORT_REPORT_ID.
  • PSP_PRE_GEN_DIST_LINES_ARCH — archived pre-generation distribution lines linked by EFFORT_REPORT_ID.
  • PSP_ADJUSTMENT_LINES_ARCH — archived adjustment lines linked by EFFORT_REPORT_ID.

These relationships confirm that PSP_EFF_REPORTS functions as the parent record for the labor distribution lifecycle: pre-generation, distribution, and adjustment activity all resolve to the effort-report header documented here.