|
1
|
HINO
|
|
2
|
|
|
3
|
BEGIN
|
|
4
|
DECLARE @now VARCHAR(10)
|
|
5
|
SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')
|
|
6
|
|
|
7
|
SELECT DISTINCT CONVERT(VARCHAR,CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(@now,1,4)))+'0201') StartDate ,
|
|
8
|
CONVERT(VARCHAR,CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(@now,1,4))+1)+'0131') AS EndDate ,
|
|
9
|
pa2.EmployeeID,
|
|
10
|
CONVERT(VARCHAR(20),'1000') AS AbsenceQuotaType,
|
|
11
|
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)
|
|
12
|
IS NOT NULL THEN
|
|
13
|
(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'
|
|
14
|
END AS [Sequence],
|
|
15
|
CONVERT(VARCHAR,CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(@now,1,4)))+'0201') StartDeduction,
|
|
16
|
CONVERT(VARCHAR,CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(@now,1,4))+1)+'0331') AS EndDeduction ,
|
|
17
|
CONVERT(varchar(10),'12') AS OriginalQuota,
|
|
18
|
CONVERT(varchar(10),'1') AS RemainQuota,
|
|
19
|
CONVERT(VARCHAR(20), '') AS Notes,
|
|
20
|
CONVERT(VARCHAR(20), 'System') AS CreateBy,
|
|
21
|
FORMAT(getdate(),'yyyyMMddHHmmss') AS CreateDate,
|
|
22
|
CONVERT(VARCHAR(20), 'System') AS ChangeBy,
|
|
23
|
FORMAT(getdate(),'yyyyMMddHHmmss') AS ChangeDate,
|
|
24
|
pa15.HiringDate
|
|
25
|
INTO #DATAREADY
|
|
26
|
FROM dbo.PHRPA0002 AS pa2 LEFT OUTER JOIN
|
|
27
|
dbo.PHRPA0015 pa15 ON pa15.EmployeeID = pa2.EmployeeID
|
|
28
|
WHERE pa2.EmployeeStatus='01' AND pa2.EndDate = '99991231'
|
|
29
|
|
|
30
|
|
|
31
|
--HARUS UPDATE DULU BARU INSERT
|
|
32
|
|
|
33
|
--INI UPDATE KALAU DATA DI PA17 SUDAH ADA
|
|
34
|
UPDATE dbo.PHRPA0017 SET RemainQuota= CONVERT(VARCHAR,CONVERT(INT,RemainQuota)+1), ChangeDate=FORMAT(getdate(),'yyyyMMddHHmmss') WHERE
|
|
35
|
CONVERT(VARCHAR,SUBSTRING(ChangeDate,1,6)) < CONVERT(VARCHAR,SUBSTRING(@now,1,6))
|
|
36
|
AND CONVERT(VARCHAR,SUBSTRING(StartDeduction,1,4)) = CONVERT(VARCHAR,SUBSTRING(@now,1,4))
|
|
37
|
AND CONVERT(VARCHAR,SUBSTRING(EndDeduction,1,4)) = CONVERT(VARCHAR,CONVERT(INT,SUBSTRING(@now,1,4)+1))
|
|
38
|
AND CONVERT(INT,RemainQuota) < 12
|
|
39
|
--SELECT * FROM dbo.PHRPA0017 WHERE CONVERT(VARCHAR,SUBSTRING(StartDeduction,1,6)) = CONVERT(VARCHAR,SUBSTRING(@now,1,6))
|
|
40
|
|
|
41
|
|
|
42
|
--INI INSERT KALAU DATA DI PA17 BELUM ADA
|
|
43
|
--INSERT INTO dbo.PHRPA0017
|
|
44
|
SELECT DISTINCT StartDate,EndDate,EmployeeID,AbsenceQuotaType,Sequence,StartDeduction,EndDeduction,OriginalQuota,RemainQuota,Notes,CreateBy,CreateDate,ChangeBy,ChangeDate FROM #DATAREADY
|
|
45
|
where CONVERT(VARCHAR,SUBSTRING(HiringDate,1,6)) <= CONVERT(VARCHAR,SUBSTRING(dbo.fn_formatdatetime(DATEADD(MONTH,-14,@now), 'yyyymmdd'),1,6))
|
|
46
|
AND EmployeeID NOT IN (SELECT EmployeeID FROM dbo.PHRPA0017 WHERE CONVERT(VARCHAR,SUBSTRING(StartDeduction,1,4)) = CONVERT(VARCHAR,SUBSTRING(@now,1,4)))
|
|
47
|
|
|
48
|
|
|
49
|
DROP TABLE #DATAREADY
|
|
50
|
END
|
