The Global Address Validation task in an Orchestrate workflow uses the Melissa Global Address Validation service. Refer to Melissa Global Address Verification for more information about this service.
Prerequisites
To set the Syniti Knowledge Platform up to use this service, Melissa configuration must be added to the tenant that is using the service. It is recommended that the following values are used on the Admin Orchestration Parameters page of the Syniti Knowledge Platform:
- Melissa API Base—http://address.melissadata.net/V3/WEB/GlobalAddress
- Melissa Customer ID—Email mdprovisioning@Syniti.com to obtain a Melissa customer account
- Melissa API Timeout Seconds—30
- Melissa Max Request Records—99
When creating an Orchestrate workflow that uses a Global Address Validation task, provide the following key information:
- Select SQL Statement—This statement selects data from a table that contains address data to be validated and maps it to the input columns of the Melissa Global Address service via the naming of the columns. Refer to the Input Fields section in the Melissa Global Address Quickstart Guide for details on the input field.
- Insert SQL Statement—This statement returns the response from the Melissa Global Address service and inserts it into an appropriate response table. Refer to the Input Fields section in the Melissa Global Address Quickstart Guide for details on the service output fields.
NOTE: The fields to be inserted into the response tables must be prefixed with : (colon) to indicate that data is coming from an internal Orchestrate variable. For example:
INSERT INTO dbo.ttAddressResponse (
RecordID,
TransmissionReference,
Results,
FormattedAddress,
Organization,
AddressLine1,
VALUES (
:RecordID,
:TransmissionReference,
:Results,
:FormattedAddress,
:Organization,
:AddressLine1 )
Implementation
In order to enable customers to quickly build solutions that leverage the Global Address Validation service, follow these steps:
- Identify or create a database that will provide the source of address data to be validated.
- Create a system and connection in the Syniti Knowledge Platform that will communicate with this database.
- Create the Address Input Table that will be the source of address data to be validated:
CREATE TABLE [dbo].[ttAddress](
[RecordID] [int] IDENTITY(1,1) NOT NULL,
[RequestID] [int] NULL,
[Organization] [nvarchar](100) NULL,
[AddressLine1] [nvarchar](100) NULL,
[AddressLine2] [nvarchar](100) NULL,
[AddressLine3] [nvarchar](100) NULL,
[AddressLine4] [nvarchar](100) NULL,
[AddressLine5] [nvarchar](100) NULL,
[AddressLine6] [nvarchar](100) NULL,
[AddressLine7] [nvarchar](100) NULL,
[AddressLine8] [nvarchar](100) NULL,
[DoubleDependentLocality] [nvarchar](100) NULL,
[DependentLocality] [nvarchar](100) NULL,
[Locality] [nvarchar](100) NULL,
[SubAdministrativeArea] [nvarchar](100) NULL,
[AdministrativeArea] [nvarchar](100) NULL,
[SubNationalArea] [nvarchar](100) NULL,
[PostalCode] [nvarchar](100) NULL,
[Country] [nvarchar](100) NULL,
[ExternalID] [nvarchar](100) NULL,
CONSTRAINT [PK_ttAddress] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO - Create the Address Response Output Table into which the service will return the validation. Download the Address Response Output Table file.
- Create the Address Response Code Output Table into which the string of comma-separated address validation response codes will be transposed into records in a table.
CREATE TABLE [dbo].[ttAddressResponseCodes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AddressResponseID] [int] NULL,
[ResultCode] [nvarchar](100) NULL,
CONSTRAINT [PK_ttAddressResponseCodes] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO - Create the Address Response Code transposition stored procedure.
CREATE PROCEDURE [dbo].[trAddressResponseCodeIns]
AS
BEGIN
INSERT INTO [dbo].[ttAddressResponseCodes]
(AddressResponseID,
ResultCode)
select ID, cs.Value as ResultCode--SplitData
from dbo.ttAddressResponse
cross apply STRING_SPLIT (Results, ',') cs
WHERE NOT EXISTS
(Select AddressResponseID from [dbo].[ttAddressResponseCodes] where AddressResponseID = dbo.ttAddressResponse.ID);
END
GO - Create the Address Response Code Information Table.
CREATE TABLE [dbo].[ztResponseCode](
[ResponseCode] [nvarchar](20) NOT NULL,
[ShortDescription] [nvarchar](100) NOT NULL,
[LongDescription] [nvarchar](1000) NULL,
[ResponseType] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_ztResponseCode_1] PRIMARY KEY CLUSTERED
(
[ResponseCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO - Insert the Melissa Global Address Response Codes into the Information Table. Download the Melissa Global Address Response Codes file.
- Go to the Syniti Knowledge Platform Orchestrate module.
- Click the Create workflow button.
- Enter TransmissionReference as a variable name in the Name-Value Pair field.
- Click the Add Task button and choose Global Address Validation as the Task Type.
- Select the system created in Step 2 from the System list box.
- Select the connection created in Step 2 from the Connection list box.
- Select the SKP Connector assigned to the connection in Step 2 from the SKP Connector list box.
- Enter the select SQL statement in the Select SQL Statement field. The attached select SQL statement reads the data from the ttAddress table created in Step 3 and passes it to the Melissa Global Address service. Download the select SQL statement file.
NOTE: In this example, RecordIDs that exist in the ttAddressResponse table created in Step 4 are excluded from the selection. This prevents sending records to the validation service multiple times. - Enter the insert SQL statement in the Insert SQL Statement field. The attached insert statement takes the response of the Melissa Global Address service and inserts it into the ttAddressResponse table created in Step 4. Download the insert SQL statement file.
NOTE: When executing the workflow, a value can be provided for the TransmissionReference variable. This is stored in the TransmissionReference field of the ttAddressResponse table.
- Click the Save & add another button. The task is saved to the workflow and a new task can be added.
- Select SQL Task from the Task Type list box to add a new SQL task to the workflow. This new task is to execute the Result Code transposition stored procedure created in Step 6.
- Enter a Name for the task.
- Select the system created in Step 2 from the System list box.
- Select the connection created in Step 2 from the Connection list box.
- Select the SKP Connector assigned to the connection created in Step 2.
- Enter the following SQL query:
Execute [dbo].[trAddressResponseCodeIns] - Click the Save button.
- Select the Global Address Validation task from the Dependencies field of the SQL task. This ensures the task to transpose result codes runs after the Global Address service call has been completed.
- Click the Save button to save the workflow.
- Click the Execute workflow button on the Global Address Validation workflow Overview page to manually execute the workflow.
- Enter a value for the TransmissionReference variable on the Variables:On-Run Values page.
- Click the Execute workflow button.
- In the database where the tables were created, review the results in the ttAddressResponse table along with the result codes and their meaning in the ttAddressResponse and ztResponseCodes tables.