SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

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

create PROCEDURE stor.sp_SetDisbandDocumentToSSCC
(
	@ObjId uniqueidentifier,
	@Delete bit
)
as
begin
	if(@Delete = 1)
	begin
		if(exists(select * from STOR.T_STORAGE_INVOICE where InvoiceID = @ObjId))
		begin
			;with ShippingContainerExclude
			as
			(	
				select sc.Recursive, sc.ShippingContainerID
				from STOR.T_SHIPPING_CONTAINER sc
				join STOR.T_MARK_MESSAGE mm on mm.[Schema] = 912 and mm.ObjId = sc.ShippingContainerID
				where sc.StorageInvoice = @ObjId and sc.Recursive = 1
				union all
				SELECT
					sc.Recursive, sc.ShippingContainerID
				FROM STOR.T_SHIPPING_CONTAINER sc
				join ShippingContainerExclude scR on sc.ShippingContainerID = sc.SSCC_Parent
			)
			select * 
			into #ShippingContainerExclude
			from ShippingContainerExclude

			;with ShippingContainer
			as
			(	
				SELECT	
					sc.ShippingContainerID
				FROM STOR.T_SHIPPING_CONTAINER sc
				where sc.StorageInvoice = @ObjId
				and not exists(select * from STOR.T_MARK_MESSAGE mm where mm.[Schema] = 912 and mm.ObjId = sc.ShippingContainerID)
				and not exists(select * from #ShippingContainerExclude ex where ex.ShippingContainerID = sc.ShippingContainerID)
				union all
				SELECT
					sc.ShippingContainerID
				FROM STOR.T_SHIPPING_CONTAINER sc
				join ShippingContainer scR on scR.ShippingContainerID = sc.SSCC_Parent
				where not exists(select * from #ShippingContainerExclude ex where ex.ShippingContainerID = sc.ShippingContainerID)
			)
			update sc
			set sc.DisbandDocument = 0
			from STOR.T_SHIPPING_CONTAINER sc
			join ShippingContainer scc on scc.ShippingContainerID = sc.ShippingContainerID

			drop table #ShippingContainerExclude
		end
		else if(exists(select * from STOR.T_SHIPPING_CONTAINER sc where ShippingContainerID = @ObjId and sc.Recursive = 1))
		begin
			;with ShippingContainer
			as
			(
				SELECT	
					sc.ShippingContainerID
				FROM STOR.T_SHIPPING_CONTAINER sc
				where sc.ShippingContainerID = @ObjId
				union all
				SELECT
					sc.ShippingContainerID
				FROM STOR.T_SHIPPING_CONTAINER sc
				join ShippingContainer scR on scR.ShippingContainerID = sc.SSCC_Parent
			)
			update sc
			set sc.DisbandDocument = 0
			from STOR.T_SHIPPING_CONTAINER sc
			join ShippingContainer scc on scc.ShippingContainerID = sc.ShippingContainerID
		end
		else if(exists(select * from STOR.T_SHIPPING_CONTAINER sc where ShippingContainerID = @ObjId and sc.Recursive = 0))
		begin
			update STOR.T_SHIPPING_CONTAINER set DisbandDocument = 0 where ShippingContainerID = @ObjId
		end
	end
	else
	begin
		if(exists(select * from STOR.T_STORAGE_INVOICE where InvoiceID = @ObjId))
		begin
			update STOR.T_SHIPPING_CONTAINER set DisbandDocument = 1 where StorageInvoice = @ObjId 
		end
		else if(exists(select * from STOR.T_SHIPPING_CONTAINER sc where ShippingContainerID = @ObjId and sc.Recursive = 1))
		begin
			;with ShippingContainer
			as
			(
				SELECT	
					sc.ShippingContainerID
				FROM STOR.T_SHIPPING_CONTAINER sc
				where sc.ShippingContainerID = @ObjId
				union all
				SELECT
					sc.ShippingContainerID
				FROM STOR.T_SHIPPING_CONTAINER sc
				join ShippingContainer scR on scR.ShippingContainerID = sc.SSCC_Parent
			)
			update sc
			set sc.DisbandDocument = 1
			from STOR.T_SHIPPING_CONTAINER sc
			join ShippingContainer scc on scc.ShippingContainerID = sc.ShippingContainerID
		end
		else if(exists(select * from STOR.T_SHIPPING_CONTAINER sc where ShippingContainerID = @ObjId and sc.Recursive = 0))
		begin
			update STOR.T_SHIPPING_CONTAINER set DisbandDocument = 1 where ShippingContainerID = @ObjId
		end
	end
end
