Purchase Approval

[Former Member]

2007B

want to trigger an approval for purchase orders higher than 25000 rupees.

I need the query for this approval

[Former Member · Accepted solution]

selamat pagi,

you don't ne a query. take a look under "approval procedures" -> "approval templates". In the tab "Terms" is displayed "Total Document". This means the DocTotal. There you can create your approval procedure.

[Former Member · Accepted solution]

Thank you.

In my case we need PO approval based on Document series

ex:

Raw material has a document series.

spares has a document series

raw material purchased above 25000 should trigger approval by certain users

spares purchased above 25000 should trigger approval by certain users

[Former Member · Accepted solution]

hi again,

if i understand correct the approval procedure, teh procedure does not make any sense. Cause approval procedures are only working in the first line. eg. you create your purchase and the approval will popup after you entered the first line.

regards

[Former Member · Accepted solution]

Approval works on document level and query based query can even go to the first row in the document. Since this approval is based on document series, only query based approval can be fit in.

I have posted an example on your previous thread, please let me know if you have tried that query or not.

Thanks,

Gordon

[former_member186095 · Accepted solution]

HI,

B1 standard approval based on all document series. So, for your issue, just use query approval (in the approval procedure template, there is a query matrix where you put it there). Try to use Gordon's query but if not succeed, you could post here the error message.

you could also try this query approval:

for spares:

select distinct 'true' from OPOR t0 where 
$[OPOR.series] = '..' 
and $[OPOR.doctotal] > '25000' -- 
fill in the blanks the series code for spares

for raw material:

select distinct 'true' from OPOR t0 where 
$[OPOR.series] = '...'
and $[OPOR.doctotal] > '25000' -- 
fill in the blanks the series code for raw material

You have to create to approval templates and optional for the stage. I mean for the approval stage, you could create 1 stage or 2 stage depends on how many authorizers to approve.

Rgds,

[former_member583013 · Accepted solution]

Vijay,

Ok, if you are talking about Document Series based approval..are you saying that the Document series for Raw Material will only contain Items which are Raw Materials. Hopefully this is the case ?

Let me know the Series Number for each of them..this is needed for the Query.

You should be able to select the Series on the PO and when you mouse over them..you should see the number on the Status Bar. (Enable View System Information from View Menu)

Your approval query would be like this..please replace the {Series Number for Raw Materials} and {Series Number for Spares} with the actual series number

SELECT 'TRUE' WHERE $\[$88.0.0] = {Series Number for Raw Materials}

SELECT 'TRUE' WHERE $\[$88.0.0] = {Series Number for Spares}

Suda

[Former Member · Accepted solution]

Tried the query. Its gives an error ( red bar below) and cudnt execute PO

[Former Member · Accepted solution]

Tried the query. replaced it with my document series code. Gives an error and cudnt execute PO.

Not able to delete the Approval template.

[Former Member · Accepted solution]

tried it. gives an error and cudnt complete PO

[former_member583013 · Accepted solution]

What did you try...I pasted the code only after testing and it work just fine.

PLease explain the steps you following and also the SQL you are using in the Approval procedure and what is the exact problem

[Former Member · Accepted solution]

followed the steps mentioned below:

1. copied the query "SELECT 'TRUE' WHERE $[$88.0.0] = " in the query generator and saved it to user queries. "RawMate" is the name of the document series.

2. when exuted the query, it gives the following message: " [Microsoft] [Sql Native Client]Syntax error, Permission Violation,or other nonspecific error.

3. created approval stage

4. created approval template using the above query.

5. result: Its not allowing to create PO and not able to disable the approval template.

vijay

Edited by: SU Vijay on Oct 6, 2008 9:25 AM

[Former Member · Accepted solution]

This query will create error if you run under query manager. It only works like FMS query. You can verify it when you open PO form. Query is runnable when you open it from user query and with PO window open.

[Former Member · Accepted solution]

Hi,

What is FMS ?

its gives an error (the red bar below) when trying to add PO.

the error is "could not commit transaction " (ADOC) "

vijay

[former_member583013 · Accepted solution]

Vijay,

I am not sure if you have { } in your query. This is not required. You query should just be as below

SELECT 'TRUE' WHERE $[$88.0.0] = 'RawMate'

Let me know if this change it done makes any difference?

[Former Member · Accepted solution]

Suda,

I didi the changes, { } to ' '.

Now it did not give any errors.

But, the PO gets added. No approval is sent to the assigned user in the approval stages.

vijay

[former_member583013 · Accepted solution]

Vijay,

You cannot use your Series Name in the WHERE clause, you need to use the Series Number. When you mouse over the Series you will see the number in the Status Bar (enable System information)

It should be some thing like

SELECT 'TRUE' WHERE $[$88.0.0] = 12

You could also Run this Query to find the Series Number

SELECT T0.Series AS 'Series', T0.SeriesName AS 'Series Name' FROM  [dbo].[NNM1] T0
WHERE  T0.ObjectCode = 22

Use the corresponding number of your Series

[Former Member · Accepted solution]

Suda,

cheers! it works! Thanks

As I mentioned earlier, also I need to include limitations of amount in the query.

eg:

1. upto 25,000 INR

2. above 25,000 to 1,00,000 INR

3. above 1,00,000 to 1,00,00,000 INR

Where is that to be defined in the query?

vijay

[former_member583013 · Accepted solution]

You could click on the Document Total check box in the Terms tab and enter your Total numbers.

When you have these range scenario's you will need 3 seperate Approval templates

The amount can also be embedded into your Query..like this

For using the Amount shown on Total Before Discount you will use

SELECT 'TRUE' WHERE $[$88.0.0] = 12 AND $[$22.0.Number] <= 25000 

For checking the DocTotal

SELECT 'TRUE' WHERE $[$88.0.0] = 12 AND $[$29.0.Number] <= 25000 

You will need 3 queries for each of the Amount ranges which will be linked to 3 seperate Approval Templates

[Former Member]

Suda,

cheers! it works! thanks

[Former Member]

The approval of PO is w.r.t to 'document series' and 'amount'

eg:

1. Approval of PO,

Document Series: RawMate

Amount: upto 25,000

2. Approval of PO,

Document Series: RawMate

Amount: above 25,000 to 1,00,000

vijay