SQL & PL/SQL

I have a query that is returning inconsistent results generally it works just fine but in some cases it does not return the correct results. Below is the dummied up query

SELECT max(trans_id), item_id FROM product_sales

WHERE location_ id = 1234

   AND purchase\_dt ≤ LAST\_DAY(TO\_DATE('05/01/2025','MM/DD/YYYY'))

   AND sent\_dt is null    

   AND trans\_status <> ‘S’     

GROUP BY item_id;

Trans_id Item_id trans_status purchase_dt sent_dt location_id

1234 222 P 05/01/2025 null 1234

1235 222 P 05/01/2025 null 1234

Now what should come back is the record with trans_id =1235 and what is coming back is trans_id = 1234. Any ideas? My only thought is to create a view to break up the processing? Does the database need to be tweeked to handle this? This is by no means a monster database.