Search Results p_item_order_by

Overview

APPS.MRP_MRPRPPLO_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 known as the Planner's Purchase Order / Planned Order report (report short name MRPRPPLO). The package follows the standard Oracle Reports-to-XML Publisher migration pattern introduced during the 11i-to-R12 transition, in which the original Oracle Reports 6i user exits and SRW built-ins are emulated through PL/SQL functions embedded in an XMLP package body.

The business function of MRPRPPLO is to present planning data — planned orders, requisitions, and purchasing information — filtered and sorted by planner, buyer, category, ABC assignment class, and cutoff date. The package is responsible for resolving runtime parameter values, building dynamic WHERE and ORDER BY fragments used by the underlying report query, and translating user-entered concurrent program parameters into the column and range predicates required by the report data model.

Key Procedures and Functions

The package exposes twenty-two documented procedures and functions. The principal entry points are:

• BEFOREREPORT — Executes once at report start. It captures the concurrent request ID via FND_GLOBAL.CONC_REQUEST_ID, resolves the quantity precision using mrp_common_xmlp_pkg.get_precision, optionally enables SQL trace when the debug parameter is set, stores the category structure number, and derives the currency description and precision from FND_CURRENCIES_VL. It also formats the cutoff date into the DD-MON-YYYY pattern.
• AFTERREPORT — Terminates report processing cleanly; the traditional SRW SRWEXIT call has been commented out and replaced with a NULL placeholder.
• C_PLANNER_RANGEFORMULA — Returns a SQL predicate fragment restricting results to the selected planner code when P_PLANNER is populated.
• C_BUYER_RANGEFORMULA — Returns a predicate fragment for the selected buyer ID when P_BUYER is populated.
• C_CATEGORY_FROMFORMULA, C_CATEGORY_WHEREFORMULA — Build the FROM and WHERE fragments that join and filter against category structures.
• C_ABC_FROMFORMULA, C_ABC_WHEREFORMULA, C_ABC_RANGEFORMULA — Construct the join and filtering logic against ABC assignment groups and ABC class ranges.
• C_ORDER_BYFORMULA, C_SORT_COLUMNFORMULA, C_CUTOFF_COLUMNFORMULA — Resolve the ORDER BY and display column expressions based on the sort and cutoff parameters.
• C_P_ITEM_SELECTFORMULA and the C_P_* family (C_P_SORTFORMULA, C_P_BUYERFORMULA, C_P_CAT_SETFORMULA, C_P_ABC_ASSGNFORMULA, C_P_CUTOFF_TYPEFORMULA, C_P_INCLUDE_COSTFORMULA)

The C_P_* functions primarily echo and normalize concurrent program parameter values for the XML template. Note that several sort branches in BEFOREREPORT are stubbed with NULL, reflecting the profile-option-driven migration state noted in the header.

Tables Accessed

The package reads two documented EBS tables through APPS synonyms:

• MTL_ABC_ASSIGNMENT_GROUPS — Used to resolve ABC assignment group classifications that drive ABC range filtering in the report.
• MTL_CATEGORY_SETS — Used to validate and resolve category set information applied when filtering by category ranges.

Additionally, FND_CURRENCIES_VL is queried in BEFOREREPORT to obtain the currency name and precision, and FND_GLOBAL is called for the concurrent request identifier. These are referenced implicitly rather than listed among the primary data tables.

Usage Notes

The package is invoked exclusively by the XML Publisher concurrent program definition MRPRPPLO. It is not called by Oracle Forms or by other PL/SQL packages; the metadata records zero referencing packages. The routine is executed within the concurrent manager's PL/SQL engine during report submission, after the user selects concurrent program parameters such as planner, buyer, category structure, ABC class, sort option, and cutoff date.

Customizations should avoid modifying this generated package body directly, since Oracle may regenerate it during patch application. The user search term "p_item_order_by" aligns with the C_ORDER_BYFORMULA and C_P_SORTFORMULA functions, which are the documented hooks governing ORDER BY behavior — a common customization point for tailoring report sort output.