[Home] [Help]
PACKAGE BODY: APPS.PAY_GENERIC_UPGRADE
Source
1 PACKAGE BODY pay_generic_upgrade AS
2 /* $Header: pycogus.pkb 120.5.12010000.1 2008/07/27 22:22:21 appldev ship $ */
3
4
5 /* Name : set_upgrade_status
6 Purpose : This sets the upgrade status.
7 Arguments :
8 Notes :
9 */
10 procedure set_upgrade_status (p_upg_def_id in number,
11 p_upg_lvl in varchar2,
12 p_bus_grp in number,
13 p_leg_code in varchar2,
14 p_status in varchar2)
15 is
16 l_status varchar2(10);
17 begin
18 --
19 if (p_upg_lvl = 'B') then
20 --
21 begin
22 --
23 select status
24 into l_status
25 from pay_upgrade_status
26 where upgrade_definition_id = p_upg_def_id
27 and business_group_id = p_bus_grp;
28 --
29 /* If we trying to reset a completed status
30 then error
31 */
32 if ( l_status = 'C'
33 and p_status <> 'C') then
34 --
35 pay_core_utils.assert_condition(
36 'pay_generic_upgrade.set_upgrade_status:2',
37 1 = 2);
38 --
39 end if;
40 --
41 update pay_upgrade_status
42 set status = p_status
43 where upgrade_definition_id = p_upg_def_id
44 and business_group_id = p_bus_grp;
45 --
46 exception
47 when no_data_found then
48 if (p_status in ('U', 'P')) then
49 insert into pay_upgrade_status
50 (upgrade_definition_id,
51 status,
52 business_group_id)
53 values (p_upg_def_id,
54 p_status,
55 p_bus_grp);
56 else
57 pay_core_utils.assert_condition(
58 'pay_generic_upgrade.set_upgrade_status:1',
59 1 = 2);
60 end if;
61 --
62 end;
63 --
64 elsif (p_upg_lvl = 'L') then
65 --
66 begin
67 --
68 select status
69 into l_status
70 from pay_upgrade_status
71 where upgrade_definition_id = p_upg_def_id
72 and legislation_code = p_leg_code;
73 --
74 /* If we trying to reset a completed status
75 then error
76 */
77 if ( l_status = 'C'
78 and p_status <> 'C') then
79 --
80 pay_core_utils.assert_condition(
81 'pay_generic_upgrade.set_upgrade_status:2',
82 1 = 2);
83 --
84 end if;
85 --
86 update pay_upgrade_status
87 set status = p_status
88 where upgrade_definition_id = p_upg_def_id
89 and legislation_code = p_leg_code;
90 --
91 exception
92 when no_data_found then
93 if (p_status in ('U', 'P')) then
94 insert into pay_upgrade_status
95 (upgrade_definition_id,
96 status,
97 legislation_code)
98 values (p_upg_def_id,
99 p_status,
100 p_leg_code);
101 else
102 pay_core_utils.assert_condition(
103 'pay_generic_upgrade.set_upgrade_status:1',
104 1 = 2);
105 end if;
106 --
107 end;
108 --
109 elsif (p_upg_lvl = 'G') then
110 --
111 begin
112 --
113 select status
114 into l_status
115 from pay_upgrade_status
116 where upgrade_definition_id = p_upg_def_id
117 and legislation_code is null
118 and business_group_id is null;
119 --
120 /* If we trying to reset a completed status
121 then error
122 */
123 if ( l_status = 'C'
124 and p_status <> 'C') then
125 --
126 pay_core_utils.assert_condition(
127 'pay_generic_upgrade.set_upgrade_status:2',
128 1 = 2);
129 --
130 end if;
131 --
132 update pay_upgrade_status
133 set status = p_status
134 where upgrade_definition_id = p_upg_def_id
135 and legislation_code is null
136 and business_group_id is null;
137 --
138 exception
139 when no_data_found then
140 if (p_status in ('U', 'P', 'C')) then
141 insert into pay_upgrade_status
142 (upgrade_definition_id,
143 status
144 )
145 values (p_upg_def_id,
146 p_status
147 );
148 else
149
150 pay_core_utils.assert_condition(
151 'pay_generic_upgrade.set_upgrade_status:1',
152 1 = 2);
153 end if;
154 --
155 end;
156 --
157 end if;
158 --
159 end set_upgrade_status;
160
161 /* Name : new_business_group
162 Purpose : For a new business group mark all the appropriate
163 upgrades as done.
164 Arguments :
165 Notes :
166 There is no point upgrading a brand new
167 business group, just mark then as upgraded.
168 */
169 procedure new_business_group (p_bus_grp_id in number,
170 p_leg_code in varchar2)
171 is
172 cursor get_upg_def (p_legislation in varchar2)
173 is
174 select pud.upgrade_definition_id
175 from pay_upgrade_definitions pud
176 where pud.upgrade_level = 'B' -- Business Group
177 and ( pud.legislation_code = p_legislation
178 or ( pud.legislation_code is null
179 and ( nvl(pud.legislatively_enabled, 'N') = 'N'
180 or ( nvl(pud.legislatively_enabled, 'N') = 'Y'
181 and exists (select ''
182 from pay_upgrade_legislations pul
183 where pul.upgrade_definition_id
184 = pud.upgrade_definition_id
185 and pul.legislation_code = p_legislation
186 )
187 )
188 )
189 )
190 );
191 cursor get_upg_def_leg (p_legislation in varchar2,
192 p_business_group in number)
193 is
194 select pud.upgrade_definition_id
195 from pay_upgrade_definitions pud
196 where pud.upgrade_level = 'L' -- Business Group
197 and ( pud.legislation_code = p_legislation
198 or ( pud.legislation_code is null
199 and nvl(pud.legislatively_enabled, 'N') = 'Y'
200 and exists (select ''
201 from pay_upgrade_legislations pul
202 where pul.upgrade_definition_id
203 = pud.upgrade_definition_id
204 and pul.legislation_code = p_legislation
205 )
206 )
207 )
208 and not exists (select 1
209 from per_business_groups_perf
210 where legislation_code = p_legislation
211 and business_group_id <> p_business_group
212 );
213 cursor get_upg_def_glo (p_business_group in number)
214 is
215 select pud.upgrade_definition_id
216 from pay_upgrade_definitions pud
217 where pud.upgrade_level = 'G'
218 and nvl(pud.legislatively_enabled, 'N') = 'N'
219 and not exists (select 1
220 from per_business_groups_perf
221 where business_group_id <> p_business_group
222 );
223 begin
224 --
225 for bgrec in get_upg_def(p_leg_code) loop
226 set_upgrade_status (p_upg_def_id => bgrec.upgrade_definition_id,
227 p_upg_lvl => 'B',
228 p_bus_grp => p_bus_grp_id,
229 p_leg_code => p_leg_code,
230 p_status => 'P'
231 );
232 set_upgrade_status (p_upg_def_id => bgrec.upgrade_definition_id,
233 p_upg_lvl => 'B',
234 p_bus_grp => p_bus_grp_id,
235 p_leg_code => p_leg_code,
236 p_status => 'C'
237 );
238 end loop;
239 --
240 for legrec in get_upg_def_leg(p_leg_code, p_bus_grp_id) loop
241 set_upgrade_status (p_upg_def_id => legrec.upgrade_definition_id,
242 p_upg_lvl => 'L',
243 p_bus_grp => p_bus_grp_id,
244 p_leg_code => p_leg_code,
245 p_status => 'P'
246 );
247 set_upgrade_status (p_upg_def_id => legrec.upgrade_definition_id,
248 p_upg_lvl => 'L',
249 p_bus_grp => p_bus_grp_id,
250 p_leg_code => p_leg_code,
251 p_status => 'C'
252 );
253 end loop;
254 --
255 for glorec in get_upg_def_glo(p_bus_grp_id) loop
256 set_upgrade_status (p_upg_def_id => glorec.upgrade_definition_id,
257 p_upg_lvl => 'G',
258 p_bus_grp => p_bus_grp_id,
259 p_leg_code => p_leg_code,
260 p_status => 'P'
261 );
262 set_upgrade_status (p_upg_def_id => glorec.upgrade_definition_id,
263 p_upg_lvl => 'G',
264 p_bus_grp => p_bus_grp_id,
265 p_leg_code => p_leg_code,
266 p_status => 'C'
267 );
268 end loop;
269 --
270 end new_business_group;
271 --
272 /* Name : range_cursor
273 Purpose : This returns the select statement that is used to created the
274 range rows.
275 Arguments :
276 Notes :
277 */
278 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
279 l_upg_def_nm pay_upgrade_definitions.short_name%type;
280 l_upg_def_id pay_upgrade_definitions.upgrade_definition_id%type;
281 l_upg_level pay_upgrade_definitions.upgrade_level%type;
282 l_bus_grp_id pay_payroll_actions.business_group_id%type;
283 l_leg_code per_business_groups.legislation_code%type;
284 l_thread_level pay_upgrade_definitions.threading_level%type;
285 l_report_type pay_report_format_mappings_f.report_type%type;
286 l_rep_id pay_report_format_mappings_f.report_format_mapping_id%type;
287 --
288 begin
289
290 /* chk if generic report or generic upgrade */
291 commit;
292
293 select rf.report_type,rf.report_format_mapping_id
294 into l_report_type,l_rep_id
295 from pay_report_format_mappings_f rf,
296 pay_payroll_actions pact
297 where pact.payroll_action_id=pactid
298 and rf.report_type =pact.report_type
299 and rf.report_qualifier=pact.report_qualifier
300 and rf.report_category=pact.report_category
301 and pact.effective_date between rf.effective_start_date
302 and rf.effective_end_date;
303
304
305 if l_report_type='GENERIC_REPORT'
306 then
307
308 select 'B',rg.thread_level
309 into l_upg_level, l_thread_level
310 from pay_report_groups rg,pay_payroll_actions ppa
311 where rg.report_format_mapping_id=l_rep_id
312 and pay_core_utils.get_parameter('REP_GROUP', ppa.legislative_parameters)=rg.short_name
313 and ppa.payroll_action_id=pactid;
314
315 else
316 --
317 select pay_core_utils.get_parameter('UPG_DEF_NAME',
318 ppa.legislative_parameters),
319 ppa.business_group_id,
320 pbg.legislation_code
321 into l_upg_def_nm,
322 l_bus_grp_id,
323 l_leg_code
324 from pay_payroll_actions ppa,
325 per_business_groups_perf pbg
326 where ppa.payroll_action_id = pactid
327 and pbg.business_group_id = ppa.business_group_id;
328 --
329 select upgrade_level,
330 upgrade_definition_id,
331 threading_level
332 into l_upg_level,
333 l_upg_def_id,
334 l_thread_level
335 from pay_upgrade_definitions
336 where short_name = l_upg_def_nm;
337 --
338 end if;
339 if (l_thread_level in ('PER', 'ASG')) then
340 --
341 if (l_upg_level = 'B') then
342 --
343 sqlstr := 'select distinct asg.person_id
344 from
345 per_all_assignments_f asg,
346 pay_payroll_actions pa1
347 where pa1.payroll_action_id = :payroll_action_id
348 and asg.business_group_id = pa1.business_group_id
349 order by asg.person_id';
350 --
351 elsif (l_upg_level = 'L') then
352 --
353 sqlstr := 'select distinct asg.person_id
354 from
355 per_all_assignments_f asg,
356 pay_payroll_actions pa1,
357 per_business_groups_perf pbg1,
358 per_business_groups_perf pbg
359 where pa1.payroll_action_id = :payroll_action_id
360 and pbg.business_group_id = pa1.business_group_id
361 and pbg1.legislation_code = pbg.legislation_code
362 and asg.business_group_id = pbg1.business_group_id
363 order by asg.person_id';
364 --
365 elsif (l_upg_level = 'G') then
366 --
367 sqlstr := 'select distinct asg.person_id
368 from
369 per_all_assignments_f asg,
370 pay_payroll_actions pa1
371 where pa1.payroll_action_id = :payroll_action_id
372 order by asg.person_id';
373 --
374 else
375 --
376 pay_core_utils.assert_condition(
377 'pay_generic_upgrade.range_cursor:2',
378 1 = 2);
379 --
380 end if;
381 --
382 elsif (l_thread_level = 'PET') then
383 --
384 if (l_upg_level = 'B') then
385 --
386 sqlstr := 'select distinct pet.element_type_id
387 from
388 pay_element_types_f pet,
389 pay_payroll_actions pa1
390 where pa1.payroll_action_id = :payroll_action_id
391 and pet.business_group_id = pa1.business_group_id
392 order by pet.element_type_id';
393 --
394 elsif (l_upg_level = 'L') then
395 --
396 sqlstr := 'select distinct pet.element_type_id
397 from
398 pay_element_types_f pet,
399 pay_payroll_actions pa1,
400 per_business_groups_perf pbg1,
401 per_business_groups_perf pbg
402 where pa1.payroll_action_id = :payroll_action_id
403 and pbg.business_group_id = pa1.business_group_id
404 and pbg1.legislation_code = pbg.legislation_code
405 and (pet.business_group_id = pbg1.business_group_id
406 or pet.legislation_code = pbg1.legislation_code)
407 order by pet.element_type_id';
408 --
409 elsif (l_upg_level = 'G') then
410 --
411 sqlstr := 'select distinct pet.element_type_id
412 from
413 pay_element_types_f pet,
414 pay_payroll_actions pa1
415 where pa1.payroll_action_id = :payroll_action_id
416 order by pet.element_type_id';
417 --
418 else
419 --
420 pay_core_utils.assert_condition(
421 'pay_generic_upgrade.range_cursor:3',
422 1 = 2);
423 --
424 end if;
425 --
426 else
427 --
428 pay_core_utils.assert_condition(
429 'pay_generic_upgrade.range_cursor:1',
430 1 = 2);
431 --
432 end if;
433 --
434 if l_report_type<>'GENERIC_REPORT'
435 then
436 set_upgrade_status (l_upg_def_id,
437 l_upg_level,
438 l_bus_grp_id,
439 l_leg_code,
440 'P');
441 end if;
442 --
443 end range_cursor;
444 --
445 /* Name : do_qualification
446 Purpose : This is used to indicate whether the object needs to be upgraded
447 Arguments :
448 Notes :
449 */
450 procedure do_qualification(p_object_id in number,
451 p_qual_proc in varchar2,
452 p_qualified out nocopy boolean
453 )
454 is
455 sql_cur number;
456 l_rows number;
457 statem varchar2(256);
458 l_qualifer varchar2(10);
459 begin
460 --
461 if (p_qual_proc is not null) then
462 --
463 statem := 'BEGIN '||p_qual_proc||'(:objectid, :qual); END;';
464 --
465 hr_utility.trace(statem);
466
467 sql_cur := dbms_sql.open_cursor;
468 dbms_sql.parse(sql_cur,
469 statem,
470 dbms_sql.v7);
471 --
472 dbms_sql.bind_variable(sql_cur, 'objectid', p_object_id);
473 dbms_sql.bind_variable(sql_cur, 'qual', l_qualifer, 10);
474 l_rows := dbms_sql.execute(sql_cur);
475 if (l_rows = 1) then
476 dbms_sql.variable_value(sql_cur, 'qual',
477 l_qualifer);
478 dbms_sql.close_cursor(sql_cur);
479 --
480 else
481 dbms_sql.close_cursor(sql_cur);
482 pay_core_utils.assert_condition(
483 'pay_generic_upgrade.do_qualification:1',
484 1 = 2);
485 end if;
486 --
487 else
488 l_qualifer:= 'Y';
489 end if;
490 --
491 if (l_qualifer = 'Y') then
492 p_qualified := TRUE;
493 else
494 p_qualified := FALSE;
495 end if;
496 --
497 end do_qualification;
498 --
499 /* Name : create_object_action
500 Purpose : This creates the object action if it passes qualification.
501 Arguments :
502 Notes :
503 */
504 procedure create_object_action(p_object_id in number,
505 p_object_type in varchar2,
506 p_qual_proc in varchar2,
507 p_pactid in number,
508 p_chunk in number
509 )
510 is
511 l_action_id pay_temp_object_actions.object_action_id%type;
512 l_qualified boolean;
513 begin
514
515 --
516 do_qualification(p_object_id, p_qual_proc, l_qualified);
517 --
518 if (l_qualified = TRUE) then
519 --
520 select pay_assignment_actions_s.nextval
521 into l_action_id
522 from dual;
523 --
524 hr_nonrun_asact.insact(lockingactid => l_action_id,
525 pactid => p_pactid,
526 chunk => p_chunk,
527 object_id => p_object_id,
528 object_type => p_object_type,
529 p_transient_action => TRUE);
530 --
531 end if;
532 --
533 end create_object_action;
534 --
535 /* Name : action_creation
536 Purpose : This creates the assignment actions for a specific chunk.
537 Arguments :
538 Notes :
539 */
540 --
541 procedure action_creation(p_pactid in number,
542 p_stperson in number,
543 p_endperson in number,
544 p_chunk in number) is
545 cursor c_bgp_per (cp_pactid number,
546 cp_stperson number,
547 cp_endperson number
548 ) is
549 select /*+ INDEX(ppf PER_PEOPLE_F_PK)*/
550 distinct ppf.person_id
551 from per_all_people_f ppf,
552 pay_payroll_actions ppa
553 where ppa.payroll_action_id = cp_pactid
554 and ppa.business_group_id = ppf.business_group_id
555 and ppf.person_id between cp_stperson and cp_endperson;
556 --
557 cursor c_bgp_asg (cp_pactid number,
558 cp_stperson number,
559 cp_endperson number
560 ) is
561 select distinct paf.assignment_id
562 from
563 per_all_assignments_f paf,
564 pay_payroll_actions ppa
565 where ppa.payroll_action_id = cp_pactid
566 and ppa.business_group_id = paf.business_group_id
567 and paf.person_id between cp_stperson and cp_endperson;
568 --
569 cursor c_bgp_pet (cp_pactid number,
570 cp_stetid number,
571 cp_endetid number
572 ) is
573 select distinct pet.element_type_id
574 from
575 pay_element_types_f pet,
576 pay_payroll_actions ppa
577 where ppa.payroll_action_id = cp_pactid
578 and ppa.business_group_id = pet.business_group_id
579 and pet.element_type_id between cp_stetid and cp_endetid;
580 --
581 cursor c_leg_per (cp_pactid number,
582 cp_stperson number,
583 cp_endperson number
584 ) is
585 select /*+ INDEX(ppf PER_PEOPLE_F_PK)*/
586 distinct ppf.person_id
587 from per_all_people_f ppf,
588 pay_payroll_actions ppa,
589 per_business_groups_perf pbg,
590 per_business_groups_perf pbg1
591 where ppa.payroll_action_id = cp_pactid
592 and ppa.business_group_id = pbg.business_group_id
593 and pbg.legislation_code = pbg1.legislation_code
594 and pbg1.business_group_id = ppf.business_group_id
595 and ppf.person_id between cp_stperson and cp_endperson;
596 --
597 cursor c_leg_asg (cp_pactid number,
598 cp_stperson number,
599 cp_endperson number
600 ) is
601 select distinct paf.assignment_id
602 from
603 per_all_assignments_f paf,
604 pay_payroll_actions ppa,
605 per_business_groups_perf pbg,
606 per_business_groups_perf pbg1
607 where ppa.payroll_action_id = cp_pactid
608 and ppa.business_group_id = pbg.business_group_id
609 and pbg.legislation_code = pbg1.legislation_code
610 and pbg1.business_group_id = paf.business_group_id
611 and paf.person_id between cp_stperson and cp_endperson;
612 --
613 cursor c_leg_pet (cp_pactid number,
614 cp_stetid number,
615 cp_endetid number
616 ) is
617 select distinct pet.element_type_id
618 from
619 pay_element_types_f pet,
620 pay_payroll_actions ppa,
621 per_business_groups_perf pbg,
622 per_business_groups_perf pbg1
623 where ppa.payroll_action_id = cp_pactid
624 and ppa.business_group_id = pbg.business_group_id
625 and pbg.legislation_code = pbg1.legislation_code
626 and ( pbg1.business_group_id = pet.business_group_id
627 or pet.legislation_code = pbg1.legislation_code
628 )
629 and pet.element_type_id between cp_stetid and cp_endetid;
630 --
631 cursor c_glo_per (
632 cp_stperson number,
633 cp_endperson number
634 ) is
635 select /*+ INDEX(ppf PER_PEOPLE_F_PK)*/
636 distinct ppf.person_id
637 from per_all_people_f ppf
638 where ppf.person_id between cp_stperson and cp_endperson;
639 --
640 cursor c_glo_asg (
641 cp_stperson number,
642 cp_endperson number
643 ) is
644 select distinct paf.assignment_id
645 from
646 per_all_assignments_f paf
647 where paf.person_id between cp_stperson and cp_endperson;
648 --
649 cursor c_glo_pet (
650 cp_stetid number,
651 cp_endetid number
652 ) is
653 select distinct pet.element_type_id
654 from pay_element_types_f pet
655 where pet.element_type_id between cp_stetid and cp_endetid;
656 --
657 l_upg_def_id pay_upgrade_definitions.upgrade_definition_id%type;
658 l_upg_def_nm pay_upgrade_definitions.short_name%type;
659 l_upg_level pay_upgrade_definitions.upgrade_level%type;
660 l_thread_level pay_upgrade_definitions.threading_level%type;
661 l_qual_proc pay_upgrade_definitions.qualifying_procedure%type;
662 l_report_type pay_report_format_mappings_f.report_type%type;
663 l_rep_id pay_report_format_mappings_f.report_format_mapping_id%type;
664 --
665 begin
666
667 /* chk if generic report or generic upgrade */
668
669 select rf.report_type,rf.report_format_mapping_id
670 into l_report_type,l_rep_id
671 from pay_report_format_mappings_f rf,
672 pay_payroll_actions pact
673 where pact.payroll_action_id=p_pactid
674 and rf.report_type =pact.report_type
675 and rf.report_qualifier=pact.report_qualifier
676 and rf.report_category=pact.report_category
677 and pact.effective_date between rf.effective_start_date
678 and rf.effective_end_date;
679
680
681 if l_report_type='GENERIC_REPORT'
682 then
683 select 'B',rg.thread_level,
684 rg.qualifying_procedure
685 into l_upg_level,
686 l_thread_level,
687 l_qual_proc
688 from pay_report_groups rg,pay_payroll_actions ppa
689 where rg.report_format_mapping_id=l_rep_id
690 and pay_core_utils.get_parameter('REP_GROUP', ppa.legislative_parameters)=rg.short_name
691 and ppa.payroll_action_id=p_pactid;
692
693
694 else
695 select pay_core_utils.get_parameter('UPG_DEF_NAME',
696 ppa.legislative_parameters)
697 into l_upg_def_nm
698 from pay_payroll_actions ppa
699 where payroll_action_id = p_pactid;
700 --
701 select upgrade_level,
702 threading_level,
703 upgrade_definition_id,
704 qualifying_procedure
705 into l_upg_level,
706 l_thread_level,
707 l_upg_def_id,
708 l_qual_proc
709 from pay_upgrade_definitions
710 where short_name = l_upg_def_nm;
711 end if;
712 --
713 if (l_upg_level = 'B') then
714 if (l_thread_level = 'PER') then
715 for perrec in c_bgp_per(p_pactid, p_stperson, p_endperson) loop
716 --
717 create_object_action(p_object_id => perrec.person_id,
718 p_object_type => 'PER',
719 p_qual_proc => l_qual_proc,
720 p_pactid => p_pactid,
721 p_chunk => p_chunk
722 );
723 --
724 end loop;
725 elsif (l_thread_level = 'ASG') then
726 for asgrec in c_bgp_asg(p_pactid, p_stperson, p_endperson) loop
727 --
728 create_object_action(p_object_id => asgrec.assignment_id,
729 p_object_type => 'ASG',
730 p_qual_proc => l_qual_proc,
731 p_pactid => p_pactid,
732 p_chunk => p_chunk
733 );
734 --
735 end loop;
736 elsif (l_thread_level = 'PET') then
737 for etrec in c_bgp_pet(p_pactid, p_stperson, p_endperson) loop
738 --
739 create_object_action(p_object_id => etrec.element_type_id,
740 p_object_type => 'PET',
741 p_qual_proc => l_qual_proc,
742 p_pactid => p_pactid,
743 p_chunk => p_chunk
744 );
745 --
746 end loop;
747 else
748 pay_core_utils.assert_condition(
749 'pay_generic_upgrade.action_creation:1',
750 1 = 2);
751 end if;
752 elsif (l_upg_level = 'L') then
753 if (l_thread_level = 'PER') then
754 for perrec in c_leg_per(p_pactid, p_stperson, p_endperson) loop
755 --
756 create_object_action(p_object_id => perrec.person_id,
757 p_object_type => 'PER',
758 p_qual_proc => l_qual_proc,
759 p_pactid => p_pactid,
760 p_chunk => p_chunk
761 );
762 --
763 end loop;
764 elsif (l_thread_level = 'ASG') then
765 for asgrec in c_leg_asg(p_pactid, p_stperson, p_endperson) loop
766 --
767 create_object_action(p_object_id => asgrec.assignment_id,
768 p_object_type => 'ASG',
769 p_qual_proc => l_qual_proc,
770 p_pactid => p_pactid,
771 p_chunk => p_chunk
772 );
773 --
774 end loop;
775 elsif (l_thread_level = 'PET') then
776 for etrec in c_leg_pet(p_pactid, p_stperson, p_endperson) loop
777 --
778 create_object_action(p_object_id => etrec.element_type_id,
779 p_object_type => 'PET',
780 p_qual_proc => l_qual_proc,
781 p_pactid => p_pactid,
782 p_chunk => p_chunk
783 );
784 --
785 end loop;
786 else
787 pay_core_utils.assert_condition(
788 'pay_generic_upgrade.action_creation:2',
789 1 = 2);
790 end if;
791 elsif (l_upg_level = 'G') then
792 if (l_thread_level = 'PER') then
793 for perrec in c_glo_per(p_stperson, p_endperson) loop
794 --
795 create_object_action(p_object_id => perrec.person_id,
796 p_object_type => 'PER',
797 p_qual_proc => l_qual_proc,
798 p_pactid => p_pactid,
799 p_chunk => p_chunk
800 );
801 --
802 end loop;
803 elsif (l_thread_level = 'ASG') then
804 for asgrec in c_glo_asg(p_stperson, p_endperson) loop
805 --
806 create_object_action(p_object_id => asgrec.assignment_id,
807 p_object_type => 'ASG',
808 p_qual_proc => l_qual_proc,
809 p_pactid => p_pactid,
810 p_chunk => p_chunk
811 );
812 --
813 end loop;
814 elsif (l_thread_level = 'PET') then
815 for etrec in c_glo_pet(p_stperson, p_endperson) loop
816 --
817 create_object_action(p_object_id => etrec.element_type_id,
818 p_object_type => 'PET',
819 p_qual_proc => l_qual_proc,
820 p_pactid => p_pactid,
821 p_chunk => p_chunk
822 );
823 --
824 end loop;
825 else
826 pay_core_utils.assert_condition(
827 'pay_generic_upgrade.action_creation:3',
828 1 = 2);
829 end if;
830 else
831 pay_core_utils.assert_condition(
832 'pay_generic_upgrade.action_creation:4',
833 1 = 2);
834 end if;
835 end action_creation;
836 --
837 /* Name : archinit
838 Purpose : This performs the US specific initialisation section.
839 Arguments :
840 Notes :
841 */
842 procedure archinit(p_payroll_action_id in number) is
843 jurisdiction_code pay_state_rules.jurisdiction_code%TYPE;
844 l_state VARCHAR2(30);
845 begin
846 null;
847 end archinit;
848 --
849
850 /* Name : archive_data
851 Purpose : This performs the US specific employee context setting for the SQWL
852 report.
853 Arguments :
854 Notes :
855 */
856 procedure upgrade_data(p_assactid in number, p_effective_date in date) is
857 --
858 sql_cur number;
859 ignore number;
860 upgrade_proc pay_upgrade_definitions.upgrade_procedure%TYPE;
861 statem varchar2(256);
862 l_upg_def_id pay_upgrade_definitions.upgrade_definition_id%type;
863 l_upg_def_nm pay_upgrade_definitions.short_name%type;
864 object_id pay_temp_object_actions.object_id%type;
865 --
866 begin
867 --
868 select pay_core_utils.get_parameter('UPG_DEF_NAME',
869 ppa.legislative_parameters),
870 ptoa.object_id
871 into l_upg_def_nm,
872 object_id
873 from pay_payroll_actions ppa,
874 pay_temp_object_actions ptoa
875 where ppa.payroll_action_id = ptoa.payroll_action_id
876 and ptoa.object_action_id = p_assactid;
877 --
878 select upgrade_procedure,
879 upgrade_definition_id
880 into upgrade_proc,
881 l_upg_def_id
882 from pay_upgrade_definitions
883 where short_name = l_upg_def_nm;
884 --
885 statem := 'BEGIN '||upgrade_proc||'(:objectid); END;';
886 --
887 hr_utility.trace(statem);
888
889 sql_cur := dbms_sql.open_cursor;
890 dbms_sql.parse(sql_cur,
891 statem,
892 dbms_sql.v7);
893 --
894 dbms_sql.bind_variable(sql_cur, 'objectid', object_id);
895 ignore := dbms_sql.execute(sql_cur);
896 dbms_sql.close_cursor(sql_cur);
897 --
898 end upgrade_data;
899
900 /* Name : deinitialise
901 Purpose : This procedure simply removes all the actions processed
902 in this run
903 Arguments :
904 Notes :
905 */
906 procedure deinitialise (pactid in number)
907 is
908 --
909 l_remove_act varchar2(10);
910 cnt_incomplete_actions number;
911 l_upg_def_id pay_upgrade_definitions.upgrade_definition_id%type;
912 l_upg_def_nm pay_upgrade_definitions.short_name%type;
913 l_upg_level pay_upgrade_definitions.upgrade_level%type;
914 l_bus_grp_id pay_payroll_actions.business_group_id%type;
915 l_leg_code per_business_groups.legislation_code%type;
916 l_report_type pay_payroll_actions.report_type%type;
917 --
918 begin
919 --
920
921 select pay_core_utils.get_parameter('UPG_DEF_NAME',
922 ppa.legislative_parameters),
923 pay_core_utils.get_parameter('REMOVE_ACT',
924 ppa.legislative_parameters),
925 ppa.business_group_id,
926 pbg.legislation_code,
927 ppa.report_type
928 into l_upg_def_nm,
929 l_remove_act,
930 l_bus_grp_id,
931 l_leg_code,
932 l_report_type
933 from pay_payroll_actions ppa,
934 per_business_groups_perf pbg
935 where ppa.payroll_action_id = pactid
936 and pbg.business_group_id = ppa.business_group_id;
937 --
938 if l_report_type='GENERIC_REPORT'
939 then
940 select count(*)
941 into cnt_incomplete_actions
942 from pay_temp_object_actions
943 where payroll_action_id = pactid
944 and action_status <> 'C';
945
946 if (l_remove_act is null or l_remove_act = 'Y') then
947 pay_archive.remove_report_actions(pactid);
948 end if;
949 else
950 select upgrade_level,
951 upgrade_definition_id
952 into l_upg_level,
953 l_upg_def_id
954 from pay_upgrade_definitions
955 where short_name = l_upg_def_nm;
956 --
957 select count(*)
958 into cnt_incomplete_actions
959 from pay_temp_object_actions
960 where payroll_action_id = pactid
961 and action_status <> 'C';
962 --
963 --
964 if (cnt_incomplete_actions = 0) then
965 --
966 set_upgrade_status (l_upg_def_id,
967 l_upg_level,
968 l_bus_grp_id,
969 l_leg_code,
970 'C');
971 --
972 if (l_remove_act is null or l_remove_act = 'Y') then
973 pay_archive.remove_report_actions(pactid);
974 end if;
975 end if;
976 end if;
977 --
978 end deinitialise;
979 --
980 --
981 END pay_generic_upgrade;