SAP Business One Payment Terms: Setup and Credit Control with SQL - Featured Image

Publications

SAP Business One Payment Terms: Setup and Credit Control with SQL

Hector Pincheira

Hector Pincheira

CIO | CTO
Digital Strategy & Technology Innovation

items

There is one question that makes every management meeting uncomfortable: how much of your company's money is in your customers' hands right now? The answer is rarely in a report. It is scattered across verbal agreements, historical exceptions and terms someone configured five years ago that nobody ever revisited. The SAP Business One payment terms are the mechanism that translates those commercial agreements into real cash flow, and when they are misconfigured the ERP stops protecting the company and starts documenting its bleeding.

In this tutorial you will configure payment terms from scratch, assign credit limits with blocking behavior, and audit everything with SQL queries that work on both SQL Server and SAP HANA. This is not accounting theory. It is the control that separates a company that sells from a company that collects.

SAP Business One payment terms - calendar with due dates marked
Every misconfigured payment term is a due date your company never controlled.

Why payment terms define your cash flow

A payment term is not an administrative detail. It is a financial decision with consequences. When you grant a customer 60 days, you are financing their operation with your working capital. Multiply that by 300 business partners and you have the reason why many companies that are profitable on paper are drowning in cash.

The problem, therefore, is not granting credit. The problem is granting it without visibility. In most implementations I have reviewed I find three recurring pathologies: duplicate terms that say the same thing under different names, business partners with a credit limit of zero who keep buying anyway, and a silent gap between what was agreed and what is actually collected that nobody measures.

There is also an invisible opportunity cost. If your real DSO exceeds your average agreed term by twenty days, those days are a loan you made to the market without charging interest. Consequently, configuring this module properly is not data hygiene: it is treasury management.

Prerequisites

  • SAP Business One 9.3 or higher (the examples were validated on 10.0 FP 2408).
  • Authorization of superuser or permissions on Administration > Setup > Business Partners.
  • Access to the Query Manager or to SQL Server Management Studio / HANA Studio in read-only mode.
  • A test environment. Never configure payment terms directly in production without validating the impact on open documents.

Step 1: Configure payment terms in SAP Business One

Navigate to Administration > Setup > Business Partners > Payment Terms. This is where you define the master table OCTG, which is the heart of the due date calculation.

The fields that really matter

  • Payment terms code (PymntGroup): the visible name. Use a readable convention, for example 30D-FF for thirty days from invoice date. Avoid names like «Term 4».
  • Start from (StartFrom): defines the starting point of the count. It can be document date, end of month, or next month. This field causes more errors than all the others combined.
  • Additional months (ExtraMonth) and additional days (ExtraDays): the real offset from the starting point.
  • Tolerance days: margin before the system considers the document overdue.
  • Credit limit and Commitment limit: default values inherited when you create a business partner with this term.
  • Default payment method: links the term to the payment wizard.

For example, if you set Start from = End of month with ExtraDays = 30, an invoice dated July 2 will fall due on August 31: almost 60 days of real credit, not 30. However, your sales team will believe it sold on 30-day terms. That difference in interpretation is what later shows up as a treasury variance.

Step 2: Assign credit limits and blocking behavior

A credit limit without blocking behavior is decorative. First, go to Administration > System Initialization > General Settings > BP tab and define what should happen when a customer exceeds their limit: warning, block, or nothing. «Nothing» is the option you will find active by default in too many companies.

Next, on each business partner (BP Master Data > Payment Terms tab) define:

  • Credit limit (CreditLine): total accepted exposure cap.
  • Commitment limit (DebtLine): includes orders and deliveries not yet invoiced. This is the number that really matters.
  • Late payment interest and grace days.
  • Collection priority, if you use the collections management module.

However, the trap is in the detail: SAP Business One evaluates exposure by adding the account balance (Balance), open orders (OrdersBal) and uninvoiced deliveries (DNotesBal). If you only look at the accounting balance, you are seeing less than half the risk.

SAP Business One payment terms - agenda for due date and collections control
The commitment limit, not the accounting balance, is the real measure of your exposure.

Step 3: X-ray query — what payment terms you actually have

Before optimizing, measure. This query shows every configured payment term, its calculation logic and how many business partners use it. As a result, you will spot duplicates and orphan terms in under a minute.

SQL Server

