|
1
|
ALTER PROCEDURE PMSGETDEFAULTKPI
|
|
2
|
(
|
|
3
|
@EmployeeID VARCHAR(20)
|
|
4
|
,@StartDate VARCHAR(20)
|
|
5
|
,@EndDate VARCHAR(20)
|
|
6
|
,@AppraisalType VARCHAR(20)
|
|
7
|
)
|
|
8
|
AS
|
|
9
|
|
|
10
|
--DECLARE @EmployeeID VARCHAR(20) = '00000074'
|
|
11
|
--DECLARE @StartDate VARCHAR(20) = '20220101'
|
|
12
|
--DECLARE @EndDate VARCHAR(20) = '20221231'
|
|
13
|
--DECLARE @AppraisalType VARCHAR(20) = 'F'
|
|
14
|
|
|
15
|
DECLARE @Job VARCHAR(20)
|
|
16
|
SELECT DISTINCT @Job = Job FROM PHRPA0002 WHERE EmployeeID = @EmployeeID AND StartDate <= @EndDate AND EndDate >= @EndDate
|
|
17
|
|
|
18
|
DECLARE @AppraisalID VARCHAR(20)
|
|
19
|
SELECT DISTINCT @AppraisalID = AppraisalID
|
|
20
|
FROM PHROM0018 AS om18
|
|
21
|
WHERE om18.ObjectID = @Job
|
|
22
|
AND om18.StartDate <= @EndDate AND om18.EndDate >= @EndDate
|
|
23
|
|
|
24
|
IF(@AppraisalID <> '')
|
|
25
|
BEGIN
|
|
26
|
SELECT DISTINCT @EmployeeID AS [EmployeeID]
|
|
27
|
,@AppraisalType AS [AppraisalType]
|
|
28
|
,'' AS [HeaderSequence]
|
|
29
|
,om7.Sequence AS [Sequence]
|
|
30
|
,'0' AS [SequenceSubKPI]
|
|
31
|
,om7.KPI
|
|
32
|
,om2.RelationshipObject AS KPIGroup
|
|
33
|
,'0' AS [IsSubKPI]
|
|
34
|
,'-' AS [SubKPI]
|
|
35
|
,om1.ObjectDescription AS [SubKPIDescription]
|
|
36
|
,'' AS [IsCalculate]
|
|
37
|
,om7.Weight AS [Weight]
|
|
38
|
,'' AS [Target]
|
|
39
|
,'' AS [Unit]
|
|
40
|
,'' AS [SelfValue]
|
|
41
|
,'' AS [App1Value]
|
|
42
|
,'' AS [App1Score]
|
|
43
|
,'' AS [App2Value]
|
|
44
|
,'' AS [App2Score]
|
|
45
|
,'' AS [App3Value]
|
|
46
|
,'' AS [App3Score]
|
|
47
|
,'' AS [App4Value]
|
|
48
|
,'' AS [App4Score]
|
|
49
|
,'' AS [App5Value]
|
|
50
|
,'' AS [App5Score]
|
|
51
|
,'' AS [AppraiserScore]
|
|
52
|
,'' AS [WeightedScore]
|
|
53
|
,'' AS [Notes]
|
|
54
|
,'' AS [ObjectDescription]
|
|
55
|
,'' AS [ScoreC]
|
|
56
|
,'' AS [KPAchievementDesc]
|
|
57
|
,'' AS [NotesNHA]
|
|
58
|
FROM PHROM0007 AS om7
|
|
59
|
LEFT JOIN dbo.PHROM0002 AS om2
|
|
60
|
ON om2.ObjectID = om7.KPI
|
|
61
|
AND om2.ObjectClass = 'K' AND om2.RelationshipClass = 'KG'
|
|
62
|
AND om2.StartDate <= @EndDate AND om2.EndDate >= @EndDate
|
|
63
|
LEFT JOIN dbo.PHROM0001 AS om1
|
|
64
|
ON om1.ObjectID = om7.KPI
|
|
65
|
AND om1.ObjectClass = 'K'
|
|
66
|
AND om1.StartDate <= @EndDate AND om1.EndDate >= @EndDate
|
|
67
|
WHERE om7.ObjectID = @AppraisalID
|
|
68
|
AND om7.ObjectClass = 'AP'
|
|
69
|
AND om7.StartDate <= @EndDate AND om7.EndDate >= @EndDate
|
|
70
|
END
|
|
71
|
ELSE
|
|
72
|
BEGIN
|
|
73
|
SELECT DISTINCT @EmployeeID AS [EmployeeID]
|
|
74
|
,@AppraisalType AS [AppraisalType]
|
|
75
|
,'' AS [HeaderSequence]
|
|
76
|
,om7.Sequence AS [Sequence]
|
|
77
|
,'0' AS [SequenceSubKPI]
|
|
78
|
,om7.KPI
|
|
79
|
,om2.RelationshipObject AS KPIGroup
|
|
80
|
,'0' AS [IsSubKPI]
|
|
81
|
,'-' AS [SubKPI]
|
|
82
|
,om1.ObjectDescription AS [SubKPIDescription]
|
|
83
|
,'' AS [IsCalculate]
|
|
84
|
,om7.Weight AS [Weight]
|
|
85
|
,'' AS [Target]
|
|
86
|
,'' AS [Unit]
|
|
87
|
,'' AS [SelfValue]
|
|
88
|
,'' AS [App1Value]
|
|
89
|
,'' AS [App1Score]
|
|
90
|
,'' AS [App2Value]
|
|
91
|
,'' AS [App2Score]
|
|
92
|
,'' AS [App3Value]
|
|
93
|
,'' AS [App3Score]
|
|
94
|
,'' AS [App4Value]
|
|
95
|
,'' AS [App4Score]
|
|
96
|
,'' AS [App5Value]
|
|
97
|
,'' AS [App5Score]
|
|
98
|
,'' AS [AppraiserScore]
|
|
99
|
,'' AS [WeightedScore]
|
|
100
|
,'' AS [Notes]
|
|
101
|
,'' AS [ObjectDescription]
|
|
102
|
,'' AS [ScoreC]
|
|
103
|
,'' AS [KPAchievementDesc]
|
|
104
|
,'' AS [NotesNHA]
|
|
105
|
FROM PHROM0007 AS om7
|
|
106
|
LEFT JOIN dbo.PHROM0002 AS om2
|
|
107
|
ON om2.ObjectID = om7.KPI
|
|
108
|
AND om2.ObjectClass = 'K' AND om2.RelationshipClass = 'KG'
|
|
109
|
AND om2.StartDate <= @EndDate AND om2.EndDate >= @EndDate
|
|
110
|
LEFT JOIN dbo.PHROM0001 AS om1
|
|
111
|
ON om1.ObjectID = om7.KPI
|
|
112
|
AND om1.ObjectClass = 'K'
|
|
113
|
AND om1.StartDate <= @EndDate AND om1.EndDate >= @EndDate
|
|
114
|
WHERE om7.ObjectID = @Job
|
|
115
|
AND om7.ObjectClass = 'J'
|
|
116
|
AND om7.StartDate <= @EndDate AND om7.EndDate >= @EndDate
|
|
117
|
END
|
