Hey pessoal,
Seguem algumas queries para consultar objetos customizados no Oracle EBS.
-- Custom Queries Objects
-- (Change the XXABC by the custom short name for your company)
-- QUERY 1: Find custom objects
Select *
from all_objects
where object_name like 'XXABC%';
-- Query 2: Find custom calls in all code sources
-- Here you will see all codes that has been customized (in standard or custom objects).
Select *
from all_source
where UPPER (TEXT) like '%XXABC%';
-- Query 3: Find custom forms functions
Select fff.function_id,
fff.function_name,
ffft.user_function_name,
fff.type AS function_type,
fff.creation_date,
fff.description
FROM fnd_form_functions fff,
fnd_form_functions_tl ffft
WHERE fff.function_id = ffft.function_id
AND ffft.language = 'US'
AND (fff.function_name LIKE 'XXABC%' OR ffft.user_function_name LIKE 'XXABC%')
ORDER BY fff.function_name;
-- Query 4: Find custom concurrent Programs
Select fct.user_concurrent_program_name,
'Concurrent Prog', flv.meaning,
fe.execution_file_name,
fa.application_short_name
from fnd_executables fe,fnd_concurrent_programs fcp,fnd_application fa,
fnd_concurrent_programs_tl fct, fnd_lookup_values flv
where 1 = 1
and fe.executable_id = fcp.executable_id
and fa.application_id = fe.application_id
and fcp.concurrent_program_id = fct.concurrent_program_id
and fa.application_id = fct.application_id
and fe.execution_method_code = flv.lookup_code
and upper(flv.lookup_type) = upper('Cp_execution_method_code')
and fct.language = 'US'
and fcp.enabled_flag = 'Y'
and flv.enabled_flag = 'Y'
and flv.language = 'US'
and (upper(fe.execution_file_name) like 'XXABC%');
-- Query 5: Custom Value Sets
Select ffvs.flex_value_set_name,
'Application', 'Value Set',
'',
fa.application_short_name
from fnd_flex_value_sets ffvs,fnd_flex_validation_tables ffvt,fnd_descr_flex_column_usages fdfc,
fnd_application fa
where 1=1
and ffvs.flex_value_set_id = ffvt.flex_value_set_id
and ffvs.flex_value_set_id = fdfc.flex_value_set_id
and fdfc.application_id = fa.application_id
and fdfc.enabled_flag = 'Y'
and (upper(ffvs.flex_value_set_name) like 'XXABC%');
-- Query 6: Custom Alerts
Select alr.alert_name,
'Application', 'Alert',
'',
fa.application_short_name
from alr_alerts alr, fnd_application fa
where (upper (alert_name) like 'XXABC%')
and alr.enabled_flag = 'Y'
and sysdate between alr.start_date_active
and nvl (alr.end_date_active, sysdate)
and fa.application_id = alr.application_id;
-- Query 7: Custom Responsibilities
Select frt.responsibility_name,
'Application', 'Responsibility',
'',
fa.application_short_name
from fnd_responsibility_tl frt,
fnd_application fa
where frt.application_id = fa.application_id
and ( upper(frt.responsibility_name) like 'XXABC%')
and frt.language = 'US';
-- Query 8: Custom Menus
Select menu_name,
'Application', 'Menu',
user_menu_name,
''
from fnd_menus fm
, fnd_menus_tl fmt
where fmt.menu_id = fm.menu_id
and (upper(fm.menu_name) like 'XXABC%')
and language = 'US';
-- Query 9: Custom Request groups
Select frg.request_group_name,
'Application', 'Request Group',
'',
fa.application_short_name
from fnd_request_groups frg
, fnd_application fa
where (upper(frg.request_group_name) like 'XXABC%')
and fa.application_id = frg.application_id;
-- Query 10: Custom Profiles
Select pro1.user_profile_option_name,
'Application', 'Profile',
pro1.profile_option_name || ' - ' || decode (pov.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Resp',
10004, 'User'),
appl.application_short_name
from fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
where pro.profile_option_name = pro1.profile_option_name
and pro.profile_option_id = pov.profile_option_id
and pov.level_value = resp.responsibility_id(+)
and pov.level_value = appl.application_id(+)
and pov.level_value = u.user_id(+)
and (upper(pro1.profile_option_name) like 'XXABC%')
and sysdate between pro.start_date_active and nvl (pro.end_date_active, sysdate);
-- Query 11: Custom Messages
Select message_name, message_text,
'Application', 'Messages',
'',
application_short_name
from fnd_new_messages fnm, fnd_application fa
where (upper(fnm.message_name) like 'XXABC%')
and fa.application_id = fnm.application_id;
-- Query 12: Custom Lookups
Select lookup_type,
'Application', 'Lookup',
'',
application_short_name
from fnd_lookup_types l, fnd_application fa
where fa.application_id = l.application_id
and (upper(lookup_type) like 'XXABC%');
-- Query 13: Custom Applications
Select application_short_name,
'Application', 'Custom Applications',
basepath,
application_short_name
from fnd_application
where (upper(application_short_name) like 'XXABC%');
-- Query 14: Custom XML report templates
Select xddb.data_source_code,
'Application', 'XML Publisher Templates',
'',
xddb.application_short_name
from xdo_ds_definitions_b xddb
,xdo_templates_b xtb
where 1 = 1
and xddb.data_source_code = xtb.data_source_code
and (upper(xddb.data_source_code) like 'XXABC%');
-- Query 15: Custom DB Objects
Select object_name,
'DB Objects', initcap(object_type),
status,
owner
from dba_objects
where 1 = 1
-- and status = 'VALID'
and (upper(object_name) like 'XXABC%');
-- Query 16: Custom Workflows
Select name,
'Workflow', 'Workflow',
'',
'Custom'
from wf_item_types_tl
where 1 = 1
and language = 'US'
and (upper(name) like 'XXABC%');
-- Query 17: Custom Personalizations - Form
Select ffv.user_form_name,
ffv.description,
'Application', 'Personalization - Form',
ffv.form_name,
fa.application_short_name
from fnd_form_vl ffv
, fnd_form_custom_rules ffcr
, fnd_application fa
where 1 = 1
and ffv.form_name = ffcr.form_name
and fa.application_id = ffv.application_id
and ffv.form_name like 'XXABC%'
and ffv.CREATED_BY not in (1, 2);
-- Query 18: Custom Personalization - OAF
Select jdr_mds_internal.getdocumentname(jp.path_docid),
'Application', 'Personalization - OAF',
'',
decode( upper(substr(jdr_mds_internal.getdocumentname(jp.path_docid),2,2)),'XX',
substr(jdr_mds_internal.getdocumentname(jp.path_docid),
regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'/',1,4)+1,
regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'/',1,5)-
regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'/',1,4)-1),
substr(jdr_mds_internal.getdocumentname(jp.path_docid),
regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'/',1,3)+1,
regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'/',1,4)-
regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'/',1,3)-1)
) module
from apps.jdr_paths jp
where jp.path_docid in (Select distinct comp_docid
from jdr_components
where comp_seq = 0
and comp_element = 'customization'
and comp_id is null);
-- Query 19: Custom Form, OAF Pages
Select decode(fff.type,'FORM', ff.user_form_name, fff.function_name) name,fff.function_name,
'Application' objects, decode(fff.type,'JSP', 'OAF Page',initcap(fff.type)) type,
decode(fff.type,'FORM', ff.form_name||'.fmb',web_html_call) exename ,
nvl(fa.application_short_name,'Custom') module
from fnd_form_functions fff
,fnd_form_vl ff
,fnd_application fa
where type in ('JSP','FORM')
and (upper(fff.function_name) like 'XXABC%')
and ff.form_id(+) = fff.form_id
and fa.application_id(+) = fff.application_id;Abraço,
Junio