-- Radiografia de condiciones de pago y su uso real
SELECT
    T0.GroupNum                      AS [Codigo],
    T0.PymntGroup                    AS [Condicion],
    CASE T0.StartFrom
         WHEN 0 THEN 'Fecha documento'
         WHEN 1 THEN 'Fin de mes'
         WHEN 2 THEN 'Mes siguiente'
         WHEN 3 THEN 'Medio mes'
         ELSE 'Otro'
    END                              AS [Inicio conteo],
    T0.ExtraMonth                    AS [Meses extra],
    T0.ExtraDays                     AS [Dias extra],
    (T0.ExtraMonth * 30) + T0.ExtraDays AS [Dias credito aprox],
    ISNULL(T0.CredLine, 0)           AS [Limite credito default],
    COUNT(T1.CardCode)               AS [Socios asignados]
FROM OCTG T0
LEFT JOIN OCRD T1 ON T1.GroupNum = T0.GroupNum AND T1.CardType = 'C'
GROUP BY T0.GroupNum, T0.PymntGroup, T0.StartFrom,
         T0.ExtraMonth, T0.ExtraDays, T0.CredLine
ORDER BY [Socios asignados] DESC;

SAP HANA

-- Radiografia de condiciones de pago y su uso real
SELECT
    T0."GroupNum"                    AS "Codigo",
    T0."PymntGroup"                  AS "Condicion",
    CASE T0."StartFrom"
         WHEN 0 THEN 'Fecha documento'
         WHEN 1 THEN 'Fin de mes'
         WHEN 2 THEN 'Mes siguiente'
         WHEN 3 THEN 'Medio mes'
         ELSE 'Otro'
    END                              AS "Inicio conteo",
    T0."ExtraMonth"                  AS "Meses extra",
    T0."ExtraDays"                   AS "Dias extra",
    (T0."ExtraMonth" * 30) + T0."ExtraDays" AS "Dias credito aprox",
    IFNULL(T0."CredLine", 0)         AS "Limite credito default",
    COUNT(T1."CardCode")             AS "Socios asignados"
FROM OCTG T0
LEFT JOIN OCRD T1 ON T1."GroupNum" = T0."GroupNum" AND T1."CardType" = 'C'
GROUP BY T0."GroupNum", T0."PymntGroup", T0."StartFrom",
         T0."ExtraMonth", T0."ExtraDays", T0."CredLine"
ORDER BY "Socios asignados" DESC;

If the result shows twelve terms and only four have partners assigned, you have already found your first cleanup task. Also, if two different terms produce the same credit days, consolidate them: every duplicate is a source of inconsistent reports.

Step 4: Real credit exposure query by customer

This is the query that should be on your sales manager's desktop. It does not measure debt: it measures committed risk, adding balance, open orders and deliveries pending invoicing.

SQL Server

-- Exposicion crediticia real vs limite autorizado
SELECT
    T0.CardCode                                   AS [Codigo SN],
    T0.CardName                                   AS [Cliente],
    T1.PymntGroup                                 AS [Condicion pago],
    T0.CreditLine                                 AS [Limite credito],
    T0.DebtLine                                   AS [Limite compromiso],
    T0.Balance                                    AS [Saldo deudor],
    T0.OrdersBal                                  AS [Pedidos abiertos],
    T0.DNotesBal                                  AS [Entregas sin facturar],
    (T0.Balance + T0.OrdersBal + T0.DNotesBal)    AS [Exposicion total],
    (T0.CreditLine - (T0.Balance + T0.OrdersBal + T0.DNotesBal)) AS [Cupo disponible],
    CASE
        WHEN T0.CreditLine = 0 THEN 'SIN LIMITE DEFINIDO'
        WHEN (T0.Balance + T0.OrdersBal + T0.DNotesBal) > T0.CreditLine THEN 'EXCEDIDO'
        WHEN (T0.Balance + T0.OrdersBal + T0.DNotesBal) > (T0.CreditLine * 0.85) THEN 'ALERTA'
        ELSE 'OK'
    END                                           AS [Estado],
    CASE T0.frozenFor WHEN 'Y' THEN 'Bloqueado' ELSE 'Activo' END AS [Situacion]
