Results for “total_amount”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PSP_EFFORT_REPORT_DETAILS is a core table within the Oracle EBS Labor Distribution (PSP) module. It stores the itemized details of an effort report, organized by charging account, so that the distribution of an employee's payroll effort across projects, tasks, awards, expenditure organizations, and expenditure types can be captured and later costed. Each row represents one effort report line, tied to a parent effort report and a specific version, providing the granular data that drives salary and fringe cost allocation downstream.

Within the Oracle EBS 12.1.1 and 12.2.2 schema, the table resides in the PSP schema and is categorized under the Labor Distribution product. Its heuristic Data Vault classification is link, reflecting its role as a transactional association table that connects an effort report to the accounts, projects, tasks, awards, and expenditure types charged. In this modeling view, it functions as an associative link rather than a hub or a pure satellite, because it resolves many-to-many relationships between effort reports and the charging accounts they allocate to.

The table is documented with 17 columns and a composite primary key, PSP_EFFORT_DETAILS_PK, defined over EFFORT_REPORT_ID, VERSION_NUM, and EFFORT_REPORT_LINE_NUM. A unique index, PSP_EFFORT_REPORT_DETAILS_U1, mirrors this key and serves as the business-key candidate, enforcing uniqueness of a detail line within a given effort report version.

Key Information Stored

The most important columns and their roles are:

• EFFORT_REPORT_ID — identifies the parent effort report; part of the composite primary key.
• VERSION_NUM — the version of the effort report, allowing multiple revisions to coexist; part of the primary key.
• EFFORT_REPORT_LINE_NUM — the line number within the effort report version; part of the primary key.
• ASSIGNMENT_ID — the assignment associated with the effort line, linking to the employee's assignment.
• ELEMENT_TYPE_ID — references the effort report element and expenditure type definition.
• GL_CODE_COMBINATIONS_ID — the charging account used for cost distribution.
• PROJECT_ID — the project charged by the effort line.
• TASK_ID — the specific task within the project.
• AWARD_ID — the award associated with the charging, relevant to sponsored programs.
• EXPENDITURE_ORGANIZATION_ID — the organization unit that owns the expenditure.
• EXPENDITURE_TYPE — the expenditure type used to classify the cost.
• TOTAL_AMOUNT — the monetary amount assigned to the effort line.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard audit columns tracking who created and modified the record and when.

The surrogate primary key columns (EFFORT_REPORT_ID, VERSION_NUM, EFFORT_REPORT_LINE_NUM) should not be confused with the business-key candidate enforced by the unique index, which serves the same logical uniqueness but is a separate index object.

Common Use Cases and Queries

Typical scenarios include reconciling effort report detail lines to costed payroll distributions, auditing charging account assignments, and generating effort summary reports by employee, project, or award. A frequent reporting pattern joins the detail table to its parent effort report to filter by version:

• Summary of total effort amounts by project: SELECT PROJECT_ID, SUM(TOTAL_AMOUNT) FROM PSP_EFFORT_REPORT_DETAILS WHERE EFFORT_REPORT_ID = :id GROUP BY PROJECT_ID;
• Detail lines with their charging accounts and expenditure types: SELECT EFFORT_REPORT_LINE_NUM, GL_CODE_COMBINATION_ID, EXPENDITURE_TYPE, TOTAL_AMOUNT FROM PSP_EFFORT_REPORT_DETAILS WHERE EFFORT_REPORT_ID = :id AND VERSION_NUM = :ver;
• Award-level analysis joining AWARD_ID to IGF_AW_AWARD_ALL for sponsored activity reporting.

Because the user's search term references an "effort summary" workbook, this table is the primary source for effort summary extracts, where line-level amounts are aggregated by project, task, award, or expenditure organization to produce summary reports.

Related Objects

The table is tightly integrated through foreign keys with several core EBS objects:

• PSP_EFFORT_REPORTS — parent report keyed by EFFORT_REPORT_ID and VERSION_NUM.
• PSP_EFFORT_REPORT_ELEMENTS — defines effort report elements via ELEMENT_TYPE_ID.
• GL_CODE_COMBINATIONS — the charging account referenced by GL_CODE_COMBINATION_ID.
• PA_PROJECTS_ALL — project master joined on PROJECT_ID.
• PA_TASKS — task details joined on TASK_ID.
• PA_EXPENDITURE_TYPES — expenditure type definitions referenced by ELEMENT_TYPE_ID.
• HR_ALL_ORGANIZATION_UNITS — expenditure organization joined on EXPENDITURE_ORGANIZATION_ID.
• IGF_AW_AWARD_ALL — award information joined on AWARD_ID for sponsored program tracking.

These relationships establish PSC_EFFORT_REPORT_DETAILS as the connective link between effort reports and the charging, project, and award structures that Oracle Labor Distribution relies upon.