Awesome Useful Oracle Apps script to debug it

Last updated on September 14th, 2016 at 09:10 pm

You will need many scripts while troubleshooting,extracting data from Oracle E-business suite.Here I am giving very Useful Oracle Apps  script/code.


Script to find out patches applied

select orig_bug_number,to_char(max(last_update_date),’DD-MON-YYYY hh24:mi:ss’) updated from ad_patch_run_bugs where
orig_bug_number in
select distinct app_patch.patch_name
from (
select substr(ap.patch_name,1,10) patch_name
from ad_patch_runs pr, ad_patch_drivers dr,
ad_applied_patches ap
where pr.start_date between to_date(‘&&1′,’MM/DD/YYYY’)
and to_date(‘&&2′,’MM/DD/YYYY’)
and dr.patch_driver_id = pr.patch_driver_id
and ap.applied_patch_id = dr.applied_patch_id
and not exists
(select ‘x’ from ad_comprising_patches cp
where cp.patch_driver_id = dr.patch_driver_id)
select substr(b.bug_number,1,10) patch_name
from ad_patch_runs pr, ad_patch_drivers dr,
ad_comprising_patches cp, ad_bugs b
where pr.start_date between to_date(‘&&1′,’MM/DD/YYYY’)
and to_date(‘&&2′,’MM/DD/YYYY’)
and dr.patch_driver_id = pr.patch_driver_id
and cp.patch_driver_id = pr.patch_driver_id
and cp.bug_id = b.bug_id) app_patch)
group by orig_bug_number;

Version information

select name, created from v$database;
select * from product_component_version;

Materialized view

select unusable, known_stale, invalid, REWRITE_ENABLED, mview_name from
sys.dba_mview_analysis where MVIEW_NAME like ‘MSC%’;

What is File version in database
set pages 52
set verify off
set term on
set feedback off
col “APP NAME” format a10
col filename format a15
col version format a15
col last_update_date format a15
set linesize 90
select af.app_short_name “APP NAME”,
from ad_files af,
ad_file_versions afv
where af.file_id = afv.file_id and
af.filename like ‘&1’
order by afv.LAST_UPDATE_DATE desc

What is the package versions?
select text
from user_source
where line = 2
and name = upper(‘&enter_package_name’)
order by line

Check XML file in MDS repository
exec JDR_UTILS.printDocument(‘/oracle/apps/fnd/framework/navigate/webui/XXXX’)

Check messages
select message_text from fnd_new_messages m, fnd_application a
where upper(‘&message_name’) = m.message_name
and upper(‘&language_code’) = m.language_code
and upper(‘&application_short_name’) = a.application_short_name
and m.application_id = a.application_id

Check profile options
set linesize 140
set pagesize 132
column SHORT_NAME format A30
column NAME format A40
column LEVEL_VAL format 999999999
column VALUE format A60 wrap

p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User’,
v.level_value LEVEL_VAL,
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper(‘%&profile_name%’)


Some more interesting articles to read

Other Useful scripts

Admin scripts for Oracle apps

Useful Unix command

Leave a Reply