Search Results p_abc_assgn

Overview

MRP_MRPRPLOR_XMLP_PKG is the PL/SQL package body that backs the Oracle E-Business Suite concurrent program "MRPRPLOR" — the Planned Order Report / Planner Report XML Publisher (BI Publisher) report used within Oracle Materials Planning (MRP). The package encapsulates all of the dynamic SQL fragment generation logic required by the report's data model, allowing a single XML Publisher definition to produce a flexible, user-driven output depending on the runtime parameter values supplied at submission. Rather than hard-coding every possible filter combination, the package exposes a family of small formula functions that return SQL WHERE clauses, FROM-clause fragments, SELECT-list columns, and ORDER BY expressions as VARCHAR2 strings. The XML Publisher data template invokes these functions to assemble the final query against planning and purchased order data. The API is classified as OTHER, indicating it is an internal, report-support package rather than a public integration API.

Key Procedures and Functions

The documented interface contains eighteen procedures and functions, all focused on parameter-driven SQL construction and report lifecycle handling.

• C_PLANNER_RANGEFORMULA — returns a WHERE predicate restricting results by planner code when the planner parameter is populated.
• C_BUYER_RANGEFORMULA — returns a WHERE predicate restricting results by buyer.
• C_CATEGORY_FROMFORMULA and C_CATEGORY_WHEREFORMULA — supply the FROM fragment and WHERE predicate that join the category tables when low/high category parameters or category-based sorting are used.
• C_ABC_FROMFORMULA, C_ABC_WHEREFORMULA, and C_ABC_RANGEFORMULA — build the join, filter, and range fragments for ABC analysis, using the assignment group and class parameters.
• C_SORT_COLUMNFORMULA, C_ORDER_BYFORMULA, and C_SORT_PFORMULA — construct the SELECT-list additions and ORDER BY expressions required by the selected sort option (planner, buyer, ABC class, organization, and other modes).
• C_BUYER_PFORMULA, C_CAT_SET_PFORMULA, C_ABC_ASSGN_PFORMULA, and C_ABC_CLASS_PFORMULA — return display/derived values for buyer, category set, ABC assignment group, and ABC class respectively; these are directly associated with the p_abc_assgn parameter the user searched for.
• C_P_MULTI_ORGFORMULA and C_P_ORG_TYPEFORMULA — resolve organization and organization-type filtering behaviour for multi-organization reporting.
• BEFOREREPORT and AFTERREPORT — standard XML Publisher report-level entry points executed before and after the data model runs.

Tables Accessed

The package references four application tables through APPS synonyms. MTL_ABC_ASSIGNMENT_GROUPS and MTL_ABC_CLASSES provide the ABC classification framework used when ABC-based filtering or sorting is selected — directly supporting the p_abc_assgn parameter and the C_ABC_* formula functions. MTL_CATEGORY_SETS supplies category-set information for the category-related formulas. MTL_PARAMETERS is the organization definition table, consulted to resolve organization codes and organization-type attributes for the multi-org formulas.

Usage Notes

MRP_MRPRPLOR_XMLP_PKG is invoked exclusively by the MRPRPLOR XML Publisher concurrent program. When a user submits the report, the chosen parameters — planner, buyer, sort option, category range, ABC assignment group, and organization — are passed into the package, which translates them into the dynamic SQL fragments embedded in the report's data template. Because the package is referenced by no other packages, it should be treated as report-private implementation and not called directly from custom code. Customizations should be limited to registering the concurrent program, managing its parameter list, and where necessary extending the XML Publisher template. Any modification to the documented functions requires careful regression testing across every parameter and sort combination, since the fragments they generate are interdependent.