Home » Listing Details
Top Websites
  1. Dynamics GP Help
    Over 5500 resources listed.
  2. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  3. Rose Business Solutions Blog New
    Over 2200 resources listed.
  4. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 1100 resources listed.
  5. Mariano Gomez at The Dynamics GP Blogster
    Over 900 resources listed.
  6. Microsoft Dynamics Partner Community Blog
    Over 900 resources listed.
  7. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 700 resources listed.
  8. Mohammad Daoud's Dynamics GP Blog
    Over 600 resources listed.
  9. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 500 resources listed.
  10. Inside Microsoft Dynamics GP Official Blog
    Over 500 resources listed.
  11. eOne Business Solutions Blog
    Over 400 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  14. Dynamics CPM
    Over 300 resources listed.
  15. BKD Dynamics GP Insights Blog
    Over 200 resources listed.
  16. Leslie Vail at Dynamics Confessor Blogspot
    Over 200 resources listed.
  17. Victoria Yudin's Dynamics GP Website
    Over 200 resources listed.
    Victoria Yudin
  18. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  19. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  20. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.

ID:11378
Title:Moving SOP Transactions from One Batch to Another
URL:http://dynamicsgpblogster.blogspot.com/2008/04/moving-sop-transactions-from-one-batch.html
Description: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 'Ship Today' that need to be invoiced, this is, transferred.


declare @bachnumb char(15), @strpostdate char(20)
declare @postdate datetime
declare @numtrx int, @batchamount numeric(19,5)
declare @noteindex numeric(19,5)

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 = 'Ship Today' 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 = 'Ship Today'
update SY00500 set NUMOFTRX = @numtrx, BCHTOTAL = @batchamount where bachnumb = 'Ship Today'

-- get next note index to assign to the new batch

select @noteindex = noteindx from dynamics..sy01500 where interid = db_name()
update dynamics..sy01500 set noteindx = noteindx + 1 where interid = db_name()

-- 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 = 'Ship Today'

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, MIS, MCP, PMP
Maximum Global Business, LLC.
http://www.maximumglobalbusiness.com/
Category:TIPS AND TRICKS BY MODULES: SOP
Link Owner:
Date Added:June 17, 2010 06:14:06 PM
Number Hits:36
RatingsAverage rating: (0 votes)
Reviews

No Reviews Yet.

 
GPWindow.com

Thank you for your support for GPWindow. It helps us cover part of the hosting costs for GPWindow.