问题现象: | 销售退货可以一直参照发货单,没有遵循(退货数量=原发货单数量-已开票数量-未开票退货 数量,可修改,但不可超过原发货单数量-已开票数量-未开票退货数量;默认退货数量≤0时,不可参照。),数据重新提交。可以无限退货例子:退货单参照发货单0098200014. (详情见附件截图,此问题之前提交过,问题号201111040054,未能解决。数据已上传至FTP/u8shanghai/聚通20111115.rar ) |
问题原因: | |
解决方案: | 您好, 此问题【经查看数据/测试】现做如下分析与答复: 【问题原因】 diapatchlists 触发器有问题 alter trigger 就可以了; 【解决方案】 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER TRIGGER [dbo].[SA_DispsINSTrig] ON [dbo].[DispatchLists] FOR INSERT NOT FOR REPLICATION AS Declare @cValue Varchar(100) --accinformation取值 Declare @cDefault Varchar(100) --accinformation取值 --Declare @bOverStock Bit --是否有超现存量控制 Declare @iSale Tinyint --是否是由发票生成的发货单,1:参照发票,0:先发货; --Declare @iCheck Tinyint --检查现存量返回值 Declare @iSOsID Int Declare @iInsQuantity Float Declare @iInsNum Float Declare @iInsSum Float Declare @cSTCode Varchar(2) Declare @dDate DateTime Declare @bFirst bit --是否期初 declare @CusCode as nvarchar(100) declare @DepCode as nvarchar(100) declare @PersonCode as nvarchar(100) declare @vouchtype as nvarchar(10) declare @bcashsale as bit declare @cverifier nvarchar(20) SET NOCOUNT ON DECLARE CurInv CURSOR FOR SELECT top 1 cSTCode,dDate,iSale,bFirst,ccuscode,cdepcode,isnull(cpersoncode,N'') as cpersoncode,cvouchtype,isnull(bcashsale,0) as bcashsale,isnull(cverifier,N'') as cverifier FROM DispatchList INNER JOIN Inserted ON DispatchList.DLID=Inserted.DLID OPEN CurInv FETCH NEXT FROM CurInv INTO @cSTCode,@dDate,@iSale,@bFirst,@CusCode,@DepCode,@PersonCode,@vouchtype,@bcashsale,@cverifier CLOSE CurInv DEALLOCATE CurInv If IsNull(@cSTCode,'')='' begin SET NOCOUNT OFF Return --销售类型为空表示是应收系统录入的发票,不能执行触发器中的语句 end SET @cValue=(SELECT cValue FROM AccInformation WHERE csysid = 'SA' AND cName = 'dSaleStartDate') If IsNull(@cValue,'')='' begin SET NOCOUNT OFF Return --系统没有启用,不能执行触发器中的语句 end if @iSale=0 begin UPDATE SO_SODetails SET iFHQuantity=IsNull(iFHQuantity,0)+a.iquantity,iFHNum=IsNull(iFHNum,0)+a.inum,iFHMoney=IsNull(iFHMoney,0)+a.isum from SO_SODetails inner join (SELECT iSOsID,Sum(IsNull(iQuantity,0)) as iquantity,Sum(IsNull(iNum,0)) as inum,Sum(IsNull(iSum,0)) as isum FROM Inserted where isnull(isosid,0)<>0 GROUP BY iSOsID) a on SO_SODetails.isosid=a.isosid UPDATE SO_SOMAIN SET cSTCode= cSTCode FROM SO_SOMAIN INNER JOIN so_sodetails on SO_SOMAIN.id=so_sodetails.id inner join Inserted ON Inserted.isosid=so_sodetails.isosid UPDATE SO_SODetails SET fretquantity=isnull(fretquantity,0)+(-1*ISNULL(a.iQuantity,0)), fretnum=isnull(fretnum,0)+(-1*ISNULL(a.inum,0)) FROM SO_SODetails INNER JOIN (select isosid,sum(isnull(iquantity,0)) as iquantity,sum(isnull(inum,0)) as inum from Inserted where ISNULL(Inserted.iQuantity,0)<0 group by isosid) a ON SO_SODetails.isosid=a.isosid if @cverifier<>N'' and @bcashsale=0 begin update SO_SODetails set fVeriDispQty=isnull(fVeriDispQty,0)+a.iquantity,fVeriDispSum=isnull(fVeriDispSum,0)+a.inatsum from (select isosid,sum(isnull(iquantity,0)) as iquantity,sum(isnull(inatsum,0)) as inatsum from inserted where isnull(isosid,0)<>0 group by isosid) a inner join so_sodetails on so_sodetails.isosid=a.isosid update dispatchlists set fVeriRetQty=isnull(fVeriRetQty,0)-a.iquantity,fVeriRetSum=isnull(fVeriRetSum,0)-a.inatsum from (select icorid,sum(isnull(iquantity,0)) as iquantity,sum(isnull(inatsum,0)) as inatsum from inserted inner join dispatchlist on dispatchlist.dlid=inserted.dlid where isnull(icorid,0)<>0 and isnull(bneedbill,0)=0 group by icorid) a inner join dispatchlists on dispatchlists.idlsid=a.icorid end end --新增退货单参照发货单,修改对应发货单的累计退货数量 UPDATE DispatchLists SET iRetQuantity=IsNull(DispatchLists.iRetQuantity,0)+(-1*ISNULL(Inserted.iQuantity,0)), fretqtywkp=IsNull(DispatchLists.fretqtywkp,0)+(case when isnull(bneedbill,0)=0 then (-1*ISNULL(Inserted.iQuantity,0)) else 0 end), fretqtyykp=IsNull(DispatchLists.fretqtyykp,0)+(case when isnull(bneedbill,0)=1 then (-1*ISNULL(Inserted.iQuantity,0)) else 0 end), frettbquantity=isnull(DispatchLists.frettbquantity,0)+(case when isnull(bneedbill,0)=0 then (-1*ISNULL(Inserted.tbquantity,0)) else 0 end), frettbqtyykp=IsNull(DispatchLists.frettbqtyykp,0)+(case when isnull(bneedbill,0)=1 then (-1*ISNULL(Inserted.tbquantity,0)) else 0 end), fretsum=isnull(DispatchLists.fretsum,0)+(case when isnull(bneedbill,0)=0 then (-1*ISNULL(Inserted.isum,0)) else 0 end), fretsumykp=isnull(DispatchLists.fretsumykp,0)+(case when isnull(bneedbill,0)=1 then (-1*ISNULL(Inserted.isum,0)) else 0 end) FROM DispatchLists INNER JOIN Inserted ON DispatchLists.iDLsID=Inserted.iCorID inner join DispatchList on DispatchList.dlid=Inserted.dlid SET NOCOUNT OFF GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO; |
相关补丁: | |
版本: | U8 V10.0 |
模块: | 销售管理 |
产品: | 供应链 |
问题名称: | 上海-聚通-刘龙飞-销售退货 |
最后更新: | 2011-12-30 23:56:12 |