Back to OIM Explorer

dbo.QER_FGIPWOVisibleForPerson

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 4.391 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_FCVStringToInt source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference

References

Referenced By

Complete Source

SQL194 lines
1CREATE FUNCTION dbo.QER_FGIPWOVisibleForPerson(2  @UID_Person varchar(38),3  @UID_PersonWantsOrg varchar(38),4  @AsManager BIT5) RETURNS BIT6BEGIN7  DECLARE @IsVisible BIT = 08  DECLARE @MyReports QBM_YSingleGUID9  DECLARE @MyOrgs QBM_YSingleGUID10  DECLARE @MyOrgsBasis QBM_YSingleGUID11  DECLARE @PersonsMe QBM_YSingleGUID12  DECLARE @ShowClosedAssignment int13  INSERT INTO @PersonsMe(UID_SingleGuid14)15SELECT16  DISTINCT me.UID_PersonAlsoMe17FROM QER_VPersonsAreMe me18WHERE19  me.UID_PersonOrigin = @uid_person20IF @AsManager = 121BEGIN22  INSERT INTO @MyOrgsBasis(UID_SingleGuid23)24SELECT x.UID_Org25FROM(26SELECT hho.UID_Org27FROM HelperHeadOrg hho28JOIN @PersonsMe me29  ON hho.UID_PersonHead = me.UID_SingleGuid30WHERE31  hho.XOrigin > 032UNION all33SELECT UID_Org34FROM BaseTree b35JOIN @PersonsMe me36  ON b.UID_PersonHead = me.UID_SingleGuid37UNION all38SELECT UID_Org f39FROM BaseTree b40JOIN @PersonsMe me41  ON b.UID_PersonHeadSecond = me.UID_SingleGuid42)43AS44x45GROUP BY x.UID_Org46INSERT INTO @MyOrgs(UID_SingleGuid,47BitProperty)48SELECT49  x.uid_org,50  min(x.isParent)51FROM(52SELECT53  b.UID_SingleGuid AS UID_Org,54  0 AS isParent55FROM @MyOrgsBasis b56UNION all57SELECT58  co.UID_Org,59  060FROM @MyOrgsBasis b61JOIN BaseTreeCollectionF co62  ON b.UID_SingleGuid = co.UID_ParentOrg AND co.UID_Org <> co.UID_ParentOrg63UNION all64SELECT65  co.UID_ParentOrg,66CASE67  WHEN co.UID_Org = co.UID_ParentOrg THEN68069ELSE 170END71FROM @MyOrgsBasis b72JOIN BaseTreeCollectionF co73  ON b.UID_SingleGuid = co.UID_Org AND co.UID_Org <> co.UID_ParentOrg) AS x74GROUP BY x.UID_Org75INSERT INTO @MyReports(UID_SingleGuid)76SELECT hpo.uid_person77FROM @MyOrgs c78JOIN helperpersonorg hpo79  ON hpo.uid_org = c.UID_SingleGuid80WHERE81  BitProperty = 082UNION83SELECT hhp.UID_Person84FROM @PersonsMe me85JOIN HelperHeadPerson hhp86  ON hhp.UID_PersonHead = me.UID_SingleGuid87WHERE88  hhp.XOrigin > 089UNION90SELECT me.UID_SingleGuid91FROM @PersonsMe me92END93ELSE94BEGIN95  INSERT INTO @MyReports(UID_SingleGuid)96  SELECT me.UID_SingleGuid97  FROM @PersonsMe me98END99IF EXISTS(100  SELECT TOP 1 1101  FROM PersonWantsOrg pwo102  JOIN @MyReports p103    ON pwo.UID_PersonOrdered = p.UID_SingleGuid104  WHERE105    pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg106  UNION all107  SELECT TOP 1 1108  FROM PersonWantsOrg pwo109  JOIN @MyReports p110    ON pwo.UID_PersonInserted = p.UID_SingleGuid111  WHERE112    pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg)113BEGIN114  SELECT @IsVisible = 1115  GOTO TestsDone116END117IF @AsManager = 1118BEGIN119  SELECT120    @ShowClosedAssignment = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\ShowClosedAssignmentOrders'),121    0)122  IF EXISTS(123    SELECT TOP 1 1124    FROM PersonWantsOrg pwo125    JOIN BaseTree b126      ON pwo.ObjectKeyOrgUsedInAssign = b.XObjectKey127    JOIN @MyOrgs mc128      ON b.uid_org = mc.UID_SingleGuid129    WHERE130      pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg AND(pwo.OrderState IN('Assigned', 'Granted', 'New',131  'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') OR @ShowClosedAssignment = 1))132  BEGIN133    SELECT @IsVisible = 1134    GOTO TestsDone135  END136END137IF @AsManager = 0138BEGIN139  IF EXISTS(140    SELECT TOP 1 1141    FROM personwantsorg pwo142    JOIN PWOHelperPWO h143      ON pwo.uid_personwantsorg = h.uid_PersonwantsOrg144    JOIN @PersonsMe me145      ON me.UID_SingleGuid = h.UID_PersonHead AND pwo.OrderState IN('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe')146  WHERE147    pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg)148  BEGIN149    SELECT @IsVisible = 1150    GOTO TestsDone151  END152  IF EXISTS(153    SELECT TOP 1 1154    FROM PWODecisionHistory h155    JOIN PersonWantsOrg pwo156      ON h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg157    JOIN @PersonsMe me158      ON me.UID_SingleGuid = h.UID_PersonHead159    WHERE160      pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg)161  BEGIN162    SELECT @IsVisible = 1163    GOTO TestsDone164  END165  IF EXISTS(166    SELECT TOP 1 1167    FROM Delegation d168    JOIN @PersonsMe me169      ON d.UID_PersonReceiver = me.UID_SingleGuid170    WHERE171      d.UID_PersonWantsOrg = @UID_PersonWantsOrg)172  BEGIN173    SELECT @IsVisible = 1174    GOTO TestsDone175  END176  IF EXISTS(177    SELECT TOP 1 1178    FROM PersonWantsOrg pwo179    JOIN PersonWantsOrg co180      ON pwo.UID_ShoppingCartOrder = co.UID_ShoppingCartOrder181    JOIN PWOHelperPWO h182      ON co.uid_personwantsorg = h.uid_PersonwantsOrg183    JOIN @PersonsMe me184      ON me.UID_SingleGuid = h.UID_PersonHead185    WHERE186      co.OrderState IN('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') AND pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg)187  BEGIN188    SELECT @IsVisible = 1189    GOTO TestsDone190  END191END192TestsDone: endLabel:193RETURN(@isvisible)194END
Open raw exported source
SQL ยท Raw29 lines
1  create   function dbo.QER_FGIPWOVisibleForPerson (@UID_Person varchar(38) , @UID_PersonWantsOrg varchar(38) , @AsManager bit   ) returns bit begin2    declare @IsVisible bit = 0 declare @MyReports QBM_YSingleGUID   declare @MyOrgs QBM_YSingleGUID   declare @MyOrgsBasis QBM_YSingleGUID   declare @PersonsMe3 QBM_YSingleGUID  declare @ShowClosedAssignment int insert into @PersonsMe (UID_SingleGuid ) select distinct me.UID_PersonAlsoMe from QER_VPersonsAreMe4 me where me.UID_PersonOrigin = @uid_person     if @AsManager = 1 begin                insert into @MyOrgsBasis(UID_SingleGuid) select x.UID_Org from( 5select hho.UID_Org from HelperHeadOrg hho join @PersonsMe me on hho.UID_PersonHead = me.UID_SingleGuid where hho.XOrigin > 0  union all select UID_Org 6from BaseTree b join @PersonsMe me on b.UID_PersonHead = me.UID_SingleGuid union all select UID_Org f from BaseTree b join @PersonsMe me on b.UID_PersonHeadSecond7 = me.UID_SingleGuid ) as x group by x.UID_Org  insert into @MyOrgs (UID_SingleGuid , BitProperty ) select x.uid_org, min(x.isParent) from ( select b.UID_SingleGuid8 as UID_Org , 0 as isParent from @MyOrgsBasis b union all select co.UID_Org, 0 from @MyOrgsBasis b join BaseTreeCollectionF co on b.UID_SingleGuid  = co.UID_ParentOrg9 and co.UID_Org <> co.UID_ParentOrg union all select co.UID_ParentOrg, case when co.UID_Org = co.UID_ParentOrg then 0 else 1 end from @MyOrgsBasis b join10 BaseTreeCollectionF co on b.UID_SingleGuid  = co.UID_Org and co.UID_Org <> co.UID_ParentOrg ) as x group by x.UID_Org insert into @MyReports(UID_SingleGuid11 )  select hpo.uid_person from @MyOrgs c join helperpersonorg hpo on hpo.uid_org = c.UID_SingleGuid   where BitProperty  = 0 union  select hhp.UID_Person12 from @PersonsMe me join HelperHeadPerson hhp on hhp.UID_PersonHead = me.UID_SingleGuid  where hhp.XOrigin > 0  union select me.UID_SingleGuid  from @PersonsMe13 me end else begin  insert into @MyReports(UID_SingleGuid ) select me.UID_SingleGuid  from @PersonsMe me end if exists (select top 1 1    from PersonWantsOrg14 pwo join @MyReports p on pwo.UID_PersonOrdered = p.UID_SingleGuid    where pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg union all     select top 1 1 from15 PersonWantsOrg pwo join @MyReports p on pwo.UID_PersonInserted = p.UID_SingleGuid    where pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg ) begin select16 @IsVisible = 1 goto TestsDone end if @AsManager = 1 begin    select @ShowClosedAssignment = dbo.QBM_FCVStringToInt( dbo.QBM_FGIConfigparmValue('QER\ITShop\ShowClosedAssignmentOrders'17) , 0) if exists (select top 1 1  from PersonWantsOrg pwo       join BaseTree b on pwo.ObjectKeyOrgUsedInAssign = b.XObjectKey join @MyOrgs mc on b.uid_org18 = mc.UID_SingleGuid   where   pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg and ( pwo.OrderState in ('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate'19, 'OrderUnsubscribe', 'Waiting') or @ShowClosedAssignment = 1 ) ) begin select @IsVisible = 1 goto TestsDone end end  if @AsManager = 0 begin    if exists20 (select top 1 1 from personwantsorg pwo join PWOHelperPWO h on pwo.uid_personwantsorg = h.uid_PersonwantsOrg join @PersonsMe me on me.UID_SingleGuid  21= h.UID_PersonHead      and pwo.OrderState in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') where pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg22 ) begin select @IsVisible = 1 goto TestsDone end    if exists (select top 1 1 from PWODecisionHistory h join PersonWantsOrg pwo on h.UID_PersonWantsOrg23 = pwo.UID_PersonWantsOrg join @PersonsMe me on me.UID_SingleGuid  = h.UID_PersonHead  where  pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg ) begin select24 @IsVisible = 1 goto TestsDone end    if exists (select top 1 1 from Delegation d join @PersonsMe me on d.UID_PersonReceiver = me.UID_SingleGuid  where25   d.UID_PersonWantsOrg = @UID_PersonWantsOrg ) begin select @IsVisible = 1 goto TestsDone end    if exists(select top 1 1 from PersonWantsOrg pwo join26 PersonWantsOrg co on pwo.UID_ShoppingCartOrder = co.UID_ShoppingCartOrder join PWOHelperPWO h on co.uid_personwantsorg = h.uid_PersonwantsOrg join @PersonsMe27 me on me.UID_SingleGuid  = h.UID_PersonHead      where co.OrderState in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') and pwo.UID_PersonWantsOrg28 = @UID_PersonWantsOrg ) begin select @IsVisible = 1 goto TestsDone end end  TestsDone: endLabel: return(@isvisible) end 29