Table of Contents

• Integrate Entity Validation Melissa Address Validation Service
  • Overview
  • Create Entity Validation Melissa Data Request
  • Add Addresses to the Entity Validation Request
  • Send Request Data to Melissa Address Validation Service
  • Review and Approve Validated Address Data
  • Consume Address Validation Results
• Integrate Entity Validation Melissa Data Call into another Application
  • Overview
  • Generate an Entity Validation Request
  • Add Source Address Records to the Request
  • Create a Button and Event on your page to Validate Address
  • Update the Source Address Record
  • Call the Process Request Event
  • Parse the Responses/Display Response Status
  • Register Rules on the Validate Address Event
    • Register a Web App Event
  • View the Response Messages
  • Update Your Address
  • Delete a Request
  • Entity Validation API Procedures
  • Process Request Public Event
  • Template SQL Statements
    • Add EVRequestID Column to a table
    • Template SQL Statements
    • Add EVRecordID and EVResponseStatus Columns to a table
    • Update the Source Address Record
    • Process Request Event View
    • Parse the Responses
    • Add Buttons to Call Address Validation and View Responses
    • Updating the Address

 

Overview

The Entity Validation application, included in the Stewardship Tier 7.4.6 and later, includes a request type called ‘Melissa Data’ that allows one or more addresses to be submitted to Melissa Address Validation Service and for the results to be reviewed, approved and consumed as required. This article details the key steps of the address validation process and explains some of the key features that are available to programmatically execute the steps. It also provides a detailed worked example that shows how a Master Data Management request-based process can leverage this feature.

 

The key steps of the Address Validation process are:

1. Create Entity Validation ‘Melissa Data’ Request
2. Add Addresses to the Entity Validation Request
3. Send Request Data to Melissa Address Validation Service
4. Review and Approve Validated Address Data
5. Consume Address Validation results

 

Create Entity Validation Melissa Data Request

 

Entity Validation requests are stored in table [EntityVal].[dbo].[ttRequest]. New requests can be created programmatically using the stored procedure [EntityVal].[dbo].[apiCreateRequest]. This procedure requires the following parameters:

 

This procedure passes the new request ID as an output parameter (@newRequestId). This value must be passed to the next step in the process.

 

Add Addresses to the Entity Validation Request

 

The data for Entity Validation requests of type ‘Melissa Data’ is stored in table [EntityVal].[dbo].[ttAddressSource]. Address Data can be programmatically added to Melissa Data requests using stored procedure [EntityVal].[dbo].[apiSourceAddressIns]. This procedure allows the following parameters:

 

 

This procedure will pass the record id as an output parameter (@newRecordId).

 

Send Request Data to Melissa Address Validation Service

 

Entity Validation Request data can be programmatically sent to the Melissa Address Validation using two different techniques:

 

1. Via Stewardship Tier Applications

   NOTE: Register Event [Entity Validation:Request:ProcessRequest] as a Business Rule.

   Review the example below in this article for further details.

2. Stored Procedure

   NOTE: Call stored procedure [EntityVal].[dbo].[apiMelissaAddressValidationService]

   while passing in the RequestID of the request to send to the address validation service into parameter @RequestID

 

The results of the Melissa Address Validation Service are saved in table [EntityVal].[dbo].[ttMelissaGlobalAddressResponseAddress]. In addition to the response data, the service also returns an array of different response codes that provide important information related to the validation results. These response codes are saved in table [EntityVal].[dbo].[ttMelissaGlobalAddressResponseAddressResponseCode] and, based upon configuration in the Stewardship Tier, can display as either Valid (0), Warning (1) or Error (2) codes.

 

After the validation has run, the address data in table [EntityVal].[dbo].[ttAddressSource] is updated with the TransmissionRecordID from table [EntityVal].[dbo].[ttMelissaGlobalAddressResponseAddress] and the worst ResponseStatus from [EntityVal].[dbo].[ttMelissaGlobalAddressResponseAddressResponseCode]. The TransmissionRecordID links the latest Melissa Address Validation response to the submitted data, unless a Response has been approved, in which case, the TransmissionRecordID of the approved record will be populated on the [EntityVal].[dbo].[ttAddressSource] record.

 

