Thursday, August 11, 2016

Query to get the list of form Personalization


Select Distinct ffcr.Id, ffcr.Form_Name, ffcr.Enabled,
       fft.User_Form_Name,  fat.Application_Name, ffcr.Description,
       ffca.Action_Type, ffca.Enabled, ffca.Object_Type,
       ffca.message_type, ffca.message_text
 from FND_FORM_CUSTOM_RULES ffcr,
      FND_FORM ff,
      FND_FORM_TL fft,
      Fnd_Application_Tl fat,
      Fnd_Form_Custom_Actions ffca 
where ffcr.form_name = ff.form_name
 And ff.Form_Id = fft.Form_Id
 And ff.Application_Id = fat.Application_Id
 And fft.User_Form_Name Like 'XXCUST%'
 And ffcr.Enabled ='Y'
 and ffcr.id = ffca.rule_id;

Query to get the list of Custom objects (TABLE, VIEW, PROCEDURE, FUNCTION AND PACKAGE)


select object_name, object_type
 from all_objects
 where 1=1
 and upper(object_type) IN ('FUNCTION','PACKAGE','PROCEDURE', 'VIEW', 'TABLE')
 and status='VALID'
 and object_name like 'XXCUST%'
order by 2;

Query to get List of Custom Form List

SELECT  DISTINCT forms.form_name,
          formstl.user_form_name,
          func.function_name,
          func.user_function_name,
          fm.menu_name,
          menu.prompt menu_prompt,
          menu.description,
          restl.responsibility_name
   FROM   fnd_form FORMS,
          fnd_form_tl FORMSTL,
          fnd_form_functions_VL FUNC,
          fnd_menu_entries_VL MENU,
          FND_MENUS FM,
          fnd_responsibility RES,
          fnd_responsibility_tl RESTL
  WHERE   1=1
          and forms.form_id = formstl.form_id
          and func.form_id = forms.form_id
          and menu.function_id = func.function_id
          and menu.menu_id=fm.menu_id
          and res.menu_id = menu.menu_id
          and res.responsibility_id = restl.responsibility_id
          and UPPER(forms.form_name) like '%XXCUST%'      
          order by 1;

Query to get List of Custom Concurrent Programs


SELECT DISTINCT fcp.user_concurrent_program_name "Concurrent Program Name",
 fa.application_name,
 fcp.concurrent_program_name conc_short_name,
 decode( fef.execution_method_code, 'H','Host',
 'I', 'PLSQL Stored Procedure',
 'P', 'Report',
 'L', 'SQL Loader',
 'Q','SQL*Plus',
 'A','Spawned',
 'K','Java Concurrent Program',
 'B','Request Set Stage Function',
 'S','Immediate',
 'J','Java Stored Procedure',
 'M','Multi-Language Function',
 fef.execution_method_code) "execution method",
 fef.executable_name,
 fcp.description "Concurrent Program Description",
 fef.executable_name"Executable Name",
 fef.description "Executable Description",
 fef.execution_file_name ,
 fcp.enabled_flag
 ,fcp.creation_date
FROM fnd_executables_form_v fef,
     fnd_concurrent_programs_VL fcp,
     fnd_application_tl fa
WHERE fcp.application_id=fef.application_id
  AND fa.application_id=fcp.application_id
  AND fef.executable_id=fcp.executable_id
  AND UPPER(fcp.user_concurrent_program_name) like '%XXCUST%'
ORDER BY 4;