FROM OCRD T0
LEFT JOIN OCTG T1 ON T1.GroupNum = T0.GroupNum
WHERE T0.CardType = 'C'
  AND (T0.Balance + T0.OrdersBal + T0.DNotesBal) > 0
ORDER BY [Exposicion total] DESC;

SAP HANA

-- Exposicion crediticia real vs limite autorizado
SELECT
    T0."CardCode"                                 AS "Codigo SN",
    T0."CardName"                                 AS "Cliente",
    T1."PymntGroup"                               AS "Condicion pago",
    T0."CreditLine"                               AS "Limite credito",
    T0."DebtLine"                                 AS "Limite compromiso",
    T0."Balance"                                  AS "Saldo deudor",
    T0."OrdersBal"                                AS "Pedidos abiertos",
    T0."DNotesBal"                                AS "Entregas sin facturar",
    (T0."Balance" + T0."OrdersBal" + T0."DNotesBal") AS "Exposicion total",
    (T0."CreditLine" - (T0."Balance" + T0."OrdersBal" + T0."DNotesBal")) AS "Cupo disponible",
    CASE
        WHEN T0."CreditLine" = 0 THEN 'SIN LIMITE DEFINIDO'
        WHEN (T0."Balance" + T0."OrdersBal" + T0."DNotesBal") > T0."CreditLine" THEN 'EXCEDIDO'
        WHEN (T0."Balance" + T0."OrdersBal" + T0."DNotesBal") > (T0."CreditLine" * 0.85) THEN 'ALERTA'
        ELSE 'OK'
    END                                           AS "Estado",
    CASE T0."frozenFor" WHEN 'Y' THEN 'Bloqueado' ELSE 'Activo' END AS "Situacion"
FROM OCRD T0
LEFT JOIN OCTG T1 ON T1."GroupNum" = T0."GroupNum"
WHERE T0."CardType" = 'C'
  AND (T0."Balance" + T0."OrdersBal" + T0."DNotesBal") > 0
ORDER BY "Exposicion total" DESC;

This gives you three actionable signals: who is over the limit, who operates with no limit defined, and how much real headroom is left before committing more capital.

Step 5: Gap query — agreed terms versus what is actually collected

Here is the indicator almost nobody measures. It compares the credit days you defined in the term against the days the customer actually took to pay. It therefore stops being a discussion of opinions and becomes data.

SQL Server

-- Brecha entre condicion pactada y comportamiento real de pago
SELECT
    T0.CardCode                                    AS [Codigo SN],
    T0.CardName                                    AS [Cliente],
    T4.PymntGroup                                  AS [Condicion pactada],
    (T4.ExtraMonth * 30) + T4.ExtraDays            AS [Dias pactados],
    COUNT(T1.DocEntry)                             AS [Facturas pagadas],
    AVG(DATEDIFF(DAY, T1.DocDate, T3.DocDate))     AS [Dias reales promedio],
    AVG(DATEDIFF(DAY, T1.DocDate, T3.DocDate))
        - ((T4.ExtraMonth * 30) + T4.ExtraDays)    AS [Desviacion dias],
    SUM(T2.SumApplied)                             AS [Monto cobrado]
FROM OCRD T0
INNER JOIN OINV T1 ON T1.CardCode = T0.CardCode
INNER JOIN RCT2 T2 ON T2.DocEntry = T1.DocEntry AND T2.InvType = 13
INNER JOIN ORCT T3 ON T3.DocEntry = T2.DocNum AND T3.Canceled = 'N'
INNER JOIN OCTG T4 ON T4.GroupNum = T0.GroupNum
WHERE T0.CardType = 'C'
  AND T1.DocDate >= DATEADD(MONTH, -12, GETDATE())
GROUP BY T0.CardCode, T0.CardName, T4.PymntGroup, T4.ExtraMonth, T4.ExtraDays
HAVING COUNT(T1.DocEntry) >= 3
ORDER BY [Desviacion dias] DESC;

SAP HANA

-- Brecha entre condicion pactada y comportamiento real de pago
SELECT
    T0."CardCode"                                  AS "Codigo SN",
    T0."CardName"                                  AS "Cliente",
    T4."PymntGroup"                                AS "Condicion pactada",
    (T4."ExtraMonth" * 30) + T4."ExtraDays"        AS "Dias pactados",
    COUNT(T1."DocEntry")                           AS "Facturas pagadas",
    AVG(DAYS_BETWEEN(T1."DocDate", T3."DocDate"))  AS "Dias reales promedio",
    AVG(DAYS_BETWEEN(T1."DocDate", T3."DocDate"))
        - ((T4."ExtraMonth" * 30) + T4."ExtraDays") AS "Desviacion dias",
    SUM(T2."SumApplied")                           AS "Monto cobrado"
