Search Results poa_edw_rcv_txns_pkg

Overview

POA_EDW_RCV_TXNS_PKG is an Oracle E-Business Suite PL/SQL package body owned by APPS and classified as OTHER in the ETRM repository. Its purpose is to support the Oracle Procurement and Receiving data warehouse extraction layer by computing net received quantities for receipt transactions and their corrections. The package isolates the arithmetic needed to reconcile original receiving movements against subsequent correction transactions, thereby providing a stable quantity figure that downstream extraction programs can consume without re-implementing correction logic.

The package depends exclusively on the RCV_TRANSACTIONS table accessed through an APPS synonym. The header comment identifies the source file as poafrcvb.pls, version 120.1, last modified 2005/06/13 by sriswami. The body exposes four documented program units: QTY_NET_CHILD_TXNS, QTY_CORRECTED, QTY_NET_CHILD_TXNS_RECURSIVE, and DATE_LAST_DELIVERY. Two other packages reference this package, confirming that it operates as a shared utility rather than a form-level private library.

Key Procedures and Functions

• QTY_NET_CHILD_TXNS — Returns the total net source document quantity for all transactions of a given transaction type that originate from a specific receipt shipment line. The implementation sums source_doc_quantity for the matching transaction type, then adds the source_doc_quantity of any CORRECT transactions whose parent_transaction_id belongs to the matching set. The function is the core reconciliation primitive and returns zero on null inputs or exceptions.
• QTY_CORRECTED — Returns the total corrections applied against a single parent transaction. It aggregates source_doc_quantity from RCV_TRANSACTIONS rows whose parent_transaction_id equals the supplied transaction. This provides the offsetting quantity that must be netted against an original receiving movement.
• QTY_NET_CHILD_TXNS_RECURSIVE — Extends the net-child quantity concept through recursive traversal, allowing correction chains nested more than one level deep to be fully accumulated. This is required because corrections may themselves be corrected in receiving workflows.
• DATE_LAST_DELIVERY — Returns the date of the last delivery associated with the relevant receipt transaction set, supplying the temporal dimension used by the extraction layer for period assignment and delta detection.

The variable l_qty, which the user searched for, is the local accumulator declared inside QTY_NET_CHILD_TXNS. It is initialized to zero and progressively accumulates the summed source_doc_quantity values before being returned.

Tables Accessed

• RCV_TRANSACTIONS — The sole documented table. It is read to obtain source_doc_quantity, transaction_type, shipment_line_id, parent_transaction_id, and transaction_id. All quantity aggregation, correction linkage, and delivery dating derive from this table. The package performs read-only access through the APPS synonym.

Usage Notes

This package is invoked from data warehouse extraction and reporting programs that populate procurement receiving fact tables. It is not exposed as a user-facing form function; instead, it is called by other PL/SQL packages and concurrent program logic. The two documented dependent packages call these functions to obtain corrected quantities and last-delivery dates before loading or refreshing warehouse aggregates. Because the functions return zero on null input or on any raised exception, callers should treat a zero result as potentially ambiguous and validate source data when exact reconciliation is required. Custom code should invoke the functions through the APPS schema and avoid duplicating the correction-netting logic, since the recursive variant exists precisely to handle multi-level correction chains consistently.