Back to OIM Explorer

dbo.QER_FTEntitlementSourceOrgWho

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 2.636 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_FTEntitlementSourceOrg source text reference
references source dbo.QER_FTEntitlementSourceWho source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE FUNCTION dbo.QER_FTEntitlementSourceOrgWho(2  @ObjectKeyToInspect varchar(138),3  @uid_Org varchar(38)4) RETURNS @result TABLE(UID_Org varchar(38) collate database_default,5UID_Person varchar(38) collate database_default,6UID_ParentOrg varchar(38) collate database_default,7Ident_Org nvarchar(256) collate database_default,8AssignType int,9ObjectKeyOrg varchar(138) collate database_default10)11AS12BEGIN13  DECLARE @erg QBM_YCursorBuffer14  DECLARE @Person QBM_YSingleGUID15  DECLARE @QER_VBaseTreeCollection_Root QBM_YMNTable16  INSERT INTO @person(UID_SingleGuid)17  SELECT18    DISTINCT uid_person19  FROM dbo.QER_FTEntitlementSourceWho(@ObjectKeyToInspect)20  INSERT INTO @QER_VBaseTreeCollection_Root(UID_Element1,21  UID_Element2)22  SELECT23    v.UID_Org,24    v.UID_ParentOrg25  FROM QER_VBaseTreeCollection_Root v26  WHERE27    v.UID_ParentOrg = @uid_org28  INSERT INTO @QER_VBaseTreeCollection_Root(UID_Element1,29  UID_Element2)30  SELECT31    DISTINCT v.UID_Org,32    v.UID_ParentOrg33  FROM @QER_VBaseTreeCollection_Root e34  JOIN QER_VBaseTreeCollection_Root v35    ON e.UID_Element1 = v.UID_Org36  JOIN QER_VBaseTreeCollection_Root v237    ON v.UID_ParentOrg = v2.UID_Org AND e.UID_Element2 = v2.UID_ParentOrg38  WHERE39    NOT EXISTS(40  SELECT TOP 1 141  FROM @QER_VBaseTreeCollection_Root x42  WHERE43    x.UID_Element1 = v.UID_Org AND x.UID_Element2 = v.UID_ParentOrg)44  INSERT INTO @erg(UID1,45  Int1,46  UID2)47  SELECT48    co.UID_Element1,49    sign(max(sign(len(isnull(x.uid_org, ''))))),50    x.UID_Person51  FROM @QER_VBaseTreeCollection_Root co52  LEFT53  OUTER54  JOIN(55  SELECT56    pio.uid_org,57    pio.UID_Person58  FROM personinBaseTree pio59  JOIN @Person p60    ON pio.uid_person = p.UID_SingleGuid AND pio.XOrigin > 061  UNION62  SELECT63    pio.uid_org,64    pio.UID_Person65  FROM helperPersonOrg pio66  JOIN @Person p67    ON pio.uid_person = p.UID_SingleGuid) AS x68    ON co.UID_Element1 = x.uid_org69  GROUP BY co.UID_Element1,70  x.UID_Person71  UPDATE @erg72  SET Int1 = e.Int1 +273  FROM @erg e74  JOIN @QER_VBaseTreeCollection_Root co75    ON co.UID_Element2 = e.UID176  WHERE77    EXISTS(78  SELECT TOP 1 179  FROM @erg e280  WHERE81    e2.UID1 = co.UID_Element1 AND e2.Int1 = 1 AND e2.UID1 <> e.UID1)82  INSERT INTO @result(uid_org,83  uid_parentorg,84  ident_org,85  assigntype,86  ObjectkeyOrg,87  UID_Person)88  SELECT89    e.UID1,90    b.uid_parentorg,91    b.ident_org,92    e.Int1,93    b.XObjectKey,94    e.UID295  FROM @erg e96  LEFT97  OUTER98  JOIN BaseTree b99    ON e.UID1 = b.uid_org100  UPDATE @result101  SET ident_org = r.ident_orgroot,102  ObjectKeyOrg = r.XObjectKey103  FROM @result rx,104  Orgroot r105  WHERE106    rx.uid_org = r.uid_orgroot107  IF @@rowcount > 0108  BEGIN109    UPDATE @result110    SET uid_parentorg = b.uid_orgroot111    FROM @result rx,112    BaseTree b113    WHERE114      rx.uid_parentorg IS NULL AND rx.uid_org = b.uid_org115  END116  ende:117  RETURN118END

