In this article we will look into the data dictionary through which we can get details of all tables, columns, views, etc that are present in the database.
When we have requirement to check for tables, we normally check the Tables and Views Documentation for the latest release as new tables might get added or table structures might have got changed with new release.
Do you know that we can also get this information from the database by querying backend tables?
It is possible with the usage of FND% tables and you can check more information on these tables and get the complete list of tables at Tables and Views for Common Features under the Middleware Extensions for Applications >Tables section.
We will look at some of the most important tables which provides valuable info that would be of interest to us.
All these FND tables capture data for not only HCM, they have data for ERP, SCM, PPM, CX and all other pillars and hence listed under Common Features.
Table of Contents
- 1 FND_TABLES – List of all Tables in Application
- 2 FND_COLUMNS – Lists all column details for all tables.
- 3 FND_VIEWS – List of all views in the application
- 4 FND_AUDIT_ATTRIBUTES – Lists all attributes which are Audit enabled
- 5 FND_AUDIT_USER_TABLE_ACCESS – Stores the Audit History for User and Table
- 6 FND_CAL_DAY – Day Level Table for all years
- 7 FND_CAL_MONTH – Month Level Table for Gregorian Calendar Time Dimension
- 8 FND_CAL_QUARTER – Quarter Level Table for Gregorian Calendar Time Dimension
- 9 FND_CAL_WEEK – Week Level Table for Gregorian Calendar Time Dimension
- 10 FND_CAL_YEAR – Year Level Table for Gregorian Calendar Time Dimension
- 11 FND_CURRENCIES_B – Lists all Currencies
FND_TABLES – List of all Tables in Application
This table contains list of all tables in the application. It also lists the Creation Date, Last Updated Date, Description, tablespace, etc.
A Quick query to check for person related tables would result in the below output:
select * from FND_TABLES where table_name like 'PER%F'
FND_COLUMNS – Lists all column details for all tables.
This table contains list of all columns for a table. Should be used in conjunction with FND_TABLES to identify columns in a table.
This table contains the columns, their sequence, description, data type, precision for numbers, NULL Allowed, etc.
select FC.* from FND_COLUMNS FC, FND_TABLES FT where FT.TABLE_ID = FC.TABLE_ID and FT.TABLE_NAME = 'PER_ALL_PEOPLE_F' order by COLUMN_SEQUENCE
FND_VIEWS – List of all views in the application
This table contains the list of all views in the application. It contains the Application which owns the view, underlying SQL behind the view, creation date and last updated date.
select * from FND_VIEWS
FND_AUDIT_ATTRIBUTES – Lists all attributes which are Audit enabled
This table contains the list of all attributes which are enabled for Audit functionality. Once Audit is enabled you can query this table to get list of all attributes which have auditing turned on with respect to table name.
SELECT * FROM FND_AUDIT_ATTRIBUTES WHERE TABLE_NAME LIKE '%ASSIGNMENT%' AND ENABLED_FLAG = 'Y' ORDER BY LAST_UPDATE_DATE DESC
FND_AUDIT_USER_TABLE_ACCESS – Stores the Audit History for User and Table
This table stores the Audit History when a base table was last updated by a particular user. There will be only 1 row per user and table so only the latest update by the user will be present in this table.
SELECT * FROM FND_AUDIT_USER_TABLE_ACCESS ORDER BY LAST_UPDATE_DATE DESC
FND_CAL_DAY – Day Level Table for all years
This table contains the list of all days in a year for all years. It contains some peculiar info like day of the week, day of the month and day of the year for which we will normally have to do calculations. Instead we can use this table directly to get this handy info.
SELECT * FROM FND_CAL_DAY WHERE REPORT_DATE >= SYSDATE ORDER BY REPORT_DATE ASC
FND_CAL_MONTH – Month Level Table for Gregorian Calendar Time Dimension
FND_CAL_QUARTER – Quarter Level Table for Gregorian Calendar Time Dimension
FND_CAL_WEEK – Week Level Table for Gregorian Calendar Time Dimension
FND_CAL_YEAR – Year Level Table for Gregorian Calendar Time Dimension
FND_CURRENCIES_B – Lists all Currencies
This table lists all currencies corresponding to countries and also lists the currency code used.
select * from FND_CURRENCIES_B
We also have other tables for Lookups, Valuesets,, Descriptive Flex Fields, Key Flex Fields, Extensible Flex Fields, Indexes, Foreign Keys, Messages, Profile Options, Timezones, Trees, Tree Nodes etc.
Check out the complete list at Tables and Views for Common Features
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM