RM Tables


Commonly Used Tables:
RM00101 – Customer Master
RM00102 – Customer Addresses
RM00103 – Customer Master Summary
RM00104 – Customer Period Summary
RM00106 – Statement Email Addresses
RM00201 – Customer Class Master
RM00301 – Salesperson Master
RM00303 – Sales Territory Master
RM00401 – RM Key File
RM10101 – Distribution Work and Open
RM10201 – Work/Unposted Cash Receipts
RM10301 – Work/Unposted Sales Transactions
RM10501 – Commissions Work
RM20101 – Open Transactions
RM20201 – Open Transactions Apply
RM30101 – Historical Transactions
RM30201 – Historical Transactions Apply
RM30301 – Distribution History
RM30501 – Commissions History
MC020102 – Multicurrency Receivables Transactions

RMDTYPAL – RM Document Types:
0 – Reserved for balance carried forward records
1 – Sale / Invoice
2 – Reserved for scheduled payments
3 – Debit Memo
4 – Finance Charge
5 – Service / Repair
6 – Warranty
7 – Credit Memo
8 – Return
9 – Payment

VOIDSTTS – Void Status in RM20101 and RM30101:
0 – Not voided
1 – Voided
2 – NSF check
3 – Waived finance charge

CSHRCTYP – Cash Receipt Type in RM20101 and RM30101:
0 – Check
1 – Cash
2 – Credit Card

BACHFREQ – Batch Frequency:
1 – Single Use
2 – Weekly
3 – Biweekly
4 – Semimonthly
5 – Monthly
6 – Bimonthly
7 – Quarterly
8 – Miscellaneous

HISTTYPE – History Type in RM00104:
0 – Calendar
1 – Fiscal

DCSTATUS – Document Status in RM00401:
0 – Reserved
1 – Work
2 – Open
3 – History

DISTTYPE – Distribution Type:
1 – Cash  (CASH)
2 – Terms Taken  (TAKEN)
3 – Accounts Receivable  (RECV)
4 – Writeoffs  (WRITE)
5 – Terms Available  (AVAIL)
6 – GST  (GST)
7 – PPS  (WH)
8 – Other  (OTHER)
9 – Sales  (SALES)
10 – Trade  (TRADE)
11 – Frieght  (FREIGHT)
12 – Miscellaneous  (MISC)
13 – Taxes  (TAXES)
14 – COGS  (COGS)
15 – Inventory  (INV)
16 – Finance Charges  (FNCHG)
17 – Returns  (RETURNS)
18 – Debit Memo  (DRMEMO)
19 – Credit Memo  (CRMEMO)
20 – Service  (SERVICE)
21 – Warranty Expense  (WARREXP)
22 – Warranty Sales  (WARRSLS)
23 – Commissions Expense  (COMMEXP)
24 – Commissions Payable  (COMMPAY)
25 – Unit Account  (UNIT)
26 – Rounding  (ROUND)
27 – Realized Gain  (RZGAIN)
28 – Realized Loss  (RZLOSS)
29 – Unrealized Gain  (URZGAIN)
30 – Unrealized Loss  (URZLOSS)

BCHSOURC – Batch Source:
RM_SALES – RM Transaction Entry, posted in batch
RM_CASH – RM Cash Receipts, posted in batch
XRM_SALES – RM Transaction Entry, posted w/o batch
XRM_CASH – RM Cash Receipts, posted w/o batch
Sales Entry – from SOP posting
Blank – could be created by auto-posted transactions, like Debit Memos created by NFR entry

MXWOFTYP – Maximum Writeoff Type:
0 – Not Allowed
1 – Unlimited
2 – Amount
[Note: If MXWOFTYP = 2 then MXWROFAM contains the amount of the writeoff allowed, otherwise MXWROFAM is zero]

CRLMTTYP - Credit Limit Type:
0 – No Credit
1 – Unlimited
2 – Amount
[Thanks to Curtman for these]

Email_Type in RM00106:

1 – To
2 – Cc
3 – Bcc


Last Updated: December 15, 2013

