Integrate Oracle CRM On Demand with Dun & Bradstreet: Part 2

Introduction

In part one of this three part series, the concept of integrating Oracle CRM On Demand (COD) with Dun and Bradstreet  (D&B) was introduced, along with the technologies to be used, including Javascript/HTML/CSS, JQuery, REST APIs and JSON.

This second of three articles describes connecting to the COD REST API, connecting to the D&B REST API, transforming the resulting JSON data, and standardizing the data so COD will accept the records.

For this example the Account record being created in COD will contain three fields; the AccountName, the Location, and the PublicCompany field. These can be expanded upon to contain any number of fields.

COD REST API

The REST API for COD is documented here. The one API call to be used for this integration is to create a new Account record. If the Javascript is embedded into COD, for instance as a Global Web Applet, then authentication is already handled, as long as you are logged into COD via your browser.

Sample Javascript for creating a test record is as shown below. Note that the jquery is being pulled dynamically from the Google CDN. In this call the URL is /OnDemand/user/Rest/latest/Accounts, on the specific COD server, and is an HTTP POST request and one new record is being created where AccountName  is “REST API Test”, Location of “Hometown” and PublicCompany set to “true”.

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script> 
<script type="text/javascript">
    $(function(){
        var account_json = {
          "Accounts": [
            {	
               "AccountName": "REST API Test",
               "Location": "Hometown",
               "PublicCompany": true
            }
          ]
        }
        $.ajax({
            url:"/OnDemand/user/Rest/latest/Accounts",
            datatype:"json",
            type:"post",
            contentType:"application/vnd.oracle.adf.resource+json",
            data: JSON.stringify(account_json),
            success: function( data ) {
            console.log(data);
            }
        });
    });
</script>

D&B REST API

D&B has multiple REST APIs to work with, and for this example the 1.7 API will be used. Two calls are required. The first is to search for a company and the second is to pull the details for a specific company.

The server in all the below URLs will change depending on what license you have with D&B. If you are using a sandbox instance, for example, then that will be different. Authentication via the D&B 1.7 REST API requires a D&B user account, an API key, and the password. It is assumed you can get access to that by contacting D&B directly.

D&B provides a few ways to search using the getCleanseMatch API call. Please read the docs for details. This example will search using one field, the company name. Below is example code to lookup the company. The resulting JSON is a list of matching D&B records with each record containing the DUNS number. That DUNS number is used in the next call.

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script type="text/javascript">
    $(function(){
        var url = "http://dnbdirect-api.dnb.com/DnBAPI-17/rest/company/getCleanseMatch?SubjectName?SubjectName=AMR%20Corporation";
        var username = "my_dnb_username";
        var password = "my_dnb_password";
        var api_key = "my_dnb_api_key";
        $.ajax({
            url:url,
            datatype:"json",
            type:"get",
            contentType:"application/json",
            headers: {
                "Authorization": "Basic " + username + ":" + password,
                "API-KEY": api_key
            },
            success: function( data ) {
                console.log(data);
            }
        });
    });
</script>

Below is the JSON output (just the first record in the JSON, not all the JSON data).

{
  "GetCleanseMatchResponse": {
    "TransactionDetail": {
      "TransactionTimestamp": "2015-06-05T13:08:58", 
      "ServiceTransactionID": "Id-aad7715547663f0092dd10005c93d1a2-1"
    }, 
    "TransactionResult": {
      "ResultID": "CM000", 
      "ResultText": "Success", 
      "SeverityText": "Information"
    }, 
    "@ServiceVersionNumber": "3.0", 
    "GetCleanseMatchResponseDetail": {
      "MatchResponseDetail": {
        "MatchDataCriteriaText": {
          "$": "Name and Address Lookup"
        }, 
        "CandidateMatchedQuantity": 22, 
        "MatchCandidate": [
          {
            "TelephoneNumber": {
              "TelecommunicationNumber": "8179672000", 
              "UnreachableIndicator": false
            }, 
            "OrganizationPrimaryName": {
              "OrganizationName": {
                "$": "A M R TRAINING GROUP INC"
              }
            }, 
            "FamilyTreeMemberRole": [
              {
                "FamilyTreeMemberRoleText": {
                  "$": "Headquarters"
                }
              }
            ], 
            "MailingAddress": {
              "CountryISOAlpha2Code": "US", 
              "UndeliverableIndicator": false
            }, 
            "PrimaryAddress": {
              "PostalCode": "76155", 
              "TerritoryAbbreviatedName": "TX", 
              "UndeliverableIndicator": false, 
              "PostalCodeExtensionCode": "2605", 
              "CountryISOAlpha2Code": "US", 
              "StreetAddressLine": [
                {
                  "LineText": "4333 AMON CARTER BLVD"
                }
              ], 
              "PrimaryTownName": "FORT WORTH"
            }, 
            "DUNSNumber": "788138980", 
            "MatchQualityInformation": {
              "MatchDataProfileComponent": [
                {
                  "MatchDataProfileComponentValue": "02", 
                  "MatchDataProfileComponentTypeText": {
                    "$": "Name"
                  }
                }, 
                {
                  "MatchDataProfileComponentValue": "00", 
                  "MatchDataProfileComponentTypeText": {
                    "$": "Street Number"
                  }
                }, 
                ...
              ], 
              "MatchGradeText": "AAAAAZZAFFZ", 
              "ConfidenceCodeValue": 10, 
              "MatchBasis": [
                {
                  "MatchBasisText": {
                    "$": "Trade style"
                  }, 
                  "SeniorPrincipalIndicator": false, 
                  "SubjectTypeText": "Business", 
                  "EndIndicator": false
                }, 
                {
                  "MatchBasisText": {
                    "$": "Primary Address"
                  }, 
                  "SeniorPrincipalIndicator": false, 
                  "SubjectTypeText": "Business", 
                  "EndIndicator": false
                }
              ], 
              "MatchDataProfileComponentCount": 14, 
              "MatchGradeComponent": [
                {
                  "MatchGradeComponentRating": "A", 
                  "MatchGradeComponentScore": 100, 
                  "MatchGradeComponentTypeText": {
                    "$": "Name"
                  }
                }, 
                {
                  "MatchGradeComponentRating": "A", 
                  "MatchGradeComponentScore": 100, 
                  "MatchGradeComponentTypeText": {
                    "$": "Street Number"
                  }
                }, 
                ...
              ], 
              "MatchDataProfileText": "0200009900989800000000009898", 
              "MatchGradeComponentCount": 11
            }, 
            "OperatingStatusText": {
              "$": "Active"
            }, 
            "TradeStyleName": {
              "OrganizationName": {
                "$": "A M R"
              }
            }, 
            "StandaloneOrganizationIndicator": false, 
            "DisplaySequence": 1
          }
        ]
      }, 
      "InquiryDetail": {
        "SubjectName": "AMR Corporation", 
        "Address": {
          "CountryISOAlpha2Code": "US", 
          "TerritoryName": "TX", 
          "FullPostalCode": "76155", 
          "StreetAddressLine": [
            {
              "LineText": "4333 Amon Carter Blvd."
            }
          ]
        }
      }
    }
  }
}

