==================================================================================================
CREATE TABLE PRODUCT_PART_TABLE (MOD_PRODUCT_NUMBER VARCHAR2(50), PRODUCT_HIERARCHY VARCHAR2(50));

INSERT INTO PRODUCT_PART_TABLE VALUES ('H555888' , 'PH05678');
INSERT INTO PRODUCT_PART_TABLE VALUES ('H8889' , 'H555888');
INSERT INTO PRODUCT_PART_TABLE VALUES ('H9955' , 'H555888');
INSERT INTO PRODUCT_PART_TABLE VALUES ('H999999' , 'H555888'); 
INSERT INTO PRODUCT_PART_TABLE VALUES ('P5555' , 'H999999');
INSERT INTO PRODUCT_PART_TABLE VALUES ('P6666' , 'H999999'); 
INSERT INTO PRODUCT_PART_TABLE VALUES ('H999999' , 'PH08968'); 
INSERT INTO PRODUCT_PART_TABLE VALUES ('H8888' , 'H999999');
INSERT INTO PRODUCT_PART_TABLE VALUES ('H7777' , 'H999999'); 

SELECT MOD_PRODUCT_NUMBER,PRODUCT_HIERARCHY
  FROM PRODUCT_PART_TABLE 
GROUP BY MOD_PRODUCT_NUMBER,PRODUCT_HIERARCHY

MOD_PRODUCT_NUMBER	PRODUCT_HIERARCHY
H9955	                H555888
H8889	                H555888
H555888	                PH05678
P6666	                H999999
H8888	                H999999
P5555	                H999999
H7777	                H999999
H999999	                H555888
H999999	                PH08968

====================================================================================================
CREATE TABLE PRODUCT_HIERARCHY (PROD_HIER VARCHAR2(50))

INSERT INTO PRODUCT_HIERARCHY VALUES ('PH05678');
INSERT INTO PRODUCT_HIERARCHY VALUES ('PH08968');

SELECT * 
  FROM PRODUCT_HIERARCHY

PROD_HIER
PH08968
PH05678

=====================================================================================================
CREATE TABLE OUTPUT_HIER (PRODUCT_HIERARCHY VARCHAR2(50) , MOD_PRODUCT_NUM VARCHAR2(50));

SELECT * 
  FROM OUTPUT_HIER 

PROD_HIER	MOD_PRODUCT_NUMBER
----------------------------------
Parent-Child-Relationship
=========================
Output Table Result must be like this am i right

SELECT * FROM OUTPUT_HIER 

==================================
Product Hierarchy | mod_product_num
============================================= 
PH05678 | H555888 | H8889 
PH05678 | H555888 | H9955 
PH05678 | H555888 | H999999 | P5555 
PH05678 | H555888 | H999999 | P6666 
PH05678 | H555888 | H999999 | H8888 
PH05678 | H555888 | H999999 | H7777 
PH08968 | H999999 | H8888 
PH08968 | H999999 | H7777 
PH08968 | H999999 | P5555 
PH08968 | H999999 | P6666 
PH08968 | H999999 | H555888 | H8889 
PH08968 | H999999 | H555888 | H9955
=======================================================================================================
INSERT INTO OUTPUT_HIER
(SELECT PROD_HIER , MOD_PRODUCT_NUMBER 
  FROM PRODUCT_PART_TABLE A,PRODUCT_HIERARCHY B
 WHERE PROD_HIER NOT IN (
                         SELECT  PRODUCT_HIERARCHY 
                           FROM PRODUCT_PART_TABLE 
                          WHERE MOD_PRODUCT_NUMBER NOT IN (
                                                           SELECT A.MOD_PRODUCT_NUMBER 
                                                             FROM PRODUCT_PART_TABLE A,PRODUCT_HIERARCHY B
                                                            WHERE A.PRODUCT_HIERARCHY = B.PROD_HIER 
                                                           )
                        )
  AND MOD_PRODUCT_NUMBER NOT IN (
                                 SELECT A.MOD_PRODUCT_NUMBER 
                                   FROM PRODUCT_PART_TABLE A,PRODUCT_HIERARCHY B
                                  WHERE A.PRODUCT_HIERARCHY = B.PROD_HIER
                                )
)     
===============================================
PROD_HIER	MOD_PRODUCT_NUMBER
PH08968	        H8889
PH08968	        P5555
PH08968	        H9955
PH08968	        P6666
PH08968	        H8888
PH08968	        H7777
PH05678	        H8889
PH05678	        P5555
PH05678	        H9955
PH05678	        P6666
PH05678	        H8888
PH05678	        H7777           
==========================================================================================================