Friday, 18 November 2011

How to Convert Number to Word in Oracle Report Builder

function CF_11Formula return Char is
   NumberIN float:=round(:CF_1,2);  
   InvalidNumberFormatModel                     EXCEPTION;
     PRAGMA EXCEPTION_INIT(InvalidNumberFormatModel,-1481);
     InvalidNumber                                   EXCEPTION;
     PRAGMA EXCEPTION_INIT(InvalidNumber,-1722);
     TYPE GroupTableType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
     ConversionType        CHAR(6)                  := '';
     GroupTable                                     GroupTableType;
     GroupIndex                                     NUMBER;
   Words                                           VARCHAR2(2000);
     WholePart                                     NUMBER;
     FractionalPart                                 NUMBER;
     FractionalDigits                             NUMBER;
     Remainder                                     NUMBER;
     Remainder1                                     NUMBER;
     Remainder2                                     NUMBER;
     Suffix                                           VARCHAR2(50);
   BEGIN
      GroupTable(0)    := '';
        GroupTable(1)    := ' ten';
        GroupTable(2)    := ' hundred';
        GroupTable(3)    := ' thousand';
        GroupTable(4)    := ' ten thousand';
        GroupTable(5)    := ' lakh ';
        GroupTable(6)    := ' ten lakh ';
        GroupTable(7)    := ' crore ';
        GroupTable(8)    := ' ten crore ';
        GroupTable(9)    := ' hundred crore ';
        GroupTable(10)    := ' thousand crore ';
        GroupTable(11)    := ' ten thousand crore ';
        GroupTable(12)    := ' lakh crore ';
        GroupTable(13)    := ' ten lakh crore ';
        GroupTable(14)    := ' hundred lakh crore ';
        GroupTable(15)    := ' thousand lakh crore ';
        GroupTable(16)    := ' ten thousand lakh crore ';
        GroupTable(17)    := ' lakh lakh crore ';
        GroupTable(18)    := ' ten lakh lakh crore ';
        GroupTable(19)    := ' crore crore ';

           WholePart    := ABS(TRUNC(NumberIN));  -- Calculate whole and fractional parts
           FractionalPart    := ABS(NumberIN) - WholePart;
           IF FractionalPart = 0 THEN         -- Check if fractional part is 0
              Words    := 'zero paise';
                     Suffix        := ' and ';
           ELSE
              IF ConversionType = 'N' THEN
                   FractionalDigits    := LENGTH(TO_CHAR(FractionalPart)) - 1;
                IF FractionalDigits > 15 THEN
                   RAISE InvalidNumber;
                END IF;
                Suffix            := GroupTable(FractionalDigits) || 'th';
                FractionalPart        := FractionalPart *    POWER(10,FractionalDigits);
           ELSE
                IF LENGTH(TO_CHAR(FractionalPart)) > 3 THEN
                   RAISE InvalidNumber;
                 END IF;
                       FractionalPart        := FractionalPart * 100;
                   IF FractionalPart = 1 THEN
                      Suffix        := ' Paise';
               ELSE
                  Suffix        := ' Paise';
                   END IF;
           END IF;

              IF FractionalPart <= 99999 THEN
                   Words    := TO_CHAR(TO_DATE(FractionalPart,'j'),'Jsp') ||Suffix;
              ELSE
                  GroupIndex    := 0;
                      WHILE FractionalPart != 0
                 LOOP
                     Remainder    := MOD(FractionalPart,1000);
                        IF Remainder != 0 THEN
                           Words    := TO_CHAR(TO_DATE(Remainder,'j'),'Jsp') ||    GroupTable(GroupIndex) || Words;
                        END IF;
                        GroupIndex    := GroupIndex + 3;
                        FractionalPart:= TRUNC(FractionalPart / 1000);
                    END LOOP;
                    Words    := Words || Suffix;
              END IF;
              Suffix        := ' and ';
          END IF;

             IF WholePart = 0  THEN
                  IF ConversionType = '' THEN
                     Words    := 'zero ' || Suffix || Words;
                  ELSE
                  Words    := 'zero' || Suffix || Words;
                  END IF;
             ELSE
                IF WholePart = 1 THEN
                     Suffix    := ' Rupee' || Suffix;
                  ELSE
                     Suffix    := '' || Suffix;
                  END IF;
                IF WholePart <= 99999 THEN
                     Words    := TO_CHAR(TO_DATE(WholePart,'j'),'Jsp') ||Suffix || Words;
                ELSE
                     IF LENGTH(TO_CHAR(WholePart)) > 15 THEN
                        RAISE InvalidNumber;
                     END IF;
                     GroupIndex    := 0;
                     Words        := Suffix || Words;
                     WHILE WholePart != 0
                    LOOP
                           IF WholePart < 10000000 THEN
                       Remainder    := MOD(WholePart,100000);
                              IF Remainder != 0 THEN

                         
                              Words    := TO_CHAR(TO_DATE(Remainder,'j'),'Jsp') ||GroupTable(GroupIndex) || Words;
                                END IF;
                              GroupIndex    := GroupIndex + 5;
                              WholePart    := TRUNC(WholePart / 100000);
                       ELSE
                       Remainder    := MOD(WholePart,10000000);
                              IF Remainder != 0 THEN
                          IF Remainder >= 100000 THEN
                          Remainder2 := MOD(Remainder,100000);
                          Words    :=  TO_CHAR(TO_DATE(Remainder2,'j'),'Jsp') || words;
                          Remainder1 := trunc(Remainder/100000);
                              Words    := TO_CHAR(TO_DATE(Remainder1,'j'),'Jsp') ||' lakh '|| Words;
                          ELSE
                          Words    := TO_CHAR(TO_DATE(Remainder,'j'),'Jsp') ||GroupTable(GroupIndex) || Words;
                          END IF;
                          END IF;
                              GroupIndex    := GroupIndex + 7;
                              WholePart    := TRUNC(WholePart / 10000000);
                       END IF;
                        END LOOP;
                END IF;
             END IF;

             IF Words IS NULL THEN
                Words    := 'zero';
             END IF;
             IF SIGN(NumberIN) = -1 THEN
                Words    := 'minus ' || Words;
             END IF;
             return Words||' Only';
   EXCEPTION
     WHEN OTHERS THEN
       RETURN ' ';                   
   END;


