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 |
