Queries : Application

To find the responsibilty for which form was assigned

SELECT fu.user_name,fu.description,furg.start_date,
frvl.responsibility_name,fff.function_name,ff.form_name
FROM fnd_user fu,fnd_user_resp_groups furg,fnd_responsibility_vl frvl,
fnd_compiled_menu_functions fcmf,fnd_form_functions_vl fff,fnd_form_vl ff
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = frvl.responsibility_id
AND frvl.menu_id = fcmf.menu_id
AND fff.function_id = fcmf.function_id
AND fff.form_id = ff.form_id
AND (fu.end_date IS NULL OR fu.end_date >= SYSDATE)
AND (furg.end_date IS NULL OR furg.end_date >= SYSDATE)
AND (frvl.end_date IS NULL OR frvl.end_date >= SYSDATE)
AND form_name IN ('FORMNAME');



To find the latest application version 


select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,
ROW_SOURCE_COMMENTS "how it is done",
BASE_RELEASE_FLAG "Base version"
FROM AD_RELEASES
where END_DATE_ACTIVE IS NULL;


To find the base application version 


select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated,
ROW_SOURCE_COMMENTS "how it is done"
from AD_RELEASES
where BASE_RELEASE_FLAG = 'Y' ;


To find all available application version


select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated,
END_DATE_ACTIVE "when lasted",
CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done"
from AD_RELEASES;

How many users are connected to Oracle Applications.

select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY Responsibility
from fnd_user fu, fnd_responsibility fr, icx_sessions ic
where fu.user_id = ic.user_id AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N' AND
ic.responsibility_id is not null AND
ic.last_connect like sysdate;



HOW TO find a concurrent program's trace file

SELECT req.request_id ,req.logfile_node_name node
,req.oracle_Process_id ,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name ,phase_code
,status_code ,ses.SID ,ses.serial# ,ses.module
,ses.machine

FROM  fnd_concurrent_requests req
,v$session ses ,v$process proc ,v$parameter dest
,v$parameter dbnm ,fnd_concurrent_programs_vl prog
,fnd_executables execname

WHERE 1=1
AND req.request_id = &request
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_id;

Regards,

Sukhwinder Singh