MEC — Oracle Custom Reports Development: PO Commitment, GL Trial Balance & AP Invoice Control

Executive Summary

MEC OptimizeIT — an Oracle EBS consulting and staffing firm — engaged William Delaney Consulting to develop a suite of custom Oracle Reports for deployment across multiple end-client environments. The engagement produced Oracle Reports Definition Files (.rdf) for PO Commitment analysis, GL Custom Trial Balance reporting, AP Vendor Payment History, and AP Invoice Control — reports addressing gaps in Oracle's standard report library that consistently appear across EBS client environments regardless of industry. Custom Oracle Reports development in this engagement followed a disciplined Original-Modified-Modified-N iteration structure, with each version representing tested refinements to report output, query performance, or layout.

The multi-client delivery model — with BHECS and NNG receiving their own named variants of the AP Invoice Control report (BHECS_AP_INVOICE_CONTROL.rdf, NNG_AP_INVOICE_CONTROL.rdf) — confirms that the reports were designed with client-specific parameter sets and data filters, not just restyled from a common template. Custom Oracle Reports built this way require both strong Oracle Reports Developer skills and deep knowledge of Oracle's AP, PO, and GL table structures.

Engagement Context

Oracle's standard report library has been a known gap for experienced Oracle EBS clients since the 11i era. The standard AP, PO, and GL reports cover basic operational needs but leave significant reporting gaps for finance teams that need commitment accounting views, detailed vendor payment analysis, or multi-entity GL trial balance presentation that differs from Oracle's standard formats. Custom Oracle Reports development — using Oracle Reports Developer (.rdf files) to build reports that run as Oracle concurrent programs — has been the traditional solution for these gaps in Oracle EBS environments through the R12 generation.

PO Commitment reporting is consistently among the most-requested custom reports across Oracle EBS client environments. Oracle's standard PO reports do not provide the combined view that budget managers need: open purchase order amounts that represent financial commitments against budget, broken down by cost center, category, and accounting period. The CUST_POCOMMIT_PIN series (four iteration versions) reflects the characteristic development cycle for this report type — balancing query performance against the comprehensive commitment view that stakeholders require.

The Oracle Reports platform (.rdf files compiled to .rep) was Oracle's primary report development tool through the EBS 12.2 era, running reports as Oracle concurrent programs against the Oracle database. It differs architecturally from Oracle BI Publisher (XML-based report templates) and Oracle Discoverer (ad hoc query and analysis). Custom .rdf reports provide the deepest integration with Oracle's concurrent processing engine and are the format required for reports that need to run on a scheduled basis or be submitted from Oracle's Standard Request Submission form.

Reports Development Scope

PO Commitment Reports (CUST_POCOMMIT Series)

The PO Commitment report suite (CUST_POCOMMIT_PIN and four MRE variations, CUST_POPDCMRA and variants, CUST_POCOMMIT_DETAIL_MGR) addresses Oracle's most common custom reporting gap: purchase order commitment balance reporting. These reports query Oracle's PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, and PO_DISTRIBUTIONS_ALL tables to compute open commitment amounts — the amount ordered but not yet invoiced — by cost center, GL account, project, and period. The POHP_PIN variant (PO Header/Price) provided summary-level procurement pricing analysis for procurement management review.

The four-iteration development cycle (MRE_CUST_POCOMMIT_PIN through MRE_CUST_POCOMMIT_PIN4) reflects a common pattern in PO commitment report development: early versions suffer from join performance issues on large PO tables, and subsequent iterations optimize the query structure — adding appropriate table indexes, restructuring subqueries, and narrowing the default parameter ranges to constrain the data volume returned.

GL Custom Trial Balance (MRE_CUST_GL_CUSTOM_TRLBAL)

The custom GL Trial Balance report addresses the presentation gaps in Oracle's standard Trial Balance — standard Oracle trial balance reports provide limited flexibility in column layout, subtotal structure, and comparative period display. The custom trial balance provided the client's finance team with the specific comparative-period, cost-center-subtotaled format used for monthly management reporting and board presentation, replacing the manual Excel reformatting step that had been applied to Oracle's standard output.

AP Invoice Control Reports (BHECS and NNG Variants)

AP Invoice Control reports (BHECS_AP_INVOICE_CONTROL.rdf, NNG_AP_INVOICE_CONTROL.rdf) provided each client with an AP operational control report covering invoice status, holds, aging, and payment readiness. Client-specific variants reflect differences in: the AP hold types relevant to each client's AP operations, the GL account segments used for departmental cost coding, and the reporting format preferences of each client's AP management team. A shared core query drives both variants, with client-specific parameter defaults and output formatting.

Custom PL/SQL Library (CAL_CUSTOM.pll)

The custom PL/SQL library (.pll file) provided shared formatting functions and utility routines used across multiple reports in the suite — avoiding redundant code in individual .rdf files and ensuring consistent behavior across the report library. Functions in a shared .pll typically include date formatting utilities, currency display functions, and label formatting routines referenced by multiple report data models.

Key Deliverables

Consultant Insights

Related Engagements