CREATE OR REPLACE PROCEDURE SP_ZY_ZYHSSR_RBB000(AS_CKBQKS IN CHAR, --是否要按病区科室显示
                                                AS_KSRQ00 IN CHAR, --开始日期
                                                AS_KSSJ00 IN CHAR, --开始时间
                                                AS_JSRQ00 IN CHAR, --结束日期
                                                AS_JSSJ00 IN CHAR, --结束时间
                                                AS_KSMC00 IN CHAR, --病区名称
                                                AS_SFYB00 IN CHAR, --是否医保病人,'0':所有病人,'1':医保,'2':非医保
                                                AS_SFZY00 IN CHAR, --是否在院病人,'0':所有病人,'1':在院病人,'2':出院病人
                                                AS_SFJZSJ IN CHAR, --是否按记账时间,'Y':记帐时间,'N':住院时间
                                                AS_JZLB00 in char, --记账类别'0':全部,'1':自付,'2':记账,'3':减免
                                                AS_TJLB00 in char, --1:一级核算项目,2:二级核算项目
                                                AS_YYID00 in char, --医院ID >0 各分院 =0全部
                                                AD_ID0000 OUT NUMBER, --统计报表对应的ID
                                                AD_ID0001 OUT NUMBER, --统计报表对应的BM_GHKSTJ_KSDY00的ID0000
                                                AS_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息
                                                AS_SYSMSG OUT VARCHAR --系统提示的错误信息
                                                ) as
  LS_BMBH00 ZY_FYMX00.KDBQ00%TYPE; --部门编号
  LS_KSBH00 ZY_FYMX00.KDKS00%TYPE; --开单科室
  LS_XMBH00 ZY_FYMX00.XMBH00%TYPE;
  LS_HJJE00 ZY_FYMX00.HJJE00%TYPE;
  LS_BH0000 BM_YYHSXM.BH0000%TYPE;
  LS_ID0000 BM_YYSFTJ.ID0000%TYPE;
  LS_BHSX00 char(2);
  LS_HSMC00 VARCHAR2(20);
  LS_BHSX01 number(12);
  VCOUNTER  number(12);
  LS_RYRQ00 ZY_BRXXB0.RYRQ00%TYPE;
  LS_CYRQ00 ZY_BRXXB0.CYRQ00%TYPE;
  LS_BRZT00 ZY_BRXXB0.BRZT00%TYPE;
  LS_ZYTS00 number(5);
  LS_RYRQ01 varchar2(8);
  LS_CYRQ01 varchar2(8);

  CURSOR CUR_ZY_BQHSSR_FYMX00_YJHSXM IS
    SELECT F.YJHSBH,
           F.HSXMMC,
           A.KDBQ00,
           A.KDKS00,
           DECODE(AS_JZLB00,
                  '0',
                  sum(A.HJJE00),
                  '1',
                  sum(A.ZFJE00),
                  '2',
                  sum(A.JZJE00),
                  sum(A.GFJE00)) HJJE00
      FROM ZY_FYMX00    A,
           ZY_BRFY00    C,
           VW_BM_YJHSXM F,
           BM_YYSFXM    G,
           BM_BMBM00    H
     where A.DJH000 = C.DJH000
       and F.HSXMID = G.HSXMID
       and G.SFXMID = A.XMBH00
       and A.KDBQ00 = H.BMBH00
       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 (A.KDBQ00 = LS_BMBH00 or LS_BMBH00 = 0)
       and F.HSXMMC <> '伙食费' --泉州第三医院有伙食费，在算合计时不包含伙食费
       and (H.YYID00 = AS_YYID00 OR AS_YYID00 = '0')
     group by F.YJHSBH, F.HSXMMC, A.KDBQ00, A.KDKS00
     order by F.YJHSBH;

  CURSOR CUR_ZY_BQHSSR_FYMX00_EJHSXM IS
    SELECT F.EJHSBH,
           F.HSXMMC,
           A.KDBQ00,
           A.KDKS00,
           DECODE(AS_JZLB00,
                  '0',
                  sum(A.HJJE00),
                  '1',
                  sum(A.ZFJE00),
                  '2',
                  sum(A.JZJE00),
                  sum(A.GFJE00)) HJJE00
      FROM ZY_FYMX00    A,
           ZY_BRFY00    C,
           VW_BM_EJHSXM F,
           BM_YYSFXM    G,
           BM_BMBM00    H
     where A.DJH000 = C.DJH000
       and F.HSXMID = G.HSXMID
       and G.SFXMID = A.XMBH00
       and A.KDBQ00 = H.BMBH00
       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 (A.KDBQ00 = LS_BMBH00 or LS_BMBH00 = 0)
       and F.HSXMMC <> '伙食费' --泉州第三医院有伙食费，在算合计时不包含伙食费
       and (H.YYID00 = AS_YYID00 OR AS_YYID00 = '0')
     group by F.EJHSBH, F.HSXMMC, A.KDBQ00, A.KDKS00
     order by F.EJHSBH;

  CURSOR CUR_ZY_BQHSSR_FYMX01_YJHSXM IS
    SELECT F.YJHSBH,
           F.HSXMMC,
           A.KDBQ00,
           A.KDKS00,
           DECODE(AS_JZLB00,
                  '0',
                  sum(A.HJJE00),
                  '1',
                  sum(A.ZFJE00),
                  '2',
                  sum(A.JZJE00),
                  sum(A.GFJE00)) HJJE00
      FROM ZY_FYMX00    A,
           ZY_BRFY00    C,
           ZY_BRXXB0    E,
           VW_BM_YJHSXM F,
           BM_YYSFXM    G,
           BM_BMBM00    H
     where A.DJH000 = C.DJH000
       and C.ZYID00 = E.ZYID00
       and F.HSXMID = G.HSXMID
       and G.SFXMID = A.XMBH00
       and A.KDBQ00 = H.BMBH00
       and (AS_SFYB00 = '0' or (E.YBZYH0 = '0' and AS_SFYB00 = '2') or
           (E.YBZYH0 <> '0' and AS_SFYB00 = '1'))
       and (AS_SFZY00 = '0' or
           (AS_SFZY00 = '1' and
           NVL(CYRQ00, AS_JSRQ00 + '1') || NVL(CYSJ00, AS_JSSJ00) >
           AS_JSRQ00 || AS_JSSJ00) or
           (AS_SFZY00 = '2' and
           NVL(CYRQ00, AS_JSRQ00 + '1') || NVL(CYSJ00, AS_JSSJ00) <=
           AS_JSRQ00 || AS_JSSJ00 and
           NVL(CYRQ00, AS_JSRQ00) || NVL(CYSJ00, AS_JSSJ00) >=
           AS_KSRQ00 || AS_KSSJ00))
       and ((AS_SFJZSJ = 'Y' 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) or
           (AS_SFJZSJ = 'N' and
           (E.CYRQ00 is null or
           (E.CYRQ00 >= AS_KSRQ00 and
           E.CYRQ00 || E.CYSJ00 >= AS_KSRQ00 || AS_KSSJ00))))
       and (A.KDBQ00 = LS_BMBH00 or LS_BMBH00 = 0)
       and F.HSXMMC <> '伙食费' --泉州第三医院有伙食费，在算合计时不包含伙食费
       and (H.YYID00 = AS_YYID00 OR AS_YYID00 = '0')
     group by F.YJHSBH, F.HSXMMC, A.KDBQ00, A.KDKS00
     order by F.YJHSBH;

  CURSOR CUR_ZY_BQHSSR_FYMX01_EJHSXM IS
    SELECT F.EJHSBH,
           F.HSXMMC,
           A.KDBQ00,
           A.KDKS00,
           DECODE(AS_JZLB00,
                  '0',
                  sum(A.HJJE00),
                  '1',
                  sum(A.ZFJE00),
                  '2',
                  sum(A.JZJE00),
                  sum(A.GFJE00)) HJJE00
      FROM ZY_FYMX00    A,
           ZY_BRFY00    C,
           ZY_BRXXB0    E,
           VW_BM_EJHSXM F,
           BM_YYSFXM    G,
           BM_BMBM00    H
     where A.DJH000 = C.DJH000
       and C.ZYID00 = E.ZYID00
       and F.HSXMID = G.HSXMID
       and G.SFXMID = A.XMBH00
       and A.KDBQ00 = H.BMBH00
       and (AS_SFYB00 = '0' or (E.YBZYH0 = '0' and AS_SFYB00 = '2') or
           (E.YBZYH0 <> '0' and AS_SFYB00 = '1'))
       and (AS_SFZY00 = '0' or
           (AS_SFZY00 = '1' and
           NVL(CYRQ00, AS_JSRQ00 + '1') || NVL(CYSJ00, AS_JSSJ00) >
           AS_JSRQ00 || AS_JSSJ00) or
           (AS_SFZY00 = '2' and
           NVL(CYRQ00, AS_JSRQ00 + '1') || NVL(CYSJ00, AS_JSSJ00) <=
           AS_JSRQ00 || AS_JSSJ00 and
           NVL(CYRQ00, AS_JSRQ00) || NVL(CYSJ00, AS_JSSJ00) >=
           AS_KSRQ00 || AS_KSSJ00))
       and ((AS_SFJZSJ = 'Y' 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) or
           (AS_SFJZSJ = 'N' and
           (E.CYRQ00 is null or
           (E.CYRQ00 >= AS_KSRQ00 and
           E.CYRQ00 || E.CYSJ00 >= AS_KSRQ00 || AS_KSSJ00))))
       and (A.KDBQ00 = LS_BMBH00 or LS_BMBH00 = 0)
       and F.HSXMMC <> '伙食费' --泉州第三医院有伙食费，在算合计时不包含伙食费
       and (H.YYID00 = AS_YYID00 OR AS_YYID00 = '0')
     group by F.EJHSBH, F.HSXMMC, A.KDBQ00, A.KDKS00
     order by F.EJHSBH;

  CURSOR CUR_RSTJ IS
    Select BMBH00, SFXM99, sum(SFXM101) SFXM101
      from BM_YYSFTJ
     where ID0000 = AD_ID0000
       and SFXM106 = 99
    -- AND  ((as_SFZY00='1'AND SFXM105=1) OR (as_SFZY00='2'AND SFXM105=2) OR (as_SFZY00='0' AND 1=1))
     group by BMBH00, SFXM99;

  CURSOR CUR_TSTJ IS
    Select distinct BMBH00, SFXM99, SFXM103
      from BM_YYSFTJ
     where ID0000 = AD_ID0000
       and SFXM106 = 9
       AND ((AS_SFZY00 = '1' AND SFXM105 = 1) OR
           (AS_SFZY00 = '2' AND SFXM105 = 2) OR
           (AS_SFZY00 = '0' AND 1 = 1));

