SP for GRPO qty with tolerance 10% error !

[Former Member]

Hi.

I am running the below query to restrict GRPO having quantity greater than PO qty with tolerance of 10 %. It works fine but it throws sql error whenever we do GRPO for open partial quantity from PO even when the qty are into range of below 10% tolerance.

IF @OBJECT_TYPE='20' AND @TRANSACTION_TYPE='A'

BEGIN

          DECLARE @ITEMCODE NVARCHAR (20)

          DECLARE @DOCENTRY INT

          DECLARE @LINENUM INT

          DECLARE @POQTY NUMERIC(19, 6)

          DECLARE @GRQTY NUMERIC(19, 6)

          SELECT @DOCENTRY = 0

          SELECT @POQTY = 0

          SELECT @GRQTY = 0

          SELECT @DOCENTRY = CONVERT(INT, SUBSTRING(@LIST_OF_COLS_VAL_TAB_DEL, 1, 10))

          SELECT GR.LINENUM, GR.ITEMCODE, POQTY=(PO.QUANTITY)*1.1, GRQTY=(SELECT SUM(GR1.QUANTITY)

               FROM PDN1 GR1 WHERE GR1.BASEENTRY=GR.BASEENTRY AND GR1.BASELINE=GR.BASELINE

               GROUP BY GR1.BASEENTRY, GR1.BASELINE)

          INTO #STR_GRPO

          FROM PDN1 GR INNER JOIN POR1 PO ON GR.BASETYPE='22' AND GR.BASEENTRY=PO.DOCENTRY AND GR.BASELINE=PO.LINENUM

          WHERE GR.DOCENTRY=@DOCENTRY

          SELECT TOP 1 @LINENUM=LINENUM+1, @ITEMCODE=ITEMCODE, @POQTY=POQTY, @GRQTY=GRQTY

               FROM #STR_GRPO WHERE GRQTY>POQTY ORDER BY LINENUM

     IF @GRQTY>@POQTY

          BEGIN

               SELECT @error=1

               SELECT @error_message='ERROR (Tolerance is 10%): TOTAL RECEIVED QTY('+CONVERT(VARCHAR(20),@GRQTY)+') > PO QTY('+CONVERT(VARCHAR(20),@POQTY)+')! LINENUM: '+CONVERT(VARCHAR(3), @LINENUM)+', ITEMCODE: '+@ITEMCODE

          END

     END

Please advise the correct solution for this.

Thanks,

Harshal

[Former Member]

Hi AndakondaRamudu A,

I have checked the above thread and also tried but it doesn't work. My above query works fine with all open quantity but the problem is only when partial quantity is remaining.

Please help to resolve this issue.

Regards,

Harshal Makwana

[Former Member]

Hi Harshal,

You may check this:

IF @transaction_type IN (N'A', N'U') AND (@Object_type = N'20')

begin

if exists (SELECT T0.BaseEntry, SUM(T0.Quantity)

FROM [dbo].[PDN1] T0

INNER JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = T0.BASEENTRY

WHERE T0.BaseType = '22' AND T0.ItemCode = T1.ItemCode AND T0.BaseLine = T1.LineNum

and T0.DOCENTRY = @list_of_cols_val_tab_del

GROUP BY T0.BaseEntry,T0.BaseLine

HAVING (sum(t0.quantity) > (sum(t0.BaseOpnQty)+ sum(t1.Quantity)*10/100)))

begin

select @Error = 10, @error_message = 'GRPO quantity is greater than PO quantity'

end

end

Thanks,

Joseph

[former_member188586]

hi

please check bellow thread

http://scn.sap.com/thread/3402973

Thanks&regards

Andakondaramudu