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;
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.