示例.
数字 ：183066999230.68
人民币大写 ：壹仟捌佰参拾亿陆仟陆佰玖拾玖万玖仟贰佰参拾圆陆角捌分
数字 ：999900000000
人民币大写 ：玖仟玖佰玖拾玖亿圆整
Step 1. Create Lookups: CUX_CNY_DICTIONARY
(N) Application Developer > Application > Lookups > Application Object Library
clip_image002[4]
Application Object Library Lookups
Type
CUX_CNY_DICTIONARY
Access Level
l
User
Extensible
System
Meaning
CUX_CNY_DICTIONARY
Application
Application Object Library
Description
CNY character translation
Code
Meaning
Description
Tag
From
To
Enabled
[ ]
0
零
*
1
壹
*
2
贰
*
3
参
*
4
肆
*
5
伍
*
6
陆
*
7
染
*
8
捌
*
9
玖
*
Q
仟
*
B
佰
*
S
拾
*
Y
亿
*
W
万
*
U
圆
*
Z
整
*
J
角
*
F
分
*
Step 2. Create Function: CUX_CONVERT_CNY
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 = 0
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)', 'U1') 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);
end;
Step 3. Apply Function: CUX_CONVERT_CNY
select cux_convert_cny (183066999230.68) cny from dual;
clip_image003[4]
select cux_convert_cny (999900000000) cny from dual;
clip_image004[4]
