6 Replies Latest reply on Jun 15, 2016 7:47 AM by Jagadekara

    Error on custom report : ORA-00923: FROM keyword not found where expected

    arunpr

      Hi Legends,

       

      We have created a custom report which was working fine. But suddenly we are facing an error as below. We tried to rectify the error but still it shows as below. Kindly do the needful.

       

      ERROR :-

      +---------------------------------------------------------------------------+

      Subledger Accounting: Version : 12.2

      Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

      XXAP_LAAARPT: Detailed Account Analysis Report

      +---------------------------------------------------------------------------+

      Current system time is 14-JUN-2016 09:34:40

      +---------------------------------------------------------------------------+

      XDO Data Engine Version No: 10.1.3.4.2

      Resp: 50663

      Org ID : 101

      Request ID: 19282799

      All Parameters: P_RESP_APPLICATION_ID=101:P_LEDGER_ID=:P_LEDGER=LEDGER:P_COA_ID=101:P_LEGAL_ENTITY_ID=:P_LEGAL_ENTITY=:P_PERIOD_FROM=APR-16:P_PERIOD_TO=APR-16:P_GL_DATE_FROM="2016/04/01 00:00:00":P_GL_DATE_TO="2016/04/30 00:00:00":P_BALANCE_TYPE_CODE=A:P_BALANCE_TYPE=Actual:P_DUMMY_BUDGET_VERSION=:P_BUDGET_VERSION_ID=:P_BUDGET_NAME=:P_DUMMY_ENCUMBRANCE_TYPE=:P_ENCUMBRANCE_TYPE_ID=:P_ENCUMBRANCE_TYPE=:P_BALANCE_SIDE_CODE=:P_BALANCE_SIDE=:P_BALANCE_AMOUNT_FROM=:P_BALANCE_AMOUNT_TO=:P_BALANCING_SEGMENT_FROM=:P_BALANCING_SEGMENT_TO=:P_ACCOUNT_SEGMENT_FROM=:P_ACCOUNT_SEGMENT_TO=:P_ACCOUNT_FLEXFIELD_FROM=14.00.00020.0000.0000.0000.00:P_ACCOUNT_FLEXFIELD_TO=14.00.00020.0000.0000.0000.00:P_INCLUDE_ZERO_AMOUNT_LINES=N:P_INCLUDE_ZERO_AMT_LINES=No:P_INCLUDE_USER_TRX_ID_FLAG=N:P_INCLUDE_USER_TRX_ID=No:P_INCLUDE_TAX_DETAILS_FLAG=N:P_INCLUDE_TAX_DETAILS=No:P_INCLUDE_LE_INFO_FLAG=NONE:P_INCLUDE_LEGAL_ENTITY=None:P_CUSTOM_PARAMETER_1=:P_CUSTOM_PARAMETER_2=:P_CUSTOM_PARAMETER_3=:P_CUSTOM_PARAMETER_4=:P_CUSTOM_PARAMETER_5=:DebugFlag=:P_INCLUDE_STAT_AMOUNT_LINES=N:P_INCLUDE_STAT_AMT_LINES=No:P_INCLUDE_ACCT_WITH_NO_ACT=N:P_INC_ACCT_WITH_NO_ACT=No

      Data Template Code: XXAP_LAAARPT

      Data Template Application Short Name: XLA

      Debug Flag:

      {P_ACCOUNT_SEGMENT_FROM=, P_ENCUMBRANCE_TYPE=, P_ACCOUNT_SEGMENT_TO=, P_BALANCE_AMOUNT_FROM=, P_DUMMY_ENCUMBRANCE_TYPE=, P_DUMMY_BUDGET_VERSION=, P_GL_DATE_TO=2016/04/30 00:00:00, P_COA_ID=101, P_BALANCE_TYPE_CODE=A, P_BALANCE_AMOUNT_TO=, P_RESP_APPLICATION_ID=101, P_ENCUMBRANCE_TYPE_ID=, P_BALANCE_SIDE=, P_CUSTOM_PARAMETER_5=, P_CUSTOM_PARAMETER_4=, P_CUSTOM_PARAMETER_3=, P_CUSTOM_PARAMETER_2=, P_CUSTOM_PARAMETER_1=, P_BALANCE_TYPE=Actual, P_INC_ACCT_WITH_NO_ACT=No, P_INCLUDE_USER_TRX_ID=No, P_ACCOUNT_FLEXFIELD_FROM=14.00.00020.0000.0000.0000.00, P_BUDGET_NAME=, P_INCLUDE_STAT_AMT_LINES=No, P_INCLUDE_LE_INFO_FLAG=NONE, P_PERIOD_TO=APR-16, P_LEGAL_ENTITY_ID=, P_BALANCE_SIDE_CODE=, P_INCLUDE_ACCT_WITH_NO_ACT=N, P_INCLUDE_STAT_AMOUNT_LINES=N, P_BUDGET_VERSION_ID=, P_INCLUDE_LEGAL_ENTITY=None, P_INCLUDE_TAX_DETAILS_FLAG=N, P_BALANCING_SEGMENT_FROM=, P_INCLUDE_ZERO_AMT_LINES=No, P_LEDGER_ID=2021, P_PERIOD_FROM=APR-16, P_INCLUDE_USER_TRX_ID_FLAG=N, P_BALANCING_SEGMENT_TO=, P_LEGAL_ENTITY=, P_INCLUDE_TAX_DETAILS=No, P_LEDGER=LEDGER, P_GL_DATE_FROM=2016/04/01 00:00:00, P_INCLUDE_ZERO_AMOUNT_LINES=N, P_ACCOUNT_FLEXFIELD_TO=14.00.00020.0000.0000.0000.00}

      Calling XDO Data Engine...

      --SQLException

      java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

       

       

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)

        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)

        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)

        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)

        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)

        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:947)

        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1283)

        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1441)

        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)

        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823)

        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671)

        at oracle.apps.xdo.dataengine.XMLPGEN.processQuery(Unknown Source)

        at oracle.apps.xdo.dataengine.XMLPGEN.processSQLDataSource(Unknown Source)

        at oracle.apps.xdo.dataengine.XMLPGEN.writeData(Unknown Source)

        at oracle.apps.xdo.dataengine.XMLPGEN.writeGroupStructure(Unknown Source)

        at oracle.apps.xdo.dataengine.XMLPGEN.processData(Unknown Source)

        at oracle.apps.xdo.dataengine.XMLPGEN.processXML(Unknown Source)

        at oracle.apps.xdo.dataengine.XMLPGEN.writeXML(Unknown Source)

        at oracle.apps.xdo.dataengine.DataProcessor.processDataStructre(Unknown Source)

        at oracle.apps.xdo.dataengine.DataProcessor.processData(Unknown Source)

        at oracle.apps.xdo.oa.util.DataTemplate.processData(DataTemplate.java:334)

        at oracle.apps.xdo.oa.cp.JCP4XDODataEngine.runProgram(JCP4XDODataEngine.java:294)

        at oracle.apps.fnd.cp.request.Run.main(Run.java:156)

      +---------------------------------------------------------------------------+

      Start of log messages from FND_FILE

      +---------------------------------------------------------------------------+

      +---------------------------------------------------------------------------+

      End of log messages from FND_FILE

      +---------------------------------------------------------------------------+

      +---------------------------------------------------------------------------+

      Executing request completion options...

      Output file size:

      2685

      Finished executing request completion options.

      +---------------------------------------------------------------------------+

      Concurrent request completed

      Current system time is 14-JUN-2016 09:34:45

      +---------------------------------------------------------------------------+

       

       

      QUERY :-

      ==============================================

      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.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_CATEGORY                       DOCUMENT_CATEGORY

             ,TABLE1.DOCUMENT_SEQUENCE_NAME                  DOCUMENT_SEQUENCE_NAME

             ,TABLE1.DOCUMENT_SEQUENCE_NUMBER                DOCUMENT_SEQUENCE_NUMBER

             ,TABLE1.APPLICATION_ID                          APPLICATION_ID

             ,TABLE1.APPLICATION_NAME                        APPLICATION_NAME

             ,TABLE1.HEADER_ID                               HEADER_ID

             ,TABLE1.HEADER_DESCRIPTION                      HEADER_DESCRIPTION

             ,TABLE1.FUND_STATUS                             FUND_STATUS

             ,TABLE1.JE_CATEGORY_NAME                        JE_CATEGORY_NAME

             ,TABLE1.JE_SOURCE_NAME                          JE_SOURCE_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.GL_BATCH_NAME                           GL_BATCH_NAME

             ,TABLE1.POSTED_DATE                             POSTED_DATE

             ,TABLE1.GL_JE_NAME                              GL_JE_NAME

             ,TABLE1.GL_LINE_NUMBER                          GL_LINE_NUMBER

             ,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.PARTY_TYPE_CODE                         PARTY_TYPE_CODE

             ,TABLE1.PARTY_TYPE                              PARTY_TYPE

             ,substr(PARTY_INFO,1,instr(PARTY_INFO,'|',1,1)-1 )                                                       PARTY_NUMBER

             ,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,1)+1,(instr(PARTY_INFO,'|',1,2)-1-instr(PARTY_INFO,'|',1,1)))  PARTY_NAME

             ,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,2)+1,(instr(PARTY_INFO,'|',1,3)-1-instr(PARTY_INFO,'|',1,2)))  PARTY_TYPE_TAXPAYER_ID

             ,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,3)+1,(instr(PARTY_INFO,'|',1,4)-1-instr(PARTY_INFO,'|',1,3)))  PARTY_TAX_REGISTRATION_NUMBER

             ,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,4)+1,(instr(PARTY_INFO,'|',1,5)-1-instr(PARTY_INFO,'|',1,4)))  PARTY_SITE_NUMBER

             ,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,5)+1,(instr(PARTY_INFO,'|',1,6)-1-instr(PARTY_INFO,'|',1,5)))  PARTY_SITE_NAME

             ,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,6)+1,(length(PARTY_INFO)- instr(PARTY_INFO,'|',1,6)))          PARTY_SITE_TAX_RGSTN_NUMBER

            ,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

             ,TABLE1.LEDGER_ID                               LEDGER_ID

             ,TABLE1.LEDGER_SHORT_NAME                       LEDGER_SHORT_NAME

             ,TABLE1.LEDGER_DESCRIPTION                      LEDGER_DESCRIPTION

             ,TABLE1.LEDGER_NAME                             LEDGER_NAME

             ,TABLE1.LEDGER_CURRENCY                         LEDGER_CURRENCY

             ,TABLE1.PERIOD_YEAR                             PERIOD_YEAR

             ,TABLE1.PERIOD_NUMBER                           PERIOD_NUMBER

             ,TABLE1.PERIOD_NAME                             PERIOD_NAME

             ,TABLE1.PERIOD_START_DATE                       PERIOD_START_DATE

             ,TABLE1.PERIOD_END_DATE                         PERIOD_END_DATE

             ,TABLE1.BALANCE_TYPE_CODE                       BALANCE_TYPE_CODE

             ,TABLE1.BALANCE_TYPE                            BALANCE_TYPE

             ,TABLE1.BUDGET_NAME                             BUDGET_NAME

             ,TABLE1.ENCUMBRANCE_TYPE                        ENCUMBRANCE_TYPE

             ,TABLE1.BEGIN_BALANCE_DR                        BEGIN_BALANCE_DR

             ,TABLE1.BEGIN_BALANCE_CR                        BEGIN_BALANCE_CR

             ,TABLE1.PERIOD_NET_DR                           PERIOD_NET_DR

             ,TABLE1.PERIOD_NET_CR                           PERIOD_NET_CR

             ,TABLE1.CODE_COMBINATION_ID                     CODE_COMBINATION_ID

             ,TABLE1.ACCOUNTING_CODE_COMBINATION             ACCOUNTING_CODE_COMBINATION

             ,TABLE1.CODE_COMBINATION_DESCRIPTION            CODE_COMBINATION_DESCRIPTION

             ,TABLE1.CONTROL_ACCOUNT_FLAG                    CONTROL_ACCOUNT_FLAG

             ,TABLE1.CONTROL_ACCOUNT                         CONTROL_ACCOUNT

             ,TABLE1.BALANCING_SEGMENT                       BALANCING_SEGMENT

             ,TABLE1.NATURAL_ACCOUNT_SEGMENT                 NATURAL_ACCOUNT_SEGMENT

             ,TABLE1.COST_CENTER_SEGMENT                     COST_CENTER_SEGMENT

             ,TABLE1.MANAGEMENT_SEGMENT                      MANAGEMENT_SEGMENT

             ,TABLE1.INTERCOMPANY_SEGMENT                    INTERCOMPANY_SEGMENT

             ,TABLE1.BALANCING_SEGMENT_DESC                  BALANCING_SEGMENT_DESC

             ,TABLE1.NATURAL_ACCOUNT_DESC                    NATURAL_ACCOUNT_DESC

             ,TABLE1.COST_CENTER_DESC                        COST_CENTER_DESC

             ,TABLE1.MANAGEMENT_SEGMENT_DESC                 MANAGEMENT_SEGMENT_DESC

             ,TABLE1.INTERCOMPANY_SEGMENT_DESC               INTERCOMPANY_SEGMENT_DESC

             ,TABLE1.SEGMENT1                                SEGMENT1

             ,TABLE1.SEGMENT2                                SEGMENT2

             ,TABLE1.SEGMENT3                                SEGMENT3

             ,TABLE1.SEGMENT4                                SEGMENT4

             ,TABLE1.SEGMENT5                                SEGMENT5

             ,TABLE1.SEGMENT6                                SEGMENT6

             ,TABLE1.SEGMENT7                                SEGMENT7

             ,TABLE1.SEGMENT8                                SEGMENT8

             ,TABLE1.SEGMENT9                                SEGMENT9

             ,TABLE1.SEGMENT10                               SEGMENT10

             ,TABLE1.SEGMENT11                               SEGMENT11

             ,TABLE1.SEGMENT12                               SEGMENT12

             ,TABLE1.SEGMENT13                               SEGMENT13

             ,TABLE1.SEGMENT14                               SEGMENT14

             ,TABLE1.SEGMENT15                               SEGMENT15

             ,TABLE1.SEGMENT16                               SEGMENT16

             ,TABLE1.SEGMENT17                               SEGMENT17

             ,TABLE1.SEGMENT18                               SEGMENT18

             ,TABLE1.SEGMENT19                               SEGMENT19

             ,TABLE1.SEGMENT20                               SEGMENT20

             ,TABLE1.SEGMENT21                               SEGMENT21

             ,TABLE1.SEGMENT22                               SEGMENT22

             ,TABLE1.SEGMENT23                               SEGMENT23

             ,TABLE1.SEGMENT24                               SEGMENT24

             ,TABLE1.SEGMENT25                               SEGMENT25

             ,TABLE1.SEGMENT26                               SEGMENT26

             ,TABLE1.SEGMENT27                               SEGMENT27

             ,TABLE1.SEGMENT28                               SEGMENT28

             ,TABLE1.SEGMENT29                               SEGMENT29

             ,TABLE1.SEGMENT30                               SEGMENT30

             ,TABLE1.BEGIN_RUNNING_TOTAL_CR                  BEGIN_RUNNING_TOTAL_CR

             ,TABLE1.BEGIN_RUNNING_TOTAL_DR                  BEGIN_RUNNING_TOTAL_DR

             ,TABLE1.END_RUNNING_TOTAL_CR                    END_RUNNING_TOTAL_CR

             ,TABLE1.END_RUNNING_TOTAL_DR                    END_RUNNING_TOTAL_DR

             ,TABLE1.LEGAL_ENTITY_ID                         LEGAL_ENTITY_ID

             ,TABLE1.LEGAL_ENTITY_NAME                       LEGAL_ENTITY_NAME

             ,TABLE1.LE_ADDRESS_LINE_1                       LE_ADDRESS_LINE_1

             ,TABLE1.LE_ADDRESS_LINE_2                       LE_ADDRESS_LINE_2

             ,TABLE1.LE_ADDRESS_LINE_3                       LE_ADDRESS_LINE_3

             ,TABLE1.LE_CITY                                 LE_CITY

             ,TABLE1.LE_REGION_1                             LE_REGION_1

             ,TABLE1.LE_REGION_2                             LE_REGION_2

             ,TABLE1.LE_REGION_3                             LE_REGION_3

             ,TABLE1.LE_POSTAL_CODE                          LE_POSTAL_CODE

             ,TABLE1.LE_COUNTRY                              LE_COUNTRY

             ,TABLE1.LE_REGISTRATION_NUMBER                  LE_REGISTRATION_NUMBER

             ,TABLE1.LE_REGISTRATION_EFFECTIVE_FROM          LE_REGISTRATION_EFFECTIVE_FROM

             ,TABLE1.LE_BR_DAILY_INSCRIPTION_NUMBER          LE_BR_DAILY_INSCRIPTION_NUMBER

             ,TABLE1.LE_BR_DAILY_INSCRIPTION_DATE            LE_BR_DAILY_INSCRIPTION_DATE

             ,TABLE1.LE_BR_DAILY_ENTITY                      LE_BR_DAILY_ENTITY

             ,TABLE1.LE_BR_DAILY_LOCATION                    LE_BR_DAILY_LOCATION

             ,TABLE1.LE_BR_DIRECTOR_NUMBER                   LE_BR_DIRECTOR_NUMBER

             ,TABLE1.LE_BR_ACCOUNTANT_NUMBER                 LE_BR_ACCOUNTANT_NUMBER

             ,TABLE1.LE_BR_ACCOUNTANT_NAME                   LE_BR_ACCOUNTANT_NAME

      FROM

           (SELECT  /*+ leading (glbgt gjl gjh gir ael aeh) */

              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_transfer_date

                       ,'YYYY-MM-DD"T"hh:mi:ss')             GL_TRANSFER_DATE

               ,to_char(aeh.reference_date

                       ,'YYYY-MM-DD')                        REFERENCE_DATE

               ,to_char(aeh.completed_date

                       ,'YYYY-MM-DD"T"hh:mi:ss')             COMPLETED_DATE

               ,ent.transaction_number                       TRANSACTION_NUMBER

               ,to_char(xle.transaction_date

                       ,'YYYY-MM-DD"T"hh:mi:ss')             TRANSACTION_DATE

               ,fsv1.header_name                             ACCOUNTING_SEQUENCE_NAME

               ,fsv1.version_name                            ACCOUNTING_SEQUENCE_VERSION

               ,aeh.completion_acct_seq_value                ACCOUNTING_SEQUENCE_NUMBER

               ,fsv2.header_name                             REPORTING_SEQUENCE_NAME

               ,fsv2.version_name                            REPORTING_SEQUENCE_VERSION

               ,aeh.close_acct_seq_value                     REPORTING_SEQUENCE_NUMBER

               ,NULL                                         DOCUMENT_CATEGORY

               ,fns.name                                     DOCUMENT_SEQUENCE_NAME

               ,aeh.doc_sequence_value                       DOCUMENT_SEQUENCE_NUMBER

               ,aeh.application_id                           APPLICATION_ID

               ,fap.application_name                         APPLICATION_NAME

               ,aeh.ae_header_id                             HEADER_ID

               ,aeh.description                              HEADER_DESCRIPTION

               ,xlk1.meaning                                 FUND_STATUS

               ,gjct.user_je_category_name                   JE_CATEGORY_NAME

               ,gjst.user_je_source_name                     JE_SOURCE_NAME

               ,xle.event_id                                 EVENT_ID

               ,to_char(xle.event_date

                       ,'YYYY-MM-DD')                        EVENT_DATE

               ,xle.event_number                             EVENT_NUMBER

               ,xet.event_class_code                         EVENT_CLASS_CODE

               ,xect.NAME                                    EVENT_CLASS_NAME

               ,aeh.event_type_code                          EVENT_TYPE_CODE

               ,xet.NAME                                     EVENT_TYPE_NAME

               ,gjb.NAME                                     GL_BATCH_NAME

               ,to_char(gjb.posted_date

                       ,'YYYY-MM-DD')                        POSTED_DATE

               ,gjh.NAME                                     GL_JE_NAME

               ,gjh.external_reference                       EXTERNAL_REFERENCE

               ,gjl.je_line_num                              GL_LINE_NUMBER

               ,ael.displayed_line_number                    LINE_NUMBER

               ,ael.accounting_class_code                    ACCOUNTING_CLASS_CODE

               ,xlk2.meaning                                 ACCOUNTING_CLASS_NAME

               ,ael.description                              LINE_DESCRIPTION

               ,ael.currency_code                            ENTERED_CURRENCY

               ,ael.currency_conversion_rate                 CONVERSION_RATE

               ,to_char(ael.currency_conversion_date

                       ,'YYYY-MM-DD')                        CONVERSION_RATE_DATE

               ,ael.currency_conversion_type                 CONVERSION_RATE_TYPE_CODE

               ,gdct.user_conversion_type                    CONVERSION_RATE_TYPE

               ,ael.entered_dr                               ENTERED_DR

               ,ael.entered_cr                               ENTERED_CR

               ,ael.unrounded_accounted_dr                   UNROUNDED_ACCOUNTED_DR

               ,ael.unrounded_accounted_cr                   UNROUNDED_ACCOUNTED_CR

               ,ael.accounted_dr                             ACCOUNTED_DR

               ,ael.accounted_cr                             ACCOUNTED_CR

               ,ael.statistical_amount                       STATISTICAL_AMOUNT

               ,ael.jgzz_recon_ref                           RECONCILIATION_REFERENCE

               ,ael.attribute_category                       ATTRIBUTE_CATEGORY

               ,ael.attribute1                               ATTRIBUTE1

               ,ael.attribute2                               ATTRIBUTE2

               ,ael.attribute3                               ATTRIBUTE3

               ,ael.attribute4                               ATTRIBUTE4

               ,ael.attribute5                               ATTRIBUTE5

               ,ael.attribute6                               ATTRIBUTE6

               ,ael.attribute7                               ATTRIBUTE7

               ,ael.attribute8                               ATTRIBUTE8

               ,ael.attribute9                               ATTRIBUTE9

               ,ael.attribute10                              ATTRIBUTE10

               ,ael.party_type_code                          PARTY_TYPE_CODE

               ,NULL                                         PARTY_TYPE

            --  &p_party_columns               --               PARTY_INFO

         --  &p_trx_identifiers_1

          -- &p_trx_identifiers_2

          -- &p_trx_identifiers_3

          -- &p_trx_identifiers_4

          -- &p_trx_identifiers_5

               ,glbgt.ledger_id                              LEDGER_ID

               ,glbgt.ledger_short_name                      LEDGER_SHORT_NAME

               ,glbgt.ledger_description                     LEDGER_DESCRIPTION

               ,glbgt.ledger_name                            LEDGER_NAME

               ,glbgt.ledger_currency                        LEDGER_CURRENCY

               ,glbgt.period_year                            PERIOD_YEAR

               ,glbgt.period_number                          PERIOD_NUMBER

               ,glbgt.period_name                            PERIOD_NAME

               ,to_char(glbgt.period_start_date

                                       ,'YYYY-MM-DD')        PERIOD_START_DATE

               ,to_char(glbgt.period_end_date

                                       ,'YYYY-MM-DD')        PERIOD_END_DATE

               ,glbgt.balance_type_code                      BALANCE_TYPE_CODE

               ,glbgt.balance_type                           BALANCE_TYPE

               ,glbgt.budget_name                            BUDGET_NAME

               ,glbgt.encumbrance_type                       ENCUMBRANCE_TYPE

               ,glbgt.begin_balance_dr                       BEGIN_BALANCE_DR

               ,glbgt.begin_balance_cr                       BEGIN_BALANCE_CR

               ,glbgt.period_net_dr                          PERIOD_NET_DR

               ,glbgt.period_net_cr                          PERIOD_NET_CR

               ,glbgt.code_combination_id                    CODE_COMBINATION_ID

               ,glbgt.accounting_code_combination            ACCOUNTING_CODE_COMBINATION

               ,glbgt.code_combination_description           CODE_COMBINATION_DESCRIPTION

               ,glbgt.control_account_flag                   CONTROL_ACCOUNT_FLAG

               ,glbgt.control_account                        CONTROL_ACCOUNT

               ,glbgt.balancing_segment                      BALANCING_SEGMENT

               ,glbgt.natural_account_segment                NATURAL_ACCOUNT_SEGMENT

               ,glbgt.cost_center_segment                    COST_CENTER_SEGMENT

               ,glbgt.management_segment                     MANAGEMENT_SEGMENT

               ,glbgt.intercompany_segment                   INTERCOMPANY_SEGMENT

               ,glbgt.balancing_segment_desc                 BALANCING_SEGMENT_DESC

               ,glbgt.natural_account_desc                   NATURAL_ACCOUNT_DESC

               ,glbgt.cost_center_desc                       COST_CENTER_DESC

               ,glbgt.management_segment_desc                MANAGEMENT_SEGMENT_DESC

               ,glbgt.intercompany_segment_desc              INTERCOMPANY_SEGMENT_DESC

               ,glbgt.segment1                               SEGMENT1

               ,glbgt.segment2                               SEGMENT2

               ,glbgt.segment3                               SEGMENT3

               ,glbgt.segment4                               SEGMENT4

               ,glbgt.segment5                               SEGMENT5

               ,glbgt.segment6                               SEGMENT6

               ,glbgt.segment7                               SEGMENT7

               ,glbgt.segment8                               SEGMENT8

               ,glbgt.segment9                               SEGMENT9

               ,glbgt.segment10                              SEGMENT10

               ,glbgt.segment11                              SEGMENT11

               ,glbgt.segment12                              SEGMENT12

               ,glbgt.segment13                              SEGMENT13

               ,glbgt.segment14                              SEGMENT14

               ,glbgt.segment15                              SEGMENT15

               ,glbgt.segment16                              SEGMENT16

               ,glbgt.segment17                              SEGMENT17

               ,glbgt.segment18                              SEGMENT18

               ,glbgt.segment19                              SEGMENT19

               ,glbgt.segment20                              SEGMENT20

               ,glbgt.segment21                              SEGMENT21

               ,glbgt.segment22                              SEGMENT22

               ,glbgt.segment23                              SEGMENT23

               ,glbgt.segment24                              SEGMENT24

               ,glbgt.segment25                              SEGMENT25

               ,glbgt.segment26                              SEGMENT26

               ,glbgt.segment27                              SEGMENT27

               ,glbgt.segment28                              SEGMENT28

               ,glbgt.segment29                              SEGMENT29

               ,glbgt.segment30                              SEGMENT30

               ,glbgt.begin_running_total_cr                 BEGIN_RUNNING_TOTAL_CR

               ,glbgt.begin_running_total_dr                 BEGIN_RUNNING_TOTAL_DR

               ,glbgt.end_running_total_cr                   END_RUNNING_TOTAL_CR

               ,glbgt.end_running_total_dr                   END_RUNNING_TOTAL_DR

               ,glbgt.legal_entity_id                        LEGAL_ENTITY_ID

               ,glbgt.legal_entity_name                      LEGAL_ENTITY_NAME

               ,glbgt.le_address_line_1                      LE_ADDRESS_LINE_1

               ,glbgt.le_address_line_2                      LE_ADDRESS_LINE_2

               ,glbgt.le_address_line_3                      LE_ADDRESS_LINE_3

               ,glbgt.le_city                                LE_CITY

               ,glbgt.le_region_1                            LE_REGION_1

               ,glbgt.le_region_2                            LE_REGION_2

               ,glbgt.le_region_3                            LE_REGION_3

               ,glbgt.le_postal_code                         LE_POSTAL_CODE

               ,glbgt.le_country                             LE_COUNTRY

               ,glbgt.le_registration_number                 LE_REGISTRATION_NUMBER

               ,glbgt.le_registration_effective_from         LE_REGISTRATION_EFFECTIVE_FROM

               ,glbgt.le_br_daily_inscription_number         LE_BR_DAILY_INSCRIPTION_NUMBER

               ,to_char(glbgt.le_br_daily_inscription_date

                                      ,'YYYY-MM-DD')                                             LE_BR_DAILY_INSCRIPTION_DATE

               ,glbgt.le_br_daily_entity                     LE_BR_DAILY_ENTITY

               ,glbgt.le_br_daily_location                   LE_BR_DAILY_LOCATION

               ,glbgt.le_br_director_number                  LE_BR_DIRECTOR_NUMBER

               ,glbgt.le_br_accountant_number                LE_BR_ACCOUNTANT_NUMBER

               ,glbgt.le_br_accountant_name                  LE_BR_ACCOUNTANT_NAME

       

       

      FROM     xla_ae_headers                   aeh

              ,xla_ae_lines                     ael

              ,xla_lookups                      xlk1

              ,xla_lookups                      xlk2

              ,xla_events                       xle

              ,xla_event_classes_tl             xect

              ,xla_event_types_tl               xet

              ,fnd_user                         fdu

              ,xla_transaction_entities         ent

              ,fnd_application_tl               fap

              ,fun_seq_versions                 fsv1

              ,fun_seq_versions                 fsv2

              ,fnd_document_sequences           fns

              ,gl_je_categories_tl              gjct

              ,gl_je_sources_tl                 gjst

              ,gl_daily_conversion_types        gdct

              ,gl_import_references             gir

              ,gl_je_lines                      gjl

              ,gl_je_headers                    gjh

              ,gl_je_batches                    gjb

              ,xla_report_balances_gt           glbgt

       

       

      WHERE    --&p_ledger_filters

      --added as part of 6719948

        --AND  

         gjl.ledger_id                    = glbgt.ledger_id

        AND    gjl.code_combination_id          = glbgt.code_combination_id

        AND    gjl.effective_date               BETWEEN glbgt.period_start_date AND glbgt.period_end_date

        AND    gjl.effective_date               BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO

        AND    gjl.period_name                  = glbgt.period_name

        AND    gjl.je_header_id                   = gjh.je_header_id

        AND    gjl.period_name                    = gjh.period_name

        AND    gjl.je_header_id                   = gir.je_header_id

        AND    gjl.je_line_num                    = gir.je_line_num

        AND    gjh.je_header_id                   = gir.je_header_id

        AND    gjh.status                         = 'P'

        AND    NVL(gjh.je_from_sla_flag,'N')      IN('Y','U')

        AND    gjb.je_batch_id                    = gir.je_batch_id

        AND    gjb.status                         = 'P'

        AND    gir.gl_sl_link_id                  = ael.gl_sl_link_id

        AND    gir.gl_sl_link_table               = ael.gl_sl_link_table

      --AND  gjh.currency_code                    = glbgt.ledger_currency --added bug 6722505

        AND    gjct.je_category_name              = aeh.je_category_name

        AND    gjct.LANGUAGE                      = USERENV('LANG')

        AND    gjst.je_source_name                = gjh.je_source

        AND    gjst.LANGUAGE                      = USERENV('LANG')

        AND    aeh.accounting_entry_status_code   = 'F'

        AND    aeh.gl_transfer_status_code        = 'Y'

        AND    aeh.balance_type_code              = glbgt.balance_type_code

        AND    NVL(aeh.budget_version_id,-19999)  = NVL(glbgt.budget_version_id,-19999)

        AND    ael.application_id                 = aeh.application_id

        AND    ael.ae_header_id                   = aeh.ae_header_id

        AND    NVL(ael.encumbrance_type_id,-19999)= NVL(glbgt.encumbrance_type_id,-19999)  -- 4458381

        AND    xlk1.lookup_type(+)                = 'XLA_FUNDS_STATUS'

        AND    xlk1.lookup_code(+)                = aeh.funds_status_code

        AND    xlk2.lookup_type                   = 'XLA_ACCOUNTING_CLASS'

        AND    xlk2.lookup_code                   = ael.accounting_class_code

        AND    xle.application_id                 = aeh.application_id

        AND    xle.event_id                       = aeh.event_id

        AND    xet.application_id                 = aeh.application_id

        AND    xet.event_type_code                = aeh.event_type_code

        AND    xet.LANGUAGE                       = USERENV('LANG')

        AND    xect.application_id                = xet.application_id

        AND    xect.entity_code                   = xet.entity_code

        AND    xect.event_class_code              = xet.event_class_code

        AND    xect.LANGUAGE                      = USERENV('LANG')

        AND    ent.application_id                 = aeh.application_id

        AND    ent.entity_id                      = aeh.entity_id

      --AND    ent.ledger_id                      = aeh.ledger_id  Bug 7557990

        AND    fdu.user_id                        = ent.created_by

        AND    fap.application_id                 = aeh.application_id

        AND    fap.LANGUAGE                       = USERENV('LANG')

        AND    fsv1.seq_version_id(+)             = aeh.completion_acct_seq_version_id

        AND    fsv2.seq_version_id(+)             = aeh.close_acct_seq_version_id

        AND    fns.application_id(+)              = aeh.application_id

        AND    fns.doc_sequence_id(+)             = aeh.doc_sequence_id

        AND    gdct.conversion_type(+)            = ael.currency_conversion_type

          &p_sla_other_filter

      --removed as part of 6719948

      --AND    ael.code_combination_id            = glbgt.code_combination_id

      --AND    aeh.ledger_id                      = glbgt.ledger_id

      --AND    aeh.accounting_date                BETWEEN glbgt.period_start_date AND glbgt.period_end_date

      --AND    aeh.accounting_date                BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO

      --AND    aeh.period_name                    = glbgt.period_name

      --AND    gjh.ledger_id                      = aeh.ledger_id

       

       

         UNION ALL

       

       

         SELECT   /*+ leading (glbgt gjl gjh gjb) */

                   to_char(gjh.default_effective_date

                           ,'YYYY-MM-DD')                        GL_DATE

                   ,fdu.user_name                                CREATED_BY

                   ,to_char(gjh.creation_date

                           ,'YYYY-MM-DD"T"hh:mi:ss')             CREATION_DATE

                   ,to_char(gjh.last_update_date

                           ,'YYYY-MM-DD')                        LAST_UPDATE_DATE

                   ,NULL                                         GL_TRANSFER_DATE

                   ,to_char(gjh.reference_date

                           ,'YYYY-MM-DD')                        REFERENCE_DATE

                   ,NULL                                         COMPLETED_DATE

                   ,NULL                                         TRANSACTION_NUMBER

                   ,NULL                                         TRANSACTION_DATE

                   ,fsv1.header_name                             ACCOUNTING_SEQUENCE_NAME

                   ,fsv1.version_name                            ACCOUNTING_SEQUENCE_VERSION

                   ,gjh.posting_acct_seq_value                   ACCOUNTING_SEQUENCE_NUMBER

                   ,fsv2.header_name                             REPORTING_SEQUENCE_NAME

                   ,fsv2.version_name                            REPORTING_SEQUENCE_VERSION

                   ,gjh.close_acct_seq_value                     REPORTING_SEQUENCE_NUMBER

                   ,NULL                                         DOCUMENT_CATEGORY

                   ,NULL                                         DOCUMENT_SEQUENCE_NAME

                   ,NULL                                         DOCUMENT_SEQUENCE_NUMBER

                   ,NULL                                         APPLICATION_ID

                   ,NULL                                         APPLICATION_NAME

                   ,gjh.je_header_id                             HEADER_ID

                   ,gjh.description                              HEADER_DESCRIPTION

                   ,NULL                                         FUND_STATUS

                   ,gjct.user_je_category_name                   JE_CATEGORY_NAME

                   ,gjst.user_je_source_name                     JE_SOURCE_NAME

                   ,NULL                                         EVENT_ID

                   ,NULL                                         EVENT_DATE

                   ,NULL                                         EVENT_NUMBER

                   ,NULL                                         EVENT_CLASS_CODE

                   ,NULL                                         EVENT_CLASS_NAME

                   ,NULL                                         EVENT_TYPE_CODE

                   ,NULL                                         EVENT_TYPE_NAME

                   ,gjb.NAME                                     GL_BATCH_NAME

                   ,to_char(gjb.posted_date

                           ,'YYYY-MM-DD')                        POSTED_DATE

                   ,gjh.NAME                                     GL_JE_NAME

                   ,gjh.external_reference                       EXTERNAL_REFERENCE

                   ,gjl.je_line_num                              GL_LINE_NUMBER

                   ,gjl.je_line_num                              LINE_NUMBER

                   ,NULL                                         ACCOUNTING_CLASS_CODE

                   ,NULL                                         ACCOUNTING_CLASS_NAME

       

       

                   ,gjl.description                              LINE_DESCRIPTION

                   ,gjh.currency_code                            ENTERED_CURRENCY

                   ,gjh.currency_conversion_rate                 CONVERSION_RATE

                   ,to_char(gjh.currency_conversion_date

                           ,'YYYY-MM-DD')                        CONVERSION_RATE_DATE

                   ,gjh.currency_conversion_type                 CONVERSION_RATE_TYPE_CODE

                   ,gdct.user_conversion_type                    CONVERSION_RATE_TYPE

                   ,gjl.entered_dr                               ENTERED_DR

                   ,gjl.entered_cr                               ENTERED_CR

                   ,NULL                                         UNROUNDED_ACCOUNTED_DR

                   ,NULL                                         UNROUNDED_ACCOUNTED_CR

                   ,gjl.accounted_dr                             ACCOUNTED_DR

                   ,gjl.accounted_cr                             ACCOUNTED_CR

                   ,gjl.stat_amount                              STATISTICAL_AMOUNT

                   ,gjl.jgzz_recon_ref_11i                       RECONCILIATION_REFERENCE

                   ,gjl.context                                  ATTRIBUTE_CATEGORY

                   ,gjl.attribute1                               ATTRIBUTE1

                   ,gjl.attribute2                               ATTRIBUTE2

                   ,gjl.attribute3                               ATTRIBUTE3

                   ,gjl.attribute4                               ATTRIBUTE4

                   ,gjl.attribute5                               ATTRIBUTE5

                   ,gjl.attribute6                               ATTRIBUTE6

                   ,gjl.attribute7                               ATTRIBUTE7

                   ,gjl.attribute8                               ATTRIBUTE8

                   ,gjl.attribute9                               ATTRIBUTE9

                   ,gjl.attribute10                              ATTRIBUTE10

                   ,NULL                                         PARTY_TYPE_CODE

                   ,NULL                                         PARTY_TYPE

                  -- ,NULL                                       PARTY_INFO

                  -- ,NULL                                       USERIDS

                   ,glbgt.ledger_id                              LEDGER_ID

                   ,glbgt.ledger_short_name                      LEDGER_SHORT_NAME

                   ,glbgt.ledger_description                     LEDGER_DESCRIPTION

                   ,glbgt.ledger_name                            LEDGER_NAME

                   ,glbgt.ledger_currency                        LEDGER_CURRENCY

                   ,glbgt.period_year                            PERIOD_YEAR

                   ,glbgt.period_number                          PERIOD_NUMBER

                   ,glbgt.period_name                            PERIOD_NAME

                   ,to_char(glbgt.period_start_date

                                              ,'YYYY-MM-DD')     PERIOD_START_DATE

                   ,to_char(glbgt.period_end_date

                                              ,'YYYY-MM-DD')     PERIOD_END_DATE

                   ,glbgt.balance_type_code                      BALANCE_TYPE_CODE

                   ,glbgt.balance_type                           BALANCE_TYPE

                   ,glbgt.budget_name                            BUDGET_NAME

                   ,glbgt.encumbrance_type                       ENCUMBRANCE_TYPE

                   ,glbgt.begin_balance_dr                       BEGIN_BALANCE_DR

                   ,glbgt.begin_balance_cr                       BEGIN_BALANCE_CR

                   ,glbgt.period_net_dr                          PERIOD_NET_DR

                   ,glbgt.period_net_cr                          PERIOD_NET_CR

                   ,glbgt.code_combination_id                    CODE_COMBINATION_ID

                   ,glbgt.accounting_code_combination            ACCOUNTING_CODE_COMBINATION

                   ,glbgt.code_combination_description           CODE_COMBINATION_DESCRIPTION

                   ,glbgt.control_account_flag                   CONTROL_ACCOUNT_FLAG

                   ,glbgt.control_account                        CONTROL_ACCOUNT

                   ,glbgt.balancing_segment                      BALANCING_SEGMENT

                   ,glbgt.natural_account_segment                NATURAL_ACCOUNT_SEGMENT

                   ,glbgt.cost_center_segment                    COST_CENTER_SEGMENT

                   ,glbgt.management_segment                     MANAGEMENT_SEGMENT

                   ,glbgt.intercompany_segment                   INTERCOMPANY_SEGMENT

                   ,glbgt.balancing_segment_desc                 BALANCING_SEGMENT_DESC

                   ,glbgt.natural_account_desc                   NATURAL_ACCOUNT_DESC

                   ,glbgt.cost_center_desc                       COST_CENTER_DESC

                   ,glbgt.management_segment_desc                MANAGEMENT_SEGMENT_DESC

                   ,glbgt.intercompany_segment_desc              INTERCOMPANY_SEGMENT_DESC

                   ,glbgt.segment1                               SEGMENT1

                   ,glbgt.segment2                               SEGMENT2

                   ,glbgt.segment3                               SEGMENT3

                   ,glbgt.segment4                               SEGMENT4

                   ,glbgt.segment5                               SEGMENT5

                   ,glbgt.segment6                               SEGMENT6

                   ,glbgt.segment7                               SEGMENT7

                   ,glbgt.segment8                               SEGMENT8

                   ,glbgt.segment9                               SEGMENT9

                   ,glbgt.segment10                              SEGMENT10

                   ,glbgt.segment11                              SEGMENT11

                   ,glbgt.segment12                              SEGMENT12

                   ,glbgt.segment13                              SEGMENT13

                   ,glbgt.segment14                              SEGMENT14

                   ,glbgt.segment15                              SEGMENT15

                   ,glbgt.segment16                              SEGMENT16

                   ,glbgt.segment17                              SEGMENT17

                   ,glbgt.segment18                              SEGMENT18

                   ,glbgt.segment19                              SEGMENT19

                   ,glbgt.segment20                              SEGMENT20

                   ,glbgt.segment21                              SEGMENT21

                   ,glbgt.segment22                              SEGMENT22

                   ,glbgt.segment23                              SEGMENT23

                   ,glbgt.segment24                              SEGMENT24

                   ,glbgt.segment25                              SEGMENT25

                   ,glbgt.segment26                              SEGMENT26

                   ,glbgt.segment27                              SEGMENT27

                   ,glbgt.segment28                              SEGMENT28

                   ,glbgt.segment29                              SEGMENT29

                   ,glbgt.segment30                              SEGMENT30

                   ,glbgt.begin_running_total_cr                 BEGIN_RUNNING_TOTAL_CR

                   ,glbgt.begin_running_total_dr                 BEGIN_RUNNING_TOTAL_DR

                   ,glbgt.end_running_total_cr                   END_RUNNING_TOTAL_CR

                   ,glbgt.end_running_total_dr                   END_RUNNING_TOTAL_DR

                   ,glbgt.legal_entity_id                        LEGAL_ENTITY_ID

                   ,glbgt.legal_entity_name                      LEGAL_ENTITY_NAME

                   ,glbgt.le_address_line_1                      LE_ADDRESS_LINE_1

                   ,glbgt.le_address_line_2                      LE_ADDRESS_LINE_2

                   ,glbgt.le_address_line_3                      LE_ADDRESS_LINE_3

                   ,glbgt.le_city                                LE_CITY

                   ,glbgt.le_region_1                            LE_REGION_1

                   ,glbgt.le_region_2                            LE_REGION_2

                   ,glbgt.le_region_3                            LE_REGION_3

                   ,glbgt.le_postal_code                         LE_POSTAL_CODE

                   ,glbgt.le_country                             LE_COUNTRY

                   ,glbgt.le_registration_number                 LE_REGISTRATION_NUMBER

                   ,glbgt.le_registration_effective_from         LE_REGISTRATION_EFFECTIVE_FROM

                   ,glbgt.le_br_daily_inscription_number         LE_BR_DAILY_INSCRIPTION_NUMBER

                   ,to_char(glbgt.le_br_daily_inscription_date

                                              ,'YYYY-MM-DD')     LE_BR_DAILY_INSCRIPTION_DATE

                   ,glbgt.le_br_daily_entity                     LE_BR_DAILY_ENTITY

                   ,glbgt.le_br_daily_location                   LE_BR_DAILY_LOCATION

                   ,glbgt.le_br_director_number                  LE_BR_DIRECTOR_NUMBER

                   ,glbgt.le_br_accountant_number                LE_BR_ACCOUNTANT_NUMBER

                   ,glbgt.le_br_accountant_name                  LE_BR_ACCOUNTANT_NAME

           FROM    fnd_user                         fdu

                  ,fun_seq_versions                 fsv1

                  ,fun_seq_versions                 fsv2

                  ,gl_je_categories_tl              gjct

                  ,gl_je_sources_tl                 gjst

                  ,gl_daily_conversion_types        gdct

                  ,gl_je_lines                      gjl

                  ,gl_je_headers                    gjh

                  ,gl_je_batches                    gjb

                  ,xla_report_balances_gt           glbgt

           WHERE   gjl.ledger_id                    = glbgt.ledger_id

             AND   gjl.code_combination_id          = glbgt.code_combination_id

             AND   gjl.effective_date               BETWEEN glbgt.period_start_date AND glbgt.period_end_date

             AND   gjl.effective_date               BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO

             AND   gjl.period_name                  = glbgt.period_name

             AND   gjh.je_header_id                 = gjl.je_header_id

             AND   gjh.actual_flag                  = glbgt.balance_type_code

             AND   decode(gjh.currency_code,'STAT',gjh.currency_code,glbgt.ledger_currency) = glbgt.ledger_currency --added bug 6686541

             AND   NVL(gjh.je_from_sla_flag,'N')    = 'N'

             AND   NVL(gjh.budget_version_id,-19999)= NVL(glbgt.budget_version_id,-19999)

             AND   NVL(gjh.encumbrance_type_id,-19999)= NVL(glbgt.encumbrance_type_id,-19999)

             AND   gjb.je_batch_id                  = gjh.je_batch_id

             AND   gjb.status                       = 'P'

             AND   fdu.user_id                      = gjb.created_by

             AND   fsv1.seq_version_id(+)           = gjh.posting_acct_seq_version_id

             AND   fsv2.seq_version_id(+)           = gjh.close_acct_seq_version_id

             AND   gjct.je_category_name            = gjh.je_category

             AND   gjct.LANGUAGE                    = USERENV('LANG')

             AND   gjst.je_source_name              = gjh.je_source

             AND   gjst.language                    = USERENV('LANG')

             AND   gdct.conversion_type(+)          = gjh.currency_conversion_type

             &p_gl_other_filter

       

       

         UNION ALL

       

       

         SELECT  /*+ leading (glbgt gjl gjh gjb) */

                   to_char(gjh.default_effective_date

                           ,'YYYY-MM-DD')                        GL_DATE

                   ,fdu.user_name                                CREATED_BY

                   ,to_char(gjh.creation_date

                           ,'YYYY-MM-DD"T"hh:mi:ss')             CREATION_DATE

                   ,to_char(gjh.last_update_date

                           ,'YYYY-MM-DD')                        LAST_UPDATE_DATE

                   ,NULL                                         GL_TRANSFER_DATE

                   ,to_char(gjh.reference_date

                           ,'YYYY-MM-DD')                        REFERENCE_DATE

                   ,NULL                                         COMPLETED_DATE

                   ,NULL                                         TRANSACTION_NUMBER

                   ,NULL                                         TRANSACTION_DATE

                   ,fsv1.header_name                             ACCOUNTING_SEQUENCE_NAME

                   ,fsv1.version_name                            ACCOUNTING_SEQUENCE_VERSION

                   ,gjh.posting_acct_seq_value                   ACCOUNTING_SEQUENCE_NUMBER

                   ,fsv2.header_name                             REPORTING_SEQUENCE_NAME

                   ,fsv2.version_name                            REPORTING_SEQUENCE_VERSION

                   ,gjh.close_acct_seq_value                     REPORTING_SEQUENCE_NUMBER

                   ,NULL                                         DOCUMENT_CATEGORY

                   ,NULL                                         DOCUMENT_SEQUENCE_NAME

                   ,NULL                                         DOCUMENT_SEQUENCE_NUMBER

                   ,NULL                                         APPLICATION_ID

                   ,NULL                                         APPLICATION_NAME

                   ,gjh.je_header_id                             HEADER_ID

                   ,gjh.description                              HEADER_DESCRIPTION

                   ,NULL                                         FUND_STATUS

                   ,gjct.user_je_category_name                   JE_CATEGORY_NAME

                   ,gjst.user_je_source_name                     JE_SOURCE_NAME

                   ,NULL                                         EVENT_ID

                   ,NULL                                         EVENT_DATE

                   ,NULL                                         EVENT_NUMBER

                   ,NULL                                         EVENT_CLASS_CODE

                   ,NULL                                         EVENT_CLASS_NAME

                   ,NULL                                         EVENT_TYPE_CODE

                   ,NULL                                         EVENT_TYPE_NAME

                   ,gjb.NAME                                     GL_BATCH_NAME

                   ,to_char(gjb.posted_date

                           ,'YYYY-MM-DD')                        POSTED_DATE

                   ,gjh.NAME                                     GL_JE_NAME

                   ,gjh.external_reference                       EXTERNAL_REFERENCE

                   ,gjl.je_line_num                              GL_LINE_NUMBER

                   ,gjl.je_line_num                              LINE_NUMBER

                   ,NULL                                         ACCOUNTING_CLASS_CODE

                   ,NULL                                         ACCOUNTING_CLASS_NAME

       

       

                   ,gjl.description                              LINE_DESCRIPTION

                   ,gjh.currency_code                            ENTERED_CURRENCY

                   ,gjh.currency_conversion_rate                 CONVERSION_RATE

                   ,to_char(gjh.currency_conversion_date

                           ,'YYYY-MM-DD')                        CONVERSION_RATE_DATE

                   ,gjh.currency_conversion_type                 CONVERSION_RATE_TYPE_CODE

                   ,gdct.user_conversion_type                    CONVERSION_RATE_TYPE

                   ,gjl.entered_dr                               ENTERED_DR

                   ,gjl.entered_cr                               ENTERED_CR

                   ,NULL                                         UNROUNDED_ACCOUNTED_DR

                   ,NULL                                         UNROUNDED_ACCOUNTED_CR

                   ,gjl.accounted_dr                             ACCOUNTED_DR

                   ,gjl.accounted_cr                             ACCOUNTED_CR

                   ,gjl.stat_amount                              STATISTICAL_AMOUNT

                   ,gjl.jgzz_recon_ref_11i                       RECONCILIATION_REFERENCE

                   ,gjl.context                                  ATTRIBUTE_CATEGORY

                   ,gjl.attribute1                               ATTRIBUTE1

                   ,gjl.attribute2                               ATTRIBUTE2

                   ,gjl.attribute3                               ATTRIBUTE3

                   ,gjl.attribute4                               ATTRIBUTE4

                   ,gjl.attribute5                               ATTRIBUTE5

                   ,gjl.attribute6                               ATTRIBUTE6

                   ,gjl.attribute7                               ATTRIBUTE7

                   ,gjl.attribute8                               ATTRIBUTE8

                   ,gjl.attribute9                               ATTRIBUTE9

                   ,gjl.attribute10                              ATTRIBUTE10

                   ,NULL                                         PARTY_TYPE_CODE

                   ,NULL                                         PARTY_TYPE

                  -- ,NULL                                       PARTY_INFO

                  --,NULL                                        USERIDS

                   ,glbgt.ledger_id                              LEDGER_ID

                   ,glbgt.ledger_short_name                      LEDGER_SHORT_NAME

                   ,glbgt.ledger_description                     LEDGER_DESCRIPTION

                   ,glbgt.ledger_name                            LEDGER_NAME

                   ,glbgt.ledger_currency                        LEDGER_CURRENCY

                   ,glbgt.period_year                            PERIOD_YEAR

                   ,glbgt.period_number                          PERIOD_NUMBER

                   ,glbgt.period_name                            PERIOD_NAME

                   ,to_char(glbgt.period_start_date

                                              ,'YYYY-MM-DD')     PERIOD_START_DATE

                   ,to_char(glbgt.period_end_date

                                              ,'YYYY-MM-DD')     PERIOD_END_DATE

                   ,glbgt.balance_type_code                      BALANCE_TYPE_CODE

                   ,glbgt.balance_type                           BALANCE_TYPE

                   ,glbgt.budget_name                            BUDGET_NAME

                   ,glbgt.encumbrance_type                       ENCUMBRANCE_TYPE

                   ,glbgt.begin_balance_dr                       BEGIN_BALANCE_DR

                   ,glbgt.begin_balance_cr                       BEGIN_BALANCE_CR

                   ,glbgt.period_net_dr                          PERIOD_NET_DR

                   ,glbgt.period_net_cr                          PERIOD_NET_CR

                   ,glbgt.code_combination_id                    CODE_COMBINATION_ID

                   ,glbgt.accounting_code_combination            ACCOUNTING_CODE_COMBINATION

                   ,glbgt.code_combination_description           CODE_COMBINATION_DESCRIPTION

                   ,glbgt.control_account_flag                   CONTROL_ACCOUNT_FLAG

                   ,glbgt.control_account                        CONTROL_ACCOUNT

                   ,glbgt.balancing_segment                      BALANCING_SEGMENT

                   ,glbgt.natural_account_segment                NATURAL_ACCOUNT_SEGMENT

                   ,glbgt.cost_center_segment                    COST_CENTER_SEGMENT

                   ,glbgt.management_segment                     MANAGEMENT_SEGMENT

                   ,glbgt.intercompany_segment                   INTERCOMPANY_SEGMENT

                   ,glbgt.balancing_segment_desc                 BALANCING_SEGMENT_DESC

                   ,glbgt.natural_account_desc                   NATURAL_ACCOUNT_DESC

                   ,glbgt.cost_center_desc                       COST_CENTER_DESC

                   ,glbgt.management_segment_desc                MANAGEMENT_SEGMENT_DESC

                   ,glbgt.intercompany_segment_desc              INTERCOMPANY_SEGMENT_DESC

                   ,glbgt.segment1                               SEGMENT1

                   ,glbgt.segment2                               SEGMENT2

                   ,glbgt.segment3                               SEGMENT3

                   ,glbgt.segment4                               SEGMENT4

                   ,glbgt.segment5                               SEGMENT5

                   ,glbgt.segment6                               SEGMENT6

                   ,glbgt.segment7                               SEGMENT7

                   ,glbgt.segment8                               SEGMENT8

                   ,glbgt.segment9                               SEGMENT9

                   ,glbgt.segment10                              SEGMENT10

                   ,glbgt.segment11                              SEGMENT11

                   ,glbgt.segment12                              SEGMENT12

                   ,glbgt.segment13                              SEGMENT13

                   ,glbgt.segment14                              SEGMENT14

                   ,glbgt.segment15                              SEGMENT15

                   ,glbgt.segment16                              SEGMENT16

                   ,glbgt.segment17                              SEGMENT17

                   ,glbgt.segment18                              SEGMENT18

                   ,glbgt.segment19                              SEGMENT19

                   ,glbgt.segment20                              SEGMENT20

                   ,glbgt.segment21                              SEGMENT21

                   ,glbgt.segment22                              SEGMENT22

                   ,glbgt.segment23                              SEGMENT23

                   ,glbgt.segment24                              SEGMENT24

                   ,glbgt.segment25                              SEGMENT25

                   ,glbgt.segment26                              SEGMENT26

                   ,glbgt.segment27                              SEGMENT27

                   ,glbgt.segment28                              SEGMENT28

                   ,glbgt.segment29                              SEGMENT29

                   ,glbgt.segment30                              SEGMENT30

                   ,glbgt.begin_running_total_cr                 BEGIN_RUNNING_TOTAL_CR

                   ,glbgt.begin_running_total_dr                 BEGIN_RUNNING_TOTAL_DR

                   ,glbgt.end_running_total_cr                   END_RUNNING_TOTAL_CR

                   ,glbgt.end_running_total_dr                   END_RUNNING_TOTAL_DR

                   ,glbgt.legal_entity_id                        LEGAL_ENTITY_ID

                   ,glbgt.legal_entity_name                      LEGAL_ENTITY_NAME

                   ,glbgt.le_address_line_1                      LE_ADDRESS_LINE_1

                   ,glbgt.le_address_line_2                      LE_ADDRESS_LINE_2

                   ,glbgt.le_address_line_3                      LE_ADDRESS_LINE_3

                   ,glbgt.le_city                                LE_CITY

                   ,glbgt.le_region_1                            LE_REGION_1

                   ,glbgt.le_region_2                            LE_REGION_2

                   ,glbgt.le_region_3                            LE_REGION_3

                   ,glbgt.le_postal_code                         LE_POSTAL_CODE

                   ,glbgt.le_country                             LE_COUNTRY

                   ,glbgt.le_registration_number                 LE_REGISTRATION_NUMBER

                   ,glbgt.le_registration_effective_from         LE_REGISTRATION_EFFECTIVE_FROM

                   ,glbgt.le_br_daily_inscription_number         LE_BR_DAILY_INSCRIPTION_NUMBER

                   ,to_char(glbgt.le_br_daily_inscription_date

                                              ,'YYYY-MM-DD')     LE_BR_DAILY_INSCRIPTION_DATE

                   ,glbgt.le_br_daily_entity                     LE_BR_DAILY_ENTITY

                   ,glbgt.le_br_daily_location                   LE_BR_DAILY_LOCATION

                   ,glbgt.le_br_director_number                  LE_BR_DIRECTOR_NUMBER

                   ,glbgt.le_br_accountant_number                LE_BR_ACCOUNTANT_NUMBER

                   ,glbgt.le_br_accountant_name                  LE_BR_ACCOUNTANT_NAME

           FROM    fnd_user                         fdu

                  ,fnd_new_messages                 fnm

                  ,fun_seq_versions                 fsv1

                  ,fun_seq_versions                 fsv2

                  ,gl_je_categories_tl              gjct

                  ,gl_je_sources_tl                 gjst

                  ,gl_daily_conversion_types        gdct

                  ,gl_je_lines                      gjl

                  ,gl_je_headers                    gjh

                  ,gl_je_batches                    gjb

                  ,xla_report_balances_gt           glbgt

           WHERE   gjl.ledger_id                    = glbgt.ledger_id

             AND   gjl.code_combination_id          = glbgt.code_combination_id

             AND   gjl.effective_date               BETWEEN glbgt.period_start_date AND glbgt.period_end_date

             AND   gjl.effective_date               BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO

             AND   gjl.period_name                  = glbgt.period_name

             AND   gjh.je_header_id                 = gjl.je_header_id

             AND   gjh.actual_flag                  = glbgt.balance_type_code

             AND   decode(gjh.currency_code,'STAT',gjh.currency_code,glbgt.ledger_currency) = glbgt.ledger_currency --added bug 6686541

             AND   NVL(gjh.je_from_sla_flag,'N')    = 'U'

             AND   fnm.application_id = 101

             AND   fnm.language_code = USERENV('LANG')

             AND   fnm.message_name in ('PPOS0220', 'PPOS0221', 'PPOS0222', 'PPOS0243', 'PPOS0222_G','PPOSO275')

             AND   gjl.description= fnm.message_text

             AND   NVL(gjh.budget_version_id,-19999) = NVL(glbgt.budget_version_id,-19999)

             AND   NVL(gjh.encumbrance_type_id,-19999) = NVL(glbgt.encumbrance_type_id,-19999)

             AND   gjb.je_batch_id                  = gjh.je_batch_id

             AND   gjb.status                       = 'P'

             AND   fdu.user_id                      = gjb.created_by

             AND   fsv1.seq_version_id(+)           = gjh.posting_acct_seq_version_id

             AND   fsv2.seq_version_id(+)           = gjh.close_acct_seq_version_id

             AND   gjct.je_category_name            = gjh.je_category

             AND   gjct.LANGUAGE                    = USERENV('LANG')

             AND   gjst.je_source_name              = gjh.je_source

             AND   gjst.language                    = USERENV('LANG')

             AND   gdct.conversion_type(+)          = gjh.currency_conversion_type

             AND  not exists    (select 'x'  from gl_import_references gir  where

                      gir.je_header_id=gjl.je_header_id

                      and gir.je_line_num=gjl.je_line_num)

             &p_gl_other_filter

             &p_begin_balance_union_all

       

       

        )TABLE1

          ORDER BY

              TABLE1.LEDGER_NAME

             ,TABLE1.LEDGER_CURRENCY

             ,TABLE1.CODE_COMBINATION_ID

             ,TABLE1.PERIOD_YEAR

             ,TABLE1.PERIOD_NUMBER

             ,TABLE1.BALANCE_TYPE_CODE

             ,TABLE1.BUDGET_NAME

             ,TABLE1.ENCUMBRANCE_TYPE

             ,TABLE1.JE_SOURCE_NAME

             ,TABLE1.HEADER_ID

       

      Thanks in Advance,

      Arun Kumar