Back to OIM Explorer

dbo.QER_FTPersonHeadCirc_Person_i

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 3.638 characters

Interpretation

Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

No extracted relations.

Typed Edges

references source dbo.QER_FTPersonHeadCirc_Person source text reference
references source dbo.QBM_FGIBitPatternXOrigin source text reference
references source dbo.QER_FTPersonHeadCirc_calc source text reference

References

Referenced By

Complete Source

1CREATE FUNCTION dbo.QER_FTPersonHeadCirc_Person_i(2  @UID_Person varchar(38),3  @UID_PersonHead varchar(38),4  @fall int5) RETURNS @erg TABLE(UID_Person varchar(38) collate database_default NOT NULL,6UID_PersonHead varchar(38) collate database_default NOT NULL,7IsDirectReportsOnly BIT DEFAULT 0,8IsDelegationContained BIT DEFAULT 09)10AS11BEGIN12  DECLARE @work QER_YPersonHeadCircular13  DECLARE @HeadOld varchar(38) = NULL14  IF @Fall = 115  BEGIN16    GOTO endLabel17  END18  IF @Fall IN(0,19  2)20  BEGIN21    INSERT INTO @work(uid_person,22    uid_personhead,23    IsDirectReportsContained,24    IsHeadOrgContained,25    IsCircular,26    IsDelegationContained,27    IsDirectReportsOnly)28    SELECT29      x.UID_Person,30      x.UID_PersonHead,31      MAX(x.IsDirectReportsContained),32      max(x.IsHeadOrgContained),33      0 AS IsCircular,34      max(IsDelegationContained),35      min(IsDirectReportsOnly)36    FROM(37    SELECT38      h.UID_Person,39      h.UID_PersonHead,40      1 AS IsDirectReportsContained,41      0 AS IsHeadOrgContained,42      0 AS IsCircular,43      sign(len(isnull(d1.UID_PersonWantsOrg, ''))) AS IsDelegationContained,44      sign(h.XOrigin & dbo.QBM_FGIBitPatternXOrigin('|Direct|', 0)) AS IsDirectReportsOnly45    FROM HelperHeadPerson h46    LEFT47    OUTER48    JOIN PersonWantsOrg d149      ON d1.ObjectKeyAssignment = h.XObjectKey AND d1.OrderState IN('OrderProlongate', 'Assigned', 'OrderUnsubscribe')50    UNION all51    SELECT52      h.UID_Person,53      h.UID_PersonHead,54      1 AS IsDirectReportsContained,55      0 AS IsHeadOrgContained,56      0 AS IsCircular,57      0 AS IsDelegationContained,58      1 AS IsDirectReportsOnly59    FROM Person h60    WHERE61      h.UID_Person = @UID_Person OR h.UID_PersonHead = @UID_Person) AS x62    GROUP BY x.UID_Person,63    x.UID_PersonHead64    HAVING x.UID_Person <> x.UID_PersonHead65  END66  ELSE67  BEGIN68    SELECT TOP 1 @HeadOld = p.UID_PersonHead69    FROM Person p70    WHERE71      p.UID_Person = @UID_Person72    SELECT73      @HeadOld = ISNULL(@HeadOld,74      '#')75    INSERT INTO @work(uid_person,76    uid_personhead,77    IsDirectReportsContained,78    IsHeadOrgContained,79    IsCircular,80    IsDelegationContained,81    IsDirectReportsOnly)82    SELECT83      x.UID_Person,84      x.UID_PersonHead,85      MAX(x.IsDirectReportsContained),86      max(x.IsHeadOrgContained),87      0 AS IsCircular,88      max(IsDelegationContained),89      min(IsDirectReportsOnly)90    FROM(91    SELECT92      h.UID_Person,93      h.UID_PersonHead,94      1 AS IsDirectReportsContained,95      0 AS IsHeadOrgContained,96      0 AS IsCircular,97      sign(len(isnull(d1.UID_PersonWantsOrg, ''))) AS IsDelegationContained,98      sign(h.XOrigin & dbo.QBM_FGIBitPatternXOrigin('|Direct|', 0)) AS IsDirectReportsOnly99    FROM HelperHeadPerson h100    LEFT101    OUTER102    JOIN PersonWantsOrg d1103      ON d1.ObjectKeyAssignment = h.XObjectKey AND d1.OrderState IN('OrderProlongate', 'Assigned', 'OrderUnsubscribe')104    WHERE105      NOT(h.UID_Person = @UID_Person AND h.UID_PersonHead = @HeadOld) AND h.XOrigin & dbo.QBM_FGIBitPatternXOrigin('|Pwo|',106    1) > 0107    UNION all108    SELECT109      @UID_Person,110      @UID_PersonHead,111      1 AS IsDirectReportsContained,112      0 AS IsHeadOrgContained,113      0 AS IsCircular,114      0 AS IsDelegationContained,115      1 AS IsDirectReportsOnly) AS x116    GROUP BY x.UID_Person,117    x.UID_PersonHead118    HAVING x.UID_Person <> x.UID_PersonHead119    IF @UID_Person = @UID_PersonHead120    BEGIN121      INSERT INTO @work(uid_person,122      uid_personhead,123      IsDirectReportsContained,124      IsHeadOrgContained,125      IsCircular,126      IsDelegationContained,127      IsDirectReportsOnly)128      SELECT129        @UID_Person,130        @UID_PersonHead,131        1,132        0,133        1,134        0,135        1136    END137  END138  Berechnung:139  INSERT INTO @erg(UID_Person,140  UID_PersonHead,141  IsDirectReportsOnly,142  IsDelegationContained)143  SELECT144    c.UID_Person,145    UID_PersonHead,146    IsDirectReportsOnly,147    IsDelegationContained148  FROM dbo.QER_FTPersonHeadCirc_calc(@work) c149  WHERE150    (c.UID_Person = @UID_Person OR c.UID_PersonHead = @UID_Person OR @UID_Person IS NULL) endLabel:151  RETURN152END

