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) = upper('concurrent program')
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 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
List of responsibilities assigned to user
SELECT fu.user_name,
frt.responsibility_name,
furg.start_date,
furg.end_date
FROM fnd_user fu,
fnd_user_resp_groups_direct furg,
fnd_responsibility_vl frt
WHERE fu.user_id = furg.user_id
AND frt.responsibility_id = furg.responsibility_id
AND frt.application_id = furg.responsibility_application_id
AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE
AND NVL (frt.end_date, SYSDATE + 1) > SYSDATE
AND fu.user_name = upper( :p_user_name);
Concurrent Program and Executable Details
The following SQL query provide you executable file name ,top name , etc...
--Oracle Applications Query to get the actual concurrent program file executable if we know the concurrent program name
SELECT b.user_concurrent_program_name, b.concurrent_program_name,
a.user_executable_name,
DECODE (a.execution_method_code,
'I', 'PL/SQL Stored Procedure',
'H', 'Host',
'S', 'Immediate',
'J', 'Java Stored Procedure',
'K', 'Java concurrent program',
'M', 'Multi Language Function',
'P', 'Oracle reports',
'B', 'Request Set Stage Function',
'A', 'Spawned',
'L', 'SQL*Loader',
'Q', 'SQL*Plus',
'E', 'Pearl concurrent Programm',
'Unkown Type'
) TYPE,
a.execution_file_name, a.execution_file_path, a.application_name,
c.basepath
FROM fnd_executables_form_v a,
fnd_concurrent_programs_vl b,
fnd_application c
WHERE a.application_id = c.application_id
AND a.executable_id = b.executable_id
AND a.application_id = b.application_id
AND a.executable_id > 4
AND b.user_concurrent_program_name LIKE '%Sales%Bill%Out%'
Script to find Scheduled concurrent programs and request sets
SELECT
A.REQUEST_ID,
B.USER_NAME,
C.USER_CONCURRENT_PROGRAM_NAME,
C.ARGUMENT_TEXT
FROM
FND_CONCURRENT_REQUESTS A,
FND_USER B,
FND_CONC_REQ_SUMMARY_V C
WHERE
B.USER_ID = A.REQUESTED_BY
AND A.REQUEST_ID = C.REQUEST_ID
AND A.REQUESTED_START_DATE > SYSDATE
AND A.HOLD_FLAG = 'N'
AND A.STATUS_CODE IN ('Q','I')
AND B.USER_NAME LIKE '%'
AND A.DESCRIPTION LIKE '%'
ORDER BY
A.REQUEST_ID;
Input program name to get list of value sets using in this program
SELECT ffvs.flex_value_set_name,
fcp.user_concurrent_program_name,
fat.application_name,
fdfc.column_seq_num Seq,
fdfc.form_left_prompt "Parameter Name",
fdfc.enabled_flag Active
FROM FND_DESCR_FLEX_COL_USAGE_VL fdfc,
fnd_flex_value_sets ffvs,
fnd_concurrent_programs_vl fcp,
fnd_application_tl fat
WHERE 1 = 1 AND ffvs.flex_value_set_id = fdfc.flex_value_set_id
AND fdfc.descriptive_flexfield_name =
'$SRS$.' || fcp.concurrent_program_name
AND fat.language = USERENV ('LANG')
AND fcp.application_id = fat.application_id
AND fcp.user_concurrent_program_name LIKE :p_programsename -- Enter program name
;
Input value set name to get list of concurrent programs using this value set
SELECT ffvs.flex_value_set_name, fcp.user_concurrent_program_name,
fat.application_name,
fdfc.column_seq_num Seq,
fdfc.form_left_prompt "Parameter Name",
fdfc.enabled_flag Active
FROM FND_DESCR_FLEX_COL_USAGE_VL fdfc,
fnd_flex_value_sets ffvs,
fnd_concurrent_programs_vl fcp,
fnd_application_tl fat
WHERE 1 = 1 AND ffvs.flex_value_set_id = fdfc.flex_value_set_id
AND fdfc.descriptive_flexfield_name =
'$SRS$.' || fcp.concurrent_program_name
and fat.language =userenv('LANG')
AND fcp.application_id = fat.application_id
AND ffvs.flex_value_set_name = :p_valuesetname -- Enter value set name
/********************************************************************
*PURPOSE: To list information about Executable, Concurrent Program *
* and its Parameters *
*********************************************************************/
SELECT DISTINCT fcpl.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name" ,
fat.application_name ,
fl.meaning execution_method ,
fe.execution_file_name ,
fcp.output_file_type ,
fdfcuv.column_seq_num "Column Seq Number" ,
fdfcuv.end_user_column_name "Parameter Name" ,
fdfcuv.form_left_prompt "Prompt" ,
fdfcuv.enabled_flag " Enabled Flag" ,
fdfcuv.required_flag "Required Flag" ,
fdfcuv.display_flag "Display Flag" ,
fdfcuv.flex_value_set_id "Value Set Id" ,
ffvs.flex_value_set_name "Value Set Name" ,
flv.meaning "Default Type" ,
fdfcuv.default_value "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_lookups fl ,
fnd_executables fe ,
fnd_executables_tl fet ,
fnd_application_tl fat
WHERE 1 = 1
AND fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcp.enabled_flag = 'Y'
AND fcpl.user_concurrent_program_name LIKE 'Workflow Background Process' --<Your Concurrent Program Name>
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 fcpl.LANGUAGE = 'US'
AND flv.LANGUAGE(+) = 'US'
AND fl.lookup_type ='CP_EXECUTION_METHOD_CODE'
AND fl.lookup_code =fcp.execution_method_code
AND fe.executable_id = fcp.executable_id
AND fe.executable_id =fet.executable_id
AND fet.LANGUAGE = 'US'
AND fat.application_id =fcp.application_id
AND fat.LANGUAGE = 'US'
ORDER BY fdfcuv.column_seq_num;
5. How to find the latest version of a file on a given instance
SELECT sub.filename
, sub.VERSION
FROM (SELECT adf.filename filename
, afv.VERSION VERSION
, RANK () OVER (PARTITION BY adf.filename ORDER BY afv.version_segment1 DESC
, afv.version_segment2 DESC
, afv.version_segment3 DESC
, afv.version_segment4 DESC
, afv.version_segment5 DESC
, afv.version_segment6 DESC
, afv.version_segment7 DESC
, afv.version_segment8 DESC
, afv.version_segment9 DESC
, afv.version_segment10 DESC
, afv.translation_level DESC) AS rank1
FROM ad_file_versions afv
, (SELECT filename
, app_short_name
, subdir
, file_id
FROM ad_files
WHERE UPPER (filename) LIKE UPPER ('%&filename%')) adf
WHERE adf.file_id = afv.file_id) sub
WHERE rank1 = 1
ORDER BY 1
You can enter partial file names and the search is not case sensitive.
1. How to check if a patch is applied?
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) = upper('concurrent program')
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 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
List of responsibilities assigned to user
SELECT fu.user_name,
frt.responsibility_name,
furg.start_date,
furg.end_date
FROM fnd_user fu,
fnd_user_resp_groups_direct furg,
fnd_responsibility_vl frt
WHERE fu.user_id = furg.user_id
AND frt.responsibility_id = furg.responsibility_id
AND frt.application_id = furg.responsibility_application_id
AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE
AND NVL (frt.end_date, SYSDATE + 1) > SYSDATE
AND fu.user_name = upper( :p_user_name);
Concurrent Program and Executable Details
The following SQL query provide you executable file name ,top name , etc...
--Oracle Applications Query to get the actual concurrent program file executable if we know the concurrent program name
SELECT b.user_concurrent_program_name, b.concurrent_program_name,
a.user_executable_name,
DECODE (a.execution_method_code,
'I', 'PL/SQL Stored Procedure',
'H', 'Host',
'S', 'Immediate',
'J', 'Java Stored Procedure',
'K', 'Java concurrent program',
'M', 'Multi Language Function',
'P', 'Oracle reports',
'B', 'Request Set Stage Function',
'A', 'Spawned',
'L', 'SQL*Loader',
'Q', 'SQL*Plus',
'E', 'Pearl concurrent Programm',
'Unkown Type'
) TYPE,
a.execution_file_name, a.execution_file_path, a.application_name,
c.basepath
FROM fnd_executables_form_v a,
fnd_concurrent_programs_vl b,
fnd_application c
WHERE a.application_id = c.application_id
AND a.executable_id = b.executable_id
AND a.application_id = b.application_id
AND a.executable_id > 4
AND b.user_concurrent_program_name LIKE '%Sales%Bill%Out%'
Script to find Scheduled concurrent programs and request sets
SELECT
A.REQUEST_ID,
B.USER_NAME,
C.USER_CONCURRENT_PROGRAM_NAME,
C.ARGUMENT_TEXT
FROM
FND_CONCURRENT_REQUESTS A,
FND_USER B,
FND_CONC_REQ_SUMMARY_V C
WHERE
B.USER_ID = A.REQUESTED_BY
AND A.REQUEST_ID = C.REQUEST_ID
AND A.REQUESTED_START_DATE > SYSDATE
AND A.HOLD_FLAG = 'N'
AND A.STATUS_CODE IN ('Q','I')
AND B.USER_NAME LIKE '%'
AND A.DESCRIPTION LIKE '%'
ORDER BY
A.REQUEST_ID;
Input program name to get list of value sets using in this program
SELECT ffvs.flex_value_set_name,
fcp.user_concurrent_program_name,
fat.application_name,
fdfc.column_seq_num Seq,
fdfc.form_left_prompt "Parameter Name",
fdfc.enabled_flag Active
FROM FND_DESCR_FLEX_COL_USAGE_VL fdfc,
fnd_flex_value_sets ffvs,
fnd_concurrent_programs_vl fcp,
fnd_application_tl fat
WHERE 1 = 1 AND ffvs.flex_value_set_id = fdfc.flex_value_set_id
AND fdfc.descriptive_flexfield_name =
'$SRS$.' || fcp.concurrent_program_name
AND fat.language = USERENV ('LANG')
AND fcp.application_id = fat.application_id
AND fcp.user_concurrent_program_name LIKE :p_programsename -- Enter program name
;
Input value set name to get list of concurrent programs using this value set
SELECT ffvs.flex_value_set_name, fcp.user_concurrent_program_name,
fat.application_name,
fdfc.column_seq_num Seq,
fdfc.form_left_prompt "Parameter Name",
fdfc.enabled_flag Active
FROM FND_DESCR_FLEX_COL_USAGE_VL fdfc,
fnd_flex_value_sets ffvs,
fnd_concurrent_programs_vl fcp,
fnd_application_tl fat
WHERE 1 = 1 AND ffvs.flex_value_set_id = fdfc.flex_value_set_id
AND fdfc.descriptive_flexfield_name =
'$SRS$.' || fcp.concurrent_program_name
and fat.language =userenv('LANG')
AND fcp.application_id = fat.application_id
AND ffvs.flex_value_set_name = :p_valuesetname -- Enter value set name
/********************************************************************
*PURPOSE: To list information about Executable, Concurrent Program *
* and its Parameters *
*********************************************************************/
SELECT DISTINCT fcpl.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name" ,
fat.application_name ,
fl.meaning execution_method ,
fe.execution_file_name ,
fcp.output_file_type ,
fdfcuv.column_seq_num "Column Seq Number" ,
fdfcuv.end_user_column_name "Parameter Name" ,
fdfcuv.form_left_prompt "Prompt" ,
fdfcuv.enabled_flag " Enabled Flag" ,
fdfcuv.required_flag "Required Flag" ,
fdfcuv.display_flag "Display Flag" ,
fdfcuv.flex_value_set_id "Value Set Id" ,
ffvs.flex_value_set_name "Value Set Name" ,
flv.meaning "Default Type" ,
fdfcuv.default_value "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_lookups fl ,
fnd_executables fe ,
fnd_executables_tl fet ,
fnd_application_tl fat
WHERE 1 = 1
AND fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcp.enabled_flag = 'Y'
AND fcpl.user_concurrent_program_name LIKE 'Workflow Background Process' --<Your Concurrent Program Name>
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 fcpl.LANGUAGE = 'US'
AND flv.LANGUAGE(+) = 'US'
AND fl.lookup_type ='CP_EXECUTION_METHOD_CODE'
AND fl.lookup_code =fcp.execution_method_code
AND fe.executable_id = fcp.executable_id
AND fe.executable_id =fet.executable_id
AND fet.LANGUAGE = 'US'
AND fat.application_id =fcp.application_id
AND fat.LANGUAGE = 'US'
ORDER BY fdfcuv.column_seq_num;
5. How to find the latest version of a file on a given instance
SELECT sub.filename
, sub.VERSION
FROM (SELECT adf.filename filename
, afv.VERSION VERSION
, RANK () OVER (PARTITION BY adf.filename ORDER BY afv.version_segment1 DESC
, afv.version_segment2 DESC
, afv.version_segment3 DESC
, afv.version_segment4 DESC
, afv.version_segment5 DESC
, afv.version_segment6 DESC
, afv.version_segment7 DESC
, afv.version_segment8 DESC
, afv.version_segment9 DESC
, afv.version_segment10 DESC
, afv.translation_level DESC) AS rank1
FROM ad_file_versions afv
, (SELECT filename
, app_short_name
, subdir
, file_id
FROM ad_files
WHERE UPPER (filename) LIKE UPPER ('%&filename%')) adf
WHERE adf.file_id = afv.file_id) sub
WHERE rank1 = 1
ORDER BY 1
You can enter partial file names and the search is not case sensitive.
1. How to check if a patch is applied?
select * from ad_bugs
where bug_number = :bug_number;
select * from ad_applied_patches
where patch_name = :bug_number;
SELECT DISTINCT a.bug_number, e.patch_name, c.end_date, b.applied_flag
FROM ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d,
ad_applied_patches e
WHERE a.bug_id = b.bug_id
AND b.patch_run_id = c.patch_run_id
AND c.patch_driver_id = d.patch_driver_id
AND d.applied_patch_id = e.applied_patch_id
AND a.bug_number LIKE ':bug_number'
ORDER BY 1 DESC ;
Query to find Form Personalization
SELECT ffv.form_id "Form ID",
ffv.form_name "Form Name",
ffv.user_form_name "User Form Name",
ffv.description "Form Description",
ffcr.sequence "Sequence",
ffcr.description "Personalization Rule Name"
FROM fnd_form_vl ffv,
fnd_form_custom_rules ffcr
WHERE ffv.form_name = ffcr.form_name
ORDER BY ffv.form_name, ffcr.sequence;
-------------------------------------------------------------------------------
-- Query to find all responsibilities of a user
-------------------------------------------------------------------------------
SELECT fu.user_name "User Name",
frt.responsibility_name "Responsibility Name",
furg.start_date "Start Date",
furg.end_date "End Date",
fr.responsibility_key "Responsibility Key",
fa.application_short_name "Application Short Name"
FROM fnd_user_resp_groups_direct furg,
applsys.fnd_user fu,
applsys.fnd_responsibility_tl frt,
applsys.fnd_responsibility fr,
applsys.fnd_application_tl fat,
applsys.fnd_application fa
WHERE furg.user_id = fu.user_id
AND furg.responsibility_id = frt.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fa.application_id = fat.application_id
AND fr.application_id = fat.application_id
AND frt.language = USERENV('LANG')
AND UPPER(fu.user_name) = UPPER('AMOHSIN') -- <change it>
-- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
ORDER BY frt.responsibility_name;
Query to find Oracle Alert
The following query finds all enabled custom alerts. You can comment out the very last two lines (alr.enabled_flag and alr.created_by) to display all both enabled and disabled alerts.
-------------------------------------------------------------------------------
-- Query to find Custom Oracle Alert
-------------------------------------------------------------------------------
SELECT alr.application_id,
alr.alert_id,
alr.alert_name,
alr.start_date_active,
alr.description,
alr.sql_statement_text
FROM alr.alr_alerts alr
WHERE 1=1
AND alr.created_by <> 1 -- show only custom alerts
AND alr.enabled_flag = 'Y'; -- show only enabled alerts
Query to find tables which are having common columns is given below:
select a.column_name,a.table_name
from dba_tab_columns a,
dba_tab_columns b
where a.owner = 'TABLE-OWNER'
and b.owner = 'TABLE-OWNER'
and b.column_name = a.column_name
and a.column_name=upper('&column_name');
