XLA Third Party Balances

Description
Categories: BI Publisher, Financials
Application: Subledger Accounting
Source: Third Party Balances Report
Short Name: XLATPRPT
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_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
                 ,hr.name                           OPERATING_UNIT
                 ,TO_CHAR(xle.transaction_date
                         ,'YYYY-MM-DD"T"hh:mi:ss')  TRANSACTION_DATE
                 ,fsh1.header_name                  ACCOUNTING_SEQUENCE_NAME
                 ,fsv1.version_name                 ACCOUNTING_SEQUENCE_VERSION
                 ,aeh.completion_acct_seq_value     ACCOUNTING_SEQUENCE_NUMBER
                 ,fsh2.header_name                  REPORTING_SEQUENCE_NAME
                 ,fsv2.version_name                 REPORTING_SEQUENCE_VERSION
                 ,aeh.close_acct_seq_value          REPORTING_SEQUENCE_NUMBER
                 ,DECODE(aeh.upg_batch_id, -5672, null, fns.name)                          DOCUMENT_SEQUENCE_NAME
                 ,DECODE(aeh.upg_batch_id, -5672, null, aeh.doc_sequence_value)            DOCUMENT_SEQUENCE_NUMBER
                 ,aeh.ae_header_id                  HEADER_ID
                 ,aeh.description                   HEADER_DESCRIPTION
                 ,xlk1.meaning                      FUND_STATUS
                 ,gjct.user_je_category_name        JE_CATEGORY_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
                 ,xlk2.meaning                      JOURNAL_ENTRY_STATUS
                 ,xlk3.meaning                      TRANSFER_TO_GL_STATUS
                 ,glk.meaning                       GL_BATCH_STATUS
                 ,TO_CHAR(gjb.posted_date
                  ,'YYYY-MM-DD')             POSTED_DATE
                 ,ael.displayed_line_number         LINE_NUMBER
                 ,ael.accounting_class_code         ACCOUNTING_CLASS_CODE
                 ,xlk4.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.attribute11                   ATTRIBUTE11
                 ,ael.attribute12                   ATTRIBUTE12
                 ,ael.attribute13                   ATTRIBUTE13
                 ,ael.attribute14                   ATTRIBUTE14
                 ,ael.attribute15                   ATTRIBUTE15
                 ,ael.ae_line_num                   AE_LINE_NUM
		 &p_trx_identifiers_1
		 &p_trx_identifiers_2
		 &p_trx_identifiers_3
		 &p_trx_identifiers_4
		 &p_trx_identifiers_5                 
         FROM     xla_ae_lines                     ael
                 ,xla_ae_headers                   aeh
                 ,xla_lookups                      xlk1
                 ,xla_lookups                      xlk2
                 ,xla_lookups                      xlk3
                 ,xla_lookups                      xlk4
                 ,gl_lookups                       glk
                 ,xla_events                       xle
                 ,xla_event_types_tl               xet
                 ,xla_event_classes_tl             xect
                 ,fnd_user                         fdu
                 ,xla_transaction_entities         ent
                 ,fnd_application_tl               fap
                 ,fun_seq_headers                  fsh1
                 ,fun_seq_assignments              fsq1
                 ,fun_seq_versions                 fsv1
                 ,fun_seq_headers                  fsh2
                 ,fun_seq_assignments              fsq2
                 ,fun_seq_versions                 fsv2
		         ,fnd_document_sequences           fns
                 ,gl_je_categories_tl              gjct
                 ,gl_daily_conversion_types        gdct
		         ,gl_import_references             gir
		         ,gl_je_headers                    gjh
                 ,gl_je_batches                    gjb
                 ,hr_operating_units               hr -- Bug 9113330
        WHERE   aeh.application_id         = :APPLICATION_ID
          AND   ael.application_id         = :APPLICATION_ID    -- Bug16917946
          AND   aeh.ledger_id              = :LEDGER_ID 
          AND   ael.ledger_id              = :LEDGER_ID
          AND   aeh.period_name            = :PERIOD_NAME
          AND   aeh.accounting_date        BETWEEN :PERIOD_START_DATE and :PERIOD_END_DATE
		  AND   ael.accounting_date        BETWEEN :PERIOD_START_DATE and :PERIOD_END_DATE 
		  AND   ael.code_combination_id    = :CODE_COMBINATION_ID
          AND   ael.party_type_code        = :PARTY_TYPE_CODE
          AND   ael.party_id               = :PARTY_ID
          AND   ael.application_id         = aeh.application_id
          AND   ael.ae_header_id           = aeh.ae_header_id          
          AND   ael.control_balance_flag   = 'Y'
          AND   NVL(ael.party_site_id,-999)= :PARTY_SITE_ID
          AND   xlk1.lookup_type(+)        = 'XLA_FUNDS_STATUS'
          AND   xlk1.lookup_code(+)        = aeh.funds_status_code
          AND   xlk2.lookup_type           = 'XLA_ACCOUNTING_ENTRY_STATUS'
          AND   xlk2.lookup_code           = aeh.accounting_entry_status_code
          AND   xlk3.lookup_type           = 'GL_TRANSFER_FLAG'
          AND   xlk3.lookup_code           = aeh.GL_TRANSFER_STATUS_CODE
          AND   xlk4.lookup_type           = 'XLA_ACCOUNTING_CLASS'
          AND   xlk4.lookup_code           = ael.accounting_class_code
	      AND   xle.event_id               = aeh.event_id
	      AND   xle.application_id         = aeh.application_id
          AND   xet.application_id         = aeh.application_id
          AND   xet.event_type_code        = aeh.event_type_code
          AND   xet.LANGUAGE               = :P_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              = :P_LANG
          AND   ent.application_id         = aeh.application_id
          AND   ent.entity_id              = aeh.entity_id
          AND   fdu.user_id                = ent.created_by
          AND   hr.organization_id(+)      = nvl(ent.security_id_int_1,0)
          AND   fap.application_id         = aeh.application_id
          AND   fap.LANGUAGE               = :P_LANG
          AND   fsv1.seq_version_id(+)     = aeh.completion_acct_seq_version_id
          AND   fsv2.seq_version_id(+)     = aeh.close_acct_seq_version_id
          AND   fsq1.assignment_id(+)      = aeh.completion_acct_seq_assign_id
          AND   fsq2.assignment_id(+)      = aeh.close_acct_seq_assign_id
          AND   fsh1.seq_header_id(+)      = fsq1.seq_header_id
          AND   fsh2.seq_header_id(+)      = fsq2.seq_header_id
	      AND   fns.doc_sequence_id(+)     = aeh.doc_sequence_id
          AND   gdct.conversion_type(+)    = ael.currency_conversion_type
          AND   gir.gl_sl_link_id(+)       = ael.gl_sl_link_id
	      AND   gir.gl_sl_link_table(+)    = ael.gl_sl_link_table
	      AND   gjh.je_header_id(+)        = gir.je_header_id
          AND   decode(gjh.je_header_id,null,'Y',gjh.je_from_sla_flag) in ('U', 'Y')
          AND   gjb.je_batch_id(+)         = gir.je_batch_id
          AND   gjct.je_category_name      = aeh.je_category_name
          AND   gjct.LANGUAGE              = :P_LANG
          AND   glk.lookup_type(+)         = 'BATCH_STATUS'
          AND   glk.lookup_code(+)         = gjb.status
          &p_other_filter          
		  ) TABLE1
          ORDER BY TABLE1.GL_DATE
