How to determine total amount customer has paid so far on an order

Epicor ERP 10
, , ,
1

How can I find for a given order how much the customer has paid on that order?

I have a BPM which has been looking at OrderHed.DocDepositBal and this has been working for a few weeks, but recently we have been hit with a spate of orders that this has failed on.

If I go to Customer Tracker > Financial > Payments and look at both applied and unapplied deposits I can total these up and they correspond to how much the customer has paid. At least I think this will always be the case. But there doesn’t appear to be a simple field I can look at. The DB field appears to be CashHead.DocTranAmt. My thought was to match all records in this table with the OrderNum, but there are both OrderNum and InvoiceNum fields - sometimes one or the other is 0.

Is it sufficient to totally all the records where OrderNum matches the order in question, either directly or as the result of a join where the InvoiceNum matches the OrderNum in the InvcHead table? My test cases work so far, but I want to make sure I’m not missing any cases I haven’t thought of. For instance, is it possible that this query my double-count something or miss certain types of payment?

When written as SQL my query looks like this:

SELECT SUM(CashHead.DocTranAmt) FROM CashHead
LEFT JOIN InvcHead
ON InvcHead.InvoiceNum = CashHead.InvoiceNum
WHERE CashHead.OrderNum = XXXXXX OR InvcHead.OrderNum = XXXXXX;
2

I think the OrderHed.DocDepositBal field will only increase with cash receipts from “deposit billing” invoices. I advise users to reference order payments by going through the invoice activity in Order Tracker.

Your new query should do it though! Make sure it’s including write off/adjustments, credit memos, and payments that originally come in as unapplied receipts.

Depending on the fields you need, an alternate solution would be to use the InvcHead.DocInvoiceBal field (could subtract that from InvcHead.DocInvoiceAmt if you need a payment amount instead of a balance number).

3

I’m not doing any filtering to remove those things, is there anything extra I need to do to make sure I’m including them?

4

You could check the CashHead.TranType field. It should display Payment vs. Credit Memo vs. Adjustment.

5

" …there are both OrderNum and InvoiceNum fields - sometimes one or the other is 0."

You might want to use CashDtl instead of CashHead. I think the OrderNum will be 0 when customers send in payment for multiple orders on one check. In my reports for project payments, I am using CashDtl because of this.

1 Like