Solved: JDBC mapping to IDOC issue

We are retrieving information from our SQL server and trying to map it to the picking IDOC SDPIID01. We are having a problem in trying to get the correct mapping to occur. The result list is as follows:

<mt_SQLPickConfirmation>

<row>

<Trans_ID />

<Trans_IDOC />

<VBELN_VL />

<WADAT_IST />

<POSNR_VL />

</row>

</mt_SQLPickConfirmation>

and the IDOC is as follows:

<VBELN_VL />

<WADAT_IST />

<VBELN_VL />

<POSNR_VL />

</E1VPDLI>

</E1VPDLH>

</IDOC>

</SDPIID01>

Now some of the information goes into the segment E1VPDLH and some of the information goes into the segment E1VPDLI. The problem is trying to create a new IDOC everytime the field TRANS_ID changes.

We have tried to manipulate the context but still have problems with the output. The data can contain many rows with the same TRANS_ID number. Every time the TRANS_ID number changes we need a new IDOC created. All of my trys have failed. I either get one IDOC with a header segment and all the line items segments for each row or I get multiple IDOCS that do not contain the correct line item information.

Any help is appreciated. Attached is a sample of the data.

<?xml version="1.0" encoding="utf-8"?>

<mt_SQLPickConfirmation>

<row>

<TRANS_ID>1</TRANS_ID>

<TRANS_IDOC>SDPIID01</TRANS_IDOC>

<VBELN_VL>0080593473</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<POSNR_VL>000001</POSNR_VL>

</row>

<row>

<TRANS_ID>2</TRANS_ID>

<TRANS_IDOC>SDPIID01</TRANS_IDOC>

<VBELN_VL>0080593503</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<POSNR_VL>000001</POSNR_VL>

</row>

<row>

<TRANS_ID>3573932</TRANS_ID>

<TRANS_IDOC>2</TRANS_IDOC>

<VBELN_VL>0080593503</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<POSNR_VL>000002</POSNR_VL>

</row>

<row>

<TRANS_ID>2</TRANS_ID>

<TRANS_IDOC>SDPIID01</TRANS_IDOC>

<VBELN_VL>0080593503</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<POSNR_VL>000003</POSNR_VL>

</row>

<row>

<TRANS_ID>3</TRANS_ID>

<TRANS_IDOC>SDPIID01</TRANS_IDOC>

<VBELN_VL>0080593504</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<POSNR_VL>000001</POSNR_VL>

</row>

<row>

<TRANS_ID>3</TRANS_ID>

<TRANS_IDOC>SDPIID01</TRANS_IDOC>

<VBELN_VL>0080593504</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<POSNR_VL>000002</POSNR_VL>

</row>

<row>

<TRANS_ID>3</TRANS_ID>

<TRANS_IDOC>SDPIID01</TRANS_IDOC>

<VBELN_VL>0080593504</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<POSNR_VL>000003</POSNR_VL>

</row>

</mt_SQLPickConfirmation>

This should contain three IDOCS That look like this.

<?xml version="1.0" encoding="UTF-8"?>

<VBELN_VL>0080593473</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<VBELN_VL>0080593473</VBELN_VL>

<POSNR_VL>000001</POSNR_VL>

</E1VPDLI>

</E1VPDLH>

</IDOC>

<VBELN_VL>0080593503</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<VBELN_VL>0080593503</VBELN_VL>

<POSNR_VL>000001</POSNR_VL>

</E1VPDLI>

<VBELN_VL>0080593503</VBELN_VL>

<POSNR_VL>000002</POSNR_VL>

</E1VPDLI>

<VBELN_VL>0080593503</VBELN_VL>

<POSNR_VL>000003</POSNR_VL>

</E1VPDLI>

</E1VPDLH>

</IDOC>

<VBELN_VL>0080593504</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<VBELN_VL>0080593504</VBELN_VL>

<POSNR_VL>000001</POSNR_VL>

</E1VPDLI>

<VBELN_VL>0080593504</VBELN_VL>

<POSNR_VL>000002</POSNR_VL>

</E1VPDLI>

<VBELN_VL>0080593504</VBELN_VL>

<POSNR_VL>000003</POSNR_VL>

</E1VPDLI>

</E1VPDLH>

</IDOC>

</SDPIID01>

Any ideas how to achieve this?

