SQL view for Dynamics GP Checkbook Register


The view below will return all Checkbook transactions for Dynamics GP. It does not show details for the deposits because I wanted to keep this to one line per transaction, as it appears on the Checkbook Register in GP. To add deposit detail to you report, you can join table CM20300 on CM20200.CMTrxNum = CM20300.depositnumber.

You can find other Dynamics GP views on my GP Reports page.

~~~~~

CREATE VIEW view_Checkbook_Register
as

/******************************************************************* 
view_Checkbook_Register
Created on Oct 14, 2010 by Victoria Yudin 
For updates see https://victoriayudin.com/gp-reports/ 
All bank rec transactions - one line per transaction 
Includes voided transactions 
Tables: CM20200 - bank transactions 
CM20600 - bank transfers 
CM40101 - transaction type setup 

Updated Nov 10, 2010 to link to CM40101 table instead of hard coding 
transaction types (thanks to Siva Venkataraman for this suggestion) 

Updated Feb 18, 2011 to include code for bank transfers from and to 
the same checkbook (thanks to Max Toledo for this suggestion)
*******************************************************************/

SELECT	T.CHEKBKID Checkbook_ID,
	T.CMTrxNum Trx_Number,
	D.DOCABREV Trx_Type,
	T.TRXDATE Trx_Date,
	T.GLPOSTDT GL_Posting_Date,
	T.paidtorcvdfrom Paid_To_Received_From,
	CASE
	  WHEN T.CMTrxType in (1,2,5,101,102)
	    THEN T.Checkbook_Amount
	  WHEN T.CMTrxType = 7 AND X.CMCHKBKID = X.CMFRMCHKBKID
	    THEN T.Checkbook_Amount*0 
	  WHEN T.CMTrxType = 7 AND T.CHEKBKID = X.CMCHKBKID
	    THEN T.Checkbook_Amount
	  WHEN T.CMTrxType = 7 AND T.CHEKBKID = X.CMFRMCHKBKID
	    THEN T.Checkbook_Amount*-1
	  ELSE T.Checkbook_Amount*-1
	  END Checkbook_Amount,
	T.CURNCYID Currency_ID,
	T.DSCRIPTN 'Description',
	T.ClrdAmt Cleared_Amount,
	CASE T.Recond
	  WHEN 1 THEN 'Yes'
	  ELSE 'No'
	  END Reconciled,
	T.AUDITTRAIL Audit_Trail,
	CASE T.VOIDED
	  WHEN 1 THEN 'Yes'
	  ELSE 'No'
	  END Voided 

 FROM CM20200 T 

 LEFT OUTER JOIN
 CM20600 X
 ON T.Xfr_Record_Number = X.Xfr_Record_Number

 INNER JOIN CM40101 D
 ON D.CMTrxType = T.CMTrxType

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Checkbook_Register TO DYNGRP

 

~~~~~

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

