last_update.sql

CREATE OR REPLACE VIEW pds_retailer_inventory_latest_vu AS 
         SELECT a.id,
            a.created,
            a.updated,
            greatest(a.created, a.updated) as last_updated,
            (SELECT a.updated WHERE a.retailer_inventory_location_id = l.id ORDER BY a.updated DESC LIMIT 1) as last_inventory_run,
            a.sku,
            a.available_qty,
            a.retailer_inventory_location_id,
            l.locator_id,
            l.longitude,
            l.latitude,
            l.location_type,
            l.address,
            l.city,
            l.postal_code,
            l.state,
            l.country,
            r.name,
            r.market,
            r.is_opted_in
           FROM pds_retailer_inventory_latest_vu_idx li
             JOIN pds_retailer_inventory a ON li.max_id = a.id
             JOIN pds_retailer_inventory_location l ON a.retailer_inventory_location_id = l.id
             JOIN pds_retailer r ON l.retailer_id = r.id
           WHERE a.available_qty > 0;