-- Start of DDL Script for Function SD_HOSPITAL.SF_BQ_YKYEPD
-- Generated 2015-6-4 15:41:10 from SD_HOSPITAL@TOSD_csclYY

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;--预扣总余额
-- 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
BEGIN
  IF PBRID00>0 THEN
    VBRID00:=PBRID00;
  ELSE
    SELECT BRID00 INTO VBRID00 FROM ZY_BRXXB0  WHERE ZYID00=PZYID00;
  END IF;
  BEGIN
    SELECT JFLBID INTO VJFLBID FROM BM_BRJFLB  WHERE SYBZ00='1' AND SFYX00='Y';
  EXCEPTION
    WHEN OTHERS THEN
    RETURN 0;
  END;

  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;
  --重新计算担保金额,担保可能过期,反映到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;
  IF (VKYJESF<>'1') and (VKYJESF<>'4') THEN
    VYBYXQF:=VYBYXQF+VYBZHYE;
  END IF;
  --IF VYKZYE0+VYXQFJE-VTYJE00+VYBYXQF<0 THEN  update by 2015-06-03 dsm
  IF VZYE000+VYXQFJE-VTYJE00+VYBYXQF<0 THEN
    RETURN -1;
  ELSE
    RETURN 1;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN -1;
END SF_BQ_YKYEPD;
/



-- End of DDL Script for Function SD_HOSPITAL.SF_BQ_YKYEPD