CREATE TRIGGER SD_HOSPITAL.TR_YJ_YW0000_UPD_PACS
 BEFORE 
 INSERT OR DELETE OR UPDATE
 ON SD_YJ.YJ_YW0000
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
declare ErrStr   varchar2(200);
        ECUSTOM  EXCEPTION;
        VCOUNTER NUMBER(5);
        VJCLBID  bm_zlzd00.JCLBID%type; --20060427
        VICKH00  ic_yhxx00.ICKH00%type;--定义为与此字段同类型，可变化
        VJCLBMC  varchar2(8);
Begin
  IF INSERTING THEN
    IF :NEW.ZLXMID>0 THEN
      ErrStr:='err1: 诊疗项目不存在ZLXMID='||to_char(:new.zlxmid);
      SELECT JCLBID INTO VJCLBID FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID;
    END IF;
    --检查类别处理
    if VJCLBID is not null then
     ErrStr:='err2: 检查类别不存在JCLBID='||VJCLBID;
     select JCLBMC into VJCLBMC from bm_jclb00 where JCLBID=VJCLBID;
     begin
        select ickh00 into VICKH00 from ic_yhxx00 where brid00=:new.brid00 and ZT0000='1';
        EXCEPTION
          when others  then
          VICKH00:='';
     end;
     --主表
     insert into spacs.EXAM_APPLY
            (APPLY_NO,SICK_ID,IC_CARD,NAME,SEX,BIRTH_DATE,CHARGE_TYPE,
             MAILING_ADDRESS,ZIP_CODE,PHONE_NUMBER,OUTPATIENT_NO,INPATIENT_NO,
             BED_NO,PATIENT_SOURCE,PRIORITY_ORDER,EXAM_CLASS,REQ_DEPT,REQ_PHYSICIAN,
             REQ_DATE,REQ_TIME,REQ_MEMO,CLIN_DIAG,PERFORM_DEPT,CHARGE_FLAG,visit_id,mi_card)
            select :new.yjdjh0,brid00,trim(VICKH00),BRXM00,BRXB00,to_char(to_date(BRCSRQ,'yyyymmdd'),'yyyy-mm-dd'),FBMC00,
             substr(BRJTDZ,1,60),BRYB00,BRDH00,decode(:new.MZZYBZ,'0',trim(:NEW.ZYHGHH),''),decode(:new.MZZYBZ,1,trim(:NEW.ZYHGHH),''),
             :new.CH0000,decode(:new.MZZYBZ,0,'门诊',1,'住院','外来'),decode(:new.JZBZ00,'Y','急诊','普通'),VJCLBMC,:new.kdksbh,:new.KDYSXM,
             to_char(to_date(:new.kdrq00,'yyyymmdd'),'yyyy-mm-dd'),:new.kdsj00,:new.ZLXMJC,substr(:NEW.ZDMC00,1,100),:new.YJKSBH,decode(:NEW.SFDJH0,0,'0','1'),:NEW.zyghid,decode(trim(ybkh00),'0','',trim(ybkh00)) from VW_BM_BRXXB1 where brid00=:new.brid00;
     end if;

      --edit by lingh    201001017  
     update spacs.exam_apply_item set spacs.exam_apply_item.amount= :new.Sl0000
            where spacs.exam_apply_item.apply_no=:new.yjdjh0;
     update spacs.exam_apply_item set cost=(COST*amount)
            where spacs.exam_apply_item.apply_no=:new.yjdjh0;
     update spacs.exam_apply_item set charge=(charge*amount)
            where spacs.exam_apply_item.apply_no=:new.yjdjh0;
      --201001017  

  ELSIF UPDATING THEN  --更改
    IF :NEW.ZLXMID>0 THEN
      ErrStr:='err1: 诊疗项目不存在ZLXMID='||to_char(:new.zlxmid);
      SELECT JCLBID INTO VJCLBID
      FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID;
    END IF;
    --检查项目变动时
    if  (VJCLBID is not null) and (:NEW.ZLXMID<>:OLD.ZLXMID) then
       ErrStr:='err2: 检查类别不存在JCLBID='||VJCLBID;
       select JCLBMC into VJCLBMC from bm_jclb00 where JCLBID=VJCLBID;
       begin
        select ickh00 into VICKH00 from ic_yhxx00 where brid00=:new.brid00 and ZT0000='1';
         EXCEPTION
          when others  then
          VICKH00:='';
       end;
       delete from spacs.EXAM_APPLY where  APPLY_NO=:old.yjdjh0;
       --主表
        insert into spacs.EXAM_APPLY
            (APPLY_NO,SICK_ID,IC_CARD,NAME,SEX,BIRTH_DATE,CHARGE_TYPE,
             MAILING_ADDRESS,ZIP_CODE,PHONE_NUMBER,OUTPATIENT_NO,INPATIENT_NO,
             BED_NO,PATIENT_SOURCE,PRIORITY_ORDER,EXAM_CLASS,REQ_DEPT,REQ_PHYSICIAN,
             REQ_DATE,REQ_TIME,REQ_MEMO,CLIN_DIAG,PERFORM_DEPT,CHARGE_FLAG,visit_id,mi_card)
            select :new.yjdjh0,brid00,trim(VICKH00),BRXM00,BRXB00,to_char(to_date(BRCSRQ,'yyyymmdd'),'yyyy-mm-dd'),FBMC00,
             BRJTDZ,BRYB00,BRDH00,decode(:new.MZZYBZ,'0',trim(:NEW.ZYHGHH),''),decode(:new.MZZYBZ,1,trim(:NEW.ZYHGHH),''),
             :new.CH0000,decode(:new.MZZYBZ,0,'门诊',1,'住院','外来'),decode(:new.JZBZ00,'Y','急诊','普通'),VJCLBMC,:new.kdksbh,:new.KDYSXM,
             to_char(to_date(:new.kdrq00,'yyyymmdd'),'yyyy-mm-dd'),:new.kdsj00,:new.ZLXMJC,substr(:NEW.ZDMC00,1,100),:new.YJKSBH,decode(:NEW.SFDJH0,0,'0','1'),:NEW.zyghid,decode(trim(ybkh00),'0','',trim(ybkh00)) from VW_BM_BRXXB1 where brid00=:new.brid00;
    
     --edit by lingh    201001017  
     update spacs.exam_apply_item set spacs.exam_apply_item.amount= :new.Sl0000
            where spacs.exam_apply_item.apply_no=:new.yjdjh0;
     update spacs.exam_apply_item set cost=(COST*amount)
            where spacs.exam_apply_item.apply_no=:new.yjdjh0;
     update spacs.exam_apply_item set charge=(charge*amount)
            where spacs.exam_apply_item.apply_no=:new.yjdjh0;
      --201001017  
    
     end if;
    if (VJCLBID is not null) and ((:new.kdksbh<>:OLD.kdksbh) OR (:new.KDYSXM<>:OLD.KDYSXM) OR (:new.YJKSBH<>:OLD.YJKSBH)) then
      update spacs.EXAM_APPLY set REQ_DEPT=:new.kdksbh,REQ_PHYSICIAN=:new.KDYSXM,PERFORM_DEPT=:new.YJKSBH where APPLY_NO=:old.yjdjh0;
    end if;
    --检查类别项目修改收费标志
    if :new.SFDJH0<>:old.SFDJH0 and VJCLBID is not null then
      update spacs.EXAM_APPLY set CHARGE_FLAG='1' where APPLY_NO=:new.yjdjh0;
      update spacs.EXAM_APPLY_ITEM set CHARGE_FLAG='1' where APPLY_NO=:new.yjdjh0;
    end if;
    --作废时,删除接口表记录
    if :new.xmzt00='6' and VJCLBID is not null then
      delete from spacs.EXAM_APPLY_ITEM where APPLY_NO=:old.yjdjh0;
      delete from spacs.EXAM_APPLY where  APPLY_NO=:old.yjdjh0;
    end if;
  ELSIF DELETING THEN
    IF :old.ZLXMID>0 THEN
      ErrStr:='err1: 诊疗项目不存在ZLXMID='||to_char(:old.zlxmid);
      SELECT JCLBID INTO VJCLBID FROM BM_ZLZD00 WHERE ZLXMID=:old.ZLXMID;
    END IF;
    IF VJCLBID is not null then
      delete from spacs.EXAM_APPLY where APPLY_NO=:old.yjdjh0;
    END IF;
  END IF;
  EXCEPTION
    WHEN ECUSTOM THEN
      RAISE_APPLICATION_ERROR(-20030,ERRSTR||'！*');
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20010,'数据没有找到,'||errStr||'！*');
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20020, NVL(SQLERRM, '原因不明出错！*'));
End;
/