locked
IDOC to XML Mapping RRS feed

  • Question

  • Hi,

    We have a requirement to sum up the total amount by credit card

    i.e.. there will be multiple credit cards, and for each credit card there will be multiple entries,

    so i need to sum up AMOUNT for each CREDITCARD and map it to TOTALAMOUNT in Destination.

    Please tell me the best way to do this?

    Thanks,

    Sarva.

    Wednesday, February 11, 2015 6:55 AM

Answers

  • You can use custom XSLT and the Muenchian Grouping for this.

    In a simplified example where input is:

    <Z1PAYHDR>
      <Z1PAYDTL>
        <AMOUNT>100</AMOUNT>
        <CREDIT_CARD>VISA</CREDIT_CARD>
      </Z1PAYDTL>
      <Z1PAYDTL>
        <AMOUNT>1000</AMOUNT>
        <CREDIT_CARD>VISA</CREDIT_CARD>
      </Z1PAYDTL>
      <Z1PAYDTL>
        <AMOUNT>10</AMOUNT>
        <CREDIT_CARD>MASTER</CREDIT_CARD>
      </Z1PAYDTL>
      <Z1PAYDTL>
        <AMOUNT>10</AMOUNT>
        <CREDIT_CARD>VISA</CREDIT_CARD>
      </Z1PAYDTL>
      <Z1PAYDTL>
        <AMOUNT>40</AMOUNT>
        <CREDIT_CARD>MASTER</CREDIT_CARD>
      </Z1PAYDTL>
    </Z1PAYHDR>

    And the XSLT is:

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl xsl" version="1.0">
      <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
      <xsl:key name="DetailCC" match="Z1PAYDTL" use="CREDIT_CARD" />
      <xsl:template match="Z1PAYHDR">
        <Output>
          <xsl:for-each select="Z1PAYDTL[count(. | key('DetailCC', CREDIT_CARD)[1]) = 1]">
            <xsl:variable name="card" select="CREDIT_CARD" />
            <CC>
              <Card>
                <xsl:value-of select="$card" />
              </Card>
              <TotalAmount>
                <xsl:value-of select="sum(//Z1PAYDTL[CREDIT_CARD = $card]/AMOUNT)" />
              </TotalAmount>
            </CC>
          </xsl:for-each>
        </Output>
      </xsl:template>
    </xsl:stylesheet>

    You will get the folllowing output:

    <Output>
      <CC>
        <Card>VISA</Card>
        <TotalAmount>1110</TotalAmount>
      </CC>
      <CC>
        <Card>MASTER</Card>
        <TotalAmount>50</TotalAmount>
      </CC>
    </Output>

    Morten la Cour

    • Marked as answer by SarvaSetty Wednesday, February 11, 2015 3:08 PM
    Wednesday, February 11, 2015 11:25 AM
  • Hi Sarva,

    You can use Cumulative Sum functoid with second parameter as "1".

    I have created one sample xsd from the sample u provided. Used below instance for testing:

    <ns0:ZPAYMENT_EXTRACT xmlns:ns0="http://sample_dynamicXSLT.input_idocSchema">
      <IDOC>
        <BEGIN>BEGIN_0</BEGIN>
        <EDI_DC40>
          <field1>field1_0</field1>
          <field2>field2_0</field2>
        </EDI_DC40>
        <Z1PAYHDR>
          <SEGMENT>Girish</SEGMENT>
          <ACCOUNT>111</ACCOUNT>
          <XMIT_DATE>XMIT_DATE_0</XMIT_DATE>
          <Z1PAYDTL>
            <PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
            <AMOUNT>100</AMOUNT>
            <CREDIT_CARD>ABC</CREDIT_CARD>
          </Z1PAYDTL>
     <Z1PAYDTL>
            <PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
            <AMOUNT>200</AMOUNT>
            <CREDIT_CARD>PQR</CREDIT_CARD>
          </Z1PAYDTL>
     <Z1PAYDTL>
            <PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
            <AMOUNT>300</AMOUNT>
            <CREDIT_CARD>XYZ</CREDIT_CARD>
          </Z1PAYDTL>
        </Z1PAYHDR>
    <Z1PAYHDR>
          <SEGMENT>Naveen</SEGMENT>
          <ACCOUNT>222</ACCOUNT>
          <XMIT_DATE>XMIT_DATE_0</XMIT_DATE>
          <Z1PAYDTL>
            <PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
            <AMOUNT>40</AMOUNT>
            <CREDIT_CARD>ABC</CREDIT_CARD>
          </Z1PAYDTL>
     <Z1PAYDTL>
            <PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
            <AMOUNT>80</AMOUNT>
            <CREDIT_CARD>PQR</CREDIT_CARD>
          </Z1PAYDTL>
        </Z1PAYHDR>
      </IDOC>
    </ns0:ZPAYMENT_EXTRACT>

    Got the output as:

    <ns0:ZPAYMENT_OUT xmlns:ns0="http://sample_dynamicXSLT.output_idocSchema">
    <CUSTOMER>
    <SEGMENT>Girish</SEGMENT>
    <ACCOUNT>111</ACCOUNT>
    <TOTALAMOUNT>600</TOTALAMOUNT>
    </CUSTOMER>
    <CUSTOMER>
    <SEGMENT>Naveen</SEGMENT>
    <ACCOUNT>222</ACCOUNT>
    <TOTALAMOUNT>120</TOTALAMOUNT>
    </CUSTOMER>
    </ns0:ZPAYMENT_OUT>


    Thanks, Girish R. Patil.

    • Marked as answer by SarvaSetty Wednesday, February 11, 2015 3:08 PM
    Wednesday, February 11, 2015 11:34 AM

