Retrieving data from another table after using MIN function

You can JOIN the tables and then use MIN(...) KEEP (DENSE_RANK FIRST ORDER BY ...) to find the minimum value out of those rows that are the minimum of other column(s).

If, for each ITEM_ID, you want to find the earliest CREATE_DATE_TIME and, within those rows with the earliest time, the rows with the minimum BATCH_NBR and then the maximum XPIRE_DATE then you can use:

SELECT MIN(PT.CREATE_DATE_TIME) AS create_date_time,
       PT.ITEM_ID, 
       MIN(PT.BATCH_NBR)
         KEEP (DENSE_RANK FIRST ORDER BY pt.create_date_time)
         AS batch_nbr,
       MAX(BM.XPIRE_DATE)
         KEEP (DENSE_RANK FIRST ORDER BY pt.create_date_time, pt.batch_nbr)
         AS xpire_nbr
FROM   PIX_TRAN PT
       INNER JOIN BATCH_MASTER BM
       ON PT.ITEM_ID = BM.ITEM_ID AND PT.BATCH_NBR = BM.BATCH_NBR
WHERE  PT.TRAN_TYPE = '605'
AND    PT.TRAN_CODE = '01'
AND    PT.CREATE_DATE_TIME >= DATE '2025-07-16'
AND    PT.CREATE_DATE_TIME <  DATE '2025-07-17'
GROUP BY
       PT.ITEM_ID
ORDER BY
       PT.ITEM_ID;

An alternate method of accomplishing the same thing is to use the ROW_NUMBER analytic function:

SELECT CREATE_DATE_TIME,
       ITEM_ID, 
       BATCH_NBR,
       XPIRE_DATE
FROM   (
  SELECT PT.CREATE_DATE_TIME,
         PT.ITEM_ID, 
         PT.BATCH_NBR,
         BM.XPIRE_DATE,
         ROW_NUMBER() OVER (
           PARTITION BY pt.item_id
           ORDER BY pt.create_date_time, pt.batch_nbr, bm.xpire_date DESC
         ) AS rn
  FROM   PIX_TRAN PT
         INNER JOIN BATCH_MASTER BM
         ON PT.ITEM_ID = BM.ITEM_ID AND PT.BATCH_NBR = BM.BATCH_NBR
  WHERE  PT.TRAN_TYPE = '605'
  AND    PT.TRAN_CODE = '01'
  AND    PT.CREATE_DATE_TIME >= DATE '2025-07-16'
  AND    PT.CREATE_DATE_TIME <  DATE '2025-07-17'
)
WHERE  rn = 1
ORDER BY
       ITEM_ID;

