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;
Subscribe to:
Posts (Atom)