GI on Invoice and SalesOrder

Hi @Leo05  Assuming that you are using SO behavior Sales Order but not the Invoice Type.

If yes, below is the query to link the Sales Order to Invoice. You can use the same joins in the GI to fetch the details. Let me know if you need any help on the GI.

select * from SOOrder S inner join SOOrderShipment SS on S.CompanyID=SS.CompanyID and S.OrderType=SS.OrderType and S.OrderNbr=SS.OrderNbr
Inner Join ARTran AR on AR.CompanyID=S.CompanyID and AR.SOShipmentType = SS.ShipmentType and AR.SOShipmentNbr = SS.ShipmentNbr
Where S.CompanyID=2 and S.OrderNbr='SO006683'

Thanks for reply but I need a bit different stuff. Im tracking Invoice updates and need to filter invoices by sales order properties. So I need to build relations between AR.ARInvoice and SO.SOOrder tables.

Hi @Leo05 ,

Please find the below GI sample. You can add conditions and result grids based on your requirement.

Thanks @jinin,

The problem I do not have permissions to import GI if possible could you please create screenshot with relations tab?

Hi @Leo05 

Please find the screenshots
 

@jinin This is helpful - thank you! When I run this, I get a lot of duplicate lines. I’m a newbie so there might be something basic I’m missing. Or is it the way the tables are joined/ related? Thank you.

I used group by and I think it is working now. Thank you anyway.

Has anyone managed to join the ARTran, SOOrder and SOLine tables without causing duplicates?

Hi @Bree612 Please find the attached Generic Inquiry as a solution.
We could join the ARTran, SOOrder and SOLine without causing duplicates with the below limitations.

Limitation: The Sales Order has one Shipment and One Invoice.

Screenshots for reference: