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 .
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).
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];
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];
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.