Batches

General Concepts

Batches will be utilized for grouping a variety of items but initially will include two types

Financial transactions:  collecting financial transactions into meaningful groups for export and reconciliation with external financial systems.

Receipts: collecting transactions based on their contribution type and receipt existence

Financial Batches:

Batching is affected by the accounting set up. Two options:

The purchase and payment of an order are in separate transactions (AR account)  

The purchase and payment are in one transaction (no AR account)

The database will be set up with AR, but for phase 1 AR accounts will not be utilized and these use cases will not be developed.

Batch types

Automatic

All real time transactions that are processed through a payment processor need to be assigned to batches automatically.  Automatic batches are created according to a specific setup  for each payment processor and payment vehicle.

Automatic batches are generally created at a specific time each day according to the rules of the payment processor. An automatic batch is considered closed as soon as a new batch of the same type is created.  Automated batches are also marked closed when they are export.

Phase 1 will not support Automatic batches.

Manual

Users need to be able to create batches to meet their the specific needs for collect transactions into logical groups. Batches are created in advance and users select a batch to contain transactions as they are entered into the system. Batches are considered open when they are created and remain open until they are export. Once a batch is exported no new transaction can be added to the batch.

Use Cases

Automatic batch setup

User’s define the parameters that will be used to automatically create the batches as necessary.

User either selects an automated batch configuration to edit, or adds a new one through the automated batch list page.

User can enable and disable batch configurations through the automated batch list page.

The batch setup screen presents the following information:

List of enabled payment processors

List of enabled payment methods

A field for entering the daily start time of new batches

A batch description field

The name format for batch names. The batch name is constructed at “user batch name”+’YYYY-MM-DD’ e.g AUTHNET-CC-2011-07-15

Manual Batch Creation

User creates a new batch for holding financial transactions.

User views current batches to edit, add, close and open through a batch list screen

Users add a new batch by defining the following: batch name, payment method, description, optional manual number of transactions, optional manual batch total. The batch open date is assigned automatically.

Users can edit names and descriptions of batches, manual number of transactions, and manual batch total, and set the batch to closed. Closing a batch requires 1) that a non-null manual number of transactions match the number that have been assigned to the batch and 2) that a non-null batch total match the total value of the transactions assigned to the batch. A batch close date is assigned automatically. Administrators may have permission to re-open a batch that has not been exported, in which case the batch close date is set to null. Once the the batch is exported it cannot be changed.

If a batch has not had any transactions assigned, it may be deleted.

Manual Batch Assignment

User searches for transactions that have not been assigned to a batch (similar to find contributions). Search can include payment method, transaction type, date range

The search result list provides a task option for “add to batch” this presents the user with a list of currently open batches

Saving the batch associates the selected transactions with the specified batch.

Batch export

Users need to select the batches that they want to export to the financial system.  The batch selection screen displays the list of non-exported batches, the batch name, the transaction type of the batch, the batch create date, the batch close date and the total transaction amount of the batch.

The user selects one or more batches for export and assigns an filename to contain the export data. The user click the “create export”  button to initiate the batch creation. A validation step ensures that all selected batches are either closed or validate for closure before the export begins. Once a batch is exported it is automatically closed and cannot be reopened or altered.

Once the export file is created the user selects it via the download link on a page of previously generated exports.

The export process summarizes the transactions by financial account.  The output includes an account name, number, debit, and credit field. Other outputs types can be generated in the future.

Batch reporting

Users can report on open and closed batches. The detail reporting lists individual transactions contained in a batch.

Receipts Batches

Receipts batches can either include all transaction of certain contribution types or only transactions of a certain transaction types without receipts. 

Receipt batches will be manual, no automatically created receipt batches

Receipt batches do not have the option of open/closed, all are considered closed once created, they can be deleted based on permission level

Receipt batches do not have names

The receipt reports will be different for different organizations, but one receipt report will be generated for phase 1

Use Cases

Batch creation /assignment:

Users selects the contribution type(s), start date, end date, selects either ALL transactions or only transactions without a receipt.  User then “Creates receipt”

The receipt batch report is displayed on screen and can be downloaded

Batch Reporting

Users can create reports with one or more receipt batches based and can create report based on  individual contact or contacts  with a particular tag, group, custom data field, etc.  The detail report(s) would mirror the report from the bath creation/assignment.

