Prospect2 Database Objects

When Prospect2 is installed into your Tessitura database, the following objects are created. Click on the object to learn more about its purpose or function.

LFT_PRO2_ELIGIBLE_PERFS

This table function is used to gather all performance-related data for parent procedures LWP_PRO2_HANDLE_TRIGGER and LWP_PRO2_GET_ORDER_DETAILS. It is only called when a Scheduled Trigger is executed and is of the Performance-based type.

Signature:

	@start_date datetime = NULL,
	@end_date datetime = NULL,
	@with_keyword varchar (500) = NULL,
	@without_keyword varchar (500) = NULL,
	@suppress_season_types varchar (64) = NULL,
	@suppress_perf_types varchar(64) = NULL

@withKeyword and @withoutKeyword are derived from parameters set in the Prospect2 dashboard Scheduled Trigger form for the scheduled job being executed.

Executed by:
LWP_PRO2_HANDLE_TRIGGER
LWP_PRO2_GET_ORDER_DETAILS

LT_P2_SSX

This table is where Segment Transfer processing information is stored. Segment ID, List Manager List ID and the resulting list count are recorded.

Schema:

	[ssx_id] [int] NOT NULL,
	[lm_list_no] [int] NOT NULL,
	[qa_count] [int] NULL,
	[org_name] [varchar](30) NULL,
	[create_dt] [datetime] NULL,

This table is updated by:
LWP_PRO2_HANDLE_SSX

LT_PRO2_PROMOTION_LOG

This table is a mirror of LT_PRO2_PROMOTION_UPDATE, and is used as part of the Asynchronous Promotion Processing workflow. After promotions from LT_PRO2_PROMOTION_UPDATE are processed, they are moved to this table for archiving for up to 3 months. Promotion rows older than 3 months are automatically deleted by LP_P2_UPDATE_PROMOTIONS_IN_BULK each time that it is run.

Schema:

	[source_no] [int] NULL,
	[opened_at] [datetime] NULL,
	[email] [varchar](80) NULL,
	[campaign_id] [int] NULL,
	[response] [int] NULL,
	[action] [varchar](80) NULL,
	[customer_no] [int] NULL)

This table is updated by:
LP_P2_UPDATE_PROMOTIONS_IN_BULK

LT_PRO2_PROMOTION_UPDATE

When Asynchronous Promotion Processing is enabled, actions such as clicks/opens/unsubscribes are stored in this table for processing against the actual promotion records on constituent records in Tessitura at a later time. After action rows in this table are processed, they are moved to LT_PRO2_PROMOTION_LOG for archiving.

Schema:

	[uid] [int] IDENTITY(1,1),	
	[source_no] [int] NULL, --source_no to update promotion row for with appropriate action 
	[opened_at] [datetime] NULL, --date/time of the action 
	[email] [varchar](80) NULL, --email address that took the action 
	[campaign_id] [int] NULL, --Prospect2 campaign ID for which the action took place 
	[response] [int] NULL,  Maps to Tessituras promotion responses 
	[action] [varchar](80) NULL, --The type of action. Open/click/unsubscribe/etc. 
	[customer_no] [int] NULL --customer_no to update.

This table is updated by:
LWP_PRO2_HANDLE_CONTACT
LP_P2_UPDATE_PROMOTIONS_IN_BULK

LWP_PRO2_GET_SEGMENT

This procedure is responsible for gathering constituent information from a List Manager List for import of a new list into the Prospect2 dashboard.

Executed By:
LWP_PRO2_GET_ELIST_DATA

Signature:

	@lm_list_id int = NULL,
	@eaddress_type int = NULL,
	@source_no int = NULL,
	@only_primary varchar(1) = 'N',
	@output_set int = NULL,
	@contact_point_purposes varchar(150) = NULL,
	@org_name varchar(30) = 'Tessitura Web',
	@diff_only varchar(1) = 'N', -- Calculates list delta contacts for Data Sync jobs
	@review_only varchar(1) = 'N', -- Does not reset list snapshot in LT_P2_LIST_CONTENTS
	@customer_nos varchar(MAX) = NULL,
	@count_only varchar(1) = 'N', -- Returns only the count of the derived contacts
	@ignore_contact_perms varchar(1) = 'N',
	@skip_list_regenerate varchar(1) = 'N'

