Deleting Empty Batches in SOP

Schedule this script to run periodically against your company database to delete empty SOP batches automatically. It will check to verify that there aren't any transactions in the batch and that there is not a batch activity record first.

DECLARE @INTERID varchar(10),
@CMPNYNAM varchar(31)

SET @INTERID = DB_Name()
SELECT @CMPNYNAM = CMPNYNAM from DYNAMICS.dbo.SY01500 where INTERID = @INTERID

DELETE SY00500
where BCHSOURC = 'Sales Entry'
and BACHNUMB not in (select BACHNUMB from SOP10100)
and BACHNUMB not in (select BACHNUMB from DYNAMICS.dbo.SY00800 where CMPNYNAM = @CMPNYNAM and TRXSOURC = 'Sales Transaction Entry')