Open Forum

Fix for incorrect future date?

  • 1.  Fix for incorrect future date?

    Posted Feb 05, 2019 09:15 AM
    In Dynamics SL 2015, is there a fix to eliminate transactions posted with an incorrect future date to prevent them from showing up
    on the daily cash balance by date? CA Integrity Check has been run, but it did not correct the problem.

    ------------------------------
    Carol Willis
    Just Dough It, Inc
    Camdenton MO
    ------------------------------


  • 2.  RE: Fix for incorrect future date?

    BRONZE CONTRIBUTOR
    Posted Feb 06, 2019 08:47 AM
    It requires some fairly detailed corrections in several tables.  If you're not familiar with the SL tables you'll need to get some assistance.  I've  made the correction once but had someone walk me through it so I'm not comfortable enough with the process to help myself.  Sorry!

    ------------------------------
    Robert Guerriero
    Director of Accounting
    Telephone Electronics Corporation
    Jackson MS
    ------------------------------



  • 3.  RE: Fix for incorrect future date?

    SILVER CONTRIBUTOR
    Posted Feb 07, 2019 06:43 AM
    Carol,

    To fix this issue, you will require some SQL script knowledge.  When a transaction date for cash is create outside of the normal time frame, a record is created out to that point in time and ultimately the application will recalculate the daily cash balance out to that point in time whenever a batch affecting cash is processed.

    I have typically done the following in SQL:
    delete from CashAvgD where PerNbr > 'xxxxxx' and BankAcct = 'yyyy'   -- I usually use the next period in the future to replace the xxxxxx and the proper cash account for yyyy
    delete from CashSumD where  PerNbr > 'xxxxxx' and BankAcct = 'yyyy'   -- I usually use the next period in the future to replace the xxxxxx and the proper cash account for yyyy

    If the offending record is in Accounts Payable (AP check)
    select * from aptran where trandate > 'current date'  -- This should provide a list of all dates in the future
    update aptran set trandate = 'xx/xx/xxxx' where refnbr = 'yyyyyy' and trandate = 'zz/zz/zzzz'  --  'xx/xx/xxxx' should be the correct date and the yyyyyy is the record identifier and 'zz/zz/zzzz' is the original bad date

    If the problem date is in GL, use the same scripts using GLtran instead of APtran
    If the problem date is in AR, use the same scripts using ARtran instead of APtran

    Then go back on run the CA Integrity check.

    ------------------------------
    David Callery
    Consultant
    Coe & Company, LLC
    New Orleans LA
    ------------------------------



  • 4.  RE: Fix for incorrect future date?

    Posted Feb 07, 2019 08:54 AM
    Will the integrity check rebuild from the start?  We did not start using Cash Manager for several months after implementing SL, so we have a great deal of OD, OM, etc. transactions that did not affect the GL. Will this be an issue?

    Thanks for your help.

    ------------------------------
    Carol Willis
    Just Dough It, Inc
    Camdenton MO
    ------------------------------



  • 5.  RE: Fix for incorrect future date?

    SILVER CONTRIBUTOR
    Posted Feb 07, 2019 09:58 AM