Which, using the sample data (adapted from the dataset in Guillaume Outters' comment):

CREATE TABLE PIX_TRAN(CREATE_DATE_TIME, BATCH_NBR, ITEM_ID, TRAN_TYPE, TRAN_CODE) AS
SELECT DATE '2025-07-16' + INTERVAL '08:30:00' HOUR TO SECOND, 1, 1, '605', '01' FROM DUAL UNION ALL
SELECT DATE '2025-07-16' + INTERVAL '08:30:00' HOUR TO SECOND, 2, 1, '605', '01' FROM DUAL UNION ALL
SELECT DATE '2025-07-16' + INTERVAL '08:30:00' HOUR TO SECOND, 3, 2, '605', '01' FROM DUAL UNION ALL
SELECT DATE '2025-07-16' + INTERVAL '08:30:00' HOUR TO SECOND, 4, 2, '605', '01' FROM DUAL UNION ALL
SELECT DATE '2025-07-16' + INTERVAL '08:46:00' HOUR TO SECOND, 5, 1, '605', '01' FROM DUAL UNION ALL
SELECT DATE '2025-07-16' + INTERVAL '08:52:00' HOUR TO SECOND, 6, 2, '605', '01' FROM DUAL UNION ALL
SELECT DATE '2025-07-16' + INTERVAL '09:11:00' HOUR TO SECOND, 7, 1, '605', '01' FROM DUAL;

CREATE TABLE BATCH_MASTER (ITEM_ID, BATCH_NBR, XPIRE_DATE) AS
SELECT 1, 1, DATE '2025-07-17' FROM DUAL UNION ALL
SELECT 1, 2, DATE '2025-07-18' FROM DUAL UNION ALL
SELECT 1, 5, DATE '2025-07-19' FROM DUAL UNION ALL
SELECT 1, 7, DATE '2025-07-20' FROM DUAL UNION ALL
SELECT 2, 3, DATE '2025-07-19' FROM DUAL UNION ALL
SELECT 2, 4, DATE '2025-07-20' FROM DUAL UNION ALL
SELECT 2, 6, DATE '2025-07-21' FROM DUAL;

Both output:

Note: You can apply TO_CHAR to the dates if you wish to reformat them; however, the sorting/aggregation needs to be performed on the unformatted dates.

fiddle

The straight forward way to join an aggregation result to another table, is, well, joining them:

select ...
from ( <aggregation query here> ) q
join some_table t on ...

Or with a CTE (WITH clause):

with q as ( <aggregation query here> )
select ...
from q
join some_table t on ...

In your case:

WITH
  item_batch_today AS
  (
    SELECT 
      TRUNC(SYSDATE) AS shipping_date,
      item_id,
      MIN(batch_nbr) AS first_batch_nbr
    FROM pix_tran pt
    WHERE tran_type = '605'
    AND tran_code = '01'
    AND create_date_time >= TRUNC(SYSDATE)
    GROUP BY item_id
  )
SELECT
  ibt.shipping_date,
  ibt.item_id,
  ibt.first_batch_nbr,
  bm.xpire_date
FROM item_batch_today ibt
JOIN batch_master bm ON bm.item_id = ibt.item_id
                    AND bm.batch_nbr = ibt.first_batch_nbr
ORDER BY ibt.item_id;

If you decide to select a range of dates or all dates rather than just today, then you'll have to group by TRUNC(create_date_time) and select this instead of TRUNC(SYSDATE).

Grouping by day

As long as you GROUP BY, any aggregate function (here MIN()) is not global anymore, but becomes local to the group.

Thus when you say "the oldest batch we have shipped today", but have a GROUP BY PT.CREATE_DATE_TIME, PT.ITEM_ID, I would tend to correct your sentence to "the oldest batch we have shipped today for each ITEM_ID and CREATE_DATE_TIME".
That is, your first query won't return 1 batch, but it will return 1 per ITEM_ID if all batches for this ITEM_ID for this day where created at the same time.

So first of all you probably want to GROUP BY TO_CHAR(PT.CREATE_DATE_TIME, 'MM/DD/YY') (or TRUNC(PT.CREATE_DATE_TIME) which will be more efficient).

Adding BATCH_MASTER

But as you have then added BM.XPIRE_DATE to your GROUP BY, we get the same problem: you will have one row per expiry date (that is, if in the day you've got one MIN over GROUP BY PT.ITEM_ID, TRUNC(PT.CREATE_DATE_TIME), but you've sent somes items expiring tomorrow and some in two days, you'll still get two results.

Getting the minimum value over a set is easy (you've done it with a MIN()).

However, getting another column of the row corresponding to your first column's minimum is not so easy.

There are two ways to get it:

1. either get the MIN(), then in a second query, filtered on rows having the same value, do fetch the info you need
2. or filter to only the first row of each subset of your GROUP BY, then you can directly access the value

Note that 1. will only work if each MIN() is unique.
In your case (MIN(BATCH_NBR)) this works because all BATCH_NBRs are different so by re-filtering against that MIN you'll get only one matching row, but if we had used MIN(CREATE_DATE_TIME) instead, and two batches were created at the exact same time, then when re-filtering on that time you would still get two rows for your final result.

WHERE BATCH_NBR = (SELECT MIN(BATCH_NBR))

What we will do is first have a query that will emit the MIN per day and ITEM_ID, then use it as a JOIN criteria to get back our full row with the same BATCH_NBR.

This two-steps method can either be done in a subquery (column-like subquery: WHERE BATCH_NBR = (SELECT MIN(BATCH_NBR)), or table-like subquery: FROM (SELECT MIN(BATCH_NBR)) AS MINI JOIN …),
or a Common Table Expression: WITH MINI AS (SELECT MIN(BATCH_NBR)) … FROM MINI JOIN ….

Let us use the Common Table Expression, which I find clearer:

WITH MINI AS
(
-- Here we paste your first query, fixed:
SELECT
 TRUNC(PT.CREATE_DATE_TIME) CREATE_DAY,
 PT.ITEM_ID, 
 MIN(PT.BATCH_NBR) FIRST_BATCH_NBR
FROM 
 PIX_TRAN PT
WHERE 
 PT.TRAN_TYPE = '605'
AND
 PT.TRAN_CODE = '01'
AND
 TRUNC(PT.CREATE_DATE_TIME) = '07/16/25'
GROUP BY
 TRUNC(PT.CREATE_DATE_TIME),
 PT.ITEM_ID
ORDER BY
 PT.ITEM_ID
)
SELECT 
     TO_CHAR(PT.CREATE_DATE_TIME, 'MM/DD/YY'),
     PT.ITEM_ID, 
     FIRST_BATCH_NBR,
     BM.XPIRE_DATE
    FROM
     MINI -- Now we can use MINI as if it were a table, and join PIX_TRAN to it:
    JOIN PIX_TRAN PT ON (PT.ITEM_ID = MINI.ITEM_ID AND TRUNC(PT.CREATE_DATE_TIME) = MINI.CREATE_DAY AND PT.BATCH_NBR = MINI.FIRST_BATCH_NBR)
    JOIN BATCH_MASTER BM ON (BM.BATCH_NBR = PT.BATCH_NBR AND BM.ITEM_ID = PT.ITEM_ID)
    WHERE 
     PT.TRAN_TYPE = '605'
    AND
     PT.TRAN_CODE = '01'
    AND
     TO_CHAR(PT.CREATE_DATE_TIME, 'MM/DD/YY') = '07/16/25'
    ORDER BY
     PT.ITEM_ID;

Tag with position and keep first of subset

The other method uses analytic functions, that can associate every row with its position (or other info) relative to other rows of the resultset.

So if we are able to get each row its position relative to other shippings of the same ITEM_ID on that day, we will then just have to filter on shippings with position = 1.

And we are lucky: this is exactly what ROW_NUMBER() OVER (PARTITION BY TRUNC(PT.CREATE_DATE_TIME), PT.ITEM_ID ORDER BY PT.BATCH_NBR) will return.

But as analytic functions are called after the WHERE (they give information for the row between its siblings in the WHERE-filtered resultset), we cannot use them to further filter: we have to use it only for adding a column to the resultset, then use this full resultset, either as a subquery or CTE, in another SELECT that will do the final filter.

SELECT * FROM
(
    SELECT 
     TRUNC(PT.CREATE_DATE_TIME) CREATE_DAY,
     PT.ITEM_ID, 
     PT.BATCH_NBR,
     BM.XPIRE_DATE,
     ROW_NUMBER() OVER (PARTITION BY TRUNC(PT.CREATE_DATE_TIME), PT.ITEM_ID ORDER BY PT.BATCH_NBR) AS position
    FROM 
     PIX_TRAN PT
    JOIN BATCH_MASTER BM ON (BM.BATCH_NBR = PT.BATCH_NBR AND BM.ITEM_ID = PT.ITEM_ID)
    WHERE 
     PT.TRAN_TYPE = '605'
    AND
     PT.TRAN_CODE = '01'
    AND
     TRUNC(PT.CREATE_DATE_TIME) = '07/16/25'
)
WHERE position = 1
ORDER BY
 ITEM_ID;

Demo!

You will find those queries running on a small test dataset in a db<>fiddle.