This topic explains how to use the Archive and Purge functions in OPM. Archive and Purge allows you to remove data which is no longer used on a day-to-day basis from the tables where current data exists. Archiving is the process of inserting data into archive tables after gathering it from live production tables based on the criteria fed to the process. Data can be removed from the live tables or left to remain there. Live tables are those that are used to store and retrieve information that is current and is required in the operation of day-to-day business transactions. Database performance is improved by moving historical data from live tables into archive tables. Archive tables are those that are used to store historical information. Purging is the deletion of the archive tables from the database. Data is not recoverable after purging except from backups of the database files.
This chapter covers the following topics:
Archive and purge process consists of the following three steps:
Defining the Purge: Use the Archive and Purge Setup window to set up archive and purge types.
See: Setting Up Archive and Purge Types
Entering the purge criteria and executing the purge: First, you test the process and the data is copied into a set of archive tables. You can then purge this test data. Once your test process is correct, you can then archive the data into the archive tables. Then, you can purge the archive data to remove it from the system completely. Use the Archive and Purge window to perform the archive and purge processes. For example, the test process copies data from op_ordr_hdr and associated tables into the test archive table, based on the archive type OPSO. Once you have your archive criteria set correctly, you run the archive process for OPSO. This moves the data from op_ordr_hdr and associated tables into the archive tables. When you want to remove this data from the system completely, you then run the purge on the archive for OPSO. This removes the data by dropping the archive tables.
See: Running an Archive and Purge
Querying the details of the archive and purge process: You can check the status of the process using the Archive and Purge Inquiry window.
See: Running an Archive and Purge Inquiry
Use the Archive and Purge Setup window to define custom purges or to view the Oracle Process Manufacturing (OPM) standard predefined purges. To run standard predefined purges, use the Archive and Purge window.
Navigate to the Archive and Purge Setup window.
Enter a Type up to four characters that specifies the category for this purge. For example, view PROD. PROD is a predefined purge. You can use PROD as an example to view its setup. PROD is display only.
View the information about the predefined purges:
Description is a brief description of the purge.
Criteria is a tag that identifies the data to be entered in this field before runtime. For example, Organization.
The database Mask for the data type contained within this criteria. The three standard masks supplied with the product are C for character, D for date, and N for numeric. Entering any of these characters in the field displays the actual to_char or to_data maskto_char maskto_data mask used in the purge. Any mask necessary can be entered here and used with custom purges.
Description is a brief description of this criteria. This displays on the Archive and Purge window when running this purge.
LOV Column are the columns to display in the list for the criteria field, when submitting the concurrent program from the Archive and Purge window.
LOV Table is the sql statement with the table and the where clause. This sql is used to build the list for the criteria fields on the Archive and Purge window.
Navigate to the Archive and Purge Tables window from the Actions menu on the Setup window.
View the information about the purge tables:
Type displays a code up to four characters that specifies the category for this purge. Example: PROD.
Description displays a description of the purge.
Name displays the name of the table.
Action displays the action to perform. An action can either be to Keep the data in the table or to Delete the data in the table.
Target Tablespace is the tablespace name for storing the archive tables. Default tablespace is used if this field is left blank.
Use the Archive and Purge window to enter criteria for and complete a given purge. The purge can be one of the predefined OPM purges, or a custom purge defined by your System Administrator.
Note: Only sales orders, batches, inventory journal records, and purchase orders that are posted to the General Ledger can be purged.
For the base OPM product, archive and purge functionality exists only for a predefined set of tables and transaction data. They are:
DCST
Cost component details and associated detail transaction records in Burden Details, Actual Costing Transactions, and Standard Cost Ledgers.
DSLA
Subledger update data.
DSLT
Test subledger update data.
Production batch records and the associated detail transaction and text records.
Sales order and shipment records and the associated detail transaction and text records. This does not purge data related to Order Management or Common Purchasing.
PO, receipt, and return records and the associated detail transaction and text records. This does not purge data related to Order Management or Common Purchasing.
Inventory journal records and the associated detail transaction and text records.
The criteria values can be selected from the List of Values for each field. The following are custom criteria:
Sales Order Status (OPSO). Valid values are -1 (voided) and 25 (completed)
Production Batch Status (PROD). Valid values are -1 (canceled), -3 (convert FPO to batch), 4 (closed)
Cost calendar, cost period, and cost method (DCST). This purge has Last Modification Date and Earliest Modification Date criteria
Company code, fiscal year, fiscal period, and source (DSLA and DSLT). Valid values for source are CM for Costing, OP for OPM Order Processing, IC for Inventory Control, PM for Production Management, PO for OPM Purchasing, PUR for Oracle Purchasing, OM for Order Management. These purges use Last Modification Date and Earliest Modification Date criteria.
Note: All transactions must be received and posted to the General Ledger in order to be archived or purged. Batches must be closed and posted to the General Ledger.
Each criteria field is required. Following are examples of proper data formatting:
Date criteria must follow this format: dd-mon-yyyy hh24:mi:ss. If you do not enter the time, it defaults to 00:00:00, which is the beginning of the day.
Document range must fill in the Pad characters (e.g. 00010 rather than 10).
Ranges of organizations must contain real organization codes. For example, it is valid to enter FBP1 to FBP9 (where FBP1 and FBP9 are valid organization codes).
After the criteria is specified on the Archive and Purge window and saved, you can either run a test archive or archive process. You cannot purge test or archive data until the test or archive process has been completed on that data. If the process is not complete, then the purge process is disabled. After the process is complete, view the log for the Archive and Purge Process request to determine if there are any errors. If a test archive fails, then you can fix the problem, and rerun the test archive process. The archive tables are dropped if they were created during the failed run, and recreated with the same archive ID.
Navigate to the Archive and Purge window.
Enter a Type that specifies the category of all records associated with the purge. This value is originally entered on the Setup window.
View the following automatically generated data.
A unique ID is assigned to this purge that you can use to query or retrieve the purge. This is automatically generated upon saving this window. This unique ID is not created through OPM document ordering.
Test Status is the status of the test process. Values are Defined, not yet run, Test Archive in progress, Test Archive Completed Successfully, Test Purge Completed Successfully, Test Purge in Progress, Test Purge Process Failed, Test Process Failed, and Test Archive Process Failed.
Archive Status is the status of the archive process. Values are Archive Completed Successfully, Archive Process Failed, Archive in Progress, Defined, not yet run, Process Failed, Purge Completed Successfully, Purge Process Failed, and Purge in Progress.
Enter the Description of the purge in the Details section of the window. Each standard purge can have up to eight criteria. These criteria are:
Last creation date
Last document number
Latest modification date
Last organization code
Earliest creation date
First document number
Earliest modification date
First organization code
Latest creation date
Last document number
Latest modification date
Last organization code
Earliest creation date
First document number
Earliest modification date
First organization code
Enter the Criteria Values in the Details region.
Save the Archive and Purge window. The Purge ID is assigned and the total row details information is updated.
Choose any one of the following options from the Actions menu:
Test Process to test the process
Purge Test Rows to purge the tested rows
Archive Process to archive the information
Purge Archived Rows to purge the archived information
A dialog box appropriate to the selected option displays.
Enter the date and time in the Concurrent Request Datetime of the dialog box for the process to start. If you do not enter the date and time, then the process runs immediately.
Click OK. A note displays the request ID. You can use this ID to perform an inquiry on the process.
Refer to the "Performing an Archive and Purge Inquiry" topic for detailed information.
The following fields are updated automatically following a test or archive in the Total Row Details section of the window and are query only:
Tested displays the total number of rows that were tested, which is the data copied from the live tables.
Archived displays the total number of rows archived, which is the data removed from the live tables.
Purged displays the total number of rows removed from the archive tables.
Navigate to the Find Purge Types window.
Enter any of the following criteria to search for a purge type:
Purge Type to find a purge type using the purge type name.
Purge ID to find a purge type using the purge identification number.
Select Marked for Deletion as Yes to search for purge types marked for deletion.
Click Find.
Use the Archive and Purge Runtime window to set runtime criteria for the archive and purge process.
To set the runtime criteria
Navigate to the Archive and Purge Runtime window.
Select the Disable Constraints indicator to disable the database foreign constraints before running the archive and purge process.
Select Debug On to allow further details to be logged in the concurrent program log. This information is useful to debug any failures in the process.
Select Calculate Storage Indicator to calculate the storage requirements for the archive tables.
Enter Commit Frequency as the number of records that must be committed in a batch when the process is running. This field can be used to control the space allocation for the rollback segments when the process is running on a large set of data.
Click OK.
Use the Inquiry window to view the status of purges. The status of the purge appears in descending order from the most recently submitted purge to the oldest submitted purge. Purge status information is stored in table sy_purg_mst.
Navigate to the Archive and Purge Inquiry window.
Enter the Type to view all of the associated archives and purges. The following information displays in the Details section of the window:
Description for the purge type.
ID is the Purge identification value generated on the Archive and Purge window.
Start contains the date and time values, specifying when the purge began or is scheduled to begin.
Elapsed (Hours) is the elapsed time, in hours, for the purge listed.
Status is the value that specifies the progress of the archive process. The displayed values include 0= Defined; not yet run; 1= Archive in progress; 2= Archived Successfully; 3= Purge in progress; 4= Purge Completed Successfully; -1 = Unsuccessful Archive; -3 = Unsuccessful purge.
Tested indicates the total number of rows test archived for this run (copied into archive tables).
Archived indicates the total number of rows archived for this run (removed from live tables and put into archive tables).
Purged indicates the total number of rows purged for this run (dropped from the database).