Home » Applications » Oracle Fusion Apps & E-Business Suite » Formula Column Datatype Char Show "/" on to_char for format number (Report 6i, Oracle 9i)
Formula Column Datatype Char Show "/" on to_char for format number [message #402549] Mon, 11 May 2009 07:52 Go to next message
carpediem
Messages: 6
Registered: May 2009
Junior Member
Hi, i have a custom report for daily book, in some cases for a lot information this report fails, in debug log show an incongruent information about results of formula items. Ie a formula item calcs an get this number 268265934.78 when show the item in the other item formula column show 000000700.00 for the same first item.

When run a to_char(000000700.00,'FML999G999G999G999G999G999G999G999D00') show $/0/,0/0,70/.0/

Please help, thanks a lot.
Regards.
Re: Formula Column Datatype Char Show "/" on to_char for format number [message #402704 is a reply to message #402549] Tue, 12 May 2009 02:28 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the result of
select to_char(000000700.00,'FML999G999G999G999G999G999G999G999D00') from dual
when executed in SQL*Plus? How are your NLS_NUMERIC_CHARACTERS set?
Re: Formula Column Datatype Char Show "/" on to_char for format number [message #402779 is a reply to message #402704] Tue, 12 May 2009 07:50 Go to previous messageGo to next message
carpediem
Messages: 6
Registered: May 2009
Junior Member
Hi Littlefoot, thanks for interest

select to_char(000000700.00,'FML999G999G999G999G999G999G999G999D00') from dual

return $700.00

select value from nls_session_parameters
return
AMERICAN
AMERICA
$
AMERICA
.,
GREGORIAN
DD-MON-YY
AMERICAN
BINARY
HH.MI.SSXFF AM
DD-MON-RR HH.MI.SSXFF AM
HH.MI.SSXFF AM TZR
DD-MON-RR HH.MI.SSXFF AM TZR
$
BINARY
BYTE
FALSE

but the concurrent log output show

Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
LATIN AMERICAN SPANISH_AMERICA.WE8ISO8859P1

'.,'



[Updated on: Tue, 12 May 2009 07:51]

Report message to a moderator

Re: Formula Column Datatype Char Show "/" on to_char for format number [message #402780 is a reply to message #402779] Tue, 12 May 2009 07:55 Go to previous messageGo to next message
carpediem
Messages: 6
Registered: May 2009
Junior Member
Here another print screen for the problem.
Re: Formula Column Datatype Char Show "/" on to_char for format number [message #402895 is a reply to message #402780] Wed, 13 May 2009 00:30 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's crazy ... some numbers are correctly converted, while the others (looking more or less the same) ended up with an error!

From your second image, there was
C_CALC_DR    = 191728356.52
converted    = $191,728,356.52    --> OK

C_CR_TOT_DAY = 191728600.00
converted    = $191,728,60/.0/    --> wrong!

C_CALC_DR    = 000000400.00
converted    = $/0/,0/0,40/.0/    --> wrong!
./fa/1600/0/I thought that, somehow, group and decimal characters were set in an unusual manner (slash and dot or comma), but I was wrong.

Though, there IS a pattern that leads to an error - too many zeros in a string. See, '000000400.00' or '191728600.00' produced an invalid result, while '191728356.52' seems to be OK.

Now, does having all those leading zeros (000000400.00) mean that "input" values are characters? If so, can you convert them to numbers first and then apply required format, so that you'd have TO_CHAR(400.00, ...) instead of TO_CHAR(000000400, ...)?
Re: Formula Column Datatype Char Show "/" on to_char for format number [message #403125 is a reply to message #402895] Wed, 13 May 2009 19:16 Go to previous messageGo to next message
carpediem
Messages: 6
Registered: May 2009
Junior Member
There's something wrong with this numbers, but this problem ocurrs on another report developed by Oracle for Brazil Localization.

By the way here is another screen cap for the same problem but withouth debug notes.

Probably just be a bug produced by data procesed.

Regards.
Re: Formula Column Datatype Char Show "/" on to_char for format number [message #403164 is a reply to message #403125] Thu, 14 May 2009 00:21 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm, it says "Version 11.5.0" - is it Oracle E-Business Suite we are talking about? If so, would you like your topic to be moved over there (i.e. into a more appropriate forum) - maybe someone, who visits it, knows the answer?

By the way, did you check Metalink?
Re: Formula Column Datatype Char Show "/" on to_char for format number [message #403231 is a reply to message #403164] Thu, 14 May 2009 06:45 Go to previous messageGo to next message
carpediem
Messages: 6
Registered: May 2009
Junior Member
You are right its Oracle E-Bussines Report. I have check Metalink and dont have nothing about it. I will move this post. Thanks for help.

Regards.
Re: Formula Column Datatype Char Show "/" on to_char for format number [message #423759 is a reply to message #403164] Sun, 27 September 2009 22:07 Go to previous message
carpediem
Messages: 6
Registered: May 2009
Junior Member
Please Littlefoot, can you move this post into a more appropriate forum?
Thanks, regards.

Previous Topic: How to make invoices / customer delinquent in AR ?
Next Topic: SSHR Bug: One of the Approvers is "BLANK" !!
Goto Forum:
  


Current Time: Sun Sep 25 16:17:20 CDT 2016

Total time taken to generate the page: 0.04202 seconds