cre_tjbb_view.sql

公务员管理系统

/*******************************
--创建劳动保障公务案件统计表视图16a1
*******************************/
create or replace view v_a1 as
select a.aj_id ajid,a.aj_flag aj_flag,a.dwxz dwxz,a.aj_lb ajlb,a.fkje fkje,a.qx qx,a.scajsj sj
			,(select sum(decode(d.mbid,'ZLGZ_HT',1,'ZLGZ_SX',1,'ZLGZ_GZ',1,'ZLGZ_QT',1,0)) from aj_ml d where a.aj_id = d.aj_id) zlgzjs
			,(select sum(decode(d.mbid,'XZCL_HT',1,'XZCL_SX',1,'XZCL_GZ',1,'XZCL_QT',1,0)) from aj_ml d where a.aj_id = d.aj_id) xzcljs
			,(select sum(decode(d.mbid,'XZCF_JG',1,'XZCF_MS',1,'XZCF_ZK',1,'XZCF_QT',1,'XZCF_FK_GZ',1,'XZCF_FK_HT',1,'XZCF_FK_QT',1,'XZCF_FK_SB',1,'XZCF_FK_TG',1,'XZCF_FK_WL',1,0)) from aj_ml d where a.aj_id = d.aj_id) xzcfjs
			,(select sum(decode(d.mbid,'XZCF_JG',1,0)) from aj_ml d where a.aj_id = d.aj_id) jgjs
			,(select sum(decode(d.mbid,'XZCF_FK_GZ',1,'XZCF_FK_HT',1,'XZCF_FK_QT',1,'XZCF_FK_SB',1,'XZCF_FK_TG',1,'XZCF_FK_WL',1,0)) from aj_ml d where a.aj_id = d.aj_id) fkjs
			,(select sum(decode(d.mbid,'XZCF_MS',1,'XZCF_ZK',1,'XZCF_QT',1,0)) from aj_ml d where a.aj_id = d.aj_id) qtjs
			,(select sum(decode(d.mbid,'QZZX_HT',1,'QZZX_SX',1,'QZZX_GZ',1,'QZZX_YG',1,'QZZX_QT',1,0)) from aj_ml d where a.aj_id = d.aj_id) qzzxjs
	from aj_djb a
 where a.aj_flag in ('03','04')
/
/*******************************
--创建劳动保障公务专项工作统计表a2
*******************************/
create or replace view v_a2_1 as
select a.jcid jcid,a.dwid dwid,c.dwxz dwxz,a.jctj jctj,a.ldzrs ldzrs,decode(b.aj_id,null,'0',b.aj_id) ajid
			,b.aj_flag aj_flag,d.JCSM_28 jzcs,b.qx qx,b.aj_lasj aj_lasj,b.fkje fkje,a.jcrq sj,d.jcsm_05 qtwlrs
	from zf_jcxx a,aj_djb b,dw_xx c,aj_jcxg d
 where a.jcid = b.jcid(+) and a.dwid = c.dwid(+) and b.aj_id = d.aj_id(+)
/
create or replace view v_a2_2 as
select a.njid njid,a.sfhg sfhg,a.blrq sj,b.dwxz dwxz,b.qx qx,b.dwid
	from zf_njxx a ,dw_xx b
 where a.dwid = b.dwid(+)
/
/*******************************
--创建劳动保障公务效果统计表视图
*******************************/
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')
/
/*******************************
--创建劳动保障公务组织统计表视图19a4
*******************************/
create or replace view v_a4_1 as
select ssbz,zjlb,sbsj sj,qx,sfjd
  from jcy_ry
/

create or replace view v_a4_2 as
select pxrs,pxqr sj,pxbm,pxbm qx
	from jcy_pxb
/
/*******************************
创建劳动保障公务效果及专项工作统计表
*******************************/
create or replace view v_b2_1 as
select a.jcid,a.jctj,a.QZJBTJ,a.jcrq sj,a.ldzrs,a.qx,b.dwxz,a.dwid
	from zf_jcxx a,dw_xx b
 where a.dwid = b.dwid(+)
