Search Results psp_efforts_pkg




Overview
Explore in AI Chat

Overview

PSP_EFFORTS_PKG is a PL/SQL package owned by APPS in the Oracle E-Business Suite, classified as an "OTHER" API. It supports the Effort Reporting functionality within Oracle Projects (PSP), which is used by organizations — particularly in government contracting, higher education, and grant-funded environments — to certify the percentage of effort that personnel devote to sponsored projects. The package header identifies two primary procedures, CRT and INIT_WORKFLOW, along with additional utilities documented in the ETRM 12.2.2 metadata. The package is declared with AUTHID CURRENT_USER, meaning its SQL executes with the privileges of the invoking user, and it references a broad set of PSP and HR tables to reconcile reported effort against actual labor distributions.

Key Procedures and Functions

  • CRT — The core effort report creation routine. It accepts the input criteria entered during Effort Report Creation, matches those criteria against actual disbursements of labor charges from the Distribution Lines, and summarizes actual effort for a person across all assignments over a period range. The summarized data is inserted into the Effort Report tables. The procedure performs extensive bookkeeping: updating distribution lines, retrieving GL and POETA values from the appropriate tables, validating active element types, and populating an error table for effort reports that have already been processed. It is invoked by the Concurrent Manager.
  • INIT_WORKFLOW — This is the procedure referenced by the search term "init_workflow." It initiates the Oracle Workflow process for effort reporting. Specifically, it fetches all report IDs associated with a given template ID and launches the workflow process for each report ID. This mechanism drives the approval and certification routing for effort reports, and it is attributed in the header comments to Venkat as the workflow addition.
  • LOG_ERRORS — Records errors encountered during package processing into the effort error table, supporting diagnostics for report creation and workflow initiation.
  • GET_GL_DESCRIPTION — Retrieves the general ledger description associated with an account, used when populating effort report detail lines with meaningful accounting context.

Tables Accessed

The package reads and writes across the PSP effort reporting, labor distribution, and HR assignment data model. Distribution data is drawn from PSP_DISTRIBUTION_LINES, PSP_DISTRIBUTION_LINES_HISTORY, PSP_PAYROLL_LINES, PSP_PAYROLL_SUB_LINES, and PSP_ADJUSTMENT_LINES_HISTORY, which together represent committed and actual labor costs. Effort report output and configuration are stored in PSP_EFFORT_REPORTS, PSP_EFFORT_REPORTS_S, PSP_EFFORT_REPORT_DETAILS, PSP_EFFORT_REPORT_TEMPLATES, and PSP_EFFORT_REPORT_ELEMENTS. Error handling writes to PSP_EFFORT_ERRORS. Supporting lookups include PER_ALL_ASSIGNMENTS_F (person assignments), PSP_ORGANIZATION_ACCOUNTS, PSP_ELEMENT_TYPE_ACCOUNTS, and PSP_DEFAULT_LABOR_SCHEDULES. The cursor declared in the specification, g_ssdseo, selects from schedule line data to drive processing.

Usage Notes

PSP_EFFORTS_PKG is not a general-purpose public API and is referenced by zero other packages. It is invoked indirectly through Oracle Projects Effort Reporting concurrent programs — principally effort report creation, executed by CRT through the Concurrent Manager, and workflow initiation, executed by INIT_WORKFLOW after reports are generated. The workflow engine then routes reports for certification. Custom code should avoid calling these procedures directly unless replicating the standard concurrent program behavior; any such call must respect the AUTHID CURRENT_USER context and the package's dependency on fully populated effort template and distribution line data.