---

SELECT
    td.id AS certificate_id,
    td.number AS certificate_number,
    td.year AS certificate_year,

    atc.id AS application_id,
    atc.number AS application_number,

    atcd.id AS latest_application_data_id,
    atcd.sharer_id AS sharer_id_from_data,
    atc.sharer_id AS sharer_id_from_application,

    sh.contract_id AS current_contract_id,
    c.number AS current_contract_number,

    c_app.number AS contract_number_via_application,

    info.name AS original_information_source,

    EXISTS (
        SELECT 1
        FROM report.fns_reference_request_journal fj
        WHERE fj.contract_number = c.number
          AND fj.response_received = true
    ) AS response_found_by_current_join,

    EXISTS (
        SELECT 1
        FROM ourpension.load_snv snv
        WHERE snv.contract_number = c.number
    ) AS snv_found_by_current_join

FROM ourpension.year_tax_deduction td

LEFT JOIN ourpension.application_termination_contract atc
    ON atc.id = td.application_termination_contract_id

LEFT JOIN LATERAL (
    SELECT
        d.id,
        d.sharer_id
    FROM ourpension.application_termination_contract_data d
    WHERE d.application_termination_contract_id = atc.id
    ORDER BY d.id DESC
    LIMIT 1
) atcd ON TRUE

LEFT JOIN ourpension.sharer sh
    ON sh.id = atcd.sharer_id

LEFT JOIN ourpension.contract c
    ON c.id = sh.contract_id

LEFT JOIN ourpension.sharer sh_app
    ON sh_app.id = atc.sharer_id

LEFT JOIN ourpension.contract c_app
    ON c_app.id = sh_app.contract_id

LEFT JOIN ourpension.information_source info
    ON info.id = atc.information_source_id

WHERE td.id = :certificate_id;