In Customer Tracker -> Financial -> Credit -> Credit Detail there is a field called “Order Credit” (field help says Customer.TotalOrderCredit).
We need a dashboard with these totals for all customers, however when I make a BAQ and add customer table I don’t see Customer.TotOrderCredit. Do I need to create this by connecting Customers -> OrderHed -> OrderDtl -> OrderRel? If so, I’m concerned this will really bog down… any suggestions?
It is not actually stored in a DB table
I believe the “Customer” referenced in the EpiBinding is a view that is created by the Customer Tracker.
To get that, you would need to link to OrderDtl and OrderRel, and sum up the value of the open releases. To be complete you’d need to include Misc Charges (both for Order and Lines), which could be messy in figuring out what is still chargeable.
Thank you! The Misc Charges is where I ran into some problems.
Depending on the type, Misc Charges could be impossible to calculate. Particularly if there is an “every shipment” type. You’d have to assume that each release would be just one shipment.
@Adam It is all stored in the ERP.GLBCustCred table.
The Order Credit is ERP.GLBCustCred.SOTotal and Invoice Credit is ERP.GLBCustCred.ARTotal. Keep in mind that these are not always correct, which is why there are quite a few data fixes that you need to keep up on with these fields.
I thought it was GlbCustCred
Is that the Customers Credit Limit? Or a running balance of the remaining Credit?
Customer.CreditLimit is the customer’s credit limit. GlbCustCred has the ARTotal, SOTotal, and PITotal (open amounts). However, in 10.0, there is a bug that this value is not always accurate (even after using the conversion workbench to try to fix it).
Jason, was it only in 10.0?
We just moved from 10.0 and I am really hoping it has gone away in 10.2.500…
Either way, did you have a BAQ or SQL to show you customers where this is off?
I am working with Epicor Support and they give me a data specific fix where I have to supply all of the customers where this is happening.
I know they have the logic built into their fix to source what the new credit totals should be so I’m hoping they’ll give me the source query so I can run it and find the customers where current <> new calculation per their fix.
Thanks for any details.
-Utah
Yes. Do a sum of each of the tables (i.e. InvcHead, OrderHed, etc) and compare that to the values in GlbCustCred. I don’t have a BAQ, because we knew it was almost always wrong, so I wrote a BPM to keep it in sync.
Interesting… do you still use that BPM to this day? What version are you on now?
That is what I was going to do, I just didn’t know if that’s exactly how they were doing it.
Thanks Jason!
I don’t. It wasn’t hard to create though.
Thanks!