Invoice Total Revenue Calculations

API Q&A
#1

Hi there,

I want to calculate the Total Invoice Revenue coming via Infs API

I can see there are three tables:

  1. Invoice
  2. Invoice Item
  3. Invoice Payments

Needed some help and clarity in the formulae that we should use to get the Total, Gross Total of Revenue using these three tables, columns and variables involved and the filters that should be used. Questions below

  1. What filters should we use to remove orders that were refunded/not paid etc? Any other filters to be used here for totals and gross totals - discounts etc?
  2. What’s the difference between “TotalDue” and “TotalPaid” in Invoice table?
  3. What should the final formulae of “Totals” and “Gross Totals” look like?

Let me know your thoughts/answers or point me to the right directions, please. Thanks!

#2

@Pravin_Singh, I may need some clarifying info to help further.

“TotalDue” should be the sum of order items on an invoice
“TotalPaid” should be the sum of total payments toward that invoice total minus any refunds

“Gross” revenue should be the sum of “Total Paid”

Are you seeing your expected amount, or does it seem to be off?

1 Like
#3

Thanks @Carlos_Ochoa for the quick reply here!
Actually, I’m still testing the numbers and will revert on how off or matching they are soon.

Some more follow up questions to get full clarity here:

  1. What’s the definition of “InvoiceAmt” in “InvoiceItem” table - I can see very off values and negative values also here. They are all very different from the “TotalPaid” before in Main invoice table

  2. I’m actually joining Invoice - InvoiceItem to get the Item descriptions mainly.
    So, I can imagine that each order that has shiopping, taxes gets converted to multiple rows
    While doing this join, I’m getting multiple orders (rows of data) for same InvoiceId with different InvoiceAmt values - what does this mean - multiple orders done on 1 invoiceID only?

  3. Should we also join Invoice with InvoicePayments to get all APPROVED Payments only to get correct values OR does “Totalpaid” and “PayStatus” = 1 in Invoice table take care of this?

  4. Should we just pick the top value of each partition of Invoice ID while joining and merging these 3 tables?

Basically, just want to understand how to merge these 3 tables so that I can get All invoices, their descriptions, their total transaction values.

Kindly help me out in understanding the underlying variables and schema as the docs are not answering them.