Back to OIM Explorer

dbo.QER_FTPersonHeadCirc_BaseTree

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 2.611 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_calc source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL93 lines
1CREATE FUNCTION dbo.QER_FTPersonHeadCirc_BaseTree(2  @UID_OrgRoot varchar(38) = NULL3) RETURNS @erg TABLE(UID_Person varchar(38) collate database_default NOT NULL,4UID_PersonHead varchar(38) collate database_default NOT NULL,5IsDelegationContained BIT DEFAULT 06)7AS8BEGIN9  DECLARE @work QER_YPersonHeadCircular10  INSERT INTO @work(uid_person,11  uid_personhead,12  IsDirectReportsContained,13  IsHeadOrgContained,14  IsCircular,15  IsDelegationContained,16  IsDirectReportsOnly)17  SELECT18    x.UID_Person,19    x.UID_PersonHead,20    MAX(x.IsDirectReportsContained),21    max(x.IsHeadOrgContained),22    0 AS IsCircular,23    max(IsDelegationContained),24    min(IsDirectReportsOnly)25  FROM(26  SELECT27    pio.UID_Person,28    h.UID_PersonHead,29    0 AS IsDirectReportsContained,30    1 AS IsHeadOrgContained,31    0 AS LevelNumber,32    0 AS IsCircular,33    sign(len(isnull(d1.UID_PersonWantsOrg, ''))) AS IsDelegationContained,34    0 AS IsDirectReportsOnly35  FROM HelperPersonOrg pio36  JOIN HelperHeadOrg h37    ON pio.UID_Org = h.UID_Org38  JOIN BaseTree b39    ON h.UID_Org = b.UID_Org40  LEFT41  OUTER42  JOIN PersonWantsOrg d143    ON d1.ObjectKeyAssignment = h.XObjectKey AND d1.OrderState IN('OrderProlongate', 'Assigned', 'OrderUnsubscribe')44  WHERE45    isnull(@UID_OrgRoot, b.UID_OrgRoot) = b.UID_OrgRoot AND EXISTS(46  SELECT TOP 1 147  FROM HelperHeadOrg ha48  WHERE49    ha.UID_PersonHead = pio.UID_Person) AND NOT EXISTS(50  SELECT TOP 1 151  FROM HelperPersonOrg po52  WHERE53    po.UID_Org = h.UID_Org AND po.UID_Person = pio.UID_Person)54  UNION all55  SELECT56    h.UID_PersonHead AS UID_Person,57    ho.UID_PersonHead,58    0 AS IsDirectReportsContained,59    1 AS IsHeadOrgContained,60    0 AS LevelNumber,61    0 AS IsCircular,62    sign(len(isnull(d1.UID_PersonWantsOrg, '')) + len(isnull(d2.UID_PersonWantsOrg, ''))) AS IsDelegationContained,63    0 AS IsDirectReportsOnly64  FROM HelperHeadOrg h65  JOIN BaseTreeCollectionF co66    ON h.UID_Org = co.UID_Org AND co.UID_Org <> co.UID_ParentOrg67  JOIN HelperHeadOrg ho68    ON co.UID_ParentOrg = ho.UID_Org69  JOIN BaseTree b70    ON h.UID_Org = b.UID_Org71  LEFT72  OUTER73  JOIN PersonWantsOrg d174    ON d1.ObjectKeyAssignment = h.XObjectKey AND d1.OrderState IN('OrderProlongate', 'Assigned', 'OrderUnsubscribe')75  LEFT76  OUTER77  JOIN PersonWantsOrg d278    ON d2.ObjectKeyAssignment = ho.XObjectKey AND d2.OrderState IN('OrderProlongate', 'Assigned', 'OrderUnsubscribe')79  WHERE80    isnull(@UID_OrgRoot, b.UID_OrgRoot) = b.UID_OrgRoot) AS x81  GROUP BY x.UID_Person,82  x.UID_PersonHead83  HAVING x.UID_Person <> x.UID_PersonHead84  INSERT INTO @erg(UID_Person,85  UID_PersonHead,86  IsDelegationContained)87  SELECT88    c.UID_Person,89    UID_PersonHead,90    IsDelegationContained91  FROM dbo.QER_FTPersonHeadCirc_calc(@work) c92  RETURN93END
Open raw exported source
SQL ยท Raw18 lines
1create function dbo.QER_FTPersonHeadCirc_BaseTree( @UID_OrgRoot varchar(38) = null  ) returns @erg table ( UID_Person varchar(38) collate database_default2 Not Null , UID_PersonHead varchar(38) collate database_default Not Null    , IsDelegationContained bit default 0  ) as begin declare @work QER_YPersonHeadCircular3 insert into @work(uid_person, uid_personhead , IsDirectReportsContained , IsHeadOrgContained  , IsCircular , IsDelegationContained , IsDirectReportsOnly4  ) select x.UID_Person, x.UID_PersonHead , MAX(x.IsDirectReportsContained) , max(x.IsHeadOrgContained )  , 0 as IsCircular , max(IsDelegationContained5) , min(IsDirectReportsOnly)  from (  select pio.UID_Person, h.UID_PersonHead , 0 as IsDirectReportsContained , 1 as IsHeadOrgContained , 0 as LevelNumber6 , 0 as IsCircular , sign(len(isnull(d1.UID_PersonWantsOrg, ''))) as IsDelegationContained , 0 as IsDirectReportsOnly  from HelperPersonOrg pio join HelperHeadOrg7 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.OrderState8 in ('OrderProlongate', 'Assigned', 'OrderUnsubscribe') where isnull(@UID_OrgRoot, b.UID_OrgRoot) = b.UID_OrgRoot and exists (select top 1 1 from HelperHeadOrg9 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 IsDirectReportsOnly12  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_ParentOrg13 = 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_Person16 <> x.UID_PersonHead insert into @erg( UID_Person , UID_PersonHead    , IsDelegationContained  ) select c.UID_Person , UID_PersonHead    , IsDelegationContained17  from dbo.QER_FTPersonHeadCirc_calc(@work) c return end 18