| Microsoft Great Plains is one of three Microsoft | | | | [ID] = @counter |
| Business Solutions mid-market ERP products: Great | | | | -- update with amount of invoice remainingupdate |
| Plains, Solomon, Navision. Considering that Great Plains | | | | #tempset |
| is now very good candidate for integration with POS | | | | INVAMT = 0where |
| application, such as Microsoft Retail Management | | | | INVNUM = @curinvnum and |
| System or RMS and Client Relation Systems, such as | | | | INVTYPE = @curinvtype |
| Microsoft CRM - there is common need in Great | | | | -- update with amount of payment remainingupdate |
| Plains customizations and integrations, especially on the | | | | #tempset |
| level of MS SQL Server transact SQL queries and | | | | PMTAMT = @curpmtamtwhere |
| stored procedures. | | | | PMTNUM = @curpmtnum and |
| In this small article we'll show you how to create | | | | PMTTYPE = @curpmttypeend |
| auto-apply utility, when you integrate huge number of | | | | -- go to the next recordselect @counter = @counter |
| sales transactions and payments. We will be working | | | | + 1end |
| with RM20101 - Receivables Open File and RM20201 - | | | | -- update the RM Open table with the correct |
| Receivables Apply Open File. | | | | amountsupdate |
| Let's see SQL code:declare @curpmtamt | | | | RM20101set |
| numeric(19,5)declare @curinvamt numeric(19,5)declare | | | | CURTRXAM = b.INVAMTfrom |
| @curpmtnum varchar(20)declare @curinvnum | | | | RM20101 ajoin #temp b on (a.DOCNUMBR = |
| varchar(20)declare @curinvtype intdeclare | | | | b.INVNUM and a.RMDTYPAL = b.INVTYPE)update |
| @curpmttype intdeclare @maxid intdeclare @counter | | | | RM20101set |
| int | | | | CURTRXAM = b.PMTAMTfrom |
| -- Create a temporary tablecreate table #temp | | | | RM20101 ajoin #temp b on (a.DOCNUMBR = |
| ( | | | | b.PMTNUM and a.RMDTYPAL = b.PMTTYPE) |
| [ID] int identity(1,1) primary key, | | | | -- create the RM Apply record or update if records |
| CUSTNMBR varchar(15), | | | | already existupdate |
| INVNUM varchar(20), | | | | RM20201set |
| INVTYPE int, | | | | DATE1 = convert(varchar(10), getdate(), 101), |
| PMTNUM varchar(20), | | | | GLPOSTDT = convert(varchar(10), getdate(), 101), |
| PMTTYPE int, | | | | APPTOAMT = APPTOAMT + a.AMTAPPLIED, |
| INVAMT numeric(19,5), | | | | ORAPTOAM = ORAPTOAM + a.AMTAPPLIED, |
| PMTAMT numeric(19,5), | | | | APFRMAPLYAMT = APFRMAPLYAMT + |
| AMTAPPLIED numeric(19,5) | | | | a.AMTAPPLIED, |
| )create index IDX_INVNUM on #temp (INVNUM)create | | | | ActualApplyToAmount = APFRMAPLYAMT + |
| index IDX_PMTNUM on #temp (PMTNUM) | | | | a.AMTAPPLIEDfrom |
| -- Insert unapplied invoices and paymentsinsert into | | | | #temp ajoin RM20101 b on (b.DOCNUMBR = |
| #temp | | | | a.INVNUM and b.RMDTYPAL = a.INVTYPE)join |
| ( | | | | RM20101 c on (c.DOCNUMBR = a.PMTNUM and |
| CUSTNMBR, | | | | c.RMDTYPAL = a.PMTTYPE)join RM20201 d on |
| INVNUM, | | | | (d.APFRDCTY = a.PMTTYPE andd.APFRDCNM = |
| INVTYPE, | | | | a.PMTNUM andd.APTODCTY = a.INVTYPE |
| PMTNUM, | | | | andd.APTODCNM = a.INVNUM)wherea.AMTAPPLIED |
| PMTTYPE, | | | | 0insert into RM20201 |
| INVAMT, | | | | (CUSTNMBR, |
| PMTAMT, | | | | DATE1, |
| AMTAPPLIED | | | | GLPOSTDT, |
| )select | | | | POSTED, |
| CUSTNMBR = a.CUSTNMBR, | | | | APTODCNM, |
| INVNUM = b.DOCNUMBR, | | | | APTODCTY, |
| INVTYPE = b.RMDTYPAL, | | | | APTODCDT, |
| PMTNUM = a.DOCNUMBR, | | | | ApplyToGLPostDate, |
| PMTTYPE = a.RMDTYPAL, | | | | CURNCYID, |
| INVAMT = b.CURTRXAM, | | | | CURRNIDX, |
| PMTAMT = a.CURTRXAM, | | | | APPTOAMT, |
| AMTAPPLIED = 0from RM20101 ajoin RM20101 b on | | | | ORAPTOAM, |
| (a.CUSTNMBR = b.CUSTNMBR)join RM00101 c on | | | | APFRDCNM, |
| (a.CUSTNMBR = c.CUSTNMBR)wherea.RMDTYPAL | | | | APFRDCTY, |
| in (7, 8, 9) andb.RMDTYPAL in (1, 3) anda.CURTRXAM | | | | APFRDCDT, |
| 0 andb.CURTRXAM 0order | | | | ApplyFromGLPostDate, |
| b.DOCNUMBR | | | | FROMCURR, |
| -- Iterate through each recordselect @maxid = | | | | APFRMAPLYAMT, |
| max([ID])from #tempselect @counter = 1while | | | | ActualApplyToAmount)select |
| @counter = @curpmtamt) and (@curpmtamt>0) | | | | CUSTNMBR = a.CUSTNMBR, |
| and (@curinvamt>0)-- if the invoice amount is | | | | DATE1 = convert(varchar(10), getdate(), 101), |
| greater or the same as the payment | | | | GLPOSTDT = convert(varchar(10), getdate(), 101), |
| amountbeginselect @curinvamt = @curinvamt - | | | | POSTED = 1, |
| @curpmtamt -- invoice amount remaining | | | | APTODCNM = a.INVNUM, |
| -- update with the amount that is applied to the current | | | | APTODCTY = a.INVTYPE, |
| invoice from | | | | APTODCDT = b.DOCDATE, |
| -- the current paymentupdate #tempset | | | | ApplyToGLPostDate = b.GLPOSTDT, |
| AMTAPPLIED = @curpmtamtwhere | | | | CURNCYID = b.CURNCYID, |
| [ID] = @counter | | | | CURRNIDX = '', |
| -- update with amount of invoice remainingupdate | | | | APPTOAMT = a.AMTAPPLIED, |
| #tempset | | | | ORAPTOAM = a.AMTAPPLIED, |
| INVAMT = @curinvamtwhere | | | | APFRDCNM = a.PMTNUM, |
| INVNUM = @curinvnum and | | | | APFRDCTY = a.PMTTYPE, |
| INVTYPE = @curinvtype | | | | APFRDCDT = c.DOCDATE, |
| -- update with amount of payment remainingupdate | | | | ApplyFromGLPostDate = c.GLPOSTDT, |
| #tempset | | | | FROMCURR = c.CURNCYID, |
| PMTAMT = 0where | | | | APFRMAPLYAMT = a.AMTAPPLIED, |
| PMTNUM = @curpmtnum and | | | | ActualApplyToAmount = a.AMTAPPLIEDfrom |
| PMTTYPE = @curpmttypeendelse if (@curinvamt 0) | | | | #temp ajoin RM20101 b on (b.DOCNUMBR = |
| and (@curinvamt>0)-- if the invoice amount is lesser | | | | a.INVNUM and b.RMDTYPAL = a.INVTYPE)join |
| to the payment amountbeginselect @curpmtamt = | | | | RM20101 c on (c.DOCNUMBR = a.PMTNUM and |
| @curpmtamt - @curinvamt -- payment amount | | | | c.RMDTYPAL = a.PMTTYPE)wherea.AMTAPPLIED 0 |
| remaining | | | | andnot exists (select 1from RM20201 dwhere |
| -- update with the amount that is applied to the current | | | | d.APFRDCTY = a.PMTTYPE andd.APFRDCNM = |
| invoice from | | | | a.PMTNUM andd.APTODCTY = a.INVTYPE |
| -- the current paymentupdate #tempset | | | | andd.APTODCNM = a. |
| AMTAPPLIED = @curinvamtwhere | | | | |