Audit Process Documentation for Intact / Sage

Overview

This explains the fr-tech automation around ensuring that we can generate Journals that are equal to the amounts settled to our bank accounts by payment processors (currently adyen only) and that we have correct and complete data for all the component transactions

There are three core processes:

Process audit (aka reconciliation) file – ensure all transactions are present in CiviCRM and that settlement data (e.g., fees, exchange rate) is recorded. Additional technical detail is in Fundraising/Data and flow/Audits
Validate payout totals – confirm that the audit file totals match the payout amounts stored in CiviCRM.
Generate journal entries and upload to Intacct – once totals are verified, journal entries are created and prepared for upload.

Process Flow Diagram

The following diagram represents the audit file processing decision and validation logic:

Audit file processing flow

Data Sources

Audit files – received from payment processors and/or Gravy (also called reconciliation files ).
TransactionLog – server-side donation logging used to enrich transactions not already in CiviCRM, including donor details and campaign metadata.
CiviCRM – contains existing transactions, including prior settlements (particularly relevant for refunds).

Data Storage

Settlement data is stored against contributions in CiviCRM in the table:

 * `contribution_value_contribution_settlement`

Additional contributions may be created (e.g. mismatched refunds or double refunds) to ensure all settlement activity is tracked.
A batch in CiviCRM represents a payment into the Wikimedia Foundation bank account.

 * Batches are stored in `civicrm_batch`
 * Batch metadata and totals are stored in `civicrm_batch_batch_data`
 * Each batch has a status (e.g. Open, total_verified) and currency-specific totals that are validated.

Validation

The process performs three main validation checks:

All audit file rows must be represented in CiviCRM

The audit file is not moved to “Completed” if any row cannot be created or matched in CiviCRM.

Audit file totals must match the payout amount stored in CiviCRM

Validated per currency.
If totals do not match, the batch remains Open and fr-tech is alerted.

Generated journal totals must match the verified batch totals

Journal upload to Intacct is blocked if they do not match.
TODO: Implement email alert to fr-tech for journal vs. batch discrepancies.

Intacct Journal Generation & Upload

Currently, journal entries are generated and saved in CSV format. This approach was originally used to manage very large transaction files. Now that transactions are aggregated before journal creation, CSV size limits may no longer apply.

The preferred upload method, based on Intacct documentation, appears to be via API:

https://developer.intacct.com/api/general-ledger/jou