Project

General

Profile

Feature #3646 » CRPTBKTWUL_202506111444.sql

Tri Rizqiaty, 06/11/2025 02:45 PM

 
1
??ALTER PROCEDURE [dbo].[CRPTBKTWUL]
2

3
(
4

5
	@Period VARCHAR(20)
6

7
	,@Organization VARCHAR(20)
8

9
)
10

11
AS
12

13

14

15
--DECLARE @Period VARCHAR(20) = '2025'
16

17
--DECLARE @Organization VARCHAR(20) = '00000002'
18

19

20

21
DECLARE @now VARCHAR(20) = FORMAT(getdate(), 'yyyyMMdd')
22

23
DECLARE @PeiodeEnDt VARCHAR(20) = @Period + '1231'
24

25

26

27
IF(@Organization = '')
28

29
BEGIN SET @Organization = '00000001' END
30

31

32

33
DECLARE @t_org TABLE(
34

35
	org_id VARCHAR(20)
36

37
	,org_name VARCHAR(500)
38

39
	,div_id VARCHAR(20)
40

41
	,div_name VARCHAR(500)
42

43
)
44

45
INSERT INTO @t_org
46

47
select org_id, org_name, org_id_04, org_name_04 from dbo.GetOrgInOrgWide(@Organization, @now)
48

49

50

51
DECLARE @t_emp TABLE
52

53
(
54

55
	EmployeeID VARCHAR(20)
56

57
	,EmployeeName VARCHAR(500)
58

59
	,div_id VARCHAR(20)
60

61
	,div_name VARCHAR(500)
62

63
	,Organization VARCHAR(20)
64

65
	,OrgDesc VARCHAR(500)
66

67
	,Position VARCHAR(20)
68

69
	,PosDesc VARCHAR(500)
70

71
	,Job VARCHAR(20)
72

73
	,JobDesc VARCHAR(500)
74

75
)
76

77

78

79
INSERT INTO @t_emp
80

81
SELECT pa2.EmployeeID, pa1.FullName, torg.div_id, torg.div_name, pa2.Organization, torg.org_name, pa2.Position, pos.ObjectDescription, pa2.Job, job.ObjectDescription
82

83
FROM PHRPA0002 as pa2
84

85
INNER JOIN @t_org AS torg
86

87
	ON pa2.Organization = torg.org_id
88

89
LEFT JOIN PHRPA0001 AS pa1
90

91
	ON pa2.EmployeeID = pa1.EmployeeID
92

93
	AND pa1.StartDate <= @now AND pa1.EndDate >= @now
94

95
LEFT JOIN PHROM0001 AS pos
96

97
	ON pa2.Position = pos.ObjectID
98

99
	AND pos.ObjectClass = 'P'
100

101
	AND pos.StartDate <= @now AND pos.EndDate >= @now
102

103
LEFT JOIN PHROM0001 AS job
104

105
	ON pa2.Job = job.ObjectID
106

107
	AND job.ObjectClass = 'J'
108

109
	AND job.StartDate <= @now AND job.EndDate >= @now
110

111
WHERE pa2.StartDate <= @now AND pa2.EndDate >= @now 
112

113

114

115
DECLARE @t_wf TABLE
116

117
(
118

119
	RegID VARCHAR(20)
120

121
	,BizFlowID VARCHAR(250)
122

123
	,TaskRunID VARCHAR(250)
124

125
	,EmployeeID VARCHAR(20)
126

127
	,WorkFlowStatus VARCHAR(20)
128

129
)
130

131
INSERT INTO @t_wf
132

133
SELECT RegID, BizFlowID, TaskIDRun, ExecuteEmployeeID, WorkFlowStatus
134

135
FROM PCMWFRUNTASK as runtask
136

137
INNER JOIN @t_emp as temp
138

139
	ON runtask.ExecuteEmployeeID = temp.EmployeeID
140

141
WHERE (TaskIDRun = 'create') 
142

