---

Вставка записи
insert into back_office.report_conformity_pu_bu(
	m_y_report,
	"version",
	service_type,
	user_id,
	form_date
)
values (
       :date_report,
			 now(),
			 :service_type,
			 :user_id,
			 :date_form
)


Вставка детализации
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 te.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 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 te.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 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, null 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, null, 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


Данные БУ
MERGE INTO back_office.report_conformity_pu_bu_detail as target
USING (with str_acc  as (
  select string_agg(sa.account,',') as acc
  from ourpension.spr_accounts_indicator_bu sa
)
 select t.account, t.debitbalance, t.creditbalance, t.uidpensionscheme, t.uidsubtypeactivity
  from str_acc s
  join mtrustgate.get_list_account_balances_bu(to_date(:endDate, 'DD.MM.YYYY'), '1147799009115', :service_type, s.acc) t on 1=1
) as source
on (target.account = source.account 
		and target.report_conformity_pu_bu_id =  currval('back_office.report_conformity_pu_bu_id_seq') 
		and target.uidpensionscheme = source.uidpensionscheme 
	 and target.uidsubtypeactivity = source.uidsubtypeactivity)
WHEN MATCHED THEN
  update set (sum_dt_bu, sum_ct_bu) = (select source.debitbalance,
	             source.creditbalance)
WHEN NOT MATCHED THEN
  INSERT (
	report_conformity_pu_bu_id,
	account,
	uidpensionscheme,	
	uidsubtypeactivity,
	sum_dt_bu,
	sum_ct_bu)
	VALUES ( currval('back_office.report_conformity_pu_bu_id_seq'),
			source.account, source.uidpensionscheme, source.uidsubtypeactivity, 
					source.debitbalance, source.creditbalance
				 )