| |
| |
|
softwarecatch.com
|
|
|
|
Great Plains Customization - Programming Auto-apply in Accounts Receivable
Microsoft Great Plains is one of three Microsoft Business Solutions mid-market ERP products: Great Plains, Solomon, Navision. Considering that Great Plains is now very good candidate for integration with POS application, such as Microsoft Retail Management System or RMS and Client Relation Systems, such as Microsoft CRM - there is common need in Great Plains customizations and integrations, especially on the level of MS SQL Server transact SQL queries and stored procedures. In this small article we'll show you how to create auto-apply utility, when you integrate huge number of sales transactions and payments. We will be working with RM20101 - Receivables Open File and RM20201 - Receivables Apply Open File. Let's see SQL code: declare @curpmtamt numeric(19,5) declare @curinvamt numeric(19,5) declare @curpmtnum varchar(20) declare @curinvnum varchar(20) declare @curinvtype int declare @curpmttype int declare @maxid int declare @counter int -- Create a temporary table create table #temp ( [ID] int identity(1,1) primary key, CUSTNMBR varchar(15), INVNUM varchar(20), INVTYPE int, PMTNUM varchar(20), PMTTYPE int, INVAMT numeric(19,5), PMTAMT numeric(19,5), AMTAPPLIED numeric(19,5) ) create index IDX_INVNUM on #temp (INVNUM) create index IDX_PMTNUM on #temp (PMTNUM) -- Insert unapplied invoices and payments insert into #temp ( CUSTNMBR, INVNUM, INVTYPE, PMTNUM, PMTTYPE, INVAMT, PMTAMT, AMTAPPLIED ) select CUSTNMBR = a.CUSTNMBR, INVNUM = b.DOCNUMBR, INVTYPE = b.RMDTYPAL, PMTNUM = a.DOCNUMBR, PMTTYPE = a.RMDTYPAL, INVAMT = b.CURTRXAM, PMTAMT = a.CURTRXAM, AMTAPPLIED = 0 from RM20101 a join RM20101 b on (a.CUSTNMBR = b.CUSTNMBR) join RM00101 c on (a.CUSTNMBR = c.CUSTNMBR) where a.RMDTYPAL in (7, 8, 9) and b.RMDTYPAL in (1, 3) and a.CURTRXAM 0 and b.CURTRXAM 0 order by a.custnmbr, b.DOCDATE, a.DOCDATE, a.DOCNUMBR, b.DOCNUMBR -- Iterate through each record select @maxid = max([ID]) from #temp select @counter = 1 while @counter = @curpmtamt) and (@curpmtamt>0) and (@curinvamt>0)-- if the invoice amount is greater or the same as the payment amount begin select @curinvamt = @curinvamt - @curpmtamt -- invoice amount remaining -- update with the amount that is applied to the current invoice from -- the current payment update #temp set AMTAPPLIED = @curpmtamt where [ID] = @counter -- update with amount of invoice remaining update #temp set INVAMT = @curinvamt where INVNUM = @curinvnum and INVTYPE = @curinvtype -- update with amount of payment remaining update #temp set PMTAMT = 0 where PMTNUM = @curpmtnum and PMTTYPE = @curpmttype end else if (@curinvamt 0) and (@curinvamt>0)-- if the invoice amount is lesser to the payment amount begin select @curpmtamt = @curpmtamt - @curinvamt -- payment amount remaining -- update with the amount that is applied to the current invoice from -- the current payment update #temp set AMTAPPLIED = @curinvamt where [ID] = @counter -- update with amount of invoice remaining update #temp set INVAMT = 0 where INVNUM = @curinvnum and INVTYPE = @curinvtype -- update with amount of payment remaining update #temp set PMTAMT = @curpmtamt where PMTNUM = @curpmtnum and PMTTYPE = @curpmttype end -- go to the next record select @counter = @counter + 1 end -- update the RM Open table with the correct amounts update RM20101 set CURTRXAM = b.INVAMT from RM20101 a join #temp b on (a.DOCNUMBR = b.INVNUM and a.RMDTYPAL = b.INVTYPE) update RM20101 set CURTRXAM = b.PMTAMT from RM20101 a join #temp b on (a.DOCNUMBR = b.PMTNUM and a.RMDTYPAL = b.PMTTYPE) -- create the RM Apply record or update if records already exist update RM20201 set DATE1 = convert(varchar(10), getdate(), 101), GLPOSTDT = convert(varchar(10), getdate(), 101), APPTOAMT = APPTOAMT + a.AMTAPPLIED, ORAPTOAM = ORAPTOAM + a.AMTAPPLIED, APFRMAPLYAMT = APFRMAPLYAMT + a.AMTAPPLIED, ActualApplyToAmount = APFRMAPLYAMT + a.AMTAPPLIED from #temp a join RM20101 b on (b.DOCNUMBR = a.INVNUM and b.RMDTYPAL = a.INVTYPE) join RM20101 c on (c.DOCNUMBR = a.PMTNUM and c.RMDTYPAL = a.PMTTYPE) join RM20201 d on (d.APFRDCTY = a.PMTTYPE and d.APFRDCNM = a.PMTNUM and d.APTODCTY = a.INVTYPE and d.APTODCNM = a.INVNUM) where a.AMTAPPLIED 0 insert into RM20201 (CUSTNMBR, DATE1, GLPOSTDT, POSTED, APTODCNM, APTODCTY, APTODCDT, ApplyToGLPostDate, CURNCYID, CURRNIDX, APPTOAMT, ORAPTOAM, APFRDCNM, APFRDCTY, APFRDCDT, ApplyFromGLPostDate, FROMCURR, APFRMAPLYAMT, ActualApplyToAmount) select CUSTNMBR = a.CUSTNMBR, DATE1 = convert(varchar(10), getdate(), 101), GLPOSTDT = convert(varchar(10), getdate(), 101), POSTED = 1, APTODCNM = a.INVNUM, APTODCTY = a.INVTYPE, APTODCDT = b.DOCDATE, ApplyToGLPostDate = b.GLPOSTDT, CURNCYID = b.CURNCYID, CURRNIDX = '', APPTOAMT = a.AMTAPPLIED, ORAPTOAM = a.AMTAPPLIED, APFRDCNM = a.PMTNUM, APFRDCTY = a.PMTTYPE, APFRDCDT = c.DOCDATE, ApplyFromGLPostDate = c.GLPOSTDT, FROMCURR = c.CURNCYID, APFRMAPLYAMT = a.AMTAPPLIED, ActualApplyToAmount = a.AMTAPPLIED from #temp a join RM20101 b on (b.DOCNUMBR = a.INVNUM and b.RMDTYPAL = a.INVTYPE) join RM20101 c on (c.DOCNUMBR = a.PMTNUM and c.RMDTYPAL = a.PMTTYPE) where a.AMTAPPLIED 0 and not exists (select 1 from RM20201 d where d.APFRDCTY = a.PMTTYPE and d.APFRDCNM = a.PMTNUM and d.APTODCTY = a.INVTYPE and d.APTODCNM = a.INVNUM) drop table #temp About The Author Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies - USA nationwide Great Plains, Microsoft CRM customization company, with offices in Chicago, San Francisco, Los Angeles, San Diego, Phoenix, Houston, Miami, Atlanta, New York, Madrid, Brazil, Moscow ( http://www.albaspectrum.com), you can reach Andrew 1-866-528-0577, he is Dexterity, SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer; akarasev@albaspectrum.com
MORE RESOURCES:
Google News |
|
|
|
RELATED ARTICLES
Why do Manufacturers Invest in Business Management Software? With many manufacturing shops heading over seas in favor of lower cost, it is tough to compete in today's marketplace. As a result, the goal for manufactures who want to compete going forward is to run leaner, faster and more accurately. C++ Tutorial 2, Input and Variables This is the tutorial where we really get into programming. Input and variables are the essence of programming. Crystal Reports for Microsoft Great Plains Microsoft Business Solutions - Great Plains is designed to meet and extend the needs of small and mid-size organizations for its business success. Its comprehensive accounting and business management applications also provide businesses with capabilities to customize various modules of the Great Plains software to fit to their specific needs. Examining the Substance of Studio MX To all web designers out there, this article is for you! I guess you already heard about Studio MX (I think so!) - the ideal bundle for professional web designers, bringing together Dreamweaver MX for page design, Flash MX for animation and interactivity, and Fireworks MX for editing and optimizing graphics. With all these components, it certainly provides professional functionality for every aspect of web development. Microsoft Great Plains eCommerce: overview for developer Microsoft Business Solutions Great Plains was designed back in the earlier 1990th as first graphical ERP/accounting system for mid-size businesses. The architects of Great Plains Dexterity - this is the internal mid-shell, all Great Plains was written on, designed it to be easily transferable between graphical operating systems (MAC, Windows, Solaris - potentially) and database platforms - initially Great Plains was available on Ctree (both Mac and PC) and Btrieve, a bit later high end version Dynamics C/S+ was available on Microsoft SQL Server 6. Microsoft CRM Implementation for Large Corporation - overview Microsoft Business Solutions CRM is now approaching the phase of being mature product and the CRM solution you may consider for large publicly traded company. Our view point considers the fact of multiple platforms and systems coexistence and balancing: UNIX, Linux, Microsoft Windows, Java, . Microsoft Great Plains Integration with Microsoft Access - Overview for Developer Microsoft Business Solutions stakes on Microsoft Great Plains as main Accounting/ERP application for US market. At the same time it seems to be staking on Navision in Europe and has Axapta as high end large corporation market competitor to Oracle, PeopleSoft, SAP, IBM. What is Groupware? Vince Lombardi once said that, "The achievements of an organization are the results of the combined effort of each individual." If this is the case, then what do we need to do in order to insure success in our companies? How can we combine the efforts of each individual as Mr. Microsoft Great Plains Customization Tools Evolution - Overview for Consultant When Great Plains Software introduced the first graphical accounting application for Mac and Windows in the beginning of 1990th it had Great Plains Dexterity customization tool, IDE and programming language. Dexterity design was based on several longevity principles: Graphical platform independence and database platform independence and easy switch from one DB to the other among the most important ones. Microsoft Great Plains: Large Scale Implementation Microsoft Business Solutions Great Plains, Navision, Axapta, Solomon and CRM are coming up to satisfy ERP needs for large corporation, including multinationals. In this article we will be describing Microsoft Great Plains as the MRP platform, fitting to multiple industries and business niches: aerospace, defense, textile, pharmaceutical, healthcare, constructions, mining, services, distributions & logistics, wholesale & retail, public sector, chemicals, oil & gas, finance, brokerage, etc. Great Plains Dynamics on Pervasive/Ctree support - overview for consultant All of us know that Microsoft bought former Great Plains Software and formed Microsoft (Great Plains) Business Solutions in the earlier 21st century. It was total success for GPS, we guess, but for Great Plains Dynamics / eEnterprise / Dynamics C/S+ clients it was the time of change. Helping Newbies Understand Professional Software The Windows registry is a huge database that ensures normal computer operation. Installing and uninstalling software can make your registry a mess, leading to decreased PC performance and causing computer crashes. Software Automation Helps Increase your Bottom Line When you own a small business, time is money. And every time a task that should be automated is handled manually, it wastes your time and your business loses money. OEComplete - A Personal Information Manager OEComplete is a utility for managing the personal information of the user. It is designed to be flexible and affordable even for the home user. Passwords Used In Microsoft Word Documents You would like to protect your documents, wouldn't you? Reasons may vary but the problem is the same - you need to protect your Microsoft Word documents from unauthorized editing or viewing. What document protection features does Microsoft® Office Word 2003, a part of Microsoft Office Professional Edition 2003 provide?Microsoft Word features for document protection. Dig Out That Worm Internet worms. Is your PC infected?If your computer has become infected with a worm, don't panic, it is not the end of the world. What You Must Know About Spyware Right Now Spyware is like the new technological nuclear weapon on the internet. Spyware can not only damage the health of your computer but it can also steal your personal identity other personal information. Editing Your Photos Using Microsoft Picture It Publishing Platinum 2002 - A Great Dinosaur I started using PIP (Picture It Publishing) Platinum 2002 right after I got it in a bundle with my HP Pavallion N5295 Notebook more than several moons ago!I don't want to be a rocket scientist to produce edited photos quickly and with quality results, again and again.I had a terrible time at first with the edited pictures. Groupware and Online Collaboration: Collaboration Series #4 This article is the fourth of a series of articles exploring specific aspects of groupware. The brief informational articles in this series discuss some of the technologies associated with groupware, as well as some of the characteristics of groupware. Scrap Booking Online: Word Perfect or Corel Graphics Suite? Scrapbooks are very popular these days. I think that almost everyone wanted to capture family histories and stories in any which way they can.
|
|
Powered By: Free Work At Home Business Opportunity! |
| © 2006 |
