create table data_tbl as
with data_tbl as
-- Actual table
(select '1234' as old_cust_id, '3456' as new_cust_id, 'I' as status_code from dual
 union
 select '3456' as old_cust_id, '6789' as new_cust_id, 'I' as status_code from dual
 union
 select '6789' as old_cust_id, '6789' as new_cust_id, 'A' as status_code from dual
 union
 select '9089' as old_cust_id, '7842' as new_cust_id, 'I' as status_code from dual
 union
 select '7842' as old_cust_id, '8964' as new_cust_id, 'I' as status_code from dual
 union
 select '8964' as old_cust_id, '8964' as new_cust_id, 'A' as status_code from dual
 )
 select * from data_tbl;

-- Identify the customer records where the status of cust_id in new_cust_id is inactive (i.e. status_code = 'I') but didn't updated to most recent new_cust_id 
 -- FOr example cust_id 1234 changed to 3456 and cust_id 3456 changed to 6789 but didn't updated the new_cust_id to 6789 wherever new_cust_id is 3456

 create table upd_tbl as 
with upd_tbl as
(select a.old_cust_id, a.new_cust_id, b.status_code
    from data_tbl a, data_tbl b
   where a.new_cust_id = b.old_cust_id
       and b.status_code = 'I'
)
select * from upd_tbl;

1234	3456	I
9089	7842	I

WITH final_tbl (old_cust_id, new_cust_id, status_code, lvl)
     AS (SELECT old_cust_id,
                new_cust_id,
                status_code,
                1 AS lvl
           FROM upd_tbl
         UNION ALL
         SELECT b.new_cust_id,
                a.new_cust_id,
                a.status_code,
                b.lvl + 1
           FROM data_tbl a, final_tbl b
          WHERE b.new_cust_id = a.old_cust_id)
           SEARCH DEPTH FIRST BY old_cust_id SET SORTING
           CYCLE new_cust_id SET is_cycle TO 1 DEFAULT 0
  select old_cust_id, new_cust_id, status_code,lvl from final_tbl;

1234	3456	I	1
3456	6789	I	2
6789	6789	A	3
9089	7842	I	1
7842	8964	I	2
8964	8964	A	3

SQL> with data_tbl as
  2  -- Actual table
  3  (select '1234' as old_cust_id, '3456' as new_cust_id, 'I' as status_code from dual
  4   union
  5   select '3456' as old_cust_id, '6789' as new_cust_id, 'I' as status_code from dual
  6   union
  7   select '6789' as old_cust_id, '6789' as new_cust_id, 'A' as status_code from dual
  8   union
  9   select '9089' as old_cust_id, '7842' as new_cust_id, 'I' as status_code from dual
 10   union
 11   select '7842' as old_cust_id, '8964' as new_cust_id, 'I' as status_code from dual
 12   union
 13   select '8964' as old_cust_id, '8964' as new_cust_id, 'A' as status_code from dual
 14   )
 15  select old_cust_id, new_cust_id, status_code
 16  from data_tbl
 17  connect by prior new_cust_id = old_cust_id and prior status_code = 'I'
 18  start with old_cust_id = '1234' and status_code = 'I'
 19  /
OLD_ NEW_ S
---- ---- -
1234 3456 I
3456 6789 I
6789 6789 A

with data_tbl as
    -- Actual table
    (select '1234' as old_cust_id, '3456' as new_cust_id, 'I' as status_code from dual
     union
     select '3456' as old_cust_id, '6789' as new_cust_id, 'I' as status_code from dual
     union
     select '6789' as old_cust_id, '8712' as new_cust_id, 'I' as status_code from dual
     union
     select '8712' as old_cust_id, '8712' as new_cust_id, 'A' as status_code from dual
     union
     select '9089' as old_cust_id, '7842' as new_cust_id, 'I' as status_code from dual
    union
    select '7842' as old_cust_id, '8964' as new_cust_id, 'I' as status_code from dual
    union
    select '8964' as old_cust_id, '8964' as new_cust_id, 'A' as status_code from dual
    )
   select connect_by_root old_cust_id, new_cust_id, status_code
   from data_tbl where status_code = 'A'
   connect by prior new_cust_id = old_cust_id and prior status_code = 'I'
   start with status_code = 'I'

1234	8712	A
3456	8712	A
6789	8712	A
7842	8964	A
9089	8964	A