Wednesday, 9 November 2016

Oracle EBS Table Suffixes

You have lot of tables ending with different suffixes in Oracle Apps database. Did you ever wonder what these denote? Listed below are the different table suffixes and what they mean.

_ALL: Table ending with _ALL means it stores data related to multiple organizations. Such tables definitely will have a column called org_id which specifies to which org the record belongs to.

_TL: Table ending with _TL is a Translation Table. Such tables have a column called language

_B: Tables ending _B are the base tables

_VL: Tables ending with _VL are Views built upon multi language or translation tables. In other words such views combine base table data with translation table data. If you wish to see the data related to a one particular language, you should set the language using USERENV(‘LANG’).

_V: Tables ending with _V are views

_S: Tables ending with _S are Sequences

_A, _Avn, _ACn: Tables ending with _A, _Avn, _ACn are audit tables.

_F: Tables in which data is tracked with two date columns (Effective_start_Date, Effective_end_date). These tables are primarily used in HRMS and payroll modules.

_DFV /_KFV: DFF/KFF view created on the base table. This is the best way to get the concatenated value of DFF/KFF.
Also using this table the values can be queried based on the DFF/KFF name and not attributes column.

Mohan G