---

WITH spv 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 = '05'
		and opaa.appeal_date <= :date_of_application
	and aibd.accepted = coalesce(:br_date, aibd.accepted)
)
SELECT
  spv.id as id,
  to_char(spv.appeal_date, 'DD.MM.YYYY') as appeal_date,
  spv.accepted as accepted,
  spv.reg_numb as reg_numb,
  spv.short_name as short_name,
  spv.fio_zl as fio_zl,
  spv.i_zl as i_zl,
  spv.contract_numb as contract_numb,
  spv.payment_way as payment_way,
  spv.address_text as address_text,
  spv.personal_account as personal_account,
  spv.bic as bic,
  spv.bank_name as bank_name,
  spv.corraccount as corraccount,
  spv.bank_addres as bank_addres,
  to_char(spv.br_date, 'DD.MM.YYYY') as br_date 
FROM spv