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: V-22 Evolving into 'Software-Defined' Platform National Defense Magazine Guesty, Short-Term Rental Software Provider, Acquires Rentals United Skift Travel News Rocket Software Buys OpenText's App Modernization & Connectivity Business for $2.3B; Milan Shetti Quoted GovCon Wire CISA urges software devs to weed out path traversal vulnerabilities BleepingComputer Durst Announces 1,000th Installation Of Durst Workflow Software | Label and Narrow Web Label & Narrow Web Tesla Cybertruck gains Off-Road modes, locker-enabling software Motor Authority Autire's Revolutionary EBP Audit Software Demonstrates Significant Time Savings for CPA Firms PR Newswire GlobalLogic Recognized as Leader in Everest Group's Software Product Engineering Services Business Wire Exclusive: IBM brings its AI, data and automation software to AWS Marketplace in 92 countries VentureBeat Verizon 2024 DBIR: Software supply chain risks fuel a data breach epidemic Security Boulevard Epicor Acquires Smart Software for AI-Powered Inventory Planning - Supply and Demand Chain Executive Epicor Acquires Smart Software for AI-Powered Inventory Planning Supply and Demand Chain Executive Paycom Software (PAYC) Q1 2024 Earnings Call Transcript The Motley Fool Best Fax Server Software 2024 | IBTimes International Business Times The power of the power pose: How a software CEO became a better leader Business Observer Auto Dealership CRM Software: Global Market Analysis and Forecast (2018-2033) by Type, Functionality, Application ... GlobeNewswire Using my speech issues to improve voice recognition software ALS News Today Best video conferencing software of 2024 TechRadar Update from eSync Alliance accelerates software-defined vehicle roadmap ComputerWeekly.com DeepSea Developments Modularizes the RTL-SDR for Multi-Receiver Software-Defined Radio Projects Hackster.io Investors in Take-Two Interactive Software (NASDAQ:TTWO) have seen decent returns of 40% over the past five years Yahoo Finance How patient engagement software can enhance oncology care Health Data Management CyberArk Shares Slide Despite Positive Outlook, Earnings Report - CyberArk Software (NASDAQ:CYBR) Benzinga Ford’s BlueCruise software probed after fatal crashes just-auto.com Software Developer SolarWinds Stock Shines After Q1 Earnings - Here's Why - SolarWinds (NYSE:SWI) Benzinga Supply Chain Management Software: Build the foundation, deliver the value Logistics Management Wyoming Senator Slams DOJ's Take on Non-Custodial Crypto Software, Vows to Protect User Rights – Bitcoin News Bitcoin.com News NVIDIA Buys Software Provider Run:AI Silicon Valley Daily IBM Expands Software Availability to 92 Countries in AWS Marketplace The Globe and Mail Paycom Software Inc (PAYC) Q1 2024 Earnings Call Transcript Highlights: Robust Growth and Strategic Expansions GuruFocus.com Top ideas in vertical software with valuations over or below 50th percentile - Baird - Seeking Alpha 2024.14.3 Official Tesla Release Notes - Software Updates Not a Tesla App Epicor acquires Smart Software Edge Middle East USENIX Security ’23 – “My Privacy for their Security”: Employees’ Privacy Perspec... Security Boulevard Boeing Advances US Navy MUM-T Aerial Refueling Software With Digital F/A-18 Super Hornet & MQ-25 Stingray Trial Simple Flying Carnival Corporation completes fleetwide software rollout Port Technology International 30 Largest Software Companies in the World by Market Cap Yahoo Finance Software support chap survived breaking his customer The Register Epicor swoops in for Smart Software - Enterprise Times Restaurant software leader Schedulefly simplifies staff scheduling with Median.co's mobile app services InvestorsObserver Severe Flaws Disclosed in Brocade SANnav SAN Management Software The Hacker News Lucid Software Announces Whiteboard Integrations with Google Meet Touchscreen Devices InvestorsObserver |
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. |
home | site map |
© 2006 |