PROCEDURE create_trx_sequence (
P_batch_source_id
IN
number,
P_org_id
IN number
default null,
P_last_number
IN
number
) IS
l_sequence_name
varchar2(1024);
l_app_short_name varchar2(3);
l_org_id
varchar2(30);
l_sql_stmt
varchar2(2000);
l_fnd_user
varchar2(30);
-- Start Bug 6010774, 6903507
l_sequence_name_2
varchar2(1024);
l_sql_stmt_2
varchar2(2000);
l_sequence_name_3
varchar2(1024);
l_sql_stmt_3
varchar2(2000);
l_sequence_name_4
varchar2(1024);
l_sql_stmt_4
varchar2(2000);
l_country_code
varchar2(30);
-- End Bug 6010774, 6903507
BEGIN
select min(ou.oracle_username)
into
l_fnd_user
from
FND_PRODUCT_INSTALLATIONS pi,
FND_ORACLE_USERID ou
where ou.oracle_id =
pi.oracle_id
and
application_id = 0;
IF (p_org_id is NULL) THEN
l_sequence_name :=
'RA_TRX_NUMBER_'||to_char(P_batch_source_id)||'_S';
--
Start Bug 6010774, 6903507
l_sequence_name_2 :=
'JA_GUI_NUMBER_'||to_char(P_batch_source_id)||'_S';
l_sequence_name_3 :=
'JL_ZZ_TRX_NUM_'||to_char(P_batch_source_id)||'_S';
--
End Bug 6010774, 6903507
l_sequence_name_4 :=
'JL_BR_TRX_NUM_'||to_char(P_batch_source_id)||'_S';
ELSE
l_sequence_name := 'RA_TRX_NUMBER_' ||
to_char(P_batch_source_id)||
'_' || p_org_id || '_S';
--
Start Bug 6010774, 6903507
l_sequence_name_2 := 'JA_GUI_NUMBER_' ||
to_char(P_batch_source_id)||
'_' || p_org_id || '_S';
l_sequence_name_3 := 'JL_ZZ_TRX_NUM_' ||
to_char(P_batch_source_id)||
'_' || p_org_id || '_S';
--
End Bug 6010774, 6903507
l_sequence_name_4 := 'JL_BR_TRX_NUM_' ||
to_char(P_batch_source_id)||
'_' ||
p_org_id || '_S';
END IF;
l_sql_stmt := 'create sequence
'||l_sequence_name||
' minvalue 1 maxvalue
99999999999999999999 start with '||
to_char(P_last_number + 1)||'
cache 20';
ad_ddl.do_ddl(l_fnd_user, 'AR',
ad_ddl.create_sequence, l_sql_stmt, l_sequence_name);
-- Start Bug 6010774, 6903507
fnd_profile.get('JGZZ_COUNTRY_CODE',
l_country_code);
IF (l_country_code = 'TW') THEN
l_sql_stmt_2 := 'create
sequence '||l_sequence_name_2||
' minvalue 1 maxvalue
99999999999999999999 start with '||
to_char(P_last_number + 1)||'
nocache';
ad_ddl.do_ddl(l_fnd_user, 'JA', ad_ddl.create_sequence,
l_sql_stmt_2, l_sequence_name_2);
ELSIF (l_country_code = 'BR') THEN
l_sql_stmt_4 := 'create sequence '||l_sequence_name_4||
' minvalue
1 maxvalue 99999999999999999999 start with
'||
to_char(P_last_number + 1)||' nocache';
ad_ddl.do_ddl(l_fnd_user, 'JL', ad_ddl.create_sequence,
l_sql_stmt_4,l_sequence_name_4);
ELSIF (l_country_code = 'AR') THEN
l_sql_stmt_3 := 'create
sequence '||l_sequence_name_3||
' minvalue 1 maxvalue
99999999999999999999 start with '||
to_char(P_last_number + 1)||'
nocache';
ad_ddl.do_ddl(l_fnd_user, 'JL', ad_ddl.create_sequence,
l_sql_stmt_3, l_sequence_name_3);
END IF;
-- End Bug 6010774, 6903507
END;
加载中…
喜欢