Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Katie Pieczynski]

Hello,
I was wondering if there is a way to batch delete empty sales batches.
Every Monday we get orders integrated into our SOP module and they each go into their own batch.  When they are ran and invoiced the batch is empty.  So at the end of the day I go into Master Posting and manually delete each empty batch by highlighting it, opening it up and then deleting it.  I was hoping there was a way to select them all at the same time and delete.
Thank you,

——————————
Katie Pieczynski
Accounts Receivable Manager
DANSR
Champaign IL
——————————

[Charles Allen]

Great question!

The short answer is there is no utility in GP to remove empty batches. 

You can use SQL to do it. You have to be careful and make sure the batch transaction totals are right by running Reconcile and Check Links prior to deleting the batches with 0 transactions just in case the batch totals are wrong.

In SY00500 (found in each company database) all saved batches are stored. In the table is a column named NUMOFTRX (Number of transactions).

You can use a statement like DELETE SY00500 WHERE NUMOFTRX = 0.

Just, please, make a backup of the table and, as mentioned above, run Check Links and Reconcile on the batches to make sure that column value is correct.

——————————
Charles Allen
Senior Managing Consultant
BKD Technologies
Houston, TX
——————————
——————————————-

[Beat Bucher]

Hi Katie,
Charles is correct.. GP has no option to take care of this, and using a SQL script is one way of doing it..
But not everyone has access to the SQL server Studio Managment back-end.. and not every user has the permissions to access the server.. Another way to manage this would be with GP Power Tools (GPPT).. which has some functions to execute SQL scripts Under user context in GP, and you can decide who has the rights and in which company the script can be executed..
The script to validate would look something like this:

SELECT SY00500.BCHSOURC

,SY00500.BACHNUMB

,SY00500.SERIES

,SY00500.USERID

,SY00500.NUMOFTRX

,SY00500.BCHTOTAL

,DYNAMICS..SY00800.USERID AS ‘Edited by’

FROM SY00500

LEFT OUTER JOIN DYNAMICS..SY00800 ON SY00500.BCHSOURC = DYNAMICS..SY00800.BCHSOURC

AND SY00500.BACHNUMB = DYNAMICS..SY00800.BACHNUMB

WHERE (SY00500.BCHSOURC LIKE ‘sales%’)

AND (DYNAMICS..SY00800.USERID IS NULL)

AND (SY00500.NUMOFTRX = 0)

AND (SY00500.BCHTOTAL = 0)

And the clean-up like this :

DELETE FROM SY00500

FROM SY00500 LEFT OUTER JOIN DYNAMICS.dbo.SY00800 ON SY00500.BCHSOURC = DYNAMICS.dbo.SY00800.BCHSOURC

AND SY00500.BACHNUMB = DYNAMICS.dbo.SY00800.BACHNUMB

WHERE (SY00500.BCHSOURC LIKE ‘sales%’)

AND (DYNAMICS.dbo.SY00800.USERID IS NULL)

AND (SY00500.NUMOFTRX = 0)

AND (SY00500.BCHTOTAL = 0)

The validation about the DYNAMICS..SY00800 is to make sure you’re not deleting a batch somebody is currently editing.. .

——————————
Beat Bucher
Business Analyst, Dynamics GP MVP
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
+1-514-489-4267
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
——————————
——————————————-

[Jon Colesby]

Katie, 

I would add to the other ideas by saying if this is a weekly process that automates bringing these transactions in, would not automating the cleanup (via SQL) be possible? Maintenance plans in SQL could take a sql script and automate them to run at a specific time. If would wanted to keep track of what was deleted, SQL Mail could also be leveraged to send you an email showing you what batches were being removed. We use this model for automating reporting of vendor and customer adds and changes from an auditing standpoint. 

The only caution I would have is that if you have any recurring financial batches, they could potentially have no transactions in them when they are created. Checking on batch source would be important (as was done in one of the suggestions). 

——————————
Jon Colesby
Tampico Beverages, Inc.
Chicago IL
——————————
——————————————-

[Katie Pieczynski]

Thank you for the responses.
I do have access to SQL but I am not sure if I am comfortable enough to run the script. ( I can also ask our partner to set something up)  It is only on Mondays that this happens and the reason is that we have Ebridge import our Amazon orders.  So the most that usually come in is around 20-30. 
If I did decide to run an automated script, do you think I could add in a filter for the batch to “start with or include” ECCSS.  Our imported ordered have a specific batch name that we do not use for anything else.  I then could run it the day after because I would know that all of the batches are empty.
It isn’t a huge deal to delete them one by one but I do like convenience…..

——————————
Katie Pieczynski
Accounts Receivable Manager
DANSR
Champaign IL
——————————
——————————————-

[Bruce Strom]

Personally, I WOULD NEVER blindly run a SQL script like:
DELETE SY00500 WHERE NUMOFTRX = 0

ALWAYS SELECT before you DELETE or UPDATE,
unless you like living on the edge or you want to risk jumping into the abyss.

If there are posting issues the number of transactions can be inaccurate.

To find blank sales batches, you can first run:

SELECT * FROM SY00500

WHERE SERIES = 3 and BCHSOURC = ‘Sales Entry’ and

bachnumb not in ( select bachnumb from SOP10100 )

IF THE RESULTS LOOK REASONABLE,
then you can run the analogous DELETE script.

Also, if you want to reconcile the SOP Batch totals,
go to Dr Google and search for “gomez dynamics sop batches”
and you will find the appropriate SQL scripts.

——————————
Bruce Strom
Programmer Analyst
Associated Grocers of Florida
sunrise FL
——————————
——————————————-

[Jon Colesby]

I agree 100% that before going the automated approach you should (a) ensure you are comfortable with it and (b) use as much ‘refinement’ to ensure you are only removing the entries that are empty and created as a result of another automated process. 
Not knowing the Ebridge integration routine, I cant say what those parameters are. 

When doing anything through SQL, extreme caution should be used. 

I would also go back to EBridge and see they have a method to ensure that empty batches are not brought it. That would be correcting the problem at the source… which is the best approach. 

——————————
Jon Colesby
Tampico Beverages, Inc.
Chicago IL
——————————
——————————————-