eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CE_XLA_EXT_HEADERS_V

Source

View Text - Preformatted


SELECT ah.event_id, cf.cashflow_id, Rowidtochar(ah.ROWID), 'CE_TRANS', ah.event_type, ah.accounting_date, ah.status_code, ah.reversal_indicator_flag, 'CE_TRANS', ah.reversal_first_dist_id, ah.reversal_second_dist_num, gl.NAME, lecf.NAME, bacf.bank_account_num, Decode(cf.cashflow_direction, 'DEBIT', 'PAYMENT', 'CREDIT', 'RECEIPT', cf.cashflow_direction), cf.cashflow_currency_code, cf.cashflow_date, cf.cashflow_amount, Decode(cf.cashflow_currency_code, bacf.currency_code, cf.cashflow_amount, cf.base_amount), Substrb(cf.description, 1, 240), cf.reference_text, Decode(cep_standard.Get_conversion_rate(cf.cashflow_ledger_id, cf.cashflow_currency_code, Decode(cf.source_trxn_type, 'STMT', Decode(sys.bsc_exchange_date_type, 'CFD', cf.cashflow_date, 'BSG', sh.gl_date, 'CLD', ah.cleared_date, 'BSD', sh.statement_date, 'CFE', cf.cashflow_exchange_date), 'BAT', Decode(sys.bat_exchange_date_type, 'CFD', cf.cashflow_date, 'AVD', cf.actual_value_date, 'CLD', ah.cleared_date, 'BSD', sh.statement_date, 'BSG', sh.gl_date, 'SLD', sl.trx_date, 'TRX', trx.transaction_date), cf.cashflow_exchange_date), sys.cashflow_exchange_rate_type), 0, To_number(NULL)), Decode(cf.source_trxn_type, 'STMT', Decode(sys.bsc_exchange_date_type, 'CFD', cf.cashflow_date, 'BSG', sh.gl_date, 'CLD', ah.cleared_date, 'BSD', sh.statement_date, 'CFE', cf.cashflow_exchange_date), 'BAT', Decode(sys.bat_exchange_date_type, 'CFD', cf.cashflow_date, 'AVD', cf.actual_value_date, 'CLD', ah.cleared_date, 'BSD', sh.statement_date, 'BSG', sh.gl_date, 'SLD', sl.trx_date, 'TRX', trx.transaction_date), cf.cashflow_exchange_date), Decode(cf.cashflow_currency_code, gl.currency_code, NULL, sys.cashflow_exchange_rate_type), cf.trxn_reference_number, cf.bank_trxn_number, cf.source_trxn_type, tstc.transaction_sub_type_name, cf.actual_value_date, lecp.NAME, bacp.bank_account_num, cf.offset_ccid, cf.cashflow_status_code, ah.cleared_date, ah.cleared_amount, Decode(bacf.currency_code, gl.currency_code, To_number(NULL), ah.cleared_exchange_rate), ah.cleared_exchange_date, Decode(bacf.currency_code, gl.currency_code, To_char(NULL), ah.cleared_exchange_rate_type), Decode(ah.clearing_charges_amount, 0, To_number(NULL), ah.clearing_charges_amount), Decode(bacf.recon_ce_foreign_diff_handling, /*sys.FOREIGN_DIFFERENCE_HANDLING*/ 'F', To_number(NULL), Decode(ah.clearing_error_amount, 0, To_number(NULL) , ah.clearing_error_amount)), Decode(bacf.recon_ce_foreign_diff_handling, /*sys.FOREIGN_DIFFERENCE_HANDLING*/ 'F', Decode(ah.clearing_error_amount, 0, To_number(NULL), ah.clearing_error_amount), To_number(NULL) ), lecf.legal_entity_identifier, lecf.registration_number, lecf.address_line_1, lecf.address_line_2, lecf.address_line_3, lecf.town_or_city, lecf.postal_code, lecf.country, bbcf.bank_name, bbcf.bank_name_alt, bbcf.short_bank_name, bbcf.bank_number, bbcf.bank_branch_name, bbcf.bank_branch_name_alt, bbcf.branch_number, bbcf.address_line1, bbcf.address_line2, bbcf.address_line3, bbcf.address_line4, bbcf.city, bbcf.state, bbcf.province, bbcf.zip, bbcf.country, bbcf.description, bbcf.eft_swift_code, bbcp.bank_name, bbcp.bank_number, bbcp.bank_branch_name, bbcp.branch_number, bacf.bank_account_name, bacf.currency_code, bacf.description, bacf.attribute_category, bacf.attribute1, bacf.attribute2, bacf.attribute3, bacf.attribute4, bacf.attribute5, bacf.attribute6, bacf.attribute7, bacf.attribute8, bacf.attribute9, bacf.attribute10, bacf.attribute11, bacf.attribute12, bacf.attribute13, bacf.attribute14, bacf.attribute15, bacf.asset_code_combination_id, bacf.cash_clearing_ccid, bacf.bank_charges_ccid, bacf.bank_errors_ccid, bacf.fx_charge_ccid, bacf.gain_code_combination_id, bacf.loss_code_combination_id, bacf.bank_account_name_alt, bacf.iban_number, bacf.short_account_name, bacf.multi_currency_allowed_flag, bacf.account_holder_name_alt, bacf.account_holder_name, bacp.bank_account_name, bacp.currency_code, bacp.iban_number, sh.statement_number, sh.statement_date, sh.doc_sequence_id, sh.doc_sequence_value, sh.gl_date, trx.transaction_date, trx.settle_by_system_flag, trx.trxn_status_code, trx.attribute_category, trx.attribute1, trx.attribute2, trx.attribute3, trx.attribute4, trx.attribute5, trx.attribute6, trx.attribute7, trx.attribute8, trx.attribute9, trx.attribute10, trx.attribute11, trx.attribute12, trx.attribute13, trx.attribute14, trx.attribute15, ah.cleared_exchange_rate, ah.cleared_exchange_rate_type FROM ce_cashflows cf, ce_cashflow_acct_h ah, xle_firstparty_information_v lecf, xle_firstparty_information_v lecp, xle_firstparty_information_v lesrc, xle_firstparty_information_v ledes, ce_bank_accounts bacf, ce_bank_accounts bacp, ce_bank_branches_v bbcf, ce_bank_branches_v bbcp, ce_statement_lines sl, ce_statement_headers sh, gl_ledgers gl, ce_trxns_subtype_codes tstc, ce_payment_transactions trx, hz_party_sites ps, ce_system_parameters sys WHERE cf.cashflow_id = ah.cashflow_id AND cf.cashflow_bank_account_id = bacf.bank_account_id AND bacf.bank_branch_id = bbcf.branch_party_id AND cf.cashflow_legal_entity_id = lecf.legal_entity_id AND cf.cashflow_ledger_id = gl.ledger_id AND bacp.bank_account_id(+) = cf.counterparty_bank_account_id AND bacp.bank_branch_id = bbcp.branch_party_id(+) AND tstc.trxn_subtype_code_id(+) = cf.source_trxn_subtype_code_id AND cf.counterparty_party_id = lecp.party_id(+) AND sl.statement_line_id(+) = cf.statement_line_id AND sl.statement_header_id = sh.statement_header_id(+) AND trx.trxn_reference_number(+) = cf.trxn_reference_number AND trx.source_party_id = lesrc.party_id(+) AND trx.destination_party_id = ledes.party_id(+) AND trx.destination_party_site_id = ps.party_site_id(+) AND sys.legal_entity_id = cf.cashflow_legal_entity_id

