Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
 CREATE TABLE "TEST1" 
   (    "DEPARTMENT" NUMBER(3,0), 
    "VALUE1" VARCHAR2(26 BYTE), 
    "VALUE2" VARCHAR2(26 BYTE), 
    "CHECK_CONDITION1" NUMBER(3,0), 
    "CHECK_CONDITION2" NUMBER(3,0)
   ) ;
  CREATE TABLE "TEST3" 
   (    "DEPARTMENT" NUMBER(3,0), 
    "VALUE1" VARCHAR2(26 BYTE), 
    "VALUE2" VARCHAR2(26 BYTE), 
    "VALUE3" VARCHAR2(26 BYTE)
   );
BEGIN
Insert into TEST1 (DEPARTMENT,VALUE1,VALUE2,CHECK_CONDITION1,CHECK_CONDITION2) values (1,'A','Z',1,0);
Insert into TEST1 (DEPARTMENT,VALUE1,VALUE2,CHECK_CONDITION1,CHECK_CONDITION2) values (1,'B','Y',1,0);


Insert into TEST3 (DEPARTMENT,VALUE1,VALUE2,VALUE3) values (1,'A','T','Whatever');
Insert into TEST3 (DEPARTMENT,VALUE1,VALUE2,VALUE3) values (1,'Z','Y','Whatever');
Insert into TEST3 (DEPARTMENT,VALUE1,VALUE2,VALUE3) values (1,'B','Y','Whatever');
END;
/
1 rows affected

select distinct t3.*
from   test3 t3
       INNER JOIN test1 t1
       ON ( t3.department=t1.department )
WHERE  t3.value1 not in (
         select value1
         from   test1
         where  department=t3.DEPARTMENT
         AND    t1.CHECK_CONDITION1 = 1
       )


DEPARTMENT
VALUE1
VALUE2
VALUE3


1
Z
Y
Whatever


update test1 set check_condition1 = 0;
2 rows affected

select distinct t3.*
from   test3 t3
       INNER JOIN test1 t1
       ON ( t3.department=t1.department )
WHERE  t3.value1 not in (
         select value1
         from   test1
         where  department=t3.DEPARTMENT
         AND    t1.CHECK_CONDITION1 = 1
       )


DEPARTMENT
VALUE1
VALUE2
VALUE3


1
B
Y
Whatever

1
A
T
Whatever

1
Z
Y
Whatever