CE Transactions Available for Reconciliation
Description
Categories: Enginatics
Repository: Github
Repository: Github
Application: Cash Management
Description: Transactions - Transactions Available for Reconciliation
Provides equivalent functionality to the following standard Oracle Forms/Reports
- Transactions Available for Reconciliation Report
Applicable Templates:
Pivot: Transactions Available by Bank Account, Source and Type
Source: Transactions Available for Reconciliation Repo ... more
Description: Transactions - Transactions Available for Reconciliation
Provides equivalent functionality to the following standard Oracle Forms/Reports
- Transactions Available for Reconciliation Report
Applicable Templates:
Pivot: Transactions Available by Bank Account, Source and Type
Source: Transactions Available for Reconciliation Repo ... more
Run
CE Transactions Available for Reconciliation and other Oracle EBS reports with Blitz Report™ on our demo environment
with ce_avail_trx_v as ( select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('JE_LINES','TRAN_ORG_TYPE',260) source, 'JE_LINES' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, catv.code_combination_id from ce_101_transactions_v catv where :p_type in ('JE_LINES','ALL') union all select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('XTR_LINES','TRAN_ORG_TYPE',260) source, 'XTR_LINES' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, null code_combination_id from ce_185_transactions_v catv where :p_type in ('XTR_LINES','ALL') union all select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('PAYMENTS','TRAN_ORG_TYPE',260) source, 'PAYMENTS' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, null code_combination_id from ce_200_transactions_v catv where :p_type in ('PAYMENTS','ALL') union all select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('RECEIPTS','TRAN_ORG_TYPE',260) source, 'RECEIPTS' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, null code_combination_id from ce_222_transactions_v catv where :p_type in ('RECEIPTS','ALL') union all select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('PAYROLLS','TRAN_ORG_TYPE',260) source, 'PAYROLLS' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, null code_combination_id from ce_801_transactions_v catv where :p_type in ('PAYROLLS','ALL') union all select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('PAYROLLS','TRAN_ORG_TYPE',260) source, 'PAYROLLS' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, null code_combination_id from ce_801_eft_transactions_v catv where :p_type in ('PAYROLLS','ALL') union all select catv.bank_account_id, catv.bank_account_name, catv.bank_account_num, catv.bank_currency_code, catv.bank_name, catv.bank_branch_name, xxen_util.meaning('ROI_LINES','TRAN_ORG_TYPE',260) source, 'ROI_LINES' source_code, catv.trx_type, catv.clearing_trx_type, catv.batch_name, catv.agent_name, catv.trx_date, catv.maturity_date, catv.bank_account_amount, catv.trx_number, catv.type_meaning, catv.payment_method, catv.currency_code, catv.amount, catv.journal_category, catv.period_name, catv.journal_entry_line_number, catv.status, catv.trx_id, catv.org_id, catv.legal_entity_id, null code_combination_id from ce_999_transactions_v catv where catv.clearing_trx_type != 'XTR_LINE' and -- exclude 185 lines :p_type in ('ROI_LINES','ALL') ) -- -- Main Query Starts Here -- select cat.legal_entity, cat.bank_account_num, cat.bank_account_name, cat.bank_account_currency, cat.bank_name, cat.branch_name, cat.source, cat.type, case when cat.type_code in ('CASH','MISC') and cat.transaction_order = 10 then 'Available Receipts' when cat.type_code in ('CASH','MISC') and cat.transaction_order = 20 then 'Reversed Receipts' when cat.type_code in ('PAYMENT') and cat.transaction_order = 30 then 'Available Payments' when cat.type_code in ('PAYMENT') and cat.transaction_order = 40 then 'Voided Payments' when cat.type_code in ('PAYROLL') and cat.transaction_order = 35 then 'Available Payroll Payments' when cat.type_code in ('PAYROLL') and cat.transaction_order = 45 then 'Voided Payroll Payments' when cat.type_code in ('REFUND') and cat.transaction_order = 50 then 'Available Refunds' when cat.type_code in ('REFUND') and cat.transaction_order = 60 then 'Voided Refunds' when cat.type_code in ('STATEMENT') and cat.transaction_order = 75 then 'Unreconciled Statement Lines' when cat.type_code in ('STATEMENT') and cat.transaction_order = 80 then 'Reconciled Statement Lines' when cat.type_code in ('CASHFLOW') and cat.transaction_order = 90 then 'Available Cashflows' when cat.type_code in ('JE_LINE') then 'Available Journal Entry Lines' when cat.type_code in ('ROI-R') then 'Available Open-Interface Transactions - Receipts' when cat.type_code in ('ROI-P') then 'Available Open-Interface Transactions - Payments' end type_description, cat.effective_date, cat.maturity_date, cat.agent_name, cat.payment_method, cat.transaction_number, cat.journal_batch_name, cat.journal_period, cat.journal_category, cat.journal_line_number, cat.journal_descripton, cat.journal_account, cat.statement_number, cat.line_type, cat.transaction_subtype, cat.direction, cat.currency_code, cat.amount, round(case when cat.type_code in ('JE_LINE','STATEMENT') then cat.account_amount else cat.bank_account_amount end,fc.precision) bank_account_amount, round(cat.account_amount,fc.precision) net_amount_available, -- case when cat.source_code in ('RECEIPTS','PAYMENTS') then cat.organization end ar_ap_operating_unit, case when cat.source_code in ('PAYROLLS') then cat.organization end payroll_business_group, cat.treasury_legal_entity, -- -- GL Cash Account Details cat.gl_company_code, cat.gl_company_desc, cat.gl_account_code, cat.gl_account_desc, cat.gl_cost_center_code, cat.gl_cost_center_desc, cat.gl_cash_account, cat.gl_cash_account_desc, -- pivot labels cat.gl_company_code || ' - ' || cat.gl_company_desc gl_company_pivot_label, cat.bank_name || ' - ' || cat.bank_account_num || ' - ' || cat.bank_account_name || ' (' || cat.bank_account_currency || ')' bank_account_pivot_label from ( select catv.bank_account_num bank_account_num, catv.bank_account_name bank_account_name, catv.bank_currency_code bank_account_currency, nvl(xxen_util.meaning(cbagv.bank_account_type,'BANK_ACCOUNT_TYPE',260),cbagv.bank_account_type) bank_account_type, xep.name legal_entity, catv.bank_name bank_name, catv.bank_branch_name branch_name, catv.source source, catv.source_code source_code, decode( catv.clearing_trx_type , 'MISC' , 'CASH' , 'PAY' , 'PAYROLL' , 'PAY_EFT' , 'PAYROLL' , 'XTR_LINE', decode( catv.trx_type , 'PAYMENT', 'ROI-P' , 'CASH' , 'ROI-R' ) , 'ROI_LINE', decode( catv.trx_type , 'PAYMENT', 'ROI-P' , 'CASH' , 'ROI-R' ) , catv.clearing_trx_type ) type_code, decode( catv.trx_type , 'PAYMENT', catv.type_meaning , 'REFUND' , catv.type_meaning , 'CASH' , catv.type_meaning , 'MISC' , catv.type_meaning || ' ' || xxen_util.meaning('CASH','TRX_TYPE',260) ) type, trunc(catv.trx_date) effective_date, trunc(nvl(catv.maturity_date ,catv.trx_date)) maturity_date, decode( catv.clearing_trx_type , 'JE_LINE', null , catv.agent_name ) agent_name, decode( catv.clearing_trx_type , 'JE_LINE', null , catv.payment_method ) payment_method, decode( catv.clearing_trx_type , 'JE_LINE', null , catv.trx_number ) transaction_number, decode( catv.clearing_trx_type , 'JE_LINE', catv.batch_name ) journal_batch_name, decode( catv.clearing_trx_type , 'JE_LINE', catv.period_name ) journal_period, decode( catv.clearing_trx_type , 'JE_LINE', catv.journal_category ) journal_category, decode( catv.clearing_trx_type , 'JE_LINE', catv.journal_entry_line_number ) journal_line_number, decode( catv.clearing_trx_type , 'JE_LINE', catv.trx_number ) journal_descripton, decode( catv.clearing_trx_type , 'JE_LINE', fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, catv.code_combination_id, 'ALL', 'Y', 'VALUE') ) journal_account, null statement_number, decode( catv.clearing_trx_type , 'JE_LINE', catv.type_meaning ) line_type, null transaction_subtype, null direction, catv.currency_code currency_code, catv.bank_account_amount bank_account_amount, catv.amount amount, decode( catv.trx_type , 'PAYMENT', decode(catv.status, 'VOIDED', catv.bank_account_amount, -catv.bank_account_amount) , 'REFUND' , decode(catv.status, 'VOIDED', catv.bank_account_amount, -catv.bank_account_amount) , 'CASH' , decode(catv.status, 'REVERSED', -catv.bank_account_amount, catv.bank_account_amount) , 'MISC' , decode(catv.status, 'REVERSED', -catv.bank_account_amount, catv.bank_account_amount) ) account_amount, decode( catv.clearing_trx_type , 'CASH' , decode(catv.status, 'REVERSED', 20, 10) , 'MISC' , decode(catv.status, 'REVERSED', 20, 10) , 'PAYMENT' , decode(catv.status, 'VOIDED' , 40, 30) , 'PAY' , decode(catv.status, 'V' , 45, 35) , 'PAY_EFT' , decode(catv.status, 'V' , 45, 35) , 'REFUND' , decode(catv.status,'VOIDED' , 60, 50) , 'JE_LINE' , 70 , 'XTR_LINE', 85 |