Review and Approve Validated Address Data

It is likely that the responses of the Melissa Address Validation Service include a mixture of records with Valid, Warning and Error Response Codes. It is recommended that users review the results and then selectively approve the records. The review process can take place directly in the Entity Validation application or a process can be created to incorporate the results of the Address Validation into a custom process.

 

Consume Address Validation Results

When it comes to consuming the results of the Address Validation Service, users can build their own logic based on the original data they submitted in the request in table [EntityVal].[dbo].[ttAddressSource] and the results of the Address Validation Service in table [EntityVal].[dbo].[ttMelissaGlobalAddressResponseAddress].

 

Importantly, the data in dbo.ttAddressSource needs to be joined to ttMelissaGlobalAddressResponseAddress via the RequestID and TransmissionRecordID (which is either the latest results from Address Validation Service or the Approved Response). The recommendation is that users only consume data related to records that have a Valid (value 0) or Warning (value 1) Response Codes in field ttAddressSource.ResonseStatus.

 

The following is an example of using the Stewardship Tier Application to send Request Data to the Melissa Address Validation Service:

  SELECT        dbo.ttMelissaGlobalAddressResponseAddress.RequestID, 
  dbo.ttRequest.Description, dbo.ttAddressSource.RecordID, 
  dbo.ttAddressSource.ResponseStatus, 
  dbo.ttMelissaGlobalAddressResponseAddress.Organization,
  
  dbo.ttMelissaGlobalAddressResponseAddress.AddressLine1, 
  dbo.ttMelissaGlobalAddressResponseAddress.Locality,
  dbo.ttMelissaGlobalAddressResponseAddress.SubAdministrativeArea, 
  
  dbo.ttMelissaGlobalAddressResponseAddress.PostalCode,
  dbo.ttMelissaGlobalAddressResponseAddress.CountryName 
  FROM            dbo.ttMelissaGlobalAddressResponseAddress INNER JOIN 
                           dbo.ttAddressSource ON 
  dbo.ttMelissaGlobalAddressResponseAddress.RequestID = 
  dbo.ttAddressSource.RequestID AND 
  dbo.ttMelissaGlobalAddressResponseAddress.TransmissionRecordID = 
  dbo.ttAddressSource.TransmissionRecordID INNER JOIN 
                            dbo.ttRequest ON 
  dbo.ttAddressSource.RequestID = dbo.ttRequest.RequestID 
  WHERE        (dbo.ttAddressSource.ResponseStatus IN (0, 1))

 

Integrate Entity Validation Melissa Data Call into another Application

Overview

This topic describes how to call the address validation processed by the Melissa API from your custom WebApp, for use with Master Data Management (MDM), Mass Maintenance and Data Construction. It includes detailed steps and SQL templates to complete the integration and contains the following sections:

• Generate an Entity Validation Request.
• Add Source Address Records to the Request.
• Create a Button and Event on the Page to Validate Address.
• Update the Source Address Record.
• Call the Process Request Event.
• Parse the Responses/Display Response Status.
• Register Rules on the Validate Address Event.
• View the Response Messages.
• Update the Address.
• Delete a Request.

 

Generate an Entity Validation Request

If there is a Request Table in the custom application’s database, append a column to that table to hold the Entity Validation Request ID. Otherwise, create a cross reference table, or use some other method of holding the Entity Validation Request ID inside of the application. This example displays a Master Data Management Content Application Request Table. Appended a column to that table with the following script.

 

ALTER TABLE dbo.ttRequest ADD EVRequestID int NULL

 

To view a template of this script, refer to Add EVRequestID Column

 

Save the Entity Validation Request ID during the normal process of creating a Master Data Management request. Create a new Request when Submitting the Content Application’s Request. Add a business rule to the Submit event.

 

