GL Journal Extraction SQL Query in Oracle EBS and Oracle Fusion

-

Introduction

                           Extracting General Ledger (GL) journal data is one of the most common and essential tasks in both Oracle E-Business Suite (EBS) and Oracle Fusion environments. Whether you're supporting reconciliation, audit reporting, or building a custom data integration, this detailed SQL helps you fetch journal entries, ledger information, accounting amounts, project attributes, and line-level details in one go.

Use Case

This GL journal extraction query is helpful when:

  • Users want detailed journal information including batch, header, line, and segment details.

  • Auditors or finance teams request transactional history within a specific ledger or period.

  • Custom reporting or reconciliation processes are being built in BI Publisher, OTBI, or any third-party reporting tools.


SQL Query:

SELECT
    gl.ledger_id,
    TO_CHAR(gjh.default_effective_date,'YYYY/MM/DD') default_effective_date,
    gjh.je_source,
    gjh.je_category,
    gjh.currency_code                                                 Currency_Code,
    TO_CHAR(gjh.date_created,'YYYY/MM/DD')  Creation_Date,
    gjh.actual_flag                                                       Actual_Flag,
    gcc.segment1,
    gcc.segment2,
    gcc.segment3,
    gcc.segment4,
    gcc.segment5,
    gcc.segment6,
    gcc.segment7,
    gcc.segment8,
    gcc.segment9,
    ROUND(gjl.ENTERED_DR,2)         Entered_Debit_Amount,
    ROUND(gjl.ENTERED_CR,2)         Entered_Credit_Amount,
    ROUND(gjl.accounted_dr,2)             Converted_Debit_Amount,
    ROUND(gjl.accounted_cr,2)             Converted_Credit_Amount,
    gjb.name                                             REFERENCE1_Batch_Name,
    gjb.description                                    REFERENCE2_Batch_Description,
    gjl.description                 REFERENCE10_Line_Description,
    gjh.currency_conversion_type,
    gjh.currency_conversion_date,
    gjh.currency_conversion_rate,
    gjl.attribute11          DOC_SEQUENCE_VALUE,
    gjl.attribute12          attribute_currency_type,
    gjl.attribute13          project_segment,
    gjl.attribute14          project_desc,
    gl.name                  Ledger_Name,
    gjh.ENCUMBRANCE_TYPE_ID Encumbrance_Type_ID,
    gjl.period_name             Period_Name,
    gjb.je_batch_id,
    gjh.je_header_id,
    gjl.je_line_num

--add columns needed 

FROM
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_ledgers gl,
gl_periods gp

--add tables needed 

WHERE
1 = 1
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.status = 'P'
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = 'A'
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.ledger_id = gl.ledger_id
AND gjl.period_name = gp.period_name
AND gp.period_set_name = 'give set name here'
AND gp.adjustment_period_flag = 'N'
AND gl.name = :p_ledger_name
        AND gjh.je_source = NVL(:p_source, gjh.je_source)
AND gcc.segment1 = :p_company
AND gp.period_year = NVL(:p_year, gp.period_year)
        AND (gjl.period_name IN (:p_period) OR 'All' IN ('All' || :p_period))
AND TRUNC(gjl.effective_date) BETWEEN NVL(:p_from_date, gjl.effective_date)         AND NVL(:p_to_date, gjl.effective_date)
ORDER BY
gjh.je_header_id,
        gjl.je_line_num
Location: Hyderabad, Telangana, India

Comments

Post a Comment

Popular posts from this blog

Query to find Responsibility and Request Group from Concurrent Program

-
 In this blog, we’ll break down a query that helps you trace a specific Concurrent Program through its Application , Request Group , and associated Responsibilities in Oracle EBS R12 Objective The goal of this query is to find: The User-Friendly Name of a Concurrent Program The Technical Concurrent Program Name The Application to which it belongs The Request Group under which it is registered The Responsibility that grants access to it This is especially useful for ensuring correct program registration or for debugging access issues. SQL Query:      SELECT DISTINCT      fcpl.user_concurrent_program_name,     fcp.concurrent_program_name,     fapp.application_name,     frg.request_group_name,     fnrtl.responsibility_name FROM      apps.fnd_request_groups         frg,     apps.fnd_application_tl         fapp,   ...
Read more »»»»

How to Extract User Role Details with SQL in Oracle Fusion

-
When working with Oracle HCM / Fusion Applications , administrators and developers often need to fetch details about users, their assigned roles, and their effective dates. This helps in audits, access reviews, and troubleshooting authorization issues. In this post, we’ll walk through a SQL query that retrieves user-role information from Oracle HCM tables. SQL Query:                          SELECT     pus.user_id             "User ID",     pus.username            "Username",     ppnaf.full_name         "Full Name",     prdn.role_common_name   "Role Common Name",     prdtl.role_name         "Role Name",     to_char(purs.start_date, 'DD-MON-YYYY') "Role Start Date",     to_char(purs.end_date, 'DD-MON-YYYY')   "...
Read more »»»»

3 Way PO Matching SQL Query in Oracle Fusion: Purchase Order, Receipt, and Invoice Reconciliation

-
3 Way Matching SQL Query in Oracle Fusion: Purchase Order, Receipt, and Invoice Reconciliation In any procurement system, ensuring the accuracy of supplier payments is critical. One of the most robust methods for validating supplier invoices is the 3-way matching process. This blog breaks down the 3-way matching concept in Oracle E-Business Suite (EBS) or Oracle Fusion, explains its benefits, and presents a detailed SQL query that helps extract and reconcile PO, Receipt, and Invoice data. What is 3-Way Matching ? 3-Way Matching is a process that compares the following three documents before payment is released: Purchase Order (PO) – What you ordered Receipt – What you received Invoice – What the supplier billed This comparison ensures that you only pay for goods that were ordered and received , avoiding overpayments, duplicates, and fraud. Matching Criteria   Element              PO      Receipt  ...
Read more »»»»