PI Mapping problem - Repeating target nodes

[Former Member]

Hello PI buddies!

My PI mapping problem is as follows:

I have a source IDoc which I wish to map to a JDBC Message Type.

In the Idoc I have 2 repeating sections that I wish to combine to create a new node in the target for each combination of the 2 repeating sections. The source and desired target message structures are pasted below. In summary for every combination of "sales_org" and "relationship" I want to create an "Access" node. In my example there are 2 "sales_org" and 5 "relationship". I would therefore like to generate 10 "Access" nodes.

I want to achieve this if possible using PI mapping and its standard functions. Please help!

SOURCE MESSAGE TYPE:

<?xml version="1.0" encoding="UTF-8" ?>
<Z_CRMXIF_PARTNER_SAVE_M01>
 <IDOC BEGIN="1">
  <EDI_DC40 SEGMENT="1">
   <TABNAM>EDI_DC40</TABNAM>
  </EDI_DC40>
  <E101CRMXIF_PARTNER_COMPLEX SEGMENT="1">
   <APPL_SNAME>CRMXIF_PARTNER_COMPLEX</APPL_SNAME>
   <E101CRMXIF_PARTNER_HEADER SEGMENT="1">
   </E101CRMXIF_PARTNER_HEADER>
   <E101BUS_EI_CENTRAL_DATA SEGMENT="1">
    <E101BUS_EI_ROLES SEGMENT="1">
     <APPL_SNAME>BUS_EI_ROLES</APPL_SNAME>
     <CURRENT_STATE>X</CURRENT_STATE>
     <E101BUS_EI_BUPA_ROLES SEGMENT="1">
      <APPL_SNAME>BUS_EI_BUPA_ROLES</APPL_SNAME>
      <DATA_KEY>ZBUP02</DATA_KEY>
     </E101BUS_EI_BUPA_ROLES>
     <E101BUS_EI_BUPA_ROLES SEGMENT="1">
      <APPL_SNAME>BUS_EI_BUPA_ROLES</APPL_SNAME>
      <DATA_KEY>ZCRM01</DATA_KEY>
     </E101BUS_EI_BUPA_ROLES>
     <E101BUS_EI_BUPA_ROLES SEGMENT="1">
      <APPL_SNAME>BUS_EI_BUPA_ROLES</APPL_SNAME>
      <DATA_KEY>ZCRM02</DATA_KEY>
     </E101BUS_EI_BUPA_ROLES>
     <E101BUS_EI_BUPA_ROLES SEGMENT="1">
      <APPL_SNAME>BUS_EI_BUPA_ROLES</APPL_SNAME>
      <DATA_KEY>ZCRM03</DATA_KEY>
     </E101BUS_EI_BUPA_ROLES>
     <E101BUS_EI_BUPA_ROLES SEGMENT="1">
      <APPL_SNAME>BUS_EI_BUPA_ROLES</APPL_SNAME>
      <DATA_KEY>ZCRM04</DATA_KEY>
     </E101BUS_EI_BUPA_ROLES>
    </E101BUS_EI_ROLES>
   </E101BUS_EI_CENTRAL_DATA>
   <E101CRMT_BUS_EI_DATA SEGMENT="1">
    <Z101RMT_BUS_EI_R3_SALESAREA SEGMENT="1">
     <APPL_SNAME>ZCRMT_BUS_EI_R3_SALESAREA</APPL_SNAME>
     <Z101ZCRMT_BUS_R3_SALES_AREA SEGMENT="1">
      <APPL_SNAME>ZCRMT_BUS_R3_SALES_AREA</APPL_SNAME>
      <ZZSALES_ORG>1000</ZZSALES_ORG>
      <ZZCHANNEL>01</ZZCHANNEL>
      <ZZDIVISION>00</ZZDIVISION>
     </Z101ZCRMT_BUS_R3_SALES_AREA>
     <Z101ZCRMT_BUS_R3_SALES_AREA SEGMENT="1">
      <APPL_SNAME>ZCRMT_BUS_R3_SALES_AREA</APPL_SNAME>
      <ZZSALES_ORG>2000</ZZSALES_ORG>
      <ZZCHANNEL>01</ZZCHANNEL>
      <ZZDIVISION>00</ZZDIVISION>
     </Z101ZCRMT_BUS_R3_SALES_AREA>
    </Z101RMT_BUS_EI_R3_SALESAREA>
   </E101CRMT_BUS_EI_DATA>
  </E101CRMXIF_PARTNER_COMPLEX>
 </IDOC>
