List of Responsibilities associated with Form Functions
SELECT DISTINCT responsibility_id
FROM apps.fnd_responsibility_vl a
WHERE a.end_date IS NULL
AND a.menu_id IN (
SELECT menu_id
FROM apps.fnd_menu_entries_vl
START WITH menu_id IN (
SELECT menu_id
FROM apps.fnd_menu_entries_vl
WHERE function_id IN (
SELECT function_id
FROM applsys.fnd_form_functions a
WHERE function_name =
pc_function_name))
CONNECT BY PRIOR menu_id = sub_menu_id)
AND a.responsibility_id NOT IN (
SELECT responsibility_id
FROM apps.fnd_responsibility_vl
WHERE responsibility_id IN (
SELECT responsibility_id
FROM applsys.fnd_resp_functions resp
WHERE action_id IN (
SELECT function_id
FROM applsys.fnd_form_functions a
WHERE function_name =
pc_function_name)))
AND a.responsibility_id NOT IN (
SELECT responsibility_id
FROM apps.fnd_responsibility_vl
WHERE responsibility_id IN (
SELECT responsibility_id
FROM applsys.fnd_resp_functions resp
WHERE action_id IN (
SELECT menu_id
FROM apps.fnd_menu_entries_vl
START WITH menu_id IN (
SELECT menu_id
FROM apps.fnd_menu_entries_vl
WHERE function_id IN (
SELECT function_id
FROM applsys.fnd_form_functions a
WHERE function_name =
pc_function_name))
CONNECT BY PRIOR menu_id =
sub_menu_id)))
ORDER BY responsibility_id;
Form Personalization Listing
SELECT ffft.user_function_name "User Form Name", ffcr.SEQUENCE,
ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,
ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query,
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ffcr.created_by) "Created By "
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;
List of Menus associated with Responsibilities
SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = ‘20538’
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US';
Profile option with Modification Date and User
SELECT t.user_profile_option_name, profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date - v.last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
Query to Validate Accounting Segment
SELECT COUNT (1)
INTO lc_segment1
FROM gl_sets_of_books GSOB, fnd_id_flex_structures_vl FIFST, fnd_id_flex_segments_vl FIFSE, fnd_flex_value_sets FFVS, fnd_flex_values_vl FFV WHERE
GSOB.chart_of_accounts_id = FIFST.id_flex_num
AND FIFST.id_flex_num = FIFSE.id_flex_num
AND FIFST.id_flex_code = FIFSE.id_flex_code
AND FIFSE.flex_value_set_id = FFVS.flex_value_set_id
AND FFVS.flex_value_set_id = FFV.flex_value_set_id
AND FIFSE.application_column_name = 'SEGMENT1'
AND FFV.flex_value = rec_cur_new.company -- Company Segment Value
AND GSOB.set_of_books_id = lc_sob_id; -- Ledger ID
List of Concurrent Programs Executed in last 2 Years
SELECT DISTINCT FCP.user_concurrent_program_name, FCP.concurrent_program_name, FCP.description, FAV.application_short_name, FAV.application_name
FROM fnd_concurrent_Requests FCR, fnd_concurrent_programs_vl FCP, fnd_application_vl FAV
WHERE FCR.concurrent_program_id = FCP.concurrent_program_id AND FCP.application_id = FAV.application_id AND TRIM (FCR.request_date) >= ADD_MONTHS (SYSDATE,
-24)
List of Oracle Reports
SELECT DISTINCT FAV.application_name, FCP.user_concurrent_program_name, FCP.concurrent_program_name, FCP.description
FROM fnd_concurrent_programs_vl fcp, FND_EXECUTABLES_FORM_V fef, fnd_application_vl FAV
WHERE fcp.executable_id = fef.executable_id AND fcp.application_id = FAV.application_id AND fcp.concurrent_program_name like 'XX%' AND
fef.execution_method_code = 'P'
List of Custom Profile Options
SELECT DISTINCT fav.application_name, fpo.profile_option_name, fpo.creation_date FROM fnd_profile_options fpo, fnd_application_vl FAV
WHERE FPO.application_id = FAV.application_id AND fpo.profile_option_name LIKE 'XX%'
List of Custom Request Groups
SELECT DISTINCT fav.application_name, frg.request_group_name, frg.description, frg.request_group_code FROM fnd_request_groups FRG, FND_APPLICATION_VL fav
WHERE frg.application_id = fav.application_id AND frg.request_group_code LIKE 'XX%'
List of Custom Responsibilities
SELECT DISTINCT fav.application_name, frv.responsibility_key, frv.responsibility_name, frv.description FROM fnd_responsibility_vl FRV, fnd_application_vl FAV
WHERE FRV.application_id = FAV.application_id AND FRV.responsibility_key LIKE 'XX%'
List of Custom Forms
SELECT DISTINCT fav.application_name, ffv.form_name, ffv.user_form_name, ffv.description FROM fnd_form_vl ffv, FND_APPLICATION_VL fav
WHERE ffv.application_id = fav.application_id AND form_name LIKE 'XX%'
List of Custom Applications
SELECT APPLICATION_ID, APPLICATION_SHORT_NAME, BASEPATH, APPLICATION_NAME, DESCRIPTION FROM FND_APPLICATION_VL
WHERE APPLICATION_SHORT_NAME LIKE 'XX%'
/
No comments:
Post a Comment