BEGIN
  LS_BHSX01 := 0;
  LS_BHSX00 := '00';
  if AS_KSMC00 = '所有病区' then
    LS_BMBH00 := 0;
  else
    begin
      select BMBH00 into LS_BMBH00 from BM_BMBM00 where BMMC00 = AS_KSMC00;
    exception
      when others then
        LS_BMBH00 := -1;
    end;
  end if;
  select SQ_BM_YYSFTJ_ID0000.NEXTVAL into AD_ID0000 from DUAL;
  if AS_SFYB00 = '0' and AS_SFZY00 = '0' and AS_SFJZSJ = 'Y' then
    if AS_TJLB00 = '1' then
      OPEN CUR_ZY_BQHSSR_FYMX00_YJHSXM;
    else
      OPEN CUR_ZY_BQHSSR_FYMX00_EJHSXM;
    end if;
  else
    if AS_TJLB00 = '1' then
      OPEN CUR_ZY_BQHSSR_FYMX01_YJHSXM;
    else
      OPEN CUR_ZY_BQHSSR_FYMX01_EJHSXM;
    end if;
  end if;
  LOOP
    if AS_SFYB00 = '0' and AS_SFZY00 = '0' and AS_SFJZSJ = 'Y' then
      if AS_TJLB00 = '1' then
        FETCH CUR_ZY_BQHSSR_FYMX00_YJHSXM
          INTO LS_BH0000, LS_HSMC00, LS_BMBH00, LS_KSBH00, LS_HJJE00;
        EXIT WHEN CUR_ZY_BQHSSR_FYMX00_YJHSXM%NOTFOUND;
      else
        FETCH CUR_ZY_BQHSSR_FYMX00_EJHSXM
          INTO LS_BH0000, LS_HSMC00, LS_BMBH00, LS_KSBH00, LS_HJJE00;
        EXIT WHEN CUR_ZY_BQHSSR_FYMX00_EJHSXM%NOTFOUND;
      end if;
    else
      if AS_TJLB00 = '1' then
        FETCH CUR_ZY_BQHSSR_FYMX01_YJHSXM
          INTO LS_BH0000, LS_HSMC00, LS_BMBH00, LS_KSBH00, LS_HJJE00;
        EXIT WHEN CUR_ZY_BQHSSR_FYMX01_YJHSXM%NOTFOUND;
      else
        FETCH CUR_ZY_BQHSSR_FYMX01_EJHSXM
          INTO LS_BH0000, LS_HSMC00, LS_BMBH00, LS_KSBH00, LS_HJJE00;
        EXIT WHEN CUR_ZY_BQHSSR_FYMX01_EJHSXM%NOTFOUND;
      end if;
    end if;
    select NVL(count(1), 0)
      into VCOUNTER
      from BM_GHKSTJ_KSDY00
     where ID0000 = AD_ID0000
       and trim(GHKSMC) = LS_BH0000;
    if VCOUNTER > 0 then
      select LPAD(TO_CHAR(GHKSBH), 2, '0')
        into LS_BHSX00
        from BM_GHKSTJ_KSDY00
       where ID0000 = AD_ID0000
         and GHKSMC = LS_BH0000;
    else
      LS_BHSX01 := LS_BHSX01 + 1;
      Select LPAD(TO_CHAR(LS_BHSX01), 2, '0') into LS_BHSX00 from DUAL;
      Insert into BM_GHKSTJ_KSDY00
        (ID0000, BH0000, GHKSMC, GHKSBH)
      values
        (AD_ID0000, 0, LS_BH0000, LS_BHSX00);
    end if;
    if LS_BHSX00 > '98' then
      AS_YHMSG0 := '住院病区收入统计失败,请察看详细信息并与系统管理员联系!';
      AS_SYSMSG := '所设置的核算项目超过98个';
      ROLLBACK;
      RETURN;
    end if;
    select count(*)
      into VCOUNTER
      from BM_YYSFTJ
     where BMBH00 = LS_BMBH00
       and ID0000 = AD_ID0000
       and SFXM99 = LS_KSBH00;
    if VCOUNTER > 0 then
      SP_EXECUTE_SQL('Update BM_YYSFTJ Set SFXM' || LS_BHSX00 ||
                     '= NVL(SFXM' || LS_BHSX00 || ',0)+' ||
                     TO_CHAR(LS_HJJE00) || ',XMHJ00=NVL(XMHJ00,0)+' ||
                     TO_CHAR(LS_HJJE00) || ' where  BMBH00 = ' ||
                     TO_CHAR(LS_BMBH00) || ' and ID0000=' ||
                     TO_CHAR(AD_ID0000) || ' and SFXM99 = ' ||
                     TO_CHAR(LS_KSBH00));
    else
      SP_EXECUTE_SQL('Insert into BM_YYSFTJ(BMBH00,SFXM' || LS_BHSX00 ||
                     ',ID0000,XMHJ00,SFXM99,SFXM100) values(' ||
                     TO_CHAR(LS_BMBH00) || ',' || TO_CHAR(LS_HJJE00) || ',' ||
                     TO_CHAR(AD_ID0000) || ',' || TO_CHAR(LS_HJJE00) || ',' ||
                     TO_CHAR(LS_KSBH00) || ',' || '99' || ')');
    end if;

    -- SELECT HSXMMC  INTO ls_HSMC00  FROM  bm_yyhsxm where BH0000=ls_BH0000;

    IF INSTRB(LS_HSMC00, '药费') > 0 then

      SP_EXECUTE_SQL('Update BM_YYSFTJ Set SFXM111=NVL(SFXM111,0)+' ||
                     TO_CHAR(LS_HJJE00) || ' where  BMBH00 = ' ||
                     TO_CHAR(LS_BMBH00) || ' and ID0000=' ||
                     TO_CHAR(AD_ID0000) || ' and SFXM99 = ' ||
                     TO_CHAR(LS_KSBH00));
    end if;

  END LOOP;
  if AS_SFYB00 = '0' and AS_SFZY00 = '0' and AS_SFJZSJ = 'Y' then
    if AS_TJLB00 = '1' then
      CLOSE CUR_ZY_BQHSSR_FYMX00_YJHSXM;
    else
      CLOSE CUR_ZY_BQHSSR_FYMX00_EJHSXM;
    end if;
  else
    if AS_TJLB00 = '1' then
      CLOSE CUR_ZY_BQHSSR_FYMX01_YJHSXM;
    else
      CLOSE CUR_ZY_BQHSSR_FYMX01_EJHSXM;
    end if;
  end if;
  AD_ID0001 := AD_ID0000;

  Insert into BM_YYSFTJ
    (ID0000, BMBH00, SFXM99, SFXM101, SFXM105, SFXM106)
    select AD_ID0000, A.BQH000, A.KSH000, count(distinct A.ZYID00), 1, 99
      from BQ_BRLDXX A, ZY_BRXXB0 B
     where QSZT00 in ('新入', '转入', '入院')
       AND YXBZ00 = '1'
       AND A.ZYID00 = B.ZYID00
       and (AS_SFYB00 = '0' or (AS_SFYB00 = '1' AND B.FBBH00 = 3) or
           (AS_SFYB00 = '2' AND B.FBBH00 <> 3))
       and QSRQ00 <= AS_JSRQ00
       and BQJSRQ > AS_JSRQ00
     group by AD_ID0000, A.BQH000, A.KSH000;

  Insert into BM_YYSFTJ
    (ID0000, BMBH00, SFXM99, SFXM101, SFXM105, SFXM106)
    select AD_ID0000, A.BQH000, A.KSH000, count(distinct A.ZYID00), 2, 99
      from BQ_BRLDXX A, ZY_BRXXB0 B
     where JSZT00 = '出院'
       AND YXBZ00 = '1'
       AND A.ZYID00 = B.ZYID00
       and JSRQ00 >= AS_KSRQ00
       and JSRQ00 <= AS_JSRQ00
       and (AS_SFYB00 = '0' or (AS_SFYB00 = '1' AND B.FBBH00 = 3) or
           (AS_SFYB00 = '2' AND B.FBBH00 <> 3))
     group by AD_ID0000, A.BQH000, A.KSH000;

  Insert into BM_YYSFTJ
    (ID0000, BMBH00, SFXM99, SFXM103, SFXM105, SFXM106)
    select distinct AD_ID0000, A.BQH000, A.KSH000, A.ZYID00, 1, 9
      from BQ_BRLDXX A, ZY_BRXXB0 B
     where QSZT00 in ('新入', '转入')
       and YXBZ00 = '1'
       AND A.ZYID00 = B.ZYID00
       and (AS_SFYB00 = '0' or (AS_SFYB00 = '1' AND B.FBBH00 = 3) or
           (AS_SFYB00 = '2' AND B.FBBH00 <> 3))
       and QSRQ00 <= AS_JSRQ00
       and BQJSRQ > AS_JSRQ00;
  Insert into BM_YYSFTJ
    (ID0000, BMBH00, SFXM99, SFXM103, SFXM105, SFXM106)
    select distinct AD_ID0000, A.BQH000, A.KSH000, A.ZYID00, 2, 9
      from BQ_BRLDXX A, ZY_BRXXB0 B
     where JSZT00 in ('出院')
       and YXBZ00 = '1'
       AND A.ZYID00 = B.ZYID00
       and (AS_SFYB00 = '0' or (AS_SFYB00 = '1' AND B.FBBH00 = 3) or
           (AS_SFYB00 = '2' AND B.FBBH00 <> 3))
       and JSRQ00 >= AS_KSRQ00
       and JSRQ00 <= AS_JSRQ00;

  for CUR_TJ IN CUR_RSTJ LOOP

    if AS_CKBQKS = 'Y' then
      UPDATE BM_YYSFTJ
         SET SFXM101 = NVL(SFXM101, 0) + CUR_TJ.SFXM101
       WHERE ID0000 = AD_ID0000
         AND BMBH00 = CUR_TJ.BMBH00
         AND SFXM99 = CUR_TJ.SFXM99
         AND SFXM100 = 99;

    else
      UPDATE BM_YYSFTJ
         SET SFXM101 = NVL(SFXM101, 0) + CUR_TJ.SFXM101
       WHERE ID0000 = AD_ID0000
         AND BMBH00 = CUR_TJ.BMBH00
         AND SFXM100 = 99;

    end if;
  END LOOP;

  FOR CUR_TS IN CUR_TSTJ LOOP
    select RYRQ00, CYRQ00, BRZT00
      into LS_RYRQ00, LS_CYRQ00, LS_BRZT00
      from ZY_BRXXB0
     where ZYID00 = CUR_TS.SFXM103;

    IF LS_BRZT00 IN ('4', '5') then
      LS_RYRQ01 := NVL(LS_RYRQ00, TO_CHAR(SYSDATE, 'YYYYMMDD'));
      LS_CYRQ01 := NVL(LS_CYRQ00, TO_CHAR(SYSDATE, 'YYYYMMDD'));
    else

      if NVL(LS_RYRQ00, TO_CHAR(SYSDATE, 'YYYYMMDD')) <= AS_KSRQ00 then
        LS_RYRQ01 := AS_KSRQ00;
      else
        LS_RYRQ01 := NVL(LS_RYRQ00, TO_CHAR(SYSDATE, 'YYYYMMDD'));
      end if;

      if NVL(LS_CYRQ00, TO_CHAR(SYSDATE, 'YYYYMMDD')) >= AS_JSRQ00 then
        LS_CYRQ01 := AS_JSRQ00;
      else
        LS_CYRQ01 := NVL(LS_CYRQ00, TO_CHAR(SYSDATE, 'YYYYMMDD'));
      end if;

    END IF;
    select GREATEST(TO_DATE(LS_CYRQ01, 'YYYYMMDD') -
                    TO_DATE(LS_RYRQ01, 'YYYYMMDD'),
                    1)
      into LS_ZYTS00
      from DUAL;

    if AS_CKBQKS = 'Y' then
      UPDATE BM_YYSFTJ
         SET SFXM102 = NVL(SFXM102, 0) + LS_ZYTS00
       WHERE ID0000 = AD_ID0000
         AND BMBH00 = CUR_TS.BMBH00
         AND SFXM99 = CUR_TS.SFXM99
         AND SFXM100 = 99;

    else
      UPDATE BM_YYSFTJ
         SET SFXM102 = NVL(SFXM102, 0) + LS_ZYTS00
       WHERE ID0000 = AD_ID0000
         AND BMBH00 = CUR_TS.BMBH00
         AND SFXM100 = 99;

    end if;
  END LOOP;

  --添加一条总计行
  if AS_CKBQKS = 'Y' then

    Insert into BM_YYSFTJ
      (ID0000,
       BMBH00,
       SFXM99,
       SFXM01,
       SFXM02,
       SFXM03,
       SFXM04,
       SFXM05,
       SFXM06,
       SFXM07,
       SFXM08,
       SFXM09,
       SFXM10,
       SFXM11,
       SFXM12,
       SFXM13,
       SFXM14,
       SFXM15,
       SFXM16,
       SFXM17,
       SFXM18,
       SFXM19,
       SFXM20,
       SFXM21,
       SFXM22,
       SFXM23,
       SFXM24,
       SFXM25,
       SFXM26,
       SFXM27,
       SFXM28,
       SFXM29,
       SFXM30,
       SFXM31,
       SFXM32,
       SFXM33,
       SFXM34,
       SFXM35,
       SFXM36,
       SFXM37,
       SFXM38,
       SFXM39,
       SFXM40,
       SFXM41,
       SFXM42,
       SFXM43,
       SFXM44,
       SFXM45,
       SFXM46,
       SFXM47,
       SFXM48,
       SFXM49,
       SFXM50,
       SFXM51,
       SFXM52,
       SFXM53,
       SFXM54,
       SFXM55,
       SFXM56,
       SFXM57,
       SFXM58,
       SFXM59,
       SFXM60,
       SFXM61,
       SFXM62,
       SFXM63,
       SFXM64,
       SFXM65,
       SFXM66,
       SFXM67,
       SFXM68,
       SFXM69,
       SFXM70,
       SFXM71,
       SFXM72,
       SFXM73,
       SFXM74,
       SFXM75,
       SFXM76,
       SFXM77,
       SFXM78,
       SFXM79,
       SFXM80,
       SFXM81,
       SFXM82,
       SFXM83,
       SFXM84,
       SFXM85,
       SFXM86,
       SFXM87,
       SFXM88,
       SFXM89,
       SFXM90,
       SFXM91,
       SFXM92,
       SFXM93,
       SFXM94,
       SFXM95,
       SFXM96,
       SFXM97,
       SFXM98,
       XMHJ00,
       SFXM100,
       SFXM101,
       SFXM102,
       SFXM111)
      select AD_ID0000,
             BMBH00,
             SFXM99,
             sum(SFXM01),
             sum(SFXM02),
             sum(SFXM03),
             sum(SFXM04),
             sum(SFXM05),
             sum(SFXM06),
             sum(SFXM07),
             sum(SFXM08),
             sum(SFXM09),
             sum(SFXM10),
             sum(SFXM11),
             sum(SFXM12),
             sum(SFXM13),
             sum(SFXM14),
             sum(SFXM15),
             sum(SFXM16),
             sum(SFXM17),
             sum(SFXM18),
             sum(SFXM19),
             sum(SFXM20),
             sum(SFXM21),
             sum(SFXM22),
             sum(SFXM23),
             sum(SFXM24),
             sum(SFXM25),
             sum(SFXM26),
             sum(SFXM27),
             sum(SFXM28),
             sum(SFXM29),
             sum(SFXM30),
             sum(SFXM31),
             sum(SFXM32),
             sum(SFXM33),
             sum(SFXM34),
             sum(SFXM35),
             sum(SFXM36),
             sum(SFXM37),
             sum(SFXM38),
             sum(SFXM39),
             sum(SFXM40),
             sum(SFXM41),
             sum(SFXM42),
             sum(SFXM43),
             sum(SFXM44),
             sum(SFXM45),
             sum(SFXM46),
             sum(SFXM47),
             sum(SFXM48),
             sum(SFXM49),
             sum(SFXM50),
             sum(SFXM51),
             sum(SFXM52),
             sum(SFXM53),
             sum(SFXM54),
             sum(SFXM55),
             sum(SFXM56),
             sum(SFXM57),
             sum(SFXM58),
             sum(SFXM59),
             sum(SFXM60),
             sum(SFXM61),
             sum(SFXM62),
             sum(SFXM63),
             sum(SFXM64),
             sum(SFXM65),
             sum(SFXM66),
             sum(SFXM67),
             sum(SFXM68),
             sum(SFXM69),
             sum(SFXM70),
             sum(SFXM71),
             sum(SFXM72),
             sum(SFXM73),
             sum(SFXM74),
             sum(SFXM75),
             sum(SFXM76),
             sum(SFXM77),
             sum(SFXM78),
             sum(SFXM79),
             sum(SFXM80),
             sum(SFXM81),
             sum(SFXM82),
             sum(SFXM83),
             sum(SFXM84),
             sum(SFXM85),
             sum(SFXM86),
             sum(SFXM87),
             sum(SFXM88),
             sum(SFXM89),
             sum(SFXM90),
             sum(SFXM91),
             sum(SFXM92),
             sum(SFXM93),
             sum(SFXM94),
             sum(SFXM95),
             sum(SFXM96),
             sum(SFXM97),
             sum(SFXM98),
             sum(XMHJ00),
             999,
             sum(SFXM101),
             sum(SFXM102),
             sum(SFXM111)
        from BM_YYSFTJ
       where ID0000 = AD_ID0000
         and SFXM100 = 99
       group by BMBH00, SFXM99;

    Insert into BM_YYSFTJ
      (ID0000,
       BMBH00,
       SFXM01,
       SFXM02,
       SFXM03,
       SFXM04,
       SFXM05,
       SFXM06,
       SFXM07,
       SFXM08,
       SFXM09,
       SFXM10,
       SFXM11,
       SFXM12,
       SFXM13,
       SFXM14,
       SFXM15,
       SFXM16,
       SFXM17,
       SFXM18,
       SFXM19,
       SFXM20,
       SFXM21,
       SFXM22,
       SFXM23,
       SFXM24,
       SFXM25,
       SFXM26,
       SFXM27,
       SFXM28,
       SFXM29,
       SFXM30,
       SFXM31,
       SFXM32,
       SFXM33,
       SFXM34,
       SFXM35,
       SFXM36,
       SFXM37,
       SFXM38,
       SFXM39,
       SFXM40,
       SFXM41,
       SFXM42,
       SFXM43,
       SFXM44,
       SFXM45,
       SFXM46,
       SFXM47,
       SFXM48,
       SFXM49,
       SFXM50,
       SFXM51,
       SFXM52,
       SFXM53,
       SFXM54,
       SFXM55,
       SFXM56,
       SFXM57,
       SFXM58,
       SFXM59,
       SFXM60,
       SFXM61,
       SFXM62,
       SFXM63,
       SFXM64,
       SFXM65,
       SFXM66,
       SFXM67,
       SFXM68,
       SFXM69,
       SFXM70,
       SFXM71,
       SFXM72,
       SFXM73,
       SFXM74,
       SFXM75,
       SFXM76,
       SFXM77,
       SFXM78,
       SFXM79,
       SFXM80,
       SFXM81,
       SFXM82,
       SFXM83,
       SFXM84,
       SFXM85,
       SFXM86,
       SFXM87,
       SFXM88,
       SFXM89,
       SFXM90,
       SFXM91,
       SFXM92,
       SFXM93,
       SFXM94,
       SFXM95,
       SFXM96,
       SFXM97,
       SFXM98,
       XMHJ00,
       SFXM100,
       SFXM101,
       SFXM102,
       SFXM111)
      select AD_ID0000,
             999999999,
             sum(SFXM01),
             sum(SFXM02),
             sum(SFXM03),
             sum(SFXM04),
             sum(SFXM05),
             sum(SFXM06),
             sum(SFXM07),
             sum(SFXM08),
             sum(SFXM09),
             sum(SFXM10),
             sum(SFXM11),
             sum(SFXM12),
             sum(SFXM13),
             sum(SFXM14),
             sum(SFXM15),
             sum(SFXM16),
             sum(SFXM17),
             sum(SFXM18),
             sum(SFXM19),
             sum(SFXM20),
             sum(SFXM21),
             sum(SFXM22),
             sum(SFXM23),
             sum(SFXM24),
             sum(SFXM25),
             sum(SFXM26),
             sum(SFXM27),
             sum(SFXM28),
             sum(SFXM29),
             sum(SFXM30),
             sum(SFXM31),
             sum(SFXM32),
             sum(SFXM33),
             sum(SFXM34),
             sum(SFXM35),
             sum(SFXM36),
             sum(SFXM37),
             sum(SFXM38),
             sum(SFXM39),
             sum(SFXM40),
             sum(SFXM41),
             sum(SFXM42),
             sum(SFXM43),
             sum(SFXM44),
             sum(SFXM45),
             sum(SFXM46),
             sum(SFXM47),
             sum(SFXM48),
             sum(SFXM49),
             sum(SFXM50),
             sum(SFXM51),
             sum(SFXM52),
             sum(SFXM53),
             sum(SFXM54),
             sum(SFXM55),
             sum(SFXM56),
             sum(SFXM57),
             sum(SFXM58),
             sum(SFXM59),
             sum(SFXM60),
             sum(SFXM61),
             sum(SFXM62),
             sum(SFXM63),
             sum(SFXM64),
             sum(SFXM65),
             sum(SFXM66),
             sum(SFXM67),
             sum(SFXM68),
             sum(SFXM69),
             sum(SFXM70),
             sum(SFXM71),
             sum(SFXM72),
             sum(SFXM73),
             sum(SFXM74),
             sum(SFXM75),
             sum(SFXM76),
             sum(SFXM77),
             sum(SFXM78),
             sum(SFXM79),
             sum(SFXM80),
             sum(SFXM81),
             sum(SFXM82),
             sum(SFXM83),
             sum(SFXM84),
             sum(SFXM85),
             sum(SFXM86),
             sum(SFXM87),
             sum(SFXM88),
             sum(SFXM89),
             sum(SFXM90),
             sum(SFXM91),
             sum(SFXM92),
             sum(SFXM93),
             sum(SFXM94),
             sum(SFXM95),
             sum(SFXM96),
             sum(SFXM97),
             sum(SFXM98),
             sum(XMHJ00),
             999,
             sum(SFXM101),
             sum(SFXM102),
             sum(SFXM111)
        from BM_YYSFTJ
       where ID0000 = AD_ID0000
         and SFXM100 = 99;
  else
    -- select  SQ_BM_YYSFTJ_ID0000.nextval  into  ls_ID0000  from  dual;
    Insert into BM_YYSFTJ
      (ID0000,
       BMBH00,
       SFXM01,
       SFXM02,
       SFXM03,
       SFXM04,
       SFXM05,
       SFXM06,
       SFXM07,
       SFXM08,
       SFXM09,
       SFXM10,
       SFXM11,
       SFXM12,
       SFXM13,
       SFXM14,
       SFXM15,
       SFXM16,
       SFXM17,
       SFXM18,
       SFXM19,
       SFXM20,
       SFXM21,
       SFXM22,
       SFXM23,
       SFXM24,
       SFXM25,
       SFXM26,
       SFXM27,
       SFXM28,
       SFXM29,
       SFXM30,
       SFXM31,
       SFXM32,
       SFXM33,
       SFXM34,
       SFXM35,
       SFXM36,
       SFXM37,
       SFXM38,
       SFXM39,
       SFXM40,
       SFXM41,
       SFXM42,
       SFXM43,
       SFXM44,
       SFXM45,
       SFXM46,
       SFXM47,
       SFXM48,
       SFXM49,
       SFXM50,
       SFXM51,
       SFXM52,
       SFXM53,
       SFXM54,
       SFXM55,
       SFXM56,
       SFXM57,
       SFXM58,
       SFXM59,
       SFXM60,
       SFXM61,
       SFXM62,
       SFXM63,
       SFXM64,
       SFXM65,
       SFXM66,
       SFXM67,
       SFXM68,
       SFXM69,
       SFXM70,
       SFXM71,
       SFXM72,
       SFXM73,
       SFXM74,
       SFXM75,
       SFXM76,
       SFXM77,
       SFXM78,
       SFXM79,
       SFXM80,
       SFXM81,
       SFXM82,
       SFXM83,
       SFXM84,
       SFXM85,
       SFXM86,
       SFXM87,
       SFXM88,
       SFXM89,
       SFXM90,
       SFXM91,
       SFXM92,
       SFXM93,
       SFXM94,
       SFXM95,
       SFXM96,
       SFXM97,
       SFXM98,
       XMHJ00,
       SFXM100,
       SFXM101,
       SFXM102,
       SFXM111)
      select AD_ID0000,
             BMBH00,
             sum(SFXM01),
             sum(SFXM02),
             sum(SFXM03),
             sum(SFXM04),
             sum(SFXM05),
             sum(SFXM06),
             sum(SFXM07),
             sum(SFXM08),
             sum(SFXM09),
             sum(SFXM10),
             sum(SFXM11),
             sum(SFXM12),
             sum(SFXM13),
             sum(SFXM14),
             sum(SFXM15),
             sum(SFXM16),
             sum(SFXM17),
             sum(SFXM18),
             sum(SFXM19),
             sum(SFXM20),
             sum(SFXM21),
             sum(SFXM22),
             sum(SFXM23),
             sum(SFXM24),
             sum(SFXM25),
             sum(SFXM26),
             sum(SFXM27),
             sum(SFXM28),
             sum(SFXM29),
             sum(SFXM30),
             sum(SFXM31),
             sum(SFXM32),
             sum(SFXM33),
             sum(SFXM34),
             sum(SFXM35),
             sum(SFXM36),
             sum(SFXM37),
             sum(SFXM38),
             sum(SFXM39),
             sum(SFXM40),
             sum(SFXM41),
             sum(SFXM42),
             sum(SFXM43),
             sum(SFXM44),
             sum(SFXM45),
             sum(SFXM46),
             sum(SFXM47),
             sum(SFXM48),
             sum(SFXM49),
             sum(SFXM50),
             sum(SFXM51),
             sum(SFXM52),
             sum(SFXM53),
             sum(SFXM54),
             sum(SFXM55),
             sum(SFXM56),
             sum(SFXM57),
             sum(SFXM58),
             sum(SFXM59),
             sum(SFXM60),
             sum(SFXM61),
             sum(SFXM62),
             sum(SFXM63),
             sum(SFXM64),
             sum(SFXM65),
             sum(SFXM66),
             sum(SFXM67),
             sum(SFXM68),
             sum(SFXM69),
             sum(SFXM70),
             sum(SFXM71),
             sum(SFXM72),
             sum(SFXM73),
             sum(SFXM74),
             sum(SFXM75),
             sum(SFXM76),
             sum(SFXM77),
             sum(SFXM78),
             sum(SFXM79),
             sum(SFXM80),
             sum(SFXM81),
             sum(SFXM82),
             sum(SFXM83),
             sum(SFXM84),
             sum(SFXM85),
             sum(SFXM86),
             sum(SFXM87),
             sum(SFXM88),
             sum(SFXM89),
             sum(SFXM90),
             sum(SFXM91),
             sum(SFXM92),
             sum(SFXM93),
             sum(SFXM94),
             sum(SFXM95),
             sum(SFXM96),
             sum(SFXM97),
             sum(SFXM98),
             sum(XMHJ00),
             999,
             sum(SFXM101),
             sum(SFXM102),
             sum(SFXM111)
        from BM_YYSFTJ
       where ID0000 = AD_ID0000
         and SFXM100 = 99
       group by BMBH00;

    Insert into BM_YYSFTJ
      (ID0000,
       BMBH00,
       SFXM01,
       SFXM02,
       SFXM03,
       SFXM04,
       SFXM05,
       SFXM06,
       SFXM07,
       SFXM08,
       SFXM09,
       SFXM10,
       SFXM11,
       SFXM12,
       SFXM13,
       SFXM14,
       SFXM15,
       SFXM16,
       SFXM17,
       SFXM18,
       SFXM19,
       SFXM20,
       SFXM21,
       SFXM22,
       SFXM23,
       SFXM24,
       SFXM25,
       SFXM26,
       SFXM27,
       SFXM28,
       SFXM29,
       SFXM30,
       SFXM31,
       SFXM32,
       SFXM33,
       SFXM34,
       SFXM35,
       SFXM36,
       SFXM37,
       SFXM38,
       SFXM39,
       SFXM40,
       SFXM41,
       SFXM42,
       SFXM43,
       SFXM44,
       SFXM45,
       SFXM46,
       SFXM47,
       SFXM48,
       SFXM49,
       SFXM50,
       SFXM51,
       SFXM52,
       SFXM53,
       SFXM54,
       SFXM55,
       SFXM56,
       SFXM57,
       SFXM58,
       SFXM59,
       SFXM60,
       SFXM61,
       SFXM62,
       SFXM63,
       SFXM64,
       SFXM65,
       SFXM66,
       SFXM67,
       SFXM68,
       SFXM69,
       SFXM70,
       SFXM71,
       SFXM72,
       SFXM73,
       SFXM74,
       SFXM75,
       SFXM76,
       SFXM77,
       SFXM78,
       SFXM79,
       SFXM80,
       SFXM81,
       SFXM82,
       SFXM83,
       SFXM84,
       SFXM85,
       SFXM86,
       SFXM87,
       SFXM88,
       SFXM89,
       SFXM90,
       SFXM91,
       SFXM92,
       SFXM93,
       SFXM94,
       SFXM95,
       SFXM96,
       SFXM97,
       SFXM98,
       XMHJ00,
       SFXM100,
       SFXM101,
       SFXM102,
       SFXM111)
      select AD_ID0000,
             999999999,
             sum(SFXM01),
             sum(SFXM02),
             sum(SFXM03),
             sum(SFXM04),
             sum(SFXM05),
             sum(SFXM06),
             sum(SFXM07),
             sum(SFXM08),
             sum(SFXM09),
             sum(SFXM10),
             sum(SFXM11),
             sum(SFXM12),
             sum(SFXM13),
             sum(SFXM14),
             sum(SFXM15),
             sum(SFXM16),
             sum(SFXM17),
             sum(SFXM18),
             sum(SFXM19),
             sum(SFXM20),
             sum(SFXM21),
             sum(SFXM22),
             sum(SFXM23),
             sum(SFXM24),
             sum(SFXM25),
             sum(SFXM26),
             sum(SFXM27),
             sum(SFXM28),
             sum(SFXM29),
             sum(SFXM30),
             sum(SFXM31),
             sum(SFXM32),
             sum(SFXM33),
             sum(SFXM34),
             sum(SFXM35),
             sum(SFXM36),
             sum(SFXM37),
             sum(SFXM38),
             sum(SFXM39),
             sum(SFXM40),
             sum(SFXM41),
             sum(SFXM42),
             sum(SFXM43),
             sum(SFXM44),
             sum(SFXM45),
             sum(SFXM46),
             sum(SFXM47),
             sum(SFXM48),
             sum(SFXM49),
             sum(SFXM50),
             sum(SFXM51),
             sum(SFXM52),
             sum(SFXM53),
             sum(SFXM54),
             sum(SFXM55),
             sum(SFXM56),
             sum(SFXM57),
             sum(SFXM58),
             sum(SFXM59),
             sum(SFXM60),
             sum(SFXM61),
             sum(SFXM62),
             sum(SFXM63),
             sum(SFXM64),
             sum(SFXM65),
             sum(SFXM66),
             sum(SFXM67),
             sum(SFXM68),
             sum(SFXM69),
             sum(SFXM70),
             sum(SFXM71),
             sum(SFXM72),
             sum(SFXM73),
             sum(SFXM74),
             sum(SFXM75),
             sum(SFXM76),
             sum(SFXM77),
             sum(SFXM78),
             sum(SFXM79),
             sum(SFXM80),
             sum(SFXM81),
             sum(SFXM82),
             sum(SFXM83),
             sum(SFXM84),
             sum(SFXM85),
             sum(SFXM86),
             sum(SFXM87),
             sum(SFXM88),
             sum(SFXM89),
             sum(SFXM90),
             sum(SFXM91),
             sum(SFXM92),
             sum(SFXM93),
             sum(SFXM94),
             sum(SFXM95),
             sum(SFXM96),
             sum(SFXM97),
             sum(SFXM98),
             sum(XMHJ00),
             999,
             sum(SFXM101),
             sum(SFXM102),
             sum(SFXM111)
        from BM_YYSFTJ
       where ID0000 = AD_ID0000
         and SFXM100 = 99;
    -- ad_ID0000:=ls_ID0000;
  end if;

  DELETE BM_YYSFTJ
   WHERE ID0000 = AD_ID0000
     AND SFXM106 = 99
     AND SFXM105 IN (1, 2);
  DELETE BM_YYSFTJ
   WHERE ID0000 = AD_ID0000
     AND SFXM106 = 9
     AND SFXM105 IN (1, 2);
  DELETE BM_YYSFTJ
   WHERE ID0000 = AD_ID0000
     AND SFXM100 in (99);
  -- commit;
EXCEPTION
  WHEN OTHERS THEN
    AS_YHMSG0 := '出错原因不详,请记录此信息并和系统管理员联系！';
    AS_SYSMSG := SUBSTR(SQLERRM || '执行存储过程错误:SP_ZY_ZYHSSR_RBB000(' ||
                        AS_KSRQ00 || ',' || AS_KSSJ00 || ',' || AS_JSRQ00 || ',' ||
                        AS_JSSJ00 || ',' || AS_KSMC00 ||
                        'ad_ID0000,as_YHMSG0,as_SYSMSG',
                        1,
                        150);
    ROLLBACK;
END;