Search Results p_awd_template_id

Overview

APPS.PSP_PSPENAWD_XMLP_PKG is the generated PL/SQL package body that backs the Oracle E-Business Suite XML Publisher (BI Publisher) report commonly known as the Encumbered Award Report within the Oracle ETRM (Enterprise Project and Portfolio Management) module. Its purpose is to supply the report's data model logic, parameter validation, and formula columns that are triggered during report execution. The package is classified as OTHER in the ETRM metadata, meaning it is not part of a formal public API surface but is instead a report-specific implementation object. The package header declares the formatting, validation, and column value functions referenced by the report's XML template, while the package body contains their implementations. It operates strictly within the APPS schema and depends on standard ETRM and HR foundation tables. Version 120.7 (last modified in 2008) confirms this is a stable, long-lived report component carried forward through Oracle EBS 12.1.1 and 12.2.2.

Key Procedures and Functions

The documented API surface contains twelve procedures and functions. A brief characterization of each follows:

• AFTERPFORM — Executes after the report's runtime parameter form values are submitted, and is responsible for constructing dynamic SQL fragments used by the report queries. It is the entry point where the user-supplied p_awd_template_id and payment template parameters are resolved against selection criteria definitions.
• BEFOREREPORT — Executes before the main report query runs, providing initialization logic for reporting run-time state.
• AFTERREPORT — Executes after the report data model completes, providing cleanup or finalization logic.
• CF_CHARGING_INSTFORMULA — A formula column that resolves and returns a descriptive charging instruction string based on project, task, award, expenditure organization, and expenditure type inputs.
• CF_AWD_END_DTFORMULA — A formula column that derives and formats the award end date for display.
• CF_DATE_CHECKFORMULA — A formula column that evaluates date-related validation within each report row.
• CF_END_DATE_RANGEFORMULA — A formula column that determines the applicable end-date range for the row.
• CF_AWARD_TOTAL_DSPFORMULA — A formula column that computes the display value for award-level totals.
• CF_PELH_ENC_AMOUNT_DSPFORMULA — A formula column that computes the displayed encumbered amount from the project encumbrance ledger history row.
• CF_PAY_TOTAL_DSPFORMULA — A formula column that computes the display value for payroll-level totals.
• CF_CURRENCY_FORMATFORMULA — A formula column that formats monetary values for a single currency display.
• CF_CURRENCY_TOTAL_DSPFORMULA — A formula column that formats aggregate monetary totals across currencies.

Tables Accessed

The package reads from a defined set of ETRM and HR tables via APPS synonyms:

• PSP_REPORT_TEMPLATE_DETAILS — The core configuration table. It stores report template selection criteria keyed by TEMPLATE_ID, CRITERIA_LOOKUP_TYPE, and CRITERIA_LOOKUP_CODE. The AFTERPFORM function queries this table to translate the user's template ID into concrete award and payroll ID filters.
• GMS_AWARDS and GMS_AWARDS_ALL — Award master data used both for filtering awards and for resolving award descriptors such as award numbers and end dates.
• PA_PROJECTS_ALL — Project master data, consumed by the charging-instruction formula to resolve project name and related attributes.
• HR_ALL_ORGANIZATION_UNITS and HR_ALL_ORGANIZATION_UNITS_TL — Organization unit definitions and their translated names, used to resolve expenditure organization names for display.

The package performs read-only access; no inserts, updates, or deletes are documented.

Usage Notes

This package is not invoked by other packages (referenced by 0 packages per the ETRM metadata). It is executed exclusively by the Oracle XML Publisher/BI Publisher engine when the Encumbered Award Report is run, either as a concurrent program or through the standardized report submission UI. The p_awd_template_id parameter that users search for is resolved inside the AFTERPFORM function: if the value is null, a pass-through predicate of "and 1 = 1" is applied; otherwise the package queries PSP_REPORT_TEMPLATE_DETAILS for an 'AWD' selection criterion associated with that template and substitutes a dynamic subquery restricting GMS_AWARDS_ALL.award_id to the configured criteria values. An analogous mechanism applies to the payment template parameter.

Because the package embeds string-concatenated SQL, administrators should ensure that template IDs are supplied only through the report's validated parameter form. Upgrade or patch activity should follow Oracle's standard procedures for repackaging XML Publisher report objects so that the generated body remains synchronized with its specification in both 12.1.1 and 12.2.2 environments.