Mapping from flat sql message to nested idoc

[Former Member]

Hi,

im trying to map the values from a database select to an idoc. the structure is like that:

?xml version="1.0" encoding="UTF-8"?>
<ns0:Messages xmlns:ns0="">
   <ns0:Message1>
      <ns1:MT_SQL_Invoice_Req_response xmlns:ns1="">
         <SELECT_STATEMENT_response>
            <row>
               <ID>
               <TYPE>
               <VALUE>
            </row>
            <row>
               <ID>
               <TYPE>
               <VALUE>
            </row>
         </SELECT_STATEMENT_response>
      </ns1:MT_SQL_Invoice_Req_response>
   </ns0:Message1>
</ns0:Messages>

So I'm getting several rows in one message. For every <ID> i want to create a new Idoc. This problem i solved with a sort and split function. (BPE) Now i want to check if the <TYPE> has a specific value "POS" If that value is in the row i want to create a new row for an Item in the idoc. If the <TYPE> is "TAX" i want a new row in the idoc in position/tax. I'm quite new to SAP PI so I am sorry that i can't describe my problem better. Does anybody have an idea how i can solve that problem?

Thank you in advance for your advices

[Former Member · Accepted solution]

Does your question is if <type>="POS" then generate line item segment else if <type>="TAX" then generate Position/Tax segment

If yes then use the below condition to generate

1. Line Item (use ifWithoutElse function)

if <type>="POS" then 'true' -

---

createIf -

---

Line Item segment

2. Position/Tax

if <type>="TAX" then 'true' -

---

createIf -

---

position/Tax segment

[Former Member · Accepted solution]

Thank you for your help, but still can't get it working

My target message should have a structure like that:

<?xml version="1.0" encoding="UTF-8"?>
<ns0:Messages xmlns:ns0="">
<ns0:Message1><ZBILLING>
<IDOC BEGIN="Constant">
	<EDI_DC40 
	</EDI_DC40>
		<E1FKK_EBS_TOI_HEADER SEGMENT="1">
			<E1FKK_EBS_TOI_ITEMS>		
				 <E1FKK_EBS_TOI_TAX SEGMENT="Constant">
                     <TAX_BASE>Constant</TAX_BASE>
                 </E1FKK_EBS_TOI_TAX>
			</E1FKK_EBS_TOI_ITEMS>
			<E1FKK_EBS_TOI_ITEMS>
				<E1FKK_EBS_TOI_TAX SEGMENT="Constant">
                     <TAX_BASE>Constant</TAX_BASE>
                </E1FKK_EBS_TOI_TAX>
			</E1FKK_EBS_TOI_ITEMS>		
		</E1FKK_EBS_TOI_HEADER>
</IDOC>
<IDOC BEGIN="Constant">
	<EDI_DC40
	</EDI_DC40>
		<E1FKK_EBS_TOI_HEADER SEGMENT="2">
			<E1FKK_EBS_TOI_ITEMS>
				<E1FKK_EBS_TOI_TAX SEGMENT="Constant">
                     <TAX_BASE>Constant</TAX_BASE>
                </E1FKK_EBS_TOI_TAX>
			</E1FKK_EBS_TOI_ITEMS>
		</E1FKK_EBS_TOI_HEADER>
</IDOC>
<IDOC BEGIN="Constant">
	<EDI_DC40 
	</EDI_DC40>
		<E1FKK_EBS_TOI_HEADER SEGMENT="3">
			<E1FKK_EBS_TOI_ITEMS>
				<E1FKK_EBS_TOI_TAX SEGMENT="Constant">
                     <TAX_BASE>Constant</TAX_BASE>
                </E1FKK_EBS_TOI_TAX>
			</E1FKK_EBS_TOI_ITEMS>
		</E1FKK_EBS_TOI_HEADER>
</IDOC><
/ZBILLING>
</ns0:Message1>
</ns0:Messages>

What I've done right now is

