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%';.