Move SOP trs from one batch to other

-- Created by Mariano Gomez, MVP
-- Maximum Global Business, LLC
declare @bachnumb char(15), @strpostdate char(20), @interid char(5)
declare @postdate datetime
declare @numtrx int, @batchamount numeric(19,5)
declare @noteindex numeric(19,5)
declare @l_result integer, @error_state integer

set @bachnumb = 'XCPTNS' + convert(char(4), year(getdate())) + '_' + convert(char(4), month(getdate())) + '_' + convert(char(4), day(getdate()))

-- drop timestamp
set @strpostdate = convert(char(20), getdate(), 101)
set @postdate = convert(datetime, @strpostdate)

-- move the left behind order transactions to
-- the new exception batch
update sop10100 set bachnumb = @bachnumb where bachnumb = 'SHIPTODAY' and soptype = 2

-- adjust the 'Ship Today' batch amounts for the
-- documents that did get transferred
select @numtrx = IsNull(count(sopnumbe), 0), @batchamount = IsNull(sum(docamnt), 0) from sop10100 where bachnumb = 'SHIPTODAY'
update SY00500 set NUMOFTRX = @numtrx, BCHTOTAL = @batchamount where bachnumb = 'SHIPTODAY'

-- get next note index to assign to the new batch
SELECT @interid = DB_NAME()
EXEC @l_result = DYNAMICS..smGetNextNoteIndex @interid, @@SPID, @noteindex output, @error_state output

-- get the number of transactions and amounts for the new batch
select @numtrx = IsNull(count(sopnumbe), 0), @batchamount = IsNull(sum(docamnt), 0) from sop10100 where bachnumb = @bachnumb

-- create the new batch in batch headers based on the
-- majority of fields in the old batch

INSERT INTO SY00500
( GLPOSTDT
, BCHSOURC
, BACHNUMB
, SERIES
, MKDTOPST
, NUMOFTRX
, RECPSTGS
, DELBACH
, MSCBDINC
, BACHFREQ
, RCLPSTDT
, NOFPSTGS
, BCHCOMNT
, BRKDNALL
, CHKSPRTD
, RVRSBACH
, USERID
, CHEKBKID
, BCHTOTAL
, BCHEMSG1
, BCHEMSG2
, BACHDATE
, BCHSTRG1
, BCHSTRG2
, POSTTOGL
, MODIFDT
, CREATDDT
, NOTEINDX
, CURNCYID
, BCHSTTUS
, CNTRLTRX
, CNTRLTOT
, PETRXCNT
, APPROVL
, APPRVLDT
, APRVLUSERID
, ORIGIN
, ERRSTATE
, GLBCHVAL
, Computer_Check_Doc_Date
, Sort_Checks_By
, SEPRMTNC
, REPRNTED
, CHKFRMTS
, TRXSORCE
, PmtMethod
, EFTFileFormat
, Workflow_Approval_Status
, Workflow_Priority
, TIME1)
SELECT
glpostdt
,bchsourc
,@bachnumb
,series
,mkdtopst
,@numtrx
,recpstgs
,delbach
,mscbdinc
,bachfreq
,rclpstdt
,0
,bchcomnt
,brkdnall
,chksprtd
,rvrsbach
,userid
,chekbkid
,@batchamount
,BCHEMSG1
,BCHEMSG2
,BACHDATE
,BCHSTRG1
,BCHSTRG2
,POSTTOGL
,MODIFDT
,CREATDDT
,@noteindex
,CURNCYID
,0
,0
,0.00000
,PETRXCNT
,APPROVL
,APPRVLDT
,APRVLUSERID
,ORIGIN
,0
,GLBCHVAL
,Computer_Check_Doc_Date
,Sort_Checks_By
,SEPRMTNC
,REPRNTED
,CHKFRMTS
,TRXSORCE
,PmtMethod
,EFTFileFormat
,Workflow_Approval_Status
,Workflow_Priority
,TIME1
FROM SY00500
WHERE bachnumb = 'SHIPTODAY'

source - http://dynamicsgpblogster.blogspot.com/2008/04/moving-sop-transactions-from-one-batch.html