create or replace procedure SP_SF_GHLBTJ_RBB000
(
        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     --系统提示的错误信息
)
as
        ls_SFCZ00    SF_BRXXB0.SFCZ00%TYPE;
        ls_GHLB00    SF_BRXXB0.GHLB00%TYPE;
        ls_GHKS00    SF_BRXXB0.GHKS00%TYPE;
        ls_BMMC00    BM_BMBM00.BMMC00%TYPE;
        ls_COUNT0    NUMBER(5);
        ls_GHLBBH    CHAR(2);
        VCounter     NUMBER(5);
        CURSOR  CUR_SF_GHLBTJ_RBB000  IS
          select SFCZ00,GHLB00,GHKS00,sum(GHCS00) from  VW_SF_GHLSXX
                         WHERE  CZRQ00 >= as_KSRQ00 AND CZRQ00 <= as_JSRQ00 AND
                                CZRQ00||CZSJ00 >= as_KSRQ00||as_KSSJ00 AND  
                                CZRQ00||CZSJ00 <= as_JSRQ00||as_JSSJ00 
                                and SFTJ00 ='Y'
                group by SFCZ00,GHLB00,GHKS00;
BEGIN

Select  SQ_BM_GHKSTJ_ID0000.nextval  into  ad_ID0000  from  dual;

OPEN CUR_SF_GHLBTJ_RBB000;
       LOOP
          FETCH CUR_SF_GHLBTJ_RBB000 INTO  ls_SFCZ00,ls_GHLB00,ls_GHKS00,ls_COUNT0;
          EXIT WHEN CUR_SF_GHLBTJ_RBB000%NOTFOUND;
          select bmmc00 into ls_BMMC00 from bm_bmbm00 where bmbh00=ls_GHKS00;
          select Lpad(to_char(ls_GHLB00),2,'0') into ls_GHLBBH from dual;
          SELECT lpad(REPLACE(ls_GHLBBH,'-'),2,'0') into ls_GHLBBH FROM DUAL; --把-去掉，以免出错
          IF  ls_SFCZ00 = '0'  THEN
                Update  BM_GHKSTJ  Set  GHKS61 = NVL(GHKS61,0)+ls_COUNT0 ,GHKS63=NVL(GHKS63,0)+ls_COUNT0  where  DYID00 = ls_GHKS00  and ID0000=ad_ID0000;
                if SQL%NOTFOUND  then
                    Insert into BM_GHKSTJ(GHRQ00,GHKS61,ID0000,GHKS63,DYID00)  values(ls_BMMC00,ls_COUNT0,ad_ID0000,ls_COUNT0,ls_GHKS00);
                end if;
          END IF;
          IF  ls_SFCZ00 = '1'  THEN
                Update  BM_GHKSTJ  Set  GHKS62 = NVL(GHKS62,0)+ls_COUNT0 ,GHKS63=NVL(GHKS63,0)+ls_COUNT0  where  DYID00 = ls_GHKS00  and ID0000=ad_ID0000;
                if SQL%NOTFOUND  then
                    Insert into BM_GHKSTJ(GHRQ00,GHKS62,ID0000,GHKS63,DYID00)  values(ls_BMMC00,ls_COUNT0,ad_ID0000,ls_COUNT0,ls_GHKS00);
                end if;
          END IF;
          SELECT COUNT(1) INTO VCounter FROM BM_GHKSTJ WHERE ID0000=ad_ID0000 AND DYID00=ls_GHKS00;
          IF VCounter>0 THEN
              SP_EXECUTE_SQL('UPDATE BM_GHKSTJ SET GHKS'||ls_GHLBBH||'= NVL(GHKS'||ls_GHLBBH||',0)+'||to_char(ls_COUNT0)||',XMHJ00=NVL(XMHJ00,0)+'||to_char(ls_COUNT0)||' where  DYID00 = '||to_char(ls_GHKS00)||' and ID0000='||to_char(ad_ID0000));
          ELSE
              SP_EXECUTE_SQL('Insert into BM_GHKSTJ(GHRQ00,GHKS'||ls_GHLBBH||',ID0000,XMHJ00,DYID00) values('||ls_BMMC00||','||to_char(ls_COUNT0)||','||to_char(ad_ID0000)||','||to_char(ls_COUNT0)||','||to_char(ls_GHKS00)||')');
          END IF;          
          --GHKS40 为合计字段
       END LOOP;
CLOSE CUR_SF_GHLBTJ_RBB000;

--添加一条总计行
Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09,GHKS10,GHKS11,
                      GHKS12,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS18,GHKS19,GHKS20,GHKS21,GHKS22,GHKS23,GHKS24,
                      GHKS25,GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36,GHKS37,
                      GHKS38,GHKS39,GHKS40,GHKS55,GHKS61,GHKS62,GHKS63,XMHJ00)
  select ad_ID0000,' 合计',sum(GHKS01),sum(GHKS02),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),
  sum(GHKS08),sum(GHKS09),sum(GHKS10),sum(GHKS11),sum(GHKS12),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),
  sum(GHKS17),sum(GHKS18),sum(GHKS19),sum(GHKS20),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25),
  sum(GHKS26),sum(GHKS27),sum(GHKS28),sum(GHKS29),sum(GHKS30),sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),
  sum(GHKS35),sum(GHKS36),sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40),sum(GHKS55),sum(GHKS61),sum(GHKS62),sum(GHKS63),sum(XMHJ00)
    from BM_GHKSTJ  where  ID0000 = ad_ID0000;

COMMIT;
EXCEPTION
      WHEN OTHERS THEN
                as_YHMSG0:='系统出错，请查看详细信息。如有不明，请与管理员联系！'||SQLERRM;
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_GHLBTJ_RBB000('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','||
                    as_JSSJ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
                ROLLBACK;
END;