[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select MEANING into l_meaning from FND_LOOKUP_VALUES_VL where LOOKUP_TYPE=p_lookup_type and LOOKUP_CODE=p_lookup_code;
It updates completion status and date for stages and request set.
*********/
procedure rsg_history_report (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
Root_request_id IN NUMBER
) is
l_root_request_id number;
select
rs.Request_set_id Request_set_id,
rs.Set_app_id Set_app_id ,
stg.request_set_stage_id Stage_id,
req.Request_id Request_id ,
rs.request_id Set_request_id,
req.actual_start_date Start_date,
req.actual_completion_date Completion_date,
req.Status_code Status_code,
req.phase_code phase_code,
req.completion_text Completion_text
from bis_rs_run_history rs,
fnd_request_set_stages stg,
fnd_concurrent_requests req
where rs.request_id = l_root_request_id
and rs.request_set_id = stg.request_set_id
and req.argument3 = stg.request_set_stage_id
and req.parent_request_id = rs.request_id ;
select
stg.request_id Stage_request_id,
req.request_id Request_id ,
req.CONCURRENT_PROGRAM_ID program_id,
req.argument1 obj_owner,
req.argument2 obj_name
from fnd_concurrent_requests req,
bis_rs_stage_run_history stg
where stg.set_request_id = l_root_request_id
and stg.request_id = req.parent_request_id ;
select
req.request_id Request_id ,
req.parent_request_id parent_request_id,
req.CONCURRENT_PROGRAM_ID program_id,
prog.STAGE_REQUEST_ID stage_req_id
from fnd_concurrent_requests req,
BIS_RS_PROG_RUN_HISTORY prog
where --(req.parent_request_id is not null) and
req.parent_request_id = prog.request_id
and (instr(l_parent_request_ids,to_char(req.parent_request_id)||',') <> 0);
select CONCURRENT_PROGRAM_ID
from fnd_concurrent_programs
where concurrent_program_name ='FNDGTST'
and APPLICATION_ID =0;
select object_type
from bis_obj_properties
where object_name= l_object_name
and (Object_type ='MV' or Object_type ='TABLE');
select obj.prog_request_id request_id
from BIS_RS_PROG_RUN_HISTORY prog,
fnd_concurrent_programs fnd,
BIS_OBJ_REFRESH_HISTORY obj
where prog.set_request_id = l_root_request_id
and prog.program_id = fnd.CONCURRENT_PROGRAM_ID
and fnd.CONCURRENT_PROGRAM_NAME ='BIS_MV_REFRESH'
and obj.prog_request_id = prog.request_id
and obj.Refresh_type = 'CONSIDER_REFRESH';
select request_id
from BIS_RS_PROG_RUN_HISTORY
where set_request_id = l_root_request_id
and PROG_APP_ID =191
and program_id = ( select CONCURRENT_PROGRAM_ID
from fnd_concurrent_programs
where CONCURRENT_PROGRAM_NAME ='BIS_MV_DUMMY_REFRESH' and APPLICATION_ID =191);
select stage_request_id
from bis_rs_prog_run_history
where request_id = p_prog_req_id;
BIS_RS_STAGE_RUN_HISTORY_PKG.Insert_Row
( p_Request_set_id => c_stages_rec.Request_set_id
,p_Set_app_id => c_stages_rec.Set_app_id
,p_Stage_id => c_stages_rec.Stage_id
, p_Request_id => c_stages_rec.Request_id
, p_Set_request_id => c_stages_rec.Set_request_id
, p_Start_date => c_stages_rec.Start_date
, p_Completion_date => c_stages_rec.Completion_date
, p_Status_code => c_stages_rec.Status_code
, p_phase_code => c_stages_rec.phase_code
, p_Creation_date => sysdate
, p_Created_by => g_current_user_id
, p_Last_update_date => sysdate
, p_Last_updated_by => g_current_user_id
, p_completion_text => c_stages_rec.completion_text
);
insert_program_object_data( x_request_id => c_programs_rec.Request_id
, x_stage_req_id => c_programs_rec.Stage_request_id
, x_object_name => c_programs_rec.obj_name
, x_object_type => l_obj_type
, x_refresh_type => 'ANALYZED'
,x_set_request_id => l_root_request_id);
insert_program_object_data( x_request_id => c_programs_rec.Request_id
,x_stage_req_id => c_programs_rec.Stage_request_id
,x_object_name => null
,x_object_type => null
,x_refresh_type => null
,x_set_request_id => l_root_request_id);
insert_program_object_data( x_request_id => c_sub_programs_rec.Request_id
,x_stage_req_id => c_sub_programs_rec.stage_req_id
,x_object_name => null
,x_object_type => null
,x_refresh_type => null
,x_set_request_id => l_root_request_id);
if( BIS_OBJ_REFRESH_HISTORY_PKG.Update_Row
( p_Prog_request_id => c_refresh_mv_rec.request_id
,p_new_Prog_request_id => consider_mv_req
,p_Last_update_date => sysdate
,p_Last_updated_by => g_current_user_id )) then
BIS_COLLECTION_UTILITIES.put_line('****Updated request for Mvs which were dummy refreshed*********');
BIS_COLLECTION_UTILITIES.put_line('******Update for consider refresh failed********');
update_rs_stage_dates(l_root_request_id);
if((BIS_RS_PROG_RUN_HISTORY_PKG.Update_Row( p_Set_request_id => l_root_request_id
, p_Stage_request_id => l_rs_history_stage
, p_Request_id => FND_GLOBAL.conc_request_id
, p_Status_code => 'C'
, p_Phase_code => 'C'
, p_Completion_date => sysdate
, p_Last_update_date => sysdate
, p_Last_updated_by => g_current_user_id
, p_completion_text => g_completion_status))
and (BIS_RS_STAGE_RUN_HISTORY_PKG.Update_Row (p_Request_id => l_rs_history_stage
, p_Set_request_id =>l_root_request_id
, p_Completion_date => sysdate
, p_Status_code => 'C'
, p_phase_code => 'C'
, p_Last_update_date => sysdate
, p_Last_updated_by =>g_current_user_id
, p_completion_text => g_completion_status))
) then
--BIS_COLLECTION_UTILITIES.put_line('****Program and stage status for current request updated sucessfully********');
update_warn_compl_txt(l_root_request_id);
update_report_date;
update_rs_stage_dates(l_root_request_id);
select request_id from BIS_RS_RUN_HISTORY
where last_update_date <= (sysdate-history_days);
select request_id from BIS_RS_PROG_RUN_HISTORY
where set_request_id = p_set_rq_id;
--delete object data first
for rs_prog_run_rec in rs_prog_run(rs_run_rec.request_id) loop
BIS_OBJ_REFRESH_HISTORY_PKG.Delete_Row(rs_prog_run_rec.request_id);
BIS_RS_PROG_RUN_HISTORY_PKG.Delete_Row(rs_run_rec.request_id);
BIS_RS_STAGE_RUN_HISTORY_PKG.Delete_Row(rs_run_rec.request_id);
BIS_RS_RUN_HISTORY_PKG.Delete_Row(sysdate-history_days);
1. If program called with program id alone then it founds objects and inserts
2. If program called with both program id and object details then it just inserts
3. It always first check if the records is there. If there updates else inserts
4. This programs also calls api to get space usage details of every object.
*/
procedure insert_program_object_data (x_request_id IN NUMBER
,x_stage_req_id IN NUMBER
,x_object_name IN VARCHAR2
,x_object_type IN VARCHAR2
,x_refresh_type IN VARCHAR2
,x_set_request_id IN NUMBER)
IS
request_id number;
select 1 from BIS_RS_PROG_RUN_HISTORY
where Request_id = x_request_id;
select program_application_id,
req.concurrent_program_id,
Status_code,
Phase_code,
actual_start_date,
actual_completion_date,
completion_text,
concurrent_program_name
from fnd_concurrent_requests req, fnd_concurrent_programs prog
where request_id = x_request_id and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id;
select linkage.OBJECT_TYPE obj_type,
linkage.object_name obj_name,
linkage.refresh_mode obj_refresh_mode
from BIS_RS_PROG_RUN_HISTORY prog,
fnd_concurrent_programs fnd,
bis_obj_prog_linkages linkage
where prog.request_id = x_request_id
and prog.program_id = fnd.CONCURRENT_PROGRAM_ID
and prog.prog_app_id = fnd.application_id
and linkage.CONC_PROGRAM_NAME = fnd.CONCURRENT_PROGRAM_NAME
and linkage.CONC_APP_ID = fnd.application_id
and linkage.ENABLED_FLAG = 'Y'
and fnd.CONCURRENT_PROGRAM_NAME not in ('BIS_MV_REFRESH','BIS_RSG_PREP','BIS_RSG_FINAL',
'BIS_BIA_RSG_VALIDATION','BIS_BIA_RSG_MLOG_CAD',
'BIS_MV_DUMMY_REFRESH','BIS_LAST_REFRESH_DATE_CONC',
'BIS_BIA_RSG_LOG_MGMNT','FNDGTST','BSC_DELETE_DATA_IND',
'BSC_REFRESH_DIM_IND','BSC_REFRESH_SUMMARY_IND','BIS_BIA_STATS_TABLE');
BIS_RS_PROG_RUN_HISTORY_PKG.Insert_Row
( p_Set_request_id => l_root_request_id
, p_Stage_request_id => x_stage_req_id
, p_Request_id => x_request_id
, p_Program_id => req_details_rec.concurrent_program_id
, p_Prog_app_id => req_details_rec.program_application_id
, p_Status_code => req_details_rec.Status_code
, p_Phase_code => req_details_rec.Phase_code
, p_Start_date => req_details_rec.actual_start_date
, p_Completion_date => req_details_rec.actual_completion_date
, p_Creation_date => sysdate
, p_Created_by => g_current_user_id
, p_Last_update_date => sysdate
, p_Last_updated_by => g_current_user_id
, p_completion_text => req_details_rec.completion_text
);
if (BIS_RS_PROG_RUN_HISTORY_PKG.update_row
( p_Set_request_id => l_root_request_id
, p_Stage_request_id => x_stage_req_id
, p_Request_id => x_request_id
, p_Status_code => req_details_rec.Status_code
, p_Phase_code => req_details_rec.Phase_code
, p_Completion_date => req_details_rec.actual_completion_date
, p_Last_update_date => sysdate
, p_Last_updated_by => g_current_user_id
, p_completion_text => req_details_rec.completion_text) ) then
p_dummy_flag := 'Y';
BIS_COLLECTION_UTILITIES.put_line('***Update failed for request id '||x_request_id);
BIS_OBJ_REFRESH_HISTORY_PKG.Insert_Row(p_Prog_request_id => x_request_id
,p_Object_type => x_object_type
,p_Object_name => x_object_name
,p_Refresh_type => x_refresh_type
,p_Object_row_count => l_Object_row_count
,p_Object_space_usage => l_Object_space_usage
,p_Tablespace_name => l_Tablespace_name
,p_Free_tablespace_size => l_Free_tablespace_size
,p_Creation_date => sysdate
,p_Created_by => g_current_user_id
,p_Last_update_date => sysdate
,p_Last_updated_by => g_current_user_id
);
BIS_OBJ_REFRESH_HISTORY_PKG.Insert_Row(p_Prog_request_id => x_request_id
,p_Object_type => c_objects_rec.obj_type
,p_Object_name => c_objects_rec.obj_name
,p_Refresh_type => c_objects_rec.obj_refresh_mode
,p_Object_row_count => l_Object_row_count
,p_Object_space_usage => l_Object_space_usage
,p_Tablespace_name => l_Tablespace_name
,p_Free_tablespace_size => l_Free_tablespace_size
,p_Creation_date => sysdate
,p_Created_by => g_current_user_id
,p_Last_update_date => sysdate
,p_Last_updated_by => g_current_user_id
);
BIS_COLLECTION_UTILITIES.put_line('Exception happens in insert_program_object_data ' || sqlerrm);
END insert_program_object_data;
SELECT TABLESPACE_NAME Tblsp
FROM all_tables
WHERE TABLE_NAME = p_object_name
and OWNER = BIS_CREATE_REQUESTSET.get_object_owner(p_object_name,l_object_type);
SELECT SUM(bytes) FreeTablespace
FROM dba_free_Space fs
WHERE fs.tablespace_name = p_Tablespace_name ;
select PARTITION_NAME , TABLESPACE_NAME
from all_tab_partitions
where table_name = p_object_name
and table_owner = BIS_CREATE_REQUESTSET.get_object_owner(p_object_name,l_object_type);
l_stmt := 'SELECT COUNT(*) FROM '|| p_object_name;
This inserts the record in the BIS_RS_RUN_HISTORY table.
This is called from history collection program if request set is of type gather statistics only
for all the case it will be called from preparation program
*/
PROCEDURE add_rsg_rs_run_record(p_request_set_id IN NUMBER,
p_request_set_appl_id IN NUMBER,
p_request_name IN VARCHAR2,
p_root_req_id IN NUMBER)
IS
l_refresh_mode varchar2(30);
select PHASE_CODE ,STATUS_CODE,completion_text from fnd_concurrent_requests
where request_id = p_root_req_id;
BIS_RS_RUN_HISTORY_PKG.Insert_Row(
p_Request_set_id => p_request_set_id
, p_Set_app_id => p_request_set_appl_id
, p_request_set_name=> p_request_name
, p_Request_id => p_root_req_id
, p_rs_refresh_type => l_refresh_mode
, p_Start_date => sysdate
, p_Completion_date => null
, p_Status_code => l_rs_status_code
, p_Phase_code => l_rs_phase_code
, p_Creation_date => sysdate
, p_Created_by => g_current_user_id
, p_Last_update_date => sysdate
, p_Last_updated_by => g_current_user_id
, p_completion_Text => l_completion_text
);
select option_value
from bis_request_set_options
where request_set_name=( select request_set_name from fnd_request_sets
where request_set_id = p_request_set_id
and application_id = p_request_set_appl_id)
and SET_APP_ID=p_request_set_appl_id
and option_name='REFRESH_MODE';
select option_value
from bis_request_set_options
where request_set_name=( select request_set_name from fnd_request_sets
where request_set_id = p_request_set_id
and application_id = p_request_set_appl_id)
and SET_APP_ID=p_request_set_appl_id
and option_name='ANALYZE_OBJECT';
select 1 from BIS_RS_RUN_HISTORY
where REQUEST_ID = l_root_request_id and PHASE_CODE = 'C';
select
req.argument1,
req.argument2,
req.status_code,
req.phase_code
from
fnd_concurrent_requests req
where
req.request_id = p_req_id
and req.argument4 is null;
select request_set_name from
fnd_request_sets
where application_id = p_request_set_appl_id and request_set_id = p_request_set_id;
But we need to update the same in out tables otherwise it will N/A in out reports.
This procedure updted the completion text to "Completed with Error" for all the programs and stages
which comepleted with status as "Warning"
********/
PROCEDURE update_warn_compl_txt(p_root_req_id IN NUMBER) IS
cursor check_in_progs(root_req_id number) is
select request_id ,STAGE_REQUEST_ID from BIS_RS_PROG_RUN_HISTORY
where SET_REQUEST_ID = root_req_id and
status_code = 'G' ;
select request_id from BIS_RS_STAGE_RUN_HISTORY
where SET_REQUEST_ID = root_req_id and
status_code = 'G' ;
if(BIS_RS_PROG_RUN_HISTORY_PKG.Update_Row
( p_Set_request_id => p_root_req_id,
p_Stage_request_id => check_in_progs_rec.STAGE_REQUEST_ID,
p_Request_id => check_in_progs_rec.request_id,
p_Last_update_date => sysdate,
p_Last_updated_by => g_current_user_id,
p_completion_text => g_warning_status)) then
--BIS_COLLECTION_UTILITIES.put_line('Updated warning completion text ');
if(BIS_RS_STAGE_RUN_HISTORY_PKG.Update_Row
(p_Request_id => check_in_stages_rec.request_id ,
p_Set_request_id => p_root_req_id,
p_Last_update_date => sysdate,
p_Last_updated_by => g_current_user_id,
p_completion_text => g_warning_status) ) then
--BIS_COLLECTION_UTILITIES.put_line('Updated warning completion text for satges');
BIS_COLLECTION_UTILITIES.put_line('Exception happens in update_warning_completion_text ' || sqlerrm);
END update_warn_compl_txt;
We can not update the status for the request sets which were terminated as
our history collection program will not be called at all. And the entry will be always "Running". This will
affect our Page refresh status report.
Hence we will call this API in preparation program and Page refresh status program.
This API will update status for the request set which were terminated.
*/
PROCEDURE update_terminated_rs is
cursor terminate_rs is
select request_id from BIS_RS_RUN_HISTORY
where phase_code ='R' ;
select ACTUAL_COMPLETION_DATE,
phase_code,
status_code,
completion_text
from
fnd_concurrent_requests
where
request_id =req_no and
phase_code ='C' and
status_code ='X';
if(BIS_RS_RUN_HISTORY_PKG.Update_Row( p_Request_id => terminate_rs_rec.request_id
,p_Completion_date => req_details_rec.ACTUAL_COMPLETION_DATE
,p_Phase_code => req_details_rec.phase_code
,p_Status_code => req_details_rec.status_code
,p_Last_update_date => sysdate
,p_Last_updated_by => g_current_user_id
,p_Completion_text => req_details_rec.completion_text)) then
--BIS_COLLECTION_UTILITIES.put_line('*****Updated Terminated Req sets with request id ' || terminate_rs_rec.request_id);
BIS_COLLECTION_UTILITIES.put_line('Exception happens in update_terminated_rs ' || sqlerrm);
END update_terminated_rs;
/* This procedure updates the correct start time and end time for all the stages and request set.*/
PROCEDURE update_rs_stage_dates(p_root_req_id IN NUMBER) IS
--for updating request set status
cursor c_request_status is
select status_code from BIS_RS_PROG_RUN_HISTORY
where set_request_id =p_root_req_id
and (status_code ='E' or status_code ='G')
--added for Bug 4173989
union
select status_code from BIS_RS_STAGE_RUN_HISTORY
where set_request_id = p_root_req_id
and (status_code ='E' or status_code ='G') ;
select min(stg.start_date) start_date
from bis_rs_stage_run_history stg
where SET_REQUEST_ID = p_root_req_id;
select request_id
from bis_rs_stage_run_history stg
where SET_REQUEST_ID = p_root_req_id;
select min(START_DATE) stage_start_date, max(COMPLETION_DATE) stage_com_date
from bis_rs_prog_run_history
where STAGE_REQUEST_ID =stage_req_id;
if (BIS_RS_STAGE_RUN_HISTORY_PKG.Update_Row(p_Request_id => c_stages_rec.request_id
, p_Set_request_id => p_root_req_id
, p_start_date => l_stage_start_date
, p_Completion_date => l_stage_end_date
, p_Last_update_date => sysdate
, p_Last_updated_by =>g_current_user_id)) then
p_dummy_flag := 'Y';
if(BIS_RS_RUN_HISTORY_PKG.Update_Row( p_Request_id => p_root_req_id
,p_start_date => rs_start_date
,p_Completion_date => sysdate
,p_Phase_code => 'C'
,p_Status_code => l_status_code
,p_Last_update_date => sysdate
,p_Last_updated_by =>g_current_user_id
,p_Completion_text => l_Completion_text)) then
BIS_COLLECTION_UTILITIES.put_line('************History Data for this request set is collected sucessfully');
BIS_COLLECTION_UTILITIES.put_line('Exception happens in update_rs_stage_dates ' || sqlerrm);
END update_rs_stage_dates;
We need to have last_update_date at the bottom of the report for all our reports.
This API updates these seeded report's last_update_date in bis_obj_properties table.
*/
PROCEDURE update_report_date IS
BEGIN
bis_impl_dev_pkg.update_obj_last_refresh_date('REPORT','BIS_BIA_RSG_REQ_DETAILS_PGE',sysdate);
bis_impl_dev_pkg.update_obj_last_refresh_date('REPORT','BIS_BIA_RSG_SETS_DET_PGE',sysdate);
bis_impl_dev_pkg.update_obj_last_refresh_date('REPORT','BIS_BIA_RSG_SETS_LVL_PGE',sysdate);
bis_impl_dev_pkg.update_obj_last_refresh_date('REPORT','BIS_BIA_RSG_SPACE_DET_PGE',sysdate);
bis_impl_dev_pkg.update_obj_last_refresh_date('REPORT','BIS_BIA_RSG_SUB_REQS_PGE',sysdate);
bis_impl_dev_pkg.update_obj_last_refresh_date('REPORT','BIS_BIA_RSG_TABLESPACE_PGE',sysdate);
BIS_COLLECTION_UTILITIES.put_line('Exception happens in update_report_date ' || sqlerrm);
END update_report_date;
* inserting object data. Hence this api to populate this information in BIS_OBJ_REFRESH_HISTORY
*/
PROCEDURE capture_object_info(p_root_request_id IN NUMBER) IS
CURSOR get_objects IS
SELECT object_name, object_type, prog_Request_id
FROM BIS_RS_PROG_RUN_HISTORY prog, BIS_OBJ_REFRESH_HISTORY obj
WHERE set_request_id = p_root_request_id AND obj.prog_request_id = prog.request_id;
l_success := BIS_OBJ_REFRESH_HISTORY_PKG.UPDATE_ROW(p_Prog_request_id => get_objects_rec.prog_Request_id
,p_Object_type => get_objects_rec.object_type
,p_Object_name => get_objects_rec.object_name
,p_Object_row_count => l_Object_row_count
,p_Object_space_usage => l_Object_space_usage
,p_Tablespace_name => l_Tablespace_name
,p_Free_tablespace_size => l_Free_tablespace_size
,p_Last_update_date => sysdate
,p_Last_updated_by => g_current_user_id);
BIS_COLLECTION_UTILITIES.put_line('Failed to Update Row for Object Refresh History in capture_object_info()');