Bug #1804 » GENEMPID_202211161740.sql
| 1 |
ALTER PROCEDURE GENEMPID |
|---|---|
| 2 |
(
|
| 3 |
@HiringDate VARCHAR(20) |
| 4 |
,@PayrollGroup VARCHAR(20) |
| 5 |
)
|
| 6 |
AS
|
| 7 |
|
| 8 |
--DECLARE @HiringDate VARCHAR(20) = '20220910'
|
| 9 |
--DECLARE @PayrollGroup VARCHAR(20) = '1000'
|
| 10 |
|
| 11 |
If(@PayrollGroup = '4000') |
| 12 |
BEGIN
|
| 13 |
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 |
| 14 |
FROM PHRPA0015 AS pa15 |
| 15 |
LEFT JOIN PHRPA0002 AS pa2 |
| 16 |
ON pa15.EmployeeID = pa2.EmployeeID |
| 17 |
where pa2.PayrollGroup = '4000' |
| 18 |
END
|
| 19 |
ELSE
|
| 20 |
BEGIN
|
| 21 |
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 |
| 22 |
FROM PHRPA0015 AS pa15 |
| 23 |
LEFT JOIN PHRPA0002 AS pa2 |
| 24 |
ON pa15.EmployeeID = pa2.EmployeeID |
| 25 |
where pa2.PayrollGroup <> '4000' |
| 26 |
END
|