SQL query to extract gl journal details in Oracle Fusion

Hi Friends, we are going to discuss about the sql query to extract the gl journal details in Oracle Fusion. We will share the complete sql query which will help to fetch the complete gl journals details in oracle fusion. This is one of the important sql query if we want to extract the complete gl dump from oracle fusion system. This sql query is very useful to develop the custom BIP reports in oracle fusion for GL journals. We will also share the Important GL tables helps develop the gl journals details sql query in oracle fusion. Please find below the complete details about sql query to extract gl journal details in Oracle Fusion.

SQL query to extract gl journal details in Oracle Fusion

Important Table in the query to extract gl journal details in Oracle Fusion

1.GL_JE_LINES

2.GL_JE_HEADERS

3.GL_CODE_COMBINATIONS

4.GL_JE_BATCHES

Detail SQL query to extract gl journal details in Oracle Fusion

Here below is the complete sql query to extract the gl journals detail in oracle fusion.

SELECT 

glh.name "Journal_Name",

glh.JE_SOURCE,

glh.JE_CATEGORY,

       glh.JE_HEADER_ID || gll.JE_LINE_NUM "JE_REFERENCE",

       gcc.SEGMENT1 "ENTITY", 

       gll.LEDGER_ID,

     gcc.SEGMENT1 || '-' || gcc.SEGMENT2 || '-' ||

       gcc.SEGMENT3 || '-' || gcc.SEGMENT4 || '-' ||

     gcc.SEGMENT5 || '-' || gcc.SEGMENT6 || '-' || gcc.SEGMENT7 "ACCOUNT_NUMBER",

    glh.posted_date "DATETIME_POSTED",

    gll.period_name "ACCOUNTING_PERIOD",

  (NVL(gll.ACCOUNTED_CR, 0)) ACCOUNTED_CR,

  (NVL(gll.ACCOUNTED_DR, 0)) ACCOUNTED_DR,

    ((NVL(gll.ACCOUNTED_CR, 0)) - (NVL(gll.ACCOUNTED_DR, 0))) "AMOUNT",

    glb.description  "BATCH_DESC",

    glh.description "HEADER_DESC",

    gll.description "LINE_DESC"

FROM GL_JE_LINES                 gll,

     GL_JE_HEADERS              glh,

     GL_CODE_COMBINATIONS        gcc,

     gl_je_batches            glb

WHERE 1=1

AND      glh.je_batch_id = glb.je_batch_id

AND      gll.je_header_id = glh.je_header_id

and gll.LEDGER_ID=:P_LEDGER_ID

and gcc.SEGMENT1='0012'

AND     gll.code_combination_id = gcc.code_combination_id

AND      NVL(gll.status, '-') = 'P'

AND     gll.period_name in ('Apr-FY19-20')

ORDER BY glh.posted_date

SQL query to extract gl journal details in Oracle Fusion