Audit Process Documentation for Intacct / 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 (our finance system, provided by Sage) – 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 (also called reconciliation files). In some cases these come as files and sometimes from the processor's api
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).

(note Gr4vy also provides a consolidated file which they promote for reconciliation purposes. Our experience is that this is not adequate as a replacement for the reconciliation/audit files we get from the processors as there are issues around edge cases and they do not provide batch total data for us to validate against and Gravy appears to be promoting this as being a more complete product than it is as it is not available for all processors. We do put in the additional work to help Gravy to get the data from the payment providers and to process their files but we don't get much value from them)

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 is updated to 'Needs Attention'

Generated journal totals must match the verified batch totals

Journal upload to Intacct is blocked if they do not match.
Fr-tech receives a daily email of the status of all batches. If the batches need attention of if files have not been processed in a timely manner fr-tech are alerted via the email subject line

Intacct Journal Generation & Upload

Currently, journal entries are generated and saved in CSV format on disk. They are uploaded to Intacct using the api

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