Orders with Paid Status but Zero Recorded Payments in Magento

Tracking and resolving inconsistencies in e-commerce transactions is vital for maintaining accurate financial records. This article delves into a SQL query crafted to identify Magento orders marked as paid but where no payment amount is recorded. By analyzing this query, businesses can uncover discrepancies, optimize payment workflows, and ensure data integrity.

SELECT 
    so.increment_id,
    so.state,
    so.status,
    so.created_at,
    sop.amount_paid,
    sop.amount_authorized,
    sop.base_amount_paid,
    sop.base_amount_authorized
FROM 
    `sales_order_payment` sop
INNER JOIN 
    sales_order AS so 
    ON so.entity_id = sop.parent_id
INNER JOIN 
    `sales_invoice` AS si 
    ON si.order_id = sop.parent_id
WHERE 
    (sop.amount_paid = 0 OR sop.amount_paid IS NULL)
    AND si.state = 2
ORDER BY 
    so.entity_id DESC;