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
|
|
|
Erp Implementation: Success Factors
As seeing large number of implementations ? in our case these are Microsoft Business Solutions Products: Great Plains, MS CRM, Navision ? we would like to give you our opinion on what should you consider to do to secure implementation success. These principles should work as for large corporation as well as for midsize and even small business. We will not be talking about old-wisdom, which you know from the college classes or business school about management and staff involvement into th...(related: Software)
Outlook... Not Just For Email! Using Your Outlook Calendar
Microsoft Outlook is one of the most widely used software programs for email, but if you're only using it for email, you're missing out on a great productivity tool! This month's column looks at a few wa...(related: Software)
Imagine If Everyone Working In Your Office Was In Synch?
Microsoft-Outlook is a pretty amazing program. So much more than simply an e-mail client, it provides a task list, a powerful calendar with recurring scheduling capabilities, wonderful electronic sticky notes, mail-merge capability with MS-Word support and so much more.The problem is, it is a little stingy with its data and doesn't like to share it with any of your employees unless you're willing to invest in the expense and headaches involved with running a Microsoft Exchange Server.Wouldn't it be nice if you could use MS-Outlook to coordinate meeting across multiple peoples' calendars, share tasks and delegate project responsibilities right from Outlook even if you were working from the road? How about managing discussion groups, sharing documents, synchronizing updates and ...(related: Software)
How To Evaluate Staffing Software
If you are in the market for new staffing software, I suspect that one of the most daunting of tasks will be to sift through the many vendors that are now servicing the staffing industry.You will find many very qualified companies with good staffing software products. These products will utilize different technologies and different approaches to how they implement and train your company. The choices will be many, the approaches varied, and the results in some cases may ...(related: Software)
Microsoft Great Plains Subcontracting ? Overview For Microsoft Business Solutions Partner
Microsoft Business Solutions Great Plains is very popular ERP/MRP applications in the USA, Canada, UK, Australia, New Zealand, Middle East, South Africa, Latin America. Considering high number of new Microsoft Great Plains partners and the desire to subcontract occasional customization, integration and reporting projects, we would like to advise you on Microsoft Great Plains subcontracting guidelines, based on our experience in dealing with Great Plains VARsCandidates for subcontracting:? Great Plains Dexterity customizations ? Dexterity was and still is the IDE and programming language of Great Plains, especially workstation thick interface. However since Microsoft bought Great ...(related: Software)
Reporting Tools For Microsoft Great Plains ? Overview For Developers
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. Small companies use Small Business Manager (which is based on the same technology ? Great Plains Dexterity dictionary and runtime), Great Plains Standard on MSDE is for small to midsize clients, and then Great Plains serves the rest of the market up to big corporations. There are several reporting tools available and you definitely need to know which one to use for different types of reports.If you are developer who is asked: how do we create report for Microsoft Grea...(related: Software)
Protect Your Most Vital Business Asset With Security Software
Homeland security, airport security, Internet security â?" these days weâ??re literally inundated with a need to protect ourselves and our property from the non-stop bombardment of potentially damaging attacks by unknown sources...(related: Software)
How To Make Attractive And Effective Powerpoint Presentations
Microsoft PowerPoint has dramatically changed the way in which academic and business presentations are made. This article outlines few tips on making more effective and attractive PowerPoint presentations.THE TEXT
- Keep the wording clear and simple. Use active, visual language. Cut unnecessary words ? a good rule of thumb is to cut paragraphs down to sentences, sentences into phrases, and phrases into key words.
- Limit the number of words and lines per slide. Try the Rule of Five - five words per line, five lines per slide. If too much text appears on one slide, us...(related: Software)
Computer Phones ? Facts And Fallacies
The stakes are high when considering security, privacy, and savings, and the old adage, "look before you leap" might be a more judicial approach when searching for a computer phone provider, aka VoIP (voice over internet protocol).FACTS? PC phones (VoIP) can save individuals and businesses up to 80% on current phone bills, regardless of whether calls are made from PC to landlines or mobile phones.? Secure lines that include patented technology are protected against terminal viruses, worms, Trojan horses, unscrupulous hackers, and uninvited guests listening in on private conversations.? Unsecure lines cripple users by creating dangerous vulnerability to security and privacy due to operating on open platforms or shared services.? Over 90% of all VoIP provider services a...(related: Software)
site-map - Copyright © 2008 | Contact Webmaster | All Rights Reserved. | Software