[Home] [Help]
PACKAGE BODY: APPS.PER_CANCEL_HIRE_OR_APL_PKG
Source
1 PACKAGE BODY per_cancel_hire_or_apl_pkg AS
2 /* $Header: pecanhir.pkb 120.27.12020000.9 2012/08/27 10:59:19 sclakkar ship $ */
3 --
4 -- Package Variables
5 --
6 g_package VARCHAR2(33) := 'per_cancel_hire_or_apl_pkg.';
7 --
8 -- procedure created for bug 8405711 to get the preivous person type of the person
9 --
10 -- ---------------------------------------------------------------------------
11 -- |------------------------< get_prev_person_type >-------------------------|
12 -- ---------------------------------------------------------------------------
13 --
14 procedure get_prev_person_type(p_business_group_id in number,
15 p_person_id in number,
16 p_effective_date in date,
17 p_current_person_type in varchar2,
18 p_system_person_type out NOCOPY varchar2)
19 is
20
21 --
22 -- Declare Type
23 --
24 type t_person_types is table of per_person_types.system_person_type%type;
25
26 --
27 -- Declare cursor
28 --
29 Cursor csr_person_types is
30 select ppt.system_person_type
31 from per_person_type_usages_f ptu, per_person_types ppt
32 where ptu.person_type_id = ppt.person_type_id
33 and person_id = p_person_id
34 and ppt.business_group_id = p_business_group_id
35 and ppt.system_person_type <> p_current_person_type
36 and p_effective_date between ptu.effective_start_date and ptu.effective_end_date
37 and ppt.system_person_type in ('EMP','EX_EMP','APL','EX_APL','CWK','EX_CWK');
38
39 Cursor csr_prev_person_types is
40 select ppt.system_person_type
41 from per_person_type_usages_f ptu, per_person_types ppt
42 where ptu.person_type_id = ppt.person_type_id
43 and person_id = p_person_id
44 and ppt.business_group_id = p_business_group_id
45 and ppt.system_person_type in ('EMP','EX_EMP','APL','EX_APL','CWK','EX_CWK')
46 and ptu.effective_end_date =
47 (select effective_start_date - 1
48 from per_person_type_usages_f ptu1, per_person_types ppt1
49 where ptu1.person_type_id = ppt1.person_type_id
50 and ptu1.person_id = p_person_id
51 and ppt1.system_person_type = p_current_person_type
52 and p_effective_date between effective_start_date and effective_end_date)
53 order by effective_end_date desc, effective_start_date desc;
54
55 Cursor csr_prev_ppl_person_types is
56 select ppt.system_person_type
57 from per_all_people_f papf, per_person_types ppt
58 where papf.person_type_id = ppt.person_type_id
59 and person_id = p_person_id
60 and ppt.business_group_id = p_business_group_id
61 and ppt.system_person_type in
62 ('EMP',
63 'EX_EMP',
64 'APL',
65 'EX_APL',
66 'EMP_APL',
67 'EX_EMP_APL',
68 'OTHER')
69 and papf.effective_end_date =
70 (select effective_start_date - 1
71 from per_person_type_usages_f ptu1, per_person_types ppt1
72 where ptu1.person_type_id = ppt1.person_type_id
73 and ptu1.person_id = p_person_id
74 and ppt.business_group_id = p_business_group_id
75 and ppt1.system_person_type = p_current_person_type
76 and p_effective_date between effective_start_date and effective_end_date)
77 order by effective_end_date desc, effective_start_date desc;
78 --
79 -- Declare local variables
80 --
81 l_person_types t_person_types;
82 l_proc varchar2(20) :='get_prev_person_type';
83
84 begin
85
86 hr_utility.set_location('Entering :'||g_package||l_proc,10);
87 hr_utility.set_location('P_business_group_id :'||P_business_group_id,11);
88 hr_utility.set_location('p_person_id :'||p_person_id,12);
89 hr_utility.set_location('p_current_person_type :'||p_current_person_type,13);
90 hr_utility.set_location('p_effective_date :'||p_effective_date,14);
91
92 open csr_person_types;
93 fetch csr_person_types bulk collect into l_person_types;
94 if l_person_types.count = 0 then --csr_person_types%notfound then
95 open csr_prev_person_types;
96 fetch csr_prev_person_types into p_system_person_type;
97 close csr_prev_person_types;
98 end if;
99 close csr_person_types;
100
101 hr_utility.set_location('p_system_person_type :'||p_system_person_type,20);
102 if p_system_person_type is null then
103 FOR v_counter IN l_person_types.FIRST .. l_person_types.LAST
104 LOOP
105 if l_person_types(v_counter) = 'EMP' then
106 p_system_person_type:= 'EMP';
107 exit;
108 end if;
109 END LOOP;
110 end if;
111
112 hr_utility.set_location('p_system_person_type :'||p_system_person_type,30);
113 if p_system_person_type is null then
114 FOR v_counter IN l_person_types.FIRST .. l_person_types.LAST
115 LOOP
116 if l_person_types(v_counter) = 'APL' then
117 p_system_person_type:= 'APL';
118 FOR v_counter IN l_person_types.FIRST .. l_person_types.LAST
119 LOOP
120 IF l_person_types(v_counter) = 'EX_EMP' THEN
121 p_system_person_type:= 'EX_EMP_APL';
122 exit;
123 END IF;
124 END LOOP;
125 exit;
126 end if;
127 END LOOP;
128 end if;
129
130 hr_utility.set_location('p_system_person_type :'||p_system_person_type,40);
131 if p_system_person_type is null then
132 FOR v_counter IN l_person_types.FIRST .. l_person_types.LAST
133 LOOP
134 if l_person_types(v_counter) = 'EX_EMP' then
135 p_system_person_type:= 'EX_EMP';
136 FOR v_counter1 IN l_person_types.FIRST .. l_person_types.LAST
137 LOOP
138 IF l_person_types(v_counter1) = 'APL' THEN
139 p_system_person_type:= 'EX_EMP_APL';
140 exit;
141 END IF;
142 END LOOP;
143 exit;
144 end if;
145 END LOOP;
146 end if;
147
148 hr_utility.set_location('p_system_person_type :'||p_system_person_type,50);
149 if p_system_person_type is null then
150 FOR v_counter IN l_person_types.FIRST .. l_person_types.LAST
151 LOOP
152 if l_person_types(v_counter) = 'EX_APL' then
153 p_system_person_type:= 'EX_APL';
154 exit;
155 end if;
156 END LOOP;
157 end if;
158
159 hr_utility.set_location('p_system_person_type :'||p_system_person_type,60);
160 if p_system_person_type is null then
161 open csr_prev_ppl_person_types;
162 fetch csr_prev_ppl_person_types into p_system_person_type;
163 if csr_prev_ppl_person_types%notfound then
164 p_system_person_type := 'OTHER';
165 end if;
166 close csr_prev_ppl_person_types;
167 end if;
168
169 hr_utility.set_location('p_system_person_type :'||p_system_person_type,99);
170 hr_utility.set_location('Leaving :'||l_proc,100);
171
172 end get_prev_person_type;
173 --
174 --
175 --
176 /*===========================================================================*
177 | |
178 | LOCK_CWK_ROWS |
179 | |
180 *============================================================================*/
181 --
182 PROCEDURE lock_cwk_rows
183 (p_person_id IN per_all_people_f.person_id%TYPE
184 ,p_business_group_id IN per_all_people_f.business_group_id%TYPE
185 ,p_effective_date IN DATE) IS
186 --
187 l_rowid VARCHAR2(18);
188 --
189 -- Comments cursor
190 --
191 cursor comments is
192 select h.rowid
193 from hr_comments h
194 , per_assignments_f paf
195 where h.comment_id = paf.comment_id
196 and paf.business_group_id + 0 = p_business_group_id
197 and paf.person_id = p_person_id
198 and paf.effective_start_date >= p_effective_date
199 for update of h.comment_id;
200 --
201 -- payment cursor
202 --
203 cursor payment is
204 select rowid
205 from pay_personal_payment_methods ppm
206 where ppm.business_group_id = p_business_group_id
207 and exists (select 'exists'
208 from per_all_assignments_f paf
209 where paf.business_group_id +0= p_business_group_id
210 and paf.person_id = p_person_id
211 and paf.assignment_id = ppm.assignment_id
212 --start bug 5987416
213 --and ppm.effective_start_date>= p_effective_date
214 --end bug 5987416
215 )
216 and ppm.effective_start_date >= p_effective_date
217 for update of ppm.assignment_id;
218 --
219 -- Budget values cursor
220 --
221 cursor budget_values is
222 select pab.rowid
223 from per_assignment_budget_values_f pab
224 , per_assignments_f paf
225 where pab.business_group_id + 0 = paf.business_group_id + 0
226 and paf.business_group_id + 0 = p_business_group_id
227 and pab.assignment_id = paf.assignment_id
228 and paf.person_id = p_person_id
229 and paf.effective_end_date >=p_effective_date
230 and pab.effective_end_date >=p_effective_date
231 for update of pab.assignment_id;
232 --
233 -- recruiter cursor
234 --
235 cursor recruiter is
236 select rowid
237 from per_assignments_f p
238 where p.recruiter_id = p_person_id
239 and (p.business_group_id = p_business_group_id OR
240 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
241 for update of assignment_id;
242 --
243 -- events cursor
244 --
245 cursor events_or_interviews is
246 select pb.rowid
247 from per_events pe
248 , per_bookings pb
249 where pe.business_group_id = pb.business_group_id
250 and (pb.business_group_id = p_business_group_id OR
251 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
252 and pe.event_id = pb.event_id
253 and pe.event_or_interview in ('I','E')
254 and pb.person_id = p_person_id
255 for update of pb.event_id;
256 --
257 -- vacancies cursor
258 --
259 cursor vacancies is
260 select rowid
261 from per_vacancies pv
262 where (pv.business_group_id = p_business_group_id OR
263 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
264 and pv.recruiter_id = p_person_id
265 and pv.date_from >= p_effective_date
266 for update of pv.vacancy_id;
267 --
268 -- requisitions cursor
269 --
270 cursor requisitions is
271 select rowid
272 from per_requisitions pr
273 where (pr.business_group_id = p_business_group_id OR
274 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
275 and pr.person_id = p_person_id
276 for update of person_id;
277 --
278 cursor supervisor is
279 select rowid
280 from per_assignments_f p
281 where (p.business_group_id = p_business_group_id OR
282 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
283 and p.supervisor_id = p_person_id
284 for update of assignment_id;
285 --
286 cursor per_rows is
287 select ppf.rowid
288 from per_people_f ppf
289 where ppf.person_id = p_person_id
290 for update of ppf.person_id;
291 --
292 --CWK assignments cursor
293 --
294 cursor cwk_ass is
295 select paf.rowid
296 from per_assignments_f paf
297 where paf.business_group_id + 0 = p_business_group_id
298 and paf.person_id = p_person_id
299 and paf.assignment_type = 'C'
300 for update of paf.assignment_id;
301 --
302 -- CWK periofs of placement
303 --
304 cursor csr_periods_of_placement is
305 select pp.rowid
306 from per_periods_of_placement pp
307 where pp.person_id = p_person_id
308 for update of person_id;
309 --
310 cursor csr_assignment_rate_values is
311 select pgr.rowid
312 from pay_grade_rules_f pgr
313 where exists (select 'x'
314 from per_assignments_f paf
315 where pgr.grade_or_spinal_point_id = paf.assignment_id
316 and paf.business_group_id + 0 = p_business_group_id
317 and paf.person_id = p_person_id)
318 for update of pgr.grade_or_spinal_point_id;
319 --
320 cursor csr_grade_steps is
321 select spp.rowid
322 from per_spinal_point_placements_f spp
323 where spp.business_group_id = p_business_group_id and
324 exists (select 'x'
325 from per_assignments_f paf
326 where spp.assignment_id = paf.assignment_id
327 and paf.business_group_id + 0 = p_business_group_id
328 and paf.person_id = p_person_id)
329 for update of spp.assignment_id;
330 --
331 cursor csr_cost_allocations is
332 select pca.rowid
333 from pay_cost_allocations_f pca
334 where exists (select 'x'
335 from per_assignments_f paf
336 where pca.assignment_id = paf.assignment_id
337 and paf.business_group_id + 0 = p_business_group_id
338 and paf.person_id = p_person_id)
339 for update of pca.assignment_id;
340 --
341 BEGIN
342 --
343 open per_rows;
344 <<person>>
345 loop
346 fetch per_rows into l_rowid;
347 exit when per_rows%notfound;
348 end loop person;
349 close per_rows;
350 --
351 open csr_cost_allocations;
352 <<cost_allocations>>
353 loop
354 fetch csr_cost_allocations into l_rowid;
355 exit when csr_cost_allocations%NOTFOUND;
356 end loop cost_allocations;
357 close csr_cost_allocations;
358 --
359 open csr_grade_steps;
360 <<grade_steps>>
361 loop
362 fetch csr_grade_steps into l_rowid;
363 exit when csr_grade_steps%NOTFOUND;
364 end loop grade_steps;
365 close csr_grade_steps;
366 --
367 open csr_periods_of_placement;
368 <<placements>>
369 loop
370 fetch csr_periods_of_placement into l_rowid;
371 exit when csr_periods_of_placement%NOTFOUND;
372 end loop placements;
373 close csr_periods_of_placement;
374 --
375 open csr_assignment_rate_values;
376 <<assignment_rates>>
377 loop
378 fetch csr_assignment_rate_values into l_rowid;
379 exit when csr_assignment_rate_values%NOTFOUND;
380 end loop assignment_rates;
381 --
382 open supervisor;
383 <<super>>
384 loop
385 fetch supervisor into l_rowid;
386 exit when supervisor%NOTFOUND;
387 end loop super;
388 close supervisor;
389 --
390 open recruiter;
391 <<recr>>
392 loop
393 fetch recruiter into l_rowid;
394 exit when recruiter%NOTFOUND;
395 end loop recr;
396 close recruiter;
397 --
398 open events_or_interviews;
399 <<event>>
400 loop
401 fetch events_or_interviews into l_rowid;
402 exit when events_or_interviews%NOTFOUND;
403 end loop event;
404 close events_or_interviews;
405 --
406 open vacancies;
407 <<vacancy>>
408 loop
409 fetch vacancies into l_rowid;
410 exit when vacancies%NOTFOUND;
411 end loop vacancy;
412 close vacancies;
413 --
414 open requisitions;
415 <<req>>
416 loop
417 fetch requisitions into l_rowid;
418 exit when requisitions%NOTFOUND;
419 end loop req;
420 close requisitions;
421 --
422 open budget_values;
423 <<budget_val>>
424 loop
425 fetch budget_values into l_rowid;
426 exit when budget_values%NOTFOUND;
427 end loop budget_val;
428 close budget_values;
429 --
430 open payment;
431 <<paym>>
432 loop
433 fetch payment into l_rowid;
434 exit when payment%NOTFOUND;
435 end loop pay;
436 close payment;
437 --
438 open comments;
439 <<comment>>
440 loop
441 fetch comments into l_rowid;
442 exit when comments%NOTFOUND;
443 end loop comment;
444 close comments;
445 --
446 open cwk_ass;
447 <<placements>>
448 loop
449 fetch cwk_ass into l_rowid;
450 exit when cwk_ass%NOTFOUND;
451 end loop placements;
452 close cwk_ass;
453 --
454 END lock_cwk_rows;
455 --
456 procedure lock_per_rows(p_person_id NUMBER,
457 p_primary_id NUMBER,
458 p_primary_date DATE,
459 p_business_group_id NUMBER,
460 p_person_type VARCHAR2)is
461 l_rowid VARCHAR2(18);
462 l_assignment_id NUMBER;
463 --
464 -- Person cursor
465 --
466 cursor per_rows is
467 select ppf.rowid
468 from per_people_f ppf
469 where ppf.person_id = p_person_id
470 for update of ppf.person_id;
471 --
472 -- Period cursor
473 --
474 cursor period_rows is
475 select pps.rowid
476 from per_periods_of_service pps
477 where pps.person_id = p_person_id
478 for update of pps.person_id;
479 --
480 -- applicant cursor
481 --
482 cursor applicant_rows is
483 select pap.rowid
484 from per_applications pap
485 where pap.person_id = p_person_id
486 for update of pap.person_id;
487 --
488 -- supervisor cursor.
489 --
490 cursor supervisor is
491 select rowid
492 from per_assignments_f p
493 where (p.business_group_id = p_business_group_id OR
494 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
495 and p.supervisor_id = p_person_id
496 for update of assignment_id;
497 --
498 -- recruiter cursor
499 --
500 cursor recruiter is
501 select rowid
502 from per_assignments_f p
503 where p.recruiter_id = p_person_id
504 and (p.business_group_id = p_business_group_id OR
505 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
506 for update of assignment_id;
507 --
508 -- events cursor
509 --
510 cursor events_or_interviews(p_type varchar2) is
511 select pb.rowid
512 from per_events pe
513 , per_bookings pb
514 where pe.business_group_id = pb.business_group_id
515 and (pb.business_group_id = p_business_group_id OR
516 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
517 and pe.event_id = pb.event_id
518 and pe.event_or_interview = p_type
519 and pb.person_id = p_person_id
520 for update of pb.event_id;
521 --
522 -- vacancies cursor
523 --
524 cursor vacancies is
525 select rowid
526 from per_vacancies pv
527 where (pv.business_group_id = p_business_group_id OR
528 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
529 and pv.recruiter_id = p_person_id
530 and pv.date_from >= p_primary_date
531 for update of pv.vacancy_id;
532 --
533 -- requisitions cursor
534 --
535 cursor requisitions is
536 select rowid
537 from per_requisitions pr
538 where (pr.business_group_id = p_business_group_id OR
539 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
540 and pr.person_id = p_person_id
541 for update of person_id;
542 --
543 -- absenses cursor
544 --
545 cursor absenses is
546 select rowid
547 from per_absence_attendances paa
548 where paa.business_group_id +0 = p_business_group_id
549 and paa.person_id = p_person_id
550 and paa.date_start >= p_primary_date
551 for update of person_id;
552 --
553 -- payment cursor
554 --
555 cursor payment is
556 select rowid
557 from pay_personal_payment_methods ppm
558 where ppm.business_group_id = p_business_group_id
559 and exists (select 'exists'
560 from per_all_assignments_f paf
561 where paf.business_group_id +0= p_business_group_id
562 and paf.person_id = p_person_id
563 and paf.assignment_id = ppm.assignment_id
564 and paf.period_of_service_id = p_primary_id
565 --start bug 5987416
566 --and ppm.effective_start_date>= p_primary_date
567 --end bug 5987416
568 )
569 and ppm.effective_start_date >= p_primary_date
570 for update of ppm.assignment_id;
571 --
572 -- Budget values cursor
573 --
574 cursor budget_values is
575 select pab.rowid
576 from per_assignment_budget_values_f pab
577 , per_assignments_f paf
578 where pab.business_group_id + 0 = paf.business_group_id + 0
579 and paf.business_group_id + 0 = p_business_group_id
580 and pab.assignment_id = paf.assignment_id
581 and paf.person_id = p_person_id
582 and paf.effective_end_date >=p_primary_date
583 and pab.effective_end_date >=p_primary_date
584 for update of pab.assignment_id;
585 --
586 -- letters cursor
587 --
588 cursor letters is
589 select p.rowid
590 from per_letter_request_lines p
591 , per_assignments_f paf
592 where p.assignment_id = paf.assignment_id
593 and paf.business_group_id + 0 = p_business_group_id
594 and paf.person_id = p_person_id
595 and paf.application_id = p_primary_id
596 and paf.effective_end_date >=p_primary_date
597 for update of p.assignment_id;
598 --
599 -- Comments cursor
600 --
601 cursor comments is
602 select h.rowid
603 from hr_comments h
604 , per_assignments_f paf
605 where h.comment_id = paf.comment_id
606 and paf.business_group_id + 0 = p_business_group_id
607 and paf.person_id = p_person_id
608 and paf.application_id = p_primary_id
609 and paf.effective_end_date >=p_primary_date
610 for update of h.comment_id;
611 --
612 --appl assignments cursor
613 --
614 cursor appl_ass is
615 select paf.rowid
616 from per_assignments_f paf
617 where paf.business_group_id + 0 = p_business_group_id
618 and paf.person_id = p_person_id
619 and paf.assignment_type = 'A'
620 and paf.application_id = p_primary_id
621 for update of paf.assignment_id;
622 --
623 --emp assignments cursor
624 --
625 cursor emp_ass is
626 select paf.rowid
627 from per_assignments_f paf
628 where paf.business_group_id + 0 = p_business_group_id
629 and paf.person_id = p_person_id
630 and paf.assignment_type = 'E'
631 and paf.application_id = p_primary_id
632 for update of paf.assignment_id;
633
634 begin
635 -- Person loop
636 open per_rows;
637 <<person>>
638 loop
639 fetch per_rows into l_rowid;
640 exit when per_rows%notfound;
641 end loop person;
642 close per_rows;
643 --
644 if p_person_type = 'EMP' then
645 --
646 open period_rows;
647 <<period>>
648 loop
649 fetch period_rows into l_rowid;
650 exit when period_rows%NOTFOUND;
651 end loop period;
652 close period_rows;
653 --
654 open supervisor;
655 <<super>>
656 loop
657 fetch supervisor into l_rowid;
658 exit when supervisor%NOTFOUND;
659 end loop super;
660 close supervisor;
661 --
662 open recruiter;
663 <<recr>>
664 loop
665 fetch recruiter into l_rowid;
666 exit when recruiter%NOTFOUND;
667 end loop recr;
668 close recruiter;
669 --
670 open events_or_interviews('E');
671 <<event>>
672 loop
673 fetch events_or_interviews into l_rowid;
674 exit when events_or_interviews%NOTFOUND;
675 end loop event;
676 close events_or_interviews;
677 --
678 open vacancies;
679 <<vacancy>>
680 loop
681 fetch vacancies into l_rowid;
682 exit when vacancies%NOTFOUND;
683 end loop vacancy;
684 close vacancies;
685 --
686 open requisitions;
687 <<req>>
688 loop
689 fetch requisitions into l_rowid;
690 exit when requisitions%NOTFOUND;
691 end loop req;
692 close requisitions;
693 --
694 open absenses;
695 <<absences>>
696 loop
697 fetch absenses into l_rowid;
698 exit when absenses%NOTFOUND;
699 end loop absences;
700 close absenses;
701 --
702 open budget_values;
703 <<budget_val>>
704 loop
705 fetch budget_values into l_rowid;
706 exit when budget_values%NOTFOUND;
707 end loop budget_val;
708 close budget_values;
709 --
710 open payment;
711 <<paym>>
712 loop
713 fetch payment into l_rowid;
714 exit when payment%NOTFOUND;
715 end loop pay;
716 close payment;
717 --
718 open comments;
719 <<comment>>
720 loop
721 fetch comments into l_rowid;
722 exit when comments%NOTFOUND;
723 end loop comment;
724 close comments;
725 --
726 open emp_ass;
727 <<assignments>>
728 loop
729 fetch emp_ass into l_rowid;
730 exit when emp_ass%NOTFOUND;
731 end loop assignments;
732 close emp_ass;
733 --
734 open appl_ass;
735 <<applications>>
736 loop
737 fetch appl_ass into l_rowid;
738 exit when appl_ass%NOTFOUND;
739 end loop applications;
740 close appl_ass;
741 --
742 elsif p_person_type='APL' then
743 --
744 open applicant_rows;
745 <<appl>>
746 loop
747 fetch applicant_rows into l_rowid;
748 exit when applicant_rows%NOTFOUND;
749 end loop appl;
750 close applicant_rows;
751 -- lock assignment rows etc
752 open appl_ass;
753 <<ass>>
754 loop
755 fetch appl_ass into l_rowid;
756 exit when appl_ass%notfound;
757 end loop ass;
758 close appl_ass;
759 -- lock comments rows
760 open comments;
761 <<comment>>
762 loop
763 fetch comments into l_rowid;
764 exit when comments%NOTFOUND;
765 end loop comment;
766 close comments;
767 -- lock letters
768 open letters;
769 <<letter>>
770 loop
771 fetch letters into l_rowid;
772 exit when letters%NOTFOUND;
773 end loop letter;
774 close letters;
775 -- lock budgets
776 open budget_values;
777 <<budget>>
778 loop
779 fetch budget_values into l_rowid;
780 exit when budget_values%NOTFOUND;
781 end loop budget;
782 close budget_values;
783 -- lock events
784 open events_or_interviews('E');
785 <<event>>
786 loop
787 fetch events_or_interviews into l_rowid;
788 exit when events_or_interviews%NOTFOUND;
789 end loop event;
790 close events_or_interviews;
791 -- lock interview
792 open events_or_interviews('I');
793 <<interview>>
794 loop
795 fetch events_or_interviews into l_rowid;
796 exit when events_or_interviews%NOTFOUND;
797 end loop interview;
798 close events_or_interviews;
799 --
800 end if;
801 end lock_per_rows;
802 --
803 --
804 --
805 procedure pre_cancel_checks(p_person_id NUMBER
806 ,p_where IN OUT NOCOPY VARCHAR2
807 ,p_business_group_id NUMBER
808 ,p_system_person_type VARCHAR2
809 ,p_primary_id NUMBER
810 ,p_primary_date DATE
811 ,p_cancel_type VARCHAR2) is
812 --
813 -- Bug 2964027 starts here.
814 -- Cursor to seelct assignment actions on or after the hire date.
815 --
816 cursor csr_assign_actions_exist is
817 select 'Y'
818 from per_all_assignments_f a
819 where a.person_id = p_person_id
820 --
821 -- 115.51 (START)
822 --
823 AND a.period_of_service_id = p_primary_id
824 --
825 -- 115.51 (END)
826 --
827 AND ((a.effective_start_date = p_primary_date
828 and a.primary_flag <> 'Y'
829 and not exists ( select b.assignment_id
830 from per_all_assignments_f b
831 where nvl(b.effective_end_date,hr_api.g_eot)
832 = (p_primary_date-1)
833 and b.assignment_id = a.assignment_id) )
834 OR a.effective_start_date > p_primary_date );
835 -- Bug 2964027 ends here.
836 --
837 -- Supervisor
838 --
839 cursor supervisor is
840 select rowid
841 from per_assignments_f p
842 where (p.business_group_id = p_business_group_id OR
843 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
844 and p.supervisor_id = p_person_id
845 --start changes for bug 9304366
846 and p.effective_end_date >= p_primary_date
847 and p.assignment_type in ('E','C')
848 and not exists (
849 select 1
850 from per_assignment_status_types past
851 where past.assignment_status_type_id = p.assignment_status_type_id
852 and past.per_system_status = 'TERM_ASSIGN'
853 and (past.business_group_id = p.business_group_id
854 or past.business_group_id is NULL));
855 --end changes for bug 9304366
856 --
857 -- recruiter cursor
858 --
859 cursor recruiter is
860 select rowid
861 from per_assignments_f p
862 where p.recruiter_id = p_person_id
863 and (p.business_group_id = p_business_group_id OR
864 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
865 --start changes for bug 9304366
866 and p.effective_end_date >= p_primary_date
867 and p.assignment_type = 'A';
868 --end changes for bug 9304366;
869 --
870 -- reviews or Events cursor
871 --
872 cursor reviews_or_events(p_type varchar2) is
873 select 'Events exist'
874 from per_events pe
875 , per_bookings pb
876 where pe.business_group_id = pb.business_group_id
877 and (pb.business_group_id = p_business_group_id OR
878 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
879 and pe.event_id = pb.event_id
880 and pe.event_or_interview = p_type
881 and pb.person_id = p_person_id;
882 --
883 -- Interviews cursor
884 --
885 cursor interviews is
886 select 'Interviews exist'
887 from per_events pe
888 where (pe.business_group_id = p_business_group_id OR
889 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
890 and pe.event_or_interview = 'I'
891 and pe.internal_contact_person_id = p_person_id;
892 --
893 -- vacancies cursor
894 --
895 cursor vacancy is
896 select rowid
897 from per_vacancies pv
898 where (pv.business_group_id = p_business_group_id OR
899 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
900 and pv.recruiter_id = p_person_id
901 and pv.date_from >= p_primary_date;
902 --
903 -- requisitions cursor
904 --
905 cursor requisition is
906 select rowid
907 from per_requisitions pr
908 where (pr.business_group_id = p_business_group_id OR
909 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
910 and pr.person_id = p_person_id
911 and pr.date_from >= p_primary_date; -- added for bug 9304366
912 --
913 -- budget_values cursor
914 --
915 cursor budget_values is
916 select rowid
917 from per_assignment_budget_values_f pab
918 where pab.business_group_id = p_business_group_id and
919 exists (select 'budget_values exist'
920 from per_all_assignments_f paf
921 where pab.business_group_id +0= paf.business_group_id + 0
922 and paf.business_group_id +0= p_business_group_id
923 and pab.assignment_id = paf.assignment_id
924 and paf.person_id = p_person_id
925 and paf.period_of_service_id = p_primary_id
926 and paf.effective_end_date >= p_primary_date
927 --start bug 5987416
928 --and pab.effective_end_date >= p_primary_date
929 )
930 AND pab.effective_end_date >= p_primary_date;
931 --end bug 5987416
932 --
933 -- payment cursor
934 --
935 cursor payment is
936 select rowid
937 from pay_personal_payment_methods ppm
938 where ppm.business_group_id = p_business_group_id
939 and exists (select 'exists'
940 from per_all_assignments_f paf
941 where paf.business_group_id +0= p_business_group_id
942 and paf.person_id = p_person_id
943 and paf.assignment_id = ppm.assignment_id
944 and paf.period_of_service_id = p_primary_id
945 --start bug 5987416
946 --and ppm.effective_start_date>= p_primary_date
947 --end bug 5987416
948 )
949 and ppm.effective_start_date >= p_primary_date;
950 --
951 -- pay actions cursor. Start Bug 2841901
952 --
953 cursor csr_payactions is
954 SELECT null
955 FROM pay_payroll_actions pac,
956 pay_assignment_actions act,
957 per_assignments_f asg
958 WHERE asg.person_id = p_person_id
959 --
960 -- 115.51 (START)
961 --
962 AND asg.period_of_service_id = p_primary_id
963 --
964 -- 115.51 (END)
965 --
966 AND act.assignment_id = asg.assignment_id
967 AND pac.payroll_action_id = act.payroll_action_id
968 --
969 --Start Bug 4724223
970 --
971 AND pac.action_type NOT IN ('X','BEE')
972 AND p_primary_date BETWEEN asg.effective_start_date
973 AND asg.effective_end_date
974 --
975 --End Bug 4724223
976 --
977 AND pac.effective_date >= p_primary_date;
978 --
979 --End Bug 2841901
980 --
981 -- Start Fix for the bug#6798826
982 cursor csr_payactions_us is
983 SELECT null
984 FROM pay_payroll_actions pac,
985 pay_assignment_actions act,
986 per_assignments_f asg
987 WHERE asg.person_id = p_person_id
988 AND asg.period_of_service_id = p_primary_id
989 AND act.assignment_id = asg.assignment_id
990 AND pac.payroll_action_id = act.payroll_action_id
991 AND pac.action_type = 'X'
992 AND pac.report_type = 'YREND'
993 AND pac.report_qualifier='FED'
994 AND pac.report_category='RT'
995 AND p_primary_date BETWEEN asg.effective_start_date
996 AND asg.effective_end_date
997 AND pac.effective_date >= p_primary_date;
998
999 -- End of Fix for the bug#6798826
1000
1001 -- Start changes for bug 8405711
1002 Cursor csr_application_change_exists is
1003 select 'exists'
1004 from per_applications pa
1005 where pa.business_group_id = business_group_id
1006 and pa.person_id = p_person_id
1007 and pa.date_received >= p_primary_date
1008 and pa.application_id <> nvl(
1009 (select application_id
1010 from per_applications
1011 where business_group_id = p_business_group_id
1012 and person_id = p_person_id
1013 and p_primary_date - 1 between date_received and nvl(date_end,to_date('31/12/4712','dd/mm/yyyy'))
1014 ),pa.application_id) ;
1015 -- end changes for bug 8405711
1016
1017 -- Start Bug 3285486
1018 CURSOR csr_get_ptu_id(p_system_person_type varchar2) IS
1019 SELECT ptu.person_type_usage_id
1020 FROM per_person_types pt,
1021 per_person_type_usages_f ptu
1022 WHERE pt.business_group_id = p_business_group_id
1023 AND pt.person_type_id = ptu.person_type_id
1024 AND p_primary_date BETWEEN ptu.effective_start_date
1025 AND ptu.effective_end_date
1026 AND ptu.person_id = p_person_id
1027 AND pt.system_person_type = p_system_person_type;
1028
1029 l_person_type_usage_id NUMBER;
1030 -- End bug 3285486
1031
1032 l_dummy VARCHAR2(30);
1033
1034 --
1035 begin
1036 -- Start Bug 3285486
1037 OPEN csr_get_ptu_id(p_system_person_type);
1038 FETCH csr_get_ptu_id INTO l_person_type_usage_id;
1039
1040 IF csr_get_ptu_id%NOTFOUND THEN
1041 --
1042 CLOSE csr_get_ptu_id;
1043 --
1044 -- # 3690364 - changed application_id from 801 to 800
1045 hr_utility.set_message(800,'HR_289548_PEM_EMP_PERSON_ID');
1046 hr_utility.raise_error;
1047 --
1048 END IF;
1049 CLOSE csr_get_ptu_id;
1050 -- end Bug 3285486
1051 if p_cancel_type = 'HIRE' then
1052 if p_where = 'BEGIN' then
1053 --
1054
1055 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',1);
1056 --
1057 -- Start Bug 3285486. commented the call to hr_person.chk_future_person_type
1058 -- added the new call to hr_person_type_usage_info.FutSysPerTypeChgExists
1059 /* if hr_person.chk_future_person_type(p_system_person_type
1060 ,p_person_id
1061 ,p_business_group_id
1062 ,p_primary_date) then*/
1063 IF hr_person_type_usage_info.FutSysPerTypeChgExists
1064 (p_person_type_usage_id => l_person_type_usage_id
1065 ,p_effective_date => p_primary_date
1066 ,p_person_id => p_person_id ) THEN
1067 -- End Bug 3285486.
1068 --
1069 hr_utility.set_message(801,'HR_7078_EMP_ENTER_CANCEL_TYPE');
1070 hr_utility.raise_error;
1071 --
1072 end if;
1073
1074 -- start changes for bug 8405711
1075 -- if whether there exists any future application for this person or not
1076 --
1077 open csr_application_change_exists;
1078 fetch csr_application_change_exists into l_dummy;
1079
1080 if csr_application_change_exists%FOUND then
1081 close csr_application_change_exists;
1082 hr_utility.set_message(800,'PER_449844_APL_ACTIONS_EXISTS');
1083 hr_utility.set_message_token('PERSON_STATUS','hire');
1084 hr_utility.set_message_token('PERSON_TYPE','Employee');
1085 hr_utility.raise_error;
1086 end if;
1087
1088 close csr_application_change_exists;
1089 --
1090 -- end changes for bug 8405711
1091 --
1092 -- BUG 2964027 STARTS HERE.
1093 open csr_assign_actions_exist;
1094 --
1095 fetch csr_assign_actions_exist into l_dummy;
1096 --
1097 if csr_assign_actions_exist%FOUND then
1098 close csr_assign_actions_exist;
1099 hr_utility.set_message(800,'PER_289566_ASG_ACTIONS_EXISTS');
1100 hr_utility.raise_error;
1101 end if;
1102 --
1103 close csr_assign_actions_exist;
1104 -- BUG 2964027 ENDS HERE.
1105 --
1106 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',2);
1107 if not hr_person.chk_prev_person_type(p_system_person_type
1108 ,p_person_id
1109 ,p_business_group_id
1110 ,p_primary_date) then
1111 --
1112 hr_utility.set_message(801,'HR_7077_NO_CANCEL_HIRE');
1113 hr_utility.raise_error;
1114 --
1115 end if;
1116
1117 -- check for pay actions.Start Bug 2841901
1118
1119 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',3);
1120 open csr_payactions;
1121 fetch csr_payactions into l_dummy;
1122 --
1123 if csr_payactions%found then
1124 close csr_payactions;
1125 --
1126 -- Bug# 2989638 Start Here
1127 -- Description : Added new message to display proper error message
1128 --
1129 --
1130 hr_utility.set_message(800,'HR_289529_EMP_FUT_PAY_EXIST');
1131 --
1132 -- Bug# 2989638 End Here
1133 --
1134 hr_utility.raise_error;
1135 end if;
1136 --
1137 close csr_payactions;
1138 -- End Bug 2841901
1139 --
1140 -- check for US pay actions.Start Bug 6798826
1141
1142 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',31);
1143 if
1144 (per_cancel_hire_or_apl_pkg.return_legislation_code(p_person_id)='US') then
1145
1146
1147 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',32);
1148 open csr_payactions_us;
1149 fetch csr_payactions_us into l_dummy;
1150 --
1151 if csr_payactions_us%found then
1152 close csr_payactions_us;
1153 --
1154 hr_utility.set_message(800,'HR_289529_EMP_FUT_PAY_EXIST');
1155
1156 hr_utility.raise_error;
1157 end if;
1158 --
1159 close csr_payactions_us;
1160 end if;
1161
1162 -- End Bug 6798826
1163
1164 p_where := 'SUPERVISOR';
1165 --
1166 end if;
1167 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',4);
1168 if p_where = 'SUPERVISOR' then
1169 --
1170 open supervisor;
1171 --
1172 fetch supervisor into l_dummy;
1173 --
1174 if supervisor%FOUND then
1175 hr_utility.set_message(801,'HR_EMP_IS_SUPER');
1176 close supervisor;
1177 return;
1178 else
1179 close supervisor;
1180 end if;
1181 p_where:= 'RECRUITER';
1182 end if;
1183 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',5);
1184 if p_where = 'RECRUITER' then
1185 --
1186 open recruiter;
1187 --
1188 fetch recruiter into l_dummy;
1189 --
1190 if recruiter%FOUND then
1191 hr_utility.set_message(801,'HR_EMP_IS_RECRUITER');
1192 close recruiter;
1193 return;
1194 else
1195 close recruiter;
1196 end if;
1197 p_where:= 'EVENT';
1198 end if;
1199 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',6);
1200 if p_where = 'EVENT' then
1201 --
1202 open reviews_or_events(p_type =>'E');
1203 --
1204 fetch reviews_or_events into l_dummy;
1205 --
1206 if reviews_or_events%FOUND then
1207 hr_utility.set_message(801,'HR_EMP_HAS_EVENTS');
1208 close reviews_or_events;
1209 return;
1210 else
1211 close reviews_or_events;
1212 end if;
1213 --
1214 p_where := 'INTERVIEW';
1215 end if;
1216 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',7);
1217 if p_where = 'INTERVIEW' then
1218 --
1219 open interviews;
1220 --
1221 fetch interviews into l_dummy;
1222 --
1223 if interviews%FOUND then
1224 hr_utility.set_message(801,'HR_EMP_IS_INTERVIEWER');
1225 close interviews;
1226 return;
1227 else
1228 close interviews;
1229 end if;
1230 p_where := 'REVIEW';
1231 end if;
1232 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',8);
1233 if p_where = 'REVIEW' then
1234 --
1235 open reviews_or_events(p_type =>'I');
1236 --
1237 fetch reviews_or_events into l_dummy;
1238 --
1239 if reviews_or_events%FOUND then
1240 hr_utility.set_message(801,'HR_EMP_DUE_REVIEW');
1241 close reviews_or_events;
1242 return;
1243 else
1244 close reviews_or_events;
1245 end if;
1246 p_where := 'VACANCY';
1247 end if;
1248 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',9);
1249 if p_where = 'VACANCY' then
1250 --
1251 open vacancy;
1252 --
1253 fetch vacancy into l_dummy;
1254 --
1255 if vacancy%FOUND then
1256 hr_utility.set_message(801,'HR_EMP_VAC_RECRUITER');
1257 close vacancy;
1258 return;
1259 else
1260 close vacancy;
1261 end if;
1262 p_where:= 'REQUISITION';
1263 end if;
1264 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',10);
1265 if p_where = 'REQUISITION' then
1266 --
1267 open requisition;
1268 --
1269 fetch requisition into l_dummy;
1270 --
1271 if requisition%FOUND then
1272 hr_utility.set_message(801,'HR_EMP_REQUISITIONS');
1273 close requisition;
1274 return;
1275 else
1276 close requisition;
1277 end if;
1278 p_where:= 'BUDGET_VALUE';
1279 end if;
1280 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',11);
1281 if p_where = 'BUDGET_VALUE' then
1282 --
1283 open budget_values;
1284 --
1285 fetch budget_values into l_dummy;
1286 --
1287 if budget_values%FOUND then
1288 hr_utility.set_message(801,'HR_EMP_BUDGET_VALUES');
1289 close budget_values;
1290 return;
1291 else
1292 close budget_values;
1293 end if;
1294 p_where:= 'PAYMENT';
1295 end if;
1296 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',12);
1297 if p_where = 'PAYMENT' then
1298 --
1299 open payment;
1300 --
1301 fetch payment into l_dummy;
1302 --
1303 if payment%FOUND then
1304 hr_utility.set_message(801,'HR_EMP_PAYMENT_METHODS');
1305 close payment;
1306 return;
1307 else
1308 close payment;
1309 end if;
1310 p_where:= 'END';
1311 else
1312 app_exception.invalid_argument('cancel_hire',
1313 'P_WHERE',p_where);
1314 end if;
1315 --
1316 --
1317 --
1318 elsif p_cancel_type = 'APL' then
1319 --
1320 if p_where = 'BEGIN' then
1321 --
1322 hr_utility.set_location('APP1.B_PRE_DEL_CHECK',1);
1323 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',13);
1324 --
1325 -- Start Bug 3285486. commented the call to hr_person.chk_future_person_type
1326 -- added the new call to hr_person_type_usage_info.FutSysPerTypeChgExists
1327 --
1328 /* if hr_person.chk_future_person_type(p_system_person_type
1329 ,p_person_id
1330 ,p_business_group_id
1331 ,p_primary_date) then*/
1332 IF hr_person_type_usage_info.FutSysPerTypeChgExists
1333 (p_person_type_usage_id => l_person_type_usage_id
1334 ,p_effective_date => p_primary_date
1335 ,p_person_id => p_person_id ) THEN
1336 --
1337 -- End Bug 3285486
1338 --
1339 hr_utility.set_message(800,'HR_7080_ALL_APP_NO_CANCEL');
1340 hr_utility.raise_error;
1341 --
1342 end if;
1343 --
1344 hr_utility.set_location('APP1.B_PRE_DEL_CHECK',2);
1345 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',14);
1346 --
1347 if not hr_person.chk_prev_person_type(p_system_person_type
1348 ,p_person_id
1349 ,p_business_group_id
1350 ,p_primary_date) then
1351 --
1352 hr_utility.set_message(800,'HR_7081_ALL_APP_NO_CANCEL');
1353 hr_utility.raise_error;
1354 --
1355 end if;
1356 --
1357 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',15);
1358
1359 else
1360 app_exception.invalid_argument('cancel_apl',
1361 'P_WHERE',p_where);
1362 end if;
1363 else
1364 app_exception.invalid_argument('cancel_hire_or_apl',
1365 'P_CANCEL_TYPE',p_cancel_type);
1366 end if;
1367 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',16);
1368 exception
1369 when hr_utility.hr_error then
1370 raise;
1371 when others then
1372 hr_utility.oracle_error(sqlcode);
1373 hr_utility.raise_error;
1374 end pre_cancel_checks;
1375 --
1376 --
1377 -- fix 7410493
1378 --
1379 -- ----------------------------------------------------------------------------
1380 -- |-----------------------< cancel_emp_apl_hire >-----------------------|
1381 -- ----------------------------------------------------------------------------
1382 --
1383 procedure cancel_emp_apl_hire
1384 (
1385 p_person_id NUMBER
1386 ,p_date_start DATE
1387 ,p_end_of_time DATE
1388 ,p_business_group_id NUMBEr
1389 ,p_period_of_service_id NUMBER) is
1390
1391
1392 p_assignment_id NUMBER;
1393 l_asg number;
1394 l_person_end_date date;
1395 p_date_start1 date;
1396 p_rowid ROWID;
1397 l_period_of_service_id NUMBER;
1398 l_assignment_id NUMBER;
1399 l_person_type_id NUMBER;
1400 l_asg_status_id irc_assignment_statuses.assignment_status_id%type;
1401 l_asg_status_ovn irc_assignment_statuses.object_version_number%type;
1402 l_asg_status_type_id per_all_assignments_f.assignment_status_type_id%type;
1403 l_dummy varchar2(10);
1404
1405 cursor budget_values1 is
1406 select rowid
1407 from per_assignment_budget_values_f pab
1408 where pab.business_group_id = p_business_group_id and
1409
1410 exists (select 'budget_values exist'
1411 from per_all_assignments_f paf
1412 where pab.business_group_id +0= paf.business_group_id + 0
1413 and paf.business_group_id +0= p_business_group_id
1414 and pab.assignment_id = paf.assignment_id
1415 and paf.person_id = p_person_id
1416 and paf.period_of_service_id = l_period_of_service_id
1417 and paf.effective_end_date >= p_date_start
1418 --for bug 5949102
1419 --and pab.effective_end_date >= p_date_start
1420 )
1421 and pab.effective_end_date >= p_date_start;
1422
1423 -- payment cursor
1424 --
1425 cursor payment1 is
1426 select rowid
1427 from pay_personal_payment_methods ppm
1428 where ppm.business_group_id = p_business_group_id
1429 and exists (select 'exists'
1430 from per_all_assignments_f paf
1431 where paf.business_group_id +0= p_business_group_id
1432 and paf.person_id = p_person_id
1433 and paf.assignment_id = ppm.assignment_id
1434 and paf.period_of_service_id = l_period_of_service_id
1435 --for 5949102
1436 --and ppm.effective_start_date>= p_date_start
1437 )
1438 --end 5949102
1439 and ppm.effective_start_date >= p_date_start;
1440
1441
1442 cursor assignments is
1443
1444 select rowid, assignment_id, assignment_status_type_id
1445 from per_all_assignments_f paf1
1446 where paf1.business_group_id +0 = p_business_group_id
1447 and paf1.person_id = p_person_id
1448 and paf1.assignment_type ='A'
1449 and paf1.effective_end_date = p_date_start -1
1450 and exists ( select '1' from
1451 per_all_assignments_f paf2
1452 where paf2.business_group_id +0= p_business_group_id
1453 and paf2.person_id = p_person_id
1454 and PAF2.assignment_type ='E'
1455 and paf2.effective_start_date = p_date_start
1456 and paf1.assignment_id = paf2.assignment_id
1457 and paf2.period_of_service_id = l_period_of_service_id );
1458
1459
1460 -- Cusror for iRecruitment assignment status
1461 cursor irc_asg_status is
1462 select iass.assignment_status_id, iass.object_version_number
1463 from irc_assignment_statuses iass
1464 where iass.assignment_status_id =
1465 (select max(iass.assignment_status_id)
1466 from irc_assignment_statuses iass
1467 where iass.assignment_id = l_assignment_id
1468 and iass.assignment_status_type_id = l_asg_status_type_id);
1469
1470 -- assignment cursor for entries update
1471 --
1472 --
1473 cursor ass1 is
1474 select assignment_id,effective_start_date
1475 from per_all_assignments_f papf1
1476 where papf1.person_id = p_person_id
1477 and papf1.business_group_id +0 = p_business_group_id
1478 and papf1.period_of_service_id is NULL
1479 and papf1.assignment_type in ('A')
1480 and (p_date_start -1) =( select max(effective_end_date)
1481 from per_all_assignments_f papf2
1482 where papf1.assignment_id =papf2.assignment_id);
1483
1484 cursor ass2 is
1485 select assignment_id,effective_start_date,ROWID
1486 from per_all_assignments_f
1487 where person_id = p_person_id
1488 and business_group_id +0 = p_business_group_id
1489 and period_of_service_id is NULL
1490 and assignment_type in ('A')
1491 and effective_end_date = p_end_of_time;
1492
1493 -- primary assignment update
1494 cursor csr_prm_asg is
1495 select assignment_id
1496 from per_all_assignments_f
1497 where person_id= p_person_id
1498 and business_group_id= p_business_group_id
1499 and assignment_type='E'
1500 and primary_flag='Y'
1501 and effective_end_date= p_date_start -1
1502 and period_of_service_id=l_period_of_service_id;
1503 --
1504 cursor c_assignments is
1505 select paf.assignment_id, paf.effective_start_date
1506 from per_all_assignments_f paf
1507 where paf.person_id = p_person_id
1508 and paf.business_group_id +0 = p_business_group_id
1509 and paf.assignment_type not in ('B','O'); -- issue 3 raised .
1510
1511
1512 -- applications cursor
1513 --
1514 cursor applications is
1515 select rowid
1516 from per_applications pap
1517 where exists (select 'row exists'
1518 from per_all_assignments_f paf
1519 where paf.person_id = p_person_id
1520 and paf.business_group_id +0 = p_business_group_id
1521 and paf.period_of_service_id is NULL
1522 and paf.effective_end_date = p_date_start - 1
1523 and pap.application_id = paf.application_id);
1524 --
1525 -- period cursor
1526 --
1527 --
1528 -- Period cursor
1529 --
1530 cursor period is
1531 select pps.rowid
1532 from per_periods_of_service pps
1533 where pps.person_id = p_person_id
1534 and period_of_service_id = l_period_of_service_id;
1535 --
1536 -- person cursor
1537 --
1538 cursor person is
1539 select p.rowid, effective_end_date from per_people_f p
1540 where p.person_id = p_person_id
1541 and p.effective_start_date >= p_date_start;
1542
1543 -- new_person
1544 --
1545 cursor new_person is
1546 select p.rowid from per_people_f p
1547 where p.person_id = p_person_id
1548 and p.effective_end_date = p_date_start -1;
1549
1550 -- fix for bug 5005157 starts here.
1551 cursor pay_proposals2(p_assignment_id NUMBER) is
1552 select ppp.pay_proposal_id, ppp.object_version_number
1553 from per_pay_proposals ppp
1554 where p_assignment_id = ppp.assignment_id;
1555
1556 cursor csr_get_salary(p_assignment_id NUMBER) is
1557 select element_entry_id
1558 from pay_element_entries_f
1559 where assignment_id = p_assignment_id
1560 and creator_type = 'SP'
1561 and p_date_start between
1562 effective_start_date and effective_end_date;
1563
1564
1565 cursor csr_chk_rec_exists(p_assignment_id NUMBER) is
1566 select element_entry_id
1567 from pay_element_entries_f
1568 where assignment_id = p_assignment_id
1569 and creator_type = 'SP'
1570 and (p_date_start - 1) between
1571 effective_start_date and effective_end_date;
1572
1573
1574 CURSOR csr_get_ptu_id IS
1575 SELECT ptu.person_type_usage_id
1576 FROM per_person_types pt,
1577 per_person_type_usages_f ptu
1578 WHERE pt.business_group_id = p_business_group_id
1579 AND pt.person_type_id = ptu.person_type_id
1580 AND p_date_start BETWEEN ptu.effective_start_date
1581 AND ptu.effective_end_date
1582 AND ptu.person_id = p_person_id
1583 AND pt.system_person_type = 'EX_APL';
1584
1585 -- Cursor to seelct assignment actions on or after the hire date.
1586 --
1587 cursor csr_assign_actions_exist is
1588 select 'Y'
1589 from per_all_assignments_f a
1590 where a.person_id = p_person_id
1591
1592 AND a.period_of_service_id = l_period_of_service_id
1593 AND ((a.effective_start_date = p_date_start
1594 and a.primary_flag <> 'Y'
1595 and not exists ( select b.assignment_id
1596 from per_all_assignments_f b
1597 where nvl(b.effective_end_date,hr_api.g_eot)
1598 = (p_date_start-1)
1599 and b.assignment_id = a.assignment_id) )
1600 OR a.effective_start_date > p_date_start );
1601
1602
1603 cursor csr_payactions is
1604
1605 SELECT null
1606 FROM pay_payroll_actions pac,
1607 pay_assignment_actions act,
1608 per_assignments_f asg
1609 WHERE asg.person_id = p_person_id
1610 AND asg.period_of_service_id = l_period_of_service_id
1611 AND act.assignment_id = asg.assignment_id
1612 AND pac.payroll_action_id = act.payroll_action_id
1613 AND pac.action_type NOT IN ('X','BEE')
1614 AND p_date_start BETWEEN asg.effective_start_date
1615 AND asg.effective_end_date
1616 AND pac.effective_date >= p_date_start;
1617
1618
1619 l_person_type_usage_id NUMBER;
1620 l_element_entry_id1 number;
1621 l_element_entry_id number;
1622 l_pk_id NUMBER;
1623 l_ovn NUMBER;
1624 l_business_group_id NUMBER;
1625 l_sal_warning BOOLEAN;
1626
1627 FUNCTION get_period_of_service (p_person_id IN INTEGER
1628 ,p_start_date IN DATE) RETURN INTEGER is
1629 --
1630 v_dummy INTEGER;
1631 --
1632 begin
1633 select pps.period_of_service_id
1634 into v_dummy
1635 from per_periods_of_service pps
1636 where p_start_date = pps.date_start
1637 and pps.person_id = p_person_id;
1638 --
1639 return v_dummy;
1640 exception
1641 when no_data_found then
1642 hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
1643 hr_utility.raise_error;
1644 end;
1645
1646
1647 begin
1648
1649 hr_utility.set_location('cancel_emp_apl_hire ',10);
1650
1651 -- FIRST PERFROM ALL THE CHECKS TO SEE IF THE CANCEL HIRE PROCESS CAN BE
1652 -- MADE WITH OUT ANY ISSUE.
1653 /*
1654 OPEN csr_get_ptu_id;
1655 FETCH csr_get_ptu_id INTO l_person_type_usage_id;
1656
1657 IF csr_get_ptu_id%NOTFOUND THEN
1658 --
1659 CLOSE csr_get_ptu_id;
1660 --
1661 -- # 3690364 - changed application_id from 801 to 800
1662 hr_utility.set_message(800,'HR_289548_PEM_EMP_PERSON_ID');
1663 hr_utility.raise_error;
1664 --
1665 END IF;
1666 CLOSE csr_get_ptu_id;
1667
1668 hr_utility.set_location('cancel_emp_apl_hire ',11);
1669
1670 IF hr_person_type_usage_info.FutSysPerTypeChgExists
1671 (p_person_type_usage_id => l_person_type_usage_id
1672 ,p_effective_date => p_date_start
1673 ,p_person_id => p_person_id ) THEN
1674 -- End Bug 3285486.
1675 --
1676 hr_utility.set_message(801,'HR_7078_EMP_ENTER_CANCEL_TYPE');
1677 hr_utility.raise_error;
1678 --
1679 end if;
1680 */
1681 if p_period_of_service_id is null then
1682 l_period_of_service_id:= get_period_of_service(p_person_id => p_person_id
1683 ,p_start_date =>p_date_start);
1684 hr_utility.set_location('cancel_emp_apl_hire ',20);
1685 else
1686 l_period_of_service_id := p_period_of_service_id;
1687 hr_utility.set_location('cancel_emp_apl_hire',30);
1688 end if;
1689 hr_utility.set_location('cancel_emp_apl_hire ',40);
1690
1691 hr_utility.set_location('cancel_emp_apl_hire ',12);
1692
1693 open csr_assign_actions_exist;
1694 fetch csr_assign_actions_exist into l_dummy;
1695
1696 if csr_assign_actions_exist%FOUND then
1697 close csr_assign_actions_exist;
1698 hr_utility.set_message(800,'PER_289566_ASG_ACTIONS_EXISTS');
1699 hr_utility.raise_error;
1700 end if;
1701 close csr_assign_actions_exist;
1702
1703 hr_utility.set_location('cancel_emp_apl_hire ',14);
1704
1705 open csr_payactions;
1706 fetch csr_payactions into l_dummy;
1707 --
1708 if csr_payactions%found then
1709 close csr_payactions;
1710 hr_utility.set_message(800,'HR_289529_EMP_FUT_PAY_EXIST');
1711 hr_utility.raise_error;
1712 end if;
1713 --
1714 close csr_payactions;
1715 hr_utility.set_location('cancel_emp_apl_hire ',15);
1716 --
1717 l_business_group_id := p_business_group_id;-- fix for bug 5005157 .
1718
1719
1720 open assignments;
1721 <<assignment>>
1722 loop
1723 -- VT #438579 03/05/79 added assignment_id
1724 hr_utility.set_location('cancel_emp_apl_hire ',50);
1725 fetch assignments into p_rowid,l_assignment_id,l_asg_status_type_id; -- Bug 3564129
1726 exit when assignments%NOTFOUND;
1727
1728 hr_utility.set_location('l_assignment_id '||l_assignment_id,60);
1729
1730 hr_utility.set_location('cancel_emp_apl_hire ',70);
1731
1732 -- VT #438579 03/05/97 added delete
1733
1734 delete from per_spinal_point_placements_f spp
1735 where spp.assignment_id = l_assignment_id;
1736
1737 delete from pay_cost_allocations_f pca
1738 where pca.assignment_id = l_assignment_id;
1739
1740 hr_utility.set_location('cancel_emp_apl_hire ',80);
1741 -- Start of fix 3564129
1742
1743 open irc_asg_status;
1744 fetch irc_asg_status into l_asg_status_id, l_asg_status_ovn;
1745 if irc_asg_status%found then
1746 --
1747 IRC_ASG_STATUS_API.delete_irc_asg_status
1748 (p_assignment_status_id => l_asg_status_id
1749 ,p_object_version_number => l_asg_status_ovn);
1750 --
1751 end if;
1752 close irc_asg_status;
1753 -- fix for bug 5005157 starts here.
1754 hr_utility.set_location('cancel_emp_apl_hire ',90);
1755 open pay_proposals2(l_assignment_id);
1756 <<pay_proposals>>
1757 loop
1758 fetch pay_proposals2 into l_pk_id, l_ovn;
1759 exit when pay_proposals2%NOTFOUND;
1760 hr_maintain_proposal_api.delete_salary_proposal(p_pay_proposal_id => l_pk_id
1761 ,p_business_group_id => l_business_group_id
1762 ,p_object_version_number => l_ovn
1763 ,p_validate => FALSE
1764 ,p_salary_warning => l_sal_warning);
1765 end loop pay_proposals;
1766 close pay_proposals2;
1767 hr_utility.set_location('cancel_emp_apl_hire ',100);
1768 open csr_get_salary(l_assignment_id);
1769 fetch csr_get_salary into l_element_entry_id;
1770 if csr_get_salary%found then
1771 close csr_get_salary;
1772
1773 open csr_chk_rec_exists(l_assignment_id);
1774 fetch csr_chk_rec_exists into l_element_entry_id1;
1775
1776 if csr_chk_rec_exists%found then
1777 close csr_chk_rec_exists;
1778 --
1779 hr_entry_api.delete_element_entry
1780 ('DELETE'
1781 ,p_date_start - 1
1782 ,l_element_entry_id);
1783 else
1784 close csr_chk_rec_exists;
1785 hr_entry_api.delete_element_entry
1786 ('ZAP'
1787 ,p_date_start
1788 ,l_element_entry_id);
1789 end if;
1790 else
1791 close csr_get_salary;
1792 end if;
1793
1794
1795 hr_utility.set_location('cancel_emp_apl_hire ',110);
1796
1797 delete from per_all_assignments_f paf
1798 where paf.assignment_id=l_assignment_id
1799 and paf.person_id=p_person_id
1800 and paf.effective_start_date=p_date_start;
1801
1802 hr_utility.set_location('cancel_emp_apl_hire ',120);
1803
1804
1805 update per_all_assignments_f
1806 set effective_end_date = p_end_of_time
1807 where assignment_id=l_assignment_id
1808 and person_id=p_person_id
1809 and effective_end_date= p_date_start -1;
1810
1811 hr_utility.set_location('cancel_emp_apl_hire ',121);
1812 hr_utility.set_location('l_assignment_id :'||l_assignment_id,121);
1813
1814 if per_otherbg_apl_api.isMultiRegVac(l_assignment_id) then
1815 per_otherbg_apl_api.open_otherbg_applications(l_assignment_id,p_date_start,'CANCEL_HIRE');
1816 end if;
1817
1818
1819 end loop assignment;
1820 close assignments;
1821
1822 hr_utility.set_location('cancel_emp_apl_hire ',130);
1823
1824 open applications;
1825 <<application>>
1826 loop
1827
1828 hr_utility.set_location('cancel_emp_apl_hire ',140);
1829 fetch applications into p_rowid;
1830
1831 exit when applications%NOTFOUND;
1832
1833 update per_applications pap
1834 set pap.date_end = NULL
1835 where pap.rowid = p_rowid;
1836
1837 if sql%notfound then
1838 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1839 hr_utility.set_message_token('PROCEDURE','cancel_hire_or_apl');
1840 hr_utility.set_message_token('STEP',11);
1841 hr_utility.raise_error;
1842 end if;
1843 end loop application;
1844 close applications;
1845
1846 hr_utility.set_location('cancel_emp_apl_hire ',150);
1847
1848 open ass2;
1849 <<entries>>
1850 loop
1851 hr_utility.set_location('cancel_emp_apl_hire',280);
1852 fetch ass2 into p_assignment_id,p_date_start1,p_rowid;
1853 exit when ass2%NOTFOUND;
1854 hr_utility.set_location('cancel_emp_apl_hire ',160);
1855 --
1856 hrentmnt.maintain_entries_asg(p_assignment_id
1857 ,p_business_group_id
1858 ,'CNCL_HIRE'
1859 ,NULL
1860 ,NULL
1861 ,NULL
1862 ,'ZAP'
1863 ,p_date_start
1864 ,p_end_of_time);
1865 --
1866 end loop entries;
1867 close ass2;
1868 --
1869 open ass1;
1870
1871 loop
1872 hr_utility.set_location('cancel_emp_apl_hire ',280);
1873 fetch ass1 into p_assignment_id,p_date_start1;
1874 exit when ass1%NOTFOUND;
1875 hr_utility.set_location('p_assignment_id '||p_assignment_id,160);
1876 hr_utility.set_location('p_date_start1 '||p_date_start1,160);
1877
1878 update per_all_assignments_f
1879 set effective_end_date = p_end_of_time
1880 where assignment_id=p_assignment_id
1881 and person_id=p_person_id
1882 and effective_end_date= p_date_start -1;
1883 --
1884 --
1885 if per_otherbg_apl_api.isMultiRegVac(p_assignment_id) then
1886 per_otherbg_apl_api.open_otherbg_applications(p_assignment_id,p_date_start,'CANCEL_HIRE');
1887 end if;
1888
1889 end loop;
1890 close ass1;
1891 --
1892 hr_utility.set_location('cancel_hire_or_apl ',250);
1893 -- this cursor is to delete the asg changes from hire date onwards
1894 -- and will update the primary flag to Y so that the asg data will
1895 -- retain as how it was..
1896
1897 open csr_prm_asg;
1898 fetch csr_prm_asg into l_asg;
1899 close csr_prm_asg;
1900
1901 delete from per_all_assignments_f
1902 where person_id= p_person_id
1903 and assignment_id = l_asg
1904 and effective_start_date > = p_date_start;
1905
1906 update per_all_assignments_f
1907 set effective_end_date = p_end_of_time
1908
1909 WHERE person_id= p_person_id
1910 and assignment_id = l_asg
1911 AND effective_end_date = p_date_start -1
1912 and primary_flag='Y';
1913
1914 open person;
1915 <<per>>
1916 loop
1917 hr_utility.set_location('cancel_emp_apl_hire ',300);
1918 fetch person into p_rowid, l_person_end_date;
1919 exit when person%notfound;
1920
1921 delete from per_people_f
1922 where rowid = p_rowid;
1923 --
1924 end loop person;
1925 close person;
1926
1927 hr_utility.set_location('cancel_emp_apl_hire ',350);
1928
1929
1930 open new_person;
1931 <<new_per>>
1932 loop
1933 hr_utility.set_location('cancel_emp_apl_hire ',370);
1934 fetch new_person into p_rowid;
1935 exit when new_person%notfound;
1936
1937
1938 hr_utility.set_location('cancel_emp_apl_hire ',400);
1939
1940 update per_people_f
1941 set effective_end_date = p_end_of_time
1942 where rowid = p_rowid;
1943
1944 hr_utility.set_location('cancel_emp_apl_hire ',450);
1945 end loop new_person;
1946 close new_person;
1947
1948 hr_utility.set_location('cancel_emp_apl_hire ',500);
1949
1950 -- this is to cancel the person type so that we can revert
1951 -- back to previous person type i.e emp-apl
1952 OPEN csr_get_ptu_id;
1953 FETCH csr_get_ptu_id INTO l_person_type_usage_id;
1954
1955 IF csr_get_ptu_id%FOUND THEN
1956 --
1957 hr_utility.set_location('cancel_emp_apl_hire', 510);
1958 CLOSE csr_get_ptu_id;
1959
1960 hr_per_type_usage_internal.cancel_emp_apl_ptu
1961 (p_effective_date => p_date_start
1962 ,p_person_id => p_person_id
1963 ,p_system_person_type => 'EX_APL');
1964
1965 else
1966
1967 CLOSE csr_get_ptu_id;
1968 end if;
1969
1970 hr_utility.set_location('cancel_emp_apl_hire', 521);
1971
1972 hr_per_type_usage_internal.cancel_emp_apl_ptu
1973 (p_effective_date => p_date_start
1974 ,p_person_id => p_person_id
1975 ,p_system_person_type => 'EMP');
1976
1977 hr_utility.set_location('cancel_emp_apl_hire : END ' ,550);
1978 --
1979
1980 update per_applications pap
1981 set pap.date_end = NULL
1982 where application_id in ( select distinct (application_id)
1983 from per_all_assignments_f
1984 where person_id= p_person_id
1985 and assignment_type = 'A'
1986 and sysdate between effective_start_date and effective_end_date
1987 );
1988
1989 per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);
1990 --
1991 for asg_rec in c_assignments loop
1992 --
1993 hr_utility.set_location('cancel_emp_apl_hire', 551);
1994 --
1995 hr_security_internal.add_to_person_list(
1996 p_effective_date => asg_rec.effective_start_date
1997 ,p_assignment_id => asg_rec.assignment_id);
1998 --
1999 hr_utility.set_location('cancel_emp_apl_hire', 552);
2000 --
2001 end loop;
2002
2003 end cancel_emp_apl_hire;
2004
2005 --
2006 -- fix 7410493
2007 /*===========================================================================*
2008 | |
2009 | upd_person_type_usage_end_date |
2010 | |
2011 *============================================================================*/
2012 /*Procedure to update the end date person type OTHER
2013 when cancel placement is done.Added for the bug 6460093*/
2014
2015 procedure upd_person_type_usage_end_date
2016 (
2017 p_effective_date in date
2018 ,p_person_id in number
2019 ,p_system_person_type in varchar2
2020
2021
2022 ) is
2023
2024 cursor csr_upded_person_type_usages
2025 (
2026 p_effective_date in date
2027 ,p_person_id in number
2028 ,p_system_person_type in varchar2
2029 ) is
2030 select ptu.person_type_usage_id
2031 ,ptu.object_version_number
2032 from per_person_type_usages_f ptu
2033 where p_effective_date between ptu.effective_start_date and ptu.effective_end_date
2034 and ptu.person_id = p_person_id
2035 and ptu.person_type_id in
2036 (select ppt.person_type_id
2037 from per_person_types ppt
2038 where (( p_system_person_type = 'OTHER'
2039 and ppt.system_person_type = 'OTHER' )));
2040
2041 l_csr_upd_per_type_usages csr_upded_person_type_usages%rowtype;
2042
2043 l_effective_end_date date := hr_general.end_of_time;
2044 begin
2045
2046 hr_utility.set_location('Entering Upd_Person_Type_Usage_End_Date',491);
2047
2048 open csr_upded_person_type_usages(
2049 p_person_id => p_person_id,
2050 p_effective_date => p_effective_date,
2051 p_system_person_type => p_system_person_type);
2052
2053 fetch csr_upded_person_type_usages into l_csr_upd_per_type_usages;
2054 if csr_upded_person_type_usages%found then
2055
2056 hr_utility.set_location('Entering Upd_Person_Type_Usage_End_Date',492);
2057
2058 update per_person_type_usages_f ptu
2059 set effective_end_date = l_effective_end_date
2060 where ptu.effective_end_date = p_effective_date
2061 and ptu.person_id = p_person_id
2062 and ptu.person_type_usage_id = l_csr_upd_per_type_usages.person_type_usage_id
2063 and ptu.object_version_number = l_csr_upd_per_type_usages.object_version_number;
2064
2065 end if;
2066 close csr_upded_person_type_usages;
2067
2068 hr_utility.set_location('Leaving Upd_Person_Type_Usage_End_Date',493);
2069 end upd_person_type_usage_end_date;
2070
2071 /*Procedure Added for the bug 6460093*/
2072
2073
2074 procedure do_cancel_hire(p_person_id NUMBER
2075 ,p_date_start DATE
2076 ,p_end_of_time DATE
2077 ,p_business_group_id NUMBER
2078 ,p_period_of_service_id NUMBER) is
2079 --
2080 p_assignment_id NUMBER;
2081 p_start_date DATE;
2082 p_rowid ROWID;
2083 l_period_of_service_id NUMBER;
2084 -- VT #438579 03/05/97
2085 l_assignment_id NUMBER;
2086 l_back2back BOOLEAN;
2087 l_person_type_id NUMBER;
2088 -- Start of fix 3564129
2089 l_asg_status_id irc_assignment_statuses.assignment_status_id%type;
2090 l_asg_status_ovn irc_assignment_statuses.object_version_number%type;
2091 l_asg_status_type_id per_all_assignments_f.assignment_status_type_id%type;
2092 -- End of fix 3564129
2093 --
2094
2095 l_system_person_type per_person_types.system_person_type%type; --added for bug 8405711
2096 --
2097 -- supervisor cursor.
2098 --
2099 cursor supervisor1 is
2100 select rowid
2101 from per_assignments_f p
2102 where (p.business_group_id = p_business_group_id OR
2103 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
2104 and p.supervisor_id = p_person_id
2105 --start changes for bug 9304366
2106 and p.effective_end_date >= p_date_start
2107 and p.assignment_type in ('E','C')
2108 and not exists (
2109 select 1
2110 from per_assignment_status_types past
2111 where past.assignment_status_type_id = p.assignment_status_type_id
2112 and past.per_system_status = 'TERM_ASSIGN'
2113 and (past.business_group_id = p.business_group_id
2114 or past.business_group_id is NULL));
2115 --end changes for bug 9304366
2116 --
2117 -- recruiter cursor
2118 --
2119 cursor recruiter1 is
2120 select rowid
2121 from per_assignments_f p
2122 where p.recruiter_id = p_person_id
2123 and (p.business_group_id = p_business_group_id OR
2124 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
2125 --start changes for bug 9304366
2126 and p.effective_end_date >= p_date_start
2127 and p.assignment_type = 'A';
2128 --end changes for bug 9304366
2129 --
2130 -- events cursor
2131 --
2132 cursor events is
2133 select rowid
2134 from per_bookings pb
2135 where (pb.business_group_id = p_business_group_id OR
2136 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
2137 and pb.person_id = p_person_id
2138 and exists ( select 'row exists'
2139 from per_events pe
2140 where pe.business_group_id + 0 = p_business_group_id
2141 and pe.event_id = pb.event_id
2142 and pe.event_or_interview in ('I','E')
2143 and pe.date_start >= p_date_start);
2144 --
2145 -- vacancies cursor
2146 --
2147 cursor vacancies is
2148 select rowid
2149 from per_vacancies pv
2150 where (pv.business_group_id = p_business_group_id OR
2151 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
2152 and pv.recruiter_id = p_person_id
2153 and pv.date_from >= p_date_start;
2154 --
2155 -- requisitions cursor
2156 --
2157 cursor requisitions1 is
2158 select rowid
2159 from per_requisitions pr
2160 where (pr.business_group_id = p_business_group_id OR
2161 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
2162 and pr.person_id = p_person_id
2163 and pr.date_from >= p_date_start; -- added for bug 9304366
2164 --
2165 -- absenses cursor
2166 --
2167 cursor absenses1 is
2168 select rowid
2169 from per_absence_attendances paa
2170 where paa.business_group_id +0 = p_business_group_id
2171 and paa.person_id = p_person_id
2172 and paa.date_start >= p_date_start;
2173 --
2174 -- budget_values cursor
2175 --
2176 cursor budget_values1 is
2177 select rowid
2178 from per_assignment_budget_values_f pab
2179 where pab.business_group_id = p_business_group_id and
2180 exists (select 'budget_values exist'
2181 from per_all_assignments_f paf
2182 where pab.business_group_id +0= paf.business_group_id + 0
2183 and paf.business_group_id +0= p_business_group_id
2184 and pab.assignment_id = paf.assignment_id
2185 and paf.person_id = p_person_id
2186 and paf.period_of_service_id = l_period_of_service_id
2187 and paf.effective_end_date >= p_date_start
2188 --start bug 5987416
2189 --and pab.effective_end_date >= p_date_start
2190 )
2191 and pab.effective_end_date >= p_date_start;
2192 --end bug 5987416
2193 --
2194 -- payment cursor
2195 --
2196 cursor payment1 is
2197 select rowid
2198 from pay_personal_payment_methods ppm
2199 where ppm.business_group_id = p_business_group_id
2200 and exists (select 'exists'
2201 from per_all_assignments_f paf
2202 where paf.business_group_id +0= p_business_group_id
2203 and paf.person_id = p_person_id
2204 and paf.assignment_id = ppm.assignment_id
2205 and paf.period_of_service_id = l_period_of_service_id
2206 --start bug 5987416
2207 -- and ppm.effective_start_date>= p_date_start
2208 --end bug 5987416
2209 )
2210 and ppm.effective_start_date >= p_date_start;
2211 --
2212 -- comments cursor
2213 --
2214 cursor comments1 is
2215 select rowid
2216 from hr_comments h
2217 where exists (select 'comments exist'
2218 from per_all_assignments_f paf
2219 where h.comment_id = paf.comment_id
2220 and paf.business_group_id +0= p_business_group_id
2221 and paf.person_id = p_person_id
2222 and paf.period_of_service_id = l_period_of_service_id);
2223 --
2224 -- assignments cursor
2225 --
2226 -- VT #438579 03/05/79 added assignment_id
2227 cursor assignments is
2228 select rowid, assignment_id, assignment_status_type_id -- Bug 3564129
2229 from per_all_assignments_f paf
2230 where paf.business_group_id +0= p_business_group_id
2231 and paf.person_id = p_person_id
2232 and paf.period_of_service_id = l_period_of_service_id;
2233 --
2234 -- Start of fix 3564129
2235 -- Cusror for iRecruitment assignment status
2236 cursor irc_asg_status is
2237 select iass.assignment_status_id, iass.object_version_number
2238 from irc_assignment_statuses iass
2239 where iass.assignment_status_id =
2240 (select max(iass.assignment_status_id)
2241 from irc_assignment_statuses iass
2242 where iass.assignment_id = l_assignment_id
2243 and iass.assignment_status_type_id = l_asg_status_type_id);
2244 -- End of fix 3564129
2245 --
2246 -- assignment cursor for entries update
2247 --
2248 --Added the assignment type condition of 'A' for fix of #3390818
2249 --
2250 cursor ass1 is
2251 select assignment_id,effective_start_date,ROWID
2252 from per_all_assignments_f
2253 where person_id = p_person_id
2254 and business_group_id +0 = p_business_group_id
2255 and period_of_service_id is NULL
2256 and assignment_type in ('E','A') -- 3194314
2257 and effective_end_date = p_date_start - 1
2258 for update of effective_end_date;
2259 --
2260 --
2261 cursor csr_alus is -- fix for the bug 7578210
2262 select assignment_id,effective_start_date,ROWID
2263 from per_all_assignments_f
2264 where person_id = p_person_id
2265 and business_group_id +0 = p_business_group_id
2266 and period_of_service_id = l_period_of_service_id
2267 and assignment_type in ('E');
2268 --
2269 --
2270 cursor c_assignments is
2271 select paf.assignment_id, paf.effective_start_date
2272 from per_all_assignments_f paf
2273 where paf.person_id = p_person_id
2274 and paf.business_group_id +0 = p_business_group_id
2275 and assignment_type not in ('B','O'); --modified for bug #6449599 and bug # 7572514
2276 --
2277 -- applications cursor
2278 --
2279 cursor applications is
2280 select rowid
2281 from per_applications pap
2282 where exists (select 'row exists'
2283 from per_all_assignments_f paf
2284 where paf.person_id = p_person_id
2285 and paf.business_group_id +0 = p_business_group_id
2286 and paf.period_of_service_id is NULL
2287 and paf.effective_end_date = p_date_start - 1
2288 and pap.application_id = paf.application_id);
2289 --
2290 -- period cursor
2291 --
2292 --
2293 -- Period cursor
2294 --
2295 cursor period is
2296 select pps.rowid
2297 from per_periods_of_service pps
2298 where pps.person_id = p_person_id
2299 and period_of_service_id = l_period_of_service_id;
2300 --
2301 -- person cursor
2302 --
2303 cursor person is
2304 select p.rowid, effective_end_date from per_people_f p
2305 where p.person_id = p_person_id
2306 and p.effective_start_date >= p_date_start;
2307 --
2308 -- 3194314
2309 --
2310 cursor csr_emp_ptu_id is
2311 select ptu.person_type_id
2312 from per_person_type_usages_f ptu
2313 ,per_person_types ppt
2314 where ptu.person_id = p_person_id
2315 and ptu.effective_start_date = p_date_start
2316 and ptu.person_type_id = ppt.person_type_id
2317 and ppt.system_person_type = 'EMP';
2318
2319 l_emp_ptu_id number;
2320
2321 --
2322 --3848352 start
2323 --
2324 cursor csr_apl_ptu_id is
2325 select ptu.person_type_id
2326 from per_person_type_usages_f ptu
2327 ,per_person_types ppt
2328 where ptu.person_id = p_person_id
2329 and ptu.effective_end_date = p_date_start - 1
2330 and ptu.person_type_id = ppt.person_type_id
2331 and ppt.system_person_type = 'APL';
2332
2333 l_apl_ptu_id number;
2334 l_dummy number;
2335 l_apl_flag varchar2(1);
2336 --
2337 --3848352 end
2338 --
2339 --
2340 cursor csr_is_cwk(cp_date_start date) is -- 3194314
2341 select pp.period_of_placement_id, pp.date_start
2342 from per_periods_of_placement pp
2343 where pp.person_id = p_person_id
2344 and pp.actual_termination_date = cp_date_start - 1;
2345
2346 l_pp_id number;
2347 l_cwk_date_start date;
2348
2349 -- <<
2350 l_person_end_date date; --#1998140
2351 --
2352 -- new_person
2353 --
2354 cursor new_person is
2355 select p.rowid from per_people_f p
2356 where p.person_id = p_person_id
2357 and p.effective_end_date = p_date_start -1;
2358
2359 -- fix for bug 5005157 starts here.
2360 cursor pay_proposals2(p_assignment_id NUMBER) is
2361 select ppp.pay_proposal_id, ppp.object_version_number
2362 from per_pay_proposals ppp
2363 where p_assignment_id = ppp.assignment_id;
2364
2365 cursor csr_get_salary(p_assignment_id NUMBER) is
2366 select element_entry_id
2367 from pay_element_entries_f
2368 where assignment_id = p_assignment_id
2369 and creator_type = 'SP'
2370 and p_start_date between
2371 effective_start_date and effective_end_date;
2372
2373
2374 cursor csr_chk_rec_exists(p_assignment_id NUMBER) is
2375 select element_entry_id
2376 from pay_element_entries_f
2377 where assignment_id = p_assignment_id
2378 and creator_type = 'SP'
2379 and (p_start_date - 1) between
2380 effective_start_date and effective_end_date;
2381
2382
2383 l_element_entry_id1 number;
2384 l_element_entry_id number;
2385 l_pk_id NUMBER;
2386 l_ovn NUMBER;
2387 l_business_group_id NUMBER;
2388 l_sal_warning BOOLEAN;
2389 -- fix for bug 5005157 ends here.
2390
2391
2392 --
2393 FUNCTION get_period_of_service (p_person_id IN INTEGER
2394 ,p_start_date IN DATE) RETURN INTEGER is
2395 --
2396 v_dummy INTEGER;
2397 --
2398 begin
2399 select pps.period_of_service_id
2400 into v_dummy
2401 from per_periods_of_service pps
2402 where p_start_date = pps.date_start
2403 and pps.person_id = p_person_id;
2404 --
2405 return v_dummy;
2406 exception
2407 when no_data_found then
2408 hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
2409 hr_utility.raise_error;
2410 end;
2411 begin
2412 --
2413 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',10);
2414 --
2415 l_business_group_id := p_business_group_id;-- fix for bug 5005157 .
2416 if p_period_of_service_id is null then
2417 l_period_of_service_id:= get_period_of_service(p_person_id => p_person_id
2418 ,p_start_date =>p_date_start);
2419 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',20);
2420 else
2421 l_period_of_service_id := p_period_of_service_id;
2422 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',30);
2423 end if;
2424 open supervisor1;
2425 <<supervisor>>
2426 loop
2427 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',40);
2428 fetch supervisor1 into p_rowid;
2429 exit when supervisor1%NOTFOUND;
2430 --
2431 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',50);
2432 update per_all_assignments_f paf
2433 set paf.supervisor_id = NULL
2434 where paf.rowid = p_rowid;
2435 --
2436 if sql%notfound then
2437 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2438 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2439 hr_utility.set_message_token('STEP',1);
2440 hr_utility.raise_error;
2441 end if;
2442 end loop supervisor;
2443 close supervisor1;
2444 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',60);
2445 open recruiter1;
2446 <<recruiter>>
2447 loop
2448 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',70);
2449 fetch recruiter1 into p_rowid;
2450 exit when recruiter1%NOTFOUND;
2451 --
2452 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',80);
2453 update per_all_assignments_f paf
2454 set paf.recruiter_id = NULL
2455 where paf.rowid = p_rowid;
2456 --
2457 if sql%notfound then
2458 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2459 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2460 hr_utility.set_message_token('STEP',2);
2461 hr_utility.raise_error;
2462 end if;
2463 end loop recruiter;
2464 close recruiter1;
2465 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',90);
2466 open events;
2467 <<event>>
2468 loop
2469 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',100);
2470 fetch events into p_rowid;
2471 exit when events%NOTFOUND;
2472 delete from per_bookings pb
2473 where pb.rowid = p_rowid;
2474 --
2475 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',110);
2476 if sql%notfound then
2477 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2478 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2479 hr_utility.set_message_token('STEP',3);
2480 hr_utility.raise_error;
2481 end if;
2482 end loop event;
2483 close events;
2484 --
2485 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',120);
2486 --
2487 open vacancies;
2488 <<vacancy>>
2489 loop
2490 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',130);
2491 fetch vacancies into p_rowid;
2492 exit when vacancies%NOTFOUND;
2493 --
2494 update per_all_vacancies pv
2495 set pv.recruiter_id = NULL
2496 where pv.rowid = p_rowid;
2497 if sql%notfound then
2498 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2499 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2500 hr_utility.set_message_token('STEP',4);
2501 hr_utility.raise_error;
2502 end if;
2503 end loop vacancy;
2504 close vacancies;
2505 --
2506 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',140);
2507 open requisitions1;
2508 <<requisition>>
2509 loop
2510 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',150);
2511 fetch requisitions1 into p_rowid;
2512 exit when requisitions1%NOTFOUND;
2513 update per_requisitions pr
2514 set pr.person_id = NULL
2515 where pr.rowid = p_rowid;
2516 if sql%notfound then
2517 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2518 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2519 hr_utility.set_message_token('STEP',5);
2520 hr_utility.raise_error;
2521 end if;
2522 end loop requisition;
2523 close requisitions1;
2524 --
2525 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',160);
2526 open absenses1;
2527 <<absence>>
2528 loop
2529 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',170);
2530 fetch absenses1 into p_rowid;
2531 exit when absenses1%NOTFOUND;
2532 delete from per_absence_attendances paa
2533 where paa.rowid = p_rowid;
2534 if sql%notfound then
2535 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2536 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2537 hr_utility.set_message_token('STEP',6);
2538 hr_utility.raise_error;
2539 end if;
2540 end loop absense;
2541 close absenses1;
2542 --
2543 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',180);
2544 open budget_values1;
2545 <<budget_value>>
2546 loop
2547 fetch budget_values1 into p_rowid;
2548 exit when budget_values1%NOTFOUND;
2549 delete from per_assignment_budget_values_f pab
2550 where pab.rowid = p_rowid;
2551 if sql%notfound then
2552 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2553 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2554 hr_utility.set_message_token('STEP',7);
2555 hr_utility.raise_error;
2556 end if;
2557 end loop budget_value;
2558 close budget_values1;
2559 --
2560 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',190);
2561 open payment1;
2562 <<pay>>
2563 loop
2564 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',200);
2565 fetch payment1 into p_rowid;
2566 exit when payment1%NOTFOUND;
2567 delete from pay_personal_payment_methods ppm
2568 where ppm.rowid = p_rowid;
2569 if sql%notfound then
2570 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2571 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2572 hr_utility.set_message_token('STEP',8);
2573 hr_utility.raise_error;
2574 end if;
2575 end loop pay;
2576 close payment1;
2577 --
2578 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',210);
2579 open comments1;
2580 <<comment>>
2581 loop
2582 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',220);
2583 fetch comments1 into p_rowid;
2584 exit when comments1%NOTFOUND;
2585 delete from hr_comments h
2586 where h.rowid = p_rowid;
2587 if sql%notfound then
2588 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2589 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2590 hr_utility.set_message_token('STEP',9);
2591 hr_utility.raise_error;
2592 end if;
2593 end loop comment;
2594 close comments1;
2595 --
2596 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',230);
2597 -- delete the alus before performing the following operation on the
2598 -- asg data so that we dont lose the child (element entries ) records
2599 -- fix for the bug 7415677
2600 open csr_alus;
2601
2602 loop
2603 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',282);
2604 fetch csr_alus into p_assignment_id,p_start_date,p_rowid;
2605 exit when csr_alus%NOTFOUND;
2606 hr_utility.set_location('p_assignment_id ' ||p_assignment_id,280);
2607 hr_utility.set_location('p_start_date ' ||p_start_date,280);
2608 hrentmnt.maintain_entries_asg(p_assignment_id
2609 ,p_business_group_id
2610 ,'CNCL_HIRE'
2611 ,NULL
2612 ,NULL
2613 ,NULL
2614 ,'ZAP'
2615 ,p_start_date
2616 ,p_end_of_time);
2617 --
2618 end loop;
2619 close csr_alus;
2620
2621 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',283);
2622 --
2623 -- fix for the bug 7578210 .
2624 --
2625 open assignments;
2626 <<assignment>>
2627 loop
2628 -- VT #438579 03/05/79 added assignment_id
2629 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',240);
2630 fetch assignments into p_rowid,l_assignment_id,l_asg_status_type_id; -- Bug 3564129
2631 exit when assignments%NOTFOUND;
2632 -- VT #438579 03/05/97 added delete
2633 delete from per_spinal_point_placements_f spp
2634 where spp.assignment_id = l_assignment_id;
2635 delete from pay_cost_allocations_f pca
2636 where pca.assignment_id = l_assignment_id;
2637 -- Start of fix 3564129
2638 open irc_asg_status;
2639 fetch irc_asg_status into l_asg_status_id, l_asg_status_ovn;
2640 if irc_asg_status%found then
2641 --
2642 IRC_ASG_STATUS_API.delete_irc_asg_status
2643 (p_assignment_status_id => l_asg_status_id
2644 ,p_object_version_number => l_asg_status_ovn);
2645 --
2646 end if;
2647 close irc_asg_status;
2648 -- fix for bug 5005157 starts here.
2649 open pay_proposals2(l_assignment_id);
2650 <<pay_proposals>>
2651 loop
2652 fetch pay_proposals2 into l_pk_id, l_ovn;
2653 exit when pay_proposals2%NOTFOUND;
2654 hr_maintain_proposal_api.delete_salary_proposal(p_pay_proposal_id => l_pk_id
2655 ,p_business_group_id => l_business_group_id
2656 ,p_object_version_number => l_ovn
2657 ,p_validate => FALSE
2658 ,p_salary_warning => l_sal_warning);
2659 end loop pay_proposals;
2660 close pay_proposals2;
2661 open csr_get_salary(l_assignment_id);
2662 fetch csr_get_salary into l_element_entry_id;
2663 if csr_get_salary%found then
2664 close csr_get_salary;
2665
2666 open csr_chk_rec_exists(l_assignment_id);
2667 fetch csr_chk_rec_exists into l_element_entry_id1;
2668
2669 if csr_chk_rec_exists%found then
2670 close csr_chk_rec_exists;
2671 --
2672 hr_entry_api.delete_element_entry
2673 ('DELETE'
2674 ,p_start_date - 1
2675 ,l_element_entry_id);
2676 else
2677 close csr_chk_rec_exists;
2678 hr_entry_api.delete_element_entry
2679 ('ZAP'
2680 ,p_start_date
2681 ,l_element_entry_id);
2682 end if;
2683 else
2684 close csr_get_salary;
2685 end if;
2686 -- fix for bug 5005157 ends here.
2687
2688
2689 -- End of fix 3564129
2690 delete from per_all_assignments_f paf
2691 where paf.rowid = p_rowid;
2692 if sql%notfound then
2693 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2694 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2695 hr_utility.set_message_token('STEP',10);
2696 hr_utility.raise_error;
2697 end if;
2698 end loop assignment;
2699 close assignments;
2700 --
2701 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',250);
2702 open applications;
2703 <<application>>
2704 loop
2705 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',260);
2706 fetch applications into p_rowid;
2707 exit when applications%NOTFOUND;
2708 update per_applications pap
2709 set pap.date_end = NULL
2710 where pap.rowid = p_rowid;
2711 if sql%notfound then
2712 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2713 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2714 hr_utility.set_message_token('STEP',11);
2715 hr_utility.raise_error;
2716 end if;
2717 end loop application;
2718 close applications;
2719 --
2720 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',270);
2721 open ass1;
2722 <<entries>>
2723 loop
2724 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',280);
2725 fetch ass1 into p_assignment_id,p_start_date,p_rowid;
2726 exit when ass1%NOTFOUND;
2727 update per_all_assignments_f paf
2728 set paf.effective_end_date = p_end_of_time
2729 where paf.rowid = p_rowid;
2730
2731
2732 if per_otherbg_apl_api.isMultiRegVac(p_assignment_id) then
2733 per_otherbg_apl_api.open_otherbg_applications(p_assignment_id,p_start_date,'CANCEL_HIRE');
2734 end if;
2735
2736 --
2737 hrentmnt.maintain_entries_asg(p_assignment_id
2738 ,p_business_group_id
2739 ,'CNCL_HIRE'
2740 ,NULL
2741 ,NULL
2742 ,NULL
2743 ,'ZAP'
2744 ,p_start_date
2745 ,p_end_of_time);
2746 --
2747 end loop entries;
2748 close ass1;
2749 --
2750 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',290);
2751 l_back2back := per_periods_of_service_pkg_v2.IsBackToBackContract
2752 ( p_person_id => p_person_id, p_hire_date_of_current_pds => p_date_start);
2753
2754 open period;
2755 <<service>>
2756 loop
2757 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',300);
2758 fetch period into p_rowid;
2759 exit when period%notfound;
2760 delete from per_periods_of_service
2761 where rowid = p_rowid;
2762 if sql%notfound then
2763 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2764 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2765 hr_utility.set_message_token('STEP',11);
2766 hr_utility.raise_error;
2767 end if;
2768 end loop service;
2769 if period%rowcount <1 then
2770 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2771 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2772 hr_utility.set_message_token('STEP',12);
2773 hr_utility.raise_error;
2774 end if;
2775 close period;
2776 --
2777 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',310);
2778 -- 3194314
2779 open csr_is_cwk(p_date_start);
2780 fetch csr_is_cwk into l_pp_id, l_cwk_date_start;
2781 close csr_is_cwk;
2782 hr_utility.trace('CWK pp_id = '||l_pp_id);
2783 --
2784 -- 3848352 start
2785 l_apl_flag := NULL;
2786 open csr_apl_ptu_id;
2787 fetch csr_apl_ptu_id into l_dummy;
2788 if csr_apl_ptu_id%found then
2789 l_apl_flag := 'Y';
2790 end if;
2791 close csr_apl_ptu_id;
2792 hr_utility.trace('l_apl_flag = '||l_apl_flag);
2793 -- 3848352 end
2794
2795 -- <<
2796 open person;
2797 <<per>>
2798 loop
2799 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',320);
2800 fetch person into p_rowid, l_person_end_date;
2801 exit when person%notfound;
2802 if l_back2back
2803 then
2804 if l_person_end_date = hr_general.end_of_time then --#1998140
2805
2806 select person_type_id into l_person_type_id
2807 from per_person_types
2808 where business_group_id = p_business_group_id
2809 and system_person_type = 'EX_EMP'
2810 and default_flag = 'Y';
2811
2812 -- code added for the bug 8504597 starts here
2813
2814 if l_apl_flag = 'Y' then
2815 select person_type_id into l_person_type_id
2816 from per_person_types
2817 where business_group_id = p_business_group_id
2818 and system_person_type = 'EX_EMP_APL'
2819 and default_flag = 'Y';
2820 end if;
2821 -- only in per_all_people_f the person tye has to be ex_emp_apl
2822 -- code added for the bug 8504597 ends here
2823
2824 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',322);
2825
2826 update per_people_f
2827 set person_type_id = l_person_type_id,
2828 effective_start_date = p_date_start, -- in case DT updates exist
2829 current_employee_flag = null,
2830 current_emp_or_apl_flag = l_apl_flag, -- 3848352 --null,
2831 current_applicant_flag = l_apl_flag -- 3848352 --null
2832 where rowid = p_rowid;
2833
2834 -- code added for the bug 8504597 starts here
2835 -- reverting the person type to EX_EMP
2836 -- to be used in per_person_type_usages_f
2837 -- as in person_type_usages_f only ex_emp record has to be created
2838 -- as the applicant record is already existing
2839 -- if ths is not done then will hit the error
2840 -- PER_PERSON_TYPE_USAGES_F_FK2
2841 -- from per_ptu_bus.chk_person_type_id
2842
2843 select person_type_id
2844 into l_person_type_id
2845 from per_person_types
2846 where business_group_id = p_business_group_id
2847 and system_person_type = 'EX_EMP'
2848 and default_flag = 'Y';
2849
2850 -- code added for the bug 8504597 ends here
2851 --
2852
2853 else -- #1998140
2854 --
2855 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',323);
2856
2857 delete from per_people_f
2858 where rowid = p_rowid;
2859 --
2860 end if;
2861
2862 elsif l_pp_id is not null then -- 3194314
2863
2864 if l_person_end_date = hr_general.end_of_time then --#1998140
2865
2866 -- 3848352 start
2867 if hr_person_type_usage_info.is_person_of_type
2868 ( p_effective_date => p_date_start
2869 ,p_person_id => p_person_id
2870 ,p_system_person_type => 'EX_APL'
2871 )
2872 and l_apl_flag is not null then
2873 select person_type_id into l_person_type_id
2874 from per_person_types
2875 where business_group_id = p_business_group_id
2876 and system_person_type = 'APL'
2877 and default_flag = 'Y';
2878 ELSE
2879 -- start changes for bug 8405711
2880 get_prev_person_type(p_business_group_id,
2881 p_person_id => p_person_id,
2882 p_effective_date => p_date_start,
2883 p_current_person_type => 'EMP',
2884 p_system_person_type => l_system_person_type);
2885
2886 l_person_type_id := hr_person_type_usage_info.get_default_person_type_id
2887 (p_business_group_id, l_system_person_type);
2888 /*select person_type_id into l_person_type_id
2889 from per_person_types
2890 where business_group_id = p_business_group_id
2891 and system_person_type = 'OTHER' -- EX_CWK but this is not maintained
2892 and default_flag = 'Y'; */
2893 -- end changes for bug 8405711
2894 end if;
2895 -- 3848352 end
2896
2897 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',324);
2898
2899 --
2900 -- 3847884 Changed from p_start_date to p_date_start
2901 --
2902 update per_people_f
2903 set person_type_id = l_person_type_id,
2904 current_employee_flag = null,
2905 current_emp_or_apl_flag = l_apl_flag, -- 3848352 -- null,
2906 current_applicant_flag = l_apl_flag, -- 3848352 --null,
2907 per_information7 = null,
2908 employee_number = null,
2909 start_date = l_cwk_date_start,
2910 effective_start_date = p_date_start, -- p_start_date, -- in case DT updates exist
2911 original_date_of_hire = null
2912 where rowid = p_rowid;
2913 --
2914 else -- #1998140
2915 --
2916 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',325);
2917
2918 delete from per_people_f
2919 where rowid = p_rowid;
2920 --
2921 end if;
2922
2923 -- <<
2924 else
2925 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',330);
2926 delete from per_people_f
2927 where rowid = p_rowid;
2928 end if;
2929 --
2930 -- hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',340); -- for bug 14523191
2931 -- if sql%ROWCOUNT <1 then
2932 if sql%notfound then
2933 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2934 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2935 hr_utility.set_message_token('STEP',13);
2936 hr_utility.raise_error;
2937 end if;
2938 end loop per;
2939 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
2940 if person%rowcount <1 then
2941 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2942 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2943 hr_utility.set_message_token('STEP',14);
2944 hr_utility.raise_error;
2945 end if;
2946 close person;
2947 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',360);
2948 -- 3194314
2949 -- this should not get exected when b2b cwk/emp
2950 if l_pp_id is null then
2951 open new_person;
2952 <<new_per>>
2953 loop
2954 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',370);
2955 fetch new_person into p_rowid;
2956 exit when new_person%notfound;
2957 if NOT l_back2back
2958 then
2959 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',380);
2960 update per_people_f
2961 set effective_end_date = p_end_of_time
2962 where rowid = p_rowid;
2963 end if;
2964 --
2965 -- hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',390); -- for bug 14523191
2966 if sql%ROWCOUNT <1 then
2967 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2968 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2969 hr_utility.set_message_token('STEP',15);
2970 hr_utility.raise_error;
2971 end if;
2972 end loop new_per;
2973 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',400);
2974 if new_person%rowcount <1 then
2975 --Fix for bug 9371186 starts here
2976
2977 -- hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2978 -- hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
2979 -- hr_utility.set_message_token('STEP',16);
2980
2981 hr_utility.set_message(800,'PER_449888_NO_PRE_PER_TYPE');
2982 --Fix for bug 9371186 ends here
2983 hr_utility.raise_error;
2984 end if;
2985 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',410);
2986 close new_person;
2987
2988 end if; -- 3194314 is b2b?
2989 --
2990 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',420);
2991 -- 115.1 Change by M Bocutt
2992 --
2993 -- If we have an EMP PTU record that started on the hire date
2994 -- then delete it.
2995 --
2996 -- PTU : Code added
2997
2998 hr_utility.set_location('cancel_hire_or_apl.p_date_start = '||to_char(p_date_start,'DD/MM/YYYY'),420);
2999 hr_utility.set_location('cancel_hire_or_apl.p_person_id = '||to_char(p_person_id),420);
3000
3001 if l_pp_id is not null then -- 3194314
3002
3003 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',424);
3004
3005 open csr_emp_ptu_id;
3006 fetch csr_emp_ptu_id into l_emp_ptu_id;
3007 if csr_emp_ptu_id%FOUND and l_emp_ptu_id is not null then
3008
3009 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',425);
3010 --fix for bug 6671352 starts here.
3011 /*hr_per_type_usage_internal.maintain_person_type_usage
3012 (p_effective_date => p_date_start
3013 ,p_person_id => p_person_id
3014 ,p_person_type_id => l_emp_ptu_id
3015 ,p_datetrack_delete_mode => 'ZAP'
3016 );*/
3017 hr_per_type_usage_internal.cancel_person_type_usage
3018 (p_effective_date => p_date_start
3019 ,p_person_id => p_person_id
3020 ,p_system_person_type => 'EMP');
3021
3022 end if;
3023 --fix for bug 6671352 ends here.
3024 close csr_emp_ptu_id;
3025 --
3026 --3848352 start
3027 --
3028 open csr_apl_ptu_id;
3029 fetch csr_apl_ptu_id into l_apl_ptu_id;
3030 if csr_apl_ptu_id%FOUND and l_apl_ptu_id is not null then
3031
3032 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',425);
3033 hr_per_type_usage_internal.maintain_person_type_usage
3034 (p_effective_date => p_date_start -1
3035 ,p_person_id => p_person_id
3036 ,p_person_type_id => l_apl_ptu_id
3037 ,p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE'
3038 );
3039 end if;
3040 close csr_apl_ptu_id;
3041 --
3042 -- 3848352 end
3043 --
3044 else
3045
3046 hr_per_type_usage_internal.cancel_person_type_usage
3047 (p_effective_date => p_date_start
3048 ,p_person_id => p_person_id
3049 ,p_system_person_type => 'EMP');
3050
3051 -- Added for the bug 6899072 starts here
3052 -- This finds out any system person type of OTHER records
3053 -- which is end dated while creating a employment
3054 -- and updates the end date to end of time while
3055 -- cancelling the hire
3056
3057 upd_person_type_usage_end_date
3058 (p_effective_date => p_date_start-1
3059 ,p_person_id => p_person_id
3060 ,p_system_person_type => 'OTHER');
3061
3062 -- Change for the bug 6899072 ends here
3063
3064 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',430);
3065 --
3066 --5450847 start : Need to maintain APL record if any. Delete the EX_APL
3067 -- record and update EED of APL record to EOTime.
3068 --
3069 open csr_apl_ptu_id;
3070 fetch csr_apl_ptu_id into l_apl_ptu_id;
3071 if csr_apl_ptu_id%FOUND and l_apl_ptu_id is not null then
3072
3073 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',432);
3074 hr_per_type_usage_internal.maintain_person_type_usage
3075 (p_effective_date => p_date_start -1
3076 ,p_person_id => p_person_id
3077 ,p_person_type_id => l_apl_ptu_id
3078 ,p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE'
3079 );
3080 end if;
3081 close csr_apl_ptu_id;
3082 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',433);
3083 --
3084 -- 5450847 end
3085 --
3086
3087 end if;
3088
3089 if l_back2back then
3090
3091 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',435);
3092
3093 hr_per_type_usage_internal.maintain_person_type_usage
3094 (p_effective_date => p_date_start
3095 ,p_person_id => p_person_id
3096 ,p_person_type_id => l_person_type_id
3097 );
3098
3099 end if;
3100 -- PTU : End of changes
3101
3102 -- hr_per_type_usage_internal.maintain_ptu(
3103 -- p_validate => false,
3104 -- p_person_id => p_person_id,
3105 -- p_action => 'CANCEL HIRE',
3106 -- p_period_of_service_id => p_period_of_service_id,
3107 -- p_actual_termination_date => NULL,
3108 -- p_business_group_id => p_business_group_id,
3109 -- p_date_start => p_date_start,
3110 -- p_leaving_reason => null,
3111 -- p_old_date_start => null,
3112 -- p_old_leaving_reason => null);
3113 --
3114 per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);
3115 --
3116 for asg_rec in c_assignments loop
3117 --
3118 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire', 501);
3119 --
3120 hr_security_internal.add_to_person_list(
3121 p_effective_date => asg_rec.effective_start_date
3122 ,p_assignment_id => asg_rec.assignment_id);
3123 --
3124 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire', 502);
3125 --
3126 end loop;
3127 --
3128 end do_cancel_hire;
3129 --
3130 procedure do_cancel_appl(p_person_id NUMBER
3131 ,p_date_received DATE
3132 ,p_end_of_time DATE
3133 ,p_business_group_id NUMBER
3134 ,p_application_id NUMBER) is
3135 --
3136 -- applicant assignment cursor
3137 -- determine the assignment_id for all the assignments of the application
3138 --
3139 cursor appl_asg is
3140 select distinct assignment_id
3141 from per_assignments_f
3142 where application_id = p_application_id;
3143 --
3144 -- events cursor -- This is only really finding applicant interviews
3145 -- since in all other cases pe.assignment_id is null
3146 --
3147 cursor events_or_interviews(p_assignment_id NUMBER) is
3148 select pe.event_id
3149 from per_events pe
3150 where pe.date_start >= p_date_received
3151 and p_assignment_id = pe.assignment_id;
3152 --
3153 -- bookings cursor
3154 --
3155 cursor bookings2 is
3156 select pb.booking_id
3157 from per_events pe,
3158 per_bookings pb
3159 where pb.event_id = pe.event_id
3160 and pe.date_start >= p_date_received
3161 and pb.person_id = p_person_id
3162 and pe.emp_or_apl = 'A';
3163 --
3164 -- Budget values cursor
3165 --
3166 cursor budget_values2(p_assignment_id NUMBER) is
3167 select pab.rowid
3168 from per_assignment_budget_values_f pab
3169 where pab.assignment_id = p_assignment_id;
3170 --
3171 -- letters cursor
3172 --
3173 cursor letters2(p_assignment_id NUMBER) is
3174 select p.letter_request_line_id
3175 from per_letter_request_lines p
3176 where p.assignment_id = p_assignment_id;
3177 --
3178 -- Comments cursor
3179 --
3180 cursor comments2(p_assignment_id NUMBER) is
3181 select h.comment_id
3182 from hr_comments h
3183 , per_assignments_f paf
3184 where h.comment_id = paf.comment_id
3185 and paf.assignment_id = p_assignment_id;
3186 --
3187 -- assignments cursor
3188 --
3189 cursor assignments is
3190 select paf.rowid
3191 from per_assignments_f paf
3192 where paf.person_id = p_person_id
3193 and paf.application_id = p_application_id;
3194 --
3195 -- applications cursor
3196 --
3197 cursor applications is
3198 select rowid
3199 from per_applications pap
3200 where pap.date_received = p_date_received
3201 and pap.person_id = p_person_id;
3202 --
3203 -- person cursor
3204 --
3205 cursor people is
3206 select rowid
3207 from per_people_f
3208 where person_id = p_person_id
3209 and effective_start_date >= p_date_received;
3210 --
3211 -- new person cursor
3212 --
3213 cursor new_people is
3214 select rowid
3215 from per_people_f
3216 where person_id = p_person_id
3217 and effective_end_date = p_date_received - 1;
3218 --
3219 --
3220 -- NEW CURSORS FOR CANCEL_APPL BEGIN adhunter feb-2001
3221 --
3222 -- PTU changes: this cursor not required.
3223 -- person type usages cursor
3224 --
3225 --cursor person_type_usages2 is
3226 --select ptu.person_type_usage_id, ptu.object_version_number
3227 --from per_person_type_usages_f ptu, per_person_types ppt
3228 --where person_id = p_person_id
3229 --and ptu.person_type_id = ppt.person_type_id
3230 --and ppt.system_person_type = 'APL'
3231 --and ptu.effective_start_date = p_date_received;
3232 --
3233 -- secondary assignment statuses cursor
3234 --
3235 cursor sec_asg_statuses2(p_assignment_id NUMBER) is
3236 select sas.rowid
3237 from per_secondary_ass_statuses sas
3238 where sas.assignment_id = p_assignment_id;
3239 --
3240 -- assignment extra info cursor
3241 --
3242 cursor asg_extra_info2(p_assignment_id NUMBER) is
3243 select paei.assignment_extra_info_id, paei.object_version_number
3244 from per_assignment_extra_info paei
3245 where p_assignment_id = paei.assignment_id;
3246 --
3247 -- pay proposals cursor
3248 --
3249 cursor pay_proposals2(p_assignment_id NUMBER) is
3250 select ppp.pay_proposal_id, ppp.object_version_number
3251 from per_pay_proposals ppp
3252 where p_assignment_id = ppp.assignment_id;
3253 --
3254 -- work incidents cursor
3255 --
3256 cursor work_incidents2(p_assignment_id NUMBER) is
3257 select pwi.incident_id, pwi.object_version_number
3258 from per_work_incidents pwi
3259 where p_assignment_id = pwi.assignment_id;
3260 --
3261 -- asg proposal answers cursor
3262 --
3263 cursor asg_prop_answer2(p_assignment_id NUMBER) is
3264 select papa.rowid
3265 from per_assign_proposal_answers papa
3266 where p_assignment_id = papa.assignment_id;
3267 --
3268 -- disabilities cursor
3269 --
3270 -- FIX 1977389 - commented out
3271 -- cursor disabilities2(p_assignment_id NUMBER) is
3272 -- select pdf.disability_id, pdf.object_version_number
3273 -- from per_disabilities_f pdf
3274 -- where p_assignment_id = pdf.assignment_id
3275 -- order by pdf.effective_start_date desc;
3276 -- END FIX.
3277 --
3278 -- person comments when apl cursor
3279 --
3280 cursor person_apl_comments2 is
3281 select distinct h.comment_id --fix for bug 7157204.
3282 from hr_comments h, per_people_f ppf
3283 where ppf.business_group_id = p_business_group_id
3284 and ppf.person_id = p_person_id
3285 and ppf.effective_start_date >= p_date_received
3286 and ppf.comment_id = h.comment_id;
3287 --
3288 -- Assignments from previous
3289 --
3290 -- NEW CURSORS FOR CANCEL_APPL
3291 -- Bug 8405711: Cursor return the effective_start_date if the action performs
3292 -- on the day where a record is already present.
3293 cursor chk_action_perform_date is
3294 select effective_start_date
3295 from per_people_f
3296 where person_id = p_person_id
3297 and effective_start_date = hr_general.effective_date;
3298
3299 -- Bug 8405711: Cursor get the future records along with end-dated record
3300 -- on the previous day, in the case if the action performs on future date.
3301 cursor people_b2b_with_prev_record is
3302 select rowid,effective_end_date,effective_start_date,current_applicant_flag
3303 from per_people_f
3304 where person_id = p_person_id
3305 and (effective_start_date > p_date_received
3306 or effective_end_date = hr_general.effective_date - 1);
3307
3308 --
3309 -- NEW CURSORS FOR CANCEL_APPL END
3310 --
3311 -- Bug 4095559: Cursor return whether B2B of EX_EMP/EX_CWK.APL
3312 cursor csr_b2b_apl is
3313 select count(ptu.person_type_id)
3314 from per_person_type_usages_f ptu
3315 ,per_person_types ppt
3316 where ptu.person_id = p_person_id
3317 and ptu.effective_start_date = p_date_received
3318 and ptu.person_type_id = ppt.person_type_id
3319 and ppt.system_person_type <> 'APL';
3320
3321 -- Bug 4095559: Cursor to update per_people_f B2B of EX_EMP/EX_CWK.APL
3322 -- new_people_b2b for person records with ESD as date_received
3323 cursor new_people_b2b is
3324 select rowid,effective_end_date,effective_start_date,current_applicant_flag
3325 from per_people_f
3326 where person_id = p_person_id
3327 and effective_start_date = p_date_received;
3328
3329 -- Bug 4095559: Cursor to update per_people_f B2B of EX_EMP/EX_CWK.APL
3330 -- people_b2b for person records with ESD future to date_received
3331 cursor people_b2b is
3332 select rowid,effective_end_date,effective_start_date,current_applicant_flag
3333 from per_people_f
3334 where person_id = p_person_id
3335 and effective_start_date > p_date_received;
3336
3337 -- Bug 4095559: Cursor to get the latest person_type_id B2B of
3338 -- EX_EMP/EX_CWK.APL
3339 cursor latest_ptid is
3340 select person_type_id
3341 from per_person_type_usages_f
3342 where person_id = p_person_id
3343 order by EFFECTIVE_END_DATE desc, EFFECTIVE_START_DATE desc;
3344 --
3345 -- Cursor to re-evaluate the security.
3346 cursor csr_asg_sec is
3347 select paf.assignment_id, paf.effective_start_date
3348 from per_all_assignments_f paf
3349 where paf.person_id = p_person_id
3350 and paf.assignment_type NOT IN ('B','O'); -- Modified for bug # 9151913
3351 -- and paf.assignment_type <> 'B'; -- Added For Bug # 6630290
3352 --
3353
3354 -- start changes for bug 8405711
3355 -- cursor to evaluate whether the person became B2B Apl on the hire day
3356 cursor chk_prv_APL_exists is
3357 select person_type_id
3358 from per_person_type_usages_f ptu
3359 where ptu.person_id = p_person_id
3360 and ptu.effective_end_date = p_date_received - 1
3361 and exists (
3362 select ppt.person_type_id
3363 from per_person_types ppt
3364 where ppt.system_person_type = 'APL'
3365 and ppt.business_group_id = p_business_group_id
3366 and ppt.person_type_id = ptu.person_type_id)
3367 and exists (select ppt1.person_type_id
3368 from per_person_types ppt1, per_person_type_usages_f ptu1
3369 where ptu1.person_type_id = ppt1.person_type_id
3370 and ppt1.business_group_id = p_business_group_id
3371 and ptu1.person_id = p_person_id
3372 and ppt1.system_person_type in ('EMP','CWK')
3373 and ptu1.effective_start_date = p_date_received);
3374
3375 l_prv_person_type_id number;
3376 -- end changes for bug 8405711
3377
3378 --
3379 -- Changes start for the bug 14156610
3380
3381 CURSOR csr_pre_apl_num IS
3382 SELECT applicant_number
3383 FROM per_all_people_f ppf
3384 WHERE ppf.effective_start_date < p_date_received
3385 AND ppf.person_id = p_person_id
3386 AND ppf.business_group_id +0 = p_business_group_id
3387 ORDER BY ppf.effective_start_date DESC;
3388
3389 l_apl_number per_people_f.applicant_number%TYPE;
3390
3391 -- Changes end for the bug 14156610
3392 --
3393
3394 l_rowid VARCHAR2(18);
3395 l_pk_id NUMBER;
3396 l_ovn NUMBER;
3397 l_start_date DATE;
3398 l_end_date DATE;
3399 l_business_group_id NUMBER;
3400 l_sal_warning BOOLEAN;
3401 l_assignment_id NUMBER;
3402 l_ptu_count number; -- Bug 4095559
3403 l_b2b_apl boolean default false; -- Bug 4095559
3404 l_person_type_id number; -- Bug 4095559
3405 l_effective_end_date date; -- Bug 4095559
3406 l_effective_start_date date; -- Bug 4095559
3407 l_current_applicant_flag VARCHAR2(30); -- Bug 4095559
3408 l_fut_per_chg boolean default false; -- Bug 4095559
3409 --
3410 l_system_person_type per_person_types.system_person_type%type; --added for bug 8405711
3411 l_action_perform_date date; --Bug 8405711
3412
3413 begin
3414 --
3415 l_business_group_id := p_business_group_id;
3416 --
3417 open appl_asg;
3418 <<apl_asg>>
3419 loop
3420 fetch appl_asg into l_assignment_id;
3421 exit when appl_asg%NOTFOUND;
3422 --
3423 --
3424 --
3425 open events_or_interviews(l_assignment_id);
3426 <<event>>
3427 loop
3428 fetch events_or_interviews into l_pk_id;
3429 exit when events_or_interviews%NOTFOUND;
3430 delete from per_events pe
3431 where pe.event_id = l_pk_id;
3432 if sql%notfound then
3433 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3434 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3435 hr_utility.set_message_token('STEP',2);
3436 hr_utility.raise_error;
3437 end if;
3438 end loop event;
3439 close events_or_interviews;
3440 --
3441 --
3442 open budget_values2(l_assignment_id);
3443 <<budget_val>>
3444 loop
3445 fetch budget_values2 into l_rowid;
3446 exit when budget_values2%NOTFOUND;
3447 delete from per_assignment_budget_values_f pab
3448 where pab.rowid = l_rowid;
3449 if sql%notfound then
3450 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3451 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3452 hr_utility.set_message_token('STEP',3);
3453 hr_utility.raise_error;
3454 end if;
3455 end loop budget_val;
3456 close budget_values2;
3457 --
3458 --
3459 open comments2(l_assignment_id);
3460 <<comments>>
3461 loop
3462 fetch comments2 into l_pk_id;
3463 exit when comments2%NOTFOUND;
3464 delete from hr_comments h
3465 where h.comment_id = l_pk_id;
3466 if sql%notfound then
3467 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3468 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3469 hr_utility.set_message_token('STEP',4);
3470 hr_utility.raise_error;
3471 end if;
3472 end loop comments;
3473 close comments2;
3474 --
3475 --
3476 open letters2(l_assignment_id);
3477 <<letters>>
3478 loop
3479 fetch letters2 into l_pk_id;
3480 exit when letters2%NOTFOUND;
3481 delete from per_letter_request_lines plrl
3482 where plrl.letter_request_line_id = l_pk_id;
3483 if sql%notfound then
3484 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3485 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3486 hr_utility.set_message_token('STEP',5);
3487 hr_utility.raise_error;
3488 end if;
3489 end loop letters;
3490 close letters2;
3491 --
3492 --
3493 open sec_asg_statuses2(l_assignment_id);
3494 <<sec_asg_statuses>>
3495 loop
3496 fetch sec_asg_statuses2 into l_rowid;
3497 exit when sec_asg_statuses2%NOTFOUND;
3498 per_secondary_ass_statuses_pkg.delete_row(l_rowid);
3499 end loop sec_asg_statuses;
3500 close sec_asg_statuses2;
3501 --
3502 --
3503 open asg_extra_info2(l_assignment_id);
3504 <<asg_extra_info>>
3505 loop
3506 fetch asg_extra_info2 into l_pk_id, l_ovn;
3507 exit when asg_extra_info2%NOTFOUND;
3508 hr_assignment_extra_info_api.delete_assignment_extra_info(
3509 p_validate => FALSE
3510 ,p_assignment_extra_info_id => l_pk_id
3511 ,p_object_version_number => l_ovn);
3512 end loop asg_extra_info;
3513 close asg_extra_info2;
3514 --
3515 --
3516 open pay_proposals2(l_assignment_id);
3517 <<pay_proposals>>
3518 loop
3519 fetch pay_proposals2 into l_pk_id, l_ovn;
3520 exit when pay_proposals2%NOTFOUND;
3521 hr_maintain_proposal_api.delete_salary_proposal(p_pay_proposal_id => l_pk_id
3522 ,p_business_group_id => l_business_group_id
3523 ,p_object_version_number => l_ovn
3524 ,p_validate => FALSE
3525 ,p_salary_warning => l_sal_warning);
3526 end loop pay_proposals;
3527 close pay_proposals2;
3528 --
3529 --
3530 open work_incidents2(l_assignment_id);
3531 <<work_incidents>>
3532 loop
3533 fetch work_incidents2 into l_pk_id, l_ovn;
3534 exit when work_incidents2%NOTFOUND;
3535 per_work_incident_api.delete_work_incident(
3536 p_validate => FALSE
3537 ,p_incident_id => l_pk_id
3538 ,p_object_version_number => l_ovn);
3539 end loop work_incidents;
3540 close work_incidents2;
3541 --
3542 --
3543 open asg_prop_answer2(l_assignment_id);
3544 <<asg_prop_answer>>
3545 loop
3546 fetch asg_prop_answer2 into l_rowid;
3547 exit when asg_prop_answer2%NOTFOUND;
3548 delete from per_assign_proposal_answers papa
3549 where papa.rowid = l_rowid;
3550 if sql%notfound then
3551 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3552 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3553 hr_utility.set_message_token('STEP',6);
3554 hr_utility.raise_error;
3555 end if;
3556 end loop asg_prop_answer;
3557 close asg_prop_answer2;
3558 --
3559 --
3560 -- FIX 1977389 - commented out.
3561 -- open disabilities2(l_assignment_id);
3562 -- <<disabilities>>
3563 -- loop
3564 -- fetch disabilities2 into l_pk_id, l_ovn;
3565 -- exit when disabilities2%NOTFOUND;
3566 -- per_disability_api.delete_disability(
3567 -- p_validate => FALSE
3568 -- ,p_effective_date => p_date_received
3569 -- ,p_datetrack_mode => 'ZAP'
3570 -- ,p_disability_id => l_pk_id
3571 -- ,p_object_version_number => l_ovn
3572 -- ,p_effective_start_date => l_start_date
3573 -- ,p_effective_end_date => l_end_date );
3574 -- end loop disabilities;
3575 -- close disabilities2;
3576 -- END FIX.
3577 --
3578 end loop apl_asg;
3579 close appl_asg;
3580 --
3581 -- now deletes which don't need to be in appl_asg loop since they don't
3582 -- drive off assignment_id
3583 --
3584 open bookings2;
3585 <<bookings>>
3586 loop
3587 fetch bookings2 into l_pk_id;
3588 exit when bookings2%NOTFOUND;
3589 delete from per_bookings pb
3590 where pb.booking_id = l_pk_id;
3591 if sql%notfound then
3592 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3593 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3594 hr_utility.set_message_token('STEP',7);
3595 hr_utility.raise_error;
3596 end if;
3597 end loop bookings;
3598 close bookings2;
3599 --
3600 --
3601 open person_apl_comments2;
3602 <<person_apl_comments>>
3603 loop
3604 fetch person_apl_comments2 into l_pk_id;
3605 exit when person_apl_comments2%NOTFOUND;
3606 delete from hr_comments h
3607 where h.comment_id = l_pk_id;
3608 if sql%notfound then
3609 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3610 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3611 hr_utility.set_message_token('STEP',8);
3612 hr_utility.raise_error;
3613 end if;
3614 end loop person_apl_comments;
3615 close person_apl_comments2;
3616 --
3617 -- assignment, application, people records should be deleted last in this
3618 -- order to avoid integrity constraint errors.
3619 --
3620 open assignments;
3621 <<assign>>
3622 loop
3623 fetch assignments into l_rowid;
3624 exit when assignments%NOTFOUND;
3625 delete from per_assignments_f paf
3626 where paf.rowid = l_rowid;
3627 if sql%notfound then
3628 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3629 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3630 hr_utility.set_message_token('STEP',9);
3631 hr_utility.raise_error;
3632 end if;
3633 end loop assign;
3634 close assignments;
3635 --
3636 --
3637 open applications;
3638 <<application>>
3639 loop
3640 fetch applications into l_rowid;
3641 exit when applications%NOTFOUND;
3642 delete from per_applications pap
3643 where pap.rowid = l_rowid;
3644 if sql%notfound then
3645 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3646 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3647 hr_utility.set_message_token('STEP',10);
3648 hr_utility.raise_error;
3649 end if;
3650 end loop application;
3651 close applications;
3652 --
3653 -- Bug 4095559 : Getting the B2B flag
3654 --
3655 open csr_b2b_apl;
3656 fetch csr_b2b_apl into l_ptu_count;
3657 close csr_b2b_apl;
3658
3659 IF l_ptu_count > 0
3660 THEN
3661 l_b2b_apl:=TRUE;
3662 ELSE
3663 l_b2b_apl:=FALSE;
3664 END IF;
3665 -- Bug 4095559 : Execute the existing logic, if it is not B2B
3666 if NOT l_b2b_apl then
3667 open people;
3668 <<per>>
3669 loop
3670 fetch people into l_rowid;
3671 exit when people%notfound;
3672 delete from per_people_f
3673 where rowid = l_rowid;
3674 if sql%notfound then
3675 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3676 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3677 hr_utility.set_message_token('STEP',11);
3678 hr_utility.raise_error;
3679 end if;
3680 end loop per;
3681 if people%rowcount <1 then
3682 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3683 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3684 hr_utility.set_message_token('STEP',12);
3685 hr_utility.raise_error;
3686 end if;
3687 close people;
3688 --
3689 --
3690 open new_people;
3691 <<new_per>>
3692 loop
3693 fetch new_people into l_rowid;
3694 exit when new_people%notfound;
3695 update per_people_f
3696 set effective_end_date = p_end_of_time
3697 where rowid = l_rowid;
3698 --
3699 if sql%ROWCOUNT <1 then
3700 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3701 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3702 hr_utility.set_message_token('STEP',13);
3703 hr_utility.raise_error;
3704 end if;
3705 end loop new_per;
3706 if new_people%rowcount <1 then
3707 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3708 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3709 hr_utility.set_message_token('STEP',14);
3710 hr_utility.raise_error;
3711 end if;
3712 close new_people;
3713 -- Bug 4095559 Starts
3714 -- Desc: : Execute the new logic, if it is B2B
3715 else
3716 -- start changes for bug 8405711
3717 get_prev_person_type(p_business_group_id,
3718 p_person_id => p_person_id,
3719 p_effective_date => p_date_received,
3720 p_current_person_type => 'APL',
3721 p_system_person_type => l_system_person_type);
3722
3723 l_person_type_id := hr_person_type_usage_info.get_default_person_type_id
3724 (p_business_group_id, l_system_person_type);
3725
3726 open chk_action_perform_date;
3727 fetch chk_action_perform_date into l_action_perform_date;
3728 close chk_action_perform_date;
3729
3730 -- if l_action_perform_date is not null, means the action performs on the future day.
3731 IF l_action_perform_date IS NOT NULL THEN
3732 open people_b2b;
3733 <<per_b2b>>
3734 loop
3735 fetch people_b2b into l_rowid,l_effective_end_date,
3736 l_effective_start_date,l_current_applicant_flag;
3737 exit when people_b2b%notfound;
3738 l_fut_per_chg := TRUE; -- set to TRUE if the future person change exists
3739 -- Get the latest person Type Id to update the person table.
3740 --commented for bug 8405711
3741 /*open latest_ptid;
3742 fetch latest_ptid into l_person_type_id;
3743 close latest_ptid;*/
3744 -- Upadte the person table with the person type id
3745 /*update per_people_f
3746 set PERSON_TYPE_ID=l_person_type_id
3747 where rowid = l_rowid;*/
3748
3749 delete from per_people_f
3750 where rowid = l_rowid;
3751
3752 if sql%notfound THEN
3753 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3754 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3755 hr_utility.set_message_token('STEP',15);
3756 hr_utility.raise_error;
3757 end if;
3758 end loop per_b2b;
3759 /* if people_b2b%rowcount <1 then
3760 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3761 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3762 hr_utility.set_message_token('STEP',16);
3763 hr_utility.raise_error;
3764 end if;*/
3765 close people_b2b;
3766 ELSE
3767 open people_b2b_with_prev_record;
3768 <<per_b2b_with_prev_record>>
3769 loop
3770 fetch people_b2b_with_prev_record into l_rowid,l_effective_end_date,
3771 l_effective_start_date,l_current_applicant_flag;
3772 exit when people_b2b_with_prev_record%notfound;
3773
3774 delete from per_people_f
3775 where rowid = l_rowid;
3776
3777 if sql%notfound then
3778 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3779 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3780 hr_utility.set_message_token('STEP',15);
3781 hr_utility.raise_error;
3782 end if;
3783 end loop per_b2b_with_prev_record;
3784 CLOSE people_b2b_with_prev_record;
3785 END IF;
3786 -- end changes for bug 8405711
3787
3788 open new_people_b2b;
3789 <<new_per_b2b>>
3790 loop
3791 fetch new_people_b2b into l_rowid,l_effective_end_date,
3792 l_effective_start_date,l_current_applicant_flag;
3793 exit when new_people_b2b%notfound;
3794
3795 --start changes for bug 8405711
3796 /* if l_effective_end_date = hr_general.effective_date -1 then
3797 delete from per_people_f
3798 where rowid = l_rowid;
3799 elsif */
3800 IF l_current_applicant_flag = 'Y' then
3801 --
3802 -- Changes start for the bug 14156610
3803
3804 open csr_pre_apl_num;
3805 fetch csr_pre_apl_num into l_apl_number;
3806 close csr_pre_apl_num;
3807
3808 update per_people_f
3809 set applicant_number = l_apl_number, --null, -- commented for fix of Bug#14156610
3810 current_applicant_flag=null,
3811 current_emp_or_apl_flag=DECODE (l_system_person_type,'EMP','Y',null),
3812 PERSON_TYPE_ID=l_person_type_id,
3813 effective_end_date=hr_general.end_of_time
3814 where rowid = l_rowid;
3815
3816 -- Changes end for the bug 14156610
3817 --
3818 END IF;
3819 -- Get the latest person Type Id to update the person table.
3820 /*open latest_ptid;
3821 fetch latest_ptid into l_person_type_id;
3822 close latest_ptid;*/
3823 -- Updating the person table, if there is future changes
3824 /* if l_fut_per_chg then
3825 update per_people_f
3826 set applicant_number = null,
3827 current_applicant_flag=null,
3828 current_emp_or_apl_flag=DECODE (l_system_person_type,'EMP','Y',null),
3829 PERSON_TYPE_ID=l_person_type_id
3830 where rowid = l_rowid;
3831 -- Updating the person table, if there is NO future changes
3832 else
3833 update per_people_f
3834 set applicant_number = null,
3835 current_applicant_flag=null,
3836 current_emp_or_apl_flag=DECODE (l_system_person_type,'EMP','Y',null),
3837 PERSON_TYPE_ID=l_person_type_id,
3838 effective_end_date=hr_general.end_of_time
3839 where rowid = l_rowid;
3840 end if;
3841 end if;*/
3842 --
3843 --end changes for bug 8405711
3844 if sql%notfound then
3845 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3846 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3847 hr_utility.set_message_token('STEP',17);
3848 hr_utility.raise_error;
3849 end if;
3850 end loop new_per_b2b;
3851 if new_people_b2b%rowcount <1 then
3852 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3853 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3854 hr_utility.set_message_token('STEP',18);
3855 hr_utility.raise_error;
3856 end if;
3857 close new_people_b2b;
3858
3859 end if;
3860 -- Bug 4095559 Ends
3861 --
3862 --
3863 /* PTU changes: this is no longer necessary since PTU records
3864 will be maintained differently.
3865
3866 open person_type_usages2;
3867 <<person_type_usages>>
3868 loop
3869 fetch person_type_usages2 into l_pk_id, l_ovn;
3870 exit when person_type_usages2%NOTFOUND;
3871 hr_per_type_usage_internal.delete_person_type_usage
3872 (p_validate => FALSE
3873 ,p_person_type_usage_id => l_pk_id
3874 ,p_effective_date => p_date_received
3875 ,p_datetrack_mode => 'ZAP'
3876 ,p_object_version_number => l_ovn
3877 ,p_effective_start_date => l_start_date
3878 ,p_effective_end_date => l_end_date );
3879 end loop person_type_usages;
3880 if person_type_usages2%rowcount <1 then
3881 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3882 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
3883 hr_utility.set_message_token('STEP',15);
3884 hr_utility.raise_error;
3885 end if;
3886 close person_type_usages2;
3887 */
3888 -- Bug 4095559 Starts
3889 -- Desc: Delete the PTU records directly, if it is B2B
3890 if NOT l_b2b_apl then
3891 hr_per_type_usage_internal.cancel_person_type_usage
3892 (p_effective_date => p_date_received
3893 ,p_person_id => p_person_id
3894 ,p_system_person_type => 'APL');
3895
3896 else
3897 -- Bug 4095559 : Delete the Applicant record from PTU
3898
3899 -- start changes for bug 8405711
3900 open chk_prv_APL_exists;
3901 fetch chk_prv_APL_exists into l_prv_person_type_id;
3902 if chk_prv_APL_exists%found then
3903 close chk_prv_APL_exists;
3904
3905 -- When the person became B2B Applicant on his hired Day then
3906 -- system will convert the APL record to EX_APL type.
3907 update per_person_type_usages_f ptu
3908 set person_type_id = hr_person_type_usage_info.get_default_person_type_id
3909 (p_business_group_id, 'EX_APL')
3910 where ptu.person_id = p_person_id
3911 and ptu.effective_start_date = p_date_received
3912 and person_type_id in (
3913 select ppt.person_type_id
3914 from per_person_types ppt
3915 where business_group_id = p_business_group_id
3916 and ppt.system_person_type = 'APL');
3917 ELSE
3918 close chk_prv_APL_exists;
3919 -- end changes for bug 8405711
3920
3921 -- updated for bug 8405711, to delete all the APL entries after the Application start date
3922 delete from per_person_type_usages_f ptu
3923 where ptu.person_id = p_person_id
3924 and ptu.effective_start_date >= p_date_received
3925 and ptu.person_type_id in (
3926 select ppt.person_type_id
3927 from per_person_types ppt
3928 where ppt.system_person_type = 'APL'
3929 and business_group_id = p_business_group_id);
3930
3931 -- start changes for bug 8405711
3932 update per_person_type_usages_f ptu
3933 set effective_end_date = to_date('31/12/4712','dd/mm/yyyy')
3934 where ptu.person_id = p_person_id
3935 and ptu.person_type_id in (
3936 select ppt.person_type_id
3937 from per_person_types ppt
3938 where ppt.system_person_type = 'EX_APL'
3939 and business_group_id = p_business_group_id)
3940 and ptu.effective_end_date = p_date_received - 1;
3941 -- end changes for bug 8405711
3942
3943 end if; --added for bug 8405711
3944
3945 end if;
3946 -- Bug 4095559 Ends
3947 --
3948 per_cancel_hire_or_apl_pkg.update_person_list (p_person_id => p_person_id);
3949 --
3950 for asg_sec_rec in csr_asg_sec loop
3951 --
3952 hr_security_internal.add_to_person_list(
3953 p_effective_date => asg_sec_rec.effective_start_date,
3954 p_assignment_id => asg_sec_rec.assignment_id);
3955 --
3956 end loop;
3957 --
3958 end do_cancel_appl;
3959 --
3960 procedure update_person_list (p_person_id NUMBER) is
3961 begin
3962 --
3963 -- Delete all rows from per_person_list
3964 --
3965 hr_security_internal.clear_from_person_list(p_person_id);
3966 --
3967 end update_person_list;
3968 --
3969 /*===========================================================================*
3970 | |
3971 | PRE_CANCEL_PLACEMENT_CHECKS |
3972 | |
3973 *============================================================================*/
3974 --
3975 PROCEDURE pre_cancel_placement_checks
3976 (p_person_id IN NUMBER
3977 ,p_business_group_id IN NUMBER
3978 ,p_effective_date IN DATE
3979 ,p_date_start IN DATE
3980 ,p_supervisor_warning IN OUT NOCOPY BOOLEAN
3981 ,p_recruiter_warning IN OUT NOCOPY BOOLEAN
3982 ,p_event_warning IN OUT NOCOPY BOOLEAN
3983 ,p_interview_warning IN OUT NOCOPY BOOLEAN
3984 ,p_review_warning IN OUT NOCOPY BOOLEAN
3985 ,p_vacancy_warning IN OUT NOCOPY BOOLEAN
3986 ,p_requisition_warning IN OUT NOCOPY BOOLEAN
3987 ,p_budget_warning IN OUT NOCOPY BOOLEAN
3988 ,p_payment_warning IN OUT NOCOPY BOOLEAN) IS
3989 --
3990 l_proc VARCHAR2(72) := g_package||'pre_cancel_placement_checks';
3991 --
3992 l_dummy VARCHAR2(30);
3993 l_dummy_id NUMBER;
3994 l_effective_date DATE;
3995 l_effective_start_date DATE;
3996 l_person_type_usage_id NUMBER;
3997 --
3998 -- Multi Assignment Check
3999 --
4000 CURSOR csr_multi_asg_check IS
4001 SELECT assignment_id
4002 FROM per_assignments_f paf
4003 WHERE (paf.business_group_id = p_business_group_id OR
4004 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
4005 AND paf.person_id = p_person_id;
4006 --
4007 -- Check person is a CWK
4008 --
4009 CURSOR csr_chk_person_is_cwk IS
4010 SELECT per.person_id,
4011 per.effective_start_date
4012 FROM per_people_f per
4013 WHERE per.person_id = p_person_id
4014 AND per.current_npw_flag = 'Y'
4015 AND per.effective_start_date >= p_date_start;
4016 --
4017 -- Supervisor
4018 --
4019 CURSOR csr_supervisor IS
4020 SELECT ROWID
4021 FROM per_assignments_f p
4022 WHERE (p.business_group_id = p_business_group_id OR
4023 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
4024 AND p.supervisor_id = p_person_id
4025 --start changes for bug 13014331
4026 and p.effective_end_date >= p_date_start
4027 and p.assignment_type in ('E','C')
4028 and not exists (
4029 select 1
4030 from per_assignment_status_types past
4031 where past.assignment_status_type_id = p.assignment_status_type_id
4032 and past.per_system_status = 'TERM_ASSIGN'
4033 and (past.business_group_id = p.business_group_id
4034 or past.business_group_id is NULL));
4035 --end changes for bug 13014331
4036
4037 --
4038 -- recruiter cursor
4039 --
4040 CURSOR csr_recruiter IS
4041 SELECT ROWID
4042 FROM per_assignments_f p
4043 WHERE p.recruiter_id = p_person_id
4044 AND (p.business_group_id = p_business_group_id OR
4045 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y');
4046 --
4047 -- reviews or Events cursor
4048 --
4049 CURSOR csr_reviews_or_events(p_type varchar2) IS
4050 SELECT 'Events exist'
4051 FROM per_events pe,
4052 per_bookings pb
4053 WHERE pe.business_group_id = pb.business_group_id
4054 AND (pb.business_group_id = p_business_group_id OR
4055 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
4056 AND pe.event_id = pb.event_id
4057 AND pe.event_or_interview = p_type
4058 AND pb.person_id = p_person_id;
4059 --
4060 -- Interviews cursor
4061 --
4062 CURSOR csr_interviews IS
4063 SELECT 'Interviews exist'
4064 FROM per_events pe
4065 WHERE (pe.business_group_id = p_business_group_id OR
4066 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
4067 AND pe.event_or_interview = 'I'
4068 AND pe.internal_contact_person_id = p_person_id;
4069 --
4070 -- vacancies cursor
4071 --
4072 CURSOR csr_vacancy IS
4073 SELECT ROWID
4074 FROM per_vacancies pv
4075 WHERE (pv.business_group_id = p_business_group_id OR
4076 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
4077 AND pv.recruiter_id = p_person_id
4078 AND pv.date_from >= l_effective_date;
4079 --
4080 -- requisitions cursor
4081 --
4082 CURSOR csr_requisition IS
4083 SELECT ROWID
4084 FROM per_requisitions pr
4085 WHERE (pr.business_group_id = p_business_group_id OR
4086 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
4087 AND pr.person_id = p_person_id;
4088 --
4089 -- budget_values cursor
4090 --
4091 CURSOR csr_budget_values IS
4092 SELECT ROWID
4093 FROM per_assignment_budget_values_f pab
4094 WHERE pab.business_group_id = p_business_group_id and
4095 EXISTS (SELECT 'budget_values exist'
4096 FROM per_all_assignments_f paf
4097 WHERE pab.business_group_id +0= paf.business_group_id + 0
4098 AND paf.business_group_id +0= p_business_group_id
4099 AND pab.assignment_id = paf.assignment_id
4100 AND paf.person_id = p_person_id
4101 AND paf.period_of_placement_date_start = p_date_start
4102 AND paf.effective_end_date >= l_effective_date
4103 --START for 5987416
4104 --AND pab.effective_end_date >= l_effective_date
4105 )
4106 AND pab.effective_end_date >= l_effective_date;
4107 --end for 5987416
4108
4109 --
4110 -- payment cursor
4111 --
4112 CURSOR csr_payment IS
4113 SELECT ROWID
4114 FROM pay_personal_payment_methods ppm
4115 WHERE ppm.business_group_id = p_business_group_id
4116 AND EXISTS (SELECT 'exists'
4117 FROM per_all_assignments_f paf
4118 WHERE paf.business_group_id +0= p_business_group_id
4119 AND paf.person_id = p_person_id
4120 AND paf.assignment_id = ppm.assignment_id
4121 AND paf.period_of_placement_date_start = p_date_start
4122 --start bug 5987416
4123 --AND ppm.effective_start_date>= l_effective_date
4124 --end bug 5987416
4125 )
4126 AND ppm.effective_start_date >= l_effective_date;
4127 --
4128 CURSOR csr_get_ptu_id IS
4129 SELECT ptu.person_type_usage_id
4130 FROM per_person_types pt,
4131 per_person_type_usages_f ptu
4132 WHERE pt.business_group_id = p_business_group_id
4133 AND pt.person_type_id = ptu.person_type_id
4134 AND l_effective_date BETWEEN ptu.effective_start_date
4135 AND ptu.effective_end_date
4136 AND ptu.person_id = p_person_id
4137 AND pt.system_person_type = 'CWK';
4138 --
4139 -- start changes for bug 8405711
4140 Cursor csr_application_change_exists is
4141 select 'exists'
4142 from per_applications pa
4143 where pa.business_group_id = business_group_id
4144 and pa.person_id = p_person_id
4145 and pa.date_received >= p_date_start
4146 and nvl(pa.application_id,0) <> nvl(
4147 (select application_id
4148 from per_applications
4149 where business_group_id = p_business_group_id
4150 and person_id = p_person_id
4151 and p_date_start - 1 between date_received and nvl(date_end,to_date('31/12/4712','dd/mm/yyyy'))
4152 ),pa.application_id) ;
4153
4154 -- end changes for bug 8405711
4155 BEGIN
4156 --
4157 hr_utility.set_location('Entering : '||l_proc,10);
4158 --
4159 l_effective_date := TRUNC(p_effective_date);
4160 --
4161 -- Retrieve the person type usage ID for the CWK,
4162 -- for later use in this procedure.
4163 --
4164 OPEN csr_get_ptu_id;
4165 FETCH csr_get_ptu_id INTO l_person_type_usage_id;
4166
4167 IF csr_get_ptu_id%NOTFOUND THEN
4168 --
4169 CLOSE csr_get_ptu_id;
4170 --
4171 hr_utility.set_message(801,'HR_289751_CWK_SYS_PER_TYPE_ERR');
4172 hr_utility.raise_error;
4173 --
4174 END IF;
4175 --
4176 hr_utility.set_location(l_proc,15);
4177 --
4178 CLOSE csr_get_ptu_id;
4179 --
4180 -- start changes for bug 8405711
4181 -- if whether there exists any future application for this person or not
4182 --
4183 open csr_application_change_exists;
4184 fetch csr_application_change_exists into l_dummy;
4185
4186 if csr_application_change_exists%FOUND then
4187 close csr_application_change_exists;
4188 hr_utility.set_message(800,'PER_449844_APL_ACTIONS_EXISTS');
4189 hr_utility.set_message_token('PERSON_STATUS','placement');
4190 hr_utility.set_message_token('PERSON_TYPE','Contingent Worker');
4191 hr_utility.raise_error;
4192 else
4193 close csr_application_change_exists;
4194 end if;
4195 -- end changes for bug 8405711
4196
4197 --
4198 -- Check that the person in a CWK employee
4199 -- if they are not then raise an error.
4200 --
4201 OPEN csr_chk_person_is_cwk;
4202 FETCH csr_chk_person_is_cwk INTO l_dummy_id, l_effective_start_date;
4203 --
4204 IF csr_chk_person_is_cwk%NOTFOUND THEN
4205 --
4206 CLOSE csr_chk_person_is_cwk;
4207 --
4208 hr_utility.set_message(801,'HR_289747_MUST_BE_CWK');
4209 hr_utility.raise_error;
4210 --
4211 END IF;
4212 --
4213 CLOSE csr_chk_person_is_cwk;
4214 --
4215 hr_utility.set_location(l_proc,20);
4216 --
4217 -- Check that the CWK Employee has only ONE assignment.
4218 -- If they do not then raise an error asking the user to delete
4219 -- the person.
4220 --
4221 OPEN csr_multi_asg_check;
4222 FETCH csr_multi_asg_check INTO l_dummy_id;
4223 --
4224 IF csr_multi_asg_check%ROWCOUNT > 1 THEN
4225 --
4226 CLOSE csr_multi_asg_check;
4227 --
4228 hr_utility.set_message(801,'HR_289748_MULTIPLE_CWK_ASG');
4229 hr_utility.raise_error;
4230 --
4231 END IF;
4232 --
4233 CLOSE csr_multi_asg_check;
4234 --
4235 hr_utility.set_location(l_proc,30);
4236 --
4237 -- Check that the CWK has no future person type changes.
4238 --
4239 IF hr_person_type_usage_info.FutSysPerTypeChgExists -- 3194314: adding person id
4240 (p_person_type_usage_id => l_person_type_usage_id
4241 ,p_effective_date => p_date_start
4242 ,p_person_id => p_person_id ) THEN
4243 -- #3684683 modified the application id to 800
4244 hr_utility.set_message(800,'HR_289749_CWK_FUTURE_PT_CHGE');
4245 hr_utility.raise_error;
4246
4247 END IF;
4248 --
4249 hr_utility.set_location(l_proc,40);
4250 --
4251 -- Check that the CWK has at least one previous person type.
4252 --
4253 IF NOT hr_person.chk_prev_person_type
4254 (p_system_person_type => 'CWK'
4255 ,p_person_id => p_person_id
4256 ,p_business_group_id => p_business_group_id
4257 ,p_effective_start_date => l_effective_start_date) THEN
4258 --
4259 hr_utility.set_message(800,'HR_289750_NO_PREVIOUS_CWK');
4260 hr_utility.raise_error;
4261 --
4262 END IF;
4263 --
4264 hr_utility.set_location(l_proc,50);
4265 --
4266 -- Check to see if the CWK is a supervisor for another worker.
4267 -- If they are set the supervisor_warning out parameter
4268 -- to TRUE.
4269 --
4270 OPEN csr_supervisor;
4271 --
4272 FETCH csr_supervisor INTO l_dummy;
4273 --
4274 IF csr_supervisor%FOUND THEN
4275 --
4276 p_supervisor_warning := TRUE;
4277 --
4278 ELSE
4279 --
4280 p_supervisor_warning := FALSE;
4281 --
4282 END IF;
4283 --
4284 CLOSE csr_supervisor;
4285 --
4286 hr_utility.set_location(l_proc,60);
4287 --
4288 -- Check to see if the CWK is a recruiter for another worker.
4289 -- If they are set the recruiter_warning out parameter
4290 -- to TRUE.
4291 --
4292 OPEN csr_recruiter;
4293 FETCH csr_recruiter INTO l_dummy;
4294 --
4295 IF csr_recruiter%FOUND THEN
4296 --
4297 p_recruiter_warning := TRUE;
4298 --
4299 ELSE
4300 --
4301 p_recruiter_warning := FALSE;
4302 --
4303 END IF;
4304 --
4305 CLOSE csr_recruiter;
4306 --
4307 hr_utility.set_location(l_proc,70);
4308 --
4309 -- Check to see if the CWK has any events.
4310 -- If they do set the event_warning out parameter
4311 -- to TRUE.
4312 --
4313 OPEN csr_reviews_or_events(p_type =>'E');
4314 FETCH csr_reviews_or_events INTO l_dummy;
4315 --
4316 IF csr_reviews_or_events%FOUND THEN
4317 --
4318 p_event_warning := TRUE;
4319 --
4320 ELSE
4321 --
4322 p_event_warning := FALSE;
4323 --
4324 END IF;
4325 --
4326 CLOSE csr_reviews_or_events;
4327 --
4328 hr_utility.set_location(l_proc,80);
4329 --
4330 -- Check to see if the CWK has any interviews.
4331 -- If they do set the interview_warning out parameter
4332 -- to TRUE.
4333 --
4334 OPEN csr_interviews;
4335 FETCH csr_interviews INTO l_dummy;
4336 --
4337 IF csr_interviews%FOUND THEN
4338 --
4339 p_interview_warning := TRUE;
4340 --
4341 ELSE
4342 --
4343 p_interview_warning := FALSE;
4344 --
4345 END IF;
4346 --
4347 CLOSE csr_interviews;
4348 --
4349 hr_utility.set_location(l_proc,90);
4350 --
4351 OPEN csr_reviews_or_events(p_type =>'I');
4352 FETCH csr_reviews_or_events into l_dummy;
4353 --
4354 IF csr_reviews_or_events%FOUND THEN
4355 --
4356 p_review_warning := TRUE;
4357 --
4358 ELSE
4359 --
4360 p_review_warning := FALSE;
4361 --
4362 END IF;
4363 --
4364 CLOSE csr_reviews_or_events;
4365 --
4366 hr_utility.set_location(l_proc,100);
4367 --
4368 -- Check to see if the CWK is the recruiter for any vacancies.
4369 -- If they do set the vacancy_warning out parameter
4370 -- to TRUE.
4371 --
4372 OPEN csr_vacancy;
4373 FETCH csr_vacancy INTO l_dummy;
4374 --
4375 IF csr_vacancy%FOUND THEN
4376 --
4377 p_vacancy_warning := TRUE;
4378 --
4379 ELSE
4380 --
4381 p_vacancy_warning := FALSE;
4382 --
4383 END IF;
4384 --
4385 CLOSE csr_vacancy;
4386 --
4387 hr_utility.set_location(l_proc,110);
4388 --
4389 -- Check to see if the CWK has any requisitions.
4390 -- If they do set the requisition_warning out parameter
4391 -- to TRUE.
4392 --
4393 OPEN csr_requisition;
4394 FETCH csr_requisition INTO l_dummy;
4395 --
4396 IF csr_requisition%FOUND THEN
4397 --
4398 p_requisition_warning := TRUE;
4399 --
4400 ELSE
4401 --
4402 p_requisition_warning := FALSE;
4403 --
4404 END IF;
4405 --
4406 CLOSE csr_requisition;
4407 --
4408 hr_utility.set_location(l_proc,120);
4409 --
4410 -- Check to see if the CWK has any budget values.
4411 -- If they do set the budget_warning out parameter
4412 -- to TRUE.
4413 --
4414 OPEN csr_budget_values;
4415 FETCH csr_budget_values into l_dummy;
4416 --
4417 IF csr_budget_values%FOUND THEN
4418 --
4419 p_budget_warning := TRUE;
4420 --
4421 ELSE
4422 --
4423 p_budget_warning := FALSE;
4424 --
4425 END IF;
4426 --
4427 CLOSE csr_budget_values;
4428 --
4429 hr_utility.set_location(l_proc,130);
4430 --
4431 OPEN csr_payment;
4432 FETCH csr_payment INTO l_dummy;
4433 --
4434 -- Check to see if the CWK has any personal payment
4435 -- methods values.If they do set the budget_warning
4436 -- out parameter to TRUE.
4437 --
4438 IF csr_payment%FOUND THEN
4439 --
4440 p_payment_warning := TRUE;
4441 --
4442 ELSE
4443 --
4444 p_payment_warning := FALSE;
4445 --
4446 END IF;
4447 --
4448 CLOSE csr_payment;
4449 --
4450 hr_utility.set_location(l_proc,130);
4451 --
4452 EXCEPTION
4453 --
4454 WHEN hr_utility.hr_error THEN
4455 raise;
4456 --
4457 WHEN OTHERS THEN
4458 --
4459 hr_utility.oracle_error(sqlcode);
4460 hr_utility.raise_error;
4461 --
4462 END pre_cancel_placement_checks;
4463 --
4464 /*===========================================================================*
4465 | |
4466 | DO_CANCEL_PLACEMENT |
4467 | |
4468 *============================================================================*/
4469 --
4470 PROCEDURE do_cancel_placement
4471 (p_person_id IN per_people_f.person_id%TYPE
4472 ,p_business_group_id IN per_people_f.business_group_id%TYPE
4473 ,p_effective_date IN DATE
4474 ,p_date_start IN DATE) AS
4475 --
4476 -- Declare local variables
4477 --
4478 l_proc VARCHAR2(72) := g_package||'do_cancel_placement';
4479 --
4480 l_assignment_id NUMBER;
4481 l_end_of_time DATE := hr_general.end_of_time;
4482 l_effective_date DATE;
4483 l_rowid ROWID;
4484 l_dummy_id NUMBER;
4485 l_person_type_id NUMBER;
4486 l_date_start DATE;
4487 l_person_end_date DATE;
4488 l_pop_back_to_back BOOLEAN;
4489 l_pos_back_to_back BOOLEAN;
4490 l_new_person_found BOOLEAN;
4491 l_person_rec_found BOOLEAN;
4492 l_effective_start_date DATE := NULL;
4493 l_effective_end_date DATE := NULL;
4494 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
4495 l_object_version_number per_person_type_usages_f.object_version_number%TYPE;
4496 c_effective_start_date date; --Added for the bug 6460093
4497 --l_ex_apl_person_type_id per_person_type_usages_f.person_type_usage_id%TYPE; --Added for the bug#13442055 -- Commented for the bug#13576986
4498 l_cur_sys_person_type per_person_types.system_person_type%TYPE; --Added for the bug#13576986
4499 l_npw_number per_people_f.npw_number%TYPE; --Added for the bug#13772471
4500 --
4501 -- events cursor
4502 --
4503 CURSOR csr_events IS
4504 SELECT ROWID
4505 FROM per_bookings pb
4506 WHERE (pb.business_group_id = p_business_group_id OR
4507 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
4508 AND pb.person_id = p_person_id
4509 AND EXISTS (SELECT 'row exists'
4510 FROM per_events pe
4511 WHERE pe.business_group_id + 0 = p_business_group_id
4512 AND pe.event_id = pb.event_id
4513 AND pe.event_or_interview in ('I','E')
4514 AND pe.date_start >= l_date_start);
4515 --
4516 -- requisitions cursor
4517 --
4518 CURSOR csr_requisitions IS
4519 SELECT ROWID
4520 FROM per_requisitions pr
4521 WHERE (pr.business_group_id = p_business_group_id OR
4522 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
4523 AND pr.person_id = p_person_id;
4524 --
4525 -- budget_values cursor
4526 --
4527 CURSOR csr_budget_values IS
4528 SELECT ROWID
4529 FROM per_assignment_budget_values_f pab
4530 WHERE pab.business_group_id = p_business_group_id and
4531 EXISTS (SELECT 'budget_values exist'
4532 FROM per_all_assignments_f paf
4533 WHERE pab.business_group_id +0= paf.business_group_id + 0
4534 AND paf.business_group_id +0= p_business_group_id
4535 AND pab.assignment_id = paf.assignment_id
4536 AND paf.person_id = p_person_id
4537 AND paf.period_of_placement_date_start = p_date_start
4538 AND paf.effective_end_date >= l_effective_date
4539 --start for 5987416
4540 --AND pab.effective_end_date >= l_effective_date
4541 )
4542 AND pab.effective_end_date >= l_effective_date;
4543 --end bug 5987416
4544 --
4545 -- payment cursor
4546 --
4547 CURSOR csr_payment IS
4548 SELECT ROWID
4549 FROM pay_personal_payment_methods ppm
4550 WHERE ppm.business_group_id = p_business_group_id
4551 AND EXISTS (SELECT 'exists'
4552 FROM per_all_assignments_f paf
4553 WHERE paf.business_group_id +0= p_business_group_id
4554 AND paf.person_id = p_person_id
4555 AND paf.assignment_id = ppm.assignment_id
4556 AND paf.period_of_placement_date_start = p_date_start
4557 --start bug 5987416
4558 --AND ppm.effective_start_date>= l_effective_date
4559 --end bug 5987416
4560 )
4561 AND ppm.effective_start_date >= l_effective_date;
4562 --
4563 -- supervisor cursor.
4564 --
4565 CURSOR csr_supervisor IS
4566 SELECT ROWID
4567 FROM per_assignments_f p
4568 WHERE (p.business_group_id = p_business_group_id OR
4569 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
4570 AND p.supervisor_id = p_person_id
4571 --start changes for bug 13014331
4572 and p.effective_end_date >= p_date_start
4573 and p.assignment_type in ('E','C')
4574 and not exists (
4575 select 1
4576 from per_assignment_status_types past
4577 where past.assignment_status_type_id = p.assignment_status_type_id
4578 and past.per_system_status = 'TERM_ASSIGN'
4579 and (past.business_group_id = p.business_group_id
4580 or past.business_group_id is NULL));
4581 --end changes for bug 13014331
4582
4583 --
4584 -- recruiter cursor
4585 --
4586 CURSOR csr_recruiter IS
4587 SELECT ROWID
4588 FROM per_assignments_f p
4589 WHERE p.recruiter_id = p_person_id
4590 AND (p.business_group_id = p_business_group_id OR
4591 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y');
4592 --
4593 -- vacancies cursor
4594 --
4595 CURSOR csr_vacancies IS
4596 SELECT ROWID
4597 FROM per_vacancies pv
4598 WHERE (pv.business_group_id = p_business_group_id OR
4599 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
4600 AND pv.recruiter_id = p_person_id
4601 AND pv.date_from >= p_date_start;
4602 --
4603 -- comments cursor
4604 --
4605 CURSOR csr_comments IS
4606 SELECT ROWID
4607 FROM hr_comments h
4608 WHERE EXISTS (SELECT 'comments exist'
4609 FROM per_all_assignments_f paf
4610 WHERE h.comment_id = paf.comment_id
4611 AND paf.business_group_id +0 = p_business_group_id
4612 AND paf.person_id = p_person_id
4613 AND paf.period_of_placement_date_start = p_date_start);
4614 --
4615 -- assignments cursor
4616 --
4617 -- VT #438579 03/05/79 added assignment_id
4618 --
4619 CURSOR csr_assignments1 IS
4620 SELECT ROWID,
4621 assignment_id
4622 FROM per_all_assignments_f paf
4623 WHERE paf.business_group_id +0 = p_business_group_id
4624 AND paf.person_id = p_person_id
4625 AND paf.period_of_placement_date_start = p_date_start;
4626 --
4627 -- assignment cursor for entries update
4628 --
4629 CURSOR csr_assignments2 IS
4630 SELECT assignment_id,
4631 effective_start_date,
4632 ROWID
4633 FROM per_all_assignments_f
4634 WHERE person_id = p_person_id
4635 AND business_group_id +0 = p_business_group_id
4636 AND effective_end_date = p_date_start - 1
4637 AND assignment_type = 'C' -- 3194314
4638 FOR UPDATE OF effective_end_date;
4639 --
4640 -- applications cursor
4641 --
4642 CURSOR csr_applications IS
4643 SELECT ROWID
4644 FROM per_applications pap
4645 WHERE EXISTS (SELECT 'row exists'
4646 FROM per_all_assignments_f paf
4647 WHERE paf.person_id = p_person_id
4648 AND paf.business_group_id +0 = p_business_group_id
4649 AND paf.effective_end_date = p_date_start - 1
4650 AND pap.application_id = paf.application_id);
4651 --
4652 -- person cursor
4653 --
4654 CURSOR csr_person IS
4655 SELECT p.rowid,
4656 effective_end_date
4657 FROM per_people_f p
4658 WHERE p.person_id = p_person_id
4659 AND p.effective_start_date >= p_date_start;
4660 --
4661 -- new_person
4662 --
4663 CURSOR csr_new_person IS
4664 SELECT p.rowid
4665 FROM per_people_f p
4666 WHERE p.person_id = p_person_id
4667 AND p.effective_end_date = p_date_start -1;
4668 --
4669 CURSOR csr_assignment_rate_values IS
4670 SELECT pgr.rowid
4671 FROM pay_grade_rules_f pgr
4672 WHERE EXISTS (SELECT 'X'
4673 FROM per_assignments_f paf
4674 WHERE pgr.grade_or_spinal_point_id = paf.assignment_id
4675 and paf.business_group_id + 0 = p_business_group_id
4676 AND paf.person_id = p_person_id);
4677 --
4678 CURSOR csr_periods_of_placement IS
4679 SELECT pp.rowid
4680 FROM per_periods_of_placement pp
4681 WHERE pp.person_id = p_person_id
4682 AND pp.date_start = p_date_start;
4683 --
4684 CURSOR csr_pop_back_to_back (p_date_start IN DATE) IS
4685 SELECT pp.period_of_placement_id
4686 FROM per_periods_of_placement pp
4687 WHERE pp.person_id = p_person_id
4688 AND pp.actual_termination_date = p_date_start -1;
4689 --
4690 CURSOR csr_pos_back_to_back (p_date_start IN DATE) IS
4691 SELECT ps.period_of_service_id
4692 FROM per_periods_of_service ps
4693 WHERE ps.person_id = p_person_id
4694 AND ps.actual_termination_date = p_date_start -1;
4695 --
4696
4697 -- Change for the bug 6460093 starts here
4698 CURSOR csr_pdped_start is
4699 SELECT pop.date_start
4700 FROM per_periods_of_placement pop
4701 WHERE pop.person_id = p_person_id
4702 AND p_effective_date between pop.date_start and
4703 nvl(pop.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'));
4704
4705 -- Change for the bug 6460093 ends here
4706
4707 CURSOR csr_get_person_type (p_system_person_type IN CHAR) IS
4708 SELECT person_type_id
4709 FROM per_person_types
4710 WHERE business_group_id = p_business_group_id
4711 AND system_person_type = p_system_person_type
4712 AND default_flag = 'Y';
4713
4714 --
4715 --
4716 -- Changes start for the bug 7110731
4717
4718 CURSOR csr_get_cwk_type (p_sys_person_type IN CHAR) IS
4719 SELECT pt.person_type_id
4720 FROM per_person_types pt, per_person_type_usages_f ptu
4721 WHERE pt.person_type_id = ptu.person_type_id
4722 AND pt.business_group_id = p_business_group_id
4723 AND pt.system_person_type = p_sys_person_type
4724 AND ptu.person_id = p_person_id
4725 AND p_effective_date BETWEEN ptu.effective_start_date
4726 AND ptu.effective_end_date;
4727
4728 l_person_type_id1 number;
4729
4730 -- Changes end for the bug 7110731
4731 --
4732
4733 CURSOR csr_get_ptu_details IS
4734 SELECT ptu.person_type_usage_id,
4735 ptu.object_version_number
4736 FROM per_person_type_usages_f ptu,
4737 per_person_types pt
4738 WHERE ptu.person_id = p_person_id
4739 AND pt.person_type_id = ptu.person_type_Id
4740 AND pt.system_person_type = 'CWK'
4741 AND p_effective_date BETWEEN ptu.effective_start_date
4742 AND ptu.effective_end_date;
4743 --
4744 -- Changes start for the bug 13576986
4745
4746 CURSOR csr_is_apl_ex_apl IS
4747 SELECT pt.system_person_type
4748 FROM per_person_type_usages_f ptu,
4749 per_person_types pt
4750 WHERE ptu.person_id = p_person_id
4751 AND pt.person_type_id = ptu.person_type_Id
4752 AND pt.system_person_type in ('EX_APL','APL')
4753 --AND p_effective_date =ptu.effective_start_date; -- Commented for Bug#14166808
4754 AND p_date_start =ptu.effective_start_date; -- Added for Bug#14166808
4755
4756 --
4757 -- Changes start for the bug 13442055
4758
4759 /*CURSOR csr_is_ex_apl IS
4760 SELECT ptu.person_type_usage_id
4761 FROM per_person_type_usages_f ptu,
4762 per_person_types pt
4763 WHERE ptu.person_id = p_person_id
4764 AND pt.person_type_id = ptu.person_type_Id
4765 AND pt.system_person_type = 'EX_APL'
4766 AND p_effective_date =ptu.effective_start_date;*/
4767
4768 -- Changes end for the bug 13442055
4769 --
4770
4771 -- Changes end for the bug 13576986
4772 --
4773 -- Changes start for the bug 13772471
4774
4775 CURSOR csr_pre_npw_num IS
4776 SELECT npw_number
4777 FROM per_all_people_f ppf
4778 --WHERE ppf.effective_start_date < p_effective_date -- Commented for Bug#14166808
4779 WHERE ppf.effective_start_date < p_date_start -- Added for Bug#14166808
4780 AND ppf.person_id = p_person_id
4781 AND ppf.business_group_id +0 = p_business_group_id
4782 ORDER BY ppf.effective_start_date DESC;
4783
4784 -- Changes end for the bug 13772471
4785 --
4786 cursor csr_asg_sec is
4787 select paf.assignment_id, paf.effective_start_date
4788 from per_all_assignments_f paf
4789 where paf.person_id = p_person_id
4790 and paf.business_group_id +0 = p_business_group_id
4791 and paf.assignment_type NOT IN ('B','O'); -- Modified for bug # 9151913
4792 -- and paf.assignment_type <> 'B'; -- Added For Bug # 6630290
4793
4794 BEGIN
4795 --
4796 hr_utility.set_location('Entering : '||l_proc,10);
4797 hr_utility.set_location(l_proc||' date_start = '||p_date_start,11);
4798 hr_utility.set_location(l_proc||' eff date = '||p_effective_date,12);
4799 --
4800 l_effective_date := TRUNC(p_effective_date);
4801 l_date_start := TRUNC(p_effective_date);
4802 --
4803 hr_utility.set_location(l_proc,20);
4804 --
4805 FOR supervisor_rec IN csr_supervisor LOOP
4806 --
4807 hr_utility.set_location(l_proc,60);
4808 --
4809 UPDATE per_all_assignments_f paf
4810 SET paf.supervisor_id = NULL
4811 WHERE paf.rowid = supervisor_rec.rowid;
4812 --
4813 IF sql%notfound THEN
4814 --
4815 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4816 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4817 hr_utility.set_message_token('STEP',1);
4818 hr_utility.raise_error;
4819 --
4820 END IF;
4821 --
4822 END LOOP;
4823 --
4824 hr_utility.set_location(l_proc,70);
4825 --
4826 FOR recruiter_rec IN csr_recruiter LOOP
4827 --
4828 hr_utility.set_location(l_proc,80);
4829 --
4830 UPDATE per_all_assignments_f paf
4831 SET paf.recruiter_id = NULL
4832 WHERE paf.rowid = recruiter_rec.rowid;
4833 --
4834 IF SQL%NOTFOUND THEN
4835 --
4836 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4837 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4838 hr_utility.set_message_token('STEP',2);
4839 hr_utility.raise_error;
4840 --
4841 END IF;
4842 --
4843 END LOOP;
4844 --
4845 hr_utility.set_location(l_proc,90);
4846 --
4847 FOR vacancies_rec IN csr_vacancies LOOP
4848 --
4849 hr_utility.set_location(l_proc,100);
4850 --
4851 UPDATE per_all_vacancies pv
4852 SET pv.recruiter_id = NULL
4853 WHERE pv.rowid = vacancies_rec.rowid;
4854 --
4855 IF SQL%NOTFOUND THEN
4856 --
4857 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4858 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4859 hr_utility.set_message_token('STEP',3);
4860 hr_utility.raise_error;
4861 --
4862 END IF;
4863 --
4864 END LOOP;
4865 --
4866 hr_utility.set_location(l_proc,110);
4867 --
4868 FOR assignment_rate_rec in csr_assignment_rate_values LOOP
4869 --
4870 hr_utility.set_location(l_proc,120);
4871 --
4872 DELETE from pay_grade_rules_f pgr
4873 WHERE pgr.rowid = assignment_rate_rec.rowid;
4874 --
4875 END LOOP;
4876 --
4877 hr_utility.set_location(l_proc,130);
4878 --
4879 FOR comments_rec IN csr_comments LOOP
4880 --
4881 hr_utility.set_location(l_proc,140);
4882 --
4883 DELETE FROM hr_comments h
4884 WHERE h.rowid = comments_rec.rowid;
4885 --
4886 IF SQL%NOTFOUND THEN
4887 --
4888 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4889 hr_utility.set_message_token('PROCEDURE','cancel_placement');
4890 hr_utility.set_message_token('STEP',4);
4891 hr_utility.raise_error;
4892 --
4893 END IF;
4894 --
4895 END LOOP;
4896 --
4897 hr_utility.set_location(l_proc,150);
4898 --
4899 FOR requisition_rec IN csr_requisitions LOOP
4900 --
4901 hr_utility.set_location(l_proc,160);
4902 --
4903 UPDATE per_requisitions pr
4904 SET pr.person_id = NULL
4905 WHERE pr.rowid = requisition_rec.rowid;
4906 --
4907 IF SQL%NOTFOUND THEN
4908 --
4909 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4910 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4911 hr_utility.set_message_token('STEP',5);
4912 hr_utility.raise_error;
4913 --
4914 END IF;
4915 --
4916 END LOOP;
4917 --
4918 hr_utility.set_location(l_proc,170);
4919 --
4920 FOR events_rec IN csr_events LOOP
4921 --
4922 hr_utility.set_location(l_proc,180);
4923 --
4924 DELETE FROM per_bookings pb
4925 WHERE pb.rowid = events_rec.rowid;
4926 --
4927 IF SQL%NOTFOUND THEN
4928 --
4929 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4930 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4931 hr_utility.set_message_token('STEP',6);
4932 hr_utility.raise_error;
4933 --
4934 END IF;
4935 --
4936 END LOOP;
4937 --
4938 hr_utility.set_location(l_proc,190);
4939 --
4940 FOR budget_rec IN csr_budget_values LOOP
4941 --
4942 hr_utility.set_location(l_proc,200);
4943 --
4944 DELETE FROM per_assignment_budget_values_f pab
4945 WHERE pab.rowid = budget_rec.rowid;
4946 --
4947 IF SQL%NOTFOUND THEN
4948 --
4949 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4950 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4951 hr_utility.set_message_token('STEP',7);
4952 hr_utility.raise_error;
4953 --
4954 END IF;
4955 --
4956 END LOOP budget_value;
4957 --
4958 hr_utility.set_location(l_proc,210);
4959 --
4960 FOR payment_rec IN csr_payment LOOP
4961 --
4962 hr_utility.set_location(l_proc,220);
4963 --
4964 DELETE FROM pay_personal_payment_methods ppm
4965 WHERE ppm.rowid = payment_rec.rowid;
4966 --
4967 IF SQL%NOTFOUND THEN
4968 --
4969 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4970 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
4971 hr_utility.set_message_token('STEP',8);
4972 hr_utility.raise_error;
4973 --
4974 end if;
4975 --
4976 END LOOP;
4977 --
4978 hr_utility.set_location(l_proc,230);
4979 --
4980 FOR assignment1_rec IN csr_assignments1 LOOP
4981 --
4982 hr_utility.set_location(l_proc,240);
4983 --
4984 DELETE FROM per_spinal_point_placements_f spp
4985 WHERE spp.assignment_id = assignment1_rec.assignment_id;
4986 --
4987 hr_utility.set_location(l_proc,250);
4988 --
4989 DELETE FROM pay_cost_allocations_f pca
4990 WHERE pca.assignment_id = assignment1_rec.assignment_id;
4991 --
4992 hr_utility.set_location(l_proc,260);
4993 --
4994 DELETE FROM per_all_assignments_f paf
4995 WHERE paf.rowid = assignment1_rec.rowid;
4996 --
4997 IF SQL%NOTFOUND THEN
4998 --
4999 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5000 hr_utility.set_message_token('PROCEDURE','cancel_placement');
5001 hr_utility.set_message_token('STEP',9);
5002 hr_utility.raise_error;
5003 --
5004 END IF;
5005 --
5006 END LOOP;
5007 --
5008 hr_utility.set_location(l_proc,270);
5009 --
5010 FOR applicantions_rec IN csr_applications LOOP
5011 --
5012 hr_utility.set_location(l_proc,280);
5013 --
5014 --
5015 -- Changes start for the bug 13558312
5016 open csr_is_apl_ex_apl;
5017 fetch csr_is_apl_ex_apl into l_cur_sys_person_type; -- Added for the bug#13576986
5018 --fetch csr_is_ex_apl into l_ex_apl_person_type_id; -- Commented for the bug#13576986
5019 close csr_is_apl_ex_apl;
5020
5021 --if l_ex_apl_person_type_id is not null then -- Commented for the bug#13576986
5022 if l_cur_sys_person_type is not null and l_cur_sys_person_type = 'EX_APL' then -- Added for the bug#13576986
5023 --
5024 hr_utility.set_location(l_proc,444);
5025 --
5026 else
5027 hr_utility.set_location(l_proc,445);
5028
5029 UPDATE per_applications pap
5030 SET pap.date_end = NULL
5031 WHERE pap.rowid = applicantions_rec.rowid;
5032 --
5033 end if;
5034 --l_ex_apl_person_type_id := null; -- Commented for the bug#13576986
5035 l_cur_sys_person_type := null; -- Added for the bug#13576986
5036 --
5037 -- Changes end for the bug 13558312
5038 --
5039
5040
5041 IF SQL%NOTFOUND THEN
5042 --
5043 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5044 hr_utility.set_message_token('PROCEDURE','cancel_placement');
5045 hr_utility.set_message_token('STEP',10);
5046 hr_utility.raise_error;
5047 --
5048 END IF;
5049 --
5050 END LOOP;
5051 --
5052 hr_utility.set_location(l_proc,320);
5053 --
5054 -- Change for the bug 6460093 starts here
5055 OPEN csr_pdped_start;
5056 FETCH csr_pdped_start into c_effective_start_date;
5057 CLOSE csr_pdped_start;
5058 -- Change for the bug 6460093 ends here
5059
5060 OPEN csr_periods_of_placement;
5061 --
5062 LOOP
5063 --
5064 hr_utility.set_location(l_proc,330);
5065 --
5066 FETCH csr_periods_of_placement INTO l_rowid;
5067 --
5068 EXIT WHEN csr_periods_of_placement%NOTFOUND;
5069 --
5070 DELETE FROM per_periods_of_placement
5071 WHERE rowid = l_rowid;
5072 --
5073 IF SQL%NOTFOUND THEN
5074 --
5075 CLOSE csr_periods_of_placement;
5076 --
5077 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5078 hr_utility.set_message_token('PROCEDURE','cancel_placement');
5079 hr_utility.set_message_token('STEP',11);
5080 hr_utility.raise_error;
5081 --
5082 END IF;
5083 --
5084 END LOOP;
5085 --
5086 hr_utility.set_location(l_proc,340);
5087 --
5088 IF csr_periods_of_placement%ROWCOUNT <1 THEN
5089 --
5090 CLOSE csr_periods_of_placement;
5091 --
5092 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5093 hr_utility.set_message_token('PROCEDURE','cancel_placement');
5094 hr_utility.set_message_token('STEP',12);
5095 hr_utility.raise_error;
5096 --
5097 END IF;
5098 --
5099 CLOSE csr_periods_of_placement;
5100 --
5101 hr_utility.set_location(l_proc,350);
5102 --
5103 -- Check that there is not a previous period of placement
5104 -- record that ended the day before the current period of placement
5105 -- record. I.e is a back to back hire.
5106 --
5107 OPEN csr_pop_back_to_back(p_date_start => p_date_start);
5108 FETCH csr_pop_back_to_back INTO l_dummy_id;
5109 --
5110 IF csr_pop_back_to_back%FOUND THEN
5111 --
5112 hr_utility.set_location(l_proc,353);
5113 --
5114 l_pop_back_to_back := TRUE;
5115 --
5116 ELSE
5117 --
5118 -- As there no back to back records for period of placements
5119 -- check that there is not a record for period of service
5120 -- that ends the day before the period of placement record
5121 -- being canceled.
5122 --
5123 hr_utility.set_location(l_proc,355);
5124 --
5125 l_pop_back_to_back := FALSE;
5126 --
5127 OPEN csr_pos_back_to_back (p_date_start => p_date_start);
5128 FETCH csr_pos_back_to_back INTO l_dummy_id;
5129 --
5130 IF csr_pos_back_to_back%FOUND THEN
5131 --
5132 hr_utility.set_location(l_proc,356);
5133 --
5134 l_pos_back_to_back := TRUE;
5135 --
5136 ELSE
5137 --
5138 hr_utility.set_location(l_proc,357);
5139 --
5140 l_pos_back_to_back := FALSE;
5141 --
5142 END IF;
5143 --
5144 CLOSE csr_pos_back_to_back;
5145 --
5146 END IF;
5147 --
5148 CLOSE csr_pop_back_to_back;
5149 --
5150 if NOT l_pop_back_to_back then -- 3194314
5151 FOR assignment2_rec IN csr_assignments2 LOOP
5152 --
5153 hr_utility.set_location(l_proc,300);
5154 --
5155 UPDATE per_all_assignments_f paf
5156 SET paf.effective_end_date = l_end_of_time
5157 WHERE paf.rowid = assignment2_rec.rowid;
5158 --
5159 hr_utility.set_location(l_proc,310);
5160 --
5161 END LOOP;
5162 end if;
5163 --
5164 hr_utility.set_location(l_proc,360);
5165 --
5166 FOR person_rec IN csr_person LOOP
5167 --
5168 l_person_rec_found := TRUE;
5169 --
5170 hr_utility.set_location(l_proc,370);
5171 --
5172 IF l_pop_back_to_back THEN
5173 --
5174 hr_utility.set_location(l_proc,380);
5175 --
5176 IF person_rec.effective_end_date = hr_general.end_of_time THEN
5177 --
5178 hr_utility.set_location(l_proc,390);
5179 --
5180 OPEN csr_get_person_type (p_system_person_type => 'EX_CWK');
5181 FETCH csr_get_person_type INTO l_person_type_id;
5182 --
5183 CLOSE csr_get_person_type;
5184 --
5185
5186 --changes start for bug 7110731
5187 OPEN csr_get_cwk_type (p_sys_person_type => 'CWK');
5188 FETCH csr_get_cwk_type INTO l_person_type_id1;
5189 CLOSE csr_get_cwk_type;
5190
5191 UPDATE per_person_type_usages_f
5192 SET person_type_id = l_person_type_id
5193 WHERE person_id = p_person_id
5194 AND person_type_id= l_person_type_id1
5195 AND p_date_start BETWEEN effective_start_date
5196 AND effective_end_date;
5197 --changes end for bug 7110731
5198
5199 UPDATE per_people_f -- 3194314
5200 SET current_npw_flag = null
5201 ,effective_start_date = p_date_start -- in case DT udpates exist
5202 WHERE rowid = person_rec.rowid;
5203 --
5204 ELSE -- #1998140
5205 --
5206 hr_utility.set_location(l_proc,400);
5207 --
5208 DELETE FROM per_people_f
5209 WHERE rowid = person_rec.rowid;
5210 --
5211 END IF;
5212 --
5213
5214 ELSIF l_pos_back_to_back then -- 3194314
5215 -- this is a back-to-back with Employee/Cwk
5216
5217 IF person_rec.effective_end_date = hr_general.end_of_time THEN
5218
5219 -- it should restore the EX_EMP record instead of removing it
5220 hr_utility.set_location(l_proc,405);
5221 --
5222 --
5223 -- Changes start for the bug 13772471
5224
5225 open csr_pre_npw_num;
5226 fetch csr_pre_npw_num into l_npw_number;
5227 close csr_pre_npw_num;
5228
5229 -- Changes end for the bug 13772471
5230 --
5231 UPDATE per_people_f
5232 SET npw_number = l_npw_number, --null -- Modified for bug#13772471
5233 effective_start_date = p_date_start, -- in case DT updates exist
5234 current_npw_flag = null,
5235 per_information7 = 'INCL'
5236 WHERE rowid = person_rec.rowid;
5237
5238 --
5239 ELSE -- #1998140
5240 --
5241 hr_utility.set_location(l_proc,406);
5242 --
5243 DELETE FROM per_people_f
5244 WHERE rowid = person_rec.rowid;
5245 --
5246 END IF;
5247 --
5248 -- << 3194314
5249
5250 ELSE
5251 --
5252 hr_utility.set_location(l_proc,410);
5253 --
5254 --
5255 -- Changes start for the bug 13442055
5256
5257 open csr_is_apl_ex_apl;
5258 fetch csr_is_apl_ex_apl into l_cur_sys_person_type; -- Added for the bug#13576986
5259 --fetch csr_is_ex_apl into l_ex_apl_person_type_id; -- Commented for the bug#13576986
5260 close csr_is_apl_ex_apl;
5261
5262 --if l_ex_apl_person_type_id is not null then -- Commented for the bug#13576986
5263 if l_cur_sys_person_type is not null and -- Added for the bug#13576986
5264 (l_cur_sys_person_type = 'EX_APL' or l_cur_sys_person_type = 'APL') then
5265 --
5266 hr_utility.set_location(l_proc,416);
5267 --
5268 --
5269 -- Changes start for the bug 13772471
5270
5271 open csr_pre_npw_num;
5272 fetch csr_pre_npw_num into l_npw_number;
5273 close csr_pre_npw_num;
5274
5275 -- Changes end for the bug 13772471
5276 --
5277 UPDATE per_people_f
5278 SET npw_number = l_npw_number, --null -- Modified for bug#13772471
5279 effective_start_date = p_date_start, -- in case DT updates exist
5280 current_npw_flag = null
5281 WHERE rowid = person_rec.rowid;
5282 else
5283 --
5284 hr_utility.set_location(l_proc,420); -- Moved this debug line here to resolve Bug#13964489
5285 --
5286 DELETE FROM per_people_f
5287 WHERE rowid = person_rec.rowid;
5288 --
5289 end if;
5290 --l_ex_apl_person_type_id := null; -- Commented for the bug#13576986
5291 l_cur_sys_person_type := null; -- Added for the bug#13576986
5292
5293 END IF;
5294
5295 -- Changes end for the bug 13442055
5296 --
5297
5298 IF SQL%NOTFOUND THEN
5299 --
5300 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5301 hr_utility.set_message_token('PROCEDURE','cancel_placement');
5302 hr_utility.set_message_token('STEP',13);
5303 hr_utility.raise_error;
5304 --
5305 END IF;
5306 --
5307 END LOOP;
5308 --
5309 -- hr_utility.set_location(l_proc,430); -- for bug 14530580
5310 --
5311 IF NOT l_person_rec_found THEN
5312 --
5313 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5314 hr_utility.set_message_token('PROCEDURE','cancel_placement');
5315 hr_utility.set_message_token('STEP',14);
5316 hr_utility.raise_error;
5317 --
5318 END IF;
5319 --
5320 -- hr_utility.set_location(l_proc,440); -- for bug 14530580
5321 --
5322 if NOT l_pos_back_to_back then -- 3194314 this should not get executed if b2b emp/cwk
5323
5324 FOR new_person_rec IN csr_new_person LOOP
5325
5326 --
5327 -- hr_utility.set_location(l_proc,450); -- for bug 14530580
5328 --
5329 l_new_person_found := TRUE;
5330 --
5331 IF NOT l_pop_back_to_back THEN
5332 --
5333 -- hr_utility.set_location(l_proc,460); -- for bug 14530580
5334 --
5335 --
5336 -- Changes start for the bug 13558312
5337 open csr_is_apl_ex_apl;
5338 fetch csr_is_apl_ex_apl into l_cur_sys_person_type; -- Added for the bug#13576986
5339 --fetch csr_is_ex_apl into l_ex_apl_person_type_id; -- Commented for the bug#13576986
5340 close csr_is_apl_ex_apl;
5341
5342 --if l_ex_apl_person_type_id is not null then -- Commented for the bug#13576986
5343 if l_cur_sys_person_type is not null and -- Added for the bug#13576986
5344 (l_cur_sys_person_type = 'EX_APL' or l_cur_sys_person_type = 'APL') then
5345 --
5346 -- modified for bug 14530580
5347 -- hr_utility.set_location(l_proc,555);
5348 NULL;
5349 -- modified for bug 14530580
5350 --
5351 else
5352 hr_utility.set_location(l_proc,556);
5353
5354 UPDATE per_people_f
5355 SET effective_end_date = l_end_of_time
5356 WHERE rowid = new_person_rec.rowid;
5357 --
5358 end if;
5359 --l_ex_apl_person_type_id := null; -- Commented for the bug#13576986
5360 l_cur_sys_person_type := null; -- Added for the bug#13576986
5361 --
5362 -- Changes end for the bug 13558312
5363 END IF;
5364 --
5365
5366
5367 IF sql%ROWCOUNT <1 then
5368 --
5369 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5370 hr_utility.set_message_token('PROCEDURE','cancel_placement');
5371 hr_utility.set_message_token('STEP',15);
5372 hr_utility.raise_error;
5373 --
5374 END IF;
5375 --
5376 END LOOP;
5377 end if; -- << 3194314
5378
5379 --
5380 hr_utility.set_location(l_proc,480);
5381 --
5382 IF NOT l_new_person_found THEN
5383 --
5384 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
5385 hr_utility.set_message_token('PROCEDURE','cancel_placement');
5386 hr_utility.set_message_token('STEP',16);
5387 hr_utility.raise_error;
5388 --
5389 END IF;
5390 --
5391 -- If there are no back to back records for
5392 -- period of placements or period of service then
5393 -- cancel the current CWK placement record
5394 --
5395 IF NOT l_pop_back_to_back AND
5396 NOT l_pos_back_to_back THEN
5397 --
5398 hr_utility.set_location(l_proc,490);
5399 --
5400 --
5401 --
5402 -- Changes start for the bug 13442055
5403
5404 open csr_is_apl_ex_apl;
5405 fetch csr_is_apl_ex_apl into l_cur_sys_person_type; -- Added for the bug#13576986
5406 --fetch csr_is_ex_apl into l_ex_apl_person_type_id; -- Commented for the bug#13576986
5407 close csr_is_apl_ex_apl;
5408
5409 --if l_ex_apl_person_type_id is not null then -- Commented for the bug#13576986
5410 if l_cur_sys_person_type is not null and l_cur_sys_person_type = 'EX_APL' then -- Added for the bug#13576986
5411 OPEN csr_get_ptu_details;
5412 FETCH csr_get_ptu_details INTO l_person_type_usage_id,
5413 l_object_version_number;
5414 --
5415 IF csr_get_ptu_details%FOUND THEN
5416 --
5417 hr_utility.set_location(l_proc,503);
5418 --
5419 hr_per_type_usage_internal.delete_person_type_usage
5420 (p_validate => FALSE
5421 ,p_person_type_usage_id => l_person_type_usage_id
5422 ,p_effective_date => p_effective_date
5423 ,p_datetrack_mode => hr_api.g_zap
5424 ,p_object_version_number => l_object_version_number
5425 ,p_effective_start_date => l_effective_start_date
5426 ,p_effective_end_date => l_effective_end_date);
5427 --
5428 END IF;
5429
5430 else
5431 hr_per_type_usage_internal.cancel_person_type_usage
5432 (p_effective_date => l_date_start
5433 ,p_person_id => p_person_id
5434 ,p_system_person_type => 'CWK');
5435 end if;
5436
5437 -- Changes end for the bug 13442055
5438 --
5439
5440 --Added for the bug 6460093
5441 --This finds out any system person type of OTHER records
5442 --which is end dated while creating a placement
5443 --and updates the end date to end of time while
5444 --cancelling the placement
5445
5446 upd_person_type_usage_end_date(c_effective_start_date-1
5447 ,p_person_id
5448 ,p_system_person_type => 'OTHER');
5449
5450 --Change for the bug 6460093 ends here
5451
5452 -- If there are back to back records for period of service
5453 -- then delete the current CWK record from per_person_type_usages_f
5454 -- table.
5455 --
5456 -- bug fix 6992346
5457 -- If there are back to back records for period of service
5458 -- and period of placements then
5459 elsif NOT l_pop_back_to_back AND l_pos_back_to_back THEN
5460 hr_utility.set_location(l_proc,491);
5461 hr_utility.set_location('l_date_start '||l_date_start,491);
5462 --
5463 --
5464 -- Changes start for the bug 13442055
5465 open csr_is_apl_ex_apl;
5466 fetch csr_is_apl_ex_apl into l_cur_sys_person_type; -- Added for the bug#13576986
5467 --fetch csr_is_ex_apl into l_ex_apl_person_type_id; -- Commented for the bug#13576986
5468 close csr_is_apl_ex_apl;
5469
5470 --if l_ex_apl_person_type_id is not null then -- Commented for the bug#13576986
5471 if l_cur_sys_person_type is not null and l_cur_sys_person_type = 'EX_APL' then -- Added for the bug#13576986
5472 OPEN csr_get_ptu_details;
5473 FETCH csr_get_ptu_details INTO l_person_type_usage_id,
5474 l_object_version_number;
5475 --
5476 IF csr_get_ptu_details%FOUND THEN
5477 --
5478 hr_utility.set_location(l_proc,504);
5479 --
5480 hr_per_type_usage_internal.delete_person_type_usage
5481 (p_validate => FALSE
5482 ,p_person_type_usage_id => l_person_type_usage_id
5483 ,p_effective_date => p_effective_date
5484 ,p_datetrack_mode => hr_api.g_zap
5485 ,p_object_version_number => l_object_version_number
5486 ,p_effective_start_date => l_effective_start_date
5487 ,p_effective_end_date => l_effective_end_date);
5488 --
5489 END IF;
5490
5491 else
5492 hr_per_type_usage_internal.cancel_person_type_usage
5493 (p_effective_date => l_date_start
5494 ,p_person_id => p_person_id
5495 ,p_system_person_type => 'CWK');
5496 end if;
5497
5498 -- Changes end for the bug 13442055
5499 --
5500
5501 ELSIF l_pos_back_to_back and l_pop_back_to_back THEN
5502 --ELSIF l_pos_back_to_back THEN
5503 --
5504 hr_utility.set_location(l_proc,500);
5505 --
5506 OPEN csr_get_ptu_details;
5507 FETCH csr_get_ptu_details INTO l_person_type_usage_id,
5508 l_object_version_number;
5509 --
5510 IF csr_get_ptu_details%FOUND THEN
5511 --
5512 hr_utility.set_location(l_proc,510);
5513 --
5514 hr_per_type_usage_internal.delete_person_type_usage
5515 (p_validate => FALSE
5516 ,p_person_type_usage_id => l_person_type_usage_id
5517 ,p_effective_date => p_effective_date
5518 ,p_datetrack_mode => hr_api.g_zap
5519 ,p_object_version_number => l_object_version_number
5520 ,p_effective_start_date => l_effective_start_date
5521 ,p_effective_end_date => l_effective_end_date);
5522 --
5523 END IF;
5524 --
5525 END IF;
5526 -- end of fix 6992346
5527 --
5528 per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);
5529 --
5530 for asg_sec_rec in csr_asg_sec loop
5531 --
5532 hr_utility.set_location(l_proc,300);
5533 -- do some security maintenance.
5534 -- reset the security access(per_person_list) for this assignment
5535 hr_security_internal.add_to_person_list(
5536 p_effective_date => asg_sec_rec.effective_start_date
5537 ,p_assignment_id => asg_sec_rec.assignment_id);
5538 --
5539 end loop;
5540 --
5541 hr_utility.set_location('Leaving : '||l_proc,999);
5542 --
5543 END do_cancel_placement;
5544 --
5545 -- ---------------------------------------------------------------------------
5546 -- |---------------------< return_legislation_code >-------------------------|
5547 -- ---------------------------------------------------------------------------
5548 --
5549 FUNCTION return_legislation_code
5550 (p_person_id IN NUMBER ) RETURN VARCHAR2 IS
5551 --
5552 -- Declare cursor
5553 --
5554 CURSOR csr_leg_code IS
5555 SELECT pbg.legislation_code
5556 FROM per_business_groups pbg
5557 , per_people_f per
5558 WHERE per.person_id = p_person_id
5559 AND pbg.business_group_id = per.business_group_id;
5560 --
5561 -- Declare local variables
5562 --
5563 l_legislation_code VARCHAR2(150);
5564 l_proc VARCHAR2(72) := g_package||'return_legislation_code';
5565 --
5566 BEGIN
5567 --
5568 hr_utility.set_location('Entering:'|| l_proc, 10);
5569 --
5570 -- Ensure that all the mandatory parameter are not null
5571 --
5572 hr_api.mandatory_arg_error
5573 (p_api_name => l_proc
5574 ,p_argument => 'person_id'
5575 ,p_argument_value => p_person_id);
5576 --
5577 OPEN csr_leg_code;
5578 FETCH csr_leg_code INTO l_legislation_code;
5579 --
5580 IF csr_leg_code%notfound THEN
5581 --
5582 -- The primary key is invalid therefore we must error
5583 --
5584 CLOSE csr_leg_code;
5585 --
5586 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
5587 fnd_message.raise_error;
5588 --
5589 END IF;
5590 --
5591 hr_utility.set_location(l_proc,20);
5592 --
5593 CLOSE csr_leg_code;
5594 --
5595 hr_utility.set_location(' Leaving:'|| l_proc, 999);
5596 --
5597 RETURN l_legislation_code;
5598 --
5599 END return_legislation_code;
5600 --
5601 END per_cancel_hire_or_apl_pkg;