---

with dates as (
 select to_date(:startDate, 'DD.MM.YYYY') as startDate, 
	      to_date(:endDate, 'DD.MM.YYYY') as endDate
)
, temp_sum as (
    select ed.account_dt as acc, 
	         coalesce(ed.pension_scheme_id, et.pens_schema_dt) as pension_scheme_id, 
	     tran.strategy_id, et.contract_class_code,
	     sum(tran.sumoper) as sum_dt, null as sum_ct
from back_office.operation o
join dates d 
  on o.operation_date between d.startDate and d.endDate
join back_office.eps_transaction_source s 
  on o.id = s.operation_id
join back_office.eps_transaction et
  on s.id = et.eps_transaction_source_id
join ourpension.eps e 
  on e.id = et.eps_id
join ourpension.eps_detail ed
  on ed.eps_id = e.id 
left join lateral (
    select
        coalesce(et.strategy_id, te.strategy_id) as strategy_id,
        sum(t.value) as sumoper
    from back_office.transaction t
    left join back_office.transaction_extended te
        on te.transaction_id = t.id
    where t.operation_id = o.id
    group by coalesce(et.strategy_id, te.strategy_id)
) tran on true
where e."service_type" = :service_type
group by ed.account_dt, ed.pension_scheme_id, tran.strategy_id, 
	       et.contract_class_code, et.pens_schema_dt
union all
select ed.account_ct, coalesce(ed.pension_scheme_id, et.pens_schema_ct) as pension_scheme_id, tran.strategy_id, 
	     et.contract_class_code, null, sum(tran.sumoper)
from back_office.operation o
join dates d 
  on o.operation_date between d.startDate and d.endDate
join back_office.eps_transaction_source s 
  on o.id = s.operation_id
join back_office.eps_transaction et
  on s.id = et.eps_transaction_source_id
join ourpension.eps e 
  on e.id = et.eps_id
join ourpension.eps_detail ed
  on ed.eps_id = e.id 
left join lateral (
    select
        coalesce(et.strategy_id, te.strategy_id) as strategy_id,
        sum(t.value) as sumoper
    from back_office.transaction t
    left join back_office.transaction_extended te
        on te.transaction_id = t.id
    where t.operation_id = o.id
    group by coalesce(et.strategy_id, te.strategy_id)
) tran on true
where e."service_type" = :service_type
group by ed.account_ct, ed.pension_scheme_id, tran.strategy_id, 
	       et.contract_class_code, et.pens_schema_ct
union all
select ed.account_dt, coalesce(ed.pension_scheme_id, et.pens_schema_dt) as pension_scheme_id, et.strategy_id as strategy_id, 
	     et.contract_class_code, coalesce(io.value, 0) as sum_dt, 
	     null as sum_ct
from back_office.incoming_order io
join dates d 
  on io.date between d.startDate and d.endDate
join back_office.eps_transaction_source s 
  on io.id = s.incoming_order_id
join back_office.eps_transaction et
  on s.id = et.eps_transaction_source_id
join ourpension.eps e 
  on e.id = et.eps_id
join ourpension.eps_detail ed
  on ed.eps_id = e.id
where e."service_type" = :service_type
union all
select ed.account_ct, coalesce(ed.pension_scheme_id, et.pens_schema_ct) as pension_scheme_id, et.strategy_id as strategy_id, et.contract_class_code, 
	     null, coalesce(io.value, 0) as sum_dt
from back_office.incoming_order io
join dates d 
  on io.date between d.startDate and d.endDate
join back_office.eps_transaction_source s 
  on io.id = s.incoming_order_id
join back_office.eps_transaction et
  on s.id = et.eps_transaction_source_id
join ourpension.eps e 
  on e.id = et.eps_id
join ourpension.eps_detail ed
  on ed.eps_id = e.id
where e."service_type" = :service_type
)
insert into back_office.report_conformity_pu_bu_detail(
	report_conformity_pu_bu_id,
	account,
	pension_scheme_id,
	strategy_id,
	contract_class_code,
	sum_dt_pu,
	sum_ct_pu
)
select currval('back_office.report_conformity_pu_bu_id_seq'),
       acc, pension_scheme_id, strategy_id, contract_class_code, 
			 sum(sum_dt) as sum_dt, 
			 sum(sum_ct) as sum_ct
from temp_sum
group by acc, pension_scheme_id, strategy_id, contract_class_code