CREATE OR REPLACE TRIGGER TR_JY_JYJG00_BINS
 BEFORE 
 INSERT OR UPDATE
 ON SD_JY.JY_JYJG00
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
DECLARE
  V_COUNT0  NUMBER;
  V_JYYWID  JY_YW0000.JYYWID%TYPE;
  V_ZLXMID  JY_YW0000.ZLXMID%TYPE;
  V_XB0000  JY_YW0000.XB0000%TYPE;
  V_NL0000  JY_YW0000.NL0000%TYPE;
  V_NLDW00  JY_YW0000.NLDW00%TYPE;
  V_BBID00  JY_YW0000.BBID00%TYPE;
  V_BGR000  JY_YW0000.BGR000%TYPE;
  V_CKZNL0  JY_BGXMZD.CKZNL0%TYPE;
  V_CKZXB0  JY_BGXMZD.CKZXB0%TYPE;
  V_CKZBB0  JY_BGXMZD.CKZBB0%TYPE;
  V_QSZ000  JY_BGXMZD.QSZ000%TYPE;
  V_DW0000  JY_BGXMZD.DW0000%TYPE;
  V_JGLX00  JY_BGXMZD.JGLX00%TYPE;
  V_JD0000  JY_BGXMZD.JD0000%TYPE;
  V_XXTS00  JY_XMCKZ0.XXTS00%TYPE;
  V_SXTS00  JY_XMCKZ0.SXTS00%TYPE;
  V_JYJG00  JY_JYJG00_TEMP.JYJG00%TYPE;
  V_YQZ000  JY_XMCKZ0.YQZ000%TYPE;
  V_VALUE0  CHAR(1);
  V_JGFS00  VARCHAR2(100);
  V_JYJG01  NUMBER(20,5);
  lvJY_WFBTGXTPDZCBZYQ number(5);
  E_MODIFY  EXCEPTION;
  Vcount    number(1);
--Person          Date          Comments
--chenyw          2011.10.08    创建触发器
--chenyw          2013.01.23    增加年龄单位为'W'(周) by LIS-20130118-001
--jinfl           2013.05.27    增加是否超疫情值的判定  LIS-20130422-002
--chenyw          2015.05.19     外送标本导入结果的时候不通过本系统判断结果超出标志  for LIS-20150519-001
--xutz            2016.07.30    增加条件当SJLY00为空时，才通过参考上下限判断结果正常标志 for LIS-20160802-001
--xutz            2016.08.19    不清空JGZCBZ,且当SJLY00为空时，判断LIS系统的参考范围 for LIS-20160818-002
BEGIN
  :NEW.JYJG00 := TRIM(:NEW.JYJG00);
  
  IF :NEW.JYJG00 = '' THEN
    :NEW.JYJG00 := NULL;
  END IF;
  
  begin
    select instr(','||trim(value0)||',',','||trim(:new.yqdh00)||',') into lvJY_WFBTGXTPDZCBZYQ  from xt_xtcs00 where name00 in ('JY_WFBTGXTPDZCBZYQ');
  exception
  when others then
     lvJY_WFBTGXTPDZCBZYQ:=0;
  end;
  
  IF :NEW.BGXMID < 999992 THEN
    SELECT JGLX00,JD0000 INTO V_JGLX00,V_JD0000 FROM JY_BGXMZD WHERE BGXMID=:NEW.BGXMID;  
  END IF;
  IF ((V_JGLX00 = 'N') OR (V_JGLX00 = 'C')) AND (:NEW.JYJG00 IS NOT NULL) THEN 
    V_JGFS00 := '9999999999999999999.';
        V_JGFS00 := RPAD(V_JGFS00,LENGTH(V_JGFS00)+V_JD0000,'9');   
    BEGIN
        V_JYJG01 := ROUND(:NEW.JYJG00,V_JD0000);
        SELECT TRIM(TO_CHAR(V_JYJG01,V_JGFS00)) INTO :NEW.JYJG00 FROM DUAL;
        IF INSTR(:NEW.JYJG00,'-.') = 1 THEN
            :NEW.JYJG00 := '-0.'||SUBSTR(:NEW.JYJG00,3,LENGTH(:NEW.JYJG00));
        END IF;
        IF INSTR(:NEW.JYJG00,'.') = 1 THEN
            :NEW.JYJG00 := '0'||:NEW.JYJG00;
        END IF;
        IF INSTR(:NEW.JYJG00,'.') = LENGTH(:NEW.JYJG00) THEN
            :NEW.JYJG00 := SUBSTR(:NEW.JYJG00,1,LENGTH(:NEW.JYJG00)-1);
        END IF;         
    EXCEPTION
        WHEN OTHERS THEN
            :NEW.JYJG00 := :NEW.JYJG00;
    END;
  END IF; 
  begin   
  SELECT COUNT(*) INTO V_COUNT0 FROM JY_YW0000_CKZ000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00;
  exception 
  when others then
     V_COUNT0:=0;
  end;
  IF V_COUNT0 > 0 THEN
    SELECT JYYWID,ZLXMID,XB0000,NL0000,NLDW00,BBID00 INTO V_JYYWID,V_ZLXMID,V_XB0000,V_NL0000,V_NLDW00,V_BBID00 
      FROM JY_YW0000_CKZ000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00 AND ROWNUM=1;     
  ELSE 
    begin
    SELECT COUNT(*) INTO V_COUNT0 FROM JY_YW0000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00;
    exception
    when others then
       V_COUNT0:=0;
    end;
    IF V_COUNT0 > 0 THEN
            SELECT JYYWID,ZLXMID,XB0000,NL0000,NLDW00,BBID00 INTO V_JYYWID,V_ZLXMID,V_XB0000,V_NL0000,V_NLDW00,V_BBID00 
              FROM JY_YW0000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00  AND ROWNUM=1;   
              
            select XB0000,NL0000,NLDW00,BBID00 INTO V_XB0000,V_NL0000,V_NLDW00,V_BBID00 FROM JY_YW0000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00 AND ROWNUM=1;                  
        END IF;    
  END IF; 
  IF :NEW.BGXMID > 999993 THEN 
    RETURN;
  END IF;
  
  if lvJY_WFBTGXTPDZCBZYQ<>0 then
    RETURN;
  end if;

