SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas. 2017-12-28. List anak buah
CREATE FUNCTION dbo.func_list_staff
(	
	@pi_head VARCHAR(4)
)
RETURNS TABLE 
AS
RETURN 
(
		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
					dbo.m_organization AS mo INNER JOIN
					dbo.m_level_type AS mlt ON mo.level_type_id = mlt.level_type_id INNER JOIN
					dbo.m_status AS ms1 ON mo.status_id = ms1.status_id INNER JOIN
					dbo.m_status AS ms2 ON mlt.status_id = ms2.status_id
				WHERE
					mo.user_id_head = @pi_head
					AND mo.is_delete = '0'
					AND mlt.is_delete = '0'
					AND ms1.status_code = 'STAT02'	--Active (status organisasi)
					AND ms2.status_code = 'STAT02'	--Active (status level type)
				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
					dbo.m_organization AS mo INNER JOIN
					dbo.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 INNER JOIN
					dbo.m_status AS ms1 ON mo.status_id = ms1.status_id INNER JOIN
					dbo.m_status AS ms2 ON mlt.status_id = ms2.status_id
				WHERE
					mo.organization_id_parent IS NOT NULL
					AND mo.is_delete = '0'
					AND mlt.is_delete = '0'
					AND ms1.status_code = 'STAT02'	--Active (status organisasi)
					AND ms2.status_code = 'STAT02'	--Active (status level type)
			)
		SELECT TOP (100) PERCENT
			mu.[user_id],
			mu.username,
			mu.user_fullname
		FROM
			OrganizationList AS OL INNER JOIN
			dbo.m_user_organization AS muo ON OL.organization_id = muo.organization_id INNER JOIN
			dbo.m_status AS ms1 ON muo.status_id = ms1.status_id INNER JOIN
			dbo.m_user AS mu ON muo.user_id_organization = mu.username INNER JOIN
			dbo.m_status AS ms2 ON mu.status_id = ms2.status_id
		WHERE
			muo.effective_date < CAST(GETDATE() AS DATE)
			AND ms1.status_code = 'STAT02'	--Active (status user organisasi)
			AND ms2.status_code = 'STAT02'	--Active (status user)
			AND muo.is_delete = '0'
			AND mu.is_delete = 0
			AND ms1.is_delete = '0'
			AND ms2.is_delete = '0'
		ORDER BY
			mu.user_fullname
)
GO
