Approval Query based on department and based on amount

[wale_adegbola]

Dear All,

I have a client that wants to customize the approval template based on this query but they want the system to be doing it in such a way that the amount will be in dollar between zero dollar to five thousand dollar  which is the system currency but the PO will be raised in Naira which is a local currency which will be in the same range in naira based on the exchange rate for that day.

This is a sample query that was written in naira but we want to rewrite it again such that when they raise the PO in naira it will pick the query which the amount is in dollar range.

SELECT CASE WHEN $[OPOR.U_UserDept] = 'ADD' AND CAST($[OPOR.Doctotal] as decimal(10,2)) < 1000001 THEN 'TRUE' ELSE 'FALSE' END

[BattleshipCobra]

Hi Wale!  You should be able to do this just with the CASE statements you are using:

SELECT CASE WHEN $[OPOR.U_UserDept] = 'ADD' AND $[OPOR.DocCur] = '' (<--currency code for Naira) AND $[OPOR.DocTotalFC] BETWEEN 0 AND 5000 THEN 'TRUE' WHEN $[OPOR.U_UserDept] = 'ADD' AND $[OPOR.DocCur] = '' (<--code for system currency) AND $[OPOR.DocTotal] BETWEEN 0 AND 1000001 THEN 'TRUE' ELSE 'FALSE' END

(something like this above, not exactly)

You could also just do two approval templates if you want different approvers but using the same fields and logic.  If you just make your specific CASE clauses have the values you want then make the query resolve to 'TRUE' you should be OK.

On the PO when you use any FC (Naira I would assume is a foreign currency in your scenario) it already will do the conversion by the exchange rate and show the total in the $[OPOR.DocTotalFC] dynamic syntax field.  (and even with FC if you use the regular [OPOR.DocTotal] it should give you the system currency equivalent of the FC value at the daily exchange rate too).

I hope this gives you what you need to finish the customization.  I am not on this forum very much (I see you tagged me) but somehow I'm getting notifications.

You can message me on LinkedIn (http://linkedin.battleshipcobra.com/) if you need more help, thanks!

M