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
/
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;
/
CREATE TABLE branches OF at_branch_type;
INSERT INTO branches VALUES (at_branch_type(1));
1 rows affected

CREATE TABLE jobs OF at_job_type;
INSERT INTO jobs VALUES (at_job_type('head'));
1 rows affected

INSERT INTO jobs VALUES (at_job_type('X'));
1 rows affected

INSERT INTO at_employee_table (
  emp_Id,
  address,
  name,
  phones,
  ni_num,
  supervisor_r,
  job_r,
  branch_r,
  join_date
) VALUES (
  1,
  at_address_type('street', 'city', 'postcode'),
  at_name_type('t1', 'fname1', 'lname1'),
  at_nested_phone(),
  '00001',
  NULL,
  (SELECT REF(j) FROM jobs j WHERE position = 'head'),
  (SELECT REF(b) FROM branches b WHERE branch_code = 1),
  DATE '1970-01-01'
);
1 rows affected

INSERT INTO at_employee_table (
  emp_Id,
  address,
  name,
  phones,
  ni_num,
  supervisor_r,
  job_r,
  branch_r,
  join_date
) VALUES (
  2,
  at_address_type('street', 'city', 'postcode'),
  at_name_type('t2', 'fname2', 'lname2'),
  at_nested_phone(),
  '00002',
  (SELECT REF(e) FROM at_employee_table e WHERE emp_id = 1),
  (SELECT REF(j) FROM jobs j WHERE position = 'X'),
  (SELECT REF(b) FROM branches b WHERE branch_code = 1),
  DATE '1970-01-01'
);
1 rows affected

INSERT INTO at_employee_table (
  emp_Id,
  address,
  name,
  phones,
  ni_num,
  supervisor_r,
  job_r,
  branch_r,
  join_date
) VALUES (
  3,
  at_address_type('street', 'city', 'postcode'),
  at_name_type('t3', 'fname3', 'lname3'),
  at_nested_phone(),
  '00003',
  (SELECT REF(e) FROM at_employee_table e WHERE emp_id = 2),
  (SELECT REF(j) FROM jobs j WHERE position = 'X'),
  (SELECT REF(b) FROM branches b WHERE branch_code = 1),
  DATE '1970-01-01'
);
ORA-20001: Invalid supervisor
ORA-06512: at "FIDDLE_HYWEWLWKRFTOFCGCZRUO.AT_EMPLOYEE_TRIG", line 17
ORA-04088: error during execution of trigger 'FIDDLE_HYWEWLWKRFTOFCGCZRUO.AT_EMPLOYEE_TRIG'