Oracle Apps 11i - R12 Migration – Queries for Identifying & Migrating Components

 

1. Responsibilities Listing

SELECT (SELECT application_short_name FROM fnd_application fa WHERE fa.application_id = frt.application_id) application, frt.responsibility_id, frt.responsibility_name FROM apps.fnd_responsibility_tl frt;

 

2. Menus Listing

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';

3. Submenu And Function Listing

SELECT c.prompt, c.description FROM apps.fnd_menus_tl a, fnd_menu_entries_tl c WHERE a.menu_id = c.menu_id AND a.user_menu_name = 'F4 UK PAY Navigator';

4. User And Assigned Responsibility Listing

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR (r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) application FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id(+) = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50), SUBSTR (r.responsibility_name, 1, 60);

5. Responsibility And Assigned Request Group Listing

SELECT responsibility_name responsibility, request_group_name, frg.description FROM fnd_request_groups frg, fnd_responsibility_vl frv WHERE frv.request_group_id = frg.request_group_id ORDER BY responsibility_name

6. 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;

77. Forms 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;

8. Patch Level Listing

SELECT a.application_name, DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status, patch_level FROM apps.fnd_application_vl a, apps.fnd_product_installations b WHERE a.application_id = b.application_id;

9. Function Listing

SELECT function_id, user_function_name, creation_date, description FROM applsys.fnd_form_functions_tl y order by user_function_name;

10. Request Attached To Responsibility Listing

SELECT responsibility_name , frg.request_group_name, fcpv.user_concurrent_program_name, fcpv.description FROM fnd_request_groups frg, fnd_request_group_units frgu, fnd_concurrent_programs_vl fcpv, fnd_responsibility_vl frv WHERE frgu.request_unit_type = 'P' AND frgu.request_group_id = frg.request_group_id AND frgu.request_unit_id = fcpv.concurrent_program_id AND frv.request_group_id = frg.request_group_id ORDER BY responsibility_name;

11. Request Listing Application Wise

SELECT fa.application_short_name, fcpv.user_concurrent_program_name, description, DECODE (fcpv.execution_method_code, 'B', 'Request Set Stage Function', 'Q', 'SQL*Plus', 'H', 'Host', 'L', 'SQL*Loader', 'A', 'Spawned', 'I', 'PL/SQL Stored Procedure', 'P', 'Oracle Reports', 'S', 'Immediate', fcpv.execution_method_code ) exe_method, output_file_type, program_type, printer_name, minimum_width, minimum_length, concurrent_program_name, concurrent_program_id FROM fnd_concurrent_programs_vl fcpv, fnd_application fa WHERE fcpv.application_id = fa.application_id ORDER BY description

12. Count Module Wise Reports

SELECT fa.application_short_name, DECODE (fcpv.execution_method_code, 'B', 'Request Set Stage Function', 'Q', 'SQL*Plus', 'H', 'Host', 'L', 'SQL*Loader', 'A', 'Spawned', 'I', 'PL/SQL Stored Procedure', 'P', 'Oracle Reports', 'S', 'Immediate', fcpv.execution_method_code ) exe_method, COUNT (concurrent_program_id) COUNT FROM fnd_concurrent_programs_vl fcpv, fnd_application fa WHERE fcpv.application_id = fa.application_id GROUP BY fa.application_short_name, fcpv.execution_method_code ORDER BY 1;

13. Request Status Listing

SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name , f.actual_start_date actual_start_date , f.actual_completion_date actual_completion_date, floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600) || ' HOURS ' || floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60) || ' MINUTES ' || round((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 - (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) || ' SECS ' time_difference , DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.descriptio n||']',p.concurrent_program_name) concurrent_program_name , decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase , f.status_code FROM apps.fnd_concurrent_programs p , apps.fnd_concurrent_programs_tl pt , apps.fnd_concurrent_requests f WHERE f.concurrent_program_id = p.concurrent_program_id and f.program_application_id = p.application_id and f.concurrent_program_id = pt.concurrent_program_id and f.program_application_id = pt.application_id AND pt.language = USERENV('Lang') and f.actual_start_date is not null ORDER by f.actual_completion_date-f.actual_start_date desc;

14. User And Responsibility Listing

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR (r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) application FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id(+) = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id and a.application_name = 'Purchasing' ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50), SUBSTR (r.responsibility_name, 1, 60)

