SQL query to get the journal approver in Oracle Fusion

Hi friends, we are going to discuss about the SQL query to get the journal approver in Oracle Fusion. We will share the detail sql query to find out the GL journals approver name in oracle fusion. If we have an requirement to get the GL journals approvers then we can refer this sql query. This sql query also helps to develop the custom GL journals dump BIP report in oracle fusion. This is one of the important sql query in oracle fusion related to gl journals and the approver detail. Oracle fusion has provided the standard API's which helps to get the gl journals approver name and journals approved date. We will try to share these API's to get the journals approvers details. Please find below the complete detail about SQL query to get the journal approver in Oracle Fusion.

SQL query to get the journal approver in Oracle Fusion

2 Important Standard API's to get the journal approver in Oracle Fusion

gl_journals_rpt_pkg.get_action_user(GLB.je_batch_id, 'APPROVED') --- FOR JOURNAL APPROVER

gl_journals_rpt_pkg.get_action_date(GLB.je_batch_id, 'APPROVED') --- FOR JOURNAL APPROVED DATE

Detail SQL Query to get the journal approver and other gl journals details in Oracle Fusion

Here below is the detail sql query to find out the journal approver in Oracle Fusion

SELECT 

glh.name "Journal_Name",

glh.JE_SOURCE,

glh.JE_CATEGORY,

       glh.JE_HEADER_ID "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",

 DECODE(GLB.approval_status_code, 'A', gl_journals_rpt_pkg.get_action_user(GLB.je_batch_id, 'APPROVED'), null) APPROVED_BY,

  DECODE(GLB.approval_status_code, 'A', gl_journals_rpt_pkg.get_action_date(GLB.je_batch_id, 'APPROVED'), null) BATCH_APPROVED_DATE

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 glh.CURRENCY_CODE='INR'

AND     gll.code_combination_id = gcc.code_combination_id

AND   GLH.JE_SOURCE='Spreadsheet'

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

AND     gll.period_name='APR-19'

ORDER BY glh.posted_date

SQL query to get the journal approver in Oracle Fusion