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 http://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,
 CASE T.CMTrxType
    WHEN 1 THEN 'DEP'
    WHEN 2 THEN 'RCT'
    WHEN 3 THEN 'CHK'
    WHEN 4 THEN 'WDL'
    WHEN 5 THEN 'IAJ'
    WHEN 6 THEN 'DAJ'
    WHEN 7 THEN 'XFR'
    WHEN 101 THEN 'INT'
    WHEN 102 THEN 'OIN'
    WHEN 103 THEN 'OEX'
    WHEN 104 THEN 'SVC'
    ELSE ''
    END 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

/** 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.