a3.sql

本系统的使用可以将工作的部分流程使用计算机的办公自动化处理

/*******************************
--创建劳动保障公务效果统计表视图
*******************************/
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'
/