2 Replies Latest reply: Aug 14, 2013 2:04 AM by user13135236 RSS

    Issue while doing SUM in ETEXT Template

    SachinAhuja
      Issue while doing SUM in ETEXT Template

      Hi All,

      We have an eText Template for Oracle Payments, where in footer record we are doing some of Values in XML file using below functions
      INTEGER_PART(SUM(OutboundPayment/PaymentAmount/Value))|| ‘.’||RPAD(SUBSTR(DECIMAL_PART(SUM(OutboundPayment/PaymentAmount/Value)),1,2),2,’0’).
      I have below values coming in the file in Value Tag,
      40000
      2727
      5487.57
      508557.5
      1548
      546846.1
      50960
      765.21
      31.06
      43.99
      5845851
      86.13
      3444
      1000001
      229.31
      2.96
      50
      100
      6584.54
      When I do there sum in SQL using below select , I am getting o/p as 8013315.37, however using function I gave first which is there in XML, we are getting value as 8013315.36, so there is difference of .01 coming while doing Sum in eText Template.
      select (40000+6584.54+5487.57+508557.5+1548+546846.1+50960+765.21+31.06+43.99+5845851+86.13+3444+1000001+229.31+2.96+50+100+2727)
      from dual
      /
      Please suggest.
        • 1. Re: Issue while doing SUM in ETEXT Template
          odie_63
          Hi,

          I'm not familiar with eText templates but, out of curiosity, a quick look at the manual suggests that you should use FORMAT_NUMBER with the appropriate format mask instead :

          http://docs.oracle.com/cd/E10091_01/doc/bip.1013/b40017/T421739T481436.htm
          • 2. Re: Issue while doing SUM in ETEXT Template
            user13135236

            hi Sachin,

             

            I am also getting same issue in the eText output. Just wanted to check in case if you were able to fix it.

             

            This is where i would like to use the format function suggested in note

            Oracle XML Publisher Release 12 Known Issues (Doc ID 404928.1)

             

            DECIMAL_PART(SUM(OutboundPayment/PaymentAmount/Value))||’00’


            content from note,


             

             

            Rounding Issue When Adding Decimal Numbers

            As Oracle XDK conforms to XSL standards for IEEE 754, you might encounter rounding issues when adding numbers that are decimals. For example, suppose you add 13683.80 and 516.39, using this syntax in Oracle BI Publisher:

            The value returned is 14200.189999999999 which is caused by a limitation based on the IEEE 754 Binary Floating-Point-Arithmetic.

            To work around this issue, use the format-number function or the round syntax as shown here to obtain a return value of 14200.19:
            <?format-number:(13683.80 + 516.39);'D99'?>
            or
            <?(round((13683.80 + 516.39)*100) div 100)?>