---

select
    rd.id as id,
    coalesce(si.name, ps.index_scheme) as index_scheme,
    rd.account as account,
    ps.name as pens_scheme,
    s.name as strateg,
    rd.uidpensionscheme as uid_pens_scheme,

    to_char(
        coalesce(rd.sum_dt_pu, 0)
        - coalesce(rd.sum_ct_pu, 0),
        '99 999 999 999 999 990D99'
    ) as sum_pu,

    to_char(
        coalesce(rd.sum_dt_bu, 0)
        - coalesce(rd.sum_ct_bu, 0),
        '99 999 999 999 999 990D99'
    ) as sum_bu,

    to_char(
        (
            coalesce(rd.sum_dt_pu, 0)
            - coalesce(rd.sum_ct_pu, 0)
        )
        - (
            coalesce(rd.sum_dt_bu, 0)
            - coalesce(rd.sum_ct_bu, 0)
        ),
        '99 999 999 999 999 990D99'
    ) as discrepancy

from back_office.report_conformity_pu_bu_detail rd

left join ourpension.spr_accounts_indicator_bu ai
    on ai.account = rd.account

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

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

left join prod_line.hb_invest_strategy s
    on s.id = rd.strategy_id

where rd.report_conformity_pu_bu_id = :id