Find orders where misc header charge doesn't match the invoiced amount

Kinetic ERP
1

We just ran into a scenario where the order’s freight amount (misc header charge, freq = first) was changed, AFTER the first shipment (of some little cheap parts). Since the misc charge is set up as a frequency of FIRST, it’ll never get invoiced again.

How can I search for other similar orders in a BAQ?

Basically, looking for:
OrderMsc.MiscAmt != InvMisc.MiscAmt where MiscCode = ‘FRTS’.

Both have OrderNum in common…

Can I compare it with a calculated field? Or do something else? I figure the query will be a dog, since it’s crawling EVERY order & invoice (I’d set a date range).

Any ideas or best practices?

Thanks!

2

Link the two tables on OrderNum
Also link on OrderMisc.MiscAmt != InvMisc.MiscAmt

Add criteria.

Left join appropriate master table.

I doubt the performance will be horrible.

3

Could also use two subqueries, just link them on order num.
Do a calc field to compare, true or false.

In top query, only pull the trues.

1 Like
4

Awesome, thanks Kevin! That worked - I had to go with the subquery route. Turns out InvcMisc did not have the OrderNum field to join with…

And the calculated field syntax errored on:

OrderMisc.OrderMsc_MiscAmt = InvcMisc1.InvcMisc_MiscAmt

Had to change that to:

case when (OrderMisc.OrderMsc_MiscAmt = InvcMisc1.InvcMisc_MiscAmt) then 1 else 0 end

There were a ton of false positives, due to the order and invoice having multiple rows of the FRTS misc header charge. I added description equals as join criteria, which knocked it down 80%, but there are still times where it’s either listed twice with the same descr, or the same descr, but one is capitalized (and BAQ’s criteria isn’t case sensitive it seems).

I tried to add a sort by Amount to the subqueries, but that also errored out.

image

Any ideas how I can get that to sort before the calc field (on the top level) does the comparison?

5

So what are you working with then?

6

I added InvcHead (which has OrderNum) to start the subquery, and joined InvcMisc to that.

image
image740×295 20.3 KB

I should have probably named the InvcMisc subquery to something other than the table name (facepalm) :person_facepalming:

7

I can’t really help with the order by, but you can probably use some windowing functions to group
those out to be listed nicely.