ALTER PROCEDURE [dbo].[INVM_InventoryValuationForIssue]
@TransDate datetime,
@IssueFrmDocType varchar(10),
@IssueFrmDocNo numeric(18,0),
@IssueFrmDocDate datetime,
@FormType char(1),
@ItemCode varchar(25),
@ItemDesc varchar(250),
@Qty numeric(18,6),
-- @Price numeric(18,6),
@WhCode nvarchar(25),
@WhLoc nvarchar(25),
@CmpyName nvarchar(50),
@DiscountAmount numeric(18,6),
@BaseDocType varchar(10),
@BaseDocNo numeric(18,0),
@BaseDocDate datetime
AS
BEGIN
declare @transnum numeric(18,0)
--Variables for Moving Avg
declare @MACumQty numeric(18,6)
declare @MACumValue numeric(18,6)
declare @MACumValue1 numeric(18,6)
declare @MACumValue2 numeric(18,6)
declare @AvgPriceMA numeric(18,6)
declare @MAPrice numeric(18,6)
--Variables for Moving Avg
declare @FIFOCumQty numeric(18,6)
declare @FIFOCumValue numeric(18,6)
declare @FIFOCumValue1 numeric(18,6)
declare @FIFOCumValue2 numeric(18,6)
declare @AvgPriceFIFO numeric(18,6)
declare @TransValue numeric(18,6)
--Variables for Standard Cost
declare @STDCumQty numeric(18,6)
declare @STDCumValue numeric(18,6)
declare @STDCumValue1 numeric(18,6)
declare @STDCumValue2 numeric(18,6)
declare @AvgPriceSTD numeric(18,6)
declare @dummy numeric(18,0)
declare @dummy1 numeric(18,0)
-- Other Local Variables
declare @CumQty numeric(18,6)
declare @CumValue numeric(18,6)
declare @ValMth char(1)
-- declare @BaseDocType varchar(10)
-- declare @BaseDocNo numeric(18,0)
-- declare @BaseDocDate datetime
declare @TransQty numeric(18,6)
declare @TransQty1 numeric(18,6)
declare @PendingQty numeric(18,6)
declare @InsertQty numeric(18,6)
declare @Total numeric(18,6)
-- select @transnum = 1
DECLARE SrNo CURSOR FOR SELECT isnull(Max(TransNum),0)+1 FROM INVM
OPEN SrNo
FETCH NEXT FROM SrNo INTO @transnum
CLOSE SrNo
DEALLOCATE SrNo
-- FOR MOVING AVG METHOD
-- Transaction Qty is assigned to Pending Qty variable
-- because looping is performed on that
SELECT @PendingQty = @Qty
WHILE @PendingQty > 0
BEGIN
If (@BaseDocType = '' or @BaseDocType is null)
Begin
DECLARE Trans CURSOR FOR SELECT TransQty,BaseDocNo,BaseDocType,BaseDocDate FROM INVM WHERE ItemCode=@ItemCode and ValMethod='M' and TransQty>0 and Status is null ORDER BY transnum
OPEN Trans
FETCH NEXT FROM Trans INTO @TransQty,@BaseDocNo,@BaseDocType,@BaseDocDate
End
Else
Begin
DECLARE Trans CURSOR FOR SELECT TransQty,TransValue,BaseDocNo,BaseDocType,BaseDocDate FROM INVM WHERE ItemCode=@ItemCode and ValMethod='M' and BaseDocNo=@BaseDocNo and BaseDocType=@BaseDocType and BaseDocDate=@BaseDocDate ORDER BY transnum
OPEN Trans
FETCH NEXT FROM Trans INTO @TransQty,@TransValue,@BaseDocNo,@BaseDocType,@BaseDocDate
End
DECLARE MAQty1 CURSOR FOR SELECT isnull(sum(TransQty),0) from INVM Where ItemCode=@ItemCode and ValMethod='M' and BaseDocNo=@BaseDocNo and BaseDocType=@BaseDocType and BaseDocDate=@BaseDocDate
OPEN MAQty1
FETCH NEXT FROM MAQty1 INTO @TransQty1
IF @PendingQty < isnull(@TransQty1,0) -- If Transaction Qty is less then CumQty.
BEGIN
SELECT @InsertQty= @PendingQty * -1
-- Setting Pending Qty as 0 be cause complete qty will be issued from transqty
SELECT @PendingQty=0
END
IF @PendingQty > isnull(@TransQty1,0)
BEGIN
SELECT @PendingQty=(@PendingQty - isnull(@TransQty1,0))
SELECT @InsertQty= @TransQty1 * -1
END
IF @PendingQty = isnull(@TransQty1,0)
BEGIN
SELECT @PendingQty = 0
SELECT @InsertQty = isnull(@TransQty1,0) * -1
END
IF @InsertQty <> 0
Begin
--FETCH THE PRICE FROM WAREHOUSE MASTER
DECLARE MAPrice CURSOR FOR SELECT AvgCost from INI1 Where ItemCode=@ItemCode and WhCode=@WhCode
OPEN MAPrice
FETCH NEXT FROM MAPrice INTO @MAPrice
if isnull(@TransValue,0)>0
BEGIN
SELECT @Total = @InsertQty * @TransValue
Select @MAPrice=@TransValue
End
else
Begin
SELECT @Total = @InsertQty * isnull(@MAPrice ,0)
End
--FIND THE CUMULATIVE
DECLARE MAcumqty CURSOR FOR SELECT isnull(sum(TransQty),0) from INVM Where ItemCode=@ItemCode and ValMethod='M'
OPEN MAcumqty
FETCH NEXT FROM MAcumqty INTO @MACumQty
-- Other Documents except Invocie
DECLARE MAcumvalue CURSOR FOR select isnull(sum(TransTotal),0) from INVM Where ItemCode=@ItemCode and ValMethod='M' and BaseDocType<>'PUIH'
OPEN MAcumvalue
FETCH NEXT FROM MAcumvalue INTO @MACumValue
-- If Invoice Entry has difference amount
DECLARE MAcumvalue1 CURSOR FOR SELECT isnull(sum(DiffAmount),0) from INVM Where ItemCode=@ItemCode and ValMethod='M' and BaseDocType='PUIH'
OPEN MAcumvalue1
FETCH NEXT FROM MAcumvalue1 INTO @MACumValue1
-- Entries which are done directly from AP invoice
DECLARE MAcumvalue2 CURSOR FOR SELECT isnull(sum(TransTotal),0) from INVM Where ItemCode=@ItemCode and ValMethod='M' and BaseDocType='PUIH' and TransQty>0
OPEN MAcumvalue2
FETCH NEXT FROM MAcumvalue2 INTO @MACumValue2
select @CumQty = isnull(@MACumQty,0) + @InsertQty
select @CumValue = isnull(@MACumValue,0) + isnull(@MACumValue1,0) + isnull(@MACumValue2,0) + @Total
IF @CumQty > 0
BEGIN
if @CumValue >0
BEGIN
select @AvgPriceMA = @CumValue / @CumQty
END
Else
BEGIN
select @AvgPriceMA=0
END
END
ELSE
BEGIN
select @AvgPriceMA=0
END
-- INSERT THE SAME IN TABLE
INSERT INTO INVM(TransNum,TransDate,BaseDocType,BaseDocNo,BaseDocDate,IssueFrmDocType,IssueFrmDocNo,IssueFrmDocDate,FormType,ItemCode,ItemDesc,
TransQty,TransValue,TransTotal,CumtQty,CumtMA,AvgPrice,WhseCode,WhseLoc,ValMethod,CmpyName,DiscountAmount)
VALUES(@transnum,@TransDate,@BaseDocType,@BaseDocNo,@BaseDocDate,@IssueFrmDocType,@IssueFrmDocNo,@IssueFrmDocDate,@FormType,
@ItemCode,@ItemDesc,@InsertQty,@MAPrice,@Total,
@CumQty,@CumValue,@AvgPriceMA,@WhCode,@WhLoc,'M',@CmpyName,@DiscountAmount)
--UPDATE LINE STATUS
DECLARE MAQty CURSOR FOR SELECT isnull(sum(TransQty),0) from INVM Where ItemCode=@ItemCode and ValMethod='M' and BaseDocNo=@BaseDocNo and BaseDocType=@BaseDocType and BaseDocDate=@BaseDocDate
OPEN MAQty
FETCH NEXT FROM MAQty INTO @TransQty1
IF isnull(@TransQty1,0) = 0
BEGIN
UPDATE INVM SET Status='C' Where ItemCode=@ItemCode and ValMethod='M' and BaseDocNo=@BaseDocNo and BaseDocType=@BaseDocType and BaseDocDate=@BaseDocDate and IssueFrmDocDate is null
select @BaseDoctype=''
END
-- Close the variables
CLOSE MAcumqty
DEALLOCATE MAcumqty
CLOSE MAcumvalue
DEALLOCATE MAcumvalue
CLOSE MAPrice
DEALLOCATE MAPrice
CLOSE MAcumvalue1
DEALLOCATE MAcumvalue1
CLOSE MAcumvalue2
DEALLOCATE MAcumvalue2
CLOSE MAQty
DEALLOCATE MAQty
select @transnum = @transnum + 1
END
CLOSE Trans
DEALLOCATE Trans
CLOSE MAQty1
DEALLOCATE MAQty1
SELECT @PendingQty = @PendingQty
END
-- select @transnum = @transnum + 1
--FETCH THE VALUATION METHOD FROM ITEM MASTER
DECLARE ItemCost CURSOR FOR SELECT ValuMth from INIM Where ItemCode=@ItemCode
OPEN ItemCost
FETCH NEXT FROM ItemCost INTO @ValMth
-- Update the Avg Price in Master's.
if @ValMth='M'
BEGIN
Update INI1 Set AvgCost=@AvgPriceMA Where ItemCode=@ItemCode
Update INIM Set AvgCost=@AvgPriceMA Where ItemCode=@ItemCode
END
CLOSE ItemCost
DEALLOCATE ItemCost
END
我该如何处理？它并不总是发生，只是有时。我写错了什么，可以进入无限循环吗？我无法理解它的问题，因为有时它也会立即执行 40 多个项目，有时只针对 1 个项目超时错误。
请
如果该过程正在处理的数据集因传入的参数值而异，则可以尝试在此处查看有关参数嗅探的答案： SQL Server 中的参数探查（或欺骗）
你必须调试它没有选项，应用日志记录并查看
谢谢阿比