---

WITH ev AS (

    /* ============================================================
       1. СТАРАЯ ЛОГИКА
       Исторические заявления, где ЕВ = ops_payment_type_code '01'
       ============================================================ */

    SELECT
        opaa.id,
        opaa.appeal_date,

        CASE
            WHEN opaa.accepted IS NOT NULL
                THEN 'Да'
            ELSE 'Нет'
        END AS accepted,

        opaa.number AS reg_numb,

        dpt.short_name,

        upper(
            i.last_name || ' ' ||
            i.first_name || ' ' ||
            i.middle_name
        ) AS fio_zl,

        i.insurance_number AS i_zl,

        c.number AS contract_numb,

        pt.name AS payment_way,

        a.address_text,

        aibd.personal_account,
        aibd.bic,

        br.bank_name,
        br.corraccount,

        br.place || ' ' || br.address AS bank_addres,

        aibd.accepted AS br_date

    FROM ourpension.ops_pension_assignment_application opaa

    LEFT JOIN ourpension.payment_type pt
        ON opaa.payment_way = pt.code

    JOIN ourpension.sharer s
        ON s.id = opaa.sharer_id

    JOIN ourpension.contract c
        ON c.id = s.contract_id

    JOIN ourpension.individual i
        ON i.id = c.individual_id

    JOIN ourpension.address a
        ON i.registration_address_id = a.id

    JOIN ourpension.decision_payment_type dpt
        ON opaa.decision_payment_type_code = dpt.code

    LEFT JOIN ourpension.application_individual_bank_detail aibd
        ON opaa.bank_detail_id = aibd.id

    LEFT JOIN ourpension.bankru br
        ON aibd.bic = br.bic

    WHERE opaa.ops_payment_type_code = '01'

      AND opaa.appeal_date BETWEEN :start_date AND :end_date

      AND aibd.accepted = COALESCE(:br_date, aibd.accepted)


    UNION ALL


    /* ============================================================
       2. НОВАЯ ЛОГИКА
       Современные ЕВ / ЕВО определяем уже по РЕШЕНИЮ
       ============================================================ */

    SELECT
        opaa.id,
        opaa.appeal_date,

        CASE
            WHEN opaa.accepted IS NOT NULL
                THEN 'Да'
            ELSE 'Нет'
        END AS accepted,

        opaa.number AS reg_numb,

        dpt.short_name,

        upper(
            i.last_name || ' ' ||
            i.first_name || ' ' ||
            i.middle_name
        ) AS fio_zl,

        i.insurance_number AS i_zl,

        c.number AS contract_numb,

        pt.name AS payment_way,

        a.address_text,

        aibd.personal_account,
        aibd.bic,

        br.bank_name,
        br.corraccount,

        br.place || ' ' || br.address AS bank_addres,

        aibd.accepted AS br_date

    FROM back_office.ops_payment_decision opd

    JOIN ourpension.ops_pension_assignment_application opaa
        ON opaa.id = opd.ops_pension_assignment_application_id

    LEFT JOIN ourpension.payment_type pt
        ON opaa.payment_way = pt.code

    JOIN ourpension.sharer s
        ON s.id = opaa.sharer_id

    JOIN ourpension.contract c
        ON c.id = s.contract_id

    JOIN ourpension.individual i
        ON i.id = c.individual_id

    JOIN ourpension.address a
        ON i.registration_address_id = a.id

    /* В новой модели тип выплаты берем из решения */
    JOIN ourpension.decision_payment_type dpt
        ON opd.decision_payment_type_code = dpt.code

    LEFT JOIN ourpension.application_individual_bank_detail aibd
        ON opaa.bank_detail_id = aibd.id

    LEFT JOIN ourpension.bankru br
        ON aibd.bic = br.bic

    WHERE opd.decision_payment_type_code IN ('01', '16')

      /* Берем только принятые решения */
      AND opd.accepted IS NOT NULL

      /* Период современной части — дата решения */
      AND opd.date BETWEEN :start_date AND :end_date

      /* Не берем отмененные / удаленные решения */
      AND opd.rejected IS NULL
      AND opd.canceled IS NULL

      AND aibd.accepted = COALESCE(:br_date, aibd.accepted)
)
SELECT
  ev.id as id,
  to_char(ev.appeal_date, 'DD.MM.YYYY') as appeal_date,
  ev.accepted as accepted,
  ev.reg_numb as reg_numb,
  ev.short_name as short_name,
  ev.fio_zl as fio_zl,
  ev.i_zl as i_zl,
  ev.contract_numb as contract_numb,
  ev.payment_way as payment_way,
  ev.address_text as address_text,
  ev.personal_account as personal_account,
  ev.bic as bic,
  ev.bank_name as bank_name,
  ev.corraccount as corraccount,
  ev.bank_addres as bank_addres,
  to_char(ev.br_date, 'DD.MM.YYYY') as br_date 
FROM ev