Search Results psp_eff_report_approvals




Overview
Explore in AI Chat

Overview

PSP_EFF_REPORT_APPROVALS is a transactional table in the PSP (Labor Distribution) product of Oracle E-Business Suite, documented as VALID in releases 12.1.1 and 12.2.2. It stores the approval details associated with the Oracle Workflow processes that are initiated when an effort report is routed for certification and sign-off. Each row represents a single approver's participation in the effort reporting workflow for a given effort report line, capturing the workflow role, the routing sequence, the approver's response, and the resultant approval status. The table therefore acts as the persistent record of who was asked to approve effort, in what order, when they responded, and what the outcome was.

Heuristically mined from its foreign key structure, the table classifies as a standalone object in a Data Vault modeling suggestion. In practice this means it is not a pure hub, link, or satellite of a shared business key, but rather a self-contained transactional entity anchored by its own surrogate identifier and linked to labor distribution effort reporting data by a detail-level foreign key.

Key Information Stored

The table contains 57 documented columns. The most significant are:

Common Use Cases and Queries

The table is commonly queried to audit effort report certification, identify pending approvers, and reconstruct the approver sequence for a given report line.

  • Listing all approvals for an effort report detail, ordered by routing sequence:

    SELECT EFFORT_REPORT_APPROVAL_ID, WF_ROLE_NAME, APPROVER_ORDER_NUM, APPROVAL_STATUS, RESPONSE_DATE FROM PSP.PSP_EFF_REPORT_APPROVALS WHERE EFFORT_REPORT_DETAIL_ID = :detail_id ORDER BY APPROVER_ORDER_NUM;

  • Identifying outstanding approvals by status for follow-up on stalled Workflow processes.
  • Joining to Workflow notification history through WF_ITEM_KEY or NOTIFICATION_ID to trace the routing lifecycle.
  • Reporting on overridden effort percentages and cost share captured during approval.
  • Populating descriptive flexfield reporting through the PERA_INFORMATION and EFF_INFORMATION segment columns.

Related Objects

  • PSP_EFFORT_REPORT_DETAILS — joined via EFFORT_REPORT_DETAIL_ID, the parent effort report line for each approval.
  • WF_ITEMS — joined via WF_ITEM_KEY, the Workflow process instance record.
  • WF_NOTIFICATIONS — joined via NOTIFICATION_ID, the Workflow notification sent to the approver.
  • WF_ROLES — joined via WF_ROLE_NAME, resolving approver role definitions.
  • FND_USER — the approver identity behind the assigned Workflow role.
  • HR_ALL_ORGANIZATION_UNITS and PER_ALL_PEOPLE_F — referenced when resolving organizational and person context for labor distribution effort reporting.