SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[stor].[fn_GetUnpackMarkMessageForSSCC]') AND type in (N'FN', N'IF', N'TF'))
DROP FUNCTION stor.[fn_GetUnpackMarkMessageForSSCC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'stor.fn_GetUnpackMarkMessageForSSCC') and xtype = N'P')
drop procedure stor.fn_GetUnpackMarkMessageForSSCC
GO

if exists (select * from dbo.sysobjects where id = object_id(N'stor.sp_GetUnpackMarkMessageForSSCC') and xtype = N'P')
drop procedure stor.sp_GetUnpackMarkMessageForSSCC
GO

create procedure stor.sp_GetUnpackMarkMessageForSSCC
(
	@Id uniqueidentifier,
	@MarkMessageID uniqueidentifier OUTPUT
)
as
begin
	;with ShippingContainer
	as
	(
		SELECT	
			sc.ShippingContainerID
		,	sc.SSCC_Parent
		,	sc.StorageInvoice
		,	sc.SSCC
		,	sc.[Recursive]
		,	0 as Lvl
		FROM STOR.T_SHIPPING_CONTAINER sc
		where sc.ShippingContainerID = @Id
		union all
		SELECT
			sc.ShippingContainerID
		,	sc.SSCC_Parent
		,	sc.StorageInvoice
		,	sc.SSCC
		,	sc.[Recursive]
		,	scR.Lvl + 1
		FROM STOR.T_SHIPPING_CONTAINER sc
		join ShippingContainer scR on sc.ShippingContainerID = scr.SSCC_Parent
	)
	, sc as
	(
		select 
			sc.ShippingContainerID
		,	sc.StorageInvoice
		,	sc.SSCC
		,	sc.[Recursive]
		,	mm1.MarkMessageID as sc_MarkMessage
		,	mm2.MarkMessageID as si_MarkMessage
		,	row_number() over(order by sc.lvl desc) as rn
		from ShippingContainer sc
		outer apply 
		(
			select top (1) mm.MarkMessageID from STOR.T_MARK_MESSAGE mm where mm.MessageType = 0 and mm.STATUS = 1 and mm.[Schema] = 912 and mm.ObjId = sc.ShippingContainerID
		) mm1
		outer apply 
		(
			select top (1) mm.MarkMessageID from STOR.T_MARK_MESSAGE mm where mm.MessageType = 0 and mm.STATUS = 1 and mm.[Schema] = 912 and mm.ObjId = sc.StorageInvoice and mm.Message like '%' + sc.SSCC + '%'
		) mm2
	) 
	select top (1) @MarkMessageID = coalesce(case when t2.rn < t1.rn or (t2.rn is not null and t1.rn is null) then t2.si_markmessage else t1.sc_markmessage end, sc.sc_markmessage, sc.si_markmessage)
	from sc sc
	outer apply
	(
		select top (1) mm.sc_MarkMessage, mm.rn from sc mm where mm.[Recursive] = 1 and mm.sc_MarkMessage is not null and sc.rn >= mm.rn order by sc.rn
	) t1
	outer apply
	(
		select top (1) mm.si_MarkMessage, mm.rn from sc mm where mm.si_MarkMessage is not null and sc.rn >= mm.rn order by mm.rn
	) t2;
end
