---

ЗЛ о назначении ЛП
WITH np as (
	select opaa.id as app, -- Заявление
		opd.id as dec, -- Решение
		case when opd.accepted is not null 
			then 'Да'
			else 'Нет'
		end as accepted_dec, -- Решение провередено
		opd.number as dec_numb, -- Номер решения
		opd.pension_date, -- Дата назначения
		opaa.appeal_date, -- Дата обращения
		ld.pension_value, -- Сумма выплат
		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 back_office.ops_payment_decision opd on opaa.id = opd.ops_pension_assignment_application_id 
	left join lateral (
		select coalesce(opdl.pension_value, opdla.pension_value, opdft.pension_value, opdo.payment_value) as pension_value,
			opd.date
		from back_office.ops_payment_decision opd		
		left join back_office.ops_payment_decision_lifetime opdl on opd.id = opdl.ops_payment_decision_id
		left join back_office.ops_payment_decision_lifetime_all opdla on opd.id = opdla.ops_payment_decision_id
		left join back_office.ops_payment_decision_fixed_term opdft on opd.id = opdft.ops_payment_decision_id
		left join back_office.ops_payment_decision_onetime opdo on opd.id = opdo.ops_payment_decision_id
		where opaa.id = opd.ops_pension_assignment_application_id 
			and opd.decision_type_code in ('УСТН','КОРР','ВОЗБ','ВОСТ')
			and opd.accepted is not null
			and opd.date < (
				select coalesce(min(d_min.date),cast('9999-12-31' as date))
				from back_office.ops_payment_decision d_min
				where opaa.id = d_min.ops_pension_assignment_application_id 
					and d_min.decision_type_code in ('ПРЕК', 'ПРОС')
					and opd.accepted is not null
			)
		order by opd.date desc 
		limit 1
	) ld on true
	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 in ('02', '04')
		and opd.date > :start_date
		and opd.date <= :end_date
	and aibd.accepted = coalesce(:br_date, aibd.accepted)
)
SELECT
  np.app as app,
  np.dec as dec,
  np.accepted_dec as accepted_dec,
  np.accepted as accepted,
  to_char(np.appeal_date, 'DD.MM.YYYY') as appeal_date,
  np.reg_numb as reg_numb,
  np.dec_numb as dec_numb,
  to_char(np.pension_date, 'DD.MM.YYYY') as pension_date,
  np.pension_value as pension_value,
  np.short_name as short_name,
  np.fio_zl as fio_zl,
  np.i_zl as i_zl,
  np.contract_numb as contract_numb,
  np.payment_way as payment_way,
  np.address_text as address_text,
  np.personal_account as personal_account,
  np.bic as bic,
  np.bank_name as bank_name,
  np.corraccount as corraccount,
  np.bank_addres as bank_addres,
  to_char(np.br_date, 'DD.MM.YYYY') as br_date 
FROM np


ПП ОПС
WITH pp as (
    select opa.id as application,
        oad.id as decision,
        p.payment_value, -- Сумма выплаты
        case when opa.accepted is not null
            then 'Да'
            else 'Нет'
        end as accepted, -- Заявление проведено
        opa.number as number_app, -- Номер заявления
        pt.name, -- Способ выплат
        opa.date, -- Дата заявления
        opa.registration, -- Дата регистрации
        opa.appeal_date, -- Дата обращения
        c.number as contract_number, -- Договор ОПС
        case when opa.assignee_type = 'LAW'
            then 'По закону'
            else 'По заявлению'
        end as assignee_type, -- Основание
        upper(i.last_name || ' ' || i.first_name || ' ' || i.middle_name) as fio_zl, -- ЗЛ
        i.insurance_number as i_zl, -- Снилс ЗЛ
        upper(ai.last_name || ' ' || ai.first_name || ' ' || ai.middle_name) as fio_pp, -- ПП
        ai.insurance_number as i_pp, -- Снилс ПП
        aibd.bic,
        br.bank_name,
        br.corraccount,
        aa.address_text as address_reg, -- Адрес регистрации
        aar.address_text as address_pay, -- Адрес для выплат
	aibd.accepted as br_date
    from ourpension.ops_assignee_payment opa
    left join back_office.ops_assignee_decision oad on opa.id = oad.ops_assignee_payment_id
    left join ourpension.payment_type pt on opa.payment_way = pt.code
    join ourpension.sharer s on s.id = opa.sharer_id
    join ourpension.contract c on c.id = s.contract_id
    join ourpension.individual i on i.id = c.individual_id
    join ourpension.individual ai on ai.id = opa.assignee_id
    join ourpension.address aa on ai.registration_address_id = aa.id
    left join ourpension.address aar on ai.residential_address_id = aar.id
    left join ourpension.application_individual_bank_detail aibd on opa.bank_detail_id = aibd.id
    left join ourpension.bankru br on aibd.bic = br.bic
    left join back_office.ops_assignee_decision_payment p on p.ops_assignee_decision_id = oad.id
    where oad.date > :start_date
        and oad.date <= :end_date
	and aibd.accepted = coalesce(:br_date, aibd.accepted)

)
SELECT
  pp.application as application,
  pp.decision as decision,
  pp.payment_value as payment_value,
  pp.accepted as accepted,
  pp.name as name,
  pp.number_app as number_app,
  to_char(pp.date, 'DD.MM.YYYY') as date,
  to_char(pp.registration, 'DD.MM.YYYY') as registration,
  to_char(pp.appeal_date, 'DD.MM.YYYY') as appeal_date,
  pp.contract_number as contract_number,
  pp.assignee_type as assignee_type,
  pp.fio_zl as fio_zl,
  pp.i_zl as i_zl,
  pp.fio_pp as fio_pp,
  pp.i_pp as i_pp,
  pp.bic as bic,
  pp.bank_name as bank_name,
  pp.corraccount as corraccount,
  pp.address_reg as address_reg,
  pp.address_pay as address_pay,
  to_char(pp.br_date, 'DD.MM.YYYY') as br_date 
FROM pp



ЗЛ о назначении СПВ
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.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)
	and (
    cast(:br_date as date) is null
    or cast(aibd.accepted as date) = cast(:br_date as date)
)
)
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