[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select b.bug_id
from AD_BUGS b, AD_TRACKABLE_ENTITIES adte
where b.bug_number = p_bug_number
and upper(b.trackable_entity_abbr) =
DECODE(UPPER(adte.abbreviation),'SO', 'SQLSO',
'FA', 'OFA',
'AP', 'SQLAP',
'GL', 'SQLGL',
UPPER(adte.abbreviation))
and b.language = p_bug_language
and ( b.baseline_name = adte.baseline
or b.generic_patch = 'y'
);
select s.snapshot_id into l_snapshot_id
from AD_SNAPSHOTS s, AD_APPL_TOPS a,
FND_PRODUCT_GROUPS fpg
where s.snapshot_name = GLOBAL_VIEW_SNP_NAME
and s.snapshot_type =
ad_file_sys_snapshots_pkg.get_snapshot_type(GLOBAL_VIEW_SNP_NAME)
and s.appl_top_id = a.appl_top_id
and a.name = 'GLOBAL'
and a.appl_top_type = 'G'
and a.applications_system_name = fpg.applications_system_name
and fpg.product_group_id=1;
select snapshot_id into l_snapshot_id
from AD_SNAPSHOTS
where appl_top_id = p_appl_top_id
and snapshot_name = CURRENT_VIEW_SNP_NAME
and snapshot_type =
ad_file_sys_snapshots_pkg.get_snapshot_type(CURRENT_VIEW_SNP_NAME);
select bug_status into l_bugst
from ad_snapshot_bugfixes
where bugfix_id = cur_bugid.bug_id
and snapshot_id = l_snapshot_id;
select s.snapshot_id into l_snapshot_id
from AD_SNAPSHOTS s, AD_APPL_TOPS a,
FND_PRODUCT_GROUPS fpg
where s.snapshot_name = GLOBAL_VIEW_SNP_NAME
and s.snapshot_type =
ad_file_sys_snapshots_pkg.get_snapshot_type(GLOBAL_VIEW_SNP_NAME)
and s.appl_top_id = a.appl_top_id
and a.name = 'GLOBAL'
and a.appl_top_type = 'G'
and a.applications_system_name = fpg.applications_system_name
and fpg.product_group_id=1;
select snapshot_id into l_snapshot_id
from AD_SNAPSHOTS
where appl_top_id = p_appl_top_id
and snapshot_name = CURRENT_VIEW_SNP_NAME
and snapshot_type =
ad_file_sys_snapshots_pkg.get_snapshot_type(CURRENT_VIEW_SNP_NAME);
select b.bug_id into l_bug_id
from AD_BUGS b
where b.bug_number = p_bug_number
and b.baseline_name = p_baseline_name
and b.aru_release_name = p_release_name
and b.language = p_language;
select bug_status into l_bugst
from ad_snapshot_bugfixes
where bugfix_id = l_bug_id
and snapshot_id = l_snapshot_id;
select v.version
from ad_file_versions v, ad_snapshot_files s
where s.file_id = v.file_id
and s.file_id = p_file_id
and s.snapshot_id = p_snapshot_id
and v.file_version_id = s.file_version_id;
select executed_flag
from ad_patch_run_bug_actions
where file_id = p_file_id
and patch_file_version_id in (select file_version_id
from ad_file_versions
where file_id = p_file_id
and version = max_ver_indb);
select s.snapshot_id into l_snapshot_id
from AD_SNAPSHOTS s, AD_APPL_TOPS a,
FND_PRODUCT_GROUPS fpg
where s.snapshot_name = GLOBAL_VIEW_SNP_NAME
and s.snapshot_type =
ad_file_sys_snapshots_pkg.get_snapshot_type(GLOBAL_VIEW_SNP_NAME)
and s.appl_top_id = a.appl_top_id
and a.name = 'GLOBAL'
and a.appl_top_type = 'G'
and a.applications_system_name = fpg.applications_system_name
and fpg.product_group_id=1;
select snapshot_id into l_snapshot_id
from AD_SNAPSHOTS
where appl_top_id = p_appl_top_id
and snapshot_name = CURRENT_VIEW_SNP_NAME
and snapshot_type =
ad_file_sys_snapshots_pkg.get_snapshot_type(CURRENT_VIEW_SNP_NAME);
select file_id into l_file_id
from ad_files
where app_short_name = p_application_short_name
and subdir = p_object_location
and filename = p_object_name;
cascade update AD_SNAPSHOT_BUGFIXES based on the values in AD_PATCH_RUN_BUGS.
- Don't update AD_SNAPSHOT_BUGFIXES at all if already set to the value
being updated.
- When updating to successful, just mark row in AD_SNAPSHOT_BUGFIXES
as successful.
- When updating to unsuccessful, only mark row in AD_SNAPSHOT_BUGFIXES
as not successful if there are no rows in AD_PATCH_RUN_BUGS marked as
successful for the bug, product, and aru_release.
This routine updates AD_SNAPSHOT_BUGFIXES for given a patch_run_id and
a flag.
*************************************************************************/
procedure mark_patch_bug_in_snpbgfix(p_appl_top_id in number,
p_patch_run_id in number,
p_flag in varchar2)
is
l_snapshot_id AD_SNAPSHOTS.snapshot_id%type;
select bug_id
from AD_PATCH_RUN_BUGS
where patch_run_id = p_patch_run_id;
select success_flag
from AD_PATCH_RUN_BUGS
where patch_run_id <> p_patch_run_id
and bug_id = p_bug_id;
no_update boolean := FALSE;
select snapshot_id into l_snapshot_id
from AD_SNAPSHOTS
where appl_top_id = p_appl_top_id
and snapshot_name = CURRENT_VIEW_SNP_NAME
and snapshot_type =
ad_file_sys_snapshots_pkg.get_snapshot_type(CURRENT_VIEW_SNP_NAME);
update AD_SNAPSHOT_BUGFIXES
set success_flag = p_flag
where snapshot_id = l_snapshot_id
and bugfix_id in (select bug_id from AD_PATCH_RUN_BUGS
where patch_run_id = p_patch_run_id);
'while trying to update success_flag to Y '||
' in AD_SNAPSHOT_BUGFIXES '||
'for patch_run_id = ' || p_patch_run_id);
no_update := TRUE;
if (no_update = FALSE) then
begin
update AD_SNAPSHOT_BUGFIXES
set success_flag = p_flag
where bugfix_id = mybugid.bug_id
and snapshot_id = l_snapshot_id;
'while trying to update success_flag to N '||
' in AD_SNAPSHOT_BUGFIXES '||
'for patch_run_id = ' || p_patch_run_id);
no_update := FALSE;
cascade update AD_SNAPSHOT_BUGFIXES based on the values in AD_PATCH_RUN_BUGS.
- Don't update AD_SNAPSHOT_BUGFIXES at all if already set to the value
being updated.
- When updating to successful, just mark row in AD_SNAPSHOT_BUGFIXES
as successful.
- When updating to unsuccessful, only mark row in AD_SNAPSHOT_BUGFIXES
as not successful if there are no rows in AD_PATCH_RUN_BUGS marked as
successful for the bug, product, and aru_release.
This routine updates AD_SNAPSHOT_BUGFIXES, given a patch_run, bug id.
*************************************************************************/
procedure mark_bug_in_snpbgfix(p_patch_run_id in number,
p_appl_top_id in number,
p_bug_id in number,
p_flag in varchar2)
is
l_bug_id ad_bugs.bug_id%TYPE;
select success_flag
from AD_PATCH_RUN_BUGS
where patch_run_id <> p_patch_run_id
and bug_id = p_bug_id;
no_update boolean := FALSE;
select snapshot_id into l_snapshot_id
from AD_SNAPSHOTS
where appl_top_id = p_appl_top_id
and snapshot_name = CURRENT_VIEW_SNP_NAME
and snapshot_type =
ad_file_sys_snapshots_pkg.get_snapshot_type(CURRENT_VIEW_SNP_NAME);
update AD_SNAPSHOT_BUGFIXES
set success_flag = p_flag
where bugfix_id = p_bug_id
and snapshot_id = l_snapshot_id;
'while trying to update success_flag to Y '||
' in AD_SNAPSHOT_BUGFIXES '||
'for patch_run_id = ' || p_patch_run_id);
no_update := TRUE;
if (no_update = FALSE) then
begin
update AD_SNAPSHOT_BUGFIXES
set success_flag = p_flag
where bugfix_id = p_bug_id
and snapshot_id = l_snapshot_id;
'while trying to update success_flag to N '||
' in AD_SNAPSHOT_BUGFIXES '||
'for patch_run_id = ' || p_patch_run_id);
no_update := FALSE;
patch_run_id, which would be selected via another layer of sql report that
the user would run to list all patch_run_ids for a given patch_name,
appl_top_id and a date.
*****************************************************************************/
procedure mark_patch_succ(p_patch_run_id in NUMBER ,
p_appl_top_id in number,
p_release_name in varchar2,
p_flag in varchar2,
p_reason_text in varchar2)
is
begin
begin
update AD_PATCH_RUNS
set success_flag = p_flag, failure_comments = p_reason_text
where patch_run_id = p_patch_run_id;
'while trying to update success_flag '||
'in AD_PATCH_RUNS '||
'for patch_run_id = ' || p_patch_run_id);
update AD_PATCH_RUN_BUGS
set success_flag = p_flag, failure_comments = p_reason_text
where patch_run_id = p_patch_run_id;
'while trying to update success_flag '||
'in AD_PATCH_RUN_BUGS '||
'for patch_run_id = ' || p_patch_run_id);
select success_flag into l_flag
from AD_PATCH_RUN_BUGS
where patch_run_id = p_patch_run_id
and success_flag = 'N';
- update AD_PATCH_RUN_BUGS for the specified patch run and bug
- Also update AD_PATCH_RUNS in the following cases:
- if updating bug to successful and all other bugs for this patch run
are successful and patch run marked as unsuccessful, update patch run
to successful.
- if updating bug to unsuccessful and patch run marked
as successful, update patch run to unsuccessful.
- cascade update AD_BUGS as described above based on values in
AD_PATCH_RUN_BUGS.
*************************************************************************/
procedure mark_bug_succ(p_patch_run_id in NUMBER ,
p_appl_top_id in number,
p_release_name in varchar2,
p_bug_number in varchar2,
p_flag in varchar2,
p_reason_text in varchar2)
is
l_bug_id ad_bugs.bug_id%TYPE;
select bug_id into l_bug_id
from AD_BUGS
where bug_number = p_bug_number
and aru_release_name = p_release_name;
update ad_patch_runs for the specified patch run and bug.
*/
begin
update AD_PATCH_RUN_BUGS
set success_flag = p_flag, failure_comments = p_reason_text
where patch_run_id = p_patch_run_id and
bug_id = l_bug_id;
'while trying to update case I success_flag '||
'in AD_PATCH_RUNS '||
'for patch_run_id = ' || p_patch_run_id);
update ad_patch_runs in the following manner.
1.if updating bug to successful and all other bugs for this patch run
are successful and patch run marked as unsuccessful, update patch run
to successful.
*/
begin
select success_flag into l_patch_succ
from AD_PATCH_RUNS
where patch_run_id = p_patch_run_id;
update AD_PATCH_RUNS
set success_flag = p_flag, failure_comments = p_reason_text
where patch_run_id = p_patch_run_id;
'while trying to update case II success_flag '||
'in AD_PATCH_RUNS '||
'for patch_run_id = ' || p_patch_run_id);
update AD_PATCH_RUNS
set success_flag = p_flag, failure_comments = p_reason_text
where patch_run_id = p_patch_run_id;
'while trying to update case III success_flag '||
'in AD_PATCH_RUNS '||
'for patch_run_id = ' || p_patch_run_id);
select s.snapshot_id into l_snapshot_id
from AD_SNAPSHOTS s, AD_APPL_TOPS a,
FND_PRODUCT_GROUPS fpg
where s.snapshot_name = GLOBAL_VIEW_SNP_NAME
and s.snapshot_type =
ad_file_sys_snapshots_pkg.get_snapshot_type(GLOBAL_VIEW_SNP_NAME)
and s.appl_top_id = a.appl_top_id
and a.name = 'GLOBAL'
and a.appl_top_type = 'G'
and a.applications_system_name = fpg.applications_system_name
and fpg.product_group_id=1;
select snapshot_id into l_snapshot_id
from AD_SNAPSHOTS
where appl_top_id = p_appl_top_id
and snapshot_name = CURRENT_VIEW_SNP_NAME
and snapshot_type =
ad_file_sys_snapshots_pkg.get_snapshot_type(CURRENT_VIEW_SNP_NAME);
select bug_id into l_bug_id
from AD_BUGS
where bug_number = p_bug_number
and aru_release_name = p_release_name;
select ad_bugs_s.nextval into l_bug_id from dual;
insert into AD_BUGS
(bug_id, bug_number,
creation_date, aru_release_name,
last_update_date, last_updated_by,created_by)
values(l_bug_id,
p_bug_number,
sysdate,
p_release_name,
sysdate,
-1,
-1);
'Attempting to insert a duplicate record '||
'into AD_BUGS for bug_number = '||
p_bug_number || ' and release '||
p_release_name);
'while trying to insert new record '||
'into AD_BUGS for bug_number = '||
p_bug_number || ' and release '||
p_release_name);
select snapshot_bug_id into l_snapshot_bug_id
from AD_SNAPSHOT_BUGFIXES
where bugfix_id = l_bug_id
and snapshot_id = l_snapshot_id;
insert into AD_SNAPSHOT_BUGFIXES
(snapshot_bug_id,
snapshot_id,
bugfix_id,
bug_status,
success_flag,
creation_date,
last_update_date,
last_updated_by,
created_by)
values (ad_snapshot_bugfixes_s.nextval,
l_snapshot_id,
l_bug_id,
p_bug_status,
'Y',
sysdate,
sysdate,
-1,
-1);
'Attempting to insert a duplicate record '||
'into AD_SNAPSHOT_BUGFIXES for bug_number = '||
p_bug_number || ' and release '||
p_release_name);
'while trying to insert new record '||
'into AD_SNAPSHOT_BUGFIXES for bug_number = '||
p_bug_number || ' and release '||
p_release_name);
update AD_SNAPSHOT_BUGFIXES
set BUG_STATUS = p_bug_status, success_flag = 'Y', last_updated_by = -1,
last_update_date = sysdate
where bugfix_id = l_bug_id and
snapshot_id = l_snapshot_id;
'while trying to update bug_status '||
'in AD_SNAPSHOT_BUGFIXES '||
'for bug_id = ' || l_bug_id);
select decode(upper(p_appl_top_type),'','R',
upper(p_appl_top_type))
into l_appl_top_type
from dual;
select applications_system_name into l_app_sys_name
from FND_PRODUCT_GROUPS;
select appl_top_id into l_appl_top_id
from AD_APPL_TOPS
where name = p_appl_top_name
and appl_top_type = l_appl_top_type
and applications_system_name = l_app_sys_name;