modify_cre_package_sys_jc.sql

公务员管理系统

	
/******************************
从dm_xx表中取出相应的汉字名称
******************************/
  FUNCTION F_GET_DWMC_BY_DWID(v_dwid  VARCHAR2) RETURN  VARCHAR2 IS
    v_dwmc  dw_xx.dwmc%TYPE;
  BEGIN
    select dwmc into v_dwmc
      from dw_xx
     where dwid = v_dwid;
    RETURN v_dwmc ;
  EXCEPTION
    WHEN others THEN
    RETURN null;
  END F_GET_DWMC_BY_DWID;
/******************************
从jcy_ry表中取出相应的汉字名称
******************************/
  FUNCTION F_GET_RYXM_BY_RYID(v_ryid  VARCHAR2) RETURN  VARCHAR2 IS
    v_ryxm  jcy_ry.username%TYPE;
  BEGIN
    select username into v_ryxm
      from jcy_ry
     where userid = v_ryid;
    RETURN v_ryxm ;
  EXCEPTION
    WHEN others THEN
    RETURN null;
  END F_GET_RYXM_BY_RYID;
/******************************
从jcy_ry表中取出相应的汉字名称
******************************/
  FUNCTION F_GET_RYXM_BY_RYCODE(v_ryid  VARCHAR2) RETURN  VARCHAR2 IS
    v_ryxm  jcy_ry.username%TYPE;
  BEGIN
    select username into v_ryxm
      from jcy_ry
     where usercode = v_ryid;
    RETURN v_ryxm ;
  EXCEPTION
    WHEN others THEN
    RETURN null;
  END F_GET_RYXM_BY_RYCODE;
/******************************
从年检信息表中取出最近的是否合格代码
******************************/
  FUNCTION F_GET_NJHG_BY_DWID(v_dwid  VARCHAR2) RETURN  VARCHAR2 IS
    v_sfhg  zf_njxx.sfhg%TYPE;
  BEGIN
		select sfhg into v_sfhg
		 	from zf_njxx 
		 where dwid = v_dwid 
		 	 and blrq = (select max(blrq) from zf_njxx where dwid = v_dwid);
    RETURN v_sfhg ;
  EXCEPTION
    WHEN others THEN
    RETURN null;
  END F_GET_NJHG_BY_DWID;
/******************************
从连审批表中取出非法事实依据
******************************/
  FUNCTION F_GET_SSYJ_BY_AJ_ID(v_ajid  VARCHAR2) RETURN  VARCHAR2 IS
    v_ssyj  aj_lasp.ssyj%TYPE;
  BEGIN
		select ssyj into v_ssyj
		 	from aj_lasp 
		 where aj_id = v_ajid ;
    RETURN v_ssyj;
  EXCEPTION
    WHEN others THEN
    RETURN null;
  END F_GET_SSYJ_BY_AJ_ID;
/******************************
从通过案件ID得到单位名称
******************************/
  FUNCTION F_GET_DWMC_BY_AJ_ID(v_ajid  VARCHAR2) RETURN  VARCHAR2 IS
    v_dwmc  dw_xx.dwmc%TYPE;
  BEGIN
		select a.dwmc into v_dwmc
		 	from dw_xx a,aj_djb b 
		 where b.aj_id = v_ajid and a.dwid = b.dwid;
    RETURN v_dwmc;
  EXCEPTION
    WHEN others THEN
    RETURN null;
  END F_GET_DWMC_BY_AJ_ID;

/******************************
检测是否可以上报不良信息
******************************/
	FUNCTION F_BL_CHECK_SB(v_ajid  VARCHAR2) RETURN  VARCHAR2 IS
  	v_qx			  VARCHAR2(3);
  	n						NUMBER;
  	v_out  			VARCHAR2(2);
  BEGIN
  	select count(1) into n from dual
  	 where exists(select 1 from bl_qyxx where aj_id = v_ajid);
		IF n = 0 THEN
			v_out := '02';
		ELSE
			select qx into v_qx from bl_qyxx where aj_id = v_ajid;
			IF v_qx = '001' THEN
			  select count(1) into n from dual
			   where exists(select 1 from bl_ysdj,bl_yssd 
			                 where bl_ysdj.lrr is not null
			                   and bl_yssd.lrr is not null
			                   and bl_ysdj.aj_id = bl_yssd.aj_id
			                   and bl_ysdj.aj_id = v_ajid);
			  IF n = 1 THEN
			    v_out := '01';
			  ELSE
			    v_out := '02';
			  END IF;
			ELSE
			  select count(1) into n from dual
			   where exists(select 1 from bl_wfbs,bl_wfdj,bl_ysdj,bl_yssd
                       where bl_wfbs.aj_id = bl_wfdj.aj_id  
                         and bl_wfdj.aj_id = bl_ysdj.aj_id  
                         and bl_ysdj.aj_id = bl_yssd.aj_id 
                         and bl_wfbs.aj_id = v_ajid
                         and bl_wfbs.lrr is not null 
                         and bl_wfdj.lrr is not null 
                         and bl_ysdj.lrr is not null 
                         and bl_yssd.lrr is not null);
			  IF n = 1 THEN
			    v_out := '01';
			  ELSE
			    v_out := '02';
			  END IF;
			END IF;
		END IF;
		RETURN v_out;
  EXCEPTION
    WHEN others THEN
    RETURN '02';
	END F_BL_CHECK_SB;	