Open raw exported source

1create function dbo.QER_FTEntitlementSourceOrgWho ( @ObjectKeyToInspect varchar(138)  , @uid_Org varchar(38)   ) returns @result table (UID_Org varchar2(38) collate database_default  , UID_Person varchar(38) collate database_default  , UID_ParentOrg varchar(38) collate database_default  , Ident_Org nvarchar3(256) collate database_default  , AssignType int  , ObjectKeyOrg varchar(138) collate database_default  ) as begin   declare @erg QBM_YCursorBuffer    4declare @Person QBM_YSingleGUID  declare @QER_VBaseTreeCollection_Root QBM_YMNTable  insert into @person(UID_SingleGuid ) select distinct uid_person from5 dbo.QER_FTEntitlementSourceWho(@ObjectKeyToInspect ) insert into @QER_VBaseTreeCollection_Root(UID_Element1, UID_Element2 ) select v.UID_Org, v.UID_ParentOrg6 from QER_VBaseTreeCollection_Root v where v.UID_ParentOrg = @uid_org  insert into @QER_VBaseTreeCollection_Root(UID_Element1, UID_Element2 ) select distinct7 v.UID_Org, v.UID_ParentOrg from @QER_VBaseTreeCollection_Root e join QER_VBaseTreeCollection_Root v on e.UID_Element1 = v.UID_Org join QER_VBaseTreeCollection_Root8 v2 on v.UID_ParentOrg = v2.UID_Org and e.UID_Element2  = v2.UID_ParentOrg where not exists (select top 1 1 from @QER_VBaseTreeCollection_Root x where 9x.UID_Element1 = v.UID_Org and x.UID_Element2  = v.UID_ParentOrg ) insert into @erg (UID1 , Int1 , UID2  ) select co.UID_Element1, sign(max(sign(len(isnull10(x.uid_org, '')))) ), x.UID_Person from @QER_VBaseTreeCollection_Root co left outer join ( select pio.uid_org, pio.UID_Person from personinBaseTree pio11 join @Person p on pio.uid_person = p.UID_SingleGuid  and pio.XOrigin > 0  union select pio.uid_org, pio.UID_Person from helperPersonOrg pio join @Person12 p on pio.uid_person = p.UID_SingleGuid  ) as x on co.UID_Element1 = x.uid_org  group by co.UID_Element1, x.UID_Person update @erg set Int1  = e.Int1  13+2 from @erg e join @QER_VBaseTreeCollection_Root co on co.UID_Element2  = e.UID1  where exists (select top 1 1 from @erg e2 where e2.UID1  = co.UID_Element114 and e2.Int1  = 1 and e2.UID1  <> e.UID1  ) insert into @result (uid_org, uid_parentorg, ident_org, assigntype, ObjectkeyOrg, UID_Person ) select e.UID115 , b.uid_parentorg, b.ident_org, e.Int1 , b.XObjectKey, e.UID2  from @erg e left outer join BaseTree b on e.UID1 = b.uid_org update @result set ident_org16 = r.ident_orgroot, ObjectKeyOrg = r.XObjectKey from @result rx , Orgroot r where rx.uid_org = r.uid_orgroot if @@rowcount > 0 begin update @result set17 uid_parentorg = b.uid_orgroot from @result rx, BaseTree b where rx.uid_parentorg is null and rx.uid_org = b.uid_org end ende: return end 18