[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
last_update_date date,
has_bugfix_replacement boolean,
pil_level integer,
has_files boolean,
download_code varchar2(30));
debug('Update Management System Loader Debugger');
debug('Legend: DM: Download Mode. LDD: Last definition Date. LUD: Last Update Date.');
select fou.oracle_username
from fnd_oracle_userid fou,
fnd_product_installations fpi
where fou.oracle_id = fpi.oracle_id
and fpi.application_id = 0;
select owner, table_name, last_analyzed, num_rows
from dba_tables
where owner = p_owner
and table_name = p_table_name;
p_last_update_date in date)
return data_contents
is
l_data_contents data_contents;
l_data_contents.last_update_date := p_last_update_date;
rpad(to_char(l_file_dc.last_update_date, G_STD_DATE_MASK), 24);
if (l_file_dc.last_update_date > l_db_dc.last_update_date) then
l_debug := l_debug || ' > ';
elsif (l_file_dc.last_update_date = l_db_dc.last_update_date) then
l_debug := l_debug || ' = ';
rpad(to_char(l_db_dc.last_update_date, G_STD_DATE_MASK), 24);
rpad(to_char(l_forced_db_dc.last_update_date, G_STD_DATE_MASK), 24);
rpad(to_char(l_final_dc.last_update_date, G_STD_DATE_MASK), 24);
p_last_update_date in varchar2,
p_custom_mode in varchar2)
is
l_file_dc data_contents;
select decode(instr(p_baseline, '.',1,2) - 1, -1,p_baseline ,
substr(p_baseline , instr(p_baseline , '.',1,2) +1)) into l_baseline from dual;
select decode(instr(p_baseline, '.',1,1) - 1, -1,p_baseline ,
substr(p_baseline , instr(p_baseline , '.',1,1) +1,1)) into l_baseline from
dual;
to_date(nvl(p_last_definition_date, p_last_update_date), G_STD_DATE_MASK),
to_date(p_last_update_date, G_STD_DATE_MASK));
select /*+ INDEX(fnd_ums_bugfixes fnd_ums_bugfixes_u2) */ *
into l_bugfix
from fnd_ums_bugfixes
where release_name = p_release_name
and bug_number = p_bug_number
and baseline = l_baseline;
l_bugfix.last_update_date);
if (l_file_dc.last_update_date <= l_db_dc.last_update_date) then
-- File last update date is older or same, do nothing
null;
l_final_dc.last_update_date := l_file_dc.last_update_date;
l_final_dc.last_update_date := l_file_dc.last_update_date;
if ((l_file_dc.last_update_date < l_db_dc.last_update_date) and
(l_file_dc.last_definition_date < l_db_dc.last_definition_date)) then
-- File is older and has no more data than DB.
raise_application_error(ERROR_ABORT,
MSG_ABORT_OLDER || p_bug_number);
delete from fnd_ums_bugfix_file_versions
where bugfix_guid = g_bugfix_guid;
delete from fnd_ums_bugfix_relationships
where bugfix_guid = g_bugfix_guid
and relation_type in (REL_TYPE_PREREQS,
REL_TYPE_INDIRECTLY_PREREQS,
REL_TYPE_INCLUDES,
REL_TYPE_INDIRECTLY_INCLUDES);
delete from fnd_ums_bugfix_relationships
where bugfix_guid = g_bugfix_guid
and relation_type = REL_TYPE_REPLACED_BY;
delete /*+ INDEX(fnd_ums_bugfixes fnd_ums_bugfixes_u1) */
from fnd_ums_bugfixes
where bugfix_guid = g_bugfix_guid;
insert into fnd_ums_bugfixes
(bugfix_guid,
release_name,
bug_number,
baseline,
download_mode,
application_short_name,
release_status,
type,
abstract,
last_definition_date,
last_update_date)
values
(g_bugfix_guid,
p_release_name,
p_bug_number,
l_baseline,
l_final_dc.download_mode,
l_application_short_name,
l_release_status,
l_type,
l_abstract,
l_final_dc.last_definition_date,
l_final_dc.last_update_date);
update /*+ INDEX(fnd_ums_bugfixes fnd_ums_bugfixes_u1) */ fnd_ums_bugfixes
set download_mode = l_final_dc.download_mode,
last_definition_date = l_final_dc.last_definition_date
where bugfix_guid = g_bugfix_guid;
select /*+ INDEX(fnd_ums_files fnd_ums_files_u2) */ file_guid
into l_file_guid
from fnd_ums_files
where application_short_name = p_application_short_name
and location = p_location
and name = p_name;
insert into fnd_ums_files
(file_guid,
application_short_name,
location,
name)
values
(sys_guid(),
p_application_short_name,
p_location,
p_name)
returning file_guid
into l_file_guid;
select /*+ INDEX(fnd_ums_files fnd_ums_files_u2) */ file_guid
into l_file_guid
from fnd_ums_files
where application_short_name = p_application_short_name
and location = p_location
and name = p_name;
select /*+ INDEX(fnd_ums_file_versions fnd_ums_file_versions_u2) */ file_version_guid
into l_file_version_guid
from fnd_ums_file_versions
where file_guid = p_file_guid
and version = p_version;
insert into fnd_ums_file_versions
(file_version_guid,
file_guid,
version)
values
(sys_guid(),
p_file_guid,
p_version)
returning file_version_guid
into l_file_version_guid;
select /*+ INDEX(fnd_ums_file_versions fnd_ums_file_versions_u2) */ file_version_guid
into l_file_version_guid
from fnd_ums_file_versions
where file_guid = p_file_guid
and version = p_version;
insert into fnd_ums_bugfix_file_versions
(bugfix_guid,
file_version_guid)
values
(g_bugfix_guid,
l_file_version_guid);
select /*+ INDEX(fnd_ums_bugfixes fnd_ums_bugfixes_u2) */ bugfix_guid
into l_bugfix_guid
from fnd_ums_bugfixes
where release_name = p_release_name
and bug_number = p_bug_number;
insert into fnd_ums_bugfixes
(bugfix_guid,
release_name,
bug_number,
download_mode,
application_short_name,
release_status,
type,
abstract,
last_definition_date,
last_update_date)
values
(sys_guid(),
p_release_name,
p_bug_number,
DL_MODE_NONE,
NOT_AVAILABLE,
NOT_AVAILABLE,
NOT_AVAILABLE,
NOT_AVAILABLE,
g_default_date,
g_default_date)
returning bugfix_guid
into l_bugfix_guid;
select /*+ INDEX(fnd_ums_bugfixes fnd_ums_bugfixes_u2) */ bugfix_guid
into l_bugfix_guid
from fnd_ums_bugfixes
where release_name = p_release_name
and bug_number = p_bug_number;
insert into fnd_ums_bugfix_relationships
(bugfix_guid,
relation_type,
related_bugfix_guid)
values
(g_bugfix_guid,
p_relation_type,
l_related_bugfix_guid);