BANK KALTENG

??USE [MinovaES_BankKalteng_Dev]

GO

/****** Object:  StoredProcedure [dbo].[CDSWIZEXTID]    Script Date: 13/04/2022 11.59.43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[CDSWIZEXTID]

(

	@EmployeeID VARCHAR(20)

	,@HiringDate VARCHAR(20)

	,@BirthDate VARCHAR(20)

)

AS

--DECLARE @EmployeeID VARCHAR(20) = '10000186'

--DECLARE @HiringDate VARCHAR(20) = '20030101'

--DECLARE @BirthDate VARCHAR(20) = '19910208'

DECLARE @TahunLahir VARCHAR(20)

SELECT DISTINCT @TahunLahir = LEFT(PA1.BirthDate,4)

FROM dbo.PHRPA0001 AS PA1 

WHERE PA1.EmployeeID = @EmployeeID

	AND PA1.StartDate <= @HiringDate AND PA1.EndDate >= @HiringDate

IF(@TahunLahir  <> '') BEGIN SET @TahunLahir = @TahunLahir END ELSE SET @TahunLahir = LEFT(@BirthDate,4)

DECLARE @LastSeq VARCHAR(20)

SELECT DISTINCT @LastSeq = CONVERT(char, RIGHT('0000' + CONVERT(VARCHAR(4),extid.LastExtID + 1),4))

FROM CEXTERNALID AS extid

WHERE extid.[Year] = LEFT(@HiringDate,4)

IF( @LastSeq <> '')

BEGIN

	UPDATE dbo.CEXTERNALID

	SET LastExtID = @LastSeq

	WHERE [Year] = LEFT(@HiringDate,4)

END

ELSE

BEGIN

	SET @LastSeq = '0001'

	INSERT INTO CEXTERNALID

	SELECT LEFT(@HiringDate,4), @LastSeq, ''

END

--SELECT @TahunLahir + LEFT(@HiringDate,4) + @LastSeq AS ExternalID			////---- comment by Tri 20220413

SELECT @TahunLahir + SUBSTRING(@HiringDate,2,3) + @LastSeq AS ExternalID