-- Start of DDL script for SP_SF_YJJYE0
-- Generated 30-九月-15  8:27:48 am
-- from fw2-SD_HOSPITAL:1

-- Procedure SP_SF_YJJYE0

CREATE OR REPLACE
procedure SP_SF_YJJYE0
as
--modify history
--date        person comments
--2007.08.28  xzw    表sf_yjjye0增加字段YJJSR0(本期预交金收入)
--2008.08.15  xzw    表sf_yjjye0增加字段sswr00(本期四舍五入)
--2008.11.03  zhr    表sf_yjjye0增加字段czsj00(记录缴款截至时间)
--2009.02.09  xzw    表sf_yjjye0增加字段xjtc00(退预交金中现金退出部分)
--2010.06.23  qks    表SF_YJJYE0增加字段DZQBJE(电子钱包增加部分)
--2013.01.23  csf    如果某一天没有任何资金来往记录，SF_YYJYE0也应该插入一条记录。MZSF-20130123-001
--2015.04.01  dsm    sf_yjjye0加YYID00  for MZSF-20150105-005
--2015.04.24  dsm    现场许天真调整归档(合计先不按分院整理)
--zhangyc   2015.07.22     增加健康通(民生通)支付功能 by MZSF-20150722-001
  Vbrid00   BM_BRXXB0.BRID00%type;
  VLastrq   SF_BRJFB0.JFRQ00%type;
  Vczrq00   SF_BRJFB0.JFRQ00%type;
  Vjksj00   SF_BRJFB0.JFSJ00%type;
  Vjksj01   SF_BRJFB0.JFSJ00%type;
  Vbqjc00   SF_YJJYE0.BQJC00%type;
  Vcounter  number(5);
  /*
  cursor c_brjfb0 is select ZFFS00,JFJE00,BRID00,YYID00 from (
                     select ZFFS00,sum(JFJE00)JFJE00,BRID00,b.YYID00 from SF_BRJFB0 a,BM_BMBM00 b
                     where  JFLBID=1 and JFJE00<>0 and JFRQ00>=VLastrq and JFRQ00||JFSJ00>Vlastrq||Vjksj00 and
                            JFRQ00<=Vczrq00 and JFRQ00||JFSJ00<=Vczrq00||Vjksj01 and a.CZYKS0=b.BMBH00
                     group by b.YYID00,ZFFS00,BRID00,sign(JFJE00)--退预交金为本日负增加
                     union all
                     select -1,sum(ZFJE00)JFJE00,BRID00,b.YYID00
                     from  SF_BRFY00 a,BM_BMBM00 b
                     where JFLBID=1 and ZFJE00<>0 and CZRQ00>=VLastrq and CZRQ00||CZSJ00>Vlastrq||Vjksj00 and
                            CZRQ00<=Vczrq00 and CZRQ00||CZSJ00<=Vczrq00||Vjksj01
                            and a.CZYKS0=b.BMBH00
                     group by b.YYID00,BRID00
                     ) order by YYID00,BRID00;
 */
  cursor c_brjfb0 is select ZFFS00,JFJE00,BRID00,YYID00 from (
                     select ZFFS00,sum(JFJE00)JFJE00,BRID00,b.YYID00 from SF_BRJFB0 a,BM_BMBM00 b
                     where  JFLBID=1 and JFJE00<>0 and JFRQ00>=VLastrq and JFRQ00||JFSJ00>Vlastrq||Vjksj00 and
                            JFRQ00<=Vczrq00 and JFRQ00||JFSJ00<=Vczrq00||Vjksj01 and a.CZYKS0=b.BMBH00
                     group by b.YYID00,ZFFS00,BRID00,sign(JFJE00)--退预交金为本日负增加
                     union all       --本期减少 减去血透减免金额
                     select -1,sum(zfje00-jmje00) jfje00,brid00,yyid00 from (
                     select zfje00,0 jmje00,BRID00,b.YYID00
                     from  SF_BRFY00 a,BM_BMBM00 b
                     where JFLBID=1 and ZFJE00<>0 and a.CZRQ00>=VLastrq and a.CZRQ00||a.CZSJ00>Vlastrq||Vjksj00 and
                            a.CZRQ00<=Vczrq00 and a.CZRQ00||a.CZSJ00<=Vczrq00||Vjksj01 and
                            not exists (select 1 from sf_fymx00 h where a.djh000=h.djh000 and h.jmbz00='5')
                            and a.CZYKS0=b.BMBH00
                      union all
                     select 0 zfje00,-c.jmje00,BRID00,b.YYID00
                     from  SF_BRFY00 a,BM_BMBM00 b,sf_fymx00 c
                     where JFLBID=1 and c.JMJE00<>0 and  a.djh000=c.djh000 and a.CZRQ00>=VLastrq and a.CZRQ00||a.CZSJ00>Vlastrq||Vjksj00 and
                            a.CZRQ00<=Vczrq00 and a.CZRQ00||a.CZSJ00<=Vczrq00||Vjksj01 and c.jmbz00='5'
                            and a.CZYKS0=b.BMBH00) group by brid00,yyid00
                     ) order by YYID00,BRID00;

