Create Approval Based on Credit Limit Condition

Sometimes, there are too much outstanding/aging A/R invoices on a company, and your monthly cash flow will be not in a good condition. On SAP Business One, you can set a credit limit to notify user when they create Sales Order document meanwhile there’s already a lot of open invoices, this could be an option to gain some opportunity to receive a payment first, so your BP Balance will be reduced, then another new Sales Order will be able to create on SAP Business One system.

First of all, you must know about the concept of credit limit on SAP Business One, please take a closer look on our previous tips here.

Also read:

• Setting Up Approval Process
• Update Your Approval Template Without Error

Open menu Administration > Approval Process > Approval Templates. There’s always an option when using a credit limit, you can choose it: use standard term, or use custom user queries.

If you choose using standard term (Option 1), there’ll be 6 options:

• Deviation from Credit Limit
• Deviation from Commitment
• Gross Profit %
• Discount %
• Deviation from Budget
• Total Document

Select terms “Deviation from Credit Limit”, on ratio drop-down menu choose “Greater Than”, and fill the value amount AUD 0.00 (zero amount), so it will be precisely activating approval phase when current DocTotal amount + Account BP Balance > Credit Limit (there’s no tolerance).

Option 2, you can use custom queries.
You must be created a query, then saved it on User Queries section (Tools > Queries > User Queries).

In case you don’t know how to save a custom query on SAP Business One:
From Tools, Open Query Generator menu, click Execute button (don’t worry if you already had an error notification, just ignore it). On Query Preview screen, copy paste that query.

Here’s a simple query  for approval based on credit limit that calculate 4 main factors: Current SO DocTotal will be added, Current BP Account Balance, Current Orders Balance (SO), Current Deliveries Balance (DO).

—SQL VERSION—

SELECT DISTINCT 'TRUE' FROM OCRD T0 WHERE T0.[CreditLine] > 1 AND T0.[CreditLine] < ($[$29.0.Number] + T0.[OrdersBal] + T0.[DNotesBal]+ T0.[Balance]) AND T0.[CardCode] = $[$4.0.0];

—HANA VERSION—

SELECT DISTINCT 'TRUE' FROM OCRD T0 WHERE T0."CreditLine" > 1 AND T0."CreditLine" < ($[$29.0.Number] + T0."OrdersBal" + T0."DNotesBal" + T0."Balance") AND T0."CardCode" = $[$4.0.0];

Remarks:

$[$29.0.Number]	ORDR.[DocTotal] $[$Field_Item_Id. Column_Id.Number] We use the NUMBER parameter: It because of that DocTotal field contains an amount and a currency key, and we only want to extract the amount.
T0.[OrdersBal]	Current Orders Balance from Open SO
T0.[DNotesBal]	Current Deliveries Balance from Open DO
T0.[Balance]	Current  Business Partner Account Balance

Just note that HANA SQL Script is are case sensitive. You will need to use double quotes (“”) for the exact case when the table name/ field name/ procedure name contains lower case. Alternatively, without the double quotes (“”) HANA SQL Engine in run-time will consider everything as UPPER CASE, it will be giving you an error “Invalid column or table name”.

At the end, don’t forget to tick Active and Active when Updating Documents Not Generated by Approval Process on Approval Templates – Setup screen.

Also read:

• What’s New in SAP Business One Version 10 – Part 2
• Setup and Customize Print Sequence
• Substitute Authorizer for Approval Process
• Deactivate Approval Template