PO Unit Price Exceeds Budget – Approval Query Issue

[Y-Yusuf]

Hi experts,

I want to trigger an approval process if the unit price in the purchase order document exceeds the budget allocated to the corresponding G/L account. I have written an approval query for this, but it is not working as expected. The issue is that it also triggers approval for unit prices that are within the budget limits.

Could you help me rewrite the approval query correctly? Here is the query I have written:

SELECT DISTINCT 'TRUE' FROM OPOR T0
INNER JOIN POR1 T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN OBGT T2 ON T2."AcctCode" = T1."AcctCode"
INNER JOIN BGT1 T3 ON T2."AbsId" = T3."BudgId"

WHERE $[$38.14.NUMBER] > T2."DebLTotal"
AND T2."DebLTotal" > 0
AND T2."FinancYear" = '2025-01-01'

Thanks. Regards.

[Felipe_Lima]

Hi @Y-Yusuf ,

Doesn't joining via DocEntry assume that you are dealing with two entries in the database? If the document has not been posted yet (pending approval), there's no DocEntry to link with.

$[$38.14.NUMBER] is implicitly referring to the current form opened, therefore you shouldn't need OPOR here.

You could try it just by removing the join with OPOR and see what happens.

Good luck.

BR,
Felipe

[Y-Yusuf]

Hi, I understand what you mean, and I have updated my query as follows:
SELECT DISTINCT 'TRUE' FROM POR1 T1
INNER JOIN OBGT T2 ON T2."AcctCode" = T1."AcctCode"
INNER JOIN BGT1 T3 ON T2."AbsId" = T3."BudgId"
WHERE $[$38.14.NUMBER] > T2."DebLTotal"
AND T2."DebLTotal" > 0
AND T2."FinancYear" = '2025-01-01'

However, the approval process still triggers even when I enter a unit price below the annual budget assigned to the G/L account. I can't figure out why this is happening.

[Y-Yusuf]

Additionally, when I convert this into a regular query, documents that went for approval are not listed in the query results, even though the unit price I entered is lower than the budget assigned to the G/L account.

SELECT T1."DocEntry", T1."Price",T2."DebLTotal", T2."AcctCode" , T1."AcctCode", T2."FinancYear" FROM POR1 T1
INNER JOIN OBGT T2 ON T2."AcctCode" = T1."AcctCode"
INNER JOIN BGT1 T3 ON T2."AbsId" = T3."BudgId"
WHERE T1."LineTotal" > T2."DebLTotal"
AND T2."DebLTotal" > 0
AND T2."FinancYear" = '2025-01-01'
GROUP BY T1."DocEntry", T1."Price",T2."DebLTotal", T2."AcctCode" , T1."AcctCode", T2."FinancYear"