/******************************
从dm_xx表中取出相应的单位性质
******************************/
  FUNCTION F_GET_DWXZ_BY_DWID(v_dwid  VARCHAR2) RETURN  VARCHAR2 IS
    v_dwxz  dw_xx.dwxz%TYPE;
  BEGIN
    select dwxz into v_dwxz
      from dw_xx
     where dwid = v_dwid;
    RETURN v_dwxz ;
  EXCEPTION
    WHEN others THEN
    RETURN null;
  END F_GET_DWXZ_BY_DWID;
  
/******************************
从tj_bbxx表中取出相应的报表名称
******************************/
  FUNCTION F_GET_BBMC_BY_BBDM(v_bbdm  VARCHAR2) RETURN  VARCHAR2 IS
    v_bbmc  tj_bbxx.bbmc%TYPE;
  BEGIN
    select bbmc into v_bbmc
      from tj_bbxx
     where bbdm = v_bbdm;
    RETURN v_bbmc;
  EXCEPTION
    WHEN others THEN
    RETURN '';
  END F_GET_BBMC_BY_BBDM;


/******************************
从jcr_ry表中取出相应的组别代码
******************************/
  FUNCTION F_GET_ZBDM_BY_CBR(v_cbr  VARCHAR2) RETURN  VARCHAR2 IS
    v_zbdm  jcy_zb.zbdm%TYPE;
  BEGIN
    select zbdm into v_zbdm
      from jcy_ry
     where userid = v_cbr;
    RETURN v_zbdm;
  EXCEPTION
    WHEN others THEN
    RETURN '';
  END F_GET_ZBDM_BY_CBR;

/******************************
从jcr_zb表中取出相应的组别名称
******************************/
  FUNCTION F_GET_ZBMC_BY_ZBDM(v_zbdm  VARCHAR2) RETURN  VARCHAR2 IS
    v_zbmc  jcy_zb.zbmc%TYPE;
  BEGIN
    select zbmc into v_zbmc
      from jcy_zb
     where zbdm = v_zbdm;
    RETURN v_zbmc;
  EXCEPTION
    WHEN others THEN
    RETURN '';
  END F_GET_ZBMC_BY_ZBDM;




/*********************************
自动生成序号，按区县每年重新从0001开始排序
*********************************/
 FUNCTION F_GET_AJXX_BY_AJBH(v_qx VARCHAR2,v_year VARCHAR2,v_db VARCHAR2) RETURN VARCHAR2 IS
   	v_add varchar2(4) :='0';
   	n_num number(4);
    v_h   VARCHAR2(10);
  BEGIN
      select count(1) into n_num from (select h from aj_qzzx where n=v_year and qxid=v_qx
                                      union
                                          select h from aj_xzcf where n=v_year and qxid=v_qx
                                              union
                                                  select h from aj_xzcl where n=v_year and qxid=v_qx
                                                      union
                                                          select h from aj_zlgz where n=v_year and qxid=v_qx);
      IF n_num>0 THEN
  	     select max(h) into v_h from (select h from aj_qzzx where n=v_year and qxid=v_qx
                                      union
                                          select h from aj_xzcf where n=v_year and qxid=v_qx
                                              union
                                                  select h from aj_xzcl where n=v_year and qxid=v_qx
                                                      union
                                                          select h from aj_zlgz where n=v_year and qxid=v_qx);
  	   v_add := lpad(to_char(to_number(v_h)+1),4,'0');
  	   ELSE
  	   v_add :='0001';
  	   END IF;
	    return v_add;
      EXCEPTION
	    WHEN OTHERS THEN
		  RETURN NULL;
      END F_GET_AJXX_BY_AJBH;
 END;
/
show errors