CREATE OR REPLACE PROCEDURE SP_ZY_BRJKQR_EDIT00
-- Person      Date        Comments
---zhuyr     2018.07.08    重新整理缴款确认功能 for ZYSF9-20180704-002
(
        ad_JKDH00    IN    NUMBER,     --缴款单号
        ad_JRJE00    IN    NUMBER,     --今日交款金额
        ad_CZY000    IN    NUMBER,     --操作员编码
        ad_CZBZ00    IN    CHAR,       --'1':确认,'2':修改,'3':取消
        as_YHMSG0    OUT   VARCHAR,    --存储过程提示的错误信息
        as_SYSMSG    OUT   VARCHAR     --系统提示的错误信息
)
as
        ls_jkrq00  bm_jkb000.jkrq00%type;
        ls_jksj00  bm_jkb000.jksj00%type;
        ls_jrye00  bm_jkkjmx.xmje00%type;
        ls_hrye00  bm_jkkjmx.xmje00%type;
        ls_sjhjje  bm_jkkjmx.xmje00%type;
        ls_sfy000  bm_jkb000.sfy000%type;
        ls_jrsj00  bm_jkkjmx.xmje00%type;
        ls_jkdh01  bm_jkkjmx.jkdh00%type;
        ls_xjje00  bm_jkkjmx.xmje00%type;
        ls_czyxm0  bm_ygbm00.ZWXM00%type;
        cursor C_jkdh00 is select distinct jkdh00 from bm_jkb000 where sfy000=ls_sfy000 and jkdh00>ad_jkdh00 and MZBZ00='1' order by jkdh00;
