• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:February 22, 2021
  • Reading time:9 mins read
You are currently viewing Oracle HCM Cloud Data Dictionary Tables
Oracle HCM Cloud Data Dictionary Tables

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.

image 69 1024x564 - Oracle HCM Cloud Data Dictionary Tables

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

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:

image 62 1024x443 - Oracle HCM Cloud Data Dictionary Tables

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:

image 64 1024x364 - Oracle HCM Cloud Data Dictionary Tables

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:

image 63 1024x538 - Oracle HCM Cloud Data Dictionary Tables

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:

image 65 1024x387 - Oracle HCM Cloud Data Dictionary Tables

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:

image 66 1024x292 - Oracle HCM Cloud Data Dictionary Tables

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:

image 67 1024x273 - Oracle HCM Cloud Data Dictionary Tables

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:

image 70 - Oracle HCM Cloud Data Dictionary Tables

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