Open raw exported source

1create function dbo.QER_FTPersonHeadCirc_Person_i( @UID_Person varchar(38) , @UID_PersonHead varchar(38) , @fall int  ) returns @erg table ( UID_Person2 varchar(38) collate database_default Not Null , UID_PersonHead varchar(38) collate database_default Not Null , IsDirectReportsOnly bit default 0  , IsDelegationContained3 bit default 0 ) as begin declare @work QER_YPersonHeadCircular declare @HeadOld varchar(38) = null  if @Fall = 1 begin goto endLabel end if @Fall in (04,2)  begin insert into @work(uid_person, uid_personhead , IsDirectReportsContained , IsHeadOrgContained  , IsCircular , IsDelegationContained , IsDirectReportsOnly5  ) select x.UID_Person, x.UID_PersonHead , MAX(x.IsDirectReportsContained) , max(x.IsHeadOrgContained )  , 0 as IsCircular , max(IsDelegationContained6) , min(IsDirectReportsOnly)  from (  select h.UID_Person, h.UID_PersonHead , 1 as IsDirectReportsContained , 0 as IsHeadOrgContained  , 0 as IsCircular7 , sign(len(isnull(d1.UID_PersonWantsOrg, ''))) as IsDelegationContained , sign(h.XOrigin & dbo.QBM_FGIBitPatternXOrigin('|Direct|', 0)) as IsDirectReportsOnly8 from HelperHeadPerson h left outer join PersonWantsOrg d1 on d1.ObjectKeyAssignment = h.XObjectKey and d1.OrderState in ('OrderProlongate', 'Assigned'9, 'OrderUnsubscribe')      union all  select h.UID_Person, h.UID_PersonHead , 1 as IsDirectReportsContained , 0 as IsHeadOrgContained  , 0 as IsCircular10 , 0 as IsDelegationContained , 1 as IsDirectReportsOnly from Person h where h.UID_Person = @UID_Person or h.UID_PersonHead = @UID_Person            ) 11as x group by x.UID_Person, x.UID_PersonHead having x.UID_Person <> x.UID_PersonHead end else  begin  select top 1 @HeadOld = p.UID_PersonHead from Person12 p where p.UID_Person = @UID_Person select @HeadOld = ISNULL(@HeadOld, '#') insert into @work(uid_person, uid_personhead , IsDirectReportsContained , IsHeadOrgContained13  , IsCircular , IsDelegationContained , IsDirectReportsOnly  ) select x.UID_Person, x.UID_PersonHead , MAX(x.IsDirectReportsContained) , max(x.IsHeadOrgContained14 )  , 0 as IsCircular , max(IsDelegationContained) , min(IsDirectReportsOnly)  from (  select h.UID_Person, h.UID_PersonHead , 1 as IsDirectReportsContained15 , 0 as IsHeadOrgContained  , 0 as IsCircular , sign(len(isnull(d1.UID_PersonWantsOrg, ''))) as IsDelegationContained , sign(h.XOrigin & dbo.QBM_FGIBitPatternXOrigin16('|Direct|', 0)) as IsDirectReportsOnly from HelperHeadPerson h left outer join PersonWantsOrg d1 on d1.ObjectKeyAssignment = h.XObjectKey and d1.OrderState17 in ('OrderProlongate', 'Assigned', 'OrderUnsubscribe')  where not (h.UID_Person = @UID_Person and h.UID_PersonHead = @HeadOld )   and h.XOrigin & dbo.QBM_FGIBitPatternXOrigin18('|Pwo|', 1) > 0              union all select @UID_Person, @UID_PersonHead , 1 as IsDirectReportsContained , 0 as IsHeadOrgContained  , 0 as IsCircular19 , 0 as IsDelegationContained , 1 as IsDirectReportsOnly ) as x group by x.UID_Person, x.UID_PersonHead having x.UID_Person <> x.UID_PersonHead  if @UID_Person20 = @UID_PersonHead  begin insert into @work(uid_person, uid_personhead , IsDirectReportsContained , IsHeadOrgContained , IsCircular , IsDelegationContained21 , IsDirectReportsOnly ) select @UID_Person, @UID_PersonHead, 1, 0, 1, 0, 1 end end Berechnung: insert into @erg( UID_Person , UID_PersonHead  , IsDirectReportsOnly22  , IsDelegationContained  ) select c.UID_Person , UID_PersonHead  , IsDirectReportsOnly  , IsDelegationContained  from dbo.QER_FTPersonHeadCirc_calc(@work23) c where ( c.UID_Person = @UID_Person or c.UID_PersonHead = @UID_Person or @UID_Person is null ) endLabel: return end 24