CREATE PROCEDURE [dbo].[webRequest_Submit_EntityValRequestIns]
                @ContentRequestID UNIQUEIDENTIFIER,
                @boaUserID nvarchar(50)
AS
BEGIN
                SET NOCOUNT ON;
                DECLARE @Description NVARCHAR(200)
                DECLARE @EntityValRequestID int
                SELECT @Description =
                'QACustomer Request ' + CONVERT(NVARCHAR(200), RequestID) FROM
                      ttRequest WHERE ContentRequestID = @ContentRequestID
                EXEC EntityVal.[dbo].[apiCreateRequest]
                     @Description,
                     'A5524A6D-C956-4F5E-8928-B4002EBDC26C', --MelissaData Request Type
                     @boaUserID,
                     @newRequestID = @EntityValRequestID OUTPUT
                UPDATE ttRequest
                     SET EVRequestID = @EntityValRequestID
                     WHERE ContentRequestID = @ContentRequestID
END

 

To view a template of this script, refer to Capture Entity Validation RequestID.

Add Source Address Records to the Request

Once an Entity Validation Request ID is created, start adding records to the Address Source page for this Request. Add the Customer Records from a Customer Address page during the On Validate event. To assist managing Melissa Data response statuses, add new columns to the table.

 

ALTER TABLE dbo.ttKNA1_ADRC ADD
           [EVRecordID] [int] NULL,
           [EVResponseStatus] [int] NULL

 

To view a template of this script, refer to Add EVRecordID and EVResponseStatus columns.

 

Next, add a rule to generate a new Entity Validation Address Source Record when new address records are created. Address Line 1 is a required input field; all other fields are optional. RecordID is an Output parameter from the API procedure. Capture the RecordID to link the address with the validated address.

 

CREATE PROCEDURE
[dbo].[webRequestAddressGeneral_OnValidate_EntityValSourceAddressIns]
      @ContentRequestID UNIQUEIDENTIFIER,
      @zCounter int
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @EVRecordID INT,
             @EVRequestID INT,
             @Organization NVARCHAR(100),
             @AddressLine1 NVARCHAR(100),
             @AddressLine2 NVARCHAR(100),
             @AddressLine3 NVARCHAR(100),
             @Locality NVARCHAR(100),
             @AdministrativeArea NVARCHAR(100),
             @Country NVARCHAR(100),
             @PostalCode NVARCHAR(100),
             @ExternalReference NVARCHAR(50),
             @EVRecordIDCheck INT


      SELECT @EVRequestID = EVRequestID FROM ttRequest WHERE ContentRequestID =
@ContentRequestID

      SELECT
            @Organization = Name1,
            @AddressLine1 = STRAS,
            @Locality = ORT01,
            @AdministrativeArea = REGIO,
            @Country = LAND1,
            @PostalCode = PSTLZ,
            @ExternalReference = KUNNR,
            @EVRecordIDCheck = EVRecordID

      FROM ttKNA1_ADRC
      WHERE ZCounter = @zCounter


      IF @EVRecordIDCheck IS NULL
      BEGIN


            EXEC EntityVal.[dbo].[apiSourceAddressIns]
                         @RequestId = @EVRequestID,
                         @Organization = @Organization,
                         @AddressLine1 = @AddressLine1,
                         @AddressLine2 = @AddressLine2,
                         @AddressLine3 = @AddressLine3,
                         @Locality = @Locality,
                         @AdministrativeArea = @AdministrativeArea,
                         @Country = @Country,
                         @PostalCode =@PostalCode,
                         @ExternalReference = @ExternalReference,
                         @newRecordID = @EVRecordID OUTPUT
            UPDATE ttKNA1_ADRC
                   SET EVRecordID= @EVRecordID
                   WHERE ContentRequestID = @ContentRequestID
                   AND zCounter = @zCounter
      END --Record not created yet.
END

 

To view a template of this script, refer to Procedure to Insert new Address Source records.

Create a Button and Event on page to Validate Address

