---

WITH target_decision AS (
    SELECT
        d.id,
        d.number
    FROM back_office.ops_assignee_decision d
    WHERE d.accepted IS NOT NULL
      AND EXISTS (
          SELECT 1
          FROM back_office.ops_assignee_decision_payment p
          WHERE p.ops_assignee_decision_id = d.id
            AND p.payment_value > 0
      )
      AND EXISTS (
          SELECT 1
          FROM back_office.operation o
          WHERE o.ops_assignee_decision_id = d.id
            AND o.approved IS NOT NULL
            AND o.deleted IS NULL
      )
    ORDER BY d.id DESC
    LIMIT 1
)
SELECT
    d.id AS decision_id,
    d.number AS decision_number,
    o.id AS operation_id,
    ot.code AS operation_type_code,
    o.effective_date,
    o.approved,
    o.deleted,
    t.id AS transaction_id,
    dat.mnemonics AS debit_account_type,
    cat.mnemonics AS credit_account_type,
    t.value
FROM target_decision d
JOIN back_office.operation o
  ON o.ops_assignee_decision_id = d.id
LEFT JOIN back_office.operation_type ot
  ON ot.id = o.operation_type_id
LEFT JOIN back_office.transaction t
  ON t.operation_id = o.id
LEFT JOIN back_office.account da
  ON da.id = t.debit_account_id
LEFT JOIN back_office.account_type dat
  ON dat.id = da.account_type_id
LEFT JOIN back_office.account ca
  ON ca.id = t.credit_account_id
LEFT JOIN back_office.account_type cat
  ON cat.id = ca.account_type_id
ORDER BY o.effective_date, o.id, t.id;