Hey pessoal,<\/p>\n\n\n\n
Seguem algumas queries para consultar objetos customizados no Oracle EBS.<\/p>\n\n\n\n
-- Custom Queries Objects\n-- (Change the XXABC by the custom short name for your company)\n\n-- QUERY 1: Find custom objects\nSelect *\n from all_objects\n where object_name like 'XXABC%'; \n\n-- Query 2: Find custom calls in all code sources\n-- Here you will see all codes that has been customized (in standard or custom objects).\nSelect *\n from all_source\n where UPPER (TEXT) like '%XXABC%'; \n\n-- Query 3: Find custom forms functions \nSelect fff.function_id,\n fff.function_name,\n ffft.user_function_name,\n fff.type AS function_type,\n fff.creation_date,\n fff.description\n FROM fnd_form_functions fff,\n fnd_form_functions_tl ffft\n WHERE fff.function_id = ffft.function_id\n AND ffft.language = 'US'\n AND (fff.function_name LIKE 'XXABC%' OR ffft.user_function_name LIKE 'XXABC%')\n ORDER BY fff.function_name;\n\n-- Query 4: Find custom concurrent Programs \nSelect fct.user_concurrent_program_name,\n 'Concurrent Prog', flv.meaning,\n fe.execution_file_name,\n fa.application_short_name\n from fnd_executables fe,fnd_concurrent_programs fcp,fnd_application fa, \n fnd_concurrent_programs_tl fct, fnd_lookup_values flv \n where 1 = 1 \n and fe.executable_id = fcp.executable_id \n and fa.application_id = fe.application_id \n and fcp.concurrent_program_id = fct.concurrent_program_id \n and fa.application_id = fct.application_id \n and fe.execution_method_code = flv.lookup_code \n and upper(flv.lookup_type) = upper('Cp_execution_method_code')\n and fct.language = 'US'\n and fcp.enabled_flag = 'Y'\n and flv.enabled_flag = 'Y' \n and flv.language = 'US'\n and (upper(fe.execution_file_name) like 'XXABC%');\n\n-- Query 5: Custom Value Sets \nSelect ffvs.flex_value_set_name,\n 'Application', 'Value Set',\n '',\n fa.application_short_name \n from fnd_flex_value_sets ffvs,fnd_flex_validation_tables ffvt,fnd_descr_flex_column_usages fdfc,\n fnd_application fa\n where 1=1 \n and ffvs.flex_value_set_id = ffvt.flex_value_set_id \n and ffvs.flex_value_set_id = fdfc.flex_value_set_id \n and fdfc.application_id = fa.application_id \n and fdfc.enabled_flag = 'Y' \n and (upper(ffvs.flex_value_set_name) like 'XXABC%');\n\n-- Query 6: Custom Alerts \nSelect alr.alert_name,\n 'Application', 'Alert',\n '',\n fa.application_short_name\n from alr_alerts alr, fnd_application fa\n where (upper (alert_name) like 'XXABC%')\n and alr.enabled_flag = 'Y'\n and sysdate between alr.start_date_active \n and nvl (alr.end_date_active, sysdate)\n and fa.application_id = alr.application_id;\n\n-- Query 7: Custom Responsibilities \nSelect frt.responsibility_name,\n 'Application', 'Responsibility',\n '',\n fa.application_short_name \nfrom fnd_responsibility_tl frt,\n fnd_application fa\nwhere frt.application_id = fa.application_id\nand ( upper(frt.responsibility_name) like 'XXABC%')\nand frt.language = 'US';\n\n-- Query 8: Custom Menus \nSelect menu_name, \n 'Application', 'Menu',\n user_menu_name,\n ''\n from fnd_menus fm\n , fnd_menus_tl fmt\n where fmt.menu_id = fm.menu_id\n and (upper(fm.menu_name) like 'XXABC%')\n and language = 'US';\n\n-- Query 9: Custom Request groups\nSelect frg.request_group_name,\n 'Application', 'Request Group',\n '',\n fa.application_short_name\n from fnd_request_groups frg\n , fnd_application fa\n where (upper(frg.request_group_name) like 'XXABC%')\n and fa.application_id = frg.application_id;\n\n-- Query 10: Custom Profiles \nSelect pro1.user_profile_option_name,\n 'Application', 'Profile',\n pro1.profile_option_name || ' - ' || decode (pov.level_id,\n 10001, 'Site',\n 10002, 'Application',\n 10003, 'Resp',\n 10004, 'User'),\n appl.application_short_name\n from fnd_profile_option_values pov,\n fnd_responsibility_tl resp,\n fnd_application appl,\n fnd_user u,\n fnd_profile_options pro,\n fnd_profile_options_tl pro1\n where pro.profile_option_name = pro1.profile_option_name\n and pro.profile_option_id = pov.profile_option_id\n and pov.level_value = resp.responsibility_id(+)\n and pov.level_value = appl.application_id(+)\n and pov.level_value = u.user_id(+)\n and (upper(pro1.profile_option_name) like 'XXABC%')\n and sysdate between pro.start_date_active and nvl (pro.end_date_active, sysdate);\n\n\n-- Query 11: Custom Messages\nSelect message_name, message_text,\n 'Application', 'Messages',\n '',\n application_short_name\n from fnd_new_messages fnm, fnd_application fa\n where (upper(fnm.message_name) like 'XXABC%')\n and fa.application_id = fnm.application_id;\n\n-- Query 12: Custom Lookups\nSelect lookup_type,\n 'Application', 'Lookup',\n '',\n application_short_name\n from fnd_lookup_types l, fnd_application fa\n where fa.application_id = l.application_id\n and (upper(lookup_type) like 'XXABC%');\n\n-- Query 13: Custom Applications\nSelect application_short_name,\n 'Application', 'Custom Applications',\n basepath,\n application_short_name\n from fnd_application\n where (upper(application_short_name) like 'XXABC%');\n\n-- Query 14: Custom XML report templates \nSelect xddb.data_source_code,\n 'Application', 'XML Publisher Templates',\n '',\n xddb.application_short_name\n from xdo_ds_definitions_b xddb\n ,xdo_templates_b xtb\n where 1 = 1 \n and xddb.data_source_code = xtb.data_source_code\n and (upper(xddb.data_source_code) like 'XXABC%');\n\n-- Query 15: Custom DB Objects\nSelect object_name,\n 'DB Objects', initcap(object_type),\n status,\n owner\n from dba_objects\n where 1 = 1\n -- and status = 'VALID'\n and (upper(object_name) like 'XXABC%');\n \n-- Query 16: Custom Workflows\nSelect name,\n 'Workflow', 'Workflow',\n '',\n 'Custom'\n from wf_item_types_tl \n where 1 = 1 \n and language = 'US'\n and (upper(name) like 'XXABC%');\n\n-- Query 17: Custom Personalizations - Form \nSelect ffv.user_form_name,\n ffv.description,\n 'Application', 'Personalization - Form',\n ffv.form_name,\n fa.application_short_name\n from fnd_form_vl ffv\n , fnd_form_custom_rules ffcr\n , fnd_application fa\n where 1 = 1 \n and ffv.form_name = ffcr.form_name \n and fa.application_id = ffv.application_id\n and ffv.form_name like 'XXABC%'\n and ffv.CREATED_BY not in (1, 2);\n\n-- Query 18: Custom Personalization - OAF\nSelect jdr_mds_internal.getdocumentname(jp.path_docid),\n 'Application', 'Personalization - OAF',\n '',\n decode( upper(substr(jdr_mds_internal.getdocumentname(jp.path_docid),2,2)),'XX', \n substr(jdr_mds_internal.getdocumentname(jp.path_docid), \n regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'\/',1,4)+1,\n regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'\/',1,5)-\n regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'\/',1,4)-1),\n substr(jdr_mds_internal.getdocumentname(jp.path_docid), \n regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'\/',1,3)+1,\n regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'\/',1,4)- \n regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'\/',1,3)-1) \n ) module \n from apps.jdr_paths jp \n where jp.path_docid in (Select distinct comp_docid \n from jdr_components \n where comp_seq = 0 \n and comp_element = 'customization'\n and comp_id is null);\n\n-- Query 19: Custom Form, OAF Pages\nSelect decode(fff.type,'FORM', ff.user_form_name, fff.function_name) name,fff.function_name,\n 'Application' objects, decode(fff.type,'JSP', 'OAF Page',initcap(fff.type)) type, \n decode(fff.type,'FORM', ff.form_name||'.fmb',web_html_call) exename , \n nvl(fa.application_short_name,'Custom') module \n from fnd_form_functions fff\n ,fnd_form_vl ff\n ,fnd_application fa \n where type in ('JSP','FORM') \n and (upper(fff.function_name) like 'XXABC%')\n and ff.form_id(+) = fff.form_id \n and fa.application_id(+) = fff.application_id;<\/code><\/pre>\n\n\n\nAbra\u00e7o,
Junio<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey pessoal, Seguem algumas queries para consultar objetos customizados no Oracle EBS. Abra\u00e7o,Junio<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[1007,622,1008,1006,796,162],"class_list":["post-2443","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-custom-objects","tag-oracle","tag-oracle-custom","tag-oracle-sql","tag-query","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.juniovitor.com\/index.php?rest_route=\/wp\/v2\/posts\/2443"}],"collection":[{"href":"https:\/\/www.juniovitor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.juniovitor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.juniovitor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.juniovitor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2443"}],"version-history":[{"count":1,"href":"https:\/\/www.juniovitor.com\/index.php?rest_route=\/wp\/v2\/posts\/2443\/revisions"}],"predecessor-version":[{"id":2444,"href":"https:\/\/www.juniovitor.com\/index.php?rest_route=\/wp\/v2\/posts\/2443\/revisions\/2444"}],"wp:attachment":[{"href":"https:\/\/www.juniovitor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2443"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.juniovitor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2443"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.juniovitor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2443"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}