---

FROM ourpension.ops_pension_assignment_application opaa

JOIN ourpension.sharer s
    ON s.id = opaa.sharer_id

JOIN ourpension.contract c
    ON c.id = s.contract_id
   AND c.service_type = 'OPS'

JOIN ourpension.individual i
    ON i.id = s.individual_id

LEFT JOIN ourpension.address a
    ON a.id = i.registration_address_id

LEFT JOIN ourpension.payment_type pt
    ON pt.code = opaa.payment_way

LEFT JOIN ourpension.decision_payment_type dpt
    ON dpt.code = opaa.decision_payment_type_code

-- Первое проведённое решение об установлении пенсии
JOIN LATERAL (
    SELECT opd.decision_payment_type_code
    FROM back_office.ops_payment_decision opd
    WHERE opd.ops_pension_assignment_application_id = opaa.id
      AND opd.decision_type_code = 'УСТН'
      AND opd.accepted IS NOT NULL
      AND opd.date <= CAST(:date_of_application AS date)
    ORDER BY opd.date ASC
    LIMIT 1
) first_decision ON true

-- СПВ по справочнику решений
JOIN ourpension.decision_payment_type pension_type
    ON pension_type.code = first_decision.decision_payment_type_code
   AND pension_type.code = '02'

LEFT JOIN ourpension.application_individual_bank_detail aibd
    ON aibd.id = opaa.bank_detail_id

LEFT JOIN ourpension.bankru br
    ON br.bic = aibd.bic

WHERE opaa.accepted IS NOT NULL
  AND opaa.canceled IS NULL
  AND opaa.rejected IS NULL
  AND opaa.appeal_date <= CAST(:date_of_application AS date)

  -- Статус счёта на конечную дату отчёта
  AND EXISTS (
      SELECT 1
      FROM ourpension.sharer_statuses_history ssh
      WHERE ssh.sharer_id = s.id
        AND ssh.code = 'CA0'
        AND CAST(:date_of_application AS date)
            BETWEEN ssh.status_date AND ssh.status_end_date
  )

  -- Наличие записи о количестве выплат к этой дате
  AND EXISTS (
      SELECT 1
      FROM back_office.ops_number_of_payments np
      WHERE np.contract_id = c.id
        AND np.effective_date <= CAST(:date_of_application AS date)
  )

  -- Реквизиты проведены до даты БР включительно
  AND (
      CAST(:br_date AS date) IS NULL
      OR aibd.accepted::date <= CAST(:br_date AS date)
  )