26 Responses to “SQL view for Dynamics GP Checkbook Register”

  1. Ma’am,
    This view is great TYVM.
    I also have a question: If my AP got their checks out of sequence (they know to fix with next check num) and the physical check is cashed and done and GP knows as different number. Can I simply update the CM20200 CMTrxNum or are there other tables that check number is hitting? I would also VOID out the paper check for the one that GP thinks it is as I update it in SQL to the correct one matching the physical paper check.

    Thanks in advance,
    jason

    Like

    • Hi Jason,

      The check numbers are in A LOT of tables because the check number becomes the Document Number (DOCNUMBR) in Payables. So they will be in numerous Payables tables (transaction tables, apply tables, distribution tables, keys table) and also in GL tables and Bank Rec tables.

      If at all possible, I would recommend making a list of the ‘mapping’ – real check numbers to the GP check numbers and keeping it with the accounting records, to be used for auditing/reconciling, and then leaving the data alone. The reason is that if you don’t do this exactly right you are going to break a lot of the drillback and linking in GP and could cause data issues afterwards for aging reports, check links and reconcile functions, and who knows what else.

      If this is not possible to leave the data alone and it absolutely has to be fixed, I would recommend working with either your GP Partner or Microsoft Dynamics GP support to come up with the appropriate scripts to fix all the tables needed. And make lots and lots of backups!

      -Victoria

      Like

  2. Good morning – I used this view to create a smartlist with Smartlist Builder. The Checkbook Amount displays as a negative value in Smartlist. I tried to use the feature of Builder to “Display as negative based on field” for Checkbook Amount but it does not change the value of that field. Is there a way to modify the above code to have Checkbook Amont display as an absolute value?

    Like

  3. Hi Victoria,

    My company is still behind the times due to budgetary constraints and am using GPS Version 8.0.

    I am looking to automate, as best as possible, a daily cash projection spreadsheet in Excel by creating an SQL view in Crystal Reports. My first instinct is to have this View/Report pull data from the Checkbook and Payables modules. We don’t use the Receivables module. It would have a column for each day of the month (I would set it up as a cross tab report) and for future dates it would show what we project to have deposited and what payables are due and for the past dates, what was actually deposited and paid.

    Am I on the right track here or I am so far off that I should have my head examined? If I am on the right track, which tables/fields would I use to link the Checkbook and Payables modules?

    THanks in advance.

    Gordon

    Like

    • Hi Gordon,

      I am not sure this would work because there is nowhere in GP for you to store your projections for future deposits. And if you create one, then someone has to keep populating it and updating it. The best you could do from GP tables is get historical data for deposits and only what is currently entered in payables. Another issue that may come up is what data is actually in payables. Many companies do not enter all their payables in a timely manner and/or do not use due dates properly in GP, so it may mean a big change to the payables procedures for the accounting staff to allow this type of reporting to work. Even for the data already in GP, this report would be specific to how (and where) your company enters transactions so I am not sure I could give you a generic answer. This sounds like a report you really need to plan out well first, to understand what kind of data it needs to show and where your company is entering/storing this data. Perhaps there is a resource at your GP Partner company that can help you with this?

      -Victoria

      Like

      • Hi Victoria,

        Thanks for your input and yes, you are correct, there is no place to store that data for future deposits in GP. I was thinking that it would have to be done in the report itself as a formula of Given data.

        My thought was to have “given” information in the report which would be our future projected deposits. I would then use IF THEN ELSE statements to the effect of “If TRXDATE is less than current date, then use actual amounts and if TRXDATE is greater than current date, then use the projected amounts. Or something like that that. I have to play around with it and see if that will get me on my way. Could you suggest which Tables/Fields I could use to link the Checkbook to the Payables?

        Like

        • Gordon,

          Why do you need to link the Checkbook to Payables? Anything already paid will be in both, but you can use either. Since you’re looking for both paid and unpaid payables, I would probably just use PM30200 for paid and PM20000 for unpaid. And only use the Checkbook tables for the deposits.

          -Victoria

          Like

  4. Hi Victoria

    A client, by mistake, when making bank transfers, select the same checkbook (CMFRMCHKBKID = CMCHKBKID). Your query does not support it, and doubles Checkbook_Amount when it make zero effect. I suggest to change your CASE function:

    CASE
    WHEN T.CMTrxType in (1,2,5,101,102)
    THEN T.Checkbook_Amount
    WHEN T.CMTrxType = 7 AND X.CMCHKBKID = X.CMFRMCHKBKID
    THEN T.Checkbook_Amount*0 — zero effect
    WHEN T.CMTrxType = 7 AND T.CHEKBKID = X.CMCHKBKID
    THEN T.Checkbook_Amount
    WHEN T.CMTrxType = 7 AND T.CHEKBKID = X.CMFRMCHKBKID
    THEN T.Checkbook_Amount*-1
    ELSE T.Checkbook_Amount*-1
    END Checkbook_Amount,

    I know that is an error, but I think you good include on your query.

    Sincerely,
    Max

    Like

  5. How can I add
    CM20300 on CM20200.CMTrxNum = CM20300.depositnumber ?
    Thanks

    Like

    • Ilya, you could add the following code at the end of my code (before the GO):

      LEFT OUTER JOIN CM20300 DEP
      ON T.CMTrxNum = DEP.depositnumber

      And add the fields from CM20300 that you need to the SELECT list.

      Or you could write separate view for the deposit details and link the two views in your report, depending on how/where you are writing your report.

      -Victoria

      Like

  6. Sivakumar Venkataraman Reply November 9, 2010 at 11:37 pm

    Hi Victoria

    Instead of hardcoding the abbreviations for the transaction type, you can have it pulled from the setup. Like the one shown below. 🙂


    CREATE VIEW view_Checkbook_Register
    AS
    /*******************************************************************
    view_Checkbook_Register
    Created on Oct 14, 2010 by Victoria Yudin
    For updates see https://victoriayudin.com/gp-reports/
    All bank rec transactions - one line per transaction
    Includes voided transactions
    Tables:
    CM20200 - bank transactions
    CM20600 - bank transfers
    *******************************************************************/

    SELECT T.CHEKBKID Checkbook_ID,
    T.CMTrxNum Trx_Number,
    S.DOCABREV Trx_Type,
    T.TRXDATE Trx_Date,
    T.GLPOSTDT GL_Posting_Date,
    T.paidtorcvdfrom Paid_To_Received_From,
    CASE WHEN T.CMTrxType IN ( 1, 2, 5, 101, 102 ) THEN T.Checkbook_Amount
    WHEN T.CMTrxType = 7
    AND T.CHEKBKID = X.CMCHKBKID THEN T.Checkbook_Amount
    WHEN T.CMTrxType = 7
    AND T.CHEKBKID = X.CMFRMCHKBKID THEN T.Checkbook_Amount * -1
    ELSE T.Checkbook_Amount * -1
    END Checkbook_Amount,
    T.CURNCYID Currency_ID,
    T.DSCRIPTN 'Description',
    T.ClrdAmt Cleared_Amount,
    CASE T.Recond
    WHEN 1 THEN 'Yes'
    ELSE 'No'
    END Reconciled,
    T.AUDITTRAIL Audit_Trail,
    CASE T.VOIDED
    WHEN 1 THEN 'Yes'
    ELSE 'No'
    END Voided
    FROM CM20200 T
    LEFT OUTER JOIN CM20600 X ON T.Xfr_Record_Number = X.Xfr_Record_Number
    INNER JOIN CM40101 S ON S.CMTrxType = T.CMTrxType

    /** the following will grant permissions to this view to DYNGRP,
    leave this section off if you do not want to grant permissions **/
    GO
    GRANT SELECT ON view_Checkbook_Register TO DYNGRP

    Hopefully this helps… 🙂

    Cheers
    Siva

    Like

Trackbacks/Pingbacks

  1. SQL view for bank deposits and receipts in Dynamics GP | Interesting Findings & Knowledge Sharing - October 9, 2011

    […] a year ago I published a view for a Checkbook Register and have received some follow up requests asking for a way to show the receipt […]

    Like

  2. SQL view for bank deposits and receipts in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - October 6, 2011

    […] 0 About a year ago I published a view for a Checkbook Register and have received some follow up requests asking for a way to show the receipt […]

    Like

  3. SQL view for bank deposits and receipts in Dynamics GP | Victoria Yudin - October 6, 2011

    […] 6, 2011 by Victoria Yudin 0 Comments About a year ago I published a view for a Checkbook Register and have received some follow up requests asking for a way to show the receipt […]

    Like

  4. Mohammad R. Daoud - January 1, 2011

    Checkbook Transactions Using SQL Query…

    Interesting article by Victoria Yudin, she displayed the Checkbook Register using SQL view, find her…

    Like

  5. Checkbook Transactions Using SQL Query - Interesting Findings & Knowledge Sharing - November 18, 2010

    […] Interesting article by Victoria Yudin, she displayed the Checkbook Register using SQL view, find her article here. […]

    Like

  6. SQL view for Dynamics GP Checkbook Register - Interesting Findings & Knowledge Sharing - November 18, 2010

    […] the original post here: SQL view for Dynamics GP Checkbook Register Tagged with: bank […]

    Like

  7. SQL Script for Checkbook Balance - Interesting Findings & Knowledge Sharing - GP Technical Blogs - Microsoft Dynamics Community - November 12, 2010

    […] take a look at the checkbook register query posted by Victoria Yudin, here. SELECT Z.CHECKBOOKID, Z.GPACCOUNTNO, Z.DOCUMENTNO, Z.DOCTYPE, Z.DOCTYPENAME, Z.POSTINGDATE, […]

    Like

  8. DynamicAccounting.net - October 15, 2010

    SQL view for Dynamics GP Checkbook Register…

    Victoria Yudin is back with a new SQL view for Dynamics GP Checkbook Register ….

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: