Search Results mtl_material_transactions_n1

Overview

CSTPLPOP is a Cost Management package body owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented API classification is OTHER, and its single public function, PO_PRICE, exists to derive a purchase-order-relevant unit cost for a given inventory item within a specified organization. The function returns the transaction cost recorded against the most recent qualifying receipt transaction for that item/organization combination.

The package is a point solution rather than a general costing engine. It answers a narrow question: at what cost was this item last received into this organization under a standard purchase order workflow? Costing, margin analysis, and transfer-price validation logic across purchasing and inventory modules frequently need this value, and CSTPLPOP centralizes the lookup so callers do not each reimplement the transaction search.

Key Procedures and Functions

• PO_PRICE — Returns the purchase price (transaction cost) for an item in an organization. The function accepts an organization identifier and an inventory item identifier and returns a NUMBER. Internally it performs a three-stage lookup. First, it determines the latest truncated transaction date for the item/organization pair among transactions that qualify as purchase order receipts — that is, rows with transaction source type 1, transaction action 27 (receipt into stores), and transaction type 18 (PO receipt). If no such transaction exists, the function returns zero. Second, within that most recent day it identifies the specific transaction with the greatest creation timestamp by concatenating creation date and transaction ID and extracting the highest value. Third, it reads the transaction cost column for that transaction ID and returns it.

Tables Accessed

• MTL_MATERIAL_TRANSACTIONS — The sole documented table, referenced through the APPS synonym. It is read exclusively; the package performs no inserts, updates, or deletes. Three separate queries touch this table: the first aggregates the maximum transaction date, the second resolves the winning transaction ID for that date, and the third retrieves the transaction cost. The queries are tuned with explicit hints referencing indexes MTL_MATERIAL_TRANSACTIONS_N1, MTL_MATERIAL_TRANSACTIONS_N15, and MTL_MATERIAL_TRANSACTIONS_U2.

Usage Notes

CSTPLPOP records no dependent packages in the ETRM metadata, indicating that it is a leaf-level utility invoked directly by forms, concurrent programs, or custom extensions rather than through a broader API chain. Standard use cases include purchase price defaulting on requisition or order entry screens, cost comparisons during receiving, and reconciliation reports that must reconstruct the cost of the last PO receipt.

The header identifies the source as CSTLPOPB.pls at version 120.2, last modified 2010/11/29, with performance fixes tracked under Bug 6819625 and Bug 10300541. The U2 index hint present in the EXISTS subquery reflects a deliberate optimization: MTL_MATERIAL_TRANSACTIONS_U2 is a unique composite index that the optimizer otherwise may not choose for the correlated existence check. Because results are date-sensitive and depend on transaction history, callers should invoke PO_PRICE at runtime rather than caching its output across periods. The function returns zero rather than null when no qualifying receipt exists, so calling code need not add its own null handling for that case, though the returned cost itself may still require validation against the caller's costing method.