Search Results p_where_item

Overview

PO_POXVCVAR_XMLP_PKG is a package belonging to the APPS schema in Oracle E-Business Suite, classified under the API classification OTHER. It serves as the supporting PL/SQL container for the POXVCVAR Oracle Reports concurrent program, which produces the Purchasing Value by Category, Vendor, or Item reporting output. Specifically, this package holds the global variable definitions, lexical bind variables, and formula functions required by the corresponding XML Publisher (BI Publisher) report definition. The internal header comment released with version 120.1 in December 2007 confirms that the source file POXVCVARS.pls is shipped as part of the standard Purchasing module.

The package is declared with AUTHID CURRENT_USER, meaning that its SQL statements execute under the privileges of the invoker rather than the definer, which is standard practice for report-support packages in EBS. The package exposes the report's runtime parameter values as public package variables, allowing the report template and the report trigger logic to reference them consistently throughout the execution lifecycle.

Key Procedures and Functions

The package defines several global variables along with five documented program units. The variables include the standard concurrent request identifier (P_CONC_REQUEST_ID) and report title, plus a large set of report parameters such as P_INVENTORY_ITEM_ID_FROM, P_INVENTORY_ITEM_ID_TO, P_CREATION_DATE_FROM, P_CREATION_DATE_TO, P_FLEX_ITEM, P_ITEM_STRUCT_NUM, P_BASE_CURRENCY, P_ORGANIZATION_ID, and P_ASSIGNMENT_SET_ID. The parameter P_WHERE_ITEM is declared as varchar2(2000) and is the principal subject of user searches; it carries the dynamically constructed WHERE clause fragment used to restrict the item-level extraction in the report query. Related parameters P_item_from and P_item_to (varchar2(900) each) hold the formatted concatenated item range descriptions displayed on the report output.

• BeforeReport — the report-level trigger function executed prior to the main query. It performs initialization and validation of the report parameters before data retrieval begins.
• P_titleValidTrigger — a validation trigger function associated with the report title parameter, ensuring the supplied value conforms to the expected format before the report proceeds.
• AfterReport — the report-level trigger function invoked after the main query has completed, used for cleanup or post-processing activities.
• get_actual — a formula function that accepts expenditure and vendor totals and returns a computed numeric value, supporting calculations shown in the report layout.
• c_intendedformula — a formula function that accepts a split value and an expenditure total and returns a numeric result, used to derive an intended-percentage or apportioned figure in the report columns.

Tables Accessed

The documented table reference for this package is MTL_PARAMETERS, accessed through an APPS synonym. MTL_PARAMETERS is the Inventory organization parameters table, and the package queries it to validate the organization context supplied through P_ORGANIZATION_ID and to derive the corresponding organization code held in P_ORGANIZATION_CODE and CP_ORGANIZATION_CODE. This validation ensures that the item range and category selections are evaluated against a valid inventory organization before the report extracts purchasing data.

Usage Notes

This package is invoked indirectly whenever the POXVCVAR concurrent program is run from the Purchasing responsibility or submitted programmatically. The report parameters, including P_WHERE_ITEM, are populated by the concurrent program definition and by the standard report parameter form; the package does not expose a callable PL/SQL API for external consumption and is not referenced by any other documented package. Customizations that alter item selection criteria should therefore be directed at the report parameter logic or the P_WHERE_ITEM construction rather than at the package body, since Oracle documents this unit as report-support infrastructure only. Because the package is compiled AUTHID CURRENT_USER and references MTL_PARAMETERS via an APPS synonym, any custom code that executes it must run under a responsibility whose user has the necessary Inventory and Purchasing security privileges.