---

SELECT
    atc.date AS application_date,
    atc.id AS application_id,
    c.id AS contract_id,
    c.number AS contract_number,
    atc.individual_id AS application_person_id,
    sh.individual_id AS participant_id,
    c.individual_id AS contract_owner_id
FROM ourpension.application_termination_contract atc
JOIN ourpension.sharer sh
    ON sh.id = atc.sharer_id
JOIN ourpension.contract c
    ON c.id = sh.contract_id
JOIN ourpension.individual i
    ON i.id = c.individual_id
WHERE c.service_type IN ('NPO', 'PDS')
  AND i.first_name IS NOT NULL
  AND EXISTS (
      SELECT 1
      FROM ourpension.uv_activity_type uat
      WHERE uat.activity_eng_type = c.service_type
        AND uat.client_type = c.client_type
  )
  AND NOT EXISTS (
      SELECT 1
      FROM ourpension.year_tax_deduction td
      WHERE td.application_termination_contract_id = atc.id
        AND td.type IN ('RECEIVED', 'NOT_RECEIVED')
  )
ORDER BY atc.date DESC NULLS LAST, atc.id DESC
LIMIT 10;