Reg: SP - Return Components restriction

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