Search Results c_subinv_whereformula




Overview
Explore in AI Chat

Overview

APPS.INV_INVIRSLO_XMLP_PKG is the generated PL/SQL package body that supports the Oracle E-Business Suite concurrent program and XML Publisher (BI Publisher) report definition for the Inventory Subinventory Listing/On-hand report (INVIRSLO). As part of the Oracle Reports-to-XML Publisher migration effort in 12.1.1 and 12.2.2, the report logic that once lived inside a .rdf Oracle Reports file was extracted into a package that exposes the report's format triggers, before-report triggers, and SQL fragment "formula" functions to the XML Publisher data template.

The package's primary business role is to prepare runtime parameters and to dynamically construct WHERE-clause fragments used by the report's underlying query. Because subinventory selection is optional and can be entered either as a single value or as a low/high range, the package encapsulates the conditional SQL generation so the same data model works regardless of how the user populates the concurrent program parameters. The package is compiled under the APPS schema and is classified as "OTHER," reflecting that it is a report-support package rather than a published API.

Key Procedures and Functions

  • BEFOREREPORT — Executes prior to report data retrieval. It initializes the concurrent request ID from FND_GLOBAL.CONC_REQUEST_ID and applies a fixed date format ('DD-MON-YY') to the cutoff date parameter, converting P_CUTOFF_DATE into the character variable CP_CUTOFF_DATE that the query expects. The historical FND SRWINIT user exit has been commented out as part of the XML Publisher conversion, with exception handling retained to preserve original error behavior.
  • AFTERREPORT — Executes after the report completes, historically to invoke the FND SRWEXIT user exit for cleanup. This call is now commented out, and the function simply returns TRUE.
  • C_CAT_SET_NAMEFORMULA — A format-trigger formula that resolves the category set name for display. It takes the category set ID parameter, queries MTL_CATEGORY_SETS, and returns the corresponding CATEGORY_SET_NAME. It handles NO_DATA_FOUND and other exceptions by returning NULL, allowing the report to render without a category set name when none is selected.
  • C_SUBINV_WHEREFORMULA — The user's search term, this function generates the subinventory WHERE-clause fragment. It inspects the P_SUBINV_LO and P_SUBINV_HI parameters and, based on which are populated, returns a SQL predicate such as "and mmt.subinventory_code between '...' and '...'". This dynamic fragment is concatenated into the report's main query.
  • C_ITEM_PADFORMULA — Constructs the item pad (concatenated descriptive key) used for the item display column in the report output.
  • BEFOREPFORM — A before-report (or before-parameter/format) supporting routine in the package, listed among the documented functions.

Tables Accessed

The package explicitly reads MTL_CATEGORY_SETS (accessed through its APPS synonym) within C_CAT_SET_NAMEFORMULA to convert a category set ID into its category set name. Additional report data—such as inventory subinventory and on-hand material records—is retrieved by the report's own XML Publisher data template queries, which incorporate the WHERE fragments produced by C_SUBINV_WHEREFORMULA. The package itself does not perform DML; it is read-only.

Usage Notes

INV_INVIRSLO_XMLP_PKG is invoked automatically by the XML Publisher concurrent program infrastructure when the associated INVIRSLO report is submitted. The formula functions are called by the data template at query-execution time, while BEFOREREPORT and AFTERREPORT execute at the start and end of the report run. Because the package is a report-support artifact and not a published API, it should not be called directly from custom code; any custom extensions to subinventory filtering should instead be implemented through the report's parameters or, where permitted, via a copied definition. The package is referenced by zero other packages, confirming its self-contained, report-bound nature.