Finance looks at customer tracker > Invoices > All to get a sense of how much business a particular customer does and how much credit they need if we offer terms. Unfortunately, the invoices subtotal doesn’t show up(see screenshot below). I looked at Field Help and the subtotal appears to be a calculated field, also looked at InvcHead and InvcDtl and I can’t seem to figure out where the $897.75 figure is coming from. The InvcDtl.DocExtPrice is $855. Finance wants that $897.75 figure to show up on customer tracker, but I don’t know how to do that(ideally unhide some field just don’t know what it is) or even create them a new BAQ but I can’t find that data in the DB. Can someone help me out?
Alternatively is there another report they can / should be looking at that already provides this information?
@TommyD the problem I’m having is in the screenshot invoice 80409 has a subtotal of $897.75, but in customer tracker invoice 80409 shows $0 values for all fields. None of the fields displayed show a dollar value > $0.
I just ran into a similar thing on the Vendor side. The ExtCost for every PO - prior to a certain date - is zero.
It shows on the PO Tracker too…
At first it looked like it aligned with a recent upgrade (10.1.400 -> 10.2.300) on 12/15/2019. But there are PO’s creted before that date that do properly calc the Ext Cost.
@ckrusen thanks for your reply. Sorry I’m not very familiar with finances so I’m not following your reply. Are you suggesting this is a bug I should take to support(Why is the Invoice Amount $0?) or is this expected behavior?
I did figure out how to show the extPrice for finance(minus taxes, and misc charges I understand), but I didn’t figure out what fields will give me the illusive $897.75 value I see in the subtotal of AR Invoice Tracker…
This could be different depending on which version you are, but the basic formula is:
SubTotal = Invoice Amount + Prepaid Deposits + Cash Deposits + Lines Advance Billing - Taxes - Rounding
So in your example it is basically SubTotal = Cash Deposits
At least on the most recent version you can find this amount in InvcHead.DocDepositCredit, doesn’t look like Prepaid Deposits are kept on InvcHead but rather calculated from other tables.
Displays the total amount that this customer owes or is credited on the invoice. This amount is calculated by subtracting the Less Deposits from the Line Amounts and calculating the tax.
Logic:
Sales Tax = (Line Amounts – Less Deposits) x Sales Tax % Total = (Line Amounts – Less Deposits) + Sales Tax
Meaning that is what the customer initially owed on that invoice.
Advanced Invoices are made prior to the shipment or final billing.
Inv 1000 - An Advanced Invoice on Order 1234, of $500
Packer 4321 - Packer for Order 1234
Inv 1001 - A Shipment invoice for packer 4321. The order that packer 4321 refrenced would make $500 of billing. But since $500 has already been invoiced under Inv 1000, Inv 1001 nets to $0.
Thanks for the explanation of how one can calculate an Invoice SubTotal. Unfortunately I don’t know where those fields live in the DB, so I’m still stuck. I just looked through all the example BAQs(starting with z) and don’t see any that demonstrate how to calculate an Invoice Subtotal.
Is is possible you could break down the calculation to DB table & Field(s), or is there a reference of some sort I can look at to see how to calculate an invoice subtotal?
This formula seems to work for the few Invoices I’ve tested: InvoiceSubtotal = InvcDtl_DocExtPrice + InvcDtl_DocTotalMiscCharge
@embedded
For what your Finance Team is trying to accomplish they may want to consider the Aging tab in Customer Tracker > Billing, there are summaries there that may be helpful.
Also, check out what Customer Credit Manager can offer.
In most cases I have found that Finance is better off using Customer Credit Manager instead of Customer Tracker for 90% of their tasks.
Logic: