oracle 存储过程 调用存储过程

CREATE OR REPLACE PROCEDURE wm_jhze_chk(cpcode in char,out_cursor out sys_refcursor) as rowcount int; BEGIN delete from err_msg; delete from ls_tssb; delete from ls_tsjh; delete from ls_tssb_sum1; delete from ls_tssb_maxid; delete from ls_tssb_mindate; delete from ls_tsjh_mindate; delete from ls_tsjh_sum_xfs; delete from ls_tsjh_sum_xfs_cjdl; delete from ls_tsjh_sum_xfs_clde; delete from ls_tssb_sum_xfs; delete from ls_tssb_sum_xfs_cjdl; delete from ls_tssb_sum_xfs_clde; delete from ls_tssb_maxid1; delete from ls_tssb_sum2; delete from ls_tssb_maxid2; delete from ls_tssb_sum3; DELETE from ls_tssb_double; DELETE from ls_tssb_double_sum; delete from ls_tsjh_double1; delete from ls_tsjh_double21; delete from ls_tsjh_double11; delete from ls_tsjh_double2; delete from ls_tssb_maxid3; delete from ls_tssb_sum4; --建立临时表 insert into ls_tssb select * from wm_tssb where cpcode = cpcode; insert into ls_tsjh select * from wm_tsjh where cpcode = cpcode; --建立临时汇总数据表 insert into ls_tsjh_sum_all(ldlp_no,cmcode,sz,dpcode,qnt,amt,sl,zsl,se,tsl,ts_amt,ts_pri,amt_pri) SELECT ldlp_no ,cmcode,sz,dpcode,SUM(qnt) AS qnt,SUM(amt) AS amt,SUM(Sl) AS sl,SUM(zsl) AS zsl,SUM(se) AS se,SUM(tsl) AS tsl,SUM(ts_amt) AS ts_amt, SUM(ts_amt) AS ts_pri,SUM(ts_amt) AS amt_pri FROM ls_tsjh GROUP BY LDLP_NO,CMCODE,SZ,dpcode; insert into ls_tssb_sum_all(LDLP_NO ,CMCODE,dpcode,qnt,zzs_ts_amt,xfs_ts_amt) SELECT LDLP_NO ,CMCODE,dpcode,SUM(qnt) AS qnt,SUM(zzs_ts_amt) AS zzs_ts_amt,SUM(xfs_ts_amt) AS xfs_ts_amt FROM ls_tssb GROUP BY LDLP_NO ,CMCODE ,dpcode; --更新单价 UPDATE ls_tsjh_sum_all SET ts_pri = (case when qnt<>0 then amt/qnt else 0 end), amt_pri= (case when qnt<>0 then ts_amt/qnt else 0 end); --增值税汇总临时表 delete from ls_tsjh_sum_zzs; insert into ls_tsjh_sum_zzs select * from ls_tsjh_sum_all where sz='V' ; --更新出口表 --更新出口表 UPDATE ls_tssb SET (ts_qnt, tsl, ts_pri) = (select (case when ls_tssb.qnt>ls_tsjh_sum_zzs.qnt then ls_tsjh_sum_zzs.qnt else ls_tssb.qnt end ), (case when ls_tsjh_sum_zzs.qnt<>0 then ls_tsjh_sum_zzs.ts_amt/ls_tsjh_sum_zzs.amt*100 else 0 end), ls_tsjh_sum_zzs.ts_pri from ls_tsjh_sum_zzs where ls_tssb.ldlp_no = ls_tsjh_sum_zzs.ldlp_no and ls_tssb.cmcode = ls_tsjh_sum_zzs.cmcode) where exists (select 1 from ls_tsjh_sum_zzs where ls_tssb.ldlp_no = ls_tsjh_sum_zzs.ldlp_no and ls_tssb.cmcode = ls_tsjh_sum_zzs.cmcode); update ls_tssb set (ckjh_amt,zzs_ts_amt) = (select (case when ls_tsjh_sum_zzs.qnt<>0 then ls_tssb.ts_qnt*ls_tsjh_sum_zzs.amt/ls_tsjh_sum_zzs.qnt else 0 end), (case when ls_tsjh_sum_zzs.qnt<>0 and ls_tsjh_sum_zzs.amt<>0 then ls_tssb.ts_qnt*ls_tsjh_sum_zzs.ts_amt/ls_tsjh_sum_zzs.qnt else 0 end) from ls_tsjh_sum_zzs where ls_tssb.ldlp_no = ls_tsjh_sum_zzs.ldlp_no and ls_tssb.cmcode = ls_tsjh_sum_zzs.cmcode) where exists (select 1 from ls_tsjh_sum_zzs where ls_tssb.ldlp_no = ls_tsjh_sum_zzs.ldlp_no and ls_tssb.cmcode = ls_tsjh_sum_zzs.cmcode ); update ls_tssb set ts_qnt=0,flag='E',ckjh_amt=0,ts_pri=0,zzs_ts_amt=0,tsl=0 where EXISTS (select 1 from ls_tsjh_sum_zzs where ls_tssb.LDLP_NO = ls_tsjh_sum_zzs.LDLP_NO and ls_tssb.CMCODE = ls_tsjh_sum_zzs.CMCODE and ls_tsjh_sum_zzs.qnt-ls_tssb.qnt<0); --检查进货出口数量 delete from err_msg; insert into err_msg select ls_tsjh_sum_zzs.LDLP_NO,ls_tsjh_sum_zzs.dpcode,ls_tsjh_sum_zzs.CMCODE, ls_tsjh_sum_zzs.qnt,LS_TSSB_SUM_ALL.qnt,'E','1' from ls_tsjh_sum_zzs inner join LS_TSSB_SUM_ALL on ls_tsjh_sum_zzs.ldlp_no = LS_TSSB_SUM_ALL.ldlp_no and ls_tsjh_sum_zzs.cmcode = LS_TSSB_SUM_ALL.cmcode where LS_TSSB_SUM_ALL.qnt<>ls_tsjh_sum_zzs.qnt; select count(*) into rowcount from ls_tsjh_sum_zzs inner join LS_TSSB_SUM_ALL on ls_tsjh_sum_zzs.ldlp_no = LS_TSSB_SUM_ALL.ldlp_no and ls_tsjh_sum_zzs.cmcode = LS_TSSB_SUM_ALL.cmcode where LS_TSSB_SUM_ALL.qnt<>ls_tsjh_sum_zzs.qnt; if rowcount =0 THEN insert into ls_tssb_maxid(id,ldlp_no,cmcode,zzs_ts_amt,amt1) select max(id) as id,ldlp_no,cmcode,0 as zzs_ts_amt,0 as amt1 from ls_tssb group by LDLP_NO ,CMCODE; insert into ls_tssb_sum1(LDLP_NO,CMCODE,qnt,zzs_ts_amt) select LDLP_NO,CMCODE,SUM(qnt) AS qnt,SUM(zzs_ts_amt) AS zzs_ts_amt from ls_tssb where not exists(select * from ls_tssb_maxid where ls_tssb.id=ls_tssb_maxid.id) group by LDLP_NO ,CMCODE ; update ls_tssb_maxid set zzs_ts_amt=(select ls_tssb_sum1.zzs_ts_amt from ls_tssb_sum1 where ls_tssb_maxid.ldlp_no=ls_tssb_sum1.ldlp_no and ls_tssb_maxid.cmcode=ls_tssb_sum1.cmcode) where EXISTS (select 1 from ls_tssb_sum1 where ls_tssb_maxid.ldlp_no=ls_tssb_sum1.ldlp_no and ls_tssb_maxid.cmcode=ls_tssb_sum1.cmcode); update ls_tssb_maxid set amt1 = (select ls_tsjh_sum_zzs.ts_amt from ls_tsjh_sum_zzs where ls_tssb_maxid.ldlp_no=ls_tsjh_sum_zzs.ldlp_no and ls_tssb_maxid.cmcode=ls_tsjh_sum_zzs.cmcode) where exists (select 1 from ls_tsjh_sum_zzs where ls_tssb_maxid.ldlp_no=ls_tsjh_sum_zzs.ldlp_no and ls_tssb_maxid.cmcode=ls_tsjh_sum_zzs.cmcode); update ls_tssb_maxid set zzs_ts_amt = amt1- zzs_ts_amt where amt1<>0 ; update ls_tssb set zzs_ts_amt =(select ls_tssb_maxid.zzs_ts_amt from ls_tssb_maxid where ls_tssb_maxid.id=ls_tssb.id) where EXISTS (select ls_tssb_maxid.zzs_ts_amt from ls_tssb_maxid where ls_tssb_maxid.id=ls_tssb.id) ; --rop table ls_tssb_maxid; --drop table ls_tssb_sum1; end if; insert into err_msg select LS_TSSB_SUM_ALL.LDLP_NO,LS_TSSB_SUM_ALL.dpcode,LS_TSSB_SUM_ALL.CMCODE, 0,LS_TSSB_SUM_ALL.qnt,'W','1' from LS_TSSB_SUM_ALL where not exists(select * from ls_tsjh_sum_zzs where ls_tsjh_sum_zzs.ldlp_no = LS_TSSB_SUM_ALL.ldlp_no and ls_tsjh_sum_zzs.cmcode = LS_TSSB_SUM_ALL.cmcode); --检查只有进货没有出口 insert into err_msg select LS_TSJH_SUM_ALL.LDLP_NO,ls_tsjh_sum_ALL.dpcode,ls_tsjh_sum_ALL.CMCODE, ls_tsjh_sum_ALL.qnt,0,'E','1' from ls_tsjh_sum_ALL where not exists(select * from ls_tssb_sum_ALL where ls_tsjh_sum_ALL.ldlp_no = LS_TSSB_SUM_ALL.ldlp_no and LS_TSJH_SUM_ALL.cmcode = LS_TSSB_SUM_ALL.cmcode); --检查消费税 --建立最小出口日期出口表 insert into ls_tssb_mindate(ldlp_no,cmcode,m_lj_date) select ldlp_no,cmcode,min(lj_date) as m_lj_date from ls_tssb group by LDLP_NO ,CMCODE ; --建立最小出口日期进货表 insert into ls_tsjh_mindate select ls_tsjh.ldlp_no,ls_tsjh.sz,ls_tsjh.dpcode,ls_tsjh.sb_ym,ls_tsjh.sb_pc, ls_tsjh.sb_no,ls_tsjh.fp_no,ls_tsjh.fp_dm, ls_tsjh.zyfp_no,ls_tsjh.fp_flag,ls_tsjh.ghfns_no,ls_tsjh.kpdate,ls_tsjh.cmcode,ls_tsjh.cmname,ls_tsjh.cmunit,ls_tsjh.qnt,ls_tsjh.amt,ls_tsjh.sl,ls_tsjh.zsl,ls_tsjh.se,ls_tsjh.tsl,ls_tsjh.ts_amt,ls_tsjh.zysp_no,ls_tsjh.note,ls_tsjh.sb_rsv,ls_tsjh.flag,ls_tsjh.sb_flag,ls_tsjh.tz_flag,ls_tsjh.sh_flag,ls_tsjh.sh_time,ls_tsjh.op_user,ls_tsjh.op_date, ls_tssb_mindate.m_lj_date as lj_date from ls_tsjh left join ls_tssb_mindate on ls_tsjh.ldlp_no = ls_tssb_mindate.ldlp_no and ls_tsjh.cmcode = ls_tssb_mindate.cmcode; --消费税进货汇总 insert into ls_tsjh_sum_xfs select ldlp_no ,cmcode,ls_tsjh_mindate.sz,dpcode,SUM(qnt) AS qnt,SUM(amt) AS amt,SUM(Sl) AS sl,SUM(zsl) AS zsl,SUM(se) AS se,SUM(ls_tsjh_mindate.tsl) AS tsl,SUM(ts_amt) AS ts_amt, SUM(ts_amt) AS ts_pri,SUM(ts_amt) AS amt_pri from ls_tsjh_mindate left join allcmcode cmcode on ls_tsjh_mindate.cmcode = cmcode.code and cmcode.st_date<= ls_tsjh_mindate.lj_date and cmcode.end_date> ls_tsjh_mindate.lj_date where ls_tsjh_mindate.sz='C' and ((cmcode.cjdl <> 0 and cmcode.clde = 0) or (cmcode.cjdl = 0 and cmcode.clde <> 0)) GROUP BY LDLP_NO,CMCODE,ls_tsjh_mindate.SZ,dpcode; insert into ls_tsjh_sum_xfs_cjdl select ldlp_no ,cmcode,ls_tsjh_mindate.sz,dpcode,SUM(qnt) AS qnt,SUM(amt) AS amt,SUM(Sl) AS sl,SUM(zsl) AS zsl,SUM(se) AS se,SUM(ls_tsjh_mindate.tsl) AS tsl,SUM(ts_amt) AS ts_amt, SUM(ts_amt) AS ts_pri,SUM(ts_amt) AS amt_pri from ls_tsjh_mindate left join allcmcode cmcode on ls_tsjh_mindate.cmcode = cmcode.code and cmcode.st_date<= ls_tsjh_mindate.lj_date and cmcode.end_date> ls_tsjh_mindate.lj_date where ls_tsjh_mindate.sz='C' and (cmcode.cjdl <> 0 and cmcode.clde = 0) GROUP BY LDLP_NO,CMCODE,ls_tsjh_mindate.SZ,dpcode; insert into ls_tsjh_sum_xfs_clde select ldlp_no ,cmcode,ls_tsjh_mindate.sz,dpcode,SUM(qnt) AS qnt,SUM(amt) AS amt,SUM(Sl) AS sl,SUM(zsl) AS zsl,SUM(se) AS se,SUM(ls_tsjh_mindate.tsl) AS tsl,SUM(ts_amt) AS ts_amt, SUM(ts_amt) AS ts_pri,SUM(ts_amt) AS amt_pri from ls_tsjh_mindate left join allcmcode cmcode on ls_tsjh_mindate.cmcode = cmcode.code and cmcode.st_date<= ls_tsjh_mindate.lj_date and cmcode.end_date> ls_tsjh_mindate.lj_date where ls_tsjh_mindate.sz='C' and (cmcode.cjdl = 0 and cmcode.clde <> 0) GROUP BY LDLP_NO,CMCODE,ls_tsjh_mindate.SZ,dpcode; --消费税出口汇总 insert into ls_tssb_sum_xfs select LDLP_NO ,CMCODE,dpcode,SUM(qnt) AS qnt,SUM(zzs_ts_amt) AS zzs_ts_amt,SUM(xfs_ts_amt) AS xfs_ts_amt from ls_tssb left join allcmcode cmcode on ls_tssb.cmcode = cmcode.code and cmcode.st_date<= ls_tssb.lj_date and cmcode.end_date> ls_tssb.lj_date where (cmcode.cjdl <> 0 and cmcode.clde = 0) or(cmcode.cjdl = 0 and cmcode.clde <> 0) GROUP BY LDLP_NO ,CMCODE ,dpcode; insert into ls_tssb_sum_xfs_cjdl select LDLP_NO ,CMCODE,dpcode,SUM(qnt) AS qnt,SUM(zzs_ts_amt) AS zzs_ts_amt,SUM(xfs_ts_amt) AS xfs_ts_amt from ls_tssb left join allcmcode cmcode on ls_tssb.cmcode = cmcode.code and cmcode.st_date<= ls_tssb.lj_date and cmcode.end_date> ls_tssb.lj_date where cmcode.cjdl <> 0 and cmcode.clde = 0 GROUP BY LDLP_NO ,CMCODE ,dpcode; insert into ls_tssb_sum_xfs_clde select LDLP_NO ,CMCODE,dpcode,SUM(qnt) AS qnt,SUM(zzs_ts_amt) AS zzs_ts_amt,SUM(xfs_ts_amt) AS xfs_ts_amt from ls_tssb left join allcmcode cmcode on ls_tssb.cmcode = cmcode.code and cmcode.st_date<= ls_tssb.lj_date and cmcode.end_date> ls_tssb.lj_date where cmcode.cjdl = 0 and cmcode.clde <> 0 GROUP BY LDLP_NO ,CMCODE ,dpcode; --更新消费税明细数据 update ls_tssb set xfs_ts_amt =(select (case when ls_tssb.qnt>ls_tsjh_sum_xfs.qnt then ls_tsjh_sum_xfs.qnt else ls_tssb.qnt end)*cmcode.cjdl*(case when ls_tsjh_sum_xfs.qnt<>0 then ls_tsjh_sum_xfs.amt/ls_tsjh_sum_xfs.qnt else 0 end) from ls_tssb inner join ls_tsjh_sum_xfs on ls_tssb.ldlp_no = ls_tsjh_sum_xfs.ldlp_no and ls_tssb.cmcode = ls_tsjh_sum_xfs.cmcode left join allcmcode cmcode on ls_tssb.cmcode = cmcode.code and cmcode.st_date<= ls_tssb.lj_date and cmcode.end_date> ls_tssb.lj_date where cmcode.cjdl<>0 and cmcode.clde=0 ) ; update ls_tssb set xfs_ts_amt =(select (case when ls_tssb.qnt>ls_tsjh_sum_xfs.qnt then ls_tsjh_sum_xfs.qnt else ls_tssb.qnt end)*cmcode.clde from ls_tssb inner join ls_tsjh_sum_xfs on ls_tssb.ldlp_no = ls_tsjh_sum_xfs.ldlp_no and ls_tssb.cmcode = ls_tsjh_sum_xfs.cmcode left join allcmcode cmcode on ls_tssb.cmcode = cmcode.code and cmcode.st_date<= ls_tssb.lj_date and cmcode.end_date> ls_tssb.lj_date where cmcode.clde<>0 and cmcode.cjdl=0); update ls_tssb set flag='E',xfs_ts_amt=0 where EXISTS (select 1 from ls_tsjh_sum_xfs where ls_tssb.LDLP_NO = ls_tsjh_sum_xfs.LDLP_NO and ls_tssb.CMCODE = ls_tsjh_sum_xfs.CMCODE and ls_tsjh_sum_xfs.qnt-ls_tssb.qnt<0); --从价定律出口进货数量不等 insert into err_msg select ls_tsjh_sum_xfs_clde.LDLP_NO,ls_tsjh_sum_xfs_clde.dpcode,ls_tsjh_sum_xfs_clde.CMCODE, ls_tsjh_sum_xfs_clde.qnt,ls_tssb_sum_xfs_clde.qnt,'E','2' from ls_tsjh_sum_xfs_clde inner join ls_tssb_sum_xfs_clde on ls_tsjh_sum_xfs_clde.ldlp_no = ls_tssb_sum_xfs_clde.ldlp_no and ls_tsjh_sum_xfs_clde.cmcode = ls_tssb_sum_xfs_clde.cmcode where ls_tssb_sum_xfs_clde.qnt<>ls_tsjh_sum_xfs_clde.qnt ; --从量定额出口数量不等 insert into err_msg select ls_tsjh_sum_xfs_cjdl.LDLP_NO,ls_tsjh_sum_xfs_cjdl.dpcode,ls_tsjh_sum_xfs_cjdl.CMCODE, ls_tsjh_sum_xfs_cjdl.qnt,ls_tssb_sum_xfs_cjdl.qnt,'E','3' from ls_tsjh_sum_xfs_cjdl inner join ls_tssb_sum_xfs_cjdl on ls_tsjh_sum_xfs_cjdl.ldlp_no = ls_tssb_sum_xfs_cjdl.ldlp_no and ls_tsjh_sum_xfs_cjdl.cmcode = ls_tssb_sum_xfs_cjdl.cmcode where ls_tssb_sum_xfs_cjdl.qnt<>ls_tsjh_sum_xfs_cjdl.qnt; --建立没有最后一条记录的汇总数据表 select count(*) into rowcount from ls_tsjh_sum_xfs_cjdl inner join ls_tssb_sum_xfs_cjdl on ls_tsjh_sum_xfs_cjdl.ldlp_no = ls_tssb_sum_xfs_cjdl.ldlp_no and ls_tsjh_sum_xfs_cjdl.cmcode = ls_tssb_sum_xfs_cjdl.cmcode where ls_tssb_sum_xfs_cjdl.qnt<>ls_tsjh_sum_xfs_cjdl.qnt; if rowcount=0 then insert into ls_tssb_maxid1(id,ldlp_no,cmcode,zzs_ts_amt,xfs_ts_amt,amt1) select max(id) as id,ldlp_no,cmcode,0 as zzs_ts_amt,0 as xfs_ts_amt,0 as amt1 from ls_tssb group by LDLP_NO ,CMCODE; insert into ls_tssb_sum2 select LDLP_NO ,CMCODE,SUM(qnt) AS qnt,SUM(zzs_ts_amt) AS zzs_ts_amt,SUM(xfs_ts_amt) AS xfs_ts_amt from ls_tssb where not exists(select * from ls_tssb_maxid1 where ls_tssb.id=ls_tssb_maxid1.id) group by LDLP_NO,CMCODE ; update ls_tssb_maxid1 set xfs_ts_amt= (select ls_tssb_sum2.xfs_ts_amt from ls_tssb_sum2 where ls_tssb_maxid1.ldlp_no=ls_tssb_sum2.ldlp_no and ls_tssb_maxid1.cmcode=ls_tssb_sum2.cmcode); update ls_tssb_maxid1 set amt1 = (select ls_tsjh_sum_xfs_cjdl.ts_amt from ls_tsjh_sum_xfs_cjdl where ls_tssb_maxid1.ldlp_no=ls_tsjh_sum_xfs_cjdl.ldlp_no and ls_tssb_maxid1.cmcode=ls_tsjh_sum_xfs_cjdl.cmcode) where exists (select 1 from ls_tsjh_sum_xfs_cjdl where ls_tssb_maxid1.ldlp_no=ls_tsjh_sum_xfs_cjdl.ldlp_no and ls_tssb_maxid1.cmcode=ls_tsjh_sum_xfs_cjdl.cmcode); update ls_tssb_maxid1 set xfs_ts_amt = (select amt1- xfs_ts_amt from ls_tssb_maxid1 where amt1<>0 ); update ls_tssb set xfs_ts_amt = (select ls_tssb_maxid1.xfs_ts_amt from ls_tssb_maxid1 where ls_tssb_maxid1.id=ls_tssb.id) where exists (select 1 from ls_tssb_maxid1 where ls_tssb_maxid1.id=ls_tssb.id ); end if; select count(*) into rowcount from ls_tsjh_sum_xfs_clde inner join ls_tssb_sum_xfs_clde on ls_tsjh_sum_xfs_clde.ldlp_no = ls_tssb_sum_xfs_clde.ldlp_no and ls_tsjh_sum_xfs_clde.cmcode = ls_tssb_sum_xfs_clde.cmcode where ls_tssb_sum_xfs_clde.qnt<>ls_tsjh_sum_xfs_clde.qnt; if rowcount=0 then insert into ls_tssb_maxid2(id,ldlp_no,cmcode,zzs_ts_amt,xfs_ts_amt,amt1) select max(id) as id,ldlp_no,cmcode,0 as zzs_ts_amt,0 as xfs_ts_amt,0 as amt1 from ls_tssb group by LDLP_NO ,CMCODE; insert into ls_tssb_sum3 select LDLP_NO ,CMCODE,SUM(qnt) AS qnt,SUM(zzs_ts_amt) AS zzs_ts_amt,SUM(xfs_ts_amt) AS xfs_ts_amt from ls_tssb where not exists(select * from ls_tssb_maxid2 where ls_tssb.id=ls_tssb_maxid2.id) group by LDLP_NO ,CMCODE ; update ls_tssb_maxid2 set xfs_ts_amt = (select ls_tssb_sum3.xfs_ts_amt from ls_tssb_sum3 where ls_tssb_maxid2.ldlp_no=ls_tssb_sum3.ldlp_no and ls_tssb_maxid2.cmcode=ls_tssb_sum3.cmcode) where exists (select 1 from ls_tssb_sum3 where ls_tssb_maxid2.ldlp_no=ls_tssb_sum3.ldlp_no and ls_tssb_maxid2.cmcode=ls_tssb_sum3.cmcode); update ls_tssb_maxid2 set amt1 = (select ls_tsjh_sum_xfs_clde.ts_amt from ls_tsjh_sum_xfs_clde where ls_tssb_maxid2.ldlp_no=ls_tsjh_sum_xfs_clde.ldlp_no and ls_tssb_maxid2.cmcode=ls_tsjh_sum_xfs_clde.cmcode) where EXISTS (select 1 from ls_tsjh_sum_xfs_clde where ls_tssb_maxid2.ldlp_no=ls_tsjh_sum_xfs_clde.ldlp_no and ls_tssb_maxid2.cmcode=ls_tsjh_sum_xfs_clde.cmcode) ; update ls_tssb_maxid2 set xfs_ts_amt =(select amt1- xfs_ts_amt from ls_tssb_maxid2 where amt1<>0 ); update ls_tssb set xfs_ts_amt =(select ls_tssb_maxid2.xfs_ts_amt from ls_tssb_maxid2 where ls_tssb_maxid2.id=ls_tssb.id) where exists (select 1 from ls_tssb_maxid2 where ls_tssb_maxid2.id=ls_tssb.id); end if; --消费税从价定律 insert into err_msg select ls_tssb_sum_xfs_cjdl.LDLP_NO,ls_tssb_sum_xfs_cjdl.dpcode,ls_tssb_sum_xfs_cjdl.CMCODE, 0,ls_tssb_sum_xfs_cjdl.qnt,'W','2' from ls_tssb_sum_xfs_cjdl where not exists(select * from ls_tsjh_sum_xfs where ls_tssb_sum_xfs_cjdl.ldlp_no=ls_tsjh_sum_xfs.ldlp_no and ls_tssb_sum_xfs_cjdl.cmcode=ls_tsjh_sum_xfs.cmcode); insert into err_msg select ls_tsjh_sum_xfs.LDLP_NO,ls_tsjh_sum_xfs.dpcode,ls_tsjh_sum_xfs.CMCODE, ls_tsjh_sum_xfs.qnt,0,'E','2' from ls_tsjh_sum_xfs where not exists(select * from ls_tssb_sum_xfs_cjdl where ls_tssb_sum_xfs_cjdl.ldlp_no=ls_tsjh_sum_xfs.ldlp_no and ls_tssb_sum_xfs_cjdl.cmcode=ls_tsjh_sum_xfs.cmcode); --消费税从量定额 insert into err_msg select ls_tssb_sum_xfs_clde.LDLP_NO,ls_tssb_sum_xfs_clde.dpcode,ls_tssb_sum_xfs_clde.CMCODE, 0,ls_tssb_sum_xfs_clde.qnt,'W','3' from ls_tssb_sum_xfs_clde where not exists(select * from ls_tsjh_sum_xfs where ls_tssb_sum_xfs_clde.ldlp_no=ls_tsjh_sum_xfs.ldlp_no and ls_tssb_sum_xfs_clde.cmcode=ls_tsjh_sum_xfs.cmcode); insert into err_msg select ls_tsjh_sum_xfs.LDLP_NO,ls_tsjh_sum_xfs.dpcode,ls_tsjh_sum_xfs.CMCODE, ls_tsjh_sum_xfs.qnt,0,'E','3' from ls_tsjh_sum_xfs where not exists(select * from ls_tssb_sum_xfs_clde where ls_tssb_sum_xfs_clde.ldlp_no=ls_tsjh_sum_xfs.ldlp_no and ls_tssb_sum_xfs_clde.cmcode=ls_tsjh_sum_xfs.cmcode) ; --消费税双从征税 insert into ls_tssb_double select ls_tssb.* from ls_tssb left join allcmcode cmcode on ls_tssb.cmcode = cmcode.code and cmcode.st_date<= ls_tssb.lj_date and cmcode.end_date> ls_tssb.lj_date where cmcode.cjdl <> 0 and cmcode.clde <> 0; insert into ls_tssb_double_sum SELECT LDLP_NO ,CMCODE,dpcode,SUM(qnt) AS qnt,SUM(zzs_ts_amt) AS zzs_ts_amt,SUM(xfs_ts_amt) AS xfs_ts_amt FROM ls_tssb_double GROUP BY LDLP_NO ,CMCODE ,dpcode; insert into ls_tsjh_double1 select ls_tsjh_mindate.* from ls_tsjh_mindate left join cmcode on cmcode.st_date<= ls_tsjh_mindate.lj_date and cmcode.end_date> ls_tsjh_mindate.lj_date where ls_tsjh_mindate.sz='C' and cmcode.cjdl <> 0 and cmcode.clde <> 0 and sl = cmcode.clde; insert into ls_tsjh_double2 select ls_tsjh_mindate.* from ls_tsjh_mindate left join cmcode on cmcode.st_date<= ls_tsjh_mindate.lj_date and cmcode.end_date> ls_tsjh_mindate.lj_date where ls_tsjh_mindate.sz='C' and cmcode.cjdl <> 0 and cmcode.clde <> 0 and sl <> cmcode.clde; insert into ls_tsjh_double11 select ldlp_no ,cmcode,sz,dpcode,SUM(qnt) AS qnt,SUM(amt) AS amt,SUM(Sl) AS sl,SUM(zsl) AS zsl,SUM(se) AS se,SUM(tsl) AS tsl,SUM(ts_amt) AS ts_amt, SUM(ts_amt) AS ts_pri,SUM(ts_amt) AS amt_pri from ls_tsjh_double1 group by ldlp_no ,cmcode,sz,dpcode; insert into ls_tsjh_double21 select ldlp_no ,cmcode,sz,dpcode,SUM(qnt) AS qnt,SUM(amt) AS amt,SUM(Sl) AS sl,SUM(zsl) AS zsl,SUM(se) AS se,SUM(tsl) AS tsl,SUM(ts_amt) AS ts_amt, SUM(ts_amt) AS ts_pri,SUM(ts_amt) AS amt_pri from ls_tsjh_double2 group by ldlp_no ,cmcode,sz,dpcode; update ls_tssb set xfs_ts_amt = (select (case when ls_tssb.qnt>ls_tsjh_double11.qnt then ls_tsjh_double11.qnt else ls_tssb.qnt end)*allcmcode.clde from ls_tsjh_double11, allcmcode where ls_tssb.ldlp_no = ls_tsjh_double11.ldlp_no and ls_tssb.cmcode = ls_tsjh_double11.cmcode and ls_tssb.cmcode = allcmcode.code(+) and allcmcode.st_date<= ls_tssb.lj_date and allcmcode.end_date> ls_tssb.lj_date) where exists (select 1 from ls_tsjh_double11 where ls_tssb.ldlp_no = ls_tsjh_double11.ldlp_no and ls_tssb.cmcode = ls_tsjh_double11.cmcode); update ls_tssb set flag='E',xfs_ts_amt=0 where exists (select 1 from ls_tsjh_double11 where ls_tssb.LDLP_NO = ls_tsjh_double11.LDLP_NO and ls_tssb.CMCODE = ls_tsjh_double11.CMCODE and ls_tsjh_double11.qnt-ls_tssb.qnt<0); insert into err_msg select ls_tsjh_double11.LDLP_NO,ls_tsjh_double11.dpcode,ls_tsjh_double11.CMCODE, ls_tsjh_double11.qnt,ls_tssb_double_sum.qnt,'E','4' from ls_tsjh_double11 inner join ls_tssb_double_sum on ls_tsjh_double11.ldlp_no = ls_tssb_double_sum.ldlp_no and ls_tsjh_double11.cmcode = ls_tssb_double_sum.cmcode where ls_tssb_double_sum.qnt<>ls_tsjh_double11.qnt; insert into err_msg select ls_tssb_double_sum.LDLP_NO,ls_tssb_double_sum.dpcode,ls_tssb_double_sum.CMCODE, 0,ls_tssb_double_sum.qnt,'W','4' from ls_tssb_double_sum where not exists(select * from ls_tsjh_double11 where ls_tsjh_double11.ldlp_no = ls_tssb_double_sum.ldlp_no and ls_tsjh_double11.cmcode = ls_tssb_double_sum.cmcode ); --进货大于出口 insert into err_msg select ls_tsjh_double11.LDLP_NO,ls_tsjh_double11.dpcode,ls_tsjh_double11.CMCODE, ls_tsjh_double11.qnt,0,'E','4' from ls_tsjh_double11 where not exists(select * from ls_tssb_double_sum where ls_tsjh_double11.ldlp_no = ls_tssb_double_sum.ldlp_no and ls_tsjh_double11.cmcode = ls_tssb_double_sum.cmcode); update ls_tssb set xfs_ts_amt = (select xfs_ts_amt+ (case when ls_tssb.qnt>ls_tsjh_double21.qnt then ls_tsjh_double21.qnt else ls_tssb.qnt end)*allcmcode.cjdl*(case when ls_tsjh_double21.qnt<>0 then ls_tsjh_double21.qnt/ls_tsjh_double21.qnt else 0 end) from ls_tsjh_double21, allcmcode where ls_tssb.ldlp_no = ls_tsjh_double21.ldlp_no and ls_tssb.cmcode = ls_tsjh_double21.cmcode and ls_tssb.cmcode = allcmcode.code(+) and allcmcode.st_date<= ls_tssb.lj_date and allcmcode.end_date> ls_tssb.lj_date) where exists (select 1 from ls_tsjh_double21 where ls_tssb.ldlp_no = ls_tsjh_double21.ldlp_no and ls_tssb.cmcode = ls_tsjh_double21.cmcode); update ls_tssb set flag='E',xfs_ts_amt=0 where exists (select 1 from ls_tsjh_double21 where ls_tssb.LDLP_NO = ls_tsjh_double21.LDLP_NO and ls_tssb.CMCODE = ls_tsjh_double21.CMCODE and ls_tsjh_double21.qnt-ls_tssb.qnt<0); insert into err_msg select ls_tsjh_double21.LDLP_NO,ls_tsjh_double21.dpcode,ls_tsjh_double21.CMCODE, ls_tsjh_double21.qnt,ls_tssb_double_sum.qnt,'E','4' from ls_tsjh_double21 inner join ls_tssb_double_sum on ls_tsjh_double21.ldlp_no = ls_tssb_double_sum.ldlp_no and ls_tsjh_double21.cmcode = ls_tssb_double_sum.cmcode where ls_tssb_double_sum.qnt<>ls_tsjh_double21.qnt; select count(*) into rowcount from ls_tsjh_double21 inner join ls_tssb_double_sum on ls_tsjh_double21.ldlp_no = ls_tssb_double_sum.ldlp_no and ls_tsjh_double21.cmcode = ls_tssb_double_sum.cmcode where ls_tssb_double_sum.qnt<>ls_tsjh_double21.qnt; if rowcount=0 THEN insert into ls_tssb_maxid3 select max(id) as id,ldlp_no,cmcode,0 as zzs_ts_amt,0 as xfs_ts_amt,0 as amt1,0 as amt2 from ls_tssb group by LDLP_NO ,CMCODE; insert into ls_tssb_sum4 select LDLP_NO ,CMCODE,SUM(xfs_ts_amt) AS xfs_ts_amt from ls_tssb where not exists(select * from ls_tssb_maxid3 where ls_tssb.id=ls_tssb_maxid3.id) group by LDLP_NO ,CMCODE; update ls_tssb_maxid3 set xfs_ts_amt = (select ls_tssb_sum4.xfs_ts_amt from ls_tssb_sum4 where ls_tssb_maxid3.ldlp_no=ls_tssb_sum4.ldlp_no and ls_tssb_maxid3.cmcode=ls_tssb_sum4.cmcode) where EXISTS (select 1 from ls_tssb_sum4 where ls_tssb_maxid3.ldlp_no=ls_tssb_sum4.ldlp_no and ls_tssb_maxid3.cmcode=ls_tssb_sum4.cmcode); update ls_tssb_maxid3 set amt1 = (select ls_tsjh_double11.ts_amt from ls_tsjh_double11 where ls_tssb_maxid3.ldlp_no=ls_tsjh_double11.ldlp_no and ls_tssb_maxid3.cmcode=ls_tsjh_double11.cmcode) where exists (select 1 from ls_tsjh_double11 where ls_tssb_maxid3.ldlp_no=ls_tsjh_double11.ldlp_no and ls_tssb_maxid3.cmcode=ls_tsjh_double11.cmcode); update ls_tssb_maxid3 set amt2 = (select ls_tsjh_double21.ts_amt from ls_tsjh_double21 where ls_tssb_maxid3.ldlp_no=ls_tsjh_double21.ldlp_no and ls_tssb_maxid3.cmcode=ls_tsjh_double21.cmcode) where exists (select 1 from ls_tsjh_double11 where ls_tssb_maxid3.ldlp_no=ls_tsjh_double11.ldlp_no and ls_tssb_maxid3.cmcode=ls_tsjh_double11.cmcode); update ls_tssb_maxid3 set xfs_ts_amt = (select amt2+amt1- xfs_ts_amt from ls_tssb_maxid3 where amt1<>0); update ls_tssb set xfs_ts_amt = (select ls_tssb_maxid3.xfs_ts_amt from ls_tssb_maxid3 where ls_tssb_maxid3.id=ls_tssb.id) where exists (select 1 from ls_tssb_maxid3 where ls_tssb_maxid3.id=ls_tssb.id) ; end if; insert into err_msg select ls_tssb_double_sum.LDLP_NO,ls_tssb_double_sum.dpcode,ls_tssb_double_sum.CMCODE, 0,ls_tssb_double_sum.qnt,'W','5' from ls_tssb_double_sum where not exists(select * from ls_tsjh_double21 where ls_tsjh_double21.ldlp_no = ls_tssb_double_sum.ldlp_no and ls_tsjh_double21.cmcode = ls_tssb_double_sum.cmcode ); insert into err_msg select ls_tsjh_double21.LDLP_NO,ls_tsjh_double21.dpcode,ls_tsjh_double21.CMCODE, ls_tsjh_double21.qnt,0,'E','5' from ls_tsjh_double21 where not exists(select * from ls_tssb_double_sum where ls_tsjh_double21.ldlp_no = ls_tssb_double_sum.ldlp_no and ls_tsjh_double21.cmcode = ls_tssb_double_sum.cmcode); --检查结束，更新数据 update ls_tssb set flag='E' where exists (select * from err_msg where ls_tssb.ldlp_no=err_msg.ldlp_no) and flag<>'Z'; update ls_tsjh set flag='E' where exists (select * from err_msg where ls_tsjh.ldlp_no=err_msg.ldlp_no) and flag<>'Z'; --删除正式表的数据,同时将临时表的数据插到正式表中去 DELETE FROM wm_tssb WHERE cpcode = cpcode; --将数据转入正式表 INSERT INTO wm_tssb(cpcode,ldlp_no,dpcode,sb_ym,sb_pc,sb_no,hgdjc_no,inv_no,bgd_no,lj_date,usd_amt,hxd_no, cmcode,cmname,cmunit,qnt,ts_qnt,ts_pri,ckjh_amt,tsl,zzs_ts_amt,xfs_ts_amt,dlzm_no,yqshzm_no, dzbq_flag,xxbq_flag,note,sb_rsv,flag,sb_flag,tz_flag,sh_flag,op_user,op_date,ht_no,item_no,bhtba) SELECT cpcode,ldlp_no,dpcode,sb_ym,sb_pc,sb_no,hgdjc_no,inv_no,bgd_no,lj_date,usd_amt,hxd_no, cmcode,cmname,cmunit,qnt,ts_qnt,ts_pri,ckjh_amt,tsl,zzs_ts_amt,xfs_ts_amt,dlzm_no,yqshzm_no, dzbq_flag,xxbq_flag,note,sb_rsv,flag,sb_flag,tz_flag,sh_flag,op_user,op_date,ht_no,item_no,bhtba FROM ls_tssb; DELETE FROM wm_tsjh WHERE cpcode = cpcode; INSERT INTO wm_tsjh(id,cpcode,ldlp_no,sz,dpcode,sb_ym,sb_pc,sb_no, fp_no,fp_dm,zyfp_no,fp_flag,ghfns_no,kpdate, cmcode,cmname,cmunit,qnt,amt,sl,zsl,se,tsl, ts_amt,zysp_no,note,sb_rsv,flag,sb_flag, tz_flag,sh_flag,sh_time,op_user,op_date) SELECT id,cpcode,ldlp_no,sz,dpcode,sb_ym,sb_pc,sb_no, fp_no,fp_dm,zyfp_no,fp_flag,ghfns_no,kpdate, cmcode,cmname,cmunit,qnt,amt,sl,zsl,se,tsl, ts_amt,zysp_no,note,sb_rsv,flag,sb_flag, tz_flag,sh_flag,sh_time,op_user,op_date FROM ls_tsjh; open out_cursor for select * from err_msg; -- routine body goes here, e.g. DBMS_OUTPUT.PUT_LINE('Navicat for Oracle'); END wm_jhze_chk;

调用 存储过程

public ArrayList getJHCKSLResult(String cpcode){ ArrayList list = new ArrayList(); Connection conn = null; CallableStatement st = null; ResultSet rs = null; JHCKCBJCVO vo = null; //调用过程 String strSQL = "{ call wm_jhze_chk (?,?) }"; try { conn = DBConnection.getConnection(); st = conn.prepareCall(strSQL); st.setString(1, cpcode); st.registerOutParameter(2,OracleTypes.CURSOR); rs = st.executeQuery(); rs = (ResultSet)st.getObject(2); while(rs.next()){ vo = new JHCKCBJCVO(); vo.setLdlp_no(rs.getString(1)); vo.setDpcode(rs.getString(2)); vo.setCmcode(rs.getString(3)); vo.setTsjh_qnt(rs.getDouble(4)); vo.setTssb_qnt(rs.getDouble(5)); //flag 分为W,E两个级别 vo.setFlag(rs.getString(6)); //err_flag含义:1.zzs 2.消费税 cjdl 3.消费税 clde 4.消费税 sczs vo.setErr_type(rs.getString(7)); list.add(vo); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ try { st.close(); conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return list; }