---

(
    SELECT w.warranty_period
    FROM prod_line.pens_product_param_pl p_w

    JOIN prod_line.param_pl def_w
        ON def_w.id = p_w.param_pl_id

    JOIN prod_line.table_param_pl_warrant_period w
        ON w.pens_product_param_pl_id = p_w.id

    JOIN prod_line.table_param_pl_invest_profile ip
        ON ip.hb_invest_profile_id = w.hb_invest_profileid

    JOIN prod_line.pens_product_param_pl p_i
        ON p_i.id = ip.pens_product_param_pl_id
       AND p_i.pens_product_id = p_w.pens_product_id

    JOIN prod_line.param_pl def_i
        ON def_i.id = p_i.param_pl_id

    WHERE p_w.pens_product_id = pp.id
      AND def_w.brief = 'ПериодГарантВосполнения'
      AND def_i.brief = 'ИнвестПрофиль'

      AND (
          w.year_conclusion_from IS NULL
          OR CAST(EXTRACT(YEAR FROM c.date) AS integer) >= w.year_conclusion_from
      )
      AND (
          w.year_conclusion_to IS NULL
          OR CAST(EXTRACT(YEAR FROM c.date) AS integer) <= w.year_conclusion_to
      )

      AND (
          w.validity_period_from IS NULL
          OR CURRENT_DATE >= w.validity_period_from
      )
      AND (
          w.validity_period_to IS NULL
          OR CURRENT_DATE <= w.validity_period_to
      )

      AND (
          ip.validity_period_from IS NULL
          OR CURRENT_DATE >= ip.validity_period_from
      )
      AND (
          ip.validity_period_to IS NULL
          OR CURRENT_DATE <= ip.validity_period_to
      )

    ORDER BY w.validity_period_from DESC NULLS LAST
    LIMIT 1
) AS guarantee_replenishment_period,