Search Results first_sort

Overview

APPS.GMP_PLNG_DTL_REPORT_PKG is an Oracle EBS Process Manufacturing (OPM) package body that generates the Planning Detail Report, a printable and XML-capable output of plan-level supply, demand, and exception data. The package is registered under the APPS schema with the 12.1.1/12.2.2 ETRM classification OTHER and is documented with fifteen procedures and functions. Its controlling entry point is CREATE_PDR, which orchestrates validation, dynamic SQL assembly, and XML generation for the report.

Digitally signed and compiled from GMPPLDRB.pls, the body maintains a large set of package-level global variables that bind runtime parameters and pre-computed SQL fragments. These include planner, buyer, item, category, and ABC class range variables; bucket cut-off dates for day, week, and period horizons; and four user-defined period set attributes (G_comb_pdr, G_horiz_pdr, G_vert_pdr, G_excep_pdr). The variables G_fsort, G_ssort, and G_tsort carry the first, secondary, and tertiary sort selections for the report output—G_fsort being the "first_sort" a user supplies at runtime to drive report ordering.

Key Procedures and Functions

CREATE_PDR is the public driver. It accepts the instance, organization, and plan identifiers plus errbuf and retcode, invokes validation, builds the SQL statements, and dispatches XML output. VALIDATE_PARAMETERS verifies user-supplied selections before processing. INSERT_ITEMS populates the temporary reporting structures used to stage item-level detail.

Four statement-builder routines assemble the report's data queries: HORIZ_PLAN_STMT, VERT_PLAN_STMT, ITEM_EXCEPTION_STMT, and ITEM_ACTION_STMT. Each generates SQL text stored in the corresponding global statement variables and driven by the period-set, bucket, and sort globals.

GENERATE_XML produces the report XML using DBMS_XMLGEN and DBMS_XMLQUERY, while XML_TRANSFER moves the generated XML document to its destination. PS_GENERATE_OUTPUT serves as the output-level orchestration routine.

Supporting lookup functions include PLAN_NAME, PLAN_ORG, CATEGORY_SET_NAME, and LOOKUP_MEANING, which resolve descriptive values for the report headers and columns. GMP_DEBUG_MESSAGE writes diagnostic messages for troubleshooting.

Tables Accessed

The package reads plan and organization metadata from MSC_PLANS and MSC_PLAN_ORGANIZATIONS to identify the plan being reported. MSC_CAL_WEEK_START_DATES and MSC_PERIOD_START_DATES supply calendar boundaries that drive week and period buckets. MSC_CATEGORY_SETS and MSC_TRADING_PARTNERS define category scope and buyer/planner relationships. FND_LOOKUP_VALUES resolves lookup meanings through LOOKUP_MEANING. XDO_TEMPLATES_B provides the XML Publisher template metadata used for formatted output.

Working data is staged in the global temporary tables GMP_HORIZONTAL_PDR_GTMP and GMP_PDR_XML_TEMP. The build emits standard Oracle supplied packages DBMS_LOB, DBMS_SQL, DBMS_XMLGEN, and DBMS_XMLQUERY, all referenced through APPS synonyms. The package is referenced by zero other packages, so its callers reside in forms or concurrent program definitions.

Usage Notes

GMP_PLNG_DTL_REPORT_PKG is invoked from the Process Manufacturing planning detail reporting flow, typically through a concurrent program or a planning workbench form that passes instance, organization, and plan identifiers along with the user's sort and filter selections. The "first_sort" parameter, represented by G_fsort, determines the primary ordering of the report detail lines; G_ssort and G_tsort provide secondary and tertiary ordering. Callers should expect the package to validate all parameters before any SQL executes, to raise the invalid_parameter exception on failure, and to emit diagnostics through GMP_DEBUG_MESSAGE when debugging is enabled.