Good queries on FND tables for Apps11i

1. TO FIND APPLICATION DETAILS
===============================

This will provide you application id alongwith shortname and base path of it:

SELECT * 
FROM fnd_application;


2. TO FIND PRODUCT INSTALLATION DETAILS
========================================

SELECT application_id, 
product_version, 
status, 
patch_level 
FROM fnd_product_installations;

Note: Here application_id would be id as per query no. 1, product_version could 
be '11.5.0', status could be 'I' / 'S' / 'N' and patch_level could be '11i.AD.I' 

3. TO FIND THE TABLE DETAILS
=============================

SELECT application_id, 
table_id, 
table_name, 
user_table_name, 
description 
FROM fnd_tables 
WHERE table_name LIKE UPPER('%&table_name%');

Note: Enter the table_name for which you want to find out details. You can put 
application_id to filter tables.


4. TO FIND TABLE COLUMNS
=========================

Make sure to enter the table name in CAPS only:

SELECT application_id, 
table_id, 
column_id, 
column_name, 
user_column_name, 
column_sequence, 
column_type, 
width, 
description 
FROM fnd_columns 
WHERE table_id = 
(SELECT table_id 
FROM fnd_tables 
WHERE table_name LIKE UPPER('&table_name'));

Note: Enter the table_name for which you want to find out column details.


5. TO FIND VIEWS DETAILS
=========================
SET LONG 1000

SELECT application_id, 
view_id, 
view_name, 
description, 
text 
FROM fnd_views 
WHERE view_name LIKE UPPER('%&view_name%');


6. TO FIND VIEW COLUMNS
========================

SELECT application_id, 
view_id, 
column_sequence, 
column_name 
FROM fnd_view_columns 
WHERE view_id = 
(SELECT view_id 
FROM fnd_views 
WHERE view_name LIKE UPPER('&view_name'));

Note: Enter the view_name for which you want to find out view column details.

7. TO FIND CURRENCY DETAILS
===========================

SELECT currency_code, 
symbol, 
enabled_flag, 
currency_flag, 
description, 
precision, 
extended_precision, 
minimum_accountable_unit, 
start_date_active, 
end_date_active 
FROM fnd_currencies 
WHERE currency_code LIKE '%¤cy_code%';

Note: Here currency_code could be 'USD', 'GBP' etc.

8. TO FIND THE EXECUTABLE DETAILS
==================================

SELECT application_id,
executable_id,
executable_name,
execution_file_name,
subroutine_name,
icon_name,
execution_file_path
FROM fnd_executables
WHERE application_id = &application_id AND executable_name LIKE '%&
executable_name%';

Note: Here application_id could be id as per query no. 1 and executable_name 
could be 'APXPBFOR'

9. TO FIND INDEX DETAILS
========================
SELECT dba.status,
fnd.application_id,
fnd.table_id,
fnd.index_id, 
fnd.index_name,
fnd.description
FROM fnd_indexes fnd, 
dba_indexes dba 
WHERE table_id = 
(SELECT table_id 
FROM fnd_tables 
WHERE table_name LIKE UPPER('&table_name')) and fnd.index_name = dba.
index_name; 

Note: Enter the table_name for which you want to find out index details.

10. TO FIND INDEX COLUMNS
==========================

SELECT application_id,
table_id,
index_id,
column_sequence,
column_id
FROM fnd_index_columns WHERE table_id = 
(SELECT table_id 
FROM fnd_tables 
WHERE table_name = UPPER('&table_name'));

Note: Enter the table_name for which y.
ou want to find out index columns. If 
you are aware of index_id from query no. 9 above then specify that in the where 
clause instead of table_name. 

11. TO KNOW PRIMARY KEY DETAILS
===============================

SELECT application_id,
table_id,
primary_key_id,
primary_key_name,
description,
enabled_flag
FROM fnd_primary_keys
WHERE table_id = 
(SELECT table_id 
FROM fnd_tables 
WHERE table_name LIKE UPPER('&table_name'));

Note: Enter the table_name for which you want to find out primary key details.

12. TO FIND SEQUENCE DETAILS
============================

SELECT application_id, 
sequence_id,
sequence_name,
start_value,
increment_by,
min_value,
max_value,
cache_size,
cycle_flag,
order_flag,
description
FROM fnd_sequences
WHERE sequence_name = UPPER('&sequence_name');


13. TO FIND PROFILE OPTION DETAILS
==================================

SELECT application_id,
profile_option_id,
profile_option_name, 
site_enabled_flag,
resp_enabled_flag,
user_enabled_flag
FROM fnd_profile_options
WHERE profile_option_name LIKE UPPER('%&profile_option_name%');


14. TO FIND FOLDERS DETAILS
============================

Note: Here the NAME is completely case sensitive and hence needs to be passed 
as defined in FOLDER:

SELECT folder_id,
object,
name,
public_flag,
autoquery_flag,
where_clause,
order_by
FROM fnd_folders
WHERE name like '%&name%';.