KB01-027
Yes, the leave transactions are in the APR_PTO20200 table. You can use SQL to remove transactions, if needed. Before deleting transactions, you may want to see if an adjustment using the Leave Transaction Entry or Leave Mass Transaction entry is a better option first. https://www.integrity-data.com/faq-items/kb-comprehensive-leave-manager-transaction-adjustments/.
If you decide to remove leave transactions from SQL, we recommend backing up the company database before you begin. Once you remove the transactions, the Reconcile SQL script will need to be ran to recalculate the balance and summary information. https://www.integrity-data.com/faq-items/kb-comprehensive-leave-manager-reconcile-sql-script/.
You can first start with a select statement to back up the existing table.
/*This statement will create a table APR_PTO20200BACKUP and enter all transactions from APR_PTO20200 into this new table.*/
Use TWO /*Enter your company database*/
Select * into APR_PTO20200BACKUP from APR_PTO20200
Next, we can use a Select statement to determine the records you want to remove.
Use TWO /* Enter your company database*/
Select * from APR_PTO20200
where EMPLOYID = ‘ACKE0001’ /*Enter your Employee ID instead of ACKE0001*/
and APRPTOCode = ‘VACN’ /*Enter your leave code instead of VACN*/
Once all the criteria is specified and the select statement returns the rows you want to remove then change the Select * from to Delete. When you execute the delete statement in SQL it will remove all the records, which appeared in the select statement. Keep in mind, once the records are removed, unless you have a backup of the data, it will be gone.
Example for removing all transactions for an employee and leave code.
Use TWO /* Enter your company database*/
Delete APR_PTO20200
where EMPLOYID = ‘ACKE0001’ /*Enter your Employee ID instead of ACKE0001*/
and APRPTOCode = ‘VACN’ /*Enter your leave code instead of VACN*/
Example for removing all transactions for all employees and leave codes.
Use TWO /* Enter your company database*/
Delete APR_PTO20200
Lastly, you need to run the Comprehensive Leave Manager reconcile script. This script recalculates the transaction amounts and updates the summary table. https://www.integrity-data.com/faq-items/kb-comprehensive-leave-manager-reconcile-sql-script/.
**IMPORTANT** this script should be ran every time you edit data in SQL for APR_PTO20200 or APR_PTO00120 table. If you do not run this script, you will receive unexpected accrual calculations.