Function CF_1Formula return number is
Number1IN float := :CS_1;-- Sum
Number2IN float := :CS_2;
Number2IN float := :CS_3;
Number2IN float := :CS_4;
Number2IN float := :CS_5;
TOT number;
v_po_num varchar2(30);
v_tx_tot number;
v_curr varchar2(30);
begin
           
             if :CURRENCY_CODE = 'INR' then
                       
                            TOT := nvl(:CS_1,0)+nvl(:CS_2,0)+nvl(:CS_3,0)+nvl(:CS_4,0)+ nvl(:CS_5,0);
                            --return TOT;
                            SRW.MESSAGE(1000, tot);
             else
                                     begin
                                                                                                             select  DISTINCT (PHA.SEGMENT1),
                                                                                                                     sum(JPT.TAX_AMOUNT),jpt.CURRENCY
                                                                                                             into    v_po_num,
                                                                                                                     v_tx_tot,
                                                                                                                     v_curr
                                                                                                             from    PO_HEADERS_ALL PHA,
                                                                                                                     JAI_PO_TAXES JPT,
                                                                                                                     JAI_CMN_TAXES_ALL  JCTA
                                                                                                            where    PHA.PO_HEADER_ID=JPT.PO_HEADER_ID
                                                                                                              AND    PHA.TYPE_LOOKUP_CODE in ('STANDARD','BLANKET')                  
                                                                                                              and    JPT.TAX_ID=JCTA.TAX_ID
                                                                                                              And    PHA.po_header_id=:po_header_id
                                                                                                            group by PHA.SEGMENT1,jpt.CURRENCY;
                                     exception
                                                when others then
                                                                                                 v_tx_tot := 0;
                                     end;
                                     
                                     if v_curr = 'INR' then           
                                                                        TOT := nvl(:CS_1,0)+nvl((v_tx_tot/:E_RATE),0);
                                                --return TOT;
                                     else
                                                                        TOT := nvl(:CS_1,0)+nvl(:CS_2,0)+nvl(:CS_3,0)+nvl(:CS_4,0)+ nvl(:CS_5,0);
                                                --return TOT;
                           end if;
             end if;
             return TOT;
exception
            when others then
              return 0;
end;


OR you can  use below oracle Package - 


SELECT AP_AMOUNT_UTILITIES_PKG.AP_CONVERT_NUMBER (123456789)   AMOUNT_IN_WORD   FROM DUAL;




No comments:

Post a Comment