/*******************************
--創建勞動保障公務效果統計表視圖
*******************************/
create or replace view v_a3 as
select a.aj_id ajid,a.dwxz dwxz,b.ldzrs ldzrs,b.jctj jctj,c.JCSM_04 qttg,c.JCSM_06 jydjk
			,c.JCSM_05 qtwdg,c.JCSM_15 bqht,c.JCSM_23 fxdyjrs,c.JCSM_24 fxdyjje,a.dwid dwid
			,c.JCSM_19 jfgzrs,C.JCSM_20 JFGZJE,c.dc_flag dc_flag,c.dc_hs dc_hs,c.JCSM_02 ffjg
			,c.shy_rs shy_rs,c.shy_je shy_je,c.yx_rs yx_rs,c.yx_je yx_je,c.gs_rs gs_rs,c.gs_je gs_je,c.sy_rs sy_rs,c.sy_je sy_je,c.yl_rs yl_rs,c.yl_je yl_je
			,c.MSFFSD msffsd,c.JCSM_36 wfjn,b.qzjbtj qzjbtj,a.qx qx,decode(a.blfs,'J',a.aj_djsj,'B',a.aj_lasj) sj,a.aj_flag aj_flag
	from aj_djb a,zf_jcxx b,aj_jcxg c
 where a.jcid = b.jcid(+) and a.aj_id = c.aj_id(+) and a.aj_flag in ('04','03')
