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 do I check if a patch is applied?

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

4. How to find instance name, host name, apps and RDBMS versions of the instance user was 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 with SQL to update the apps tables?

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

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

How do I check for invalid objects in all the modules?

How to find the applications in the system is either installed GKFX?

How to determine database character set?

How do I check the indexes on a table?

How do I check for custom triggers on seeded tables?

How do I get the header file versions for a 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 is meant to provide the most commonly requested information.

Functional analysts with SQL and Unix access should is able to run these scripts and provide the information to Oracle Sup Port.

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

QUESTIONS and Answersimportant:most of the information provided by the scripts below can is obtained from the RDA diagnost ICS, which is simple to perform and provides more complete information about the installed software. This should is the privileged set of information provided to support.
You can find the RDA test for your APPS version in one of the following:

• r12.0.6+: All RDA Data Collection Test (Doc ID 732091.1)
• R12.0. [3-4]: All RDA Data Collection Test (Doc ID 420427.1)
• 11i:applications DBA RDA Data Collection Test (Doc ID 183274.1)

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 do I 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 a 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 was 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 the partial file names and the search are not a case sensitive.

For example your 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 with 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 is not allowed unless directed to does so by Oracle support or development

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

The User knows the column_name but isn't sure what table (s) the column name was in.

Example:

SELECT * FROM Dba_tab_columns
Where column_name like '%set_of_books_id% ';

This would provide the names of all the tables that have column_name set_of_books_id.

9. How do I 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 ';

How do I 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;

How to find the applications in the system is either installed GKFX?

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 gkfx.

How to determine database character set?

Select value from Nls_database_parameters
where parameter = ' nls_characterset ';

The following scripts would 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;

How do I 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 ';

How do I check for custom triggers on seeded tables?

Example:

Select Trigger_name, owner
From Dba_triggers
WHERE table_name = ' gl_balances ';

How do I get the header file versions for a 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 would provide the versions of all the headers files in those executables.

note:the command adident (in Unix, Windows and other OS) can also is used to provide the file versions.

Still have Questions?

To discuss the information further with Oracle experts and industry peers, we encourage you to review, join or start a di Scussion in the My Oracle support communities.

To provide feedback in this note, click on theRate this documentLink.

referencesnote:134430.1-How to Check the Indexes in 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

Useful Scripts for e-business Suite applications analysts