CREATE or Replace procedure COMPANY_STRUCTURE_SP as

CURSOR Get_Roots is

select COMPANY_KEY ROOT_KEY, decode(PARENT_KEY, NULL,'Y','N') HIGHEST_FLAG,
 COMPANY_NAME ROOT_COMPANY
from COMPANY;

BEGIN

For Roots in Get_Roots 
LOOP
	insert into COMPANY_STRUCTURE 
	(PARENT_KEY,
 	 SUBSIDIARY_KEY,
 	 SUBSIDIARY_LEVEL,
 	 SEQUENCE_NUMBER,
 	 LOWEST_FLAG,
 	 HIGHEST_FLAG,
	 PARENT_COMPANY,
 	 SUBSIDIARY_COMPANY)
	select
	  roots.ROOT_KEY,
	  COMPANY_KEY,
	  LEVEL - 1,
	  ROWNUM,
	  'N',
	  roots.HIGHEST_FLAG,
	  roots.ROOT_COMPANY,
	  COMPANY_NAME
	from
	  COMPANY
	 Start with COMPANY_KEY = roots.ROOT_KEY
	 connect by prior COMPANY_KEY = PARENT_KEY;
END LOOP;

update COMPANY_STRUCTURE 
   SET LOWEST_FLAG = 'Y'
where not exists (select * from COMPANY
where PARENT_KEY = COMPANY_STRUCTURE.SUBSIDIARY_KEY);

COMMIT;
END;  /* of procedure */