Developer ToolsFree generatorPDF, Excel, Word exportChina

China GL Voucher Trial Balance SQL Generator

During an ERP migration or consolidation project the finance team needs to prove that a trial balance built from voucher lines matches the source system. This generator writes the SQL for that check using table and column names you supply.

Fill in

Voucher line table name

Account code column

Voucher date column

Debit amount column

Credit amount column

Legal entity column

Legal entity code

Period start

Period end

Account prefix length

Generated code

Download:

Trial balance by account prefix

SELECT
    SUBSTRING(account_code, 1, 4) AS account,
    SUM(CASE WHEN voucher_date < '2026-10-01' THEN debit_amount - credit_amount ELSE 0 END) AS opening_balance,
    SUM(CASE WHEN voucher_date BETWEEN '2026-10-01' AND '2026-10-31' THEN debit_amount ELSE 0 END) AS period_debit,
    SUM(CASE WHEN voucher_date BETWEEN '2026-10-01' AND '2026-10-31' THEN credit_amount ELSE 0 END) AS period_credit,
    SUM(CASE WHEN voucher_date <= '2026-10-31' THEN debit_amount - credit_amount ELSE 0 END) AS closing_balance
FROM gl_voucher_line
WHERE org_code = 'CN01'
  AND voucher_date <= '2026-10-31'
GROUP BY SUBSTRING(account_code, 1, 4)
ORDER BY account;

Check that debits equal credits

SELECT
    SUM(debit_amount) AS total_debit,
    SUM(credit_amount) AS total_credit,
    SUM(debit_amount) - SUM(credit_amount) AS difference
FROM gl_voucher_line
WHERE org_code = 'CN01'
  AND voucher_date BETWEEN '2026-10-01' AND '2026-10-31';

Table and column names are generic placeholders. Kingdee, Yonyou and Inspur use different physical tables and many cloud editions do not allow direct database access, in which case use the vendor export or API. SUBSTRING syntax is for SQL Server and MySQL, use SUBSTR on Oracle. Opening balances of balance sheet accounts should also include the carried forward opening balance table if the system holds one. Verify before relying on it in a migration sign-off.

Migration reconciliation pack

Optional: describe your source and target ledgers and we will outline the reconciliation checks to run before sign-off.

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

How to use the query

Enter the names used in your extract: the voucher line table, account column, date column, debit and credit columns and the legal entity column. Pick the legal entity code, the period dates and how many characters of the account code to group by. Copy the output, run it on your staging database or exported data, and compare each line to the trial balance report in the source ERP. The second query confirms that total debits equal total credits for the period, which should always hold in a double entry ledger.

The method behind it

The trial balance is a roll-up. Opening balance equals all activity before the period start, period debit and credit are activity inside the period, and closing balance is the cumulative debit minus credit up to the period end. Grouping by a four digit prefix gives first-level statutory accounts, while longer prefixes expose sub-accounts. Auxiliary dimensions such as customer or department are not included here and can be added as extra group columns.

Opening = all activity before period start
Closing = opening + debits - credits
Group by prefix to roll up sub-accounts
Add auxiliary columns for customer or project detail

Reading the result

Compare closing balance by account to the system report. Differences usually come from unposted or draft vouchers included in the extract, voucher types such as period-end closing entries that zero out profit and loss accounts, or accounts whose natural sign is credit. Always reconcile on net balance and mark the sign convention in your workpaper.

Common mistakes

Do not forget to filter by legal entity and posting status, or you will mix ledgers and include unapproved vouchers. Remember closing entries that transfer profit and loss to account 4103 at year end, which can make revenue accounts look empty for a closed year. Test on one month before running the full year.

Frequently Asked Questions

Can I run this directly against Kingdee or Yonyou?

Only if you have database access, which cloud editions often do not allow. Most teams extract voucher data through the vendor export or API into a staging database and run the query there, using the physical column names from that extract.

Why group by an account prefix?

Chinese statutory accounts have a four digit first level with sub-accounts below. Grouping by the prefix rolls the detail up to the level used in statutory statements, making comparison with the official balance sheet easy.

Why do debits and credits not balance in my extract?

Typical causes are a partial extract, filtering by account instead of voucher, or amounts stored in a foreign currency column. Check that you extracted whole vouchers and use the same currency basis on both columns.

Does this include opening balances loaded outside vouchers?

No. Many systems store initial balances in a separate table. If yours does, add those amounts to the opening balance, or the totals will not match.

If you need a full migration reconciliation pack across ledgers, Netray can help build and run those checks against your extracts.

Talk to an Expert Explore More Free Tools

Related Tools

Country & Tax

More Developer Tools tools

See all

Back to All Tools