I am trying to create/generate a report that will show me all outstanding (uncleared) checks at a given point in time. For example, any day of the week, throughout the month, not only after the account's been reconciled (to help manage cash flow). I've read online that this is an impossibility in GP v10. Does anyone have ideas on how to make this happen? Thank you.
*This post is locked for comments
To enhance Leslie's script above, you will also want to include voiding flags and dates to get the exact reconciled amount as of a specific date.
declare @date as varchar(max)='2016-12-31'
declare @checkbook as varchar(max)='Checkbook Name'
SELECT CHEKBKID as Checkbook
, CMTrxNum as [CM Number]
, case CMTrxType
when 1 then 'Deposit'
when 2 then 'Receipt'
when 3 then 'Check'
when 4 then 'Withdraw'
when 5 then 'Increase Adj'
when 6 then 'Decrease Adj'
when 7 then 'Transfer'
when 101 then 'Interest Income'
when 102 then 'Other Income'
when 103 then 'Other Expense'
when 104 then 'Service Charge' end as [TRX Type]
, TRXDATE as [TRX Date]
, TRXAMNT as [TRX Amount]
FROM CM20200
WHERE TRXDATE <= CONVERT(DATETIME, @date, 102) AND (clearedate > CONVERT(DATETIME, @date, 102)
OR clearedate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102)) and chekbkid=@checkbook and (voided<>1 or voiddate>@date)
The report may not be canned, but the information is certainly in the system. All you need to do is write a report/smartlist that looks at the bank transaction table and pick checks according to the date they were written and the date they cleared. If I want to print a report showing all checks outstanding on 04/30/2017, I would write something like this:
SELECT CHEKBKID, CMTrxNum, CMTrxType, TRXDATE, TRXAMNT
FROM CM20200
WHERE (TRXDATE <= CONVERT(DATETIME, '2017-04-30 00:00:00', 102)) AND (clearedate > CONVERT(DATETIME, '2017-04-30 00:00:00', 102) OR
clearedate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102))
Kind regards,
Leslie
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,317
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
