Search Results where_optional




Overview
Explore in AI Chat

Overview

APPS.PAY_PAYRPVPR_XMLP_PKG is the generated PL/SQL package body that supports the Oracle Payroll XML Publisher concurrent program PAYRPVPR (Payment Register / Payment Value by Payroll report). In Oracle EBS 12.1.1 and 12.2.2, packages bearing the _XMLP_PKG suffix are produced automatically when a report is defined against an XML Publisher data template; the package body encapsulates the report's Before Report, After Report, and formula functions, and it constructs the dynamic WHERE and ORDER BY fragments that the report's SQL query consumes at runtime.

The package is owned by APPS and is classified as OTHER in the ETRM repository. It is a report-support object rather than a callable business API, and it is referenced by no other packages: its only consumer is the Oracle Reports/XML Publisher runtime that executes the concurrent program. The user search term "p_uk_us" corresponds directly to a package-level variable populated in BEFOREREPORT and exposed through P_UK_USFORMULA; it carries the legislation code (for example 'US' or 'UK') of the business group selected when the report is submitted, and it drives the report's legislative branching.

Key Procedures and Functions

  • AFTERPFORM — Post-formula trigger executed by the Reports runtime. It initializes the ORDER_OPTIONAL and WHERE_OPTIONAL variables to a single space when they are null, ensuring that the concatenation logic performed later in BEFOREREPORT always operates on a valid string.
  • BEFOREREPORT — The principal initialization routine. It checks PAY_ACTION_PARAMETERS for a TRACE parameter set to 'Y' and, if found, enables SQL trace for the session. It then obtains the session date, derives P_UK_US from PER_BUSINESS_GROUPS using P_BUSINESS_GROUP_ID, resolves the business group name, appends the appropriate GRE ordering clause for US legislation, decodes the payment type lookup value ('CHECK' versus 'CHEQUE' depending on legislation), and builds the WHERE_OPTIONAL clause from the payroll, consolidation set, and tax unit parameters.
  • AFTERREPORT — Post-report trigger, typically used to release session-level settings or perform cleanup once the report output has been produced.
  • C_PAYMENT_TYPEFORMULA — Formula function that returns the payment type value passed to it, used by the report layout to display the decoded payment method description.
  • C_BUSINESS_GROUP_NAME_P — PL/SQL formula column that exposes the resolved business group name to the report.
  • C_PAYROLL_NAME_P — Formula column returning the payroll name for display on the report output.
  • C_CONSOLIDATION_SET_NAME_P — Formula column returning the consolidation set name associated with the reported payments.
  • C_GRE_NAME_P — Formula column returning the GRE (Group Reporting Entity) name, relevant when the US legislative ordering clause is applied.
  • C_SESSION_DATE_P — Formula column returning the session date captured in BEFOREREPORT.
  • P_UK_USFORMULA — Formula function that exposes the legislation code held in P_UK_US, allowing the report layout to conditionally render legislative-specific sections.

Tables Accessed

The package reads PAY_CONSOLIDATION_SETS (through its APPS synonym) to support the consolidation set name formula and to validate the consolidation set parameter supplied at submission. Beyond the ETRM-documented table, the source excerpt shows direct queries against PAY_ACTION_PARAMETERS (TRACE parameter lookup), PER_BUSINESS_GROUPS (legislation code resolution by BUSINESS_GROUP_ID), and SYS.DUAL (session date). The report's main query references payroll action and assignment tables aliased ppa1, paa2, and ppa3 within the dynamically assembled WHERE_OPTIONAL clause, joining by payroll, consolidation set, and tax unit identifiers.

Usage Notes

PAY_PAYRPVPR_XMLP_PKG is never called directly from a form or custom code. It is invoked implicitly by the Oracle Payroll Payment Register/Value by Payroll concurrent program when the report is run, with the Reports runtime calling BEFOREREPORT, the formula functions for each retrieved row, and AFTERREPORT at termination. Because P_UK_US is a package global set during BEFOREREPORT, its value is scoped to the single concurrent request execution and must not be treated as persistent state. The report is typically submitted with parameters for business group, payroll, consolidation set, and tax unit; null values for the latter three simply omit the corresponding predicate from WHERE_OPTIONAL. The TRACE action parameter provides a supported diagnostic path for enabling SQL trace during troubleshooting. Customizations should be directed at the underlying XML Publisher template or data definition rather than at this generated package, since regenerating the report definition will overwrite the package body.