How to Perform SAP Data Lookups Within XI Mappings

SAP Developer Network E-mail This Document

By Jerome Delune
16 October, 2003

Summary

This document explains how to access R/3 data from an XI mapping, via the SAP® Java Connector (SAP JCo). This is done with a special focus on how to handle shared Java libraries in SAP® Exchange Infrastructure (SAP XI) mapping objects.

Introduction

Within an XI mapping it is a common requirement to be able to perform data lookups on-the-fly. In particular, there may be a need to look up some data that is maintained in an R/3 application.

Some use cases:

  • Look up material number from table MARA.
  • Look up cost center budget.
  • Look up employee information.
  • Look up unit-of-measure (UOM) information from table t006a.

The purpose of the lookup may be:

  • To perform application-level validation of the data, before sending it to the backend.
  • To populate fields of the XML document with some additional data found in the backend application.

This is a form of value transformation.

The focus of this document is on accessing R/3 data only. The "value mappings" offered by XI are not adequate in this case, since the data would have to be manually entered in the Integration Directory.

User Exits

Since XI 2.0 does not provide out-of-the-box functionality for performing such lookups, this has to be implemented manually in the form of a user-exit.

Both graphical mappings (message-mapping objects) and XSLT offer the concept of user exits. Since the XI mapping runtime is Java-based, the user exits must be written in Java.

Accessing R/3 Data

All R/3 data is stored in the underlying database. One tempting option would be to utilize JDBC technology and execute a SQL statement directly against the database. However, this would go against the SAP recommendation, even in a read-only situation.

The general SAP recommendation is to utilize the OpenSQL technology from ABAP. Therefore the correct approach is to write a function module in ABAP which will perform the lookup.

As a consequence, the user exit in Java needs to call the function module in ABAP. Therefore the JCo must be used in order to establish this connectivity.

In future releases of the SAP Web Application Server (SAP Web AS), it will be possible to use the SQLJ technology in order to perform the lookup directly in the Java environment.

For R/3 applications running on a recent release of SAP Web AS, it may be an option to expose the lookup functionality as a Web service. The goal of the user exit would then be to make a call to this Web service. This option is not discussed in this document.

Java Libraries

JCo Libraries

The JCo is automatically available in the XI design-time and runtime environment, via the SAP J2EE Engine. If external tools are used for development and testing, the JCo libraries must be properly referenced during the compilation and testing process.

External Libraries

In some more complex situations, the Java lookup code may be utilizing external Java libraries. In this case it makes sense to import these libraries into the XI Integration Repository so that they are available to all mapping objects within the same namespace and software component.

If a set of Java libraries is to be used across multiple namespaces and software components, it may make sense to share these libraries at the J2EE engine level, instead of importing them multiple times in the XI tool.

In the sections below, we will discuss how to integrate shared libraries within Java user exits, either as imported archives within a SW component/namespace in the Integration Repository, or as core J2EE services.

Prerequisites

For the purpose of this example we assume the following:

  • JDK 1.3.1 is installed on local PC.
  • SAP JCo 2.1.0 is installed on local PC.
  • Environment variables CLASSPATH and PATH have been adjusted to properly reference JCo-related DLL libraries (sapjcorfc.dll and librfc32.dll).

Optionally, a Java IDE may be used for development purposes. We are using Eclipse 2.1.

Scenario

In this example we will implement a simple lookup into the R/3 table t006a (Units of measure).

The source XML document contains a UOM code ("KG" or "CM" for example). The purpose of the lookup is:

  1. To ensure that the UOM code is valid.
  2. To obtain the long text for the UOM code.

The UOM long text will be added as an additional field in the target XML document.

The columns of interest in table t006a are: MSEHI (UOM Code) and MSEHL (UOM long text).

The figure below illustrates the message flow and the lookup process.

In the next sections we will provide step-by-step guides for the following 4 procedures:

  1. Call lookup method from GUI mapping.
  2. Call lookup method from XSLT mapping.
  3. Reference external Java libraries within the same SW component/namespace.
  4. Reference external Java libraries across all SW components/namespaces.


Procedure 1: Call Lookup Method from GUI Mapping

The steps are as follows:

  1. Develop and test ABAP function module.
  2. Develop and test Java method within Integration Repository/message mapping tool.

