[Home] [Help]
PACKAGE BODY: APPS.PAY_GENERIC_UPGRADE
Source
1 PACKAGE BODY pay_generic_upgrade AS
2 /* $Header: pycogus.pkb 120.5.12010000.6 2009/10/21 05:28:30 sivanara 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 are 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 are 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 are 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
192
193 cursor get_upg_def_leg (p_legislation in varchar2,
194 p_business_group in number)
195 is
196 select pud.upgrade_definition_id
197 from pay_upgrade_definitions pud
198 where pud.upgrade_level = 'L' -- Business Group
199 and ( pud.legislation_code = p_legislation
200 or ( pud.legislation_code is null
201 and nvl(pud.legislatively_enabled, 'N') = 'Y'
202 and exists (select ''
203 from pay_upgrade_legislations pul
204 where pul.upgrade_definition_id
205 = pud.upgrade_definition_id
206 and pul.legislation_code = p_legislation
207 )
208 )
209 )
210 and not exists (select 1
211 from per_business_groups_perf
212 where legislation_code = p_legislation
213 and business_group_id <> p_business_group
214 )
215 and not exists (select 1 --Bug 7296843
216 from hr_organization_information hoi_1 , --'not exists' validation being done against the base table itself.
217 hr_organization_information hoi_2
218 where hoi_1.organization_id <> p_business_group
219 and hoi_1.organization_id = hoi_2.organization_id
220 and hoi_1.org_information9 = p_legislation
221 and hoi_2.org_information_context ='CLASS'
222 and hoi_1.org_information_context ='Business Group Information'
223 and hoi_2.org_information1 = 'HR_BG'
224 and hoi_2.org_information2='N'
225 );
226
227
228 cursor get_upg_def_glo (p_business_group in number)
229 is
230 select pud.upgrade_definition_id
231 from pay_upgrade_definitions pud
232 where pud.upgrade_level = 'G'
233 and nvl(pud.legislatively_enabled, 'N') = 'N'
234 and not exists (select 1
235 from per_business_groups_perf
236 where business_group_id <> p_business_group
237 );
238 begin
239 --
240 for bgrec in get_upg_def(p_leg_code) loop
241 set_upgrade_status (p_upg_def_id => bgrec.upgrade_definition_id,
242 p_upg_lvl => 'B',
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 => bgrec.upgrade_definition_id,
248 p_upg_lvl => 'B',
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 legrec in get_upg_def_leg(p_leg_code, p_bus_grp_id) loop
256 set_upgrade_status (p_upg_def_id => legrec.upgrade_definition_id,
257 p_upg_lvl => 'L',
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 => legrec.upgrade_definition_id,
263 p_upg_lvl => 'L',
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 for glorec in get_upg_def_glo(p_bus_grp_id) loop
271 set_upgrade_status (p_upg_def_id => glorec.upgrade_definition_id,
272 p_upg_lvl => 'G',
273 p_bus_grp => p_bus_grp_id,
274 p_leg_code => p_leg_code,
275 p_status => 'P'
276 );
277 set_upgrade_status (p_upg_def_id => glorec.upgrade_definition_id,
278 p_upg_lvl => 'G',
279 p_bus_grp => p_bus_grp_id,
280 p_leg_code => p_leg_code,
281 p_status => 'C'
282 );
283 end loop;
284 --
285 end new_business_group;
286 --
287 /* Name : range_cursor
288 Purpose : This returns the select statement that is used to created the
289 range rows.
290 Arguments :
291 Notes :
292 */
293 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
294 l_upg_def_nm pay_upgrade_definitions.short_name%type;
295 l_upg_def_id pay_upgrade_definitions.upgrade_definition_id%type;
296 l_upg_level pay_upgrade_definitions.upgrade_level%type;
297 l_bus_grp_id pay_payroll_actions.business_group_id%type;
298 l_leg_code per_business_groups.legislation_code%type;
299 l_thread_level pay_upgrade_definitions.threading_level%type;
300 l_report_type pay_report_format_mappings_f.report_type%type;
301 l_rep_id pay_report_format_mappings_f.report_format_mapping_id%type;
302 --
303
304 statem varchar2(2000);
305 sql_cursor integer;
306 l_rows integer;
307 lv_sqlstr varchar2(2000);
308
309 begin
310
311 /* chk if generic report or generic upgrade */
312 commit;
313
314 select rf.report_type,rf.report_format_mapping_id
315 into l_report_type,l_rep_id
316 from pay_report_format_mappings_f rf,
317 pay_payroll_actions pact
318 where pact.payroll_action_id=pactid
319 and rf.report_type =pact.report_type
320 and rf.report_qualifier=pact.report_qualifier
321 and rf.report_category=pact.report_category
322 and pact.effective_date between rf.effective_start_date
323 and rf.effective_end_date;
324
325
326 if l_report_type='GENERIC_REPORT'
327 then
328
329 select 'B',rg.thread_level,rg.legislation_code
330 into l_upg_level, l_thread_level, l_leg_code
331 from pay_report_groups rg,pay_payroll_actions ppa
332 where rg.report_format_mapping_id=l_rep_id
333 and pay_core_utils.get_parameter('REP_GROUP', ppa.legislative_parameters)=rg.short_name
334 and ppa.payroll_action_id=pactid;
335
336 else
337 --
338 select pay_core_utils.get_parameter('UPG_DEF_NAME',
339 ppa.legislative_parameters),
340 ppa.business_group_id,
341 pbg.legislation_code
342 into l_upg_def_nm,
343 l_bus_grp_id,
344 l_leg_code
345 from pay_payroll_actions ppa,
346 per_business_groups_perf pbg
347 where ppa.payroll_action_id = pactid
348 and pbg.business_group_id = ppa.business_group_id;
349 --
350 select upgrade_level,
351 upgrade_definition_id,
352 threading_level
353 into l_upg_level,
354 l_upg_def_id,
355 l_thread_level
356 from pay_upgrade_definitions
357 where short_name = l_upg_def_nm;
358 --
359 end if;
360 if (l_thread_level in ('PER', 'ASG')) then
361 --
362 if (l_upg_level = 'B') then
363 --
364 if l_report_type='GENERIC_REPORT' then
365 statem := 'begin pay_'||l_leg_code||'_rules.payslip_range_cursor(';
366 statem := statem||':pactid, :p_sqlstr); end;';
367 --
368 sql_cursor := dbms_sql.open_cursor;
369 --
370 dbms_sql.parse(sql_cursor, statem, dbms_sql.v7);
371 --
372 dbms_sql.bind_variable(sql_cursor, 'pactid', pactid);
373 --
374 dbms_sql.bind_variable(sql_cursor, 'p_sqlstr', lv_sqlstr,2000);
375 --
376 Begin
377 l_rows := dbms_sql.execute (sql_cursor);
378 exception when others then
379 l_rows := 0;
380 end;
381 --
382 if (l_rows = 1) then
383 dbms_sql.variable_value(sql_cursor, 'p_sqlstr', lv_sqlstr);
384 dbms_sql.close_cursor(sql_cursor);
385 else
386 lv_sqlstr := null;
387 dbms_sql.close_cursor(sql_cursor);
388 end if;
389 sqlstr:=lv_sqlstr;
390 end if;
391
392 if sqlstr is null then
393 sqlstr := 'select distinct asg.person_id
394 from
395 per_all_assignments_f asg,
396 pay_payroll_actions pa1
397 where pa1.payroll_action_id = :payroll_action_id
398 and asg.business_group_id = pa1.business_group_id
399 order by asg.person_id';
400 end if;
401 --
402 elsif (l_upg_level = 'L') then
403 --
404 sqlstr := 'select distinct asg.person_id
405 from
406 per_all_assignments_f asg,
407 pay_payroll_actions pa1,
408 per_business_groups_perf pbg1,
409 per_business_groups_perf pbg
410 where pa1.payroll_action_id = :payroll_action_id
411 and pbg.business_group_id = pa1.business_group_id
412 and pbg1.legislation_code = pbg.legislation_code
413 and asg.business_group_id = pbg1.business_group_id
414 order by asg.person_id';
415 --
416 elsif (l_upg_level = 'G') then
417 --
418 sqlstr := 'select distinct asg.person_id
419 from
420 per_all_assignments_f asg,
421 pay_payroll_actions pa1
422 where pa1.payroll_action_id = :payroll_action_id
423 order by asg.person_id';
424 --
425 else
426 --
427 pay_core_utils.assert_condition(
428 'pay_generic_upgrade.range_cursor:2',
429 1 = 2);
430 --
431 end if;
432 --
433 elsif (l_thread_level = 'PET') then
434 --
435 if (l_upg_level = 'B') then
436 --
437 sqlstr := 'select distinct pet.element_type_id
438 from
439 pay_element_types_f pet,
440 pay_payroll_actions pa1
441 where pa1.payroll_action_id = :payroll_action_id
442 and pet.business_group_id = pa1.business_group_id
443 order by pet.element_type_id';
444 --
445 elsif (l_upg_level = 'L') then
446 --
447 sqlstr := 'select distinct pet.element_type_id
448 from
449 pay_element_types_f pet,
450 pay_payroll_actions pa1,
451 per_business_groups_perf pbg1,
452 per_business_groups_perf pbg
453 where pa1.payroll_action_id = :payroll_action_id
454 and pbg.business_group_id = pa1.business_group_id
455 and pbg1.legislation_code = pbg.legislation_code
456 and (pet.business_group_id = pbg1.business_group_id
457 or pet.legislation_code = pbg1.legislation_code)
458 order by pet.element_type_id';
459 --
460 elsif (l_upg_level = 'G') then
461 --
462 sqlstr := 'select distinct pet.element_type_id
463 from
464 pay_element_types_f pet,
465 pay_payroll_actions pa1
466 where pa1.payroll_action_id = :payroll_action_id
467 order by pet.element_type_id';
468 --
469 else
470 --
471 pay_core_utils.assert_condition(
472 'pay_generic_upgrade.range_cursor:3',
473 1 = 2);
474 --
475 end if;
476 --
477 else
478 --
479 pay_core_utils.assert_condition(
480 'pay_generic_upgrade.range_cursor:1',
481 1 = 2);
482 --
483 end if;
484 --
485 if l_report_type<>'GENERIC_REPORT'
486 then
487 set_upgrade_status (l_upg_def_id,
488 l_upg_level,
489 l_bus_grp_id,
490 l_leg_code,
491 'P');
492 end if;
493 --
494 end range_cursor;
495 --
496 /* Name : do_qualification
497 Purpose : This is used to indicate whether the object needs to be upgraded
498 Arguments :
499 Notes :
500 */
501 procedure do_qualification(p_object_id in number,
502 p_qual_proc in varchar2,
503 p_qualified out nocopy boolean
504 )
505 is
506 sql_cur number;
507 l_rows number;
508 statem varchar2(256);
509 l_qualifer varchar2(10);
510 begin
511 --
512 if (p_qual_proc is not null) then
513 --
514 statem := 'BEGIN '||p_qual_proc||'(:objectid, :qual); END;';
515 --
516 hr_utility.trace(statem);
517
518 sql_cur := dbms_sql.open_cursor;
519 dbms_sql.parse(sql_cur,
520 statem,
521 dbms_sql.v7);
522 --
523 dbms_sql.bind_variable(sql_cur, 'objectid', p_object_id);
524 dbms_sql.bind_variable(sql_cur, 'qual', l_qualifer, 10);
525 l_rows := dbms_sql.execute(sql_cur);
526 if (l_rows = 1) then
527 dbms_sql.variable_value(sql_cur, 'qual',
528 l_qualifer);
529 dbms_sql.close_cursor(sql_cur);
530 --
531 else
532 dbms_sql.close_cursor(sql_cur);
533 pay_core_utils.assert_condition(
534 'pay_generic_upgrade.do_qualification:1',
535 1 = 2);
536 end if;
537 --
538 else
539 l_qualifer:= 'Y';
540 end if;
541 --
542 if (l_qualifer = 'Y') then
543 p_qualified := TRUE;
544 else
545 p_qualified := FALSE;
546 end if;
547 --
548 end do_qualification;
549 --
550 /* Name : create_object_action
551 Purpose : This creates the object action if it passes qualification.
552 Arguments :
553 Notes :
554 */
555 procedure create_object_action(p_object_id in number,
556 p_object_type in varchar2,
557 p_qual_proc in varchar2,
558 p_pactid in number,
559 p_chunk in number
560 )
561 is
562 l_action_id pay_temp_object_actions.object_action_id%type;
563 l_qualified boolean;
564 begin
565
566 --
567 do_qualification(p_object_id, p_qual_proc, l_qualified);
568 --
569 if (l_qualified = TRUE) then
570 --
571 select pay_assignment_actions_s.nextval
572 into l_action_id
573 from dual;
574 --
575 hr_nonrun_asact.insact(lockingactid => l_action_id,
576 pactid => p_pactid,
577 chunk => p_chunk,
578 object_id => p_object_id,
579 object_type => p_object_type,
580 p_transient_action => TRUE);
581 --
582 end if;
583 --
584 end create_object_action;
585 --
586 /* Name : action_creation
587 Purpose : This creates the assignment actions for a specific chunk.
588 Arguments :
589 Notes :
590 */
591 --
592 procedure action_creation(p_pactid in number,
593 p_stperson in number,
594 p_endperson in number,
595 p_chunk in number) is
596 cursor c_bgp_per (cp_pactid number,
597 cp_stperson number,
598 cp_endperson number
599 ) is
600 select /*+ INDEX(ppf PER_PEOPLE_F_PK)*/
601 distinct ppf.person_id
602 from per_all_people_f ppf,
603 pay_payroll_actions ppa
604 where ppa.payroll_action_id = cp_pactid
605 and ppa.business_group_id = ppf.business_group_id
606 and ppf.person_id between cp_stperson and cp_endperson;
607 --
608 cursor c_bgp_asg (cp_pactid number,
609 cp_stperson number,
610 cp_endperson number
611 ) is
612 select distinct paf.assignment_id
613 from
614 per_all_assignments_f paf,
615 pay_payroll_actions ppa
616 where ppa.payroll_action_id = cp_pactid
617 and ppa.business_group_id = paf.business_group_id
618 and paf.person_id between cp_stperson and cp_endperson;
619 --
620 cursor c_bgp_asg_range (cp_pactid number,
621 c_chunk number
622 ) is
623 select distinct paf.assignment_id
624 from
625 per_all_assignments_f paf,
626 pay_payroll_actions ppa,
627 pay_population_ranges ppr
628 where ppr.chunk_number = c_chunk
629 and ppr.payroll_action_id = ppa.payroll_action_id
630 and ppa.payroll_action_id = cp_pactid
631 and ppa.business_group_id = paf.business_group_id
632 and paf.person_id = ppr.person_id;
633 --
634 cursor c_bgp_pet (cp_pactid number,
635 cp_stetid number,
636 cp_endetid number
637 ) is
638 select distinct pet.element_type_id
639 from
640 pay_element_types_f pet,
641 pay_payroll_actions ppa
642 where ppa.payroll_action_id = cp_pactid
643 and ppa.business_group_id = pet.business_group_id
644 and pet.element_type_id between cp_stetid and cp_endetid;
645 --
646 cursor c_leg_per (cp_pactid number,
647 cp_stperson number,
648 cp_endperson number
649 ) is
650 select /*+ INDEX(ppf PER_PEOPLE_F_PK)*/
651 distinct ppf.person_id
652 from per_all_people_f ppf,
653 pay_payroll_actions ppa,
654 per_business_groups_perf pbg,
655 per_business_groups_perf pbg1
656 where ppa.payroll_action_id = cp_pactid
657 and ppa.business_group_id = pbg.business_group_id
658 and pbg.legislation_code = pbg1.legislation_code
659 and pbg1.business_group_id = ppf.business_group_id
660 and ppf.person_id between cp_stperson and cp_endperson;
661 --
662 cursor c_leg_asg (cp_pactid number,
663 cp_stperson number,
664 cp_endperson number
665 ) is
666 select distinct paf.assignment_id
667 from
668 per_all_assignments_f paf,
669 pay_payroll_actions ppa,
670 per_business_groups_perf pbg,
671 per_business_groups_perf pbg1
672 where ppa.payroll_action_id = cp_pactid
673 and ppa.business_group_id = pbg.business_group_id
674 and pbg.legislation_code = pbg1.legislation_code
675 and pbg1.business_group_id = paf.business_group_id
676 and paf.person_id between cp_stperson and cp_endperson;
677 --
678 cursor c_leg_pet (cp_pactid number,
679 cp_stetid number,
680 cp_endetid number
681 ) is
682 select distinct pet.element_type_id
683 from
684 pay_element_types_f pet,
685 pay_payroll_actions ppa,
686 per_business_groups_perf pbg,
687 per_business_groups_perf pbg1
688 where ppa.payroll_action_id = cp_pactid
689 and ppa.business_group_id = pbg.business_group_id
690 and pbg.legislation_code = pbg1.legislation_code
691 and ( pbg1.business_group_id = pet.business_group_id
692 or pet.legislation_code = pbg1.legislation_code
693 )
694 and pet.element_type_id between cp_stetid and cp_endetid;
695 --
696 cursor c_glo_per (
697 cp_stperson number,
698 cp_endperson number
699 ) is
700 select /*+ INDEX(ppf PER_PEOPLE_F_PK)*/
701 distinct ppf.person_id
702 from per_all_people_f ppf
703 where ppf.person_id between cp_stperson and cp_endperson;
704 --
705 cursor c_glo_asg (
706 cp_stperson number,
707 cp_endperson number
708 ) is
709 select distinct paf.assignment_id
710 from
711 per_all_assignments_f paf
712 where paf.person_id between cp_stperson and cp_endperson;
713 --
714 cursor c_glo_pet (
715 cp_stetid number,
716 cp_endetid number
717 ) is
718 select distinct pet.element_type_id
719 from pay_element_types_f pet
720 where pet.element_type_id between cp_stetid and cp_endetid;
721 --
722 l_upg_def_id pay_upgrade_definitions.upgrade_definition_id%type;
723 l_upg_def_nm pay_upgrade_definitions.short_name%type;
724 l_upg_level pay_upgrade_definitions.upgrade_level%type;
725 l_thread_level pay_upgrade_definitions.threading_level%type;
726 l_qual_proc pay_upgrade_definitions.qualifying_procedure%type;
727 l_report_type pay_report_format_mappings_f.report_type%type;
728 l_report_format pay_report_format_mappings_f.report_format%type;
729 l_report_qualifier pay_report_format_mappings_f.report_qualifier%type;
730 l_report_category pay_report_format_mappings_f.report_category%type;
731 l_rep_id pay_report_format_mappings_f.report_format_mapping_id%type;
732 l_range_person boolean default FALSE; -- Variable used to check if RANGE_PERSON_ID is enabled, introduced for bug 8851143
733 --
734 begin
735
736 /* chk if generic report or generic upgrade */
737
738 select rf.report_type, rf.report_format, rf.report_qualifier, rf.report_category, rf.report_format_mapping_id
739 into l_report_type, l_report_format, l_report_qualifier, l_report_category, l_rep_id
740 from pay_report_format_mappings_f rf,
741 pay_payroll_actions pact
742 where pact.payroll_action_id=p_pactid
743 and rf.report_type =pact.report_type
744 and rf.report_qualifier=pact.report_qualifier
745 and rf.report_category=pact.report_category
746 and pact.effective_date between rf.effective_start_date
747 and rf.effective_end_date;
748
749
750 if l_report_type='GENERIC_REPORT'
751 then
752 select 'B',rg.thread_level,
753 rg.qualifying_procedure
754 into l_upg_level,
755 l_thread_level,
756 l_qual_proc
757 from pay_report_groups rg,pay_payroll_actions ppa
758 where rg.report_format_mapping_id=l_rep_id
759 and pay_core_utils.get_parameter('REP_GROUP', ppa.legislative_parameters)=rg.short_name
760 and ppa.payroll_action_id=p_pactid;
761
762
763 else
764 select pay_core_utils.get_parameter('UPG_DEF_NAME',
765 ppa.legislative_parameters)
766 into l_upg_def_nm
767 from pay_payroll_actions ppa
768 where payroll_action_id = p_pactid;
769 --
770 select upgrade_level,
771 threading_level,
772 upgrade_definition_id,
773 qualifying_procedure
774 into l_upg_level,
775 l_thread_level,
776 l_upg_def_id,
777 l_qual_proc
778 from pay_upgrade_definitions
779 where short_name = l_upg_def_nm;
780 end if;
781 --
782 if (l_upg_level = 'B') then
783 if (l_thread_level = 'PER') then
784 for perrec in c_bgp_per(p_pactid, p_stperson, p_endperson) loop
785 --
786 create_object_action(p_object_id => perrec.person_id,
787 p_object_type => 'PER',
788 p_qual_proc => l_qual_proc,
789 p_pactid => p_pactid,
790 p_chunk => p_chunk
791 );
792 --
793 end loop;
794 elsif (l_thread_level = 'ASG') THEN -- Bug 8851133 Implemented RANGE_PERSON_ID
795 l_range_person:=pay_ac_utility.range_person_on(
796 p_report_type => l_report_type
797 ,p_report_format => l_report_format
798 ,p_report_qualifier => l_report_qualifier
799 ,p_report_category => l_report_category);
800 if l_range_person then
801 for asgrec in c_bgp_asg_range(p_pactid, p_chunk) loop
802 --
803 create_object_action(p_object_id => asgrec.assignment_id,
804 p_object_type => 'ASG',
805 p_qual_proc => l_qual_proc,
806 p_pactid => p_pactid,
807 p_chunk => p_chunk
808 );
809 --
810 end loop;
811 else
812 for asgrec in c_bgp_asg(p_pactid, p_stperson, p_endperson) loop
813 --
814 create_object_action(p_object_id => asgrec.assignment_id,
815 p_object_type => 'ASG',
816 p_qual_proc => l_qual_proc,
817 p_pactid => p_pactid,
818 p_chunk => p_chunk
819 );
820 --
821 end loop;
822 end if;
823 elsif (l_thread_level = 'PET') then
824 for etrec in c_bgp_pet(p_pactid, p_stperson, p_endperson) loop
825 --
826 create_object_action(p_object_id => etrec.element_type_id,
827 p_object_type => 'PET',
828 p_qual_proc => l_qual_proc,
829 p_pactid => p_pactid,
830 p_chunk => p_chunk
831 );
832 --
833 end loop;
834 else
835 pay_core_utils.assert_condition(
836 'pay_generic_upgrade.action_creation:1',
837 1 = 2);
838 end if;
839 elsif (l_upg_level = 'L') then
840 if (l_thread_level = 'PER') then
841 for perrec in c_leg_per(p_pactid, p_stperson, p_endperson) loop
842 --
843 create_object_action(p_object_id => perrec.person_id,
844 p_object_type => 'PER',
845 p_qual_proc => l_qual_proc,
846 p_pactid => p_pactid,
847 p_chunk => p_chunk
848 );
849 --
850 end loop;
851 elsif (l_thread_level = 'ASG') then
852 for asgrec in c_leg_asg(p_pactid, p_stperson, p_endperson) loop
853 --
854 create_object_action(p_object_id => asgrec.assignment_id,
855 p_object_type => 'ASG',
856 p_qual_proc => l_qual_proc,
857 p_pactid => p_pactid,
858 p_chunk => p_chunk
859 );
860 --
861 end loop;
862 elsif (l_thread_level = 'PET') then
863 for etrec in c_leg_pet(p_pactid, p_stperson, p_endperson) loop
864 --
865 create_object_action(p_object_id => etrec.element_type_id,
866 p_object_type => 'PET',
867 p_qual_proc => l_qual_proc,
868 p_pactid => p_pactid,
869 p_chunk => p_chunk
870 );
871 --
872 end loop;
873 else
874 pay_core_utils.assert_condition(
875 'pay_generic_upgrade.action_creation:2',
876 1 = 2);
877 end if;
878 elsif (l_upg_level = 'G') then
879 if (l_thread_level = 'PER') then
880 for perrec in c_glo_per(p_stperson, p_endperson) loop
881 --
882 create_object_action(p_object_id => perrec.person_id,
883 p_object_type => 'PER',
884 p_qual_proc => l_qual_proc,
885 p_pactid => p_pactid,
886 p_chunk => p_chunk
887 );
888 --
889 end loop;
890 elsif (l_thread_level = 'ASG') then
891 for asgrec in c_glo_asg(p_stperson, p_endperson) loop
892 --
893 create_object_action(p_object_id => asgrec.assignment_id,
894 p_object_type => 'ASG',
895 p_qual_proc => l_qual_proc,
896 p_pactid => p_pactid,
897 p_chunk => p_chunk
898 );
899 --
900 end loop;
901 elsif (l_thread_level = 'PET') then
902 for etrec in c_glo_pet(p_stperson, p_endperson) loop
903 --
904 create_object_action(p_object_id => etrec.element_type_id,
905 p_object_type => 'PET',
906 p_qual_proc => l_qual_proc,
907 p_pactid => p_pactid,
908 p_chunk => p_chunk
909 );
910 --
911 end loop;
912 else
913 pay_core_utils.assert_condition(
914 'pay_generic_upgrade.action_creation:3',
915 1 = 2);
916 end if;
917 else
918 pay_core_utils.assert_condition(
919 'pay_generic_upgrade.action_creation:4',
920 1 = 2);
921 end if;
922 end action_creation;
923 --
924 /* Name : archinit
925 Purpose : This performs the US specific initialisation section.
926 Arguments :
927 Notes :
928 */
929 procedure archinit(p_payroll_action_id in number) is
930 jurisdiction_code pay_state_rules.jurisdiction_code%TYPE;
931 l_state VARCHAR2(30);
932 begin
933 null;
934 end archinit;
935 --
936
937 /* Name : archive_data
938 Purpose : This performs the US specific employee context setting for the SQWL
939 report.
940 Arguments :
941 Notes :
942 */
943 procedure upgrade_data(p_assactid in number, p_effective_date in date) is
944 --
945 sql_cur number;
946 ignore number;
947 upgrade_proc pay_upgrade_definitions.upgrade_procedure%TYPE;
948 statem varchar2(256);
949 l_upg_def_id pay_upgrade_definitions.upgrade_definition_id%type;
950 l_upg_def_nm pay_upgrade_definitions.short_name%type;
951 object_id pay_temp_object_actions.object_id%type;
952 --
953 begin
954 --
955 select pay_core_utils.get_parameter('UPG_DEF_NAME',
956 ppa.legislative_parameters),
957 ptoa.object_id
958 into l_upg_def_nm,
959 object_id
960 from pay_payroll_actions ppa,
961 pay_temp_object_actions ptoa
962 where ppa.payroll_action_id = ptoa.payroll_action_id
963 and ptoa.object_action_id = p_assactid;
964 --
965 select upgrade_procedure,
966 upgrade_definition_id
967 into upgrade_proc,
968 l_upg_def_id
969 from pay_upgrade_definitions
970 where short_name = l_upg_def_nm;
971 --
972 statem := 'BEGIN '||upgrade_proc||'(:objectid); END;';
973 --
974 hr_utility.trace(statem);
975
976 sql_cur := dbms_sql.open_cursor;
977 dbms_sql.parse(sql_cur,
978 statem,
979 dbms_sql.v7);
980 --
981 dbms_sql.bind_variable(sql_cur, 'objectid', object_id);
982 ignore := dbms_sql.execute(sql_cur);
983 dbms_sql.close_cursor(sql_cur);
984 --
985 end upgrade_data;
986
987 /* Name : deinitialise
988 Purpose : This procedure simply removes all the actions processed
989 in this run
990 Arguments :
991 Notes :
992 */
993 procedure deinitialise (pactid in number)
994 is
995 --
996 l_remove_act varchar2(10);
997 cnt_incomplete_actions number;
998 l_upg_def_id pay_upgrade_definitions.upgrade_definition_id%type;
999 l_upg_def_nm pay_upgrade_definitions.short_name%type;
1000 l_upg_level pay_upgrade_definitions.upgrade_level%type;
1001 l_bus_grp_id pay_payroll_actions.business_group_id%type;
1002 l_leg_code per_business_groups.legislation_code%type;
1003 l_report_type pay_payroll_actions.report_type%type;
1004 --
1005 begin
1006 --
1007
1008 select pay_core_utils.get_parameter('UPG_DEF_NAME',
1009 ppa.legislative_parameters),
1010 pay_core_utils.get_parameter('REMOVE_ACT',
1011 ppa.legislative_parameters),
1012 ppa.business_group_id,
1013 pbg.legislation_code,
1014 ppa.report_type
1015 into l_upg_def_nm,
1016 l_remove_act,
1017 l_bus_grp_id,
1018 l_leg_code,
1019 l_report_type
1020 from pay_payroll_actions ppa,
1021 per_business_groups_perf pbg
1022 where ppa.payroll_action_id = pactid
1023 and pbg.business_group_id = ppa.business_group_id;
1024 --
1025 if l_report_type='GENERIC_REPORT'
1026 then
1027 select count(*)
1028 into cnt_incomplete_actions
1029 from pay_temp_object_actions
1030 where payroll_action_id = pactid
1031 and action_status <> 'C';
1032
1033 if (l_remove_act is null or l_remove_act = 'Y') then
1034 DELETE FROM pay_file_details pfd
1035 WHERE EXISTS (SELECT 1
1036 FROM pay_temp_object_actions ptoa
1037 WHERE ptoa.object_action_id = pfd.source_id
1038 AND pfd.source_type = 'PAA') OR
1039 (pfd.source_id = pactid
1040 AND pfd.source_type = 'PPA') ;
1041 pay_archive.remove_report_actions(pactid);
1042 end if;
1043 else
1044 select upgrade_level,
1045 upgrade_definition_id
1046 into l_upg_level,
1047 l_upg_def_id
1048 from pay_upgrade_definitions
1049 where short_name = l_upg_def_nm;
1050 --
1051 select count(*)
1052 into cnt_incomplete_actions
1053 from pay_temp_object_actions
1054 where payroll_action_id = pactid
1055 and action_status <> 'C';
1056 --
1057 --
1058 if (cnt_incomplete_actions = 0) then
1059 --
1060 set_upgrade_status (l_upg_def_id,
1061 l_upg_level,
1062 l_bus_grp_id,
1063 l_leg_code,
1064 'C');
1065 --
1066 if (l_remove_act is null or l_remove_act = 'Y') then
1067 pay_archive.remove_report_actions(pactid);
1068 end if;
1069 end if;
1070 end if;
1071 --
1072 end deinitialise;
1073 --
1074 --
1075 END pay_generic_upgrade;