6 Replies Latest reply: Dec 7, 2011 3:42 AM by user12989669 RSS

    Calculation on Date

      I entered the following in my form field of the temlate


      I declared the parameter C_REPORT_START_DATE in the template
      When I run the report I recieve the following error in the OPP log file

      Template code: EXBRZAPXINAGE
      Template app: EXELAP
      Language: pt
      Territory: 00
      Output type: PDF
      [8/13/07 4:23:04 PM] [UNEXPECTED] [88123:RT6651647] java.lang.reflect.InvocationTargetException
           at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
           at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
           at java.lang.reflect.Method.invoke(Method.java:585)
           at oracle.apps.xdo.common.xml.XSLT10gR1.invokeProcessXSL(XSLT10gR1.java:586)
           at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:383)
           at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:201)
           at oracle.apps.xdo.common.xml.XSLTWrapper.transform(XSLTWrapper.java:161)
           at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:1015)
           at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:968)
           at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:209)
           at oracle.apps.xdo.template.FOProcessor.createFO(FOProcessor.java:1561)
           at oracle.apps.xdo.template.FOProcessor.generate(FOProcessor.java:951)
           at oracle.apps.xdo.oa.schema.server.TemplateHelper.runProcessTemplate(TemplateHelper.java:5975)
           at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3555)
           at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3614)
           at oracle.apps.fnd.cp.opp.XMLPublisherProcessor.process(XMLPublisherProcessor.java:247)
           at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:153)
      Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert to number.
           at oracle.xdo.parser.v2.XSLStylesheet.flushErrors(XSLStylesheet.java:1526)
           at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:517)
           at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:485)
           at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:264)
           at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:150)
           at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:187)
           ... 18 more

      The date is in the iso format (YYYY-MM-DD..etc)

      We are on version 5.6.2

      Any suggestion is appreciated

        • 1. Re: Calculation on Date

          I think still date manipluation is not supported.You can refer following blog entry from Tim. http://blogs.oracle.com/xmlpublisher/2007/04/20#a251

          But i would like to give you workaround.

          1) Create one Hidden parameter for that concurrent program and assign default value as ( select :$FLEX$.C_REPORT_START_DATE - :$FLEX$.C_DUE_DATE from dual) and refer this parameter in your layout template. You have to twick above query to extract correct difference between dates.

          So in summary what i mean is ,derive difference between date from parameter it self through concurrent processing rather than in layout template.

          2) If you still want to do it in template then i would like to suggest you first that convert your dates into number by julius format_code to_number(TO_CHAR(SYSDATE,'JSSSSS')) and then do substraction/addition. This will give you the difference in seconds.
          <?xdofx:tO_NUMBER(TO_CHAR(to_date('2007-08-15', 'YYYY-MM-DD'),'JSSSSS'))-TO_ NUMBER(TO_CHAR(to_date('2007-08-14', 'YYYY-MM-DD'),'JSSSSS'))?>

          Hope this will help you little bit.

          Darshan Bhavsar
          • 2. Re: Calculation on Date
            Tim Dexter
            Hi Darshan
            Nice workaround .... worthy of a blog entry with full credit to you if you dont mind?
            Good news is that we are addressing this real soon.

            • 3. Re: Calculation on Date

              Thank you for this.

              I can calculate the difference between these two dates. The result is in Julian seconds. I am having difficulty with getting the seconds to days.

              This is what I have now that calculates the seconds.
              <?xdofx:to_number(to_char(SYSDATE,'JSSSSS'))-to_number(to_char(to_date(C_DUE_DATE, 'YYYY-MM-DD'),'JSSSSS'))?>

              When I change it to
              <?xdofx:to_number(to_char(SYSDATE,'JSSSSS'))-to_number(to_char(to_date(C_DUE_DATE, 'YYYY-MM-DD'),'JSSSSS'))/86400?>

              I also tried
              <?xdofx:(to_number(to_char(SYSDATE,'JSSSSS'))-to_number(to_char(to_date(C_DUE_DATE, 'YYYY-MM-DD'),'JSSSSS')))/86400?>

              To get the days it ignores the division. I must be missing a () or a comma or somehting.

              Any suggestions?

              • 4. Re: Calculation on Date

                Please try following ,it will give you correct difference between dates in days.

                <?xdofx:round((to_number(to_char(SYSDATE,'JSSSSS'))-to_number(to_char(to_date(C_DUE_DATE, 'YYYY-MM-DD'),'JSSSSS'))) div 100000)?>

                Little explanation so that it will help to others as well. Comment below is purely based on my observation. Tim or Other XML Gurus ,please correct me if i am wrong

                1) I think Problem is division sign (/) is not still not supported ,when i tried to use it,it was throwing an parser error <!--Error: Encountered "/" at line 1, column XXX.
                Was expecting one of:

                Reason why it will throw an error because division sign ( / ) is treated as special XML character ( end_tag symbol) and XML parser engine is not able to parse the expression if it has special characters. So i think <?xdofx:expression?> implementation has to be changed to handle division sign ( / ).

                Meanwhile workaround is to use div instead of division sign ( / ).
                I have tested other mathematical operator ,all are working except division due to division sign ( / ) is treated as as XML special character.

                2) Reason why i have given 100000 as divisor because when you convert sysdate to julian seconds ,sysdate will include time component as well. so it will not give correct days.

                I have tested it and it works as desired.Hope its clear to you.

                With Best Regards
                Darshan Bhavsar
                • 5. Re: Calculation on Date

                  It worked perfectly. Thank you for undertaking this challenge.

                  It will even give accurate differences when the dates cross calendar years.

                  Thank You,

                  • 6. Re: Calculation on Date
                    Hi Darshan...

                    I'm trying to do the following. But getting no results (null)

                    <?xdoxslt:set_variable($_XDOCTX, 'TD', '2011-01-10')?>

                    <?xdofx:round((to_number(to_char(SYSDATE,'JSSSSS'))-to_number(to_char(to_date(get_variable($_XDOCTX, 'TD'), 'YYYY-MM-DD'),'JSSSSS'))) div 100000)?>

                    What's wrong with it?
                    Please help me...