Search Results get_input_value_id

Overview

PAY_NL_WTS_REPORT is an Oracle E-Business Suite PL/SQL package body owned by the APPS schema and classified under the ETRM taxonomy as OTHER. It supports Dutch (NL) statutory payroll reporting obligations, specifically the reporting of the Wage Tax Subsidy (Wet tegemoetkoming loonkosten, or WTL) regime and its predecessor schemes such as the Wage Tax Subsidy for Low Wages, Education, Long Term Unemployed, and Paid Parental Leave. The package does not deliver a general-purpose API; rather, it encapsulates the query and extraction logic required to identify qualifying payroll actions, assignment actions, and run results for these subsidy categories so that downstream reporting can be produced accurately and consistently.

The package is catalogued in the ETRM metadata for Oracle EBS 12.1.1 and 12.2.2, and the source header indicates a last revision shipped with the 120.8 file version (paynlwts.pkb 120.8 2012/01/19), consistent with the 12.1.x and 12.2.x code lines. No other package in the documented inventory references PAY_NL_WTS_REPORT, confirming that it is invoked as a top-level reporting unit rather than as a shared utility.

Key Procedures and Functions

The ETRM metadata records ten documented procedures and functions within the package, though only a subset is exposed in the excerpt. The central function described is GET_LAST_ASG_ACT_ID, which returns the most recent assignment action identifier for a given person, payroll, and date earned. It accepts the person identifier, payroll identifier, and date earned, and returns a numeric assignment action identifier. Internally it opens an explicit cursor that joins assignment actions, payroll actions, and assignment records, restricting results to completed actions (action_status = 'C') of run, quick pay, reversal, balance adjustment, or initialization types ('R','Q','V','B','I'), and filters on the existence of run results whose element types correspond to the WTL subsidy categories resolved via Get_Element_Type_Id.

The package also exposes TO and RECORD_4712 per the documented procedure listing. These names indicate conversion or record-mapping helpers used to shape the extracted data into the required reporting structure. Parameter lists are not reproduced here; only their documented purpose is described. The repeated invocation of Get_Element_Type_Id within the cursor predicate demonstrates that the package resolves element type identifiers by name at runtime rather than hard-coding identifiers, which is the standard Oracle Payroll practice that preserves portability across environments.

Tables Accessed

The package reads from a broad set of Payroll and HR tables through APPS synonyms. PAY_ASSIGNMENT_ACTIONS and PAY_PAYROLL_ACTIONS provide the action-level context and status required to select completed, eligible payroll runs. PER_ALL_ASSIGNMENTS_F supplies the person-to-payroll and assignment-to-person relationships, including the effective dating range used to correlate the date earned with the assignment's active period. PAY_RUN_RESULTS is used in an existence check to confirm that the assignment action actually produced results for one of the WTL element types. PAY_ELEMENT_TYPES_F and PAY_INPUT_VALUES_F support element type resolution and input value retrieval, while PAY_BALANCE_TYPES, PAY_BALANCE_DIMENSIONS, and PAY_DEFINED_BALANCES are referenced for balance-based verification and reporting. PAY_ALL_PAYROLLS_F provides payroll definitions. Organizational context is drawn from HR_ORGANIZATION_INFORMATION, PER_ORG_STRUCTURE_ELEMENTS, and PER_ORG_STRUCTURE_VERSIONS, which allow the report to attribute subsidy amounts to the correct legal employer or organization hierarchy. FND_LOBS and PER_GB_XDO_TEMPLATES indicate the use of XML Publisher or BI Publisher templates and their associated LOB storage for report output.

Usage Notes

PAY_NL_WTS_REPORT is typically invoked from a concurrent program or a Dutch statutory reporting process rather than interactively from a form, since its logic is oriented toward scheduled extraction of payroll results. Because it resolves element types by name using Get_Element_Type_Id, the required WTL element names must exist in the target environment before the package is executed; missing elements would cause the cursor predicates to fail or return no rows. The function GET_LAST_ASG_ACT_ID is designed to return the single most recent qualifying action, ordering by assignment action identifier descending, which makes it suitable for point-in-time reporting where only the latest processed result should be considered. Custom code should treat the package as read-only with respect to payroll data, as its documented purpose is extraction and reporting, and it does not perform DML on payroll results. When extending or debugging the package, developers should verify the effective dates on PER_ALL_ASSIGNMENTS_F and the action types included in the predicate, since changes to payroll action types or element naming in later releases can alter which assignments are selected.