All replies

  • Hi Sarva,

    Can you please provide us sample input and expected output?


    Thanks, Girish R. Patil.

    Wednesday, February 11, 2015 9:45 AM
  • You can use custom XSLT and the Muenchian Grouping for this.

    In a simplified example where input is:

    <Z1PAYHDR>
      <Z1PAYDTL>
        <AMOUNT>100</AMOUNT>
        <CREDIT_CARD>VISA</CREDIT_CARD>
      </Z1PAYDTL>
      <Z1PAYDTL>
        <AMOUNT>1000</AMOUNT>
        <CREDIT_CARD>VISA</CREDIT_CARD>
      </Z1PAYDTL>
      <Z1PAYDTL>
        <AMOUNT>10</AMOUNT>
        <CREDIT_CARD>MASTER</CREDIT_CARD>
      </Z1PAYDTL>
      <Z1PAYDTL>
        <AMOUNT>10</AMOUNT>
        <CREDIT_CARD>VISA</CREDIT_CARD>
      </Z1PAYDTL>
      <Z1PAYDTL>
        <AMOUNT>40</AMOUNT>
        <CREDIT_CARD>MASTER</CREDIT_CARD>
      </Z1PAYDTL>
    </Z1PAYHDR>

    And the XSLT is:

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl xsl" version="1.0">
      <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
      <xsl:key name="DetailCC" match="Z1PAYDTL" use="CREDIT_CARD" />
      <xsl:template match="Z1PAYHDR">
        <Output>
          <xsl:for-each select="Z1PAYDTL[count(. | key('DetailCC', CREDIT_CARD)[1]) = 1]">
            <xsl:variable name="card" select="CREDIT_CARD" />
            <CC>
              <Card>
                <xsl:value-of select="$card" />
              </Card>
              <TotalAmount>
                <xsl:value-of select="sum(//Z1PAYDTL[CREDIT_CARD = $card]/AMOUNT)" />
              </TotalAmount>
            </CC>
          </xsl:for-each>
        </Output>
      </xsl:template>
    </xsl:stylesheet>

    You will get the folllowing output:

    <Output>
      <CC>
        <Card>VISA</Card>
        <TotalAmount>1110</TotalAmount>
      </CC>
      <CC>
        <Card>MASTER</Card>
        <TotalAmount>50</TotalAmount>
      </CC>
    </Output>

    Morten la Cour

    • Marked as answer by SarvaSetty Wednesday, February 11, 2015 3:08 PM
    Wednesday, February 11, 2015 11:25 AM
  • Hi Sarva,

    You can use Cumulative Sum functoid with second parameter as "1".

    I have created one sample xsd from the sample u provided. Used below instance for testing:

    <ns0:ZPAYMENT_EXTRACT xmlns:ns0="http://sample_dynamicXSLT.input_idocSchema">
      <IDOC>
        <BEGIN>BEGIN_0</BEGIN>
        <EDI_DC40>
          <field1>field1_0</field1>
          <field2>field2_0</field2>
        </EDI_DC40>
        <Z1PAYHDR>
          <SEGMENT>Girish</SEGMENT>
          <ACCOUNT>111</ACCOUNT>
          <XMIT_DATE>XMIT_DATE_0</XMIT_DATE>
          <Z1PAYDTL>
            <PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
            <AMOUNT>100</AMOUNT>
            <CREDIT_CARD>ABC</CREDIT_CARD>
          </Z1PAYDTL>
     <Z1PAYDTL>
            <PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
            <AMOUNT>200</AMOUNT>
            <CREDIT_CARD>PQR</CREDIT_CARD>
          </Z1PAYDTL>
     <Z1PAYDTL>
            <PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
            <AMOUNT>300</AMOUNT>
            <CREDIT_CARD>XYZ</CREDIT_CARD>
          </Z1PAYDTL>
        </Z1PAYHDR>
    <Z1PAYHDR>
          <SEGMENT>Naveen</SEGMENT>
          <ACCOUNT>222</ACCOUNT>
          <XMIT_DATE>XMIT_DATE_0</XMIT_DATE>
          <Z1PAYDTL>
            <PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
            <AMOUNT>40</AMOUNT>
            <CREDIT_CARD>ABC</CREDIT_CARD>
          </Z1PAYDTL>
     <Z1PAYDTL>
            <PAYMENT_DATE>PAYMENT_DATE_0</PAYMENT_DATE>
            <AMOUNT>80</AMOUNT>
            <CREDIT_CARD>PQR</CREDIT_CARD>
          </Z1PAYDTL>
        </Z1PAYHDR>
      </IDOC>
    </ns0:ZPAYMENT_EXTRACT>

    Got the output as:

    <ns0:ZPAYMENT_OUT xmlns:ns0="http://sample_dynamicXSLT.output_idocSchema">
    <CUSTOMER>
    <SEGMENT>Girish</SEGMENT>
    <ACCOUNT>111</ACCOUNT>
    <TOTALAMOUNT>600</TOTALAMOUNT>
    </CUSTOMER>
    <CUSTOMER>
    <SEGMENT>Naveen</SEGMENT>
    <ACCOUNT>222</ACCOUNT>
    <TOTALAMOUNT>120</TOTALAMOUNT>
    </CUSTOMER>
    </ns0:ZPAYMENT_OUT>


    Thanks, Girish R. Patil.

    • Marked as answer by SarvaSetty Wednesday, February 11, 2015 3:08 PM
    Wednesday, February 11, 2015 11:34 AM