</Z_CRMXIF_PARTNER_SAVE_M01>

DESIRED TARGET MESSAGE TYPE:

<?xml version="1.0" encoding="UTF-8"?>
<ns0:MT_CTX001_JDBC_In xmlns:ns0="http://mycompany.co.uk/pi/CTXIN001">
 <SQLStatement1>
  <dbTable Action="INSERT">
   <Table>customer_relationship</Table>
   <Access>
    <sales_org>1000</sales_org>
    <dist_channel>01</dist_channel>
    <division>00</division>
    <relationship>ZBUP02</relationship>
   </Access>
   <Access>
    <sales_org>2000</sales_org>
    <dist_channel>01</dist_channel>
    <division>00</division>
    <relationship>ZBUP02</relationship>
   </Access>
   <Access>
    <sales_org>1000</sales_org>
    <dist_channel>01</dist_channel>
    <division>00</division>
    <relationship>ZCRM01</relationship>
   </Access>
   <Access>
    <sales_org>2000</sales_org>
    <dist_channel>01</dist_channel>
    <division>00</division>
    <relationship>ZCRM01</relationship>
   </Access>
   <Access>
    <sales_org>1000</sales_org>
    <dist_channel>01</dist_channel>
    <division>00</division>
    <relationship>ZCRM02</relationship>
   </Access>
   <Access>
    <sales_org>2000</sales_org>
    <dist_channel>01</dist_channel>
    <division>00</division>
    <relationship>ZCRM02</relationship>
   </Access>
   <Access>
    <sales_org>1000</sales_org>
    <dist_channel>01</dist_channel>
    <division>00</division>
    <relationship>ZCRM03</relationship>
   </Access>
   <Access>
    <sales_org>2000</sales_org>
    <dist_channel>01</dist_channel>
    <division>00</division>
    <relationship>ZCRM03</relationship>
   </Access>
   <Access>
    <sales_org>1000</sales_org>
    <dist_channel>01</dist_channel>
    <division>00</division>
    <relationship>ZCRM04</relationship>
   </Access>
   <Access>
    <sales_org>2000</sales_org>
    <dist_channel>01</dist_channel>
    <division>00</division>
    <relationship>ZCRM04</relationship>
   </Access>
  </dbTable>
 </SQLStatement1>
</ns0:MT_CTX001_JDBC_In>

Edited by: Jim Kendall on Apr 22, 2010 12:48 PM

Edited by: Jim Kendall on Apr 22, 2010 12:50 PM

[stefan_grube · Accepted solution]

The node function "useOneAsMany" will help you.

As all 4 target nodes are a little bit different, you should play a little bit with the function to find the correct use of it.

[Former Member · Accepted solution]

SOURCE MESSAGE TYPE:

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

<Z_CRMXIF_PARTNER_SAVE_M01>

<IDOC BEGIN="1">

<EDI_DC40 SEGMENT="1">

<TABNAM>EDI_DC40</TABNAM>

</EDI_DC40>

<E101CRMXIF_PARTNER_COMPLEX SEGMENT="1">

<APPL_SNAME>CRMXIF_PARTNER_COMPLEX</APPL_SNAME>

<E101CRMXIF_PARTNER_HEADER SEGMENT="1">

</E101CRMXIF_PARTNER_HEADER>

<E101BUS_EI_CENTRAL_DATA SEGMENT="1">

<E101BUS_EI_ROLES SEGMENT="1">