143
	AND runtask.BizFlowID IN ('MD_PMS_Final','MD_PMS_Goal','MD_PMS_Review')
144

145
	AND LEFT(InboundDate,4) = @Period
146

147
order by EmployeeID, RegID, Sequence ASC
148

149

150

151
DECLARE @t_wf_detail TABLE
152

153
(
154

155
	RegID VARCHAR(20)
156

157
	,BizFlowID VARCHAR(250)
158

159
	,TaskRunID VARCHAR(250)
160

161
	,TaskStatus VARCHAR(20)
162

163
	,ExecuteEmployeeID VARCHAR(20)
164

165
	,WorkFlowStatus VARCHAR(20)
166

167
	,EmployeeID VARCHAR(20)
168

169
)
170

171

172

173
INSERT INTO @t_wf_detail
174

175
SELECT runtask.RegID, runtask.BizFlowID, runtask.TaskIDRun, runtask.TaskStatus, runtask.ExecuteEmployeeID, runtask.WorkFlowStatus, twf.EmployeeID
176

177
FROM PCMWFRUNTASK as runtask
178

179
INNER JOIN @t_wf as twf
180

181
	ON runtask.RegID = twf.RegID
182

183
where (runtask.TaskIDRun IN ('create ','apv_ds', 'apv_nha') OR runtask.WorkFlowStatus = 'F')
184

185
	AND runtask.BizFlowID IN ('MD_PMS_Final','MD_PMS_Goal','MD_PMS_Review')
186

187
order by RegID ASC
188

189

190

191
DECLARE @t_emp_detail TABLE
192

193
(
194

195
	EmployeeID VARCHAR(20)
196

197
	,EmployeeName VARCHAR(500)
198

199
	,div_id VARCHAR(20)
200

201
	,div_name VARCHAR(500)
202

203
	,Organization VARCHAR(20)
204

205
	,OrgDesc VARCHAR(500)
206

207
	,Position VARCHAR(20)
208

209
	,PosDesc VARCHAR(500)
210

211
	,Job VARCHAR(20)
212

213
	,JobDesc VARCHAR(500)
214

215
	,TotCreate DECIMAL(22,0)
216

217
	,TotFinish DECIMAL(22,0)
218

219
	,EmpWFStatus VARCHAR(20)
220

221
	,TotApvDs DECIMAL(22,0)
222

223
	,TotApvNha DECIMAL(22,0)
224

225
)
226

227
INSERT INTO @t_emp_detail
228

229
SELECT temp.*, tcreate.TotCreate, tfinish.TotFinish, 
230

231
		CASE WHEN tcreate.TotCreate = tfinish.TotFinish THEN 'F' ELSE 'N' END AS EmpWFStatus, ISNULL(TotApvDs,0) AS TotApvDs, ISNULL(TotApvNha,0) AS TotApvNha
232

233
FROM @t_emp AS temp
234

235
LEFT JOIN ( SELECT EmployeeID, count(RegID) AS TotCreate FROM @t_wf WHERE TaskRunID = 'create' group by EmployeeID ) AS tcreate
236

237
	ON temp.EmployeeID = tcreate.EmployeeID
238

239
LEFT JOIN ( SELECT EmployeeID, count(RegID) AS TotFinish FROM @t_wf_detail WHERE WorkFlowStatus = 'F' group by EmployeeID ) AS tfinish
240

241
	ON temp.EmployeeID = tfinish.EmployeeID
242

243
LEFT JOIN ( SELECT EmployeeID, count(RegID) AS TotApvDs FROM @t_wf_detail WHERE TaskRunID = 'apv_ds' group by EmployeeID ) AS tapv_ds
244

245
	ON temp.EmployeeID = tapv_ds.EmployeeID
246

247
LEFT JOIN ( SELECT EmployeeID, count(RegID) AS TotApvNha FROM @t_wf_detail WHERE TaskRunID = 'apv_nha' group by EmployeeID ) AS tapv_nha
248