Thanks,

Jim

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (1)

Stefan,

We are currently on SP12. Is there some advanced user function that would simplify this process?

Thanks,

Jim

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Jim,

If you want to use formatByExample as user defined function, you can use following code:


     for (int j=0,k=0;j<a.length&&k<b.length;k++){
        if (b[k].equals(ResultList.CC))
            result.addContextChange();
        else{
            while (a[j].equals(ResultList.CC) && j<a.length){
                j++;
            }
        result.addValue(a[j]);
        j++;
        }  
    }

I am not sure, if it is the exact code, but for your scenario it should work.

When you define the advanced funtion, check the flag 'cache whole queue'.

When you use this function, you can replace it with the standard function after patching.

Instead of index use counter, that should work as well.

Let me know, if it works for you.

Regards

Stefan

Hi Stefan,

I did above mentioned mapping using formatByExample(as User-Defined function), its working partially.

But its not generating as many IDocs as VBELN_VL values.

So I changed IDOC node mapping as:

VBELN_VL - removeContexts - splitByValue(ValueChanged) - collapseContexts - exists - creatIf - IDOC. and

VBELN_VL - removeContexts - splitByValue(ValueChanged) - E1VPDLH.

Now its just generating IDOC(2nd IDOC for change in VBELN_VL value) with header segment E1VPDLH not header segment fileds and no line item segments E1VPDLI.

Do you have any idea what else we can do to achieve the same??

Output should look like this:

<IDOC_1 BEGIN="">

<VBELN_VL>0080593473</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<VBELN_VL>0080593473</VBELN_VL>

<POSNR_VL>000001</POSNR_VL>

</E1VPDLI>

</E1VPDLH>

</IDOC>

<IDOC_2 BEGIN="">

<VBELN_VL>0080593503</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<VBELN_VL>0080593503</VBELN_VL>

<POSNR_VL>000001</POSNR_VL>

</E1VPDLI>

<VBELN_VL>0080593503</VBELN_VL>

<POSNR_VL>000002</POSNR_VL>

</E1VPDLI>

<VBELN_VL>0080593503</VBELN_VL>

<POSNR_VL>000003</POSNR_VL>

</E1VPDLI>

</E1VPDLH>

</IDOC>

<IDOC_3 BEGIN="">

<VBELN_VL>0080593504</VBELN_VL>

<WADAT_IST>20051219</WADAT_IST>

<VBELN_VL>0080593504</VBELN_VL>

<POSNR_VL>000001</POSNR_VL>

</E1VPDLI>

<VBELN_VL>0080593504</VBELN_VL>

<POSNR_VL>000002</POSNR_VL>

</E1VPDLI>

<VBELN_VL>0080593504</VBELN_VL>

<POSNR_VL>000003</POSNR_VL>

</E1VPDLI>

</E1VPDLH>

</IDOC>

</SDPIID01>

Thank You

Indrasena

Hi Stefan,

We got it. We are getting required output.

I made two small changes to the mappign you given

VBELN_VL - removeContexts - splitByValue(ValueChanged) - collapseContexts - exists - creatIf - IDOC. and

VBELN_VL - removeContexts - splitByValue(ValueChanged) - index - splitByValue(ValueChanged) - E1VPDLH.

Everything else is same. Used formatByExample as User-Defined function.

Thank You Very Much

Indrasena

Hi Indrasena,

I am sorry, I did not read the request carefully enough.

Now I have adjusted the mapping. It should repeat the IDOC node as well (and not only the E1VPDLH node).

Regards

Stefan

Hi Indrasena,

great, but one remark from my side:

> VBELN_VL - removeContexts - splitByValue(ValueChanged) - index - splitByValue(ValueChanged) - E1VPDLH.

This might not work in each mapping.

In your case it would be OK, just to use this:

constant(empty) - E1VPDLH

Regards

Stefan

Stefan,

Thanks for your help on this problem. We have know solved the issue thanks to your knowledge. 1000 points to you. Unfortunately 10 is the limit.

Thanks again.

Jim

Hi Stefan,

We got similar scenario as you gave mapping solution above. But this time its BAPI. For IDoc, I exported IDoc signature, changed max occurs to unbounded and in message mapping imported that IDoc as XSD, with which we are able to generate multiple IDoc's, when ever there is change in VBELN_VL in source.

