CREATE OR REPLACE TRIGGER TR_YJ_YW0000_UPD_PACS
 AFTER 
 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;
   vjclbid   bm_jclb00.jclbid%TYPE;
   vickh00   ic_yhxx00.ickh00%TYPE;
   vybkh00   VARCHAR2 (32);
   vjclbmc   bm_jclb00.jclbmc%type;
   vcount    number(2,0);
   vzdmc      VARCHAR2 (200);
   vFlag00   char(1);   -- 1、开单后传送；；3、执行后传送；

BEGIN
   --return;
     vFlag00 := '3';
   IF INSERTING and  vFlag00 = '1' 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';
            SELECT DECODE(TRIM (ybkh00),'0','',TRIM (ybkh00))  INTO vybkh00  FROM bm_brxxb0   WHERE brid00 = :NEW.brid00;

         EXCEPTION
            WHEN OTHERS
            THEN
               vickh00 := '';
               vybkh00 := '';
         END;

         --主表
         INSERT INTO exam_apply@yppacs
                     (apply_no, sick_id, ic_card,MI_CARD, NAME, sex, birth_date,
                      charge_type, costs, charges, 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)
            SELECT :NEW.yjdjh0, brid00, TRIM (vickh00),TRIM(vybkh00),
                   SUBSTR (brxm00, 1, 24), SUBSTR (brxb00, 1, 4),
                   TO_CHAR (TO_DATE (brcsrq, 'yyyymmdd'), 'yyyy-mm-dd'),
                   SUBSTR (fbmc00, 1, 8), :NEW.zje000, :NEW.zje000, brjtdz,
                   SUBSTR (bryb00, 1, 6), SUBSTR (brdh00, 1, 16),
                   DECODE (:NEW.mzzybz,'0', SUBSTR (TRIM (:NEW.zyhghh), 1, 10),''),
                   DECODE (:NEW.mzzybz,1, SUBSTR (TRIM (:NEW.zyhghh), 1, 10),''),
                   :NEW.ch0000,
                   DECODE (:NEW.mzzybz, 0, '门诊', 1, '住院', '外来'),
                   DECODE (:NEW.jzbz00, 'Y', '急诊', '普通'), vjclbmc,
                   :NEW.kdksbh, SUBSTR (:NEW.kdysxm, 1, 8),
                   TO_CHAR (TO_DATE (:NEW.kdrq00, 'yyyymmdd'), 'yyyy-mm-dd'),
                   :NEW.kdsj00, :NEW.zlxmjc, SUBSTR (:NEW.zdmc00, 1, 100),
                   :NEW.yjksbh, '1', :NEW.zyghid
              FROM vw_bm_brxxb1
             WHERE brid00 = :NEW.brid00;
      END IF;
   ELSIF UPDATING  THEN
    SELECT jclbid  INTO vjclbid   FROM bm_zlzd00   WHERE zlxmid = :NEW.zlxmid;
    --根据门诊住院标志获取诊断名称
     
        if :new.mzzybz='1' then 
           begin
                  select zdmc00 into vzdmc from (  
           select zdmc00 from bq_brzdxx where zyid00=:new.zyghid   
           order by sfyz00,decode(zdlb00,'5','0',zdlb00) desc,zdrq00 desc) where rownum=1;
           exception
               when others then
               vzdmc:='';
           end;
        elsif  :new.mzzybz='0' then 
           begin
                 select ZDMC00 into vzdmc from (  
           select ZDMC00 from ys_brzdxx where ghid00=:new.zyghid   
           order by sfyz00,decode(zzdbz0,'1','6','2','7',zzdbz0),zdrq00 desc) where rownum=1;
           exception
               when others then
               vzdmc:='';
           end;
        end if;
      
    if vjclbid is not null 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 :new.yjdjh0 is not null then
         select count(*) into vcount from  exam_apply@yppacs  where apply_no = :new.yjdjh0;
      end if;

      IF (vjclbid IS NOT null) and (vcount=0)
      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';
            --SELECT 'FZYB'||YBKH00  INTO vickh00  FROM BM_BRXXB0  WHERE brid00 = :NEW.brid00 AND ROWNUM=1;
            SELECT DECODE(TRIM (ybkh00),'0','',TRIM (ybkh00))  INTO vybkh00  FROM bm_brxxb0   WHERE brid00 = :NEW.brid00;
         EXCEPTION
            WHEN OTHERS
            THEN
               vickh00 := '';
               vybkh00 := '';
         END;

         if  (vFlag00 = '3') and  (:NEW.XMZT00 = 3 ) then
           if  (:New.zxsl00 > 0)  then

           INSERT INTO exam_apply@yppacs
                     (apply_no, sick_id, ic_card,MI_CARD, NAME, sex, birth_date,
                      charge_type, costs, charges, 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)
            SELECT :NEW.yjdjh0, brid00, TRIM (vickh00),TRIM(vybkh00),
                   SUBSTR (brxm00, 1, 24), SUBSTR (brxb00, 1, 4),
                   TO_CHAR (TO_DATE (brcsrq, 'yyyymmdd'), 'yyyy-mm-dd'),
                   SUBSTR (fbmc00, 1, 8), :NEW.zje000, :NEW.zje000, brjtdz,
                   SUBSTR (bryb00, 1, 6), SUBSTR (brdh00, 1, 16),
                   DECODE (:NEW.mzzybz, '0', SUBSTR (TRIM (:NEW.zyhghh), 1, 10), '' ),
                   DECODE (:NEW.mzzybz,1, SUBSTR (TRIM (:NEW.zyhghh), 1, 10),''),
                   :NEW.ch0000,
                   DECODE (:NEW.mzzybz, 0, '门诊', 1, '住院', '外来'),
                   DECODE (:NEW.jzbz00, 'Y', '急诊', '普通'), vjclbmc,
                   :NEW.kdksbh, SUBSTR (:NEW.kdysxm, 1, 8),
                   TO_CHAR (TO_DATE (:NEW.kdrq00, 'yyyymmdd'), 'yyyy-mm-dd'),
                   :NEW.kdsj00, :NEW.zlxmjc,vzdmc,--SUBSTR (:NEW.zdmc00, 1, 100),
                   :NEW.yjksbh, '1', :NEW.zyghid
              FROM vw_bm_brxxb1
             WHERE brid00 = :NEW.brid00;
            end if;
          end if;
      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';
            --SELECT 'FZYB'||YBKH00  INTO vickh00  FROM BM_BRXXB0  WHERE brid00 = :NEW.brid00 AND ROWNUM=1;
            SELECT DECODE(TRIM (ybkh00),'0','',TRIM (ybkh00))  INTO vybkh00  FROM bm_brxxb0   WHERE brid00 = :NEW.brid00;
            --select zdmc00  into vzdmc  from vw_yj_yw0000 WHERE yjdjh0 = :NEW.yjdjh0;



         EXCEPTION
            WHEN OTHERS
            THEN
               vickh00 := '';
               vybkh00 := '';
         END;

         DELETE FROM exam_apply@yppacs WHERE apply_no = :OLD.yjdjh0;

         INSERT INTO exam_apply@yppacs                     (apply_no, sick_id, ic_card,MI_CARD, NAME, sex, birth_date,
                      charge_type, costs, charges, 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)
            SELECT :NEW.yjdjh0, brid00, TRIM (vickh00),TRIM(vybkh00),
                   SUBSTR (brxm00, 1, 24), SUBSTR (brxb00, 1, 4),
                   TO_CHAR (TO_DATE (brcsrq, 'yyyymmdd'), 'yyyy-mm-dd'),
                   SUBSTR (fbmc00, 1, 8), :NEW.zje000, :NEW.zje000, brjtdz,
                   SUBSTR (bryb00, 1, 6), SUBSTR (brdh00, 1, 16),
                   DECODE (:NEW.mzzybz,'0', SUBSTR (TRIM (:NEW.zyhghh), 1, 10),''),
                   DECODE (:NEW.mzzybz,1, SUBSTR (TRIM (:NEW.zyhghh), 1, 10),''),
                   :NEW.ch0000,
                   DECODE (:NEW.mzzybz, 0, '门诊', 1, '住院', '外来'),
                   DECODE (:NEW.jzbz00, 'Y', '急诊', '普通'), vjclbmc,
                   :NEW.kdksbh, SUBSTR (:NEW.kdysxm, 1, 8),
                   TO_CHAR (TO_DATE (:NEW.kdrq00, 'yyyymmdd'), 'yyyy-mm-dd'),
                   :NEW.kdsj00, :NEW.zlxmjc,vzdmc,--SUBSTR (:NEW.zdmc00, 1, 100),
                   :NEW.yjksbh, '1', :NEW.zyghid
              FROM vw_bm_brxxb1
             WHERE brid00 = :NEW.brid00;
      END IF;
      IF  (vjclbid IS NOT NULL) AND ((:NEW.kdksbh <> :OLD.kdksbh)OR (:NEW.kdysxm <> :OLD.kdysxm)OR (:NEW.yjksbh <>
:OLD.yjksbh))
      THEN
         UPDATE exam_apply@yppacs 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 exam_apply@yppacs SET charge_flag = '1' WHERE apply_no = :NEW.yjdjh0;
         UPDATE exam_apply_item@yppacs SET charge_flag = '1' WHERE apply_no = :NEW.yjdjh0;
      END IF;

      --作废时,删除接口表记录
      IF :NEW.xmzt00 = '6' AND vjclbid IS NOT NULL
      THEN
         DELETE FROM exam_apply_item@yppacs WHERE apply_no = :OLD.yjdjh0;
         DELETE FROM exam_apply@yppacs WHERE apply_no = :OLD.yjdjh0;
      END IF;
   end if;
   ELSIF DELETING THEN
      IF :OLD.zlxmid > 0
      THEN
         errstr := 'err1: 诊疗项目不存在ZLXMID=' || TO_CHAR (:OLD.zlxmid);
         --if :new.yjksbh in (531,566) and vjclbid = 10 then
           --   vjclbid:= 12;
         --ELSif  :new.yjksbh in (499,505) and vjclbid = 10 then
           --   vjclbid:= 13;
         --ELSif  :new.yjksbh in (872) and vjclbid = 1 then
           --   vjclbid:= 20;
             -- end if;
         SELECT jclbid INTO vjclbid FROM bm_zlzd00  WHERE zlxmid = :OLD.zlxmid;
      END IF;
      IF vjclbid IS NOT NULL
      THEN
         DELETE FROM exam_apply@yppacs  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;
/