Distribution Accounts

The following requirements need to be integrated into the design below, at least for my client's needs.

  1. The process for generating a batch of direct debit transactions needs to be integrated in some way with generating a batch of direct credit transactions that move the direct debit funds from the asset account where they are received (eg the central organization's bank account) to the accounts where they belong (eg bank accounts of local organizations within the central organization, to which the donor belongs).
  2. Donors may be making donations to separate funds at local level, and many donors may be making donations to the same local fund. To reduce per transaction fees, all donations from a donor's account should be aggregated and processed as a single direct debit, and all donations to a local fund should be aggregated and processed as a single direct credit.
  3. Failures in one month's direct debits result in adjustments to the next month's direct credit. For example, if contact A's $25 donation to local fund B fails to go through (eg NSF or a reversal later), the central organization needs to reduce the next month's credit to local fund B to adjust for the $25 they have already transferred to it.

Direct Debit - Monthly Batches

This page specifies how CiviCRM will be enhanced to support interacting with a payment processor using monthly batches of direct debit transactions.

The implementation will have two levels:

  1. A core system for processing monthly direct debits.
  2. A plugin for a particular payment processor for direct debits (RBC ACH).

Direct debits are made against banking accounts based on the receipt of a prior authorization to debit an account for a specific amount at a specific interval. They are known by a variety of names, including monthly payment plans, pre-authorized cheque payments, and automated payment plans. We will initially support only monthly debits.

Payment processors offer services such as storing credit card numbers securely for later ad hoc payments. Similarly, some offer services that provide a webpage that will take in an authorization for a recurring direct debit, such as the one referred to at Direct Debit integration (UK) requirements and spec . This page describes a project where CiviCRM takes and stores the banking information. The implementation will not encrypt the bank account information, as the Payment Card Industry Data Security Standard allows bank account information for direct debit transactions to be stored unencrypted even though credit card numbers must be encrypted.

General functionality will include:

  1. Allowing a payment processor to be set up to support a batch direct debit processor. This involves extending the work of Bernhard Fuerst's German DTA Drupal module for export, and creating import mappings for the return files from it. An RBC Export custom activity will be used to record the creation of RBC export files and store them, an RBC Upload custom activity will record their upload to RBC, and an RBC Import custom activity will be used to record the import of an RBC returns file as well as store it.
  2. Extending CiviContribute's recurring contribution functionality to support direct debits against a contact's bank account(s).
  3. Creating a new UI allowing multiple recurring contributions to be specified for a contact (e.g. $100 / month to contribution type 1 from account A, $25 / month to contribution type 2 from account B).
  4. Creating and tracking batch files containing direct debit transactions to be uploaded to the payment processor in the format required by the payment processor. When a direct debit transaction is created for submission, a CiviCRM contribution in the same amount is created with a status indicating it is in progress.
  5. Processing return files from the payment processor that indicate the success and failure of previously submitted batches of transactions. Error codes and messages will be stored in CiviCRM so that users can fix problems (e.g. invalid bank account number, Not Sufficient Funds, bank account transferred, bank account closed, etc.) before the transactions are resubmitted.
  6. The criteria for selecting transactions to include in a batch will be hard-coded for convenience.

Implementation Approach

Support export of a batch of debit transactions
  1. create a new report option for fixed length records, with a user interface that allows the following to be specified for data records:
    1. field length
    2. formatting (eg leading spaces, leading zeros, left justified with trailing spaces)
    3. the export field value to be either a CiviCRM field, or a constant or a token
    4. (tokens to include: record number in export, number of records in export, tally of a field for all records in export)
  2. allow header and footer fields to be specified in addition to data records
    1. reuse the interface for data records that currently exists for other record types, like header records
    2. extend this interface to indicate if it is a header, data or footer record
  3. report selection criteria will include pledges funds (ie contribution_types) and frequency of donations (monthly to start with)
  4. report fields available will include bank account info and fund info
  5. there will be one record per contact per fund they are giving to
Support import of return file from RBC
  1. The import mapping data structure for contributions will be used. Some enhancements will be made to the data structure in 1.d below which Dave Greenberg has agreed to accept into core. The import itself will be invoked from the UI via the creation of a custom Activity Type of RBC import, which will call in to a modified version of the existing import contribution code.
    1. create a fixed length file import type:
      1. As full support is not going to be provided in core, this should not be submitted as a patch as in 1.d. below.
    2. insert a new form in the wizard that allows a special RecordType field to be specified by start position and length. If it is specified, then 1 to n rows can be entered in table associating a value for the RecordType field with a Record Type Name
    3. the normal field mapping screen will be repeated once for each RecordType, except the RecordType field will not be modifiable in the field mapping. This will allow the header, various detail, and various trailer record types to be specified
    4. for each field in a RecordType field mapping, also include the start position, the length, a PHP sprintf format for parsing the input value into the CiviCRM field by modifying xml/schema/Core/MappingField.xml so that it produces output:
      1. ALTER TABLE civicrm_mapping_field ADD COLUMN column_start_position INT (10) UNSIGNED DEFAULT NULL COMMENT 'Fixed length import starting position of column', column_length INT (10) UNSIGNED DEFAULT NULL COMMENT 'Fixed length mapping column length', column_format VARCHAR (64) DEFAULT NULL COMMENT 'Fixed length mapping sprintf format for output and input';
    5. add pre_ and post_ hooks for import processing of a record and file
    6. (if necessary) allow the code that processes a record to look ahead one or two records and optionally consume them (this enables the optional message records following a data record to be processed at the same time)
    7. code handlers for all types of records:
      1. Header
      2. Detail valid, optional message record
      3. Detail invalid, message record
      4. Detail rejected, message record
      5. Detail returned (NB: optional Foreign Currency Info Record will not be supported)
      6. Detail reversed (NB: optional Foreign Currency Info Record will not be supported)
      7. Trailer Account Trailer record
      8. Trailer Client Trailer record
      9. Trailer Client2 Trailer record (possibly)
      10. Trailer All Records Trailer record (possibly)
      11. Trailer All Input Trailer record (possibly)

There will need to be a bunch of processing to validate records and map to something in CiviCRM. At a high level:

  1. Detail records should produce
    1. on success: update contribution and contribution_payment records, e.g. status
    2. on rejection/failure: update contribution and contribution_payment records, e.g. status, and create an activity record recording error and specifying who should follow up
    3. on reversal: negative amount records against original contribution & payment
  2. Header records should likely match the returns against the export and mark the Activity associated with Export as now completed
  3. Trailer records should be used to validate the data and likely be used to create text in Activity Descriptions indicating the results of the run
    1. The Account Trailer record (type 3) follows the last returned item in the file and can be processed for them at that point. Other records that are not returned items may follow. The Client 2 / All records / Input Trailer record (type 4) will be the last record in the file and will contain totals for all records in the file, including those above and below any Account Trailer record.
  4. Later phases could possibly take specific actions on certain (error) conditions like emailing certain people

Direct Credit - Monthly Batches

CiviCRM does not currently have the infrastructure to handle payments, although the CiviAccounts initiative will be enhancing CiviCRM to support disbursements from a central organization to its sub-organizations, such as chapters, constituency associations, or parishes.

The first phase of this Direct Credit - Monthly Batches initiative will support creating files to be uploaded on a monthly or ad hoc basis to initiate payments from the CiviCRM organization to another part of the organization. A later phase will build on CiviAccounts by supporting the transfer of funds between bank accounts of the central and local parts of the organization.

The implementation will have two levels:

  1. A core system for processing monthly direct credits.
  2. A plugin for a particular payment processor for direct credits (RBC ACH).

Implementation Approach

Support export of a batch of credit transactions to RBC
  1. same sort of approach as for debit transactions in terms of specifying width of fields, filling, formatting
  2. additional type of field to support are tokens created by calculating during a report
  3. tokens to be available for each Charge for each month are a TotalCredit (the only one used for the batch output file, which is a sum of the following tokens), MonthlyCredit, NSFCredit (negative amount), FeeCredit (negative amount), MasterCardCredit, VisaCredit.