mssql server 2000 触发器研究
来源:互联网 发布:vanelife软件 编辑:程序博客网 时间:2024/05/21 15:41
/****** 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
- mssql server 2000 触发器研究
- 在MSSQL Server中创建触发器
- MSSql Server事务和锁研究
- mssql 触发器
- mssql 触发器
- MSSQL触发器
- MSSQL 触发器
- MSSQL 触发器
- MSSQL:触发器
- 怎样制定自己的SQL Server 2000触发器-Mssql数据库教程
- MSSQL Server 2000
- [MSSQL]查看SQL SERVER 加密存储过程,函数,触发器,视图
- 关于mssql 2000 的 行级触发器
- 触发器-MSSQL常用操作
- 触发器-MSSQL常用操作
- mssql触发器更新
- [MSSQL]禁用触发器
- MSSQL 触发器常用操作
- Linux 入门常用命令 — 文件的复制、删除和移动
- .net精简框架集下的ini文件读取(C#)
- java乱码问题及解决方法的相关链接!
- 实现带有用户身份验证的Web Service
- windows XP home版安装IIS
- mssql server 2000 触发器研究
- ASP、JSP、PHP三种技术比较
- 简单的验证码程序
- 又开始上班了
- Tomcat5调用Weblogic8.1布署的EJB
- 在VB和DLL间传递字符串
- 如何让局域网内的一台机器作web服务器
- 整合Spring与Struts的几种方法
- Frame 内置网页的简单方法