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.
Ex:HR_DOCUMENT_TYPES_B
_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.
Ex: PER_JOBS_F
_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.
Ex: PER_JOBS_F_V
_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.
Ex: PER_ALL_ASSIGNMENTS_M
_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.
Ex: PER_MANAGER_HRCHY_DN
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
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.
