[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MEANING INTO l_meaning FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'CP_PHASE_CODE' AND
LOOKUP_CODE = p_phase_code;
SELECT MEANING INTO l_meaning FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'CP_STATUS_CODE' AND
LOOKUP_CODE = p_status_code;
l_days_cond := 'and r.last_update_date >= sysdate - :BIND_HISTORY ';
l_sql_stmt := 'select
view_type.value AS VIEWBY,
view_type.ID AS VIEWBYID,
BIS_RSG_PMV_REPORT_PKG.duration(avg(avg_run_time)) AS BIS_TIME,
BIS_RSG_PMV_REPORT_PKG.duration_str(avg(avg_run_time)) AS BIS_REQUEST_REFRESH_TIME,
BIS_RSG_PMV_REPORT_PKG.duration(max(max_run_time)) AS BIS_MAXIMUM,
BIS_RSG_PMV_REPORT_PKG.duration_str(max(max_run_time)) AS BIS_MAX,
BIS_RSG_PMV_REPORT_PKG.duration(min(min_run_time)) AS BIS_MINIMUM,
BIS_RSG_PMV_REPORT_PKG.duration_str(min(min_run_time)) AS BIS_MIN,
sum(num_runs) AS BIS_RUN,
sum(space_use)/(1024*1024) AS BIS_RS_TOTAL_SPACE_OCCUPIED,
NULL
BISREPORTURL ';
l_sql_stmt := 'select
view_type.value AS VIEWBY,
view_type.ID AS VIEWBYID,
BIS_RSG_PMV_REPORT_PKG.duration(avg_run_time) AS BIS_TIME,
BIS_RSG_PMV_REPORT_PKG.duration_str(avg_run_time) AS BIS_REQUEST_REFRESH_TIME,
BIS_RSG_PMV_REPORT_PKG.duration(max_run_time) AS BIS_MAXIMUM,
BIS_RSG_PMV_REPORT_PKG.duration_str(max_run_time) AS BIS_MAX,
BIS_RSG_PMV_REPORT_PKG.duration(min_run_time) AS BIS_MINIMUM,
BIS_RSG_PMV_REPORT_PKG.duration_str(min_run_time) AS BIS_MIN,
num_runs AS BIS_RUN,
space_use/(1024*1024) AS BIS_RS_TOTAL_SPACE_OCCUPIED,
nvl2(space_use,
''pFunctionName=BIS_BIA_RSG_SPACE_DET_PGE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',
null)
BISREPORTURL ';
select agg_runs.request_set_id,
agg_runs.request_set_type,
min_run_time,
max_run_time,
Num_runs,
avg_run_time
from
(select request_set_id,request_set_type
,Min(completion_date-start_date) min_run_time
,Max(completion_date-start_date) max_run_time
,avg(completion_date- start_date) avg_run_time
,R.REQUEST_SET_TYPE rstype
from
bis_rs_run_history r
where
r.phase_code =''C'' AND (STATUS_CODE=''G''
or STATUS_CODE=''C'') ';
l_sql_stmt:= l_sql_stmt || '(Select request_set_id,request_set_type
,count(r.request_set_id) Num_runs
from
bis_rs_run_history r, bis_rs_names_v v
where
r.phase_code =''C''
AND v.id = r.request_set_id
AND r.STATUS_CODE<>''X'' ';
(select tmp.srid srid, sum(tmp.object_space_usage) space_use,tmp.rsid rsid, tmp.rstype rstype
from
(select distinct object_name, object_type, object_space_usage,p.set_request_id srid,
Latestreq.request_set_id rsid,
Latestreq.request_set_type rstype from bis_obj_refresh_history o,
bis_rs_prog_run_history p,
(select max(request_id) maxid, request_set_id,request_set_type
from bis_rs_run_history WHERE PHASE_CODE=''C'' AND (STATUS_CODE=''C''
OR STATUS_CODE =''G'')
group by request_set_id,request_set_type) Latestreq
where
o.prog_request_id = p.request_id
and p.set_request_id = Latestreq.maxid) tmp
group by tmp.srid,tmp.rsid, tmp.rstype ) total_space ';
l_sql_stmt := 'select B.request_id AS VIEWBY,
B.request_id AS VIEWBYID,
BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ(start_date) AS BIS_RS_START_TIME,
BIS_RSG_PMV_REPORT_PKG.duration_str(completion_date - start_date) AS BIS_RS_DURATION,
BIS_RSG_PMV_REPORT_PKG.get_meaning(STATUS_CODE,PHASE_CODE) AS BIS_RS_STATUS,
''pFunctionName=BIS_BIA_RSG_SUB_REQS_PGE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' BISREPORTURL
,BIS_RSG_PMV_REPORT_PKG.returnLogUrl( B.request_id,2) BISLOGFILEURL
from BIS_RS_RUN_HISTORY B, FND_REQUEST_SETS_VL F
where F.request_set_id = B.request_set_id
AND PHASE_CODE = ''C'' and STATUS_CODE <>''X'' ';
l_sql_stmt:= l_sql_stmt||'and B.last_update_date >= sysdate - :BIND_HISTORY ';
l_prog_cond2:= 'and exists (Select 1 from bis_rs_prog_run_history where stage_request_id=B.request_id and program_id in(' || rs_prog_req ||'))';
l_sql_stmt := 'select
rid BIS_RS_SUB_REQS_ID,
rname BIS_RS_REQUEST_NAME
, BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ(started) BIS_RS_START_TIME
,dur BIS_RS_DURATION
,BIS_RSG_PMV_REPORT_PKG.get_meaning(tmp.STATUS,''C'') BIS_RS_STATUS
,LOG_MESSAGE BIS_LOG_MESSAGE
,decode(url,'' '',null,url) BISREPORTURL
,BIS_RSG_PMV_REPORT_PKG.returnLogUrl(rid,1) BISLOGFILEURL
FROM ';
'(select R.request_id rid,F.user_request_set_name rname,
R.start_date started,
BIS_RSG_PMV_REPORT_PKG.Duration_str(R.completion_date-R.start_date) dur,
STATUS_CODE status, R.completion_text LOG_MESSAGE, '' '' url
from bis_rs_run_history R,fnd_request_sets_vl F
where F.request_set_id = R.request_set_id AND R.PHASE_CODE=''C'' ' ||
l_type_cond ||l_setid_cond ||l_runid_cond1
||' union
select B.request_id rid, F.user_stage_name rname,
B.start_date started,
BIS_RSG_PMV_REPORT_PKG.Duration_str(B.completion_date-B.start_date) dur,
B.STATUS_CODE status,B.completion_text LOG_MESSAGE,'' '' url
from
bis_rs_stage_run_history B, fnd_request_set_stages_vl F,bis_rs_run_history R
where F.request_set_stage_id = B.stage_id
and R.request_id = B.set_request_id AND R.PHASE_CODE=''C'' '
||l_type_cond||l_setid_cond||l_runid_cond2||l_stage_cond1||l_prog_cond2
||' union
Select B.request_id rid, F.user_concurrent_program_name rname, B.start_date started,
BIS_RSG_PMV_REPORT_PKG.Duration_str(B.completion_date-B.start_date) dur,
B.STATUS_CODE status,B.completion_text LOG_MESSAGE,
decode(num_obj,0,
''pFunctionName=BIS_BIA_RSG_RPT_ERR_MSG_PG&formErrorType=NO_OBJECT'',
''pFunctionName=BIS_BIA_RSG_REQ_DETAILS_PGE&BIS_RS_SUB_REQS_ID=BIS_RS_SUB_REQS_ID&BISRSSTAGE=''||B.stage_request_id||''&BISRSPROG=''||B.program_id||''&pParamIds=Y''
) url
from
bis_rs_prog_run_history B, fnd_concurrent_programs_vl F,
bis_rs_run_history R,bis_rs_stage_run_history S,
(select distinct prog_request_id prid, 1 num_obj from bis_obj_refresh_history
union select request_id prid,0 num_obj from bis_rs_prog_run_history where request_id not
in(select prog_request_id from bis_obj_refresh_history)) count_obj
where F.concurrent_program_id = B.program_id
and F.APPLICATION_ID = B.Prog_app_id
and R.request_id=B.set_request_id
and S.request_id=B.stage_request_id
and B.request_id=count_obj.prid
AND R.PHASE_CODE=''C'' '
||l_type_cond||l_setid_cond||l_runid_cond2||l_stage_cond2
||l_prog_cond ||
') tmp ';
l_sql_stmt:='select distinct object_name BIS_REQUEST_OBJECT_NAME,
--BIS_REQUESTSET_VIEWHISTORY.get_bis_lookup_meaning( ''BIS_OBJECT_TYPE'',O.OBJECT_TYPE) BIS_REQUEST_OBJECT_TYPE,
CASE O.OBJECT_TYPE WHEN ''MV_LOG'' THEN BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning( ''BIS_OBJECT_TYPE_RPT'',O.OBJECT_TYPE)
WHEN ''BSC_CUSTOM_KPI'' THEN BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning( ''BIS_OBJECT_TYPE_RPT'',O.OBJECT_TYPE)
ELSE BIS_REQUESTSET_VIEWHISTORY.get_bis_lookup_meaning( ''BIS_OBJECT_TYPE'',O.OBJECT_TYPE) END BIS_REQUEST_OBJECT_TYPE,
0 BIS_OBJECT_ROW_COUNT,
decode(refresh_type,''INCR'',
BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning( ''BIS_REQUEST_SET_TYPE'',''INCR_LOAD''),
''ANALYZED'',
BIS_REQUESTSET_VIEWHISTORY.get_bis_lookup_meaning( ''BIS_REFRESH_MODE'',''ANALYZED''),
''CONSIDER_REFRESH'',
BIS_REQUESTSET_VIEWHISTORY.get_bis_lookup_meaning( ''BIS_REFRESH_MODE'',''CONSIDER_REFRESH''),
BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning( ''BIS_REQUEST_SET_TYPE'',''INIT_LOAD'')) BIS_REQUEST_REFRESH_TYPE,
null BIS_RS_STATUS,
decode(O.OBJECT_TYPE,''MV_LOG'',NULL,''BSC_CUSTOM_KPI'',NULL,'||''''||
'pFunctionName=BIS_BIA_RSG_DEPENDENCIES_ALONE'
||'&requestType=RSGReport&ObjType=''||o.object_type||''&ObjName=BIS_REQUEST_OBJECT_NAME''
) BISREPORTURL
from bis_obj_refresh_history O,bis_rs_prog_run_history P,bis_rs_run_history R,
bis_rs_stage_run_history S
where O.prog_request_id = P.request_id
AND R.REQUEST_ID = S.SET_REQUEST_ID
AND S.REQUEST_ID = P.STAGE_REQUEST_ID
AND R.PHASE_CODE = ''C'' ';
l_sql_stmt:='SELECT Distinct OBJECT_NAME BIS_REQUEST_OBJECT_NAME,
--BIS_REQUESTSET_VIEWHISTORY.get_bis_lookup_meaning( ''BIS_OBJECT_TYPE'',O.OBJECT_TYPE) BIS_REQUEST_OBJECT_TYPE,
CASE O.OBJECT_TYPE WHEN ''MV_LOG'' THEN BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning( ''BIS_OBJECT_TYPE_RPT'',O.OBJECT_TYPE)
WHEN ''BSC_CUSTOM_KPI'' THEN BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning( ''BIS_OBJECT_TYPE_RPT'',O.OBJECT_TYPE)
ELSE BIS_REQUESTSET_VIEWHISTORY.get_bis_lookup_meaning( ''BIS_OBJECT_TYPE'',O.OBJECT_TYPE) END BIS_REQUEST_OBJECT_TYPE,
O.TABLESPACE_NAME BIS_TABLESPACE_NAME,
-- next two columns are not in use anymore..
0 BIS_TABLESPACE_SIZE,
0 BIS_TABLESPACE_FREE_SPACE,
OBJECT_SPACE_USAGE/(1024*1024) BIS_RS_TOTAL_SPACE_OCCUPIED,
object_row_count BIS_OBJECT_ROW_COUNT,
(OBJECT_SPACE_USAGE/TOTAL_SPACE.BYTES)*100 BIS_RS_PCT_SPACE_USED,
decode(O.OBJECT_TYPE,''MV_LOG'',NULL,''BSC_CUSTOM_KPI'',NULL,'||''''||
'pFunctionName=BIS_BIA_RSG_DEPENDENCIES_ALONE'
||'&requestType=RSGReport&ObjType=''||o.object_type||''&ObjName=BIS_REQUEST_OBJECT_NAME''
) BISREPORTURL
from BIS_OBJ_REFRESH_HISTORY O,
(select max(request_id) maxid, request_set_id rsid,request_set_type rstype
from bis_rs_run_history WHERE PHASE_CODE=''C'' AND (STATUS_CODE=''C''
OR STATUS_CODE =''G'')
group by request_set_id,request_set_type) Latestreq,
bis_rs_prog_run_history P, bis_rs_stage_run_history S,
(select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files
group by TABLESPACE_NAME) TOTAL_SPACE
where
Latestreq.maxid=S.set_request_id
and S.request_id= P.stage_request_id
and P.request_id=O.prog_request_id
AND TOTAL_SPACE.TABLESPACE_NAME=O.TABLESPACE_NAME ';
l_sql_stmt:='select DB_TS.TABLESPACE_NAME BIS_TS_NAME_PARAM,
TOTAL_SPACE.BYTES/(1024*1024) BIS_TABLESPACE_SIZE,
INITIAL_EXTENT/(1024*1024) BIS_TS_INIT_EXTENT,
NEXT_EXTENT/(1024*1024) BIS_TS_NEXT_EXTENT,
MAX_EXTENTS BIS_TS_MAX_EXTENT,
free_space.BYTES/(1024*1024) BIS_TABLESPACE_FREE_SPACE
from dba_tablespaces DB_TS,
(select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files
group by TABLESPACE_NAME) TOTAL_SPACE,
(select TABLESPACE_NAME, sum(BYTES) BYTES from dba_free_space
group by TABLESPACE_NAME) FREE_SPACE
WHERE free_space.tablespace_name=db_ts.tablespace_name
AND total_space.tablespace_name=db_ts.tablespace_name
AND db_ts.contents = ''PERMANENT'' ';
Select max(stage_request_id) from bis_rs_prog_run_history where program_id=pid
and set_request_id= srid;
select max(request_id) maxid
from bis_rs_run_history WHERE PHASE_CODE='C' AND (STATUS_CODE='C'
OR STATUS_CODE ='G') and request_set_id= rsid
group by request_set_id,request_set_type;
select 1 from dual where exists
(select r.request_set_id from bis_rs_run_history r, bis_rs_prog_run_history p,
bis_obj_refresh_history o
where
r.phase_code ='C' AND (r.STATUS_CODE='C' OR r.STATUS_CODE ='G')
and p.set_request_id=r.Request_id
and p.request_id = o.prog_request_id and r.request_set_id= rsid);