--  if :NEW.SJLY00 is null then  
--    :NEW.JGZCBZ := NULL;
--    :NEW.SFCJJZ := 'N';
--    :NEW.SFCYQZ := 'N'; 
--  end if;

  :NEW.SFCYQZ := 'N';      
  begin
  SELECT COUNT(*) INTO V_COUNT0 FROM JY_YW0000_CKZ000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00;
  exception 
  when others then
     V_COUNT0:=0;
  end;
  IF V_COUNT0 > 0 THEN
    SELECT JYYWID,ZLXMID,XB0000,NL0000,NLDW00,BBID00 INTO V_JYYWID,V_ZLXMID,V_XB0000,V_NL0000,V_NLDW00,V_BBID00 
      FROM JY_YW0000_CKZ000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00 AND ROWNUM=1;     
  ELSE 
    begin
    SELECT COUNT(*) INTO V_COUNT0 FROM JY_YW0000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00;
    exception 
    when others then
        V_COUNT0:=0;
     end;
    IF V_COUNT0 > 0 THEN
            SELECT JYYWID,ZLXMID,XB0000,NL0000,NLDW00,BBID00 INTO V_JYYWID,V_ZLXMID,V_XB0000,V_NL0000,V_NLDW00,V_BBID00 
              FROM JY_YW0000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00  AND ROWNUM=1;   
              
            select XB0000,NL0000,NLDW00,BBID00 INTO V_XB0000,V_NL0000,V_NLDW00,V_BBID00 FROM JY_YW0000 WHERE YBH000=:NEW.YBH000 AND PYRQ00=:NEW.PYRQ00 AND ROWNUM=1;              
        END IF;    
  END IF; 
    
  BEGIN
    SELECT CKZNL0,CKZXB0,CKZBB0,QSZ000,DW0000 INTO V_CKZNL0,V_CKZXB0,V_CKZBB0,V_QSZ000,V_DW0000
    FROM JY_BGXMZD WHERE BGXMID=:NEW.BGXMID;
      
    IF V_CKZNL0 = 'Y' THEN
        SELECT a.ckxx00,a.cksx00,a.ckfw00,a.jjzsx0,a.jjzxx0,XXTS00,SXTS00,a.YQZ000 INTO :NEW.CKXX00,:NEW.CKSX00,:NEW.CKFW00,:NEW.JJZSX0,:NEW.JJZXX0,V_XXTS00,V_SXTS00,V_YQZ000 
        FROM JY_XMCKZ0 A 
        WHERE BGXMID=:NEW.BGXMID 
        AND NVL(XBDM00,'W')=NVL(DECODE(V_CKZXB0,'Y',V_XB0000,XBDM00),'W')
        AND NVL(BBID00,0)=NVL(DECODE(V_CKZBB0,'Y',V_BBID00,BBID00),0)
        AND decode(V_NLDW00,'M',TO_NUMBER(V_NL0000)*30,'W',TO_NUMBER(V_NL0000)*7,TO_NUMBER(V_NL0000))>=decode(A.NLDW00,'M',NVL(A.NLXX00,0)*30,'W',NVL(A.NLXX00,0)*7,NVL(A.NLXX00,0))
        AND decode(V_NLDW00,'M',TO_NUMBER(V_NL0000)*30,'W',TO_NUMBER(V_NL0000)*7,TO_NUMBER(V_NL0000))<=decode(A.NLDW00,'M',NVL(A.NLSX00,180)*30,'W',NVL(A.NLSX00,1200)*7,NVL(A.NLSX00,180))
        AND V_NLDW00=A.NLDW00 AND ROWNUM=1;
    ELSE
        SELECT a.ckxx00,a.cksx00,a.ckfw00,a.jjzsx0,a.jjzxx0,XXTS00,SXTS00,a.YQZ000 INTO :NEW.CKXX00,:NEW.CKSX00,:NEW.CKFW00,:NEW.JJZSX0,:NEW.JJZXX0,V_XXTS00,V_SXTS00,V_YQZ000 
        FROM JY_XMCKZ0 A 
        WHERE BGXMID=:NEW.BGXMID 
        AND NVL(XBDM00,'W')=NVL(DECODE(V_CKZXB0,'Y',V_XB0000,XBDM00),'W')
        AND NVL(BBID00,0)=NVL(DECODE(V_CKZBB0,'Y',V_BBID00,BBID00),0)
        AND ROWNUM=1; 
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN;
  END;
   
  IF (:NEW.CKFW00 IS NULL) OR (TRIM(:NEW.CKFW00) = '') THEN 
    RETURN;
  END IF;
  
  
  if nvl(:NEW.SJLY00,'0') = '0' then  
    IF (V_JGLX00 = 'N') OR (V_JGLX00 = 'C') THEN
    BEGIN
        IF TO_NUMBER(:NEW.JYJG00) > TO_NUMBER(:NEW.CKSX00) THEN
            :NEW.JGZCBZ := V_SXTS00;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN    
            :NEW.JGZCBZ := NULL;
    END;
    
    BEGIN
        IF TO_NUMBER(:NEW.JYJG00) < TO_NUMBER(:NEW.CKXX00) THEN
            :NEW.JGZCBZ := V_XXTS00;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            :NEW.JGZCBZ := NULL;
    END;    
    BEGIN
       IF (TO_NUMBER(:NEW.JYJG00) > TO_NUMBER(:NEW.JJZSX0)) THEN
            :NEW.SFCJJZ := 'Y';
            :NEW.JGZCBZ :='↑↑';
        elsif (TO_NUMBER(:NEW.JYJG00) < TO_NUMBER(:NEW.JJZXX0)) then
            :NEW.SFCJJZ := 'Y';
            :NEW.JGZCBZ :='↓↓';
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            :NEW.SFCJJZ := 'N';
    END;        
    IF INSTR(:NEW.JYJG00,'>') = 1 THEN
        IF INSTR(:NEW.JYJG00,'>=') = 1 THEN 
            V_JYJG00 := SUBSTR(:NEW.JYJG00,3,LENGTH(:NEW.JYJG00));
        ELSE
            V_JYJG00 := SUBSTR(:NEW.JYJG00,2,LENGTH(:NEW.JYJG00));
        END IF;
        BEGIN
            IF TO_NUMBER(V_JYJG00)+0.0001 > TO_NUMBER(:NEW.CKSX00) THEN
                :NEW.JGZCBZ := V_SXTS00;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                :NEW.JGZCBZ := NULL;
        END;  
        BEGIN
            IF TO_NUMBER(V_JYJG00)+0.0001 > TO_NUMBER(:NEW.JJZSX0) THEN
                :NEW.SFCJJZ := 'Y';
              :NEW.JGZCBZ :='↑↑';
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                :NEW.SFCJJZ := 'N';
        END;                
    END IF;
    IF INSTR(:NEW.JYJG00,'<') = 1 THEN
        IF INSTR(:NEW.JYJG00,'<=') = 1 THEN 
            V_JYJG00 := SUBSTR(:NEW.JYJG00,3,LENGTH(:NEW.JYJG00));
        ELSE
            V_JYJG00 := SUBSTR(:NEW.JYJG00,2,LENGTH(:NEW.JYJG00));
        END IF;
        BEGIN
            IF TO_NUMBER(V_JYJG00)-0.0001 < TO_NUMBER(:NEW.CKXX00) THEN
                :NEW.JGZCBZ := V_XXTS00;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                :NEW.JGZCBZ := NULL;
        END; 
        BEGIN
            IF TO_NUMBER(V_JYJG00)-0.0001 > TO_NUMBER(:NEW.JJZXX0) THEN
                :NEW.SFCJJZ := 'Y';
                :NEW.JGZCBZ :='↓↓';
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                :NEW.SFCJJZ := 'N';
        END;                
    END IF;     
    END IF;
  
    IF (V_JGLX00 = 'L') OR (V_JGLX00 = 'D') OR (V_JGLX00 = 'T') THEN
    IF instr(TRIM('|'||:NEW.CKFW00)||'|','|'||:NEW.JYJG00||'|',1) =0 THEN
        :NEW.JGZCBZ := V_SXTS00;
    END IF;
    IF (V_YQZ000<>'') and (V_YQZ000 is not null ) and (instr(TRIM('|'||V_YQZ000)||'|','|'||:NEW.JYJG00||'|',1) =0) THEN
        :NEW.SFCYQZ := 'Y';
    END IF;
    END IF;
  end if;
    
EXCEPTION
    WHEN E_MODIFY THEN
        RAISE_APPLICATION_ERROR(-20005,'该标本非您排样，不能修改结果!*');
    WHEN OTHERS THEN
        RETURN;
END;
/