I am facing this weird problem where my VO is not getting filled because I am getting "A formatting exception occured: DocumentExpiryDatenull". I have around 12 more dates and for all of them is happening the same. The weird thing is that if I go to DB and re-execute the query I don't get any problems so I assume is at VO level. They just come as null.
Also, with others record which the same attributes are null, I DONT get the issue.
This is the query for my VO:
FND_DATE.canonical_to_date(MyEO.DEI_INFORMATION3) as DocumentExpiryDate,
FROM MyTableName MyEO
I have turned on the diagnostic and everuthing but I just see:
"A formatting exception occured: DocumentExpiryDatenull"
That is an oracle API which converts strings dates from canonical form ("YYYY-MM-DD hh:mm:ss"). It does works when getting null values. and it just returns a null date. The problem is that with some rows I get the exception. So I am starting to believe that could be a hide character or something on the data because It doesn't make really sense. And I can't find the way to get more information from the log.
If I run the queries on DB the all work perfect.
Well all this time I have been trying to figure out what is really happening. I realised that if I don't apply a ViewCriteria the error doesn't happen. Let me explain:
My view object is based on three EO. So I need to retrieve the effective rows for each of the EO so my view object contains the updated data. Basically I applied to each EO a view criteria to get the active row
effectiveStartDate < adf.currentDate and effectiveEndDate > adf.currentDate...
Is just one of the EO who is failing but the interesting part is that if I create a viewObject based on that particular EO and apply the same viewCriteria, guess... There is not error. Im sorry if I don't explained very well. But I am really frustrated.
Have you verified the query created at run-time by the view criteria?
You can set debug output to console in order to get more information about that error.
BTW, did you tried to use to_date function instead FND_DATE.canonical_to_date?
Well, I found out that that is not the issue any more. The format exception was appearing because for some reason my VO stops executing and because of that all the transient attributes (even adf.currentDate) would return the exception. When I deleted all the transient attributes (like the DATES and adf.currentDates etc) I don't have anymore any exception but the VO comes empty when it should not happen.
It is the view criteria I am applying that for some reason makes my VO to collapse. If I don't apply that VC the VO loads with the information. There is no difference (i can't see one) between the rows that normally work and the rows that make my AM to fail like this.
You made me go and check again and I realised....... Those error were happening because my VO was empty. And the reason for they being empty in that particular VC is because the test date was created today and my VC was currentDate > effectiveStartDate but 29-Nov-2012 !> 29-Nov-2012. Thank you Ale and Timo for your help.