---

SELECT
    opaa.id AS application_id,
    opaa.appeal_date,
    opaa.registration,
    opaa.accepted IS NOT NULL AS application_accepted,

    opaa.ops_payment_type_code,

    opaa.decision_payment_type_code AS app_decision_payment_type_code,

    dpt.code IS NOT NULL AS app_decision_payment_type_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,
    aibd.accepted AS bank_detail_accepted_date,

    EXISTS (
        SELECT 1
        FROM back_office.ops_payment_decision opd
        WHERE opd.ops_pension_assignment_application_id = opaa.id
          AND opd.decision_payment_type_code = '02'
    ) AS spv_decision_exists,

    (
        SELECT max(opd.date)
        FROM back_office.ops_payment_decision opd
        WHERE opd.ops_pension_assignment_application_id = opaa.id
          AND opd.decision_payment_type_code = '02'
    ) AS spv_decision_date

FROM ourpension.ops_pension_assignment_application opaa

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

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

WHERE opaa.ops_payment_type_code = '05'
  AND opaa.registration >= CURRENT_DATE - INTERVAL '30 days'

ORDER BY opaa.registration DESC;