DROP PROCEDURE IF EXISTS stor.sp_SetDisbandDocumentToSSCC;

CREATE PROCEDURE sp_SetDisbandDocumentToSSCC
(
	"Id" uuid,
    "Delete" boolean
)
    language plpgsql
as
$$
Begin 
	if("Delete" = 1)
	then
		if(exists(select * from STOR.T_STORAGE_INVOICE where InvoiceID = "Id"))
		then
			
			create temp table TmpShippingContainerExclude(Recursive boolean,ShippingContainerID uuid);
			
			with recursive 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 = "Id" 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
			)
			INSERT INTO TmpShippingContainerExclude(Recursive, ShippingContainerID)
			select Recursive, ShippingContainerID 
			from ShippingContainerExclude;

			with recursive ShippingContainer
			as
			(	
				SELECT	
					sc.ShippingContainerID
				FROM STOR.T_SHIPPING_CONTAINER sc
				where sc.StorageInvoice = "Id"
				and not exists(select * from STOR.T_MARK_MESSAGE mm where mm.Schema = 912 and mm.ObjId = sc.ShippingContainerID)
				and not exists(select * from TmpShippingContainerExclude 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 TmpShippingContainerExclude ex where ex.ShippingContainerID = sc.ShippingContainerID)
			)
			update STOR.T_SHIPPING_CONTAINER sc
			set DisbandDocument = '0'
			from ShippingContainer scc 
			where scc.ShippingContainerID = sc.ShippingContainerID;

			drop table TmpShippingContainerExclude;

		elsif(exists(select * from STOR.T_SHIPPING_CONTAINER sc where ShippingContainerID = "Id" and sc.Recursive = '1'))
		then
			with recursive ShippingContainer
			as
			(
				SELECT	
					sc.ShippingContainerID
				FROM STOR.T_SHIPPING_CONTAINER sc
				where sc.ShippingContainerID = "Id"
				union all
				SELECT
					sc.ShippingContainerID
				FROM STOR.T_SHIPPING_CONTAINER sc
				join ShippingContainer scR on scR.ShippingContainerID = sc.SSCC_Parent
			)
			update STOR.T_SHIPPING_CONTAINER sc
			set DisbandDocument = '0'
			from ShippingContainer scc 
			where scc.ShippingContainerID = sc.ShippingContainerID;
		
		elsif(exists(select * from STOR.T_SHIPPING_CONTAINER sc where ShippingContainerID = "Id" and sc.Recursive = '0'))
		then
			update STOR.T_SHIPPING_CONTAINER set DisbandDocument = 0 where ShippingContainerID = "Id";
		end if;
	else
		if(exists(select * from STOR.T_STORAGE_INVOICE where InvoiceID = "Id"))
		then
			update STOR.T_SHIPPING_CONTAINER set DisbandDocument = '1' where StorageInvoice = "Id";
		elsif(exists(select * from STOR.T_SHIPPING_CONTAINER sc where ShippingContainerID = "Id" and sc.Recursive = '1'))
		then
			with recursive ShippingContainer
			as
			(
				SELECT	
					sc.ShippingContainerID
				FROM STOR.T_SHIPPING_CONTAINER sc
				where sc.ShippingContainerID = "Id"
				union all
				SELECT
					sc.ShippingContainerID
				FROM STOR.T_SHIPPING_CONTAINER sc
				join ShippingContainer scR on scR.ShippingContainerID = sc.SSCC_Parent
			)
			update STOR.T_SHIPPING_CONTAINER sc
			set DisbandDocument = '1'
			from ShippingContainer scc 
			where scc.ShippingContainerID = sc.ShippingContainerID;
		elsif(exists(select * from STOR.T_SHIPPING_CONTAINER sc where ShippingContainerID = "Id" and sc.Recursive = '0'))
		then
			update STOR.T_SHIPPING_CONTAINER set DisbandDocument = '1' where ShippingContainerID = "Id";
		end if;
	end if;
End;
$$;
