Approval Process to require Approval for non-Euro Purchase Orders

[Former Member]

Hi Experts

I am trying to create an Approval Process whereby any PO that is raised in a Currency other than the EURO will cross my desk.

I am having difficulty creating the SQL query necessary to isolate these Orders. Anyone able to help me?

Many thanks

Derek

[former_member187989 · Accepted solution]

hi,

SELECT Distinct 'TRUE' FROM OPOR T0 WHERE T0.DocCur ! = 'EUR'

Save query in query manager.

Jeyakanthan

Edited by: Jeyakanthan A on Apr 27, 2009 1:11 PM

Edited by: Jeyakanthan A on Apr 27, 2009 1:12 PM

Edited by: Jeyakanthan A on Apr 27, 2009 1:12 PM

[Former Member · Accepted solution]

Hi Jeyakanthan

Ran the query, but both Euro, US$ and £ PO's require authorisation!

Any further thoughts?

Derek

[Former Member · Accepted solution]

Hi Jeyakanthan

What about a different route to the same issue - ie. if the currency of the Supplier not = Euro, then run approval process?

Derek

[Former Member · Accepted solution]

Hi Jeyakanthan

Ignore earlier question - just realised that was what we were trying to achieve in SQL Query!

Early monday morning here !

Derek

[Former Member · Accepted solution]

Hi Jeyakanthan

we were recently updated to : V8.00.178 SP 00 PL: 42

Derek

[former_member204969 · Accepted solution]

Try to use this code to send approval the non-eur PO-s:

If  $[OPOR.DocCur] != 'EUR'
 Select 'TRUE'
else 
 Select 'False'

[Former Member · Accepted solution]

Hi Jeyakanthan

2007 version

Derek

[Former Member · Accepted solution]

Thank you for your input Istvan.

I keyed in the following:

If $[OPOR.DocCur] != 'EUR'

Select 'TRUE'

else

Select 'False'

But got Error: (1) incorrect syntax near OPOR.DocCur. and (2)...OCLG could not be prepared. I do not have the SQL skills to recognise the syntax problem?

Derek

[former_member204969 · Accepted solution]

This code can not be executed as a query. It works only as an FS or as an approval procedure. So try to save it and set in the approval template terms.

By the way, you can copy the code from the forum after selecting with the mouse.

[Former Member · Accepted solution]

Hi Istvan

As a non technical person, I'm afraid I'm lost!

When you advise me to save it, what format and where do I save it?

When you advise set in the Approval "Terms" I assume you mean the "Terms" tab. If so, how do I link to this file?

Appreciate your input

Derek

[former_member204969 · Accepted solution]

You should save it as a query with any name.

And under the terms tab you should

1. set When the Following Applies

2. in the appearing Terms Based on User Queries window double click and select the query according to the name you gave to it.

[Former Member · Accepted solution]

HI Istivan

I did save the following as a Query and linked it to an Approval Process. However, as you can see below, I was getting errors.

If $http://OPOR.DocCur != 'EUR'

Select 'TRUE'

else

Select 'False'

But got Error: (1) incorrect syntax near OPOR.DocCur. and (2)...OCLG could not be prepared. I do not have the SQL skills to recognise the syntax problem?

Derek

[Former Member · Accepted solution]

Please run this simple query and give the results here:

SELECT Distinct T0.DocCur FROM OPOR T0

Thanks,

Gordon

[Former Member · Accepted solution]

Gordon

The result was

EUR

GBP

USD

Derek

[Former Member · Accepted solution]

The query given by Istvan should work:

If $[OPOR.DocCur\] != 'EUR'

Select 'TRUE'

Save it under one of you user query folder with any name such as POApproval. Then assign it through alert menu. Do not run it. It is not runnable under query generator.

[Former Member · Accepted solution]

Thank you Gordon - will give it a try later this morning and revert.

Derek

[Former Member · Accepted solution]

Eureka, thanks a mil for your assistance

Derek

[Former Member · Accepted solution]

Istvan

Many thanks for assisting me with sorting out my question

Regards

Derek

[former_member187989]

hi,

Check currency is defined in Bp master data ?

Deleted activity related to purchase order & then have a try.

Jeyakanthan

[Former Member]

Hi Jeyakanthan

Currency EUR is definetely defined in BP Master record

Not sure what you mean by " Deleted activity related to purchase order "

Appreciate your patience and assistance

Derek

[former_member187989]

Istvans query will work.

Jeyakanthan

[former_member187989]

r u using 2005 or 2007 version ?

Jeyakanthan

[Former Member]

Hi Jeyakanthan

Many thanks for your input in resolving my query

Regards

Derek

[former_member187989]

What is SAP B1 version & patch level ?

Jeyakanthan

[former_member187989]

hi,

What is error message ?

Jeyakanthan

[Former Member]

1.incorrect syntax near '>'.

2.statement 'Activities' (OCLG) (s) could not be prepared

Derek

[former_member187989]

Have u tested this query in demo database ?

Jeyakanthan

[Former Member]

Hi Jeyakanthan

I don't have Demo Database, merely copies of existing database for testing purposes.

Ref. the logic of the Query, should we not be trying to identify a PO where the currency is not = Euro.

I tried:

SELECT Distinct TRUE FROM OPOR T0 WHERE T0.DocCur ! <> 'EUR'

but was getting error message.

Any thoughts?

Derek

[former_member187989]

SELECT Distinct TRUE FROM OPOR T0 WHERE T0.DocCur != 'EUR'

Jeyakanthan

[Former Member]

Same problem as previous SQL. Getting approval process for all PO's.

How can I be sure that new SQL given are actually being used? They are certainly saved, And the Approval screen is definetely pointing to the same SQL

Derek

[reno1]

Hi Derek,

Try this Query,

declare @curr nvarchar (10)
SELECT @curr = case (select T0.[DocCur] FROM OPOR T0 where T0.docentry = $[opor.docentry])
when 'EUR' then (select 'EUR') 
end
if (@curr = 'EUR')
select @curr

This query will give capture if the currency of the BP is EUR in the documetn that is being added.

If it is true then you can pass this for Approval.

Set the Query based Approval procedure.

Regards

Reno

[Former Member]

Hi Reno

Thank you for coming back to me. I ran your SQL and got incorrect syntax errors pointing to "opor.docentry" and also near "if". I do not have the necessary skills to recognise what the issue is as I am very much on a Learner Plate when it comes to SQL

Thanks

Derek

[former_member187989]

sorry query will be like this

SELECT Distinct TRUE FROM OPOR T0 WHERE T0.DocCur != 'USD'

Now approval procedure will work.

Jeyakanthan

[Former Member]

thanks again, Jeyakanthan. Making progress...

ran SQL: SELECT Distinct 'TRUE' FROM OPOR T0 WHERE T0.DocCur!= 'USD' . Result was TRUE.

When I then create PO in ANY currency, approval process kicks in. I only require approval process to kick when the Curency is not equal to EURO.

Derek

BTW I am very much a beginner in SQL so please bear with me......

[former_member187989]

hi,

Query will be like this

SELECT Distinct T0.DocCur FROM OPOR T0 WHERE T0.DocCur != 'USD'

Jeyakanthan

Edited by: Jeyakanthan A on Apr 24, 2009 5:15 PM

[Former Member]

Many thanks for coming back to me.

I created the SQL suggested. The result is GBP and USD.

The Approval using this SQL does not work.

Our Base Currency is EURO.

What I am looking to create is an approval process whereby if anyone tries to create a PO for USD or GBP, the PO will need to be approved by me.

Hope you can help.

Derek