I’ve written this query quite a few times recently and wanted to share it in-case anyone needs to do something similar either in on-prem DB analysis, reports, or in process flow.

This query shows the link between a Service Order material demand and its associated Internal Material Order material demand that is created from returning the part to another warehouse.

-- Get Linked IMO Demand ID and IMO ID from Order ID.
select o.order_id, dm.demand_id, dmrd.demand_id, lo.logistic_order_id from order_line o with (nolock) -- Order
inner join demand_material dm with (nolock)
	inner join demand_mtrl_return dmr with (nolock)
		inner join demand_material dmrd with (nolock)
			inner join logistic_order lo with (nolock) 
				on lo.logistic_order_id = dmrd.request_id -- Return IMO Order
            on dmrd.rb_request_type = 'L' and CONVERT(varchar, dmrd.demand_id) = dmr.request_id -- Linked Demand on return IMO
		on dmr.demand_id = dm.demand_id -- Order's material return information
	on dm.order_id = o.order_id -- Order's material demand
where o.order_id = 'SV2108030259@@1';