|
1
|
ALTER PROCEDURE [dbo].[Rpt_Mega_WTRecap_Summary_New]
|
|
2
|
(
|
|
3
|
@landscape VARCHAR(10)
|
|
4
|
,@payperiod VARCHAR(10)
|
|
5
|
,@companycode VARCHAR(10)
|
|
6
|
,@paygroup VARCHAR(10)
|
|
7
|
,@emp_id VARCHAR(10)
|
|
8
|
)
|
|
9
|
AS
|
|
10
|
|
|
11
|
|
|
12
|
--DECLARE @landscape VARCHAR(10) = '100'
|
|
13
|
--DECLARE @companycode VARCHAR(10) = ''
|
|
14
|
--DECLARE @paygroup VARCHAR(10) = '01'
|
|
15
|
--DECLARE @payperiod VARCHAR(10) = '202405'
|
|
16
|
--DECLARE @emp_id VARCHAR(10) = ''
|
|
17
|
|
|
18
|
|
|
19
|
--//-- Variable Declaration
|
|
20
|
DECLARE @payPeriodMonth varchar(2)
|
|
21
|
DECLARE @payPeriodYear varchar(4)
|
|
22
|
DECLARE @payPeriodStartDate varchar(8)
|
|
23
|
DECLARE @payPeriodEndDate varchar(8)
|
|
24
|
DECLARE @payPeriodEndDateDt datetime
|
|
25
|
|
|
26
|
--//-- Table Declaration
|
|
27
|
DECLARE @tbl_result TABLE
|
|
28
|
(employee_id varchar(8),
|
|
29
|
employee_name varchar(50),
|
|
30
|
CostCenterGroup varchar(50),
|
|
31
|
CostCenterGroup_Abr varchar(50),
|
|
32
|
CostCenterGroup_Desc varchar(50),
|
|
33
|
CostCenter varchar(50),
|
|
34
|
CostCenter_Abr varchar(50),
|
|
35
|
CostCenter_Desc varchar(50),
|
|
36
|
WT0101 int,WT0102 int,WT0103 int,WT0104 int,WT0105 int,WT0106 int,WT0107 int,WT0108 int,WT0109 int,WT0110 int,WT0111 int,WT0112 int,WT0113 int,WT0114 int,WT0115 int,WT0116 int,WT0117 int,WT0118 int,WT0119 int,WT0120 int,WT0121 int,WT0122 int,WT0123 int,WT0124 int,WT0125 int,WT0126 int,WT0127 int,WT0128 int,WT0129 int,WT0130 int,WT0131 int,WT0132 int,WT0133 int,WT0134 int,WT0135 int,WT0136 int,WT0137 int,WT0138 int,WT0139 int,WT0140 int,WT0141 int,WT0142 int,WT0143 int,WT0144 int,WT0145 int,WT0146 int,WT0147 int,WT0148 int,WT0149 int,WT0150 int,
|
|
37
|
WT0201 int,WT0202 int,WT0203 int,WT0204 int,WT0205 int,WT0206 int,WT0207 int,WT0208 int,WT0209 int,WT0210 int,WT0211 int,WT0212 int,WT0213 int,WT0214 int,WT0215 int,WT0216 int,WT0217 int,WT0218 int,WT0219 int,WT0220 int,WT0221 int,WT0222 int,WT0223 int,WT0224 int,WT0225 int,WT0226 int,WT0227 int,WT0228 int,WT0229 int,WT0230 int,WT0231 int,WT0232 int,WT0233 int,WT0234 int,WT0235 int,WT0236 int,WT0237 int,WT0238 int,WT0239 int,WT0240 int,WT0241 int,WT0242 int,WT0243 int,WT0244 int,WT0245 int,WT0246 int,WT0247 int,WT0248 int,WT0249 int,WT0250 int,
|
|
38
|
WT0301 int,WT0302 int,WT0303 int,WT0304 int,WT0305 int,WT0306 int,WT0307 int,WT0308 int,WT0309 int,WT0310 int,WT0311 int,WT0312 int,WT0313 int,WT0314 int,WT0315 int,WT0316 int,WT0317 int,WT0318 int,WT0319 int,WT0320 int,WT0321 int,WT0322 int,WT0323 int,WT0324 int,WT0325 int,WT0326 int,WT0327 int,WT0328 int,WT0329 int,WT0330 int,WT0331 int,WT0332 int,WT0333 int,WT0334 int,WT0335 int,WT0336 int,WT0337 int,WT0338 int,WT0339 int,WT0340 int,WT0341 int,WT0342 int,WT0343 int,WT0344 int,WT0345 int,WT0346 int,WT0347 int,WT0348 int,WT0349 int,WT0350 int,
|
|
39
|
WT0401 int,WT0402 int,WT0403 int,WT0404 int,WT0405 int,WT0406 int,WT0407 int,WT0408 int,WT0409 int,WT0410 int,WT0411 int,WT0412 int,WT0413 int,WT0414 int,WT0415 int,WT0416 int,WT0417 int,WT0418 int,WT0419 int,WT0420 int,WT0421 int,WT0422 int,WT0423 int,WT0424 int,WT0425 int,WT0426 int,WT0427 int,WT0428 int,WT0429 int,WT0430 int,WT0431 int,WT0432 int,WT0433 int,WT0434 int,WT0435 int,WT0436 int,WT0437 int,WT0438 int,WT0439 int,WT0440 int,WT0441 int,WT0442 int,WT0443 int,WT0444 int,WT0445 int,WT0446 int,WT0447 int,WT0448 int,WT0449 int,WT0450 int)
|
|
40
|
|
|
41
|
DECLARE @tbl_employee TABLE
|
|
42
|
(employee_id varchar(8),
|
|
43
|
employee_name varchar(50),
|
|
44
|
CCG varchar(50),
|
|
45
|
CCG_Abr varchar(50),
|
|
46
|
CCG_Desc varchar(50),
|
|
47
|
CC varchar(50),
|
|
48
|
CC_Abr varchar(50),
|
|
49
|
CC_Desc varchar(50),
|
|
50
|
org_group VARCHAR(50),
|
|
51
|
org_group_desc VARCHAR(250))
|
|
52
|
|
|
53
|
|
|
54
|
DECLARE @tbl_employee2 TABLE
|
|
55
|
(
|
|
56
|
employee_id varchar(250),
|
|
57
|
employee_name varchar(250),
|
|
58
|
employee_area_id varchar(250),
|
|
59
|
employee_area_descr varchar(250),
|
|
60
|
employee_office_id varchar(250),
|
|
61
|
employee_office_descr varchar(250),
|
|
62
|
employee_status_id varchar(250),
|
|
63
|
employee_status_descr varchar(250),
|
|
64
|
employee_type_id varchar(250),
|
|
65
|
employee_type_descr varchar(250),
|
|
66
|
employee_sub_type_id varchar(250),
|
|
67
|
employee_sub_type_descr varchar(250),
|
|
68
|
payroll_group_id varchar(250),
|
|
69
|
payroll_group_descr varchar(250),
|
|
70
|
organization_id varchar(250),
|
|
71
|
organization_descr varchar(250),
|
|
72
|
cost_center_id varchar(250),
|
|
73
|
cost_center_abbr varchar(250),
|
|
74
|
cost_center_descr varchar(250),
|
|
75
|
cost_center_group_id varchar(250),
|
|
76
|
cost_center_group_abbr varchar(250),
|
|
77
|
cost_center_group_descr varchar(250),
|
|
78
|
position_id varchar(250),
|
|
79
|
position_descr varchar(250),
|
|
80
|
job_id varchar(250),
|
|
81
|
job_descr varchar(250),
|
|
82
|
pay_type_id varchar(250),
|
|
83
|
pay_type_descr varchar(250),
|
|
84
|
pay_class_id varchar(250),
|
|
85
|
pay_class_descr varchar(250),
|
|
86
|
pay_grade_id varchar(250),
|
|
87
|
pay_grade_descr varchar(250),
|
|
88
|
pay_area_id varchar(250),
|
|
89
|
pay_area_descr varchar(250)
|
|
90
|
)
|
|
91
|
|
|
92
|
DECLARE @tbl_tr_enc TABLE
|
|
93
|
(employee_id varchar(8),
|
|
94
|
payPeriodMonth varchar(2),
|
|
95
|
payPeriodYear varchar(4),
|
|
96
|
runPeriodMonth varchar(2),
|
|
97
|
runPeriodYear varchar(4),
|
|
98
|
wageType varchar(4),
|
|
99
|
amount varchar(250))
|
|
100
|
|
|
101
|
--//-- Setting variable
|
|
102
|
SET @payPeriodMonth = SUBSTRING(@payPeriod, 5, 2)
|
|
103
|
SET @payPeriodYear = SUBSTRING(@payPeriod, 1, 4)
|
|
104
|
SET @payPeriodStartDate = @payPeriod + '01'
|
|
105
|
SET @payPeriodEndDateDt = DATEADD(dd, -1, DATEADD(mm, 1, CONVERT(datetime, @payPeriodStartDate)))
|
|
106
|
SET @payPeriodEndDate = @payPeriod + CONVERT(varchar(2), DATEPART(dd, @payPeriodEndDateDt))
|
|
107
|
|
|
108
|
--//-- Select Employee
|
|
109
|
INSERT INTO @tbl_employee
|
|
110
|
SELECT md01.emp_id, md01.full_name, '' AS cg , '' AS cgabbr, '' AS cgdesc, '' AS ccabbr, '', '' AS ccdesc
|
|
111
|
,ISNULL(orggroup.object, 'None') AS org_group
|
|
112
|
,case when oo.location like'____' then locs.emp_subarea_description when oo.location='KPNO' then 'KPNO' else oo.location end as org_grooup_desc
|
|
113
|
FROM hr_md_emp_md0001 AS md01
|
|
114
|
LEFT JOIN hr_tr0300_new AS hr_tr0300 WITH (NOLOCK)
|
|
115
|
ON md01.emp_id = hr_tr0300.employee_id
|
|
116
|
LEFT JOIN hr_md_emp_md0002 md02 WITH (NOLOCK)
|
|
117
|
ON md02.landscape = @landscape
|
|
118
|
AND md02.start_date <= @payPeriodEndDate
|
|
119
|
AND md02.end_date >= @payPeriodEndDate
|
|
120
|
AND md02.emp_id = md01.emp_id
|
|
121
|
LEFT JOIN dbo.hr_md_orm_object AS org
|
|
122
|
ON org.landscape = md02.landscape
|
|
123
|
AND org.object = md02.organization
|
|
124
|
AND org.class = 'O'
|
|
125
|
AND org.start_date <= @payPeriodEndDate
|
|
126
|
AND org.end_date >= @payPeriodEndDate
|
|
127
|
LEFT JOIN hr_md_orm_object orggroup
|
|
128
|
ON hr_tr0300.organization = orggroup.object
|
|
129
|
AND orggroup.class='O'
|
|
130
|
AND orggroup.end_date='99991231'
|
|
131
|
LEFT JOIN hr_md_orm_o_o oo
|
|
132
|
ON orggroup.object= oo.object
|
|
133
|
AND oo.end_date='99991231'
|
|
134
|
LEFT JOIN base_cust_ref_emp_office AS locs
|
|
135
|
ON oo.location=locs.emp_subarea
|
|
136
|
WHERE hr_tr0300.pay_period_month = @payPeriodMonth
|
|
137
|
AND ( hr_tr0300.pay_period_year = @payPeriodYear )
|
|
138
|
AND ( hr_tr0300.company_id = @companycode OR @companycode = '' )
|
|
139
|
AND ( hr_tr0300.payroll_group = @paygroup OR @paygroup = '' )
|
|
140
|
AND ( hr_tr0300.employee_id = @emp_id OR @emp_id = '' )
|
|
141
|
AND md01.start_date <= @payPeriodEndDate
|
|
142
|
AND md01.end_date >= @payPeriodEndDate
|
|
143
|
|
|
144
|
|
|
145
|
|
|
146
|
DECLARE @employee_id varchar(8)
|
|
147
|
DECLARE @employee_name varchar(50)
|
|
148
|
DECLARE @CCG varchar(50)
|
|
149
|
DECLARE @CCG_Abr varchar(50)
|
|
150
|
DECLARE @CCG_Desc varchar(50)
|
|
151
|
DECLARE @CC varchar(50)
|
|
152
|
DECLARE @CC_Abr varchar(50)
|
|
153
|
DECLARE @CC_Desc varchar(50)
|
|
154
|
DECLARE @org_group VARCHAR(50)
|
|
155
|
DECLARE @org_group_desc VARCHAR(250)
|
|
156
|
|
|
157
|
|
|
158
|
DECLARE @tr0300 TABLE(
|
|
159
|
[landscape] [varchar](3) NOT NULL,
|
|
160
|
[employee_id] [varchar](8) NOT NULL,
|
|
161
|
[pay_period_month] [varchar](2) NOT NULL,
|
|
162
|
[pay_period_year] [varchar](4) NOT NULL,
|
|
163
|
[run_period_month] [varchar](2) NOT NULL,
|
|
164
|
[run_period_year] [varchar](4) NOT NULL,
|
|
165
|
[split_indicator] [varchar](1) NOT NULL,
|
|
166
|
[start_date] [varchar](8) NOT NULL,
|
|
167
|
[end_date] [varchar](8) NOT NULL,
|
|
168
|
[data_status] [varchar](1) NULL,
|
|
169
|
[company_id] [varchar](4) NULL,
|
|
170
|
[cost_center] [varchar](8) NULL,
|
|
171
|
[employee_area] [varchar](4) NOT NULL,
|
|
172
|
[employee_office] [varchar](4) NOT NULL,
|
|
173
|
[employee_status] [varchar](2) NOT NULL,
|
|
174
|
[employee_type] [varchar](2) NOT NULL,
|
|
175
|
[employee_subtype] [varchar](2) NOT NULL,
|
|
176
|
[payroll_group] [varchar](2) NOT NULL,
|
|
177
|
[organization] [varchar](8) NOT NULL,
|
|
178
|
[position] [varchar](8) NOT NULL,
|
|
179
|
[job] [varchar](8) NOT NULL,
|
|
180
|
[pay_type] [varchar](2) NULL,
|
|
181
|
[pay_class] [varchar](2) NULL,
|
|
182
|
[pay_grade] [varchar](2) NULL,
|
|
183
|
[pay_area] [varchar](2) NULL,
|
|
184
|
pay_curr VARCHAR(5),
|
|
185
|
pay_curr_rate DECIMAL(18,7),
|
|
186
|
[change_by] [varchar](12) NOT NULL,
|
|
187
|
[change_date] [varchar](14) NOT NULL,
|
|
188
|
[created_by] [varchar](12) NOT NULL,
|
|
189
|
[created_date] [varchar](14) NOT NULL
|
|
190
|
)
|
|
191
|
|
|
192
|
DECLARE @emp_id_ varchar (8)
|
|
193
|
DECLARE @pay_month varchar (2) = substring(@payPeriod,5,2)
|
|
194
|
DECLARE @pay_year varchar (4) = substring(@payPeriod,1,4)
|
|
195
|
|
|
196
|
DECLARE @start_date varchar (10)
|
|
197
|
DECLARE @end_date varchar (10)
|
|
198
|
|
|
199
|
DECLARE @pay_period_month varchar (4)
|
|
200
|
DECLARE @pay_period_year varchar(4)
|
|
201
|
DECLARE @employe_id varchar (8)
|
|
202
|
DECLARE @full_name varchar (250)
|
|
203
|
DECLARE @company_id varchar(4)
|
|
204
|
DECLARE @cost_center varchar (8)
|
|
205
|
DECLARE @employee_area varchar (250)
|
|
206
|
DECLARE @employee_office varchar (250)
|
|
207
|
DECLARE @employee_status varchar (250)
|
|
208
|
DECLARE @employee_type varchar (250)
|
|
209
|
DECLARE @employee_subtype varchar (250)
|
|
210
|
DECLARE @payroll_group varchar (250)
|
|
211
|
DECLARE @organization varchar (250)
|
|
212
|
DECLARE @position varchar (250)
|
|
213
|
DECLARE @job varchar (250)
|
|
214
|
DECLARE @pay_type varchar (250)
|
|
215
|
DECLARE @pay_class varchar (250)
|
|
216
|
DECLARE @pay_grade varchar (250)
|
|
217
|
DECLARE @pay_area varchar (250)
|
|
218
|
|
|
219
|
--Get System Parameter
|
|
220
|
DECLARE @is_encrypted varchar
|
|
221
|
SELECT @is_encrypted = val1
|
|
222
|
FROM dbo.base_cust_parameter
|
|
223
|
where landscape = @landscape and
|
|
224
|
param = 'HR_PY_ENCRYPTED' and
|
|
225
|
start_date <= @payPeriodEndDate and
|
|
226
|
end_date >= @payPeriodEndDate
|
|
227
|
|
|
228
|
DECLARE cur_employee CURSOR FOR
|
|
229
|
SELECT * FROM @tbl_employee
|
|
230
|
|
|
231
|
OPEN cur_employee
|
|
232
|
FETCH cur_employee INTO
|
|
233
|
@employee_id,
|
|
234
|
@employee_name,
|
|
235
|
@CCG,
|
|
236
|
@CCG_Abr,
|
|
237
|
@CCG_Desc,
|
|
238
|
@CC,
|
|
239
|
@CC_Abr,
|
|
240
|
@CC_Desc
|
|
241
|
,@org_group
|
|
242
|
,@org_group_desc
|
|
243
|
WHILE @@Fetch_Status = 0
|
|
244
|
BEGIN
|
|
245
|
DELETE FROM @tr0300
|
|
246
|
|
|
247
|
SET @emp_id_ = @employee_id
|
|
248
|
|
|
249
|
SET @start_date = ''
|
|
250
|
SET @end_date = ''
|
|
251
|
|
|
252
|
SET @pay_period_month = ''
|
|
253
|
SET @pay_period_year = ''
|
|
254
|
SET @employe_id = ''
|
|
255
|
SET @full_name = ''
|
|
256
|
SET @company_id = ''
|
|
257
|
SET @cost_center = ''
|
|
258
|
SET @employee_area = ''
|
|
259
|
SET @employee_office = ''
|
|
260
|
SET @employee_status = ''
|
|
261
|
SET @employee_type = ''
|
|
262
|
SET @employee_subtype = ''
|
|
263
|
SET @payroll_group = ''
|
|
264
|
SET @organization = ''
|
|
265
|
SET @position = ''
|
|
266
|
SET @job = ''
|
|
267
|
SET @pay_type = ''
|
|
268
|
SET @pay_class = ''
|
|
269
|
SET @pay_grade = ''
|
|
270
|
SET @pay_area = ''
|
|
271
|
|
|
272
|
|
|
273
|
INSERT INTO @tr0300
|
|
274
|
SELECT TOP(1) [landscape]
|
|
275
|
,[employee_id]
|
|
276
|
,[pay_period_month]
|
|
277
|
,[pay_period_year]
|
|
278
|
,[run_period_month]
|
|
279
|
,[run_period_year]
|
|
280
|
,[split_indicator]
|
|
281
|
,[start_date]
|
|
282
|
,[end_date]
|
|
283
|
,[data_status]
|
|
284
|
,[company_id]
|
|
285
|
,[cost_center]
|
|
286
|
,[employee_area]
|
|
287
|
,[employee_office]
|
|
288
|
,[employee_status]
|
|
289
|
,[employee_type]
|
|
290
|
,[employee_subtype]
|
|
291
|
,[payroll_group]
|
|
292
|
,[organization]
|
|
293
|
,[position]
|
|
294
|
,[job]
|
|
295
|
,[pay_type]
|
|
296
|
,[pay_class]
|
|
297
|
,[pay_grade]
|
|
298
|
,[pay_area]
|
|
299
|
,[pay_curr]
|
|
300
|
,[pay_curr_rate]
|
|
301
|
,[change_by]
|
|
302
|
,[change_date]
|
|
303
|
,[created_by]
|
|
304
|
,[created_date]
|
|
305
|
FROM hr_tr0300_new
|
|
306
|
WHERE employee_id = @emp_id_
|
|
307
|
AND pay_period_month = @pay_month
|
|
308
|
AND pay_period_year = @pay_year order by run_period_month desc
|
|
309
|
|
|
310
|
SELECT @start_date = tr0300.start_date , @end_date = tr0300.end_date
|
|
311
|
FROM @tr0300 as tr0300
|
|
312
|
|
|
313
|
SELECT @pay_period_month = pay_period_month, @pay_period_year = pay_period_year
|
|
314
|
FROM @tr0300
|
|
315
|
|
|
316
|
SELECT @employe_id = md1.emp_id, @full_name = md1.full_name
|
|
317
|
FROM hr_md_emp_md0001 as md1
|
|
318
|
INNER JOIN @tr0300 as tr0300
|
|
319
|
ON md1.emp_id = tr0300.employee_id
|
|
320
|
AND md1.start_date <= @end_date
|
|
321
|
AND md1.end_date >= @end_date
|
|
322
|
|
|
323
|
SELECT @cost_center = object.description
|
|
324
|
FROM @tr0300 as tr0300
|
|
325
|
LEFT JOIN hr_md_orm_object as object
|
|
326
|
ON object.object = tr0300.cost_center
|
|
327
|
AND object.start_date <= @start_date
|
|
328
|
AND object.end_date >= @end_date
|
|
329
|
AND object.class = 'CC'
|
|
330
|
|
|
331
|
select @employee_area = object.description
|
|
332
|
from @tr0300 as tr0300
|
|
333
|
left join base_cust_ref_emp_area as object
|
|
334
|
on object.emp_area = tr0300.employee_area
|
|
335
|
|
|
336
|
select @employee_office = object.emp_subarea_description
|
|
337
|
from @tr0300 as tr0300
|
|
338
|
left join base_cust_ref_emp_office as object
|
|
339
|
on object.emp_subarea = tr0300.employee_office
|
|
340
|
|
|
341
|
select @employee_status = object.description
|
|
342
|
from @tr0300 as tr0300
|
|
343
|
left join base_cust_ref_emp_status as object
|
|
344
|
on object.emp_status = tr0300.employee_status
|
|
345
|
|
|
346
|
select @employee_type = object.description
|
|
347
|
from @tr0300 as tr0300
|
|
348
|
left join base_cust_ref_emp_type as object
|
|
349
|
on object.emp_type = tr0300.employee_type
|
|
350
|
|
|
351
|
select @employee_subtype = object.description
|
|
352
|
from @tr0300 as tr0300
|
|
353
|
left join base_cust_ref_emp_subtype as object
|
|
354
|
on object.emp_subtype = tr0300.employee_subtype
|
|
355
|
|
|
356
|
select @payroll_group = object.description
|
|
357
|
from @tr0300 as tr0300
|
|
358
|
left join base_cust_ref_payroll_group as object
|
|
359
|
on object.payroll_group = tr0300.payroll_group
|
|
360
|
|
|
361
|
select @organization = object.description
|
|
362
|
from @tr0300 as tr0300
|
|
363
|
left join hr_md_orm_object as object
|
|
364
|
on object.object = tr0300.organization
|
|
365
|
and object.start_date <= @end_date
|
|
366
|
and object.end_date >= @end_date
|
|
367
|
and object.class = 'O'
|
|
368
|
|
|
369
|
select @position = object.description
|
|
370
|
from @tr0300 as tr0300
|
|
371
|
left join hr_md_orm_object as object
|
|
372
|
on object.object = tr0300.position
|
|
373
|
and object.start_date <= @end_date
|
|
374
|
and object.end_date >= @end_date
|
|
375
|
and object.class = 'P'
|
|
376
|
|
|
377
|
select @job = object.description
|
|
378
|
from @tr0300 as tr0300
|
|
379
|
left join hr_md_orm_object as object
|
|
380
|
on object.object = tr0300.job
|
|
381
|
and object.start_date <= @end_date
|
|
382
|
and object.end_date >= @end_date
|
|
383
|
and object.class = 'J'
|
|
384
|
|
|
385
|
select @pay_type = object.description
|
|
386
|
from @tr0300 as tr0300
|
|
387
|
left join base_cust_ref_pay_type as object
|
|
388
|
on object.pay_type = tr0300.pay_type
|
|
389
|
|
|
390
|
select @pay_class = object.description
|
|
391
|
from @tr0300 as tr0300
|
|
392
|
left join base_cust_ref_pay_class as object
|
|
393
|
on object.pay_class = tr0300.pay_class
|
|
394
|
|
|
395
|
select @pay_grade = object.description
|
|
396
|
from @tr0300 as tr0300
|
|
397
|
left join base_cust_ref_pay_grade as object
|
|
398
|
on object.pay_grade = tr0300.pay_grade
|
|
399
|
|
|
400
|
select @pay_area = object.description
|
|
401
|
from @tr0300 as tr0300
|
|
402
|
left join base_cust_ref_pay_area as object
|
|
403
|
on object.pay_area = tr0300.pay_area
|
|
404
|
|
|
405
|
|
|
406
|
INSERT INTO @tbl_employee2
|
|
407
|
select
|
|
408
|
tr0300.employee_id ,
|
|
409
|
employee_name ,
|
|
410
|
tr0300.employee_area as employee_area_id ,
|
|
411
|
a.employee_area_descr ,
|
|
412
|
tr0300.employee_office as employee_office_id ,
|
|
413
|
a.employee_office_descr ,
|
|
414
|
tr0300.employee_status as employee_status_id ,
|
|
415
|
a.employee_status_descr ,
|
|
416
|
tr0300.employee_type as employee_type_id ,
|
|
417
|
a.employee_type_descr ,
|
|
418
|
tr0300.employee_subtype as employee_subtype_id ,
|
|
419
|
a.employee_subtype_descr ,
|
|
420
|
tr0300.payroll_group as payroll_group_id ,
|
|
421
|
a.payroll_group_descr ,
|
|
422
|
tr0300.organization as organization_id ,
|
|
423
|
a.organization_descr ,
|
|
424
|
@CC,
|
|
425
|
@CC_Abr,
|
|
426
|
@CC_Desc ,
|
|
427
|
@CCG,
|
|
428
|
@CCG_Abr,
|
|
429
|
@CCG_Desc,
|
|
430
|
tr0300.position as position_id ,
|
|
431
|
a.position_descr ,
|
|
432
|
tr0300.job as job_id ,
|
|
433
|
a.job_descr ,
|
|
434
|
tr0300.pay_type as pay_type_id,
|
|
435
|
a.pay_type_descr ,
|
|
436
|
tr0300.pay_class as pay_class_id ,
|
|
437
|
a.pay_class_descr ,
|
|
438
|
tr0300.pay_grade as pay_grade_id ,
|
|
439
|
a.pay_grade_descr ,
|
|
440
|
tr0300.pay_area as pay_area_id ,
|
|
441
|
a.pay_area_descr
|
|
442
|
from(
|
|
443
|
select
|
|
444
|
@employe_id as employee_id
|
|
445
|
,@pay_period_month as pay_period_month
|
|
446
|
,@pay_period_year as pay_period_year
|
|
447
|
,@full_name as employee_name
|
|
448
|
,@cost_center as cost_center_descr
|
|
449
|
,@employee_area as employee_area_descr
|
|
450
|
,@employee_office as employee_office_descr
|
|
451
|
,@employee_status as employee_status_descr
|
|
452
|
,@employee_type as employee_type_descr
|
|
453
|
,@employee_subtype as employee_subtype_descr
|
|
454
|
,@payroll_group as payroll_group_descr
|
|
455
|
,@organization as organization_descr
|
|
456
|
,@position as position_descr
|
|
457
|
,@job as job_descr
|
|
458
|
,@pay_type as pay_type_descr
|
|
459
|
,@pay_class as pay_class_descr
|
|
460
|
,@pay_grade as pay_grade_descr
|
|
461
|
,@pay_area as pay_area_descr
|
|
462
|
) as a
|
|
463
|
inner join @tr0300 as tr0300 on a.employee_id = tr0300.employee_id
|
|
464
|
|
|
465
|
|
|
466
|
FETCH cur_employee INTO
|
|
467
|
@employee_id,
|
|
468
|
@employee_name,
|
|
469
|
@CCG,
|
|
470
|
@CCG_Abr,
|
|
471
|
@CCG_Desc,
|
|
472
|
@CC,
|
|
473
|
@CC_Abr,
|
|
474
|
@CC_Desc
|
|
475
|
,@org_group
|
|
476
|
,@org_group_desc
|
|
477
|
|
|
478
|
END
|
|
479
|
|
|
480
|
CLOSE cur_employee
|
|
481
|
|
|
482
|
DEALLOCATE cur_employee
|
|
483
|
|
|
484
|
declare @hr_cust_py_report TABLE (
|
|
485
|
[landscape] [varchar](4) NOT NULL,
|
|
486
|
[rep_id] [varchar](50) NOT NULL,
|
|
487
|
[row] [int] NOT NULL,
|
|
488
|
[col_no] [int] NOT NULL,
|
|
489
|
[seq_no] [int] NOT NULL,
|
|
490
|
[wage_type] [varchar](5) NULL,
|
|
491
|
[descr] [varchar](50) NULL
|
|
492
|
)
|
|
493
|
|
|
494
|
insert into @hr_cust_py_report
|
|
495
|
SELECT [landscape]
|
|
496
|
,[rep_id]
|
|
497
|
,[row]
|
|
498
|
,[col_no]
|
|
499
|
,[seq_no]
|
|
500
|
,[wage_type]
|
|
501
|
,[descr]
|
|
502
|
FROM [dbo].[hr_cust_py_report] WHERE start_date <= @payPeriodEndDate
|
|
503
|
AND end_date >= @payPeriodEndDate and rep_id = 'WT01R'
|
|
504
|
|
|
505
|
declare @hr_cust_py_report_col_name TABLE (
|
|
506
|
[landscape] [varchar](4) NULL,
|
|
507
|
[rep_id] [varchar](50) NULL,
|
|
508
|
[row] [int] NULL,
|
|
509
|
[col_no] [int] NULL,
|
|
510
|
column_name varchar(6))
|
|
511
|
|
|
512
|
insert into @hr_cust_py_report_col_name
|
|
513
|
select *,
|
|
514
|
'WT' + right('00' + convert(varchar,(ROW_NUMBER () OVER (ORDER BY landscape, rep_id, row, col_no) + 49) / 50) ,2)
|
|
515
|
+ right('00' + convert(varchar,((ROW_NUMBER () OVER (ORDER BY landscape, rep_id, row, col_no) -1) % 50)+1) ,2) as column_name
|
|
516
|
from(
|
|
517
|
select distinct landscape, rep_id, row, col_no from @hr_cust_py_report )a ORDER BY landscape, rep_id, row, col_no
|
|
518
|
|
|
519
|
|
|
520
|
declare @row int
|
|
521
|
declare @col int
|
|
522
|
declare @column_name varchar(6)
|
|
523
|
set @row = 0
|
|
524
|
set @col = 0
|
|
525
|
while @row < 4
|
|
526
|
begin
|
|
527
|
set @row = @row + 1
|
|
528
|
set @col = 0
|
|
529
|
while @col < 50
|
|
530
|
begin
|
|
531
|
set @col = @col + 1
|
|
532
|
set @column_name = null
|
|
533
|
|
|
534
|
select @column_name = column_name from @hr_cust_py_report_col_name
|
|
535
|
where column_name = 'WT' + right('00' + convert(varchar, @row),2) + right('00' + convert(varchar, @col),2)
|
|
536
|
|
|
537
|
if @column_name is null or @column_name = ''
|
|
538
|
begin
|
|
539
|
insert into @hr_cust_py_report_col_name
|
|
540
|
select @landscape,'',0,0,'WT' + right('00' + convert(varchar, @row),2) + right('00' + convert(varchar, @col),2)
|
|
541
|
end
|
|
542
|
|
|
543
|
|
|
544
|
|
|
545
|
end
|
|
546
|
|
|
547
|
end
|
|
548
|
|
|
549
|
declare @hr_cust_py_report_desc TABLE (
|
|
550
|
[landscape] [varchar](4) NOT NULL,
|
|
551
|
[rep_id] [varchar](50) NOT NULL,
|
|
552
|
[row] [int] NOT NULL,
|
|
553
|
[col_no] [int] NOT NULL,
|
|
554
|
column_name varchar(6),
|
|
555
|
[description] [varchar](50) NULL)
|
|
556
|
|
|
557
|
insert into @hr_cust_py_report_desc
|
|
558
|
select a.*, b.descr from @hr_cust_py_report_col_name as a
|
|
559
|
left join(
|
|
560
|
select MAX(landscape) as landscape, MAX(rep_id) as rep_id, MAX(row) as row, MAX(col_no) as col_no,MAX(descr) as descr
|
|
561
|
from @hr_cust_py_report group by landscape, rep_id, row,col_no)as b
|
|
562
|
on a.landscape =b.landscape and
|
|
563
|
a.rep_id=b.rep_id and
|
|
564
|
a.row=b.row and
|
|
565
|
a.col_no=b.col_no
|
|
566
|
|
|
567
|
|
|
568
|
INSERT INTO @tbl_tr_enc
|
|
569
|
SELECT pr.employee_id, pr.pay_period_month, pr.pay_period_year,
|
|
570
|
pr.run_period_month, pr.run_period_year, pr.wage_type, pr.amount
|
|
571
|
FROM hr_tr0301_new pr WITH (NOLOCK)
|
|
572
|
INNER JOIN @tbl_employee emp
|
|
573
|
ON pr.employee_id = emp.employee_id
|
|
574
|
INNER JOIN hr_cust_py_report rep
|
|
575
|
ON pr.wage_type = rep.wage_type
|
|
576
|
WHERE pr.run_period_year = @payPeriodYear
|
|
577
|
AND pr.run_period_month = @payPeriodMonth
|
|
578
|
AND pr.run_period_month = pr.pay_period_month
|
|
579
|
AND pr.run_period_year = pr.pay_period_year
|
|
580
|
|
|
581
|
INSERT INTO @tbl_tr_enc
|
|
582
|
SELECT pr.employee_id, pr.pay_period_month, pr.pay_period_year,
|
|
583
|
pr.run_period_month, pr.run_period_year, pr.wage_type, pr.amount
|
|
584
|
FROM hr_tr0301_retro_new pr WITH (NOLOCK)
|
|
585
|
INNER JOIN @tbl_employee emp
|
|
586
|
ON pr.employee_id = emp.employee_id
|
|
587
|
INNER JOIN hr_cust_py_report rep
|
|
588
|
ON pr.wage_type = rep.wage_type
|
|
589
|
WHERE pr.run_period_year = @payPeriodYear
|
|
590
|
AND pr.run_period_month = @payPeriodMonth
|
|
591
|
AND pr.run_period_month = pr.pay_period_month
|
|
592
|
AND pr.run_period_year = pr.pay_period_year
|
|
593
|
|
|
594
|
DECLARE @tbl_tr_dec TABLE
|
|
595
|
(col_no int,
|
|
596
|
employee_id varchar(8),
|
|
597
|
amount DECIMAL(22,0),
|
|
598
|
period_id VARCHAR(20),
|
|
599
|
period_desc VARCHAR(50))
|
|
600
|
|
|
601
|
insert into @tbl_tr_dec
|
|
602
|
select DISTINCT col_no, employee_id, SUM(amount) as amount, period_id, period_desc from(
|
|
603
|
select DISTINCT b.col_no, c.employee_id,
|
|
604
|
CASE @is_encrypted when '0' then convert(decimal(18, 2),amount) else convert(decimal(18, 2),dbo.SDE(amount, N'M!N0V@2010')) end as amount
|
|
605
|
,(c.payPeriodMonth + c.payPeriodYear) AS period_id
|
|
606
|
,dbo.fn_formatdatetime_indonesia((c.payPeriodYear + c.payPeriodMonth + '01'), 'mmmm yyyy') AS period_desc
|
|
607
|
from @hr_cust_py_report_desc as b
|
|
608
|
inner join @hr_cust_py_report as a on
|
|
609
|
a.landscape = b.landscape and
|
|
610
|
a.col_no = b.col_no and
|
|
611
|
a.rep_id = b.rep_id and
|
|
612
|
a.row = b.row
|
|
613
|
inner join @tbl_tr_enc c on
|
|
614
|
a.wage_type = c.wageType ) as d
|
|
615
|
group by col_no, employee_id, period_id, period_desc
|
|
616
|
|
|
617
|
|
|
618
|
SELECT
|
|
619
|
SUM([WT0101]) as [WT0101],
|
|
620
|
SUM([WT0102]) as [WT0102],
|
|
621
|
SUM([WT0103]) as [WT0103],
|
|
622
|
SUM([WT0104]) as [WT0104],
|
|
623
|
SUM([WT0105]) as [WT0105],
|
|
624
|
SUM([WT0106]) as [WT0106],
|
|
625
|
SUM([WT0107]) as [WT0107],
|
|
626
|
SUM([WT0108]) as [WT0108],
|
|
627
|
SUM([WT0109]) as [WT0109],
|
|
628
|
SUM([WT0110]) as [WT0110],
|
|
629
|
SUM([WT0111]) as [WT0111],
|
|
630
|
SUM([WT0112]) as [WT0112],
|
|
631
|
SUM([WT0113]) as [WT0113],
|
|
632
|
SUM([WT0114]) as [WT0114],
|
|
633
|
SUM([WT0115]) as [WT0115],
|
|
634
|
SUM([WT0116]) as [WT0116],
|
|
635
|
SUM([WT0117]) as [WT0117],
|
|
636
|
SUM([WT0118]) as [WT0118],
|
|
637
|
SUM([WT0119]) as [WT0119],
|
|
638
|
SUM([WT0120]) as [WT0120],
|
|
639
|
SUM([WT0121]) as [WT0121],
|
|
640
|
SUM([WT0122]) as [WT0122],
|
|
641
|
SUM([WT0123]) as [WT0123],
|
|
642
|
SUM([WT0124]) as [WT0124],
|
|
643
|
SUM([WT0125]) as [WT0125],
|
|
644
|
SUM([WT0126]) as [WT0126],
|
|
645
|
SUM([WT0127]) as [WT0127],
|
|
646
|
SUM([WT0128]) as [WT0128],
|
|
647
|
SUM([WT0129]) as [WT0129],
|
|
648
|
SUM([WT0130]) as [WT0130],
|
|
649
|
SUM([WT0131]) as [WT0131],
|
|
650
|
SUM([WT0132]) as [WT0132],
|
|
651
|
SUM([WT0133]) as [WT0133],
|
|
652
|
SUM([WT0134]) as [WT0134],
|
|
653
|
SUM([WT0135]) as [WT0135],
|
|
654
|
SUM([WT0136]) as [WT0136],
|
|
655
|
SUM([WT0137]) as [WT0137],
|
|
656
|
SUM([WT0138]) as [WT0138],
|
|
657
|
SUM([WT0139]) as [WT0139],
|
|
658
|
SUM([WT0140]) as [WT0140],
|
|
659
|
SUM([WT0141]) as [WT0141],
|
|
660
|
SUM([WT0142]) as [WT0142],
|
|
661
|
SUM([WT0143]) as [WT0143],
|
|
662
|
SUM([WT0144]) as [WT0144],
|
|
663
|
SUM([WT0145]) as [WT0145],
|
|
664
|
SUM([WT0146]) as [WT0146],
|
|
665
|
SUM([WT0147]) as [WT0147],
|
|
666
|
SUM([WT0148]) as [WT0148],
|
|
667
|
SUM([WT0149]) as [WT0149],
|
|
668
|
SUM([WT0150]) as [WT0150],
|
|
669
|
SUM([WT0201]) as [WT0201],
|
|
670
|
SUM([WT0202]) as [WT0202],
|
|
671
|
SUM([WT0203]) as [WT0203],
|
|
672
|
SUM([WT0204]) as [WT0204],
|
|
673
|
SUM([WT0205]) as [WT0205],
|
|
674
|
SUM([WT0206]) as [WT0206],
|
|
675
|
SUM([WT0207]) as [WT0207],
|
|
676
|
SUM([WT0208]) as [WT0208],
|
|
677
|
SUM([WT0209]) as [WT0209],
|
|
678
|
SUM([WT0210]) as [WT0210],
|
|
679
|
SUM([WT0211]) as [WT0211],
|
|
680
|
SUM([WT0212]) as [WT0212],
|
|
681
|
SUM([WT0213]) as [WT0213],
|
|
682
|
SUM([WT0214]) as [WT0214],
|
|
683
|
SUM([WT0215]) as [WT0215],
|
|
684
|
SUM([WT0216]) as [WT0216],
|
|
685
|
SUM([WT0217]) as [WT0217],
|
|
686
|
SUM([WT0218]) as [WT0218],
|
|
687
|
SUM([WT0219]) as [WT0219],
|
|
688
|
SUM([WT0220]) as [WT0220],
|
|
689
|
SUM([WT0221]) as [WT0221],
|
|
690
|
SUM([WT0222]) as [WT0222],
|
|
691
|
SUM([WT0223]) as [WT0223],
|
|
692
|
SUM([WT0224]) as [WT0224],
|
|
693
|
SUM([WT0225]) as [WT0225],
|
|
694
|
SUM([WT0226]) as [WT0226],
|
|
695
|
SUM([WT0227]) as [WT0227],
|
|
696
|
SUM([WT0228]) as [WT0228],
|
|
697
|
SUM([WT0229]) as [WT0229],
|
|
698
|
SUM([WT0230]) as [WT0230],
|
|
699
|
SUM([WT0231]) as [WT0231],
|
|
700
|
SUM([WT0232]) as [WT0232],
|
|
701
|
SUM([WT0233]) as [WT0233],
|
|
702
|
SUM([WT0234]) as [WT0234],
|
|
703
|
SUM([WT0235]) as [WT0235],
|
|
704
|
SUM([WT0236]) as [WT0236],
|
|
705
|
SUM([WT0237]) as [WT0237],
|
|
706
|
SUM([WT0238]) as [WT0238],
|
|
707
|
SUM([WT0239]) as [WT0239],
|
|
708
|
SUM([WT0240]) as [WT0240],
|
|
709
|
SUM([WT0241]) as [WT0241],
|
|
710
|
SUM([WT0242]) as [WT0242],
|
|
711
|
SUM([WT0243]) as [WT0243],
|
|
712
|
SUM([WT0244]) as [WT0244],
|
|
713
|
SUM([WT0245]) as [WT0245],
|
|
714
|
SUM([WT0246]) as [WT0246],
|
|
715
|
SUM([WT0247]) as [WT0247],
|
|
716
|
SUM([WT0248]) as [WT0248],
|
|
717
|
SUM([WT0249]) as [WT0249],
|
|
718
|
SUM([WT0250]) as [WT0250],
|
|
719
|
SUM([WT0301]) as [WT0301],
|
|
720
|
SUM([WT0302]) as [WT0302],
|
|
721
|
SUM([WT0303]) as [WT0303],
|
|
722
|
SUM([WT0304]) as [WT0304],
|
|
723
|
SUM([WT0305]) as [WT0305],
|
|
724
|
SUM([WT0306]) as [WT0306],
|
|
725
|
SUM([WT0307]) as [WT0307],
|
|
726
|
SUM([WT0308]) as [WT0308],
|
|
727
|
SUM([WT0309]) as [WT0309],
|
|
728
|
SUM([WT0310]) as [WT0310],
|
|
729
|
SUM([WT0311]) as [WT0311],
|
|
730
|
SUM([WT0312]) as [WT0312],
|
|
731
|
SUM([WT0313]) as [WT0313],
|
|
732
|
SUM([WT0314]) as [WT0314],
|
|
733
|
SUM([WT0315]) as [WT0315],
|
|
734
|
SUM([WT0316]) as [WT0316],
|
|
735
|
SUM([WT0317]) as [WT0317],
|
|
736
|
SUM([WT0318]) as [WT0318],
|
|
737
|
SUM([WT0319]) as [WT0319],
|
|
738
|
SUM([WT0320]) as [WT0320],
|
|
739
|
SUM([WT0321]) as [WT0321],
|
|
740
|
SUM([WT0322]) as [WT0322],
|
|
741
|
SUM([WT0323]) as [WT0323],
|
|
742
|
SUM([WT0324]) as [WT0324],
|
|
743
|
SUM([WT0325]) as [WT0325],
|
|
744
|
SUM([WT0326]) as [WT0326],
|
|
745
|
SUM([WT0327]) as [WT0327],
|
|
746
|
SUM([WT0328]) as [WT0328],
|
|
747
|
SUM([WT0329]) as [WT0329],
|
|
748
|
SUM([WT0330]) as [WT0330],
|
|
749
|
SUM([WT0331]) as [WT0331],
|
|
750
|
SUM([WT0332]) as [WT0332],
|
|
751
|
SUM([WT0333]) as [WT0333],
|
|
752
|
SUM([WT0334]) as [WT0334],
|
|
753
|
SUM([WT0335]) as [WT0335],
|
|
754
|
SUM([WT0336]) as [WT0336],
|
|
755
|
SUM([WT0337]) as [WT0337],
|
|
756
|
SUM([WT0338]) as [WT0338],
|
|
757
|
SUM([WT0339]) as [WT0339],
|
|
758
|
SUM([WT0340]) as [WT0340],
|
|
759
|
SUM([WT0341]) as [WT0341],
|
|
760
|
SUM([WT0342]) as [WT0342],
|
|
761
|
SUM([WT0343]) as [WT0343],
|
|
762
|
SUM([WT0344]) as [WT0344],
|
|
763
|
SUM([WT0345]) as [WT0345],
|
|
764
|
SUM([WT0346]) as [WT0346],
|
|
765
|
SUM([WT0347]) as [WT0347],
|
|
766
|
SUM([WT0348]) as [WT0348],
|
|
767
|
SUM([WT0349]) as [WT0349],
|
|
768
|
SUM([WT0350]) as [WT0350],
|
|
769
|
SUM([WT0401]) as [WT0401],
|
|
770
|
SUM([WT0402]) as [WT0402],
|
|
771
|
SUM([WT0403]) as [WT0403],
|
|
772
|
SUM([WT0404]) as [WT0404],
|
|
773
|
SUM([WT0405]) as [WT0405],
|
|
774
|
SUM([WT0406]) as [WT0406],
|
|
775
|
SUM([WT0407]) as [WT0407],
|
|
776
|
SUM([WT0408]) as [WT0408],
|
|
777
|
SUM([WT0409]) as [WT0409],
|
|
778
|
SUM([WT0410]) as [WT0410],
|
|
779
|
SUM([WT0411]) as [WT0411],
|
|
780
|
SUM([WT0412]) as [WT0412],
|
|
781
|
SUM([WT0413]) as [WT0413],
|
|
782
|
SUM([WT0414]) as [WT0414],
|
|
783
|
SUM([WT0415]) as [WT0415],
|
|
784
|
SUM([WT0416]) as [WT0416],
|
|
785
|
SUM([WT0417]) as [WT0417],
|
|
786
|
SUM([WT0418]) as [WT0418],
|
|
787
|
SUM([WT0419]) as [WT0419],
|
|
788
|
SUM([WT0420]) as [WT0420],
|
|
789
|
SUM([WT0421]) as [WT0421],
|
|
790
|
SUM([WT0422]) as [WT0422],
|
|
791
|
SUM([WT0423]) as [WT0423],
|
|
792
|
SUM([WT0424]) as [WT0424],
|
|
793
|
SUM([WT0425]) as [WT0425],
|
|
794
|
SUM([WT0426]) as [WT0426],
|
|
795
|
SUM([WT0427]) as [WT0427],
|
|
796
|
SUM([WT0428]) as [WT0428],
|
|
797
|
SUM([WT0429]) as [WT0429],
|
|
798
|
SUM([WT0430]) as [WT0430],
|
|
799
|
SUM([WT0431]) as [WT0431],
|
|
800
|
SUM([WT0432]) as [WT0432],
|
|
801
|
SUM([WT0433]) as [WT0433],
|
|
802
|
SUM([WT0434]) as [WT0434],
|
|
803
|
SUM([WT0435]) as [WT0435],
|
|
804
|
SUM([WT0436]) as [WT0436],
|
|
805
|
SUM([WT0437]) as [WT0437],
|
|
806
|
SUM([WT0438]) as [WT0438],
|
|
807
|
SUM([WT0439]) as [WT0439],
|
|
808
|
SUM([WT0440]) as [WT0440],
|
|
809
|
SUM([WT0441]) as [WT0441],
|
|
810
|
SUM([WT0442]) as [WT0442],
|
|
811
|
SUM([WT0443]) as [WT0443],
|
|
812
|
SUM([WT0444]) as [WT0444],
|
|
813
|
SUM([WT0445]) as [WT0445],
|
|
814
|
SUM([WT0446]) as [WT0446],
|
|
815
|
SUM([WT0447]) as [WT0447],
|
|
816
|
SUM([WT0448]) as [WT0448],
|
|
817
|
SUM([WT0449]) as [WT0449],
|
|
818
|
SUM([WT0450]) as [WT0450]
|
|
819
|
,COUNT(DISTINCT employee_id) AS TotEmpID
|
|
820
|
,period_id
|
|
821
|
,period_desc
|
|
822
|
,convert(decimal(28,0), '1') AS flag
|
|
823
|
,org_group
|
|
824
|
,org_group_desc
|
|
825
|
FROM (
|
|
826
|
SELECT DISTINCT d.*, c.column_name, c.description, c.landscape, c.rep_id, c.row
|
|
827
|
,h.org_group
|
|
828
|
,h.org_group_desc
|
|
829
|
FROM @hr_cust_py_report_desc as c
|
|
830
|
LEFT JOIN ( SELECT DISTINCT b.*, a.col_no, a.amount, period_id, period_desc
|
|
831
|
FROM @tbl_tr_dec AS a
|
|
832
|
INNER JOIN @tbl_employee2 as b on a.employee_id = b.employee_id ) AS d
|
|
833
|
ON c.col_no = d.col_no
|
|
834
|
LEFT JOIN @tbl_employee AS h
|
|
835
|
ON h.employee_id = d.employee_id
|
|
836
|
)AS SourceTable
|
|
837
|
PIVOT
|
|
838
|
(
|
|
839
|
SUM(amount)
|
|
840
|
FOR column_name IN (
|
|
841
|
[WT0101],[WT0102],[WT0103],[WT0104],[WT0105],[WT0106],[WT0107],[WT0108],[WT0109],[WT0110],[WT0111],[WT0112],[WT0113],[WT0114],[WT0115],[WT0116],[WT0117],[WT0118],[WT0119],[WT0120],[WT0121],[WT0122],[WT0123],[WT0124],[WT0125],[WT0126],[WT0127],[WT0128],[WT0129],[WT0130],[WT0131],[WT0132],[WT0133],[WT0134],[WT0135],[WT0136],[WT0137],[WT0138],[WT0139],[WT0140],[WT0141],[WT0142],[WT0143],[WT0144],[WT0145],[WT0146],[WT0147],[WT0148],[WT0149],[WT0150],
|
|
842
|
[WT0201],[WT0202],[WT0203],[WT0204],[WT0205],[WT0206],[WT0207],[WT0208],[WT0209],[WT0210],[WT0211],[WT0212],[WT0213],[WT0214],[WT0215],[WT0216],[WT0217],[WT0218],[WT0219],[WT0220],[WT0221],[WT0222],[WT0223],[WT0224],[WT0225],[WT0226],[WT0227],[WT0228],[WT0229],[WT0230],[WT0231],[WT0232],[WT0233],[WT0234],[WT0235],[WT0236],[WT0237],[WT0238],[WT0239],[WT0240],[WT0241],[WT0242],[WT0243],[WT0244],[WT0245],[WT0246],[WT0247],[WT0248],[WT0249],[WT0250],
|
|
843
|
[WT0301],[WT0302],[WT0303],[WT0304],[WT0305],[WT0306],[WT0307],[WT0308],[WT0309],[WT0310],[WT0311],[WT0312],[WT0313],[WT0314],[WT0315],[WT0316],[WT0317],[WT0318],[WT0319],[WT0320],[WT0321],[WT0322],[WT0323],[WT0324],[WT0325],[WT0326],[WT0327],[WT0328],[WT0329],[WT0330],[WT0331],[WT0332],[WT0333],[WT0334],[WT0335],[WT0336],[WT0337],[WT0338],[WT0339],[WT0340],[WT0341],[WT0342],[WT0343],[WT0344],[WT0345],[WT0346],[WT0347],[WT0348],[WT0349],[WT0350],
|
|
844
|
[WT0401],[WT0402],[WT0403],[WT0404],[WT0405],[WT0406],[WT0407],[WT0408],[WT0409],[WT0410],[WT0411],[WT0412],[WT0413],[WT0414],[WT0415],[WT0416],[WT0417],[WT0418],[WT0419],[WT0420],[WT0421],[WT0422],[WT0423],[WT0424],[WT0425],[WT0426],[WT0427],[WT0428],[WT0429],[WT0430],[WT0431],[WT0432],[WT0433],[WT0434],[WT0435],[WT0436],[WT0437],[WT0438],[WT0439],[WT0440],[WT0441],[WT0442],[WT0443],[WT0444],[WT0445],[WT0446],[WT0447],[WT0448],[WT0449],[WT0450]
|
|
845
|
)
|
|
846
|
)AS PivotTable
|
|
847
|
GROUP BY
|
|
848
|
period_id
|
|
849
|
,period_desc
|
|
850
|
,org_group
|
|
851
|
,org_group_desc
|
|
852
|
HAVING org_group IS NOT NULL
|
|
853
|
|
