Search Results get_supervisor

Overview

APPS.PSP_WF_ADJ_PKG is a PL/SQL package body that supports approval workflow processing for payroll distribution adjustments in Oracle EBS Payroll (PSP) module. The acronym "WF" in the package name denotes its close relationship to the Oracle Workflow engine. The package orchestrates the human approval lifecycle for distribution adjustment transactions: identifying the appropriate supervisor, dispatching workflow notifications, recording approver responses, and finalising or undoing adjustments based on those responses. The source header indicates an active lifecycle through file version 120.0.12010000.2 (August 2008), and the code carries explicit references to Bug 1994421, which introduced the "Employee assignments with Zero work days" enhancement — a notable driver of change in the package's cursor logic. The package is classified as API type OTHER in the ETRM metadata and is referenced by one other package, indicating it is consumed programmatically rather than directly by an end-user form.

Key Procedures and Functions

• INIT_WORKFLOW — Initialises the workflow process for a distribution adjustment, setting up the workflow item and associated runtime context prior to approval routing.
• SELECT_APPROVER — Determines and selects the approver who must act on the adjustment, typically resolving the supervisor chain derived from the assignment data.
• GET_APPROVAL_RESPONDER — Retrieves the responder associated with an outstanding approval notification, supporting subsequent decision handling.
• RECORD_APPROVER — Persists the identity of the approver against the adjustment control record for audit and downstream processing.
• RECORD_CREATOR — Captures the creator of the adjustment, supporting segregation-of-duties and notification targeting.
• OMIT_APPROVAL — Bypasses the approval step when the business rules dictate that approval is not required, allowing the adjustment to proceed.
• UNDO_DISTRIBUTION_ADJUSTMENT — Reverses a previously applied distribution adjustment, restoring prior state when approval fails or the adjustment is rescinded.

The source excerpt additionally documents GET_SUPERVISOR, which returns the supervisor person ID for a given assignment. When no supervisor is found, it returns -1. The cursor was rewritten for Bug 1994421 to select the row with the maximum effective end date per assignment, rather than the row covering SYSDATE — reflecting the zero-work-days enhancement.

Tables Accessed

• WF_ITEMS, WF_ITEM_ACTIVITY_STATUSES, WF_NOTIFICATIONS, WF_PROCESS_ACTIVITIES — workflow runtime tables supporting item creation, activity status tracking, notification dispatch, and process definition lookup.
• PER_ALL_PEOPLE_F and PER_ASSIGNMENTS_F — HR tables used to resolve supervisor relationships via assignment records.
• PSP_ADJUSTMENT_CONTROL_TABLE and PSP_PAYROLL_CONTROLS — the adjustment and payroll control records that carry approval state.
• FND_USER — resolves application user identity for approver and creator mapping.

Usage Notes

PSP_WF_ADJ_PKG is invoked primarily from Oracle Workflow process definitions that drive the distribution adjustment approval cycle. The Workflow engine calls the package procedures as activity functions within the process, and the package in turn queries the adjustment control and payroll control records to determine routing. Because it is referenced by one other package, custom extensions may also call these procedures directly. Typical invocation occurs when a distribution adjustment is submitted, when a notification is responded to, or when the approval outcome requires the adjustment to be finalised or undone.