<APPL_SNAME>BUS_EI_ROLES</APPL_SNAME>

<CURRENT_STATE>X</CURRENT_STATE>

<E101BUS_EI_BUPA_ROLES SEGMENT="1">

<APPL_SNAME>BUS_EI_BUPA_ROLES</APPL_SNAME>

<DATA_KEY>ZBUP02</DATA_KEY>

</E101BUS_EI_BUPA_ROLES>

<E101BUS_EI_BUPA_ROLES SEGMENT="1">

<APPL_SNAME>BUS_EI_BUPA_ROLES</APPL_SNAME>

<DATA_KEY>ZCRM01</DATA_KEY>

</E101BUS_EI_BUPA_ROLES>

<E101BUS_EI_BUPA_ROLES SEGMENT="1">

<APPL_SNAME>BUS_EI_BUPA_ROLES</APPL_SNAME>

<DATA_KEY>ZCRM02</DATA_KEY>

</E101BUS_EI_BUPA_ROLES>

<E101BUS_EI_BUPA_ROLES SEGMENT="1">

<APPL_SNAME>BUS_EI_BUPA_ROLES</APPL_SNAME>

<DATA_KEY>ZCRM03</DATA_KEY>

</E101BUS_EI_BUPA_ROLES>

<E101BUS_EI_BUPA_ROLES SEGMENT="1">

<APPL_SNAME>BUS_EI_BUPA_ROLES</APPL_SNAME>

<DATA_KEY>ZCRM04</DATA_KEY>

</E101BUS_EI_BUPA_ROLES>

</E101BUS_EI_ROLES>

</E101BUS_EI_CENTRAL_DATA>

<E101CRMT_BUS_EI_DATA SEGMENT="1">

<Z101RMT_BUS_EI_R3_SALESAREA SEGMENT="1">

<APPL_SNAME>ZCRMT_BUS_EI_R3_SALESAREA</APPL_SNAME>

<Z101ZCRMT_BUS_R3_SALES_AREA SEGMENT="1">

<APPL_SNAME>ZCRMT_BUS_R3_SALES_AREA</APPL_SNAME>

<ZZSALES_ORG>1000</ZZSALES_ORG>

<ZZCHANNEL>01</ZZCHANNEL>

<ZZDIVISION>00</ZZDIVISION>

</Z101ZCRMT_BUS_R3_SALES_AREA>

<Z101ZCRMT_BUS_R3_SALES_AREA SEGMENT="1">

<APPL_SNAME>ZCRMT_BUS_R3_SALES_AREA</APPL_SNAME>

<ZZSALES_ORG>2000</ZZSALES_ORG>

<ZZCHANNEL>01</ZZCHANNEL>

<ZZDIVISION>00</ZZDIVISION>

</Z101ZCRMT_BUS_R3_SALES_AREA>

</Z101RMT_BUS_EI_R3_SALESAREA>

</E101CRMT_BUS_EI_DATA>

</E101CRMXIF_PARTNER_COMPLEX>

</IDOC>

</Z_CRMXIF_PARTNER_SAVE_M01>

DESIRED TARGET MESSAGE TYPE:

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

<ns0:MT_CTX001_JDBC_In xmlns:ns0="http://mycompany.co.uk/pi/CTXIN001">

<SQLStatement1>

<dbTable Action="INSERT">

<Table>customer_relationship</Table>

<Access>

<sales_org>1000</sales_org>

<dist_channel>01</dist_channel>

<division>00</division>

<relationship>ZBUP02</relationship>

</Access>

<Access>

<sales_org>2000</sales_org>

<dist_channel>01</dist_channel>

<division>00</division>

<relationship>ZBUP02</relationship>

</Access>

<Access>

<sales_org>1000</sales_org>

<dist_channel>01</dist_channel>

<division>00</division>

<relationship>ZCRM01</relationship>

</Access>

<Access>

<sales_org>2000</sales_org>

<dist_channel>01</dist_channel>

<division>00</division>

