[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_bsc_loader_del_program varchar2(30):='BSC_DELETE_DATA_IND';
select 'Y'
from dual
where exists
(select 'Y'
from BIS_BIA_RSG_IMPL_FLAG_TEMP
where set_name=p_set_name
and set_app=p_set_app
and top_object_type=p_top_object_type
and top_object_name=p_top_object_name
and object_type=p_object_type
and object_name=p_object_name
and object_impl_flag='Y');
procedure delete_set_all(p_setname in varchar2,p_setlongname in varchar2,p_setapp in varchar2) is
l_group_name varchar2(30);
select
REQUEST_SET_NAME
from
fnd_request_sets_vl a,
fnd_application b
where a.USER_REQUEST_SET_NAME=p_setlongname
and a.application_id=b.application_id
and b.application_short_name=p_setapp;
fnd_set.delete_set(upper(l_setname),p_setapp);
/* changes for 'view request set history': delete from
bis_request_set_options and bis_request_set_objects if the request
set already exists in these tables. */
delete_rs_objects(upper(l_setname), p_setapp);
delete_rs_option(upper(l_setname), p_setapp);
fnd_set.delete_set(upper(p_setname),p_setapp);
/* changes for 'view request set history': delete from
bis_request_set_options and bis_request_set_objects if the request
set already exists in these tables. */
delete_rs_objects(upper(p_setname), p_setapp);
delete_rs_option(upper(p_setname), p_setapp);
end delete_set_all;
select 'Y'
from dual
where exists
(select 'Y'
from user_objects
where object_name='BSC_DBGEN_UTILS' and object_type='PACKAGE' );
select
REQUEST_SET_NAME
from
fnd_request_sets_vl a,
fnd_application b
where a.USER_REQUEST_SET_NAME=p_setlongname
and a.application_id=b.application_id
and b.application_short_name=p_setapp;
fnd_set.delete_set(upper(l_setname),p_setapp);
/* changes for 'view request set history': delete from
bis_request_set_options and bis_request_set_objects if the request
set already exists in these tables. */
delete_rs_objects(upper(l_setname), p_setapp);
delete_rs_option(upper(l_setname), p_setapp);
fnd_set.delete_set(upper(p_setname),p_setapp);
/* changes for 'view request set history': delete from
bis_request_set_options and bis_request_set_objects if the request
set already exists in these tables. */
delete_rs_objects(upper(p_setname), p_setapp);
delete_rs_option(upper(p_setname), p_setapp);
select 'Y'
from
( select distinct
obj.depend_OBJECT_NAME obj_name,
obj.depend_object_type obj_type
from
( select object_name,
object_type,
depend_object_name,
depend_object_type,
enabled_flag
from
bis_obj_dependency
where enabled_flag='Y'
and depend_object_type<>'REPORT'
and object_type<>'REPORT'
) obj
start with obj.object_type =p_top_object_type
and obj.object_name=p_top_object_name
connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
and prior obj.DEPEND_OBJECT_TYPE=obj.object_type ) depend_objects
where depend_objects.obj_type=p_object_type
and depend_objects.obj_name=p_object_name;
select object_name
from BIS_BIA_RSG_IMPL_FLAG_TEMP
where set_name=p_set_name
and set_app=p_set_app
and top_object_type=p_top_object_type
and top_object_name=p_top_object_name
and object_type='REPORT'
and object_impl_flag='N';
select object_name
from BIS_BIA_RSG_IMPL_FLAG_TEMP
where set_name=p_set_name
and set_app=p_set_app
and top_object_type=p_top_object_type
and top_object_name=p_top_object_name
and object_type='REPORT'
and object_impl_flag='Y';
select distinct dep.depend_object_name, dep.depend_object_type
from
( select object_name,
object_type,
depend_object_name,
depend_object_type
from bis_obj_dependency
where enabled_flag='Y') dep
where dep.depend_object_type<>'REPORT'
start with dep.object_type = 'REPORT'
and dep.object_name=p_report_name
connect by prior dep.depend_object_name = dep.object_name
and prior dep.depend_object_type = dep.object_type;
update BIS_BIA_RSG_IMPL_FLAG_TEMP
set object_impl_flag='N'
where set_name=p_set_name
and set_app=p_set_app
and top_object_type=p_top_object_type
and top_object_name=p_top_object_name
and object_type=l_obj_rec.depend_object_type
and object_name=l_obj_rec.depend_object_name;
update BIS_BIA_RSG_IMPL_FLAG_TEMP
set object_impl_flag='Y'
where set_name=p_set_name
and set_app=p_set_app
and top_object_type=p_top_object_type
and top_object_name=p_top_object_name
and object_type=l_obj_rec.depend_object_type
and object_name=l_obj_rec.depend_object_name;
procedure insert_stage_objects(p_set_name in varchar2,
p_set_app in varchar2,
p_object_name in varchar2,
p_object_type in varchar2) is
l_sql_stmt varchar2(2000);
select depend_objects.obj_type object_type,depend_objects.obj_name object_name
from
( select distinct
obj.depend_OBJECT_NAME obj_name,
obj.depend_object_type obj_type,
obj.depend_object_owner obj_owner
from
( select object_name,
object_type,
object_owner,
depend_object_name,
depend_object_type,
depend_object_owner,
enabled_flag
from
bis_obj_dependency
where enabled_flag='Y' ) obj
start with obj.object_type =p_object_type
and obj.object_name = p_object_name
connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
and prior obj.DEPEND_OBJECT_TYPE=obj.object_type ) depend_objects
union---the object itself could have program so we need union here
select object_type,object_name
from bis_obj_properties
where object_type= p_object_type
and object_name=p_object_name;
l_sql_stmt:='insert into BIS_BIA_RSG_IMPL_FLAG_TEMP(set_name,set_app,top_object_type,top_object_name,object_type,object_name,object_impl_flag)
values (:1,:2,:3,:4,:5,:6,:7)';
l_sql_stmt := 'insert into BIS_BIA_RSG_STAGE_OBJECTS(set_name,set_app,stage_name,object_type,object_name)
values (:1,:2,:3,:4,:5)';
select meaning into l_meaning
from fnd_common_lookups
where lookup_type=p_type
and lookup_code=p_code;
select
c.stage_name,
b.sequence
from
fnd_request_sets a,
fnd_request_set_programs b,
fnd_request_set_stages c,
fnd_concurrent_programs d,
fnd_application e,
fnd_application f
where a.request_set_id=b.request_set_id
and b.request_set_stage_id=c.request_set_stage_id
and a.request_set_id=c.request_set_id
and b.concurrent_program_id=d.concurrent_program_id
and b.program_application_id=d.application_id
and a.application_id=e.application_id
and d.application_id=f.application_id
and a.request_set_name=p_set_name
and d.concurrent_program_name=p_process_name
and e.application_short_name=p_set_app
and f.application_short_name=p_process_app
-- added to pickup indexes on FND_REQUEST_SET_STAGES and FND_REQUEST_SET_PROGRAMS
-- bug3143536
and c.set_application_id = a.application_id
and b.request_set_id = c.request_set_id
and a.application_id = b.set_application_id;
select
max(b.sequence) max_prog_seq
from
fnd_request_sets a,
fnd_request_set_programs b,
fnd_request_set_stages c,
fnd_application d
where a.request_set_id=b.request_set_id
and b.request_set_stage_id=c.request_set_stage_id
and a.request_set_id=c.request_set_id
and a.application_id=d.application_id
and d.application_short_name=p_set_app
and a.request_set_name=p_set_name
and c.stage_name=p_stage_name
-- added to pickup indexes on FND_REQUEST_SET_STAGES and FND_REQUEST_SET_PROGRAMS
-- bug3143536
and c.set_application_id = a.application_id
and b.request_set_id = c.request_set_id
and a.application_id = b.set_application_id
group by c.stage_name;
select 'Y'
from dual
where exists
(select 1
from bis_obj_prog_linkages
where object_type=p_object_type
and object_name=p_object_name
and enabled_flag='Y');
execute immediate 'select distinct indicator from bsc_kpis_b where short_name=:1' into l_indicator using p_object_name;
select distinct
depend_object_owner object_owner,
depend_object_type object_type,
depend_OBJECT_NAME object_name,
level+1 mylevel
from
(select distinct
a.depend_object_owner ,
a.depend_object_type ,
a.depend_object_name ,
a.object_type ,
a.object_owner ,
a.object_name
from
bis_obj_dependency a
where enabled_flag='Y') temp
start with object_type =p_object_type
and object_name = p_object_name
connect by prior DEPEND_OBJECT_NAME=object_name
and prior DEPEND_OBJECT_TYPE=object_type
union
select distinct
object_owner,
object_type,
object_name,
1 mylevel
from
bis_obj_dependency
where enabled_flag='Y'
and object_type =p_object_type
and object_name = p_object_name
union
select distinct
depend_object_owner object_owner,
depend_object_type object_type,
depend_object_name object_name,
1 mylevel
from
bis_obj_dependency
where enabled_flag='Y'
and depend_object_type =p_object_type
and depend_object_name = p_object_name
union --added for bug 4648079 auto gen report with custom calendar
select distinct
object_owner,
object_type,
object_name,
1 mylevel
from bis_obj_prog_linkages
where object_type=p_object_type
and object_name=p_object_name
order by mylevel desc;
select nvl(max(level),0)+1 from
(select distinct
a.depend_object_owner ,
a.depend_object_type ,
a.depend_object_name ,
a.object_type ,
a.object_owner ,
a.object_name
from
bis_obj_dependency a
where enabled_flag='Y') temp
start with object_type=p_object_type
and object_name = p_object_name
connect by prior depend_object_name=object_name
and prior depend_object_type=object_type;
select distinct
a.CONC_PROGRAM_NAME CONCURRENT_PROGRAM_NAME,
a.CONC_APP_SHORT_NAME APPLICATION_SHORT_NAME
from
bis_obj_prog_linkages a,
fnd_concurrent_programs b
where a.object_name=p_objectname
and a.object_type=p_objecttype
and a.enabled_flag='Y'
and a.CONC_PROGRAM_NAME<>g_bsc_loader_ind_program
and (decode(nvl(a.refresh_mode,'INCR'),'INIT_INCR','INCR',nvl(a.refresh_mode,'INCR'))=p_mode
or
decode(nvl(a.refresh_mode,'INCR'),'INIT_INCR','INIT',nvl(a.refresh_mode,'INCR'))=p_mode)
and a.CONC_PROGRAM_NAME=b.concurrent_program_name
and a.CONC_APP_ID=b.application_id
and b.ENABLED_FLAG='Y' ;
select
max(b.display_sequence)
from
fnd_request_sets a,
fnd_request_set_stages b,
fnd_application c
where a.request_set_id=b.request_set_id
and a.application_id=b.set_application_id
and a.application_id=c.application_id
and c.application_short_name=p_set_application
and a.request_set_name=upper(p_setname);
insert_stage_objects(upper(l_setname),
l_set_application,
p_object_name,
p_object_type );
select 'Y'
from dual
where exists
(select a.request_set_name
from fnd_request_sets a,
fnd_request_group_units b,
fnd_request_groups c,
fnd_application d1,
fnd_application d2
where a.application_id=b.unit_application_id
and a.request_set_id=b.request_unit_id
and b.request_unit_type='S'
and a.application_id=d1.application_id
and a.request_set_name=p_set_name
and d1.application_short_name=p_setapp
and c.application_id=b.application_id
and c.request_group_id=b.request_group_id
and c.application_id=d2.application_id
and d2.application_short_name=p_group_app
and c.request_group_name=p_group_name);
select
a.application_id set_app_id ,
a.request_set_id set_id ,
c.REQUEST_SET_STAGE_ID stage_id,
c.STAGE_NAME stage_name,
c.display_sequence
from
fnd_request_sets a,
fnd_application b,
fnd_request_set_stages c
where
a.application_id=b.application_id
and b.application_short_name=p_setapp
and a.application_id=c.SET_APPLICATION_ID
and a.request_set_id=c.REQUEST_SET_ID
and a.request_set_name=upper(p_set_name)
order by c.display_sequence;
select start_stage
from
fnd_request_sets a,
fnd_application b
where
a.application_id=b.application_id
and a.request_set_name=p_set_name
and b.application_short_name=p_setapp;
update fnd_request_sets
set start_stage=l_first_stage_id
where request_set_id=l_set_id
and application_id=l_set_app_id;
select 'N'
from dual
where exists
(select request_set_program_id
from
fnd_request_set_programs
where set_application_id=p_setapp_id
and request_set_id=p_set_id
and request_set_stage_id=p_set_stage_id);
select
distinct
b.stage_name,
c.sequence
from
fnd_request_sets a,
fnd_request_set_stages b,
fnd_request_set_programs c,
fnd_request_set_program_args d,
fnd_application e
where a.request_set_id=b.request_set_id
and a.application_id=b.set_application_id
and a.application_id=e.application_id
and e.application_short_name=p_set_app
and a.request_set_name=p_set_name
and b.set_application_id=c.set_application_id
and b.request_set_id=c.request_set_id
and b.request_set_stage_id=c.request_set_stage_id
and c.request_set_id=d.request_set_id
and c.set_application_id=d.application_id
and c.request_set_program_id=d.request_set_program_id
and d.descriptive_flexfield_name='$SRS$.'||p_process_name
and d.default_type=p_parameter_type
and d.default_value=p_parameter_value;
SELECT oracle_username
FROM fnd_oracle_userid WHERE oracle_id
BETWEEN 900 AND 999 AND read_only_flag = 'U';
(SELECT owner
FROM all_mviews
WHERE owner = p_apps_schema_name
AND Upper(mview_name) = Upper(p_obj_name))
UNION ALL
(SELECT s.table_owner owner
FROM all_mviews mv, user_synonyms s
WHERE mv.owner = s.table_owner
AND mv.mview_name = s.table_name
AND Upper(mv.mview_name) = Upper(p_obj_name)
);**/
select owner
from all_mviews
where owner=p_apps_schema_name
and mview_name = Upper(p_obj_name);
SELECT s.table_owner owner
FROM all_mviews mv, user_synonyms s
WHERE mv.owner = s.table_owner
AND mv.mview_name = s.table_name
AND s.synonym_name = Upper(p_obj_name);
(SELECT owner
FROM all_tables
WHERE owner = p_apps_schema_name
AND Upper(table_name) = Upper(p_obj_name))
UNION ALL
(SELECT s.table_owner owner
FROM user_synonyms s, all_tables t
WHERE t.owner = s.table_owner
AND t.table_name = s.table_name
AND t.table_name = Upper(p_obj_name));**/
SELECT s.table_owner owner
FROM user_synonyms s, all_tables t
WHERE t.owner = s.table_owner
AND t.table_name = s.table_name
AND s.synonym_name = Upper(p_obj_name);
select owner
from all_tables
where owner= p_apps_schema_name
and table_name = Upper(p_obj_name);
SELECT p_apps_schema_name owner
FROM user_views
WHERE view_name = Upper(p_obj_name);
select distinct
depend_object_type object_type,
depend_OBJECT_NAME object_name,
level
from bis_obj_dependency a
where a.enabled_flag='Y'
and a.object_type<>'VIEW'
and EXISTS( Select 'Y' from bis_obj_properties b
where a.depend_object_name=b.object_name
and a.depend_object_type=b.object_type
and b.DIMENSION_FLAG='Y' )
start with a.object_type= p_dim_type and a.object_name=p_dim_name
connect by prior a.DEPEND_OBJECT_NAME=a.object_name
and prior a.depend_object_type= a.object_type
order by level desc;
select max(level)
from bis_obj_dependency a
where a.enabled_flag='Y'
and EXISTS ( Select 'Y' from bis_obj_properties b
where a.depend_object_name=b.object_name
and a.depend_object_type=b.object_type
and b.DIMENSION_FLAG='Y')
start with a.object_type= p_dim_type and a.object_name=p_dim_name
connect by prior a.DEPEND_OBJECT_NAME=a.object_name
and prior a.depend_object_type=a.object_type
order by level desc;
select distinct
a.CONC_PROGRAM_NAME CONCURRENT_PROGRAM_NAME,
a.CONC_APP_SHORT_NAME APPLICATION_SHORT_NAME
from
bis_obj_prog_linkages a,
fnd_concurrent_programs b
where a.object_name=p_objectname
and a.object_type=p_objecttype
and a.enabled_flag='Y'
---and (nvl(a.refresh_mode,'INCR')=p_refresh_mode or nvl(a.refresh_mode,'INCR')='INIT_INCR')
and decode(nvl(a.refresh_mode,'INCR'),'INIT_INCR','INCR',nvl(a.refresh_mode,'INCR'))=p_refresh_mode
and a.CONC_PROGRAM_NAME=b.concurrent_program_name
and a.CONC_APP_ID=b.application_id
and b.ENABLED_FLAG='Y';
select
min(b.display_sequence)
from
fnd_request_sets a,
fnd_request_set_stages b,
fnd_application c
where a.request_set_id=b.request_set_id
and a.application_id=b.set_application_id
and a.application_id=c.application_id
and c.application_short_name=p_setapp
and a.request_set_name=upper(p_setname);
/* changes for 'view request set history': insert record into
bis_request_set_objects. */
create_rs_objects(upper(p_setname), p_setapp, p_object_type,
p_object_name , p_object_owner);
select 'Y'
from dual
where exists
(select a.depend_object_name
from bis_obj_dependency a,
bis_obj_properties b
where a.depend_object_name=b.object_name(+)
and a.depend_object_type=b.object_type(+)
and a.object_name=p_object_name
and a.object_type=p_object_type
and a.enabled_flag='Y'
and nvl(b.dimension_flag,'N')='N');
SELECT DISTINCT LOG_TABLE
FROM all_snapshot_logs
WHERE master = p_object_name
AND log_owner = p_schema_name;
select distinct a.object_name,a.object_type
from
bis_request_set_objects a,
fnd_request_sets b,
fnd_application c
where a.request_set_name=b.request_set_name
and a.set_app_id=b.application_id
and b.request_set_name=upper(p_request_set_code)
and b.application_id=c.application_id
and c.application_short_name=p_set_app;
select depend_objects.obj_type,depend_objects.obj_name
from
( select distinct
obj.depend_OBJECT_NAME obj_name,
obj.depend_object_type obj_type,
obj.depend_object_owner obj_owner
from
( select object_name,
object_type,
object_owner,
depend_object_name,
depend_object_type,
depend_object_owner,
enabled_flag
from
bis_obj_dependency
where enabled_flag='Y' ) obj
start with obj.object_type =p_object_type
and obj.object_name = p_object_name
connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
and prior depend_object_type=object_type
) depend_objects
where depend_objects.obj_type='MV'
or (depend_objects.obj_type in ('MV','TABLE') and
depend_objects.obj_type||depend_objects.obj_name in
(select object_type||object_name from bis_obj_prog_linkages where enabled_flag='Y'));
select distinct
'REPORT' object_type,
c.default_value object_name
from
fnd_request_set_programs a,
fnd_request_set_program_args c
where a.set_application_id=p_set_app_id
and a.request_set_id=p_set_id
and a.request_set_stage_id=p_stage_id
and a.request_set_id=c.request_set_id
and a.set_application_id=c.application_id
and a.request_set_program_id=c.request_set_program_id
and c.descriptive_flexfield_name='$SRS$.'||g_bsc_loader_ind_program
and c.default_type=g_parameter_default_type
and c.application_column_name='ATTRIBUTE1';
(3) Add stages: first stage---Update object implementation flag
last stage---MV dummy refresh programs
Alerting
(4) remove empty stages
It is called from UI
**/
procedure wrapup( p_setname in varchar2,
p_setapp in varchar2,
p_option in varchar2,
p_analyze_table in varchar2,
p_refresh_mode in varchar2,
p_force_full_refresh in varchar2,
p_alert_flag in varchar2,
p_rsg_history_flag in varchar2) is
cursor c_stages is
select
a.application_id set_app_id ,
a.request_set_id set_id ,
a.request_set_name set_name,
b.application_short_name set_app,
c.REQUEST_SET_STAGE_ID stage_id,
c.STAGE_NAME stage_name,
c.display_sequence
from
fnd_request_sets a,
fnd_application b,
fnd_request_set_stages c
where
a.application_id=b.application_id
and b.application_short_name=p_setapp
and a.application_id=c.SET_APPLICATION_ID
and a.request_set_id=c.REQUEST_SET_ID
and a.request_set_name=upper(p_setname)
order by c.display_sequence;
select
max(b.display_sequence)
from
fnd_request_sets a,
fnd_request_set_stages b,
fnd_application c
where a.request_set_id=b.request_set_id
and a.application_id=b.set_application_id
and a.application_id=c.application_id
and c.application_short_name=p_setapp
and a.request_set_name=upper(p_setname);
select
min(b.display_sequence)
from
fnd_request_sets a,
fnd_request_set_stages b,
fnd_application c
where a.request_set_id=b.request_set_id
and a.application_id=b.set_application_id
and a.application_id=c.application_id
and c.application_short_name=p_setapp
and a.request_set_name=upper(p_setname);
select distinct
c.object_type,
c.object_name
from
fnd_request_set_programs a,
fnd_concurrent_programs b,
bis_obj_prog_linkages c,
BIS_BIA_RSG_STAGE_OBJECTS d
where a.set_application_id=p_set_app_id
and a.request_set_id=p_set_id
and a.request_set_stage_id=p_stage_id
and a.program_application_id=b.application_id
and a.concurrent_program_id=b.concurrent_program_id
and b.application_id=c.CONC_APP_ID
and b.concurrent_program_name=c.CONC_PROGRAM_NAME
and c.enabled_flag='Y'
and c.object_type=d.object_type
and c.object_name=d.object_name
and d.set_name=p_set_name
and d.set_app=p_set_app
union
select distinct
'MV' object_type,
c.default_value object_name
from
fnd_request_set_programs a,
fnd_request_set_program_args c
where a.set_application_id=p_set_app_id
and a.request_set_id=p_set_id
and a.request_set_stage_id=p_stage_id
and a.request_set_id=c.request_set_id
and a.set_application_id=c.application_id
and a.request_set_program_id=c.request_set_program_id
and c.descriptive_flexfield_name='$SRS$.BIS_MV_REFRESH'
and c.default_type=g_parameter_default_type
and c.application_column_name='ATTRIBUTE2';
select
distinct d.CUSTOM_API custom_api
from
fnd_request_set_programs a,
fnd_concurrent_programs b,
bis_obj_prog_linkages c,
bis_obj_properties d
where a.set_application_id=p_set_app_id
and a.request_set_id=p_set_id
and a.request_set_stage_id=p_stage_id
and a.program_application_id=b.application_id
and a.concurrent_program_id=b.concurrent_program_id
and b.application_id=c.CONC_APP_ID
and b.concurrent_program_name=c.CONC_PROGRAM_NAME
and c.enabled_flag='Y'
and c.refresh_mode in ('INIT','INIT_INCR')-----?? can we use INIT_INCR here
and c.object_type=d.object_type
and c.object_name=d.object_name
and d.object_type=p_obj_type
and d.object_name=p_obj_name;
/* changes for 'view request set history': insert record into
bis_request_set_options. */
create_rs_option(upper(p_setname), p_setapp,
p_refresh_mode, p_analyze_table,p_force_full_refresh, p_alert_flag, p_rsg_history_flag);
select APPLICATION_ID into l_set_app_id from fnd_application where
application_short_name=p_set_app;
l_stmt := 'insert into bis_request_set_options(request_set_name, set_app_id,
option_name,option_value, CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,LAST_UPDATE_DATE) values
(:1,:2,:3,:4,:5,:6,:7,:8,:9)';
cursor c_owner is select distinct OBJECT_OWNER from bis_obj_dependency
where object_type=p_object_type and object_name=p_object_name;
UPDATE bis_request_set_objects
set object_name = :1
where object_name = :2
and object_type = ''PAGE''
';
select APPLICATION_ID into l_set_app_id from fnd_application where
application_short_name=p_set_app;
l_stmt := 'insert into bis_request_set_objects(request_set_name, set_app_id,
object_type,object_name, object_owner, CREATED_BY,
CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,LAST_UPDATE_DATE) values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)';
procedure delete_rs_objects(p_set_name in varchar2, p_set_app in varchar2) is
l_stmt VARCHAR2 (2000);
select APPLICATION_ID into l_set_app_id from fnd_application where
application_short_name=p_set_app;
l_stmt := 'delete bis_request_set_objects where request_set_name=:a and
set_app_id=:b';
end delete_rs_objects;
procedure delete_rs_option(p_set_name in varchar2, p_set_app in varchar2) is
l_stmt VARCHAR2 (2000);
select APPLICATION_ID into l_set_app_id from fnd_application where
application_short_name=p_set_app;
l_stmt := 'delete bis_request_set_options where request_set_name=:a and
set_app_id=:b';
end delete_rs_option;
l_sql:='select /*+ FIRST_ROWS */ 1 from '||l_owner||'.'||p_object_name||' where rownum=1';
select NVL(OPTION_VALUE, 'N')
from bis_request_set_options
where request_set_name = pReqCode
and OPTION_NAME = 'ALERT_FLAG';
select distinct a.object_type, bis_impl_dev_pkg.get_user_object_name(a.object_type,a.object_name) user_object_name
from bis_request_set_objects a,
bis_obj_properties b
where a.request_set_name=p_request_set_code
and a.set_app_id=191
and a.object_type=b.object_type
and a.object_name=b.object_name
and b.implementation_flag='N';
select distinct c.user_request_set_name from
bis_request_set_objects a,
bis_obj_properties b,
fnd_request_sets_vl c
where a.object_name=b.object_name
and a.object_type=b.object_type
and b.implementation_flag='N'
and a.request_set_name=c.request_set_name
and a.set_app_id=c.application_id;
select option_value
from bis_request_set_options
where request_set_name=p_request_set_code
and SET_APP_ID=191
and option_name='REFRESH_MODE';
select option_value
from bis_request_set_options
where request_set_name=p_request_set_code
and set_app_id=191
and option_name='FORCE_FULL';
select
req.argument1,
req.argument2
from
fnd_concurrent_requests req
where
req.request_id = p_req_id ;
SELECT req.request_id REQUEST, req.phase_code Phase, requested_start_date s_date
FROM fnd_concurrent_programs prog, fnd_concurrent_requests req
WHERE prog.CONCURRENT_PROGRAM_NAME = 'BIS_BIA_TRUNCATE_EMPTY_MV_LOGS'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND req.phase_code = 'R';
BIS_COLL_RS_HISTORY.update_terminated_rs;
l_sql:='select distinct to_char(b.indicator) indicator_id from '||
' bis_indicators a, '||
' bsc_kpi_analysis_measures_b b '||
' where a.dataset_id=b.dataset_id '||
' and a.function_name=:1';
select 'Y'
into l_exist_flag
from fnd_concurrent_programs
where concurrent_program_name='BSC_LOAD_INDICATORS_DATA'
and application_id=271;
select distinct option_value into l_loading_mode
from bis_request_set_options
where request_set_name=p_request_set_name
and option_name='REFRESH_MODE';
select distinct option_value into l_force_full_refresh
from bis_request_set_options
where request_set_name=p_request_set_name
and option_name='FORCE_FULL';
select distinct a.object_name,a.object_type
from
bis_request_set_objects a,
fnd_request_sets b
where a.request_set_name=b.request_set_name
and a.set_app_id=b.application_id
and b.request_set_name=p_request_set_code
and b.application_id=191;
select distinct get_indicator(obj_name) kpi
from
( select distinct
obj.depend_OBJECT_NAME obj_name,
obj.depend_object_type obj_type,
obj.depend_object_owner obj_owner
from
( select object_name,
object_type,
object_owner,
depend_object_name,
depend_object_type,
depend_object_owner,
enabled_flag
from
bis_obj_dependency
where enabled_flag='Y' ) obj
start with obj.object_type =p_object_type
and obj.object_name = p_object_name
connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
and prior depend_object_type=object_type
) depend_objects
where obj_type ='REPORT'
and obj_owner=get_bsc_schema_name
and obj_name like 'BSC%';
BIS_COLLECTION_UTILITIES.put_line('Initial loading mode. calling loader program to delete data from indicators');
'BSC_DELETE_DATA_IND',
NULL,
NULL,
FALSE,
l_kpi_list,
'Y');---'Y' means keep input tables data
BIS_COLLECTION_UTILITIES.put_line('Submitted request for BSC_DELETE_INDICATORS_DATA ' || l_request_id);
BIS_COLL_RS_HISTORY.insert_program_object_data(x_request_id => l_request_id,
x_stage_req_id => null,
x_object_name => l_kpi_list,
x_object_type => 'BSC_CUSTOM_KPI',
x_refresh_type => loading_mode(p_request_set_code),
x_set_request_id => FND_GLOBAL.CONC_PRIORITY_REQUEST);
BIS_COLL_RS_HISTORY.insert_program_object_data( x_request_id => l_request_id,
x_stage_req_id => null,
x_object_name => l_kpi_list,
x_object_type => 'BSC_CUSTOM_KPI',
x_refresh_type => loading_mode(p_request_set_code),
x_set_request_id => FND_GLOBAL.CONC_PRIORITY_REQUEST);
BIS_COLL_RS_HISTORY.insert_program_object_data( x_request_id => l_request_id,
x_stage_req_id => null,
x_object_name => l_kpi_list,
x_object_type => 'BSC_CUSTOM_KPI',
x_refresh_type => loading_mode(p_request_set_code),
x_set_request_id => FND_GLOBAL.CONC_PRIORITY_REQUEST);
select 'Y'
from fnd_form_functions
where function_name=p_object_name
and type in ('WWW','JSP');
select 'Y'
from fnd_form_functions
where function_name=p_object_name
and type in ('WEBPORTLET','WEBPORTLETX');
select 'Y'
from fnd_form_functions
where function_name= bis_impl_dev_pkg.get_function_by_page(p_object_name)
and upper(web_html_call) like '%BIS_COMPONENT_PAGE'||'&'||'AKREGIONAPPLICATIONID=191%';
select distinct a.object_name,a.object_type
from
bis_request_set_objects a,
fnd_request_sets b,
fnd_application c
where a.request_set_name=b.request_set_name
and a.set_app_id=b.application_id
and b.request_set_name=upper(p_request_set_code)
and b.application_id=c.application_id
and c.application_short_name=p_set_app;
select depend_objects.obj_type object_type,depend_objects.obj_name object_name,depend_objects.obj_owner object_owner
from
( select distinct
obj.depend_OBJECT_NAME obj_name,
obj.depend_object_type obj_type,
obj.depend_object_owner obj_owner
from
( select object_name,
object_type,
object_owner,
depend_object_name,
depend_object_type,
depend_object_owner,
enabled_flag
from
bis_obj_dependency
where enabled_flag='Y' ) obj
start with obj.object_type =p_object_type
and obj.object_name = p_object_name
connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
and prior depend_object_type=object_type
) depend_objects;
l_sql_stmt := 'insert into BIS_BIA_RSG_SEED_VALIDATION(object_type,object_name,object_owner)
values (:1,:2,:3)';
l_sql_stmt := 'insert into BIS_BIA_RSG_SEED_VALIDATION(object_type,object_name,object_owner)
values (:1,:2,:3)';
l_sql_stmt:='select distinct object_type,object_owner,object_name from BIS_BIA_RSG_SEED_VALIDATION
order by object_type,object_owner,object_name';
select s.STAGE_NAME
from fnd_request_sets r ,fnd_request_set_stages s,
fnd_application app
where r.REQUEST_SET_ID= s.REQUEST_SET_ID
and r.application_id = s.set_Application_id
and r.REQUEST_SET_NAME = p_set_name
and r.application_id = app.application_id
and app.application_short_name=p_set_app;
select application_short_name from fnd_application
where application_id =271;
SELECT request_id, user_concurrent_program_name NAME, 'PROG' TYPE
FROM BIS_RS_PROG_RUN_HISTORY bis, FND_CONCURRENT_PROGRAMS_VL fnd
WHERE bis.set_request_id = l_root_request_id AND bis.status_code ='E'
AND bis.prog_app_id = fnd.application_id AND bis.program_id = fnd.concurrent_program_id
UNION
SELECT request_id, user_stage_name NAME, 'STAGE' TYPE
FROM BIS_RS_STAGE_RUN_HISTORY bis, FND_REQUEST_SET_STAGES_VL fnd
WHERE set_request_id = l_root_request_id AND status_code ='E'
AND bis.set_app_id = fnd.set_application_id AND bis.request_set_id = fnd.request_set_id
AND bis.stage_id = fnd.request_set_stage_id ;
select NVL(OPTION_VALUE, 'Y')
from bis_request_set_options
where request_set_name = rs_name
and set_app_id = rs_app_id
and OPTION_NAME = 'HISTORY_COLLECT';
SELECT rs.request_set_name
FROM fnd_concurrent_requests cr, fnd_request_sets rs
WHERE cr.request_id = p_root_request_id
AND rs.application_id = cr.argument1
AND rs.request_set_id = cr.argument2;