dbo.QER_FTOrgHighestRulerContainer

1create function dbo.QER_FTOrgHighestRulerContainer (@uid_org varchar(38)  , @uid_personinserted varchar(38)  , @uid_personordered varchar(38) , @WithFallBackRoot
2 bit  , @UseRulerContainerIT int    ) returns @erg table (UID_Person varchar(38) collate database_default , UID_Org varchar(38) collate database_default
3 , UID_PWORulerOrigin varchar(38) collate database_default ) as begin declare @UID_Rulerorg varchar(38) declare @QER_ITShop_PersonInsertedNoDecide nvarchar
4(256) = '' declare @QER_ITShop_PersonOrderedNoDecide nvarchar(256) = '' declare @allPersonsOrdered QBM_YSingleGUID declare @allPersonsInserted QBM_YSingleGUID
5   if @uid_personinserted > ' ' begin select @QER_ITShop_PersonInsertedNoDecide = dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonInsertedNoDecide') end if
6 @QER_ITShop_PersonInsertedNoDecide = '1' and @uid_personinserted > ' ' begin  insert into @allPersonsInserted(UID_SingleGuid ) select distinct me.UID_PersonAlsoMe
7 from QER_VPersonsAreMe me where me.UID_PersonOrigin = @uid_personinserted end if @uid_personordered > ' ' begin select @QER_ITShop_PersonOrderedNoDecide
8 = dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide') end if @QER_ITShop_PersonOrderedNoDecide = '1' and @uid_personordered > ' ' begin insert
9 into @allPersonsOrdered(UID_SingleGuid ) select distinct me.UID_PersonAlsoMe from QER_VPersonsAreMe me where me.UID_PersonOrigin = @uid_personordered 
10end select top 1 @UID_Rulerorg = SUBSTRING(y.zusammen, 6,38) from ( select max( dbo.QBM_FCVStringPadLeft( dbo.QBM_FCVIntToString(x.treelevel),5, '0') +
11 x.UID_Org) as zusammen from (select distinct b.treelevel + 1 as treelevel, case @UseRulerContainerIT when 1 then b.UID_RulerContainerIT when -1 then b.UID_Org
12 else b.UID_RulerContainer end as UID_Org from BaseTreeCollection co join BaseTree b on co.UID_Org = @uid_org and co.UID_ParentOrg = b.UID_Org join PersonInBaseTree
13 pio on pio.UID_Org = case @UseRulerContainerIT when 1 then b.UID_RulerContainerIT when -1 then b.UID_Org else b.UID_RulerContainer end and pio.XOrigin
14 > 0 where (@QER_ITShop_PersonInsertedNoDecide <> '1' or not exists (select top 1 1 from @allPersonsInserted me where me.UID_SingleGuid  = pio.uid_person
15 ) ) and ( @QER_ITShop_PersonOrderedNoDecide <> '1' or not exists (select top 1 1 from @allPersonsOrdered me where me.UID_SingleGuid  = pio.uid_person 
16) ) union all select 0, r.UID_OrgAttestator as uid_org from OrgRoot r join BaseTree b on b.UID_OrgRoot = r.UID_OrgRoot and b.UID_Org = @uid_org join PersonInBaseTree
17 pio on r.UID_OrgAttestator = pio.UID_Org and pio.XOrigin > 0 where ( @QER_ITShop_PersonInsertedNoDecide <> '1' or not exists (select top 1 1 from @allPersonsInserted
18 me where me.UID_SingleGuid  = pio.uid_person ) ) and ( @QER_ITShop_PersonOrderedNoDecide <> '1' or not exists (select top 1 1 from @allPersonsOrdered 
19me where me.UID_SingleGuid  = pio.uid_person ) ) and @WithFallBackRoot = 1 ) as x ) as y insert into @erg (UID_Org, UID_Person, UID_PWORulerOrigin) select
20 pib.UID_Org, pib.UID_Person, pwo.UID_PersonWantsOrg from PersonInBaseTree pib left outer join PersonWantsOrg pwo on pwo.ObjectKeyAssignment = pib.XObjectKey
21   and pwo.OrderState in ('Assigned', 'OrderProlongate', 'OrderUnsubscribe' ) and pib.XOrigin > 0 where pib.UID_Org = @UID_Rulerorg and pib.XOrigin > 0
22 and ( @QER_ITShop_PersonInsertedNoDecide <> '1' or not exists (select top 1 1 from @allPersonsInserted me where me.UID_SingleGuid  = pib.uid_person ) 
23) and ( @QER_ITShop_PersonOrderedNoDecide <> '1' or not exists (select top 1 1 from @allPersonsOrdered me where me.UID_SingleGuid  = pib.uid_person ) )
24 return end 
25