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.

---

Notes #

Click to create a new notes page