|
1
|
DECLARE @period AS VARCHAR(4)='2024'
|
|
2
|
SELECT rmn.EmpIDCreate, rmn.RegID,
|
|
3
|
CASE
|
|
4
|
WHEN rts.TaskID = 'apv_ds' AND rts.TaskStatus = 'W' THEN 'Menunggu Approval PUK 1'
|
|
5
|
WHEN rts.TaskID = 'apv_nha' AND rts.TaskStatus = 'W' THEN 'Menunggu Approval PUK 2'
|
|
6
|
WHEN rts.TaskID = 'apv_ds' AND rts.TaskStatus = 'C' THEN 'Approved By PUK 1'
|
|
7
|
WHEN rts.TaskID = 'apv_nha' AND rts.TaskStatus = 'C' THEN 'Approved By PUK 2'
|
|
8
|
WHEN rts.TaskID = 'proc_data_PHRPA0047' AND rts.TaskStatus = 'C' THEN 'DONE HC PROCESS'
|
|
9
|
ELSE 'TBD'
|
|
10
|
END AS Status,
|
|
11
|
rbd_s.Value AS SelfAssestment,
|
|
12
|
rbd_1.Value AS 'PUK1Nama',
|
|
13
|
rbd_2.Value AS 'PUK2Nama',
|
|
14
|
rbd_p.Value AS PA,
|
|
15
|
rts.ExecuteEmployeeID AS Approval,
|
|
16
|
rmn.FlowType AS FlowType,
|
|
17
|
rmn.WorkflowStatus
|
|
18
|
FROM dbo.PCMWFRUNBIZDATA rbd
|
|
19
|
INNER JOIN dbo.PCMWFRUNMAIN rmn ON rbd.RegID = rmn.RegID
|
|
20
|
INNER JOIN dbo.PCMWFRUNTASK rts ON rbd.RegID = rts.RegID
|
|
21
|
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
|
|
22
|
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
|
|
23
|
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
|
|
24
|
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
|
|
25
|
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
|
|
26
|
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')
|
|
27
|
ORDER BY rts.RegID, rts.Sequence
|
