create or replace trigger TR_BQ_YPYZMX_BUPD    --皮试医嘱触发器
before insert OR UPDATE of PSTGBZ,YPNM00,LYL000,JLDW00,J2J000,Z2J000,ZYFYDW
on BQ_YPYZMX
REFERENCING NEW AS NEW OLD AS OLD
for each row
declare
    VBRID00  ZY_BRXXB0.BRID00%TYPE;
    VDQBQ00  ZY_BRXXB0.DQBQ00%TYPE;
    VCZYXM0  BM_YGBM00.ZWXM00%TYPE;
    vZJJLDW  BM_YD0000.ZJJLDW%TYPE;
    vJLDW00  BM_YD0000.JLDW00%TYPE;
    vZYFYDW  BM_YD0000.ZYFYDW%TYPE;
    vZJJL2J  number(13,3);
    vK2j000  number(13,3);
    vKCDW00  BM_YD0000.KCDW00%TYPE;
    vZ2J000  BM_YD0000.Z2J000%TYPE;
    Vzjzycf  BM_YD0000.zjzycf%TYPE;
    Vzyts00  number;
    VTEMP    CHAR(3);
    counter  NUMBER;
    Verrmsg  varchar2(200);
    Ecustom  exception;
begin
-- MODifICATION HISTORY
-- Person      Date        Comments
-- qks         2014.04.03  增加限制:临时医嘱领药量不能为小数
-- zhaoxz      2017.05.15  修改单位长度，与BM_YD0000表一致 BQHS9.0-20170515-003
--省三院       2018.12.05   添加条件，中药为可拆时，不提示“次量*贴数不等于总量” 
   select ZJJLDW,ZJJL2J,JLDW00,Z2J000,ZYFYDW,KCDW00,K2j000 into vZJJLDW,vZJJL2J,vJLDW00,vZ2J000,vZYFYDW,vKCDW00,vK2j000
     from BM_YD0000 where YPNM00=:new.YPNM00;
   if not(:new.jldw00=vZJJLDW or :new.jldw00=vJLDW00 or :new.jldw00=vZYFYDW or :new.jldw00=vKCDW00) then
          Verrmsg:=:new.YPMC00||'次量单位不正确';
          RAISE Ecustom;
   end if;
   if not(:new.ZYFYDW=vZJJLDW or :new.ZYFYDW=vJLDW00 or :new.ZYFYDW=vZYFYDW or :new.ZYFYDW=vKCDW00) then
          Verrmsg:=:new.YPMC00||'总量单位不正确';
          RAISE Ecustom;
   end if;
   if :new.jldw00=vJLDW00 and (:new.J2J000<>1) then
      insert into YF_MZCFMX_MONITOR(cfid00,cflsh0,ypnm00,ypmc00,ypgg00,csl000,jldw00,f2j000,zhl000,cfdw00,lsdj00,flag00 )
                  values (:new.yzmxid,:new.yzid00,:new.ypnm00,:new.ypmc00,:new.ypgg00,:new.mcsl00,:new.jldw00,:new.Z2J000,:new.J2J000,:new.ZYFYDW,0,'2');
      :new.J2J000:=1;
   elsif :new.jldw00=vZJJLDW and (:new.J2J000<>vZJJL2J) then
      insert into YF_MZCFMX_MONITOR(cfid00,cflsh0,ypnm00,ypmc00,ypgg00,csl000,jldw00,f2j000,zhl000,cfdw00,lsdj00,flag00 )
                  values (:new.yzmxid,:new.yzid00,:new.ypnm00,:new.ypmc00,:new.ypgg00,:new.mcsl00,:new.jldw00,:new.Z2J000,:new.J2J000,:new.ZYFYDW,0,'2');
      :new.J2J000:=vZJJL2J;
   elsif :new.jldw00=vZYFYDW and (:new.J2J000<>vZ2J000) then
      insert into YF_MZCFMX_MONITOR(cfid00,cflsh0,ypnm00,ypmc00,ypgg00,csl000,jldw00,f2j000,zhl000,cfdw00,lsdj00,flag00 )
                  values (:new.yzmxid,:new.yzid00,:new.ypnm00,:new.ypmc00,:new.ypgg00,:new.mcsl00,:new.jldw00,:new.Z2J000,:new.J2J000,:new.ZYFYDW,0,'2');
      :new.J2J000:=vZ2J000;
   end if;

   if :new.ZYFYDW=vJLDW00 and (:new.Z2J000<>1) then
      insert into YF_MZCFMX_MONITOR(cfid00,cflsh0,ypnm00,ypmc00,ypgg00,csl000,jldw00,f2j000,zhl000,cfdw00,lsdj00,flag00 )
                  values (:new.yzmxid,:new.yzid00,:new.ypnm00,:new.ypmc00,:new.ypgg00,:new.mcsl00,:new.jldw00,:new.Z2J000,:new.J2J000,:new.ZYFYDW,0,'3');
      :new.Z2J000:=1;
   elsif :new.ZYFYDW=vZJJLDW and (:new.Z2J000<>vZJJL2J) then
      insert into YF_MZCFMX_MONITOR(cfid00,cflsh0,ypnm00,ypmc00,ypgg00,csl000,jldw00,f2j000,zhl000,cfdw00,lsdj00,flag00 )
                  values (:new.yzmxid,:new.yzid00,:new.ypnm00,:new.ypmc00,:new.ypgg00,:new.mcsl00,:new.jldw00,:new.Z2J000,:new.J2J000,:new.ZYFYDW,0,'3');
      :new.Z2J000:=vZJJL2J;
   elsif :new.ZYFYDW=vZYFYDW and (:new.Z2J000<>vZ2J000) then
      insert into YF_MZCFMX_MONITOR(cfid00,cflsh0,ypnm00,ypmc00,ypgg00,csl000,jldw00,f2j000,zhl000,cfdw00,lsdj00,flag00 )
                  values (:new.yzmxid,:new.yzid00,:new.ypnm00,:new.ypmc00,:new.ypgg00,:new.mcsl00,:new.jldw00,:new.Z2J000,:new.J2J000,:new.ZYFYDW,0,'3');
      :new.Z2J000:=vZ2J000;
   elsif :new.ZYFYDW=vKCDW00 and (:new.Z2J000<>vK2J000) then
      insert into YF_MZCFMX_MONITOR(cfid00,cflsh0,ypnm00,ypmc00,ypgg00,csl000,jldw00,f2j000,zhl000,cfdw00,lsdj00,flag00 )
                  values (:new.yzmxid,:new.yzid00,:new.ypnm00,:new.ypmc00,:new.ypgg00,:new.mcsl00,:new.jldw00,:new.Z2J000,:new.J2J000,:new.ZYFYDW,0,'3');
      :new.Z2J000:=vK2J000;
   end if;

  if UPDATING then
     if (:new.PSTGBZ<>:old.PSTGBZ) AND ((:new.PSTGBZ='+') OR (:new.PSTGBZ='-')) then
       select BRID00,DQBQ00 into VBRID00,VDQBQ00 from ZY_BRXXB0 where ZYID00=(
              select ZYID00 from BQ_YPYZ00 where YZID00=:new.YZID00);
       select ZWXM00 into VCZYXM0 from BM_YGBM00 where YGBH00=:new.PSBGR0;
       VTEMP:=SUBSTRB(:new.YPMC00,LENGTHB(RTRIM(:new.YPMC00))-2,3);
       if (VTEMP='(+)')OR (VTEMP='(-)') then
         :new.YPMC00:=SUBSTRB(:new.YPMC00,1,LENGTHB(RTRIM(:new.YPMC00))-3)||'('||:new.PSTGBZ||')';
       ELSE
         :new.YPMC00:=:new.YPMC00||'('||:new.PSTGBZ||')';
       end if;
       select COUNT(*) into counter from BM_PSJGFZ where YPNM00=:new.YPNM00 AND ROWNUM=1;
       insert into YJ_BRGMJL(BRID00,YPNM00,YPMC00,BGKSBH,BGR000,
                             BGRXM0,JGMS00,ZCBZ00,BZ0000,
                             JLRQ00,
                             JLSJ00)
                             VALUES
                            (VBRID00,:new.YPNM00,:new.YPMC00,VDQBQ00,:new.PSBGR0,
                             VCZYXM0,:new.PSTGBZ,DECODE(:new.PSTGBZ,'-',DECODE(counter,0,'Y',1,'N'),DECODE(counter,0,'N',1,'Y')),NULL,
                             TO_CHAR(SYSDATE,'YYYYMMDD'),
                             TO_CHAR(SYSDATE,'HH24:MI:SS')
                            );
     end if;
     if :new.YPNM00<>:old.YPNM00 then
       select LBBH00 into :new.YPLBBH from BM_YD0000 where YPNM00=:new.YPNM00;
     end if;
  elsif inserting then
    if :new.YPLBBH IS NULL then
       select LBBH00 into :new.YPLBBH from BM_YD0000 where YPNM00=:new.YPNM00;
    end if;
    if :new.YPLBBH='2'and :new.LYL000>0 then--次量=总量
       select Z2J000 into vZ2J000 from BM_YD0000 where YPNM00=:new.YPNM00;
       select zjzycf into Vzjzycf FROM BM_YD0000 where YPNM00=:new.YPNM00;
       select ZQCYTS into Vzyts00 from BQ_YPYZ00 where YZID00=:new.YZID00;
       if ceil(:new.LYL000*:new.Z2J000/vZ2J000)<>ceil(:new.MCSL00*Vzyts00/vZ2j000)
         AND  Vzjzycf = '0'--添加条件，中药为不可拆时，再如此提示 2018.12.05
         then
          Verrmsg:=:new.YPMC00||'次量*贴数不等于总量,请重开或者联系分机86250155！*';
          RAISE Ecustom;
       end if;
    end if;

  end if;
  if (inserting or (updating and :new.LYL000<>:old.LYL000) ) and
     (:new.LYL000=0 and :new.YZCLLB not in ('嘱托','自备药','门诊带药') ) then
       select count(*) into counter from BQ_YPYZ00 where YZID00=:new.YZID00 and CLBZ00='1';
       if counter>0 then
          Verrmsg:='临时医嘱领药量不能为0,请重开或者联系分机86250155！*';
          RAISE Ecustom;
       end if;

  end if;
  if inserting and :new.YPLBBH='2'  then--全成分不能输代煎次数
       select count(*) into counter from BM_YD0000 where YPNM00=:new.YPNM00 and YPCZFL='2';
       if counter>0 then
          select JYCS00 into Vzyts00 from BQ_YPYZ00 where YZID00=:new.YZID00;
          if Vzyts00>0 then
             Verrmsg:='中药颗粒不能输入代煎剂数！*';
             raise Ecustom;
          end if;
       end if;
  end if;
  if (inserting or updating) and :new.YPLBBH in ('0','1') --and :new.ZYFYDW not in ('g','ml')
       and mod(:new.LYL000,1)>0 and :new.YZCLLB='正常' and :new.QZFSID in (0,1,2) then
       select count(*) into counter from BQ_YPYZ00 where YZID00=:new.YZID00 and CLBZ00='1';
       if counter>0 then
         Verrmsg:='临时医嘱领药量不能为小数！*';
         raise Ecustom;
       end if;
  end if;
exception
  when no_data_found then
    raise_application_error(-20001,substr('数据没有找到！*'||sqlerrm||Verrmsg||',',1,250));
  when Ecustom       then
    raise_application_error(-20010,substr(Verrmsg||'！*',1,250));
  when others        then
    raise_application_error(-20002,substr(nvl(sqlerrm, '原因不明出错！*'),1,250));
end;