CREATE OR REPLACE PROCEDURE SP_SF_MZFPSR_TJ0000
(
        as_KSRQ00    in    char,       --开始日期
        as_KSSJ00    in    char,       --开始时间
        as_JSRQ00    in    char,       --结束日期
        as_JSSJ00    in    char,       --结束时间
        as_HSXMMC    in    char,       --核算项目名称
        as_XXLB00    in    char,       --显示类别,'0':按发票项目显示,'1':按核算项目显示
        as_TJLB00    in    char,       --统计类别,'0':所有病人,'1':现金病人,'2':持卡病人,'3':记账
        as_FYFSLB    in    char,       --费用发生类别：'0':全部 '1':自付 '2': 记帐 '3': 减免
        as_BRFBMC    in    char,       --病人费别名称
        as_YYJC00    in    char,       --分院简称
        ad_ID0000    out   number,     --统计报表对应的ID
        as_YHMSG0    out   varchar,    --存储过程提示的错误信息
        as_SYSMSG    out   varchar     --系统提示的错误信息
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- xzw      2007.06.21     不体现出费用为零的项目;
-- zhr      2009.09.22     修正医保结算小于0的，不体现在报表上的错误。
-- zhangyc  2013.04.10     增加光泽县医院 228215 控制 by MZSF-20130409-001
-- zhangyc  2014.05.08     把大病补偿(SF_JZB000.YBYL13)从医保中分离出来 by MZSF-20140504-001
-- zhangyc  2014.07.03     修正统计大病补偿导致预交金金额错误 by MZSF-20140606-002
-- dsm      2015.01.30     南平其中药品按药房分类的，再细到药品大类 for MZSF-20150112-003
-- zhangyc  2017.06.05     光泽县医院增加个人体检统计 by MZSF-20170428-002
-- zhangyc  2018.02.09     增加医院支付 by MZSF-20180209-002
as
        ls_BH0000   BM_FPXM00.BH0000%TYPE;
        ls_YBMC00   IC_YBBRLB.YBMC00%TYPE;
        LS_YBMC01   IC_YBBRLB.YBMC00%TYPE;
        ls_TMPBH0   BM_FPXM00.XMMC00%TYPE;--BM_FPXM00.BH0000%TYPE;
        ls_XMMC00   BM_FPXM00.XMMC00%TYPE;
        ls_HJJE00   SF_FYMX00.HJJE00%TYPE;
        ls_ZFJE00   SF_FYMX00.ZFJE00%TYPE;
        ls_GFJE00   SF_FYMX00.GFJE00%TYPE;
        ls_JZJE00   SF_FYMX00.JZJE00%TYPE;
        ls_XMBH00   BM_MZSRTJ.XMBH01%TYPE;
        ls_XMBH01   BM_MZSRTJ.XMBH01%TYPE;
        ls_JE0000   SF_FYMX00.HJJE00%TYPE;
        ls_JE0001   SF_FYMX00.HJJE00%TYPE;
        ls_JE0002   SF_FYMX00.HJJE00%TYPE;
        V_YYID00    XT_YYXX00.YYID00%TYPE;
        V_YYID00_1    XT_YYXX00.YYID00%TYPE;
        ls_yjshj0   number(12,2);
        ls_wjshj0   number(12,2);
        LS_YBJE00   number(12,4);
        LS_DBBCJE   number(12,4);
        LS_XH0000   number(3);
        LS_PXH000   IC_YBBRLB.PXH000%TYPE;
        LS_XMMC02   BM_MZSRTJ.XMBH01%TYPE;
        --按核算项目显示
        CURSOR CUR_SF_MZHSXM_TJ0000 IS
           select  C.XLH000,C.XMMC00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00)
                 from  SF_FYMX00 A,BM_YYSFXM B,BM_PARAMS C,SF_BRFY00 H,BM_BRXXB0 I,IC_YBBRLB J
                where  A.XMBH00  = B.SFXMID
                  and  B.HSXMID  = C.ID0000(+)
                  and (C.BH0000 like ls_TMPBH0||'%' or  as_HSXMMC = '所有项目')
                  and  A.DJH000  = H.DJH000
                  and  H.BRID00  = I.BRID00
                  --AND  H.JZDH00<>0
                  and  I.FBBH00  = J.FBBH00
                  and  I.YBLB00  = J.YBLB00
                  and  H.CZRQ00 >= as_KSRQ00
                  and  H.CZRQ00 <= as_JSRQ00
                  and  H.CZRQ00||H.CZSJ00>=as_KSRQ00||as_KSSJ00
                  and  H.CZRQ00||H.CZSJ00<=as_JSRQ00||as_JSSJ00
                  and (J.YBMC00  = as_BRFBMC  or  as_BRFBMC = '所有病人')
                  --and ((as_TJLB00='0') or (as_TJLB00='1' and H.JFLBID=0) or (as_TJLB00='2' and H.JFLBID <> 0) or (as_TJLB00='3'))
                  and ((as_TJLB00='0') or (as_TJLB00='1' and H.JFLBID=0) or (as_TJLB00='2' and H.JFLBID <> 0))

                group  by  C.XLH000,C.XMMC00;
