/**
 * Example of a procedure returning a recordset
 * @Original Author: faser
 * @Created: 29/11/2005
*/

CREATE OR REPLACE PACKAGE PKG_EMPLOYEES AS
 	TYPE CUSTOM_REF_CURSOR IS ref cursor;
 	PROCEDURE get_hierarchy(
 		arg_manager_id in employees.manager_id%type := NULL,
 		arg_cur in out CUSTOM_REF_CURSOR
	);
END;

CREATE OR REPLACE PACKAGE BODY PKG_EMPLOYEES as
 PROCEDURE get_hierarchy(arg_manager_id in employees.manager_id%type := NULL, arg_cur in out CUSTOM_REF_CURSOR)
 IS
  BEGIN

  OPEN arg_cur FOR
	SELECT employee_id, manager_id,
	LPAD(' ', (LEVEL - 1) * 3) || last_name || ' ' || first_name AS Employee, -- Padding to better show the hierarchy 
	LEVEL, -- Pseudo Oracle column indicate the nested level
	CONNECT_BY_ROOT last_name  his_boss, -- Indicate who is the GranFather
	DECODE(CONNECT_BY_ISLEAF, 1, 'No', 'Yes') has_kids, -- Indicate if there are children for the node
	SYS_CONNECT_BY_PATH (last_name, ' ->') hierarchy_path -- Indicate the node path
	
	FROM employees

	CONNECT BY PRIOR employee_id = manager_id -- Indicate the field in relationship
	START WITH manager_id = arg_manager_id -- Indicate from wich level to start 
		
	ORDER SIBLINGS BY manager_id; -- Mantain the natural order 
  END;
END;