Oracle Apps EBS – Technical Common SQL/ Scripts for regular investigations


These scripts are meant to provide the most commonly requested information. Functional analysts with SQL and Unix access should be able to run these scripts and provide the information to Oracle Support.

1. How to find versions of files in packages?

select text from dba_source
where name like ‘%&PKG_NAME%’
and line = 2;

Example:

select text
from dba_source
where name = ‘GLRX_JOURNAL_PKG’
and line = 2;

 

2. How to check if a patch is applied?

select * from ad_bugs where bug_number = &bug_number;

select * from ad_applied_patches where patch_name = &bug_number;
SELECT DISTINCT a.bug_number,e.patch_name,
c.end_date,b.applied_flag
FROM ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d,
ad_applied_patches e
WHERE a.bug_id = b.bug_id
AND b.patch_run_id = c.patch_run_id
AND c.patch_driver_id = d.patch_driver_id
AND d.applied_patch_id = e.applied_patch_id
AND a.bug_number LIKE ‘123456’
ORDER BY 1 DESC ;

3. How to find the patch set level for an application?

select substr(aa.application_short_name,1,20) "Product", a.patch_level
"Patch Level" from fnd_product_installations a, fnd_application aa
where a.application_id = aa.application_id
and aa.application_short_name like ‘%&short_name%’;

Example:

select substr(aa.application_short_name,1,20) "Product", a.patch_level
"Patch Level" from fnd_product_installations a, fnd_application aa
where a.application_id = aa.application_id
and aa.application_short_name like ‘%AP%’;

4. How to find instance name, host name, apps and RDBMS versions of the instance user is logged into?

select i.instance_name, i.host_name,
f.release_name release, i.version

from v$instance i,
fnd_product_groups f where upper(substr(i.instance_name,1,4)) =
upper(substr(f.applications_system_name,1,4));

5. How to find the latest version of a file on a given instance?

SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE ‘&file_name’ AND ve.file_id = fi.file_id AND version =
(SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id =
fi.file_id);

Example 1 – finding java class version of a file).

SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE ‘%FrmSheet1VBA%’ AND ve.file_id = fi.file_id AND version =
(SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id = fi.file_id);

Example 2 – Finding version of .fmb file.

SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE ‘%GLXJEENT.fmb%’ AND ve.file_id = fi.file_id AND version =
(SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id = fi.file_id);

Note: This works for .class, .fmb .pls, .o, and .sql files only. Doens’t work
for .lpc,.lc files etc. If querying for a Form, please use GLXJEENT.fmb
as glxjeent.fmb will not work.

6. How to check the installation status and patch set level for a product?

Example 1

select patch_level, status from fnd_product_installations
where patch_level like ‘%FND%’;

Example 2

select patch_level, status from fnd_product_installations
where patch_level like ‘%XDO%’;

7. How to backup a table before users use sql to update the apps tables?

Example 1:

Create table ap_invoices_all_bkp as select * from ap_invoices_all;

Example 2:

Create table gl_interface_bkp as select * from gl_interface;

8. How to find the table(s) name with the column name?

User knows the column_name but not sure what table(s) the column name is in.

Example:

select * from dba_tab_columns where column_name like ‘%SET_OF_BOOKS_ID%’;

This will provide the names of all the tables that has column_name SET_OF_BOOKS_ID.

9. How to check for invalid objects in a particular module?

select OWNER, OBJECT_NAME, OBJECT_TYPE
from DBA_OBJECTS
where OBJECT_NAME like ‘FND_%’
and STATUS = ‘INVALID’;

select OWNER, OBJECT_NAME, OBJECT_TYPE
from DBA_OBJECTS
where OBJECT_NAME like ‘AP_%’
and STATUS = ‘INVALID’;

10. How to check for invalid objects in all the modules?

select owner, object_name, object_type from dba_objects
where status = ‘INVALID’
order by object_name, object_type;

11. How to find the applications in the system that are either installed shared?

select fat.application_id, FAT.APPLICATION_NAME, fdi.status, fdi.patch_level FROM
FND_APPLICATION_TL FAT,
fnd_product_installations FDI
WHERE FDI.APPLICATION_ID = FAT.APPLICATION_ID
and fdi.status in (‘I’, ‘S’)

Note: Status ‘I’ meaning installed and status ‘S’ meaning shared.

12. How to determine database character set?

select value from nls_database_parameters
where parameter = ‘NLS_CHARACTERSET’;

The following scripts will provide NLS parameter and value for database, instance and session.

select * from nls_database_parameters;
select * from nls_instance_parameters;
select * from nls_session_parameters;

13. How to check the indexes on a table?

Example:

select index_owner owner,table_name tab, index_name ind,
column_name colu, column_position position
from DBA_IND_COLUMNS
where table_name = ‘GL_CODE_COMBINATIONS’;

14. How to check for custom triggers on seeded tables?

Example:
select trigger_name, owner
from dba_triggers
where table_name = ‘GL_BALANCES’;

15. How to get the header file versions for an executable in Unix?

Example 1
Log into UNIX.

cd $AP_TOP/bin
strings -a APXXTR | grep Header

Example 2
cd $RG_TOP/bin
Strings -a RGRARG |grep Header
The above will provide the versions of all the header files in those executables.

Advertisements

One thought on “Oracle Apps EBS – Technical Common SQL/ Scripts for regular investigations

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s