Activity Stream x Tessitura Integration Guide

To make sure your Tessitura system syncs properly with Activity Stream, we need you to set up a few items in Tessitura:

1. Create an Activity Stream API User in Tessitura Securities
2. Create new Stored Procedures
3. Whitelist Exchange IP Addresses
4. Make decisions about how you want your data to appear in Activity Stream

---

1. Create an Activity Stream API User in Tessitura Securities

Create a new user

In Tessitura Securities, go to Maintain Users and then to New. Create a new user for the Activity Stream API. This user must be of the type API, you must set a secure password and you need to add a location. Make a note of the User ID for later.

Create a new user group.

Go to Maintain Groups and select New. Create a new user group for the API, if you use Divisions, Organizations, or Control Groups make sure to select the ones you want Activity Stream to view data from. Note the User Group ID for later.

Go to Maintain Users and add the new User Group to the new User.

Assign services to the user User Group

In Tessitura Securities, go to Services, select the User Group you just made and add View permissions for the following resources:

• CRM/Attributes
• CRM/Constituents
• CRM/ContactPermissions
• ReferenceData/PerformanceTypes
• ReferenceData/Theaters
• TXN/Facilities
• TXN/InventoryWebContents
• TXN/Orders
• TXN/Performances
• TXN/ProductionSeasons
• TXN/Productions
• TXN/Titles

Next, add Add, Edit and View permissions to the following services:

• Custom/Execute
• TXN/PerformancePriceLayers

Select or create a Machine Location 

Go to TX_MACHINE_LOCATION in System/Reference tables and either add a new value or select the value you would like our API user to use.

2. Create new Stored Procedures

Your system administrator may be able to install new Stored Procedures or you may need to ask Tessitura Support to install them for you. The procedures are:

``sql
IF OBJECT_ID('dbo.Lp_as_updated_orders', 'P') IS NULL
    EXEC('CREATE PROCEDURE dbo.Lp_as_updated_orders AS SET NOCOUNT ON;')
GO

ALTER PROCEDURE [dbo].[Lp_as_updated_orders]
    @start_dt DATETIME = NULL,
    @end_dt   DATETIME = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        t_order.order_no,
        CASE
            WHEN t_order.last_update_dt >= ISNULL(MAX(t_sub_lineitem.last_update_dt), t_order.last_update_dt) AND t_order.last_update_dt <= @end_dt
                THEN t_order.last_update_dt
            ELSE MAX(t_sub_lineitem.last_update_dt)
        END AS last_update_dt
    FROM
        t_order
    LEFT JOIN
        t_sub_lineitem
        ON t_order.order_no = t_sub_lineitem.order_no
        AND t_sub_lineitem.last_update_dt BETWEEN @start_dt AND @end_dt
    WHERE
        t_order.last_update_dt BETWEEN @start_dt AND @end_dt
        OR t_sub_lineitem.last_update_dt BETWEEN @start_dt AND @end_dt
    GROUP BY
        t_order.order_no, t_order.last_update_dt;
END;
```

``sql
IF OBJECT_ID('dbo.LP_AS_SUBLINE_ITEMS', 'P') IS NULL
BEGIN EXEC('CREATE PROCEDURE LP_AS_SUBLINE_ITEMS AS SET NOCOUNT
ON;') END;
GO
ALTER PROCEDURE [dbo].[LP_AS_SUBLINE_ITEMS]
@order_no INT AS BEGIN SET NOCOUNT ON;
SELECT * FROM dbo.T_SUB_LINEITEM
WHERE order_no = @order_no;
END;
GO
```

3. Whitelisting IP Addresses

If your Tessitura instance is self-hosted, the following IP addresses need to be whitelisted:

• 20.54.97.242
• 20.166.158.55
• 4.245.250.180
• 4.245.250.181

 

 

 

4. Make decisions about how you want your data to appear in Activity Stream

We know that Tessitura is highly configurable so we have built settings to accommodate several ways Tessitura Members have their performances set up.

Constituent attributes as Customer Tags

You can choose which of your Customer Attributes are passed through to Activity Stream as Customer Tags, we just need a list of the keyword descriptions. To get this list you can:

1. Go to the T_KEYWORD System/Reference table and pick from those where the DETAIL TBL is TX_CUST_KEYWORD.
2. Run the following query in SSMS:.

   select * from T_KEYWORD where detail_tbl = 'TX_CUST_KEYWORD

Ticket value to display in Activity Stream

Activity Stream displays value per ticket however, you can choose whether that value includes fees or not. For Tessitura clients we need to understand if the value of a sold ticket in Activity Stream should include:

1. Price Layers that are of the type Ticket
2. All Price Layers including those of the type fee or charge
3. Other fees associated with subline items

An example of this might be a ticket that costs £50, inclusive of a £5 producers fee, that
also has a £4 booking fee