347 Responses to “RM Tables”

  1. Victoria,
    Working on report for new customer growth by month for each of our offices, can the CREATDDT field from the Customer Master RM00101?

    Thanks,
    Raul

    Like

    • Hi Raul,

      This may depend on how customers are created. For example, if they are always manually entered by users, then yes. If they are imported, you might need to double check the import populates that date correctly.

      -Victoria

      Like

  2. great helfull

    Like

  3. Hi Victoria,

    I just tried to print Receivables Statement, but my report still gave me $0.00 transactions even after selecting in printing options the “Exclude Fully Applied Payments”. It should not show the fully paid payments right?

    I also tried to use combination of: No Activity, Zero Balance Due, Exclude Fully Applied Payments but gave me “No records”.

    Do you know can I not show the $0.00 transactions?

    Thanks in advance!

    Like

    • Rotchine,

      Unfortunately, I think the only way to do this would be to move all fully paid transactions to history. This is just one of the many reasons I am not crazy about the statement printing functionality in out-of-the-box GP.

      If you’re looking for something more functional and user friendly, consider GP Reports Viewer – easily emailing and printing statements is just one of the features.

      -Victoria

      Like

      • Hi Victoria,

        Thanks for that! Did you mean, to move all fully paid transactions to history by using ROUTINE> SALES> PAID TRANSACTION REMOVAL?

        But my only option for now is to use default GP report writer for some customizations…. :(

        Like

        • Rotchine,

          Yes, if you run the Paid Transaction Removal routine, the fully paid transactions should come off the GP statement report.

          -Victoria

          Like

          • Victoria,

            Once I’ve done running the Paid Transaction Removal, does it will affect any latest or unpaid transactions?

            Thanks,

            Rotchine

            Like

            • Rotchine,

              Paid Transaction Removal moves fully applied transactions from open to history tables, so it will not touch unpaid transactions. I am not sure what you mean by ‘latest’ transactions?

              If you are not sure of something, it is always best to test in a test company before doing anything with your live data.

              -Victoria

              Like

  4. Veronica-

    Our GP Vendor did the upgrade in Jan and are aware of the problem. They are stumped as well. Right now the Aging report is off- the trial balance matches what we had before the upgrade however the aging is now different. No one knows what happened- our technician said he would look into it but so far it remains a mystery. We checked all the other accounts on the TB sheet. Looks like the only problem is linked to AR- AP is still correct in both categories. Inventory was correct. So we have no idea if the records were partially corrupted and the upgrade caused them to go daffy. I was hoping you might have an idea- I love the website. It is awesome.

    Like

    • Hi Marybeth,

      Yes, it does sound like there is possibly ‘bad’ data in the AR tables. However, it’s very difficult to suggest anything without being able to look at the data. Have you already run check links on the sales series? And Reconcile?

      If so, based on what you already found, I would probably start by doing a search for any records in the apply tables that do not actually exist. See if running this in SQL against your company database returns anything:

      select
      a.*, t.*
      from
      (select APFRDCTY, APFRDCNM from RM20201
      union
      select APFRDCTY, APFRDCNM from RM30201) a
      left outer join
      (select RMDTYPAL, DOCNUMBR from RM20101
      union
      select RMDTYPAL, DOCNUMBR from RM30101
      union
      select RMDTYPAL, DOCNUMBR from RM10201) t
      on a.APFRDCNM = t.DOCNUMBR
      and a.APFRDCTY = t.RMDTYPAL
      where APFRDCTY is null or APFRDCNM is null
      or RMDTYPAL is null or DOCNUMBR is null

      -Victoria

      Like

  5. Hello,
    Is there any column that will allow me to detect if a customer record has been modified in a timeframe smaller than the day tmestamp in DEX_ROW_TS ? Thanks Stephen

    Like

    • Stephen,

      No, not out of the box.

      -Victoria

      Like

      • Hello,
        on closer inspection it looks like DEX_ROW_TS is actually filled with a full timestamp such as: 2014-03-17 20:24:48.237.
        One idea we are formulating is a periodic scan sweeping through RM00101 picking out all modified records since the last scan. We can then update a secondary system with demographic information.

        Stephen

        Like

        • Sorry, I misread your question before. What is it exactly that you are trying to accomplish?

          -Victoria

          Like

          • Hello,
            I am using GP native web services to push customer creates and updates into GP13. We are also looking at a bi-directional flow back into the originating system.

            Stephen

            Like

            • Hi Stephen,

              Thanks for the additional detail. The problem with using the DEX_ROW_TS is that you might get a lot of false positives. If someone simply looks at a record and clicks ‘save’, the DEX_ROW_TS will update even though they might not have changed anything. If that’s ok, then no problem. If you want to avoid that, you can create triggers on the RM00101 and RM00102 tables for inserts and updates (or use the eConnect Requester) to track only real changes.

              -Victoria

              Like

  6. Certainly- In the Financial Module I run a summary trial balance report to get the number for AR for a specific period. From there I go back into Sales, Select ALL REPORTS and then run a Historical Trial Balance Report for the same period. Before the upgrade all of the months of 2013 were closed and balanced. Now they are off starting in June of 2013. I was told to run a report to help locate the errors. Microsoft GP-Tools- Routines-Financial -Reconcile to GL. Because 2013 is closed and financials were completed I was asked to locate the errors and repair them in 2014. And right now I am going through each client file in the Aged AR to locate the problem.

    Like

    • Marybeth,

      Thanks for the additional detail. So the same report printed after the upgrade is not the same as it was printed before the upgrade? This gets me a little worried about the rest of the data after the upgrade. Which report is different? The GL trial balance or the AR historical trial balance?

      I agree with not going back to 2013 to fix anything. The problem is I don’t think I can help in a few blog comments – you really need to understand why there is a difference after the upgrade. Is the report different/wrong? Did the data change? Something else? Who did the upgrade? Was it done internally, or did your GP partner do it for you? Have you talked to them about this?

      -Victoria

      Like

  7. Victoria-

    We just upgraded GP to 12.00.1.1482. Before the upgrade AR Trial Balance and AR Aging matched. After the upgrade they did not. I first tried the the reconcile to GL function, it took me back 8 months and includes periods that were closed at the time of the upgrade. After doing quite some digging I found a payment that was posted to our customer where the payment shows as HIST but the invoice is OPEN. When I highlight the invoice and click Applied payments I can see the payment on the screen. If I highlight that payment and click document number I get the error that the payment doesn’t exist. Any ideas how to fix this? Or even a simpler way to identify what accounts no longer match as it took me quite a bit of time to uncover this one?

    Thank you

    Like

    • Hi Marybeth,

      When you say you’re comparing the “AR Trial Balance” to the “AR Aging” – can you be more specific? Usually the AR Trial Balance is the same as the AR Aging. Can you please let me know specifically what reports you are running, or where you are looking?

      -Victoria

      Like

  8. Carleesha Silvera Reply March 11, 2014 at 11:02 am

    Good day Victoria,

    Seeking your very useful advice once more.
    A staff member accidentally deleted a customer record, is there anyway to restore the deleted customer record without doing a complete restore of our database?

    Like

    • Carleesha,

      You should be able to manually re-enter the customer record.

      -Victoria

      Like

      • Carleesha Silvera Reply March 11, 2014 at 1:08 pm

        Hi Victoria, I am such a nob, I forgot to tell you that the customer currently has sales history in our database. Will re-creating the customer record automatically link the record to the sales history?

        Like

        • Carleesha,

          I am curious how GP allowed the customer to be deleted if there are transactions for it. Was it done in SQL? In any case, the only way to find out if this will work would be to try it. You don’t have anything to loose at this point. I would run check links and reconcile afterwards.

          -Victoria

          Like

          • Carleesha Silvera Reply March 11, 2014 at 1:23 pm

            We were wondering the same thing but we had no one working in SQL at that point We will definitely try your suggestion though and keep our fingers crossed.

            Thank you Victoria.

            Like

          • Carleesha Silvera Reply March 12, 2014 at 4:15 pm

            Hi Victoria, the recommendation worked along with manually removing some empty records from a few RM tables that were causing the reconcile to fail.

            Like

  9. Hi Victoria,

    Congrats on the MVP status again, you surely earn and deserve it. I am working on a real-time integration between Oracle and GP 2013. We are using e-connect to pull in Sales/Invoices, Debit memos and Credit memos (RMDTYPAL 1,3,7) from Oracle into GP RM. I understand that GP does not allow me to auto-post these Docs via e-connect. However I do need all of these to auto-post so that the customer balances can be updated back in Oracle in real-time along with other balance changes. This ensures the Oracle Max credit limits are not exceeded for Customer Orders in Oracle. Can this be accomplished through Setup>Posting settings for a 3 doc types mentioned? Or do I need to consider a Dexterity SDK background application, or possibly a 3rd party autoPost.dll? I’m really looking for the shortest path to victory here. Thanks!

    Like

  10. I’m using excel to create a report which can search by item in order to pull up all SOPs with that item on them, and also return customer name and email information. I think I’ll need columns for item number, SOP number, Customer Name and Email (user def 1 in RM00102 is where we keep that), I’m just not 100% sure which to use so any help would be greatly appreciated. Thanks!

    Like

    • Hi Tom,

      Could you please be a bit more specific in what you need help with? I am just not quite sure what you’re asking. :-)

      -Victoria

      Like

      • Thanks for the reply Victoria. I’m asking which tables to use given the query I’m trying to create.using the fields I indicated. I’d attach screenshot but I don’t think I can here. Thanks again.

        Like

        • Tom,

          Sounds like you need SOP10100 and SOP10200 for the order information – these will actually have everything you’ve listed except the email address. The table to check for that will depend directly on where you are storing the email address. If you’re actually using the Customer User Defined 1 field for that, it will be in table RM00101. However, that’s limited to 20 characters and is typically too short to store email addresses.

          -Victoria

          Like

    • It sounds like you need to do this in SQL, then pull it into excel if its necessary. To pull all this into excel will get really ugly – you’d have to bring in so many different fields and then do V-lookups. You’re better off creating a SQL view, pulling that into Smartlist Builder or Excel Builder and working from there.

      Like

  11. Victoria, as always you’re blog has been an incredible help, however this one has got me stumpted. It’s my understanding that the “Average days to pay” in GP is displaying the number of days from the document date. Our customers can have varying terms based upon the order type, so I while 45 days may be late for 1 order, it could be early for another one. I’m trying to create a view that shows me the average days late (or early) based on the due date. I can’t see to figure out how to identify when the document is paid in full. Do you have any suggestions? From there I can see trends (slowing, increasing) and look at different time frames.

    Thanks in advance for any suggestions.

    Like

  12. Victoria love the site and appreciate all the help you give us “mere mortals” I have a question regarding credit card fees.is it possible to charge a transaction fee if a customer pays by credit card. I am a wholesaler and with fees being charged at up to 2.5% per transaction it can take out a big chunk of GP Margin. I am using dynamics gp 2010

    Like

    • Hi Mark,

      I don’t think this is possible in out of the box GP. It would need to be a customization or entering a ‘debit memo’ in addition to the invoice…which is a little messy and would need to be calculated manually. :-(

      -Victoria

      Like

  13. Hi Victoria – I hope this is a quick question. I have a RM payment that was entered with a document date of 1/10/4111. We can’t apply or void the payment. How can I can change the document date? Is there a SQL script available? Can we open up the year 4111 in GP and then void?
    Thank you!
    Bob

    Like

  14. Victoria,

    We have an issue with someone posting documents in the sales -> “transaction entry” window (not “sales transaction entry”) individually and immediately getting an error stating that her batch is in batch recovery. The documents make it to RM20101. She’s not posting it in a batch, it’s inserting a record with her user name in SY00500 (which i understand is normal), and after posting, the bachsttus becomes an 11. The record shows 0 documents in the batch (since this isn’t really a batch) and the batch number doesn’t show up in batch recovery. Then when she tries to open the sales transaction entry window she gets a transaction level posting error (“your transaction hasn’t finished processing”, etc). I have to delete that SY00500 record for her to continue working, only for it to happen again every single time. None of our other users are experiencing this iseus. We haven’t tried adding to a batch a posting the batch because she’s never had to do that. Any suggestions? Security mirrors other in her position.

    Thanks in advance.

    Like

    • Jim,

      To quickly rule out a security issue, try this:

      1. Have the user log out of GP
      2. Add the POWERUSER security role to the user
      3. Have them log back in and post a transaction

      If this works without the error, then you know it’s a GP permissions issue.

      -Victoria

      Like

  15. Victoria,

    I’m trying to write a query that only returns the current customer balance where the balance has changed since the day before. I was thinking I could use the LSTTRXDT from the RM00103 table. Do you know if the LSTTRXDT includes all transaction types that would affect the CUSTBLNC? Thanks

    - Rob

    Like

    • Hi Rob,

      I don’t have source code to be able to tell you thins definitively, but my educated guess would be that LSTTRXDT would only include actual transactions posted. So if you had a situation where you applied 2 existing transactions and took a discount or writeoff, which would change the customer’s balance without a new transaction being posted, that would not be captured in the LSTTRXDT.

      -Victoria

      Like

  16. Hi Victoria,
    I’m having challenges on how to generate customer balances trend on GP.

    i want to be able to generate a report that will compare each customer balance last month to this month, then look at quarterly, bi-annually and yearly. look at the sample of report below.

    Customer Name Balance Jan Balance Feb Balance March

    Customer A xxxxxxx XXXX xxxx

    Customer B xxxx xxxxx xxxxx

    Customer C xxxxxx xxxxx xxxxxxx

    The report will help me to check if my collection or Receivable manager is working or not

    Rgds

    Seafetcher

    Like

    • Hi Seafetcher,

      If I understand correctly, what you’re asking for would require a separate monthly historical trial balance for each customer. GP does not store this, so each would have to calculated. While that is certainly possible, it’s pretty complicated and probably not something I would post on the blog. If this is something that you’re interested in having created for you as a consulting project, please let me know.

      -Victoria

      Like

  17. Hi Victoria,

    It’s been a few years, but I’m back again with a few new questions. I’m updating our statement form (RM Statement Long Form) in ReportWriter. We print on a custom form, and the default document numbering for some document types is too wide for the column in which we want the value to display. For instance, our credit memos are of the format: CREDT000000000001. We will never come close to going that high, so we can safely remove a bunch of zeros to create shorter numbers. I know where to change this going forward, but that won’t help for existing documents. Is it safe to update document numbers, and if so, what is the best way to determine the tables that need to be updated? I have some familiarity with the tables, but want to make sure that if we do this, we don’t break anything. Our invoices and returns are okay (they use a prefix and six digit numbering), so we’re looking at doing this for credit memos, debit memos, and payments (the only three document types we use on the Receivables Setup Options screen). Can you tell me which tables would be affected for these three document types? I think RM20101, RM20201 (and their historical counterparts), but are there others? And after making the change, what would be the recommended procedure – could/should we run Check Links?

    As usual, thank you for the wonderful information here and the time you give to help us navigate GP!

    John

    Like

    • Hi John,

      I would strongly encourage you not to do this. These could be in dozens of tables, and in some they won’t be called ‘DOCNUMBR’. It would be very easy to significantly compromise the integrity of your data by not doing this exactly right and in all the tables. I certainly don’t feel comfortable giving advice on how to do this in a blog post, without examining your data and having a good understanding of all the products, modules, customizations, etc. that you have in place.

      To get around the issue on your statements, you might instead consider writing some custom code to replace ’00000000′ with ’0′ or something like that, on the report only. That way it’s just a report display issue. (Of course, your users would need to know this is happening, so they are not searching for CREDT0000001 when it’s really CREDT000000000001 in the system.) I am not sure how easy it is to achieve something like this in Report Writer, but I would think this should be less work than changing the actual data.

      -Victoria

      Like

      • Thank you for your guidance – yes, it sounds like updating the document numbers is a patently bad idea, and modifying the report is a good alternative.

        Is it safe to assume that I can change the document format for new documents? So, for instance, for credit memos, the “Next Number” is currently CREDT000000000236 – I would change it to CREDT000236. So the numbers would run CREDT000000000001 to CREDT000000000235, and then CREDT000236+. No new number would have “000000″, so from a reporting end, they would all display the new format, and as you said, internally we would just need to know that below a certain number the real document number has the longer format.

        Also, ReportWriter seems to be the default system for creating reports, and since our use of GP is pretty simple, we haven’t done much in the way of reporting. My experience is that it is a cumbersome and unintuitive UI, but there are lots of built-in reports, and report deployment is fairly straightforward. I know Crystal Reports is one alternative (and I think SQL Server’s built-in reporting is another). In your experience, is ReportWriter widely used, or do many customers choose an alternative like Crystal or SQL Server reporting that provides a better environment to create reports? I realize every customer has different needs, I’m just trying to understand the landscape of how companies tend to make GP work for them – especially those on the simpler end. We’re a software company that sells services to a relatively small customer base, so we don’t deal with shipping, back orders, varying tax scenarios – it’s pretty vanilla. But we do have basic needs, like generating invoices and statements, tracking A/R, etc.

        Thanks again!

        John

        Like

        • Hi John,

          There is no problem with changing the ‘next’ numbers in GP. The only issue might be sorting – for example, if you dump all the transactions out into Excel from a SmartList, then sort by doc number, they will not sort how in the ‘right’ order. This is not usually a huge deal, but something to keep in mind. And the sooner you change the next numbers, the less time you have to deal with current transactions having the insane amount of zeros.

          As to the reporting tool question – there are pros and cons to all the options and I often see companies using a combination of the tools, not simply pick one. I personally am not a big fan of Report Writer (RW) for just the reasons you list – terrible UI, cumbersome, also some things are just not possible, or possible but with so much effort, it makes you want to strangle someone. My company makes a product called GP Reports Viewer that lets you run any Crystal and SQL Reporting Services (SSRS) reports inside of GP. So our invoices and statements are created in Crystal and look fabulous (at least we think so) and don’t have any of the limitations of the out-of-the-box reports. For statements, we like not having to run paid transaction removal before being able to print them so that paid transactions do not show up there. For invoices, we have a lot of custom logic – like hiding and showing sections based on customer class, or extender and user-defined fields. All of this is a breeze in Crystal (or SQL – most of the time we create SQL views or stored procedures to use in our reports). We also use SSRS reports for some of the reports we run that have a lot more columns, SSRS is better geared towards that than Crystal. Although another option for this would be creating reports directly in Excel – ie, having Excel point to a SQL view or stored procedure directly.

          The right approach will typically depend on a combination of your reporting needs, resources, experience and budget. For example, if you have already put a fair amount of time into learning how to use RW and are comfortable with it, and don’t have too many critical needs it cannot meet, then it is hard to justify the time to purchase and/or learn something else.

          Hope that helps. If you would like to discuss this in more detail, let me know.

          -Victoria

          Like

  18. Hi Victoria,

    I am trying to create a commission report based upon when invoices were paid. Example: October commissions will be paid on all payments received in October.

    I can see payments in receivables transactions, but I need to see which invoices were paid. What table(s) should I use?

    Thanks!

    Like

  19. Hi Victoria,
    I’m rummaging through the RM tables in search for a buyer name that i can link back to the POP tables. The way the user can view this buyer name is by opening the “Receivings Transaction Inquiry Zoom”, then opening “Receivings User-Defined Fields Inquiry” where the “Buyer Id” is said to be user defined field 3. Do you know where i should be looking?

    Thanks Victoria!

    Like

  20. Hi Victoria,

    What is the Series 4 in TX30000 table?

    Thanks,
    Anvar

    Like

  21. Hi Victoria,

    I am relatively new to GP and your site has been a great help as I continue to learn. I am trying to clean up and speed up our system and I have determined that my predecessor closed out years, but never processed purchase document reconciliation or moved purchase documents into history. We now have some 7 years of data “open” in the system.

    Could you explain in beginner’s terms the purpose of these processes and whether there are potential negative consequences for performing them now?

    If it matters, we are using GP 10.0

    Thank you in advance for any comments.

    Casey

    Like

    • Hi Casey,

      You should not have to run reconciliation utilities unless something is wrong. Moving purchase documents to history simply moves completed PO’s to a different set of tables – if you do this, you may want to first confirm that you’re keeping history (GP | Tools | Setup | Purchasing | Purchase Order Processing – there are 3 History checkboxes in the middle). The reason to do this would be to allow searching of open purchase orders only, which should greatly speed things up if you have a lot of closed PO’s. The only negative I can think of is if you have any custom reports that are only looking at the open (non-historical) data, they will now not see all of the data that you might want. It’s not very typical, but I have sen it happen.

      Hope that helps.
      -Victoria

      Like

  22. Hi Victoria,

    Have you ever seen a case where the noteindx on RM20101 transactions is 0.00000, even though there is a note attached to the transaction?

    Like

    • Hi Nysa,

      There will be many times when the NOTEINDX is 0, often it only gets a valid number when a note is created. What I have never seen is an actual note in the SY03900 table with a NOTEINDX of 0. I would think this is an issue, as you could have many objects (not just transactions, but master records) in your system with a NOTEINDX of 0 and now they will ALL point to this one note.

      -Victoria

      Like

  23. Victoria, we use the note beside the Document Number in Receivables Transaction Eqnuiry Zoom or the Receipt Number in Cash Receipts Enquiry Zoom.

    thanks

    Like

  24. Victoria, we attach comments to our RM transactions (invoices, payments etc) that help us work out what is going on with the client without using the collections module.

    Could you please tell me what table these comments reside in?

    Like

    • Hi Geraldine,

      Can you tell me exactly what field on each of the windows you are populating with your ‘comments’? There are a couple of different ones I can think of, so I want to make sure we’re both talking about the same fields.

      -Victoria

      Like

  25. I’ll try to duplicate the issue in my test environment and keep you posted. Thanks.

    Like

  26. Hi:

    We have a customer that runs Paid Transaction Removal with the wrong parameters. They know that they can unapply RM from history using the PSTL one by one, but they are having another problem with this issue and Bank Reconciliation. All the voids (Voids and NSF) that are in the History tables are not available in Bank Reconciliation Deposits to be deposited. Is this a typical GP BR behavior or we got something wrong. I look into the CM20300 table and they are there, but I look for them in the Deposits Window and they does not appear.

    Thanks for your help.

    Jesus

    Like

    • Jesus,

      When you NSF a check it does not need a separate ‘deposit’. It will show up in the Checkbook right away as a withdrawal.

      For voided checks, I cannot duplicate what you are seeing. And I am not sure how moving the voided payment to history would make it disappear from the Bank Deposits window. Can you duplicate this at will?

      -Victoria

      Like

  27. Hi Victoria, I am running a RM integration from Oracle to GP 10 via linked server and advanced ODBC data sources. I am using SQL views to the Oracle DB for Customers, RM headers, and RM details with relationships and mappings.
    I am selecting only customers NOT IN RM00101 in my view to insure I don’t bring over existing GP customers. I am selecting NOT IN RM10101, RM20101, RM30101 in my view to insure I don’t bring over DOCNUMBRs that already exist in GP. My customers and headers are coming over just fine and I’m mapping my Sales and FRT to the Receivables Transaction Screen. This is automatically creating the distributions just fine. However, I need to manually create distributions for local and sales tax. When I do so, the Sales tax distributions come over fine, but seem to override the distributions for Sales, FRT, etc … Any suggestions on how to keep the distributions created from the RM Transactions entry while also adding the Sales Tax Distributions I create in the detail file. Here is a link to a screen shot that shows the problem that I am having.
    http://www.aristotlesalexander.com/images/RM.png
    Thanks in advance for the direction!
    CSH

    Like

    • Hi Chris,

      Do you mean that you’re importing directly to SQL tables instead of using a tool like eConnect or Integration Manager? If so, that’s really not something I would recommend. Why not use one those tools and have GP populate/calculate the taxes for you automatically?

      -Victoria

      Like

      • I am using IM – RM destination.

        Like

        • Oh great! Then why not have GP calculate the taxes for you?

          -Victoria

          Like

          • Hi Victoria, once I set up the necessary tax details and schedules per customer and used those schedules in the RM IM imports, all worked as expected. I’m having some small issues with the rounding of tax. Largely because my Oracle system rounds the total of local and state where GP is rounding local and state individually … producing some occasional annoyances. I’ll work through that one. Thanks for your help on this and the value you provide to the community. I have some additional projects that may require your expertise. Please email me with your contact details when you have a few minutes. Thanks! CSH

            Like

            • Hi Chris,

              Yes, GP rounds all the taxes individually, then adds them up. If you were using the SOP module with line items on your invoices, it would be even worse, as taxes for each line would be calculated separately, then added up. One thing to try to mitigate the issue is to see what rounding settings you have on the Tax Details. I believe they usually default to rounding up, whereas I think they should be rounded to the ‘nearest decimal place’.

              If that does not help, you could possibly check for this in your code and add a Misc amount (to add) or Discount amount (to subtract) to make sure the total matches your Oracle total. This is not very pretty, but if the goal is to have the total match exactly, this is one way to do it. There may be other alternatives depending on your other requirements/needs.

              -Victoria

              Like

  28. Hi Victoria,
    This is the first time I have posted on your site but have found useful information on here many a time :) Thank you for the site :)

    I have a query which I wonder if you could help with…is it possible to see SOP transactions that have been deleted – is there a table somewhere obscure that holds this information – I am trying to trace “Missing numbers” for a client going back about 4 years!

    Any advice would be gratefully appreciated
    Many thanks
    Trace

    Like

    • Hi Tracy,

      Thank you for your kind words and for frequenting my blog!

      Unfortunately, GP does not track anything that is deleted. That’s why many companies don’t allow deleting SOP transactions and only allow voids. :-( The only alternative to this would be to create a custom SQL trigger and table to store anything that is deleted, but that would only work going forward, can’t go back with something like that.

      -Victoria

      Like

    • We need to break out tax into local and state. and Some customers only pay state not local. See query below for how i am detemrining tax from oracle before moving to GP distributions.

      /*

      TAX Breakdowns
      2750 St. Martin parish 3.5 0.466666667
      2800 state 4 0.533333333
      7.5 1
      */ SELECT IH.INVC_NUMBER AS DocNo, ’0-000-2750-000′ AS Account,
      13 AS DistType, CASE WHEN EXISTS
      (SELECT LEFT(RTRIM(LTRIM(c1.company_name)), 15)
      FROM [ORACLE11]..[QCTL].[COMPANIES] C1(nolock)
      WHERE LEFT(RTRIM(LTRIM(c.company_name)), 15) = LEFT(RTRIM(LTRIM(c1.company_name)), 15)
      GROUP BY LEFT(RTRIM(LTRIM(c1.company_name)), 15)
      HAVING COUNT(LEFT(RTRIM(LTRIM(c1.company_name)), 15)) > 1) THEN (LEFT(RTRIM(LTRIM(c.company_name)), 10)
      + ” + LEFT(RTRIM(LTRIM(C.[COMPANY_CODE])), 5)) ELSE (LEFT(RTRIM(LTRIM(c.company_name)), 15)) END AS Company_ID, C.COMPANY_NAME AS Customer_Name,
      C.COMPANY_CODE AS [Short Name], 0 AS Debit, CASE WHEN c.company_name = ‘CHEM AIR’ OR
      c.CMP_AUTO_KEY = ’6796′ THEN 0.00 WHEN IH.INVC_TYPE = ‘M’ OR
      CAST(TOTAL_PRICE AS float) ’1/1/2013′) AND (IH.post_status IN (2, 3)) AND (C.COMPANY_CODE NOT IN (’113′)) AND (IH.TOTAL_PRICE 0) AND (LEFT(IH.INVC_NUMBER, 1)
      ‘I’) AND (C.COMPANY_NAME ‘Helifab’) AND (C.COMPANY_NAME NOT LIKE ‘%GML%’) AND (IH.INVC_TYPE IN (‘I’, ‘D’, ‘M’)) AND
      (IH.INVC_NUMBER NOT IN
      (SELECT DOCNUMBR
      FROM dbo.IM_GetAllDocNums)) AND (IH.TAX_amount 0)
      UNION ALL
      SELECT IH.INVC_NUMBER AS DocNo, ’0-000-2800-000′ AS Account, 13 AS DistType, CASE WHEN EXISTS
      (SELECT LEFT(RTRIM(LTRIM(c1.company_name)), 15)
      FROM [ORACLE11]..[QCTL].[COMPANIES] C1(nolock)
      WHERE LEFT(RTRIM(LTRIM(c.company_name)), 15) = LEFT(RTRIM(LTRIM(c1.company_name)), 15)
      GROUP BY LEFT(RTRIM(LTRIM(c1.company_name)), 15)
      HAVING COUNT(LEFT(RTRIM(LTRIM(c1.company_name)), 15)) > 1) THEN (LEFT(RTRIM(LTRIM(c.company_name)), 10)
      + ” + LEFT(RTRIM(LTRIM(C.[COMPANY_CODE])), 5)) ELSE (LEFT(RTRIM(LTRIM(c.company_name)), 15)) END AS Company_ID, C.COMPANY_NAME AS Customer_Name,
      C.COMPANY_CODE AS [Short Name], 0 AS Debit, CASE /*Credit Tax for Chem Air – 100% State*/ WHEN (IH.INVC_TYPE = ‘M’ OR
      CAST(TOTAL_PRICE AS float) = 0) AND (c.company_name = ‘CHEM AIR’ OR
      c.CMP_AUTO_KEY = ’6796′) THEN (ISNULL(IH.TAX_AMOUNT, 0)) /*Credit Tax for Others – */ WHEN (IH.INVC_TYPE = ‘M’ OR
      CAST(TOTAL_PRICE AS float) < 0) AND (c.company_name ‘CHEM AIR’ AND c.CMP_AUTO_KEY ’6796′) THEN (ISNULL(IH.TAX_AMOUNT, 0) * 0.533333333)
      * – 1 /*Sales/Invoices Tax for others */ WHEN (IH.INVC_TYPE = ‘I’ OR
      CAST(TOTAL_PRICE AS float) >= 0) AND c.company_name ‘CHEM AIR’ AND c.CMP_AUTO_KEY ’6796′ THEN (ISNULL(IH.TAX_AMOUNT, 0) * 0.533333333)
      ELSE (ISNULL(IH.TAX_AMOUNT, 0) * 0.533333333) END AS Credit
      FROM ORACLE11..QCTL.COMPANIES AS C WITH (nolock) LEFT OUTER JOIN
      ORACLE11..QCTL.INVC_HEADER AS IH ON C.CMP_AUTO_KEY = IH.CMP_AUTO_KEY
      WHERE (IH.INVOICE_DATE > ’1/1/2013′) AND (IH.post_status IN (2, 3)) AND (C.COMPANY_CODE NOT IN (’113′)) AND (IH.TOTAL_PRICE 0) AND (LEFT(IH.INVC_NUMBER, 1)
      ‘I’) AND (C.COMPANY_NAME ‘Helifab’) AND (C.COMPANY_NAME NOT LIKE ‘%GML%’) AND (IH.INVC_TYPE IN (‘I’, ‘D’, ‘M’)) AND
      (IH.INVC_NUMBER NOT IN
      (SELECT DOCNUMBR
      FROM dbo.IM_GetAllDocNums)) AND (IH.TAX_amount 0)

      Like

      • I thought a bit more about what you are saying …. If my existing customers have the right tax setup and my new customers creating through IM have the right tax setup created during integration, then GP would be able to calculate the correct local and/or state tax per customer during the IM import. Is that correct? If so, would I need to go back and configure tax for each customer and can I provision that in IM for the new customers created daily during the IM import?

        Like

  29. Hi Victoria, new to your blog but I’ve found it very helpful so thank you for your hard work to keep this forum going.

    My question is about distributions at the cash receipt level. The sample scenario is that a customer has a $500 receivable and a $475 payment was received because our friend, the bank, has taken a nice little $25 off the top.

    Would it be possible to create a cash receipt that accounts for the actual cash amount received, the bank fee, and also clears the receivable in its entirety?

    As we use a 3rd party bank rec application which determines the book value directly from the GL, I’m less concerned about the processing of a traditional GP cash transaction (so that it properly hits the GP bank rec module), and more interested in the distribution and the proper clearing of the receivable.

    I was able to play around with this scenario a little and the only was I was able to do this was to create a cash receipt for the whole receivable amount, and then create two distribution lines with transaction type CASH, one being for the $475 DR Cash, and the other being for $25 DR the bank fee account.

    I’m not totally comfortable with the cash receipt amount not matching the actual cash received so do you have any other ideas as to how this can be processed?

    Thanks in advance for your insight.

    Like

    • Edmond,

      This is a pretty typical scenario when receiving payment via wires, especially internationally. Here is one way to handle this, using the numbers in your example:

      • Enter the Cash Receipt for the actual amount you received, $475.
      • When applying the cash receipt to the invoice(s), on one of the invoices use the Writeoffs field to record the $25 fee (or you can split this up across the invoices – totally up to you). If you had invoices totaling $500, this would cause all of them to now be fully applied.
      • On the Distributions window for this Cash Receipt you will see a line for a $25 debit with a Type of WRITE. You can change the account on that line to be wherever you want to record the bank fees. If this is the same account every time, you can set up a default for it under the company posting accounts. If it the specific to the customer, you can set it up for each customer individually.

      This will take care of recording the cash receipt as the amount you actually got, and will work better with the Bank Rec module if you are using it. The only negative I can see to this approach is if you are actually using Writeoffs to track ‘real’ writeoffs. Now these bank fees will be tracked together with real writeoffs and you may see over-inflated writeoff totals for these customers in your reporting. Usually this is not an issue, but just wanted to mention it.

      Hope that helps.
      -Victoria

      Like

      • Thanks very much Victoria, this helped a lot. My impression from the business is that a high percentage of short pays are in fact due to bank charges so I think we should be OK on our side.

        Edmond

        Like

  30. Sharing some info on the Credit Limit fields:
    Credit Limit Type (CrLmtTyp) – integer: 0 = No Credit, 1 = Unlimited, 2 = Amount
    Credit Limit Amount (CrLmtAmt) – currency;
    Credit Limit Period (CrLmtPer) – integer
    Credit Limit Period Amount (CrLmtPam) – currency

    Like

  31. Good afternoon, Victoria. As always – your blog is my first and foremost stop for researching issues and solving problems with our implementation. One of the ARs accidentally deleted a receivables batch that had already been applied to the wrong invoice. I was able to remove a transactions from rm20201 and from rm10201. Then I ran CheckLinks on the sales module. It told me that RM Distribution Work File: the transaction for this distribution information is missing so the distribution information was removed. And … RM Key File: The transaction for this keys record is missing. The keys record has been removed. My problem is the invoice is still not showing the whole invoice amount and the system is telling me that particular check number has already been applied. (Yes – I’m doing this in a test system first.) Can you, once again, steer me in the right direction? Thanks in advance!

    Like

    • Billie Dee,

      I would recommend running Check Links again, until you receive “No Errors” back. When there are records removed, there may be several passes of Check Links needed to remove all additional/related records. Then I would run Reconcile on the “outstanding document amounts”. You can run it just for the one customer involved to speed it up.

      -Victoria

      Like

  32. Hi Victoria,

    I’m new to GP so this question may be silly, but…

    Do the account distribution types (PURCH, SALES, RECV, etc) have any effect on the GL and financial reporting? If not what are they used for?

    Thanks in advance.

    Casey

    Like

    • Casey,

      No, the distribution types do not have any effect on the GL or financial reporting. They are used to make sure that your distributions match up to the totals on your transaction and to facilitate automatically populating the proper distributions. They are also very useful for reconciliations of sublegers to the GL in case that becomes necessary.

      Towards the bottom of this blog post I show an example of how the distribution types match up to the transaction totals.

      -Victoria

      Like

  33. Hi Victoria,

    Let me first say your blog is awesome. I’ve used it from time to time as a reference and I am very pleased with the contents. Keep up the good work!

    Now for the reason I am posting. I am facing a situation at my workplace. We have a sales invoice which failed to save in the receivables module. The information seems to be correct in the SOP tables but there are no entries in the RM tables, namely, RM20101, RM10601, RM10501, RM10101 and also the MC020102. I have tried to replicate such a case with no success. This is the second time this problem has happened and I would really like to know if you have been in this situation before or if you know what the problem could be. Any help will be appreciated.

    Like

    • Hi Joel,

      This is certainly not a known issue and I would not expect you to be able to duplicate it at will. My first step would be to make absolutely sure that the invoice is really not anywhere in RM tables. Can you check to see if it is in the RM00401 table? Also, which SOP tables is the invoice in?

      -Victoria

      Like

      • Victoria,

        The invoice exists in the RM00401 table. The SOP tables where the invoice is are: SOP30300, SOP30200, SOP10106, SOP10105, SOP10102 and SOP10101.

        Joel

        Like

        • Joel,

          What is the value in the DCSTATUS field in RM00401?

          -Victoria

          Like

          • Victoria,

            The value for that field is 2.

            Joel

            Like

            • Mi bad, the value for the field is 0.

              Joel

              Like

              • Joel,

                0 means the number was assigned in RM (which happens during the posting of the SOP invoice), but not actually posted in RM. Did something go wrong during posting? Like someone lost connection to the server, or crashed out of GP, or received an error? Have you run check links on the sales series?

                If this invoice is truly not in the RM tables, a work around may be to recreate it manually directly in RM. You would also want to check the GL to see if this transaction made its way in there properly. Past this, you may want to get further help on this from either your GP Partner or Dynamics GP Support, so they can take a look at your data.

                -Victoria

                Like

                • Victoria,

                  Many thanks for your quick responses. The user have not reported having received an error while posting, nor have any of our GP clients crashed in a long time. I have run several check links for the sales series (every sunday) but the records are not repaired.

                  As I mentioned before this is the second time this happens and I will rebuild the data manually -which worked fine the first time.

                  Again, thank you very much for taking the time to answer my questions. You are great!

                  Joel

                  Like

            • Joel,

              Well, that would seem to indicate that the invoice posted and should be in the RM20101 table. You’re positive it’s not in there? What is the TRXSORCE for this invoice in the RM00401 table? Also, what is the value of the NEGQTYSOPINV field?

              -Victoria

              Like

  34. I love your site!!! I am so glad i found it . I have been able to get some answeres to pending question I was having. So now I have a question for you. I have a stange thing happening with my cash receipts.. When I create a Cash receipt in Sales I will sometimes have a wite off for CC fee’s that I need to enter. When I enter these into the apply screen as a Write off the first thing that happens is the GL account does not pull over from the Customer Card (there is an account in the write off field under accounts for that customer). The second thing that happens if I forget to go in an enter that account in the distributions and post that receipt it will post even though there is an error (account missing). It then will create a batch on the GL side with the account missing and will have to enter it there before posting the GL batch.

    Like

    • Hi Steph,

      It doesn’t sound right that the write off account would not pull from the customer properly. I can’t remember ever seeing that before, what version and build of GP are you on? I just tested this to make sure and it’s working properly on my GP 2010 SP 2.

      If you have checked the customer setup and are confident it is correct and do not have any customizations or 3rd party products that may be causing GP to behave differently, then you might need to talk to your GP Partner or GP Support about this to see if they can help you determine what is going on.

      For the other part of your question, I have seen situations where a subledger transaction will post without all the accounts needed and then it will get stuck in the GL, since that will not allow a posting without all the accounts. However, it has been a while since I have seen that, and on my GP 2010 SP 2 I get an error when I try to post a transaction like that, so it’s possible this has been fixed with a new version or service pack you do not have. One way you can combat this, especially if the write off account is usually the same, would be to enter it globally for the company, under the Posting Accounts. That way, if the customer does not have the account set up, the global posting accounts will be used.

      -Victoria

      Like

      • I found the problem to this. Just to clarify.. We are on the latest version of GP all SP up to date.. Posting accounts were in the global setup for the write off account, and on the customer card write off account.
        Looks like what happened is a default account was entered on the customer cards, then at some point the Account was deleted(no activity so that was ok) But it left the customer card in limbo thinking that there was an account, but showed an error account not found. Even though I had added a valid account to the one customer I was working with, there were several customers that i hardly used that had this account not found error.
        What led me to look into this was while watching the SQL tables when entering a cash receipt it was trying to use a that GL account(which did not exist). It only verfied that there was an account, not that it was valid. This was all on the SQL side on the GP side the account looked blank, so didn’t know it was trying to attach an invalid account. Hence when it got to a Journal Entry it would bomb because at that point it validates the account.
        Easy fix was to go through all the classes and roll down a valid account this took all references to the non existant account and now it works fine. Until all references to that account was gone the problem did not go away. All customers with that problem had to be fixed.

        Like

      • Hi Victoria,

        I was hoping you could point me in the right direction, i need to build a cash management report for my company, basically see any and all outstanding COD payments, for Debit memos,credit memos and payments for a certain date range, i thought i would need to join the RM20101 table for the RMTDYPAL of (3,7,9) and get COD doc and docdate from the SOP30200 table, then subtract CURTRXAM from ORTRXAMT to see the outstanding amount, Am i wrong in thinking this?

        Hope to hear from you soon

        Like

        • Mark,

          I am not sure what you mean by ‘outstanding COD payments’. Can you please explain exactly what that means in your usage of GP? Also, typically debit memos are not grouped together with credit memos and payments – you might need to explain more about how you are using these to help answer your questions.

          -Victoria

          Like

          • Hi, sorry for the delay and vague question.

            In essence , i need to find all COD documents(PYMTRMID = ‘C.O.D’ on SOP30200 table)then find all unapplied doucments/amounts linking to these sop30200 docs, i think im supposed to link sop30200 docs to rm20101 docs.but this doesnt seem to be working.

            Belos is a query im using to get all unapplied amounts

            select
            rtrim(RM1.CUSTNMBR)CUSTNMBR
            ,rtrim(RM1.CUSTNAME)CUSTNAME
            ,ORTRXAMT
            ,CURTRXAM
            ,DOCDATE
            ,rtrim(DOCNUMBR)DOCNUMBR
            ,rtrim(TRXDSCRN)TRXDSCRN
            ,rtrim(CreditCollectorName)CreditCollectorName
            ,rtrim(RMDTYPAL)RMDTYPAL

            from RM20101 R2
            inner join RM00101 RM1 ON RM1.CUSTNMBR =R2.CUSTNMBR
            INNER JOIN ACT40300 A4 ON A4.CUSTNMBR = R2.CUSTNMBR
            –inner join SOP30200 S32 ON S32.SOPNUMBE = DOCNUMBR
            where –RM1.CUSTNMBR in(’1000032′,’1000042′,’1000009′)
            –AND DOCDATE >’2011-09-14′
            –AND DOCDATE <'2012-09-23'
            –and
            CURTRXAM ’0.00′
            and RMDTYPAL = ’9′ –payments only–>=7
            and VOIDSTTS=0
            –and ORTRXAMT CURTRXAM
            order by CUSTNMBR

            now i need to link this to SOP30200 COD doc.

            Does this make more sense?

            Like

            • Mark,

              Sorry, still not understanding the logic of what you’re trying to do. Your code below looks like it is getting all unapplied payments. But if a payment is unapplied, how can you find invoices it is linked (or applied) to? What logic can you use to link them?

              -Victoria

              Like

              • Hi Victoria, i finally came right, had to ask our OPs director for their logic, as i was getting nowhere, i had to look on RM20101 table for any documents with payment methods of COD OR ”, and not just COD, thank you for persevering in trying to assist me, as i dont come from a financila background, so this report killed me.

                Thank you for such a great source of information, i normally always troll your site to get information and understandings regarding tables, views, requests, etc

                Like

  35. Hello Victoria,

    We’ve recently come across a payment that did not fully apply in all the GP windows, it shows as being applied correctly everywhere except the “Sales Payment Inquiry Zoom’ window. For some reason this value is $1000.00 off from the actual payment amount. If we look at the “Sales Transaction Inquiry” window, “Receivable Transaction Inquiry” window, and even at the GL Entry it shows the correct amount.

    This is a Historical payment that was made last year and I have been through all of the RM tables and cannot find anywhere that the document is off by $1000.00, would you know where the “Sales Payment Inquiry’ gets its data from so that I can update it? Thanks!

    Like

  36. Hi Victoria

    I found an odd record in RM00103 with a blank CUSTNMBR. This entry has a customer balance and various amounts in 3 of the aging periods amount columns. I cannot locate any transactions in the system for a blank customer and I believe these amounts only seem to change after a check links is performed monthly. There is also a blank customer record in RM00101 with no created date or any details. Do you have any ideas where this summary data would be coming from? We are running GP 9.0. Thanks for any help you can give.

    Like

    • Chris,

      Unfortunately, it is very difficult to give you a definitive answer without looking at the rest of your data and knowing what has happened in the past. It could be that there was a customer record created in error with a blank Customer ID, that would typically cause a record in RM00103 to be created, but I would not expect it to have numbers in the aging columns. It’s also possible that something went wrong during an import or something was changed directly in the database to cause there to be transactions in the RM tables with no corresponding customer record in the RM00101 table. In that case, maybe Check Links is trying to account for this customer’s transactions with the records you are seeing. Have you checked RM and/or SOP transaction tables for records with a blank customer ID?

      When I have come across this in the past, we have either left it when it was not causing any problems/errors elsewhere, or deleted all records with a blank Customer ID from all the tables where we found them. If you go this route, unless you find all the tables to delete from, Check Links may bring some of these records back.

      -Victoria

      Like

  37. Hi Victoria

    I had been advised to use you site for GP related stuff and i first of all want to say its awesome :)
    I do however have a query for one of my clients. They have a debtor that is a USD customer (we are in the UK) who has settled their debt in full however they will not disappear. The originating subtotal is $0.00 but the functional subtotal has $0.01 so you cant post journals or credit/debit notes to get rid of it. Any suggestions as i am not so familiar with the Receivables side of GP?

    Like

    • Hi Laura,

      Thanks for the kind words. I have not run into this particular situation before, but have you tried using Write Off Documents (under Sales Routines) for this? If that does not help, I would recommend posting your question on the Dynamics GP Community Forum, where hopefully you will find someone that has run into (and fixed) the same issue.

      -Victoria

      Like

  38. Hi Victoria,
    I am Told to create Customer Account Statement report, Can u please tell me which all are the tables, it must include customer account no. also…

    Like

  39. Hi Victoria,

    We have accidentally clicked on NSF button for one of our customer’s cheques in the Receivables Posted Transactions screen. Now the Customer Payment Summary Inquiry screen shows that there is 1 NSF cheque present for this customer. This has now also reversed th original GL entries linked to this payment. Is there any way this can be reversed to not show as an NSF cheque.

    Like

    • Hi Zafar,

      Similar to a void, there is no way to ‘undo’ or reverse an NSF. At this point you would need to re-enter the Cash Receipt, which will update your GL accordingly. If you’re using the Bank Rec module, I believe you will also see a negative payment from the NSF waiting to be deposited, so the re-entered Cash Receipt will need to be ‘deposited’ together with the NSF/negative payment to zero that out.

      -Victoria

      Like

  40. Hi Victoria, your blog is definitely THE most helpful GP site out there!

    We have a problem with the direct debit module which I’m hoping you may be able to help with?

    At the moment, we have a number of debtors under class ID “DD” and when we build a DD batch, we ensure that select debtor by class ID is from DD to DD, and we also ensure that the automatically apply payments/credit notes/returns boxes are ticked, and once built, the amount shown for the batch on the Select Direct Debits window is exactly the amount we expect it to be.

    But, when we hit process, and the batch posts, the total batch amount (and therefore the amounts posted to the GL) changes exactly by the balance of invoices that have had a credit note applied to them automatically as part of the direct debit process, BUT the balance still applies to the payment! Difficult to explain but this might be clearer:

    For instance, debtor BEN01, which is in class ID “DD” has the following documents to be picked up on by the direct debit..

    Inv1 £50
    Inv2 £10
    Inv3 £20
    CRN1 -£20 (to be applied automatically to Inv1)

    Bal to be collected by DD £60

    So, on the select direct debits screen, before processing/posting the batch, the amount shown would correctly be £60. BUT, when posted, and we look at the debtor enquiry for BEN01, the CRN1 would be applied to Inv1, correctly leaving a balance of £30 on Inv1, but it would show a DD payment of just £30 (for just Inv2&3) but would have £60 applied to it (i.e. more has been applied to the payment than the amount the payment is actually for!)

    If I were to run the same process, but only for this one debtor (not using class ID at all), it would do the whole process 100% correctly, so I can only assume something is wrong when using the class ID, but I cannot see any settings within the class ID screens that would suggest why this is happening!

    Hope this all makes sense!

    Many many thanks for your help
    Ben

    Like

    • Hi Ben,

      Thank you for your kind words.

      I do not have any direct experience with the Direct Debits module, I do not believe this is something used in US installations which is what I typically work with. However, from what you are describing, I cannot imagine what would cause this process to work differently based on how you are selecting your customers (debtors). And there is typically no process in GP that goes back to the class settings to change functionality – class is only informational at this level, simply a field used to select the appropriate records. The only thing I can think of is that the process has an issue when more that one customer is selected. Are you able to test this by selecting 2 or more customers individually, without using the class ID? Have you tried posting your question on the GP Community Forum to see if others may have some more feedback for you? The next step after that would be to talk to GP Support and see if they can help resolve this.

      -Victoria

      Like

  41. Hi Victoria,
    We’ve got a situation where someone invoiced and posted a large amount of Sales Documents with Sales Territory “A” selected when they should have used Sales Territory “B”. I know there are far too many tables affected to try and do a simple update with SQL and we can’t use PSTL because we don’t want to change EVERY transaction from Territory “A” to Territory “B”. However, it does happen to be ALL the transcations (open and historical) for one specific Customer. Any thoughts on a creative fix that doesn’t require voiding and re-entering the transactions?

    Like

    • Brynn,

      If these are posted SOP documents, voiding and re-entering would cause issues with inventory, so that is not recommended. I don’t have a ‘generic’ recommendation for this, as I believe the solution may greatly depend on details in your data as well as a good understanding of everything you’re actually using the sales territories for. I would recommend talking to your GP Partner or GP Support to see what they can come up with.

      -Victoria

      Like

  42. It appears that the RM00103 table contains VOIDED transaction information. If I create a payment and then VOID it, this payment appears to remain in the LASTPYDT and LPYMTAMT fields. If a payment gets entered on an account incorrectly, I don’t want that data to show as possibly valid. Is there a way to remove VOIDS from the summary information?

    Like

    • Chris,

      I am not aware of any way to do this out-of-the-box, you’re talking about a customization. You bring up an interesting point, however not one that any of my customers have ever asked before. I guess because it is not very typical to have payments entered against the wrong customer. Is that something that happens a lot for you? If so, I would maybe look to see what I could do to prevent that, not just because of the reporting issue you bring up, but also because I imagine it creates a lot of work tracking this down and fixing it.

      Separately, if you are relying heavily on the ‘summary’ information, consider creating a custom report for it instead that would allow you to incorporate whatever logic you want.

      -Victoria

      Like

      • Victoria:

        Thanks for the insight. I was hoping to prevent the customization part but it looks like I will need to do that. We have SQL views which are used to display the summary data in external applications so it won’t be too difficult.

        We do occasionally have payments put on the incorrect account due to multiple accounts for spouses. The husband got the payment applied when it should have been the wife sort of thing. We also process many ACH payments and have had occasions were a payment is rejected or should not have been recorded (if we mistakenly processed ACH transasctions on a wrong date).

        I’ve tried many combinations but the RM00103 table does retain the “bad” payment date and amount even if I void, remove to history or remove completely and run a reconciliation and a check links.

        Thanks, Chris

        Like

  43. Victoria,

    Occasionally I have payments information which go missing in the AR module but are displayed in the banking module. This is strange since only some transactions within the same batch display this anomaly.

    We have no network issues, no posting issues, no general GP issues have been reported. How do I go about investigating or even correcting these errors; reconcile or checklinks do not help

    Like

    • Mark,

      Without looking at your data, based on what you’re describing, it sounds like maybe some of your customers are not set up to track history. That would make the record not be saved in the AR module, but still flow through to Bank Rec and GL. Often the problem with trying to confirm that this has happened is that there is no record kept of the history settings. So if they have been changed after the ‘missing’ transaction was posted, you will not be able to tell if this is what caused what you are seeing.

      -Victoria

      Like

  44. Hi Victoria,

    Could you tell me which field is used to specify the credit card type (VISA, AMEX or MC) if the Cash Receipt Type is set to ‘Credit Card’ (2) ?
    I am using the taRMCashReceiptInsert xml node class for adding customer payment info but cant figure out how to specify the credit card type for the receipt.
    Thanks a lot in advance…

    Like

  45. Hi Victoria,

    If you don’t mind me asking, what is the use of the RM table RM10601?

    Thank you very much.

    Regards.

    Like

    • Neil,

      Looking at the table name and our data, I would deduce that this table holds the tax details for any WORK or OPEN receivables transactions. In receivables, WORK means unposted and OPEN means posted but not moved to history (note that this is different from paid, so transactions can still have an OPEN status if they are paid).

      -Victoria

      Like

  46. Thanks so much for posting this information – I’ve only just begun my Dynamics GP adventure, and I’ve already been to this site numerous times. Being a straight up SQL developer, I have to know where the data resides, how it got there, and where it’s going next. Your resource has provided me with an abundance of information, so, again, many thanks!

    Like

  47. Hi Victoria,

    It’s been about a year since I’ve ping’d you for help but have been on the site a bunch, so again, thanks very much for a wonderful resource.

    I have a few questions this time:

    1. Where does GP store the date that payments and credit memos are applied to invoices? I’ve been unable to figure this out. I thought it was RM20201.DATE1, but this seems to be wrong sometimes. I’m looking for the date reported on the “Applied to Debits” window (where you end up when you click the Apply button on the “Cash Receipts Inquiry Zoom” window for a payment, or the “Receivables Transaction Inquiry Zoom” window for a credit memo.)

    2. Can returns be used when no money is changing hands? I would prefer that we use returns instead of credit memos when we need to adjust an invoice because returns track the specific items. Our accounting person has told me that returns can only be used if a payment has been made and we are refunding the money – they can’t be used to simply reduce an unpaid invoice. (For instance, a customer orders 3 of item A and wants to return 1 of them.)

    3. Can partial payments be associated with (tied to) individual items within an invoice, or only with the invoice itself? It would be helpful if, when a payment is applied, I knew which items were paid for. I am fairly confident the answer is no, but figured I’d tack this on. Also, if you have any suggestions for how to track this, let me know.

    Thank you!

    John

    Like

    • Hi John,

      1. There are actually 2 apply dates: a document apply date and a GL apply date. I believe the Apply to Debits window shows the doc apply date, which is DATE1 in the RM20201 and RM30201 tables. You can see both of the apply dates using my AR Apply Detail view.
      2. Returns can be used in the same way as Credit Memos, there is no requirement for money to be “changing hands”. If this is the understanding your staff has, I would ask why. Either there is something set up specifically for your GP environment that is not out-of-the-box that makes this a requirement, or possibly some refresher training may be warranted. Typically here is how I train users to choose: if you need to effect Inventory items, track returns to specific items, and/or show the customer line item details on a document, use a Return. If you are simply giving the customer a credit back to their account, not specific to any item…or are fixing an application of cash/credits or some other accounting issue, a Credit Memo may be better.
      3. As you suspect, GP does not allow for application of any credits (cash receipts, credit memos or returns even) on a line item basis. There is just no mechanism to enter this information or store it out-of-the-box. We have done a customization for this a long time ago for a customer, and it was REALLY involved and I would typically not recommend this approach without making sure there were no other options to accomplish what you’re looking for. In my experience most businesses will not see a need for this regularly (unless maybe they’re not entering Returns for line items that are actually returned/not paid for ;-) ). If you have a business where your invoices typically contain a few line items and it is important to track at the line item level, another approach that may be accomplished with some custom reports can be to always put one line item per GP invoice and then ‘consolidate’ billing to your customers. So when you send a customer facing invoice, it may really be 5 individual invoices in GP. Each GP invoice in this case can be shown as an individual item on the customer invoice because there is only one line item. If the customer partially pays, you can apply the payment to only the GP invoices with the items the customer paid for, leaving the others unapplied. We have done something like this for several customers successfully in the past.

      Hope this helps,
      -Victoria

      Like

  48. Thanks for the reply. No performance issues; I just happened to notice the duplicates & was wondering if a PTR failed or something & left the original records in the Open table.

    Makes writing reports of applied transactions more challenging, though :-)

    Like

  49. Hi, Victoria! It’s been a while since we traded comments over at the old Dynamics GP board :-)

    I have a question….I recently noticed that about 10% of the records (around 55,000) in our RM20201 table are duplicated in the RM30201 table – is that indicative of a problem? I thought the records were moved from RM20201 to RM30201 during a Paid Transaction Removal?

    A poster above (on 5/18/2011) hinted that the records could appear in both places if an invoice still has a balance on it, but I checked one of the records and this isn’t true – it’s fully paid.

    Any ideas?

    Like

    • Hi Bud, good to hear from you again!

      I believe there are a number of factors that determine whether an apply record is in RM20201, RM30201 or both, including the status of the credit transaction, the status of the debit transactions it’s applied to and the cut off dates used during paid transaction removal. There should be no harm as far as data integrity caused by transactions being in both of these tables at the same time. However, it’s possible you can see some performance issues. Is this why you are investigating this? While 10% of the records sounds a bit high to me, it’s entirely possible this is absolutely fine for your specific circumstances.

      -Victoria

      Like

  50. Firt, I saw a lot of useful information, Thank you for your excelllent job.

    I am new to GP, and trying to develop some commission reports now. Each salesperson has pretty unique commission rate which is not also a percentage number, but also involved the products type or product number. So I want to create a commission report for each salesperson. I am searching the RM tables to get the information I need, but get confused about open transaction, open applied transaction, history transaction and history applied transaction. Can you explain a little bit of these tables and where can I find the information for me to do the commission report? Thank you!

    Like

    • Neil,

      This gets pretty involved and not something that I feel I can do justice in blog comments. In my experience commissions are pretty unique in most companies and there is no one approach I could recommend that will work for everyone. Without understanding exactly how commissionable transactions are entered in your GP environment and what reporting is required, any answer you get would be partial at best and might be completely off track. I would recommend that you work with your GP partner to get a better understanding of the transaction flows and have them help you analyze your workflow and requirements to design the commissions report.

      -Victoria

      Like

  51. Is there a recommended process for MASS VOIDING transactions? I have a few hundred transactions that need to be voided. I ‘think’ I can update certain RM20101 fields and then run a reconcile… this appears to work. But is there another approach that is better? If the SQL method is fine, is there documentation on what specifically should be updated to complete the voiding.

    Like

    • Chris,

      I am all for using SQL whenever possible to save time, but this is not something I would recommend doing directly in the tables. You will be missing a lot of related transactions, summary tables, status tables, etc., not the least of which are the related General Ledger entries. Since the related transactions and tables may vary widely based on your specific GP setup, by the time you research and test all of this, it will end up being a lot less timely and a great deal safer to manually void all the transactions.

      -Victoria

      Like

      • Thanks for the advice. There were more than enough transactions to void to make this a painful process when attempting manually. So we went the Macro route. Best of both worlds. Dynamics did it’s thing, and it was over quickly.

        Like

  52. Hi Victoria,

    I’ve noticed starting from GP2010, user can enter a negative amount into GP Cash Receipt document. Do you know what is the purpose of having this capability? It seems to only able increase the customer’s balance and doesn’t do much. We cannot associate any document with this negative cash receipt either. Your help is much appreciated.

    -Nikki

    Like

    • Nikki,

      Here is the description of this new feature from the GP documentation: “You can enter a negative cash receipt for the purpose of decreasing a deposit. The negative amount flows through the system in the same way as a positive cash receipt, but is shown as a deposit with a negative amount.” I cannot recall a single time I needed this functionality in GP, but I guess there were enough requests for this to make the new feature list.

      In general I think that negative cash receipts are a bad idea and do not recommend that anyone use them – for the reasons that you stated.

      -Victoria

      Like

  53. Thanks Victoria!!!!

    Like

  54. Hi Victoria

    Can you tell me what table hold the customer notes? Someone here in there infinite wisdom would like to AUDIT them for all 8000 plus customers.

    Thanks
    Vic

    Like

  55. Victoria,

    I have an issue where a payment that was applied to an invoice is twice the amount of that invoice. Do you have any idea how this could’ve happened or how to fix it?

    Thanks a bunch for making this awesome site and for your help in advance!

    Like

    • Rob,

      Hard to say how it happened, most likely there was a system interruption as the payment was being applied or posted. Have you tried unapplying the payment and then reconciling the customer?

      -Victoria

      Like

    • Hi Victoria,

      I am new in GP and learn lot of things from your community. I also have one query, i want to know that where the SHIPMTDH of RM20101 come from, means from where the interface lies.

      Looking for your response.

      Like

      • Hi,

        Shipping Methods are stored in SY03000 for each GP company.

        -Victoria

        Like

        • Thanks for your quick response.
          i actually wants to know when SHIPMTHD in RM20101 were assign a value, because i have tried it from the POST of CMB Batch Entry, then POST same in ‘Receivable Batch Entry’ but it does not enter any value in ‘SHIPMTHD’ and i also don’t see any field of which link to me that this is ‘Shipping Method’.

          Kindly help me out of this.

          Thanks

          Like

          • When you enter a receivables transaction, the Shipping Method will default from the Customer and can be changed on each individual transaction prior to posting. However, not all transactions will have a Shipping Method – for example, there is no Shipping Method for payments (RMDTYPAL = 9).

            -Victoria

            Like

            • Thanks Victoria,

              you solve my problem and guide me in right direction. I hear your reply frequency and helping habits but you deserve more than that.
              I will be there soon if i got some problem.
              Thanks Dear

              Like

  56. Hi, does anyone know if it’s possible in the case of an NSF payment to determine the original document the payment was applied to? It appears once a payment is marked NSF the record in RM20201/RM30301 table gets deleted.
    Thanks!

    Like

  57. Victoria,

    I have to fix an incorrectly entered Invoice date. I have tracked in SOP30200 the following: DOCDATE INVODATE DISCDATE DUEDATE Tax_Date. I am looking through RM tables now. Is there a document on the proper clean up for this?

    Thanks,
    Allen

    Like

    • Allen,

      I strongly recommend against doing this in the tables. Just of the top of my head, I can think of multiple SOP, RM, IV, GL, MC, CM and TX tables that could be involved. And not just transaction tables, but also summary tables. And if you have customizations or 3rd party products, they need to be considered, too. The proper way to fix this is to enter a return on the ‘wrong’ date and re-enter an invoice on the right date. If the issue is just a GL issue, consider a GL adjustment.

      -Victoria

      Like

  58. Hello Victoria,

    I want to get information regarding notes which I entered while creating Credit Memo entries since I want to display those description inside the notes for report printing purpose. Pls advise me from which table i will get proper infformation

    Like

    • Hi Arun,

      The notes are stored in the SY03900 table. You can link to them from the transactions by NOTEINDX.

      -Victoria

      Like

    • Hello Victoria,

      This site is very Use Full having lot of information regarding different Issue in Great Plains. I have learnt lof of stuff about RM regarding how to move OPEN RM documents to HIST if document is not fully applied means have some remaining amount. I have done that using write off process that bring the remaining amount to Zero then use paid transaciton removal. But currently I am stuck into situation where I want to move OPEN documents to HIST but want to keep the customer balance remain same, which was before write off and paid removal. I have many documents types like Sales / Invoices, Debit Memo, Credit Memo and Payments with remaining amounts exist , that I like to move from OPEN to HIST but I can not because it would change customer account balance, I have looked about this but could not find any solution yet, Any help ? I am very thankful and appreciate your help and precious time.
      Thanks
      Naveed (naveedsadiq@hotmail.com)

      Like

      • Hi Naveed,

        You will not be able to do what you are asking in GP, as only fully applied documents can be moved to history. This also may present an accounting issue, as technically a customer’s balance is only made up of OPEN documents. You cannot have historical documents be part of the balance. The only thing I can think of is to create a new document for the difference and keep that open, while moving the documents you want to history.

        -Victoria

        Like

        • Hi Victoria,

          Many thanks to your response to my earlier question, well I have follow your instructions as well as consult MS GP consultant to close OPEN document, the way we found is to use write off and paid removal first and then create new documents in the year with status OPEN including same amount which was exist in other documents. This is solution works for Sales Invoice, Debit Memo and Payments but not for credit memo following solution provided

          The Credit memos that have an outstanding amount will need to be handled differently as there is not a writer-off process. The only option is to create a debit memo for the total amount that needs to be written-off for the customer. You will then need to apply this to the Credit Memos that need to be cleared. The next step will be to create a credit memo in the new year for the amount that was written off.
          But it did not work because in Manually Apply sales document window, debit not cannot apply to credit note

          Any idea how I can fix this issue for the credit note documents. Really appreciate your response.
          Many Thanks
          Naveed

          Like

  59. Victoria,

    I’m using your Receivable SQL script for All Posted Transactions. How can I join in the Customer Name from RM00101?

    Thanks,

    Mark

    Like

  60. Hi Victoria,
    What a valuable support you offer, I really thank you so much.
    I have a small question if you don’t mind,
    I need the table that bring the sales orders status, whether the order transfered or not transfered to sales invoice.
    Thank you Again.
    Regards,
    Sameh Adel

    Like

    • Sameh,

      If the SOP order is in SOP30200 (instead of SOP10100), then it has been fully transferred, but it does not necessarily mean it was transferred to an invoice…it could be transferred to a Back Order. Also, because you can have partial transfers, there is not just one status code. You can check the quantity fields on the SOP line items in SOP30300 and SOP10200 to see the details of what has happened. You can also use the ORIGTYPE and ORIGNUMB fields in SOP10100 and SOP30200 to see where transactions were transferred from.

      -Victoria

      Like

  61. Victoria,

    Excellent blog and very helpful.

    We found that if an invoice has multiple payments applied to it but still has a balance, the applied amounts exist in both the RM20201 & RM30201 tables at the same time. We could clear these out by running the Paid Tran Removal however we would like to keep a month or two of payments in case of NSFs. So in order to have a complete data base of applied payments (used for calculating commissions) We need all the transactions in RM30201 but only the NON-duplicates from RM20201. My script writing is beginner-limited so I’m enlisting your help for a script. Thanks for all your help.

    Mark

    Like

  62. Hi Victoria,

    Trust you’re doing well. I have a question about cutomomer’s Last Statement Balance. From GP Customer Payment Summary Inquiry, there’s the last statement balance at the bottom of the window. I could find that balance from the RM00103 table. However, is there a way I can get the precise list for ALL the invoices/documents that adds up to that balance?

    Your help is much appreciated.

    Best Regards,
    Nikki

    Like

  63. Hi Victoria,

    I am trying to get a table in RM which is having the Customer Balance in Foreign currency

    Like

    • I forgot to tell you thanks in advance.

      Regards,,,

      Sameh Adel

      Like

    • Hi Sameh,

      I do not believe customer balances are stored in anything other than the Functional Currency in GP.

      -Victoria

      Like

      • Hi Victoria,

        And thank you for the reply, but we are recording the transactions using an originating currency which is US$ and then use the exchange rate to convert to kd in the GL. So, my question is is there a table for the originating currency which by i can come to know the outstanding balance of the customers.

        Regards,,,
        Sameh Adel

        Like

        • Sameh,

          That would be the RM00103 table.

          -Victoria

          Like

          • Thank you Victoria, I need to know also where I can find the Exchange rate related to each transaction.

            Appreciate your continuous support.

            Regards,,,

            Smahe Adel

            Like

            • Sameh,

              I would look in the MC020102 (Multicurrency Receivables Transactions) table for that.

              -Victoria

              Like

              • Thank you Victoria,

                I tried this table but the problem that it is repeating the balances many time for the same customer.
                Is there a way to remove this duplication?
                Regards,,,
                Sameh Adel

                Like

                • Sameh,

                  I don’t understand what you mean. The MC020102 table has transaction information – it will not be showing any customer balances.

                  -Victoria

                  Like

                  • See, I will tell you exactly what I am doing
                    I am preparing a reporting technique which links all required data from GP directly thru SQL connection. I am almost done with all what the report may require, except that Receivables part. We have receivables with KD currency (our local Currency) and receivables with $ Currency, some of them pay in advance, so what I need here is to identify the customers’ remaining balances, or in other words, what they have paid and still laying in thier account. I was able to get the data easily from table RM00103, but the problem that it is showing only in Local Currency, while we maintain the balances with $ customers in $, at this point, there would be no problem if and only if the exchange rate dosen’t change. But the exchange rate flactuates and will show different value for balances in KD at the current xchng rate, so we cannot depend on KD balance, other wise we will perform this reporting manually.
                    This is the picture of the situation, Can you help in this.
                    Regards,
                    Sameh Adel

                    Like

                    • Sameh,

                      In that case, you cannot use the customer balances table and must instead get the transaction information from RM20101 and MC020102 and sum them up as needed.

                      -Victoria

                      Like

  64. Hi Victoria,

    Thank you for your site! It is very informative.

    I have a question regarding multicurrency cash receipts. When entering a multicurrency cash receipt the Apply button is greyed out. Microsoft states that we should post the invoice and then go to the Apply Sales Documents window to apply it, but this creates additional work for our users. Do you know why the cash application is disabled at the time of entry and are there any workarounds for this issue?

    Thank you!

    Peter

    Like

    • Hi Peter,

      I do not have any special insight as to why this is the way it is and I agree that this is counter-intuitive and creates additional work and have seen other complaints about this in the past. The only thing I can recommend is to submit changing this as a suggestion for future GP versions.

      -Victoria

      Like

      • The reason I have been given in the past – it needs to determine the equivalent amount functional currency, and can only do so once the exchange rate is known.
        Once the cash receipt is posted the functional amount is ‘locked’ and when it is applied to the appropriate invoice in the originating currency id, there can be a Realized Loss or Gain due to exchange rate differences.
        Due to this highlihood of RZLOSS/RZGAIN is why a cash receipt in non functional currency has to be posted before it can be applied.

        Like

        • Robert,

          That’s the reason I have been given as well, except all that means to me is that two separate calculations have to be done ‘behind the scenes’. I am not sure why that necessarily translates to the user having to perform two separate steps. :-)

          -Victoria

          Like

  65. Hi Victoria

    I was wondering what goes into calculating the field RM00104.SMRYSALS. I have a view that subtracts out the returns for a report I am writing , but my CFO wants to still try and tie things out to the GL so I need to know how the values that get updated in that field. So are they like from the sub total on an invoice or the total after shipping?

    Thanks

    Like

    • Hi Vic,

      The only way to tie to the GL is to start with the GL and determine where each transaction to the sales account(s) came from. Otherwise, you have to make two reports (GL and Sales) and find where the differences are.

      To answer your specific question, the ‘sales total’ tracked and summarized for sales transactions is determined by the settings on the Receivables Setup Options window (GP | Tools | Setup | Sales | Receivables | Options). If all the checkboxes at the bottom of that screen are checked, then all of those amounts are being added into the SMRYSALS amount. Since no history is tracked for changes to these checkboxes, if you were to change them today, there is no way to tell what made up the total for transactions posted prior to today. So again, not a good idea to use this to tie to the GL as they may be incomplete, include too much and there is no way to determine what exactly made them up and when.

      -Victoria

      Like

  66. Hi Victoria,

    I have a scenario to apply a sales return against a cash reciept.Exmaple sales return of 1000 and reciept of 500. I know this is a very rare scenario. But my client needs a solution for this. Let me know if you have any idea to do this.

    Thanks!

    Like

    • NB,

      There is no way to do this in GP. You cannot apply a credit transaction to another credit transaction.

      -Victoria

      Like

      • Does this same rule hold for Debit transactions?

        For instance, in a conversion we did for a client from MAS to GP10, we were left with what amounts to several cash receipts of negative amounts that were applied to invoices in MAS. It has been suggested that we create Debit Memos in GP for the negative receipts and apply the Debit Memo to the Sales Invoice to increase its balance.

        Can a debit memo be applied to a sales invoice?

        Like

        • Yes, the same rule holds. You cannot apply a debit memo to an invoice. You can only apply a credit transaction (payment, return, credit memo) to a debit transaction. No other combination is possible.

          Another basic principle is that the current transaction amount cannot be greater than the original transaction amount. So to increase the amount of a transaction you could (a) void it and re-enter it with the new amount or (b) have two or more transactions that make up the total.

          -Victoria

          Like

          • Thanks for confirming my fears. :D

            Your blog has been a great reference for those occasions when I venture outside of the Project Accounting module. Thanks for sharing!

            Like

  67. Hello Victoria.. love the new look of your site!
    We are using Dynamics GP Professional version 9.00.0281 and SQL Server Management Studio version 9.00.3042.00.

    My users have been entering third party shipping accounts numbers in User defined field 2 on the Customer Internet Window. I’ve found (with your help) email address table (SY01200) but it doesn’t contain the userdef2 table.

    In the RM00101 table there is userdef2 but it’s not the table that has the third party shipping information.

    Can you tell me where I can find the information?

    Thank you in advance,
    Cindy

    Like

    • Hi Cindy,

      Glad you like the new look!

      Just to make sure we are talking about the same thing…I am not familiar with a GP window called ‘Customer Internet’. There is ‘Customer Maintenance’, ‘Customer Address Maintenance’, and ‘Internet Information’ – are you using one of these, or something else?

      -Victoria

      Like

      • Thank you for replying.

        To enter the information the steps they take:
        Customer Maintenance Window → Address ID → click on i → Internet Information → User Defined 2 field.

        Like

        • Thanks Cindy,

          That is the Internet Information window. Since you can change the labels for the fields on this window, it does not necessarily follow that your User Defined 2 will be the same as my User Defined 2. However, if that is the field right above the Additional Information box at the bottom, then it is INET8 in table SY01200.

          -Victoria

          Like

  68. Hi Victoria
    I got a big problem migrating from GP 10 to 2010 with RMCashWork,
    do you know why in GP2010 in RMCashWork window the receipt field (DOCNUMBR) only accepts 17 characters and in GP10 the same field accepts 20 characters?
    in both cases table RM10201 is char(21)

    Thanks a lot

    Like

    • Hi FAC,

      I have not seen this come up as an issue before, I usually recommend smaller numbers, not larger ones. But I do see what you mean, I can manually type 20 characters into the Receipt field in GP 10.0 (and GP 9.0 for that matter), but it stops at 17 in GP 2010.

      This is one I would recommend talking to GP support about to see what they say.

      -Victoria

      Like

  69. Victoria,

    Wonderful Blog/Website, I use all these references on a daily basis. Thank you for all the hard work that you put into this for us.

    Now on to a question…

    We are currently using Open Item Statements for our customers; however we want to move to Balance Forward Statements. I have found that there is no way to do this. Is this true? If not where might I look to find how to change the statement successfully? I have tried changing the balance type field in the RM00101 field (in a test company of course), but with no real change to the statement. Only a balance forward amount and nothing else (no invoices or payments).
    Any help is much appreciated!

    Thanks!

    Topher

    Like

    • Hi Topher,

      Thanks for the kind words. I’ve never had someone want to go to Balance Forward…usually it’s the other way around. :-) Have you tried running Reconcile after changing the balance type for the customer?

      -Victoria

      Like

  70. Would you be so kind as to summarize the difference between RM20101 (Open Transactions) and RM20201 (Open Transactions Apply)?

    Does “_Apply” simply include more details? My accountant is asking me to write him a query that shows all open deposits (not applied/reconciled) on customers’ records.

    Like

    • Paul,

      The RM20101 table holds all posted Receivables transactions that have not been paid or have not been moved to history.

      The RM20201 table holds apply information for Receivables transactions that have not been moved to history.

      For example, let’s say you have 2 invoices and 1 credit memo:

      • Invoice 1001 for $400
      • Invoice 1040 for $500
      • Credit Memo 2010 for $450

      $400 from the credit memo is applied to invoice 1001 and the remaining $50 is applied to invoice 1040.
      RM20101 will have 3 records for these – one for each transaction. RM20201 will have 2 records for these – one for each apply record. While related, completely different sets of data.

      To find records that have nothing applied, you simply need to look in the RM20101 table, no need for the RM20201. My Unapplied Transactions view may help with this.

      Hope this helps,
      -Victoria

      Like

  71. Hi Victoria,

    Trust you’re doing well and congrats on the 7th MVP Title!! You’re very well deserved it for all your valuable contributions to the Dynamics Community.

    I have a question that hope you could help me. We have created Payment Cash Receipt document within Dynamics GP. However, the document cannot be found (seemed to be deleted) from GP. Is there a way to track or trace to see when it was deleted or who deleted the document from any of the tables in GP?

    Thank you so much in advanced!

    Best Regards,
    Nikki

    Like

    • Hi Nikki,

      Thanks! If this was a saved transaction that got deleted prior to posting, there will no record of it kept in GP unless you (ahead of time) set up something to specifically track this type of activity. GP does not do this out of the box, but there is an Audit Trails module and ISV products that can help with this.

      -Victoria

      Like

  72. Hi Victoria,

    Thanks so much for a wonderful blog! I regularly visit it for reference.

    Quick question….I had located the Customer Pricing Maintenance SQL table at one time, but have since lost it. Do you know where that table is located (customers to price sheet linkage).

    Thanks again!

    Chad

    Like

  73. Hi Victoria

    Congrats on the new book. I wil lbe sure to pick up a copy of “The Bible” as soon as i can. ;-)

    I am writing a report for sales that uses the RM00104 table. I need to get the sum of sales based on 5 diffrent date ranges in the same query or view. Like this;

    Salesperiod = 10 and Year is 2009 and Salesperiod is 10 and Year is 2010 also
    Salesperiod = between 1 and 10 and Year is 2009 and Salesperiod is between 1 and 10 and Year is 2010 also
    Salesperiod = between 1- 12 and Year is 2009

    I also need the RM00101 table to get sales territory and Salesperson and customer name info.

    I have this done in Access (My old favorite!) by combining 5 diffrent querys but i would like to have it in SQL server so I can make an ssrs report out of it.

    Do you know of any way this can be done?

    thanks

    Like

    • Thanks Vic,

      I am guessing what you’ve listed is just an example, since those overlap…so you’re just looking for an example of how to do this in SQL? If so, you can do something like this:

      SELECT SLPRSNID Salesperson,
      SUM(SMRYSALS) TotalSales
      FROM RM00104 S
      INNER JOIN RM00101 C
      ON S.CUSTNMBR = C.CUSTNMBR
      WHERE (PERIODID = 10 and YEAR1 = 2009)
      or (PERIODID between 1 and 10 and YEAR1 = 2010)
      or (PERIODID between 1 and 10 and YEAR1 = 2011)
      and (HISTTYPE = 0)
      GROUP BY SLPRSNID

      You can just change or add additional period or year restrictions following this example.

      -Victoria

      Like

  74. hi, I doing a report on balances, but I have a question where I can take the beginning balance of a customer which would be the field.

    Greetings

    Like

  75. Hello Victoria,

    I have a big mystery on my hands and I thought I’d run it by the expert (you!!). I have records in my RM20201 table showing (among other things) Returns that were applied to Invoices. I am assuming that that table gets populated when an end user goes into ‘Apply Sales Documents’ and processes the apply there.
    However, the end users have sworn to me that they never go into ‘Apply Sales Docs’. So I’m just wondering how that RM20201 table is getting populated? Is there some other auto-apply utility or tool in GP that I’m missing?

    Thanks!!

    Like

    • Hi Kat,

      Depending on where/how the return or credit was entered, it’s possible that the apply process was done at the same time, by clicking the Apply button. So it’s not always necessary for users to actively navigate to the Apply Sales Documents window. It’s also possible to apply credits and returns at the same time as entering Cash Receipts. Finally, if you have any 3rd party apps or customizations (or even imports), they may be doing this. If you are on GP 10.0 or higher, there should be a time stamp on the apply record that may help you figure out when and who created it.

      -Victoria

      Like

      • Thanks for your response Victoria.

        When I go into Sales Order Trans and try to process a test Return, I do not see the ‘Apply’ button anywhere. I know that this exists in AP, but on the AR side of things, it does not seem to exist. Not even when you go to ‘Additional > Create Return’ in SOP. It appears that the only way to apply it is if you go into the ‘Apply Sales Docs’ screen and do it there. Which the end users swear they don’t use. So I’m still a bit stumped. We aren’t using any third party products and of course the RM apply to table doesn’t contain user_who_posted or anything like that. I’ll have to check out the other thing you said, that they may be applying when they enter the cash receipts.

        Thanks a lot!!

        Like

        • Hi Kat,

          You are right, there is no Apply button in SOP, but in RM there is – so it you are entering a credit memo or a return from the Transaction Entry window (instead of the Sales Transaction Entry window), there will be an Apply button there.

          For what it’s worth, my money is on a user going into the Apply Sales Documents window and either not remembering or not admitting to it. :-)

          -Victoria

          Like

  76. Hi Vic,
    How do i increase the description field size for receivables transaction entry in gp 2010.
    Transaction>Sales>Transaction Entry.
    regards,
    Charles

    Like

  77. Hi victoria

    any idea where the email address for the salespeople is located?

    thanks
    Vic

    Like

  78. Hi Victoria,

    I’ve been using your website for 1-2 years now, and it’s been extraordinarily helpful – thank you so much!

    I learned today that our accounting person had created a credit memo to offset a transaction which had been paid by EFT, resulting in our having the money, but no payment record. Two questions – first, do you know whether there’s a good alternative to handling payments like this which occur outside of GP? And second, do you know which table/column contains the “Description” field for receivables transactions? (The field I’m talking about is on the Receivables Transaction Inquiry Zoom field – the one displayed when you click on the Document Number link in the Receivables Transaction Inquiry window.) If we have to stick with using credit memos, I can at least pull this field, in which our accounting person records a short note of explanation for each CM.

    John

    Like

    • Hi John,

      Thanks for the kind words, glad my blog is helpful!

      I have seen GP users do this before and it always confuses me, why would getting an EFT be any different from other ways customers send you money? If a customer sends you a check and you go to the bank and deposit it, the end result is the same – your cash is increased and your Accounts Receivables are decreased. The only difference with an EFT (or wire) is that the money is automatically in your account, you do not need to go to the bank to deposit the check.

      All that said, I would recommend entering these as Receivables Cash Receipts of type Cash. If you are using the Bank Rec module, this will result in a receipt that will need to be deposited, just like checks received from customers do. Yes, the money is already in the bank, but you just have to tell GP that with the deposit step. If you are not using Bank Rec, nothing else needs to be done, the Cash Receipt will simply debit Cash and credit AR and show a customer payment on the customer account. (And if you are using Bank Rec, I imagine the Credit Memos are throwing it off…not sure if that is why you need the additional reporting?)

      As to your last question, descriptions for all posted RM transactions will be in either RM20101 or RM30101, depending on whether the transactions has been move to history or not.

      Hope that helps.
      -Victoria

      Like

      • Victoria,

        Thanks so much for that information – I just went over this with our accounting person, and he agreed that your way is better. He explained that we get so few EFTs that he tends to forget how best to process them. BTW, we do use the Bank Rec module, but he entered a corresponding IAJ, so it didn’t throw off the reconciliation.

        Thanks again!

        John

        Like

  79. Hi Victoria!

    One of our users reported an error:

    You can’t edit this document because a multicurrency record is missing.

    The error occured while the user retrieves a specific document from the Receivables Transaction Entry Window.

    What would have caused this error? And how are we going to fix this?

    Thanks a lot!

    Like

    • Chris,

      I have not come across this error before… If you try the same thing on a different computer, do you get the same error? If so, I would try running Check Links on the sales series and see if that fixes the issue.

      -Victoria

      Like

  80. Hi Victoria,

    Trust you’re doing well. We ran into an issue with Cash Receipt for a Canadian customer. We created a Cash Receipt for credit card payment; however, once the Cash Receipt is posted, the unapplied amount shows up as $0.00 even though the Cash Receipt has not been applied yet to any invoices. Have you seen this occur before and do you know what might have caused it?

    Many Thanks & Best Regards,
    Nikki

    Like

    • Hi Nikki,

      Doing great, thanks! Hope you are, also. :-)

      Sounds like something may have gone wrong with this transaction. Have you tried running Sales Reconcile and/or Check Links?

      -Victoria

      Like

      • Hi Victoria,

        Thanks for the kind response. Yes, we’ve tried running Check Links but no lucks and it happens most of the time for Canadian customers. We’re wondering if there’re any settings we might have missed for multicurrency.

        Best Regards,
        Nikki

        Like

        • Hi Nikki,

          Reconcile would actually be my first choice for this, not Check Links. As far as whether something is missing in setup, it’s really impossible to say without checking every single setting. However, I would think that if there was actually setup missing, you would get errors. More likely there is setup that is slightly incorrect, which may actually be more difficult to track down. :-(

          -Victoria

          Like

  81. hi victoria,

    where table can I find the posted Receivables Management batch comment?

    Like

  82. Victoria,

    As an avid user of your site for finding solutions for GP challenges, I again am seeking your expertise. I’m looking to determine if there is a way to determine whether invoice is paid or not by using a particular field in one of the tables. What I’m finding is that one payment or check can cover more than one invoice, so there’s not a one to one match. Is there a field that gets populated when a payment is applied indicating that the invoice has been paid?

    Regards,
    Raul

    Like

    • Raul,

      Why not use the Current Transaction Amount? If it is zero, then the invoice is paid.

      -Victoria

      Like

      • Victoria,

        Thank you! I am pulling from the RM20101 and looks like I’m getting the data I need for my report. Is the CURTRXAM field the same as Amount Remaining while looking at the Receivables Transaction Inquiry window in GP?

        Regards,
        Raul

        Like

  83. Hello Victoria,

    Do you know if there is a field in RM30201 that indicates when a transaction was moved to History. I would like to be able to query for new items added, since the previous time I checked. I am not seeing something like CREATEDDT or LastModified or ItWasMovedToHistoryOnDate :-).

    Thanks in advance.

    – Rick

    Like

  84. Victoria,

    I have reconciled my bank statement in GP9.
    My adjusted Bank Balance and
    My adjusted Book Balance equal
    My GL is low about 258K from the reconciled Balance.

    How can I bring my GL in line with my Reconciled bank Balance?

    Also, in the Bank inquiry window my current checkbook balance and my current cash accoutn balance are different as well.
    Hohw do I correct this?

    Like

    • Paula,

      Not sure why this is posted under RM Tables, but in general, if you are satisfied that your Bank Rec balances are correct and your GL does not match this, then your GL is incorrect and you need to enter an adjustment entry in the GL.

      My 2 cents would be that 258k is a large enough difference that I would want to understand why it’s there and do some further investigation. Typically there are 3 reasons why the GL would be off from the Bank Rec:

      1. Not everything is posted in the GL.
      2. Timing issues with Payables Voids – they are in Bank Rec in one month and in the GL in a different month.
      3. Erroneous entries to cash account in the GL or other modules.

      A tool that might help you reconcile your difference is The Closer from Reporting-Central: http://www.reporting-central.com/.

      As to your last question – you have already confirmed that your reconciled Bank Rec balance does not match your GL balance – that’s all the inquiry window is showing you, this should not be a surprise, right? :-)

      -Victoria

      Like

    • True. I think I just needed confirmation from someone else. The number just sent me crazy!!! Thank you Victoria.

      Like

  85. Victoria,

    I am working to clean up unencrypted credit card numbers that have gotten into our GP database. I am running GP9 and have fixed the inputs to where they are now only sending the last 4 instead of the entire card number. I Have identified RM20101, RM30101, SOP10103, SOP30201 that contain the numbers, but I am wondering if there is anywhere else that I may be missing.

    Thank you very much for your help.

    Like

  86. Hi Victoria,

    Once again, thank you so much for sharing all of this information and knowledge about GP for the community. These are greatly helpful to us!

    I have a quick question regarding SalesPerson & TerritoryID in GP. Is there any relationship between SalesPersonID & TerritoryID? (ie: 1:1, 1:N) For every customer record in GP, it’s assigned with a SalesPersonID and a TerritoryID. Is there any reason whereas CustomerA has SalesPersonA and TerritoryA but CustomerB has SalesPersonA but TerritoryB? Thanks so much in advance.

    Like

    • Hi Nikki,

      The only relationship I can think of is that Territory ID is a required field when setting up a Salesperson ID. I am not sure, but I think the only thing this is used for is to possibly to summarize the totals on the Sales Territory Maintenance window. I don’t know any companies who actually use those numbers for reporting. Typically reporting requirements are much more complicated than this allows for.

      So…no, there is no reason why you can’t set up Customer A with Salesperson A and Territory A and Customer B with Salesperson A, but Territory B.

      -Victoria

      Like

  87. Victoria,

    Thank you worked like a champ! The only piece left that I need is the project number, what table can I join to in order to get this?

    Thanks,
    Raul

    Like

  88. Victoria,

    I’ve been looking for a way to gather all credit memo transactions in our system. I see RMDTYPAL 7 = Credit Memo and also see DISTTYPE 19 = Credit Memo. Tables RM00101, RM20201 and RM30101 appear to have a lot of the information I need but how can find out what GL account these transactions interface to and what field (if any) can I use to join in order to tie all this information together? Thanks in advance for your knowledge sharing,

    Raul

    Like

  89. Victoria,

    Have a mystery on my hands, someone or some how 90% of sales customers taxschid was changed. What is fastest way to change back, through GP or update to underlying tables on back end and how many tables would it involve, RM00101, RM00102,….

    Thanks,
    Raul

    Like

    • Raul,

      This can happen with changes to classes that are rolled down to all the customers in the class. If you are using customer classes you can make whatever changes you need to that way and roll them down to customers. Otherwise, I believe RM00101 and RM00102 are the tables that would require changes.

      -Victoria

      Like

  90. Hi Victoria,

    We received a posting error message on the RM Cash Receipt Posting Journal: ‘A control ID is missing or invalid. Transaction will not post.’

    The stuck documentshave Posting Status of 20. The Batch Status is 11.

    Have you seen this problem before and do you know what might have cause it?

    Your help is greatly appreciated. Thanks!

    Best Regards,
    Ellen

    Like

    • Hi Ellen,

      I cannot recall ever seeing the term ‘control ID’ in a GP message or window, so I am not exactly sure what the problem is in this case. Batch Status of 11 is ‘Single Use Error – Edit Required’ (from http://victoriayudin.com/gp-reports/companysystem-tables/). Can you see this Batch in Batch Recovery? If so, I would recover the batch from there and print the batch edit list to see if that gives any more ideas about what the problem is.

      -Victoria

      Like

      • Hi Victoria,

        Thanks for the kind response. I’m sorry for being unclear but the error ‘A control ID is missing or invalid…” is what we’re getting from the batch edit list from the RM Posting Journal.

        It happens quite sporatically without any clear pattern. The batch can be retrieved from Batch Recovery and repost with no problem but the trouble is that the payments already hit Bank Deposit, which in turns caused duplicated bank deposit entry after reposting.

        Once again, thank you so much for your help.

        Ellen

        Like

        • Hi Ellen,

          Unfortunately, I still have the same answer. :-( The term ‘Control ID’ is not something I have seen in GP anywhere, so I am not quite sue where that is coming from.

          I will say that if your batches periodically stop because of this during the posting process and then end up posting twice, there is definitely a serious problem and I would recommend talking to your GP Partner or GP Support to troubleshoot this once and for all.

          -Victoria

          Like

  91. Hi Victoria,

    I would like to know invoice numbers where credit/debit memos were applied to. I see CREDT and DEBIT transactions in RM20101, but there are no invoice numbers associated with them. Can you please let me know which tables contain the information? Your help will be greatly appreciated. Thank you so much.

    Like

    • CT,

      RM20201 and RM30201 have the apply information for receivables transactions. Something to keep in mind when looking at the data in these tables…credit documents (payments, credit memos and returns) will be applied FROM and debit documents will be applied TO.

      -Victoria

      Like

  92. Hi Victoria,

    We publish GP via Citrix. On of my users voided a check in RM and the check voided, but he Invoices the check were applied to do not show as open. They are in the RM historical tables.

    Do you know if I can run a script to update these records or will I have to remove history on them and reenter the invoices.

    Thank in advance for you answer,

    Tammy Cowart

    Like

    • Hi Tammy,

      I would start out with running Check Links and then Reconcile on the Sales Series. If that does not help, you may need to investigate what exactly is in the tables to determine what needs to be fixed – I am not aware of any ‘global’ script that would fix this.

      -Victoria

      Like

  93. Victoria,

    Do you know which table has posted cash transactions?

    Thanks,
    Hishma

    Like

  94. If I do a sales transaction, what all tables are affected once processed?

    Like

    • Larry,

      This could be a great many tables, depending on what module you are using, what details your sales transaction has and you posting settings. In SOP, with inventory items this could easily be 20 or 30 tables. You can look at the Dynamics GP SDK to see the transaction flow for most of the common transaction types and modules. If you want to get the answer specific to your environment, you can set up a DEXSQL.log or a SQL trace to determine the tables updated by your sales transactions.

      -Victoria

      Like

  95. Victoria,

    Our Controller used the Paid Sales Transaction Removal function in GP. For one reason or another it moved a bunch of invoices to History but not the payments that paid those invoices. As a result the wrong balance is printing on the RM Statements. I am not sure if this happened because the Checks checkbox was left blank or if it was because of the cutoff date that they used. I am trying to figure out what the best way would be to determine how many transactions we have that are like this. They have also requested that I undo what they did, which unless I am mistaken, is not possible. The only solution that I see is to determine which transactions are like this and move the corresponding payments to history as well.

    Thanks,
    Nate

    Like

    • Hi Nate,

      You are right, you cannot undo Paid Transaction Removal process. If you own the RM Unapply Tool from the Professional Tools Library you can unapply payments from invoices one at a time and that would move transactions back to the open tables, but that is probably not what you’re looking for.

      If you suspect that all the options were not checked correctly, I would recommend re-running the Paid Transaction Removal process and including all the things that should have been included in the first place.

      Another reason you might see payments still in the open status and showing up on the statements is that they are not fully applied. Until a payment is fully applied, it will not be able to move to history.

      You can see all the payments that are still in the open tables by either looking in the RM20101 table or searching for all payments with a Document Status of Posted on the Receivables Transactions SmartList.

      -Victoria

      Like

  96. Hi Victoria

    I wonder if you can help me figure out what I did wrong in my steps of doing RM integration? I’m trying to run integration for Debit Memo on Receivable (Sales) module and I’m encountering problem with posting to the GL distribution account. Here are my steps:

    1. Distribution > Distribution Account from Source Field
    2. Distribution Type > Use constant > “RECV”
    3. Credit Amount > Amount from Source Field

    I have a default account setup for the Debit amount and so would like to integrate the Credit side of the transaction but somehow the credit GL Account was not integrated correctly where the result is always a blank account.

    One thing I did try was to change the Option to Record Source to point to my source file but this changes my default debit account to my source file, which I do not want. At this point I’m a bit clueless and cannot find a good source of reference for help.

    Your advice will be greatly appreciated. Thanks a lot !!

    –Helena

    Like

    • Hi Helena,

      I am not sure that you can give the import only one of the accounts it needs. I think your options are to either give it all the accounts or none (meaning they will all default from GP settings.) At least that is how I have always done any transactional integrations. However, I am by no means an expert on Integration Manager, so I would encourage you to seek other opinions in one of the GP Newsgroups – there is a list of the more popular ones in the right column of this blog, towards the bottom.

      - Victoria

      Like

      • Hi Victoria

        Thank you very much for your reply. I will try your suggestion and perhaps also look for more answers in the GP newsgroup. Thanks again for your help !

        -Helena

        Like

  97. Hello,
    Is there a way to find out who(employee) applied a credit invoice? Or who applied the open balace of a check to an invoice? (Using Apply Sales Documents)

    Is there a report I can create? Or a history i can view?

    Like

    • sRv,

      I am not aware of any table that stored this information. I think your options for tracking this kind of information are:

      • – Use an auditing tool/module (something like Audit Trails)
      • – Create a customization that tracks the information you want in a custom table – then you can report on that using any tool you’d like

      -Victoria

      Like

  98. Hi, thanks for this info.

    i have a question,, how could i get a statement for the customer for posted and non-posted transactions??

    thanks in advance

    Like

    • Ghaleb,

      You would need to get information from multiple places. You could start with the view I have posted here for all unapplied receivables transactions and then add in the information for your unposted transactions. Where you get that depends on what module you are entering the transactions in, for example, if you’re using SOP, you’d need the SOP10100 table (and possibly a few others, depending on what level of detail you need for your statement).

      -Victoria

      Like

  99. Thank you very much for this site. It is quite helpful and informative. Great job!

    I am new to GP and I am looking to find out whether or not an invoice has been paid off. Can I use DINVPDOF field from the RM30101 table using DOCNUMBR as the invoice num? If this field is populated (other than 1/1/1900) then can I assume this invoice has been paid off?

    Like

    • Hi Karen,

      Thanks for the kind words!

      Since RM30101 is a historical table, invoices will only be there if they are fully paid or voided. No need to check anything else. If you are looking at RM20101, that could have both paid and unpaid invoices. In either case, though, if you want to check a field, I would recommend using the CURTRXAM field (Current Transaction Amount) to determine whether something is paid. If CURTRXAM = 0 it is paid, otherwise it is not.

      -Victoria

      Like

      • Victoria,

        I am resurrecting this topic as I have more questions regarding paid invoices.

        If all invoices in the RM30101 table are fully paid (or voided), what does a date of 1/1/1900 indicate for the DINVPDOF field? I thought it would have the date that it was fully paid.

        Also, what field indicates the invoice? I was using DOCNUMBR. However, for credit card payments, DOCNUMBR holds a value that is not an invoice number and I cannot link back to the invoice. I believe CSHRCTYP = 2 indicates credit card. Is there anyway to link back to the invoice for these?

        Thank you,
        Karen

        Like

        • Karen, there are a number of questions in here, I will try to address them all:

          1. Since Dexterity does not recognize NULLS, 1/1/1900 is used to indicate a NULL date. Anytime you see a date of 1/1/1900 in GP, that is a placeholder for no date.

          2. DINVPDOF may not always be used, it really depends on the details of the transaction. While I do not have a definitive list on when it is or is not used, here are some examples where I have seen a blank (or 1/1/1900) DINVPDOF:
          a. On non-debit transactions (like payments, credit memos and returns).
          b. On invoices that had a total amount of 0.
          c. On invoices with a payment entered directly on the invoice. (Only in older GP versions.)

          3. The RMDTYPAL field determines the transaction type – the list of available types and corresponding codes is listed above on this page.

          4. A list of available cash receipt types is also listed above on this page, and yes, 2 is a Credit Card payment.

          5. To link payments to invoices you would need to look at tables RM20201 and RM30201. I have a view posted on this blog that already links payments, credits and returns to the invoices they were applied to: http://victoriayudin.com/2010/02/15/sql-view-with-ar-apply-detail/.

          Hope this helps.
          -Victoria

          Like

  100. Hello Victoria, do you have any knowledge of AA tables? I have a question regarding this let me know. thanks.

    Like

  101. Hi Victoria,

    I have a question. Can you tell me what table or tables are updated when you process a transaction via Transactions>>Sales>>Apply Sales Documents.

    For example, if I have an unapplied amount of $1,000 and I apply $500 of it to a specific invoice. Can you tell me what table I will see the $500 amount that was applied?

    Like

    • Yolanda,

      The answer to this may depend on the complexity of your transactions, for example, Multicurrency or discounts will add additional tables. There are also summary tables that may be affected, depending on the details.

      However, the basic transactional tables where you can see the $500 applied would be:
      RM20101 – this holds all the transactions, so the current transaction amount for both the the payment/credit and invoice will go down by $500
      RM20201 – this holds apply information, a new record will be added here for the $500 applied

      -Victoria

      Like

  102. is there a query to find the bill to and ship address?

    Like

    • MC,

      Where to find the bill to and ship to addresses will greatly depend on where your invoices originated from. Are you using Sales Order Processing to enter invoices? Or entering them directly into the Receivable module? Also, do you need this for posted or unposted transactions?

      -Victoria

      Like

  103. Hi Victoria,

    I have a question. I am looking for a table that will show me the comment that was entered as the distribution reference when applying cash receipts. I have found a column in the RM30301 table called DistRef and I want to know if this is the correct table and column. Also, when I do a query of select * from RM30301 it retrieves no data. Should that be the case?

    Like

    • Hi Yolanda,

      Yes, if you are entering data on the Distribution Reference line of the Cash Receipts Distribution Entry window it will be stored in the DistRef column of either the RM30301 table for historical transactions or the RM10101 table for open and work transactions. For what it’s worth, it is very unusual to see distribution references on cash receipts, so I am not surprised that you’re not seeing anything there. If you have definitely entered data there, make sure it’s not in the RM10101 table first, then if you’re still not finding it, please write back with the exact window name and field name where you are seeing the data in GP and I will help you track it down.

      -Victoria

      Like

  104. Hi Victoria,

    Once again…thanks for all of the help. Your SQL update worked and my user was able to apply a payment to the invoices. I set the CURTRXAM = ORTRXAMT since no payments were received against the invoices.

    I greatly appreciate your prompt, thorough and accurate responses.

    Best Regards,

    Brent

    Like

  105. Victoria,

    Perhaps I should be asking is there a table that has the current Invoice Amounts? If yes, can I set them to 0 or would I delete the record?

    Brent

    Like

    • Brent,

      If Reconcile does not fix the issue, run this in SQL:

      update RM20101
      set CURTRXAM = 0 --change this to what should be the amount remaining
      where DOCNUMBR = 'YourInvoiceNumber' --change this to your invoice #
      and RMDTYPAL = 1 --just in case the DOCNUMBR is not unique

      -Victoria

      Like

  106. Victoria,

    A little further info…The records are in table RM20101 and they are not in table RM20201 (I mistyped the table name in the original post). All but one of the invoices showed in RM20201…I ran check links and it said that the “ghost” payment had been unapplied. I looked at table RM20201 and the one Invoice was no longer in the table. However, the invoices still don’t show if I try to apply a payment against them.

    Next steps?

    FYI – I was searching google and yahoo to try and figure out why our users were unable to see ole notes and it, of course, led me back to this site with an answer…Thanks so much for the help!

    Brent

    Like

    • Brent,

      My next step would be to run Reconcile on the Outstanding Transaction Amounts. If that fixes the issue, that would be best, since you won’t have to go fix this directly in SQL.

      -Victoria

      Like

  107. Hi Victoria,

    Your website is a tremendous tool.

    I have 9 Invoices in RM20101 that do not appear in table RM20102. The user was in the middle of applying payments to invoices and gp froze up on her. She is now unable to select those invoices to apply payment to them.

    Any suggestions?

    Thanks so much for your help.

    Brent

    Like

    • Hi Brent,

      Did you mean RM20201 (instead of RM20102)? If so, what is the status of the payment transaction(s)? You may need to either run check links or delete the payment records from the database to get rid of them first, then run reconcile on the Outstanding Document Amounts to update the invoices so that the current trx amount for them gets updated. It’s a little difficult to give exact steps here since there are so many possibilities as to the status of everything. But the basic issue is that even though the payment(s) may not have made their way completely into the system, the current trx amounts on the invoices have been updated to reflect the apply information. Because the payments were not deleted ‘cleanly’ the current trx amounts on the invoices were not updated to reflect that the payment is no longer applied. Hope this helps get you started on fixing this.

      -Victoria

      Like

  108. What RM table should I look in to find payment history? (i.e. the payments that were applied to specific invoices). I have checked tables RM30101, 30201, and RM20201

    Like

    • Yolanda,

      The payment record itself will be either in RM20101 or RM30101 depending on whether the transaction has been moved to history or not. The apply information will be either in RM30201 or PM20201.

      -Victoria

      Like

  109. Thank you for your prompt response – very helpful. Do you have a similar script for the Invoicing module? I am looking for the same information you provided in your View_RM_Transactions plus I need the distributions as well.

    Any help with this would be GREATLY appreciated.

    Thanks.

    Like

    • Sue,

      You’re looking for the Invoicing module, not SOP? Since that’s not a very popular module, I am not sure if I have ever seen any views made for it. And I don’t have any that I’ve created already. Sorry.

      -Victoria

      Like

  110. Thank you so much for publishing all of this information about all the GP Tables and the very helpful SQL queries. Can you tell me how I can get the distribution for each RM Transaction? I am using your View_RM_Transactions and need to also show the distribution accounts. I have looked at both of those tables and the RM20201 and RM30201 tables, but can not identify the fields where the distributions are stored.

    Thanks.

    Like

  111. Dear Victoria

    It is really very good stuff; I am trying to study the technical aspects and your informations are really healty

    thanks

    Anil Kumar

    Like

  112. great article, very useful !

    Like

  113. Victoria,

    Is there a place anywhere in the RM tables that will show me the “Last Sale/Activity Date” for a Customer. I want to help clean up our Customer info and this would be a huge help.

    I’m new to GP, so finding this website was great. I find this website more helpful than Microsoft’s GP support site. Keep up the good work.

    Thank in advance for your help.

    -Shane

    Like

  114. Can anyone tell me what the numerical values are associated with the aging periods in Table RM40101 or Table RM40201? Are there any values like 1=current, 2=30-60 Days, etc. Where would I find this information? I have looked all over.

    Like

    • Yolanda,

      Since the aging periods can be individually set up for each company, there are no set values like 1=current, 2=30-60 Days, etc. However, for the company you’re looking at, you can use the INDEX1 column in RM40201 to determine the number of the bucket. The 2 lines for Balance Forward customers have 101 and 102 in INDEX1, so you can ignore those, all the others will have INDEX1 values of 1 through 7 corresponding to the number of the aging bucket.

      -Victoria

      Like

  115. Victoria,

    I am looking to retrieve balace data based on SOPNUMBE and I am able to get most of the data querying the RM30201 table using customized eConnect Requester. This table will give me the APTODCNM and APPTOAMT which I can sum up and compare to the SOP30200 table using DOCAMT and OPNUMBE. But our users also can apply a return to an invoice. This however is not stored in the RM30201 table at all. Do you know where this is stored?

    I love your site BTW. Good job, very helpful.

    Like

    • Joel,

      Thanks for the kind words!

      Are you looking for the current balance on a given transaction? If so, I would use the RM20101 table instead. The CURTRXAM column holds the current balance. If you can guarantee that your SOPNUMBE is unique, you can just link that to the DOCNUMBR, if not, you’ll need to do a little creative mapping from SOPTYPE to RMDTYPAL. Hope that helps.

      -Victoria

      Like

  116. It would be nice to also have the relationships. Or at least the ones that for sure are inner joins.

    Thanks.

    Like

    • Elizabeth,

      There are so many possible table relationships that it really depends on what you’re looking to do and may be difficult to show here. I have been posting up some SQL scripts, they’re all listed on the GP Reports page – you can see some of the table relationships there. If you’re looking for something specific that’s not there, please let me know and I will see if I can help.

      -Victoria

      Like

  117. Thanks for this! You’ve saved me some work again.

    Like

  118. Is there views setup for the RM20101 and RM30101 merged together?

    Like

  119. VERY useful. Thanks. Do you post any downloadable reference docs?

    Like

    • Elie,

      Glad you’re finding this useful! I have not posted up any docs to date. I had a lot of this information in various documents and formats and combined it on the blog to make it more easily accessible. It’s all text, so you can easily copy from it or print it. If there is something specific that you’re looking for that’s not easily copied from here, please let me know and I’ll see if I can put it together for you.

      -Victoria

      Like

  120. Excellent – Thanks

    Like

  121. This is really a Good Stuff….

    Thank you very much!!!!!!!!!

    Like

  122. Excellent – very useful. Thankx

    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,470 other followers

%d bloggers like this: