CE_AR_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 STRUCTURED_PAYMENT_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 PAYMENT_SERVER_ORDER_NUMBER 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 'AR' as transaction_source , ar_r.cash_receipt_id as transaction_id , ce_ba.bank_account_id as bank_account_id , ce_ba.currency_code as ba_currency_code , ar_r.currency_code as trx_currency_code , ar_r.receipt_number as recon_match_reference , ar_r.structured_payment_reference as structured_payment_reference , decode(ar_r.currency_code, ce_ba.currency_code, decode(ar_crh.status, 'CLEARED', ar_crh.amount, ar_crh.amount + nvl(ar_crh.factor_discount_amount,0)) , ce_recon_util.get_converted_amount( ar_r.currency_code , ce_ba.currency_code , nvl(ce_recon_util.get_rate(ar_r.currency_code, ce_ba.currency_code, nvl(ar_r.deposit_date, ar_r.receipt_date), ce_ba.bank_exchange_rate_type) , nvl(ar_r.exchange_rate , ce_recon_util.get_rate(ar_r.currency_code, ce_ba.currency_code, ar_r.exchange_date, ar_r.exchange_rate_type))) , decode(ar_crh.status, 'CLEARED', ar_crh.amount, ar_crh.amount + nvl(ar_crh.factor_discount_amount,0))) ) as recon_match_amount , decode(ar_r.currency_code, ce_ba.currency_code, null , nvl(ce_recon_util.get_rate(ar_r.currency_code, ce_ba.currency_code, nvl(ar_r.deposit_date, ar_r.receipt_date), ce_ba.bank_exchange_rate_type) , nvl(ar_r.exchange_rate , ce_recon_util.get_rate(ar_r.currency_code, ce_ba.currency_code, ar_r.exchange_date, ar_r.exchange_rate_type))) ) recon_match_exchange_rate , trunc(decode(ar_crh.status, 'CLEARED', ar_crh.trx_date, NVL(ar_r.deposit_date, ar_r.receipt_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 , ar_r.receipt_date as trx_date , decode(ar_crh.status, 'CLEARED', ar_crh.amount, ar_crh.amount + nvl(ar_crh.factor_discount_amount,0)) as trx_amount , ar_r.receipt_number as transaction_number , trunc(decode(ar_crh.status, 'CLEARED', ar_crh.trx_date)) as cleared_date , to_char(ar_m.receipt_method_id) as pay_receipt_method , ar_rc.name as receipt_class_name , ar_crh.status as status , hz_cust.party_name as cparty_name , null as cparty_site , ar_r.customer_bank_account_id as cparty_bank_account_id , null as payment_instruction_id , null as payment_reference_id , iby_summ.logical_group_reference as logical_group_reference , iby_summ.tangibleid as payment_server_order_number , null as payment_logical_group_id , ar_b.name as batch_name , lockbox.name as receipt_batch_name , ar_b.bank_deposit_number as bank_deposit_number , ar_r.org_id as business_unit_id , ce_ba.account_owner_org_id as legal_entity_id , ar_r.recon_flag as recon_flag , null as recon_history_id , null as external_trx_source , ar_r.exchange_rate as trx_exchange_rate , ar_r.exchange_rate_type as trx_exchange_rate_type , ar_r.exchange_date as trx_exchange_date , null as accounting_flag , ar_r.attribute_category, ar_r.attribute1, ar_r.attribute2, ar_r.attribute3 , ar_r.attribute4, ar_r.attribute5, ar_r.attribute6, ar_r.attribute7 , ar_r.attribute8, ar_r.attribute9, ar_r.attribute10, ar_r.attribute11 , ar_r.attribute12, ar_r.attribute13, ar_r.attribute14, ar_r.attribute15 , ar_r.global_attribute_category, ar_r.global_attribute1, ar_r.global_attribute2 , ar_r.global_attribute3, ar_r.global_attribute4, ar_r.global_attribute5 , ar_r.global_attribute6, ar_r.global_attribute7, ar_r.global_attribute8 , ar_r.global_attribute9, ar_r.global_attribute10, ar_r.global_attribute11 , ar_r.global_attribute12, ar_r.global_attribute13, ar_r.global_attribute14 , ar_r.global_attribute15 from ar_cash_receipts_all ar_r , ar_cash_receipt_history_all ar_crh , ce_bank_acct_uses_all ce_bau , ce_bank_accounts ce_ba , hz_cust_accounts hz_ca , hz_parties hz_cust , ar_batches_all ar_b , ar_batches_all lockbox , ar_receipt_methods ar_m , ar_receipt_classes ar_rc , ce_trx_type_mapping ce_ttm , ce_lookups ce_ttl , iby_extn_summ_settles_v iby_sttl , iby_trxn_summaries_all iby_summ where ar_crh.org_id = ar_r.org_id and ar_crh.status in ('CLEARED', 'REMITTED', 'RISK_ELIMINATED') and ar_crh.current_record_flag = 'Y' and ar_crh.trx_date >= trunc(nvl(ce_ba.recon_start_date, to_date('1970-01-01', 'YYYY-MM-DD'))) and ar_crh.cash_receipt_id = ar_r.cash_receipt_id and ar_r.receipt_date >= trunc(nvl(ce_ba.recon_start_date, to_date('1970-01-01', 'YYYY-MM-DD'))) and ar_r.recon_flag = 'N' and nvl(ar_r.status, 'X') = decode(nvl(ar_r.status, 'X'),'REV','UNAPP', nvl(ar_r.status, 'X')) and ce_bau.org_id = ar_r.org_id and ce_bau.bank_acct_use_id = ar_r.remit_bank_acct_use_id and ce_bau.ar_use_enable_flag = 'Y' and ce_ba.bank_account_id = ce_bau.bank_account_id and ar_m.receipt_method_id = ar_r.receipt_method_id and to_char(ar_r.receipt_method_id) = ce_ttm.pmt_rct_method(+) 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' and ar_rc.receipt_class_id = ar_m.receipt_class_id and hz_ca.cust_account_id(+) = ar_r.pay_from_customer and hz_cust.party_id(+) = hz_ca.party_id and ar_b.org_id(+) = ar_r.org_id and ar_b.batch_id(+) = ar_r.remittance_batch_id and lockbox.batch_id(+) = ar_r.receipt_batch_id and lockbox.type(+) = 'MANUAL' and iby_sttl.trxn_extension_id(+) = ar_r.payment_trxn_extension_id and iby_summ.trxnmid(+) = iby_sttl.trxnmid and not exists (select 1 from iby_extn_summ_returns_v summ where summ.trxn_extension_id = ar_r.payment_trxn_extension_id and nvl(summ.returned_status, 'NONE') <> 'RETURN_SUCCESS' and ar_r.amount < 0) and not exists (select 1 from iby_extn_summ_settles_v sett where sett.trxn_extension_id = ar_r.payment_trxn_extension_id and nvl(sett.settlement_status,'NONE') <> 'SETTLEMENT_SUCCESS' and ar_r.amount >= 0)