CREATE OR REPLACE PROCEDURE SP_ZY_KSSRHZ_RBB000
(
        as_KSRQ00    in    char,       --开始日期
        as_KSSJ00    in    char,       --开始时间
        as_JSRQ00    in    char,       --结束日期
        as_JSSJ00    in    char,       --结束时间
        as_TJLB00    in    char,       --统计类别,'0'-发票项目 '1'-一级核算项目 2-二级核算项目
        as_KSLB00    in    char,       --统计科室类别,'0':开单科室,'1':执行科室,'2':医生组
        as_KSBH00    in    char,       --统计的科室编号
        as_ybmc00    in    char,       --医保名称
        as_jzlb00    in    char,       --记账类别'0'-全部,'1'-自付,'2'-记账,'3'-减免
        as_YYID00    in    char,       --医院ID >0 各分院 =0全部
        ad_ID0000    out   number,     --统计报表对应的ID
        as_YHMSG0    out   varchar,    --存储过程提示的错误信息
        as_SYSMSG    out   varchar,    --系统提示的错误信息
        ad_YSZID0    in    number default -1  --统计医生组
)
as
        ls_FPBH00    BM_YYHSXM.BH0000%TYPE;
        ls_FPXMMC    BM_FPXM00.XMMC00%TYPE;
        ls_TJLB00    BM_FPXM00.BH0000%TYPE;
        ls_HJJE00    BM_MZSRTJ.xmje01%type;--ZY_FYMX00.HJJE00%TYPE;
        ls_SFTJGJJE  varchar2(10);
        ls_GJJE00    BM_MZSRTJ.xmje01%type;--ZY_FYMX00.HJJE00%TYPE;
        ls_KSLB00    char(1); --统计类型 '0':开单科室,'1':执行科室,'2':医生组,'3' 执行药品为药房
        cursor CUR_ZY_BM_FPXM00_TJ IS
             select  '0' TJLB00,BH0000,XMMC00  from  BM_FPXM00
           union all
             select  distinct '1' TJLB00,YJHSBH,HSXMMC  from  VW_BM_YJHSXM
           union all
             select  '2' TJLB00,EJHSBH,HSXMMC  from  VW_BM_EJHSXM;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- xzw      2008.02.22     根据分院进行统计KDBQ00->KDKS00;
