Search Results txn_purge

Overview

APPS.INV_HV_TXN_PURGE is a PL/SQL purge utility within the Oracle E-Business Suite Inventory (INV) module. Its purpose is to remove obsolete transactional data from the high-volume inventory transaction tables that accumulate over time, thereby controlling database growth and preserving the performance of inventory transaction processing. The package is defined with AUTHID CURRENT_USER, meaning its SQL executes under the privileges of the calling session rather than the definer, and it is owned by the APPS schema. It is an archived Oracle object; the source header (INVHVPGS.pls 115.0, dated 2003/03/10) indicates it belongs to earlier INV generations, and ETRM classifies it under API classification OTHER. It is not a public integration API and exposes only a single documented procedure. All three standard concurrent program return codes are declared as package constants: RETCODE_SUCCESS ('0'), RETCODE_WARNING ('1'), and RETCODE_ERROR ('2'). The presence of RETCODE_WARNING is the reference most commonly searched, since it signals the partial-success outcome that a concurrent request based on this package may return.

Key Procedures and Functions

• TXN_PURGE — The sole documented procedure. It performs the physical deletion of inventory transaction records, scoped by organization and by a cutoff date. Consistent with concurrent-program conventions, it declares two OUT NOCOPY parameters: an error buffer and a numeric return code. It also accepts an optional organization identifier (defaulting to NULL, which implies all organizations are eligible) and a mandatory cutoff date supplied as a VARCHAR2. Any record whose transaction date precedes the cutoff becomes a purge candidate. Internally the procedure builds and executes dynamic SQL, which is why DBMS_SQL appears among the referenced objects.

No functions and no additional public procedures are documented for this package.

Tables Accessed

The package operates across the core high-volume inventory transaction tables and their dependent detail tables:

• MTL_MATERIAL_TRANSACTIONS — The primary transaction header table and the principal purge target.
• MTL_MATERIAL_TXN_ALLOCATIONS — Allocation detail lines tied to each material transaction.
• MTL_TRANSACTION_ACCOUNTS — Accounting distribution rows generated for inventory transactions.
• MTL_TRANSACTION_LOT_NUMBERS — Lot and serial associations recorded against transactions.
• MTL_UNIT_TRANSACTIONS — Move-order and unit-level transfer transactions.
• ORG_ACCT_PERIODS — Organization accounting periods, consulted to validate that candidate transactions fall in closed periods before removal.
• DBMS_SQL and PLITBLM — Oracle-supplied packages invoked at runtime for dynamic SQL execution and PL/SQL table handling.

Because dependent child rows must be removed in the correct sequence alongside the parent material transaction, the package maintains referential integrity through ordered deletion across these tables.

Usage Notes

INV_HV_TXN_PURGE is designed to be driven by a concurrent program, and its parameter signature — error buffer, return code, organization, and cutoff date — follows the standard Oracle concurrent manager contract. A request completes with status Warning when TXN_PURGE returns RETCODE_WARNING, typically indicating that the purge proceeded but with non-fatal conditions, such as records skipped because their accounting periods remained open or because selected rows could not be fully processed. RETCODE_ERROR denotes an outright failure, while RETCODE_SUCCESS indicates a clean completion.

Practically, the procedure should only be scheduled after the relevant accounting periods are closed and after a verified backup, since purged transactions cannot be recovered. Selection by organization allows a phased rollout, but leaving the organization parameter at its default NULL widens the scope to every organization, which raises the volume and runtime substantially. ETRM records no packages that reference INV_HV_TXN_PURGE, confirming it is a standalone maintenance utility rather than a dependency of other inventory logic. Custom code may call TXN_PURGE directly, but callers must handle the OUT parameters and honor the same period-close and backup prerequisites.