dbo.QER_FTPWODecisionPossible

1create function dbo.QER_FTPWODecisionPossible(@UID_personWantsOrg varchar(38) , @LevelNumber int , @SubLevelNumber int  ) returns table with schemabinding
2 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_PersonAdditional
4 ,  h.UID_PWOHelperPWO ) as UID_PersonAdditional , isnull(UID_PersonHead,  reverse(h.UID_PWOHelperPWO)) as UID_PersonHead  , Decision from dbo.PWOHelperPWO
5 h where h.UID_PersonWantsOrg = @UID_personWantsOrg and h.LevelNumber = @LevelNumber ) as y group by y.UID_PersonHead, y.UID_QERWorkingStep, y.SubLevelNumber
6 ) 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.CountDeciderMinimum
8 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 end
10 as CountDeciderMinimum , y.CountDecided , y.CountDeciderAvailable , y.IsDenied from ( select x.sublevelnumber  , isnull(min(x.CountApprover), 1) as CountDeciderMinimum
11 , isnull(SUM (x.isDecided), 0) as CountDecided , isnull(COUNT(*), 0) as CountDeciderAvailable , isnull(sum(x.IsDeciderOnLevel0), 0) as CountDeciderOnLevel0
12 , 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 when
13 kAidH.UID_QERWorkingStep  > ' ' then 1 else s.CountApprover end as CountApprover , case when isnull(h1.decision, '') in('P', 'N', 'R', '+') then 1 else
14 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 IsDeciderOnLevel0
15 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(@SubLevelNumber
17, x.SubLevelNumber)  ) as y ) as z ) as w ) 
18