# China GL Voucher Trial Balance SQL Generator

> Source: https://netray.co/tools/china-gl-voucher-trial-balance-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Builds a trial balance SQL query by account from a voucher line table, with opening, period movement and closing columns.

## Category

developer

## Region

China

## Audience

- ERP developer
- Finance analyst
- Data migration lead

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Voucher line table name

**Key:** table

**Type:** text

**Default:** gl_voucher_line

#### Account code column

**Key:** acct_col

**Type:** text

**Default:** account_code

#### Voucher date column

**Key:** date_col

**Type:** text

**Default:** voucher_date

#### Debit amount column

**Key:** debit_col

**Type:** text

**Default:** debit_amount

#### Credit amount column

**Key:** credit_col

**Type:** text

**Default:** credit_amount

#### Legal entity column

**Key:** org_col

**Type:** text

**Default:** org_code

#### Legal entity code

**Key:** org

**Type:** text

**Default:** CN01

#### Period start

**Key:** start

**Type:** date

**Default:** 2026-10-01

#### Period end

**Key:** end

**Type:** date

**Default:** 2026-10-31

#### Account prefix length

**Key:** level

**Type:** select

**Options:**

- 4
- 6
- 8

**Default:** 4

**Blocks:**

#### Trial balance by account prefix

**Body:** SELECT
    SUBSTRING({{acct_col}}, 1, {{level}}) AS account,
    SUM(CASE WHEN {{date_col}} < '{{start}}' THEN {{debit_col}} - {{credit_col}} ELSE 0 END) AS opening_balance,
    SUM(CASE WHEN {{date_col}} BETWEEN '{{start}}' AND '{{end}}' THEN {{debit_col}} ELSE 0 END) AS period_debit,
    SUM(CASE WHEN {{date_col}} BETWEEN '{{start}}' AND '{{end}}' THEN {{credit_col}} ELSE 0 END) AS period_credit,
    SUM(CASE WHEN {{date_col}} <= '{{end}}' THEN {{debit_col}} - {{credit_col}} ELSE 0 END) AS closing_balance
FROM {{table}}
WHERE {{org_col}} = '{{org}}'
  AND {{date_col}} <= '{{end}}'
GROUP BY SUBSTRING({{acct_col}}, 1, {{level}})
ORDER BY account;

#### Check that debits equal credits

**Body:** SELECT
    SUM({{debit_col}}) AS total_debit,
    SUM({{credit_col}}) AS total_credit,
    SUM({{debit_col}}) - SUM({{credit_col}}) AS difference
FROM {{table}}
WHERE {{org_col}} = '{{org}}'
  AND {{date_col}} BETWEEN '{{start}}' AND '{{end}}';

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the query

**Body:** 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

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

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

**Faq:**

#### Can I run this directly against Kingdee or Yonyou?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

china gl trial balance sql, voucher line table sql query, kingdee trial balance sql, yonyou voucher table query, reconcile chinese ledger migration sql, chinese voucher to trial balance, gl balance reconciliation sql, china erp migration reconciliation query

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json