Friday, December 3, 2010

Mass “Auto Apply” for Payables Management in Microsoft Dynamics GP

Have you ever wanted to apply your vendors transactions automatically without having to move between all your vendors? I been in a situation with a new client that works on Dynamics GP since 2001 and they never applied their vendors payments to their invoices.

Working on this manually is a real nightmare and the user will keep working on this for ever before being able to finalize, therefore I have wrote script below that generates Macro to set this up, follow steps below to get the macro:

1. Open SQL Server and create new query.

2. Select your company database.

3. Paste code below ** “at the article footer under the signature” and run.

4. Script will generate a macro:

image

5. Copy macro text and paste it into a new notepad document.

6. Save it under “.Mac” extension.

7. Go to “Transactions >> Purchasing >> Apply Payables Documents”.

8. Go to “Tools >> Macro >> Play” and select the file you just saved:

image

Enjoy watching your transactions during the apply process.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCT, MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

 

** SQL Code:

/** SQL Code:            **/
/** By Mohammad R. Daoud **/
/** Dynamics GP MVP      **/
/** www.mohdaoud.com     **/
/** +962 79 999 65 85    **/
/** 02-12-2010           **/
DECLARE @VENDORID		VARCHAR(500)
DECLARE @DOCTYPE		VARCHAR(500)
DECLARE @DOCTYPEDESC	VARCHAR(500)
DECLARE @DOCNUMBR		VARCHAR(500)
DECLARE PMAPPLY CURSOR FOR 
SELECT VENDORID, CASE WHEN DOCTYPE = 4 THEN 1 WHEN DOCTYPE = 5 THEN 2 WHEN DOCTYPE = 6 THEN 3 END AS DOCTYPE, CASE WHEN DOCTYPE = 4 THEN 'RETURN' WHEN DOCTYPE = 5 THEN 'CREDIT MEMO' WHEN DOCTYPE = 6 THEN 'PAYMENT' END AS DOCTYPEDESC, DOCNUMBR FROM PM20000 WHERE DOCTYPE IN (4,5,6) AND YEAR(DOCDATE) BETWEEN 2001 AND 2017 ORDER BY DOCTYPE, DOCDATE ASC
OPEN PMAPPLY
FETCH NEXT FROM PMAPPLY INTO @VENDORID, @DOCTYPE, @DOCTYPEDESC, @DOCNUMBR
WHILE @@FETCH_STATUS = 0
BEGIN 
Print '##################################'
Print 'CheckActiveWin dictionary ''default''  form ''PM_Apply_To_Maintenance'' window ''PM_Apply_Document'''
Print 'NewActiveWin dictionary ''default''  form ''PM_Apply_To_Maintenance'' window ''PM_Apply_Document'''
Print 'MoveTo field ''Vendor ID'''
Print 'TypeTo field ''Vendor ID'' , ''' + LTRIM(RTRIM(@VENDORID)) + ''''
Print 'MoveTo field ''Document Number'''
Print 'MoveTo field ''Document Type'' item ''' + @DOCTYPE + ''' # ''' + @DOCTYPEDESC + ''''
Print 'ClickHit field ''Document Type'' item ' + @DOCTYPE +  ' # ''' + @DOCTYPEDESC + ''''
Print 'MoveTo field ''Document Number'''
Print 'TypeTo field ''Document Number'' , ''' + LTRIM(RTRIM(@DOCNUMBR)) + ''''
Print 'MoveTo field ''Auto Apply Button'''
Print 'ClickHit field ''Auto Apply Button'''
FETCH NEXT FROM PMAPPLY INTO @VENDORID, @DOCTYPE, @DOCTYPEDESC, @DOCNUMBR
END
CLOSE PMAPPLY
DEALLOCATE PMAPPLY

10 comments:

TLCinNM said...

Is there a version of this that will work on the Manual Payment window? We want the manual payment to automatically apply to an open invocie for the customer.

Anonymous said...

Thanks for such a wonderful script.

However I am not trying to autoapply. I want to select an invoice and then click OK.

Is there a way to select a checkbox based on a particular value ?

It would be very helpful.

Thanks

GPYoungGuns said...

