[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from dual
where exists
(select descriptive_flexfield_name
from fnd_descr_flex_column_usages
where application_id=p_app_id
and descriptive_flex_context_code = 'Global Data Elements'
and descriptive_flexfield_name='$SRS$.'||p_program_name
and enabled_flag='Y'
and display_flag='Y');
procedure update_default_value(p_program_name varchar2, p_app_id number) is
sqlstmt varchar2(2000);
sqlstmt:='update fnd_descr_flex_column_usages '||
'set default_value=null '||
'where application_id='||p_app_id||
' and descriptive_flexfield_name='||'''$SRS$.'||p_program_name||''''||
' and default_value is not null '||
' and enabled_flag =''Y'''||
' and display_flag=''Y'''||
' and upper(END_USER_COLUMN_NAME) like ''%DATE%'''||
' and default_type=''S''';
select distinct
obj.depend_OBJECT_NAME ,
obj.depend_object_type
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
where depend_object_type='REPORT'
start with
obj.object_type ='PORTLET'
and obj.object_name=p_portlet_name
connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
and prior obj.DEPEND_OBJECT_TYPE=obj.object_TYPE ;
select max(temp.latest_date) latest_date
from
(select
max(aa.actual_completion_date) latest_date,
aa.program_application_id,
aa.concurrent_program_id
from
fnd_concurrent_requests aa,
( select distinct
b.application_id application_id,
b.CONCURRENT_PROGRAM_ID concurrent_program_id
from
bis_obj_prog_linkages a,
fnd_concurrent_programs b
where a.object_name=p_obj_name
and a.object_type=p_obj_type
and a.CONC_PROGRAM_NAME=b.concurrent_program_name
and a.conc_program_name <>'BSC_REFRESH_SUMMARY_IND'
and a.CONC_APP_ID=b.application_id
and a.enabled_flag='Y'
and b.enabled_flag='Y'
and a.refresh_mode in ('INIT','INCR','INIT_INCR')) bb
where
aa.program_application_id= bb.application_id
and aa.concurrent_program_id=bb.concurrent_program_id
and aa.status_code in ('I','R','G','C')
and aa.phase_code='C'
group by aa.program_application_id,aa.concurrent_program_id) temp;
select
max(aa.actual_completion_date) latest_time
-- aa.program_application_id program_application_id,
-- aa.concurrent_program_id concurrent_program_id
from
fnd_concurrent_requests aa,
fnd_concurrent_programs bb
where bb.concurrent_program_name='BIS_MV_REFRESH'
and bb.application_id=191
and aa.program_application_id=bb.application_id
and aa.concurrent_program_id=bb.concurrent_program_id
and aa.status_code in ('I','R','G','C')
and aa.phase_code='C'
and aa.argument2=p_obj_name
and aa.argument1 in ('INIT','INCR');
select 'Y'
from dual
where exists
(select 'Y'
from bis_obj_prog_linkages a,
fnd_concurrent_programs b
where a.object_name=p_obj_name
and a.object_type='MV'
and a.enabled_flag='Y'
and a.CONC_APP_ID=b.application_id
and a.CONC_PROGRAM_NAME=b.concurrent_program_name
and b.enabled_flag='Y');
select
max(aa.actual_completion_date) latest_time
-- aa.program_application_id program_application_id,
-- aa.concurrent_program_id concurrent_program_id
from
fnd_concurrent_requests aa,
fnd_concurrent_programs bb
where bb.concurrent_program_name='BSC_REFRESH_SUMMARY_IND'
and bb.application_id=271
and aa.program_application_id=bb.application_id
and aa.concurrent_program_id=bb.concurrent_program_id
and aa.status_code in ('I','R','G','C')
and aa.phase_code='C'
and aa.argument1=to_char(bis_create_requestset.get_indicator_auto_gen(p_obj_name));
select last_refresh_date
from bis_obj_properties
where object_name= p_obj_name
and object_type= p_obj_type;
select distinct
--- obj.depend_object_owner object_owner,
obj.depend_object_type object_type,
obj.depend_OBJECT_NAME object_name
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
union----the following part is for picking up the report itself
select distinct
--- objdep.object_owner object_owner,
objp.object_type object_type,
objp.object_name object_name
from
bis_obj_properties objp
where objp.object_name=p_object_name
and objp.object_type = p_object_type;
select 'Y'
from dual
where exists (select 'Y'
from bis_obj_prog_linkages a,
fnd_concurrent_programs b
where a.object_name=p_obj_name
and a.object_type=p_obj_type
and (a.refresh_mode=p_refresh_mode or a.refresh_mode='INIT_INCR')
and a.enabled_flag='Y'
and a.CONC_APP_ID=b.application_id
and a.CONC_PROGRAM_NAME=b.concurrent_program_name
and b.enabled_flag='Y'
);
select LAST_REFRESH_DATE,object_name
from bis_obj_properties
where object_name= p_object_name
and object_type in ('REPORT','PORTLET');
/** Bug 4257955--this piece of logic is moved to update last refresh date program.
Objects with programs or MVs but not refreshed successfully
will be updated to '01-01-1900' to differenciate from those without
programs or MVs defined.
---The following change is for enhancement 3426538
if l_object_name is not null then ---object registered in RSG
if l_date is null then
l_date:=to_date('01-01-1900','DD-MM-YYYY');
We no longer derive last refresh date on the fly. The last refresh date will be updated to
bis_obj_properties table whenever the request set is re-generated in 4.0.7 and gets run
successfully
if l_date is not null then
return l_date;
select distinct
obj.depend_OBJECT_NAME ,
obj.depend_object_type
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
where depend_object_type in ('REPORT','PORTLET')
start with
obj.object_type ='PAGE'
and obj.object_name=p_page_name
connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
and prior obj.DEPEND_OBJECT_TYPE=obj.object_TYPE ;
select LAST_REFRESH_DATE
from bis_obj_properties
where bis_impl_dev_pkg.get_function_by_page(object_name)=bis_impl_dev_pkg.get_function_by_page(p_page_name)
and object_type='PAGE';
select LAST_REFRESH_DATE
from bis_obj_properties
where object_name=p_page_name||'_OA'
and object_type='PAGE'
union
select LAST_REFRESH_DATE
from bis_obj_properties
where object_name=p_page_name
and object_type='PAGE';
select LAST_REFRESH_DATE,object_name
from bis_obj_properties
where object_name=p_page_name
and object_type='PAGE';
select LAST_REFRESH_DATE,object_name
from bis_obj_properties
where object_name=p_page_name||'_OA'
and object_type='PAGE';
The logic of '01-01-1900' is moved to update last
refresh date program for individual object
if l_date is null then
l_date:=to_date('01-01-1900','DD-MM-YYYY');
Select
max(aa.actual_completion_date) last_refresh_date
from
fnd_concurrent_requests aa,
fnd_concurrent_programs bb
where bb.concurrent_program_name='BSC_REFRESH_SUMMARY_IND'
and bb.application_id=271
and aa.program_application_id=bb.application_id
and aa.concurrent_program_id=bb.concurrent_program_id
and aa.status_code in ('I','R','G','C')
and aa.phase_code='C'
and aa.argument1 like '%'||indicator_id||'%'
and aa.argument2='N';
procedure update_page_portlet_date (p_request_id in number) is
----fixing bug 4053299
---remove the condition parent_request_id=-1 for
cursor request_set_id is
select to_number(argument2), to_number(argument1)
from fnd_concurrent_requests
----where parent_request_id=-1
where request_id=p_request_id;
select distinct a.option_value refresh_mode
from bis_request_set_options a,
fnd_request_sets b
where a.request_set_name=b.request_set_name
and a.set_app_id=b.application_id
and a.option_name='REFRESH_MODE'
and b.request_set_id=p_request_set_id
and b.application_id=p_req_set_app_id;
select distinct object_type,
object_name,
object_owner
from BIS_OBJ_SET_TEMP
where request_id=p_request_id
and object_type in ('REPORT','PORTLET');
select distinct object_type,
object_name,
object_owner
from BIS_OBJ_SET_TEMP
where request_id=p_request_id
and object_type ='PAGE';
BIS_COLLECTION_UTILITIES.put_line('*****'||l_object_rec.object_type||' '||l_object_rec.object_name||' has no implemented reports. Not to update its date.');
BIS_COLLECTION_UTILITIES.put_line('*****'||l_object_rec.object_type||' '||l_object_rec.object_name||' is not implemented. Not to update its date.');
bis_impl_dev_pkg.update_obj_last_refresh_date(l_object_rec.object_type,l_object_rec.object_name, l_date);
BIS_COLLECTION_UTILITIES.put_line('In this case, ''Data Last Update: Date is not available for Display'' will be displayed on UI');
bis_impl_dev_pkg.update_obj_last_refresh_date('PAGE',l_page_obj_rec.object_name,l_date);
select 'Y'
from bis_obj_prog_linkages
where object_name=p_report_name
and object_type='REPORT'
and CONC_PROGRAM_NAME='BSC_REFRESH_SUMMARY_IND';
procedure update_last_refresh_date(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_request_id IN NUMBER
) is
cursor c_obj_direct_in_set (P_REQSET_ID NUMBER) is
select distinct a.object_name, a.object_type,a.object_owner
from
bis_request_set_objects a,
fnd_request_sets b,
fnd_concurrent_requests c
where a.request_set_name=b.request_set_name
and a.SET_APP_ID=b.application_id
and b.request_set_id=to_number(c.argument2)
and b.application_id=to_number(c.argument1)
and c.request_id=P_REQSET_ID;
CURSOR C_OBJ_TO_BE_UPDATED(p_obj_type varchar2,p_obj_name varchar2)
IS
select distinct
obj.depend_OBJECT_NAME object_name,
obj.depend_object_type object_type,
obj.depend_object_owner object_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_obj_type
and obj.object_name=p_obj_name
connect by prior obj.DEPEND_OBJECT_NAME=obj.object_name
and prior obj.DEPEND_OBJECT_TYPE=obj.object_TYPE ;
l_indirect_obj_rec C_OBJ_TO_BE_UPDATED%rowtype;
select object_name
from bis_obj_properties
where bis_impl_dev_pkg.get_function_by_page(object_name)=bis_impl_dev_pkg.get_function_by_page(p_name)
and object_type='PAGE';
select 'Y'
from dual
where exists (select 'Y'
from bis_obj_prog_linkages a,
fnd_concurrent_programs b
where a.object_name=p_obj_name
and a.object_type=p_obj_type
and a.enabled_flag='Y'
and a.CONC_APP_ID=b.application_id
and a.CONC_PROGRAM_NAME=b.concurrent_program_name
and b.enabled_flag='Y'
);
select distinct object_type,
object_name
from BIS_OBJ_SET_TEMP
where request_id=p_request_id
and has_program='Y'
union
select distinct object_type,
object_name
from BIS_OBJ_SET_TEMP
where request_id=p_request_id
and object_type='MV';
BIS_COLLECTION_UTILITIES.put_line(l_top_obj_type||' '||l_top_obj_name||' is not implemented. Not to update its date.');
l_sql:='insert into BIS_OBJ_SET_TEMP(request_id,'||
'object_name,'||
'object_type,'||
'object_owner,'||
'has_program) '||
'values (:1,:2,:3,:4,:5)';
For l_indirect_obj_rec in C_OBJ_TO_BE_UPDATED(l_top_obj_type,l_top_obj_name) loop
l_obj_has_program:='N';
BIS_COLLECTION_UTILITIES.put_line(l_obj_type||' '||l_obj_name||' is not implemented. Not to update its date.');
bis_impl_dev_pkg.update_obj_last_refresh_date(l_obj_type,l_obj_name, l_last_refresh_date);
update_page_portlet_date(l_request_id);
if C_OBJ_TO_BE_UPDATED%isopen then
close C_OBJ_TO_BE_UPDATED;
SELECT 'Y', START_DATE
FROM BIS_RS_RUN_HISTORY HISTORY, BIS_REQUEST_SET_OBJECTS OBJECTS, FND_CONCURRENT_REQUESTS FND
WHERE OBJECTS.object_name = p_obj_name
AND OBJECTS.object_type = p_obj_type
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.REQUEST_ID = FND.REQUEST_ID
AND FND.STATUS_CODE <> 'X'
ORDER BY START_DATE;
SELECT 'Y', START_DATE
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 IN ('REPORT','PORTLET')
AND obj.depend_object_name= p_obj_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 = p_obj_name
AND object_type IN ('REPORT','PORTLET')
) RS, BIS_RS_RUN_HISTORY HISTORY, FND_CONCURRENT_REQUESTS FND
WHERE BIS_BIA_RSG_PSTATE.get_refresh_mode(RS.REQUEST_SET_NAME) <> 'ANAL'
AND RS.REQUEST_SET_NAME = HISTORY.REQUEST_SET_NAME AND HISTORY.PHASE_CODE = 'R'
AND HISTORY.REQUEST_ID = FND.REQUEST_ID AND FND.STATUS_CODE <> 'X'
ORDER BY START_DATE;
select
function_id,
web_html_call,
parameters
from
fnd_form_functions
where function_name='BIS_BIA_RSG_PSTATE_REPORT';
'&fromLastUpdateDate=Y&'||'pParameters=pParamIds'||'@'||'Y'||'~'||'DBI_REQUEST_SET'||'^'||'DBI_REQUEST_SET'||
'@'||p_obj_name||'~'||'DBI_CONTENT_TYPE'||'^'||'DBI_CONTENT_TYPE@PAGE'
, p_application_id =>fnd_global.resp_appl_id
, p_responsibility_id => fnd_global.resp_id
, p_security_group_id => icx_sec.g_security_group_id) */
p_RF_Url||'" class="OraLinkText">'||l_formatted_date||''||'';
l_return_string:=''||fnd_message.get_string('BIS','BIS_PMV_LAST_UPDATE_ERR')||'';
l_return_string:=fnd_message.get_string('BIS','BIS_PMV_LAST_UPDATE_ERR');
'&fromLastUpdateDate=Y&'||'pParameters=pParamIds'||'@'||'Y'||'~'||'DBI_REQUEST_SET'||'^'||'DBI_REQUEST_SET'||
'@'||p_obj_name||'~'||'DBI_CONTENT_TYPE'||'^'||'DBI_CONTENT_TYPE@REPORT'
, p_application_id =>fnd_global.resp_appl_id
, p_responsibility_id => fnd_global.resp_id
, p_security_group_id => icx_sec.g_security_group_id) */
p_RF_Url||'" class="OraLinkText">'||l_formatted_date||''||'';
l_return_string:=''||fnd_message.get_string('BIS','BIS_PMV_LAST_UPDATE_ERR')||'';
l_return_string:=fnd_message.get_string('BIS','BIS_PMV_LAST_UPDATE_ERR');