This procedure is responsible for gathering information about orders related to performances selected by LFT_M2_GET_ELIGIBLE_PERFS. This procedure is dependant on LFT_M2_GET_ELIGIBLE_PERFS and joins to it on [perf_no] to select appropriate sub-lineitems for the scheudled job being executed. This procedure is only executed for performance-based scheduled campaigns.
@sessionKey varchar(64) = NULL, @startDate datetime = NULL, @endDate datetime = NULL, @withKeyword varchar (500) = null, @withoutKeyword varchar (500)= null, @withPricetype varchar (500) = null, @withoutPricetype varchar (500) = null, @suppressSeasonTypes varchar(64) = null, @suppressPerfTypes varchar(64) = null, @suppressConst varchar(64) = null, @listCat int = null, @suppressLists varchar(64) = null
This procedure uses the following t_defaults fields:
- m2DefaultEaddressType Use in the hierarchy of address selection for the email address assoiciated with the orders for which an automated campaign is being deployed. See section below on email address hierarchy.
- m2DefaultControlGroup The control group used when creating a List Manager list of customer_nos selected for a scheduled job when it runs. The control_group field in T_LIST is not nullable, and so a default control group is required for proper operation of this procedure, and should be populated in T_DEFAULTS. The default control group ID of -1 may be used.
- m2DefaultSliStatusList 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 undesireable SLI statuses are not selected for any automated perfromance-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 patrons 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 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:
- Use the address identified on the order itself in T_ORDER ([eaddress_no] field) if it is active.
- Use the top 1 active address with the mail2 default eaddress type associated with the customer on the order (Uses T_DEFAULT m2DefaultEaddressType).
- 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.)
- Use the top 1 active address with the mail2 default eaddress type associated with the primary affiliate of the customer_no on the order (Uses T_DEFAULT m2DefaultEaddressType).
- 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 that is present.
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 in to it. This list is for audit purposes only, and is not used by the mail2 application for any reason. If this behavior is not desireable, lines 207 - 269 may be commented out in this procedure. This includes the statments that are responsible for creating the list manager list.
IF @@ROWCOUNT >0 BEGIN --Create List Manager of eligible customer_nos EXEC @listID = [dbo].ap_get_nextid_function @type = 'LI'
DECLARE @theDateTime datetime SET @theDateTime = CURRENT_TIMESTAMP
INSERT INTO [impresario].[dbo].[T_LIST] ([list_no] ,[list_desc] ,[list_rights] ,[num_members] ,[criteria] ,[last_used_dt] ,[created_by] ,[create_dt] ,[last_updated_by] ,[last_update_dt] ,[edit_flag] ,[recalc_status] ,[control_group] ,[tms] ,[inactive] ,[category] ,[tstats]) VALUES (@listID, 'Sched:'+ convert(varchar(10),@startDate,101)+'->'+ CONVERT(varchar(10),@endDate,101) ,NULL ,0 ,NULL ,@theDateTime ,CURRENT_USER ,@theDateTime ,CURRENT_USER ,@theDateTime ,'Y' ,'S' ,@default_control_group ,'N' ,'N' ,@listCat ,'N') INSERT INTO T_LIST_CONTENTS (customer_no,list_no) SELECT DISTINCT(o.customer_no),@listID FROM #theOrders o JOIN T_CUSTOMER ci WITH (nolock) ON o.customer_no = ci.customer_no WHERE ci.inactive = 1 AND (ISNULL(@suppressConst,'') = ' ' or o.customer_no not in (SELECT customer_no FROM VXS_CONST_CUST cc WITH (Nolock) WHERE cc.constituency in (SELECT element FROM FT_SPLIT_LIST(@suppressConst,',')))) AND (ISNULL(@suppressLists,'') = '' or o.customer_no not in (SELECT customer_no FROM T_LIST_CONTENTS tc WITH(NOLOCK) WHERE tc.list_no IN (SELECT element FROM FT_SPLIT_LIST(@suppressLists,',')) )) UPDATE [impresario].[dbo].[T_LIST] SET [num_members] = ( SELECT COUNT(*) FROM T_LIST_CONTENTS WHERE list_no = @listID ) WHERE list_no = @listID END