Using a lookup for mapping program to retrieve the specific value

[Former Member]

Hi All,

I have a scenario like …I need to use a lookup for mapping program to retrieve the specific value based on the input parameters.

Here I have got some rough idea like …

1. Creation of java program to connect the DB table and access the values, Import this java program as archive into XI.

2. Creation of user defined function to use the above java program

3. Include the user defined function in the interface mapping.

Here I feel it needs some more info to complete this scenario, so can anyone provide the step by step procedure for the above scenario.

Thanks in advance.

Vijay.

[Former Member · Accepted solution]

Hi Vijay,

Basically you have embed Database lookup code in the UDF. For all kind of Lookups refer to below links..

Lookup - /people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer

DB lookup - /people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

SOAP Lookup - /people/bhavesh.kantilal/blog/2006/11/20/webservice-calls-from-a-user-defined-function

http://help.sap.com/saphelp_nw04/helpdata/en/cf/406642ea59c753e10000000a1550b0

Lookup’s in XI made simpler - /people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

How to check JDBC SQL Query Syntax and verify the query results inside a User Defined Function of the Lookup API -

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

/people/prasad.illapani/blog/2006/10/25/how-to-check-jdbc-sql-query-syntax-and-verify-the-query-results-inside-a-user-defined-function-of-the-lookup-api

Lookups - /people/morten.wittrock/blog/2006/03/30/wrapping-your-mapping-lookup-api-code-in-easy-to-use-java-classes

Lookups - /people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

http://help.sap.com/saphelp_nw04/helpdata/en/cf/406642ea59c753e10000000a1550b0/content.htm

/people/sap.user72/blog/2005/12/06/optimizing-lookups-in-xi

Lookups with XSLT - https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/8e7daa90-0201-0010-9499-cd347ffb...

/people/sravya.talanki2/blog

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/05a3d62e-0a01-0010-14bc-adc8efd4...

How we have to create the lookups?

Check this weblogs with some screenshots on how to achieve this:

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

/people/sravya.talanki2/blog/2005/12/21/use-this-crazy-piece-for-any-rfc-mapping-lookups

/people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer

/people/sap.user72/blog/2005/12/06/optimizing-lookups-in-xi

/people/morten.wittrock/blog/2006/03/30/wrapping-your-mapping-lookup-api-code-in-easy-to-use-java-classes

Ranjeet Singh.

[Former Member · Accepted solution]

Still your requirement is not clear to me. what i understood is u will be getting values from file and for that value u need to use the lookup now my question is You wanted to lookup the value from R3 or you need to do a lookup in DB.

Please clarify the same.

Here are some example of lookup where it can be implemented.

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.

In the error handling topic we have seen the different validations which need to be performed on file. This can be done through Lookup.

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.

• Lookup for raising an alert.

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.

There are two ways in which we can do lookup:

• Call lookup method from GUI mapping.

• Call lookup method from XSLT mapping.

Lookup method from GUI mapping can be called using any of the following ways.

• RFC lookup using JCO (without communication channel)

/people/sravya.talanki2/blog/2005/12/21/use-this-crazy-piece-for-any-rfc-mapping-lookups

• RFC lookup with communication channel.

/people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer

• Lookup using JDBC adapter.

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

/people/sap.user72/blog/2005/12/06/optimizing-lookups-in-xi

• CSV file lookup.

/people/sundararamaprasad.subbaraman/blog/2005/12/09/making-csv-file-lookup-possible-in-sap-xi

Call lookup method from GUI mapping:

The steps are as follows:

• Develop and test ABAP function module.

• Develop and test Java method within Integration Repository/message mapping tool.

Thnx

Chirag

[Former Member · Accepted solution]

Hi,

Actually in my scenario, I have created a ztable in XI server itself.

Here do I need to have a JDBC connection in CC to connect the table or can i directly access the table with the help of java code which i want to write in user defined function.

Please aware me if iam wrong.

Thanks,

Vijay

[Former Member · Accepted solution]

HI,

You do not require to go for JDBC connection you can use simple lookup in UDF.Web log are already given in the thread...

Thnx

Chirag

[Former Member · Accepted solution]

Hi,

If it is a Ztable, i would suggest you to create an RFC which will execute open SQL to interact with the table and the RFC can be called from XI. However, if the table is going to be created on some external DB, then you need to create a datasource in Visual admin to point to the db. And then it in a UDF/Java class, use the data source to get a connection and perform the SQL operation. For enterprise level application, it is not advisable to keep the connection parameters in the Java file, so Data source is a prefered approach. It is scalable as well.

UDF or UDF calling Java class are both valid. But the second option would be better as the data base code can be isolated and reused.

cheers

danus

[Former Member · Accepted solution]

Hi Danus,

Thanks for the apt procedure.I have created UDF and tried to test but it is asking to import JCO pacakages. In the creation of UDF I did not given any Import pacakages.

Can anyone suggest what are the pacakages needs to be imported to run the UDF for this scenario.

Thanks,

Vijay

[Former Member · Accepted solution]

Hi,

Import the below package:

com.sap.mw.jco.,com.sap.aii.mapping.lookup.,java.io.*

Thnx

Chirag

[Former Member]

Hi All,

Thanks for the great support, I have got the lookup for mapping program it is working absolutly fine..

Thanks a lot for all the support.

Thanks,

Vijay Naredla

[aashish_sinha]

Hi Vijay,

You need to use the mapping pattern Table/Value lookup.This lookup process is easily accomplished using XSLT, since we can navigate the XML document using absolute or relative " path " specification. In message mapping , there is no facility to navigate the XML document.

In message mapping, we have to use the context of the different elements required for the lookup. A java function will be needed to perform the comparisions . and the matched value will have to be written to ResultList.

Hope this will help you.

Regards

Aashish Sinha

PS : reward points if helpful

[Former Member]

check this blog for DB lookup - /people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

[Former Member]

no need to create a seperate java program....you have to put your java code inside the user defined function itself......