useful Scripts For Finanical (and All Other) Applications Analysts

Questions and Answers
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;

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.