Search Results get_participant_status_code

Overview

APPS.PQP_US_PENSION_EXTRACTS is a server-side PL/SQL package in Oracle E-Business Suite that supports the extraction of United States pension and benefits data from Oracle Payroll and Oracle Advanced Benefits. Its primary business purpose is to assemble pension-relevant participant, payroll, balance, and element entry information into extract records that can be transmitted to third-party pension providers, plan administrators, or regulatory bodies. The package operates within the Oracle Payroll data model, drawing on assignment actions, balance dimensions, consolidation sets, and element definitions to determine what data should be reported for a given extract run.

The package declares a substantial set of global variables, including the concurrent request identifier, legislation code, assignment action identifier, action effective date, business group, person identifier, and GRE tax unit identifier. It also defines PL/SQL collection and record types — ValTabTyp, extract_params, and t_extract_params — that hold extract parameters and value tables during processing. These structures indicate the package is designed for set-based, parameter-driven extraction driven by a concurrent request context.

Key Procedures and Functions

The documented routines cover the full extraction lifecycle:

• PENSION_EXTRACT_PROCESS — The primary driver that orchestrates the pension extract run.
• PENSION_CRITERIA_FULL_PROFILE — Applies full-profile selection criteria to determine eligible participants.
• GET_PARTICIPANT_STATUS_CODE — Derives a participant's pension status code.
• GET_INDICATIVE_DATESWITCH — Returns indicative date-switch information used in extract logic.
• GET_SIT_SEGMENT — Retrieves the State Income Tax (SIT) segment value.
• GET_DDF_DF_VALUE and GET_DDF_VALUE — Return values from Data Definition Framework (DDF) definition and value records; these commonly relate to legislative or plan-defined data elements.
• GET_DATA_ELEMENTS — Collects the data elements that make up an extract.
• GET_ELEMENT_ENTRY_VALUE — Extracts a value from an element entry for a payroll assignment.
• GET_BALANCE_VALUE — Returns a payroll balance value for a defined balance and dimension.
• GET_DEDUCTION_AMOUNT and GET_CONTR_AMTPER — Return deduction and contribution amount/period figures.
• GET_PAYROLL_DATE — Returns the relevant payroll date.
• GET_CONCPROG_INFORMATION — Retrieves concurrent program context information.
• CHECK_ASG_ACTIONS — Validates assignment actions relevant to the extract.
• DEL_SERVICE_DETAIL_RECS — Deletes prior service detail records, typically before repopulating an extract.
• GET_CURRENT_EXTRACT_RESULT and GET_CURRENT_EXTRACT_PERSON — Return the current extract result and person context.
• RAISE_EXTRACT_WARNING — Emits a warning message during extract processing.

Tables Accessed

Documented references include BEN_EXT_DFN, BEN_EXT_RCD, BEN_EXT_RSLT, and BEN_EXT_RSLT_DTL for Advanced Benefits extract definitions, records, results, and result details. HR_ALL_ORGANIZATION_UNITS supplies GRE and tax unit identifiers, while BEN_PERSON_ACTIONS provides benefit action context. Payroll tables include PAY_ASSIGNMENT_ACTIONS, PAY_BALANCE_DIMENSIONS, PAY_BALANCE_TYPES, PAY_DEFINED_BALANCES, PAY_CONSOLIDATION_SETS, PAY_ELEMENT_SETS, PAY_ELEMENT_TYPES_F, and PAY_INPUT_VALUES_F, used to resolve balances, element entries, and consolidation scope. PER_ALL_ASSIGNMENTS_F supplies assignment and person context.

Usage Notes

This package is invoked in the context of concurrent programs that run pension extracts, typically initiated from Oracle Payroll or Advanced Benefits extract setup. It is not referenced by other packages, so it functions as a top-level extract processor rather than a shared library. Custom code invoking it should supply the concurrent request, business group, and extract parameter context; the search term "get_ddf_value" corresponds to the documented DDF value routines used to retrieve plan- or legislation-defined data values during extraction.