SQL view for bank deposits and receipts in Dynamics GP

Victoria Yudin | 10/06/2011 | 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 details. Primarily this request seems to come from the need to see the difference in dates between the receipts and the deposits for tracking down possible bank reconciliation issues. I didn’t want to clutter up the original view with this, so I thought it would be better to create a separate view showing the receipt details.

The view below shows all the posted deposits and the associated receipts. Each receipt is on a separate line and the deposit information will be repeated as many times as there are receipts in that deposit.

~~~~~

CREATE VIEW view_Bank_Deposits_and_Receipts
as

/*******************************************************************
view_Bank_Deposits_and_Receipts
Created on Oct 6, 2011 by Victoria Yudin
For updates please see http://victoriayudin.com/gp-reports/
All bank rec deposits with their receipts - one line per receipt
Includes voided transactions
Tables:
CM20200 - bank transactions
CM20300 - bank receipts
CM40101 - transaction type setup
*******************************************************************/

SELECT
     T.CHEKBKID Checkbook_ID,
     T.CMTrxNum Deposit_Number,
     T.TRXDATE Deposit_Date,
     T.GLPOSTDT Deposit_GL_Posting_Date,
     T.Checkbook_Amount Deposit_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 Deposit_Voided,
     R.RCPTNMBR Receipt_Number,
     R.receiptdate Receipt_Date,
     R.GLPOSTDT Receipt_GL_Posting_Date,
     CASE R.RcpType
       WHEN 1 THEN 'Check'
       WHEN 2 THEN 'Cash'
       WHEN 3 THEN 'Credit Card'
       END Receipt_Type,
     R.RcvdFrom Received_From,
     R.ORIGAMT Originating_Amount,
     R.Checkbook_Amount Receipt_Amount,
     R.CURNCYID Receipt_Currency,
     CASE R.VOIDED
       WHEN 1 THEN 'Yes'
       ELSE 'No'
       END Receipt_Voided

FROM CM20200 T

LEFT OUTER JOIN CM40101 D
ON D.CMTrxType = T.CMTrxType

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

WHERE D.DOCABREV = 'DEP'

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

Filed under: Bank Rec SQL code, Dynamics GP, GP Reports code, GP SQL scripts Tagged: Bank Reconciliation, GP Reports code, GP SQL view, SQL code