Search Results get_last_trx_date

Overview

POA_SUPPERF_API_PKG is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its name, "Supplier Performance," reflects its role as the calculation engine behind supplier performance analysis and supplier scorecard reporting in Oracle Purchasing and Oracle Procurement. The package exposes a set of scalar helper functions that derive purchasing metrics — receipt dates, average prices, ordered and received quantities, late and early delivery quantities, and transaction dates — from the transactional core of the purchasing and receiving modules. These metrics feed supplier performance reports and supplier scorecards without requiring the calling program to re-implement the underlying aggregation logic.

The package is declared with AUTHID CURRENT_USER, meaning its SQL executes under the privileges of the invoking user rather than the package owner. It is classified under the ETRM API classification "OTHER," indicating it is primarily an internal computational utility rather than a public business-event API, and it is referenced by one other package in the ETRM inventory.

Key Procedures and Functions

The package contains 14 documented functions, all of which return scalar values to the caller:

• GET_RECEIPT_DATE — returns the receipt date associated with a line location.
• GET_AVG_PRICE — returns the average price for a line location, allowing a price override.
• GET_PRIMARY_AVG_PRICE — returns the average price converted to the item's primary unit of measure.
• GET_NUM_RECEIPTS — returns the count of receipts recorded against a line location.
• GET_PRIMARY_UOM — returns the primary unit of measure for an item in a given organization.
• GET_PRIMARY_QUANTITY — converts a supplied quantity into the item's primary unit of measure for the specified item and organization. This is the function most directly relevant to the "get_primary_quantity" search term and is the standard mechanism for presenting purchasing quantities on a consistent, UOM-normalized basis.
• GET_QUANTITY_PURCHASED — derives the net purchased quantity from ordered, billed, cancelled, and received quantities combined with cancellation and closed-code indicators.
• GET_TOTAL_AMOUNT — computes the total amount for a line location from price and status flags.
• GET_QUANTITY_LATE and GET_QUANTITY_EARLY — return quantities received outside the allowed late and early tolerances relative to an expected date.
• GET_QUANTITY_PAST_DUE — returns the quantity still outstanding beyond the allowed number of late days.
• GET_SUPPLIERS — returns a delimited list of suppliers matching ordering, item, and date-range criteria, limited to a requested count.
• GET_LAST_TRX_DATE — returns the date of the last receiving transaction for a line location; this function carries a RESTRICT_REFERENCES pragma declaring WNDS, WNPS, and RNPS purity.
• GET_RCV_TXN_QTY — returns the quantity for a given receiving transaction type against a line location.

Tables Accessed

The package reads across five documented tables accessed through APPS synonyms:

• PO_LINE_LOCATIONS_ALL — the primary source for shipment and receipt scheduling data.
• PO_DISTRIBUTIONS_ALL — provides accounting distribution detail used in amount and quantity derivations.
• RCV_TRANSACTIONS — source of receiving activity used by the receipt count, last transaction date, and receiving quantity functions.
• MTL_SYSTEM_ITEMS — supplies item master attributes, including the primary unit of measure referenced by GET_PRIMARY_UOM and GET_PRIMARY_QUANTITY.
• FINANCIALS_SYSTEM_PARAMS_ALL — provides operating unit and financials system parameter context for currency and precision-sensitive calculations.

Usage Notes

POA_SUPPERF_API_PKG is invoked as a server-side PL/SQL library, typically from supplier performance and procurement reporting components rather than from end-user forms directly. Its functions are well suited for embedding in custom reports, concurrent program logic, and supplier scorecard queries where UOM-normalized quantities and delivery timeliness metrics are required. Because the package is declared AUTHID CURRENT_USER, callers must possess the necessary privileges on the underlying tables and synonyms. The presence of a RESTRICT_REFERENCES pragma only on GET_LAST_TRX_DATE indicates that the remaining functions were not certified for use from SQL statements in older releases; custom code should therefore invoke them from PL/SQL blocks rather than direct SELECT statements to avoid purity violations. The package should be treated as a read-only calculation utility; it does not perform inserts, updates, or deletes against purchasing or receiving data.