JG Global Third Party Detail and Balances

Description
Categories: BI Publisher
Application: Regional Localizations
Source: Global Third Party Detail and Balances Report
Short Name: JGSLATPDBL
DB package: XLA_TP_BALANCE_RPT_PKG
        SELECT TABLE1.GL_DATE                GL_DATE
       ,TABLE1.CREATED_BY                    CREATED_BY
       ,TABLE1.CREATION_DATE                 CREATION_DATE
       ,TABLE1.LAST_UPDATE_DATE              LAST_UPDATE_DATE
       ,TABLE1.GL_TRANSFER_DATE              GL_TRANSFER_DATE
       ,TABLE1.REFERENCE_DATE                REFERENCE_DATE
       ,TABLE1.COMPLETED_DATE                COMPLETED_DATE
       ,TABLE1.TRANSACTION_NUMBER            TRANSACTION_NUMBER
       ,TABLE1.OPERATING_UNIT                OPERATING_UNIT --Bug 9113330
       ,TABLE1.TRANSACTION_DATE              TRANSACTION_DATE
       ,TABLE1.ACCOUNTING_SEQUENCE_NAME      ACCOUNTING_SEQUENCE_NAME
       ,TABLE1.ACCOUNTING_SEQUENCE_VERSION   ACCOUNTING_SEQUENCE_VERSION
       ,TABLE1.ACCOUNTING_SEQUENCE_NUMBER    ACCOUNTING_SEQUENCE_NUMBER
       ,TABLE1.REPORTING_SEQUENCE_NAME       REPORTING_SEQUENCE_NAME
       ,TABLE1.REPORTING_SEQUENCE_VERSION    REPORTING_SEQUENCE_VERSION
       ,TABLE1.REPORTING_SEQUENCE_NUMBER     REPORTING_SEQUENCE_NUMBER
       ,TABLE1.DOCUMENT_SEQUENCE_NAME        DOCUMENT_SEQUENCE_NAME
       ,TABLE1.DOCUMENT_SEQUENCE_NUMBER      DOCUMENT_SEQUENCE_NUMBER
       ,TABLE1.HEADER_ID                     HEADER_ID
       ,TABLE1.HEADER_DESCRIPTION            HEADER_DESCRIPTION
       ,TABLE1.FUND_STATUS                   FUND_STATUS
       ,TABLE1.JE_CATEGORY_NAME              JE_CATEGORY_NAME
       ,TABLE1.EVENT_ID                      EVENT_ID
       ,TABLE1.EVENT_DATE                    EVENT_DATE
       ,TABLE1.EVENT_NUMBER                  EVENT_NUMBER
       ,TABLE1.EVENT_CLASS_CODE              EVENT_CLASS_CODE
       ,TABLE1.EVENT_CLASS_NAME              EVENT_CLASS_NAME
       ,TABLE1.EVENT_TYPE_CODE               EVENT_TYPE_CODE
       ,TABLE1.EVENT_TYPE_NAME               EVENT_TYPE_NAME
       ,TABLE1.JOURNAL_ENTRY_STATUS          JOURNAL_ENTRY_STATUS
       ,TABLE1.TRANSFER_TO_GL_STATUS         TRANSFER_TO_GL_STATUS
       ,TABLE1.GL_BATCH_STATUS               GL_BATCH_STATUS
       ,TABLE1.POSTED_DATE                   POSTED_DATE
       ,TABLE1.LINE_NUMBER                   LINE_NUMBER
       ,TABLE1.ACCOUNTING_CLASS_CODE         ACCOUNTING_CLASS_CODE
       ,TABLE1.ACCOUNTING_CLASS_NAME         ACCOUNTING_CLASS_NAME
       ,TABLE1.LINE_DESCRIPTION              LINE_DESCRIPTION
       ,TABLE1.ENTERED_CURRENCY              ENTERED_CURRENCY
       ,TABLE1.CONVERSION_RATE               CONVERSION_RATE
       ,TABLE1.CONVERSION_RATE_DATE          CONVERSION_RATE_DATE
       ,TABLE1.CONVERSION_RATE_TYPE_CODE     CONVERSION_RATE_TYPE_CODE
       ,TABLE1.CONVERSION_RATE_TYPE          CONVERSION_RATE_TYPE
       ,TABLE1.ENTERED_DR                    ENTERED_DR
       ,TABLE1.ENTERED_CR                    ENTERED_CR
       ,TABLE1.UNROUNDED_ACCOUNTED_DR        UNROUNDED_ACCOUNTED_DR
       ,TABLE1.UNROUNDED_ACCOUNTED_CR        UNROUNDED_ACCOUNTED_CR
       ,TABLE1.ACCOUNTED_DR                  ACCOUNTED_DR
       ,TABLE1.ACCOUNTED_CR                  ACCOUNTED_CR
       ,TABLE1.STATISTICAL_AMOUNT            STATISTICAL_AMOUNT
       ,TABLE1.RECONCILIATION_REFERENCE      RECONCILIATION_REFERENCE
       ,TABLE1.ATTRIBUTE_CATEGORY            ATTRIBUTE_CATEGORY
       ,TABLE1.ATTRIBUTE1                    ATTRIBUTE1
       ,TABLE1.ATTRIBUTE2                    ATTRIBUTE2
       ,TABLE1.ATTRIBUTE3                    ATTRIBUTE3
       ,TABLE1.ATTRIBUTE4                    ATTRIBUTE4
       ,TABLE1.ATTRIBUTE5                    ATTRIBUTE5
       ,TABLE1.ATTRIBUTE6                    ATTRIBUTE6
       ,TABLE1.ATTRIBUTE7                    ATTRIBUTE7
       ,TABLE1.ATTRIBUTE8                    ATTRIBUTE8
       ,TABLE1.ATTRIBUTE9                    ATTRIBUTE9
       ,TABLE1.ATTRIBUTE10                   ATTRIBUTE10
       ,TABLE1.ATTRIBUTE11                   ATTRIBUTE11
       ,TABLE1.ATTRIBUTE12                   ATTRIBUTE12
       ,TABLE1.ATTRIBUTE13                   ATTRIBUTE13
       ,TABLE1.ATTRIBUTE14                   ATTRIBUTE14
       ,TABLE1.ATTRIBUTE15                   ATTRIBUTE15
       ,TABLE1.AE_LINE_NUM                   AE_LINE_NUM
       ,substr(USERIDS,1,instr(USERIDS,'|',1,1)-1) USER_TRX_IDENTIFIER_NAME_1
       ,substr(USERIDS,instr(USERIDS,'|',1,1)+1,(instr(USERIDS,'|',1,2)-1-instr(USERIDS,'|',1,1))) USER_TRX_IDENTIFIER_VALUE_1
       ,substr(USERIDS,instr(USERIDS,'|',1,2)+1,(instr(USERIDS,'|',1,3)-1-instr(USERIDS,'|',1,2))) USER_TRX_IDENTIFIER_NAME_2
       ,substr(USERIDS,instr(USERIDS,'|',1,3)+1,(instr(USERIDS,'|',1,4)-1-instr(USERIDS,'|',1,3))) USER_TRX_IDENTIFIER_VALUE_2
       ,substr(USERIDS,instr(USERIDS,'|',1,4)+1,(instr(USERIDS,'|',1,5)-1-instr(USERIDS,'|',1,4))) USER_TRX_IDENTIFIER_NAME_3
       ,substr(USERIDS,instr(USERIDS,'|',1,5)+1,(instr(USERIDS,'|',1,6)-1-instr(USERIDS,'|',1,5))) USER_TRX_IDENTIFIER_VALUE_3
       ,substr(USERIDS,instr(USERIDS,'|',1,6)+1,(instr(USERIDS,'|',1,7)-1-instr(USERIDS,'|',1,6))) USER_TRX_IDENTIFIER_NAME_4
       ,substr(USERIDS,instr(USERIDS,'|',1,7)+1,(instr(USERIDS,'|',1,8)-1-instr(USERIDS,'|',1,7))) USER_TRX_IDENTIFIER_VALUE_4
       ,substr(USERIDS,instr(USERIDS,'|',1,8)+1,(instr(USERIDS,'|',1,9)-1-instr(USERIDS,'|',1,8))) USER_TRX_IDENTIFIER_NAME_5
       ,substr(USERIDS,instr(USERIDS,'|',1,9)+1,(instr(USERIDS,'|',1,10)-1-instr(USERIDS,'|',1,9))) USER_TRX_IDENTIFIER_VALUE_5
       ,substr(USERIDS,instr(USERIDS,'|',1,10)+1,(instr(USERIDS,'|',1,11)-1-instr(USERIDS,'|',1,10))) USER_TRX_IDENTIFIER_NAME_6
       ,substr(USERIDS,instr(USERIDS,'|',1,11)+1,(instr(USERIDS,'|',1,12)-1-instr(USERIDS,'|',1,11))) USER_TRX_IDENTIFIER_VALUE_6
       ,substr(USERIDS,instr(USERIDS,'|',1,12)+1,(instr(USERIDS,'|',1,13)-1-instr(USERIDS,'|',1,12))) USER_TRX_IDENTIFIER_NAME_7
       ,substr(USERIDS,instr(USERIDS,'|',1,13)+1,(instr(USERIDS,'|',1,14)-1-instr(USERIDS,'|',1,13))) USER_TRX_IDENTIFIER_VALUE_7
       ,substr(USERIDS,instr(USERIDS,'|',1,14)+1,(instr(USERIDS,'|',1,15)-1-instr(USERIDS,'|',1,14))) USER_TRX_IDENTIFIER_NAME_8
       ,substr(USERIDS,instr(USERIDS,'|',1,15)+1,(instr(USERIDS,'|',1,16)-1-instr(USERIDS,'|',1,15))) USER_TRX_IDENTIFIER_VALUE_8
       ,substr(USERIDS,instr(USERIDS,'|',1,16)+1,(instr(USERIDS,'|',1,17)-1-instr(USERIDS,'|',1,16))) USER_TRX_IDENTIFIER_NAME_9
       ,substr(USERIDS,instr(USERIDS,'|',1,17)+1,(instr(USERIDS,'|',1,18)-1-instr(USERIDS,'|',1,17))) USER_TRX_IDENTIFIER_VALUE_9
       ,substr(USERIDS,instr(USERIDS,'|',1,18)+1,(instr(USERIDS,'|',1,19)-1-instr(USERIDS,'|',1,18))) USER_TRX_IDENTIFIER_NAME_10
       ,substr(USERIDS,instr(USERIDS,'|',1,19)+1,(length(USERIDS)-instr(USERIDS,'|',1,19))) USER_TRX_IDENTIFIER_VALUE_10       
FROM
(SELECT  /*+ leading (ael, aeh) use_nl(ael, aeh) index(ael XLA_AE_LINES_N5)  NO_EXPAND */
                  TO_CHAR(aeh.accounting_date
                         ,'YYYY-MM-DD')             GL_DATE
                 ,fdu.user_name                     CREATED_BY
                 ,TO_CHAR(aeh.creation_date
                         ,'YYYY-MM-DD"T"hh:mi:ss')  CREATION_DATE
                 ,TO_CHAR(aeh.last_update_date
                         ,'YYYY-MM-DD')             LAST_UPDATE_DATE
                 ,TO_CHAR(aeh.gl_transf