AHCS Create Accounting Report

WITH SECURITY_TBL AS
(
    SELECT DISTINCT GL.LEDGER_ID
    FROM 
        GL_LEDGERS GL,
        GL_ACCESS_SETS GAS,
        FUN_USER_ROLE_DATA_ASGNMNTS FURDA,
        PER_ROLES_DN PRD,
        PER_USER_ROLES PUR,
        PER_USERS PU
    WHERE 
        UPPER(GL.LEDGER_CATEGORY_CODE) <> 'NONE'
        AND GAS.ACCESS_SET_ID = GL.IMPLICIT_ACCESS_SET_ID
        AND (GAS.ACCESS_SET_ID = FURDA.ACCESS_SET_ID OR GL.LEDGER_ID = FURDA.LEDGER_ID)
        AND UPPER(FURDA.role_name) IN (
            'XXC_AHCS_MANAGER_JOB',
            'XXC_GL_INQUIRY_JOB',
            'XXC_GENERAL_ACCOUNTANT_JOB',
            'XXC_GENERAL_ACCOUNTING_MANAGER_JOB'
        )
        AND UPPER(FURDA.ACTIVE_FLAG) = 'Y'
        AND UPPER(FURDA.ROLE_NAME) = UPPER(PRD.ROLE_COMMON_NAME)
        AND PRD.ROLE_ID = PUR.ROLE_ID
        AND PUR.USER_ID = PU.USER_ID
        AND UPPER(PU.USERNAME) = UPPER(:xdo_user_name)
        AND PU.USER_GUID = FURDA.USER_GUID
),

