GL Journals Detail Report

SELECT
    GSOB.NAME                                               AS Ledger_Name,
    GJB.DEFAULT_PERIOD_NAME                                 AS Period_Name,
    GJB.NAME                                                AS Journal_Batch_Name,
    GJH.NAME                                                AS Journal_Name,
    GJL.JE_LINE_NUM                                         AS Journal_Line_Number,
    GJL.DESCRIPTION                                         AS Journal_Line_Description,
    GCC.SEGMENT1                                            AS Company,
    GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(
        GCC.CHART_OF_ACCOUNTS_ID,
        1,
        GCC.SEGMENT1
    )                                                       AS Company_Description,
    GCC.SEGMENT2                                            AS Segment_2,
    GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(
        GCC.CHART_OF_ACCOUNTS_ID,
        2,
        GCC.SEGMENT2
    )                                                       AS Segment_2_Description,
    GCC.SEGMENT3                                            AS Segment_3,
    GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(
        GCC.CHART_OF_ACCOUNTS_ID,
        3,
        GCC.SEGMENT3
    )                                                       AS Segment_3_Description,
    GCC.SEGMENT4                                            AS Segment_4,
    GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(
        GCC.CHART_OF_ACCOUNTS_ID,
        4,
        GCC.SEGMENT4
    )                                                       AS Segment_4_Description,
    GCC.SEGMENT5                                            AS Segment_5,
    GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(
        GCC.CHART_OF_ACCOUNTS_ID,
        5,
        GCC.SEGMENT5
    )                                                       AS Segment_5_Description,
    GCC.SEGMENT6                                            AS Segment_6,
    GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(
        GCC.CHART_OF_ACCOUNTS_ID,
        6,
        GCC.SEGMENT6
    )                                                       AS Segment_6_Description,
    GJH.JE_SOURCE                                           AS Journal_Source,
    GJH.JE_CATEGORY                                         AS Journal_Category,
    TO_CHAR(
        GJH.POSTED_DATE,
        'DD-MON-YYYY',
        'NLS_DATE_LANGUAGE=AMERICAN'
    )                                                       AS Posted_Date,
    GP.PERIOD_YEAR                                          AS Posted_Year,
    GP.QUARTER_NUM                                          AS Posted_Quarter,
    GP.PERIOD_NAME                                          AS Posted_Month,
    GP.PERIOD_YEAR                                          AS Period_Year,
    GP.QUARTER_NUM                                          AS Period_Quarter,
    GP.PERIOD_NAME                                          AS Period_Month,
    (
        SELECT
            GLU.MEANING
        FROM
            GL_LOOKUPS                  GLU
        WHERE
                GLU.LOOKUP_TYPE         = 'JE_BATCH_APPROVAL_STATUS'
            AND GLU.LOOKUP_CODE         = GJB.APPROVAL_STATUS_CODE
    )                                                       AS Approval_Status,
    (
        SELECT
            GLU.MEANING
        FROM
            GL_LOOKUPS                  GLU
        WHERE
                GLU.LOOKUP_TYPE         = 'JL_INQ_STATUS'
            AND GLU.LOOKUP_CODE         = GJL.STATUS
    )                                                       AS Posted_Status,
    GJL.CURRENCY_CODE                                       AS Currency_Code,
    NVL(GJL.ENTERED_DR, 0) - NVL(GJL.ENTERED_CR, 0)         AS Amount,
    NVL(GJL.ACCOUNTED_DR, 0) - NVL(GJL.ACCOUNTED_CR, 0)     AS Accounted_Amount,
    GSOB.CURRENCY_CODE                                      AS Ledger_Currency_Code,
    GJL.CREATED_BY                                          AS Journal_Prepared_By,
    PPNFV.DISPLAY_NAME                                      AS Journal_Approved_By,
    GJL.CREATION_DATE                                       AS Creation_Date,
    GJL.CREATED_BY                                          AS Created_By,
    GJL.LAST_UPDATE_DATE                                    AS Last_Update_Date,
    GJL.LAST_UPDATED_BY                                     AS Last_Updated_By,
    GJAL.ACTION_CODE                                        AS Action_Code,
    GJAL.ACTION_DATE                                        AS Action_Date,
    GP.PERIOD_SET_NAME                                      AS Period_Set_Name
FROM
    FUSION.GL_SETS_OF_BOOKS         GSOB,
    FUSION.GL_CODE_COMBINATIONS     GCC,
    FUSION.GL_JE_LINES              GJL,
    FUSION.GL_JE_HEADERS            GJH,
    FUSION.GL_JE_BATCHES            GJB,
    GL_PERIODS                      GP,
    PER_PERSON_NAMES_F_V            PPNFV,
    GL_JE_ACTION_LOG                GJAL
WHERE
        GJB.JE_BATCH_ID             = GJH.JE_BATCH_ID
    AND GJH.JE_HEADER_ID            = GJL.JE_HEADER_ID
    AND GJL.CODE_COMBINATION_ID     = GCC.CODE_COMBINATION_ID
    AND GJH.LEDGER_ID               = GSOB.SET_OF_BOOKS_ID
    AND GJB.APPROVER_EMPLOYEE_ID    = PPNFV.PERSON_ID (+)
    AND GJAL.JE_BATCH_ID            = GJB.JE_BATCH_ID
    AND GJAL.ACTION_DATE            = (
        SELECT
            MAX(GJAL1.ACTION_DATE)
        FROM
            GL_JE_ACTION_LOG        GJAL1
        WHERE
            GJAL1.JE_BATCH_ID       = GJAL.JE_BATCH_ID
    )
    AND GP.PERIOD_SET_NAME          = 'AccountingMMYY'
    AND TRUNC(GJH.POSTED_DATE) BETWEEN GP.START_DATE AND GP.END_DATE