Purge Aged Store/Day Transaction, Total Value and Error Data from Sales Audit (sapurge)
|
Module Name |
sapurge.pc |
|
Description |
Purge Aged Store/Day Transaction, Total Value and Error Data from Sales Audit |
|
Functional Area |
Oracle Retail Sales Audit |
|
Module Type |
Admin |
|
Module Technology |
ProC |
|
Catalog ID |
RSA21 |
|
Wrapper Script |
rmswrap_out.ksh |
Design Overview
This program will be run daily to control the size of the tables in the sales audit database. Older information will be deleted to ensure optimal performance of the system as a whole.
Different kinds of data need to be kept in the system for different amounts of time. Transactions, all associated transaction details, and Totals calculated or reported for a store day will be deleted when they meet the following criteria:
-
The Business Date for those transactions and totals is older than or equal to today's date minus the days_before_purge parameter set up on the sales audit system parameters.
-
No locks exist on the store/day.
-
One of the two following statements is true for the store/day:
-
Fully loaded, and all errors either corrected or overridden (sa_store_day.audit_status is A (Audited) and sa_store_day.data_status equals F (Fully loaded)). In addition, there are no outstanding exports (records for the store/day in the sa_export_log table where sa_export_log.status equals R (Ready for export)).
-
Never loaded (sa_store_day.audit_status is U (Unaudited) and sa_store_day.data_status equals R (Ready for import)).
-
Flash Sales data will be deleted when it meets the following criteria:
-
Date is two years before today's date minus the days_before_purge parameter set up on the sales audit system parameters.
-
Company open and close dates will also need to be kept for two years plus days_before_purge, so that the historical comparisons in flash sales reporting carry the appropriate weight.
Voucher data will be deleted when it meets the following criteria:
-
The redeemed date or the escheat date for the specific voucher type is before today's date minus the purge_no_days on sales audit voucher options table for the corresponding voucher type.
The program can also take in a list of store_day_seq_no to delete. For example, the command line could be: sapurge userid/passwd 1000 1001 1002, where 1000, 1001 and 1003 are store_day_seq_nos that you want to delete. These must also meet the criteria defined above. If a store_day_seq_no is passed to this program, but does not meet the criteria, an error will be written out to the error log.
An output file will be created to store a record for each store and business date that was purged. The file name must be passed in at the command line as a parameter to sapurge.
This program will also purge the data, which is being used for Sales Audit Auditor Framwork and purging criteria based on days_before_purge value from SA_SYSTEM_OPTIONS table.