DATA AS (
    SELECT
         GLL.NAME LEDGER_NAME
        ,XAH.PERIOD_NAME AS ACCOUNTING_PERIOD
        ,XAH.ACCOUNTING_DATE
        ,XEC.NAME AS EVENT_CLASS
        ,XET.NAME AS EVENT_TYPE
        ,XE.EVENT_DATE
        ,GJST.USER_JE_SOURCE_NAME AS JOURNAL_SOURCE 
        ,GJCT.USER_JE_CATEGORY_NAME AS JOURNAL_CATEGORY
        ,GJB.NAME AS JOURNAL_BATCH_NAME
        ,XTE.TRANSACTION_NUMBER
        ,XAL.AE_LINE_NUM LINE_NUMBER
        ,XAL.CURRENCY_CODE ENTERED_CURRENCY
        ,NVL(XAL.ENTERED_DR,0) ENTERED_DR
        ,NVL(XAL.ENTERED_CR,0) ENTERED_CR
        ,GLL.CURRENCY_CODE LEDGER_CURRENCY
        ,NVL(XAL.ACCOUNTED_DR,0) ACCOUNTED_DR
        ,NVL(XAL.ACCOUNTED_CR,0) ACCOUNTED_CR
        ,CASE 
            WHEN NVL(GCC.SEGMENT4,'-1') != '-1' THEN 
                GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5
                || '.' || GCC.SEGMENT6 || '.' || GCC.SEGMENT7 || '.' || GCC.SEGMENT8 || '.' || GCC.SEGMENT9 
         END AS ACCOUNT_CODE_COMBINATION
        ,FLV3.MEANING AS ACCOUNTING_CLASS
        ,FLV.MEANING ACCOUNTING_ENTRY_STATUS
        ,NVL(FLV2.DESCRIPTION, 'Unposted') POSTING_STATUS
        ,XAH.CREATION_DATE JOURNAL_CREATION_DATE
        ,XAH.DESCRIPTION HEADER_DESCRIPTION
        ,XAL.DESCRIPTION LINE_DESCRIPTION
        ,ROW_NUMBER() OVER (
            PARTITION BY 
                GLL.NAME,                -- Ledger
                GCC.CODE_COMBINATION_ID, -- Account Combination
                XTE.TRANSACTION_NUMBER,
                XAL.AE_LINE_NUM
            ORDER BY 
                XAH.AE_HEADER_ID
        ) RN
    FROM
        XLA_TRANSACTION_ENTITIES XTE,
        XLA_TRANSACTION_HEADERS XTH,
        XLA_AE_HEADERS XAH,
        GL_LEDGERS GLL,
        XLA_SUBLEDGERS XSL,
        XLA_AE_LINES XAL,
        GL_CODE_COMBINATIONS GCC,
        XLA_SUBLEDGERS_TL XST,
        XLA_EVENT_TYPES_TL XET,
        GL_JE_SOURCES_TL GJST,
        GL_JE_CATEGORIES_TL GJCT,
        GL_JE_BATCHES GJB,
        FND_LOOKUP_VALUES_VL FLV2,
        FND_LOOKUP_VALUES_VL FLV,
        FND_LOOKUP_VALUES_VL FLV3,
        GL_IMPORT_REFERENCES GIR,
        XLA_EVENT_CLASSES_TL XEC,
        XLA_EVENTS XE,
        SECURITY_TBL ST
    WHERE 1 = 1
        AND ST.LEDGER_ID = GLL.LEDGER_ID
        AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
        AND XAH.APPLICATION_ID = XTH.APPLICATION_ID(+)
        AND XAH.EVENT_ID = XTH.EVENT_ID(+)
        AND XAH.APPLICATION_ID = XAL.APPLICATION_ID
        AND XAH.ENTITY_ID = XTE.ENTITY_ID 
        AND XAH.APPLICATION_ID = XTE.APPLICATION_ID  
        AND XAH.APPLICATION_ID = XSL.APPLICATION_ID 
        AND XSL.APPLICATION_ID = XST.APPLICATION_ID
        AND XST.LANGUAGE = userenv('LANG')
        AND XAH.APPLICATION_ID = XET.APPLICATION_ID
        AND XAH.EVENT_TYPE_CODE = XET.EVENT_TYPE_CODE
        AND XET.LANGUAGE = userenv('LANG')
        AND XSL.IMPLEMENTATION_MODE_CODE = 'SIMPLIFIED'
        AND XAH.LEDGER_ID = GLL.LEDGER_ID
        --AND XST.APPLICATION_NAME ='DOM INVENTORY'
        --AND NVL(XAL.SR31,'-1')!='-1'
        AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
        AND GJST.JE_SOURCE_NAME = XSL.JE_SOURCE_NAME
        AND GJCT.JE_CATEGORY_NAME(+) = XAH.JE_CATEGORY_NAME
        AND GJCT.LANGUAGE(+) = userenv('LANG')
        AND GJST.LANGUAGE = userenv('LANG')
        AND FLV3.LOOKUP_TYPE = 'XLA_ACCOUNTING_CLASS'
        AND FLV3.LOOKUP_CODE = XAL.ACCOUNTING_CLASS_CODE
        AND FLV.LOOKUP_TYPE = 'XLA_ACCOUNTING_ENTRY_STATUS'
        AND FLV.LOOKUP_CODE = XAH.ACCOUNTING_ENTRY_STATUS_CODE
        AND GJB.GROUP_ID(+) = XAH.GROUP_ID
        AND FLV2.LOOKUP_TYPE(+) = 'MJE_BATCH_STATUS'
        AND FLV2.LOOKUP_CODE(+) = GJB.STATUS
        AND GIR.GL_SL_LINK_ID(+) = XAL.GL_SL_LINK_ID	
        AND GIR.GL_SL_LINK_TABLE(+) = XAL.GL_SL_LINK_TABLE	
        AND GIR.JE_BATCH_ID = GJB.JE_BATCH_ID(+)
        AND XAL.ACCOUNTING_CLASS_CODE = (
            SELECT XAL1.ACCOUNTING_CLASS_CODE
            FROM XLA_AE_LINES XAL1 
            WHERE XAL1.AE_HEADER_ID = XAL.AE_HEADER_ID
              AND XAL1.AE_LINE_NUM = XAL.AE_LINE_NUM
              AND XAL1.APPLICATION_ID = XAL.APPLICATION_ID
              AND (
                    XAL1.ACCOUNTING_CLASS_CODE != 'EXCHANGE_GAIN_LOSS'
                    OR (XAL1.ACCOUNTED_DR != 0 OR XAL1.ACCOUNTED_CR != 0)
                  )
        )
        AND XEC.APPLICATION_ID = XET.APPLICATION_ID
        AND XEC.ENTITY_CODE = XET.ENTITY_CODE
        AND XEC.EVENT_CLASS_CODE = XET.EVENT_CLASS_CODE
        AND XEC.LANGUAGE = userenv('LANG')
        AND XAH.APPLICATION_ID = XEC.APPLICATION_ID
        AND XAH.APPLICATION_ID = XE.APPLICATION_ID
        AND XAH.EVENT_ID = XE.EVENT_ID
        AND XAH.ENTITY_ID = XE.ENTITY_ID
        AND UPPER(FLV.MEANING) = 'FINAL'
        -- Parameters
        AND (XST.APPLICATION_NAME IN (:P_SUBLEDGER_APPLICATION) OR 'All' IN ('All' || :P_SUBLEDGER_APPLICATION))
        AND XAH.ACCOUNTING_DATE BETWEEN :P_FROM_ACCOUNTING_DATE AND :P_TO_ACCOUNTING_DATE
        AND (XAH.PERIOD_NAME IN (:P_ACCOUNTING_PERIOD) OR 'All' IN ('All' || :P_ACCOUNTING_PERIOD))
        AND (GLL.NAME IN (:P_Ledger) OR 'All' IN ('All' || :P_Ledger))
        AND (XTH.REQUEST_ID IN (:P_REQUEST_ID) OR 'All' IN ('All' || :P_REQUEST_ID))
        AND (XET.NAME IN (:P_EVENT_TYPE) OR 'All' IN ('All' || :P_EVENT_TYPE))
)

SELECT
     LEDGER_NAME
    ,ACCOUNTING_PERIOD
    ,ACCOUNTING_DATE
    ,EVENT_CLASS
    ,EVENT_TYPE
    ,EVENT_DATE
    ,JOURNAL_SOURCE 
    ,JOURNAL_CATEGORY
    ,JOURNAL_BATCH_NAME
    ,TRANSACTION_NUMBER
    ,LINE_NUMBER
    ,ENTERED_CURRENCY
    ,ENTERED_DR
    ,ENTERED_CR
    ,LEDGER_CURRENCY
    ,ACCOUNTED_DR
    ,ACCOUNTED_CR
    ,ACCOUNT_CODE_COMBINATION
    ,ACCOUNTING_CLASS
    ,ACCOUNTING_ENTRY_STATUS
    ,POSTING_STATUS
    ,JOURNAL_CREATION_DATE
    ,HEADER_DESCRIPTION
    ,LINE_DESCRIPTION
FROM DATA
WHERE RN = 1
ORDER BY 
     LEDGER_NAME
    ,ACCOUNTING_PERIOD
    ,TRANSACTION_NUMBER
    ,LINE_NUMBER
    ,EVENT_TYPE
    ,ACCOUNT_CODE_COMBINATION