Again on the age-old problem of value mapping in SAP XI...
This time I think I found a nice methodology, general enough to cover 95% use cases.
Written in cooperation with my colleague and friend Matteo Franciolli.
Introduction
Let's be honest: customers just keep on asking the same things (sometimes also referred to as requirements :-), and most of the times architects/developers have common (dumb) answers. Once again I was asked about value mapping in XI, how it can be done, advantages, limitations and so on. But this time I didn't answer: "Well, in my opinion there's no good method", which is really a bad answer, and a thought I still partially share.
I decided to turn the screw, I figure out something smarter.
Literature
This is not the first article on the subject... So that fortunately I'm not forced to make everything explicit... At least four pieces deserve to be quoted:
* XI Mapping lookups RFC API, by Michal the-never-sleepin' Krawczyk, a good starting point, but a bit too raw for my taste.
* Lookup's in XI made simpler, by Siva Maranani, dealing with DB lookup, quite a simple one.
* Use this crazy piece for any RFC Mapping Lookups, by my SDN-friend Sravya Talanki, based on RFC_READ_TABLE function module and JCo, very effective and for sure more perfomant than the Lookup API.
* Making CSV File Lookup Possible In SAP XI !!!, by Sundararamaprasad Subbaraman, which I didn't go through honestly, but the CSV idea sounds good.
Finally, the Lookup API JavaDoc should be your real bible here.
So, what's new?
Given the above links, which seem to already cover any aspect of this vital and out-of-focus matter, you may be wondering what else can be said... Well, my job here is based on the following strong assumption:
In mappings, when you need to put a converted value in the target document, you need to put one value, and in order to get it you need to provide one or more values either taken from the source document or defaulted, which will serve as search criteria.
The goal here is to provide a simple method to perform these kind of lookups both for RFC and JDBC sources.
The LookupHandler
If you read other blogs'o'mine you probably know I'm a big fan of Netweaver Developer Studio, for a lot of reasons, but in this case mainly because I feel more confortable writing Java code in NWDS rather than in UDF editor, where you don't have syntax check, great context hints and so on. So in NWDS you need to create a simple Java project, a package and a class. Include in your build path the aii_map_api.jar (the biggest one you can find in your XI box file system, my own being about 38 Kb on SP15... make a search!).
The code of my LookupHandler class is below, where you'll notice I made my best with Java DOM ;-)
------------------------------------------------------------------------------------------------------------------------------------
/*
* Created on 23-mar-2006
*
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
package com.sic.nw.xi.mapping.udf;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.util.Iterator;
import java.util.Map;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.*;
import com.sap.aii.mapping.api.AbstractTrace;
import com.sap.aii.mapping.lookup.*;
/**
* @author Ale
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class LookupHandler {
public static String RFCLookup(String table, String[] keyNames, String[] keyValues, String resultFieldName, AbstractTrace trace, String service) {
final String CHANNEL_NAME = "cc_Rfc_Receiver_Lookup",
VALNOTFOUND = "VALUE_NOT_FOUND",
SAPRFCNS = "urn:sap-com:document:sap:rfc:functions",
TAG_FM = "Z_BC_RFCLOOKUP",
TAG_QTB = "QUERY_TABLE",
TAG_QFL = "QUERY_FIELD",
TAG_RES = "DATA_OUT",
TAG_OPT = "OPTIONS",
TAG_OPT_N = "NAME",
TAG_OPT_V = "VALUE";
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = null;
factory.setNamespaceAware(false);
factory.setValidating(false);
try {
builder = factory.newDocumentBuilder();
} catch (Exception e) {
trace.addWarning("Error creating DocumentBuilder - " + e.getMessage());
return null;
}
Document docReq = null;
try {
// Building up RFC Request Document
docReq = builder.newDocument();
Node root = docReq.appendChild(docReq.createElementNS(SAPRFCNS, TAG_FM));
root.appendChild(docReq.createElement(TAG_QTB)).appendChild(docReq.createTextNode(table));
root.appendChild(docReq.createElement(TAG_QFL)).appendChild(docReq.createTextNode(resultFieldName));
Node nodeTbOpt = root.appendChild(docReq.createElement(TAG_OPT));
for (int i = 0; i < keyNames.length; i++) {
Node nodeTbOptItem = nodeTbOpt.appendChild(docReq.createElement("item"));
nodeTbOptItem.appendChild(docReq.createElement(TAG_OPT_N)).appendChild(docReq.createTextNode(keyNames[i]));
nodeTbOptItem.appendChild(docReq.createElement(TAG_OPT_V)).appendChild(docReq.createTextNode(keyValues[i]));
}
} catch (Exception e) {
trace.addWarning("Error while building RFC Request - " + e);
return null;
}
trace.addInfo("RFC Request XML: " + docReq.toString());
// Lookup
Payload result = null;
try {
Channel channel = LookupService.getChannel(service, CHANNEL_NAME);
RfcAccessor accessor = LookupService.getRfcAccessor(channel);
InputStream is = new ByteArrayInputStream(docReq.toString().getBytes());
XmlPayload payload = LookupService.getXmlPayload(is);
result = accessor.call(payload);
} catch (LookupException e) {
trace.addWarning("Error during lookup - " + e);
return null;
}
// Parsing RFC Response Document
Document docRsp = null;
try {
docRsp = builder.parse(result.getContent());
} catch (Exception e) {
trace.addWarning("Error when parsing RFC Response - " + e.getMessage());
return null;
}
trace.addInfo("RFC Response XML: " + docRsp.toString());
String res = "";
try {
res = docRsp.getElementsByTagName(TAG_RES).item(0).getFirstChild().getNodeValue();
} catch (Exception e) {
trace.addWarning("Result value not found in DOM - " + e);
return VALNOTFOUND;
}
return res;
}
public static String JDBCLookup(String table, String[] keyNames, String[] keyValues, String[] keyTypes, String resultFieldName, AbstractTrace trace, String service) {
final String CHANNEL_NAME = "cc_JDBC_Receiver_Lookup", VALNOTFOUND = "VALUE_NOT_FOUND";
// Build of SQL Statement
String sqlStmt = "SELECT " + resultFieldName + " FROM " + table + " WHERE ";
for (int i = 0; i < keyNames.length; i++) {
if (i > 0)
sqlStmt += " AND ";
// Keys are not types, so assume they're all char
if (keyTypes==null) {
sqlStmt += keyNames[i] + "='" + keyValues[i] + "'";
} else {
if (keyTypes[i].equalsIgnoreCase("C"))
sqlStmt += keyNames[i] + "='" + keyValues[i] + "'";
else
sqlStmt += keyNames[i] + "=" + keyValues[i];
}
}
trace.addInfo("SQL Statement: " + sqlStmt);
// Lookup
DataBaseAccessor accessor = null;
DataBaseResult dbRes = null;
try {
Channel channel = LookupService.getChannel(service, CHANNEL_NAME);
accessor = LookupService.getDataBaseAccessor(channel);
dbRes = accessor.execute(sqlStmt);
} catch (LookupException e) {
trace.addWarning("Error during lookup - " + e);
return null;
} finally {
if (accessor != null)
try {
accessor.close();
} catch (LookupException e1) {
trace.addWarning("Error closing accessor - " + e1);
}
}
trace.addInfo("Rows retrieved from DB: " + dbRes.getRows());
String res = "";
try {
// Getting reponse (we just want the 1st)
Iterator rows = dbRes.getRows();
Map rowMap = (Map) rows.next();
trace.addInfo("Column class - " + rowMap.get(resultFieldName).getClass().getName());
res = new String(rowMap.get(resultFieldName).toString());
} catch (Exception e) {
trace.addWarning("Result value not found in resultset or Exception thrown - " + e);
return VALNOTFOUND;
}
return res;
}
public static String JDBCLookup(String table, String[] keyNames, String[] keyValues, String resultFieldName, AbstractTrace trace, String service) {
return JDBCLookup(table, keyNames, keyValues, null, resultFieldName, trace, service);
}
}
------------------------------------------------------------------------------------------------------------------------------------
To integrate in XI you just need to export as .jar file (remember to export source also: it's always good for people comin' after you to be able to look at source code at a glance!), and import it as Imported Archive in XI Repository.
Define an Advanced User Defined Function (I named it SmartLookup), with a signature as shown in the picture below. 
The code in the function is really simple, of course because all of the needed intelligence it's already written in the java class! 
A sample usage in the mapping can be represented by the picture below.
For those of you with a basic knowledge of R/3 tables, you should easily realize what I'm looking for: the name of customer in the Company data of the Customer Master Data, given Z001 as company code and MYTOWN as city (as constants) and the customer code taken from the source document. 
You'll notice the use of the MakeArray UDF, whose code follows (btw, if anyone has a smarter idea to get the same result, please let me know!), which is needed to provide multiple names and values for the lookup WHERE clause (if you think this is obscure, see the ABAP function code before you despair). 
Ok, now we're just missing the RFC function code (as you have probably guessed from the LookupHandler source code, JDBC handling is much simpler...), which, again, is basic and highly enhanceable (sorry about italian comments ;-)
LOOP AT options.
CONCATENATE '''' options-value '''' INTO options-value.
CONCATENATE clause sep options-name c_eq
options-value INTO clause separated by space.
IF sy-tabix = 1.
sep = and.
ENDIF.
ENDLOOP.
SELECT SINGLE (query_field) INTO data_out
FROM (query_table)
WHERE (clause).
Enhancements
A couple of things still need to be said.
First, in the LookupHandler class Java code I've put some stuff as constants, which you might like or not. To have the receiver channel named cc_<type>_Receiver_Lookup it's a choice. If you don't like it, put it as a parameter.
Secondly, the Service name is passed to the LookupHandler method as a constant in the UDF... Of course here you may find something much smarter, like retrieving the receiver or sender name (based on your message direction) from the Map taken form the Container object... (have a look here). For instance, if you have an IDoc2File scenario, and you're storing lookup values on R/3 tables (to have users confortably editing values with SM30 ;-) you'll wanna get the Service name from the Sender, like this:
String Sender;
java.util.Map map;
// get constant map
map = container.getTransformationParameters();
Sender = (String) map.get(StreamTransformationConstants.SENDER_SERVICE);
BUG FIXED
I realized today (30/03/2006) a stupid bug that could heavily affect the percentage of succesful JDBC lookups!
The bug: you cannot - of course - build the WHERE clause of the SQL statement assuming values are always characters, and thus enclosing them with single quotes!
The solution: the code of the LookupHandler class has been updated; the JDBCLookup method has been overloaded, so that the call in the UDF described here keeps working (but with the above said limitation). If you want to get rid of this problem, you'll have to define another array in the UDF declaration, named keyTypes for instance, where for each field (search criteria) you'll set a constant, either "C" (character, so single-quoted in the WHERE clause) or "N"(numeric, so without single quotes). Always use the makeArray UDF for this purpose. Obviously, keyTypes[] must be passed to the JDBCLookup method which will then properly build the WHERE clause.
I found another minor issue - corrected as well - when retrieving the result value, which was working only for strings, but not e.g. with BigDecimal (typically PACKED table fields).
Conlcusion
Even though I know I won't probably get any feedback, if you feel you have a better idea, or ways to enhance it, please let me know. I will be pleased by helping you bulding something better (as I already did with other SDNers).
