# SAP Business One AR Aging Report Query Generator

> Source: https://netray.co/tools/sap-b1-ar-aging-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate a customer receivables aging query for SAP Business One Query Manager, with your own bucket days and aging basis, in SQL Server and HANA syntax.

## Category

developer

## Erp

SAP Business One

## Audience

- SAP B1 consultant
- B1 power user
- Accounts receivable lead

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Aging date basis

**Key:** basis

**Type:** select

**Options:**

- DueDate
- RefDate

**Default:** DueDate

**Help:** DueDate = due date, RefDate = posting date of the journal line.

#### Bucket 1 upper limit (days)

**Key:** b1

**Type:** number

**Default:** 30

#### Bucket 2 upper limit (days)

**Key:** b2

**Type:** number

**Default:** 60

#### Bucket 3 upper limit (days)

**Key:** b3

**Type:** number

**Default:** 90

#### Customer group code (optional filter)

**Key:** group

**Type:** text

**Default:** 100

**Blocks:**

#### 1. SQL Server: aging from open journal items (JDT1)

**Body:** SELECT
  T0.ShortName AS CardCode,
  T1.CardName,
  SUM(T0.BalDueDeb - T0.BalDueCred) AS Balance,
  SUM(CASE WHEN DATEDIFF(day, T0.{{basis}}, GETDATE()) < 0
           THEN T0.BalDueDeb - T0.BalDueCred ELSE 0 END) AS [Not due],
  SUM(CASE WHEN DATEDIFF(day, T0.{{basis}}, GETDATE()) BETWEEN 0 AND {{b1}}
           THEN T0.BalDueDeb - T0.BalDueCred ELSE 0 END) AS [0 to {{b1}}],
  SUM(CASE WHEN DATEDIFF(day, T0.{{basis}}, GETDATE()) BETWEEN {{b1}} + 1 AND {{b2}}
           THEN T0.BalDueDeb - T0.BalDueCred ELSE 0 END) AS [To {{b2}}],
  SUM(CASE WHEN DATEDIFF(day, T0.{{basis}}, GETDATE()) BETWEEN {{b2}} + 1 AND {{b3}}
           THEN T0.BalDueDeb - T0.BalDueCred ELSE 0 END) AS [To {{b3}}],
  SUM(CASE WHEN DATEDIFF(day, T0.{{basis}}, GETDATE()) > {{b3}}
           THEN T0.BalDueDeb - T0.BalDueCred ELSE 0 END) AS [Over {{b3}}]
FROM JDT1 T0
INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode
WHERE T1.CardType = 'C'
  AND (T0.BalDueDeb - T0.BalDueCred) <> 0
  /* AND T1.GroupCode = {{group}} */
GROUP BY T0.ShortName, T1.CardName
ORDER BY Balance DESC

#### 2. SAP HANA: same query

**Body:** SELECT
  T0."ShortName" AS "CardCode",
  T1."CardName",
  SUM(T0."BalDueDeb" - T0."BalDueCred") AS "Balance",
  SUM(CASE WHEN DAYS_BETWEEN(T0."{{basis}}", CURRENT_DATE) < 0
           THEN T0."BalDueDeb" - T0."BalDueCred" ELSE 0 END) AS "Not due",
  SUM(CASE WHEN DAYS_BETWEEN(T0."{{basis}}", CURRENT_DATE) BETWEEN 0 AND {{b1}}
           THEN T0."BalDueDeb" - T0."BalDueCred" ELSE 0 END) AS "0 to {{b1}}",
  SUM(CASE WHEN DAYS_BETWEEN(T0."{{basis}}", CURRENT_DATE) BETWEEN {{b1}} + 1 AND {{b2}}
           THEN T0."BalDueDeb" - T0."BalDueCred" ELSE 0 END) AS "To {{b2}}",
  SUM(CASE WHEN DAYS_BETWEEN(T0."{{basis}}", CURRENT_DATE) BETWEEN {{b2}} + 1 AND {{b3}}
           THEN T0."BalDueDeb" - T0."BalDueCred" ELSE 0 END) AS "To {{b3}}",
  SUM(CASE WHEN DAYS_BETWEEN(T0."{{basis}}", CURRENT_DATE) > {{b3}}
           THEN T0."BalDueDeb" - T0."BalDueCred" ELSE 0 END) AS "Over {{b3}}"
FROM "JDT1" T0
INNER JOIN "OCRD" T1 ON T0."ShortName" = T1."CardCode"
WHERE T1."CardType" = 'C'
  AND (T0."BalDueDeb" - T0."BalDueCred") <> 0
  /* AND T1."GroupCode" = {{group}} */
GROUP BY T0."ShortName", T1."CardName"
ORDER BY "Balance" DESC

#### 3. Alternative: open A/R invoices only (SQL Server)

**Body:** SELECT
  T0.CardCode,
  T0.CardName,
  T0.DocNum,
  T0.DocDate,
  T0.DocDueDate,
  T0.DocTotal - T0.PaidToDate AS OpenAmount,
  DATEDIFF(day, T0.DocDueDate, GETDATE()) AS DaysOverdue