<relationship>ZCRM01</relationship>

</Access>

<Access>

<sales_org>1000</sales_org>

<dist_channel>01</dist_channel>

<division>00</division>

<relationship>ZCRM02</relationship>

</Access>

<Access>

<sales_org>2000</sales_org>

<dist_channel>01</dist_channel>

<division>00</division>

<relationship>ZCRM02</relationship>

</Access>

<Access>

<sales_org>1000</sales_org>

<dist_channel>01</dist_channel>

<division>00</division>

<relationship>ZCRM03</relationship>

</Access>

<Access>

<sales_org>2000</sales_org>

<dist_channel>01</dist_channel>

<division>00</division>

<relationship>ZCRM03</relationship>

</Access>

<Access>

<sales_org>1000</sales_org>

<dist_channel>01</dist_channel>

<division>00</division>

<relationship>ZCRM04</relationship>

</Access>

<Access>

<sales_org>2000</sales_org>

<dist_channel>01</dist_channel>

<division>00</division>

<relationship>ZCRM04</relationship>

</Access>

</dbTable>

</SQLStatement1>

</ns0:MT_CTX001_JDBC_In>

[stefan_grube · Accepted solution]

please use this forum:

[Former Member · Accepted solution]

Thanks for the link to the test forum Stefan. I'm a newbie on the forum and am having difficulty formatting messages to be more readable. </br></br>

Thanks also for the tip re: useOneAsMany function, I had been trying that but have been running into difficulties with the fact that the 1st parameter (ie ZZSALES_ORG) occurs more than once in the IDoc and therefore a runtime exception of "Too Many values in the first queue" is raised.

[stefan_grube · Accepted solution]

> I'm a newbie on the forum and am having difficulty formatting messages to be more readable.

You are not the only one who has difficulties

> Thanks also for the tip re: useOneAsMany function, I had been trying that but have been running into difficulties with the fact that the 1st parameter (ie ZZSALES_ORG) occurs more than once in the IDoc and therefore a runtime exception of "Too Many values in the first queue" is raised.

Sorry, useOneAsMany will not work here.

You have to use a UDF as Nayan already has proposed.

[Former Member · Accepted solution]

Ah, I was afraid it was going to involve an UDF as my Java skills are weak. <br>

Any help with regard to the content of the UDF I require will be much appreciated... a code sample will be greatly appreciated.

[stefan_grube · Accepted solution]

You need two different UDFs, both of tpye "all values of a context" with two import parameters var1 and var2:

copyFirstValue

for(int i=0;i<var1.length;i++)
  for (int k=0;k<var2.length;k++)
    result.addValue(var1<i>);

copySecondValue

for(int i=0;i<var1.length;i++)
  for (int k=0;k<var2.length;k++)
    result.addValue(var2[k]);

You see the difference is: first UDF takes the output values from first parameter, the outer loop,

the second UDF from inner loop.

now you assign:

DATA_KEY - removeContexts       \
ZZSALES_ORG - removeContexts - copyFirstValue - Access

DATA_KEY - removeContexts       \
ZZSALES_ORG - removeContexts - copySecondValue - splitByValue - sales_org

DATA_KEY - removeContexts       \
ZZDIST_CHANNEL - removeContexts  - copySecondValue - splitByValue - dist_channel

DATA_KEY - removeContexts       \
ZZDIVISION - removeContexts  - copySecondValue - splitByValue - division

DATA_KEY - removeContexts       \
ZZSALES_ORG - removeContexts - copyFirstValue - splitByValue  - relationship

[Former Member · Accepted solution]

Fantastic! A perfect solution. Many thanks Stefan.

[Former Member · Accepted solution]

excellent solution, thank you very much Stefan

[Former Member]

one easy solution will be to create one UDF.

Logic of UDF is as follows:

1)Get two node values as input to the UDF.

2) Get count of the values present in each input queue.

3) multiply 2 counts.Make a loop upto that multiply value

4) Add context as per ur requirements in the relt queue.