Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 3 May 2014

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;

1 comments: