cursor get_details is 
SELECT 
SUPERSEDED_PART,
PART_STATUS
FROM PART_INO
WHERE PART_NO = PART_NO_;

cursor get_details is 
SELECT 
SUPERSEDED_PART,
PART_STATUS
FROM PART_INO
WHERE PART_NO = PART_NO_;

BEGIN

for get_details_a in get_details loop
if get_details_a.STATUS = '1' then
return_value_ := part_no
end if
if get_details_a.STATUS = '2' then
part_no_ := get_details_a.SUPERSEDED_PART
--REPEAT THROUGH LOOP AGAIN until the STATUS = 1

Does that make more sense?


return return_value_

SCOTT@orcl_12.1.0.2.0> -- version:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

SCOTT@orcl_12.1.0.2.0> -- statements to create table with primary key and insert sample data like we expected you to provide:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE part_ino
  2    (part_no 	 VARCHAR2(15) PRIMARY KEY,
  3  	status		 VARCHAR2(8),
  4  	superseded_part  VARCHAR2(15))
  5  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2    INTO part_ino VALUES ('PART_A', 'ACTIVE', 'NULL')
  3    INTO part_ino VALUES ('PART_B', 'INACTIVE', 'PART_C')
  4    INTO part_ino VALUES ('PART_C', 'ACTIVE', 'NULL')
  5    INTO part_ino VALUES ('PART_D', 'INACTIVE', 'PART_E')
  6    INTO part_ino VALUES ('PART_E', 'INACTIVE', 'PART_F')
  7    INTO part_ino VALUES ('PART_F', 'ACTIVE', 'NULL')
  8  SELECT * FROM DUAL
  9  /

6 rows created.

SCOTT@orcl_12.1.0.2.0> -- resultng sample data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM part_ino
  2  /

PART_NO         STATUS   SUPERSEDED_PART
--------------- -------- ---------------
PART_A          ACTIVE   NULL
PART_B          INACTIVE PART_C
PART_C          ACTIVE   NULL
PART_D          INACTIVE PART_E
PART_E          INACTIVE PART_F
PART_F          ACTIVE   NULL

6 rows selected.

SCOTT@orcl_12.1.0.2.0> COLUMN active_part FORMAT A15
SCOTT@orcl_12.1.0.2.0> SELECT p2.part_no, p2.status, p2.superseded_part,
  2  	    (SELECT  MAX (p1.part_no) KEEP (DENSE_RANK LAST ORDER BY LEVEL)
  3  	     FROM    part_ino p1
  4  	     START   WITH p1.part_no = p2.part_no
  5  	     CONNECT BY PRIOR p1.superseded_part = p1.part_no
  6  		    AND PRIOR p1.status != 'ACTIVE') AS active_part
  7  FROM   part_ino p2
  8  /

PART_NO         STATUS   SUPERSEDED_PART ACTIVE_PART
--------------- -------- --------------- ---------------
PART_A          ACTIVE   NULL            PART_A
PART_B          INACTIVE PART_C          PART_C
PART_C          ACTIVE   NULL            PART_C
PART_D          INACTIVE PART_E          PART_F
PART_E          INACTIVE PART_F          PART_F
PART_F          ACTIVE   NULL            PART_F

6 rows selected.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION active_part
  2    (part_no_       IN part_ino.part_no%TYPE)
  3    RETURN		  part_ino.part_no%TYPE
  4  AS
  5    superseded_part_  part_ino.superseded_part%TYPE;
  6    status_		 part_ino.status%TYPE;
  7    return_value_	 part_ino.part_no%TYPE;
  8  BEGIN
  9    SELECT superseded_part, status
 10    INTO   superseded_part_, status_
 11    FROM   part_ino
 12    WHERE  part_no = part_no_;
 13    IF status_ = 'ACTIVE' THEN return_value_ := part_no_;
 14    ELSIF status_ = 'INACTIVE' THEN return_value_ := active_part (superseded_part_);
 15    END IF;
 16    RETURN return_value_;
 17  END active_part;
 18  /

Function created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> COLUMN active_part FORMAT A15
SCOTT@orcl_12.1.0.2.0> SELECT part_no, status, superseded_part,
  2  	    active_part (part_no) AS active_part
  3  FROM   part_ino
  4  ORDER  BY part_no
  5  /

PART_NO         STATUS   SUPERSEDED_PART ACTIVE_PART
--------------- -------- --------------- ---------------
PART_A          ACTIVE   NULL            PART_A
PART_B          INACTIVE PART_C          PART_C
PART_C          ACTIVE   NULL            PART_C
PART_D          INACTIVE PART_E          PART_F
PART_E          INACTIVE PART_F          PART_F
PART_F          ACTIVE   NULL            PART_F

6 rows selected.