CE_PR_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 'PR' transaction_source, ce_pr.payroll_rel_action_id transaction_id, ce_pr.payer_bank_account_id bank_account_id, ce_ba.currency_code ba_currency_code, ce_pr.currency_code trx_currency_code, ce_pr.check_number recon_match_reference, -decode(ce_pr.currency_code, ce_ba.currency_code, ce_pr.amount, CE_RECON_UTIL.get_converted_amount (ce_pr.CURRENCY_CODE, ce_ba.currency_code, decode(ce_ba.bank_exchange_rate_type, null, null, CE_RECON_UTIL.get_rate(ce_pr.CURRENCY_CODE, ce_ba.currency_code, ce_pr.payment_date, ce_ba.bank_exchange_rate_type)), ce_pr.AMOUNT)) recon_match_amount, decode(ce_pr.currency_code, ce_ba.currency_code, null, decode(ce_ba.bank_exchange_rate_type, null, null, CE_RECON_UTIL.get_rate(ce_pr.CURRENCY_CODE, ce_ba.currency_code, ce_pr.payment_date, ce_ba.bank_exchange_rate_type))) recon_match_exchange_rate, trunc(ce_pr.payment_date) recon_match_date, lookup.meaning recon_match_type, ce_trx.trx_type recon_match_type_code, null trx_subtype, trunc(ce_pr.payment_date) trx_date, ce_pr.amount trx_amount, ce_pr.check_number transaction_number, trunc(ce_pr.cleared_date) cleared_date, to_char(ce_pr.org_payment_method_id) pay_receipt_method, null receipt_class_name, ce_pr.payment_status status, null cparty_name, null cparty_site, ce_pr.payee_bank_account_id cparty_bank_account_id, null payment_instruction_id, null payment_reference_id, null payment_logical_group_id, pfi.instance_name batch_name, null receipt_batch_name, null bank_deposit_number, null business_unit_id, ce_ba.account_owner_org_id legal_entity_id, 'N' recon_flag, null recon_history_id, null external_trx_source, null trx_exchange_rate, null trx_exchange_rate_type, null trx_exchange_date, null accounting_flag, null attribute_category, null attribute1, null attribute2, null attribute3, null attribute4, null attribute5, null attribute6, null attribute7, null attribute8, null attribute9, null attribute10, null attribute11, null attribute12, null attribute13, null attribute14, null 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 pay_ce_transactions ce_pr, ce_bank_accounts ce_ba, CE_LOOKUPS lookup, ce_trx_type_mapping ce_trx, pay_payroll_actions ppa, (select distinct pr.pay_request_id, pfi1.instance_name from pay_requests pr, pay_flow_instances pfi1 where pr.flow_instance_id = pfi1.flow_instance_id) pfi where ce_pr.recon_flag <> 'Y' and ce_pr.payment_status = 'PAID' and ce_ba.bank_account_id = ce_pr.payer_bank_account_id and ce_pr.payment_date >= TRUNC(nvl(ce_ba.recon_start_date, to_date('1970-01-01', 'YYYY-MM-DD'))) and ce_trx.pay_payment_type_id(+) = ce_pr.payment_type_id and nvl(ce_trx.active_flag (+), 'Y') = 'Y' and ce_trx.trx_type = lookup.LOOKUP_CODE(+) and lookup.LOOKUP_TYPE(+) = 'CE_TRX_TYPE' and decode(ce_pr.CURRENCY_CODE, ce_ba.currency_code, 1, decode(ce_ba.bank_exchange_rate_type, null, null, CE_RECON_UTIL.get_rate(ce_pr.CURRENCY_CODE, ce_ba.currency_code, ce_pr.payment_date, ce_ba.bank_exchange_rate_type))) is not null and ce_pr.payroll_action_id = ppa.payroll_action_id and ppa.pay_request_id = pfi.pay_request_id(+)