Script to generate the FND Load Script

/* Formatted on 6/24/2014 12:20:16 PM (QP5 v5.115.810.9015) */
CREATE OR REPLACE VIEW xx_generate_fnd_scriptc_v (
   owner,
   object_name,
   object_description,
   last_update_date,
   object_type,
   source,
   download_fnd_script,
   upload_fnd_script
   )
AS
   ---------------------------------------------------
   --- ALL_OBJECTS
   ---------------------------------------------------
   SELECT owner,
          object_name,
          object_type || ' ' || owner || '.' || object_name object_description,
          last_ddl_time last_update_date,
          object_type,
          'ALL_OBJECTS' source,
             'sqlplus apps/$PASSWORD @admin/sql/'
          || LOWER (REPLACE (object_type, ' ', '_'))
          || ' '
          || object_name
             download,
          'sqlplus apps/$PASSWORD @admin/sql/' || object_name || '.sql' upload
   FROM all_objects
   UNION ALL
   ---------------------------------------------------
   --- PROGRAM
   ---------------------------------------------------
   SELECT fa.application_short_name,
          fcp.concurrent_program_name,
          fcpt.user_concurrent_program_name,
          fcp.last_update_date,
          'PROGRAM',
          'FND_CONCURRENT_PROGRAMS',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct'
          || fcp.concurrent_program_name
          || '_CP.ldt PROGRAM APPLICATION_SHORT_NAME="'
          || fa.application_short_name
          || '" CONCURRENT_PROGRAM_NAME="'
          || fcp.concurrent_program_name
          || '"'
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct'
          || fcp.concurrent_program_name
          || '_CP.ldt'
             upload
   FROM fnd_concurrent_programs fcp,
        fnd_concurrent_programs_tl fcpt,
        fnd_application fa
   WHERE     fcpt.concurrent_program_id = fcp.concurrent_program_id
         AND fcpt.language = 'US'
         AND fa.application_id = fcp.application_id
   UNION ALL
   ---------------------------------------------------
   --- FORM
   ---------------------------------------------------
   SELECT fa.application_short_name,
          ff.form_name,
          fft.user_form_name,
          ff.last_update_date,
          'FORM',
          'FND_FORM',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct '
          || ff.form_name
          || '_FRM.ldt FORM APPLICATION_SHORT_NAME="'
          || fa.application_short_name
          || '" FORM_NAME="'
          || ff.form_name
          || '"'
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct '
          || ff.form_name
          || '_FRM.ldt'
             upload
   FROM fnd_form ff, fnd_form_tl fft, fnd_application fa
   WHERE     fft.form_id = ff.form_id
         AND fft.language = 'US'
         AND fa.application_id = ff.application_id
   UNION ALL
   ---------------------------------------------------
   --- EXECUTABLES TYPES
   ---------------------------------------------------
   SELECT fa.application_short_name,
          fe.execution_file_name,
          fet.user_executable_name,
          fe.last_update_date,
          'EXECUTABLE '
          || DECODE (fe.execution_method_code,
                'H', 'HOST',
                'S', 'IMMEDIATE',
                'J', 'JAVA STORED PROC',
                'K', 'JAVA CONC PROG',
                'M', 'MULTI LANG FUNC',
                'I', 'PL/SQL',
                'B', 'REQ SET STAGE',
                'A', 'SPAWNED',
                'P', 'REPORT',
                'Q', 'SQL*PLUS',
                'L', 'SQL*LOADER',
                'E', 'PERL',
                '*' || fe.execution_method_code || ' ' || execution_file_name),
          'FND_EXECUTABLES',
          '' download,
          '' upload
   FROM fnd_executables fe, fnd_executables_tl fet, fnd_application fa
   WHERE     fet.executable_id = fe.executable_id
         AND fet.language = 'US'
         AND fa.application_id = fe.application_id
   UNION ALL
   ---------------------------------------------------
   --- EXECUTABLE
   ---------------------------------------------------
   SELECT fa.application_short_name,
          fe.executable_name,
          fet.user_executable_name,
          fe.last_update_date,
          'EXECUTABLE',
          'FND_EXECUTABLES',
          '' download,
          '' upload
   FROM fnd_executables fe, fnd_executables_tl fet, fnd_application fa
   WHERE     fet.executable_id = fe.executable_id
         AND fet.language = 'US'
         AND fa.application_id = fe.application_id
   UNION ALL
   ---------------------------------------------------
   --- WORKFLOW
   ---------------------------------------------------
   SELECT 'APPS',
          wit.name,
          witl.display_name,
          wfa.last_update_date,
          'WORKFLOW',
          'WF_ITEM_TYPES',
             'WFLOAD apps/$PASSWORD 0 Y DOWNLOAD wf/'
          || wit.name
          || '_'
          || TO_CHAR (wfa.last_update_date, 'ddmonyyyy')
          || '.wft '
          || wit.name
             download,
             'WFLOAD apps/$PASSWORD 0 Y UPLOAD wf/'
          || wit.name
          || '_'
          || TO_CHAR (wfa.last_update_date, 'ddmonyyyy')
          || '.wft '
             upload
   FROM (SELECT wpa.activity_item_type, MAX (wfa.begin_date) last_update_date
         FROM wf_process_activities wpa, wf_activities wfa
         WHERE wpa.activity_item_type = wfa.item_type
               AND wpa.activity_name = wfa.name
               AND wfa.version =
                     (SELECT MAX (version)
                      FROM wf_activities wfa1
                      WHERE wpa.activity_item_type = wfa1.item_type
                            AND wpa.activity_name = wfa1.name)
         GROUP BY wpa.activity_item_type) wfa,
        wf_item_types wit,
        wf_item_types_tl witl
   WHERE     witl.name = wit.name
         AND witl.language = 'US'
         AND wfa.activity_item_type(+) = wit.name
   UNION ALL
   ---------------------------------------------------
   --- MENU
   ---------------------------------------------------
   SELECT 'APPS',
          fm.menu_name,
          fmt.user_menu_name,
          fm.last_update_date,
          'MENU',
          'FND_MENUS',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct '
          || 'fndload/'
          || REPLACE (fm.menu_name, ' ', '_')
          || '_fm.ldt '
          || 'MENU MENU_NAME="'
          || fm.menu_name
          || '"'
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct '
          || 'fndload/'
          || REPLACE (fm.menu_name, ' ', '_')
          || '.ldt '
             upload
   FROM fnd_menus fm, fnd_menus_tl fmt
   WHERE fmt.menu_id = fm.menu_id AND fmt.language = 'US'
   UNION ALL
   ---------------------------------------------------
   --- MENU ENTRY
   ---------------------------------------------------
   SELECT 'APPS',
          m2.menu_name || '/' || u.function_name || m.menu_name,
          e.entry_sequence || ' ' || u.user_function_name || m.user_menu_name,
          e.last_update_date,
          'MENU ENTRY',
          'FND_MENU_ENTRIES',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct '
          || 'fndload/'
          || REPLACE (m2.menu_name, ' ', '_')
          || '_'
          || REPLACE (u.function_name, ' ', '_')
          || '_'
          || REPLACE (m.menu_name, ' ', '_')
          || '.ldt '
          || 'MENU PARENT_MENU_NAME="'
          || m2.menu_name
          || DECODE (u.function_name,
                NULL, '',
                '" FUNCTION_NAME="' || u.function_name || '"')
          || DECODE (m.menu_name,
                NULL, '',
                '" SUB_MENU_NAME="' || m.menu_name || '"')
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct '
          || 'fndload/'
          || REPLACE (m2.menu_name, ' ', '_')
          || '_'
          || REPLACE (u.function_name, ' ', '_')
          || '_'
          || REPLACE (m.menu_name, ' ', '_')
          || '_fme.ldt '
             upload
   FROM fnd_menu_entries_vl e,
        fnd_menus_vl m,
        fnd_menus_vl m2,
        fnd_form_functions_vl u
   WHERE     e.function_id = u.function_id(+)
         AND e.sub_menu_id = m.menu_id(+)
         AND e.menu_id = m2.menu_id
   UNION ALL
   ---------------------------------------------------
   --- LOOKUP
   ---------------------------------------------------
   SELECT fa.application_short_name,
          flt.lookup_type,
          fltt.meaning,
          flt.last_update_date,
          'LOOKUP',
          'FND_LOOKUP_TYPES',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct '
          || 'fndload/'
          || REPLACE (flt.lookup_type, ' ', '_')
          || '_flt.ldt '
          || 'FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="'
          || fa.application_short_name
          || '" '
          || 'LOOKUP_TYPE="'
          || flt.lookup_type
          || '"'
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct '
          || 'fndload/'
          || REPLACE (flt.lookup_type, ' ', '_')
          || '_flt.ldt '
             upload
   FROM fnd_application fa, fnd_lookup_types flt, fnd_lookup_types_tl fltt
   WHERE     fltt.lookup_type = flt.lookup_type
         AND fltt.language = 'US'
         AND fa.application_id = flt.application_id
   UNION ALL
   ---------------------------------------------------
   --- FLEXVALUES
   ---------------------------------------------------
   SELECT 'APPS',
          ffvs.flex_value_set_name,
          ffvs.description,
          ffvs.last_update_date,
          'FLEXVALUE',
          'FND_FLEX_VALUE_SETS',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct '
          || 'fndload/'
          || REPLACE (ffvs.flex_value_set_name, ' ', '_')
          || '_ffvs.ldt '
          || 'VALUE_SET FLEX_VALUE_SET_NAME="'
          || flex_value_set_name
          || '"'
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct '
          || 'fndload/'
          || REPLACE (ffvs.flex_value_set_name, ' ', '_')
          || '_ffvs.ldt '
             upload
   FROM fnd_flex_value_sets ffvs
   UNION ALL
   ---------------------------------------------------
   --- PERSONALIZATION
   ---------------------------------------------------
   SELECT 'APPS',
          form_name,
          function_name,
          MAX (last_update_date),
          'PERSONALIZATION',
          'FND_FORM_CUSTOM_RULES',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct '
          || 'fndload/'
          || REPLACE (form_name, ' ', '_')
          || '_'
          || REPLACE (function_name, ' ', '_')
          || '_ffcr.ldt FND_FORM_CUSTOM_RULES FORM_NAME="'
          || form_name
          || '" '
          || 'FUNCTION_NAME="'
          || function_name
          || '"'
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct '
          || form_name
          || '_ffcr.ldt'
             upload
   FROM fnd_form_custom_rules
   GROUP BY form_name, function_name
   UNION ALL
   ---------------------------------------------------
   --- PROFILE
   ---------------------------------------------------
   SELECT fa.application_short_name,
          fpo.profile_option_name,
          user_profile_option_name,
          fpo.last_update_date,
          'PROFILE',
          'FND_PROFILE_OPTIONS_VL',
          'FNDLOAD APPS/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct '
          || 'fndload/'
          || fpo.profile_option_name
          || '_fpo.ldt '
          || 'PROFILE FND_PROFILE_OPTION_VALUES PROFILE_NAME="'
          || fpo.profile_option_name
          || '" APPLICATION_SHORT_NAME="'
          || fa.application_short_name
          || '"',
          'FNDLOAD APPS/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct '
          || 'fndload/'
          || fpo.profile_option_name
          || '_fpo.ldt '
   FROM fnd_profile_options_vl fpo, fnd_application fa
   WHERE fpo.application_id = fa.application_id
   UNION ALL
   ---------------------------------------------------
   --- REQUEST GROUPS
   ---------------------------------------------------
   SELECT a.application_short_name,
          v.request_group_code,
          v.request_group_name,
          v.last_update_date,
          'REQUEST GROUP',
          'FND_REQUEST_GROUPS',
          'FNDLOAD APPS/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct '
          || 'fndload/'
          || v.request_group_code
          || '_rg.ldt '
          || 'REQUEST_GROUP REQUEST_GROUP_NAME="'
          || v.request_group_code
          || '"',
          'FNDLOAD APPS/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct '
          || 'fndload/'
          || v.request_group_code
          || '_rg.ldt '
   FROM fnd_request_groups v, fnd_application a
   WHERE 1 = 1 AND a.application_id = v.application_id
   UNION ALL
   ---------------------------------------------------
   --- REQUEST GROUP UNITS
   ---------------------------------------------------
   SELECT va.application_short_name,
          g.request_group_name,
          DECODE (v.request_unit_type,
                  'P',
                  p.concurrent_program_name,
                  'S',
                  s.request_set_name
          ),
          v.last_update_date,
          'REQUEST GROUP UNIT',
          'FND_REQUEST_GROUP_UNITS',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct'
          || REPLACE (g.request_group_name || '_'
                      || DECODE (v.request_unit_type,
                                 'P',
                                 p.concurrent_program_name,
                                 'S',
                                 s.request_set_name
                         ),
                      ' ',
                      '_'
             )
          || '_frgu.ldt '
          || 'REQUEST_GROUP REQUEST_GROUP_NAME="'
          || g.request_group_name
          || '" UNIT_NAME="'
          || DECODE (v.request_unit_type,
                     'P',
                     p.concurrent_program_name,
                     'S',
                     s.request_set_name
             )
          || '"',
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct'
          || REPLACE (g.request_group_name || '_'
                      || DECODE (v.request_unit_type,
                                 'P',
                                 p.concurrent_program_name,
                                 'S',
                                 s.request_set_name
                         ),
                      ' ',
                      '_'
             )
          || '_frgu.ldt '
   FROM fnd_application va,
        fnd_application a,
        fnd_concurrent_programs p,
        fnd_request_sets s,
        fnd_request_group_units v,
        fnd_request_groups g
   WHERE     1 = 1
         AND g.application_id = va.application_id
         AND v.application_id = g.application_id
         AND v.request_group_id = g.request_group_id
         AND a.application_id = v.unit_application_id
         AND v.unit_application_id = p.application_id(+)
         AND v.unit_application_id = s.application_id(+)
         AND ( (v.request_unit_type = 'P'
                AND v.request_unit_id = p.concurrent_program_id)
              OR (v.request_unit_type = 'S'
                  AND v.request_unit_id = s.request_set_id)
              OR v.request_unit_type NOT IN ('S', 'P'))
         AND DECODE (v.request_unit_type, 'P', v.request_unit_id, NULL) =
               p.concurrent_program_id(+)
         AND DECODE (v.request_unit_type, 'S', v.request_unit_id, NULL) =
               s.request_set_id(+)
   UNION ALL ----------- REQUEST GROUP UNITS
   SELECT 'APPS' owner,
          plsql_type object_name,
          plsql_name object_description,
          last_update_date,
          'WEB PLSQL' object_type,
          'FND_ENABLED_PLSQL',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct'
          || plsql_type
          || '_'
          || plsql_name
          || '_fep.ldt '
          || 'FND_ENABLED_PLSQL PLSQL_TYPE="'
          || plsql_type
          || '" PLSQL_NAME="'
          || plsql_name
          || '"',
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct'
          || plsql_type
          || '_'
          || plsql_name
          || '_fep.ldt '
   FROM fnd_enabled_plsql
   UNION ALL
   ---------------------------------------------------
   --- ALERT
   ---------------------------------------------------
   SELECT a1.application_short_name,
          v.alert_name,
          v.description,
          v.last_update_date,
          'ALERT',
          'ALR_ALERTS',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct'
          || v.alert_name
          || '_alr.ldt '
          || 'ALR_ALERTS APPLICATION_SHORT_NAME="'
          || a1.application_short_name
          || '" ALERT_NAME="'
          || v.alert_name
          || '"',
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct'
          || v.alert_name
          || '_alr.ldt '
   FROM fnd_application a1, alr_alerts v, fnd_application a2
   WHERE a1.application_id = v.application_id
         AND v.table_application_id = a2.application_id(+)
   UNION ALL
   ---------------------------------------------------
   --- USER
   ---------------------------------------------------
   SELECT 'APPS',
          user_name,
          description,
          last_update_date,
          'USER',
          'FND_USER',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct'
          || user_name
          || '_user.ldt FND_USER USER_NAME="'
          || user_name
          || '"',
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct '
          || user_name
          || '_user.ldt'
   FROM fnd_user
   ORDER BY 1, 2, 3;