Now that a DUNS number is available, we need to retrieve details about a company using an HTTP GET command via this URL http://dnbdirect-api.dnb.com/DnBAPI-17/rest/company/788138980

Below is the Javascript for that call.

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script type="text/javascript">
    $(function(){
        var url = "http://dnbdirect-api.dnb.com/DnBAPI-17/rest/company/788138980";
        var username = "my_dnb_username";
        var password = "my_dnb_password";
        var api_key = "my_dnb_api_key";
        $.ajax({
            url:url,
            datatype:"json",
            type:"get",
            contentType:"application/json",
            headers: {
                "Authorization": "Basic " + username + ":" + password,
                "API-KEY": api_key
            },
            success: function( data ) {
                console.log(data);
            }
        });
    });
</script>

The number at the end of the URL is the DUNS number. 788138980 is the DUNS number for the AMR. In our example that DUNS number is replaced by the DUNS number of the company we want to retrieve. That DUNS number was acquired in the getCleanseMatch API call above.

A small section of the resulting JSON is shown below. Note that the entire JSON data is not shown because that would be very large and for our example we are only interested in the company name, location and if the company is public.

{
  "otherURLs": {
    "url": []
  }, 
  "manufacturingIndicator": "", 
  "parentName": "AMR", 
  "locations": {
    "location": [
      {
        "city": "Short Hills", 
        "addressType": "PRIMARY_ADDRESS", 
        "countryId": 76, 
        "stateOfIncorporation": null, 
        "zip": "07078", 
        "address1": "103 Jfk Pkwy", 
        "address2": "", 
        "longitude": -74.354241, 
        "county": "Essex County", 
        "state": "NJ", 
        "country": "United States", 
        "latitude": 40.742406, 
        "metroArea": "New York-Newark-Jersey City NY-NJ-PA", 
        "latLongAccuracy": "0", 
        "zip4": "2708"
      }
    ]
  }, 
  "yearFounded": "1841", 
  "fein": "223725387", 
  "name": "THE DUN & BRADSTREET CORPORATION",
  "legalStatus": "Corporation", 
  "companyType": "PUBLIC", 
}

Transforming JSON

Now that REST calls can be made to both D&B and COD, the JSON itself needs to be transformed from what comes back from D&B into what COD is expecting. This transformation needs to map the tree of JSON data returned from D&B into the flat structure required by COD.

The D&B JSON tree is a multi-level tree of data. Notice that in the above JSON from D&B that the location information is stored in locations -> location. For this example we will map the city field in D&B to the “location” field in COD. In javascript, assuming the “account_json” variable contains dictionary (JSON) to be sent to COD and the “dnb” variable contains the parsed JSON data as a dictionary, then the Javascript for transforming to AccountName, Location and PublicCompany would be as follows:

var account_json = {
    "Accounts": [
        {	
            "AccountName": dnb[“name”],
            "Location": dnb[“locations”][“location”][“city”].
            "PublicCompany": dnb[“companyType”]
         }
    ]
}

This same type of transformation would be needed for every field to be mapped from D&B into COD.

Standardizing Data

Notice the “PublicCompany” field in the above Javascript.  One issue with that is that in D&B the data about if the company is public or not is stored in the “companyType” field and contains “PUBLIC” in this case where the company is public. However, in COD there’s a specific boolean value called “PublicCompany”. Hence the above Javascript must be modified to ensure that COD is receiving the proper, standardized data. The same is true for fields in COD like State and Country. Those fields require specific values like “USA” instead of “United States”. Hence there’s a fair amount of standardization to be applied before this will work.

For the “PublicCompany” field, the Javascript changes to this

var account_json = {
    "Accounts": [
        {	
            "AccountName": dnb[“name”],
            "Location": dnb[“locations”][“location”][“city”].
            "PublicCompany": dnb[“companyType”] == “PUBLIC”
         }
    ]
}

State and Country standardization would be much more involved. The valid country and state symbols that D&B returns would each be mapped to the valid country and state symbols that COD can accept. While that is rather straight forward to code up, the details are beyond the scope of this article.

What’s Next

Now that data can be retrieved from D&B and put into COD, the final article, Part 3, will present the UI. This includes an introduction to the COD Javascript API, how to launch the integration process, how to view potential D&B records before being entered into COD, and then writing the results into COD.