Search Results psp_eff_report_details




Overview
Explore in AI Chat

Overview

PSP_EFF_REPORT_DETAILS is a transaction-detail table within the Oracle E-Business Suite Labor Distribution (PSP) module. It stores the line-level content of effort reports — the periodic certifications through which principal investigators and administrators confirm the distribution of an employee's payroll across sponsored projects, awards, tasks, and expenditures. While the parent effort report header carries submission, routing, and approval context, PSP_EFF_REPORT_DETAILS captures each individual distribution line, including the payroll percentage and proposed effort percentage attributed to a specific project, task, award, and expenditure type combination. In Oracle EBS 12.1.1 and 12.2.2 the table is owned by the PSP schema, is marked VALID, and contains 86 documented columns in the 12.2.2 physical model.

The ETRM metadata provides a heuristic Data Vault classification of standalone, meaning the mining of foreign-key structure did not identify this table as a hub, link, or satellite. As a modeling suggestion only, this classification should be treated as inconclusive: the table is a pure detail entity whose grain is defined by its composite primary key rather than by an independent business key. Analysts modeling this data should therefore treat it as a dependent child of the effort report header and resolve its relationships through that parent.

Key Information Stored

The physical primary key is PSP_EFF_REPORT_DETAILS_PK, defined on the composite of EFFORT_REPORT_ID and EFFORT_REPORT_DETAIL_ID. A separate unique index, PSP_EFF_REPORT_DETAILS_U1, is defined on EFFORT_REPORT_DETAIL_ID alone, making it the documented business-key candidate and confirming that the detail identifier is unique independent of its parent report.

Common Use Cases and Queries

Effort reporting is typically reported at detail grain: a single certified line per employee, project, task, award, and expenditure type. A common query joins the detail table to the effort report header to retrieve submission and approval status alongside the distribution lines. For example:

  • Effort certification by investigator: select detail rows where INVESTIGATOR_PERSON_ID matches a given person and SCHEDULE_START_DATE falls within the certification period, grouping by PROJECT_NUMBER and AWARD_NUMBER to total PAYROLL_PERCENT and PROPOSED_EFFORT_PERCENT.
  • Variance analysis: compare ACTUAL_SALARY_AMT against PROPOSED_SALARY_AMT and PAYROLL_PERCENT against PROPOSED_EFFORT_PERCENT to identify lines where certified effort diverges from the proposal.
  • Award-level effort rollup: join AWARD_ID to IGF_AW_AWARD_ALL to report total certified effort by sponsor and award.
  • Approval audit: join AME_TRANSACTION_ID to the AME transaction history to trace pending or completed approvals.
  • GL reconciliation: aggregate by GL_SEGMENT values to verify that effort lines reconcile to the distribution posted in the general ledger.

Because the table carries 86 columns and a one-row-per-distribution grain, queries should always filter on EFFORT_REPORT_ID or an indexed report-level predicate before scanning.

Related Objects

  • PSP_EFF_REPORT_DETAILS_PK — composite primary key on (EFFORT_REPORT_ID, EFFORT_REPORT_DETAIL_ID).
  • PSP_EFF_REPORT_DETAILS_U1 — unique index on EFFORT_REPORT_DETAIL_ID, the documented business-key candidate.
  • IGF_AW_AWARD_ALL — referenced via AWARD_ID; provides award and sponsor attributes.
  • The effort report header table in the PSP schema, keyed by EFFORT_REPORT_ID, which owns the detail rows.
  • PA_PROJECTS_ALL and PA_TASKS — referenced through PROJECT_ID and TASK_ID for project and task attributes.
  • PER_ALL_ASSIGNMENTS_F — referenced through ASSIGNMENT_ID to resolve employee and assignment details.
  • PSP_EFFORT_REPORT_HEADERS — the parent entity for submission, certification, and lifecycle status.
  • Labor Distribution transaction and costing tables that consume certified effort for payroll and costing distribution.