CREATE OR REPLACE TRIGGER tr_jy_yw0000_update_jgzt00
 BEFORE
   UPDATE OF jgzt00
 ON "SD_JY"."JY_YW0000"
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
DECLARE
  LD_COUNTER    NUMBER;
  LD_ID0000 NUMBER;
  LD_KWH000 NUMBER;
  LD_ZLXMIDNUM  Number;
  V_ZWXM00  VARCHAR2(20);
  V_JYJG00  JY_JYJG00.JYJG01%TYPE;
  JY_BQDYTMSFGXCYSJ xt_xtcs00.value0%type;
  JY_ZYHGHHPYCYSDRQFS  xt_xtcs00.value0%type;
  JY_JYJGSHSFSCWDZXM   xt_xtcs00.value0%type;
  JY_JYJGCLBZMRZ   xt_xtcs00.value0%type;
  JY_BRXXDYBGXMID  xt_xtcs00.value0%type;
  v_CYRQ00 JY_YW0000.PYRQ00%type;
  v_CYSJ00 JY_YW0000.PYSJ00%type;
  v_SDRQ00 JY_YW0000.PYRQ00%type;
  v_SDSJ00 JY_YW0000.PYSJ00%type;
  lscount number;
  V_LSTDBYXCXDY CHAR(1);
  V_bmmc00 BM_BMBM00.BMMC00%type;
  V_ch0000 JY_YW0000.CH0000%type;
  V_brxm00 JY_YW0000.BRXM00%type;
  V_sqbq00 JY_YW0000.SQBQ00%type;
  V_sqks00 JY_YW0000.SQKS00%type;
  V_czyxm0 JY_YW0000.BRXM00%type;
  V_czyks0 varchar2(20);
  JY_JYTHYCXSHSFTS xt_xtcs00.value0%type;
  V_ZYHGHH jy_yw0000.zyhghh%type;
  lvCOUNT0 number(3);
  lvCOUNT1 number(3);
  V_YJKSBH YJ_YW0000.YJKSBH%type;
  v_XXNR00 XT_XTTSXX.XXNR00%type;
  V_BGXMMC varchar2(2000);
  VCOUNT0 number(10);
  VCXRQ00 varchar2(8);
  VCXSJ00 varchar2(8);
  CURSOR CUR_JY_SJXMDY_SJBH00 IS
    SELECT SJBH00,SYSL00 FROM JY_SJXMDY WHERE ZLXMID = :NEW.ZLXMID AND YQDH00=:NEW.YQDH00;
  CURSOR CUR_JY_JYJG00 IS
    SELECT JYYWID,JYJG00,B.BGXMID FROM JY_BGXMZD A,JY_JYJG00 B
    WHERE A.BGXMID=B.BGXMID
      AND A.JGLX00 IN ('C','N')
      AND B.YBH000=:NEW.YBH000
      AND B.PYRQ00=:NEW.PYRQ00;
  cursor C_bgxmmc is select distinct ZWMC00 from jy_jyjg00 A,JY_BGXMZD B
    where A.YBH000=:NEW.YBH000 and A.PYRQ00=:NEW.PYRQ00 and A.SFCJJZ='Y' and A.BGXMID=B.BGXMID;
