Search Results c_source_type3formula

Overview

INV_INVTRSTS_XMLP_PKG is the generated PL/SQL package that backs the Oracle Inventory transaction source usage report, a concurrent program published through the XML Publisher (BI Publisher) engine in Oracle E-Business Suite 12.1.1 and 12.2.2. The package belongs to the APPS schema and is compiled with AUTHID CURRENT_USER, meaning that all unqualified object references resolve against the privileges of the invoking user rather than the package owner. Its primary role is to accept the report's runtime parameters from the concurrent manager, construct the dynamic SQL fragments that drive the report's query, and supply the derived column and WHERE-clause expressions that the XML Publisher data template consumes when rendering the report output.

The report addresses the inventory planner's or cost accountant's need to analyze how material transactions are attributed to transaction source types across organizations, subinventories, categories, and date ranges. The package is tightly coupled to the Oracle Reports-to-XML Publisher migration pattern used throughout the Inventory product family, where the original Reports user exits and formula columns were re-implemented as PL/SQL functions in a package of this naming convention.

Key Procedures and Functions

The package exposes fourteen documented functions and one trigger-style procedure alongside numerous package-level variables. The functions divide into three functional groupings.

• Report lifecycle handlers: BEFOREREPORT, BEFOREPFORM, AFTERPFORM, and AFTERREPORT execute at defined points in the XML Publisher processing cycle. BEFOREREPORT performs initialization before the data model executes; BEFOREPFORM and AFTERPFORM operate around the parameter form; AFTERREPORT performs cleanup after output generation.
• Dynamic SQL and selection builders: C_FROM_CATFORMULA and C_WHERE_CATFORMULA build the FROM and WHERE fragments for the category flexfield join, using the P_CAT_FLEX and P_WHERE_CAT package variables. C_WHERE_SUBINVFORMULA and C_STATUS_WHEREFORMULA construct predicates for subinventory and item status filtering respectively.
• Source type and display formulas: C_SOURCE_TYPE1FORMULA, C_SOURCE_TYPE2FORMULA, and C_SOURCE_TYPE3FORMULA generate conditional expressions that map transaction source type codes into the report's source type columns. C_ITEM_TOTALFORMULA computes item-level totals, C_CAT_SET_NAMEFORMULA resolves the category set name, C_CURRENCY_CODEFORMULA determines the currency displayed, and C_CAT_PADFORMULA pads the category segment for alignment. P_VIEW_PUTVALIDTRIGGER validates the P_VIEW_PUT value.

The user's search term c_source_type3formula corresponds directly to the third source type expression function, which returns a VARCHAR2 fragment used to derive the third transaction source type column in the report layout.

Tables Accessed

Two tables are documented as referenced through APPS synonyms. MTL_CATEGORY_SETS is read to resolve category set identifiers, names, and structure information required by the category flexfield formulas (C_CAT_SET_NAMEFORMULA and the structural variables P_CAT_SET_ID and P_CAT_STRUCT_NUM). MTL_TXN_SOURCE_TYPES is read to resolve transaction source type codes and descriptions, supplying the lookup values that the C_SOURCE_TYPEnFORMULA expressions compare against. Additional inventory tables are referenced indirectly through the dynamic SQL assembled by the formula functions, but only these two are documented as direct dependencies.

Usage Notes

INV_INVTRSTS_XMLP_PKG is invoked exclusively by the XML Publisher concurrent program for the transaction source usage report. Parameters such as P_ORG_ID, P_DATE_LO, P_DATE_HI, P_CAT_LO, P_CAT_HI, P_ITEM_LO, P_ITEM_HI, P_SORT_ID, and the P_STYPEn values are populated by the concurrent manager from the report's parameter form before BEFOREREPORT fires. No other package in the ETRM inventory references this package, so direct invocation from custom code is not expected and should be avoided, since the package's contract is the XML Publisher data template rather than a public API. Customizations are more safely applied at the data template or report definition level. The header comment indicates the package was last revised in 2008 and is marked "noship," consistent with its role as a supporting artifact of a seeded report rather than a shippable standalone API.