[APP EBS 查詢 Oracle EBS版本]

select RELEASE_NAME from fnd_product_groups

 select * from v$version ;



很久以前google搜尋修改的數值金額轉英文範例，給友需要的朋友

select number2eng_sfs(123456,78) from dual
select to_char(to_date(substr(12, length(12)-2, 3),'J' ),'Jsp' ) from dual

create or replace function number2eng_sfs(in_number in number ) return varchar2 is

type eArray is table of varchar2(255);

l_str eArray := eArray('',
' Thousand ', ' Million ',
' Billion ', ' Trillion ',
' Quadrillion ', ' Quintillion ',
' Sextillion ', ' Septillion ',
' Octillion ', ' Nonillion ',
' Decillion ', ' Undecillion ',
' Duodecillion ');

l_num varchar2(50) default trunc(in_number);
h_number number;
v_total varchar2(4000);

begin
   h_number := round(in_number*100)/100;
   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
          v_total := to_char(to_date(substr(l_num, length(l_num)-2, 3),'J' ),'Jsp' )||l_str(i)||v_total;
       end if;
     
       l_num := substr( l_num, 1, length(l_num)-3 );
   end loop;

   if v_total is null then
      v_total := 'Zero' ;
   end if;

   if trunc( h_number ) > 1 then
      v_total := v_total || ' Dollars ';
   else
      v_total := v_total || ' Dollar ';
   end if;

   if to_char( h_number ) like '%.%' then
      l_num := substr(h_number,instr(h_number,'.')+1);
      if length(l_num) = 1 then
         l_num := l_num || '0';
      end if;
   
      if l_num > 0 then
         if l_num > 1 then
            v_total := v_total ||'And '||l_num||' Cents ';
         else
            v_total := v_total ||'And '||l_num||' Cent ';
         end if;
      end if;
   end if;

   return v_total;
end;



==Version 2==

create or replace function number2eng_sfs(in_number in number ) return varchar2 is

type eArray is table of varchar2(255);

l_str eArray := eArray('',
' Thousand ', ' Million ',
' Billion ', ' Trillion ',
' Quadrillion ', ' Quintillion ',
' Sextillion ', ' Septillion ',
' Octillion ', ' Nonillion ',
' Decillion ', ' Undecillion ',
' Duodecillion ');

l_num varchar2(50) default trunc(in_number);
h_number number;
h_number_e varchar2(50);
v_total varchar2(4000);

begin
   h_number := round(in_number*100)/100;
   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
          v_total := to_char(to_date(substr(l_num, length(l_num)-2, 3),'J' ),'Jsp' )||l_str(i)||v_total;
       end if;
     
       l_num := substr( l_num, 1, length(l_num)-3 );
   end loop;

   if v_total is null then
      v_total := 'Zero' ;
   end if;

   if trunc( h_number ) > 1 then
      v_total := v_total || ' Dollars ';
   else
      v_total := v_total || ' Dollar ';
   end if;
 
   --Cents
   if to_char( h_number ) like '%.%' then
      l_num := substr(h_number,instr(h_number,'.')+1);
      if length(l_num) = 1 then
         l_num := l_num || '0';
      end if;
   
      h_number_e := to_char(to_date(substr(l_num, length(l_num)-2, 3),'J' ),'Jsp' );
      if l_num > 0 then
         if l_num > 1 then
            --v_total := v_total ||'And '||l_num||' Cents ';
            v_total := v_total ||'And '||h_number_e||' Cents ';
         else
            --v_total := v_total ||'And '||l_num||' Cent ';
            v_total := v_total ||'And '||h_number_e||' Cent ';
         end if;
      end if;
   end if;

   return v_total;
end;