Search Results c_category_whereformula

Overview

MRP_MRPRPLOR_XMLP_PKG is the packaged PL/SQL specification that backs the Oracle EBS concurrent program "MRPRPLOR" (the Oracle Report used within Oracle Master Scheduling/MRP and Supply Chain Planning). It is an Oracle Reports XML Publisher (XMLP) supporting package whose primary role is to expose report-level parameters, bind values, and dynamically generated SQL fragments as PL/SQL functions that the report layout and its query invoke at runtime. The designation "_XMLP_PKG" indicates the package was generated for an XML Publisher-enabled report, where the report logic resides partly in the report definition and partly in this server-side package.

Because the package is a specification-only declaration under the APPS schema, it defines the public interface used by the report's after-parameter and before-report formula triggers. It centralizes the report's input substitution values—such as organization, planner, buyer, item and category ranges, ABC classification, calendar code, and sort criteria—into strongly named variables and accessor functions.

Key Procedures and Functions

The package declares eighteen documented functions. They group logically as follows:

• Range-building functions — C_PLANNER_RANGEFORMULA, C_BUYER_RANGEFORMULA, C_ABC_RANGEFORMULA, and C_CATEGORY_FROMFORMULA construct the FROM/range clause fragments used to constrain the report query based on the planner, buyer, ABC assignment, and category inputs.
• Sort and order functions — C_SORT_COLUMNFORMULA, C_SORT_PFORMULA, and C_ORDER_BYFORMULA translate the user's sort selection into an ORDER BY clause and related column expressions. The parameter the user searched for, c_sort_columnformula, is the documented function that resolves the sort column used in that clause.
• ABC and category predicate functions — C_ABC_WHEREFORMULA, C_ABC_FROMFORMULA, C_ABC_ASSGN_PFORMULA, C_ABC_CLASS_PFORMULA, and C_CATEGORY_WHEREFORMULA form the WHERE and FROM predicates that restrict output to the requested ABC class, assignment group, and category set.
• Parameter helper functions — C_BUYER_PFORMULA, C_CAT_SET_PFORMULA, C_P_MULTI_ORGFORMULA, and C_P_ORG_TYPEFORMULA resolve buyer, category set, multi-org flag, and organization type values from the run-time environment.
• Report lifecycle functions — BEFOREREPORT and AFTERREPORT perform initialization and cleanup logic when the report executes.

Tables Accessed

The package's documented tables (through APPS synonyms) support the ABC and category filtering logic:

• MTL_ABC_ASSIGNMENT_GROUPS — supplies ABC assignment group definitions used to validate and build ABC-related predicates.
• MTL_ABC_CLASSES — provides ABC class codes (for example A, B, C) referenced by the ABC class and range formulas.
• MTL_CATEGORY_SETS — resolves category set structure and validates the category set input used by the category FROM and WHERE formulas.
• MTL_PARAMETERS — supplies organization-level parameters such as organization type and multi-organization context.

Usage Notes

This package is not called directly by application forms or by other PL/SQL packages; the metadata records zero referencing packages. It is invoked implicitly by the Oracle Reports/XML Publisher runtime whenever the MRP report program runs. The report's query and formula columns call the C_* functions to build dynamic SQL fragments, while BEFOREREPORT and AFTERREPORT run at report open and close. Customizations should be confined to the report definition or a cloned program rather than editing the seeded package, since the specification is generated and shipped by Oracle and would be overwritten by patching. The c_sort_columnformula function is the supported entry point for controlling which column the report sorts by.