ALTER PROCEDURE [dbo].[IMFI_ESS_MAIL_COMPID_CCHR]
(
	@emailto VARCHAR(500)
)
AS

--DECLARE @emailto VARCHAR(500) = 'trierizprimadonna@gmail.com, trierizinspirit@gmail.com, tri@minovais.com, trierizprimadonna@gmail.com, trierizinspirit@gmail.com'
------DECLARE @emailto VARCHAR(500) = 'a@gmail.com, b@gmail.com, c@minovais.com, a@gmail.com, b@gmail.com' ----'daud@minovais.com,d4ud.sulaiman80@gmail.com'
DECLARE @now VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(),'yyyymmdd')

DECLARE @EmailEmp VARCHAR(MAX) =''
SELECT DISTINCT @EmailEmp = e.Value
FROM dbo.fn_SplitString(@emailto, ',') AS e
WHERE EXISTS (
    SELECT 1
    FROM hr_md_emp_md0012 AS md12
    LEFT JOIN dbo.hr_md_emp_md0002 AS md2 ON md2.emp_id = md12.emp_id
    WHERE comm_type = '04'
      AND md2.start_date <= @now AND md2.end_date >= @now
      AND LTRIM(RTRIM(e.Value)) = md12.comm_description
)

DECLARE @CompanyID VARCHAR(100) = ''

SELECT @CompanyID = md2.company_id
FROM hr_md_emp_md0012 AS md12
LEFT JOIN dbo.hr_md_emp_md0002 AS md2 ON md2.emp_id = md12.emp_id
WHERE comm_type = '04'
    AND md2.start_date <= @now AND md2.end_date >= @now
	AND md12.comm_description = @EmailEmp

DECLARE @EmailCC VARCHAR(MAX)
SELECT @EmailCC = par.val2
FROM base_cust_parameter AS par
WHERE par.param = 'IMFI_ESS_MAIL_COMPID_CCHR'
	AND par.val1 = @CompanyID

if(@EmailCC IS NULL OR @EmailCC = '')
BEGIN
	DECLARE @EmailTerdaftar VARCHAR(MAX) = '';
	SELECT @EmailTerdaftar = @EmailTerdaftar + 
		   CASE WHEN @EmailTerdaftar = '' THEN '' ELSE ',' END + LTRIM(RTRIM(a.Value))
	FROM dbo.fn_SplitString(@emailto, ',') AS a
	WHERE EXISTS (
		SELECT 1
		FROM base_cust_parameter AS par
		WHERE par.param = 'IMFI_ESS_MAIL_COMPID_CCHR'
		  AND LTRIM(RTRIM(a.Value)) <> par.val2
	)
	SET @EmailCC = @EmailTerdaftar
END

SELECT @EmailCC + ',' + @EmailEmp AS EmailCC