--How to get document
sequence detail
SELECT (SELECT gl_sets_of_books.NAME
FROM gl_sets_of_books
WHERE set_of_books_id = fnd_doc_sequence_assignments.set_of_books_id)
gl_sets_of_books,
(SELECT application_name
FROM fnd_application_tl fat
WHERE fat.application_id =
fnd_doc_sequence_assignments.application_id)
application,
(SELECT NAME
FROM fnd_document_sequences
WHERE doc_sequence_id = fnd_doc_sequence_assignments.doc_sequence_id)
doc_sequence_name,
start_date,
end_date,
(SELECT fu.user_name
FROM fnd_user fu
WHERE fu.user_id = fnd_doc_sequence_assignments.last_updated_by)
last_updated_by,
last_update_date,
(SELECT fu.user_name
FROM fnd_user fu
WHERE fu.user_id = fnd_doc_sequence_assignments.created_by)
created_by,
creation_date,
last_update_login,
doc_sequence_assignment_id
FROM fnd_doc_sequence_assignments
ORDER BY 1, 2, 3
--To check Current Applied
Patch
SELECT patch_name,
patch_type,
maint_pack_level,
creation_date
FROM applsys.ad_applied_patches
ORDER BY
creation_date
--To find Oracle API's for
any module
SELECT SUBSTR (a.owner, 1, 20) owner,
SUBSTR (a.name, 1, 30) api_name,
SUBSTR (a.TYPE, 1, 20) TYPE,
SUBSTR (u.status, 1, 10) stat,
u.last_ddl_time,
SUBSTR (text, 1, 80) description
FROM dba_source a, dba_objects u
WHERE 1 = 1
AND u.object_name = a.name
AND a.text LIKE '%Header%'
AND a.TYPE = u.object_type
AND a.name LIKE '%API%'
ORDER BY a.name
/*To check Responsibilities
assigned to a particular user or users assigned for particular responsibility
or all users and their responsibilities:*/
SELECT fu.user_id,
fu.user_name,
fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu, fnd_user_resp_groups fur, fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ( (fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ( (fur.end_date - 1), SYSDATE))
AND user_name = NVL (:username, user_name) --'LOKANADHAM' --- for all
user or for particular user
ORDER BY user_name
/*To get the concurrent
program file name (procedure / pkg name) based on the concurrent program name*/
select fct.user_concurrent_program_name,
fcp.concurrent_program_name,
fe.execution_file_name,
fl.meaning execution_method
from fnd_concurrent_programs_tl
fct,
fnd_concurrent_programs fcp,
fnd_executables fe,
fnd_lookups fl
where upper (fct.user_concurrent_program_name) =
nvl( upper (:concurrent_program), fct.user_concurrent_program_name)
and fct.concurrent_program_id = fcp.concurrent_program_id
and fe.executable_id = fcp.executable_id
and fl.lookup_code = fe.execution_method_code
and fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'
--To find all running
concurrent requests with their run times
select request_id,
fcpt.user_concurrent_program_name,
completion_text,
actual_start_date,
actual_completion_date,
(actual_completion_date - actual_start_date) *24*60 DURATION
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt
where fcr.concurrent_program_id
= fcp.concurrent_program_id
and fcp.concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.user_concurrent_program_name = NVL(:CP_NAME, fcpt.user_concurrent_program_name)
and fcr.actual_start_date > sysdate - 1
order by REQUEST_ID DESC
--To find from which
responsibility a concurrent program can be run
SELECT DISTINCT a.concurrent_program_id,
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 =
NVL (:p_conc_program_id, a.concurrent_program_id)
--To get all the tables with
the column name
select
table_name
from
all_tab_columns
where
column_name = 'PO_DISTRIBUTION_ID'
order by table_name