create or replace function SF_BQ_KYJE00
(
  PZYID00 IN NUMBER,  --住院流水号
  PBRID00 IN NUMBER,   --病人ID
  PKYESF0 in number default '-1'
)
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;   --已结算金额
    VSYWJS0     ZY_BRZHXX.WJSJE0%TYPE;   --所有未结算金额
    VZYE000     ZY_BRZHXX.ZYE000%TYPE;   --总余额
    VKYJE00     ZY_BRZHXX.ZYE000%TYPE;   --可用金额
    VYBZFJE     ZY_BRZHXX.ZYE000%TYPE;   --可用金额
    VBRXJYE     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     char(1);
    VZFY000     ZY_BRFY00.HJJE00%type;
    VZYID00     ZY_BRXXB0.ZYID00%type;
    VFBBH00     ZY_BRXXB0.FBBH00%type;
    VDBZDE0     ZY_BRZHXX.ZYE000%type;
    VDBZNFY     ZY_BRZHXX.ZYE000%type;
    VDBZWFY     ZY_BRZHXX.ZYE000%type;
    LS_BZYBZX   varchar2(10);
    VYZFJE0     ZY_BRZHXX.ZYE000%type;
    VDBZCWF     ZY_BRZHXX.DBZCWF%type;
    VDBZHCF     ZY_BRZHXX.DBZHCF%type;
    VDBZBM0     ZY_BRXXB0.DBZBM0%type;
    VKYJESF_DBZ varchar2(10);
    VBYXQFYBBR  number(5);
    LS_BRZTKQ   BM_BRXXB0.BRZTQK%type;
    VYBYJSZF    ZY_BRZHXX.YBZFJE%type;  --医保预结算自费
    VWJZJE0     ZY_BRZHXX.WJZJE0%type;  --未记账金额
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- qks         2009.11.24  当BM_YBBRLB.ZYYJXX>0时：如果住院预交金总额<ZYYJXX,那么ZYZFSX=0
-- zhangwz     2010.05.04  要求当停用金额设置为负数时病区管理系统的可用额也能体现
-- liuj        2013.10.23  参数XT_KYJESF加一个值计算方式4 for BQ-20131015-001
-- dsm         2014.06.06  XT_KYJESF增加5的情况 for BQ-20140603-001
-- zhanghr     2015.01.21  for YJ-20150109-001
-- dsm         2017.03.24  XT_KYJESF增加7的情况 for BQ-20170302-001
-- dsm         2017.03.24  增加单病种可用额的算法 for BQ-20170216-001
-- dsm         2017.10.27  XT_KYJESF=6时加if VKYJE00<VZYE000 then for BQ-20171025-001
-- dsm         2018.01.01  增加参数PKYESF0 for BQ-20180227-002
-- dsm         2018.04.20  莆田附属单病种病人除了超标床位和除外耗材费用都按BM_YBBRLB.YBZFBL来计算可用金额 for ZYSF-20180420-001
-- zhuyr       2018.07.13  添加判断功能，如果ZY_BRZHXX.YXQFJE（允许欠费金额）值不为0，则使用该表中的值  by ZYSF-20180628-002
-- dsm         2018.07.23  ZYSF-20180420-001 VKYJE00算法做变动 for ZYSF-20180711-001
-- dsm         2018.09.08  单病种病人的可用额也要考虑允许欠费金额 for BQ-20180908-001
-- dsm         2018.12.24  单病种病人的预结算自费金额已经包括了除外耗材的自费部分 归档BQ-20181224-001
-- dsm         2019.08.14  获取VW_BQ_YBZFBL数据时的病人属性要从ZY_BRXXB0获取 for ZYSF-20190814-001
-- dsm         2020.03.13  XT_KYJESF=5时增加VYXQFJE for BQ-20200306-002
-- qiulf       2020.06.28  ZY_DBZKYESF=2自费金额>0时可用额为预交金-自费金额 ZYSF-20200420-002
-- dsm         2020.08.10  ZY_DBZKYESF=2时可用余额包括医保账户余额 for BQ-20200616-002
-- dsm         2021.01.13  没有做预结算时可用金额直接用预交金-总费用 for BQ-20201210-001
-- dsm         2021.09.27  省二自费单病种归档 for BQ-20210910-001
-- qiulf       2021.10.27  限制自费病人单病种不能超额 by ZYSF-20211018-004
-- dsm         2022.08.17  单病种有预结算时可用额=预交金-医保预结算自费 归档 for ZHCX-20220826-001
-- dsm         2024.04.08  可用金额减未记账金额 归档 for BQ-20240402-002
BEGIN
  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;
  if PKYESF0>=0 then
    VKYJESF:=PKYESF0;
  end if;
  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 WJSJE0,ZYE000,YKZYE0,decode(sign(TYJE00),-1,0,TYJE00),YBYJS0,YBYZF0,YXQFJE,YJSJE0,WJSJE0+ZYE000-YKZYE0,YBZHYE 要求停用金额设置负数时也能体现
    SELECT WJSJE0,ZYE000,YKZYE0,TYJE00,YBYJS0,YBYZF0,YXQFJE,YJSJE0,WJSJE0+ZYE000-YKZYE0,YBZHYE,nvl(WJZJE0,0)
    INTO   VWJSJE0,VZYE000,VYKZYE0,VTYJE00,VYBYJS0,VYBYZF0,VYXQFJE,VYJSJE0,VSYWJS0,VYBZHYE,VWJZJE0
    FROM   ZY_BRZHXX WHERE BRID00=VBRID00 AND JFLBID=VJFLBID AND SFDJ00='0';
  EXCEPTION
    WHEN OTHERS THEN
    RETURN 0;
  END;
  --重新计算担保金额,担保可能过期,反映到YXQFJE字段
  if VYXQFJE = 0 then
    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;
  end if;
  select ZYID00,FBBH00,nvl(DBZBM0,' ') into VZYID00,VFBBH00,VDBZBM0 from (
  select ZYID00,FBBH00,DBZBM0 from ZY_BRXXB0 where BRID00=VBRID00 order by ZYID00 desc) where rownum=1;
  begin
      select SF_ZY_SFQYDBZJK(VZYID00) into LS_BZYBZX  from  dual;
      exception
      when others then
      LS_BZYBZX:='-1';
  end;
  if LS_BZYBZX<>'-1' and VDBZBM0<>' ' and VFBBH00=3 then
    begin
      select substrb(value0,1,1) into VKYJESF_DBZ from xt_xtcs00 where name00='ZY_DBZKYESF';
    exception
    when others then
       VKYJESF_DBZ:='0';--'0'有计算医保预结算返回参与计算可用额,'1'按医保预结算+院内支付比例计算+许可欠费
    end;
    VKYJESF:='7';
  end if;
  if VKYJESF='0' then--按医保支付比例计算允许欠费
     BEGIN
       --取住院支付上限，医保起伏线，医保自付比例
       select max(ZYID00) into VZYID00 from ZY_BRXXB0 where BRID00=VBRID00;
       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.YBBRLB=Y.YBBRLB AND
              --               B.FBBH00=Y.FBBH00 AND BRID00=VBRID00);
              (Y.YBLB00,Y.YBBRLB,Y.FBBH00) in  (SELECT B.YBLB00,B.YBBRLB,B.FBBH00 from ZY_BRXXB0  B where B.ZYID00=VZYID00 );

       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;
       --返回医保病人所有未结部分(含预扣和预结)医保预计支付金额
       VYBZFJE:=SF_BM_YBZFJE(VZYE000,VYKZYE0,VWJSJE0,VYJSJE0,VYBYJS0,VYBYZF0,VZYZFSX,VYBQFBZ,VYBZFBL);
       VZYZFSX:=GREATEST(VZYZFSX-VYJSJE0,0);
       VYBQFBZ:=GREATEST(VYBQFBZ-VYJSJE0,0);
       --计算当前情况下，医保可支付最高金额
       IF VYBYJS0>=VZYZFSX THEN--预结>上限
         IF VYBYJS0>VSYWJS0 THEN--预结>未结,病人有退费
           VYBYXQF:=VYBYZF0;
         ELSE--预结<=未结
           VYBYXQF:=VYBYZF0;
         END IF;
       ELSE--VYBYJS0<VZYZFSX预结<上限
         IF VYBYJS0>VYBQFBZ THEN --预结>起付
           VYBYXQF:=VYBYZF0+(VZYZFSX-VYBYJS0)*VYBZFBL;--(上限-预结)*比率
         ELSE--VYBYJS0<=VYBQFBZ 预结<起付
           IF VSYWJS0>=VYBQFBZ THEN
             VYBYXQF:=VYBYZF0+(VZYZFSX-VYBQFBZ)*VYBZFBL;
           ELSE--VSYWJS0<VYBQFBZ
             VYBYXQF:=VYBYZF0;
           END IF;
         END IF;
       END IF;
     EXCEPTION
       WHEN OTHERS THEN
         VYBZFJE:=0;
         VYBYXQF:=0;
     END;
     select BRZTQK into LS_BRZTKQ from BM_BRXXB0 where BRID00=VBRID00;
     select count(*) into VBYXQFYBBR from BM_TYZD00 where ZDMC00='医保预结算金额不做为透支金额的病人' and MC0000=LS_BRZTKQ;
     if VBYXQFYBBR>0 then  --医保预结算金额不做为透支金额的病人
       VYBYXQF:=0;
     end if;
     --VBRXJYE:=VYKZYE0+VYBZFJE-VTYJE00+VYXQFJE;
     VBRXJYE:=VYKZYE0+VYBZFJE+VYXQFJE;--change by czc 2024.04.14
     IF VYBZFBL=1 THEN
       IF VBRXJYE<0 THEN
         VKYJE00:=VBRXJYE;
       ELSIF  VYBYXQF>VSYWJS0 THEN
         --VKYJE00:=VBRXJYE+VYBYXQF;
         VKYJE00:=VYKZYE0-VTYJE00+VYXQFJE+VYBYXQF;
       ELSE
         VKYJE00:=VBRXJYE;
       END IF;
     ELSE
