Home
>
Software
>
Great Plains Customization ? Programming Auto-apply in Accounts Receivable

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,< /p>

APTODCDT,

ApplyToGLPostDate,

CURNCYID,

CURRNIDX,

APPTOAMT,

ORAPT OAM,

APFRDCNM,

APFRDCTY,

APFRDCDT,

ApplyFromGLPostDate,

FROMCURR,

< p>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; http://www.albaspectrum.com

Wood Dale Chicago prom limo .. Lockport Chicago limo O’Hare

In The News:

Fueled by artificial intelligence, CyberDog the robotic dog is designed specifically locate fire ant nests, which are endangering ecosystems.

Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents

Internet browsers are useful, but they can be dangerous. Clicking on malicious links can direct you to dangerous websites that steal information or infect your device.

Apple's latest iOS update introduces advanced artificial intelligence capabilities, which may be capturing and analyzing sensitive information.

Breach site confirmed 56,904,909 Hot Topic users' data leaked online. Tech expert Kurt “CyberGuy" Knutsson says the company's silence makes matters even worse.

Tech expert Kurt “CyberGuy" Knutsson discusses how an innovative Easy-Way kit turns standard strollers electric, simplifying navigation for parents.

Tech expert Kurt “CyberGuy" Knutsson talks about T-Mobile being hacked in broad cyberattack on global phone and internet companies.

Tech expert Kurt “CyberGuy" Knutsson shows you how to add, customize and manage widgets for quick access to apps and info from your iPhone home screen.

Wrong Google searches can not only compromise your device and personal data. They can also bring law enforcement to your doorstep. Hackers are targeting Google searches.

China's Shanghai Kepler Robotics has developed a new humanoid robot that can carry up to 35 pounds per hand for commercial applications across various industries

Checking your hearing has gotten easier with the new hearing test feature in AirPods Pro 2. You can take reliable hearing tests using your iPhone or iPad.

Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.

Kurt "CyberGuy" Knutsson explains how a VPN — virtual private network — works and how running it can slow down the operation of your device.

The WalkON Suit F1, an exoskeleton developed to help people with disabilities, can actually walk over to a person in a wheelchair, solving a major problem.

ClickFix, a new scam targeting computer users, is on the rise in the U.S. The scam prompts its targets to click on a link to fix a problem.

Skydweller is the world's largest unmanned solar-powered aircraft that can stay airborne for weeks, and even months, without refueling.

The U.S. government said it is investigating after People's Republic of China hackers targeted commercial telecommunications service providers in the U.S.

Stay up to date on the latest AI technology advancements and learn about the challenges and opportunities AI presents now and for the future.

Scammers are using increasingly sophisticated ways to lure unsuspecting victims, and some are impersonating an email help desk or support team.

You can unsend or edit your iMessages with the latest iOS updates. Kurt the CyberGuy explains how you can save yourself some potential embarrassment.

Kurt “CyberGuy" Knutsson says there’s been a rise in cybercriminal services using hacked police and government emails to send subpoenas and data requests to U.S. companies.

Kurt “CyberGuy" Knutsson discusses how Axiom Space and luxury designer Prada are collaborating on NASA's Artemis III spacesuit design.

Voice assistants may cause confusion across devices. Tech expert Kurt “CyberGuy" Knutsson offers some solutions to fix it.

Shameless scammers trick veterans into giving personal info or cash. Tech expert Kurt “CyberGuy" Knutsson explores five common scams.

Great Plains Customization ? Programming Auto-apply in Accounts Receivable

Software