Skip to content

KB-353 — SQL Accounting Validation Guide

Version: 1.0
Status: Approved
Author: BLACK ERP Engineering
Category: Technical Accounting Certification

Purpose

This document provides the standard SQL procedures used to validate accounting transactions directly from the PostgreSQL database.

Unlike ERP accounting windows, these queries retrieve accounting information directly from FACT_ACCT, allowing consultants, developers and auditors to validate accounting postings without relying on the application interface.

The SQL queries documented here were developed and validated during the BLACK ERP accounting certification process.

Objective

The objectives of this guide are:

  • Validate accounting directly from the database.
  • Verify posting accuracy.
  • Trace accounting entries back to their originating business documents.
  • Validate accounting dimensions.
  • Support technical audits.
  • Troubleshoot accounting issues.

The Single Source of Truth

All accounting validations begin with the same assumption:

FACT_ACCT is the authoritative accounting repository.

Every accounting document generated by ADempiere is ultimately stored inside this table.

Step 1 — Identify the Source Table

Before querying FACT_ACCT, identify the AD_Table_ID associated with the business document.

Example:

SELECT
    ad_table_id,
    tablename
FROM adempiere.ad_table
WHERE tablename IN
(
    'C_Invoice',
    'C_Payment',
    'C_AllocationHdr',
    'M_InOut',
    'C_BankStatement'
)
ORDER BY tablename;

Result obtained during BLACK ERP certification:

Table AD_Table_ID
C_Invoice 318
M_InOut 319
C_Payment 335
C_BankStatement 392
C_AllocationHdr 735

Step 2 — Obtain the Internal Record ID

FACT_ACCT does not store DocumentNo.

It stores the internal primary key of the originating document.

Examples:

Purchase Invoice

SELECT
    c_invoice_id,
    documentno
FROM adempiere.c_invoice
WHERE documentno='1000002';

Material Receipt

SELECT
    m_inout_id,
    documentno
FROM adempiere.m_inout
WHERE documentno='1000004';

Payment

SELECT
    c_payment_id,
    documentno
FROM adempiere.c_payment
WHERE documentno='1000004';

Allocation

SELECT
    c_allocationhdr_id,
    documentno
FROM adempiere.c_allocationhdr
WHERE documentno='490004';

Important Finding

During certification, it was confirmed that:

Record_ID

≠

DocumentNo

Record_ID always corresponds to the internal primary key.

Step 3 — Retrieve Accounting Entries

Once the internal ID is known, accounting can be retrieved directly from FACT_ACCT.

SELECT

    t.tablename,

    fa.record_id,

    fa.dateacct,

    ev.value,

    ev.name,

    fa.amtacctdr,

    fa.amtacctcr,

    bp.name,

    p.value,

    pr.value

FROM adempiere.fact_acct fa

JOIN adempiere.ad_table t

ON t.ad_table_id=fa.ad_table_id

JOIN adempiere.c_elementvalue ev

ON ev.c_elementvalue_id=fa.account_id

LEFT JOIN adempiere.c_bpartner bp

ON bp.c_bpartner_id=fa.c_bpartner_id

LEFT JOIN adempiere.m_product p

ON p.m_product_id=fa.m_product_id

LEFT JOIN adempiere.c_project pr

ON pr.c_project_id=fa.c_project_id

WHERE ...

ORDER BY ...

;

Important Finding

The certification demonstrated that:

FACT_ACCT.Account_ID

references

C_ElementValue.C_ElementValue_ID

directly.

No intermediate lookup through C_ValidCombination is required for obtaining the natural account.

This relationship was verified using PostgreSQL system metadata.

Purchase Certification Example

Purchase Case C-001

Invoice

Document

1000002

↓

Record_ID

1000005

↓

FACT_ACCT

Results

  • 115.008
  • 119.001
  • 201.001.001

Allocation

↓

118.001

119.001

201.001.001

201.001.004

Sales Certification Example

Sales Case V-001

Invoice

↓

209.001

↓

Allocation

↓

208.001

209.001

106.001.001

102.001.001.001.004

Validating VAT Cash Basis

Purchase

Expected Flow

119.001

↓

118.001

Sales

Expected Flow

209.001

↓

208.001

The SQL queries successfully confirmed this behavior directly from FACT_ACCT.

Validation Checklist

Every accounting validation should confirm:

✓ Correct source table

✓ Correct Record_ID

✓ Correct debit amounts

✓ Correct credit amounts

✓ Correct natural account

✓ Correct Business Partner

✓ Correct Product

✓ Correct Project

✓ Correct accounting date

Common Mistakes

Mistake 1

Using DocumentNo instead of Record_ID.

Mistake 2

Joining Account_ID with C_ValidCombination.

The correct relationship is:

FACT_ACCT.Account_ID

↓

C_ElementValue

Mistake 3

Ignoring accounting dimensions.

Always verify:

  • Project
  • Product
  • Business Partner
  • Organization

Mistake 4

Validating only through ERP windows.

Technical certification should always include SQL validation.

Best Practices

BLACK ERP recommends:

  • Validate accounting directly from FACT_ACCT.
  • Always obtain the internal document ID first.
  • Validate accounting dimensions.
  • Compare SQL results with Accounting Information.
  • Confirm consistency with financial reports.

Related Knowledge Base

  • KB-212 — Accounting Engine Architecture
  • KB-350 — Technical Accounting Certification Framework
  • KB-351 — FACT_ACCT Anatomy
  • KB-352 — Accounting Traceability
  • KB-354 — Cash Basis VAT Technical Validation
  • KB-355 — Accounting Audit Procedures

Conclusion

SQL validation provides direct evidence that accounting transactions generated by ADempiere have been correctly persisted in FACT_ACCT.

Combined with functional certification, SQL validation constitutes one of the strongest forms of accounting evidence available during ERP implementation and technical auditing.