Payments not applied correctly to Invoice and Sales order

Overview

This article provides a solution when payments are not getting applied correctly to an Invoice and corresponding Sales order. 

When this issue occurs, users will report having transactions showing up in Everest Receivables that are not supposed to be there. In addition, deposit payments on Sales Orders will not be applied correctly on the Invoice when the items are shipped.

Solution

When a Sales Order where the payment is being received is not showing the correct outstanding balance, i.e. instead of showing the overpayment balance, it is showing 0 (zero) balance, this can be corrected by adjusting the balance through the back-end directly using the following SQL query:

--getting internal invoice ID
select invoices_id from invoices where status = 8 and order_no = '<order number>';

--Running update
update invoices set paid_amt  = <outstanding>, paid_sofar = <total paid>, pd_amt_fex  = <outstanding>, pd_sfar_fex = <total paid> 
where invoices_id = <result from previous query> and order_no = '<order number>';

--purge the associated receipt journal
update BCH_TRAN set AMOUNT_FEX = 0 where BATCH_NO = <receipt_journal#>;
update BATCH set DR_AMT = 0, CR_AMT = 0 where BATCH_NO = <receipt_journal#>;
update BCH_HEAD set IS_SYSTEM = 'F' where BATCH_NO = <receipt_journal#>;

Testing

After the balance is updated, the user should be able to refund the payment normally and the payments should apply correctly to the Invoice and Sales Order.

Back to top