cre_views.sql

公务员管理系统

--创建案件不良信息视图
create or replace view v_bl_ajxx as 
select a.aj_id,a.aj_bh,a.qx,a.cbr,b.dwmc,decode(b.dwlb,'01',b.dwfrm,'03',b.swdjh,b.bzhm) zzjgdm,b.bzhm,b.dbr,b.dbrzw,b.zcdz
			,b.jydz,b.dblxdh,b.jyyzbm,b.dwxz,b.yyzzh,b.sbdjh
			,dp_sys.f_get_njhg_by_dwid(b.dwid) njqk,a.aj_lb,a.aj_lasj
			,b.bzhm zzmc,dp_sys.f_get_ssyj_by_aj_id(a.aj_id) ssyj ,decode(c.aj_id,'','02','01') flag,a.scajsj scajsj
	from aj_djb a,dw_xx b,bl_qyxx c
 where a.dwid = b.dwid and a.aj_id = c.aj_id(+) and a.aj_flag = '04'
/


--创建单位下检查次数视图
create or replace view v_dwjccs as
select dwid ,count(dwid) jccs
	from zf_jcxx 
 group by dwid
/
--创建单位下违法案卷数量视图
create or replace view v_dwajsl as
select dwid,count(aj_flag) ajsl 
	from aj_djb  
 where aj_flag = '04' 
 group by dwid
/ 

 --创建单位下从业人数视图
create or replace view v_dwcyrs as
select a.dwid,a.cyzs cyrs
	from zf_njxx a,(select max(year) year,dwid 
										from zf_njxx 
									 group by dwid) b 
 where a.dwid = b.dwid and a.year = b.year
/
--创建单位下各种检查途径数量视图
create or replace view v_dwtjcs as
select dwid,sum(decode(jctj,'01',1,null)) jbsl
			 ,sum(decode(jctj,'02',1,null)) zdxcsl,sum(decode(jctj,'03',1,null)) zxxcsl
			 ,sum(decode(jctj,'04',1,null)) njsl
	from zf_jcxx
 group by dwid
/
--创建单位下案卷处理情况视图
create or replace view v_dwclqksl as
select a.dwid,sum(decode(b.wdid,'004',1,null)) xzcl
			 ,sum(decode(b.wdid,'005',1,null)) zlgz,sum(decode(b.wdid,'006',1,null)) xzcf
			 ,sum(decode(b.wdid,'007',1,null)) qzzx
	from aj_djb a,aj_ml b
 where a.aj_id = b.aj_id and a.aj_flag = '04'
 group by dwid
/
--创建单位信息列表查询视图
create or replace view v_dw_xx as
select  a.dwid dwid,a.dwmc dwmc,a.dwlb dwlb,dp_sys.f_get_mc_by_dm('1001',a.dwlb) lbmc
			 ,a.qx qx,dp_sys.f_get_mc_by_dm('0001',a.qx) qxmc,a.jyjd jd,dp_sys.f_get_mc_by_dm('0002',a.jyjd) jdmc
			 ,a.dbr dbr,a.dwxz dwxz,dp_sys.f_get_mc_by_dm('1002',a.dwxz) dwxzmc,a.zczj zczj
			 ,b.cyrs cyrs,c.jccs jccs,d.ajsl ajsl,decode(d.ajsl,0,'02',null,'02','01') sfyaj
			 ,a.blrq djsj,dp_sys.to_chn_date(a.blrq,'ymd') djsjmc
			 ,e.jbsl,e.zdxcsl,e.zxxcsl,e.njsl,f.xzcl,f.zlgz,f.xzcf,f.qzzx
  from dw_xx a,v_dwcyrs b,v_dwjccs c,v_dwajsl d,v_dwtjcs e,v_dwclqksl f
 where a.dwid = b.dwid(+) and a.dwid = c.dwid(+) and a.dwid = d.dwid(+) and a.dwid = e.dwid(+) and a.dwid = f.dwid(+) and a.flag <> '03'
/
col lbmc format a30
col qxmc format a10
col jdmc format a20
col dwxzmc format a20
col djsjmc format a20

--创建执法检查信息视图
CREATE OR REPLACE VIEW V_JCXX AS
SELECT A.JCID,DP_SYS.F_GET_MC_BY_DM('2001',A.JCTJ) JCTJMC,A.JCTJ
,A.LDZRS,A.WDGRS,DP_SYS.TO_CHN_DATE(A.JCRQ,'YMD') JCRQMC,A.JCRQ
,dp_sys.F_Get_ryxm_by_ryid(A.CBR) CBRMC,A.CBR,DP_SYS.F_GET_MC_BY_DM('0001',A.QX) QXMC,B.QX QX,JYJD,A.QX CBQX
,B.DWMC,B.DWLB from ZF_JCXX A,DW_XX B where A.DWID=B.DWID
/
COL JCTJMC FORMAT A20
COL JCRQMC FORMAT A20
COL CBRMC FORMAT A20
COL QXMC FORMAT A30