View Text - HTML Formatted

SELECT AH.EVENT_ID
, CF.CASHFLOW_ID
, ROWIDTOCHAR(AH.ROWID)
, 'CE_TRANS'
, AH.EVENT_TYPE
, AH.ACCOUNTING_DATE
, AH.STATUS_CODE
, AH.REVERSAL_INDICATOR_FLAG
, 'CE_TRANS'
, AH.REVERSAL_FIRST_DIST_ID
, AH.REVERSAL_SECOND_DIST_NUM
, GL.NAME
, LECF.NAME
, BACF.BANK_ACCOUNT_NUM
, DECODE(CF.CASHFLOW_DIRECTION
, 'DEBIT'
, 'PAYMENT'
, 'CREDIT'
, 'RECEIPT'
, CF.CASHFLOW_DIRECTION)
, CF.CASHFLOW_CURRENCY_CODE
, CF.CASHFLOW_DATE
, CF.CASHFLOW_AMOUNT
, DECODE(CF.CASHFLOW_CURRENCY_CODE
, BACF.CURRENCY_CODE
, CF.CASHFLOW_AMOUNT
, CF.BASE_AMOUNT)
, SUBSTRB(CF.DESCRIPTION
, 1
, 240)
, CF.REFERENCE_TEXT
, DECODE(CEP_STANDARD.GET_CONVERSION_RATE(CF.CASHFLOW_LEDGER_ID
, CF.CASHFLOW_CURRENCY_CODE
, DECODE(CF.SOURCE_TRXN_TYPE
, 'STMT'
, DECODE(SYS.BSC_EXCHANGE_DATE_TYPE
, 'CFD'
, CF.CASHFLOW_DATE
, 'BSG'
, SH.GL_DATE
, 'CLD'
, AH.CLEARED_DATE
, 'BSD'
, SH.STATEMENT_DATE
, 'CFE'
, CF.CASHFLOW_EXCHANGE_DATE)
, 'BAT'
, DECODE(SYS.BAT_EXCHANGE_DATE_TYPE
, 'CFD'
, CF.CASHFLOW_DATE
, 'AVD'
, CF.ACTUAL_VALUE_DATE
, 'CLD'
, AH.CLEARED_DATE
, 'BSD'
, SH.STATEMENT_DATE
, 'BSG'
, SH.GL_DATE
, 'SLD'
, SL.TRX_DATE
, 'TRX'
, TRX.TRANSACTION_DATE)
, CF.CASHFLOW_EXCHANGE_DATE)
, SYS.CASHFLOW_EXCHANGE_RATE_TYPE)
, 0
, TO_NUMBER(NULL))
, DECODE(CF.SOURCE_TRXN_TYPE
, 'STMT'
, DECODE(SYS.BSC_EXCHANGE_DATE_TYPE
, 'CFD'
, CF.CASHFLOW_DATE
, 'BSG'
, SH.GL_DATE
, 'CLD'
, AH.CLEARED_DATE
, 'BSD'
, SH.STATEMENT_DATE
, 'CFE'
, CF.CASHFLOW_EXCHANGE_DATE)
, 'BAT'
, DECODE(SYS.BAT_EXCHANGE_DATE_TYPE
, 'CFD'
, CF.CASHFLOW_DATE
, 'AVD'
, CF.ACTUAL_VALUE_DATE
, 'CLD'
, AH.CLEARED_DATE
, 'BSD'
, SH.STATEMENT_DATE
, 'BSG'
, SH.GL_DATE
, 'SLD'
, SL.TRX_DATE
, 'TRX'
, TRX.TRANSACTION_DATE)
, CF.CASHFLOW_EXCHANGE_DATE)
, DECODE(CF.CASHFLOW_CURRENCY_CODE
, GL.CURRENCY_CODE
, NULL
, SYS.CASHFLOW_EXCHANGE_RATE_TYPE)
, CF.TRXN_REFERENCE_NUMBER
, CF.BANK_TRXN_NUMBER
, CF.SOURCE_TRXN_TYPE
, TSTC.TRANSACTION_SUB_TYPE_NAME
, CF.ACTUAL_VALUE_DATE
, LECP.NAME
, BACP.BANK_ACCOUNT_NUM
, CF.OFFSET_CCID
, CF.CASHFLOW_STATUS_CODE
, AH.CLEARED_DATE
, AH.CLEARED_AMOUNT
, DECODE(BACF.CURRENCY_CODE
, GL.CURRENCY_CODE
, TO_NUMBER(NULL)
, AH.CLEARED_EXCHANGE_RATE)
, AH.CLEARED_EXCHANGE_DATE
, DECODE(BACF.CURRENCY_CODE
, GL.CURRENCY_CODE
, TO_CHAR(NULL)
, AH.CLEARED_EXCHANGE_RATE_TYPE)
, DECODE(AH.CLEARING_CHARGES_AMOUNT
, 0
, TO_NUMBER(NULL)
, AH.CLEARING_CHARGES_AMOUNT)
, DECODE(BACF.RECON_CE_FOREIGN_DIFF_HANDLING
, /*SYS.FOREIGN_DIFFERENCE_HANDLING*/ 'F'
, TO_NUMBER(NULL)
, DECODE(AH.CLEARING_ERROR_AMOUNT
, 0
, TO_NUMBER(NULL)
, AH.CLEARING_ERROR_AMOUNT))
, DECODE(BACF.RECON_CE_FOREIGN_DIFF_HANDLING
, /*SYS.FOREIGN_DIFFERENCE_HANDLING*/ 'F'
, DECODE(AH.CLEARING_ERROR_AMOUNT
, 0
, TO_NUMBER(NULL)
, AH.CLEARING_ERROR_AMOUNT)
, TO_NUMBER(NULL) )
, LECF.LEGAL_ENTITY_IDENTIFIER
, LECF.REGISTRATION_NUMBER
, LECF.ADDRESS_LINE_1
, LECF.ADDRESS_LINE_2
, LECF.ADDRESS_LINE_3
, LECF.TOWN_OR_CITY
, LECF.POSTAL_CODE
, LECF.COUNTRY
, BBCF.BANK_NAME
, BBCF.BANK_NAME_ALT
, BBCF.SHORT_BANK_NAME
, BBCF.BANK_NUMBER
, BBCF.BANK_BRANCH_NAME
, BBCF.BANK_BRANCH_NAME_ALT
, BBCF.BRANCH_NUMBER
, BBCF.ADDRESS_LINE1
, BBCF.ADDRESS_LINE2
, BBCF.ADDRESS_LINE3
, BBCF.ADDRESS_LINE4
, BBCF.CITY
, BBCF.STATE
, BBCF.PROVINCE
, BBCF.ZIP
, BBCF.COUNTRY
, BBCF.DESCRIPTION
, BBCF.EFT_SWIFT_CODE
, BBCP.BANK_NAME
, BBCP.BANK_NUMBER
, BBCP.BANK_BRANCH_NAME
, BBCP.BRANCH_NUMBER
, BACF.BANK_ACCOUNT_NAME
, BACF.CURRENCY_CODE
, BACF.DESCRIPTION
, BACF.ATTRIBUTE_CATEGORY
, BACF.ATTRIBUTE1
, BACF.ATTRIBUTE2
, BACF.ATTRIBUTE3
, BACF.ATTRIBUTE4
, BACF.ATTRIBUTE5
, BACF.ATTRIBUTE6
, BACF.ATTRIBUTE7
, BACF.ATTRIBUTE8
, BACF.ATTRIBUTE9
, BACF.ATTRIBUTE10
, BACF.ATTRIBUTE11
, BACF.ATTRIBUTE12
, BACF.ATTRIBUTE13
, BACF.ATTRIBUTE14
, BACF.ATTRIBUTE15
, BACF.ASSET_CODE_COMBINATION_ID
, BACF.CASH_CLEARING_CCID
, BACF.BANK_CHARGES_CCID
, BACF.BANK_ERRORS_CCID
, BACF.FX_CHARGE_CCID
, BACF.GAIN_CODE_COMBINATION_ID
, BACF.LOSS_CODE_COMBINATION_ID
, BACF.BANK_ACCOUNT_NAME_ALT
, BACF.IBAN_NUMBER
, BACF.SHORT_ACCOUNT_NAME
, BACF.MULTI_CURRENCY_ALLOWED_FLAG
, BACF.ACCOUNT_HOLDER_NAME_ALT
, BACF.ACCOUNT_HOLDER_NAME
, BACP.BANK_ACCOUNT_NAME
, BACP.CURRENCY_CODE
, BACP.IBAN_NUMBER
, SH.STATEMENT_NUMBER
, SH.STATEMENT_DATE
, SH.DOC_SEQUENCE_ID
, SH.DOC_SEQUENCE_VALUE
, SH.GL_DATE
, TRX.TRANSACTION_DATE
, TRX.SETTLE_BY_SYSTEM_FLAG
, TRX.TRXN_STATUS_CODE
, TRX.ATTRIBUTE_CATEGORY
, TRX.ATTRIBUTE1
, TRX.ATTRIBUTE2
, TRX.ATTRIBUTE3
, TRX.ATTRIBUTE4
, TRX.ATTRIBUTE5
, TRX.ATTRIBUTE6
, TRX.ATTRIBUTE7
, TRX.ATTRIBUTE8
, TRX.ATTRIBUTE9
, TRX.ATTRIBUTE10
, TRX.ATTRIBUTE11
, TRX.ATTRIBUTE12
, TRX.ATTRIBUTE13
, TRX.ATTRIBUTE14
, TRX.ATTRIBUTE15
, AH.CLEARED_EXCHANGE_RATE
, AH.CLEARED_EXCHANGE_RATE_TYPE
FROM CE_CASHFLOWS CF
, CE_CASHFLOW_ACCT_H AH
, XLE_FIRSTPARTY_INFORMATION_V LECF
, XLE_FIRSTPARTY_INFORMATION_V LECP
, XLE_FIRSTPARTY_INFORMATION_V LESRC
, XLE_FIRSTPARTY_INFORMATION_V LEDES
, CE_BANK_ACCOUNTS BACF
, CE_BANK_ACCOUNTS BACP
, CE_BANK_BRANCHES_V BBCF
, CE_BANK_BRANCHES_V BBCP
, CE_STATEMENT_LINES SL
, CE_STATEMENT_HEADERS SH
, GL_LEDGERS GL
, CE_TRXNS_SUBTYPE_CODES TSTC
, CE_PAYMENT_TRANSACTIONS TRX
, HZ_PARTY_SITES PS
, CE_SYSTEM_PARAMETERS SYS
WHERE CF.CASHFLOW_ID = AH.CASHFLOW_ID
AND CF.CASHFLOW_BANK_ACCOUNT_ID = BACF.BANK_ACCOUNT_ID
AND BACF.BANK_BRANCH_ID = BBCF.BRANCH_PARTY_ID
AND CF.CASHFLOW_LEGAL_ENTITY_ID = LECF.LEGAL_ENTITY_ID
AND CF.CASHFLOW_LEDGER_ID = GL.LEDGER_ID
AND BACP.BANK_ACCOUNT_ID(+) = CF.COUNTERPARTY_BANK_ACCOUNT_ID
AND BACP.BANK_BRANCH_ID = BBCP.BRANCH_PARTY_ID(+)
AND TSTC.TRXN_SUBTYPE_CODE_ID(+) = CF.SOURCE_TRXN_SUBTYPE_CODE_ID
AND CF.COUNTERPARTY_PARTY_ID = LECP.PARTY_ID(+)
AND SL.STATEMENT_LINE_ID(+) = CF.STATEMENT_LINE_ID
AND SL.STATEMENT_HEADER_ID = SH.STATEMENT_HEADER_ID(+)
AND TRX.TRXN_REFERENCE_NUMBER(+) = CF.TRXN_REFERENCE_NUMBER
AND TRX.SOURCE_PARTY_ID = LESRC.PARTY_ID(+)
AND TRX.DESTINATION_PARTY_ID = LEDES.PARTY_ID(+)
AND TRX.DESTINATION_PARTY_SITE_ID = PS.PARTY_SITE_ID(+)
AND SYS.LEGAL_ENTITY_ID = CF.CASHFLOW_LEGAL_ENTITY_ID