Custom Stored Procedure behavior type

Overview

This policy disposition behavior type (Custom Stored Procedure) allows customers to execute SQL at the end of any type of call, potentially to update a table outside of Dialer. For example, once a number has been dialed, it could be added to a custom exclusion table, so that the number is never dialed again, because it has been dealt with.

To implement custom SQL, customers add custom code to a stored procedure (sproc) in the contact list database. The sproc takes any arbitrary action based on parameters passed to it.

The sproc is only engaged if customers set up a Policy disposition behavior (Custom Stored Procedure). Dialer automatically passes call attributes to the sproc, along with additional information (defined in the behavior) that is not already attached to the call object.

Customers are responsible for coding whatever the stored procedure should do, and also for configuring a Policy disposition behavior to invoke the procedure. The stored procedure must be in the CallList. It cannot be in the CIC database.

Usage

This policy disposition behavior can be used to query against a custom Phone Number Detail column. It invokes a sproc in the call list database to perform a logical operation of some sort, such as altering records in the call list database or altering entries in custom columns. The procedure accepts two optional character strings for custom information.

The data for the sproc call is sent from the ODS to the CCS as part of the call list update. If the call list update fails for any reason, then the entire update is sent to PMQ for retry and the custom sproc is not called. If the call list update succeeds, then the custom sproc data will be packaged into a separate PMQ message and queued.

Custom sproc PMQ messages appear in the I3\IC|PMQ\UpdateServer3\Disposition_Custom_Sproc directory in the CCS. This ensures that the call list update will always happen first, and that the custom sproc call is made. If a custom sproc call fails, it will be retried the same number of times as the call list update is attempted.

User Interface

Name

Type a name for the new behavior in the Name field. This can be anything you like, so long as it is unique within the policy. Try to use of a descriptive name that describes what this behavior does when its condition is true.

Behavior Type

Select "Custom Stored Procedure" to display options that configure this condition.

Custom data 1

Any additional information not already attached to the call object that will be passed to the sproc in @i_CustomArgument1.

Custom data 2

Any additional information not already attached to the call object that will be passed to the sproc in @i_CustomArgument2.

If you want to use a macro in the custom data fields, click the Macro () button. (For more information, see the Macros topic.)

Parameters passed to stored procedure

This behavior always calls a sproc named "spid_disposition_custom".  If a sproc of that name doesn't exist in the call list database for a campaign, a sproc with that name will be created with the proper arguments and an empty body. Dialer passes the following parameters to the stored procedure:

SQL Server parameter name	SQL Server parameter type	Oracle parameter name	Oracle parameter type	Description
@i_CampaignName	nvarchar(255)	i_CampaignName	nvarchar2	Name of the campaign
@i_ContactIdentity	bigint	i_ContactIdentity	number	The value of the call list and PND table's i3_identity columns. Uniquely identifies a contact.
@i_ContactColumnId	bigint	i_ContactColumnId	number	The value of the column id containing the phone number called for the contact that resulted in the contact disposition. Uniquely identifies a phone number and PND row (in addition to the PND i3_identity column).
@i_ContactColumnName	nvarchar(255)	i_ContactColumnName	nvarchar2	The name of the column in the call list table that contains the phone number called for the contact that resulted in the contact disposition.
@i_ContactPhoneNumber	nvarchar(255)	i_ContactPhoneNumber	nvarchar2	The phone number called for the contact that resulted in the contact disposition.
@i_InteractionId	nvarchar(255)	i_InteractionId	nvarchar2	The CIC interaction id of the call.
@i_WrapupCode	nvarchar(255)	i_WrapupCode	nvarchar2	The wrap-up code resulting from the call dispostion.
@i_CallListTableName	nvarchar(255)	i_CallListTableName	nvarchar2	The name of the call list table. This value is NOT escaped.
@i_CustomArgument1	nvarchar(2000)	i_CustomArgument1	nvarchar2	The contents of the Custom Data 1 field from the Custom Stored Procedure dialog.
@i_CustomArgument2	nvarchar(2000)	i_CustomArgument2	nvarchar2	The contents of the Custom Data 2 field from the Custom Stored Procedure dialog.

Since Policy Sets can be assigned to multiple campaigns, care should be taken to ensure that the call list used by the campaign actually contains the columns required by the policy.