DECLARE @period AS VARCHAR(4)='2024'
SELECT rmn.EmpIDCreate, rmn.RegID,
 CASE
  WHEN rts.TaskID = 'apv_ds' AND rts.TaskStatus = 'W' THEN 'Menunggu Approval PUK 1'
  WHEN rts.TaskID = 'apv_nha' AND rts.TaskStatus = 'W' THEN 'Menunggu Approval PUK 2'
  WHEN rts.TaskID = 'apv_ds' AND rts.TaskStatus = 'C' THEN 'Approved By PUK 1'
  WHEN rts.TaskID = 'apv_nha' AND rts.TaskStatus = 'C' THEN 'Approved By PUK 2'
  WHEN rts.TaskID = 'proc_data_PHRPA0047' AND rts.TaskStatus = 'C' THEN 'DONE HC PROCESS'
  ELSE 'TBD' 
  END AS Status,
  rbd_s.Value AS SelfAssestment,
  rbd_1.Value AS 'PUK1Nama',
  rbd_2.Value AS 'PUK2Nama',
  rbd_p.Value AS PA,
  rts.ExecuteEmployeeID AS Approval,
  rmn.FlowType AS FlowType,
  rmn.WorkflowStatus
FROM dbo.PCMWFRUNBIZDATA rbd
INNER JOIN dbo.PCMWFRUNMAIN rmn ON rbd.RegID = rmn.RegID
INNER JOIN dbo.PCMWFRUNTASK rts ON rbd.RegID = rts.RegID
INNER JOIN (SELECT RegID, MAX(Sequence) AS Sequence FROM dbo.PCMWFRUNBIZDATA WHERE BizProcessID = 'PMS_Final' GROUP BY RegID) AS rbd_t ON rbd.RegID = rbd_t.RegID
LEFT JOIN dbo.PCMWFRUNBIZDATA rbd_s ON rbd_s.RegID = rbd_t.RegID AND rbd_s.TableReference = 'PDSPMSKOMENDS' AND rbd_s.FieldID = 'SelfAssesTotal' AND rbd_s.Sequence = rbd_t.Sequence
LEFT JOIN dbo.PCMWFRUNBIZDATA rbd_1 ON rbd_1.RegID = rbd_t.RegID AND rbd_1.TableReference = 'PHRPA0048ESS' AND rbd_1.FieldID = 'AppraiserName_48' AND rbd_1.Sequence = 1 AND rbd_1.DataSequence = 1
LEFT JOIN dbo.PCMWFRUNBIZDATA rbd_2 ON rbd_2.RegID = rbd_t.RegID AND rbd_2.TableReference = 'PHRPA0048ESS' AND rbd_2.FieldID = 'AppraiserName_48' AND rbd_2.Sequence = 1 AND rbd_2.DataSequence = 2
LEFT JOIN dbo.PCMWFRUNBIZDATA rbd_p ON rbd_p.RegID = rbd_t.RegID AND rbd_p.TableReference = 'PHRPA0047ESS' AND rbd_p.FieldID = 'CalculatedScore_47' AND rbd_p.Sequence = rbd_t.Sequence
WHERE rbd.FieldID = 'StartDate_47' AND LEFT(rbd.Value,4) = @period AND rbd.BizProcessID = 'PMS_Final' AND rbd.Sequence = 1 AND rts.TaskID IN ('apv_ds', 'apv_nha', 'proc_data_PHRPA0047')
ORDER BY rts.RegID, rts.Sequence