Search Results c_sql_traceformula

Overview

APPS.SSP_SMPRPELR_XMLP_PKG is an Oracle E-Business Suite PL/SQL package that supports a concurrent program report built with Oracle XML Publisher (BI Publisher). The package name follows the standard EBS convention for XML Publisher report packages, where a server-side PL/SQL package encapsulates report-level parameters, anchored data columns, and formula functions invoked by the report template or by the Oracle Reports/XML Publisher engine.

The $Header value indicates that the package source file is SMPRPELRS.pls, version 120.0, last updated in December 2007. The package belongs to the Oracle Payroll reporting family (the SSP_ prefix and SMPRPELR identifier suggest a payroll-related listing report). Its primary function is to retrieve payroll, consolidation set, time period, and business group context values that drive report parameters and layout, and to format those values for display in the report header and body. The package is owned by APPS and is classified as OTHER, meaning it is an internal supporting object rather than a public API.

Key Procedures and Functions

The package exposes eleven documented procedures and functions:

• BEFOREREPORT — Executed before the report body is rendered. It establishes report context, resolves parameter defaults, and prepares the data columns returned to the XML template.
• P_SORT_OPTIONVALIDTRIGGER — A validation trigger associated with the SORT_OPTION parameter. It enforces that the supplied sort option is one of the permitted values before execution proceeds.
• C_SQL_TRACEFORMULA — A formula column function. In XML Publisher report packages, the SQL_TRACE formula is conventionally used to capture or emit trace/debug information for the report's SQL, aiding diagnosis of query execution when a report fails or returns unexpected results.
• AFTERREPORT — Executed after the report body completes, handling any post-processing or cleanup required at the end of the report run.
• 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 — Accessor functions returning the values of the corresponding anchored columns. These supply business group name, report subtitle, payroll name, consolidation set name, time period name, and sort option text to the report template.
• DERIVE_SORT_CRITERIA — An additional function (attributed in the source header to "Modified By Raj") that builds the derived sort criterion string applied to the report query based on user selection.

Tables Accessed

The package references two tables through APPS synonyms:

• PAY_CONSOLIDATION_SETS — Read to resolve the consolidation set identifier into the consolidation set name displayed in the report, supporting payroll grouping and consolidation reporting.
• PER_TIME_PERIODS — Read to resolve the time period identifier into a reportable period name and associated date range, ensuring the report reflects the correct payroll period context.

These lookups align with the parameters P_CONSOLIDATION_SET and P_TIME_PERIOD_ID declared in the package specification.

Usage Notes

This package is not a public API and is documented as referenced by no other packages. It is invoked indirectly by the concurrent program associated with the XML Publisher report definition, which passes user-entered parameters (business group, session date, payroll, consolidation set, time period, sort option) into the package. Because the C_SQL_TRACEFORMULA function is part of this package, administrators investigating report performance or failures typically look here when diagnosing the SQL trace behavior of the payroll report. Customizations should be avoided; the package is regenerated from its source (.pls) file during patching, so any direct modification would be overwritten.