-- jinfl    2013.01.09     解决统计一级核算时多条显示问题  ZYSF-20130109-001
-- linzy    2014.06.11     增加入参ad_YSZID0 统计医生组 by ZYSF-20140609-003
-- dsm      2016.07.01     增加购进金额的统计 for ZYSF-20160516-001
-- dsm      2019.07.10     连BM_BRXXB0的地方改ZY_BRXXB0 因为如果ZY_BRXXB0的费别如果和BM_BRXXB0不一致会导致查不出数据 ZYSF-20190710-003
-- dsm      2019.09.09     修正ZYSF-20190710-003的bug and D.ZYID00=C.ZYID00 for ZYSF-20190910-005
-- linbin   2020.07.31     药品的执行科室为药房系统参数ZY_ZYKSXMTJYPZXKSWYF控制 by ZYSF-20200721-002
-- ------- ------------ --------------------------------------------------------
begin

    begin
        --参数：住院科室收入统计报表药品执行科室是否为药房
       select decode(count(VALUE0),0,as_KSLB00,'3') into ls_KSLB00 from XT_XTCS00 where NAME00='ZY_ZYKSXMTJYPZXKSWYF' and trim(VALUE0)='Y';
    exception
       when others then
        ls_SFTJGJJE:='N'   ;
    end;

    begin
       select nvl(max(trim(VALUE0)),'N') into ls_SFTJGJJE from XT_XTCS00 where NAME00='SF_KSSRHZTJGJJE' and VALUE0='Y';
    exception
       when others then
        ls_SFTJGJJE:='N'   ;
    end;

    select  SQ_BM_MZSRTJ_ID0000.nextval  into  ad_ID0000  from  dual;
    if as_TJLB00='0' then
        insert into BM_MZSRTJ(ID0000,XMBH01,XMBH02,XMJE01,XMJE02,BZ0000)
           select ID0000,BH0000,KSBH00,sum(XMJE01),sum(XMJE02),ls_SFTJGJJE from (

              select ad_ID0000 ID0000,F.BH0000,
                     decode(ls_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0),'3',decode(sign(A.XMBH00-9999999901),-1,A.ZXKS00,nvl(J.YFBMBH,K.YFBMBH))) KSBH00,
                     decode(as_JZLB00,'0',sum(A.HJJE00),'1',sum(A.HJJE00-A.JZJE00-A.GFJE00),'2',sum(A.JZJE00),sum(A.GFJE00)) XMJE01,0 XMJE02 from (
                 select nvl(( select max(CFLSH0) from YF_ZYCF00 where DJH000 = B.DJH000 and YPDLBH = ( select YPDLBH from BM_YPLB00 where SFDM00=B.XMBH00 ) ),'') CFLSH0,
                        nvl(( select max(YPQLPC) from YF_YZYPSQ where DJH000 = B.DJH000 and YPDLBH = ( select YPDLBH from BM_YPLB00 where SFDM00=B.XMBH00 ) ),0) YPQLPC,
                        C.BRID00,C.ZYID00,B.XMBH00,B.KDKS00,B.ZXKS00,B.HJJE00,B.JZJE00,B.GFJE00,B.YSZID0
                   from ZY_FYMX00 B,ZY_BRFY00 C
                  where B.DJH000 = C.DJH000 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00
                    and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00
                    ) A, BM_YYSFXM E,BM_FPXM00 F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H, YF_ZYCF00 J, YF_YZYPSQ K
               where A.XMBH00 = E.SFXMID and A.KDKS00 = H.BMBH00 and E.ZYFPID = F.FPXMID
                 and D.BRID00 = A.BRID00 and D.ZYID00 = A.ZYID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00
                 and A.CFLSH0 = J.CFLSH0(+) and A.YPQLPC = K.YPQLPC(+) and (H.YYID00 = as_YYID00 OR as_YYID00 = '0')
                 and ((ls_KSLB00='0' and KDKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and ZXKS00=as_KSBH00)  or  as_KSBH00 = '-1')
                 and (ad_YSZID0=-1 or ad_YSZID0=nvl(A.YSZID0,0)) and (G.YBMC00=as_YBMC00  or  as_YBMC00 = '所有病人')
               group by F.BH0000,decode(ls_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0),'3',decode(sign(A.XMBH00-9999999901),-1,A.ZXKS00,nvl(J.YFBMBH,K.YFBMBH)))

              union all
              select ad_ID0000,F.BH0000,decode(ls_KSLB00,'0',I.DQKS00,'1',I.YFBMBH,'2',nvl(C.YSZID0,0),'3',I.YFBMBH) KSBH00,0,round(sum(J.YPZSL0*J.GJDJ00),2)
                from ZY_BRFY00 C,BM_YYSFXM E,BM_FPXM00 F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,YF_ZYCF00 I,YF_ZYCFMX J,BM_YPLB00 K
               where I.DJH000 = C.DJH000 and J.YPDLBH = K.LBBH00 and K.SFDM00 = E.SFXMID and I.DQKS00 = H.BMBH00
                 and E.ZYFPID = F.FPXMID and D.BRID00 = C.BRID00 and D.ZYID00 = C.ZYID00 and D.FBBH00 = G.FBBH00
                 and D.YBLB00 = G.YBLB00 and  C.CZRQ00 >= as_KSRQ00 and  C.CZRQ00 <= as_JSRQ00
                 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and  C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00
                 and ((ls_KSLB00='0' and I.DQKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and I.YFBMBH=as_KSBH00) or as_KSBH00 = '-1')
                 and (H.YYID00 = as_YYID00 or as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(C.YSZID0,0))
                 and (G.YBMC00 = as_YBMC00 or as_YBMC00 = '所有病人') and I.CFLSH0=J.CFLSH0 and ls_SFTJGJJE='Y'
               group by F.BH0000,decode(ls_KSLB00,'0',I.DQKS00,'1',I.YFBMBH,'2',nvl(C.YSZID0,0),'3',I.YFBMBH)

              union all
              select ad_ID0000,F.BH0000,decode(ls_KSLB00,'0',J.DQKS00,'1',J.YFBMBH,'2',nvl(C.YSZID0,0),'3',J.YFBMBH) KSBH00,0,round(sum(J.YPZSL0*J.GJDJ00),2)
                from ZY_BRFY00 C,BM_YYSFXM E,BM_FPXM00 F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,YF_YZYPSQ J,BM_YPLB00 K
               where J.DJH000 = C.DJH000 and J.YPDLBH = K.LBBH00 and K.SFDM00 = E.SFXMID and J.DQKS00 = H.BMBH00
                 and E.ZYFPID = F.FPXMID and D.BRID00 = C.BRID00 and D.ZYID00 = C.ZYID00 and D.FBBH00 = G.FBBH00
                 and D.YBLB00 = G.YBLB00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00
                 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00
                 and ((ls_KSLB00='0' and J.DQKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and J.YFBMBH=as_KSBH00)  or  as_KSBH00 = '-1')
                 and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(C.YSZID0,0))
                 and (G.YBMC00=as_YBMC00  or  as_YBMC00 = '所有病人') and ls_SFTJGJJE='Y'
               group by F.BH0000,decode(ls_KSLB00,'0',J.DQKS00,'1',YFBMBH,'2',nvl(C.YSZID0,0),'3',J.YFBMBH)
         ) group by ID0000,BH0000,KSBH00 ;

    elsif as_TJLB00='1' then
        insert  into BM_MZSRTJ(ID0000,XMBH01,XMBH02,XMJE01,XMJE02,BZ0000)
           select ID0000,BH0000,KSBH00,sum(XMJE01),sum(XMJE02),ls_SFTJGJJE from (
              select ad_ID0000 ID0000,F.YJHSBH BH0000,
                     decode(ls_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0),'3',decode(sign(A.XMBH00-9999999901),-1,A.ZXKS00,nvl(J.YFBMBH,K.YFBMBH))) KSBH00,
                     decode(as_JZLB00,'0',sum(A.HJJE00),'1',sum(A.HJJE00-A.JZJE00-A.GFJE00),'2',sum(A.JZJE00),sum(A.GFJE00)) XMJE01,0 XMJE02 from (
                 select nvl(( select max(CFLSH0) from YF_ZYCF00 where DJH000 = B.DJH000 and YPDLBH = ( select YPDLBH from BM_YPLB00 where SFDM00=B.XMBH00 ) ),'') CFLSH0,
                        nvl(( select max(YPQLPC) from YF_YZYPSQ where DJH000 = B.DJH000 and YPDLBH = ( select YPDLBH from BM_YPLB00 where SFDM00=B.XMBH00 ) ),0) YPQLPC,
                        C.BRID00,C.ZYID00,B.XMBH00,B.KDKS00,B.ZXKS00,B.HJJE00,B.JZJE00,B.GFJE00,B.YSZID0
                   from ZY_FYMX00 B,ZY_BRFY00 C
                  where B.DJH000 = C.DJH000 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00
                    and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00
                    ) A, BM_YYSFXM E,VW_BM_YJHSXM F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H, YF_ZYCF00 J, YF_YZYPSQ K
               where A.XMBH00 = E.SFXMID and A.KDKS00 = H.BMBH00 and E.HSXMID = F.HSXMID and D.BRID00 = A.BRID00
                 and D.ZYID00 = A.ZYID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00
                 and A.CFLSH0 = J.CFLSH0(+) and A.YPQLPC = K.YPQLPC(+)
                 and ((ls_KSLB00='0' and KDKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and ZXKS00=as_KSBH00)  or  as_KSBH00 = '-1')
                 and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(A.YSZID0,0))
                 and (G.YBMC00=as_YBMC00  or  as_YBMC00 = '所有病人')
               group by  F.YJHSBH,decode(ls_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0),'3',decode(sign(A.XMBH00-9999999901),-1,A.ZXKS00,nvl(J.YFBMBH,K.YFBMBH)))

              union all
              select ad_ID0000,F.YJHSBH,decode(ls_KSLB00,'0',I.DQKS00,'1',I.YFBMBH,'2',nvl(C.YSZID0,0),'3',I.YFBMBH) KSBH00,0,round(sum(J.YPZSL0*J.GJDJ00),2)
                from ZY_BRFY00 C,BM_YYSFXM E,VW_BM_YJHSXM F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,YF_ZYCF00 I,YF_ZYCFMX J,BM_YPLB00 K
               where I.DJH000 = C.DJH000 and J.YPDLBH = K.LBBH00 and K.SFDM00 = E.SFXMID and I.DQKS00 = H.BMBH00
                 and E.HSXMID = F.HSXMID and D.BRID00 = C.BRID00 and D.ZYID00 = C.ZYID00 and D.FBBH00 = G.FBBH00
                 and D.YBLB00 = G.YBLB00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00
                 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00
                 and ((ls_KSLB00='0' and I.DQKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and YFBMBH=as_KSBH00)  or  as_KSBH00 = '-1')
                 and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(C.YSZID0,0))
                 and (G.YBMC00=as_YBMC00  or  as_YBMC00 = '所有病人') and I.CFLSH0=J.CFLSH0 and ls_SFTJGJJE='Y'
               group by  F.YJHSBH,decode(ls_KSLB00,'0',I.DQKS00,'1',I.YFBMBH,'2',nvl(C.YSZID0,0),'3',I.YFBMBH)

              union all
              select ad_ID0000,F.YJHSBH,decode(ls_KSLB00,'0',J.DQKS00,'1',J.YFBMBH,'2',nvl(C.YSZID0,0),'3',J.YFBMBH) KSBH00,0,round(sum(J.YPZSL0*J.GJDJ00),2)
                from ZY_BRFY00 C,BM_YYSFXM E,VW_BM_YJHSXM F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,YF_YZYPSQ J,BM_YPLB00 K
               where J.DJH000 = C.DJH000 and J.YPDLBH = K.LBBH00 and K.SFDM00 = E.SFXMID and J.DQKS00 = H.BMBH00
                 and E.HSXMID = F.HSXMID and D.BRID00 = C.BRID00 and D.ZYID00 = C.ZYID00 and D.FBBH00 = G.FBBH00
                 and D.YBLB00 = G.YBLB00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00
                 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00
                 and ((ls_KSLB00='0' and J.DQKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and YFBMBH=as_KSBH00)  or  as_KSBH00 = '-1')
                 and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(C.YSZID0,0))
                 and (G.YBMC00=as_YBMC00  or  as_YBMC00 = '所有病人')  and ls_SFTJGJJE='Y'
               group by  F.YJHSBH,decode(ls_KSLB00,'0',J.DQKS00,'1',J.YFBMBH,'2',nvl(C.YSZID0,0),'3',J.YFBMBH)
         ) group by ID0000,BH0000,KSBH00 ;
    else
        insert  into BM_MZSRTJ(ID0000,XMBH01,XMBH02,XMJE01,XMJE02,BZ0000)
           select ID0000,BH0000,KSBH00,sum(XMJE01),sum(XMJE02),ls_SFTJGJJE from (
              select ad_ID0000 ID0000,F.EJHSBH BH0000,
                     decode(ls_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0),'3',decode(sign(A.XMBH00-9999999901),-1,A.ZXKS00,nvl(J.YFBMBH,K.YFBMBH))) KSBH00,
                     decode(as_JZLB00,'0',sum(A.HJJE00),'1',sum(A.HJJE00-A.JZJE00-A.GFJE00),'2',sum(A.JZJE00),sum(A.GFJE00)) XMJE01,0 XMJE02 from (
                 select nvl(( select max(CFLSH0) from YF_ZYCF00 where DJH000 = B.DJH000 and YPDLBH = ( select YPDLBH from BM_YPLB00 where SFDM00=B.XMBH00 ) ),'') CFLSH0,
                        nvl(( select max(YPQLPC) from YF_YZYPSQ where DJH000 = B.DJH000 and YPDLBH = ( select YPDLBH from BM_YPLB00 where SFDM00=B.XMBH00 ) ),0) YPQLPC,
                        C.DJH000,C.BRID00,C.ZYID00,B.XMBH00,B.KDKS00,B.ZXKS00,B.HJJE00,B.JZJE00,B.GFJE00,B.YSZID0
                   from ZY_FYMX00 B,ZY_BRFY00 C
                  where B.DJH000 = C.DJH000 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00
                    and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00
                    ) A, BM_YYSFXM E,VW_BM_EJHSXM F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H, YF_ZYCF00 J, YF_YZYPSQ K
               where A.XMBH00 = E.SFXMID and A.KDKS00 = H.BMBH00 and E.HSXMID = F.HSXMID and D.BRID00 = A.BRID00
                 and D.ZYID00 = A.ZYID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00
                 and A.CFLSH0 = J.CFLSH0(+) and A.YPQLPC = K.YPQLPC(+)
                 and ((ls_KSLB00='0' and KDKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and ZXKS00=as_KSBH00)  or  as_KSBH00 = '-1')
                 and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (G.YBMC00=as_YBMC00  or  as_YBMC00 = '所有病人')
                 and (ad_YSZID0=-1 or ad_YSZID0=nvl(A.YSZID0,0))
               group by F.EJHSBH,decode(ls_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0),'3',decode(sign(A.XMBH00-9999999901),-1,A.ZXKS00,nvl(J.YFBMBH,K.YFBMBH)))

              union all
              select ad_ID0000,F.EJHSBH,decode(ls_KSLB00,'0',I.DQKS00,'1',I.YFBMBH,'2',nvl(C.YSZID0,0),'3',I.YFBMBH) KSBH00,0,round(sum(J.YPZSL0*J.GJDJ00),2)
                from ZY_BRFY00 C,BM_YYSFXM E,VW_BM_EJHSXM F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,YF_ZYCF00 I,YF_ZYCFMX J,BM_YPLB00 K
               where I.DJH000 = C.DJH000 and J.YPDLBH = K.LBBH00 and K.SFDM00 = E.SFXMID and I.DQKS00 = H.BMBH00
                 and E.HSXMID = F.HSXMID and D.BRID00 = C.BRID00 and D.ZYID00 = C.ZYID00 and D.FBBH00 = G.FBBH00
                 and D.YBLB00 = G.YBLB00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00
                 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00
                 and ((ls_KSLB00='0' and I.DQKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and I.YFBMBH=as_KSBH00)  or  as_KSBH00 = '-1')
                 and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(C.YSZID0,0))
                 and (G.YBMC00=as_YBMC00  or  as_YBMC00 = '所有病人') and I.CFLSH0=J.CFLSH0 and ls_SFTJGJJE='Y'
               group by F.EJHSBH,decode(ls_KSLB00,'0',I.DQKS00,'1',I.YFBMBH,'2',nvl(C.YSZID0,0),'3',I.YFBMBH)

              union all
              select ad_ID0000,F.EJHSBH,decode(ls_KSLB00,'0',J.DQKS00,'1',J.YFBMBH,'2',nvl(C.YSZID0,0),'3',J.YFBMBH) KSBH00,0,round(sum(J.YPZSL0*J.GJDJ00),2)
                from ZY_BRFY00 C,BM_YYSFXM E,VW_BM_EJHSXM F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,YF_YZYPSQ J,BM_YPLB00 K
               where J.DJH000 = C.DJH000 and J.YPDLBH = K.LBBH00 and K.SFDM00 = E.SFXMID and J.DQKS00 = H.BMBH00
                 and E.HSXMID = F.HSXMID and D.BRID00 = C.BRID00 and D.ZYID00 = C.ZYID00 and D.FBBH00 = G.FBBH00
                 and D.YBLB00 = G.YBLB00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00
                 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00
                 and ((ls_KSLB00='0' and J.DQKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and J.YFBMBH=as_KSBH00)  or  as_KSBH00 = '-1')
                 and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(C.YSZID0,0))
                 and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') and ls_SFTJGJJE='Y'
               group by  F.EJHSBH,decode(ls_KSLB00,'0',J.DQKS00,'1',J.YFBMBH,'2',nvl(C.YSZID0,0),'3',J.YFBMBH)
         ) group by ID0000,BH0000,KSBH00 ;
    end if;
    if ls_KSLB00='2' then
      update BM_MZSRTJ A set XMMC02=(select YSZMC0 from ZS_YSZ000 where YSZID0=to_number(A.XMBH02)) where ID0000=ad_ID0000;
    else
      update BM_MZSRTJ A set XMMC02=(select BMMC00 from BM_BMBM00 where BMBH00=to_number(A.XMBH02)) where ID0000=ad_ID0000;
    end if;
   --添加一条合计数
    insert  into BM_MZSRTJ(ID0000,XMBH01,XMMC02,XMJE01,BZ0000,XMJE02)
    select ad_ID0000,'00',' 合计',sum(NVL(XMJE01,0)),ls_SFTJGJJE,sum(nvl(XMJE02,0))  from BM_MZSRTJ  where ID0000 = ad_ID0000 ;
    open CUR_ZY_BM_FPXM00_TJ;
    loop
          fetch CUR_ZY_BM_FPXM00_TJ into  ls_TJLB00,ls_FPBH00,ls_FPXMMC;
          exit when CUR_ZY_BM_FPXM00_TJ%NOTFOUND;
          if ls_TJLB00=as_TJLB00 then
              select  nvl(sum(XMJE01),0),nvl(sum(XMJE02),0) into  ls_HJJE00,ls_GJJE00  from  BM_MZSRTJ  where  ID0000 = ad_ID0000 and XMBH01 = ls_FPBH00;
              insert  into BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMBH02,XMMC02,XMJE01,BZ0000,XMJE02)  values(ad_ID0000,ls_FPBH00,ls_FPXMMC,'-1',' 小计',ls_HJJE00,ls_SFTJGJJE,ls_GJJE00);
          end if;
    end loop;
    close CUR_ZY_BM_FPXM00_TJ;
    commit;
exception
      when OTHERS then
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_KSSRHZ_RBB000('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','||
                    as_JSSJ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
                ROLLBACK;
end;