This page (revision-7) was last changed on 06-Jun-2022 17:31 by Kevin Higgs

This page was created on 26-Nov-2021 10:22 by JMyers

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note
7 06-Jun-2022 17:31 8 KB Kevin Higgs to previous
6 26-Nov-2021 10:22 2 KB kparrott to previous | to last
5 26-Nov-2021 10:22 2 KB jmyers to previous | to last
4 26-Nov-2021 10:22 2 KB rforbes to previous | to last
3 26-Nov-2021 10:22 2 KB JMyers to previous | to last
2 26-Nov-2021 10:22 2 KB JMyers to previous | to last
1 26-Nov-2021 10:22 2 KB JMyers to last

Page References

Incoming links Outgoing links
UMHP
UMHP

Version management

Difference between version and

At line 1 changed one line
!!!HISTORY PURGE
!!!HISTORY PURGE (UMHP)
At line 3 changed 11 lines
The Purge History (UMHP) form is used to physically delete rows from the database that are no longer necessary or that are passed there legal requirement for holding on to.
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
This process could take a very long time to run depending on the number of tables setup and the amount of data in each table as sequential reads will be done to check each rows dates for qualifying to be deleted.
Based on Table Details (IMTD) - processes tables that are set up with either
X Retained Periods (Numeric) and Period Type (Lexicon - see below) are both not null, or
X Purge Date is not null
Note: Purge Date will take precedence IF all three fields are 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.
!!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.
\\
%%information 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.%% \\
\\
At line 29 added one line
At line 16 changed 7 lines
Period Types
X 01 - Years- Specify the number of years to keep rows in the table. For example: retained periods was 2 and the system date is 15-JAN-2000 then any rows who's qualifying date is less than or equal to 15-JAN-1998 on the table being processed, will be deleted.
X 02 - Months- Specify the number of months to keep rows in the table. For example: retained periods was 6 and the system date is 15-JAN-2000 then any rows who's qualifying date is less than or equal to 15-JUL-1999 on the table being processed, will be deleted.
X 03 - Weeks- Specify the number of weeks to keep rows in the table. For example: retained periods was 1 and the system date is 15-JAN-2000 then any rows who's qualifying date is less than or equal to 08-JAN-2000 on the table being processed, will be deleted.
X 04 - Days- Specify the number of days to keep rows in the table. For example: retained periods was 30 and the system date is 15-JAN-2000 then any rows who's qualifying date is less than or equal to 16-DEC-1999 on the table being processed, will be deleted.
To run execute the function UMHP. BACKUP the database BEFORE executing a History Purge.
Include list of tables with possible recommendations.
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__. \\
\\
%%information 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|Edit:Internal.UMHP]
[{InsertPage page='Internal.UMHP' default='Click to create a new notes page'}]
This page (revision-7) was last changed on 06-Jun-2022 17:31 by Kevin HiggsTop