1 package body hxc_tcsummary_migrate as
2 /* $Header: hxcsummig.pkb 120.7 2007/07/13 09:27:29 asrajago noship $ */
3
4 procedure delete_timecards is
5
6 cursor c_timecards is
7 select ts.timecard_id
8 ,ts.timecard_ovn
9 from hxc_timecard_summary ts
10 where not exists (select 'Y'
11 from hxc_time_building_blocks tbb
12 where tbb.time_building_block_id = ts.timecard_id and
13 tbb.object_version_number = ts.timecard_ovn and
14 tbb.scope = 'TIMECARD' and
15 tbb.date_to = hr_general.end_of_time);
16
17 cursor c_deleted_timecards is
18 select ts.timecard_id
19 ,ts.timecard_ovn
20 from hxc_timecard_summary ts
21 where exists (select 'Y'
22 from hxc_time_building_blocks tbb
23 where tbb.time_building_block_id = ts.timecard_id and
24 tbb.object_version_number = ts.timecard_ovn and
25 tbb.scope = 'TIMECARD' and
26 tbb.date_to <> hr_general.end_of_time);
27
28
29 l_blocks hxc_block_table_type;
30
31 Begin
32
33 for timecard_rec in c_timecards loop
34
35 hxc_timecard_summary_api.delete_timecard(timecard_rec.timecard_id);
36
37 end loop;
38
39 for timecard_rec in c_deleted_timecards loop
40
41 hxc_timecard_summary_api.delete_timecard(timecard_rec.timecard_id);
42
43 end loop;
44
45 End delete_timecards;
46
47 procedure delete_templates is
48
49 cursor c_templates is
50 select ts.template_id
51 ,ts.template_ovn
52 from hxc_template_summary ts
53 where not exists (select 'Y'
54 from hxc_time_building_blocks tbb
55 where tbb.time_building_block_id = ts.template_id and
56 tbb.object_version_number = ts.template_ovn and
57 tbb.scope = 'TIMECARD_TEMPLATE' and
58 tbb.date_to = hr_general.end_of_time);
59
60 cursor c_deleted_templates is
61 select ts.template_id
62 ,ts.template_ovn
63 from hxc_template_summary ts
64 where exists (select 'Y'
65 from hxc_time_building_blocks tbb
66 where tbb.time_building_block_id = ts.template_id and
67 tbb.object_version_number = ts.template_ovn and
68 tbb.scope = 'TIMECARD_TEMPLATE' and
69 tbb.date_to <> hr_general.end_of_time);
70
71 -- l_blocks hxc_block_table_type;
72
73 Begin
74
75 for template_rec in c_templates loop
76
77 hxc_template_summary_api.delete_template(template_rec.template_id);
78
79 end loop;
80
81 for template_rec in c_deleted_templates loop
82
83 hxc_template_summary_api.delete_template(template_rec.template_id);
84
85 end loop;
86
87 End delete_templates;
88
89 procedure timecard_summary(p_resource_id in hxc_time_building_blocks.resource_id%type) is
90
91 cursor c_timecards(p_rid in number) is
92 select tbb.time_building_block_id
93 ,tbb.object_version_number
94 from hxc_time_building_blocks tbb
95 where tbb.resource_id = p_rid and
96 tbb.date_to = hr_general.end_of_time and
97 tbb.scope = 'TIMECARD' and
98 not exists
99 (select 'Y'
100 from hxc_timecard_summary
101 where timecard_id = tbb.time_building_block_id and
102 timecard_ovn = tbb.object_version_number);
103
104 l_blocks hxc_block_table_type;
105
106
107 Begin
108
109 for timecard_rec in c_timecards(p_resource_id) loop
110 hxc_timecard_summary_api.timecard_deposit
111 (p_timecard_id => timecard_rec.time_building_block_id
112 ,p_mode => hxc_timecard_summary_pkg.c_migration_mode
113 ,p_approval_item_type =>null
114 ,p_approval_process_name=> null
115 ,p_approval_item_key => null
116 ,p_tk_audit_item_type => null
117 ,p_tk_audit_process_name => null
118 ,p_tk_audit_item_key => null
119 );
120 end loop;
121
122 End timecard_summary;
123
124 procedure template_summary(p_resource_id in hxc_time_building_blocks.resource_id%type) is
125
126 cursor c_templates(p_rid in number) is
127 select tbb.time_building_block_id
128 ,tbb.object_version_number
129 from hxc_time_building_blocks tbb
130 where tbb.resource_id = p_rid and
131 tbb.date_to = hr_general.end_of_time and
132 tbb.scope = 'TIMECARD_TEMPLATE'
133 and resource_id <>-1 and
134 not exists
135 (select 'Y'
136 from hxc_template_summary
137 where template_id = tbb.time_building_block_id and
138 template_ovn = tbb.object_version_number);
139
140 l_blocks hxc_block_table_type;
141
142 Begin
143
144 for template_rec in c_templates(p_resource_id) loop
145 hxc_template_summary_api.template_deposit
146 (p_template_id => template_rec.time_building_block_id
147 ,p_template_ovn =>template_rec.object_version_number
148 );
149 end loop;
150
151 End template_summary;
152
153 Procedure application_period_summary(p_resource_id in hxc_time_building_blocks.resource_id%type) is
154
155
156 type app_period_list is table of hxc_time_building_blocks.time_building_block_id%type;
157
158 l_app_periods app_period_list;
159
160 cursor c_app_periods(p_resource_id in hxc_time_building_blocks.resource_id%type) is
161 select distinct tbb.time_building_block_id
162 from hxc_time_building_blocks tbb
163 ,hxc_time_attribute_usages tau
164 ,hxc_time_attributes ta
165 where tbb.scope = 'APPLICATION_PERIOD' and
166 tbb.date_to = hr_general.end_of_time and
167 tbb.resource_id = p_resource_id and
168 tau.time_building_block_id = tbb.time_building_block_id and
169 tau.time_building_block_ovn = tbb.object_version_number and
170 ta.attribute_category = 'APPROVAL' and
171 ta.time_attribute_id = tau.time_attribute_id and
172 not exists
173 (select 'Y'
174 from hxc_app_period_summary aps
175 where aps.application_period_id = tbb.time_building_block_id and
176 aps.application_period_ovn = tbb.object_version_number)
177 order by 1;
178
179 l_index number := 1;
180
181 Begin
182
183 open c_app_periods(p_resource_id);
184 fetch c_app_periods bulk collect
185 into l_app_periods;
186 close c_app_periods;
187
188 l_index := l_app_periods.first;
189
190 Loop
191 Exit when not l_app_periods.exists(l_index);
192 hxc_app_period_summary_api.app_period_create(l_app_periods(l_index)
193 ,hxc_timecard_summary_pkg.c_migration_mode);
194 l_index := l_app_periods.next(l_index);
195 end loop;
196
197 End application_period_summary;
198
199
200 procedure run_migration is
201
202 cursor c_valid_resources is
203 select distinct resource_id
204 from hxc_time_building_blocks tbb
205 where tbb.scope = 'TIMECARD' and
206 tbb.date_to = hr_general.end_of_time and
207 not exists
208 (select 'Y'
209 from hxc_timecard_summary ts
210 where tbb.time_building_block_id = ts.timecard_id and
211 tbb.object_version_number = ts.timecard_ovn);
212
213 cursor c_timecard_summary is
214 select 1 from dual
215 where exists ( select 'x' from hxc_timecard_summary);
216
217 cursor c_template_summary is
218 select 1 from dual
219 where exists ( select 'x' from hxc_template_summary);
220
221 cursor c_valid_template_resources is
222 select distinct resource_id
223 from hxc_time_building_blocks tbb
224 where tbb.scope = 'TIMECARD_TEMPLATE' and
225 tbb.date_to = hr_general.end_of_time and
226 not exists
227 (select 'Y'
228 from hxc_template_summary ts
229 where tbb.time_building_block_id = ts.template_id and
230 tbb.object_version_number = ts.template_ovn);
231 l_dummy number;
232
233 l_timecard_count number;
234
235 l_timecard_found boolean;
236 l_template_found boolean;
237
238 begin
239
240 l_timecard_found := false;
241 l_template_found := false;
242
243 -- even if there is a single record in hxc_timecard_summary, we are not going
244 -- to allow the migration of timecards.
245 open c_timecard_summary;
246 fetch c_timecard_summary
247 into l_dummy;
248
249 if c_timecard_summary%found then
250 close c_timecard_summary;
251 l_timecard_found := true;
252 --return;
253 else
254 close c_timecard_summary;
255 end if;
256
257 open c_template_summary;
258 fetch c_template_summary
259 into l_dummy;
260
261 if c_template_summary%found then
262 close c_template_summary;
263 l_template_found := true;
264 --return;
265 else
266 close c_template_summary;
267 end if;
268
269 if (l_timecard_found) and (l_template_found) then
270 return;
271 end if;
272
273 if (l_timecard_found = false) then
274 for resource_rec in c_valid_resources loop
275
276 --
277 -- 1. Create all the timecard summary information
278 --
279 timecard_summary(resource_rec.resource_id);
280
281 --
282 -- 2. Create all the application period summary information
283 -- and populate other link tables.
284 --
285 application_period_summary(resource_rec.resource_id);
286
287
288 end loop;
289
290 --
291 -- 3. Clean up summary tables if the migration has been run before
292 --
293
294 delete_timecards;
295 end if;
296
297 if (l_template_found = false) then
298
299 for resource_rec in c_valid_template_resources loop
300
301 --
302 -- 1. Create all the template summary information
303 --
304 template_summary(resource_rec.resource_id);
305
306 end loop;
307 delete_templates;
308 end if;
309
310 end run_migration;
311
312
313 function is_process_time_over(p_process_end_time in date) return boolean is
314 begin
315 if nvl(p_process_end_time, sysdate) < sysdate then
316 return true;
317 else
318 return false;
319 end if;
320 end;
321
322 PROCEDURE update_appl_set_id(p_time_building_block_id in hxc_time_building_blocks.time_building_block_id%type
323 ,p_start_date in date
324 ,p_end_date in date
325 ,p_resource_id in hxc_time_building_blocks.resource_id%type
326 ,p_object_version_number in hxc_time_building_blocks.object_version_number%type
327 ,p_upg_count out nocopy number) IS
328
329 CURSOR C_Time_BB_Ids(p_Building_Block_Id number, p_Object_Version_Number number) is
330 SELECT tbb1.time_building_block_id
331 ,tbb1.object_version_number
332 ,tbb1.scope
333 FROM hxc_time_building_blocks tbb1
334 WHERE scope IN ('TIMECARD', 'DAY', 'DETAIL') AND
335 application_set_id IS NULL
336 START WITH ((tbb1.time_building_block_id = p_building_block_id) AND
337 (tbb1.object_version_number = p_object_version_number))
338 CONNECT BY PRIOR tbb1.time_building_block_id =
339 tbb1.parent_building_block_id AND
340 PRIOR tbb1.object_version_number =
341 tbb1.parent_building_block_ovn;
342
343 CURSOR C_get_Appl_Set_Id(p_rec_id number, p_start_time date, p_stop_time date) is
344 SELECT a
345 FROM (SELECT application_set_id a
346 ,COUNT(*) cnt
347 FROM hxc_application_set_comps_v
348 WHERE time_recipient_id IN
349 (SELECT DISTINCT attribute1
350 FROM hxc_time_attributes
351 WHERE time_attribute_id IN
352 (SELECT time_attribute_id
353 FROM hxc_time_attribute_usages
354 WHERE time_building_block_id IN
355 (SELECT htb2.time_building_block_id
356 FROM hxc_time_building_blocks htb2
357 WHERE htb2.scope = 'APPLICATION_PERIOD' AND
358 htb2.resource_id = p_rec_id AND
359 TRUNC(htb2.start_time) =
360 p_start_time AND
361 TRUNC(htb2.stop_time) =
362 TRUNC(p_stop_time) AND
363 htb2.object_version_number =
364 (SELECT MAX(hb.object_version_number)
365 FROM hxc_time_building_blocks hb
366 WHERE hb.time_building_block_id =
367 htb2.time_building_block_id AND
368 hb.start_time =
369 htb2.start_time AND
370 hb.stop_time =
371 htb2.stop_time AND
372 hb.resource_id =
373 htb2.resource_id AND
374 hb.scope =
375 'APPLICATION_PERIOD'))) AND
376 attribute_category = 'APPROVAL')
377 GROUP BY application_set_id)
378 WHERE cnt =
379 (SELECT COUNT(distinct attribute1)
380 FROM hxc_time_attributes
381 WHERE time_attribute_id IN
382 (SELECT time_attribute_id
383 FROM hxc_time_attribute_usages
384 WHERE time_building_block_id IN
385 (SELECT htb2.time_building_block_id
386 FROM hxc_time_building_blocks htb2
387 WHERE htb2.scope = 'APPLICATION_PERIOD' AND
388 htb2.resource_id = p_rec_id AND
389 TRUNC(htb2.start_time) = p_start_time AND
390 TRUNC(htb2.stop_time) =
391 TRUNC(p_stop_time) AND
392 htb2.object_version_number =
393 (SELECT MAX(hb.object_version_number)
394 FROM hxc_time_building_blocks hb
395 WHERE hb.time_building_block_id =
396 htb2.time_building_block_id AND
397 hb.start_time = htb2.start_time AND
398 hb.stop_time = htb2.stop_time AND
399 hb.resource_id = htb2.resource_id AND
400 hb.scope = 'APPLICATION_PERIOD'))) AND
401 attribute_category = 'APPROVAL') AND
402 a NOT IN
403 (SELECT application_set_id a
404 FROM hxc_application_set_comps_v
405 WHERE time_recipient_id NOT IN
406 (SELECT DISTINCT attribute1
407 FROM hxc_time_attributes
408 WHERE time_attribute_id IN
409 (SELECT time_attribute_id
410 FROM hxc_time_attribute_usages
411 WHERE time_building_block_id IN
412 (SELECT htb2.time_building_block_id
413 FROM hxc_time_building_blocks htb2
414 WHERE htb2.scope = 'APPLICATION_PERIOD' AND
415 htb2.resource_id = p_rec_id AND
416 TRUNC(htb2.start_time) =
417 p_start_time AND
418 TRUNC(htb2.stop_time) =
419 TRUNC(p_stop_time) AND
420 htb2.object_version_number =
421 (SELECT MAX(hb.object_version_number)
422 FROM hxc_time_building_blocks hb
423 WHERE hb.time_building_block_id =
424 htb2.time_building_block_id AND
425 hb.start_time =
426 htb2.start_time AND
427 hb.stop_time =
428 htb2.stop_time AND
429 hb.resource_id =
430 htb2.resource_id AND
431 hb.scope =
432 'APPLICATION_PERIOD'))) AND
433 attribute_category = 'APPROVAL'));
434
435 l_application_set_id hxc_time_building_blocks.APPLICATION_SET_ID%TYPE;
436 Begin
437
438 OPEN C_get_Appl_Set_Id(p_resource_id, p_start_date, p_end_date);
439 FETCH C_get_Appl_Set_Id into l_application_set_id;
440 CLOSE C_get_Appl_Set_Id;
441
442 --
443 -- Call to update the value in the table
444 FOR C2 in C_Time_BB_Ids(p_time_building_block_id
445 ,p_object_version_number) LOOP
446
447 BEGIN
448 update hxc_time_building_blocks
449 set application_set_id = l_application_set_id
450 where time_building_block_id = C2.time_building_block_id and
451 object_version_number = C2.object_version_number;
452
453
454 p_upg_count:=p_upg_count+1;
455
456 IF (C2.scope = 'DETAIL') THEN
457
458 BEGIN
459 UPDATE hxc_latest_details hld
460 SET hld.application_set_id = l_application_set_id
461 WHERE hld.time_building_block_id =
462 C2.time_building_block_id AND
463 hld.object_version_number = C2.object_version_number;
464
465 EXCEPTION
466 WHEN OTHERS THEN
467 -- in case the DETAIL has not been upgraded
468 NULL;
469 END;
470
471 END IF;
472
473 EXCEPTION
474 WHEN OTHERS THEN
475 --dbms_output.put_line('Problematic IN UPD are = '||C2.time_building_block_id);
476 raise;
477 END;
478 END LOOP;
479
480 EXCEPTION
481 -- Used to trap any exceptions that may occur due to BAD TIME CARDS
482 -- in the system
483 -- i.e. There may be resources for whom as of that effective date
484 -- there does not exist any prefernece of Application Set ID
485 -- An open issue as of now as to what needs to be done for such cases
486 --
487 -- The currnet code will just ignore such cases
488 --
489 WHEN OTHERS THEN
490 --
491 -- Increment the counter to check as to how many
492 -- such cases exist in the system
493 --ct:=ct+1;
494 --
495 -- Following will print the resource ids for the problematic TIMECARDS
496 --
497 -- dbms_output.put_line('Problematic Resources are = '||C1.RESOURCE_ID);
498 raise;
499 End update_appl_set_id;
500
501
502 PROCEDURE populate_appl_set_id(p_business_group_id in number default null
503 ,p_process_end_time in date
504 ,p_start_date in date
505 ,p_end_date in date
506 ,p_upg_count out nocopy number) IS
507
508 CURSOR C_Resource_Id is
509 SELECT DISTINCT htb.time_building_block_id
510 ,htb.start_time
511 ,htb.stop_time
512 ,htb.resource_id
513 ,htb.object_version_number
514 FROM hxc_time_building_blocks htb
515 WHERE htb.scope = 'TIMECARD' AND
516 htb.application_set_id IS NULL AND
517 htb.object_version_number =
518 (SELECT MAX(hb.object_version_number)
519 FROM hxc_time_building_blocks hb
520 WHERE hb.time_building_block_id = htb.time_building_block_id AND
521 hb.start_time = htb.start_time AND
522 hb.stop_time = htb.stop_time AND
523 hb.resource_id = htb.resource_id AND
524 hb.scope = 'TIMECARD' AND
525 hb.application_set_id IS NULL) and
526 htb.start_time >= nvl(p_start_date, htb.start_time) and
527 htb.stop_time <= nvl(p_end_date, htb.stop_time) and
528 exists
529 (select 1
530 from per_all_people_f per
531 where htb.resource_id = per.person_id);
532
533 CURSOR C_Resource_Id_Bg is
534 SELECT DISTINCT htb.time_building_block_id
535 ,htb.start_time
536 ,htb.stop_time
537 ,htb.resource_id
538 ,htb.object_version_number
539 FROM hxc_time_building_blocks htb
540 WHERE htb.scope = 'TIMECARD' AND
541 htb.application_set_id IS NULL AND
542 htb.object_version_number =
543 (SELECT MAX(hb.object_version_number)
544 FROM hxc_time_building_blocks hb
545 WHERE hb.time_building_block_id = htb.time_building_block_id AND
546 hb.start_time = htb.start_time AND
547 hb.stop_time = htb.stop_time AND
548 hb.resource_id = htb.resource_id AND
549 hb.scope = 'TIMECARD' AND
550 hb.application_set_id IS NULL) and
551 htb.start_time >= nvl(p_start_date, htb.start_time) and
552 htb.stop_time <= nvl(p_end_date, htb.stop_time) and
553 exists
554 (select 1
555 from per_all_people_f per
556 where htb.resource_id = per.person_id and
557 per.business_group_id = p_business_group_id);
558 BEGIN
559
560 p_upg_count := 0;
561 IF ( p_business_group_id is null) THEN
562 FOR C1 in C_Resource_Id
563 LOOP
564 if (is_process_time_over(p_process_end_time)) then
565 commit;
566 return;
567 end if;
568 update_appl_set_id(p_time_building_block_id =>c1.time_building_block_id
569 ,p_start_date => c1.start_time
570 ,p_end_date => c1.stop_time
571 ,p_resource_id => c1.resource_id
572 ,p_object_version_number => c1.object_version_number
573 ,p_upg_count =>p_upg_count);
574 END LOOP;
575 ELSE
576 FOR C1 in C_Resource_Id_Bg
577 LOOP
578 if (is_process_time_over(p_process_end_time)) then
579 commit;
580 return;
581 end if;
582 update_appl_set_id(p_time_building_block_id =>c1.time_building_block_id
583 ,p_start_date => c1.start_time
584 ,p_end_date => c1.stop_time
585 ,p_resource_id => c1.resource_id
586 ,p_object_version_number => c1.object_version_number
587 ,p_upg_count =>p_upg_count);
588 END LOOP;
589 End if;
590 EXCEPTION
591 -- For any other Exceptions
592 WHEN Others THEN
593 --
594 -- Rollback to the start
595 --
596 raise;
597 --
598 --
599 END populate_appl_set_id;
600
601
602
603 PROCEDURE populate_details(p_business_group_id in number default null
604 ,p_start_date in date
605 ,p_end_date in date
606 ,p_process_end_time in date default null
607 ,p_detail_count out nocopy number) IS
608
609 CURSOR csr_get_resource_details IS
610 SELECT DISTINCT tbb.resource_id
611 ,tbb.time_building_Block_id
612 ,tbb.object_version_number
613 ,tbb.approval_status
614 ,tbb.application_set_id
615 ,tbb.last_update_date
616 ,tbb.resource_type
617 ,tbb.comment_text
618 ,tbb_day.start_time
619 ,tbb_day.stop_time
620 FROM hxc_time_building_blocks tbb
621 ,hxc_time_building_Blocks tbb_day
622 WHERE tbb.scope = 'DETAIL' AND
623 tbb.object_Version_number =
624 (SELECT /*+ no_unnest */
625 MAX(dovn.object_version_number)
626 FROM hxc_time_building_blocks dovn
627 WHERE dovn.time_building_block_id =
628 tbb.time_building_block_id) AND
629 tbb_day.time_building_block_id = tbb.parent_building_block_id AND
630 NOT EXISTS
631 (SELECT 'x'
632 FROM hxc_latest_details hld
633 WHERE hld.time_building_block_id = tbb.time_building_block_id) AND
634 tbb_day.object_Version_number =
635 (SELECT /*+ no_unnest */
636 MAX(dovn1.object_version_number)
637 FROM hxc_time_building_blocks dovn1
638 WHERE dovn1.time_building_block_id =
639 tbb_day.time_building_block_id) and
640 tbb_day.start_time >= nvl(p_start_date, tbb_day.start_time) and
641 tbb_day.stop_time <= nvl(p_end_date, tbb_day.stop_time) and
642 EXISTS
643 ( SELECT 'x'
644 FROM hxc_time_building_blocks tbb_timecard
645 WHERE tbb_timecard.time_building_block_id = tbb_day.parent_building_block_id
646 AND scope = 'TIMECARD' );
647
648
649
650
651 TYPE resource_id_tab IS TABLE OF hxc_time_building_blocks.resource_id%TYPE INDEX BY BINARY_INTEGER;
652 TYPE tbb_id_tab IS TABLE OF hxc_time_building_blocks.time_building_block_id%TYPE INDEX BY BINARY_INTEGER;
653 TYPE ovn_tab IS TABLE OF hxc_time_building_blocks.object_version_number%TYPE INDEX BY BINARY_INTEGER;
654 TYPE approval_status_tab IS TABLE OF hxc_time_building_blocks.approval_status%TYPE INDEX BY BINARY_INTEGER;
655 TYPE application_set_id_tab IS TABLE OF hxc_time_building_blocks.application_set_id%TYPE INDEX BY BINARY_INTEGER;
656 TYPE last_update_date_tab IS TABLE OF hxc_time_building_blocks.last_update_date%TYPE INDEX BY BINARY_INTEGER;
657 TYPE resource_type_tab IS TABLE OF hxc_time_building_blocks.resource_type%TYPE INDEX BY BINARY_INTEGER;
658 TYPE comment_text_tab IS TABLE OF hxc_time_building_blocks.comment_text%TYPE INDEX BY BINARY_INTEGER;
659 TYPE start_time_tab IS TABLE OF hxc_time_building_blocks.start_time%TYPE INDEX BY BINARY_INTEGER;
660 TYPE stop_time_tab IS TABLE OF hxc_time_building_blocks.stop_time%TYPE INDEX BY BINARY_INTEGER;
661
662 t_resource_id resource_id_tab;
663 t_tbb_id tbb_id_tab;
664 t_ovn ovn_tab;
665 t_approval_status approval_status_tab;
666 t_application_set_id application_set_id_tab;
667 t_last_update_date last_update_date_tab;
668 t_resource_type resource_type_tab;
669 t_comment_text comment_text_tab;
670 t_start_time start_time_tab;
671 t_stop_time stop_time_tab;
672
673
674
675 BEGIN
676 p_detail_count := 0;
677
678 open csr_get_resource_details;
679 Loop
680
681 if (is_process_time_over(p_process_end_time)) then
682 close csr_get_resource_details;
683 commit;
684 return;
685 end if;
686
687 fetch csr_get_resource_details BULK COLLECT
688 INTO t_resource_id,
689 t_tbb_id, t_ovn,
690 t_approval_status,
691 t_application_set_id,
692 t_last_update_date,
693 t_resource_type,
694 t_comment_text,
695 t_start_time,
696 t_stop_time LIMIT 100;
697
698 IF (t_tbb_id.COUNT <> 0) THEN
699
700 forall x in t_tbb_id.first .. t_tbb_id.last
701 INSERT INTO hxc_latest_details
702 (resource_id
703 ,time_building_block_id
704 ,object_version_number
705 ,approval_status
706 ,application_set_id
707 ,last_update_date
708 ,resource_type
709 ,comment_text
710 ,start_time
711 ,stop_time)
712 VALUES
713 (t_resource_id(x)
714 ,t_tbb_id(x)
715 ,t_ovn(x)
716 ,t_approval_status(x)
717 ,t_application_set_id(x)
718 ,t_last_update_date(x)
719 ,t_resource_type(x)
720 ,t_comment_text(x)
721 ,t_start_time(x)
722 ,t_stop_time(x));
723
724 p_detail_count := p_detail_count + t_tbb_id.count;
725
726 t_resource_id.DELETE;
727 t_tbb_id.DELETE;
728 t_ovn.DELETE;
729 t_approval_status.DELETE;
730 t_application_set_id.DELETE;
731 t_last_update_date.DELETE;
732 t_resource_type.DELETE;
733 t_comment_text.DELETE;
734 t_start_time.DELETE;
735 t_stop_time.DELETE;
736
737 END IF;
738
739 EXIT WHEN csr_get_resource_details%NOTFOUND;
740 END LOOP;
741
742 CLOSE csr_get_resource_details;
743
744 Exception
745 When others then
746 fnd_file.put_line(fnd_file.LOG,'Exception in populate_details is :' || SQLERRM);
747 return;
748 END populate_details;
749
750
751 Procedure migrate_templates(errbuf out nocopy varchar2
752 ,retcode out nocopy number
753 ,p_business_group_id in number default null
754 ,p_start_date in varchar2 default null
755 ,p_end_date in varchar2 default null
756 ,p_stop_time in varchar2 default null
757 ,p_batch_size in number default 500
758 ,p_num_workers in number
759 ) is
760
761 cursor c_templates(p_start_date date, p_end_date date) is
762 select tbb.time_building_block_id
763 ,tbb.object_version_number
764 from hxc_time_building_blocks tbb
765 where tbb.date_to = hr_general.end_of_time and
766 tbb.scope = 'TIMECARD_TEMPLATE'
767 and resource_id <>-1 and
768 not exists
769 (select 'Y'
770 from hxc_template_summary
771 where template_id = tbb.time_building_block_id and
772 template_ovn = tbb.object_version_number) and
773 TRUNC(tbb.start_time) >= nvl(p_start_date, TRUNC(tbb.start_time)) and -- 5985862 Added TRUNC to truncate time component from date
774 TRUNC(tbb.stop_time) <= nvl(p_end_date, tbb.stop_time) and -- 5985862 Added TRUNC to truncate time component from date
775 exists
776 (select 1
777 from per_all_people_f per
778 where tbb.resource_id = per.person_id);
779
780 cursor c_templates_bg(p_start_date date, p_end_date date) is
781 select tbb.time_building_block_id
782 ,tbb.object_version_number
783 from hxc_time_building_blocks tbb
784 where tbb.date_to = hr_general.end_of_time and
785 tbb.scope = 'TIMECARD_TEMPLATE'
786 and resource_id <>-1 and
787 not exists
788 (select 'Y'
789 from hxc_template_summary
790 where template_id = tbb.time_building_block_id and
791 template_ovn = tbb.object_version_number) and
792 TRUNC(tbb.start_time) >= nvl(p_start_date, TRUNC(tbb.start_time)) and -- 5985862 Added TRUNC to truncate time component from date
793 TRUNC(tbb.stop_time) <= nvl(p_end_date, tbb.stop_time) and -- 5985862 Added TRUNC to truncate time component from date
794 exists
795 (select 1
796 from per_all_people_f per
797 where tbb.resource_id = per.person_id and
798 per.business_group_id = p_business_group_id);
799
800
801 TYPE tbb_id_tab IS TABLE OF hxc_time_building_blocks.time_building_block_id%TYPE INDEX BY BINARY_INTEGER;
802 TYPE ovn_tab IS TABLE OF hxc_time_building_blocks.object_version_number%TYPE INDEX BY BINARY_INTEGER;
803 l_tbb_id_tab tbb_id_tab;
804 l_ovn_tab ovn_tab;
805 l_app_bb_id_tab tbb_id_tab;
806
807
808 type req_id_tab is table of number index by binary_integer;
809 l_req_id req_id_tab;
810
811 l_request_id number;
812 i pls_integer;
813
814 l_start_date date;
815 l_end_date date;
816
817 l_process_end_time date;
818
819 l_timecard_count number;
820 l_detail_count number;
821 l_batch_size number;
822 l_elp_upg_count number;
823
824 begin
825
826 fnd_file.put_line(fnd_file.LOG, 'Starting Template Migration');
827 fnd_file.put_line(fnd_file.LOG,'Starting Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
828
829 l_timecard_count := 0;
830 l_detail_count := 0;
831 l_elp_upg_count := 0;
832
833 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
834 l_process_end_time := fnd_date.canonical_to_date(p_stop_time);
835 l_start_date := trunc(fnd_date.canonical_to_date(p_start_date));
836 l_end_date := trunc(fnd_date.canonical_to_date(p_end_date)) + 1 - (1 / (24 * 60 * 60));
837 l_batch_size := nvl(p_batch_size,500);
838
839
840 fnd_file.put_line(fnd_file.LOG,'-------------------------------------');
841 fnd_file.put_line(fnd_file.LOG, 'Parameters');
842 fnd_file.put_line(fnd_file.LOG, '----------');
843 fnd_file.put_line(fnd_file.LOG,'Business Group Id :' || p_business_group_id);
844 fnd_file.put_line(fnd_file.LOG, 'Start Date :' || l_start_date);
845 fnd_file.put_line(fnd_file.LOG, 'End Date :' || l_end_date);
846 fnd_file.put_line(fnd_file.LOG, 'Stop Processing At :' || p_stop_time);
847 fnd_file.put_line(fnd_file.LOG, 'Batch Size :' || l_batch_size);
848 fnd_file.put_line(fnd_file.LOG,'Number of Workers : ' || p_num_workers);
849 fnd_file.put_line(fnd_file.LOG,'--------------------------------------');
850
851 IF ( p_business_group_id is null) THEN
852 open c_templates(l_start_date, l_end_date);
853 ELSE
854 open c_templates_bg(l_start_date, l_end_date);
855 END IF;
856
857
858 i := 1;
859 loop
860
861 if (is_process_time_over(l_process_end_time)) then
862 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
863 fnd_file.put_line(fnd_file.LOG,'Total Number of templates processed : ' ||l_timecard_count);
864 fnd_file.put_line(fnd_file.LOG,'Total Number of detail blocks processed : ' ||l_detail_count);
865 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
866 fnd_file.put_line(fnd_file.LOG, 'Leaving Template Migration');
867 fnd_file.put_line(fnd_file.LOG,'Ending Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
868 commit;
869 return;
870 end if;
871
872 if not l_req_id.exists(i) then
873 l_req_id(i) := FND_REQUEST.SUBMIT_REQUEST(application => 'HXC'
874 ,program => 'HXCTCMIGRATEWK'
875 ,description => NULL
876 ,sub_request => FALSE
877 ,argument1 => l_request_id
878 ,argument2 => p_stop_time
879 ,argument3 => 'TEMPLATE');
880 if l_req_id(i) = 0 then
881 --some problem with the concurrent request. write to log file.
882 fnd_file.put_line(fnd_file.LOG,'There was a problem while submitting the concurrent request for Worker ' || i);
883
884 end if;
885 commit;
886
887 end if;
888
889 IF ( p_business_group_id is null) THEN
890 fetch c_templates BULK COLLECT
891 INTO l_tbb_id_tab, l_ovn_tab limit l_batch_size;
892 ELSE
893 fetch c_templates_bg BULK COLLECT
894 INTO l_tbb_id_tab, l_ovn_tab limit l_batch_size;
895 END IF;
896
897 l_timecard_count := l_timecard_count + l_tbb_id_tab.count;
898
899
900 BEGIN
901
902 forall x in l_tbb_id_tab.first .. l_tbb_id_tab.last
903 insert into hxc_temp_timecards
904 (time_building_block_id
905 ,object_version_number
906 ,scope
907 ,worker_id)
908 values
909 (l_tbb_id_tab(x)
910 ,l_ovn_tab(x)
911 ,'TIMECARD_TEMPLATE'
912 ,l_req_id(i));
913
914 EXCEPTION
915 when others then
916 null;
917 END;
918 commit;
919
920 -- 5985862 Exit from whichever cursor is open.
921 IF ( p_business_group_id is null) THEN
922 exit when c_templates%notfound;
923 ELSE
924 exit when c_templates_bg%notfound;
925 END IF;
926
927 l_tbb_id_tab.delete;
928 l_ovn_tab.delete;
929
930 i := i + 1;
931 if (i > p_num_workers) then
932 i := 1;
933 end if;
934
935 end loop;
936
937 -- 5985862 Close whichever is open
938 IF ( p_business_group_id is null) THEN
939 close c_templates;
940 ELSE
941 close c_templates_bg;
942 END IF;
943
944 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
945 fnd_file.put_line(fnd_file.LOG,'Total Number of templates processed : ' ||l_timecard_count);
946
947
948
949 insert into hxc_temp_timecards
950 (TIME_BUILDING_BLOCK_ID
951 ,scope
952 ,object_version_number
953 ,worker_id
954 ,processed)
955 values
956 (null
957 ,'COMPLETED'
958 ,null
959 ,l_request_id
960 ,'Y');
961 commit;
962
963 fnd_file.put_line(fnd_file.LOG,'Total Number of detail blocks processed : ' ||l_detail_count);
964 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
965 fnd_file.put_line(fnd_file.LOG, 'Leaving Template Migration');
966 fnd_file.put_line(fnd_file.LOG,'Ending Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
967
968 -- delete_templates;
969 commit;
970 end migrate_templates;
971
972 Procedure migrate_timecards(errbuf out nocopy varchar2
973 ,retcode out nocopy number
974 ,p_business_group_id in number default null
975 ,p_start_date in varchar2 default null
976 ,p_end_date in varchar2 default null
977 ,p_stop_time in varchar2 default null
978 ,p_batch_size in number default 500
979 ,p_num_workers in number
980 ) is
981
982 cursor c_timecards(p_start_date date, p_end_date date) is
983 select tbb.time_building_block_id
984 ,tbb.object_version_number
985 from hxc_time_building_blocks tbb
986 where tbb.date_to = hr_general.end_of_time and
987 tbb.scope = 'TIMECARD' and
988 not exists
989 (select 'Y'
990 from hxc_timecard_summary
991 where timecard_id = tbb.time_building_block_id and
992 timecard_ovn = tbb.object_version_number) and
993 TRUNC(tbb.start_time) >= nvl(p_start_date, TRUNC(tbb.start_time)) and -- 5985862 Added TRUNC to truncate time component from date
994 TRUNC(tbb.stop_time) <= nvl(p_end_date, tbb.stop_time) and -- 5985862 Added TRUNC to truncate time component from date
995 exists
996 (select 1
997 from per_all_people_f per
998 where tbb.resource_id = per.person_id);
999
1000 cursor c_timecards_bg(p_start_date date, p_end_date date) is
1001 select tbb.time_building_block_id
1002 ,tbb.object_version_number
1003 from hxc_time_building_blocks tbb
1004 where tbb.date_to = hr_general.end_of_time and
1005 tbb.scope = 'TIMECARD' and
1006 not exists
1007 (select 'Y'
1008 from hxc_timecard_summary
1009 where timecard_id = tbb.time_building_block_id and
1010 timecard_ovn = tbb.object_version_number) and
1011 TRUNC(tbb.start_time) >= nvl(p_start_date, TRUNC(tbb.start_time)) and -- 5985862 Added TRUNC to truncate time component from date
1012 TRUNC(tbb.stop_time) <= nvl(p_end_date, tbb.stop_time) and -- 5985862 Added TRUNC to truncate time component from date
1013 exists
1014 (select 1
1015 from per_all_people_f per
1016 where tbb.resource_id = per.person_id and
1017 per.business_group_id = p_business_group_id);
1018
1019 cursor c_app_periods(p_start_date date, p_end_date date) is
1020 select distinct tbb.time_building_block_id
1021 from hxc_time_building_blocks tbb
1022 ,hxc_time_attribute_usages tau
1023 ,hxc_time_attributes ta
1024 where tbb.scope = 'APPLICATION_PERIOD' and
1025 tbb.date_to = hr_general.end_of_time and
1026 tau.time_building_block_id = tbb.time_building_block_id and
1027 tau.time_building_block_ovn = tbb.object_version_number and
1028 ta.attribute_category = 'APPROVAL' and
1029 ta.time_attribute_id = tau.time_attribute_id and
1030 TRUNC(tbb.start_time) >= nvl(p_start_date, TRUNC(tbb.start_time)) and -- 5985862 Added TRUNC to truncate time component from date
1031 TRUNC(tbb.stop_time) <= nvl(p_end_date, tbb.stop_time) and -- 5985862 Added TRUNC to truncate time component from date
1032 not exists
1033 (select 'Y'
1034 from hxc_app_period_summary aps
1035 where aps.application_period_id = tbb.time_building_block_id and
1036 aps.application_period_ovn = tbb.object_version_number) and
1037 exists
1038 (select 1
1039 from per_all_people_f per
1040 where tbb.resource_id = person_id )
1041 order by 1;
1042
1043 cursor c_app_periods_bg(p_start_date date, p_end_date date) is
1044 select distinct tbb.time_building_block_id
1045 from hxc_time_building_blocks tbb
1046 ,hxc_time_attribute_usages tau
1047 ,hxc_time_attributes ta
1048 where tbb.scope = 'APPLICATION_PERIOD' and
1049 tbb.date_to = hr_general.end_of_time and
1050 tau.time_building_block_id = tbb.time_building_block_id and
1051 tau.time_building_block_ovn = tbb.object_version_number and
1052 ta.attribute_category = 'APPROVAL' and
1053 ta.time_attribute_id = tau.time_attribute_id and
1054 TRUNC(tbb.start_time) >= nvl(p_start_date, TRUNC(tbb.start_time)) and -- 5985862 Added TRUNC to truncate time component from date
1055 TRUNC(tbb.stop_time) <= nvl(p_end_date, tbb.stop_time) and -- 5985862 Added TRUNC to truncate time component from date
1056 not exists
1057 (select 'Y'
1058 from hxc_app_period_summary aps
1059 where aps.application_period_id = tbb.time_building_block_id and
1060 aps.application_period_ovn = tbb.object_version_number) and
1061 exists
1062 (select 1
1063 from per_all_people_f per
1064 where tbb.resource_id = person_id and
1065 per.business_group_id =
1066 p_business_group_id)
1067 order by 1;
1068
1069
1070 TYPE tbb_id_tab IS TABLE OF hxc_time_building_blocks.time_building_block_id%TYPE INDEX BY BINARY_INTEGER;
1071 TYPE ovn_tab IS TABLE OF hxc_time_building_blocks.object_version_number%TYPE INDEX BY BINARY_INTEGER;
1072 l_tbb_id_tab tbb_id_tab;
1073 l_ovn_tab ovn_tab;
1074 l_app_bb_id_tab tbb_id_tab;
1075
1076
1077 type req_id_tab is table of number index by binary_integer;
1078 l_req_id req_id_tab;
1079
1080 l_request_id number;
1081 i pls_integer;
1082
1083 l_start_date date;
1084 l_end_date date;
1085
1086 l_process_end_time date;
1087
1088 l_timecard_count number;
1089 l_app_period_count number;
1090 l_detail_count number;
1091 l_batch_size number;
1092 l_elp_upg_count number;
1093
1094 begin
1095
1096 fnd_file.put_line(fnd_file.LOG, 'Starting Timecard Migration');
1097 fnd_file.put_line(fnd_file.LOG,'Starting Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
1098
1099 l_timecard_count := 0;
1100 l_app_period_count := 0;
1101 l_detail_count := 0;
1102 l_elp_upg_count := 0;
1103
1104 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1105 l_process_end_time := fnd_date.canonical_to_date(p_stop_time);
1106 l_start_date := trunc(fnd_date.canonical_to_date(p_start_date));
1107 l_end_date := trunc(fnd_date.canonical_to_date(p_end_date)) + 1 - (1 / (24 * 60 * 60));
1108 l_batch_size := nvl(p_batch_size,500);
1109
1110
1111 fnd_file.put_line(fnd_file.LOG,'-------------------------------------');
1112 fnd_file.put_line(fnd_file.LOG, 'Parameters');
1113 fnd_file.put_line(fnd_file.LOG, '----------');
1114 fnd_file.put_line(fnd_file.LOG,'Business Group Id :' || p_business_group_id);
1115 fnd_file.put_line(fnd_file.LOG, 'Start Date :' || l_start_date);
1116 fnd_file.put_line(fnd_file.LOG, 'End Date :' || l_end_date);
1117 fnd_file.put_line(fnd_file.LOG, 'Stop Processing At :' || p_stop_time);
1118 fnd_file.put_line(fnd_file.LOG, 'Batch Size :' || l_batch_size);
1119 fnd_file.put_line(fnd_file.LOG,'Number of Workers : ' || p_num_workers);
1120 fnd_file.put_line(fnd_file.LOG,'--------------------------------------');
1121
1122 IF(p_business_group_id is null) THEN
1123 open c_timecards(l_start_date, l_end_date);
1124 ELSE
1125 open c_timecards_bg(l_start_date, l_end_date);
1126 END IF;
1127
1128 i := 1;
1129 loop
1130
1131 if (is_process_time_over(l_process_end_time)) then
1132 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1133 fnd_file.put_line(fnd_file.LOG,'Total Number of timecards processed : ' ||l_timecard_count);
1134 fnd_file.put_line(fnd_file.LOG,'Total Number of application period blocks processed : ' ||l_app_period_count);
1135 fnd_file.put_line(fnd_file.LOG,'Total Number of detail blocks processed : ' ||l_detail_count);
1136 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1137 fnd_file.put_line(fnd_file.LOG, 'Leaving Timecard Migration');
1138 fnd_file.put_line(fnd_file.LOG,'Ending Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
1139 commit;
1140 return;
1141 end if;
1142
1143 if not l_req_id.exists(i) then
1144 l_req_id(i) := FND_REQUEST.SUBMIT_REQUEST(application => 'HXC'
1145 ,program => 'HXCTCMIGRATEWK'
1146 ,description => NULL
1147 ,sub_request => FALSE
1148 ,argument1 => l_request_id
1149 ,argument2 => p_stop_time
1150 ,argument3 => 'TIMECARD');
1151 if l_req_id(i) = 0 then
1152 --some problem with the concurrent request. write to log file.
1153 fnd_file.put_line(fnd_file.LOG,'There was a problem while submitting the concurrent request for Worker ' || i);
1154
1155 end if;
1156 commit;
1157
1158 end if;
1159
1160 IF(p_business_group_id is null) THEN
1161 fetch c_timecards BULK COLLECT
1162 INTO l_tbb_id_tab, l_ovn_tab limit l_batch_size;
1163 ELSE
1164 fetch c_timecards_bg BULK COLLECT
1165 INTO l_tbb_id_tab, l_ovn_tab limit l_batch_size;
1166 END IF;
1167
1168 l_timecard_count := l_timecard_count + l_tbb_id_tab.count;
1169 BEGIN
1170
1171 forall x in l_tbb_id_tab.first .. l_tbb_id_tab.last
1172 insert into hxc_temp_timecards
1173 (time_building_block_id
1174 ,object_version_number
1175 ,scope
1176 ,worker_id)
1177 values
1178 (l_tbb_id_tab(x)
1179 ,l_ovn_tab(x)
1180 ,'TIMECARD'
1181 ,l_req_id(i));
1182
1183 EXCEPTION
1184 when others then
1185 null;
1186 END;
1187 commit;
1188
1189 -- 5985862 Exit from whichever cursor is open.
1190 IF(p_business_group_id is null) THEN
1191 exit when c_timecards%notfound;
1192 ELSE
1193 exit when c_timecards_bg%notfound;
1194 END IF;
1195
1196 l_tbb_id_tab.delete;
1197 l_ovn_tab.delete;
1198
1199 i := i + 1;
1200 if (i > p_num_workers) then
1201 i := 1;
1202 end if;
1203
1204
1205 end loop;
1206
1207 -- 5985862 Close whichever cursor is open
1208 IF(p_business_group_id is null) THEN
1209 close c_timecards;
1210 ELSE
1211 close c_timecards_bg;
1212 END IF;
1213
1214 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1215 fnd_file.put_line(fnd_file.LOG,'Total Number of timecards processed : ' ||l_timecard_count);
1216
1217 if(p_business_group_id is null) then
1218 open c_app_periods(l_start_date, l_end_date);
1219 else
1220 open c_app_periods_bg(l_start_date, l_end_date);
1221 end if;
1222
1223 i := 1;
1224 loop
1225 if (is_process_time_over(l_process_end_time)) then
1226 fnd_file.put_line(fnd_file.LOG,'Total Number of application period blocks processed : ' ||l_app_period_count);
1227 fnd_file.put_line(fnd_file.LOG,'Total Number of detail blocks processed : ' ||l_detail_count);
1228 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1229 fnd_file.put_line(fnd_file.LOG, 'Leaving Timecard Migration');
1230 fnd_file.put_line(fnd_file.LOG,'Ending Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
1231 commit;
1232 return;
1233 end if;
1234
1235 if not l_req_id.exists(i) then
1236
1237 l_req_id(i) := FND_REQUEST.SUBMIT_REQUEST(application => 'HXC'
1238 ,program => 'HXCTCMIGRATEWK'
1239 ,description => NULL
1240 ,sub_request => FALSE
1241 ,argument1 => l_request_id
1242 ,argument2 => p_stop_time
1243 ,argument3 => 'TIMECARD');
1244 if l_req_id(i) = 0 then
1245 --some problem with the concurrent request. write to log file.
1246 fnd_file.put_line(fnd_file.LOG,'There was a problem while submitting the concurrent request for Worker ' || i);
1247 end if;
1248
1249 commit;
1250
1251 end if;
1252
1253 IF(p_business_group_id is null) THEN
1254 fetch c_app_periods BULK COLLECT
1255 INTO l_app_bb_id_tab limit l_batch_size;
1256 ELSE
1257 fetch c_app_periods_bg BULK COLLECT
1258 INTO l_app_bb_id_tab limit l_batch_size;
1259 END IF;
1260
1261 l_app_period_count := l_app_period_count + l_app_bb_id_tab.count;
1262
1263 BEGIN
1264 forall x in l_app_bb_id_tab.first .. l_app_bb_id_tab.last
1265 insert into hxc_temp_timecards
1266 (time_building_block_id
1267 ,scope
1268 ,worker_id)
1269 values
1270 (l_app_bb_id_tab(x)
1271 ,'APPLICATION_PERIOD'
1272 ,l_req_id(i));
1273
1274 EXCEPTION
1275 when others then
1276 null;
1277 END;
1278 commit;
1279
1280 -- 5985862 Exit from whichever cursor is open.
1281 IF(p_business_group_id is null) THEN
1282 exit when c_app_periods%notfound;
1283 ELSE
1284 exit when c_app_periods_bg%notfound;
1285 END IF;
1286
1287 l_app_bb_id_tab.delete;
1288
1289 i := i + 1;
1290 if (i > p_num_workers) then
1291 i := 1;
1292 end if;
1293
1294 end loop;
1295
1296 -- 5985862 Close whichever is open
1297 IF(p_business_group_id is null) THEN
1298 close c_app_periods;
1299 ELSE
1300 close c_app_periods_bg;
1301 END IF;
1302
1303 fnd_file.put_line(fnd_file.LOG,'Total Number of application period blocks processed : ' ||l_app_period_count);
1304
1305 insert into hxc_temp_timecards
1306 (TIME_BUILDING_BLOCK_ID
1307 ,scope
1308 ,object_version_number
1309 ,worker_id
1310 ,processed)
1311 values
1312 (null
1313 ,'COMPLETED'
1314 ,null
1315 ,l_request_id
1316 ,'Y');
1317 commit;
1318
1319
1320 --we do not do parallel processing for details
1321 populate_details(p_business_group_id
1322 ,l_start_date
1323 ,l_end_date
1324 ,l_process_end_time
1325 ,l_detail_count);
1326
1327 populate_appl_set_id(p_business_group_id, l_process_end_time, l_start_date, l_end_date,l_elp_upg_count);
1328
1329 fnd_file.put_line(fnd_file.LOG,'Total Number of detail blocks processed : ' ||l_detail_count);
1330 fnd_file.put_line(fnd_file.LOG,'Total Number of blocks processsed for ELP upgrade : ' ||l_elp_upg_count);
1331 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1332 fnd_file.put_line(fnd_file.LOG, 'Leaving Timecard Migration');
1333 fnd_file.put_line(fnd_file.LOG,'Ending Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
1334
1335 delete_timecards;
1336 commit;
1337
1338 end migrate_timecards;
1339
1340 procedure run_tc_migration(errbuf out nocopy varchar2
1341 ,retcode out nocopy number
1342 ,p_business_group_id in number default null
1343 ,p_start_date in varchar2 default null
1344 ,p_end_date in varchar2 default null
1345 ,p_stop_time in varchar2 default null
1346 ,p_batch_size in number default 500
1347 ,p_num_workers in number
1348 ,p_migration_type in varchar2) is
1349 l_timecard_req_id number;
1350 l_template_req_id number;
1351 begin
1352 IF(p_migration_type = 'TIMECARD') then
1353 migrate_timecards(errbuf
1354 ,retcode
1355 ,p_business_group_id
1356 ,p_start_date
1357 ,p_end_date
1358 ,p_stop_time
1359 ,p_batch_size
1360 ,p_num_workers);
1361 ELSIF(p_migration_type = 'TEMPLATE') then
1362 migrate_templates(errbuf
1363 ,retcode
1364 ,p_business_group_id
1365 ,p_start_date
1366 ,p_end_date
1367 ,p_stop_time
1368 ,p_batch_size
1369 ,p_num_workers);
1370 ELSIF(p_migration_type = 'BOTH') then
1371 /*l_timecard_req_id := FND_REQUEST.SUBMIT_REQUEST(application => 'HXC'
1372 ,program => 'HXCTCMIGRATE'
1373 ,description => NULL
1374 ,sub_request => FALSE
1375 ,argument1 =>p_business_group_id
1376 ,argument2 => p_start_date
1377 ,argument3 => p_end_date
1378 ,argument4 => p_stop_time
1379 ,argument5 => p_batch_size
1380 ,argument6 => p_num_workers
1381 ,argument7 =>'TIMECARD');*/
1382
1383 if (hr_update_utility.isUpdateComplete(p_app_shortname => 'HXC',
1384 p_function_name => NULL,
1385 p_business_group_id => p_business_group_id,
1386 p_update_name => 'HXCTCMIGRATE'
1387 ) = 'FALSE')
1388 then
1389 hr_update_utility.setUpdateProcessing(p_update_name => 'HXCTCMIGRATE' );
1390 end if;
1391
1392 /* changed from FND_REQUEST.SUBMIT_REQUEST to hr_update_utility.submitRequest in order to reflect the
1393 status in DTR report */
1394 hr_update_utility.submitRequest(p_app_shortname => 'HXC'
1395 ,p_update_name => 'HXCTCMIGRATE'
1396 ,p_validate_proc => 'hxc_tcsummary_migrate.check_hxt_installed'
1397 ,p_business_group_id => p_business_group_id
1398 --,p_legislation_code in varchar2 default null
1399 ,p_argument1 =>p_business_group_id
1400 ,p_argument2 => p_start_date
1401 ,p_argument3 => p_end_date
1402 ,p_argument4 => p_stop_time
1403 ,p_argument5 => p_batch_size
1404 ,p_argument6 => p_num_workers
1405 ,p_argument7 =>'TIMECARD'
1406 ,p_request_id => l_timecard_req_id) ;
1407
1408
1409 if l_timecard_req_id = 0 then
1410 --some problem with the concurrent request. write to log file.
1411 fnd_file.put_line(fnd_file.LOG,'There was a problem while submitting the concurrent request for migrating tiemcards');
1412
1413 end if;
1414
1415 /*l_template_req_id := FND_REQUEST.SUBMIT_REQUEST(application => 'HXC'
1416 ,program => 'HXCTCMIGRATE'
1417 ,description => NULL
1418 ,sub_request => FALSE
1419 ,argument1 =>p_business_group_id
1420 ,argument2 => p_start_date
1421 ,argument3 => p_end_date
1422 ,argument4 => p_stop_time
1423 ,argument5 => p_batch_size
1424 ,argument6 => p_num_workers
1425 ,argument7 =>'TEMPLATE');*/
1426
1427
1428 /* changed from FND_REQUEST.SUBMIT_REQUEST to hr_update_utility.submitRequest in order to reflect the
1429 status in DTR report */
1430 hr_update_utility.submitRequest(p_app_shortname => 'HXC'
1431 ,p_update_name => 'HXCTCMIGRATE'
1432 ,p_validate_proc => 'hxc_tcsummary_migrate.check_hxt_installed'
1433 ,p_business_group_id => p_business_group_id
1434 --,p_legislation_code in varchar2 default null
1435 ,p_argument1 =>p_business_group_id
1436 ,p_argument2 => p_start_date
1437 ,p_argument3 => p_end_date
1438 ,p_argument4 => p_stop_time
1439 ,p_argument5 => p_batch_size
1440 ,p_argument6 => p_num_workers
1441 ,p_argument7 =>'TEMPLATE'
1442 ,p_request_id => l_template_req_id) ;
1443
1444
1445
1446 if l_template_req_id = 0 then
1447 --some problem with the concurrent request. write to log file.
1448 fnd_file.put_line(fnd_file.LOG,'There was a problem while submitting the concurrent request for migrating templates' );
1449
1450 end if;
1451
1452 if ( l_timecard_req_id <> 0 AND l_template_req_id <> 0
1453 AND hr_update_utility.isUpdateComplete(p_app_shortname => 'HXC',
1454 p_function_name => NULL,
1455 p_business_group_id => p_business_group_id,
1456 p_update_name => 'HXCTCMIGRATE'
1457 ) = 'FALSE'
1458 )
1459 then
1460 hr_update_utility.setUpdateComplete(p_update_name => 'HXCTCMIGRATE' );
1461 end if;
1462
1463 END IF;
1464 end run_tc_migration;
1465
1466 procedure run_tc_migration_worker(errbuf out nocopy varchar2
1467 ,retcode out nocopy number
1468 ,p_parent_req_id in number
1469 ,p_stop_time in varchar2 default null
1470 ,p_migration_type in varchar2) IS
1471
1472 cursor c_temp_resources(p_worker_id number, p_scope varchar2) is
1473 select time_building_block_id
1474 ,object_version_number
1475 from hxc_temp_timecards
1476 where worker_id = p_worker_id and
1477 scope = p_scope and
1478 processed = 'Y';
1479
1480 cursor c_check_completion(p_worker_id number) is
1481 select 'Y'
1482 from hxc_temp_timecards
1483 where worker_id = p_worker_id and
1484 scope = 'COMPLETED';
1485
1486
1487 l_worker_id number;
1488 l_dummy varchar2(1);
1489
1490 l_req boolean;
1491 l_phase varchar2(80);
1492 l_status varchar2(80);
1493 l_dev_phase varchar2(30);
1494 l_dev_status varchar2(30);
1495 l_message varchar2(240);
1496 l_parent_req_id number;
1497 l_process_end_time date;
1498
1499 l_time_summary_count number;
1500 l_app_per_summary_count number;
1501
1502 Begin
1503
1504 fnd_file.put_line(fnd_file.LOG,'Starting Worker Migration');
1505 fnd_file.put_line(fnd_file.LOG,'Starting Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
1506 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1507
1508 l_process_end_time := fnd_date.canonical_to_date(p_stop_time);
1509 l_worker_id := FND_GLOBAL.CONC_REQUEST_ID;
1510 l_parent_req_id := p_parent_req_id;
1511
1512 l_time_summary_count := 0;
1513 l_app_per_summary_count := 0;
1514
1515 if (p_migration_type ='TIMECARD') THEN
1516 loop
1517 if (is_process_time_over(l_process_end_time)) then
1518 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1519 fnd_file.put_line(fnd_file.LOG,'Number of Summary Records Processed :' ||
1520 l_time_summary_count);
1521 fnd_file.put_line(fnd_file.LOG,'Number of Application Summary Records Processed :' ||l_app_per_summary_count);
1522 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1523 fnd_file.put_line(fnd_file.LOG, 'Leaving Worker Migration');
1524 fnd_file.put_line(fnd_file.LOG,'Ending Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
1525 delete from hxc_temp_timecards where worker_id = l_worker_id;
1526 commit;
1527 return;
1528 end if;
1529
1530 update hxc_temp_timecards
1531 set processed = 'Y'
1532 where worker_id = l_worker_id and
1533 scope in ('TIMECARD', 'APPLICATION_PERIOD');
1534
1535 if sql%found then
1536
1537 for timecard_rec in c_temp_resources(l_worker_id, 'TIMECARD') loop
1538 hxc_timecard_summary_api.timecard_deposit
1539 (p_timecard_id => timecard_rec.time_building_block_id
1540 ,p_mode => hxc_timecard_summary_pkg.c_migration_mode
1541 ,p_approval_item_type =>null
1542 ,p_approval_process_name=> null
1543 ,p_approval_item_key => null
1544 ,p_tk_audit_item_type => null
1545 ,p_tk_audit_process_name => null
1546 ,p_tk_audit_item_key => null
1547 );
1548 l_time_summary_count := l_time_summary_count + 1;
1549 end loop;
1550
1551 for l_app_period_rec in c_temp_resources(l_worker_id,'APPLICATION_PERIOD') loop
1552 hxc_app_period_summary_api.app_period_create(l_app_period_rec.time_building_block_id
1553 ,hxc_timecard_summary_pkg.c_migration_mode);
1554 l_app_per_summary_count := l_app_per_summary_count + 1;
1555 end loop;
1556
1557 delete from hxc_temp_timecards
1558 where worker_id = l_worker_id and
1559 scope in ('TIMECARD', 'APPLICATION_PERIOD') and
1560 processed = 'Y';
1561
1562 commit;
1563 else
1564 -- check the parent request's status
1565 -- if it has got completed, we need to complete this worker too
1566
1567 open c_check_completion(l_parent_req_id);
1568 fetch c_check_completion
1569 into l_dummy;
1570 if c_check_completion%found then
1571 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1572 fnd_file.put_line(fnd_file.LOG,'Number of Summary Records Processed :' ||l_time_summary_count);
1573 fnd_file.put_line(fnd_file.LOG,'Number of Application Summary Records Processed :' ||l_app_per_summary_count);
1574 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1575 fnd_file.put_line(fnd_file.LOG, 'Leaving Worker Migration');
1576 fnd_file.put_line(fnd_file.LOG,'Ending Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
1577 commit;
1578 close c_check_completion;
1579 return;
1580 end if;
1581 close c_check_completion;
1582
1583 l_req := FND_CONCURRENT.GET_REQUEST_STATUS(request_id => l_parent_req_id
1584 ,phase => l_phase
1585 ,status => l_status
1586 ,dev_phase => l_dev_phase
1587 ,dev_status => l_dev_status
1588 ,message => l_message);
1589
1590 if (l_dev_Phase = 'COMPLETE') then
1591 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1592 fnd_file.put_line(fnd_file.LOG,'Number of Summary Records Processed :' ||l_time_summary_count);
1593 fnd_file.put_line(fnd_file.LOG,'Number of Application Summary Records Processed :' ||l_app_per_summary_count);
1594 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1595 fnd_file.put_line(fnd_file.LOG, 'Leaving Worker Migration');
1596 fnd_file.put_line(fnd_file.LOG,'Ending Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
1597 commit;
1598 return;
1599 end if;
1600
1601 end if;
1602 end loop;
1603 elsif (p_migration_type ='TEMPLATE') THEN
1604 loop
1605 if (is_process_time_over(l_process_end_time)) then
1606 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1607 fnd_file.put_line(fnd_file.LOG,'Number of Summary Records Processed :' ||
1608 l_time_summary_count);
1609 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1610 fnd_file.put_line(fnd_file.LOG, 'Leaving Worker Migration');
1611 fnd_file.put_line(fnd_file.LOG,'Ending Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
1612 delete from hxc_temp_timecards where worker_id = l_worker_id;
1613 commit;
1614 return;
1615 end if;
1616
1617 update hxc_temp_timecards
1618 set processed = 'Y'
1619 where worker_id = l_worker_id and
1620 scope in ('TIMECARD_TEMPLATE');
1621
1622 if sql%found then
1623
1624 for timecard_rec in c_temp_resources(l_worker_id, 'TIMECARD_TEMPLATE') loop
1625 hxc_template_summary_api.template_deposit
1626 (p_template_id => timecard_rec.time_building_block_id
1627 ,p_template_ovn =>timecard_rec.object_version_number
1628 );
1629 l_time_summary_count := l_time_summary_count + 1;
1630 end loop;
1631
1632 delete from hxc_temp_timecards
1633 where worker_id = l_worker_id and
1634 scope in ('TIMECARD_TEMPLATE') and
1635 processed = 'Y';
1636
1637 commit;
1638 else
1639 -- check the parent request's status
1640 -- if it has got completed, we need to complete this worker too
1641
1642 open c_check_completion(l_parent_req_id);
1643 fetch c_check_completion
1644 into l_dummy;
1645 if c_check_completion%found then
1646 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1647 fnd_file.put_line(fnd_file.LOG,'Number of Summary Records Processed :' ||l_time_summary_count);
1648 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1649 fnd_file.put_line(fnd_file.LOG, 'Leaving Worker Migration');
1650 fnd_file.put_line(fnd_file.LOG,'Ending Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
1651 commit;
1652 close c_check_completion;
1653 return;
1654 end if;
1655 close c_check_completion;
1656
1657 l_req := FND_CONCURRENT.GET_REQUEST_STATUS(request_id => l_parent_req_id
1658 ,phase => l_phase
1659 ,status => l_status
1660 ,dev_phase => l_dev_phase
1661 ,dev_status => l_dev_status
1662 ,message => l_message);
1663
1664 if (l_dev_Phase = 'COMPLETE') then
1665 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1666 fnd_file.put_line(fnd_file.LOG,'Number of Summary Records Processed :' ||l_time_summary_count);
1667 fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
1668 fnd_file.put_line(fnd_file.LOG, 'Leaving Worker Migration');
1669 fnd_file.put_line(fnd_file.LOG,'Ending Time :' ||to_char(sysdate, 'DD/MM/RRRR HH:MI:SS'));
1670 commit;
1671 return;
1672 end if;
1673
1674 end if;
1675 end loop;
1676 END IF;
1677
1678 End run_tc_migration_worker;
1679
1680 /*****************************************************************************
1681 Function name : CHECK_HXT_INSTALLED
1682 Creation date : 10-Oct-2006
1683 Purpose : This procedure returns true when OTL Product
1684 is Installed.
1685 *****************************************************************************/
1686 PROCEDURE CHECK_HXT_INSTALLED(do_upg OUT NOCOPY VARCHAR2)
1687 is
1688
1689 PSP_APPLICATION_ID constant number:=809;
1690 PSP_STATUS_INSTALLED constant varchar2(2):='I';
1691
1692 l_installed fnd_product_installations.status%type;
1693
1694 cursor csr_psp_installed is
1695 select status
1696 from fnd_product_installations
1697 where application_id = PSP_APPLICATION_ID;
1698
1699 l_do_submit varchar2(10) := 'FALSE';
1700
1701 begin
1702
1703 open csr_psp_installed;
1704 fetch csr_psp_installed into l_installed;
1705 if ( l_installed =PSP_STATUS_INSTALLED ) then
1706 l_do_submit := 'TRUE';
1707 end if;
1708 close csr_psp_installed;
1709
1710 do_upg := l_do_submit;
1711
1712 END CHECK_HXT_INSTALLED;
1713
1714 end hxc_tcsummary_migrate;
[Home] [Help]