HISTORY PURGE (UMHP)#
Overview#
The History Purge (UMHP) process can be very useful however it must be tested thoroughly before being run in a production environment. This function needs to have restricted access and very careful set up in order to ensure database integrity.Its intent is to allow clients to physically delete rows from the database that are no longer necessary or that are passed their legal requirement for retention. The requirements and set up for retention are up to each client to determine.
Set Up#
Before running the History Purge (UMHP) process, the set up on the Table Details (IMTD) form should be carefully checked to ensure changes have not inadvertently occurred. Restricted access should also be set up for IMTD to ensure that someone other than the System Administrator does not update the table information.There are 2 options for purging data based on set up on (IMTD). Tables will be set up using either;
- Purge Date
- Retained Periods (Numeric) and Period Type (Lexicon - see below) or
Purge Date (Date) - All records up to and including this date will be deleted.
Retained Periods (Numeric)- The number of years, months, weeks, or days that information is to be retained back to from the SYSDATE.
Period Type (Lexicon) - The type of period as defined in the lexicon X_PERIOD_TYPE
| Saved Value | Displayed Value | Description | Example |
|---|---|---|---|
| 01 | Years | Specify the number of years to keep rows in the table | Retained periods was 2 and the system date is 15-JAN-2001 then any rows who’s qualifying date is less than or equal to 15-JAN-1999 on the table being processed, will be deleted. |
| 02 | Months | Specify the number of months to keep rows in the table | Retained periods was 6 and the system date is 15-JAN-2001 then any rows who’s qualifying date is less than or equal to 15-JUL-2000 on the table being processed, will be deleted. |
| 03 | Weeks | Specify the number of weeks to keep rows in the table | Retained periods was 1 and the system date is 15-JAN-2001 then any rows who’s qualifying date is less than or equal to 08-JAN-2001 on the table being processed, will be deleted. |
| 04 | Days | Specify the number of days to keep rows in the table | Retained periods was 30 and the system date is 15-JAN-2001 then any rows who’s qualifying date is less than or equal to 16-DEC-2000 on the table being processed, will be deleted. |
Purge Date will take precedence IF all three fields are incorrectly filled in. Thus if you have it set to normally delete rows that are older then 5 years BUT for a special run want to delete all rows prior to a specific date, you can.
For tables that have a column called expiry or end date these columns are used for checking against the retention date. For all other tables the change date column is used.
Once an effective date table has been processed, its date adjustment procedure will be run to ensure continuity from the beginning to the end of time as we know it.
The following tables are commonly removed through the purging process:
| TABLE_NAME | Notes | Date Field Used | Purge Option 1 | Purge Option 2 |
|---|---|---|---|---|
| P2K_AM_EXECUTIONS | Where not in use by foreign key and prior to date specified | EXECUTION_END_DATE | Delete | |
| P2K_AM_PARAMETER_VALUES | Cascade deleted when Executions purged | CHANGE_DATE | Cascade | Delete |
| P2K_AT_LEAVE_ACCRUALS END_DATE | Delete | |||
| P2K_AT_LEAVE_ACCRUAL_DETAILS | Cascade deleted when Accruals purged | CHANGE_DATE | Cascade | |
| P2K_AT_LEAVE_LINES | END_DATE | Delete | ||
| P2K_AT_LEAVE_LINE_DETAILS | Cascade deleted when Leave Lines purged | CHANGE_DATE | Cascade | |
| P2K_BE_BENEFIT_LINES CHANGE_DATE | Delete | |||
| P2K_BE_BENEFIT_LINE_DETAILS | Cascade deleted when Benefit Lines purged | CHANGE_DATE | Cascade | |
| P2K_CM_CALENDAR_DETAILS | CALENDAR_END_DATE | Delete | ||
| P2K_PR_BATCHES | When no longer has children | CHANGE_DATE | Delete | |
| P2K_PR_DISBURSEMENTS | When no longer has children | CHANGE_DATE | Delete | |
| P2K_PR_CHECKS | Must have cleared bank | CHANGE_DATE | Delete | |
| P2K_PR_DEPOSITS | Must have been sent to bank | CHANGE_DATE | Delete | |
| P2K_PR_PAY_STUBS | Not needed once Checks/Deposits have been printed. | CHANGE_DATE | Delete | |
| P2K_PR_PAY_STUB_DETAILS | Cascade deleted when Pay Stubs purged | CHANGE_DATE | Cascade | Delete |
| P2K_PR_JOURNAL_ENTRIES | CHANGE_DATE | Delete | ||
| P2K_PR_JOURNAL_DETAILS | Cascade deleted when Journal Entries purged | CHANGE_DATE | Cascade | Delete |
| P2K_PR_PAY_HEADERS | CHANGE_DATE | Delete | ||
| P2K_PR_PAY_LINES | Cascade deleted when Pay Headers purged | END_DATE | Cascade | Delete |
| P2K_PR_PAY_LINE_DETAILS | Cascade deleted when Pay Headers purged , Cascade deleted when Pay Lines purged | CHANGE_DATE | Cascade | Cascade |
| P2K_PR_PAY_PC_AMOUNTS | Cascade deleted when Pay Headers purged | CHANGE_DATE | Cascade | Delete |
| P2K_PR_NET_PAY_SPLITS | Cascade deleted when Pay Headers purged | CHANGE_DATE | Cascade | Delete |
| P2K_PR_PAY_LEAVE_ACCRUALS | Cascade deleted when Pay Headers purged | CHANGE_DATE | Cascade | Delete |
| P2K_PR_PAY_TRANSACTIONS | END_DATE | Delete | ||
| P2K_PR_PAY_TRANS_TIME_CODES | Cascade deleted when Pay Transactions purged | CHANGE_DATE | Cascade | |
| P2K_PR_SUNDRY_LINES END_DATE | Delete | |||
| P2K_PR_SUNDRY_LINE_DETAILS | Cascade deleted when Sundry Lines purged | CHANGE_DATE | Cascade | |
| P2K_PR_TODATE_HEADERS | CHANGE_DATE | Delete | ||
| P2K_PR_TODATE_PC_AMOUNTS | Cascade deleted when Todate Headers purged | CHANGE_DATE | Cascade | |
| P2K_SA_PERSONNEL_ACTIONS | PA_EXPIRY_DATE | Delete | ||
| P2K_SA_PERSONNEL_CHANGES | Cascade deleted when Personnel Actions purged | CHANGE_DATE | Cascade | |
| P2K_SA_APPROVALS | Cascade deleted when Personnel Actions purged | CHANGE_DATE | Cascade | Delete |
| P2K_TS_AUTHORIZED_JOBS END_DATE | Delete | |||
| P2K_TS_WORK_RESTRICTIONS | Cascade deleted when Authorized Jobs purged | END_DATE | Cascade | Delete |
| P2K_TS_TIME_ENTRIES END_DATE | Delete | |||
| P2K_TS_TIME_EXCEPTIONS | Cascade deleted when Time Entries purged | END_DATE | Cascade | Delete |
| P2K_TS_WORK_TEAMS END_DATE | Delete | |||
| P2K_TS_TIME_BATCHES | Cascade deleted when Work Teams purged | CHANGE_DATE | Cascade | Delete |
| P2K_TS_TEAM_POSTS | Cascade deleted when Work Teams | CHANGE_DATE | Cascade | Delete |
| P2K_TS_EVENT_SITES END_DATE | Delete | |||
| P2K_TS_STATION_REQUIREMENTS | Cascade deleted when Event Sites purged | END_DATE | Cascade | Delete |
| P2K_TS_CLOCK_ENTRIES | CHANGE_DATE | Delete | ||
| P2K_TS_CALL_LOGS | CHANGE_DATE | Delete |
The following tables should not be removed through the purging process:
| TABLE_NAME | Notes |
|---|---|
| P2K_HR_IDENTITIES | Script available ‘db_person_delete.sql’ |
| P2K_HR_ASSIGNMENTS | Script available ‘db_person_delete.sql’ |
| P2K_HR_ASSIGNMENT_DETAILS | Script available ‘db_person_delete.sql’ |
| P2K_HR_EMPLOYMENTS | Script available ‘db_person_delete.sql’ |
| P2K_HR_PERSONALS | Script available ‘db_person_delete.sql’ |
| P2K_HR_STATISTICS | Script available ‘db_person_delete.sql’ |
| P2K_PR_LOADED_PAY_LINES | Not necessary - removed after load |
| P2K_PR_LOADED_CLEARED_CHECKS | Not necessary - removed after load |
Process#
To run execute the function UMHP.BACKUP the database BEFORE executing a History Purge.
This process could take a very long time to run depending on the number of tables setup to be purged or selected through the UMHP process. The amount of data found in each table will also impact processing time, as sequential reads will be done to check each rows dates for qualifying to be deleted.
| Report Parameters | |
|---|---|
| Trace Level | Optional, LOV Available When performing a trial calculation, you may specify the level of message that the calculation report should display. This field is used for troubleshooting purposes ‘Exceptions Only’ is the default value |
| Specific Tables | This field identifies the tables that will be purged. This is a mandatory parameter for the Purge History process. |
