-- 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/
2 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
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.
Post a Comment