Search Results psp_effort_report_elements




Overview
Explore in AI Chat

Overview

The PSP_EFFORT_REPORT_ELEMENTS table is a core data structure within the Oracle E-Business Suite (EBS) Labor Distribution module (PSP). It functions as a master reference table that defines the distinct types of elements or components that can be reported on an effort report. Effort reporting is a critical process for institutions, particularly in higher education and research, to certify and document how effort (typically expressed as a percentage of time) is distributed across funded projects and other activities. This table provides the foundational catalog of element types, such as specific projects, tasks, awards, or other cost objects, against which actual labor distribution details are recorded and reported. Its role is to ensure consistency and control over what can be included in the effort certification process.

Key Information Stored

While the provided ETRM metadata does not list specific columns beyond the primary key, the table's description and relationships indicate its core data. The primary column is ELEMENT_TYPE_ID, which serves as the unique identifier (Primary Key) for each reportable element type. Based on standard EBS design patterns for such reference tables, it typically includes columns to store a code and a name or description for the element (e.g., ELEMENT_CODE, ELEMENT_NAME), along with control flags to indicate its active status and potentially its classification. The table essentially holds the controlled vocabulary of effort reporting components used across the system.

Common Use Cases and Queries

The primary use case is the setup and maintenance of valid effort reporting elements within the Labor Distribution module. Administrators would populate this table via the application's front-end forms to define the universe of elements available for reporting. For reporting and data validation, common SQL queries involve joining this master table to transactional details. A fundamental query retrieves all active elements available for use:

  • SELECT element_type_id, element_code, element_name FROM psp_effort_report_elements WHERE enabled_flag = 'Y' ORDER BY element_code;

Another critical use is in effort report generation, where the element descriptions from this table are joined to detail lines to produce human-readable reports:

  • SELECT det.report_header_id, det.element_type_id, ele.element_name, det.percentage FROM psp_effort_report_details det, psp_effort_report_elements ele WHERE det.element_type_id = ele.element_type_id AND det.report_header_id = :p_header_id;

Related Objects

The table has a direct and essential relationship with the primary transactional table for effort reporting details, as documented in the provided metadata.

  • PSP_EFFORT_REPORT_DETAILS: This is the main transactional table that stores the actual effort percentages reported for specific elements on an effort report. It references PSP_EFFORT_REPORT_ELEMENTS via a foreign key constraint. The join is performed on the ELEMENT_TYPE_ID column (PSP_EFFORT_REPORT_DETAILS.ELEMENT_TYPE_ID → PSP_EFFORT_REPORT_ELEMENTS.ELEMENT_TYPE_ID). This relationship ensures that every detail line in an effort report points to a valid, pre-defined element type.