Forum Home » Business Intelligence Foundation » BI Publisher

Thread: Calculation on Date

Reply to this Thread Reply to this Thread Search Forum Search Forum Back to Thread List Back to Thread List

Permlink Replies: 5 - Pages: 1 - Last Post: Aug 16, 2007 3:56 AM Last Post By: user589845
user589845

Posts: 5
Registered: 08/13/07
Calculation on Date
Posted: Aug 13, 2007 2:49 PM
Click to report abuse...   Click to reply to this thread Reply
I entered the following in my form field of the temlate

<?xdofx:$C_REPORT_START_DATE-C_DUE_DATE?>

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

Valerie
Darshan2

Posts: 78
Registered: 08/02/06
Re: Calculation on Date
Posted: Aug 14, 2007 3:51 AM   in response to: user589845 in response to: user589845
Click to report abuse...   Click to reply to this thread Reply
Valarie

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.

Thanks
Darshan Bhavsar
Tim Dexter

Posts: 2,268
Registered: 01/10/01
Re: Calculation on Date
Posted: Aug 14, 2007 3:10 PM   in response to: Darshan2 in response to: Darshan2
Click to report abuse...   Click to reply to this thread Reply
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.

Tim
user589845

Posts: 5
Registered: 08/13/07
Re: Calculation on Date
Posted: Aug 15, 2007 1:52 PM   in response to: user589845 in response to: user589845
Click to report abuse...   Click to reply to this thread Reply
Darchan,

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?

Valerie
Darshan2

Posts: 78
Registered: 08/02/06
Re: Calculation on Date
Posted: Aug 15, 2007 9:51 PM   in response to: user589845 in response to: user589845
Click to report abuse...   Click to reply to this thread Reply
Valerie

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
user589845

Posts: 5
Registered: 08/13/07
Re: Calculation on Date
Posted: Aug 16, 2007 3:56 AM   in response to: Darshan2 in response to: Darshan2
Click to report abuse...   Click to reply to this thread Reply
Darshan,

It worked perfectly. Thank you for undertaking this challenge.

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

Thank You,

Valerie
Pages: 1
Back to Thread List Back to Thread List
Legend
Guru Guru : 2500 - 1000000 pts
Expert Expert : 1000 - 2499 pts
Pro Pro : 500 - 999 pts
Journeyman Journeyman : 200 - 499 pts
Newbie Newbie : 0 - 199 pts
Oracle ACE Director
Oracle ACE Member
Oracle Employee ACE
Helpful Answer (5 pts)
Correct Answer (10 pts)

Point your RSS reader here for a feed of the latest messages in all forums