Dear Team,
I have prepared BAQ to get the data of Order Qty vs Invoice. In this I am getting duplicate line of order entry because there is one order line but there are multiple invoices have been created. Logically that is correct where there is one order but partially shipped and partially invoiced but I want accurate data where I can get order qty, invoiced qty and balance qty in BAQ… Are there any ways to get these combinations ?
Hi
Try going from Order to Shipment to Invoice linking on Order Number, Order Line and Release to get all the details
then using that data as a sub-query you can create a top-level query that sums everything up
1 Like
I wanted to bring to your attention an issue I encountered with the BAQ report. The report is correctly showing the invoice quantities, but when it comes to the order quantities, it is showing them multiple times. Here’s the situation:
For example, let’s say we have Order A with an order quantity of 100. We received two separate invoices for this order: one for 50 qty and another for 50 qty. However, in the BAQ output, the order quantity is being displayed twice—once for each invoice. It shows:
- Order qty = 100 — Invoice qty = 50
- Order qty = 100 — Invoice qty = 50
I have a BAQ that does this along with some other things…
In my BAQ I have a subquery of the invoices that does an aggregate to sum the SellingShipQty and groups on the part number, order number, order line and company. This provides me with the total quantity invoiced regardless of how many times we’ve invoiced against the sales order. I link that subquery to my Top Level on the company, order number and line number and do my calculations to get the balance.
Hi @KPreda , Thanks for your valuable reply but in that I have not received Invoice number details, if I add invoice number then system again duplicating the lines.
I’m likely not going to explain this very well…
If I were to add the invoice number to my subquery that’s summing the quantities I would have duplicates as well. My subquery would then be summing the quantity shipped per part per invoice number.
Maybe I misunderstood what you’re trying to accomplish.
What my BAQ does is shows
The quantity shipped is an aggregate total of the quantities invoiced, it works because in my subquery the invoice number isn’t needed. I link my subquery on the sale order detail in my top level on the order number and order line.