begin
    ls_TMPBH0:='';
    if as_yyjc00<>'0' then
       select yyid00 into V_YYID00_1 from xt_yyxx00 where yyjc00=as_YYJC00;
     else
       select yyid00 into V_YYID00_1 from xt_yyxx00 where rownum=1;
    end if;
    select  SQ_BM_MZSRTJ_ID0000.nextval  into  ad_ID0000  from  dual;
    insert into  BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01,XMBH02,XMMC02,XMJE02)  values(ad_ID0000,'00','     合计',0,'00','     合计',0);
    IF  as_XXLB00 = '0'  THEN  --按票据项目
       insert into  BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01)
           select ad_ID0000,BH0000,XMMC00,0  from  BM_FPXM00  Group by BH0000,XMMC00;


    if trim(V_YYID00_1) in ('227049','228215') then --南平医院227049 --光泽县医院 228215
        LS_XH0000:=90;
     declare CURSOR cur_sf_gfjfb0 IS
        --加药品大类for MZSF-20150112-003
        select ' 其中'||BMMC00||'('||A.XMMC00||')' as XMMC00,sum(A.HJJE00) XMJE00
            from  SF_FYMX00 A ,SF_BRFY00 H,BM_BMBM00 C
          where  A.DJH000  = H.DJH000  and A.ZXKS00=C.BMBH00 AND A.XMBH00>9999999990
                 and  H.CZRQ00 >= as_KSRQ00 and H.CZRQ00 <= as_JSRQ00
                   and  H.CZRQ00||H.CZSJ00>=as_KSRQ00||as_KSSJ00
                   and  H.CZRQ00||H.CZSJ00<=as_JSRQ00||as_JSSJ00 group by BMMC00,A.XMMC00;
      begin
      for yb in cur_sf_gfjfb0 loop
         insert into  BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01)values(ad_ID0000,LS_XH0000,yb.XMMC00,yb.XMJE00);
          LS_XH0000:=LS_XH0000+1;
       end loop;
      end;
    end if;

       --按发票项目显示
       if as_BRFBMC = '所有病人' then
          if as_yyjc00<>'0' then
             declare CURSOR CUR_SF_MZFPSR_TJ0000 IS
               select  C.BH0000,C.XMMC00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00)
                  from  SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 H
                  where  A.XMBH00=B.SFXMID and B.MZFPID=C.FPXMID and A.DJH000=H.DJH000 and H.DJH000 <>0 and
                         H.CZRQ00 >= as_KSRQ00 and H.CZRQ00||H.CZSJ00>=as_KSRQ00||as_KSSJ00 and
                         H.CZRQ00 <= as_JSRQ00 and H.CZRQ00||H.CZSJ00<=as_JSRQ00||as_JSSJ00 and
                        --((as_TJLB00 in('0','3'))or(as_TJLB00='1' and H.JFLBID=0)or(as_TJLB00='2'and H.JFLBID<>0))
                        ((as_TJLB00='0')or(as_TJLB00='1' and H.JFLBID=0)or(as_TJLB00='2'and H.JFLBID<>0))
                        and a.kdks00 in(select bmbh00 from bm_bmbm00 where yyid00=V_YYID00_1)
                  group  by  C.BH0000,C.XMMC00;
             begin
                 OPEN CUR_SF_MZFPSR_TJ0000;
                 LOOP
                   FETCH CUR_SF_MZFPSR_TJ0000 INTO  ls_BH0000,ls_XMMC00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00;
                   EXIT WHEN CUR_SF_MZFPSR_TJ0000%NOTFOUND;
                --Update  BM_MZSRTJ  set  XMJE01 = DECODE(as_TJLB00,'3',ls_JZJE00,'1',ls_ZFJE00,ls_HJJE00)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
                Update  BM_MZSRTJ  set  XMJE01 = DECODE(as_FYFSLB,'1',ls_ZFJE00,'2',ls_JZJE00,'3',ls_GFJE00,ls_HJJE00)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
                --Update  BM_MZSRTJ  set  XMJE01 =NVL(ls_HJJE00,0)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
               END LOOP;
               CLOSE CUR_SF_MZFPSR_TJ0000;
               end;
          else
               declare CURSOR CUR_SF_MZFPSR_TJ0000 IS
               select  C.BH0000,C.XMMC00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00)
                  from  SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 H
                  where  A.XMBH00=B.SFXMID and B.MZFPID=C.FPXMID and A.DJH000=H.DJH000 and H.DJH000 <>0 and
                         H.CZRQ00 >= as_KSRQ00 and H.CZRQ00||H.CZSJ00>=as_KSRQ00||as_KSSJ00 and
                         H.CZRQ00 <= as_JSRQ00 and H.CZRQ00||H.CZSJ00<=as_JSRQ00||as_JSSJ00 and
                        --((as_TJLB00 in('0','3'))or(as_TJLB00='1' and H.JFLBID=0)or(as_TJLB00='2'and H.JFLBID<>0))
                        ((as_TJLB00='0')or(as_TJLB00='1' and H.JFLBID=0)or(as_TJLB00='2'and H.JFLBID<>0))
                  group  by  C.BH0000,C.XMMC00;

             begin
                 OPEN CUR_SF_MZFPSR_TJ0000;
                 LOOP
                   FETCH CUR_SF_MZFPSR_TJ0000 INTO  ls_BH0000,ls_XMMC00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00;
                   EXIT WHEN CUR_SF_MZFPSR_TJ0000%NOTFOUND;
                --Update  BM_MZSRTJ  set  XMJE01 = DECODE(as_TJLB00,'3',ls_JZJE00,'1',ls_ZFJE00,ls_HJJE00)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
                Update  BM_MZSRTJ  set  XMJE01 = DECODE(as_FYFSLB,'1',ls_ZFJE00,'2',ls_JZJE00,'3',ls_GFJE00,ls_HJJE00)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
                --Update  BM_MZSRTJ  set  XMJE01 =NVL(ls_HJJE00,0)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
               END LOOP;
               CLOSE CUR_SF_MZFPSR_TJ0000;
               end;
          end if;
       else
          if as_yyjc00<>'0' then
             declare CURSOR CUR_SF_MZFPSR_TJ0000 IS
                 select C.BH0000,C.XMMC00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00)
                 from  SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 H,BM_BRXXB0 I
                 where  A.XMBH00=B.SFXMID and B.MZFPID=C.FPXMID and A.DJH000=H.DJH000 and H.BRID00=I.BRID00 and
                        H.DJH000 <>0 and H.CZRQ00 >= as_KSRQ00 and H.CZRQ00||H.CZSJ00>=as_KSRQ00||as_KSSJ00 and
                        H.CZRQ00 <= as_JSRQ00 and H.CZRQ00||H.CZSJ00<=as_JSRQ00||as_JSSJ00 and
                       ((as_TJLB00='0')or(as_TJLB00='1' and H.JFLBID=0)or(as_TJLB00='2'and H.JFLBID<>0))
                       and (I.FBBH00,I.YBLB00) in (select fbbh00,yblb00 from IC_YBBRLB where YBMC00=as_BRFBMC)
                       and a.kdks00 in(select bmbh00 from bm_bmbm00 where yyid00=V_YYID00_1)
                 group  by  C.BH0000,C.XMMC00;
             begin
                 OPEN CUR_SF_MZFPSR_TJ0000;
                 LOOP
                      FETCH CUR_SF_MZFPSR_TJ0000 INTO  ls_BH0000,ls_XMMC00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00;
                      EXIT WHEN CUR_SF_MZFPSR_TJ0000%NOTFOUND;
                    --Update  BM_MZSRTJ  set  XMJE01 = DECODE(as_TJLB00,'3',ls_JZJE00,'1',ls_ZFJE00,ls_HJJE00)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
                    Update  BM_MZSRTJ  set  XMJE01 = DECODE(as_FYFSLB,'1',ls_ZFJE00,'2',ls_JZJE00,'3',ls_GFJE00,ls_HJJE00)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
                   --Update  BM_MZSRTJ  set  XMJE01 =NVL(ls_HJJE00,0)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
                   END LOOP;
                   CLOSE CUR_SF_MZFPSR_TJ0000;
            end;
          else
             declare CURSOR CUR_SF_MZFPSR_TJ0000 IS
                 select C.BH0000,C.XMMC00,sum(A.HJJE00),sum(A.ZFJE00),sum(A.JZJE00),sum(A.GFJE00)
                 from  SF_FYMX00 A,BM_YYSFXM B,BM_FPXM00 C,SF_BRFY00 H,BM_BRXXB0 I
                 where  A.XMBH00=B.SFXMID and B.MZFPID=C.FPXMID and A.DJH000=H.DJH000 and H.BRID00=I.BRID00 and
                        H.DJH000 <>0 and H.CZRQ00 >= as_KSRQ00 and H.CZRQ00||H.CZSJ00>=as_KSRQ00||as_KSSJ00 and
                        H.CZRQ00 <= as_JSRQ00 and H.CZRQ00||H.CZSJ00<=as_JSRQ00||as_JSSJ00 and
                       ((as_TJLB00='0')or(as_TJLB00='1' and H.JFLBID=0)or(as_TJLB00='2'and H.JFLBID<>0))
                       and (I.FBBH00,I.YBLB00) in (select fbbh00,yblb00 from IC_YBBRLB where YBMC00=as_BRFBMC)
                 group  by  C.BH0000,C.XMMC00;
             begin
                 OPEN CUR_SF_MZFPSR_TJ0000;
                 LOOP
                   FETCH CUR_SF_MZFPSR_TJ0000 INTO  ls_BH0000,ls_XMMC00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00;
                   EXIT WHEN CUR_SF_MZFPSR_TJ0000%NOTFOUND;
                 --Update  BM_MZSRTJ  set  XMJE01 = DECODE(as_TJLB00,'3',ls_JZJE00,'1',ls_ZFJE00,ls_HJJE00)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
                 Update  BM_MZSRTJ  set  XMJE01 = DECODE(as_FYFSLB,'1',ls_ZFJE00,'2',ls_JZJE00,'3',ls_GFJE00,ls_HJJE00)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
                 --Update  BM_MZSRTJ  set  XMJE01 =NVL(ls_HJJE00,0)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
                   END LOOP;
                   CLOSE CUR_SF_MZFPSR_TJ0000;
             end;
          end if;
    end if;
    ELSE  --按核算项目
       if  as_HSXMMC <> '所有项目' then
          select  min(BH0000)  into  ls_TMPBH0  from  BM_YYHSXM  where  HSXMMC||'('||BH0000||')' =as_HSXMMC;
       end if;
         insert into  BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01)
           select ad_ID0000,XLH000,XMMC00,0  from  BM_PARAMS  where ( BH0000 like ls_TMPBH0||'%' or as_HSXMMC = '所有项目')  Group by XLH000,XMMC00;

    if trim(V_YYID00_1) in ('227049','228215') then --南平医院227049 --光泽县医院 228215
        LS_XH0000:=90;
     declare CURSOR cur_sf_gfjfb0 IS
        select ' 其中'||BMMC00 as XMMC00,sum(A.HJJE00) XMJE00
            from  SF_FYMX00 A ,SF_BRFY00 H,BM_BMBM00 C
          where  A.DJH000  = H.DJH000  and A.ZXKS00=C.BMBH00 AND A.XMBH00>9999999990
                 and  H.CZRQ00 >= as_KSRQ00 and H.CZRQ00 <= as_JSRQ00
                   and  H.CZRQ00||H.CZSJ00>=as_KSRQ00||as_KSSJ00
                   and  H.CZRQ00||H.CZSJ00<=as_JSRQ00||as_JSSJ00 group by BMMC00;
      begin
      for yb in cur_sf_gfjfb0 loop
         insert into  BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01)values(ad_ID0000,LS_XH0000,yb.XMMC00,yb.XMJE00);
          LS_XH0000:=LS_XH0000+1;
       end loop;
      end;
    end if;

       OPEN CUR_SF_MZHSXM_TJ0000;
           LOOP
                FETCH CUR_SF_MZHSXM_TJ0000 INTO  ls_BH0000,ls_XMMC00,ls_HJJE00,ls_ZFJE00,ls_JZJE00,ls_GFJE00;
                EXIT WHEN CUR_SF_MZHSXM_TJ0000%NOTFOUND;
              --Update  BM_MZSRTJ  set  XMJE01 = DECODE(as_TJLB00,'3',ls_JZJE00,'1',ls_ZFJE00,ls_HJJE00)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
                Update  BM_MZSRTJ  set  XMJE01 = DECODE(as_FYFSLB,'1',ls_ZFJE00,'2',ls_JZJE00,'3',ls_GFJE00,ls_HJJE00)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
              --Update  BM_MZSRTJ  set  XMJE01 = NVL(ls_HJJE00,0)  where XMMC01 = ls_XMMC00 and ID0000 = ad_ID0000;
             END LOOP;
       CLOSE CUR_SF_MZHSXM_TJ0000;
    END IF;
    if as_BRFBMC = '所有病人' then
       --期间所有发生的费用(包括期间现金结算)
       if as_yyjc00<>'0' then
          declare CURSOR cur_sf_brfy00 IS
              select jflbid,sum(nvl(hjje00,0))hjje00 from SF_BRFY00 A
                     where A.CZRQ00 >= as_KSRQ00 and  A.CZRQ00||A.CZSJ00>=as_KSRQ00||as_KSSJ00 and
                           A.CZRQ00 <= as_JSRQ00 and  A.CZRQ00||A.CZSJ00<=as_JSRQ00||as_JSSJ00 and
                           ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID =1))
                           and a.CZYKS0 in (select bmbh00 from bm_bmbm00 where yyid00=V_YYID00_1)
                     group by jflbid
                     having sum(nvl(hjje00,0))<>0;
          begin
              for fy in cur_sf_brfy00 loop
              if fy.jflbid=1 then
                 select  min(XMBH01)  into  ls_XMBH00  from  BM_MZSRTJ  where  ID0000 = ad_ID0000 and XMMC02 is null;
                 Update  BM_MZSRTJ  set  XMBH02 = '93',XMJE02 = NVL(fy.HJJE00,0), XMMC02 = '预交金病人结算' where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00;
              elsif fy.jflbid=0 then
                 select  min(XMBH01)  into  ls_XMBH00  from  BM_MZSRTJ  where  ID0000 = ad_ID0000 and XMMC02 is null;
                 Update  BM_MZSRTJ  set  XMBH02 = '90',XMJE02 = NVL(fy.HJJE00,0) , XMMC02 = '现金病人结算'  where  ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00;
              end if;
              end loop;
          end;
       else
          declare CURSOR cur_sf_brfy00 IS
          select jflbid,sum(nvl(hjje00,0))hjje00 from SF_BRFY00 A
                 where A.CZRQ00 >= as_KSRQ00 and  A.CZRQ00||A.CZSJ00>=as_KSRQ00||as_KSSJ00 and
                       A.CZRQ00 <= as_JSRQ00 and  A.CZRQ00||A.CZSJ00<=as_JSRQ00||as_JSSJ00 and
                       ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID =1))
                 group by jflbid
                 having sum(nvl(hjje00,0))<>0;

          begin
              for fy in cur_sf_brfy00 loop
              if fy.jflbid=1 then
                 select  min(XMBH01)  into  ls_XMBH00  from  BM_MZSRTJ  where  ID0000 = ad_ID0000 and XMMC02 is null;
                 Update  BM_MZSRTJ  set  XMBH02 = '93',XMJE02 = NVL(fy.HJJE00,0), XMMC02 = '预交金病人结算' where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00;
              elsif fy.jflbid=0 then
                 select  min(XMBH01)  into  ls_XMBH00  from  BM_MZSRTJ  where  ID0000 = ad_ID0000 and XMMC02 is null;
                 Update  BM_MZSRTJ  set  XMBH02 = '90',XMJE02 = NVL(fy.HJJE00,0) , XMMC02 = '现金病人结算'  where  ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00;
              end if;
              end loop;
          end;
    end if;
    else
       --期间所有发生的费用(包括期间现金结算)
       if as_yyjc00<>'0' then
          declare CURSOR cur_sf_brfy00 IS
              select jflbid,sum(nvl(hjje00,0))hjje00 from SF_BRFY00 A,BM_BRXXB0 B
                     where A.BRID00 = B.BRID00 and A.CZRQ00>=as_KSRQ00 and A.CZRQ00||A.CZSJ00>=as_KSRQ00||as_KSSJ00
                           and A.CZRQ00<=as_JSRQ00 and A.CZRQ00||A.CZSJ00<=as_JSRQ00||as_JSSJ00
                           and (B.FBBH00,B.YBLB00) in (select fbbh00,yblb00 from IC_YBBRLB where YBMC00=as_BRFBMC)
                           and a.CZYKS0 in (select bmbh00 from bm_bmbm00 where yyid00=V_YYID00_1)
                     group by jflbid
                     having sum(nvl(hjje00,0))<>0;
          begin
              for fy in cur_sf_brfy00 loop
                  if fy.jflbid=1 and as_TJLB00 in ('0','2') then
                     select  min(XMBH01)  into  ls_XMBH00  from  BM_MZSRTJ  where  ID0000 = ad_ID0000 and XMMC02 is null;
                     Update  BM_MZSRTJ  set  XMBH02 = '93',XMJE02 = NVL(fy.HJJE00,0), XMMC02 = '预交金病人结算' where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00;
                  elsif fy.jflbid=0 and as_TJLB00 in ('0','1') then
                     select  min(XMBH01)  into  ls_XMBH00  from  BM_MZSRTJ  where  ID0000 = ad_ID0000 and XMMC02 is null;
                     Update  BM_MZSRTJ  set  XMBH02 = '90',XMJE02 = NVL(fy.HJJE00,0) , XMMC02 = '现金病人结算'  where  ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00;
                  end if;
                  end loop;
          end;
    --end if;
       else
          declare CURSOR cur_sf_brfy00 IS
          select jflbid,sum(nvl(hjje00,0))hjje00 from SF_BRFY00 A,BM_BRXXB0 B
                 where A.BRID00 = B.BRID00 and A.CZRQ00>=as_KSRQ00 and A.CZRQ00||A.CZSJ00>=as_KSRQ00||as_KSSJ00
                       and A.CZRQ00<=as_JSRQ00 and A.CZRQ00||A.CZSJ00<=as_JSRQ00||as_JSSJ00
                       and (B.FBBH00,B.YBLB00) in (select fbbh00,yblb00 from IC_YBBRLB where YBMC00=as_BRFBMC)
                 group by jflbid
                 having sum(nvl(hjje00,0))<>0;

           begin
               for fy in cur_sf_brfy00 loop
                   if fy.jflbid=1 and as_TJLB00 in ('0','2') then
                      select  min(XMBH01)  into  ls_XMBH00  from  BM_MZSRTJ  where  ID0000 = ad_ID0000 and XMMC02 is null;
                      Update  BM_MZSRTJ  set  XMBH02 = '93',XMJE02 = NVL(fy.HJJE00,0), XMMC02 = '预交金病人结算' where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00;
                    elsif fy.jflbid=0 and as_TJLB00 in ('0','1') then
                      select  min(XMBH01)  into  ls_XMBH00  from  BM_MZSRTJ  where  ID0000 = ad_ID0000 and XMMC02 is null;
                    Update  BM_MZSRTJ  set  XMBH02 = '90',XMJE02 = NVL(fy.HJJE00,0) , XMMC02 = '现金病人结算'  where  ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00;
                    end if;
                    end loop;
            end;
       end if;
    end if;
    --医保病人医保中心支付

    --先整理到临时表

    if as_yyjc00<>'0' then
       declare CURSOR cur_sf_ybjfb0 IS
              select a.jflbid,b.yblb00,b.fbbh00,sum(nvl(a.jfje00,0)) jfje00
              from   sf_brjfb0 a,sf_jzb000 b,sf_brxxb0 c
              where  a.brid00=b.brid00 and a.jzdh00=b.jzdh00 and a.jfrq00>=as_ksrq00 and A.jfrq00||A.jfsj00>=as_KSRQ00||as_KSSJ00 and
                     a.zffs00 in(3,4)  and a.jfrq00<=as_jsrq00 and A.jfrq00||A.jfsj00<=as_JSRQ00||as_JSSJ00 and
                     (b.yblb00,b.fbbh00)in(select yblb00,fbbh00 from ic_ybbrlb where YBMC00=as_BRFBMC or as_BRFBMC='所有病人')and
                     ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1))
                     and B.MZID00=C.GHID00 AND C.JZKS00  in (select d.bmbh00 from bm_bmbm00 d where d.yyid00=V_YYID00_1)
              group  by a.jflbid,b.yblb00,b.fbbh00
              having sum(nvl(a.jfje00,0))<>0;
       begin
           for yb in cur_sf_ybjfb0 loop
               select YBMC00,PXH000 into ls_YBMC00,LS_PXH000 from ic_ybbrlb where fbbh00=yb.fbbh00 and yblb00=yb.yblb00;
               Update  BM_MZSRTJ  set  XMHJ00 =nvl(XMHJ00,0)+yb.JFJE00 where  ID0000 = ad_ID0000
                   and XMMC01= ls_YBMC00 and XMMC02 = ls_YBMC00 and BZ0000='9';
               if  SQL%NOTFOUND  then
                   insert  into  BM_MZSRTJ(ID0000,XMBH01,XMHJ00,XMMC01,XMMC02,BZ0000) values(ad_ID0000,LS_PXH000,yb.JFJE00,ls_YBMC00,ls_YBMC00,'9');
                 end if;
                 if yb.jflbid=0 then--现金结算病人
                  Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-yb.jFJE00  where  ID0000 = ad_ID0000 and XMBH02 = '90';
               else--预交金结算病人
                  Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-yb.jFJE00   where  ID0000 = ad_ID0000 and XMBH02 = '93';
               end if;

           end loop;
     end;
    else
       declare cursor cur_sf_ybjfb0 is
            select a.jflbid,b.yblb00,b.fbbh00,sum(nvl(a.jfje00,0)) jfje00
            from   sf_brjfb0 a,sf_jzb000 b
            where  a.brid00=b.brid00 and a.jzdh00=b.jzdh00 and a.jfrq00>=as_ksrq00 and A.jfrq00||A.jfsj00>=as_KSRQ00||as_KSSJ00 and
                   a.zffs00 in(3,4)  and a.jfrq00<=as_jsrq00 and A.jfrq00||A.jfsj00<=as_JSRQ00||as_JSSJ00 and
                   (b.yblb00,b.fbbh00)in(select yblb00,fbbh00 from ic_ybbrlb where YBMC00=as_BRFBMC or as_BRFBMC='所有病人')and
                   ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1))
            group  by a.jflbid,b.yblb00,b.fbbh00
            having sum(nvl(a.jfje00,0))<>0;
    begin
        for yb in cur_sf_ybjfb0 loop
          select YBMC00,PXH000 into ls_YBMC00,LS_PXH000 from ic_ybbrlb where fbbh00=yb.fbbh00 and yblb00=yb.yblb00;
           Update  BM_MZSRTJ  set  XMHJ00 =nvl(XMHJ00,0)+yb.JFJE00 where  ID0000 = ad_ID0000 and XMMC01= ls_YBMC00 and XMMC02 = ls_YBMC00 and BZ0000='9';
            if  SQL%NOTFOUND  then
                 insert  into  BM_MZSRTJ(ID0000,XMBH01,XMHJ00,XMMC01,XMMC02,BZ0000) values(ad_ID0000,LS_PXH000,yb.JFJE00,ls_YBMC00,ls_YBMC00,'9');
              end if;
             if yb.jflbid=0 then--现金结算病人
                  Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-yb.jFJE00  where  ID0000 = ad_ID0000 and XMBH02 = '90';
               else--预交金结算病人
                  Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-yb.jFJE00   where  ID0000 = ad_ID0000 and XMBH02 = '93';
           end if;
      end loop;
    end;
    end if;
  --临时汇总下

  --医院支付
  if as_yyjc00<>'0' then
       declare CURSOR cur_YYZFJE IS
              select a.jflbid,sum(nvl(a.jfje00,0)) jfje00
              from   sf_brjfb0 a,sf_jzb000 b,sf_brxxb0 c
              where  a.brid00=b.brid00 and a.jzdh00=b.jzdh00 and a.jfrq00>=as_ksrq00 and A.jfrq00||A.jfsj00>=as_KSRQ00||as_KSSJ00 and
                     a.zffs00 in(20)  and a.jfrq00<=as_jsrq00 and A.jfrq00||A.jfsj00<=as_JSRQ00||as_JSSJ00 and
                     (b.yblb00,b.fbbh00)in(select yblb00,fbbh00 from ic_ybbrlb where YBMC00=as_BRFBMC or as_BRFBMC='所有病人')and
                     ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1))
                     and B.MZID00=C.GHID00 AND C.JZKS00  in (select d.bmbh00 from bm_bmbm00 d where d.yyid00=V_YYID00_1)
              group  by a.jflbid
              having sum(nvl(a.jfje00,0))<>0;
       begin
           for yb in cur_YYZFJE loop
                select  min(XMBH01)  into  ls_XMBH00  from  BM_MZSRTJ  where  ID0000 = ad_ID0000 and XMMC02 is null;
                 if yb.jflbid=0 then--现金结算病人
                  Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-yb.jFJE00  where  ID0000 = ad_ID0000 and XMBH02 = '90';
               else--预交金结算病人
                  Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-yb.jFJE00   where  ID0000 = ad_ID0000 and XMBH02 = '93';
               end if;

               Update  BM_MZSRTJ  set  XMBH02 = '95',XMJE02 = nvl(XMJE02,0)+nvl(yb.jFJE00,0), XMMC02 = '医院支付' where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00;


           end loop;
     end;
    else
       declare cursor cur_YYZFJE is
            select a.jflbid,sum(nvl(a.jfje00,0)) jfje00
            from   sf_brjfb0 a,sf_jzb000 b
            where  a.brid00=b.brid00 and a.jzdh00=b.jzdh00 and a.jfrq00>=as_ksrq00 and A.jfrq00||A.jfsj00>=as_KSRQ00||as_KSSJ00 and
                   a.zffs00 in(20)  and a.jfrq00<=as_jsrq00 and A.jfrq00||A.jfsj00<=as_JSRQ00||as_JSSJ00 and
                   (b.yblb00,b.fbbh00)in(select yblb00,fbbh00 from ic_ybbrlb where YBMC00=as_BRFBMC or as_BRFBMC='所有病人')and
                   ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1))
            group  by a.jflbid
            having sum(nvl(a.jfje00,0))<>0;
    begin
       for yb in cur_YYZFJE loop
                select  min(XMBH01)  into  ls_XMBH00  from  BM_MZSRTJ  where  ID0000 = ad_ID0000 and XMMC02 is null;
                 if yb.jflbid=0 then--现金结算病人
                  Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-yb.jFJE00  where  ID0000 = ad_ID0000 and XMBH02 = '90';
               else--预交金结算病人
                  Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-yb.jFJE00   where  ID0000 = ad_ID0000 and XMBH02 = '93';
               end if;

               Update  BM_MZSRTJ  set  XMBH02 = '95',XMJE02 = nvl(XMJE02,0)+nvl(yb.jFJE00,0), XMMC02 = '医院支付' where ID0000=ad_ID0000 and XMBH01 = ls_XMBH00;


           end loop;
    end;
    end if;
  --临时汇总下




  insert  into  BM_MZSRTJ(ID0000,XMHJ00,XMBH01,XMMC01,XMMC02,BZ0000)
  Select ad_ID0000,Sum(XMHJ00)XMHJ00,XMBH01,XMMC01,XMMC02,'99' from BM_MZSRTJ
  where  ID0000=ad_ID0000 and  BZ0000='9' group by XMBH01,XMMC01,XMMC02;
  delete BM_MZSRTJ where  ID0000=ad_ID0000 and  BZ0000='9';
  declare CURSOR cur_sf_ybjfb0 IS
  select XMBH01,XMMC02,XMHJ00 as JFJE00 from BM_MZSRTJ where ID0000=ad_ID0000 and  BZ0000='99' order by XMBH01,XMMC02;
  begin
    for yb in cur_sf_ybjfb0 loop
          if as_yyjc00<>'0' then
            begin
           select nvl(sum(b.YBYL13),0) into LS_DBBCJE
            from   sf_brjfb0 a,sf_jzb000 b,sf_brxxb0 c
              where  a.brid00=b.brid00
                    and a.jzdh00=b.jzdh00
                    and a.zffs00 in(4)
                    and b.ybyl13<>0
                    and a.jfrq00>=as_ksrq00 and A.jfrq00||A.jfsj00>=as_KSRQ00||as_KSSJ00
                     and a.jfrq00<=as_jsrq00 and A.jfrq00||A.jfsj00<=as_JSRQ00||as_JSSJ00
                     and (b.yblb00,b.fbbh00)in(select yblb00,fbbh00 from ic_ybbrlb where YBMC00=yb.XMMC02)
                     and ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1))
                     and B.MZID00=C.GHID00 and C.JZKS00  in (select d.bmbh00 from bm_bmbm00 d where d.yyid00=V_YYID00_1);
         exception
          when others then
          LS_DBBCJE:=0;
         end;

        if LS_DBBCJE<>0 then
         Update  BM_MZSRTJ  set  XMHJ00 =nvl(XMHJ00,0)-nvl(LS_DBBCJE,0) where  ID0000 = ad_ID0000  and XMMC02 = yb.XMMC02 and BZ0000='99';
         Update  BM_MZSRTJ  set  XMHJ00 =nvl(XMHJ00,0)+LS_DBBCJE where  ID0000 = ad_ID0000  and XMMC02 = yb.XMMC02||'(大病补偿)' and BZ0000='99';
          if  SQL%NOTFOUND  then
               insert  into  BM_MZSRTJ(ID0000,XMBH01,XMHJ00,XMMC01,XMMC02,BZ0000) values(ad_ID0000,yb.XMBH01,LS_DBBCJE,yb.XMMC02,yb.XMMC02||'(大病补偿)','99');
           end if;
          end if;
        else
             begin
           select nvl(sum(b.YBYL13),0) into LS_DBBCJE
            from   sf_brjfb0 a,sf_jzb000 b
            where  a.brid00=b.brid00 and a.jzdh00=b.jzdh00  and a.zffs00 in(4) and b.ybyl13<>0
                    and a.jfrq00>=as_ksrq00 and A.jfrq00||A.jfsj00>=as_KSRQ00||as_KSSJ00
                    and a.jfrq00<=as_jsrq00 and A.jfrq00||A.jfsj00<=as_JSRQ00||as_JSSJ00
                    and(b.yblb00,b.fbbh00)in(select yblb00,fbbh00 from ic_ybbrlb where YBMC00=yb.XMMC02)
                   and((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1));
          exception
           when others then
           LS_DBBCJE:=0;
          end ;

         if LS_DBBCJE<>0 then
         Update  BM_MZSRTJ  set  XMHJ00 =nvl(XMHJ00,0)-nvl(LS_DBBCJE,0) where  ID0000 = ad_ID0000  and XMMC02 = yb.XMMC02 and BZ0000='99';
         Update  BM_MZSRTJ  set  XMHJ00 =nvl(XMHJ00,0)+LS_DBBCJE where  ID0000 = ad_ID0000  and XMMC02 = yb.XMMC02||'(大病补偿)' and BZ0000='99';
          if  SQL%NOTFOUND  then
               insert  into  BM_MZSRTJ(ID0000,XMBH01,XMHJ00,XMMC01,XMMC02,BZ0000) values(ad_ID0000,yb.XMBH01,LS_DBBCJE,yb.XMMC02,yb.XMMC02||'(大病补偿)','99');
           end if;
          end if;
    end if;
    end loop;
  end;

   declare CURSOR cur_sf_ybjfb0 IS
  select XMMC02,XMHJ00 as JFJE00 from BM_MZSRTJ where ID0000=ad_ID0000 and  BZ0000='99' order by XMBH01,XMMC01,XMMC02;
  begin
    for yb in cur_sf_ybjfb0 loop
        select  min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null;
        Update  BM_MZSRTJ  set  XMJE02 = yb.JFJE00 , XMMC02 = yb.XMMC02  where  ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00;
            if  SQL%NOTFOUND  then
                insert  into  BM_MZSRTJ(ID0000,XMJE02,XMMC02) values(ad_ID0000,yb.JFJE00,yb.XMMC02);
            end if;
    end loop;
  end;

  delete BM_MZSRTJ where  ID0000=ad_ID0000 and  BZ0000='99';

    --公费病人发生的记帐费用
    if as_yyjc00<>'0' then
    declare CURSOR cur_sf_gfjfb0 IS
             select jflbid,sum(nvl(a.jzje00,0)) jfje00,b.yblb00,b.fbbh00
             from   sf_brfy00 a,bm_brxxb0 b
             where  a.brid00=b.brid00 and a.czrq00>=as_ksrq00 and A.czrq00||A.czsj00>=as_KSRQ00||as_KSSJ00 and
                    a.czrq00<=as_jsrq00 and A.czrq00||A.czsj00<=as_JSRQ00||as_JSSJ00 and
                    (b.yblb00,b.fbbh00) in (select yblb00,fbbh00 from ic_ybbrlb
                                            where (YBMC00=as_BRFBMC or as_BRFBMC='所有病人') and
                                                   b.fbbh00 not in ('1','3') ) and
                    ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1))
                    and a.CZYKS0 in (select bmbh00 from bm_bmbm00 where yyid00=V_YYID00_1)
             group  by a.jflbid,b.yblb00,b.fbbh00
             having sum(nvl(a.jzje00,0))<>0;
         begin
      for yb in cur_sf_gfjfb0 loop
          select YBMC00 into ls_YBMC00 from ic_ybbrlb where fbbh00=yb.fbbh00 and yblb00=yb.yblb00;
           Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)+yb.JFJE00 where  ID0000 = ad_ID0000 and XMMC02 = ls_YBMC00||'(记帐)';
           if sql%notfound then
              select  min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null;
              Update  BM_MZSRTJ  set  XMJE02 = yb.JFJE00 , XMMC02 = ls_YBMC00||'(记帐)'  where  ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00;
          if  SQL%NOTFOUND  then
              insert  into  BM_MZSRTJ(ID0000,XMJE02,XMMC02) values(ad_ID0000,yb.JFJE00,ls_YBMC00||'(记帐)');
          end if;
              if yb.jflbid=0 then--现金结算病人
                 Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-yb.jFJE00  where  ID0000 = ad_ID0000 and XMBH02 = '90';
              else--预交金结算病人
                Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-yb.jFJE00   where  ID0000 = ad_ID0000 and XMBH02 = '93';
              end if;
           end if;
      end loop;
    end;
     else
         declare CURSOR cur_sf_gfjfb0 IS
             select jflbid,sum(nvl(a.jzje00,0)) jfje00,b.yblb00,b.fbbh00
             from   sf_brfy00 a,bm_brxxb0 b
             where  a.brid00=b.brid00 and a.czrq00>=as_ksrq00 and A.czrq00||A.czsj00>=as_KSRQ00||as_KSSJ00 and
                    a.czrq00<=as_jsrq00 and A.czrq00||A.czsj00<=as_JSRQ00||as_JSSJ00 and
                    (b.yblb00,b.fbbh00) in (select yblb00,fbbh00 from ic_ybbrlb
                                            where (YBMC00=as_BRFBMC or as_BRFBMC='所有病人') and
                                                   b.fbbh00 not in ('1','3') ) and
                    ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1))
             group  by a.jflbid,b.yblb00,b.fbbh00
             having sum(nvl(a.jzje00,0))<>0;

    begin
      for yb in cur_sf_gfjfb0 loop
          select YBMC00 into ls_YBMC00 from ic_ybbrlb where fbbh00=yb.fbbh00 and yblb00=yb.yblb00;
          Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)+yb.JFJE00 where  ID0000 = ad_ID0000 and XMMC02 = ls_YBMC00||'(记帐)';
          if sql%notfound then
             select  min(XMBH01) into ls_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null;
             Update  BM_MZSRTJ  set  XMJE02 = yb.JFJE00 , XMMC02 = ls_YBMC00||'(记帐)'  where  ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00;
            if  SQL%NOTFOUND  then
              insert  into  BM_MZSRTJ(ID0000,XMJE02,XMMC02) values(ad_ID0000,yb.JFJE00,ls_YBMC00||'(记帐)');
            end if;
              if yb.jflbid=0 then--现金结算病人
                 Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-yb.jFJE00  where  ID0000 = ad_ID0000 and XMBH02 = '90';
              else--预交金结算病人
                 Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-yb.jFJE00   where  ID0000 = ad_ID0000 and XMBH02 = '93';
              end if;
         end if;
      end loop;
    end;
    end if;

    --光泽县医院 228215 体检统计  by  MZSF-20170428-002 --begin--
    --GHLB00=5:体检号
    if trim(V_YYID00_1) in ('228215') then

         declare cursor CUR_SF_TJFY00 is
          select A.JFLBID,sum(nvl(HJJE00,0))HJJE00
            from SF_BRFY00 A,BM_BRXXB0 B
          where 1=1
           and A.BRID00 = B.BRID00
           and A.CZRQ00>=as_KSRQ00
           and A.CZRQ00<=as_JSRQ00
           and A.CZRQ00||A.CZSJ00>=as_KSRQ00||as_KSSJ00
           and A.CZRQ00||A.CZSJ00<=as_JSRQ00||as_JSSJ00
           and (B.FBBH00,B.YBLB00) in (select FBBH00,YBLB00 from IC_YBBRLB where (YBMC00=as_BRFBMC or as_BRFBMC='所有病人'))
           and exists (select 1 from SF_BRXXB0 AA where AA.GHID00=A.MZID00 and AA.GHH000=MZH000 and AA.BRID00=A.BRID00 and AA.GHLB00 in (5))
           group by JFLBID
           having sum(nvl(HJJE00,0))<>0;
     begin
      for TJ in CUR_SF_TJFY00 loop
          if TJ.JFLBID=0 then--现金结算病人
            update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)+TJ.HJJE00 where  ID0000 = ad_ID0000 and XMMC02 = '现金病人结算(体检)';
            if sql%notfound then
              select  min(XMBH01) into LS_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null;
              update  BM_MZSRTJ  set  XMJE02 = TJ.HJJE00, XMMC02 ='现金病人结算(体检)'  where  ID0000 = ad_ID0000 and XMBH01 = LS_XMBH00;
               if  sql%notfound  then
                 insert  into  BM_MZSRTJ(ID0000,XMJE02,XMMC02) values(ad_ID0000,TJ.HJJE00,'现金病人结算(体检)');
                end if;
                update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-TJ.HJJE00  where  ID0000 = ad_ID0000 and XMBH02 = '90';
            end if;
          else
            update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)+TJ.HJJE00 where  ID0000 = ad_ID0000 and XMMC02 = '预交金病人结算(体检)';
            if sql%notfound then
              select  min(XMBH01) into LS_XMBH00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMMC02 is null;
              update  BM_MZSRTJ  set  XMJE02 = TJ.HJJE00 , XMMC02 ='预交金病人结算(体检)'  where  ID0000 = ad_ID0000 and XMBH01 = LS_XMBH00;
                if  sql%notfound  then
                 insert  into  BM_MZSRTJ(ID0000,XMJE02,XMMC02) values(ad_ID0000,TJ.HJJE00,'预交金病人结算(体检)');
                end if;
            end if;
            update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-TJ.HJJE00  where  ID0000 = ad_ID0000 and XMBH02 = '93';
          end if;
      end loop;
     end;
    end if;
   --光泽县医院 228215 体检统计  by  MZSF-20170428-002 --end--

    --计算减免费用
    if as_yyjc00<>'0' then
       select sum(nvl(a.gfje00,0)) into ls_GFJE00
              from     sf_brfy00 a,bm_brxxb0 b
              where    a.brid00=b.brid00 and a.czrq00>=as_ksrq00 and A.czrq00||A.czsj00>=as_KSRQ00||as_KSSJ00 and
                       a.czrq00<=as_jsrq00 and A.czrq00||A.czsj00<=as_JSRQ00||as_JSSJ00 and
                       (b.yblb00,b.fbbh00) in (select yblb00,fbbh00 from ic_ybbrlb
                       where (YBMC00=as_BRFBMC or as_BRFBMC='所有病人')) and
                       ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1))
                       and a.CZYKS0 in (select bmbh00 from bm_bmbm00 where yyid00=V_YYID00_1)
                       and a.gfje00<>'0' and a.hjje00=0;
          if ls_GFJE00<>0 then
             update BM_MZSRTJ set XMJE02=ls_GFJE00,XMMC02='费用减免' where ID0000 = ad_ID0000 and XMMC02 is null and rownum=1;
             Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-ls_GFJE00   where  ID0000 = ad_ID0000 and XMBH02 = '93';
          end if;
     else
         select sum(nvl(a.gfje00,0)) into ls_GFJE00
                from     sf_brfy00 a,bm_brxxb0 b
                where    a.brid00=b.brid00 and a.czrq00>=as_ksrq00 and A.czrq00||A.czsj00>=as_KSRQ00||as_KSSJ00 and
                         a.czrq00<=as_jsrq00 and A.czrq00||A.czsj00<=as_JSRQ00||as_JSSJ00 and
                        (b.yblb00,b.fbbh00) in (select yblb00,fbbh00 from ic_ybbrlb
                        where (YBMC00=as_BRFBMC or as_BRFBMC='所有病人')) and
                        ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1))
                        and a.gfje00<>'0' and a.hjje00=0;
        if ls_GFJE00<>0 then
           update BM_MZSRTJ set XMJE02=ls_GFJE00,XMMC02='费用减免' where ID0000 = ad_ID0000 and XMMC02 is null and rownum=1;
           Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-ls_GFJE00   where  ID0000 = ad_ID0000 and XMBH02 = '93';
        end if;
    end if;

    --老年医院计算本院福利卡及医疗救助(FBBH00=21,22)
    declare cursor cur_sf_lnyy is
        select YYID00 from XT_YYXX00; --where rownum=1;
    begin
        open cur_sf_lnyy;
    loop
       FETCH cur_sf_lnyy INTO  V_YYID00;
          EXIT WHEN cur_sf_lnyy%NOTFOUND;
       IF V_YYID00='220035' and V_YYID00=V_YYID00_1 then  --省老年医院专用
           select nvl(sum(a.zfje00),0) into ls_GFJE00
              from  sf_brfy00 a,bm_brxxb0 b
              where  a.brid00=b.brid00 and a.czrq00>=as_ksrq00 and A.czrq00||A.czsj00>=as_KSRQ00||as_KSSJ00 and
                     a.czrq00<=as_jsrq00 and A.czrq00||A.czsj00<=as_JSRQ00||as_JSSJ00 and
                    (b.yblb00,b.fbbh00) in (select yblb00,fbbh00 from ic_ybbrlb
                                            where (YBMC00=as_BRFBMC or as_BRFBMC='所有病人')) and b.fbbh00='21' and
                    ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1));
           if  ls_GFJE00<>0 then
               update BM_MZSRTJ set XMJE02=ls_GFJE00,XMMC02='本院福利卡' where ID0000 = ad_ID0000 and XMMC02 is null and rownum=1;
               if sql%notfound then
                   insert  into  BM_MZSRTJ(ID0000,XMJE02,XMMC02) values(ad_ID0000,ls_GFJE00,'福利卡');
               end if;
               Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-ls_GFJE00   where  ID0000 = ad_ID0000 and XMBH02 = '93';
           end if;

           select nvl(sum(a.zfje00),0) into ls_GFJE00
              from  sf_brfy00 a,bm_brxxb0 b
              where  a.brid00=b.brid00 and a.czrq00>=as_ksrq00 and A.czrq00||A.czsj00>=as_KSRQ00||as_KSSJ00 and
                     a.czrq00<=as_jsrq00 and A.czrq00||A.czsj00<=as_JSRQ00||as_JSSJ00
                     and  (b.yblb00,b.fbbh00) in (select yblb00,fbbh00 from ic_ybbrlb
                                            where (YBMC00=as_BRFBMC or as_BRFBMC='所有病人')) and b.fbbh00='22' and
                    ((as_TJLB00='0')or(as_TJLB00='1' and a.JFLBID=0)or(as_TJLB00='2' and a.JFLBID=1));
           if ls_GFJE00<>0 then
               update BM_MZSRTJ set XMJE02=ls_GFJE00,XMMC02='医疗救助' where ID0000 = ad_ID0000 and XMMC02 is null and rownum=1;
               if sql%notfound then
                   insert  into  BM_MZSRTJ(ID0000,XMJE02,XMMC02) values(ad_ID0000,ls_GFJE00,'医疗救助');
               end if;
               Update  BM_MZSRTJ  set  XMJE02 =nvl(XMJE02,0)-ls_GFJE00   where  ID0000 = ad_ID0000 and XMBH02 = '93';
           end if;
       end if;
    end loop;
      close cur_sf_lnyy;
    end;
    --计算左边的合计数
    update  BM_MZSRTJ  set  XMJE01 = (select sum(XMJE01) from  BM_MZSRTJ where ID0000 = ad_ID0000
              and XMBH01 not in ('97','98') and to_number(nvl(XMBH01,'0'))<90)

     where  ID0000 = ad_ID0000 and XMBH01 = '00';

    --计算右边的合计数
    update  BM_MZSRTJ  set  XMJE02 = (select sum(XMJE02) from  BM_MZSRTJ where ID0000 = ad_ID0000)
                 where  ID0000 = ad_ID0000 and XMBH02 = '00';
    select xmje01,xmje02 into ls_je0001,ls_je0002 from bm_mzsrtj where id0000=ad_id0000 and xmbh02='00';
    ls_je0000:=nvl(ls_je0001,0)-nvl(ls_je0002,0);
    if nvl(ls_je0000,0)<>0 then--更新左右两边不平的数据
       update  BM_MZSRTJ  set  XMJE02 =nvl(xmje02,0)+nvl(ls_je0000,0) where  ID0000 = ad_ID0000 and XMBH02 = '00';
       begin
             update  BM_MZSRTJ  set  XMJE02 =nvl(xmje02,0)+nvl(ls_je0000,0) where  ID0000 = ad_ID0000 and XMBH02 = '93';
       exception
             WHEN OTHERS THEN
             select  min(XMBH01)  into  ls_XMBH00  from  BM_MZSRTJ  where  ID0000 = ad_ID0000 and XMMC02 is null;
             Update  BM_MZSRTJ  set  XMBH02 = '93',XMJE02 = NVL(ls_je0000,0), XMMC02 = '预交金病人结算'  where  ID0000 = ad_ID0000 and XMBH01 = ls_XMBH00;
       end;
    end IF;


    /*
    if trim(V_YYID00_1) in ('227049','228215') then --南平医院227049 --光泽县医院 228215
       insert into  BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMJE01)
       select  ad_ID0000,97,'  其中'||BMMC00,sum(A.HJJE00) XMJE00   from  SF_FYMX00 A ,SF_BRFY00 H,BM_BMBM00 C
          where  A.DJH000  = H.DJH000  and A.ZXKS00=C.BMBH00 AND A.XMBH00>9999999990
                 and  H.CZRQ00 >= as_KSRQ00 and H.CZRQ00 <= as_JSRQ00
                   and  H.CZRQ00||H.CZSJ00>=as_KSRQ00||as_KSSJ00
                   and  H.CZRQ00||H.CZSJ00<=as_JSRQ00||as_JSSJ00 group by BMMC00;
    end if;
  */
if as_XXLB00='1' then
   if trim(as_HSXMMC)='所有项目' then
      delete from BM_MZSRTJ where  id0000=ad_ID0000 and  trim(xmmc01) is null and trim(xmmc02) is null;
   else
      delete from BM_MZSRTJ where  id0000=ad_ID0000 and  trim(xmmc01) is null;-- and trim(xmmc02) is null;
   end if;
end if;
commit;
exception
      WHEN OTHERS THEN
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_MZFPSR_TJ0000('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','||
                    as_JSSJ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
        ROLLBACK;
END;