[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.8.12010000.7 2008/09/29 05:19:08 ubhat ship $ */
3 --
4 -- Package Variables
5 --
6 g_package VARCHAR2(33) := 'per_cancel_hire_or_apl_pkg.';
7 --
8 /*===========================================================================*
9 | |
10 | LOCK_CWK_ROWS |
11 | |
12 *============================================================================*/
13 --
14 PROCEDURE lock_cwk_rows
15 (p_person_id IN per_all_people_f.person_id%TYPE
16 ,p_business_group_id IN per_all_people_f.business_group_id%TYPE
17 ,p_effective_date IN DATE) IS
18 --
19 l_rowid VARCHAR2(18);
20 --
21 -- Comments cursor
22 --
23 cursor comments is
24 select h.rowid
25 from hr_comments h
26 , per_assignments_f paf
27 where h.comment_id = paf.comment_id
28 and paf.business_group_id + 0 = p_business_group_id
29 and paf.person_id = p_person_id
30 and paf.effective_start_date >= p_effective_date
31 for update of h.comment_id;
32 --
33 -- payment cursor
34 --
35 cursor payment is
36 select rowid
37 from pay_personal_payment_methods ppm
38 where ppm.business_group_id = p_business_group_id
39 and exists (select 'exists'
40 from per_all_assignments_f paf
41 where paf.business_group_id +0= p_business_group_id
42 and paf.person_id = p_person_id
43 and paf.assignment_id = ppm.assignment_id
44 --start bug 5987416
45 --and ppm.effective_start_date>= p_effective_date
46 --end bug 5987416
47 )
48 and ppm.effective_start_date >= p_effective_date
49 for update of ppm.assignment_id;
50 --
51 -- Budget values cursor
52 --
53 cursor budget_values is
54 select pab.rowid
55 from per_assignment_budget_values_f pab
56 , per_assignments_f paf
57 where pab.business_group_id + 0 = paf.business_group_id + 0
58 and paf.business_group_id + 0 = p_business_group_id
59 and pab.assignment_id = paf.assignment_id
60 and paf.person_id = p_person_id
61 and paf.effective_end_date >=p_effective_date
62 and pab.effective_end_date >=p_effective_date
63 for update of pab.assignment_id;
64 --
65 -- recruiter cursor
66 --
67 cursor recruiter is
68 select rowid
69 from per_assignments_f p
70 where p.recruiter_id = p_person_id
71 and (p.business_group_id = p_business_group_id OR
72 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
73 for update of assignment_id;
74 --
75 -- events cursor
76 --
77 cursor events_or_interviews is
78 select pb.rowid
79 from per_events pe
80 , per_bookings pb
81 where pe.business_group_id = pb.business_group_id
82 and (pb.business_group_id = p_business_group_id OR
83 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
84 and pe.event_id = pb.event_id
85 and pe.event_or_interview in ('I','E')
86 and pb.person_id = p_person_id
87 for update of pb.event_id;
88 --
89 -- vacancies cursor
90 --
91 cursor vacancies is
92 select rowid
93 from per_vacancies pv
94 where (pv.business_group_id = p_business_group_id OR
95 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
96 and pv.recruiter_id = p_person_id
97 and pv.date_from >= p_effective_date
98 for update of pv.vacancy_id;
99 --
100 -- requisitions cursor
101 --
102 cursor requisitions is
103 select rowid
104 from per_requisitions pr
105 where (pr.business_group_id = p_business_group_id OR
106 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
107 and pr.person_id = p_person_id
108 for update of person_id;
109 --
110 cursor supervisor is
111 select rowid
112 from per_assignments_f p
113 where (p.business_group_id = p_business_group_id OR
114 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
115 and p.supervisor_id = p_person_id
116 for update of assignment_id;
117 --
118 cursor per_rows is
119 select ppf.rowid
120 from per_people_f ppf
121 where ppf.person_id = p_person_id
122 for update of ppf.person_id;
123 --
124 --CWK assignments cursor
125 --
126 cursor cwk_ass is
127 select paf.rowid
128 from per_assignments_f paf
129 where paf.business_group_id + 0 = p_business_group_id
130 and paf.person_id = p_person_id
131 and paf.assignment_type = 'C'
132 for update of paf.assignment_id;
133 --
134 -- CWK periofs of placement
135 --
136 cursor csr_periods_of_placement is
137 select pp.rowid
138 from per_periods_of_placement pp
139 where pp.person_id = p_person_id
140 for update of person_id;
141 --
142 cursor csr_assignment_rate_values is
143 select pgr.rowid
144 from pay_grade_rules_f pgr
145 where exists (select 'x'
146 from per_assignments_f paf
147 where pgr.grade_or_spinal_point_id = paf.assignment_id
148 and paf.business_group_id + 0 = p_business_group_id
149 and paf.person_id = p_person_id)
150 for update of pgr.grade_or_spinal_point_id;
151 --
152 cursor csr_grade_steps is
153 select spp.rowid
154 from per_spinal_point_placements_f spp
155 where spp.business_group_id = p_business_group_id and
156 exists (select 'x'
157 from per_assignments_f paf
158 where spp.assignment_id = paf.assignment_id
159 and paf.business_group_id + 0 = p_business_group_id
160 and paf.person_id = p_person_id)
161 for update of spp.assignment_id;
162 --
163 cursor csr_cost_allocations is
164 select pca.rowid
165 from pay_cost_allocations_f pca
166 where exists (select 'x'
167 from per_assignments_f paf
168 where pca.assignment_id = paf.assignment_id
169 and paf.business_group_id + 0 = p_business_group_id
170 and paf.person_id = p_person_id)
171 for update of pca.assignment_id;
172 --
173 BEGIN
174 --
175 open per_rows;
176 <<person>>
177 loop
178 fetch per_rows into l_rowid;
179 exit when per_rows%notfound;
180 end loop person;
181 close per_rows;
182 --
183 open csr_cost_allocations;
184 <<cost_allocations>>
185 loop
186 fetch csr_cost_allocations into l_rowid;
187 exit when csr_cost_allocations%NOTFOUND;
188 end loop cost_allocations;
189 close csr_cost_allocations;
190 --
191 open csr_grade_steps;
192 <<grade_steps>>
193 loop
194 fetch csr_grade_steps into l_rowid;
195 exit when csr_grade_steps%NOTFOUND;
196 end loop grade_steps;
197 close csr_grade_steps;
198 --
199 open csr_periods_of_placement;
200 <<placements>>
201 loop
202 fetch csr_periods_of_placement into l_rowid;
203 exit when csr_periods_of_placement%NOTFOUND;
204 end loop placements;
205 close csr_periods_of_placement;
206 --
207 open csr_assignment_rate_values;
208 <<assignment_rates>>
209 loop
210 fetch csr_assignment_rate_values into l_rowid;
211 exit when csr_assignment_rate_values%NOTFOUND;
212 end loop assignment_rates;
213 --
214 open supervisor;
215 <<super>>
216 loop
217 fetch supervisor into l_rowid;
218 exit when supervisor%NOTFOUND;
219 end loop super;
220 close supervisor;
221 --
222 open recruiter;
223 <<recr>>
224 loop
225 fetch recruiter into l_rowid;
226 exit when recruiter%NOTFOUND;
227 end loop recr;
228 close recruiter;
229 --
230 open events_or_interviews;
231 <<event>>
232 loop
233 fetch events_or_interviews into l_rowid;
234 exit when events_or_interviews%NOTFOUND;
235 end loop event;
236 close events_or_interviews;
237 --
238 open vacancies;
239 <<vacancy>>
240 loop
241 fetch vacancies into l_rowid;
242 exit when vacancies%NOTFOUND;
243 end loop vacancy;
244 close vacancies;
245 --
246 open requisitions;
247 <<req>>
248 loop
249 fetch requisitions into l_rowid;
250 exit when requisitions%NOTFOUND;
251 end loop req;
252 close requisitions;
253 --
254 open budget_values;
255 <<budget_val>>
256 loop
257 fetch budget_values into l_rowid;
258 exit when budget_values%NOTFOUND;
259 end loop budget_val;
260 close budget_values;
261 --
262 open payment;
263 <<paym>>
264 loop
265 fetch payment into l_rowid;
266 exit when payment%NOTFOUND;
267 end loop pay;
268 close payment;
269 --
270 open comments;
271 <<comment>>
272 loop
273 fetch comments into l_rowid;
274 exit when comments%NOTFOUND;
275 end loop comment;
276 close comments;
277 --
278 open cwk_ass;
279 <<placements>>
280 loop
281 fetch cwk_ass into l_rowid;
282 exit when cwk_ass%NOTFOUND;
283 end loop placements;
284 close cwk_ass;
285 --
286 END lock_cwk_rows;
287 --
288 procedure lock_per_rows(p_person_id NUMBER,
289 p_primary_id NUMBER,
290 p_primary_date DATE,
291 p_business_group_id NUMBER,
292 p_person_type VARCHAR2)is
293 l_rowid VARCHAR2(18);
294 l_assignment_id NUMBER;
295 --
296 -- Person cursor
297 --
298 cursor per_rows is
299 select ppf.rowid
300 from per_people_f ppf
301 where ppf.person_id = p_person_id
302 for update of ppf.person_id;
303 --
304 -- Period cursor
305 --
306 cursor period_rows is
307 select pps.rowid
308 from per_periods_of_service pps
309 where pps.person_id = p_person_id
310 for update of pps.person_id;
311 --
312 -- applicant cursor
313 --
314 cursor applicant_rows is
315 select pap.rowid
316 from per_applications pap
317 where pap.person_id = p_person_id
318 for update of pap.person_id;
319 --
320 -- supervisor cursor.
321 --
322 cursor supervisor is
323 select rowid
324 from per_assignments_f p
325 where (p.business_group_id = p_business_group_id OR
326 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
327 and p.supervisor_id = p_person_id
328 for update of assignment_id;
329 --
330 -- recruiter cursor
331 --
332 cursor recruiter is
333 select rowid
334 from per_assignments_f p
335 where p.recruiter_id = p_person_id
336 and (p.business_group_id = p_business_group_id OR
337 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
338 for update of assignment_id;
339 --
340 -- events cursor
341 --
342 cursor events_or_interviews(p_type varchar2) is
343 select pb.rowid
344 from per_events pe
345 , per_bookings pb
346 where pe.business_group_id = pb.business_group_id
347 and (pb.business_group_id = p_business_group_id OR
348 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
349 and pe.event_id = pb.event_id
350 and pe.event_or_interview = p_type
351 and pb.person_id = p_person_id
352 for update of pb.event_id;
353 --
354 -- vacancies cursor
355 --
356 cursor vacancies is
357 select rowid
358 from per_vacancies pv
359 where (pv.business_group_id = p_business_group_id OR
360 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
361 and pv.recruiter_id = p_person_id
362 and pv.date_from >= p_primary_date
363 for update of pv.vacancy_id;
364 --
365 -- requisitions cursor
366 --
367 cursor requisitions is
368 select rowid
369 from per_requisitions pr
370 where (pr.business_group_id = p_business_group_id OR
371 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
372 and pr.person_id = p_person_id
373 for update of person_id;
374 --
375 -- absenses cursor
376 --
377 cursor absenses is
378 select rowid
379 from per_absence_attendances paa
380 where paa.business_group_id +0 = p_business_group_id
381 and paa.person_id = p_person_id
382 and paa.date_start >= p_primary_date
383 for update of person_id;
384 --
385 -- payment cursor
386 --
387 cursor payment is
388 select rowid
389 from pay_personal_payment_methods ppm
390 where ppm.business_group_id = p_business_group_id
391 and exists (select 'exists'
392 from per_all_assignments_f paf
393 where paf.business_group_id +0= p_business_group_id
394 and paf.person_id = p_person_id
395 and paf.assignment_id = ppm.assignment_id
396 and paf.period_of_service_id = p_primary_id
397 --start bug 5987416
398 --and ppm.effective_start_date>= p_primary_date
399 --end bug 5987416
400 )
401 and ppm.effective_start_date >= p_primary_date
402 for update of ppm.assignment_id;
403 --
404 -- Budget values cursor
405 --
406 cursor budget_values is
407 select pab.rowid
408 from per_assignment_budget_values_f pab
409 , per_assignments_f paf
410 where pab.business_group_id + 0 = paf.business_group_id + 0
411 and paf.business_group_id + 0 = p_business_group_id
412 and pab.assignment_id = paf.assignment_id
413 and paf.person_id = p_person_id
414 and paf.effective_end_date >=p_primary_date
415 and pab.effective_end_date >=p_primary_date
416 for update of pab.assignment_id;
417 --
418 -- letters cursor
419 --
420 cursor letters is
421 select p.rowid
422 from per_letter_request_lines p
423 , per_assignments_f paf
424 where p.assignment_id = paf.assignment_id
425 and paf.business_group_id + 0 = p_business_group_id
426 and paf.person_id = p_person_id
427 and paf.application_id = p_primary_id
428 and paf.effective_end_date >=p_primary_date
429 for update of p.assignment_id;
430 --
431 -- Comments cursor
432 --
433 cursor comments is
434 select h.rowid
435 from hr_comments h
436 , per_assignments_f paf
437 where h.comment_id = paf.comment_id
438 and paf.business_group_id + 0 = p_business_group_id
439 and paf.person_id = p_person_id
440 and paf.application_id = p_primary_id
441 and paf.effective_end_date >=p_primary_date
442 for update of h.comment_id;
443 --
444 --appl assignments cursor
445 --
446 cursor appl_ass is
447 select paf.rowid
448 from per_assignments_f paf
449 where paf.business_group_id + 0 = p_business_group_id
450 and paf.person_id = p_person_id
451 and paf.assignment_type = 'A'
452 and paf.application_id = p_primary_id
453 for update of paf.assignment_id;
454 --
455 --emp assignments cursor
456 --
457 cursor emp_ass is
458 select paf.rowid
459 from per_assignments_f paf
460 where paf.business_group_id + 0 = p_business_group_id
461 and paf.person_id = p_person_id
462 and paf.assignment_type = 'E'
463 and paf.application_id = p_primary_id
464 for update of paf.assignment_id;
465
466 begin
467 -- Person loop
468 open per_rows;
469 <<person>>
470 loop
471 fetch per_rows into l_rowid;
472 exit when per_rows%notfound;
473 end loop person;
474 close per_rows;
475 --
476 if p_person_type = 'EMP' then
477 --
478 open period_rows;
479 <<period>>
480 loop
481 fetch period_rows into l_rowid;
482 exit when period_rows%NOTFOUND;
483 end loop period;
484 close period_rows;
485 --
486 open supervisor;
487 <<super>>
488 loop
489 fetch supervisor into l_rowid;
490 exit when supervisor%NOTFOUND;
491 end loop super;
492 close supervisor;
493 --
494 open recruiter;
495 <<recr>>
496 loop
497 fetch recruiter into l_rowid;
498 exit when recruiter%NOTFOUND;
499 end loop recr;
500 close recruiter;
501 --
502 open events_or_interviews('E');
503 <<event>>
504 loop
505 fetch events_or_interviews into l_rowid;
506 exit when events_or_interviews%NOTFOUND;
507 end loop event;
508 close events_or_interviews;
509 --
510 open vacancies;
511 <<vacancy>>
512 loop
513 fetch vacancies into l_rowid;
514 exit when vacancies%NOTFOUND;
515 end loop vacancy;
516 close vacancies;
517 --
518 open requisitions;
519 <<req>>
520 loop
521 fetch requisitions into l_rowid;
522 exit when requisitions%NOTFOUND;
523 end loop req;
524 close requisitions;
525 --
526 open absenses;
527 <<absences>>
528 loop
529 fetch absenses into l_rowid;
530 exit when absenses%NOTFOUND;
531 end loop absences;
532 close absenses;
533 --
534 open budget_values;
535 <<budget_val>>
536 loop
537 fetch budget_values into l_rowid;
538 exit when budget_values%NOTFOUND;
539 end loop budget_val;
540 close budget_values;
541 --
542 open payment;
543 <<paym>>
544 loop
545 fetch payment into l_rowid;
546 exit when payment%NOTFOUND;
547 end loop pay;
548 close payment;
549 --
550 open comments;
551 <<comment>>
552 loop
553 fetch comments into l_rowid;
554 exit when comments%NOTFOUND;
555 end loop comment;
556 close comments;
557 --
558 open emp_ass;
559 <<assignments>>
560 loop
561 fetch emp_ass into l_rowid;
562 exit when emp_ass%NOTFOUND;
563 end loop assignments;
564 close emp_ass;
565 --
566 open appl_ass;
567 <<applications>>
568 loop
569 fetch appl_ass into l_rowid;
570 exit when appl_ass%NOTFOUND;
571 end loop applications;
572 close appl_ass;
573 --
574 elsif p_person_type='APL' then
575 --
576 open applicant_rows;
577 <<appl>>
578 loop
579 fetch applicant_rows into l_rowid;
580 exit when applicant_rows%NOTFOUND;
581 end loop appl;
582 close applicant_rows;
583 -- lock assignment rows etc
584 open appl_ass;
585 <<ass>>
586 loop
587 fetch appl_ass into l_rowid;
588 exit when appl_ass%notfound;
589 end loop ass;
590 close appl_ass;
591 -- lock comments rows
592 open comments;
593 <<comment>>
594 loop
595 fetch comments into l_rowid;
596 exit when comments%NOTFOUND;
597 end loop comment;
598 close comments;
599 -- lock letters
600 open letters;
601 <<letter>>
602 loop
603 fetch letters into l_rowid;
604 exit when letters%NOTFOUND;
605 end loop letter;
606 close letters;
607 -- lock budgets
608 open budget_values;
609 <<budget>>
610 loop
611 fetch budget_values into l_rowid;
612 exit when budget_values%NOTFOUND;
613 end loop budget;
614 close budget_values;
615 -- lock events
616 open events_or_interviews('E');
617 <<event>>
618 loop
619 fetch events_or_interviews into l_rowid;
620 exit when events_or_interviews%NOTFOUND;
621 end loop event;
622 close events_or_interviews;
623 -- lock interview
624 open events_or_interviews('I');
625 <<interview>>
626 loop
627 fetch events_or_interviews into l_rowid;
628 exit when events_or_interviews%NOTFOUND;
629 end loop interview;
630 close events_or_interviews;
631 --
632 end if;
633 end lock_per_rows;
634 --
635 --
636 --
637 procedure pre_cancel_checks(p_person_id NUMBER
638 ,p_where IN OUT NOCOPY VARCHAR2
639 ,p_business_group_id NUMBER
640 ,p_system_person_type VARCHAR2
641 ,p_primary_id NUMBER
642 ,p_primary_date DATE
643 ,p_cancel_type VARCHAR2) is
644 --
645 -- Bug 2964027 starts here.
646 -- Cursor to seelct assignment actions on or after the hire date.
647 --
648 cursor csr_assign_actions_exist is
649 select 'Y'
650 from per_all_assignments_f a
651 where a.person_id = p_person_id
652 --
653 -- 115.51 (START)
654 --
655 AND a.period_of_service_id = p_primary_id
656 --
657 -- 115.51 (END)
658 --
659 AND ((a.effective_start_date = p_primary_date
660 and a.primary_flag <> 'Y'
661 and not exists ( select b.assignment_id
662 from per_all_assignments_f b
663 where nvl(b.effective_end_date,hr_api.g_eot)
664 = (p_primary_date-1)
665 and b.assignment_id = a.assignment_id) )
666 OR a.effective_start_date > p_primary_date );
667 -- Bug 2964027 ends here.
668 --
669 -- Supervisor
670 --
671 cursor supervisor is
672 select rowid
673 from per_assignments_f p
674 where (p.business_group_id = p_business_group_id OR
675 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
676 and p.supervisor_id = p_person_id;
677 --
678 -- recruiter cursor
679 --
680 cursor recruiter is
681 select rowid
682 from per_assignments_f p
683 where p.recruiter_id = p_person_id
684 and (p.business_group_id = p_business_group_id OR
685 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y');
686 --
687 -- reviews or Events cursor
688 --
689 cursor reviews_or_events(p_type varchar2) is
690 select 'Events exist'
691 from per_events pe
692 , per_bookings pb
693 where pe.business_group_id = pb.business_group_id
694 and (pb.business_group_id = p_business_group_id OR
695 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
696 and pe.event_id = pb.event_id
697 and pe.event_or_interview = p_type
698 and pb.person_id = p_person_id;
699 --
700 -- Interviews cursor
701 --
702 cursor interviews is
703 select 'Interviews exist'
704 from per_events pe
705 where (pe.business_group_id = p_business_group_id OR
706 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
707 and pe.event_or_interview = 'I'
708 and pe.internal_contact_person_id = p_person_id;
709 --
710 -- vacancies cursor
711 --
712 cursor vacancy is
713 select rowid
714 from per_vacancies pv
715 where (pv.business_group_id = p_business_group_id OR
716 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
717 and pv.recruiter_id = p_person_id
718 and pv.date_from >= p_primary_date;
719 --
720 -- requisitions cursor
721 --
722 cursor requisition is
723 select rowid
724 from per_requisitions pr
725 where (pr.business_group_id = p_business_group_id OR
726 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
727 and pr.person_id = p_person_id;
728 --
729 -- budget_values cursor
730 --
731 cursor budget_values is
732 select rowid
733 from per_assignment_budget_values_f pab
734 where pab.business_group_id = p_business_group_id and
735 exists (select 'budget_values exist'
736 from per_all_assignments_f paf
737 where pab.business_group_id +0= paf.business_group_id + 0
738 and paf.business_group_id +0= p_business_group_id
739 and pab.assignment_id = paf.assignment_id
740 and paf.person_id = p_person_id
741 and paf.period_of_service_id = p_primary_id
742 and paf.effective_end_date >= p_primary_date
743 --start bug 5987416
744 --and pab.effective_end_date >= p_primary_date
745 )
746 AND pab.effective_end_date >= p_primary_date;
747 --end bug 5987416
748 --
749 -- payment cursor
750 --
751 cursor payment is
752 select rowid
753 from pay_personal_payment_methods ppm
754 where ppm.business_group_id = p_business_group_id
755 and exists (select 'exists'
756 from per_all_assignments_f paf
757 where paf.business_group_id +0= p_business_group_id
758 and paf.person_id = p_person_id
759 and paf.assignment_id = ppm.assignment_id
760 and paf.period_of_service_id = p_primary_id
761 --start bug 5987416
762 --and ppm.effective_start_date>= p_primary_date
763 --end bug 5987416
764 )
765 and ppm.effective_start_date >= p_primary_date;
766 --
767 -- pay actions cursor. Start Bug 2841901
768 --
769 cursor csr_payactions is
770 SELECT null
771 FROM pay_payroll_actions pac,
772 pay_assignment_actions act,
773 per_assignments_f asg
774 WHERE asg.person_id = p_person_id
775 --
776 -- 115.51 (START)
777 --
778 AND asg.period_of_service_id = p_primary_id
779 --
780 -- 115.51 (END)
781 --
782 AND act.assignment_id = asg.assignment_id
783 AND pac.payroll_action_id = act.payroll_action_id
784 --
785 --Start Bug 4724223
786 --
787 AND pac.action_type NOT IN ('X','BEE')
788 AND p_primary_date BETWEEN asg.effective_start_date
789 AND asg.effective_end_date
790 --
791 --End Bug 4724223
792 --
793 AND pac.effective_date >= p_primary_date;
794 --
795 --End Bug 2841901
796 --
797 -- Start Bug 3285486
798 CURSOR csr_get_ptu_id(p_system_person_type varchar2) IS
799 SELECT ptu.person_type_usage_id
800 FROM per_person_types pt,
801 per_person_type_usages_f ptu
802 WHERE pt.business_group_id = p_business_group_id
803 AND pt.person_type_id = ptu.person_type_id
804 AND p_primary_date BETWEEN ptu.effective_start_date
805 AND ptu.effective_end_date
806 AND ptu.person_id = p_person_id
807 AND pt.system_person_type = p_system_person_type;
808
809 l_person_type_usage_id NUMBER;
810 -- End bug 3285486
811
812 l_dummy VARCHAR2(30);
813
814 --
815 begin
816 -- Start Bug 3285486
817 OPEN csr_get_ptu_id(p_system_person_type);
818 FETCH csr_get_ptu_id INTO l_person_type_usage_id;
819
820 IF csr_get_ptu_id%NOTFOUND THEN
821 --
822 CLOSE csr_get_ptu_id;
823 --
824 -- # 3690364 - changed application_id from 801 to 800
825 hr_utility.set_message(800,'HR_289548_PEM_EMP_PERSON_ID');
826 hr_utility.raise_error;
827 --
828 END IF;
829 CLOSE csr_get_ptu_id;
830 -- end Bug 3285486
831 if p_cancel_type = 'HIRE' then
832 if p_where = 'BEGIN' then
833 --
834
835 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',1);
836 --
837 -- Start Bug 3285486. commented the call to hr_person.chk_future_person_type
838 -- added the new call to hr_person_type_usage_info.FutSysPerTypeChgExists
839 /* if hr_person.chk_future_person_type(p_system_person_type
840 ,p_person_id
841 ,p_business_group_id
842 ,p_primary_date) then*/
843 IF hr_person_type_usage_info.FutSysPerTypeChgExists
844 (p_person_type_usage_id => l_person_type_usage_id
845 ,p_effective_date => p_primary_date
846 ,p_person_id => p_person_id ) THEN
847 -- End Bug 3285486.
848 --
849 hr_utility.set_message(801,'HR_7078_EMP_ENTER_CANCEL_TYPE');
850 hr_utility.raise_error;
851 --
852 end if;
853 --
854 -- BUG 2964027 STARTS HERE.
855 open csr_assign_actions_exist;
856 --
857 fetch csr_assign_actions_exist into l_dummy;
858 --
859 if csr_assign_actions_exist%FOUND then
860 close csr_assign_actions_exist;
861 hr_utility.set_message(800,'PER_289566_ASG_ACTIONS_EXISTS');
862 hr_utility.raise_error;
863 end if;
864 --
865 close csr_assign_actions_exist;
866 -- BUG 2964027 ENDS HERE.
867 --
868 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',2);
869 if not hr_person.chk_prev_person_type(p_system_person_type
870 ,p_person_id
871 ,p_business_group_id
872 ,p_primary_date) then
873 --
874 hr_utility.set_message(801,'HR_7077_NO_CANCEL_HIRE');
875 hr_utility.raise_error;
876 --
877 end if;
878
879 -- check for pay actions.Start Bug 2841901
880
881 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',3);
882 open csr_payactions;
883 fetch csr_payactions into l_dummy;
884 --
885 if csr_payactions%found then
886 close csr_payactions;
887 --
888 -- Bug# 2989638 Start Here
889 -- Description : Added new message to display proper error message
890 --
891 --
892 hr_utility.set_message(800,'HR_289529_EMP_FUT_PAY_EXIST');
893 --
894 -- Bug# 2989638 End Here
895 --
896 hr_utility.raise_error;
897 end if;
898 --
899 close csr_payactions;
900 -- End Bug 2841901
901 --
902 p_where := 'SUPERVISOR';
903 --
904 end if;
905 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',4);
906 if p_where = 'SUPERVISOR' then
907 --
908 open supervisor;
909 --
910 fetch supervisor into l_dummy;
911 --
912 if supervisor%FOUND then
913 hr_utility.set_message(801,'HR_EMP_IS_SUPER');
914 close supervisor;
915 return;
916 else
917 close supervisor;
918 end if;
919 p_where:= 'RECRUITER';
920 end if;
921 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',5);
922 if p_where = 'RECRUITER' then
923 --
924 open recruiter;
925 --
926 fetch recruiter into l_dummy;
927 --
928 if recruiter%FOUND then
929 hr_utility.set_message(801,'HR_EMP_IS_RECRUITER');
930 close recruiter;
931 return;
932 else
933 close recruiter;
934 end if;
935 p_where:= 'EVENT';
936 end if;
937 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',6);
938 if p_where = 'EVENT' then
939 --
940 open reviews_or_events(p_type =>'E');
941 --
942 fetch reviews_or_events into l_dummy;
943 --
944 if reviews_or_events%FOUND then
945 hr_utility.set_message(801,'HR_EMP_HAS_EVENTS');
946 close reviews_or_events;
947 return;
948 else
949 close reviews_or_events;
950 end if;
951 --
952 p_where := 'INTERVIEW';
953 end if;
954 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',7);
955 if p_where = 'INTERVIEW' then
956 --
957 open interviews;
958 --
959 fetch interviews into l_dummy;
960 --
961 if interviews%FOUND then
962 hr_utility.set_message(801,'HR_EMP_IS_INTERVIEWER');
963 close interviews;
964 return;
965 else
966 close interviews;
967 end if;
968 p_where := 'REVIEW';
969 end if;
970 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',8);
971 if p_where = 'REVIEW' then
972 --
973 open reviews_or_events(p_type =>'I');
974 --
975 fetch reviews_or_events into l_dummy;
976 --
977 if reviews_or_events%FOUND then
978 hr_utility.set_message(801,'HR_EMP_DUE_REVIEW');
979 close reviews_or_events;
980 return;
981 else
982 close reviews_or_events;
983 end if;
984 p_where := 'VACANCY';
985 end if;
986 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',9);
987 if p_where = 'VACANCY' then
988 --
989 open vacancy;
990 --
991 fetch vacancy into l_dummy;
992 --
993 if vacancy%FOUND then
994 hr_utility.set_message(801,'HR_EMP_VAC_RECRUITER');
995 close vacancy;
996 return;
997 else
998 close vacancy;
999 end if;
1000 p_where:= 'REQUISITION';
1001 end if;
1002 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',10);
1003 if p_where = 'REQUISITION' then
1004 --
1005 open requisition;
1006 --
1007 fetch requisition into l_dummy;
1008 --
1009 if requisition%FOUND then
1010 hr_utility.set_message(801,'HR_EMP_REQUISITIONS');
1011 close requisition;
1012 return;
1013 else
1014 close requisition;
1015 end if;
1016 p_where:= 'BUDGET_VALUE';
1017 end if;
1018 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',11);
1019 if p_where = 'BUDGET_VALUE' then
1020 --
1021 open budget_values;
1022 --
1023 fetch budget_values into l_dummy;
1024 --
1025 if budget_values%FOUND then
1026 hr_utility.set_message(801,'HR_EMP_BUDGET_VALUES');
1027 close budget_values;
1028 return;
1029 else
1030 close budget_values;
1031 end if;
1032 p_where:= 'PAYMENT';
1033 end if;
1034 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',12);
1035 if p_where = 'PAYMENT' then
1036 --
1037 open payment;
1038 --
1039 fetch payment into l_dummy;
1040 --
1041 if payment%FOUND then
1042 hr_utility.set_message(801,'HR_EMP_PAYMENT_METHODS');
1043 close payment;
1044 return;
1045 else
1046 close payment;
1047 end if;
1048 p_where:= 'END';
1049 else
1050 app_exception.invalid_argument('cancel_hire',
1051 'P_WHERE',p_where);
1052 end if;
1053 --
1054 --
1055 --
1056 elsif p_cancel_type = 'APL' then
1057 --
1058 if p_where = 'BEGIN' then
1059 --
1060 hr_utility.set_location('APP1.B_PRE_DEL_CHECK',1);
1061 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',13);
1062 --
1063 -- Start Bug 3285486. commented the call to hr_person.chk_future_person_type
1064 -- added the new call to hr_person_type_usage_info.FutSysPerTypeChgExists
1065 --
1066 /* if hr_person.chk_future_person_type(p_system_person_type
1067 ,p_person_id
1068 ,p_business_group_id
1069 ,p_primary_date) then*/
1070 IF hr_person_type_usage_info.FutSysPerTypeChgExists
1071 (p_person_type_usage_id => l_person_type_usage_id
1072 ,p_effective_date => p_primary_date
1073 ,p_person_id => p_person_id ) THEN
1074 --
1075 -- End Bug 3285486
1076 --
1077 hr_utility.set_message(800,'HR_7080_ALL_APP_NO_CANCEL');
1078 hr_utility.raise_error;
1079 --
1080 end if;
1081 --
1082 hr_utility.set_location('APP1.B_PRE_DEL_CHECK',2);
1083 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',14);
1084 --
1085 if not hr_person.chk_prev_person_type(p_system_person_type
1086 ,p_person_id
1087 ,p_business_group_id
1088 ,p_primary_date) then
1089 --
1090 hr_utility.set_message(800,'HR_7081_ALL_APP_NO_CANCEL');
1091 hr_utility.raise_error;
1092 --
1093 end if;
1094 --
1095 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',15);
1096
1097 else
1098 app_exception.invalid_argument('cancel_apl',
1099 'P_WHERE',p_where);
1100 end if;
1101 else
1102 app_exception.invalid_argument('cancel_hire_or_apl',
1103 'P_CANCEL_TYPE',p_cancel_type);
1104 end if;
1105 hr_utility.set_location('cancel_hire_or_apl.pre_cancel_checks',16);
1106 exception
1107 when hr_utility.hr_error then
1108 raise;
1109 when others then
1110 hr_utility.oracle_error(sqlcode);
1111 hr_utility.raise_error;
1112 end pre_cancel_checks;
1113 --
1114
1115 /*===========================================================================*
1116 | |
1117 | upd_person_type_usage_end_date |
1118 | |
1119 *============================================================================*/
1120 /*Procedure to update the end date person type OTHER
1121 when cancel placement is done.Added for the bug 6460093*/
1122
1123 procedure upd_person_type_usage_end_date
1124 (
1125 p_effective_date in date
1126 ,p_person_id in number
1127 ,p_system_person_type in varchar2
1128
1129
1130 ) is
1131
1132 cursor csr_upded_person_type_usages
1133 (
1134 p_effective_date in date
1135 ,p_person_id in number
1136 ,p_system_person_type in varchar2
1137 ) is
1138 select ptu.person_type_usage_id
1139 ,ptu.object_version_number
1140 from per_person_type_usages_f ptu
1141 where p_effective_date between ptu.effective_start_date and ptu.effective_end_date
1142 and ptu.person_id = p_person_id
1143 and ptu.person_type_id in
1144 (select ppt.person_type_id
1145 from per_person_types ppt
1146 where (( p_system_person_type = 'OTHER'
1147 and ppt.system_person_type = 'OTHER' )));
1148
1149 l_csr_upd_per_type_usages csr_upded_person_type_usages%rowtype;
1150
1151 l_effective_end_date date := hr_general.end_of_time;
1152 begin
1153
1154 hr_utility.set_location('Entering Upd_Person_Type_Usage_End_Date',491);
1155
1156 open csr_upded_person_type_usages(
1157 p_person_id => p_person_id,
1158 p_effective_date => p_effective_date,
1159 p_system_person_type => p_system_person_type);
1160
1161 fetch csr_upded_person_type_usages into l_csr_upd_per_type_usages;
1162 if csr_upded_person_type_usages%found then
1163
1164 hr_utility.set_location('Entering Upd_Person_Type_Usage_End_Date',492);
1165
1166 update per_person_type_usages_f ptu
1167 set effective_end_date = l_effective_end_date
1168 where ptu.effective_end_date = p_effective_date
1169 and ptu.person_id = p_person_id
1170 and ptu.person_type_usage_id = l_csr_upd_per_type_usages.person_type_usage_id
1171 and ptu.object_version_number = l_csr_upd_per_type_usages.object_version_number;
1172
1173 end if;
1174 close csr_upded_person_type_usages;
1175
1176 hr_utility.set_location('Leaving Upd_Person_Type_Usage_End_Date',493);
1177 end upd_person_type_usage_end_date;
1178
1179 /*Procedure Added for the bug 6460093*/
1180
1181
1182 procedure do_cancel_hire(p_person_id NUMBER
1183 ,p_date_start DATE
1184 ,p_end_of_time DATE
1185 ,p_business_group_id NUMBER
1186 ,p_period_of_service_id NUMBER) is
1187 --
1188 p_assignment_id NUMBER;
1189 p_start_date DATE;
1190 p_rowid ROWID;
1191 l_period_of_service_id NUMBER;
1192 -- VT #438579 03/05/97
1193 l_assignment_id NUMBER;
1194 l_back2back BOOLEAN;
1195 l_person_type_id NUMBER;
1196 -- Start of fix 3564129
1197 l_asg_status_id irc_assignment_statuses.assignment_status_id%type;
1198 l_asg_status_ovn irc_assignment_statuses.object_version_number%type;
1199 l_asg_status_type_id per_all_assignments_f.assignment_status_type_id%type;
1200 -- End of fix 3564129
1201 --
1202 -- supervisor cursor.
1203 --
1204 cursor supervisor1 is
1205 select rowid
1206 from per_assignments_f p
1207 where (p.business_group_id = p_business_group_id OR
1208 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
1209 and p.supervisor_id = p_person_id;
1210 --
1211 -- recruiter cursor
1212 --
1213 cursor recruiter1 is
1214 select rowid
1215 from per_assignments_f p
1216 where p.recruiter_id = p_person_id
1217 and (p.business_group_id = p_business_group_id OR
1218 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y');
1219 --
1220 -- events cursor
1221 --
1222 cursor events is
1223 select rowid
1224 from per_bookings pb
1225 where (pb.business_group_id = p_business_group_id OR
1226 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
1227 and pb.person_id = p_person_id
1228 and exists ( select 'row exists'
1229 from per_events pe
1230 where pe.business_group_id + 0 = p_business_group_id
1231 and pe.event_id = pb.event_id
1232 and pe.event_or_interview in ('I','E')
1233 and pe.date_start >= p_date_start);
1234 --
1235 -- vacancies cursor
1236 --
1237 cursor vacancies is
1238 select rowid
1239 from per_vacancies pv
1240 where (pv.business_group_id = p_business_group_id OR
1241 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
1242 and pv.recruiter_id = p_person_id
1243 and pv.date_from >= p_date_start;
1244 --
1245 -- requisitions cursor
1246 --
1247 cursor requisitions1 is
1248 select rowid
1249 from per_requisitions pr
1250 where (pr.business_group_id = p_business_group_id OR
1251 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
1252 and pr.person_id = p_person_id;
1253 --
1254 -- absenses cursor
1255 --
1256 cursor absenses1 is
1257 select rowid
1258 from per_absence_attendances paa
1259 where paa.business_group_id +0 = p_business_group_id
1260 and paa.person_id = p_person_id
1261 and paa.date_start >= p_date_start;
1262 --
1263 -- budget_values cursor
1264 --
1265 cursor budget_values1 is
1266 select rowid
1267 from per_assignment_budget_values_f pab
1268 where pab.business_group_id = p_business_group_id and
1269 exists (select 'budget_values exist'
1270 from per_all_assignments_f paf
1271 where pab.business_group_id +0= paf.business_group_id + 0
1272 and paf.business_group_id +0= p_business_group_id
1273 and pab.assignment_id = paf.assignment_id
1274 and paf.person_id = p_person_id
1275 and paf.period_of_service_id = l_period_of_service_id
1276 and paf.effective_end_date >= p_date_start
1277 --start bug 5987416
1278 --and pab.effective_end_date >= p_date_start
1279 )
1280 and pab.effective_end_date >= p_date_start;
1281 --end bug 5987416
1282 --
1283 -- payment cursor
1284 --
1285 cursor payment1 is
1286 select rowid
1287 from pay_personal_payment_methods ppm
1288 where ppm.business_group_id = p_business_group_id
1289 and exists (select 'exists'
1290 from per_all_assignments_f paf
1291 where paf.business_group_id +0= p_business_group_id
1292 and paf.person_id = p_person_id
1293 and paf.assignment_id = ppm.assignment_id
1294 and paf.period_of_service_id = l_period_of_service_id
1295 --start bug 5987416
1296 -- and ppm.effective_start_date>= p_date_start
1297 --end bug 5987416
1298 )
1299 and ppm.effective_start_date >= p_date_start;
1300 --
1301 -- comments cursor
1302 --
1303 cursor comments1 is
1304 select rowid
1305 from hr_comments h
1306 where exists (select 'comments exist'
1307 from per_all_assignments_f paf
1308 where h.comment_id = paf.comment_id
1309 and paf.business_group_id +0= p_business_group_id
1310 and paf.person_id = p_person_id
1311 and paf.period_of_service_id = l_period_of_service_id);
1312 --
1313 -- assignments cursor
1314 --
1315 -- VT #438579 03/05/79 added assignment_id
1316 cursor assignments is
1317 select rowid, assignment_id, assignment_status_type_id -- Bug 3564129
1318 from per_all_assignments_f paf
1319 where paf.business_group_id +0= p_business_group_id
1320 and paf.person_id = p_person_id
1321 and paf.period_of_service_id = l_period_of_service_id;
1322 --
1323 -- Start of fix 3564129
1324 -- Cusror for iRecruitment assignment status
1325 cursor irc_asg_status is
1326 select iass.assignment_status_id, iass.object_version_number
1327 from irc_assignment_statuses iass
1328 where iass.assignment_status_id =
1329 (select max(iass.assignment_status_id)
1330 from irc_assignment_statuses iass
1331 where iass.assignment_id = l_assignment_id
1332 and iass.assignment_status_type_id = l_asg_status_type_id);
1333 -- End of fix 3564129
1334 --
1335 -- assignment cursor for entries update
1336 --
1337 --Added the assignment type condition of 'A' for fix of #3390818
1338 --
1339 cursor ass1 is
1340 select assignment_id,effective_start_date,ROWID
1341 from per_all_assignments_f
1342 where person_id = p_person_id
1343 and business_group_id +0 = p_business_group_id
1344 and period_of_service_id is NULL
1345 and assignment_type in ('E','A') -- 3194314
1346 and effective_end_date = p_date_start - 1
1347 for update of effective_end_date;
1348 --
1349 --
1350 cursor c_assignments is
1351 select paf.assignment_id, paf.effective_start_date
1352 from per_all_assignments_f paf
1353 where paf.person_id = p_person_id
1354 and paf.business_group_id +0 = p_business_group_id
1355 and assignment_type <> 'B'; --modified for bug #6449599
1356 --
1357 -- applications cursor
1358 --
1359 cursor applications is
1360 select rowid
1361 from per_applications pap
1362 where exists (select 'row exists'
1363 from per_all_assignments_f paf
1364 where paf.person_id = p_person_id
1365 and paf.business_group_id +0 = p_business_group_id
1366 and paf.period_of_service_id is NULL
1367 and paf.effective_end_date = p_date_start - 1
1368 and pap.application_id = paf.application_id);
1369 --
1370 -- period cursor
1371 --
1372 --
1373 -- Period cursor
1374 --
1375 cursor period is
1376 select pps.rowid
1377 from per_periods_of_service pps
1378 where pps.person_id = p_person_id
1379 and period_of_service_id = l_period_of_service_id;
1380 --
1381 -- person cursor
1382 --
1383 cursor person is
1384 select p.rowid, effective_end_date from per_people_f p
1385 where p.person_id = p_person_id
1386 and p.effective_start_date >= p_date_start;
1387 --
1388 -- 3194314
1389 --
1390 cursor csr_emp_ptu_id is
1391 select ptu.person_type_id
1392 from per_person_type_usages_f ptu
1393 ,per_person_types ppt
1394 where ptu.person_id = p_person_id
1395 and ptu.effective_start_date = p_date_start
1396 and ptu.person_type_id = ppt.person_type_id
1397 and ppt.system_person_type = 'EMP';
1398
1399 l_emp_ptu_id number;
1400
1401 --
1402 --3848352 start
1403 --
1404 cursor csr_apl_ptu_id is
1405 select ptu.person_type_id
1406 from per_person_type_usages_f ptu
1407 ,per_person_types ppt
1408 where ptu.person_id = p_person_id
1409 and ptu.effective_end_date = p_date_start - 1
1410 and ptu.person_type_id = ppt.person_type_id
1411 and ppt.system_person_type = 'APL';
1412
1413 l_apl_ptu_id number;
1414 l_dummy number;
1415 l_apl_flag varchar2(1);
1416 --
1417 --3848352 end
1418 --
1419 --
1420 cursor csr_is_cwk(cp_date_start date) is -- 3194314
1421 select pp.period_of_placement_id, pp.date_start
1422 from per_periods_of_placement pp
1423 where pp.person_id = p_person_id
1424 and pp.actual_termination_date = cp_date_start - 1;
1425
1426 l_pp_id number;
1427 l_cwk_date_start date;
1428
1429 -- <<
1430 l_person_end_date date; --#1998140
1431 --
1432 -- new_person
1433 --
1434 cursor new_person is
1435 select p.rowid from per_people_f p
1436 where p.person_id = p_person_id
1437 and p.effective_end_date = p_date_start -1;
1438
1439 -- fix for bug 5005157 starts here.
1440 cursor pay_proposals2(p_assignment_id NUMBER) is
1441 select ppp.pay_proposal_id, ppp.object_version_number
1442 from per_pay_proposals ppp
1443 where p_assignment_id = ppp.assignment_id;
1444
1445 cursor csr_get_salary(p_assignment_id NUMBER) is
1446 select element_entry_id
1447 from pay_element_entries_f
1448 where assignment_id = p_assignment_id
1449 and creator_type = 'SP'
1450 and p_start_date between
1451 effective_start_date and effective_end_date;
1452
1453
1454 cursor csr_chk_rec_exists(p_assignment_id NUMBER) is
1455 select element_entry_id
1456 from pay_element_entries_f
1457 where assignment_id = p_assignment_id
1458 and creator_type = 'SP'
1459 and (p_start_date - 1) between
1460 effective_start_date and effective_end_date;
1461
1462
1463 l_element_entry_id1 number;
1464 l_element_entry_id number;
1465 l_pk_id NUMBER;
1466 l_ovn NUMBER;
1467 l_business_group_id NUMBER;
1468 l_sal_warning BOOLEAN;
1469 -- fix for bug 5005157 ends here.
1470
1471
1472 --
1473 FUNCTION get_period_of_service (p_person_id IN INTEGER
1474 ,p_start_date IN DATE) RETURN INTEGER is
1475 --
1476 v_dummy INTEGER;
1477 --
1478 begin
1479 select pps.period_of_service_id
1480 into v_dummy
1481 from per_periods_of_service pps
1482 where p_start_date = pps.date_start
1483 and pps.person_id = p_person_id;
1484 --
1485 return v_dummy;
1486 exception
1487 when no_data_found then
1488 hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
1489 hr_utility.raise_error;
1490 end;
1491 begin
1492 --
1493 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',10);
1494 --
1495 l_business_group_id := p_business_group_id;-- fix for bug 5005157 .
1496 if p_period_of_service_id is null then
1497 l_period_of_service_id:= get_period_of_service(p_person_id => p_person_id
1498 ,p_start_date =>p_date_start);
1499 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',20);
1500 else
1501 l_period_of_service_id := p_period_of_service_id;
1502 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',30);
1503 end if;
1504 open supervisor1;
1505 <<supervisor>>
1506 loop
1507 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',40);
1508 fetch supervisor1 into p_rowid;
1509 exit when supervisor1%NOTFOUND;
1510 --
1511 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',50);
1512 update per_all_assignments_f paf
1513 set paf.supervisor_id = NULL
1514 where paf.rowid = p_rowid;
1515 --
1516 if sql%notfound then
1517 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1518 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1519 hr_utility.set_message_token('STEP',1);
1520 hr_utility.raise_error;
1521 end if;
1522 end loop supervisor;
1523 close supervisor1;
1524 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',60);
1525 open recruiter1;
1526 <<recruiter>>
1527 loop
1528 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',70);
1529 fetch recruiter1 into p_rowid;
1530 exit when recruiter1%NOTFOUND;
1531 --
1532 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',80);
1533 update per_all_assignments_f paf
1534 set paf.recruiter_id = NULL
1535 where paf.rowid = p_rowid;
1536 --
1537 if sql%notfound then
1538 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1539 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1540 hr_utility.set_message_token('STEP',2);
1541 hr_utility.raise_error;
1542 end if;
1543 end loop recruiter;
1544 close recruiter1;
1545 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',90);
1546 open events;
1547 <<event>>
1548 loop
1549 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',100);
1550 fetch events into p_rowid;
1551 exit when events%NOTFOUND;
1552 delete from per_bookings pb
1553 where pb.rowid = p_rowid;
1554 --
1555 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',110);
1556 if sql%notfound then
1557 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1558 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1559 hr_utility.set_message_token('STEP',3);
1560 hr_utility.raise_error;
1561 end if;
1562 end loop event;
1563 close events;
1564 --
1565 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',120);
1566 --
1567 open vacancies;
1568 <<vacancy>>
1569 loop
1570 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',130);
1571 fetch vacancies into p_rowid;
1572 exit when vacancies%NOTFOUND;
1573 --
1574 update per_all_vacancies pv
1575 set pv.recruiter_id = NULL
1576 where pv.rowid = p_rowid;
1577 if sql%notfound then
1578 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1579 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1580 hr_utility.set_message_token('STEP',4);
1581 hr_utility.raise_error;
1582 end if;
1583 end loop vacancy;
1584 close vacancies;
1585 --
1586 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',140);
1587 open requisitions1;
1588 <<requisition>>
1589 loop
1590 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',150);
1591 fetch requisitions1 into p_rowid;
1592 exit when requisitions1%NOTFOUND;
1593 update per_requisitions pr
1594 set pr.person_id = NULL
1595 where pr.rowid = p_rowid;
1596 if sql%notfound then
1597 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1598 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1599 hr_utility.set_message_token('STEP',5);
1600 hr_utility.raise_error;
1601 end if;
1602 end loop requisition;
1603 close requisitions1;
1604 --
1605 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',160);
1606 open absenses1;
1607 <<absence>>
1608 loop
1609 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',170);
1610 fetch absenses1 into p_rowid;
1611 exit when absenses1%NOTFOUND;
1612 delete from per_absence_attendances paa
1613 where paa.rowid = p_rowid;
1614 if sql%notfound then
1615 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1616 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1617 hr_utility.set_message_token('STEP',6);
1618 hr_utility.raise_error;
1619 end if;
1620 end loop absense;
1621 close absenses1;
1622 --
1623 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',180);
1624 open budget_values1;
1625 <<budget_value>>
1626 loop
1627 fetch budget_values1 into p_rowid;
1628 exit when budget_values1%NOTFOUND;
1629 delete from per_assignment_budget_values_f pab
1630 where pab.rowid = p_rowid;
1631 if sql%notfound then
1632 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1633 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1634 hr_utility.set_message_token('STEP',7);
1635 hr_utility.raise_error;
1636 end if;
1637 end loop budget_value;
1638 close budget_values1;
1639 --
1640 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',190);
1641 open payment1;
1642 <<pay>>
1643 loop
1644 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',200);
1645 fetch payment1 into p_rowid;
1646 exit when payment1%NOTFOUND;
1647 delete from pay_personal_payment_methods ppm
1648 where ppm.rowid = p_rowid;
1649 if sql%notfound then
1650 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1651 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1652 hr_utility.set_message_token('STEP',8);
1653 hr_utility.raise_error;
1654 end if;
1655 end loop pay;
1656 close payment1;
1657 --
1658 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',210);
1659 open comments1;
1660 <<comment>>
1661 loop
1662 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',220);
1663 fetch comments1 into p_rowid;
1664 exit when comments1%NOTFOUND;
1665 delete from hr_comments h
1666 where h.rowid = p_rowid;
1667 if sql%notfound then
1668 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1669 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1670 hr_utility.set_message_token('STEP',9);
1671 hr_utility.raise_error;
1672 end if;
1673 end loop comment;
1674 close comments1;
1675 --
1676 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',230);
1677 open assignments;
1678 <<assignment>>
1679 loop
1680 -- VT #438579 03/05/79 added assignment_id
1681 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',240);
1682 fetch assignments into p_rowid,l_assignment_id,l_asg_status_type_id; -- Bug 3564129
1683 exit when assignments%NOTFOUND;
1684 -- VT #438579 03/05/97 added delete
1685 delete from per_spinal_point_placements_f spp
1686 where spp.assignment_id = l_assignment_id;
1687 delete from pay_cost_allocations_f pca
1688 where pca.assignment_id = l_assignment_id;
1689 -- Start of fix 3564129
1690 open irc_asg_status;
1691 fetch irc_asg_status into l_asg_status_id, l_asg_status_ovn;
1692 if irc_asg_status%found then
1693 --
1694 IRC_ASG_STATUS_API.delete_irc_asg_status
1695 (p_assignment_status_id => l_asg_status_id
1696 ,p_object_version_number => l_asg_status_ovn);
1697 --
1698 end if;
1699 close irc_asg_status;
1700 -- fix for bug 5005157 starts here.
1701 open pay_proposals2(l_assignment_id);
1702 <<pay_proposals>>
1703 loop
1704 fetch pay_proposals2 into l_pk_id, l_ovn;
1705 exit when pay_proposals2%NOTFOUND;
1706 hr_maintain_proposal_api.delete_salary_proposal(p_pay_proposal_id => l_pk_id
1707 ,p_business_group_id => l_business_group_id
1708 ,p_object_version_number => l_ovn
1709 ,p_validate => FALSE
1710 ,p_salary_warning => l_sal_warning);
1711 end loop pay_proposals;
1712 close pay_proposals2;
1713 open csr_get_salary(l_assignment_id);
1714 fetch csr_get_salary into l_element_entry_id;
1715 if csr_get_salary%found then
1716 close csr_get_salary;
1717
1718 open csr_chk_rec_exists(l_assignment_id);
1719 fetch csr_chk_rec_exists into l_element_entry_id1;
1720
1721 if csr_chk_rec_exists%found then
1722 close csr_chk_rec_exists;
1723 --
1724 hr_entry_api.delete_element_entry
1725 ('DELETE'
1726 ,p_start_date - 1
1727 ,l_element_entry_id);
1728 else
1729 close csr_chk_rec_exists;
1730 hr_entry_api.delete_element_entry
1731 ('ZAP'
1732 ,p_start_date
1733 ,l_element_entry_id);
1734 end if;
1735 else
1736 close csr_get_salary;
1737 end if;
1738 -- fix for bug 5005157 ends here.
1739
1740
1741 -- End of fix 3564129
1742 delete from per_all_assignments_f paf
1743 where paf.rowid = p_rowid;
1744 if sql%notfound then
1745 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1746 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1747 hr_utility.set_message_token('STEP',10);
1748 hr_utility.raise_error;
1749 end if;
1750 end loop assignment;
1751 close assignments;
1752 --
1753 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',250);
1754 open applications;
1755 <<application>>
1756 loop
1757 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',260);
1758 fetch applications into p_rowid;
1759 exit when applications%NOTFOUND;
1760 update per_applications pap
1761 set pap.date_end = NULL
1762 where pap.rowid = p_rowid;
1763 if sql%notfound then
1764 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1765 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1766 hr_utility.set_message_token('STEP',11);
1767 hr_utility.raise_error;
1768 end if;
1769 end loop application;
1770 close applications;
1771 --
1772 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',270);
1773 open ass1;
1774 <<entries>>
1775 loop
1776 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',280);
1777 fetch ass1 into p_assignment_id,p_start_date,p_rowid;
1778 exit when ass1%NOTFOUND;
1779 update per_all_assignments_f paf
1780 set paf.effective_end_date = p_end_of_time
1781 where paf.rowid = p_rowid;
1782 --
1783 hrentmnt.maintain_entries_asg(p_assignment_id
1784 ,p_business_group_id
1785 ,'CNCL_HIRE'
1786 ,NULL
1787 ,NULL
1788 ,NULL
1789 ,'ZAP'
1790 ,p_start_date
1791 ,p_end_of_time);
1792 --
1793 end loop entries;
1794 close ass1;
1795 --
1796 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',290);
1797 l_back2back := per_periods_of_service_pkg_v2.IsBackToBackContract
1798 ( p_person_id => p_person_id, p_hire_date_of_current_pds => p_date_start);
1799
1800 open period;
1801 <<service>>
1802 loop
1803 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',300);
1804 fetch period into p_rowid;
1805 exit when period%notfound;
1806 delete from per_periods_of_service
1807 where rowid = p_rowid;
1808 if sql%notfound then
1809 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1810 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1811 hr_utility.set_message_token('STEP',11);
1812 hr_utility.raise_error;
1813 end if;
1814 end loop service;
1815 if period%rowcount <1 then
1816 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1817 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1818 hr_utility.set_message_token('STEP',12);
1819 hr_utility.raise_error;
1820 end if;
1821 close period;
1822 --
1823 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',310);
1824 -- 3194314
1825 open csr_is_cwk(p_date_start);
1826 fetch csr_is_cwk into l_pp_id, l_cwk_date_start;
1827 close csr_is_cwk;
1828 hr_utility.trace('CWK pp_id = '||l_pp_id);
1829 --
1830 -- 3848352 start
1831 l_apl_flag := NULL;
1832 open csr_apl_ptu_id;
1833 fetch csr_apl_ptu_id into l_dummy;
1834 if csr_apl_ptu_id%found then
1835 l_apl_flag := 'Y';
1836 end if;
1837 close csr_apl_ptu_id;
1838 hr_utility.trace('l_apl_flag = '||l_apl_flag);
1839 -- 3848352 end
1840
1841 -- <<
1842 open person;
1843 <<per>>
1844 loop
1845 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',320);
1846 fetch person into p_rowid, l_person_end_date;
1847 exit when person%notfound;
1848 if l_back2back
1849 then
1850 if l_person_end_date = hr_general.end_of_time then --#1998140
1851
1852 select person_type_id into l_person_type_id
1853 from per_person_types
1854 where business_group_id = p_business_group_id
1855 and system_person_type = 'EX_EMP'
1856 and default_flag = 'Y';
1857
1858 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',322);
1859
1860 update per_people_f
1861 set person_type_id = l_person_type_id,
1862 effective_start_date = p_date_start, -- in case DT updates exist
1863 current_employee_flag = null,
1864 current_emp_or_apl_flag = l_apl_flag, -- 3848352 --null,
1865 current_applicant_flag = l_apl_flag -- 3848352 --null
1866 where rowid = p_rowid;
1867 --
1868 else -- #1998140
1869 --
1870 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',323);
1871
1872 delete from per_people_f
1873 where rowid = p_rowid;
1874 --
1875 end if;
1876
1877 elsif l_pp_id is not null then -- 3194314
1878
1879 if l_person_end_date = hr_general.end_of_time then --#1998140
1880
1881 -- 3848352 start
1882 if hr_person_type_usage_info.is_person_of_type
1883 ( p_effective_date => p_date_start
1884 ,p_person_id => p_person_id
1885 ,p_system_person_type => 'EX_APL'
1886 )
1887 and l_apl_flag is not null then
1888 select person_type_id into l_person_type_id
1889 from per_person_types
1890 where business_group_id = p_business_group_id
1891 and system_person_type = 'APL'
1892 and default_flag = 'Y';
1893 else
1894 select person_type_id into l_person_type_id
1895 from per_person_types
1896 where business_group_id = p_business_group_id
1897 and system_person_type = 'OTHER' -- EX_CWK but this is not maintained
1898 and default_flag = 'Y';
1899 end if;
1900 -- 3848352 end
1901
1902 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',324);
1903
1904 --
1905 -- 3847884 Changed from p_start_date to p_date_start
1906 --
1907 update per_people_f
1908 set person_type_id = l_person_type_id,
1909 current_employee_flag = null,
1910 current_emp_or_apl_flag = l_apl_flag, -- 3848352 -- null,
1911 current_applicant_flag = l_apl_flag, -- 3848352 --null,
1912 per_information7 = null,
1913 employee_number = null,
1914 start_date = l_cwk_date_start,
1915 effective_start_date = p_date_start, -- p_start_date, -- in case DT updates exist
1916 original_date_of_hire = null
1917 where rowid = p_rowid;
1918 --
1919 else -- #1998140
1920 --
1921 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',325);
1922
1923 delete from per_people_f
1924 where rowid = p_rowid;
1925 --
1926 end if;
1927
1928 -- <<
1929 else
1930 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',330);
1931 delete from per_people_f
1932 where rowid = p_rowid;
1933 end if;
1934 --
1935 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',340);
1936 -- if sql%ROWCOUNT <1 then
1937 if sql%notfound then
1938 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1939 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1940 hr_utility.set_message_token('STEP',13);
1941 hr_utility.raise_error;
1942 end if;
1943 end loop per;
1944 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',350);
1945 if person%rowcount <1 then
1946 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1947 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1948 hr_utility.set_message_token('STEP',14);
1949 hr_utility.raise_error;
1950 end if;
1951 close person;
1952 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',360);
1953 -- 3194314
1954 -- this should not get exected when b2b cwk/emp
1955 if l_pp_id is null then
1956 open new_person;
1957 <<new_per>>
1958 loop
1959 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',370);
1960 fetch new_person into p_rowid;
1961 exit when new_person%notfound;
1962 if NOT l_back2back
1963 then
1964 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',380);
1965 update per_people_f
1966 set effective_end_date = p_end_of_time
1967 where rowid = p_rowid;
1968 end if;
1969 --
1970 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',390);
1971 if sql%ROWCOUNT <1 then
1972 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1973 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1974 hr_utility.set_message_token('STEP',15);
1975 hr_utility.raise_error;
1976 end if;
1977 end loop new_per;
1978 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',400);
1979 if new_person%rowcount <1 then
1980 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1981 hr_utility.set_message_token('PROCEDURE','do_cancel_hire');
1982 hr_utility.set_message_token('STEP',16);
1983 hr_utility.raise_error;
1984 end if;
1985 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',410);
1986 close new_person;
1987
1988 end if; -- 3194314 is b2b?
1989 --
1990 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',420);
1991 -- 115.1 Change by M Bocutt
1992 --
1993 -- If we have an EMP PTU record that started on the hire date
1994 -- then delete it.
1995 --
1996 -- PTU : Code added
1997
1998 hr_utility.set_location('cancel_hire_or_apl.p_date_start = '||to_char(p_date_start,'DD/MM/YYYY'),420);
1999 hr_utility.set_location('cancel_hire_or_apl.p_person_id = '||to_char(p_person_id),420);
2000
2001 if l_pp_id is not null then -- 3194314
2002
2003 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',424);
2004
2005 open csr_emp_ptu_id;
2006 fetch csr_emp_ptu_id into l_emp_ptu_id;
2007 if csr_emp_ptu_id%FOUND and l_emp_ptu_id is not null then
2008
2009 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',425);
2010 --fix for bug 6671352 starts here.
2011 /*hr_per_type_usage_internal.maintain_person_type_usage
2012 (p_effective_date => p_date_start
2013 ,p_person_id => p_person_id
2014 ,p_person_type_id => l_emp_ptu_id
2015 ,p_datetrack_delete_mode => 'ZAP'
2016 );*/
2017 hr_per_type_usage_internal.cancel_person_type_usage
2018 (p_effective_date => p_date_start
2019 ,p_person_id => p_person_id
2020 ,p_system_person_type => 'EMP');
2021
2022 end if;
2023 --fix for bug 6671352 ends here.
2024 close csr_emp_ptu_id;
2025 --
2026 --3848352 start
2027 --
2028 open csr_apl_ptu_id;
2029 fetch csr_apl_ptu_id into l_apl_ptu_id;
2030 if csr_apl_ptu_id%FOUND and l_apl_ptu_id is not null then
2031
2032 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',425);
2033 hr_per_type_usage_internal.maintain_person_type_usage
2034 (p_effective_date => p_date_start -1
2035 ,p_person_id => p_person_id
2036 ,p_person_type_id => l_apl_ptu_id
2037 ,p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE'
2038 );
2039 end if;
2040 close csr_apl_ptu_id;
2041 --
2042 -- 3848352 end
2043 --
2044 else
2045
2046 hr_per_type_usage_internal.cancel_person_type_usage
2047 (p_effective_date => p_date_start
2048 ,p_person_id => p_person_id
2049 ,p_system_person_type => 'EMP');
2050
2051 -- Added for the bug 6899072 starts here
2052 -- This finds out any system person type of OTHER records
2053 -- which is end dated while creating a employment
2054 -- and updates the end date to end of time while
2055 -- cancelling the hire
2056
2057 upd_person_type_usage_end_date
2058 (p_effective_date => p_date_start-1
2059 ,p_person_id => p_person_id
2060 ,p_system_person_type => 'OTHER');
2061
2062 -- Change for the bug 6899072 ends here
2063
2064 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',430);
2065 --
2066 --5450847 start : Need to maintain APL record if any. Delete the EX_APL
2067 -- record and update EED of APL record to EOTime.
2068 --
2069 open csr_apl_ptu_id;
2070 fetch csr_apl_ptu_id into l_apl_ptu_id;
2071 if csr_apl_ptu_id%FOUND and l_apl_ptu_id is not null then
2072
2073 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',432);
2074 hr_per_type_usage_internal.maintain_person_type_usage
2075 (p_effective_date => p_date_start -1
2076 ,p_person_id => p_person_id
2077 ,p_person_type_id => l_apl_ptu_id
2078 ,p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE'
2079 );
2080 end if;
2081 close csr_apl_ptu_id;
2082 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',433);
2083 --
2084 -- 5450847 end
2085 --
2086
2087 end if;
2088
2089 if l_back2back then
2090
2091 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire',435);
2092
2093 hr_per_type_usage_internal.maintain_person_type_usage
2094 (p_effective_date => p_date_start
2095 ,p_person_id => p_person_id
2096 ,p_person_type_id => l_person_type_id
2097 );
2098
2099 end if;
2100 -- PTU : End of changes
2101
2102 -- hr_per_type_usage_internal.maintain_ptu(
2103 -- p_validate => false,
2104 -- p_person_id => p_person_id,
2105 -- p_action => 'CANCEL HIRE',
2106 -- p_period_of_service_id => p_period_of_service_id,
2107 -- p_actual_termination_date => NULL,
2108 -- p_business_group_id => p_business_group_id,
2109 -- p_date_start => p_date_start,
2110 -- p_leaving_reason => null,
2111 -- p_old_date_start => null,
2112 -- p_old_leaving_reason => null);
2113 --
2114 per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);
2115 --
2116 for asg_rec in c_assignments loop
2117 --
2118 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire', 501);
2119 --
2120 hr_security_internal.add_to_person_list(
2121 p_effective_date => asg_rec.effective_start_date
2122 ,p_assignment_id => asg_rec.assignment_id);
2123 --
2124 hr_utility.set_location('cancel_hire_or_apl.do_cancel_hire', 502);
2125 --
2126 end loop;
2127 --
2128 end do_cancel_hire;
2129 --
2130 procedure do_cancel_appl(p_person_id NUMBER
2131 ,p_date_received DATE
2132 ,p_end_of_time DATE
2133 ,p_business_group_id NUMBER
2134 ,p_application_id NUMBER) is
2135 --
2136 -- applicant assignment cursor
2137 -- determine the assignment_id for all the assignments of the application
2138 --
2139 cursor appl_asg is
2140 select distinct assignment_id
2141 from per_assignments_f
2142 where application_id = p_application_id;
2143 --
2144 -- events cursor -- This is only really finding applicant interviews
2145 -- since in all other cases pe.assignment_id is null
2146 --
2147 cursor events_or_interviews(p_assignment_id NUMBER) is
2148 select pe.event_id
2149 from per_events pe
2150 where pe.date_start >= p_date_received
2151 and p_assignment_id = pe.assignment_id;
2152 --
2153 -- bookings cursor
2154 --
2155 cursor bookings2 is
2156 select pb.booking_id
2157 from per_events pe,
2158 per_bookings pb
2159 where pb.event_id = pe.event_id
2160 and pe.date_start >= p_date_received
2161 and pb.person_id = p_person_id
2162 and pe.emp_or_apl = 'A';
2163 --
2164 -- Budget values cursor
2165 --
2166 cursor budget_values2(p_assignment_id NUMBER) is
2167 select pab.rowid
2168 from per_assignment_budget_values_f pab
2169 where pab.assignment_id = p_assignment_id;
2170 --
2171 -- letters cursor
2172 --
2173 cursor letters2(p_assignment_id NUMBER) is
2174 select p.letter_request_line_id
2175 from per_letter_request_lines p
2176 where p.assignment_id = p_assignment_id;
2177 --
2178 -- Comments cursor
2179 --
2180 cursor comments2(p_assignment_id NUMBER) is
2181 select h.comment_id
2182 from hr_comments h
2183 , per_assignments_f paf
2184 where h.comment_id = paf.comment_id
2185 and paf.assignment_id = p_assignment_id;
2186 --
2187 -- assignments cursor
2188 --
2189 cursor assignments is
2190 select paf.rowid
2191 from per_assignments_f paf
2192 where paf.person_id = p_person_id
2193 and paf.application_id = p_application_id;
2194 --
2195 -- applications cursor
2196 --
2197 cursor applications is
2198 select rowid
2199 from per_applications pap
2200 where pap.date_received = p_date_received
2201 and pap.person_id = p_person_id;
2202 --
2203 -- person cursor
2204 --
2205 cursor people is
2206 select rowid
2207 from per_people_f
2208 where person_id = p_person_id
2209 and effective_start_date >= p_date_received;
2210 --
2211 -- new person cursor
2212 --
2213 cursor new_people is
2214 select rowid
2215 from per_people_f
2216 where person_id = p_person_id
2217 and effective_end_date = p_date_received - 1;
2218 --
2219 --
2220 -- NEW CURSORS FOR CANCEL_APPL BEGIN adhunter feb-2001
2221 --
2222 -- PTU changes: this cursor not required.
2223 -- person type usages cursor
2224 --
2225 --cursor person_type_usages2 is
2226 --select ptu.person_type_usage_id, ptu.object_version_number
2227 --from per_person_type_usages_f ptu, per_person_types ppt
2228 --where person_id = p_person_id
2229 --and ptu.person_type_id = ppt.person_type_id
2230 --and ppt.system_person_type = 'APL'
2231 --and ptu.effective_start_date = p_date_received;
2232 --
2233 -- secondary assignment statuses cursor
2234 --
2235 cursor sec_asg_statuses2(p_assignment_id NUMBER) is
2236 select sas.rowid
2237 from per_secondary_ass_statuses sas
2238 where sas.assignment_id = p_assignment_id;
2239 --
2240 -- assignment extra info cursor
2241 --
2242 cursor asg_extra_info2(p_assignment_id NUMBER) is
2243 select paei.assignment_extra_info_id, paei.object_version_number
2244 from per_assignment_extra_info paei
2245 where p_assignment_id = paei.assignment_id;
2246 --
2247 -- pay proposals cursor
2248 --
2249 cursor pay_proposals2(p_assignment_id NUMBER) is
2250 select ppp.pay_proposal_id, ppp.object_version_number
2251 from per_pay_proposals ppp
2252 where p_assignment_id = ppp.assignment_id;
2253 --
2254 -- work incidents cursor
2255 --
2256 cursor work_incidents2(p_assignment_id NUMBER) is
2257 select pwi.incident_id, pwi.object_version_number
2258 from per_work_incidents pwi
2259 where p_assignment_id = pwi.assignment_id;
2260 --
2261 -- asg proposal answers cursor
2262 --
2263 cursor asg_prop_answer2(p_assignment_id NUMBER) is
2264 select papa.rowid
2265 from per_assign_proposal_answers papa
2266 where p_assignment_id = papa.assignment_id;
2267 --
2268 -- disabilities cursor
2269 --
2270 -- FIX 1977389 - commented out
2271 -- cursor disabilities2(p_assignment_id NUMBER) is
2272 -- select pdf.disability_id, pdf.object_version_number
2273 -- from per_disabilities_f pdf
2274 -- where p_assignment_id = pdf.assignment_id
2275 -- order by pdf.effective_start_date desc;
2276 -- END FIX.
2277 --
2278 -- person comments when apl cursor
2279 --
2280 cursor person_apl_comments2 is
2281 select distinct h.comment_id --fix for bug 7157204.
2282 from hr_comments h, per_people_f ppf
2283 where ppf.business_group_id = p_business_group_id
2284 and ppf.person_id = p_person_id
2285 and ppf.effective_start_date >= p_date_received
2286 and ppf.comment_id = h.comment_id;
2287 --
2288 -- Assignments from previous
2289 --
2290 -- NEW CURSORS FOR CANCEL_APPL END
2291 --
2292 -- Bug 4095559: Cursor return whether B2B of EX_EMP/EX_CWK.APL
2293 cursor csr_b2b_apl is
2294 select count(ptu.person_type_id)
2295 from per_person_type_usages_f ptu
2296 ,per_person_types ppt
2297 where ptu.person_id = p_person_id
2298 and ptu.effective_start_date = p_date_received
2299 and ptu.person_type_id = ppt.person_type_id
2300 and ppt.system_person_type <> 'APL';
2301
2302 -- Bug 4095559: Cursor to update per_people_f B2B of EX_EMP/EX_CWK.APL
2303 -- new_people_b2b for person records with ESD as date_received
2304 cursor new_people_b2b is
2305 select rowid,effective_end_date,effective_start_date,current_applicant_flag
2306 from per_people_f
2307 where person_id = p_person_id
2308 and effective_start_date = p_date_received;
2309
2310 -- Bug 4095559: Cursor to update per_people_f B2B of EX_EMP/EX_CWK.APL
2311 -- people_b2b for person records with ESD future to date_received
2312 cursor people_b2b is
2313 select rowid,effective_end_date,effective_start_date,current_applicant_flag
2314 from per_people_f
2315 where person_id = p_person_id
2316 and effective_start_date > p_date_received;
2317
2318 -- Bug 4095559: Cursor to get the latest person_type_id B2B of
2319 -- EX_EMP/EX_CWK.APL
2320 cursor latest_ptid is
2321 select person_type_id
2322 from per_person_type_usages_f
2323 where person_id = p_person_id
2324 order by EFFECTIVE_END_DATE desc, EFFECTIVE_START_DATE desc;
2325 --
2326 -- Cursor to re-evaluate the security.
2327 cursor csr_asg_sec is
2328 select paf.assignment_id, paf.effective_start_date
2329 from per_all_assignments_f paf
2330 where paf.person_id = p_person_id
2331 and paf.assignment_type <> 'B'; -- Added For Bug # 6630290
2332 --
2333 l_rowid VARCHAR2(18);
2334 l_pk_id NUMBER;
2335 l_ovn NUMBER;
2336 l_start_date DATE;
2337 l_end_date DATE;
2338 l_business_group_id NUMBER;
2339 l_sal_warning BOOLEAN;
2340 l_assignment_id NUMBER;
2341 l_ptu_count number; -- Bug 4095559
2342 l_b2b_apl boolean default false; -- Bug 4095559
2343 l_person_type_id number; -- Bug 4095559
2344 l_effective_end_date date; -- Bug 4095559
2345 l_effective_start_date date; -- Bug 4095559
2346 l_current_applicant_flag VARCHAR2(30); -- Bug 4095559
2347 l_fut_per_chg boolean default false; -- Bug 4095559
2348 --
2349 begin
2350 --
2351 l_business_group_id := p_business_group_id;
2352 --
2353 open appl_asg;
2354 <<apl_asg>>
2355 loop
2356 fetch appl_asg into l_assignment_id;
2357 exit when appl_asg%NOTFOUND;
2358 --
2359 --
2360 --
2361 open events_or_interviews(l_assignment_id);
2362 <<event>>
2363 loop
2364 fetch events_or_interviews into l_pk_id;
2365 exit when events_or_interviews%NOTFOUND;
2366 delete from per_events pe
2367 where pe.event_id = l_pk_id;
2368 if sql%notfound then
2369 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2370 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2371 hr_utility.set_message_token('STEP',2);
2372 hr_utility.raise_error;
2373 end if;
2374 end loop event;
2375 close events_or_interviews;
2376 --
2377 --
2378 open budget_values2(l_assignment_id);
2379 <<budget_val>>
2380 loop
2381 fetch budget_values2 into l_rowid;
2382 exit when budget_values2%NOTFOUND;
2383 delete from per_assignment_budget_values_f pab
2384 where pab.rowid = l_rowid;
2385 if sql%notfound then
2386 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2387 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2388 hr_utility.set_message_token('STEP',3);
2389 hr_utility.raise_error;
2390 end if;
2391 end loop budget_val;
2392 close budget_values2;
2393 --
2394 --
2395 open comments2(l_assignment_id);
2396 <<comments>>
2397 loop
2398 fetch comments2 into l_pk_id;
2399 exit when comments2%NOTFOUND;
2400 delete from hr_comments h
2401 where h.comment_id = l_pk_id;
2402 if sql%notfound then
2403 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2404 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2405 hr_utility.set_message_token('STEP',4);
2406 hr_utility.raise_error;
2407 end if;
2408 end loop comments;
2409 close comments2;
2410 --
2411 --
2412 open letters2(l_assignment_id);
2413 <<letters>>
2414 loop
2415 fetch letters2 into l_pk_id;
2416 exit when letters2%NOTFOUND;
2417 delete from per_letter_request_lines plrl
2418 where plrl.letter_request_line_id = l_pk_id;
2419 if sql%notfound then
2420 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2421 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2422 hr_utility.set_message_token('STEP',5);
2423 hr_utility.raise_error;
2424 end if;
2425 end loop letters;
2426 close letters2;
2427 --
2428 --
2429 open sec_asg_statuses2(l_assignment_id);
2430 <<sec_asg_statuses>>
2431 loop
2432 fetch sec_asg_statuses2 into l_rowid;
2433 exit when sec_asg_statuses2%NOTFOUND;
2434 per_secondary_ass_statuses_pkg.delete_row(l_rowid);
2435 end loop sec_asg_statuses;
2436 close sec_asg_statuses2;
2437 --
2438 --
2439 open asg_extra_info2(l_assignment_id);
2440 <<asg_extra_info>>
2441 loop
2442 fetch asg_extra_info2 into l_pk_id, l_ovn;
2443 exit when asg_extra_info2%NOTFOUND;
2444 hr_assignment_extra_info_api.delete_assignment_extra_info(
2445 p_validate => FALSE
2446 ,p_assignment_extra_info_id => l_pk_id
2447 ,p_object_version_number => l_ovn);
2448 end loop asg_extra_info;
2449 close asg_extra_info2;
2450 --
2451 --
2452 open pay_proposals2(l_assignment_id);
2453 <<pay_proposals>>
2454 loop
2455 fetch pay_proposals2 into l_pk_id, l_ovn;
2456 exit when pay_proposals2%NOTFOUND;
2457 hr_maintain_proposal_api.delete_salary_proposal(p_pay_proposal_id => l_pk_id
2458 ,p_business_group_id => l_business_group_id
2459 ,p_object_version_number => l_ovn
2460 ,p_validate => FALSE
2461 ,p_salary_warning => l_sal_warning);
2462 end loop pay_proposals;
2463 close pay_proposals2;
2464 --
2465 --
2466 open work_incidents2(l_assignment_id);
2467 <<work_incidents>>
2468 loop
2469 fetch work_incidents2 into l_pk_id, l_ovn;
2470 exit when work_incidents2%NOTFOUND;
2471 per_work_incident_api.delete_work_incident(
2472 p_validate => FALSE
2473 ,p_incident_id => l_pk_id
2474 ,p_object_version_number => l_ovn);
2475 end loop work_incidents;
2476 close work_incidents2;
2477 --
2478 --
2479 open asg_prop_answer2(l_assignment_id);
2480 <<asg_prop_answer>>
2481 loop
2482 fetch asg_prop_answer2 into l_rowid;
2483 exit when asg_prop_answer2%NOTFOUND;
2484 delete from per_assign_proposal_answers papa
2485 where papa.rowid = l_rowid;
2486 if sql%notfound then
2487 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2488 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2489 hr_utility.set_message_token('STEP',6);
2490 hr_utility.raise_error;
2491 end if;
2492 end loop asg_prop_answer;
2493 close asg_prop_answer2;
2494 --
2495 --
2496 -- FIX 1977389 - commented out.
2497 -- open disabilities2(l_assignment_id);
2498 -- <<disabilities>>
2499 -- loop
2500 -- fetch disabilities2 into l_pk_id, l_ovn;
2501 -- exit when disabilities2%NOTFOUND;
2502 -- per_disability_api.delete_disability(
2503 -- p_validate => FALSE
2504 -- ,p_effective_date => p_date_received
2505 -- ,p_datetrack_mode => 'ZAP'
2506 -- ,p_disability_id => l_pk_id
2507 -- ,p_object_version_number => l_ovn
2508 -- ,p_effective_start_date => l_start_date
2509 -- ,p_effective_end_date => l_end_date );
2510 -- end loop disabilities;
2511 -- close disabilities2;
2512 -- END FIX.
2513 --
2514 end loop apl_asg;
2515 close appl_asg;
2516 --
2517 -- now deletes which don't need to be in appl_asg loop since they don't
2518 -- drive off assignment_id
2519 --
2520 open bookings2;
2521 <<bookings>>
2522 loop
2523 fetch bookings2 into l_pk_id;
2524 exit when bookings2%NOTFOUND;
2525 delete from per_bookings pb
2526 where pb.booking_id = l_pk_id;
2527 if sql%notfound then
2528 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2529 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2530 hr_utility.set_message_token('STEP',7);
2531 hr_utility.raise_error;
2532 end if;
2533 end loop bookings;
2534 close bookings2;
2535 --
2536 --
2537 open person_apl_comments2;
2538 <<person_apl_comments>>
2539 loop
2540 fetch person_apl_comments2 into l_pk_id;
2541 exit when person_apl_comments2%NOTFOUND;
2542 delete from hr_comments h
2543 where h.comment_id = l_pk_id;
2544 if sql%notfound then
2545 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2546 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2547 hr_utility.set_message_token('STEP',8);
2548 hr_utility.raise_error;
2549 end if;
2550 end loop person_apl_comments;
2551 close person_apl_comments2;
2552 --
2553 -- assignment, application, people records should be deleted last in this
2554 -- order to avoid integrity constraint errors.
2555 --
2556 open assignments;
2557 <<assign>>
2558 loop
2559 fetch assignments into l_rowid;
2560 exit when assignments%NOTFOUND;
2561 delete from per_assignments_f paf
2562 where paf.rowid = l_rowid;
2563 if sql%notfound then
2564 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2565 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2566 hr_utility.set_message_token('STEP',9);
2567 hr_utility.raise_error;
2568 end if;
2569 end loop assign;
2570 close assignments;
2571 --
2572 --
2573 open applications;
2574 <<application>>
2575 loop
2576 fetch applications into l_rowid;
2577 exit when applications%NOTFOUND;
2578 delete from per_applications pap
2579 where pap.rowid = l_rowid;
2580 if sql%notfound then
2581 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2582 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2583 hr_utility.set_message_token('STEP',10);
2584 hr_utility.raise_error;
2585 end if;
2586 end loop application;
2587 close applications;
2588 --
2589 -- Bug 4095559 : Getting the B2B flag
2590 --
2591 open csr_b2b_apl;
2592 fetch csr_b2b_apl into l_ptu_count;
2593 close csr_b2b_apl;
2594
2595 IF l_ptu_count > 0
2596 THEN
2597 l_b2b_apl:=TRUE;
2598 ELSE
2599 l_b2b_apl:=FALSE;
2600 END IF;
2601 -- Bug 4095559 : Execute the existing logic, if it is not B2B
2602 if NOT l_b2b_apl then
2603 open people;
2604 <<per>>
2605 loop
2606 fetch people into l_rowid;
2607 exit when people%notfound;
2608 delete from per_people_f
2609 where rowid = l_rowid;
2610 if sql%notfound then
2611 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2612 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2613 hr_utility.set_message_token('STEP',11);
2614 hr_utility.raise_error;
2615 end if;
2616 end loop per;
2617 if people%rowcount <1 then
2618 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2619 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2620 hr_utility.set_message_token('STEP',12);
2621 hr_utility.raise_error;
2622 end if;
2623 close people;
2624 --
2625 --
2626 open new_people;
2627 <<new_per>>
2628 loop
2629 fetch new_people into l_rowid;
2630 exit when new_people%notfound;
2631 update per_people_f
2632 set effective_end_date = p_end_of_time
2633 where rowid = l_rowid;
2634 --
2635 if sql%ROWCOUNT <1 then
2636 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2637 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2638 hr_utility.set_message_token('STEP',13);
2639 hr_utility.raise_error;
2640 end if;
2641 end loop new_per;
2642 if new_people%rowcount <1 then
2643 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2644 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2645 hr_utility.set_message_token('STEP',14);
2646 hr_utility.raise_error;
2647 end if;
2648 close new_people;
2649 -- Bug 4095559 Starts
2650 -- Desc: : Execute the new logic, if it is B2B
2651 else
2652 open people_b2b;
2653 <<per_b2b>>
2654 loop
2655 fetch people_b2b into l_rowid,l_effective_end_date,
2656 l_effective_start_date,l_current_applicant_flag;
2657 exit when people_b2b%notfound;
2658 l_fut_per_chg := TRUE; -- set to TRUE if the future person change exists
2659 -- Get the latest person Type Id to update the person table.
2660 open latest_ptid;
2661 fetch latest_ptid into l_person_type_id;
2662 close latest_ptid;
2663 -- Upadte the person table with the person type id
2664 update per_people_f
2665 set PERSON_TYPE_ID=l_person_type_id
2666 where rowid = l_rowid;
2667 if sql%notfound then
2668 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2669 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2670 hr_utility.set_message_token('STEP',15);
2671 hr_utility.raise_error;
2672 end if;
2673 end loop per_b2b;
2674 /* if people_b2b%rowcount <1 then
2675 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2676 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2677 hr_utility.set_message_token('STEP',16);
2678 hr_utility.raise_error;
2679 end if;*/
2680 close people_b2b;
2681
2682 open new_people_b2b;
2683 <<new_per_b2b>>
2684 loop
2685 fetch new_people_b2b into l_rowid,l_effective_end_date,
2686 l_effective_start_date,l_current_applicant_flag;
2687 exit when new_people_b2b%notfound;
2688
2689 if l_effective_end_date = hr_general.effective_date -1 then
2690 delete from per_people_f
2691 where rowid = l_rowid;
2692 elsif l_current_applicant_flag = 'Y' then
2693 -- Get the latest person Type Id to update the person table.
2694 open latest_ptid;
2695 fetch latest_ptid into l_person_type_id;
2696 close latest_ptid;
2697 -- Updating the person table, if there is future changes
2698 if l_fut_per_chg then
2699 update per_people_f
2700 set applicant_number = null,
2701 current_applicant_flag=null,
2702 current_emp_or_apl_flag=null,
2703 PERSON_TYPE_ID=l_person_type_id
2704 where rowid = l_rowid;
2705 -- Updating the person table, if there is NO future changes
2706 else
2707 update per_people_f
2708 set applicant_number = null,
2709 current_applicant_flag=null,
2710 current_emp_or_apl_flag=null,
2711 PERSON_TYPE_ID=l_person_type_id,
2712 effective_end_date=hr_general.end_of_time
2713 where rowid = l_rowid;
2714 end if;
2715 end if;
2716 --
2717 if sql%notfound then
2718 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2719 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2720 hr_utility.set_message_token('STEP',17);
2721 hr_utility.raise_error;
2722 end if;
2723 end loop new_per_b2b;
2724 if new_people_b2b%rowcount <1 then
2725 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2726 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2727 hr_utility.set_message_token('STEP',18);
2728 hr_utility.raise_error;
2729 end if;
2730 close new_people_b2b;
2731
2732 end if;
2733 -- Bug 4095559 Ends
2734 --
2735 --
2736 /* PTU changes: this is no longer necessary since PTU records
2737 will be maintained differently.
2738
2739 open person_type_usages2;
2740 <<person_type_usages>>
2741 loop
2742 fetch person_type_usages2 into l_pk_id, l_ovn;
2743 exit when person_type_usages2%NOTFOUND;
2744 hr_per_type_usage_internal.delete_person_type_usage
2745 (p_validate => FALSE
2746 ,p_person_type_usage_id => l_pk_id
2747 ,p_effective_date => p_date_received
2748 ,p_datetrack_mode => 'ZAP'
2749 ,p_object_version_number => l_ovn
2750 ,p_effective_start_date => l_start_date
2751 ,p_effective_end_date => l_end_date );
2752 end loop person_type_usages;
2753 if person_type_usages2%rowcount <1 then
2754 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2755 hr_utility.set_message_token('PROCEDURE','do_cancel_appl');
2756 hr_utility.set_message_token('STEP',15);
2757 hr_utility.raise_error;
2758 end if;
2759 close person_type_usages2;
2760 */
2761 -- Bug 4095559 Starts
2762 -- Desc: Delete the PTU records directly, if it is B2B
2763 if NOT l_b2b_apl then
2764 hr_per_type_usage_internal.cancel_person_type_usage
2765 (p_effective_date => p_date_received
2766 ,p_person_id => p_person_id
2767 ,p_system_person_type => 'APL');
2768
2769 else
2770 -- Bug 4095559 : Delete the Applicant record from PTU
2771 delete from per_person_type_usages_f ptu
2772 where ptu.person_id = p_person_id
2773 and ptu.effective_start_date = p_date_received
2774 and ptu.person_type_id in (
2775 select ppt.person_type_id
2776 from per_person_types ppt
2777 where ppt.system_person_type = 'APL');
2778
2779 end if;
2780 -- Bug 4095559 Ends
2781 --
2782 per_cancel_hire_or_apl_pkg.update_person_list (p_person_id => p_person_id);
2783 --
2784 for asg_sec_rec in csr_asg_sec loop
2785 --
2786 hr_security_internal.add_to_person_list(
2787 p_effective_date => asg_sec_rec.effective_start_date,
2788 p_assignment_id => asg_sec_rec.assignment_id);
2789 --
2790 end loop;
2791 --
2792 end do_cancel_appl;
2793 --
2794 procedure update_person_list (p_person_id NUMBER) is
2795 begin
2796 --
2797 -- Delete all rows from per_person_list
2798 --
2799 hr_security_internal.clear_from_person_list(p_person_id);
2800 --
2801 end update_person_list;
2802 --
2803 /*===========================================================================*
2804 | |
2805 | PRE_CANCEL_PLACEMENT_CHECKS |
2806 | |
2807 *============================================================================*/
2808 --
2809 PROCEDURE pre_cancel_placement_checks
2810 (p_person_id IN NUMBER
2811 ,p_business_group_id IN NUMBER
2812 ,p_effective_date IN DATE
2813 ,p_date_start IN DATE
2814 ,p_supervisor_warning IN OUT NOCOPY BOOLEAN
2815 ,p_recruiter_warning IN OUT NOCOPY BOOLEAN
2816 ,p_event_warning IN OUT NOCOPY BOOLEAN
2817 ,p_interview_warning IN OUT NOCOPY BOOLEAN
2818 ,p_review_warning IN OUT NOCOPY BOOLEAN
2819 ,p_vacancy_warning IN OUT NOCOPY BOOLEAN
2820 ,p_requisition_warning IN OUT NOCOPY BOOLEAN
2821 ,p_budget_warning IN OUT NOCOPY BOOLEAN
2822 ,p_payment_warning IN OUT NOCOPY BOOLEAN) IS
2823 --
2824 l_proc VARCHAR2(72) := g_package||'pre_cancel_placement_checks';
2825 --
2826 l_dummy VARCHAR2(30);
2827 l_dummy_id NUMBER;
2828 l_effective_date DATE;
2829 l_effective_start_date DATE;
2830 l_person_type_usage_id NUMBER;
2831 --
2832 -- Multi Assignment Check
2833 --
2834 CURSOR csr_multi_asg_check IS
2835 SELECT assignment_id
2836 FROM per_assignments_f paf
2837 WHERE (paf.business_group_id = p_business_group_id OR
2838 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
2839 AND paf.person_id = p_person_id;
2840 --
2841 -- Check person is a CWK
2842 --
2843 CURSOR csr_chk_person_is_cwk IS
2844 SELECT per.person_id,
2845 per.effective_start_date
2846 FROM per_people_f per
2847 WHERE per.person_id = p_person_id
2848 AND per.current_npw_flag = 'Y'
2849 AND per.effective_start_date >= p_date_start;
2850 --
2851 -- Supervisor
2852 --
2853 CURSOR csr_supervisor IS
2854 SELECT ROWID
2855 FROM per_assignments_f p
2856 WHERE (p.business_group_id = p_business_group_id OR
2857 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
2858 AND p.supervisor_id = p_person_id;
2859 --
2860 -- recruiter cursor
2861 --
2862 CURSOR csr_recruiter IS
2863 SELECT ROWID
2864 FROM per_assignments_f p
2865 WHERE p.recruiter_id = p_person_id
2866 AND (p.business_group_id = p_business_group_id OR
2867 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y');
2868 --
2869 -- reviews or Events cursor
2870 --
2871 CURSOR csr_reviews_or_events(p_type varchar2) IS
2872 SELECT 'Events exist'
2873 FROM per_events pe,
2874 per_bookings pb
2875 WHERE pe.business_group_id = pb.business_group_id
2876 AND (pb.business_group_id = p_business_group_id OR
2877 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
2878 AND pe.event_id = pb.event_id
2879 AND pe.event_or_interview = p_type
2880 AND pb.person_id = p_person_id;
2881 --
2882 -- Interviews cursor
2883 --
2884 CURSOR csr_interviews IS
2885 SELECT 'Interviews exist'
2886 FROM per_events pe
2887 WHERE (pe.business_group_id = p_business_group_id OR
2888 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
2889 AND pe.event_or_interview = 'I'
2890 AND pe.internal_contact_person_id = p_person_id;
2891 --
2892 -- vacancies cursor
2893 --
2894 CURSOR csr_vacancy IS
2895 SELECT ROWID
2896 FROM per_vacancies pv
2897 WHERE (pv.business_group_id = p_business_group_id OR
2898 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
2899 AND pv.recruiter_id = p_person_id
2900 AND pv.date_from >= l_effective_date;
2901 --
2902 -- requisitions cursor
2903 --
2904 CURSOR csr_requisition IS
2905 SELECT ROWID
2906 FROM per_requisitions pr
2907 WHERE (pr.business_group_id = p_business_group_id OR
2908 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
2909 AND pr.person_id = p_person_id;
2910 --
2911 -- budget_values cursor
2912 --
2913 CURSOR csr_budget_values IS
2914 SELECT ROWID
2915 FROM per_assignment_budget_values_f pab
2916 WHERE pab.business_group_id = p_business_group_id and
2917 EXISTS (SELECT 'budget_values exist'
2918 FROM per_all_assignments_f paf
2919 WHERE pab.business_group_id +0= paf.business_group_id + 0
2920 AND paf.business_group_id +0= p_business_group_id
2921 AND pab.assignment_id = paf.assignment_id
2922 AND paf.person_id = p_person_id
2923 AND paf.period_of_placement_date_start = p_date_start
2924 AND paf.effective_end_date >= l_effective_date
2925 --START for 5987416
2926 --AND pab.effective_end_date >= l_effective_date
2927 )
2928 AND pab.effective_end_date >= l_effective_date;
2929 --end for 5987416
2930
2931 --
2932 -- payment cursor
2933 --
2934 CURSOR csr_payment IS
2935 SELECT ROWID
2936 FROM pay_personal_payment_methods ppm
2937 WHERE ppm.business_group_id = p_business_group_id
2938 AND EXISTS (SELECT 'exists'
2939 FROM per_all_assignments_f paf
2940 WHERE paf.business_group_id +0= p_business_group_id
2941 AND paf.person_id = p_person_id
2942 AND paf.assignment_id = ppm.assignment_id
2943 AND paf.period_of_placement_date_start = p_date_start
2944 --start bug 5987416
2945 --AND ppm.effective_start_date>= l_effective_date
2946 --end bug 5987416
2947 )
2948 AND ppm.effective_start_date >= l_effective_date;
2949 --
2950 CURSOR csr_get_ptu_id IS
2951 SELECT ptu.person_type_usage_id
2952 FROM per_person_types pt,
2953 per_person_type_usages_f ptu
2954 WHERE pt.business_group_id = p_business_group_id
2955 AND pt.person_type_id = ptu.person_type_id
2956 AND l_effective_date BETWEEN ptu.effective_start_date
2957 AND ptu.effective_end_date
2958 AND ptu.person_id = p_person_id
2959 AND pt.system_person_type = 'CWK';
2960 --
2961 BEGIN
2962 --
2963 hr_utility.set_location('Entering : '||l_proc,10);
2964 --
2965 l_effective_date := TRUNC(p_effective_date);
2966 --
2967 -- Retrieve the person type usage ID for the CWK,
2968 -- for later use in this procedure.
2969 --
2970 OPEN csr_get_ptu_id;
2971 FETCH csr_get_ptu_id INTO l_person_type_usage_id;
2972
2973 IF csr_get_ptu_id%NOTFOUND THEN
2974 --
2975 CLOSE csr_get_ptu_id;
2976 --
2977 hr_utility.set_message(801,'HR_289751_CWK_SYS_PER_TYPE_ERR');
2978 hr_utility.raise_error;
2979 --
2980 END IF;
2981 --
2982 hr_utility.set_location(l_proc,15);
2983 --
2984 CLOSE csr_get_ptu_id;
2985 --
2986 -- Check that the person in a CWK employee
2987 -- if they are not then raise an error.
2988 --
2989 OPEN csr_chk_person_is_cwk;
2990 FETCH csr_chk_person_is_cwk INTO l_dummy_id, l_effective_start_date;
2991 --
2992 IF csr_chk_person_is_cwk%NOTFOUND THEN
2993 --
2994 CLOSE csr_chk_person_is_cwk;
2995 --
2996 hr_utility.set_message(801,'HR_289747_MUST_BE_CWK');
2997 hr_utility.raise_error;
2998 --
2999 END IF;
3000 --
3001 CLOSE csr_chk_person_is_cwk;
3002 --
3003 hr_utility.set_location(l_proc,20);
3004 --
3005 -- Check that the CWK Employee has only ONE assignment.
3006 -- If they do not then raise an error asking the user to delete
3007 -- the person.
3008 --
3009 OPEN csr_multi_asg_check;
3010 FETCH csr_multi_asg_check INTO l_dummy_id;
3011 --
3012 IF csr_multi_asg_check%ROWCOUNT > 1 THEN
3013 --
3014 CLOSE csr_multi_asg_check;
3015 --
3016 hr_utility.set_message(801,'HR_289748_MULTIPLE_CWK_ASG');
3017 hr_utility.raise_error;
3018 --
3019 END IF;
3020 --
3021 CLOSE csr_multi_asg_check;
3022 --
3023 hr_utility.set_location(l_proc,30);
3024 --
3025 -- Check that the CWK has no future person type changes.
3026 --
3027 IF hr_person_type_usage_info.FutSysPerTypeChgExists -- 3194314: adding person id
3028 (p_person_type_usage_id => l_person_type_usage_id
3029 ,p_effective_date => p_date_start
3030 ,p_person_id => p_person_id ) THEN
3031 -- #3684683 modified the application id to 800
3032 hr_utility.set_message(800,'HR_289749_CWK_FUTURE_PT_CHGE');
3033 hr_utility.raise_error;
3034
3035 END IF;
3036 --
3037 hr_utility.set_location(l_proc,40);
3038 --
3039 -- Check that the CWK has at least one previous person type.
3040 --
3041 IF NOT hr_person.chk_prev_person_type
3042 (p_system_person_type => 'CWK'
3043 ,p_person_id => p_person_id
3044 ,p_business_group_id => p_business_group_id
3045 ,p_effective_start_date => l_effective_start_date) THEN
3046 --
3047 hr_utility.set_message(800,'HR_289750_NO_PREVIOUS_CWK');
3048 hr_utility.raise_error;
3049 --
3050 END IF;
3051 --
3052 hr_utility.set_location(l_proc,50);
3053 --
3054 -- Check to see if the CWK is a supervisor for another worker.
3055 -- If they are set the supervisor_warning out parameter
3056 -- to TRUE.
3057 --
3058 OPEN csr_supervisor;
3059 --
3060 FETCH csr_supervisor INTO l_dummy;
3061 --
3062 IF csr_supervisor%FOUND THEN
3063 --
3064 p_supervisor_warning := TRUE;
3065 --
3066 ELSE
3067 --
3068 p_supervisor_warning := FALSE;
3069 --
3070 END IF;
3071 --
3072 CLOSE csr_supervisor;
3073 --
3074 hr_utility.set_location(l_proc,60);
3075 --
3076 -- Check to see if the CWK is a recruiter for another worker.
3077 -- If they are set the recruiter_warning out parameter
3078 -- to TRUE.
3079 --
3080 OPEN csr_recruiter;
3081 FETCH csr_recruiter INTO l_dummy;
3082 --
3083 IF csr_recruiter%FOUND THEN
3084 --
3085 p_recruiter_warning := TRUE;
3086 --
3087 ELSE
3088 --
3089 p_recruiter_warning := FALSE;
3090 --
3091 END IF;
3092 --
3093 CLOSE csr_recruiter;
3094 --
3095 hr_utility.set_location(l_proc,70);
3096 --
3097 -- Check to see if the CWK has any events.
3098 -- If they do set the event_warning out parameter
3099 -- to TRUE.
3100 --
3101 OPEN csr_reviews_or_events(p_type =>'E');
3102 FETCH csr_reviews_or_events INTO l_dummy;
3103 --
3104 IF csr_reviews_or_events%FOUND THEN
3105 --
3106 p_event_warning := TRUE;
3107 --
3108 ELSE
3109 --
3110 p_event_warning := FALSE;
3111 --
3112 END IF;
3113 --
3114 CLOSE csr_reviews_or_events;
3115 --
3116 hr_utility.set_location(l_proc,80);
3117 --
3118 -- Check to see if the CWK has any interviews.
3119 -- If they do set the interview_warning out parameter
3120 -- to TRUE.
3121 --
3122 OPEN csr_interviews;
3123 FETCH csr_interviews INTO l_dummy;
3124 --
3125 IF csr_interviews%FOUND THEN
3126 --
3127 p_interview_warning := TRUE;
3128 --
3129 ELSE
3130 --
3131 p_interview_warning := FALSE;
3132 --
3133 END IF;
3134 --
3135 CLOSE csr_interviews;
3136 --
3137 hr_utility.set_location(l_proc,90);
3138 --
3139 OPEN csr_reviews_or_events(p_type =>'I');
3140 FETCH csr_reviews_or_events into l_dummy;
3141 --
3142 IF csr_reviews_or_events%FOUND THEN
3143 --
3144 p_review_warning := TRUE;
3145 --
3146 ELSE
3147 --
3148 p_review_warning := FALSE;
3149 --
3150 END IF;
3151 --
3152 CLOSE csr_reviews_or_events;
3153 --
3154 hr_utility.set_location(l_proc,100);
3155 --
3156 -- Check to see if the CWK is the recruiter for any vacancies.
3157 -- If they do set the vacancy_warning out parameter
3158 -- to TRUE.
3159 --
3160 OPEN csr_vacancy;
3161 FETCH csr_vacancy INTO l_dummy;
3162 --
3163 IF csr_vacancy%FOUND THEN
3164 --
3165 p_vacancy_warning := TRUE;
3166 --
3167 ELSE
3168 --
3169 p_vacancy_warning := FALSE;
3170 --
3171 END IF;
3172 --
3173 CLOSE csr_vacancy;
3174 --
3175 hr_utility.set_location(l_proc,110);
3176 --
3177 -- Check to see if the CWK has any requisitions.
3178 -- If they do set the requisition_warning out parameter
3179 -- to TRUE.
3180 --
3181 OPEN csr_requisition;
3182 FETCH csr_requisition INTO l_dummy;
3183 --
3184 IF csr_requisition%FOUND THEN
3185 --
3186 p_requisition_warning := TRUE;
3187 --
3188 ELSE
3189 --
3190 p_requisition_warning := FALSE;
3191 --
3192 END IF;
3193 --
3194 CLOSE csr_requisition;
3195 --
3196 hr_utility.set_location(l_proc,120);
3197 --
3198 -- Check to see if the CWK has any budget values.
3199 -- If they do set the budget_warning out parameter
3200 -- to TRUE.
3201 --
3202 OPEN csr_budget_values;
3203 FETCH csr_budget_values into l_dummy;
3204 --
3205 IF csr_budget_values%FOUND THEN
3206 --
3207 p_budget_warning := TRUE;
3208 --
3209 ELSE
3210 --
3211 p_budget_warning := FALSE;
3212 --
3213 END IF;
3214 --
3215 CLOSE csr_budget_values;
3216 --
3217 hr_utility.set_location(l_proc,130);
3218 --
3219 OPEN csr_payment;
3220 FETCH csr_payment INTO l_dummy;
3221 --
3222 -- Check to see if the CWK has any personal payment
3223 -- methods values.If they do set the budget_warning
3224 -- out parameter to TRUE.
3225 --
3226 IF csr_payment%FOUND THEN
3227 --
3228 p_payment_warning := TRUE;
3229 --
3230 ELSE
3231 --
3232 p_payment_warning := FALSE;
3233 --
3234 END IF;
3235 --
3236 CLOSE csr_payment;
3237 --
3238 hr_utility.set_location(l_proc,130);
3239 --
3240 EXCEPTION
3241 --
3242 WHEN hr_utility.hr_error THEN
3243 raise;
3244 --
3245 WHEN OTHERS THEN
3246 --
3247 hr_utility.oracle_error(sqlcode);
3248 hr_utility.raise_error;
3249 --
3250 END pre_cancel_placement_checks;
3251 --
3252 /*===========================================================================*
3253 | |
3254 | DO_CANCEL_PLACEMENT |
3255 | |
3256 *============================================================================*/
3257 --
3258 PROCEDURE do_cancel_placement
3259 (p_person_id IN per_people_f.person_id%TYPE
3260 ,p_business_group_id IN per_people_f.business_group_id%TYPE
3261 ,p_effective_date IN DATE
3262 ,p_date_start IN DATE) AS
3263 --
3264 -- Declare local variables
3265 --
3266 l_proc VARCHAR2(72) := g_package||'do_cancel_placement';
3267 --
3268 l_assignment_id NUMBER;
3269 l_end_of_time DATE := hr_general.end_of_time;
3270 l_effective_date DATE;
3271 l_rowid ROWID;
3272 l_dummy_id NUMBER;
3273 l_person_type_id NUMBER;
3274 l_date_start DATE;
3275 l_person_end_date DATE;
3276 l_pop_back_to_back BOOLEAN;
3277 l_pos_back_to_back BOOLEAN;
3278 l_new_person_found BOOLEAN;
3279 l_person_rec_found BOOLEAN;
3280 l_effective_start_date DATE := NULL;
3281 l_effective_end_date DATE := NULL;
3282 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
3283 l_object_version_number per_person_type_usages_f.object_version_number%TYPE;
3284 c_effective_start_date date; --Added for the bug 6460093
3285 --
3286 -- events cursor
3287 --
3288 CURSOR csr_events IS
3289 SELECT ROWID
3290 FROM per_bookings pb
3291 WHERE (pb.business_group_id = p_business_group_id OR
3292 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
3293 AND pb.person_id = p_person_id
3294 AND EXISTS (SELECT 'row exists'
3295 FROM per_events pe
3296 WHERE pe.business_group_id + 0 = p_business_group_id
3297 AND pe.event_id = pb.event_id
3298 AND pe.event_or_interview in ('I','E')
3299 AND pe.date_start >= l_date_start);
3300 --
3301 -- requisitions cursor
3302 --
3303 CURSOR csr_requisitions IS
3304 SELECT ROWID
3305 FROM per_requisitions pr
3306 WHERE (pr.business_group_id = p_business_group_id OR
3307 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
3308 AND pr.person_id = p_person_id;
3309 --
3310 -- budget_values cursor
3311 --
3312 CURSOR csr_budget_values IS
3313 SELECT ROWID
3314 FROM per_assignment_budget_values_f pab
3315 WHERE pab.business_group_id = p_business_group_id and
3316 EXISTS (SELECT 'budget_values exist'
3317 FROM per_all_assignments_f paf
3318 WHERE pab.business_group_id +0= paf.business_group_id + 0
3319 AND paf.business_group_id +0= p_business_group_id
3320 AND pab.assignment_id = paf.assignment_id
3321 AND paf.person_id = p_person_id
3322 AND paf.period_of_placement_date_start = p_date_start
3323 AND paf.effective_end_date >= l_effective_date
3324 --start for 5987416
3325 --AND pab.effective_end_date >= l_effective_date
3326 )
3327 AND pab.effective_end_date >= l_effective_date;
3328 --end bug 5987416
3329 --
3330 -- payment cursor
3331 --
3332 CURSOR csr_payment IS
3333 SELECT ROWID
3334 FROM pay_personal_payment_methods ppm
3335 WHERE ppm.business_group_id = p_business_group_id
3336 AND EXISTS (SELECT 'exists'
3337 FROM per_all_assignments_f paf
3338 WHERE paf.business_group_id +0= p_business_group_id
3339 AND paf.person_id = p_person_id
3340 AND paf.assignment_id = ppm.assignment_id
3341 AND paf.period_of_placement_date_start = p_date_start
3342 --start bug 5987416
3343 --AND ppm.effective_start_date>= l_effective_date
3344 --end bug 5987416
3345 )
3346 AND ppm.effective_start_date >= l_effective_date;
3347 --
3348 -- supervisor cursor.
3349 --
3350 CURSOR csr_supervisor IS
3351 SELECT ROWID
3352 FROM per_assignments_f p
3353 WHERE (p.business_group_id = p_business_group_id OR
3354 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
3355 AND p.supervisor_id = p_person_id;
3356 --
3357 -- recruiter cursor
3358 --
3359 CURSOR csr_recruiter IS
3360 SELECT ROWID
3361 FROM per_assignments_f p
3362 WHERE p.recruiter_id = p_person_id
3363 AND (p.business_group_id = p_business_group_id OR
3364 NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y');
3365 --
3366 -- vacancies cursor
3367 --
3368 CURSOR csr_vacancies IS
3369 SELECT ROWID
3370 FROM per_vacancies pv
3371 WHERE (pv.business_group_id = p_business_group_id OR
3372 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
3373 AND pv.recruiter_id = p_person_id
3374 AND pv.date_from >= p_date_start;
3375 --
3376 -- comments cursor
3377 --
3378 CURSOR csr_comments IS
3379 SELECT ROWID
3380 FROM hr_comments h
3381 WHERE EXISTS (SELECT 'comments exist'
3382 FROM per_all_assignments_f paf
3383 WHERE h.comment_id = paf.comment_id
3384 AND paf.business_group_id +0 = p_business_group_id
3385 AND paf.person_id = p_person_id
3386 AND paf.period_of_placement_date_start = p_date_start);
3387 --
3388 -- assignments cursor
3389 --
3390 -- VT #438579 03/05/79 added assignment_id
3391 --
3392 CURSOR csr_assignments1 IS
3393 SELECT ROWID,
3394 assignment_id
3395 FROM per_all_assignments_f paf
3396 WHERE paf.business_group_id +0 = p_business_group_id
3397 AND paf.person_id = p_person_id
3398 AND paf.period_of_placement_date_start = p_date_start;
3399 --
3400 -- assignment cursor for entries update
3401 --
3402 CURSOR csr_assignments2 IS
3403 SELECT assignment_id,
3404 effective_start_date,
3405 ROWID
3406 FROM per_all_assignments_f
3407 WHERE person_id = p_person_id
3408 AND business_group_id +0 = p_business_group_id
3409 AND effective_end_date = p_date_start - 1
3410 AND assignment_type = 'C' -- 3194314
3411 FOR UPDATE OF effective_end_date;
3412 --
3413 -- applications cursor
3414 --
3415 CURSOR csr_applications IS
3416 SELECT ROWID
3417 FROM per_applications pap
3418 WHERE EXISTS (SELECT 'row exists'
3419 FROM per_all_assignments_f paf
3420 WHERE paf.person_id = p_person_id
3421 AND paf.business_group_id +0 = p_business_group_id
3422 AND paf.effective_end_date = p_date_start - 1
3423 AND pap.application_id = paf.application_id);
3424 --
3425 -- person cursor
3426 --
3427 CURSOR csr_person IS
3428 SELECT p.rowid,
3429 effective_end_date
3430 FROM per_people_f p
3431 WHERE p.person_id = p_person_id
3432 AND p.effective_start_date >= p_date_start;
3433 --
3434 -- new_person
3435 --
3436 CURSOR csr_new_person IS
3437 SELECT p.rowid
3438 FROM per_people_f p
3439 WHERE p.person_id = p_person_id
3440 AND p.effective_end_date = p_date_start -1;
3441 --
3442 CURSOR csr_assignment_rate_values IS
3443 SELECT pgr.rowid
3444 FROM pay_grade_rules_f pgr
3445 WHERE EXISTS (SELECT 'X'
3446 FROM per_assignments_f paf
3447 WHERE pgr.grade_or_spinal_point_id = paf.assignment_id
3448 and paf.business_group_id + 0 = p_business_group_id
3449 AND paf.person_id = p_person_id);
3450 --
3451 CURSOR csr_periods_of_placement IS
3452 SELECT pp.rowid
3453 FROM per_periods_of_placement pp
3454 WHERE pp.person_id = p_person_id
3455 AND pp.date_start = p_date_start;
3456 --
3457 CURSOR csr_pop_back_to_back (p_date_start IN DATE) IS
3458 SELECT pp.period_of_placement_id
3459 FROM per_periods_of_placement pp
3460 WHERE pp.person_id = p_person_id
3461 AND pp.actual_termination_date = p_date_start -1;
3462 --
3463 CURSOR csr_pos_back_to_back (p_date_start IN DATE) IS
3464 SELECT ps.period_of_service_id
3465 FROM per_periods_of_service ps
3466 WHERE ps.person_id = p_person_id
3467 AND ps.actual_termination_date = p_date_start -1;
3468 --
3469
3470 -- Change for the bug 6460093 starts here
3471 CURSOR csr_pdped_start is
3472 SELECT pop.date_start
3473 FROM per_periods_of_placement pop
3474 WHERE pop.person_id = p_person_id
3475 AND p_effective_date between pop.date_start and
3476 nvl(pop.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'));
3477
3478 -- Change for the bug 6460093 ends here
3479
3480 CURSOR csr_get_person_type (p_system_person_type IN CHAR) IS
3481 SELECT person_type_id
3482 FROM per_person_types
3483 WHERE business_group_id = p_business_group_id
3484 AND system_person_type = p_system_person_type
3485 AND default_flag = 'Y';
3486
3487 --
3488 -- Changes start for the bug 7110731
3489
3490 CURSOR csr_get_cwk_type (p_sys_person_type IN CHAR) IS
3491 SELECT person_type_id
3492 FROM per_person_types
3493 WHERE business_group_id = p_business_group_id
3494 AND system_person_type = p_sys_person_type
3495 AND default_flag = 'Y';
3496
3497 l_person_type_id1 number;
3498
3499 -- Changes end for the bug 7110731
3500 --
3501
3502 CURSOR csr_get_ptu_details IS
3503 SELECT ptu.person_type_usage_id,
3504 ptu.object_version_number
3505 FROM per_person_type_usages_f ptu,
3506 per_person_types pt
3507 WHERE ptu.person_id = p_person_id
3508 AND pt.person_type_id = ptu.person_type_Id
3509 AND pt.system_person_type = 'CWK'
3510 AND p_effective_date BETWEEN ptu.effective_start_date
3511 AND ptu.effective_end_date;
3512 --
3513 cursor csr_asg_sec is
3514 select paf.assignment_id, paf.effective_start_date
3515 from per_all_assignments_f paf
3516 where paf.person_id = p_person_id
3517 and paf.business_group_id +0 = p_business_group_id
3518 and paf.assignment_type <> 'B'; -- Added For Bug # 6630290
3519
3520 BEGIN
3521 --
3522 hr_utility.set_location('Entering : '||l_proc,10);
3523 hr_utility.set_location(l_proc||' date_start = '||p_date_start,11);
3524 hr_utility.set_location(l_proc||' eff date = '||p_effective_date,12);
3525 --
3526 l_effective_date := TRUNC(p_effective_date);
3527 l_date_start := TRUNC(p_effective_date);
3528 --
3529 hr_utility.set_location(l_proc,20);
3530 --
3531 FOR supervisor_rec IN csr_supervisor LOOP
3532 --
3533 hr_utility.set_location(l_proc,60);
3534 --
3535 UPDATE per_all_assignments_f paf
3536 SET paf.supervisor_id = NULL
3537 WHERE paf.rowid = supervisor_rec.rowid;
3538 --
3539 IF sql%notfound THEN
3540 --
3541 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3542 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3543 hr_utility.set_message_token('STEP',1);
3544 hr_utility.raise_error;
3545 --
3546 END IF;
3547 --
3548 END LOOP;
3549 --
3550 hr_utility.set_location(l_proc,70);
3551 --
3552 FOR recruiter_rec IN csr_recruiter LOOP
3553 --
3554 hr_utility.set_location(l_proc,80);
3555 --
3556 UPDATE per_all_assignments_f paf
3557 SET paf.recruiter_id = NULL
3558 WHERE paf.rowid = recruiter_rec.rowid;
3559 --
3560 IF SQL%NOTFOUND THEN
3561 --
3562 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3563 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3564 hr_utility.set_message_token('STEP',2);
3565 hr_utility.raise_error;
3566 --
3567 END IF;
3568 --
3569 END LOOP;
3570 --
3571 hr_utility.set_location(l_proc,90);
3572 --
3573 FOR vacancies_rec IN csr_vacancies LOOP
3574 --
3575 hr_utility.set_location(l_proc,100);
3576 --
3577 UPDATE per_all_vacancies pv
3578 SET pv.recruiter_id = NULL
3579 WHERE pv.rowid = vacancies_rec.rowid;
3580 --
3581 IF SQL%NOTFOUND THEN
3582 --
3583 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3584 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3585 hr_utility.set_message_token('STEP',3);
3586 hr_utility.raise_error;
3587 --
3588 END IF;
3589 --
3590 END LOOP;
3591 --
3592 hr_utility.set_location(l_proc,110);
3593 --
3594 FOR assignment_rate_rec in csr_assignment_rate_values LOOP
3595 --
3596 hr_utility.set_location(l_proc,120);
3597 --
3598 DELETE from pay_grade_rules_f pgr
3599 WHERE pgr.rowid = assignment_rate_rec.rowid;
3600 --
3601 END LOOP;
3602 --
3603 hr_utility.set_location(l_proc,130);
3604 --
3605 FOR comments_rec IN csr_comments LOOP
3606 --
3607 hr_utility.set_location(l_proc,140);
3608 --
3609 DELETE FROM hr_comments h
3610 WHERE h.rowid = comments_rec.rowid;
3611 --
3612 IF SQL%NOTFOUND THEN
3613 --
3614 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3615 hr_utility.set_message_token('PROCEDURE','cancel_placement');
3616 hr_utility.set_message_token('STEP',4);
3617 hr_utility.raise_error;
3618 --
3619 END IF;
3620 --
3621 END LOOP;
3622 --
3623 hr_utility.set_location(l_proc,150);
3624 --
3625 FOR requisition_rec IN csr_requisitions LOOP
3626 --
3627 hr_utility.set_location(l_proc,160);
3628 --
3629 UPDATE per_requisitions pr
3630 SET pr.person_id = NULL
3631 WHERE pr.rowid = requisition_rec.rowid;
3632 --
3633 IF SQL%NOTFOUND THEN
3634 --
3635 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3636 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3637 hr_utility.set_message_token('STEP',5);
3638 hr_utility.raise_error;
3639 --
3640 END IF;
3641 --
3642 END LOOP;
3643 --
3644 hr_utility.set_location(l_proc,170);
3645 --
3646 FOR events_rec IN csr_events LOOP
3647 --
3648 hr_utility.set_location(l_proc,180);
3649 --
3650 DELETE FROM per_bookings pb
3651 WHERE pb.rowid = events_rec.rowid;
3652 --
3653 IF SQL%NOTFOUND THEN
3654 --
3655 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3656 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3657 hr_utility.set_message_token('STEP',6);
3658 hr_utility.raise_error;
3659 --
3660 END IF;
3661 --
3662 END LOOP;
3663 --
3664 hr_utility.set_location(l_proc,190);
3665 --
3666 FOR budget_rec IN csr_budget_values LOOP
3667 --
3668 hr_utility.set_location(l_proc,200);
3669 --
3670 DELETE FROM per_assignment_budget_values_f pab
3671 WHERE pab.rowid = budget_rec.rowid;
3672 --
3673 IF SQL%NOTFOUND THEN
3674 --
3675 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3676 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3677 hr_utility.set_message_token('STEP',7);
3678 hr_utility.raise_error;
3679 --
3680 END IF;
3681 --
3682 END LOOP budget_value;
3683 --
3684 hr_utility.set_location(l_proc,210);
3685 --
3686 FOR payment_rec IN csr_payment LOOP
3687 --
3688 hr_utility.set_location(l_proc,220);
3689 --
3690 DELETE FROM pay_personal_payment_methods ppm
3691 WHERE ppm.rowid = payment_rec.rowid;
3692 --
3693 IF SQL%NOTFOUND THEN
3694 --
3695 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3696 hr_utility.set_message_token('PROCEDURE','do_cancel_placement');
3697 hr_utility.set_message_token('STEP',8);
3698 hr_utility.raise_error;
3699 --
3700 end if;
3701 --
3702 END LOOP;
3703 --
3704 hr_utility.set_location(l_proc,230);
3705 --
3706 FOR assignment1_rec IN csr_assignments1 LOOP
3707 --
3708 hr_utility.set_location(l_proc,240);
3709 --
3710 DELETE FROM per_spinal_point_placements_f spp
3711 WHERE spp.assignment_id = assignment1_rec.assignment_id;
3712 --
3713 hr_utility.set_location(l_proc,250);
3714 --
3715 DELETE FROM pay_cost_allocations_f pca
3716 WHERE pca.assignment_id = assignment1_rec.assignment_id;
3717 --
3718 hr_utility.set_location(l_proc,260);
3719 --
3720 DELETE FROM per_all_assignments_f paf
3721 WHERE paf.rowid = assignment1_rec.rowid;
3722 --
3723 IF SQL%NOTFOUND THEN
3724 --
3725 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3726 hr_utility.set_message_token('PROCEDURE','cancel_placement');
3727 hr_utility.set_message_token('STEP',9);
3728 hr_utility.raise_error;
3729 --
3730 END IF;
3731 --
3732 END LOOP;
3733 --
3734 hr_utility.set_location(l_proc,270);
3735 --
3736 FOR applicantions_rec IN csr_applications LOOP
3737 --
3738 hr_utility.set_location(l_proc,280);
3739 --
3740 UPDATE per_applications pap
3741 SET pap.date_end = NULL
3742 WHERE pap.rowid = applicantions_rec.rowid;
3743 --
3744 IF SQL%NOTFOUND THEN
3745 --
3746 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3747 hr_utility.set_message_token('PROCEDURE','cancel_placement');
3748 hr_utility.set_message_token('STEP',10);
3749 hr_utility.raise_error;
3750 --
3751 END IF;
3752 --
3753 END LOOP;
3754 --
3755 hr_utility.set_location(l_proc,320);
3756 --
3757 -- Change for the bug 6460093 starts here
3758 OPEN csr_pdped_start;
3759 FETCH csr_pdped_start into c_effective_start_date;
3760 CLOSE csr_pdped_start;
3761 -- Change for the bug 6460093 ends here
3762
3763 OPEN csr_periods_of_placement;
3764 --
3765 LOOP
3766 --
3767 hr_utility.set_location(l_proc,330);
3768 --
3769 FETCH csr_periods_of_placement INTO l_rowid;
3770 --
3771 EXIT WHEN csr_periods_of_placement%NOTFOUND;
3772 --
3773 DELETE FROM per_periods_of_placement
3774 WHERE rowid = l_rowid;
3775 --
3776 IF SQL%NOTFOUND THEN
3777 --
3778 CLOSE csr_periods_of_placement;
3779 --
3780 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3781 hr_utility.set_message_token('PROCEDURE','cancel_placement');
3782 hr_utility.set_message_token('STEP',11);
3783 hr_utility.raise_error;
3784 --
3785 END IF;
3786 --
3787 END LOOP;
3788 --
3789 hr_utility.set_location(l_proc,340);
3790 --
3791 IF csr_periods_of_placement%ROWCOUNT <1 THEN
3792 --
3793 CLOSE csr_periods_of_placement;
3794 --
3795 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3796 hr_utility.set_message_token('PROCEDURE','cancel_placement');
3797 hr_utility.set_message_token('STEP',12);
3798 hr_utility.raise_error;
3799 --
3800 END IF;
3801 --
3802 CLOSE csr_periods_of_placement;
3803 --
3804 hr_utility.set_location(l_proc,350);
3805 --
3806 -- Check that there is not a previous period of placement
3807 -- record that ended the day before the current period of placement
3808 -- record. I.e is a back to back hire.
3809 --
3810 OPEN csr_pop_back_to_back(p_date_start => p_date_start);
3811 FETCH csr_pop_back_to_back INTO l_dummy_id;
3812 --
3813 IF csr_pop_back_to_back%FOUND THEN
3814 --
3815 hr_utility.set_location(l_proc,353);
3816 --
3817 l_pop_back_to_back := TRUE;
3818 --
3819 ELSE
3820 --
3821 -- As there no back to back records for period of placements
3822 -- check that there is not a record for period of service
3823 -- that ends the day before the period of placement record
3824 -- being canceled.
3825 --
3826 hr_utility.set_location(l_proc,355);
3827 --
3828 l_pop_back_to_back := FALSE;
3829 --
3830 OPEN csr_pos_back_to_back (p_date_start => p_date_start);
3831 FETCH csr_pos_back_to_back INTO l_dummy_id;
3832 --
3833 IF csr_pos_back_to_back%FOUND THEN
3834 --
3835 hr_utility.set_location(l_proc,356);
3836 --
3837 l_pos_back_to_back := TRUE;
3838 --
3839 ELSE
3840 --
3841 hr_utility.set_location(l_proc,357);
3842 --
3843 l_pos_back_to_back := FALSE;
3844 --
3845 END IF;
3846 --
3847 CLOSE csr_pos_back_to_back;
3848 --
3849 END IF;
3850 --
3851 CLOSE csr_pop_back_to_back;
3852 --
3853 if NOT l_pop_back_to_back then -- 3194314
3854 FOR assignment2_rec IN csr_assignments2 LOOP
3855 --
3856 hr_utility.set_location(l_proc,300);
3857 --
3858 UPDATE per_all_assignments_f paf
3859 SET paf.effective_end_date = l_end_of_time
3860 WHERE paf.rowid = assignment2_rec.rowid;
3861 --
3862 hr_utility.set_location(l_proc,310);
3863 --
3864 END LOOP;
3865 end if;
3866 --
3867 hr_utility.set_location(l_proc,360);
3868 --
3869 FOR person_rec IN csr_person LOOP
3870 --
3871 l_person_rec_found := TRUE;
3872 --
3873 hr_utility.set_location(l_proc,370);
3874 --
3875 IF l_pop_back_to_back THEN
3876 --
3877 hr_utility.set_location(l_proc,380);
3878 --
3879 IF person_rec.effective_end_date = hr_general.end_of_time THEN
3880 --
3881 hr_utility.set_location(l_proc,390);
3882 --
3883 OPEN csr_get_person_type (p_system_person_type => 'EX_CWK');
3884 FETCH csr_get_person_type INTO l_person_type_id;
3885 --
3886 CLOSE csr_get_person_type;
3887 --
3888
3889 --changes start for bug 7110731
3890 OPEN csr_get_cwk_type (p_sys_person_type => 'CWK');
3891 FETCH csr_get_cwk_type INTO l_person_type_id1;
3892 CLOSE csr_get_cwk_type;
3893
3894 UPDATE per_person_type_usages_f
3895 SET person_type_id = l_person_type_id
3896 WHERE person_id = p_person_id
3897 AND person_type_id= l_person_type_id1
3898 AND p_date_start BETWEEN effective_start_date
3899 AND effective_end_date;
3900 --changes end for bug 7110731
3901
3902 UPDATE per_people_f -- 3194314
3903 SET current_npw_flag = null
3904 ,effective_start_date = p_date_start -- in case DT udpates exist
3905 WHERE rowid = person_rec.rowid;
3906 --
3907 ELSE -- #1998140
3908 --
3909 hr_utility.set_location(l_proc,400);
3910 --
3911 DELETE FROM per_people_f
3912 WHERE rowid = person_rec.rowid;
3913 --
3914 END IF;
3915 --
3916
3917 ELSIF l_pos_back_to_back then -- 3194314
3918 -- this is a back-to-back with Employee/Cwk
3919
3920 IF person_rec.effective_end_date = hr_general.end_of_time THEN
3921
3922 -- it should restore the EX_EMP record instead of removing it
3923 hr_utility.set_location(l_proc,405);
3924 --
3925 UPDATE per_people_f
3926 SET npw_number = null,
3927 effective_start_date = p_date_start, -- in case DT updates exist
3928 current_npw_flag = null,
3929 per_information7 = 'INCL'
3930 WHERE rowid = person_rec.rowid;
3931
3932 --
3933 ELSE -- #1998140
3934 --
3935 hr_utility.set_location(l_proc,406);
3936 --
3937 DELETE FROM per_people_f
3938 WHERE rowid = person_rec.rowid;
3939 --
3940 END IF;
3941 --
3942 -- << 3194314
3943
3944 ELSE
3945 --
3946 hr_utility.set_location(l_proc,410);
3947 --
3948 DELETE FROM per_people_f
3949 WHERE rowid = person_rec.rowid;
3950 --
3951 END IF;
3952 --
3953 hr_utility.set_location(l_proc,420);
3954 --
3955 IF SQL%NOTFOUND THEN
3956 --
3957 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3958 hr_utility.set_message_token('PROCEDURE','cancel_placement');
3959 hr_utility.set_message_token('STEP',13);
3960 hr_utility.raise_error;
3961 --
3962 END IF;
3963 --
3964 END LOOP;
3965 --
3966 hr_utility.set_location(l_proc,430);
3967 --
3968 IF NOT l_person_rec_found THEN
3969 --
3970 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3971 hr_utility.set_message_token('PROCEDURE','cancel_placement');
3972 hr_utility.set_message_token('STEP',14);
3973 hr_utility.raise_error;
3974 --
3975 END IF;
3976 --
3977 hr_utility.set_location(l_proc,440);
3978 --
3979 if NOT l_pos_back_to_back then -- 3194314 this should not get executed if b2b emp/cwk
3980
3981 FOR new_person_rec IN csr_new_person LOOP
3982
3983 --
3984 hr_utility.set_location(l_proc,450);
3985 --
3986 l_new_person_found := TRUE;
3987 --
3988 IF NOT l_pop_back_to_back THEN
3989 --
3990 hr_utility.set_location(l_proc,460);
3991 --
3992 UPDATE per_people_f
3993 SET effective_end_date = l_end_of_time
3994 WHERE rowid = new_person_rec.rowid;
3995 --
3996 END IF;
3997 --
3998 hr_utility.set_location(l_proc,470);
3999 --
4000 IF sql%ROWCOUNT <1 then
4001 --
4002 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4003 hr_utility.set_message_token('PROCEDURE','cancel_placement');
4004 hr_utility.set_message_token('STEP',15);
4005 hr_utility.raise_error;
4006 --
4007 END IF;
4008 --
4009 END LOOP;
4010 end if; -- << 3194314
4011
4012 --
4013 hr_utility.set_location(l_proc,480);
4014 --
4015 IF NOT l_new_person_found THEN
4016 --
4017 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
4018 hr_utility.set_message_token('PROCEDURE','cancel_placement');
4019 hr_utility.set_message_token('STEP',16);
4020 hr_utility.raise_error;
4021 --
4022 END IF;
4023 --
4024 -- If there are no back to back records for
4025 -- period of placements or period of service then
4026 -- cancel the current CWK placement record
4027 --
4028 IF NOT l_pop_back_to_back AND
4029 NOT l_pos_back_to_back THEN
4030 --
4031 hr_utility.set_location(l_proc,490);
4032 --
4033 hr_per_type_usage_internal.cancel_person_type_usage
4034 (p_effective_date => l_date_start
4035 ,p_person_id => p_person_id
4036 ,p_system_person_type => 'CWK');
4037 --
4038
4039 --Added for the bug 6460093
4040 --This finds out any system person type of OTHER records
4041 --which is end dated while creating a placement
4042 --and updates the end date to end of time while
4043 --cancelling the placement
4044
4045 upd_person_type_usage_end_date(c_effective_start_date-1
4046 ,p_person_id
4047 ,p_system_person_type => 'OTHER');
4048
4049 --Change for the bug 6460093 ends here
4050
4051 -- If there are back to back records for period of service
4052 -- then delete the current CWK record from per_person_type_usages_f
4053 -- table.
4054 --
4055 -- bug fix 6992346
4056 -- If there are back to back records for period of service
4057 -- and period of placements then
4058 elsif NOT l_pop_back_to_back AND l_pos_back_to_back THEN
4059 hr_utility.set_location(l_proc,491);
4060 hr_utility.set_location('l_date_start '||l_date_start,491);
4061 --
4062 hr_per_type_usage_internal.cancel_person_type_usage
4063 (p_effective_date => l_date_start
4064 ,p_person_id => p_person_id
4065 ,p_system_person_type => 'CWK');
4066
4067
4068 ELSIF l_pos_back_to_back and l_pop_back_to_back THEN
4069 --ELSIF l_pos_back_to_back THEN
4070 --
4071 hr_utility.set_location(l_proc,500);
4072 --
4073 OPEN csr_get_ptu_details;
4074 FETCH csr_get_ptu_details INTO l_person_type_usage_id,
4075 l_object_version_number;
4076 --
4077 IF csr_get_ptu_details%FOUND THEN
4078 --
4079 hr_utility.set_location(l_proc,510);
4080 --
4081 hr_per_type_usage_internal.delete_person_type_usage
4082 (p_validate => FALSE
4083 ,p_person_type_usage_id => l_person_type_usage_id
4084 ,p_effective_date => p_effective_date
4085 ,p_datetrack_mode => hr_api.g_zap
4086 ,p_object_version_number => l_object_version_number
4087 ,p_effective_start_date => l_effective_start_date
4088 ,p_effective_end_date => l_effective_end_date);
4089 --
4090 END IF;
4091 --
4092 END IF;
4093 -- end of fix 6992346
4094 --
4095 per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);
4096 --
4097 for asg_sec_rec in csr_asg_sec loop
4098 --
4099 hr_utility.set_location(l_proc,300);
4100 -- do some security maintenance.
4101 -- reset the security access(per_person_list) for this assignment
4102 hr_security_internal.add_to_person_list(
4103 p_effective_date => asg_sec_rec.effective_start_date
4104 ,p_assignment_id => asg_sec_rec.assignment_id);
4105 --
4106 end loop;
4107 --
4108 hr_utility.set_location('Leaving : '||l_proc,999);
4109 --
4110 END do_cancel_placement;
4111 --
4112 -- ---------------------------------------------------------------------------
4113 -- |---------------------< return_legislation_code >-------------------------|
4114 -- ---------------------------------------------------------------------------
4115 --
4116 FUNCTION return_legislation_code
4117 (p_person_id IN NUMBER ) RETURN VARCHAR2 IS
4118 --
4119 -- Declare cursor
4120 --
4121 CURSOR csr_leg_code IS
4122 SELECT pbg.legislation_code
4123 FROM per_business_groups pbg
4124 , per_people_f per
4125 WHERE per.person_id = p_person_id
4126 AND pbg.business_group_id = per.business_group_id;
4127 --
4128 -- Declare local variables
4129 --
4130 l_legislation_code VARCHAR2(150);
4131 l_proc VARCHAR2(72) := g_package||'return_legislation_code';
4132 --
4133 BEGIN
4134 --
4135 hr_utility.set_location('Entering:'|| l_proc, 10);
4136 --
4137 -- Ensure that all the mandatory parameter are not null
4138 --
4139 hr_api.mandatory_arg_error
4140 (p_api_name => l_proc
4141 ,p_argument => 'person_id'
4142 ,p_argument_value => p_person_id);
4143 --
4144 OPEN csr_leg_code;
4145 FETCH csr_leg_code INTO l_legislation_code;
4146 --
4147 IF csr_leg_code%notfound THEN
4148 --
4149 -- The primary key is invalid therefore we must error
4150 --
4151 CLOSE csr_leg_code;
4152 --
4153 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
4154 fnd_message.raise_error;
4155 --
4156 END IF;
4157 --
4158 hr_utility.set_location(l_proc,20);
4159 --
4160 CLOSE csr_leg_code;
4161 --
4162 hr_utility.set_location(' Leaving:'|| l_proc, 999);
4163 --
4164 RETURN l_legislation_code;
4165 --
4166 END return_legislation_code;
4167 --
4168 END per_cancel_hire_or_apl_pkg;