Search Results cp_where_clause

Overview

APPS.PAY_PAYHKSOE_XMLP_PKG is the Oracle E-Business Suite PL/SQL package body that supports the Payroll Hong Kong Statement of Earnings (SOE) XML Publisher report. The object is classified under ETRM as API classification OTHER, and the header comment references PAYHKSOEB.pls (version 120.0, dated 2007/12/13), confirming it is a report-specific package rather than a general-purpose payroll API. Its purpose is to assemble the runtime data structures required by the SOE concurrent program before the XML Publisher template renders the output.

The package populates global package variables (prefixed cp_, denoting report-level compute/parameter values) that are bound into the report's data model: display-formatted date parameters, a dynamically constructed WHERE clause, a dynamically constructed ORDER BY clause, and a set of calculated totals for earnings, deductions, net pay, direct payments, and payment totals across both the current pay period and year-to-date.

Key Procedures and Functions

Twenty-nine documented procedures and functions are present. The most significant are:

• BEFOREREPORT — The report entry point. It converts the payment from/to date parameters into displayed text (DD-MON-YYYY), then calls CONSTRUCT_WHERE_CLAUSE and CONSTRUCT_ORDER_BY to prepare query fragments before returning TRUE.
• CONSTRUCT_WHERE_CLAUSE — Builds the cp_where_clause global. It initialises the clause to a single space, then appends and Consolidation_Set_ID = <value> when the consolidation set parameter is supplied, and and Payroll_ID = <value> when the payroll parameter is supplied. This is the exact procedure associated with the search term "cp_where_clause".
• CONSTRUCT_ORDER_BY — Builds cp_order_by by concatenating the up to four sort-order parameters, always terminating with , Assignment_ID as a deterministic tie-breaker.
• AFTERREPORT — Post-report cleanup routine.
• CF_GET_BALANCES_TOTALSFORMULA — A formula-column function delegating to pay_hk_soe_pkg.balance_totals, passing the assignment action ID and tax unit ID and returning a balance amount used in the totals columns.
• CF_CURRENCY_FORMAT_MASKFORMULA — Derives the currency formatting mask for display.
• CF_MPF_FLAG1FORMULA / CF_MPF_FLAG2FORMULA — Derive Mandatory Provident Fund indicator flags for Hong Kong statutory reporting.
• CF_NET_ACCRUALFORMULA — Computes the net accrual figure.
• Totals placeholders — 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 the corresponding _YTD_P variables (earnings, deductions, net pay, direct payments, total payment), plus CP_COUNT_P, hold aggregated values consumed by the report layout.

Tables Accessed

The only documented table referenced through APPS synonyms is FND_CURRENCIES, read to obtain currency information for formatting monetary amounts on the statement of earnings. Payroll balance data is not read directly by this package; it is retrieved through the companion package PAY_HK_SOE_PKG via the CF_GET_BALANCES_TOTALSFORMULA call. The report's main queries are driven by the dynamically generated cp_where_clause and cp_order_by fragments bound into the XML Publisher data definition.

Usage Notes

This package is invoked automatically by the Oracle Reports/XML Publisher runtime when the Hong Kong Statement of Earnings concurrent program is executed; it is not intended for direct invocation from forms or custom code. Because it is documented as referenced by zero other packages, it functions as a leaf-level report support unit. No package state persists between runs, and no public DML API is exposed. Customisations should be confined to the report definition or to PAY_HK_SOE_PKG, since changes to CONSTRUCT_WHERE_CLAUSE or CONSTRUCT_ORDER_BY directly alter the report's result set, and the ORDER BY clause relies on Assignment_ID being present in the underlying query.