Decimal issue in currency (merged 2) [message #664583] |
Mon, 24 July 2017 02:50 |
|
Amjad_1975
Messages: 82 Registered: January 2017
|
Member |
|
|
Hello All Dear seniors I have issu in decimal I have converted currency in word amount successfully done it working fine problem in forms 6i I have a field payable amount which is data type char. In oman works three decimals now in showing only 1 decimal 50.5 but it should show 50.500 please help me to resolve this issue
create or replace function spell_number_inr( payable_amt in varchar2 )
return varchar2
-------------
Thanks
|
|
|
Decimal issue in currency [message #664585 is a reply to message #664583] |
Mon, 24 July 2017 02:58 |
|
Amjad_1975
Messages: 82 Registered: January 2017
|
Member |
|
|
Hello all my question is I have converted currency in word amount in forms 6i done it successfully working fine but in forms 6i I have a field payable_amt data type char u lives in oman and in oman currency have three decimals now payable_amt showing only single decimal 50.5 but it should show 50.500 please help me to resolve this issue
create or replace function cinvert_currency(payable_amt in varchar2 )
return varchar2
------
Thanks
[Updated on: Mon, 24 July 2017 03:01] Report message to a moderator
|
|
|
|
Re: Decimal issue in currency [message #664588 is a reply to message #664586] |
Mon, 24 July 2017 03:21 |
|
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Consider using 0 instead of 9, as well as 'D' as a decimal point character. Here's an example:
SQL> select
2 0.120 col,
3 to_char(0.120, '999.999') fmt_1,
4 to_char(0.120, '990.000') fmt_2,
5 to_char(0.120, '990D000') fmt_3
6 from dual;
COL FMT_1 FMT_2 FMT_3
---------- -------- -------- --------
,12 .120 0.120 0,120
SQL>
|
|
|
|
|
|
|
Re: Decimal issue in currency [message #664595 is a reply to message #664592] |
Mon, 24 July 2017 05:11 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
1) don't use key-next-item for calulcations, because users can navigate out of items without firing it. That trigger should only be used for navigation nothing else
2) It's a number, it needs to be a number and if that breaks the function then you need to fix the function.
3) .5 = .500 How it's calculated doesn't effect how it's displayed. The format mask effects how it's displayed.
|
|
|
Re: Decimal issue in currency [message #664608 is a reply to message #664595] |
Mon, 24 July 2017 09:36 |
|
Amjad_1975
Messages: 82 Registered: January 2017
|
Member |
|
|
Sir this is a function which I am calling on key-next-item sir why I am using key-next-item because barcode scanner are working here
create or replace function convert_currency(payable_amt in varchar2 )
return varchar2
as
type myArray is table of varchar2(255);
l_str myArray := myArray( '',
' thousand ', ' lakhs ', 'million',
' billion ', ' trillion ',
' quadrillion ', ' quintillion ',
' sextillion ', ' septillion ',
' octillion ', ' nonillion ',
' decillion ', ' undecillion ',
' duodecillion ' );
l_num varchar2(50) default trunc( p_number );
l_return varchar2(4000);
begin
for i in 1 .. l_str.count
loop
exit when l_num is null;
if ( substr(l_num, length(l_num)-2, 3)<>0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-2, 3),
'J' ),
'Jsp' ) || l_str(i) || l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-3 );
end loop;
l_return := l_return || ' Rupees';
if payable_amt like '%.%'
then
l_return := l_return || ' and';
l_num := substr( p_number, instr( p_number, '.' )+1 );
l_return := l_return
|| ' '
|| to_char(
to_date(l_num,'j' ),
'jsp' );
l_return := l_return || ' Paisa';
end if;
-- end of section added to include decimal places
return l_return;
end convert_currency;
Thanks but still decimal showing .5 kindly guide me I know you are genius you can easily do this
Regards
|
|
|
|
|
|
|
|
Re: Decimal issue in currency [message #664632 is a reply to message #664622] |
Tue, 25 July 2017 08:15 |
|
Amjad_1975
Messages: 82 Registered: January 2017
|
Member |
|
|
Sir I have changed the data type of payable_amt char to number now 3 decimals are showing but now word amount has same issue "thirty Rial and five Baisa" instead of five hundred baisa
create or replace function convert_currency(payable_amt in varchar2 )
return varchar2
as
type myArray is table of varchar2(255);
l_str myArray := myArray( '',
' thousand ', ' lakhs ', 'million',
' billion ', ' trillion ',
' quadrillion ', ' quintillion ',
' sextillion ', ' septillion ',
' octillion ', ' nonillion ',
' decillion ', ' undecillion ',
' duodecillion ' );
l_num varchar2(50) default trunc( p_number );
l_return varchar2(4000);
begin
for i in 1 .. l_str.count
loop
exit when l_num is null;
if ( substr(l_num, length(l_num)-2, 3)<>0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-2, 3),
'J' ),
'Jsp' ) || l_str(i) || l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-3 );
end loop;
l_return := l_return || ' Rial';
if payable_amt like '%.%'
then
l_return := l_return || ' and';
l_num := substr( payable_amt, instr( payable_amt, '.' )+1 );
l_return := l_return
|| ' '
|| to_char(
to_date(l_num,'j' ),
'jsp' );
l_return := l_return || ' Baisa';
end if;
return l_return;
end convert_currency;
Sir where I am wrong in code kindly correct it
Thanks
Regards
[Updated on: Tue, 25 July 2017 08:24] Report message to a moderator
|
|
|
Re: Decimal issue in currency [message #664633 is a reply to message #664632] |
Tue, 25 July 2017 08:25 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Because your parameter is a varchar and you are passing it a number forms is implicitly converting it using the sessions default format mask - which obviously isn't forcing 3 decimal places.
So number .5 becomes string .5 while your code relies on it being .500.
Simplest solution is use to_char with the appropriate format mask on the number when you pass it to the function.
|
|
|
|
|