Queries for extracting data in view of upgrade project

1. Concurrent program details with executable and execution file name.

SELECT substr (fcpv.USER_CONCURRENT_PROGRAM_NAME, -5) "CnI number",

       fa.APPLICATION_SHORT_NAME,

       fcpv.concurrent_program_name,

       fcpv.USER_CONCURRENT_PROGRAM_NAME,

       fcpv.DESCRIPTION,

       fcpv.ENABLED_FLAG,

       DECODE (fe.execution_method_code,

               'P', 'Oracle Reports',

               'E', 'Perl Concurrent Program',

               'Q', 'SQL*Plus',

               'L', 'SQL*Loader',

               'A', 'Spawned',

               'B', 'Request Set Stage Function',

               'I', 'PL/SQL Stored Procedure',

               'K', 'Java Concurrent Program',

               'J', 'Java Stored Procedure',

               'S', 'Immediate',

               'H', 'Host')

          Execution_Method,

       fe.executable_name,

       fe.EXECUTION_FILE_NAME

  FROM fnd_concurrent_programs_vl fcpv,

       fnd_application fa,

       fnd_executables fe

 WHERE     fa.application_id = fcpv.application_id

       AND fe.executable_id = fcpv.executable_id

       AND fcpv.executable_application_id = fe.application_id

       AND (   UPPER (fcpv.concurrent_program_name) LIKE 'XX%'

            OR UPPER (fcpv.USER_CONCURRENT_PROGRAM_NAME) LIKE 'XX%'

            OR UPPER (fa.application_short_name) LIKE 'XX%')

2. Valueset used in the Concurrent program.

Select  xddt.Data_source_name "TEMPLATE NAME"

     --,xl.lob_type

       ,xl.file_name "RTF FILE NAME"

     ,xtb.template_code

      ,fcr.request_id  "REQUEST ID"

from   xdo_lobs xl

      ,xdo_templates_b xtb

      ,xdo_ds_definitions_tl xddt

      ,fnd_concurrent_requests fcr

      ,fnd_concurrent_programs_tl fcpt

      ,fnd_concurrent_programs fcp

where xl.application_short_name = xtb.application_short_name

and   xl.lob_code = xtb.template_code

and   xtb.data_source_code = xddt.data_source_code

and   fcr.concurrent_program_id = fcpt.concurrent_program_id

and   fcp.concurrent_program_id = fcpt.concurrent_program_id

and   xddt.data_source_code = fcp.concurrent_program_name

and   xl.lob_type='TEMPLATE_SOURCE'

--and   fcr.request_id='5095760' --PASS REQUEST ID

and (upper(xddt.Data_source_name) like 'XX%' or upper(xl.file_name) like 'XX%')

3.  RTF template code and file name.

Select  xddt.Data_source_name "TEMPLATE NAME"

     --,xl.lob_type

       ,xl.file_name "RTF FILE NAME"

     ,xtb.template_code

      ,fcr.request_id  "REQUEST ID"

from   xdo_lobs xl

      ,xdo_templates_b xtb

      ,xdo_ds_definitions_tl xddt

      ,fnd_concurrent_requests fcr

      ,fnd_concurrent_programs_tl fcpt

      ,fnd_concurrent_programs fcp

where xl.application_short_name = xtb.application_short_name

and   xl.lob_code = xtb.template_code

and   xtb.data_source_code = xddt.data_source_code

and   fcr.concurrent_program_id = fcpt.concurrent_program_id

and   fcp.concurrent_program_id = fcpt.concurrent_program_id

and   xddt.data_source_code = fcp.concurrent_program_name

and   xl.lob_type='TEMPLATE_SOURCE'

--and   fcr.request_id='5095760' --PASS REQUEST ID

and (upper(xddt.Data_source_name) like 'XX%' or upper(xl.file_name) like 'XX%')

4. Forms 

select fa.application_short_name, ff.USER_FORM_NAME,ff.form_name  from apps.fnd_form_vl ff,fnd_application fa where form_name like 'XX%'

