Back to OIM Explorer

dbo.ATT_FGIATTDecisionPossible

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 2.071 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.ATT_FGIATTDecisionPossible(2  @UID_AttestationCase 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_AttestationCase, 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_AttestationCase, h1.levelnumber, h1.sublevelnumber, ws.uid_QERWorkingStep, CASE ws.CountApprover48    WHEN NULL THEN49    150    WHEN 0 THEN51    152  ELSE ws.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.AttestationHelper h163  JOIN dbo.QERWorkingStep ws64    ON h1.uid_QERWorkingStep = ws.uid_QERWorkingStep65  WHERE66    h1.UID_AttestationCase = @UID_AttestationCase AND h1.levelnumber = @LevelNumber AND h1.sublevelnumber = CASE67  WHEN @SubLevelNumber IS NULL THEN68  h1.SubLevelNumber69  ELSE @SubLevelNumber70  END) AS x71  GROUP BY x.UID_AttestationCase, x.levelnumber, x.sublevelnumber, x.uid_QERWorkingStep, x.CountApprover) AS y) AS z) AS w option(recompile) endLabel:72  RETURN(@erg)73END
Open raw exported source
SQL ยท Raw15 lines
1   create   function dbo.ATT_FGIATTDecisionPossible(@UID_AttestationCase 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_AttestationCase, 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_AttestationCase9, h1.levelnumber, h1.sublevelnumber, ws.uid_QERWorkingStep , case ws.CountApprover when null then 1 when 0 then 1 else ws.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.AttestationHelper h1 join dbo.QERWorkingStep ws on h1.uid_QERWorkingStep = ws.uid_QERWorkingStep where h1.UID_AttestationCase12 = @UID_AttestationCase and h1.levelnumber = @LevelNumber and h1.sublevelnumber = case when @SubLevelNumber IS null then h1.SubLevelNumber else @SubLevelNumber13 end   ) as x group by x.UID_AttestationCase, x.levelnumber, x.sublevelnumber, x.uid_QERWorkingStep, x.CountApprover ) as y ) as z ) as w option (recompile14)       endLabel: return(@erg) end 15