create trigger tecRS_FromPWtoAS
on RdRecord
for update
as
if update(cHandler)
declare @ccDefine3 char(20)
declare @dnverifytime datetime
declare @iID int
declare @ccDefine5 int
declare @cVouchType char(3)
declare @cPersonCode char(10)
declare @cMaker char(10)
select @dnverifytime=dnverifytime,@ccDefine3=cCode,@iID=ID,@ccDefine5=cDefine5,@cVouchType=cVouchType,@cPersonCode=cPersonCode,@cMaker=cMaker from inserted
if @cVouchType = '01'
begin
if @dnverifytime is not NULL
begin
if @ccDefine5 = 1
begin
declare @ccAVCode char(20)
declare @ccAccPCode char(20)
declare @ccInvCode char(20)
declare @ccInvCodeFa char(20)
declare @iQuantity int
declare @ccBatch char(10)
declare @ccDefine34 int
declare @iIDASV int
declare @iAutoID1 int
declare @iAutoID2 int
declare @ccAVbatch char(15)
declare @iAVQuantity int
declare @unitquantity int
/*定义表体记录的游标*/
declare Rs_RdRecords_cur cursor
global static
for select cInvCode,cBatch,cDefine34 from RdRecords where ID = @iID
for read only
declare @iRows int
open Rs_RdRecords_cur
select @iRows=@@CURSOR_ROWS
/*遍历所有相关记录，并根据每条记录建对应单独的表头和表体*/
while @iRows > 0
begin
set @iRows=@iRows-1
fetch next from Rs_RdRecords_cur into @ccInvCode,@ccBatch,@ccDefine34
if @ccDefine34 = 0
continue
/*取存货档案中对应母件的编码*/
select @ccInvCodeFa=cInvCode from inventory
where cInvAddCode = (select cInvDefine5 from inventory where cInvCode = @ccInvCode)
/*计算最大流水号*/
select @iIDASV = (max(ID)+1) from AssemVouch
select @ccAVCode = (max(cAVCode)+1) from AssemVouch
/*插入表头*/
insert into AssemVouch
(
cAVCode,cVouchType,cAccPCode,dAVDate,cDepCode,cPersonCode,
cIRdCode,cORdCode,cMaker,iNetLock,ID,bTransFlag,VT_ID,
dnMaketime,cDefine3
)
values
(
@ccAVCode,'13',@ccInvCodeFa,getdate(),'102',@cPersonCode,
'17','27',@cMaker,'0',@iIDASV,'false','31',
getdate(),@ccDefine3
)
/*计算流水号和批号*/
select @iAutoID1 = (max(autoID)+1) from AssemVouchs
set @iAutoID2 = @iAutoID1+1
set @ccAVbatch = ('99'+@ccBatch)
/*插入表体第一行*/
insert into AssemVouchs
(
ID,cAVCode,bAVType,cWhCode,cInvCode,iAVQuantity,cAVbatch,
autoID,bfvqty,compscrp,baseqtyn,baseqtyd,bCosting,
iExpiratDateCalcu,unitquantity,irowno,cDefine24
)
values
(
@iIDASV,@ccAVCode,'套件','00001',@ccInvCodeFa,@ccDefine34,@ccAVbatch,
@iAutoID1,'false',NULL,NULL,NULL,'true',
'0',1,1,@ccDefine3
)
/*计算对应散件的数量*/
select @unitquantity = cInvDefine12 from inventory where cInvCode = @ccInvCode
set @iAVQuantity=@unitquantity*@ccDefine34
/*插入表体第二行*/
insert into AssemVouchs
(
ID,cAVCode,bAVType,cWhCode,cInvCode,iAVQuantity,cAVbatch,
autoID,bfvqty,compscrp,baseqtyn,baseqtyd,bCosting,
iExpiratDateCalcu,unitquantity,irowno,cDefine24
)
values
(
@iIDASV,@ccAVCode,'散件','00002',@ccInvCode,@iAVQuantity,@ccBatch,
@iAutoID2,'true',0,@unitquantity,1,'true',
'0',@unitquantity,2,@ccDefine3
)
end
close Rs_RdRecords_cur
deallocate Rs_RdRecords_cur
end
end
if @dnverifytime is NULL and @ccDefine5 = 1
begin
if exists (select cDefine3 from AssemVouch where cDefine3 = @ccDefine3)
begin
delete from AssemVouchs where cDefine24 = @ccDefine3 and (select dnverifytime from AssemVouch where cDefine3 = @ccDefine3) is NULL
delete from AssemVouch where cDefine3 = @ccDefine3 and dnverifytime is NULL
end
end
end
评论