DECLARE @PHRPA0018 AS TABLE
(
[NUMBER] VARCHAR(50),
RegID VARCHAR(50),
[StartDate] VARCHAR(50),
[EndDate] VARCHAR(50),
[EmployeeID] VARCHAR(50),
[AbsenceType] VARCHAR(50),
[IDOnBehalf] VARCHAR(50),
[AbsenceAddress] VARCHAR(50),
[AbsencePhone] VARCHAR(50),
[Notes] VARCHAR(50),
[CreateBy] VARCHAR(50),
[CreateDate] VARCHAR(50),
[ChangeBy] VARCHAR(50),
[ChangeDate] VARCHAR(50),
[TotalAbsenceTaken] VARCHAR(50)
)

SELECT DISTINCT RegID,MAX(Sequence)AS SEQ, FieldID,Value,BizProcessID INTO #TEMP FROM PCMWFRUNBIZDATA WHERE BizProcessID='Leave_AJTM' AND
(FieldID='DelegateTo' OR FieldID='AbsenceType' OR FieldID='StartDate'OR FieldID='EndDate'OR FieldID='TotalAbsenceTaken'OR (FieldID='LeaveConfirmation'AND Value='y'))
AND  RegID IN (SELECT RegID FROM PCMWFRUNMAIN WHERE BizflowID='MD_Leave_AJTM' AND SUBSTRING(CreateDate, 0, 5)  = '2021' )
GROUP BY RegID,FieldID,Value,BizProcessID

DECLARE @ROWSNUMBER  INT = (SELECT COUNT(RegID) FROM PCMWFRUNMAIN WHERE BizflowID='MD_Leave_AJTM' AND SUBSTRING(CreateDate, 0, 5)  = '2021')
DECLARE @i int = 1

WHILE @i <= @ROWSNUMBER 
BEGIN  
INSERT INTO @PHRPA0018 VALUES ( @i ,'','','','','','','','','','','','','','')
   SET @i = @i + 1
END

UPDATE @PHRPA0018 SET
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
WHERE A.NUMBER = [@PHRPA0018].NUMBER

UPDATE @PHRPA0018 SET
StartDate =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='StartDate')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID

UPDATE @PHRPA0018 SET
EndDate =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='EndDate')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID

UPDATE @PHRPA0018 SET
EmployeeID =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='DelegateTo')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID

UPDATE @PHRPA0018 SET
AbsenceType =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='AbsenceType')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID

UPDATE @PHRPA0018 SET
TotalAbsenceTaken =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='TotalAbsenceTaken')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID

INSERT INTO PHRPA0018
SELECT 
[StartDate]
,[EndDate]
,[EmployeeID]
,[AbsenceType]
,[IDOnBehalf]
,[AbsenceAddress]
,[AbsencePhone]
,[Notes]
,[CreateBy]
,[CreateDate]
,[ChangeBy]
,[ChangeDate]
,[TotalAbsenceTaken]
 FROM @PHRPA0018
DROP TABLE #TEMP