Batch deleting

Users with proper permissions can delete batches.

Users would search for a batches for a contact and date range.  Preview the batch delete the batch.

--------

From previous version of page:

Bank deposits encompassing multiple contributions need to be added in a way that supports rapid bulk data entry of cheques for new and existing contacts. See [|]Batches for Data Entry and Financial Auditing|confluence/display/CRM/Finance+and+Accounting#FinanceandAccounting-BatchesforDataEntryandFinancialAuditing||||||||\

Technical Specification

Manual Batches

Schema

NB: Reuse of civicrm_batch has obsoleted this schema spec.

Create a new table civicrm_financial_batch with the following fields:

• id INT
• batch_type_id INT NOT NULL fk to Batch Type options in civicrm_option_values
• contact_id INT fk NOT NULL to civicrm_contact.id of batch creator
• description VARCHAR (255) NOT NULL
• payment_instrument_id INT NULL fk to Payment Instrument options in civicrm_option_values
• manual_number_trans INT NULL
• manual_total DECIMAL (20,2) NULL
• open_date DATETIME NOT NULL DEFAULT NOW()
• close_date DATETIME NULL
• export_date DATETIME NULL
• batch_status_id INT NOT NULL fk to Batch Status options in civicrm_option_values

Create new record in civicrm_option_group for batch types and batch statuses

Create new records in civicrm_option_value for Manual batch, Automatic batch; Open, Closed, Reopened, Exported

Create new permissions in Drupal (and if supported, in Joomla and WordPress) for:

• Create manual batch
• Edit own manual batches
• Edit all manual batches
• View own manual batches
• View all manual batches
• Delete own manual batches
• Delete all manual batches

Create new Menu entries under Contributions, after Manage Price Sets:

• Insert a menu spacer/divider
• New Batch
• Open Batches
• Closed Batches
• Exported Batches

Create a new reserved Activity Type of "Export of Financial Transactions Batch".

Forms / Pages

New Batch

Buttons at top and bottom: Save, Save and New, Cancel

Fields as defined in Edit Batch below.

On Save, leaves user on the Batch Transactions form for that batch. On Save and New, leaves user on blank New Batch form. On Cancel, redirects to CiviCRM Home.

Batch Transactions

Across the top of the form, list the batch information as follows:

• Created by: display_name of contact_id
• Description: description
• Payment Instrument: label of payment_instrument_id
• Type: label of batch_type_id
• Entered transactions: manual_number_trans
• Assigned transactions: number of assigned transactions
• Entered total: manual_total
• Assigned total: total of total_amount of assigned transactions
• Opened: open_date

Below that, provide a fieldset, initially closed, of the financial_items assigned to the batch. Do not display the search criteria. Enable a single action of "Remove from Batch" when one or more records have been selected. The results lists the currently assigned financial_item records as per the entity_financial_item records pointing to this batch, using the following columns (cf Find Contributions results):

• checkbox to select contribution for removal
• ID - the financial_item.id (Q: should we show the entity_id too?)
• symbol for contact type
• Contact Name
• Amount
• Type (to be confirmed)
• Source (to be confirmed)
• Received
• View link - for financial_item (or its encompassing item, eg view a contribution if the item is a line item in contribution)
• Remove link - after confirmation dialog, removes the transaction from the batch and returns to this page

Below that, provide a fieldset that cannot be closed that replicates the Find Contributions form, modified to require that Contribution Status be Completed, and only displaying transactions that have not been assigned to batches. Use force=1 so that Edit Search Criteria comes up closed when there are financial transactions available to be selected and assigned. There should be no Print button. It should include the following columns:

• checkbox to select contribution for assignment
• ID - the financial_item.id (Q: should we show the entity_id too?)
• symbol for contact type
• Contact Name
• Amount
• Received
• Type (to be confirmed)
• Source (to be confirmed)
• View link - for financial_item (or its encompassing item, eg view a contribution if the item is a line item in contribution)
• Assign link - after confirmation dialog, assign the transaction to the batch and returns to this page

Once at least one contribution is selected, the - actions - select field changes to "Assign to Batch". Clicking Go brings up a confirmation dialog, and then redirects back to this page, refreshed to include the new transactions in the number and total.

