CREATE OR REPLACE PROCEDURE SP_SF_MZSFYJK_HZBB00
(
        as_KSRQ00    IN    CHAR,       --开始日期
        as_JSRQ00    IN    CHAR,       --结束日期
        as_KSSJ00    IN    CHAR,       --开始时间
        as_JSSJ00    IN    CHAR,       --结束时间
        as_CZYXM0    IN    CHAR,       --操作员姓名
        ad_ID0000    OUT   NUMBER,     --统计报表对应的ID
        as_YHMSG0    OUT   VARCHAR,    --存储过程提示的错误信息
        as_SYSMSG    OUT   VARCHAR     --系统提示的错误信息
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- pwt     2018.12.10  增加支付方式  by MZSF9-20181126-003
as
        ls_CZYXM0    SF_BRJFB0.CZYXM0%TYPE;
        ls_JE0000    SF_BRJFB0.JFJE00%TYPE;
        ls_ZFFSMC    SF_BRJFB0.ZFFSMC%TYPE;
        CURSOR  CUR_SF_MZSFYJK_HZBB00  IS
             select CZYXM0,SUM(JFJE00) FKJE00,ZFFSMC from sf_brjfb0 WHERE
                              JFRQ00>=as_KSRQ00 AND
                              JFRQ00<=as_JSRQ00 AND
                              JFRQ00||JFSJ00>=as_KSRQ00||as_KSSJ00 AND
                              JFRQ00||JFSJ00<=as_JSRQ00||as_JSSJ00 AND
                              ZFFS00 IN (1,2,5,8,24,25) AND JFJE00>0
                              AND (CZYXM0=as_CZYXM0 OR as_CZYXM0='0')
                              GROUP BY CZYXM0,ZFFSMC
                 UNION ALL
             select CZYXM0,SUM(JFJE00) FKJE00,ZFFSMC from sf_brjfb0 WHERE
                              JFRQ00>=as_KSRQ00 AND
                              JFRQ00<=as_JSRQ00 AND
                              JFRQ00||JFSJ00>=as_KSRQ00||as_KSSJ00 AND
                              JFRQ00||JFSJ00<=as_JSRQ00||as_JSSJ00 AND
                              ZFFS00 IN (1,2,5,8,24,25) AND JFJE00<0
                              AND (CZYXM0=as_CZYXM0 OR as_CZYXM0='0')
                              GROUP BY CZYXM0,ZFFSMC ;
BEGIN
Select  SQ_BM_GHKSTJ_ID0000.nextval  into  ad_ID0000  from  dual;
OPEN CUR_SF_MZSFYJK_HZBB00;
    LOOP
      FETCH CUR_SF_MZSFYJK_HZBB00 INTO  ls_CZYXM0,ls_JE0000,ls_ZFFSMC;
      EXIT WHEN CUR_SF_MZSFYJK_HZBB00%NOTFOUND;
      if  ls_JE0000 >0  then
         Update  BM_GHKSTJ  Set  GHKS01 =ls_JE0000
          where  ID0000=ad_ID0000 and GHRQ00= ls_CZYXM0 and BEIZHU = ls_ZFFSMC;
      if SQL%NOTFOUND  then
           Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,ID0000,BEIZHU)
              values(ls_CZYXM0,ls_JE0000,0,ad_ID0000,ls_ZFFSMC);
      end if;
      end if;
   if  ls_JE0000 <0  then
         Update  BM_GHKSTJ  Set  GHKS02 =-ls_JE0000
          where   ID0000=ad_ID0000 and GHRQ00= ls_CZYXM0 and BEIZHU = ls_ZFFSMC;
      if SQL%NOTFOUND  then
           Insert into BM_GHKSTJ(GHRQ00,GHKS02,GHKS01,ID0000,BEIZHU)
              values(ls_CZYXM0,-ls_JE0000,0,ad_ID0000,ls_ZFFSMC);
      end if;
      end if;
   END LOOP;
CLOSE CUR_SF_MZSFYJK_HZBB00;
--添加一条总计行
Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,BEIZHU)
  select ad_ID0000,' 合计 ',SUM(GHKS01),SUM(GHKS02),''
      from BM_GHKSTJ  where  ID0000 = ad_ID0000;
--commit
EXCEPTION
      WHEN OTHERS THEN
                as_YHMSG0:='系统出错，请查看详细信息。如有不明，请与管理员联系！'||SQLERRM;
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_MZSRYB_RBB000('||as_KSRQ00||','||as_JSRQ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150);
                ROLLBACK;
END;