Manage Lookups

A lookup associates values used by one application for a specific field to the values used by other applications for the same field. This provides the capability to map values across vocabularies or systems. For example, you can map country codes, city codes, currency codes, and so on.

Create a Lookup

Create a lookup to map values between applications.

  1. In the navigation pane, click Integrations, then click Lookups.
  2. Click Create.
    The Create New Lookup dialog is displayed.
  3. Enter a name and optional description for the lookup.
  4. Click Create.
The Lookup page is displayed.

Add Adapters or Domain Names to a Lookup

Add adapters or domain names to a lookup to map values between connections.

  1. Click Domain Name 1.
    A menu with options is displayed.
  2. Select the type to create:
    • Select Adapter: This selection invokes the Select Adapter dialog for choosing the adapter to use. You can change your selection:

      • If you want to change your adapter selection, click the selected adapter type, then choose Select Different Adapter.

      • If you want to change from an adapter to a domain name, click the selected adapter type and choose Replace with Domain Name.

    • Enter Domain Name: This selection invokes a dialog for entering the domain name to use. There is a 50 character limit. You can change your selection:

      • If you want to change from a domain name to an adapter, click the domain name, then choose Replace with Adapter.

      • If you want to change the domain name, click the name, then choose Edit Domain Name.

      The adapter or domain name is displayed.

  3. Enter a value in the field below the adapter or domain name. To add more rows for additional values, click the + sign.
  4. Click Domain Name 2.
  5. Repeat Step 2 to add an adapter or domain name.
  6. Enter a value in the field below the adapter or domain name. To add more rows for additional values, click the + sign.
  7. If you want to add another column, click +.
  8. Repeat to add more adapters and domain names to the lookup.
  9. Click Save when complete.

Clone a Lookup

You can clone a copy of an existing lookup. It is a quick way to create a new lookup with similar information. Lookups with adapters and domain names can be cloned.

  1. On the Lookups page, select Clone from the hamburger menu menu.
    The Clone Lookup dialog is displayed.
  2. Enter the lookup information.
  3. Click Clone.
  4. Click Edit to further configure your cloned connection.

Delete a Lookup

You can delete a connection from the Lookup menu.

  1. Select Delete from the hamburger menu menu.
    The Delete Lookup dialog is displayed.
  2. Click Yes to confirm deletion.

Update Lookups in Active Integrations

Lookups are not deployed as part of integration activation. Therefore, changes that you make to lookups already used in active integrations take effect immediately. There is no need to re-activate integrations using a changed lookup for the new lookup value to take effect.

Export a Lookup

Once you create a lookup, you can export that lookup for use in other Oracle Integration environments. You can export a lookup from either the Lookup Designer or from the Lookups list.

To export a lookup:
  1. In the navigation pane, click Integrations, then click Lookups.
  2. Select the Actions menu to the right in the lookup’s row, then select Export to CVS.
  3. In the dialog that appears, select Save File, and then click OK.
  4. Save the file to the location you want.
The file is saved as a CSV file with the same name as the lookup.

Import a Lookup

You can import lookups that were previously exported from Oracle Integration.

The file to import must have the following for the first row, where table_name is the name of the table as you want it to appear in Oracle Integration. This name cannot contain spaces.
DVM,table_name

The second row contains the names of the adapters that are being mapped. Use the following case-sensitive IDs for each adapter:

Adapter Name Identifier to Use in the Import File
Oracle Eloqua Cloud eloqua
Oracle Engagement Cloud osc
Oracle Messaging Cloud Service oms
Oracle Service Cloud rightnow
Oracle HCM Cloud hcm
Oracle ERP Cloud erp
Salesforce Cloud salesforce
To import a lookup:
  1. Locate the CSV file containing the lookup table you want to import.
  2. In the navigation pane, click Integrations, then click Lookups.
  3. In the banner, click Import.
  4. In the Import Lookup dialog, click Browse to navigate to and select the CSV file to import.
  5. Click Import.
    If a lookup already exists with the same identifier and version, you must confirm whether to overwrite the existing lookup.
