---

with cte as (
select 2 as cid, 0 as pid,  'текущие обязательства' as name, '' as strateg, 
       sum( case when "account" = '38302'
                 and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end) as sVFA_рu,
       sum( case when "account" = '38902' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end) as ВВА_рu,
       sum(case when "account" in ('39002', '39022', '39023', '39024')
                 and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end) as iVFA_рu,
       sum( case when "account" = '38302'
                 and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end) as sVFA_bu,
       sum( case when "account" = '38902' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end) as ВВА_bu,
       sum(case when "account" in ('39002', '39022', '39023', '39024')
                 and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end) as iVFA_bu
from back_office.report_conformity_pu_bu_detail
where report_conformity_pu_bu_id =  $P{id}
union all
select 3, 0, 'обязательства к выплате', '',
       sum( case when "account" = '38311.1' 
                  and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end) as sVFA_рu,
       sum(case when "account" = '38911.1' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end) as ВВА_рu,
       sum(case when "account" in ('39011.1', '39032.1', '39033.1', '39037.1') 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end) as iVFA_рu,
       sum( case when "account" = '38311.1' 
                  and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum(case when "account" = '38911.1' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum(case when "account" in ('39011.1', '39032.1', '39033.1', '39037.1') 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end)
from back_office.report_conformity_pu_bu_detail
where report_conformity_pu_bu_id = $P{id}
union all
select 4, 0, 'обязательства, не исполненные в срок', '',
       sum(case when "account" = '38311.2'
                 and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end) as sVFA_рu,
       sum(case when "account" = '38911.2' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end) as ВВА_рu,
       sum(case when "account" in ('39011.2', '39032.2', '39033.2', '39037.2') 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end) as iVFA_рu,
       sum(case when "account" = '38311.2'
                 and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum(case when "account" = '38911.2' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum(case when "account" in ('39011.2', '39032.2', '39033.2', '39037.2') 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end)
from back_office.report_conformity_pu_bu_detail
where report_conformity_pu_bu_id = $P{id}
union all
select 5, 0, 'Страховой резерв', '',
       null,
       sum(case when "account" = '39703'
                 and coalesce(contract_class_code, 'INS') = 'INS' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       sum(case when "account" = '39703'
               and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       null,
       sum(case when "account" = '39703'
                 and coalesce(contract_class_code, 'INS') = 'INS' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum(case when "account" = '39703'
               and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end)
from back_office.report_conformity_pu_bu_detail
where report_conformity_pu_bu_id =  $P{id}
union all
select 8, 0, 'Незавершенные (неопознанные) платежи', '',
       null,
       sum(case when "account" = '48309' 
                 and coalesce(contract_class_code, 'INS') = 'INS' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       sum(case when "account" = '48309'
                 and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       null,
       sum(case when "account" = '48309' 
                 and coalesce(contract_class_code, 'INS') = 'INS' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum(case when "account" = '48309'
                 and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end)
from back_office.report_conformity_pu_bu_detail
where report_conformity_pu_bu_id =  $P{id}
union all
select 9, 0, 'Расчеты по пенсионным взносам (переплаты)', '',
       null,
       sum( case when "account" = '48306.1' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       sum( case when "account" = '48306.3' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       null,
       sum( case when "account" = '48306.1' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum( case when "account" = '48306.3' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end)
from back_office.report_conformity_pu_bu_detail
where report_conformity_pu_bu_id =  $P{id}
union all
select 10, 0, 'Расчеты по пенсионным взносам с ФЛ', '',
       null,
       sum( case when "account" = '48301.1' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       sum( case when "account" = '48301.3' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       null,
       sum( case when "account" = '48301.1' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum( case when "account" = '48301.3' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end)
from back_office.report_conformity_pu_bu_detail
where report_conformity_pu_bu_id =  $P{id}
union all
select 11, 0, 'Расчеты по пенсионным взносам с ЮЛ', '',
       null,
       sum( case when "account" = '48303.1' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       sum( case when "account" = '48303.3' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       null,
       sum( case when "account" = '48303.1' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum( case when "account" = '48303.3' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end)
from back_office.report_conformity_pu_bu_detail
where report_conformity_pu_bu_id =  $P{id}
union all
select 13, 0, 'Незавершенные (неопознанные) платежи', '',
       null,
       sum( case when "account" = '48310'
                  and coalesce(contract_class_code, 'INS') = 'INS' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       sum( case when "account" = '48310'
                  and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       null,
       sum( case when "account" = '48310'
                  and coalesce(contract_class_code, 'INS') = 'INS' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum( case when "account" = '48310'
                  and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end)
from back_office.report_conformity_pu_bu_detail
where report_conformity_pu_bu_id =  $P{id}
union all
select 14, 0, 'Расчеты по выплатам по договорам', '',
       null,
       sum( case when "account" = '48305.1' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       sum( case when "account" = '48305.3' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       null,
       sum( case when "account" = '48305.1' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum( case when "account" = '48305.3' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end)
from back_office.report_conformity_pu_bu_detail
where report_conformity_pu_bu_id =  $P{id}
union all
select 15, 0, 'Расчеты по пенсионным взносам с ФЛ', '',
       null,
       sum( case when "account" = '48302.1' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       sum( case when "account" = '48302.3' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       null,
       sum( case when "account" = '48302.1' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum( case when "account" = '48302.3' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end)
from back_office.report_conformity_pu_bu_detail
where report_conformity_pu_bu_id = $P{id}
union all
select 16, 0, 'Расчеты по пенсионным взносам с ЮЛ', '',
       null,
       sum( case when "account" = '48304.1' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       sum( case when "account" = '48304.3' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end),
       null,
       sum( case when "account" = '48304.1' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end),
       sum( case when "account" = '48304.3' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end)
from back_office.report_conformity_pu_bu_detail
where report_conformity_pu_bu_id = $P{id}
),
cte_gr as (
select 1 as cid, 0 as pid, 'РППО, итого' as name, '' as strateg,
  sum (svfa_рu) as svfa_рu,
  sum(ВВА_рu) as ВВА_рu,
  sum(ivfa_рu) as ivfa_рu,
  sum (svfa_bu) as svfa_bu,
  sum (ВВА_bu) as ВВА_bu,
  sum(ivfa_bu) as ivfa_bu
from cte
where cid in (2,3,4)
union all
select 7, 0, 'Дебиторская задолженность, итого', '',
  sum (svfa_рu) as svfa_рu,
  sum(ВВА_рu) as ВВА_рu,
  sum(ivfa_рu) as ivfa_рu,
  sum (svfa_bu) as svfa_bu,
  sum (ВВА_bu) as ВВА_bu,
  sum(ivfa_bu) as ivfa_bu
from cte
where cid in (8,9,10,11)
union all
select 12, 0, 'Кредиторская задолженность, итого', '',
  sum (svfa_рu) as svfa_рu,
  sum(ВВА_рu) as ВВА_рu,
  sum(ivfa_рu) as ivfa_рu,
  sum (svfa_bu) as svfa_bu,
  sum (ВВА_bu) as ВВА_bu,
  sum(ivfa_bu) as ivfa_bu
from cte
where cid in (13,14,15,16)
),
cte_gr_gr as (
  select 0 as cid, 0 as pid, 'ПЕНСИОННЫЕ ОБЯЗАТЕЛЬСТВА по НПО, итого' as name, '' as strateg,
    sum (svfa_рu) as svfa_рu,
    sum(ВВА_рu) as ВВА_рu,
    sum(ivfa_рu) as ivfa_рu,
    sum (svfa_bu) as svfa_bu,
    sum (ВВА_bu) as ВВА_bu,
    sum(ivfa_bu) as ivfa_bu
  from cte
  where cid in (1,5)
  union all   
  select 6, 0, 'РАСЧЕТЫ ПО ДОГОВОРАМ НПО', '',
    sum (case when cid = 12 then svfa_рu * -1 else svfa_рu end) as svfa_рu,
    sum(case when cid = 12 then ВВА_рu * -1 else ВВА_рu end) as ВВА_рu,
    sum(case when cid = 12 then ivfa_рu * -1 else ivfa_рu end) as ivfa_рu,
    sum (case when cid = 12 then svfa_bu * -1 else svfa_bu end) as svfa_bu,
    sum (case when cid = 12 then ВВА_bu * -1 else ВВА_bu end) as ВВА_bu,
    sum(case when cid = 12 then ivfa_bu * -1 else ivfa_bu end) as ivfa_bu
  from cte_gr
  where cid in (7,12)
),
cte_ps as (
  select 1 as cid, row_number() over() as pid, uidpensionscheme as name, 
    cast(strategy_id as text) as strateg, 
    sum(case when "account" = '38302'
                 and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end) as sVFA_рu,
    sum( case when "account" = '38902' 
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end) as ВВА_bu,
       sum(case when "account" in ('39002', '39022', '39023', '39024')
                 and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_pu,0) - coalesce(sum_ct_pu,0))
            end) as iVFA_рu,
       sum( case when "account" = '38302'
                 and coalesce(contract_class_code, 'INS') = 'INS'
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end) as sVFA_bu,
       sum( case when "account" = '38902' 
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end) as ВВА_bu,
       sum(case when "account" in ('39002', '39022', '39023', '39024')
                 and coalesce(contract_class_code, 'INV') = 'INV'
            then abs(coalesce(sum_dt_bu,0) - coalesce(sum_ct_bu,0))
            end) as iVFA_bu
  from back_office.report_conformity_pu_bu_detail
  where report_conformity_pu_bu_id =  $P{id}
    and "account" in ('38302', '38902', '39002', '39022', '39023', '39024')
  group by uidpensionscheme, strategy_id
),
cte_all as (
select *
from cte
union all
select *
from cte_gr
union all
select *
from cte_gr_gr
union all
select *
from cte_ps
),
cte_all_itog as (
select *, coalesce(svfa_рu, 0) + coalesce(ВВА_рu, 0) as s_pu,
 coalesce(svfa_рu, 0) + coalesce(ВВА_рu, 0) + coalesce(ivfa_рu, 0) as itog_pu,
 coalesce(svfa_bu, 0) + coalesce(ВВА_bu, 0) as s_bu,
 coalesce(svfa_bu, 0) + coalesce(ВВА_bu, 0) + coalesce(ivfa_bu, 0) as itog_bu
from cte_all
)
select *, s_pu - s_bu as rash_s,
  coalesce(ivfa_рu, 0) - coalesce(ivfa_bu, 0) as rash_i,
  itog_pu - itog_bu as rash_itog
from cte_all_itog
order by cid, pid