Useful SQL Queries to validate FND(Foundation)(Conc. Program, Req Set etc.,) and XDO(Xml Data Objects) Objects(RTF file, Template)

-- Select statement to identify which a concurrent_program

-- Parameters:

--        p_conc_prog - Concurrent Program Short Name

 

SELECT cp.user_concurrent_program_name, cp.concurrent_program_name

  FROM fnd_concurrent_programs_vl cp

 WHERE cp.concurrent_program_name = '&p_conc_prog';

 

-- Select statement to identify which request group is associated to a particular concurrent_program

-- Parameters:

--        p_conc_prog - Concurrent Program Name

--        p_req_group - Request Group Name

 

SELECT cp.user_concurrent_program_name,

       cp.concurrent_program_name,

       rg.request_group_name

  FROM fnd_request_group_units rgu,

       fnd_request_groups rg,

       fnd_concurrent_programs_vl cp

 WHERE     cp.concurrent_program_id = rgu.request_unit_id

       AND rg.request_group_id = rgu.request_group_id

       AND cp.concurrent_program_name = '&p_conc_prog'

       AND rg.request_group_name = '&p_req_group';

 

  -- Select statement to identify Request set and it's corresponding stages and stage links for a particular request set

-- Parameters:

--        p_req_set_short_name - Request Set Name

 

SELECT rs.request_set_name,

       rs.user_request_set_name,

       sv.stage_name,

       sv.request_set_stage_id,

       sv.success_link,

       sv.success_link_name,

       sv.warning_link,

       sv.warning_link_name,

       sv.error_link,

       sv.error_link_name,

       sv.display_sequence,

       sv.user_executable_name,

       sv.user_stage_name

  FROM apps.fnd_request_sets_vl rs, fnd_req_set_stages_form_v sv

 WHERE rs.request_set_name = UPPER('&p_req_set_short_name')

       AND sv.request_set_id = rs.request_set_id;

 

xx_ar_std_register_trans

 

-- Select statement to identify which request group is associated to a particular request set

-- Parameters:

--        p_req_set - Request Set Name

--        p_req_group - Request Group Name

 

SELECT cp.request_set_name, cp.user_request_set_name, rg.request_group_name

  FROM fnd_request_group_units rgu,

       fnd_request_groups rg,

       fnd_request_sets_vl cp

 WHERE     cp.request_set_id = rgu.request_unit_id

       AND rg.request_group_id = rgu.request_group_id

       AND cp.request_set_name = '&p_req_set'

       AND rg.request_group_name = '&p_req_group';

 

  --Select statement to validate RTF Template

  --Parameter

  --     p_template_code - Template Code

 

SELECT xl.lob_type,

       xl.application_short_name,

       xl.lob_code,

       xl.language,

       xl.file_name,

       xl.xdo_file_type,

       xl.file_content_type,

       xl.file_data,

       xl.program,

       xl.territory,

       xt.template_code,

       xt.data_source_code

  FROM xdo_lobs xl, xdo_templates_b xt

 WHERE xt.template_code = xl.lob_code(+)

       AND template_code = '&p_template_code';

 

--Select statement to validate Forms Personalization

-- Parameter

--    p_func_name - Form Function Name

 

SELECT ff.user_function_name,

       ff.user_function_name,

       ff.function_id,

       cr.description,

       cr.trigger_event,

       cr.trigger_object,

       cr.condition,

       cr.sequence,

       cr.form_name

  FROM fnd_form_custom_rules cr, fnd_form_functions_vl ff

 WHERE ff.function_name = cr.function_name

       AND ff.function_name = '&p_func_name';

 

 -- Select statement to validate Custom Profile

-- Parameter:

--       p_prof_name -- Profile Option Name

 

SELECT po.profile_option_name,

       po.user_profile_option_name,

       pov.profile_option_value

  FROM fnd_profile_options_vl po, fnd_profile_option_values pov

 WHERE pov.profile_option_id = po.profile_option_id

       AND po.profile_option_name LIKE UPPER ('&p_prof_name%');

 

-- Select statement to identify association of Form function to a particular Menu as a menu entry

-- Parameters:

--        p_menu_name - Menu Name

--        p_function_name - Function Name

 

SELECT mv.menu_name,

       ff.function_name,

       ff.user_function_name,

       me.prompt,

       me.description Entry_Description

  FROM fnd_menus_vl mv, fnd_menu_entries_vl me, fnd_form_functions_vl ff

 WHERE     mv.menu_id = me.menu_id

       AND mv.menu_name = '&p_menu_name'

       AND ff.function_id = me.function_id

       AND ff.function_name = '&p_function_name';