15. Applied Patch Listing

SELECT patch_name, patch_type, maint_pack_level, creation_date FROM applsys.ad_applied_patches ORDER BY creation_date DESC

16. Query To Find Responsibility of a Concurrent Program

SELECT fcpl.user_concurrent_program_name "REPORT NAME", fnrtl.responsibility_name, frg.request_group_name, fcp.concurrent_program_name "CONCURRENT PROGRAM SHORT NAME" FROM apps.fnd_request_groups frg, apps.fnd_request_group_units frgu, apps.fnd_concurrent_programs fcp, apps.fnd_concurrent_programs_tl fcpl, apps.fnd_executables fe, apps.fnd_responsibility fnr, apps.fnd_responsibility_tl fnrtl WHERE frg.application_id = frgu.application_id AND frg.request_group_id = frgu.request_group_id AND frg.request_group_id = fnr.request_group_id AND frg.application_id = fnr.application_id AND fnr.responsibility_id = fnrtl.responsibility_id AND frgu.request_unit_id = fcp.concurrent_program_id AND frgu.unit_application_id = fcp.application_id AND fcp.concurrent_program_id = fcpl.concurrent_program_id AND fe.execution_method_code LIKE 'P' --'Oracle Reports' AND fcp.executable_id = fe.executable_id AND fe.application_id = fcp.executable_application_id AND fcpl.user_concurrent_program_name LIKE :conc_prog_name --AND fnrtl.responsibility_name LIKE '6010 DPW FUJ A%' -- Example Resp. Name : Inventory, Vision Operations (USA) -- AND fnrtl.LANGUAGE = 'US' --AND fcpl.LANGUAGE = 'US';

17. Query To Find Parameters and ValueSets of a Concurrent Program

SELECT fcpl.user_concurrent_program_name "Concurrent Program Name", fcp.concurrent_program_name "Short Name", 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 WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id AND fcpl.user_concurrent_program_name = :conc_prg_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 = USERENV ('LANG') AND flv.LANGUAGE(+) = USERENV ('LANG') ORDER BY fdfcuv.column_seq_num;

18. Query To Find Discoverer Reports – Last Run Details

SELECT DISTINCT docs.doc_created_by doc_owner, docs.doc_name doc_name, (SELECT TRUNC (MAX (dm.qs_created_date)) FROM eul5_qpp_stats dm WHERE dm.qs_doc_name = stats.qs_doc_name) last_run FROM eul5_qpp_stats stats, eul5_documents docs WHERE docs.doc_name NOT LIKE 'Workbook%' AND stats.qs_created_date(+) > :cutoff AND docs.doc_created_date < :cutoff AND docs.doc_name = stats.qs_doc_name(+) HAVING :run_date >= NVL ((SELECT TRUNC (MAX (dm.qs_created_date)) FROM eul5_qpp_stats dm WHERE dm.qs_doc_name = stats.qs_doc_name), '01-JAN-2000' ) GROUP BY docs.doc_created_by, docs.doc_name, stats.qs_doc_name ORDER BY last_run DESC, docs.doc_created_by, docs.doc_name;

19. Query to Find Output Type of a Concurrent Program

SELECT --fcpv.application_id,fcpv.executable_id, fe.executable_name, fcpv.user_concurrent_program_name, fcpv.output_file_type FROM fnd_concurrent_programs_vl fcpv, fnd_executables fe, fnd_application fa WHERE fcpv.executable_id = fe.executable_id --AND fcpv.output_file_type = 'XML' AND fcpv.application_id = fa.application_id AND fa.basepath LIKE ('%') AND fa.application_short_name LIKE ('%') AND fcpv.user_concurrent_program_name = :concurrent_program_name

20. Query to get details of XML Publisher Templates

SELECT * FROM XDO_TEMPLATES_VL WHERE END_DATE IS NOT NULL WHERE TEMPLATE_CODE =:temp_code; select * from xdo_lobs where lob_code =:template_code;

21. Query to get the details of Valueset

SELECT flex_value_set_name,application_table_name, value_column_name, id_column_name, meaning_column_name, additional_where_clause FROM fnd_flex_validation_tables ffvt,fnd_flex_value_sets ffvs WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id and flex_value_set_name=:ValueSetName