FND LOAD...
Create LDT File from where we already created Concarent Program
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXOPENPOHEADERCONVLOADER.ldt PROGRAM APPLICATION_SHORT_NAME="CUST_TOP" CONCURRENT_PROGRAM_NAME="XXOPENPOHEADERCONVLOADER"
Adding concarent program throgh shall script based on downloded LTD File
chmod 755 XXAPSUPPLOADER.ldt >> $LOGFILE 2>> $LOGERR
dos2unix XXAPSUPPLOADER.ldt >> $LOGFILE 2>> $LOGERR
FNDLOAD apps/$P_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $CUST_TOP/ldt/XXAPSUPPSITELOADER.ldt - CUSTOM_MODE=FORCE >> $LOGFILE 2>> $LOGERR
Adding concarent program to request Group
BEGIN
IF NOT fnd_program.program_in_group(program_short_name => 'XXAPSUPPSITELOADER',
program_application => 'CUST_TOP',
request_group => 'JAI_FixedAssets_RG',
group_application => 'JAIFARG') THEN
fnd_program.add_to_group(program_short_name => 'XXAPSUPPSITELOADER',
program_application => 'CUST_TOP',
request_group => 'JAI_FixedAssets_RG',
group_application => 'JAIFARG');
dbms_output.put_line('program attached to the request group');
else
dbms_output.put_line('program is in request group');
end if;
commit;
exception
when others then
raise;
end;
Tuesday, July 9, 2013
Friday, July 5, 2013
Query to list concurrent program details with its parameter, values set and default value/type AND Attached Responsibilities
1. query to list all the responsibilities attached to a user
select fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('lang')
2.to find from which responsibility a concurrent program can be run
select distinct
a.user_concurrent_program_name,
a.description,
request_group_name,
e.responsibility_name
from
fnd_concurrent_programs_tl a,
fnd_request_groups b,
fnd_request_group_units c,
fnd_responsibility d,
fnd_responsibility_tl e
where
a.concurrent_program_id = c.request_unit_id
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
and a.concurrent_program_id = :p_conc_program_id
3.provide concurrent program name to the following query.
it lists all the request sets which are created with the concurrent program given.
select distinct user_request_set_name
from fnd_request_sets_tl
where request_set_id in
(select request_set_id
from fnd_request_set_programs
where concurrent_program_id =
(select concurrent_program_id
from fnd_concurrent_programs_tl
where upper(user_concurrent_program_name) = upper( '&enter_prog_name')));
4. provide the request set name to the following query.
it lists all concurrent programs of this request set.
select user_concurrent_program_name
from fnd_concurrent_programs_tl
where concurrent_program_id in
(select concurrent_program_id
from fnd_request_set_programs
where request_set_id =
(select request_set_id
from fnd_request_sets_tl
where upper(user_request_set_name) = upper('&request_set_name')));
5. query to list concurrent program details with its parameter, values set and default value/type:
select fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, fav.application_short_name
, fav.application_name
, fav.application_id
, fdfcuv.end_user_column_name
, fdfcuv.form_left_prompt prompt
, fdfcuv.enabled_flag
, fdfcuv.required_flag
, fdfcuv.display_flag
, fdfcuv.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, fdfcuv.default_value
from fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl fdfcuv
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
, fnd_application_vl fav
where fcp.concurrent_program_id = fcpl.concurrent_program_id
and fcpl.user_concurrent_program_name = :conc_prg_name
and fcpl.language = 'us'
and fav.application_id=fcp.application_id
and fdfcuv.descriptive_flexfield_name = '$srs$.' || fcp.concurrent_program_name
and ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
and flv.lookup_type(+) = 'flex_default_type'
and flv.lookup_code(+) = fdfcuv.default_type
and flv.language(+) = userenv ('lang');
6. query to find out concurrent program details and its parameters
select fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, fcp.concurrent_program_id
, fav.application_short_name
, fav.application_name
, fav.application_id
, fdfcuv.end_user_column_name
, fdfcuv.form_left_prompt prompt
, fdfcuv.enabled_flag
, fdfcuv.required_flag
, fdfcuv.display_flag
from fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl fdfcuv
, fnd_application_vl fav
where fcp.concurrent_program_id = fcpl.concurrent_program_id
and fcpl.user_concurrent_program_name = :conc_prg_name
and fav.application_id=fcp.application_id
and fcpl.language = 'US'
and fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;
7 :for checking the locks in concurrent jobs
select decode(request,0,'holder: ','waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type from gv$lock
where (id1, id2, type) in (select id1, id2, type from gv$lock where request>0) order by id1,request;
8 :for checking the concurrent programs running currently with details of processed time-- and start date
select distinct c.user_concurrent_program_name,round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 as lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.fnd_concurrent_programs_tl c,apps.fnd_user d
where a.concurrent_program_id=b.concurrent_program_id and b.concurrent_program_id=c.concurrent_program_id and
a.requested_by=d.user_id and status_code='r' order by process_time desc;
9 :for checking last run of a concurrent program along with processed time
-- useful to find the details of concurrent programs which run daily and comparison purpose
select distinct c.user_concurrent_program_name,
round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) as process_time,
a.request_id,a.parent_request_id,to_char(a.request_date,'dd-mon-yy hh24:mi:ss'),to_char(a.actual_start_date,'dd-mon-yy hh24:mi:ss'),
to_char(a.actual_completion_date,'dd-mon-yy hh24:mi:ss'), (a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 as lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.fnd_concurrent_programs_tl c,
apps.fnd_user d
where a.concurrent_program_id= b.concurrent_program_id and
b.concurrent_program_id=c.concurrent_program_id and
a.requested_by =d.user_id and
-- trunc(a.actual_completion_date) = '24-aug-2005'
c.user_concurrent_program_name='incentive compensation analytics - odi' -- and argument_text like '%, , , , ,%';
-- and status_code!='c'
10 :for checking the last run of concurrent program.
- use below query to check all the concurrent request running which may refer given package
-- this is very useful check before compiling any package on given instance.
-- the query can be modified as per requirement.
-- remove fnd_concurrent_requests table and joins to check all program dependent on given package.
select
fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');
11 :the following query will display the time taken to execute the concurrent programs
--for a particular user with the latest concurrent programs sorted in least time taken
-- to complete the request.
select
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' hours ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' minutes ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' secs ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'r','running','c','complete',f.phase_code) phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
and pt.language = userenv('lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
12 : by using the below query we can get sid,serial#,spid of the concurrent request..
select a.request_id, d.sid, d.serial# , c.spid
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
where a.controlling_manager = b.concurrent_process_id
and c.pid = b.oracle_process_id
and b.session_id=d.audsid
and a.request_id = &request_id
and a.phase_code = 'r';
13 : by using below concurrent manager and program rules...
--gives detail of the concurrent_queue_name and user_concurrent_program_name
select b.concurrent_queue_name, c.user_concurrent_program_name
from fnd_concurrent_queue_content a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
where a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(include_flag, 'i', 1, 2), type_code;
14 : gives details of running concurrent jobs
select distinct c.user_concurrent_program_name,
round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
(a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 as lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.fnd_concurrent_programs_tl c,
apps.fnd_user d
where a.concurrent_program_id=b.concurrent_program_id and
b.concurrent_program_id=c.concurrent_program_id and
a.requested_by=d.user_id and
status_code='r' order by process_time desc;
15 : gives detail of concurrent job completed and pending
select
fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');
16 :gives detail of running and completed concurrent jobs with start date and end date
-- latest one at the top
select
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' hours ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' minutes ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' secs ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'r','running','c','complete',f.phase_code) phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
and pt.language = userenv('lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
17 : wait events details related with concurrent programs
select s.saddr, s.sid, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
upper (s.program) program, s.type, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
from v$session s
where ( (s.username is not null)
and (nvl (s.osuser, 'x') <> 'system')
and (s.type <> 'background') and status='active'
)
order by "program";
18 : to find the pid of the concurrent job and kill it.
select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_id ='31689665'
and a.inst_id = b.inst_id and c.os_process_id = a.process;
19 :to find the database sid of the concurrent job
-- we need our concurrent request id as an input.
-- c.spid= is the operating system process id
-- d.sid= is the oracle process id
sql> column process heading "fndlibr pid"
select a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.spid
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
where a.controlling_manager = b.concurrent_process_id
and c.pid = b.oracle_process_id
and b.session_id=d.audsid
and a.request_id = &request_id
and a.phase_code = 'r';
select fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('lang')
2.to find from which responsibility a concurrent program can be run
select distinct
a.user_concurrent_program_name,
a.description,
request_group_name,
e.responsibility_name
from
fnd_concurrent_programs_tl a,
fnd_request_groups b,
fnd_request_group_units c,
fnd_responsibility d,
fnd_responsibility_tl e
where
a.concurrent_program_id = c.request_unit_id
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
and a.concurrent_program_id = :p_conc_program_id
3.provide concurrent program name to the following query.
it lists all the request sets which are created with the concurrent program given.
select distinct user_request_set_name
from fnd_request_sets_tl
where request_set_id in
(select request_set_id
from fnd_request_set_programs
where concurrent_program_id =
(select concurrent_program_id
from fnd_concurrent_programs_tl
where upper(user_concurrent_program_name) = upper( '&enter_prog_name')));
4. provide the request set name to the following query.
it lists all concurrent programs of this request set.
select user_concurrent_program_name
from fnd_concurrent_programs_tl
where concurrent_program_id in
(select concurrent_program_id
from fnd_request_set_programs
where request_set_id =
(select request_set_id
from fnd_request_sets_tl
where upper(user_request_set_name) = upper('&request_set_name')));
5. query to list concurrent program details with its parameter, values set and default value/type:
select fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, fav.application_short_name
, fav.application_name
, fav.application_id
, fdfcuv.end_user_column_name
, fdfcuv.form_left_prompt prompt
, fdfcuv.enabled_flag
, fdfcuv.required_flag
, fdfcuv.display_flag
, fdfcuv.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, fdfcuv.default_value
from fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl fdfcuv
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
, fnd_application_vl fav
where fcp.concurrent_program_id = fcpl.concurrent_program_id
and fcpl.user_concurrent_program_name = :conc_prg_name
and fcpl.language = 'us'
and fav.application_id=fcp.application_id
and fdfcuv.descriptive_flexfield_name = '$srs$.' || fcp.concurrent_program_name
and ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
and flv.lookup_type(+) = 'flex_default_type'
and flv.lookup_code(+) = fdfcuv.default_type
and flv.language(+) = userenv ('lang');
6. query to find out concurrent program details and its parameters
select fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, fcp.concurrent_program_id
, fav.application_short_name
, fav.application_name
, fav.application_id
, fdfcuv.end_user_column_name
, fdfcuv.form_left_prompt prompt
, fdfcuv.enabled_flag
, fdfcuv.required_flag
, fdfcuv.display_flag
from fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl fdfcuv
, fnd_application_vl fav
where fcp.concurrent_program_id = fcpl.concurrent_program_id
and fcpl.user_concurrent_program_name = :conc_prg_name
and fav.application_id=fcp.application_id
and fcpl.language = 'US'
and fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;
7 :for checking the locks in concurrent jobs
select decode(request,0,'holder: ','waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type from gv$lock
where (id1, id2, type) in (select id1, id2, type from gv$lock where request>0) order by id1,request;
8 :for checking the concurrent programs running currently with details of processed time-- and start date
select distinct c.user_concurrent_program_name,round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 as lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.fnd_concurrent_programs_tl c,apps.fnd_user d
where a.concurrent_program_id=b.concurrent_program_id and b.concurrent_program_id=c.concurrent_program_id and
a.requested_by=d.user_id and status_code='r' order by process_time desc;
9 :for checking last run of a concurrent program along with processed time
-- useful to find the details of concurrent programs which run daily and comparison purpose
select distinct c.user_concurrent_program_name,
round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) as process_time,
a.request_id,a.parent_request_id,to_char(a.request_date,'dd-mon-yy hh24:mi:ss'),to_char(a.actual_start_date,'dd-mon-yy hh24:mi:ss'),
to_char(a.actual_completion_date,'dd-mon-yy hh24:mi:ss'), (a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 as lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.fnd_concurrent_programs_tl c,
apps.fnd_user d
where a.concurrent_program_id= b.concurrent_program_id and
b.concurrent_program_id=c.concurrent_program_id and
a.requested_by =d.user_id and
-- trunc(a.actual_completion_date) = '24-aug-2005'
c.user_concurrent_program_name='incentive compensation analytics - odi' -- and argument_text like '%, , , , ,%';
-- and status_code!='c'
10 :for checking the last run of concurrent program.
- use below query to check all the concurrent request running which may refer given package
-- this is very useful check before compiling any package on given instance.
-- the query can be modified as per requirement.
-- remove fnd_concurrent_requests table and joins to check all program dependent on given package.
select
fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');
11 :the following query will display the time taken to execute the concurrent programs
--for a particular user with the latest concurrent programs sorted in least time taken
-- to complete the request.
select
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' hours ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' minutes ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' secs ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'r','running','c','complete',f.phase_code) phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
and pt.language = userenv('lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
12 : by using the below query we can get sid,serial#,spid of the concurrent request..
select a.request_id, d.sid, d.serial# , c.spid
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
where a.controlling_manager = b.concurrent_process_id
and c.pid = b.oracle_process_id
and b.session_id=d.audsid
and a.request_id = &request_id
and a.phase_code = 'r';
13 : by using below concurrent manager and program rules...
--gives detail of the concurrent_queue_name and user_concurrent_program_name
select b.concurrent_queue_name, c.user_concurrent_program_name
from fnd_concurrent_queue_content a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
where a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(include_flag, 'i', 1, 2), type_code;
14 : gives details of running concurrent jobs
select distinct c.user_concurrent_program_name,
round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
(a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 as lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.fnd_concurrent_programs_tl c,
apps.fnd_user d
where a.concurrent_program_id=b.concurrent_program_id and
b.concurrent_program_id=c.concurrent_program_id and
a.requested_by=d.user_id and
status_code='r' order by process_time desc;
15 : gives detail of concurrent job completed and pending
select
fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');
16 :gives detail of running and completed concurrent jobs with start date and end date
-- latest one at the top
select
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' hours ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' minutes ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' secs ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'r','running','c','complete',f.phase_code) phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
and pt.language = userenv('lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
17 : wait events details related with concurrent programs
select s.saddr, s.sid, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
upper (s.program) program, s.type, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
from v$session s
where ( (s.username is not null)
and (nvl (s.osuser, 'x') <> 'system')
and (s.type <> 'background') and status='active'
)
order by "program";
18 : to find the pid of the concurrent job and kill it.
select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_id ='31689665'
and a.inst_id = b.inst_id and c.os_process_id = a.process;
19 :to find the database sid of the concurrent job
-- we need our concurrent request id as an input.
-- c.spid= is the operating system process id
-- d.sid= is the oracle process id
sql> column process heading "fndlibr pid"
select a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.spid
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
where a.controlling_manager = b.concurrent_process_id
and c.pid = b.oracle_process_id
and b.session_id=d.audsid
and a.request_id = &request_id
and a.phase_code = 'r';
How do I calculate the table space size in oracle
select /* + rule */
df.tablespace_name "tablespace",
df.bytes / (1024 * 1024 * 1024) "size(gb)",
sum (fs.bytes) / (1024 * 1024 * 1024) "free(gb)",
nvl (
round (
sum (fs.bytes) * 100 / df.bytes
),
1
) "%free",
round (
( df.bytes
- sum (fs.bytes)
) * 100 / df.bytes
) "%used"
from dba_free_space fs,
(select tablespace_name, sum (bytes) bytes
from dba_data_files
where tablespace_name like 'ontd%'
group by tablespace_name) df
where fs.tablespace_name(+) = df.tablespace_name
group by df.tablespace_name, df.bytes
order by 3 desc
to find the which sql is executing by the concurrent program in a schema
run the below query first to get the sid and the program details.
select q.concurrent_queue_name qname, f.user_name, a.request_id "req id",
decode (a.parent_request_id, -1, null, a.parent_request_id) "parent",
a.concurrent_program_id "prg id", a.phase_code, a.status_code,
vs.inst_id, vs.sid, vs.serial# "serial#", vp.spid,
b.os_process_id apprsid,
( nvl (a.actual_completion_date, sysdate)
- a.actual_start_date
)
* 1440
"time",
c.concurrent_program_name
|| ' - '
|| c2.user_concurrent_program_name "program"
from applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_processes b,
applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_programs_tl c2,
applsys.fnd_concurrent_programs c,
applsys.fnd_user f,
gv$session vs,
gv$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and f.user_name = :user_name
and c2.application_id = c.application_id
and a.phase_code in ('i', 'p', 'r', 't')
and a.status_code in ('r')
and a.requested_by = f.user_id
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and vp.spid = a.oracle_process_id
and vs.paddr(+) = vp.addr
and vs.inst_id(+) = vp.inst_id
order by 12;
query to find which sql is executing in a session
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.sid = :sid
df.tablespace_name "tablespace",
df.bytes / (1024 * 1024 * 1024) "size(gb)",
sum (fs.bytes) / (1024 * 1024 * 1024) "free(gb)",
nvl (
round (
sum (fs.bytes) * 100 / df.bytes
),
1
) "%free",
round (
( df.bytes
- sum (fs.bytes)
) * 100 / df.bytes
) "%used"
from dba_free_space fs,
(select tablespace_name, sum (bytes) bytes
from dba_data_files
where tablespace_name like 'ontd%'
group by tablespace_name) df
where fs.tablespace_name(+) = df.tablespace_name
group by df.tablespace_name, df.bytes
order by 3 desc
to find the which sql is executing by the concurrent program in a schema
run the below query first to get the sid and the program details.
select q.concurrent_queue_name qname, f.user_name, a.request_id "req id",
decode (a.parent_request_id, -1, null, a.parent_request_id) "parent",
a.concurrent_program_id "prg id", a.phase_code, a.status_code,
vs.inst_id, vs.sid, vs.serial# "serial#", vp.spid,
b.os_process_id apprsid,
( nvl (a.actual_completion_date, sysdate)
- a.actual_start_date
)
* 1440
"time",
c.concurrent_program_name
|| ' - '
|| c2.user_concurrent_program_name "program"
from applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_processes b,
applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_programs_tl c2,
applsys.fnd_concurrent_programs c,
applsys.fnd_user f,
gv$session vs,
gv$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and f.user_name = :user_name
and c2.application_id = c.application_id
and a.phase_code in ('i', 'p', 'r', 't')
and a.status_code in ('r')
and a.requested_by = f.user_id
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and vp.spid = a.oracle_process_id
and vs.paddr(+) = vp.addr
and vs.inst_id(+) = vp.inst_id
order by 12;
query to find which sql is executing in a session
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.sid = :sid
fnd load details
how to copy the fnd tables data from one instance to another instance using fndload
fndload apps/apps@devdb 0 y download testcfg.lct out.ldt fnd_application_tl appsname=fnd
how to insert the data into fnd_territories from back end
using the below package we can insert / update the data into fnd_territories_tl and fnd_territories.
fnd_territories_pkg.load_row (
x_territory_code=> :territory_code,
x_eu_code=> :eu_code,
x_iso_numeric_code=> :iso_numeric_code,
x_alternate_territory_code=> :alternate_territory_code,
x_nls_territory=> :nls_territory,
x_address_style=> :address_style,
x_address_validation=> :address_validation,
x_bank_info_style=> :bank_info_style,
x_bank_info_validation=> :bank_info_validation,
x_territory_short_name=> :territory_short_name,
x_description=> :description,
x_owner=> :owner,
x_last_update_date=> :last_update_date,
x_custom_mode=> :custom_mode,
x_obsolete_flag=> :obsolete_flag,
x_iso_territory_code=> :iso_territory_code
);
apart from the above package we need to use the below package to insert the data into fnd_currencies
fnd_currencies_pkg.load_row ( x_currency_code in varchar2,
x_derive_effective in date,
x_derive_type in varchar2,
x_global_attribute1 in varchar2,
x_global_attribute2 in varchar2,
x_global_attribute3 in varchar2,
x_global_attribute4 in varchar2,
x_global_attribute5 in varchar2,
x_global_attribute6 in varchar2,
x_global_attribute7 in varchar2,
x_global_attribute8 in varchar2,
x_global_attribute9 in varchar2,
x_global_attribute10 in varchar2,
x_global_attribute11 in varchar2,
x_global_attribute12 in varchar2,
x_global_attribute13 in varchar2,
x_global_attribute14 in varchar2,
x_global_attribute15 in varchar2,
x_global_attribute16 in varchar2,
x_global_attribute17 in varchar2,
x_global_attribute18 in varchar2,
x_global_attribute19 in varchar2,
x_global_attribute20 in varchar2,
x_derive_factor in number,
x_enabled_flag in varchar2,
x_currency_flag in varchar2,
x_issuing_territory_code in varchar2,
x_precision in number,
x_extended_precision in number,
x_symbol in varchar2,
x_start_date_active in date,
x_end_date_active in date,
x_minimum_accountable_unit in number,
x_context in varchar2,
x_attribute1 in varchar2,
x_attribute2 in varchar2,
x_attribute3 in varchar2,
x_attribute4 in varchar2,
x_attribute5 in varchar2,
x_attribute6 in varchar2,
x_attribute7 in varchar2,
x_attribute8 in varchar2,
x_attribute9 in varchar2,
x_attribute10 in varchar2,
x_attribute11 in varchar2,
x_attribute12 in varchar2,
x_attribute13 in varchar2,
x_attribute14 in varchar2,
x_attribute15 in varchar2,
x_iso_flag in varchar2,
x_global_attribute_category in varchar2,
x_name in varchar2,
x_description in varchar2,
x_owner in varchar2)
fndload apps/apps@devdb 0 y download testcfg.lct out.ldt fnd_application_tl appsname=fnd
how to insert the data into fnd_territories from back end
using the below package we can insert / update the data into fnd_territories_tl and fnd_territories.
fnd_territories_pkg.load_row (
x_territory_code=> :territory_code,
x_eu_code=> :eu_code,
x_iso_numeric_code=> :iso_numeric_code,
x_alternate_territory_code=> :alternate_territory_code,
x_nls_territory=> :nls_territory,
x_address_style=> :address_style,
x_address_validation=> :address_validation,
x_bank_info_style=> :bank_info_style,
x_bank_info_validation=> :bank_info_validation,
x_territory_short_name=> :territory_short_name,
x_description=> :description,
x_owner=> :owner,
x_last_update_date=> :last_update_date,
x_custom_mode=> :custom_mode,
x_obsolete_flag=> :obsolete_flag,
x_iso_territory_code=> :iso_territory_code
);
apart from the above package we need to use the below package to insert the data into fnd_currencies
fnd_currencies_pkg.load_row ( x_currency_code in varchar2,
x_derive_effective in date,
x_derive_type in varchar2,
x_global_attribute1 in varchar2,
x_global_attribute2 in varchar2,
x_global_attribute3 in varchar2,
x_global_attribute4 in varchar2,
x_global_attribute5 in varchar2,
x_global_attribute6 in varchar2,
x_global_attribute7 in varchar2,
x_global_attribute8 in varchar2,
x_global_attribute9 in varchar2,
x_global_attribute10 in varchar2,
x_global_attribute11 in varchar2,
x_global_attribute12 in varchar2,
x_global_attribute13 in varchar2,
x_global_attribute14 in varchar2,
x_global_attribute15 in varchar2,
x_global_attribute16 in varchar2,
x_global_attribute17 in varchar2,
x_global_attribute18 in varchar2,
x_global_attribute19 in varchar2,
x_global_attribute20 in varchar2,
x_derive_factor in number,
x_enabled_flag in varchar2,
x_currency_flag in varchar2,
x_issuing_territory_code in varchar2,
x_precision in number,
x_extended_precision in number,
x_symbol in varchar2,
x_start_date_active in date,
x_end_date_active in date,
x_minimum_accountable_unit in number,
x_context in varchar2,
x_attribute1 in varchar2,
x_attribute2 in varchar2,
x_attribute3 in varchar2,
x_attribute4 in varchar2,
x_attribute5 in varchar2,
x_attribute6 in varchar2,
x_attribute7 in varchar2,
x_attribute8 in varchar2,
x_attribute9 in varchar2,
x_attribute10 in varchar2,
x_attribute11 in varchar2,
x_attribute12 in varchar2,
x_attribute13 in varchar2,
x_attribute14 in varchar2,
x_attribute15 in varchar2,
x_iso_flag in varchar2,
x_global_attribute_category in varchar2,
x_name in varchar2,
x_description in varchar2,
x_owner in varchar2)
how to reset user password from back end in oracle apps
BEGIN
FND_USER_PKG.UPDATEUSER(x_user_name => 'EXUSTUBGYSER'
,x_owner => 'APPS'
,x_unencrypted_password => 'oracle1234'
,x_password_date => SYSDATE );
END;
Monday, May 27, 2013
Oracle R12 – How To Turn Off Low-Level Diagnostic Logging
“Low-level Diagnostic Logging is turned on. This may temporarily reduce performance”
How can this be disabled / turned off?
Disable diagnostic logging with the following steps:
1. Login as a user with System Administrator responsibility and then navigate to: Profile > System.
Set the following profile option at the User level:
1. Login as a user with System Administrator responsibility and then navigate to: Profile > System.
Set the following profile option at the User level:
FND: Debug Log Enabled = No
2. Logout of the application. You may also want to clear the browser’s cache.
3. Login again and confirm that the message no longer appears.
Tuesday, May 14, 2013
PLSQL LIMIT
declare
cursor a_cur is
select program_id
from airplanes;
type myarray is table of a_cur%rowtype;
cur_array myarray;
begin
open a_cur;
loop
fetch a_cur bulk collect into cur_array limit 1000;
exit when a_cur%notfound;
end loop;
close a_cur;
end;
/
FOR More info Please go through the URL: http://psoug.org/reference/array_processing.html
Subscribe to:
Posts (Atom)