BEGIN
        ls_jkrq00:=to_char(sysdate,'YYYYMMDD');
        ls_jksj00:=to_char(sysdate,'HH24:MI:SS');
        
        select sfy000 into ls_sfy000 from bm_jkb000 where jkdh00=ad_jkdh00 and rownum=1;
        select ZWXM00 into ls_czyxm0 from bm_ygbm00 where ygbh00=ad_CZY000;
        if ad_czbz00='1' then
             select min(jkdh00) into ls_jkdh01 from bm_jkb000 where qrbz00=0
                  and sfy000=ls_sfy000 and jkdh00<=ad_jkdh00 and mzbz00='1';
             /*if ls_jkdh01<>ad_jkdh00 then
                  as_YHMSG0:='当前缴款单号不是该收费员最小的缴款单号"'||to_char(ls_jkdh01)||'"';
                  as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_BRJKQR_EDIT00('||to_char(ad_JKDH00)
                  ||','||to_char(ad_JRJE00)||','||to_char(ad_CZY000)||','||ad_CZBZ00||',as_YHMSG0,as_SYSMSG)',1,200);
                  ROLLBACK;
                  return;
             end if;*/
             select nvl(sum(a.xmje00),0) into ls_hrye00 from bm_jkkjmx a
                where a.xmbh00='81'
                and a.jkdh00=(select max(c.jkdh00) from bm_jkb000 c where c.mzbz00='1' and c.sfy000=ls_sfy000 and c.jkdh00<ad_jkdh00);
             select nvl(sum(xmje00),0) into ls_sjhjje  from bm_jkkjmx where xmbh00='35' and jkdh00=ad_jkdh00;
             select nvl(sum(xmje00),0) into ls_jrsj00  from bm_jkkjmx where xmbh00='80' and jkdh00=ad_jkdh00;
             --update bm_jkkjmx set xmje00=ad_jrje00 where xmbh00='80' and jkdh00=ad_jkdh00;
             --if  SQL%NOTFOUND  then
             --     insert  into  BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00)
             --        values(ad_JKDH00,'80',' 21、今日上交',ad_jrje00,'','1',42);
             --end if;
             if ls_jrsj00<0 then
                ls_jrsj00:=0;
             end if;
             update bm_jkkjmx set xmje00=nvl(ls_hrye00,0)+nvl(ls_sjhjje,0)-nvl(ls_jrsj00,0)
                where xmbh00='81' and jkdh00=ad_jkdh00;
             if  SQL%NOTFOUND  then
                insert  into  BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00)
                   values(ad_JKDH00,'81',' 22、今日余额',(nvl(ls_hrye00,0)+nvl(ls_sjhjje,0)-nvl(ls_jrsj00,0)),'','1',43);
             end if;
             update bm_jkb000 set qrbz00=1, qrrq00=ls_jkrq00, qrsj00=ls_jksj00, qrry00=ad_CZY000
                    where jkdh00=ad_jkdh00;
            
             insert into bm_jkqr00(jkdh00,mzzybz,qrry00,qrryxm,qrrq00,qrsj00)
                      values(ad_jkdh00,2,ad_CZY000,ls_czyxm0,ls_jkrq00,ls_jksj00);

        end if;
        --缴款单修改余额
        if ad_czbz00='2' then
           --修改本日上缴
           update bm_jkkjmx set xmje00=ad_jrje00 where xmbh00='80' and jkdh00=ad_jkdh00;
           if  SQL%NOTFOUND  then
                  insert  into  BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00)
                   values(ad_JKDH00,'80',' 21、今日上交',ad_jrje00,'','1',42);
           end if;
           --取上期余额
           select xmje00 into ls_xjje00 from bm_jkkjmx where xmbh00='81' and jkdh00=(
                     select max(jkdh00) from bm_jkb000 where jkdh00<ad_jkdh00 and sfy000=ls_sfy000 and mzbz00='1');
           --修改本期余额=上期余额+本期应缴(不含上期余额)-本期上缴
           update BM_JKKJMX set XMJE00=(select ls_xjje00+sum(decode(XMBH00,35,XMJE00,-1*XMJE00)) from BM_JKKJMX where XMBH00 in (35,80) and JKDH00=ad_jkdh00)
           where  XMBH00='81' and JKDH00=ad_jkdh00;
           --修改后续缴款单的缴款上限
           for jkdh in C_jkdh00 loop
             --取最近一次缴款的本期余额
             select XMJE00 into ls_XJJE00 from BM_JKKJMX
                    where JKDH00=(select max(jkdh00) from BM_JKB000
                                         where JKDH00<jkdh.JKDH00 and SFY000=ls_sfy000 and mzbz00='1') and XMBH00='81';
             --修改今日上缴=上期余额+本期应缴
             select XMJE00+ls_XJJE00 into ls_XJJE00 from BM_JKKJMX where JKDH00=jkdh.JKDH00 and XMBH00='35';
             update BM_JKKJMX set XMJE00=greatest(ls_XJJE00,0) where JKDH00=jkdh.JKDH00 and XMBH00='80';
             --修改今日余额
             update BM_JKKJMX set XMJE00=Least(ls_XJJE00,0) where  XMBH00='81' and JKDH00=jkdh.JKDH00;
           end loop;

        end if;
        
        if ad_czbz00='3' then
             select min(jkdh00) into ls_jkdh01 from bm_jkb000 where qrbz00=0
                  and sfy000=ls_sfy000 and jkdh00<=ad_jkdh00 and mzbz00='1';
             /*if ls_jkdh01<>ad_jkdh00 then
                  as_YHMSG0:='当前缴款单号不是该收费员最小的缴款单号"'||to_char(ls_jkdh01)||'"';
                  as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_BRJKQR_EDIT00('||to_char(ad_JKDH00)
                  ||','||to_char(ad_JRJE00)||','||to_char(ad_CZY000)||','||ad_CZBZ00||',as_YHMSG0,as_SYSMSG)',1,200);
                  ROLLBACK;
                  return;
             end if;*/
             select nvl(sum(a.xmje00),0) into ls_hrye00 from bm_jkkjmx a
                where a.xmbh00='81'
                and a.jkdh00=(select max(c.jkdh00) from bm_jkb000 c where c.mzbz00='1' and c.sfy000=ls_sfy000 and c.jkdh00<ad_jkdh00);
             select nvl(sum(xmje00),0) into ls_sjhjje  from bm_jkkjmx where xmbh00='35' and jkdh00=ad_jkdh00;
             select nvl(sum(xmje00),0) into ls_jrsj00  from bm_jkkjmx where xmbh00='80' and jkdh00=ad_jkdh00;
             --update bm_jkkjmx set xmje00=ad_jrje00 where xmbh00='80' and jkdh00=ad_jkdh00;
             --if  SQL%NOTFOUND  then
             --     insert  into  BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00)
             --        values(ad_JKDH00,'80',' 21、今日上交',ad_jrje00,'','1',42);
             --end if;
             if ls_jrsj00<0 then
                ls_jrsj00:=0;
             end if;
             update bm_jkkjmx set xmje00=0
                where xmbh00='81' and jkdh00=ad_jkdh00;
             --if  SQL%NOTFOUND  then
             --   insert  into  BM_JKKJMX(JKDH00,XMBH00,XMMC00,XMJE00,BEIZHU,BZ0000,XMXH00)
             --      values(ad_JKDH00,'81',' 22、今日余额',(nvl(ls_hrye00,0)+nvl(ls_sjhjje,0)-nvl(ls_jrsj00,0)),'','1',43);
             --end if;
             update bm_jkb000 set qrbz00=0, qrrq00='', qrsj00='', qrry00=''
                    where jkdh00=ad_jkdh00;
                    
             delete bm_jkqr00 where jkdh00 = ad_jkdh00;
             --insert into bm_jkqr00(jkdh00,mzzybz,qrry00,qrryxm,qrrq00,qrsj00)
             --         values(ad_jkdh00,2,ls_sfy000,ls_czyxm0,ls_jkrq00,ls_jksj00);

        end if;
        COMMIT;
EXCEPTION
        WHEN OTHERS THEN
                as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
                as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_BRJKQR_EDIT00('||to_char(ad_JKDH00)
                ||','||to_char(ad_JRJE00)||','||to_char(ad_CZY000)||','||ad_CZBZ00||',as_YHMSG0,as_SYSMSG)',1,200);
        ROLLBACK;
END;