All Oracle Fusion General Ledger Tables
Oracle Fusion General Ledger is a comprehensive financial management solution that forms the core of Oracle's ERP Cloud offering. Understanding the underlying table structure is essential for anyone working with Oracle Financials, whether you're developing custom reports, troubleshooting issues, or planning data migrations.
This post provides a complete reference guide to Oracle Fusion General Ledger tables, organized by functional categories to help you understand how they relate to business processes.
Why Understanding GL Tables Matters
Before diving into the specific tables, it's important to understand why this knowledge is valuable:
- Custom Reporting: When standard reports don't meet your needs, understanding the underlying tables allows you to create custom queries and reports.
- Data Validation: During implementations or upgrades, knowledge of table structures helps validate data integrity.
- Troubleshooting: When issues arise, knowing where and how data is stored can significantly speed up problem resolution.
- Integration Projects: For integrating with other systems, understanding the data model is crucial for mapping fields correctly.
- Performance Optimization: Knowledge of table relationships helps optimize queries and improve system performance.
Core Financial Data Tables
These tables store the actual financial balances and are the heart of the General Ledger system:
| Table Name | Description | Key Fields |
|---|---|---|
| GL_BALANCES | Stores account balances - the core financial data in General Ledger | LEDGER_ID, CODE_COMBINATION_ID, PERIOD_NAME, CURRENCY_CODE, ACTUAL_FLAG |
| GL_DAILY_BALANCES | Contains daily balance information for more granular reporting | LEDGER_ID, CODE_COMBINATION_ID, CURRENCY_CODE, BALANCE_DATE |
| GL_BUDGET_BALANCES | Stores budget data for comparison with actual results | LEDGER_ID, CODE_COMBINATION_ID, PERIOD_NAME, BUDGET_VERSION_ID |
| GL_BALANCE_CURRENCIES | Maintains currency information for multi-currency balances | LEDGER_ID, CURRENCY_CODE |
| GL_BALANCE_INIT_HISTORY | Records history of balance initialization processes | LEDGER_ID, PERIOD_NAME, INIT_DATE |
| GL_BALANCE_TRANSFER_HISTORY | Tracks balance transfers between accounts | LEDGER_ID, FROM_CCID, TO_CCID, TRANSFER_DATE |
| GL_BAL_CUBE_CONCURRENCY | Manages concurrency for balance cube processing | LEDGER_ID, CUBE_ID, PROCESS_ID |
Real-world application: When analyzing financial performance, finance teams query GL_BALANCES to compare actual results against budgets stored in GL_BUDGET_BALANCES. For daily cash management, GL_DAILY_BALANCES provides more granular visibility.
Journal Entry Management Tables
These tables manage the journal entries that create and modify financial balances:
| Table Name | Description | Key Fields |
|---|---|---|
| GL_JE_HEADERS | Contains journal entry header information | JE_HEADER_ID, LEDGER_ID, PERIOD_NAME, NAME, STATUS |
| GL_JE_LINES | Stores journal entry line details | JE_HEADER_ID, JE_LINE_NUM, CODE_COMBINATION_ID, ENTERED_DR, ENTERED_CR |
| GL_JE_BATCHES | Manages journal batches for processing | JE_BATCH_ID, NAME, STATUS, BATCH_POSTED_DATE |
| GL_INTERFACE | Serves as interface for journal import | REFERENCE_ID, STATUS, GROUP_ID, LEDGER_ID |
| GL_IMPORT_REFERENCES | Maintains references for imported journals | REFERENCE_ID, JE_HEADER_ID, SOURCE_TABLE |
| GL_DAILY_POST_INT | Supports daily posting interface processes | LEDGER_ID, POSTING_DATE, STATUS |
Real-world application: When accountants create journal entries in the system, data is stored in GL_JE_HEADERS and GL_JE_LINES. For automated journal imports from other systems (like Accounts Payable), data first lands in GL_INTERFACE before being validated and transferred to the journal tables.
Ledger Configuration Tables
These tables define the ledger structures that organize financial data:
| Table Name | Description | Key Fields |
|---|---|---|
| GL_LEDGERS | Defines ledgers in the system | LEDGER_ID, NAME, CHART_OF_ACCOUNTS_ID, CURRENCY_CODE |
| GL_LEDGER_CONFIG_DETAILS | Stores ledger configuration details | CONFIGURATION_ID, LEDGER_ID, OBJECT_TYPE_CODE |
| GL_LEDGER_CONFIGURATIONS | Contains ledger configuration settings | CONFIGURATION_ID, NAME, STATUS_CODE |
| GL_LEDGER_RELATIONSHIPS | Defines relationships between ledgers | PRIMARY_LEDGER_ID, SECONDARY_LEDGER_ID, RELATIONSHIP_TYPE_CODE |
| GL_LEDGER_SEGMENT_VALUES | Maps segment values to ledgers | LEDGER_ID, SEGMENT_VALUE_ID, SEGMENT_TYPE_CODE |
| GL_LEDGER_SETS | Defines sets of ledgers for processing | LEDGER_SET_ID, NAME, DESCRIPTION |
| GL_LEDGER_SET_ASSIGNMENTS | Assigns ledgers to ledger sets | LEDGER_SET_ID, LEDGER_ID, EFFECTIVE_DATE |
Real-world application: In multinational companies, GL_LEDGERS defines each country's ledger, while GL_LEDGER_RELATIONSHIPS establishes connections between primary and reporting ledgers. GL_LEDGER_SETS groups ledgers for consolidated reporting.
Chart of Accounts Tables
These tables define the accounting structure used to categorize financial transactions:
| Table Name | Description | Key Fields |
|---|---|---|
| GL_CODE_COMBINATIONS | Stores valid account combinations | CODE_COMBINATION_ID, CHART_OF_ACCOUNTS_ID, SEGMENT1-30 |
| GL_CHART_OF_ACCOUNTS_OPTIONS | Contains chart of accounts configuration | CHART_OF_ACCOUNTS_ID, NAME, NUM_SEGMENTS |
| GL_ACCOUNT_ALIASES_B | Manages account aliases (base table) | ACCOUNT_ALIAS_ID, CODE_COMBINATION_ID, ALIAS |
| GL_ACCOUNT_ALIASES_TL | Stores translated account aliases | ACCOUNT_ALIAS_ID, LANGUAGE, DESCRIPTION |
| GL_ACCOUNT_GROUPS | Defines account groupings for reporting | GROUP_ID, NAME, DESCRIPTION |
| GL_CROSS_VALIDATION_RULES_INT | Contains cross-validation rules | RULE_ID, ENABLED_FLAG, DESCRIPTION |
Real-world application: When users enter accounting codes in forms, the system validates against GL_CODE_COMBINATIONS to ensure only valid combinations are used. For easier data entry, users can use aliases defined in GL_ACCOUNT_ALIASES_B instead of remembering full account combinations.
Calendar and Period Tables
These tables define the time periods used for financial reporting:
| Table Name | Description | Key Fields |
|---|---|---|
| GL_CALENDARS | Defines accounting calendars | CALENDAR_ID, NAME, PERIOD_TYPE |
| GL_PERIOD_SETS | Contains sets of accounting periods | PERIOD_SET_ID, NAME, DESCRIPTION |
| GL_PERIOD_TYPES | Defines types of accounting periods | PERIOD_TYPE, NAME, CALENDAR_TYPE_CODE |
| GL_PERIODS | Stores individual accounting periods | PERIOD_NAME, PERIOD_SET_ID, START_DATE, END_DATE |
| GL_DATE_PERIOD_MAP | Maps dates to accounting periods | PERIOD_SET_ID, CALENDAR_DATE, PERIOD_NAME |
| GL_CAL_CONFIRMED_YEARS | Tracks confirmed calendar years | CALENDAR_ID, FISCAL_YEAR, CONFIRMED_FLAG |
Real-world application: The financial close process relies on GL_PERIODS to control which periods are open for posting. When transactions are entered, GL_DATE_PERIOD_MAP determines which accounting period receives the entry based on the transaction date.
Currency and Exchange Rate Tables
These tables support multi-currency transactions and reporting:
| Table Name | Description | Key Fields |
|---|---|---|
| GL_DAILY_RATES | Stores daily currency exchange rates | FROM_CURRENCY, TO_CURRENCY, CONVERSION_DATE, CONVERSION_TYPE, CONVERSION_RATE |
| GL_DAILY_RATES_INTERFACE | Provides interface for currency rates | FROM_CURRENCY, TO_CURRENCY, CONVERSION_DATE, STATUS |
| GL_CROSS_RATE_RULE_DTLS | Contains cross-rate calculation rules | RULE_ID, FROM_CURRENCY, TO_CURRENCY |
| GL_DAILY_CONVERSION_TYPES | Defines currency conversion types | CONVERSION_TYPE, NAME, DESCRIPTION |
Real-world application: For multinational companies, GL_DAILY_RATES provides the exchange rates used when converting foreign currency transactions to the ledger currency. Treasury departments often load rates through GL_DAILY_RATES_INTERFACE from external sources.
Access Control and Security Tables
These tables manage security and access to financial data:
| Table Name | Description | Key Fields |
|---|---|---|
| GL_ACCESS_SETS | Defines access control sets | ACCESS_SET_ID, NAME, DESCRIPTION |
| GL_ACCESS_SET_ASSIGNMENTS | Contains access set assignments | ACCESS_SET_ID, USER_ID, ASSIGNMENT_TYPE |
| GL_ACCESS_SET_LEDGERS | Maps ledgers to access sets | ACCESS_SET_ID, LEDGER_ID, READ_ONLY_FLAG |
| GL_SECURITY_RULES | Stores security rule definitions | RULE_ID, NAME, ENABLED_FLAG |
| GL_SECURITY_SEGMENTS | Manages security for segments | SEGMENT_ID, SEGMENT_NAME, SECURITY_TYPE |
| GL_CONCURRENCY_CONTROL | Contains concurrency control settings | OBJECT_ID, OBJECT_TYPE, LOCK_DATE |
Real-world application: Finance departments use GL_ACCESS_SETS and GL_ACCESS_SET_ASSIGNMENTS to control which users can access specific ledgers, while GL_ACCESS_SET_LEDGERS determines whether users have read-only or read-write access.
Allocation and Automation Tables
These tables support automated processes for financial operations:
| Table Name | Description | Key Fields |
|---|---|---|
| GL_ALLOC_RULES | Defines allocation rules | RULE_ID, NAME, FORMULA, STATUS |
| GL_ALLOC_RULESETS | Contains sets of allocation rules | RULESET_ID, NAME, DESCRIPTION |
| GL_AUTORUN_SETUP | Stores automated process configuration | SETUP_ID, PROCESS_TYPE, FREQUENCY_TYPE |
| GL_AUTORUN_RUNS | Tracks automated process executions | RUN_ID, SETUP_ID, START_TIME, END_TIME |
| GL_AUTORUN_RUN_RESULTS | Records results of automated processes | RESULT_ID, RUN_ID, STATUS, MESSAGE |
| GL_AUTOMATIC_POSTING_OPTIONS | Configures automatic posting | LEDGER_ID, SOURCE_NAME, ENABLED_FLAG |
Real-world application: Month-end processes often use GL_ALLOC_RULES to automatically distribute overhead costs to departments. GL_AUTORUN_SETUP configures these allocations to run on schedule, while GL_AUTORUN_RUN_RESULTS tracks their success or failure.
Mapping and Transformation Tables
These tables support financial consolidation and reporting across different accounting structures:
| Table Name | Description | Key Fields |
|---|---|---|
| GL_COA_MAPPINGS | Defines chart of accounts mappings | MAPPING_ID, NAME, SOURCE_COA_ID, TARGET_COA_ID |
| GL_COA_MAP_ACCOUNT_RULES | Contains account mapping rules | RULE_ID, MAPPING_ID, SOURCE_VALUE, TARGET_VALUE |
| GL_COA_MAP_ROLLUP_RULES | Defines rollup mapping rules | RULE_ID, MAPPING_ID, TARGET_VALUE |
| GL_COA_MAP_SEGMENT_RULES | Stores segment mapping rules | RULE_ID, MAPPING_ID, SOURCE_SEGMENT, TARGET_SEGMENT |
| GL_ACCTS_MAP_SEG1_GT through GL_ACCTS_MAP_SEG30_GT | Segment mapping tables | SOURCE_VALUE, TARGET_VALUE, MAPPING_ID |
Real-world application: During acquisitions or system migrations, GL_COA_MAPPINGS and related tables facilitate mapping between different chart of accounts structures, ensuring financial data can be consolidated correctly despite structural differences.
Reversal and Adjustment Tables
These tables manage reversals and adjustments to financial data:
| Table Name | Description | Key Fields |
|---|---|---|
| GL_AUTOREVERSE_OPTIONS | Configures auto-reversal options | LEDGER_ID, REVERSAL_PERIOD_TYPE, REVERSAL_METHOD |
| GL_AUTOREV_CRITERIA_SETS | Defines auto-reversal criteria | CRITERIA_SET_ID, NAME, DESCRIPTION |
| GL_CLEARING_ACCT_RECON_GT | Supports clearing account reconciliation | LEDGER_ID, ACCOUNT_CCID, RECONCILIATION_DATE |
Real-world application: Accounting teams use GL_AUTOREVERSE_OPTIONS to configure how accrual entries automatically reverse in the following period. This ensures temporary entries don't permanently affect financial statements.
Understanding Table Relationships
Oracle Fusion General Ledger tables work together in an integrated system:
- Journal entries (GL_JE_HEADERS, GL_JE_LINES) create and modify balances (GL_BALANCES)
- Ledger configuration (GL_LEDGERS) and chart of accounts (GL_CODE_COMBINATIONS) define the structure for financial data
- Calendar and period tables (GL_CALENDARS, GL_PERIODS) determine when journals can be posted
- Currency and exchange rate tables support multi-currency operations
- Access control tables determine who can view and modify financial data
- Allocation and automation tables support automated processes
- Mapping and transformation tables enable reporting across different accounting structures
Key Tables for Common Tasks
When working with Oracle Fusion General Ledger, these key tables are most frequently used:
- GL_BALANCES - For querying account balances
- Example use:
SELECT * FROM GL_BALANCES WHERE LEDGER_ID = 123 AND PERIOD_NAME = 'JAN-25'
- Example use:
- GL_JE_HEADERS and GL_JE_LINES - For journal entry information
- Example use:
SELECT h.NAME, l.ENTERED_DR, l.ENTERED_CR FROM GL_JE_HEADERS h JOIN GL_JE_LINES l ON h.JE_HEADER_ID = l.JE_HEADER_ID WHERE h.PERIOD_NAME = 'JAN-25'
- Example use:
- GL_CODE_COMBINATIONS - For account combination details
- Example use:
SELECT * FROM GL_CODE_COMBINATIONS WHERE SEGMENT1 = '01' AND SEGMENT2 = '1000'
- Example use:
- GL_LEDGERS - For ledger configuration information
- Example use:
SELECT * FROM GL_LEDGERS WHERE NAME = 'US Operations'
- Example use:
- GL_PERIODS - For period status and date ranges
- Example use:
SELECT * FROM GL_PERIODS WHERE PERIOD_SET_ID = 123 ORDER BY START_DATE
- Example use:
Best Practices for Working with GL Tables
- Always use the API when possible: While direct table access is useful for reporting, use Oracle's APIs for data manipulation to ensure proper validation and processing.
- Consider performance implications: Many GL tables contain millions of rows. Use appropriate indexing and filtering in your queries.
- Understand the impact of security: Remember that data security policies may restrict access to certain records even when querying tables directly.
- Be aware of multi-org data: In a multi-organization implementation, ensure your queries include appropriate organization filters.
- Test in non-production first: Always test custom queries in a test environment before running them in production.
Conclusion
Understanding Oracle Fusion General Ledger tables is essential for effectively working with Oracle Financials. This comprehensive reference organizes the tables by functional category to help you navigate the complex data model.
Whether you're developing reports, troubleshooting issues, or planning data migrations, this guide provides the foundation you need to work with Oracle Fusion General Ledger data effectively.
Note: Table structures may vary slightly between Oracle Fusion versions. Always verify the exact structure in your specific implementation.
Labels: All Oracle General Ledger Tables, Oracle Fusion, Oracle Tables
posted by SM @ 19:33
0 Comments
