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
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
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
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
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.
