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:

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:

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