Pledge & Gift - Stored Procedure Installation

15 Minutes to install from Start to Finish

To use Pledge & Gift functionality, you will need to run "LWP_D2_HANDLE_PLEDGES.sql" install script against your Tessitura environment.

Step-by-Step Installation

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

• LWP_D2_HANDLE_PLEDGES.sql
• Donate2 Pledge Scheduled SQL Job Code.sql

LWP_D2_HANDLE_PLEDGES.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 two new T_DEFAULTS settings:

1. D2PledgePaymentBatchType

   Batch Type ID to use when applying a payment to an existing pledge (non-controlled batch).

2. D2PledgeImportSetId

   Import Set ID from TR_IMPORT_CONTRIBUTION_SET to use when calling AP_CONT_IMPORT to create pledges.

Here is a screenshot of the newly created T_DEFAULTS, where the default values are empty. You'll need to configure these to ensure the entire process works.

Here are the same T_DEFAULTS and notice that the default values are configured.

Here are the same T_DEFAULTS , now configured with default values.

D2PledgePaymentBatchType

You can choose to create a new dedicated Batch Type in the TR_BATCH_TYPE table, or you can use an existing non-controlled Batch Type.

In our example, we created a "Donate2" entry in the TR_BATCH_TYPE table as a non-controlled Batch Type. We then used the TR_BATCH_TYPE ID "23" and applied that to our D2PledgePaymentBatchType T_DEFAULT as the default value.

Note: Auto Billing may be an option for you.

D2PledgeImportSetId

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

You can choose to create a new dedicated Import Contribution Set in the TR_IMPORT_CONTRIBUTION_SET table, or you can use an existing Import Contribution Set ID.

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.

Here, we show an entry created for processing Donate2 Pledges named "Donate2 Pledges."

Once you have an ID value, you will need to update the T_DEFAULTS for D2PledgeImportSetId .

Format File is not required.

FilePath needs something in the field—space, star, slash... It just needs something, though it won't actually be used.

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

Tessitura Documentation

Custom Contribution Data Fields

The script will automatically create two new custom data fields in the T_KEYWORD system table for contributions that Donate2 will use to more easily look up the contribution.

Contribution custom fields are defined in the T_KEYWORD table to set which custom IDs will be used by Donate2 (e.g., custom_1, custom_2) when creating a contribution. Donate2 needs to record a Stripe Subscription ID and a Stripe Customer ID on the contribution at the time of processing.

Here is a screenshot of a Contribution Detail showing the Custom Data tab activated, which displays the two custom fields populated with a Stripe Subscription ID and a Stripe Customer ID.

The Custom ID value defines which custom field on the Contribution is used. For example, based on what is shown above:

• Custom ID = 1 indicates that the Custom_1 field will be the Stripe Subscription ID.
• Custom ID = 2 indicates that the Custom_2 field will be the Stripe Customer ID.

Billing Type

Pledge Schedule

A local table is created for tracking the Stripe Subscription schedule created for the pledge.

LT_D2_PLEDGE_SCHEDULES

When the contribution is created by the Contribution Import Utility, the Tessitura payment schedule will be overwritten with the Stripe schedule, so the schedule in Tessitura matches the Stripe schedule.

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 Pledge & Gift functionality. If it is not validated, the Pledge & Gift functionality will not appear as an option on your Donate2 Enhanced Donation Forms in the Tessitura Collapsible.

Once you install the Stored Procedure, enter the Stored Procedure ID and validate it. If the validation is successful, you will get a green "Validated" message.

At this point, the Pledge & Gift 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 Gifts and Pledges using the Tessitura Contribution Import Utility.

Tessitura Contribution Import Utility Recurring Job Setup

1. In SQL Management Studio, expand the SQL Server Agent folder under Object Explorer.

   
   

   

   
   

2. Next, right-click on Jobs and choose "New Job"; a "New Job" interface will appear.

   
   

   

   
   

3. Under General, give the job a name and a description.

   See the screenshot below for an example of a name and description.

   
   

   

   
   

4. Then, click on Steps, choose "New" at the bottom of the window.

   
   

   

   
   

5. Give the step a name (e.g., Contribution Import), and next to "Database," choose "Impresario."

   
   

   

   
   

6. In the "Command" section, paste the entire contents of the "Donate2 Pledge Scheduled SQL Job Code.sql" file into the empty field and click "OK."

   The "Donate2 Pledge Scheduled SQL Job Code.sql" is provided as part of the zip file you decompressed.

   
   

   

   
   

7. Back at the New Job window, click on "Schedules" in the left navigation, then click "New" at the bottom.

   
   

   

   
   

8. Give the job schedule a name, ensure the Schedule type is "Recurring," and that the "Enabled" box is checked.

   
   
   

9. Under Frequency, choose "Daily" and set it to recur every 1 day.

   
   
   

10. 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.

11. Set the Duration to "No end date."

    
    

12. Back at the job setup window, choose "OK" at the bottom to close out the dialog window.