Open Forum

Expand all | Collapse all

where can I find the RM20101 query

  • 1.  where can I find the RM20101 query

    Posted Jan 04, 2019 01:40 PM
    Hi, we are trying change some transactions posted in future years. I researched and found running the RM20101 query would work, but we have been unable to find it. Can anyone assist?

    ------------------------------
    Akeda Wilkinson
    Hancock Claims Consultants
    Alpharetta GA
    ------------------------------


  • 2.  RE: where can I find the RM20101 query

    GPUG ALL STAR
    Posted Jan 04, 2019 02:25 PM
    @Akeda Wilkinson,

    Well, RM20101​ is one of the main SQL tables in GP: the Receivables Management Open File. There is no query or stored procedure by that name.

    There are literally thousands of GP stored procedures that reference that table.

    What information did you find that says you can use a query to do what you want?

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------



  • 3.  RE: where can I find the RM20101 query

    Posted Jan 04, 2019 02:42 PM
    Thanks for responding Steve, it was in a dynamics community discussion.  the instructions were as follows:

    If you have SQL knowledge, run the below query against company database and correct the document date for the open transactions.

    select * from RM20101 where DOCNUMBR = 'Enter Doc Number' and RMDTYPAL = 1 or 9

    select * from RM20201 where APTODCNM = 'Enter Doc Number' and APTODCTY = 1 or 9

    Make sure if the above query return the one line of records with incorrect document date and run the below query to update the document date.

    Update RM20101 set DOCDATE = 'Enter Correct Doc Date' where DOCNUMBR = 'Enter Doc Number' and RMDTYPAL = 1 or 9

    update RM20201 set APTODCDT = 'Enter Correct Doc Date'  where APTODCNM = 'Enter Doc Number' and APTODCTY = 1 or 9

    Note: To correct doc date for cash receipt use RMDTYPAL = 9, for invoice use RMDTYPAL = 1

    ------------------------------
    Akeda Wilkinson
    Hancock Claims Consultants
    Alpharetta GA
    ------------------------------



  • 4.  RE: where can I find the RM20101 query

    GPUG ALL STAR
    Posted Jan 04, 2019 03:09 PM
    @Akeda Wilkinson,

    Ah! I see. Those instructions allow a GP Administrator to alter the document dates directly in the SQL tables.​ A very accomplished GP guy (with the most amazing name: Somakarpagamoorthy Kanagasabapathy!) posted those instructions; so you can take them to the bank.

    There were two sets of instructions: one set for Open receivables (the instructions you posted), and one for Historical receivables (using tables RM30101 and RM30201).

    Another GP big gun, Frank Hamelly, recommended that "to prevent future occurrences, get the 'Doc Date Verify' chunk file from your GP Partner and install it. That checks for a valid fiscal period and will not allow posting into a period that is not setup and open." Though, depending on your version of GP, you can find Doc Date Verify in the GP menus without installing anything extra.

    The instructions have you look first (with SELECT queries) to pinpoint the proper records. The UPDATE queries actually make the changes to the tables.

    I was actually surprised to see that Mr. Kanagasabapathy did not post the usual stern warning about changing GP data outside of the menu system's user interface. Using an UPDATE query in GP isn't recommended unless the user is absolutely sure of the intended result.

    If your Administrator can try this in a TEST company first, I would highly recommend that course of action.

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    2018-12-06_08_06_34-Mozilla_Firefox.jpg
    ───────────────
    Excel Webinar List
    ------------------------------



  • 5.  RE: where can I find the RM20101 query

    Posted Jan 07, 2019 06:51 AM
    I am not sure those would be the only tables affected by a document date change.  Wouldn't RM00401 (the keys table) also require an update?  And yes, I would be very careful making changes on the back end.

    ------------------------------
    Gregory DeBacker
    Regal Lager Inc.
    ------------------------------



  • 6.  RE: where can I find the RM20101 query

    TOP CONTRIBUTOR
    Posted Jan 07, 2019 11:35 AM
    Hi @Akeda Wilkinson

    If it is more than 100 transactions then I would say to use SQL, otherwise you MUST use the GP User Interface.  Also, only use SQL if you are very, very sure of what you are doing and very, very sure of each and every table you need to hit.

    This is because those transactions and transaction dates can possibly be in at least 5 tables, all of them needing updating - and each of these transactions also may be in the Financial Tables, Tax Tables, you name it.

    Unless you are working with someone who is adept at data repair in Dynamics GP do NOT try to do this in the tables.

    Your best bet is to use GP to VOID these transactions and then enter them on their correct dates - this will use GP to hit all of the affected tables AND provide the necessary audit trail for what you have just done.

    Again, this is 100% NOT the correct use-case for updating SQL tables.

    ------------------------------
    Kindest Regards,
    Jo deRuiter , MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    GPUG Academy Instructor
    Dynamics GP Credentialing Council-Vice Chair
    770-906-4504 (Cell)
    ------------------------------