JG Global Third Party Detail and Balances
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Regional Localizations
Source: Global Third Party Detail and Balances Report
Short Name: JGSLATPDBL
DB package: XLA_TP_BALANCE_RPT_PKG
Application: Regional Localizations
Source: Global Third Party Detail and Balances Report
Short Name: JGSLATPDBL
DB package: XLA_TP_BALANCE_RPT_PKG
Run
JG Global Third Party Detail and Balances and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |