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/