---

SELECT
    opd.id AS decision_id,
    opd.date AS decision_date,
    opd.accepted IS NOT NULL AS decision_accepted,

    opd.decision_type_code,
    opd.decision_payment_type_code,
    dpt.short_name AS decision_payment_type,

    opaa.ops_payment_type_code AS application_type_code,

    opaa.id IS NOT NULL AS application_found,
    opaa.bank_detail_id IS NOT NULL AS bank_detail_id_exists,
    aibd.id IS NOT NULL AS bank_detail_found,
    aibd.personal_account IS NOT NULL AS personal_account_exists,
    aibd.bic IS NOT NULL AS bic_exists,
    aibd.accepted IS NOT NULL AS bank_detail_accepted,

    s.id IS NOT NULL AS sharer_found,
    c.id IS NOT NULL AS contract_found,
    i.id IS NOT NULL AS individual_found,
    a.id IS NOT NULL AS address_found

FROM back_office.ops_payment_decision opd

JOIN ourpension.ops_pension_assignment_application opaa
    ON opaa.id = opd.ops_pension_assignment_application_id

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

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

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

LEFT JOIN ourpension.individual i
    ON i.id = c.individual_id

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

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

WHERE opd.decision_payment_type_code IN ('01', '16')
  AND opd.date BETWEEN DATE '2026-08-21'
                   AND DATE '2026-08-28'

ORDER BY opd.date DESC;