---

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
),