GL - Journal Entry Line Details

SELECT   
      JournalEntryHeaderEO.LEDGER_ID,  
      GlLedgers.NAME AS LEDGER_NAME,  
      JournalSourcePEO.USER_JE_SOURCE_NAME,  
      SubledgerApplicationEO.JE_SOURCE_NAME,  
      JournalEntryLineEO.AE_HEADER_ID,  
      JournalEntryLineEO.AE_LINE_NUM,  
      JournalEntryLineEO.CODE_COMBINATION_ID,  
      JournalEntryHeaderEO.ACCOUNTING_DATE,  
      JournalEntryLineEO.APPLICATION_ID,  
      SubledgerApplicationEO.APPLICATION_ID AS APPLICATION_ID1,  
      JournalEntryLineEO.ACCOUNTED_DR,  
      JournalEntryLineEO.ACCOUNTED_CR,  
      JournalEntryLineEO.ACCOUNTING_CLASS_CODE,  
      JournalEntryHeaderEO.COMPLETION_ACCT_SEQ_VERSION_ID,  
      JournalEntryHeaderEO.AE_HEADER_ID AS AE_HEADER_ID1,  
      JournalEntryHeaderEO.COMPLETION_ACCT_SEQ_VALUE,  
      JournalEntryHeaderEO.COMPLETION_ACCT_SEQ_ASSIGN_ID,  
      JournalEntryHeaderEO.BALANCE_TYPE_CODE,  
      JournalEntryHeaderEO.COMPLETED_DATE,  
      JournalEntryLineEO.CURRENCY_CONVERSION_DATE,  
      JournalEntryLineEO.CURRENCY_CONVERSION_TYPE,  
      JournalEntryLineEO.CURRENCY_CONVERSION_RATE,  
      JournalEntryLineEO.CREATED_BY,  
      JournalEntryLineEO.CREATION_DATE,  
      JournalEntryHeaderEO.DOC_SEQUENCE_ID,  
      JournalEntryHeaderEO.DOC_SEQUENCE_VALUE,  
      JournalEntryLineEO.ENTERED_DR,  
      JournalEntryLineEO.ENTERED_CR,  
      JournalEntryLineEO.CURRENCY_CODE,  
      JournalEntryHeaderEO.EVENT_TYPE_CODE,  
      JournalEntryHeaderEO.PERIOD_NAME,  
      JournalEntryHeaderEO.JE_CATEGORY_NAME,  
      JournalEntryHeaderEO.ACCOUNTING_ENTRY_TYPE_CODE,  
      JournalEntryHeaderEO.ACCOUNTING_ENTRY_STATUS_CODE,  
      DECODE(JournalEntryHeaderEO.ACCOUNTING_ENTRY_STATUS_CODE,'I','I','RELATED_EVENT_ERROR','I','R','I', JournalEntryHeaderEO.ACCOUNTING_ENTRY_STATUS_CODE) AS STATUS_FOR_SEARCH,  
      XleEntityProfiles.LEGAL_ENTITY_ID,  
      XleEntityProfiles.LEGAL_ENTITY_IDENTIFIER,  
      XleEntityProfiles.NAME AS LEGAL_ENTITY_NAME,  
      JournalEntryLineEO.PARTY_TYPE_CODE,  
      JournalEntryHeaderEO.REFERENCE_DATE,  
      JournalEntryHeaderEO.CLOSE_ACCT_SEQ_ASSIGN_ID,  
      JournalEntryHeaderEO.CLOSE_ACCT_SEQ_VALUE,  
      JournalEntryHeaderEO.CLOSE_ACCT_SEQ_VERSION_ID,  
      TransactionEO.TRANSACTION_NUMBER,  
      TransactionEO.ENTITY_ID,  
      JournalEntryHeaderEO.GL_TRANSFER_DATE,  
      JournalEntryHeaderEO.GL_TRANSFER_STATUS_CODE,  
      JournalEntryLineEO.DESCRIPTION,  
      GlLedgers.LEDGER_ID AS LEDGER_ID1,  
      GlLedgers.CHART_OF_ACCOUNTS_ID,  
      GlLedgers.SLA_ACCOUNTING_METHOD_CODE,  
      GlLedgers.SLA_ACCOUNTING_METHOD_TYPE,  
      JournalEntryLineEO.JGZZ_RECON_REF,  
      TransactionEO.APPLICATION_ID AS TRANSACTION_APPLICATION_ID,  
      TransactionEO.ENTITY_CODE,  
      TransactionEO.LEDGER_ID AS TRANSACTION_LEDGER_ID,  
      JournalEntryLineEO.GL_SL_LINK_ID,  
      JournalEntryLineEO.GL_SL_LINK_TABLE,  
      GlLedgers.CURRENCY_CODE AS LEDGER_CURRENCY_CODE,  
      FND_FLEX_EXT.GET_SEGS('GL', 'GL#', GlLedgers.CHART_OF_ACCOUNTS_ID, JournalEntryLineEO.CODE_COMBINATION_ID) AS ACCOUNT_CONCATENATED_STR,  
      TransactionEO.SOURCE_ID_INT_1,  
      TransactionEO.SOURCE_ID_CHAR_1,  
      TransactionEO.SOURCE_ID_INT_2,  
      TransactionEO.SOURCE_ID_CHAR_2,  
      TransactionEO.SOURCE_ID_INT_3,  
      TransactionEO.SOURCE_ID_CHAR_3,  
      TransactionEO.SOURCE_ID_INT_4,  
      TransactionEO.SOURCE_ID_CHAR_4,  
      TransactionEO.SECURITY_ID_INT_1,  
      TransactionEO.SECURITY_ID_INT_2,  
      TransactionEO.SECURITY_ID_INT_3,  
      TransactionEO.SECURITY_ID_CHAR_1,  
      TransactionEO.SECURITY_ID_CHAR_2,  
      TransactionEO.SECURITY_ID_CHAR_3,  
      TransactionEO.VALUATION_METHOD,  
      GL_FLEXFIELDS_PKG.get_concat_description(GlLedgers.CHART_OF_ACCOUNTS_ID, JournalEntryLineEO.CODE_COMBINATION_ID) AS ACCOUNT_DESCRIPTION,  
      CurrencyPEO.PRECISION AS ENTERED_CURRENCY_PRECISION,  
      AccountingClassLookup.MEANING AS ACCOUNTING_CLASS_NAME,  
      EventClassEO.NAME AS NAME1,  
      EventClassEO.APPLICATION_ID AS APPLICATION_ID2,  
      EventClassEO.ENTITY_CODE AS ENTITY_CODE1,  
      EventClassEO.EVENT_CLASS_CODE,  
      EventTypeEO.NAME AS NAME2,  
      EventTypeEO.APPLICATION_ID AS APPLICATION_ID3,  
      EventTypeEO.ENTITY_CODE AS ENTITY_CODE2,  
      EventTypeEO.EVENT_CLASS_CODE AS EVENT_CLASS_CODE1,  
      EventTypeEO.EVENT_TYPE_CODE AS EVENT_TYPE_CODE1,  
      JournalCategoryPEO.USER_JE_CATEGORY_NAME,  
      JournalCategoryPEO.JE_CATEGORY_NAME AS JE_CATEGORY_NAME1,  
      CurrencyPEO.CURRENCY_CODE AS CURRENCY_CODE1,  
      AccountingClassLookup.LOOKUP_CODE,  
      AccountingClassLookup.LOOKUP_TYPE,  
      GlDailyConversionTypes.USER_CONVERSION_TYPE,  
      GlDailyConversionTypes.CONVERSION_TYPE,  
      JournalSourcePEO.JE_SOURCE_NAME AS JE_SOURCE_NAME1,  
      NULL AS SUPP_REF_NAME,  
      JournalEntryLineEO.OVERRIDDEN_CODE_COMBINATION_ID,  
      JournalEntryLineEO.OVERRIDE_REASON,  
      JournalEntryLineEO.OBJECT_VERSION_NUMBER,  
      GlLedgers.CHART_OF_ACCOUNTS_ID AS CHART_OF_ACCOUNTS_ID1,  
      JournalEntryHeaderEO.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER1,  
      JournalEntryLineEO.ENCUMBRANCE_TYPE_ID,  
      XleEntityProfiles.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER2,  
      JournalEncumbranceTypePEO.ENCUMBRANCE_TYPE,  
      GlLedgers.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER3,  
      JournalEncumbranceTypePEO.ENCUMBRANCE_TYPE_ID AS ENCUMBRANCE_TYPE_ID1,  
      JournalEntryLineEO.ANCHOR_LINE_FLAG,  
      EventClassEO.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER4,  
      SubledgerApplicationEO.IMPLEMENTATION_MODE_CODE,  
      EventTypeEO.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER5,  
      JournalEntryLineEO.LAST_UPDATED_BY,  
      JournalEntryLineEO.LAST_UPDATE_DATE,  
      JournalEntryHeaderEO.DESCRIPTION AS DESCRIPTION1,  
      GlDailyConversionTypes.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER6,  
      SubledgerApplicationEO.APPLICATION_SHORT_NAME,  
      GlLedgers.LEDGER_CATEGORY_CODE,  
      JournalEntryLineEO.ATTRIBUTE_CATEGORY AS ATTRIBUTECATEGORY,  
      JournalEntryLineEO.ATTRIBUTE1,  
      JournalEntryLineEO.ATTRIBUTE2,  
      JournalEntryLineEO.ATTRIBUTE3,  
      JournalEntryLineEO.ATTRIBUTE4,  
      JournalEntryLineEO.ATTRIBUTE5,  
      JournalEntryLineEO.ATTRIBUTE6,  
      JournalEntryLineEO.ATTRIBUTE7,  
      JournalEntryLineEO.ATTRIBUTE8,  
      JournalEntryLineEO.ATTRIBUTE9,  
      JournalEntryLineEO.ATTRIBUTE10,  
      JournalEntryLineEO.ATTRIBUTE11,  
      JournalEntryLineEO.ATTRIBUTE12,  
      JournalEntryLineEO.ATTRIBUTE13,  
      JournalEntryLineEO.ATTRIBUTE14,  
      JournalEntryLineEO.ATTRIBUTE15,  
      JournalEntryLineEO.ATTRIBUTE_DATE1,  
      JournalEntryLineEO.ATTRIBUTE_DATE2,  
      JournalEntryLineEO.ATTRIBUTE_DATE3,  
      JournalEntryLineEO.ATTRIBUTE_DATE4,  
      JournalEntryLineEO.ATTRIBUTE_DATE5,  
      JournalEntryLineEO.ATTRIBUTE_DATE6,  
      JournalEntryLineEO.ATTRIBUTE_DATE7,  
      JournalEntryLineEO.ATTRIBUTE_DATE8,  
      JournalEntryLineEO.ATTRIBUTE_DATE9,  
      JournalEntryLineEO.ATTRIBUTE_DATE10,  
      JournalEntryLineEO.ATTRIBUTE_NUMBER1,  
      JournalEntryLineEO.ATTRIBUTE_NUMBER2,  
      JournalEntryLineEO.ATTRIBUTE_NUMBER3,  
      JournalEntryLineEO.ATTRIBUTE_NUMBER4,  
      JournalEntryLineEO.ATTRIBUTE_NUMBER5,  
      JournalEntryLineEO.ATTRIBUTE_NUMBER6,  
      JournalEntryLineEO.ATTRIBUTE_NUMBER7,  
      JournalEntryLineEO.ATTRIBUTE_NUMBER8,  
      JournalEntryLineEO.ATTRIBUTE_NUMBER9,  
      JournalEntryLineEO.ATTRIBUTE_NUMBER10  
  FROM  
      fusion.XLA_AE_LINES JournalEntryLineEO,  
      fusion.XLA_AE_HEADERS JournalEntryHeaderEO,  
      fusion.XLA_SUBLEDGERS_VL SubledgerApplicationEO,  
      fusion.XLA_TRANSACTION_ENTITIES TransactionEO,  
      fusion.XLE_ENTITY_PROFILES XleEntityProfiles,  
      fusion.XLA_GL_LEDGERS GlLedgers,  
      fusion.XLA_EVENT_CLASSES_VL EventClassEO,  
      fusion.XLA_EVENT_TYPES_VL EventTypeEO,  
      fusion.GL_JE_CATEGORIES_VL JournalCategoryPEO,  
      fusion.FND_CURRENCIES_VL CurrencyPEO,  
      fusion.XLA_LOOKUPS AccountingClassLookup,  
      fusion.GL_DAILY_CONVERSION_TYPES GlDailyConversionTypes,  
      fusion.GL_JE_SOURCES_VL JournalSourcePEO,  
      fusion.GL_ENCUMBRANCE_TYPES_VL JournalEncumbranceTypePEO  
  WHERE  
      JournalEntryHeaderEO.APPLICATION_ID = JournalEntryLineEO.APPLICATION_ID  
      AND JournalEntryHeaderEO.AE_HEADER_ID = JournalEntryLineEO.AE_HEADER_ID  
      AND TransactionEO.APPLICATION_ID = JournalEntryHeaderEO.APPLICATION_ID  
      AND TransactionEO.ENTITY_ID = JournalEntryHeaderEO.ENTITY_ID  
      AND EventTypeEO.APPLICATION_ID = JournalEntryHeaderEO.APPLICATION_ID  
      AND EventTypeEO.ENTITY_CODE = TransactionEO.ENTITY_CODE  
      AND EventTypeEO.EVENT_TYPE_CODE = JournalEntryHeaderEO.EVENT_TYPE_CODE  
      AND EventClassEO.APPLICATION_ID = EventTypeEO.APPLICATION_ID  
      AND EventClassEO.ENTITY_CODE = EventTypeEO.ENTITY_CODE  
      AND EventClassEO.EVENT_CLASS_CODE = EventTypeEO.EVENT_CLASS_CODE  
      AND SubledgerApplicationEO.APPLICATION_ID = JournalEntryHeaderEO.APPLICATION_ID  
      AND XleEntityProfiles.LEGAL_ENTITY_ID(+) = JournalEntryHeaderEO.LEGAL_ENTITY_ID  
      AND GlLedgers.LEDGER_ID = JournalEntryHeaderEO.LEDGER_ID  
      AND (GlLedgers.LEDGER_CATEGORY_CODE <> 'PRIMARY' OR  
           (GlLedgers.LEDGER_CATEGORY_CODE = 'PRIMARY' AND TransactionEO.LEDGER_ID = JournalEntryHeaderEO.LEDGER_ID))  
      AND JournalCategoryPEO.JE_CATEGORY_NAME = JournalEntryHeaderEO.JE_CATEGORY_NAME  
      AND CurrencyPEO.CURRENCY_CODE = JournalEntryLineEO.CURRENCY_CODE  
      AND AccountingClassLookup.LOOKUP_TYPE = 'XLA_ACCOUNTING_CLASS'  
      AND AccountingClassLookup.LOOKUP_CODE = JournalEntryLineEO.ACCOUNTING_CLASS_CODE  
      AND GlDailyConversionTypes.CONVERSION_TYPE(+) = JournalEntryLineEO.CURRENCY_CONVERSION_TYPE  
      AND SubledgerApplicationEO.JE_SOURCE_NAME = JournalSourcePEO.JE_SOURCE_NAME  
      AND JournalEntryHeaderEO.BALANCE_TYPE_CODE IN ('A', 'E')  
      AND (NVL(JournalEntryLineEO.ACCOUNTED_CR,0) <> 0 OR NVL(JournalEntryLineEO.ACCOUNTED_DR,0) <> 0 OR FND_PROFILE.value('XLA_SHOW_ZERO_AMT_JRNL') = 'Y')  
      AND JournalEntryLineEO.ENCUMBRANCE_TYPE_ID = JournalEncumbranceTypePEO.ENCUMBRANCE_TYPE_ID(+)  
      AND NOT ((ABS(NVL(JournalEntryLineEO.accounted_cr,0)-NVL(JournalEntryLineEO.accounted_dr,0)) = 0  
               AND ABS(NVL(JournalEntryLineEO.entered_cr,0)-NVL(JournalEntryLineEO.entered_dr,0)) = 0  
               AND NVL(JournalEntryLineEO.gain_or_loss_flag,'N')='Y'))  
      AND JournalEntryHeaderEO.LEDGER_ID = GlLedgers.ledger_id  
      AND GlLedgers.NAME = :LEDGER_NAME  
      AND JournalSourcePEO.USER_JE_SOURCE_NAME = :JOURNAL_SOURCE_NAME  
      AND JournalEntryHeaderEO.ACCOUNTING_DATE BETWEEN :FROM_DATE AND :TO_DATE  
      AND JournalEntryLineEO.APPLICATION_ID = SubledgerApplicationEO.APPLICATION_ID  
      AND UPPER(DECODE(JournalEntryHeaderEO.ACCOUNTING_ENTRY_STATUS_CODE,'I','I','RELATED_EVENT_ERROR','I','R','I',JournalEntryHeaderEO.ACCOUNTING_ENTRY_STATUS_CODE)) = UPPER(:JOURNAL_STATUS_CODE);