Payables Transactions not in GL

Picture this: the auditors are in, they asking for myriads of reports, and precisely one of the things they ask is something you cannot easily achieved from the slur of reports and SmartLists available from within Microsoft Dynamics GP. To make matters worst, the "go to" guy for queries and special requests is out of the office sick with the flu. Well worry no more! If you are asked to show all the payables transactions that were never recorded or posted to GL you can run the following query from SQL Server Management Studio (or Query Analyzer if you still happen to be on SQL Server 2000):

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
SELECT
   A.VENDORID, A.DOCNUMBR, A.DOCDATE, A.CNTRLNUM, GL.JRNENTRY,
   GL.TRXDATE, GL.ACTINDX, C.ACTNUMST, D.ACTDESCR, GL.REFRENCE,
   GL.ORTRXTYP, GL.ORCTRNUM, GL.ORMSTRID, GL.ORMSTRNM,
   GL.ORDOCNUM
FROM PM00400 A LEFT OUTER JOIN
  ( SELECT JRNENTRY, TRXDATE, REFRENCE, ORTRXTYP, ACTINDX, ORCTRNUM, ORMSTRID,
      ORMSTRNM, ORDOCNUM
    FROM GL20000
    WHERE SERIES = 4

    UNION ALL

    SELECT JRNENTRY, TRXDATE, REFRENCE, ORTRXTYP, ACTINDX, ORCTRNUM, ORMSTRID,
      ORMSTRNM, ORDOCNUM
    FROM GL30000
    WHERE SERIES = 4
  ) GL ON(A.VENDORID = GL.ORMSTRID) AND (A.CNTRLNUM = GL.ORCTRNUM) AND 
    (A.DOCNUMBR = GL.ORDOCNUM)
LEFT OUTER JOIN GL00105 C ON (GL.ACTINDX = C.ACTINDX)
LEFT OUTER JOIN GL00100 D ON (GL.ACTINDX = D.ACTINDX);
GO


Knowing what transactions have not been posted or recorded in GL is particularly useful when attempting to reconcile the AP subsidiary module to General Ledger. It will also allow you to identify those records loaded as beginning balances from within AP.

With little or no effort, this query can be added to SmartList Builder (SLB), by creating a SQL Server view with the inner query, and exposing the view to SLB. Don't forget to run the GRANT.SQL utility script to assign permissions to the DYNGRP.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Comments


Dear Mariano Gomez,

I'm looking for "Receivables Transactions not in GL" which lead me to your article.
Have you made such a thing!?

Best Regards

Hussain Ali Al-Yousef
Mariano Gomez said…
Hussain,
This is now what the Reconcile to GL routine does, under Financials > Routine > Reconcile to GL. When I created the above script, the Reconcile to GL feature did not exist in GP.

Popular posts from this blog

Power Apps - Application Monitoring with Azure Application Insights

DBMS: 12 Microsoft Dynamics GP: 0 error when updating to Microsoft Dynamics GP 2013 R2

eConnect Integration Service for Microsoft Dynamics GP 2010