Delete empty Sales Batches

(0) Share

Report

Posted on by Community Member

Is there any way to mass delete empty sales batches? Due to the way that sales orders are invoiced there are many empty batches by the end of the month which will not be reused. The only method I can think of would be to use a SQL statement on the sy00500 table to delete any batches with 0 as the number of transactions. Any Utility available that could accomplish this? Thank you!

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Chris Isbell on at

Like (0)

Report

I think deleting from the SY00500 table would be the simplest way. You could also make a SQL job to do this, perhaps at the end of each month.

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (1)

Report

If you do create a script to delete sales batches, make sure you check more than just the document count. GP is notorious for incorrect batch counts and totals. Have your script check for any records with the Batch ID you are about to delete. This way you will be sure it will only delete truly empty batches.

Was this reply helpful? Yes No
Tristan Clores 2,812 on at

Like (0)

Report

Hi Susan,

By default, GP should delete the sales batches once all transactions in it are posted/completed and moved to History. Try using the Reconcile - Remove Sales Documents utility and use the Remove Completed Documents option.

If it does not work, you can then use SQL. Make a FULL BACKUP. Use the NUMOFTRX = 0, BCHSOURC = 'Sales Entry' as part of your delete statement.

Let me know how this goes.

Regards,

Tristan

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

Don't forget that sales batches can have quotes and orders in them, so when you print the sales batch, nothing prints so you think it is empty. Also, you can move transactions from one batch to another so a zero transaction batch can result from this as well. Just have your script count the number of transactions associated with the batch. If the result is zero, then it is safe to remove the batch.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

First, make a full backup of the Company Database Affected.

Use this code to make sure you have selected a Company Database and not the DYNAMICS database:

SELECT * FROM SY00500 ORDER BY BACHNUMB;

(I recommend you save these results for easy review)

Use this code to make the deletion:

DELETE FROM SY00500 WHERE NUMOFTRX=0 AND BCHSOUR='Sales Entry';

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

First, Richard's advice is spot on, be careful. I am not sure what your root cause of this problem is, but Willoware makes a really cool batch handling application that might make this a moot point. willoware.com/.../gp-powerpack

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

/*

Used to reset the Batch Header Sales Transaction Entry Batch Counts

Execute from the GYP System Database

*/

DECLARE @Cmd AS VARCHAR(5000)

DECLARE MyCurrsor CURSOR FOR

SELECT Cmd = '

USE ['+RTRIM(INTERID)+'];

UPDATE SY00500

SET NUMOFTRX= ISNULL((SELECT COUNT(1) FROM SOP10100 WHERE VOIDSTTS=0 AND BACHNUMB=A.BACHNUMB),0)

FROM SY00500 A

WHERE A.BCHSOURC=''Sales Entry''

AND NUMOFTRX<>ISNULL((SELECT COUNT(1) FROM SOP10100 WHERE VOIDSTTS=0 AND BACHNUMB=A.BACHNUMB),0);

'

FROM SY01500

OPEN MyCurrsor;

FETCH NEXT FROM MyCurrsor INTO @Cmd;

WHILE @@FETCH_STATUS = 0

BEGIN

EXECUTE(@Cmd);

FETCH NEXT FROM MyCurrsor INTO @Cmd;

END

CLOSE MyCurrsor;

DEALLOCATE MyCurrsor;

Was this reply helpful? Yes No