Parameter Name SQL text Validation
Enable Scalable Flag
 
LOV Oracle
P_INCLUDE_LEGAL_ENTITY
 
Include Legal Enity Information
 
LOV Oracle
P_INCLUDE_TAX_DETAILS
 
Include Tax Details
 
LOV Oracle
P_INCLUDE_USER_TRX_IDENTIFIERS
 
P_INCLUDE_ZERO_AMOUNT_BALANCES
 
P_INCLUDE_ZERO_AMOUNT_LINES
 
P_THIRD_PARTY_SITE
 
P_THIRD_PARTY_NAME
 
P_DUMMY_PARTY
 
P_BALANCE_SIDE
 
P_INCLUDE_DRAFT_ACTIVITY
 
GL Date To
 
Date
GL Date From
 
Date
P_LEGAL_ENTITY
 
XLA_SRS_JE_SOURCES_ALL
 
LOV Oracle
P_COA_ID
 
LOV Oracle
P_LEDGER
 
XLA_SRS_SUBLEDGERS
 
LOV Oracle
Include User Transaction Identifiers
 
LOV Oracle
Include Zero Amount Balances
 
LOV Oracle
Include Zero Amount Lines
 
LOV Oracle
Account Flexfield To
 
Account Flexfield From
 
Third Party Number To
 
LOV Oracle
Third Party Number From
 
LOV Oracle
Third Party Site
 
LOV Oracle
Third Party Name
 
LOV Oracle
Party Type
 
LOV Oracle
Include Draft Activity
 
LOV Oracle
Period To
 
LOV Oracle
Period From
 
LOV Oracle
Journal Source Name
 
Ledger/Ledger Set
 
LOV Oracle