---

with b (id, balance) as (
    select
    a.owner_contract_id,
    sum(b.total_credit - b.total_debit)
    from back_office.account a
        join back_office.account_type at on a.account_type_id = at.id
        and at.mnemonics in ('СПС', 'СПС ИД')
        join back_office.balance b on a.id = b.account_id
        and current_date between b.start_date and b.end_date
    where a.owner_contract_id = :id
    group by a.owner_contract_id
),
termination as (
    select 
        tr.name as termination_reason, 
        t.date as termination_date,
        row_number() over (partition by tc.contract_id order by t.date, t.version desc) as num
    from ourpension.termination_contract tc 
        join ourpension.termination t 
            left join ourpension.contract_termination_reason tr 
            on tr.id = t.contract_termination_reason_id
        on tc.termination_id = t.id
    where tc.contract_id = :id
),
s as(
    select         
        his.strategy_name, 
        c."service_type",
        c.close_date, 
        c.effective_date, 
        c.client_type, 
        c.account_management_form,
        case c.account_management_form 
            when 'PERSONAL' then 'Именная'
            when 'JOINT' then 'Солидарная'
        end as account_management_form_text,
        coalesce(b.balance, 0) as curr_state,
        case
            when c.client_type = 0 and c.service_type = 'NPO' then 'НПО индивидуальный'
            when c.service_type = 'NPO' then 'НПО корпоративный'
            else 'ОПС'
        end as client_type_text,
        r.name as close_reason, 
        c.invest_type, 
        c.death_table_query_datasource_file_id, 
        c.pension_rule_id,
        c.periodicity_payment_id, 
        c.periodicity_payout_id,
        replace(dt.name,'.'||dt.extension, '') as death_table_name,
        payment.name as periodicity_payment, payout.name as periodicity_payout,
        'Пенсионные правила редакции № '||pr.edition_number||' от ' 
            ||to_char(pr.date_reg, 'dd.MM.yyyy') as pension_rule,
        pr.edition_number,
        c.min_year_income, 
        c.min_tariff_rate, 
        c.k1,
        c.inheritance_type_code, it.name as inheritance_type_text,
        c.change_inheritance_type_by_sharer,
        case c.change_inheritance_type_by_sharer
            when true then 'Разрешено'
            when false then 'Запрещено'
        end as change_inheritance_type_by_sharer_text,
        c.postaccrual_income_action, 
        c.pension_deposit_value,
        trim(to_char(c.pension_deposit_value, '99 999 999 999 999 990D99')) as pension_deposit_value_formatted,
        c.entity_id,
        c.individual_id as contract_individual_id,
        sh.individual_id as sharer_individual_id,
        cp.min_annual_income_period,
        cp.min_annual_savings_income,
        t.termination_date,
        t.termination_reason,
        e.enum_value as nonstandard_reason,
        c.nonstandard_reason_id,
        urd.id as uorls_document_id,
        urd.file_name as uorls_document_file
    from ourpension.contract c
        left join b on b.id = c.id
        left join ourpension.contract_params cp on cp.contract_id = c.id
        left join ourpension.contract_close_reason r on c.contract_close_reason_id = r.id
        left join dev.query_datasource_file dt on dt.id=c.death_table_query_datasource_file_id
        left join ourpension.periodicity payment on payment.id = c.periodicity_payment_id
        left join ourpension.periodicity payout on payout.id = c.periodicity_payout_id
        left join ourpension.pension_rule pr on pr.id = c.pension_rule_id
        left join ourpension.inheritance_type it on c.inheritance_type_code = it.code
        left join ourpension.sharer sh on sh.contract_id = c.id and c.client_type = 0
        left join termination t on t.num =1
                left join ourpension.enums e on c.nonstandard_reason_id = e.id
                left join ourpension.enums_name en on e.enums_name_id = en.id and en.enum_name = 'ПричиныНестандарта'
        left join lateral (
            select 
                h.begin_date,
                s."name" as strategy_name
            from ourpension.invest_strategy_history h
                inner join prod_line.hb_invest_strategy s on h.strategy_id = s.id
            where h.contract_id = c.id
            order by h.begin_date desc
            limit 1
        ) his on true 
        left join lateral (
            select urd.id
                ,urd.file_name
            from back_office.changed_snils cs
            left join report.uorls_register_document urd on
                cs.uorls_register_document_id = urd.id
            where cs.contract_id = c.id
            order by cs.change_date desc
            limit 1
        ) urd on true
        LEFT JOIN LATERAL (
            SELECT 
                pl_schemas.index_scheme as scheme_number,                
                history.pens_scheme_id as scheme_id                
            FROM ourpension.contract_scheme_history history
                INNER JOIN prod_line.pens_scheme pl_schemas 
                ON pl_schemas.id = history.pens_scheme_id
            WHERE history.contract_id = c.id
                AND CURRENT_TIMESTAMP BETWEEN history.begin_date AND COALESCE( history.end_date, 'infinity' )
            LIMIT 1
        ) ls 
        ON true
    where c.id = :id
)
SELECT
    s.account_management_form,
    s.account_management_form_text,
    s.change_inheritance_type_by_sharer_text,
    s.client_type, 
    s.client_type_text,
    s.close_date,
    s.close_reason,
    s.contract_individual_id,
    trim(to_char(s.curr_state, '99 999 999 999 999 990D99')) as curr_state,
    s.death_table_name,
    s.death_table_query_datasource_file_id,
    s.edition_number,
    s.effective_date,
    s.entity_id,
    s.inheritance_type_code,
    s.inheritance_type_text,
    s.invest_type,
    s.k1,
    s.min_annual_income_period as mgd_accumulation,
    s.min_annual_savings_income as mgd_payments,
    s.min_tariff_rate,
    trim(to_char(s.min_tariff_rate, '99 999 999 999 999 990D99')) as min_tariff_rate_formatted,
    s.min_year_income,
    s.nonstandard_reason,
    s.nonstandard_reason_id,
    s.postaccrual_income_action,
    s.pension_deposit_value,
    s.pension_deposit_value_formatted,
    s.pension_rule, 
    s.pension_rule_id,
    s.periodicity_payment, 
    s.periodicity_payment_id,
    s.periodicity_payout, 
    s.periodicity_payout_id,
    case s.postaccrual_income_action
        when 'ONE_TIME_PAYMENT' then 'Дополнительная разовая выплата пенсии в размере ИД'
        when 'RECALCULATION' then 'Перерасчет размера выплачиваемой негосударственной пенсии'
    end as postaccrual_income_action_text,
    s."service_type",
    s.sharer_individual_id,
    s.strategy_name,
    s.termination_date,
    s.termination_reason,
    s.uorls_document_id,
    s.uorls_document_file,
    s1.*
