---

WITH target_transaction as materialized (
    select
        t.*
    from back_office.transaction as t
    where t.operation_id = :operation_id

    union all

    select
        t.*
    from back_office.transaction_without_effective_date as t
    where t.operation_id = :operation_id
)
SELECT
  t.id as id,
  trim(to_char(t.value, '99 999 999 999 999 990D99')) as value,
  case 
  when a_cr.owner_sharer_id is not null then 'Участник'
  when a_cr.owner_contract_id is not null then 'Договор'
  when a_cr.owner_individual_id is not null then 'Физическое лицо'
  when a_cr.owner_entity_id is not null then 'Юридическое лицо'
end as accountLevelCredit,
  case
when at_cr.mnemonics = 'Р(П) ИД' then 'Выплатной резерв'
when at_cr.mnemonics = 'Р(С) ИД' then 'Резерв срочных выплат'
end as accountType,
  at_cr.mnemonics as accountTypeCredit,
  case 
  when a_cr.owner_sharer_id is not null
  then 
   '<a target="_blank" href="view?viewCode=Client_list&id='||si_cr.id||'">'||rtrim(si_cr.last_name || ' ' || si_cr.first_name || ' ' || 
  coalesce(si_cr.middle_name, ''))||'</a>' 
  when a_cr.owner_contract_id is not null 
 then
 '<a target="_blank" href="view?viewCode=7HPZX8vF&id='||c_cr.id||'"target="_blank">'||c_cr.number||'</a>'
  when a_cr.owner_individual_id is not null
  then 
  '<a target="_blank" href="view?viewCode=Client_list&id='||i_cr.id||'">'||rtrim(i_cr.last_name || ' ' || i_cr.first_name || ' ' || 
   coalesce(i_cr.middle_name, ''))||'</a>' 
  when a_cr.owner_entity_id is not null 
  then
  e_cr.short_name
end as accountOwnerCredit,
  CASE 
WHEN p.accrual_repurchase_id is not null THEN '<a target="_blank" href="view?viewCode=fCwI2nmi&id=' || ap.id || '"target="_blank">Начисление выкуп. суммы № '  || ap.id|| '</a>'

WHEN p.accrual_pension_id is not null THEN '<a target="_blank" href="view?viewCode=s4s34pY2&id=' || al.id || '"target="_blank">Начисление пенсии № '  || al.id|| '</a>'
END as accrual_document,
  case when a_cr.owner_sharer_id is not null then
'<a target="_blank" href="view?viewCode=7HPZX8vF&id='|| sc_cr.id||'"target="_blank">'|| sc_cr.number||'</a>'
end as sharerContractCredit,
  case when a_cr.owner_sharer_id is not null then
'<a target="_blank" href="view?viewCode=Pzbmp8Pg&id='|| s_cr.id||'"target="_blank">'|| s_cr.number||'</a>'
end as sharerCredit,
  ot.name as operationTypeName,
  at_db.mnemonics as accountTypeDebit,
  case when a_db.owner_sharer_id is not null then 'Участник' when a_db.owner_contract_id is not null then 'Договор' when a_db.owner_individual_id is not null then 'Физическое лицо' when a_db.owner_entity_id is not null then 'Юридическое лицо' end as accountLevelDebit,
  case 
  when a_db.owner_sharer_id is not null then '<a target="_blank" href="view?viewCode=Client_list&id='||si_db.id||'">'||rtrim(si_db.last_name || ' ' || si_db.first_name || ' ' || coalesce(si_db.middle_name, ''))||'</a>' 
  when a_db.owner_contract_id is not null then '<a target="_blank" href="view?viewCode=7HPZX8vF&id='||c_db.id||'"target="_blank">'||c_db.number||'</a>'
  when a_db.owner_individual_id is not null then '<a target="_blank" href="view?viewCode=Client_list&id='||i_db.id||'">'||rtrim(i_db.last_name || ' ' || i_db.first_name || ' ' || coalesce(i_db.middle_name, ''))||'</a>' 
  when a_db.owner_entity_id is not null then e_db.short_name
end as accountOwnerDebit,
  case when a_db.owner_sharer_id is not null then sc_db.number end as sharerContractDebit,
  case when a_db.owner_sharer_id is not null then
'<a target="_blank" href="view?viewCode=Pzbmp8Pg&id='|| s_db.id||'"target="_blank">'|| s_db.number||'</a>'
end as sharerDebit,
  to_char(t.effective_date, 'dd.MM.yyyy') as effectiveDate,
  to_char(t.operation_date, 'dd.MM.yyyy') as operationDate,
  to_char(t.period_date, 'dd.MM.yyyy') as periodDate,
  t.comment as comment,
  to_char(t.version, 'DD.MM.YYYY HH24:MI:SS') as version 
FROM target_transaction as t
	join back_office.operation o on t.operation_id = o.id
	join back_office.operation_type ot on o.operation_type_id = ot.id

	-- счет, кредит
	left join back_office.account a_cr on t.credit_account_id = a_cr.id
	left join back_office.account_type at_cr on a_cr.account_type_id = at_cr.id
	-- счет участника, кредит
	left join ourpension.sharer s_cr on s_cr.id = a_cr.owner_sharer_id
	left join ourpension.contract sc_cr on sc_cr.id = s_cr.contract_id
	left join ourpension.individual si_cr on si_cr.id = s_cr.individual_id
	-- счет договора, кредит
	left join ourpension.contract c_cr on c_cr.id = a_cr.owner_contract_id
	-- счет фл, кредит
	left join ourpension.individual i_cr on i_cr.id = a_cr.owner_individual_id
	-- счет юл, кредит
	left join ourpension.entity e_cr on e_cr.id = a_cr.owner_entity_id

	-- счет, дебит
	left join back_office.account a_db on t.debit_account_id = a_db.id
	left join back_office.account_type at_db on a_db.account_type_id = at_db.id
	-- счет участника, дебит
	left join ourpension.sharer s_db on s_db.id = a_db.owner_sharer_id
	left join ourpension.contract sc_db on sc_db.id = s_db.contract_id
	left join ourpension.individual si_db on si_db.id = s_db.individual_id
	-- счет договора, дебит
	left join ourpension.contract c_db on c_db.id = a_db.owner_contract_id
	-- счет фл, дебит
	left join ourpension.individual i_db on i_db.id = a_db.owner_individual_id
	-- счет юл, дебит
	left join ourpension.entity e_db on e_db.id = a_db.owner_entity_id
	
	-- 1. new! Документ начисления 
	left join back_office.payment p on   p.id = t.payment_id
		-- 1.1 Начисление выкупной суммы
	 left join back_office.accrual_repurchase_amount_payments_details pd on pd.id = p.accrual_repurchase_id
	 left join back_office.accrual_repurchase_amount_payments ap on ap.id = pd.accrual_repurchase_amount_payments_id
	
	  --  1.2 Начисление пенсии
	 left join back_office.accrual_detail ad on ad.id = p.accrual_pension_id
	 left join back_office.accrual_list al  on al.id  = ad.accrual_list_id