Tax Reconciliation
The control check before you file: the tax the sub-ledger (BSET) says posted, versus the actual balance on each G/L tax account. They must agree to the cent — the variance is what you chase down.
Sample build of the Tax Reconciliation report — BSET tax versus the G/L tax-account balance per account, with the variance highlighted, and rendered tool-neutral so it runs in Power BI, SAC, or Tableau.
| G/L tax account | BSET tax | G/L balance | Variance |
|---|---|---|---|
| 175000 · Output VAT | 950,000 | 950,000 | 0 |
| 154000 · Input VAT | 608,000 | 612,000 | (4,000) |
| 176000 · Acquisition tax | 120,000 | 120,000 | 0 |
Each G/L tax account: the tax the sub-ledger (BSET) says posted, versus the actual G/L balance on that account. They should match to the cent before the return is filed.
Input VAT (154000) shows a 4K variance — the G/L balance exceeds BSET. That is almost always a manual posting to the tax account without a tax code, so it never reached BSET or the return. Locate and correct it before filing.
A tax account moves two ways — through a tax code (which writes a BSET line and lands on the return) or a direct manual posting (which does not). Reconcile BSET to the tax accounts so nothing on the books is missing from the return, and nothing on the return is missing from the books.
The report's query logic — sums tax from BSET by G/L account, sums the ACDOCA balance on those same accounts, and shows the variance side by side. The same SQL becomes a dbt model in your warehouse.
Show / hide SQL
WITH bset_tax AS (
SELECT b.hkont AS gl_account,
SUM(b.hwste) AS bset_tax
FROM bset b
JOIN bkpf k ON k.bukrs = b.bukrs
AND k.belnr = b.belnr
AND k.gjahr = b.gjahr
WHERE b.bukrs = :P_COMPANY_CODE
AND k.budat BETWEEN :P_FROM_DATE AND :P_TO_DATE
GROUP BY b.hkont
),
gl_tax AS (
SELECT a.racct AS gl_account,
SUM(a.hsl) AS gl_balance
FROM acdoca a
WHERE a.rbukrs = :P_COMPANY_CODE
AND a.budat BETWEEN :P_FROM_DATE AND :P_TO_DATE
AND a.racct IN (SELECT gl_account FROM bset_tax) -- the tax accounts
GROUP BY a.racct
)
SELECT COALESCE(b.gl_account, g.gl_account) AS gl_account,
b.bset_tax,
g.gl_balance,
(g.gl_balance - b.bset_tax) AS variance
FROM bset_tax b
FULL OUTER JOIN gl_tax g ON b.gl_account = g.gl_account
ORDER BY ABS(g.gl_balance - b.bset_tax) DESC;The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per tax line (document · tax code).
| Element | Type | Definition |
|---|---|---|
| dim_tax_code | dimension | Tax code (MWSKZ) — rate, treatment, and direction (output / input) |
| dim_gl_account | dimension | The G/L tax account the amount posted to (HKONT) |
| dim_company_code | dimension | Reporting entity & country for the return |
| dim_date | dimension | Conformed calendar — posting date & tax reporting date |
| bset_tax | measure | Tax the sub-ledger says posted, per account (BSET HWSTE) |
| gl_balance | measure | The actual G/L balance on the tax account (ACDOCA HSL) |
| variance | measure | G/L tax-account balance less BSET tax — should be zero |
Every source object behind this report. Each linked object has its own page — with its fields and its real S/4HANA status, so you build on the right thing.
| Object | Role | Key fields | S/4HANA status |
|---|---|---|---|
| BSET | Tax data document segment — one summarized tax line per code (MWSKZ · KTOSL) | 4 PK · base/amt | Transparent table |
| BKPF | Document header — posting date, tax reporting date, document type | BUKRS · BELNR · GJAHR | Transparent table |
| BSEG | Source line items behind the tax code — joined back for line detail | BUKRS · BELNR · GJAHR | Transparent table |
| I_GLAccountLineItem | Released journal-entry line view — carries the tax code at line level | released | Released CDS view |
| T007A | Tax codes (Customizing) — the rate and treatment behind each MWSKZ | config | Customizing table |
| WITH_ITEM | Withholding-tax line items — the companion for vendor withholding | reference | Transparent table |
| RFUMSV00 | The VAT-return program (S_ALR_87012357) — SAP's own logic over BSET | program | Standard report |