CREATE OR REPLACE TRIGGER TR_BM_YD0000_BFUPDDET
 BEFORE 
 INSERT OR DELETE OR UPDATE
 ON BM_YD0000
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
DECLARE
  v_YYGXH0    XT_XTCS00.VALUE0%TYPE;
  V_ZJYPLS    XT_XTCS00.VALUE0%TYPE; --是否增加药品隶属
  V_PDDW00    YF_YPKCXX.PDDW00%TYPE;
  V_YPMCLB    BM_YPBM00.YPMCLB%TYPE;
  V_YPMCNR    XT_XTCS00.VALUE0%TYPE;
  V_YFDJGL    XT_XTCS00.VALUE0%TYPE;
  v_YDSFDDWH  XT_XTCS00.VALUE0%TYPE;
  YK_YF_LSJSFDLGL XT_XTCS00.VALUE0%TYPE;

  V_COUNTER   NUMBER;      --计数
  v_EXIST0    NUMBER(1);
  E_NoEdit    EXCEPTION;
  E_NoDelete  EXCEPTION;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- JETHUA      2003.04.25  通用名更改触发修改别名
-- JETHUA      2003.04.29  如通用名在别名中已存在则不重复增加
-- JETHUA      2004.06.30  同时更新药品库存信息表的零售单价
-- JETHUA      2004.08.25  增加是否政府定价维护
-- JETHUA      2005.01.31  铁路医院，启用药品不允许修改关键信息
-- JETHUA      2005.03.08  增加功能，药品隶属增加库存
-- JETHUA      2005.04.18  闽侯二院，启用药品不允许修改关键信息
-- JETHUA      2005.09.13  允许修改Z2J000和M2J000，但要同步更新YF_YFKCXX
-- CSF         2006.03.21  龙岩市漳平市医院,启用药品不允许修改关键信息
-- csf         2006.04.02  老年医院药典维护中直接增加“药品别名”
-- JETHUA      2006.04.13  同步更新YF_YFKCXX 避免表中无数据而报错
-- CSF         2006.05.10  老年医院默认隶属处理
--                         西药默认隶属为中心药房和西药房,中成药和中药隶属默认为中药房
-- JETHUA      2006.07.01  导入日期通过触发器实现
-- YANGH       2007.09.04  在参数 YK_YPMCZDNR控制下写BM_YPBM00.YPMCLB(0->1,1->0)
-- YANGH       2007.09.18  老年医院要求修改K2J000以后，要同步修改到YF_YPKCXX表的ZHL000下
-- CSF         2008.01.25   BUG：药库入库更新了yf_ypkcxx的价格，现改成只有单价格下才更新
-- CSF         2008.07.14   老年医院要求“片剂”的取整方式新增时候默认为"不取整"
-- CSF         2008.10.08   若更新k2j000必须同时更新yf_ypkcxx的zhl000
-- CSF         2009.03.10   老年医院步修改到YF_YPKCXX表的转化率时候，同时同步fydw00、f2j000
-- CSF         2010.03.01   将生成药典后将YPTYM0字段插入BM_YPBM00,原来只有老年医院这样处理，现有改成所有医院都这样处理。
-- CSF         2011.11.30   只有药房的数量为0时候或者药房、药库都单价格的形式下才进行更新药房的单价。 YF-20111212-002  
-- CSF         2012.02.10   老年医院，药典维护时候，自动隶属药房，在本处方器处理，也得插入购进价 YF-20120207-003
-- zhangwz     2012.03.22   QZFS00的写入有特别处理，新增MZQZFS写入也和它一起处理 by YK-20120320-001 。
-- laijg       2012.07.25   新增修改的时候写入zjjldw,ZJJL2J,k2zjjl  by YK-20120625-001 
-- ---------   ----------  -------------------------------------------
BEGIN
  --//医院个性化:0普通1罗源2闽侯二院3七院4省二院5建阳;6南安;7泉三;8平和;9铁路
    --取出医院个性化参数
  BEGIN
    SELECT VALUE0 INTO V_YFDJGL FROM XT_XTCS00 WHERE NAME00='YF_SFDLSJGL';
  EXCEPTION
    WHEN OTHERS THEN
      V_YFDJGL:='N';
  END;
  BEGIN
    SELECT VALUE0 INTO YK_YF_LSJSFDLGL FROM XT_XTCS00 WHERE NAME00='YK_YF_LSJSFDLGL';
  EXCEPTION
    WHEN OTHERS THEN
      YK_YF_LSJSFDLGL:='N';
  END;

  BEGIN
    SELECT Trim(VALUE0) INTO v_YYGXH0 FROM XT_XTCS00 WHERE NAME00='YK_YYGXH';
  EXCEPTION
    WHEN OTHERS THEN
      v_YYGXH0:='0';
  END;
  --取出药品增加隶属
  BEGIN
    SELECT VALUE0 INTO V_ZJYPLS FROM XT_XTCS00 WHERE NAME00='YK_SFQYLSYP';
  EXCEPTION
    WHEN OTHERS THEN
      V_ZJYPLS:='N';
  END;

  BEGIN
    SELECT VALUE0 INTO V_YPMCNR FROM XT_XTCS00 WHERE NAME00='YK_YPMCZDNR';
  EXCEPTION
    WHEN OTHERS THEN
      V_YPMCNR:='0';
  END;
  IF V_YPMCNR='0' THEN
    V_YPMCLB:= '1';
  ELSE
    V_YPMCLB:= '0';
  END IF;
  --药典是否单独维护 
    BEGIN
    SELECT Trim(VALUE0) INTO v_YDSFDDWH FROM XT_XTCS00 WHERE NAME00='YK_YDSFDDWH';
  EXCEPTION
    WHEN OTHERS THEN
      v_YDSFDDWH:='N';
  END;
  
  

  IF INSERTING THEN
    --没有隶属药品，增加药品时直接插入隶属表
    IF V_ZJYPLS='N' THEN
      INSERT INTO YK_YPKCXX(YPNM00,YKBMBH,LSJ000) VALUES(:NEW.YPNM00,:NEW.YKBMBH,:NEW.LSJ000);
    END IF;
    --老年医院药典维护中直接增加“药品别名”
    IF (v_YYGXH0='15') and   (:NEW.YPTYM0 IS NOT NULL) THEN
      DELETE BM_YPBM00 WHERE YPNM00=:NEW.YPNM00 ;
      INSERT INTO BM_YPBM00 (YPNM00,BMMC00,PYSM00,WBSM00,YPMCLB
      )VALUES (:NEW.YPNM00,:NEW.YPTYM0,SF_YJ_GETPYSM(:NEW.YPTYM0),SF_YJ_GETWBSM(:NEW.YPTYM0),V_YPMCLB);
    ELSIF  (:NEW.YPTYM0 IS NOT NULL) AND (:NEW.YPTYM0<>:NEW.YPMC00) THEN
      DELETE BM_YPBM00 WHERE YPNM00=:NEW.YPNM00 AND BMMC00=:NEW.YPTYM0;
      INSERT INTO BM_YPBM00 (YPNM00,BMMC00,PYSM00,WBSM00,YPMCLB
      )VALUES (:NEW.YPNM00,:NEW.YPTYM0,SF_YJ_GETPYSM(:NEW.YPTYM0),SF_YJ_GETWBSM(:NEW.YPTYM0),V_YPMCLB);    
    END IF;
    --老年医院要求“片剂”的取整方式新增时候默认为"不取整"
    if v_yygxh0='15' then
      --判断是否片剂
      select count(*) into v_counter from bm_ypjx00 where lbbh00=:new.lbbh00 and jxbh00=:new.jxbh00 and jxmc00 like '%片剂%';
      if v_counter>0 then
        :new.qzfs00:=3;
        :new.mzqzfs:=3;
      end if;
    end if;
    --老年医院默认隶属处理 西药默认隶属为中心药房和西药房,中成药和中药隶属默认为中药房
    --修改为：老年医院默认隶属处理 西药、中成药默认隶属为中心药房、西药房，草药默认到中药房，农本方默认农本方药房。
    IF  v_YYGXH0='15' THEN
      IF :NEW.LBBH00 in('0','1') THEN
        INSERT INTO YF_YPKCXX(YFBMBH,YPNM00,LSDJ00,PDDW00,ZHL000,FYDW00,F2J000,GJDJ00)
        Values(497,:NEW.YPNM00,:NEW.LSJ000,:NEW.KCDW00,:NEW.K2J000,:NEW.MZFYDW,:NEW.M2J000,:NEW.GJJ000);--中心药房
        INSERT INTO YF_YPKCXX(YFBMBH,YPNM00,LSDJ00,PDDW00,ZHL000,FYDW00,F2J000,GJDJ00)
        Values(495,:NEW.YPNM00,:NEW.LSJ000,:NEW.KCDW00,:NEW.K2J000,:NEW.MZFYDW,:NEW.M2J000,:NEW.GJJ000);--西药房
      ELSIF :NEW.LBBH00 ='2' and :NEW.JXBH00<>'13' THEN
        INSERT INTO YF_YPKCXX(YFBMBH,YPNM00,LSDJ00,PDDW00,ZHL000,FYDW00,F2J000,GJDJ00)
        Values(496,:NEW.YPNM00,:NEW.LSJ000,:NEW.KCDW00,:NEW.K2J000,:NEW.MZFYDW,:NEW.M2J000,:NEW.GJJ000);--中药房
      ELSE
        INSERT INTO YF_YPKCXX(YFBMBH,YPNM00,LSDJ00,PDDW00,ZHL000,FYDW00,F2J000,GJDJ00)
        Values(756,:NEW.YPNM00,:NEW.LSJ000,:NEW.KCDW00,:NEW.K2J000,:NEW.MZFYDW,:NEW.M2J000,:NEW.GJJ000);--颗粒剂药房
      END IF;
    END IF;
    :NEW.DRRQ00:=TO_CHAR(SYSDATE,'YYYYMMDD');
    --写入zjjldw,ZJJL2J,k2zjjl 
   IF v_YDSFDDWH<>'Y' then        --药品目录维护新增时药典非单独维护 
     :NEW.ZJJLDW:= :NEW.ZYFYDW;
     :NEW.ZJJL2J:= :NEW.Z2J000;
     :NEW.K2ZJJL:= ROUND(:NEW.K2J000/:NEW.Z2J000,3);
   end if; 
    
  ELSIF UPDATING THEN
    SELECT COUNT(1) INTO V_COUNTER FROM DUAL
      WHERE EXISTS(SELECT 1 FROM YK_YPRKMX WHERE YPNM00=:OLD.YPNM00);

    IF V_COUNTER=1 THEN  --保护已经使用药品的转换率与内码不被修改
      IF ((:OLD.K2J000<>:NEW.K2J000) OR (:OLD.YPNM00<>:NEW.YPNM00) ) THEN
         RAISE E_NoEdit;
      END IF;
    END IF;
    --2闽侯二院,9铁路，16龙岩市漳平市医院 要求启用药品不允许修改以下信息
    IF NOT (:NEW.QYRQ00 IS NULL) AND (v_YYGXH0='2' or v_YYGXH0='9' or v_YYGXH0='16') THEN
      IF (:OLD.YPGG00<>:NEW.YPGG00) OR
         (:OLD.KCDW00<>:NEW.KCDW00) OR
         (:OLD.JLDW00<>:NEW.JLDW00) OR
         (:OLD.ZYFYDW<>:NEW.ZYFYDW) OR
         (:OLD.MZFYDW<>:NEW.MZFYDW) OR
         (:OLD.Z2J000<>:NEW.Z2J000) OR
         (:OLD.M2J000<>:NEW.M2J000) THEN
         RAISE E_NoEdit;
      END IF;
    END IF;


    IF :OLD.YPTYM0<>:NEW.YPTYM0 THEN
       DELETE BM_YPBM00 WHERE YPNM00=:NEW.YPNM00 AND TRIM(BMMC00)=TRIM(:OLD.YPTYM0);
       IF :NEW.YPMC00<>:NEW.YPTYM0 then
          INSERT INTO BM_YPBM00 (YPNM00,BMMC00,PYSM00,WBSM00,YPMCLB)
          VALUES (:NEW.YPNM00,:NEW.YPTYM0,SF_YJ_GETPYSM(:NEW.YPTYM0),SF_YJ_GETWBSM(:NEW.YPTYM0),V_YPMCLB);
       END IF;
    END IF;
    --------同时更新药品库存信息表的零售单价－不应该更新，更新如果有差价应该进行调价，否则账不平
    --IF (v_YYGXH0<>'13') and (V_YFDJGL='N')  THEN
    
    if (:old.LSJ000<>:new.LSJ000) or (:old.GJJ000<>:new.GJJ000) then
      BEGIN
        UPDATE YF_YPKCXX SET LSDJ00=:NEW.LSJ000,GJDJ00=:NEW.GJJ000 WHERE YPNM00=:NEW.YPNM00 and SJKCSL=0 and YKKCSL=0;
      EXCEPTION
        WHEN OTHERS THEN
           raise E_NoEdit;
       END;
    end if;
    
    if (YK_YF_LSJSFDLGL='N') and (V_YFDJGL='N') and ((:old.LSJ000<>:new.LSJ000) or (:old.GJJ000<>:new.GJJ000)) then
      BEGIN
        UPDATE YF_YPKCXX SET LSDJ00=:NEW.LSJ000,GJDJ00=:NEW.GJJ000 WHERE YPNM00=:NEW.YPNM00 ;
      EXCEPTION
        WHEN OTHERS THEN
           raise E_NoEdit;
       END;    
    end if;
    IF :OLD.PDXH00<>:NEW.PDXH00 THEN
      UPDATE YK_YPKCXX SET PDXH00=:NEW.PDXH00 WHERE YPNM00=:NEW.YPNM00;
    END IF;
    --最高定价维护
    IF :NEW.ZFZGDJ>0 THEN
      :NEW.SFZFDJ:='Y';
    ELSE
      :NEW.SFZFDJ:='N';
    END IF;
    --老年允许修改K2J000，但要同步更新YF_YFKCXX --单位相同，则改
    IF  (:OLD.K2J000<>:NEW.K2J000) THEN
      UPDATE YF_YPKCXX SET ZHL000=:NEW.K2J000
       WHERE YPNM00=:NEW.YPNM00 AND PDDW00=:NEW.KCDW00 ;
    END IF;
    --允许修改Z2J000和M2J000，但要同步更新YF_YFKCXX --单位相同，则改
    IF (:OLD.M2J000<>:NEW.M2J000) THEN
      UPDATE YF_YPKCXX SET ZHL000=:NEW.M2J000
       WHERE YPNM00=:NEW.YPNM00 AND PDDW00=:NEW.MZFYDW ;
    END IF;

    IF (:OLD.Z2J000<>:NEW.Z2J000) THEN
      UPDATE YF_YPKCXX SET ZHL000=:NEW.Z2J000
       WHERE YPNM00=:NEW.YPNM00 AND PDDW00=:NEW.ZYFYDW;
    END IF;
    
       --写入zjjldw,ZJJL2J,k2zjjl      
    IF   (:OLD.Z2J000<>:NEW.Z2J000) OR (:OLD.K2J000<>:NEW.K2J000) or (:OLD.ZYFYDW<>:NEW.ZYFYDW)  THEN
     :NEW.ZJJLDW:= :NEW.ZYFYDW;
     :NEW.ZJJL2J:= :NEW.Z2J000;
     :NEW.K2ZJJL:= ROUND(:NEW.K2J000/:NEW.Z2J000,3); 
    END IF; 
    
  ELSE  --DELETING
    SELECT COUNT(1) INTO V_COUNTER FROM DUAL
      WHERE EXISTS(SELECT 1 FROM YK_YPMXZ0 WHERE YPNM00=:OLD.YPNM00)
         or EXISTS(SELECT 1 FROM YF_YPMXZ0 WHERE YPNM00=:OLD.YPNM00);
   --IF NOT (:OLD.QYRQ00 IS NULL) THEN
    IF V_COUNTER>0 THEN
      RAISE E_NoDelete;
    END IF;

    --没有隶属药品，删除药品时直接删除隶属表
    IF V_ZJYPLS='N' THEN
      DELETE YK_YPKCXX WHERE YPNM00=:OLD.YPNM00;
    END IF;
  END IF;
EXCEPTION
  WHEN E_NoEdit THEN
    RAISE_APPLICATION_ERROR(-20001, '该记录已经被使用,关键信息不能被修改!*');
  WHEN E_NoDelete THEN
    RAISE_APPLICATION_ERROR(-20002, '该记录已经被使用,不能被删除!*');
END;
/