dbo.ATT_FTATTDecisionPossible

1create function dbo.ATT_FTATTDecisionPossible(@UID_AttestationCase varchar(38) , @LevelNumber int , @SubLevelNumber int   ) returns table with schemabinding
2 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.CountDeciderAvailable
3 < z.CountDeciderMinimum then -1  when z.CountDecided >= z.CountDeciderMinimum OR z.IsDenied = 1 then 1  when z.CountDeciderAvailable >= z.CountDeciderMinimum
4 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.CountDeciderMinimum
6 end as CountDeciderMinimum , y.CountDecided , y.CountDeciderAvailable , y.IsDenied from ( select x.UID_AttestationCase, x.levelnumber, x.sublevelnumber
7, x.uid_QERWorkingStep , isnull(x.CountApprover, 1) as CountDeciderMinimum , isnull(SUM (x.isDecided), 0) as CountDecided , isnull(COUNT(*), 0) as CountDeciderAvailable
8 , isnull(MIN(x.RulerLevel),0) as RulerLevel  , ISNULL(max(x.IsDenied), 0) as IsDenied from ( select h1.UID_AttestationCase, h1.levelnumber, h1.sublevelnumber
9, ws.uid_QERWorkingStep , case ws.CountApprover when null then 1 when 0 then 1 else ws.CountApprover end as CountApprover , case when isnull(h1.decision
10, '') 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 from
11 dbo.AttestationHelper h1 join dbo.QERWorkingStep ws on h1.uid_QERWorkingStep = ws.uid_QERWorkingStep  join dbo.AttestationCase ac on h1.UID_AttestationCase
12 = ac.UID_AttestationCase and ac.IsUnderConstruction = 0 where h1.UID_AttestationCase = @UID_AttestationCase and h1.levelnumber = @LevelNumber and h1.sublevelnumber
13 =  @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