Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE box (id, code, expiration_date) AS
SELECT 1, 'Box_1', DATE '2001-01-01' FROM DUAL UNION ALL
SELECT 2, 'Box_2', DATE '2024-01-01' FROM DUAL UNION ALL
SELECT 3, 'Box_3', DATE '2001-01-01' FROM DUAL UNION ALL
SELECT 4, 'Box_4', DATE '2001-01-01' FROM DUAL UNION ALL
SELECT 5, 'Box_5', NULL FROM DUAL;
5 rows affected

CREATE TABLE component_a (id, b_id) AS
SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 6;
6 rows affected

CREATE TABLE component_z (id, a_id) AS
SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 6;
6 rows affected

CREATE TABLE "FILE" (id, z_id, expiration_date) AS
SELECT  1, 1, DATE '1999-01-01' FROM DUAL UNION ALL
SELECT  2, 1, DATE '2023-01-01' FROM DUAL UNION ALL
SELECT  3, 2, DATE '1999-01-01' FROM DUAL UNION ALL
SELECT  4, 2, DATE '2002-01-01' FROM DUAL UNION ALL
SELECT  5, 3, DATE '1999-01-01' FROM DUAL UNION ALL
SELECT  6, 3, DATE '2001-01-01' FROM DUAL UNION ALL
SELECT  7, 4, DATE '2001-01-01' FROM DUAL UNION ALL
SELECT  8, 4, NULL FROM DUAL UNION ALL
SELECT  9, 5, DATE '2001-01-01' FROM DUAL UNION ALL
SELECT 10, 6, DATE '2001-01-01' FROM DUAL;
10 rows affected

SELECT b.code,
   MAX(b.EXPIRATION_DATE) AS expiration_date
FROM   box b
   join COMPONENT_A cpp on cpp.b_Id=b.Id
   join COMPONENT_Z cpt on cpt.A_Id=cpp.Id
   join "FILE" f on f.Z_Id =cpt.Id
WHERE  b.EXPIRATION_DATE < DATE '2022-01-01'
GROUP BY b.code
HAVING MAX(f.EXPIRATION_DATE) < DATE '2022-01-01'
AND    COUNT(CASE WHEN f.EXPIRATION_DATE IS NULL THEN 1 END) = 0;


CODE
EXPIRATION_DATE


Box_3
01-JAN-01


-- Add a duplicate box.
INSERT INTO box (id, code, expiration_date)
VALUES (6, 'Box_5', DATE '2001-01-01');
1 rows affected

SELECT b.code,
   MAX(b.EXPIRATION_DATE) AS expiration_date
FROM   box b
   join COMPONENT_A cpp on cpp.b_Id=b.Id
   join COMPONENT_Z cpt on cpt.A_Id=cpp.Id
   join "FILE" f on f.Z_Id =cpt.Id
WHERE  b.EXPIRATION_DATE < DATE '2022-01-01'
GROUP BY b.code
HAVING MAX(f.EXPIRATION_DATE) < DATE '2022-01-01'
AND    COUNT(CASE WHEN f.EXPIRATION_DATE IS NULL THEN 1 END) = 0;


CODE
EXPIRATION_DATE


Box_3
01-JAN-01

Box_5
01-JAN-01


-- Fixed for duplicate boxes.
SELECT b.code,
   MAX(b.EXPIRATION_DATE) AS expiration_date
FROM   box b
   join COMPONENT_A cpp on cpp.b_Id=b.Id
   join COMPONENT_Z cpt on cpt.A_Id=cpp.Id
   join "FILE" f on f.Z_Id =cpt.Id
WHERE  b.EXPIRATION_DATE < DATE '2022-01-01'
OR     b.expiration_date IS NULL
GROUP BY b.code
HAVING MAX(f.EXPIRATION_DATE) < DATE '2022-01-01'
AND    COUNT(CASE WHEN b.EXPIRATION_DATE IS NULL THEN 1 END) = 0
AND    COUNT(CASE WHEN f.EXPIRATION_DATE IS NULL THEN 1 END) = 0;


CODE
EXPIRATION_DATE


Box_3
01-JAN-01