Search Results construct_order_by

Overview

APPS.PAY_PAYHKSOE_XMLP_PKG is an Oracle E-Business Suite PL/SQL package that supports the Hong Kong Statement of Earnings (SOE) XML Publisher report. The package encapsulates the runtime logic behind a BI Publisher–based statutory and payroll reporting output for Hong Kong payroll processing, where the Statement of Earnings must present earnings, deductions, net pay, direct payments, and Mandatory Provident Fund (MPF) contributions for an employee over a defined payment period and year-to-date basis. The package version header ($Header: PAYHKSOES.pls 120.0 2007/12/13) confirms it belongs to the PAY HK SOE report family and is a report-support package rather than a business API.

The package exposes public package variables that act as report parameters and computed accumulators, alongside functions and procedures invoked by the XML Publisher report definition. It is classified as OTHER in the ETRM API classification, meaning it is not a published integration API but an internal report package. It is referenced by no other packages, so it functions as a leaf component consumed directly by the concurrent program and its XML template.

Key Procedures and Functions

The package contains 29 documented procedures and functions. The most relevant to report construction are:

• BEFOREREPORT — report-level initialization function invoked before the query executes.
• CONSTRUCT_ORDER_BY — builds the dynamic ORDER BY clause for the report query, driven by the sort-order parameters (P_SORT_ORDER_1 through P_SORT_ORDER_4) and the cp_order_by variable. This is the procedure most closely associated with the user search term construct_order_by.
• CONSTRUCT_WHERE_CLAUSE — assembles the dynamic WHERE clause that filters the SOE data set by payroll, consolidation set, payment dates, and related parameters.
• AFTERREPORT — post-processing function executed after the report completes.

Formula functions supply computed values to the report layout: CF_GET_BALANCES_TOTALSFORMULA, CF_CURRENCY_FORMAT_MASKFORMULA, CF_MPF_FLAG1FORMULA, CF_MPF_FLAG2FORMULA, and CF_NET_ACCRUALFORMULA. The CP_*_P functions (CP_TOTAL_EARNINGS_THIS_PAY_P, CP_TOTAL_DEDUCTIONS_THIS_PAY_P, CP_NET_PAY_THIS_PAY_P, CP_DIRECT_PAYMENTS_THIS_PAY_P, CP_TOTAL_PAYMENT_THIS_PAY_P and their YTD counterparts, plus CP_COUNT_P) expose the corresponding package-level accumulator variables to the report.

Tables Accessed

The documented table reference is FND_CURRENCIES, accessed via the APPS synonym. It is used to resolve currency attributes — most likely the precision and format mask applied through CF_CURRENCY_FORMAT_MASKFORMULA — so that monetary amounts in the Statement of Earnings are rendered with the correct number of decimal places for the payroll currency. All other data is sourced through the report's master query and PL/SQL computations rather than direct package-level DML; the package performs no documented inserts, updates, or deletes.

Usage Notes

PAY_PAYHKSOE_XMLP_PKG is invoked indirectly through the Oracle Payroll Hong Kong Statement of Earnings concurrent program when the report is submitted with an XML Publisher template. The report parameters P_Payroll, P_Consolidation_Set, P_Payments_From_Date, P_Payments_To_Date, P_Date_MPF_Paid_to_Trustee, P_CONC_REQUEST_ID, and P_BUSINESS_GROUP_ID populate the package variables, and the sort-order parameters drive CONSTRUCT_ORDER_BY. Because construct_order_by and construct_where_clause build SQL fragments from user-supplied sort selections, the sort values are mapped to fixed column identifiers rather than concatenated freely, which limits SQL injection risk. Custom code should not call this package directly; extensions requiring the same logic should reference the underlying payroll balance and payroll action tables instead, or invoke the standard concurrent program. The P_PAYMENTS_FROM_DATE_DISP and P_PAYMENTS_TO_DATE_DISP variables were added as a fix to carry display-formatted date values into the report output.