---

SELECT
  o.id as id,
  '<a target="_blank" href="view?viewCode=9CNmoQeC&id=' || o.id || '" target="_blank">' || ot.name || '</a>' as name,
  o.comment as comment,
  to_char(o.operation_date, 'dd.MM.yyyy') as operationDate,
  trim(to_char((select sum(t.value) as sumoper from back_office.transaction t where t.operation_id = o.id), '99 999 999 999 999 990D99')) as sumoper,
  to_char(o.effective_date, 'dd.MM.yyyy') as effectiveDate,
  to_char(o.period_date, 'dd.MM.yyyy') as periodDate,
  to_char(o.created, 'DD.MM.YYYY HH24:MI:SS') as created,
  c.last_name || ' ' ||substr(c.first_name,1,1) ||'.'||coalesce(substr(c.middle_name,1,1)||'.', '') as author,
  to_char(o.approved, 'DD.MM.YYYY HH24:MI:SS') as approved,
  a.last_name || ' ' || coalesce(substr(a.first_name,1,1)||'.', '') ||coalesce(substr(a.middle_name,1,1)||'.', '') as approver,
  to_char(o.deleted, 'DD.MM.YYYY HH24:MI:SS') as deleted,
  r.last_name || ' ' ||substr(r.first_name,1,1) ||'.'|| coalesce(substr(r.middle_name,1,1)||'.', '') as remover,
  '<a href="queryDataSource/queryDataSourceFile/download?id='||f.id||'">'||f.name||'</a>' as file,
  case
    when o.pension_calculation_id is not null then '<a target="_blank" href="view?viewCode=fgcx21i8&id='||pc.id||'"target="_blank">'||'Расчет пенсии от '||to_char(pc.calculation_date, 'dd.MM.yyyy')||'</a>'
    when o.termination_contract_calculation_id is not null then '<a target="_blank" href="'||'view?viewCode=Js7E4zaZ&id='||tc.id||'">'||'Расчет выкупной суммы от '||to_char(tc.version,'dd.MM.yyyy')||'</a>'
    when o.inheritance_calculation_id is not null then '<a target="_blank" href="'||'view?viewCode=DZg6CX4G&id='||ic.id||'">'||'Расчет наследуемой суммы от '||to_char(ic.version,'dd.MM.yyyy')||'</a>'
end as calculation,
  case 
when o.payment_register_id is not null then
'<a target="_blank" href="view?viewCode=QoQTOUeG&id='||pr.id||'">'||pr.register_number||'</a>'
when o.ops_payment_register_id is not  null then
'<a target="_blank" href="view?viewCode=xT40mPRG&id='||opr.id||'">'||opr.number||'</a>'
end as paymentRegister,
  od.number as operationDocumentNumber,
  to_char(od.date, 'dd.MM.yyyy') as operationDocumentDate,
  to_char(o.version, 'DD.MM.YYYY HH24:MI:SS') as version 
FROM back_office.operation o
	join back_office.operation_type ot on o.operation_type_id = ot.id
	join ourpension.agent c on o.author_agent_id = c.id
	left join ourpension.agent r on o.remover_agent_id = r.id
	left join ourpension.agent a on o.approver_agent_id = a.id
left join dev.query_datasource_file f on o.query_datasource_file_id = f.id
left join back_office.ops_payment_decision pd on o.ops_payment_decision_id = pd.id
left join back_office.payment_register pr on pr.id =o.payment_register_id
left join back_office.ops_payment_register opr on opr.id =o.ops_payment_register_id
left join back_office.pension_calculation pc on pc.id =o.pension_calculation_id
left join back_office.termination_contract_calculation tc on tc.id =o.termination_contract_calculation_id
left join back_office.inheritance_calculation ic on ic.id =o.inheritance_calculation_id
left join back_office.operation_document od on od.id = o.operation_document_id