Search Results so_exceptions_s

Overview

APPS.OEXPURGE is the order purge engine for the Oracle Order Management (OM) module in Oracle E-Business Suite. Its business purpose is to permanently remove obsolete or completed sales order records from the transactional schema once those orders no longer carry open demand, open invoicing, open holds, or open returns. Order Management accumulates a large volume of header, line, detail, approval, freight, and reference data over time; the OEXPURGE package provides the controlled deletion mechanism that frees this space while preserving referential integrity across the tightly coupled OM and Receivables tables.

The package body is classified as OTHER in the ETRM repository and owns 31 documented procedures. It is a standalone utility rather than a callable business API: the metadata records zero packages referencing it, indicating that OEXPURGE functions as a terminal, self-contained purge routine invoked by the order purge concurrent program and, in some flows, from OM purge forms.

Key Procedures and Functions

The package is organized around a validation layer that decides whether an order is eligible for purge and a deletion layer that removes it table by table.

• SELECT_PURGE_ORDERS — the driver procedure. It accepts numeric and date range filters, order category, order type, and customer, then constructs a candidate list of closed sales orders. A local cursor selects header_id, order_number, and order type name from SO_HEADERS joined to SO_ORDER_TYPES, restricted to orders with open_flag IS NULL. Before returning each candidate, it assigns a local return_status and calls guard procedures. The date filters compare TRUNC(creation_date) and TRUNC(date_ordered) so the time component is ignored, a behavior explicitly carried forward from Release 11 (Bug 916858, Bug 914321). The procedure first deletes stale rows from the temporary SO_PURGE_ORDERS table where request_id IS NULL.
• SO_CHECK_OPEN_INVOICED_ORDERS, SO_CHECK_OPEN_DEMAND_ORDERS, SO_CHECK_OPEN_ORDERS, SO_CHECK_OPEN_RETURNS — verification functions. Each returns a status indicating whether blocking activity exists; a non-zero status prevents the candidate order from being purged, and the caller records an error message such as "Open demand exists for order number:".
• SO_ORDER_PURGE — the master purge routine that coordinates the individual delete steps for a single order.
• Table-specific purge procedures — SO_PURGE_HEADERS, SO_PURGE_LINES, SO_PURGE_LINE_DETAILS, SO_PURGE_LINE_SERVICE_DETAILS, SO_PURGE_LINE_APPROVALS, SO_PURGE_ORDER_APPROVALS, SO_PURGE_FREIGHT_CHARGES, SO_PURGE_HOLD_RELEASES, SO_PURGE_HOLD_SOURCES, SO_PURGE_NOTE_REFERENCES, SO_PURGE_EXCEPTIONS family procedures, SO_PURGE_ORDER_CANCEL_LINES, SO_PURGE_ORDER_CANCELLATIONS, SO_PURGE_MTL_SO_RMA_INTERFACE, and SO_PURGE_MTL_SO_RMA_RECEIPTS. Each targets one child or dependent entity so deletions can be ordered correctly.

Tables Accessed

The package reads and writes the core Order Management tables and cross-references supporting applications. SO_HEADERS, SO_LINE_DETAILS, SO_LINE_SERVICE_DETAILS, SO_LINE_APPROVALS, SO_ORDER_APPROVALS, SO_HOLD_RELEASES, SO_FREIGHT_CHARGES, and SO_EXCEPTIONS/SO_EXCEPTIONS_S hold the order data being purged. MTL_DEMAND and MTL_SALES_ORDERS are queried to detect outstanding supply or demand before deletion, and MTL_SO_RMA_INTERFACE and MTL_SO_RMA_RECEIPTS are checked and cleaned for return material authorizations. RA_CUSTOMER_TRX and RA_CUSTOMER_TRX_LINES supply the open-invoice check. FND_APPLICATION and FND_CONCURRENT_PROGRAMS support the concurrent program context, and the staging table SO_PURGE_ORDERS holds the candidate list keyed by request_id.

Usage Notes

OEXPURGE is invoked through the Order Purge concurrent program, which passes the low/high order number, creation date, and ordered date ranges together with order category, order type, and customer parameters into SELECT_PURGE_ORDERS. Because deletions are permanent and cascading, standard practice is to run the program in a validation or report-only mode first, or to execute it during a maintenance window with an export of the affected orders in place. Custom code should not call the SO_PURGE_* procedures directly; relying on the driver ensures the open demand, invoice, hold, and return guards execute in the correct sequence. After a purge run, the rows staged in SO_PURGE_ORDERS provide the audit trail of what was removed.