Back to OIM Explorer

dbo.ATT_FTATTDecisionPossible

Inline Table FunctionSQL_INLINE_TABLE_VALUED_FUNCTIONSandbox DB

Inline Table Function.

Source: sandbox-db sys.sql_modules

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

SQL67 lines
1CREATE FUNCTION dbo.ATT_FTATTDecisionPossible(2  @UID_AttestationCase varchar(38),3  @LevelNumber int,4  @SubLevelNumber int5) RETURNS TABLE6  WITH schemabinding7AS8RETURN(9SELECT TOP 1 CASE MIN(w.LevelResult)10WHEN 1 THEN11012WHEN 0 THEN13114ELSE -115END AS DecisionPossible16FROM(17SELECT CASE18WHEN z.CountDeciderAvailable < z.CountDeciderMinimum THEN19-120WHEN z.CountDecided >= z.CountDeciderMinimum OR z.IsDenied = 1 THEN21122WHEN z.CountDeciderAvailable >= z.CountDeciderMinimum AND z.CountDecided < z.CountDeciderMinimum AND z.IsDenied = 0 THEN23024ELSE -125END AS LevelResult, z.IsDenied26FROM(27SELECT CASE28WHEN y.RulerLevel > 0 THEN29130WHEN y.CountDeciderMinimum = -1 THEN31CASE32  WHEN y.CountDeciderAvailable < 1 THEN33134ELSE y.CountDeciderAvailable35END36ELSE y.CountDeciderMinimum37END AS CountDeciderMinimum, y.CountDecided, y.CountDeciderAvailable, y.IsDenied38FROM(39SELECT40  x.UID_AttestationCase, x.levelnumber, x.sublevelnumber, x.uid_QERWorkingStep, isnull(x.CountApprover,41  1) AS CountDeciderMinimum, isnull(SUM(x.isDecided), 0) AS CountDecided, isnull(COUNT(*), 0) AS CountDeciderAvailable,42  isnull(MIN(x.RulerLevel), 0) AS RulerLevel, ISNULL(max(x.IsDenied), 0) AS IsDenied43FROM(44SELECT45  h1.UID_AttestationCase, h1.levelnumber, h1.sublevelnumber, ws.uid_QERWorkingStep, CASE ws.CountApprover46  WHEN NULL THEN47  148  WHEN 0 THEN49  150ELSE ws.CountApprover51END AS CountApprover, CASE52WHEN isnull(h1.decision, '') IN('P', 'N', 'R', '+') THEN53154ELSE 055END AS IsDecided, h1.RulerLevel, CASE56WHEN isnull(h1.decision, '') IN('N') THEN57158ELSE 059END AS IsDenied60FROM dbo.AttestationHelper h161JOIN dbo.QERWorkingStep ws62  ON h1.uid_QERWorkingStep = ws.uid_QERWorkingStep63JOIN dbo.AttestationCase ac64  ON h1.UID_AttestationCase = ac.UID_AttestationCase AND ac.IsUnderConstruction = 065WHERE66  h1.UID_AttestationCase = @UID_AttestationCase AND h1.levelnumber = @LevelNumber AND h1.sublevelnumber = @SubLevelNumber) AS x67GROUP BY x.UID_AttestationCase, x.levelnumber, x.sublevelnumber, x.uid_QERWorkingStep, x.CountApprover) AS y) AS z) AS w)
Open raw exported source
SQL ยท Raw15 lines
1create function dbo.ATT_FTATTDecisionPossible(@UID_AttestationCase varchar(38) , @LevelNumber int , @SubLevelNumber int   ) returns table with schemabinding2 as return ( 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.CountDeciderAvailable3 < z.CountDeciderMinimum then -1  when z.CountDecided >= z.CountDeciderMinimum OR z.IsDenied = 1 then 1  when z.CountDeciderAvailable >= z.CountDeciderMinimum4 and z.CountDecided < z.CountDeciderMinimum and z.IsDenied = 0 then 0  else -1  end as LevelResult , z.IsDenied from ( select case when y.RulerLevel > 50  then 1 when y.CountDeciderMinimum = -1 then case when y.CountDeciderAvailable < 1 then 1 else y.CountDeciderAvailable end else y.CountDeciderMinimum6 end as CountDeciderMinimum , y.CountDecided , y.CountDeciderAvailable , y.IsDenied from ( select x.UID_AttestationCase, x.levelnumber, x.sublevelnumber7, x.uid_QERWorkingStep , isnull(x.CountApprover, 1) as CountDeciderMinimum , isnull(SUM (x.isDecided), 0) as CountDecided , isnull(COUNT(*), 0) as CountDeciderAvailable8 , isnull(MIN(x.RulerLevel),0) as RulerLevel  , ISNULL(max(x.IsDenied), 0) as IsDenied from ( select h1.UID_AttestationCase, h1.levelnumber, h1.sublevelnumber9, ws.uid_QERWorkingStep , case ws.CountApprover when null then 1 when 0 then 1 else ws.CountApprover end as CountApprover , case when isnull(h1.decision10, '') in('P', 'N', 'R', '+') then 1 else 0 end as IsDecided , h1.RulerLevel , case when isnull(h1.decision, '') in('N') then 1 else 0 end as IsDenied from11 dbo.AttestationHelper h1 join dbo.QERWorkingStep ws on h1.uid_QERWorkingStep = ws.uid_QERWorkingStep  join dbo.AttestationCase ac on h1.UID_AttestationCase12 = ac.UID_AttestationCase and ac.IsUnderConstruction = 0 where h1.UID_AttestationCase = @UID_AttestationCase and h1.levelnumber = @LevelNumber and h1.sublevelnumber13 =  @SubLevelNumber       ) as x group by x.UID_AttestationCase, x.levelnumber, x.sublevelnumber, x.uid_QERWorkingStep, x.CountApprover ) as y ) as z )14 as w       ) 15