web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / outstanding (uncleared...
Microsoft Dynamics GP (Archived)

outstanding (uncleared) checks

(0) ShareShare
ReportReport
Posted on by Community Member

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

I have the same question (0)
  • Suggested answer
    L Vail Profile Picture
    L Vail 65,271 on at

    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

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    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)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans