EBS开发_数字转换成人民币大写

create or replace function cux_convert_cny(num in number) return varchar2 is

  l_receive_number number(14, 2);
  l_number         varchar2(100);
  l_num_length     number;
  l_unit           varchar2(100) := 'QBSGYQBSGWQBSG.JF';
  l_cny            varchar2(200);
  l_trans_cny      varchar2(200);
  function translater(transNum in varchar2) return varchar2 is
    l_meaning varchar2(80);
  begin
    select flv.meaning
      into l_meaning
      from fnd_lookup_values_vl flv
     where 1 = 1
       and flv.enabled_flag = 'Y'
       and sysdate between nvl(flv.start_date_active, sysdate) and
           nvl(flv.end_date_active, sysdate + 1)
       and flv.lookup_code = transNum
       and flv.lookup_type = 'CUX_CNY_DICTIONARY'
       and flv.view_application_id = 3
       and flv.security_group_id = 0;
    return l_meaning;
  end translater;
begin
  --checking input number throw exception
  select decode(num, 0, 'x', num) into l_receive_number from dual;
  l_number     := trim(to_char(l_receive_number, '9999,9999,9999.99'));
  l_num_length := length(l_number);
  --linking number and unit
  for numIndex in 1 .. l_num_length loop
    l_cny := l_cny || (substr(l_number, numIndex, 1) ||
             substr(l_unit, - (l_num_length - numIndex + 1), 1));
  end loop;
  --replace symbol
  select regexp_replace(l_cny, ',|\.', '') into l_cny from dual;
  --insert UZ to 0J0F
  select regexp_replace(l_cny, '(0J0F)', 'UZ') into l_cny from dual;
  --insert U to xJxF
  select regexp_replace(l_cny, '([0-9]J[0-9]F)', 'U\1')
    into l_cny
    from dual;
  --replace more zero to blank
  select regexp_replace(l_cny,
                        '(0Q0B0S0GW)|(0Q0B0S0G)|(0B0S0G)|(0S0G)|(0G)|(G)|(0J)|(0F)',
                        '')
    into l_cny
    from dual;
  --replace more zero to zero
  select regexp_replace(l_cny, '(0Q0B0S)|(0Q0B)|(0Q)|(0B)|(0S)', '0')
    into l_cny
    from dual;
  --translation
  for transIndex in 1 .. length(l_cny) loop
    l_trans_cny := l_trans_cny || translater(substr(l_cny, transIndex, 1));
  end loop;
  return l_trans_cny;
exception
  when others then
    fnd_file.put_line(fnd_file.output, sqlcode);
    fnd_file.put_line(fnd_file.output, sqlerrm);
    return sqlcode || sqlerrm;
end;