IMPORTANT FND Queries

Concurrent Program Attached to which responsibility
----------------------------------------------------------------


select responsibility_name
from fnd_responsibility_tl rsp_tl, fnd_responsibility fr, --fnd_request_groups frg,
fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt
where rsp_tl.responsibility_id = fr.responsibility_id
--and frg.request_group_id = fr.request_group_id
and fr.request_group_id = frgu.request_group_id
and fcpt.concurrent_program_id = frgu.request_unit_id
and upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) = upper('concurrent program name');


--------
Long running program taking more than 30 mins
-------------------------------------------------


SELECT fr.RESPONSIBILITY_NAME,
fcp.concurrent_program_id,
DECODE(fcptl.user_concurrent_program_name,'Report Set','Request Set'||'-'||fcr.description,fcptl.user_concurrent_program_name) user_concurrent_program_name,
fcptl.DESCRIPTION,
DECODE(fcr.STATUS_CODE,'C','Completed','R','Running','W','Paused','E','Error','G','Warning', fcr.STATUS_CODE) STATUS_CODE,
DECODE(fcr.PHASE_CODE,'C','Completed','R','Running',fcr.PHASE_CODE) PHASE_CODE,
fcr.request_id,
DECODE(phase_code,'R',ROUND(((SYSDATE-ACTUAL_START_DATE)*60*24),0),ROUND(((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*60*24),0)) diffmin,
TO_CHAR(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') ACTUAL_START_DATE,
TO_CHAR(ACTUAL_COMPLETION_DATE,'DD-MON-YYYY HH24:MI:SS') ACTUAL_COMPLETION_DATE,
fcr.ARGUMENT1,fcr.ARGUMENT2,fcr.ARGUMENT_TEXT
FROM apps.fnd_responsibility_tL fr,
apps.fnd_executables fe,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcptl,
apps.fnd_user fu,
apps.fnd_concurrent_requests fcr
WHERE 1=1
AND fcr.requested_by + 0 = fu.user_id
AND TRUNC(ACTUAL_START_DATE) >= TRUNC(SYSDATE)-1
AND fcr.concurrent_program_id =fcp.concurrent_program_id
AND FCR.PROGRAM_APPLICATION_ID = fcptl.application_id
AND fcp.concurrent_program_id = fcptl.concurrent_program_id
AND fcp.executable_application_id = fe.application_id
AND fcp.executable_id = fe.executable_id
AND fcr.RESPONSIBILITY_APPLICATION_ID = fr.APPLICATION_ID
AND fcr.responsibility_id = fr.responsibility_id
AND fcr.phase_code = 'R'
and DECODE(phase_code,'R',ROUND(((SYSDATE-ACTUAL_START_DATE)*60*24),0),ROUND(((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*60*24),0)) > 30
--AND fcptl.user_concurrent_program_name LIKE 'NAPP: CMAT Concurrent Program%'
ORDER BY fcr.request_id

Long running Query
-----------------------



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 = 57550441--57550441
AND a.phase_code = 'R';

SELECT sql_text
FROM v$sqltext_with_newlines
WHERE address = (SELECT prev_sql_addr
FROM v$session
WHERE SID = 6547)--6584)
ORDER BY piece;

SELECT sid, DECODE( block, 0, 'NO', 'YES' ) BLOCKER,
DECODE( request, 0, 'NO', 'YES' ) WAITER, id1, id2, lmode /* 6 is exclusive lock. For others, refer to SV10 */
FROM v$lock
WHERE request > 0 OR block > 0
ORDER BY block DESC

SELECT * FROM v$access WHERE object = upper('napp_pt01_batch_pkg')

select * from v$lock where block <> 0 ;




--------

net
-------

SELECT
fu.user_name,
fcpt.USER_CONCURRENT_PROGRAM_NAME,
fcpt.description,
fcp.CONCURRENT_PROGRAM_NAME,
fcr.REQUEST_ID,
round((fcr.actual_completion_date – decode (trunc(fcr.request_date),fcr.requested_start_date,fcr.request_date,fcr.requested_start_date))*60*24) WaitTimeMIN,
DECODE(fcr.PHASE_CODE,'C','Completed','R','Running',fcr.PHASE_CODE) PHASE_CODE,
DECODE(fcr.STATUS_CODE,‘C’,‘Completed’,‘R’,‘Running’,‘W’,‘Paused’,‘E’,‘Error’,‘G’, ‘Warning’, fcr.STATUS_CODE) STATUS_CODE,
to_char(fcr.request_date,‘DD-MON-YYYY HH24:MI:SS’) request_date,
to_char(fcr.requested_start_date,‘DD-MON-YYYY HH24:MI:SS’) start_time,
to_char(fcr.actual_completion_date,‘DD-MON-YYYY HH24:MI:SS’) complete_time
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_user fu
WHERE 1=1
AND fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
AND fcpt.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID
AND fcr.requested_by = fu.user_id
AND trunc(fcr.request_date) BETWEEN sysdate – 1 AND sysdate
AND fcr.status_code IN (‘E’,‘G’)
ORDER BY fcr.status_code,fcp.CONCURRENT_PROGRAM_NAME,fcr.REQUEST_ID;


--- Menu..............


select responsibility_name resp fm.menu_name
from fnd_responsibility_vl resp fnd_request_group_units rgu fnd_concurrent_programs_vl prog
fnd_menus fm
where resp.request_group_id = rgu.request_group_id
AND fm.menu_id = resp.menu_id
AND rgu.request_unit_id = prog.concurrent_program_id
AND rgu.unit_application_id = prog.application_id
AND prog.user_concurrent_program_name = 'Financial Adjustments Report'
order by resp




how to find all indexes and their columns for tables, views and synonyms in oracle
**********************************************************************************



select b.uniqueness, a.index_name, a.table_name, a.column_name from all_ind_columns a, all_indexes b
where a.index_name=b.index_name
and a.table_name = upper('table_name') order by a.table_name, a.index_name, a.column_position;


Query to find all columns for a table
****************************************************


Select * From All_Tab_Columns
WHERE TABLE_NAME='NAPP_OE_ACCESS_KEYS'



Query to find all the scheduled concurrent programs
**********************************************************


SELECT fcr.request_id,
DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) CONC_PROG_NAME,
argument_text PARAMETERS,
NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
DECODE(NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
'PERIODICALLY',
'EVERY ' || fcr.resubmit_interval || ' ' ||
fcr.resubmit_interval_unit_code || ' FROM ' ||
fcr.resubmit_interval_type_code || ' OF PREV RUN',
'ONCE',
'AT :' ||
TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
fu.user_name USER_NAME,
requested_start_date START_DATE
FROM apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_concurrent_requests fcr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcrc
WHERE fcpt.application_id = fcr.program_application_id
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcr.requested_by = fu.user_id
AND fcr.phase_code = 'P'
AND fcr.requested_start_date > SYSDATE
AND fcpt.LANGUAGE = 'US'
AND fcrc.release_class_id(+) = fcr.release_class_id
AND fcrc.application_id(+) = fcr.release_class_app_id;