Create a button to Validate or Check the Address through Melissa data on the page. As example, a Validate Address button on the page’s toolbar is shown. The Validate Address event is set as a Background event running in the Background Queue. See Add a Toolbar Button in the online help for how to add the button, and Create Events in the online help for additional information on event creation.

Append the columns to the Select Statement that will build the Horizontal or Vertical view that will be added to the Address Validation button functionality.

NULL AS AddressValidation,
dbo.ttRequest.EVRequestID,
dbo.ttKNA1_ADRC.EVRecordID,
dbo.ttKNA1_ADRC.EVReponseStatus,
'4046526a-43ae-449b-b891-e1e7b741a49f' AS ViewValidationResults,
NULL AS AcceptAddress

 

To view a template of this script, refer to Add Buttons to Call Address Validation and View Responses

Update the Source Address Record

A similar procedure can be used to update the Address Source record. Update the Address Source record before processing the address through the Melissa Data Service. This procedure is helpful to allow users to edit the Address in their own application either as part of the normal usage of the page or to fix issues detected by the Melissa Data service. Call this procedure as the first Rule on the ValidateAddress event on the Customer Address General page. The apiSourceAddressUpd procedure marks the address as ‘ReadyToSend,’ so the Process Request event sends the address to the Melissa Data Service even if it has already been sent to the service once.

CREATE PROCEDURE
[dbo].[webRequestAddressGeneral_ValidateAddress_EntityValSourceAddressUpd]
      @ContentRequestID UNIQUEIDENTIFIER,
      @boaUserID nvarchar(50),
      @zCounter int
AS
BEGIN

      SET NOCOUNT ON;
      DECLARE @EVRecordID BIGINT,
             @EVRequestID INT,
             @Organization NVARCHAR(100),
             @AddressLine1 NVARCHAR(100),
             @AddressLine2 NVARCHAR(100),
             @AddressLine3 NVARCHAR(100),
             @Locality NVARCHAR(100),
             @AdministrativeArea NVARCHAR(100),
             @Country NVARCHAR(100),
             @PostalCode NVARCHAR(100)


      SELECT @EVRequestID = EVRequestID FROM ttRequest WHERE ContentRequestID =
@ContentRequestID


      SELECT
             @Organization = Name1,
             @AddressLine1 = STRAS,
             @Locality = ORT01,
             @AdministrativeArea = REGIO,
             @Country = LAND1,
             @PostalCode = PSTLZ,
             @EVRecordID = EVRecordID

      FROM ttKNA1_ADRC
      WHERE ZCounter = @zCounter


      IF NOT(@EVRecordID IS NULL)
      BEGIN
      
      
             EXEC [EntityVal].[dbo].[apiSourceAddressUpd]
                          @RequestId = @EVRequestID,
                          @RecordID = @EVRecordID,
                          @Organization = @Organization,
                          @AddressLine1 = @AddressLine1,
                          @AddressLine2 = @AddressLine2,
                          @AddressLine3 = @AddressLine3,
                          @Locality = @Locality,
                          @AdministrativeArea = @AdministrativeArea,
                          @Country = @Country,
                          @PostalCode =@PostalCode


      END --EVRecordID not null
END
  

 

To view a template of this script, refer to Update the Source Address Records.

Call the Process Request Event

The Entity Validation Process Request Event is a public event that can be called as a Business Rule during an event on your page. The Validate Address event on the Customer Address General page uses this event.

 

Calling this event processes ALL addresses under the request that meet at least one of these criteria:

1. The address is new and has not already been sent to the Melissa Data Service (Addresses created by the apiSourceAddressIns meet this criteria)
2. The address has the ReadyToSend bit set to true. (Addresses updated by the apiSourceAddressUpd meet this criteria)

Before registering the public event as a rule, create a view that contains the key columns for the page’s table and the EntityValidation RequestID value, which are stored on the ttRequest table in the example. In the Event Parameters, the RequestID required by the event should use the EVRequestID value from this view. After registering this rule, Clear Cache under System Administration > Configuration > Parameters

 

