---

select
    i.id as smev_register_id,
    to_char(now(), 'DD.MM.YYYY') as date_doc,

    -- атрибуты Файл
    'UT_ZAPRSTRORGNPF_'
        || i.id
        || '_'
        || to_char(i.birth_date, 'YYYYMMDD')
        || '_'
        || replace(cast(gen_random_uuid() as text), '-', '') as file_id,

    '5.02' as vers_form,
    'КОНТУР-ЭКСТЕРН, ВЕРСИЯ 16.0' as vers_prog,

    -- атрибуты Документ
    coalesce(fc.const_value, '7702') as kod_no,
    to_char(now(), 'DD.MM.YYYY') as doc_date,
    '1184042' as knd,

    -- СвНП/НПЮЛ
    f.kpp as org_kpp,
    f.inn as org_inn,
    fe.name as org_name,

    (
        select cast(serial_number as varchar(10))
        from dev.unique_number
        where code = 'smev_register'
    ) as req_number

from ourpension.individual i

-- Получаем конкретный договор по входному параметру
inner join ourpension.contract ct
    on ct.id = :contract_id
   and ct.individual_id = i.id

-- Дальше полностью повторяем определение фонда из первого запроса
inner join ourpension.agent a
    on a.id = ct.agent_id

left join ourpension.fund_branch fb
    on fb.id = a.fund_branch_id

left join ourpension.fund f
    on f.id = fb.fund_id

left join ourpension.entity fe
    on fe.id = f.entity_id

left join ourpension.our_funds_constants fc
    on fc.fund_id = f.id
   and fc.const_name = 'kod_no'

where i.id = :id;