Query For purchase order item quantity to Grpo

[Former Member]

Hi Experts,

In Purchase order if i selected 100 qty for a item and while copy to GRPO the quanitity of item may be 110

so if its is 5% means 105 its ok but if it is 106 qty the document should not be added

what is the query for that

in which stored procedure i have to write this query

Please help

[former_member188586 · Accepted solution]

hi

Use bellow SP

-------------- Validation for Goods Receipt PO Restricting Excess Qunatity ----------------------------

IF @Object_type = '20' and @transaction_type = 'A'

BEGIN

declare  @line int

SELECT @line = (LineNum + 1)

From PDN1

Where PDN1.DocEntry = @list_of_cols_val_tab_del

   and (Quantity > BaseOpnQty)

Order by LineNum

If (not ISNULL(@line, 0) = 0)

begin

  Set @error = 10

  Set @error_message = 'Line quantity' + CONVERT(nvarchar(4), @line) +' is more then open quantity !'

end

END

Regards

Ramudu

[former_member212181]

Hi Nikhil,

Please refer below post.

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

Thanks

Unnikrishnan

[Former Member]

i got it but i want exactly if the quantity is below 5% and 5% is ok but it is more than 5% only i want the erroe message

please help

[former_member212181]

Hi Nikhil,

Please try below query then and revert back

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

HAVING SUM(T0.Quantity)+(SUM(T0.Quantity)*.05) > SUM(T1.Quantity)

or SUM(T0.Quantity)+(SUM(T0.Quantity)*.05) > sum(t0.BaseOpnQty)

begin

select @Error = 10, @error_message =

'GRPO quantity is over PO quantity'

end

end

Thanks

Unnikrishnan

[Former Member]

its showing this error while executing

Incorrect syntax near the keyword 'begin'.

[Former Member]

its not working even it is more than 1 quantity also its showing the error

[former_member212181]

Hi Nikhil,

Pleae make the changes in this area

old:-

HAVING SUM(T0.Quantity)+(SUM(T0.Quantity)*.05) > SUM(T1.Quantity)

or SUM(T0.Quantity)+(SUM(T0.Quantity)*.05) > sum(t0.BaseOpnQty)

New

HAVING SUM(T0.Quantity) > (SUM(T1.Quantity) +(SUM(T1.Quantity)*.05))

or SUM(T0.Quantity) > (sum(t0.BaseOpnQty) +(SUM(T0.BaseOpnQty)*.05))

Thanks

Unnikrishnan

[Former Member]

for one item its working fine

but if i added two items its again adding

means if 1st item is 102 its ok

second item is 106 so second item is more than 5% but it is adding

it should not add

Please Help