---

select
i.id as smev_register_id,
    to_char(now(), 'DD.MM.YYYY') as date_doc,
-- атрибуты Файл
'UT_ZAPRSTRORGNPF_' || coalesce(fc.const_value, '7702') || '_' || coalesce(fc.const_value, '7702') || '_' || f.inn ||''|| f.kpp || '_' || to_char(current_date,'YYYYMMDD') || '_' || replace(cast(gen_random_uuid() as text),'-','') as file_id,
'5.03' 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,
replace(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 = :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'