[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select option_value
from bis_request_set_options
where request_set_name = P_REQUEST_SET_NAME
and option_name='REFRESH_MODE';
select option_value
from bis_request_set_options
where request_set_name= P_REQUEST_SET_NAME
and option_name='ANALYZE_OBJECT';
select request_set_name from (select request_set_name --request set for dashboard that has this report
from bis_request_set_objects
where object_name in (
select Distinct obj.OBJECT_NAME
from bis_obj_dependency obj
where object_type in ('PAGE') and enabled_flag='Y'
start with
obj.depend_object_type ='REPORT'
and obj.depend_object_name=l_report_name
connect by prior obj.OBJECT_NAME=obj.depend_object_name
and prior obj.OBJECT_TYPE=obj.depend_object_TYPE)
and object_type ='PAGE'
union -- request set for report directly
select request_set_name from bis_request_set_objects
where object_name =l_report_name and object_type ='REPORT') where
BIS_BIA_RSG_PSTATE.get_refresh_mode(REQUEST_SET_NAME) <> 'ANAL';
SELECT REQUEST_SET_NAME FROM
(select request_set_name from bis_request_set_objects
where object_name =l_PAGE_name and object_type ='PAGE') WHERE
BIS_BIA_RSG_PSTATE.get_refresh_mode(REQUEST_SET_NAME) <> 'ANAL';
BIS_COLL_RS_HISTORY.update_terminated_rs;
debug('Error happened while trying to update the status of those request sets that were terminated by user. Report MAY show terminated request sets as running');
l_sql_stmt := 'select
DISP.value BIS_BIA_RSG_PAGE_NAME_DISPLAY,
BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ( LAST_SUCCESSFUL_REFRESH.Last_Refresh_Time ) BIS_BIA_RSG_PAGE_LSTREFDATE,
LAST_SUCCESSFUL_REFRESH.Last_Refresh_Duration BIS_BIA_RSG_PAGE_LAST_REFDUR,
CURRENT_RUN.Current_Status BIS_BIA_RSG_PAGE_STATUS,
BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ(CURRENT_RUN.Refresh_Start_Time) BIS_BIA_RSG_PAGE_REFSTARTTIME,
BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ(NEXT_SCHEDULED_REFRESH.REQUESTED_START_DATE) BIS_BIA_RSG_PAGE_NEXTREF,
decode('''||plan_url||''','' '',null,'''|| plan_text||''') BIS_BIA_RSG_PAGE_REFPLAN,
decode('''||plan_url||''','' '',null,'''||plan_url||''') BISREPORTURL
from
BIS_BIA_RSG_PAGE_STATUS_V DISP,
(
select OBJECT_NAME,
decode(Last_Refresh_Time,to_date(''01-01-1900'',''DD-MM-YYYY''),null,Last_Refresh_Duration) Last_Refresh_Duration,
BIS_BIA_RSG_PSTATE.sync_last_refresh_time(Last_Refresh_Time) Last_Refresh_Time
from
(
select OBJECTS.OBJECT_NAME,
BIS_BIA_RSG_PSTATE.duration(COMPLETION_DATE - START_DATE) Last_Refresh_Duration,
bis_submit_requestset.get_last_refreshdate(objects.object_type,null,objects.object_name) Last_Refresh_Time,
rank() over(partition by OBJECTS.OBJECT_NAME order by START_DATE desc) rk
FROM BIS_RS_RUN_HISTORY HISTORY, BIS_REQUEST_SET_OBJECTS OBJECTS
WHERE
'|| l_bind ||' AND
OBJECTS.OBJECT_TYPE = ''PAGE'' AND
BIS_BIA_RSG_PSTATE.get_refresh_mode(OBJECTS.REQUEST_SET_NAME) <> ''ANAL'' AND
OBJECTS.REQUEST_SET_NAME = HISTORY.REQUEST_SET_NAME AND
HISTORY.PHASE_CODE = ''C'' AND
(HISTORY.STATUS_CODE = ''C'' OR HISTORY.STATUS_CODE = ''G'')
)
where rk =1
)
LAST_SUCCESSFUL_REFRESH,
(
SELECT OBJECTS.OBJECT_NAME,
LOOKUPS.MEANING Current_Status,
min(START_DATE) Refresh_Start_Time
FROM BIS_RS_RUN_HISTORY HISTORY, BIS_REQUEST_SET_OBJECTS OBJECTS, FND_LOOKUPS LOOKUPS
WHERE
'|| l_bind ||' AND
OBJECTS.OBJECT_TYPE = ''PAGE'' AND
BIS_BIA_RSG_PSTATE.get_refresh_mode(OBJECTS.REQUEST_SET_NAME) <>''ANAL'' AND
OBJECTS.REQUEST_SET_NAME = HISTORY.REQUEST_SET_NAME AND
HISTORY.PHASE_CODE = ''R'' AND
HISTORY.PHASE_CODE = LOOKUPS.LOOKUP_CODE AND
LOOKUPS.LOOKUP_TYPE = ''BIS_RSG_PSTATE_RPT_LKP'' --modified for bug#5144541: rkumar
group by OBJECTS.OBJECT_NAME,LOOKUPS.MEANING
)
CURRENT_RUN,
(
SELECT
OBJECTS.OBJECT_NAME,
min(FND_CONC.REQUESTED_START_DATE) REQUESTED_START_DATE
from
BIS_REQUEST_SET_OBJECTS OBJECTS,
FND_CONCURRENT_REQUESTS FND_CONC,
FND_REQUEST_SETS FND
where
'|| l_bind ||' AND
OBJECTS.OBJECT_TYPE = ''PAGE'' AND
BIS_BIA_RSG_PSTATE.get_refresh_mode(OBJECTS.REQUEST_SET_NAME) <> ''ANAL'' AND
OBJECTS.REQUEST_SET_NAME = FND.REQUEST_SET_NAME AND
OBJECTS.SET_APP_ID = FND.APPLICATION_ID AND
to_char(FND.APPLICATION_ID) = FND_CONC.ARGUMENT1 AND
to_char(FND.REQUEST_SET_ID) = FND_CONC.ARGUMENT2 AND
FND_CONC.PHASE_CODE = ''P'' AND
FND_CONC.STATUS_CODE = ''I''
group by objects.object_name
)
NEXT_SCHEDULED_REFRESH
WHERE
DISP.TYPE=''PAGE'' AND --added for enh 4638578
bis_impl_dev_pkg.get_function_by_page(NEXT_SCHEDULED_REFRESH.object_name(+)) = DISP.ID AND
bis_impl_dev_pkg.get_function_by_page(CURRENT_RUN.object_name(+)) = DISP.ID AND
bis_impl_dev_pkg.get_function_by_page(LAST_SUCCESSFUL_REFRESH.object_name(+)) = DISP.ID
';
l_sql_stmt := ' select
DISP.value BIS_BIA_RSG_PAGE_NAME_DISPLAY,
BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ( LAST_SUCCESSFUL_REFRESH.Last_Refresh_Time ) BIS_BIA_RSG_PAGE_LSTREFDATE,
LAST_SUCCESSFUL_REFRESH.Last_Refresh_Duration BIS_BIA_RSG_PAGE_LAST_REFDUR,
CURRENT_RUN.Current_Status BIS_BIA_RSG_PAGE_STATUS,
BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ(CURRENT_RUN.Refresh_Start_Time) BIS_BIA_RSG_PAGE_REFSTARTTIME,
BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ(NEXT_SCHEDULED_REFRESH.REQUESTED_START_DATE) BIS_BIA_RSG_PAGE_NEXTREF,
decode('''||plan_url||''','' '',null,'''|| plan_text||''') BIS_BIA_RSG_PAGE_REFPLAN,
decode('''||plan_url||''','' '',null,'''||plan_url||''') BISREPORTURL
from
(SELECT ''Dummy'' JOIN_COL,id,value from BIS_BIA_RSG_PAGE_STATUS_V where TYPE=''REPORT''
AND ID=:CONTENT_NAME) DISP,
( select ''Dummy'' AS JOIN_COL,
decode(Last_Refresh_Time,to_date(''01-01-1900'',''DD-MM-YYYY''),null,Last_Refresh_Duration) Last_Refresh_Duration,
BIS_BIA_RSG_PSTATE.sync_last_refresh_time(Last_refresh_time) Last_Refresh_Time
from (SELECT Last_Refresh_Duration,
bis_submit_requestset.get_last_refreshdate(''REPORT'',null,:CONTENT_NAME) Last_refresh_time
from
(select BIS_BIA_RSG_PSTATE.duration(COMPLETION_DATE - START_DATE) Last_Refresh_Duration
FROM BIS_RS_RUN_HISTORY HISTORY WHERE
HISTORY.REQUEST_SET_NAME IN
(select request_set_name RS_NAME from (select request_set_name --request set for dashboard that has this report
from bis_request_set_objects where object_name in ( select Distinct obj.OBJECT_NAME
from bis_obj_dependency obj where object_type in (''PAGE'') and enabled_flag=''Y''
start with obj.depend_object_type =''REPORT'' and obj.depend_object_name=:CONTENT_NAME
connect by prior obj.OBJECT_NAME=obj.depend_object_name and
prior obj.OBJECT_TYPE=obj.depend_object_TYPE) and object_type =''PAGE'' union -- request set for report directly
select request_set_name RS_NAME from bis_request_set_objects
where object_name =:CONTENT_NAME and object_type =''REPORT'')
where BIS_BIA_RSG_PSTATE.get_refresh_mode(REQUEST_SET_NAME) != ''ANAL'') AND
HISTORY.PHASE_CODE = ''C'' AND
(HISTORY.STATUS_CODE = ''C'' OR HISTORY.STATUS_CODE = ''G'')
order by start_date desc
)
where rownum =1 )
)
LAST_SUCCESSFUL_REFRESH,
(
SELECT ''Dummy'' AS JOIN_COL,
LOOKUPS.MEANING Current_Status,
min(START_DATE) Refresh_Start_Time
FROM BIS_RS_RUN_HISTORY HISTORY, FND_LOOKUPS LOOKUPS
WHERE
HISTORY.REQUEST_SET_NAME IN
(select request_set_name RS_NAME from (select request_set_name --request set for dashboard that has this report
from bis_request_set_objects where object_name in ( select Distinct obj.OBJECT_NAME
from bis_obj_dependency obj where object_type in (''PAGE'') and enabled_flag=''Y''
start with obj.depend_object_type =''REPORT'' and obj.depend_object_name=:CONTENT_NAME
connect by prior obj.OBJECT_NAME=obj.depend_object_name and
prior obj.OBJECT_TYPE=obj.depend_object_TYPE) and object_type =''PAGE'' union -- request set for report directly
select request_set_name RS_NAME from bis_request_set_objects
where object_name =:CONTENT_NAME and object_type =''REPORT'')
where BIS_BIA_RSG_PSTATE.get_refresh_mode(REQUEST_SET_NAME) != ''ANAL'')AND
HISTORY.PHASE_CODE = ''R'' AND
HISTORY.PHASE_CODE = LOOKUPS.LOOKUP_CODE AND
LOOKUPS.LOOKUP_TYPE = ''BIS_RSG_PSTATE_RPT_LKP''
group by history.request_id,LOOKUPS.MEANING
)
CURRENT_RUN,
(
SELECT ''Dummy'' AS JOIN_COL,
min(FND_CONC.REQUESTED_START_DATE) REQUESTED_START_DATE
from
BIS_REQUEST_SET_OBJECTS OBJECTS,
FND_CONCURRENT_REQUESTS FND_CONC,
FND_REQUEST_SETS FND
where
OBJECTS.REQUEST_SET_NAME in(select request_set_name RS_NAME from (select request_set_name --request set for dashboard that has this report
from bis_request_set_objects where object_name in ( select Distinct obj.OBJECT_NAME
from bis_obj_dependency obj where object_type in (''PAGE'') and enabled_flag=''Y''
start with obj.depend_object_type =''REPORT'' and obj.depend_object_name=:CONTENT_NAME
connect by prior obj.OBJECT_NAME=obj.depend_object_name and
prior obj.OBJECT_TYPE=obj.depend_object_TYPE) and object_type =''PAGE'' union -- request set for report directly
select request_set_name RS_NAME from bis_request_set_objects
where object_name =:CONTENT_NAME and object_type =''REPORT'')
where BIS_BIA_RSG_PSTATE.get_refresh_mode(REQUEST_SET_NAME) != ''ANAL'') and
OBJECTS.REQUEST_SET_NAME = FND.REQUEST_SET_NAME AND
OBJECTS.SET_APP_ID = FND.APPLICATION_ID AND
to_char(FND.APPLICATION_ID) = FND_CONC.ARGUMENT1 AND
to_char(FND.REQUEST_SET_ID) = FND_CONC.ARGUMENT2 AND
FND_CONC.PHASE_CODE = ''P'' AND
FND_CONC.STATUS_CODE = ''I''
group by fnd_conc.request_id
)
NEXT_SCHEDULED_REFRESH
WHERE
NEXT_SCHEDULED_REFRESH.JOIN_COL(+) = DISP.JOIN_COL AND
CURRENT_RUN.JOIN_COL(+) = DISP.JOIN_COL AND
LAST_SUCCESSFUL_REFRESH.JOIN_COL(+) = DISP.JOIN_COL
';