DESCRIPTION: Accidentally confirmed pay for the whole group.
SOLUTIONS:
There are two options available to reverse a Confirmed Pay:
- To restore the database from Last Night Backup OR
- The below Tables need to be deleted and updated.
DISCLAIMER: Before running this script, perform a full DB Backup and ensure users are not in the system.
Replace DDMMYYYY with the current date under all scripts.
Then, replace the EMP_GROUP_CODE and PAY_NUMBER with the appropriate Employee Group and Pay Number.
BACKUP TABLE STATEMENT:
select *
into EC_PAYRUN_POINTER_ED_DDMMYYYY
from EC_PAYRUN_POINTER
select *
into EC_EMPLOYEE_PAY_INPUT_ED_DDMMYYYY
from EC_EMPLOYEE_PAY_INPUT
select *
into EC_EMPLOYEE_PAY_INPUT_EI_ED_DDMMYYYY
from EC_EMPLOYEE_PAY_INPUT_EI
select *
into EC_EMPLOYEE_PAY_RECORD_ED_DDMMYYYY
from EC_EMPLOYEE_PAY_RECORD
select *
into EC_EMPLOYEE_PAY_RECORD2_ED_DDMMYYYY
from EC_EMPLOYEE_PAY_RECORD2
select *
into EC_EMPLOYEE_ACCOUNTING_ED_DDMMYYYY
from EC_EMPLOYEE_ACCOUNTING
select *
into EC_EMPLOYEE_DAILY_STAT2_DATA_ED_DDMMYYYY
from EC_EMPLOYEE_DAILY_STAT2_DATA
select *
into EC_EMPLOYEE_DAILY_EI_DATA_ED_DDMMYYYY
from EC_EMPLOYEE_DAILY_EI_DATA
select *
into EC_EMPLOYEE_CHEQUE_ED_DDMMYYYY
from EC_EMPLOYEE_CHEQUE
--DELETE STATEMENT:
delete EC_PAYRUN_POINTER
where EMP_GROUP_CODE = '6'
and PAY_NUMBER = '202602'
delete EC_EMPLOYEE_PAY_INPUT
where EMP_GROUP_CODE = '6'
and PAY_NUMBER = '202602'
delete EC_EMPLOYEE_PAY_INPUT_EI
where EMP_GROUP_CODE = '6'
and PAY_NUMBER = '202602'
delete EC_EMPLOYEE_PAY_RECORD
where EMP_GROUP_CODE = '6'
and PAY_NUMBER = '202602'
delete EC_EMPLOYEE_PAY_RECORD2
where EMP_GROUP_CODE = '6'
and PAY_NUMBER = '202602'
delete EC_EMPLOYEE_ACCOUNTING
where EMP_GROUP_CODE = '6'
and PAY_NUMBER = '202602'
delete EC_EMPLOYEE_DAILY_EI_DATA
where RUN_EMP_GROUP_CODE = '6'
and RUN_PAY_NUMBER = '202602'
delete EC_EMPLOYEE_CHEQUE
where EMP_GROUP_CODE = '6'
and PAY_NUMBER = '202602'
--UPDATE STATEMENT:
update EC_EMPLOYEE_YTD_ENTITY
set AMOUNT = AMOUNT - CURRENT_AMOUNT
where EMP_GROUP_CODE = '6'
and PAY_NUMBER = '202602'
update EC_EMPLOYEE_YTD_ENTITY
set CURRENT_AMOUNT = '0'
where EMP_GROUP_CODE = '6'
and PAY_NUMBER = '202602'
update EC_EMPLOYEE_SEN_SERV
set SEN_SERV_VALUE = SEN_SERV_VALUE - CURRENT_SEN_SERV_VALUE
where EMP_GROUP_CODE = '6'
and PAY_NUMBER = '202602'
update EC_EMPLOYEE_SEN_SERV
set CURRENT_SEN_SERV_VALUE = '0'
where EMP_GROUP_CODE = '6'
and PAY_NUMBER = '202602'
update EC_PAY_PERIOD_CALENDAR
set PROCESSED_BY_PAYROLL_IND = 'N'
where EMP_GROUP_CODE = '6'
and PAY_NUMBER = '202602'