I'm working on a query to join GLTran data to Sales Order (SOOrder or SOLine) and need some pointers if anyone knows.
From what little I could find, I need to join GLTran and Sales Order through ARTran. I have the join between GLTran and ARTran producing correct results. However, I'm struggling on the Sales Order to ARTran join as it's not producing the correct results. Below is what I have thus far.
Any help or guidance is much appreciated!
select top 100 *
from acumatica.dbo.SOLine sol
left join acumatica.dbo.SOShipLine sl
on sol.OrderNbr = sl.OrigOrderNbr
and sol.LineNbr = sl.OrigLineNbr
and sol.OrderType = sl.OrigOrderType
left join acumatica.dbo.SOShipment SS
on sl.CompanyID = ss.CompanyID
and sl.ShipmentType = SS.ShipmentType
and sl.ShipmentNbr = SS.ShipmentNbr
left Join acumatica.dbo.ARTran AR
on sl.CompanyID = ar.CompanyID
and sl.OrigOrderType = ar.SOOrderType
and sl.OrigOrderNbr = ar.SOOrderNbr
and sl.LineNbr = ar.SOOrderLineNbr
