XLA Third Party Balances
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Standard Third Party Balances Report
Application: Subledger Accounting
Source: Third Party Balances Report
Short Name: XLATPRPT
DB package: XLA_TP_BALANCE_RPT_PKG
Description: Standard Third Party Balances Report
Application: Subledger Accounting
Source: Third Party Balances Report
Short Name: XLATPRPT
DB package: XLA_TP_BALANCE_RPT_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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 | |
|---|---|---|---|
| Ledger/Ledger Set |
|
LOV Oracle | |
| Journal Source Name |
|
Char | |
| Period From |
|
LOV Oracle | |
| Period To |
|
LOV Oracle | |
| Include Draft Activity |
|
LOV Oracle | |
| Party Type |
|
LOV Oracle | |
| Third Party Name |
|
LOV Oracle | |
| Third Party Site |
|
LOV Oracle | |
| Third Party Number From |
|
LOV Oracle | |
| Third Party Number To |
|
LOV Oracle | |
| Account Flexfield From |
|
Char | |
| Account Flexfield To |
|
Char | |
| Include Zero Amount Lines |
|
LOV Oracle | |
| Include Zero Amount Balances |
|
LOV Oracle | |
| Include User Transaction Identifiers |
|
LOV Oracle |
