Overview

While working with CORT Payroll, you may have had to manually change some check numbers, perhaps because the old check numbers were incorrectly listed. You notice that after this change, you are unable to mark these checks as Cleared in CORT Payroll. 

Solution

Follow the steps below in order to directly modify the status of these checks to cancelled in the CORT Payroll Database: 

1. Open the SQL Server Management Studio (SSMS) and navigate to your CORT Payroll database.
2. In SSMS, execute the following queries:
   • Verify the details of the checks that need to be cleared/cancelled: 
     

     -- Verify checks details
     Select CHECK_NUMBER, PAYROLL, CHECK_CLEARED_BANK, NET, DATE_CANCELLED, EMP, CHECK_TYPE, VOIDED
     FROM [PYAppRole].CHECKS 
     where (payroll like '%XXXX') 
     and check_number like 'NNNNN%'

     where (payroll like '%XXXX') is used to identify the Payroll number to which these checks are linked, and check_number like 'NNNNN%' is used to identify the check numbers which you are unable to cancel from the UI. Modify these queries as required to get the complete list of all checks. As an example, see below a version of this query and its associated results: 
     ​
   • As a rollback plan, create a backup of the existing records: 
     

     -- Create a backup of the records
     Select * INTO [PYAppRole].CHECKS_BK
     FROM [PYAppRole].CHECKS 
     where (payroll like '%XXXX') 
     and check_number like 'NNNNN%'

   • Clear the checks and set the cancellation date:
     

     -- Cancel the checks
     UPDATE [PYAppRole].CHECKS 
     set check_cleared_bank = 'Y', date_cancelled = 'YYYY-MM-DD 00:00:00.000' 
     where (payroll like '%XXXX') 
     and check_number like 'NNNNN%'

     where the date_cancelled is the date you want the UI to display as the date of cancellation. Once this script is executed, you will get a message stating the number of records that have been updated, e.g. from executing it on the 7 rows above: 
     ​

Testing

1. Verify Cancellation:

1. Login to the Check Reconciliation - I program again. Navigate to List > Display Cancelled for Payroll Range:
   
2. In the following window prompt, enter the payroll range for which you modified the check status: 
   ​
   
   
3. The range will show the checks that were cancelled using the procedure above. From the same example as previously: 
   ​

2. Verify that Checks are Cleared: 

1. Login to the Check Reconciliation - I program again. Navigate to List > Display Outstanding to Date:
   ​
   
   
2. In the following Window Prompt, enter the date that you entered as the cancelled_date in the procedure above, and make sure that the Include Cleared box is unchecked:
   
   ​
3. Since the checks are now cleared, they will not show up in the subsequent window that lists outstanding checks.