cancel
Showing results for 
Search instead for 
Did you mean: 

SAP CPI - Merge two Line Item to Single Row

sachin_yadav3
Active Participant
0 Kudos

Hi All,

From source payload I am getting two line item i.e. one as main Item and another as Tax for each item. My requirement is to merge the Item with tax and produce single row in target using message mapping.

Input Payload :

<LineItems>
	<item>
		<AdjustedCostInERPPrecision>
			<Amount>100.00000</Amount>
		</AdjustedCostInERPPrecision>
		<NumberInCollection>1</NumberInCollection>
		<custom>
			<CustomString name="LineType">Item</CustomString>
			<CustomString name="UnitofMeasure">EA</CustomString>
		</custom>
	</item>
	<item>
		<AdjustedCostInERPPrecision>
			<Amount>10.00000</Amount>
		</AdjustedCostInERPPrecision>
		<NumberInCollection>2</NumberInCollection>
		<Parent>
			<NumberInCollection>1</NumberInCollection>
		</Parent>
		<custom>
			<CustomString name="LineType">Tax</CustomString>
			<CustomString name="UnitofMeasure">EA</CustomString>
		</custom>
	</item>
</LineItems>


Output :

<LineItems>
	<item>
		<AdjustedCostInERPPrecision>
			<Amount>100.00000</Amount>
		</AdjustedCostInERPPrecision>
		<NumberInCollection>1</NumberInCollection>
		<TaxAmount>10</TaxAmount>
		<custom>
			<CustomString name="LineType">Item</CustomString>
			<CustomString name="UnitofMeasure">EA</CustomString>
		</custom>
	</item>
</LineItems>

We can get multiple line item in source payload and each item will have two line one as item and other as Tax.

Would appreciate suggestion to achieve this functionality to message mapping ?

MortenWittrock
Active Contributor
0 Kudos

Hi

It would probably be good to have at least two pairs of line items in your sample input and output XML documents.

Also: Why must this be done in Message Mapping, specifically?

Regards,

Morten

Willem_Pardaens
Product and Topic Expert
Product and Topic Expert
0 Kudos

I suggest to read through this blog to see if you can use any of the mentioned concepts (eg. Convert Tree Structure to Flat Structure): https://blogs.sap.com/2019/11/15/sap-cloud-platform-integration-cpi-use-cases-of-node-functions-in-m...

sachin_yadav3
Active Participant
0 Kudos

7a519509aed84a2c9e6f627841825b5a : Thanks , I did added two line item initially but thought it will be too long. Please find below the example with two line item.

I thought message mapping might be easier to achieve this functionality so that's the reason I was inclined towards message mapping.

Source Payload :

<LineItems>
	<item>
		<AdjustedCostInERPPrecision>
			<Amount>100.00000</Amount>
		</AdjustedCostInERPPrecision>
		<NumberInCollection>1</NumberInCollection>
		<custom>
			<CustomString name="LineType">Item</CustomString>
			<CustomString name="UnitofMeasure">EA</CustomString>
		</custom>
	</item>
	<item>
		<AdjustedCostInERPPrecision>
			<Amount>10.00000</Amount>
		</AdjustedCostInERPPrecision>
		<NumberInCollection>2</NumberInCollection>
		<Parent>
			<NumberInCollection>1</NumberInCollection>
		</Parent>
		<custom>
			<CustomString name="LineType">Tax</CustomString>
			<CustomString name="UnitofMeasure">EA</CustomString>
		</custom>
	</item>
	<item>
		<AdjustedCostInERPPrecision>
			<Amount>200.00000</Amount>
		</AdjustedCostInERPPrecision>
		<NumberInCollection>3</NumberInCollection>
		<custom>
			<CustomString name="LineType">Item</CustomString>
			<CustomString name="UnitofMeasure">EA</CustomString>
		</custom>
	</item>
	<item>
		<AdjustedCostInERPPrecision>
			<Amount>22.00000</Amount>
		</AdjustedCostInERPPrecision>
		<NumberInCollection>4</NumberInCollection>
		<Parent>
			<NumberInCollection>3</NumberInCollection>
		</Parent>
		<custom>
			<CustomString name="LineType">Tax</CustomString>
			<CustomString name="UnitofMeasure">EA</CustomString>
		</custom>
	</item>
</LineItems>

Target Payload :

<LineItems>
	<item>
		<AdjustedCostInERPPrecision>
			<Amount>100.00000</Amount>
		</AdjustedCostInERPPrecision>
		<NumberInCollection>1</NumberInCollection>
		<TaxAmount>10</TaxAmount>
		<custom>
			<CustomString name="LineType">Item</CustomString>
			<CustomString name="UnitofMeasure">EA</CustomString>
		</custom>
	</item>
	<item>
		<AdjustedCostInERPPrecision>
			<Amount>200.00000</Amount>
		</AdjustedCostInERPPrecision>
		<NumberInCollection>2</NumberInCollection>
		<TaxAmount>22</TaxAmount>
		<custom>
			<CustomString name="LineType">Item</CustomString>
			<CustomString name="UnitofMeasure">EA</CustomString>
		</custom>
	</item>
</LineItems>
sachin_yadav3
Active Participant
0 Kudos

willem.pardaens : Thanks for providing the SAP blog unfortunately I don't think any of the use case mentioned in the blog fullfil my requirement.

MortenWittrock
Active Contributor

Hi

In your second sample output, shouldn't the NumberInCollection value of the second item be 3? Or are they supposed to be numbered 1, 2, 3 etc.?

As to whether Message Mapping is a good fit for this problem, I don't really think so. It's doable, but solving this would be a lot easier using either scripting or XSLT.

I've written some more about the mapping options here.

Regards,

Morten

sachin_yadav3
Active Participant
0 Kudos

7a519509aed84a2c9e6f627841825b5a : Thanks for your response. Second output sample, NumberInCollection value is sequential i.e. 1, 2 ,3 etc because it indicate the line item number in the target system.

Thanks for recommendation, I am not very good with XSLT however will try to build the logic as per help url provided by you.

Any further help would be highly appreciated.

Thanks & Regards

Sachin Yadav

Accepted Solutions (0)

Answers (1)

Answers (1)

MortenWittrock
Active Contributor

Hi Sachin

Here is a stylesheet that performs this transformation for you:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="3.0">

<xsl:mode on-no-match="shallow-copy"/>

<xsl:output method="xml" indent="yes"/>

<xsl:strip-space elements="LineItems"/>

<!-- Remove the child item elements -->
<xsl:template match="item[Parent]"/>

<!-- This template handles the NumberInCollection element -->
<xsl:template match="item[not(Parent)]/NumberInCollection">
<xsl:variable name="parent" select="string(.)"/>
<!-- Convert from 1, 3, 5, ... to 1, 2, 3, ... -->
<xsl:variable name="num" select="(number(.) + 1) div 2"/>
<!-- Update the contents of the NumberInCollection element -->
<xsl:copy select=".">
<xsl:value-of select="$num"/>
</xsl:copy>
<!-- Add the new TaxAmount element -->
<TaxAmount>
<!-- Look up the tax amount in the child item element -->
<xsl:value-of select="number(/LineItems/item[Parent/NumberInCollection = $parent]/AdjustedCostInERPPrecision/Amount)"/>
</TaxAmount>
</xsl:template>

</xsl:stylesheet>

If you are new to XSLT, there is quite a bit to take in here. I've added some comments to the stylesheet, and I'd suggest that you take some time to read and understand it. It's generally a bad idea to add code to your solution, that you do not understand.

Have fun with Cloud Integration!

Regards,

Morten