Wednesday, 19 March 2025

Essential SQL Queries for Oracle Fusion General Ledger

Following our comprehensive guide to Oracle Fusion General Ledger tables, this post provides essential SQL queries for common reporting and analysis tasks. These queries are organized by functional area to help you quickly find solutions for your specific needs.

Note: These queries are designed for Oracle Fusion Applications and may need minor adjustments based on your specific version and implementation. Always test queries in a non-production environment before running them in production.

1. Account Balance Queries

These queries help you analyze and report on financial balances in your General Ledger.

1.1 Basic Period Balance Query

This query retrieves account balances for a specific period:

SELECT 
    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || 
    gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 AS account,
    gb.period_name,
    gb.currency_code,
    gb.actual_flag,
    gb.period_net_dr,
    gb.period_net_cr,
    gb.period_net_dr - gb.period_net_cr AS period_net,
    gb.begin_balance_dr,
    gb.begin_balance_cr,
    gb.begin_balance_dr - gb.begin_balance_cr AS begin_balance,
    gb.end_balance_dr,
    gb.end_balance_cr,
    gb.end_balance_dr - gb.end_balance_cr AS end_balance
FROM 
    gl_balances gb,
    gl_code_combinations gcc,
    gl_ledgers gl
WHERE 
    gb.code_combination_id = gcc.code_combination_id
    AND gb.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gb.currency_code = 'USD'
    AND gb.actual_flag = 'A'
    AND gb.period_name = 'JAN-25'
ORDER BY 
    account;

1.2 Trial Balance Query

Generate a trial balance report for a specific period:

