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;
	}