---

WITH ev as(
        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)
)
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