dbo.QER_FTPersonHeadCirc_BaseTree

1create function dbo.QER_FTPersonHeadCirc_BaseTree( @UID_OrgRoot varchar(38) = null  ) returns @erg table ( UID_Person varchar(38) collate database_default
2 Not Null , UID_PersonHead varchar(38) collate database_default Not Null    , IsDelegationContained bit default 0  ) as begin declare @work QER_YPersonHeadCircular
3 insert into @work(uid_person, uid_personhead , IsDirectReportsContained , IsHeadOrgContained  , IsCircular , IsDelegationContained , IsDirectReportsOnly
4  ) select x.UID_Person, x.UID_PersonHead , MAX(x.IsDirectReportsContained) , max(x.IsHeadOrgContained )  , 0 as IsCircular , max(IsDelegationContained
5) , min(IsDirectReportsOnly)  from (  select pio.UID_Person, h.UID_PersonHead , 0 as IsDirectReportsContained , 1 as IsHeadOrgContained , 0 as LevelNumber
6 , 0 as IsCircular , sign(len(isnull(d1.UID_PersonWantsOrg, ''))) as IsDelegationContained , 0 as IsDirectReportsOnly  from HelperPersonOrg pio join HelperHeadOrg
7 h on pio.UID_Org = h.UID_Org join BaseTree b on h.UID_Org = b.UID_Org left outer join PersonWantsOrg d1 on d1.ObjectKeyAssignment = h.XObjectKey and d1.OrderState
8 in ('OrderProlongate', 'Assigned', 'OrderUnsubscribe') where isnull(@UID_OrgRoot, b.UID_OrgRoot) = b.UID_OrgRoot and exists (select top 1 1 from HelperHeadOrg
9 ha where ha.UID_PersonHead = pio.UID_Person )  and not exists (select top 1 1 from HelperPersonOrg po where po.UID_Org = h.UID_Org and po.UID_Person =
10 pio.UID_Person )  union all select h.UID_PersonHead as UID_Person, ho.UID_PersonHead , 0 as IsDirectReportsContained , 1 as IsHeadOrgContained , 0 as 
11LevelNumber , 0 as IsCircular , sign(len(isnull(d1.UID_PersonWantsOrg, '')) + len(isnull(d2.UID_PersonWantsOrg, ''))) as IsDelegationContained , 0 as IsDirectReportsOnly
12  from HelperHeadOrg h join BaseTreeCollectionF co on h.UID_Org = co.UID_Org  and co.UID_Org <> co.UID_ParentOrg join HelperHeadOrg ho on co.UID_ParentOrg
13 = ho.UID_Org join BaseTree b on h.UID_Org = b.UID_Org left outer join PersonWantsOrg d1 on d1.ObjectKeyAssignment = h.XObjectKey and d1.OrderState in 
14('OrderProlongate', 'Assigned', 'OrderUnsubscribe') left outer join PersonWantsOrg d2 on d2.ObjectKeyAssignment = ho.XObjectKey and d2.OrderState in ('OrderProlongate'
15, 'Assigned', 'OrderUnsubscribe') where isnull(@UID_OrgRoot, b.UID_OrgRoot) = b.UID_OrgRoot ) as x group by x.UID_Person, x.UID_PersonHead having x.UID_Person
16 <> x.UID_PersonHead insert into @erg( UID_Person , UID_PersonHead    , IsDelegationContained  ) select c.UID_Person , UID_PersonHead    , IsDelegationContained
17  from dbo.QER_FTPersonHeadCirc_calc(@work) c return end 
18