Approval Procedure not working randomly on sales orders

[Former Member]

Hi Experts,

We place approval procedure for 2 users in SAP B1 2007B pl15.

Some SO does not pass approval procedure and we cannot trace why. The are 2 set up for approval procedure 1 for within on below credit limit. The other one beyond credit limit.

Does anyone have the same problem with Approval procedure?

What do I need to check to isolate the problem?

Regards,

Amy

[msundararaja_perumal]

Hi,

I think you are dealing with Multicurrency,try to include the currency also in the approval query's condition,

Now check whether it is triggering accordingly.

[Former Member]

Hi M. Sundararaja ,

Does it help to say all Customer is using the local currency only?

Please recommend how to construct the query if we are to consider the multi currency set up.

Regards,

Amy

[Former Member]

The error is because of the two queries' logic. Whenever a draft submitted, 2 contrary checking must be processed in the same time.

The first query might be changed from < limit to a range from a small value to the limit.

[Former Member]

Hi Gordon,

We tried to disable the SO query beyond credit limit in a test database to check if the approval procedure will run if there are no other queries checking the credit limit. Now we only have one query running- the SO within credit limit.

We eliminated another possibility for the error. The error occurs for a newly created BP. The approval procedure will not work in SO is the first SO ever created in the system. All subsequent SO will go thru the approval procedure even if the SO, DN and Account balance is zero

1)We also tried this query and diabled the SO Beyond Credit Limit

SELECT DISTINCT 'TRUE' from DBO.ORDR T0 INNER JOIN DBO.OCRD T1 ON T0.CardCode=T1.CardCode Where T0.CardCode=$[$4.0.string] and T1.CreditLine >=(

(case when T1.Balance = 0.00 then 0.00 else T1.Balance end) +

(case when T1.OrdersBal = 0.00 then 0.00 else T1.OrdersBal end) +

(case when T1.DNotesBal = 0.00 then 0.00 else T1.DNotesBal end) +

+$[$29.0.number])

2) We also tried the Deviation from credlit limit is less than .01. Still error.

Is this a system error or error in the query?.

Regards,

Amy

[Former Member]

I don't understand your logic to check those 3 balances. However, technically, you may try this simplified query:

SELECT DISTINCT 'TRUE' from DBO.OCRD T1 Where T0.CardCode=$[$4.0.0\] and

T1.CreditLine >= ISNULL(T1.Balance, 0) + ISNULL(T1.OrdersBal,0) +

ISNULL(T1.DNotesBal,0) + $[$29.0.number\]

[Former Member]

Hi Gordon,

There is an error when I use your query

Error message include "The isnull function requires 2 argument"

Regards,

Amy

[Former Member]

Hi Gordon,

I also tried the query below. The approval procedure is now working for new customer upon testing. We will still observe if the query below will really solve the problem.

SELECT DISTINCT 'TRUE' from DBO.OCRD T1 Where T1.CardCode=$[$4.0.0] and T1.CreditLine < ISNULL(T1.Balance, 0) + ISNULL(T1.OrdersBal,0) +

ISNULL(T1.DNotesBal,0) + $[$29.0.number]

Thank you!

Regards,

Amy

Edited by: Amy D. Ricohermoso on Feb 23, 2010 6:20 AM

[Former Member]

You may post your queries here for others to check.

[Former Member]

Hi Gordon

Here are teh 2 query for the SO Approval Procedure

Credit limit check beyond credit limit

SELECT DISTINCT 'TRUE' from DBO.ORDR T0 INNER JOIN DBO.OCRD T1 ON T0.CardCode=T1.CardCode Where T0.CardCode=$[$4.0.string] and T1.CreditLine<(T1.BalanceT1.OrdersBalT1.DNotesBal+$[$29.0.number])

and

Credit limit check within credit limit

SELECT DISTINCT 'TRUE' from DBO.ORDR T0 INNER JOIN DBO.OCRD T1 ON T0.CardCode=T1.CardCode Where T0.CardCode=$[$4.0.string] and T1.CreditLine >=(T1.BalanceT1.OrdersBalT1.DNotesBal+$[$29.0.number])

Regards,

Amy

[Former Member]

Hi!

Approval Procedure - User Query will not work as expected for Line Items.

Try to Check the Credit Limit based on the BP Account Balance.

[Former Member]

Hi Thanga Raj.K.

There is no line item in my query, the query is getting data from sales Order Header (ORDR ) and Business partner (OCRD).

Line item table for SO is RDR1 which is not icnldued in the query

I cannot use account balance since we also need the open DR and SO.

Regards,

Amy