FROM OINV T0
WHERE T0.DocStatus = 'O'
  AND T0.CANCELED = 'N'
ORDER BY DaysOverdue DESC

#### 4. Alternative: open A/R invoices only (HANA)

**Body:** SELECT
  T0."CardCode",
  T0."CardName",
  T0."DocNum",
  T0."DocDate",
  T0."DocDueDate",
  T0."DocTotal" - T0."PaidToDate" AS "OpenAmount",
  DAYS_BETWEEN(T0."DocDueDate", CURRENT_DATE) AS "DaysOverdue"
FROM "OINV" T0
WHERE T0."DocStatus" = 'O'
  AND T0."CANCELED" = 'N'
ORDER BY "DaysOverdue" DESC

**Notes:** Amounts are in local currency; for foreign currency balances use BalFcDeb - BalFcCred and group by currency as well. Remove the /* */ around the GroupCode line to filter one customer group. Tested logic against the SAP Business One table structure (JDT1, OCRD, OINV); check the totals against the standard Customer Receivables Aging report before you rely on it.

## Content

**Intro:** This generator writes a customer aging query for SAP Business One that buckets every open receivable line by days past due, in SQL Server or HANA syntax, ready for Query Manager. It reads open items from JDT1, so payments on account and open credit memos are included the way the standard aging report includes them.

**Sections:**

#### How to use it

**Body:** Pick the aging basis (due date or posting date), set the three bucket limits and, if needed, a customer group code. Copy block 1 for a SQL Server company or block 2 for HANA, paste it into Tools > Queries > Query Generator or the Query Manager, run it and save it in a query category. Results can be exported to Excel from the toolbar, or used as the data source of a Crystal report or a dashboard. If you only want invoices, use blocks 3 and 4, which age each open A/R invoice individually.

**Bullets:**

- Not due: lines whose basis date is in the future.
- 0 to bucket 1: due today up to the first limit.
- Over bucket 3: the long tail that usually needs a credit hold decision.

#### Where the numbers come from

**Body:** Every posting to a business partner writes a JDT1 line whose ShortName is the BP code and whose Account is the control account. BalDueDeb and BalDueCred hold the part of that line not yet reconciled, in local currency, with BalFcDeb and BalFcCred for foreign currency. Internal reconciliation, incoming payments and credit memo applications reduce those columns. Summing BalDueDeb minus BalDueCred per customer therefore gives the open balance including unapplied payments and credit memos, which show as negative amounts in their bucket. OCRD.CardType = 'C' keeps customers only ('S' is supplier, 'L' is lead). The invoice-only variant uses OINV.DocTotal minus PaidToDate and ignores credits that are not applied to a specific invoice.

#### Worked example and reconciliation

**Body:** A customer with a 10,000 invoice due 45 days ago, a 4,000 invoice due in 10 days and an unapplied payment of 3,000 posted 5 days ago shows 11,000 balance: 10,000 in the 31 to 60 column, 4,000 not due and minus 3,000 in 0 to 30, because the payment line ages from its own date. The standard Customer Receivables Aging report would show the same total. To reconcile, run that report for the same date and basis, compare totals per customer and investigate differences, which usually come from manual journal entries on the control account or open items in foreign currency.

#### Gotchas

**Body:** The BalDue columns show today's open state, so this query cannot reproduce aging as of a past date; use the standard report with an as-of date for that. On HANA every identifier must be double quoted with the exact case, and DAYS_BETWEEN replaces DATEDIFF. A customer with a different control account is still captured because the join uses ShortName, not the account. Large companies should add a date range on RefDate if the query is slow, and avoid running it on the production database during posting peaks.

**Cta:** If your finance team wants to ask Business One which customers are drifting past 60 days in plain English, answered on your own server, we can set that up.

**Faq:**

#### What is the SQL query for an aging report in SAP Business One?

**Answer:** Sum JDT1.BalDueDeb minus BalDueCred per customer (join OCRD on ShortName = CardCode, CardType 'C'), and split the sum into buckets with CASE on DATEDIFF between DueDate and today. The generator writes the full query in SQL Server and HANA syntax.

#### Why does my query not match the standard aging report?

**Answer:** Check that both use the same basis (due date or posting date), currency and date. The standard report can age as of a past date, while BalDue columns are current. Manual journal entries on control accounts and partial reconciliations are the usual sources of differences.

#### Should I use JDT1 or OINV for aging?

**Answer:** JDT1 gives the true open balance including payments on account, credit memos and journal entries. OINV with DocTotal minus PaidToDate is simpler and shows invoice numbers, but ignores unapplied credits. Many teams run both: JDT1 for totals, OINV for collection call lists.

#### How do I run SQL queries in SAP Business One?

**Answer:** Open Tools > Queries > Query Generator, or the Query Manager, paste the SQL, execute and save it to a category. Users need query authorization. On HANA databases, use double-quoted identifiers as in the HANA blocks.

## Search terms

sap business one aging report query, sap business one sql queries, sql queries for sap business one, mastering sql queries for sap business one, sap business one queries, sap b1 customer aging query, jdt1 baldue query

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json