NB: Use a transaction around the assignment of one or more financial_items to a batch to prevent collisions. Inside the transaction, check that the status of the transaction is completed and that it is not assigned to a batch already, rolling back on errors. Wherever Contribution Status can be modified from Completed to something else, use a transaction around changing a transaction status, checking inside the transaction that the financial_item is not assigned to batch, rolling back if it is.

Edit Batch

Provide a form that allows user to edit the following fields of manual batches that are in the Open but not Closed or Exported status:

• Created by: display_name of contact_id (only available if user has Edit All Batches permission)
• Name
• Description: description
• Payment Instrument: label of payment_instrument_id
• Type: label of batch_type_id (view only)
• Entered transactions: manual_number_trans
• Assigned transactions: number of assigned transactions (view only)
• Entered total: manual_total 
• Assigned total: total of total_amount of assigned transactions (view only)
• Opened: created_date (view only)
• Status: batch status (validation for switching from open to closed same as closing batch; UI does not allow changing status to Exported)

Open Batches

Display a search results profile with the following fields with a possible actions of 'Close Batches', 'Export Batches' and 'Delete Batches' available when batches are selected:

• Checkbox to select batch
• Created by: display_name of contact_id
• Name: name
• Description: description
• Payment Instrument: label of payment_instrument_id
• Type: label of batch_type_id
• Entered transactions: manual_number_trans
• Assigned transactions: number of assigned transactions
• Entered total: manual_total 
• Assigned total: total of total_amount of assigned transactions
• Opened: opened_date 
• Transactions link - opens view of assigned transactions with option to assign more
• Edit link - opens form allowing batch fields to be edited (includes option to delete)
• Close link - after confirmation dialog, closes batch and redirects to Open Batches
• Export link - after confirmation dialog, closes and exports batch and redirects to Open Batches
• Delete link - after confirmation dialog, deletes batch leaving its transactions unassigned and redirects to Open Batches

Search criteria should be all fields displayed in results, excepted: description, link. In addition, provide a checkbox for Entered transactions <> Assigned Transactions, and for Entered total <> Assigned total. (NB: these should not checked by default).

Closed Batches

Display a search results profile with the following fields with possible actions of 'Reopen Batches', 'Export Batches' and 'Delete Batches' available when batches are selected for batches that are closed but not exported:

• Checkbox to select batch
• Created by: display_name of contact_id
• Description: description
• Payment Instrument: label of payment_instrument_id
• Type: label of batch_type_id
• Entered transactions: manual_number_trans
• Assigned transactions: number of assigned transactions
• Entered total: manual_total 
• Assigned total: total of total_amount of assigned transactions
• Opened: opened_date
• Closed: closed_date
• Transactions link - opens view of assigned transactions
• Reopen link - after confirmation dialog, changes status to Reopened and redirects to Closed Batches.
• Export link - after confirmation dialog, does an export of transactions, makes export file available for download, and redirects back to Closed Batches.
• Delete link - after confirmation dialog, deletes batch leaving its transactions unassigned and redirects to Closed Batches

NB: export of one or more batches involves the following:

• set batch status to Exported for each batch
• use an export field mapping saved via an administrative form (to be spec'd here shortly) to create a .csv file of the transactions in all of the batch(es)
• save the file to server disk and create an entry for it in civicrm_file
• create an "Export of Financial Transactions Batch" Activity, attaching the .csv file to it via civicrm_entity_file
• attach the .csv file to each batch via additional civicrm_entity_file records

Exported Batches

Display a search results profile with the following fields with possible actions of 'Download Batches' and 'Delete Batches' available when batches are selected:

• Checkbox to select batch
• Created by: display_name of contact_id
• Description: description
• Payment Instrument: label of payment_instrument_id
• Type: label of batch_type_id
• Entered transactions: manual_number_trans
• Assigned transactions: number of assigned transactions
• Entered total: manual_total 
• Assigned total: total of total_amount of assigned transactions
• Opened: opened_date
• Closed: closed_date
• Exported: exported_date
• Transactions link - opens view of assigned transactions
• Download link - opens file download dialog of file of batched transactions
• Delete link - after confirmation dialog, deletes the batch. Transactions previously assigned to batch become available for assignment to a different batch.

The Download Batches action downloads a compressed .zip file if two or more batches are selected, else the uncompressed single file.