Step 1: Develop and Test ABAP function Module

A sample is provided below. In this document we will not explore the details of ABAP programming. We consider this function module to be trivial.

FUNCTION Z_UOM_LOOKUP.
*"----------------------------------------------------------------------
*"*"Local interface:
*" IMPORTING
*" VALUE(UOMCODE) LIKE T006A-MSEHI
*" EXPORTING
*" VALUE(UOMTEXT) LIKE T006A-MSEHL
*" EXCEPTIONS
*" UOM_NOT_FOUND
*"----------------------------------------------------------------------

SELECT SINGLE MSEHL FROM T006A INTO UOMTEXT
    WHERE MSEHI = UOMCODE
        AND SPRAS = SY-LANGU.

    IF SY-SUBRC NE 0.
        RAISE UOM_NOT_FOUND.
    ENDIF.
ENDFUNCTION.


Notes:

  • Ensure that the function module is RFC-enabled.
  • Ensure that the parameters are passed by value and not by reference (mandatory for RFC-enabled function modules).
Articles/XI_Oct16/xi_mapping1.jpg

Figure 1. Function module is RFC-enabled.

Articles/XI_Oct16/xi_mapping2.jpg

Figure 2. Parameters are passed by value.

Step 2: Develop and Test Java Function

This is done from within the XI Integration Repository / Message mapping object.

Articles/XI_Oct16/xi_mapping3.jpg

Figure 3.

Articles/xi_oct16/xi_mapping4.jpg

Figure 4.

The sample code provided below requires basic JCo programming knowledge only.

In this document we will not explore the details of Java/JCo programming.

Note: The method UOMLookup() is developed and compiled entirely within the Integration Repository. For this particular scenario it is not necessary to develop and test the Java code externally (using an IDE for example). This approach is only suitable for very simple cases.

If the programming logic is more complex, or if debugging features are required, then it makes sense to use an IDE for this purpose.

Public String UOMLookup(String a, Container container){
JCO.Repository mRepository;

// Change the logon information to your own system/user
JCO.Client mConnection = JCO.createClient(
    "800", // SAP client
    "xilookup", // userid
    "test", // password
    "EN", // language
    "tsphl850", // host name
    "00" ); // system number
// connect to SAP
mConnection.connect();

// create repository
mRepository = new JCO.Repository( "SAPLookup", mConnection );

// Create function
JCO.Function function = null;
IFunctionTemplate ft = mRepository.getFunctionTemplate("Z_UOM_LOOKUP");
function = ft.getFunction();
// Obtain parameter list for function
JCO.ParameterList input = function.getImportParameterList();
// Pass function parameters
input.setValue( a , "UOMCODE" );

mConnection.execute( function );
String ret = function.getExportParameterList().getString( "UOMTEXT" );
mConnection.disconnect();

return ret;
}

Notes:

  • The userid should be a dedicated CPIC user with appropriate roles in the backend system. The JCo also allows for storing all connection data within a properties file, or utilizing logon tickets (SSO).
  • In the "Imports" area of the editing window, the package com.sap.mw.jco.* must be specified. As mentioned before, the JCo libraries are always accessible from the Integration Repository and from the runtime environment.
  • Exception handling should be coded as appropriate.
Articles/Xi_oct16/xi_mapping5.jpg

Figure 5.

The code can be compiled with the button. Any javac compilation errors will appear in a popup window.

The function can now be used anywhere within the same message mapping object only.

articles/xi_oct16/xi_mapping6.jpg

Figure 6.

The mapping can be tested with the standard testing tool.

Articles/xi_oct16/xi_mapping7.jpg

Figure 7.


Procedure 2: Call Lookup Method from XSLT Mapping

In this case it will be necessary to develop a Java Class containing the method UOMLookup(). We are using the Eclipse IDE for that purpose. The steps are as follows: The input XML document is the same as in the previous example:

  1. Develop and test Java class.
  2. Reference Java class within XSLT stylesheet.
  3. Import XSLT + Java archive into Integration Repository.

The input XML document is the same as in the previous example:

<?xml version="1.0" ?>
<?xml version="1.0" ?>
<Material>
    <Description>TEST</Description>
    <quantity>20</quantity>
    <UOMCode>KG</UOMCode>