--Person           Date              Comments
--chenyw           2010.08.16        参数JY_XMCJRSFWKDYS里的项目开单人为申请人
--lintj            2010.09.01        过滤掉非正常排样的项目
--lintj            2010.10.10        增加：参数控制绿色通道的标本打印后不允许撤销
--lintj            2010.10.12        图像记录虽是非正常排样，但不能删除
--lintj            2010.10.15        排样后更新明细表
--lintj            2010.10.22        撤销排样后更新明细表
--lintj            2010.10.29        排样或撤销排样更新明细表转入TR_JY_YW0000_BINS_UPD中处理
--chenyw      2010.12.13        门诊条码签收时不要更新‘JY_XMCJRSFWKDYS ’里包含的项目的采集人。
--chenyw      2012.04.20        病区标本签收,如果BBCYRQ,BBCYSJ,BBCJR0为空的话,就取TMDYRQ,TMDYSJ和TMDYR0  by LIS-20120416-001
--chenyw      2012.07.19         排样的时候默认把dybz00如果是空的话改成'/'; by  LIS-20120719-001
--chenyw      2012.07.19        审核的时候,诊疗项目为99999999的项目不删除! by  LIS-20120626-001
--chenyw      2012.07.20        用住院号挂号号排样没条形码的时候,修改BBCYRQ BBCYSJ和 BBSDRQ BBSDSJ by LIS-20120704-001
--chenyw      2012.08.20        条形码排样的时候默认把dybz00如果是空的话改成'/'; by LIS-20120820-001
--chenyw      2012.08.24        根据JY_JYYQZD的SCKJL0 1:删除,2:不删除 来决定是否要删除某仪器审核时候空结果的数据  by  LIS-20120823-002
--chenyw      2013.01.28        增加一参数来控制是否删除没有项目对照的检验项目  by  LIS-20130128-002
--jinfl       2013.05.21        增加参数控制备注的默认值  by LIS-20130506-003
--jinfl       2013.05.27        增加传入疫情值   LIS-20130422-002
--chenyw      2013.06.17        增加一段代码来控制是否修改BM_BRXXB0中的BRXX00 for LIS-20130615-001
--chenyw      2013.09.03        把检验方法插入到检验结果表中 for LIS-20130827-001
--chenyw      2013.09.06        处理检验复查结果信息   for LIS-20130906-001
--chenyw      2016.01.15        撤销把ZZJFS0设置成‘0’  for LIS-20160113-001
--xutz        2016.02.23        当BBSDRQ以及BBSDSJ为空时，取当前的日期时间 for LIS-20151222-002
--yushenjun   2016.07.12        JYJG00增加当bgxmid为888888,888887,888886,888885时photo1字段存入艾迪康pdf报告  for LIS-20160224-001
--daihq       2016.07.29        读取参数JY_JYTHYCXSHSFTS，M和S模式，危急值增加写入系统消息 for LIS-20160122-001
--yushenjun   2016.08.18        修改当参数为JY_JYTHYCXSHSFTS时, bmbh00查询语句写法 for LIS-20160803-001
--xutz        2016.12.17        增加记录DYYBH0 for LIS-20161217-002
--xutz        2017.02.24        当标本删除采集时，重置FSBZ00为N for LIS-20170224-001
--daihq       2017.05.24        JY_JYJG00增加写入SJLY00 for LIS-20170527-003
--Nixj        2018.04.10        JY_JYJG00增加写入CUTOFF for LIS-20180312-001
Begin
--  IF (:OLD.JGZT00 = '7') AND (:NEW.JGZT00 <> '7') THEN
--      RAISE_APPLICATION_ERROR(-20030,'该标本已作废或冲销，请刷新后再操作！×');
--  END IF;
  IF :NEW.JGZT00 = '7' THEN
    :NEW.TXM000 := NULL;
  END IF;
  IF ((:OLD.JGZT00 = '8') OR (:OLD.JGZT00 = '9')) AND (:NEW.JGZT00 = '4') AND ((:NEW.SFBZ00 = '1') OR (:NEW.SFBZ00 = '0'))THEN
        SELECT NVL(MAX(TRIM(VALUE0)),'N') INTO V_LSTDBYXCXDY FROM XT_XTCS00 WHERE NAME00='JY_LSTDBYXCXDY';
        IF V_LSTDBYXCXDY = 'Y' THEN
              RAISE_APPLICATION_ERROR(-20060,'绿色通道标本不允许撤销打印！×');
        END IF;
  END IF;

  BEgin
    select instrb(trim(','||value0||','),to_char(:NEW.ZLXMID)) into LD_ZLXMIDNUM   from xt_xtcs00 where name00='JY_XMCJRSFWKDYS';
  exception
    when others then
     LD_ZLXMIDNUM:=0;
  end;


  BEgin
    select value0 into JY_BRXXDYBGXMID  from xt_xtcs00 where name00='JY_GXBRXXBGXMID';
  exception
    when others then
     JY_BRXXDYBGXMID :='N';
  end;


  BEgin
    select value0 into JY_JYJGSHSFSCWDZXM  from xt_xtcs00 where name00='JY_JYJGSHSFSCWDZXM ';
  exception
    when others then
     JY_JYJGSHSFSCWDZXM :='Y';
  end;

  Begin
    select value0 into JY_JYJGCLBZMRZ  from xt_xtcs00 where name00='JY_JYJGCLBZMRZ';
  exception
    when others then
     JY_JYJGCLBZMRZ :='/';
  end;

  begin
     select trim(value0) into JY_JYTHYCXSHSFTS  from xt_xtcs00 where name00 in ('JY_JYTHYCXSHSFTS');
  exception
    when others then
     JY_JYTHYCXSHSFTS:='N';
  end;

  IF (((:OLD.JGZT00 = '0') OR (:NEW.JGZT00 = '1')) AND (LD_ZLXMIDNUM>0))  THEN
        :NEW.BBCJR0 := :NEW.SQYS00;
  END IF;
  IF (:NEW.JGZT00 = '4') OR (:NEW.JGZT00 = '5') THEN
     begin
        select trim(value0)  into JY_ZYHGHHPYCYSDRQFS from xt_xtcs00 where name00 ='JY_ZYHGHHPYCYSDRQFS';
     exception
     when others then
        JY_ZYHGHHPYCYSDRQFS:='0';
     end;
     if  JY_ZYHGHHPYCYSDRQFS='1' then
       if (:NEW.BBSDRQ is null) and (:NEW.BBCYRQ is null)  then
        V_SDRQ00:=to_char(to_date(NVL(:NEW.PYRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.PYSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                           -(to_date(NVL(:NEW.PYRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.PYSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                           -to_date(NVL(:NEW.SQRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.SQSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                          )/2,'YYYYMMDD');
        V_SDSJ00:=to_char(to_date(NVL(:NEW.PYRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.PYSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                          -(to_date(NVL(:NEW.PYRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.PYSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                          -to_date(NVL(:NEW.SQRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.SQSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                          )/2,'HH24:MI:SS');

        V_CYRQ00:=to_char(to_date(V_SDRQ00||' '||V_SDSJ00,'YYYYMMDD HH24:MI:SS')
                                           -(to_date(V_SDRQ00||' '||V_SDSJ00,'YYYYMMDD HH24:MI:SS')
                                           -to_date(NVL(:NEW.SQRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.SQSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                          )/2,'YYYYMMDD');
        V_CYSJ00:=to_char(to_date(V_SDRQ00||' '||V_SDSJ00,'YYYYMMDD HH24:MI:SS')
                                           -(to_date(V_SDRQ00||' '||V_SDSJ00,'YYYYMMDD HH24:MI:SS')
                                           -to_date(NVL(:NEW.SQRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.SQSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                          )/2,'HH24:MI:SS');
       elsif (:NEW.BBSDRQ is  null) and (:NEW.BBCYRQ is not null)  then
           V_SDRQ00:=to_char(to_date(NVL(:NEW.PYRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.PYSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                           -(to_date(NVL(:NEW.PYRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.PYSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                           -to_date(NVL(:NEW.BBCYRQ,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.BBCYSJ,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                          )/2,'YYYYMMDD');
         V_SDSJ00:=to_char(to_date(nvl(:NEW.BBSDRQ,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||nvl(:NEW.BBSDSJ,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                           -(to_date(nvl(:NEW.BBSDRQ,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||nvl(:NEW.BBSDSJ,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                           -to_date(NVL(:NEW.BBCYRQ,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.BBCYSJ,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                          )/2,'HH24:MI:SS');
       elsif  (:NEW.BBSDRQ is not  null) and (:NEW.BBCYRQ is null)  then
         V_CYRQ00:=to_char(to_date(:NEW.BBSDRQ||' '||:NEW.BBSDSJ,'YYYYMMDD HH24:MI:SS')
                                           -(to_date(:NEW.BBSDRQ||' '||:NEW.BBSDSJ,'YYYYMMDD HH24:MI:SS')
                                           -to_date(NVL(:NEW.SQRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.SQSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                          )/2,'YYYYMMDD');
        V_CYSJ00:=to_char(to_date(:NEW.BBSDRQ||' '||:NEW.BBSDSJ,'YYYYMMDD HH24:MI:SS')
                                           -(to_date(:NEW.BBSDRQ||' '||:NEW.BBSDSJ,'YYYYMMDD HH24:MI:SS')
                                           -to_date(NVL(:NEW.SQRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'))||' '||NVL(:NEW.SQSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS')),'YYYYMMDD HH24:MI:SS')
                                          )/2,'HH24:MI:SS');
       end if;
    else
         V_CYRQ00:= NVL(:NEW.PYRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'));
         V_CYSJ00:= NVL(:NEW.PYSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS'));
         V_SDRQ00:= NVL(:NEW.PYRQ00,TO_CHAR(SYSDATE,'YYYYMMDD'));
         V_SDSJ00:= NVL(:NEW.PYSJ00,TO_CHAR(SYSDATE,'HH24:MI:SS'));
    end if;

    IF :NEW.BBCYRQ IS NULL THEN
        :NEW.BBCYRQ := V_CYRQ00;
        :NEW.BBCYSJ := V_CYSJ00;
        if LD_ZLXMIDNUM>0 then
          :NEW.BBCJR0 := :NEW.SQYS00;
        else
          :NEW.BBSDR0 := :NEW.BGR000;
        end if;
    END IF;
    IF ((:NEW.JGZT00 = '5') or (:NEW.JGZT00 = '4')) and (:NEW.DYBZ00 is null) and (trim(JY_JYJGCLBZMRZ)<>'N') THEN
         :NEW.DYBZ00:=trim(JY_JYJGCLBZMRZ);
     END IF;

    IF :NEW.BBSDRQ IS NULL THEN
        :NEW.BBSDRQ := V_SDRQ00;
        :NEW.BBSDSJ := V_SDSJ00;
        :NEW.BBSDR0 := :NEW.BGR000;
    END IF;

    UPDATE JY_JYJG00_TEMP SET SFDY00='Y' WHERE PYRQ00=:NEW.PYRQ00 AND YBH000=:NEW.YBH000 AND SFDY00='N';

    UPDATE JY_KWH000 SET SYBZ00='N',RKRQ00=NULL,TXM000=NULL WHERE KWH000 = :NEW.KWH000;
    :NEW.KWH000 := NULL;

    if ((:OLD.JGZT00 = '6') or (:OLD.JGZT00='8')) and ((:NEW.JGZT00='4') or (:NEW.JGZT00='5')) then
       :NEW.ZZJFS0:='0';
    end if;

    IF :OLD.JGZT00 <> '6' AND :OLD.JGZT00<>'8' THEN
        RETURN;
    END IF;

    IF :OLD.SSR000 IS NOT NULL THEN
        SELECT ZWXM00 INTO V_ZWXM00 FROM BM_YGBM00 WHERE YGBH00=:OLD.SSR000;
        RAISE_APPLICATION_ERROR(-20010,'样本号'||:OLD.YBH000||'被'||V_ZWXM00||'上锁，请解锁后再操作！*');
    END IF;
    VCXRQ00:=TO_CHAR(SYSDATE,'YYYYMMDD');VCXSJ00:=TO_CHAR(SYSDATE,'HH24:MI:SS');
    select count(1) into VCOUNT0 from JY_CXRZ00 where YBH000=:NEW.YBH000 and PYRQ00=:NEW.PYRQ00 and CXRQ00=VCXRQ00 and CXSJ00=VCXSJ00;
    if VCOUNT0 = 0 then
      SELECT SEQ_JY_CXRZ00_ID0000.NEXTVAL INTO LD_ID0000 FROM DUAL;
      INSERT INTO JY_CXRZ00(ID0000,YBH000,PYRQ00,CXRQ00,CXSJ00,CXR000,CXYY00)
          VALUES(LD_ID0000,:NEW.YBH000,:NEW.PYRQ00,VCXRQ00,VCXSJ00,
              :NEW.CZY000,:NEW.CXSHYY);
      INSERT INTO JY_JYJG00_CX(ID0000,JYYWID,YBH000,HBHYBH,PYRQ00,ZLXMID,BGXMID,YSJYJG,JYJG00,JGDW00,CKXX00,CKSX00,CKFW00,
          JGZCBZ,YQDH00,SECTOR,CUPNUM,BGRQ00,BGSJ00,YQJCRQ,SHR000,JGBZ00,JYFF00)
      SELECT LD_ID0000,JYYWID,YBH000,HBHYBH,PYRQ00,ZLXMID,BGXMID,YSJYJG,JYJG00,JGDW00,CKXX00,CKSX00,CKFW00,
          JGZCBZ,YQDH00,SECTOR,CUPNUM,BGRQ00,BGSJ00,YQJCRQ,SHR000,JGBZ00,JYFF00
        FROM JY_JYJG00
      WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00;
    end if;

    FOR ROW IN CUR_JY_SJXMDY_SJBH00 LOOP
        UPDATE JY_SJB000 SET YCKSL0 = YCKSL0 - ROW.SYSL00 WHERE SJBH00=ROW.SJBH00;
    END LOOP;
  END IF;

  IF (:NEW.JGZT00 = '2' AND :NEW.MZZYBZ = '0' and LD_ZLXMIDNUM>0) then
       :new.BBCJR0:=:OLD.BBCJR0;
  end if;

  begin
    select trim(value0) into JY_BQDYTMSFGXCYSJ from xt_xtcs00 where name00='JY_BQDYTMSFGXCYSJ' and rownum=1;
  EXCEPTION
  when others then
    JY_BQDYTMSFGXCYSJ:='Y';
  end;
  if (:NEW.JGZT00 = '2') and  (:old.JGZT00 = '1') and (:NEW.MZZYBZ = '1') and (JY_BQDYTMSFGXCYSJ='N') and (:new.BBCJR0 is null )then
      :new.BBCJR0:=:new.TMDYR0;
      :new.BBCYRQ:=:new.TMDYRQ;
      :new.BBCYSJ:=:new.TMDYSJ;
  end if ;

  IF (:NEW.JGZT00 = '2' AND :NEW.MZZYBZ = '1') THEN
    IF :NEW.KWH000 IS NULL THEN
        BEGIN
            SELECT KWH000 INTO LD_KWH000 FROM JY_KWH000 WHERE TXM000=NVL(:NEW.TXM000,0)
               AND SYBZ00='Y' AND ROWNUM=1;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                SELECT MIN(KWH000) INTO LD_KWH000 FROM JY_KWH000 WHERE SYBZ00='N';

        END;
        UPDATE JY_KWH000 SET SYBZ00='N',RKRQ00=NULL WHERE RKRQ00<TO_CHAR(SYSDATE-20,'YYYYMMDD') AND SYBZ00='Y';
        UPDATE JY_KWH000 SET SYBZ00='Y',RKRQ00=:NEW.SQRQ00,TXM000=:NEW.TXM000 WHERE KWH000 = LD_KWH000;
        :NEW.KWH000 := LD_KWH000;
    END IF;
  END IF;

  IF (:NEW.JGZT00 = '6') AND (:NEW.JGZT00 <> :OLD.JGZT00) THEN
    DELETE from jy_jyjg00_temp A where YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00
       AND bgxmid in (select bgxmid from jy_jyjg00_temp where YBH000=A.YBH000 AND PYRQ00=A.PYRQ00 AND NVL(DYYBH0,'0')='0'  group by bgxmid having count(*)>1)
       and rowid not in (select min(rowid) from jy_jyjg00_temp where YBH000=A.YBH000 AND PYRQ00=A.PYRQ00  AND NVL(DYYBH0,'0')='0' group by bgxmid having count(*)>1);

     if JY_JYJGSHSFSCWDZXM='Y' then
        DELETE FROM JY_JYJG00_TEMP WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00 AND (BGXMID<999990 and BGXMID not in (888888,888887,888886,888885))
               AND (BGXMID NOT IN (SELECT B.BGXMID FROM JY_YWMX00 A,JY_ZLBGDY B WHERE A.ZLXMID=B.ZLXMID
               AND A.YBH000=:NEW.YBH000 AND A.PYRQ00=:NEW.PYRQ00) and ZLXMID<>99999999) and YQDH00 in (SELECT YQDH00 FROM JY_JYYQZD WHERE SCKJL0='1');
     end if;

    INSERT INTO JY_JYJG00(JGID00,JYYWID,YBH000,PYRQ00,ZLXMID,BGXMID,JYJG00,PHOTO1,YSJYJG,JGDW00,
          CKXX00,CKSX00,CKFW00,SECTOR,CUPNUM,JGZCBZ,YQDH00,SHR000,BGRQ00,BGSJ00,YQJCRQ,OD0000,SFCJJZ,JJZSX0,JJZXX0,SFCYQZ,JYFF00,DYYBH0,SJLY00,CUTOFF)
        SELECT SEQ_JY_JYJG00_JGID00.nextval,DECODE(JYYWID,0,:NEW.JYYWID,NULL,:NEW.JYYWID,JYYWID),YBH000,PYRQ00,
        DECODE(ZLXMID,0,:NEW.ZLXMID,NULL,:NEW.ZLXMID,ZLXMID),A.BGXMID,JYJG00,PHOTO1,YSJYJG,JGDW00,
          CKXX00,CKSX00,CKFW00,NVL(SECTOR,0),NVL(CUPNUM,0),JGZCBZ,A.YQDH00,:NEW.BGJYR0,:NEW.CBGRQ0,:NEW.CBGSJ0,
          YQJCRQ,OD0000,SFCJJZ,JJZSX0,JJZXX0,SFCYQZ,nvl(A.JYFF00,B.JYFF00) JYFF00,a.DYYBH0,A.SJLY00,A.CUTOFF
        FROM JY_JYJG00_TEMP A,JY_BGXMZD B
        WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00 AND JYYWID>0 AND A.BGXMID<999992 AND (TRIM(JYJG00) IS NOT NULL  or ZLXMID=99999999
        or A.YQDH00 in (SELECT YQDH00 FROM JY_JYYQZD WHERE SCKJL0='0') ) AND A.BGXMID=B.BGXMID and B.SYBZ00='Y' ;

    INSERT INTO JY_JYJG00(JGID00,JYYWID,YBH000,PYRQ00,ZLXMID,BGXMID,JYJG00,PHOTO1,YSJYJG,JGDW00,
          CKXX00,CKSX00,CKFW00,SECTOR,CUPNUM,JGZCBZ,YQDH00,SHR000,BGRQ00,BGSJ00,YQJCRQ,OD0000,SFCJJZ,JJZSX0,JJZXX0,SFCYQZ,JYFF00,DYYBH0,SJLY00,CUTOFF)
        SELECT SEQ_JY_JYJG00_JGID00.nextval,DECODE(JYYWID,0,:NEW.JYYWID,NULL,:NEW.JYYWID,JYYWID),YBH000,PYRQ00,
        DECODE(ZLXMID,0,:NEW.ZLXMID,NULL,:NEW.ZLXMID,ZLXMID),BGXMID,JYJG00,PHOTO1,YSJYJG,JGDW00,
          CKXX00,CKSX00,CKFW00,NVL(SECTOR,0),NVL(CUPNUM,0),JGZCBZ,YQDH00,:NEW.BGJYR0,:NEW.CBGRQ0,:NEW.CBGSJ0,
          YQJCRQ,OD0000,SFCJJZ,JJZSX0,JJZXX0,SFCYQZ,JYFF00,a.DYYBH0,A.SJLY00,CUTOFF
        FROM JY_JYJG00_TEMP A
        WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00 AND BGXMID>999991;

    INSERT INTO JY_JYJG00(JGID00,JYYWID,YBH000,PYRQ00,ZLXMID,BGXMID,JYJG00,PHOTO1,YSJYJG,JGDW00,
          CKXX00,CKSX00,CKFW00,SECTOR,CUPNUM,JGZCBZ,YQDH00,SHR000,BGRQ00,BGSJ00,YQJCRQ,OD0000,SFCJJZ,JJZSX0,JJZXX0,SFCYQZ,JYFF00,DYYBH0,SJLY00,CUTOFF)
        SELECT SEQ_JY_JYJG00_JGID00.nextval,DECODE(JYYWID,0,:NEW.JYYWID,NULL,:NEW.JYYWID,JYYWID),YBH000,PYRQ00,
        DECODE(ZLXMID,0,:NEW.ZLXMID,NULL,:NEW.ZLXMID,ZLXMID),BGXMID,JYJG00,PHOTO1,YSJYJG,JGDW00,
          CKXX00,CKSX00,CKFW00,NVL(SECTOR,0),NVL(CUPNUM,0),JGZCBZ,YQDH00,:NEW.BGJYR0,:NEW.CBGRQ0,:NEW.CBGSJ0,
          YQJCRQ,OD0000,SFCJJZ,JJZSX0,JJZXX0,SFCYQZ,JYFF00,a.DYYBH0,A.SJLY00,A.CUTOFF
        FROM JY_JYJG00_TEMP A
        WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00 AND BGXMID in (888888,888887,888886,888885);

    --复查结果处理
    begin
       select count(*) into lscount from xt_xtcs00 where name00='JY_SFQYFCCZ' and trim(value0)='Y';
    exception
         when others then
            lscount:=0;
    end;
    if lscount>0 then
       insert into jy_fcjg00(YBH000,PYRQ00,FCYBH0,FCRQ00,BGXMID,FCJG00,JGZCBZ)
                   select YBH000,PYRQ00,FCYBH0,FCRQ00,BGXMID,FCJG00,FCZCBZ from vw_jy_fcjg00
                   where YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00;
    end if;
    --复查结果处理

    if trim(JY_BRXXDYBGXMID)<>'N' then
        begin
        select count(*) into lscount  from  JY_JYJG00_TEMP where YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00
                 and instr(','||trim(JY_BRXXDYBGXMID)||',',','||to_char(bgxmid)||',')>0;
        exception
        when others then
            lscount:=0;
      end;
      if lscount>0 then
       begin
          update BM_BRXXB0 set BRXX00=(select trim(jyjg00) from jy_jyjg00_temp where YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00
                               and instr(','||trim(JY_BRXXDYBGXMID)||',',','||to_char(bgxmid)||',')>0) where brid00=:New.brid00;
       exception
        when others then
         null;
       end;
      end if;
    end if;
    if (JY_JYTHYCXSHSFTS in ('S','M')) then
      select count(1) into lvCOUNT0 from JY_JYJG00 where YBH000=:NEW.YBH000 and PYRQ00=:NEW.PYRQ00 and SFCJJZ='Y';
      if lvCOUNT0>0 then
        select count(1) into lvCOUNT1 from XT_XTTSXX
        where CZRQ00=to_char(sysdate,'YYYYMMDD')
        and YXQ000>=to_char(sysdate,'YYYYMMDD')
        and XXGLBZ2=nvl(:NEW.YBH000,'-1')
        and XXGLBZ3=nvl(:NEW.PYRQ00,'-1')
        and ZT0000='0'
        and XTDM00='J';
        if lvCOUNT1=0 then
          for bgxm in C_bgxmmc loop
            if lengthb(V_BGXMMC) > 2000 then
                 V_BGXMMC := Substrb(V_BGXMMC,1,2000);
                 exit;
            end if;
            if V_BGXMMC is null then
                V_BGXMMC:=trim(bgxm.ZWMC00);
            else
                V_BGXMMC:=trim(V_BGXMMC||'|'||trim(bgxm.ZWMC00));
            end if;
          end loop;
          select a.YJKSBH,KDKSBH,KDBQ00,a.ZYHGHH,a.CH0000,a.BRXM00
          into V_YJKSBH,V_sqKS00,V_sqbq00,V_ZYHGHH,V_ch0000,V_brxm00
          from YJ_YW0000 a
          where a.YJDJH0=:NEW.YJDJH0;
          if :new.MZZYBZ='1' then
            select nvl(BMMC00,'') into V_bmmc00 from BM_BMBM00 where BMBH00=V_sqbq00;
            v_XXNR00:='【'||trim(V_bmmc00)||'】【'||trim(V_ch0000)||'床】';
          else
            select nvl(BMMC00,'') into V_bmmc00 from BM_BMBM00 where BMBH00=V_sqKS00;
            V_sqbq00:=V_sqKS00;
            v_XXNR00:='【'||trim(V_bmmc00)||'】门诊号：【'||trim(V_ZYHGHH)||'】';
          end if;
          select nvl(max(BMMC00),'') into V_bmmc00 from BM_BMBM00 where BMBH00=V_YJKSBH and rownum=1;
          select nvl(max(b.bmbh00),'-1'),nvl(max(a.zwxm00),'无') into V_czyks0,V_czyxm0 from bm_ygbm00 a,bm_bmbm00 b where a.bmbh00=b.bmbh00 and a.ygbh00=:new.BGJYR0 and rownum=1;
          insert into XT_XTTSXX
          (XTDM00,XXBH00,XXNR00,YXQ000,CZYBH0,CZYXM0,CZRQ00,XH0000,JSKSBH,JSKSMC,XXXZ00,czyks0,XXGLBZ,XXGLBZ2,XXGLBZ3)
          values
          ('J',sq_xt_xttsxx_xxbh00.NEXTVAL,v_XXNR00||'【'||V_brxm00||'】条码【'||:new.txm000||'】样本号【'||:NEW.YBH000||'】排样日期【'||:NEW.PYRQ00||
          '】审核人【'||V_czyxm0||'】项目名称：'||V_BGXMMC||'，超出警戒值请及时查看',to_char(sysdate,'YYYYMMDD'),
          :new.BGJYR0,V_czyxm0,TO_CHAR(SYSDATE,'YYYYMMDD'),sq_xt_xttsxx_xh0000.NEXTVAL,V_YJKSBH,V_bmmc00,'0',V_czyks0,:new.txm000,:NEW.YBH000,:NEW.PYRQ00);
        end if;
      end if;
    end if;
    DELETE FROM JY_JYJG00_TEMP WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00;
--  DELETE FROM JY_JYJG00_TEMP WHERE JYYWID=:NEW.JYYWID;
    --复查结果删除处理
    begin
      select count(*) into lscount from xt_xtcs00 where name00='JY_SFQYFCCZ' and trim(value0)='Y';
    exception
        when others then
           lscount:=0;
    end;
    if lscount>0 then
       delete from jy_jyjg00_temp where pyrq00=:new.fcrq00 and ybh000=:new.fcybh0;
    end if;
    --20130905 xtz 复查结果
    FOR ROW IN CUR_JY_SJXMDY_SJBH00 LOOP
        UPDATE JY_SJB000 SET YCKSL0 = YCKSL0 + ROW.SYSL00 WHERE SJBH00=ROW.SJBH00;
    END LOOP;
    FOR ROW IN CUR_JY_JYJG00 LOOP
        BEGIN
        V_JYJG00 := TO_NUMBER(ROW.JYJG00);
        UPDATE JY_JYJG00 SET JYJG01=V_JYJG00 WHERE JYYWID=ROW.JYYWID AND BGXMID=ROW.BGXMID;
        EXCEPTION
                WHEN OTHERS THEN
            V_JYJG00 := 0;
        END;
    END LOOP;
  END IF;
  IF (:NEW.JGZT00 = '8') OR (:NEW.JGZT00 = '9') THEN
    DELETE FROM JY_PIC000 WHERE PYRQ00=:NEW.PYRQ00 AND YBH000=:NEW.YBH000;
  END IF;

--add by xutz for LIS-20170224-001
  IF (:NEW.JGZT00 = '0') and ((:OLD.JGZT00 = '1') or (:OLD.JGZT00 = '2')) THEN
    :new.FSBZ00:='N';
  END IF;

EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20020, NVL(SQLERRM, '原因不明出错！*'));
End;