KB-351 — FACT_ACCT: Anatomy of the Accounting Ledger¶

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

Purpose¶

This document provides a detailed technical analysis of the FACT_ACCT table, the central accounting repository of ADempiere.

Every accounting transaction generated by the ERP is ultimately stored in this table.

Understanding FACT_ACCT is fundamental for:

Accounting troubleshooting
Technical auditing
SQL validation
Financial report verification
Accounting engine debugging

This document forms the foundation of the Technical Accounting Certification Framework.

What is FACT_ACCT?¶

FACT_ACCT is the accounting ledger used internally by ADempiere.

Every accounting document generated by the system ultimately produces one or more records inside this table.

Examples include:

Purchase Invoices
Sales Invoices
Material Receipts
Customer Shipments
Payments
Allocations
Inventory Adjustments
Production
Journal Entries
Bank Statements

FACT_ACCT is therefore considered the Single Source of Truth for accounting transactions.

Accounting Posting Flow¶

Every accounting document follows the same internal lifecycle.

Business Document

        │

        ▼

Document Engine

        │

        ▼

Doc_*

        │

        ▼

Fact

        │

        ▼

FactLine

        │

        ▼

FACT_ACCT

        │

        ▼

Accounting Information

        │

        ▼

General Ledger

        │

        ▼

Trial Balance

        │

        ▼

Financial Statements

Regardless of the originating business document, every accounting entry is ultimately persisted inside FACT_ACCT.

FACT_ACCT Architecture¶

FACT_ACCT stores accounting information using a fully normalized structure.

Every row represents one accounting line.

A complete accounting entry is therefore composed of multiple rows sharing the same source document.

Key Fields¶

The following columns are considered the most important for accounting validation.

Column	Description	Importance
AD_Table_ID	Source table	★★★★★
Record_ID	Internal document identifier	★★★★★
Account_ID	Natural account	★★★★★
AmtAcctDr	Debit amount	★★★★★
AmtAcctCr	Credit amount	★★★★★
DateAcct	Accounting date	★★★★★
AD_Org_ID	Organization	★★★★★
C_AcctSchema_ID	Accounting Schema	★★★★★
C_BPartner_ID	Business Partner	★★★★☆
M_Product_ID	Product	★★★★☆
C_Project_ID	Project	★★★★☆

Source Document Identification¶

Every accounting line can be traced back to its originating business document using two fields.

AD_Table_ID

+

Record_ID

Example:

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

These identifiers uniquely determine the business document that generated the accounting entry.

Record_ID¶

One of the most important discoveries made during the BLACK ERP implementation was the correct interpretation of Record_ID.

Record_ID is NOT the document number displayed to users.

Instead, it stores the internal primary key of the originating record.

Example:

Document	Visible Number	Record_ID
Vendor Invoice	1000002	1000005
Material Receipt	1000004	1000006
Allocation	490004	1000004

Therefore, SQL validations must always retrieve the internal ID before querying FACT_ACCT.

Account_ID¶

Another important validation performed during the project confirmed the relationship used by FACT_ACCT.

Database metadata demonstrates the following foreign key:

FACT_ACCT.Account_ID

        │

        ▼

C_ElementValue.C_ElementValue_ID

This relationship was verified directly from PostgreSQL system metadata.

Consequently, account descriptions should be obtained directly from C_ElementValue.

Accounting Dimensions¶

Besides debit and credit amounts, FACT_ACCT preserves the complete accounting context.

Typical accounting dimensions include:

Organization
Business Partner
Product
Project
Campaign
Activity
User Elements

These dimensions allow financial reports to be generated without accessing the original business documents.

Purchase Certification Example¶

During BLACK ERP accounting certification, the following accounting flow was validated.

Vendor Invoice

119.001
VAT Pending Payment

↓

Payment Allocation

118.001
VAT Paid

119.001
VAT Pending Payment

The SQL validation demonstrated that the accounting engine correctly persisted both accounting entries inside FACT_ACCT.

Sales Certification Example¶

Customer Invoice

209.001
VAT Not Collected

↓

Payment Allocation

208.001
VAT Collected

209.001
VAT Not Collected

Again, all accounting evidence was validated directly from FACT_ACCT.

Common Validation Workflow¶

Technical accounting validation follows the sequence below.

Locate Business Document

        │

Retrieve Internal ID

        │

Identify AD_Table_ID

        │

Query FACT_ACCT

        │

Validate Accounts

        │

Validate Amounts

        │

Validate Accounting Dimensions

        │

Confirm Financial Reports

Common Mistakes¶

Mistake 1¶

Using DocumentNo instead of Record_ID.

Incorrect.

FACT_ACCT stores the internal identifier.

Mistake 2¶

Assuming Accounting Information stores accounting entries.

Incorrect.

The Accounting Information window simply displays data already stored inside FACT_ACCT.

Mistake 3¶

Validating accounting exclusively through ERP windows.

Technical certification should always include direct SQL validation.

Best Practices¶

BLACK ERP recommends:

Always validate accounting directly from FACT_ACCT.
Retrieve internal document IDs before querying.
Verify debit and credit totals.
Validate accounting dimensions.
Compare SQL results with Accounting Information.
Confirm consistency with General Ledger and Trial Balance.

KB-212 — Accounting Engine Architecture
KB-350 — Technical Accounting Certification Framework
KB-352 — Accounting Traceability
KB-353 — SQL Validation Guide
KB-354 — Cash Basis VAT Technical Validation
KB-355 — Accounting Audit Procedures

Conclusion¶

FACT_ACCT is the central accounting repository of ADempiere.

Every accounting transaction generated by the ERP is persisted in this table, making it the primary source for technical accounting validation, auditing and financial traceability.

Understanding FACT_ACCT is therefore essential for every consultant, developer and auditor working with BLACK ERP.