CE Cleared Transactions

with q_cleared_transactions as
(
select --Q1 Cleared Payments
 cbagv.bank_account_id            bank_account_id,
 cbagv.bank_account_name          bank_account_name,
 cbagv.masked_account_num         bank_account_num,
 hp_bank.party_name               bank_name,
 hp_branch.party_name             branch_name,
 xep.name                         legal_entity,
 'PAYMENT'                        type1,
 'PAYMENT'                        type2,
 aca.vendor_name                  supplier_customer,
 aca.check_date                   trx_date,
 aca.future_pay_due_date          maturity_date,
 to_char(aca.check_number)        trx_number,
 ipmv.payment_method_name         payment_method,
 aca.currency_code                transaction_currency,
 30                               transaction_order,
 ipa.payment_instruction_id       batch_id,
 nvl2(ipa.payment_instruction_id
     ,aca.checkrun_name,null)     batch_name,
 to_char(ipa.payment_instruction_id)
                                  remittance_number,
 ipia.payment_currency_code       batch_currency,
 nvl2(cbagv2.masked_account_num,
      hp_batch_bank.party_name || ' - ' || cbagv2.masked_account_num || ' - ' || cbagv2.bank_account_name,
      null)                       batch_bank_account,
 ipa.payment_date                 batch_date,
 aca.cleared_date                 cleared_date,
 aca.amount                       amount,
 decode( aca.currency_code
       , cbagv.currency_code, aca.amount
                            , nvl(aca.base_amount,aca.amount)
 )                                account_amount,
 decode(aca.currency_code
       , cbagv.currency_code, aca.cleared_amount
                          , nvl(aca.cleared_base_amount,aca.cleared_amount)
       )                          account_cleared_amount,
 cspg.name                        org_name,
 cbagv.currency_code                bacurr,
 gcc.code_combination_id asset_ccid,
 gcc.chart_of_accounts_id coaid
from
 ap_checks_all                    aca,
 iby_payment_methods_vl           ipmv,
 iby_payments_all                 ipa,
 iby_pay_instructions_all         ipia,
 ce_bank_accts_gt_v               cbagv,
 ce_bank_accts_gt_v               cbagv2,
 hz_parties                       hp_bank,
 hz_parties                       hp_branch,
 hz_parties                       hp_batch_bank,
 ce_bank_acct_uses_all            cbaua,
 ce_security_profiles_gt          cspg,
 xle_entity_profiles              xep,
 hr_all_organization_units        haou,
 gl_code_combinations             gcc
where
 aca.cleared_date is not null and
 ipmv.payment_method_code = aca.payment_method_code and
 ipa.payment_id (+) = aca.payment_id and
 ipia.payment_instruction_id (+) = ipa.payment_instruction_id and
 cbagv2.bank_account_id (+) = ipia.internal_bank_account_id and
 hp_batch_bank.party_id (+) =  cbagv2.bank_id and
 cbagv.bank_branch_id = hp_branch.party_id and
 cbagv.bank_id = hp_bank.party_id and
 cbagv.bank_account_id = cbaua.bank_account_id and
 cbaua.bank_acct_use_id = aca.ce_bank_acct_use_id and
 cbaua.ap_use_enable_flag = 'Y' and
 cbaua.org_id = cspg.organization_id and
 cspg.organization_type = 'OPERATING_UNIT' and
 cbaua.org_id = aca.org_id and
 xep.legal_entity_id = cbagv.account_owner_org_id and
 haou.organization_id = cbaua.org_id and
 gcc.code_combination_id = cbagv.asset_code_combination_id and
 aca.cleared_date >= nvl(:p_date_from, aca.cleared_date) and
 aca.cleared_date <= nvl(:p_date_to, aca.cleared_date) and
 :p_batch_or_trx = 'T' and
 :p_type in ('AR_AND_AP', 'PAYMENTS','ALL') and
 haou.name = nvl(:p_operating_unit,haou.name)
union all
select --Q2 Cleared Receipts
 cbagv.bank_account_id,
 cbagv.bank_account_name,
 cbagv.masked_account_num,
 hp_bank.party_name bank_name,
 hp_branch.party_name branch_name,
 xep.name legal_entity,
 'RECEIPT' type1,
 'RECEIPT' type2,
 hp.party_name supplier_customer,
 acr.receipt_date trx_date,
 apsa.due_date maturity_date,
 acr.receipt_number trx_number,
 arm.name payment_method,
 acr.currency_code transaction_currency,
 10 transaction_order,
 aba.batch_id batch_id,
 aba.name batch_name,
 aba.bank_deposit_number remittance_number,
 aba.currency_code batch_currency,
 nvl2(cbagv2.masked_account_num,
      hp_batch_bank.party_name || ' - ' || cbagv2.masked_account_num || ' - ' || cbagv2.bank_account_name,
      null) batch_bank_account,
 aba.batch_date batch_date,
 acrha.trx_date cleared_date,
 acr.amount amount,
 decode(acr.currency_code, cbagv.currency_code, acrha.amount, nvl(acrha.acctd_amount, acrha.amount)) account_amount,
 decode(acr.currency_code, cbagv.currency_code, acrha.amount, nvl(acrha.acctd_amount, acrha.amount)) account_cleared_amount,
 cspg.name org_name,
 cbagv.currency_code bacurr,
 gcc.code_combination_id asset_ccid,
 gcc.chart_of_accounts_id coaid
from
 ar_cash_receipts             acr,
 ar_cash_receipt_history_all  acrha,
 ar_batches_all               aba,
 ce_bank_accts_gt_v           cbagv,
 ce_bank_acct_uses_all        cbaua2,
 ce_bank_accts_gt_v           cbagv2,
 ce_lookups                   cl,
 hz_cust_accounts             hca,
 hz_parties                   hp,
 ar_payment_schedules_all     apsa,
 ar_receipt_methods           arm,
 hz_parties                   hp_bank,
 hz_parties                   hp_branch,
 hz_parties                   hp_batch_bank,
 ce_bank_acct_uses_all        cbaua,
 ce_security_profiles_gt      cspg,
 xle_entity_profiles          xep,
 hr_all_organization_units    haou,
 gl_code_combinations         gcc
where
 cl.lookup_type = 'TRX_TYPE' and
 cl.lookup_code = acr.type and
 acr.cash_receipt_id = acrha.cash_receipt_id and
 aba.batch_id (+) = acrha.batch_id and
 cbaua2.bank_acct_use_id (+) = aba.remit_bank_acct_use_id and
 cbaua2.org_id (+) = aba.org_id and
 cbagv2.bank_account_id (+) = cbaua2.bank_account_id and
 hp_batch_bank.party_id (+) = cbagv2.bank_id and
 hca.cust_account_id(+) = acr.pay_from_customer and
 hp.party_id(+) = hca.party_id and
 apsa.cash_receipt_id(+) = acr.cash_receipt_id and
 arm.receipt_method_id = acr.receipt_method_id and
 cbagv.bank_branch_id = hp_branch.party_id and
 cbagv.bank_id = hp_bank.party_id and
 cbagv.bank_account_id = cbaua.bank_account_id and
 cbaua.ar_use_enable_flag = 'Y' and
 cbaua.org_id = cspg.organization_id and
 cspg.organization_type = 'OPERATING_UNIT' and
 cbaua.bank_acct_use_id = acr.remit_bank_acct_use_id and
 cbaua.org_id = acr.org_id and
 xep.legal_entity_id = cbagv.account_owner_org_id and
 haou.organization_id = cbaua.org_id and
 gcc.code_combination_id = cbagv.asset_code_combination_id and
 acrha.status in ('CLEARED', 'RISK_ELIMINATED') and
 acrha.current_record_flag = 'Y' and
 acrha.trx_date >= nvl(:p_date_from, acrha.trx_date) and
 acrha.trx_date <= nvl(:p_date_to, acrha.trx_date) and
 :p_batch_or_trx = 'T' and
 :p_type in ('AR_AND_AP', 'RECEIPTS', 'ALL') and
 haou.name = nvl(:p_operating_unit,haou.name)
union all --Q5 payroll payments
select
 cbagv.bank_account_id,
 cbagv.bank_account_name,
 cbagv.masked_account_num,
 hp_bank.party_name bank_name,
 hp_branch.party_name branch_name,
 xep.name legal_entity,
 'PAYROLL' type1,
 'PAYMENT' type2,
 c801rv.vendor_name supplier_customer,
 c801rv.trx_date trx_date,
 c801rv.maturity_date maturity_date,
 c801rv.trx_number trx_number,
 popm.org_payment_method_name payment_method,
 c801rv.currency_code transaction_currency,
 40 transaction_order,
 to_number(null) batch_id,
 null batch_name,
 null remittance_number,
 null batch_currency,
 null batch_bank_account,
 to_date(null) batch_date,
 c801rv.cleared_date cleared_date,
 c801rv.amount amount,
 c801rv.bank_account_amount account_amount,
 c801rv.amount_cleared account_cleared_amount,
 fnd_access_control_util.get_org_name(c801rv.org_id) org_name,
 cbagv.currency_code bacurr,
 gcc.code_combination_id asset_ccid,
 gcc.chart_of_accounts_id coaid
from
 ce_801_reconciled_v       c801rv,
 hz_parties                hp_bank,
 hz_parties                hp_branch,
 ce_bank_accts_gt_v        cbagv,
 pay_pre_payments          ppp,
 pay_assignment_actions    paa,
 pay_org_payment_methods_f popm,
 xle_entity_profiles       xep,
 hr_all_organization_units haou,
 gl_code_combinations      gcc
where
 ppp.org_payment_method_id = popm.org_payment_method_id and
 paa.assignment_action_id = c801rv.trx_id and
 paa.pre_payment_id = ppp.pre_payment_id and
 cbagv.bank_account_id = c801rv.bank_account_id and
 cbagv.bank_branch_id = hp_branch.party_id and
 cbagv.bank_id = hp_bank.party_id and
 xep.legal_entity_id = cbagv.account_owner_org_id and
 haou.organization_id = c801rv.org_id and
 gcc.code_combination_id = cbagv.asset_code_combination_id and
 c801rv.cleared_date >= nvl(:p_date_from, c801rv.cleared_date) and
 c801rv.cleared_date <= nvl(:p_date_to, c801rv.cleared_date) and
 c801rv.trx_date between popm.effective_start_date and popm.effective_end_date and
 :p_batch_or_trx = 'T' and
 :p_type in ( 'PAYROLLS', 'ALL') and
 haou.name = nvl(:p_business_group, haou.name)
union all --Q6 eft payroll
select
 cbagv.bank_account_id,
 cbagv.bank_account_name,
 cbagv.masked_account_num,
 hp_bank.party_name bank_name,
 hp_branch.party_name branch_name,
 xep.name legal_entity,
 'PAYROLL' type1,
 'PAYMENT' type2,
 c801erv.vendor_name supplier_customer,
 c801erv.trx_date trx_date,
 c801erv.maturity_date maturity_date,
 c801erv.trx_number trx_number,
 popm.org_payment_method_name payment_method,
 c801erv.currency_code transaction_currency,
 40 transaction_order,
 c801erv.batch_id batch_id,
 c801erv.batch_name batch_name,
 null remittance_number,
 null batch_currency,
 null batch_bank_account,
 to_date(null) batch_date,
 c801erv.cleared_date cleared_date,
 c801erv.amount amount,
 c801erv.bank_account_amount account_amount,
 c801erv.amount_cleared account_cleared_amount,
 fnd_access_control_util.get_org_name(c801erv.org_id) org_name,
 cbagv.currency_code bacurr,
 gcc.code_combination_id asset_ccid,
 gcc.chart_of_accounts_id coaid
from
 ce_801_eft_reconciled_v   c801erv,
 ce_bank_accts_gt_v        cbagv,
 hz_parties                hp_bank,
 hz_parties                hp_branch,
 pay_pre_payments          ppp,
 pay_assignment_actions    paa,
 pay_org_payment_methods_f popm,
 xle_entity_profiles       xep,
 hr_all_organization_units haou,
 gl_code_combinations      gcc
where
 ppp.org_payment_method_id = popm.org_payment_method_id and
 paa.assignment_action_id = c801erv.trx_id and
 paa.pre_payment_id = ppp.pre_payment_id and
 cbagv.bank_account_id = c801erv.bank_account_id and
 cbagv.bank_branch_id = hp_branch.party_id and
 cbagv.bank_id = hp_bank.party_id and
 xep.legal_entity_id = cbagv.account_owner_org_id and
 haou.organization_id = c801erv.org_id and
 gcc.code_combination_id = cbagv.asset_code_combination_id and
 c801erv.cleared_date >= nvl(:p_date_from, c801erv.cleared_date) and
 c801erv.cleared_date <= nvl(:p_date_to, c801erv.cleared_date) and
 c801erv.trx_date between popm.effective_start_date and popm.effective_end_date and
 :p_batch_or_trx = 'T' and
 :p_type in ( 'PAYROLLS', 'ALL') and
 haou.name = nvl(:p_business_group, haou.name)
union all --Q7 roi_line
select
 cbagv.bank_account_id,
 cbagv.bank_account_name,
 cbagv.masked_account_num,
 cbbv.bank_name,
 cbbv.bank_branch_name branch_name,
 xep.name legal_entity,
 'ROI_LINE' type1,
 'RECEIPT' type2,
 null supplier_customer,
 c999iv.trx_date trx_date,
 to_date(null) maturity_date,
 c999iv.trx_number trx_number,
 null payment_method,
 c999iv.currency_code transaction_currency,
 50 transaction_order,
 to_number(null) batch_id,
 null batch_name,
 null remittance_number,
 null batch_currency,
 null batch_bank_account,
 to_date(null) batch_date,
 c999iv.cleared_date cleared_date,
 c999iv.amount amount,
 decode(c999iv.currency_code, cbagv.currency_code, c999iv.amount, nvl(c999iv.acctd_amount, c999iv.amount)) account_amount,
 decode(c999iv.currency_code, cbagv.currency_code, c999iv.cleared_amount, nvl(c999iv.acctd_cleared_amount, c999iv.cleared_amount)) account_cleared_amount,
 null org_name,
 cbagv.currency_code bacurr,
 gcc.code_combination_id asset_ccid,
 gcc.chart_of_accounts_id coaid
from
 ce_999_interface_v   c999iv,
 ce_bank_accts_gt_v   cbagv,
 ce_bank_branches_v   cbbv,
 xle_entity_profiles  xep,
 gl_code_combinations gcc
where
 cbagv.bank_branch_id = cbbv.branch_party_id and
 cbagv.bank_account_id = c999iv.bank_account_id and
 xep.legal_entity_id = cbagv.account_owner_org_id and
 gcc.code_combination_id = cbagv.asset_code_combination_id and
 c999iv.status = nvl(cbagv.recon_oi_cleared_status, '#') and
 c999iv.cleared_date >= nvl(:p_date_from, c999iv.cleared_date) and
 c999iv.cleared_date <= nvl(:p_date_to, c999iv.cleared_date) and
 :p_batch_or_trx = 'T' and
 :p_type in ('ROI_LINES', 'ALL') and
 c999iv.trx_type = 'CASH'
union all
select --Q8 roi_line
 cbagv.bank_account_id,
 cbagv.bank_account_name,
 cbagv.masked_account_num,
 cbbv.bank_name,
 cbbv.bank_branch_name branch_name,
 xep.name legal_entity,
 'ROI_LINE' type1,
 'PAYMENT' type2,
 null supplier_customer,
 c999iv.trx_date trx_date,
 to_date(null) maturity_date,
 c999iv.trx_number trx_number,
 null payment_method,
 c999iv.currency_code transaction_currency,
 60 transaction_order,
 to_number(null) batch_id,
 null batch_name,
 null remittance_number,
 null batch_currency,
 null batch_bank_account,
 to_date(null) batch_date,
 c999iv.cleared_date cleared_date,
 c999iv.amount amount,
 decode(c999iv.currency_code, cbagv.currency_code, c999iv.amount, nvl(c999iv.acctd_amount, c999iv.amount)) account_amount,
 decode(c999iv.currency_code, cbagv.currency_code, c999iv.cleared_amount, nvl(c999iv.acctd_cleared_amount, c999iv.cleared_amount)) account_cleared_amount,
 null org_name,
 cbagv.currency_code bacurr,
 gcc.code_combination_id asset_ccid,
 gcc.chart_of_accounts_id coaid
from
 ce_999_interface_v   c999iv,
 ce_bank_accts_gt_v   cbagv,
 ce_bank_branches_v   cbbv,
 xle_entity_profiles  xep,
 gl_code_combinations gcc
where
 cbagv.bank_branch_id = cbbv.branch_party_id and
 cbagv.bank_account_id = c999iv.bank_account_id and
 c999iv.status = nvl(cbagv.recon_oi_cleared_status, '#') and
 xep.legal_entity_id = cbagv.account_owner_org_id and
 gcc.code_combination_id = cbagv.asset_code_combination_id and
 c999iv.cleared_date >= nvl(:p_date_from, c999iv.cleared_date) and
 c999iv.cleared_date <= nvl(:p_date_to, c999iv.cleared_date) and
 :p_batch_or_trx = 'T' and
 :p_type in ('ROI_LINES','ALL') and
 c999iv.trx_type = 'PAYMENT'
union all
select --Q9 xtr_line
 cbagv.bank_account_id,
 cbagv.bank_account_name,
 cbagv.masked_account_num,
 cbbv.bank_name,
 cbbv.bank_branch_name branch_name,
 xep.name legal_entity,
 'XTR_LINE' type1,
 'RECEIPT' type2,
 null supplier_customer,
 c185rv.trx_date trx_date,
 to_date(null) maturity_date,
 c185rv.trx_number trx_number,
 null payment_method,
 c185rv.currency_code transaction_currency,
 50 transaction_order,
 to_number(null) batch_id,
 null batch_name,
 null remittance_number,
 null batch_currency,
 null batch_bank_account,
 to_date(null) batch_date,
 c185rv.cleared_date cleared_date,
 c185rv.amount amount,
 c185rv.bank_account_amount account_amount,
 c185rv.amount_cleared account_cleared_amount,
 xep2.name org_name,
 cbagv.currency_code bacurr,
 gcc.code_combination_id asset_ccid,
 gcc.chart_of_accounts_id coaid
from
 ce_185_reconciled_v  c185rv,
 ce_bank_accts_gt_v   cbagv,
 ce_bank_branches_v   cbbv,
 xle_entity_profiles  xep,
 xle_entity_profiles  xep2,
 gl_code_combinations gcc
where
 cbagv.bank_branch_id = cbbv.branch_party_id and
 cbagv.bank_account_id = c185rv.bank_account_id and
 xep.legal_entity_id = cbagv.account_owner_org_id and
 xep2.legal_entity_id = c185rv.legal_entity_id and
 gcc.code_combination_id = cbagv.asset_code_combination_id and
 c185rv.cleared_date >= nvl(:p_date_from, c185rv.cleared_date) and
 c185rv.cleared_date <= nvl(:p_date_to, c185rv.cleared_date) and
 :p_batch_or_trx = 'T' and
 :p_type in ( 'XTR_LINES', 'ALL') and
 c185rv.trx_type = 'CASH' and
 xep2.name = nvl(:p_legal_entity,xep2.name)
union all
select --Q10 xtr_line
 cbagv.bank_account_id,
 cbagv.bank_account_name,
 cbagv.masked_account_num,
 cbbv.bank_name bank_name,
 cbbv.bank_branch_name branch_name,
 xep.name legal_entity,
 'XTR_LINE' type2,
 'PAYMENT' type2,
 null supplier_customer,
 c185rv.trx_date trx_date,
 to_date(null) maturity_date,
 c185rv.trx_number trx_number,
 null payment_method,
 c185rv.currency_code transaction_currency,
 60 transaction_order,
 to_number(null) batch_id,
 null batch_name,
 null remittance_number,
 null batch_currency,
 null batch_bank_account,
 to_date(null) batch_date,
 c185rv.cleared_date cleared_date,
 c185rv.amount amount,
 c185rv.bank_account_amount account_amount,
 c185rv.amount_cleared account_cleared_amount,
 xep2.name org_name,
 cbagv.currency_code bacurr,
 gcc.code_combination_id asset_ccid,
 gcc.chart_of_accounts_id coaid
from
 ce_185_reconciled_v  c185rv,
 ce_bank_accts_gt_v   cbagv,
 ce_bank_branches_v   cbbv,
 xle_entity_profiles  xep,
 xle_entity_profiles  xep2,
 gl_code_combinations gcc
where
 cbagv.bank_branch_id = cbbv.branch_party_id and
 cbagv.bank_account_id = c185rv.bank_account_id and
 xep.legal_entity_id = cbagv.account_owner_org_id and
 xep2.legal_entity_id = c185rv.legal_entity_id and
 gcc.code_combination_id = cbagv.asset_code_combination_id and
 c185rv.cleared_date >= nvl(:p_date_from, c185rv.cleared_date) and
 c185rv.cleared_date <= nvl(:p_date_to, c185rv.cleared_date) and
 :p_batch_or_trx = 'T' and
 :p_type in ( 'XTR_LINES', 'ALL') and
 c185rv.trx_type = 'PAYMENT' and
 xep2.name = nvl(:p_legal_entity,xep2.name)
union all --Q11 cashflow receipt
select
 cbagv.bank_account_id,
 cbagv.bank_account_name,
 cbagv.masked_account_num,
 hp_bank.party_name bank_name,
 hp_branch.party_name branch_name,
 cspg.name legal_entity,
 'CASHFLOW' type1,
 'RECEIPT' type2,
 null supplier_customer,
 cc.cashflow_date trx_date,
 to_date(null) maturity_date,
 nvl(cc.bank_trxn_number, cc.cashflow_id) trx_number,
 null payment_method,
 cc.cashflow_currency_code transaction_currency,
 70 transaction_order,
 to_number(null) batch_id,
 null batch_name,
 null remittance_number,
 null batch_currency,
 null batch_bank_account,
 to_date(null) batch_date,
 cc.cleared_date cleared_date,
 cc.cashflow_amount amount,
 decode(cc.cashflow_currency_code, cbagv.currency_code, cc.cashflow_amount, nvl(cc.base_amount, cc.cashflow_amount)) account_amount,
 ccah.cleared_amount account_cleared_amount,
 xep2.name org_name,
 cbagv.currency_code bacurr,
 gcc.code_combination_id asset_ccid,
 gcc.chart_of_accounts_id coaid
from
 ce_bank_accts_gt_v      cbagv,
 ce_cashflows            cc,
 ce_cashflow_acct_h      ccah,
 hz_parties              hp_bank,
 hz_parties              hp_branch,
 ce_security_profiles_gt cspg,
 xle_entity_profiles     xep2,
 gl_code_combinations    gcc
where
 cbagv.bank_branch_id = hp_branch.party_id and
 cbagv.bank_id = hp_bank.party_id and
 cbagv.account_owner_org_id = cspg.organization_id and
 cspg.organization_type = 'LEGAL_ENTITY' and
 cbagv.bank_account_id = cc.cashflow_bank_account_id and
 cbagv.account_owner_org_id = cc.cashflow_legal_entity_id and
 xep2.legal_entity_id = cc.cashflow_legal_entity_id and
 gcc.code_combination_id = cbagv.asset_code_combination_id and
 cc.cashflow_status_code in ('CLEARED','RECONCILED') and
 cc.cleared_date >= nvl(:p_date_from, cc.cleared_date) and
 cc.cleared_date <= nvl(:p_date_to, cc.cleared_date) and
 cc.cashflow_direction = 'RECEIPT' and
 ccah.cashflow_id = cc.cashflow_id and
 ccah.current_record_flag = 'Y' and
 ccah.event_type in ('CE_BAT_CLEARED','CE_STMT_RECORDED') and
 :p_batch_or_trx = 'T'