[Home] [Help]
PACKAGE BODY: APPS.HXC_SUPERVISOR_DASHBOARD
Source
1 package body HXC_SUPERVISOR_DASHBOARD AS
2 /* $Header: hxcsupdash.pkb 120.0.12020000.7 2013/03/13 06:56:33 pravesk noship $ */
3
4 g_package varchar2(33) := ' hxc_supervisor_dashboard.';
5 g_debug BOOLEAN := hr_utility.debug_enabled;
6
7 /*
8 Procedure which will dump data into temporary table
9 hxc_tcd_timecards and hxc_tcd_status_count.
10 */
11 PROCEDURE go(p_recurring_period IN varchar2 default Null,
12 p_supervisor_id IN Number,
13 p_reptng_emp IN varchar2,
14 p_location_id IN Number,
15 p_organization_id IN Number,
16 p_start_date IN Date,
17 p_end_date IN Date,
18 p_msg OUT NOCOPY VARCHAR2,
19 p_level OUT NOCOPY VARCHAR2
20 )
21 AS
22
23 l_proc varchar2(72);
24 l_person_det persondet;
25 l_eval_date date;
26 l_timecards_ne timecards;
27 l_timecard timecardsnotexist;
28 l_person_ids NUMBERTAB;
29
30 resourcetab NUMBERTAB;
31 starttimetab DATETAB;
32 stoptimetab DATETAB;
33 timecardtab NUMBERTAB;
34 ovntab NUMBERTAB;
35 nextstarttimetab DATETAB;
36 nextstoptimetab DATETAB;
37 approvalstatustab VARCHARTAB;
38 firststarttimetab DATETAB;
39 firststoptimetab DATETAB;
40
41
42 resourcetabtrunc NUMBERTAB;
43 starttimetabtrunc DATETAB;
44 stoptimetabtrunc DATETAB;
45 firststarttimetabtrunc DATETAB;
46 firststoptimetabtrunc DATETAB;
47 nextstarttimetabtrunc DATETAB;
48 nextstoptimetabtrunc DATETAB;
49 approvalstatustabtrunc VARCHARTAB;
50
51 l_next_start_time DATETAB;
52 l_next_stop_time DATETAB;
53 l_count number;
54
55 l_recurring_period VARCHARTAB;
56
57 l_recc_pref varchar2(10);
58 l_user_id number := FND_GLOBAL.login_id;
59 l_direct_cursor SYS_REFCURSOR;
60 l_all_cursor SYS_REFCURSOR;
61
62 l_org_id varchar2(100) := p_organization_id;
63 l_location_id varchar2(100) := p_location_id;
64 l_ref_cursor SYS_REFCURSOR;
65 l_timecards timecards;
66 l_flag varchar2(1) := 'y';
67
68 l_recurring_period_id number;
69 l_period_type varchar2(30);
70 l_number_per_fiscal_year number(15,0);
71 l_start_date date;
72 l_day number;
73 l_duration_in_days number;
74 l_start_date_day number;
75 l_count_day number;
76
77 l_change_start_date date;
78
79 l_period_start_date VARCHARTAB;
80 l_period_end_date VARCHARTAB;
81 l_application_set_id number;
82
83 l_resource_ids NUMBERTAB;
84
85
86 l_temp number;
87
88 pref_table hxc_preference_evaluation.t_pref_table;
89
90 l_direct_sql VARCHAR2(32000) :=
91 'SELECT
92 asg.person_id,
93 asg.payroll_id,
94 asg.location_id,
95 asg.supervisor_id,
96 asg.organization_id,
97 greatest(asg.EFFECTIVE_START_DATE,:1),
98 least(asg.EFFECTIVE_END_DATE,:2),
99 1,
100 1,
101 asg.EFFECTIVE_START_DATE,
102 asg.EFFECTIVE_END_DATE
103 FROM
104 per_assignments_f asg
105 , per_assignment_status_types past
106 WHERE asg.supervisor_id = :3
107 AND asg.assignment_type IN ( ''E'' , ''C'' )
108 AND asg.primary_flag = ''Y''
109 AND trunc(asg.effective_start_date) <= :4
110 AND trunc(asg.effective_end_date) >= :5
111 AND past.assignment_status_type_id = asg.assignment_status_type_id
112 AND past.per_system_status IN (''ACTIVE_ASSIGN'', ''ACTIVE_CWK'')';
113
114 l_all_direct_sql VARCHAR2(32000) := 'SELECT
115 DISTINCT
116 perasn.person_id,
117 perasn.payroll_id,
118 perasn.location_id,
119 perasn.supervisor_id,
120 perasn.organization_id,
121 greatest(perasn.EFFECTIVE_START_DATE,:1),
122 least(perasn.EFFECTIVE_END_DATE,:2) ,
123 1,
124 1,
125 perasn.EFFECTIVE_START_DATE,
126 perasn.EFFECTIVE_END_DATE
127 FROM
128 per_assignments_f perasn
129 , per_assignment_status_types past
130 WHERE perasn.person_id <> :3
131 AND perasn.primary_flag = ''Y''
132 AND assignment_type IN (''E'' , ''C'' )
133 START WITH person_id = :4
134 AND trunc(effective_start_date) <= :5
135 AND trunc(effective_end_date) >= :6
136 CONNECT BY PRIOR person_id = supervisor_id
137 AND ( ( trunc(effective_start_date) <= :7
138 AND trunc(effective_end_date) >= :8 ) )
139 AND past.assignment_status_type_id = perasn.assignment_status_type_id
140 AND past.per_system_status IN (''ACTIVE_ASSIGN'', ''ACTIVE_CWK'')';
141
142
143 l_sql varchar2(32000) DEFAULT 'SELECT
144 tim.resource_id,
145 tim.timecard_id,
146 tim.timecard_ovn,
147 tim.start_time,
148 tim.stop_time,
149 DECODE((trunc(LEAD(tim.start_time,1,NULL) over (partition by tim.resource_id Order by tim.stop_time))-1 - trunc(tim.stop_time)),0,NULL,tim.stop_time + 1),
150 DECODE((trunc(LEAD(tim.start_time,1,NULL) over (partition by tim.resource_id Order by tim.stop_time))-1 - trunc(tim.stop_time)),0,
151 NULL,LEAD(tim.start_time,1,NULL) over (partition by tim.resource_id Order by tim.stop_time) -1),
152 tim.approval_status
153
154 FROM
155 hxc_timecard_summary tim, hxc_temp_tcd temp
156 WHERE tim.resource_id = temp.resource_id
157 tim.stop_time >= temp.start_time
158 and tim.start_time <= temp.stop_time';
159
160 l_sql1 varchar2(3200);
161
162 l_sql2 varchar2(500) :=
163 ' select resource_id, start_time,
164 stop_time, approval_status,
165 timecard_exist from HXC_TEMP_TCD';
166
167 connect_by_detected EXCEPTION;
168 PRAGMA EXCEPTION_INIT(connect_by_detected, -1436);
169
170 CURSOR c_get_timecard
171 (p_user_id IN number) IS
172 SELECT tim.resource_id
173 , tim.timecard_id
174 , tim.timecard_ovn
175 , tim.start_time
176 , tim.stop_time
177 , decode (lag (tim.start_time) OVER (PARTITION BY tim.resource_id
178 , temp.period_exist
179 ORDER BY tim.stop_time) , NULL
180 , decode (abs (tim.start_time - temp.start_time), tim.start_time - temp.start_time
181 , temp.start_time), NULL) first_start
182 , decode (lag (tim.start_time) OVER (PARTITION BY tim.resource_id
183 , temp.period_exist
184 ORDER BY tim.stop_time) , NULL
185 , decode (abs (tim.start_time - temp.start_time), tim.start_time - temp.start_time
186 , tim.start_time - 1), NULL) first_stop
187 , decode ((trunc (lead (tim.start_time) OVER (PARTITION BY tim.resource_id
188 , temp.period_exist
189 ORDER BY tim.stop_time) ) - 1 - trunc (tim.stop_time)), 0
190 , NULL, tim.stop_time + 1) next_start
191 , decode ((trunc (lead (tim.start_time) OVER (PARTITION BY tim.resource_id
192 , temp.period_exist
193 ORDER BY tim.stop_time) ) - 1 - trunc (tim.stop_time)), 0
194 , NULL, nvl ((lead (tim.start_time, 1
195 , NULL) OVER (PARTITION BY tim.resource_id
196 , temp.period_exist
197 ORDER BY tim.stop_time) - 1), temp.stop_time)) next_end
198 , tim.approval_status
199 , temp.recurring_period_id
200 , temp.person_number
201 , temp.full_name
202 , temp.payroll_name
203 , temp.organization_name
204 , temp.location_name
205 , temp.supervisor_id
206 , temp.supervisor_name
207 , temp.application
208 , decode (temp.period_exist, 'T'
209 , 'P', temp.period_exist)
210 , temp.start_time
211 , temp.stop_time
212 FROM hxc_timecard_summary tim
213 , hxc_temp_tcd temp
214 WHERE tim.stop_time >= temp.start_time
215 AND tim.start_time <= temp.stop_time
216 AND temp.resource_id = tim.resource_id
217 AND temp.user_id = p_user_id;
218
219 CURSOR c_get_periods
220 (p_number_per_fiscal_year IN number
221 , p_start_date IN date
222 , p_end_date IN date
223 , p_year_days IN number) IS
224 SELECT trunc (((p_start_date - 1) + (p_year_days / p_number_per_fiscal_year) * (level - 1)) + 1)
225 , trunc (((p_start_date - 1) + ((p_year_days / p_number_per_fiscal_year) * (level - 1))) + (p_year_days / p_number_per_fiscal_year))
226 FROM dual
227 WHERE trunc (((p_start_date - 1) + ((p_year_days / p_number_per_fiscal_year) * (level - 1))) + (p_year_days / p_number_per_fiscal_year)) <= to_date (p_end_date, fnd_profile.value ('ICX_DATE_FORMAT_MASK'))
228 CONNECT BY level <= (p_number_per_fiscal_year + 1);
229
230 CURSOR c_get_monthly_periods(p_start_date DATE, p_end_date DATE)
231 IS
232 SELECT add_months (p_start_date, (level - 1)) start_date
233 , (add_months (p_start_date, level) - 1) end_date
234 FROM dual
235 WHERE (add_months (p_start_date, level) - 1) <= p_end_date
236 CONNECT BY level <= ceil (months_between (p_end_date, p_start_date));
237
238 CURSOR c_get_yearly_periods(p_start_date DATE, p_end_date DATE)
239 is
240 SELECT add_months (p_start_date, ((level - 1)*12)) start_date
241 , (add_months (p_start_date, (level*12)) - 1) end_date
242 FROM dual
243 WHERE (add_months (p_start_date, (level*12)) - 1) <= p_end_date
244 CONNECT BY level <= (ceil (months_between (p_end_date, p_start_date))/12);
245
246 CURSOR c_get_quarterly_periods(p_start_date DATE, p_end_date DATE)
247 is
248 SELECT add_months (p_start_date, ((level - 1)*12)) start_date
249 , (add_months (p_start_date, (level*12)) - 1) end_date
250 FROM dual
251 WHERE (add_months (p_start_date, (level*12)) - 1) <= p_end_date
252 CONNECT BY level <= (ceil (months_between (p_end_date, p_start_date))/12);
253
254 --change the query a bit
255
256 CURSOR c_get_duration_periods(p_duration_in_days number, p_start_date date, p_end_date date)
257 IS
258 SELECT
259 trunc(((p_start_date - 1) + p_duration_in_days * (level - 1)) + 1) startdate,
260 trunc(((p_start_date - 1) + (p_duration_in_days * (level - 1))) + p_duration_in_days) enddate
261 FROM
262 dual
263 WHERE trunc(((p_start_date - 1) + (p_duration_in_days * (level - 1))) + p_duration_in_days) <= p_end_date
264 CONNECT BY level <= trunc(p_end_date - p_start_date + 1) / (p_duration_in_days);
265
266
267 CURSOR c_get_last_modified(p_timecard_id number, p_timecard_ovn number)
268 IS
269 SELECT
270 last_update_login,
271 last_update_date
272 FROM
273 hxc_time_building_blocks
274 WHERE scope = 'TIMECARD'
275 AND time_building_block_id = p_timecard_id
276 AND object_version_number = p_timecard_ovn
277 AND date_to = hr_general.end_of_time;
278
279 /*
280 Delete extra records from GTT hxc_temp_tcd.
281 */
282
283
284
285 PROCEDURE delete_temp_extra_rec(p_user_id number)
286 AS
287 BEGIN
288
289 /**
290 * Initially, when the data is populated with the global start_time and stop_time (the search criteria)
291 * the period exist flag will be 'T'. If a person is terminated mid-period then the flag is 'M' and if
292 * the person is hired (or) rehired then the flag will be 'R'
293 *
294 * However, when the start_time and stop_time in the table are
295 * adjusted based on if a timecard exists between the search criteria (period_exist flag is 'P' or 'p') or
296 * if the person is terminated mid-period (period_exist flag is 'm') or if the person is hired or re-hired
297 * mid-period (period_exist flag is 'r') a new row is inserted with the corresponding flag.
298 *
299 * Since, hire, rehire and mid-period termination within the search criteria is already captured in the table
300 * this detail must be preserved after the period generation. Hence the duplicate values are deleted here.
301 */
302
303 IF g_debug THEN
304 hr_utility.trace('deleting temp extra records');
305 END IF;
306
307 DELETE FROM hxc_temp_tcd
308 WHERE resource_id in (SELECT distinct resource_id FROM hxc_temp_tcd WHERE period_exist = 'P' or period_exist = 'p')
309 AND period_exist = 'T';
310
311 DELETE FROM hxc_temp_tcd
312 WHERE resource_id in (SELECT distinct resource_id FROM hxc_temp_tcd WHERE period_exist = 'm')
313 AND period_exist = 'M';
314
315 DELETE FROM hxc_temp_tcd
316 WHERE resource_id in (SELECT distinct resource_id FROM hxc_temp_tcd WHERE period_exist = 'r')
317 AND period_exist = 'R';
318
319 IF g_debug THEN
320 hr_utility.trace('deleting temp extra records completed');
321 END IF;
322
323 END delete_temp_extra_rec;
324
325
326 PROCEDURE delete_extra_rec(p_user_id number)
327 AS
328 BEGIN
329
330 IF g_debug THEN
331 l_proc := g_package||'delete_extra_rec';
332 hr_utility.set_location('Entering:'|| l_proc, 10);
333 END IF;
334
335 DELETE
336 FROM HXC_TCD_TIMECARDS htt
337 WHERE EXISTS
338 (
339 SELECT 1
340 FROM HXC_TCD_TIMECARDS htt1
341 WHERE htt1.resource_id = htt.resource_id
342 AND trunc (htt1.period_start_date) = trunc (htt.period_start_date)
343 AND htt1.approval_status <> htt.approval_status
344 AND htt.approval_status = 'NOTENTERED'
345 AND htt1.user_id = p_user_id
346 AND htt1.user_id = htt.user_id
347 )
348 AND htt.user_id = p_user_id;
349
350 /*
351 * The following script deletes duplicate records for mid period
352 * assignment changes.
353 */
354 DELETE
355 FROM hxc_tcd_timecards t1
356 WHERE EXISTS
357 (
358 SELECT 1
359 FROM hxc_tcd_timecards t2
360 WHERE t1.user_id = p_user_id
361 AND t1.resource_id = t2.resource_id
362 AND t2.user_id = t1.user_id
363 AND t1.period_start_date = t2.period_start_date
364 AND t1.period_end_date = t2.period_end_date
365 AND t1.approval_status = t2.approval_status
366 AND (
367 t1.supervisor <> t2.supervisor
368 OR t1.organization <> t2.organization
369 OR t1.location <> t2.location
370 OR t1.person_type <> t2.person_type
371 )
372 AND t1.rowid > t2.rowid
373 );
374
375
376 IF g_debug THEN
377 l_proc := g_package||'delete_extra_rec';
378 hr_utility.set_location('Leaving:'|| l_proc, 20);
379 END IF;
380
381 END delete_extra_rec;
382
383
384 PROCEDURE delete_person_temp(p_recurring_period number, p_user_id number)
385 AS
386 BEGIN
387 IF g_debug THEN
388 l_proc := g_package||'delete_person_temp';
389 hr_utility.set_location('Entering:'|| l_proc, 10);
390 END IF;
391
392 DELETE FROM HXC_TEMP_TCD
393 WHERE recurring_period_id <> p_recurring_period
394 AND user_id = p_user_id
395 AND PERIOD_EXIST = 'T';
396
397 IF g_debug THEN
398 l_proc := g_package||'delete_person_temp';
399 hr_utility.set_location('Leaving:'|| l_proc, 20);
400 END IF;
401
402 END delete_person_temp;
403
404 /* clear data from temporary tables. Procedure also clears data from GTT*/
405
406 PROCEDURE clear_all_temp_tab(p_user_id IN number)
407 AS
408 BEGIN
409
410 IF g_debug THEN
411 l_proc := g_package||'clear_all_temp_tab';
412 hr_utility.set_location('Entering:'|| l_proc, 10);
413 END IF;
414
415 DELETE FROM HXC_TEMP_TCD
416 WHERE user_id = p_user_id;
417
418 DELETE FROM HXC_TCD_TIMECARDS
419 WHERE user_id = p_user_id;
420
421 DELETE FROM HXC_TCD_STATUS_COUNT
422 WHERE user_id = p_user_id;
423
424 COMMIT;
425
426 IF g_debug THEN
427 l_proc := g_package||'clear_all_temp_tab';
428 hr_utility.set_location('Leaving:'|| l_proc, 20);
429 END IF;
430
431 END clear_all_temp_tab;
432
433 /* clear data from temporary tables based on fnd_logins. Data less than sysdate will be deleted*/
434
435 PROCEDURE clear_all_inactive_logins
436 AS
437
438 CURSOR c_get_inactive_sessions
439 IS
440 SELECT distinct hxc.user_id
441 FROM fnd_logins fnd, hxc_tcd_timecards hxc
442 WHERE
443 hxc.user_id = fnd.login_id
444 AND
445 (fnd.start_time < sysdate - 1 OR fnd.end_time IS NOT NULL);
446
447 l_login_ids NUMBERTAB;
448
449 BEGIN
450
451 IF g_debug THEN
452 l_proc := g_package||'clear_all_inactive_logins';
453 hr_utility.set_location('Entering:'|| l_proc, 10);
454 END IF;
455
456 OPEN c_get_inactive_sessions;
457 LOOP
458 FETCH c_get_inactive_sessions BULK COLLECT INTO l_login_ids
459 LIMIT 500;
460 EXIT WHEN l_login_ids.COUNT = 0;
461 FORALL i IN l_login_ids.FIRST..l_login_ids.LAST
462 DELETE FROM HXC_TCD_TIMECARDS
463 WHERE user_id = l_login_ids(i);
464
465 FORALL i IN l_login_ids.FIRST..l_login_ids.LAST
466 DELETE FROM HXC_TCD_STATUS_COUNT
467 WHERE user_id = l_login_ids(i);
468
469 END LOOP;
470 CLOSE c_get_inactive_sessions;
471 COMMIT;
472
473 IF g_debug THEN
474 l_proc := g_package||'clear_all_inactive_logins';
475 hr_utility.set_location('Leaving:'|| l_proc, 20);
476 END IF;
477
478 END clear_all_inactive_logins;
479
480
481 /* appends oraganization and location in l_direct_sql and l_all_direct_sql
482 */
483 FUNCTION build_person_sql(p_sql IN OUT NOCOPY varchar2, p_org_id IN OUT NOCOPY number, p_loc_id IN OUT NOCOPY number)
484 RETURN VARCHAR2
485 IS
486
487 BEGIN
488
489 IF g_debug THEN
490 l_proc := g_package||'build_person_sql';
491 hr_utility.set_location('Entering:'|| l_proc, 10);
492 END IF;
493
494 IF p_org_id IS NULL OR p_org_id = ''
495 THEN
496 p_org_id := -1;
497 ELSE
498 p_sql := p_sql || ' AND organization_id = :6 ';
499 END IF;
500
501 IF p_loc_id IS NULL OR p_loc_id = '' THEN
502 p_loc_id := -1;
503 ELSIF l_org_id <> -1 THEN
504 p_sql := p_sql || ' AND location_id = :7';
505 ELSE
506 p_sql := p_sql || ' AND location_id = :6 ';
507 END IF;
508
509 IF g_debug THEN
510 hr_utility.trace('build_person_sql returing query :: '||p_sql);
511 hr_utility.set_location('Leaving:'|| l_proc, 20);
512 END IF;
513
514 RETURN p_sql;
515
516 END build_person_sql;
517
518 /**
519 * Sem-Monthly period generation is little bit tricky. The periods are generated based on the previous period
520 * end date rather than the start-date. The logic behind the generation is
521 *
522 * If the previous period end-date is either the lsat day of the month or 15th of a month, then the end dates
523 * will be 15th and the last day of that month.
524 *
525 * If the previous period end-date is from 1 to 14 then the second semi-period will be the addition of 15 to
526 * the date. In case of Feb. if the previous period end date is 14 (or) 29 then the end date would be the
527 * last day of february.
528 *
529 * If the previous period end date is greater than 15, then the second semi-period end date can be arrived by
530 * adding one month and subtracting 15 from the date.
531 */
532
533 FUNCTION get_semi_monthly_periods(p_start_date DATE, p_stop_date DATE)
534 RETURN VARCHARTAB
535 IS
536
537 l_period_end_date VARCHARTAB;
538 l_act_end_date DATE;
539 l_last_day number;
540 l_end_day number;
541 l_end_date DATE;
542 l_bool_mid_period boolean := FALSE;
543 l_diff number := 0;
544 l_count number := 0;
545 l_start_date DATE;
546
547 BEGIN
548
549 IF g_debug THEN
550 hr_utility.trace('Entering semi-monthly period generation ');
551 END IF;
552
553 l_act_end_date := p_start_date - 1; --From the period start date fetching the end date.
554 l_end_day := to_number(to_char(l_act_end_date,'DD')); --Fetching the day from the actual end date
555 l_last_day := to_number(to_char(last_day(l_act_end_date),'DD')); --Fetching the last day of that month
556
557 l_period_end_date(l_count) := l_act_end_date;
558 l_count := l_count + 1;
559
560 IF(l_end_day = 15 OR l_end_day = l_last_day) THEN
561 l_bool_mid_period := TRUE;
562 END IF;
563
564 IF g_debug THEN
565 hr_utility.trace('l_act_end_date :: '||l_act_end_date);
566 hr_utility.trace('p_stop_date :: '||p_stop_date);
567 END IF;
568
569 WHILE (l_act_end_date < p_stop_date)
570 LOOP
571 l_start_date := l_act_end_date + 1;
572 l_end_day := to_number (to_char (l_act_end_date, 'DD'));
573 l_last_day := to_number (to_char (last_day (l_act_end_date), 'DD'));
574
575 IF (l_bool_mid_period) THEN
576
577 IF (l_end_day = 15) THEN
578 l_end_date := last_day (l_start_date);
579 ELSE
580 l_end_date := l_start_date + 14;
581 END IF;
582
583 ELSIF (l_end_day > 15) THEN
584
585 l_last_day := to_number (to_char (last_day (l_act_end_date), 'DD'));
586 l_end_date := add_months ((l_act_end_date - 15), 1);
587 IF (l_end_day = l_last_day) THEN
588 l_end_date := l_end_date + l_diff;
589 END IF;
590
591 ELSE
592
593 l_end_date := l_start_date + 14;
594 l_last_day := to_number (to_char (l_end_date, 'DD'));
595 IF l_last_day < l_end_day THEN
596 l_diff := l_end_date - last_day (l_start_date);
597 l_end_date := last_day (l_start_date);
598 END IF;
599
600 END IF;
601 l_period_end_date (l_count) := l_end_date;
602 l_act_end_date := l_end_date;
603 l_count := l_count + 1;
604 END LOOP;
605
606 IF g_debug THEN
607 hr_utility.trace('Leaving semi-monthly period generation ');
608 END IF;
609
610 RETURN l_period_end_date;
611
612 END get_semi_monthly_periods;
613
614
615 FUNCTION build_all_person_sql(p_sql IN OUT NOCOPY varchar2, p_org_id IN OUT NOCOPY number, p_loc_id IN OUT NOCOPY number)
616 RETURN VARCHAR2
617 IS
618
619 BEGIN
620
621 IF g_debug THEN
622 l_proc := g_package||'build_all_person_sql';
623 hr_utility.set_location('Entering:'|| l_proc, 10);
624 END IF;
625
626 IF p_org_id IS NULL OR p_org_id = ''
627 THEN
628 p_org_id := -1;
629 ELSE
630 p_sql := p_sql || ' AND organization_id = :9 ';
631 END IF;
632
633 IF p_loc_id IS NULL OR p_loc_id = '' THEN
634 p_loc_id := -1;
635 ELSIF l_org_id <> -1 THEN
636 p_sql := p_sql || ' AND location_id = :10';
637 ELSE
638 p_sql := p_sql || ' AND location_id = :9 ';
639 END IF;
640
641 IF g_debug THEN
642 hr_utility.trace('p_sql generated for all_person is :: '||p_sql);
643 hr_utility.set_location('Leaving:'|| l_proc, 20);
644 END IF;
645
646 RETURN p_sql;
647
648 END build_all_person_sql;
649
650 /**
651 * The API recognizes the mid period termination and rehire with the flag 'R' and 'M'. However, the assignment changes
652 * are also marked as termination in the initial queries. This leads to show two records for the same timecard period.
653 *
654 * This issue is resolved, by verifying if the start_time ans stop_time of the timecard falls within their service
655 * period and change the flag accordingly.
656 *
657 * Employees and contingent workers have different service period records and hence we have two cursors to update the
658 * assignment changes.
659 */
660
661 PROCEDURE update_assignment_changes(l_user_id number)
662 AS
663
664 CURSOR c_emp_hire_records(p_user_id number,p_period_exist VARCHAR2)
665 IS
666 SELECT
667 htt.resource_id
668 ,htt.start_time
669 ,htt.stop_time
670 ,ppos.date_start period_start_date
671 ,NVL(ppos.actual_termination_date,hr_general.end_of_time) period_end_date
672 FROM
673 hxc_temp_tcd htt
674 ,per_periods_of_service ppos
675 WHERE (htt.period_exist = p_period_exist OR
676 htt.period_exist = lower(p_period_exist))
677 AND ppos.person_id = htt.resource_id
678 AND p_start_date > ppos.date_start
679 AND p_end_date < NVL(ppos.actual_termination_date, hr_general.end_of_time)
680 AND htt.user_id = p_user_id;
681
682 CURSOR c_cont_emp_hire_records(p_user_id number,p_period_exist VARCHAR2)
683 IS
684 SELECT
685 htt.resource_id
686 ,htt.start_time
687 ,htt.stop_time
688 ,ppos.date_start period_start_date
689 ,NVL(ppos.actual_termination_date,hr_general.end_of_time) period_end_date
690 FROM
691 hxc_temp_tcd htt
692 ,per_periods_of_placement ppos
693 WHERE (htt.period_exist = p_period_exist OR
694 htt.period_exist = lower(p_period_exist))
695 AND ppos.person_id = htt.resource_id
696 AND p_start_date > ppos.date_start
697 AND p_end_date < NVL(ppos.actual_termination_date,hr_general.end_of_time)
698 AND htt.user_id = p_user_id;
699
700
701 CURSOR c_get_suspended_assignments
702 (p_user_id IN number) IS
703 SELECT htt.start_time
704 , htt.stop_time
705 , htt.resource_id
706 , decode (greatest (asg.effective_start_date, htt.start_time), asg.effective_start_date
707 , 'M', 'R')
708 FROM per_all_assignments_f asg
709 , per_assignment_status_types past
710 , hxc_temp_tcd htt
711 WHERE asg.person_id = htt.resource_id
712 AND asg.effective_start_date > p_start_date
713 AND asg.effective_end_date < p_end_date
714 AND htt.user_id = p_user_id
715 AND asg.assignment_status_type_id = past.assignment_status_type_id
716 AND past.per_system_status IN ('SUSP_CWK_ASG', 'SUSP_ASSIGN');
717
718
719 CURSOR c_get_assginment_dates
720 (p_user_id IN number) IS
721 SELECT htt.resource_id
722 , htt.start_time
723 , htt.stop_time
724 , min (paaf.effective_start_date) start_date
725 , max (paaf.effective_end_date) end_date
726 FROM hxc_temp_tcd htt
727 , per_all_assignments_f paaf
728 , per_assignment_status_types past
729 WHERE paaf.person_id = htt.resource_id
730 AND paaf.effective_start_date <= p_end_date
731 AND paaf.effective_end_date >= p_start_date
732 AND past.assignment_status_type_id = paaf.assignment_status_type_id
733 AND past.per_system_status IN ('ACTIVE_ASSIGN', 'ACTIVE_CWK')
734 AND htt.user_id = p_user_id
735 AND period_exist = 'T'
736 GROUP BY htt.resource_id
737 , htt.start_time
738 , htt.stop_time;
739
740
741
742 CURSOR get_records(p_user_id IN number) IS
743 SELECT resource_id
744 , start_time
745 , stop_time
746 , period_exist
747 FROM hxc_temp_tcd
748 WHERE user_id = p_user_id;
749
750 l_resource_id VARCHARTAB;
751 l_start_time DATETAB;
752 l_stop_time DATETAB;
753 l_period_start_date DATETAB;
754 l_period_end_date DATETAB;
755 l_exist VARCHARTAB;
756
757 BEGIN
758
759 IF g_debug THEN
760 hr_utility.trace('Entering update assignment changes ');
761 END IF;
762
763 OPEN c_emp_hire_records(l_user_id,'R');
764 LOOP
765 FETCH c_emp_hire_records BULK COLLECT INTO l_resource_id,l_start_time,l_stop_time,l_period_start_date, l_period_end_date LIMIT 500;
766 EXIT WHEN l_resource_id.count = 0;
767
768 FORALL i IN l_resource_id.FIRST..l_resource_id.LAST
769 UPDATE hxc_temp_tcd
770 SET period_exist = 'T',
771 period_start_date = l_period_start_date(i),
772 period_end_date = l_period_end_date(i)
773 WHERE user_id = l_user_id
774 AND resource_id = l_resource_id(i)
775 AND start_time = l_start_time(i)
776 AND stop_time = l_stop_time(i)
777 AND (period_exist = 'R'
778 OR period_exist = 'r');
779
780 END LOOP;
781 CLOSE c_emp_hire_records;
782 OPEN c_emp_hire_records(l_user_id,'T');
783 LOOP
784 FETCH c_emp_hire_records BULK COLLECT INTO l_resource_id,l_start_time,l_stop_time,l_period_start_date, l_period_end_date LIMIT 500;
785 EXIT WHEN l_resource_id.count = 0;
786
787 FORALL i IN l_resource_id.FIRST..l_resource_id.LAST
788 UPDATE hxc_temp_tcd
789 SET
790 period_start_date = l_period_start_date(i),
791 period_end_date = l_period_end_date(i)
792 WHERE user_id = l_user_id
793 AND resource_id = l_resource_id(i)
794 AND start_time = l_start_time(i)
795 AND stop_time = l_stop_time(i)
796 AND period_exist = 'T';
797
798 END LOOP;
799 CLOSE c_emp_hire_records;
800
801 OPEN c_emp_hire_records(l_user_id,'M');
802 LOOP
803 FETCH c_emp_hire_records BULK COLLECT INTO l_resource_id,l_start_time,l_stop_time,l_period_start_date, l_period_end_date LIMIT 500;
804 EXIT WHEN l_resource_id.count = 0;
805
806 FORALL i IN l_resource_id.FIRST..l_resource_id.LAST
807 UPDATE hxc_temp_tcd
808 SET period_exist = 'T',
809 period_start_date = l_period_start_date(i),
810 period_end_date = l_period_end_date(i)
811 WHERE user_id = l_user_id
812 AND resource_id = l_resource_id(i)
813 AND start_time = l_start_time(i)
814 AND stop_time = l_stop_time(i)
815 AND (period_exist = 'm'
816 OR period_exist = 'M');
817
818 END LOOP;
819 CLOSE c_emp_hire_records;
820
821
822 OPEN c_cont_emp_hire_records(l_user_id,'R');
823 LOOP
824 FETCH c_cont_emp_hire_records BULK COLLECT INTO l_resource_id,l_start_time,l_stop_time,l_period_start_date, l_period_end_date LIMIT 500;
825 EXIT WHEN l_resource_id.count = 0;
826
827 FORALL i IN l_resource_id.FIRST..l_resource_id.LAST
828 UPDATE hxc_temp_tcd
829 SET period_exist = 'T',
830 period_start_date = l_period_start_date(i),
831 period_end_date = l_period_end_date(i)
832 WHERE user_id = l_user_id
833 AND resource_id = l_resource_id(i)
834 AND start_time = l_start_time(i)
835 AND stop_time = l_stop_time(i)
836 AND (period_exist = 'R'
837 OR period_exist = 'r');
838
839 END LOOP;
840 CLOSE c_cont_emp_hire_records;
841
842 OPEN c_cont_emp_hire_records(l_user_id,'M');
843 LOOP
844 FETCH c_cont_emp_hire_records BULK COLLECT INTO l_resource_id,l_start_time,l_stop_time,l_period_start_date, l_period_end_date LIMIT 500;
845 EXIT WHEN l_resource_id.count = 0;
846
847 FORALL i IN l_resource_id.FIRST..l_resource_id.LAST
848 UPDATE hxc_temp_tcd
849 SET period_exist = 'T',
850 period_start_date = l_period_start_date(i),
851 period_end_date = l_period_end_date(i)
852 WHERE user_id = l_user_id
853 AND resource_id = l_resource_id(i)
854 AND start_time = l_start_time(i)
855 AND stop_time = l_stop_time(i)
856 AND (period_exist = 'm'
857 OR period_exist = 'M');
858
859 END LOOP;
860 CLOSE c_cont_emp_hire_records;
861
862
863 open c_get_suspended_assignments(l_user_id);
864 loop
865 fetch c_get_suspended_assignments BULK COLLECT INTO l_start_time, l_stop_time, l_resource_id, l_exist LIMIT 500;
866 hr_utility.trace('count :: '||l_resource_id.count);
867 exit when l_resource_id.count = 0;
868
869 FORALL i IN l_resource_id.FIRST..l_resource_id.LAST
870 UPDATE hxc_temp_tcd
871 SET period_exist = l_exist(i)
872 WHERE user_id = l_user_id
873 AND resource_id = l_resource_id(i)
874 AND start_time = l_start_time(i)
875 AND stop_time = l_stop_time(i);
876 END LOOP;
877
878 close c_get_suspended_assignments;
879
880
881 open c_get_assginment_dates(l_user_id);
882 loop
883 fetch c_get_assginment_dates BULK COLLECT INTO l_resource_id,l_start_time,l_stop_time,l_period_start_date, l_period_end_date LIMIT 500;
884 hr_utility.trace('count :: '||l_resource_id.count);
885 exit when l_resource_id.count = 0;
886
887
888 FORALL i IN l_resource_id.FIRST..l_resource_id.LAST
889 UPDATE hxc_temp_tcd
890 SET ASSG_START_DATE = l_period_start_date(i)
891 , ASSG_END_DATE = l_period_end_date(i)
892 WHERE user_id = l_user_id
893 AND resource_id = l_resource_id(i)
894 AND start_time = l_start_time(i)
895 AND stop_time = l_stop_time(i);
896 END LOOP;
897
898 close c_get_assginment_dates;
899
900 IF g_debug THEN
901 hr_utility.trace('Leaving update assignment changes ');
902 END IF;
903
904 END update_assignment_changes;
905
906 /*
907 Procedure to update employee_number and person_name in GTT
908 */
909 PROCEDURE update_person_det(p_user_id number)
910 AS
911
912 CURSOR c_person_temp(p_user_id number)
913 IS
914 SELECT
915 nvl(per.employee_number
916 ,per.npw_number) person_number,
917 per.full_name,
918 htt.resource_id
919 FROM
920 hxc_temp_tcd htt,
921 per_all_people_f per
922 WHERE htt.resource_id = per.person_id
923 AND trunc(per.effective_start_date) <= trunc(SYSDATE)
924 AND trunc(per.effective_end_date) >= trunc(SYSDATE)
925 AND htt.user_id = p_user_id;
926
927
928
929 l_person_ids VARCHARTAB;
930 l_resource_ids NUMBERTAB;
931 l_person_names VARCHARTAB;
932
933 BEGIN
934
935 IF g_debug THEN
936 l_proc := g_package||'update_person_det';
937 hr_utility.set_location('Entering:'|| l_proc, 10);
938 END IF;
939
940 OPEN c_person_temp(p_user_id);
941 LOOP
942
943 FETCH c_person_temp BULK COLLECT INTO l_person_ids,
944 l_person_names,
945 l_resource_ids
946 LIMIT 500;
947 EXIT WHEN l_person_ids.COUNT = 0;
948
949 FORALL i IN l_person_ids.FIRST..l_person_ids.LAST
950 UPDATE hxc_temp_tcd
951 SET person_number = l_person_ids(i),
952 full_name = l_person_names(i)
953 WHERE user_id = p_user_id
954 AND resource_id = l_resource_ids(i);
955
956 END LOOP;
957 CLOSE c_person_temp;
958
959 IF g_debug THEN
960 hr_utility.set_location('Leaving:'|| l_proc, 20);
961 END IF;
962
963 END update_person_det;
964
965 /*
966 Procedure to update payroll_name in GTT
967 */
968 PROCEDURE update_payroll(p_user_id IN number)
969 AS
970 CURSOR c_payroll_temp(p_user_id number)
971 IS
972
973 SELECT
974 DISTINCT
975 pap.payroll_name,
976 htt.payroll_name
977 FROM
978 pay_all_payrolls_f pap, hxc_temp_tcd htt
979 WHERE pap.payroll_id = htt.payroll_name
980 AND trunc(pap.effective_start_date) <= trunc(SYSDATE)
981 AND trunc(pap.effective_end_date) >= trunc(SYSDATE)
982 AND htt.user_id = p_user_id;
983
984 l_payroll_ids VARCHARTAB;
985 l_payroll_names VARCHARTAB;
986
987 BEGIN
988
989 IF g_debug THEN
990 l_proc := g_package||'update_payroll';
991 hr_utility.set_location('Entering:'|| l_proc, 10);
992 END IF;
993
994 OPEN c_payroll_temp(p_user_id);
995 LOOP
996 FETCH c_payroll_temp BULK COLLECT INTO l_payroll_names,
997 l_payroll_ids LIMIT 500;
998
999 EXIT WHEN l_payroll_ids.COUNT = 0;
1000
1001 FORALL i IN l_payroll_ids.FIRST..l_payroll_ids.LAST
1002 UPDATE hxc_temp_tcd
1003 SET payroll_name = l_payroll_names(i)
1004 WHERE user_id = p_user_id
1005 AND payroll_name = l_payroll_ids(i);
1006 END LOOP;
1007 CLOSE c_payroll_temp;
1008
1009 IF g_debug THEN
1010 hr_utility.set_location('Leaving:'|| l_proc, 20);
1011 END IF;
1012
1013 END update_payroll;
1014
1015 /*
1016 Procedure to update organization_name in GTT
1017 */
1018 PROCEDURE update_organization(p_user_id IN number)
1019 AS
1020
1021 CURSOR c_organization_temp(p_user_id number)
1022 IS
1023 SELECT
1024 DISTINCT
1025 hxo.name,
1026 htt.organization_name
1027 FROM
1028 hr_all_organization_units_tl hxo, hxc_temp_tcd htt
1029 WHERE hxo.organization_id = htt.organization_name
1030 AND htt.user_id = p_user_id
1031 AND hxo.language = userenv('LANG');
1032
1033 l_organization_ids VARCHARTAB;
1034 l_organization_names VARCHARTAB;
1035 BEGIN
1036
1037 IF g_debug THEN
1038 l_proc := g_package||'update_organization';
1039 hr_utility.set_location('Entering:'|| l_proc, 10);
1040 END IF;
1041
1042 OPEN c_organization_temp(p_user_id);
1043 LOOP
1044 FETCH c_organization_temp BULK COLLECT INTO l_organization_names,
1045 l_organization_ids LIMIT 500;
1046
1047 EXIT WHEN l_organization_ids.COUNT = 0;
1048
1049 FORALL i IN l_organization_ids.FIRST..l_organization_ids.LAST
1050 UPDATE hxc_temp_tcd
1051 SET organization_name = l_organization_names(i)
1052 WHERE user_id = p_user_id
1053 AND organization_name = l_organization_ids(i);
1054
1055 END LOOP;
1056 CLOSE c_organization_temp;
1057
1058 IF g_debug THEN
1059 hr_utility.set_location('Leaving:'|| l_proc, 20);
1060 END IF;
1061
1062 END update_organization;
1063
1064
1065 /*
1066 Procedure to update location_name in GTT
1067 */
1068 PROCEDURE update_location(p_user_id IN number)
1069 AS
1070
1071
1072 CURSOR c_location_temp(p_user_id number)
1073 IS
1074 SELECT
1075 DISTINCT
1076 hxl.location_code,
1077 htt.location_name
1078 FROM
1079 hr_locations_all_tl hxl, hxc_temp_tcd htt
1080 WHERE hxl.location_id = htt.location_name
1081 AND htt.user_id = p_user_id
1082 AND hxl.language = userenv('LANG');
1083
1084 l_location_ids VARCHARTAB;
1085 l_location_names VARCHARTAB;
1086
1087 BEGIN
1088
1089 IF g_debug THEN
1090 l_proc := g_package||'update_location';
1091 hr_utility.set_location('Entering:'|| l_proc, 10);
1092 END IF;
1093
1094 OPEN c_location_temp(p_user_id);
1095 LOOP
1096 FETCH c_location_temp BULK COLLECT INTO l_location_names,
1097 l_location_ids
1098 LIMIT 500;
1099 EXIT WHEN l_location_ids.COUNT = 0;
1100
1101 FORALL i IN l_location_ids.FIRST..l_location_ids.LAST
1102 UPDATE hxc_temp_tcd
1103 SET location_name = l_location_names(i)
1104 WHERE user_id = p_user_id
1105 AND location_name = l_location_ids(i);
1106
1107 END LOOP;
1108 CLOSE c_location_temp;
1109
1110 IF g_debug THEN
1111 hr_utility.set_location('Leaving:'|| l_proc, 20);
1112 END IF;
1113
1114 END update_location;
1115
1116 /*
1117 Procedure to update supervisor_name in GTT
1118 */
1119 PROCEDURE update_supervisor(p_user_id IN number)
1120 AS
1121
1122
1123 CURSOR c_supervisor_temp(p_user_id number)
1124 IS
1125 SELECT
1126 DISTINCT
1127 per.full_name,
1128 htt.supervisor_name
1129 FROM
1130 per_all_people_f per, hxc_temp_tcd htt
1131 WHERE per.person_id = htt.supervisor_name
1132 AND htt.user_id = p_user_id;
1133
1134 l_supervisor_ids VARCHARTAB;
1135 l_supervisor_names VARCHARTAB;
1136
1137 BEGIN
1138
1139 IF g_debug THEN
1140 l_proc := g_package||'update_supervisor';
1141 hr_utility.set_location('Entering:'|| l_proc, 10);
1142 END IF;
1143
1144 OPEN c_supervisor_temp(p_user_id);
1145 LOOP
1146 FETCH c_supervisor_temp BULK COLLECT INTO l_supervisor_names,
1147 l_supervisor_ids
1148 LIMIT 500;
1149 EXIT WHEN l_supervisor_ids.COUNT = 0;
1150
1151 FORALL i IN l_supervisor_ids.FIRST..l_supervisor_ids.LAST
1152 UPDATE hxc_temp_tcd
1153 SET supervisor_name = l_supervisor_names(i)
1154 WHERE user_id = p_user_id
1155 AND supervisor_name = l_supervisor_ids(i);
1156
1157 END LOOP;
1158 CLOSE c_supervisor_temp;
1159
1160 IF g_debug THEN
1161 hr_utility.set_location('Leaving:'|| l_proc, 20);
1162 END IF;
1163
1164 END update_supervisor;
1165
1166 /*
1167 Procedure to update person_type in GTT
1168 */
1169 PROCEDURE update_person_types(p_user_id IN number)
1170 AS
1171
1172 CURSOR c_person_types(p_user_id number)
1173 IS
1174 SELECT
1175 ppt.user_person_type,
1176 htt.resource_id
1177 FROM
1178 per_person_types ppt,
1179 per_person_type_usages_f ptu,
1180 hxc_temp_tcd htt
1181 WHERE htt.resource_id = ptu.person_id
1182 AND ppt.person_type_id = ptu.person_type_id
1183 AND trunc(ptu.effective_start_date) <= trunc(SYSDATE)
1184 AND trunc(ptu.effective_end_date) >= trunc(SYSDATE)
1185 AND htt.user_id = p_user_id;
1186 -- AND ppt.system_person_type IN ('EMP', 'EMP_APL', 'EX_EMP', 'EX_EMP_APL', 'CWK', 'EX_CWK'); --remove
1187
1188 l_resource_ids NUMBERTAB;
1189 l_person_types VARCHARTAB;
1190
1191 BEGIN
1192
1193 IF g_debug THEN
1194 l_proc := g_package||'update_person_types';
1195 hr_utility.set_location('Entering:'|| l_proc, 10);
1196 END IF;
1197
1198 OPEN c_person_types(p_user_id);
1199 LOOP
1200 FETCH c_person_types BULK COLLECT INTO l_person_types,
1201 l_resource_ids
1202 LIMIT 500;
1203 EXIT WHEN l_resource_ids.COUNT = 0;
1204
1205 FORALL i IN l_person_types.FIRST..l_person_types.LAST
1206 UPDATE hxc_temp_tcd
1207 SET person_type = l_person_types(i)
1208 WHERE user_id = p_user_id
1209 AND resource_id = l_resource_ids(i);
1210
1211
1212 END LOOP;
1213 CLOSE c_person_types;
1214
1215 IF g_debug THEN
1216 hr_utility.set_location('Leaving:'|| l_proc, 20);
1217 END IF;
1218
1219 END update_person_types;
1220
1221 /*
1222 Procedure to update application_set_name in GTT
1223 */
1224 PROCEDURE update_application(p_user_id IN number)
1225 AS
1226
1227 CURSOR c_timecard_ne(p_user_id number)
1228 IS
1229 SELECT
1230 heg.name application_set_name,
1231 htt.resource_id
1232 FROM
1233 hxc_entity_groups heg,
1234 hxc_temp_tcd htt
1235 WHERE heg.entity_type = 'TIME_RECIPIENTS'
1236 AND htt.application IS NOT NULL
1237 AND heg.entity_group_id = htt.application
1238 AND htt.user_id = p_user_id;
1239
1240 l_resource_ids NUMBERTAB;
1241 l_applications VARCHARTAB;
1242
1243 BEGIN
1244
1245 IF g_debug THEN
1246 l_proc := g_package||'update_application';
1247 hr_utility.set_location('Entering:'|| l_proc, 10);
1248 END IF;
1249
1250 OPEN c_timecard_ne(p_user_id);
1251 LOOP
1252 FETCH c_timecard_ne BULK COLLECT INTO l_applications,
1253 l_resource_ids
1254 LIMIT 500;
1255 EXIT WHEN l_resource_ids.COUNT = 0;
1256
1257 FORALL i IN l_applications.FIRST..l_applications.LAST
1258 UPDATE hxc_temp_tcd
1259 SET application = l_applications(i)
1260 WHERE user_id = p_user_id
1261 AND resource_id = l_resource_ids(i);
1262
1263 END LOOP;
1264 CLOSE c_timecard_ne;
1265
1266 IF g_debug THEN
1267 hr_utility.set_location('Leaving:'|| l_proc, 20);
1268 END IF;
1269
1270 END update_application;
1271
1272 /*
1273 Procedure to update approval_status in GTT
1274 */
1275 PROCEDURE insert_approval_meaning(p_user_id IN number)
1276 AS
1277 CURSOR c_approval_status(p_user_id number)
1278 IS
1279 SELECT distinct
1280 flv.meaning,
1281 htt.approval_status
1282 FROM
1283 fnd_lookup_values flv,
1284 hxc_temp_tcd htt
1285 WHERE flv.language = userenv('LANG')
1286 AND flv.view_application_id = 3
1287 AND flv.lookup_type = 'HXC_APPROVAL_STATUS'
1288 AND htt.approval_status = flv.lookup_code
1289 AND htt.user_id = p_user_id;
1290
1291 l_meanings VARCHARTAB;
1292 l_approval_status VARCHARTAB;
1293
1294 BEGIN
1295
1296 IF g_debug THEN
1297 l_proc := g_package||'insert_approval_meaning';
1298 hr_utility.set_location('Entering:'|| l_proc, 10);
1299 END IF;
1300
1301 OPEN c_approval_status(p_user_id);
1302 LOOP
1303 FETCH c_approval_status BULK COLLECT INTO l_meanings,
1304 l_approval_status
1305 LIMIT 500;
1306
1307 EXIT WHEN l_approval_status.COUNT = 0;
1308
1309 FORALL i IN l_approval_status.FIRST..l_approval_status.LAST
1310 UPDATE hxc_temp_tcd
1311 SET meaning = l_meanings(i)
1312 WHERE user_id = p_user_id
1313 AND approval_status = l_approval_status(i);
1314
1315 END LOOP;
1316 CLOSE c_approval_status;
1317
1318 IF g_debug THEN
1319 hr_utility.set_location('Leaving:'|| l_proc, 20);
1320 END IF;
1321
1322 END insert_approval_meaning;
1323
1324 PROCEDURE update_last_update_det(p_user_id IN number)
1325 AS
1326 CURSOR c_last_update_det(p_user_id number)
1327 IS
1328 SELECT
1329 tim.last_updated_by,
1330 tim.last_update_date,
1331 htt.timecard_id,
1332 htt.timecard_ovn
1333 FROM
1334 hxc_time_building_blocks tim,
1335 hxc_temp_tcd htt
1336 WHERE tim.scope like 'TIMECARD'
1337 AND htt.timecard_id = tim.time_building_block_id
1338 AND htt.timecard_ovn = tim.object_version_number
1339 AND htt.user_id = p_user_id ;
1340
1341
1342 l_last_update_logins NUMBERTAB;
1343 l_last_update_dates DATETAB;
1344 l_timecard_ids NUMBERTAB;
1345 l_timecard_ovns NUMBERTAB;
1346
1347 BEGIN
1348
1349 IF g_debug THEN
1350 l_proc := g_package||'update_last_update_det';
1351 hr_utility.set_location('Entering:'|| l_proc, 10);
1352 END IF;
1353
1354 OPEN c_last_update_det(p_user_id);
1355 LOOP
1356 FETCH c_last_update_det BULK COLLECT INTO l_last_update_logins,
1357 l_last_update_dates,
1358 l_timecard_ids,
1359 l_timecard_ovns
1360 LIMIT 500 ;
1361
1362 EXIT WHEN l_timecard_ids.COUNT = 0;
1363
1364 FORALL i IN l_timecard_ids.FIRST..l_timecard_ids.LAST
1365 UPDATE hxc_temp_tcd
1366 SET last_modified_by = l_last_update_logins(i),
1367 last_modified_date = l_last_update_dates(i)
1368 WHERE user_id = p_user_id
1369 AND timecard_id = l_timecard_ids(i)
1370 AND timecard_ovn = l_timecard_ovns(i);
1371
1372 END LOOP;
1373 CLOSE c_last_update_det;
1374
1375 IF g_debug THEN
1376 hr_utility.set_location('Leaving:'|| l_proc, 20);
1377 END IF;
1378
1379
1380 END update_last_update_det;
1381
1382
1383 PROCEDURE update_last_update_by(p_user_id IN number)
1384 AS
1385 CURSOR c_update_last_modified_by(p_user_id number)
1386 IS
1387 SELECT DISTINCT
1388 fu.user_name,
1389 htt.last_modified_by
1390 FROM
1391 fnd_user fu,
1392 hxc_temp_tcd htt
1393 WHERE fu.user_id = htt.last_modified_by
1394 AND htt.user_id = p_user_id;
1395
1396
1397 l_user_names VARCHARTAB;
1398 l_last_modified_by VARCHARTAB;
1399
1400 BEGIN
1401
1402 IF g_debug THEN
1403 l_proc := g_package||'update_last_update_det';
1404 hr_utility.set_location('Entering:'|| l_proc, 10);
1405 END IF;
1406
1407 OPEN c_update_last_modified_by(p_user_id);
1408 LOOP
1409 FETCH c_update_last_modified_by BULK COLLECT INTO l_user_names,
1410 l_last_modified_by
1411 LIMIT 500;
1412
1413 EXIT WHEN l_user_names.COUNT = 0;
1414
1415 FOR i IN 1..l_last_modified_by.COUNT
1416 LOOP
1417 UPDATE hxc_temp_tcd
1418 SET last_modified_by = l_user_names(i)
1419 WHERE user_id = p_user_id
1420 AND last_modified_by = l_last_modified_by(i)
1421 AND last_modified_by IS NOT NULL;
1422 END LOOP;
1423
1424 END LOOP;
1425 CLOSE c_update_last_modified_by;
1426
1427 IF g_debug THEN
1428 hr_utility.set_location('Leaving:'|| l_proc, 20);
1429 END IF;
1430
1431
1432 END update_last_update_by;
1433
1434
1435 PROCEDURE insert_notification_id(p_user_id IN number)
1436 AS
1437 CURSOR c_timecards(p_user_id number)
1438 IS
1439 SELECT htt.timecard_id
1440 FROM hxc_temp_tcd htt
1441 WHERE htt.timecard_id IS NOT NULL
1442 AND htt.approval_status LIKE 'SUBMITTED'
1443 AND htt.user_id = p_user_id;
1444
1445 CURSOR c_insert_application_period_id(p_user_id number)
1446 IS
1447 SELECT htt.timecard_id
1448 ,hta.application_period_id
1449 FROM hxc_temp_tcd htt
1450 ,hxc_tc_ap_links hta
1451 WHERE htt.timecard_id = hta.application_period_id
1452 AND htt.timecard_id IS NOT NULL
1453 AND htt.user_id = p_user_id;
1454
1455 CURSOR c_get_notif_ids(p_timecard_id number, p_user_name varchar2)
1456 IS
1457 SELECT htal.timecard_id
1458 ,wias.notification_id
1459 FROM hxc_tc_ap_links htal
1460 ,hxc_app_period_summary haps
1461 ,wf_item_attribute_values wiav
1462 ,wf_item_activity_statuses wias
1463 ,wf_notifications wf
1464 WHERE htal.application_period_id = haps.application_period_id
1465 AND wf.notification_id = wias.notification_id
1466 AND htal.timecard_id = p_timecard_id
1467 AND haps.approval_status = 'SUBMITTED'
1468 AND wiav.item_type = 'HXCEMP'
1469 AND wiav.item_key = haps.approval_item_key
1470 AND wiav.name = 'APR_PERSON_ID'
1471 AND wias.item_type = wiav.item_type
1472 AND wias.item_key = wiav.item_key
1473 AND wias.activity_status = 'NOTIFIED'
1474 AND wf.recipient_role = p_user_name;
1475
1476 l_timecard_ids NUMBERTAB;
1477
1478 l_notification_ids NUMBERTAB;
1479 l_timecard_notif_ids NUMBERTAB;
1480 l_user_name varchar2(80);
1481
1482 BEGIN
1483
1484 IF g_debug THEN
1485 l_proc := g_package||'insert_notification_id';
1486 hr_utility.set_location('Entering:'|| l_proc, 10);
1487 END IF;
1488 hr_utility.trace('PNS fnd_global.user_id '||fnd_global.user_id);
1489 SELECT user_name
1490 INTO l_user_name
1491 FROM fnd_user
1492 WHERE user_id = fnd_global.user_id;
1493
1494 OPEN c_timecards(p_user_id);
1495 LOOP
1496 FETCH c_timecards BULK COLLECT INTO l_timecard_ids;
1497 EXIT WHEN l_timecard_ids.COUNT = 0;
1498
1499 FOR i IN l_timecard_ids.FIRST..l_timecard_ids.LAST
1500 LOOP
1501
1502 OPEN c_get_notif_ids(l_timecard_ids(i),l_user_name);
1503 LOOP
1504 FETCH c_get_notif_ids BULK COLLECT INTO l_timecard_notif_ids,
1505 l_notification_ids
1506 LIMIT 500;
1507 EXIT WHEN l_timecard_notif_ids.COUNT = 0;
1508
1509 FORALL j IN l_timecard_notif_ids.FIRST..l_timecard_notif_ids.LAST
1510 UPDATE hxc_temp_tcd
1511 SET notification_id = l_notification_ids(j)
1512 WHERE user_id = p_user_id
1513 AND timecard_id = l_timecard_notif_ids(j);
1514
1515 END LOOP; -- END LOOP FOR c_get_notif_ids
1516 CLOSE c_get_notif_ids;
1517
1518 END LOOP; --END LOOP FOR L_TIMECARDS_IDS
1519
1520 END LOOP; --END LOOP FOR c_timecards
1521 CLOSE c_timecards;
1522 IF g_debug THEN
1523 hr_utility.set_location('Leaving:'|| l_proc, 20);
1524 END IF;
1525
1526 END insert_notification_id;
1527
1528 /*
1529 Procedure to insert data in temporary table hxc_tcd_timecards
1530 for timecards which exist.
1531 */
1532
1533 PROCEDURE insert_timecard_exist(p_user_id IN number)
1534 AS
1535
1536 l_count number;
1537 BEGIN
1538
1539 IF g_debug THEN
1540 l_proc := g_package||'insert_timecard_exist';
1541 hr_utility.set_location('Entering:'|| l_proc, 10);
1542 END IF;
1543
1544 INSERT INTO HXC_TCD_TIMECARDS
1545 (
1546 resource_id,
1547 rec_period_id,
1548 period_start_date,
1549 period_end_date,
1550 person_number,
1551 person_name,
1552 approval_status,
1553 supervisor_id,
1554 supervisor,
1555 organization,
1556 location,
1557 person_type,
1558 payroll_name,
1559 last_modified_by,
1560 last_modified_date,
1561 application,
1562 user_id,
1563 notification_id,
1564 timecard_id,
1565 timecard_ovn ,
1566 meaning
1567 )
1568 SELECT
1569 resource_id,
1570 recurring_period_id,
1571 start_time,
1572 stop_time,
1573 person_number,
1574 full_name,
1575 approval_status,
1576 supervisor_id,
1577 supervisor_name,
1578 organization_name,
1579 location_name,
1580 person_type,
1581 payroll_name,
1582 last_modified_by,
1583 last_modified_date,
1584 application,
1585 user_id,
1586 notification_id,
1587 timecard_id,
1588 timecard_ovn,
1589 meaning
1590 FROM
1591 hxc_temp_tcd
1592 WHERE timecard_exist = 'Y'
1593 AND user_id = p_user_id;
1594
1595 IF g_debug THEN
1596 hr_utility.set_location('Leaving:'|| l_proc, 20);
1597 END IF;
1598
1599 END insert_timecard_exist;
1600
1601 /*
1602 Procedure to insert data in temporary table hxc_tcd_timecards
1603 for timecards which does not exist.
1604 */
1605
1606 PROCEDURE insert_timecard_not_exist(p_user_id IN number)
1607 AS
1608 CURSOR c_temp_data(p_user_id number)
1609 IS
1610 SELECT
1611 resource_id,
1612 start_time,
1613 stop_time,
1614 approval_status,
1615 recurring_period_id,
1616 person_number,
1617 full_name,
1618 payroll_name,
1619 organization_name,
1620 location_name,
1621 supervisor_id,
1622 supervisor_name,
1623 person_type,
1624 application,
1625 meaning,
1626 period_exist,
1627 assg_start_date,
1628 assg_end_date,
1629 period_start_date,
1630 period_end_date
1631 FROM
1632 hxc_temp_tcd
1633 WHERE timecard_exist = 'N'
1634 AND user_id = p_user_id;
1635
1636 cursor c_get_recurring_period_det(p_recurring_id number)
1637 IS
1638 SELECT
1639 hrp.recurring_period_id,
1640 hrp.period_type,
1641 hrp.start_date,
1642 to_char(to_date(hrp.start_date)
1643 ,'D') day,
1644 hrp.duration_in_days
1645 FROM
1646 hxc_recurring_periods hrp
1647 WHERE
1648 hrp.recurring_period_id = p_recurring_id;
1649
1650 l_start_time date;
1651 l_stop_time date;
1652 l_temp number;
1653
1654 l_st_day NUMBER := 0;
1655 l_cr_day NUMBER := 0;
1656 l_diff NUMBER := 0;
1657 l_period_temp_date DATE;
1658 --l_period_start_date DATE;
1659 l_period_stop_date DATE;
1660
1661 l_act_start_date1 DATE;
1662 l_act_start_date2 DATE;
1663 l_act_end_date2 DATE;
1664 l_act_end_date1 DATE;
1665 l_tmp_date DATE;
1666 l_semi_st_day NUMBER;
1667 l_semi__stop_day NUMBER;
1668 l_semi_end_day NUMBER;
1669 l_end_last_day NUMBER;
1670
1671 l_start_day1 number;
1672 l_start_day2 number;
1673
1674 l_semi__stop_day1 number;
1675 l_semi__stop_day2 number;
1676
1677 l_pass_start number;
1678 l_pass_end number;
1679 l_month number;
1680 l_tmp_num number;
1681
1682 bool_month_end boolean := FALSE;
1683
1684 BEGIN
1685
1686 IF g_debug THEN
1687 l_proc := g_package||'insert_timecard_not_exist';
1688 hr_utility.set_location('Entering:'|| l_proc, 10);
1689 END IF;
1690
1691 OPEN c_temp_data(l_user_id);
1692 LOOP
1693 FETCH c_temp_data BULK COLLECT INTO l_timecard LIMIT 500;
1694 EXIT WHEN l_timecard.COUNT = 0;
1695
1696 FOR i IN l_timecard.FIRST..l_timecard.LAST
1697 LOOP
1698 /*Caching recurring_period details*/
1699 IF (g_recurring_det.EXISTS(l_timecard(i).recurring_period_id))
1700 THEN
1701
1702 IF g_debug THEN
1703 hr_utility.set_location(l_proc, 20);
1704 END IF;
1705
1706 l_recurring_period_id := g_recurring_det(l_timecard(i).recurring_period_id).recurring_period_id;
1707 l_period_type := g_recurring_det(l_timecard(i).recurring_period_id).period_type;
1708 l_start_date := g_recurring_det(l_timecard(i).recurring_period_id).start_day;
1709 l_day := g_recurring_det(l_timecard(i).recurring_period_id).day_count;
1710 l_duration_in_days := g_recurring_det(l_timecard(i).recurring_period_id).duration_in_days;
1711
1712 ELSE
1713
1714 IF g_debug THEN
1715 hr_utility.set_location(l_proc, 30);
1716 END IF;
1717
1718 OPEN c_get_recurring_period_det(l_timecard(i).recurring_period_id);
1719 FETCH c_get_recurring_period_det INTO l_recurring_period_id,
1720 l_period_type,
1721 l_start_date,
1722 l_day,
1723 l_duration_in_days;
1724 CLOSE c_get_recurring_period_det;
1725 g_recurring_det(l_timecard(i).recurring_period_id).recurring_period_id := l_recurring_period_id;
1726 g_recurring_det(l_timecard(i).recurring_period_id).period_type := l_period_type;
1727 g_recurring_det(l_timecard(i).recurring_period_id).start_day := l_start_date;
1728 g_recurring_det(l_timecard(i).recurring_period_id).day_count := l_day;
1729 g_recurring_det(l_timecard(i).recurring_period_id).duration_in_days := l_duration_in_days;
1730
1731 END IF; --caching end if
1732
1733 --- *** Format everything under this IF condition once clearly. The indentation is not clear.
1734 IF l_period_type IS NOT NULL AND l_duration_in_days IS NULL
1735 THEN
1736 /**
1737 *
1738 */
1739 IF (g_rec_period_type.EXISTS(l_timecard(i).recurring_period_id))
1740 THEN
1741
1742 IF g_debug THEN
1743 hr_utility.set_location(l_proc, 21);
1744 END IF;
1745 l_number_per_fiscal_year := g_rec_period_type(l_timecard(i).recurring_period_id).number_per_fiscal_year;
1746 ELSE
1747 SELECT ptt.number_per_fiscal_year INTO l_number_per_fiscal_year
1748 FROM per_time_period_types ptt
1749 WHERE ptt.period_type = l_period_type
1750 AND ptt.system_flag = 'Y';
1751 g_rec_period_type(l_timecard(i).recurring_period_id).recurring_period_id := l_recurring_period_id;
1752 g_rec_period_type(l_timecard(i).recurring_period_id).period_type := l_period_type;
1753 g_rec_period_type(l_timecard(i).recurring_period_id).number_per_fiscal_year := l_number_per_fiscal_year;
1754
1755 END IF;
1756
1757 IF l_period_type = 'Calendar Month' THEN
1758
1759 IF g_debug THEN
1760 hr_utility.set_location(l_proc, 40);
1761 hr_utility.trace('PNS:Calendar Monthly ');
1762 hr_utility.trace('PNS:l_start_date_day '||l_start_date_day);
1763 hr_utility.trace('PNS:l_day '||l_day);
1764 END IF;
1765
1766 l_period_stop_date := l_start_date - 1;
1767
1768 l_st_day := to_number(to_char(l_start_date,'DD'));
1769 l_cr_day := to_number(to_char(l_timecard(i).start_time,'DD'));
1770
1771 hr_utility.trace('PNS:l_st_day '||l_st_day);
1772 hr_utility.trace('PNS:l_cr_day '||l_cr_day);
1773
1774 if l_st_day > l_cr_day then
1775 l_diff := l_st_day - l_cr_day;
1776 l_start_time := add_months(l_timecard(i).start_time,-1);
1777 l_start_time := l_start_time + l_diff;
1778 elsif l_st_day < l_cr_day then
1779 l_diff := l_cr_day - l_st_day;
1780 l_start_time := l_timecard(i).start_time - l_diff;
1781 else
1782 l_start_time := l_timecard(i).start_time;
1783 end if;
1784
1785 l_st_day := to_number(to_char(l_period_stop_date,'DD'));
1786 l_cr_day := to_number(to_char(l_timecard(i).stop_time,'DD'));
1787
1788 if l_st_day < l_cr_day then
1789 l_period_temp_date := last_day(l_timecard(i).stop_time);
1790 l_stop_time := l_period_temp_date + l_st_day;
1791 elsif l_st_day > l_cr_day then
1792 l_diff := l_st_day - l_cr_day;
1793 l_stop_time := l_timecard(i).stop_time + l_diff;
1794 else
1795 l_stop_time := l_timecard(i).stop_time;
1796 end if;
1797
1798
1799 OPEN c_get_monthly_periods(l_start_time, l_stop_time);
1800 LOOP
1801 IF g_debug THEN
1802 hr_utility.trace('PNS l_start_date: '||l_start_time);
1803 hr_utility.trace('PNS l_stop_time: '||l_stop_time);
1804 END IF;
1805 FETCH c_get_monthly_periods BULK COLLECT INTO l_period_start_date,
1806 l_period_end_date
1807 LIMIT 500;
1808 EXIT WHEN l_period_start_date.COUNT = 0;
1809 FOR j in l_period_start_date.first..l_period_start_date.last
1810 LOOP
1811
1812 if((l_timecard(i).period_exist = 'r') OR (l_timecard(i).period_exist = 'R')) then
1813 if(l_timecard(i).start_time > l_period_start_date(j))then
1814 l_period_start_date(j) := l_timecard(i).start_time;
1815 end if;
1816 end if ;
1817
1818 if((l_timecard(i).period_exist = 'm') OR (l_timecard(i).period_exist = 'M')) then
1819 if(l_timecard(i).stop_time < l_period_end_date(j)) then
1820 l_period_end_date(j) := l_timecard(i).stop_time;
1821 end if;
1822 end if;
1823
1824 if(l_timecard(i).period_exist = 'T') then
1825 if(l_period_end_date(j) > l_timecard(i).period_end_date) then
1826 l_period_end_date(j) := l_timecard(i).period_end_date;
1827 elsif (l_period_end_date(j) > l_timecard(i).assg_end_date) then
1828 l_period_end_date(j) := l_timecard(i).assg_end_date;
1829 end if;
1830
1831 if(l_period_start_date(j) < l_timecard(i).period_start_date) then
1832 l_period_start_date(j) := l_timecard(i).period_start_date;
1833 elsif(l_period_start_date(j) < l_timecard(i).assg_start_date) then
1834 l_period_start_date(j) := l_timecard(i).assg_start_date;
1835 end if;
1836 end if;
1837 INSERT INTO HXC_TCD_TIMECARDS
1838 (
1839 resource_id,
1840 rec_period_id,
1841 period_start_date,
1842 period_end_date,
1843 person_number,
1844 person_name,
1845 approval_status,
1846 supervisor_id,
1847 supervisor,
1848 organization,
1849 location,
1850 person_type,
1851 payroll_name,
1852 /* last_modified_by
1853 last_modified_date */
1854 application,
1855 meaning,
1856 user_id
1857 )
1858 VALUES
1859 (
1860 l_timecard(i).resource_id,
1861 l_timecard(i).recurring_period_id,
1862 l_period_start_date(j),
1863 l_period_end_date(j),
1864 l_timecard(i).person_number,
1865 l_timecard(i).full_name,
1866 l_timecard(i).approval_status,
1867 l_timecard(i).supervisor_id,
1868 l_timecard(i).supervisor_name,
1869 l_timecard(i).organization_name,
1870 l_timecard(i).location_name,
1871 l_timecard(i).person_types,
1872 l_timecard(i).payroll_name,
1873 l_timecard(i).application,
1874 l_timecard(i).meaning,
1875 l_user_id
1876 );
1877 END LOOP;
1878
1879 END LOOP;
1880 CLOSE c_get_monthly_periods;
1881
1882
1883 ELSIF l_period_type = 'Year'
1884 THEN
1885 IF g_debug THEN
1886 hr_utility.set_location(l_proc, 40);
1887 hr_utility.trace('PNS:Year ');
1888 hr_utility.trace('PNS:l_start_date_day '||l_start_date_day);
1889 END IF;
1890
1891 l_period_stop_date := l_start_date - 1;
1892
1893 l_st_day := to_number(to_char(l_start_date,'DD'));
1894 l_cr_day := to_number(to_char(l_timecard(i).start_time,'DD'));
1895
1896 if l_st_day > l_cr_day then
1897 l_diff := l_st_day - l_cr_day;
1898 l_start_time := add_months(l_timecard(i).start_time,-1);
1899 l_start_time := l_start_time + l_diff;
1900 elsif l_st_day < l_cr_day then
1901 l_diff := l_cr_day - l_st_day;
1902 l_start_time := l_timecard(i).start_time - l_diff;
1903 else
1904 l_start_time := l_timecard(i).start_time;
1905 end if;
1906
1907
1908 l_st_day := to_number(to_char(l_period_stop_date,'DD'));
1909 l_cr_day := to_number(to_char(l_timecard(i).stop_time,'DD'));
1910
1911 if l_st_day < l_cr_day then
1912 l_period_temp_date := last_day(l_timecard(i).stop_time);
1913 l_stop_time := l_period_temp_date + l_st_day;
1914 elsif l_st_day > l_cr_day then
1915 l_diff := l_st_day - l_cr_day;
1916 l_stop_time := l_timecard(i).stop_time + l_diff;
1917 else
1918 l_stop_time := l_timecard(i).stop_time;
1919 end if;
1920
1921
1922 OPEN c_get_yearly_periods(l_start_time, l_stop_time);
1923 LOOP
1924 IF g_debug THEN
1925 hr_utility.trace('PNS l_start_date: '||l_start_time);
1926 hr_utility.trace('PNS l_stop_time: '||l_stop_time);
1927 END IF;
1928 FETCH c_get_yearly_periods BULK COLLECT INTO l_period_start_date,
1929 l_period_end_date
1930 LIMIT 500;
1931 EXIT WHEN l_period_start_date.COUNT = 0;
1932 FOR j in l_period_start_date.first..l_period_start_date.last
1933 LOOP
1934
1935 if((l_timecard(i).period_exist = 'r') OR (l_timecard(i).period_exist = 'R')) then
1936 if(l_timecard(i).start_time > l_period_start_date(j))then
1937 l_period_start_date(j) := l_timecard(i).start_time;
1938 end if;
1939 end if ;
1940
1941 if((l_timecard(i).period_exist = 'm') OR (l_timecard(i).period_exist = 'M')) then
1942 if(l_timecard(i).stop_time < l_period_end_date(j)) then
1943 l_period_end_date(j) := l_timecard(i).stop_time;
1944 end if;
1945 end if;
1946
1947 if(l_timecard(i).period_exist = 'T') then
1948 if(l_period_end_date(j) > l_timecard(i).period_end_date) then
1949 l_period_end_date(j) := l_timecard(i).period_end_date;
1950 elsif (l_period_end_date(j) > l_timecard(i).assg_end_date) then
1951 l_period_end_date(j) := l_timecard(i).assg_end_date;
1952 end if;
1953
1954 if(l_period_start_date(j) < l_timecard(i).period_start_date) then
1955 l_period_start_date(j) := l_timecard(i).period_start_date;
1956 elsif(l_period_start_date(j) < l_timecard(i).assg_start_date) then
1957 l_period_start_date(j) := l_timecard(i).assg_start_date;
1958 end if;
1959 end if;
1960 INSERT INTO HXC_TCD_TIMECARDS
1961 (
1962 resource_id,
1963 rec_period_id,
1964 period_start_date,
1965 period_end_date,
1966 person_number,
1967 person_name,
1968 approval_status,
1969 supervisor_id,
1970 supervisor,
1971 organization,
1972 location,
1973 person_type,
1974 payroll_name,
1975 /* last_modified_by
1976 last_modified_date */
1977 application,
1978 meaning,
1979 user_id
1980 )
1981 VALUES
1982 (
1983 l_timecard(i).resource_id,
1984 l_timecard(i).recurring_period_id,
1985 l_period_start_date(j),
1986 l_period_end_date(j),
1987 l_timecard(i).person_number,
1988 l_timecard(i).full_name,
1989 l_timecard(i).approval_status,
1990 l_timecard(i).supervisor_id,
1991 l_timecard(i).supervisor_name,
1992 l_timecard(i).organization_name,
1993 l_timecard(i).location_name,
1994 l_timecard(i).person_types,
1995 l_timecard(i).payroll_name,
1996 l_timecard(i).application,
1997 l_timecard(i).meaning,
1998 l_user_id
1999 );
2000 END LOOP;
2001
2002 END LOOP;
2003 CLOSE c_get_yearly_periods;
2004
2005 ELSIF l_period_type = 'Quarter'
2006 THEN
2007 IF g_debug THEN
2008 hr_utility.set_location(l_proc, 40);
2009 hr_utility.trace('PNS:Quarter ');
2010 hr_utility.trace('PNS:l_start_date_day '||l_start_date_day);
2011 hr_utility.trace('PNS:l_day '||l_day);
2012 END IF;
2013
2014 l_period_stop_date := l_start_date - 1;
2015
2016 l_st_day := to_number(to_char(l_start_date,'DD'));
2017 l_cr_day := to_number(to_char(l_timecard(i).start_time,'DD'));
2018
2019 if l_st_day > l_cr_day then
2020 l_diff := l_st_day - l_cr_day;
2021 l_start_time := add_months(l_timecard(i).start_time,-1);
2022 l_start_time := l_start_time + l_diff;
2023 elsif l_st_day < l_cr_day then
2024 l_diff := l_cr_day - l_st_day;
2025 l_start_time := l_timecard(i).start_time - l_diff;
2026 else
2027 l_start_time := l_timecard(i).start_time;
2028 end if;
2029
2030 l_st_day := to_number(to_char(l_period_stop_date,'DD'));
2031 l_cr_day := to_number(to_char(l_timecard(i).start_time,'DD'));
2032
2033 if l_st_day < l_cr_day then
2034 l_period_temp_date := last_day(l_timecard(i).stop_time);
2035 l_stop_time := l_period_temp_date + l_st_day;
2036 elsif l_st_day > l_cr_day then
2037 l_diff := l_st_day - l_cr_day;
2038 l_stop_time := l_timecard(i).stop_time + l_diff;
2039 else
2040 l_stop_time := l_timecard(i).stop_time;
2041 end if;
2042
2043
2044 OPEN c_get_quarterly_periods(l_start_time, l_stop_time);
2045 LOOP
2046 IF g_debug THEN
2047 hr_utility.trace('PNS l_start_date: '||l_start_time);
2048 hr_utility.trace('PNS l_stop_time: '||l_stop_time);
2049 END IF;
2050 FETCH c_get_quarterly_periods BULK COLLECT INTO l_period_start_date,
2051 l_period_end_date
2052 LIMIT 500;
2053 EXIT WHEN l_period_start_date.COUNT = 0;
2054 FOR j in l_period_start_date.first..l_period_start_date.last
2055 LOOP
2056
2057 if((l_timecard(i).period_exist = 'r') OR (l_timecard(i).period_exist = 'R')) then
2058 if(l_timecard(i).start_time > l_period_start_date(j))then
2059 l_period_start_date(j) := l_timecard(i).start_time;
2060 end if;
2061 end if ;
2062
2063 if((l_timecard(i).period_exist = 'm') OR (l_timecard(i).period_exist = 'M')) then
2064 if(l_timecard(i).stop_time < l_period_end_date(j)) then
2065 l_period_end_date(j) := l_timecard(i).stop_time;
2066 end if;
2067 end if;
2068
2069 if(l_timecard(i).period_exist = 'T') then
2070 if(l_period_end_date(j) > l_timecard(i).period_end_date) then
2071 l_period_end_date(j) := l_timecard(i).period_end_date;
2072 elsif (l_period_end_date(j) > l_timecard(i).assg_end_date) then
2073 l_period_end_date(j) := l_timecard(i).assg_end_date;
2074 end if;
2075
2076 if(l_period_start_date(j) < l_timecard(i).period_start_date) then
2077 l_period_start_date(j) := l_timecard(i).period_start_date;
2078 elsif(l_period_start_date(j) < l_timecard(i).assg_start_date) then
2079 l_period_start_date(j) := l_timecard(i).assg_start_date;
2080 end if;
2081 end if;
2082
2083 INSERT INTO HXC_TCD_TIMECARDS
2084 (
2085 resource_id,
2086 rec_period_id,
2087 period_start_date,
2088 period_end_date,
2089 person_number,
2090 person_name,
2091 approval_status,
2092 supervisor_id,
2093 supervisor,
2094 organization,
2095 location,
2096 person_type,
2097 payroll_name,
2098 /* last_modified_by
2099 last_modified_date */
2100 application,
2101 meaning,
2102 user_id
2103 )
2104 VALUES
2105 (
2106 l_timecard(i).resource_id,
2107 l_timecard(i).recurring_period_id,
2108 l_period_start_date(j),
2109 l_period_end_date(j),
2110 l_timecard(i).person_number,
2111 l_timecard(i).full_name,
2112 l_timecard(i).approval_status,
2113 l_timecard(i).supervisor_id,
2114 l_timecard(i).supervisor_name,
2115 l_timecard(i).organization_name,
2116 l_timecard(i).location_name,
2117 l_timecard(i).person_types,
2118 l_timecard(i).payroll_name,
2119 l_timecard(i).application,
2120 l_timecard(i).meaning,
2121 l_user_id
2122 );
2123 END LOOP;
2124
2125 END LOOP;
2126 CLOSE c_get_quarterly_periods;
2127
2128 ELSIF l_period_type = 'Semi-Month'
2129 THEN
2130 IF g_debug THEN
2131 hr_utility.set_location(l_proc, 40);
2132 hr_utility.trace('PNS:Semi-Monthly ');
2133 hr_utility.trace('PNS:l_start_date_day '||l_start_date_day);
2134 hr_utility.trace('PNS:l_day '||l_day);
2135 END IF;
2136
2137 l_act_start_date1 := l_start_date;
2138
2139 l_pass_start := to_number(to_char(l_timecard(i).start_time,'DD'));
2140 l_pass_end := to_number(to_char(l_timecard(i).stop_time,'DD'));
2141
2142 l_semi_st_day := to_number(to_char(l_act_start_date1,'DD'));
2143 l_semi__stop_day := to_number(to_char(l_timecard(i).stop_time,'DD'));
2144
2145 l_act_end_date1 := l_act_start_date1 - 1;
2146 l_semi_end_day := to_number(to_char(l_act_end_date1,'DD'));
2147 l_end_last_day := to_number(to_char(last_day(l_act_end_date1),'DD'));
2148
2149 if l_semi_end_day < 15 then
2150 l_act_end_date2 := l_act_end_date1 + 15;
2151 bool_month_end := FALSE;
2152 elsif l_semi_end_day > 15 and l_end_last_day <> l_semi_end_day then
2153 l_act_end_date2 := l_act_end_date1 - 15;
2154 bool_month_end := FALSE;
2155 elsif l_semi_end_day > 15 and l_end_last_day = l_semi_end_day then
2156 l_act_end_date2 := l_act_end_date1 + 15;
2157 bool_month_end := TRUE;
2158 else
2159 bool_month_end := TRUE;
2160 l_act_end_date2 := last_day(l_act_start_date1);
2161 end if;
2162
2163 l_act_start_date2 := l_act_end_date2 + 1;
2164
2165 l_start_day1 := to_number(to_char(l_act_start_date1,'DD'));
2166 l_start_day2 := to_number(to_char(l_act_start_date2,'DD'));
2167
2168 l_semi__stop_day1 := to_number(to_char(l_act_end_date1,'DD'));
2169 l_semi__stop_day2 := to_number(to_char(l_act_end_date2,'DD'));
2170
2171
2172 if l_start_day1 > l_start_day2 then
2173 l_tmp_num := l_start_day1;
2174 l_start_day1 := l_start_day2;
2175 l_start_day2 := l_tmp_num;
2176 end if;
2177
2178 if l_semi__stop_day1 > l_semi__stop_day2 then
2179 l_tmp_num := l_semi__stop_day1;
2180 l_semi__stop_day1 := l_semi__stop_day2;
2181 l_semi__stop_day2 := l_tmp_num;
2182 end if;
2183
2184 l_start_time := l_timecard(i).start_time;
2185 if l_pass_start > l_start_day1 and l_pass_start < l_start_day2 then
2186 l_start_time := l_timecard(i).start_time - (l_pass_start - l_start_day1);
2187 elsif l_pass_start > l_start_day2 and l_pass_start > l_start_day1 then
2188 l_start_time := l_timecard(i).start_time - (l_pass_start - l_start_day2);
2189 elsif l_pass_start < l_start_day1 then
2190 l_start_time := add_months(l_timecard(i).start_time,-1) + (l_start_day2 - l_pass_start);
2191 end if;
2192
2193 l_tmp_date := l_timecard(i).stop_time;
2194 l_stop_time := l_tmp_date;
2195 if (l_semi__stop_day1 = 15 and l_pass_end > 15) OR bool_month_end then
2196 l_stop_time := last_day(l_timecard(i).stop_time);
2197 elsif l_pass_end > l_semi__stop_day1 and l_pass_end < l_semi__stop_day2 then
2198 l_month := to_number(to_char(l_timecard(i).stop_time,'MM'));
2199 l_stop_time := l_timecard(i).stop_time + (l_semi__stop_day2 - l_pass_end);
2200 if to_number(to_char(l_timecard(i).stop_time,'MM')) > l_month then
2201 l_stop_time := last_day(l_tmp_date);
2202 end if;
2203 elsif l_pass_end > l_semi__stop_day2 and l_pass_end > l_semi__stop_day1 then
2204 l_stop_time := l_timecard(i).stop_time - (l_pass_end - l_semi__stop_day2);
2205 l_stop_time := add_months(l_stop_time,1) - 15;
2206 elsif l_pass_end < l_semi__stop_day1 then
2207 l_stop_time := l_timecard(i).stop_time + (l_semi__stop_day1 - l_pass_end);
2208 end if;
2209
2210
2211
2212 /**
2213 * The get_semi_monthly_periods function returns the list of end_dates
2214 * along with the previous end dates. Lets say for eg: the start_time is
2215 * 01-JAN-2012 and stop_time is 01-AUG-2012, with the recurring period
2216 * starts on 1st of every month, the l_period_end_date list will have
2217 * dates from 31-DEC-2011, 15-JAN-2012 ...15-AUG-2012.
2218 *
2219 * So, now we have one record extra in l_period_end_date record than the
2220 * l_period_start_date.
2221 */
2222
2223 l_period_end_date := get_semi_monthly_periods(l_start_time, l_stop_time);
2224 For i in 0..l_period_end_date.count-1
2225 loop
2226 l_period_start_date(i) := to_char((to_date(l_period_end_date(i))+1),'DD-MON-YYY');
2227 end loop;
2228 --Verify this logic pravesk
2229 FOR j in 0..l_period_start_date.count-2
2230 LOOP
2231
2232 if((l_timecard(i).period_exist = 'r') OR (l_timecard(i).period_exist = 'R')) then
2233 if(l_timecard(i).start_time > l_period_start_date(j))then
2234 l_period_start_date(j) := l_timecard(i).start_time;
2235 end if;
2236 end if ;
2237
2238 if((l_timecard(i).period_exist = 'm') OR (l_timecard(i).period_exist = 'M')) then
2239 if(l_timecard(i).stop_time < l_period_end_date(j)) then
2240 l_period_end_date(j+1) := l_timecard(i).stop_time;
2241 end if;
2242 end if;
2243
2244 if(l_timecard(i).period_exist = 'T') then
2245 if(l_period_end_date(j) > l_timecard(i).period_end_date) then
2246 l_period_end_date(j) := l_timecard(i).period_end_date;
2247 elsif (l_period_end_date(j) > l_timecard(i).assg_end_date) then
2248 l_period_end_date(j) := l_timecard(i).assg_end_date;
2249 end if;
2250
2251 if(l_period_start_date(j) < l_timecard(i).period_start_date) then
2252 l_period_start_date(j) := l_timecard(i).period_start_date;
2253 elsif(l_period_start_date(j) < l_timecard(i).assg_start_date) then
2254 l_period_start_date(j) := l_timecard(i).assg_start_date;
2255 end if;
2256 end if;
2257
2258 INSERT INTO HXC_TCD_TIMECARDS
2259 (
2260 resource_id,
2261 rec_period_id,
2262 period_start_date,
2263 period_end_date,
2264 person_number,
2265 person_name,
2266 approval_status,
2267 supervisor_id,
2268 supervisor,
2269 organization,
2270 location,
2271 person_type,
2272 payroll_name,
2273 /* last_modified_by
2274 last_modified_date */
2275 application,
2276 meaning,
2277 user_id
2278 )
2279 VALUES
2280 (
2281 l_timecard(i).resource_id,
2282 l_timecard(i).recurring_period_id,
2283 l_period_start_date(j),
2284 l_period_end_date(j+1),
2285 l_timecard(i).person_number,
2286 l_timecard(i).full_name,
2287 l_timecard(i).approval_status,
2288 l_timecard(i).supervisor_id,
2289 l_timecard(i).supervisor_name,
2290 l_timecard(i).organization_name,
2291 l_timecard(i).location_name,
2292 l_timecard(i).person_types,
2293 l_timecard(i).payroll_name,
2294 l_timecard(i).application,
2295 l_timecard(i).meaning,
2296 l_user_id
2297 );
2298 END LOOP;
2299
2300 ELSE --weekly (or) bi-weekly
2301 l_start_date_day := to_number(to_char(to_date(l_timecard(i).start_time),'D'));
2302
2303 IF g_debug THEN
2304 hr_utility.set_location(l_proc, 40);
2305 hr_utility.trace('PNS:l_start_date_day '||l_start_date_day);
2306 hr_utility.trace('PNS:l_day '||l_day);
2307 END IF;
2308
2309 IF l_start_date_day > l_day
2310 THEN
2311 l_count_day := l_start_date_day - l_day;
2312 -- l_start_time := l_timecard(i).start_time + l_count_day;
2313 l_start_time := l_timecard(i).start_time - l_count_day;
2314
2315 IF g_debug THEN
2316 hr_utility.set_location(l_proc, 50);
2317 hr_utility.trace('PNS Record:l_count_day '||l_count_day);
2318 END IF;
2319
2320 ELSIF l_start_date_day < l_day
2321 THEN
2322 l_count_day := l_day - l_start_date_day;
2323 l_start_time := (l_timecard(i).start_time - trunc(365 / l_number_per_fiscal_year)) + l_count_day; --code for year 366
2324
2325 IF g_debug THEN
2326 hr_utility.set_location(l_proc, 60);
2327 hr_utility.trace('PNS:l_count_day '||l_count_day);
2328 hr_utility.trace('PNS: l_start_time'||l_start_time);
2329 END IF;
2330
2331 ELSE
2332 IF g_debug THEN
2333 hr_utility.set_location(l_proc, 70);
2334 END IF;
2335 l_start_time := l_timecard(i).start_time;
2336
2337 END IF; -- l_start_date_day > l_day
2338 l_stop_time := l_timecard(i).stop_time;
2339 IF mod(((l_stop_time - l_start_time)+1), trunc(365 / l_number_per_fiscal_year)) <> 0
2340 THEN
2341 l_stop_time := l_stop_time + ( trunc(365 / l_number_per_fiscal_year) - mod(((l_stop_time - l_start_time)+1), trunc(365 / l_number_per_fiscal_year)) );
2342 IF g_debug THEN
2343 hr_utility.set_location(l_proc, 80);
2344 hr_utility.trace('PNS: l_stop_time '||l_stop_time);
2345 END IF;
2346
2347 END IF; --mod
2348
2349 --code for leap year
2350 OPEN c_get_periods(l_number_per_fiscal_year, l_start_time, l_stop_time, 365);
2351 LOOP
2352 IF g_debug THEN
2353 hr_utility.trace('PNS l_number_per_fiscal_year: '||l_number_per_fiscal_year);
2354 hr_utility.trace('PNS l_timecard(i).start_time: '||l_timecard(i).start_time);
2355 hr_utility.trace('PNS l_start_date: '||l_start_time);
2356 hr_utility.trace('PNS l_stop_time: '||l_stop_time);
2357 END IF;
2358 FETCH c_get_periods BULK COLLECT INTO l_period_start_date,
2359 l_period_end_date
2360 LIMIT 500;
2361 EXIT WHEN l_period_start_date.COUNT = 0;
2362 FOR j in l_period_start_date.first..l_period_start_date.last
2363 LOOP
2364 if((l_timecard(i).period_exist = 'r') OR (l_timecard(i).period_exist = 'R')) then
2365 if(l_timecard(i).start_time > l_period_start_date(j))then
2366 l_period_start_date(j) := l_timecard(i).start_time;
2367 end if;
2368 end if ;
2369
2370 if((l_timecard(i).period_exist = 'm') OR (l_timecard(i).period_exist = 'M')) then
2371 if(l_timecard(i).stop_time < l_period_end_date(j)) then
2372 l_period_end_date(j) := l_timecard(i).stop_time;
2373 end if;
2374 end if;
2375
2376 if(l_timecard(i).period_exist = 'T') then
2377 if(l_period_end_date(j) > l_timecard(i).period_end_date) then
2378 l_period_end_date(j) := l_timecard(i).period_end_date;
2379 elsif (l_period_end_date(j) > l_timecard(i).assg_end_date) then
2380 l_period_end_date(j) := l_timecard(i).assg_end_date;
2381 end if;
2382
2383 if(l_period_start_date(j) < l_timecard(i).period_start_date) then
2384 l_period_start_date(j) := l_timecard(i).period_start_date;
2385 elsif(l_period_start_date(j) < l_timecard(i).assg_start_date) then
2386 l_period_start_date(j) := l_timecard(i).assg_start_date;
2387 end if;
2388 end if;
2389
2390
2391
2392 INSERT INTO HXC_TCD_TIMECARDS
2393 (
2394 resource_id,
2395 rec_period_id,
2396 period_start_date,
2397 period_end_date,
2398 person_number,
2399 person_name,
2400 approval_status,
2401 supervisor_id,
2402 supervisor,
2403 organization,
2404 location,
2405 person_type,
2406 payroll_name,
2407 /* last_modified_by
2408 last_modified_date */
2409 application,
2410 meaning,
2411 user_id
2412 )
2413 VALUES
2414 (
2415 l_timecard(i).resource_id,
2416 l_timecard(i).recurring_period_id,
2417 l_period_start_date(j),
2418 l_period_end_date(j),
2419 l_timecard(i).person_number,
2420 l_timecard(i).full_name,
2421 l_timecard(i).approval_status,
2422 l_timecard(i).supervisor_id,
2423 l_timecard(i).supervisor_name,
2424 l_timecard(i).organization_name,
2425 l_timecard(i).location_name,
2426 l_timecard(i).person_types,
2427 l_timecard(i).payroll_name,
2428 l_timecard(i).application,
2429 l_timecard(i).meaning,
2430 l_user_id
2431 );
2432 END LOOP;
2433
2434 END LOOP;
2435 CLOSE c_get_periods;
2436
2437 END IF ;--l_period_type week or bi-weeik
2438
2439
2440
2441 ELSE -- l_period_type IS NOT NULL AND l_duration_in_days IS NULL
2442 l_count_day := 0;
2443 IF l_duration_in_days = 1
2444 THEN
2445 l_start_time := l_timecard(i).start_time;
2446 ELSE
2447 IF l_timecard(i).start_time >= l_start_date
2448 THEN
2449
2450 l_count_day := mod(to_number(to_char(l_timecard(i).start_time,'j'))- to_number(to_char(l_start_date,'j')),l_duration_in_days);
2451 IF l_count_day > 0
2452 THEN
2453
2454 l_count_day := l_duration_in_days - l_count_day;
2455 END IF;
2456 l_start_time := l_timecard(i).start_time + l_count_day;
2457 ELSE
2458 IF l_start_date > l_timecard(i).start_time AND l_start_date <= l_timecard(i).stop_time
2459 THEN
2460 l_start_time := l_start_date;
2461 END IF;
2462 END IF;
2463 -- select mod(to_number(to_char(l_start_date,'j'))- to_number(to_char(to_date('03-jan-2012'),'j')),5) from dual
2464 END IF;
2465
2466 OPEN c_get_duration_periods(l_duration_in_days, l_start_time, l_timecard(i).stop_time);
2467 LOOP
2468 FETCH c_get_duration_periods BULK COLLECT INTO l_period_start_date,
2469 l_period_end_date
2470 LIMIT 500; -- INCREASE LATER
2471 EXIT WHEN l_period_start_date.COUNT = 0;
2472 FORALL j in l_period_start_date.first..l_period_start_date.last
2473 INSERT INTO HXC_TCD_TIMECARDS
2474 (
2475 resource_id,
2476 rec_period_id,
2477 period_start_date,
2478 period_end_date,
2479 person_number,
2480 person_name,
2481 approval_status,
2482 supervisor_id,
2483 supervisor,
2484 organization,
2485 location,
2486 person_type,
2487 payroll_name,
2488 /* last_modified_by
2489 last_modified_date */
2490 application,
2491 meaning,
2492 user_id
2493 )
2494 VALUES
2495 (
2496 l_timecard(i).resource_id,
2497 l_timecard(i).recurring_period_id,
2498 l_period_start_date(j),
2499 l_period_end_date(j),
2500 l_timecard(i).person_number,
2501 l_timecard(i).full_name,
2502 l_timecard(i).approval_status,
2503 l_timecard(i).supervisor_id,
2504 l_timecard(i).supervisor_name,
2505 l_timecard(i).organization_name,
2506 l_timecard(i).location_name,
2507 l_timecard(i).person_types,
2508 l_timecard(i).payroll_name,
2509 l_timecard(i).application,
2510 l_timecard(i).meaning,
2511 l_user_id
2512 );
2513
2514 END LOOP; -- END LOOP FOR c_get_duration_periods
2515 CLOSE c_get_duration_periods;
2516 END IF;
2517 END LOOP;
2518 END LOOP; --END LOOP FOR c_temp_data
2519 CLOSE c_temp_data;
2520
2521 END insert_timecard_not_exist;
2522
2523 /*
2524 Procedure to insert data in temporary table hxc_tcd_status_count
2525 for timecard's count
2526 */
2527
2528 --- *** Add Login id into the tables here.
2529
2530 PROCEDURE insert_timecard_count(p_user_id IN number)
2531 AS
2532
2533 BEGIN
2534 IF g_debug THEN
2535 l_proc := g_package||'insert_timecard_count';
2536 hr_utility.set_location('Entering:'|| l_proc, 10);
2537 END IF;
2538
2539 INSERT INTO HXC_TCD_STATUS_COUNT(supervisor_id,supervisor_name,not_enetered,working,error, submitted,rejected,approved,user_id)
2540 SELECT
2541 supervisor_id,
2542 supervisor,
2543 max(decode(approval_status
2544 ,'NOTENTERED'
2545 ,cnt
2546 ,0)) "Not Entered",
2547 max(decode(approval_status
2548 ,'WORKING'
2549 ,cnt
2550 ,0)) "Working",
2551 max(decode(approval_status
2552 ,'ERROR'
2553 ,cnt
2554 ,0)) "Error",
2555 max(decode(approval_status
2556 ,'SUBMITTED'
2557 ,cnt
2558 ,0)) "Submitted",
2559 max(decode(approval_status
2560 ,'REJECTED'
2561 ,cnt
2562 ,0)) "Rejected",
2563 max(decode(approval_status
2564 ,'APPROVED'
2565 ,cnt
2566 ,0)) "Approved",
2567 user_id
2568 FROM
2569 (SELECT
2570 supervisor_id,
2571 supervisor,
2572 approval_status,
2573 user_id,
2574 count (*) cnt
2575 FROM
2576 hxc_tcd_timecards
2577 GROUP BY
2578 supervisor_id,
2579 approval_status,
2580 supervisor,
2581 user_id )
2582 WHERE user_id = p_user_id
2583 GROUP BY
2584 supervisor_id,
2585 supervisor,
2586 user_id;
2587
2588 IF g_debug THEN
2589 l_proc := g_package||'insert_timecard_count';
2590 hr_utility.set_location('Leaving:'|| l_proc, 20);
2591 END IF;
2592
2593 END insert_timecard_count;
2594
2595 /*
2596 Procedure to update total in temporary table hxc_tcd_status_count
2597
2598 */
2599 PROCEDURE update_total(p_user_id IN number)
2600 AS
2601 CURSOR c_calc_total(p_user_id number)
2602 IS
2603 SELECT
2604 not_enetered + working + error + submitted + rejected + approved "Total", supervisor_id
2605 FROM
2606 hxc_tcd_status_count
2607 WHERE user_id = p_user_id;
2608
2609 l_supervisor_ids NUMBERTAB;
2610 l_totals NUMBERTAB;
2611
2612 BEGIN
2613 OPEN c_calc_total(p_user_id);
2614 LOOP
2615 FETCH c_calc_total BULK COLLECT INTO l_totals,
2616 l_supervisor_ids;
2617 EXIT WHEN c_calc_total%NOTFOUND;
2618 END LOOP;
2619 CLOSE c_calc_total;
2620
2621 FORALL i IN l_totals.FIRST..l_totals.LAST
2622 UPDATE hxc_tcd_status_count
2623 SET total = l_totals(i)
2624 WHERE user_id = p_user_id
2625 AND supervisor_id = l_supervisor_ids(i);
2626
2627
2628 END update_total;
2629
2630 --Begin of Go Method
2631 BEGIN
2632 /*hr_utility.trace_on(null,'sdb');
2633 g_debug := true;*/
2634 IF g_debug THEN
2635 hr_utility.trace('Parameter:p_start_date'||p_start_date);
2636 l_proc := g_package||'GO';
2637 hr_utility.set_location('Entering:'|| l_proc, 10);
2638 END IF;
2639 clear_all_temp_tab(l_user_id);
2640 clear_all_inactive_logins;
2641
2642 IF g_debug THEN
2643 hr_utility.trace('Parameter:p_supervisor_id'||p_supervisor_id);
2644 hr_utility.trace('Parameter:p_start_date'||p_start_date);
2645 hr_utility.trace('Parameter:p_end_date'||p_end_date);
2646 hr_utility.trace('Parameter:p_organization_id'||p_organization_id);
2647 hr_utility.trace('Parameter:p_location_id'||p_location_id);
2648 hr_utility.trace('Parameter:p_recurring_period'||p_recurring_period);
2649 END IF;
2650
2651 IF p_reptng_emp = 'DIRECT_REPORTEES'
2652 THEN
2653 IF g_debug THEN
2654 hr_utility.set_location(l_proc, 20);
2655 END IF;
2656
2657 l_direct_sql := build_person_sql(l_direct_sql, l_org_id, l_location_id);
2658
2659 IF g_debug THEN
2660 hr_utility.trace('l_direct_sql:'||l_direct_sql);
2661 END IF;
2662
2663
2664 IF l_org_id = -1 AND l_location_id = -1 THEN
2665 EXECUTE IMMEDIATE l_direct_sql BULK COLLECT INTO l_person_det USING
2666 p_start_date, p_end_date,
2667 p_supervisor_id,
2668 p_end_date,p_start_date;
2669
2670 ELSIF l_org_id <> -1 AND l_location_id = -1 THEN
2671 EXECUTE IMMEDIATE l_direct_sql BULK COLLECT INTO l_person_det USING
2672 p_start_date, p_end_date,
2673 p_supervisor_id,
2674 p_end_date,p_start_date,
2675 p_organization_id;
2676
2677 ELSIF l_org_id = -1 AND l_location_id <> -1 THEN
2678 EXECUTE IMMEDIATE l_direct_sql BULK COLLECT INTO l_person_det USING
2679 p_start_date, p_end_date,
2680 p_supervisor_id,
2681 p_end_date,p_start_date,
2682 p_location_id;
2683
2684 ELSE
2685 EXECUTE IMMEDIATE l_direct_sql BULK COLLECT INTO l_person_det USING
2686 p_start_date, p_end_date,
2687 p_supervisor_id,
2688 p_end_date,p_start_date,
2689 p_organization_id, p_location_id;
2690
2691 END IF;
2692
2693 ELSIF p_reptng_emp = 'ALL_EMPLOYEES'
2694 THEN
2695 IF g_debug THEN
2696 hr_utility.set_location(l_proc, 30);
2697 END IF;
2698
2699 l_all_direct_sql := build_all_person_sql(l_all_direct_sql, l_org_id, l_location_id);
2700
2701 IF g_debug THEN
2702 hr_utility.trace('l_all_direct_sql:'||l_all_direct_sql);
2703 END IF;
2704
2705
2706 IF l_org_id = -1 AND l_location_id = -1 THEN
2707 EXECUTE IMMEDIATE l_all_direct_sql BULK COLLECT INTO l_person_det USING
2708 p_start_date, p_end_date,
2709 p_supervisor_id, p_supervisor_id,
2710 p_end_date,p_start_date,
2711 p_end_date,p_start_date;
2712
2713 ELSIF l_org_id <> -1 AND l_location_id = -1 THEN
2714 EXECUTE IMMEDIATE l_all_direct_sql BULK COLLECT INTO l_person_det USING
2715 p_start_date, p_end_date,
2716 p_supervisor_id, p_supervisor_id,
2717 p_end_date,p_start_date,
2718 p_end_date,p_start_date,
2719 p_organization_id;
2720
2721 ELSIF l_org_id = -1 AND l_location_id <> -1 THEN
2722 EXECUTE IMMEDIATE l_all_direct_sql BULK COLLECT INTO l_person_det USING
2723 p_start_date, p_end_date,
2724 p_supervisor_id,p_supervisor_id,
2725 p_end_date,p_start_date,
2726 p_end_date,p_start_date,
2727 p_location_id;
2728
2729 ELSE
2730 EXECUTE IMMEDIATE l_all_direct_sql BULK COLLECT INTO l_person_det USING
2731 p_start_date, p_end_date,
2732 p_supervisor_id, p_supervisor_id,
2733 p_end_date,p_start_date,
2734 p_end_date,p_start_date,
2735 p_organization_id, p_location_id;
2736
2737 END IF;
2738
2739 END IF;
2740
2741 IF l_person_det.COUNT > 0
2742 THEN
2743
2744 IF g_debug THEN
2745 hr_utility.set_location(l_proc, 40);
2746 END IF;
2747
2748 FOR l_index in l_person_det.First..l_person_det.Last
2749 LOOP
2750
2751 hxc_preference_evaluation.resource_preferences(p_resource_id => l_person_det(l_index).person_id,
2752 p_start_evaluation_date => l_person_det(l_index).start_date,
2753 p_end_evaluation_date => l_person_det(l_index).end_date,
2754 p_pref_table => pref_table);
2755 IF pref_table.COUNT > 0
2756 THEN
2757 FOR i IN pref_table.FIRST..pref_table.LAST
2758 LOOP
2759 IF pref_table(i).preference_code = 'TC_W_TCRD_PERIOD'
2760 THEN
2761 l_person_det(l_index).recurring_period_id := pref_table(i).attribute1;
2762 ELSIF pref_table(i).preference_code = 'TS_PER_APPLICATION_SET'
2763 THEN
2764 l_person_det(l_index).application_set_id := pref_table(i).attribute1;
2765 END IF;
2766 END LOOP;
2767 END IF;
2768 END LOOP;
2769 IF g_debug THEN
2770 hr_utility.set_location(l_proc, 70);
2771 END IF;
2772 --DELETE FROM HXC_TEMP_TCD;
2773 FOR i IN l_person_det.FIRST..l_person_det.LAST
2774 LOOP
2775
2776
2777
2778 if(l_person_det(i).start_date > p_start_date) then
2779 l_flag := 'R'; --Midperiod hire
2780 elsif (l_person_det(i).end_date < p_end_date) then
2781 l_flag := 'M'; --Midperiod terminate
2782 else
2783 l_flag := 'T';
2784 end if ;
2785
2786
2787 INSERT INTO HXC_TEMP_TCD
2788 (resource_id,
2789 start_time,
2790 stop_time,
2791 approval_status,
2792 user_id,
2793 recurring_period_id,
2794 timecard_exist,
2795 person_number,
2796 full_name,
2797 payroll_name,
2798 organization_name,
2799 location_name,
2800 supervisor_id,
2801 supervisor_name,
2802 application,
2803 period_exist
2804 )
2805 VALUES
2806 (
2807 l_person_det(i).person_id,
2808 l_person_det(i).start_date,
2809 l_person_det(i).end_date,
2810 'NOTENTERED',
2811 l_user_id,
2812 l_person_det(i).recurring_period_id,
2813 'N',
2814 l_person_det(i).person_id,
2815 l_person_det(i).person_id,
2816 l_person_det(i).payroll_id,
2817 l_person_det(i).organization_id,
2818 l_person_det(i).location_id,
2819 l_person_det(i).supervisor_id,
2820 l_person_det(i).supervisor_id,
2821 l_person_det(i).application_set_id,
2822 l_flag
2823 );
2824 END LOOP;
2825
2826
2827 IF g_debug THEN
2828 hr_utility.set_location(l_proc, 80);
2829 END IF;
2830
2831 END IF;
2832
2833 IF p_recurring_period IS NOT NULL
2834 THEN
2835 IF g_debug THEN
2836 hr_utility.set_location(l_proc, 90);
2837 END IF;
2838 delete_person_temp(p_recurring_period,l_user_id);
2839 END IF;
2840
2841 IF g_debug THEN
2842 hr_utility.set_location(l_proc, 100);
2843 END IF;
2844
2845 OPEN c_get_timecard(l_user_id);
2846 LOOP
2847 FETCH c_get_timecard BULK COLLECT INTO l_timecards_ne LIMIT 500;
2848 EXIT WHEN l_timecards_ne.COUNT = 0;
2849
2850 IF l_timecards_ne.COUNT > 0
2851 THEN
2852
2853 IF g_debug THEN
2854 hr_utility.set_location(l_proc, 110);
2855 hr_utility.trace('Timecard Count from c_get_timecard :'||l_timecards_ne.COUNT);
2856 END IF;
2857
2858 FOR i IN l_timecards_ne.FIRST..l_timecards_ne.LAST
2859 LOOP
2860 IF (l_timecards_ne(i).first_st_time IS NOT NULL)
2861 AND (l_timecards_ne(i).first_end_time IS NOT NULL)
2862 --AND (l_timecards_ne(i).first_st_time < l_timecards_ne(i).first_end_time)
2863 --AND (l_timecards_ne(i).period_exist <> 'R')
2864 THEN
2865 INSERT INTO HXC_TEMP_TCD
2866 (resource_id,
2867 start_time,
2868 stop_time,
2869 approval_status,
2870 user_id,
2871 recurring_period_id,
2872 timecard_exist,
2873 person_number,
2874 full_name,
2875 payroll_name,
2876 organization_name,
2877 location_name,
2878 supervisor_id,
2879 supervisor_name,
2880 application,
2881 period_exist
2882 )
2883 VALUES
2884 (
2885 l_timecards_ne(i).resource_id,
2886 l_timecards_ne(i).first_st_time,
2887 l_timecards_ne(i).first_end_time,
2888 'NOTENTERED',
2889 l_user_id,
2890 l_timecards_ne(i).recurring_period_id,
2891 'N',
2892 l_timecards_ne(i).resource_id,
2893 l_timecards_ne(i).resource_id,
2894 l_timecards_ne(i).payroll_name,
2895 l_timecards_ne(i).organization_name,
2896 l_timecards_ne(i).location_name,
2897 l_timecards_ne(i).supervisor_id,
2898 l_timecards_ne(i).supervisor_id,
2899 l_timecards_ne(i).application,
2900 lower(l_timecards_ne(i).period_exist)
2901 );
2902 END IF;
2903
2904 IF (l_timecards_ne(i).next_st_time IS NOT NULL)
2905 AND (l_timecards_ne(i).next_end_time IS NOT NULL)
2906 AND ( l_timecards_ne(i).next_st_time < l_timecards_ne(i).next_end_time)
2907 THEN
2908
2909 INSERT INTO HXC_TEMP_TCD
2910 (resource_id,
2911 start_time,
2912 stop_time,
2913 approval_status,
2914 user_id,
2915 recurring_period_id,
2916 timecard_exist,
2917 person_number,
2918 full_name,
2919 payroll_name,
2920 organization_name,
2921 location_name,
2922 supervisor_id,
2923 supervisor_name,
2924 application,
2925 period_exist
2926 )
2927 VALUES
2928 (
2929 l_timecards_ne(i).resource_id,
2930 l_timecards_ne(i).next_st_time,
2931 l_timecards_ne(i).next_end_time,
2932 'NOTENTERED',
2933 l_user_id,
2934 l_timecards_ne(i).recurring_period_id,
2935 'N',
2936 l_timecards_ne(i).resource_id,
2937 l_timecards_ne(i).resource_id,
2938 l_timecards_ne(i).payroll_name,
2939 l_timecards_ne(i).organization_name,
2940 l_timecards_ne(i).location_name,
2941 l_timecards_ne(i).supervisor_id,
2942 l_timecards_ne(i).supervisor_id,
2943 l_timecards_ne(i).application,
2944 lower(l_timecards_ne(i).period_exist)
2945 );
2946 END IF;
2947
2948 END LOOP;
2949
2950 FOR i IN l_timecards_ne.FIRST..l_timecards_ne.LAST
2951 LOOP
2952 INSERT INTO HXC_TEMP_TCD
2953 (resource_id,
2954 start_time,
2955 stop_time,
2956 timecard_id,
2957 timecard_ovn,
2958 approval_status,
2959 user_id,
2960 recurring_period_id,
2961 timecard_exist,
2962 person_number,
2963 full_name,
2964 payroll_name,
2965 organization_name,
2966 location_name,
2967 supervisor_id,
2968 supervisor_name,
2969 application,
2970 period_exist
2971 )
2972 VALUES
2973 (
2974 l_timecards_ne(i).resource_id,
2975 l_timecards_ne(i).start_time,
2976 l_timecards_ne(i).stop_time,
2977 l_timecards_ne(i).timecard_id,
2978 l_timecards_ne(i).timecard_ovn,
2979 l_timecards_ne(i).approval_status,
2980 l_user_id,
2981 l_timecards_ne(i).recurring_period_id,
2982 'Y',
2983 l_timecards_ne(i).resource_id,
2984 l_timecards_ne(i).resource_id,
2985 l_timecards_ne(i).payroll_name,
2986 l_timecards_ne(i).organization_name,
2987 l_timecards_ne(i).location_name,
2988 l_timecards_ne(i).supervisor_id,
2989 l_timecards_ne(i).supervisor_id,
2990 l_timecards_ne(i).application,
2991 'P'
2992 );
2993 END LOOP;
2994
2995 END IF;
2996
2997 END LOOP;
2998 CLOSE c_get_timecard;
2999
3000
3001 IF g_debug THEN
3002 hr_utility.set_location(l_proc, 120);
3003 END IF;
3004 delete_temp_extra_rec(l_user_id);
3005 -- delete_extra_rec(l_user_id);
3006 update_assignment_changes(l_user_id);
3007 update_person_det(l_user_id);
3008 update_payroll(l_user_id);
3009 update_organization(l_user_id);
3010 update_location(l_user_id);
3011 update_supervisor(l_user_id);
3012 update_person_types(l_user_id);
3013 update_application(l_user_id);
3014 update_last_update_det(l_user_id);
3015 update_last_update_by(l_user_id);
3016 insert_notification_id(l_user_id);
3017 insert_approval_meaning(l_user_id);
3018 insert_timecard_exist(l_user_id);
3019 insert_timecard_not_exist(l_user_id);
3020 delete_extra_rec(l_user_id);
3021 insert_timecard_count(l_user_id);
3022 update_total(l_user_id);
3023 commit;
3024
3025 p_msg:='yes';
3026 p_level:='no';
3027
3028 IF g_debug THEN
3029 hr_utility.set_location('Leaving:'||l_proc, 130);
3030 END IF;
3031 --hr_utility.trace_off();
3032 EXCEPTION
3033 when connect_by_detected
3034 then
3035
3036 IF g_debug THEN
3037 hr_utility.set_location(l_proc, 140);
3038 END IF;
3039 p_msg := 'CONNECT_BY_ERROR';
3040 p_level := 'ERROR';
3041
3042 when others
3043 then
3044 hr_utility.trace('Other exception occured');
3045 raise;
3046
3047 END GO;
3048
3049 PROCEDURE generate_supervisor_xml(
3050 p_start_time IN VARCHAR2 DEFAULT NULL,
3051 p_stop_time IN VARCHAR2 DEFAULT NULL,
3052 p_supervisor_id IN VARCHAR2 DEFAULT NULL,
3053 p_reporting_emp IN VARCHAR2 DEFAULT NULL,
3054 p_recurring_period IN VARCHAR2 DEFAULT NULL,
3055 p_timecard_status IN VARCHAR2 DEFAULT NULL,
3056 p_location IN VARCHAR2 DEFAULT NULL,
3057 p_organization IN VARCHAR2 DEFAULT NULL,
3058 p_column_name IN VARCHAR2 DEFAULT NULL,
3059 p_supervisor_param IN VARCHAR2,
3060 p_pre_xml OUT NOCOPY CLOB
3061 )
3062 IS
3063
3064 l_proc varchar2(72);
3065 l_icx_date_format VARCHAR2(20);
3066 l_language_code VARCHAR2(30);
3067 l_report_info VARCHAR2(100);
3068
3069 query1 varchar2(200);
3070
3071 qryCtx1 dbms_xmlgen.ctxType;
3072 xmlresult1 CLOB;
3073 l_pre_xml CLOB DEFAULT empty_clob();
3074 l_resultOffset int;
3075
3076 l_dynamic_sql varchar2(3000):= '
3077 SELECT person_type
3078 ,person_name
3079 ,person_number
3080 ,meaning
3081 ,supervisor
3082 ,organization
3083 ,location
3084 ,payroll_name
3085 ,application
3086 ,period_start_date
3087 ,period_end_date
3088 ,last_modified_by
3089 ,last_modified_date
3090 FROM hxc_tcd_timecards htt
3091 WHERE user_id = fnd_global.LOGIN_ID';
3092
3093 l_dynamic_cursor SYS_REFCURSOR;
3094
3095 CURSOR c_report(supervisor_id number, column_name varchar2 default null, approval_status varchar2)
3096 IS
3097 SELECT person_type
3098 ,person_name
3099 ,person_number
3100 ,meaning
3101 ,supervisor
3102 ,organization
3103 ,location
3104 ,payroll_name
3105 ,application
3106 ,period_start_date
3107 ,period_end_date
3108 ,last_modified_by
3109 ,last_modified_date
3110 FROM hxc_tcd_timecards htt
3111 WHERE user_id = fnd_global.login_id
3112 AND supervisor_id = supervisor_id
3113 AND approval_status = 'SUBMITTED';
3114 /*AND nvl (column_name
3115 ,1) = nvl2 (column_name
3116 ,approval_status
3117 ,1);
3118 */
3119 TYPE r_details IS RECORD
3120 (
3121 person_type varchar2(50),
3122 person_name varchar2(250),
3123 person_number varchar2(50),
3124 meaning varchar2(50),
3125 supervisor varchar2(250),
3126 organization varchar2(250),
3127 location varchar2(250),
3128 payroll_name varchar2(250),
3129 application varchar2(250),
3130 start_time varchar2(50),
3131 stop_time varchar2(50),
3132 last_modified_by varchar2(50),
3133 last_modified_date varchar2(50));
3134
3135 TYPE t_details IS TABLE OF r_details
3136 INDEX BY BINARY_INTEGER;
3137
3138 timecard_details_tab t_details;
3139
3140 l_time_period varchar2(50);
3141 l_supervisor_name varchar2(250);
3142 l_organization_name varchar2(250);
3143 l_location_name varchar2(250);
3144 BEGIN
3145
3146 IF g_debug THEN
3147 l_proc := g_package||' Generate XML';
3148 hr_utility.set_location('Entering : '|| l_proc, 10);
3149 END IF;
3150
3151
3152 SELECT full_name INTO l_supervisor_name
3153 FROM per_all_people_f
3154 WHERE person_id = p_supervisor_param
3155 AND trunc (sysdate) BETWEEN effective_start_date
3156 AND effective_end_date;
3157
3158 IF p_organization IS NOT NULL
3159 THEN
3160 SELECT DISTINCT
3161 name INTO l_organization_name
3162 FROM hr_all_organization_units_tl
3163 WHERE organization_id = p_organization
3164 AND language = userenv('LANG');
3165 END IF;
3166
3167 IF p_location IS NOT NULL
3168 THEN
3169 SELECT DISTINCT
3170 location_code INTO l_location_name
3171 FROM hr_locations_all_tl
3172 WHERE location_id = p_location
3173 AND language = userenv('LANG');
3174 END IF;
3175
3176 fnd_profile.get('ICX_DATE_FORMAT_MASK', l_icx_date_format);
3177 l_language_code := USERENV('LANG');
3178
3179 IF g_debug THEN
3180 l_proc := g_package||' Generating XML';
3181 hr_utility.set_location('Entering : '|| l_proc, 20);
3182 END IF;
3183
3184 l_report_info := '<?xml version="1.0" encoding="UTF-8"?> <HXCTCDRPT> ';
3185
3186 query1 := 'SELECT '
3187 || 'user_name INITIATED_BY, '
3188 || 'TO_CHAR(SYSDATE, ''' || l_icx_date_format || ''') RUN_DATE '
3189 || 'from fnd_user '
3190 || 'where user_id = fnd_global.user_id' ;
3191
3192 qryCtx1 := dbms_xmlgen.newContext(query1);
3193 dbms_xmlgen.setRowTag(qryCtx1, NULL);
3194 dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
3195 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
3196 dbms_xmlgen.closecontext(qryctx1);
3197 l_pre_xml := xmlresult1;
3198 dbms_lob.write(l_pre_xml, length(l_report_info), 1, l_report_info);
3199 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
3200 dbms_lob.copy(l_pre_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, length(l_report_info), l_resultOffset +1);
3201
3202 IF g_debug THEN
3203 l_proc := g_package||' Generate XML';
3204 hr_utility.set_location('Entering : '|| l_proc, 30);
3205 END IF;
3206
3207 l_time_period := p_start_time ||' - '||p_stop_time;
3208 dbms_lob.writeappend(l_pre_xml, length('
3209 <L_REC_PERIOD>'||p_recurring_period||'</L_REC_PERIOD>
3210 <L_TC_PERIOD>'||l_time_period||'</L_TC_PERIOD>
3211 <L_SUPERVISOR_NAME>'||l_supervisor_name||'</L_SUPERVISOR_NAME>
3212 <L_LOCATION_NAME>'||l_location_name||'</L_LOCATION_NAME>
3213 <L_ORG_NAME>'||l_organization_name||'</L_ORG_NAME>
3214 '),
3215
3216 '
3217 <L_REC_PERIOD>'||p_recurring_period||'</L_REC_PERIOD>
3218 <L_TC_PERIOD>'||l_time_period||'</L_TC_PERIOD>
3219 <L_SUPERVISOR_NAME>'||l_supervisor_name||'</L_SUPERVISOR_NAME>
3220 <L_LOCATION_NAME>'||l_location_name||'</L_LOCATION_NAME>
3221 <L_ORG_NAME>'||l_organization_name||'</L_ORG_NAME>
3222 ');
3223
3224
3225 dbms_lob.writeappend(l_pre_xml, length('<LIST_G_PERSON_TYPE> '), '<LIST_G_PERSON_TYPE> ');
3226
3227
3228
3229 IF p_supervisor_id IS NOT NULL
3230 THEN
3231 IF g_debug THEN
3232 hr_utility.trace('PNS CHECK p_supervisor_id'||p_supervisor_id);
3233 END IF;
3234 l_dynamic_sql := l_dynamic_sql || ' AND supervisor_id =' || p_supervisor_id;
3235 END IF;
3236 IF g_debug THEN
3237 hr_utility.trace('PNS CHECK p_column_name out'||p_column_name);
3238 END IF;
3239
3240 IF p_column_name IS NOT NULL
3241 THEN
3242 IF g_debug THEN
3243 hr_utility.trace('PNS CHECK p_column_name in'||p_column_name);
3244 END IF;
3245
3246 l_dynamic_sql := l_dynamic_sql || ' AND approval_status = ''' || p_timecard_status ||'''';
3247 END IF;
3248
3249 IF g_debug THEN
3250 hr_utility.trace('PNS CHECK l_dynamic_sql'||l_dynamic_sql);
3251 END IF;
3252
3253 l_dynamic_sql := l_dynamic_sql || ' ORDER BY period_start_date
3254 , period_end_date
3255 , meaning
3256 , person_name ASC';
3257
3258 OPEN l_dynamic_cursor FOR l_dynamic_sql;
3259 LOOP
3260 FETCH l_dynamic_cursor BULK COLLECT INTO timecard_details_tab;
3261 EXIT WHEN timecard_details_tab.COUNT = 0;
3262
3263 FOR l_index IN 1..timecard_details_tab.COUNT
3264 LOOP
3265
3266 dbms_lob.writeappend(l_pre_xml, length('<G_PERSON_TYPE>
3267 <PERSON_TYPE>' || timecard_details_tab(l_index).person_type || '</PERSON_TYPE>
3268 <PERSON_NAME>' || timecard_details_tab(l_index).person_name || '</PERSON_NAME>
3269 <PERSON_NUMBER>' || timecard_details_tab(l_index).person_number || '</PERSON_NUMBER>
3270 <APPROVAL_STATUS>' || timecard_details_tab(l_index).meaning || '</APPROVAL_STATUS>
3271 <SUPERVISOR>' || timecard_details_tab(l_index).supervisor || '</SUPERVISOR>
3272 <ORGANIZATION>' || timecard_details_tab(l_index).organization || '</ORGANIZATION>
3273 <LOCATION>' || timecard_details_tab(l_index).location || '</LOCATION>
3274 <PAYROLL>' || timecard_details_tab(l_index).payroll_name || '</PAYROLL>
3275 <APPLICATION>' || timecard_details_tab(l_index).application || '</APPLICATION>
3276 <TC_START_DATE>' || timecard_details_tab(l_index).start_time || '</TC_START_DATE>
3277 <TC_END_DATE>' || timecard_details_tab(l_index).stop_time || '</TC_END_DATE>
3278 <LAST_MODIFIED_BY>' || timecard_details_tab(l_index).last_modified_by || '</LAST_MODIFIED_BY>
3279 <LAST_MODIFIED_DATE>' || timecard_details_tab(l_index).last_modified_date || '</LAST_MODIFIED_DATE>
3280 </G_PERSON_TYPE>'),
3281
3282 '<G_PERSON_TYPE>
3283 <PERSON_TYPE>' || timecard_details_tab(l_index).person_type || '</PERSON_TYPE>
3284 <PERSON_NAME>' || timecard_details_tab(l_index).person_name || '</PERSON_NAME>
3285 <PERSON_NUMBER>' || timecard_details_tab(l_index).person_number || '</PERSON_NUMBER>
3286 <APPROVAL_STATUS>' || timecard_details_tab(l_index).meaning || '</APPROVAL_STATUS>
3287 <SUPERVISOR>' || timecard_details_tab(l_index).supervisor || '</SUPERVISOR>
3288 <ORGANIZATION>' || timecard_details_tab(l_index).organization || '</ORGANIZATION>
3289 <LOCATION>' || timecard_details_tab(l_index).location || '</LOCATION>
3290 <PAYROLL>' || timecard_details_tab(l_index).payroll_name || '</PAYROLL>
3291 <APPLICATION>' || timecard_details_tab(l_index).application || '</APPLICATION>
3292 <TC_START_DATE>' || timecard_details_tab(l_index).start_time || '</TC_START_DATE>
3293 <TC_END_DATE>' || timecard_details_tab(l_index).stop_time || '</TC_END_DATE>
3294 <LAST_MODIFIED_BY>' || timecard_details_tab(l_index).last_modified_by || '</LAST_MODIFIED_BY>
3295 <LAST_MODIFIED_DATE>' || timecard_details_tab(l_index).last_modified_date || '</LAST_MODIFIED_DATE>
3296 </G_PERSON_TYPE>');
3297
3298 END LOOP;
3299
3300 END LOOP;
3301
3302 CLOSE l_dynamic_cursor;
3303
3304 dbms_lob.writeappend(l_pre_xml, length('</LIST_G_PERSON_TYPE>
3305 </HXCTCDRPT>
3306 '), '</LIST_G_PERSON_TYPE>
3307 </HXCTCDRPT>
3308 ');
3309
3310 p_pre_xml := l_pre_xml;
3311 IF g_debug THEN
3312 l_proc := g_package||' Generate XML';
3313 hr_utility.set_location('Leaving : '|| l_proc, 40);
3314 END IF;
3315
3316 END generate_supervisor_xml;
3317
3318 END HXC_SUPERVISOR_DASHBOARD;