• Post category:Others
  • Post comments:0 Comments
  • Post last modified:January 22, 2021
  • Reading time:5 mins read
You are currently viewing Table Naming Convention in Fusion Applications
Table Naming Convention in Fusion Applications

We have many tables in Fusion and we need to understand their naming conventions in order to find out the right table that we need to refer to.

_B these are the BASE tables. They are very important and the data is stored in the table with all validations.

_F these are date tracked tables, which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the primary key identifies a row uniquely.

_TL tables are the tables that store the translated values for the base or date-tracked tables. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column. Ex: PER_JOBS_F_TL

_V are views created from base or date-tracked tables.

_VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV(‘LANG’). Ex: PER_GRADES_F_VL

table names ending with underscore character(_) are audit tables. If Auditing has been enabled then these tables captures the transaction details that have been done on business object.. like old values, new values, who did that update, what action was performed and when was it performed, etc. Ex: PER_ALL_PEOPLE_F_

_M tables storemultiple changes per date table. Like a _F table, but it allows rows to start and end on the same day. There is only one such table per_all_assignments_m, and it has additionally the effective_sequence in the primary key.

_DN tables are denormalised tables. usually just a simplification of another table that holds hierarchies or trees of some kind. The denormalised tables are populated by ESS processes and hold the flattened hierarchy that has direct links between all nodes and their distance. Those are used e.g. for security profiles based on the manager hierarchy.

We can restrict your queries to a single language when using translated tables or views by including the following: where language = userenv(‘lang’) or where language = ‘US'(code for US English)

Below is the list of Module Identifiers by which the tables can be identified:
Absence Mgmt(ANC)
Benefits (BEN)
Career Dev. (HRD)
Compensation (CMP)
Fast Formulas (FF)
Goals (HRG)
Human Resources (EEC, HCM, HR, PER)
Human Resources Common (HRC)
Payroll (PAY)
Performance (HRA)
Profiles (HRQ)
Succession (HRM)
Time & Labor (HXT)
Workforce Mgmt(HWM)

Link for the Tables and Views List – https://docs.oracle.com/en/cloud/saas/global-human-resources/r13-update17d/oedmh/toc.htm

We can search for the table/view name or the field name on the above link

image 1 - Table Naming Convention in Fusion Applications

Using SQL Queries to get list of all tables/columns:

Select * from FND_TABLES

Select * from FND_COLUMNS

select * from ALL_TAB_COLUMNS

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning Fusion Technical tools go through this post

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.