---

SELECT DISTINCT
    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
JOIN ourpension.uv_activity_type uat
    ON uat.activity_eng_type = c.service_type
   AND uat.client_type = c.client_type
WHERE atc.date >= current_date - 1
  AND atc.date < current_date + 1
  AND c.service_type IN ('NPO', 'PDS')
  AND i.first_name IS NOT NULL
  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 c.id, atc.id, sh.individual_id;