HINO

HINO 

BEGIN

DECLARE @now VARCHAR(10)

SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')

SELECT DISTINCT CONVERT(VARCHAR,CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(@now,1,4)))+'0201') StartDate ,

        CONVERT(VARCHAR,CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(@now,1,4))+1)+'0131') AS EndDate ,

        pa2.EmployeeID,

        CONVERT(VARCHAR(20),'1000') AS AbsenceQuotaType,

        CASE WHEN (SELECT TOP(1) CONVERT(VARCHAR(20), CONVERT(INT,[Sequence])+1) AS seq FROM dbo.PHRPA0017 WHERE EmployeeID= pa2.EmployeeID ORDER BY [Sequence] desc) 

                IS NOT NULL THEN 

                (SELECT TOP(1) CONVERT(VARCHAR(20), CONVERT(INT,[Sequence])+1) AS seq FROM dbo.PHRPA0017 WHERE EmployeeID= pa2.EmployeeID ORDER BY [Sequence] DESC) ELSE '1'

                END AS [Sequence],

        CONVERT(VARCHAR,CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(@now,1,4)))+'0201') StartDeduction,

        CONVERT(VARCHAR,CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(@now,1,4))+1)+'0331') AS EndDeduction ,

        CONVERT(varchar(10),'12') AS OriginalQuota,

        CONVERT(varchar(10),'1')  AS RemainQuota,

        CONVERT(VARCHAR(20), '') AS Notes,

        CONVERT(VARCHAR(20), 'System') AS CreateBy,

        FORMAT(getdate(),'yyyyMMddHHmmss') AS CreateDate,  

        CONVERT(VARCHAR(20), 'System') AS ChangeBy,

        FORMAT(getdate(),'yyyyMMddHHmmss') AS ChangeDate,

        pa15.HiringDate

        INTO #DATAREADY

FROM dbo.PHRPA0002  AS pa2  LEFT OUTER JOIN                       

dbo.PHRPA0015 pa15  ON pa15.EmployeeID = pa2.EmployeeID

WHERE  pa2.EmployeeStatus='01' AND pa2.EndDate = '99991231' 

--HARUS UPDATE DULU BARU INSERT

--INI UPDATE KALAU DATA DI PA17 SUDAH ADA

UPDATE dbo.PHRPA0017 SET RemainQuota= CONVERT(VARCHAR,CONVERT(INT,RemainQuota)+1), ChangeDate=FORMAT(getdate(),'yyyyMMddHHmmss') WHERE 

CONVERT(VARCHAR,SUBSTRING(ChangeDate,1,6)) < CONVERT(VARCHAR,SUBSTRING(@now,1,6))

AND CONVERT(VARCHAR,SUBSTRING(StartDeduction,1,4)) = CONVERT(VARCHAR,SUBSTRING(@now,1,4))

AND   CONVERT(VARCHAR,SUBSTRING(EndDeduction,1,4)) = CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(@now,1,4)+1))

AND CONVERT(INT,RemainQuota) < 12

--SELECT * FROM  dbo.PHRPA0017 WHERE CONVERT(VARCHAR,SUBSTRING(StartDeduction,1,6)) = CONVERT(VARCHAR,SUBSTRING(@now,1,6))

--INI INSERT KALAU DATA DI PA17 BELUM ADA 

--INSERT INTO  dbo.PHRPA0017 

SELECT DISTINCT  StartDate,EndDate,EmployeeID,AbsenceQuotaType,Sequence,StartDeduction,EndDeduction,OriginalQuota,RemainQuota,Notes,CreateBy,CreateDate,ChangeBy,ChangeDate FROM #DATAREADY 

where CONVERT(VARCHAR,SUBSTRING(HiringDate,1,6)) <= CONVERT(VARCHAR,SUBSTRING(dbo.fn_formatdatetime(DATEADD(MONTH,-14,@now), 'yyyymmdd'),1,6))

AND EmployeeID NOT IN (SELECT EmployeeID FROM  dbo.PHRPA0017 WHERE CONVERT(VARCHAR,SUBSTRING(StartDeduction,1,4)) = CONVERT(VARCHAR,SUBSTRING(@now,1,4))) 

DROP TABLE #DATAREADY

END