Search Results pay_us_xdo_report




Overview
Explore in AI Chat

Overview

APPS.PAY_US_XDO_REPORT is a PL/SQL package body in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 releases that supports the generation of the US Payroll XDO (eXtensible Document Output) report. This report is a statutory and operational payroll deliverable for United States payroll processing, producing structured output that can be rendered in multiple formats, including XML-based document templates, for archival, distribution, or downstream tax and audit reporting. The package is classified as an OTHER API in the ETRM 12.2.2 metadata, indicating that it is intended primarily for internal use by the payroll reporting framework rather than as a published integration interface.

The package encapsulates the data extraction, aggregation, and large-object transformation logic required to assemble payroll results into a report-ready structure. It bridges payroll data stored in transactional tables with Oracle's large object (LOB) facilities so that the final report payload can be emitted as a CLOB or BLOB, which is a prerequisite for XDO/BI Publisher style output. Given that the package body is VALID and is not referenced by any database object, it is a terminal node in the dependency graph — it consumes data and public utilities but is not depended upon by other PL/SQL units.

Key Procedures and Functions

The ETRM metadata documents three routines within this package body:

  • POPULATE_GTN_REPORT_DATA — The principal data-gathering routine. It selects, filters, and consolidates US payroll results for the group term life (GTN) reporting context and stages the results in an internal structure suitable for report emission. This procedure embodies the business logic that determines which payroll runs, consolidation sets, and totals are included.
  • WRITE_TO_CLOB — A helper routine responsible for writing textual or serialized report content into a CLOB (Character Large Object). It is used to accumulate report fragments or finalized character-based output prior to publication.
  • CLOB_TO_BLOB — A conversion routine that transforms character CLOB content into binary BLOB data. This is required where the report consumer or template engine requires a binary payload, or where character-set conversion must be applied before the report is persisted or delivered.

The presence of these three routines confirms a design pattern of extract-and-render, where payroll data is first gathered, then serialized into a character buffer, and finally converted into a binary form as needed by the output layer.

Tables Accessed

The package body references the following tables and objects through APPS synonyms:

  • PAY_PAYROLL_ACTIONS — Provides information on executed payroll runs, which are used to scope the report to a specific payroll period or action.
  • PAY_PAYROLLS_F — Supplies the payroll definition records that qualify the payrolls included in the US report.
  • PAY_CONSOLIDATION_SETS — Used to group multiple payrolls into a consolidation set so that the report can present aggregated results across payrolls.
  • PAY_US_RPT_TOTALS — The primary US payroll reporting totals table, from which the aggregated report figures are drawn.
  • DBMS_LOB, PLITBLM, UTL_RAW — Public Oracle utilities leveraged for LOB manipulation and raw-to-character conversion, supporting the WRITE_TO_CLOB and CLOB_TO_BLOB operations.

Additional dependencies include HR_GENERAL, HR_ORGANIZATION_UNITS, HR_REPORTS, HR_UTILITY, and PAY_PAYGTN_PKG, which provide organization, reporting, and payment-related context used during population of the report data.

Usage Notes

Because the package is classified as OTHER and is not referenced by any other database object, it is not exposed as a public API. It is expected to be invoked indirectly through the Oracle Payroll reporting framework — typically as the data engine behind a concurrent program or an XDO/BI Publisher report definition that presents US payroll results. Administrative users trigger the report from the Submit Requests form, while technical users may reference the package when troubleshooting payroll report output or when extending the standard US payroll report set.

Customizations should generally avoid modifying this package body directly. Instead, implementers should wrap or supersede its logic in a separate custom package to preserve upgrade safety across EBS 12.1.1 and 12.2.2. Any direct call should account for the LOB conversion behavior provided by WRITE_TO_CLOB and CLOB_TO_BLOB, since the final output format depends on which routine is exercised.