Back to OIM Explorer

dbo.QER_FTEntitlementSourceOrg

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 2.607 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_FTEntitlementSourceWho source text reference

References

dbo.QER_FTEntitlementSourceWho

Referenced By

dbo.QER_FTEntitlementSourceOrgWho

Complete Source

1CREATE FUNCTION dbo.QER_FTEntitlementSourceOrg(2  @ObjectKeyToInspect varchar(138),3  @uid_Org varchar(38)4) RETURNS @result TABLE(UID_Org varchar(38) collate database_default,5UID_ParentOrg varchar(38) collate database_default,6Ident_Org nvarchar(256) collate database_default,7AssignType int,8ObjectKeyOrg varchar(138) collate database_default9)10AS11BEGIN12  DECLARE @erg QBM_YSingleGUID13  DECLARE @Person QBM_YSingleGUID14  DECLARE @QER_VBaseTreeCollection_Root QBM_YMNTable15  INSERT INTO @person(UID_SingleGuid)16  SELECT w.UID_Person17  FROM dbo.QER_FTEntitlementSourceWho(@ObjectKeyToInspect) w18  GROUP BY w.UID_Person19  INSERT INTO @QER_VBaseTreeCollection_Root(UID_Element1,20  UID_Element2)21  SELECT22    v.UID_Org,23    v.UID_ParentOrg24  FROM QER_VBaseTreeCollection_Root v25  WHERE26    v.UID_ParentOrg = @uid_org27  INSERT INTO @QER_VBaseTreeCollection_Root(UID_Element1,28  UID_Element2)29  SELECT30    DISTINCT v.UID_Org,31    v.UID_ParentOrg32  FROM @QER_VBaseTreeCollection_Root e33  JOIN QER_VBaseTreeCollection_Root v34    ON e.UID_Element1 = v.UID_Org35  JOIN QER_VBaseTreeCollection_Root v236    ON v.UID_ParentOrg = v2.UID_Org AND e.UID_Element2 = v2.UID_ParentOrg37  WHERE38    NOT EXISTS(39  SELECT TOP 1 140  FROM @QER_VBaseTreeCollection_Root x41  WHERE42    x.UID_Element1 = v.UID_Org AND x.UID_Element2 = v.UID_ParentOrg)43  INSERT INTO @erg(UID_SingleGuid,44  IntProperty)45  SELECT46    co.UID_Element1,47    sign(max(sign(len(isnull(x.uid_org, '')))))48  FROM @QER_VBaseTreeCollection_Root co49  LEFT50  OUTER51  JOIN(52  SELECT pio.uid_org53  FROM personinBaseTree pio54  JOIN @Person p55    ON pio.uid_person = p.UID_SingleGuid AND pio.XOrigin > 056  UNION57  SELECT pio.uid_org58  FROM helperPersonOrg pio59  JOIN @Person p60    ON pio.uid_person = p.UID_SingleGuid) AS x61    ON co.UID_Element1 = x.uid_org62  GROUP BY co.UID_Element163  UPDATE @erg64  SET IntProperty += 265  FROM @erg e66  JOIN @QER_VBaseTreeCollection_Root co67    ON co.UID_Element2 = e.UID_SingleGuid68  WHERE69    EXISTS(70  SELECT TOP 1 171  FROM @erg e272  WHERE73    e2.UID_SingleGuid = co.UID_Element1 AND e2.IntProperty = 1 AND e2.UID_SingleGuid <> e.UID_SingleGuid)74  INSERT INTO @result(uid_org,75  uid_parentorg,76  ident_org,77  assigntype,78  ObjectkeyOrg)79  SELECT80    e.UID_SingleGuid,81    b.uid_parentorg,82    b.ident_org,83    e.IntProperty,84    b.XObjectKey85  FROM @erg e86  LEFT87  OUTER88  JOIN BaseTree b89    ON e.UID_SingleGuid = b.uid_org90  UPDATE @result91  SET ident_org = r.ident_orgroot,92  ObjectKeyOrg = r.XObjectKey93  FROM @result rx,94  Orgroot r95  WHERE96    rx.uid_org = r.uid_orgroot97  IF @@rowcount > 098  BEGIN99    UPDATE @result100    SET uid_parentorg = b.uid_orgroot101    FROM @result rx,102    BaseTree b103    WHERE104      rx.uid_parentorg IS NULL AND rx.uid_org = b.uid_org105  END106  ende:107  RETURN108END

Open raw exported source

1create function dbo.QER_FTEntitlementSourceOrg ( @ObjectKeyToInspect varchar(138)  , @uid_Org varchar(38)   ) returns @result table (UID_Org varchar(382) collate database_default  , UID_ParentOrg varchar(38) collate database_default  , Ident_Org nvarchar(256) collate database_default  , AssignType int 3 , ObjectKeyOrg varchar(138) collate database_default  ) as begin  declare @erg QBM_YSingleGUID  declare @Person QBM_YSingleGUID  declare @QER_VBaseTreeCollection_Root4 QBM_YMNTable  insert into @person(UID_SingleGuid ) select w.UID_Person from dbo.QER_FTEntitlementSourceWho(@ObjectKeyToInspect ) w group by w.UID_Person5 insert into @QER_VBaseTreeCollection_Root(UID_Element1 , UID_Element2 ) select v.UID_Org, v.UID_ParentOrg from QER_VBaseTreeCollection_Root v where v.UID_ParentOrg6 = @uid_org  insert into @QER_VBaseTreeCollection_Root(UID_Element1 , UID_Element2 ) select distinct v.UID_Org, v.UID_ParentOrg from @QER_VBaseTreeCollection_Root7 e join QER_VBaseTreeCollection_Root v on e.UID_Element1  = v.UID_Org join QER_VBaseTreeCollection_Root v2 on v.UID_ParentOrg = v2.UID_Org and e.UID_Element28  = v2.UID_ParentOrg where not exists (select top 1 1 from @QER_VBaseTreeCollection_Root x where x.UID_Element1  = v.UID_Org and x.UID_Element2  = v.UID_ParentOrg9 )  insert into @erg (UID_SingleGuid , IntProperty ) select co.UID_Element1 , sign(max(sign(len(isnull(x.uid_org, '')))) ) from @QER_VBaseTreeCollection_Root10 co left outer join ( select pio.uid_org from personinBaseTree pio join @Person p on pio.uid_person = p.UID_SingleGuid  and pio.XOrigin > 0  union select11 pio.uid_org from helperPersonOrg pio join @Person p on pio.uid_person = p.UID_SingleGuid  ) as x on co.UID_Element1  = x.uid_org  group by co.UID_Element112   update @erg set IntProperty  += 2 from @erg e join @QER_VBaseTreeCollection_Root co on co.UID_Element2  = e.UID_SingleGuid  where exists (select top13 1 1 from @erg e2 where e2.UID_SingleGuid  = co.UID_Element1  and e2.IntProperty  = 1 and e2.UID_SingleGuid  <> e.UID_SingleGuid  )  insert into @result14 (uid_org, uid_parentorg, ident_org, assigntype, ObjectkeyOrg ) select e.UID_SingleGuid , b.uid_parentorg, b.ident_org, e.IntProperty , b.XObjectKey from15 @erg e left outer join BaseTree b on e.UID_SingleGuid  = b.uid_org update @result set ident_org = r.ident_orgroot, ObjectKeyOrg = r.XObjectKey from @result16 rx , Orgroot r where rx.uid_org = r.uid_orgroot if @@rowcount > 0 begin update @result set uid_parentorg = b.uid_orgroot from @result rx, BaseTree b where17 rx.uid_parentorg is null and rx.uid_org = b.uid_org end ende: return end 18