Vocus/Cision Setup

Abila will complete the setup tasks outlined below.

Setup a Mailing List

Create a mailing list in the Marketing module with the name of VOCUS. Add individuals that need to be updated with Vocus into the mailing list.

Note: The start date of each list member has no effect for the integration.

For any new client, before the integration can be initiated, Abila needs to populate this mailing list based on the client's business rules (no more than 2 hours of work as a baseline service). The list name VOCUS is hard-coded for the integration and cannot be changed.

After the initial list is populated, it is the client's responsibility to maintain this mailing list for any new additions or deletions.

Setup Update Routine

Abila will create a SQL scheduled job that will update the mailing list records based on changes made during normal NetForum integration. The logic in this store procedure [client_xxx_update_vocus_list] is explained later in this document.

1. Create an SQL Job in the SQL server and run as SQL Login netForumAdminUser.
2. Create a Step in the Job to execute an update store procedure in SQL server.
3. Create a Schedule for this job to run every night.
4. Save the job when done.
5. Create a system option called VocusIntegrationMailingListKey whose value is the mailing list primary key [mk_mailing_list.mls_key] of the VOCUS mailing list (created earlier) that need to be updated through the update routine.

Setup XML Web Service

1. Add an xml web service: in the Toolkit module.Name the service VocusIntegration and give it the namespace VocusIntegration.GetVocusCustomers:
   
2. Add a web method to the Web Service called GetVocusCustomers.
   
3. Add a node to GetVocusCustomers method that calls the stored procedure to return the customer update list.
   

   The SQL will be the following, although the SP name will change:

   execute client_xxx_get_vocus_list @ListCode ={ListCode},@date_from ={DateFrom},@date_to ={DateTo}@rows_returned ={RowsReturned},@minRecNo ={MinRecNo}

 

 

4. The stored procedure client_xxx_get_vocus_list is written to return all the mailing list members that meet the filter passed in (based on the parameters) and generate the XML file with customers and their related information for VOCUS to process.

Client Setup Tasks (Maintain the VOCUS Lists )

The client will be responsible for the setup tasks described below.

Synch Individual in iWeb

Flag an individual that needs be synced with VOCUS:.

User does not need to manually track which member records in the mailing list needs to sync with VOCUS. The update routine (store procedure client_xxx_update_vocus_list) in the previous section updates the change date (mld_change_date) of the member entry with the latest add/change dates of the member"™s records in the following tables:

• co_customer.
• co_individual.
• co_customer_x_address.
• co_address.
• co_customer_x_phone.
• co_phone.
• co_customer_x_fax.
• co_fax.
• co_email.

VOCUS application calls the NetForum web service to request a list of changed individuals within a period of time. The time frame should be set accordingly based on the frequency of the sync to reduce redundancy.

Remove Individual in iWeb

To remove an individual in VOCUS from NetForum, the client needs to enter the end date of the list member of the "VOCUS" mailing list. The individual will be marked with the action as "DELETE" in the XML output file from the xWeb call. The client should not delete the list member records in the mailing list; instead, the client should enter the "end date".

Add a New Individual in iWeb to be Synched with Vocus

If a new customer is added in NetForum and the client decides this record is needed for VOCUS integration, theclient will add this customer into the VOCUS mailing list. In the scenario when an individual was in the member list before but it has an"end date", the client should not remove the end date from the entry. Instead, the client should add a new list member.

The following section describes the detail technical implementation of the xWeb service calls.

Overall Methodology

VOCUS will use xWeb to call a web method called ExecuteMethod. In the topic, some code samples have been provided for VOCUS' reference. Abila will have created the stored procedure (setup above) with parameters for this method to call and return the designed results.

Name	Value
Web Service Name	VocusIntegration
Web Service Method	GetVocusCustomers
Parameter 1	ListCode
Value 1	VOCUS
Parameter 2	DateFrom
Value 2	Date that the insert/update/delete takes place starts
Parameter 3	DateTo
Value 3	Date that the insert/update/delete takes place ends
Parameter 4	RowsReturned
Value 4	How many rows to return for each pull (this includes inserts, updates and deletes)
Parameter 5	MinRecNo
Value 5	The Record Number that needs to be greater than in the result set