--       VKYJE00:=VBRXJYE+LEAST(GREATEST(VBRXJYE,0)/(1-VYBZFBL)*VYBZFBL,VYBYXQF);
       VKYJE00:=VYKZYE0-VTYJE00+VYXQFJE+LEAST(GREATEST(VBRXJYE,0)/(1-VYBZFBL)*VYBZFBL+VYBZFJE,VYBYXQF);
     END IF;
  elsif VKYJESF='1' then --直接返回帐户的预扣总余额
    VKYJE00:=VYKZYE0;--+VYXQFJE+VYBYZF0-VTYJE00;
  elsif VKYJESF='2' then --按医保预结算的计算+许可欠费计算
    VKYJE00:=VYKZYE0+VYXQFJE+VYBYZF0+VYBZHYE-VTYJE00;
  elsif VKYJESF='3' then --只按医保预结算的计算
    VKYJE00:=VYKZYE0+VYBYZF0+VYBZHYE-VTYJE00;
  elsif VKYJESF='4' then --按医保预结算且不将医保个人账户余额计算在内
    VKYJE00:=VYKZYE0+VYBYZF0-VTYJE00;
  elsif VKYJESF='5' then --按医保预结算+按自费比例允许欠费,且不将医保个人账户余额计算在内
       select nvl(max(ZYID00),0) into VZYID00 from ZY_BRXXB0 where BRID00=VBRID00;
       SELECT  YBZFBL INTO  VYBZFBL
       FROM   VW_BQ_YBZFBL Y
       WHERE  EXISTS (SELECT 1 FROM BM_BRXXB0 B
                      WHERE  B.YBLB00=Y.YBLB00 AND B.YBBRLB=Y.YBBRLB AND
                             B.FBBH00=Y.FBBH00 AND BRID00=VBRID00);
       select  NVL(sum(JFJE00),0) into VJFJE00 from  zy_brjfb0  where  brid00 =VBRID00 and JFLBID = 2 and jzdh00 = 0;
       select nvl(sum(HJJE00),0) into VZFY000 from ZY_BRFY00 where ZYID00=VZYID00 and nvl(JZDH00,0)=0;
       VKYJE00:=VJFJE00-VZFY000*(1-VYBZFBL)+VYXQFJE;
  elsif VKYJESF='6' then --按医保自费比例(莆田秀屿)
     select nvl(max(ZYID00),0) into VZYID00 from ZY_BRXXB0 where BRID00=VBRID00;
     SELECT YBZFBL,YBQFBZ INTO VYBZFBL,VYBQFBZ
        FROM VW_BQ_YBZFBL Y
            WHERE EXISTS (SELECT 1 FROM BM_BRXXB0 B
                    WHERE B.YBLB00=Y.YBLB00 AND B.YBBRLB=Y.YBBRLB AND
                      B.FBBH00=Y.FBBH00 AND BRID00=VBRID00);
     select NVL(sum(JFJE00),0) into VJFJE00 from zy_brjfb0 where brid00 =VBRID00 and JFLBID = 2 and jzdh00 = 0;
     select nvl(sum(WJSJE0),0) into VZFY000 from ZY_BRZHXX where BRID00=VBRID00 ;
     IF VYBZHYE<=1000 THEN
       VYBZHYE:=0;
     ELSE
       VYBZHYE:=VYBZHYE-1000;
     END IF;
     VKYJE00:=VJFJE00+VYXQFJE+VYBZHYE-(VZFY000-(VZFY000-VYBQFBZ)*VYBZFBL);
     if  VKYJE00<VZYE000 then  --王进现场加BQ-20171025-001
        VKYJE00:=VZYE000;
     end if;
  elsif VKYJESF='7' then --单病种病人      --
      if VKYJESF_DBZ='1' then  --莆田学院附属医院  223368
        --select nvl(max(ZYID00),0) into VZYID00 from ZY_BRXXB0 where BRID00=VBRID00;
        SELECT YBZFBL,YBQFBZ INTO VYBZFBL,VYBQFBZ
          FROM VW_BQ_YBZFBL Y WHERE EXISTS (SELECT 1 FROM BM_BRXXB0 B WHERE B.YBLB00=Y.YBLB00 AND B.YBBRLB=Y.YBBRLB
             AND B.FBBH00=Y.FBBH00 AND BRID00=VBRID00);
        select NVL(sum(JFJE00),0) into VJFJE00 from zy_brjfb0 where brid00 =VBRID00 and JFLBID = 2 and jzdh00 = 0;
        select nvl(WJSJE0,0),nvl(DBZCWF,0),nvl(DBZHCF,0) into
          VZFY000,--总费用
          VDBZCWF,--超标床位费
          VDBZHCF --除外耗材费
        from ZY_BRZHXX where BRID00=VBRID00 ;
        --VKYJE00:=VJFJE00+VYXQFJE+VYBZHYE-(VDBZCWF+VDBZHCF)-((VZFY000-(VDBZCWF+VDBZHCF))*(1 -VYBZFBL));
        VKYJE00:=VJFJE00+VYXQFJE+VYBZHYE-(VDBZCWF+VDBZHCF*(1 -VYBZFBL))-((VZFY000-(VDBZCWF+VDBZHCF))*(1 -VYBZFBL));
      elsif VKYJESF_DBZ='2' then
        select SF_ZY_GETDBZXX(VZYID00,1) into VDBZDE0 from dual;  --定额
        select SF_ZY_GETDBZXX(VZYID00,2) into VDBZNFY from dual;  --病种内费用
        select NVL(sum(JFJE00),0) into VJFJE00 from  zy_brjfb0  where  brid00 =VBRID00 and JFLBID = 2 and jzdh00 = 0;
        VJFJE00:=VJFJE00+VYXQFJE+VYBZHYE;
        select nvl(WJSJE0,0),YBYJS0-YBYZF0 into VZFY000,VYZFJE0 from ZY_BRZHXX where BRID00=VBRID00 and rownum=1;
        VDBZWFY:=VZFY000-VDBZNFY;
        if VZFY000<VJFJE00 and VJFJE00<VDBZDE0 then --费用小于预交金
          --VKYJE00:=VJFJE00-LEAST(VZFY000,VYZFJE0);
          if VYZFJE0=0 then
            VKYJE00:=VJFJE00-VZFY000;
          else
            VKYJE00:=VJFJE00-LEAST(VZFY000,VYZFJE0);
          end if;
        elsif  VJFJE00>VDBZDE0 then --预交金 >定额
          VKYJE00:=99999;
        elsif VYBYJS0>0 then   --有做预结算时,预交金-医保预结算自费部分-未参与预结算的金额
             if VDBZCWF+VDBZHCF>0 then
             VKYJE00:=VJFJE00-nvl(VYBYJSZF,0)-(VZFY000-VYBYJS0) ;
           else
             VKYJE00:=VJFJE00-nvl(VYBYJSZF,0);
           end if;
        else
           VKYJE00:=VZYE000+VYXQFJE;        --直接取余额+YXQFJE0
        end if;
        if VFBBH00='1' then  --BQ-20210910-001
          VKYJE00:=VJFJE00-VDBZWFY-VYZFJE0-VDBZDE0;
        else
          VKYJE00:=VKYJE00;        --预交金-病种外费用-医保预结算自费部分
        end if;
      else
        select SF_ZY_GETDBZXX(VZYID00,1) into VDBZDE0 from dual;  --定额
        select SF_ZY_GETDBZXX(VZYID00,2) into VDBZNFY from dual;  --病种内费用
        select NVL(sum(JFJE00),0) into VJFJE00 from  zy_brjfb0  where  brid00 =VBRID00 and JFLBID = 2 and jzdh00 = 0;
        VJFJE00:=VJFJE00+VYXQFJE;
        select nvl(WJSJE0,0),YBYJS0-YBYZF0 into VZFY000,VYZFJE0 from ZY_BRZHXX where BRID00=VBRID00 and rownum=1;
        VDBZWFY:=VZFY000-VDBZNFY;
        if VZFY000<VJFJE00 and VJFJE00<VDBZDE0 then --费用小于预交金
          --VKYJE00:=VJFJE00-LEAST(VZFY000,VYZFJE0);
          if VYZFJE0=0 then
            VKYJE00:=VJFJE00-VZFY000;
          else
            VKYJE00:=VJFJE00-LEAST(VZFY000,VYZFJE0);
          end if;
        elsif  VJFJE00>VDBZDE0 then --预交金 >定额
          VKYJE00:=99999;
        elsif VYZFJE0>0 then
          VKYJE00:=VJFJE00-VYZFJE0;---(VZFY000-VYBYJS0); --预交金-医保预结算自费部分 -未预结算部分
        else
          VKYJE00:=VJFJE00-VDBZWFY-VYZFJE0;        --预交金-病种外费用-医保预结算自费部分
        end if;
      end if;
  end if;
  VKYJE00:=VKYJE00-VWJZJE0;
  RETURN VKYJE00;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END SF_BQ_KYJE00;