You can periodically archive and purge data to meet data retention requirements and improve your system performance.
This chapter covers the following topics:
For any organization which maintains an ERP system, data is an important asset. Organizations must balance how much data to store and for what period of time against the cost and performance implications of large amounts of data. Periodically removing data that is no longer required enables your organization to operate more efficiently. Many organizations have predefined data retention requirements issued from regulatory authorities, such as the Food and Drug Administration (FDA) in the US and the General Data Protection Regulation (GDPR) in Europe. Due to these data retention requirements, organizations must periodically archive and purge data in their ERP production environment.
Use the following process to archive and purge your data:
Retain: Define the retention period for each Purge Type based on your organization's requirements. The default data retention period is 24 or 120 months, depending on the Purge Type. All data that has been created or updated outside of the retention period is eligible for archiving and purging.
Test: Select the data that you want to archive or purge, then review a report listing this data. The selected data is copied from the production tables to similarly-named test tables that are duplicates of the production tables. For example, test data copied from the production table GMO_BATCH_STEP_LOCK_DETAILS is copied into the test table GMA.GMO_BATCH_STP_LOCK_DTLS_T108, where T indicates a test table and 108 is the ID. View the test data and tables created in the output log.
Archive: Once the test data report correctly lists the data you want to archive, run the archive request. Archiving moves the data from the production tables to similarly-named archive tables that are duplicates of the production tables. For example, data archived from the production table GMO_BATCH_STEP_LOCK_DETAILS is moved into the archive table GMA.GMO_BATCH_STP_LOCK_DTLS_A108, where A indicates an archive table and 108 is the ID. Verify that the archived data and the names of the archive tables appear in the output log.
Purge: Once you no longer need a particular ID's tables, you can delete (or purge) them. The output log lists the tables purged. You can run different requests for an ID to separately purge the ID's test and the archive tables.
View the status of each ID: Use the Archive and Purge Inquiry window.
Warning: Do not archive or purge data from batches that were closed during the current period. Running costing engines such as the Actual Cost Process (ACP) after archiving or purging current period batches results in inaccurate item costs.
Predefined types enable you to test, archive, and purge selected records from OPM production tables associated with each type. Use the Archive and Purge Setup window to view the following types and their associated OPM production tables. You can change the record retention period for each type.
Types
PROD - the default Retention Period value is 24 months.
PROD data eligible for archiving and purging must have a Last Modification Date older than (System Date - Retention Period).
CSTP - the default Retention Period value is 120 months.
CSTP data eligible for archiving and purging must have an End Date older than (System Date - Retention Period).
SUBR - the default Retention Period value is 120 months.
SUBR data eligible for archiving and purging must have an End Date older than (System Date - Retention Period).
QCDT - the default Retention Period value is 24 months.
QCDT data eligible for archiving and purging must have a Last Modification Date older than (System Date - Retention Period).
Additional Information: Your environment may have other predefined types, but these additional types are not supported.
The PROD Purge Type enables you to test, archive, and purge selected records from the following OPM production tables:
GMO_CBR_XML
GMO_BATCH_STEP_LOCK_DETAILS
GMO_BATCH_STEP_LOCK_HISTP
GMO_BATCH_PROGRESSION
GMO_DISPENSE_CONFIG_INST
GMO_INSTR_SET_INSTANCE_B
GMO_INSTR_SET_INSTANCE_TL
GMO_INSTR_INSTANCE_B
GMO_INSTR_INSTANCE_TL
GMO_INSTR_ADD_ATTR_INSTANCE_B
GMO_INSTR_ADD_ATTR_INSTN_TL
GMO_INSTR_EREC_INSTANCE
GMO_INSTR_TASK_INSTANCE
GMO_INSTR_APPR_INSTANCE
GMO_MATERIAL_UNDISPENSES
GMO_MATERIAL_DISPENSES
GME_BATCH_STEPS_QUANTITY
GME_BATCH_GENEALOGY
GME_BATCH_SALES_ORDERS
GME_LAB_BATCH_LOTS
GMD_RESULTS
GMD_SAMPLES
GMD_SAMPLES_LAB
GMD_COMPOSITE_RESULT_ASSOC
GMD_COMPOSITE_RESULTS
GMD_COMPOSITE_SPEC_DISP
GMD_SPEC_RESULTS
GMD_SAMPLE_SPEC_DISP
GMD_EVENT_SPEC_DISP
GMD_WIP_SPEC_VRS
GMD_SAMPLING_EVENTS
GME_TEXT_TABLE_TL
GME_TEXT_HEADER
GME_BATCH_STEP_RSRC_SUMMARY
GME_RESOURCE_TXNS
GME_BATCH_STEP_RESOURCES
GME_BATCH_STEP_ACTIVITIES
GME_BATCH_STEP_TRANSFERS
GME_BATCH_STEP_DEPENDENCIES
GME_BATCH_STEP_ITEMS
GME_BATCH_STEPS
GME_BATCH_HISTORY
GME_MATERIAL_DETAILS
GME_BATCH_HEADER
GMF_BATCH_VIB_DETAILS
GMF_BATCH_REQUIREMENTS
GMF_INCOMING_MATERIAL_LAYERS
GMF_OUTGOING_MATERIAL_LAYERS
GMF_RESOURCE_LAYERS
The CSTP Purge Type enables you to test, archive, and purge selected records from the following OPM Costing tables:
GMF_LAYER_COST_DETAILS
CM_SCST_LED
CM_ACST_LED
CM_BRDN_DTL
CM_CMPT_DTL
The SUBR Purge Type enables you to test, archive, and purge selected records created by the OPM Accounting Preprocessor output tables:
GMF_XLA_EXTRACT_HEADERS
GMF_XLA_EXTRACT_LINES
GMF_TRANSACTION_VALUATION
The QCDT Purge Type enables you to test, archive, and purge selected records from the following OPM quality sample production tables:
GMD_COMPOSITE_RESULTS
GMD_COMPOSITE_RESULT_ASSOC
GMD_COMPOSITE_SPEC_DISP
GMD_EVENT_SPEC_DISP
GMD_RESULTS
GMD_SAMPLES
GMD_SAMPLE_SPEC_DISP
GMD_SAMPLING_EVENTS
GMD_SPEC_RESULTS
To update the retention period for a type of data
Navigate to the Archive and Purge Setup window.
Search for a Type of data.
In the Type field, use the list of values to select a valid type.
In the Retention Period (months) field, enter the number of months to keep this type of data in the production tables.
The Archive and Purge window enables you to enter criteria for and then run the test, archive, and purge processes across the OPM tables based on Purge Type.
The archive and purge functionality exists only for certain predefined sets of tables. Material transactions related to production are not archived and purged. To remove these, use the Inventory purge functionality. See the Oracle Inventory User's Guide for more information.
Select the record criteria values from the list of values for each field. The PROD type uses the following criteria:
Batch status. Select one of the following values:
-3 (Converted Firmed Order)
4 (Closed Batch): Selects the batches with a status of Closed that have also been posted to the general ledger.
Note: Ensure that all transactions associated with cancelled and converted firmed order batches have been posted to the general ledger before archiving. Archiving occurs regardless of whether or not transactions have posted to the general ledger.
First batch number and Last batch number. Select the batch numbers that meet the selected organization code criteria. The list of values displays the batch numbers and selected batch status.
First organization code and Last organization code. Enter a range of organizations containing real organization codes. For example, it is valid to enter FBP1 to FBP9 (where FBP1 and FBP9 are valid organization codes).
Earliest creation date, Latest creation date, Earliest modification date, and Latest modification date. Enter the date criteria using the following format: dd-mon-yyyy hh24:mi:ss. If you do not enter the time, the earliest date fields default to 00:00:00 (the beginning of the day) and the latest date fields default to 23:59:59 (the end of the day).
Select the record criteria values from the list of values for each field. The CSTP type uses the following criteria:
Legal Entity. Select a valid Legal Entity Name. For example, PRU-Vision Process Industries.
Calendar Code. Select a valid accounting calendar Name. For example, 2011.
Period Code. Enter a valid period Name. For example, 04 (Apr-2011).
Cost Method Code. Enter a supported Cost Type. For example, enter PMAC.
Select the record criteria values from the list of values for each field. The SUBR type uses the following criteria:
Legal Entity. Select a valid Legal Entity Name. For example, PRU Vision Industries.
Calendar Code. Select a valid accounting calendar Name. For example, 2012.
Period Code. Select a valid period Name. For example, July-2012.
Cost Method Code. Select a supported Cost Type. For example, select PMAC.
Transaction Source. Select one of the following Oracle E-Business Suite application sources:
A - All
C - Costing
E - Process Execution
I - Inventory
O - Order Management
P - Purchasing
Select the record criteria values from the list of values for each field. The QCDT type uses the following criteria:
Organization Code. Select a valid Organization Code from the list. For example, PR1.
Sample Source. Select one of the following quality sample sources:
C - Customer
I - Inventory
S - Supplier
W - WIP
Sample Disposition. Select one of the following quality sample dispositions:
0PL - Planned
4A - Accept
5AV - Accept with Variance
6RJ - Reject
7CN - Cancel
Earliest modification date. The modification date is the last date that the record in the table was updated. Select the earliest modification date for the date range of records to archive or purge.
Latest modification date. Select the latest modification date for the date range of records to archive or purge.
After specifying and saving the criteria values in the Archive and Purge window, you can run either a test process or an archive process. You cannot purge test or archive data until after completion of the test or archive process on that data. If the process is not complete, then the purge process is disabled. After the test or archive process is complete, view the log for either the test or archive process request to determine if there are any errors.
If a test process fails, you can fix the problem and then rerun the test process. Rerunning the test process deletes any test tables created during the earlier, failed run and recreates them with the same ID. If an archive process fails, then the selected data can be removed from some, but not all, of the production tables.
Review the output log to verify from which tables the data was removed.
Run the archive process again with the same criteria to create another ID.
Review the output log for the second ID to verify that the data was removed from the remaining production tables.
Navigate to the Archive and Purge window.
In the Type field, search for and select one of the following valid types:
PROD
CSTP
SUBR
QCDT
The criteria for selecting records to purge and archive appears in the Details region.
Optionally, enter a Comment for this set of criteria values.
Enter Criteria Values for each criteria.
Refer to the type's Criteria Value descriptions above.
Click Save.
A new ID is assigned. Notice that the following fields now contain values:
ID is a unique identifier that is automatically generated and assigned to this set of criteria values when you save this window.
Test Status is the status of the test process for the ID. 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
Test Archive Process Failed
Archive Status is the status of the archive process for the ID. Values are:
Archive Completed Successfully
Archive Process Failed
Archive in Progress
Defined, not yet run
Process Failed
Purge Completed Successfully
Purge Process Failed
Purge in Progress
Choose any one of the following options from the Actions menu:
Test Process to test the selected records for archiving.
Purge Test Rows to purge the tested rows. This option is available after successfully running the test process.
Archive Process to archive the selected records.
Purge Archived Rows to purge the archived information. This option is available after successfully running the archive process.
A dialog box for the selected option displays.
Select the Concurrent Request Datetime values to indicate when the process should 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 view the concurrent request output.
The following fields are updated when you requery the ID after submitting a test, archive, or purge in the Total Row Details section of the window:
Tested displays the total number of rows that were tested, which are the records that were copied from the production tables.
Archived displays the total number of rows archived, which are the records that were removed from the production tables.
Purged displays the total number of rows that were removed from the archive tables.
In the Archive and Purge window, enter an ID.
Click Find.
Use the Archive and Purge Inquiry window to view the status of all IDs. The status of the IDs appears in descending order from the most recently submitted ID to the oldest submitted ID.
Navigate to the Archive and Purge Inquiry window.
In the Type field, search for and select a type to view all of the IDs submitted for testing, archiving, and purging. The following information displays in the Details section of the window:
ID is the identification value generated in the Archive and Purge window.
Start contains the date and time when the test, archive, or purge for the ID began or is scheduled to begin.
Elapsed (Hours) displays the elapsed time, in hours, for the ID listed.
Status specifies the progress of the ID. 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 that were test archived for this run (copied into test tables).
Archived indicates the total number of rows that were archived for this run (removed from the production tables and added into archive tables).
Purged indicates the total number of rows that were purged for this run (deleted from the production tables).