1

Please help in summation logic where I am getting an error while adding the variable value.

XML below

<OutboundPaymentInstruction>
    <OutboundPayment>
        <DocumentPayable>
            <DocumentPayableLine>
                <LineNumber>2</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>TAX</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Utilities; Justification: W-IN-18-6402-1237845 </LineDescription>
                <LineGrossAmount>
                    <Value>7912.5</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH VAT</TaxCode>
                    <TaxRate>7</TaxRate>
                    <TaxRateCode>TH AP STD SVC 7</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>3</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>AWT</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Utilities; Justification: W-IN-18-6402-1237845 </LineDescription>
                <LineGrossAmount>
                    <Value>-3391.07</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH WHT</TaxCode>
                    <TaxRate>2.999999</TaxRate>
                    <TaxRateCode>TH WHT SVCS 03</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>1</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>ITEM</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Utilities;Justification: W-IN-18-6402-1237845 </LineDescription>
                <LineGrossAmount>
                    <Value>113035.71</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode/>
                    <TaxRate>0</TaxRate>
                    <TaxRateCode/>
                </Tax>
            </DocumentPayableLine>
        </DocumentPayable>
    </OutboundPayment>
    <OutboundPayment>
        <DocumentPayable>
            <DocumentPayableLine>
                <LineNumber>2</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>TAX</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>The Garbage Service Fee For 02/64</LineDescription>
                <LineGrossAmount>
                    <Value>6730.5</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH VAT</TaxCode>
                    <TaxRate>7</TaxRate>
                    <TaxRateCode>TH AP STD SVC 7</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>3</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>AWT</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>The Garbage Service Fee For 02/64</LineDescription>
                <LineGrossAmount>
                    <Value>-2884.5</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH WHT</TaxCode>
                    <TaxRate>3</TaxRate>
                    <TaxRateCode>TH WHT SVCS 03</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>1</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>ITEM</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>The Garbage Service Fee For 02/64</LineDescription>
                <LineGrossAmount>
                    <Value>96150</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode/>
                    <TaxRate>0</TaxRate>
                    <TaxRateCode/>
                </Tax>
            </DocumentPayableLine>
        </DocumentPayable>
        <DocumentPayable>
            <DocumentPayableLine>
                <LineNumber>2</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>TAX</Code>
                    <Meaning/>
                </LineType>
                <LineDescription> Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Waste Water )</LineDescription>
                <LineGrossAmount>
                    <Value>28276.42</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH VAT</TaxCode>
                    <TaxRate>7</TaxRate>
                    <TaxRateCode>TH AP STD SVC 7</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>3</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>AWT</Code>
                    <Meaning/>
                </LineType>
                <LineDescription> Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Waste Water )</LineDescription>
                <LineGrossAmount>
                    <Value>-12118.46</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH WHT</TaxCode>
                    <TaxRate>2.999999</TaxRate>
                    <TaxRateCode>TH WHT SVCS 03</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>1</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>ITEM</Code>
                    <Meaning/>
                </LineType>
                <LineDescription> Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Waste Water )</LineDescription>
                <LineGrossAmount>
                    <Value>403948.8</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode/>
                    <TaxRate>0</TaxRate>
                    <TaxRateCode/>
                </Tax>
            </DocumentPayableLine>
        </DocumentPayable>
        <DocumentPayable>
            <DocumentPayableLine>
                <LineNumber>2</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>TAX</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Waste Water )</LineDescription>
                <LineGrossAmount>
                    <Value>13296.15</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH VAT</TaxCode>
                    <TaxRate>7</TaxRate>
                    <TaxRateCode>TH AP STD SVC 7</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>3</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>AWT</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Waste Water )</LineDescription>
                <LineGrossAmount>
                    <Value>-5698.35</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH WHT</TaxCode>
                    <TaxRate>3</TaxRate>
                    <TaxRateCode>TH WHT SVCS 03</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>1</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>ITEM</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Waste Water )</LineDescription>
                <LineGrossAmount>
                    <Value>189945</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode/>
                    <TaxRate>0</TaxRate>
                    <TaxRateCode/>
                </Tax>
            </DocumentPayableLine>
        </DocumentPayable>
        <DocumentPayable>
            <DocumentPayableLine>
                <LineNumber>2</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>TAX</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Rental Car parking ) Cloud GL : 630086 Cloud CC : 50020 Invoice = VAT 0%</LineDescription>
                <LineGrossAmount>
                    <Value>0</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH VAT</TaxCode>
                    <TaxRate>0</TaxRate>
                    <TaxRateCode>TH AP ZERO 0</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>3</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>AWT</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Rental Car parking ) Cloud GL : 630086 Cloud CC : 50020 Invoice = VAT 0%</LineDescription>
                <LineGrossAmount>
                    <Value>-13763.2</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH WHT</TaxCode>
                    <TaxRate>5</TaxRate>
                    <TaxRateCode>TH WHT RENT 05</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>1</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>ITEM</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Rental Car parking )Cloud GL : 630086 Cloud CC : 50020 Invoice = VAT 0%</LineDescription>
                <LineGrossAmount>
                    <Value>275264</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode/>
                    <TaxRate>0</TaxRate>
                    <TaxRateCode/>
                </Tax>
            </DocumentPayableLine>
        </DocumentPayable>
    </OutboundPayment>
</OutboundPaymentInstruction>

Please help as I am trying to do the below logic for individual "AWT" scenario and finally summing up the total by grouping by TaxRateCode for its own OutboundPayment tag.

Forumla trying to implement = (100 div (Tax/TaxRate)) * LineGrossAmount/Value

Example - ((100 div 3) * -5698.35) = -189945
          ((100 div 2.999999) * -12118.46) = -403948.8013
          ((100 div 3) * -2884.5) = -96150

 <calc>690043.80</calc>

