Back to OIM Explorer

dbo.QER_FTPersonHeadCirc_All

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 3.221 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.QBM_FGIBitPatternXOrigin source text reference
  • references source dbo.QER_FTPersonHeadCirc_calc source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL112 lines
1CREATE FUNCTION dbo.QER_FTPersonHeadCirc_All(2) RETURNS @erg TABLE(UID_Person varchar(38) collate database_default NOT NULL,3UID_PersonHead varchar(38) collate database_default NOT NULL,4IsDirectReportsContained BIT DEFAULT 0,5IsDirectReportsOnly BIT DEFAULT 0,6IsHeadOrgContained BIT DEFAULT 0,7IsDelegationContained BIT DEFAULT 08)9AS10BEGIN11  DECLARE @Heads QBM_YSingleGUID12  DECLARE @work QER_YPersonHeadCircular13  INSERT INTO @Heads(UID_SingleGuid)14  SELECT h.UID_PersonHead15  FROM HelperHeadOrg h16  UNION17  SELECT h.UID_PersonHead18  FROM HelperHeadPerson h19  INSERT INTO @work(uid_person,20  uid_personhead,21  IsDirectReportsContained,22  IsHeadOrgContained,23  IsCircular,24  IsDelegationContained,25  IsDirectReportsOnly)26  SELECT27    x.UID_Person,28    x.UID_PersonHead,29    MAX(x.IsDirectReportsContained),30    max(x.IsHeadOrgContained),31    0 AS IsCircular,32    max(IsDelegationContained),33    min(IsDirectReportsOnly)34  FROM(35  SELECT36    h.UID_Person,37    h.UID_PersonHead,38    1 AS IsDirectReportsContained,39    0 AS IsHeadOrgContained,40    0 AS IsCircular,41    sign(len(isnull(d1.UID_PersonWantsOrg, ''))) AS IsDelegationContained,42    sign(h.XOrigin & dbo.QBM_FGIBitPatternXOrigin('|Direct|', 0)) AS IsDirectReportsOnly43  FROM HelperHeadPerson h44  JOIN @Heads o45    ON h.UID_Person = o.UID_SingleGuid46  LEFT47  OUTER48  JOIN PersonWantsOrg d149    ON d1.ObjectKeyAssignment = h.XObjectKey AND d1.OrderState IN('OrderProlongate', 'Assigned', 'OrderUnsubscribe')50  UNION all51  SELECT52    pio.UID_Person,53    h.UID_PersonHead,54    0 AS IsDirectReportsContained,55    1 AS IsHeadOrgContained,56    0 AS IsCircular,57    sign(len(isnull(d1.UID_PersonWantsOrg, ''))) AS IsDelegationContained,58    0 AS IsDirectReportsOnly59  FROM HelperPersonOrg pio60  JOIN @Heads o61    ON pio.UID_Person = o.UID_SingleGuid62  JOIN HelperHeadOrg h63    ON pio.UID_Org = h.UID_Org64  LEFT65  OUTER66  JOIN PersonWantsOrg d167    ON d1.ObjectKeyAssignment = h.XObjectKey AND d1.OrderState IN('OrderProlongate', 'Assigned', 'OrderUnsubscribe') AND NOT EXISTS(68  SELECT TOP 1 169  FROM HelperPersonOrg po70  WHERE71    po.UID_Org = h.UID_Org AND po.UID_Person = pio.UID_Person)72  UNION all73  SELECT74    h.UID_PersonHead AS UID_Person,75    ho.UID_PersonHead,76    0 AS IsDirectReportsContained,77    1 AS IsHeadOrgContained,78    0 AS IsCircular,79    sign(len(isnull(d1.UID_PersonWantsOrg, '')) + len(isnull(d2.UID_PersonWantsOrg, ''))) AS IsDelegationContained,80    0 AS IsDirectReportsOnly81  FROM HelperHeadOrg h82  JOIN BaseTreeCollectionF co83    ON h.UID_Org = co.UID_Org AND co.UID_Org <> co.UID_ParentOrg84  JOIN HelperHeadOrg ho85    ON co.UID_ParentOrg = ho.UID_Org86  LEFT87  OUTER88  JOIN PersonWantsOrg d189    ON d1.ObjectKeyAssignment = h.XObjectKey AND d1.OrderState IN('OrderProlongate', 'Assigned', 'OrderUnsubscribe')90  LEFT91  OUTER92  JOIN PersonWantsOrg d293    ON d2.ObjectKeyAssignment = ho.XObjectKey AND d2.OrderState IN('OrderProlongate', 'Assigned', 'OrderUnsubscribe')) AS x94  GROUP BY x.UID_Person,95  x.UID_PersonHead96  HAVING x.UID_Person <> x.UID_PersonHead97  INSERT INTO @erg(UID_Person,98  UID_PersonHead,99  IsDirectReportsContained,100  IsDirectReportsOnly,101  IsHeadOrgContained,102  IsDelegationContained)103  SELECT104    c.UID_Person,105    UID_PersonHead,106    IsDirectReportsContained,107    IsDirectReportsOnly,108    IsHeadOrgContained,109    IsDelegationContained110  FROM dbo.QER_FTPersonHeadCirc_calc(@work) c111  RETURN112END
Open raw exported source
SQL ยท Raw22 lines
1create function dbo.QER_FTPersonHeadCirc_All() returns @erg table ( UID_Person varchar(38) collate database_default Not Null , UID_PersonHead varchar(382) collate database_default Not Null , IsDirectReportsContained bit default 0 , IsDirectReportsOnly bit default 0  , IsHeadOrgContained bit default 0 , 3IsDelegationContained bit default 0  ) as begin declare @Heads QBM_YSingleGUID declare @work QER_YPersonHeadCircular insert into @Heads (UID_SingleGuid4) select h.UID_PersonHead from HelperHeadOrg h union select h.UID_PersonHead from HelperHeadPerson h insert into @work(uid_person, uid_personhead , IsDirectReportsContained5 , IsHeadOrgContained  , IsCircular , IsDelegationContained , IsDirectReportsOnly  ) select x.UID_Person, x.UID_PersonHead , MAX(x.IsDirectReportsContained6) , max(x.IsHeadOrgContained )  , 0 as IsCircular , max(IsDelegationContained) , min(IsDirectReportsOnly)  from (  select h.UID_Person, h.UID_PersonHead7 , 1 as IsDirectReportsContained , 0 as IsHeadOrgContained  , 0 as IsCircular , sign(len(isnull(d1.UID_PersonWantsOrg, ''))) as IsDelegationContained ,8 sign(h.XOrigin & dbo.QBM_FGIBitPatternXOrigin('|Direct|', 0)) as IsDirectReportsOnly  from HelperHeadPerson h join @Heads o on h.UID_Person = o.UID_SingleGuid9 left outer join PersonWantsOrg d1 on d1.ObjectKeyAssignment = h.XObjectKey and d1.OrderState in ('OrderProlongate', 'Assigned', 'OrderUnsubscribe') union10 all  select pio.UID_Person, h.UID_PersonHead , 0 as IsDirectReportsContained , 1 as IsHeadOrgContained  , 0 as IsCircular , sign(len(isnull(d1.UID_PersonWantsOrg11, ''))) as IsDelegationContained , 0 as IsDirectReportsOnly  from HelperPersonOrg pio join @Heads o on pio.UID_Person = o.UID_SingleGuid join HelperHeadOrg12 h on pio.UID_Org = h.UID_Org left outer join PersonWantsOrg d1 on d1.ObjectKeyAssignment = h.XObjectKey and d1.OrderState in ('OrderProlongate', 'Assigned'13, 'OrderUnsubscribe')  and not exists (select top 1 1 from HelperPersonOrg po where po.UID_Org = h.UID_Org and po.UID_Person = pio.UID_Person )  union 14all select h.UID_PersonHead as UID_Person, ho.UID_PersonHead , 0 as IsDirectReportsContained , 1 as IsHeadOrgContained  , 0 as IsCircular , sign(len(isnull15(d1.UID_PersonWantsOrg, '')) + len(isnull(d2.UID_PersonWantsOrg, ''))) as IsDelegationContained , 0 as IsDirectReportsOnly  from HelperHeadOrg h join BaseTreeCollectionF16 co on h.UID_Org = co.UID_Org and co.UID_Org <> co.UID_ParentOrg join HelperHeadOrg ho on co.UID_ParentOrg = ho.UID_Org left outer join PersonWantsOrg 17d1 on d1.ObjectKeyAssignment = h.XObjectKey and d1.OrderState in ('OrderProlongate', 'Assigned', 'OrderUnsubscribe') left outer join PersonWantsOrg d2 18on d2.ObjectKeyAssignment = ho.XObjectKey and d2.OrderState in ('OrderProlongate', 'Assigned', 'OrderUnsubscribe') ) as x group by x.UID_Person, x.UID_PersonHead19 having x.UID_Person <> x.UID_PersonHead insert into @erg( UID_Person , UID_PersonHead , IsDirectReportsContained , IsDirectReportsOnly , IsHeadOrgContained20 , IsDelegationContained  ) select c.UID_Person , UID_PersonHead , IsDirectReportsContained , IsDirectReportsOnly , IsHeadOrgContained , IsDelegationContained21  from dbo.QER_FTPersonHeadCirc_calc(@work) c return end 22