1.

<ID> --> sort > splitByValue(valuechanged) - > collapseContext> <IDOC>

I will get an <IDOC> for every ID

2.

For <E1FKK_EBS_TOI_HEADER> i use a constant

3.

for <E1FKK_EBS_TOI_ITEMS> i do

<ART> equals CONSTANT "POS" --> ifWithoutElse then <Art> --> <E1FKK_EBS_TOI_ITEMS>

with that mappings i will get an Idoc for every ID but the <E1FKK_EBS_TOI_ITEMS> are all created in the first <IDOC> the others are empty. I tried to use constant'true' in case "then" of the ifWithoutElse but here I need a bool. And what about the contexts, I'm not sure if use them in the right way?

Thank you for any idea

[Harish · Accepted solution]

Hi Stefen,

you are using Sort funtion for creating the IDOC so put sortbykey function for item field. Then field will come in right context as IDOC.

regards,

Harish

[Former Member · Accepted solution]

Hi Harish,

can u explain that a bit more detailed where to put the sortBy function in the item mapping. I've tried it in several positions, but I either get all the items "rows" in the first or only one item in the last idoc. Is it because i switch the context from "MT_SQL_Invoice_Req" to "Message1" forthe <id> before i do my mapping described above for the <idoc> If don't do that i will get too many idocs.

[Harish · Accepted solution]

Hi Stefen,

you are getting the row in the first IDOC because you are having all the values in one context. if you going to have on item per idoc then you can use splitbyvalue node function.

Regards,

Harish

[Former Member · Accepted solution]

I don't know how many position items i will get. What i need is a new idoc for every <id>. Within that idoc i need to map the header if the the row type equals "head" i hav to map it to the to the idoc header for the specific id. These two problems I already solved.

The problem is, i don't know how many rows for a specific id i will get with the type "pos". Thats's because the id is a "bill id" and every bill can have many positions. I have to map these rows in dependency to the id to the right idoc. But I still didn't solve it. Any help or idea would be great.

[Former Member · Accepted solution]

for <E1FKK_EBS_TOI_ITEMS> i do

<ART> equals CONSTANT "POS" --> ifWithoutElse then <Art> --> <E1FKK_EBS_TOI_ITEMS>

After the Art field, use SortBtkey with the ID field. then have the mapping you have described above. Also, keep the context of ART and ID at the root level and after the ifWithoutElse use a splitByValue function.

[Former Member · Accepted solution]

Hi Shiladitya

thank your the the advice but it unfortunately doesn't work.

I made some screenshots, to show you what I did and what results i get

1.Idoc Mapping

http://www.pictureupload.de/originals/pictures/190711165008_idoc.JPG

2.Header Mapping

http://www.pictureupload.de/originals/pictures/190711165320_Header.JPG

3.Item Mapping

http://www.pictureupload.de/originals/pictures/190711165359_items.JPG

4. Test instance

http://www.pictureupload.de/originals/pictures/190711165445_Test.JPG

<MTInvoice> are the rows from the jdbc sql response

The mapping for the <idoc> and the <header> works fine but for the <item> level i get only one result in the second idoc(<id>4712)

Actually i should get one item in the first idoc, two items in the second and one in the third.

[Former Member]

Hi Stefan,

try to avoid ccBPM/BPE, it´s usually a performance problem. You can use the link mentioned before in order to generate several IDOCs without BPM. Regarding your problem at position level, you can use the if function to filter based on the TYPE field, you propably will have to apply the same filter for the VALUE field.

Regards,

Matthias

[Former Member]

Sorry i told you wrong, i want to to do it with a message mapping not with BPE

[baskar_gopalakrishnan2]

Just Few cents...

For every ID you need to create a new idoc rite? If so use this [link|https://wiki.sdn.sap.com/wiki/display/XI/File%20to%20Multiple%20IDOC%20Splitting%20without%20BPM]

This way you can create for every row a seperate idoc.