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:

  1. Use Bind Variables: For frequently executed queries, use bind variables instead of hard-coded values to improve performance.
  2. Optimize Joins: Ensure your queries join tables efficiently, especially when working with large tables like GL_BALANCES and GL_JE_LINES.
  3. 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.
  4. Index Awareness: Be aware of indexed columns and design queries to leverage these indexes.
  5. Test in Non-Production: Always test complex queries in a non-production environment first.
  6. Consider Performance Impact: Be cautious when running queries during business hours, especially those that scan large tables.
  7. Use Analytic Functions: For complex calculations, consider using analytic functions like SUM OVER, RANK, etc., which can be more efficient than multiple queries.
  8. 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: , , , , ,

posted by SM @ 20:37   0 Comments