Start a project

Cash flow by month (incoming vs outgoing)

Incoming payments, outgoing payments and the net cash movement per month for a date range.

By Zain Abbas, Founder & Lead SAP Business One / AI Engineer · Last updated

Tables: ORCT, OVPMUse: Monthly cash reports, liquidity planning and board packs.Parameters: [%0] = from date, [%1] = to date

Tool

SELECT X.PayYear, X.PayMonth, SUM(X.CashIn) AS CashIn, SUM(X.CashOut) AS CashOut,
  SUM(X.CashIn) - SUM(X.CashOut) AS NetCash
FROM (
  SELECT YEAR(T0.DocDate) AS PayYear, MONTH(T0.DocDate) AS PayMonth, T0.DocTotal AS CashIn, 0 AS CashOut
  FROM ORCT T0
  WHERE T0.Canceled = 'N' AND T0.DocDate BETWEEN '[%0]' AND '[%1]'
  UNION ALL
  SELECT YEAR(T1.DocDate), MONTH(T1.DocDate), 0, T1.DocTotal
  FROM OVPM T1
  WHERE T1.Canceled = 'N' AND T1.DocDate BETWEEN '[%0]' AND '[%1]'
) X
GROUP BY X.PayYear, X.PayMonth
ORDER BY X.PayYear, X.PayMonth
Need your UDFs in this report, or a dashboard? We extend it for your company.Customize it →

What this query answers

The owner wants a simple monthly view of cash: how much came in from customers, how much went out to vendors and what is left. This returns one row per month with the total of incoming payments, the total of outgoing payments and the net movement, from payment documents that were not cancelled, in a date range.

Sample output

Illustrative demo values. Your columns are the same; the numbers come from your company.

PayYearPayMonthCashInCashOutNetCash
2026718240015130031100
20268164900172250-7350
2026920130016080040500
20261017655014990026650

SQL Server vs SAP HANA

YEAR() and MONTH() exist on HANA too. Quote the mixed-case column "Canceled" exactly on both ORCT and OVPM, and every alias such as "CashIn".

Common errors and fixes

  • Bank fees or journal entries are not included: This reads payment documents only (ORCT and OVPM). Bank charges posted as journal entries and bank statement postings outside payments are not in it.
  • Payments on account look like extra income: Down payments and payments on account are real cash movements, so they are included. Filter by DocType or by the paid invoices in RCT2 and VPM2 if you only want invoice payments.

Need this report built into SAP Business One?

A saved query answers the question once. We do SAP Business One development: add-ons, integrations and reports, from the database to the screen. Or hire a dedicated SAP B1 developer for the whole job, fixed price per phase.

Finance

Related queries

Incoming payments by dateCustomer payments received in a period, split into cash, transfer, check and card.Outgoing payments by dateVendor payments made in a period, split into cash, transfer, check and card.Open A/R invoices by due dateEvery open customer invoice ordered by due date, with the open amount and days until (or past) the due date.Monthly sales trendNet invoiced sales per month for the last 12 months.Customer aging report (AR aging)Open invoice balance per customer in 0–30, 31–60, 61–90 and 90+ day buckets by due date.Overdue customer invoicesOpen invoices past their due date, with days overdue and the customer phone number.

Table reference: ORCT table (Incoming Payments (header)) · OVPM table (Outgoing Payments (header))

How it works

Three steps, no signup

1

Copy the right version

Use the SQL Server tab for MS SQL databases and the HANA tab for SAP B1 on HANA.

2

Open Query Generator

In SAP Business One go to Tools → Queries → Query Generator, paste and click Execute.

3

Save and share

Click Save, pick a category and give the query a name. Your team can run it from Query Manager.

FAQ

Questions about this tool

How do I run the cash flow by month (incoming vs outgoing) query in SAP Business One?

Copy the version that matches your database, open Tools → Queries → Query Generator, paste it in the SQL box and click Execute. Save it to reuse it later.

Which tables does this query use?

It reads ORCT, OVPM from the standard SAP Business One schema. No user-defined fields are needed.

What should I enter when the selection window opens?

This query uses parameters: [%0] = from date, [%1] = to date. SAP B1 shows a selection window for each one when you run it.

Can this be turned into a dashboard or alert?

Yes. We turn queries into Crystal reports, Excel reports that refresh themselves, SAP B1 alerts or WhatsApp messages for managers.

More free tools

Built for SAP Business One teams

ERP selector: which ERP suits you?Answer 15 questions and compare ERPs on processes, size, budget, hosting and your country’s rules, with every claim sourced.SAP B1 dashboard builderDrag and drop SAP B1 dashboards for Sales, Purchasing, Inventory, Receivables and Finance with click-to-filter. Works offline, no developer needed.SQL to HANA query converterPaste a SQL Server query and get the SAP HANA version, with every change explained.SAP B1 table finderSearch any table: what it stores, its line tables and its key fields.

Need this inside SAP Business One?

We build add-ons, reports and integrations for SAP B1 on SQL Server and HANA. Tell us what you need and get a fixed price, usually within 24 hours.

Start a project Chat on WhatsApp