GI on Invoice and SalesOrder

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:

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 @jinin,

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

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

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.