cancel
Showing results for 
Search instead for 
Did you mean: 

Approval Procedure - how to pass value to query

Former Member
0 Kudos

I want to do an approval procedure for checking GRPO Qty which greater than PO OpenQty by Query.

How to pass the GRPO DocEntry and LineNum to the query, and how can check the passing value is correct or not?

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Try this simple one, remember only the first line will be checked:

SELECT 'TRUE' from POR1 T1

WHERE T1.ItemCode = $[$-38.1.0\] AND T1.OpenQty < $[$-38.11.Number\] AND T1.DocEntry = $[$-38.45.Number\]

Answers (1)

Answers (1)

former_member197621
Active Contributor
0 Kudos

Hi Fu On Wong

The Approval Procedures which are based on row level data only take into consideration the result based on the FIRST ROW.

So, if you have mutiple rows (lines) on your document the Approval Query will always be applied on the first row.

CONCLUSION:

APPROVAL PROCEDURES ON ROW LEVEL WILL ONLY BE USEFUL WHEN YOU HAVE ONLY ONE LINE IN YOUR DOCUMENTS.

Edited by: Prasanna s on Oct 13, 2009 10:27 AM

Former Member
0 Kudos

I have tested one row only, but also not work.

Here is the query I used.

select case when (t1.openqty < $\[PDN1.Quantity.0]) then 'TRUE' ELSE 'FALSE' END as RESULT from pdn1 t0

right outer join por1 t1 on t1.docentry = t0.baseentry and t1.linenum = t0.baseline

where t1.docentry in ($\[PDN1.BASEENTRY.0])

group by t1.docentry, t1.linenum, t1.quantity, t1.openqty

having (sum(isnull(t0.quantity,0)) < t1.quantity)

order by t1.docentry, t1.linenum, t1.quantity, t1.openqty

If the pass value is correct, the result should be like this.

TRUE

Is the query wrong? Any suggestion?

Thanks.

Former Member
0 Kudos

Try this one:

SELECT 'TRUE' from pdn1 t0

INNER JOIN POR1 t1 on t1.docentry = t0.baseentry and t1.linenum = t0.baseline

INNER JOIN OPDN T2 ON T2.DocEntry = T0.DocEntry

WHERE T1.OpenQty < $[$-38.11.Number\] AND T2.DocNum = $[$8.0.Number\]

Thanks,

Gordon

Former Member
0 Kudos

I tried, but it does not work.

Former Member
0 Kudos

You may check by opening a GRPO and run query from this saved query. Have you got any error messages?

Former Member
0 Kudos

The saved query cannot run directly. How can I test it?

I test it as a formatted search. But nothing is returned.

I use the old query, it returned 'TURE', but also no approval message is shown up.

SELECT 'TRUE' from pdn1 t0

INNER JOIN POR1 t1 on t1.docentry = t0.baseentry and t1.linenum = t0.baseline

INNER JOIN OPDN T2 ON T2.DocEntry = T0.DocEntry

WHERE T1.OpenQty < $\[$-38.11.Number] AND T2.DocNum = $\[$8.0.Number]

I think the query may not work. Because the GRPO has not added yet.

So the where condition is always 'FALSE'.