LWP_PRO2_GET_ELIST_DATA

This procedure is executed by the Prospect2 application when an import of contacts from Tessitura is requested by a user from the Prospect2 dashboard. This procedure is responsible for gathering information about the addresses selected as a part of the import. This procedure is required to be registered in TR_LOCAL_PROCEDURE.

Executed by:
Prospect2

Executes:
LWP_PRO2_GET_NEW_SEGMENT

Signature:

	@lm_list_id int = NULL,
	@eaddress_type int  = NULL,
	@only_primary varchar(1) = 'N',
	@contact_point_purposes varchar(150) = NULL,
	@org_name varchar(30) = 'Tessitura Web',
	@diff_only varchar(1) = 'N',
	@review_only varchar(1) = 'N',
	@customer_nos varchar(MAX) = NULL,
	@count_only varchar(1) = 'N',
	@ignore_contact_perms varchar(1) = 'N',
	@skip_list_regenerate varchar(1) = 'N'

When the procedure is executed to gather the contacts for import, it executes LWP_PRO2_GET_SEGMENT. If the List Manager List ID being passed in is marked as dynamic, then the procedure will attempt to regenerate that list at the point of execution.

Lists that take a significant amount of time to regenerate can cause the Tessitura API application to time out and the Prospect2 import to fail. By default, the Tessitura API application has a timeout period of 30 seconds that it waits for the database to respond. To extend this timeout period, organizations may adjust the web.config file for the Tessitura API application used by Prospect2. Please refer to Tessitura's documentation if this is required.

If a source number has been promoted to the List Manager List being used for the import, then this procedure will select the most recent source number promoted to the List Manager List ID being used, and return that to the Prospect2 dashboard for use in a custom field for each email address. This source_no will need to be associated with the email campaigns using the Promotions area in the Prospect2 Integrated Dashboard in order to update promotions records in Tessitura when actions are taken on those campaigns.

LWP_PRO2_GET_ELISTS

This procedure is responsible for gathering available List Manager Lists, Contact Point Purposes, Custom Views, Eaddress Types, Ticketing Keywords, Pricetypes, Version Data, Defaults Values and Special Activity types for use in Imports, Scheduled Triggers, Data Syncs and Segment Transfers.

Signature:

	@org_name varchar(30) = 'Tessitura Web'

Executed by:
Prospect2

LWP_PRO2_GET_ESUBSCRIPTIONS

This procedure returns data for the Tessitura custom screen and provides a reverse lookup for customer_nos associated with a provided email addresses. It retrieves all of the email addresses for a particular constituent and their subscription preferences for those addresses. Only addresses that are accessible to the Prospect2 API user will be displayed.

Signature:

	@customer_no int 		 = NULL,
	@email 		 varchar(80) = NULL,
	@org_name    varchar(30) = 'Tessitura Web'

LWP_PRO2_UPDATE_EADDRESS

This procedure is used for adding an email address to an existing constituent record.

Signature:

	@customer_no int = NULL,
	@email varchar(80) = NULL,
	@org_name varchar(30) = 'Tessitura Web'

LWP_PRO2_HANDLE_CONTACT_LOCAL

This procedure is provided as a shell if you want to extend the functionality beyond standard P2 promotions updating. For example, if you wanted to set an Attribute on the constituent record, or mark the “No Emarketing” flag when someone unsubscribed, you could write in the functionality to do so.

Signature:

	@address varchar(80) = NULL,
	@action varchar(80) = NULL,
	@list_id int = NULL,
	@opened_at datetime = NULL,
	@campaign_id int = NULL,
	@parent_key varchar(30) = ''Tessitura Web'', -- retained @parent_key for legacy compatibility
	@list_name varchar(100) = NULL,
	@list_type char(10) = NULL,
	@source_no datetime =  NULL,
	@response int = NULL

