I'm using the following SP in order to restrict the return of more issued quantities of components item.
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.
Edited by: Balakumar Viswanathan on Feb 15, 2011 4:38 PM