Script to Compare GR and PO Orderlines Item

SELECT 
    po.name AS po_number,
    sp.name AS transfer_ref,
    spt.name AS operation_type,
    sl_src.complete_name AS source_location,
    sl_dest.complete_name AS dest_location,
    sl_dest.usage AS dest_usage,
    pt.name AS product_name,
    sm.product_uom_qty AS demand_qty,
    sm.quantity AS done_qty, -- in Odoo <=16 use: sm.quantity_done
    sm.to_refund AS is_to_refund,
    sm.state AS move_status
FROM stock_move sm
JOIN purchase_order_line pol ON pol.id = sm.purchase_line_id
JOIN purchase_order po ON po.id = pol.order_id
JOIN stock_picking sp ON sp.id = sm.picking_id
JOIN stock_picking_type spt ON spt.id = sp.picking_type_id
JOIN stock_location sl_src ON sl_src.id = sm.location_id
JOIN stock_location sl_dest ON sl_dest.id = sm.location_dest_id
JOIN product_product pp ON pp.id = sm.product_id
JOIN product_template pt ON pt.id = pp.product_tmpl_id
WHERE po.name = 'POTR/2606/02274'
ORDER BY sm.id;

SELECT 
    po.name AS po_number,
    rp.name AS vendor,
    pt.name AS product_name,
    pol.product_qty AS qty_ordered,
    pol.qty_received AS qty_received,
    (pol.qty_received - pol.product_qty) AS excess_qty,
    po.state AS po_status
FROM purchase_order_line pol
JOIN purchase_order po ON po.id = pol.order_id
JOIN res_partner rp ON rp.id = po.partner_id
JOIN product_product pp ON pp.id = pol.product_id
JOIN product_template pt ON pt.id = pp.product_tmpl_id
WHERE pol.qty_received > pol.product_qty
ORDER BY po.name DESC;

SELECT 
    po.name AS po_number,
    COUNT(DISTINCT sp.id) AS validated_receipt_count,
    STRING_AGG(sp.name, ', ') AS receipt_numbers
FROM stock_picking sp
JOIN stock_picking_type spt ON spt.id = sp.picking_type_id
JOIN purchase_order po ON (sp.purchase_id = po.id OR sp.origin = po.name)
WHERE sp.state = 'done'
  AND spt.code = 'incoming'
GROUP BY po.name
HAVING COUNT(DISTINCT sp.id) > 1
ORDER BY validated_receipt_count DESC;

WITH po_stock_summary AS (
    SELECT 
        pol.id AS pol_id,
        pol.order_id,
        pol.product_id,
        pol.product_qty AS qty_ordered,
        pol.qty_received AS odoo_recorded_received,
        
        -- Total Received (Vendor -> Internal Stock)
        COALESCE(SUM(CASE 
            WHEN sl_dest.usage = 'internal' AND sl_src.usage = 'supplier' 
            THEN sm.quantity -- in Odoo <= 16 use: sm.quantity_done
            ELSE 0 
        END), 0) AS actual_gr_qty,
        
        -- Total Returned to Vendor (Internal Stock -> Vendor)
        COALESCE(SUM(CASE 
            WHEN sl_dest.usage = 'supplier' AND sl_src.usage = 'internal' 
            THEN sm.quantity -- in Odoo <= 16 use: sm.quantity_done
            ELSE 0 
        END), 0) AS actual_return_qty

    FROM purchase_order_line pol
    JOIN stock_move sm ON sm.purchase_line_id = pol.id AND sm.state = 'done'
    JOIN stock_location sl_src ON sl_src.id = sm.location_id
    JOIN stock_location sl_dest ON sl_dest.id = sm.location_dest_id
    GROUP BY pol.id, pol.order_id, pol.product_id, pol.product_qty, pol.qty_received
)
SELECT 
    po.name AS po_number,
    rp.name AS vendor,
    pt.name AS product_name,
    s.qty_ordered,
    s.actual_gr_qty,
    s.actual_return_qty,
    (s.actual_gr_qty - s.actual_return_qty) AS net_received_qty,
    s.odoo_recorded_received,
    ((s.actual_gr_qty - s.actual_return_qty) - s.qty_ordered) AS real_excess_qty
FROM po_stock_summary s
JOIN purchase_order po ON po.id = s.order_id
JOIN res_partner rp ON rp.id = po.partner_id
JOIN product_product pp ON pp.id = s.product_id
JOIN product_template pt ON pt.id = pp.product_tmpl_id
WHERE s.actual_gr_qty > s.qty_ordered  -- True double GR
ORDER BY po.name DESC;