Moving SOP Transactions from One Batch to Another

It is no secret that SOP allows users to transfer transactions from Orders to Invoices (or any other valid transfer type). In the process, the exceptions, that is, transaction records that did not get transferred from one type to another, are comingled with the transactions that did get transferred. In this case, the SOP batch will contain, say for example, orders and invoices making it difficult for the user to distinguish exceptions from final transactions. Yes, batch edit lists do exist, but are not quite an effective tool in sorting out the good, from the the bad, from the ugly.

The following SQL script allows you to split the transferred records from the exceptions in two batches. The original batch name will be used for the transactions that got successfully transferred and an exception batch will be created for those that were left behind during the transfer.

Lets assume that batch contains orders 'SHIPTODAY' that need to be invoiced, this is, transferred.

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
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'

This batch of T-SQL statements can be automated in a SQL job to execute daily at certain time after the orders have been transferred by the AR department. This will automate the process and since the exception batch is created based on the current date, it is easy to distinguish one from the other on a daily basis, even if previous exceptions have not been processed. In addition, the 'Ship Today' batch totals (number of transactions and batch total) will be updated automatically as well, even if previous invoices remain unprocessed in the 'Ship Today' batch.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/