BASICS OF ORACLE APPS FND TABLES

FND Tables

FIND APPLICATION DETAILS


SELECT * 
FROM fnd_application;

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' 

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.

FIND TABLE COLUMNS

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.

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

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.

 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 '%&currency_code%';

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

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'

 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.

 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. 

  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.

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');


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

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