Search Results c_p_abc_assgnformula

Overview

MRP_MRPRPPLO_XMLP_PKG is an Oracle EBS PL/SQL package owned by APPS that supports the concurrent program XML Publisher (BI Publisher) report MRPRPPLO. The report prints the planned order details produced by Oracle Master Scheduling/MRP and Advanced Supply Chain Planning. The package follows the standard Oracle Reports-to-XML-Publisher migration pattern in Oracle EBS 12.1.1 and 12.2.2: the report layout is defined in an XML template, while this package encapsulates the bind variables, lexical parameters, and SRW-style formula functions that supply dynamic SQL fragments, WHERE clauses, and ORDER BY strings to the report data model. Because it uses AUTHID CURRENT_USER, it executes with the privileges of the calling session, which in the EBS concurrent manager context is APPS.

The package exposes a set of public variables that represent the concurrent program parameters, including P_ORG_ID, P_PLAN_NAME, P_CUTOFF_DATE, P_ITEM_SELECT, P_CATEGORY_FROM, P_HIGH_CAT, P_ABC_ASSGN, P_ABC_CLASS, P_SORT, P_PLANNER, P_BUYER, P_ITEM_RANGE, P_CAT_RANGE, P_ORDER_BY, P_DEBUG, and P_PRECISION. These variables are populated by the Oracle Reports runtime or by the XML Publisher engine before the formula functions are evaluated.

Key Procedures and Functions

The package includes two entry-point functions, BEFOREREPORT and AFTERREPORT, which handle the initialization and cleanup phases of report execution. BEFOREREPORT typically retrieves the organization unit name, currency code and description, precision settings, and default sort values required by the layout. AFTERREPORT finalizes any remaining session state.

The remaining functions are report formula columns, invoked by name from the data model to build dynamic SQL. C_PLANNER_RANGEFORMULA and C_BUYER_RANGEFORMULA construct the planner and buyer range predicates. C_CATEGORY_FROMFORMULA, C_CATEGORY_WHEREFORMULA, C_SORT_COLUMNFORMULA, C_ABC_WHEREFORMULA, C_ABC_FROMFORMULA, and C_ABC_RANGEFORMULA build category and ABC classification clauses. C_CUTOFF_COLUMNFORMULA resolves the cutoff column for the selected plan. C_ORDER_BYFORMULA and CP_ORDER compose the ORDER BY and supporting sort text. C_P_ITEM_SELECTFORMULA, C_P_INCLUDE_COSTFORMULA, C_P_SORTFORMULA, C_P_BUYERFORMULA, C_P_CAT_SETFORMULA, C_P_ABC_ASSGNFORMULA, and C_P_CUTOFF_TYPEFORMULA resolve the corresponding report parameters into usable values at runtime. A lookup for c_p_abc_assgnformula maps to the function C_P_ABC_ASSGNFORMULA, which returns the ABC assignment group value selected on the concurrent program parameter form.

Tables Accessed

Two APPS synonyms are referenced. MTL_ABC_ASSIGNMENT_GROUPS is queried to validate or retrieve the ABC assignment group selected by the C_P_ABC_ASSGNFORMULA function, ensuring the report restricts planned orders to the correct ABC compilation. MTL_CATEGORY_SETS is accessed to resolve the category set identifier and associated structure used by the category range formulas. Additional planning and item master tables are referenced implicitly through the generated SQL rather than by direct package references.

Usage Notes

This package is invoked exclusively through the MRPRPPLO concurrent program submitted from the MRP or ASCP responsibility, or attached to a request set. It is not referenced by other PL/SQL packages, so no external code should depend on its public variables. Oracle Reports 10g (12.1.1) and the XML Publisher engine (12.2.2) both execute these formula functions during data-model execution. Customizations should avoid modifying the package directly; instead, extend the underlying XML template or use a custom report wrapper, because the package will be overwritten during patching.