oracle 存储过程 调用存储过程

631jz5350x

　　CREATE OR REPLACE PROCEDURE wm_jhze_chk(cpcode in char,out_cursor out sys_refcursor) as rowcount int;BEGINdelete 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_priFROM 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_amtFROM ls_tssbGROUP 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_prifrom 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 1from   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=0where 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_msgselect 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.cmcodewhere 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 THENinsert 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_tssbwhere 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 wherels_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_msgselect 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 wherels_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_msgselect 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_mindateselect 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_noand ls_tsjh.cmcode = ls_tssb_mindate.cmcode;--消费税进货汇总insert into ls_tsjh_sum_xfsselect 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_datewhere 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_cjdlselect 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_datewhere 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_cldeselect 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_datewhere 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_xfsselect LDLP_NO ,CMCODE,dpcode,SUM(qnt) AS qnt,SUM(zzs_ts_amt) AS zzs_ts_amt,SUM(xfs_ts_amt) AS xfs_ts_amtfrom 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_datewhere (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_cjdlselect LDLP_NO ,CMCODE,dpcode,SUM(qnt) AS qnt,SUM(zzs_ts_amt) AS zzs_ts_amt,SUM(xfs_ts_amt) AS xfs_ts_amtfrom 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_datewhere cmcode.cjdl <> 0  and cmcode.clde = 0GROUP BY LDLP_NO ,CMCODE ,dpcode;insert into ls_tssb_sum_xfs_cldeselect LDLP_NO ,CMCODE,dpcode,SUM(qnt) AS qnt,SUM(zzs_ts_amt) AS zzs_ts_amt,SUM(xfs_ts_amt) AS xfs_ts_amtfrom 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_datewhere cmcode.cjdl = 0  and cmcode.clde <> 0GROUP 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.cmcodeleft join allcmcode cmcode on ls_tssb.cmcode = cmcode.code and  cmcode.st_date<= ls_tssb.lj_date and cmcode.end_date> ls_tssb.lj_datewhere 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.cldefrom 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.cmcodeleft join allcmcode cmcode on ls_tssb.cmcode = cmcode.code and  cmcode.st_date<= ls_tssb.lj_date and cmcode.end_date> ls_tssb.lj_datewhere cmcode.clde<>0 and cmcode.cjdl=0);update ls_tssb set flag='E',xfs_ts_amt=0where 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_msgselect 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.cmcodewhere ls_tssb_sum_xfs_clde.qnt<>ls_tsjh_sum_xfs_clde.qnt ;--从量定额出口数量不等insert into err_msgselect 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.cmcodewhere ls_tssb_sum_xfs_cjdl.qnt<>ls_tsjh_sum_xfs_cjdl.qnt;--建立没有最后一条记录的汇总数据表select count(*) into  rowcountfrom 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.cmcodewhere 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_sum2select 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_tssbwhere 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 rowcountfrom 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.cmcodewhere 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_tssbwhere 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_msgselect 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_msgselect 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_msgselect 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_msgselect 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_doubleselect 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_datewhere cmcode.cjdl <> 0  and cmcode.clde <> 0;insert into ls_tssb_double_sumSELECT LDLP_NO ,CMCODE,dpcode,SUM(qnt) AS qnt,SUM(zzs_ts_amt) AS zzs_ts_amt,SUM(xfs_ts_amt) AS xfs_ts_amtFROM ls_tssb_doubleGROUP BY LDLP_NO ,CMCODE ,dpcode;insert into ls_tsjh_double1select ls_tsjh_mindate.*  from ls_tsjh_mindate left join cmcode oncmcode.st_date<= ls_tsjh_mindate.lj_date and cmcode.end_date> ls_tsjh_mindate.lj_datewhere ls_tsjh_mindate.sz='C' and cmcode.cjdl <> 0  and cmcode.clde <> 0 and sl = cmcode.clde;insert into ls_tsjh_double2select ls_tsjh_mindate.*  from ls_tsjh_mindate left join cmcode oncmcode.st_date<= ls_tsjh_mindate.lj_date and cmcode.end_date> ls_tsjh_mindate.lj_datewhere ls_tsjh_mindate.sz='C' and cmcode.cjdl <> 0  and cmcode.clde <> 0 and sl <> cmcode.clde;insert into ls_tsjh_double11select 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_prifrom ls_tsjh_double1 group by ldlp_no ,cmcode,sz,dpcode;insert into ls_tsjh_double21select 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_prifrom 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.cldefrom  ls_tsjh_double11,allcmcode  where ls_tssb.ldlp_no = ls_tsjh_double11.ldlp_no and ls_tssb.cmcode = ls_tsjh_double11.cmcodeand  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=0where exists (select 1 from ls_tsjh_double11 where  ls_tssb.LDLP_NO = ls_tsjh_double11.LDLP_NO and ls_tssb.CMCODE = ls_tsjh_double11.CMCODEand  ls_tsjh_double11.qnt-ls_tssb.qnt<0);insert into err_msgselect 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.cmcodewhere ls_tssb_double_sum.qnt<>ls_tsjh_double11.qnt;insert into err_msgselect 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 wherels_tsjh_double11.ldlp_no = ls_tssb_double_sum.ldlp_no and  ls_tsjh_double11.cmcode = ls_tssb_double_sum.cmcode );--进货大于出口insert into err_msgselect 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.cmcodeand   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=0where exists (select 1 from ls_tsjh_double21 where  ls_tssb.LDLP_NO = ls_tsjh_double21.LDLP_NO and ls_tssb.CMCODE = ls_tsjh_double21.CMCODEand ls_tsjh_double21.qnt-ls_tssb.qnt<0);insert into err_msgselect 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.cmcodewhere ls_tssb_double_sum.qnt<>ls_tsjh_double21.qnt;select count(*) into rowcountfrom 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.cmcodewhere ls_tssb_double_sum.qnt<>ls_tsjh_double21.qnt;if rowcount=0 THEN  insert into ls_tssb_maxid3select 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_tssbwhere 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_msgselect 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 wherels_tsjh_double21.ldlp_no = ls_tssb_double_sum.ldlp_no and  ls_tsjh_double21.cmcode = ls_tssb_double_sum.cmcode );insert into err_msgselect 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,bhtbaFROM 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_dateFROM 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.消费税 sczsvo.setErr_type(rs.getString(7));list.add(vo);}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally{try {st.close();conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}return list;}