begin
  select trim(max(CZRQ00)) into Vczrq00 from sf_yjjye0;
  if Vczrq00 is null then
     select to_char(to_date(nvl(min(JFRQ00),'20010102'),'YYYYMMDD')-1,'YYYYMMDD') into Vczrq00 from sf_brjfb0 where jflbid=1;
  end if;
  if Vczrq00=to_char(sysdate-1,'YYYYMMDD') then--只能结到昨天为止
     Return;
  end if;
  loop
    VLastrq:=Vczrq00;
    Vczrq00:=to_char(to_date(Vczrq00,'YYYYMMDD')+1,'YYYYMMDD');
    begin
      select JKSJ00 into Vjksj00 from MZ_MRJKSJ where JKRQ00=Vlastrq;
    exception
      when others then
        Vjksj00:='23:59:59';
    end;
    begin
      select JKSJ00 into Vjksj01 from MZ_MRJKSJ where JKRQ00=Vczrq00;
    exception
      when others then
        Vjksj01:='23:59:59';
    end;
    for jf in c_brjfb0 loop
        if  jf.JFJE00<>0 then
           if jf.ZFFS00 in (1) then--现金
              update SF_YJJYE0 set BRZJ00=BRZJ00+jf.JFJE00,XJ0000=XJ0000+jf.JFJE00,BQJC00=BQJC00+jf.JFJE00,YJJSR0=YJJSR0+decode(sign(jf.JFJE00),1,jf.JFJE00,0),XJTC00=XJTC00+decode(sign(jf.JFJE00),-1,jf.JFJE00,0)
                     where BRID00=jf.BRID00 and CZRQ00=Vczrq00 and YYID00=jf.YYID00;
              if sql%notfound then
                 --取上期余额
                 begin
                    select nvl(BQJC00,0) into Vbqjc00 from SF_YJJYE0
                           where BRID00=jf.BRID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=jf.BRID00 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
                 exception
                   when others then
                        Vbqjc00:=0;
                 end;
                 insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BQJC00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZP0000,BRJS00,YJJSR0,SSWR00,CZSJ00,XJTC00,YYID00)
                        values(jf.BRID00,Vczrq00,Vbqjc00,Vbqjc00+jf.JFJE00,jf.JFJE00,jf.JFJE00,0,0,0,0,0,decode(sign(jf.JFJE00),1,jf.JFJE00,0),0,Vjksj01,decode(sign(jf.JFJE00),-1,jf.JFJE00,0),jf.YYID00);
              end if;
           elsif jf.ZFFS00 in (3,10) then--个人帐户支付
              update SF_YJJYE0 set BRZJ00=BRZJ00+jf.JFJE00,GRZHZF=GRZHZF+jf.JFJE00,BQJC00=BQJC00+jf.JFJE00
                     where BRID00=jf.BRID00 and CZRQ00=Vczrq00 and YYID00=jf.YYID00;
              if sql%notfound then
                 --取上期余额
                 begin
                    select nvl(BQJC00,0) into Vbqjc00 from SF_YJJYE0
                           where BRID00=jf.BRID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=jf.BRID00 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
                 exception
                   when others then
                        Vbqjc00:=0;
                 end;
                 insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BQJC00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZP0000,BRJS00,YJJSR0,SSWR00,CZSJ00,XJTC00,YYID00)
                        values(jf.BRID00,Vczrq00,Vbqjc00,Vbqjc00+jf.JFJE00,jf.JFJE00,0,jf.JFJE00,0,0,0,0,0,0,Vjksj01,0,jf.YYID00);
              end if;
           elsif jf.ZFFS00 in (4,11,16) then--统筹基金支付
              update SF_YJJYE0 set BRZJ00=BRZJ00+jf.JFJE00,TCJJZF=TCJJZF+jf.JFJE00,BQJC00=BQJC00+jf.JFJE00
                     where BRID00=jf.BRID00 and CZRQ00=Vczrq00 and YYID00=jf.YYID00;
              if sql%notfound then
                 --取上期余额
                 begin
                    select nvl(BQJC00,0) into Vbqjc00 from SF_YJJYE0
                           where BRID00=jf.BRID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=jf.BRID00 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
                 exception
                   when others then
                        Vbqjc00:=0;
                 end;
                 insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BQJC00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZP0000,BRJS00,YJJSR0,SSWR00,CZSJ00,XJTC00,YYID00)
                        values(jf.BRID00,Vczrq00,Vbqjc00,Vbqjc00+jf.JFJE00,jf.JFJE00,0,0,jf.JFJE00,0,0,0,0,0,Vjksj01,0,jf.YYID00);
              end if;
           elsif jf.ZFFS00 in (8) then--银行卡
              update SF_YJJYE0 set BRZJ00=BRZJ00+jf.JFJE00,YHK000=YHK000+jf.JFJE00,BQJC00=BQJC00+jf.JFJE00,YJJSR0=YJJSR0+decode(sign(jf.JFJE00),1,jf.JFJE00,0)
                     where BRID00=jf.BRID00 and CZRQ00=Vczrq00 and YYID00=jf.YYID00;
              if sql%notfound then
                 --取上期余额
                 begin
                    select nvl(BQJC00,0) into Vbqjc00 from SF_YJJYE0
                           where BRID00=jf.BRID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=jf.BRID00 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
                 exception
                   when others then
                        Vbqjc00:=0;
                 end;
                 insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BQJC00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZP0000,BRJS00,YJJSR0,SSWR00,CZSJ00,XJTC00,YYID00)
                        values(jf.BRID00,Vczrq00,Vbqjc00,Vbqjc00+jf.JFJE00,jf.JFJE00,0,0,0,jf.JFJE00,0,0,decode(sign(jf.JFJE00),1,jf.JFJE00,0),0,Vjksj01,0,jf.YYID00);
              end if;
           elsif jf.ZFFS00 in (18) then--电子钱包
              update SF_YJJYE0 set BRZJ00=BRZJ00+jf.JFJE00,BQJC00=BQJC00+jf.JFJE00,
                   DZQBSR=nvl(DZQBSR,0)+decode(sign(jf.JFJE00),1,jf.JFJE00,0),
                   DZQBTC=nvl(DZQBTC,0)+decode(sign(jf.JFJE00),-1,jf.JFJE00,0)  --YJJSR0=YJJSR0+decode(sign(jf.JFJE00),1,jf.JFJE00,0)
                     where BRID00=jf.BRID00 and CZRQ00=Vczrq00 and YYID00=jf.YYID00;
              if sql%notfound then
                 --取上期余额
                 begin
                    select nvl(BQJC00,0) into Vbqjc00 from SF_YJJYE0
                           where BRID00=jf.BRID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=jf.BRID00 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
                 exception
                   when others then
                        Vbqjc00:=0;
                 end;
                 insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BQJC00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,DZQBSR,DZQBTC,ZP0000,BRJS00,YJJSR0,SSWR00,CZSJ00,XJTC00,YYID00)
                        values(jf.BRID00,Vczrq00,Vbqjc00,Vbqjc00+jf.JFJE00,jf.JFJE00,0,0,0,0,decode(sign(jf.JFJE00),1,jf.JFJE00,0),decode(sign(jf.JFJE00),-1,jf.JFJE00,0),0,0,0,0,Vjksj01,0,jf.YYID00);
              end if;
           elsif jf.ZFFS00 in (20) then--医院支付
              update SF_YJJYE0 set BRZJ00=BRZJ00+jf.JFJE00,BQJC00=BQJC00+jf.JFJE00,
                   YYZFSR=nvl(YYZFSR,0)+decode(sign(jf.JFJE00),1,jf.JFJE00,0),
                   YYZFTC=nvl(YYZFTC,0)+decode(sign(jf.JFJE00),-1,jf.JFJE00,0)
                     where BRID00=jf.BRID00 and CZRQ00=Vczrq00 and YYID00=jf.YYID00;
              if sql%notfound then
                 --取上期余额
                 begin
                    select nvl(BQJC00,0) into Vbqjc00 from SF_YJJYE0
                           where BRID00=jf.BRID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=jf.BRID00 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
                 exception
                   when others then
                        Vbqjc00:=0;
                 end;
                 insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BQJC00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,YYZFSR,YYZFTC,ZP0000,BRJS00,YJJSR0,SSWR00,CZSJ00,XJTC00,YYID00)
                        values(jf.BRID00,Vczrq00,Vbqjc00,Vbqjc00+jf.JFJE00,jf.JFJE00,0,0,0,0,decode(sign(jf.JFJE00),1,jf.JFJE00,0),decode(sign(jf.JFJE00),-1,jf.JFJE00,0),0,0,0,0,Vjksj01,0,jf.YYID00);
              end if;

           elsif jf.ZFFS00 in (24) then--微信支付
              update SF_YJJYE0 set BRZJ00=BRZJ00+jf.JFJE00,BQJC00=BQJC00+jf.JFJE00,
                   WXZFSR=nvl(WXZFSR,0)+decode(sign(jf.JFJE00),1,jf.JFJE00,0),
                   WXZFTC=nvl(WXZFTC,0)+decode(sign(jf.JFJE00),-1,jf.JFJE00,0)
                     where BRID00=jf.BRID00 and CZRQ00=Vczrq00 and YYID00=jf.YYID00;
              if sql%notfound then
                 --取上期余额
                 begin
                    select nvl(BQJC00,0) into Vbqjc00 from SF_YJJYE0
                           where BRID00=jf.BRID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=jf.BRID00 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
                 exception
                   when others then
                        Vbqjc00:=0;
                 end;
                 insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BQJC00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,WXZFSR,WXZFTC,ZP0000,BRJS00,YJJSR0,SSWR00,CZSJ00,XJTC00,YYID00)
                        values(jf.BRID00,Vczrq00,Vbqjc00,Vbqjc00+jf.JFJE00,jf.JFJE00,0,0,0,0,decode(sign(jf.JFJE00),1,jf.JFJE00,0),decode(sign(jf.JFJE00),-1,jf.JFJE00,0),0,0,0,0,Vjksj01,0,jf.YYID00);
              end if;
           elsif jf.ZFFS00 in (25) then--支付宝支付
              update SF_YJJYE0 set BRZJ00=BRZJ00+jf.JFJE00,BQJC00=BQJC00+jf.JFJE00,
                   ZFBSR0=nvl(ZFBSR0,0)+decode(sign(jf.JFJE00),1,jf.JFJE00,0),
                   ZFBTC0=nvl(ZFBTC0,0)+decode(sign(jf.JFJE00),-1,jf.JFJE00,0)
                     where BRID00=jf.BRID00 and CZRQ00=Vczrq00 and YYID00=jf.YYID00;
              if sql%notfound then
                 --取上期余额
                 begin
                    select nvl(BQJC00,0) into Vbqjc00 from SF_YJJYE0
                           where BRID00=jf.BRID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=jf.BRID00 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
                 exception
                   when others then
                        Vbqjc00:=0;
                 end;
                 insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BQJC00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZFBSR0,ZFBTC0,ZP0000,BRJS00,YJJSR0,SSWR00,CZSJ00,XJTC00,YYID00)
                        values(jf.BRID00,Vczrq00,Vbqjc00,Vbqjc00+jf.JFJE00,jf.JFJE00,0,0,0,0,decode(sign(jf.JFJE00),1,jf.JFJE00,0),decode(sign(jf.JFJE00),-1,jf.JFJE00,0),0,0,0,0,Vjksj01,0,jf.YYID00);
              end if;
                      elsif jf.ZFFS00 in (26) then--民生通支付
              update SF_YJJYE0 set BRZJ00=BRZJ00+jf.JFJE00,BQJC00=BQJC00+jf.JFJE00,
                   MSTSR0=nvl(MSTSR0,0)+decode(sign(jf.JFJE00),1,jf.JFJE00,0),
                   MSTTC0=nvl(MSTTC0,0)+decode(sign(jf.JFJE00),-1,jf.JFJE00,0)
                     where BRID00=jf.BRID00 and CZRQ00=Vczrq00 and YYID00=jf.YYID00;
              if sql%notfound then
                 --取上期余额
                 begin
                    select nvl(BQJC00,0) into Vbqjc00 from SF_YJJYE0
                           where BRID00=jf.BRID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=jf.BRID00 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
                 exception
                   when others then
                        Vbqjc00:=0;
                 end;
                 insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BQJC00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,MSTSR0,MSTTC0,ZP0000,BRJS00,YJJSR0,SSWR00,CZSJ00,XJTC00,YYID00)
                        values(jf.BRID00,Vczrq00,Vbqjc00,Vbqjc00+jf.JFJE00,jf.JFJE00,0,0,0,0,decode(sign(jf.JFJE00),1,jf.JFJE00,0),decode(sign(jf.JFJE00),-1,jf.JFJE00,0),0,0,0,0,Vjksj01,0,jf.YYID00);
              end if;
           elsif jf.ZFFS00 in (2) then--支票
              update SF_YJJYE0 set BRZJ00=BRZJ00+jf.JFJE00,ZP0000=ZP0000+jf.JFJE00,BQJC00=BQJC00+jf.JFJE00,YJJSR0=YJJSR0+decode(sign(jf.JFJE00),1,jf.JFJE00,0)
                     where BRID00=jf.BRID00 and CZRQ00=Vczrq00 and YYID00=jf.YYID00;
              if sql%notfound then
                 --取上期余额
                 begin
                    select nvl(BQJC00,0) into Vbqjc00 from SF_YJJYE0
                           where BRID00=jf.BRID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=jf.BRID00 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
                 exception
                   when others then
                        Vbqjc00:=0;
                 end;
                 insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BQJC00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZP0000,BRJS00,YJJSR0,SSWR00,CZSJ00,XJTC00,YYID00)
                        values(jf.BRID00,Vczrq00,Vbqjc00,Vbqjc00+jf.JFJE00,jf.JFJE00,0,0,0,0,jf.JFJE00,0,decode(sign(jf.JFJE00),1,jf.JFJE00,0),0,Vjksj01,0,jf.YYID00);
              end if;
           elsif jf.ZFFS00 in (5) then--四舍五入
              update SF_YJJYE0 set SSWR00=SSWR00+jf.JFJE00
                     where BRID00=jf.BRID00 and CZRQ00=Vczrq00 and YYID00=jf.YYID00;
              if sql%notfound then
                 --取上期余额
                 begin
                    select nvl(BQJC00,0) into Vbqjc00 from SF_YJJYE0
                           where BRID00=jf.BRID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=jf.BRID00 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
                 exception
                   when others then
                        Vbqjc00:=0;
                 end;
                 insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BQJC00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZP0000,BRJS00,YJJSR0,SSWR00,CZSJ00,XJTC00,YYID00)
                        values(jf.BRID00,Vczrq00,Vbqjc00,Vbqjc00,0,0,0,0,0,0,0,0,jf.JFJE00,Vjksj01,0,jf.YYID00);
              end if;
           elsif jf.ZFFS00=-1 then
              update SF_YJJYE0 set BRJS00=BRJS00+jf.JFJE00,BQJC00=BQJC00-jf.JFJE00
                     where BRID00=jf.BRID00 and CZRQ00=Vczrq00 and YYID00=jf.YYID00;
              if sql%notfound then
                 --取上期余额
                 begin
                    select nvl(BQJC00,0) into Vbqjc00 from SF_YJJYE0
                           where BRID00=jf.BRID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=jf.BRID00 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
                 exception
                   when others then
                        Vbqjc00:=0;
                 end;
                 insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BQJC00,BRJS00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZP0000,YJJSR0,CZSJ00,XJTC00,YYID00)
                        values(jf.BRID00,Vczrq00,Vbqjc00,Vbqjc00-jf.JFJE00,jf.JFJE00,0,0,0,0,0,0,0,Vjksj01,0,jf.YYID00);
                end if;
           end if;
        end if;
    end loop;
    --取上期结存
   -- begin
   --   select BQJC00 into Vbqjc00 from SF_YJJYE0 where BRID00=0 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=0 and CZRQ00<Vczrq00 and YYID00=jf.YYID00);
  --  exception
   --   when others then
   --     Vbqjc00:=0;
  --  end;
 /*
  --=================================分院数据暂时不写入

    insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BRJS00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZP0000,BQJC00,YJJSR0,SSWR00,CZSJ00,XJTC00,DZQBSR,DZQBTC,YYID00)
           select 0,Vczrq00
           --,Vbqjc00
           ,nvl((select BQJC00 from SF_YJJYE0 where BRID00=0 and YYID00=a.YYID00 and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=0 and CZRQ00<Vczrq00 and YYID00=a.YYID00)),0)
           ,sum(BRJS00),sum(BRZJ00),sum(XJ0000),sum(GRZHZF),sum(TCJJZF),sum(YHK000),sum(ZP0000),0,SUM(YJJSR0),SUM(SSWR00),Vjksj01,SUM(XJTC00),SUM(DZQBSR),SUM(DZQBTC),YYID00
           from SF_YJJYE0 a where CZRQ00=Vczrq00 and BRID00<>0 group by czrq00,YYID00;
    if sql%notfound then
       insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BRJS00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZP0000,BQJC00,YJJSR0,SSWR00,CZSJ00,XJTC00,DZQBSR,DZQBTC,YYID00)
           select 0,Vczrq00,SUM(BQJC00),0,0,0,0,0,0,0,0,0,0,Vjksj01,0,0,0,YYID00
           from SF_YJJYE0 where CZRQ00=to_char(to_date(Vczrq00,'YYYYMMDD')-1,'YYYYMMDD') and BRID00=0 group by czrq00,YYID00;
    end if ;
    insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BRJS00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZP0000,BQJC00,YJJSR0,SSWR00,CZSJ00,XJTC00,DZQBSR,DZQBTC,YYID00)
    select 0,CZRQ00
           --,Vbqjc00
           ,(select BQJC00 from SF_YJJYE0 where BRID00=0 and YYID00='0' and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=0 and CZRQ00<Vczrq00 and YYID00='0'))
           ,0,0,0,0,0,0,0,0,0,0,Vjksj01,0,0,0,'0'
           from SF_YJJYE0 a where CZRQ00>=Vczrq00 and BRID00=0  group by czrq00;
     */
    insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BRJS00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZP0000,
                           BQJC00,YJJSR0,SSWR00,CZSJ00,XJTC00,DZQBSR,DZQBTC,YYID00,
                           MSTSR0,MSTTC0,WXZFSR,WXZFTC,ZFBSR0,ZFBTC0,YYZFSR,YYZFTC)
           select 0,czrq00,
           nvl((select BQJC00 from SF_YJJYE0 where BRID00=0 and YYID00='0' and CZRQ00=(select max(CZRQ00) from SF_YJJYE0 where BRID00=0 and CZRQ00<Vczrq00 and YYID00='0')),0)
           ,sum(BRJS00),sum(BRZJ00),sum(XJ0000),sum(GRZHZF),sum(TCJJZF),sum(YHK000),sum(ZP0000),0,SUM(YJJSR0),SUM(SSWR00),Vjksj01,SUM(XJTC00),SUM(DZQBSR),SUM(DZQBTC),'0',
           sum(nvl(MSTSR0,0)),sum(nvl(MSTTC0,0)),sum(nvl(WXZFSR,0)),sum(nvl(WXZFTC,0)),sum(nvl(ZFBSR0,0)),sum(nvl(ZFBTC0,0)),sum(nvl(YYZFSR,0)),sum(nvl(YYZFTC,0))
           from SF_YJJYE0 a where CZRQ00=Vczrq00 and BRID00<>0
           group by czrq00;
    if sql%notfound then
       insert into SF_YJJYE0 (BRID00,CZRQ00,SQJC00,BRJS00,BRZJ00,XJ0000,GRZHZF,TCJJZF,YHK000,ZP0000,BQJC00,YJJSR0,SSWR00,CZSJ00,XJTC00,DZQBSR,DZQBTC,YYID00)
           select 0,Vczrq00,SUM(BQJC00),0,0,0,0,0,0,0,0,0,0,Vjksj01,0,0,0,'0'
           from SF_YJJYE0 where CZRQ00=to_char(to_date(Vczrq00,'YYYYMMDD')-1,'YYYYMMDD') and BRID00=0 group by czrq00;
    end if ;
    update SF_YJJYE0 set BQJC00=SQJC00+BRZJ00-BRJS00 where BRID00=0 and CZRQ00=Vczrq00 ;
    exit when Vczrq00=to_char(sysdate-1,'YYYYMMDD');
    commit;
  end loop;
  SP_SF_ZZYE00;
  commit;
end;
/

-- End of DDL script for SP_SF_YJJYE0