Query for credit limit of customers of a particular type

[former_member485915]

Hi,

We are using SAP B1 2007B PL04 and want to activate credit limit so that whenever a delivery or A/R breaches the credit limit then the document must go for approval. However we want this to be activated only when the customers are having group code say 110 and not for others.

So basically could you all help me with a query which would do the following

1. check whether customer for whom delivery or A/R is being generated belongs to group having croup code 110

2. check if the credit limit set in BP master for that customer will be breached if the delivery or A/R is made (ie Current A/R or delivery amount when added to current outstanding will exceed the credit limit)

3. if yes then will activate approval procedure and send the document for approval as per approval template

Thanks

regards

Pradipta

[Former Member · Accepted solution]

Hi Pradipta,

I believe approval for delivery is more useful because it might be too late if you already delivered.

Try this:

SELECT Distinct 'true' FROM ODLN T0

INNER JOIN OCRD T1 ON T1.CardCode=T0.CardCode AND T1.GroupCode = '110'

WHERE T1.CreditLine - T1.Balance < $[$29.0.number\]

Thanks,

Gordon

[former_member485915 · Accepted solution]

hi Gordon

Thanks for the reply

could you please tell me what does this $[$29.0.number]

signify and

secondly can i add WHERE T1.CreditLine - T1.Balance < $[$29.0.number]

and T1.[GroupCode]=122

as i want this query to only run when the customer belong to a particular group

Also please tell me how do i test this query from query manager

Thanks

Regards

Pradipta

[Former Member · Accepted solution]

Your request is 110 in your first posting. You can replace it to any code in my updated query.

$[$29.0.number\] means the current document total that needs to be checked if it is subject to approval.

[former_member485915 · Accepted solution]

Dear Gordon,

the query is working in general but it is being applied to all customers and not specifically to those in group 122

please help

regards

Pradipta

[Former Member · Accepted solution]

Sorry. One more condition needed:

SELECT Distinct 'true' FROM ODLN T0

INNER JOIN OCRD T1 ON T1.CardCode=T0.CardCode AND T1.GroupCode = '110'

WHERE T1.CreditLine - T1.Balance < $[$29.0.number\] AND T1.CardCode = $[$4.0.0\]