SELECT 
    gcc.segment1 AS company,
    gcc.segment2 AS cost_center,
    gcc.segment3 AS account,
    gcc.segment4 AS product,
    gcc.segment5 AS intercompany,
    gcc.segment6 AS future,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('ACCOUNTING_FLEX', 'GL#', 
        gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCES', 'Y', 'VALUE') AS account_desc,
    SUM(gb.period_net_dr) AS period_debit,
    SUM(gb.period_net_cr) AS period_credit,
    SUM(gb.period_net_dr - gb.period_net_cr) AS period_net,
    SUM(gb.begin_balance_dr - gb.begin_balance_cr) AS begin_balance,
    SUM(gb.end_balance_dr - gb.end_balance_cr) AS end_balance
FROM 
    gl_balances gb,
    gl_code_combinations gcc,
    gl_ledgers gl
WHERE 
    gb.code_combination_id = gcc.code_combination_id
    AND gb.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gb.currency_code = 'USD'
    AND gb.actual_flag = 'A'
    AND gb.period_name = 'JAN-25'
GROUP BY 
    gcc.segment1,
    gcc.segment2,
    gcc.segment3,
    gcc.segment4,
    gcc.segment5,
    gcc.segment6,
    gcc.chart_of_accounts_id,
    gcc.code_combination_id
ORDER BY 
    gcc.segment1, gcc.segment2, gcc.segment3;

1.3 Year-to-Date Balance Query

Compare year-to-date balances across multiple periods:

SELECT 
    gcc.segment1 || '-' || gcc.segment3 AS account_combo,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('ACCOUNTING_FLEX', 'GL#', 
        gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCES', 'Y', 'VALUE') AS account_desc,
    gb.period_name,
    SUM(gb.period_net_dr - gb.period_net_cr) AS period_net,
    SUM(gb.begin_balance_dr - gb.begin_balance_cr) AS begin_balance,
    SUM(gb.end_balance_dr - gb.end_balance_cr) AS end_balance,
    SUM(gb.quarter_to_date_dr - gb.quarter_to_date_cr) AS qtd_balance,
    SUM(gb.year_to_date_dr - gb.year_to_date_cr) AS ytd_balance,
    SUM(gb.project_to_date_dr - gb.project_to_date_cr) AS ptd_balance
FROM 
    gl_balances gb,
    gl_code_combinations gcc,
    gl_ledgers gl
WHERE 
    gb.code_combination_id = gcc.code_combination_id
    AND gb.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gb.currency_code = 'USD'
    AND gb.actual_flag = 'A'
    AND gb.period_name IN ('JAN-25', 'FEB-25', 'MAR-25')
    AND gcc.segment3 BETWEEN '4000' AND '4999'  -- Revenue accounts
GROUP BY 
    gcc.segment1 || '-' || gcc.segment3,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('ACCOUNTING_FLEX', 'GL#', 
        gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCES', 'Y', 'VALUE'),
    gb.period_name
ORDER BY 
    account_combo, gb.period_name;

1.4 Balance Comparison (Actual vs. Budget)

Compare actual balances with budget balances:

SELECT 
    gcc.segment1 || '-' || gcc.segment3 AS account_combo,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('ACCOUNTING_FLEX', 'GL#', 
        gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCES', 'Y', 'VALUE') AS account_desc,
    gb.period_name,
    SUM(CASE WHEN gb.actual_flag = 'A' THEN gb.period_net_dr - gb.period_net_cr ELSE 0 END) AS actual_amount,
    SUM(CASE WHEN gb.actual_flag = 'B' THEN gb.period_net_dr - gb.period_net_cr ELSE 0 END) AS budget_amount,
    SUM(CASE WHEN gb.actual_flag = 'A' THEN gb.period_net_dr - gb.period_net_cr ELSE 0 END) - 
    SUM(CASE WHEN gb.actual_flag = 'B' THEN gb.period_net_dr - gb.period_net_cr ELSE 0 END) AS variance
FROM 
    gl_balances gb,
    gl_code_combinations gcc,
    gl_ledgers gl
WHERE 
    gb.code_combination_id = gcc.code_combination_id
    AND gb.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gb.currency_code = 'USD'
    AND gb.actual_flag IN ('A', 'B')
    AND gb.period_name = 'JAN-25'
    AND gcc.segment3 BETWEEN '5000' AND '5999'  -- Expense accounts
GROUP BY 
    gcc.segment1 || '-' || gcc.segment3,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('ACCOUNTING_FLEX', 'GL#', 
        gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCES', 'Y', 'VALUE'),
    gb.period_name
ORDER BY 
    account_combo, gb.period_name;

2. Journal Entry Queries

These queries help you analyze journal entries in your General Ledger.

2.1 Journal Entry Header and Line Details

Retrieve detailed information about journal entries:

SELECT 
    gjh.je_header_id,
    gjh.name AS journal_name,
    gjh.description AS journal_description,
    gjh.period_name,
    gjh.je_source,
    gjh.je_category,
    gjh.currency_code,
    gjh.status,
    gjh.posted_date,
    gjh.created_by,
    gjh.creation_date,
    gjl.je_line_num,
    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || 
    gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 AS account,
    gjl.entered_dr,
    gjl.entered_cr,
    gjl.accounted_dr,
    gjl.accounted_cr,
    gjl.description AS line_description
FROM 
    gl_je_headers gjh,
    gl_je_lines gjl,
    gl_code_combinations gcc,
    gl_ledgers gl
WHERE 
    gjh.je_header_id = gjl.je_header_id
    AND gjl.code_combination_id = gcc.code_combination_id
    AND gjh.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gjh.period_name = 'JAN-25'
    AND gjh.je_source = 'Manual'
    AND gjh.status = 'P'  -- Posted journals
ORDER BY 
    gjh.je_header_id, gjl.je_line_num;

2.2 Journal Entry Summary by Source and Category

Summarize journal entries by source and category:

SELECT 
    gjh.je_source,
    gjh.je_category,
    gjh.period_name,
    COUNT(DISTINCT gjh.je_header_id) AS journal_count,
    SUM(gjl.entered_dr) AS total_debits,
    SUM(gjl.entered_cr) AS total_credits,
    SUM(gjl.entered_dr - gjl.entered_cr) AS net_amount
FROM 
    gl_je_headers gjh,
    gl_je_lines gjl,
    gl_ledgers gl
WHERE 
    gjh.je_header_id = gjl.je_header_id
    AND gjh.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gjh.period_name = 'JAN-25'
    AND gjh.status = 'P'  -- Posted journals
GROUP BY 
    gjh.je_source,
    gjh.je_category,
    gjh.period_name
ORDER BY 
    gjh.je_source, gjh.je_category;

2.3 Unposted Journal Entries

Identify unposted journal entries:

SELECT 
    gjh.je_header_id,
    gjh.name AS journal_name,
    gjh.description,
    gjh.period_name,
    gjh.je_source,
    gjh.je_category,
    gjh.status,
    gjh.created_by,
    gjh.creation_date,
    SUM(gjl.entered_dr) AS total_debits,
    SUM(gjl.entered_cr) AS total_credits,
    SUM(gjl.entered_dr - gjl.entered_cr) AS net_amount
FROM 
    gl_je_headers gjh,
    gl_je_lines gjl,
    gl_ledgers gl
WHERE 
    gjh.je_header_id = gjl.je_header_id
    AND gjh.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gjh.status != 'P'  -- Not posted
GROUP BY 
    gjh.je_header_id,
    gjh.name,
    gjh.description,
    gjh.period_name,
    gjh.je_source,
    gjh.je_category,
    gjh.status,
    gjh.created_by,
    gjh.creation_date
ORDER BY 
    gjh.creation_date DESC;

2.4 Journal Entries by User

Analyze journal entries created by specific users:

SELECT 
    fu.user_name,
    gjh.period_name,
    COUNT(DISTINCT gjh.je_header_id) AS journal_count,
    SUM(gjl.entered_dr) AS total_debits,
    SUM(gjl.entered_cr) AS total_credits
FROM 
    gl_je_headers gjh,
    gl_je_lines gjl,
    gl_ledgers gl,
    fnd_user fu
WHERE 
    gjh.je_header_id = gjl.je_header_id
    AND gjh.ledger_id = gl.ledger_id
    AND gjh.created_by = fu.user_id
    AND gl.name = 'Vision Operations'
    AND gjh.period_name LIKE '%25'  -- All periods in 2025
    AND gjh.je_source = 'Manual'
GROUP BY 
    fu.user_name,
    gjh.period_name
ORDER BY 
    fu.user_name, gjh.period_name;

3. Chart of Accounts Queries

These queries help you analyze your chart of accounts structure.

3.1 Account Combinations Query

List all active account combinations:

SELECT 
    gcc.code_combination_id,
    gcc.segment1 AS company,
    gcc.segment2 AS cost_center,
    gcc.segment3 AS account,
    gcc.segment4 AS product,
    gcc.segment5 AS intercompany,
    gcc.segment6 AS future,
    gcc.enabled_flag,
    gcc.start_date_active,
    gcc.end_date_active,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('ACCOUNTING_FLEX', 'GL#', 
        gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCES', 'Y', 'VALUE') AS account_desc
FROM 
    gl_code_combinations gcc,
    gl_ledgers gl
WHERE 
    gcc.chart_of_accounts_id = gl.chart_of_accounts_id
    AND gl.name = 'Vision Operations'
    AND gcc.enabled_flag = 'Y'
    AND (gcc.end_date_active IS NULL OR gcc.end_date_active > SYSDATE)
ORDER BY 
    gcc.segment1, gcc.segment2, gcc.segment3;

3.2 Segment Value Query

List values for a specific segment:

SELECT 
    fsv.flex_value AS segment_value,
    fsv.flex_value_meaning,
    fvt.description,
    fsv.enabled_flag,
    fsv.start_date_active,
    fsv.end_date_active
FROM 
    fnd_flex_values fsv,
    fnd_flex_values_tl fvt,
    fnd_flex_value_sets fvs,
    gl_ledgers gl,
    fnd_id_flex_segments fifs
WHERE 
    fsv.flex_value_id = fvt.flex_value_id
    AND fsv.flex_value_set_id = fvs.flex_value_set_id
    AND fifs.flex_value_set_id = fvs.flex_value_set_id
    AND fifs.id_flex_code = 'GL#'
    AND fifs.id_flex_num = gl.chart_of_accounts_id
    AND gl.name = 'Vision Operations'
    AND fifs.segment_name = 'COMPANY'  -- Change to segment name you want to query
    AND fvt.language = USERENV('LANG')
    AND fsv.enabled_flag = 'Y'
    AND (fsv.end_date_active IS NULL OR fsv.end_date_active > SYSDATE)
ORDER BY 
    fsv.flex_value;

3.3 Account Hierarchy Query

Retrieve account hierarchies:

SELECT 
    fvh.flex_value_set_id,
    fvs.flex_value_set_name,
    fvh.parent_flex_value AS parent_value,
    fvh.flex_value AS child_value,
    fvt.description AS child_description,
    fvh.distance_from_root,
    fvh.enabled_flag,
    fvh.start_date_active,
    fvh.end_date_active
FROM 
    fnd_flex_value_hierarchies fvh,
    fnd_flex_values_tl fvt,
    fnd_flex_values fv,
    fnd_flex_value_sets fvs
WHERE 
    fvh.flex_value = fv.flex_value
    AND fvh.flex_value_set_id = fv.flex_value_set_id
    AND fv.flex_value_id = fvt.flex_value_id
    AND fvh.flex_value_set_id = fvs.flex_value_set_id
    AND fvs.flex_value_set_name = 'ACCOUNT_VALUE_SET'  -- Change to your value set name
    AND fvt.language = USERENV('LANG')
    AND fvh.enabled_flag = 'Y'
    AND (fvh.end_date_active IS NULL OR fvh.end_date_active > SYSDATE)
ORDER BY 
    fvh.parent_flex_value, fvh.flex_value;

4. Period and Calendar Queries

These queries help you analyze accounting periods and calendars.

4.1 Period Status Query

Check the status of accounting periods:

SELECT 
    gps.period_name,
    gp.period_year,
    gp.period_num,
    gp.period_type,
    gp.start_date,
    gp.end_date,
    gps.closing_status,
    gps.open_flag,
    gl.name AS ledger_name
FROM 
    gl_period_statuses gps,
    gl_periods gp,
    gl_ledgers gl
WHERE 
    gps.period_name = gp.period_name
    AND gps.application_id = 101  -- GL application
    AND gps.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gp.period_year = 2025
ORDER BY 
    gp.period_year, gp.period_num;

4.2 Calendar Definition Query

Retrieve calendar definitions:

SELECT 
    gc.calendar_id,
    gc.name AS calendar_name,
    gc.period_type,
    gc.description,
    gc.quarters_per_year,
    gc.periods_per_year,
    gc.reference_calendar_id,
    gc.start_date,
    gc.creation_date,
    gc.created_by
FROM 
    gl_calendars gc,
    gl_ledgers gl
WHERE 
    gc.calendar_id = gl.calendar_id
    AND gl.name = 'Vision Operations';

5. Currency and Exchange Rate Queries

These queries help you analyze currency conversion rates.

5.1 Daily Conversion Rates Query

Retrieve currency conversion rates:

SELECT 
    gdr.from_currency,
    gdr.to_currency,
    gdr.conversion_type,
    gdr.conversion_date,
    gdr.conversion_rate,
    gdr.status_code,
    gdr.creation_date,
    gdr.created_by
FROM 
    gl_daily_rates gdr
WHERE 
    gdr.from_currency = 'EUR'
    AND gdr.to_currency = 'USD'
    AND gdr.conversion_type = 'Corporate'
    AND gdr.conversion_date BETWEEN TO_DATE('01-JAN-2025', 'DD-MON-YYYY') 
                               AND TO_DATE('31-MAR-2025', 'DD-MON-YYYY')
ORDER BY 
    gdr.conversion_date DESC;

5.2 Foreign Currency Journals Query

Analyze journals with foreign currency transactions:

SELECT 
    gjh.je_header_id,
    gjh.name AS journal_name,
    gjh.period_name,
    gjh.currency_code AS entered_currency,
    gl.currency_code AS ledger_currency,
    gjh.exchange_rate_type,
    gjh.exchange_rate,
    gjh.exchange_rate_date,
    SUM(gjl.entered_dr) AS total_entered_dr,
    SUM(gjl.entered_cr) AS total_entered_cr,
    SUM(gjl.accounted_dr) AS total_accounted_dr,
    SUM(gjl.accounted_cr) AS total_accounted_cr
FROM 
    gl_je_headers gjh,
    gl_je_lines gjl,
    gl_ledgers gl
WHERE 
    gjh.je_header_id = gjl.je_header_id
    AND gjh.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gjh.currency_code != gl.currency_code
    AND gjh.period_name LIKE '%25'  -- All periods in 2025
GROUP BY 
    gjh.je_header_id,
    gjh.name,
    gjh.period_name,
    gjh.currency_code,
    gl.currency_code,
    gjh.exchange_rate_type,
    gjh.exchange_rate,
    gjh.exchange_rate_date
ORDER BY 
    gjh.period_name, gjh.je_header_id;

6. Ledger Configuration Queries

These queries help you analyze ledger configurations.

6.1 Ledger Information Query

Retrieve ledger configuration details:

SELECT 
    gl.ledger_id,
    gl.name AS ledger_name,
    gl.short_name,
    gl.description,
    gl.ledger_category_code,
    gl.currency_code,
    gl.period_set_name,
    gl.accounted_period_type,
    gl.chart_of_accounts_id,
    gcc.name AS coa_name,
    gl.calendar_id,
    gc.name AS calendar_name,
    gl.first_ledger_period_name,
    gl.ret_earn_code_combination_id,
    gl.suspense_allowed_flag,
    gl.allow_intercompany_post_flag,
    gl.track_rounding_imbalance_flag
FROM 
    gl_ledgers gl,
    gl_chart_of_accounts_options gcc,
    gl_calendars gc
WHERE 
    gl.chart_of_accounts_id = gcc.chart_of_accounts_id
    AND gl.calendar_id = gc.calendar_id
    AND gl.name = 'Vision Operations';

6.2 Ledger Sets Query

Retrieve ledger sets and their assignments:

SELECT 
    gls.ledger_set_id,
    gls.name AS ledger_set_name,
    gls.description,
    gl.ledger_id,
    gl.name AS ledger_name,
    glsa.effective_date,
    glsa.end_date
FROM 
    gl_ledger_sets gls,
    gl_ledger_set_assignments glsa,
    gl_ledgers gl
WHERE 
    gls.ledger_set_id = glsa.ledger_set_id
    AND glsa.ledger_id = gl.ledger_id
ORDER BY 
    gls.name, gl.name;

7. Performance Optimization Queries

These queries help you analyze and optimize performance.

7.1 Large Journal Batches Query

Identify large journal batches that might impact performance:

SELECT 
    gjb.je_batch_id,
    gjb.name AS batch_name,
    gjb.description,
    gjb.status,
    gjb.posted_date,
    gjb.created_by,
    gjb.creation_date,
    COUNT(DISTINCT gjh.je_header_id) AS journal_count,
    COUNT(gjl.je_line_num) AS line_count
FROM 
    gl_je_batches gjb,
    gl_je_headers gjh,
    gl_je_lines gjl,
    gl_ledgers gl
WHERE 
    gjb.je_batch_id = gjh.je_batch_id
    AND gjh.je_header_id = gjl.je_header_id
    AND gjh.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gjb.status = 'P'  -- Posted batches
GROUP BY 
    gjb.je_batch_id,
    gjb.name,
    gjb.description,
    gjb.status,
    gjb.posted_date,
    gjb.created_by,
    gjb.creation_date
HAVING 
    COUNT(gjl.je_line_num) > 1000  -- Adjust threshold as needed
ORDER BY 
    COUNT(gjl.je_line_num) DESC;

7.2 Account Combinations Usage Query

Identify most frequently used account combinations:

SELECT 
    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || 
    gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 AS account,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('ACCOUNTING_FLEX', 'GL#', 
        gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCES', 'Y', 'VALUE') AS account_desc,
    COUNT(gjl.je_line_num) AS line_count,
    SUM(gjl.entered_dr) AS total_debits,
    SUM(gjl.entered_cr) AS total_credits
FROM 
    gl_je_lines gjl,
    gl_je_headers gjh,
    gl_code_combinations gcc,
    gl_ledgers gl
WHERE 
    gjl.je_header_id = gjh.je_header_id
    AND gjl.code_combination_id = gcc.code_combination_id
    AND gjh.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gjh.period_name LIKE '%25'  -- All periods in 2025
GROUP BY 
    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || 
    gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('ACCOUNTING_FLEX', 'GL#', 
        gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCES', 'Y', 'VALUE'),
    gcc.chart_of_accounts_id,
    gcc.code_combination_id
ORDER BY 
    COUNT(gjl.je_line_num) DESC
FETCH FIRST 100 ROWS ONLY;  -- Adjust as needed

8. Reconciliation Queries

These queries help with account reconciliation tasks.

8.1 Account Reconciliation Query

Retrieve journal lines for account reconciliation:

SELECT 
    gjh.je_header_id,
    gjh.name AS journal_name,
    gjh.description AS journal_description,
    gjh.period_name,
    gjh.je_source,
    gjh.je_category,
    gjh.posted_date,
    gjl.je_line_num,
    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || 
    gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 AS account,
    gjl.entered_dr,
    gjl.entered_cr,
    gjl.accounted_dr,
    gjl.accounted_cr,
    gjl.description AS line_description,
    gjl.reference_1,
    gjl.reference_2,
    gjl.reference_3
FROM 
    gl_je_headers gjh,
    gl_je_lines gjl,
    gl_code_combinations gcc,
    gl_ledgers gl
WHERE 
    gjh.je_header_id = gjl.je_header_id
    AND gjl.code_combination_id = gcc.code_combination_id
    AND gjh.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gjh.period_name = 'JAN-25'
    AND gcc.segment1 = '01'  -- Company
    AND gcc.segment2 = '000'  -- Cost Center
    AND gcc.segment3 = '1110'  -- Account (e.g., Cash)
ORDER BY 
    gjh.posted_date, gjh.je_header_id, gjl.je_line_num;

8.2 Subledger Reconciliation Query

Reconcile General Ledger with subledger data:

SELECT 
    gjh.je_source,
    gjh.je_category,
    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || 
    gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 AS account,
    SUM(gjl.accounted_dr) AS gl_debits,
    SUM(gjl.accounted_cr) AS gl_credits,
    SUM(gjl.accounted_dr - gjl.accounted_cr) AS gl_net
FROM 
    gl_je_headers gjh,
    gl_je_lines gjl,
    gl_code_combinations gcc,
    gl_ledgers gl
WHERE 
    gjh.je_header_id = gjl.je_header_id
    AND gjl.code_combination_id = gcc.code_combination_id
    AND gjh.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gjh.period_name = 'JAN-25'
    AND gjh.je_source = 'Payables'  -- Change to relevant subledger
GROUP BY 
    gjh.je_source,
    gjh.je_category,
    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || 
    gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6
ORDER BY 
    gjh.je_source, gjh.je_category, account;

9. Audit and Control Queries

These queries help with audit and control tasks.

9.1 Journal Approval Status Query

Check journal approval status:

SELECT 
    gjh.je_header_id,
    gjh.name AS journal_name,
    gjh.description,
    gjh.period_name,
    gjh.je_source,
    gjh.je_category,
    gjh.status,
    gjh.approval_status_code,
    gjh.posted_date,
    gjh.created_by,
    gjh.creation_date,
    fu.user_name AS created_by_user,
    gjh.last_updated_by,
    gjh.last_update_date
FROM 
    gl_je_headers gjh,
    gl_ledgers gl,
    fnd_user fu
WHERE 
    gjh.ledger_id = gl.ledger_id
    AND gjh.created_by = fu.user_id
    AND gl.name = 'Vision Operations'
    AND gjh.period_name = 'JAN-25'
    AND gjh.approval_status_code IS NOT NULL
ORDER BY 
    gjh.creation_date DESC;

9.2 Journal Reversal Query

Identify reversed journals:

SELECT 
    gjh.je_header_id,
    gjh.name AS journal_name,
    gjh.description,
    gjh.period_name,
    gjh.je_source,
    gjh.je_category,
    gjh.status,
    gjh.posted_date,
    gjh.accrual_rev_flag,
    gjh.accrual_rev_period_name,
    gjh.accrual_rev_status,
    gjh.accrual_rev_je_header_id,
    gjh.reversal_flag,
    gjh.reversal_period,
    gjh.reversal_status,
    gjh.reversal_je_header_id
FROM 
    gl_je_headers gjh,
    gl_ledgers gl
WHERE 
    gjh.ledger_id = gl.ledger_id
    AND gl.name = 'Vision Operations'
    AND gjh.period_name LIKE '%25'  -- All periods in 2025
    AND (gjh.accrual_rev_flag = 'Y' OR gjh.reversal_flag = 'Y')
ORDER BY 
    gjh.period_name, gjh.je_header_id;

10. Best Practices for SQL Queries

When working with Oracle Fusion General Ledger queries, keep these best practices in mind:

Use Bind Variables: For frequently executed queries, use bind variables instead of hard-coded values to improve performance.
Optimize Joins: Ensure your queries join tables efficiently, especially when working with large tables like GL_BALANCES and GL_JE_LINES.
Limit Results: Use WHERE clauses to limit results to only the data you need, and consider using FETCH FIRST n ROWS ONLY for large result sets.
Index Awareness: Be aware of indexed columns and design queries to leverage these indexes.
Test in Non-Production: Always test complex queries in a non-production environment first.
Consider Performance Impact: Be cautious when running queries during business hours, especially those that scan large tables.
Use Analytic Functions: For complex calculations, consider using analytic functions like SUM OVER, RANK, etc., which can be more efficient than multiple queries.
Document Your Queries: Add comments to explain complex logic and make queries easier to maintain.

Tip: When troubleshooting performance issues, use the EXPLAIN PLAN statement to understand how Oracle is executing your query, and look for opportunities to optimize.

Conclusion

These SQL queries provide a foundation for reporting and analysis in Oracle Fusion General Ledger. By understanding and utilizing these queries, you can extract valuable insights from your financial data, streamline reconciliation processes, and support audit requirements.

Remember to adapt these queries to your specific Oracle Fusion implementation, as table structures and column names may vary slightly between versions.

Note: The queries in this post are designed for Oracle Fusion Applications. If you're using Oracle E-Business Suite, you may need to modify these queries to match the E-Business Suite data model.

Labels: All Oracle General Ledger Tables, Oracle Fusion, Oracle Fusion General Ledger sql queries, Oracle Fusion GL SQL Queries, Oracle Sql Queries, SQL Queries

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'
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'
GL_CODE_COMBINATIONS - For account combination details
- Example use: SELECT * FROM GL_CODE_COMBINATIONS WHERE SEGMENT1 = '01' AND SEGMENT2 = '1000'
GL_LEDGERS - For ledger configuration information
- Example use: SELECT * FROM GL_LEDGERS WHERE NAME = 'US Operations'
GL_PERIODS - For period status and date ranges
- Example use: SELECT * FROM GL_PERIODS WHERE PERIOD_SET_ID = 123 ORDER BY START_DATE

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

Monday, 3 October 2016

All Oracle General Ledger Tables

Table Name

GL_ACCESS_SETS

GL_ACCESS_SET_ASSIGNMENTS

GL_ACCESS_SET_LEDGERS

GL_ACCESS_SET_NORM_ASSIGN

GL_ACCOUNT_HIERARCHIES

GL_ALLOC_BATCHES

GL_ALLOC_FORMULAS

GL_ALLOC_FORMULA_LINES

GL_ALLOC_HISTORY

GL_ALLOC_INTERIM_1

GL_ALLOC_INTERIM_2

GL_ALLOC_INTERIM_3

GL_ALLOC_TARGETS

GL_APPLICATION_GROUPS

GL_ARCHIVE_BALANCES

GL_ARCHIVE_BATCHES

GL_ARCHIVE_DAILY_BALANCES

GL_ARCHIVE_HEADERS

GL_ARCHIVE_HISTORY

GL_ARCHIVE_LINES

GL_ARCHIVE_REFERENCES

GL_AUTHORIZATION_LIMITS

GL_AUTOMATIC_POSTING_OPTIONS

GL_AUTOMATIC_POSTING_SETS

GL_AUTOREVERSE_OPTIONS

GL_AUTOREV_CRITERIA_SETS

GL_AUTO_ALLOC_BATCHES

GL_AUTO_ALLOC_BATCH_HISTORY

GL_AUTO_ALLOC_BAT_HIST_DET

GL_AUTO_ALLOC_REV_BATCHES

GL_AUTO_ALLOC_SETS

GL_AUTO_ALLOC_SET_HISTORY

GL_BALANCES

GL_BALANCES_DELTA

GL_BALANCES_DELTA_INTERIM

GL_BC_DUAL

GL_BC_DUAL2

GL_BC_EVENT_TIMESTAMPS

GL_BC_OPTIONS

GL_BC_OPTION_DETAILS

GL_BC_PACKETS

GL_BC_PACKETS_HISTS

GL_BC_PACKET_ARRIVAL_ORDER

GL_BC_PERIOD_MAP

GL_BUDGETS

GL_BUDGET_ASSIGNMENTS

GL_BUDGET_ASSIGNMENT_RANGES

GL_BUDGET_BATCHES

GL_BUDGET_ENTITIES

GL_BUDGET_FROZEN_RANGES

GL_BUDGET_INTERFACE

GL_BUDGET_INTERIM

GL_BUDGET_PERIOD_RANGES

GL_BUDGET_RANGE_INTERIM

GL_BUDGET_TYPES

GL_BUDGET_VERSIONS

GL_BUDORG_BC_OPTIONS

GL_BUD_DOWNLOAD_GT

GL_CAPTURED_INFO

GL_CARRYFORWARD_RANGES

GL_COA_MAPPINGS

GL_CODE_COMBINATIONS

GL_CONCURRENCY_CONTROL

GL_CONCURRENT_SCHEDULES

GL_CONSOLIDATION

GL_CONSOLIDATION_ACCOUNTS

GL_CONSOLIDATION_AUDIT

GL_CONSOLIDATION_HISTORY

GL_CONSOLIDATION_SETS

GL_CONS_BATCHES

GL_CONS_FLEXFIELD_MAP

GL_CONS_FLEX_HIERARCHIES

GL_CONS_SEGMENT_MAP

GL_CONS_SET_ASSIGNMENTS

GL_CROSS_RATE_RULES

GL_CROSS_RATE_RULE_DTLS

GL_DAILY_BALANCES

GL_DAILY_CONVERSION_RATES

GL_DAILY_CONVERSION_TYPES

GL_DAILY_RATES

GL_DAILY_RATES_INTERFACE

GL_DATE_PERIOD_MAP

GL_DEFAS_ACCESS_SETS

GL_DEFAS_ASSIGNMENTS

GL_DEFAS_RESP_ASSIGN

GL_DOC_SEQUENCE_AUDIT

GL_DRILL_ACCOUNT_RANGES_GT

GL_DRM_HIERARCHY_INTERFACE

GL_DRM_SEGVALUES_INTERFACE

GL_DYNAMIC_SUMM_COMBINATIONS

GL_EFC_BUDGET_MAPPING

GL_EFC_CHANGED_TRX

GL_EFC_DISABLED_TRIGGERS

GL_EFC_RUN_HISTORY

GL_ELIMINATION_HISTORY

GL_ELIMINATION_JOURNALS

GL_ELIMINATION_SETS

GL_ELIM_ACCOUNTS_MAP

GL_ELIM_BAL_OPTIONS

GL_ENCUMBRANCE_TYPES

GL_ENTITY_BUDGETS

GL_EXTRACT_HISTORY

GL_FIXED_CONV_RATES

GL_HISTORICAL_RATES

GL_HISTORICAL_RATE_RANGES

GL_IEA_AUTOGEN_MAP

GL_IEA_CLEARING_ACCOUNTS

GL_IEA_IMPORT_REGISTRY

GL_IEA_INTERFACE

GL_IEA_RECUR_BATCHES

GL_IEA_RECUR_HISTORY

GL_IEA_RECUR_LINES

GL_IEA_RECUR_TRANSACTIONS

GL_IEA_SEGMENT_MAP

GL_IEA_SEGMENT_RULE_MAP

GL_IEA_SUBSIDIARIES

GL_IEA_TRANSACTIONS

GL_IEA_TRANSACTION_LINES

GL_IEA_TRANSACTION_TYPES

GL_IMPORT_REFERENCES

GL_IMP_COA_ERR_INTERFACE

GL_IMP_COA_NORM_HIER_INTERFACE

GL_IMP_COA_SEG_VAL_INTERFACE

GL_INTERCOMPANY_ACCOUNTS

GL_INTERCOMPANY_ACC_SETS

GL_INTERFACE

GL_INTERFACE_CONTROL

GL_INTERFACE_HISTORY

GL_ISPEED_COA_SEGMENTS

GL_ISPEED_COA_TEMPLATES

GL_JE_BATCHES

GL_JE_CATEGORIES_TL

GL_JE_HEADERS

GL_JE_INCLUSION_RULES

GL_JE_LINES

GL_JE_LINES_RECON

GL_JE_SEGMENT_VALUES

GL_JE_SEGVALS_UPGRADE

GL_JE_SOURCES_TL

GL_JOURNAL_REPORTS_ITF

GL_LEDGERS

GL_LEDGER_BSV_GT

GL_LEDGER_CONFIGURATIONS

GL_LEDGER_CONFIG_DETAILS

GL_LEDGER_LE_BSV_GT

GL_LEDGER_NORM_SEG_VALS

GL_LEDGER_RELATIONSHIPS

GL_LEDGER_SEGMENT_VALUES

GL_LEDGER_SETS

GL_LEDGER_SET_ASSIGNMENTS

GL_LEDGER_SET_NORM_ASSIGN

GL_LEDGER_SET_REQUESTS

GL_LEGAL_ENTITIES_BSVS

GL_LE_VALUE_SETS

GL_LOOKUPS_OLD

GL_MC_BOOK_ASSIGNMENTS

GL_MC_BOOK_ASSIGNMENTS_11I

GL_MC_CONVERSION_RULES

GL_MC_CONVERSION_RULES_11I

GL_MC_REPORTING_OPTIONS

GL_MC_REPORTING_OPTIONS_11I

GL_MC_UPGRADE_HISTORY

GL_MC_UPGRADE_RATES

GL_MGT_SEG_UPGRADE

GL_MGT_SEG_UPGRADE_H

GL_MOVEMERGE_ACCOUNTS

GL_MOVEMERGE_REQUESTS

GL_NET_INCOME_ACCOUNTS

GL_OASIS_FIN_ASSIGNMENTS

GL_OASIS_FIN_ASSIGNMENTS_11I

GL_OASIS_FIN_ITEMS

GL_OASIS_FIN_ITEMS_11I

GL_OASIS_FIN_METRICS

GL_OASIS_FIN_METRICS_11I

GL_OASIS_SUMMARY_DATA

GL_PERIODS

GL_PERIOD_SETS

GL_PERIOD_STATUSES

GL_PERIOD_TYPES

GL_POSTING_INTERIM

GL_RECON_BALANCE

GL_RECURRING_BATCHES

GL_RECURRING_HEADERS

GL_RECURRING_LINES

GL_RECURRING_LINE_CALC_RULES

GL_REPORT_INTERFACE

GL_REVALUATION

GL_REVALUATIONS

GL_REVALUATION_ACCOUNTS

GL_REVAL_ACCOUNT_RANGES

GL_ROLLUP_GROUP_SCORES

GL_ROW_MULTIPLIERS

GL_RX_TRIAL_BALANCE_ITF

GL_SEGMENT_FREQUENCIES

GL_SEGVAL_INHERIT_HISTORY

GL_SEG_VAL_HIERARCHIES

GL_SEG_VAL_NORM_HIERARCHY

GL_SETS_OF_BOOKS

GL_SETS_OF_BOOKS_11I

GL_SHARES_ACTIVITY

GL_SHARES_OUTSTANDING

GL_STAT_ACCOUNT_UOM

GL_STORAGE_PARAMETERS

GL_SUMMARY_BC_OPTIONS

GL_SUMMARY_HIERARCHIES

GL_SUMMARY_HIERARCHY_INT

GL_SUMMARY_INTERIM

GL_SUMMARY_TEMPLATES

GL_SUSPENSE_ACCOUNTS

GL_SYSTEM_SETUP

GL_SYSTEM_SETUP_11I

GL_SYSTEM_USAGES

GL_TAX_CODES

GL_TAX_OPTIONS

GL_TAX_OPTION_ACCOUNTS

GL_TEMPORARY_COMBINATIONS

GL_TRACK_DELTA_BALANCES

GL_TRANSACTION_CALENDAR

GL_TRANSACTION_DATES

GL_TRANSACTION_RATE_EXCEPTIONS

GL_TRANSLATION_INTERIM

GL_TRANSLATION_RATES

GL_TRANSLATION_RATES_11I

GL_TRANSLATION_STATUSES

GL_TRANSLATION_TRACKING

GL_UPDATE_RECOVERY_H

GL_UPGRADE_STATUS

GL_USSGL_ACCOUNT_PAIRS

GL_USSGL_TRANSACTION_CODES

GL_WEIGHTED_AVG_DETAILS

GL_XFR_CALENDARS

GL_XFR_CALENDAR_MAPPINGS

GL_XFR_CCID_MAPPINGS

GL_XFR_INTERFACE

GL_XFR_LEDGERS

GL_XFR_LEDGER_BSV_MAPS

GL_XFR_LEDGER_MAPPINGS

GL_XFR_LED_MAP_PERIODS

GL_XFR_PERIODS

GL_XFR_PERIOD_MAPPINGS

GL_XFR_REJECTED_BSVS_GT

GL_XFR_SOURCE_SYSTEMS

GL_XFR_SYSTEM_LEDGERS

GL_XFR_TRACKING

Labels: All Oracle General Ledger Tables, Oracle R12 tables, Oracle Tables

DO YOU KNOW ALL ABOUT ORACLE FINANCIALS

Wednesday, 19 March 2025

Essential SQL Queries for Oracle Fusion General Ledger

Following our comprehensive guide to Oracle Fusion General Ledger tables, this post provides essential SQL queries for common reporting and analysis tasks. These queries are organized by functional area to help you quickly find solutions for your specific needs.

1. Account Balance Queries

1.1 Basic Period Balance Query

1.2 Trial Balance Query

1.3 Year-to-Date Balance Query

1.4 Balance Comparison (Actual vs. Budget)

2. Journal Entry Queries

2.1 Journal Entry Header and Line Details

2.2 Journal Entry Summary by Source and Category

2.3 Unposted Journal Entries

2.4 Journal Entries by User

3. Chart of Accounts Queries

3.1 Account Combinations Query

3.2 Segment Value Query

3.3 Account Hierarchy Query

4. Period and Calendar Queries

4.1 Period Status Query

4.2 Calendar Definition Query

5. Currency and Exchange Rate Queries

5.1 Daily Conversion Rates Query

5.2 Foreign Currency Journals Query

6. Ledger Configuration Queries

6.1 Ledger Information Query

6.2 Ledger Sets Query

7. Performance Optimization Queries

7.1 Large Journal Batches Query

7.2 Account Combinations Usage Query

8. Reconciliation Queries

8.1 Account Reconciliation Query

8.2 Subledger Reconciliation Query

9. Audit and Control Queries

9.1 Journal Approval Status Query

9.2 Journal Reversal Query

10. Best Practices for SQL Queries

Conclusion

All Oracle Fusion General Ledger Tables

Why Understanding GL Tables Matters

Core Financial Data Tables

Journal Entry Management Tables

Ledger Configuration Tables

Chart of Accounts Tables

Calendar and Period Tables

Currency and Exchange Rate Tables

Access Control and Security Tables

Allocation and Automation Tables

Mapping and Transformation Tables

Reversal and Adjustment Tables

Understanding Table Relationships

Key Tables for Common Tasks

Best Practices for Working with GL Tables

Conclusion

Monday, 3 October 2016

All Oracle General Ledger Tables

About Me

Links

Previous Posts

Archives