MinRecNo is used to avoid returning too many rows that will timeout the system. Abila will return the record set order by the record number (cst_recno). VOCUS should get the last highest record number from the previous pull and then issue another query to get the next record set.

The stored procedure has been created to accept the 5 parameters and return the information that VOCUS needs:

The SP must return the information about the customer name, addresses, phones, faxes, email, etc., based on the parameters. Abila will use the date ranges to determine any contact information changed during the time frame. All tables that involve contact information will need to be considered in this store procedure.

VOCUS will take the XML file and use the cst_key to determine if the record already exists in VOCUS or not. If the cst_key exists in VOCUS, VOCUS will update the record. If the cst_key does not exist, then it will be an insert. VOCUS does not need to know the netFORUM business rules and only needs to run this query and pass in the parameters and process the result set. Even though the virtual column "action" is part of the XML output, VOCUS should still use the cst_key to determine if the customer record should be an insert or update. The "Delete" action can be used to determine if a record should be deleted on the VOCUS side.

Following is a screenshot of the output XML file. The file includes customer information and all the related contact information (all addresses, phones, faxes that a customer has). Any column that is not in this file layout is not considered part of the baseline offering.

The client will go to the mailing list member record and fill in an end date for the customer record that is supposed be removed from VOCUS. This is a manual process performed by the client.

In the result set (from the SP), Abila will add a virtual column called "action" which will return the value of "Insert", "Update" or "Delete". If the end date of a mailing list member record falls into the date parameter passed in, this record will be returned in the result set with the action column of "Delete" and VOCUS will process this record accordingly on the VOCUS end.

Client Test Site XWEB

http://client_server/netforumxxxtest/xweb/secure/netforumxml.asmx

xWeb Login: xxxxxxxPassword: xxxxxx

xWeb login and password will be created by Abila for the client to use.

To test, use the NetForum xWeb Laboratory:

http://xweblab.avectra.com

VOCUS will perforum the following steps.

Connect to the Web Service

The formula for the web service WSDL of the test environment is as follows:

http://client_server/netforumxxxtest/xweb/secure/netforumxml.asmx?WSDL

Get Token

Run the Authenticate web method using the credentials below:

Login: xxxxxxx Password: xxxxxxxxxx

The returned xml contains a token to be used for the following call.

ExecuteMethod

Use ExecuteMethod to receive update list for VOCUS.

Use the previous token returned from the Authenticate method to execute ExecuteMethod web method. Use service name "VocusIntegration" and method name "GetVocusCustomers".

You need to pass 5 parameters: "ListCode", "DateFrom", "DateTo", "RowReturned", and "MinRecNo". See the Overall Methodology section above for more information on these parameters.

The execute time varies depending on the number of records in the member lists, the number of records in the individual table, and the number of records returned in every execution.

Each sync session involves multiple execution of the same web method until it reaches the end. For instance, 700 records changed in 1/3/2009 and VOCUS integration application wants to sync data that are changed from 1/3/2009 to 1/3/2009. If "RowsReturned" was set to 300, the application needs 3 executions of the web method to complete the list (the first execution updates the first 300 records, the second updates the following 300 records, and the last updates the remaining 100 records.). For "RowsReturned" parameter set to 100, the application would need 7 executions to complete (each execution updates 100 records).

To complete a sync session, the first web method execution of one sync session should set "MinRecNo" = 0. By comparing the number returned of records to "RowsReturned", the application can decide if there are more records behind. If the number of returned record is same as "RowsReturned", there would be more records after the last record. The application should set the "MinRecNo" to the "cst_recno" of the last record and execute the web method again to receive the next set of records. If the number of returned record is less then "RowsReturned", there would be no records after the last record and the application can stop.

Due to the volume of data for the initial data load to populate VOCUS database, the first time to insert all the customers into VOCUS will be done through a file import. Abila will provide an XML file that has all the customers that are needed for VOCUS to process. The initial file format for the import should be the same as the XML file for the daily synchronization.