Thu, Jan 17, 2013 at 11:59 AMThu, 11:59 AM Message starred from Neha Varma to you [No Subject] Show Details select to_char(t1.type) TYPE, to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC, to_char(YTD) YTD--,t2.Prior_YEAR from (select '# Inspections Created' type, SUM(CASE WHEN mon= '01' THEN 1 ELSE 0 END) Jan, SUM(CASE WHEN mon= '02' THEN 1 ELSE 0 END) Feb, SUM(CASE WHEN mon= '03' THEN 1 ELSE 0 END) Mar, SUM(CASE WHEN mon= '04' THEN 1 ELSE 0 END) Apr, SUM(CASE WHEN mon= '05' THEN 1 ELSE 0 END) May, SUM(CASE WHEN mon= '06' THEN 1 ELSE 0 END)Jun, SUM(CASE WHEN mon= '07' THEN 1 ELSE 0 END) Jul, SUM(CASE WHEN mon= '08' THEN 1 ELSE 0 END) Aug, SUM(CASE WHEN mon= '09' THEN 1 ELSE 0 END) Sep, SUM(CASE WHEN mon= '10' THEN 1 ELSE 0 END) Oct, SUM(CASE WHEN mon= '11' THEN 1 ELSE 0 END) Nov, SUM(CASE WHEN mon= '12' THEN 1 ELSE 0 END) DEC, count(*) YTD FROM ( select cas.row_id case_id, proj.row_id proj_id,cas.case_num ,to_char(cas.x_lease_end_dt,'MM') mon, cas.x_sec8_move_out_dt , proj.proj_num ,proj.x_result_cd , proj.status_cd ,proj.created ,evt.act_start_dt ,evt.act_end_dt ,evt.act_status, evt.reason_cannot_inspect ,evt.inspector_id,evt.activity_created, ROW_NUMBER() OVER ( partition BY proj.case_id order by case when proj.x_result_cd in ('Pass','Pass with Comments') then 1 when proj.x_result_cd in ('Fail','Fail Tenant-Caused Damage') then 1 end, proj.created desc nulls last ) ins, rank() OVER ( partition BY evt.proj_id order by case when evt.act_status ='Done' then 1 end,evt.activity_created desc ) activity from sbl_proj proj, siebel.s_case cas,ins_activity_stg evt where proj.CASE_ID = cas.row_id and proj.row_id=evt.proj_id and cas.type_cd='Section 8' and cas.stage_cd='Tenancy' and cas.status_cd='Active' and cas.sub_status_cd='Rented' and cas.x_Voucher_Category_cd not in ('City/State Conversion Program') --- and proj.proj_type_cd ='Annual' and cas.x_lease_end_dt is not null and proj.created between add_months(to_date(to_char(cas.x_lease_end_dt,'MM')||'-'||:p_year,'MM-YY'),-6) and add_months(to_date(to_char(cas.x_lease_end_dt,'MM')||'-'||:p_year,'MM-YY'),6) ) WHERE ins= 1 and activity=1 )t1 union all select to_char(t1.type) TYPE, to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC, to_char(YTD) YTD--,t2.Prior_YEAR from (select '# COMPLETE' type, SUM(CASE WHEN mon= '01' THEN 1 ELSE 0 END) Jan, SUM(CASE WHEN mon= '02' THEN 1 ELSE 0 END) Feb, SUM(CASE WHEN mon= '03' THEN 1 ELSE 0 END) Mar, SUM(CASE WHEN mon= '04' THEN 1 ELSE 0 END) Apr, SUM(CASE WHEN mon= '05' THEN 1 ELSE 0 END) May, SUM(CASE WHEN mon= '06' THEN 1 ELSE 0 END)Jun, SUM(CASE WHEN mon= '07' THEN 1 ELSE 0 END) Jul, SUM(CASE WHEN mon= '08' THEN 1 ELSE 0 END) Aug, SUM(CASE WHEN mon= '09' THEN 1 ELSE 0 END) Sep, SUM(CASE WHEN mon= '10' THEN 1 ELSE 0 END) Oct, SUM(CASE WHEN mon= '11' THEN 1 ELSE 0 END) Nov, SUM(CASE WHEN mon= '12' THEN 1 ELSE 0 END) DEC, count(*) YTD FROM -- select * from ( select case_num,lease_end_month mon,insp_num,ins_result,ins_status, reason,prev_actstartdt,prev_actenddt,latest_actstartdt,latest_actenddt, inspector_id,act_status,proj_created from inspections_stg_1 where inspection_yr=:p_year -- nvl(prev_actstartdt,proj_created)30 days' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,round((sysdate-sr.sr_stat_dt),0) diff from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product in ('Lease Renewal','Contract Rent Change') and sr.sr_area in ('Standard','Commissioner Order') -- and to_char(sr.created,'MM') between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon and to_char(sr.created,'YYYY')= :p_year ) where rn=1)sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') and sr.sr_stat_id in ('Paused','In Progress') and sr.sr_sub_stat_id in ('Pending System Update','Documents Received','Eligible For Lease Renewal', 'Pending Inspection Results','Pending Asst Manager Approval') and (sysdate-sr.sr_stat_dt)>=30 ) t -- where t.mon between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon -- and to_char(t.created,'YYYY')= p_year )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from( select '# of Lease-up SRs Created' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) ytd from (select case.case_num,case.x_voucher_iss_dt,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,round((sysdate-sr.sr_stat_dt),0) diff from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product ='Lease Up' and (sr.sr_area in ('In Place Rental','Move-In Rental','Pending Briefing','Port In','Port-Out', 'Rental','Rented','Set Aside','Standard') or sr.sr_area is null) -- and to_char(sr.created,'MM') between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon and to_char(sr.created,'YYYY')= :p_year ) where rn=1)sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') ) t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from( select '# of Lease-up SR Scheduled Briefing' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) ytd from (select case.case_num,case.x_voucher_iss_dt,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,round((sysdate-sr.sr_stat_dt),0) diff from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product ='Lease Up' and (sr.sr_area in ('In Place Rental','Move-In Rental','Pending Briefing','Port In','Port-Out', 'Rental','Rented','Set Aside','Standard') or sr.sr_area is null) -- and to_char(sr.created,'MM') between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id='Open' and sr_sub_stat_id ='Pending Briefing' )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') ) t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from( select '# of Lease-up SR Attended Briefing' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) ytd from (select case.case_num,case.x_voucher_iss_dt,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,round((sysdate-sr.sr_stat_dt),0) diff from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product ='Lease Up' and (sr.sr_area in ('In Place Rental','Move-In Rental','Pending Briefing','Port In','Port-Out', 'Rental','Rented','Set Aside','Standard') or sr.sr_area is null) -- and to_char(sr.created,'MM') between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id='Open' and sr_sub_stat_id ='Closed Briefing' )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') ) t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from( select '# of Lease-Up SR In Progress' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) ytd from (select case.case_num,case.x_voucher_iss_dt,case.status_cd,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * -- distinct sr_stat_id, sr_sub_stat_id from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product ='Lease Up' and (sr.sr_area in ('In Place Rental','Move-In Rental','Pending Briefing','Port In','Port-Out', 'Rental','Rented','Set Aside','Standard') or sr.sr_area is null) -- and to_char(sr.created,'MM') between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and (sr_stat_id in ('Open','In Progress','Pending Review','Paused','Completed') and (sr_sub_stat_id in ('Assistant Manager','Pending AI','Pending Validation','In Validation','Pending Approval', 'Rental Housing Assistant','BRU Review','Pending Scanning') or sr_sub_stat_id is null) ) )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') ) t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from( select '# of Lease-Up SRs Cancelled' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) ytd from (select case.case_num,case.x_voucher_iss_dt,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,round((sysdate-sr.sr_stat_dt),0) diff from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product ='Lease Up' and (sr.sr_area in ('In Place Rental','Move-In Rental','Pending Briefing','Port In','Port-Out', 'Rental','Rented','Set Aside','Standard') or sr.sr_area is null) -- and to_char(sr.created,'MM') between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('Cancelled','Error') -- or (sr_stat_id ='Closed' -- and sr_sub_stat_id in ('Ineligible','Expired','Disapproved') -- ) )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.x_voucher_category_cd not in ('City/State Conversion Program') ) t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from( select '# of Lease-Up SRs Closed' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) ytd from (select case.case_num,case.x_voucher_iss_dt,case.status_cd,case.stage_cd,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,round((sysdate-sr.sr_stat_dt),0) diff from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product ='Lease Up' and (sr.sr_area in ('In Place Rental','Move-In Rental','Pending Briefing','Port In','Port-Out', 'Rental','Rented','Set Aside','Standard') or sr.sr_area is null) -- and to_char(sr.created,'MM') between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id='Closed' -- and (sr_sub_stat_id in ('Pending Briefing','Closed Briefing','In Validation','Rented') -- or sr_sub_stat_id is null) )sr where case.row_id=sr.case_id and case.type_cd='Section 8' -- and case.status_cd='Active' -- and case.sub_status_cd='Rented' -- and case.stage_cd='Tenancy' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') ) t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from( select '# of Participants who Leased-Up' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) ytd from (select case.case_num,case.x_voucher_iss_dt,case.status_cd,case.stage_cd,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,round((sysdate-sr.sr_stat_dt),0) diff from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product ='Lease Up' and (sr.sr_area in ('In Place Rental','Move-In Rental','Pending Briefing','Port In','Port-Out', 'Rental','Rented','Set Aside','Standard') or sr.sr_area is null) -- and to_char(sr.created,'MM') between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id='Closed' -- and (sr_sub_stat_id in ('Pending Briefing','Closed Briefing','In Validation','Rented') -- or sr_sub_stat_id is null) )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.stage_cd='Tenancy' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') ) t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from ( select 'Average Number of Days from Lease-up SR Creation to Closed' type, sum(case when t.mon = '01' then round(t.days/t.tot,0) else 0 end) Jan, sum(case when t.mon = '02' then round(t.days/t.tot,0) else 0 end) Feb, sum(case when t.mon = '03' then round(t.days/t.tot,0) else 0 end) Mar, sum(case when t.mon = '04' then round(t.days/t.tot,0) else 0 end) Apr, sum(case when t.mon = '05' then round(t.days/t.tot,0) else 0 end) May, sum(case when t.mon = '06' then round(t.days/t.tot,0) else 0 end) Jun, sum(case when t.mon = '07' then round(t.days/t.tot,0) else 0 end) Jul, sum(case when t.mon = '08' then round(t.days/t.tot,0) else 0 end) Aug, sum(case when t.mon = '09' then round(t.days/t.tot,0) else 0 end) Sep, sum(case when t.mon = '10' then round(t.days/t.tot,0) else 0 end) Oct, sum(case when t.mon = '11' then round(t.days/t.tot,0) else 0 end) Nov, sum(case when t.mon = '12' then round(t.days/t.tot,0) else 0 end) Dec, round(sum(t.days)/sum(t.tot),0) Ytd from (select mon, sum(diff) days,count(*) tot from (select case.case_num,case.x_voucher_iss_dt,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.diff from siebel.s_case case , (select case_id,sr_num,created,ins_product,sr_area,sr_stat_id, sr_sub_stat_id,sr_stat_dt,resolution_cd,round(sr_stat_dt - created,0) diff from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.sr_stat_id, sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product ='Lease Up' and (sr.sr_area in ('In Place Rental','Move-In Rental','Pending Briefing','Port In','Port-Out', 'Rental','Rented','Set Aside','Standard') or sr.sr_area is null) -- and to_char(sr.created,'MM') between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id='Closed' )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.sub_status_cd='Rented' and case.x_voucher_category_cd not in ('City/State Conversion Program') ) group by mon ) t )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# of Recertifications SRs Created' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (SELECT case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(case.x_next_annual_recert_dt,'MM') mon, case.X_NEXT_ANNUAL_RECERT_DT,case.X_LAST_ANNUAL_RECERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area = 'Annual' and to_date(sr.created,'DD-MON-YY') between add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),-7) and add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),5) --and to_char(case.x_next_annual_recert_dt,'YY') in ('12','13') and case.x_next_annual_recert_dt is not null -- and case.case_num='1998860' --and to_char(sr.created,'YYYY')= :p_year ) where rn=1 )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# of Recertifications SRs Open' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (SELECT case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(case.x_next_annual_recert_dt,'MM') mon, case.X_NEXT_ANNUAL_RECERT_DT,case.X_LAST_ANNUAL_RECERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area = 'Annual' and to_date(sr.created,'DD-MON-YY') between add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),-7) and add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),5) --and to_char(case.x_next_annual_recert_dt,'YY') in ('12','13') and case.x_next_annual_recert_dt is not null -- and case.case_num='1998860' --and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id not in ('Closed','Cancelled','Complete','Completed') )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# NYCHA Processing' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (SELECT case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(case.x_next_annual_recert_dt,'MM') mon, case.X_NEXT_ANNUAL_RECERT_DT,case.X_LAST_ANNUAL_RECERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area = 'Annual' and to_date(sr.created,'DD-MON-YY') between add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),-7) and add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),5) --and to_char(case.x_next_annual_recert_dt,'YY') in ('12','13') and case.x_next_annual_recert_dt is not null -- and case.case_num='1998860' --and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('Paused','In Progress','Pending Review','Open') and (sr_sub_stat_id in ('Pending System Update','Documents Received', 'Follow -up Required','Follow Up Required','Follow-up Required','Follow-up', 'No Pending Documents','Admin Review','Approval','Interim Documents Received', 'Pending Closure') or sr_sub_stat_id is null) )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# Awaiting Information from Tenant' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (SELECT case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(case.x_next_annual_recert_dt,'MM') mon, case.X_NEXT_ANNUAL_RECERT_DT,case.X_LAST_ANNUAL_RECERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area = 'Annual' and to_date(sr.created,'DD-MON-YY') between add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),-7) and add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),5) --and to_char(case.x_next_annual_recert_dt,'YY') in ('12','13') and case.x_next_annual_recert_dt is not null -- and case.case_num='1998860' --and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('Paused','Open') and sr_sub_stat_id in ('Waiting on Documents','Additional Info Required','Additional Information') )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# Cancelled' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (SELECT case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(case.x_next_annual_recert_dt,'MM') mon, case.X_NEXT_ANNUAL_RECERT_DT,case.X_LAST_ANNUAL_RECERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area = 'Annual' and to_date(sr.created,'DD-MON-YY') between add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),-7) and add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),5) --and to_char(case.x_next_annual_recert_dt,'YY') in ('12','13') and case.x_next_annual_recert_dt is not null -- and case.case_num='1998860' --and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id ='Cancelled' )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# Closed' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (SELECT case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(case.x_next_annual_recert_dt,'MM') mon, case.X_NEXT_ANNUAL_RECERT_DT,case.X_LAST_ANNUAL_RECERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area = 'Annual' and to_date(sr.created,'DD-MON-YY') between add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),-7) and add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),5) --and to_char(case.x_next_annual_recert_dt,'YY') in ('12','13') and case.x_next_annual_recert_dt is not null -- and case.case_num='1998860' --and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('Closed','Complete','Completed') )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# of Recertication SRs in NYCHA Processing>30 days' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (SELECT case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(case.x_next_annual_recert_dt,'MM') mon, case.X_NEXT_ANNUAL_RECERT_DT,case.X_LAST_ANNUAL_RECERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area = 'Annual' and to_date(sr.created,'DD-MON-YY') between add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),-7) and add_months(to_date(to_char(case.x_next_annual_recert_dt,'MM')||'-'||:p_year,'MM-YY'),5) --and to_char(case.x_next_annual_recert_dt,'YY') in ('12','13') and case.x_next_annual_recert_dt is not null -- and case.case_num='1998860' --and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('Paused','In Progress','Pending Review','Open') and (sr_sub_stat_id in ('Pending System Update','Documents Received', 'Follow -up Required','Follow Up Required','Follow-up Required','Follow-up', 'No Pending Documents','Admin Review','Approval','Interim Documents Received', 'Pending Closure') or sr_sub_stat_id is null) and (sysdate- SR_STAT_DT)>=30 )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# of Interim Recertifications SRs Created' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (SELECT case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(sr.created,'MM') mon, case.X_INTRM_CERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area = 'Interim' -- and case.case_num='1998860' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# of Interim Recertifications SRs Open' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (select case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(sr.created,'MM') mon, case.X_INTRM_CERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area = 'Interim' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id not in ('Closed','Cancelled','Complete','Completed') )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# NYCHA Processing' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (SELECT case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(sr.created,'MM') mon, case.X_INTRM_CERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area = 'Interim' -- and case.case_num='1998860' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('Paused','In Progress','Pending Review','Open') and (sr_sub_stat_id in ('Pending System Update','Documents Received', 'Follow -up Required','Follow Up Required','Follow-up Required','Follow-up', 'No Pending Documents','Admin Review','Approval','Interim Documents Received', 'Pending Closure') or sr_sub_stat_id is null) )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# Awaiting Information from Tenant' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (SELECT case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(sr.created,'MM') mon, case.X_INTRM_CERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area='Interim' -- and case.case_num='1998860' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('Paused','Open') and sr_sub_stat_id in ('Waiting on Documents','Additional Info Required','Additional Information') )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# Cancelled' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (SELECT case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(sr.created,'MM') mon, case.X_INTRM_CERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area = 'Interim' -- and case.case_num='1998860' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id ='Cancelled' )t1 union all select to_char(t1.type) TYPE,to_char(t1.Jan) JAN,to_char(t1.Feb) FEB,to_char(t1.Mar) MAR,to_char(t1.Apr) APR,to_char(t1.May) MAY,to_char(t1.Jun) JUN,to_char(t1.Jul) JUL,to_char(t1.Aug) AUG, to_char(t1.Sep) SEP,to_char(t1.Oct) OCT,to_char(t1.Nov) NOV,to_char(t1.Dec) DEC,to_char(t1.Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec) YTD--,t2.Prior_YEAR from (select '# Closed' type, sum(case when mon= '01' then 1 else 0 end) Jan, sum(case when mon= '02' then 1 else 0 end) Feb, sum(case when mon= '03' then 1 else 0 end) Mar, sum(case when mon= '04' then 1 else 0 end) Apr, sum(case when mon= '05' then 1 else 0 end) May, sum(case when mon= '06' then 1 else 0 end) Jun, sum(case when mon= '07' then 1 else 0 end) Jul, sum(case when mon= '08' then 1 else 0 end) Aug, sum(case when mon= '09' then 1 else 0 end) Sep, sum(case when mon= '10' then 1 else 0 end) Oct, sum(case when mon= '11' then 1 else 0 end) Nov, sum(case when mon= '12' then 1 else 0 end) Dec, count(*) YTD from (SELECT case.case_num,sr.sr_stat_dt,sr.x_eff_dt, to_char(sr.created,'MM') mon, case.X_INTRM_CERT_DT, sr.created, sr.SR_STAT_ID,sr.sr_sub_stat_id, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn FROM SIEBEL.S_CASE CASE, siebel.s_srv_req sr WHERE sr.case_id=case.row_id and case.STAGE_CD ='Tenancy' and case.TYPE_CD ='Section 8' and case.STATUS_CD='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' and sr.ins_product = 'Recertification' and sr.sr_area = 'Interim' -- and case.case_num='1998860' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('Closed','Complete','Completed') )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# of Interim Recertication SRs in NYCHA Processing>15 Days' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num, to_char(sr.created,'MM') mon, case.X_INTRM_CERT_DT, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.sr_stat_id, sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_eff_dt, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product = 'Recertification' and sr.sr_area = 'Interim' ) where rn=1 and to_char(created,'YYYY')= :p_year )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd !='City/State Conversion Program' ) t where sr_stat_id in ('Paused','In Progress','Pending Review','Open') and (sr_sub_stat_id in ('Pending System Update','Documents Received','Follow -up Required','Follow Up Required', 'Follow-up Required','Follow-up','No Pending Documents','Admin Review','Approval', 'Interim Documents Received','Pending Closure') or sr_sub_stat_id is null) and (sysdate- sr_stat_dt)>=15 )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# of Emergency Requests ' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_emerg_xfer_flg, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn From siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='Y' and to_char(sr.created,'YYYY')= :p_year ) where rn=1)sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') )t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# of Valid Emergency Transfer Requests' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_emerg_xfer_flg, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='Y' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id<>'Cancelled')sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd <>'City/State Conversion Program' )t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# NYCHA Processing' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_emerg_xfer_flg, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='Y' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('In Progress','Paused') and sr_sub_stat_id in ('Documents Received', 'Follow -up Required','Follow Up Required','Follow-up Required','Follow-up') )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd <>'City/State Conversion Program' )t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# Awaiting Information from Tenant' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_emerg_xfer_flg, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='Y' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('Paused') and sr_sub_stat_id in ('Additional Info Required','Additional Information') )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd <>'City/State Conversion Program' )t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# Complete' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_emerg_xfer_flg, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='Y' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('Complete','Completed','Closed') )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd <>'City/State Conversion Program' )t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from ( select '# Average Days to Review' type, sum(case when t.mon = '01' then round(t.days/t.tot,0) else 0 end) Jan, sum(case when t.mon = '02' then round(t.days/t.tot,0) else 0 end) Feb, sum(case when t.mon = '03' then round(t.days/t.tot,0) else 0 end) Mar, sum(case when t.mon = '04' then round(t.days/t.tot,0) else 0 end) Apr, sum(case when t.mon = '05' then round(t.days/t.tot,0) else 0 end) May, sum(case when t.mon = '06' then round(t.days/t.tot,0) else 0 end) Jun, sum(case when t.mon = '07' then round(t.days/t.tot,0) else 0 end) Jul, sum(case when t.mon = '08' then round(t.days/t.tot,0) else 0 end) Aug, sum(case when t.mon = '09' then round(t.days/t.tot,0) else 0 end) Sep, sum(case when t.mon = '10' then round(t.days/t.tot,0) else 0 end) Oct, sum(case when t.mon = '11' then round(t.days/t.tot,0) else 0 end) Nov, sum(case when t.mon = '12' then round(t.days/t.tot,0) else 0 end) Dec, round(sum(t.days)/sum(t.tot),0) Ytd from (select mon, sum(diff) days,count(*) tot from (select case.case_num,case.x_voucher_iss_dt,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.diff from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.x_emerg_xfer_flg, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.X_XFER_APPROVAL_DT,round(sr.X_XFER_APPROVAL_DT-sr.created,0) diff, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='Y' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id not in ('Cancelled','Error') and resolution_cd='Approved' and X_XFER_APPROVAL_DT is not null )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.sub_status_cd='Rented' and case.x_voucher_category_cd not in ('City/State Conversion Program') and sr.diff>0 ) group by mon ) t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from ( select '# Average Days to Schedule Briefing ' type, sum(case when t.mon = '01' then round(t.days/t.tot,0) else 0 end) Jan, sum(case when t.mon = '02' then round(t.days/t.tot,0) else 0 end) Feb, sum(case when t.mon = '03' then round(t.days/t.tot,0) else 0 end) Mar, sum(case when t.mon = '04' then round(t.days/t.tot,0) else 0 end) Apr, sum(case when t.mon = '05' then round(t.days/t.tot,0) else 0 end) May, sum(case when t.mon = '06' then round(t.days/t.tot,0) else 0 end) Jun, sum(case when t.mon = '07' then round(t.days/t.tot,0) else 0 end) Jul, sum(case when t.mon = '08' then round(t.days/t.tot,0) else 0 end) Aug, sum(case when t.mon = '09' then round(t.days/t.tot,0) else 0 end) Sep, sum(case when t.mon = '10' then round(t.days/t.tot,0) else 0 end) Oct, sum(case when t.mon = '11' then round(t.days/t.tot,0) else 0 end) Nov, sum(case when t.mon = '12' then round(t.days/t.tot,0) else 0 end) Dec, round(sum(t.days)/sum(t.tot),0) Ytd from (select mon, sum(diff) days,count(*) tot from (select case.case_num,case.x_voucher_iss_dt,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.diff from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.x_emerg_xfer_flg, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,round(sr.sr_stat_dt-sr.X_XFER_APPROVAL_DT,0) diff, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='Y' and to_char(sr.created,'YYYY')= :p_year and sr.X_XFER_APPROVAL_DT is not null ) where rn=1 and sr_stat_id in ('Paused') and sr_sub_stat_id in ('Invited - Briefing','Approved for Briefing') )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.sub_status_cd='Rented' and case.x_voucher_category_cd not in ('City/State Conversion Program') ) group by mon ) t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# of Non-Emergency Requests ' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.type_cd,case.X_LEASE_END_DT,case.stage_cd,case.status_cd,case.sub_status_cd,to_char(sr.created,'MM') mon,sr.created, sr.sr_num,sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.x_emerg_xfer_flg from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_emerg_xfer_flg, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn From siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='N' and to_char(sr.created,'YYYY')= :p_year ) where rn=1)sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') )t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# of Valid Non-Emergency Transfer Requests' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_emerg_xfer_flg, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='N' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id<>'Cancelled')sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd <>'City/State Conversion Program' )t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# NYCHA Processing' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_emerg_xfer_flg, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='N' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('In Progress','Paused') and sr_sub_stat_id in ('Documents Received', 'Follow -up Required','Follow Up Required','Follow-up Required','Follow-up') )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd <>'City/State Conversion Program' )t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# Awaiting Information from Tenant' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_emerg_xfer_flg, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='N' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('Paused') and sr_sub_stat_id in ('Additional Info Required','Additional Information') )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd <>'City/State Conversion Program' )t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# Complete' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_emerg_xfer_flg, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='N' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id in ('Complete','Completed','Closed') )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd <>'City/State Conversion Program' )t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from ( select '# Average Days to Review' type, sum(case when t.mon = '01' then round(t.days/t.tot,0) else 0 end) Jan, sum(case when t.mon = '02' then round(t.days/t.tot,0) else 0 end) Feb, sum(case when t.mon = '03' then round(t.days/t.tot,0) else 0 end) Mar, sum(case when t.mon = '04' then round(t.days/t.tot,0) else 0 end) Apr, sum(case when t.mon = '05' then round(t.days/t.tot,0) else 0 end) May, sum(case when t.mon = '06' then round(t.days/t.tot,0) else 0 end) Jun, sum(case when t.mon = '07' then round(t.days/t.tot,0) else 0 end) Jul, sum(case when t.mon = '08' then round(t.days/t.tot,0) else 0 end) Aug, sum(case when t.mon = '09' then round(t.days/t.tot,0) else 0 end) Sep, sum(case when t.mon = '10' then round(t.days/t.tot,0) else 0 end) Oct, sum(case when t.mon = '11' then round(t.days/t.tot,0) else 0 end) Nov, sum(case when t.mon = '12' then round(t.days/t.tot,0) else 0 end) Dec, round(sum(t.days)/sum(t.tot),0) Ytd from (select mon, sum(diff) days,count(*) tot from (select case.case_num,case.x_voucher_iss_dt,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.diff from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.x_emerg_xfer_flg, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.X_XFER_APPROVAL_DT,round(sr.X_XFER_APPROVAL_DT-sr.created,0) diff, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='N' and to_char(sr.created,'YYYY')= :p_year ) where rn=1 and sr_stat_id not in ('Cancelled','Error') and resolution_cd='Approved' and X_XFER_APPROVAL_DT is not null )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.sub_status_cd='Rented' and case.x_voucher_category_cd not in ('City/State Conversion Program') and sr.diff>0 ) group by mon ) t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from ( select '# Average Days to Schedule Briefing ' type, sum(case when t.mon = '01' then round(t.days/t.tot,0) else 0 end) Jan, sum(case when t.mon = '02' then round(t.days/t.tot,0) else 0 end) Feb, sum(case when t.mon = '03' then round(t.days/t.tot,0) else 0 end) Mar, sum(case when t.mon = '04' then round(t.days/t.tot,0) else 0 end) Apr, sum(case when t.mon = '05' then round(t.days/t.tot,0) else 0 end) May, sum(case when t.mon = '06' then round(t.days/t.tot,0) else 0 end) Jun, sum(case when t.mon = '07' then round(t.days/t.tot,0) else 0 end) Jul, sum(case when t.mon = '08' then round(t.days/t.tot,0) else 0 end) Aug, sum(case when t.mon = '09' then round(t.days/t.tot,0) else 0 end) Sep, sum(case when t.mon = '10' then round(t.days/t.tot,0) else 0 end) Oct, sum(case when t.mon = '11' then round(t.days/t.tot,0) else 0 end) Nov, sum(case when t.mon = '12' then round(t.days/t.tot,0) else 0 end) Dec, round(sum(t.days)/sum(t.tot),0) Ytd from (select mon, sum(diff) days,count(*) tot from (select case.case_num,case.x_voucher_iss_dt,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.diff from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area,sr.x_emerg_xfer_flg, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,round(sr.sr_stat_dt-sr.X_XFER_APPROVAL_DT,0) diff, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Transfer' and nvl(sr.x_emerg_xfer_flg,'N')='N' and to_char(sr.created,'YYYY')= :p_year and sr.X_XFER_APPROVAL_DT is not null ) where rn=1 and sr_stat_id in ('Paused') and sr_sub_stat_id in ('Invited - Briefing','Approved for Briefing') )sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.sub_status_cd='Rented' and case.x_voucher_category_cd not in ('City/State Conversion Program') ) group by mon ) t )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# of Case Agreements Suspended> 180 days' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.status_cd,case.type_cd,case.stage_cd,case.sub_status_cd, case.x_voucher_category_cd,case.X_LEASE_END_DT,to_char(case.X_LEASE_END_DT,'MM') mon, agr.agree_num,agr.stat_cd,agr.agree_cd,agr.last_upd,round(sysdate-agr.last_upd,0) diff from siebel.s_case case , siebel.s_doc_agree agr where case.row_id=agr.x_case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') and agr.agree_cd in ('HAP','PHA Billing') and agr.stat_cd='Suspended' and sysdate-agr.last_upd>180 )t -- where t.mon between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon -- and to_char(t.created,'YYYY')= p_year )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# of Move-off for Long-term Suspensions' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.X_MOVE_OUT_REASON_CD, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Case Maintenance' and sr.sr_area ='Move-Out' and sr.X_MOVE_OUT_REASON_CD='Long-Term Suspension' and to_char(sr.created,'YYYY')= :p_year ) where rn=1)sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') )t -- where t.mon between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon -- and to_char(t.created,'YYYY')= p_year )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# of Terminations Created for Annual Inspections' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_term_reason_cd, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Case Maintenance' and sr.sr_area ='Termination' and sr.x_term_reason_cd='Annual Inspection' and to_char(sr.created,'YYYY')= :p_year ) where rn=1)sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') )t -- where t.mon between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon -- and to_char(t.created,'YYYY')= p_year )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# of Termination Created for Annual Recertifcations' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_term_reason_cd, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Case Maintenance' and sr.sr_area ='Termination' and sr.x_term_reason_cd='Annual Recertification' and to_char(sr.created,'YYYY')= :p_year ) where rn=1)sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') )t -- where t.mon between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon -- and to_char(t.created,'YYYY')= p_year )t1 union all select to_char(t1.type) type,to_char(t1.Jan) Jan, to_char(t1.Feb) Feb,to_char(t1.Mar) Mar,to_char(t1.Apr) Apr, to_char(t1.May) May,to_char(Jun) Jun, to_char(t1.Jul) Jul, to_char(t1.Aug) Aug,to_char(t1.Sep) Sep, to_char(t1.Oct) Oct, to_char(t1.Nov) Nov, to_char(t1.Dec) Dec, to_char(t1.YTD) YTD from (select '# of Termination Created for ALL OTHER REASONS' type, sum(case when t.mon= '01' then 1 else 0 end) Jan, sum(case when t.mon= '02' then 1 else 0 end) Feb, sum(case when t.mon= '03' then 1 else 0 end) Mar, sum(case when t.mon= '04' then 1 else 0 end) Apr, sum(case when t.mon= '05' then 1 else 0 end) May, sum(case when t.mon= '06' then 1 else 0 end) Jun, sum(case when t.mon= '07' then 1 else 0 end) Jul, sum(case when t.mon= '08' then 1 else 0 end) Aug, sum(case when t.mon= '09' then 1 else 0 end) Sep, sum(case when t.mon= '10' then 1 else 0 end) Oct, sum(case when t.mon= '11' then 1 else 0 end) Nov, sum(case when t.mon= '12' then 1 else 0 end) Dec, count(*) Ytd from (select case.case_num,case.X_LEASE_END_DT,to_char(sr.created,'MM') mon,sr.created, sr.ins_product,sr.sr_area,sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt from siebel.s_case case , (select * from(select sr.case_id,sr.sr_num,sr.created,sr.ins_product,sr.sr_area, sr.sr_stat_id,sr.sr_sub_stat_id,sr.sr_stat_dt,sr.resolution_cd,sr.x_term_reason_cd, row_number() OVER ( partition by sr.case_id order by case when sr.sr_stat_id!='Cancelled' then 1 end,sr.created desc) rn from siebel.s_srv_req sr where sr.ins_product='Case Maintenance' And Sr.Sr_Area ='Termination' And (Sr.X_Term_Reason_Cd Not In ('Annual Inspection','Annual Recertification') or Sr.X_Term_Reason_Cd is null) and to_char(sr.created,'YYYY')= :p_year ) where rn=1)sr where case.row_id=sr.case_id and case.type_cd='Section 8' and case.stage_cd='Tenancy' and case.status_cd='Active' and case.sub_status_cd='Rented' and case.x_Voucher_Category_cd not in ('City/State Conversion Program') )t -- where t.mon between to_char(trunc(add_months(to_date(p_mon,'MM'),0),'YY'),'MM') and p_mon -- and to_char(t.created,'YYYY')= p_year )t1