T-SQL Script for Outstanding Checks (Dynamics GP)

Hello:

Below is a T-SQL script that I ran for a Microsoft Dynamics GP environment and that I designed for pulling payables checks that have not been reconciled for the UPTOWN TRUST checkbook and for checks that are dated on or before 06/30/2015.  In effect, I'm trying to extract outstanding checks from SQL.  The client is not on GP 2013.  So, they cannot use the "new" Outstanding Transactions report.

Actually, I modified this for one of our client's checkbooks.

In any case, it does not seem to be pulling enough data.  Specifically, I should be seeing checks whose document amounts total about $589k.  When I dump the results to Excel, that total is only about $86k.

Does anyone have any ideas on how I can modify this, to pull accurate data?

Thanks!

TBSupport
select  * from PM30200
 INNER JOIN CM20200
 on PM30200.DOCNUMBR = CM20200.CMTrxNum
 and PM30200.VENDORID = CM20200.CMLinkID
 and PM30200.CHEKBKID = CM20200.CHEKBKID
 and PM30200.DOCDATE = CM20200.TRXDATE
 where
 PM30200.DOCDATE <= ‘06/30/2015’
 and PM30200.VCHRNMBR in (select CNTRLNUM from PM00400 where CHEKBKID in (‘UPTOWN TRUST’) and DOCTYPE =  '6' and  TRXSORCE  like 'PMCHK%')
 and CM20200.SOURCDOC = 'PMCHK'  and CM20200.Recond  = '0'
 UNION
 select * from PM20000  
 INNER JOIN CM20200
  on PM20000.DOCNUMBR = CM20200.CMTrxNum
 and PM20000.VENDORID = CM20200.CMLinkID
 and PM20000.CHEKBKID = CM20200.CHEKBKID
 and PM20000.DOCDATE = CM20200.TRXDATE
 where
 PM20000.DOCDATE <= ‘06/30/2015’
 and PM20000.VCHRNMBR in (select CNTRLNUM from PM00400 where CHEKBKID in (‘UPTOWN TRUST’) and DOCTYPE =  '6' and TRXSORCE  like 'PMCHK%')
 and CM20200.SOURCDOC = 'PMCHK'  and CM20200.Recond  = '0'
LVL 1
TBSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
I do not know GP's data model so I cannot comment on that, but there are 2 things I would do differently

a. For extracting financial transactions it is rare that UNION [DISTINCT] would be correct
nb: When you use just the word UNION the "DISTINCT" is assumed. The alternative is to use UNION ALL this will return all rows.

However as I said I'm not a GP user.

b. ALWAYS use the "safe" date literal format of YYYYMMDD
     06/30/2015      is NOT SAFE (if dbms settings change it could fail)
     20150630         IS safe (if dbms settings change it will still work)
--TBSupport
SELECT
      *
FROM PM30200
      INNER JOIN CM20200 ON PM30200.DOCNUMBR = CM20200.CMTrxNum
                  AND PM30200.VENDORID = CM20200.CMLinkID
                  AND PM30200.CHEKBKID = CM20200.CHEKBKID
                  AND PM30200.DOCDATE = CM20200.TRXDATE
WHERE PM30200.DOCDATE <= '20150630'
      AND PM30200.VCHRNMBR IN (
            SELECT
                  CNTRLNUM
            FROM PM00400
            WHERE CHEKBKID IN ('UPTOWN TRUST')
                  AND DOCTYPE = '6'
                  AND TRXSORCE LIKE 'PMCHK%'
      )
      AND CM20200.SOURCDOC = 'PMCHK'
      AND CM20200.Recond = '0'
UNION ALL
      SELECT
            *
      FROM PM20000
            INNER JOIN CM20200 ON PM20000.DOCNUMBR = CM20200.CMTrxNum
                        AND PM20000.VENDORID = CM20200.CMLinkID
                        AND PM20000.CHEKBKID = CM20200.CHEKBKID
                        AND PM20000.DOCDATE = CM20200.TRXDATE
      WHERE PM20000.DOCDATE <= '20150630'
            AND PM20000.VCHRNMBR IN (
                  SELECT
                        CNTRLNUM
                  FROM PM00400
                  WHERE CHEKBKID IN ('UPTOWN TRUST')
                        AND DOCTYPE = '6'
                        AND TRXSORCE LIKE 'PMCHK%'
            )
            AND CM20200.SOURCDOC = 'PMCHK'
            AND CM20200.Recond = '0'
;

Open in new window

sorry if this doesn't address the more substantive issue.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.