LWP_PRO2_HANDLE_CONTACT

This procedure is responsible for updating actions taken against deployed campaigns in Tessitura's T_PROMOTIONS table.

Signature:

	@address varchar(80) = NULL,
	@action varchar(80) = NULL,
	@list_id int = NULL,
	@opened_at datetime = NULL,
	@campaign_id int = NULL,
	@org_name varchar(30) = 'Tessitura Web',
	@list_name varchar(100) = NULL,
	@list_type char(10) = NULL,
	@source_no int = NULL,
	@create_promos varchar(1) = 'N'

If the T_DEFAULT P2UpdatePromoInBulk is set to 'N', this procedure updates promotions for the following actions immediately upon receipt of the communication from Prospect2:

• campaign click
• campaign open
• campaign unsubscribe
• campaign hard bounce

Executed by:
LWP_PRO2_HANDLE_WEB_HOOK

Executes: 
LWP_PRO2_HANDLE_CONTACT_LOCAL

Updates Tables:
T_PROMOTION or LT_PRO2_PROMOTION_UPDATE

LWP_PRO2_HANDLE_WEB_HOOKS

This procedure is the primary entry point for webhook processing from Prospect2. Depending on the type of webhook being processed, this procedure will call the relevant Prospect2 procedure for the hook type.

Signature:

	@type varchar(80), -- Webhook type (eg. click, open, unsubscribe, email_changed)
	@email varchar(80),
	@list_id int = NULL,
	@tag varchar(80) = NULL,
	@opened_at datetime = NULL,
	@campaign_id int = NULL,
	@list_name varchar(100) = NULL,
	@old_email varchar(80) = NULL,
	@source_no int = NULL,
	@phone varchar(32) = NULL,
	@org_name varchar(30) = 'Tessitura Web',
	@create_promos varchar(1) = 'N',
	@remove varchar(1) = 'N'

Executes:
LWP_PRO2_HANDLE_CONTACT

LWP_PRO2_GET_CUSTOM_VIEW

This procedure is responsible for selecting eligible rows from a view defined by the client organization as part of a custom view-based Scheduled Trigger. The view can contain as many fields as the organization would like, and all of them are returned to the Prospect2 application as output of this procedure for use as personalization tags in the campaigns that are deployed as part of the scheduled job.

Signature:

	@view varchar(64) = NULL,
    @start_date datetime = NULL,
    @start_date datetime = NULL,
    @search_field varchar(80) = NULL,
    @search_value varchar(80) = NULL,
    @contact_limit int = 0 -- Used to return a limited set of data either for Data Preview or validation of data

Any rows in the local view defined by the @view parameter where the [custom_date] field is greater than or equal to the [start_date] and less than the [start_date] will be selected as eligible. If the local view doesn't have a [custom_date] field, then an exception is thrown and no data is returned.

Executed by:
LWP_PRO2_HANDLE_TRIGGER

LWP_PRO2_GET_ORDER_DETAILS

This procedure is responsible for gathering information about orders related to performances selected by LFT_PRO2_GET_ELIGIBLE_PERFS for performance based Scheduled Trigger. This procedure is dependent on LFT_PRO2_GET_ELIGIBLE_PERFS and joins to it on [perf_no] to select appropriate sub-line items for the scheduled job being executed. This procedure is only executed for performance-based Scheduled Triggers.

Executed By:
LWP_PRO2_HANDLE_TRIGGER

Executes:
LFT_PRO2_GET_ELIGIBLE_PERFS

Signature:

    @start_date              datetime     = NULL,
    @end_date                datetime     = NULL,
    @with_keyword            varchar(500) = NULL,
    @without_keyword         varchar(500) = NULL,
    @with_pricetype          varchar(500) = NULL,
    @without_pricetype       varchar(500) = NULL,
    @suppress_season_types   varchar(64)  = NULL,
    @suppress_perf_types     varchar(64)  = NULL,
    @suppress_constituencies varchar(64)  = NULL,
    @list_category           int          = NULL,
    @suppress_lists          varchar(64)  = NULL,
    @order_no                varchar(64)  = NULL,
    @org_name                varchar(30)  = 'Tessitura Web',
    @review_only             varchar(1)   = 'N',
    @contact_limit           int 		  = 0

T_DEFAULT fields:

P2DefaultEaddressType
Used in the hierarchy of address selection for the email addresses associated with the orders for which an automated campaign is being deployed. See section below on email address hierarchy.

P2DefaultSliStatusList
This is a comma-separated list of SLI statuses from TR_SLI_STATUS that are selected when the procedure collects orders that have tickets to an eligible performance. If nothing is populated in this T_DEFAULT, the following SLI statuses are eligible: 2,3,6,12. This ensures that canceled, returned, or other undesirable SLI statuses are not selected for any automated performance-based campaigns.

Address Selection Hierarchy

Because there isn't always an eaddress_no present in the [eaddress_no] field of T_ORDER for orders that are created, there needs to be a deeper eaddress selection hierarchy that uncovers an address that can be used for the constituents that have tickets to a performance. If an organization is in a consortium, or if orders are placed at the household level but email addresses are stored at the individual level, several tiers must be parsed in order to derive a valid recipient email address for the automated campaign.The hierarchy is defined as follows:

1. Use the address identified on the order itself in T_ORDER ([eaddress_no] field) if exists and it is active.
2. Use the top 1 active address with the Prospect2 default eaddress type associated with the customer on the order (Uses T_DEFAULT P2DefaultEaddressType).
3. Use the top 1 active address on the customer_no associated with the order that has the [primary_ind] = 'Y'. (It is the primary email on the record.)
4. Use the top 1 active address with the Prospect2 default eaddress type associated with the primary affiliate of the customer_no on the order (Uses T_DEFAULT P2DefaultEaddressType).
5. Use the top 1 active address on the primary affiliate of the customer_no associated with the order that has the [primary_ind] = 'Y'. (It is the primary email on the record.)

The selection process will follow that hierarchy and select the first non-null value among the 5 options.

List Manager List Creation

Whenever this procedure is run, it will create an audit list, of all customer_nos selected using the criteria that are passed into it. This list is for audit purposes only, and is not used by the Prospect2 application for any reason. If this behavior is not desirable, the functionality may be commented out in this procedure.

The audit list created will have a prefix of PRO2-SCH: and each time the job runs, it will remove audit lists and contents older than 30 days to prevent database clutter.

LWP_PRO2_HANDLE_TRIGGER

This procedure is called from Prospect2 application as the first step in executing a Scheduled Trigger.

This procedure is required to be registered in TR_LOCAL_PROCEDURE.

Executed By:
Prospect2

Depends on procedures:
LFT_PRO2_GET_ELIGIBLE_PERFS
LWP_PRO2_GET_ORDER_DETAILS
LWP_PRO2_GET_CUSTOM_VIEW

If the Scheduled Trigger is performance-based:

This procedure calls procedures LFT_PRO2_GET_ELIGIBLE_PERFS and LWP_PRO2_GET_ORDER_DETAILS. The following T_DEFAULT data is gathered:

• P2SuppressSeasonTypes
• P2SuppressConstituencies
• P2ScheduledListCat
• P2SuppressPerfTypes
• P2ListManagerSupressions [If the list ID(s) provided here are checked as dynamic, they will be regenerated]

A performance data set is returned that contains information related to the performance(s) for which a reminder is being sent. This is the output of LFT_PRO2_GET_ELIGIBLE_PERFS. An order data set is returned that contains information about the orders and constituents that have sub-line-items on an order for one of the performances in LFT_PRO2_GET_ELIGIBLE_PERFS.

Web content will also be returned for all performances identified as eligible. The web content can exist on any level associated with the performance (Title, Production, Production Season or Performance). This data is returned by the LWP_PRO2_GET_WEB_CONTENT procedure.

If the Scheduled Trigger is custom view-based:

When executed for a Custom view-based Scheduled Trigger, this procedure calls LWP_PRO2_GET_CUSTOM_VIEW. LWP_PRO2_HANDLE_TRIGGER is responsible for gathering the data for any Scheduled Triggers. No T_DEFAULT data is retrieved for custom view-based campaigns. An data set is returned that contains any of the fields selected from the custom view being used and the custom_date falls between the passed in start_date and end_date parameters.

LWP_PRO2_HANDLE_SSX

This procedure processes email addresses passed in by a Segment Transfer. It’s primary job is to identify any and all relevant constituent records that are associated with the provided email addresses. Constituents with existing email addresses will be added to the List Manager list with the name provided in the Prospect2 Segment Transfer form and recorded in the LT_P2_SSX table. Email addresses that aren't found in Tessitura will be returned for reporting to the Prospect2 integrated dashboard.

Signature:

	@list_name varchar(30),
	@email_string varchar(8000) = '',
	@ssx_id int = NULL,
	@exclude_households varchar(1) = 'N',
	@only_primary varchar(1) = 'N',
	@lm_list_no int = 0,
	@org_name varchar(30) = 'Tessitura Web'

Executed By:
Prospect2

LWP_PRO2_UPDATE_PHONE

This is a wrapper procedure for WP_MAINTAIN_PHONE. Used to insert or update phones by Prospect2.

Signature:

    @customer_no int = NULL,
    @phone varchar(32) = NULL,
    @org_name varchar(30) = 'Tessitura Web',
    @remove varchar(1) = NULL

Executed By:
Prospect2

LFS_PRO2_CURRENT_VERSION

This function accepts no parameters. The usage is intended to more reliably return the current installed Tessitura version.

This function is called by:
LWP_PRO2_GET_ELISTS

LWP_PRO2_SYNC_PURPOSES

Signature:

	@email varchar(80) = NULL,
	@list_id int = 0,
	@type varchar(80) = NULL,
	@tag varchar(80) = NULL,
	@org_name varchar(30) = 'Tessitura Web'

The procedure that handles the updating of the TX_CONTACT_POINT_PURPOSE table (adding/removing Contact Point Purposes) when a list subscribe/unsubscribe or tag add/remove webhook is processed. It handles the lookup in the LTR_P2_ELISTS to identify if a list id has a CPP assigned. a

This procedure updates the following tables:
TX_CONTACT_POINT_PURPOSE

LRP_P2_UPDATE_PURPOSES

Signature:

	@range int

Webhooks that are processed for email addresses that don't exist in Tessitura are stored in a local db table named LTX_P2_UNASSIGNED_EMAILS. This procedure is implemented as a SQL job to periodically check for new email addresses created in Tessitura and compare those to existing CPP records in the LTX_P2_UNASSIGNED_EMAILS table, then assign appropriate CPPs to the new emails.

The @range parameter is the number of minutes back the procedure should search VS_EADDRESS for new emails to process. Only email addresses with the email_ind value of 'Y' will be evaluated.

This procedure calls the following procedure:
LWP_PRO2_SYNC_PURPOSES

LP_P2_UPDATE_PROMOTIONS_IN_BULK

This procedure accepts no parameters. This procedure is intended to be used as part of the Asynchronous Promotion Processing workflow. This procedure should be scheduled to run as often as your organization requires and processes any rows present in the local table LT_PRO2_PROMOTION_UPDATE. This procedure does the following actions:

Retrieves any stored promotion actions from LT_P2_PROMOTION_UPDATE that need to be updated in Tessitura's native T_PROMOTION table. Updates the promotions as required. Truncates LT_PRO2_PROMOTION_UPDATE and logs any promotions processed as part of this process in LT_PRO2_PROMOTION_LOG Deletes any rows in the LT_PRO2_PROMOTION_LOG table that are older than 3 months.

This procedure updates the following tables:
LT_PRO2_PROMOTION_UPDATE
LT_PRO2_PROMOTION_LOG