---

with operation (id,period_date,ef_date,cred_mnemonics,deb_mnemonics,value,operation_type_id) as (
  select o.id, o.period_date, o.effective_date as ef_date, cat.mnemonics as cred_mnemonics, dat.mnemonics as deb_mnemonics, t.value, o.operation_type_id
  from back_office.operation o
  join back_office.transaction t on t.operation_id = o.id
  join back_office.account ca on ca.id = t.credit_account_id
  left join back_office.account da on da.id = t.debit_account_id
  left join back_office.account_type dat on dat.id = da.account_type_id
  left join back_office.account_type cat on cat.id = ca.account_type_id
  where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
    and ca.owner_sharer_id = $P{sharerId}

  union all -- Возврат ОПС
  select o.id, o.period_date, o.effective_date, cat.mnemonics, dat.mnemonics, t.value, o.operation_type_id
  from back_office.operation o
  join back_office.ops_payment_return r on r.id = o.ops_payment_return_id
  join back_office.ops_payment_register_decision rd on rd.id = r.ops_payment_register_decision_id
  join back_office.ops_payment_decision d on d.id = rd.ops_payment_decision_id
  join ourpension.ops_pension_assignment_application a on a.id = d.ops_pension_assignment_application_id
  join back_office.transaction t on t.operation_id = o.id
  join back_office.account da on da.id = t.debit_account_id
  join back_office.account ca on ca.id = t.credit_account_id
  join back_office.account_type dat on dat.id = da.account_type_id
  join back_office.account_type cat on cat.id = ca.account_type_id
  where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
    and ca.owner_individual_id = rd.recipient_individual_id
    and a.sharer_id = $P{sharerId}

  union all -- Возврат НПО
  select o.id, o.period_date, o.effective_date, cat.mnemonics, dat.mnemonics, t.value, o.operation_type_id
  from back_office.operation o
  join back_office.payment_return pr on pr.id = o.payment_return_id
  join back_office.payment p on p.id = pr.payment_id
  join back_office.payment_register r on p.payment_register_id = r.id
  join back_office.transaction t on t.operation_id = o.id
  join back_office.account da on da.id = t.debit_account_id
  join back_office.account ca on ca.id = t.credit_account_id
  join back_office.account_type dat on dat.id = da.account_type_id
  join back_office.account_type cat on cat.id = ca.account_type_id
  where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
    and ca.owner_individual_id = p.recipient_individual_id
    and p.sharer_id = $P{sharerId}

  union all -- Повторная выплата возврата
  select o.id, o.period_date, o.effective_date, cat.mnemonics, dat.mnemonics, t.value, o.operation_type_id
  from back_office.operation o
  join back_office.ops_payment_register_decision rd on rd.ops_payment_register_id = o.ops_payment_register_id
  join back_office.ops_payment_return r on r.id = rd.ops_payment_return_id
  join back_office.ops_payment_decision d on d.id = rd.ops_payment_decision_id
  join ourpension.ops_pension_assignment_application a on a.id = d.ops_pension_assignment_application_id
  join back_office.transaction t on t.operation_id = o.id
  join back_office.account da on da.id = t.debit_account_id
  join back_office.account ca on ca.id = t.credit_account_id
  join back_office.account_type dat on dat.id = da.account_type_id
  join back_office.account_type cat on cat.id = ca.account_type_id
  where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
    and ca.owner_individual_id = rd.recipient_individual_id
    and a.sharer_id = $P{sharerId}
)
select 
  to_char(o.ef_date, 'DD.MM.YYYY') as effective_date, 
  m.name as month_period, 
  extract(year from o.period_date) as year_period, 
  ot.name, 
  o.value, 
  o.cred_mnemonics, 
  o.ef_date
from operation o
join back_office.operation_type ot on ot.id = o.operation_type_id
left join data.month as m on m.month_number = extract(month from o.period_date)
where o.cred_mnemonics is not null 
  and ot.code <> 'PDS_RETURN_NDFL_REDEMPTION'
  and ot.display_in_statement = true
order by o.ef_date;