Open Forum

AR "payment on account" does not show as open

  • 1.  AR "payment on account" does not show as open

    Posted 27 days ago
    We have a payment that was made to a new customer (#1) last month. It was not applied to anything, just a "payment on account". The AR Aging & AR GL were off by exactly this amount at month end so we knew what it was. However, we had another customer (#2) make a payment on account (they were in the same batch) that DID show on the AR Aging. Today, Jaime created the invoices for these customers & the payment from last month for customer #2 showed & she was able to apply it to the invoice.

    Payment for customer #1 does not show anywhere except in the AR GL account for 8-5-19 and on the Batch Control Report. In the Customer Maintenance, it does not show in Activity or History, and when running a quick query (AR Transactions) this payment does not show up (all others in the batch do tho) when searching by transaction amount, batch number, customer etc. Nowhere in Quick Query can we find this payment. When looking at the Batch Control Report there seems to be nothing done differently with customer #1 & customer #2. It does not show on the AR Aging as being open even tho it was not applied to anything and the customer's current total balance is for the new invoice that was created today - it does not reflect the payment made on their account last month. The payment hit the bank, so where would this payment go? There are no batches in limbo, everything else about this batch worked out just fine, its only THIS particular payment.

    Thanks, everyone!


    ------------------------------
    Erin Walsh
    Assistant Controller
    Inside Edge CIS
    Eagan MN
    ------------------------------


  • 2.  RE: AR "payment on account" does not show as open

    TOP CONTRIBUTOR
    Posted 27 days ago
    Hi Erin,

    Was the payment done thru Cash Manager / Cash Transactions?  If so, 1 of 2 things - it wasn't done to a tran type of PA which allows you to assign a customer OR it was done to the tran type of PA but no customer was entered.  Either 1 of these would reduce AR and increase cash, allow for clearing on a bank rec BUT not see it in AR Aging or the Customer....

    Hope this helps....let me know if it didn't....

    Thanks,
    Michael

    ------------------------------
    Michael O'Neill
    Director of Finance
    Eastex Telephone Cooperative
    Henderson TX
    ------------------------------



  • 3.  RE: AR "payment on account" does not show as open

    Posted 27 days ago
    It was not done thru Cash Manager, but thru AR - Payment Application. The Batch Control Report shows the customers name. I have searched to see if it was on a different customer's account but when I search by the exact payment amount nothing comes up (not even the customer that's listed in the batch.)

    ------------------------------
    Erin Walsh
    Assistant Controller
    Inside Edge CIS
    Eagan MN
    ------------------------------



  • 4.  RE: AR "payment on account" does not show as open

    TOP CONTRIBUTOR
    Posted 27 days ago
    Have you run the Payment Application report to see if it is on there?  You should see the batch and the customers where it was applied to or if it was an unapplied payment....

    ------------------------------
    Michael O'Neill
    Director of Finance
    Eastex Telephone Cooperative
    Henderson TX
    ------------------------------



  • 5.  RE: AR "payment on account" does not show as open

    Posted 27 days ago
    Nope, it does not show on the Payment Application report.

    ------------------------------
    Erin Walsh
    Assistant Controller
    Inside Edge CIS
    Eagan MN
    ------------------------------



  • 6.  RE: AR "payment on account" does not show as open

    TOP CONTRIBUTOR
    Posted 27 days ago
    Alrighty then....this is where I would start digging in SQL, but should be able to do the same in QQ...

    I would do things like get batch total from Batch...
    Total AR documents for that batch and make sure equal batch total...look at release status, should be 1....
    Total AR tran for that batch and make sure equals AR docs and total batch...look at release status, should be 1....

    Probably before doing the above digging, I would run AR integrity check to see if that fixes the issue prior to doing more research....

    Without doing a GTM and seeing things myself, I'm running out of ideas....lol

    Let me know if the integrity check didn't work and still out....we can do a GTM and see if we can't find the issue....

    ------------------------------
    Michael O'Neill
    Director of Finance
    Eastex Telephone Cooperative
    Henderson TX
    ------------------------------



  • 7.  RE: AR "payment on account" does not show as open

    SILVER CONTRIBUTOR
    Posted 26 days ago

    Hi Erin,
    Not sure if you have found the issue, yet, but I agree with Michael about checking some of the data in your tables...There is an inner join used in QQ_ARTRAN which requires data to exist in ARDOC, ARTRAN and CUSTOMER tables to see the row.

    You can start with these to make sure everything balances and you have data returned in each table; replace the variables at the top with your batch number, reference number, and customer ID

    Good luck!
    Marc

    declare @batnbr varchar(10) = '058400'
    declare @refnbr varchar(10) = '1234'
    declare @custid varchar(15) = 'ODOT0000'
    
    --customer
    select custid, name, status from Customer where CustId = @custid 
    
    --batch
    select batnbr, perpost, CrTot, DrTot, status from batch where batnbr = @batnbr and module = 'AR'
    
    --AR DOC
    select BatNbr, custid, perpost, rlsed, SUM(origdocamt) as bat_amt from ardoc where batnbr = @batnbr group by batnbr, custid, perpost, rlsed
    
    --AR TRAN TOTALS
    select batnbr, perpost, SUM(case when drcr = 'd' then tranamt else 0 end) as DrTot, SUM(case when DrCr = 'c' then TranAmt else 0 end) as CrTot from artran where batnbr = @batnbr group by batnbr, perpost
    
    --AR TRAN SPECIFIC REFNBR
    select batnbr, custid, perpost, DrCr, acct, SUM(tranamt) as tranamt from artran where batnbr = @batnbr and RefNbr = @refnbr group by batnbr, custid, perpost, acct, drcr
    
    --GL TRAN
    select BatNbr, Module, TranType, perpost, Acct, DrAmt, CrAmt, rlsed, Posted from GLTran where BatNbr = @batnbr and RefNbr = @refnbr and Module = 'AR'


    ------------------------------
    Marc DeLozier
    Finance SysAdmin
    David Evans and Associates Inc.
    Portland OR
    ------------------------------



  • 8.  RE: AR "payment on account" does not show as open

    Posted 25 days ago
    Running the AR Integrity worked! I was not aware that this should be ran regularly (still learning SL). Thanks so much for your help!

    ------------------------------
    Erin Walsh
    Assistant Controller
    Inside Edge CIS
    Eagan MN
    ------------------------------