I need a report that highlights discrepancies in tax rates between Sales Order line items and their corresponding line items on invoices.
A single Sales Order often can be associated to multiple invoices due to billing schedules. A single a sales Order can generally associated to many invoices (since there are some billing schedule)
1. Sales Order Line Tax Rate
I attempted to use a saved search to extract the tax rate for each line item on a Sales Order. However, a Sales Order lines may have varying tax rates.
On my saved search, I used the formula (currency) to derive Tax rate for a line as you can see below ABS(({taxamount}+{netamount})/nullif({amount},0)-1)
Here is how I set up my search & its result
However when we examine the 2 first SO line, we clearly see that the rates are different. So 7% (0.07) for all the SO lines are not accurate...
So How can I accurately display the tax rate applied to each Sales Order lines.
2 What methods to use?
As previously mentioned, while a Sales Order may generate multiple invoices, my primary focus is on flagging those invoices that exhibit discrepancies in tax rates.
For example, if a Sales Order line shows a tax rate of 8%, but one of its corresponding invoices lists a rate of 0% for the same line, this discrepancy should be flagged in my report
So is there a method to :
a - Extract the tax rate of each Sales Order line,
b- Compare it against the tax rate of every linked invoice line (found in the related record subtab),
c- And then flag any mismatches?
Would this require a standard saved search, SuiteQL, or perhaps a script? I'd appreciate any insights or suggestions.
Thanks!