How to Convert NetTotal Amount in words from digit in oracle Report

How to Convert NetTotal Amount in words from digit in oracle Report

Use the Summary column to find the sum of the details report amount then later use the formula column to convert this NetTotal amount into words.

Create Summary Column CS_1

Number 14,2

Function: Sum

Source : Amount (base table column)

Reset at: Block_name

Through this way you are able to find the sum of the details column Amount.

Now to convert Amount in words from digit, Create a formula column
function CF_AMOUNT_IN_WORDSFormula return Char is
vRiyals NUMBER;
VHalala NUMBER;

begin
 VRIYALS := TRUNC(:cs_1);
 VHalala := TRUNC((:CS_1 - VRIYALS)*100);
 
 IF VRIYALS > 0 THEN
  IF VHALALA > 0 THEN
   RETURN('SAR '||NumberInWords(VRIYALS)|| ' & ' || NumberInWords(VHalala)||' Halala Only');
  ELSE
   return('SAR '||NumberInWords(VRIYALS)||' Only');
  END IF;
 ELSE
  IF VHALALA > 0 THEN
   RETURN('SAR ' || NumberInWords(VHalala)||' Halala Only');
  ELSE
   return('SAR Zero Only');
  END IF;
 END IF;  
 
end;
Then later write Procedure to convert Amount from digit to words in ‘Program unit’
FUNCTION NumberInWords(NumberArg IN NUMBER) RETURN VARCHAR2 IS

  TYPE v_arr19 IS VARRAY(19) of VARCHAR2(12);
  TYPE v_arr9 IS VARRAY(9) of VARCHAR2(12);

  W19 v_arr19 := v_arr19('One','Two','Three','Four','Five','Six','Seven','Eight','Nine','Ten',
           'Eleven','Twelve','Thirteen','Fourteen','Fifteen','Sixteen','Seventeen','Eighteen','Nineteen');
  W90 v_arr9 := V_ARR9('Ten','Twenty','Thirty','Forty','Fifty','Sixty','Seventy','Eighty','Ninety');
  Billions NUMBER;
  Millions NUMBER;
  Thousands NUMBER;
  Hundreds NUMBER;
  TENS NUMBER;
  VNumberInWords VARCHAR2(300);
  vNumberArg NUMBER;
BEGIN          
 
    If NumberArg > 999999999999999 Then
        return('Number Too High!!!');
    End If;
    vNumberArg := NumberArg;
 
    VNumberInWords := '';
    ------ Billion?
     Billions := TRUNC(VNumberArg / 1000000000000);
     If Billions > 0 Then
        VNumberInWords := NumberInWords(Billions) || 'Billion ';
        VNumberArg := MOD(VNumberArg, 1000000000000);
     End If;
    ------ Million?
     Millions := TRUNC(VNumberArg / 1000000);
     If Millions > 0 Then
        VNumberInWords := VNumberInWords || NumberInWords(Millions) ||'Million ';
        VNumberArg := MOD(VNumberArg, 1000000);
     End If;
    ------ Thousands?
     Thousands := TRUNC(VNumberArg / 1000);
     If Thousands > 0 Then
        VNumberInWords := VNumberInWords || NumberInWords(Thousands) ||'Thousand ';
        VNumberArg := MOD(VNumberArg,1000);
     End If;
    ------ Hundreds?
     Hundreds := TRUNC(VNumberArg / 100);
     If Hundreds > 0 Then
        VNumberInWords := VNumberInWords || NumberInWords(Hundreds) ||'Hundred ';
        VNumberArg := MOD(VNumberArg, 100);
     End If;
    ------ Tens?
     tens := TRUNC(VNumberArg / 10);
     If tens > 1 Then
        If LENGTH(VNumberInWords) IS NULL THEN--VNumberInWords = '' Then
            VNumberInWords := W90(tens)||' ';
        Else
            VNumberInWords := VNumberInWords ||'and ' || W90(tens)||' ';
        End If;
        VNumberArg := MOD(VNumberArg,10);
     End If;
     If VNumberArg > 0 Then
        If LENGTH(VNumberInWords) IS NULL Then
            VNumberInWords := W19(VNumberArg)||' ';
        Else
            If tens > 1 Then
                VNumberInWords := VNumberInWords || W19(VNumberArg) || ' ';
            Else
              VNumberInWords := VNumberInWords ||'and ' || W19(VNumberArg) || ' ';
            End If;
        End If;
     End If;
    RETURN(vNumberInWords);
END NumberInWords;