Open Forum

Expand all | Collapse all

Receivable showing up after a year with strange amount

Jump to Best Answer
  • 1.  Receivable showing up after a year with strange amount

    TOP CONTRIBUTOR
    Posted May 07, 2019 06:46 PM
    My controller is trying to get through an audit and she is saying she is seeing an open receivable back on the A/R after being closed (with zero balance) over a year ago.  Can anyone help me out?

    Here is the receivable record (RM20101)

    CUSTNMBR DOCNUMBR    DOCDATE    OriginalTxAmount CurrentTxAmount SalesAmount DiscountTaken
    SOB100        PEK0056408    2018-02-09                  5648.65                 0.00000          3077.10     61.54

    The difference between the Original Transaction Amount and the Sales Amount is what is appearing on the A/R.

    Here's what the original invoice had on it:

    SOPNUMBE     DOCDATE   DocumentAmount    INVODATE      DiscountAvailable InvoiceSubtotal OriginalInvoiceSubtotal ExchangeRate Canadian
    PEK0056408    2018-02-09      3077.09                2018-02-09        61.54                    3077.10             3879.04                       0.7932620       3879.03

    Can I just adjust the Receivable record manually and set it back to the correct amount?

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------


  • 2.  RE: Receivable showing up after a year with strange amount

    GOLD CONTRIBUTOR
    Posted May 08, 2019 08:53 AM
    Blair,

    I wish I had a simple solution for you.  But, I don't.  Here's all I can offer...

    When things mysteriously appear in GP, I usually blame checklinks.  Checklinks usually leaves its fingerprints on the transactions somewhere.  If this is the case, if you simply update or remove the open trx, I bet it will mysteriously appear again the next time you run checklinks.

    I would dive into all of the history tables that hold that transaction (SOP and A/R) and look for inconsistencies.

    Here are the tables I would start with...  SOP30200, SOP30300, RM30201, RM30101, RM00401...

    I hope this helps.

    Chris

    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------



  • 3.  RE: Receivable showing up after a year with strange amount

    TOP CONTRIBUTOR
    Posted May 08, 2019 11:10 AM
    @Blair Christensen

    Checklinks could very well be the culprit, but reconcile might also be the culprit.

    The question is, why is checklinks changing it?  It's likely because there is a table that has different amounts and it is altering this transaction based on what is in that table.

    Please use one of the search scripts or use the locator tool and find all of the tables that SOP Number exists in, find the bad data and run the update to correct THAT data.

    If it bad data does not exist, the you can update to the correct amounts, but use the results of the search tools and make sure you update EVERY table that the transaction exists in or the next time someone runs CL or Reconcile then the nightmare will repeat.​

    ------------------------------
    Jo deRuiter, MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    770-906-4504 (Cell)

    ------------------------------



  • 4.  RE: Receivable showing up after a year with strange amount

    TOP CONTRIBUTOR
    Posted May 09, 2019 09:49 AM
    @Jo deRuiter "Please use one of the search scripts or use the locator tool"

    I'm not familiar with either.  Can you point me to either or both of these resources?​

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------



  • 5.  RE: Receivable showing up after a year with strange amount

    TOP CONTRIBUTOR
    Posted May 09, 2019 06:08 PM
      |   view attached
    This is an old tool that sits outside of SQL and queries based on numerous parameters, but if used right can find a transaction in any table.

    It is attached.  Simple install, very benign.


    ------------------------------
    Jo deRuiter, MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    770-906-4504 (Cell)

    ------------------------------

    Attachment(s)

    zip
    LocVal.zip   11K 1 version


  • 6.  RE: Receivable showing up after a year with strange amount

    TOP CONTRIBUTOR
    Posted May 10, 2019 07:29 PM
    Hi Blair,

    To clarify...
    • Do you mean that these transactions were in HISTory, but now they are back in OPEN status?
    • or... are you saying that the historical aged trial balance report is picking up these transactions despite being fully paid?

    Additional questions...
    • Do the amounts in the transaction tables (RM20101 / RM30101) match with the amounts in the apply tables (RM20201 / RM30201)?
    • Are there values in in the 3rd, 4th, 5th decimal places?


    ------------------------------
    Ryan Galang
    BTP Technologies LLC
    Chantilly VA
    ------------------------------



  • 7.  RE: Receivable showing up after a year with strange amount

    TOP CONTRIBUTOR
    Posted May 13, 2019 09:46 AM
    According to my controller, it is an invoice which was paid in full back in February of 2018 that is now showing up again on the Aged Trial Balance.  And yes, it is being listed as Open.  It looks like the only thing goofy with it is the original transaction amount is suddenly out of whack, because it is showing 0 as the amount still open on the transaction itself.

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------



  • 8.  RE: Receivable showing up after a year with strange amount

    TOP CONTRIBUTOR
    Posted May 13, 2019 10:34 AM
    Hi Blair,

    To clarify... is the aging report being used is the "Aged Trial Balance" Report (vs Historical ATB)?
    If so, the Aged Trial Balance report is designed to show all transactions in OPEN status (even if fully applied)...

    So assuming that there's nothing wrong with the Apply amounts... Have you tried to re-run Paid Transaction Removal (PTR) to move change back the status of the transaction to HISTory?



    ------------------------------
    Ryan Galang
    BTP Technologies LLC
    Chantilly VA
    ------------------------------



  • 9.  RE: Receivable showing up after a year with strange amount

    Posted May 13, 2019 11:09 AM
    Edited by Michael LaFave May 13, 2019 11:11 AM
    Blair,

    I am not one of the GP Guru's by any means.  But, did someone unapply the original payment then tried to reapply it?  If it happened, the issue maybe because they are trying to post it in a closed period.  You may need to go an reopen the closed period and post again.  Make sure the payment is applied as the original and all the GL Codes are present before you post and master post.​

    Just my thought, best of luck

    ------------------------------
    Michael LaFave
    Innovation Ventures, LLC
    Farmington Hills MI
    ------------------------------



  • 10.  RE: Receivable showing up after a year with strange amount

    TOP CONTRIBUTOR
    Posted May 13, 2019 12:18 PM
    The original post does not state this explicitly, but what I think the problem is that the Historical Aged Trial Balance (HATB) is incorrect.
    I have written this blog about this:

    https://dynamicsgptipsandtraps.wordpress.com/2018/07/23/fixing-the-pm-rm-historical-trial-balance/

    My blog also has several other blogs on balancing the GL to the subsidiary ledgers.

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



  • 11.  RE: Receivable showing up after a year with strange amount

    Posted May 15, 2019 11:44 AM
    Silly question, but are you running a modified or alternative version of the report?  It could be as simple as a flaw in a customized report.  Find out what Modified/Alternative profile the user has assigned to them, then look at this profile.  Is there a customized or alternative version of the report assigned to the profile.  Change the user to run the standard report and see if you receive the same results.

    It never hurts to rule out the basic causes first.  I have seen hours trying to figure out why a trial balance was out of balance only to find out that the user was running a bad modified version of the report.​

    ------------------------------
    Bill Jones
    Systems Engineer, Financial Systems
    Universal Service Administrative Company
    Washington DC
    ------------------------------



  • 12.  RE: Receivable showing up after a year with strange amount

    TOP CONTRIBUTOR
    Posted May 16, 2019 10:54 AM
    While I appreciate the suggestions that it may only be a "reporting" issue, that is not consistent with the actual data from the tables which inexplicably shows an original transaction amount nearly $2500 higher than the invoice.  I want to know how to fix the receivable and if I can simply set the original transaction amount back to match the sales amount without causing issues.

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------



  • 13.  RE: Receivable showing up after a year with strange amount

    GOLD CONTRIBUTOR
    Posted May 17, 2019 08:06 AM
    Blair,

    I would go ahead and fix the inconsistencies in the tables.  Be sure to do it in a test company first and run reconcile and checklinks after to ensure they don't identify any corruption or inconsistencies again.

    Good luck!

    Chris

    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------



  • 14.  RE: Receivable showing up after a year with strange amount

    GPUG ALL STAR
    Posted May 20, 2019 08:37 AM
    @Blair Christensen

    I would also make sure to check the rmkeys table rm00400 as I believe the totals are also stored there.​

    ------------------------------
    Bert Green
    GP Developer/GP Administrator
    Berger Transfer & Storage, Inc.
    Saint Paul MN
    GP2015/MR CU11
    GPUG All Star 2017
    ------------------------------



  • 15.  RE: Receivable showing up after a year with strange amount
    Best Answer

    TOP CONTRIBUTOR
    Posted May 20, 2019 09:21 AM
    So just to let everyone know, I changed the Original Transaction Amount via SQL and that seems to have done the trick.  No idea what happened, but it looks to be solved now.  Thanks to all for your suggestions.

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role