</Material>

Step 1: Develop and Test Java Class

The logic is similar to the previous example. This time we are implementing a full Java class.

/*
* Created on Sep 25, 2003
*/
package com.sap.xirig.lookup;

import com.sap.mw.jco.*;

public class SAPLookup {

    public static String UOMLookup(String UOMCode) {
     JCO.Repository mRepository;

        // Change the logon information to your own system/user
        JCO.Client mConnection = JCO.createClient(
            "800",         // SAP client    
            "xilookup",    // userid
            "test",        // password
            "EN",         // language
            "tsphl850",    // host name
            "00" );     // system number
        // connect to SAP
        mConnection.connect();

        // create repository
        mRepository = new JCO.Repository( "SAPLookup", mConnection );

        // Create function
        JCO.Function function = null;
        function = createFunction( "Z_UOM_LOOKUP", mRepository );

        // Obtain parameter list for function
        JCO.ParameterList input = function.getImportParameterList();
        // Pass function parameters
        input.setValue( UOMCode, "UOMCODE" );

        mConnection.execute( function );

        String ret =
            function.getExportParameterList().getString( "UOMTEXT" );

        mConnection.disconnect();

     return ret;
    }

    public static JCO.Function createFunction( String name, JCO.Repository mRepository ) {

        IFunctionTemplate ft =
             mRepository.getFunctionTemplate( name.toUpperCase() );

        if (ft == null)
         return null;

        return ft.getFunction();
    }
    
    public static void main( String args[] ) {

     try {

         // SAPLookup sap = new SAPLookup();
         String ret = SAPLookup.UOMLookup( "KG" );
         System.out.println(ret);

     } catch( Exception e ) {
         e.printStackTrace();
     }
    }
}


Notes:

  • The method main() is for testing purposes only. The entry point for the class will be UOMLookup().
  • The method UOMLookup() must be defined as static.

The code can be tested and debugged from the Eclipse IDE. Please note, the JCo libraries must be made accessible at compilation time and runtime. The compiled CLASS is exported to a JAR file, for instance XILookup.jar

Articles/xi_oct16/xi_mapping8.jpg

Figure 8.

Step 2: Reference Java Class Within XSLT Stylesheet

The reference is done as follows:

<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
     xmlns:r3lookup="java:com.sap.xirig.lookup.SAPLookup">
<xsl:output method='xml' indent='yes' />
<xsl:template match="Material">
<Material>
    <xsl:copy-of select="Description"/>
    <xsl:copy-of select="quantity"/>
    <xsl:copy-of select="UOMCode"/>
    <xsl:variable name="UOMCode">
    <xsl:value-of select="UOMCode"/>
    </xsl:variable>
    <UOMText><xsl:value-of select="r3lookup:UOMLookup($UOMCode)"/></UOMText>
</Material>
</xsl:template>
</xsl:stylesheet>

The stylesheet can be tested on the desktop as follows.

Note: Both sapjco.jar and XILookup.jar must be included in the CLASSPATH.

java -cp inqmyxml.jar;XILookup.jar;c:\SAPJCo_2_1_0\sapjco.jar;.
com.inqmy.lib.xsl.xslt.CommandLine in.xml UOMlookup.xsl

The result looks like the following:

<?xml version="1.0" encoding="utf-8"?>
<Material>
    <Description>test</Description>
    <quantity>20</quantity>
    <UOMCode>KG</UOMCode>
    <UOMText>Kilogram</UOMText>
</Material>

Step 3: Import XSLT + Java Archive into the Integration Repository

After the XSLT mapping has been tested, it must be archived into a ZIP/JAR file. The relevant Java classes can be included in the same archive, or in a separate JAR file (See Procedure 3 below). The JCo classes should not be imported manually into XI because they are already available in the XI design-time and runtime.

Follow the standard procedure (Imported Archives) to upload the mapping into the XI Integration Repository.

Notes:

  • The directory structure for the location of the Java class within the ZIP file must match the package name.
  • There cannot be nested archives. However there can be several archives imported separately (see Procedure 3).
  • It is not necessary to include the Java sources in the ZIP file, however this may make sense from the maintenance point-of-view (the XI Integration Repository can act as central storage location for Java user exits).

