---

SELECT
    pa.id AS application_id,
    pa.number AS application_number,
    pa."date" AS application_date,
    pa.appeal_date,
    pa.registration,
    pa.accepted AS application_accepted,

    pa.ops_payment_type_code,
    pt.short_name AS application_payment_type,

    pa.decision_payment_type_code AS app_decision_payment_type,

    t.id AS decision_id,
    t.number AS decision_number,
    t.date AS decision_date,
    t.accepted AS decision_accepted,
    t.decision_type_code,

    t.decision_payment_type_code,
    dpt.short_name AS decision_payment_type,
    dpt.name AS decision_payment_type_name,

    pa.bank_detail_id,
    pa.bank_account_id,

    aibd.id AS bank_detail_found,
    aibd.personal_account,
    aibd.bic,
    aibd.accepted AS bank_detail_accepted

FROM ourpension.ops_pension_assignment_application pa

LEFT JOIN back_office.ops_payment_decision t
    ON t.ops_pension_assignment_application_id = pa.id

LEFT JOIN ourpension.ops_payment_type pt
    ON pt.code = pa.ops_payment_type_code

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

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

WHERE pa.registration >= CURRENT_DATE - INTERVAL '30 days'

ORDER BY
    pa.registration DESC,
    pa.id DESC,
    t.id DESC;



SELECT
    t.decision_payment_type_code,
    dpt.short_name,
    dpt.name,
    count(*) AS cnt,
    min(t.date) AS min_date,
    max(t.date) AS max_date,
    max(t.accepted) AS max_accepted
FROM back_office.ops_payment_decision t
LEFT JOIN ourpension.decision_payment_type dpt
    ON dpt.code = t.decision_payment_type_code
WHERE t.decision_payment_type_code IN ('01', '16')
GROUP BY
    t.decision_payment_type_code,
    dpt.short_name,
    dpt.name;