Back to OIM Explorer

dbo.QER_FGIPWODecisionPossible

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 2.035 characters

Interpretation

  • Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

  • No extracted relations.

Typed Edges

  • No typed edges extracted for this source.

References

  • No direct source references extracted.

Referenced By

Complete Source

SQL73 lines
1CREATE FUNCTION dbo.QER_FGIPWODecisionPossible(2  @UID_personWantsOrg varchar(38),3  @LevelNumber int,4  @SubLevelNumber int5) RETURNS int6  WITH schemabinding7AS8BEGIN9  DECLARE @erg int10  SELECT TOP 1 @erg = CASE MIN(w.LevelResult)11  WHEN 1 THEN12  013  WHEN 0 THEN14  115  ELSE -116  END17  FROM(18  SELECT CASE19  WHEN z.CountDeciderAvailable < z.CountDeciderMinimum THEN20  -121  WHEN z.CountDecided >= z.CountDeciderMinimum OR z.IsDenied = 1 THEN22  123  WHEN z.CountDeciderAvailable >= z.CountDeciderMinimum AND z.CountDecided < z.CountDeciderMinimum AND z.IsDenied = 0 THEN24  025  ELSE -126  END AS LevelResult,27  z.IsDenied28  FROM(29  SELECT CASE30  WHEN y.RulerLevel > 0 THEN31  132  WHEN y.CountDeciderMinimum = -1 THEN33  CASE34    WHEN y.CountDeciderAvailable < 1 THEN35  136  ELSE y.CountDeciderAvailable37  END38  ELSE y.CountDeciderMinimum39  END AS CountDeciderMinimum, y.CountDecided, y.CountDeciderAvailable, y.IsDenied40  FROM(41  SELECT42    x.uid_personwantsorg, x.levelnumber, x.sublevelnumber, x.uid_QERWorkingStep, isnull(x.CountApprover,43    1) AS CountDeciderMinimum, isnull(SUM(x.isDecided), 0) AS CountDecided, isnull(COUNT(*), 0) AS CountDeciderAvailable,44    isnull(MIN(x.RulerLevel), 0) AS RulerLevel, ISNULL(max(x.IsDenied), 0) AS IsDenied45  FROM(46  SELECT47    h1.uid_personwantsorg, h1.levelnumber, h1.sublevelnumber, s.uid_QERWorkingStep, CASE s.CountApprover48    WHEN NULL THEN49    150    WHEN 0 THEN51    152  ELSE s.CountApprover53  END AS CountApprover, CASE54  WHEN isnull(h1.decision, '') IN('P', 'N', 'R', '+') THEN55  156  ELSE 057  END AS IsDecided, h1.RulerLevel, CASE58  WHEN isnull(h1.decision, '') IN('N') THEN59  160  ELSE 061  END AS IsDenied62  FROM dbo.PWOHelperPWO h163  JOIN dbo.QERWorkingStep s64    ON h1.uid_QERWorkingStep = s.uid_QERWorkingStep65  WHERE66    h1.uid_personwantsorg = @UID_personWantsOrg AND h1.levelnumber = @LevelNumber AND h1.sublevelnumber = CASE67  WHEN @SubLevelNumber IS NULL THEN68  h1.SubLevelNumber69  ELSE @SubLevelNumber70  END) AS x71  GROUP BY x.uid_personwantsorg, x.levelnumber, x.sublevelnumber, x.UID_QERWorkingStep, x.CountApprover) AS y) AS z) AS w endLabel:72  RETURN(@erg)73END
Open raw exported source
SQL ยท Raw15 lines
1    create   function dbo.QER_FGIPWODecisionPossible(@UID_personWantsOrg varchar(38) , @LevelNumber int , @SubLevelNumber int  ) returns int with2 schemabinding as begin declare @erg int  select top 1 @erg = case MIN(w.LevelResult) when 1 then 0  when 0 then 1  else -1  end from ( select case when3 z.CountDeciderAvailable < z.CountDeciderMinimum then -1  when z.CountDecided >= z.CountDeciderMinimum OR z.IsDenied = 1 then 1  when z.CountDeciderAvailable4 >= z.CountDeciderMinimum and z.CountDecided < z.CountDeciderMinimum and z.IsDenied = 0 then 0  else -1  end as LevelResult , z.IsDenied from ( select 5case when y.RulerLevel > 0  then 1 when y.CountDeciderMinimum = -1 then case when y.CountDeciderAvailable < 1 then 1 else y.CountDeciderAvailable end else6 y.CountDeciderMinimum end as CountDeciderMinimum , y.CountDecided , y.CountDeciderAvailable , y.IsDenied from ( select x.uid_personwantsorg, x.levelnumber7, x.sublevelnumber, x.uid_QERWorkingStep , isnull(x.CountApprover, 1) as CountDeciderMinimum , isnull(SUM (x.isDecided), 0) as CountDecided , isnull(COUNT8(*), 0) as CountDeciderAvailable , isnull(MIN(x.RulerLevel),0) as RulerLevel  , ISNULL(max(x.IsDenied), 0) as IsDenied from ( select h1.uid_personwantsorg9, h1.levelnumber, h1.sublevelnumber, s.uid_QERWorkingStep , case s.CountApprover when null then 1 when 0 then 1 else s.CountApprover end as CountApprover10 , case when isnull(h1.decision, '') in('P', 'N', 'R', '+') then 1 else 0 end as IsDecided , h1.RulerLevel , case when isnull(h1.decision, '') in('N') 11then 1 else 0 end as IsDenied from dbo.PWOHelperPWO h1 join dbo.QERWorkingStep s on h1.uid_QERWorkingStep = s.uid_QERWorkingStep where h1.uid_personwantsorg12 = @UID_personWantsOrg and h1.levelnumber = @LevelNumber and h1.sublevelnumber = case when @SubLevelNumber IS null then h1.SubLevelNumber else @SubLevelNumber13 end   ) as x group by x.uid_personwantsorg, x.levelnumber, x.sublevelnumber, x.UID_QERWorkingStep, x.CountApprover ) as y ) as z ) as w    endLabel: return14(@erg) end 15