--PROMPT 118.TR_JY_YW0000_UPDATE_JGZT00_TJ  
CREATE OR REPLACE TRIGGER TR_JY_YW0000_UPDATE_JGZT00_TJ
AFTER UPDATE OF JGZT00,JGSFWZ
ON JY_YW0000
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (NEW.JGZT00 = '6' OR NEW.JGZT00 = '4' or NEW.JGSFWZ='Y')
DECLARE
  LD_COUNT0  NUMBER;
  LD_GHH000  SF_BRXXB0.GHH000%TYPE;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- jinfl      2013.09.11  插入中间表时ZLXMID取YJ_YW0000表的值  JKTJ-20130814-001
-- jinfl      2013.12.19  单位改为取报告项目字典表
-- chenyw     2015.04.23  根据JGSFWZ从'N'改成'Y'的时候也要插入到体检表中  for LIS-20150304-006
Begin
  IF :OLD.MZZYBZ <> '0' THEN 
    RETURN;
  END IF;
  
  IF (:OLD.JGZT00 = '6' OR :OLD.JGZT00 = '8') AND (:NEW.JGZT00 = '4') THEN
    DELETE FROM TJ_RECIEVE_REPORT WHERE PYRQ00=:OLD.PYRQ00 AND YBH000=:OLD.YBH000;
    RETURN;
  END IF;
  
  IF ( :OLD.JGZT00 = '8' OR :OLD.JGZT00 = '0' OR :OLD.JGZT00 = '1' OR :OLD.JGZT00 = '2'
     OR :OLD.JGZT00 = '3'  OR :OLD.JGZT00 = '7') THEN
    RETURN;
  END IF;
  
  if (:NEW.JGSFWZ='N') then
      RETURN;
  end if;
  
  SELECT COUNT(*) INTO LD_COUNT0 FROM XT_XTCS00 WHERE NAME00='TJ_JYJGFROMJYXT' AND VALUE0='Y';
  IF LD_COUNT0 = 0 THEN
    RETURN;
  END IF;
  
  SELECT COUNT(*) INTO LD_COUNT0 FROM TJ_BRXXB0 WHERE GHID00=:OLD.ZYGHID;
  IF LD_COUNT0 = 0 THEN
    RETURN;
  END IF;  
   
  SELECT GHH000 INTO LD_GHH000 FROM SF_BRXXB0 WHERE GHID00=:OLD.ZYGHID;
  DELETE FROM TJ_RECIEVE_REPORT WHERE PYRQ00=:OLD.PYRQ00 AND YBH000=:OLD.YBH000 AND ZLXMID=:OLD.ZLXMID;
  INSERT INTO TJ_RECIEVE_REPORT(
  GHH000    ,
  GHID00    ,  
  YBH000    ,  
  TMH000    ,  
  JCXMID    ,  
  JCXMMC    ,  
  XSSX00    ,  
  CDZ000    ,  
  XMDW00    ,      
  CDMS00    ,  
  JCYS00    ,  
  JCYSXM    ,  
  JCRQ00    ,  
  XXZ000    ,  
  SXZ000    ,  
  CKZ000    ,     
  JGSM00    ,  
  ZLXMID    ,
  SJLX00    ,  
  PYRQ00    ,
  BGSJ00    ,
  YJFZH0    ,
  JKBH00
  ) 
  select LD_GHH000,:OLD.ZYGHID,A.YBH000,:OLD.TXM000,A.YQDH00||'_'||A.BGXMID,ZWMC00,DYSX00,JYJG00,B.DW0000,JGBZ00,nvl(SHR000,'0'),
         nvl((select ZWXM00 from BM_YGBM00 where YGBH00=A.SHR000),:OLD.BGJYXM) ZWXM00,BGRQ00,CKXX00,CKSX00,CKFW00,JGZCBZ,
         (select ZLXMID from YJ_YW0000 where YJDJH0=:NEW.YJDJH0 and rownum=1),DECODE(B.JGLX00,'N','N','C','N','C'),
         A.PYRQ00,A.BGSJ00,NVL(:NEW.YJFZH0,0),B.JKBH00
  from JY_JYJG00 A,JY_BGXMZD B--,BM_YGBM00 C
  where A.BGXMID=B.BGXMID
    --AND A.SHR000=C.YGBH00
    and A.PYRQ00=:OLD.PYRQ00
    and A.YBH000=:OLD.YBH000
    and A.ZLXMID=:OLD.ZLXMID;
EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20020, NVL(SQLERRM, '原因不明出错！*'));  
End;
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%