CE General Ledger Reconciliation

with
q_gl_end_bal as
(
  select
    'GL END BALANCE'   record_type,
    null               operating_unit,
    null               transaction_number,
    to_date(null)      gl_date,
    to_date(null)      effective_date,
    null               agent,
    null               payment_method,
    null               transaction_subtype,
    null               je_name,
    to_number(null)    je_line,
    null               je_line_type,
    to_date(null)      je_posted_date,
    null               statement_number,
    to_number(null)    statement_line,
    null               statement_trx_type,
    null               status,
    :c_bank_curr_dsp   currency,
    to_number(null)    transaction_amount,
    to_number(null)    accounted_amount,
    to_number (nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0) + nvl(bal.begin_balance_dr,0) - nvl(bal.begin_balance_cr,0))
                       gl_end_balance,
    glcc.code_combination_id
                       cash_account_ccid
  from
    gl_balances bal,
    gl_code_combinations glcc
  where
   bal.actual_flag(+) = 'A' and
   bal.ledger_id(+) = :c_set_of_books_id and
   bal.currency_code(+) = :c_bank_curr_dsp and
   nvl(bal.translated_flag,'R') = 'R' and
   bal.period_name(+) = :p_period_name and
   bal.code_combination_id(+) = glcc.code_combination_id and
   glcc.chart_of_accounts_id = :c_chart_of_accounts_id and
   glcc.template_id is null  and
   glcc.code_combination_id in
    ( select distinct
       case col.cnum
       when 1 then cba2.asset_code_combination_id
       when 2 then cgac2.asset_code_combination_id
       when 3 then cgac2.ap_asset_ccid
       when 4 then cgac2.ar_asset_ccid
       when 5 then cgac2.xtr_asset_ccid
       end
      from
       ce_bank_accounts cba2,
       ce_bank_acct_uses_all cbaua2,
       ce_gl_accounts_ccid cgac2,
       ( select 1 as cnum from dual union all
         select 2 as cnum from dual union all
         select 3 as cnum from dual union all
         select 4 as cnum from dual union all
         select 5 as cnum from dual
       ) col
      where
       cba2.bank_account_id = :p_bank_account_id and
       cbaua2.bank_account_id = cba2.bank_account_id and
       cgac2.bank_acct_use_id = cbaua2.bank_acct_use_id
    )
),
q_stmt_end_bal as
(
  select
    'STMT END BALANCE' record_type,
    null               operating_unit,
    null               transaction_number,
    to_date(null)      gl_date,
    to_date(null)      effective_date,
    null               agent,
    null               payment_method,
    null               transaction_subtype,
    null               je_name,
    to_number(null)    je_line,
    null               je_line_type,
    to_date(null)      je_posted_date,
    null               statement_number,
    to_number(null)    statement_line,
    null               statement_trx_type,
    null               status,
    :c_bank_curr_dsp   currency,
    to_number(null)    transaction_amount,
    to_number(:p_closing_balance)
                       accounted_amount,
    to_number(null)    gl_end_balance,
    to_number(:c_asset_cc_id) cash_account_ccid
  from
    dual
),
q_ar_receipts as
(
  /* query 1: fetch latest receipt history event which has hit gl cash account and is not reconciled
     query 2: fetch unreconciled cleared event for a reversed receipt */
  select
    'RECEIPT'          record_type,
    fnd_access_control_util.get_org_name(cr.org_id)
                       operating_unit,
    cr.receipt_number  transaction_number,
    crh.gl_date        gl_date,
    cr.receipt_date    effective_date,
    hz.party_name      agent,
    arm.name           payment_method,
    null               transaction_subtype,
    null               je_name,
    to_number(null)    je_line,
    null               je_line_type,
    to_date(null)      je_posted_date,
    null               statement_number,
    to_number(null)    statement_line,
    null               statement_trx_type,
    crh.status         status,
    cr.currency_code   currency,
    cr.amount          transaction_amount,
    decode(:c_bank_curr_dsp,
           :c_gl_currency_code, decode(crh.status,'REVERSED',-crh.acctd_amount,crh.acctd_amount),
                                decode(crh.status,'REVERSED',-crh.amount,crh.amount)
          )            accounted_amount,
    to_number(null)    gl_end_balance,
    gla.ar_asset_ccid cash_account_ccid
  from
    ar_cash_receipts cr,
    ar_cash_receipt_history crh,
    hz_cust_accounts cu,
    hz_parties hz,
    ar_receipt_methods arm,
    ce_bank_acct_uses_ou_v bau,
    ce_bank_accounts ba,
    ce_gl_accounts_ccid gla,
    ce_system_parameters sys
  where
    cr.cash_receipt_id = crh.cash_receipt_id and
    cr.remit_bank_acct_use_id = bau.bank_acct_use_id and
    bau.bank_account_id = :p_bank_account_id and
    bau.org_id = cr.org_id and
    bau.bank_account_id = ba.bank_account_id and
    ba.account_owner_org_id = sys.legal_entity_id and
    bau.ar_use_enable_flag = 'Y' and
    gla.bank_acct_use_id = bau.bank_acct_use_id and
    crh.account_code_combination_id = gla.ar_asset_ccid and
    crh.status in('REMITTED', 'CLEARED', 'REVERSED') and
    crh.gl_date <= :c_as_of_date and
    crh.gl_date >= sys.cashbook_begin_date and
    decode(crh.status,'REMITTED',nvl(crh.reversal_created_from,'X'),crh.created_from) <> 'RATE ADJUSTMENT TRIGGER' and
    not ( crh.status = 'REMITTED' and
          crh.created_from = 'RATE ADJUSTMENT TRIGGER' and
          crh.reversal_created_from is null and
          crh.amount = - 1 * cr.amount
        ) and
    not ( crh.status ='CLEARED' and
          nvl(crh.reversal_created_from,'x') ='RATE ADJUSTMENT TRIGGER'
        ) and
    crh.posting_control_id > 0 and
    not exists
      ( select 1
        from ar_cash_receipt_history_all crh_r
        where
          crh_r.cash_receipt_history_id = crh.reversal_cash_receipt_hist_id and
          crh_r.gl_date <= :c_as_of_date and
          crh_r.posting_control_id > 0 and
          crh_r.created_from <> 'RATE ADJUSTMENT TRIGGER'
      ) and
    cu.cust_account_id(+) = cr.pay_from_customer and
    hz.party_id(+) = cu.party_id and
    arm.receipt_method_id = cr.receipt_method_id and
    cr.status <> 'REV' and
    not exists
      ( select null
        from
          ce_statement_recon_gt_v sr,
          ce_statement_lines sl,
          ce_statement_headers sh
        where
          sr.reference_id = crh.cash_receipt_history_id and
          sr.reference_type = 'RECEIPT' and
          sr.status_flag = 'M' and
          sr.current_record_flag = 'Y' and
          sl.statement_line_id = sr.statement_line_id and
          sl.statement_header_id = sh.statement_header_id and
          sh.bank_account_id = :p_bank_account_id and
          sh.statement_date <= :c_as_of_date
        union
        /* this union is required for receipts with rate adjustment that are reconciled. for such cases, the reference will be of the rate adjustment record */
        select null
        from
          ce_statement_recon_gt_v sr,
          ce_statement_lines sl,
          ce_statement_headers sh,
          ar_cash_receipt_history crh_rc
        where
          sr.reference_id = crh_rc.cash_receipt_history_id and
          sr.reference_type = 'RECEIPT' and
          sr.status_flag = 'M' and
          sr.current_record_flag = 'Y' and
          sl.statement_line_id = sr.statement_line_id and
          sl.statement_header_id = sh.statement_header_id and
          sh.bank_account_id = :p_bank_account_id and
          sh.statement_date <= :c_as_of_date and
          crh_rc.created_from = 'RATE ADJUSTMENT TRIGGER' and
          crh_rc.cash_receipt_id = cr.cash_receipt_id
      ) and
    /* not exists to filter out receipts reversed without remittance */
    not exists
      ( select 1
        from
          ar_cash_receipt_history_all crh2
        where
          crh.cash_receipt_history_id = crh2.reversal_cash_receipt_hist_id and
          crh.status = 'REVERSED' and
          crh2.status = 'CONFIRMED'
      )
  union all
  select
    'RECEIPT'          record_type,
    fnd_access_control_util.get_org_name(cr.org_id)
                       operating_unit,
    cr.receipt_number  transaction_number,
    crh.gl_date        gl_date,
    cr.receipt_date    effective_date,
    hz.party_name      agent,
    arm.name           payment_method,
    null               transaction_subtype,
    null               je_name,
    to_number(null)    je_line,
    null               je_line_type,
    to_date(null)      je_posted_date,
    null               statement_number,
    to_number(null)    statement_line,
    null               statement_trx_type,
    crh.status         status,
    cr.currency_code   currency,
    cr.amount          transaction_amount,
    decode(:c_bank_curr_dsp,:c_gl_currency_code,crh.acctd_amount,crh.amount)
                       accounted_amount,
    to_number(null)    gl_end_balance,
    gla.ar_asset_ccid  cash_account_ccid
  from
    ar_cash_receipts cr,
    ar_cash_receipt_history crh2,
    ar_cash_receipt_history crh,
    hz_cust_accounts cu,
    hz_parties hz,
    ar_receipt_methods arm,
    ce_bank_acct_uses_ou_v bau,
    ce_gl_accounts_ccid gla,
    ce_bank_accounts ba,
    ce_system_parameters sys
  where
    cr.cash_receipt_id = crh.cash_receipt_id and
    cr.remit_bank_acct_use_id = bau.bank_acct_use_id and
    bau.bank_account_id = :p_bank_account_id and
    bau.org_id = cr.org_id and
    bau.bank_account_id = ba.bank_account_id and
    ba.account_owner_org_id = sys.legal_entity_id and
    bau.ar_use_enable_flag = 'Y' and
    gla.bank_acct_use_id = bau.bank_acct_use_id and
    crh.account_code_combination_id = gla.ar_asset_ccid and
    crh.status in('REMITTED', 'CLEARED') and
    crh.gl_date <= :c_as_of_date and
    crh.gl_date >= sys.cashbook_begin_date and
    crh.gl_posted_date is not null and
    crh.created_from <> 'RATE ADJUSTMENT TRIGGER' and
    crh2.cash_receipt_id = crh.cash_receipt_id and
    crh2.cash_receipt_history_id = crh.reversal_cash_receipt_hist_id and
    crh2.status = 'REVERSED' and
    crh2.gl_date <= :c_as_of_date and
    crh2.gl_date >= sys.cashbook_begin_date and
    cu.cust_account_id(+) = cr.pay_from_customer and
    hz.party_id(+) = cu.party_id and
    arm.receipt_method_id = cr.receipt_method_id and
    (cr.status <> 'REV' or
     (cr.status = 'REV' and crh.reversal_gl_date > :c_as_of_date)
    ) and
    not exists
      ( select null
        from
          ce_statement_recon_gt_v sr,
          ce_statement_lines sl,
          ce_statement_headers sh
        where
          sr.reference_id = crh.cash_receipt_history_id and
          sr.reference_type = 'RECEIPT' and
          sr.status_flag = 'M' and
          sr.current_record_flag = 'Y' and
          sl.statement_line_id = sr.statement_line_id and
          sl.statement_header_id = sh.statement_header_id and
          sh.bank_account_id = :p_bank_account_id and
          sh.statement_date <= :c_as_of_date
      )
),
q_ap_payments as
(
  /* query 1: checks have not hit gl cash, but are reconciled. (query 1 of union)
     query 2: checks have hit gl cash, but are not reconciled. (query 2 of union) */
  select
    'PAYMENT'                record_type,
    fnd_access_control_util.get_org_name(c.org_id)
                             operating_unit,
    to_char(c.check_number)  transaction_number,
    xle.event_date           gl_date,
    c.check_date             effective_date,
    c.vendor_name            agent,
    c.payment_method_code    payment_method,
    null                     transaction_subtype,
    null                     je_name,
    to_number(null)          je_line,
    null                     je_line_type,
    to_date(null)            je_posted_date,
    null                     statement_number,
    to_number(null)          statement_line,
    null                     statement_trx_type,
    c.status_lookup_code     status,
    c.currency_code          currency,
    c.amount                 transaction_amount,
    decode(:c_bank_curr_dsp,
           :c_gl_currency_code,nvl(nvl(c.cleared_base_amount,c.base_amount),c.amount)
                              ,c.amount
          )                  accounted_amount,
    to_number(null)          gl_end_balance,
    coalesce(gla.ap_asset_ccid,gla.asset_code_combination_id,ba.asset_code_combination_id) cash_account_ccid
  from
    ap_checks_all c,
    ce_bank_acct_uses_all bau,
    ce_gl_accounts_ccid gla,
    ce_security_profiles_gt ou,
    ce_bank_accounts ba,
    ce_system_parameters sys,
    xla_transaction_entities trx,
    xla_events xle
  where
    c.check_date <=   trunc(:c_as_of_date) + 1 - 1/24/60/60 and
    c.ce_bank_acct_use_id = bau.bank_acct_use_id and
    c.org_id = bau.org_id and
    bau.bank_account_id = :p_bank_account_id and
    gla.bank_acct_use_id = bau.bank_acct_use_id and
    bau.org_id = ou.organization_id and
    ou.organization_type = 'OPERATING_UNIT' and
    bau.bank_account_id = ba.bank_account_id and
    ba.account_owner_org_id = sys.legal_entity_id and
    trx.application_id = 200 and
    trx.ledger_id = sys.set_of_books_id and
    nvl(trx.source_id_int_1,-99) = c.check_id and
    trx.entity_code = 'AP_PAYMENTS' and
    xle.application_id = trx.application_id and
    xle.entity_id = trx.entity_id and
    xle.event_type_code not in ('PAYMENT CANCELLED','REFUND CANCELLED') and
    xle.event_number =
      ( select max(event_number)
        from
          xla_events xe2
        where xe2.application_id = xle.application_id and
          xe2.entity_id = xle.entity_id and
          xe2.event_date <= :c_as_of_date and
          xe2.event_date >= sys.cashbook_begin_date and
          xe2.event_status_code = 'P' and
          xe2.event_type_code not in
            ('PAYMENT MATURITY ADJUSTED',
             'MANUAL PAYMENT ADJUSTED',
             'PAYMENT ADJUSTED',
             'PAYMENT CLEARING ADJUSTED',
             'MANUAL REFUND ADJUSTED',
             'REFUND ADJUSTED'
            )
      ) and
    exists
      ( select  /*+ push_subq no_unnest*/ 1
        from
          ap_payment_history_all h2
        where
          h2.check_id = c.check_id and
          h2.transaction_type like decode(c.void_date, null, h2.transaction_type,'%CANCEL%')
      ) and
    not exists
      ( select null
        from
          xla_ae_headers xeh,
          xla_ae_lines ael1
        where
          xeh.event_id = xle.event_id and
          xeh.application_id = xle.application_id and
          ael1.application_id = xeh.application_id and
          xeh.entity_id = xle.entity_id and
          ael1.ae_header_id = xeh.ae_header_id and
          xeh.event_type_code not in
            ('PAYMENT UNCLEARED',
             'PAYMENT CANCELLED',