Tax Register
The VAT return — output tax on sales versus input tax on purchases, by tax code, to the net VAT payable (the old RFUMSV00 / S_ALR_87012357), built on BSET — including the zero-rated lines a G/L line-item report would miss.
Sample build of the Tax Register report — output versus input tax by code to the net VAT payable, reconciled to the G/L tax accounts, and rendered tool-neutral so it runs in Power BI, SAC, or Tableau.
| Tax code | Direction | Tax base | Tax amount |
|---|---|---|---|
| A1 · Output 19% | Output (sales) | 5,000,000 | 950,000 |
| A0 · Output 0% | Output (sales) | 800,000 | 0 |
| V1 · Input 19% | Input (purchases) | 3,200,000 | 608,000 |
| V0 · Input 0% | Input (purchases) | 150,000 | 0 |
Every tax-relevant line by code — base, amount, and direction. Output tax on sales (0.95M) less input tax on purchases (0.61M) is the net VAT payable (0.34M) for the period.
Tax code A0 carries 800K of base at 0% — zero-rated (e.g. exports). Confirm it is genuinely zero-rated and not a missing tax code: BSET captures it, but a G/L tax-account line-item report would not show it at all.
BSET stores tax summarized per document per code, including 0% lines. Reconcile the tax to the G/L tax accounts before filing; once filed, RFUMSV00 stamps BSET so a document can't appear in two returns.
The report's query logic — generic SQL on BSET, the tax line-item table. It sums the base and tax by tax code and direction, so output tax less input tax gives the net VAT payable. The same SQL becomes a dbt model in your warehouse.
Show / hide SQL
SELECT b.mwskz AS tax_code,
b.ktosl AS transaction_key,
CASE WHEN b.ktosl = 'MWS' THEN 'Output (sales)'
WHEN b.ktosl = 'VST' THEN 'Input (purchases)'
ELSE 'Other' END AS direction,
SUM(b.hwbas) AS tax_base,
SUM(b.hwste) AS tax_amount
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 -- or the tax reporting date
GROUP BY b.mwskz, b.ktosl
ORDER BY direction, b.mwskz;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 |
| tax_base | measure | Tax base amount the tax is calculated on (HWBAS) |
| tax_amount | measure | Tax amount — output (payable) or input (deductible) (HWSTE) |
| net_vat_payable | measure | Output tax less input tax — what is owed to the authority |
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 |