Thursday, April 3, 2008

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/

4 comments:

Joel Rodencal\Quadis Technologies said...

Great article, was trying out the code because we might use it as a partner.

I would like to inform you though that I believe that you left a "from" clause off at the end, the second to last line right before the "where" line I believe you need a
"From sy00500" line, that is the only way I got it to work.

Mariano Gomez said...

Joel,
Effectively, the query is missing the "FROM SY00500" statement. Something must have happened in the process of copying/pasting from SQL Server Management Studio and editing for readability.

Feel free to use the code and let me know how it works for you.

MG.-
Mariano Gomez
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Vijay m said...

Make it simple!
Declare @notxn as numeric(18,0), @txntot as decimal(18,5)
set @notxn =(select COUNT(ORDOCAMT) from PSTGL..SOP10100 WHERE BACHNUMB='SOPBATCH' and SOPTYPE =2)
set @txntot =(select SUM(ORDOCAMT) from PSTGL..SOP10100 WHERE BACHNUMB='SOPBATCH' and SOPTYPE =2)
if @notxn >0 and @txntot >0
BEGIN
Update PSTGL..SOP10100 SET BACHNUMB ='ECONNECT DAILY' where SOPTYPE =2 and BACHNUMB='SOPBATCH'
Update PSTGL..SY00500 SET NUMOFTRX =NUMOFTRX+isnull(@notxn,0), BCHTOTAL =BCHTOTAL+isnull(@txntot,0) where BACHNUMB='ECONNECT DAILY'
Update PSTGL..SY00500 SET NUMOFTRX =NUMOFTRX-isnull(@notxn,0), BCHTOTAL =BCHTOTAL-isnull(@txntot,0) where BACHNUMB='SOPBATCH'
END

Anonymous said...

Great script, simple but powerful, thanks for sharing it.
Jorge Mejia