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:

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
Advertisements

About Mohammad R. Daoud
Mohammad R. Daoud has been working as a Microsoft Dynamics GP consultant since 2004. His career path started with version 7.5; he studied every single tip of the application technicalities and did allot of successful implementations that includes functional consultations, analysis, and custom development projects. He holds a graduate degree in Computer Science and currently he is pursuing MBA degree in Accounting. In January 2007 he was nominated to the Microsoft Most Valuable Professional (MVP) certificate and was certified in April 2008 due to his online contributions in the Dynamics Community (Dynamics GP Newsgroups, Forums, User Groups and his blog: http://mohdaoud.blogspot.com). Worth to mention that he was also listed as one of the Microsoft Dynamics Top 100 Most Influential People in 2009 by DynamicsWorld. And more... • Demonstrated Passion for MS Products through participating in beta testing for some product versions, an effective member of Microsoft Connect. • Talented in securing strong high quality relationships with premier customers, partners and sales teams passionately and profitably. • Dedicated to meeting the expectations and requirements of internal and/or external customers • Problem Solver – Uses systematic approach to uncover true problem(s) and practical solution and can marshal resources to solve problem! • Proven record of effective account management, including Account Planning, Opportunity Management, and Business Management Excellence and working as part of a larger virtual team • Successful in delivering high quality technical engagements in the enterprise Dynamics GP implementations area according to customer requirements. • Dedicated, Committed, Self Motivated, Creative and highly focused with excellent communication, Negotiation, presentation, Active Listening and Objection Handling skills. • Experienced in Profitable researches and studies including white papers and technical presentations.

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

  1. TLCinNM says:

    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.

  2. Anonymous says:

    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

  3. GPYoungGuns says:

    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!!

  4. Glade to hear that this helped!

  5. Anonymous says:

    Mohammad – you're a life saver!Would you have a similar script for Receivables? (before I start trying to write one!!!).Ian Stewart MVP

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: