Bank Rec Tables


Commonly Used Tables:

  • CM00100 – Checkbook Master   - setup information like GL account, next check and deposit numbers and checkbook balance
  • CM10100 – Deposit Work   - saved unposted deposit data
  • CM20100 – Bank Rec Journal   - contains a record for each posted transaction, allows for reprinting of some bank rec posting journals
  • CM20200 – Transactions   - all transaction data except receipts
  • CM20201 – Transaction Totals   - information for posted deposits
  • CM20300 – Receipts  - all receipts (deposited and undeposited)
  • CM20400 – Distributions   - GL distributions for all bank rec transactions
  • CM20500 – Reconciliation Headers   - header information entered during bank reconciliation
  • CM20501 – Reconcile Adjustments   - adjustments on current reconciliations
  • CM20600 – Transfers

INACTIVE in CM00100:
0 – Active
1 – Inactive

CNTRLTYP (Control Type):
1 – Transaction
2 – Receipt

CMTrxType (Transaction Type) and abbreviation:
1 – DEP – Deposit
2 – RCT – Receipt [not used in CM20200]
3 – CHK – Check
4 – WDL – Withdrawal
5 – IAJ – Increase Adjustment
6 – DAJ – Decrease Adjustment
7 – XFR – Transfer
101 – INT – Interest Income
102 – OIN – Other Income
103 – OEX – Other Expense
104 – SVC – Service Charge

DEPTYPE (Deposit Type):
1 – Deposit with Receipts
2 – Deposit without Receipts
3 – Clearing Deposit

DEPOSITED:
0 – False
1 – True

RcpType (Receipt Type):
1 – Check
2 – Cash
3 – Credit Card

~~~~~

Thank you to Michael Lupro of BestTechsNW and Paul Maynell of Maynell Associates for their help with providing additional information on this page!

 


Last Updated 01.27.2009

121 Responses to “Bank Rec Tables”

  1. Thanks Victoria, this query works perfectly for what I need. Just have to filter the document type for ‘Payments’ and add a date range. Thanks again.

    Like

  2. Hi Victoria, trying to do a check register report that includes the vendor class in it for a report my accountant does. Right now its being done manually. Any suggestions on how to pull in the class id from the PM00200 table to go with this report?

    Like

    • Hi Debbie,

      Do you have checks entered directly in the Bank Rec module, not going through Payables? That’s not very common… If you only enter checks in payables transactions, you can use something like my my view for all payables transactions as a start. The PM00200 table is already linked there, so you can add the vendor class. If you want to differentiate between checks and other types of payments, you may need to add a little more code.

      -Victoria

      Like

  3. Thanks for the reply! Yes, the void was done in error.

    Like

    • Hi Jana,

      If that is the case, you will need to re-enter the payment in AP, right? When you do that, you will have a positive and a negative in Bank Rec, selecting both on the next reconciliation will clear them both out. Hope that helps.

      -Victoria

      Like

  4. Hi Victoria,
    We managed to void a check through payables transaction history (Dynamics 2010) that was previously reconciled. It now show as a negative on our bank reconciliation. What is the best way to fix this? (We have done nothing yet to make sure we do it right the first time).

    Thank you

    Like

  5. Hi Victoria…need help-a voided check was accidentally mailed and cleared the bank. To reconcile to the bank a DAJ was done to Debit A/P and Credit Cash. In the next month, a Manual payment was done as cash to relieve the invoices from Payables for the voided check that cleared the bank. Now, the bank rec has a WDL waiting to clear. And A/P aging does not balance with GL. But can’t clear the WDL bcz the DAJ already handled the voided check clearing the bank. So, a General JE was done to reverse the DAJ so the WDL could be cleared. Along the way, another JE was done Dr to Cash Cr to A/P. WDL has also been cleared at this point. A/P aging is still off balance with G/L, if I void a trx – Cash will be off. Did reversing the DAJ as a General JE create an issue with reconciling the bank account? Big mess.

    Like

    • Hi Dana,

      As you say, ‘big mess’. While we could try to go through each of the ‘fixes’ above one at a time and try to unravel what has happened, I think I would be doing you a disservice offering advice on this kind of a situation in blog messages. Not to mention it would probably take a couple of months of messages back and forth, because I personally would have multiple questions on every single transaction you’ve mentioned.

      Once you’re more than a few ‘fixes’ past the original issue, you would be much better served to have someone actually look at your system and see all of these transactions in detail. I would recommend talking to your GP Partner to get them to look at this with you and help fix it for good.

      -Victoria

      Like

  6. Hi Victoria! I hope you are able to help me. I found this post about the CM tables very helpful, but what I thought I fixed a couple weeks ago with a Bank Rec issue has come back to me!

    The problem I am having is that two cash payments to vendors were voided. They show as voided in the Checkbook Register and in the Vendor Inquiry. However, they are showing up in the bank rec as un-reconciled. After trying a finance reconcile and running checklinks with no success, I updated the CM20200 table field RECOND = 1 for the two records. Later, I did update other fields in this table to show zero dollars and mark it void. This did take it out of reconciliation and after running a checklinks and reconcile, it updated the Adj Book Balance = Adj Bank Balance. Since then, they have moved on to the next month’s reconcile. Now the problem has returned. Now when I run CL, it puts back in the two transactions with zero dollar. However the Adjusted balances are off by the original amounts…

    Log story short, what tables to I need to look at in order to get this straightened out? I’ve looked at all of the CM tables and cant see what I am missing. I hope you can point me in the right direction. Thanks so much in advance!
    Jim

    Like

    • Hi Jim,

      For the record, I do not recommend doing anything like this directly in the tables. As you have seen, it typically creates a bigger issue in the long run as there are many other related tables. At this point, I would suggest working with either GP Support or your GP Partner so they can look at your data and help you fix this. Since there have been multiple changes at this point, without actually seeing it, I am not sure what to even suggest for a fix.

      -Victoria

      Like

      • Hi Victoria, thanks for responding. I agree with your statement. Normally I would not touch anything in SQL, but nothing I tried within the GP utilities worked. I think you right in that I need to get Microsoft involved at this point.
        Thanks again and thanks for always helping out the community!
        Jim

        Like

  7. Hi Victoria,

    Do you know which table contains the Next IAJ and DAJ numbers?

    Like

  8. Hi Victoria

    Firstly apologies that this probably isnt the right place to post this but I would be very grateful for any advice. When attempting to do an Advance Bank Reconciliation, upon selecting the required Bank Account ID, I am given the following errror:

    An ABR Reconciliation For Bank ID ___ ____ Is In Progress By User ______ – Please Try Again When This Has Completed

    This has been the case now for a couple of days. I have removed the stated user (and all other users) from GP through Tools–>Utilities–>System–>User Activity… Please can you propose any solutions? (I have a very basic knowledge of SQL so an idiots guide may be needed)

    Thanks in advance!
    Chris

    Like

    • Hi Chris,

      Is ‘Advance Bank Reconciliation’ a 3rd party product? I see a similarly named product from Nolan, is that what you’re using? If so, I would recommend asking Nolan the best way to clear this up. Most likely there is a stuck record somewhere, but they should be able to easily tell you how to fix this.

      -Victoria

      Like

    • Was this ever figured out? We are having the same error. We tried clearing them out of system user activity with no fix.

      Like

      • Yes, ABR is Nolan’s 3rd party product. It has its own separate ABR Clear User Activity feature, which should help you in your situation.

        Like

  9. Hi Victoria,

    We already have referred this issue to our GP parter, they just said that GP should work fine with multiple users of a company accessing select bank transactions window at the same time provided they are reconciling different checkbooks. That’s what I also thought before we came upon this issue. But after making series of testing on different server and on different companies including Fabrikam on a standard GP installation this notion proved to be inaccurate. Anyways, I guess we just have to make some workaround so we can maximize the time of our people while they wait their turn to do bank recon in GP. Thanks Victoria for always replying to our GP inquiries.

    Herson

    Like

    • Hey Victoria,

      I always appreciate your blog posts and wisdom. We are experiencing extreme performance problems with our EFT process because of the number of records we have in CM20300 (over 1M in one company alone). The Microsoft KB article 2531207 says this is a known bug corrected in the 2011 Payroll Year End Update. The KB also says it’s a viable solution to simply delete records out of this table if you’re not using bank reconciliation.

      I’m torn between going with the service pack, which may make a lot of changes we don’t necessarily want, versus deleting records, which isn’t ideal either but in a way seems less risky. Do you see any drawbacks from either approach? Would you recommend applying the patch or deleting records?

      Like

      • Hi Jay,

        I am not familiar with this particular issue, however my gut feeling would be that deleting records should be a last resort. Usually service packs do not make a lot of changes, they may fix existing issues or add some functionality, but they rarely break anything. To me, that would be the preferable option. That said, again, I am not familiar with your particular setup or situation, so it may be best to discuss this with your GP Partner to get their feedback on it.

        -Victoria

        Like

  10. Hi Victoria,

    Thank you so much for your reply. Yes, I could recreate the same scenario even for a standard GP installation (without customization) on Fabrikam company with LESSONUSER1 and LESSONUSER2 users reconciling checkbooks PAYROLL and UPTOWN TRUST. We do have 3rd party product installed but I believe it should not in anyway get in the way of bank recon module as the customization deals only with the computation of tax. And as I’ve mentioned, the scenario can be recreated
    on a different server with GP’s sample company Fabrikam so the server and customization could not have cause the issue. Hope we can clear this or maybe you could suggest a workaround so that multiple users can do bank reconciliation simulataneously because we have many checkbooks to reconcile. Thank you Victoria for being so accommodating to our inquiries.

    herson

    Like

    • Hi Herson,

      I do not have access to a test GP 9.0 environment right now, but I just tested this with GP 2010 and am not able to duplicate this behavior. Since you’re on a version of GP that is no longer supported, you cannot go to Microsoft to ask about this, however, you could try posting this question on the GP community forum to see if anyone else has run into this or has any other ideas.

      For what it’s worth, my gut feeling on this is that it is not a bug or known issue, but something specific to your environment/data. Having multiple users perform bank reconciliations simultaneously is not uncommon and based on my understanding of how GP stores the bank rec transactions, there should never be any cross over like what you’re describing. The fact that you can see it in the sample company with the default checkbooks seems to indicate that it is not your data, but rather something in application and how it is displaying the data. Unfortunately, without being able to see this in action, I am not sure how much more can be done to troubleshoot it. Have you talked to your GP Partner about this?

      -Victoria

      Like

  11. Hi Victoria,

    We’re still on GP9. The problem we encounter right now is that when 2 users do bank reconciliation at the same time for 2 different checkbooks, the select bank transactions windows for both of them shows mix of the two checkbooks transactions. Is this the normal behaviour of GP? Does it limit only one user at a time to do the reconcile bank statement? Any workaround or fixes on this? Hope to hear from you. Thank you very much Victoria.

    herson

    Like

    • Hi Herson,

      I have not heard of this being a problem before in any version of GP. Can you recreate this at will? After restarting the SQL Server and the computers the 2 users are on? Do you have any customizations or 3rd party products installed?

      -Victoria

      Like

  12. Hi Victoria,

    We were posting a payable check through Purchasing>Post Checks when the machine was stuck and hanged, which made us terminate the GP application. When we reopened the batch, it no longer existed in the Payables Batch IDs. We located the Batch number in Batch recovery and we continued it and it disappeard in Batch Recovery. We checked if the Check Balance was updated by going to Financial>Inquiry>Checkbook Balance and Financial>Inquiry>Checkbook Register but unfortunately, the checkbook ID did not dispaly the payables documents that have been applied. We checked tables CM20100, CM20200 and CM20300 respectively and no record were found for the said Checkbook ID.We also Checked GL10001 but there was no entry in there either. The Payables Documents were posted and applied,we did verify it in PM30200 and PM30300 tables respectively.

    Can we fix this by running Reconcile or Check Links? Any suggestions will be highly appreciated. Thanks. :D

    John

    Like

    • John,

      I would certainly try Check Links and Reconcile – and I would run it on the entire Purchasing and Financial series. However, from what you are describing, it’s possible that will not fix this, in which case I would recommend getting help from your GP Partner or GP Support for this so they can take a look at your data and determine what the best approach is.

      -Victoria

      Like

  13. Hi Victoria – we had to remove the user’s access to the modified version of the bank reports because we think the issue is related to “field level” security that we were not able to easily detect with any SQL scripts (checking activity tables for denied access once activity tracking was turned out – great feature if anyone cares – I use it often). We think it’s field level because when we gave the user “poweruser” access and left their access to the modified version of the bank reports, they were able to execute them without receiving the calculation error. It’s only when we reverted their security back to the custom tasks/roles and left them with access to the modified version of the bank reports that they would receive the error. So, rather than keeping the user with poweruser access, we opted to give them access to the non-modified (Microsoft Dynamics GP) version of the reports and that has allowed the user to run their reports. The layout is not ideal in the reports they receive, but it’ll do in a pinch. If/when we discover the offending field/security, we’ll let you know.

    Like

  14. We feel like we have provided a user with all of the correct security tasks which should allow them the ability to perform bank reconciliation tasks (happy to list if needed) but when the user attempts to do so they receive this error message “Error in equation ‘trxvoided'” and I am not able to find anything on this. Has anyone had the same issue and if so, please, how were you able to resolve it?

    Like

    • Kristie,

      What version of GP? That sounds like an error in a report, are any of your Bank Reconciliation reports modified? If so, can you point this user to the unmodified reports and see if you still have the error? Does the Bank Rec actually get completed after this error comes up?

      -Victoria

      Like

  15. Hi Victoria

    I have a user who was applying a deposit in cash recipts, gp froze up then booted her out. She went back in to try and retrive the deposit to finish it , it was not there. When she tried to start over the invoices were not available to select GP is telling her that the invoices are applied to a payment. When we pull up the payment number and click on it we get a message that it does not exist.

    Any ideas?

    thanks
    Vic

    Like

  16. Hi Victoria,

    I’m a newbie to GP so please bare with me. I’m reconciling my bank rec and my Adjusted Bank balance does not equal the adjusted book balance. My Book balance is correct though. What could be wrong? Thank you.

    Like

    • Hi Rev1976,

      This is not something easy to help with in blog comments, as I do not know the details of your GP setup and there are many things that can contribute to what you are seeing. If you are just learning GP, I would recommend getting some training on Bank Rec from your GP Partner. Often going through the first reconciliation together is a great learning experience.

      -Victoria

      Like

  17. Hi Victoria
    please what would be the key fields to create an extender in financials/bank transactions entry window?
    Checkbookid, DepositNumber and DepositType would be enough?
    Thanks a lot

    Like

    • FAC,

      Deposit Number and Deposit Type are not on the Bank Transaction Entry window in my GP version (using US installation). Are they on yours? Or are you asking about a different window?

      If you do need this for the Bank Transaction Entry window, from what I can tell by looking at the tables, the Record Number is the key field to use …doesn’t look like you need anything else.

      -Victoria

      Like

      • Thanks Victoria
        i was looking at the wrong tables (CM10100 deposit work), there are no work tables for CM Bank Transactions (CM20100) right?
        The problem is if i create an extender on Bank Transactions Entry and I use RecordNumber as Key Field when i enter data in that extender in bank transaction entry, the key value (Record Number) is 0.00000.
        I think it only gets its real value when it is posted, after posting i went to inquiry bank transaction and to the extender inquiry window an record number is 3915.00000 and no data in the other fields.
        Thats what i was thinking of using CHEKBKID, CMTrxType and CMTrxNum as Key Fields of that extender.

        I hope you can understand me.
        Thanks a lot for your time Victoria.

        Like

        • FAC,

          Yep, that makes sense. I have not tried this and would not be able to tell you without trying whether it will work for certain, but the CHEKBKID, CMTrxType and CMTrxNum as key fields sound correct to me. :-)

          -Victoria

          Like

  18. Hi Victoria,

    We just installed Analytical Accounting. And when we reconcile the bank, we are getting the Analytical Accounting Error List and we are unable to continue with the bank rec. The transactions that come up on this error list are INT…, OIN… etc. and these transactions are very old and reconciled already. I’m puzzled as to why these errors are coming up now saying AA required and your prompt response on how to fix this will be much appreciated.

    Thanks,

    Depar

    Like

  19. Hi Victoria,

    I created an Integration for entering data in Bank Transaction in GP. This integration pulls data from an excel file. When I run this integration once, the data enters GP and everything is fine. But If I run it again, the data gets duplicated. Is there a way to avoid duplicates in GP?

    I searched a lot on many websites. One solution I got for this is to use a VB Script in IM. Can you help me with this VB Script as I have no idea how to do this.

    If there is any other way, then also let me know. Any hlep will be appreciated. Thanks.

    Pooja Shah

    Like

    • Pooja,

      Two ways I can think of to avoid this:

      1. Import a transaction number instead of letting GP create it automatically. Since transaction numbers have to be unique, GP will kick out any that are duplicates.
      2. Institute some controls for importing data – once it has been imported, mark the file and update a list so that it is clear this data should not be re-imported.

      For help with scripts, you may want to contact your GP Partner.

      -Victoria

      Like

      • Hi Victoria,

        Thanks for your reply.

        But as far as I know, for Bank Transactions window, GP is allowing duplicates. I am using GP 2010. I go to GP->Financial->Transactions->Bank Transaction Window. Here there is a field called Number (CMTrxNum). I entered ‘1’ as the number twice while entering a transaction and posted it & it allows that. It doesn’t give me any message that the number already exists.

        If I do the same in Payables Transaction entry window (GP->Purchasing->Transactions->Transaction Entry) and write the same document number again, it does give a message in GP that ‘This document number already exists’.

        Is there any solution for this?
        Is there any field in Bank Transaction window which is unique and doesn’t allow duplicates?

        Pooja Shah

        Like

        • Hi Pooja,

          You are right, I was thinking that the combination of checkbook/transaction type/transaction number has to be unique. But I just tested this and it does not seem to be the case, so that’s not a valid method. If you cannot use a non-automated way (manually checking or marking something as already imported), then you may need to code around it – this is beyond what I can help with, you would need to talk to a GP developer for help with that.

          -Victoria

          Like

  20. Hi, Victoria,

    Thank you for publishing all this useful information.

    Do you have any idea what the field values might be for the RECONUM field in the Bank Rec tables? I’ve been looking, but have so far been unable to find anything.

    Regards,
    Lyn

    Like

    • Hi Lyn,

      Looks like this is a link to the bank reconciliation that a record is on. In my data, it looks like all the unreconciled transactions have a value of 0 for this and all the others correspond to a record in the CM20500 table (the reconciliation headers).

      -Victoria

      Like

  21. Hello Victoria,

    One of my customers wants to have batches for Bank Reconsiliation (which is not available out of the box offcourse). Can you please help me in analyzing its feasibility?

    1.Why do u think it is not available out-of-the-box?

    2.what can be the challenges if we try to add this feature through customizations?

    Like

    • Hi Sohaib,

      I do not have any special knowledge of why the Bank Rec module was written to not allow batches. My guess is that since not too much should be going directly through the Bank Rec and since most companies want to see the effects of cash movements immediately, it may have been felt by the original designers that batches simply would not be needed. While I always make sure that my customers understand that there are no batches in Bank Rec because it is different from other GP modules, I have not had a single customer experience any issues because of this.

      Why does your customer need to have batches in Bank Rec? What specifically are they trying to accomplish and what types of transactions would they need to batch? Would this be only for manually entered transactions? Transactions coming from other modules? Imported transactions? In general, and without seeing your customer’s detailed requirements, I would not expect this to be a trivial customization.

      Looks like you’ve already started a thread on this topic on the GP Community Forum, so maybe some others will have more feedback for you.

      -Victoria

      Like

  22. No, I’d prefer to void them if possible. Maybe I’ll have to go the macro route? We were not allowing duplicate checks. So entering in duplicate checks in Bank Rec is allowed, NOT good.

    Like

  23. Any idea how to use integration manager to void a check transaction? My original integration brought in checks twice and I need to void the 2nd ones. Oops.

    Like

    • Hi Brynne,

      I am not aware of a way to bring in a void check using IM. The only thing I can think of is to import increase adjustments to ‘reverse’ the checks, but that still leaves the original checks in there, which may or may not be an issue. And still has to reconciled. Did you have ‘Duplicate Check Numbers’ allowed on the checkbook setup? I am just wondering if that would have prevented it…doesn’t help with this situation, but maybe for the future?

      -Victoria

      Like

  24. Hi Victoria,

    Quick question: Are you aware of any product that will allow bank transactions to be uploaded into GP? We are looking to automate the process of entering the daily bank deposits since we have many transactions and many accounts. I know there are E-Bank Rec products out there, but bank recs aren’t an issue for us when it comes to time spent, just the entering of transactions.

    Thanks and regards,

    Gordon

    Like

    • Hi Gordon,

      Both Integration Manager and eConnect can import Bank Transactions.

      -Victoria

      Like

      • Thanks Victoria.

        We have Integration Manager, but not eConnect. I had no idea IM could be used for this.

        Are there any templates/step by step instructions/websites that would show me how to do this?

        Thanks,

        Gordon

        Like

        • Gordon,

          There should be a sample integration for this that gets installed with Integration Manager. For anything more, I would recommend getting some help from your GP partner. Bank transaction imports are a little different from others – as there are no batches in Bank Rec, they get posted right away, so you may want to do some testing first.

          -Victoria

          Like

  25. Hi Victoria

    I was wondering If you had a view that could show me the sales tax paid on invoices shipped in a certain state. Specifcally Ohio.

    Thanks
    Vic

    Like

  26. Hi Victoria

    Happy 2011 and congrats on your 7th year as an MVP.

    Unfortunately, for me our 1st bank reconciliation is giving me the blues. I have this h-u-g-e difference after (1) entering my bank statement ending balance (2) entering my cut off date (3) ticking all my items that reconcile to my bank statement (4) entering my adjustments.

    I cannot figure out how GP is calculating this h-u-g-e difference so I need to find a solution to make it zero so that I can reconcile the bank account. My questions are firstly, what are the correct steps for doing a bank reconciliaiton for the 1st time & secondly, the only way I can think of to get rid of my difference is to post an IAJ or DAJ. What do you think? Will this transaction “bite” me later?

    Like

    • Lulu,

      It’s sometimes difficult to help with bank rec questions without seeing it. Sometimes the difference can be handled by entering a different ‘Last Reconciled Balance’ on the Checkbook Maintenance window. Other times an adjustment is needed for the very first bank rec. If you are confident that all your numbers are correct and need to simply wipe out the difference to be able to reconcile, you can enter an increase or decrease adjustment, as you’re suggesting, with a debit and credit both going to the Cash account for the checkbook. Can it “bite” you later? Possibly. If something was incorrect you will most likely need an additional adjustment on the next bank rec. Also, if the amount of your adjustment is very large, your auditors may flag it and you will have to explain why it was needed. You should enter a note as an explanation anyway, so this is not a mystery to anyone looking up the transaction later.

      -Victoria

      Like

      • Hello again,

        I’ve triend entering the last reconciled balance for the previous month in the Checkbook maintenance window but it didnt help, it just made my difference bigger. If only I can figure out how GP is calculating this difference and storing the values that appear in the Adjustments window! I think my adjustment entry is my only solution in this case.

        Thanks for your help as always

        Like

  27. Victoria,

    Thanks for the reply. I did see that but did not see my comment here so I didn’t update. I think I was looking at the bottom of the page. I was basically trying to cheat and mark all the differences from a test company (restored from a “bad” version of live) to live. I decided to just clear them manually. :)

    Like

  28. Hi,

    Any idea where the Cleared Difference done during a bank reconciliation are stored? I think I’ve looked in every CM table!

    Thanks!

    Like

    • Stefanie,

      I don’t believe the cleared ‘difference’ is actually stored anywhere… what I do see is the cleared amount (ClrdAmt) in CM20200 that is different from the transaction amount (TRXAMNT) for transactions that were cleared for a different amount.

      -Victoria

      Like

  29. Hi Victoria,

    I am having an issue with an existing Crystal Report I have created that pulls data from the Cash Management tables. Everything seemed to be working fine until a recent snag. Basically what I need this report to do is report CM transactions, both inflows and outflows, which it does. We have multiple companies, so I created the SQL View and everything was working fine until I added new bank accounts. What I now need the report to do is report CM transacations grouped by company and then by bank account (Operating and Deposit – CHKBKID). I get the Operating account info displayed just fine, no issues. However, since I added these new Deposit accounts, those accounts do NOT get displayed on the report and for the life of me I cannot figure out why.

    Hopefully I have provided enough info for you to come up with some solutions.

    Thanks and regards,

    Gordon

    Like

    • Gordon,

      Since the report is working correctly for one of the accounts, if I had to guess without looking at it, I would say that you have a restriction for that one account somewhere. If you are using a SQL view or stored procedure, it could be there. Or, it may be in the Crystal report itself – maybe in the Record Selection Formula?

      -Victoria

      Like

      • Hi Victoria,

        Thanks for the prompt reply and that was my initial thought too. I will go back and double check that, but I didn’t notice anything the first time through. Very strange. I’ll let you know.

        Thanks,

        Gordon

        Like

        • Hi Victoria,

          I have pasted below the SQL View that I am using (well, one segment of it. It is copied to include all 10 of our companies.) As you can see, there are no restrictions in it. So, it must be at the report level. I just haven’t been able to locate the issue yet.

          Thanks,

          Gordon

          SELECT ‘SMP’ AS Company, [Cash Management Trx].CHEKBKID, [Cash Management Trx].CMTrxNum, [Cash Management Trx].CMTrxType,
          [Cash Management Trx].TRXDATE, [Cash Management Trx].TRXAMNT, [Cash Management Trx].paidtorcvdfrom, [Cash Management Trx].VOIDED,
          [Reconciliation Headers].CUTOFFBAL, [Reconciliation Headers].CUTOFDAT
          FROM SMP.dbo.CM20200 [Cash Management Trx] INNER JOIN
          SMP.dbo.CM20500 [Reconciliation Headers] ON [Cash Management Trx].CHEKBKID = [Reconciliation Headers].CHEKBKID

          Like

          • Gordon,

            You’re using an INNER JOIN to link the 2 tables in your code, which means that the checkbook ID has to be in both tables to make it onto the report. CM20500 will only include a checkbook after at least one Bank Reconciliation has been run for it, so it would make sense that newly created checkbooks would not be in there yet. Try changing this to a LEFT OUTER JOIN.

            -Victoria

            Like

  30. Hi
    Is there a way I can pull the daily or monthly checkbook balance into a crystal report? thanks!

    Like

  31. Hi Victoria,
    I was wondering if you’ve ever experienced getting stuck in the Bank Transfer window? This usually happens when we do intercompany transfers and now we’re left with several batches to be posted to our G/L that cannot be posted because they cannot be recovered. Do you have any suggestions on how to get rid of these “stuck” batches? We’ve moved all the transactions into other batches that could be posted so they’re basically empty batches. Thanks!

    Like

    • Kathryn,

      I have not heard of anyone getting stuck in a Bank Transfer window before. Do you have a customization or a 3rd party product that is allowing you to do intercompany Bank Transfers? That is not part of the out-of-the-box GP functionality and I am wondering if that is possibly what’s causing the issues you are experiencing.

      If you have GL transactions that cannot be posted and are not needed anymore because you have manually re-entered them, you should be able to remove them directly from the tables. However, I hesitate to simply post advice for doing that, because it is very easy to make a mistake and wipe out critical data. This is something that should be done very carefully by someone that understands SQL Server and the GP table structure well.

      -Victoria

      Like

      • Hi Victoria,

        Thanks for the input. We do have a 3rd party product that allows us to do Advanced Intercompany Transactions and it probably is this product that is causing us trouble. No worries, we’ll deal with removing the batches when we do the upgrage to GP 10 with our consultants.

        Thanks! K.

        Like

      • Looks like a record is held in CB200006 table – I also haven’t heard about this message until I had to unlock an end user…

        Like

        • Hi E-zroda,

          In my default US install of Dynamics GP I do not see any tables starting with CB. Not sure if that is part of a non-US module or a 3rd party product…?

          -Victoria

          Like

          • I believe this module in pre-GP10 was called European Cashbook Bank Management (CBM). From what I’ve heard about it, it has been bought by MBS and integrated in the core offering.. At the moment gets installed when you tick Bank Management on a standard UK install. It is not considered a third party add-on anymore, but the full integration is still not a reality (e.g. there are two separate Chequebook Master Tables – one prefixed CB and one CM… go figure…) It is great fun though – especially when there is a case of data corruption.
            Thank you for the prompt reply , by the way – what time it is at your end? :)

            Like

  32. Hi Vistoria
    I am using GP 10, I have noticed, whenever a CM deposit is made and posted for AR Cash Receipts, Received From information does not get update field ‘paidtorcvdfrom’ in CM20200.

    Is this a bug or am i missing something

    Would appreciate any help on this

    Thanks and regards

    Like

    • Shan,

      Since a deposit is typically a grouping of multiple cash receipts, it is logical that the deposit itself would not have a Paid To / Received From entry. The individual cash receipts associated with each deposit would have the Paid To / Received From information. I would not consider this a bug nor a flaw in design.

      -Victoria

      Like

  33. Thank you Victoria for getting back to me so quickly.
    I was afraid that was going to be your answer, but I think just entering a correcting JE will do the trick as well.

    Again, I want to express how much I appreciate your time in sharing your knowledge with the rest of us.

    Thanks!
    Stephanie

    Like

  34. Hi Victoria,

    Is it possible to “undo” a bank reconciliation?
    There is a situation where posting was set to post to on bank trx entry, and the entry was done in Aug but the GL batch was posted until September. The checkbook reconciliation was done for August and the receipts were deposited and reconciled…and it wasn’t until October that it was discovered the GL for September did not match the bank rec due to the posting differences. Is there any way to go back in and undo the bank reconciliation for August, so that the receipts could be voided in August and re-entered and posted to the GL in the correct month, or is there a better way to handle this?

    Any help is greatly appreciated.
    And thank you for all your great posts and information, I utilize your website all the time, as it is so helpful!
    Thanks,
    Stephanie

    Like

    • Hi Stephanie,

      There is no way to undo a Bank Reconciliation. If it just happened and you needed to undo it, possibly restoring from a backup would be an option, but most likely this was done a while ago, so it is too late now. If this is just a timing issue, ie, it catches up in September, and this is not your year end, many companies will find it sufficient to simply add a note to their reconciliation files explaining this. Another option is entering a GL adjustment and reversing it the following month to ‘correct’ for this.

      -Victoria

      Like

    • Hi Victoria,

      I have this error “There was an error posting to the CBM Bank Reconciliation”.

      This error appeared after my computer hanged up during voiding of payment in the Payable Module.

      Please help me on this.

      Thanks.

      Like

  35. Victoria,

    Thanks so much for posting this information and making it so readily available!

    Can you tell me how to relate CM information to the GL20000 table? Specifically, I’m identifying document types (ORTRXTYP) based on their series (ex: Series = 3, ORTRXTYP =1 is a ‘Sales Invoice’); how can I provide a user-friendly name for CM transactions entered through the Bank rec module?

    Like

    • Rick,

      Looking at even a small subset of data, I don’t see how you can use the ORTRXTYP for Bank Rec transactions. I have not done any testing on this, but I would probably start by linking the GL20000 to CM20100 using:
      GL20000.ORTRXSRC = CM20100.AUDITTRAIL
      and GL20000.ORCTRNUM = CAST(CM20100.RecNumControl as char)

      Then you can use the list of values for CMTrxType above to get the friendly name for the transaction type.

      -Victoria

      Like

  36. Victoria,

    Yeah, I totally understand that it’d have to be a webex session. I have tried a couple alternatives and also just posted an entry on newsgroup.

    I’m going to open a Support case with Microsoft, hopefully we could get it clear out.

    Thanks so much for your suggestions.

    Warm Regards,
    Nikki

    Like

  37. Victoria,

    Still no luck with previous bank deposits. =(

    Like

    • Nikki,

      Sorry, this is possibly one of those situations that someone would have to take a look at your system and the tables for. Pretty difficult to figure out what the issue is without seeing it. :-( Did you try posting this on the GP newsgroup? Maybe someone else will have some more ideas for you to try?

      -Victoria

      Like

  38. Hi Victoria,

    – Yes.
    – Yes, it is definitely an order.
    – I’ll definitely look into this. Will keep you posted.

    Thanks so much for your help.

    ~Nikki

    Like

  39. Hi Victoria,

    Thanks for getting back to me so quickly. The credit card is setup to go to a checkbook ID.

    Like

    • Nikki,

      A few other things to check:
      – Has the person who entered the deposit closed the Sales Transaction Entry window?
      – This is definitely an order, not an invoice? On an invoice, the deposit will only be posted once the invoice is posted.
      – Have you looked at previous Bank Deposits to make sure it has not already been deposited?

      -Victoria

      Like

  40. Hi Victoria,

    We have a customer who has an Order with a credit card deposit tied to it. However, when we go into Bank Deposit, we couldn’t find the deposit for that particular Order.

    Could you please shed some lights on the possibilities this could happen?

    Like

    • Nikki,

      How is the credit card set up? Is it set to go to a checkbook or a GL account? If it’s set to go to a GL account, you won’t see the receipt to be deposited until you enter a receipt Bank Transaction.

      -Victoria

      Like

  41. Thankyou very much Victoria,

    You are the best!

    Like

  42. Hi, Victoria,

    I am trying to copy all my current company file to a testing company file to test bank reconciliation feature(version 9.0). Should I use intergration manager to import all the transactions to a testing company file? Would you please give me some insight?

    Thank you very much!

    Like

  43. Hi Victoria,

    I am looking to resolve an issue regarding an EFT Error message. “Another User is Already Generating EFT Files”.

    I went through knowledge base, and discovered I can clear the EFT Activity Table – ME234601 (ME_EFT_Generation_Activity), however this table does not exist in GP10??

    Do you know what the table is in GP10, so that I can resolve this issue?

    Thank you!

    Like

    • Hi J.,

      Sorry, I am not seeing that table anywhere. And while there is a list of changes from GP 9.0 to GP 10.0 in the SDK with a bunch of ME tables on it, there is nothing for this particular table. Have you tried the new Microsoft Partner forums to see if you can get an answer on this?

      -Victoria

      Like

  44. Thanks Victoria. That is exactly what I needed
    Seth

    Like

  45. Victoria,

    I am trying to link an accounts payable check to all of the voucher numbers that the check paid in a sql query. Can you point me in the right direction for the CM tables linked to the correct PM tables to do this?

    Thanks,
    Seth

    Like

  46. Hey! Thanks for the quick response! I am trying to link RM00101.UserDef1 to actual Cash Deposits. Possibly CM20300.Checkbook_Amount.

    We have both Residential and Commercial customers and would like to differentiate between the two for cash collections. Currently the UserDef1 field contains the information that can distinguish them apart. Thanks for all your help!

    – William

    Like

    • William,

      It sounds like you first have to link from CM20300 the to the cash receipt transaction (either in RM20101 or RM30101) using the logic in my previous comment. Then you will have the Customer ID in RM20101 or RM30101. Once you have that, you can link on the Customer ID to RM00101 and pull in the user defined value. Hope that helps.

      -Victoria

      Like

  47. William,

    What specifically are you trying to link? Cash receipts? It looks like depending on where the cash receipt came from you would need to add some different logic. I have not fully tested this, but try something like this:
    RM20101.DOCNUMBR = CM20300.SRCDOCNUM
    or RM20101.DOCNUMBR = CM20300.RCPTNMBR

    -Victoria

    Like

  48. Hi Victoria!

    Do you know the easiest way to link the CM tables to the RM tables? I am having trouble finding the correct keys. Thanks!

    – William

    Like

  49. Hi Victoria,

    Is it possible to generate a daily cash position report in GP or does it have to be done in Crystal? Thanks.

    herson

    Like

    • Herson,
      In versions 9.0 and 10.0 of GP there is a Checkbook Balance Inquiry window (Inquiry > Financial > Checkbook Balance) that will show you the balance as of a specified date. This did not exist in older versions of GP. Let me know if that’s not what you’re looking for.
      -Victoria

      Like

Trackbacks/Pingbacks

  1. Dynamics GP Bank Rec table information - Victoria Yudin - January 16, 2009

    [...] GP Bank Rec table information A new page is now available with table information for the Bank Reconciliation module.  I have reorganized all the pages a little so they are now alphabetized on the sidebar to the [...]

    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

Follow

Get every new post delivered to your Inbox.

Join 1,476 other followers

%d bloggers like this: