CE_AP_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 LOGICAL_GROUP_REFERENCE UNIQUE_REMITTANCE_IDENTIFIER PAYMENT_LOGICAL_GROUP_ID END_TO_END_IDENTIFIER 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 PAYMENT_FILE_REFERENCE PAYMENT_PROCESS_REQUEST_NAME 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 'AP' as transaction_source , ap_c.check_id as transaction_id , ce_ba.bank_account_id as bank_account_id , ce_ba.currency_code as ba_currency_code , ap_c.currency_code as trx_currency_code , to_char(ap_c.check_number) as recon_match_reference , -1 * ( case ce_recon_util.is_multi_curr_forex_acct_allowed when 'N' then decode(ap_c.currency_code, ce_ba.currency_code, ap_c.amount , ce_recon_util.get_converted_amount( ap_c.currency_code , ce_ba.currency_code , nvl(ce_recon_util.get_rate(ap_c.currency_code, ce_ba.currency_code, ap_c.check_date, ce_ba.bank_exchange_rate_type) , nvl(ap_c.exchange_rate , ce_recon_util.get_rate(ap_c.currency_code, ce_ba.currency_code, ap_c.exchange_date, ap_c.exchange_rate_type))) , ap_c.amount) ) else decode(ap_c.currency_code, ce_ba.currency_code, ap_c.amount , ce_recon_util.get_converted_amount( ap_c.currency_code , ce_ba.currency_code , nvl(ce_recon_util.get_rate(ap_c.currency_code, ce_ba.currency_code, ap_c.check_date, ce_ba.bank_exchange_rate_type) , nvl(ap_c.pmt_to_bank_rate , nvl( ce_recon_util.get_rate(ap_c.currency_code, ce_ba.currency_code, ap_c.check_date, ap_c.pmt_to_bank_rate_type) , ce_recon_util.get_rate(ap_c.currency_code, ce_ba.currency_code, ap_c.exchange_date, ap_c.exchange_rate_type)))) , ap_c.amount) ) end ) as recon_match_amount , ( case ce_recon_util.is_multi_curr_forex_acct_allowed when 'N' then decode(ap_c.currency_code, ce_ba.currency_code, null , nvl(ce_recon_util.get_rate(ap_c.currency_code, ce_ba.currency_code, ap_c.check_date, ce_ba.bank_exchange_rate_type) , nvl(ap_c.exchange_rate , ce_recon_util.get_rate(ap_c.currency_code, ce_ba.currency_code, ap_c.exchange_date, ap_c.exchange_rate_type))) ) else decode(ap_c.currency_code, ce_ba.currency_code, null , nvl(ce_recon_util.get_rate(ap_c.currency_code, ce_ba.currency_code, ap_c.check_date, ce_ba.bank_exchange_rate_type) , nvl(ap_c.pmt_to_bank_rate, nvl(ce_recon_util.get_rate(ap_c.currency_code, ce_ba.currency_code, ap_c.check_date, ap_c.pmt_to_bank_rate_type) , ce_recon_util.get_rate(ap_c.currency_code, ce_ba.currency_code, ap_c.exchange_date, ap_c.exchange_rate_type)))) ) end ) as recon_match_exchange_rate , trunc(ap_c.check_date) as recon_match_date , ce_ttl.meaning as recon_match_type , ce_ttm.trx_type as recon_match_type_code , null as trx_subtype , trunc(ap_c.check_date) as trx_date , ap_c.amount as trx_amount , to_char(ap_c.check_number) as transaction_number , trunc(ap_c.cleared_date) as cleared_date , ap_c.payment_method_code as pay_receipt_method , null as receipt_class_name , ap_c.status_lookup_code as status , ap_c.vendor_name as cparty_name , ap_c.vendor_site_code as cparty_site , ap_c.external_bank_account_id as cparty_bank_account_id , ap_c.payment_instruction_id as payment_instruction_id , ap_c.payment_id as payment_reference_id , iby_pa.logical_group_reference as logical_group_reference , iby_pa.unique_remittance_identifier as unique_remittance_identifier , null as payment_logical_group_id , iby_pa.payment_reference_number as end_to_end_identifier , decode(ap_c.payment_type_flag, 'A', ap_c.checkrun_name) as batch_name , null as receipt_batch_name , null as bank_deposit_number , ap_c.org_id business_unit_id , ce_ba.account_owner_org_id as legal_entity_id , ap_c.recon_flag as recon_flag , null as recon_history_id , null as external_trx_source , ( case ce_recon_util.is_multi_curr_forex_acct_allowed when 'N' then ap_c.exchange_rate else ap_c.pmt_to_bank_rate end ) as trx_exchange_rate , ( case ce_recon_util.is_multi_curr_forex_acct_allowed when 'N' then ap_c.exchange_rate_type else ap_c.pmt_to_bank_rate_type end ) as trx_exchange_rate_type , ( case ce_recon_util.is_multi_curr_forex_acct_allowed when 'N' then ap_c.exchange_date else ap_c.check_date end ) as trx_exchange_date , null as accounting_flag , to_char(ap_c.payment_instruction_id) as payment_file_reference , decode(ap_c.payment_type_flag, 'A', ap_c.checkrun_name) as payment_process_request_name , ap_c.attribute_category, ap_c.attribute1, ap_c.attribute2, ap_c.attribute3, ap_c.attribute4 , ap_c.attribute5, ap_c.attribute6, ap_c.attribute7, ap_c.attribute8, ap_c.attribute9 , ap_c.attribute10, ap_c.attribute11, ap_c.attribute12, ap_c.attribute13, ap_c.attribute14 , ap_c.attribute15 , ap_c.global_attribute_category, ap_c.global_attribute1, ap_c.global_attribute2, ap_c.global_attribute3 , ap_c.global_attribute4, ap_c.global_attribute5, ap_c.global_attribute6, ap_c.global_attribute7 , ap_c.global_attribute8, ap_c.global_attribute9, ap_c.global_attribute10, ap_c.global_attribute11 , ap_c.global_attribute12, ap_c.global_attribute13, ap_c.global_attribute14, ap_c.global_attribute15 from ap_checks_all ap_c , ce_bank_acct_uses_all ce_bau , ce_bank_accounts ce_ba , iby_payments_all iby_pa , ce_lookups ce_ttl , ce_trx_type_mapping ce_ttm where ce_bau.org_id = ap_c.org_id and ce_bau.bank_acct_use_id = ap_c.ce_bank_acct_use_id and ce_bau.ap_use_enable_flag = 'Y' and ce_ba.bank_account_id = ce_bau.bank_account_id and iby_pa.payment_id(+) = ap_c.payment_id and ap_c.status_lookup_code in ('NEGOTIABLE', 'CLEARED') and ap_c.check_date >= trunc(nvl(ce_ba.recon_start_date, to_date('1970-01-01', 'YYYY-MM-DD'))) and ap_c.recon_flag = 'N' and ce_ttm.pmt_rct_method(+) = ap_c.payment_method_code and nvl(ce_ttm.active_flag (+), 'Y') = 'Y' and ce_ttm.trx_type = ce_ttl.lookup_code(+) and ce_ttl.lookup_type(+) = 'CE_TRX_TYPE'