Extract data based on multiple columns from lookup in OIC

Lookup is one of the most useful component in Oracle Integration Cloud which help to keep mapping that can be modified anytime without impacting the integration. The lookupValue function extract the values from lookup based on single column. There are scenarios where we require to extract value based on multiple columns.

Few blogs related to Lookup:

Oracle Integration Cloud Service Lookups

Import CSV file to create Lookup Oracle Integration Cloud Service (ICS)

Consider there is lookup defined in the OIC with below structure:

Col1	Col2	Col3
a	b	ab
x	y	xy
c	d	cd

You wanted to get Col3 value based on Col1 and Col2. The inbuilt function lookupValue allow to get column values based on single column.

In order to get value based on two columns, we have to use Lookup REST API with expand parameter as value “datarow” with slight modification in XSLT.

Lookup REST API

Have created a lookup with name “lookup” . Try to hit the API from postman and see the response:

API URL:

https://myoic-ide3dr442a-ia.integration.ocp.oraclecloud.com:443/ic/api/integration/v1/lookups/lookup?expand=datarow

Response

{
    "columns": [
        "Col1",
        "Col2",
        "Col3"
    ],
    "created": "2020-03-27T16:57:08.053+0000",
    "createdBy": "ankur",
    "id": "lookup",
    "keywords": "",
    "lastUpdated": "2020-03-27T16:58:28.914+0000",
    "lastUpdatedBy": "ankur",
    "links": [
        {
            "href": "https://myoic-ide3dr442a-ia.integration.ocp.oraclecloud.com:443/ic/api/integration/v1/lookups/lookup",
            "rel": "self"
        },
        {
            "href": "https://myoic-ide3dr442a-ia.integration.ocp.oraclecloud.com:443/ic/api/integration/v1/lookups/lookup",
            "rel": "canonical"
        }
    ],
    "lockedDVMFlag": false,
    "lockedFlag": false,
    "name": "lookup",
    "resourcePath": "tenant/resources/dvms/lookup",
    "rowCount": 3,
    "rows": [
        {
            "rowData": [
                "a",
                "b",
                "ab"
            ]
        },
        {
            "rowData": [
                "c",
                "d",
                "cd"
            ]
        },
        {
            "rowData": [
                "x",
                "y",
                "xy"
            ]
        }
    ],
    "status": "CONFIGURED",
    "usage": 0,
    "usageActive": 0
}

Use Case Steps

Have created a REST connection with Trigger and Invoke where I have configured the REST API Base URL as “https://myoic-ide3dr442a-ia.integration.ocp.oraclecloud.com:443” with Basic Authentication.
Create an App Driven Orchestration Integration, name it as “ExtractLookupData” and drop the REST connection as trigger point
Provide the endpoint name as “ExtractData” and click on the Next button
From the next screen, configure below options and click on the Next button
- What does this operation do: GetLookupValue
- What is the endpoint’s relative resource URI: /data
- What action do you want to perform on the endpoint: GET
- Select “Add and review parameter for this endpoint” checkbox
- Select “Configure this endpoint to receive the response” checkbox

From the “Request Parameters” screen add two string parameters with name “source1” and “source2“
From the “Response” screen, add sample json as below and finish the wizard

{
  "target" : "",
  "one" : "",
  "two":""
}

Drop Rest connection again beneath the trigger point
From the “Basic Info” page, configure below options and click on the Next button
- What do you want to call your endpoint: DataLookup
- What is the endpoint’s relative resource URI: /ic/api/integration/v1/lookups/{lookupname}
- What action do you want to perform on the endpoint: GET
- Select “Add and review parameter for this endpoint” checkbox
- Select “Configure this endpoint to receive the response” checkbox

From the “Request Parameters” screen add one string parameter with name “expand“
From the “Response” screen, provide sample json and finish the wizard. Sample json will be the Lookup Service response which is provided in the “Lookup REST API” section above

Edit the ” DataLookup” mapper and hard-code fields as below:
- lookupname: lookup
- expand: datarow

Add the mapper at the end and map fields as below
- DataLookup-responsemapper-rows-rowData To executeResponse-responsewrapper-target
- source1 To one
- source2 to two

Now the designer configuration is completed. Let’s modify the xslt from backend.

Modify the XSLT

Export the integration and search the last mapper xslt. In this case we found at “resources\processor_66\resourcegroup_69\” path. Modify the xslt as below:

Before

<xsl:template match="/" xml:id="id_11">
      <nstrgmpr:executeResponse xml:id="id_12">
         <nstrgdfl:response-wrapper xml:id="id_31">
            <nstrgdfl:target xml:id="id_32">
               <xsl:value-of select="$DataLookup/nsmpr0:executeResponse/nsmpr2:response-wrapper/nsmpr2:rows/nsmpr2:rowData" xml:id="id_33"/>
            </nstrgdfl:target>
         <nstrgdfl:one xml:id="id_47">
               <xsl:value-of select="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr5:source1" xml:id="id_48"/>
            </nstrgdfl:one>
         <nstrgdfl:two xml:id="id_62">
               <xsl:value-of select="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr5:source2" xml:id="id_63"/>
            </nstrgdfl:two>
         </nstrgdfl:response-wrapper>
      </nstrgmpr:executeResponse>
   </xsl:template>

After

<xsl:template match="/" xml:id="id_11">
      <nstrgmpr:executeResponse xml:id="id_12">
         <nstrgdfl:response-wrapper xml:id="id_31">
            <nstrgdfl:target xml:id="id_32">
               <xsl:value-of select="$DataLookup/nsmpr0:executeResponse/nsmpr2:response-wrapper/nsmpr2:rows[nsmpr2:rowData[1]=/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr5:source1 and nsmpr2:rowData[2]=/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr5:source2]/nsmpr2:rowData[3]" xml:id="id_33"/>
            </nstrgdfl:target>
         <nstrgdfl:one xml:id="id_47">
               <xsl:value-of select="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr5:source1" xml:id="id_48"/>
            </nstrgdfl:one>
         <nstrgdfl:two xml:id="id_62">
               <xsl:value-of select="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr5:source2" xml:id="id_63"/>
            </nstrgdfl:two>
         </nstrgdfl:response-wrapper>
      </nstrgmpr:executeResponse>
   </xsl:template>

Pack the iar again with the change and re-import the integration.

Test the Integration

Activate the integration, hit the API from postman and see the result:

Positive Test

Pass the valid combination in the source1 and source2 request parameters which exist in the lookup.

Negative Test

Pass the invalid combination in the source1 and source2 request parameters which doesn’t exist in the lookup.

Download the IAR file from here.

Please subscribe my YouTube channel to learn more and more about Oracle Integration Cloud.

Post Views: 2,886

Enter your keyword

post