Query to list the Oracle RICEW Objects

Pass the application short name &APP_SHORT_NAME parameter to find RICE objects for the particular application and include any exclusion characters in parameter &EXCLUSION_CHARACTERS :

SELECT 'Concurrent Program'                AS "Object Type",

       cp.user_concurrent_program_name     AS "RICE Name",

       cp.description,

       flv.meaning                         EXECUTION_TYPE,

       exe.executable_name                 Executable_Name,

       exe.execution_file_name             Execution_File_Name,

       appl.application_name

  FROM apps.fnd_application_vl          appl,

       apps.fnd_concurrent_programs_vl  cp,

       apps.fnd_executables             exe,

       apps.fnd_lookup_values_vl        flv

 WHERE     1 = 1

       AND appl.application_id = cp.application_id

       AND UPPER (cp.user_concurrent_program_name) LIKE '%&APP_SHORT_NAME%'

       AND UPPER (cp.user_concurrent_program_name) NOT LIKE '%&EXCLUSION_CHARACTERS%'

       AND cp.enabled_flag = 'Y'

       AND cp.executable_id = exe.executable_id

       AND exe.execution_method_code = flv.lookup_code

       AND flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'

UNION ALL

SELECT 'Alert'             AS "Object Type",

       alr.alert_name      AS "RICE Name",

       alr.description     AS "Description",

       NULL                EXECUTION_TYPE,

       NULL                Executable_Name,

       NULL                Execution_File_Name,

       appl.application_name

  FROM apps.fnd_application_vl appl, apps.alr_alerts alr

 WHERE     1 = 1

       AND appl.application_id = alr.application_id

       AND UPPER (alr.alert_name) LIKE '%&APP_SHORT_NAME%'

       AND alr.enabled_flag = 'Y'

       AND SYSDATE BETWEEN NVL (alr.start_date_active, SYSDATE - 1)

                       AND NVL (end_date_active, SYSDATE + 1)

UNION ALL

SELECT 'Form'                      AS "Object Type",

       frm.user_form_name          AS "RICE Name",

       frm.description             AS "Description",

       NULL                        EXECUTION_TYPE,

       ffv.function_name           Executable_Name,

       frm.form_name || '.fmb'     Execution_File_Name,

       appl.application_name

  FROM apps.fnd_application_vl     appl,

       apps.fnd_form_vl            frm,

       apps.fnd_form_functions_vl  ffv

 WHERE     1 = 1

       AND appl.application_id = frm.application_id

       AND appl.application_short_name = '&APP_SHORT_NAME'

       AND UPPER (frm.form_name) LIKE '%&APP_SHORT_NAME%'

       AND frm.form_id = ffv.form_id

UNION ALL

SELECT 'Web ADI'                AS "Object Type",

       intg.user_name           AS "RICE Name",

       NULL                     AS "Description",

       NULL                     EXECUTION_TYPE,

       intg.integrator_code     Executable_Name,

       NULL                     Execution_File_Name,

       appl.application_name

  FROM apps.fnd_application_vl  appl,

       apps.bne_integrators_vl  intg,

       apps.bne_interfaces_vl   intf,

       apps.bne_param_lists_vl  pl,

       apps.bne_attributes      att

 WHERE     1 = 1

       AND appl.application_id = intg.application_id

       AND intg.enabled_flag = 'Y'

       AND UPPER (intg.integrator_code) LIKE '&APP_SHORT_NAME'||'%'

       AND intg.integrator_code = intf.integrator_code(+)

       AND intf.upload_param_list_code = pl.param_list_code(+)

       AND pl.attribute_code = att.attribute_code(+)

UNION ALL

SELECT 'Workflow'              AS "Object Type",

       wfit.display_name       AS "RICE Name",

       wfit.description        AS "Description",

       NULL                    EXECUTION_TYPE,

       wfit.Name               Executable_Name,

       wfit.Name || '.wft'     Execution_File_Name,

       appl.application_name

  FROM apps.fnd_application_vl appl, apps.wf_item_types_vl wfit

 WHERE     1 = 1

       AND wfit.name LIKE

                  DECODE (appl.application_short_name,

                          'OFA', 'FA',

                          appl.application_short_name)

               || '%'

       AND appl.application_name NOT LIKE '%Obsolete%'

       AND appl.application_name NOT IN

               ('Sourcing', 'iSupplier Portal', '&APP_SHORT_NAME'||'_AP_APPS')

UNION ALL

SELECT 'AOL Forms Custom Rules'                       AS "Object Type",

       ffv.user_function_name                         AS "RICE Name",

       FFCR.SEQUENCE || ' - ' || FFCR.DESCRIPTION     AS "Description",

       'Form Personalization'                         EXECUTION_TYPE,

       NULL                                           Executable_Name,

       FFCR.FORM_NAME                                 Execution_File_Name,

       fav.application_name

  FROM apps.fnd_form_custom_rules  ffcr,

       apps.fnd_form_vl            ff,

       apps.fnd_application_vl     fav,

       apps.fnd_form_functions_vl  ffv

 WHERE     ffcr.form_name = ff.form_name

       AND ff.application_id = fav.application_id

       AND ffcr.function_name = ffv.function_name

       AND ffcr.enabled = 'Y'

       AND (   UPPER (ffcr.description) LIKE '%&APP_SHORT_NAME%'

            OR ffcr.form_name LIKE '%&APP_SHORT_NAME%'

            OR ffcr.function_name LIKE'%&APP_SHORT_NAME%')

       AND ffcr.created_by NOT IN (122, 0)