[Home] [Help]
PACKAGE BODY: APPS.PER_APPLICATIONS_PKG
Source
1 PACKAGE BODY PER_APPLICATIONS_PKG as
2 /* $Header: peapp01t.pkb 120.0 2005/05/31 05:20:53 appldev noship $ */
3 /* =========================================================================
4 Name
5 per_applications_pkg
6 Purpose
7 Supports the Termination Details Block (APL) in the form
8 PERWSTAP - Terminate Applicant.
9 ==========================================================================
10 */
11 -----------------------------------------------------------------------------
12 -- Name --
13 -- get_prev_ass_type_id --
14 -- Purpose --
15 -- to populate a field in the F4 form PERWSTAP, needed for the procedure --
16 -- del_letter_term when terminating an applicant. --
17 -- Arguments
18 -- see below
19 -- Notes --
20 -----------------------------------------------------------------------------
21 FUNCTION get_prev_ass_type_id(P_Business_Group_id NUMBER,
22 p_person_id NUMBER,
23 p_application_id NUMBER,
24 p_date_end DATE) return NUMBER IS
25
26 CURSOR c_get_ass_type IS
27 SELECT a.assignment_status_type_id
28 FROM per_assignments_f a
29 WHERE a.person_id = p_person_id
30 AND A.business_group_id + 0 = p_business_group_id
31 AND A.APPLICATION_ID = p_application_id
32 AND p_date_end between A.EFFECTIVE_START_DATE
33 and A.EFFECTIVE_END_DATE; --
34 --
35 v_prev_asg_status_id NUMBER(15);
36 --
37 BEGIN
38 --
39 OPEN c_get_ass_type;
40 FETCH c_get_ass_type INTO v_prev_asg_status_id;
41 CLOSE c_get_ass_type;
42 RETURN v_prev_asg_status_id;
43 --
44 END get_prev_ass_type_id;
45
46 -------------------------------------------------------------------------------------------------------
47 -------------------------------------------------------------------------------------------------------
48 -- PRIVATE PROCEDURE
49 -- Name
50 -- term-update_ass_bud_val
51 -- Purpose
52 -- Required due to the date tracking of the assignment budget values table.
53 -- To delete all assignment budget values where they start after the assignment termination
54 -- end date.
55 -- Also for the row where the termination date is between the start and end dates,the assignment budget value's effective
56 -- end date will need to be changed to reflect the change of the assignment's effective end date.
57 -- Arguments
58 -- see below
59 --
60 -- Notes
61 -- Although this could have been included within the Hr_Assignments package due to the deletion of the
62 -- assignment being made within this package it was felt that this proc should also be included in here.
63 --
64 -- SASmith 17-APR-1998
65 -------------------------------------------------------------------------------------------------------
66 PROCEDURE term_update_ass_bud_val(p_application_id NUMBER
67 ,p_person_id NUMBER
68 ,p_business_group_id NUMBER
69 ,p_date_end DATE
70 ,p_last_updated_by NUMBER
71 ,p_last_update_login NUMBER) IS
72
73 --
74 p_del_flag VARCHAR2(1) := 'N';
75
76 --
77 -- Look at all assignments for the application to be terminated.
78 -- Check for and delete any assignment budget value rows where they start after the termination
79 -- end date.
80 --
81 --
82 -- Start of fix for WWBUG 1408379
83 --
84 cursor c1 is
85 select abv1.*
86 from PER_ALL_ASSIGNMENTS_F paa,
87 per_assignment_budget_values_f abv1
88 where paa.APPLICATION_ID = p_application_id
89 and paa.PERSON_ID = p_person_id
90 and paa.business_group_id = p_business_group_id
91 and paa.ASSIGNMENT_TYPE = 'A'
92 and paa.assignment_id = abv1.assignment_id
93 and p_date_end
94 between abv1.effective_start_date
95 and abv1.effective_end_date;
96 --
97 l_c1 c1%rowtype;
98 l_old ben_abv_ler.g_abv_ler_rec;
99 l_new ben_abv_ler.g_abv_ler_rec;
100 --
101 -- End of fix for WWBUG 1408379
102 --
103 BEGIN
104 p_del_flag := 'N';
105
106 hr_utility.set_location('PER_APPLICATIONS_PKG.term_update_ass_bud_val',5);
107 hr_utility.set_location(p_date_end,6);
108
109 BEGIN
110
111 select 'Y'
112 into p_del_flag
113 from sys.dual
114 where exists (
115 Select null
116 from PER_ALL_ASSIGNMENTS_F paa, per_assignment_budget_values_f abv
117 where paa.APPLICATION_ID = p_application_id
118 and paa.PERSON_ID = p_person_id
119 and paa.business_group_id + 0 = p_business_group_id
120 and paa.ASSIGNMENT_TYPE = 'A'
121 and paa.assignment_id = abv.assignment_id
122 and abv.effective_start_date > p_date_end);
123
124
125 EXCEPTION
126 WHEN NO_DATA_FOUND THEN NULL;
127 END;
128 --
129 IF p_del_flag = 'Y' THEN
130 --
131 hr_utility.set_location('PER_APPLICATIONS_PKG.term_update_ass_bud_val',10);
132 --
133 delete from per_assignment_budget_values_f abv
134 where exists (
135 Select null
136 from PER_ALL_ASSIGNMENTS_F paa, per_assignment_budget_values_f abv1
137 where paa.APPLICATION_ID = p_application_id
138 and paa.PERSON_ID = p_person_id
139 and paa.business_group_id + 0 = p_business_group_id
140 and paa.ASSIGNMENT_TYPE = 'A'
141 and paa.assignment_id = abv1.assignment_id
142 and abv1.assignment_id = abv.assignment_id
143 and abv1.effective_start_date > p_date_end
144 and abv1.effective_start_date = abv.effective_start_date);
145
146
147 END IF;
148
149 p_del_flag := 'N';
150 --
151 hr_utility.set_location('PER_APPLICATIONS_PKG.term_update_ass_bud_val',15);
152 --
153 -- Check for and update any assignment budget value row(s) where the termination end date occurs during the
154 -- life of the assignment budget value row(s).
155 --
156
157 BEGIN
158
159 select 'Y'
160 into p_del_flag
161 from sys.dual
162 where exists (
163 Select null
164 from PER_ALL_ASSIGNMENTS_F paa, per_assignment_budget_values_f abv
165 where paa.APPLICATION_ID = p_application_id
166 and paa.PERSON_ID = p_person_id
167 and paa.business_group_id + 0 = p_business_group_id
168 and paa.ASSIGNMENT_TYPE = 'A'
169 and paa.assignment_id = abv.assignment_id
170 and p_date_end between abv.effective_start_date and abv.effective_end_date);
171
172
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN NULL;
175 END;
176
177 IF p_del_flag = 'Y' THEN
178 --
179 hr_utility.set_location('PER_APPLICATIONS_PKG.term_update_ass_bud_val',20);
180 --
181 --
182 -- Start of fix for WWBUG 1408379
183 --
184 open c1;
185 --
186 loop
187 --
188 fetch c1 into l_c1;
189 exit when c1%notfound;
190 --
191 update per_assignment_budget_values_f abv
192 set abv.effective_end_date = p_date_end,
193 abv.last_updated_by = P_LAST_UPDATED_BY,
194 abv.last_update_login = P_LAST_UPDATE_LOGIN,
195 abv.last_update_date = sysdate
196 where abv.assignment_budget_value_id=l_c1.assignment_budget_value_id
197 and abv.effective_start_date = l_c1.effective_start_date
198 and abv.effective_end_date = l_c1.effective_end_date;
199 --
200 l_old.assignment_id := l_c1.assignment_id;
201 l_old.business_group_id := l_c1.business_group_id;
202 l_old.value := l_c1.value;
203 l_old.assignment_budget_value_id := l_c1.assignment_budget_value_id;
204 l_old.effective_start_date := l_c1.effective_start_date;
205 l_old.effective_end_date := l_c1.effective_end_date;
206 l_new.assignment_id := l_c1.assignment_id;
207 l_new.business_group_id := l_c1.business_group_id;
208 l_new.value := l_c1.value;
209 l_new.assignment_budget_value_id := l_c1.assignment_budget_value_id;
210 l_new.effective_start_date := l_c1.effective_start_date;
211 l_new.effective_end_date := p_date_end;
212 --
213 ben_abv_ler.ler_chk(p_old => l_old,
214 p_new => l_new,
215 p_effective_date => l_c1.effective_start_date);
216 --
217 end loop;
218 --
219 close c1;
220 --
221 END IF;
222
223 END term_update_ass_bud_val;
224
225 ----------------------------------------------------------------------------------------------
226
227 ----------------------------------------------------------------------------------------------
228 -- PRIVATE PROCEDURE
229 -- Name
230 -- cancel_update_ass_bud
231 -- Purpose
232 -- Required due to the date tracking of the assignment budget values table.
233 -- To cancel the termination of the assignment budget values. The requirement is for the LAST assignment
234 -- budget value row's effective end date to be opened out to be the same as the assignment's
235 -- effective end date. This is required where the termination is being cancelled.
236 --
237 -- Arguments
238 -- see below
239 -- Notes
240 -- Although this could have been included within the Hr_Assignments package due to the deletion of the
241 -- assignment being made within this package it was felt that this proc should also be included in here.
242 --
243 --
244 -- SASmith 17-APR-1998
245 -----------------------------------------------------------------------------------------------
246 PROCEDURE cancel_update_ass_bud_val(p_application_id NUMBER
247 ,p_person_id NUMBER
248 ,p_business_group_id NUMBER
249 ,p_date_end DATE
250 ,p_end_of_time DATE
251 ,p_last_updated_by NUMBER
252 ,p_last_update_login NUMBER) IS
253
254 --
255 p_del_flag VARCHAR2(1) := 'N';
256
257 --
258 --
259 -- Start of fix for WWBUG 1408379
260 --
261 cursor c1 is
262 select abv1.*
263 from PER_ALL_ASSIGNMENTS_F paa,
264 per_assignment_budget_values_f abv1
265 where paa.APPLICATION_ID = p_application_id
266 and paa.PERSON_ID = p_person_id
267 and paa.business_group_id = p_business_group_id
268 and paa.ASSIGNMENT_TYPE = 'A'
269 and abv1.assignment_id = paa.assignment_id
270 and abv1.effective_end_date = p_date_end;
271 --
272 l_c1 c1%rowtype;
273 l_old ben_abv_ler.g_abv_ler_rec;
274 l_new ben_abv_ler.g_abv_ler_rec;
275 --
276 -- End of fix for WWBUG 1408379
277 --
278 BEGIN
279 p_del_flag := 'N';
280
281 hr_utility.set_location('PER_APPLICATIONS_PKG.cancel_update_ass_bud_val',5);
282
283 --
284 BEGIN
285
286 select 'Y'
287 into p_del_flag
288 from sys.dual
289 where exists (
290 Select null
291 from PER_ALL_ASSIGNMENTS_F paa
292 where paa.APPLICATION_ID = p_application_id
293 and paa.PERSON_ID = p_person_id
294 and paa.business_group_id + 0 = p_business_group_id
295 and paa.ASSIGNMENT_TYPE = 'A'
296 and exists (Select abv.assignment_id
297 from per_assignment_budget_values_f abv
298 where abv.assignment_id = paa.assignment_id
299 and abv.effective_end_date = p_date_end));
300
301 EXCEPTION
302 WHEN NO_DATA_FOUND THEN NULL;
303 END;
304
305 IF p_del_flag = 'Y' THEN
306 --
307 hr_utility.set_location('PER_APPLICATIONS_PKG.cancel_update_ass_bud_val',10);
308 --
309 --
310 -- Start of fix for WWBUG 1408379
311 --
312 open c1;
313 --
314 loop
315 --
316 fetch c1 into l_c1;
317 exit when c1%notfound;
318 --
319 update per_assignment_budget_values_f abv
320 set abv.effective_end_date = p_end_of_time,
321 abv.last_updated_by = P_LAST_UPDATED_BY,
322 abv.last_update_login = P_LAST_UPDATE_LOGIN,
323 abv.last_update_date = sysdate
324 where abv.assignment_budget_value_id = l_c1.assignment_budget_value_id
328 l_old.assignment_id := l_c1.assignment_id;
325 and abv.effective_start_date = l_c1.effective_start_date
326 and abv.effective_end_date = l_c1.effective_end_date;
327 --
329 l_old.business_group_id := l_c1.business_group_id;
330 l_old.value := l_c1.value;
331 l_old.assignment_budget_value_id := l_c1.assignment_budget_value_id;
332 l_old.effective_start_date := l_c1.effective_start_date;
333 l_old.effective_end_date := l_c1.effective_end_date;
334 l_old.assignment_id := l_c1.assignment_id;
335 l_old.business_group_id := l_c1.business_group_id;
336 l_old.value := l_c1.value;
337 l_old.assignment_budget_value_id := l_c1.assignment_budget_value_id;
338 l_old.effective_start_date := l_c1.effective_start_date;
339 l_old.effective_end_date := p_end_of_time;
340 --
341 ben_abv_ler.ler_chk(p_old => l_old,
342 p_new => l_new,
343 p_effective_date => l_c1.effective_start_date);
344 --
345 end loop;
346 --
347 close c1;
348 --
349 END IF;
350
351 END cancel_update_ass_bud_val;
352
353
354 -------------------------------------------------------------------------------
355
356
357 --
358 -----------------------------------------------------------------------------
359 -- Name --
360 -- del_letter_term --
361 -- Purpose --
362 -- on termination of an applicant's application delete any letter request--
363 -- lines for the applicant's assignments if they exist for assigment status-
364 -- types other than TERM_APL.
365 -- Arguments --
366 -- See below. --
367 -- Notes --
368 -- NB. The applicant status TERM_APL is never held on the applicant's --
369 -- assignment record.
370 -----------------------------------------------------------------------------
371 PROCEDURE del_letter_term(p_person_id NUMBER,
372 p_business_group_id NUMBER,
373 p_date_end DATE,
374 p_application_id NUMBER,
375 P_dummy_asg_stat_id NUMBER) IS
376
377 CURSOR c_letter_stat_exists IS
378 SELECT 1
379 FROM PER_LETTER_GEN_STATUSES PLG
380 WHERE PLG.business_group_id + 0 = p_business_group_id
381 AND PLG.ASSIGNMENT_STATUS_TYPE_ID = P_dummy_asg_stat_id
382 AND PLG.ENABLED_FLAG = 'Y';
383
384 CURSOR c_chk_lines IS
385 SELECT distinct(1)
386 FROM per_letter_request_lines l
387 WHERE L.person_id = p_person_id
388 AND l.business_group_id + 0 = p_business_group_id
389 AND l.assignment_status_type_id = P_dummy_asg_stat_id
390 AND EXISTS
391 (SELECT NULL
392 FROM per_assignments_f A
393 WHERE a.business_group_id + 0 = p_business_group_id
394 AND a.person_id = p_person_id
395 AND a.assignment_status_type_id = P_dummy_asg_stat_id
396 AND a.assignment_type = 'A'
397 AND a.application_id = p_application_id
398 AND a.assignment_id = l.assignment_id);
399
400 CURSOR c_chk_empty_requests IS
401 SELECT 1
402 FROM per_letter_requests r
403 WHERE NOT EXISTS
404 (SELECT NULL
405 FROM per_letter_request_lines L
406 WHERE r.letter_request_id = l.letter_request_id);
407
408
409 ----
410 v_stat_exists NUMBER(1);
411 v_lines_exist NUMBER(1);
412 v_empty_requests NUMBER(1);
413 -----
414
415 BEGIN
416 --
417 OPEN c_letter_stat_exists;
418 FETCH c_letter_stat_exists INTO v_stat_exists;
419 IF c_letter_stat_exists%FOUND THEN
420 CLOSE c_letter_stat_exists;
421 --
422 OPEN c_chk_lines;
423 FETCH c_chk_lines INTO v_lines_exist;
424 IF c_chk_lines%FOUND THEN
425 CLOSE c_chk_lines;
426 DELETE FROM PER_LETTER_REQUEST_LINES l
427 WHERE l.person_id = p_person_id
428 AND l.assignment_status_type_id = P_dummy_asg_stat_id
429 AND l.business_group_id + 0 = p_business_group_id;
430 --
431 OPEN c_chk_empty_requests;
432 FETCH c_chk_empty_requests INTO v_empty_requests;
433 IF c_chk_empty_requests%FOUND THEN
434 CLOSE c_chk_empty_requests;
435 --
436 DELETE FROM per_letter_requests R
437 WHERE r.business_group_id = p_business_group_id
438 AND r.request_status = 'PENDING'
439 AND r.auto_or_manual = 'AUTO'
440 AND NOT EXISTS
441 (SELECT null
442 FROM Per_letter_request_lines l
446 ELSE CLOSE c_chk_empty_requests;
443 WHERE l.letter_request_id = r.letter_request_id
444 AND l.business_group_id + 0 = p_business_group_id);
445 --
447 END IF;
448 ELSE CLOSE c_chk_lines;
449 END IF;
450 ELSE CLOSE c_letter_stat_exists;
451 END IF;
452 --
453 END del_letter_term;
454 -----------------------------------------------------------------------------
455 -- Name --
456 -- insert_letter_term --
457 -- Purpose --
458 -- to insert letter request if needs be and to insert letter request lines-
459 -- when the user specifies a termination status(otional) when doing an --
460 -- applicant termination.
461 -- Arguments --
462 -- See below. --
463 -- Notes --
464 -----------------------------------------------------------------------------
465 PROCEDURE insert_letter_term(P_business_group_id NUMBER,
466 p_application_id NUMBER,
467 p_person_id NUMBER,
468 p_session_date DATE,
469 p_last_updated_by NUMBER,
470 p_last_update_login NUMBER,
471 p_assignment_status_type_id NUMBER ) IS
472
473 CURSOR ck_gen_stats IS
474 SELECT 1
475 FROM per_letter_gen_statuses s
476 WHERE S.business_group_id + 0 = P_business_group_id
477 AND s.assignment_status_type_id = p_assignment_status_type_id
478 AND s.enabled_flag = 'Y';
479
480
481 CURSOR csr_let_req IS
482 SELECT R.LETTER_REQUEST_ID,
483 r.letter_type_id
484 FROM PER_LETTER_REQUESTS R,
485 PER_LETTER_GEN_STATUSES s
486 WHERE r.business_group_id + 0 = p_business_group_id
487 AND s.business_group_id + 0 = p_business_group_id
488 AND r.LETTER_TYPE_ID = S.LETTER_TYPE_ID
489 AND s.ASSIGNMENT_STATUS_TYPE_ID = p_assignment_status_type_id
490 AND s.ENABLED_FLAG = 'Y'
491 AND R.REQUEST_STATUS = 'PENDING'
492 AND R.AUTO_OR_MANUAL = 'AUTO';
493
494 CURSOR test_new_req IS
495 SELECT 1
496 FROM per_letter_gen_statuses s
497 WHERE S.business_group_id + 0 = P_business_group_id
498 AND s.assignment_status_type_id = p_assignment_status_type_id
499 AND s.enabled_flag = 'Y'
500 AND NOT EXISTS
501 (SELECT NULL
502 FROM per_letter_requests r
503 WHERE r.letter_type_id = S.letter_type_id
504 AND R.business_group_id + 0 = P_business_group_id
505 AND r.request_status = 'PENDING'
506 AND r.auto_or_manual = 'AUTO');
507
508 CURSOR csr_letter_type IS
509 SELECT distinct(s.letter_type_id)
510 FROM per_letter_gen_statuses s
511 WHERE s.business_group_id + 0 = p_business_group_id
512 AND s.assignment_status_type_id = p_assignment_status_type_id
513 AND s.enabled_flag = 'Y'
514 AND s.letter_type_id NOT IN (SELECT distinct(r.letter_type_id)
515 FROM per_letter_requests r
516 WHERE r.business_group_id + 0
517 = p_business_group_id
518 and r.REQUEST_STATUS
519 = 'PENDING'
520 and r.AUTO_OR_MANUAL
521 = 'AUTO');
522
523 CURSOR csr_assign IS
524 SELECT ASSIGNMENT_ID
525 FROM PER_ASSIGNMENTS_f
526 WHERE business_group_id + 0 = p_business_group_id
527 AND PERSON_ID = p_person_id
528 AND APPLICATION_ID = p_application_id
529 AND ASSIGNMENT_TYPE = 'A'
530 and effective_start_date <= p_session_date
531 and effective_end_date > p_session_date;
532
533 --
534 -- Cursor added for bug 3680947.
535 --
536 CURSOR csr_check_manual_or_auto IS
537 SELECT 1
538 FROM PER_LETTER_REQUESTS PLR,
539 PER_LETTER_GEN_STATUSES PLGS
540 WHERE PLGS.business_group_id + 0 = p_business_group_id
541 AND PLR.business_group_id +0 = p_business_group_id
542 AND PLGS.assignment_status_type_id = p_assignment_status_type_id
543 AND PLR.letter_type_id = PLGS.letter_type_id
544 AND PLR.auto_or_manual = 'MANUAL';
545 --
546 --
547 v_dummy_asg_id NUMBER(1);
548 v_letter_request_id NUMBER(15);
549 v_test_new_req NUMBER(1);
550 v_letter_type NUMBER(15);
551 v_assignment_id per_assignments_f.assignment_id%TYPE;
552 l_dummy_number number; -- Added for bug 3680947.
553
554 --
555
556 BEGIN
557 --
558 -- Fix for bug 3680947 starts here.
559 --
560 open csr_check_manual_or_auto;
564 return;
561 fetch csr_check_manual_or_auto into l_dummy_number;
562 if csr_check_manual_or_auto%found then
563 close csr_check_manual_or_auto;
565 end if;
566 close csr_check_manual_or_auto;
567 --
568 -- Fix for bug 3680947 ends here.
569 --
570 OPEN ck_gen_stats;
571 FETCH ck_gen_stats INTO v_dummy_asg_id;
572 IF ck_gen_stats%FOUND THEN
573 CLOSE ck_gen_stats;
574 OPEN csr_let_req;
575 LOOP
576 FETCH csr_let_req into v_letter_request_id,v_letter_type;
577 EXIT when csr_let_req%NOTFOUND;
578 INSERT INTO PER_LETTER_REQUEST_LINES
579 (
580 LETTER_REQUEST_LINE_ID
581 , BUSINESS_GROUP_ID
582 , LETTER_REQUEST_ID
583 , PERSON_ID
584 , ASSIGNMENT_ID
585 , ASSIGNMENT_STATUS_TYPE_ID
586 , DATE_FROM
587 , LAST_UPDATE_DATE
588 , LAST_UPDATED_BY
589 , LAST_UPDATE_LOGIN
590 , CREATED_BY
591 , CREATION_DATE)
592 select
593 PER_LETTER_REQUEST_LINES_S.nextval
594 , p_business_group_id
595 , v_letter_request_id
596 , p_person_id
597 , a.ASSIGNMENT_ID
598 , p_assignment_status_type_id
599 , p_session_date
600 , trunc(SYSDATE)
601 , p_last_updated_by
602 , p_last_update_login
603 , p_last_updated_by
604 , trunc(SYSDATE)
605 FROM PER_LETTER_REQUESTS r
606 , PER_LETTER_GEN_STATUSES s
607 , PER_ASSIGNMENTS a
608 WHERE R.LETTER_TYPE_ID = S.LETTER_TYPE_ID
609 AND R.LETTER_TYPE_ID = v_letter_type
610 AND R.letter_request_id = v_letter_request_id -- Added for bug3680947.
611 AND R.REQUEST_STATUS = 'PENDING'
612 AND S.ASSIGNMENT_STATUS_TYPE_ID = p_assignment_status_type_id
613 AND S.business_group_id + 0 = R.business_group_id + 0
614 AND S.BUSINESS_GROUP_ID + 0 = p_business_group_id
615 AND s.ENABLED_FLAG = 'Y'
616 AND a.BUSINESS_GROUP_ID + 0 = p_business_group_id
617 AND a.PERSON_ID = p_person_id
618 AND a.APPLICATION_ID = p_application_id
619 and not exists
620 (select null
621 from PER_LETTER_REQUEST_LINES l
622 where l.PERSON_ID = p_person_id
623 AND A.PERSON_ID = p_person_id
624 and l.ASSIGNMENT_ID = a.ASSIGNMENT_ID
625 and l.ASSIGNMENT_STATUS_TYPE_ID =
626 p_assignment_status_type_id
627 and l.LETTER_REQUEST_ID = v_letter_request_id
628 and l.business_group_id + 0 = p_business_group_id
629 and l.business_group_id + 0 = A.business_group_id + 0
630 and l.business_group_id + 0 = p_business_group_id);
631 END LOOP;
632 CLOSE CSR_LET_REQ;
633 --
634
635 OPEN test_new_req;
636 FETCH test_new_req INTO v_test_new_req;
637 IF test_new_req%FOUND THEN
638 CLOSE test_new_req;
639 --
640 OPEN csr_letter_type;
641 LOOP
642 FETCH csr_letter_type into v_letter_type;
643 EXIT WHEN csr_letter_type%NOTFOUND;
644 insert into PER_LETTER_REQUESTS(
645 LETTER_REQUEST_ID
646 , BUSINESS_GROUP_ID
647 , LETTER_TYPE_ID
648 , DATE_FROM
649 , REQUEST_STATUS
650 , AUTO_OR_MANUAL
651 , LAST_UPDATE_DATE
652 , LAST_UPDATED_BY
653 , LAST_UPDATE_LOGIN
654 , CREATED_BY
655 , CREATION_DATE)
656 select PER_LETTER_REQUESTS_S.nextval
657 , P_Business_group_id
658 , v_letter_type
659 , P_session_date
660 , 'PENDING'
661 , 'AUTO'
662 , trunc(SYSDATE)
663 , p_last_updated_by
664 , p_last_update_login
665 , p_last_updated_by
666 , trunc(SYSDATE)
667 from sys.dual;
668 END LOOP;
669 CLOSE csr_letter_type;
670 --
671 OPEN csr_assign;
672 LOOP
673 FETCH csr_assign INTO v_assignment_id;
674 EXIT WHEN csr_assign%NOTFOUND;
675 INSERT INTO PER_LETTER_REQUEST_LINES
676 (
677 LETTER_REQUEST_LINE_ID
678 , BUSINESS_GROUP_ID
679 , LETTER_REQUEST_ID
680 , PERSON_ID
681 , ASSIGNMENT_ID
682 , ASSIGNMENT_STATUS_TYPE_ID
683 , DATE_FROM
687 , CREATED_BY
684 , LAST_UPDATE_DATE
685 , LAST_UPDATED_BY
686 , LAST_UPDATE_LOGIN
688 , CREATION_DATE)
689 select
690 PER_LETTER_REQUEST_LINES_S.nextval
691 , P_Business_group_id
692 , r.LETTER_REQUEST_ID
693 , P_person_id
694 , v_assignment_id
695 , p_assignment_status_type_id
696 , p_session_date
697 , trunc(SYSDATE)
698 , p_last_updated_by
699 , p_last_update_login
700 , p_last_updated_by
701 , trunc(SYSDATE)
702 FROM PER_LETTER_REQUESTS R
703 , PER_LETTER_GEN_STATUSES s
704 WHERE R.LETTER_TYPE_ID = S.LETTER_TYPE_ID
705 AND p_assignment_status_type_id = S.ASSIGNMENT_STATUS_TYPE_ID
706 AND S.business_group_id + 0 = R.business_group_id + 0
707 AND S.BUSINESS_GROUP_ID + 0 = P_Business_group_id
708 AND R.REQUEST_STATUS = 'PENDING'
709 AND R.AUTO_OR_MANUAL = 'AUTO'
710 AND r.DATE_FROM = p_session_date
711 AND s.ENABLED_FLAG = 'Y'
712 AND NOT EXISTS
713 (SELECT NULL
714 FROM per_letter_request_lines L
715 WHERE L.person_id = P_person_id
716 AND L.assignment_id = v_assignment_id
717 AND L.assignment_status_type_id =
718 p_assignment_status_type_id
719 AND L.letter_request_id = r.letter_request_id
720 AND L.business_group_id + 0 = r.business_group_id + 0
721 AND L.business_group_id + 0 = P_Business_group_id);
722 END LOOP;
723 CLOSE csr_assign;
724 --
725 ELSE CLOSE test_new_req;
726 END IF;
727 --
728 ELSE CLOSE ck_gen_stats;
729 END IF;
730 --
731 END insert_letter_term;
732
733 -----------------------------------------------------------------------------
734 -- Name --
735 -- del_letters_cancel --
736 -- Purpose --
737 -- on cancellation of a termination ensure that any letter lines for the --
738 -- applicant are deleted if the status is TERM_APL for the letter.
739 -- Delete any rougue letter requests that have no letter lines since they
740 -- have just been deleted.
741 -- Arguments --
742 -- See below. --
743 -- Notes --
744 -----------------------------------------------------------------------------
745 PROCEDURE del_letters_cancel(p_business_group_id NUMBER,
746 P_person_id NUMBER,
747 p_application_id NUMBER
748 ) IS
749
750 CURSOR c_term_apl_stat IS
751 SELECT 1
752 FROM per_letter_gen_statuses s
753 , per_assignment_status_types a
754 WHERE s.business_group_id + 0 = p_business_group_id
755 AND a.assignment_status_type_id = s.assignment_status_type_id
756 AND a.per_system_status = 'TERM_APL'
757 AND s.enabled_flag = 'Y';
758
759 CURSOR c_chk_lines IS
760 SELECT distinct(1)
761 FROM per_letter_request_lines L
762 , per_assignments_f a
763 WHERE l.person_id = P_person_id
764 AND l.business_group_id + 0 = p_business_group_id
765 AND l.assignment_id = a.assignment_id
766 AND a.person_id = P_person_id
767 AND a.business_group_id + 0 = p_business_group_id
768 AND a.assignment_type = 'A'
769 AND a.application_id = p_application_id;
770
771 CURSOR csr_let_req_id IS
772 SELECT r.letter_request_id
773 FROM PER_LETTER_REQUESTS R,
774 PER_LETTER_GEN_STATUSES s,
775 PER_ASSIGNMENT_STATUS_TYPES T
776 WHERE r.business_group_id + 0 = p_business_group_id
777 AND s.business_group_id + 0 = p_business_group_id
778 AND r.LETTER_TYPE_ID = S.LETTER_TYPE_ID
779 AND s.ASSIGNMENT_STATUS_TYPE_ID = T.ASSIGNMENT_STATUS_TYPE_ID
780 AND T.PER_SYSTEM_STATUS = 'TERM_APL'
781 AND s.ENABLED_FLAG = 'Y'
782 AND R.REQUEST_STATUS = 'PENDING'
783 AND r.auto_or_manual = 'AUTO';
784
785 CURSOR csr_odd_reqs IS
786 SELECT R.LETTER_REQUEST_ID
787 FROM PER_LETTER_REQUESTS R,
788 PER_LETTER_GEN_STATUSES s,
789 PER_ASSIGNMENT_STATUS_TYPES T
790 WHERE r.business_group_id + 0 = p_business_group_id
791 AND s.business_group_id + 0 = p_business_group_id
792 AND r.LETTER_TYPE_ID = S.LETTER_TYPE_ID
796 and R.REQUEST_STATUS = 'PENDING'
793 AND s.ASSIGNMENT_STATUS_TYPE_ID = T.ASSIGNMENT_STATUS_TYPE_ID
794 AND T.PER_SYSTEM_STATUS = 'TERM_APL'
795 and s.ENABLED_FLAG = 'Y'
797 and R.AUTO_OR_MANUAL = 'AUTO'
798 and not exists
799 (select null
800 from PER_LETTER_REQUEST_LINES l
801 where l.LETTER_REQUEST_ID = R.LETTER_REQUEST_ID
802 and l.business_group_id + 0 = r.business_group_id + 0
803 and l.business_group_id + 0 = p_business_group_id);
804
805 ------
806 v_c_term_apl_stat NUMBER(1);
807 v_c_lines NUMBER(1);
808 v_letter_request_id csr_let_req_id%rowtype;
809 v_csr_odd_reqs csr_odd_reqs%rowtype;
810 ----
811
812 BEGIN
813 OPEN c_term_apl_stat;
814 FETCH c_term_apl_stat INTO v_c_term_apl_stat;
815 IF c_term_apl_stat%FOUND THEN
816 CLOSE c_term_apl_stat;
817 OPEN c_chk_lines;
818 FETCH c_chk_lines INTO v_c_lines;
819 IF c_chk_lines%FOUND THEN
820 CLOSE c_chk_lines;
821 OPEN csr_let_req_id;
822 FETCH csr_let_req_id INTO v_letter_request_id;
823 IF csr_let_req_id%FOUND THEN
824 CLOSE csr_let_req_id;
825 FOR csr_let_req_id_rec IN csr_let_req_id LOOP
826 --
827 DELETE FROM per_letter_request_lines lrL
828 WHERE lrl.business_group_id + 0 = p_business_group_id
829 AND lrl.letter_request_id =
830 csr_let_req_id_REC.letter_request_id
831 AND lrl.person_id = P_person_id
832 AND lrl.person_id = P_person_id
833 AND EXISTS
834 (SELECT NULL
835 FROM per_assignments_f a
836 WHERE a.assignment_id = lrl.assignment_id
837 AND a.person_id = P_person_id
838 AND a.application_id = p_application_id
839 AND a.business_group_id + 0 = P_business_group_id);
840 --
841 END LOOP;
842 OPEN csr_odd_reqs;
843 FETCH csr_odd_reqs INTO v_csr_odd_reqs;
844 IF csr_odd_reqs%FOUND THEN
845 CLOSE csr_odd_reqs;
846 --
847 FOR csr_odd_reqs_rec IN csr_odd_reqs LOOP
848 DELETE FROM per_letter_requests R
849 WHERE r.letter_request_id =
850 csr_odd_reqs_REC.letter_request_id
851 AND r.business_group_id + 0 = p_business_group_id;
852 END LOOP;
853 ELSE CLOSE csr_odd_reqs;
854 END IF;
855 --
856 ELSE CLOSE csr_let_req_id;
857 END IF;
858 --
859 ELSE CLOSE c_chk_lines;
860 END IF;
861 --
862 ELSE CLOSE c_term_apl_stat;
863 END IF;
864 --
865 END del_letters_cancel;
866 -----------------------------------------------------------------------------
867 -- Name --
868 -- cancel_chk_current_emp --
869 -- Purpose --
870 -- to ensure that if the applicant has been hired as an employee that the -
871 -- user cannot canel a termination of the applicant's application
872 -- Arguments --
873 -- See below. --
874 -- Notes --
875 -- called from the client PERWSTAP pre-cancellation
876 -----------------------------------------------------------------------------
877 PROCEDURE cancel_chk_current_emp(p_person_id NUMBER,
878 p_business_group_id NUMBER,
879 p_date_end DATE) IS
880
881 --
882 -- Bug 3380724 Starts Here
883 -- Description : The cursor is modified so that the cursor is checking whether the
884 -- application is terminated by the user or by the system while hiring
885 -- him into the Job.
886 CURSOR c_hired_emp IS
887 SELECT 1
888 FROM per_all_people_f pap
889 WHERE pap.person_id = p_person_id
890 AND pap.applicant_number IS NOT NULL
891 and EXISTS(SELECT 1 -- If hired app table has appl with end date and SUCCESSFUL_FLAG = 'Y'
892 from per_applications app
893 where app.person_id = p_person_id
894 AND app.business_group_id + 0 = p_business_group_id
895 and app.DATE_END = p_date_end
896 and nvl(app.SUCCESSFUL_FLAG,'N') = 'Y'
897 )
898 AND pap.effective_start_date = p_date_end + 1 -- If hired pap table has emp record with date_end+1
899 AND EXISTS
900 (SELECT 1
901 FROM per_person_types PP
902 WHERE pp.person_type_id = pap.person_type_id
903 AND PP.business_group_id + 0 = p_business_group_id
907 -- Bug 3380724 Ends Here
904 AND pp.active_flag ='Y'
905 AND pp.system_person_type IN ('EMP'));
906 --
908 --
909 -- VT 05/21/96 #364623 added NOT EXISTS criteria to CURSOR above
910 -----
911 v_dummy_hired_emp NUMBER(1);
912 -----
913
914 BEGIN
915 OPEN c_hired_emp;
916 FETCH c_hired_emp INTO v_dummy_hired_emp;
917 IF c_hired_emp%FOUND THEN
918 CLOSE c_hired_emp;
919 hr_utility.set_message(800,'PER_7594_APP_TERM_EMP_HIRE');
920 hr_utility.raise_error;
921 ELSE CLOSE c_hired_emp;
922 END IF;
923 END cancel_chk_current_emp;
924 -----------------------------------------------------------------------------
925 -- Name --
926 -- cancel_update_assigns --
927 -- Purpose --
928 -- on cancelling a termination open the applicant assignments to the end of
929 -- time.
930 -- If the applicant was entered through the Quick Entry screen with a --
931 -- status of TERM_APL i.e just for recording purposes then the applicant --
932 -- assignment must be re-opened with the status of ACTIVE_APL. --
933 -- Arguments --
934 -- See below. --
935 -- Notes --
936 -----------------------------------------------------------------------------
937 PROCEDURE cancel_update_assigns(p_person_id NUMBER,
938 p_business_group_id NUMBER,
939 P_date_end DATE,
940 P_application_id NUMBER,
941 p_legislation_code VARCHAR2,
942 P_end_of_time DATE,
943 P_last_updated_by NUMBER,
944 p_last_update_login NUMBER) IS
945
946 CURSOR c_chk_prv_status IS
947 select 1
948 from per_assignment_status_types t
949 , per_assignments_f asg
950 where asg.person_id = p_person_id
951 and nvl(t.business_group_id,
952 p_business_group_id) = p_business_group_id
953 and t.PER_SYSTEM_STATUS = 'TERM_APL'
954 and asg.effective_start_date <= P_date_end
955 and asg.effective_end_date >= P_date_end
956 and asg.business_group_id + 0 = p_business_group_id
957 and asg.application_id = P_application_id
958 and t.assignment_status_type_id = asg.assignment_status_type_id;
959
960 --
961 CURSOR get_actve_apl IS
962 SELECT a.assignment_status_type_id
963 FROM per_assignment_status_types a
964 , per_ass_status_type_amends b
965 WHERE a.per_system_status = 'ACTIVE_APL'
966 AND b.assignment_status_type_id(+) = a.assignment_status_type_id
967 AND b.business_group_id(+) + 0 = p_business_group_id
968 AND nvl(a.business_group_id, p_business_group_id) =
969 p_business_group_id
970 AND nvl(a.legislation_codE,
971 p_legislation_code) = p_legislation_code
972 AND NVL(B.ACTIVE_FLAG,A.ACTIVE_FLAG) = 'Y'
973 and nvl(B.DEFAULT_FLAG, A.DEFAULT_FLAG) = 'Y';
974 --
975 v_dummy_ast NUMBER(1);
976 v_act_ass_stat_id NUMBER(15);
977 --
978
979 BEGIN
980 OPEN c_chk_prv_status;
981 FETCH c_chk_prv_status INTO v_dummy_ast;
982 IF c_chk_prv_status%FOUND THEN
983 CLOSE c_chk_prv_status;
984 OPEN get_actve_apl;
985 FETCH get_actve_apl INTO v_act_ass_stat_id;
986 CLOSE get_actve_apl;
987 UPDATE PER_ALL_ASSIGNMENTS_F A
988 SET A.LAST_UPDATE_DATE = trunc(sysdate)
989 , A.LAST_UPDATED_BY = P_last_updated_by
990 , A.LAST_UPDATE_LOGIN = p_last_update_login
991 , A.EFFECTIVE_END_DATE = P_end_of_time
992 , A.ASSIGNMENT_STATUS_TYPE_ID = v_act_ass_stat_id
993 WHERE A.APPLICATION_ID = P_application_id
994 AND A.PERSON_ID = p_person_id
995 AND A.business_group_id + 0 = p_business_group_id
996 AND A.ASSIGNMENT_TYPE = 'A'
997 AND A.EFFECTIVE_END_DATE = P_date_end;
998
999
1000 -- call to new proc required due to date tracking assignment budget values. To cancel termination
1001 -- of the assignment budget values.
1002 --SASmith 17-APR-1998
1003 cancel_update_ass_bud_val(p_application_id
1004 ,p_person_id
1005 ,p_business_group_id
1006 ,p_date_end
1007 ,p_end_of_time
1008 ,p_last_updated_by
1009 ,p_last_update_login);
1010 --
1011 ELSE CLOSE c_chk_prv_status;
1012 --
1013 UPDATE PER_ALL_ASSIGNMENTS_F A
1014 SET A.LAST_UPDATE_DATE = trunc(sysdate)
1015 , A.LAST_UPDATED_BY = P_last_updated_by
1016 , A.LAST_UPDATE_LOGIN = p_last_update_login
1020 AND A.business_group_id + 0 = p_business_group_id
1017 , A.EFFECTIVE_END_DATE = P_end_of_time
1018 WHERE A.APPLICATION_ID = P_application_id
1019 AND A.PERSON_ID = p_person_id
1021 AND A.ASSIGNMENT_TYPE = 'A'
1022 AND A.EFFECTIVE_END_DATE = P_date_end;
1023
1024 -- call to new proc required due to date tracking assignment budget values. To cancel termination
1025 -- of the assignment budget values.
1026 --SASmith 17-APR-1998
1027 cancel_update_ass_bud_val(p_application_id
1028 ,p_person_id
1029 ,p_business_group_id
1030 ,p_date_end
1031 ,p_end_of_time
1032 ,p_last_updated_by
1033 ,p_last_update_login);
1034 END IF;
1035 END cancel_update_assigns;
1036 -----------------------------------------------------------------------------
1037 -- Name --
1038 -- term_update_assignments --
1039 -- Purpose --
1040 -- when terminating an applicant close down all the applicant assignments
1041 -- as of the termination date.
1042 -- Arguments --
1043 -- See below. --
1044 -- Notes --
1045 -----------------------------------------------------------------------------
1046 PROCEDURE term_update_assignments(p_person_id NUMBER,
1047 p_business_group_id NUMBER,
1048 P_date_end DATE,
1049 P_application_id NUMBER,
1050 p_last_updated_by NUMBER,
1051 p_last_update_login NUMBER) IS
1052
1053 -- CURSOR c_chk_assigns IS
1054 -- SELECT 1
1055 -- FROM per_all_assignments_f a
1056 -- WHERE a.application_id = P_application_id
1057 -- AND a.person_id = p_person_id
1058 -- AND a.business_group_id + 0 = p_business_group_id
1059 -- AND a.assignment_type = 'A'
1060 -- AND a.effective_start_date > P_date_end;
1061 --
1062 --dummy_assign NUMBER(1);
1063 --
1064 cursor csr_get_future_assignments is
1065 select assignment_id, object_version_number, effective_start_date
1066 from per_all_assignments_f a
1067 WHERE a.application_id = P_application_id
1068 AND a.person_id = p_person_id
1069 AND a.business_group_id = p_business_group_id
1070 AND a.assignment_type = 'A'
1071 AND a.effective_start_date > P_date_end
1072 AND not exists
1073 (select 'Y'
1074 from per_all_assignments_f paf2
1075 where paf2.assignment_id = a.assignment_id
1076 and paf2.effective_start_date < a.EFFECTIVE_START_DATE);
1077 --
1078 l_validation_start_date DATE;
1079 l_validation_end_date DATE;
1080 l_effective_start_date DATE;
1081 l_effective_end_date DATE;
1082 l_business_group_id hr_all_organization_units.organization_id%TYPE;
1083 l_org_now_no_manager_warning BOOLEAN;
1084 --
1085 BEGIN
1086 -- Delete all future assignments
1087 FOR l_assignment in csr_get_future_assignments LOOP
1088 per_asg_del.del
1089 (p_assignment_id => l_assignment.assignment_id
1090 ,p_object_version_number => l_assignment.object_version_number
1091 ,p_effective_date => l_assignment.effective_start_date --p_date_end+1
1092 ,p_datetrack_mode => hr_api.g_zap
1093 ,p_effective_start_date => l_effective_start_date
1094 ,p_effective_end_date => l_effective_end_date
1095 ,p_business_group_id => l_business_group_id
1096 ,p_validation_start_date => l_validation_start_date
1097 ,p_validation_end_date => l_validation_end_date
1098 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1099 );
1100 END LOOP;
1101 -- Delete DT updates
1102 DELETE per_all_assignments_f a
1103 WHERE a.application_id = P_application_id
1104 AND a.person_id = p_person_id
1105 AND a.business_group_id = p_business_group_id
1106 AND a.assignment_type = 'A'
1107 AND a.effective_start_date > P_date_end;
1108
1109 -- Terminate assignments
1110
1111 UPDATE per_all_assignments_f paa
1112 SET paa.last_update_date = trunc(sysdate),
1113 paa.last_updated_by = p_last_updated_by,
1114 paa.last_update_login = p_last_update_login,
1115 paa.EFFECTIVE_END_DATE = P_date_end
1116 where paa.APPLICATION_ID = P_application_id
1117 and paa.PERSON_ID = p_person_id
1118 and paa.business_group_id + 0 = p_business_group_id
1119 and paa.ASSIGNMENT_TYPE = 'A'
1120 and paa.EFFECTIVE_END_DATE =
1121 (select max(pa2.EFFECTIVE_END_DATE)
1122 from PER_ALL_ASSIGNMENTS_F pa2
1123 where pa2.PERSON_ID = p_person_id
1127
1124 and pa2.assignment_id = paa.assignment_id -- 3957964 >>
1125 and pa2.effective_end_date > p_date_end -- <<
1126 and pa2.APPLICATION_ID = P_application_id);
1128 -- call to new proc due to date tracking of assignment budget values. This will terminate the
1129 -- assignment budget values related to the assignment being terminated.
1130 --SASmith 17-APR-1998
1131
1132 term_update_ass_bud_val(p_application_id
1133 ,p_person_id
1134 ,p_business_group_id
1135 ,p_date_end
1136 ,p_last_updated_by
1137 ,p_last_update_login);
1138 --
1139 END term_update_assignments;
1140 --
1141 --
1142 --
1143 PROCEDURE canc_chk_fut_per_changes(p_person_id NUMBER,
1144 p_application_id NUMBER,
1145 p_date_end DATE ) is
1146 --
1147 cursor c1 is
1148 SELECT 1
1149 FROM PER_ALL_PEOPLE_F PAPF
1150 WHERE PAPF.PERSON_ID = P_PERSON_ID
1151 AND PAPF.EFFECTIVE_START_DATE > P_DATE_END + 1 ;
1152 --
1153 l_dummy number ;
1154 BEGIN
1155 --
1156 open c1 ;
1157 fetch c1 into l_dummy ;
1158 if c1%found then
1159 close c1 ;
1160 hr_utility.set_message(801,'HR_6385_APP_TERM_FUT_CHANGES' );
1161 hr_utility.raise_error ;
1162 end if;
1163 close c1 ;
1164 end canc_chk_fut_per_changes ;
1165 -----------------------------------------------------------------------------
1166 -----------------------------------------------------------------------------
1167 -- Name --
1168 -- term_chk_per_assign_changes --
1169 -- Purpose --
1170 -- check that the applicant has no future person record changes after the
1171 -- apparent termination date since this would prohibit a termination. --
1172 -- Arguments --
1173 -- See below. --
1174 -- Notes --
1175 -----------------------------------------------------------------------------
1176 PROCEDURE term_chk_fut_per_changes(p_person_id NUMBER,
1177 p_business_group_id NUMBER,
1178 P_date_end DATE) IS
1179 CURSOR c_per_changes IS
1180 SELECT 1
1181 FROM per_all_people_f papf
1182 WHERE papf.person_id = p_person_id
1183 AND papf.effective_start_date > P_date_end
1184 AND papf.business_group_id + 0 = p_business_group_id;
1185 ------
1186 v_dummy_number NUMBER(1);
1187 ---
1188 BEGIN
1189 OPEN c_per_changes;
1190 FETCH c_per_changes INTO v_dummy_number;
1191 IF c_per_changes%FOUND THEN
1192 CLOSE c_per_changes;
1193 hr_utility.set_message(800,'HR_6382_APP_TERM_FUTURE_PPT');
1194 hr_utility.set_message_token('DATE',P_date_end);
1195 hr_utility.raise_error;
1196 ELSE CLOSE c_per_changes;
1197 END IF;
1198 --
1199 END term_chk_fut_per_changes;
1200 -----------------------------------------------------------------------------
1201 -- Name --
1202 -- term_chk_fut_assign_changes --
1203 -- Purpose --
1204 -- if future assignment changes of any sort exist for the person, then --
1205 -- the user cannot terminate the application. --
1206 -- Arguments --
1207 -- See below. --
1208 -- Notes --
1209 -----------------------------------------------------------------------------
1210 PROCEDURE term_chk_fut_assign_changes(p_person_id NUMBER,
1211 p_business_group_id NUMBER,
1212 P_date_end DATE) IS
1213 CURSOR c_assign_changes IS
1214 SELECT 1
1215 FROM PER_ALL_ASSIGNMENTS_F PAAF
1216 WHERE PAAF.PERSON_ID = p_person_id
1217 AND PAAF.business_group_id + 0 = p_business_group_id
1218 AND PAAF.EFFECTIVE_START_DATE > P_date_end;
1219 ------
1220 v_number NUMBER(1);
1221 ---
1222 BEGIN
1223 OPEN c_assign_changes;
1224 FETCH c_assign_changes INTO v_number;
1225 IF c_assign_changes%FOUND THEN
1226 CLOSE c_assign_changes;
1227 hr_utility.set_message(800,'HR_6583_APP_TERM_FUT_ASS');
1228 hr_utility.set_message_token('DATE',P_date_end);
1229 hr_utility.raise_error;
1230 ELSE CLOSE c_assign_changes;
1231 END IF;
1232 --
1233 END term_chk_fut_assign_changes;
1234
1235 -----------------------------------------------------------------------------
1236 -- Name --
1237 -- maint_security_cancel --
1238 -- Purpose --
1242 -- Notes --
1239 -- Stubbed as part of the ex-person security enhancements. --
1240 -- Arguments --
1241 -- See below. --
1243 -----------------------------------------------------------------------------
1244 PROCEDURE maint_security_cancel(p_person_id NUMBER) IS
1245
1246 --
1247 BEGIN
1248 --
1249 NULL;
1250 --
1251 END maint_security_cancel;
1252 -----------------------------------------------------------------------------
1253 -- Name --
1254 -- maint_security_term --
1255 -- Purpose --
1256 -- Stubbed as part of the ex-person security enhancements. --
1257 -- Arguments --
1258 -- See below. --
1259 -- Notes --
1260 -----------------------------------------------------------------------------
1261 PROCEDURE maint_security_term(p_person_id NUMBER) IS
1262
1263 BEGIN
1264 --
1265 NULL;
1266 --
1267 END maint_security_term;
1268
1269
1270 -----------------------------------------------------------------------------
1271 -- Name --
1272 -- sec_statuses_cancel --
1273 -- Purpose --
1274 -- to nuliify any secondary assignment statuses end dates on the applicant's
1275 -- assignments if they are currently the same as the termination date when
1276 -- the applicant was terminated.
1277 -- Arguments --
1278 -- See below. --
1279 -- Notes --
1280 -----------------------------------------------------------------------------
1281 PROCEDURE sec_statuses_cancel(p_end_date DATE,
1282 p_application_id NUMBER,
1283 p_business_group_id NUMBER,
1284 p_last_updated_by NUMBER,
1285 p_last_update_login NUMBER,
1286 p_person_id NUMBER) IS
1287
1288 CURSOR c_sec_stat_cancel IS
1289 select sa.assignment_id
1290 from per_secondary_ass_statuses sa
1291 where sa.business_group_id + 0 = p_business_group_id
1292 and sa.end_date = p_end_date
1293 and exists
1294 ( SELECT s.assignment_id
1295 FROM PER_SECONDARY_ASS_STATUSES s
1296 where s.business_group_id + 0 = p_business_group_id
1297 and s.end_date = p_end_date
1298 and sa.assignment_id = s.assignment_id
1299 and exists
1300 (select null
1301 from per_assignments_f paf
1302 where paf.person_id = p_person_id
1303 and paf.application_id = p_application_id
1304 and paf.assignment_type = 'A'
1305 and paf.effective_end_date = p_end_date
1306 and paf.assignment_id = s.assignment_id));
1307 --
1308 v_assignment_id NUMBER(15);
1309 --
1310
1311 BEGIN
1312 OPEN c_sec_stat_cancel;
1313 LOOP
1314 FETCH c_sec_stat_cancel into v_assignment_id;
1315 EXIT WHEN c_sec_stat_cancel%NOTFOUND;
1316 UPDATE per_secondary_ass_statuses s
1317 SET s.END_DATE = NULL
1318 , s.LAST_UPDATE_DATE = trunc(SYSDATE)
1319 , s.LAST_UPDATED_BY = p_last_updated_by
1320 , s.LAST_UPDATE_LOGIN = p_last_update_login
1321 WHERE s.assignment_id = v_assignment_id
1322 AND s.business_group_id + 0 = p_business_group_id
1323 AND s.END_DATE = p_end_date;
1324 END LOOP;
1325 CLOSE c_sec_stat_cancel;
1326 --
1327 END sec_statuses_cancel;
1328 -----------------------------------------------------------------------------
1329 -- Name --
1330 -- sec_statuses_term --
1331 -- Purpose --
1332 -- to delete any future sec.statuses when terminating an applicant. Puts an
1333 -- end date as of the applicant's termination date for any secondary
1334 -- applicant assignment statuses that start before the termination date
1335 -- and which don't have end dates before the termination end date. --
1336 -- Arguments --
1337 -- See below. --
1338 -- Notes --
1339 -----------------------------------------------------------------------------
1340 procedure sec_statuses_term(p_end_date date
1341 ,p_application_id number
1342 ,p_business_group_id number
1346 -- WWbug 633263
1343 ,p_last_updated_by number
1344 ,p_last_update_login number
1345 ,p_person_id number) is
1347 -- Modified cursor for performance improvements by removing the full table
1348 -- scan on per_secondary_ass_statuses.
1349 -- This was achieved by removing the sub-query
1350 cursor chk_sec_stat is
1351 select 1
1352 from per_secondary_ass_statuses s
1353 ,per_assignments_f a
1354 where s.business_group_id + 0 = p_business_group_id
1355 and s.start_date is not null
1356 and a.business_group_id + 0 = p_business_group_id
1357 and a.person_id = p_person_id
1358 and s.assignment_id = a.assignment_id
1359 and a.application_id = p_application_id
1360 and a.assignment_type = 'A'
1361 and p_end_date
1362 between a.effective_start_date
1363 and a.effective_end_date;
1364 -- WWbug 633263
1365 -- Modified cursor for performance improvements by removing the full table
1366 -- scan on per_secondary_ass_statuses.
1367 -- This was achieved by removing the sub-query
1368 cursor c_sec_stat is
1369 select sa.assignment_id
1370 from per_secondary_ass_statuses sa
1371 ,per_assignments_f paf
1372 where sa.business_group_id + 0 = p_business_group_id
1373 and sa.start_date <= p_end_date
1374 and (sa.end_date is null
1375 or sa.end_date > p_end_date)
1376 and sa.assignment_id = paf.assignment_id
1377 and paf.person_id = p_person_id
1378 and paf.application_id = p_application_id
1379 and paf.assignment_type = 'A'
1380 and p_end_date
1381 between paf.effective_start_date
1382 and paf.effective_end_date;
1383 --
1384 v_dummy number(1);
1385 --
1386 begin
1387 open chk_sec_stat;
1388 fetch chk_sec_stat into v_dummy;
1389 if chk_sec_stat%found then
1390 close chk_sec_stat;
1391 -- WWbug 633263
1392 -- Modified cursor for performance improvements by removing the full table
1393 -- scan on per_secondary_ass_statuses.
1394 -- This was achieved by replacing the EXISTS sub-query with an IN sub-query
1395 delete from per_secondary_ass_statuses s
1396 where s.business_group_id + 0 = p_business_group_id
1397 and trunc(s.start_date) > p_end_date
1398 and s.assignment_id in
1399 (select a.assignment_id
1400 from per_assignments_f a
1401 where a.business_group_id + 0 = p_business_group_id
1402 and a.person_id = p_person_id
1403 and a.application_id = p_application_id
1404 and a.assignment_type = 'A'
1405 and p_end_date
1406 between a.effective_start_date
1407 and a.effective_end_date);
1408 -- WWbug 633263
1409 -- Cleared up the previous code with a cursor for loop
1410 for csr_rec in c_sec_stat loop
1411 update per_secondary_ass_statuses s
1412 set s.end_date = p_end_date
1413 , s.last_update_date = trunc(sysdate)
1414 , s.last_updated_by = p_last_updated_by
1415 , s.last_update_login = p_last_update_login
1416 where s.assignment_id = csr_rec.assignment_id
1417 and s.business_group_id + 0 = p_business_group_id
1418 and s.start_date <= p_end_date
1419 and (s.end_date is null
1420 or s.end_date > p_end_date);
1421 end loop;
1422 --
1423 else
1424 -- WWbug 633263
1425 -- Closed the cursor which was previously not closed
1426 close chk_sec_stat;
1427 end if;
1428 end sec_statuses_term;
1429 -----------------------------------------------------------------------------
1430 -- Name --
1431 -- del_interviews_term --
1432 -- Purpose --
1433 -- Arguments --
1434 -- See below. --
1435 -- Notes --
1436 -----------------------------------------------------------------------------
1437 PROCEDURE del_interviews_term(P_person_id NUMBER,
1438 P_date_end DATE,
1439 P_Business_group_id NUMBER,
1440 P_application_id NUMBER) IS
1441 CURSOR chk_events IS
1442 SELECT E.EVENT_ID
1443 FROM PER_EVENTS E
1444 , PER_ASSIGNMENTS_F A
1445 WHERE A.PERSON_ID = P_person_id
1446 AND E.business_group_id + 0 = p_business_group_id
1447 AND A.business_group_id + 0 = p_business_group_id
1448 AND A.APPLICATION_ID = P_application_id
1449 AND E.ASSIGNMENT_ID = A.ASSIGNMENT_ID
1450 AND E.DATE_START >= P_date_end
1451 AND E.EVENT_OR_INTERVIEW = 'I';
1452
1453
1454 CURSOR chk_bookings IS
1455 SELECT distinct(1)
1456 FROM PER_BOOKINGS B
1457 , PER_EVENTS E
1461 AND B.EVENT_ID = E.EVENT_ID
1458 , PER_ASSIGNMENTS_F A
1459 WHERE A.PERSON_ID = P_person_id
1460 AND A.APPLICATION_ID = P_application_id
1462 AND E.DATE_START >= P_date_end
1463 AND E.EVENT_OR_INTERVIEW = 'I'
1464 AND E.ASSIGNMENT_ID = A.ASSIGNMENT_ID;
1465 --
1466 -- the person_id on per_bookings is the employee who is doing the
1467 -- interviewing of the applicant and is NOT the applicant.
1468 --
1469
1470 CURSOR c_viewers IS
1471 select B.PERSON_ID,B.BOOKING_ID
1472 from PER_BOOKINGS B
1473 , PER_EVENTS E
1474 , PER_ASSIGNMENTS A
1475 where B.business_group_id + 0 = p_business_group_id
1476 and E.business_group_id + 0 = p_business_group_id
1477 and A.business_group_id + 0 = p_business_group_id
1478 and A.PERSON_ID = p_person_id
1479 and A.APPLICATION_ID = p_application_id
1480 and B.EVENT_ID = E.EVENT_ID
1481 and E.DATE_START >= P_date_end
1482 and E.EVENT_OR_INTERVIEW = 'I'
1483 and E.ASSIGNMENT_ID = A.ASSIGNMENT_ID;
1484
1485 V_dummy_events chk_events%rowtype;
1486 v_dummy_bookings NUMBER(1);
1487 r_interviewers c_viewers%rowtype;
1488 l_event_found BOOLEAN;
1489
1490 BEGIN
1491 OPEN chk_events;
1492 FETCH chk_events into V_dummy_events;
1493 --
1494 l_event_found := chk_events%found;
1495 IF l_event_found THEN
1496 CLOSE chk_events;
1497 OPEN chk_bookings;
1498 FETCH chk_bookings into v_dummy_bookings;
1499 --
1500 IF chk_bookings%found THEN
1501 CLOSE chk_bookings;
1502 OPEN c_viewers;
1503 FETCH c_viewers into r_interviewers;
1504 CLOSE c_viewers;
1505 FOR c_viewers_rec IN c_viewers LOOP
1506 DELETE FROM per_bookings bk
1507 WHERE bk.business_group_id + 0 = p_business_group_id
1508 AND bk.booking_id = c_viewers_rec.BOOKING_ID
1509 AND bk.person_id = c_viewers_rec.PERSON_ID;
1510 END LOOP;
1511
1512 FOR chk_events_rec IN chk_events LOOP
1513 DELETE FROM per_events ev
1514 WHERE ev.event_id = chk_events_rec.event_id
1515 AND ev.business_group_id + 0 = p_business_group_id;
1516 END LOOP;
1517 --
1518 ELSE CLOSE chk_bookings;
1519 FOR chk_events_rec IN chk_events LOOP
1520 DELETE FROM per_events ev
1521 WHERE ev.event_id = chk_events_rec.event_id
1522 AND ev.business_group_id + 0 = p_business_group_id;
1523 END LOOP;
1524
1525 --
1526 END IF;
1527 END IF;
1528 END del_interviews_term;
1529 -----------------------------------------------------------------------------
1530 -- Name --
1531 -- maintain_ppt_cancel --
1532 -- Purpose --
1533 -- When cancelling an already terminated application this procedure ensures
1534 -- that the last record is deleted from the person table i.e the one that
1535 -- has a person_type_id of TERM_APL so that the person reverts back to an
1536 -- APL and secondly it opens out the now new last record by putting an
1537 -- effective_end_date on PER_PEOPLE_F as of the end_of_time.
1538 -- Arguments --
1539 -- See below. --
1540 -- Notes --
1541 -----------------------------------------------------------------------------
1542 PROCEDURE maintain_ppt_cancel(P_person_id NUMBER,
1543 P_Business_group_id NUMBER,
1544 P_date_end DATE,
1545 P_last_updated_by NUMBER,
1546 P_last_update_login NUMBER,
1547 P_end_of_time DATE) IS
1548 --
1549 BEGIN
1550 DELETE FROM per_all_people_f papf
1551 WHERE papf.person_id = P_person_id
1552 AND papf.business_group_id + 0 = P_Business_group_id
1553 AND papf.effective_start_date = P_date_end + 1;
1554 --
1555 UPDATE per_all_people_f papf
1556 SET papf.effective_end_date = P_end_of_time
1557 , papf.last_updated_by = P_last_updated_by
1558 , papf.last_update_date = trunc(sysdate)
1559 , papf.last_update_login = P_last_update_login
1560 WHERE papf.person_id = P_person_id
1561 AND papf.BUSINESS_GROUP_ID + 0 = P_Business_group_id
1562 AND papf.effective_end_date = P_date_end;
1563 --
1564 END maintain_ppt_cancel;
1565 ----------------------------------------------------------------------------
1566 -- Name --
1567 -- chk_not_already_termed --
1568 -- Purpose --
1572 -- See below. --
1569 -- To ensure that the user cannot terminate an application which has already
1570 -- been terminated.
1571 -- Arguments --
1573 -- Notes --
1574 -----------------------------------------------------------------------------
1575 PROCEDURE chk_not_already_termed(P_Business_group_id NUMBER,
1576 P_person_id NUMBER,
1577 P_application_id NUMBER,
1578 P_date_end DATE) IS
1579 --
1580
1581 CURSOR c_chk_already_term IS
1582 SELECT 1
1583 FROM PER_APPLICATIONS PA
1584 WHERE PA.business_group_id + 0 = P_Business_group_id
1585 AND PA.PERSON_ID = P_person_id
1586 AND PA.APPLICATION_ID = P_application_id
1587 AND PA.DATE_END IS NOT NULL
1588 AND PA.DATE_END = P_date_end;
1589
1590 V_dummy_1 NUMBER(1);
1591 --
1592 BEGIN
1593
1594 OPEN c_chk_already_term;
1595 FETCH c_chk_already_term into V_dummy_1;
1596 IF c_chk_already_term%found THEN
1597 CLOSE c_chk_already_term;
1598 hr_utility.set_message(800,'HR_7105_APPL_ALREADY_TERMED');
1599 hr_utility.raise_error;
1600 ELSE
1601 CLOSE c_chk_already_term;
1602 END IF;
1603 END chk_not_already_termed;
1604 -----------------------------------------------------------------------------
1605 -- Name --
1606 -- maintain_ppt_term --
1607 -- Purpose --
1608 -- This procedure maintains the person's record when going from an --
1609 -- applicant to an ex-applicant. --
1610 -- In particular this maintiains the person_type_id on per_all_people_f --
1611 -- by closing down the record in per_all_people_f as of the end date of --
1612 -- the person's application and inserting a row with the new person_type_id
1613 -- on the next day. --
1614 -- Arguments --
1615 -- See below. --
1616 -- Notes --
1617 -- --
1618 -------------------------------------------------------------------------------
1619 PROCEDURE maintain_ppt_term(P_Business_group_id NUMBER,
1620 P_person_id NUMBER,
1621 P_date_end DATE,
1622 P_end_of_time DATE,
1623 P_last_updated_by NUMBER,
1624 P_last_update_login NUMBER) IS
1625 --
1626 BEGIN
1627 UPDATE per_all_people_f papf
1628 set PAPF.effective_end_date = P_date_end
1629 , PAPF.last_updated_by = P_last_updated_by
1630 , PAPF.last_update_date = trunc(sysdate)
1631 , PAPF.last_update_login = P_last_update_login
1632 where PAPF.person_id = P_person_id
1633 and P_date_end BETWEEN
1634 PAPF.effective_start_date AND PAPF.effective_end_date
1635 and PAPF.business_group_id + 0 = P_Business_group_id;
1636 --
1637
1638 INSERT INTO per_all_people_f
1639 (PERSON_ID ,EFFECTIVE_START_DATE ,EFFECTIVE_END_DATE
1640 ,BUSINESS_GROUP_ID ,PERSON_TYPE_ID ,LAST_NAME
1641 ,START_DATE ,APPLICANT_NUMBER
1642 ,COMMENT_ID
1643 ,CURRENT_APPLICANT_FLAG
1644 ,CURRENT_EMP_OR_APL_FLAG
1645 ,CURRENT_EMPLOYEE_FLAG
1646 ,CURRENT_NPW_FLAG
1647 ,DATE_EMPLOYEE_DATA_VERIFIED
1648 ,DATE_OF_BIRTH ,EMAIL_ADDRESS
1649 ,EMPLOYEE_NUMBER ,EXPENSE_CHECK_SEND_TO_ADDRESS
1650 ,FIRST_NAME ,FULL_NAME
1651 ,KNOWN_AS ,MARITAL_STATUS ,MIDDLE_NAMES
1652 ,NATIONALITY ,NATIONAL_IDENTIFIER ,PREVIOUS_LAST_NAME
1653 ,REGISTERED_DISABLED_FLAG ,SEX ,TITLE
1654 ,VENDOR_ID ,WORK_TELEPHONE ,REQUEST_ID
1655 ,PROGRAM_APPLICATION_ID ,PROGRAM_ID
1656 ,PROGRAM_UPDATE_DATE ,ATTRIBUTE_CATEGORY
1657 ,ATTRIBUTE1 ,ATTRIBUTE2 ,ATTRIBUTE3 ,ATTRIBUTE4 ,ATTRIBUTE5
1658 ,ATTRIBUTE6 ,ATTRIBUTE7 ,ATTRIBUTE8 ,ATTRIBUTE9 ,ATTRIBUTE10
1659 ,ATTRIBUTE11 ,ATTRIBUTE12 ,ATTRIBUTE13 ,ATTRIBUTE14
1660 ,ATTRIBUTE15 ,ATTRIBUTE16 ,ATTRIBUTE17 ,ATTRIBUTE18 ,ATTRIBUTE19
1661 ,ATTRIBUTE20 , ATTRIBUTE21 ,ATTRIBUTE22 ,ATTRIBUTE23 ,ATTRIBUTE24
1662 ,ATTRIBUTE25 ,ATTRIBUTE26 ,ATTRIBUTE27 ,ATTRIBUTE28 ,ATTRIBUTE29
1663 ,ATTRIBUTE30 , LAST_UPDATE_DATE ,LAST_UPDATED_BY
1664 ,LAST_UPDATE_LOGIN ,CREATED_BY ,CREATION_DATE
1665 ,PER_INFORMATION_CATEGORY
1666 ,PER_INFORMATION1
1667 ,PER_INFORMATION2
1668 ,PER_INFORMATION3
1669 ,PER_INFORMATION4
1670 ,PER_INFORMATION5
1671 ,PER_INFORMATION6
1672 ,PER_INFORMATION7
1673 ,PER_INFORMATION8
1674 ,PER_INFORMATION9
1675 ,PER_INFORMATION10
1676 ,PER_INFORMATION11
1677 ,PER_INFORMATION12
1678 ,PER_INFORMATION13
1679 ,PER_INFORMATION14
1683 ,PER_INFORMATION18
1680 ,PER_INFORMATION15
1681 ,PER_INFORMATION16
1682 ,PER_INFORMATION17
1684 ,PER_INFORMATION19
1685 ,PER_INFORMATION20
1686 ,PER_INFORMATION21
1687 ,PER_INFORMATION22
1688 ,PER_INFORMATION23
1689 ,PER_INFORMATION24
1690 ,PER_INFORMATION25
1691 ,PER_INFORMATION26
1692 ,PER_INFORMATION27
1693 ,PER_INFORMATION28
1694 ,PER_INFORMATION29
1695 ,PER_INFORMATION30
1696 ,BACKGROUND_CHECK_STATUS
1697 ,BACKGROUND_DATE_CHECK
1698 ,BLOOD_TYPE
1699 ,CORRESPONDENCE_LANGUAGE
1700 ,FAST_PATH_EMPLOYEE
1701 ,FTE_CAPACITY
1702 ,HOLD_APPLICANT_DATE_UNTIL
1703 ,HONORS
1704 ,INTERNAL_LOCATION
1705 ,LAST_MEDICAL_TEST_BY
1706 ,LAST_MEDICAL_TEST_DATE
1707 ,MAILSTOP
1708 ,OFFICE_NUMBER
1709 ,ON_MILITARY_SERVICE
1710 ,ORDER_NAME
1711 ,PRE_NAME_ADJUNCT
1712 ,PROJECTED_START_DATE
1713 ,REHIRE_AUTHORIZOR
1714 ,REHIRE_REASON
1715 ,REHIRE_RECOMMENDATION
1716 ,RESUME_EXISTS
1717 ,RESUME_LAST_UPDATED
1718 ,SECOND_PASSPORT_EXISTS
1719 ,STUDENT_STATUS
1720 ,SUFFIX
1721 ,WORK_SCHEDULE
1722 ,town_of_birth
1723 ,region_of_birth
1724 ,country_of_birth
1725 ,global_person_id
1726 ,party_id
1727 ,original_date_of_hire
1728
1729 --Bug2974671 starts here.
1730
1731 ,BENEFIT_GROUP_ID
1732 ,COORD_BEN_MED_PLN_NO
1733 ,COORD_BEN_NO_CVG_FLAG
1734 ,DPDNT_ADOPTION_DATE
1735 ,DPDNT_VLNTRY_SVCE_FLAG
1736 ,USES_TOBACCO_FLAG
1737
1738 -- Bug2974671 ends here.
1739 ,NPW_NUMBER -- Added for Fix for #3184546
1740 )
1741 select PAPF.PERSON_ID
1742 ,PAPF.EFFECTIVE_END_DATE+1
1743 ,P_end_of_time
1744 ,PAPF.BUSINESS_GROUP_ID ,PPT.PERSON_TYPE_ID
1745 ,PAPF.LAST_NAME ,PAPF.START_DATE
1746 ,PAPF.APPLICANT_NUMBER ,PAPF.COMMENT_ID
1747 ,null
1748 ,PAPF.CURRENT_EMPLOYEE_FLAG
1749 ,PAPF.CURRENT_EMPLOYEE_FLAG
1750 ,PAPF.CURRENT_NPW_FLAG
1751 ,PAPF.DATE_EMPLOYEE_DATA_VERIFIED
1752 ,PAPF.DATE_OF_BIRTH
1753 ,PAPF.EMAIL_ADDRESS
1754 ,PAPF.EMPLOYEE_NUMBER
1755 ,PAPF.EXPENSE_CHECK_SEND_TO_ADDRESS
1756 ,PAPF.FIRST_NAME ,PAPF.FULL_NAME
1757 ,PAPF.KNOWN_AS ,PAPF.MARITAL_STATUS
1758 ,PAPF.MIDDLE_NAMES ,PAPF.NATIONALITY
1759 ,PAPF.NATIONAL_IDENTIFIER
1760 ,PAPF.PREVIOUS_LAST_NAME
1761 ,PAPF.REGISTERED_DISABLED_FLAG
1762 ,PAPF.SEX ,PAPF.TITLE ,PAPF.VENDOR_ID
1763 ,PAPF.WORK_TELEPHONE ,PAPF.REQUEST_ID
1764 ,PAPF.PROGRAM_APPLICATION_ID
1765 ,PAPF.PROGRAM_ID
1766 ,PAPF.PROGRAM_UPDATE_DATE
1767 ,PAPF.ATTRIBUTE_CATEGORY
1768 ,PAPF.ATTRIBUTE1 ,PAPF.ATTRIBUTE2
1769 ,PAPF.ATTRIBUTE3 ,PAPF.ATTRIBUTE4
1770 ,PAPF.ATTRIBUTE5 ,PAPF.ATTRIBUTE6
1771 ,PAPF.ATTRIBUTE7 ,PAPF.ATTRIBUTE8
1772 ,PAPF.ATTRIBUTE9 ,PAPF.ATTRIBUTE10
1773 ,PAPF.ATTRIBUTE11 ,PAPF.ATTRIBUTE12
1774 ,PAPF.ATTRIBUTE13 ,PAPF.ATTRIBUTE14
1775 ,PAPF.ATTRIBUTE15 ,PAPF.ATTRIBUTE16
1776 ,PAPF.ATTRIBUTE17 ,PAPF.ATTRIBUTE18
1777 ,PAPF.ATTRIBUTE19 ,PAPF.ATTRIBUTE20
1778 ,PAPF.ATTRIBUTE21 ,PAPF.ATTRIBUTE22
1779 ,PAPF.ATTRIBUTE23 ,PAPF.ATTRIBUTE24
1780 ,PAPF.ATTRIBUTE25 ,PAPF.ATTRIBUTE26
1781 ,PAPF.ATTRIBUTE27 ,PAPF.ATTRIBUTE28
1782 ,PAPF.ATTRIBUTE29 ,PAPF.ATTRIBUTE30
1783 ,PAPF.LAST_UPDATE_DATE ,PAPF.LAST_UPDATED_BY
1784 ,PAPF.LAST_UPDATE_LOGIN ,PAPF.CREATED_BY
1785 ,PAPF.CREATION_DATE
1786 ,PAPF.PER_INFORMATION_CATEGORY
1787 ,PAPF.PER_INFORMATION1
1788 ,PAPF.PER_INFORMATION2
1789 ,PAPF.PER_INFORMATION3
1790 ,PAPF.PER_INFORMATION4
1791 ,PAPF.PER_INFORMATION5
1792 ,PAPF.PER_INFORMATION6
1793 ,PAPF.PER_INFORMATION7
1794 ,PAPF.PER_INFORMATION8
1795 ,PAPF.PER_INFORMATION9
1796 ,PAPF.PER_INFORMATION10
1797 ,PAPF.PER_INFORMATION11
1798 ,PAPF.PER_INFORMATION12
1799 ,PAPF.PER_INFORMATION13
1800 ,PAPF.PER_INFORMATION14
1801 ,PAPF.PER_INFORMATION15
1802 ,PAPF.PER_INFORMATION16
1803 ,PAPF.PER_INFORMATION17
1804 ,PAPF.PER_INFORMATION18
1805 ,PAPF.PER_INFORMATION19
1806 ,PAPF.PER_INFORMATION20
1807 ,PAPF.PER_INFORMATION21
1808 ,PAPF.PER_INFORMATION22
1809 ,PAPF.PER_INFORMATION23
1810 ,PAPF.PER_INFORMATION24
1811 ,PAPF.PER_INFORMATION25
1812 ,PAPF.PER_INFORMATION26
1813 ,PAPF.PER_INFORMATION27
1814 ,PAPF.PER_INFORMATION28
1815 ,PAPF.PER_INFORMATION29
1816 ,PAPF.PER_INFORMATION30
1817 ,PAPF.BACKGROUND_CHECK_STATUS
1818 ,PAPF.BACKGROUND_DATE_CHECK
1819 ,PAPF.BLOOD_TYPE
1820 ,PAPF.CORRESPONDENCE_LANGUAGE
1821 ,PAPF.FAST_PATH_EMPLOYEE
1822 ,PAPF.FTE_CAPACITY
1823 ,PAPF.HOLD_APPLICANT_DATE_UNTIL
1824 ,PAPF.HONORS
1825 ,PAPF.INTERNAL_LOCATION
1826 ,PAPF.LAST_MEDICAL_TEST_BY
1827 ,PAPF.LAST_MEDICAL_TEST_DATE
1828 ,PAPF.MAILSTOP
1829 ,PAPF.OFFICE_NUMBER
1830 ,PAPF.ON_MILITARY_SERVICE
1831 ,PAPF.ORDER_NAME
1832 ,PAPF.PRE_NAME_ADJUNCT
1836 ,PAPF.REHIRE_RECOMMENDATION
1833 ,PAPF.PROJECTED_START_DATE
1834 ,PAPF.REHIRE_AUTHORIZOR
1835 ,PAPF.REHIRE_REASON
1837 ,PAPF.RESUME_EXISTS
1838 ,PAPF.RESUME_LAST_UPDATED
1839 ,PAPF.SECOND_PASSPORT_EXISTS
1840 ,PAPF.STUDENT_STATUS
1841 ,PAPF.SUFFIX
1842 ,PAPF.WORK_SCHEDULE
1843 ,PAPF.town_of_birth
1844 ,PAPF.region_of_birth
1845 ,PAPF.country_of_birth
1846 ,PAPF.global_person_id
1847 ,PAPF.party_id
1848 ,PAPF.original_date_of_hire
1849
1850 -- Bug2974671 starts here.
1851
1852 ,PAPF.BENEFIT_GROUP_ID
1853 ,PAPF.COORD_BEN_MED_PLN_NO
1854 ,PAPF.COORD_BEN_NO_CVG_FLAG
1855 ,PAPF.DPDNT_ADOPTION_DATE
1856 ,PAPF.DPDNT_VLNTRY_SVCE_FLAG
1857 ,PAPF.USES_TOBACCO_FLAG
1858
1859 --Bug2974671 ends here.
1860 ,PAPF.NPW_NUMBER -- Added for Fix for #3184546
1861
1862 FROM per_all_people_f PAPF,
1863 PER_PERSON_TYPES PPT,
1864 per_person_types PPT2
1865 WHERE PAPF.person_id = P_person_id
1866 AND PAPF.effective_end_date = P_date_end
1867 AND PPT.business_group_id = P_business_group_id
1868 and PAPF.business_group_id + 0 = P_Business_group_id
1869 AND PPT.default_flag = 'Y'
1870 AND PPT2.person_type_id = PAPF.person_type_id
1871 AND PPT.system_person_type =
1872 decode(PPT2.system_person_type,'APL', 'EX_APL'
1873 ,'APL_EX_APL', 'EX_APL'
1874 ,'EMP_APL', 'EMP'
1875 ,'EX_EMP', 'EX_APL'
1876 ,'EX_EMP_APL', 'EX_EMP' -- Added for fix of #3311891
1877 ,'EX_APL');
1878
1879 --
1880 END maintain_ppt_term;
1881 --
1882 --
1883 -- 3652025:
1884 -- -------------------------------------------------------------------------- +
1885 -- Name: cancel_ptu_updates
1886 -- Description: Performs PTU updates whenever there is a reverse termination.
1887 --
1888 -------------------------------------------------------------------------------
1889 PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
1890 p_Application_Id IN OUT NOCOPY NUMBER,
1891 p_Business_Group_Id NUMBER,
1892 p_Person_Id NUMBER,
1893 p_Date_Received DATE,
1894 p_Comments VARCHAR2,
1895 p_Current_Employer VARCHAR2,
1896 p_Date_End DATE,
1897 p_Projected_Hire_Date DATE,
1898 p_Successful_Flag VARCHAR2,
1899 p_Termination_Reason VARCHAR2,
1900 p_Appl_Attribute_Category VARCHAR2,
1901 p_Appl_Attribute1 VARCHAR2,
1902 p_Appl_Attribute2 VARCHAR2,
1903 p_Appl_Attribute3 VARCHAR2,
1904 p_Appl_Attribute4 VARCHAR2,
1905 p_Appl_Attribute5 VARCHAR2,
1906 p_Appl_Attribute6 VARCHAR2,
1907 p_Appl_Attribute7 VARCHAR2,
1908 p_Appl_Attribute8 VARCHAR2,
1909 p_Appl_Attribute9 VARCHAR2,
1910 p_Appl_Attribute10 VARCHAR2,
1911 p_Appl_Attribute11 VARCHAR2,
1912 p_Appl_Attribute12 VARCHAR2,
1913 p_Appl_Attribute13 VARCHAR2,
1914 p_Appl_Attribute14 VARCHAR2,
1915 p_Appl_Attribute15 VARCHAR2,
1916 p_Appl_Attribute16 VARCHAR2,
1917 p_Appl_Attribute17 VARCHAR2,
1918 p_Appl_Attribute18 VARCHAR2,
1919 p_Appl_Attribute19 VARCHAR2,
1920 p_Appl_Attribute20 VARCHAR2,
1921 p_Last_Update_Date DATE,
1922 p_Last_Updated_By NUMBER,
1923 p_Last_Update_Login NUMBER,
1924 p_Created_By NUMBER,
1925 p_Creation_Date DATE
1926 ) IS
1927 CURSOR C IS SELECT rowid FROM PER_APPLICATIONS
1928 WHERE application_id = p_Application_Id;
1929 CURSOR C2 IS SELECT per_applications_s.nextval FROM sys.dual;
1930 BEGIN
1931 if (p_Application_Id is NULL) then
1932 OPEN C2;
1933 FETCH C2 INTO p_Application_Id;
1934 CLOSE C2;
1935 end if;
1936 INSERT INTO PER_APPLICATIONS(
1937 application_id,
1938 business_group_id,
1939 person_id,
1940 date_received,
1944 projected_hire_date,
1941 comments,
1942 current_employer,
1943 date_end,
1945 successful_flag,
1946 termination_reason,
1947 appl_attribute_category,
1948 appl_attribute1,
1949 appl_attribute2,
1950 appl_attribute3,
1951 appl_attribute4,
1952 appl_attribute5,
1953 appl_attribute6,
1954 appl_attribute7,
1955 appl_attribute8,
1956 appl_attribute9,
1957 appl_attribute10,
1958 appl_attribute11,
1959 appl_attribute12,
1960 appl_attribute13,
1961 appl_attribute14,
1962 appl_attribute15,
1963 appl_attribute16,
1964 appl_attribute17,
1965 appl_attribute18,
1966 appl_attribute19,
1967 appl_attribute20,
1968 last_update_date,
1969 last_updated_by,
1970 last_update_login,
1971 created_by,
1972 creation_date
1973 ) VALUES (
1974 p_Application_Id,
1975 p_Business_Group_Id,
1976 p_Person_Id,
1977 p_Date_Received,
1978 p_Comments,
1979 p_Current_Employer,
1980 p_Date_End,
1981 p_Projected_Hire_Date,
1982 p_Successful_Flag,
1983 p_Termination_Reason,
1984 p_Appl_Attribute_Category,
1985 p_Appl_Attribute1,
1986 p_Appl_Attribute2,
1987 p_Appl_Attribute3,
1988 p_Appl_Attribute4,
1989 p_Appl_Attribute5,
1990 p_Appl_Attribute6,
1991 p_Appl_Attribute7,
1992 p_Appl_Attribute8,
1993 p_Appl_Attribute9,
1994 p_Appl_Attribute10,
1995 p_Appl_Attribute11,
1996 p_Appl_Attribute12,
1997 p_Appl_Attribute13,
1998 p_Appl_Attribute14,
1999 p_Appl_Attribute15,
2000 p_Appl_Attribute16,
2001 p_Appl_Attribute17,
2002 p_Appl_Attribute18,
2003 p_Appl_Attribute19,
2004 p_Appl_Attribute20,
2005 p_Last_Update_Date,
2006 p_Last_Updated_By,
2007 p_Last_Update_Login,
2008 p_Created_By,
2009 p_Creation_Date
2010 );
2011
2012 OPEN C;
2013 FETCH C INTO p_Rowid;
2014 if (C%NOTFOUND) then
2015 CLOSE C;
2016 RAISE NO_DATA_FOUND;
2017 end if;
2018 CLOSE C;
2019 END Insert_Row;
2020 PROCEDURE Lock_Row(p_Rowid VARCHAR2,
2021 p_Application_Id NUMBER,
2022 p_Business_Group_Id NUMBER,
2023 p_Person_Id NUMBER,
2024 p_Date_Received DATE,
2025 p_Comments VARCHAR2,
2026 p_Current_Employer VARCHAR2,
2027 p_Date_End DATE,
2028 p_Projected_Hire_Date DATE,
2029 p_Successful_Flag VARCHAR2,
2030 p_Termination_Reason VARCHAR2,
2031 p_Appl_Attribute_Category VARCHAR2,
2032 p_Appl_Attribute1 VARCHAR2,
2033 p_Appl_Attribute2 VARCHAR2,
2034 p_Appl_Attribute3 VARCHAR2,
2035 p_Appl_Attribute4 VARCHAR2,
2036 p_Appl_Attribute5 VARCHAR2,
2037 p_Appl_Attribute6 VARCHAR2,
2038 p_Appl_Attribute7 VARCHAR2,
2039 p_Appl_Attribute8 VARCHAR2,
2040 p_Appl_Attribute9 VARCHAR2,
2041 p_Appl_Attribute10 VARCHAR2,
2042 p_Appl_Attribute11 VARCHAR2,
2043 p_Appl_Attribute12 VARCHAR2,
2044 p_Appl_Attribute13 VARCHAR2,
2045 p_Appl_Attribute14 VARCHAR2,
2046 p_Appl_Attribute15 VARCHAR2,
2047 p_Appl_Attribute16 VARCHAR2,
2048 p_Appl_Attribute17 VARCHAR2,
2049 p_Appl_Attribute18 VARCHAR2,
2050 p_Appl_Attribute19 VARCHAR2,
2051 p_Appl_Attribute20 VARCHAR2
2052 ) IS
2053 CURSOR C IS
2054 SELECT *
2055 FROM PER_APPLICATIONS
2056 WHERE rowid = p_Rowid
2057 FOR UPDATE of Application_Id NOWAIT;
2058 Recinfo C%ROWTYPE;
2059 BEGIN
2060 OPEN C;
2061 FETCH C INTO Recinfo;
2062 if (C%NOTFOUND) then
2063 CLOSE C;
2064 RAISE NO_DATA_FOUND;
2065 end if;
2066 CLOSE C;
2067 --
2068 --
2069 Recinfo.appl_attribute4 := rtrim(Recinfo.appl_attribute4);
2070 Recinfo.appl_attribute5 := rtrim(Recinfo.appl_attribute5);
2071 Recinfo.appl_attribute6 := rtrim(Recinfo.appl_attribute6);
2072 Recinfo.appl_attribute7 := rtrim(Recinfo.appl_attribute7);
2076 Recinfo.appl_attribute11 := rtrim(Recinfo.appl_attribute11);
2073 Recinfo.appl_attribute8 := rtrim(Recinfo.appl_attribute8);
2074 Recinfo.appl_attribute9 := rtrim(Recinfo.appl_attribute9);
2075 Recinfo.appl_attribute10 := rtrim(Recinfo.appl_attribute10);
2077 Recinfo.appl_attribute12 := rtrim(Recinfo.appl_attribute12);
2078 Recinfo.appl_attribute13 := rtrim(Recinfo.appl_attribute13);
2079 Recinfo.appl_attribute14 := rtrim(Recinfo.appl_attribute14);
2080 Recinfo.appl_attribute15 := rtrim(Recinfo.appl_attribute15);
2081 Recinfo.appl_attribute16 := rtrim(Recinfo.appl_attribute16);
2082 Recinfo.appl_attribute17 := rtrim(Recinfo.appl_attribute17);
2083 Recinfo.appl_attribute18 := rtrim(Recinfo.appl_attribute18);
2084 Recinfo.appl_attribute19 := rtrim(Recinfo.appl_attribute19);
2085 Recinfo.appl_attribute20 := rtrim(Recinfo.appl_attribute20);
2086 Recinfo.comments := rtrim(Recinfo.comments);
2087 Recinfo.current_employer := rtrim(Recinfo.current_employer);
2088 Recinfo.successful_flag := rtrim(Recinfo.successful_flag);
2089 Recinfo.termination_reason := rtrim(Recinfo.termination_reason);
2090 Recinfo.appl_attribute_category := rtrim(Recinfo.appl_attribute_category);
2091 Recinfo.appl_attribute1 := rtrim(Recinfo.appl_attribute1);
2092 Recinfo.appl_attribute3 := rtrim(Recinfo.appl_attribute3);
2093 --
2094 --
2095 if (
2096 ( (Recinfo.application_id = p_Application_Id)
2097 OR ( (Recinfo.application_id IS NULL)
2098 AND (p_Application_Id IS NULL)))
2099 AND ( (Recinfo.business_group_id = p_Business_Group_Id)
2100 OR ( (Recinfo.business_group_id IS NULL)
2101 AND (p_Business_Group_Id IS NULL)))
2102 AND ( (Recinfo.person_id = p_Person_Id)
2103 OR ( (Recinfo.person_id IS NULL)
2104 AND (p_Person_Id IS NULL)))
2105 AND ( (Recinfo.date_received = p_Date_Received)
2106 OR ( (Recinfo.date_received IS NULL)
2107 AND (p_Date_Received IS NULL)))
2108 AND ( (Recinfo.comments = p_Comments)
2109 OR ( (Recinfo.comments IS NULL)
2110 AND (p_Comments IS NULL)))
2111 AND ( (Recinfo.current_employer = p_Current_Employer)
2112 OR ( (Recinfo.current_employer IS NULL)
2113 AND (p_Current_Employer IS NULL)))
2114 AND ( (Recinfo.date_end = p_Date_End)
2115 OR ( (Recinfo.date_end IS NULL)
2116 AND (p_Date_End IS NULL)))
2117 AND ( (Recinfo.projected_hire_date = p_Projected_Hire_Date)
2118 OR ( (Recinfo.projected_hire_date IS NULL)
2119 AND (p_Projected_Hire_Date IS NULL)))
2120 AND ( (Recinfo.successful_flag = p_Successful_Flag)
2121 OR ( (Recinfo.successful_flag IS NULL)
2122 AND (p_Successful_Flag IS NULL)))
2123 AND ( (Recinfo.termination_reason = p_Termination_Reason)
2124 OR ( (Recinfo.termination_reason IS NULL)
2125 AND (p_Termination_Reason IS NULL)))
2126 AND ( (Recinfo.appl_attribute_category = p_Appl_Attribute_Category)
2127 OR ( (Recinfo.appl_attribute_category IS NULL)
2128 AND (p_Appl_Attribute_Category IS NULL)))
2129 AND ( (Recinfo.appl_attribute1 = p_Appl_Attribute1)
2130 OR ( (Recinfo.appl_attribute1 IS NULL)
2131 AND (p_Appl_Attribute1 IS NULL)))
2132 AND ( (Recinfo.appl_attribute2 = p_Appl_Attribute2)
2133 OR ( (Recinfo.appl_attribute2 IS NULL)
2134 AND (p_Appl_Attribute2 IS NULL)))
2135 AND ( (Recinfo.appl_attribute3 = p_Appl_Attribute3)
2136 OR ( (Recinfo.appl_attribute3 IS NULL)
2137 AND (p_Appl_Attribute3 IS NULL)))
2138 AND ( (Recinfo.appl_attribute4 = p_Appl_Attribute4)
2139 OR ( (Recinfo.appl_attribute4 IS NULL)
2140 AND (p_Appl_Attribute4 IS NULL)))
2141 AND ( (Recinfo.appl_attribute5 = p_Appl_Attribute5)
2142 OR ( (Recinfo.appl_attribute5 IS NULL)
2143 AND (p_Appl_Attribute5 IS NULL)))
2144 AND ( (Recinfo.appl_attribute6 = p_Appl_Attribute6)
2145 OR ( (Recinfo.appl_attribute6 IS NULL)
2146 AND (p_Appl_Attribute6 IS NULL)))
2147 AND ( (Recinfo.appl_attribute7 = p_Appl_Attribute7)
2148 OR ( (Recinfo.appl_attribute7 IS NULL)
2149 AND (p_Appl_Attribute7 IS NULL)))
2150 AND ( (Recinfo.appl_attribute8 = p_Appl_Attribute8)
2151 OR ( (Recinfo.appl_attribute8 IS NULL)
2152 AND (p_Appl_Attribute8 IS NULL)))
2153 AND ( (Recinfo.appl_attribute9 = p_Appl_Attribute9)
2154 OR ( (Recinfo.appl_attribute9 IS NULL)
2155 AND (p_Appl_Attribute9 IS NULL)))
2156 AND ( (Recinfo.appl_attribute10 = p_Appl_Attribute10)
2157 OR ( (Recinfo.appl_attribute10 IS NULL)
2158 AND (p_Appl_Attribute10 IS NULL)))
2159 AND ( (Recinfo.appl_attribute11 = p_Appl_Attribute11)
2160 OR ( (Recinfo.appl_attribute11 IS NULL)
2161 AND (p_Appl_Attribute11 IS NULL)))
2162 AND ( (Recinfo.appl_attribute12 = p_Appl_Attribute12)
2163 OR ( (Recinfo.appl_attribute12 IS NULL)
2164 AND (p_Appl_Attribute12 IS NULL)))
2165 AND ( (Recinfo.appl_attribute13 = p_Appl_Attribute13)
2166 OR ( (Recinfo.appl_attribute13 IS NULL)
2167 AND (p_Appl_Attribute13 IS NULL)))
2168 AND ( (Recinfo.appl_attribute14 = p_Appl_Attribute14)
2169 OR ( (Recinfo.appl_attribute14 IS NULL)
2173 AND (p_Appl_Attribute15 IS NULL)))
2170 AND (p_Appl_Attribute14 IS NULL)))
2171 AND ( (Recinfo.appl_attribute15 = p_Appl_Attribute15)
2172 OR ( (Recinfo.appl_attribute15 IS NULL)
2174 AND ( (Recinfo.appl_attribute16 = p_Appl_Attribute16)
2175 OR ( (Recinfo.appl_attribute16 IS NULL)
2176 AND (p_Appl_Attribute16 IS NULL)))
2177 AND ( (Recinfo.appl_attribute17 = p_Appl_Attribute17)
2178 OR ( (Recinfo.appl_attribute17 IS NULL)
2179 AND (p_Appl_Attribute17 IS NULL)))
2180 AND ( (Recinfo.appl_attribute18 = p_Appl_Attribute18)
2181 OR ( (Recinfo.appl_attribute18 IS NULL)
2182 AND (p_Appl_Attribute18 IS NULL)))
2183 AND ( (Recinfo.appl_attribute19 = p_Appl_Attribute19)
2184 OR ( (Recinfo.appl_attribute19 IS NULL)
2185 AND (p_Appl_Attribute19 IS NULL)))
2186 AND ( (Recinfo.appl_attribute20 = p_Appl_Attribute20)
2187 OR ( (Recinfo.appl_attribute20 IS NULL)
2188 AND (p_Appl_Attribute20 IS NULL)))
2189 ) then
2190 return;
2191 else
2192 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2193 APP_EXCEPTION.RAISE_EXCEPTION;
2194 end if;
2195 END Lock_Row;
2196
2197 PROCEDURE Update_Row(p_Rowid VARCHAR2,
2198 p_Application_Id NUMBER,
2199 p_Business_Group_Id NUMBER,
2200 p_Person_Id NUMBER,
2201 p_Person_Type_Id NUMBER,
2202 p_Date_Received DATE,
2203 p_Comments VARCHAR2,
2204 p_Current_Employer VARCHAR2,
2205 p_Date_End DATE,
2206 p_Projected_Hire_Date DATE,
2207 p_Successful_Flag VARCHAR2,
2208 p_Termination_Reason VARCHAR2,
2209 p_Cancellation_Flag VARCHAR2, -- parameter added for Bug 3053711
2210 p_Appl_Attribute_Category VARCHAR2,
2211 p_Appl_Attribute1 VARCHAR2,
2212 p_Appl_Attribute2 VARCHAR2,
2213 p_Appl_Attribute3 VARCHAR2,
2214 p_Appl_Attribute4 VARCHAR2,
2215 p_Appl_Attribute5 VARCHAR2,
2216 p_Appl_Attribute6 VARCHAR2,
2217 p_Appl_Attribute7 VARCHAR2,
2218 p_Appl_Attribute8 VARCHAR2,
2219 p_Appl_Attribute9 VARCHAR2,
2220 p_Appl_Attribute10 VARCHAR2,
2221 p_Appl_Attribute11 VARCHAR2,
2222 p_Appl_Attribute12 VARCHAR2,
2223 p_Appl_Attribute13 VARCHAR2,
2224 p_Appl_Attribute14 VARCHAR2,
2225 p_Appl_Attribute15 VARCHAR2,
2226 p_Appl_Attribute16 VARCHAR2,
2227 p_Appl_Attribute17 VARCHAR2,
2228 p_Appl_Attribute18 VARCHAR2,
2229 p_Appl_Attribute19 VARCHAR2,
2230 p_Appl_Attribute20 VARCHAR2
2231 ) IS
2232
2233 --changed for 2506446 from the old select
2234 cursor csr_ptu_row is
2235 select ptu.effective_start_date
2236 from per_person_type_usages_f ptu
2237 ,per_person_types ppt
2238 where ptu.person_id = p_person_id
2239 and ptu.effective_start_date > p_date_received
2240 and ptu.person_type_id = ppt.person_type_id
2241 and ppt.system_person_type = 'EX_APL'
2242 order by ptu.effective_start_date;
2243
2244 --Bug 3891787 Added the cursor to check for the person_type change
2245 cursor csr_ptu_row1
2246 is
2247 select ptu.person_type_id,ptu.effective_start_date
2248 from per_person_type_usages_f ptu
2249 where ptu.person_id = p_person_id
2250 and p_date_end+1 between ptu.effective_start_date and
2251 ptu.effective_start_date;
2252
2253 l_person_type_id per_person_type_usages.person_id%type;
2254 l_start_date date;
2255 l_date_end date;
2256 l_update_mode varchar2(30);
2257
2258 BEGIN
2259 hr_utility.set_location('per_applications_pkg.update_row',10);
2260 -- Bug 3053711 Start
2261 -- Added the check if flag = 'Y'
2262 --Commented out for the Bug 4202317
2263 -- if p_Cancellation_Flag = 'Y' then
2264 UPDATE PER_APPLICATIONS
2265 SET
2266 application_id = p_Application_Id,
2267 business_group_id = p_Business_Group_Id,
2268 person_id = p_Person_Id,
2269 date_received = p_Date_Received,
2270 comments = p_Comments,
2271 current_employer = p_Current_Employer,
2272 date_end = p_Date_End,
2273 projected_hire_date = p_Projected_Hire_Date,
2277 appl_attribute1 = p_Appl_Attribute1,
2274 successful_flag = p_Successful_Flag,
2275 termination_reason = p_Termination_Reason,
2276 appl_attribute_category = p_Appl_Attribute_Category,
2278 appl_attribute2 = p_Appl_Attribute2,
2279 appl_attribute3 = p_Appl_Attribute3,
2280 appl_attribute4 = p_Appl_Attribute4,
2281 appl_attribute5 = p_Appl_Attribute5,
2282 appl_attribute6 = p_Appl_Attribute6,
2283 appl_attribute7 = p_Appl_Attribute7,
2284 appl_attribute8 = p_Appl_Attribute8,
2285 appl_attribute9 = p_Appl_Attribute9,
2286 appl_attribute10 = p_Appl_Attribute10,
2287 appl_attribute11 = p_Appl_Attribute11,
2288 appl_attribute12 = p_Appl_Attribute12,
2289 appl_attribute13 = p_Appl_Attribute13,
2290 appl_attribute14 = p_Appl_Attribute14,
2291 appl_attribute15 = p_Appl_Attribute15,
2292 appl_attribute16 = p_Appl_Attribute16,
2293 appl_attribute17 = p_Appl_Attribute17,
2294 appl_attribute18 = p_Appl_Attribute18,
2295 appl_attribute19 = p_Appl_Attribute19,
2296 appl_attribute20 = p_Appl_Attribute20
2297 WHERE rowid = p_rowid;
2298 --Commented out for the Bug 4202317
2299 /*else
2300 UPDATE PER_APPLICATIONS
2301 SET
2302 application_id = p_Application_Id,
2303 business_group_id = p_Business_Group_Id,
2304 person_id = p_Person_Id,
2305 date_received = p_Date_Received,
2306 comments = p_Comments,
2307 current_employer = p_Current_Employer,
2308 date_end = p_Date_End,
2309 projected_hire_date = p_Projected_Hire_Date,
2310 successful_flag = p_Successful_Flag,
2311 termination_reason = p_Termination_Reason,
2312 appl_attribute_category = p_Appl_Attribute_Category,
2313 appl_attribute1 = p_Appl_Attribute1,
2314 appl_attribute2 = p_Appl_Attribute2,
2315 appl_attribute3 = p_Appl_Attribute3,
2316 appl_attribute4 = p_Appl_Attribute4,
2317 appl_attribute5 = p_Appl_Attribute5,
2318 appl_attribute6 = p_Appl_Attribute6,
2319 appl_attribute7 = p_Appl_Attribute7,
2320 appl_attribute8 = p_Appl_Attribute8,
2321 appl_attribute9 = p_Appl_Attribute9,
2322 appl_attribute10 = p_Appl_Attribute10,
2323 appl_attribute11 = p_Appl_Attribute11,
2324 appl_attribute12 = p_Appl_Attribute12,
2325 appl_attribute13 = p_Appl_Attribute13,
2326 appl_attribute14 = p_Appl_Attribute14,
2327 appl_attribute15 = p_Appl_Attribute15,
2328 appl_attribute16 = p_Appl_Attribute16,
2329 appl_attribute17 = p_Appl_Attribute17,
2330 appl_attribute18 = p_Appl_Attribute18,
2331 appl_attribute19 = p_Appl_Attribute19,
2332 appl_attribute20 = p_Appl_Attribute20
2333 WHERE rowid = p_rowid;
2334 end if;*/
2335 -- Bug 3053711 End
2336 hr_utility.set_location('per_applications_pkg.update_row',20);
2337 if (SQL%NOTFOUND) then
2338 RAISE NO_DATA_FOUND;
2339 end if;
2340
2341 hr_utility.set_location('per_applications_pkg.update_row',30);
2342 --
2343 -- Now maintain the PTU data...
2344 --
2345 -- 3652025: Another package will do the updates when performing a termination.
2346 -- The reverse termination is still part of this update.
2347 --
2348 if p_Date_End is not null then
2349 NULL;
2350 --
2351 -- Either terminating or updating an already
2352 -- terminated application.
2353 --
2354 -- PTU : Following code added for PTU
2355 --
2356 --hr_utility.set_location('per_applications_pkg.update_row',40);
2357
2358 --Bug No 3891787 starts here
2359 --Open csr_ptu_row1;
2360 --fetch csr_ptu_row1 into l_person_type_id,l_start_date;
2361 --if csr_ptu_row1%notfound then
2362 -- null;
2363 --end if;
2364 --if nvl(l_person_type_id,-1) <> p_person_type_id then
2365 -- if p_date_end +1 = l_start_date then
2366 -- l_update_mode := hr_api.g_correction;
2367 -- end if;
2368 -- hr_per_type_usage_internal.maintain_person_type_usage
2369 -- ( p_effective_date => p_Date_End+1
2370 -- ,p_person_id => p_Person_id
2374 ( p_Business_Group_Id
2371 -- ,p_person_type_id => p_Person_Type_Id
2372 -- ,p_datetrack_update_mode => l_update_mode
2373 /*hr_person_type_usage_info.get_default_person_type_id
2375 ,'EX_APL')*/
2376
2377 -- );
2378 --end if;
2379 --close csr_ptu_row1;
2380 --Bug No 3891787 ends here
2381 --hr_utility.set_location('per_applications_pkg.update_row',50);
2382 -- End of PTU Changes
2383 --
2384 -- hr_per_type_usage_internal.maintain_ptu(
2385 -- p_action => 'TERM_APL',
2386 -- p_person_id => p_Person_id,
2387 -- p_actual_termination_date => p_Date_End);
2388 else
2389 --
2390 -- Either rev-terming or updating an unterminated application
2391 --
2392 -- hr_per_type_usage_internal.maintain_ptu(
2393 -- p_action => 'REV_TERM_APL',
2394 -- p_date_start => p_date_received,
2395 -- p_person_id => p_person_id);
2396
2397 -- PTU : Following code added for PTU (and changed for bug 2506446)
2398
2399 open csr_ptu_row;
2400 fetch csr_ptu_row into l_date_end;
2401 close csr_ptu_row;
2402
2403 hr_utility.set_location('per_applications_pkg.p_date_received = '||to_char(p_date_received,'DD/MM/YYYY'),60);
2404 hr_utility.set_location('per_applications_pkg.p_date_end = '||to_char(l_date_end,'DD/MM/YYYY'),60);
2405 hr_utility.set_location('per_applications_pkg.p_person_id = '||to_char(p_person_id),60);
2406
2407 hr_per_type_usage_internal.cancel_person_type_usage
2408 (
2409 p_effective_date => l_date_end
2410 ,p_person_id => p_person_id
2411 ,p_system_person_type => 'EX_APL'
2412 );
2413
2414 -- End of PTU Changes
2415
2416 end if;
2417 END Update_Row;
2418 --
2419 PROCEDURE Delete_Row(p_Rowid VARCHAR2) IS
2420 BEGIN
2421 DELETE FROM PER_APPLICATIONS
2422 WHERE rowid = p_Rowid;
2423
2424 if (SQL%NOTFOUND) then
2425 RAISE NO_DATA_FOUND;
2426 end if;
2427 END Delete_Row;
2428 --
2429 -- ----------------------------------------------------------------------------
2430 -- |-----------------------< maintain_irc_ass_status >----------------------|
2431 -- ----------------------------------------------------------------------------
2432 procedure maintain_irc_ass_status(p_person_id number,
2433 p_business_group_id number,
2434 p_date_end date,
2435 p_effective_date date,
2436 p_application_id number,
2437 p_legislation_code varchar2,
2438 p_action varchar2) is
2439 --
2440 l_assignment_id per_all_assignments_f.assignment_id%Type;
2441 l_irc_ass_status_id irc_assignment_statuses.assignment_status_id%Type;
2442 l_ass_status per_assignment_status_types.per_system_status%Type;
2443 l_irc_asg_status_ovn irc_assignment_statuses.object_version_number%Type;
2444 l_ass_status_type_id per_all_assignments_f.assignment_status_type_id%Type;
2445 --
2446 -- To get the assignment status based o the action (Termination or
2447 -- Reverse termination)
2448 cursor csr_get_asg_status is
2449 select a.assignment_status_type_id
2450 from per_assignment_status_types a,
2451 per_ass_status_type_amends b
2452 where a.per_system_status = l_ass_status
2453 and b.assignment_status_type_id(+) = a.assignment_status_type_id
2454 and b.business_group_id(+) + 0 = p_business_group_id
2455 and nvl(a.business_group_id, p_business_group_id) =
2456 p_business_group_id
2457 and nvl(a.legislation_codE, p_legislation_code) =
2458 p_legislation_code
2459 and nvl(b.active_flag, a.active_flag) = 'Y'
2460 and nvl(b.default_flag, a.default_flag) = 'Y';
2461 --
2462 -- To get all the assignment id's for the concerned application to be
2463 -- terminated
2464 cursor csr_term_ass_id is
2465 select paa.assignment_id
2466 from per_all_assignments_f paa
2467 where paa.application_id = p_application_id
2468 and paa.person_id = p_person_id
2469 and paa.business_group_id + 0 = p_business_group_id
2470 and paa.assignment_type = 'A'
2471 and paa.effective_end_date =
2472 (select max(pa2.effective_end_date)
2473 from per_all_assignments_f pa2
2474 where pa2.person_id = p_person_id
2475 and pa2.application_id = p_application_id);
2476 --
2477 -- To get all the assignment id's for the concerned application to be
2478 -- reverse terminated
2479 cursor csr_cancel_ass_id is
2480 select paa.assignment_id
2481 from per_all_assignments_f paa
2482 where paa.application_id = p_application_id
2483 and paa.person_id = p_person_id
2484 and paa.business_group_id + 0 = p_business_group_id
2485 and paa.assignment_type = 'A'
2486 and paa.effective_end_date = p_date_end;
2487 --
2488 begin
2489 --
2490 hr_utility.set_location('PER_APPLICATIONS_PKG.maintain_irc_ass_status', 10);
2491 --
2492 -- Termination of applicant
2493 if p_action = 'TERM' then
2494 --
2495 l_ass_status := 'TERM_APL';
2496 --
2497 hr_utility.set_location('PER_APPLICATIONS_PKG.maintain_irc_ass_status', 20);
2498 --
2499 open csr_get_asg_status;
2500 fetch csr_get_asg_status into l_ass_status_type_id;
2501 close csr_get_asg_status;
2502 --
2503 open csr_term_ass_id;
2504 loop
2505 fetch csr_term_ass_id into l_assignment_id;
2506 exit when csr_term_ass_id%notfound;
2507 --
2508 irc_asg_status_api.create_irc_asg_status
2509 (p_assignment_id => l_assignment_id,
2510 p_assignment_status_type_id => l_ass_status_type_id,
2511 p_status_change_date => p_effective_date,
2512 p_assignment_status_id => l_irc_ass_status_id,
2513 p_object_version_number => l_irc_asg_status_ovn);
2514 --
2515 end loop;
2516 close csr_term_ass_id;
2517 --
2518 -- Reverse termination of applicant
2519 else
2520 --
2521 l_ass_status := 'ACTIVE_APL';
2522 --
2523 hr_utility.set_location('PER_APPLICATIONS_PKG.maintain_irc_ass_status', 30);
2524 --
2525 open csr_get_asg_status;
2526 fetch csr_get_asg_status into l_ass_status_type_id;
2527 close csr_get_asg_status;
2528 --
2529 open csr_cancel_ass_id;
2530 loop
2531 fetch csr_cancel_ass_id into l_assignment_id;
2532 exit when csr_cancel_ass_id%notfound;
2533 --
2534 irc_asg_status_api.create_irc_asg_status
2535 (p_assignment_id => l_assignment_id,
2536 p_assignment_status_type_id => l_ass_status_type_id,
2537 p_status_change_date => p_effective_date,
2538 p_assignment_status_id => l_irc_ass_status_id,
2539 p_object_version_number => l_irc_asg_status_ovn);
2540 --
2541 end loop;
2542 close csr_cancel_ass_id;
2543 --
2544 end if;
2545 --
2546 hr_utility.set_location('PER_APPLICATIONS_PKG.maintain_irc_ass_status', 40);
2547 --
2548 end maintain_irc_ass_status;
2549 --
2550 END PER_APPLICATIONS_PKG;