DECLARE
	@pi_head VARCHAR(4) = 'DSO';

--SELECT
--	*
--FROM
--	m_organization AS mo INNER JOIN
--	m_level_type AS mlt ON mo.level_type_id = mlt.level_type_id
--WHERE
--	mo.user_id_head = @pi_head

--SELECT
--	*
--FROM
--	m_user_organization
--WHERE
--	organization_id = 5

--WITH EmployeeList AS
--	(
--		--SELECT 1 AS EmpLevel FROM Employee AS Boss WHERE Boss.ManagerID IS NULL
--		SELECT
--			mo.user_id_head AS EmpID,
--			mo.organization_id,
--			mo.level_type_id,
--			1 AS EmpLevel
--		FROM
--			m_organization AS mo INNER JOIN
--			m_level_type AS mlt ON mo.level_type_id = mlt.level_type_id
--		WHERE
--			mo.user_id_head = @pi_head
--		UNION ALL
--		--SELECT EL.EmpLevel + 1 FROM Employee AS Emp INNER JOIN
--		--														EmployeeList AS EL ON Emp.ManagerID = EL.EmpID
--		--WHERE Emp.ManagerID IS NOT NULL
--		SELECT 
--			muo.user_id_organization AS EmpID,
--			muo.organization_id,
--			0 AS level_type_id,
--			0 AS EmpLevel
--		FROM 
--			m_user_organization AS muo INNER JOIN
--			EmployeeList AS EL ON muo.organization_id = EL.organization_id
--	)
--SELECT * FROM EmployeeList

WITH OrganizationList AS
	(
		SELECT
			mo.user_id_head AS EmpID,
			mo.organization_id,
			mo.description,
			mlt.head_description,
			mo.level_type_id,
			mlt.seq_number,
			mo.organization_id_parent,
			1 AS EmpLevel
		FROM
			m_organization AS mo INNER JOIN
			m_level_type AS mlt ON mo.level_type_id = mlt.level_type_id
		WHERE
			mo.user_id_head = @pi_head
		UNION ALL
		SELECT
			mo.user_id_head AS EmpID,
			mo.organization_id,
			mo.description,
			mlt.head_description,
			mo.level_type_id,
			mlt.seq_number,
			mo.organization_id_parent,
			0 AS EmpLevel
		FROM
			m_organization AS mo INNER JOIN
			m_level_type AS mlt ON mo.level_type_id = mlt.level_type_id INNER JOIN
			OrganizationList AS OL ON mo.organization_id_parent = OL.organization_id
		WHERE
			mo.organization_id_parent IS NOT NULL
			--mo.user_id_head = @pi_head
	)
SELECT * FROM OrganizationList