Multi-table hieararchy

You must execute the code again to save those values in the database.

create table projects
( project_id varchar(14) not null
, parent_project_id varchar(14) not null);

create table tasks
( project_id varchar(14) not null
, task_id varchar(6) not null
, parent_task_id varchar(6) not null
, inactive char(1) not null
, redefined char(1) not null
);

insert into projects (project_id, parent_project_id) values ('MASTER_PROJECT', ' ');
insert into projects (project_id, parent_project_id) values ('EXECUTION_PROJ', 'MASTER_PROJECT');
insert into projects (project_id, parent_project_id) values ('UNRELATED_PROJ', ' ');

alter table projects
add constraint projects_pk
primary key (project_id);

insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('EXECUTION_PROJ', '101000', '100000', 'X', 'X');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('EXECUTION_PROJ', '101010', '101000', 'X', ' ');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('EXECUTION_PROJ', '101011', '101010', 'X', ' ');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('EXECUTION_PROJ', '101012', '101010', ' ', ' ');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('EXECUTION_PROJ', '102000', '100000', ' ', 'X');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('EXECUTION_PROJ', '102010', '102000', ' ', ' ');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('EXECUTION_PROJ', '102011', '102010', ' ', ' ');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('EXECUTION_PROJ', '210000', '200000', ' ', ' ');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('EXECUTION_PROJ', '300000', '000000', ' ', 'X');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('EXECUTION_PROJ', '310000', '000000', 'X', 'X');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('MASTER_PROJECT', '100000', '000000', ' ', ' ');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('MASTER_PROJECT', '101000', '100000', ' ', ' ');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('MASTER_PROJECT', '102000', '100000', 'X', ' ');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('MASTER_PROJECT', '200000', '000000', ' ', ' ');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('MASTER_PROJECT', '300000', '000000', ' ', ' ');
insert into tasks (project_id, task_id, parent_task_id, inactive, redefined) values ('MASTER_PROJECT', '310000', '300000', ' ', ' ');

alter table tasks
add constraint tasks_pk
primary key (project_id, task_id);

with toplvl_proj as (
select project_id from projects
where connect_by_isleaf = 1
connect by prior parent_project_id = project_id
start with project_id = 'EXECUTION_PROJ')

select t.*, p.parent_project_id
from tasks t
join projects p on p.project_id = t.project_id
connect by prior t.task_id = t.parent_task_id
and (prior t.project_id = p.parent_project_id
     or prior t.project_id = t.project_id)
start with t.project_id in (select project_id from projects
                            where connect_by_isleaf = 1
                            connect by prior parent_project_id = project_id
                            start with project_id = 'EXECUTION_PROJ')
                            and t.parent_task_id = '000000'
/

PROJECT_ID	TASK_I	PARENT	I	R	PARENT_PROJECT
MASTER_PROJECT	100000	000000
MASTER_PROJECT	101000	100000
EXECUTION_PROJ	101010	101000	X		MASTER_PROJECT
EXECUTION_PROJ	101011	101010	X		MASTER_PROJECT
EXECUTION_PROJ	101012	101010			MASTER_PROJECT
EXECUTION_PROJ	101000	100000	X	X	MASTER_PROJECT
EXECUTION_PROJ	101010	101000	X		MASTER_PROJECT
EXECUTION_PROJ	101011	101010	X		MASTER_PROJECT
EXECUTION_PROJ	101012	101010			MASTER_PROJECT
MASTER_PROJECT	102000	100000	X
EXECUTION_PROJ	102010	102000			MASTER_PROJECT
EXECUTION_PROJ	102011	102010			MASTER_PROJECT
EXECUTION_PROJ	102000	100000		X	MASTER_PROJECT
EXECUTION_PROJ	102010	102000			MASTER_PROJECT
PROJECT_ID	TASK_I	PARENT	I	R	PARENT_PROJECT
EXECUTION_PROJ	102011	102010			MASTER_PROJECT
MASTER_PROJECT	200000	000000
EXECUTION_PROJ	210000	200000			MASTER_PROJECT
MASTER_PROJECT	300000	000000
MASTER_PROJECT	310000	300000