|
1
|
DECLARE @PHRPA0018 AS TABLE
|
|
2
|
(
|
|
3
|
[NUMBER] VARCHAR(50),
|
|
4
|
RegID VARCHAR(50),
|
|
5
|
[StartDate] VARCHAR(50),
|
|
6
|
[EndDate] VARCHAR(50),
|
|
7
|
[EmployeeID] VARCHAR(50),
|
|
8
|
[AbsenceType] VARCHAR(50),
|
|
9
|
[IDOnBehalf] VARCHAR(50),
|
|
10
|
[AbsenceAddress] VARCHAR(50),
|
|
11
|
[AbsencePhone] VARCHAR(50),
|
|
12
|
[Notes] VARCHAR(50),
|
|
13
|
[CreateBy] VARCHAR(50),
|
|
14
|
[CreateDate] VARCHAR(50),
|
|
15
|
[ChangeBy] VARCHAR(50),
|
|
16
|
[ChangeDate] VARCHAR(50),
|
|
17
|
[TotalAbsenceTaken] VARCHAR(50)
|
|
18
|
)
|
|
19
|
|
|
20
|
SELECT DISTINCT RegID,MAX(Sequence)AS SEQ, FieldID,Value,BizProcessID INTO #TEMP FROM PCMWFRUNBIZDATA WHERE BizProcessID='Leave_AJTM' AND
|
|
21
|
(FieldID='DelegateTo' OR FieldID='AbsenceType' OR FieldID='StartDate'OR FieldID='EndDate'OR FieldID='TotalAbsenceTaken'OR (FieldID='LeaveConfirmation'AND Value='y'))
|
|
22
|
AND RegID IN (SELECT RegID FROM PCMWFRUNMAIN WHERE BizflowID='MD_Leave_AJTM' AND SUBSTRING(CreateDate, 0, 5) = '2021' )
|
|
23
|
GROUP BY RegID,FieldID,Value,BizProcessID
|
|
24
|
|
|
25
|
DECLARE @ROWSNUMBER INT = (SELECT COUNT(RegID) FROM PCMWFRUNMAIN WHERE BizflowID='MD_Leave_AJTM' AND SUBSTRING(CreateDate, 0, 5) = '2021')
|
|
26
|
DECLARE @i int = 1
|
|
27
|
|
|
28
|
WHILE @i <= @ROWSNUMBER
|
|
29
|
BEGIN
|
|
30
|
INSERT INTO @PHRPA0018 VALUES ( @i ,'','','','','','','','','','','','','','')
|
|
31
|
SET @i = @i + 1
|
|
32
|
END
|
|
33
|
|
|
34
|
UPDATE @PHRPA0018 SET
|
|
35
|
RegID =A.RegID FROM (SELECT ROW_NUMBER() OVER(ORDER BY RegID ASC)AS NUMBER,RegID FROM PCMWFRUNMAIN WHERE BizflowID='MD_Leave_AJTM' AND SUBSTRING(CreateDate, 0, 5) = '2021' GROUP BY RegID)A
|
|
36
|
WHERE A.NUMBER = [@PHRPA0018].NUMBER
|
|
37
|
|
|
38
|
UPDATE @PHRPA0018 SET
|
|
39
|
StartDate =A.Value FROM (
|
|
40
|
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
|
|
41
|
(FieldID='StartDate')
|
|
42
|
GROUP BY RegID,FieldID,Value,BizProcessID
|
|
43
|
)A
|
|
44
|
WHERE A.RegID = [@PHRPA0018].RegID
|
|
45
|
|
|
46
|
UPDATE @PHRPA0018 SET
|
|
47
|
EndDate =A.Value FROM (
|
|
48
|
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
|
|
49
|
(FieldID='EndDate')
|
|
50
|
GROUP BY RegID,FieldID,Value,BizProcessID
|
|
51
|
)A
|
|
52
|
WHERE A.RegID = [@PHRPA0018].RegID
|
|
53
|
|
|
54
|
UPDATE @PHRPA0018 SET
|
|
55
|
EmployeeID =A.Value FROM (
|
|
56
|
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
|
|
57
|
(FieldID='DelegateTo')
|
|
58
|
GROUP BY RegID,FieldID,Value,BizProcessID
|
|
59
|
)A
|
|
60
|
WHERE A.RegID = [@PHRPA0018].RegID
|
|
61
|
|
|
62
|
UPDATE @PHRPA0018 SET
|
|
63
|
AbsenceType =A.Value FROM (
|
|
64
|
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
|
|
65
|
(FieldID='AbsenceType')
|
|
66
|
GROUP BY RegID,FieldID,Value,BizProcessID
|
|
67
|
)A
|
|
68
|
WHERE A.RegID = [@PHRPA0018].RegID
|
|
69
|
|
|
70
|
UPDATE @PHRPA0018 SET
|
|
71
|
TotalAbsenceTaken =A.Value FROM (
|
|
72
|
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
|
|
73
|
(FieldID='TotalAbsenceTaken')
|
|
74
|
GROUP BY RegID,FieldID,Value,BizProcessID
|
|
75
|
)A
|
|
76
|
WHERE A.RegID = [@PHRPA0018].RegID
|
|
77
|
|
|
78
|
INSERT INTO PHRPA0018
|
|
79
|
SELECT
|
|
80
|
[StartDate]
|
|
81
|
,[EndDate]
|
|
82
|
,[EmployeeID]
|
|
83
|
,[AbsenceType]
|
|
84
|
,[IDOnBehalf]
|
|
85
|
,[AbsenceAddress]
|
|
86
|
,[AbsencePhone]
|
|
87
|
,[Notes]
|
|
88
|
,[CreateBy]
|
|
89
|
,[CreateDate]
|
|
90
|
,[ChangeBy]
|
|
91
|
,[ChangeDate]
|
|
92
|
,[TotalAbsenceTaken]
|
|
93
|
FROM @PHRPA0018
|
|
94
|
DROP TABLE #TEMP
|
|
95
|
|
|
96
|
|
|
97
|
|
|
98
|
|