Amount in words in Oracle Reports

 Step 1:

To begin creating an Oracle report with the aid of the Report Wizard, just choose Use the Report Wizard from the Checkbox and click OK.

Step 2:

The wizard's assistance menu is all that is there in this section. just press the Next button.

Step 3:

The third page asks you the kind of report you want to produce in my instance, I wanted to make a tabular report, so I checked the Tabular box and clicked on Next.

Step 4:

In this section, the kind of question is "ask." Examine the SQL query and then press the Next button.

Step 5:

You may just type the SQL query in the Query statement box. And then click on the Next Button to proceed.

Step 6:

It is necessary to shift fields from the available part to the display section in the display section. Simply click on thisbutton, and all of your fields will be moved to the display fields. After that, click on the Next Button.

Step 7:                                                                     

In the section under "Calculation," When it comes to my situation, you decide which field has to be computed. I add up the Sal column totals.

Step 8:

In this window, you can only edit the labels and widths of the columns.

Step 9:

In the template area, you must choose a template to work with. In my situation, I don't use any templates, therefore I tick the box next to no template and then click on Next.

Step 10:

On the last page of the report wizard in this window, just click the Finish button.

Step 11:

A tabular format was used to construct the report.

Step 12:

Click on program unit and create it.

Step 13:

Simply create a function with the name Word_conversion on the programme tab of the editor. And then press the OK button.

 

Step 14:

In this function window paste the code I mentioned below.

FUNCTION Word_Conversion (Amount number,Currency varchar2) RETURN varchar2 IS

  Len number; 

  Pos number; 

  Word varchar2(300);

  Unit varchar2(30);

  Integer_Value varchar2(15);

  Integer_For_Millions varchar2(4);

  Fraction_Value varchar2(4);

begin

  Integer_VAlue:=floor(Amount);

  Fraction_Value:=substr(to_char(Amount-Integer_VAlue,'.99'),3,2);

 

     IF Integer_VAlue<=5373484 THEN

         If integer_value = 0 then

            word := ' 0 Only ';        

         else

            word:=initcap(to_char(to_date(trunc(abs(Integer_VAlue)), 'J'), 'JSP'));

         end if;

     else

         loop

             Len:=length(Integer_VAlue);

             if Len >12 then     Pos:=12;Unit:=' Trillion ';

             elsif Len >9 then   Pos:=9; Unit:=' Billion ';

             elsif Len >6 then   Pos:=6; Unit:=' Million ';

             end if;

             Integer_For_Millions:=SUBSTR(Integer_VAlue,1,len-pos);

             Integer_VAlue:=SUBSTR(Integer_VAlue,Len-Pos+1,Len);

             If Integer_For_Millions <> 0 then 

                Word:=' '||Word||initcap(to_char(to_date((Integer_For_Millions), 'J'), 'JSP'))||Unit;

             end If;

             if Pos=6 then exit; end if;

             Pos:=Pos-3;

         end loop;

         If Integer_Value = 0 then

             Word := ' '||Word;

         Else

            Word:=' '||Word||initcap(to_char(to_date((Integer_VAlue), 'J'), 'JSP'));

         End If;

     end if;

        if Fraction_Value = 0 then

             Word:=' '||Word;

        else

             Word:=' '||Word||' and '||initcap(to_char(to_date(Fraction_Value, 'J'), 'JSP'))||' cents only.';

        end if;

      return UPPER(Word);

END;

Step 15:

In the data model section, add a formula column with the following parameter to represent the following.

Column type = “Character”

Width = 200

Step 16:

You must copy and paste the code that I have provided below into the function.

function number_into_wordsFormula return Char is

a varchar2(200);

begin

  a := word_conversion(:SumSalPerReport,'Dollars');

  return a;

end;

Step 17:

Create the field in report layout model and get the property of the field and set the source of formular column

Output:

If you have any problem so download the file to click on "Download".

Download

I hope it's helpful for you. If you have any queries, don't hesitate to contact me.

Name : Muhammad Sahal Qasim

E-mail : s.m.sahal789@gmail.com

Thank you.