The imported lookup appears in the Lookups list on the left. You can customize or activate it, if it is ready.

Create the lookupValue Function

You can create the parameter values for the lookupValue function with the Build Lookup Function wizard. This wizard enables you to define the lookup table, source column, target column, and default value to use in the function. For these parameter values to be selectable in the wizard, you must have already created a lookup on the Lookups page.

Access the Build Lookup Function Wizard

The Build Lookup Function wizard for creating the lookupValue function parameter values is accessible from the Expression Builder in Oracle Integration.

To access the Build Lookup Function wizard:

Note:

You must already have created lookups to use this wizard. See Manage Lookups.
  1. Go to the Integrations page.

  2. Open an integration.

  3. Invoke the Expression Builder from within the integration. For example:

    • In an orchestrated integration, edit a switch activity.

    • In a basic integration, click the Filter link.

    The Expression Builder is displayed.

  4. Expand Components.

  5. Expand Functions > ICS.

  6. Expand the lookupValue function to view the available parameters to define in the Build Lookup Function wizard and Expression Builder.

    • dvmLocation

    • srcColumn

    • srcValue

    • targetColumn

    • defaultValue

  7. Drag the lookupValue function into the New Condition field.

    The Build Lookup Function wizard is displayed. To create the lookupValue function parameter values, see section Select the Lookup Table.

Select the Lookup Table

Select the lookup table to use in the lookupValue function.

Note:

You must already have created a lookup. Otherwise, no lookups are displayed for selection.
Element Description

Lookup Table

Select the lookup table to use in the function. You can view the lookup description by clicking the information icon in the table. This can guide you in selecting the required lookup table. The number of columns defined in the lookup is also displayed.

Select the Source and Target Columns

Select the source and target columns to use in the lookupValue function.

The lookupValue function requires one source column and one target column. When you select a source and target column, the values available with the columns are displayed.

Element Description

Select Source Column

Click the source column header to select from a list of available columns for this lookup table. The data included with the selected column is displayed. Both adapter and domain name columns are displayed.

Select Target Column

Click the target column header to select from a list of available columns for this lookup table. The data included with the selected column is displayed. Both adapter and domain name columns are displayed.

Specify the Default Value

Select the default value to use in the lookupValue function.

Enter the default value to use if no match is found. If there is no match that satisfies all the search values, the lookup fails and the default value is returned.

Element Description

Default Value

Enter a default value to use if no match is found (for example, an actual default value to use or an error message such as No Value Found).

Review Your Lookup Table Selections

You can review the lookup table values to use in the lookupValue function on the Summary page.

You can review the lookup table values from the Summary page. The Summary page is the final wizard page after you have completed your configuration.

Element Description

Parameter and Value Table

Displays a summary of the parameters and values you defined on previous pages of the wizard.

To return to a previous page to update any values, click the appropriate tab in the left panel or click Back.

Resulting Expression

Displays the expression you defined on the previous pages of the wizard. The lookupValue function takes the following format:

 lookupValue(dvmLocation, srcColumn, srcValue, targetColumn, defaultValue)

Where:

  • dvmLocation: The lookup table selected on the Select Lookup Table page.

  • srcColumn: The source column selected on the Select Columns page.

  • srcValue: The source value you enter in the New Condition field of the Expression Builder after completing this wizard. Click Done to complete this wizard, then define the srcValue parameter value.

  • targetColumn: The target column selected on the Select Columns page.

  • defaultValue: The default value entered on the Default Value page.

For example, a defined lookupValue function after you have completed the wizard and defined the srcValue parameter value in the Expression Builder can look as follows:
dvm:lookupValue('tenant/resources/dvms/Country','rightnow','US','mysoap','No data found')

When you click Done, the function icon is created in the mapper and the function XPath expression is displayed in the Expression Builder.