Here in this scenario whenever there is change in TRANS_ID we have to make new BAPI call and get the response back.

How do we acheive the same? Can we do this by message mapping? or Do we need to use BPM?? Can I change the occurence here same as we did for IDoc?

Here are the source and target(Expecting like this):

<?xml version="1.0" encoding="utf-8"?>

<mt_PhysCountEnt>

<row>

<TRANS_ID>1847204</TRANS_ID>

<TRANS_RFC>ZAPI_MATPHYSINV_COUNT ('20060103','2005','000','1000006021') ITEMS, RETURN</TRANS_RFC>

<ENTRY_QNT>900</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</row>

<row>

<TRANS_ID>1847204</TRANS_ID>

<TRANS_RFC>ZAPI_MATPHYSINV_COUNT('20060103','2005','000','1000006021') ITEMS, RETURN</TRANS_RFC>

<ENTRY_QNT>600</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</row>

<row>

<TRANS_ID>1847204</TRANS_ID>

<TRANS_RFC>ZAPI_MATPHYSINV_COUNT('20060103','2005','000','1000006021') ITEMS, RETURN</TRANS_RFC>

<ENTRY_QNT>0</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT>X</ZERO_COUNT>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</row>

<row>

<TRANS_ID>1847204</TRANS_ID>

<TRANS_RFC>ZAPI_MATPHYSINV_COUNT('20060103','2005','000','1000006021') ITEMS, RETURN</TRANS_RFC>

<ENTRY_QNT>175</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</row>

<row>

<TRANS_ID>1847205</TRANS_ID>

<TRANS_RFC>ZAPI_MATPHYSINV_COUNT('20060103','2005','000','1000006023') ITEMS, RETURN</TRANS_RFC>

<ENTRY_QNT>120</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</row>

<row>

<TRANS_ID>1847205</TRANS_ID>

<TRANS_RFC>ZAPI_MATPHYSINV_COUNT('20060103','2005','000','1000006023') ITEMS, RETURN</TRANS_RFC>

<ENTRY_QNT>295</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</row>

</mt_PhysCountEnt>

TARGET:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:ZAPI_MATPHYSINV_COUNT xmlns:ns0="urn:sap-com:document:sap:rfc:functions">

<COUNT_DATE>20060103</COUNT_DATE>

<PERCENTAGE_VARIANCE>000</PERCENTAGE_VARIANCE>

<ITEMS>

<item>

<ENTRY_QNT>900</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</item>

<item>

<ENTRY_QNT>600</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</item>

<item>

<ENTRY_QNT>0</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT>X</ZERO_COUNT>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</item>

<item>

<ENTRY_QNT>175</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</item>

</ITEMS>

</ns0:ZAPI_MATPHYSINV_COUNT>

<ns0:ZAPI_MATPHYSINV_COUNT xmlns:ns0="urn:sap-com:document:sap:rfc:functions">

<COUNT_DATE>20060103</COUNT_DATE>

<PERCENTAGE_VARIANCE>000</PERCENTAGE_VARIANCE>

<ITEMS>

<item>

<ENTRY_QNT>120</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</item>

<item>

<ENTRY_QNT>295</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</item>

</ITEMS>

</ns0:ZAPI_MATPHYSINV_COUNT>

Thank You

Indrasena

Hi Indrasena,

as a BAPI is a synchonous call, you have to do it with BPM.

Define a multi mapping where you split your original requests to multiple BAPI requests. Call each BAPI and combine the results to a single response.

This is very complicated, so I recommend to consider to write a wrapper RFC with enhanced interface which calls the BAPIs as often as needed. A good ABAP developer will do this quicker than an XI expert can create the BPM

Regards

Stefan

Hi Stefan,

I defined multi-mapping to split original request to multiple BAPI requests. Output of multi-mapping is follows:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:Messages xmlns:ns0="http://sap.com/xi/XI/SplitAndMerge">

<ns0:Message1>

<mt_MessgeSplit>

<row>

<TRANS_ID>1847204</TRANS_ID>

<TRANS_RFC>ZAPI_MATPHYSINV_COUNT('20060103','2005','000','1000006021') ITEMS, RETURN</TRANS_RFC>

<ENTRY_QNT>900</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</row>

<row>

<TRANS_ID>1847204</TRANS_ID>