[Former Member]

Hi!

Can you please tell me in which Document you are firing the Approval Procedure.

According to your Query, Approval will be triggerd only for those BP for whom you have entered Sales Order.

Can you pl check the document which is not going fr approval has SO or not.

Edited by: Thanga Raj.K on Jan 25, 2010 11:19 AM

[Former Member]

HI

This is fpr SO. Most of the time the approval is triggered everytime we add SO. 175 out of 3191 is the total number of SO doc where the approval procedure is not trigerred I we dont know why.

Regards,

Amy

[Former Member]

Hi!

Did all those 175 BP has defined Credit Limit in BP Master ?

[Former Member]

Hi Amy,

Try this,

Credit limit check beyond credit limit

SELECT DISTINCT 'TRUE' FROM ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode 
Where T0.CardCode=$[4.0.0] 
AND 
T1.CreditLine<(SELECT $[$29.0.Number]+T1.Balance+T1.OrdersBal+T1.DNotesBal)

Credit limit check within credit limit

SELECT DISTINCT 'TRUE' FROM ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode 
WHERE T0.CardCode=$[$4.0.0] 
AND 
T1.CreditLine >=(SELECT $[$29.0.Number]+T1.Balance+T1.OrdersBal+T1.DNotesBal)

[Former Member]

Since you use two conditions against each other in the same time, that would confuse system to produce any meaningful results. What is the purpose of the 2nd query? If it is within limit, you should not set any approvals. Otherwise, why you need that limit in the 1st space?

[Former Member]

HI,

Please dont question the requirement. This is still an issue. Anyway the reason for 2 separate query is that the approver for SO within the credit limit is up to supervisor level only while approval for beyond credit limit will go to another set of approver up to VP for finance.

We are still testing the query you have provided since as I've said this is still a random issue. We still cannot isolate the cause of the error.

Thank you for the query.

Regards,

Amy

[Former Member]

The same requirement could be solved by different means. I never say this requirement is not realistic.

How about by different stage approvals?

[Former Member]

Hi Gordon,

Since we have 2 separate query we set up 2 separate approval template for each query. Below is the current set up.

1)Within credit Limit = 2 approver/stages

originator

Approver1

Approver2

2)Beyond credit limit 3 approver/stages

originator

Approver1

Approver2

Approver3

I did not undertand why did you mention the different stages since the different stages is the only way to meet our requirement. Is there another way uing SAP B1 approval template?

We did not think this is an error on the approval template set up. Right now we are concentrating on the 2 query.

Regards,

Amy

[Former Member]

The logic would become an issue whenever the concurrent Sales Orders trigger two mutual exclusive conditions.

[Former Member]

Hi Gordon,

We have 2 different approval templates for each query and each template has stages of approval just like below

Beyond credit limit approval

Stage 1 Supervisor1

Stage 2 manager1

Stage 3 CFO

Within credit limit approval

Stage 1 Supervisor1

Stage 2 manager1

Based on this set up please clarify your suggestion.

Regards,

Amy

[Former Member]

Try this:

One approval as Always that no need query for

Stage 1 Supervisor1

Stage 2 manager1

One approval based on query for over limit for

Stage 1 Supervisor1

Stage 2 manager1

Stage 3 CFO

[Former Member]

Hi Gordon,

Your last suggestion will trigger 2 set of appproval procedure for document created beyond credit limit. Meaning the supervisor and manager will have to approved the document twice.

Regards,

Amy

[Former Member]

In the 2nd, you only need CFO then.

[Former Member]

Hi Gordon,

If we will set up the approval this way then the CFO will have to check manually if manager has approved the document before he can also approve the SO. The 3 stages of approval is a requirement. The CFO cannot approve without prior approval of the manager.

Back to the issue, is the error cause by the wrong query?

Regards,

Amy

[Former Member]

Have you checked the B1Log files on the client to see if anything is recorded? You can increase the logging levels.

You may find there are issues with table locks if there are many users?

[former_member583013]

When you say "The are 2 set up for approval procedure 1 for within on below credit limit. The other one beyond credit limit." it seems that every document would have approval's required.

Have you used you own query to determine the credit limit deviations or are you using the parameters which come with the system?

Suda Sampath

[Former Member]

We created 2 queries instead of suign thye SAP credit limit check

After user added the SO without the approval procedure I immediate chack the query if one of this query will check the SO and it seems ok

Regards,

Amy

[Former Member]

Hi Amy,

Do you mean you have selected standard Deviation from Credit Limit term for two approval templates? If it is true, what is your exact Ratio selected?

Thanks,

Gordon