1. Introduction

In enterprise integration scenarios, data formats and codes often differ between source and target systems. For example, a source application might represent a country as ‘IN’ while the target ERP system expects ‘India’. Manually handling such mappings in XSLT or expressions can become complex and hard to maintain.

Oracle Integration Cloud (OIC) provides a powerful built-in feature called Lookups that enables developers to define and manage cross-reference value mappings centrally — without hardcoding them in integration logic. These mappings are consumed in OIC mappers using the lookupValue function.

This blog explains what Lookups are, how to create them, and how to use the lookupValue function effectively in your OIC integrations.

2. What Are Lookups in OIC?

A Lookup in OIC is a named mapping table that stores key-value pairs for cross-referencing data between different systems or domains. Think of it as a simple translation dictionary that lives inside OIC.

A Lookup in OIC is a named mapping table that stores key-value pairs for cross-referencing data between different systems or domains. Think of it as a simple translation dictionary that lives inside OIC. 

2.1 Common Use Cases 

• Mapping country codes between systems (IN vs India vs IND) 

• Translating document types (INV vs INVOICE vs Tax Invoice) 

• Converting status codes (S vs Success vs COMPLETED) 

• Mapping department codes between HR and Finance systems 

• Currency code translations (INR vs Indian Rupee)de 

• Translating error codes to human-readable messages 

 2.2 Lookup Structure 

A Lookup table in OIC has the following structure:

Domain 1 (Source System)	Domain 2 (Target System)	Domain 3 (Optional)	Notes
IN	India	IND	Country Code
US	United States	USA	Country Code
GB	United Kingdom	GBR	Country Code
AU	Australia	AUS	Country Code

Key Concept

A Lookup can have multiple domains (columns). Each row maps equivalent values across different systems. You can have 2 to many domains per lookup table.

3. How to Create a Lookup in OIC

Creating a Lookup in OIC is straightforward. Follow these steps:

Step 1: Navigate to Lookups

• Log in to your OIC instance.
• Go to Design menu in the left navigation.
• Click on Lookups.
• Click the Create button (+ icon).

Step 2: Define the Lookup

• Enter a unique Lookup Name (e.g., COUNTRY_CODE_LOOKUP).
• Add a Description for easy identification.
• Define the Domain Names (columns) — e.g., Source_Code, Target_Name, ISO_Code.
• Click Add Row to add mapping entries.
• Fill in the values for each domain across all rows.
• Click Save when done.

Naming Convention Tip

Use UPPERCASE with underscores for lookup names — e.g., COUNTRY_CODE_LOOKUP, DOCTYPE_MAPPING, STATUS_CODE_REF. This makes them easy to search and identify in the mapper

3.1 Example: Document Type Lookup

Let us create a lookup to map E-Invoice document types between source and Oracle ERP Cloud:

Source_DocType	ERP_DocType	Description
INV	STANDARD	Standard Tax Invoice
CRN	CREDIT_MEMO	Credit Note / Return
DBN	DEBIT_MEMO	Debit Note
EXP	EXPENSE	Expense Report

4. The lookupValue Function

Once a Lookup is created, you use the lookupValue function inside the OIC Mapper (XSLT) to retrieve the mapped value at runtime.

4.1 Function Syntax

lookupValue(
    'LookupName',       -- Name of the lookup table
    'SourceDomain',     -- Domain name to search IN
    SourceValue,        -- The value to look up
    'TargetDomain',     -- Domain name to retrieve FROM
    'DefaultValue'      -- Value to return if no match found
)

4.2 Parameter Details

Parameter	Type	Description
LookupName	String (literal)	Exact name of the OIC Lookup table
SourceDomain	String (literal)	Column name to search the input value in
SourceValue	XPath / String	The actual value to look up — usually from source payload
TargetDomain	String (literal)	Column name whose value you want to retrieve
DefaultValue	String (literal)	Returned when no matching row is found — never leave empty

4.3 Practical Example — Document Type Mapping

Scenario: The source system sends DocType as ‘INV’ but Oracle ERP Cloud expects ‘STANDARD’. Using the DOCTYPE_MAPPING lookup:

lookupValue(
    'DOCTYPE_MAPPING',
    'Source_DocType',
    /nssrcmpr:request/ns1:DocType,
    'ERP_DocType',
    'STANDARD'
)

How this works at runtime:

• Source payload has DocType = ‘INV’.
• lookupValue searches DOCTYPE_MAPPING lookup in Source_DocType column for ‘INV’.
• Finds the row where Source_DocType = ‘INV’.
• Returns the value from ERP_DocType column = ‘STANDARD’.
• If no match is found, returns the default value ‘STANDARD’.

4.4 Example — Country Code Mapping

lookupValue(
    'COUNTRY_CODE_LOOKUP',
    'Source_Code',
    /nssrcmpr:request/ns1:CountryCode,
    'Target_Name',
    'Unknown'
)

Input: CountryCode = 'IN' --> Output: 'India'

5. Using lookupValue in the OIC Mapper

Here is how to use lookupValue directly inside the OIC graphical mapper:

1. Open your integration and click on the Map activity.
2. In the Target field where you want the mapped value, click the Expression Editor icon.
3. In the expression editor, go to the Functions tab.
4. Expand Lookup Functions section.
5. Double-click lookupValue to insert it into the expression.
6. Fill in the parameters — Lookup name, source domain, source value XPath, target domain, default value.
7. Click Validate to confirm the expression is correct.
8. Click Save.

