ALTER PROCEDURE GENEMPID
(
	@HiringDate VARCHAR(20)
	,@PayrollGroup VARCHAR(20)
)
AS

--DECLARE @HiringDate VARCHAR(20) = '20220910'
--DECLARE @PayrollGroup VARCHAR(20) = '1000'

If(@PayrollGroup = '4000')
BEGIN
	SELECT DISTINCT CASE WHEN MAX(RIGHT(pa15.EmployeeID,4)) <> '' THEN @PayrollGroup + SUBSTRING(@HiringDate,3,4) + RIGHT('0000' + CONVERT(VARCHAR(20),MAX(RIGHT(pa15.EmployeeID,4)) + 1),4) ELSE @PayrollGroup + SUBSTRING(@HiringDate,3,4) + '0001' END AS EmpID  
	FROM PHRPA0015 AS pa15 
	LEFT JOIN PHRPA0002 AS pa2
		ON pa15.EmployeeID = pa2.EmployeeID
	where pa2.PayrollGroup = '4000'
END
ELSE
BEGIN
	SELECT DISTINCT CASE WHEN MAX(RIGHT(pa15.EmployeeID,4)) <> '' THEN SUBSTRING(@HiringDate,3,4) + RIGHT('0000' + CONVERT(VARCHAR(20),MAX(RIGHT(pa15.EmployeeID,4)) + 1),4) ELSE SUBSTRING(@HiringDate,3,4) + '0001' END AS EmpID  
	FROM PHRPA0015 AS pa15 
	LEFT JOIN PHRPA0002 AS pa2
		ON pa15.EmployeeID = pa2.EmployeeID
	where pa2.PayrollGroup <> '4000'
END