-- Start of DDL Script for Trigger SD_HOSPITAL.TR_SF_SF_BRZHXX_UPDINS
-- Generated 2013-03-21 15:59:40 from SD_HOSPITAL@DHZYY

CREATE OR REPLACE TRIGGER tr_sf_sf_brzhxx_updins
 BEFORE
  INSERT OR UPDATE
 ON sf_brzhxx
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
declare
  Vcounter   number(5);
  Vyyid00    XT_YYXX00.YYID00%type;
  ls_JKZDQF  VARCHAR2(10);
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- jlg        2007.10.31   煤矿中心医院要求医保病人挂号及诊察费允许欠费
-- xzw        2008.07.21   医大附二医院要求泉州离休病人挂号及诊察费允许欠费
-- zhr        2008.10.31   邵武人民医院和邵武妇幼医院由于售卡/换卡都不收卡费，但要求账户冻结4元钱
-- qks        2008.12.01   沙县医院医保病人允许欠费6元
-- qks        2008.12.10   沙县医院非医保病人设置停用金额5元
-- xzw        2009.04.07   德化医院医保病人挂号及诊察费允许欠费
-- xzw        2009.04.17   医大附二要求所有病人挂号及诊察费允许欠费20
-- zhr        2009.08.24   漳浦中医院允许医保病人欠费10元
-- zhr        2009.08.24   根据参数SF_JKZDQF设置允许欠费金额
-- zhangyc    2010.09.08   增加条件inserting(SF_JKZDQF=2,3,4)MZSF-20100906-003
-- zhangyc    2010.11.05   修改医院id取值(select SF_SF_GETYYID00() YYID00 from dual) 
-- zhangyc    2012.07.11   三明市妇幼保健院(223672)非医保病人建卡时停用金额为5元 by MZSF-20120709-001
begin
  --select YYID00 into Vyyid00 from xt_yyxx00 where rownum=1 ;--福州铁路中心医院
  select SF_SF_GETYYID00() YYID00 into Vyyid00  from dual;
  
  if Vyyid00='220019' then
     if inserting or (updating and :new.yxqfje<15) then --铁路医保的病人允许欠10.00
        select count(*) into Vcounter from bm_brxxb0 a,ic_ybbrlb b
               where brid00=:new.brid00 and a.fbbh00=b.fbbh00 and b.yblb00=a.yblb00 and b.ybzxlb='Z';
        if Vcounter>0 then
           select count(*) into Vcounter from bm_brxxb0 a,ic_ybbrlb b
               where brid00=:new.brid00 and a.fbbh00=b.fbbh00 and b.yblb00=a.yblb00 and 
                     b.ybzxlb='Z' and a.ybbrlb in ('4','5','7');
           if Vcounter>0 then--铁路医保的离休病人允许欠15元
              :new.yxqfje:=15;
           else
              :new.yxqfje:=10;--铁路医保的普通病人允许欠10元
           end if;
        end if;
     end if;
  elsif Vyyid00 in ('225088','226521')then--泉州人民医院,德化医院225898,漳浦中医院--225898
     if inserting or (updating and :new.yxqfje<10) then --医保的病人允许欠10.00
        select count(*) into Vcounter from bm_brxxb0 a where brid00=:new.brid00 and a.fbbh00=3;
        if Vcounter>0 then
           :new.yxqfje:=10;
        end if;
     end if;
     
  elsif Vyyid00 in ('225785')then--彭胡
     if inserting or (updating and :new.yxqfje<6) then --医保的病人允许欠5.00
        select count(*) into Vcounter from bm_brxxb0 a where brid00=:new.brid00 and a.fbbh00=3;
        if Vcounter>0 then
           :new.yxqfje:=6;
        end if;
     end if;
  elsif Vyyid00 in ('222211')then--平潭
     if inserting or (updating and :new.yxqfje<8) then --医保的病人允许欠8.00
        select count(*) into Vcounter from bm_brxxb0 a where brid00=:new.brid00 and a.fbbh00=3;
        if Vcounter>0 then
           :new.yxqfje:=8;
        end if;
     end if;
  elsif Vyyid00 in ('227320')then--  邵武市妇幼保健院 
     if inserting or (updating and :new.yxqfje<8) then --医保的病人允许欠8.00
        select count(*) into Vcounter from bm_brxxb0 a where brid00=:new.brid00 and a.fbbh00=3;
        if Vcounter>0 then
           :new.yxqfje:=8;
        end if;
     end if;
     if :new.ybzhye<>0 then
        :new.ybzhye:=0;
     end if;
  elsif Vyyid00 in ('220034')then --煤矿中心医院
     if inserting or (updating and :new.yxqfje<10) then --医保的病人允许欠10.00
        select count(*) into Vcounter from bm_brxxb0 a where brid00=:new.brid00 and a.fbbh00=3;
        if Vcounter>0 then
           :new.yxqfje:=10;
        end if;
     end if;
  elsif Vyyid00 in ('224465')then --沙县医院
     if inserting or (updating and :new.yxqfje<6) then --医保的病人允许欠6.00
        select count(*) into Vcounter from bm_brxxb0 a where brid00=:new.brid00 and a.fbbh00=3;
        if Vcounter>0 then
           :new.yxqfje:=6;
        else
        	 :new.tyje00:=5; 
        end if;
     end if;   
  elsif Vyyid00 in ('225085')then --医大附二医院
     if inserting or (updating and :new.yxqfje<20) then --所有病人欠费20（之前要求离休的病人允许欠60.00）
        select count(*) into Vcounter from bm_brxxb0 a where brid00=:new.brid00; -- and a.fbbh00=16;
        if Vcounter>0 then
           :new.yxqfje:=20;
        end if;
     end if;
  else
     --建卡时自动允许欠费方式和金额，格式如 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)='1' then
        :new.yxqfje:=to_number(substrb(ls_JKZDQF,3,3));
     end if;
     if inserting and substrb(ls_JKZDQF,1,1)='2' then
        select count(*) into Vcounter from BM_BRXXB0 where BRID00=:new.BRID00 and FBBH00=3;
        if Vcounter>0  then
           :new.yxqfje:=to_number(substrb(ls_JKZDQF,3,3));
        end if;
     end if;
     if inserting and substrb(ls_JKZDQF,1,1)='3'  then
        if SF_SF_LXBR00(:new.BRID00)='1' then
        :new.yxqfje:=to_number(substrb(ls_JKZDQF,3,3));
        end  if;
     end if;
     if inserting and substrb(ls_JKZDQF,1,1)='4' then
        select count(*) into Vcounter from BM_BRXXB0 where BRID00=:new.BRID00 and SFYD00='1' and FBBH00=3;
        if Vcounter>0 then
           :new.yxqfje:=to_number(substrb(ls_JKZDQF,3,3));
        end if;
     end if;
  end if;
  if Vyyid00 in ('227320','227318') and Inserting then
     --zhr 2008.10.31邵武人民医院和邵武妇幼医院由于售卡/换卡都不收卡费，但要求账户冻结4元钱
     select count(*) into Vcounter from bm_brxxb0 a where brid00=:new.brid00 and a.fbbh00<>3;
     if Vcounter>0 then
        :new.tyje00:=4; 
     end if;     
  end if; 
   if Vyyid00 in ('223672') and Inserting then  ---三明市妇幼保健院
     select count(*) into Vcounter from bm_brxxb0 a where brid00=:new.brid00 and a.fbbh00<>3;
     if Vcounter>0 then
        :new.tyje00:=5; 
     end if;     
  end if;   
end;
/


-- End of DDL Script for Trigger SD_HOSPITAL.TR_SF_SF_BRZHXX_UPDINS