Useful Scripts for E-Business Suite Applications Analysts

In this Document

  Purpose
  Questions and Answers
  IMPORTANT:
  1. How to find versions of files in packages?
  2. How to check if a patch is applied?
  3. How to find the patch set level for an application?
  4. How to find instance name, host name, apps and RDBMS versions
of the instance user is logged into?
  5. How to find the latest version of a file on a given instance?
  6. How to check the installation status and patch set level
for a product?
  7. How to backup a table before users use sql to update the
apps tables?
  8. How to find the table(s) name with the column name?
  9. How to check for invalid objects in a particular module?
  10. How to check for invalid objects in all the modules?
  11. How to find the applications in the system that are
either installed shared?
  12. How to determine database character set?
  13. How to check the indexes on a table?
  14. How to check for custom triggers on seeded tables?
  15. How to get the header file versions for an executable
in Unix?
  Still Have Questions?
  References

APPLIES TO:

Oracle Purchasing - Version 11.5.1 to 12.1.3 [Release 11.5 to 12.1]

Oracle Payables - Version 11.5.0 to 12.1.3 [Release 11.5 to 12.1]

Oracle Assets - Version 11.5.0 to 12.1.3 [Release 11.5 to 12.1]

Oracle General Ledger - Version 11.5 to 12.1.3 [Release 11.5.0 to 12.1]

Oracle Application Object Library - Version 11.5.0 to 12.1.3 [Release 11.5 to 12.1]

Information in this document applies to any platform.

***Checked for relevance on 14-Oct-2013***

PURPOSE

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.

User need to log into SQL plus to run the SQL scripts.

QUESTIONS AND ANSWERS

IMPORTANT:

Most of the information provided by the scripts below can be obtained from the RDA diagnostics, which is simple to perform and provides more complete information about the installed software. This should be the privileged set of information provided to Support. 

You can find the RDA test for your APPS version in one of the following:

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 '&bug_number' 

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 sub.filename, sub.version 

from (

   select adf.filename filename,

   afv.version version,

   rank()over(partition by adf.filename

     order by afv.version_segment1 desc,

     afv.version_segment2 desc,afv.version_segment3 desc,

     afv.version_segment4 desc,afv.version_segment5 desc,

     afv.version_segment6 desc,afv.version_segment7 desc,

     afv.version_segment8 desc,afv.version_segment9 desc,

     afv.version_segment10 desc,

     afv.translation_level desc) as rank1 

   from ad_file_versions afv,

     (

     select filename, app_short_name, subdir, file_id

     from ad_files

     where upper(filename) like upper('%&filename%')

     ) adf

   where adf.file_id = afv.file_id

) sub

where rank1 = 1

order by 1

You can enter partial file names and the search is not case sensitive.

For example you can search on "glxjeent" for the form "GLXJEENT.fmb" or "frmsheet1" for java file "FrmSheet1VBA.class".

Note: This script works for the following file types:

- .class, .drvx, .fmb, .htm, .lct, .ldt, .o, .odf, .pkb, .pkh, .pls, .rdf, .rtf, .sql, .xml.

It doens't work for .lpc, .lc files, etc.

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;

Note: SQL updates are not allowed unless directed to do so by Oracle Support or Development

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.

Note: the command adident (in unix, windows and other OS) can also be used to provide the file versions.


Still Have Questions?

To discuss this information further with Oracle experts and industry peers, we encourage you to review, join or start a discussion in the My Oracle
Support Communities
.



To provide feedback on this note, click on the Rate this document link.

REFERENCES

NOTE:134430.1 - How To Check The Indexes
On The Main General Ledger Tables

NOTE:183274.1 - 11i : Applications
DBA RDA Data Collection Test

NOTE:420427.1 - R12.0.[3-4] : All
RDA Data Collection Test

NOTE:732091.1 - R12.0.6+ : All RDA
Data Collection Test

上一篇:用 Spring Boot 和 MybatisPlus 快速构建项目


下一篇:js对象系列【一】深层理解对象与原型