I’m trying to create the AR invoice report that our users want at BAQ. They want only the total amount to be displayed on one line, not the invoice details. So I took InvcHead instead of InvcDtl.
But, they want the report to include the Sales detail in SO and Shipment. This field is in OrderDtl, and when I connect OrderDtl, the invoice line increases by the number of order lines. So, I created an InnerSubQuery and Group-By’ed the necessary fields from Dtl.
After that I thought my report would be complete. But, I’m running into a problem I never expected. As shown in the screenshot below, several invoices are generated in two lines. These invoices are no different from other invoices, with one difference: they have a sales kit on the SO. I never expected that SalesKit would affect my invoice report.
I am sharing my report in the screenshot below, please let me know how I can solve this issue.
Thanks in advance!
I needed several fields in OrderDtl, and since linking them directly from Invchead on the first screen would result in duplicate lines equal to the number of Order lines, I grouped these fields in Subqueires.
There were no null values in fields imported from OrderDtl, such as Sales cat and Reference. And if the values of these fields in OrderDtl, such as Sales cat and Referance, change, we create an order separately. So, there can be no different Sales cat for each order. The only difference between these orders and other orders is that they have a Sales Kit, I haven’t found any other differences.
Combining your comment with the Saleskit issue, I think I know the cause. Thanks,
When it is a kit part, the Sales cat entered in the Order line is not imported into this field in Kit Components.
Do you know any way to solve this issue? Not only does it not fetch values, input is also disabled.
I just did a query to see the value of the sales category on the kit component lines on a sales order with the sales category for the parent, and the sales category for the kit components has a sales category being populated in it’s own right
My test consisted of a customer with a customer group and a sales category tied to that customer group.
Interestingly if you change the customer group and enter in the same kit parent on a new line the kit component line sales category reverts back to the one derived from the customer group.
Do you have some bpm that is overriding the behaviour. Perhaps the easiest method if you do is make sure the BPM is populating the kit component line sales cat id…
Faliing that you would need to split up your order detail lines querries one for kts compontents and other lines you for the kit components query you would need to join back to the orderdetail kit parent line to get the sales cateory and use that field to return the sales cat rather than the componetn line.
Here’s some sql, and probably an easier way to do this, but right now that’s all I can think of.
select orderdtl.kitflag,OrderDtl.SalesCatID,od2.SalesCatID,orderdtl.KitParentLine,od2.PartNum,OrderDtl.PartNum,* from erp.OrderDtl
Inner join erp.OrderDtl OD2 on OrderDtl.company = OD2.company
and OrderDtl.OrderNum = od2.OrderNum
and orderdtl.kitparentline = od2.OrderLine
where OrderDtl.kitflag = 'c'
and OrderDtl.company = 'EPIC06'
