Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table person (
  id number primary key
, registration_number varchar2(9) unique
, primary_number varchar2(9)
-- , women_act varchar2(1)   <- not needed!
); 
create table consolidated_numbers (
  secondary_number varchar2(9) references person( registration_number )
, person_id number references person( id )
); 
create table code (
  valid_code varchar2(2) primary key
);
-- CHECK constraint added to allow only certain TYPE_IDs
create table history_transaction (
  reason varchar2(2) references code( valid_code ) -- valid VALID_CODEs :-) enforced by FK constraint
, person_id number references person( id )
, type_id number check (
    type_id in (
      120, 140, 1420, 1440, 160, 180, 150, 1520, 1540, 1560  -- only allow these type_ids
    )
  )
, action_date date
);
-- INSERT your initial test data
begin   
  insert into person (ID,registration_number,primary_number) values(132, '000000001', null);
  insert into person (ID,registration_number,primary_number) values (151, '000000002', '000000001');
  insert into consolidated_numbers (SECONDARY_NUMBER,person_id) values ('000000002', 132);
  insert into code (valid_code) values ('A1');
  insert into code (valid_code) values ('T1');
  insert into code (valid_code) values ('N2');
  insert into history_transaction (reason,person_id,type_id,action_date) 
    values ('A1', 132, 1420, DATE '2019-01-01');
  commit ;
end ;
/  
1 rows affected

select * from person ;


ID
REGISTRATION_NUMBER
PRIMARY_NUMBER


132
000000001
null

151
000000002
000000001


select * from consolidated_numbers ;


SECONDARY_NUMBER
PERSON_ID


000000002
132


select * from code ;


VALID_CODE


A1

N2

T1


select * from history_transaction ;


REASON
PERSON_ID
TYPE_ID
ACTION_DATE


A1
132
1420
01-JAN-19


create or replace view personx
as
with PID as (
  select distinct person_id
  from history_transaction
)
select person_id, 'X' as woman_act  -- [Q1] all person_ids from history_transaction
from PID
union
select P.id, 'X' as woman_act       -- [Q2] all person_ids associated with ids from Q1
from person P
  join consolidated_numbers C
    on P.registration_number = C.secondary_number
    and C.person_id in (
      select person_id from PID
    )
;
select * from personx ;


PERSON_ID
WOMAN_ACT


132
X

151
X


-- test 1
delete from history_transaction ;
1 rows affected

select * from personx ;
-- test 2
insert into history_transaction (reason,person_id,type_id,action_date) 
  values ('A1', 132, 1420, DATE '2019-01-01');
1 rows affected

select * from personx ;


PERSON_ID
WOMAN_ACT


132
X

151
X


-- test 3: add more associations
begin   
-- existing:  insert into person (ID,registration_number,primary_number) values(132, '000000001', null);
-- existing:  insert into person (ID,registration_number,primary_number) values (151, '000000002', '000000001');
-- new: person 345 associated with person 132
  insert into person (ID,registration_number,primary_number) values (345, '000000345', '000000001');
  insert into consolidated_numbers (SECONDARY_NUMBER,person_id) values ('000000345', 132);
  commit ;
end ;
/
1 rows affected

select * from personx ;


PERSON_ID
WOMAN_ACT


132
X

151
X

345
X


-- test 4
-- add more associations 
-- no entry in history_transactions for person(id) 1000        
begin   
  insert into person (ID,registration_number,primary_number) values(1000, '000000777', null);
  insert into person (ID,registration_number,primary_number) values (2000, '000000778', '000000777');
  insert into consolidated_numbers (SECONDARY_NUMBER,person_id) values ('000000778', 1000);
  commit ;
end ;
/ 
1 rows affected

select * from personx ;


PERSON_ID
WOMAN_ACT


132
X

151
X

345
X


-- test 5
-- add an entry from person 1000 into the history_transaction table
insert into history_transaction (reason,person_id,type_id,action_date) 
    values ('N2', 1000, 1420, sysdate);  
1 rows affected

select * from personx ;


PERSON_ID
WOMAN_ACT


132
X

151
X

345
X

1000
X

2000
X


-- show more details
select P.id, P.registration_number, P.primary_number, PX.woman_act
from personx PX right join person P on PX.person_id = P.id ;


ID
REGISTRATION_NUMBER
PRIMARY_NUMBER
WOMAN_ACT


132
000000001
null
X

151
000000002
000000001
X

345
000000345
000000001
X

1000
000000777
null
X

2000
000000778
000000777
X


-- test 6
-- add more associations 
-- no entry in history_transactions for person(id) 10000       
begin   
  insert into person (ID,registration_number,primary_number) values(10000, '000007777', null);
  insert into person (ID,registration_number,primary_number) values (20000, '000007778', '000007777');
  insert into consolidated_numbers (SECONDARY_NUMBER,person_id) values ('000007778', 10000);
  commit ;
end ;
/ 
1 rows affected

select P.id, P.registration_number, P.primary_number, PX.woman_act
from personx PX right join person P on PX.person_id = P.id ;


ID
REGISTRATION_NUMBER
PRIMARY_NUMBER
WOMAN_ACT


132
000000001
null
X

151
000000002
000000001
X

345
000000345
000000001
X

1000
000000777
null
X

2000
000000778
000000777
X

20000
000007778
000007777
null

10000
000007777
null
null
ID	REGISTRATION_NUMBER	PRIMARY_NUMBER	WOMAN_ACT
132	000000001	null	X
151	000000002	000000001	X
345	000000345	000000001	X
1000	000000777	null	X
2000	000000778	000000777	X