Unbuild Batches that Cannot Be Unbuilt Through the User Interface

The user will get the following error when attempting to unbuild the batch: 

‘The selected payroll batch cannot be unbuilt.  One or more employees in this batch is also included in a later batch.  Batches with common employees must be unbuilt in order.’ 

Problems can also occur if users build separate batches at the same time that incorporate some of the same time records.

In order to unbuild the unwanted batches, you must run scripts through DB Explorer. This process performs a true unbuild of the batch in question, restoring its time records to Time Entry so they can be edited and incorporated into a later batch. 

Solution

CAUTION: Extreme caution should be exercised when using these procedures. When in doubt, make a back-up. Furthermore, the best practice is to take a copy of the client DB at the time the issue comes up, bring it in-house and practice the process on that version before doing it for real.

DB Explorer will need to be installed on the client machine to complete the process.

1. Make a back-up of pathwaynt.ib, making sure to rename it so that it is clear that it contains the unwanted batches.
2. Open Client Payroll Manager and go to Build Payroll.
3. For each batch you want to unbuild, record the batch number.  
4. OPTIONAL: Perform these steps if you want to verify the results at the end of the process.
   
   1. Record the start and stop date for the batch.
   2. Run the Time Sheet for Labor History report, using the date range that corresponds to the batch start and stop dates and making sure to apply any filters as needed to match the records that would have been included in the batch.  
   3. At the end of the report, record the value for the total earnings and hours.
   4. Run Time Sheet Edit for the same date range, making sure not to filter anything out.
   5. At the end of the report, record the value for the total earnings and hours.
   6. Add the totals from both reports together and record it for later use.
5. Open DB Explorer and log on to the Pathway database.
6. Click the Enter SQL tab.
7. To find the ID of each batch, run the following SQL command for each batch you need to unbuild, substituting the batch number in place of <batch number>.
   A copy and pastable version of this script is available as a comment to this article under the title ‘Copy and Paste: Step 7’.SELECT PR_BATCH_NUMBER, PR_BATCH_NAME, PR_BATCH_ID FROM PAYROLL_BATCH
   WHERE PR_BATCH_NUMBER='<batch number>’
   • The <batch number> value is case sensitive.
   • The script will display the batch number, batch name and batch ID.
      
8. Record the PR_BATCH_ID for each batch.  
9. Now click the Enter SQL tab in DB Explorer.
10. For each batch, run the following four scripts, one-by-one, substituting the appropriate PR_BATCH_ID value in place of <batch ID>.
    A copy and pastable version of these scripts is available as a comment to this article under the title ‘Copy and Paste: Step 10 (4 Scripts)’.Script 1:
    DELETE FROM PRODUCTIVITY_TRANSACTION
    WHERE (PT_PR_BATCH_IDENTIFIER = ‘<batch ID>’)

    Script 2:
    UPDATE
      TIME_RECORDS
    SET
      TIME_PAYROLL_GENERATED_DATE = NULL,
      TIME_PAYROLL_BATCH_IDENTIFIER = NULL
     WHERE
      TIME_PAYROLL_BATCH_IDENTIFIER = ‘<batch ID>’

    Script 3:
    UPDATE
      PAYROLL_ADVANCE
     SET
      PA_GENERATED_DATE = NULL,
      PR_BATCH_ID = NULL
    WHERE
      PR_BATCH_ID = ‘<batch ID>’

    Script 4:
    UPDATE
      PAYROLL_BATCH
    SET
      PR_BATCH_GENERATED_DATE = NULL,
      PR_BATCH_GENERATED_BY_ID = NULL
    WHERE
      PR_BATCH_ID = ‘<batch ID>’

11. Log back in to Client Payroll Manager.
12. Open Build Payroll.
13. Review the list of batches. Any built-but-not-posted batch you wanted to remove should either not appear in the list or not be listed as built.  
14. OPTIONAL: Perform these steps if you performed the optional process at step 4.
    
    1. Run Time Sheet Edit report for the date range of the batch you unbuilt, making sure not to filter anything out.
    2. At the end of the report, review the value for the total earnings and hours. These values should match the totals you logged in step 4.6.

Once the process is complete and successful, you can delete the back-up you created when you started the process. 

Additional Comments

Steps 6 through 8 are provided as a simple way of locating the ID for a payroll batch number and name. You can find the ID without using SQL by reviewing the PAYROLL_BATCH table, Data tab in DB Explorer. 

See Also: Removing Batches That Have Been Built and/or Posted from Client Payroll Manager