Thank you for this script!!! We just finished migrating a client that wanted all of the A/P history to come over. However, their legacy system couldn't give us the invoices each payment was applied to. We imported all of their closed Invoices and Payments and then ran this script to Auto-Apply everything. After we verified the A/P balance was zero, we integrated in the open A/P. They had over 25,000 payments that this script auto-applied for us and it worked PERFECTLY!!

Mohammad R. Daoud said...

Glade to hear that this helped!

Anonymous said...

Mohammad - you're a life saver!

Would you have a similar script for Receivables? (before I start trying to write one!!!).

Ian Stewart MVP

Anonymous said...

If anyone else is looking for the code for RM mass apply, I created this:

DECLARE @CUSTNMBR VARCHAR(500)

DECLARE @DOCTYPE VARCHAR(500)

DECLARE @DOCTYPEDESC VARCHAR(500)

DECLARE @DOCNUMBR VARCHAR(500)

DECLARE RMAPPLY CURSOR FOR

SELECT CUSTNMBR, CASE WHEN RMDTYPAL = 7 THEN 1 WHEN RMDTYPAL = 8 THEN 2 WHEN RMDTYPAL = 9 THEN 3 END AS DOCTYPE, CASE WHEN RMDTYPAL = 7 THEN 'CREDIT MEMO' WHEN RMDTYPAL = 8 THEN 'RETURN'
WHEN RMDTYPAL = 9 THEN 'PAYMENT' END AS DOCTYPEDESC, DOCNUMBR FROM RM20101 WHERE CURTRXAM <> '0.00000' AND RMDTYPAL IN (7,8,9) AND YEAR(DOCDATE) BETWEEN 2001 AND 2017 ORDER BY DOCTYPE, DOCDATE ASC

OPEN RMAPPLY

FETCH NEXT FROM RMAPPLY INTO @CUSTNMBR, @DOCTYPE, @DOCTYPEDESC, @DOCNUMBR

WHILE @@FETCH_STATUS = 0

BEGIN

Print '##################################'

Print 'CheckActiveWin dictionary ''default'' form ''RM_Cash_Apply'' window ''RM_Apply_Document'''

Print 'NewActiveWin dictionary ''default'' form ''RM_Cash_Apply'' window ''RM_Apply_Document'''

Print 'MoveTo field ''Customer Number'''

Print 'TypeTo field ''Customer Number'' , ''' + LTRIM(RTRIM(@CUSTNMBR)) + ''''

Print 'MoveTo field ''Document Number'''

Print 'MoveTo field ''Document Type'' item ''' + @DOCTYPE + ''' # ''' + @DOCTYPEDESC + ''''

Print 'ClickHit field ''Document Type'' item ' + @DOCTYPE + ' # ''' + @DOCTYPEDESC + ''''

Print 'MoveTo field ''Document Number'''

Print 'TypeTo field ''Document Number'' , ''' + LTRIM(RTRIM(@DOCNUMBR)) + ''''

Print 'MoveTo field ''Auto Apply Button'''

Print 'ClickHit field ''Auto Apply Button'''

FETCH NEXT FROM RMAPPLY INTO @CUSTNMBR, @DOCTYPE, @DOCTYPEDESC, @DOCNUMBR

END

CLOSE RMAPPLY

DEALLOCATE RMAPPLY

Anonymous said...

For the autoapply script to RM, is there a way to restrict the process to a given date range? Or document range? Or make the script refer to an excel spreadsheet for the documents to be used?

Thanks

21st Century Software Solutions said...

micro dynamics online training| micro dynamics training| call ...
www.21cssindia.com/courses/micro-dynamics-online-training-111.html
Micro Dynamics Online Training, Micro Dynamics training, Micro Dynamics course contents, Micro Dynamics , call us: +919000444287 ... or contact@21cssindia.com

MarkW101 said...

I just ran the AP version - it worked great with GP 2015! Thanks for your help.

Anonymous said...

First of all thank you so much the script is working just as intended. I have one question though. It's fairly slow for us is there anyway to speed it up? We have a few hundred thousand credits to apply. I left the script running all weekend and it still wasn't done. Any thoughts?

Related Posts:

Related Posts with Thumbnails