CREATE OR REPLACE TRIGGER SD_HOSPITAL.TR_YS_BRZDXX_LIMIT
 BEFORE 
 INSERT OR UPDATE
 ON SD_MZ.YS_BRZDXX
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
declare
  -- local variables here
  VS_PROCESS XT_XTRZ00.JCID00%Type;
  VS_YGBH00  XT_XTRZ00.YGBH00%Type;
  VS_XTDM00  XT_XTRZ00.XTDM00%type;
  VS_CZNR00  XT_XTCZRZ.CZNR00%Type;
  VS_BMMC00  VARCHAR2(100);
  YS_SRZDYZD XT_XTCS00.value0 %type;
  v_count    number(5);
  v_count1    number(5);
  Ecustom   EXCEPTION;

-- MODIFICATION HISTORY
-- Person      Date        Comments
-- csf         2011.01.29     create 参数YS_SRZDYZD为N,医生系统保存的诊断一定要有icd码。
-- csf         2011.03.02     修改也要限制
-- zhanghr     2011.04.02     考虑到可能使用别名，所以去掉and trim(JBMC00)=trim(:new.ZDMC00)
-- CSF         2011.04.06     判断诊断是否是自定义输入还是选择输入不仅要判断诊断码还要判断名称，但改从视图
-- daihq       2014.05.30     修改前后就诊医生不同，则写入日志表XT_XTCZRZ for MZYS-20140416-003
-- dsm         2014.06.30     YS_SRZDYZD=N时如果医生8Z01有权限则可以录入自定义诊断 for MZYS-20140619-001
-- dsm         2014.08.07     YS_SRZDYZD=K时如果医生8Z01有权限则可以录入西医自定义诊断 for MZYS-20140802-001   
-- dsm         2015.12.07     扩展YS_SRZDYZD=N时,中西医都不能录入自定义诊断 for MZYS-20151204-001
-- dsm         2016.08.04     诊断匹配时加trim 归档 for   MZYS-20160804-001
-- dsm         2016.10.20     增加项目,带有医生的挂号诊断时保存会报错,诊断是由挂号带出的上次诊断带有空格引起的 for YJ-20161014-001
Begin
    if inserting OR updating then  --for YJ-20161014-001
      :new.ICD900:=trim(:new.ICD900);
    end if;  
    Select userenv('SESSIONID') PROCESS Into VS_PROCESS from dual where rownum=1;
    Select nvl(trim(max(XTDM00)),'0') Into VS_XTDM00 From XT_XTRZ00 Where JCID00=VS_PROCESS And
         LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=VS_PROCESS);
    if VS_XTDM00='8' then
      select nvl(trim(max(VALUE0)),'N') into YS_SRZDYZD from XT_XTCS00 where NAME00='YS_SRZDYZD' ;
      select count(*) into v_count  from XT_XTMK00 where XTDM00='8' and MKDM00='8Z01' and ( 
                (BZ0000<>'0' and MKDM00 in (select MKDM00 from XT_ZBQX00  where XTDM00='8' and ZBDM00 in ( select ZBDM00 from  VW_XT_YHZB00 where YGBH00=:new.jzys00))));
          
      if (YS_SRZDYZD='N' or YS_SRZDYZD='K'  ) and v_count<2 then
        if (:new.ICD900 is null) and (:new.SFXYZD='1' or   YS_SRZDYZD='N')  then
           raise Ecustom;
        elsif (:new.SFXYZD='1' or YS_SRZDYZD='N' ) then
          if :new.SFXYZD='1' then
            select count(*) into v_count from vw_ys_jbzdzd  where trim(ICD900)=trim(:new.ICD900)  and trim(JBMC00)=trim(:new.ZDMC00);
             if :new.GJBZBM is NOT NULL THEN  
              select count(*) into v_count1 from vw_ys_jbzdzd where trim(DYBZBM)=trim(:new.GJBZBM)   ;
               END IF;
          else
            if :new.ZZDBZ0='1' then
              select count(*) into v_count from vw_bm_zyjbzd where trim(JBBH00)=trim(:new.ICD900)  ;   
              if :new.GJBZBM is NOT NULL THEN 
              select count(*) into v_count1 from vw_bm_zyjbzd where trim(DYBZBM)=trim(:new.GJBZBM)  ;
              END IF ;
            else  
              select count(*) into v_count from vw_bm_zyzhzd  where trim(ZHBH00)=trim(:new.ICD900)  ;   
               if :new.GJBZBM is NOT NULL THEN 
               select count(*) into v_count1 from vw_bm_zyzhzd  where trim(DYBZBM)=trim(:new.GJBZBM) ;
               END IF;
            end if;  
            
          end if;       
          if v_count=0 OR v_count1=0 then
            raise Ecustom;
          end if;
        end if;
      end if;
    end if;
    if updating then
      if :old.JZYS00<>:new.JZYS00 then
        VS_CZNR00 := VS_CZNR00||'诊断流水号:'||trim(to_char(:new.ZDXXID))||';';
        VS_CZNR00 := VS_CZNR00||'挂号号:'||trim((:new.GHH000))||';';
        VS_CZNR00 := VS_CZNR00||'原诊断标志:'||trim((:old.ZZDBZ0))||';';
        VS_CZNR00 := VS_CZNR00||'原icd码:'||trim((:old.ICD900))||';';
        VS_CZNR00 := VS_CZNR00||'原诊断名称:'||trim((:old.ZDMC00))||';';
        VS_CZNR00 := VS_CZNR00||'原医生:'||trim((:old.JZYSXM))||'('||trim((:old.JZYS00))||');';
        VS_CZNR00 := VS_CZNR00||'原时间:'||trim((:old.ZDRQ00))||trim((:old.ZDSJ00))||';';
        VS_CZNR00 := VS_CZNR00||'现诊断标志:'||trim((:new.ZZDBZ0))||';';
        VS_CZNR00 := VS_CZNR00||'现icd码:'||trim((:new.ICD900))||';';
        VS_CZNR00 := VS_CZNR00||'现诊断名称:'||trim((:new.ZDMC00))||';';
        VS_CZNR00 := VS_CZNR00||'修改医生:'||trim((:new.JZYSXM))||'('||trim((:new.JZYS00))||');';
        VS_CZNR00 := VS_CZNR00||'修改时间:'||trim((:new.ZDRQ00))||trim((:new.ZDSJ00))||';';
        insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID)
            values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'YS_BRZDXX',1,:new.JZYS00,VS_CZNR00,:NEW.GHID00);
      end if;
    end if;
exception
  when  Ecustom then
    raise_application_error(-20010,substr('不允许手动输入诊断,请选择诊断！*',1,220));
End;
/