Open Forum

Like what you see? Discover the benefits of the GPUG Community. Learn More

Can document date be changed?

  • 1.  Can document date be changed?

    Posted 11 days ago
    We have a payables invoice that ended up with a doc date many years into the future is there a way for me to change this date?  Or what would be the implications if it doesn't get changed?

    ------------------------------
    Andy Berntson
    Fargo Public Schools
    Fargo ND
    ------------------------------


  • 2.  RE: Can document date be changed?

    TOP CONTRIBUTOR
    Posted 11 days ago
    Edited by Shari Bruno 11 days ago

    ​Hi @Andy Berntson

    If it is just the document date that is in the future the only implications are that the invoice will not show up in searches that include a document date qualifier, and you will not be able to delete the invoice until a fiscal period for that future date is setup.​​ But, the document date can be updated through SQL. I don't have experience with this so someone better versed in SQL will have to advise on how to do this.

    To prevent this from happening again, in the PSTL there is a tool called Doc-Date Verify that prevents document dates from being entered that do not fall within a fiscal period that has already been set up.



    ------------------------------
    Shari Bruno
    Accounting Operations Manager
    Bible League International
    Crete, IL
    ------------------------------



  • 3.  RE: Can document date be changed?

    Posted 11 days ago
    Thanks for the reply!

    Can anyone walk me through the sql update?  I can see the transaction on the pm20000 table, is that the only place i would need to update the date?  I've also never done an sql update with the date format that GP uses.

    ------------------------------
    Andy Berntson
    Fargo Public Schools
    Fargo ND
    ------------------------------



  • 4.  RE: Can document date be changed?

    GPUG ALL STAR
    Posted 11 days ago
    Edited by Steve Erbach 11 days ago
    @Andy Berntson,

    It would be best if you got help from your Partner to do this. In GP it's rare that only one table needs to be updated!

    Were you aware that the Microsoft development team for Dynamics GP is right there in Fargo?

    Sincerely,

    ------------------------------
    IRue5vF6TOmaX3JIYzVK_2018-09-30 13_42_40-Deer in the Headlights - Opera.jpg"Sparkly" Steve Erbach - QMS Manager and Business Development Analyst
    GLC Minerals, LLC - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    GP 2016 R2 (16.00.0579) / MR 2012 CU16
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------



  • 5.  RE: Can document date be changed?

    TOP CONTRIBUTOR
    Posted 11 days ago
      |   view attached
    Hi @Andy Berntson

    That transaction could be in several tables, but is generally in at least two.

    If there were taxes, there will be an entry to the tax tables, if a credit memo has been applied it will show up in apply tables, if it was paid it's in many more tables.

    I have attached a tool I use to find transaction when I need to repair them.  Here are screenshots.  I did research on one transaction in my test company it it was in these tables - and I've named a few of the tables for you here.
    --PM10100.VCHRNMBR PM Distribution Table for Work and Open
    --PM10200.VCHRNMBR PM Apply to for Work and Open
    --PM10500.VCHRNMBR PM Tax Table​


    *CAUTION:  Data Repair in GP can be tricky so if you are not used to it or are not 100% comfortable, please reach out to a partner for assistance.*

    Let me know if you have any other questions.


    ------------------------------
    Kindest Regards,
    Jo deRuiter , MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    Advanced Credentialed Professional-Dynamics GP
    GPUG Academy Instructor
    Co-Chair: GPUG GP Credentialing Exam Council
    Chairman, GPUG Partner Advisory Board
    Principal Financial Systems Consultant
    Milwaukee, WI
    770-906-4504 (Cell)
    ------------------------------

    Attachment(s)

    zip
    LocVal.zip   11K 1 version


  • 6.  RE: Can document date be changed?

    GPUG ALL STAR
    Posted 11 days ago
    Edited by Steve Erbach 11 days ago
    Interesting, @Jo deRuiter. I use a SQL-only version of this app; so it has to be used w/SSMS or with the GPPT SQL statement executer.



    Where did this app come from?​

    Regards,

    ------------------------------
    tPAQcOVkRAmrMTWEsS3Y_Emerald25pct.jpg"Sparkly" Steve Erbach - QMS Manager and Business Development Analyst
    GLC Minerals, LLC - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    GP 2016 R2 (16.00.0579) / MR 2012 CU16
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------



  • 7.  RE: Can document date be changed?

    TOP CONTRIBUTOR
    Posted 11 days ago
    Hi @Steve Erbach

    I can no longer remember where I got it, but I can't live without it now, LOL​

    ------------------------------
    Kindest Regards,
    Jo deRuiter , MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    Advanced Credentialed Professional-Dynamics GP
    GPUG Academy Instructor
    Co-Chair: GPUG GP Credentialing Exam Council
    Chairman, GPUG Partner Advisory Board
    Principal Financial Systems Consultant
    Milwaukee, WI
    770-906-4504 (Cell)
    ------------------------------



  • 8.  RE: Can document date be changed?

    GPUG ALL STAR
    Posted 11 days ago
    Edited by Steve Erbach 11 days ago
    @Jo deRuiter,

    I've become accustomed to using the stored proc that returns the same results:

    EXECUTE dbo.uspSearchAllTablesForAnything
        @ValuePatternToSearchFor = N'%181137';

    This is a sample of the output:

    The main advantage of the stored proc is that I can supply additional parameters:

    Value to search for; can be alpha, numeric, timestamp, or GUID.
    Column name pattern;
    Table name pattern;
    Search In XML fields: 0 = No (default); 1 = Yes
    Show Full Row Results: 0 = No (default); 1 = Yes
    Number Of Rows To Show: Max number of full rows to display



    Anyway, I was intrigued by the app. It appears to produce results faster than the stored procedure.

    Sincerely,


    ------------------------------
    DQ9W2VY6T9S3zTUYM0ID_AllStar64.jpg"Sparkly" Steve Erbach - QMS Manager and Business Development Analyst
    GLC Minerals, LLC - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    GP 2016 R2 (16.00.0579) / MR 2012 CU16
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------



  • 9.  RE: Can document date be changed?

    TOP CONTRIBUTOR
    Posted 7 days ago
    The reason why the document date cannot be changed for posted transactions is that is horrible internal control, and if your internal or external auditors discover you are changing document dates in posted transactions, they will likely execute you and condemn you to purgatory.

    ------------------------------
    Bruce Strom
    Programmer Analyst
    Associated Grocers of Florida / Supervalu
    Sunrise FL
    ------------------------------