- Contents
Interaction Dialer Manager Help
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.