DAFpay - Stored Procedure Installation

Quick Start Install Guide

To use DAFpay functionality, you will need to do the following:

Connect your Chariot DAFpay account. This must be done by the owner of your 2Account.
Once connected, download the install script "dafpay_setup.zip" from the link provided in your 2Account Stored Procedure collapsible.
Decompress the zip file, you will have 2 sql scripts.
- Donate2 DAFpay Scheduled SQL Job Code.sql
- LWP_D2_HANDLE_DAFPAY_GIFT.sql

Using SQL Server Management Studio (SSMS), run the LWP_D2_HANDLE_DAFPAY_GIFT.sql install script against your Tessitura database.

Once the LWP_D2_HANDLE_DAFPAY_GIFT script is installed, get the Stored Procedure ID that was generated during installation and enter the value in the Procedure ID field in the Stored Procedure collapsible. Validate & Save. Once validated, the procedure has been installed successfully. If the Procedure ID did not validate, double check that you are using the correct value for the stored procedure named LWP_D2_HANDLE_DAFPAY_GIFT. If that still fails, reach out for help and we will assist you.

Ready to accept a Procedure ID for validation

A validated ID indicating you successfully installed the Stored Procedure

Step-by-Step Installation

The dafpay_setup.zip file, when decompressed, will contain two files:

LWP_D2_HANDLE_DAFPAY_GIFT.sql
Donate2 DAFpay Scheduled SQL Job Code.sql

LWP_D2_HANDLE_DAFPAY_GIFT.sql

Open this file in a text editor, such as Notepad or a similar application, and copy the text. Alternatively, you can directly open the file in SQL Management Studio.

If you are part of a consortium or if you have a unique Organization ID, change the @org_no value from -1 to your organization's ID.

SET @org_no = -1    -- Replace with value FROM TR_ORGANIZATION if other than "Tessitura Web" org

You can now run (execute) the script in SQL Management Studio.

T_DEFAULTS

The script creates a new T_DEFAULTS setting:

D2DAFpayImportSetId

Import Set ID from TR_IMPORT_CONTRIBUTION_SET to use when calling AP_CONT_IMPORT (Contribution Import Utility) to import the pledges and gifts.

Import Contribution Set Configuration

We need to assign a default value for D2DAFpayImportSetId , which is empty by default and must be configured.

The TR_IMPORT_CONTRIBUTION_SET table holds import set criteria for use with the Contribution Import Utility. A row must be set up here before the Contribution Import Utility may be used. Consult the report or system tables documentation for information on specific column settings.

Most likely, you will be creating a new Import Contribution Set ID.

Creating a Contribution Import Set

A Contribution Import Set specifies default values and criteria for the import process to define how constituents and contributions are processed, including assigning a default Source No., assigning a Billing Type, etc. Please review your organization's needs.

Required Fields

Format File is a required field, but not used by the Donate2 DAFpay process. (eg. format.xml)
FilePath needs something in the field—space, star, slash... It just needs something, though it won't actually be used. (eg. c:\)
Batch Type - The batch type the Contribution Import Utility will use when processing contributions for import. This must be a controlled batch type.
Creditee Mode - This field just needs to have some value defined in order for the creditee info to be properly applied to the gift. All of the creditee info (eg. customer no, creditee type) will be coming from settings on the Donate2 form, but this field needs a value to 'enable' it.

The remaining "required" fields aren't used for anything but default values. Most of these values will be coming from the Donate2 form settings.

Here, we show an entry created for processing Donate2 DAFpay gifts named "Donate2 DAFpay Gifts".

Note the ID: 5 value in the below screenshot—that will be used in the T_DEFAULTS entry next.

For details on all the fields in a TR_IMPORT_CONTRIBUTION_SET row, please refer to the Tessitura documentation:

Tessitura Documentation

Here is the T_DEFAULTS setting with the default value configured.

Billing Type

The script will also automatically generate a Donate2 Stripe Billing Type if one does not already exist in TR_BILLING_TYPE

Stored Procedure ID

After the script is successfully completed, you will receive output in SQL Management Studio that provides the Stored Procedure ID, which you will need to validate the Pledge & Gift functionality in your Tessitura LIVE configuration.

In your Tessitura LIVE configuration, you will find a new setting for the DAFpay functionality. If it is not validated, the DAFpay functionality will not appear as an option on your Donate2 Enhanced Donation Forms in the Tessitura Collapsible.

At this point, the Donate2 DAFpay functionality will be available on your Donate2 Enhanced Donation Forms in the Tessitura Collapsible.

You will also have access to Designations if they are configured correctly.

When this functionality is enabled, donations are no longer processed as Ticket Orders. They are processed as DAFpay gifts using the Tessitura Contribution Import Utility.

Tessitura Contribution Import Utility Recurring Job Setup

In SQL Management Studio, expand the SQL Server Agent folder under Object Explorer.
Next, right-click on Jobs and choose "New Job"; a "New Job" interface will appear.

NOTE: If a job is already set up for Donate2 Pledge and Gift functionality, there is no need to create a new job. This code can be used as an additional step on the existing job. Double-click the existing job to open the properties window, then skip to step 4 below.
Under General, give the job a name and a description.

See the screenshot below for an example of a name and description.
Then, click on Steps, choose "New" at the bottom of the window.
Give the step a name (e.g., Contribution Import, or DAFPay Import), and next to "Database," choose "Impresario."
In the "Command" section, paste the entire contents of the "Donate2 DAFpay Scheduled SQL Job Code.sql" file into the empty field and click "OK."

The "Donate2 DAFpay Scheduled SQL Job Code.sql" is provided as part of the zip file you decompressed.
Back at the New Job window, click on "Schedules" in the left navigation, then click "New" at the bottom.
Give the job schedule a name, ensure the Schedule type is "Recurring," and that the "Enabled" box is checked.
Under Frequency, choose "Daily" and set it to recur every 1 day.
In Daily Frequency, click the "Occurs Every" radio button, select 5 minutes, and click "OK." (The starting time should default to 12:00:00 AM, and the ending time should be 11:59:59 PM.)

Contribution Import Utility

Tessitura recommends not running the Contribution Import Utility more frequently than every 5 minutes.
Set the Duration to "No end date."
Back at the job setup window, choose "OK" at the bottom to close out the dialog window.