Does anyone have a script to delete empty batches? I was just visiting a client who has used GP since 1998. They have thousands of empty batches. It appears no one has ever considered deleting batches.I can create the script but I told them I would check first if one exists.
This is what I would up using.
delete [dbo].[SY00500]
where NUMOFTRX=0 and bchsourc='Sales Entry' and bachnumb not in(select bachnumb from [dbo].[sop10100])
Please use with caution as it does delete batches.
Another one of my "batch management" scripts. Specifically deletes Sales Batches with no transactions. Also checks SOP10100 to make sure there aren't any transactions associated with the batch (for example if I moved an invoice, but forgot to run my recalculate script or checklinks).
I added an additional "batches with dashes" where clause because that is how our sales batches are created from the invoicing system, so you will need to remove that.
select *
--Delete s
from sy00500 s
where s.numoftrx = 0 -- Only zero count
and s.series = 3 -- Sales
and s.bchsourc = 'Sales Entry' -- Sales Entry
and charindex('-',s.bachnumb) > 0 -- batches with a dash
and s.bachnumb not in (select Bachnumb -- Don't have existing invoice in SOP10100
from sop10100
group by bachnumb)
That should be straightforward: Select on the Batch Headers table and include the restrictions for the transaction count being blank and other relevant information. (Making a copy of the Batch Headers table may also be useful in case Check Links recreates a batch and you want to look back on what it looked like before the delete.)
Lisa. this is a client who was never trained on the use of batches. When I watched what they were doing, using SOP as an example, they would create an orders batch with their initials and today's date. This batch would start with 'ORD'. When it was time to invoice this order they would transfer it and the create a new batch that starts with 'INV'. So once all the sales orders were transferred the 'ORD' batch was empty. They have been doing this every day for 20 years. I fully intend to test this on a copy on their database in our data center.
It depends on why the batches are empty. (If they are truly empty batches with no abandoned rows in the source table, then it is a simple delete from the Batch Headers table where the batch number is ''. I like to include some additional data checks like DEX_ROW_ID < x and/or Document Count is zero.)
I find Inventory (and financial) tables like to have abandoned rows in them and Check Links creates blank batches in the Batch Headers table (with a reference to the Batch being created by Check Links.) Those you need to delete the blank rows from the source tables and then the blank batches from Batch Headers. When you are done with the cleanup, run Check Links on the work tables to verify you found the source of the error.
I am purposely not including key information of the script as it is a destructive script. I'll also add the disclaimer about restoring to a test company and verifying yorur scripts there first.
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,409
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
