---

WITH cte_pens_product AS MATERIALIZED
(   -- Продукт - последнее значение
    SELECT 
        products.id, 
        products."name",
        products.pens_scheme_accum_stage_id as scheme_id
    FROM ourpension.contract_product_history history
        INNER JOIN prod_line.pens_product products 
        ON products.id = history.pens_product_id
    WHERE history.contract_id = :id
        AND CURRENT_TIMESTAMP BETWEEN 
            history.begin_date AND COALESCE( history.end_date, 'infinity' )
    LIMIT 1
), cte_param_pl AS MATERIALIZED 
(   -- Параметры из продуктовой линейки
    SELECT 
        pp2.brief, 
        prod_line.get_value_link_param
            (   CAST( pp2.id AS integer ), 
                CAST( pppp.value_ref AS integer ) 
            ) as param_value
    FROM cte_pens_product cpp
        INNER JOIN prod_line.pens_product pens_p 
        ON pens_p.id = cpp.id
        INNER JOIN prod_line.pens_product_param_pl pppp
        ON pppp.pens_product_id = pens_p.id
        INNER JOIN prod_line.param_pl pp2 
        ON pp2.id = pppp.param_pl_id     
)
SELECT 
    c.id,                                                           --  ИД договора
    (   SELECT param.param_value
        FROM cte_param_pl param
        WHERE param.brief = 'ФормаДоговора'
    ) AS contract_form,                                             --  Форма договора
    sct."name" AS sales_channel,                                    --  Канал продаж
    sct.brief AS cfo,                                               --  ЦФО
    e.short_name AS employer,                                       --  Работодатель
    pp."name" AS product_name,                                      --  Наименование продукта
    pp.id AS product_id,                                            --  ИД продукта
    tv.amount_type,                                                 --  Тип суммы
    tv.month_since_open AS transfer_term_in_months,                 --  Срок перевода в месяцах с момента открытия ПС
    pr.edition_number AS pension_rules_edition,                     --  Редакция пенсионных правил
    to_json( COALESCE( tl.tariff_name, '' ) ) AS tariff,            --  Тариф (оборачиваем в to_json, чтобы отсечь мусор в строке из-за миграции)
    cp.tariff_id,                                                   --  ID тарифа
    cp.min_annual_income_period AS mgd_accumulation_period,         --  МГД в периоде накопления
    cp.min_annual_savings_income AS mgd_payment_period,             --  МГД в периоды выплат
    (   SELECT param.param_value
        FROM cte_param_pl param
        WHERE param.brief = 'СрокОжиданияПервогоВзносаКоличДней'
    ) AS first_contribution_wait_period,                            --  Срок ожидания первого взноса
    cp.current_payment AS current_contribution,                     --  Текущий взнос
    (   SELECT mpf."name"
        FROM prod_line.hb_param_pl_multiselect mpf
        WHERE mpf.id = cp.payment_frequency
    ) AS contribution_frequency,                                    --  Периодичность взносов
    cp.contribution_ss AS ss_deduction_rate,                        --  Размер отчислений в СС
    cp.contribution_sr AS sr_deduction_rate,                        --  Размер отчислений в СР
    (   SELECT vst."label"
        FROM ourpension.voc_sharer_types vst
        WHERE vst."code" = cp.contribution_account
    ) AS contribution_account,                                      --  Счет учета взносов
    (   SELECT param.param_value
        FROM cte_param_pl param
        WHERE param.brief = 'ТипВыплаты'
    ) AS payment_type,                                              --  Тип выплаты
    cp.minimal_payment_amount AS min_payment_amount,                --  Минимальный размер выплаты
    cp.payment_term,                                                --  Срок выплаты
    cp.min_payment_term,                                            --  Минимальный срок выплаты
    (   SELECT param.param_value
        FROM cte_param_pl param
        WHERE param.brief = 'ПоэтапныйВестингПарамРасчетаСрока'
    ) AS term_calculation_parametr,                                 --  Параметр расчета срока
    (   SELECT param.param_value
        FROM cte_param_pl param
        WHERE param.brief = 'ПериодПредостПравТреб'
    ) AS choice_right_provision_parameter,                          --  Период предоставления права требования
    cp.initial_payment,                                             --  Первоначальный взнос
    cp.min_actuarial_yield,
    cp.indexing_value,                                              -- Величина прироста размера пенсии
    pp.scheme_id,                                                   -- Пенсионная схема (ИД)
		ps.brief as scheme_number                                       -- Пенсионная схема (название)
FROM ourpension.contract c
    LEFT JOIN ourpension.pension_rule pr ON pr.id = c.pension_rule_id
    LEFT JOIN ourpension.contract_params cp ON cp.contract_id = c.id
    LEFT JOIN ourpension.entity e ON e.id = c.entity_id
    LEFT JOIN ourpension.fund f ON f.id = c.fund_id
    LEFT JOIN actuary.tariff_list tl ON tl.id = cp.tariff_id
    LEFT JOIN ourpension.sale_channel sc ON sc.id = c.sale_channel_id
    LEFT JOIN prod_line.sale_channel_type sct ON sct.id = sc.type_sale_channel_id
    CROSS JOIN cte_pens_product pp
    LEFT JOIN prod_line.pens_scheme ps
    ON ps.id = pp.scheme_id
    LEFT JOIN prod_line.table_vesting tv ON tv.product_id = pp.id
WHERE c.id = :id


ШАБЛОН
#set ( $s = $db.findOne($search_brief_multiselect_sql) )
#if ($s)
{
	amountType:											'$s.amount_type',
	cfo: 														'$s.cfo',
  choiceRightProvisionParameter:	'$s.choice_right_provision_parameter',
	contractForm: 									'$s.contract_form',
	contributionAccount:						'$s.contribution_account',
	contributionFrequency:					'$s.contribution_frequency',
	currentContribution:						'$s.current_contribution',
	employer: 											'$s.employer',
	firstContributionWaitPeriod:		'$s.first_contribution_wait_period',
	id:															'$s.id',
	indexingValue:  								'$s.indexing_value',
	initialPayment: 								'$s.initial_payment',
	mgdAccumulationPeriod:					'$s.mgd_accumulation_period',
	mgdPaymentPeriod:              	'$s.mgd_payment_period',
	minActuarialYield:							'$s.min_actuarial_yield',
	minPaymentAmount:								'$s.min_payment_amount',
	minPaymentTerm:									'$s.min_payment_term',
	paymentTerm:										'$s.payment_term',
	paymentType:										'$s.payment_type',
	pensionRulesEdition:						'$s.pension_rules_edition',
	productId:										  '$s.product_id',
	productName:										'$s.product_name',
	salesChannel: 									'$s.sales_channel',
	schemeId:											 	'$s.scheme_id',	
	schemeNumber:										'$s.scheme_number',
	ssDeductionRate:								'$s.ss_deduction_rate',
	srDeductionRate:								'$s.sr_deduction_rate',
	tariff:													$s.tariff,
	tariff_id:											'$s.tariff_id',
	termCalculationParametr:				'$s.term_calculation_parametr',
	transferTermInMonths:						'$s.transfer_term_in_months'
}
#else {}
#end