Expected Output

<Result>       
                <Rcrd>
                  <TaxAmt>
                     <Rate>3</Rate>
                     <calc>690043.80</calc>
                  </TaxAmt>
               </Rcrd>
               <Rcrd>
                  <TaxAmt>
                     <Rate>5</Rate>
                     <calc>275264.00</calc>
                  </TaxAmt>
               </Rcrd>
               
</Result>
<Result>
               <Rcrd>
                  <TaxAmt>
                     <Rate>3</Rate>
                     <calc>113035.7043</calc>
                  </TaxAmt>
               </Rcrd>
</Result>

Code Tried

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

<xsl:key name="k" match="DocumentPayableLine[LineType/Code='AWT']" use="concat(generate-id(../..), Tax/TaxRateCode)" />

<xsl:template match="/OutboundPaymentInstruction">
    <xsl:for-each select="OutboundPayment">
        <Result>
            <xsl:for-each select="DocumentPayable/DocumentPayableLine[LineType/Code='AWT'][count(. | key('k', concat(generate-id(../..), Tax/TaxRateCode))[1]) = 1]">
                <Rcrd>
                         <xsl:variable name="TX1"><xsl:value-of select="Tax/TaxRate"/></xsl:variable>
                         <xsl:variable name="GrossAmount"><xsl:value-of select="LineGrossAmount/Value"/></xsl:variable>         
                    <TaxAmt>
                        <Rate>
                            <xsl:value-of select="round(Tax/TaxRate)"/>
                        </Rate>
                        
                        <xsl:variable name="IndividualCalc"><xsl:value-of select="((100 div $TX1) * $GrossAmount)"/></xsl:variable>
                        <calc>      
                         <xsl:value-of select="-sum(key('k', concat(generate-id(../..), Tax/TaxRateCode))$IndividualCalc)"/>
                        </calc>
                    </TaxAmt>
                </Rcrd>
            </xsl:for-each>
        </Result>
    </xsl:for-each>
    
    </xsl:template>

</xsl:stylesheet>

Please help.

Amit Sebastian
  • 11
  • 1
  • I cannot follow your explanation. Your first group has a total LineGrossAmount/Value of -3391.07 and a tax rate of 3 so I would expect a tax amount of 101.7321. I don't see these numbers anywhere in your question. – michael.hor257k Dec 09 '21 at 20:42
  • @michael.hor257k - Hi Michael, One of the "OutboundPayment" tags have only one "AWT" scenario which has LineGrossAmount/Value as -3391.07. Hence the formula which we are trying to execute is fromat_number((100 / 2.999999) * -3391.07) = 113035.7043. Please help – Amit Sebastian Dec 10 '21 at 06:39

1 Answers1

0

Without the expressive power of XPath 2 or higher I think, one way is to store the individual values in an XML result tree fragment and convert it to a node-set for summing the values up:

<xsl:template match="/OutboundPaymentInstruction">
    <xsl:for-each select="OutboundPayment">
        <Result>
            <xsl:for-each select="DocumentPayable/DocumentPayableLine[LineType/Code='AWT'][count(. | key('k', concat(generate-id(../..), Tax/TaxRateCode))[1]) = 1]">
                <xsl:variable name="amounts">
                  <xsl:for-each select="key('k', concat(generate-id(../..), Tax/TaxRateCode))">
                    <amount>
                      <xsl:value-of select="100 div Tax/TaxRate * LineGrossAmount/Value"/>
                    </amount>
                  </xsl:for-each>
                </xsl:variable>
                <Rcrd>
                    <TaxAmt>
                        <Rate>
                            <xsl:value-of select="round(Tax/TaxRate)"/>
                        </Rate>
                        
                        <calc>      
                         <xsl:value-of select="format-number(-sum(exsl:node-set($amounts)/amount), '0.00')"/>
                        </calc>
                    </TaxAmt>
                </Rcrd>
            </xsl:for-each>
        </Result>
    </xsl:for-each>
</xsl:template>

I get the values you have shown but in a different order:

<Result>
  <Rcrd>
    <TaxAmt>
      <Rate>3</Rate>
      <calc>113035.70</calc>
    </TaxAmt>
  </Rcrd>
</Result>
<Result>
  <Rcrd>
    <TaxAmt>
      <Rate>3</Rate>
      <calc>690043.80</calc>
    </TaxAmt>
  </Rcrd>
  <Rcrd>
    <TaxAmt>
      <Rate>5</Rate>
      <calc>275264.00</calc>
    </TaxAmt>
  </Rcrd>
</Result>

The XSLT 1 solution needs you to declare xmlns:exsl="http://exslt.org/common" in the XSLT stylesheet (or, for MSXML, change it to a proprietary xmlns:msxml="urn:schemas-microsoft-com:xslt" and use msxml:node-set instead of exsl:node-set).

Martin Honnen
  • 160,499
  • 6
  • 90
  • 110
  • Hi @Martin Honnen , After using the above given code, I am getting the result as -0.00 in tag. Please find the code line also included in the existing XSL 1.0 version of mine. Please help ``` ``` – Amit Sebastian Dec 21 '21 at 16:30
  • Where in your original question do you use `Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03"`? Please don't change requirements days after posting a question. For the code in the question not using a namespace the suggested answer produces the code I have shown in the answer, with two `Result` elements, where the first one has a single `Rcrd` with `113035.70` and the second one two of them, the first with `690043.80` and the second with `275264.00`. – Martin Honnen Dec 21 '21 at 18:23
  • If you are trying to use a result namespace, don't use it for the temporary elements, e.g. by using `..`. Otherwise the XPath selection can't work `exsl:node-set($amounts)/amount`. – Martin Honnen Dec 21 '21 at 18:24