BOR Report

select campus.description as Campus, person.uhuuid, person.Name, positions.positions,
e.primary_department as Department, e.primary_college as College,
concat(ar.classification_code,ar.code) as Current_Rank,ar.description as Current_Title,
application_type.description as Proposed_Action,
case
 when application_type.id=1 then concat(ar.classification_code,ar.code)
 else concat(ar.classification_code,ar.code+1)
end as Proposed_Rank,
case
 when application_type.id=1 then ar.description
 else (select description from appointment_rank where ar.id+1=appointment_rank.id)
end as Proposed_Title,
e.subject as Discipline,
case
    when dpc_review.recommendation='Y' then 'R'
    when dpc_review.recommendation='N' then 'NR'
    when dpc_review.recommendation='D' then '-'
    else 'NA' 
end as DPC,
case
    when dpc_review.recommendation2='Y' then 'R'
    when dpc_review.recommendation2='N' then 'NR'
    when dpc_review.recommendation2='D' then '-'
    else 'NA'
end as DPC_P,
case
    when dpc2_review.recommendation='Y' then 'R'
    when dpc2_review.recommendation='N' then 'NR'
    when dpc2_review.recommendation='D' then '-'
    else 'NA'
end as DPC2nd,
case
    when dpc2_review.recommendation2='Y' then 'R'
    when dpc2_review.recommendation2='N' then 'NR'
    when dpc2_review.recommendation2='D' then '-'
    else 'NA'
end as DPC2nd_P,
case
    when dc_review.recommendation='Y' then 'R'
    when dc_review.recommendation='N' then 'NR'
    when dc_review.recommendation='D' then '-'
    else 'NA' 
end as DC,
case
    when dc_review.recommendation2='Y' then 'R'
    when dc_review.recommendation2='N' then 'NR'
    when dc_review.recommendation2='D' then '-'
    else 'NA'
end as DC_P,
case
    when dean_review.recommendation='Y' then 'R'
    when dean_review.recommendation='N' then 'NR'
    when dean_review.recommendation='D' then '-'
    else 'NA'
end as Dean,
case
    when dean_review.recommendation2='Y' then 'R'
    when dean_review.recommendation2='N' then 'NR'
    when dean_review.recommendation2='D' then '-'
    else 'NA'
end as Dean_P,
case
    when tprc_review.recommendation='Y' then 'R'
    when tprc_review.recommendation='N' then 'NR'
    when tprc_review.recommendation='D' then '-'
    else 'NA'
end as TRPC,
case
    when tprc_review.recommendation2='Y' then 'R'
    when tprc_review.recommendation2='N' then 'NR'
    when tprc_review.recommendation2='D' then '-'
    else 'NA'
end as TRPC_P,
case
    when tprc2_review.recommendation='Y' then 'R'
    when tprc2_review.recommendation='N' then 'NR'
    when tprc2_review.recommendation='D' then '-'
    else 'NA'
end as TRPC2nd,
case
    when tprc2_review.recommendation2='Y' then 'R'
    when tprc2_review.recommendation2='N' then 'NR'
    when tprc2_review.recommendation2='D' then '-'
    else 'NA'
end as TRPC2nd_P,
case
    when chancellor_review.recommendation='Y' then 'R'
    when chancellor_review.recommendation='N' then 'NR'
    when chancellor_review.recommendation='D' then '-'
    else 'NA' 
end as Chancellor,
case
    when chancellor_review.recommendation2='Y' then 'R'
    when chancellor_review.recommendation2='N' then 'NR'
    when chancellor_review.recommendation2='D' then '-'
    else 'NA'
end as Chancellor_P,
case
 when (chancellor_review.recommendation='Y' or chancellor_review.recommendation2='Y') then 'Positive'
 else 'Negative'
end as Final_Action
from application a, office, person, campus, eligibility e, appointment_rank ar, application_role, application_type, state,
(select app.id as application_id,arec.recommendation,arec.recommendation2 from application app left join (select * from application_recommendation where reviewer='DPC') arec on app.id=arec.application_id) dpc_review,
(select app.id as application_id,arec.recommendation,arec.recommendation2 from application app left join (select * from application_recommendation where reviewer='DPC-2ND') arec on app.id=arec.application_id) dpc2_review,
(select app.id as application_id,arec.recommendation,arec.recommendation2 from application app left join (select * from application_recommendation where reviewer='DC') arec on app.id=arec.application_id) dc_review,
(select app.id as application_id,arec.recommendation,arec.recommendation2 from application app left join (select * from application_recommendation where reviewer='Dean') arec on app.id=arec.application_id) dean_review,
(select app.id as application_id,arec.recommendation,arec.recommendation2 from application app left join (select * from application_recommendation where reviewer='TPRC') arec on app.id=arec.application_id) tprc_review,
(select app.id as application_id,arec.recommendation,arec.recommendation2 from application app left join (select * from application_recommendation where reviewer='TPRC-2ND') arec on app.id=arec.application_id) tprc2_review,
(select app.id as application_id,arec.recommendation,arec.recommendation2 from application app left join (select * from application_recommendation where reviewer='Chancellor') arec on app.id=arec.application_id) chancellor_review,
(SELECT emp.uhuuid, GROUP_CONCAT(emp.position_no separator ', ') as positions FROM hrdw_employee emp LEFT JOIN person ON emp.uhuuid=person.uhuuid GROUP BY emp.uhuuid) positions
where a.office_id=office.id and
a.id=e.application_id and
application_role.person_id=person.id and
application_role.application_id=a.id and
application_role.role_id=1 and
office.campus_id=campus.id and
e.current_rank_id=ar.id and
a.year=${year} and
dpc_review.application_id=a.id and
dpc2_review.application_id=a.id and
dc_review.application_id=a.id and
dean_review.application_id=a.id and
tprc_review.application_id=a.id and
tprc2_review.application_id=a.id and
chancellor_review.application_id=a.id and
application_type.id=a.application_type_id and
a.application_type_id<>5 and
person.uhuuid=positions.uhuuid and
(state.id=118 or state.id=122 or state.id=115) and
campus.code=${campusCode}
group by a.id
order by campus