Search Results derive_sort_criteria

Overview

SSP_SSPRPELR_XMLP_PKG is the generated PL/SQL package body that backs the Oracle EBS concurrent program "Payroll Element Results" (report short name SSPRPELR), an XML Publisher–based report used in the Oracle Payroll module. The package belongs to the APPS schema and is classified as OTHER under ETRM, meaning it is a report-support package rather than a public API. Its principal role is to perform report-level initialization, resolve display names for report parameters, and derive the SQL ORDER BY clause used by the underlying report query. The package is compiled from SSPRPELRB.pls, with a header reference indicating a shipped version dated 2007.

In Oracle EBS 12.1.1 and 12.2.2, this package is invoked automatically by the Oracle Reports runtime through the standard XMLP placeholder mechanism. It is not intended for direct invocation by customers or custom code.

Key Procedures and Functions

• BEFOREREPORT — Executes before the report query runs. It calls the sort-option trigger, assigns the derived sort criteria to the report variable, and populates the display variables for payroll name, business group name, time period name, and consolidation set name by querying the corresponding tables. A NO_DATA_FOUND exception handler silently ignores missing lookups.
• P_SORT_OPTIONVALIDTRIGGER — The validation trigger tied to the P_SORT_OPTION parameter. It calls DERIVE_SORT_CRITERIA and stores the result in P_SORT_OPTION_1, ensuring the selected sort option maps to a valid ORDER BY expression.
• AFTERREPORT — Executes after the report completes; in this package it is a no-op returning TRUE.
• DERIVE_SORT_CRITERIA — Returns the ORDER BY clause based on the P_SORT_OPTION value: "Assignment Number" or an unrecognized value yields "Order By assignment_number", while "Employee Name" yields "Order By employee_name,assignment_number".
• C_BUSINESS_GROUP_NAME_P, C_REPORT_SUBTITLE_P, C_PAYROLL_NAME_P, C_CONSOLIDATION_SET_NAME_P, C_TIME_PERIOD_NAME_P, C_SORT_OPTION_P — Placeholder accessor functions that return the corresponding package-level variables to the XML Publisher template.

Tables Accessed

• PAY_PAYROLLS_X (via PAY_PAYROLLS_X) — Read in BEFOREREPORT to resolve the payroll name from P_PAYROLL_ID.
• PER_TIME_PERIODS — Read to resolve the time period name from P_TIME_PERIOD_ID.
• PAY_CONSOLIDATION_SETS — Read to resolve the consolidation set name from P_CONSOLIDATION_SET.
• HR_REPORTS.GET_BUSINESS_GROUP — Called to derive the business group name from P_BUSINESS_GROUP_ID.

Usage Notes

The package is bound to the SSPRPELR concurrent program definition in Oracle Payroll and is executed when users submit the "Payroll Element Results" report. It is referenced by no other packages, confirming it is a leaf-level report support object. Because the SRWINIT and SRWEXIT user exits are commented out, responsibility for session initialization rests with the concurrent manager environment rather than the report itself. Customizations should avoid editing the generated body directly; instead, use the report's parameter and template configuration. Any modification requires recompilation into APPS and the appropriate concurrent program cache refresh.