This chapter covers the following topics:
To optimize your database storage, you can delete Oracle Payables, Oracle Purchasing, and Oracle Supplier Scheduling records that you no longer need to access online. You can also purge invoices, purchase orders, suppliers, and related records such as invoice payments, supplier schedules, and purchase receipts.
Once a record is purged, you cannot query or view it in the system. However, the system maintains summary information of deleted records to prevent you from entering duplicate invoices or purchase orders.
Note: You should create a special responsibility for purging information from Oracle Payables and Oracle Purchasing and assign this responsibility only to the person responsible for purging information from your database.
Related Topics
In addition to meeting the Record Category and Last Activity Date criteria that you select, records must meet other system criteria before the Purge program will delete them. This ensures that you do not inadvertently purge active records or records you need to keep for other reasons.
Supplier Schedule Purge Criteria
If you are purging invoices, the Purge program purges related invoice batches and invoice distributions, and related invoice validations, invoice validation history records, accounting entries, and payments. You can purge invoices that meet the following criteria:
Last Update Dates of the invoice and its distributions, and the Invoice Date, are on or before the Last Activity Date
Invoice is fully paid or is a zero-amount invoice
Invoice is transferred to general ledger
Invoice is not a prepayment, and no prepayments have been applied to the invoice
All of the invoice's payments meet the Payment Purge Criteria
No open encumbrances are associated with the invoice
All purchase orders referencing the invoice meet the Purchase Order Purge Criteria
If Oracle Assets is installed, all of the invoice's distributions were tested by Mass Additions
When you are purging invoices, the Purge program deletes both related payments and the accounting entries associated with those payments. You can purge payments that meet the following criteria:
Payment is transferred to general ledger
All of the invoices paid by the payment meet the Invoice Purge Criteria
Payments that are not reconciled and referenced by Oracle Cash Management. If you do use Oracle Cash Management, then you have already purged related records in Cash Management.
If you are purging suppliers that have bank account assignments, only the link to the bank account is deleted. The bank account is not deleted. You can purge suppliers that meet the following criteria:
Supplier is inactive, and the values of Inactive On and Last Update Date are on or before the Last Activity Date you specify
Supplier is not a parent company or subsidiary of another supplier
Supplier is not referenced by records in any operating unit in Oracle Payables, Oracle Purchasing, or Oracle Assets
All invoices and payments for the supplier meet the Invoice and Payment Purge Criteria
If Oracle e-Commerce Gateway is installed, the Last Update Date of any e-Commerce Gateway control table row associated with the supplier must be on or before the Last Activity Date. Also, no EDI transactions can exist in any e-Commerce Gateway interface table for any of the supplier's sites.
Supplier is not present on any active or inactive sourcing rule. Before purging the supplier, you must first purge the sourcing rule that is associated with the supplier.
Supplier is not referenced on any planning or shipping schedule
The RCV Open Interface tables can contain no rows referencing the supplier (intransit shipments through ASNs or barcoded receipts awaiting processing)
You can purge requisitions that meet the following criteria:
Requisition is cancelled or finally closed
All purchase orders referencing the requisition meet the Purchase Order Purge Criteria
Requisition must be supplier (rather than internally) sourced. Payables will not purge invoices sourced by Oracle Inventory.
If Oracle Supplier Scheduling is installed, the requisition cannot be referenced on a supplier schedule, nor can the approved supplier list or planning sourcing rules be impacted
Requisition is not Oracle Projects related
If you are purging purchase orders, the Purge program deletes related purchase requisitions, and receipts.
Note that once you purge receiving transactions and the associated accounting information, that you can no longer recost those accounting entries.
You can purge purchase orders that meet the following criteria:
You have not updated the header, line, shipment, or distribution after the Last Activity Date. Note that Oracle Purchasing automatically updates some of your purchase order information even if you are not in a purchase order window. For example, when you receive items against a purchase order, Purchasing automatically updates your purchase order shipment to reflect the quantity received.
You have not updated any releases for a blanket agreement that meets the purge criteria after the Last Activity Date
Purchase order is cancelled or finally closed
Any contract referenced on a standard purchase order meets the purge criteria
All online requisitions and all receipts referencing the purchase order meet the purge criteria
All invoices referencing the purchase order meet the Invoice Purge Criteria
No invoices match the purchase order (this condition applies only when you choose Simple Purchase Orders for the purge category)
Purchase order is not referenced in Oracle Inventory or Oracle MRP
If Oracle Supplier Scheduling is installed, the blanket release is not referenced on a supplier schedule
Purchase order is not referenced on an ASL
Purchase Order is not Oracle Projects related
If Oracle Supplier Scheduling is installed, you can purge supplier schedules that meet the following criteria:
The schedule must be for the organization specified in the Submit Purge window. For multi-org schedules, we purge the schedule line that meets the purge criteria. Once all lines associated with a schedule are purged, the whole schedule is purged.
The schedule header Last Update Date must be on or before the Last Activity Date
All releases of blanket purchase orders referenced on the schedule must be either closed or eligible for purging
For organizations in which CUM Management is enabled, schedules must have a horizon start date before the current defined CUM Period
The e-Commerce Gateway interface table does not contain the schedule
If Oracle Supplier Scheduling is installed, you can purge CUM Periods that meet the following criteria:
CUM Management is enabled for the organization specified in the Submit Purge window. For multi-org schedules, we purge the schedule line that meets the purge criteria. Once all lines associated with a schedule are purged, the whole schedule is purged.
The Last Activity Date must be after the CUM Period end date for the purge organization. All previous CUM Periods are purged.
The system date cannot be within the CUM Period
When a CUM Period is purged, all CUM Period items, all data for supplier planning and shipping schedules, CUM Period high authorizations, and CUM Period adjustments are also purged
Use the Submit Purge window to delete records you no longer need to access online.
Suggestions for Purging Efficiently
To purge the most complete set of records, first choose the Invoices and POs category and then initiate a second purge for the Suppliers category.
Submit a purge when users are least likely to update records in the database.
The purge process can take several hours to complete, depending on the number of records in the purge and the resource capacity of your system. You can more efficiently purge a large number of records by submitting several smaller purges. On your first purge, set a Last Activity Date that is far in the past, and gradually increase the date with each purge.
Make sure that the database rollback segments you create are large enough so that you have sufficient memory to perform the purge. See your database administrator for assistance. For more information see your Oracle Server documentation.
Prerequisites
Back up and archive the database. See: Backing up the Database (Oracle Server Utilities Guide).
Confirm the integrity of the database backup.
Create rollback segments to prepare the database.
Log in as the Purge Administrator.
In the Submit Purge Window, enter your purge parameters:
Name. Unique name for the purge.
Category. The category of documents you want to delete.
Invoices and POs. Simple invoices, invoices matched to purchase orders, and purchase orders.
Schedules by CUM Period. CUM Periods and associated planning and shipping schedules.
Schedules by Organization. Planning and shipping schedules for the specified organization.
Simple Invoices. Invoices that are not matched to purchase orders.
Simple Purchase Orders. Purchase orders that are not matched to invoices.
Simple Requisitions. Cancelled purchase requisitions.
Suppliers. Suppliers that are inactive and have no active records associated with them.
Last Activity Date. The purge program will delete records that have not been updated since the Last Activity Date you enter.
Organization Code. For document categories Schedules by CUM Period and Schedules by Organization, you must select the organization.
Choose Initiate. The purge program identifies and lists all purge candidates. Purge candidates are all records that match your submission criteria of Category, Last Activity Date, and Organization Code. Note that each purge candidate will then need to meet the other system criteria for the category before the record is purged.
Review the Preliminary Purged Listings report and identify any purge candidates that you do not want to purge.
For each purge candidate you do not want to purge, slightly modify the record so it no longer meets the submission criteria. For example, if you add a period at the end of an Invoice Description in the Invoices window, then when you save the record you will update the record's Last Update Date to today's date, thereby disqualifying it from the purge candidate list.
Confirm the Purge by querying the purge in the Submit Purge window and choosing Confirm. The Purge program then reevaluates each record in the system to see if it meets the submission criteria (Category, Last Activity Date, and Organization Code). These purge candidates must then meet the submission criteria for the category. The Purge program then purges each purge candidate and its related records if it meets both the submission criteria and the purge criteria for the category.
The Purge program then automatically submits a concurrent request to print the following reports:
Final Purged Listings Report. Lists all purged records.
Rejected Purged Listings Report. Lists any records that were listed as purge candidates on the Preliminary Purge Report, but that were not purged. For example, records you updated.
Final Purge Statistics Report. Lists summary statistics on the number of records you purge from each table in Oracle Payables and Oracle Purchasing.
Give your database administrator the Purge Statistics Report, and ask your database administrator to export and import the database tables and indexes from which you purged data. See: Recreating Database Objects From Which You Purge.
Related Topics
Submitting Standard Reports, Programs, and Listings
The Purge program uses a set of purge concurrent requests that run in a predefined order to complete a purge.
Query the purge in the Submit Purge window by using the Purge Name or other identifying information. The Submit Purge window displays one of the following purge statuses:
| Variable | Description |
|---|---|
| Initiate | Purge process is about to begin |
| Initiating | Purge candidate selection is in process |
| Initiated | Purge candidate selection completed |
| Printed | Preliminary Purge Report printed |
| Revalidating | Revalidation of candidate purge eligibility |
| Confirmed | Revalidation complete |
| Printing | Running Final Purged Listings Report |
| Printed Confirmed | Final Purged Listings Report printed |
| Printing Rejected | Running Rejected Purged Listings Report |
| Printed Rejected | Rejected Purged Listings Report printed |
| Summarizing | Summarizing purge candidates |
| Summarized | Summarization process complete |
| Deleting | Deleting records |
| Completed-Purged | Purge successfully completed and confirmed |
| Aborting | User-initiated abort in progress |
| Completed-Aborted | Purge aborted |
You can abort a purge after you initiate the purge and before the Purge program begins deleting records. If the Purge has one of the following statuses, you cannot abort until the phase is completed: Initiating, Revalidating, Summarizing.
In the Submit Purge window, query the purge. Choose Abort.
If your computer system fails or the purge process ends in an error, you can restart the last active action of the purge process.
In the Submit Purge window, query the purge.
Choose Restart. The Purge program will restart the last action you chose: Initiate, Confirm, or Abort.
After you purge records from your database, ask your database administrator to export and import the tables and indexes from which you purged data by performing the following steps. By recreating these objects, you reduce the memory each object occupies in the tablespace thus increasing system performance.
Contact Oracle Worldwide Customer Support Services for further details.
Prevent users from accessing the database.
Analyze the size required for each table and index that should be recreated. Refer to the following:
Purge Statistics Report. Provides summary information on which tables you purged from and how many records you purged from each table
Oracle Payables Applications Technical Reference Manual. Provides information on which indexes Payables requires for each database table
Export the unpurged data from the tables you are going to drop and recreate.
Drop the indexes and tables from the database.
Recreate each table and index. Ideally, the initial extent of each object will provide enough space to store all data in that object, and the next extent should be somewhat smaller. Do not use a percentage increase for next extents. Depending on your database configuration, you may want to create certain objects on different tablespaces to maximize the input/output efficiency of your storage devices. For example, you may want to separate large, heavily-used tables and indexes into separate tablespaces.
Import the unpurged data back into the database and verify the results.
Bring users back online.
Related Topics
Reports (Oracle Payables Applications Technical Reference Manual)
When you purge records, Payables automatically prints the following reports. You cannot submit any purge report from the Submit Request window.
Use the Preliminary Purged Listings to review the items (invoices, payments, purchase orders, receipts, requisitions, and suppliers) that Payables selects as candidates to purge based on the criteria you specify when you submit the Payables Purge Program (Category, Last Activity Date, and Organization Code). Each selected item, or purge candidate, will then need to meet the system purge criteria listed in the previous section before it can be purged.
Payables divides the report into eight sections (one section for each type of item it purges):
Preliminary Invoice Listing
Preliminary Payment Listing
Preliminary Requisitions Listing
Preliminary Purchase Order Listing
Preliminary Receipts Listing
Preliminary Supplier Listing
Preliminary Supplier Schedules Listing
Preliminary CUM Periods Listing
If Payables does not select any items to purge for a certain type, Payables does not print the section.
Payables automatically prints this report when you initiate a purge. See: Submitting Purges.
The Preliminary, Final, and Rejected Purged Listings reports use the same format, column structure, and report headings.
Last Paid Date. Payables prints the last paid date of each invoice purge candidate. Payables does not purge any invoice unless you fully pay the invoice or update the amount remaining for payment to 0.
Void. If a payment is void, Payables prints Yes.
Requisition Number/Type. Number/type for each requisition purge candidate.
Preparer. Name of the person who prepared each requisition purge candidate.
Creation Date. Date a requisition was created.
Release. Release number.
Printed Date. Date the purchase order was printed.
Line. Line number of the purchase order for which the receipt was recorded.
Receipt Date/Receiver. Date a receipt was recorded for a purchase order and the name of the person who recorded the purchase order receipt.
Inactive Date. Payables prints the inactive date of a supplier. You cannot select a supplier to purge unless it is inactive.
Organization. The organization for which the schedule was issued.
Organization. The organization for which CUM Period information is being purged.
Use the Final Purged Listings to review the items (invoices, payments, requisitions, purchase orders, receipts, suppliers, supplier schedules, and CUM Periods) that Payables purges from your database. You can review the Final Purge Statistics report for summary information on the number of records Payables purges from each table in your database.
Payables divides the report into eight sections (one section for each type of item it purges):
Invoice
Payment
Requisitions
Purchase Orders
Receipts
Suppliers
CUM Periods
Supplier Schedules
If Payables does not purge any items for a certain type, Payables does not print the section.
Payables automatically prints this report when you confirm a purge. See: Submitting Purges.
The Final Purged Listings Report uses the same format, column structure, and headings as the Preliminary Purge Listings. See: Selected Report Headings for Purge Reports.
Use the Rejected Purge Listings to review the invoice and payment records which Payables originally selects to purge, but does not purge because you update the record between initiating and confirming the purge.
Payables divides the report into eight sections (one section for each type of item it rejects for purge): Invoice, Payment, Requisitions, Purchase Orders, Receipts, Suppliers, Supplier Schedules, and CUM Periods.
If Payables does not select reject any items to purge for a certain type, Payables does not print the section.
Payables automatically prints this report when you initiate a purge. See: Submitting Purges.
The Rejected Purged Listings uses the same format, column structure, and headings as the Preliminary Purge Listings. See: Selected Report Headings for Purge Reports.
Use the Final Purge Statistics Report to review the number of records Payables deletes during a purge from each table in your database.
Payables automatically prints this report when you confirm a purge. See: Submitting Purges.
Table Name. Payables prints the name of each table in your database from which it deletes records.
Rows Deleted. Payables prints the number of records that it deleted from each table in your database. Payables calculates this number by taking the difference in the number of records in each table before and after it deletes from the database. If you update the database during the purge process, these numbers can differ from the records Payables actually purges because of records you add or delete outside the purge process.
Oracle Payables and Oracle Purchasing let you purge documents and related records from your database. You can easily purge all purchase orders, requisitions, invoices, receipts, and suppliers that match your purge criteria, but only if you can purge all corresponding documents. When you purge information from the database, Oracle Payables or Oracle Purchasing deletes inactive documents, inactive suppliers, and all references in other tables to documents and suppliers that you purge. Oracle Payables and Oracle Purchasing store basic information about the suppliers and documents that you purge in separate tables.
Below is a list of the tables that contain the summary information of the suppliers and documents that you successfully purged. You can also review the tables that the Purge routines affect when you purge a supplier or a particular type of document. For complete details on all the tables that Oracle Payables or Oracle Purchasing uses for purging, please consult your technical reference manuals.
FINANCIALS_PURGES - Stores the criteria and status of a single purge process. The table also records the number of records you successfully purge from other tables
PO_HISTORY_VENDORS - Stores summary information of the suppliers your Oracle Purchasing application purges
PO_HISTORY_POS - Stores summary information of the purchase orders your Oracle Purchasing application purges
PO_HISTORY_RECEIPTS - Stores summary information of the receipts your Oracle Purchasing application purges
PO_HISTORY_REQUISITIONS - Stores summary information of the requisitions your Oracle Purchasing application purges
PO_PURGE_PO_LIST - Stores PO_HEADER_IDs temporarily during the purchase order purge process
PO_PURGE_REQ_LIST - Stores REQUISITION_HEADER_IDs temporarily during the requisition purge process
PO_PURGE_VENDOR_LIST - Stores VENDOR_IDs temporarily during the supplier purge process
AP_PURGE_INVOICE_LIST - Stores INVOICE_IDs temporarily during the invoice purge process
AP_HISTORY_CHECKS - Stores summary information of the invoice payments that Payables purges
AP_HISTORY_INVOICES - Stores summary information of the invoices that Payables purges
AP_HISTORY_INVOICE_PAYMENTS - Stores the relationship between invoices and payments that Payables purges
CHV_PURGE_CUM_LIST - Stores CUM-IDs temporarily during the CUM purge process
CHV_PURGE_SCHEDULE_LIST - Stores SCHEDULE_IDs temporarily during the schedule purge process
Tables Affected By Purging a Purchase Order
PO_ACCEPTANCES
PO_ACTION_HISTORY
PO_DISTRIBUTIONS
PO_HEADERS
PO_HEADERS_ARCHIVE
PO_LINE_LOCATIONS
PO_LINE_LOCATIONS_ARCHIVE
PO_LINES
PO_LINES_ARCHIVE
PO_NOTE_REFERENCES
PO_NOTES
PO_RELEASES
Tables Affected By Purging a Requisition
PO_ACTION_HISTORY
PO_NOTE_REFERENCES
PO_NOTES
PO_REQ_DISTRIBUTIONS
PO_REQUISITION_HEADERS
PO_REQUISITION_LINES
Tables Affected By Purging an Invoice
AP_ACCOUNTING_EVENTS
AP_AE_HEADERS
AP_AE_LINES
AP_BATCHES
AP_CHRG_ALLOCATIONS
AP_CHECKS
AP_DOC_SEQUENCE_AUDIT
AP_ENCUMBRANCE_LINES
AP_HOLDS
AP_INVOICE_APPROVAL_HISTORY_ALL
AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
AP_INVOICE_PAYMENTS
AP_INVOICE_SELECTION_CRITERIA
AP_PAYMENT_HISTORY
AP_PAYMENT_SCHEDULES
RCV_RECEIVING_SUB_LEDGER
RCV_SUBLEDGER_DETAILS_NUMBER
Tables Affected By Purging a Supplier
PO_VENDORS
PO_VENDOR_CONTACTS
PO_VENDOR_SITES_ALL
PO_VENDOR_LIST_ENTRIES
PO_VENDOR_LIST_HEADERS
PO_ASL_DOCUMENTS
PO_ASL_ATTRIBUTES
PO_APPROVED_SUPPLIER_LIST
Tables Affected By Purging a Supplier Schedule
CHV_SCHEDULE_HEADERS
CHV_SCHEDULE_ITEMS
CHV_ITEM_ORDERS
CHV_HORIZONTAL_SCHEDULES
CHV_AUTHORIZATIONS
Tables Affected By Purging a CUM period
CHV_SCHEDULE_HEADERS
CHV_SCHEDULE_ITEMS
CHV_ITEM_ORDERS
CHV_HORIZONTAL_SCHEDULES
CHV_AUTHORIZATIONS
CHV_CUM_PERIODS
CHV_CUM_ADJUSTMENTS
PO_MC_DISTRIBUTIONS
PO_MC_HEADERS
AP_MC_CHECKS
AP_MC_INVOICE_DISTS
AP_MC_INVOICE_PAYMENTS
AP_MC_INVOICES
Related Topics
Table Definitions (Oracle Purchasing Applications Technical Reference Manual)
Table Definitions (Oracle Payables Applications Technical Reference Manual)