1 PACKAGE BODY HXC_TCD_XML_PKG AS
2 /* $Header: hxctcdrpt.pkb 120.21 2011/02/17 09:01:49 pnshukla 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 VARCHAR2(30),
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 AND ppt.system_person_type in (''EMP'', ''EMP_APL'', ''EX_EMP'', ''EX_EMP_APL'', ''CWK'', ''EX_CWK'')';
598
599
600 -- Bug 7658500
601 -- Added a set of start and stop dates because
602 -- the above sql is added with two new bind variables.
603
604 -- Bug 7829336
605 -- Added bind variables to include the changed queries.
606
607
608 -- Bug 8205132
609 -- Less complex query, less bind variables.
610 -- Commented out the unwanted bind variables in
611 -- all the below executions.
612
613 IF l_org_id = -1 AND l_location_id = -1 THEN
614 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_tcd_rpt using
615 l_period_start_date, l_period_end_date, -- :1,:2
616 l_period_start_date, l_period_end_date, -- :3,:4
617 l_period_start_date, l_period_end_date, -- :31,:41
618 l_period_start_date, l_period_end_date, -- :5,:6
619 l_period_start_date, l_period_end_date, -- :7,:8
620 l_period_end_date, l_period_start_date; -- :9,:10
621 /* l_period_start_date, l_period_start_date, -- :9,:10
622 l_period_start_date, l_period_end_date, -- :101,:102
623 l_period_start_date, l_period_start_date, -- :103,:104
624 l_period_end_date ; -- :105
625 */
626 ELSIF l_org_id <> -1 AND l_location_id = -1 THEN
627 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_tcd_rpt using
628 l_period_start_date, l_period_end_date, -- :1,:2
629 l_period_start_date, l_period_end_date, -- :3,:4
630 l_period_start_date, l_period_end_date, -- :31,:41
631 l_period_start_date, l_period_end_date, -- :5,:6
632 l_period_start_date, l_period_end_date, -- :7,:8
633 l_period_end_date, l_period_start_date, -- :9,:10
634 /* l_period_start_date, l_period_start_date, -- :9,:10
635 l_period_start_date, l_period_end_date, -- :101,:102
636 l_period_start_date, l_period_start_date, -- :103,:104
637 l_period_end_date ; -- :105
638 */
639 l_org_id;
640 ELSIF l_org_id = -1 AND l_location_id <> -1 THEN
641 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_tcd_rpt using
642 l_period_start_date, l_period_end_date, -- :1,:2
643 l_period_start_date, l_period_end_date, -- :3,:4
644 l_period_start_date, l_period_end_date, -- :31,:41
645 l_period_start_date, l_period_end_date, -- :5,:6
646 l_period_start_date, l_period_end_date, -- :7,:8
647 l_period_end_date, l_period_start_date, -- :9,:10
648 /* l_period_start_date, l_period_start_date, -- :9,:10
649 l_period_start_date, l_period_end_date, -- :101,:102
650 l_period_start_date, l_period_start_date, -- :103,:104
651 l_period_end_date ; -- :105
652 */
653 l_location_id;
654 ELSE
655 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_tcd_rpt using
656 l_period_start_date, l_period_end_date, -- :1,:2
657 l_period_start_date, l_period_end_date, -- :3,:4
658 l_period_start_date, l_period_end_date, -- :31,:41
659 l_period_start_date, l_period_end_date, -- :5,:6
660 l_period_start_date, l_period_end_date, -- :7,:8
661 l_period_end_date, l_period_start_date, -- :9,:10
662 /* l_period_start_date, l_period_start_date, -- :9,:10
663 l_period_start_date, l_period_end_date, -- :101,:102
664 l_period_start_date, l_period_start_date, -- :103,:104
665 l_period_end_date ; -- :105
666 */
667 l_org_id,l_location_id;
668 END IF;
669
670
671 IF l_sel_supervisor_id IS NULL OR l_sel_supervisor_id = '' THEN
672 l_sel_supervisor_id := -1;
673 END IF;
674
675 FOR i IN l_tcd_rpt.FIRST..l_tcd_rpt.LAST
676 LOOP
677
678 INSERT INTO HXC_TCD_TMP_RPT(
679 RESOURCE_ID ,
680 REC_PERIOD_ID ,
681 PERIOD_START_DATE,
682 PERIOD_END_DATE ,
683 SUPERVISOR_ID ,
684 LOCATION_ID ,
685 ORGANIZATION_ID,
686 SEL_SUPERVISOR_ID,
687 SEL_TC_STATUS ,
688 RPT_STATUS ,
689 PERSON_TYPE ,
690 PERSON_NAME ,
691 PERSON_NUMBER ,
692 APPROVAL_STATUS,
693 SUPERVISOR ,
694 ORGANIZATION ,
695 LOCATION ,
696 PAYROLL ,
697 APPLICATION ,
698 TC_START_DATE ,
699 TC_END_DATE ,
700 LAST_MODIFIED_BY,
701 LAST_MODIFIED_DATE
702 )
703 VALUES(l_resource_id,
704 l_rec_period_id,
705 l_period_start_date,
706 l_period_end_date,
707 l_supervisor_id,
708 l_location_id,
709 l_org_id,
710 l_sel_supervisor_id,
711 l_sel_tc_status,
712 l_rpt_status,
713 l_tcd_rpt(i).PERSON_TYPE,
714 l_tcd_rpt(i).PERSON_NAME,
715 l_tcd_rpt(i).PERSON_NUMBER,
716 l_tcd_rpt(i).APPROVAL_STATUS,
717 l_tcd_rpt(i).SUPERVISOR,
718 l_tcd_rpt(i).ORGANIZATION,
719 l_tcd_rpt(i).LOCATION,
720 l_tcd_rpt(i).PAYROLL,
721 l_tcd_rpt(i).APPLICATION,
722 l_tcd_rpt(i).TC_START_DATE,
723 l_tcd_rpt(i).TC_END_DATE,
724 l_tcd_rpt(i).LAST_MODIFIED_BY,
725 l_tcd_rpt(i).LAST_MODIFIED_DATE);
726 END LOOP;
727 commit;
728
729 END populate_temp_table;
730
731 PROCEDURE populate_report_table
732 IS
733
734 l_supervisor varchar2(20);
735
736 l_person_ids t_person_ids;
737 l_filtered_person_ids t_person_ids;
738
739 l_directsSQL varchar2(2000);
740 l_allEmpSQL varchar2(2000);
741
742 BEGIN
743
744 IF (l_sel_supervisor_id is not null)
745 THEN
746 -- Selected a link from dashboard summary
747 l_supervisor := l_sel_supervisor_id;
748 ELSE
749 -- Selected a link from dashboard summary totals
750 l_supervisor := l_supervisor_id;
751 END IF;
752
753 IF (l_reptng_emp = 'DIRECT_REPORTEES' OR
754 l_sel_supervisor_id = l_supervisor_id)
755 THEN
756
757
758
759 -- Bug 8205132
760 -- Commented out the below sql and rewrote with a more
761 -- correct WHERE clause to trim out unwanted records.
762
763 /*
764 -- Bug 7829336
765 -- Added condition to check for Mid period hire
766 -- and mid period hire and termination.
767 -- Prior to this bug, the date check did not cover these aspects.
768
769
770
771 l_directsSQL := 'SELECT DISTINCT person_id
772 FROM per_all_assignments_f paaf
773 WHERE assignment_type IN (''E'', ''C'')
774 AND primary_flag = ''Y''
775 AND supervisor_id = :1
776 AND (
777 ( trunc(effective_start_date) <= :2
778 AND trunc(effective_end_date) >= :3
779 )
780 OR
781 ( trunc(effective_start_date) > :4
782 AND trunc(effective_end_date) >= :5
783 AND trunc(effective_end_date) > :6
784 )
785 OR
786 ( trunc(effective_start_date) > :7
787 AND trunc(effective_end_date) < :8
788 )
789 ) ';
790
791 */
792
793 l_directsSQL := 'SELECT DISTINCT person_id
794 FROM per_all_assignments_f paaf
795 WHERE assignment_type IN (''E'', ''C'')
796 AND primary_flag = ''Y''
797 AND supervisor_id = :1
798 AND trunc(effective_start_date) <= :2
799 AND trunc(effective_end_date) >= :3 ';
800
801 IF l_location_id IS NOT null
802 THEN
803 l_directsSQL := l_directsSQL || ' AND location_id = ' || l_location_id;
804 END IF;
805
806 IF l_org_id IS NOT null
807 THEN
808 l_directsSQL := l_directsSQL || ' AND organization_id = ' || l_org_id;
809 END IF;
810
811 l_directsSQL := l_directsSQL || ' ORDER BY 1 ';
812
813 hr_utility.trace(' l_directsSQL ' || l_directsSQL);
814
815
816 -- Bug 8205132
817 -- New query, and less bind variables.
818 -- Rewrote the Execute Immediate below.
819 /*
820 -- Bug 7829336
821 -- Added bind variables to include the changed queries.
822
823 EXECUTE IMMEDIATE l_directsSQL BULK COLLECT INTO l_person_ids USING
824 l_supervisor,l_period_start_date,l_period_start_date, -- :1,:2,:3
825 l_period_start_date,l_period_end_date,l_period_start_date, -- :4,:5,:6
826 l_period_start_date,l_period_end_date; -- :7,:8
827
828 */
829
830 EXECUTE IMMEDIATE l_directsSQL BULK COLLECT INTO l_person_ids USING
831 l_supervisor,l_period_end_date,l_period_start_date ;
832
833
834 ELSE
835 l_allEmpSQL := 'SELECT distinct person_id
836 FROM per_all_assignments_f asgn
837 WHERE person_id <> :1
838 AND primary_flag = ''Y''
839 AND assignment_type in (''E'',''C'')';
840
841 IF l_location_id IS NOT NULL
842 THEN
843 l_allEmpSQL := l_allEmpSQL || ' AND location_id = ' || l_location_id;
844 END IF;
845
846 IF l_org_id IS NOT NULL
847 THEN
848 l_allEmpSQL := l_allEmpSQL || ' AND organization_id = ' || l_org_id;
849 END IF;
850
851
852 -- Bug 8205132
853 -- Rewrote the below SQL in line with the directs sql
854 -- to avoid unwanted records.
855 -- New query, less bind variables, hence rewrote
856 -- the Execute Immediate also.
857 /*
858 -- Bug 7829336
859 -- Added condition to check for Mid period hire
860 -- and mid period hire and termination.
861 -- Prior to this bug, the date check did not cover these aspects.
862
863 l_allEmpSQL := l_allEmpSQL ||
864 ' CONNECT BY PRIOR person_id = supervisor_id
865 AND ( ( trunc(effective_start_date) <= :2
866 AND trunc(effective_end_date) >= :3
867 )
868 OR
869 ( trunc(effective_start_date) > :31
870 AND trunc(effective_end_date) >= :5
871 AND trunc(effective_end_date) > :6
872 )
873 OR
874 ( trunc(effective_start_date) > :7
875 AND trunc(effective_end_date) < :8
876 )
877 )
878 START WITH person_id = :4
879 AND trunc(effective_start_date) <= trunc(sysdate)
880 AND trunc(effective_end_date) >= trunc(sysdate)
881 ORDER BY 1';
882
883
884 -- Bug 7829336
885 -- Added bind variables to include the changed queries.
886
887 EXECUTE IMMEDIATE l_allEmpSQL
888 BULK COLLECT
889 INTO l_person_ids
890 USING l_supervisor, l_period_start_date, l_period_start_date, -- :1,:2,:3
891 l_period_start_date,l_period_end_date,l_period_start_date, -- :31,:5,:6
892 l_period_start_date,l_period_end_date,l_supervisor; -- :7,:8,:4
893
894 */
895
896
897 l_allEmpSQL := l_allEmpSQL ||
898 ' CONNECT BY PRIOR person_id = supervisor_id
899 AND trunc(effective_start_date) <= :2
900 AND trunc(effective_end_date) >= :3
901 START WITH person_id = :4
902 AND trunc(effective_start_date) <= :5
903 AND trunc(effective_end_date) >= :6
904 ORDER BY 1';
905
906
907 EXECUTE IMMEDIATE l_allEmpSQL
908 BULK COLLECT
909 INTO l_person_ids
910 USING l_supervisor, l_period_end_date, l_period_start_date,
911 l_supervisor,
912 l_period_end_date, l_period_start_date;
913
914
915 END IF;
916
917 filter_person_ids(p_person_ids => l_person_ids,
918 p_filtered_person_ids => l_filtered_person_ids);
919
920 populate_temp_table(l_filtered_person_ids);
921
922
923 END populate_report_table;
924
925 PROCEDURE clear_report_table
926 IS
927
928 l_sql varchar2(1000);
929
930 BEGIN
931 l_sql := 'DELETE FROM HXC_TCD_TMP_RPT
932 WHERE RESOURCE_ID = :1
933 AND REC_PERIOD_ID = :2
934 AND PERIOD_START_DATE = :3
935 AND PERIOD_END_DATE = :4
936 AND SUPERVISOR_ID = :5
937 AND LOCATION_ID = :6
938 AND ORGANIZATION_ID = :7
939 AND SEL_SUPERVISOR_ID = :8
940 AND SEL_TC_STATUS = :9
941 AND RPT_STATUS = :10';
942
943 EXECUTE IMMEDIATE l_sql USING l_resource_id, l_rec_period_id, l_period_start_date, l_period_end_date,
944 l_supervisor_id, l_location_id, l_org_id, l_sel_supervisor_id, l_sel_tc_status, l_rpt_status;
945
946 END clear_report_table;
947
948 END HXC_TCD_XML_PKG ;
[Home] [Help]