🧮 Accounting Audit in iDempiere Using SQL: Verifying Financial Integrity

 A solid accounting audit in iDempiere goes beyond standard reports—it requires strategic use of SQL to detect inconsistencies, errors, and financial risks. In this article, I’ll share key queries you can use to validate accounting entries, spot duplicates, and ensure your ERP reflects accurate financial data.

🔍 Why Audit with SQL?

While iDempiere offers built-in accounting reports, SQL gives you:

• Real-time validation of accounting balances

• Detection of hidden errors not visible in the UI

• Automation of monthly review processes

• Preparation for external audits and compliance checks

🧠 Query 1: Check for Unbalanced Accounting Entries

sql

SELECT ad_table_id, record_id,
       SUM(amtacctdr - amtacctcr) AS difference
FROM fact_acct
WHERE dateacct BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY ad_table_id, record_id
HAVING SUM(amtacctdr - amtacctcr) <> 0;

🔎 This query identifies documents that generated unbalanced entries. You can investigate each record_id using the “Accounting Fact Details” window in iDempiere.

🧠 Query 2: Detect Duplicate Entries

sql

SELECT account_id, dateacct, amtacctdr, amtacctcr, COUNT(*) AS duplicates
FROM fact_acct
GROUP BY account_id, dateacct, amtacctdr, amtacctcr
HAVING COUNT(*) > 1;

🔎 Useful for spotting entries that were duplicated due to reprocessing errors or failed reversals.

🧠 Query 3: Review Account Balances

sql

SELECT account_id, SUM(amtacctdr - amtacctcr) AS balance
FROM fact_acct
GROUP BY account_id
ORDER BY balance DESC;

🔎 Helps you identify accounts with unexpected or negative balances, especially in liabilities or equity.

🧠 Query 4: Find Entries Without Document Reference

sql

SELECT * FROM fact_acct
WHERE record_id IS NULL OR ad_table_id IS NULL;

🔎 Flags entries that aren’t linked to any source document—often a sign of integration issues or incomplete processes.

🛠️ Best Practices for SQL-Based Auditing

• Run these queries at the end of each accounting period

• Document findings and correct issues before closing

• Integrate these checks into your app (like AIDEN) or external dashboards

• Share results with your accounting team for cross-validation

📌 Real-World Example

In a recent implementation, duplicate entries were found in IR payment transactions. The root cause was a re-execution of the posting process without clearing the previous state. Query 2 helped identify the duplicates, which were manually corrected before closing the period.