FROM s
    CROSS JOIN ourpension.get_contract_fields_main(:id) s1
LIMIT 1



ШАБЛОН
#set ( $s = $db.findOne($find_contract_sql) )
#if ($s)
{
	id: 										 	 			  	'$s.id',
	accountManagementForm:     				  '$s.account_management_form',
	accountManagementFormText:					'$s.account_management_form_text',
	actionDate: 												'$s.action_date',
	affiliateProgramContractId:  				'$s.affiliate_program_contract_id',
	affiliateProgramContractText:				$s.affiliate_program_contract_text,
	agentCode:													'$s.agent_code',
	agentName:													'$s.agent_name',
	annulledDate:										 	 	'$s.annulled_date',
	changeInheritanceTypeBySharerText:	'$s.change_inheritance_type_by_sharer_text',
	clientType:									       	'$s.client_type',
	clientTypeText:										 	'$s.client_type_text',
	closeDate:											   	'$s.close_date',
	closeReason:											 	'$s.close_reason',
	comment:										    	 	'$s.comment',
	contractClassName:                	'$s.contract_class_name',
	contractIndividualId:								'$s.contract_individual_id',
	contractLifeCicle:          				'$s.contract_life_cicle',
	contractType:												'$s.contract_type',
	createDate:											   	'$s.create_date',
	currState:							 			     	'$s.curr_state',
	date:												       	'$s.date',
	deathTableId:               			 	'$s.death_table_query_datasource_file_id',
	deathTableName:										  '$s.death_table_name',
	departmentType: 										'$s.department_type',
	editionNumber:											'$s.edition_number',
	effectiveDate:										 	'$s.effective_date',
	entityId:														'$s.entity_id',
	familyMember:												'$s.family_member',
	fundBranchName:											'$s.fund_branch_name',
	fundName:                           '$s.fund_name',
	fundNameShort:											'$s.fund_name_short',
	inheritanceTypeCode:  							'$s.inheritance_type_code',
	inheritanceTypeText:								'$s.inheritance_type_text',	
	investor:													 	'$s.investor',
	investType:												 	'$s.invest_type',
	investStrategyName:               	'$s.strategy_name',
	investTypeText:				  						'$s.invest_type_text',
	k1:																	'$s.k1',
	mgdAccumulation:										'$s.mgd_accumulation',
	mgdPayments:												'$s.mgd_payments',
	minTariffRate: 											'$s.min_tariff_rate',
	minTariffRateFormatted:	  					'$s.min_tariff_rate_formatted',
	minYearIncome:											'$s.min_year_income',
  nameInPfr:													'$s.name_in_pfr',
	newInsurerName:											'$s.new_insurer_name',
	number:										 			  	$s.number,
	nonstandardReason:                  '$s.nonstandard_reason',
	nonstandardReasonId:                '$s.nonstandard_reason_id',
	pensionDepositValue:								'$s.pension_deposit_value',
	pensionDepositValueFormatted:				'$s.pension_deposit_value_formatted',
	pensionRule:												'$s.pension_rule',
	pensionRuleId:										 	'$s.pension_rule_id',
	periodicityPayment:									'$s.periodicity_payment',
	periodicityPaymentId:       			 	'$s.periodicity_payment_id',
	periodicityPayout:									'$s.periodicity_payout',
	periodicityPayoutId:       				 	'$s.periodicity_payout_id',
	postaccrualIncomeAction:						'$s.postaccrual_income_action',
	postaccrualIncomeActionText:				'$s.postaccrual_income_action_text',
	previousInsurerName:								'$s.previous_insurer_name',
	saleChannelId: 										 	'$s.sale_channel_id',
	saleChannelText:										'$s.sale_channel_text',
	serviceType:                      	'$s.service_type',
	serviceTypeRus:											'$s.service_type_rus',
	sharerIndividualId:									'$s.sharer_individual_id',
	terminationDate:										'$s.termination_date',
	terminationReason:                 	'$s.termination_reason',
	uorls_document_id:									'$s.uorls_document_id',
	uorls_document_file:								'$s.uorls_document_file'
	 }
#else {}
#end