CREATE OR REPLACE PROCEDURE SP_ZY_JSSRHZBB
(
        as_KSRQ00    in    char,                   --开始日期
        as_KSSJ00    in    char,                   --开始时间
        as_JSRQ00    in    char,                   --结束日期
        as_JSSJ00    in    char,                   --结束时间
        ad_ID0000    out   number,                 --统计报表对应的ID
        as_YHMSG0    out   varchar,                --存储过程提示的错误信息
        as_SYSMSG    out   varchar,                --系统提示的错误信息
        ad_FBBH00    in    number  default -1,     --费别编号
        as_YBZXLB    in    char    default null,   --医保中心类别
        as_YBLB00    in    char    default null,   --医保类别
        as_TJLB00    in    char    default '0'     --统计类别 0按大中心统计 1按分中心统计
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- jlg        2016.05.25   新增 住院结算收入汇总报表 for ZYSF9-20160521-001
-- jlg        2017.12.04   增加ad_FBBH00入参 ZYSF9-20171101-001
-- jlg        2017.12.04   增加as_YBZXLB,as_YBLB00入参  ZYSF9-20171201-001
-- jlg        2017.12.05   增加as_TJLB00入参 ZYSF9-20171205-001
-- jlg        2017.12.07   分中心的也按全部费别统计 ZYSF9-20171207-001
-- jlg        2018.05.17   增加单病种医院支付金额的显示 ZYSF9-20180329-001
-- jlg        2018.08.10   增加’医保其它支付‘列；处理分中心多了的话，单病种医院支付金额被盖的问题 ZYSF9-20180810-002
-- jlg        2019.03.07   处理分中心数量大于发票项目数量的情况下，超出部分不能显示的问题 ZYSF9-20190306-001
-- linzetao   2020.04.14   修正当分中心数量大于发票项目数量的情况下，不能正常显示问题  ZYSF9-20200410-002
-- linzetao   2020.10.28   修正查询一年数据分中心显示不完整问题   ZYSF9-20201028-003
  ls_ROWNUM    number;
  ls_COUNT0    number;
  ls_BMBH00    BM_YYSFTJ.BMBH00%type;
  ls_BMMC00    BM_YYSFTJ.BMMC00%type;
  ls_YBMC00    IC_YBBRLB.YBMC00%type;
  lf_BRZJE0    ZY_JZB000.BRZJE0%type;
  lf_ZFJE00    ZY_JZB000.ZFJE00%type;
  lf_GRZHZF    ZY_JZB000.GRZHZF%type;
  lf_TCJJZF    ZY_JZB000.TCJJZF%type;
  lf_YYZFJE    ZY_JZB000.YBYL05%type;  --医院支付金额
  ls_VALUE0    XT_XTCS00.VALUE0%type;
  cursor CUR_BM_YYSFTJ is
    select ROWNUM,BMBH00,BMMC00 from (select BMBH00,BMMC00 from BM_YYSFTJ where ID0000 = ad_ID0000 order by BMBH00);

  --结果倒序排列，为了插入医院支付的金额
  cursor CUR_BM_YYSFTJ01 is
    select BMBH00,SJYSXM from BM_YYSFTJ where ID0000 = ad_ID0000 and BMBH00 <>99 order by BMBH00 desc;

  --医保结算金额--大中心
  cursor CUR_ZY_DZXJZJE is
    select b.YBMC00,sum(a.BRZJE0) BRZJE0,sum(a.ZFJE00) ZFJE00,sum(a.GRZHZF) GRZHZF,sum(a.TCJJZF) TCJJZF,sum(YBQTZF) YBQTZF,
           sum(decode(a.CXJZDH,0,decode(a.JZZT00,'0',decode(sign(a.YBYL05),1,a.YBYL05,0),0),0)) YYZFJEZ,
           sum(decode(a.CXJZDH,0,decode(a.JZZT00,'0',decode(sign(a.YBYL05),1,0,a.YBYL05),0),0)) YYZFJEF
      from ZY_JZB000 a,IC_YBBRLB b
    where a.FBBH00 = b.FBBH00
      and a.YBZXLB = b.YBLB00
      and (
            (ad_FBBH00=-1) or
            (
             (a.FBBH00 = ad_FBBH00) and
              (
                (ad_FBBH00 <> 3) or
                ((ad_FBBH00 = 3) and ((as_YBZXLB is null) or (a.YBZXLB = as_YBZXLB))and ((as_YBLB00 is null) or (a.YBLB00 = as_YBLB00)) )  --
              )
             )
          )
      and a.JZRQ00 >= as_KSRQ00
      and a.JZRQ00||a.JZSJ00 >= as_KSRQ00||as_KSSJ00
      and a.JZRQ00 <= as_JSRQ00
      and a.JZRQ00||a.JZSJ00 <= as_JSRQ00||as_JSSJ00
    group by b.YBZXLB,b.YBMC00
      order by b.YBZXLB;

  --医保结算金额--分中心
  cursor CUR_ZY_FZXJZJE is
    select b.YBMC00,sum(a.BRZJE0) BRZJE0,sum(a.ZFJE00) ZFJE00,sum(a.GRZHZF) GRZHZF,sum(a.TCJJZF) TCJJZF,sum(YBQTZF) YBQTZF,
           sum(decode(a.CXJZDH,0,decode(a.JZZT00,'0',decode(sign(a.YBYL05),1,a.YBYL05,0),0),0)) YYZFJEZ,
           sum(decode(a.CXJZDH,0,decode(a.JZZT00,'0',decode(sign(a.YBYL05),1,0,a.YBYL05),0),0)) YYZFJEF
      from ZY_JZB000 a,IC_YBBRLB b
    where a.FBBH00 = b.FBBH00
      and a.YBLB00 = b.YBLB00
      and (
            (ad_FBBH00=-1) or
            (
             (a.FBBH00 = ad_FBBH00) and
              (
                (ad_FBBH00 <> 3) or
                ((ad_FBBH00 = 3) and ((as_YBZXLB is null) or (a.YBZXLB = as_YBZXLB))and ((as_YBLB00 is null) or (a.YBLB00 = as_YBLB00)) )
              )
             )
          )
      and a.JZRQ00 >= as_KSRQ00
      and a.JZRQ00||a.JZSJ00 >= as_KSRQ00||as_KSSJ00
      and a.JZRQ00 <= as_JSRQ00
      and a.JZRQ00||a.JZSJ00 <= as_JSRQ00||as_JSSJ00
    group by b.YBLB00,b.YBMC00
      order by b.YBLB00;

begin

  select  SQ_BM_YYSFTJ_ID0000.nextval  into  ad_ID0000  from  dual;
  --住院结算收入汇总报表是否显示医院支付金额，1是0否，默认0
  select nvl(max(VALUE0),'0') into ls_VALUE0 from XT_XTCS00 where NAME00='ZY_JSSRHZBBSFXSYYZFJE';

  insert into BM_YYSFTJ(ID0000,BMBH00,BMMC00,XMHJ00)
  select ad_ID0000,a.XMBH00,a.XMMC00,sum(a.XMJE00) XMHJJE
    from ZY_JZMX00 a,ZY_JZB000 b
  where a.JZDH00 = b.JZDH00
        and (
            (ad_FBBH00=-1) or
            (
             (b.FBBH00 = ad_FBBH00) and
              (
                (ad_FBBH00 <> 3) or
                ((ad_FBBH00 = 3) and ((as_YBZXLB is null) or (b.YBZXLB = as_YBZXLB))and ((as_YBLB00 is null) or (b.YBLB00 = as_YBLB00)) )
              )
             )
          )
    and b.JZRQ00>= as_KSRQ00
    and b.JZRQ00||b.JZSJ00 >= as_KSRQ00||as_KSSJ00
    and b.JZRQ00<= as_JSRQ00
    and b.JZRQ00||b.JZSJ00 <= as_JSRQ00||as_JSSJ00
    group by a.XMMC00,a.XMBH00
  order by a.XMBH00;

  --增加记录数，解决分中心大于发票项目数导致有些分中心数据缺失的问题
  ls_COUNT0:=30;
  while ls_COUNT0 < 150 loop
    insert into BM_YYSFTJ(ID0000,BMBH00,BMMC00,XMHJ00) values(ad_ID0000,ls_COUNT0,'',0);
    ls_COUNT0:= ls_COUNT0 +1;
  end loop;


  ls_COUNT0:= 1;
  for tj in CUR_BM_YYSFTJ loop
    if tj.ROWNUM = ls_COUNT0 then
      ls_ROWNUM:= 1;
      if (as_TJLB00='1') then --按分中心统计
        for yb in CUR_ZY_FZXJZJE loop
          if ls_ROWNUM = ls_COUNT0 then  --发票项目统计数据集与分中心统计数据集同一行
            update BM_YYSFTJ set SJYSXM =yb.YBMC00, SFXM10 = yb.BRZJE0,SFXM11 = yb.ZFJE00,SFXM12 = yb.GRZHZF,SFXM13 = yb.TCJJZF,SFXM16 = yb.YBQTZF,SFXM14 = yb.YYZFJEZ,SFXM15 = yb.YYZFJEF
                where ID0000 = ad_ID0000 and BMBH00 = tj.BMBH00;
          end if;
          ls_ROWNUM:= ls_ROWNUM + 1;
        end loop;
      else
        for yb in CUR_ZY_DZXJZJE loop
          if ls_ROWNUM = ls_COUNT0 then  --同一行
            update BM_YYSFTJ set SJYSXM =yb.YBMC00, SFXM10 = yb.BRZJE0,SFXM11 = yb.ZFJE00,SFXM12 = yb.GRZHZF,SFXM13 = yb.TCJJZF,SFXM16 = yb.YBQTZF,SFXM14 = yb.YYZFJEZ,SFXM15 = yb.YYZFJEF
                where ID0000 = ad_ID0000 and BMBH00 = tj.BMBH00;
          end if;
          ls_ROWNUM:= ls_ROWNUM + 1;
        end loop;
      end if;
    end if;
    ls_COUNT0 := ls_COUNT0 + 1;
  end loop;

  --参数有设置进行单病种医院支付金额显示的
  if ls_VALUE0 ='1' then
    ls_COUNT0:= 0;
    for zf in CUR_BM_YYSFTJ01 loop
      if zf.SJYSXM is null then --从没有显示医保名称的行开始
          ls_COUNT0 := ls_COUNT0 + 1;
          if ls_COUNT0 = 1 then
              update BM_YYSFTJ a set SJYSXM = '单病种医院支付负数合计',SFXM10 = (select sum(nvl(SFXM15,0)) from BM_YYSFTJ where ID0000 = a.ID0000)
                  where ID0000 = ad_ID0000 and BMBH00 = zf.BMBH00;
          end if;
          if ls_COUNT0 = 2 then
              update BM_YYSFTJ a set SJYSXM = '单病种医院支付正数合计',SFXM10 = (select sum(nvl(SFXM14,0)) from BM_YYSFTJ where ID0000 = a.ID0000)
                  where ID0000 = ad_ID0000 and BMBH00 = zf.BMBH00;
          end if;
          if ls_COUNT0 = 3 then
              update BM_YYSFTJ a set SJYSXM = '单病种医院支付总额',SFXM10 = (select sum(nvl(SFXM14,0))+sum(nvl(SFXM15,0)) from BM_YYSFTJ where ID0000 = a.ID0000)
                  where ID0000 = ad_ID0000 and BMBH00 = zf.BMBH00;
          end if;
      end if;
    end loop;
    --如果不足显示单病种回复合计行数的，则直接新增显示单病种支付合计的函数
    if ls_COUNT0 <= 2 then --只显示到2行的,插入最后一行
      insert into BM_YYSFTJ(ID0000,BMBH00,BMMC00,SJYSXM,XMHJ00,SFXM10)
        select ad_ID0000,143,' ','单病种医院支付总额',0,sum(nvl(SFXM14,0))+sum(nvl(SFXM15,0))
          from BM_YYSFTJ where ID0000 = ad_ID0000;
    end if;
    if ls_COUNT0 <= 1 then --只显示到1行的，插入倒数第二行
      insert into BM_YYSFTJ(ID0000,BMBH00,BMMC00,SJYSXM,XMHJ00,SFXM10)
        select ad_ID0000,142,' ','单病种医院支付正数合计',0,sum(nvl(SFXM14,0))
          from BM_YYSFTJ where ID0000 = ad_ID0000;
    end if;
    if ls_COUNT0 = 0 then --都没有显示的，插入第一行
      insert into BM_YYSFTJ(ID0000,BMBH00,BMMC00,SJYSXM,XMHJ00,SFXM10)
        select ad_ID0000,141,' ','单病种医院支付负数合计',0,sum(nvl(SFXM15,0))
          from BM_YYSFTJ where ID0000 = ad_ID0000;
    end if;
  end if;

  --添加合计
  insert into BM_YYSFTJ(ID0000,BMBH00,BMMC00,XMHJ00,SFXM10,SFXM11,SFXM12,SFXM13,SFXM16)
    select ad_ID0000,149,'合计',sum(XMHJ00),sum(SFXM10),sum(SFXM11),sum(SFXM12),sum(SFXM13),sum(SFXM16)
      from BM_YYSFTJ where ID0000 = ad_ID0000;

  delete BM_YYSFTJ where ID0000 = ad_ID0000 and BMMC00 is null and SJYSXM is null;
  --commit;
exception
     when OTHERS then
                  as_YHMSG0:='出错原因未知！请记录此信息并和系统管理员联系！';
                  as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_JSSRHZBB',1,150);
                  rollback;
end;