---

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 (
            order by
                g.uidpensionscheme,
                g.pension_scheme_id,
                g.uidsubtypeactivity,
                g.strategy_id
        ) as pid,

        coalesce(
            ps_id.name,
            ps_uid.name,
            case
                when g.pension_scheme_id is null
                 and nullif(btrim(g.uidpensionscheme), '') is null
                then 'Пенсионная схема не указана'
                else 'Пенсионная схема не сопоставлена'
            end
        ) as name,

        coalesce(
            st_id.name,
            st_uid.name,
            case
                when g.strategy_id is null
                 and nullif(btrim(g.uidsubtypeactivity), '') is null
                then 'Стратегия не указана'
                else 'Стратегия не сопоставлена'
            end
        ) as strateg,

        g.svfa_рu,
        g.ВВА_рu,
        g.ivfa_рu,
        g.svfa_bu,
        g.ВВА_bu,
        g.ivfa_bu

    from (
        select
            rd.pension_scheme_id,
            rd.uidpensionscheme,
            rd.strategy_id,
            rd.uidsubtypeactivity,

            sum(
                case
                    when rd.account = '38302'
                     and coalesce(rd.contract_class_code, 'INS') = 'INS'
                    then abs(
                        coalesce(rd.sum_dt_pu, 0)
                        - coalesce(rd.sum_ct_pu, 0)
                    )
                end
            ) as svfa_рu,

            sum(
                case
                    when rd.account = '38902'
                    then abs(
                        coalesce(rd.sum_dt_pu, 0)
                        - coalesce(rd.sum_ct_pu, 0)
                    )
                end
            ) as ВВА_рu,

            sum(
                case
                    when rd.account in (
                        '39002', '39022', '39023', '39024'
                    )
                     and coalesce(rd.contract_class_code, 'INV') = 'INV'
                    then abs(
                        coalesce(rd.sum_dt_pu, 0)
                        - coalesce(rd.sum_ct_pu, 0)
                    )
                end
            ) as ivfa_рu,

            sum(
                case
                    when rd.account = '38302'
                     and coalesce(rd.contract_class_code, 'INS') = 'INS'
                    then abs(
                        coalesce(rd.sum_dt_bu, 0)
                        - coalesce(rd.sum_ct_bu, 0)
                    )
                end
            ) as svfa_bu,

            sum(
                case
                    when rd.account = '38902'
                    then abs(
                        coalesce(rd.sum_dt_bu, 0)
                        - coalesce(rd.sum_ct_bu, 0)
                    )
                end
            ) as ВВА_bu,

            sum(
                case
                    when rd.account in (
                        '39002', '39022', '39023', '39024'
                    )
                     and coalesce(rd.contract_class_code, 'INV') = 'INV'
                    then abs(
                        coalesce(rd.sum_dt_bu, 0)
                        - coalesce(rd.sum_ct_bu, 0)
                    )
                end
            ) as ivfa_bu

        from back_office.report_conformity_pu_bu_detail rd
        where rd.report_conformity_pu_bu_id = $P{id}
          and rd.account in (
              '38302', '38902', '39002',
              '39022', '39023', '39024'
          )
        group by
            rd.pension_scheme_id,
            rd.uidpensionscheme,
            rd.strategy_id,
            rd.uidsubtypeactivity
    ) g

    left join prod_line.pens_scheme ps_id
        on ps_id.id = g.pension_scheme_id

    left join prod_line.hb_invest_strategy st_id
        on st_id.id = g.strategy_id

    left join lateral (
        select max(ps.name) as name
        from prod_line.pens_scheme ps
        where g.pension_scheme_id is null
          and nullif(
              lower(btrim(cast(ps.uid_1c as text))), ''
          ) = nullif(
              lower(btrim(g.uidpensionscheme)), ''
          )
        having count(*) = 1
    ) ps_uid on true

    left join lateral (
        select max(st.name) as name
        from prod_line.hb_invest_strategy st
        where g.strategy_id is null
          and nullif(
              lower(btrim(cast(st.uid_1c as text))), ''
          ) = nullif(
              lower(btrim(g.uidsubtypeactivity)), ''
          )
        having count(*) = 1
    ) st_uid on true
),
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