SQL view for bank deposits and receipts in Dynamics GP | Interesting Findings & Knowledge Sharing.INNER JOIN CM40101 S ON S.CMTrxType = T.CMTrxType LEFT OUTER JOIN CM20600 X ON T.Xfr_Record_Number = X.Xfr_Record_Number 🙂ĬASE WHEN T.CMTrxType IN ( 1, 2, 5, 101, 102 ) THEN T.Checkbook_AmountĪND T.CHEKBKID = X.CMCHKBKID THEN T.Checkbook_AmountĪND T.CHEKBKID = X.CMFRMCHKBKID THEN T.Checkbook_Amount * -1 Instead of hardcoding the abbreviations for the transaction type, you can have it pulled from the setup. Perhaps there is a resource at your GP Partner company that can help you with this? 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. 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. 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. Another issue that may come up is what data is actually in payables. The best you could do from GP tables is get historical data for deposits and only what is currently entered in payables. And if you create one, then someone has to keep populating it and updating it. I am not sure this would work because there is nowhere in GP for you to store your projections for future deposits. 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. 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 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. 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. The check numbers are in A LOT of tables because the check number becomes the Document Number (DOCNUMBR) in Payables.
#Chequebook register update#
GRANT SELECT ON view_Checkbook_Register TO DYNGRPĭisclaimer: 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. Leave this section off if you do not want to grant permissions **/ ** the following will grant permissions to this view to DYNGRP, ON T.Xfr_Record_Number = X.Xfr_Record_Number WHEN T.CMTrxType = 7 AND T.CHEKBKID = X.CMFRMCHKBKID WHEN T.CMTrxType = 7 AND T.CHEKBKID = X.CMCHKBKID WHEN T.CMTrxType = 7 AND X.CMCHKBKID = X.CMFRMCHKBKID The same checkbook (thanks to Max Toledo for this suggestion)
#Chequebook register code#
Updated to include code for bank transfers from and to Transaction types (thanks to Siva Venkataraman for this suggestion) Updated to link to CM40101 table instead of hard coding ~~~~~ CREATE VIEW view_Checkbook_Register You can find other Dynamics GP views on my GP Reports page. To add deposit detail to you report, you can join table CM20300 on CM20200.CMTrxNum = positnumber.
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. The view below will return all Checkbook transactions for Dynamics GP.
0 kommentar(er)
