October 6, 2011 
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 https://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.
Victoria Yudin 
Hello Victoria,
As always, I find your posts so invaluable in resolving my day to day reporting needs from GP.
I have been searching your posts for a solution to my problem but cannot find anything like it.
We are using CBM to enter our cash receipts for both AR and GL type trans. The problem is that when the AR receipts are viewed in AR the receipt number entered in CBM is not carried over to AR, instead a default receipt number is used. This is a problem as we print the cash receipts from CBM and then it is difficult for the person reconciling to relate that transaction to AR. The original receipt number does not appear on the AR Edit list nor the Posting journal.
Is there a view that ties these two side together or is there an RM table where the receipt number is referenced?
As always, I find your posts so invaluable in resolving my day to day reporting needs from GP.
LikeLike
Hi Jackie,
Thanks for your kind words. I am not sure what you mean by “CBM” – can you please give me some more details on exactly how your cash receipts are entered?
-Victoria
LikeLike
Hello Victoria, Thank you for responding to my query. The CBM module is the Cashbook Bank Management. Because I am unable to print the cash receipts when entered as a receivables transaction, nor can I enter non-AR receipts I have chosen to uses this module to enter my receipts as it allows me to do both.
there is a cash receipts option found under Transactions–> Financial–> BankManagement . These cash receipts are found in the CB330222 TABLE.
LikeLike
Hi Jackie,
Thanks for the additional info. Unfortunately, I’ve never worked with the CBM module nor have any clients that use it, so I am not much help with that. Normally I recommend entering Receivables Cash Receipts for all AR receipts and Bank Transaction receipts for non-AR receipts and they all show up in the same place at the end of the day for deposits and bank rec (and of course, GL). However, I am not sure what implications this has for CBM.
You might find some better answers on the Dynamics GP community forum, here is the link for that in case you do not have it: https://community.dynamics.com/gp/f/32
-Victoria
LikeLike
LEFT OUTER JOIN CM20300 R
ON r.CHEKBKID = t.chekbkid and R.depositnumber = T.CMTrxNum
allowed me to keep deposits linked to a specific checkbook.
LikeLike
Looks interesting, but just be aware that when you put a ‘where’ criteria on D, it negates the left join from T to D.
“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’ ”
You will only end up with records from T that match to a ‘DEP’ record in D. As long as this is what you want, all is good.
Your query is synonymous to this version, which is probably the slightest bit more efficient:
FROM CM20200 T
JOIN CM40101 D
ON D.CMTrxType = T.CMTrxType and D.DOCABREV=’DEP’
LEFT OUTER JOIN CM20300 R
ON R.depositnumber = T.CMTrxNum
LikeLike
Thanks for the tip Jay.
-Victoria
LikeLike