1 PACKAGE BODY HXC_TCD_XML_PKG AS
2 /* $Header: hxctcdrpt.pkb 120.8.12010000.12 2009/02/02 13:15:59 asrajago ship $ */
3
4
5 TYPE r_person_id IS RECORD (person_id NUMBER(15));
6 TYPE t_person_ids IS TABLE OF r_person_id INDEX BY BINARY_INTEGER;
7
8 g_tc_start_date DATE;
9
10 function afterpform return boolean is
11 begin
12
13 l_resource_id := p_resource_id;
14
15 l_rec_period_id := p_rec_period_id;
16 l_tc_period := p_tc_period;
17 l_period_start_date := p_period_start_date;
18 l_period_end_date := p_period_end_date;
19 l_supervisor_id := p_supervisor_id;
20 l_reptng_emp := p_reptng_emp;
21 l_org_id := p_org_id;
22 l_location_id := p_location_id;
23
24 l_sel_supervisor_id := p_sel_supervisor_id;
25 l_sel_tc_status := p_sel_tc_status;
26
27 l_rpt_status := 'IN PROGRESS';
28
29 g_tc_start_date := l_period_start_date;
30
31 SELECT name INTO l_rec_period
32 FROM hxc_recurring_periods
33 WHERE recurring_period_id = l_rec_period_id;
34
35 SELECT full_name INTO l_supervisor_name
36 FROM per_all_people_f
37 WHERE person_id = l_supervisor_id
38 AND sysdate between effective_start_date and effective_end_date;
39
40 l_location_name := null;
41 l_org_name := null;
42
43 IF p_org_id IS NOT null
44 THEN
45 SELECT name INTO l_org_name
46 FROM hr_all_organization_units
47 WHERE organization_id = l_org_id;
48 END IF;
49
50 IF p_location_id IS NOT null
51 THEN
52 SELECT location_code INTO l_location_name
53 FROM hr_locations_all
54 WHERE location_id = l_location_id;
55 END IF;
56
57 return (TRUE);
58 end;
59
60 function BeforeReport return boolean is
61 begin
62 clear_report_table;
63 populate_report_table;
64 --hr_standard.event('BEFORE REPORT');
65 return (TRUE);
66 end;
67
68 function AfterReport return boolean is
69 begin
70 --hr_standard.event('AFTER REPORT');
71 clear_report_table;
72 return (TRUE);
73 end;
74
75 --Functions to refer Oracle report placeholders--
76
77
78 PROCEDURE filter_person_ids(p_person_ids IN t_person_ids,
79 p_filtered_person_ids OUT NOCOPY t_person_ids)
80 IS
81
82 l_index NUMBER;
83 l_person_index NUMBER;
84 l_rec_period NUMBER;
85 l_pref_eval_date DATE;
86 l_EvalDateSql VARCHAR2(800);
87 BEGIN
88
89 l_index := p_person_ids.FIRST;
90 l_person_index := 1;
91
92 -- Bug 8205132
93 -- Added a group by person_id in the below dynamic sql text.
94 -- Since the person_ids table coming in might have some
95 -- unwanted person ids, the query might be run for persons
96 -- who are not active on the given period.
97 -- The below query would trim all that off fine, but since
98 -- the selected column is an MIN value without a GROUP BY
99 -- it would always return a value -- a NULL date.
100 -- This would create problems in the following Preference
101 -- Evaluation call.
102 -- Added the GROUP BY clause so no row is returned when
103 -- the WHERE clause fails.
104
105 l_EvalDateSql := 'SELECT GREATEST(:1,tmp.start_date)
106 FROM
107 (SELECT min(effective_start_date) start_date
108 FROM per_all_assignments_f
109 WHERE person_id = :2
110 AND assignment_type IN (''E'', ''C'')
111 AND primary_flag = ''Y''
112 AND ((trunc(effective_start_date) <= trunc(:3)
113 AND trunc(effective_end_date) >= trunc(:4))
114 OR (effective_start_date = (SELECT min(effective_start_date)
115 FROM per_all_assignments_f
116 WHERE person_id = :5
117 AND assignment_type IN (''E'', ''C'')
118 AND primary_flag = ''Y''
119 AND trunc(effective_start_date) > trunc(:6)
120 AND trunc(effective_start_date) <= trunc(:7)))) GROUP BY person_id ) tmp';
121 LOOP
122 EXIT WHEN NOT p_person_ids.exists(l_index);
123
124 -- Bug 8205132
125 -- The BEGIN-END block to handle the 1403 error due to the
126 -- above changed behavior.
127 -- If the query returns nothing, the l_rec_period value is set
128 -- to -1 in the Exception block, and the person_id would not pass
129 -- the filter.
130
131 BEGIN
132
133 EXECUTE IMMEDIATE l_EvalDateSql INTO l_pref_eval_date USING
134 l_period_start_date, p_person_ids(l_index).person_id, l_period_start_date,l_period_start_date,
135 p_person_ids(l_index).person_id, l_period_start_date,l_period_end_date;
136
137 l_rec_period := hxc_preference_evaluation.resource_preferences
138 (p_person_ids(l_index).person_id,
139 'TC_W_TCRD_PERIOD',
140 1,
141 l_pref_eval_date, -99);
142
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145 l_rec_period := -1;
146 END;
147
148 IF (l_rec_period = l_rec_period_id)
149 THEN
150 p_filtered_person_ids(l_person_index).person_id := p_person_ids(l_index).person_id;
151 l_person_index := l_person_index + 1;
152 END IF;
153
154 l_index := p_person_ids.next(l_index);
155 END LOOP;
156
157 END filter_person_ids;
158
159 FUNCTION get_timecard_start_date(p_person_id IN NUMBER, p_timecard_id IN NUMBER,
160 p_period_start_date IN DATE DEFAULT null, p_period_end_date IN DATE default null)
161 RETURN DATE
162 IS
163 l_index NUMBER;
164 l_tc_start_date DATE;
165 l_tc_start_time DATE;
166 l_sql varchar2(800);
167 l_dummy VARCHAR2(2);
168
169 -- Bug 7658500
170 -- Added a new not exists condition for the below cursor.
171 -- The cursor originally added to look for a midperiod
172 -- termination so that the timecard would be picked up
173 -- even if the stop_time is less that the period end date.
174 -- This introduced a problem in reverse termination.
175 -- Eg. Emp has to submit timecard for 1-jan-07 to 7-jan-07
176
177
178 -- If he is terminated on 4-Jan-07, Time store lets him enter
179 -- timecard only for 1-jan to 4-jan. Without the below cursor
180 -- the timecard for 1-jan-07 to 7-jan-07 would look appear as
181 -- NOT ENTERED because the stop times dont match.
182 --
183 -- This adds a problem for reverse termination. If the termination
184 -- is reversed as of 5-jan-07, the employee can submit a second timecard
185 -- for the period, 5-jan-07 to 7-jan-07.
186 -- Before this bug fix, the Dashboard and report would pull up
187 -- an extra record for NOT ENTERED status because there is no timecard
188 -- entered for the period end date -- at least that was not being
189 -- checked here. So the below cursor is altered to process this
190 -- termination scenario only if there was no reverse termination
191 -- happening. The smaller timecard EXISTS condition is appended
192 -- with a condition which checks if there is one timecard for
193 -- the rest of the period -- dont pick up a record if there is one
194 -- such timecard.
195
196 -- Bug 8205132
197 -- Added input values for the cursor below to pick up the
198 -- exact values.
199
200
201 CURSOR c_chk_tc_exists ( p_person_id IN NUMBER,
202 p_period_start_date IN DATE,
203 p_period_end_date IN DATE)
204 IS
205 SELECT 'Y' FROM dual
206 WHERE (
207 EXISTS (
208 SELECT 'Y'
209 FROM hxc_timecard_summary
210 WHERE resource_id = p_person_id
211 AND TRUNC(start_time) >= p_period_start_date
212 AND TRUNC(stop_time) < p_period_end_date)
213 AND NOT EXISTS (
214 SELECT 'Y'
215 FROM hxc_timecard_summary
216 WHERE resource_id = p_person_id
217 AND TRUNC(start_time) > p_period_start_date
218 AND TRUNC(stop_time) <= p_period_end_date)
219 )
220 AND EXISTS
221 (SELECT 'Y'
222 FROM per_all_assignments_f
223 WHERE person_id = p_person_id
224 AND assignment_type IN('E','C')
225 AND primary_flag = 'Y'
226 AND trunc(effective_start_date) <= trunc(p_period_start_date)
227 AND trunc(effective_end_date) >= trunc(p_period_end_date));
228 BEGIN
229
230 hr_utility.trace(' ANUTHI Getting timecard start date for person id ' || p_person_id);
231
232 IF p_period_start_date IS NOT NULL THEN
233 l_period_start_date := p_period_start_date;
234 g_tc_start_date := p_period_start_date;
235 END IF;
236
237 IF p_period_end_date IS NOT NULL THEN
238 l_period_end_date := p_period_end_date;
239 END IF;
240
241 -- Get the max of effective start date, this will pick up the timecards for mid period rehires which are NOT ENTERED
242 l_sql := 'SELECT max(effective_start_date)
243 FROM per_all_assignments_f
244 WHERE person_id = :1
245 AND assignment_type IN (''E'', ''C'')
246 AND primary_flag = ''Y''
247 AND ((effective_start_date <= :2
248 AND effective_end_date >= :3)
249 OR (effective_start_date > :4
250 AND effective_start_date <= :5)
251 OR (effective_start_date <= :6
252 AND effective_end_date <= :7))';
253
254 EXECUTE IMMEDIATE l_sql INTO l_tc_start_date USING
255 p_person_id, l_period_start_date, l_period_end_date,
256 l_period_start_date, l_period_end_date, l_period_start_date, l_period_end_date;
257
258 hr_utility.trace(' ANUTHI l_tc_start_date 1 ' || l_tc_start_date);
259
260 IF l_tc_start_date < l_period_start_date
261 THEN
262 l_tc_start_date := l_period_start_date;
263 END IF;
264
265 -- If a timecard exists, start date will be set to timecard start date
266 IF p_timecard_id IS NOT NULL
267 THEN
268 SELECT start_time INTO l_tc_start_time FROM hxc_timecard_summary WHERE timecard_id = p_timecard_id;
269
270 l_tc_start_date := l_tc_start_time;
271
272 END IF;
273
274 --In case of mid period reverse termination, check if a timecard already exists in the selected timecard period
275 --but with the end date as the termination date
276 --If yes, then there will be a not entered timecard for the rest of the timecard period
277
278 -- Bug 8205132
279 -- Added input parameters for the cursor.
280
281 OPEN c_chk_tc_exists( p_person_id => p_person_id,
282 p_period_start_date => l_period_start_date,
283 p_period_end_date => l_period_end_date );
284
285 FETCH c_chk_tc_exists INTO l_dummy;
286
287 IF c_chk_tc_exists%NOTFOUND THEN
288 CLOSE c_chk_tc_exists;
289 ELSE
290 CLOSE c_chk_tc_exists;
291 IF p_timecard_id IS NULL THEN
292 l_sql := null;
293 -- Bug 8205132
294 -- Added a TRUNC below to avoid the timestamp.
295 l_sql := 'SELECT TRUNC(stop_time) + 1
296 FROM hxc_timecard_summary
297 WHERE resource_id = :1
298 AND TRUNC(start_time) >= :2
299 AND TRUNC(stop_time) < :3';
300
301 EXECUTE IMMEDIATE l_sql INTO l_tc_start_time USING
302 p_person_id, l_period_start_date, l_period_end_date;
303
304
305 IF l_tc_start_time is NOT NULL AND l_tc_start_date < l_tc_start_time THEN
306 l_tc_start_date := l_tc_start_time;
307 END IF;
308 END IF;
309 END IF;
310 g_tc_start_date := l_tc_start_date;
311
312 hr_utility.trace(' ANUTHI g_tc_start_date ' || g_tc_start_date);
313
314 return l_tc_start_date;
315
316 END get_timecard_start_date;
317
318 FUNCTION get_timecard_end_date(p_person_id IN NUMBER, p_timecard_id IN NUMBER, p_period_end_date IN DATE default null)
319 RETURN DATE
320 IS
321 l_pref_end_date DATE;
322 l_pref_table hxc_preference_evaluation.t_pref_table;
323 l_start_date DATE;
324 l_end_date DATE;
325 l_index NUMBER;
326 l_tc_end_date DATE;
327 l_tc_stop_date DATE;
328 l_sql VARCHAR2(500);
329 l_rec_period varchar2(100);
330 l_prev_rec_period varchar2(100);
331
332 BEGIN
333
334 l_pref_table.DELETE;
335
336 hr_utility.trace(' Getting timecard end date for person id ' || p_person_id);
337
338 IF p_period_end_date IS NOT NULL THEN
339 l_period_end_date := p_period_end_date;
340 END IF;
341
342 hxc_preference_evaluation.resource_preferences(
343 p_resource_id => p_person_id
344 ,p_preference_code => 'TC_W_TCRD_PERIOD'
345 ,p_start_evaluation_date => g_tc_start_date
346 ,p_end_evaluation_date => l_period_end_date
347 ,p_sorted_pref_table => l_pref_table );
348
349 hr_utility.trace(' l_pref_table.count ' || l_pref_table.count);
350
351 l_index := l_pref_table.FIRST;
352 l_prev_rec_period := '';
353 l_pref_end_date := l_pref_table(l_index).end_date;
354 WHILE ( l_index IS NOT NULL )
355 LOOP
356 l_start_date := l_pref_table(l_index).start_date;
357 l_end_date := l_pref_table(l_index).end_date;
358
359 hr_utility.trace(' l_pref_table.start_date ' || l_pref_table(l_index).start_date);
360 hr_utility.trace(' l_pref_table.end_date ' || l_pref_table(l_index).end_date);
361
362 l_rec_period := l_pref_table(l_index).attribute1;
363
364 hr_utility.trace(' l_rec_period ' || l_rec_period);
365 hr_utility.trace(' l_prev_rec_period ' || l_prev_rec_period);
366
367 IF(l_rec_period = l_prev_rec_period) THEN
368 l_pref_end_date := l_end_date;
369 END IF;
370 l_prev_rec_period := l_rec_period;
371 l_index := l_pref_table.NEXT(l_index);
372 END LOOP;
373 SELECT max(effective_end_date) INTO l_tc_end_date
374 FROM per_all_assignments_f
375 WHERE person_id = p_person_id
376 and effective_end_date >= g_tc_start_date
377 and primary_flag = 'Y'
378 and assignment_type in ('E','C');
379
380 SELECT LEAST(l_pref_end_date,l_tc_end_date,l_period_end_date) INTO l_tc_end_date FROM dual;
381
382 IF p_timecard_id IS NOT NULL
383 THEN
384 SELECT stop_time INTO l_tc_stop_date FROM hxc_timecard_summary WHERE timecard_id = p_timecard_id;
385
386 IF l_tc_end_date > l_tc_stop_date
387 THEN
388 l_tc_end_date := l_tc_stop_date;
389 END IF;
390
391 END IF;
392
393 hr_utility.trace(' l_tc_end_date final ' || l_tc_end_date);
394
395 return l_tc_end_date;
396 END get_timecard_end_date;
397
398 PROCEDURE populate_temp_table(p_person_ids IN t_person_ids)
399 IS
400
401 l_sql VARCHAR2(32000);
402
403 TYPE r_tcd_rpt IS RECORD (
404 PERSON_ID NUMBER(15),
405 PERSON_NAME VARCHAR2(100),
406 PERSON_NUMBER NUMBER(15),
407 APPROVAL_STATUS VARCHAR2(20),
408 SUPERVISOR VARCHAR2(100),
409 ORGANIZATION VARCHAR2(100),
410 LOCATION VARCHAR2(100),
411 PAYROLL VARCHAR2(100),
412 TC_START_DATE DATE,
413 TC_END_DATE DATE,
414 LAST_MODIFIED_BY VARCHAR2(100),
415 LAST_MODIFIED_DATE DATE,
416 PERSON_TYPE VARCHAR2(30),
417 APPLICATION VARCHAR2(100)
418 );
419
420 TYPE t_tcd_rpt IS TABLE OF r_tcd_rpt INDEX BY BINARY_INTEGER;
421
422 l_tcd_rpt t_tcd_rpt;
423
424 BEGIN
425
426 hr_utility.trace(' In populate_temp_table ');
427
428 l_sql := 'SELECT temp.*, ppt.user_person_type PERSON_TYPE, hasv.application_set_name APPLICATION
429 FROM
430 (SELECT distinct person_id,
431 person_name,
432 person_number,
433 (NVL(tim.approval_status,''NOTENTERED'')) AS approval_status,
434 supervisor_name,
435 organization,
436 location,
437 payroll,
438 hxc_tcd_xml_pkg.get_timecard_start_date(person_id,tim.timecard_id) AS start_date,
439 hxc_tcd_xml_pkg.get_timecard_end_date(person_id,tim.timecard_id) AS end_date,
440 (SELECT user_name FROM fnd_user
441 WHERE user_id = (SELECT last_updated_by FROM hxc_time_building_blocks
442 WHERE scope = ''TIMECARD''
443 AND resource_id = person_id
444 AND time_building_block_id = tim.timecard_id
445 AND date_to = hr_general.end_of_time)
446 ) AS last_updated_by,
447 (SELECT last_update_date FROM hxc_time_building_blocks
448 WHERE scope = ''TIMECARD''
449 AND resource_id = person_id
450 AND time_building_block_id = tim.timecard_id
451 AND date_to = hr_general.end_of_time
452 ) AS last_update_date
453 FROM HXC_TCD_DETAILS_V v,
454 ((SELECT resource_id, timecard_id, approval_status FROM hxc_timecard_summary
455 WHERE resource_id IN (
456 ';
457 FOR i IN 1..p_person_ids.count
458 LOOP
459 if i > 1
460 then
461 l_sql := l_sql || ',';
462 end if;
463 l_sql := l_sql || p_person_ids(i).person_id;
464
465
466 END LOOP;
467
468 l_sql := l_sql || ') AND trunc(start_time) >= :1 AND trunc(stop_time) <= :2)
469 UNION
470 (SELECT person_id AS
471 resource_id,
472 NULL timecard_id,
473 NULL approval_status
474 FROM per_all_assignments_f paaf
475 WHERE person_id IN(';
476
477 FOR i IN 1..p_person_ids.count
478 LOOP
479 if i > 1
480 then
481 l_sql := l_sql || ',';
482 end if;
483 l_sql := l_sql || p_person_ids(i).person_id;
484
485
486 END LOOP;
487
488 -- Bug 7658500
489 -- Added a new not exists condition for the dynamic sql.
490 -- The sql text originally added to look for a midperiod
491 -- termination so that the timecard would be picked up
492 -- even if the stop_time is less that the period end date.
493 -- This introduced a problem in reverse termination.
494 -- Eg. Emp has to submit timecard for 1-jan-07 to 7-jan-07
495
496
497 -- If he is terminated on 4-Jan-07, Time store lets him enter
498 -- timecard only for 1-jan to 4-jan. Without the below cursor
499 -- the timecard for 1-jan-07 to 7-jan-07 would look appear as
500 -- NOT ENTERED because the stop times dont match.
501 --
502 -- This adds a problem for reverse termination. If the termination
503 -- is reversed as of 5-jan-07, the employee can submit a second timecard
504 -- for the period, 5-jan-07 to 7-jan-07.
505 -- Before this bug fix, the Dashboard and report would pull up
506 -- an extra record for NOT ENTERED status because there is no timecard
507 -- entered for the period end date -- at least that was not being
508 -- checked here. So the below cursor is altered to process this
509 -- termination scenario only if there was no reverse termination
510 -- happening. The smaller timecard EXISTS condition is appended
511 -- with a condition which checks if there is one timecard for
512 -- the rest of the period -- dont pick up a record if there is one
513 -- such timecard.
514
515
516 l_sql := l_sql || ' )
517 AND( EXISTS
518 (SELECT ''Y''
519 FROM hxc_timecard_summary
520 WHERE resource_id = paaf.person_id
521 AND TRUNC(start_time) >= :3
522 AND TRUNC(stop_time) < :4)
523 AND NOT EXISTS
524 (SELECT ''Y''
525 FROM hxc_timecard_summary
526 WHERE resource_id = paaf.person_id
527 AND TRUNC(start_time) > :31
528 AND TRUNC(stop_time) <= :41) )
529 AND EXISTS
530 (SELECT ''Y''
531 FROM per_all_assignments_f
532 WHERE person_id = paaf.person_id
533 AND assignment_type IN(''E'', ''C'')
534 AND primary_flag = ''Y''
535 AND((TRUNC(effective_start_date) <= :5
536 AND TRUNC(effective_end_date) >= :6) OR(TRUNC(effective_start_date) > :7
537 AND TRUNC(effective_start_date) <= :8)))))
538 tim ';
539
540 l_sql := l_sql || ' WHERE v.person_id = tim.resource_id(+) AND v.person_id IN (' ;
541
542
543 FOR i IN 1..p_person_ids.count
544 LOOP
545 if i > 1
546 then
547 l_sql := l_sql || ',';
548 end if;
549 l_sql := l_sql || p_person_ids(i).person_id;
550
551
552 END LOOP;
553
554 l_sql := l_sql || ')';
555
556 -- Bug 7829336
557 -- Added conditions to check for Mid period hire and
558 -- mid period hire and terminate scenario.
559
560
561 -- Bug 8205132
562 -- Removed the above conditions and put up the correct conditions.
563 -- Neednt have all the bind variables too, so removed them.
564
565
566 l_sql := l_sql || 'AND trunc(effective_start_date) <= :9
567 AND trunc(effective_end_date) >= :10';
568
569
570 IF l_sel_tc_status <> 'ALL' THEN
571
572 l_sql := l_sql || 'AND '''|| l_sel_tc_status ||''' = NVL(tim.approval_status,''NOTENTERED'')';
573 END IF;
574
575 IF l_org_id IS NULL OR l_org_id = ''
576 THEN
577 l_org_id := -1;
578 ELSE
579 l_sql := l_sql || ' AND organization_id = :11';
580 END IF;
581
582 IF l_location_id IS NULL OR l_location_id = '' THEN
583 l_location_id := -1;
584 ELSIF l_org_id <> -1 THEN
585 l_sql := l_sql || ' AND location_id = :12';
586 ELSE
587 l_sql := l_sql || ' AND location_id = :11';
588 END IF;
589
590 l_sql := l_sql || ') temp, per_person_types ppt, per_person_type_usages_f ptu,hxc_application_sets_v hasv
591 WHERE hasv.application_set_id =
592 hxc_preference_evaluation.resource_preferences(temp.person_id,''TS_PER_APPLICATION_SET'',1,temp.start_date,-99)
593 AND ppt.person_type_id = ptu.person_type_id
594 AND ptu.person_id = temp.person_id
595 AND trunc(ptu.effective_start_date) <= trunc(temp.start_date)
596 AND trunc(effective_end_date) >= trunc(temp.start_date)';
597
598
599 -- Bug 7658500
600 -- Added a set of start and stop dates because
601 -- the above sql is added with two new bind variables.
602
603 -- Bug 7829336
604 -- Added bind variables to include the changed queries.
605
606
607 -- Bug 8205132
608 -- Less complex query, less bind variables.
609 -- Commented out the unwanted bind variables in
610 -- all the below executions.
611
612 IF l_org_id = -1 AND l_location_id = -1 THEN
613 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_tcd_rpt using
614 l_period_start_date, l_period_end_date, -- :1,:2
615 l_period_start_date, l_period_end_date, -- :3,:4
616 l_period_start_date, l_period_end_date, -- :31,:41
617 l_period_start_date, l_period_end_date, -- :5,:6
618 l_period_start_date, l_period_end_date, -- :7,:8
619 l_period_end_date, l_period_start_date; -- :9,:10
620 /* l_period_start_date, l_period_start_date, -- :9,:10
621 l_period_start_date, l_period_end_date, -- :101,:102
622 l_period_start_date, l_period_start_date, -- :103,:104
623 l_period_end_date ; -- :105
624 */
625 ELSIF l_org_id <> -1 AND l_location_id = -1 THEN
626 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_tcd_rpt using
627 l_period_start_date, l_period_end_date, -- :1,:2
628 l_period_start_date, l_period_end_date, -- :3,:4
629 l_period_start_date, l_period_end_date, -- :31,:41
630 l_period_start_date, l_period_end_date, -- :5,:6
631 l_period_start_date, l_period_end_date, -- :7,:8
632 l_period_end_date, l_period_start_date, -- :9,:10
633 /* l_period_start_date, l_period_start_date, -- :9,:10
634 l_period_start_date, l_period_end_date, -- :101,:102
635 l_period_start_date, l_period_start_date, -- :103,:104
636 l_period_end_date ; -- :105
637 */
638 l_org_id;
639 ELSIF l_org_id = -1 AND l_location_id <> -1 THEN
640 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_tcd_rpt using
641 l_period_start_date, l_period_end_date, -- :1,:2
642 l_period_start_date, l_period_end_date, -- :3,:4
643 l_period_start_date, l_period_end_date, -- :31,:41
644 l_period_start_date, l_period_end_date, -- :5,:6
645 l_period_start_date, l_period_end_date, -- :7,:8
646 l_period_end_date, l_period_start_date, -- :9,:10
647 /* l_period_start_date, l_period_start_date, -- :9,:10
648 l_period_start_date, l_period_end_date, -- :101,:102
649 l_period_start_date, l_period_start_date, -- :103,:104
650 l_period_end_date ; -- :105
651 */
652 l_location_id;
653 ELSE
654 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_tcd_rpt using
655 l_period_start_date, l_period_end_date, -- :1,:2
656 l_period_start_date, l_period_end_date, -- :3,:4
657 l_period_start_date, l_period_end_date, -- :31,:41
658 l_period_start_date, l_period_end_date, -- :5,:6
659 l_period_start_date, l_period_end_date, -- :7,:8
660 l_period_end_date, l_period_start_date, -- :9,:10
661 /* l_period_start_date, l_period_start_date, -- :9,:10
662 l_period_start_date, l_period_end_date, -- :101,:102
663 l_period_start_date, l_period_start_date, -- :103,:104
664 l_period_end_date ; -- :105
665 */
666 l_org_id,l_location_id;
667 END IF;
668
669
670 IF l_sel_supervisor_id IS NULL OR l_sel_supervisor_id = '' THEN
671 l_sel_supervisor_id := -1;
672 END IF;
673
674 FOR i IN l_tcd_rpt.FIRST..l_tcd_rpt.LAST
675 LOOP
676
677 INSERT INTO HXC_TCD_TMP_RPT(
678 RESOURCE_ID ,
679 REC_PERIOD_ID ,
680 PERIOD_START_DATE,
681 PERIOD_END_DATE ,
682 SUPERVISOR_ID ,
683 LOCATION_ID ,
684 ORGANIZATION_ID,
685 SEL_SUPERVISOR_ID,
686 SEL_TC_STATUS ,
687 RPT_STATUS ,
688 PERSON_TYPE ,
689 PERSON_NAME ,
690 PERSON_NUMBER ,
691 APPROVAL_STATUS,
692 SUPERVISOR ,
693 ORGANIZATION ,
694 LOCATION ,
695 PAYROLL ,
696 APPLICATION ,
697 TC_START_DATE ,
698 TC_END_DATE ,
699 LAST_MODIFIED_BY,
700 LAST_MODIFIED_DATE
701 )
702 VALUES(l_resource_id,
703 l_rec_period_id,
704 l_period_start_date,
705 l_period_end_date,
706 l_supervisor_id,
707 l_location_id,
708 l_org_id,
709 l_sel_supervisor_id,
710 l_sel_tc_status,
711 l_rpt_status,
712 l_tcd_rpt(i).PERSON_TYPE,
713 l_tcd_rpt(i).PERSON_NAME,
714 l_tcd_rpt(i).PERSON_NUMBER,
715 l_tcd_rpt(i).APPROVAL_STATUS,
716 l_tcd_rpt(i).SUPERVISOR,
717 l_tcd_rpt(i).ORGANIZATION,
718 l_tcd_rpt(i).LOCATION,
719 l_tcd_rpt(i).PAYROLL,
720 l_tcd_rpt(i).APPLICATION,
721 l_tcd_rpt(i).TC_START_DATE,
722 l_tcd_rpt(i).TC_END_DATE,
723 l_tcd_rpt(i).LAST_MODIFIED_BY,
724 l_tcd_rpt(i).LAST_MODIFIED_DATE);
725 END LOOP;
726 commit;
727
728 END populate_temp_table;
729
730 PROCEDURE populate_report_table
731 IS
732
733 l_supervisor varchar2(20);
734
735 l_person_ids t_person_ids;
736 l_filtered_person_ids t_person_ids;
737
738 l_directsSQL varchar2(2000);
739 l_allEmpSQL varchar2(2000);
740
741 BEGIN
742
743 IF (l_sel_supervisor_id is not null)
744 THEN
745 -- Selected a link from dashboard summary
746 l_supervisor := l_sel_supervisor_id;
747 ELSE
748 -- Selected a link from dashboard summary totals
749 l_supervisor := l_supervisor_id;
750 END IF;
751
752 IF (l_reptng_emp = 'DIRECT_REPORTEES' OR
753 l_sel_supervisor_id = l_supervisor_id)
754 THEN
755
756
757
758 -- Bug 8205132
759 -- Commented out the below sql and rewrote with a more
760 -- correct WHERE clause to trim out unwanted records.
761
762 /*
763 -- Bug 7829336
764 -- Added condition to check for Mid period hire
765 -- and mid period hire and termination.
766 -- Prior to this bug, the date check did not cover these aspects.
767
768
769
770 l_directsSQL := 'SELECT DISTINCT person_id
771 FROM per_all_assignments_f paaf
772 WHERE assignment_type IN (''E'', ''C'')
773 AND primary_flag = ''Y''
774 AND supervisor_id = :1
775 AND (
776 ( trunc(effective_start_date) <= :2
777 AND trunc(effective_end_date) >= :3
778 )
779 OR
780 ( trunc(effective_start_date) > :4
781 AND trunc(effective_end_date) >= :5
782 AND trunc(effective_end_date) > :6
783 )
784 OR
785 ( trunc(effective_start_date) > :7
786 AND trunc(effective_end_date) < :8
787 )
788 ) ';
789
790 */
791
792 l_directsSQL := 'SELECT DISTINCT person_id
793 FROM per_all_assignments_f paaf
794 WHERE assignment_type IN (''E'', ''C'')
795 AND primary_flag = ''Y''
796 AND supervisor_id = :1
797 AND trunc(effective_start_date) <= :2
798 AND trunc(effective_end_date) >= :3 ';
799
800 IF l_location_id IS NOT null
801 THEN
802 l_directsSQL := l_directsSQL || ' AND location_id = ' || l_location_id;
803 END IF;
804
805 IF l_org_id IS NOT null
806 THEN
807 l_directsSQL := l_directsSQL || ' AND organization_id = ' || l_org_id;
808 END IF;
809
810 l_directsSQL := l_directsSQL || ' ORDER BY 1 ';
811
812 hr_utility.trace(' l_directsSQL ' || l_directsSQL);
813
814
815 -- Bug 8205132
816 -- New query, and less bind variables.
817 -- Rewrote the Execute Immediate below.
818 /*
819 -- Bug 7829336
820 -- Added bind variables to include the changed queries.
821
822 EXECUTE IMMEDIATE l_directsSQL BULK COLLECT INTO l_person_ids USING
823 l_supervisor,l_period_start_date,l_period_start_date, -- :1,:2,:3
824 l_period_start_date,l_period_end_date,l_period_start_date, -- :4,:5,:6
825 l_period_start_date,l_period_end_date; -- :7,:8
826
827 */
828
829 EXECUTE IMMEDIATE l_directsSQL BULK COLLECT INTO l_person_ids USING
830 l_supervisor,l_period_end_date,l_period_start_date ;
831
832
833 ELSE
834 l_allEmpSQL := 'SELECT distinct person_id
835 FROM per_all_assignments_f asgn
836 WHERE person_id <> :1
837 AND primary_flag = ''Y''
838 AND assignment_type in (''E'',''C'')';
839
840 IF l_location_id IS NOT NULL
841 THEN
842 l_allEmpSQL := l_allEmpSQL || ' AND location_id = ' || l_location_id;
843 END IF;
844
845 IF l_org_id IS NOT NULL
846 THEN
847 l_allEmpSQL := l_allEmpSQL || ' AND organization_id = ' || l_org_id;
848 END IF;
849
850
851 -- Bug 8205132
852 -- Rewrote the below SQL in line with the directs sql
853 -- to avoid unwanted records.
854 -- New query, less bind variables, hence rewrote
855 -- the Execute Immediate also.
856 /*
857 -- Bug 7829336
858 -- Added condition to check for Mid period hire
859 -- and mid period hire and termination.
860 -- Prior to this bug, the date check did not cover these aspects.
861
862 l_allEmpSQL := l_allEmpSQL ||
863 ' CONNECT BY PRIOR person_id = supervisor_id
864 AND ( ( trunc(effective_start_date) <= :2
865 AND trunc(effective_end_date) >= :3
866 )
867 OR
868 ( trunc(effective_start_date) > :31
869 AND trunc(effective_end_date) >= :5
870 AND trunc(effective_end_date) > :6
871 )
872 OR
873 ( trunc(effective_start_date) > :7
874 AND trunc(effective_end_date) < :8
875 )
876 )
877 START WITH person_id = :4
878 AND trunc(effective_start_date) <= trunc(sysdate)
879 AND trunc(effective_end_date) >= trunc(sysdate)
880 ORDER BY 1';
881
882
883 -- Bug 7829336
884 -- Added bind variables to include the changed queries.
885
886 EXECUTE IMMEDIATE l_allEmpSQL
887 BULK COLLECT
888 INTO l_person_ids
889 USING l_supervisor, l_period_start_date, l_period_start_date, -- :1,:2,:3
890 l_period_start_date,l_period_end_date,l_period_start_date, -- :31,:5,:6
891 l_period_start_date,l_period_end_date,l_supervisor; -- :7,:8,:4
892
893 */
894
895
896 l_allEmpSQL := l_allEmpSQL ||
897 ' CONNECT BY PRIOR person_id = supervisor_id
898 AND trunc(effective_start_date) <= :2
899 AND trunc(effective_end_date) >= :3
900 START WITH person_id = :4
901 AND trunc(effective_start_date) <= :5
902 AND trunc(effective_end_date) >= :6
903 ORDER BY 1';
904
905
906 EXECUTE IMMEDIATE l_allEmpSQL
907 BULK COLLECT
908 INTO l_person_ids
909 USING l_supervisor, l_period_end_date, l_period_start_date,
910 l_supervisor,
911 l_period_end_date, l_period_start_date;
912
913
914 END IF;
915
916 filter_person_ids(p_person_ids => l_person_ids,
917 p_filtered_person_ids => l_filtered_person_ids);
918
919 populate_temp_table(l_filtered_person_ids);
920
921
922 END populate_report_table;
923
924 PROCEDURE clear_report_table
925 IS
926
927 l_sql varchar2(1000);
928
929 BEGIN
930 l_sql := 'DELETE FROM HXC_TCD_TMP_RPT
931 WHERE RESOURCE_ID = :1
932 AND REC_PERIOD_ID = :2
933 AND PERIOD_START_DATE = :3
934 AND PERIOD_END_DATE = :4
935 AND SUPERVISOR_ID = :5
936 AND LOCATION_ID = :6
937 AND ORGANIZATION_ID = :7
938 AND SEL_SUPERVISOR_ID = :8
939 AND SEL_TC_STATUS = :9
940 AND RPT_STATUS = :10';
941
942 EXECUTE IMMEDIATE l_sql USING l_resource_id, l_rec_period_id, l_period_start_date, l_period_end_date,
943 l_supervisor_id, l_location_id, l_org_id, l_sel_supervisor_id, l_sel_tc_status, l_rpt_status;
944
945 END clear_report_table;
946
947 END HXC_TCD_XML_PKG ;
[Home] [Help]