Reg: SP - Return Components restriction

[kvbalakumar]

Dear Experts,

I'm using the following SP in order to restrict the return of more issued quantities of components item.

For ex.

FG - A0001 baseqty - 1

Component - C0001 baseqty - 2

1.Let a production order made for 100 quantity of item A0001 and components issued is only 80 qty.

2. Receipt from production document made for 20 qty (A0001) i.e 20 x 2 = 40 qty of C0001 was utilized for production.

3. User must be able to return only less than or equal to 80 - (20 x 2) = 40 qty.

My SP is

IF exists(SELECT distinct 'error'  
	FROM dbo.OIGN T0 INNER JOIN dbo.IGN1 T1 ON T0.DOCENTRY = T1.DOCENTRY 
	inner join WOR1 T2 on T1.BaseEntry = T2.DocEntry and (T2.ItemCode = T1.ItemCode) and (T1.BaseLine = T2.LineNum)
	inner join OWOR T3 on T3.DocEntry = T1.BaseEntry 
	WHERE (T1.TranType is null) 
	and (T3.Type != 'D') and ((isnull(T2.IssuedQty,0) - (isnull(T2.BaseQty,0) * (isnull(T3.CmpltQty,0) + isnull(T3.RjctQty,0)))) < isnull(T1.Quantity,0))
	and (T1.BaseType = N'202') and (T0.DOCENTRY = @list_of_cols_val_tab_del))
begin
	SELECT @Error = 1, @error_message = 'More than Produced quantity!'
End

I don't know where i'm going wrong.

Regards,

Bala

Edited by: Balakumar Viswanathan on Feb 15, 2011 4:38 PM

[Former Member]

Hi Balakumar,

try this:

IF @transaction_type = N'A' AND (@Object_type = N'59')

BEGIN

declare @TMP Table(

[NormsQty] [numeric](38, 6) NULL,

[ItemCodeRe] [nvarchar](20) null,

[Total] [int] NULL,

[DocNum] [int] NOT NULL,

[ItemCode] [nvarchar](20) NULL,

[IssueQty] [numeric](38, 6) NULL

)

insert into @TMP

SELECT SUM(dbo.WOR1.PlannedQty * dbo.IGN1.Quantity / dbo.OWOR.PlannedQty) AS NormsQty, dbo.OWOR.ItemCode AS [ItemCodeRe],

dbo.IGN1.DocEntry AS Total, dbo.OWOR.DocNum, dbo.WOR1.ItemCode, SUM(V_Total.Quantity) AS IssueQty

FROM dbo.OWOR INNER JOIN

dbo.WOR1 ON dbo.OWOR.DocEntry = dbo.WOR1.DocEntry LEFT OUTER JOIN

(SELECT BaseEntry, ItemCode, SUM(Quantity) AS Quantity

FROM dbo.IGE1

GROUP BY BaseEntry, ItemCode) AS V_Total ON dbo.WOR1.ItemCode = V_Total.ItemCode AND

dbo.OWOR.DocEntry = V_Total.BaseEntry LEFT OUTER JOIN

dbo.IGN1 ON dbo.OWOR.ItemCode = dbo.IGN1.ItemCode AND dbo.OWOR.DocEntry = dbo.IGN1.BaseEntry

WHERE (dbo.IGN1.BaseType = 202) AND (dbo.WOR1.IssueType = 'M')

GROUP BY dbo.OWOR.ItemCode, dbo.IGN1.DocEntry, dbo.OWOR.DocNum, dbo.WOR1.ItemCode

declare @DocNum nvarchar(30)

declare @ITemTP nvarchar(50)

declare @Total nvarchar(50)

declare @ItemCode nvarchar(50)

declare cur cursor for

select distinct DocNum ,[ItemCodeRe] ,Total, Itemcode from @TMP

open cur

fetch next from cur into @DocNum,@ITemTP,@Total,@ItemCode

while @@fetch_status=0

begin

update @TMP set NormsQty=( select sum(NormsQty) from @TMP where [ItemCodeRe]=@ITemTP and DocNum=@DocNum and Total<=@Total and ItemCode=@ItemCode)

where [ItemCodeRe]=@ITemTP and DocNum=@DocNum and Total=@Total and ItemCode=@ItemCode

fetch next from cur into @DocNum,@ITemTP,@Total,@ItemCode

end

close cur

deallocate cur

if Exists (SELECT [ItemCode]

FROM (select * from @TMP) AS tmp

where ( isnull(NormsQty,0) > isnull(IssueQty,0))and [Total]= @list_of_cols_val_tab_del)

BEGIN

SELECT @Error = 101, @error_message = N'Receipt Quantity greater than Issuse quantity. Please check again!'

END

END

hope this help you..

Thanks

H2

Edited by: Hoe Pham Huy on Feb 15, 2011 1:23 PM

[kvbalakumar]

Hi H2,

Thanks for the SP but it doesn't solve by problem rather it hangs my system.

Regards,

Bala