Query for Credit Limit Approval Procedure

[Former Member]

Hi experts,

Need your help with an approval procedure query. There is already the option to launch when the BP's balance exceeds the credit limit by a set dollar amount. What we would like is for the procedure to launch when the balance exceeds an additional 10% of their credit limit.

Ex: Customer's credit limit = $1,000. We want the approval procedure to kick in when the sales document causes their balance to be >= $1,100. Thanks.

[Former Member]

Try this one:

SELECT distinct 'true' FROM OCRD T0 WHERE T0.CardCode = $[OINV.CardCode] and T0.[Balance]  >= T0.[CreditLine] * 1.1-$[$39.0.number]

Thanks,

Gordon

[Former Member]

Hi Allen,

I think the right query should be this:

SELECT distinct 'true' FROM OCRD T0 WHERE T0.CardCode = $[OINV.CardCode] and T0.[Balance]  >= T0.[CreditLine] * 1.1

Thanks,

Gordon

[Former Member]

Hi Gordon,

I believe this query will use the BP's current AR balance. How can I take into account the doc total of the document at hand if it were added? For example: A BP's credit limit is $1,000 and current AR balance is $950. Based on this query they are still technically under the credit limit and the system would allow a new invoice to be added (say, for $10,000). Of course, this would create a credit limit problem.

So in this case, we want the approval procedure to be activated when:

(current balance + total amt of doc to be added) >= (credit limit * 1.1)

How can we revise the script? Thanks.

[former_member583013]

Adding this to the WHERE clause will include the DocTotal

T0.Balance + $[$29.0.Number]

SELECT distinct 'true' FROM OCRD T0 WHERE T0.CardCode = $[OINV.CardCode] AND
(T0.Balance + $[$29.0.Number]) >= T0.[CreditLine] * 1.1

[former_member583013]

Allen,

Would you want to consider delivery balances (deliveries not invoiced) and open sales order balances? Or just the AR balance?

Suda

[Former Member]

Hi Suda,

Just AR balance in our case.

[Former Member]

Create a Query based approval procedure for the following query

SELECT distinct 'true' FROM OCRD T0   WHERE T0.CardCode = $[OINV.CardCode] and  T0.[Balance]  >= T0.[CreditLine] + (T0.[CreditLine] * (10/100))

Note: This works on AR Invoice Screen.