CE_XT_AVAILABLE_TRX_V

Details

• Schema: FUSION

• Object owner: CE

• Object type: VIEW

Columns

Name

TRANSACTION_SOURCE TRANSACTION_ID BANK_ACCOUNT_ID BA_CURRENCY_CODE TRX_CURRENCY_CODE RECON_MATCH_REFERENCE RECON_MATCH_AMOUNT RECON_MATCH_EXCHANGE_RATE RECON_MATCH_DATE RECON_MATCH_TYPE RECON_MATCH_TYPE_CODE TRX_SUBTYPE TRX_DATE TRX_AMOUNT TRANSACTION_NUMBER CLEARED_DATE PAY_RECEIPT_METHOD RECEIPT_CLASS_NAME STATUS CPARTY_NAME CPARTY_SITE CPARTY_BANK_ACCOUNT_ID PAYMENT_INSTRUCTION_ID PAYMENT_REFERENCE_ID PAYMENT_LOGICAL_GROUP_ID BATCH_NAME RECEIPT_BATCH_NAME BANK_DEPOSIT_NUMBER BUSINESS_UNIT_ID LEGAL_ENTITY_ID RECON_FLAG RECON_HISTORY_ID EXTERNAL_TRX_SOURCE TRX_EXCHANGE_RATE TRX_EXCHANGE_RATE_TYPE TRX_EXCHANGE_DATE ACCOUNTING_FLAG ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE15

Query

SQL_Statement

select 'XT' transaction_source, ce_xt.external_transaction_id transaction_id, ce_xt.bank_account_id bank_account_id, ce_xt.currency_code ba_currency_code, ce_xt.currency_code trx_currency_code, ce_xt.reference_text recon_match_reference, ce_xt.amount recon_match_amount, null recon_match_exchange_rate, trunc(nvl(ce_xt.value_date, ce_xt.transaction_date)) recon_match_date, lookup.meaning recon_match_type, ce_xt.transaction_type recon_match_type_code, ce_xt.transaction_subtype trx_subtype, trunc(ce_xt.transaction_date) trx_date, ce_xt.amount trx_amount, to_char(ce_xt.transaction_id) transaction_number, null cleared_date, ce_xt.transaction_type pay_receipt_method, null receipt_class_name, ce_xt.status status, null cparty_name, null cparty_site, null cparty_bank_account_id, null payment_instruction_id, null payment_reference_id, null payment_logical_group_id, null batch_name, null receipt_batch_name, null bank_deposit_number, ce_xt.business_unit_id business_unit_id, ce_xt.legal_entity_id legal_entity_id, 'N' recon_flag, ce_xt.recon_history_id recon_history_id, ce_xt.source external_trx_source, null trx_exchange_rate, null trx_exchange_rate_type, null trx_exchange_date, ce_xt.accounting_flag accounting_flag, ce_xt.attribute_category, ce_xt.attribute1, ce_xt.attribute2, ce_xt.attribute3, ce_xt.attribute4, ce_xt.attribute5, ce_xt.attribute6, ce_xt.attribute7, ce_xt.attribute8, ce_xt.attribute9, ce_xt.attribute10, ce_xt.attribute11, ce_xt.attribute12, ce_xt.attribute13, ce_xt.attribute14, ce_xt.attribute15, null global_attribute_category, null global_attribute1, null global_attribute2, null global_attribute3, null global_attribute4, null global_attribute5, null global_attribute6, null global_attribute7, null global_attribute8, null global_attribute9, null global_attribute10, null global_attribute11, null global_attribute12, null global_attribute13, null global_attribute14, null global_attribute15 from ce_external_transactions ce_xt, CE_LOOKUPS lookup, ce_bank_accounts ce_ba where ce_xt.status = 'UNR' and ce_xt.bank_account_id = ce_ba.bank_account_id and TRUNC(nvl(ce_xt.value_date, transaction_date)) >= TRUNC(nvl(ce_ba.recon_start_date, to_date('1970-01-01', 'YYYY-MM-DD'))) and ce_xt.TRANSACTION_TYPE = lookup.LOOKUP_CODE(+) AND lookup.LOOKUP_TYPE(+) = 'CE_TRX_TYPE'