---

SELECT
    ri.id AS id,

    /* Служебные идентификаторы для ссылок */
    ri.client_id AS client_id,
    ri.contract_id AS contract_id,

    /* Данные клиента */
    CONCAT_WS(
        ' ',
        ind.last_name,
        ind.first_name,
        NULLIF(ind.middle_name, '')
    ) AS client_name,

    ri.snils AS snils,

    /* Договор */
    CASE
        WHEN ri.contract_type = 'NPO' THEN 'НПО'
        WHEN ri.contract_type = 'PDS' THEN 'ПДС'
        ELSE ri.contract_type
    END AS contract_type_name,

    c.number AS contract_number,

    /* Суммы */
    ri.contributions_amount AS contributions_amount,
    ri.preliminary_deduction_amount
        AS preliminary_deduction_amount,

    /* Ежегодное получение */
    CASE
        WHEN ri.annual_deduction_flag = 1 THEN 'Да'
        ELSE 'Нет'
    END AS annual_deduction_flag_name,

    /* Наличие ЛК ФНС */
    CASE
        WHEN ri.has_fns_lk = 1  THEN 'Есть'
        WHEN ri.has_fns_lk = 0  THEN 'Нет'
        WHEN ri.has_fns_lk = 99 THEN 'Ошибка проверки'
        ELSE 'Не проверялось'
    END AS has_fns_lk_name,

    TO_CHAR(ri.last_sent_date, 'DD.MM.YYYY')
        AS last_sent_date,

    ri.status AS status,
    ri.send_count AS send_count,
    ri.tax_period_year AS tax_period_year

FROM report.unv_registry_item ri
JOIN ourpension.individual ind
    ON ind.id = ri.client_id
JOIN ourpension.contract c
    ON c.id = ri.contract_id

WHERE ri.registry_id = CAST(:registry_id AS integer)

ORDER BY ri.id;