Search Results get_ofi_line_count




Overview
Explore in AI Chat

Overview

The APPS.GML_AUDIT_REPORTS package is a PL/SQL utility package within the Oracle E-Business Suite Process Manufacturing (OPM/GML) application, delivered under the APPS schema with AUTHID CURRENT_USER. Its header comment dates to a 1999 porting shipment, and it continues to be catalogued in ETRM for Release 12.1.1 and 12.2.2 as an OTHER-classified API. The package supports the reconciliation and audit of process manufacturing documents against their Oracle Purchasing counterparts. In OPM terminology, an OFI (Order Fulfillment/Order Interface) document represents a process manufacturing order or receipt, while GEMMS entries represent the Oracle Purchasing side of the same transaction. The package exists to answer a narrow but essential set of audit questions: how many lines and shipment lines exist on each side of the comparison, what each side costs in total, whether a given purchasing line has been matched correctly, and how to resolve purchase order and release numbers into their internal identifiers. It is a read-oriented reporting aid rather than a transactional API; no inserts, updates, or deletes are documented.

Key Procedures and Functions

The package exposes thirteen documented callables, all functions, several of which operate in mirrored pairs — one for the OFI side and one for the GEMMS side.

  • GET_OFI_LINE_COUNT — Returns the count of OFI document lines for a supplied document identifier.
  • GET_OFI_BLINE_COUNT — Returns the count of OFI shipment (below-line) entries for the same identifier; this is the function the user searched for.
  • GET_OFI_TOTAL_COST — Returns the aggregate cost of the OFI document.
  • GET_OFI_BTOTAL_COST — Returns the aggregate cost of OFI shipment lines.
  • GET_GEMMS_LINE_COUNT — Counts GEMMS (Oracle Purchasing) lines associated with the given identifier.
  • GET_GEMMS_BLINE_COUNT — Counts GEMMS shipment lines.
  • GET_GEMMS_TOTAL_COST — Returns the GEMMS aggregate document cost.
  • GET_GEMMS_BTOTAL_COST — Returns the GEMMS shipment-line aggregate cost.
  • MATCH_OFI_GMS_PO_LINE — Validates whether an OFI order header, line, shipment line, and receiving record correspond to a given purchasing line; returns a VARCHAR2 status.
  • CHK_OFI_GMS_POLN_ITM — Checks item-level correspondence between an OFI/purchasing line and the receiving line.
  • CHK_OFI_GMS_POLN_STS — Checks status-level correspondence for the purchasing shipment line.
  • GET_PO_NUM — Resolves a purchasing document number reference to its canonical value.
  • GET_REL_NUM — Derives the release number associated with a purchase order number.

Every function carries PRAGMA RESTRICT_REFERENCES(..., WNDS, WNPS), confirming that the package neither writes database state nor packages state — a deliberate design choice permitting safe use inside SQL statements and views.

Tables Accessed

The package reads process manufacturing and purchasing data through APPS synonyms. CPG_ORAGEMS_MAPPING supplies the bridge identifying which GEMMS records correspond to which OFI documents — the foundation of all matching logic. PO_ORDR_DTL holds OPM order detail rows used for line and cost counts. PO_LINES_ALL and PO_LINE_LOCATIONS_ALL provide purchasing line and shipment-line details for the GEMMS-side counts and for the PO line matching functions. IC_ITEM_MST and MTL_SYSTEM_ITEMS supply item master information used by the item-level check function, bridging OPM and Oracle Inventory item definitions. DUAL is used only for computational scaffolding.

Usage Notes

The package is referenced by four other packages, indicating it is consumed as a shared service rather than invoked directly by end users. Its typical consumers are OPM audit and reconciliation reports, concurrent programs that compare purchasing receipts against process order expectations, and custom validation code embedded in forms or workflows. Because the functions are RESTRICT_REFERENCES-compliant and read-only, they may be called safely from SQL queries, report data models, and views. Developers debugging order-to-purchase discrepancies most commonly begin with GET_OFI_BLINE_COUNT and GET_GEMMS_BLINE_COUNT to confirm shipment-line populations before investigating cost variances with the corresponding total-cost functions. No direct form invocations are documented.