---

SELECT
  e.id as id,
  e.code as code,
  uat.activity_rus_type as service_type_rus,
  case when o.id is not null then to_char(o.operation_date, 'DD.MM.YYYY')
when io.id is not null then to_char(io.date, 'DD.MM.YYYY')
end as operation_date,
  case when o.id is not null then 
'<a target="_blank" href="view?viewCode=9CNmoQeC&id=' || o.id || '" target="_blank">' || ot.name || '</a>'
when io.id is not null then 
'<a target="_blank" href="view?viewCode=NL0wI3vg&id=' || io.id || '" target="_blank">' || 'ВХ ПП ' || io.number || ' от ' || io.date || '</a>'
end as operation_id,
  case when o.id is not null then trim(to_char( coalesce(tran.sumoper, 0) , '99 999 999 999 999 990D99'))
 when io.id is not null then trim(to_char( coalesce(io.value, 0) , '99 999 999 999 999 990D99')) end as operation_sum,
  e.name as eps_name,
  ed.line_number as line_number,
  ed.account_dt as account_dt,
  ed.kind_sub_dt1 as kind_sub_dt1,
  ed.kind_sub_dt2 as kind_sub_dt2,
  ed.kind_sub_dt3 as kind_sub_dt3,
  ed.account_ct as account_ct,
  ed.kind_sub_ct1 as kind_sub_ct1,
  ed.kind_sub_ct2 as kind_sub_ct2,
  ed.kind_sub_ct3 as kind_sub_ct3,
  ed.oper_content as oper_content,
  case when ed.is_main_transaction then '<span class="glyphicon glyphicon-ok" aria-hidden="true"></span>' 
end as is_main_transaction,
  ed.trans_description as trans_description 
FROM back_office.eps_transaction et
join ourpension.eps e on e.id = et.eps_id 
join ourpension.eps_detail ed on ed.eps_id = e.id --ed.id = et.eps_detail_id
join back_office.eps_transaction_source s on s.id = et.eps_transaction_source_id

left join back_office.operation o on o.id = s.operation_id
left join back_office.operation_type ot on ot.id = o.operation_type_id

left join back_office.incoming_order io on io.id = s.incoming_order_id

left join ourpension.uv_activity_type uat on uat.client_type = 0 and uat.activity_eng_type = e.service_type

left join lateral
(select sum(t.value) as sumoper 
 from back_office.transaction t
where t.operation_id = o.id
) tran on true