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

📘 Setting Up Accounting Schemas in iDempiere for NIIF/GAAP Compliance

 Designing a robust accounting schema in iDempiere is essential for financial transparency, audit readiness, and regulatory compliance. Whether you're working under NIIF (IFRS) or GAAP, iDempiere offers flexible tools to structure your chart of accounts and reporting flows. In this guide, we’ll walk through the key steps to configure a compliant schema, with practical tips for Latin American businesses.

🧩 Step 1: Define Your Accounting Schema

Navigate to: Accounting > Accounting Schema

Key fields to configure:

• Currency: Set your functional currency (e.g., NIO or USD).

• GAAP/NIIF Compliance: Choose the standard your company follows.

• Costing Method: Standard, Average, FIFO, etc.

• Auto Post: Enable automatic posting of documents.

💡 Tip: You can create multiple schemas if you need parallel reporting (e.g., one for local GAAP, another for IFRS).

📂 Step 2: Build a Structured Chart of Accounts

Go to: Accounting > Element Value

Organize your accounts by:

• Type: Asset, Liability, Revenue, Expense, Equity

• Hierarchy: Use parent-child relationships for grouping

• Segmentation: Include dimensions like department, project, or region

Example for NIIF:

• 1.1.01 – Cash and Cash Equivalents

• 2.1.01 – Trade Payables

• 4.1.01 – Revenue from Contracts with Customers

• 5.1.01 – Operating Expenses

🧮 Step 3: Assign Default Accounts

Navigate to: Accounting > Accounting Schema > Default Accounts

Set up:

• Receivables/Payables

• Tax Accounts

• Inventory and COGS

• Bank and Cash Accounts

This ensures that every transaction automatically posts to the correct account.

📊 Step 4: Configure Accounting Dimensions

Use dimensions to enhance reporting:

• Organization

• Business Partner

• Product

• Project

• Campaign

These allow for multi-level financial analysis and are essential for NIIF-compliant reporting.

🔍 Step 5: Validate with Sample Transactions

Before going live:

• Create test invoices, payments, and journal entries.

• Review accounting facts in Accounting > Fact Details.

• Ensure debit and credit balances match expected flows.

SQL to verify balance integrity:

sql

SELECT account_id, SUM(amtacctdr - amtacctcr) AS balance
FROM fact_acct
GROUP BY account_id
HAVING SUM(amtacctdr - amtacctcr) <> 0;

✅ Final Checklist

• [ ] Chart of accounts structured and segmented

• [ ] Default accounts assigned

• [ ] Dimensions configured

• [ ] Test transactions validated

• [ ] Backup created before deployment

🧾 Closing Accounting Periods in iDempiere: Common Errors and How to Fix Them

 Closing accounting periods in iDempiere is a critical step to ensure financial integrity and compliance. However, many users encounter errors that block the process or lead to inconsistencies. In this article, I’ll walk you through the most common issues and how to resolve them using practical steps and SQL queries.

🔍 Issue 1: “Cannot close period due to unposted documents”

Cause: There are documents (invoices, payments, etc.) that haven’t been posted to accounting.

Solution:

1. Navigate to: Accounting > Unposted Documents.

2. Filter by the period you want to close.

3. Review and manually post any pending documents.

Helpful SQL Query:

sql

SELECT * FROM fact_acct
WHERE dateacct BETWEEN '2025-01-01' AND '2025-01-31'
AND processed = 'N';

🔍 Issue 2: “Period is closed but accounting entries are still being generated”

Cause: The accounting period is closed, but the document period remains open.

Solution:

1. Go to Accounting > Periods.

2. Make sure both the accounting period and the document period are closed.

3. Check for any automated processes that might be generating entries outside the valid date range.

🔍 Issue 3: “Error during closing process: ‘Cannot close due to inconsistent balances’”

Cause: Some accounting entries are unbalanced or incomplete.

Solution:

1. Run an audit using SQL to detect discrepancies.

2. Look for entries without counterpart accounts or with incorrect account assignments.

Correct SQL to detect unbalanced 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 helps you identify documents that generated incorrect or incomplete accounting entries. You can then open the source document from the “Accounting Fact Details” window in iDempiere to investigate further.

🛠️ Best Practices Before Closing a Period

• Ensure all documents are posted.

• Confirm both accounting and document periods are closed.

• Run SQL audits to detect inconsistencies.

• Always back up your database before closing.