The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_file_last_update_date IN VARCHAR2,
p_db_last_updated_by IN NUMBER,
p_db_last_update_date IN DATE,
x_file_who IN OUT nocopy who_type)
RETURN BOOLEAN
IS
l_db_who who_type;
x_file_who.last_updated_by := fnd_load_util.owner_id(l_file_owner);
x_file_who.last_update_date := Trunc(Nvl(To_date(p_file_last_update_date,
g_date_mask),
g_default_lud));
x_file_who.last_update_login := 0;
x_file_who.created_by := x_file_who.last_updated_by;
x_file_who.creation_date := x_file_who.last_update_date;
x_file_who.last_updated_by := fnd_load_util.owner_id(l_file_owner);
x_file_who.last_update_date := Trunc(g_default_lud);
x_file_who.last_update_login := 0;
x_file_who.created_by := x_file_who.last_updated_by;
x_file_who.creation_date := x_file_who.last_update_date;
l_db_who.last_updated_by := Nvl(p_db_last_updated_by,
x_file_who.last_updated_by);
l_db_owner := fnd_load_util.owner_name(l_db_who.last_updated_by);
l_db_who.last_update_date := Nvl(p_db_last_update_date,
x_file_who.last_update_date - 1);
l_db_who.last_update_login := 0;
l_db_who.created_by := l_db_who.last_updated_by;
l_db_who.creation_date := l_db_who.last_update_date;
(p_file_id => x_file_who.last_updated_by,
p_file_lud => x_file_who.last_update_date,
p_db_id => l_db_who.last_updated_by,
p_db_lud => l_db_who.last_update_date,
p_custom_mode => p_custom_mode);
x_last_update_date in varchar2,
x_owner in varchar2
)
is
l_user_id number := 0;
l_db_last_updated_by NUMBER;
l_db_last_update_date DATE;
select last_updated_by, last_update_date into l_db_last_updated_by, l_db_last_update_date
from qp_pte_source_systems
where pte_code = x_pte_code
and application_short_name = x_application_short_name
and rownum = 1;
p_file_last_update_date => x_last_update_date,
p_db_last_updated_by => l_db_last_updated_by,
p_db_last_update_date => l_db_last_update_date,
x_file_who => l_db_who))
THEN
null;
update qp_pte_source_systems
set enabled_flag = x_enabled_flag,
last_updated_by = l_user_id,
last_update_date =to_date(x_last_update_date,'YYYY/MM/DD'),
last_update_login = 0
where pte_code = x_pte_code
and application_short_name = x_application_short_name;
select qp_pte_source_system_s.nextval into l_pte_source_system_id from dual;
insert into qp_pte_source_systems
(pte_source_system_id,
pte_code,
application_short_name,
enabled_flag,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by)
values
(l_pte_source_system_id,
x_pte_code,
x_application_short_name,
x_enabled_flag,
sysdate,
l_user_id,
to_date(x_last_update_date,'YYYY/MM/DD'),
0,
l_user_id);
x_last_update_date in varchar2,
x_owner in varchar2
)
is
l_user_id number := 0;
l_db_last_updated_by NUMBER;
l_db_last_update_date DATE;
select pte_source_system_id into l_pte_source_system_id
from qp_pte_source_systems
where pte_code = x_pte_code
and application_short_name = x_application_short_name
and rownum = 1;
select pte_sourcesystem_fnarea_id,last_updated_by, last_update_date
into l_pte_sourcesystem_fnarea_id, l_db_last_updated_by, l_db_last_update_date
from QP_SOURCESYSTEM_FNAREA_MAP
where pte_source_system_id = l_pte_source_system_id
and functional_area_id = x_functional_area_id
and rownum = 1;
p_file_last_update_date => x_last_update_date,
p_db_last_updated_by => l_db_last_updated_by,
p_db_last_update_date => l_db_last_update_date,
x_file_who => l_db_who))
THEN
null;
update QP_SOURCESYSTEM_FNAREA_MAP
set enabled_flag = x_enabled_flag,
seeded_flag = x_seeded_flag,
last_updated_by = l_user_id,
last_update_date =to_date(x_last_update_date,'YYYY/MM/DD'),
last_update_login = 0
where pte_sourcesystem_fnarea_id = l_pte_sourcesystem_fnarea_id;
select qp_pte_ss_fnarea_id_s.nextval into l_pte_sourcesystem_fnarea_id from dual;
insert into QP_SOURCESYSTEM_FNAREA_MAP ( pte_sourcesystem_fnarea_id,
pte_source_system_id,
functional_area_id,
enabled_flag,
seeded_flag,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by)
values (l_pte_sourcesystem_fnarea_id,
l_pte_source_system_id,
x_functional_area_id,
x_enabled_flag,
x_seeded_flag,
sysdate,
l_user_id,
to_date(x_last_update_date,'YYYY/MM/DD'),
0,
l_user_id);
x_last_update_date in varchar2,
x_owner in varchar2
)
is
begin
update qp_pte_request_types_tl set
request_type_desc = nvl(x_request_type_desc, request_type_desc),
last_update_date = to_date(x_last_update_date,'YYYY/MM/DD'),
last_updated_by = decode(x_owner, 'SEED', 1, 0),
last_update_login = 0,
source_lang = userenv('LANG')
where request_type_code = x_request_type_code
and userenv('LANG') in (language, source_lang);
x_last_update_date in varchar2,
x_owner in varchar2
)
is
l_user_id number := 0;
update qp_pte_request_types_b set
order_level_global_struct = x_order_level_global_struct,
line_level_global_struct = x_line_level_global_struct,
order_level_view_name = x_order_level_view_name,
line_level_view_name = x_line_level_view_name,
enabled_flag = x_enabled_flag,
last_updated_by = l_user_id,
last_update_date = to_date(x_last_update_date ,'YYYY/MM/DD'),
last_update_login = 0
where request_type_code = x_request_type_code
and pte_code = x_pte_code;
insert into qp_pte_request_types_b (
request_type_code,
pte_code,
order_level_global_struct,
line_level_global_struct,
order_level_view_name,
line_level_view_name,
enabled_flag,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by
) values (
x_request_type_code,
x_pte_code,
x_order_level_global_struct,
x_line_level_global_struct,
x_order_level_view_name,
x_line_level_view_name,
x_enabled_flag,
sysdate,
l_user_id,
to_date(x_last_update_date,'YYYY/MM/DD'),
0,
l_user_id
);
insert into qp_pte_request_types_tl (
request_type_code,
request_type_desc,
source_lang,
language,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by
) select
x_request_type_code,
x_request_type_desc,
userenv('LANG'),
l.language_code,
sysdate,
l_user_id,
to_date(x_last_update_date,'YYYY/MM/DD'),
0,
l_user_id
from fnd_languages l
where l.installed_flag in ('I', 'B')
and not exists
(select null
from qp_pte_request_types_tl t,
qp_pte_request_types_b b
where t.request_type_code = x_request_type_code
and t.language = l.language_code);
update qp_pte_request_types_tl set
request_type_desc = nvl(x_request_type_desc, request_type_desc),
last_update_date=to_date(x_last_update_date,'YYYY/MM/DD'),
last_updated_by = l_user_id,
last_update_login = 0,
source_lang = userenv('LANG')
where request_type_code = x_request_type_code
and userenv('LANG') in (language, source_lang);