»
»
Using SQL Statements to Delete Batches That Have Been Posted or Can’t Otherwise be Unbuilt
Using SQL Statements to Delete Batches That Have Been Posted or Can’t Otherwise be Unbuilt
Even when using any method provided in the user interface (presuming any employees are common to both the built and the posted batch), 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.’
Or, it may be necessary to remove a batch that was actually posted. For example, during implementation, the parallel test may end up in the ‘live’ copy of the database if the sequence of events is not managed properly.
Solution
In order to remove the unwanted batches, you must run scripts through DB Explorer.
IMPORTANT NOTE: This DOES NOT restore the time records for the unbuilt/unposted batches like unbuilding. The scripts remove the time records from the DB completely. As a result, do not use this process if the customer needs to perform a true ‘unbuild’ on the batches in question and restore the time records back to a live or useable state. To perform an unbuild that restores the record, see Unbuild Batches that Cannot Be Unbuilt Through the User Interface.CAUTION: The following scripts delete historical time records and alter the database’s historical information. Extreme caution should be exercised when using these procedures. 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.
- Make a back-up of pathwaynt.ib, making sure to rename it so that it is clear that it contains the unwanted batches and history.
- Open Client Payroll Manager and go to Build Payroll.
- For each batch you want to remove, record:
- The batch number.
- Whether the batch is built but not posted, or built and posted.
- Open DB Explorer and log on to the Pathway database.
- Click the Enter SQL tab.
- To find the ID of each batch, run the following SQL command for each batch you need to remove, 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 6′.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.
- Record the PR_BATCH_ID for each batch.
- For each batch that was built and posted, run the following script, substituting the appropriate PR_BATCH_ID value in place of <batch ID>. A copy and pastable version of this script is available as a comment to this article under the title ‘Copy and Paste: Step 8’. DELETE FROM PRODUCTIVITY_TRANSACTION WHERE (PT_PR_BATCH_IDENTIFIER = ‘<batch ID>’);
- For each batch that was built but never posted, run the following four scripts on the Enter SQL tab, one-by-one, substituting the appropriate PR_BATCH_ID value in place of <batch ID>. Each script must be executed
separately. A copy and pastable version of these scripts is available as a comment to this article under the title ‘Copy and Paste: Step 9 (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>’; - Log back in to Client Payroll Manager.
- Open Build Payroll.
- Review the list of batches. Any built-but-not-posted batch you wanted to remove should appear as an ‘unbuilt’ batch.
- NOTE: Batches built-and-posted will still appear.
- Access the reports area.
- Run the following reports to verify that the historical payroll information is gone. If you get the results specified, it is generally an indication that you have successfully removed the batch history information. Employee Earnings Information: In parameters, select the built-and-posted batch you wanted to remove, then run the report. Result: the report should have no records. Payroll Edit List: In parameters, select the built-and-posted batch you wanted to remove, then run the report. Result: the report should have no records. Time Sheet for Labor History: In parameters, select the date range for any of the batch data you intended to remove. Result: the report should have no records.
- If the reports in step 15 are coming up blank, go to Tools>Administrator>Access Groups.
- Select the Vertex Administrator access group (assuming this is the access group in which the current user appears).
- Click the Subjects tab.
- Add a check mark to the ‘Delete’ option for Build Payroll.
- Close the Access Groups dialog box.
- If necessary, close out of Build Payroll.
- Open Build Payroll.
- Select the batch you want to delete.
- Click the Delete button and answer ‘Yes’ when prompted.
- Repeat steps 23-24 for any other batches you need to delete.
- Repeat steps 16-18.
- Remove the check mark for the ‘Delete’ option under Build Payroll.
- Close the Access Groups dialog box.
Once the process is complete and successful, you can delete the back-up you created when you started the process.
Steps 5 through 7 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.