Search Results date_last_delivery




Overview
Explore in AI Chat

Overview

POA_EDW_RCV_TXNS_PKG is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. Its classification in the E-Business Technical Reference Manual (ETRM) is "OTHER," indicating that it is not a public, supported API but rather an internal utility used by Oracle's Procurement and Receiving analytics, specifically the Enterprise Data Warehouse (EDW) extraction layer for purchasing and receiving subject areas. The package header carries a 2005 header signature (poafrcvs.pls 120.1) and is marked noship, confirming that it is delivered as part of the base product but is not intended for direct customer invocation.

The package encapsulates business logic for aggregating receipt transaction quantities and correction quantities at the shipment line and transaction level. Its central purpose is to provide net source document quantities — quantities adjusted for correction transactions — that downstream EDW fact tables and receiving analyses can consume consistently. This ensures that warehouse reporting reflects the true accepted, rejected, delivered, and corrected quantities associated with a receipt shipment line.

Key Procedures and Functions

Four documented functions comprise the package's public surface:

  • QTY_NET_CHILD_TXNS — Returns the total net source_doc_quantity for all transactions of a given transaction type that originate from a specified rcv_shipment_line. It aggregates the receipt-level quantities of accepted, rejected, delivered, and similar transaction types, inclusive of corrections, for the shipment line.
  • QTY_CORRECTED — Returns the total correction quantity applied against a specific transaction. This isolates the correcting entries so that original and adjusting quantities can be reconciled separately.
  • QTY_NET_CHILD_TXNS_RECURSIVE — Traverses the transaction hierarchy via parent_transaction_id and returns the net quantity of all child transactions, at any depth, that match a supplied transaction type. The net quantity is computed as the sum of quantity plus correct_quantity, always using source_doc_quantity as the unit of measure. This is the function most commonly sought by developers searching for this package.
  • DATE_LAST_DELIVERY — Returns the date of the most recent delivery recorded against a given rcv_shipment_line_id, supporting aging and delivery-timeliness metrics.

Each function is declared with PRAGMA RESTRICT_REFERENCES using WNDS, WNPS, and RNPS, guaranteeing that the functions do not write database state, package state, or read package state. This purity declaration allows the functions to be called safely from SQL statements and views.

Tables Accessed

The package reads from RCV_TRANSACTIONS, accessed through the APPS synonym layer. This is the base table for receiving transactions, holding receipt, delivery, correction, and return records, along with the parent_transaction_id self-referential key and the quantity, correct_quantity, and source_doc_quantity columns. All aggregation logic in the package is derived from this single table; no inserts, updates, or deletes are performed, consistent with the RESTRICT_REFERENCES purity constraints.

Usage Notes

Because the package is classified as OTHER and marked noship, Oracle does not publish it as a supported extension point. In practice it is invoked from EDW extraction programs, purchasing and receiving analytics, and occasionally from custom reporting views that require recursive net-quantity rollups across the receiving transaction hierarchy. The recursive function is particularly relevant to reporting on multi-level receiving activity, such as return-to-vendor or correction chains, where a top-level transaction may have several generations of dependent child transactions. Custom code that references these functions should account for the fact that source_doc_quantity is the consistent unit of measure, and that the RESTRICT_REFERENCES pragmas preclude any transactional side effects. The package is referenced by two other packages, indicating an internal dependency chain within the POA EDW layer. Developers should treat it as read-only, version-frozen utility code and avoid patching it directly; any required extension should be implemented in a separate custom package that calls these functions.