CREATE VIEW [dbo].[webRequest_EntityValProcRequestEvt]
AS
SELECT
       dbo.ttRequest.ContentRequestID,
       dbo.ttRequest.EVRequestID,
       dbo.ttKNA1_ADRC.RequestID,
       dbo.ttKNA1_ADRC.KTOKD,
       dbo.ttKNA1_ADRC.ZCounter
FROM
       dbo.ttRequest INNER JOIN dbo.ttKNA1_ADRC
       ON dbo.ttRequest.ContentRequestID = dbo.ttKNA1_ADRC.ContentRequestID
       AND dbo.ttRequest.RequestID = dbo.ttKNA1_ADRC.RequestID

 

To view a template of this script, refer to Process Request Event View.

Parse the Responses/Display Response Status

A final rule in the Validate Address event gathers the worst response value that has been written to the ttAddressSource record in the EntityValidation application. This is an integer value that can be interpreted by referencing the EntityVal..ztResponseType table. A NULL or -1 value indicates an “UNKNOWN” response type, where Melissa returned no response at all or only responses that is not listed under the Melissa Data Service Responses configuration.

 

To parse this value, read values out of the ttAddressSource table in the Entity Validation application.

 

CREATE PROCEDURE
[dbo].[webRequestAddressGeneral_ValidateAddress_EVResponseStatusUpd]
    @ContentRequestID UNIQUEIDENTIFIER,
    @boaUserID nvarchar(50),
    @zCounter int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EVRecordID BIGINT,
       @EVRequestID INT,
       @EVResponseStatus INT
    SELECT @EVRequestID = EVRequestID FROM ttRequest WHERE ContentRequestID =
@ContentRequestID
    SELECT
       @EVRecordID = EVRecordID
    FROM ttKNA1_ADRC
    WHERE ZCounter = @zCounter


    IF NOT(@EVRecordID IS NULL)
    BEGIN
    
       SELECT @EVResponseStatus = ResponseStatus FROM EntityVal..ttAddressSource
       WHERE RequestID = @EVRequestID and RecordID = @EVRecordID

       UPDATE ttKNA1_ADRC
         SET EVResponseStatus = @EVResponseStatus
       WHERE ZCounter = @zCounter
    END --EVRecordID not null
END

 

To view a template of this script refer to Parse the Responses.

Register Rules on the Validate Address Event

Register a Web App Event

To register the WebApp event:

1. Select Admin > WebApps in the Navigation pane.
2. Click the Pages icon for the custom WEB APP NAME.
3. Click the Events icon for the page to add the event to.
4. Click the Business Rules icon for an event.
5. Click the Vertical View icon for the rule.
6. Select the page Entity Validation: Request from the Event Page ID list box.
7. Select Process Request from the Event Name list box.
8. Select the event view created previously in the Parameter View list box.
9. Click Save.

10. Then click the Parameters icon and set the RequestID parameter to be the EVRequestID.

    

View the Response Messages

A simple method to view the Melissa Data responses is to link to the page in the Entity Validation application.  In our page, we are going to link to the Header/Detail page using a ‘Dynamic Page Link’ by referencing the GUID value of that page.

 

First, these were already added to your page’s Horizontal or Vertical view:

 

dbo.ttRequest.EVRequestID,
dbo.ttKNA1_ADRC.EVRecordID,
'4046526a-43ae-449b-b891-e1e7b741a49f' AS ViewValidationResults

 

To view a template of this script, refer to Add Buttons to Call Address Validation and View Responses

 

You can then use these columns to configure a button as a Dynamic Link Type of ‘Dynamic Page,’ either using the ‘ViewValidationResults’ column itself for the button or referencing that column as the ‘Dynamic Link Column’ in the Advanced Column Properties.

 

Once you create that link, go into the Dynamic Page link properties and set the following Binding Criteria:

EVRequestID=RequestID, EVRecordID=RecordID

 

You should now be able to get into the EntityVal pages to see the transmissions of this address to the Melissa Data service, to see what the Melissa Data service sent back, and open the response codes from the Melissa Data Service.

 

If you want to use the ‘Accept Address’ process on this page, your users can lock in a specific response from the Melissa Data service as the one to keep.

Update Your Address

The EntityVal..ttAddressSource table has a TransmissionRecordID value that is equal to the value of the latest Melissa Data response OR to the value of the ‘Accepted’ Melissa Data Response if a user has accepted an older response message. While it is not being handled in this example, care needs to be taken with the Melissa Response as the AddressLine2 through 8 fields can contain the entire address, including City, State, ZIP and Country.

  CREATE PROCEDURE [dbo].[webRequestAddressGeneral_AcceptAddress_KNA1_ADRCUpd]
	@ContentRequestID UNIQUEIDENTIFIER,
	@boaUserID nvarchar(50),
	@zCounter int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @EVRecordID BIGINT,
		@EVRequestID INT,
		@EVTransmissionRecordID UNIQUEIDENTIFIER,
		@AddressLine1 NVARCHAR(100),
		@Locality NVARCHAR(100),
		@AdministrativeArea NVARCHAR(100),
		@PostalCode NVARCHAR(100)

	SELECT @EVRequestID = EvRequestID FROM ttRequest WHERE ContentRequestID = @ContentRequestID


	SELECT 
		@EVRecordID = EVRecordID
	FROM ttKNA1_ADRC
	WHERE ZCounter = @zCounter
	
	SELECT @EVTransmissionRecordID = TransmissionRecordID 
FROM EntityVal..ttAddressSource 
WHERE RecordID = @EVRecordID

	IF NOT(@EVTransmissionRecordID IS NULL)
	BEGIN

		SELECT 
			@AddressLine1 = AddressLine1,
			@Locality = Locality,
			@AdministrativeArea = AdministrativeArea,
			@PostalCode = PostalCode
				
		FROM 
			EntityVal..ttMelissaGlobalAddressResponseAddress
		WHERE
			RequestID = @EVRequestID
			AND TransmissionRecordID = @EVTransmissionRecordID


		UPDATE ttKNA1_ADRC
		SET 
			STRAS = @AddressLine1,
			ORT01 = @Locality,
			REGIO = @AdministrativeArea ,
			PSTLZ = @PostalCode

		WHERE ZCounter = @zCounter

	END --EVTransmissionRecordID not null
END

 

To view a template of this script, refer to Updating your Address based on the Melissa Data Response

 

Change the AcceptAddress column on your page into a Button, and make that column into an event. Refer to Create Events in the online help for more information.

 

Add this stored procedure as a business rule for that event.

Delete a Request

Once the data has processed, delete the Entity Validation Request, either by deleting the EntityVal..ttRequest record directly or by using the ‘Purge Data’ action in the Entity Validation application.

Entity Validation API Procedures

The following table outlines the Entity Validation API Procedures:

 

Process Request Public Event

To process a request public event:

1. Register this event as a Business Rule:Entity Validation:Request:ProcessRequest
2. Add an EVT view to the rule that contains the Page’s Key Fields and the Entity Validation Request ID.

Logic behind running this event.

• Any NEW addresses that have not yet been validated are validated at that time.
• Addresses marked "Ready To Send" are sent again.
• Addresses with Response Address marked as Accepted are NOT sent again.

Template SQL Statements

Add EVRequestID Column to a table

ALTER TABLE MYTABLE ADD EVRequestID int NULL
        

 

Capture Entity Validation Request ID

CREATE PROCEDURE [dbo].[MyRequestCreatingProcedure]
@MyTableKey UNIQUEIDENTIFIER,
	@boaUserID nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @Description NVARCHAR(200)
	DECLARE @EntityValRequestID int

	SELECT @Description = ‘Way to find request in Entity Validation’


	EXEC EntityVal.[dbo].[apiCreateRequest]
		@Description,
		'A5524A6D-C956-4F5E-8928-B4002EBDC26C', --MelissaData Request Type
		@boaUserID,
		@newRequestID = @EntityValRequestID OUTPUT

	UPDATE MyTable
		SET EVRequestID = @EntityValRequestID
		WHERE TableKey = @MyTableKey
    

 