Pro Tip

You can also type the lookupValue function directly in the expression editor if you know the lookup name and domain names. This is faster than navigating through the Functions panel.

6.1 Alw6. Best Practice says Provide a Default Value

Never leave the DefaultValue parameter empty. If the lookup does not find a match and there is no default, the integration will fail at runtime.

BAD: No default value

lookupValue('MY_LOOKUP', 'Source', /src/value, 'Target', '')

GOOD: Meaningful default value

lookupValue('MY_LOOKUP', 'Source', /src/value, 'Target', 'UNKNOWN')

6.2 Lookup Naming Conventions

• Use UPPERCASE with underscores: COUNTRY_CODE_LOOKUP
• Include the purpose in the name: DOCTYPE_MAPPING, STATUS_REF
• Add version if needed: GSTIN_CODE_LOOKUP_V2
• Keep names short but descriptive

6.3 Domain Naming

• Use descriptive domain names: Source_System, ERP_Code, Display_Name
• Avoid spaces in domain names — use underscores.
• Document the domain purpose in the lookup description.

6.4 Maintenance

• Lookups are reusable across integrations — avoid duplicating lookup tables.
• When a new mapping is needed, just add a row — no code change required.
• Export lookups periodically as backup.
• Review and validate lookup data before each release.

7. Common Errors and Troubleshooting

Error	Cause	Fix
Lookup not found	Wrong lookup name in function	Verify exact name in OIC Lookups menu
Returns default always	Source value not matching any row	Check for case sensitivity or extra spaces in data
Domain name error	Typo in domain name parameter	Copy domain name exactly from lookup definition
Empty result	Default value is empty string	Always provide a meaningful default value
Integration fails	No match + no default value	Add a non-empty default value

Important Note

Lookup values are case-sensitive. If your source sends ‘inv’ (lowercase) but the lookup has ‘INV’ (uppercase), it will not match. Use the upper-case() or lower-case() XPath function to normalize before passing to lookupValue.

8. Sample Scenario: Order Management Integration

Consider an Order Management integration where a source system sends order and customer data in its own format, but the target ERP system expects different codes. Lookups can handle all the translations centrally:

Lookup Name	Source Domain	Target Domain	Purpose
ORDER_TYPE_MAP	Source_OrderType	ERP_OrderType	Map order types between systems
CUSTOMER_STATUS_MAP	Source_Status	ERP_Status	Map customer status codes
CURRENCY_CODE_MAP	Source_Currency	ERP_Currency	Map currency codes
PAYMENT_TERM_MAP	Source_PayTerm	ERP_PayTerm	Map payment terms

With this approach, when business rules change — for example, a new order type is introduced — only the lookup table needs updating. No changes to the integration flow, no redeployment, no regression testing of integration logic.

Business Value

This kind of centralized lookup-driven design significantly reduces the cost of change management in enterprise integrations. Business teams can even manage lookup data themselves without involving the development team.

9. Advantages of Using Lookups

• Centralized maintenance — update mapping in one place, reflects across all integrations.
• No redeployment needed — lookup data changes take effect immediately.
• Business-user friendly — non-developers can update lookup values.
• Reduces hardcoding — cleaner, more maintainable integration code.
• Reusable — same lookup can be used in multiple integrations.
• Auditable — lookup changes are logged in OIC activity stream.

10. Conclusion

OIC Lookups with the lookupValue function are an essential tool in every integration developer’s toolkit. They provide a clean, OIC Lookups with the lookupValue function are an essential tool in every integration developer’s toolkit. They provide a clean, maintainable, and business-friendly way to handle cross-reference mappings between systems — eliminating hardcoded values and reducing maintenance overhead.

Key takeaways from this blog:

• Lookups are centralized mapping tables in OIC with multiple domains.
• lookupValue(LookupName, SourceDomain, Value, TargetDomain, Default) is the function syntax.
• Always provide a meaningful default value to avoid runtime failures.
• Domain names and lookup values are case-sensitive.
• Lookups are reusable across integrations and update without redeployment.

Start replacing hardcoded mappings in your integrations with OIC Lookups today and experience cleaner, more maintainable integration flows. Organisations looking to streamline cross-reference mapping and connect applications efficiently can explore Kovaion’s Oracle Integration Cloud (OIC) integration services to support their enterprise integration requirements.

Simplify Oracle Integration Cloud with Kovaion’s OIC Expertise

Managing cross-reference mappings across enterprise applications does not have to involve complex XSLT logic or hardcoded values. With Oracle Integration Cloud (OIC) Lookups and the lookupValue function, organisations can simplify data transformations, centralise mapping maintenance, and build more flexible, maintainable integration workflows.

Kovaion helps organisations streamline enterprise integrations with Oracle Integration Cloud, connecting applications, automating data flows, and supporting efficient information exchange across business systems. From integration design and implementation to optimisation and ongoing support, our Oracle expertise helps businesses address integration challenges and adapt to evolving requirements.

Whether you are modernising existing integrations, reducing mapping-related maintenance, or connecting Oracle Cloud applications with third-party systems, Kovaion can help you build an integration approach aligned with your business needs.

Ready to simplify your enterprise integrations with Oracle Integration Cloud? Connect with Kovaion to explore how our OIC expertise can help improve integration efficiency, maintainability, and scalability.