| |
| |
|
Software Information |
|
|
|
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:
Software - Google News |
|
|
|
RELATED ARTICLES
Microsoft CRM Lotus Notes Domino Connector FAQ Microsoft Business Solutions CRM and IBM Lotus Notes Domino, being two groupware products from competing software development leaders, however could coexist within one organization computer network and even work together in collaboration. There maybe multiple reason why corporation would use both products: licensing, commitment to IBM Lotus Notes as legacy product, risk balancing - staking on both Microsoft and Java/EJB/J2EE platforms, deploying Lotus advanced workflow to automate document management, etc. Great Plains Dexterity: Customizations & Source Code Programming Great Plains Software Dynamics, Dynamics C/S+, eEnterprise were written on GPS proprietary programming language and development environment - Great Plains Dexterity. When Microsoft bought Great Plains Software, Dynamics was renamed into Microsoft Great Plains, but Dexterity is still the architectural base, there were trends to move MS Great Plains to . Accounts Payable: A Powerful Document Management and Workflow Solution Accounts payable is just one area of office management where problems arise because of the sheer complexity of transactions, and the vast amount of paperwork that is generated.A disproportionate amount of time and administrative resources is consumed just getting invoices approved for payment. New SQL Delta Version 3.1 COMMAND LINE FUNCTIONA powerful command line script processor has been introduced to SQL Delta 3.1. Internet Faxing Service Review The Internet is reshaping every form of communications medium, and faxing is no exception. The latest twist: Internet faxing services that let you send messages to any fax machine from any Web browser or email, and others that give you a "personal fax phone number," then forward any documents sent there to your e-mail inbox. Tripwire for Linux File Integrity What is Tripwire?Tripwire is a form intrusion detection system (IDS) that helps you keep tabs on the integrity of the files on your computer. Quite simply it will help identify files or modifications made to your system in the event someone compromised your system. RSS: Get Notified When Your Favorite Websites Are Updated RSS (Really Simple Syndication) is a way for a site to publish it's updates. RSS readers can read these notifications and display a listing of the updates to you, the user. The Dreaded Paper Label - Should it be Used? While paper labeling CDs and DVDs may appear to be a cost effective solution for printing on your media, there are solid reasons why you should consider other options.1) Hand application of paper labels can be time consuming and frustrating. 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. Assertion in Java Assertion facility is added in J2SE 1.4. Microsoft Great Plains Data Conversion - Overview For Developer Looks like Microsoft Great Plains becomes more and more popular, partly because of Microsoft muscles behind it. Now it is targeted to the whole spectrum of horizontal and vertical market clientele. Microsoft Great Plains RW: Report Writer Microsoft Business Solutions Great Plains is marketed for mid-size companies as well as Navision (which has very good positions in Europe and emerging markets where it can be easily localized). Great Plains Report Writer (RW) module allows for modification and creation of Great Plains reports to suit your business needs. Reduce TCO: The Java Database Way TCO (Total Cost Ownership) is the buzzword in today's business world. This metric helps enterprise managers assess direct and indirect costs and benefits derived from their investment on IT components and services. 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. Reporting for Microsoft Great Plains/Dynamics/eEnterprise: RW - ReportWriter - Tips for Developer Microsoft Business Solutions Great Plains is written in Great Plains Software programming tool: Great Plains Dexterity. Dexterity in turn was built with conception of graphical cross-platform transferability (in time - 1992 - mostly Mac and MS Windows). Lotus Domino: Application Integration - A Programmer View There are two approaches for application integration:? Programmer's approach - for applications integration it is necessary to create some program codes;? Integrator's approach - in this case special software products are to be used and the integration process set up by those software settings. The following tools may be used for the first approach:? ODBC. My Experience - Making a Vision into Reality Disclaimer: All the thoughts expressed are my views only! Your perception might differ.. Healthcare Preventive Maintenance Software Healthcare facilities such as clinics, hospitals, and biomedical laboratories can benefit greatly if up to date CMMS software is used. Healthcare CMMS programs will help with the maintenance of the building, alerting workers when items such as automatic doors, light fixtures, and plumbing structures are due for checkups. Software Companies: Generate New Revenue Streams and Decrease Costs with Custom e-Learning Content It's no secret that software companies operate in a very competitive space where rivalry is increasingly fierce and where profit margins can be razor thin. New, smaller software companies are sprouting up each month and the leading software companies continually make strong advancements forward leveraging massive cash flow reserves. Microsoft CRM Implementation - Fundamental CRM Principles Revision Microsoft CRM is relatively new player on the now becoming traditional CRM software applications market. We would like just to mention shift in the technical conception - Microsoft CRM is committed to Windows and Microsoft components: Exchange, Active Directory, SQL Server, etc (while traditional CRM applications, such as Siebel were biased toward multiplatform compatibility: Unix, Oracle, Windows, SQL Server) and move up to business logic paradigm shift.
|
| home | site map |
| © 2006 |