and fa.application_id = ff.application_id;

5. Functions

select fa.application_short_name,ff.USER_FUNCTION_NAME,ff.FUNCTION_NAME,ff.PARAMETERS, ff.type from apps.fnd_form_functions_vl ff,fnd_application fa 

where (upper(ff.function_name) like 'XX%' or upper(fa.application_short_name) like 'XX%')

and fa.application_id = ff.application_id;

6. Lookups

select fa.APPLICATION_SHORT_NAME , flv.LOOKUP_TYPE, flv.LOOKUP_CODE,flv.MEANING, flv.ENABLED_FLAG, flv.START_DATE_ACTIVE,flv.END_DATE_ACTIVE from fnd_lookup_values  flv, fnd_application fa where LOOKUP_TYPE like 'XX%'

and fa.application_id = flv.view_application_id;

7.Valuesets

SELECT FFVS.FLEX_VALUE_SET_NAME,

       ffvs.DESCRIPTION,

       DECODE (ffvs.VALIDATION_TYPE,

               'N', 'None',

               'I', 'Independent',

               'D', 'Dependent',

               'F', 'Table',

               'U', 'Special',

               'P', 'Pair',

               'Y', 'Translatable Independent',

               'X', 'Translatable Dependent')

          Valueset_type,

       FFV.FLEX_VALUE

  FROM FND_FLEX_VALUE_SETS FFVS, FND_FLEX_VALUES FFV

 WHERE     FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID

       AND FFVS.FLEX_VALUE_SET_NAME LIKE 'XX%'

8. Request set

SELECT fa.APPLICATION_SHORT_NAME,

       frsv.USER_REQUEST_SET_NAME,

   frsv.REQUEST_SET_NAME,fcpv.user_concurrent_program_name,frsv.CONCURRENT_PROGRAM_ID

  FROM apps.fnd_request_sets_vl frsv, fnd_application fa, fnd_concurrent_programs_vl fcpv

 WHERE frsv.application_id = fa.application_id

 and fcpv.concurrent_program_id(+) = frsv.CONCURRENT_PROGRAM_ID

 and (upper(request_set_name) like 'XX%' or fa.application_short_name like 'XX%');

9. Request Group

select frg.request_group_name,fa.application_short_name from fnd_request_groups frg, fnd_application fa

where fa.application_id = frg.application_id

and frg.request_group_name like 'XX%'

and fa.application_short_name like 'XX%';

10. MENU

select fmv.menu_name, fmv.user_menu_name from fnd_menus_vl fmv;

11. Responsibility

select fa.application_short_name , frv.responsibility_name, frv.responsibility_key from fnd_responsibility_vl frv, fnd_application fa

where frv.application_id= fa.application_id

and fa.application_short_name like 'XX%';

12. Responsibility, Request group and menu

select fa.application_short_name "responsiblity application", frv.responsibility_name, frv.responsibility_key , fmv.user_menu_name

, frg.request_group_name, fa1.application_short_name "req grp application"

from fnd_responsibility_vl frv, fnd_application fa, fnd_menus_vl fmv, fnd_request_groups frg, fnd_application fa1

where frv.application_id= fa.application_id

and fmv.menu_id= frv.menu_id

and frg.request_group_id =frv.request_group_id

and frg.application_id = frv.group_application_id

and fa1.application_id= frg.application_id;

13. Concurrent program attached to responsiblity

SELECT frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg, fnd_request_group_units frgu,
    fnd_concurrent_programs_tl fcpt
    WHERE frt.responsibility_id = fr.responsibility_id
    AND frg.request_group_id = fr.request_group_id
    AND frgu.request_group_id = frg.request_group_id
    AND fcpt.concurrent_program_id = frgu.request_unit_id
    AND frt.LANGUAGE = USERENV('LANG')
    AND fcpt.LANGUAGE = USERENV('LANG')
    AND fcpt.user_concurrent_program_name = :conc_prg_name
    ORDER BY 1,2,3,4