SAP B1 Approval Procedure

[former_member366557]

Hi Sir/Ma'am,

I'am trying to create a simple query for my Approval Procedure in SAP B1

,i want to execute approval procedure  if  OCRD.creditline is less than OCRD.Balance , my problem is everytime i create a sales order it will asking me for approval even creditline is greater than balance, i am sorry i am new of  creating script/s,  please see my script below for your assistance:

SELECT distinct 'true' FROM OCRD T0  INNER JOIN ORDR T1 ON T0.CardCode = T1.CardCode WHERE T0.[CreditLine] <  T0.[Balance]

Regards,

silentknight

[former_member366557 · Accepted solution]

hi experts,

can someone help me on this? thank you very much..

[MukeshSingh · Accepted solution]

Hi Silent,

Try this

SELECT distinct 'true' FROM OCRD T0  INNER JOIN ORDR T1 ON T0.CardCode = T1.CardCode WHERE T0.[CreditLine] < T0.[Balance] and t0.cardcode =t1.$[$4.0.0]

Regards

Mukesh

[former_member366557 · Accepted solution]

hi mukesh,

i tried the script but it returns me error: please see screenshot for your reference.. thank you for your response..

[former_member366557 · Accepted solution]

[Former Member · Accepted solution]

Dear Silent Knight,

If I setup Approval Procedure with below query on Sales Order Level, it works.

SELECT distinct 'true' FROM OCRD T0  INNER JOIN ORDR T1 ON T0.CardCode = T1.CardCode WHERE T0.[CreditLine] <  T0.[Balance] and T0.CardCode=$[$4.0.0]

This query will not directly run in query generator as there is another way of checking the output.

Please make sure that you have done below settings for Originator and Sales Order document shown below.

1. Approval Stage

  2. Approval Template-Originator Tab

3. Approval Template-Document Tab

4. Approval Template-Stages Tab

5. Approval Template-Terms Tab

Once you do all the settings and apply query to the template. Open Sales Order and select customer whose CreditLine<Balance and keep the Sales Order open and Go to Tools-->Queries-->User Queries--> and click on the approval query that you saved (Query shown in screen shot in my test case) and once it returns True in query output then it will go for approval.

Hope this will help you to identify where what goes wrong.

Thanks,

Rahul

[former_member366557 · Accepted solution]

hi rahul,

thank you for your response,  I already did the step 1 to 5. except  your instruction in checking query.

Please see image below result for your reference:

after clicking the ok button please see image below:

Please help sir..

thank you

Best Regards

silentknight

[former_member366557 · Accepted solution]

HI SIR RAHUL,

I AM SO SORRY , I DOUBLE CHECK EVERYTHING ALL THE STEPS THAT YOU SENT  AND ITS WORKING ALREADY.. IAM SORRY SIR..

MY APOLOGY..

REGARDS,

silentknight

[Former Member]

Hi,

Looks like the Approval Query is not complete and make sure that you have selected Sales Order under Documents Tab of Approval template.

Please use the below query

SELECT distinct 'true' FROM OCRD T0  INNER JOIN ORDR T1 ON T0.CardCode = T1.CardCode WHERE T0.[CreditLine] <  T0.[Balance] and T0.CardCode=$[$4.0.0]

Thanks,

Rahul

[former_member366557]

hi sir,

thank you for your response, and i already used your script but still the user can added the sales order even the creditline is less than balance..

thank you.

regards

silentknight

[former_member366557]

sir in addition please see image below their a n error when executing the script:

[former_member366557]

by the way sir i tried also this but still user can add the sales order even the creditline is less than balance.

SELECT distinct 'true' FROM OCRD T0  INNER JOIN ORDR T1 ON T0.CardCode = T1.CardCode WHERE T0.[CreditLine] < ( T0.[Balance] ) and t0.cardcode =t1.cardcode

regards

silentknight

[kothandaraman_nagarajan]

Hi,

Your query is not linked with sales order and its validating only OCRD balance, not with sales order document total.

Thanks,

[former_member366557]

sir what is the best cript to link this on sales order?

Thank you