FROM OCRD T0
INNER JOIN OINV T1 ON T1."CardCode" = T0."CardCode"
INNER JOIN RCT2 T2 ON T2."DocEntry" = T1."DocEntry" AND T2."InvType" = 13
INNER JOIN ORCT T3 ON T3."DocEntry" = T2."DocNum" AND T3."Canceled" = 'N'
INNER JOIN OCTG T4 ON T4."GroupNum" = T0."GroupNum"
WHERE T0."CardType" = 'C'
  AND T1."DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)
GROUP BY T0."CardCode", T0."CardName", T4."PymntGroup", T4."ExtraMonth", T4."ExtraDays"
HAVING COUNT(T1."DocEntry") >= 3
ORDER BY "Desviacion dias" DESC;

A positive variance of 25 days on a 400 million portfolio means you financed the market with your own capital for almost a month. However, that cost never appears as a line in the income statement. It appears as cash pressure at month end.

SAP Business One payment terms - credit risk and financial exposure analysis
Measuring the gap between what was agreed and what was collected turns collections into management, not nagging.

Variations and use cases

Suppliers. swap CardType = 'C' by 'S' and replace OINV/RCT2/ORCT by OPCH/VPM2/OVPM. This way you measure whether your company is paying earlier than necessary, which is as costly as collecting late.

Segmentation by salesperson. add OSLP using T0.SlpCode to identify which sales rep holds the slowest portfolio. As a result, the incentive conversation stops being based on sales volume alone.

Automatic alert. Turn the exposure query into a native SAP Business One alert, running daily and addressed to Finance. Consequently, control no longer depends on someone remembering to run a report.

Formatted search on the sales order. Adapt the available headroom calculation so it appears while the order is being entered. For example, the salesperson sees the risk before committing the sale, not after.

Best practices and common mistakes

  • Never use «End of month» without explaining it to the sales team. It is the main source of wrong collection expectations.
  • Do not leave CreditLine at zero thinking it means «no credit». For SAP Business One it means «no control».
  • Consolidate duplicate terms before building any BI dashboard. A report on dirty master data only distributes the error faster.
  • Enable blocking on exceeded limit in general settings. A warning without a block is ignored by the third week.
  • Review DebtLine, not just CreditLine. Risk lives in open orders, not in issued invoices.
  • Document the exceptions. If a customer has a term different from their segment, there should be a user field with the justification and the approver.
  • Validate in test. Modifying StartFrom on an active term does not recalculate existing documents, but it does change every future one.

Official SAP resources

Conclusion: the ERP does not collect, you do

Properly configuring SAP Business One payment terms does not make your customers pay sooner. It does something more valuable: it takes away your excuses. When every term is documented, every limit is active and every variance is measurable, the conversation shifts from «customers are slow» to «these four customers account for 60% of our cash gap and this is the decision we are going to make».

Run the credit exposure query in your environment today. If more than 20% of your active partners show up with «NO LIMIT DEFINED», you have just found the most profitable work of your week. It needs no new licenses and no six-month project. It needs a decision that credit control is a management function, not a field in the business partner master.

Most Read

QlikSense Hierarchy: parent-child chart of accounts expanded by levels
QlikSense Hierarchy: the Chart of Accounts That Doesn't Add Up
QlikSense Sum TOTAL: share of each family over the total
QlikSense Sum TOTAL: the Percentage That Changes When You Filter
SAP Business One Inventory Revaluation - featured image of the tutorial
SAP Business One Inventory Revaluation: Fix Wrongly Loaded Costs
Empty chamber with wooden benches: the counterweight that required being present to be used
The Veto You Had to Say in Person: Your Decisions With No Counterweight
SAP Business One customizations - featured image of the pre-upgrade audit tutorial
SAP Business One Customizations: Pre-Upgrade Audit
SAP Business One Customer Concentration - featured image of the tutorial
SAP Business One Customer Concentration: Measure Your Risk with SQL

RELATED ARTICLES