Share
ReportHello:
Below is a T-SQL script that I designed for pulling payables checks that have not been reconciled for the a 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.
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!
John
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 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 <= ‘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'
*This post is locked for comments
I have the same question (0)
All responses (
Answers (
