---

select
    cast(r.step as integer) as step,
    cast(r.name as varchar) as name,
    cast(r.s_pu as numeric) as s_pu,
    cast(r.s_bu as numeric) as s_bu,
    cast(r.s_pu - r.s_bu as numeric) as razn
from (
    select
        0 as step,
        'РППО по договорам НПО (ООЧП) (38902)' as name,

        coalesce(
            sum(
                coalesce(rd.sum_dt_pu, 0)
                - coalesce(rd.sum_ct_pu, 0)
            ),
            0
        ) as s_pu,

        coalesce(
            sum(
                coalesce(rd.sum_dt_bu, 0)
                - coalesce(rd.sum_ct_bu, 0)
            ),
            0
        ) as s_bu

    from back_office.report_conformity_pu_bu_detail rd
    where rd.report_conformity_pu_bu_id = $P{id}
      and rd.account = '38902'

    union all

    select
        1 as step,

        min(
            coalesce(
                nullif(btrim(ps.name), ''),
                nullif(btrim(ps.brief), ''),
                case
                    when rd.pension_scheme_id is not null
                        then 'Схема ID '
                            || cast(rd.pension_scheme_id as text)

                    when nullif(
                        btrim(rd.uidpensionscheme), ''
                    ) is not null
                        then 'UID схемы: '
                            || lower(btrim(rd.uidpensionscheme))

                    else 'Без указания схемы'
                end
            )
        ) as name,

        sum(
            coalesce(rd.sum_dt_pu, 0)
            - coalesce(rd.sum_ct_pu, 0)
        ) as s_pu,

        sum(
            coalesce(rd.sum_dt_bu, 0)
            - coalesce(rd.sum_ct_bu, 0)
        ) as s_bu

    from back_office.report_conformity_pu_bu_detail rd

    left join prod_line.pens_scheme ps
        on ps.id = rd.pension_scheme_id

    where rd.report_conformity_pu_bu_id = $P{id}
      and rd.account = '38902'

    group by
        case
            when rd.pension_scheme_id is not null
                then 'id:'
                    || cast(rd.pension_scheme_id as text)

            when nullif(
                btrim(rd.uidpensionscheme), ''
            ) is not null
                then 'uid:'
                    || lower(btrim(rd.uidpensionscheme))

            else 'unknown'
        end
) r
order by
    r.step,
    r.name