Oracle HCM Cloud Data Dictionary 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.

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:

Query:

select * from FND_TABLES where table_name like 'PER%F'

Output:

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.

Query:

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 

Output:

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.

Query:

select * from FND_VIEWS

Output:

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.

Query:

SELECT * FROM FND_AUDIT_ATTRIBUTES 
WHERE TABLE_NAME LIKE '%ASSIGNMENT%' 
AND ENABLED_FLAG = 'Y'
ORDER BY LAST_UPDATE_DATE DESC

Output:

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.

Query:

SELECT * FROM FND_AUDIT_USER_TABLE_ACCESS
ORDER BY LAST_UPDATE_DATE DESC

Output:

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.

Query:

SELECT * FROM FND_CAL_DAY WHERE REPORT_DATE >= SYSDATE ORDER BY REPORT_DATE ASC

Output:

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.

Query:

select * from FND_CURRENCIES_B

Output:

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