-- Start of DDL script for TR_SF_BM_BRXXB0_INSERT
-- Generated 15-十一月-10  9:15:58 am
-- from fjlnyy-SD_HOSPITAL:1

-- Trigger TR_SF_BM_BRXXB0_INSERT

CREATE OR REPLACE TRIGGER tr_sf_bm_brxxb0_insert
BEFORE INSERT  OR UPDATE
  OF fbbh00,
     ybbrlb,
     brzjbh,
     yblb00,
     sfyd00
ON bm_brxxb0
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
   ls_BRID00  BM_BRXXB0.BRID00%TYPE;
   Vlnzdyd    char(1);                --老年自动优待
   ls_SFLNYD  char(1);                --是否老年优待
   Vzdydfb    varchar2(50);           --自动优待费别
   Vcounter   number(5);
   Ecustom   exception;               --错误变量
   Verrmsg   varchar2(255);           --错误提示变量
   ls_JKZDQF  varchar2(20);
   ls_LXBR00  char(1);
   ls_YBZXLB  IC_YBBRLB.YBZXLB%TYPE;
begin
 if inserting then
   ls_BRID00 := 0;
   select  NVL(max(BRID00),0)  into  ls_BRID00  from  BM_BRXXB0  where  BRBLH0 = :NEW.BRBLH0 and :NEW.BRBLH0 <> '0';

   IF  ls_BRID00 <> 0   THEN   --该病历号的病人已有记录
       if  ls_BRID00 <> :NEW.BRID00  then
           Verrmsg:= '该病历号已被别的病人所使用!'||'!*';
           RAISE  Ecustom;
       end if;
   END IF;
   :NEW.PYSM00 := SF_YJ_GETPYSM(:NEW.BRXM00);
   :NEW.WBSM00 := SF_YJ_GETWBSM(:NEW.BRXM00);
   IF :NEW.TYDWID IS NOT NULL THEN
     insert into BM_BRTYDW (LSH000,BRID00,TYDWID) values (SQ_BM_BRTYDW_LSH000.nextval,:NEW.BRID00,:NEW.TYDWID);
   END IF;
   if :new.SFYD00='0'then
      ls_SFLNYD:='N';
      --两费病人自动优待
      select ';'||nvl(max(trim(value0)),'0,0')||';' into Vzdydfb from XT_XTCS00 where name00='SF_ZDYDFB';
      if Vzdydfb<>';0,0;' then
         select count(*) into Vcounter from IC_YBBRLB b
         where :new.FBBH00=b.FBBH00 and :new.YBLB00=b.YBLB00
           and instrb(Vzdydfb,';'||to_char(:new.FBBH00)||','||trim(b.YBZXLB)||';')>0;
         if Vcounter>0 then
            :new.SFYD00:='1';
            ls_SFLNYD:='Y';
         end if;
      end if;
      if ls_SFLNYD<>'Y' then
         --要求70岁以上的老人,两费病人自动优待,免除挂号费
         select substrb(nvl(max(trim(value0)),'N'),1,1) into Vlnzdyd from XT_XTCS00 where name00='SF_LNZDYD';
         if Vlnzdyd='Y' then
            if (sysdate-to_date(:new.BRCSRQ,'YYYYMMDD'))>70*365 and
               (substrb(:new.BRZJBH,7,6)=substrb(:new.BRCSRQ,3,6) or substrb(:new.BRZJBH,7,8)=:new.BRCSRQ) then
               :new.SFYD00:='1';
            end if;
         end if;
      end if;
   end if;
   --变更身份证号
 elsif updating and nvl(:old.BRZJBH,' ')<>nvl(:new.BRZJBH,' ') then
      --要求70岁以上的老人,两费病人自动优待,免除挂号费
      select substrb(nvl(max(trim(value0)),'N'),1,1) into Vlnzdyd from XT_XTCS00 where name00='SF_LNZDYD';
      if Vlnzdyd='Y' then
         if (sysdate-to_date(:old.BRCSRQ,'YYYYMMDD'))>70*365 and
            (substrb(:old.BRZJBH,7,6)=substrb(:old.BRCSRQ,3,6) or substrb(:old.BRZJBH,7,8)=:old.BRCSRQ) then
             Verrmsg:= '70岁以上优待病人，不允许修改身份证号!*';
             RAISE  Ecustom;
         end if;
         if (sysdate-to_date(:new.BRCSRQ,'YYYYMMDD'))>70*365 and
            (substrb(:new.BRZJBH,7,6)=substrb(:new.BRCSRQ,3,6) or substrb(:new.BRZJBH,7,8)=:new.BRCSRQ) then
            :new.SFYD00:='1';
         end if;
      end if;
 end if;
 --变更病人身份
 if updating and (:old.FBBH00<>:new.FBBH00 or :new.YBLB00<>:old.YBLB00 or :new.YBBRLB<>:old.YBBRLB or :old.SFYD00<>:new.SFYD00) then
      select count(*) into Vcounter from SF_BRZHXX where BRID00=:new.BRID00;
      if Vcounter>0 then
         --建卡时自动允许欠费方式和金额，格式如 3,10，用逗号格式 第一位表示方式0 不允许 1 所有病人2医保病人 3离休病人 4优待病人 逗号后表示金额
         select nvl(max(trim(VALUE0)),'0,0') into ls_JKZDQF from XT_XTCS00 where NAME00='SF_JKZDQF';
         if substrb(ls_JKZDQF,1,1)='2' then
            if :new.FBBH00=3 then
               update SF_BRZHXX set yxqfje=to_number(substrb(ls_JKZDQF,3,3)) where BRID00=:new.BRID00;
            else
               update SF_BRZHXX set yxqfje=0 where BRID00=:new.BRID00;
            end if;
         end if;
         if substrb(ls_JKZDQF,1,1)='3' then
           if :new.FBBH00<>3 then
               update SF_BRZHXX set yxqfje=0 where BRID00=:new.BRID00;
           else
              select b.YBZXLB into ls_YBZXLB
                  FROM IC_YBBRLB b,BM_YBBRLB c where b.FBBH00=:new.FBBH00 and b.YBLB00=:new.YBLB00 and
                       c.YBZXLB=b.YBZXLB and c.YBBRLB=:new.YBBRLB and c.FBBH00=:new.FBBH00;
              select decode(count(*),0,'0','1') into ls_LXBR00 from BM_LXBRDW where YBZXLB=ls_YBZXLB and DWMC00=:new.TYDWMC;
              if ls_LXBR00='1' then
               update SF_BRZHXX set yxqfje=to_number(substrb(ls_JKZDQF,3,3)) where BRID00=:new.BRID00;
              else
               update SF_BRZHXX set yxqfje=0 where BRID00=:new.BRID00;
              end if;
           end if;
         end if;
         if substrb(ls_JKZDQF,1,1)='4' then
             if :new.SFYD00='1' and :new.FBBH00=3 then
               update SF_BRZHXX set yxqfje=to_number(substrb(ls_JKZDQF,3,3)) where BRID00=:new.BRID00;
             else
               update SF_BRZHXX set yxqfje=0 where BRID00=:new.BRID00;
             end if;
         end if;
      end if;
 end if;
exception
     WHEN Ecustom THEN
	  RAISE_APPLICATION_ERROR(-20911,Verrmsg);
          ROLLBACK;
     WHEN OTHERS THEN
	  RAISE_APPLICATION_ERROR(-20912,substr(nvl(sqlerrm,'出错原因不详'),1,40)||'，请记录此信息并和系统管理员联系！'||'!*');
	  ROLLBACK;
end;
/