2 Tables
Overview
This section provides details of tables such as key columns, data types, and table descriptions. Use the "Referred Table" and "Referred Column" information to decide on the join condition between Fact and Dimension tables.
Note:
Tables ending with the suffix_EN and those listed in Tables Without WHO Columns are for
Oracle Internal use only. You must not use the UPDATE
W$_INSERT_DT and W$_UPDATE_DT columns for reporting purposes. Use these columns with
caution for downstream custom ETL. Fusion Data Intelligence truncates and reloads tables as required, effectively removing the historical record
of these columns.
Download this ZIP file that contains available tables that apply to the current version of Oracle Fusion ERP Analytics. Once the file downloads, extract the file, open the folder, and then open the release-specific HTML file. For example, click 25R2_Fusion_ERP_Analytics_Tables.html.
Table Types
The suffixes in table names designate the table type.
- CA, A: Aggregate
- CF, F: Fact
- CF_DN: Denormalized Fact
- CG, G: General
- D: Dimension
- DH: Dimension Hierarchy
- EF: Extended Fact
- EN: Oracle internal use only
- H: Helper
- P: Prediction
- TL: Translation
Tables Without WHO Columns
All tables have two system date columns which can be used to get the last refresh dates.
- W$_INSERT_DT - the timestamp when that particular record was inserted into that table.
- W$_UPDATE_DT - the timestamp when that particular row was updated in that table.
- DW_INV_ORGANIZATION_D_TL
- DW_FISCAL_DAY_D_NO_ADJUST
- DW_INV_ORGANIZATION_D
- DW_JOB_D_CURRENT_TL
- DW_PERSON_LEGISLATION_ALL_CURRENT_D
- DW_JOB_CURRENT_D
- DW_PERSON_NAME_CURRENT_D
- DW_GL_BALANCE_CA
- DW_GL_BALANCE_CF
- DW_GL_JOURNAL_CA
Fusion Accounting Hub Tables
When you deploy the Fusion Accounting Hub custom application, these warehouse tables are published in Autonomous Data Warehouse.
- Balance Fact: DW_FAH_SLA_BALANCE_CF
- SLA Fact: DW_FAH_SLA_<APPL_NAME>_CF
- Supporting Reference Dimension: DW_FAH_<SR_NAME>_D
- Application and metadata tables
Balance Fact DW_FAH_SLA_BALANCE_CF
This table has the XLA balance data for balancing supporting references for all sub ledger applications (custom and seeded) along with GL segments. All the configured balancing supporting references are provided with functional names and datatypes based on user configurations.
| Name | Datatype | Length | Precision | Not Null | Comments |
|---|---|---|---|---|---|
| AC_BALANCE_ID | NUMBER | 38 | N | Balance Identifier | |
| SOURCE_RECORD_ID | VARCHAR2 | 256 | N | Source record identifier. Value is derived from a combination of AE_HEADER_ID,TEMP_LINE_NUM,APPLICATION_ID and REF_AE_HEADER_ID | |
| APPLICATION_ID | NUMBER | 38 | N | Identifier of a functional area within a software suite of products. For example: Financials, or Payroll. | |
| JOURNAL_SOURCE_NAME | VARCHAR2 | 32 | Y | Code used to indicate the origin of journal entries such as payables, receivables, or manual. | |
| LEDGER_ID | NUMBER | 38 | Y | Identifier of a self-contained accounting information set for single or multiple legal or management entities. | |
| LEGAL_ENTITY_ID | NUMBER | 38 | Y | Legal entity identifier. | |
| CODE_COMBINATION_ID | NUMBER | 38 | Y | Accounting code combination identifier. | |
| SUPP_REF_COMBINATION_ID | NUMBER | 38 | Y | SupportingRefBalanceSuppRefCombinationId | |
| FISCAL_PERIOD_NAME | VARCHAR2 | 64 | N | Accounting period name. | |
| FISCAL_PERIOD_SET_NAME | VARCHAR2 | 16 | N | Accounting calendar name. | |
| FISCAL_PERIOD_TYPE | VARCHAR2 | 16 | Y | Accounting period type. | |
| ACCT_PERIOD_START_DATE | DATE | N | Accounting period start date. | ||
| ACCT_PERIOD_END_DATE | DATE | N | Accounting period end date. | ||
| FIRST_PERIOD_FLAG | VARCHAR2 | 16 | Y | SupportingRefBalanceFirstPeriodFlag | |
| PERIOD_YEAR | NUMBER | Y | SupportingRefBalancePeriodYear | ||
| INITIAL_BALANCE_FLAG | VARCHAR2 | 16 | Y | SupportingRefBalanceInitialBalanceFlag | |
| GL_BALANCING_SEGMENT | VARCHAR2 | 32 | Y | Balancing segment. | |
| GL_BLNC_SGMNT_VALUESET_CODE | VARCHAR2 | 64 | Y | Balancing segment value set code. | |
| COST_CENTER_SEGMENT | VARCHAR2 | 32 | Y | Cost center segment. | |
| COST_CENTER_VALUESET_CODE | VARCHAR2 | 64 | Y | Cost center value set code. | |
| NATURAL_ACCOUNT_SEGMENT | VARCHAR2 | 32 | Y | Natural account segment. | |
| NTRL_ACNT_SGMNT_VALUESET_CODE | VARCHAR2 | 64 | Y | Natural account value set code. | |
| GL_SEGMENT1 to GL_SEGMENT10 | VARCHAR2 | 32 | Y | Accounting segment 1 to 10 | |
| GL_SEGMENT1_VALUESET_CODE to GL_SEGMENT10_VALUESET_CODE | VARCHAR2 | 64 | Y | Accounting segment value set code 1to 10 | |
| TRANSACTION_CURRENCY_CODE | VARCHAR2 | 16 | N | Transaction currency code. | |
| GBL_CURRENCY_CODE | VARCHAR2 | 16 | N | Global currency code. | |
| GBL_CURRENCY_EXCH_RATE | BIGDECIMAL | 38 | Y | Ledger currency to global currency conversion rate. | |
| ENTERED_CUR_BEGIN_BAL_CR | BIGDECIMAL | 38 | Y | Entered Currency Opening Balance CR | |
| ENTERED_CUR_BEGIN_BAL_DR | BIGDECIMAL | 38 | Y | Entered Currency Opening Balance DR | |
| TRANSACTION_BEGIN_BALANCE | BIGDECIMAL | 38 | Y | Transaction Beginning Balance | |
| BEGINNING_BALANCE_DR | BIGDECIMAL | 38 | Y | Opening Balance DR | |
| BEGINNING_BALANCE_CR | BIGDECIMAL | 38 | Y | Opening Balance CR | |
| LEDGER_BEGIN_BALANCE | BIGDECIMAL | 38 | Y | Ledger Beginning Balance | |
| GLOBAL_BEGIN_BAL_DR | BIGDECIMAL | 38 | Y | Global Opening Balance DR | |
| GLOBAL_BEGIN_BAL_CR | BIGDECIMAL | 38 | Y | Global Opening Balance CR | |
| GLOBAL_BEGIN_BALANCE | BIGDECIMAL | 38 | Y | Global Beginning Balance | |
| PERIOD_BALANCE_DR | BIGDECIMAL | 38 | Y | Period Balance DR | |
| PERIOD_BALANCE_CR | BIGDECIMAL | 38 | Y | Period Balance CR | |
| LEDGER_PERIOD_BALANCE | BIGDECIMAL | 38 | Y | Ledger period balance | |
| PERIOD_ENTERED_BALANCE_DR | BIGDECIMAL | 38 | Y | Entered Currency Period Balance DR | |
| PERIOD_ENTERED_BALANCE_CR | BIGDECIMAL | 38 | Y | Entered Currency Period Balance CR | |
| TRANSACTION_PERIOD_BALANCE | BIGDECIMAL | 38 | Y | Transaction period balance | |
| GLOBAL_PERIOD_BALANCE_DR | BIGDECIMAL | 38 | Y | Global Period Balance DR | |
| GLOBAL_PERIOD_BALANCE_CR | BIGDECIMAL | 38 | Y | Global Period Balance CR | |
| GLOBAL_PERIOD_BALANCE | BIGDECIMAL | 38 | Y | Global period balance | |
| LEDGER_CURRENCY_CODE | VARCHAR2 | 16 | N | Ledger currency code. | |
|
Configured balancing supporting references with functional short names (SR1,SR2…SR30) |
Dimensional Identifiers for SR Dimensions <Balance SR Columns> |
||||
| CREATED_BY_USER_ID | VARCHAR2 | 64 | Y | ||
| CREATION_DATE | TIMESTAMP | Y |
SLA Fact DW_FAH_SLA_<APPL_NAME>_CF
This table has subledger accounting data for the selected Fusion Accounting Hub application with de-normalized transactional details. XLA details and the corresponding supporting references (balance and non-balance) configured for an application are provided with functional names
While framing the Fusion Accounting Hub fact name, consider using the first 35 characters of the Application code DW_FAH_SLA_SUBSTR(APPL_CODE,1,35)_CF. If the first 35 characters aren'tt unique in the selected Fusion Accounting Hub applications, then use the name DW_FAH_SLA_STR(APPL_ID)_CF.
Dynamic Columns
- If any of the Dynamic column matches with the factory columns, then the Dynamic column name uses the prefix SR_ for SR columns, and TXN_ and TXN_LINE for transaction attribute columns.
- If any supporting reference name matches mandatory transaction attributes, then the SR name uses the prefix SR_ (SR_NAME).
- If a non-mandatory TXN attribute name matches a SR name, then those TXN attributes use the prefix TXN or TXN_LINE.
| Column_Name | Column_Grain |
|---|---|
| DEFAULT_AMOUNT | LINE |
| DEFAULT_CURRENCY | LINE |
| LINE_NUMBER | LINE |
| TRANSACTION_DATE | HEADER |
| TRANSACTION_NUMBER | HEADER |
| TRANSACTION_REVERSAL_FLAG | HEADER |
Supporting Reference Dimension DW_FAH_<SR_NAME>_D
The dimension is created for all the configured balance supporting references in all sub ledger applications. This dimension allows you to extend further with the dimension entity using data augmentation.
| Customer_ID | Customer |
|---|---|
| FAWQA_APAC | FAWQA_APAC |
| FAWQA_UAE | FAWQA_UAE |
| Apple | Apple |
| Sony | Sony |
| FAWQA_EMEA | FAWQA_EMEA |
| FAWQA_LATAM | FAWQA_LATAM |
Application and Metadata Tables
DW_FAH_APPLICATION_FACTS
This table provides the facts details provided for the selected Fusion Accounting Hub applications.
| DW_FAH_APPLICATION_FACTS | |
|---|---|
| COLUM_NAME | Description |
| APPLICATION_ID | Application Identifier |
| SOURCE_RECORD_ID | |
| APPLICATION_CODE | Application Code |
| FACT_NAME | Fact Name |
DW_FAH_APPLICATION_TL
This table has the translation names of all the subledger applications.
| DW_FAH_APPLICATION_TL | |
|---|---|
| COLUM_NAME | Description |
| APPLICATION_ID | Application Identifier |
| LANGUAGE | Language |
| SOURCE_RECORD_ID | |
| APPLICATION_CODE | Application Code |
| APPLICATION_NAME | Application Name |
| APPLICATION_DESCRIPTION | Application Description |
DW_FAH_TABLE_COLUMN_GROUPS
This table includes the data for SR and Transaction columns of the Balance and SLA fact with their corresponding column groups.
| DW_FAH_TABLE_COLUMN_GROUPS | |
|---|---|
| Column Name | Description |
| FAH_TABLE_NAME | Table name |
| COLUMN_NAME | Column name |
| SOURCE_RECORD_ID | |
| COLUMN_GROUP | Column Group |