Back to OIM Explorer

dbo.QER_FTPWODecisionPossible

Inline Table FunctionSQL_INLINE_TABLE_VALUED_FUNCTIONSandbox DB

Inline Table Function.

Source: sandbox-db sys.sql_modules

Source size: 2.555 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

SQL86 lines
1CREATE FUNCTION dbo.QER_FTPWODecisionPossible(2  @UID_personWantsOrg varchar(38),3  @LevelNumber int,4  @SubLevelNumber int5) RETURNS TABLE6  WITH schemabinding7AS8RETURN(9  WITH Auswahl AS(10SELECT11  y.UID_PersonHead, y.UID_QERWorkingStep, y.SubLevelNumber, min(y.RulerLevel) AS RulerLevel, max(y.Decision) AS Decision,12  max(y.UID_PersonAdditional) AS UID_PersonAdditional13FROM(14SELECT15  UID_QERWorkingStep, SubLevelNumber, RulerLevel, isnull(UID_PersonAdditional, h.UID_PWOHelperPWO) AS UID_PersonAdditional,16  isnull(UID_PersonHead, reverse(h.UID_PWOHelperPWO)) AS UID_PersonHead, Decision17FROM dbo.PWOHelperPWO h18WHERE19  h.UID_PersonWantsOrg = @UID_personWantsOrg AND h.LevelNumber = @LevelNumber) AS y20GROUP BY y.UID_PersonHead, y.UID_QERWorkingStep, y.SubLevelNumber)21SELECT TOP 1 CASE MIN(w.LevelResult)22WHEN 1 THEN23024WHEN 0 THEN25126ELSE -127END AS DecisionPossible28FROM(29SELECT CASE30WHEN z.CountDeciderAvailable < z.CountDeciderMinimum THEN31-132WHEN z.CountDecided >= z.CountDeciderMinimum OR z.IsDenied = 1 THEN33134WHEN z.CountDeciderAvailable >= z.CountDeciderMinimum AND z.CountDecided < z.CountDeciderMinimum AND z.IsDenied = 0 THEN35036ELSE -137END AS LevelResult38FROM(39SELECT CASE40WHEN y.CountDeciderOnLevel0 = 0 THEN41142WHEN y.CountDeciderMinimum = -1 THEN43CASE44  WHEN y.CountDeciderOnLevel0 = 0 THEN45146ELSE y.CountDeciderOnLevel047END48ELSE y.CountDeciderMinimum49END AS CountDeciderMinimum, y.CountDecided, y.CountDeciderAvailable, y.IsDenied50FROM(51SELECT52  x.sublevelnumber, isnull(min(x.CountApprover), 1) AS CountDeciderMinimum, isnull(SUM(x.isDecided),53  0) AS CountDecided, isnull(COUNT(*), 0) AS CountDeciderAvailable, isnull(sum(x.IsDeciderOnLevel0), 0) AS CountDeciderOnLevel0,54  ISNULL(max(x.IsDenied), 0) AS IsDenied55FROM(56SELECT57  h1.sublevelnumber, CASE58  WHEN s.CountApprover IS NULL THEN59  160  WHEN s.CountApprover = 0 THEN61  162  WHEN kAidH.UID_QERWorkingStep > ' ' THEN63  164ELSE s.CountApprover65END AS CountApprover, CASE66WHEN isnull(h1.decision, '') IN('P', 'N', 'R', '+') THEN67168ELSE 069END AS IsDecided, CASE70WHEN isnull(h1.decision, '') IN('N') THEN71172ELSE 073END AS IsDenied, CASE74WHEN h1.RulerLevel = 0 THEN75176ELSE 077END IsDeciderOnLevel078FROM Auswahl h179JOIN dbo.QERWorkingStep s80  ON h1.UID_QERWorkingStep = s.UID_QERWorkingStep81LEFT82OUTER83JOIN Auswahl kAidH84  ON kAidH.SubLevelNumber + 1 = h1.SubLevelNumber AND kAidH.UID_PersonAdditional = h1.UID_PersonHead) AS x85GROUP BY x.sublevelnumber86HAVING x.SubLevelNumber = isnull(@SubLevelNumber, x.SubLevelNumber)) AS y) AS z) AS w)
Open raw exported source
SQL ยท Raw18 lines
1create function dbo.QER_FTPWODecisionPossible(@UID_personWantsOrg varchar(38) , @LevelNumber int , @SubLevelNumber int  ) returns table with schemabinding2 as return (  with Auswahl as ( select y.UID_PersonHead, y.UID_QERWorkingStep, y.SubLevelNumber , min(y.RulerLevel) as RulerLevel , max(y.Decision) as 3Decision , max(y.UID_PersonAdditional) as UID_PersonAdditional from( select UID_QERWorkingStep   , SubLevelNumber  , RulerLevel , isnull(UID_PersonAdditional4 ,  h.UID_PWOHelperPWO ) as UID_PersonAdditional , isnull(UID_PersonHead,  reverse(h.UID_PWOHelperPWO)) as UID_PersonHead  , Decision from dbo.PWOHelperPWO5 h where h.UID_PersonWantsOrg = @UID_personWantsOrg and h.LevelNumber = @LevelNumber ) as y group by y.UID_PersonHead, y.UID_QERWorkingStep, y.SubLevelNumber6 ) select top 1 case MIN(w.LevelResult) when 1 then 0  when 0 then 1  else -1  end as DecisionPossible from ( select case when z.CountDeciderAvailable 7< z.CountDeciderMinimum then -1  when z.CountDecided >= z.CountDeciderMinimum OR z.IsDenied = 1 then 1  when z.CountDeciderAvailable >= z.CountDeciderMinimum8 and z.CountDecided < z.CountDeciderMinimum and z.IsDenied = 0 then 0  else -1  end as LevelResult  from ( select case when y.CountDeciderOnLevel0 = 0 9 then 1 when y.CountDeciderMinimum = -1 then case when y.CountDeciderOnLevel0 = 0 then 1 else y.CountDeciderOnLevel0 end else y.CountDeciderMinimum end10 as CountDeciderMinimum , y.CountDecided , y.CountDeciderAvailable , y.IsDenied from ( select x.sublevelnumber  , isnull(min(x.CountApprover), 1) as CountDeciderMinimum11 , isnull(SUM (x.isDecided), 0) as CountDecided , isnull(COUNT(*), 0) as CountDeciderAvailable , isnull(sum(x.IsDeciderOnLevel0), 0) as CountDeciderOnLevel012 , ISNULL(max(x.IsDenied), 0) as IsDenied from ( select h1.sublevelnumber  , case when s.CountApprover is null then 1 when s.CountApprover = 0 then 1 when13 kAidH.UID_QERWorkingStep  > ' ' then 1 else s.CountApprover end as CountApprover , case when isnull(h1.decision, '') in('P', 'N', 'R', '+') then 1 else14 0 end as IsDecided , case when isnull(h1.decision, '') in('N') then 1 else 0 end as IsDenied , case when h1.RulerLevel = 0 then 1 else 0 end IsDeciderOnLevel015 from Auswahl  h1 join dbo.QERWorkingStep s on h1.UID_QERWorkingStep = s.UID_QERWorkingStep left outer join Auswahl  kAidH on kAidH.SubLevelNumber + 1 16= h1.SubLevelNumber and kAidH.UID_PersonAdditional = h1.UID_PersonHead ) as x group by x.sublevelnumber  having x.SubLevelNumber = isnull(@SubLevelNumber17, x.SubLevelNumber)  ) as y ) as z ) as w ) 18