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 , 'ROI_LINE', 85 ) transaction_order, catv.trx_id trx_id, haou.name organization, xep2.name treasury_legal_entity, -- -- GL Cash Account Details fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') gl_company_code, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCING', 'Y', 'DESCRIPTION') gl_company_desc, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') gl_account_code, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_ACCOUNT', 'Y', 'DESCRIPTION') gl_account_desc, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'FA_COST_CTR', 'Y', 'VALUE') gl_cost_center_code, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'FA_COST_CTR', 'Y', 'DESCRIPTION') gl_cost_center_desc, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'ALL', 'Y', 'VALUE') gl_cash_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'ALL', 'Y', 'DESCRIPTION') gl_cash_account_desc from ce_avail_trx_v catv, ce_bank_accts_gt_v cbagv, ce_system_parameters csp, xle_entity_profiles xep, gl_code_combinations gcc, hr_all_organization_units haou, xle_entity_profiles xep2 where cbagv.bank_account_id = catv.bank_account_id and csp.legal_entity_id (+) = cbagv.account_owner_org_id and xep.legal_entity_id = cbagv.account_owner_org_id and gcc.code_combination_id = cbagv.asset_code_combination_id and haou.organization_id (+) = catv.org_id and xep2.legal_entity_id (+) = catv.legal_entity_id and catv.status != decode( nvl(csp.show_void_payment_flag, 'N') , 'N', decode(catv.clearing_trx_type,'PAY', 'V', 'PAY_EFT', 'V', 'VOIDED') , ' x' ) and catv.clearing_trx_type != 'STATEMENT' and nvl(catv.journal_category,'Y') != decode(catv.clearing_trx_type,'JE_LINE','Revaluation','X') and catv.trx_date >= csp.cashbook_begin_date and ( ( :p_type in ('PAYMENTS', 'RECEIPTS', 'AR_AND_AP','ALL') and haou.name = nvl(:p_operating_unit, haou.name) and catv.clearing_trx_type in ( 'PAYMENT', 'REFUND', 'CASH', 'MISC') ) or ( :p_type in ('XTR_LINES', 'ALL') and catv.legal_entity_id = csp.legal_entity_id and xep2.name = nvl(:p_legal_entity, xep2.name) and catv.clearing_trx_type = 'XTR_LINE' ) or ( :p_type in ( 'PAYROLLS', 'ALL') and catv.legal_entity_id is null and haou.name = nvl(:p_business_group, haou.name) and catv.clearing_trx_type in ( 'PAY', 'PAY_EFT') ) or ( :p_type in ('ROI_LINES', 'JE_LINES','ALL') and catv.org_id is null and catv.legal_entity_id is null and catv.clearing_trx_type in ( 'ROI_LINE', 'JE_LINE') ) ) and 1=1 and 2=2 union all select distinct 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, xxen_util.meaning< |