[Home] [Help]
334: from
335: (select distinct object_name, object_type, object_space_usage,p.set_request_id srid,
336: Latestreq.request_set_id rsid,
337: Latestreq.request_set_type rstype from bis_obj_refresh_history o,
338: bis_rs_prog_run_history p,
339: (select max(request_id) maxid, request_set_id,request_set_type
340: from bis_rs_run_history WHERE PHASE_CODE=''C'' AND (STATUS_CODE=''C''
341: OR STATUS_CODE =''G'')
342: group by request_set_id,request_set_type) Latestreq
633: end if;
634:
635: if (rs_prog_req is not null AND rs_prog_req <> 'All') then
636: l_prog_cond := 'and B.program_id in (' || rs_prog_req ||') ';
637: 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 ||'))';
638: end if;
639:
640: l_sql_stmt := 'select
641: rid BIS_RS_SUB_REQS_ID,
674: ''pFunctionName=BIS_BIA_RSG_RPT_ERR_MSG_PG&formErrorType=NO_OBJECT'',
675: ''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''
676: ) url
677: from
678: bis_rs_prog_run_history B, fnd_concurrent_programs_vl F,
679: bis_rs_run_history R,bis_rs_stage_run_history S,
680: (select distinct prog_request_id prid, 1 num_obj from bis_obj_refresh_history
681: union select request_id prid,0 num_obj from bis_rs_prog_run_history where request_id not
682: in(select prog_request_id from bis_obj_refresh_history)) count_obj
677: from
678: bis_rs_prog_run_history B, fnd_concurrent_programs_vl F,
679: bis_rs_run_history R,bis_rs_stage_run_history S,
680: (select distinct prog_request_id prid, 1 num_obj from bis_obj_refresh_history
681: union select request_id prid,0 num_obj from bis_rs_prog_run_history where request_id not
682: in(select prog_request_id from bis_obj_refresh_history)) count_obj
683: where F.concurrent_program_id = B.program_id
684: and F.APPLICATION_ID = B.Prog_app_id
685: and R.request_id=B.set_request_id
799: decode(O.OBJECT_TYPE,''MV_LOG'',NULL,''BSC_CUSTOM_KPI'',NULL,'||''''||
800: 'pFunctionName=BIS_BIA_RSG_DEPENDENCIES_ALONE'
801: ||'&requestType=RSGReport&ObjType=''||o.object_type||''&ObjName=BIS_REQUEST_OBJECT_NAME''
802: ) BISREPORTURL
803: from bis_obj_refresh_history O,bis_rs_prog_run_history P,bis_rs_run_history R,
804: bis_rs_stage_run_history S
805: where O.prog_request_id = P.request_id
806: AND R.REQUEST_ID = S.SET_REQUEST_ID
807: AND S.REQUEST_ID = P.STAGE_REQUEST_ID
949: (select max(request_id) maxid, request_set_id rsid,request_set_type rstype
950: from bis_rs_run_history WHERE PHASE_CODE=''C'' AND (STATUS_CODE=''C''
951: OR STATUS_CODE =''G'')
952: group by request_set_id,request_set_type) Latestreq,
953: bis_rs_prog_run_history P, bis_rs_stage_run_history S,
954: (select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files
955: group by TABLESPACE_NAME) TOTAL_SPACE
956: where
957: Latestreq.maxid=S.set_request_id
1103: l_stmt Varchar2(200);
1104: stg_req_id Number;
1105:
1106: cursor get_max_stg (pid NUMBER ,srid NUMBER) is
1107: Select max(stage_request_id) from bis_rs_prog_run_history where program_id=pid
1108: and set_request_id= srid;
1109:
1110: BEGIN
1111:
1154:
1155: ret_val Number;
1156: cursor check_rsid (rsid NUMBER) is
1157: select 1 from dual where exists
1158: (select r.request_set_id from bis_rs_run_history r, bis_rs_prog_run_history p,
1159: bis_obj_refresh_history o
1160: where
1161: r.phase_code ='C' AND (r.STATUS_CODE='C' OR r.STATUS_CODE ='G')
1162: and p.set_request_id=r.Request_id