/
create or replace view v_b2_2 as
select a.aj_id ajid,a.dwxz dwxz,b.ldzrs ldzrs,b.jctj jctj,c.JCSM_04 qttg,c.JCSM_06 jydjk,a.dwid dwid
			,c.JCSM_05 qtwdg,c.JCSM_15 bqht,c.JCSM_23 fxdyjrs,c.JCSM_24 fxdyjje,c.jcsm_19 zfgzrs,c.jcsm_20 zfgzje
			,c.JCSM_19 jfgzrs,C.JCSM_20 JFGZJE,c.dc_flag dc_flag,c.dc_hs dc_hs,c.JCSM_02 ffjg,c.jcsm_09 zyzg,c.jcsm_10 zypx
			,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,a.aj_jcqk aj_jcqk
	from aj_djb a,zf_jcxx b,aj_jcxg c
 where a.jcid = b.jcid(+) and a.aj_id = c.aj_id(+)
/
/*******************************
--创建对用人单位专项检查情况统计表视图
*******************************/
create or replace view v_c1 as
select a.jcid jcid,a.dwid dwid,dp_sys.f_get_dwxz_by_dwid(a.dwid) dwxz,trunc(a.jcrq,'dd') sj,a.ldzrs ldzrs,a.qx qx
			,c.aj_flag aj_flag,c.aj_id ajid,c.fkje fkje
			,(select sum(decode(e.mbid,'ZLGZ_HT',1,'ZLGZ_SX',1,'ZLGZ_GZ',1,'ZLGZ_QT',1,0)) from aj_ml e where c.aj_id = e.aj_id) zlgzjs
			,(select sum(decode(e.mbid,'XZCL_HT',1,'XZCL_SX',1,'XZCL_GZ',1,'XZCL_QT',1,0)) from aj_ml e where c.aj_id = e.aj_id) xzcljs
			,(select sum(decode(e.mbid,'XZCF_JG',1,'XZCF_MS',1,'XZCF_ZK',1,'XZCF_QT',1,'XZCF_FK_GZ',1,'XZCF_FK_HT',1,'XZCF_FK_QT',1,'XZCF_FK_SB',1,'XZCF_FK_TG',1,'XZCF_FK_WL',1,0)) from aj_ml e where c.aj_id = e.aj_id) xzcfjs
			,(select sum(decode(e.mbid,'QZZX_HT',1,'QZZX_SX',1,'QZZX_GZ',1,'QZZX_YG',1,'QZZX_QT',1,0)) from aj_ml e where c.aj_id = e.aj_id) qzzxjs
			,d.shbxzjxs ctfs,d.yl_je ylje,d.sy_je syje,d.yx_je yxje,d.gs_je gsje
			,d.yl_rs ylrs,d.sy_rs syrs,d.yx_rs yxrs,d.gs_rs gsrs,a.zxjc zxjc
	from zf_jcxx a,aj_djb c,aj_jcxg d
 where a.jcid = c.jcid(+)  and c.aj_id = d.aj_id(+) and a.jctj = '03'
/
/*******************************
--创建对用人单位专项检查情况统计表视图
*******************************/
create or replace view v_c2 as
select a.aj_id aj_id,a.qx qx,a.dwid dwid,dp_sys.f_get_dwmc_by_dwid(a.dwid) dwmc
      ,a.cbr cbr,dp_sys.f_get_ryxm_by_ryid(a.cbr) rymc
			,decode(dp_sys.f_get_zbdm_by_cbr(a.cbr),null,'99999999',dp_sys.f_get_zbdm_by_cbr(a.cbr)) zbdm,decode(dp_sys.f_get_zbmc_by_zbdm(dp_sys.f_get_zbdm_by_cbr(a.cbr)),null,'无组别人员',dp_sys.f_get_zbmc_by_zbdm(dp_sys.f_get_zbdm_by_cbr(a.cbr))) zbmc
			,a.aj_djsj sj,a.scajsj sbsj ,a.aj_flag aj_flag,b.jcid jcid
	from aj_djb a,zf_jcxx b
 where a.jcid = b.jcid(+) and b.jctj = '01'
/