<TRANS_RFC>ZAPI_MATPHYSINV_COUNT('20060103','2005','000','1000006021') ITEMS, RETURN</TRANS_RFC>

<ENTRY_QNT>600</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</row>

<row>

<TRANS_ID>1847204</TRANS_ID>

<TRANS_RFC>ZAPI_MATPHYSINV_COUNT('20060103','2005','000','1000006021') ITEMS, RETURN</TRANS_RFC>

<ENTRY_QNT>0</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT>X</ZERO_COUNT>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</row>

<row>

<TRANS_ID>1847204</TRANS_ID>

<TRANS_RFC>ZAPI_MATPHYSINV_COUNT('20060103','2005','000','1000006021') ITEMS, RETURN</TRANS_RFC>

<ENTRY_QNT>175</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</row>

</mt_MessgeSplit>

<mt_MessgeSplit>

<row>

<TRANS_ID>1847205</TRANS_ID>

<TRANS_RFC>ZAPI_MATPHYSINV_COUNT('20060103','2005','000','1000006023') ITEMS, RETURN</TRANS_RFC>

<ENTRY_QNT>120</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</row>

<row>

<TRANS_ID>1847205</TRANS_ID>

<TRANS_RFC>ZAPI_MATPHYSINV_COUNT('20060103','2005','000','1000006023') ITEMS, RETURN</TRANS_RFC>

<ENTRY_QNT>295</ENTRY_QNT>

<ENTRY_UOM>EA</ENTRY_UOM>

<ENTRY_UOM_ISO/>

<SALES_VAL/>

<ZERO_COUNT/>

<MATERIAL_EXTERNAL/>

<MATERIAL_GUID/>

<MATERIAL_VERSION/>

</row>

</mt_MessgeSplit>

</ns0:Message1>

</ns0:Messages>

What are the steps I need to follow in BPM to send these messages as individual BAPI calls to SAP??

We are using warpper RFC to original BAPI_MATPHYSINV_COUNT.

Thank You

Indrasena

Hi Indrasena,

here are some useful weblogs:

synchronous calls in BPM:

/people/arpit.seth/blog/2005/06/27/rfc-scenario-using-bpm--starter-kit

sync - async - Brigde:

/people/sriram.vasudevan3/blog/2005/01/11/demonstrating-use-of-synchronous-asynchronous-bridge-to-integrate-synchronous-and-asynchronous-systems-using-ccbpm-in-sap-xi

sync - sync - Scenario:

https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/1403 [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken]

Multimapping:

/people/narendra.jain/blog/2005/12/30/various-multi-mappings-and-optimizing-their-implementation-in-integration-processes-bpm-in-xi

Regards

Stefan

Answers (2)

Stefan..I think there should be a XI mapping forum sepeartaely..:)

Seems that you can solve any mapping problem..:)

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Jim,

you could do this with SP13:


VBELN_VL - removeContexts - splitByValue(valueChanged) - collapseContexts - IDOC
constant(1) - BEGIN

constant() - E1VPDLH
constant(1) - SEGMENT(1)

VBELN_VL - removeContexts - splitByValue(valueChanged) - VBELN_VL(1)(*)

                                                 VBELN 
                                                        formatByExample - VBELN(1)(*)
VBELN_VL - removeContexts - splitByValue(valueChanged) /

the other header fields similar

(*) if the occurrancy of the target field is not 0:1 or 1:1, then you have to add collapseContexts - splitByValue(eachValue) before the target.


VBELN_VL - removeContexts - splitByValue(valueChanged) - E1VPDLI
VBELN_VL - removeContexts - splitByValue(valueChanged) - index - splitByValue(eachValue) - SEGMENT(2)

VBELN_VL - VBELN_VL(2)

the other position fields equal

Hope that helps

Stefan

Message was edited by: Stefan Grube

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Ask a Question

Top Q&A Solution Author

User

Count

JDBC mapping to IDOC issue

Accepted Solutions (1)

Accepted Solutions (1)

Answers (2)

Answers (2)

How to use CopyProvider on Table created by TypeSc...

How to execute/fire onPost when user press enter o...

Re: Connecting SAP CAP with SAP Cloud ALM

Re: How can assign in Identity Authentication Serv...

Re: Are there plans to update the Spring framework...