Add EVRecordID and EVResponseStatus Columns to a table

  CREATE PROCEDURE [dbo].[MyProcedureFor_EntityValSourceAddressIns]
	@MyAddressTableKey UNIQUEIDENTIFIER, 
@MyTableKey UNIQUEIDENTIFIER
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @EVRecordID INT,
		@EVRequestID INT,
		@Organization NVARCHAR(100),
		@AddressLine1 NVARCHAR(100),
		@AddressLine2 NVARCHAR(100),
		@AddressLine3 NVARCHAR(100),
		@Locality NVARCHAR(100),
		@AdministrativeArea NVARCHAR(100),
		@Country NVARCHAR(100),
		@PostalCode NVARCHAR(100),
              @ExternalReference NVARCHAR(50),
		@EVRecordIDCheck INT


	SELECT @EVRequestID = EVRequestID FROM MyTable WHERE MyTableKey = @MyTableKey

	SELECT 
		@Organization = Name,
		@AddressLine1 = StreetAddress,
		@Locality = City,
		@AdministrativeArea = State,
		@Country = Country,
		@PostalCode = PostalCode,
              @ExternalReference = RecordNumber
		@EVRecordIDCheck = EVRecordID

	FROM MyAddressTable
	WHERE MyAddressTableKey = @MyAddressTableKey
		

	IF @EVRecordIDCheck IS NULL
	BEGIN


		EXEC EntityVal.[dbo].[apiSourceAddressIns]
				@RequestId = @EntityValRequestID,
				@Organization = @Organization,
				@AddressLine1 = @AddressLine1,
				@AddressLine2 = @AddressLine2,
				@AddressLine3 = @AddressLine3,
				@Locality = @Locality,
				@AdministrativeArea = @AdministrativeArea,
				@Country = @Country,
				@PostalCode =@PostalCode,
                           @ExternalReference= @ExternalReference,
				@newRecordID = @EVRecordID OUTPUT

		UPDATE MyAddressTable
			SET EVRecordID= @EVRecordID
			WHERE MyAddressTableKey = @MyAddressTableKey
			
	END --Record not created yet.
END

Update the Source Address Record

 CREATE PROCEDURE [dbo].[MyProcedureFor_EntityValSourceAddressUpd]
	@MyAddressTableKey UNIQUEIDENTIFIER,
@MyTableKey UNIQUEIDENTIFIER
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @EVRecordID BIGINT,
		@EVRequestID INT,
		@Organization NVARCHAR(100),
		@AddressLine1 NVARCHAR(100),
		@AddressLine2 NVARCHAR(100),
		@AddressLine3 NVARCHAR(100),
		@Locality NVARCHAR(100),
		@AdministrativeArea NVARCHAR(100),
		@Country NVARCHAR(100),
		@PostalCode NVARCHAR(100)

	SELECT @EVRequestID = EVRequestID FROM MyTable WHERE MyTableKey = @MyTableKey

	SELECT 
		@Organization = Name,
		@AddressLine1 = AddressLine1,
		@Locality = City,
		@AdministrativeArea = State,
		@Country = Country,
		@PostalCode = PostalCode,
		@EVRecordID = EVRecordID
	FROM MyAddressTable
	WHERE MyAddressTableKey = @MyAddressCounterKey
		
	IF NOT(@EVRecordID IS NULL)
	BEGIN

		EXEC [EntityVal].[dbo].[apiSourceAddressUpd]
				@RequestId = @EVRequestID,
				@RecordID = @EVRecordID,
				@Organization = @Organization,
				@AddressLine1 = @AddressLine1,
				@AddressLine2 = @AddressLine2,
				@AddressLine3 = @AddressLine3,
				@Locality = @Locality,
				@AdministrativeArea = @AdministrativeArea,
				@Country = @Country,
				@PostalCode =@PostalCode
	END --EVRecordID not null
