Home » Developer & Programmer » Forms » Decimal issue in currency (merged 2) (Db11g, form 6i)
Decimal issue in currency (merged 2) [message #664583] Mon, 24 July 2017 02:50 Go to next message
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 Go to previous messageGo to next message
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 #664586 is a reply to message #664585] Mon, 24 July 2017 03:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
make it a number and set the format mask to 9999.999 (add more 9's to the front as appropriate).
Re: Decimal issue in currency [message #664588 is a reply to message #664586] Mon, 24 July 2017 03:21 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 #664589 is a reply to message #664588] Mon, 24 July 2017 03:29 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Thanks for quick reply sir

:payable_amt:=:g_tot-:discount

Sir how I would manage this


Thanks
Re: Decimal issue in currency [message #664590 is a reply to message #664589] Mon, 24 July 2017 03:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Manage what?
Does the assignment not work?
Re: Decimal issue in currency [message #664591 is a reply to message #664589] Mon, 24 July 2017 03:58 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sir I tried to make it a number but thn function is not working
Re: Decimal issue in currency [message #664592 is a reply to message #664591] Mon, 24 July 2017 04:20 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sir same
I used formula in key-next-item

payable_amt:=:g_tot-:discount

Same single decimal is showing 50.5 instead of 50.500
Re: Decimal issue in currency [message #664595 is a reply to message #664592] Mon, 24 July 2017 05:11 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 #664609 is a reply to message #664608] Mon, 24 July 2017 10:29 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's p_number?
Re: Decimal issue in currency [message #664617 is a reply to message #664609] Tue, 25 July 2017 00:26 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sorry sir it is actually payable_amt

Kindly correct me

Regards
Re: Decimal issue in currency [message #664618 is a reply to message #664617] Tue, 25 July 2017 00:43 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sir everything is working fine but decimals still 50.5

Formula i am using

payable_amt:=:g_tot-:discount

G_tot is grand total and payable_amt is net bill but decimals is a issue if I changed data type in form char to number thn formula does collapse please help me solve this issue

Thanks sir
Re: Decimal issue in currency [message #664619 is a reply to message #664618] Tue, 25 July 2017 00:51 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That doesn't make much sense.

What does "formula collapses" mean? What error do you get?

You are (or, if you are not, you should be) subtracting numbers. Therefore, PAYABLE_AMT, G_TOT and DISCOUNT (as they are preceded with a colon) represent block items, all of them should be NUMBERS, and you should set their FORMAT MASK to a desired value (as described earlier).
Re: Decimal issue in currency [message #664622 is a reply to message #664619] Tue, 25 July 2017 01:32 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Thanks for reply sir let me test I will update u soon sorry about English if I could not make u understand


Regards
Re: Decimal issue in currency [message #664632 is a reply to message #664622] Tue, 25 July 2017 08:15 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: Decimal issue in currency [message #664669 is a reply to message #664633] Wed, 26 July 2017 23:32 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Thanks to all

Work done with some alterations

l_dec   varchar2(50) Default Substr(To_Char(payable_amt  - Trunc(payable_amt ), '.999'),2);

Regards
Re: Decimal issue in currency [message #664692 is a reply to message #664669] Fri, 28 July 2017 09:08 Go to previous message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Thanks sir yes to_char also worked where I was passing parameter to function


Thanks again
Previous Topic: execute_query failure
Next Topic: Cancel process
Goto Forum:
  


Current Time: Thu Mar 28 07:15:46 CDT 2024