Developer ToolsFree generatorPDF, Excel, Word exportSAP S/4HANA

SAP ACDOCA SQL Query Generator for the Universal Journal

In S/4HANA the universal journal ACDOCA holds all finance line items, so most finance questions become a query on one table. This generator creates three starting queries: balances by account and cost center, a line item drill-down and a document count check.

Fill in

Schema name

Client (RCLNT)

Ledger (RLDNR)

Company code (RBUKRS)

Fiscal year (GJAHR)

Period from (POPER)

Period to (POPER)

G/L account from (RACCT)

G/L account to (RACCT)

Generated code

Download:

Balances by account and cost center

SELECT rbukrs, racct, rcntr, rhcur,
       SUM(hsl) AS balance_local
FROM SAPHANADB.acdoca
WHERE rclnt = '100'
  AND rldnr = '0L'
  AND rbukrs = '1000'
  AND gjahr = '2026'
  AND poper BETWEEN '001' AND '010'
  AND racct BETWEEN '0000400000' AND '0000499999'
GROUP BY rbukrs, racct, rcntr, rhcur
ORDER BY racct, rcntr;

Line item drill-down

SELECT belnr, docln, budat, blart, racct, rcntr, prctr,
       drcrk, hsl, rhcur, sgtxt
FROM SAPHANADB.acdoca
WHERE rclnt = '100'
  AND rldnr = '0L'
  AND rbukrs = '1000'
  AND gjahr = '2026'
  AND poper BETWEEN '001' AND '010'
  AND racct BETWEEN '0000400000' AND '0000499999'
ORDER BY budat, belnr, docln;

Document count check

SELECT rbukrs, gjahr, poper,
       COUNT(DISTINCT belnr) AS documents,
       COUNT(*) AS line_items
FROM SAPHANADB.acdoca
WHERE rclnt = '100'
  AND rldnr = '0L'
  AND rbukrs = '1000'
  AND gjahr = '2026'
GROUP BY rbukrs, gjahr, poper
ORDER BY poper;

Field names used: RCLNT client, RLDNR ledger, RBUKRS company code, GJAHR fiscal year, POPER posting period, RACCT account, RCNTR cost center, PRCTR profit center, HSL amount in company code currency, RHCUR company code currency, DRCRK debit credit indicator, BELNR document number, DOCLN line item, BUDAT posting date, BLART document type, SGTXT item text. Verify them in SE11 for your S/4HANA release. The schema name differs by installation. Run in a sandbox and use read-only credentials.

Want these answers without writing SQL?

Ask how a read-only natural-language layer over ACDOCA could answer finance questions with the query shown.

Optional. The tool and every export work without this. No spam.

How to use the generator

Enter your schema, client, ledger, company code, fiscal year and period range. Copy the query into HANA Studio, a SQL console, or a reporting tool with read-only access. Always filter on client, ledger, company code and fiscal year, because ACDOCA is a very large table and unrestricted scans are slow and can hit memory limits.

Leading ledger is usually 0L; check your ledger configuration.
Periods are three-digit strings such as 001 to 012.
Use read-only database users for all extracts.

What the queries show

The first query sums the local currency amount HSL by account and cost center. Because HSL carries the sign of the posting (debit positive, credit negative), a revenue account normally shows a negative total. The second query lists the line items behind a balance so you can trace differences. The third counts documents and lines per period, which helps validate a migration or interface load.

Reading results correctly

Compare account balances with a standard report such as the trial balance, using the same ledger and period range. Differences usually come from currency types (HSL versus KSL or OSL), missing period 000 for carried forward balances, or special periods 013 to 016 outside your range. Keep the date logic explicit when you use BUDAT instead of period.

Common mistakes

Leaving out the ledger and double counting when several ledgers exist is common. Another is joining ACDOCA to master data without the validity date and language keys, which multiplies rows. Treat custom Z fields and extension ledgers with care: they are not covered by this generic query and need to be added to the select list.

Frequently Asked Questions

What does HSL mean in ACDOCA?

HSL is the amount in company code currency (the first local currency). Related fields exist for other currency types, such as KSL for the group currency, and TSL for the transaction currency.

Why does revenue show as negative?

HSL carries the posting sign: debits are positive and credits negative. Revenue is credited, so its balance is negative. Multiply by minus one in the presentation layer if you want it positive.

Can I run this on ECC?

No. ACDOCA exists only in S/4HANA and in some Simple Finance add-ons. On ECC with new General Ledger use FAGLFLEXA and FAGLFLEXT, and on classic ledger use GLT0.

Is direct SQL on the database supported?

Direct reads are common for analytics, but SAP recommends released CDS views and APIs for stability and authorization. Use direct SQL with read-only users and avoid writing to SAP tables.

Writing the right query is half the work; trusting it is the other half. Netray can discuss a read-only assistant that answers finance questions over ACDOCA and shows the query for review.

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Developer Tools tools

See all

Back to All Tools