/
--勞動保障公務效果統計表第一行合計
select 1 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(qttg) col3,sum(jydjk) col4,sum(qtwdg) col5
			,sum(bqht) col6,sum(fxdyjrs) col7,sum(fxdyjje) col8,sum(jfgzrs) col9,sum(jfgzje) col10
			,sum(decode(dc_flag,'01',dc_hs,0)) col11,sum(decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col12
			,sum(decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0)) col13,sum(decode(dc_flag,'02',dc_hs,0)) col14
			,sum(decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col15,sum(ffjg) col16
			,sum(msffsd) col17,sum(wfjn) col18,sum(decode(qzjbtj,'04',1,0)) col19,sum(decode(qzjbtj,'04',ldzrs,0)) col20
	from v_a3
 where 1=1
/

--勞動保障公務效果統計表第2行分類統計
select 2 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(qttg) col3,sum(jydjk) col4,sum(qtwdg) col5
			,sum(bqht) col6,sum(fxdyjrs) col7,sum(fxdyjje) col8,sum(jfgzrs) col9,sum(jfgzje) col10
			,sum(decode(dc_flag,'01',dc_hs,0)) col11,sum(decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col12
			,sum(decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0)) col13,sum(decode(dc_flag,'02',dc_hs,0)) col14
			,sum(decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col15,sum(ffjg) col16
			,sum(msffsd) col17,sum(wfjn) col18,sum(decode(qzjbtj,'04',1,0)) col19,sum(decode(qzjbtj,'04',ldzrs,0)) col20
	from v_a3
 where 1=1 and dwxz = '01'
/
--勞動保障公務效果統計表第3行分類統計
select 3 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(qttg) col3,sum(jydjk) col4,sum(qtwdg) col5
			,sum(bqht) col6,sum(fxdyjrs) col7,sum(fxdyjje) col8,sum(jfgzrs) col9,sum(jfgzje) col10
			,sum(decode(dc_flag,'01',dc_hs,0)) col11,sum(decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col12
			,sum(decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0)) col13,sum(decode(dc_flag,'02',dc_hs,0)) col14
			,sum(decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col15,sum(ffjg) col16
			,sum(msffsd) col17,sum(wfjn) col18,sum(decode(qzjbtj,'04',1,0)) col19,sum(decode(qzjbtj,'04',ldzrs,0)) col20
	from v_a3
 where 1=1 and dwxz = '02'
/
--勞動保障公務效果統計表第4行分類統計
select 4 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(qttg) col3,sum(jydjk) col4,sum(qtwdg) col5
			,sum(bqht) col6,sum(fxdyjrs) col7,sum(fxdyjje) col8,sum(jfgzrs) col9,sum(jfgzje) col10
			,sum(decode(dc_flag,'01',dc_hs,0)) col11,sum(decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col12
			,sum(decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0)) col13,sum(decode(dc_flag,'02',dc_hs,0)) col14
			,sum(decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col15,sum(ffjg) col16
			,sum(msffsd) col17,sum(wfjn) col18,sum(decode(qzjbtj,'04',1,0)) col19,sum(decode(qzjbtj,'04',ldzrs,0)) col20
	from v_a3
 where 1=1 and dwxz = '03'
/
--勞動保障公務效果統計表第5行分類統計
select 5 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(qttg) col3,sum(jydjk) col4,sum(qtwdg) col5
			,sum(bqht) col6,sum(fxdyjrs) col7,sum(fxdyjje) col8,sum(jfgzrs) col9,sum(jfgzje) col10
			,sum(decode(dc_flag,'01',dc_hs,0)) col11,sum(decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col12
			,sum(decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0)) col13,sum(decode(dc_flag,'02',dc_hs,0)) col14
			,sum(decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col15,sum(ffjg) col16
			,sum(msffsd) col17,sum(wfjn) col18,sum(decode(qzjbtj,'04',1,0)) col19,sum(decode(qzjbtj,'04',ldzrs,0)) col20
	from v_a3
 where 1=1 and dwxz = '04'
/
--勞動保障公務效果統計表第6行分類統計
select 6 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(qttg) col3,sum(jydjk) col4,sum(qtwdg) col5
			,sum(bqht) col6,sum(fxdyjrs) col7,sum(fxdyjje) col8,sum(jfgzrs) col9,sum(jfgzje) col10
			,sum(decode(dc_flag,'01',dc_hs,0)) col11,sum(decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col12
			,sum(decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0)) col13,sum(decode(dc_flag,'02',dc_hs,0)) col14
			,sum(decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col15,sum(ffjg) col16
			,sum(msffsd) col17,sum(wfjn) col18,sum(decode(qzjbtj,'04',1,0)) col19,sum(decode(qzjbtj,'04',ldzrs,0)) col20
	from v_a3
 where 1=1 and dwxz = '05'
/
--勞動保障公務效果統計表第7行分類統計
select 7 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(qttg) col3,sum(jydjk) col4,sum(qtwdg) col5
			,sum(bqht) col6,sum(fxdyjrs) col7,sum(fxdyjje) col8,sum(jfgzrs) col9,sum(jfgzje) col10
			,sum(decode(dc_flag,'01',dc_hs,0)) col11,sum(decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col12
			,sum(decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0)) col13,sum(decode(dc_flag,'02',dc_hs,0)) col14
			,sum(decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col15,sum(ffjg) col16
			,sum(msffsd) col17,sum(wfjn) col18,sum(decode(qzjbtj,'04',1,0)) col19,sum(decode(qzjbtj,'04',ldzrs,0)) col20
	from v_a3
 where 1=1 and dwxz = '06'
/
--勞動保障公務效果統計表第8行分類統計
select 8 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(qttg) col3,sum(jydjk) col4,sum(qtwdg) col5
			,sum(bqht) col6,sum(fxdyjrs) col7,sum(fxdyjje) col8,sum(jfgzrs) col9,sum(jfgzje) col10
			,sum(decode(dc_flag,'01',dc_hs,0)) col11,sum(decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col12
			,sum(decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0)) col13,sum(decode(dc_flag,'02',dc_hs,0)) col14
			,sum(decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col15,sum(ffjg) col16
			,sum(msffsd) col17,sum(wfjn) col18,sum(decode(qzjbtj,'04',1,0)) col19,sum(decode(qzjbtj,'04',ldzrs,0)) col20
	from v_a3
 where 1=1 and dwxz = '07'
/
--勞動保障公務效果統計表第9行分類統計
select 9 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(qttg) col3,sum(jydjk) col4,sum(qtwdg) col5
			,sum(bqht) col6,sum(fxdyjrs) col7,sum(fxdyjje) col8,sum(jfgzrs) col9,sum(jfgzje) col10
			,sum(decode(dc_flag,'01',dc_hs,0)) col11,sum(decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col12
			,sum(decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0)) col13,sum(decode(dc_flag,'02',dc_hs,0)) col14
			,sum(decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) col15,sum(ffjg) col16
			,sum(msffsd) col17,sum(wfjn) col18,sum(decode(qzjbtj,'04',1,0)) col19,sum(decode(qzjbtj,'04',ldzrs,0)) col20
	from v_a3
 where 1=1 and dwxz = '08'
/