In this example we are including the XSLT + Java class in one common archive, XILookup.zip.

Articles/xi_oct16/xi_mapping9.jpg


Procedure 3: Reference External Java Libraries Within the Same SW Component/Namespace

In most cases it makes sense to make some mapping logic available through one or more separate Java classes. The idea is to be able to reuse those classes from any mapping object (graphical mapping or XSLT) within the same SW component and namespace.

The procedure to follow is simple:

Each JAR file should be imported into the XI Integration Repository, as an "Imported Archive" object. Then it should be accessible from any mapping object within the same SW Component and namespace.

For example, the Java CLASS from Procedure 2 can be included in a JAR (XILookup.jar) and imported into the XI Integration Repository.

The message mapping from Procedure 1 can therefore be simplified, as follows:

Articles/xi_oct16/xi_mapping10.jpg

Figure 10.

Any referencing XSLT mappings will remain exactly the same. Indeed this technique is already used implicitly when calling Java extensions from XSLT.

For example, for the XSLT mapping described in Procedure 2, we could have separated the Java classes from the XSLT mapping itself. Each of them could be included in a separate archive.

In fact, SAP recommends that you separate Java classes and XSL when structuring archives since this can improve performance (as stated in the XI 2.0 SR1 online help).


Procedure 4: Reference External Java Libraries Across All SW Components/Namespaces

This is a more complex procedure that involves system configuration of the SAP J2EE engine. Therefore, this should only be performed by advanced users, or with the assistance of a J2EE administrator.

If one or more classes are to be reused across different SW components and namespaces, it makes sense to configure the SAP J2EE Engine to expose them as shared libraries. These shared libraries can be accessible from one or several J2EE applications. It is important to understand that XI consists of several J2EE applications. In particular, the J2EE applications that are relevant for this exercise are the following:

"IntegrationServices": mapping runtime
"ExchangeRepository": integration repository

Our goal in this example is to take the Java CLASS defined in Procedure 2, and make it visible across the whole XI design-time and runtime environments.

The steps to take are as follows:

  1. Upload the libraries onto the file system where the SAP J2EE engine resides.
  2. Adjust library.txt and reference.txt to expose the shared libraries.
  3. Restart the J2EE engine.

Step 1: Upload Libraries onto the J2EE Engine File System

Every JAR file must be copied into the directory \usr\sap\...\j2ee\cluster\server\additional-lib

Step 2: Adjust library.txt and reference.txt

These files are located in the directory \usr\sap\...\j2ee\cluster\server\managers

First an entry needs to be added in the file library.txt. The purpose is to define an alias for each library that needs to be shared. The entry should be as follows:

library XILookup xilookup.jar

Note that the JCo already has its own entry since many applications are using it.

Now entries need to be added to the file reference.txt. The purpose is to make each library visible to the appropriate J2EE applications, as well as to define interdependencies between libraries. In our case the entries are as follows:

reference library:XILookup library:jco

(The XILookup class needs access to JCO)

reference IntegrationServices library:XILookup

(make XILookup visible to mapping runtime)

reference ExchangeRepository library:XILookup

(make XILookup visible to design-time)

Step 3: Restart the J2EE Engine.

Follow standard procedure from transaction SMICM.

The classes in XILookup.jar should now be accessible from all mapping objects.

Please note, this is applicable to SAP WebAS 6.20 only. In release 6.30, the procedure will be enhanced.

Conclusion

The principles of performing the JCo lookup itself are straightforward. Any programmer with basic JCo knowledge should be able to implement this functionality. The integration of external Java libraries into XI can be done at different levels, depending on the degree of reusability that needs to be achieved:

  • Integration of single method as User-defined function in graphical mapping -- User-defined function is only visible within the same mapping object.
  • Java classes as imported archives within XI Integration Repository -- Classes are visible from any mapping (GUI or XSLT) within the same SW component/namespace.
  • Java classes as shared libraries at the J2EE engine level -- Classes are visible throughout the XI design-time and runtime.

Copyright © 2003 SAP AG, Inc. All Rights Reserved. SAP, mySAP, mySAP.com, xApps, xApp, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other product, service names, trademarks and registered trademarks mentioned are the trademarks of their respective owners.

SAP Developer Network E-mail This Document