mssql server 2000 触发器研究

/****** Object:  Trigger dbo.tr_kcdjsh_compute_jc    Script Date: 2005-1-24 14:22:23 ******/

/****** Object:  Trigger dbo.tr_kcdjsh_compute_jc    Script Date: 2003-8-12 18:24:57 ******/
-----作用：--库存单据审核后自动计算货品库存（区分审核、取消审核）
-----By yang.2004/05/14
CREATE   TRIGGER [tr_kcdjsh_compute_jc] ON dbo.kc_dj
FOR UPDATE
AS
declare @s_shbz_delete varchar(10)--审核标志
declare @s_shbz_insert varchar(10)
declare @l_id int,@mxid int --审核的单据流水号
declare @djlx int,@dwid int,@y_year int , @year_order int,@i_count int
declare @to_ckid varchar(100),@from_ckid varchar(100),@s_funname varchar(100),@clid varchar(100)
declare @sl1  decimal(20,6) , @sl2  decimal(20,6)  ,@dj1  decimal(20,6)  ,@dj2  decimal(20,6)  ,@hsdj1 decimal(20,6)  ,@hsdj2 decimal(20,6) ,@je1 decimal(20,6) ,@hsje1 decimal(20,6)
declare @de_kcdj decimal(20,6),@de_kchsdj decimal(20,6)-----------------库存的成本无税单价和含税单价
declare @de_jcsl1 decimal(20,6),@de_jcsl2 decimal(20,6)
 if update(shbz) --
 begin
 select @s_shbz_delete=isnull(shbz,'不成功') from deleted
 select @s_shbz_insert=isnull(shbz,'不成功'),@l_id=id_p,@djlx=djlx,@to_ckid=to_ckid,@from_ckid=from_ckid,@dwid=dwid,
            @y_year=y_year,@year_order=year_order
 from inserted
 select @s_funname=lxbz from x_djlx where djid=@djlx--获取单据类型
 if (@s_shbz_delete='否' and @s_shbz_insert='是') or  (@s_shbz_delete='是' and @s_shbz_insert='否')  --表示审核一张单据----------------------------------------------------------
  begin
   declare cu_djmx cursor for
   select clid,isnull(sjsl1,0),isnull(sjsl2,0),isnull(dj1,0),isnull(dj2,0),isnull(hsdj1,0),isnull(hsdj2,0),isnull(je1,0),isnull(hsje1,0),mxid from kc_djmx where id_p=@l_id
   open cu_djmx
   fetch cu_djmx into @clid,@sl1,@sl2,@dj1,@dj2,@hsdj1,@hsdj2,@je1,@hsje1,@mxid
    if @s_funname='仓库入库单'-------------------------------------------------------------------------------------
    begin
     while(@@fetch_status=0)
     begin
     if @to_ckid='mj'
      select @i_count=count(*) from kc_jc where  clid=@clid and ckid=@to_ckid and dwid=@dwid
     else
      select @i_count=count(*) from kc_jc where  y_year=@y_year and year_order=@year_order and clid=@clid and ckid=@to_ckid and dwid=@dwid
     if @i_count=0 --表示库存收发存中没有这种货品，然后插入
      begin
      insert kc_jc (dwid,y_year,year_order,ckid,clid,rksl1,rksl2,rkje,rkhsje,jcsl1,jcsl2,jcdj,jchsdj,jcje,jchsje)
      values(@dwid,@y_year,@year_order,@to_ckid,@clid,@sl1,@sl2,@je1,@hsje1,@sl1,@sl2,@dj1,@hsdj1,@je1,@hsje1)
      end
     else                 --表示库存收发存中有这种货品，直接更新
      begin
             -- add code function by debug:模具仓库没有月结转的概念
      if @to_ckid<>'mj'  --非模具仓库:有月结转的概念
       begin
        update kc_jc set rksl1=(select isnull(sum(kc_djmx.sjsl1+kc_djmx.bpsl1),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid and kc_dj.shbz='是' ),
         jcsl1=isnull(qcsl1,0) - isnull(cksl1,0)+(select isnull(sum(kc_djmx.sjsl1+kc_djmx.bpsl1),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid and kc_dj.shbz='是' ),
         rksl2=(select isnull(sum(isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         jcsl2=qcsl2-cksl2+(select isnull(sum(isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid and kc_dj.shbz='是' ),
         rkje=(select isnull(sum(isnull(kc_djmx.je1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         rkhsje=(select isnull(sum(isnull(kc_djmx.hsje1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         jcje=isnull(qcje,0)+isnull(ckje,0)+(select isnull(sum(isnull(kc_djmx.je1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         jchsje=isnull(qchsje,0) - isnull(ckhsje,0)+(select isnull(sum(isnull(kc_djmx.hsje1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' )
         --jcdj=isnull(jcje/jcsl1,jcdj),
         --jchsdj=isnull(jchsje/jcsl1,jchsdj)
         where  y_year=@y_year and year_order=@year_order and clid=@clid and ckid=@to_ckid and dwid=@dwid
       end
      else   --模具仓库:没有月结底的
       begin
        update kc_jc set rksl1=(select isnull(sum(kc_djmx.sjsl1+kc_djmx.bpsl1),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p  and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid and kc_dj.shbz='是' ),
         jcsl1=isnull(qcsl1,0) - isnull(cksl1,0)+(select isnull(sum(kc_djmx.sjsl1+kc_djmx.bpsl1),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and  kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid and kc_dj.shbz='是' ),
         rksl2=(select isnull(sum(isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p  and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         jcsl2=qcsl2-cksl2+(select isnull(sum(isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p  and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid and kc_dj.shbz='是' ),
         rkje=(select isnull(sum(isnull(kc_djmx.je1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and  kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         rkhsje=(select isnull(sum(isnull(kc_djmx.hsje1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p  and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         jcje=isnull(qcje,0)+isnull(ckje,0)+(select isnull(sum(isnull(kc_djmx.je1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and  kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         jchsje=isnull(qchsje,0) - isnull(ckhsje,0)+(select isnull(sum(isnull(kc_djmx.hsje1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p  and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' )
         --jcdj=isnull(jcje/jcsl1,jcdj),
         --jchsdj=isnull(jchsje/jcsl1,jchsdj)
         where   clid=@clid and ckid=@to_ckid and dwid=@dwid
       

       end
      fetch cu_djmx into @clid,@sl1,@sl2,@dj1,@dj2,@hsdj1,@hsdj2,@je1,@hsje1,@mxid
      end
     end
    end
    if @s_funname='仓库出库单'-------------------------------------------------------------------------------------
    begin
     while(@@fetch_status=0)
     begin
     select @de_jcsl1=isnull(jcsl1,0), @de_jcsl2=isnull(jcsl2,0)-----取库存数量
      from kc_jc where   y_year=@y_year and year_order=@year_order and clid=@clid and ckid=@from_ckid and dwid=@dwid
     if @de_jcsl2<>0
      begin
       select @de_kcdj=isnull(isnull(jcje,0)/jcsl2,0), @de_kchsdj=isnull(isnull(jchsje,0)/jcsl2,0)-----取库存单价
        from kc_jc where   y_year=@y_year and year_order=@year_order and clid=@clid and ckid=@from_ckid and dwid=@dwid
      end
     else
      begin
       select @de_kcdj=0,@de_kchsdj=0 
      end
     if @from_ckid='mj'
       select @i_count=count(*) from kc_jc where  clid=@clid and ckid=@from_ckid and dwid=@dwid 
     else
      select @i_count=count(*) from kc_jc where  y_year=@y_year and year_order=@year_order and clid=@clid and ckid=@from_ckid and dwid=@dwid
     select @je1=@sl2*@de_kcdj
     select @hsje1=@sl2*@de_kchsdj
     --------------------------------更新回出库单
     update kc_djmx set cbdj1=@de_kcdj,cbhsdj1=@de_kchsdj,cbdj2=@de_kcdj,cbhsdj2=@de_kchsdj where mxid=@mxid
     ---------------------------------------------------------------------------------------------------------------------
     if @i_count=0 --表示库存收发存中没有这种货品，然后插入
      begin
      insert kc_jc (dwid,y_year,year_order,ckid,clid,cksl1,cksl2,ckje,ckhsje,jcsl1,jcsl2,jcdj,jchsdj,jcje,jchsje)
      values(@dwid,@y_year,@year_order,@from_ckid,@clid,@sl1,@sl2,@je1,@hsje1,-@sl1,-@sl2,@de_kcdj,@de_kchsdj,-@je1,-@hsje1)
      end
     else                 --表示库存收发存中有这种货品，直接更新
      begin
      if @from_ckid<>'mj' --非模具仓库，有月结的
       begin
        update kc_jc set cksl1=(select isnull(sum(isnull(kc_djmx.sjsl1,0)+isnull(kc_djmx.bpsl1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid   and kc_dj.shbz='是'),
         jcsl1=isnull(qcsl1,0)+isnull(rksl1,0) - (select isnull(sum(isnull(kc_djmx.sjsl1,0)+isnull(kc_djmx.bpsl1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid   and kc_dj.shbz='是'),
         cksl2=(select isnull(sum(isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid   and kc_dj.shbz='是'),
         jcsl2=isnull(qcsl2,0)+isnull(rksl2,0) - (select isnull(sum(isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid   and kc_dj.shbz='是'),
         ckje=(select isnull(sum((isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0))*isnull(kc_djmx.hsdj2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         ckhsje=(select isnull(sum((isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0))*isnull(kc_djmx.hsdj2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         jcje=isnull(qcje,0)+isnull(rkje,0) - (select isnull(sum((isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0))*isnull(kc_djmx.hsdj2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         jchsje=isnull(qchsje,0)+isnull(rkhsje,0) - (select isnull(sum((isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0))*isnull(kc_djmx.hsdj2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' )
        
        where  y_year=@y_year and year_order=@year_order and clid=@clid and ckid=@from_ckid and dwid=@dwid
       end
      else  ---模具仓库，无月结的
              begin
        update kc_jc set cksl1=(select isnull(sum(isnull(kc_djmx.sjsl1,0)+isnull(kc_djmx.bpsl1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p  and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid   and kc_dj.shbz='是'),
         jcsl1=isnull(qcsl1,0)+isnull(rksl1,0) - (select isnull(sum(isnull(kc_djmx.sjsl1,0)+isnull(kc_djmx.bpsl1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p  and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid   and kc_dj.shbz='是'),
         cksl2=(select isnull(sum(isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and  kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid   and kc_dj.shbz='是'),
         jcsl2=isnull(qcsl2,0)+isnull(rksl2,0) - (select isnull(sum(isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p  and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid   and kc_dj.shbz='是'),
         ckje=(select isnull(sum((isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0))*isnull(kc_djmx.hsdj2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         ckhsje=(select isnull(sum((isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0))*isnull(kc_djmx.hsdj2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p  and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         jcje=isnull(qcje,0)+isnull(rkje,0) - (select isnull(sum((isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0))*isnull(kc_djmx.hsdj2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p  and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
         jchsje=isnull(qchsje,0)+isnull(rkhsje,0) - (select isnull(sum((isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0))*isnull(kc_djmx.hsdj2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' )
        
        where    clid=@clid and ckid=@from_ckid and dwid=@dwid

       end
      fetch cu_djmx into @clid,@sl1,@sl2,@dj1,@dj2,@hsdj1,@hsdj2,@je1,@hsje1,@mxid
      end
     end
    end
    if @s_funname='仓库调拨单'-------------------------------------------------------------------------------------
    begin
     while(@@fetch_status=0)
     begin
     --------------------------先处理调出仓库
     select @i_count=count(*) from kc_jc where  y_year=@y_year and year_order=@year_order and clid=@clid and ckid=@from_ckid and dwid=@dwid
     select @de_jcsl1=isnull(jcsl1,0), @de_jcsl2=isnull(jcsl2,0)-----取库存数量
      from kc_jc where   y_year=@y_year and year_order=@year_order and clid=@clid and ckid=@from_ckid and dwid=@dwid
     if @de_jcsl2<>0
      begin
       select @de_kcdj=isnull(jcje/jcsl2,0), @de_kchsdj=isnull(jchsje/jcsl2,0)-----取库存单价
        from kc_jc where   y_year=@y_year and year_order=@year_order and clid=@clid and ckid=@from_ckid and dwid=@dwid
      end
     else
      begin
       select @de_kcdj=0,@de_kchsdj=0 
      end
     select @je1=@sl2*@de_kcdj
     select @hsje1=@sl2*@de_kchsdj
     
     --------------------------------成本单价更新回出库单，并填回入库单价和金额
     update kc_djmx set cbdj1=@de_kcdj,cbhsdj1=@de_kchsdj,cbdj2=@de_kcdj,cbhsdj2=@de_kchsdj where mxid=@mxid
     -----------------------------
     if @i_count=0 --表示库存收发存中没有这种货品，然后插入
      begin
      insert kc_jc (dwid,y_year,year_order,ckid,clid,cksl1,cksl2,ckje,ckhsje,jcsl1,jcsl2,jcdj,jchsdj,jcje,jchsje)
      values(@dwid,@y_year,@year_order,@from_ckid,@clid,@sl1,@sl2,@je1,@hsje1,-@sl1,-@sl2,@de_kcdj,@de_kchsdj,-@je1,-@hsje1)
      end
     else       
                                                                                begin
      update kc_jc set  cksl1=(select isnull(sum(isnull(kc_djmx.sjsl1,0)+isnull(kc_djmx.bpsl1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid   and kc_dj.shbz='是'),
        jcsl1=isnull(qcsl1,0)+isnull(rksl1,0) - (select isnull(sum(isnull(kc_djmx.sjsl1,0)+isnull(kc_djmx.bpsl1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid   and kc_dj.shbz='是'),
        cksl2=(select isnull(sum(isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid   and kc_dj.shbz='是'),
        jcsl2=isnull(qcsl2,0)+isnull(rksl2,0) - (select isnull(sum(isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid   and kc_dj.shbz='是'),
        ckje=(select isnull(sum((isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0))*isnull(kc_djmx.cbdj1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
        ckhsje=(select isnull(sum((isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0))*isnull(kc_djmx.cbhsdj1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
        jcje=isnull(qcje,0)+isnull(rkje,0) - (select isnull(sum((isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0))*isnull(kc_djmx.cbdj2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
        jchsje=isnull(qchsje,0)+isnull(rkhsje,0) - (select isnull(sum((isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0))*isnull(kc_djmx.cbhsdj2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.from_ckid=@from_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' )
        where  y_year=@y_year and year_order=@year_order and clid=@clid and ckid=@from_ckid and dwid=@dwid
      ---fetch cu_djmx into @clid,@sl1,@sl2,@dj1,@dj2,@hsdj1,@hsdj2,@je1,@hsje1,@mxid
      end
     ---------------------------再处理调入仓库
     select @i_count=count(*) from kc_jc where  y_year=@y_year and year_order=@year_order and clid=@clid and ckid=@to_ckid and dwid=@dwid
     if @i_count=0 --表示库存收发存中没有这种货品，然后插入
      begin
      insert kc_jc (dwid,y_year,year_order,ckid,clid,rksl1,rksl2,rkje,rkhsje,jcsl1,jcsl2,jcdj,jchsdj,jcje,jchsje)
      values(@dwid,@y_year,@year_order,@to_ckid,@clid,@sl1,@sl2,@je1,@hsje1,@sl1,@sl2,@dj1,@hsdj1,@je1,@hsje1)
      end
     else                 --表示库存收发存中有这种货品，直接更新
      begin
      update kc_jc set  rksl1=(select isnull(sum(isnull(kc_djmx.sjsl1,0)+isnull(kc_djmx.bpsl1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid and kc_dj.shbz='是' ),
        jcsl1=isnull(qcsl1,0) - isnull(cksl1,0)+(select isnull(sum(isnull(kc_djmx.sjsl1,0)+isnull(kc_djmx.bpsl1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid and kc_dj.shbz='是' ),
        rksl2=(select isnull(sum(isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
        jcsl2=qcsl2-cksl2+(select isnull(sum(isnull(kc_djmx.sjsl2,0)+isnull(kc_djmx.bpsl2,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid and kc_dj.shbz='是' ),
        rkje=(select isnull(sum(kc_djmx.je1),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
        rkhsje=(select isnull(sum(kc_djmx.hsje1),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
        jcje=isnull(qcje,0)+isnull(ckje,0)+(select isnull(sum(isnull(kc_djmx.je1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' ),
        jchsje=isnull(qchsje,0) - isnull(ckhsje,0)+(select isnull(sum(isnull(kc_djmx.hsje1,0)),0) from kc_djmx,kc_dj where kc_dj.id_p=kc_djmx.id_p and kc_dj.y_year=@y_year and kc_dj.year_order=@year_order and kc_djmx.clid=@clid and kc_dj.to_ckid=@to_ckid and kc_dj.dwid=@dwid  and kc_dj.shbz='是' )
        ---jcdj=isnull(jcje/jcsl1,jcdj),
        --jchsdj=isnull(jchsje/jcsl1,0)
      where  y_year=@y_year and year_order=@year_order and clid=@clid and ckid=@to_ckid and dwid=@dwid
      fetch cu_djmx into @clid,@sl1,@sl2,@dj1,@dj2,@hsdj1,@hsdj2,@je1,@hsje1,@mxid
      end
     -------------------------处理完毕
     end
    end
   close cu_djmx
   deallocate cu_djmx
    
  end
  else----------------------------------------------------------表示取消审核一张单据，实际上不需要执行这么一段程序，为了结构的需要：By Fengql.2003/05/14
  begin
   select @mxid=1
  end
 end