END

 

Process Request Event View

 CREATE VIEW [dbo].[MyViewFor_EntityValProcRequestEvt]
AS
SELECT        
	dbo.MyTable.EVRequestID, 
	dbo.MyAddressTable.MyAddressTableKey
FROM            
	dbo.MyTable INNER JOIN dbo.MyAddressTable 
	ON dbo.MyTable.MyTableKey = dbo.MyAddressTable.MyTableKey

 

Parse the Responses

 CREATE PROCEDURE [dbo].[MyProcedureFor_EVResponseStatusUpd]
	@MyAddressTableKey UNIQUEIDENTIFIER, 
@MyTableKey UNIQUEIDENTIFIER
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @EVRecordID BIGINT,
		@EVRequestID INT,
		@EVResponseStatus INT

	SELECT @EVRequestID = EVRequestID FROM MyTable WHERE MyTableKey = @MyTableKey

	SELECT 
		@EVRecordID = EVRecordID
	FROM MyAddressTable
	WHERE MyAddressTableKey = @MyAddressTableKey

	IF NOT(@EVRecordID IS NULL)
	BEGIN

		SELECT @EVResponseStatus = ResponseStatus FROM EntityVal..ttAddressSource
		WHERE RequestID = @EVRequestID and RecordID = @EVRecordID

		UPDATE MyAddressTable
			SET EVResponseStatus = @EVResponseStatus
		WHERE MyAddressTableKey = @MyAddressTableKey

	END --EVRecordID not null
END

Add Buttons to Call Address Validation and View Responses

 NULL AS MyButtonName,
dbo.MyTable.EVRequestID,
dbo.MyAddressTable.EVRecordID,
dbo.MyAddressTable.EVReponseStatus,
'4046526a-43ae-449b-b891-e1e7b741a49f' AS ViewValidationResults,
NULL AS AcceptAddress

 

Updating the Address

CREATE PROCEDURE [dbo].[MyProcedureTo_UpdateMyAddressTableUpd]
	@MyAddressTableKey UNIQUEIDENTIFIER, 
@MyTableKey UNIQUEIDENTIFIER
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @EVRecordID BIGINT,
		@EVRequestID INT,
		@EVTransmissionRecordID UNIQUEIDENTIFIER,
		@AddressLine1 NVARCHAR(100),
		@AddressLine2 NVARCHAR(100),
		@AddressLine3 NVARCHAR(100),
		@Locality NVARCHAR(100),
		@AdministrativeArea NVARCHAR(100),
		@Country NVARCHAR(100),
		@PostalCode NVARCHAR(100)

	SELECT @EVRequestID = EvRequestID FROM MyTable WHERE MyTableKey = @MyTableKey

	SELECT 
		@EVRecordID = EVRecordID
	FROM MyAddressTable
	WHERE MyAddressTableKey = @MyAddressTableKey
	
	SELECT @EVTransmissionRecordID = TransmissionRecordID 
FROM EntityVal..ttAddressSource 
WHERE RecordID = @EVRecordID

	IF NOT(@EVTransmissionRecordID IS NULL)
	BEGIN

		SELECT 
			@AddressLine1 = AddressLine1,
			@Locality = Locality,
			@AdministrativeArea = AdministrativeArea,
			@PostalCode = PostalCode
				
		FROM 
			EntityVal..ttMelissaGlobalAddressResponseAddress
		WHERE
			RequestID = @EVRequestID
			AND TransmissionRecordID = @EVTransmissionRecordID

--Example is only updating these 4 address lines. Care needs to be taken
--when parsing out other Melissa Data response fields.
		UPDATE MyAddressTable
		SET 
			STRAS = @AddressLine1,
			ORT01 = @Locality,
			REGIO = @AdministrativeArea ,
			PSTLZ = @PostalCode

		WHERE MyAddressTableKey = @MyAddressTableKey

	END --EVTransmissionRecordID not null
END