CREATE OR REPLACE FUNCTION SF_BQ_YKYEPD
--判定一个病人的医疗账户预扣余额是否足够--
( PZYID00 IN NUMBER,  --住院流水号
  PBRID00 IN NUMBER   --病人ID
)
RETURN NUMBER IS
    COUNTER     NUMBER(3);
    VBRID00     BM_BRXXB0.BRID00%TYPE;
    VJFLBID     BM_BRJFLB.JFLBID%TYPE;
    VYBYXQF     ZY_BRZHXX.WJSJE0%TYPE;--医保允许欠费金额
    VWJSJE0     ZY_BRZHXX.WJSJE0%TYPE;--未结算金额
    VYJSJE0     ZY_BRZHXX.WJSJE0%TYPE;--已结算金额
    VZYE000     ZY_BRZHXX.ZYE000%TYPE;--总余额
    VYKZYE0     ZY_BRZHXX.YKZYE0%TYPE;--预扣后总余额
    VTYJE00     ZY_BRZHXX.TYJE00%TYPE;--停用金额
    VYXQFJE     ZY_BRZHXX.YXQFJE%TYPE;--允许欠费金额
    VYBZHYE     ZY_BRZHXX.YBZHYE%TYPE;--医保账户余额
    VYBYJS0     ZY_BRZHXX.YBYJS0%TYPE;--医保预结算金额
    VYBYZF0     ZY_BRZHXX.YBYZF0%TYPE;--医保预支付金额
    VZYZFSX     VW_BQ_YBZFBL.ZYZFSX%TYPE;--医保支付上限
    VYBQFBZ     VW_BQ_YBZFBL.ZYZFSX%TYPE;--医保起付标准
    VYBZFBL     VW_BQ_YBZFBL.ZYZFSX%TYPE;--医保支付比例
    VZYYJXX     bm_ybbrlb.ZYYJXX%type;--住院预交金下限
    VJFJE00     ZY_BRJFB0.JFJE00%type;--预交金总额
    VKYJESF     XT_XTCS00.VALUE0%type;--预扣总余额
    VDBZBM0     ZY_BRXXB0.DBZBM0%type;
    ls_FBBH00   ZY_BRXXB0.FBBH00%type;
    ls_YBZXLB   ZY_BRXXB0.YBZXLB%type;
    ls_SFXNH0   IC_YBBRLB.SFXNH0%type;
    LS_YBHDJE   BM_DBZDEB.YBHDJE%type;
    LS_BZYBZX   varchar2(2);
    LS_BRZTKQ   BM_BRXXB0.BRZTQK%type;
    VBYXQFYBBR  number(5);    
    VKYJESF_DBZ  varchar2(10);
    LS_KYE000   ZY_BRZHXX.XJYE00%type;
    LS_QFZXJE   ZY_BRZHXX.XJYE00%type;
    CNT         number(5); 
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- qks         2009.11.24  当BM_YBBRLB.ZYYJXX>0时：如果住院预交金总额<ZYYJXX,那么ZYZFSX=0
-- chenqw      2010.01.14  取VZYZFSX时可能出现多个记录而导致报错，增加条件B.FBBH00=Y.FBBH0
-- csf         2011.06.29  参数XT_KYJESF =(0 or 2 3)余额是否足够的判断应加上ybzhye，这样跟可用余额才一致
-- liuj        2013.10.23  参数XT_KYJESF=4 余额是否足够的判断应不算ybzhye for BQ-20131015-001
-- dsm         2017.04.11  单病种的病人余额不预判 for BQ-20170411-001
-- dsm         2017.08.07  自费,农合单病种病人如果未交够定额的预交金时,只能用不大于预交金总额的费用 for BQ-20170707-001
-- dsm         2018.02.27  城乡居民(BM_TYZD00.ZDMC00='医保预结算金额不做为透支金额的病人')不能做医保欠费 for BQ-20180227-002
-- dsm         2018.04.20  莆田附属单病种病人可用金额用SF_BQ_KYJE00来计算可用金额 ZYSF-20180420-001
-- dsm         2018.09.08  调用SF_BQ_KYJE00判断余额 for BQ-20180908-001 
-- dsm         2018.12.06  泉二病人欠费金额如果没有达到此值(2000元) BQ-20181204-002
-- huangjy     2020.02.26  莆田市慈康医院增加特殊免费0预交金病人续住   BQ-20200217-002 
BEGIN
  IF PBRID00>0 THEN
    VBRID00:=PBRID00;
    SELECT BRID00,FBBH00,YBZXLB INTO VBRID00,ls_FBBH00,ls_YBZXLB FROM ZY_BRXXB0  WHERE BRID00=PBRID00 and substr(BRZT00,1,1)='2' ;
  ELSE
    SELECT BRID00,FBBH00,YBZXLB INTO VBRID00,ls_FBBH00,ls_YBZXLB FROM ZY_BRXXB0  WHERE ZYID00=PZYID00;
  END IF;
  select SF_BQ_KYJE00(PZYID00,PBRID00) into LS_KYE000 from dual;
  begin
    --select count(1) into CNT from BM_TYZD00 where ZDMC00='特殊免费0预交金病人费别' and BH0000=to_char(ls_FBBH00) and YXBZ00='1';
    select count(1) into CNT from BM_TYZD00 where ZDMC00='特殊免费0预交金病人费别' and instr(','||BH0000||',',','||to_char(ls_FBBH00)||',')>0 and YXBZ00='1';
  exception
    when others then
    CNT:=0;	   
  end; 
  LS_QFZXJE:=0;
  select SF_BQ_BRLWKQFJE(VBRID00,LS_KYE000) into LS_QFZXJE from dual;
  if CNT>=1 then 
     if LS_KYE000+LS_QFZXJE>=0 then   --2020.02.26
       return 1;
     else
       return -1;
     end if;
  else
     if LS_KYE000+LS_QFZXJE>0 then   --2018.09.08
       return 1;
     else
       return -1;
     end if;
  end if;  
  ----下面无效------------------------ 

  BEGIN
    SELECT JFLBID INTO VJFLBID FROM BM_BRJFLB  WHERE SYBZ00='1' AND SFYX00='Y';
  EXCEPTION
    WHEN OTHERS THEN
    RETURN 0;
  END;
  select BRZTQK into LS_BRZTKQ from BM_BRXXB0 where BRID00=VBRID00;
  select  SFXNH0  into ls_SFXNH0   from  ic_ybbrlb  where  YBZXLB = ls_YBZXLB and FBBH00 = ls_FBBH00 and rownum=1;
  begin
    select substrb(value0,1,1) into VKYJESF from xt_xtcs00 where name00='XT_KYJESF';
  exception
    when others then
       VKYJESF:='0';--'0'按医保预结算+院内支付比例计算+许可欠费,'1'直接返回帐户的预扣总余额
                    --'2'按医保预结算+许可欠费计算'3'只按医保预结算的计算'4'按医保预结算且不将医保个人账户余额计算在内
  end;

  BEGIN
    SELECT ZYE000,  YKZYE0, WJSJE0, YXQFJE, TYJE00, YBZHYE, YBYJS0, YBYZF0, YJSJE0
    INTO   VZYE000,VYKZYE0,VWJSJE0,VYXQFJE,VTYJE00,VYBZHYE,VYBYJS0,VYBYZF0,VYJSJE0
    FROM   ZY_BRZHXX WHERE BRID00=VBRID00 AND JFLBID=VJFLBID AND SFDJ00='0';
  EXCEPTION
    WHEN OTHERS THEN
    RETURN 0;
  END;
  select count(*) into COUNTER from BM_DBZZDB where rownum=1;
  begin
    select DBZBM0 into VDBZBM0 FROM ZY_BRXXB0  WHERE BRID00=VBRID00 and substr(BRZT00,1,1)='2';
  exception 
    when others then
        VDBZBM0:=null;
  end;
  if VDBZBM0 is not null and COUNTER>0 then
    begin
         select substrb(value0,1,1) into VKYJESF_DBZ from xt_xtcs00 where name00='XT_DBZKYESF';
    exception
       when others then
          VKYJESF_DBZ:='0';--'0'有计算医保预结算返回参与计算可用额,'1'按医保预结算+院内支付比例计算+许可欠费
    end;        
    if  ls_FBBH00=1 or (ls_FBBH00=3 and ls_SFXNH0='1') then
      select SF_ZY_SFQYDBZJK(PZYID00) into LS_BZYBZX  from  dual;
      LS_KYE000:=0;
      if VKYJESF_DBZ='1' then
        select SF_BQ_KYJE00(0,VBRID00,'-1') into LS_KYE000 from dual;
        if LS_KYE000>0 then 
          return 1;
        else
          return -1;
        end if;    
      end if;      
      select  NVL(sum(JFJE00),0) into VJFJE00 from  zy_brjfb0  where  brid00 =VBRID00 and JFLBID = 2 and jzdh00 = 0;
      begin
         select YBHDJE into LS_YBHDJE  from BM_DBZDEB where YBZXLB=LS_BZYBZX  and DBZBM0=VDBZBM0;
      exception
         when others then
          LS_YBHDJE:=1;
      end;       
      if VJFJE00>=LS_YBHDJE then 
         return 1;
      end if;
    else
      return 1; 
    end if;
    
  end if;    
  --重新计算担保金额,担保可能过期,反映到YXQFJE字段
  BEGIN
    SELECT NVL(SUM(DBJE00),0) INTO VYXQFJE FROM ZY_BRDB00
    WHERE  BRID00=VBRID00 AND JFLBID=VJFLBID AND SFYX00='Y' AND
           NVL(DBJSRQ,TO_CHAR(SYSDATE,'YYYYMMDD'))||NVL(DBJSSJ,TO_CHAR(SYSDATE,'HH24:MI:SS'))>=TO_CHAR(SYSDATE,'YYYYMMDD')||TO_CHAR(SYSDATE,'HH24:MI:SS');
  EXCEPTION
    WHEN OTHERS THEN
      VYXQFJE:=0;
  END;
  BEGIN
    SELECT ZYZFSX, YBQFBZ,YBZFBL,ZYYJXX INTO VZYZFSX,VYBQFBZ,VYBZFBL,VZYYJXX
    FROM   VW_BQ_YBZFBL Y
    WHERE  EXISTS (SELECT 1 FROM BM_BRXXB0 B WHERE B.YBLB00=Y.YBLB00 AND B.FBBH00=Y.FBBH00 and B.YBBRLB=Y.YBBRLB AND BRID00=VBRID00);
    if VZYYJXX>0 then
      select  NVL(sum(JFJE00),0) into VJFJE00 from  zy_brjfb0  where  brid00 =VBRID00 and JFLBID = 2 and jzdh00 = 0;
      if VJFJE00<VZYYJXX then
        VZYZFSX:=0;
      end if;
    end if;
    VYBYXQF:=SF_BM_YBZFJE(VZYE000,VYKZYE0,VWJSJE0,VYJSJE0,VYBYJS0,VYBYZF0,VZYZFSX,VYBQFBZ,VYBZFBL);
  EXCEPTION
    WHEN OTHERS THEN
      VYBYXQF:=0;
  END;
  select count(*) into VBYXQFYBBR from BM_TYZD00 where ZDMC00='医保预结算金额不做为透支金额的病人' and MC0000=LS_BRZTKQ;
  if VBYXQFYBBR>0 then
    VYBYXQF:=0;
  end if;    
  IF (VKYJESF<>'1') and (VKYJESF<>'4') THEN
    VYBYXQF:=VYBYXQF+VYBZHYE;
  END IF;
  IF VYKZYE0+VYXQFJE-VTYJE00+VYBYXQF<0 THEN
    RETURN -1;
  ELSE
    RETURN 1;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN -1;
END SF_BQ_YKYEPD;
/