[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fnd_global.user_id into v_userid from dual;
PROCEDURE insert_metric_internal (
p_metric_name in varchar2,
p_context in varchar2,
p_value in number)
IS
v_userid number;
insert into fnd_oam_chart_metrics (metric_short_name, metric_context,
value, last_updated_by, last_update_date,
last_update_login, created_by, creation_date)
values (p_metric_name, p_context, p_value,
v_userid, sysdate, v_userid, 0, sysdate);
END insert_metric_internal;
PROCEDURE update_metric_internal (
p_metric_name in varchar2,
p_context in varchar2,
p_value in number)
IS
v_userid number;
select metric_short_name into name
from fnd_oam_chart_metrics
where metric_short_name = p_metric_name
and metric_context = p_context;
update fnd_oam_chart_metrics
set value = p_value,
last_updated_by = v_userid,
last_update_date = sysdate,
last_update_login = v_userid
where
metric_short_name = p_metric_name
and metric_context = p_context;
insert into fnd_oam_chart_metrics (metric_short_name, metric_context,
value, last_updated_by, last_update_date,
last_update_login, created_by, creation_date)
values (p_metric_name, p_context, p_value,
v_userid, sysdate, v_userid, 0, sysdate);
END update_metric_internal;
PROCEDURE delete_metric_internal (
p_metric_name in varchar2)
IS
BEGIN
-- delete the entry
delete from fnd_oam_chart_metrics
where metric_short_name = p_metric_name;
END delete_metric_internal;
update_req_status_metric('REQ_RUNNING');
update_req_status_metric('REQ_PENDING');
update_req_status_metric('REQ_COMPLETED');
update_req_status_metric('REQ_WAITING_ON_LOCK');
update_req_status_metric('REQ_INACTIVE');
PROCEDURE update_req_status_metric(p_metric_name in varchar2)
IS
ct_running number;
select count(*) into ct_running
from fnd_concurrent_requests
where status_code = 'R';
update_metric_internal(p_metric_name, '0', ct_running);
select count(rv.Request_ID) into ct_pending
from Fnd_amp_requests_v rv,
Fnd_lookups l
Where rv.phase_code = 'P'
and l.meaning = rv.phase
and l.lookup_code = 'P'
and l.lookup_type = 'CP_PHASE_CODE';
select count(distinct(R.Request_ID)) into ct_pending
from Fnd_Concurrent_Programs_vl CP,
Fnd_User U,
Fnd_Concurrent_Requests R,
Fnd_Responsibility_Tl RES,
Fnd_Application A,
Fnd_amp_requests_v rv,
Fnd_lookups l
Where rv.phase_code = 'P'
and l.meaning = rv.phase
and l.lookup_code = 'P'
and l.lookup_type = 'CP_PHASE_CODE'
And CP.Application_ID = rv.Program_Application_ID
And CP.Concurrent_Program_ID = rv.Concurrent_Program_ID
and R.request_id = rv.request_id
and rv.Program_Application_ID = R.Program_Application_ID
And rv.Concurrent_Program_ID = R.Concurrent_Program_ID
And A.Application_ID = rv.Program_Application_ID
And U.User_ID = R.Requested_By
And RES.application_id = R.responsibility_application_id
AND RES.language(+)=USERENV('LANG')
And RES.responsibility_id = R.responsibility_id;
update_metric_internal(p_metric_name, '0', ct_pending);
select count(*) into ct_completed
from fnd_concurrent_requests
where phase_code = 'C' and (sysdate - actual_completion_date)*1440 <= 60;
update_metric_internal(p_metric_name, '0', ct_completed);
select count(*) into ct_waiting_on_lock
from fnd_concurrent_requests r, GV$SESSION WS
where r.phase_code = 'R'
and r.oracle_session_id = WS.AUDSID
and WS.LOCKWAIT IS NOT NULL;
update_metric_internal(p_metric_name, '0', ct_waiting_on_lock);
select count(rv.Request_ID) into ct_inactive
From Fnd_amp_requests_v rv,
Fnd_lookups l
Where rv.phase_code = 'P'
and l.meaning = rv.phase
and l.lookup_code = 'I'
and l.lookup_type = 'CP_PHASE_CODE';
select count(distinct(R.Request_ID)) into ct_inactive
From Fnd_Concurrent_Programs_vl CP,
Fnd_User U,
Fnd_Concurrent_Requests R,
Fnd_Responsibility_Tl RES,
Fnd_Application A,
Fnd_amp_requests_v rv,
Fnd_lookups l
Where rv.phase_code = 'P'
and l.meaning = rv.phase
and l.lookup_code = 'I'
and l.lookup_type = 'CP_PHASE_CODE'
And CP.Application_ID = rv.Program_Application_ID
And CP.Concurrent_Program_ID = rv.Concurrent_Program_ID
and R.request_id = rv.request_id
and rv.Program_Application_ID = R.Program_Application_ID
And rv.Concurrent_Program_ID = R.Concurrent_Program_ID
And A.Application_ID = rv.Program_Application_ID
And U.User_ID = R.Requested_By
And RES.application_id = R.responsibility_application_id
AND RES.language(+)=USERENV('LANG')
And RES.responsibility_id = R.responsibility_id;
update_metric_internal(p_metric_name, '0', ct_inactive);
END update_req_status_metric;
SELECT count(*) count, status_code
FROM fnd_concurrent_requests
WHERE status_code IN ('C', 'G', 'E', 'X' )
AND ((sysdate - actual_completion_date) * (1440)) <= 60
GROUP BY status_code;
update_metric_internal('COMPLETED_REQ_ERROR', '0', ct_error);
update_metric_internal('COMPLETED_REQ_SUCCESSFUL', '0', ct_succ);
update_metric_internal('COMPLETED_REQ_TERMINATED', '0', ct_term);
update_metric_internal('COMPLETED_REQ_WARNING', '0', ct_warn);
SELECT count(*) count, status_code
FROM fnd_concurrent_requests
WHERE (status_code IN ('I', 'Q')
AND requested_start_date <= sysdate
AND phase_code = 'P'
AND hold_flag = 'N')
GROUP BY status_code;
SELECT count(*) into ct_scheduled
FROM fnd_concurrent_requests
WHERE (phase_code = 'P' AND hold_flag = 'N')
AND ( (status_code = 'P' )
OR (status_code IN( 'I', 'Q')
AND requested_start_date > sysdate ));
update_metric_internal('PENDING_REQ_NORMAL', '0', ct_normal);
update_metric_internal('PENDING_REQ_STANDBY', '0', ct_standby);
update_metric_internal('PENDING_REQ_SCHEDULED', '0', ct_scheduled);
select count(*) into ct_10min
from fnd_concurrent_requests
where ((sysdate - actual_start_date) * (1440)) <= 10
and status_code in ('R');
select count(*) into ct_60min
from fnd_concurrent_requests
where ((sysdate - actual_start_date) * (1440)) between 10 and 60
and status_code in ('R');
select count(*) into ct_long
from fnd_concurrent_requests
where ((sysdate - actual_start_date) * (1440)) >= 60
and status_code in ('R');
update_metric_internal('RUNNING_REQ_10MIN', '0', ct_10min);
update_metric_internal('RUNNING_REQ_60MIN', '0', ct_60min);
update_metric_internal('RUNNING_REQ_LONG', '0', ct_long);
select count(*) count, user_name
from fnd_concurrent_requests, fnd_user
where requested_by = user_id and status_code = 'R'
group by user_name;
delete_metric_internal('RUNNING_REQ_USER');
insert_metric_internal('RUNNING_REQ_USER', req.user_name, req.count);
select count(rv.Request_ID) count, user_name
from Fnd_amp_requests_v rv,
Fnd_lookups l
Where rv.phase_code = 'P'
and l.meaning = rv.phase
and l.lookup_code = 'P'
and l.lookup_type = 'CP_PHASE_CODE'
group by user_name;
delete_metric_internal('PENDING_REQ_USER');
insert_metric_internal('PENDING_REQ_USER', req.user_name, req.count);
select count(*) count, application_name
from fnd_concurrent_requests r, fnd_application_vl v
where r.program_application_id = v.application_id and status_code = 'R'
group by application_name;
delete_metric_internal('RUNNING_REQ_APPLICATION');
insert_metric_internal('RUNNING_REQ_APPLICATION', req.application_name, req.count);
select count(rv.Request_ID) count, application_name
from Fnd_amp_requests_v rv,
Fnd_lookups l
Where rv.phase_code = 'P'
and l.meaning = rv.phase
and l.lookup_code = 'P'
and l.lookup_type = 'CP_PHASE_CODE'
group by application_name;
delete_metric_internal('PENDING_REQ_APPLICATION');
insert_metric_internal('PENDING_REQ_APPLICATION', req.application_name, req.count);
select count(*) count, responsibility_name
from fnd_concurrent_requests r, fnd_responsibility_vl v
where r.responsibility_application_id = v.application_id
and r.responsibility_id = v.responsibility_id and status_code = 'R'
group by responsibility_name;
delete_metric_internal('RUNNING_REQ_RESP');
insert_metric_internal('RUNNING_REQ_RESP', req.responsibility_name, req.count);
select count(rv.Request_ID) count, responsibility_name
from Fnd_amp_requests_v rv,
Fnd_lookups l
Where rv.phase_code = 'P'
and l.meaning = rv.phase
and l.lookup_code = 'P'
and l.lookup_type = 'CP_PHASE_CODE'
group by responsibility_name;
delete_metric_internal('PENDING_REQ_RESP');
insert_metric_internal('PENDING_REQ_RESP', req.responsibility_name, req.count);
PROCEDURE update_run_req_mgr_metric(p_queue_application_id in number,
p_concurrent_queue_name in varchar2,
p_user_concurrent_queue_name in varchar2)
IS
ct_running number := 0;
select count(*) into ct_running
from fnd_concurrent_worker_requests r
where queue_application_id = p_queue_application_id
and concurrent_queue_name = p_concurrent_queue_name
and status_code = 'R';
update_metric_internal('RUNNING_REQ_MANAGER', p_user_concurrent_queue_name, ct_running);
END update_run_req_mgr_metric;
PROCEDURE update_pend_req_mgr_metric(p_queue_application_id in number,
p_concurrent_queue_name in varchar2,
p_user_concurrent_queue_name in varchar2)
IS
ct_pending number := 0;
select count(*) into ct_pending
from fnd_concurrent_worker_requests r
where queue_application_id = p_queue_application_id
and concurrent_queue_name = p_concurrent_queue_name
and status_code = 'I' and hold_flag <> 'Y'
and requested_start_date <= sysdate;
update_metric_internal('PENDING_REQ_MANAGER', p_user_concurrent_queue_name, ct_pending);
END update_pend_req_mgr_metric;
PROCEDURE update_process_mgr_metric(p_queue_application_id in number,
p_concurrent_queue_name in varchar2,
p_user_concurrent_queue_name in varchar2)
IS
ct_process number :=0;
select running_processes into ct_process
from fnd_concurrent_queues_vl
where application_id = p_queue_application_id
and concurrent_queue_name = p_concurrent_queue_name;
update_metric_internal('RUNNING_PROCESS_MANAGER', p_user_concurrent_queue_name, ct_process);
update_metric_internal('RUNNING_PROCESS_MANAGER', p_user_concurrent_queue_name, 0);
END update_process_mgr_metric;
select application_id, concurrent_queue_name, user_concurrent_queue_name
from fnd_concurrent_queues_vl
where manager_type = 1;
delete_metric_internal('RUNNING_REQ_MANAGER');
delete_metric_internal('RUNNING_PROCESS_MANAGER');
update_run_req_mgr_metric(mgr.application_id, mgr.concurrent_queue_name, mgr.user_concurrent_queue_name);
update_process_mgr_metric(mgr.application_id, mgr.concurrent_queue_name, mgr.user_concurrent_queue_name);
select application_id, concurrent_queue_name, user_concurrent_queue_name
from fnd_concurrent_queues_vl
where manager_type = 1;
delete_metric_internal('PENDING_REQ_MANAGER');
update_pend_req_mgr_metric(mgr.application_id, mgr.concurrent_queue_name, mgr.user_concurrent_queue_name);
SELECT r.requested_by user_id,
count(r.actual_completion_date) count,
sum(r.actual_completion_date-nvl(r.actual_start_date,r.requested_start_date))*24*3600 runtime --in seconds
FROM fnd_concurrent_requests r
WHERE r.phase_code = 'C'
and (sysdate - r.actual_completion_date) <= 1
group by r.requested_by;
SELECT r.requested_by user_id,
count(r.actual_completion_date) count,
sum(r.actual_completion_date-nvl(r.actual_start_date,r.requested_start_date))*24*3600 runtime --in seconds
FROM fnd_concurrent_requests r
WHERE r.phase_code = 'C'
and (sysdate - r.actual_completion_date) <= 7
group by r.requested_by;
SELECT r.requested_by user_id,
count(r.actual_completion_date) count,
sum(r.actual_completion_date-nvl(r.actual_start_date,r.requested_start_date))*24*3600 runtime --in seconds
FROM fnd_concurrent_requests r
WHERE r.phase_code = 'C'
and (sysdate - r.actual_completion_date) <= 31
group by r.requested_by;
delete from fnd_oam_cpstats_user where stats_interval = 24; --24 hours
insert_stats_user(stat.user_id, 24, stat.count, stat.runtime);
delete from fnd_oam_cpstats_user where stats_interval = 168; -- 7days = 168 hours
insert_stats_user(stat.user_id, 168, stat.count, stat.runtime);
delete from fnd_oam_cpstats_user where stats_interval = 744; --31 days = 744 hours
insert_stats_user(stat.user_id, 744, stat.count, stat.runtime);
SELECT r.program_application_id app_id, r.concurrent_program_id prog_id,
round(sum(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) total, -- in seconds
round(avg(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) ave, -- in seconds
round(min(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) minimum, -- in seconds
round(max(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) maximum, -- in seconds
count(*) count
FROM fnd_concurrent_requests r
WHERE r.phase_code = 'C'
and r.actual_completion_date is not null
and r.actual_start_date is not null
and (sysdate - r.actual_completion_date) <= 1
GROUP BY r.program_application_id, r.concurrent_program_id;
SELECT r.program_application_id app_id, r.concurrent_program_id prog_id,
round(sum(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) total,
round(avg(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) ave,
round(min(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) minimum,
round(max(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) maximum,
count(*) count
FROM fnd_concurrent_requests r
WHERE r.phase_code = 'C'
and r.actual_completion_date is not null
and r.actual_start_date is not null
and (sysdate - r.actual_completion_date) <= 7
GROUP BY r.program_application_id, r.concurrent_program_id;
SELECT r.program_application_id app_id, r.concurrent_program_id prog_id,
round(sum(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) total,
round(avg(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) ave,
round(min(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) minimum,
round(max(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) maximum,
count(*) count
FROM fnd_concurrent_requests r
WHERE r.phase_code = 'C'
and r.actual_completion_date is not null
and r.actual_start_date is not null
and (sysdate - r.actual_completion_date) <= 31
GROUP BY r.program_application_id, r.concurrent_program_id;
delete from fnd_oam_cpstats_program where stats_interval = 24;
insert_stats_program(stat.app_id, stat.prog_id, 24,
stat.total, stat.ave, stat.minimum, stat.maximum, stat.count);
delete from fnd_oam_cpstats_program where stats_interval = 168; -- 7 days = 168 hours
insert_stats_program(stat.app_id, stat.prog_id, 168,
stat.total, stat.ave, stat.minimum, stat.maximum, stat.count);
delete from fnd_oam_cpstats_program where stats_interval = 744; -- 31 days = 744 hours
insert_stats_program(stat.app_id, stat.prog_id, 744,
stat.total, stat.ave, stat.minimum, stat.maximum, stat.count);
PROCEDURE insert_stats_user (
p_user_id in number,
p_stats_interval in varchar2,
p_comp_req_count in number,
p_total_runtime in number)
IS
v_userid number;
insert into fnd_oam_cpstats_user (user_id, stats_interval,
comp_req_count, total_runtime,
last_updated_by, last_update_date,
last_update_login, created_by, creation_date)
values (p_user_id, p_stats_interval, p_comp_req_count,
p_total_runtime,
v_userid, sysdate, v_userid, 0, sysdate);
END insert_stats_user;
PROCEDURE insert_stats_program (
p_app_id in number,
p_program_id in number,
p_stats_interval in varchar2,
p_total_runtime in number,
p_ave_tuntime in number,
p_min_tuntime in number,
p_max_tuntime in number,
p_times_run in number)
IS
v_userid number;
insert into fnd_oam_cpstats_program (application_id, program_id, stats_interval,
total_runtime, ave_runtime, min_runtime, max_runtime, times_run,
last_updated_by, last_update_date,
last_update_login, created_by, creation_date)
values (p_app_id, p_program_id, p_stats_interval, p_total_runtime,
p_ave_tuntime, p_min_tuntime, p_max_tuntime,
p_times_run,
v_userid, sysdate, v_userid, 0, sysdate);
END insert_stats_program;
select application_id, responsibility_id, responsibility_key
into appl_id, resp_id, resp_key
from fnd_responsibility
where responsibility_key = 'SYSTEM_ADMINISTRATOR';
select user_id, user_name
into user_id, user_name
from fnd_user
where user_name = 'SYSADMIN';