Approval template query for GRPO Qty greater than PO Qty

[former_member236650]

Hello Experts!

I need some help with creating a query for my approval procedures.

I would like to set up a query based approval template that creates an approval prompt whenever the quantity entered for a GRPO (PDN1.Quantity) is greater than the quantity from the referenced PO (POR1.Quantity).

Thank you!

[edy_simon]

Hi Nikka,

The last time i check,  approval does not support document lines.

It check for the header only.

You may need to do work around, with the combination of sp notification.

Regards

Edy

[former_member185682]

Hi Edy and Nikka,

First of all my SAP is 9.1 PL 12, maybe doesn't work in older versions.

About the Approval template in my version, is not possible do queries in line level looking information of the header  level or queries in header level looking information on line level, but in the situation that Nikka describe, only look on line level then it is possible.

I did a lot of tests now, and I checked that the sintax $[Table.Field] doesn't work correctly for approval template, I don't know why. But if you look the screen field using the following sintax $[$Item.col] the approval template works correctly.

The new query:

SELECT 
  CASE WHEN CAST(REPLACE($[$38.11], ',', '.') AS Numeric) > Quantity THEN 'TRUE' ELSE 'FALSE' END
FROM 
  POR1 
WHERE 
  DocEntry = $[$38.45] AND LineNum = $[$38.46] AND $[$38.43] = 22

$[$38.11] -> Column quantity

$[$38.45] -> Column BaseEntry

$[$38.46] -> Column Base LineNum

$[$38.43] -> Column BaseType

Hope it helps.

---

Best regards,

Diego Lother

[former_member233854]

Hi,

Diego is right, just adding something else to you query

You don't need to use CAST and REPLACE, in order to get the correct format of the fields you can use for example

$[$38.11.NUMBER] instead of  CAST(REPLACE($[$38.11], ',', '.') AS Numeric)

[former_member185682]

Hi all,

Just to correct my last answer, is it possible combine line and header level.

I tested with a query like this and works fine:

SELECT CASE WHEN $[$38.15.NUMBER] > 20 AND $[$4.0.0] = 'C00001'  THEN 'TRUE' ELSE 'FALSE' END

Hope it helps.

---

Best regards,

Diego Lother

[former_member236650]

Hi Mr. Diego!

Thanks a lot for responding to my post. I'll try out the query today and will let you know if it works.

Regards,

Nikka

[former_member185682]

Hi Nikka,

Try something like this:

SELECT 
  CASE WHEN $[PDN1.Quantity] > Quantity THEN 'TRUE' ELSE 'FALSE' END
FROM 
  POR1 
WHERE 
  DocEntry = $[PDN1.BaseEntry] AND LineNum = $[PDN1.BaseLine] AND $[PDN1.BaseType] = 22

Hope it helps.

---

Best regards,

Diego Lother