[Home] [Help]
PACKAGE BODY: APPS.PER_APPLICATIONS_PKG
Source
1 PACKAGE BODY PER_APPLICATIONS_PKG as
2 /* $Header: peapp01t.pkb 120.7.12020000.6 2013/05/27 06:13:48 srannama ship $ */
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 -------------------------------------------------------------------------------------------------------
50 -- term-update_ass_bud_val
47 -------------------------------------------------------------------------------------------------------
48 -- PRIVATE PROCEDURE
49 -- Name
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,
197 and abv.effective_start_date = l_c1.effective_start_date
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
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
325 and abv.effective_start_date = l_c1.effective_start_date
326 and abv.effective_end_date = l_c1.effective_end_date;
327 --
328 l_old.assignment_id := l_c1.assignment_id;
329 l_old.business_group_id := l_c1.business_group_id;
333 l_old.effective_end_date := l_c1.effective_end_date;
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;
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
443 WHERE l.letter_request_id = r.letter_request_id
444 AND l.business_group_id + 0 = p_business_group_id);
445 --
446 ELSE CLOSE c_chk_empty_requests;
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 --
549 v_test_new_req NUMBER(1);
546 --
547 v_dummy_asg_id NUMBER(1);
548 v_letter_request_id NUMBER(15);
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;
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;
564 return;
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
684 , LAST_UPDATE_DATE
685 , LAST_UPDATED_BY
686 , LAST_UPDATE_LOGIN
687 , CREATED_BY
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
774 PER_LETTER_GEN_STATUSES s,
771 CURSOR csr_let_req_id IS
772 SELECT r.letter_request_id
773 FROM PER_LETTER_REQUESTS R,
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
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'
796 and R.REQUEST_STATUS = 'PENDING'
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
904 AND pp.active_flag ='Y'
905 AND pp.system_person_type IN ('EMP'));
906 --
907 -- Bug 3380724 Ends Here
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
961 CURSOR get_actve_apl IS
958 and t.assignment_status_type_id = asg.assignment_status_type_id;
959
960 --
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
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
1020 AND A.business_group_id + 0 = p_business_group_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
1038 PROCEDURE cancel_update_assigns_obg(p_person_id NUMBER,
1039 p_business_group_id NUMBER,
1040 P_date_end DATE,
1041 P_application_id NUMBER,
1042 p_legislation_code VARCHAR2,
1043 P_end_of_time DATE,
1044 P_last_updated_by NUMBER,
1045 p_last_update_login NUMBER,
1046 p_assignment_id NUMBER) IS
1047
1048 CURSOR c_chk_prv_status IS
1049 select 1
1050 from per_assignment_status_types t
1051 , per_assignments_f asg
1052 where asg.person_id = p_person_id
1053 and nvl(t.business_group_id,
1054 p_business_group_id) = p_business_group_id
1055 and t.PER_SYSTEM_STATUS = 'TERM_APL'
1056 and asg.effective_start_date <= P_date_end
1057 and asg.effective_end_date >= P_date_end
1058 and asg.business_group_id + 0 = p_business_group_id
1059 and asg.application_id = P_application_id
1060 and t.assignment_status_type_id = asg.assignment_status_type_id;
1061
1062 --
1063 CURSOR get_actve_apl IS
1064 SELECT a.assignment_status_type_id
1065 FROM per_assignment_status_types a
1066 , per_ass_status_type_amends b
1067 WHERE a.per_system_status = 'ACTIVE_APL'
1068 AND b.assignment_status_type_id(+) = a.assignment_status_type_id
1069 AND b.business_group_id(+) + 0 = p_business_group_id
1070 AND nvl(a.business_group_id, p_business_group_id) =
1071 p_business_group_id
1072 AND nvl(a.legislation_codE,
1073 p_legislation_code) = p_legislation_code
1074 AND NVL(B.ACTIVE_FLAG,A.ACTIVE_FLAG) = 'Y'
1075 and nvl(B.DEFAULT_FLAG, A.DEFAULT_FLAG) = 'Y';
1076 --
1077 v_dummy_ast NUMBER(1);
1078 v_act_ass_stat_id NUMBER(15);
1079 --
1080
1081 BEGIN
1082 OPEN c_chk_prv_status;
1083 FETCH c_chk_prv_status INTO v_dummy_ast;
1084 IF c_chk_prv_status%FOUND THEN
1085 CLOSE c_chk_prv_status;
1086 OPEN get_actve_apl;
1087 FETCH get_actve_apl INTO v_act_ass_stat_id;
1088 CLOSE get_actve_apl;
1089 UPDATE PER_ALL_ASSIGNMENTS_F A
1090 SET A.LAST_UPDATE_DATE = trunc(sysdate)
1091 , A.LAST_UPDATED_BY = P_last_updated_by
1092 , A.LAST_UPDATE_LOGIN = p_last_update_login
1093 , A.EFFECTIVE_END_DATE = P_end_of_time
1094 , A.ASSIGNMENT_STATUS_TYPE_ID = v_act_ass_stat_id
1095 WHERE A.APPLICATION_ID = P_application_id
1096 AND A.PERSON_ID = p_person_id
1097 AND A.business_group_id + 0 = p_business_group_id
1098 AND A.ASSIGNMENT_TYPE = 'A'
1099 AND A.EFFECTIVE_END_DATE = P_date_end;
1100
1101
1102 -- call to new proc required due to date tracking assignment budget values. To cancel termination
1103 -- of the assignment budget values.
1104 --SASmith 17-APR-1998
1105 cancel_update_ass_bud_val(p_application_id
1106 ,p_person_id
1107 ,p_business_group_id
1108 ,p_date_end
1109 ,p_end_of_time
1110 ,p_last_updated_by
1111 ,p_last_update_login);
1112 --
1113 ELSE CLOSE c_chk_prv_status;
1114 --
1115 UPDATE PER_ALL_ASSIGNMENTS_F A
1116 SET A.LAST_UPDATE_DATE = trunc(sysdate)
1117 , A.LAST_UPDATED_BY = P_last_updated_by
1118 , A.LAST_UPDATE_LOGIN = p_last_update_login
1119 , A.EFFECTIVE_END_DATE = P_end_of_time
1120 WHERE A.APPLICATION_ID = P_application_id
1121 AND A.PERSON_ID = p_person_id
1122 AND A.business_group_id + 0 = p_business_group_id
1123 AND A.ASSIGNMENT_TYPE = 'A'
1124 AND A.EFFECTIVE_END_DATE = P_date_end --;
1125 and a.assignment_id = p_assignment_id; -- fix for the bug#12593632
1126 -- call to new proc required due to date tracking assignment budget values. To cancel termination
1127 -- of the assignment budget values.
1128 --SASmith 17-APR-1998
1129 cancel_update_ass_bud_val(p_application_id
1130 ,p_person_id
1131 ,p_business_group_id
1132 ,p_date_end
1133 ,p_end_of_time
1134 ,p_last_updated_by
1135 ,p_last_update_login);
1136 END IF;
1137 END cancel_update_assigns_obg;
1138
1139 ----------------------------------------------------------------------------
1140
1141
1142 -- Name --
1143 -- term_update_assignments --
1144 -- Purpose --
1145 -- when terminating an applicant close down all the applicant assignments
1146 -- as of the termination date.
1147 -- Arguments --
1148 -- See below. --
1149 -- Notes --
1150 -----------------------------------------------------------------------------
1151 PROCEDURE term_update_assignments(p_person_id NUMBER,
1152 p_business_group_id NUMBER,
1153 P_date_end DATE,
1154 P_application_id NUMBER,
1155 p_last_updated_by NUMBER,
1156 p_last_update_login NUMBER) IS
1157
1158 -- CURSOR c_chk_assigns IS
1159 -- SELECT 1
1160 -- FROM per_all_assignments_f a
1161 -- WHERE a.application_id = P_application_id
1162 -- AND a.person_id = p_person_id
1163 -- AND a.business_group_id + 0 = p_business_group_id
1164 -- AND a.assignment_type = 'A'
1165 -- AND a.effective_start_date > P_date_end;
1166 --
1167 --dummy_assign NUMBER(1);
1168 --
1169 cursor csr_get_future_assignments is
1170 select assignment_id, object_version_number, effective_start_date
1171 from per_all_assignments_f a
1172 WHERE a.application_id = P_application_id
1176 AND a.effective_start_date > P_date_end
1173 AND a.person_id = p_person_id
1174 AND a.business_group_id = p_business_group_id
1175 AND a.assignment_type = 'A'
1177 AND not exists
1178 (select 'Y'
1179 from per_all_assignments_f paf2
1180 where paf2.assignment_id = a.assignment_id
1181 and paf2.effective_start_date < a.EFFECTIVE_START_DATE);
1182 --
1183 l_validation_start_date DATE;
1184 l_validation_end_date DATE;
1185 l_effective_start_date DATE;
1186 l_effective_end_date DATE;
1187 l_business_group_id hr_all_organization_units.organization_id%TYPE;
1188 l_org_now_no_manager_warning BOOLEAN;
1189 --
1190 BEGIN
1191 -- Delete all future assignments
1192 FOR l_assignment in csr_get_future_assignments LOOP
1193 per_asg_del.del
1194 (p_assignment_id => l_assignment.assignment_id
1195 ,p_object_version_number => l_assignment.object_version_number
1196 ,p_effective_date => l_assignment.effective_start_date --p_date_end+1
1197 ,p_datetrack_mode => hr_api.g_zap
1198 ,p_effective_start_date => l_effective_start_date
1199 ,p_effective_end_date => l_effective_end_date
1200 ,p_business_group_id => l_business_group_id
1201 ,p_validation_start_date => l_validation_start_date
1202 ,p_validation_end_date => l_validation_end_date
1203 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1204 );
1205 END LOOP;
1206 -- Delete DT updates
1207 DELETE per_all_assignments_f a
1208 WHERE a.application_id = P_application_id
1209 AND a.person_id = p_person_id
1210 AND a.business_group_id = p_business_group_id
1211 AND a.assignment_type = 'A'
1212 AND a.effective_start_date > P_date_end;
1213
1214 -- Terminate assignments
1215
1216 UPDATE per_all_assignments_f paa
1217 SET paa.last_update_date = trunc(sysdate),
1218 paa.last_updated_by = p_last_updated_by,
1219 paa.last_update_login = p_last_update_login,
1220 paa.EFFECTIVE_END_DATE = P_date_end
1221 where paa.APPLICATION_ID = P_application_id
1222 and paa.PERSON_ID = p_person_id
1223 and paa.business_group_id + 0 = p_business_group_id
1224 and paa.ASSIGNMENT_TYPE = 'A'
1225 and paa.EFFECTIVE_END_DATE =
1226 (select max(pa2.EFFECTIVE_END_DATE)
1227 from PER_ALL_ASSIGNMENTS_F pa2
1228 where pa2.PERSON_ID = p_person_id
1229 and pa2.assignment_id = paa.assignment_id -- 3957964 >>
1230 and pa2.effective_end_date > p_date_end -- <<
1231 and pa2.APPLICATION_ID = P_application_id);
1232
1233 -- call to new proc due to date tracking of assignment budget values. This will terminate the
1234 -- assignment budget values related to the assignment being terminated.
1235 --SASmith 17-APR-1998
1236
1237 term_update_ass_bud_val(p_application_id
1238 ,p_person_id
1239 ,p_business_group_id
1240 ,p_date_end
1241 ,p_last_updated_by
1242 ,p_last_update_login);
1243 --
1244 END term_update_assignments;
1245 --
1246 --
1247 --
1248 PROCEDURE canc_chk_fut_per_changes(p_person_id NUMBER,
1249 p_application_id NUMBER,
1250 p_date_end DATE ) is
1251 --
1252 cursor c1 is
1253 SELECT 1
1254 FROM PER_ALL_PEOPLE_F PAPF
1255 WHERE PAPF.PERSON_ID = P_PERSON_ID
1256 AND PAPF.EFFECTIVE_START_DATE > P_DATE_END + 1 ;
1257 cursor c2 is
1258 SELECT 1
1259 FROM PER_PERSON_TYPE_USAGES_F PTU, PER_PERSON_TYPES PPT
1260 WHERE PTU.PERSON_ID = P_PERSON_ID
1261 AND PTU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
1262 AND PPT.SYSTEM_PERSON_TYPE in ('EMP','CWK')
1263 AND PTU.EFFECTIVE_START_DATE > P_DATE_END + 1 ;
1264 --
1265 l_dummy number ;
1266 BEGIN
1267 --
1268 open c1 ;
1269 fetch c1 into l_dummy ;
1270 if c1%found then
1271 -- ER FPT
1272 if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
1273 open c2;
1274 fetch c2 into l_dummy;
1275 if c2%found then
1276 hr_utility.set_message(801,'HR_6385_APP_TERM_FUT_CHANGES' );
1277 hr_utility.raise_error ;
1278 end if;
1279 close c2;
1280 else
1281 hr_utility.set_message(801,'HR_6385_APP_TERM_FUT_CHANGES' );
1282 hr_utility.raise_error ;
1283 end if;
1284 end if;
1285 close c1 ;
1286 end canc_chk_fut_per_changes ;
1287 -----------------------------------------------------------------------------
1288 -----------------------------------------------------------------------------
1289 -- Name --
1290 -- term_chk_per_assign_changes --
1291 -- Purpose --
1292 -- check that the applicant has no future person record changes after the
1293 -- apparent termination date since this would prohibit a termination. --
1294 -- Arguments --
1295 -- See below. --
1296 -- Notes --
1297 -----------------------------------------------------------------------------
1298 PROCEDURE term_chk_fut_per_changes(p_person_id NUMBER,
1299 p_business_group_id NUMBER,
1300 P_date_end DATE) IS
1301 CURSOR c_per_changes IS
1302 SELECT 1
1303 FROM per_all_people_f papf
1304 WHERE papf.person_id = p_person_id
1305 AND papf.effective_start_date > P_date_end
1306 AND papf.business_group_id + 0 = p_business_group_id;
1307 ------
1308 v_dummy_number NUMBER(1);
1309 ---
1310 BEGIN
1311 OPEN c_per_changes;
1312 FETCH c_per_changes INTO v_dummy_number;
1313 IF c_per_changes%FOUND THEN
1314 CLOSE c_per_changes;
1315 hr_utility.set_message(800,'HR_6382_APP_TERM_FUTURE_PPT');
1316 hr_utility.set_message_token('DATE',P_date_end);
1317 hr_utility.raise_error;
1318 ELSE CLOSE c_per_changes;
1319 END IF;
1320 --
1321 END term_chk_fut_per_changes;
1322 -----------------------------------------------------------------------------
1323 -- Name --
1324 -- term_chk_fut_assign_changes --
1325 -- Purpose --
1326 -- if future assignment changes of any sort exist for the person, then --
1327 -- the user cannot terminate the application. --
1328 -- Arguments --
1329 -- See below. --
1330 -- Notes --
1331 -----------------------------------------------------------------------------
1332 PROCEDURE term_chk_fut_assign_changes(p_person_id NUMBER,
1333 p_business_group_id NUMBER,
1334 P_date_end DATE) IS
1335 CURSOR c_assign_changes IS
1336 SELECT 1
1337 FROM PER_ALL_ASSIGNMENTS_F PAAF
1338 WHERE PAAF.PERSON_ID = p_person_id
1339 AND PAAF.business_group_id + 0 = p_business_group_id
1340 AND PAAF.EFFECTIVE_START_DATE > P_date_end;
1341 ------
1342 v_number NUMBER(1);
1343 ---
1344 BEGIN
1345 OPEN c_assign_changes;
1346 FETCH c_assign_changes INTO v_number;
1347 IF c_assign_changes%FOUND THEN
1348 CLOSE c_assign_changes;
1349 hr_utility.set_message(800,'HR_6583_APP_TERM_FUT_ASS');
1350 hr_utility.set_message_token('DATE',P_date_end);
1351 hr_utility.raise_error;
1352 ELSE CLOSE c_assign_changes;
1353 END IF;
1354 --
1355 END term_chk_fut_assign_changes;
1356
1357 -----------------------------------------------------------------------------
1358 -- Name --
1359 -- maint_security_cancel --
1360 -- Purpose --
1361 -- Stubbed as part of the ex-person security enhancements. --
1362 -- Arguments --
1363 -- See below. --
1364 -- Notes --
1365 -----------------------------------------------------------------------------
1366 PROCEDURE maint_security_cancel(p_person_id NUMBER) IS
1367
1368 --
1369 BEGIN
1370 --
1371 NULL;
1372 --
1373 END maint_security_cancel;
1374 -----------------------------------------------------------------------------
1375 -- Name --
1376 -- maint_security_term --
1377 -- Purpose --
1378 -- Stubbed as part of the ex-person security enhancements. --
1379 -- Arguments --
1380 -- See below. --
1381 -- Notes --
1382 -----------------------------------------------------------------------------
1383 PROCEDURE maint_security_term(p_person_id NUMBER) IS
1384
1385 BEGIN
1386 --
1387 NULL;
1388 --
1389 END maint_security_term;
1390
1391
1392 -----------------------------------------------------------------------------
1393 -- Name --
1394 -- sec_statuses_cancel --
1395 -- Purpose --
1396 -- to nuliify any secondary assignment statuses end dates on the applicant's
1397 -- assignments if they are currently the same as the termination date when
1398 -- the applicant was terminated.
1399 -- Arguments --
1400 -- See below. --
1401 -- Notes --
1402 -----------------------------------------------------------------------------
1403 PROCEDURE sec_statuses_cancel(p_end_date DATE,
1404 p_application_id NUMBER,
1405 p_business_group_id NUMBER,
1406 p_last_updated_by NUMBER,
1407 p_last_update_login NUMBER,
1408 p_person_id NUMBER) IS
1409
1410 CURSOR c_sec_stat_cancel IS
1411 select sa.assignment_id
1412 from per_secondary_ass_statuses sa
1413 where sa.business_group_id + 0 = p_business_group_id
1414 and sa.end_date = p_end_date
1415 and exists
1416 ( SELECT s.assignment_id
1417 FROM PER_SECONDARY_ASS_STATUSES s
1418 where s.business_group_id + 0 = p_business_group_id
1419 and s.end_date = p_end_date
1420 and sa.assignment_id = s.assignment_id
1421 and exists
1422 (select null
1423 from per_assignments_f paf
1424 where paf.person_id = p_person_id
1425 and paf.application_id = p_application_id
1426 and paf.assignment_type = 'A'
1427 and paf.effective_end_date = p_end_date
1428 and paf.assignment_id = s.assignment_id));
1429 --
1430 v_assignment_id NUMBER(15);
1431 --
1432
1433 BEGIN
1434 OPEN c_sec_stat_cancel;
1435 LOOP
1436 FETCH c_sec_stat_cancel into v_assignment_id;
1437 EXIT WHEN c_sec_stat_cancel%NOTFOUND;
1438 UPDATE per_secondary_ass_statuses s
1439 SET s.END_DATE = NULL
1440 , s.LAST_UPDATE_DATE = trunc(SYSDATE)
1441 , s.LAST_UPDATED_BY = p_last_updated_by
1442 , s.LAST_UPDATE_LOGIN = p_last_update_login
1443 WHERE s.assignment_id = v_assignment_id
1444 AND s.business_group_id + 0 = p_business_group_id
1445 AND s.END_DATE = p_end_date;
1446 END LOOP;
1447 CLOSE c_sec_stat_cancel;
1448 --
1449 END sec_statuses_cancel;
1450 -----------------------------------------------------------------------------
1451 -- Name --
1452 -- sec_statuses_term --
1453 -- Purpose --
1454 -- to delete any future sec.statuses when terminating an applicant. Puts an
1455 -- end date as of the applicant's termination date for any secondary
1456 -- applicant assignment statuses that start before the termination date
1457 -- and which don't have end dates before the termination end date. --
1458 -- Arguments --
1459 -- See below. --
1460 -- Notes --
1461 -----------------------------------------------------------------------------
1462 procedure sec_statuses_term(p_end_date date
1463 ,p_application_id number
1464 ,p_business_group_id number
1465 ,p_last_updated_by number
1466 ,p_last_update_login number
1467 ,p_person_id number) is
1468 -- WWbug 633263
1469 -- Modified cursor for performance improvements by removing the full table
1470 -- scan on per_secondary_ass_statuses.
1471 -- This was achieved by removing the sub-query
1472 cursor chk_sec_stat is
1473 select 1
1474 from per_secondary_ass_statuses s
1475 ,per_assignments_f a
1476 where s.business_group_id + 0 = p_business_group_id
1477 and s.start_date is not null
1478 and a.business_group_id + 0 = p_business_group_id
1479 and a.person_id = p_person_id
1480 and s.assignment_id = a.assignment_id
1481 and a.application_id = p_application_id
1482 and a.assignment_type = 'A'
1483 and p_end_date
1484 between a.effective_start_date
1485 and a.effective_end_date;
1486 -- WWbug 633263
1487 -- Modified cursor for performance improvements by removing the full table
1488 -- scan on per_secondary_ass_statuses.
1489 -- This was achieved by removing the sub-query
1490 cursor c_sec_stat is
1491 select sa.assignment_id
1492 from per_secondary_ass_statuses sa
1493 ,per_assignments_f paf
1494 where sa.business_group_id + 0 = p_business_group_id
1495 and sa.start_date <= p_end_date
1496 and (sa.end_date is null
1497 or sa.end_date > p_end_date)
1498 and sa.assignment_id = paf.assignment_id
1499 and paf.person_id = p_person_id
1500 and paf.application_id = p_application_id
1501 and paf.assignment_type = 'A'
1502 and p_end_date
1503 between paf.effective_start_date
1504 and paf.effective_end_date;
1505 --
1506 v_dummy number(1);
1507 --
1508 begin
1509 open chk_sec_stat;
1510 fetch chk_sec_stat into v_dummy;
1511 if chk_sec_stat%found then
1512 close chk_sec_stat;
1513 -- WWbug 633263
1514 -- Modified cursor for performance improvements by removing the full table
1515 -- scan on per_secondary_ass_statuses.
1516 -- This was achieved by replacing the EXISTS sub-query with an IN sub-query
1517 delete from per_secondary_ass_statuses s
1518 where s.business_group_id + 0 = p_business_group_id
1519 and trunc(s.start_date) > p_end_date
1520 and s.assignment_id in
1521 (select a.assignment_id
1522 from per_assignments_f a
1523 where a.business_group_id + 0 = p_business_group_id
1524 and a.person_id = p_person_id
1525 and a.application_id = p_application_id
1526 and a.assignment_type = 'A'
1527 and p_end_date
1528 between a.effective_start_date
1529 and a.effective_end_date);
1530 -- WWbug 633263
1531 -- Cleared up the previous code with a cursor for loop
1532 for csr_rec in c_sec_stat loop
1533 update per_secondary_ass_statuses s
1534 set s.end_date = p_end_date
1535 , s.last_update_date = trunc(sysdate)
1536 , s.last_updated_by = p_last_updated_by
1537 , s.last_update_login = p_last_update_login
1538 where s.assignment_id = csr_rec.assignment_id
1539 and s.business_group_id + 0 = p_business_group_id
1540 and s.start_date <= p_end_date
1541 and (s.end_date is null
1542 or s.end_date > p_end_date);
1543 end loop;
1544 --
1545 else
1546 -- WWbug 633263
1547 -- Closed the cursor which was previously not closed
1548 close chk_sec_stat;
1549 end if;
1550 end sec_statuses_term;
1551 -----------------------------------------------------------------------------
1552 -- Name --
1553 -- del_interviews_term --
1554 -- Purpose --
1555 -- Arguments --
1556 -- See below. --
1557 -- Notes --
1558 -----------------------------------------------------------------------------
1559 PROCEDURE del_interviews_term(P_person_id NUMBER,
1560 P_date_end DATE,
1561 P_Business_group_id NUMBER,
1562 P_application_id NUMBER) IS
1563 CURSOR chk_events IS
1564 SELECT E.EVENT_ID
1565 FROM PER_EVENTS E
1566 , PER_ASSIGNMENTS_F A
1567 WHERE A.PERSON_ID = P_person_id
1568 AND E.business_group_id + 0 = p_business_group_id
1569 AND A.business_group_id + 0 = p_business_group_id
1570 AND A.APPLICATION_ID = P_application_id
1571 AND E.ASSIGNMENT_ID = A.ASSIGNMENT_ID
1572 AND E.DATE_START >= P_date_end
1573 AND E.EVENT_OR_INTERVIEW = 'I';
1574
1575
1576 CURSOR chk_bookings IS
1577 SELECT distinct(1)
1578 FROM PER_BOOKINGS B
1579 , PER_EVENTS E
1580 , PER_ASSIGNMENTS_F A
1581 WHERE A.PERSON_ID = P_person_id
1582 AND A.APPLICATION_ID = P_application_id
1583 AND B.EVENT_ID = E.EVENT_ID
1584 AND E.DATE_START >= P_date_end
1585 AND E.EVENT_OR_INTERVIEW = 'I'
1586 AND E.ASSIGNMENT_ID = A.ASSIGNMENT_ID;
1587 --
1588 -- the person_id on per_bookings is the employee who is doing the
1589 -- interviewing of the applicant and is NOT the applicant.
1590 --
1591
1592 CURSOR c_viewers IS
1593 select B.PERSON_ID,B.BOOKING_ID
1594 from PER_BOOKINGS B
1595 , PER_EVENTS E
1596 , PER_ASSIGNMENTS A
1597 where B.business_group_id + 0 = p_business_group_id
1598 and E.business_group_id + 0 = p_business_group_id
1599 and A.business_group_id + 0 = p_business_group_id
1600 and A.PERSON_ID = p_person_id
1601 and A.APPLICATION_ID = p_application_id
1602 and B.EVENT_ID = E.EVENT_ID
1603 and E.DATE_START >= P_date_end
1604 and E.EVENT_OR_INTERVIEW = 'I'
1605 and E.ASSIGNMENT_ID = A.ASSIGNMENT_ID;
1606
1607 V_dummy_events chk_events%rowtype;
1608 v_dummy_bookings NUMBER(1);
1609 r_interviewers c_viewers%rowtype;
1610 l_event_found BOOLEAN;
1611
1612 BEGIN
1613 OPEN chk_events;
1614 FETCH chk_events into V_dummy_events;
1615 --
1616 l_event_found := chk_events%found;
1617 IF l_event_found THEN
1618 CLOSE chk_events;
1619 OPEN chk_bookings;
1620 FETCH chk_bookings into v_dummy_bookings;
1621 --
1622 IF chk_bookings%found THEN
1623 CLOSE chk_bookings;
1624 OPEN c_viewers;
1625 FETCH c_viewers into r_interviewers;
1626 CLOSE c_viewers;
1627 FOR c_viewers_rec IN c_viewers LOOP
1628 DELETE FROM per_bookings bk
1629 WHERE bk.business_group_id + 0 = p_business_group_id
1630 AND bk.booking_id = c_viewers_rec.BOOKING_ID
1631 AND bk.person_id = c_viewers_rec.PERSON_ID;
1632 END LOOP;
1633
1634 FOR chk_events_rec IN chk_events LOOP
1635 DELETE FROM per_events ev
1636 WHERE ev.event_id = chk_events_rec.event_id
1637 AND ev.business_group_id + 0 = p_business_group_id;
1638 END LOOP;
1639 --
1640 ELSE CLOSE chk_bookings;
1641 FOR chk_events_rec IN chk_events LOOP
1642 DELETE FROM per_events ev
1643 WHERE ev.event_id = chk_events_rec.event_id
1644 AND ev.business_group_id + 0 = p_business_group_id;
1645 END LOOP;
1646
1647 --
1648 END IF;
1649 END IF;
1650 END del_interviews_term;
1651 -----------------------------------------------------------------------------
1652 -- Name --
1653 -- maintain_ppt_cancel --
1654 -- Purpose --
1655 -- When cancelling an already terminated application this procedure ensures
1656 -- that the last record is deleted from the person table i.e the one that
1657 -- has a person_type_id of TERM_APL so that the person reverts back to an
1658 -- APL and secondly it opens out the now new last record by putting an
1659 -- effective_end_date on PER_PEOPLE_F as of the end_of_time.
1660 -- Arguments --
1661 -- See below. --
1662 -- Notes --
1663 -----------------------------------------------------------------------------
1664 PROCEDURE maintain_ppt_cancel(P_person_id NUMBER,
1665 P_Business_group_id NUMBER,
1666 P_date_end DATE,
1667 P_last_updated_by NUMBER,
1668 P_last_update_login NUMBER,
1669 P_end_of_time DATE) IS
1670 --
1671 BEGIN
1672 -- ER FPT
1673 if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'N') then
1674
1675 DELETE FROM per_all_people_f papf
1676 WHERE papf.person_id = P_person_id
1677 AND papf.business_group_id + 0 = P_Business_group_id
1678 AND papf.effective_start_date = P_date_end + 1;
1679 --
1680 UPDATE per_all_people_f papf
1681 SET papf.effective_end_date = P_end_of_time
1682 , papf.last_updated_by = P_last_updated_by
1683 , papf.last_update_date = trunc(sysdate)
1684 , papf.last_update_login = P_last_update_login
1685 WHERE papf.person_id = P_person_id
1686 AND papf.BUSINESS_GROUP_ID + 0 = P_Business_group_id
1687 AND papf.effective_end_date = P_date_end;
1688 end if;
1689 --
1690 END maintain_ppt_cancel;
1691 ----------------------------------------------------------------------------
1692 -- Name --
1693 -- chk_not_already_termed --
1694 -- Purpose --
1695 -- To ensure that the user cannot terminate an application which has already
1696 -- been terminated.
1697 -- Arguments --
1698 -- See below. --
1699 -- Notes --
1700 -----------------------------------------------------------------------------
1701 PROCEDURE chk_not_already_termed(P_Business_group_id NUMBER,
1702 P_person_id NUMBER,
1703 P_application_id NUMBER,
1704 P_date_end DATE) IS
1705 --
1706
1707 CURSOR c_chk_already_term IS
1708 SELECT 1
1709 FROM PER_APPLICATIONS PA
1710 WHERE PA.business_group_id + 0 = P_Business_group_id
1711 AND PA.PERSON_ID = P_person_id
1712 AND PA.APPLICATION_ID = P_application_id
1713 AND PA.DATE_END IS NOT NULL;
1714 -- AND PA.DATE_END = P_date_end; /* Fix for bug 8433186 */
1715
1716 V_dummy_1 NUMBER(1);
1717 --
1718 BEGIN
1719
1720 OPEN c_chk_already_term;
1721 FETCH c_chk_already_term into V_dummy_1;
1722 IF c_chk_already_term%found THEN
1723 CLOSE c_chk_already_term;
1724 hr_utility.set_message(800,'HR_7105_APPL_ALREADY_TERMED');
1725 hr_utility.raise_error;
1726 ELSE
1727 CLOSE c_chk_already_term;
1728 END IF;
1729 END chk_not_already_termed;
1730 -----------------------------------------------------------------------------
1731 -- Name --
1732 -- maintain_ppt_term --
1733 -- Purpose --
1734 -- This procedure maintains the person's record when going from an --
1735 -- applicant to an ex-applicant. --
1736 -- In particular this maintiains the person_type_id on per_all_people_f --
1737 -- by closing down the record in per_all_people_f as of the end date of --
1738 -- the person's application and inserting a row with the new person_type_id
1739 -- on the next day. --
1740 -- Arguments --
1741 -- See below. --
1742 -- Notes --
1743 -- --
1744 -------------------------------------------------------------------------------
1745 PROCEDURE maintain_ppt_term(P_Business_group_id NUMBER,
1746 P_person_id NUMBER,
1747 P_date_end DATE,
1748 P_end_of_time DATE,
1749 P_last_updated_by NUMBER,
1750 P_last_update_login NUMBER) IS
1751 --
1752 BEGIN
1753 UPDATE per_all_people_f papf
1754 set PAPF.effective_end_date = P_date_end
1755 , PAPF.last_updated_by = P_last_updated_by
1756 , PAPF.last_update_date = trunc(sysdate)
1757 , PAPF.last_update_login = P_last_update_login
1758 where PAPF.person_id = P_person_id
1759 and P_date_end BETWEEN
1760 PAPF.effective_start_date AND PAPF.effective_end_date
1761 and PAPF.business_group_id + 0 = P_Business_group_id;
1762 --
1763
1764 INSERT INTO per_all_people_f
1765 (PERSON_ID ,EFFECTIVE_START_DATE ,EFFECTIVE_END_DATE
1766 ,BUSINESS_GROUP_ID ,PERSON_TYPE_ID ,LAST_NAME
1767 ,START_DATE ,APPLICANT_NUMBER
1768 ,COMMENT_ID
1769 ,CURRENT_APPLICANT_FLAG
1770 ,CURRENT_EMP_OR_APL_FLAG
1771 ,CURRENT_EMPLOYEE_FLAG
1772 ,CURRENT_NPW_FLAG
1773 ,DATE_EMPLOYEE_DATA_VERIFIED
1774 ,DATE_OF_BIRTH ,EMAIL_ADDRESS
1775 ,EMPLOYEE_NUMBER ,EXPENSE_CHECK_SEND_TO_ADDRESS
1776 ,FIRST_NAME ,FULL_NAME
1777 ,KNOWN_AS ,MARITAL_STATUS ,MIDDLE_NAMES
1778 ,NATIONALITY ,NATIONAL_IDENTIFIER ,PREVIOUS_LAST_NAME
1779 ,REGISTERED_DISABLED_FLAG ,SEX ,TITLE
1780 ,VENDOR_ID ,WORK_TELEPHONE ,REQUEST_ID
1781 ,PROGRAM_APPLICATION_ID ,PROGRAM_ID
1782 ,PROGRAM_UPDATE_DATE ,ATTRIBUTE_CATEGORY
1783 ,ATTRIBUTE1 ,ATTRIBUTE2 ,ATTRIBUTE3 ,ATTRIBUTE4 ,ATTRIBUTE5
1784 ,ATTRIBUTE6 ,ATTRIBUTE7 ,ATTRIBUTE8 ,ATTRIBUTE9 ,ATTRIBUTE10
1785 ,ATTRIBUTE11 ,ATTRIBUTE12 ,ATTRIBUTE13 ,ATTRIBUTE14
1786 ,ATTRIBUTE15 ,ATTRIBUTE16 ,ATTRIBUTE17 ,ATTRIBUTE18 ,ATTRIBUTE19
1787 ,ATTRIBUTE20 , ATTRIBUTE21 ,ATTRIBUTE22 ,ATTRIBUTE23 ,ATTRIBUTE24
1788 ,ATTRIBUTE25 ,ATTRIBUTE26 ,ATTRIBUTE27 ,ATTRIBUTE28 ,ATTRIBUTE29
1789 ,ATTRIBUTE30 , LAST_UPDATE_DATE ,LAST_UPDATED_BY
1790 ,LAST_UPDATE_LOGIN ,CREATED_BY ,CREATION_DATE
1791 ,PER_INFORMATION_CATEGORY
1792 ,PER_INFORMATION1
1793 ,PER_INFORMATION2
1794 ,PER_INFORMATION3
1795 ,PER_INFORMATION4
1796 ,PER_INFORMATION5
1797 ,PER_INFORMATION6
1798 ,PER_INFORMATION7
1799 ,PER_INFORMATION8
1800 ,PER_INFORMATION9
1801 ,PER_INFORMATION10
1802 ,PER_INFORMATION11
1803 ,PER_INFORMATION12
1804 ,PER_INFORMATION13
1805 ,PER_INFORMATION14
1806 ,PER_INFORMATION15
1807 ,PER_INFORMATION16
1808 ,PER_INFORMATION17
1809 ,PER_INFORMATION18
1810 ,PER_INFORMATION19
1811 ,PER_INFORMATION20
1812 ,PER_INFORMATION21
1813 ,PER_INFORMATION22
1814 ,PER_INFORMATION23
1815 ,PER_INFORMATION24
1816 ,PER_INFORMATION25
1817 ,PER_INFORMATION26
1818 ,PER_INFORMATION27
1819 ,PER_INFORMATION28
1820 ,PER_INFORMATION29
1821 ,PER_INFORMATION30
1822 ,BACKGROUND_CHECK_STATUS
1823 ,BACKGROUND_DATE_CHECK
1824 ,BLOOD_TYPE
1825 ,CORRESPONDENCE_LANGUAGE
1826 ,FAST_PATH_EMPLOYEE
1827 ,FTE_CAPACITY
1828 ,HOLD_APPLICANT_DATE_UNTIL
1829 ,HONORS
1830 ,INTERNAL_LOCATION
1831 ,LAST_MEDICAL_TEST_BY
1832 ,LAST_MEDICAL_TEST_DATE
1833 ,MAILSTOP
1834 ,OFFICE_NUMBER
1835 ,ON_MILITARY_SERVICE
1836 ,ORDER_NAME
1837 ,PRE_NAME_ADJUNCT
1838 ,PROJECTED_START_DATE
1839 ,REHIRE_AUTHORIZOR
1840 ,REHIRE_REASON
1841 ,REHIRE_RECOMMENDATION
1842 ,RESUME_EXISTS
1843 ,RESUME_LAST_UPDATED
1844 ,SECOND_PASSPORT_EXISTS
1845 ,STUDENT_STATUS
1846 ,SUFFIX
1847 ,WORK_SCHEDULE
1848 ,town_of_birth
1849 ,region_of_birth
1850 ,country_of_birth
1851 ,global_person_id
1852 ,party_id
1853 ,original_date_of_hire
1854
1855 --Bug2974671 starts here.
1856
1857 ,BENEFIT_GROUP_ID
1858 ,COORD_BEN_MED_PLN_NO
1859 ,COORD_BEN_NO_CVG_FLAG
1860 ,DPDNT_ADOPTION_DATE
1861 ,DPDNT_VLNTRY_SVCE_FLAG
1862 ,USES_TOBACCO_FLAG
1863
1864 -- Bug2974671 ends here.
1868 ,LOCAL_NAME
1865 ,NPW_NUMBER -- Added for Fix for #3184546
1866 -- Start changes for bug 10245640
1867 ,RECEIPT_OF_DEATH_CERT_DATE
1869 ,GLOBAL_NAME
1870 ,DATE_OF_DEATH
1871 -- End changes for bug 10245640
1872 )
1873 select PAPF.PERSON_ID
1874 ,PAPF.EFFECTIVE_END_DATE+1
1875 ,P_end_of_time
1876 ,PAPF.BUSINESS_GROUP_ID ,PPT.PERSON_TYPE_ID
1877 ,PAPF.LAST_NAME ,PAPF.START_DATE
1878 ,PAPF.APPLICANT_NUMBER ,PAPF.COMMENT_ID
1879 ,null
1880 ,PAPF.CURRENT_EMPLOYEE_FLAG
1881 ,PAPF.CURRENT_EMPLOYEE_FLAG
1882 ,PAPF.CURRENT_NPW_FLAG
1883 ,PAPF.DATE_EMPLOYEE_DATA_VERIFIED
1884 ,PAPF.DATE_OF_BIRTH
1885 ,PAPF.EMAIL_ADDRESS
1886 ,PAPF.EMPLOYEE_NUMBER
1887 ,PAPF.EXPENSE_CHECK_SEND_TO_ADDRESS
1888 ,PAPF.FIRST_NAME ,PAPF.FULL_NAME
1889 ,PAPF.KNOWN_AS ,PAPF.MARITAL_STATUS
1890 ,PAPF.MIDDLE_NAMES ,PAPF.NATIONALITY
1891 ,PAPF.NATIONAL_IDENTIFIER
1892 ,PAPF.PREVIOUS_LAST_NAME
1893 ,PAPF.REGISTERED_DISABLED_FLAG
1894 ,PAPF.SEX ,PAPF.TITLE ,PAPF.VENDOR_ID
1895 ,PAPF.WORK_TELEPHONE ,PAPF.REQUEST_ID
1896 ,PAPF.PROGRAM_APPLICATION_ID
1897 ,PAPF.PROGRAM_ID
1898 ,PAPF.PROGRAM_UPDATE_DATE
1899 ,PAPF.ATTRIBUTE_CATEGORY
1900 ,PAPF.ATTRIBUTE1 ,PAPF.ATTRIBUTE2
1901 ,PAPF.ATTRIBUTE3 ,PAPF.ATTRIBUTE4
1902 ,PAPF.ATTRIBUTE5 ,PAPF.ATTRIBUTE6
1903 ,PAPF.ATTRIBUTE7 ,PAPF.ATTRIBUTE8
1904 ,PAPF.ATTRIBUTE9 ,PAPF.ATTRIBUTE10
1905 ,PAPF.ATTRIBUTE11 ,PAPF.ATTRIBUTE12
1906 ,PAPF.ATTRIBUTE13 ,PAPF.ATTRIBUTE14
1907 ,PAPF.ATTRIBUTE15 ,PAPF.ATTRIBUTE16
1908 ,PAPF.ATTRIBUTE17 ,PAPF.ATTRIBUTE18
1909 ,PAPF.ATTRIBUTE19 ,PAPF.ATTRIBUTE20
1910 ,PAPF.ATTRIBUTE21 ,PAPF.ATTRIBUTE22
1911 ,PAPF.ATTRIBUTE23 ,PAPF.ATTRIBUTE24
1912 ,PAPF.ATTRIBUTE25 ,PAPF.ATTRIBUTE26
1913 ,PAPF.ATTRIBUTE27 ,PAPF.ATTRIBUTE28
1914 ,PAPF.ATTRIBUTE29 ,PAPF.ATTRIBUTE30
1915 ,PAPF.LAST_UPDATE_DATE ,PAPF.LAST_UPDATED_BY
1916 ,PAPF.LAST_UPDATE_LOGIN ,PAPF.CREATED_BY
1917 ,PAPF.CREATION_DATE
1918 ,PAPF.PER_INFORMATION_CATEGORY
1919 ,PAPF.PER_INFORMATION1
1920 ,PAPF.PER_INFORMATION2
1921 ,PAPF.PER_INFORMATION3
1922 ,PAPF.PER_INFORMATION4
1923 ,PAPF.PER_INFORMATION5
1924 ,PAPF.PER_INFORMATION6
1925 ,PAPF.PER_INFORMATION7
1926 ,PAPF.PER_INFORMATION8
1927 ,PAPF.PER_INFORMATION9
1928 ,PAPF.PER_INFORMATION10
1929 ,PAPF.PER_INFORMATION11
1930 ,PAPF.PER_INFORMATION12
1931 ,PAPF.PER_INFORMATION13
1932 ,PAPF.PER_INFORMATION14
1933 ,PAPF.PER_INFORMATION15
1934 ,PAPF.PER_INFORMATION16
1935 ,PAPF.PER_INFORMATION17
1936 ,PAPF.PER_INFORMATION18
1937 ,PAPF.PER_INFORMATION19
1938 ,PAPF.PER_INFORMATION20
1939 ,PAPF.PER_INFORMATION21
1940 ,PAPF.PER_INFORMATION22
1941 ,PAPF.PER_INFORMATION23
1942 ,PAPF.PER_INFORMATION24
1943 ,PAPF.PER_INFORMATION25
1944 ,PAPF.PER_INFORMATION26
1945 ,PAPF.PER_INFORMATION27
1946 ,PAPF.PER_INFORMATION28
1947 ,PAPF.PER_INFORMATION29
1948 ,PAPF.PER_INFORMATION30
1949 ,PAPF.BACKGROUND_CHECK_STATUS
1950 ,PAPF.BACKGROUND_DATE_CHECK
1951 ,PAPF.BLOOD_TYPE
1952 ,PAPF.CORRESPONDENCE_LANGUAGE
1953 ,PAPF.FAST_PATH_EMPLOYEE
1954 ,PAPF.FTE_CAPACITY
1955 ,PAPF.HOLD_APPLICANT_DATE_UNTIL
1956 ,PAPF.HONORS
1957 ,PAPF.INTERNAL_LOCATION
1958 ,PAPF.LAST_MEDICAL_TEST_BY
1959 ,PAPF.LAST_MEDICAL_TEST_DATE
1960 ,PAPF.MAILSTOP
1961 ,PAPF.OFFICE_NUMBER
1962 ,PAPF.ON_MILITARY_SERVICE
1963 ,PAPF.ORDER_NAME
1964 ,PAPF.PRE_NAME_ADJUNCT
1965 ,PAPF.PROJECTED_START_DATE
1966 ,PAPF.REHIRE_AUTHORIZOR
1967 ,PAPF.REHIRE_REASON
1968 ,PAPF.REHIRE_RECOMMENDATION
1969 ,PAPF.RESUME_EXISTS
1970 ,PAPF.RESUME_LAST_UPDATED
1971 ,PAPF.SECOND_PASSPORT_EXISTS
1972 ,PAPF.STUDENT_STATUS
1973 ,PAPF.SUFFIX
1974 ,PAPF.WORK_SCHEDULE
1975 ,PAPF.town_of_birth
1976 ,PAPF.region_of_birth
1977 ,PAPF.country_of_birth
1978 ,PAPF.global_person_id
1979 ,PAPF.party_id
1980 ,PAPF.original_date_of_hire
1981
1982 -- Bug2974671 starts here.
1983
1984 ,PAPF.BENEFIT_GROUP_ID
1985 ,PAPF.COORD_BEN_MED_PLN_NO
1986 ,PAPF.COORD_BEN_NO_CVG_FLAG
1987 ,PAPF.DPDNT_ADOPTION_DATE
1988 ,PAPF.DPDNT_VLNTRY_SVCE_FLAG
1989 ,PAPF.USES_TOBACCO_FLAG
1990
1991 --Bug2974671 ends here.
1992 ,PAPF.NPW_NUMBER -- Added for Fix for #3184546
1993 -- Start changes for bug 10245640
1994 ,PAPF.RECEIPT_OF_DEATH_CERT_DATE
1995 ,PAPF.LOCAL_NAME
1996 ,PAPF.GLOBAL_NAME
1997 ,PAPF.DATE_OF_DEATH
1998 -- End changes for bug 10245640
1999 FROM per_all_people_f PAPF,
2000 PER_PERSON_TYPES PPT,
2001 per_person_types PPT2
2002 WHERE PAPF.person_id = P_person_id
2003 AND PAPF.effective_end_date = P_date_end
2004 AND PPT.business_group_id = P_business_group_id
2005 and PAPF.business_group_id + 0 = P_Business_group_id
2006 AND PPT.default_flag = 'Y'
2007 AND PPT2.person_type_id = PAPF.person_type_id
2008 AND PPT.system_person_type =
2009 decode(PPT2.system_person_type,'APL', 'EX_APL'
2010 ,'APL_EX_APL', 'EX_APL'
2011 ,'EMP_APL', 'EMP'
2012 ,'EX_EMP', 'EX_APL'
2013 ,'EX_EMP_APL', 'EX_EMP' -- Added for fix of #3311891
2014 ,'EX_APL');
2015
2016 --
2017 END maintain_ppt_term;
2018 --
2019 --
2020 -- 3652025:
2021 -- -------------------------------------------------------------------------- +
2022 -- Name: cancel_ptu_updates
2023 -- Description: Performs PTU updates whenever there is a reverse termination.
2024 --
2025 -------------------------------------------------------------------------------
2026 PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
2027 p_Application_Id IN OUT NOCOPY NUMBER,
2028 p_Business_Group_Id NUMBER,
2029 p_Person_Id NUMBER,
2030 p_Date_Received DATE,
2031 p_Comments VARCHAR2,
2032 p_Current_Employer VARCHAR2,
2033 p_Date_End DATE,
2034 p_Projected_Hire_Date DATE,
2035 p_Successful_Flag VARCHAR2,
2036 p_Termination_Reason VARCHAR2,
2037 p_Appl_Attribute_Category VARCHAR2,
2038 p_Appl_Attribute1 VARCHAR2,
2039 p_Appl_Attribute2 VARCHAR2,
2040 p_Appl_Attribute3 VARCHAR2,
2041 p_Appl_Attribute4 VARCHAR2,
2042 p_Appl_Attribute5 VARCHAR2,
2043 p_Appl_Attribute6 VARCHAR2,
2044 p_Appl_Attribute7 VARCHAR2,
2045 p_Appl_Attribute8 VARCHAR2,
2046 p_Appl_Attribute9 VARCHAR2,
2047 p_Appl_Attribute10 VARCHAR2,
2048 p_Appl_Attribute11 VARCHAR2,
2049 p_Appl_Attribute12 VARCHAR2,
2050 p_Appl_Attribute13 VARCHAR2,
2051 p_Appl_Attribute14 VARCHAR2,
2052 p_Appl_Attribute15 VARCHAR2,
2053 p_Appl_Attribute16 VARCHAR2,
2054 p_Appl_Attribute17 VARCHAR2,
2055 p_Appl_Attribute18 VARCHAR2,
2056 p_Appl_Attribute19 VARCHAR2,
2057 p_Appl_Attribute20 VARCHAR2,
2058 p_Last_Update_Date DATE,
2059 p_Last_Updated_By NUMBER,
2060 p_Last_Update_Login NUMBER,
2061 p_Created_By NUMBER,
2062 p_Creation_Date DATE
2063 ) IS
2064 CURSOR C IS SELECT rowid FROM PER_APPLICATIONS
2065 WHERE application_id = p_Application_Id;
2066 CURSOR C2 IS SELECT per_applications_s.nextval FROM sys.dual;
2067 BEGIN
2068 if (p_Application_Id is NULL) then
2069 OPEN C2;
2070 FETCH C2 INTO p_Application_Id;
2071 CLOSE C2;
2072 end if;
2073 INSERT INTO PER_APPLICATIONS(
2074 application_id,
2075 business_group_id,
2076 person_id,
2077 date_received,
2078 comments,
2079 current_employer,
2080 date_end,
2081 projected_hire_date,
2082 successful_flag,
2083 termination_reason,
2084 appl_attribute_category,
2085 appl_attribute1,
2086 appl_attribute2,
2087 appl_attribute3,
2088 appl_attribute4,
2089 appl_attribute5,
2090 appl_attribute6,
2091 appl_attribute7,
2092 appl_attribute8,
2093 appl_attribute9,
2094 appl_attribute10,
2095 appl_attribute11,
2096 appl_attribute12,
2097 appl_attribute13,
2098 appl_attribute14,
2099 appl_attribute15,
2100 appl_attribute16,
2101 appl_attribute17,
2102 appl_attribute18,
2103 appl_attribute19,
2104 appl_attribute20,
2105 last_update_date,
2106 last_updated_by,
2107 last_update_login,
2108 created_by,
2109 creation_date
2110 ) VALUES (
2111 p_Application_Id,
2112 p_Business_Group_Id,
2113 p_Person_Id,
2114 p_Date_Received,
2115 p_Comments,
2116 p_Current_Employer,
2117 p_Date_End,
2118 p_Projected_Hire_Date,
2119 p_Successful_Flag,
2120 p_Termination_Reason,
2121 p_Appl_Attribute_Category,
2122 p_Appl_Attribute1,
2123 p_Appl_Attribute2,
2124 p_Appl_Attribute3,
2125 p_Appl_Attribute4,
2126 p_Appl_Attribute5,
2127 p_Appl_Attribute6,
2128 p_Appl_Attribute7,
2129 p_Appl_Attribute8,
2130 p_Appl_Attribute9,
2131 p_Appl_Attribute10,
2132 p_Appl_Attribute11,
2133 p_Appl_Attribute12,
2134 p_Appl_Attribute13,
2135 p_Appl_Attribute14,
2136 p_Appl_Attribute15,
2137 p_Appl_Attribute16,
2138 p_Appl_Attribute17,
2139 p_Appl_Attribute18,
2140 p_Appl_Attribute19,
2141 p_Appl_Attribute20,
2142 p_Last_Update_Date,
2143 p_Last_Updated_By,
2144 p_Last_Update_Login,
2145 p_Created_By,
2146 p_Creation_Date
2147 );
2148
2149 OPEN C;
2150 FETCH C INTO p_Rowid;
2151 if (C%NOTFOUND) then
2152 CLOSE C;
2153 RAISE NO_DATA_FOUND;
2154 end if;
2155 CLOSE C;
2156 END Insert_Row;
2157 PROCEDURE Lock_Row(p_Rowid VARCHAR2,
2158 p_Application_Id NUMBER,
2159 p_Business_Group_Id NUMBER,
2160 p_Person_Id NUMBER,
2161 p_Date_Received DATE,
2162 p_Comments VARCHAR2,
2163 p_Current_Employer VARCHAR2,
2164 p_Date_End DATE,
2165 p_Projected_Hire_Date DATE,
2166 p_Successful_Flag VARCHAR2,
2167 p_Termination_Reason VARCHAR2,
2168 p_Appl_Attribute_Category VARCHAR2,
2169 p_Appl_Attribute1 VARCHAR2,
2170 p_Appl_Attribute2 VARCHAR2,
2171 p_Appl_Attribute3 VARCHAR2,
2172 p_Appl_Attribute4 VARCHAR2,
2173 p_Appl_Attribute5 VARCHAR2,
2174 p_Appl_Attribute6 VARCHAR2,
2178 p_Appl_Attribute10 VARCHAR2,
2175 p_Appl_Attribute7 VARCHAR2,
2176 p_Appl_Attribute8 VARCHAR2,
2177 p_Appl_Attribute9 VARCHAR2,
2179 p_Appl_Attribute11 VARCHAR2,
2180 p_Appl_Attribute12 VARCHAR2,
2181 p_Appl_Attribute13 VARCHAR2,
2182 p_Appl_Attribute14 VARCHAR2,
2183 p_Appl_Attribute15 VARCHAR2,
2184 p_Appl_Attribute16 VARCHAR2,
2185 p_Appl_Attribute17 VARCHAR2,
2186 p_Appl_Attribute18 VARCHAR2,
2187 p_Appl_Attribute19 VARCHAR2,
2188 p_Appl_Attribute20 VARCHAR2
2189 ) IS
2190 CURSOR C IS
2191 SELECT *
2192 FROM PER_APPLICATIONS
2193 WHERE rowid = p_Rowid
2194 FOR UPDATE of Application_Id NOWAIT;
2195 Recinfo C%ROWTYPE;
2196 BEGIN
2197 OPEN C;
2198 FETCH C INTO Recinfo;
2199 if (C%NOTFOUND) then
2200 CLOSE C;
2201 RAISE NO_DATA_FOUND;
2202 end if;
2203 CLOSE C;
2204 --
2205 --
2206 Recinfo.appl_attribute4 := rtrim(Recinfo.appl_attribute4);
2207 Recinfo.appl_attribute5 := rtrim(Recinfo.appl_attribute5);
2208 Recinfo.appl_attribute6 := rtrim(Recinfo.appl_attribute6);
2209 Recinfo.appl_attribute7 := rtrim(Recinfo.appl_attribute7);
2210 Recinfo.appl_attribute8 := rtrim(Recinfo.appl_attribute8);
2211 Recinfo.appl_attribute9 := rtrim(Recinfo.appl_attribute9);
2212 Recinfo.appl_attribute10 := rtrim(Recinfo.appl_attribute10);
2213 Recinfo.appl_attribute11 := rtrim(Recinfo.appl_attribute11);
2214 Recinfo.appl_attribute12 := rtrim(Recinfo.appl_attribute12);
2215 Recinfo.appl_attribute13 := rtrim(Recinfo.appl_attribute13);
2216 Recinfo.appl_attribute14 := rtrim(Recinfo.appl_attribute14);
2217 Recinfo.appl_attribute15 := rtrim(Recinfo.appl_attribute15);
2218 Recinfo.appl_attribute16 := rtrim(Recinfo.appl_attribute16);
2219 Recinfo.appl_attribute17 := rtrim(Recinfo.appl_attribute17);
2220 Recinfo.appl_attribute18 := rtrim(Recinfo.appl_attribute18);
2221 Recinfo.appl_attribute19 := rtrim(Recinfo.appl_attribute19);
2222 Recinfo.appl_attribute20 := rtrim(Recinfo.appl_attribute20);
2223 Recinfo.comments := rtrim(Recinfo.comments);
2224 Recinfo.current_employer := rtrim(Recinfo.current_employer);
2225 Recinfo.successful_flag := rtrim(Recinfo.successful_flag);
2226 Recinfo.termination_reason := rtrim(Recinfo.termination_reason);
2227 Recinfo.appl_attribute_category := rtrim(Recinfo.appl_attribute_category);
2228 Recinfo.appl_attribute1 := rtrim(Recinfo.appl_attribute1);
2229 Recinfo.appl_attribute3 := rtrim(Recinfo.appl_attribute3);
2230 --
2231 --
2232 if (
2233 ( (Recinfo.application_id = p_Application_Id)
2234 OR ( (Recinfo.application_id IS NULL)
2235 AND (p_Application_Id IS NULL)))
2236 AND ( (Recinfo.business_group_id = p_Business_Group_Id)
2237 OR ( (Recinfo.business_group_id IS NULL)
2238 AND (p_Business_Group_Id IS NULL)))
2239 AND ( (Recinfo.person_id = p_Person_Id)
2240 OR ( (Recinfo.person_id IS NULL)
2241 AND (p_Person_Id IS NULL)))
2242 AND ( (Recinfo.date_received = p_Date_Received)
2243 OR ( (Recinfo.date_received IS NULL)
2244 AND (p_Date_Received IS NULL)))
2245 AND ( (Recinfo.comments = p_Comments)
2246 OR ( (Recinfo.comments IS NULL)
2247 AND (p_Comments IS NULL)))
2248 AND ( (Recinfo.current_employer = p_Current_Employer)
2249 OR ( (Recinfo.current_employer IS NULL)
2250 AND (p_Current_Employer IS NULL)))
2251 AND ( (Recinfo.date_end = p_Date_End)
2252 OR ( (Recinfo.date_end IS NULL)
2253 AND (p_Date_End IS NULL)))
2254 AND ( (Recinfo.projected_hire_date = p_Projected_Hire_Date)
2255 OR ( (Recinfo.projected_hire_date IS NULL)
2256 AND (p_Projected_Hire_Date IS NULL)))
2257 AND ( (Recinfo.successful_flag = p_Successful_Flag)
2258 OR ( (Recinfo.successful_flag IS NULL)
2259 AND (p_Successful_Flag IS NULL)))
2260 AND ( (Recinfo.termination_reason = p_Termination_Reason)
2261 OR ( (Recinfo.termination_reason IS NULL)
2262 AND (p_Termination_Reason IS NULL)))
2263 AND ( (Recinfo.appl_attribute_category = p_Appl_Attribute_Category)
2264 OR ( (Recinfo.appl_attribute_category IS NULL)
2265 AND (p_Appl_Attribute_Category IS NULL)))
2266 AND ( (Recinfo.appl_attribute1 = p_Appl_Attribute1)
2267 OR ( (Recinfo.appl_attribute1 IS NULL)
2268 AND (p_Appl_Attribute1 IS NULL)))
2269 AND ( (Recinfo.appl_attribute2 = p_Appl_Attribute2)
2270 OR ( (Recinfo.appl_attribute2 IS NULL)
2271 AND (p_Appl_Attribute2 IS NULL)))
2272 AND ( (Recinfo.appl_attribute3 = p_Appl_Attribute3)
2273 OR ( (Recinfo.appl_attribute3 IS NULL)
2274 AND (p_Appl_Attribute3 IS NULL)))
2275 AND ( (Recinfo.appl_attribute4 = p_Appl_Attribute4)
2276 OR ( (Recinfo.appl_attribute4 IS NULL)
2277 AND (p_Appl_Attribute4 IS NULL)))
2278 AND ( (Recinfo.appl_attribute5 = p_Appl_Attribute5)
2279 OR ( (Recinfo.appl_attribute5 IS NULL)
2280 AND (p_Appl_Attribute5 IS NULL)))
2281 AND ( (Recinfo.appl_attribute6 = p_Appl_Attribute6)
2282 OR ( (Recinfo.appl_attribute6 IS NULL)
2283 AND (p_Appl_Attribute6 IS NULL)))
2284 AND ( (Recinfo.appl_attribute7 = p_Appl_Attribute7)
2285 OR ( (Recinfo.appl_attribute7 IS NULL)
2286 AND (p_Appl_Attribute7 IS NULL)))
2287 AND ( (Recinfo.appl_attribute8 = p_Appl_Attribute8)
2288 OR ( (Recinfo.appl_attribute8 IS NULL)
2289 AND (p_Appl_Attribute8 IS NULL)))
2290 AND ( (Recinfo.appl_attribute9 = p_Appl_Attribute9)
2291 OR ( (Recinfo.appl_attribute9 IS NULL)
2292 AND (p_Appl_Attribute9 IS NULL)))
2293 AND ( (Recinfo.appl_attribute10 = p_Appl_Attribute10)
2294 OR ( (Recinfo.appl_attribute10 IS NULL)
2295 AND (p_Appl_Attribute10 IS NULL)))
2296 AND ( (Recinfo.appl_attribute11 = p_Appl_Attribute11)
2297 OR ( (Recinfo.appl_attribute11 IS NULL)
2298 AND (p_Appl_Attribute11 IS NULL)))
2299 AND ( (Recinfo.appl_attribute12 = p_Appl_Attribute12)
2300 OR ( (Recinfo.appl_attribute12 IS NULL)
2301 AND (p_Appl_Attribute12 IS NULL)))
2302 AND ( (Recinfo.appl_attribute13 = p_Appl_Attribute13)
2303 OR ( (Recinfo.appl_attribute13 IS NULL)
2304 AND (p_Appl_Attribute13 IS NULL)))
2305 AND ( (Recinfo.appl_attribute14 = p_Appl_Attribute14)
2306 OR ( (Recinfo.appl_attribute14 IS NULL)
2307 AND (p_Appl_Attribute14 IS NULL)))
2308 AND ( (Recinfo.appl_attribute15 = p_Appl_Attribute15)
2309 OR ( (Recinfo.appl_attribute15 IS NULL)
2310 AND (p_Appl_Attribute15 IS NULL)))
2311 AND ( (Recinfo.appl_attribute16 = p_Appl_Attribute16)
2312 OR ( (Recinfo.appl_attribute16 IS NULL)
2313 AND (p_Appl_Attribute16 IS NULL)))
2314 AND ( (Recinfo.appl_attribute17 = p_Appl_Attribute17)
2315 OR ( (Recinfo.appl_attribute17 IS NULL)
2316 AND (p_Appl_Attribute17 IS NULL)))
2317 AND ( (Recinfo.appl_attribute18 = p_Appl_Attribute18)
2318 OR ( (Recinfo.appl_attribute18 IS NULL)
2319 AND (p_Appl_Attribute18 IS NULL)))
2320 AND ( (Recinfo.appl_attribute19 = p_Appl_Attribute19)
2321 OR ( (Recinfo.appl_attribute19 IS NULL)
2322 AND (p_Appl_Attribute19 IS NULL)))
2323 AND ( (Recinfo.appl_attribute20 = p_Appl_Attribute20)
2324 OR ( (Recinfo.appl_attribute20 IS NULL)
2325 AND (p_Appl_Attribute20 IS NULL)))
2326 ) then
2327 return;
2328 else
2329 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2330 APP_EXCEPTION.RAISE_EXCEPTION;
2331 end if;
2332 END Lock_Row;
2333
2334 PROCEDURE Update_Row(p_Rowid VARCHAR2,
2335 p_Application_Id NUMBER,
2336 p_Business_Group_Id NUMBER,
2337 p_Person_Id NUMBER,
2338 p_Person_Type_Id NUMBER,
2339 p_Date_Received DATE,
2343 p_Projected_Hire_Date DATE,
2340 p_Comments VARCHAR2,
2341 p_Current_Employer VARCHAR2,
2342 p_Date_End DATE,
2344 p_Successful_Flag VARCHAR2,
2345 p_Termination_Reason VARCHAR2,
2346 p_Cancellation_Flag VARCHAR2, -- parameter added for Bug 3053711
2347 p_Appl_Attribute_Category VARCHAR2,
2348 p_Appl_Attribute1 VARCHAR2,
2349 p_Appl_Attribute2 VARCHAR2,
2350 p_Appl_Attribute3 VARCHAR2,
2351 p_Appl_Attribute4 VARCHAR2,
2352 p_Appl_Attribute5 VARCHAR2,
2353 p_Appl_Attribute6 VARCHAR2,
2354 p_Appl_Attribute7 VARCHAR2,
2355 p_Appl_Attribute8 VARCHAR2,
2356 p_Appl_Attribute9 VARCHAR2,
2357 p_Appl_Attribute10 VARCHAR2,
2358 p_Appl_Attribute11 VARCHAR2,
2359 p_Appl_Attribute12 VARCHAR2,
2360 p_Appl_Attribute13 VARCHAR2,
2361 p_Appl_Attribute14 VARCHAR2,
2362 p_Appl_Attribute15 VARCHAR2,
2363 p_Appl_Attribute16 VARCHAR2,
2364 p_Appl_Attribute17 VARCHAR2,
2365 p_Appl_Attribute18 VARCHAR2,
2366 p_Appl_Attribute19 VARCHAR2,
2367 p_Appl_Attribute20 VARCHAR2
2368 ) IS
2369
2370 --changed for 2506446 from the old select
2371 cursor csr_ptu_row is
2372 select ptu.effective_start_date
2373 from per_person_type_usages_f ptu
2374 ,per_person_types ppt
2375 where ptu.person_id = p_person_id
2376 and ptu.effective_start_date > p_date_received
2377 and ptu.person_type_id = ppt.person_type_id
2378 and ppt.system_person_type = 'EX_APL'
2379 order by ptu.effective_start_date;
2380
2381 -- Bug 10286850
2382 l_person_id per_all_people_f.person_id%type;
2383 cursor csr_chk_emp is
2384 select asg.person_id
2385 from per_all_assignments_f asg
2386 where asg.application_id = p_application_id
2387 and asg.assignment_type = 'E'
2388 and asg.effective_start_date > p_date_received;
2389
2390
2391 --Bug 3891787 Added the cursor to check for the person_type change
2392 cursor csr_ptu_row1
2393 is
2394 select ptu.person_type_id,ptu.effective_start_date
2395 from per_person_type_usages_f ptu
2396 where ptu.person_id = p_person_id
2397 and p_date_end+1 between ptu.effective_start_date and
2398 ptu.effective_start_date;
2399
2400 l_person_type_id per_person_type_usages.person_id%type;
2401 l_start_date date;
2402 l_date_end date;
2403 l_update_mode varchar2(30);
2404
2405 -- ER FPT
2406 l_new_application_id number;
2407 l_current_apl_id number;
2408 l_apl_end_date date;
2409 l_current_apl_end_date date;
2410 l_person_type_id1 number;
2411 l_effective_start_date date;
2412
2413 cursor future_apl_asg is
2414 SELECT asg.assignment_id
2415 ,asg.effective_start_date
2416 ,asg.effective_end_date
2417 FROM per_all_assignments_f asg
2418 ,per_assignment_status_types ast
2419 WHERE asg.effective_start_date > p_date_end
2420 AND asg.person_id = p_person_id
2421 AND asg.application_id = p_application_id
2422 AND asg.assignment_status_type_id = ast.assignment_status_type_id
2423 AND ast.per_system_status = 'ACTIVE_APL'
2424 AND asg.assignment_id NOT IN
2425 (
2426 SELECT assignment_id
2427 FROM per_all_assignments_f asg1
2428 WHERE asg1.application_id = p_application_id
2429 AND asg1.person_id = p_person_id
2430 AND p_date_end BETWEEN asg1.effective_start_date AND asg1.effective_end_date)
2431 ORDER BY asg.effective_start_date;
2432
2433 cursor chk_apl_exists(p_asg_start_date date) is
2434 select application_id, date_end
2435 from per_applications
2436 where person_id = p_person_id
2437 and application_id <> p_application_id
2438 and p_asg_start_date between date_received and nvl(date_end,hr_api.g_eot);
2439
2440 cursor fpt_person_rec is
2441 select effective_start_date,effective_end_date
2442 from per_all_people_f per, per_person_types ppt
2443 where person_id = p_person_id
2444 and per.person_type_id = ppt.person_type_id
2445 and effective_start_date > p_date_received
2446 order by effective_start_date;
2447
2448 cursor fpt_ptu_apl is
2449 select effective_start_date, ptu.person_type_id
2450 from per_person_type_usages_f ptu, per_person_types ppt
2451 where ptu.person_type_id = ppt.person_type_id
2452 and person_id = p_person_id
2453 and system_person_type in ('APL')
2454 and effective_start_date > p_date_received;
2455
2456 cursor future_apln is
2457 select rowid,application_id
2458 from per_applications
2459 where person_id = p_person_id
2460 and date_received > p_date_received;
2461
2462 BEGIN
2463 hr_utility.set_location('per_applications_pkg.update_row',10);
2464
2465 -- ER FPT
2466 IF (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
2467 -- Reverse Terminating the End Application
2468 if p_date_end is null then
2469 -- looping through the future APL
2470 for apl in future_apln loop
2471
2472
2473 hr_utility.set_location('Future APL exists on : '||apl.application_id,12);
2474 -- updating the assignments whose APL ID is in future with current APL ID
2475 update per_all_assignments_f
2476 set application_id = p_application_id
2477 where application_id = apl.application_id
2478 and person_id = p_person_id
2479 and assignment_type = 'A';
2480 -- deleting the future APL
2481 per_applications_pkg.delete_row(apl.rowid);
2482
2483 end loop;
2484
2485 -- looping through the future PTU records of APL
2486 for ptu_rec in fpt_ptu_apl loop
2487
2488 hr_utility.set_location('Cancelling future APL records on PTU on : '||ptu_rec.effective_start_date,12);
2489 l_person_type_id1 := ptu_rec.person_type_id;
2490 delete from per_person_type_usages_f
2491 where person_id = p_person_id
2492 and effective_start_date = ptu_rec.effective_start_date
2493 and person_type_id = l_person_type_id1;
2494 end loop;
2495
2496 -- Extending the current APL record till EOT
2497 update per_person_type_usages_f
2498 set effective_end_date = hr_api.g_eot
2499 where person_id = p_person_id
2500 and effective_start_date = p_date_received
2501 and person_type_id = l_person_type_id1;
2502
2503 -- looping through the future PER records
2504 for per_rec in fpt_person_rec loop
2505
2506 select min(effective_start_date) into l_effective_start_date
2507 from per_all_people_f
2508 where person_id = p_person_id
2509 and effective_start_date > p_date_received;
2510
2511 -- Updating the current records end date
2512 update per_all_people_f
2513 set effective_end_date = l_effective_start_date - 1
2514 where person_id = p_person_id
2515 and effective_start_date = p_date_received;
2516
2517 -- setting the person type ID for updating the PER records
2518 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id(p_business_group_id,'APL');
2519 if hr_general2.is_person_type(p_person_id,'EMP',per_rec.effective_start_date) then
2520 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id(p_business_group_id,'EMP_APL');
2521 elsif hr_general2.is_person_type(p_person_id,'EX_EMP',per_rec.effective_start_date) then
2522 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id(p_business_group_id,'EX_EMP_APL');
2523 end if;
2524
2525 hr_utility.set_location('Updating PER record on : '||per_rec.effective_start_date,12);
2526
2527 update per_all_people_f
2528 set person_type_id = l_person_type_id1
2529 ,current_applicant_flag = 'Y'
2530 ,current_emp_or_apl_flag = 'Y'
2531 where person_id = p_person_id
2532 and effective_start_date = per_rec.effective_start_date
2533 and effective_end_date = per_rec.effective_end_date;
2534
2535 end loop;
2536
2537 else
2538 -- looping through the future application assgts
2539 for asg_rec in future_apl_asg loop
2540
2541 select max(effective_end_date)
2542 into l_apl_end_date
2543 from per_all_assignments_f
2544 where person_id = p_person_id
2545 and assignment_id = asg_rec.assignment_id
2546 and assignment_type = 'A';
2547
2548 if l_apl_end_date = hr_api.g_eot then
2549 l_apl_end_date := null;
2550 end if;
2551
2552 -- checking whether an APL exists on this date
2553 open chk_apl_exists(asg_rec.effective_start_date);
2554 fetch chk_apl_exists into l_current_apl_id,l_current_apl_end_date;
2555 if chk_apl_exists%notfound then
2556
2557 SELECT per_applications_s.nextval into l_new_application_id FROM sys.dual;
2558 l_current_apl_id := l_new_application_id;
2559 hr_utility.set_location('Creating a new APL with ID : '||l_new_application_id,15);
2560 begin
2561 -- If there is no APL, creating the new APL
2562 INSERT INTO PER_APPLICATIONS(
2563 application_id,
2564 business_group_id,
2565 person_id,
2566 date_received,
2567 date_end)
2568
2569 VALUES (l_new_application_id,p_business_group_id,p_person_id,asg_rec.effective_start_date,l_apl_end_date);
2570 exception
2571 when others then
2572 raise;
2573 end;
2574 else
2575
2576 if nvl(l_current_apl_end_date,hr_api.g_eot) < nvl(l_apl_end_date,hr_api.g_eot) then
2577
2578 update per_applications
2579 set date_end = l_apl_end_date
2580 where person_id = p_person_id
2581 and application_id = l_current_apl_id;
2582 end if;
2583 end if;
2584 close chk_apl_exists;
2585
2586 -- Updating the asgts with the new APLN Id
2587 update per_all_assignments_f
2588 set application_id = l_current_apl_id
2589 where person_id = p_person_id
2590 and application_id = p_application_id
2591 and assignment_id = asg_rec.assignment_id
2592 and effective_start_date = asg_rec.effective_start_date
2593 and effective_end_date = asg_rec.effective_end_date;
2594
2595 end loop;
2596 end if;
2597 END IF;
2598 -- ER FPT
2599
2600 -- Bug 3053711 Start
2601 -- Added the check if flag = 'Y'
2602 --Commented out for the Bug 4202317
2603 -- if p_Cancellation_Flag = 'Y' then
2604 UPDATE PER_APPLICATIONS
2605 SET
2606 application_id = p_Application_Id,
2607 business_group_id = p_Business_Group_Id,
2608 person_id = p_Person_Id,
2609 date_received = p_Date_Received,
2610 comments = p_Comments,
2611 current_employer = p_Current_Employer,
2612 date_end = p_Date_End,
2613 projected_hire_date = p_Projected_Hire_Date,
2614 successful_flag = p_Successful_Flag,
2615 termination_reason = p_Termination_Reason,
2616 appl_attribute_category = p_Appl_Attribute_Category,
2617 appl_attribute1 = p_Appl_Attribute1,
2618 appl_attribute2 = p_Appl_Attribute2,
2619 appl_attribute3 = p_Appl_Attribute3,
2620 appl_attribute4 = p_Appl_Attribute4,
2621 appl_attribute5 = p_Appl_Attribute5,
2622 appl_attribute6 = p_Appl_Attribute6,
2623 appl_attribute7 = p_Appl_Attribute7,
2624 appl_attribute8 = p_Appl_Attribute8,
2625 appl_attribute9 = p_Appl_Attribute9,
2626 appl_attribute10 = p_Appl_Attribute10,
2627 appl_attribute11 = p_Appl_Attribute11,
2628 appl_attribute12 = p_Appl_Attribute12,
2629 appl_attribute13 = p_Appl_Attribute13,
2630 appl_attribute14 = p_Appl_Attribute14,
2631 appl_attribute15 = p_Appl_Attribute15,
2632 appl_attribute16 = p_Appl_Attribute16,
2633 appl_attribute17 = p_Appl_Attribute17,
2634 appl_attribute18 = p_Appl_Attribute18,
2635 appl_attribute19 = p_Appl_Attribute19,
2636 appl_attribute20 = p_Appl_Attribute20
2637 WHERE rowid = p_rowid;
2638 --Commented out for the Bug 4202317
2639 /*else
2640 UPDATE PER_APPLICATIONS
2641 SET
2642 application_id = p_Application_Id,
2643 business_group_id = p_Business_Group_Id,
2644 person_id = p_Person_Id,
2645 date_received = p_Date_Received,
2646 comments = p_Comments,
2647 current_employer = p_Current_Employer,
2648 date_end = p_Date_End,
2649 projected_hire_date = p_Projected_Hire_Date,
2650 successful_flag = p_Successful_Flag,
2651 termination_reason = p_Termination_Reason,
2652 appl_attribute_category = p_Appl_Attribute_Category,
2653 appl_attribute1 = p_Appl_Attribute1,
2654 appl_attribute2 = p_Appl_Attribute2,
2655 appl_attribute3 = p_Appl_Attribute3,
2656 appl_attribute4 = p_Appl_Attribute4,
2657 appl_attribute5 = p_Appl_Attribute5,
2658 appl_attribute6 = p_Appl_Attribute6,
2659 appl_attribute7 = p_Appl_Attribute7,
2660 appl_attribute8 = p_Appl_Attribute8,
2661 appl_attribute9 = p_Appl_Attribute9,
2662 appl_attribute10 = p_Appl_Attribute10,
2663 appl_attribute11 = p_Appl_Attribute11,
2664 appl_attribute12 = p_Appl_Attribute12,
2665 appl_attribute13 = p_Appl_Attribute13,
2666 appl_attribute14 = p_Appl_Attribute14,
2667 appl_attribute15 = p_Appl_Attribute15,
2668 appl_attribute16 = p_Appl_Attribute16,
2669 appl_attribute17 = p_Appl_Attribute17,
2670 appl_attribute18 = p_Appl_Attribute18,
2671 appl_attribute19 = p_Appl_Attribute19,
2672 appl_attribute20 = p_Appl_Attribute20
2673 WHERE rowid = p_rowid;
2674 end if;*/
2675 -- Bug 3053711 End
2676 -- hr_utility.set_location('per_applications_pkg.update_row',20); --Commented to resolve Bug#13934184
2677 if (SQL%NOTFOUND) then
2678 RAISE NO_DATA_FOUND;
2679 end if;
2680
2681 hr_utility.set_location('per_applications_pkg.update_row',30);
2682 --
2683 -- Now maintain the PTU data...
2684 --
2685 -- 3652025: Another package will do the updates when performing a termination.
2686 -- The reverse termination is still part of this update.
2687 --
2688 if p_Date_End is not null then
2689 NULL;
2690 --
2691 -- Either terminating or updating an already
2692 -- terminated application.
2693 --
2694 -- PTU : Following code added for PTU
2695 --
2696 --hr_utility.set_location('per_applications_pkg.update_row',40);
2697
2698 --Bug No 3891787 starts here
2699 --Open csr_ptu_row1;
2700 --fetch csr_ptu_row1 into l_person_type_id,l_start_date;
2701 --if csr_ptu_row1%notfound then
2702 -- null;
2703 --end if;
2704 --if nvl(l_person_type_id,-1) <> p_person_type_id then
2705 -- if p_date_end +1 = l_start_date then
2706 -- l_update_mode := hr_api.g_correction;
2707 -- end if;
2708 -- hr_per_type_usage_internal.maintain_person_type_usage
2709 -- ( p_effective_date => p_Date_End+1
2710 -- ,p_person_id => p_Person_id
2711 -- ,p_person_type_id => p_Person_Type_Id
2712 -- ,p_datetrack_update_mode => l_update_mode
2713 /*hr_person_type_usage_info.get_default_person_type_id
2714 ( p_Business_Group_Id
2715 ,'EX_APL')*/
2716
2717 -- );
2718 --end if;
2719 --close csr_ptu_row1;
2720 --Bug No 3891787 ends here
2721 --hr_utility.set_location('per_applications_pkg.update_row',50);
2722 -- End of PTU Changes
2723 --
2724 -- hr_per_type_usage_internal.maintain_ptu(
2725 -- p_action => 'TERM_APL',
2726 -- p_person_id => p_Person_id,
2727 -- p_actual_termination_date => p_Date_End);
2728 else
2729 --
2730 -- Either rev-terming or updating an unterminated application
2731 --
2732 -- hr_per_type_usage_internal.maintain_ptu(
2733 -- p_action => 'REV_TERM_APL',
2734 -- p_date_start => p_date_received,
2735 -- p_person_id => p_person_id);
2736
2737 -- PTU : Following code added for PTU (and changed for bug 2506446)
2738
2739 open csr_ptu_row;
2740 fetch csr_ptu_row into l_date_end;
2741 close csr_ptu_row;
2742
2743 -- Bug 10286850
2744 open csr_chk_emp;
2745 fetch csr_chk_emp into l_person_id;
2746 if csr_chk_emp%found then
2747 hr_utility.set_location('per_applications_pkg.update_row',60);
2748 hr_utility.set_message(800,'PER_7594_APP_TERM_EMP_HIRE');
2749 hr_utility.raise_error;
2750 end if;
2751 close csr_chk_emp;
2752
2753 hr_utility.set_location('per_applications_pkg.p_date_received = '||to_char(p_date_received,'DD/MM/YYYY'),60);
2754 hr_utility.set_location('per_applications_pkg.p_date_end = '||to_char(l_date_end,'DD/MM/YYYY'),60);
2755 hr_utility.set_location('per_applications_pkg.p_person_id = '||to_char(p_person_id),60);
2756
2757 if l_date_end is not null then
2758 hr_per_type_usage_internal.cancel_person_type_usage
2759 (
2760 p_effective_date => l_date_end
2761 ,p_person_id => p_person_id
2762 ,p_system_person_type => 'EX_APL'
2763 );
2764 end if;
2765
2766 -- End of PTU Changes
2767
2768 end if;
2769 END Update_Row;
2770 --
2771 PROCEDURE Delete_Row(p_Rowid VARCHAR2) IS
2772 BEGIN
2773 DELETE FROM PER_APPLICATIONS
2774 WHERE rowid = p_Rowid;
2775
2776 if (SQL%NOTFOUND) then
2777 RAISE NO_DATA_FOUND;
2778 end if;
2779 END Delete_Row;
2780 --
2781 -- ----------------------------------------------------------------------------
2782 -- |-----------------------< maintain_irc_ass_status >----------------------|
2783 -- ----------------------------------------------------------------------------
2784 procedure maintain_irc_ass_status(p_person_id number,
2785 p_business_group_id number,
2786 p_date_end date,
2787 p_effective_date date,
2788 p_application_id number,
2789 p_legislation_code varchar2,
2790 p_action varchar2) is
2791 --
2792 l_assignment_id per_all_assignments_f.assignment_id%Type;
2793 l_irc_ass_status_id irc_assignment_statuses.assignment_status_id%Type;
2794 l_ass_status per_assignment_status_types.per_system_status%Type;
2795 l_irc_asg_status_ovn irc_assignment_statuses.object_version_number%Type;
2796 l_ass_status_type_id per_all_assignments_f.assignment_status_type_id%Type;
2797 --
2798 -- To get the assignment status based o the action (Termination or
2799 -- Reverse termination)
2800 cursor csr_get_asg_status is
2801 select a.assignment_status_type_id
2802 from per_assignment_status_types a,
2803 per_ass_status_type_amends b
2804 where a.per_system_status = l_ass_status
2805 and b.assignment_status_type_id(+) = a.assignment_status_type_id
2806 and b.business_group_id(+) + 0 = p_business_group_id
2807 and nvl(a.business_group_id, p_business_group_id) =
2808 p_business_group_id
2809 and nvl(a.legislation_codE, p_legislation_code) =
2810 p_legislation_code
2811 and nvl(b.active_flag, a.active_flag) = 'Y'
2812 and nvl(b.default_flag, a.default_flag) = 'Y';
2813 --
2814 -- To get all the assignment id's for the concerned application to be
2815 -- terminated
2816 cursor csr_term_ass_id is
2817 select paa.assignment_id
2818 from per_all_assignments_f paa
2819 where paa.application_id = p_application_id
2820 and paa.person_id = p_person_id
2821 and paa.business_group_id + 0 = p_business_group_id
2822 and paa.assignment_type = 'A'
2823 and paa.effective_end_date =
2824 (select max(pa2.effective_end_date)
2825 from per_all_assignments_f pa2
2826 where pa2.person_id = p_person_id
2827 and pa2.application_id = p_application_id);
2828 --
2829 -- To get all the assignment id's for the concerned application to be
2830 -- reverse terminated
2831 cursor csr_cancel_ass_id is
2832 select paa.assignment_id
2833 from per_all_assignments_f paa
2834 where paa.application_id = p_application_id
2835 and paa.person_id = p_person_id
2836 and paa.business_group_id + 0 = p_business_group_id
2837 and paa.assignment_type = 'A'
2838 and paa.effective_end_date = p_date_end;
2839 --
2840 begin
2841 --
2842 hr_utility.set_location('PER_APPLICATIONS_PKG.maintain_irc_ass_status', 10);
2843 --
2844 -- Termination of applicant
2845 if p_action = 'TERM' then
2846 --
2847 l_ass_status := 'TERM_APL';
2848 --
2849 hr_utility.set_location('PER_APPLICATIONS_PKG.maintain_irc_ass_status', 20);
2850 --
2851 open csr_get_asg_status;
2852 fetch csr_get_asg_status into l_ass_status_type_id;
2853 close csr_get_asg_status;
2854 --
2855 open csr_term_ass_id;
2856 loop
2857 fetch csr_term_ass_id into l_assignment_id;
2858 exit when csr_term_ass_id%notfound;
2859 --
2860 irc_asg_status_api.create_irc_asg_status
2861 (p_assignment_id => l_assignment_id,
2862 p_assignment_status_type_id => l_ass_status_type_id,
2863 p_status_change_date => p_effective_date,
2864 p_assignment_status_id => l_irc_ass_status_id,
2865 p_object_version_number => l_irc_asg_status_ovn);
2866 --
2867 end loop;
2868 close csr_term_ass_id;
2869 --
2870 -- Reverse termination of applicant
2871 else
2872 --
2873 l_ass_status := 'ACTIVE_APL';
2874 --
2875 hr_utility.set_location('PER_APPLICATIONS_PKG.maintain_irc_ass_status', 30);
2876 --
2877 open csr_get_asg_status;
2878 fetch csr_get_asg_status into l_ass_status_type_id;
2879 close csr_get_asg_status;
2880 --
2881 open csr_cancel_ass_id;
2882 loop
2883 fetch csr_cancel_ass_id into l_assignment_id;
2884 exit when csr_cancel_ass_id%notfound;
2885 --
2886 irc_asg_status_api.create_irc_asg_status
2887 (p_assignment_id => l_assignment_id,
2888 p_assignment_status_type_id => l_ass_status_type_id,
2889 p_status_change_date => p_effective_date,
2890 p_assignment_status_id => l_irc_ass_status_id,
2891 p_object_version_number => l_irc_asg_status_ovn);
2892 --
2893 end loop;
2894 close csr_cancel_ass_id;
2895 --
2896 end if;
2897 --
2898 hr_utility.set_location('PER_APPLICATIONS_PKG.maintain_irc_ass_status', 40);
2899 --
2900 end maintain_irc_ass_status;
2901 --
2902 END PER_APPLICATIONS_PKG;