Monday, July 1, 2013

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

No comments:

Post a Comment