Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create type at_address_type as object(street varchar2(10), city varchar2(10), postcode varchar2(8))
/
create type at_name_type as object(title varchar2(5), firstname varchar2(10),surname varchar2(10))
/
create type at_phone_type as object (x varchar2(15))
/
create type at_nested_phone as table of at_phone_type
/
create type at_job_type as object (position varchar2(10))
/
create type at_branch_type as object (branch_code number)
/
CREATE TYPE at_person_type AS OBJECT(
	address at_address_type,
	name at_name_type,
	phones at_nested_phone,
	ni_num VARCHAR2(5)
)NOT FINAL;
/	
CREATE TYPE at_employee_type UNDER at_person_type(
	emp_id NUMBER,
	supervisor_r REF at_employee_type,  -- NOTE supervisor is a reference to the same type
	job_r REF at_job_type,
	branch_r REF at_branch_type,
	join_date DATE
)FINAL;
/
CREATE TABLE at_employee_table of at_employee_type(
	emp_id PRIMARY KEY,
	
	CONSTRAINT check_emp_address
		CHECK (
			address.street IS NOT NULL
			AND
			address.city IS NOT NULL
			AND
			address.postcode IS NOT NULL
		),
	CONSTRAINT check_emp_name
		CHECK (
			name.title IS NOT NULL
			AND		
			name.firstname IS NOT NULL
			AND
			name.surname IS NOT NULL
		),
	CONSTRAINT check_emp_ni CHECK (ni_num IS NOT NULL),
	CONSTRAINT unique_emp_ni UNIQUE (ni_num),
	CONSTRAINT check_emp_job CHECK (job_r IS NOT NULL),
	CONSTRAINT check_emp_branch CHECK (branch_r IS NOT NULL),
	CONSTRAINT check_emp_join CHECK (join_date IS NOT NULL)
)NESTED TABLE phones STORE AS at_nested_emp_phones_storage
/
alter table at_employee_table add constraint x check (
supervisor_r.job_r.position IN ('head','manager','team leader') 
AND
supervisor_r.branch_r.branch_code = branch_r.branch_code
)
ORA-22808: REF dereferencing not allowed

CREATE TRIGGER at_employee_trig
BEFORE INSERT ON at_employee_table
FOR EACH ROW
DECLARE
  supervisor_job at_job_type;
  supervisor_branch at_branch_type;
  branch at_branch_type;
BEGIN
  SELECT DEREF(:new.branch_r),
    DEREF(DEREF(:new.supervisor_r).job_r),
    DEREF(DEREF(:new.supervisor_r).branch_r)
  INTO branch, supervisor_job, supervisor_branch
  FROM dual;

  IF supervisor_job.position NOT IN ('head','manager','team leader')  
  OR supervisor_branch.branch_code != branch.branch_code
  THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid supervisor');
  END IF;
END;
/
select * from user_errors