Validation If unit price of GRPO is different from PO

[Former Member]

Hi,

I am trying to assign 1 validation for scenario mentioned in the subject. The validation which i have tried is showing me error even if i have not changed the unit price at the GRPO level.

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

(@Object_type = N'20')

begin

if exists (SELECT T1.[Price], T3.[Price] FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OPOR T2 ON T2.DocEntry=T1.BaseEntry INNER JOIN POR1 T3 ON T2.DocEntry = T3.DocEntry

WHERE T1.ItemCode=T3.ItemCode And T1.[Price]<>T3.[Price])

Begin

select @Error = 10, @error_message =

'UNit price is different from PO'

Regards-

Monica.

[Former Member · Accepted solution]

Its not working if i have put discount value

[Former Member · Accepted solution]

Ok........

So what you exactly want validation?

If price is different then its validating but now even if you put discount it should work?

Regards,

Best Rahul

[Former Member · Accepted solution]

Hi,

This validation is not working if more than one line items are there.For example i have one po with 5 different line items & only one line item have some open qty when i have not chnged unit price for that item in grpo then also it is showing me error.

mona.

[Former Member · Accepted solution]

Hi Mono,

Tr this,

If @object_type='20' AND @transaction_type = 'A'
BEGIN 
If Exists (SELECT T0.Price, T1.Price FROM PDN1 T0 
INNER JOIN POR1 T1 ON T0.BaseEntry=T1.DocEntry
AND T0.BaseLine=T1.LineNum
WHERE 
T0.DocEntry = @list_of_cols_val_tab_del
AND
T0.Price!=T1.Price)
BEGIN
SELECT @error = 1,
@error_message = 'Price is not Same'
End
End

Regards,

Madhan.

[former_member204969 · Accepted solution]

Try this one:

If @object_type='20' AND @transaction_type = 'A'
BEGIN 
If Exists
 (SELECT T0.Price, T1.Price
   FROM PDN1 T0 INNER JOIN POR1 T1
         ON T0.BaseEntry=T1.DocEntry and 
            T0.BaseLine=T1.LineNum and T0.BaseType=22
   WHERE T0.DocEntry = @list_of_cols_val_tab_del
     and T0.Price!=T1.Price)
Begin
SELECT @error = 1,
@error_message = 'Price is different from order !'
End
END

[Former Member · Accepted solution]

Dear Madhan & Istvan,

Thank you for your response but its not working.

Regards-

Mona.

Edited by: Mona on Dec 10, 2009 12:45 PM

[former_member204969 · Accepted solution]

Hi Mona!

I think it should work. I tried it in different cases.

Did you try exactly the code I sent? What was the case you tried and what result you got?

Regards Istvá

[Former Member · Accepted solution]

Hello,

I tried by creating one PO with 5 different items with some price.Then i had copied to GRPO & changed the unit price but the system allowed me to do so without any error.

Regards-

Monica.

[former_member204969 · Accepted solution]

Could you show the full SBO_SP_TransactionNotification stored procedure?

[Former Member · Accepted solution]

Hi Mona,

Try this,

If @object_type='20' AND @transaction_type = 'A'
BEGIN 
If Exists
 (SELECT TRUE
   FROM PDN1 T0 INNER JOIN POR1 T1
         ON T0.BaseEntry=T1.DocEntry and 
            T0.BaseLine=T1.LineNum 
   WHERE T0.DocEntry = @list_of_cols_val_tab_del
      AND T0.BaseRef=' '
     AND T0.Price!=T1.Price)
Begin
SELECT @error = 1,
@error_message = 'Price is different'
End
END

[Former Member · Accepted solution]

Hi Mona

Try with this :

If @object_type='20' AND @transaction_type = 'A'
BEGIN 
If Exists
 (SELECT TRUE
   FROM PDN1 T0 INNER JOIN POR1 T1
         ON T0.BaseEntry=T1.DocEntry and 
            T0.BaseLine=T1.LineNum 
   WHERE T0.DocEntry = @list_of_cols_val_tab_del
      AND (T0.BaseRef=' ' or T0.BaseRef IS NULL)
     AND T0.Price!=T1.Price)
Begin
SELECT @error = 1,
@error_message = 'Price is different'
End
END

The logic is same but becasue of data coming into the system . Also check in your database

select * from POR1

Hope this helps

Bishal

[Former Member]

Hi Monica,

You may run this query first to see any GRPO records meet the conditions:

SELECT T0.DocEntry, T0.Price, T1.Price

FROM PDN1 T0 INNER JOIN POR1 T1

ON T0.BaseEntry=T1.DocEntry and

T0.BaseLine=T1.LineNum and T0.BaseType=22

WHERE T0.DocEntry = T1.DocEntry and T0.Price!=T1.Price

Could you find the new GRPO you just tried to add?

Thanks,

Gordon

[Former Member]

Hi Mona....

Try This....

If @object_type='20' and @transaction_type='A'
BEGIN 
If Exists (Select T0.Price, T1.Price from [dbo].[PDN1] T0 Inner Join POR1 T1 
On T0.BaseEntry=T1.DocEntry
Where T0.Price<>T1.Price
And T0.DocEntry = @list_of_cols_val_tab_del)
BEGIN
Select @error = -1,
@error_message = 'Price is not Same'
End
End

Regards,

Best Rahul

[Former Member]

I tried that stored procedure but it is not working.

[Former Member]

Hi rahul,

Now its working the difference was Where T0.PriceT1.Price

If @object_type='20' and @transaction_type='A'

BEGIN

If Exists (Select T0.Price, T1.Price from [dbo].[PDN1] T0 Inner Join POR1 T1

On T0.BaseEntry=T1.DocEntry

Where T0.Price<>T1.Price

And T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

Select @error = -1,

@error_message = 'Price is not Same'

End

End

thanks-

Mona.

[Former Member]

Ok.....

Kooooooooooooooool.............

[Former Member]

Rahul,

It's not working if i have applied discount ?

mona.