249
	ON temp.EmployeeID = tapv_nha.EmployeeID
250

251
WHERE div_id <> ''
252

253

254

255

256

257
SELECT tdiv.*, ISNULL((CONVERT(DECIMAL(22,2),(TotalEmpFinish/TotalEmp)) * 100),0) AS Persentase, 
258

259
		ISNULL(tfinish.TotalEmpFinish,0) AS TotalEmpFinish, ISNULL(tcreate.TotCreate,0) AS TotCreate, ISNULL(tapvds.TotApvDs,0) AS TotApvDs, ISNULL(tapvnha.TotApvNha,0) AS TotApvNha
260

261
FROM (SELECT div_id, div_name, CONVERT(DECIMAL(22,2),COUNT(EmployeeID)) AS TotalEmp FROM @t_emp_detail GROUP BY div_id, div_name) AS tdiv
262

263
LEFT JOIN (SELECT div_id, div_name, ISNULL(COUNT(EmployeeID),0) AS TotalEmpFinish FROM @t_emp_detail WHERE EmpWFStatus = 'F' GROUP BY div_id, div_name) AS tfinish
264

265
	ON tdiv.div_id = tfinish.div_id
266

267
LEFT JOIN (SELECT div_id, div_name, ISNULL(COUNT(EmployeeID),0) AS TotCreate FROM @t_emp_detail WHERE TotCreate > 0 GROUP BY div_id, div_name) AS tcreate
268

269
	ON tdiv.div_id = tcreate.div_id
270

271
LEFT JOIN (SELECT div_id, div_name, ISNULL(COUNT(EmployeeID),0) AS TotApvDs FROM @t_emp_detail WHERE TotApvDs > 0 GROUP BY div_id, div_name) AS tapvds
272

273
	ON tdiv.div_id = tapvds.div_id
274

275
LEFT JOIN (SELECT div_id, div_name, ISNULL(COUNT(EmployeeID),0) AS TotApvNha FROM @t_emp_detail WHERE TotApvNha > 0 GROUP BY div_id, div_name) AS tapvnha
276

277
	ON tdiv.div_id = tapvnha.div_id
278

279

280

281
--SELECT div_id, div_name, COUNT(EmployeeID) AS TotalEmp FROM @t_emp_detail GROUP BY div_id, div_name
282

283
--SELECT div_id, div_name, COUNT(EmployeeID) AS TotalEmpFinish FROM @t_emp_detail WHERE EmpWFStatus = 'F' GROUP BY div_id, div_name
284

285

286

287
--SELECT * FROM PCMWFRUNTASK 
288

289
--WHERE LEFT(InboundDate,4) = @Period AND BizFlowID = 'MD_Leave_SJ' and RegID = '850704'
290

291
----where TaskIDRun IN ('create ','apv_ds', 'apv_nha', 'Complate')
292

293
--order by RegID desc
294

295

296

297
--SELECT DISTINCT RegID, BizFlowID, EmployeeID,
298

299
--   [create], [apv_ds], [apv_nha]
300

301
--FROM (
302

303
--	SELECT runtask.RegID, runtask.BizFlowID, runtask.TaskIDRun, runtask.WorkFlowStatus, runtask.TaskStatus, twf.EmployeeID
304

305
--	FROM PCMWFRUNTASK as runtask
306

307
--	INNER JOIN @t_wf as twf
308

309
--		ON runtask.RegID = twf.RegID
310

311
--	where (runtask.TaskIDRun IN ('create ','apv_ds', 'apv_nha') OR runtask.WorkFlowStatus = 'F')
312

313
--) AS SourceTable
314

315
--PIVOT (
316

317
--    MIN(TaskStatus) FOR TaskIDRun IN
318

319
--    ([create], [apv_ds], [apv_nha], [notif_mail_success])
320

321
--) AS PivotTable;
(6-6/9)