用友U8.72 审核成品入库单时生成销售出库单 SQL触发器 －－白波九道

将自己以前跟踪分析后写的语句整理分享　　－－白波九道　2011.12

这个触发器的制作是以前公司想在某一成品入库时就自动生成销售订单然后出库销售，因为是这样的销售模式，每天都有大量的单据做，所以做了这个触发器来自动生成销售订单和销售出库单减少工作量。

这是一个实用的触发器，已经使用了近两年了，完全没有问题。我加了备注方便理解。

RCREATE TRIGGER [IN_DIS] ON [dbo].[RdRecord] 
FOR UPDATE
not for replication 
AS

------------==========================★触发器－审核一个帐套成品入库时生成销售出库单★=======================---------------------------

-------------------------------------------格式化单号-------------------------------------------------------------
DECLARE @cDLCode char(15)  --发货单号
DECLARE @DJHH char(11) 
DECLARE @YM char(6)
DECLARE @cmemo char(50) 
DECLARE @MNO INT --当天最大的单据号(数值) 

  --格式化日期
SET @YM = CONVERT(char(6),GETDATE(),12)

  --单据字头 
SET @DJHH = 'DRWH'+ @YM +'-' 

if not exists (select DLID from dispatchlist where substring(cDlcode,5,6) = @YM)
    set @mno=0
else
    select top 1 @mno=convert(int,right(cDlcode,3)) from dispatchlist where left(cDlcode,4)='DRWH' AND substring(cDlcode,5,6) = @YM  order by cDlcode DESC
--格式化序号
set @cDLCode=@DJHH+right('000'+cast(@mno+1 as varchar),3)
set @cmemo='由成品入库单'+(select cCode from  inserted)+'生成'
-----------------------------------------------------------------------------------------------------------------------

if Update(cHandler) and (select cHandler from inserted )<>'' and (select cWhCode from  inserted)='WXYLC' and (select cBusType from inserted)='成品入库'  
and (select cRdCode from inserted)='SCP' and (select cDepcode from inserted)='H202' 
and (select cdefine9 from inserted) is null and not exists(select DLid from DispatchList wherecDLCode=@cDLCode)

BEGIN
 
 DECLARE @DLID int      --发货单主表标识
 DECLARE @AutoID int --发货单子表标识 
 DECLARE @iDlsID int    --发货单子表辅助标识 
 DECLARE @maxid int 
 DECLARE @sysid int
 DECLARE @i int
 DECLARE @count int
 DECLARE @DDate varchar(10)

 set @ddate=(SELECT CONVERT(VARCHAR(10),GETDATE(),120))

 
 set @maxid=(select max(dlid)+1 from dispatchlist)
 set @sysid=(select iFatherId+1 from UFSystem..UA_Identity where cAcc_id='009' and cVouchtype='DISPATCH' )
  if @maxid>=@sysid set @DLID=@maxid   else set @DLID=@sysid
 
 set @maxid=(select max(iDlsID)+1 from dispatchlists)
 set @sysid=(select iChildId+1 from UFSystem..UA_Identity where cAcc_id='009' and cVouchtype='DISPATCH')
 if @maxid>=@sysid set @iDlsID=@maxid   else set @iDlsID=@sysid
 
 
 

 -------------------------------------------插入新记录到销售出库单主表-------------------------------------------------------------
 Insert Into dispatchlist
 (cbustype,ivtid,cDLCode,cvouchtype,cstcode,ddate,cdepcode,cpersoncode,ccuscode,cexch_name,iexchrate,itaxrate,cmemo,breturnflag,
 dlid,cmaker,bfirst,sbvid,isale,ccusname,bcredit,iverifystate) 
 Values 
 ('普通销售',71,@cDLCode,'05','H0',@ddate,'H0','H005','J0008','人民币',1,17,@cmemo,0,
 @DLID,'何伶伶',0,0,0,'绵阳得润',0,0)

 update rdrecord set cdefine9='1' where id=(select id from inserted)
 update UFSystem..UA_Identity set iFatherId=@DLID where cAcc_id='009' and cVouchtype='DISPATCH'

 
 -------------------------------------------插入新记录到销售出库单子表-------------------------------------------------------------

 set @autoid=(select max(autoid) from rdrecords where id=(select id from inserted))

 Select identity(int,1,1) tmpid,null iDlsID,@DLID dlid,'WXYLC' cwhcode,a.cInvCode,iQuantity,isnull(iinvscost,0) iquotedprice,isnull(iinvscost,0) iunitprice,isnull(iinvscost,0)*1.17 itaxunitprice,isnull(iinvscost,0)*iQuantity imoney,
 isnull(iinvscost,0)*iQuantity*0.17 itax,isnull(iinvscost,0)*iQuantity*1.17  isum,0 idiscount,isnull(iinvscost,0) inatunitprice,isnull(iinvscost,0)*iQuantity inatmoney,
 isnull(iinvscost,0)*iQuantity*0.17 inattax,isnull(iinvscost,0)*iQuantity*1.17 inatsum,0 inatdiscount,0 bsettleall,0 itb,100 kl,100 kl2,cinvname,17 itaxrate
 into #tmpdisp 
 from Rdrecords a left join SA_CusUPriceView  b  on b.ccuscode='j0008' and a.cinvcode=b.cinvcode where id=(select id from inserted)

 set @i=1
 set @count=(select count(*) from #tmpdisp)

 while @i<=@count
 begin
     update #tmpdisp set iDlsID=@iDlsID where tmpid=@i
     set @i=@i+1
     set @iDlsID=@iDlsID+1
 end
  
 Insert Into dispatchlists
 (dlid,iDlsID,cwhcode,cInvCode,iquantity,iquotedprice,iunitprice,itaxunitprice,imoney,
 itax,isum,idiscount,inatunitprice,inatmoney,
 inattax,inatsum,inatdiscount,bsettleall,itb,kl,kl2,cinvname,itaxrate,
 fsalecost,fsaleprice,bIsSTQc,bGsp,cMassUnit,bQANeedCheck,bQAUrgency,bQAChecking,bQAChecked,iQAQuantity,iQANum,bcosting,fcusminprice) 
 select dlid,iDlsID,cwhcode,cInvCode,iquantity,iquotedprice,iunitprice,itaxunitprice,imoney,
 itax,isum,idiscount,inatunitprice,inatmoney,
 inattax,inatsum,inatdiscount,bsettleall,itb,kl,kl2,cinvname,itaxrate,
 0,0,0,0,0,0,0,0,0,0,0,1,0 from #tmpdisp
 
 update UFSystem..UA_Identity set iChildId=@iDlsID-1 where cAcc_id='009' and cVouchtype='DISPATCH'

END