---

with missing_accounts as (
    select
        rd.account,
        sum(
            coalesce(rd.sum_dt_pu, 0)
            - coalesce(rd.sum_ct_pu, 0)
        ) as sum_pu,
        sum(
            coalesce(rd.sum_dt_bu, 0)
            - coalesce(rd.sum_ct_bu, 0)
        ) as sum_bu

    from back_office.report_conformity_pu_bu_detail rd

    join back_office.report_conformity_pu_bu r
        on r.id = rd.report_conformity_pu_bu_id

    where rd.report_conformity_pu_bu_id = 94
      and not exists (
          select 1
          from ourpension.spr_accounts_indicator_bu sa
          join ourpension.spr_indicator_bu si
              on si.id = sa.spr_indicator_bu_id
          where sa.account = rd.account
            and r.service_type = any(si.service_type)
      )

    group by rd.account
)

select
    m.account,
    m.sum_pu,
    m.sum_bu,
    sa.spr_indicator_bu_id as indicator_id,
    si.name as indicator_name,
    si.service_type

from missing_accounts m

left join ourpension.spr_accounts_indicator_bu sa
    on sa.account = m.account

left join ourpension.spr_indicator_bu si
    on si.id = sa.spr_indicator_bu_id

order by m.account, si.id;