SQL query to Extract GL Journal Detail in Oracle Apps : GL Dump Sql Query

SQL query to Extract GL Journal Detail in Oracle Apps : GL Dump Sql Query

This sql query to extract GL journals data in Oracle application. We can find the GL journals from all the sources. This will help to provide the GL journals dump for the specific period. This also helps to get the Journal Submitter and journal Approver name. This sql query helps to extract complete journal data from GL in oracle application.

 

 

SELECT

glh.name "Journal_Name",

glh.JE_SOURCE,

glh.JE_CATEGORY,

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

       gcc.SEGMENT1 "ENTITY",

     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",

    fu2.USER_NAME "CREATED_BY_USER",

  (select ppf3.full_name from         apps.per_all_people_f    ppf3

  where person_id=glb.APPROVER_EMPLOYEE_ID

  and ppf3.effective_end_date >sysdate) approver,

  (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 apps.GL_JE_LINES                 gll,

     apps.GL_JE_HEADERS              glh,

     apps.GL_CODE_COMBINATIONS        gcc,

     apps.gl_je_batches            glb,

     apps.FND_USER                    fu1,

     apps.FND_USER                    fu2

WHERE gll.last_updated_by =fu1.user_id

AND      gll.created_by = fu2.user_id

AND      glh.je_batch_id = glb.je_batch_id

AND      gll.je_header_id = glh.je_header_id

and gll.LEDGER_ID=12332

and gcc.SEGMENT1='0001'

AND     gll.code_combination_id = gcc.code_combination_id

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

AND     gll.period_name in ('apr-18')

ORDER BY 1, 2

 

 

SQL query to Extract GL Journal Detail in Oracle Apps : GL Dump Sql Query