[Home] [Help]
PACKAGE BODY: APPS.HR_APPLICANT_INTERNAL
Source
1 Package Body hr_applicant_internal as
2 /* $Header: peaplbsi.pkb 120.2 2005/10/25 00:29 risgupta noship $ */
3 --
4 -- Package Variables
5 --
6 g_package constant varchar2(33) := 'hr_applicant_internal.';
7 g_debug boolean := hr_utility.debug_enabled;
8 --
9 g_APL_person_type constant varchar2(10) := 'APL';
10 g_EX_APL_person_type constant varchar2(10) := 'EX_APL';
11 --
12
13 CURSOR csr_person_record (cp_person_id number) IS
14 select *
15 from per_all_people_f
16 where person_id = cp_person_id
17 and rownum = 1;
18
19 -- ------------------------------------------------------------------------ +
20 -- -------------------< generate_applicant_number >------------------------ |
21 -- ------------------------------------------------------------------------ +
22 procedure generate_applicant_number
23 (p_business_group_id IN NUMBER
24 ,p_person_id IN NUMBER
25 ,p_effective_date IN DATE
26 ,p_party_id IN NUMBER
27 ,p_date_of_birth IN DATE
28 ,p_start_date IN DATE
29 ,p_applicant_number IN OUT NOCOPY VARCHAR2) IS
30 --
31 cursor csr_get_apl_number(cp_person_id number
32 ,cp_business_group_id number
33 ,cp_effective_date date ) is
34 select applicant_number
35 from per_all_people_f
36 where person_id = cp_person_id
37 and business_group_id = cp_business_group_id
38 and applicant_number is not null
39 and (cp_effective_date between effective_start_date and effective_end_date
40 or effective_start_date > cp_effective_date)
41 order by effective_start_date ASC;
42 --
43 l_dummy varchar2(100);
44 l_applicant_number per_all_people_f.applicant_number%TYPE;
45 l_method_of_generation VARCHAR2(30);
46 --
47 BEGIN
48 --
49 l_applicant_number := p_applicant_number;
50 --
51 SELECT pbg.method_of_generation_apl_num
52 INTO l_method_of_generation
53 FROM per_business_groups_perf pbg
54 WHERE pbg.business_group_id = p_business_group_id;
55 --
56
57 if l_method_of_generation = 'M' and l_applicant_number IS NULL then
58 open csr_get_apl_number(p_person_id, p_business_group_id, p_effective_date);
59 fetch csr_get_apl_number into l_applicant_number;
60 close csr_get_apl_number;
61 end if;
62
63 hr_person.generate_number
64 (p_current_employee => 'N'
65 ,p_current_applicant => 'Y'
66 ,p_current_npw => 'N'
67 ,p_national_identifier => NULL
68 ,p_business_group_id => p_business_group_id
69 ,p_person_id => p_person_id
70 ,p_employee_number => l_dummy
71 ,p_applicant_number => l_applicant_number
72 ,p_npw_number => l_dummy
73 ,p_effective_date => p_effective_date
74 ,p_party_id => p_party_id
75 ,p_date_of_birth => p_date_of_birth
76 ,p_start_date => p_start_date);
77
78 hr_person.validate_unique_number
79 (p_person_id => p_person_id
80 ,p_business_group_id => p_business_group_id
81 ,p_employee_number => null
82 ,p_applicant_number => l_applicant_number
83 ,p_npw_number => null
84 ,p_current_employee => 'N'
85 ,p_current_applicant => 'Y'
86 ,p_current_npw => 'N');
87 --
88 p_applicant_number := l_applicant_number;
89 --
90 END generate_applicant_number;
91 --
92 -- ------------------------------------------------------------------------ +
93 -- ---------------< get_new_APL_person_type >------------------------------ |
94 -- ------------------------------------------------------------------------ +
95 procedure get_new_APL_person_type(p_business_group_id IN number
96 ,p_current_person_type IN varchar2
97 ,p_new_sys_person_type OUT nocopy varchar2
98 ,p_new_person_type_id OUT nocopy number)
99 is
100 -- ------------------------------------------------------
101 -- Current person type New person type
102 -- ------------------- -------------------------------
103 -- EX_APL APL
104 -- EX_EMP EX_EMP_APL
105 -- EMP EMP_APL
106 -- EX_CWK APL
107 -- CWK APL
108 -- OTHER APL
109 -- ------------------------------------------------------
110
111 l_new_sys_person_type per_person_types.system_person_type%TYPE;
112
113 begin
114
115 if p_current_person_type in ('EMP','EMP_APL') then
116 l_new_sys_person_type := 'EMP_APL';
117 elsif p_current_person_type in ('EX_EMP', 'EX_EMP_APL') then
118 l_new_sys_person_type := 'EX_EMP_APL';
119 else
120 l_new_sys_person_type := 'APL';
121
122 end if;
123
124 p_new_person_type_id := hr_person_type_usage_info.get_default_person_type_id
125 (p_business_group_id, l_new_sys_person_type);
126
127 p_new_sys_person_type := l_new_sys_person_type;
128
129 end get_new_APL_person_type;
130 --
131 -- ------------------------------------------------------------------------ +
132 -- ----------------< get_new_EX_APL_person_type >-------------------------- |
133 -- ------------------------------------------------------------------------ +
134 procedure get_new_EX_APL_person_type(p_business_group_id IN number
135 ,p_current_person_type IN varchar2
136 ,p_new_sys_person_type OUT nocopy varchar2
137 ,p_new_person_type_id OUT nocopy number)
138 is
139 -- ------------------------------------------------------
140 -- Current person type New person type
141 -- ------------------- -------------------------------
142 -- APL APL_EX_APL
143 -- EX_EMP EX_APL
144 -- EMP EX_APL
145 -- EX_CWK EX_APL
146 -- CWK EX_APL
147 -- OTHER EX_APL
148 -- ------------------------------------------------------
149
150 l_new_sys_person_type per_person_types.system_person_type%TYPE;
151
152 begin
153 --
154 if p_current_person_type = 'EX_EMP_APL' then
155 l_new_sys_person_type := 'EX_EMP';
156 elsif p_current_person_type = 'EMP_APL' then
157 l_new_sys_person_type := 'EMP';
158 else
159 l_new_sys_person_type := 'EX_APL';
160
161 end if;
162
163 p_new_person_type_id := hr_person_type_usage_info.get_default_person_type_id
164 (p_business_group_id, l_new_sys_person_type);
165
166 p_new_sys_person_type := l_new_sys_person_type;
167
168 end get_new_EX_APL_person_type;
169 --
170 -- ----------------------------------------------------------------------- +
171 -- -----------------------< Update_Person_Rec >--------------------------- |
172 -- ----------------------------------------------------------------------- +
173 PROCEDURE Update_Person_Rec
174 (p_person_id number
175 ,p_effective_start_date date
176 ,p_effective_end_date date
177 ,p_person_type_id number
178 ,p_applicant_number varchar2
179 ,p_current_emp_apl_flag varchar2
180 ,p_current_apl_flag varchar2
181 ,p_object_version_number in out nocopy number -- BUG4081676
182 ) IS
183 --
184 l_ovn per_all_people_f.object_version_number%TYPE;
185 --
186 BEGIN
187
188 l_ovn := p_object_version_number + 1; -- BUG4081676
189
190 UPDATE per_all_people_f
191 set person_type_id = p_person_type_id
192 ,current_applicant_flag = p_current_apl_flag
193 ,current_emp_or_apl_flag = p_current_emp_apl_flag
194 ,applicant_number = p_applicant_number
195 ,object_version_number = l_ovn -- BUG4081676
196 where person_id = p_person_id
197 and effective_start_date = p_effective_start_date
198 and effective_end_date = p_effective_end_date;
199
200 p_object_version_number := l_ovn; -- BUG4081676
201 --
202 END Update_Person_rec;
203 -- ----------------------------------------------------------------------- +
204 -- -----------------------< Insert_Person_Rec >--------------------------- |
205 -- ----------------------------------------------------------------------- +
206 PROCEDURE Insert_Person_Rec(p_rec csr_person_record%ROWTYPE
207 ,p_person_id number
208 ,p_effective_start_date date
209 ,p_effective_end_date date
210 ,p_person_type_id number
211 ,p_applicant_number varchar2
212 ,p_current_emp_apl_flag varchar2
213 ,p_current_apl_flag varchar2
214 ,p_current_npw_flag varchar2
215 ,p_current_employee_flag varchar2
216 ,p_object_version_number in out nocopy number -- BUG4081676
217 ) IS
218 --
219 l_created_by per_all_people_f.created_by%TYPE;
220 l_creation_date per_all_people_f.creation_date%TYPE;
221 l_last_update_date per_all_people_f.last_update_date%TYPE;
222 l_last_updated_by per_all_people_f.last_updated_by%TYPE;
223 l_last_update_login per_all_people_f.last_update_login%TYPE;
224 l_ovn per_all_people_f.object_version_number%TYPE;
225 --
226 BEGIN
227 -- Set the AOL updated WHO values
228 --
229 l_last_update_date := sysdate;
230 l_last_updated_by := fnd_global.user_id;
231 l_last_update_login := fnd_global.login_id;
232 l_ovn := p_object_version_number + 1; -- BUG4081676
233
234 INSERT INTO per_all_people_f
235 (person_id,
236 effective_start_date,
237 effective_end_date,
238 business_group_id,
239 person_type_id,
240 last_name,
241 start_date,
242 applicant_number,
243 comment_id,
244 current_applicant_flag,
245 current_emp_or_apl_flag,
246 current_employee_flag,
247 date_employee_data_verified,
248 date_of_birth,
249 email_address,
250 employee_number,
251 expense_check_send_to_address,
252 first_name,
253 full_name,
254 known_as,
255 marital_status,
256 middle_names,
257 nationality,
258 national_identifier,
259 previous_last_name,
260 registered_disabled_flag,
261 sex,
262 title,
263 vendor_id,
264 request_id,
265 program_application_id,
266 program_id,
267 program_update_date,
268 attribute_category,
269 attribute1,
270 attribute2,
271 attribute3,
272 attribute4,
273 attribute5,
274 attribute6,
275 attribute7,
276 attribute8,
277 attribute9,
278 attribute10,
279 attribute11,
280 attribute12,
281 attribute13,
282 attribute14,
283 attribute15,
284 attribute16,
285 attribute17,
286 attribute18,
287 attribute19,
288 attribute20,
289 attribute21,
290 attribute22,
291 attribute23,
292 attribute24,
293 attribute25,
294 attribute26,
295 attribute27,
296 attribute28,
297 attribute29,
298 attribute30,
299 per_information_category,
300 per_information1,
301 per_information2,
302 per_information3,
303 per_information4,
304 per_information5,
305 per_information6,
306 per_information7,
307 per_information8,
308 per_information9,
309 per_information10,
310 per_information11,
311 per_information12,
312 per_information13,
313 per_information14,
314 per_information15,
315 per_information16,
316 per_information17,
317 per_information18,
318 per_information19,
319 per_information20,
320 object_version_number,
321 suffix,
322 DATE_OF_DEATH,
323 BACKGROUND_CHECK_STATUS ,
324 BACKGROUND_DATE_CHECK ,
325 BLOOD_TYPE ,
326 CORRESPONDENCE_LANGUAGE ,
327 FAST_PATH_EMPLOYEE ,
328 FTE_CAPACITY ,
329 HOLD_APPLICANT_DATE_UNTIL ,
330 HONORS ,
331 INTERNAL_LOCATION ,
332 LAST_MEDICAL_TEST_BY ,
333 LAST_MEDICAL_TEST_DATE ,
334 MAILSTOP ,
335 OFFICE_NUMBER ,
336 ON_MILITARY_SERVICE ,
337 ORDER_NAME ,
338 PRE_NAME_ADJUNCT ,
339 PROJECTED_START_DATE ,
340 REHIRE_AUTHORIZOR ,
341 REHIRE_RECOMMENDATION ,
342 RESUME_EXISTS ,
343 RESUME_LAST_UPDATED ,
344 SECOND_PASSPORT_EXISTS ,
345 STUDENT_STATUS ,
346 WORK_SCHEDULE ,
347 PER_INFORMATION21 ,
348 PER_INFORMATION22 ,
349 PER_INFORMATION23 ,
350 PER_INFORMATION24 ,
351 PER_INFORMATION25 ,
352 PER_INFORMATION26 ,
353 PER_INFORMATION27 ,
354 PER_INFORMATION28 ,
355 PER_INFORMATION29 ,
356 PER_INFORMATION30 ,
357 REHIRE_REASON ,
358 benefit_group_id ,
359 receipt_of_death_cert_date ,
360 coord_ben_med_pln_no ,
361 coord_ben_no_cvg_flag ,
362 COORD_BEN_MED_EXT_ER,
363 COORD_BEN_MED_PL_NAME,
364 COORD_BEN_MED_INSR_CRR_NAME,
365 COORD_BEN_MED_INSR_CRR_IDENT,
366 COORD_BEN_MED_CVG_STRT_DT,
367 COORD_BEN_MED_CVG_END_DT,
368 uses_tobacco_flag ,
369 dpdnt_adoption_date ,
370 dpdnt_vlntry_svce_flag ,
371 original_date_of_hire ,
372 town_of_birth ,
373 region_of_birth ,
374 country_of_birth ,
375 global_person_id ,
376 party_id ,
377 npw_number,
378 current_npw_flag,
379 local_name,
380 global_name,
381 created_by,
382 creation_date,
383 last_update_date,
384 last_updated_by,
385 last_update_login
386 )
387 -- ---------------------------------------------
388 VALUES
389 -- ---------------------------------------------
390 (p_person_id,
391 p_effective_start_date,
392 p_effective_end_date,
393 p_rec.business_group_id,
394 p_person_type_id,
395 p_rec.last_name,
396 p_rec.start_date,
397 p_applicant_number,
398 p_rec.comment_id,
399 p_current_apl_flag,
400 p_current_emp_apl_flag,
401 p_current_employee_flag,
402 p_rec.date_employee_data_verified,
403 p_rec.date_of_birth,
404 p_rec.email_address,
405 p_rec.employee_number,
406 p_rec.expense_check_send_to_address,
407 p_rec.first_name,
408 p_rec.full_name,
409 p_rec.known_as,
410 p_rec.marital_status,
411 p_rec.middle_names,
412 p_rec.nationality,
413 p_rec.national_identifier,
414 p_rec.previous_last_name,
415 p_rec.registered_disabled_flag,
416 p_rec.sex,
417 p_rec.title,
418 p_rec.vendor_id,
419 p_rec.request_id,
420 p_rec.program_application_id,
421 p_rec.program_id,
422 p_rec.program_update_date,
423 p_rec.attribute_category,
424 p_rec.attribute1,
425 p_rec.attribute2,
426 p_rec.attribute3,
427 p_rec.attribute4,
428 p_rec.attribute5,
429 p_rec.attribute6,
430 p_rec.attribute7,
431 p_rec.attribute8,
432 p_rec.attribute9,
433 p_rec.attribute10,
434 p_rec.attribute11,
435 p_rec.attribute12,
436 p_rec.attribute13,
437 p_rec.attribute14,
438 p_rec.attribute15,
439 p_rec.attribute16,
440 p_rec.attribute17,
441 p_rec.attribute18,
442 p_rec.attribute19,
443 p_rec.attribute20,
444 p_rec.attribute21,
445 p_rec.attribute22,
446 p_rec.attribute23,
447 p_rec.attribute24,
448 p_rec.attribute25,
449 p_rec.attribute26,
450 p_rec.attribute27,
451 p_rec.attribute28,
452 p_rec.attribute29,
453 p_rec.attribute30,
454 p_rec.per_information_category,
455 p_rec.per_information1,
456 p_rec.per_information2,
457 p_rec.per_information3,
458 p_rec.per_information4,
459 p_rec.per_information5,
460 p_rec.per_information6,
461 p_rec.per_information7,
462 p_rec.per_information8,
463 p_rec.per_information9,
464 p_rec.per_information10,
465 p_rec.per_information11,
466 p_rec.per_information12,
467 p_rec.per_information13,
468 p_rec.per_information14,
469 p_rec.per_information15,
470 p_rec.per_information16,
471 p_rec.per_information17,
472 p_rec.per_information18,
473 p_rec.per_information19,
474 p_rec.per_information20,
475 -- p_rec.object_version_number,
476 l_ovn, -- BUG4081676
477 p_rec.suffix,
478 p_rec.DATE_OF_DEATH ,
479 p_rec.BACKGROUND_CHECK_STATUS ,
480 p_rec.BACKGROUND_DATE_CHECK ,
481 p_rec.BLOOD_TYPE ,
482 p_rec.CORRESPONDENCE_LANGUAGE ,
483 p_rec.FAST_PATH_EMPLOYEE ,
484 p_rec.FTE_CAPACITY ,
485 p_rec.HOLD_APPLICANT_DATE_UNTIL ,
486 p_rec.HONORS ,
487 p_rec.INTERNAL_LOCATION ,
488 p_rec.LAST_MEDICAL_TEST_BY ,
489 p_rec.LAST_MEDICAL_TEST_DATE ,
490 p_rec.MAILSTOP ,
491 p_rec.OFFICE_NUMBER ,
492 p_rec.ON_MILITARY_SERVICE ,
493 p_rec.ORDER_NAME ,
494 p_rec.PRE_NAME_ADJUNCT ,
495 p_rec.PROJECTED_START_DATE ,
496 p_rec.REHIRE_AUTHORIZOR ,
497 p_rec.REHIRE_RECOMMENDATION ,
498 p_rec.RESUME_EXISTS ,
499 p_rec.RESUME_LAST_UPDATED ,
500 p_rec.SECOND_PASSPORT_EXISTS ,
501 p_rec.STUDENT_STATUS ,
502 p_rec.WORK_SCHEDULE ,
503 p_rec.PER_INFORMATION21 ,
504 p_rec.PER_INFORMATION22 ,
505 p_rec.PER_INFORMATION23 ,
506 p_rec.PER_INFORMATION24 ,
507 p_rec.PER_INFORMATION25 ,
508 p_rec.PER_INFORMATION26 ,
509 p_rec.PER_INFORMATION27 ,
510 p_rec.PER_INFORMATION28 ,
511 p_rec.PER_INFORMATION29 ,
512 p_rec.PER_INFORMATION30 ,
513 p_rec.REHIRE_REASON ,
514 p_rec.BENEFIT_GROUP_ID ,
515 p_rec.RECEIPT_OF_DEATH_CERT_DATE ,
516 p_rec.COORD_BEN_MED_PLN_NO ,
517 p_rec.COORD_BEN_NO_CVG_FLAG ,
518 p_rec.COORD_BEN_MED_EXT_ER,
519 p_rec.COORD_BEN_MED_PL_NAME,
520 p_rec.COORD_BEN_MED_INSR_CRR_NAME,
521 p_rec.COORD_BEN_MED_INSR_CRR_IDENT,
522 p_rec.COORD_BEN_MED_CVG_STRT_DT,
523 p_rec.COORD_BEN_MED_CVG_END_DT ,
524 p_rec.USES_TOBACCO_FLAG ,
525 p_rec.DPDNT_ADOPTION_DATE ,
526 p_rec.DPDNT_VLNTRY_SVCE_FLAG ,
527 p_rec.ORIGINAL_DATE_OF_HIRE ,
528 p_rec.town_of_birth ,
529 p_rec.region_of_birth ,
530 p_rec.country_of_birth ,
531 p_rec.global_person_id ,
532 p_rec.party_id ,
533 p_rec.npw_number,
534 p_current_npw_flag,
535 p_rec.local_name,
536 p_rec.global_name,
537 p_rec.created_by,
538 p_rec.creation_date,
539 l_last_update_date,
540 l_last_updated_by,
541 l_last_update_login
542 );
543
544 p_object_version_number := l_ovn; -- BUG4081676
545
546 END Insert_Person_Rec;
547 -- -------------------------------------------------------------------------- +
548 -- |--------------------< Update_PER_PTU_to_EX_APL >------------------------- |
549 -- -------------------------------------------------------------------------- +
550 PROCEDURE Update_PER_PTU_to_EX_APL
551 (p_business_group_id IN number
552 ,p_person_id IN number
553 ,p_effective_date IN date
554 ,p_person_type_id IN number -- EX_APL type
555 ,p_per_effective_start_date out nocopy date
556 ,p_per_effective_end_date out nocopy DATE
557 )
558 IS
559 --
560 cursor csr_get_person_details(cp_person_id number, cp_effective_date date)
561 IS
562 select *
563 from per_all_people_f peo
564 where person_id = cp_person_id
565 and (effective_start_date >= cp_effective_date
566 OR
567 cp_effective_date between effective_start_date
568 and effective_end_date)
569 order by peo.effective_start_date ASC
570 for update of person_type_id;
571
572 cursor csr_get_person_type(cp_person_type_id number) IS
573 select ppt.system_person_type
574 from per_person_types ppt
575 where ppt.person_type_id = cp_person_type_id;
576
577 cursor csr_ptu_details(cp_person_id number, cp_effective_date date) is
578 select ptu.person_type_id, ppt.system_person_type
579 ,ptu.effective_start_date, ptu.effective_end_date
580 from per_person_type_usages_f ptu
581 ,per_person_types ppt
582 where ptu.person_id = cp_person_id
583 and cp_effective_date between ptu.effective_start_date
584 and ptu.effective_end_date
585 and ppt.person_type_id = ptu.person_type_id
586 and ppt.system_person_type in ('APL','EX_APL')
587 order by effective_start_date ASC;
588
589
590 l_proc constant varchar2(100) := g_package||'Update_PER_PTU_to_EX_APL';
591 l_effective_date date;
592
593 l_business_group_id per_all_people_f.business_group_id%TYPE;
594 l_effective_start_date per_all_people_f.effective_start_date%TYPE;
595 l_effective_end_date per_all_people_f.effective_end_date%TYPE;
596 l_system_person_type per_person_types.system_person_type%TYPE;
597 l_ovn per_all_people_f.object_version_number%TYPE;
598
599 l_new_person_type_id per_person_types.person_type_id%TYPE;
600 l_ptu_person_type_id per_person_types.person_type_id%TYPE;
601 l_new_sys_person_type per_person_types.system_person_type%TYPE;
602
603 l_new_effective_date DATE;
604 --
605 l_per_effective_start_date date;
606 l_per_effective_end_date date;
607 l_name_combination_warning boolean;
608 l_dob_null_warning boolean;
609 l_orig_hire_warning boolean;
610 l_comment_id number;
611
612 l_current_applicant_flag per_people_f.current_applicant_flag%type;
613 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
614 l_current_employee_flag per_people_f.current_employee_flag%type;
615 l_employee_number per_people_f.employee_number%type;
616 l_applicant_number per_people_f.applicant_number%TYPE;
617 l_npw_number per_people_f.npw_number%TYPE;
618
619 l_full_name per_people_f.full_name%type;
620
621 l_person_rec csr_get_person_details%ROWTYPE;
622 l_future_person_rec csr_get_person_details%ROWTYPE;
623 l_ptu_rec csr_ptu_details%ROWTYPE;
624
625 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
626 l_ptu_ovn per_person_type_usages_f.object_version_number%TYPE;
627 l_ptu_eff_start_date per_person_type_usages_f.effective_start_date%TYPE;
628 l_ptu_eff_end_date per_person_type_usages_f.effective_end_date%TYPE;
629
630
631 begin
632 if g_debug then
633 hr_utility.set_location(' Entering: '||l_proc,10);
634 end if;
635 --
636 l_effective_date := trunc(p_effective_date);
637 --
638 l_ptu_person_type_id := p_person_type_id;
639 per_per_bus.chk_person_type
640 (p_person_type_id => l_ptu_person_type_id,
641 p_business_group_id => p_business_group_id,
642 p_expected_sys_type => 'EX_APL');
643 --
644 if g_debug then
645 hr_utility.set_location(' Entering: '||l_proc,10);
646 end if;
647 --
648 open csr_get_person_details(p_person_id, l_effective_date);
649 fetch csr_get_person_details into l_person_rec;
650 if csr_get_person_details%FOUND then
651
652 if g_debug then
653 hr_utility.set_location(l_proc,15);
654 end if;
655
656 open csr_get_person_type(l_person_rec.person_type_id);
657 fetch csr_get_person_type into l_system_person_type;
658 close csr_get_person_type;
659
660 get_new_EX_APL_person_type(l_person_rec.business_group_id
661 ,l_system_person_type
662 ,l_new_sys_person_type
663 ,l_new_person_type_id);
664
665 if hr_general2.is_person_type(
666 p_person_id => l_person_rec.person_id
667 ,p_person_type => 'EMP'
668 ,p_effective_date => l_effective_date) then
669 l_current_emp_or_apl_flag := 'Y';
670 else
671 l_current_emp_or_apl_flag := null;
672 end if;
673 l_current_applicant_flag := null;
674 l_ovn := l_person_rec.object_version_number;
675 l_new_effective_date := l_person_rec.effective_start_date;
676 l_per_effective_end_date := l_person_rec.effective_end_date;
677
678 if l_new_sys_person_type = l_system_person_type then
679 --
680 -- person is ex_applicant; do nothing
681 --
682 if g_debug then
683 hr_utility.set_location(l_proc,20);
684 end if;
685 --
686 else
687 --
688 -- update current record to ex_applicant
689 --
690 if l_person_rec.effective_start_date = l_effective_date then
691 --
692 -- Update current record, simulate 'CORRECTION' mode
693 --
694 if g_debug then
695 hr_utility.set_location(l_proc,25);
696 end if;
697 --
698 Update_Person_Rec
699 (p_person_id => l_person_rec.person_id
700 ,p_effective_start_date => l_person_rec.effective_start_date
701 ,p_effective_end_date => l_person_rec.effective_end_date
702 ,p_person_type_id => l_new_person_type_id
703 ,p_applicant_number => l_person_rec.applicant_number
704 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
705 ,p_current_apl_flag => l_current_applicant_flag
706 ,p_object_version_number => l_ovn);
707
708 else
709 --
710 -- DT update: person becomes ex_applicant on effective date
711 --
712 if g_debug then
713 hr_utility.set_location(l_proc,30);
714 end if;
715
716 l_new_effective_date := l_effective_date;
717 l_per_effective_end_date := l_person_rec.effective_end_date;
718 --
719 -- End date current record
720 UPDATE per_all_people_f
721 set effective_end_date = l_effective_date -1
722 where person_id = l_person_rec.person_id
723 and effective_start_date = l_person_rec.effective_start_date
724 and effective_end_date = l_person_rec.effective_end_date;
725
726 -- Create the new DT update using new person type
727 --
728 Insert_Person_Rec
729 (p_rec => l_person_rec
730 ,p_person_id => p_person_id
731 ,p_effective_start_date => l_effective_date
732 ,p_effective_end_date => l_person_rec.effective_end_date
733 ,p_person_type_id => l_new_person_type_id
734 ,p_applicant_number => l_person_rec.applicant_number
735 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
736 ,p_current_apl_flag => l_current_applicant_flag
737 ,p_current_employee_flag => l_person_rec.current_employee_flag
738 ,p_current_npw_flag => l_person_rec.current_npw_flag
739 ,p_object_version_number => l_ovn);
740 end if;
741 end if;
742 --
743 if g_debug then
744 hr_utility.set_location(l_proc,40);
745 end if;
746 --
747 -- process future person records using "CORRECTION" mode
748 --
749 LOOP
750 fetch csr_get_person_details into l_person_rec;
751
752 exit when csr_get_person_details%NOTFOUND;
753
754 open csr_get_person_type(l_person_rec.person_type_id);
755 fetch csr_get_person_type into l_system_person_type;
756 close csr_get_person_type;
757
758 l_ovn := l_person_rec.object_version_number;
759
760 get_new_EX_APL_person_type(l_person_rec.business_group_id
761 , l_system_person_type
762 , l_new_sys_person_type
763 , l_new_person_type_id);
764 if hr_general2.is_person_type
765 (p_person_id => l_person_rec.person_id
766 ,p_person_type => 'EMP'
767 ,p_effective_date => l_person_rec.effective_start_date) then
768 l_current_emp_or_apl_flag := 'Y';
769 else
770 l_current_emp_or_apl_flag := null;
771 end if;
772
773 if l_new_sys_person_type <> l_system_person_type then
774
775 Update_Person_Rec
776 (p_person_id => l_person_rec.person_id
777 ,p_effective_start_date => l_person_rec.effective_start_date
778 ,p_effective_end_date => l_person_rec.effective_end_date
779 ,p_person_type_id => l_new_person_type_id
780 ,p_applicant_number => l_person_rec.applicant_number
781 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
782 ,p_current_apl_flag => l_current_applicant_flag
783 ,p_object_version_number => l_ovn);
784
785 end if; -- person type is different
786
787 END LOOP;
788 if g_debug then
789 hr_utility.set_location(l_proc,45);
790 end if;
791
792 else
793 -- person details not found: ABNORMAL condition
794 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
795 hr_utility.set_message_token('PROCEDURE',l_proc);
796 hr_utility.set_message_token('STEP',80);
797 hr_utility.raise_error;
798 end if;
799 close csr_get_person_details;
800
801
802 -- ---------------------------------------------------------------------- +
803 -- ------------------------ PTU UPDATES --------------------------------- |
804 -- ---------------------------------------------------------------------- +
805 if g_debug then
806 hr_utility.set_location(l_proc,50);
807 hr_utility.trace(' ==> person record became EX_APL on '||
808 to_char(l_new_effective_date));
809 end if;
810 --
811 open csr_ptu_details(p_person_id, l_new_effective_date);
812 fetch csr_ptu_details into l_ptu_rec;
813 if csr_ptu_details%FOUND then
814
815 if l_ptu_rec.system_person_type = 'EX_APL' then
816 --
817 -- person is ex_applicant on new_effective_date
818 --
819 if g_debug then
820 hr_utility.set_location(l_proc,60);
821 end if;
822
823 else -- person is APL
824 if g_debug then
825 hr_utility.set_location(l_proc,65);
826 end if;
827 --
828 if l_ptu_rec.effective_end_date <> hr_api.g_eot then
829 hr_per_type_usage_internal.maintain_person_type_usage
830 (p_effective_date => l_new_effective_date
831 ,p_person_id => p_person_id
832 ,p_person_type_id => l_ptu_person_type_id
833 ,p_datetrack_update_mode => hr_api.g_update_override
834 );
835 else
836 hr_per_type_usage_internal.maintain_person_type_usage
837 (p_effective_date => l_new_effective_date
838 ,p_person_id => p_person_id
839 ,p_person_type_id => l_ptu_person_type_id
840 ,p_datetrack_update_mode => hr_api.g_update
841 );
842 end if;
843 --
844 end if;
845 ELSE -- APL ptu record not found
846 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
847 hr_utility.set_message_token('PROCEDURE',l_proc);
848 hr_utility.set_message_token('STEP',85);
849 hr_utility.raise_error;
850 END IF;
851 if g_debug then
852 hr_utility.set_location(' Leaving: '||l_proc,1000);
853 end if;
854 --
855 -- Setting OUT parameters
856 --
857 p_per_effective_start_date := l_new_effective_date;
858 p_per_effective_end_date := l_per_effective_end_date;
859 --
860 end Update_PER_PTU_to_EX_APL;
861 --
862 --
863 -- -------------------------------------------------------------------------- +
864 -- |---------------------< Update_PER_PTU_Records >-------------------------- |
865 -- -------------------------------------------------------------------------- +
866 PROCEDURE Update_PER_PTU_Records
867 (p_business_group_id IN number
868 ,p_person_id IN number
869 ,p_effective_date IN date
870 ,p_applicant_number IN varchar2
871 ,p_APL_person_type_id IN number
872 ,p_per_effective_start_date out nocopy date
873 ,p_per_effective_end_date out nocopy DATE
874 ,p_per_object_version_number in out nocopy number -- BUG4081676
875 )
876 IS
877 --
878 cursor csr_get_person_details(cp_person_id number, cp_effective_date date)
879 IS
880 select *
881 from per_all_people_f peo
882 where person_id = cp_person_id
883 and (effective_start_date >= cp_effective_date
884 OR
885 cp_effective_date between effective_start_date
886 and effective_end_date)
887 order by peo.effective_start_date ASC
888 for update of person_type_id;
889
890 cursor csr_get_person_type(cp_person_type_id number) IS
891 select ppt.system_person_type
892 from per_person_types ppt
893 where ppt.person_type_id = cp_person_type_id;
894
895 cursor csr_ptu_details(cp_person_id number, cp_effective_date date) is
896 select ptu.person_type_id, ppt.system_person_type
897 ,ptu.effective_start_date, ptu.effective_end_date
898 from per_person_type_usages_f ptu
899 ,per_person_types ppt
900 where ptu.person_id = cp_person_id
901 and (cp_effective_date between ptu.effective_start_date
902 and ptu.effective_end_date
903 or
904 effective_start_date > cp_effective_date)
905 and ppt.person_type_id = ptu.person_type_id
906 and ppt.system_person_type in ('APL','EX_APL')
907 order by effective_start_date ASC;
908
909
910 l_proc constant varchar2(100) := g_package||'Update_PER_PTU_Records';
911 l_effective_date date;
912
913 l_business_group_id per_all_people_f.business_group_id%TYPE;
914 l_effective_start_date per_all_people_f.effective_start_date%TYPE;
915 l_effective_end_date per_all_people_f.effective_end_date%TYPE;
916 l_system_person_type per_person_types.system_person_type%TYPE;
917 l_ovn per_all_people_f.object_version_number%TYPE;
918
919 l_ptu_person_type_id per_person_types.person_type_id%TYPE;
920 l_new_person_type_id per_person_types.person_type_id%TYPE;
921 l_new_sys_person_type per_person_types.system_person_type%TYPE;
922 l_first_person_type_id per_person_types.person_type_id%TYPE;
923 l_current_person_type per_person_types.person_type_id%TYPE;
924 l_start_date date;
925
926 l_new_effective_date DATE;
927 --
928 l_per_effective_start_date date;
929 l_per_effective_end_date date;
930 l_name_combination_warning boolean;
931 l_dob_null_warning boolean;
932 l_orig_hire_warning boolean;
933 l_comment_id number;
934
935 l_current_applicant_flag per_people_f.current_applicant_flag%type;
936 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
937 l_current_employee_flag per_people_f.current_employee_flag%type;
938 l_employee_number per_people_f.employee_number%type;
939 l_applicant_number per_people_f.applicant_number%TYPE;
940 l_npw_number per_people_f.npw_number%TYPE;
941
942 l_full_name per_people_f.full_name%type;
943
944 l_person_rec csr_get_person_details%ROWTYPE;
945 l_future_person_rec csr_get_person_details%ROWTYPE;
946 l_ptu_rec csr_ptu_details%ROWTYPE;
947
948 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
949 l_ptu_ovn per_person_type_usages_f.object_version_number%TYPE;
950 l_ptu_eff_start_date per_person_type_usages_f.effective_start_date%TYPE;
951 l_ptu_eff_end_date per_person_type_usages_f.effective_end_date%TYPE;
952 -- BUG4081676
953 cursor csr_get_per_ovn is
954 select object_version_number
955 from per_all_people_f
956 where person_id = p_person_id
957 and effective_start_date = l_new_effective_date
958 and effective_end_date = l_per_effective_end_date;
959 --
960 begin
961 --
962 if g_debug then
963 hr_utility.set_location(' Entering: '||l_proc,10);
964 end if;
965 --
966 l_effective_date := trunc(p_effective_date);
967 --
968 l_ptu_person_type_id := p_APL_person_type_id;
969 per_per_bus.chk_person_type
970 (p_person_type_id => l_ptu_person_type_id,
971 p_business_group_id => p_business_group_id,
972 p_expected_sys_type => 'APL');
973 --
974 open csr_get_person_details(p_person_id, l_effective_date);
975 fetch csr_get_person_details into l_person_rec;
976 if csr_get_person_details%FOUND then
977
978 if g_debug then
979 hr_utility.set_location(l_proc,15);
980 end if;
981 --
982 open csr_get_person_type(l_person_rec.person_type_id);
983 fetch csr_get_person_type into l_system_person_type;
984 close csr_get_person_type;
985 --
986 get_new_APL_person_type(l_person_rec.business_group_id
987 ,l_system_person_type
988 ,l_new_sys_person_type
989 ,l_new_person_type_id);
990 --
991 if l_person_rec.effective_start_date > l_effective_date then
992 --
993 -- person becomes applicant before first created in the system
994 --
995 if g_debug then
996 hr_utility.set_location(l_proc,16);
997 end if;
998 --
999 l_ovn := l_person_rec.object_version_number;
1000
1001 l_new_effective_date := l_effective_date;
1002 l_per_effective_end_date := l_person_rec.effective_start_date - 1;
1003 --
1004 Insert_Person_Rec
1005 (p_rec => l_person_rec
1006 ,p_person_id => p_person_id
1007 ,p_effective_start_date => l_effective_date
1008 ,p_effective_end_date => l_person_rec.effective_start_date - 1
1009 ,p_person_type_id => l_ptu_person_type_id
1010 ,p_applicant_number => p_applicant_number
1011 ,p_current_emp_apl_flag => 'Y'
1012 ,p_current_apl_flag => 'Y'
1013 ,p_current_employee_flag => NULL
1014 ,p_current_npw_flag => NULL
1015 ,p_object_version_number => l_ovn); -- BUG4081676
1016 --
1017 if l_person_rec.applicant_number is null then
1018 l_applicant_number := p_applicant_number;
1019 else
1020 l_applicant_number := l_person_rec.applicant_number;
1021 end if;
1022 if l_system_person_type <> l_new_sys_person_type then
1023 Update_Person_Rec
1024 (p_person_id => l_person_rec.person_id
1025 ,p_effective_start_date => l_person_rec.effective_start_date
1026 ,p_effective_end_date => l_person_rec.effective_end_date
1027 ,p_person_type_id => l_new_person_type_id
1028 ,p_applicant_number => l_applicant_number
1029 ,p_current_emp_apl_flag => 'Y'
1030 ,p_current_apl_flag => 'Y'
1031 ,p_object_version_number => l_ovn); -- BUG4081676
1032 end if;
1033 -- --------------------------------------------------------------------+
1034 else
1035 -- --------------------------------------------------------------------+
1036 l_ovn := l_person_rec.object_version_number;
1037 l_new_effective_date := l_person_rec.effective_start_date;
1038 l_per_effective_end_date := l_person_rec.effective_end_date;
1039 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
1040 if l_new_sys_person_type = l_system_person_type then
1041 --
1042 -- person is applicant; do nothing
1043 --
1044 if g_debug then
1045 hr_utility.set_location(l_proc,20);
1046 end if;
1047 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
1048 else
1049 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
1050 -- update current record to applicant
1051 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1052 if l_person_rec.effective_start_date = l_effective_date then
1053 --
1054 -- Update current record, simulate 'CORRECTION' mode
1055 --
1056 if g_debug then
1057 hr_utility.set_location(l_proc,25);
1058 end if;
1059 --
1060 Update_Person_Rec
1061 (p_person_id => l_person_rec.person_id
1062 ,p_effective_start_date => l_person_rec.effective_start_date
1063 ,p_effective_end_date => l_person_rec.effective_end_date
1064 ,p_person_type_id => l_new_person_type_id
1065 ,p_applicant_number => p_applicant_number
1066 ,p_current_emp_apl_flag => 'Y'
1067 ,p_current_apl_flag => 'Y'
1068 ,p_object_version_number => l_ovn); -- BUG4081676
1069 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1070 else
1071 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1072 -- DT update: person becomes applicant on effective date
1073 --
1074 if g_debug then
1075 hr_utility.set_location(l_proc,30);
1076 end if;
1077
1078 l_new_effective_date := l_effective_date;
1079 l_per_effective_end_date := l_person_rec.effective_end_date;
1080 --
1081 -- End date current record
1082 UPDATE per_all_people_f
1083 set effective_end_date = l_effective_date -1
1084 where person_id = l_person_rec.person_id
1085 and effective_start_date = l_person_rec.effective_start_date
1086 and effective_end_date = l_person_rec.effective_end_date;
1087
1088 -- Create the new DT update using new person type
1089 --
1090 Insert_Person_Rec
1091 (p_rec => l_person_rec
1092 ,p_person_id => p_person_id
1093 ,p_effective_start_date => l_effective_date
1094 ,p_effective_end_date => l_person_rec.effective_end_date
1095 ,p_person_type_id => l_new_person_type_id
1096 ,p_applicant_number => p_applicant_number
1097 ,p_current_emp_apl_flag => 'Y'
1098 ,p_current_apl_flag => 'Y'
1099 ,p_current_employee_flag => l_person_rec.current_employee_flag
1100 ,p_current_npw_flag => l_person_rec.current_npw_flag
1101 ,p_object_version_number => l_ovn); -- BUG4081676
1102
1103 end if; -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++
1104 end if; -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
1105 end if; -- person became applicant before first created?
1106 --
1107 if g_debug then
1108 hr_utility.set_location(l_proc,40);
1109 end if;
1110 --
1111 -- process future person records using "CORRECTION" mode
1112 --
1113 LOOP
1114 fetch csr_get_person_details into l_person_rec;
1115
1116 exit when csr_get_person_details%NOTFOUND;
1117
1118 open csr_get_person_type(l_person_rec.person_type_id);
1119 fetch csr_get_person_type into l_system_person_type;
1120 close csr_get_person_type;
1121
1122 -- l_ovn := l_person_rec.object_version_number;
1123
1124 get_new_APL_person_type(l_person_rec.business_group_id
1125 , l_system_person_type
1126 , l_new_sys_person_type
1127 , l_new_person_type_id);
1128
1129 if l_new_sys_person_type <> l_system_person_type then
1130 if l_person_rec.applicant_number is null then
1131 l_applicant_number := p_applicant_number;
1132 else
1133 l_applicant_number := l_person_rec.applicant_number;
1134 end if;
1135 --
1136 Update_Person_Rec
1137 (p_person_id => l_person_rec.person_id
1138 ,p_effective_start_date => l_person_rec.effective_start_date
1139 ,p_effective_end_date => l_person_rec.effective_end_date
1140 ,p_person_type_id => l_new_person_type_id
1141 ,p_applicant_number => l_applicant_number
1142 ,p_current_emp_apl_flag => 'Y'
1143 ,p_current_apl_flag => 'Y'
1144 ,p_object_version_number => l_ovn); -- BUG4081676
1145
1146 end if; -- person type is different
1147
1148 END LOOP;
1149 if g_debug then
1150 hr_utility.set_location(l_proc,45);
1151 end if;
1152
1153 else
1154 -- person details not found: ABNORMAL condition
1155 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
1156 hr_utility.set_message_token('PROCEDURE',l_proc);
1157 hr_utility.set_message_token('STEP',50);
1158 hr_utility.raise_error;
1159 end if;
1160 close csr_get_person_details;
1161
1162 -- ---------------------------------------------------------------------- +
1163 -- ------------------------ PTU UPDATES --------------------------------- |
1164 -- ---------------------------------------------------------------------- +
1165 if g_debug then
1166 hr_utility.set_location(l_proc,50);
1167 hr_utility.trace(' ==> person record became APL on '||
1168 to_char(l_new_effective_date));
1169 end if;
1170 -- get default APL person type for PTU updates
1171 l_new_person_type_id := l_ptu_person_type_id;
1172
1173 open csr_ptu_details(p_person_id, l_new_effective_date);
1174 fetch csr_ptu_details into l_ptu_rec;
1175 if csr_ptu_details%FOUND then
1176
1177 if l_ptu_rec.effective_start_date > l_new_effective_date then
1178 -- APL is created in the future, so change the start date
1179 -- and cancel first EX_APL + future rows after EX_APL
1180 --
1181 if l_ptu_rec.system_person_type <> 'APL' then
1182 -- person should be an applicant otherwise it is an abnormal condition
1183 close csr_ptu_details;
1184 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
1185 hr_utility.set_message_token('PROCEDURE',l_proc);
1186 hr_utility.set_message_token('STEP',60);
1187 hr_utility.raise_error;
1188 else
1189 --
1190 l_first_person_type_id := l_ptu_rec.person_type_id;
1191
1192 hr_per_type_usage_internal.change_hire_date_ptu
1193 (p_date_start => l_new_effective_date
1194 ,p_old_date_start => l_ptu_rec.effective_start_date
1195 ,p_person_id => p_person_id
1196 ,p_system_person_type => l_ptu_rec.system_person_type
1197 );
1198 l_start_date := l_ptu_rec.effective_start_date;
1199 Loop
1200 fetch csr_ptu_details into l_ptu_rec;
1201 exit when csr_ptu_details%NOTFOUND;
1202
1203 if l_ptu_rec.system_person_type = 'EX_APL' then
1204 hr_per_type_usage_internal.maintain_person_type_usage
1205 (p_effective_date => l_start_date
1206 ,p_person_id => p_person_id
1207 ,p_person_type_id => l_first_person_type_id
1208 ,p_datetrack_delete_mode => hr_api.g_future_change
1209 );
1210 exit;
1211 else
1212 l_start_date := l_ptu_rec.effective_start_date;
1213 l_first_person_type_id := l_ptu_rec.person_type_id;
1214 end if;
1215 end loop;
1216 close csr_ptu_details;
1217
1218 end if; -- person is APL
1219 --
1220 else -- start_date <= new effective date
1221 --
1222 if l_ptu_rec.system_person_type = 'APL' then
1223 --
1224 -- person is applicant on new_effective_date
1225 --
1226 if g_debug then
1227 hr_utility.set_location(l_proc,60);
1228 end if;
1229 l_first_person_type_id := l_ptu_rec.person_type_id;
1230 --
1231 -- check whether future changes exist, if yes delete otherwise do nothing
1232 --
1233 l_start_date := l_ptu_rec.effective_start_date;
1234 l_current_person_type := l_ptu_rec.person_type_id;
1235 Loop
1236 fetch csr_ptu_details into l_ptu_rec;
1237 exit when csr_ptu_details%NOTFOUND;
1238
1239 if l_ptu_rec.system_person_type = 'EX_APL' then
1240 hr_per_type_usage_internal.maintain_person_type_usage -- 3962781
1241 (p_effective_date => l_start_date
1242 ,p_person_id => p_person_id
1243 ,p_person_type_id => l_current_person_type
1244 ,p_datetrack_delete_mode => hr_api.g_future_change
1245 );
1246 --
1247 exit;
1248 else
1249 l_start_date := l_ptu_rec.effective_start_date;
1250 l_current_person_type := l_ptu_rec.person_type_id;
1251 end if;
1252 end loop;
1253 close csr_ptu_details;
1254 --
1255 else -- person is EX_APL
1256 if g_debug then
1257 hr_utility.set_location(l_proc,65);
1258 end if;
1259 --
1260 if l_ptu_rec.effective_start_date = l_new_effective_date then
1261 close csr_ptu_details;
1262 open csr_ptu_details(p_person_id, l_new_effective_date -1);
1263 fetch csr_ptu_details into l_ptu_rec;
1264 close csr_ptu_details;
1265 --
1266 hr_per_type_usage_internal.maintain_person_type_usage -- 3962781
1267 (p_effective_date => l_new_effective_date -1
1268 ,p_person_id => p_person_id
1269 ,p_person_type_id => l_ptu_rec.person_type_id --l_new_person_type_id
1270 ,p_datetrack_delete_mode => hr_api.g_future_change
1271 );
1272 hr_per_type_usage_internal.maintain_person_type_usage
1273 (p_effective_date => l_new_effective_date
1274 ,p_person_id => p_person_id
1275 ,p_person_type_id => l_new_person_type_id
1276 ,p_datetrack_update_mode => hr_api.g_update
1277 );
1278 else
1279 if g_debug then
1280 hr_utility.set_location(l_proc,75);
1281 end if;
1282 close csr_ptu_details;
1283 if l_ptu_rec.effective_end_date <> hr_api.g_eot then
1284 hr_per_type_usage_internal.maintain_person_type_usage
1285 (p_effective_date => l_new_effective_date
1286 ,p_person_id => p_person_id
1287 ,p_person_type_id => l_new_person_type_id
1288 ,p_datetrack_update_mode => hr_api.g_update_override
1289 );
1290 else
1291 hr_per_type_usage_internal.maintain_person_type_usage
1292 (p_effective_date => l_new_effective_date
1293 ,p_person_id => p_person_id
1294 ,p_person_type_id => l_new_person_type_id
1295 ,p_datetrack_update_mode => hr_api.g_update
1296 );
1297 end if;
1298 end if;
1299 --
1300 end if;
1301 end if;
1302 ELSE -- APL ptu record not found
1303 ---
1304 -- person needs to be transformed into applicant on effective_date
1305 --
1306 if g_debug then
1307 hr_utility.set_location(l_proc,80);
1308 end if;
1309 hr_per_type_usage_internal.create_person_type_usage
1310 (p_person_id => p_person_id
1311 ,p_person_type_id => l_new_person_type_id
1312 ,p_effective_date => l_new_effective_date
1313 ,p_person_type_usage_id => l_person_type_usage_id
1314 ,p_object_version_number => l_ptu_ovn
1315 ,p_effective_start_date => l_ptu_eff_start_date
1316 ,p_effective_end_date => l_ptu_eff_end_date
1317 );
1318
1319 END IF;
1320
1321 -- Get the person's object version number BUG4081676
1322 open csr_get_per_ovn;
1323 fetch csr_get_per_ovn into l_ovn;
1324 close csr_get_per_ovn;
1325
1326 if g_debug then
1327 hr_utility.trace(' l_ovn : '||l_ovn);
1328 hr_utility.set_location(' Leaving: '||l_proc,1000);
1329 end if;
1330 --
1331 -- Setting OUT parameters
1332 --
1333 p_per_effective_start_date := l_new_effective_date;
1334 p_per_effective_end_date := l_per_effective_end_date;
1335 p_per_object_version_number := l_ovn; -- BUG4081676
1336
1337 end Update_PER_PTU_Records;
1338 --
1339 -- -------------------------------------------------------------------------- +
1340 -- |-------------------< Upd_person_EX_APL_and_APL >------------------------- |
1341 -- -------------------------------------------------------------------------- +
1342 PROCEDURE Upd_person_EX_APL_and_APL
1343 (p_business_group_id IN number
1344 ,p_person_id IN number
1345 ,p_ex_apl_effective_date IN date -- date person becomes EX_APL
1346 ,p_apl_effective_date IN date -- date person becomes APL
1347 ,p_per_effective_start_date out nocopy date
1348 ,p_per_effective_end_date out nocopy DATE
1349 )
1350 IS
1351 --
1352 cursor csr_get_person_details(cp_person_id number, cp_ex_apl_date date, cp_apl_date date) is
1353 select *
1354 from per_all_people_f peo
1355 where person_id = cp_person_id
1356 and (cp_ex_apl_date between effective_start_date
1357 and effective_end_date -- becomes ex-apl on this date
1358 or cp_apl_date between effective_start_date
1359 and effective_end_date -- is apl on this date
1360 )
1361 order by peo.effective_start_date ASC;
1362 --
1363 cursor csr_get_person_type(cp_person_type_id number) IS
1364 select ppt.system_person_type
1365 from per_person_types ppt
1366 where ppt.person_type_id = cp_person_type_id;
1367
1368 cursor csr_ptu_details(cp_person_id number, cp_effective_date date) is
1369 select ptu.person_type_id, ppt.system_person_type
1370 ,ptu.effective_start_date, ptu.effective_end_date
1371 from per_person_type_usages_f ptu
1372 ,per_person_types ppt
1373 where ptu.person_id = cp_person_id
1374 and cp_effective_date between ptu.effective_start_date
1375 and ptu.effective_end_date
1376 and ppt.person_type_id = ptu.person_type_id
1377 and ppt.system_person_type in ('APL','EX_APL')
1378 order by effective_start_date ASC;
1379 --
1380 l_proc constant varchar2(100) := g_package||'Upd_person_EX_APL_and_APL';
1381 l_effective_date date;
1382
1383 l_business_group_id per_all_people_f.business_group_id%TYPE;
1384 l_effective_start_date per_all_people_f.effective_start_date%TYPE;
1385 l_effective_end_date per_all_people_f.effective_end_date%TYPE;
1386 l_system_person_type per_person_types.system_person_type%TYPE;
1387 l_ovn per_all_people_f.object_version_number%TYPE;
1388
1389 l_new_person_type_id per_person_types.person_type_id%TYPE;
1390 l_ptu_person_type_id per_person_types.person_type_id%TYPE;
1391 l_new_sys_person_type per_person_types.system_person_type%TYPE;
1392
1393 l_new_effective_date DATE;
1394 --
1395 l_per_effective_start_date date;
1396 l_per_effective_end_date date;
1397 l_name_combination_warning boolean;
1398 l_dob_null_warning boolean;
1399 l_orig_hire_warning boolean;
1400 l_comment_id number;
1401
1402 l_current_applicant_flag per_people_f.current_applicant_flag%type;
1403 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
1404 l_current_employee_flag per_people_f.current_employee_flag%type;
1405 l_employee_number per_people_f.employee_number%type;
1406 l_applicant_number per_people_f.applicant_number%TYPE;
1407 l_npw_number per_people_f.npw_number%TYPE;
1408
1409 l_full_name per_people_f.full_name%type;
1410
1411 l_person_rec csr_get_person_details%ROWTYPE;
1412 l_future_person_rec csr_get_person_details%ROWTYPE;
1413 l_ptu_rec csr_ptu_details%ROWTYPE;
1414
1415 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
1416 l_ptu_ovn per_person_type_usages_f.object_version_number%TYPE;
1417 l_ptu_eff_start_date per_person_type_usages_f.effective_start_date%TYPE;
1418 l_ptu_eff_end_date per_person_type_usages_f.effective_end_date%TYPE;
1419 --
1420 l_ex_per_rec csr_get_person_details%ROWTYPE;
1421 l_apl_per_rec csr_get_person_details%ROWTYPE;
1422 --
1423 PROCEDURE Upd_person
1424 (p_mode varchar2
1425 ,p_ex_apl_rec csr_get_person_details%ROWTYPE
1426 ,p_apl_rec csr_get_person_details%ROWTYPE
1427 ,p_ex_apl_date date
1428 ,p_apl_date date
1429 ,p_person_type number
1430 ,p_current_emp_apl_flag varchar2
1431 ,p_current_apl_flag varchar2
1432 ,p_object_version_number in out nocopy number
1433 ) IS
1434 BEGIN
1435 if p_mode = 'CORRECTION' then
1436 UPDATE per_all_people_f
1437 SET person_type_id = p_person_type
1438 WHERE person_id = p_ex_apl_rec.person_id
1439 AND effective_start_date = p_ex_apl_rec.effective_start_date
1440 AND effective_end_date = p_ex_apl_rec.effective_end_date;
1441 else
1442 -- the UPDATE scenarios will:
1443 -- a. end current applicant record
1444 -- b. create the ex_applicant record effective on p_ex_apl_date
1445 -- c. insert/update APL record depending on scenario
1446 --
1447 -- a. End APL record
1448 UPDATE per_all_people_f
1449 SET effective_end_date = p_ex_apl_date - 1
1450 WHERE person_id = p_ex_apl_rec.person_id
1451 AND effective_start_date = p_ex_apl_rec.effective_start_date
1452 AND effective_end_date = p_ex_apl_rec.effective_end_date;
1453 --
1454 -- b. Insert EX_APL record as of p_ex_apl_date
1455 Insert_Person_Rec(
1456 p_rec => p_ex_apl_rec
1457 ,p_person_id => p_ex_apl_rec.person_id
1458 ,p_effective_start_date => p_ex_apl_date
1459 ,p_effective_end_date => p_apl_date - 1
1460 ,p_person_type_id => p_person_type
1461 ,p_applicant_number => p_ex_apl_rec.applicant_number
1462 ,p_current_emp_apl_flag => p_current_emp_apl_flag
1463 ,p_current_apl_flag => p_current_apl_flag
1464 ,p_current_employee_flag => p_ex_apl_rec.current_employee_flag
1465 ,p_current_npw_flag => p_ex_apl_rec.current_npw_flag
1466 ,p_object_version_number => p_object_version_number -- BUG4081676
1467 );
1468 -- c. Insert/Update next APL record
1469 if p_mode = 'UPDATE_CHANGE_INSERT' then
1470 -- insert APL record as of p_apl_date
1471 Insert_Person_Rec(
1472 p_rec => p_ex_apl_rec
1473 ,p_person_id => p_ex_apl_rec.person_id
1474 ,p_effective_start_date => p_apl_date
1475 ,p_effective_end_date => p_ex_apl_rec.effective_end_date
1476 ,p_person_type_id => p_ex_apl_rec.person_type_id
1477 ,p_applicant_number => p_ex_apl_rec.applicant_number
1478 ,p_current_emp_apl_flag => p_ex_apl_rec.current_emp_or_apl_flag
1479 ,p_current_apl_flag => p_ex_apl_rec.current_applicant_flag
1480 ,p_current_employee_flag => p_ex_apl_rec.current_employee_flag
1481 ,p_current_npw_flag => p_ex_apl_rec.current_npw_flag
1482 ,p_object_version_number => p_object_version_number -- BUG4081676
1483 );
1484 elsif p_mode = 'UPDATE_OVERRIDE' then
1485 -- insert APL record as of p_apl_date
1486 UPDATE per_all_people_f
1487 SET effective_start_date = p_apl_date
1488 WHERE person_id = p_apl_rec.person_id
1489 AND effective_start_date = p_apl_rec.effective_start_date
1490 AND effective_end_date = p_apl_rec.effective_end_date;
1491 end if;
1492 end if;
1493 END Upd_person;
1494 --
1495 BEGIN
1496 if g_debug then
1497 hr_utility.set_location(' Entering: '||l_proc,10);
1498 end if;
1499 --
1500 open csr_get_person_details (p_person_id, p_ex_apl_effective_date, p_apl_effective_date);
1501 fetch csr_get_person_details into l_ex_per_rec;
1502 --
1503 if csr_get_person_details%FOUND then -- yes current
1504 --
1505 open csr_get_person_type(l_ex_per_rec.person_type_id);
1506 fetch csr_get_person_type into l_system_person_type;
1507 close csr_get_person_type;
1508
1509 get_new_EX_APL_person_type(l_ex_per_rec.business_group_id
1510 ,l_system_person_type
1511 ,l_new_sys_person_type
1512 ,l_new_person_type_id);
1513 --
1514 if hr_general2.is_person_type(
1515 p_person_id => l_ex_per_rec.person_id
1516 ,p_person_type => 'EMP'
1517 ,p_effective_date => l_effective_date) then
1518 l_current_emp_or_apl_flag := 'Y';
1519 else
1520 l_current_emp_or_apl_flag := null;
1521 end if;
1522 l_current_applicant_flag := null;
1523 l_ovn := l_ex_per_rec.object_version_number;
1524 l_new_effective_date := l_ex_per_rec.effective_start_date;
1525 l_per_effective_end_date := l_ex_per_rec.effective_end_date;
1526 --
1527 if l_ex_per_rec.effective_start_date = p_ex_apl_effective_date then
1528 -- correct current: only update person type
1529 close csr_get_person_details;
1530 Upd_person( p_mode => 'CORRECTION'
1531 ,p_ex_apl_rec => l_ex_per_rec
1532 ,p_apl_rec => NULL
1533 ,p_ex_apl_date => p_ex_apl_effective_date
1534 ,p_apl_date => NULL
1535 ,p_person_type => l_new_person_type_id
1536 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
1537 ,p_current_apl_flag => l_current_applicant_flag
1538 ,p_object_version_number => l_ovn -- BUG4081676
1539 );
1540 else
1541 fetch csr_get_person_details into l_apl_per_rec;
1542 if csr_get_person_details%NOTFOUND then -- next not found
1543 --
1544 -- |----APL----> OR
1545 -- |----APL----|---APL--->
1546 -- |----| << the EX_APL period does not expand current APL record
1547 --
1548 -- end date current, DT update insert (ex_apl) before apl date,
1549 -- insert APL on apl date
1550 --
1551 close csr_get_person_details;
1552 Upd_person(p_mode => 'UPDATE_CHANGE_INSERT'
1553 ,p_ex_apl_rec => l_ex_per_rec
1554 ,p_apl_rec => NULL
1555 ,p_ex_apl_date => p_ex_apl_effective_date
1556 ,p_apl_date => p_apl_effective_date
1557 ,p_person_type => l_new_person_type_id
1558 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
1559 ,p_current_apl_flag => l_current_applicant_flag
1560 ,p_object_version_number => l_ovn -- BUG4081676
1561 );
1562
1563 else
1564 -- next found
1565 -- |----APL----|---APL--->
1566 -- |----| << the EX_APL period expands two APL records
1567 --
1568 -- end date current, insert DT update (ex_apl), move start date of next record
1569 --
1570 close csr_get_person_details;
1571 Upd_person(p_mode => 'UPDATE_OVERRIDE'
1572 ,p_ex_apl_rec => l_ex_per_rec
1573 ,p_apl_rec => l_apl_per_rec
1574 ,p_ex_apl_date => p_ex_apl_effective_date
1575 ,p_apl_date => p_apl_effective_date
1576 ,p_person_type => l_new_person_type_id
1577 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
1578 ,p_current_apl_flag => l_current_applicant_flag
1579 ,p_object_version_number => l_ovn -- BUG4081676
1580 );
1581 end if;
1582 --
1583 end if;
1584 --
1585 else
1586 close csr_get_person_details;
1587 -- abnormal: record not found
1588 null;
1589 end if;
1590 -- ---------------------------------------------------------------------- +
1591 -- ------------------------ PTU UPDATES --------------------------------- |
1592 -- ---------------------------------------------------------------------- +
1593 if g_debug then
1594 hr_utility.set_location(l_proc,50);
1595 hr_utility.trace(' ==> person record became EX_APL on '||
1596 to_char(l_new_effective_date));
1597 end if;
1598 --
1599 l_ptu_person_type_id := hr_person_type_usage_info.get_default_person_type_id
1600 (p_business_group_id, 'EX_APL');
1601
1602 open csr_ptu_details(p_person_id, p_ex_apl_effective_date);
1603 fetch csr_ptu_details into l_ptu_rec;
1604 IF csr_ptu_details%FOUND THEN
1605
1606 if l_ptu_rec.system_person_type = 'EX_APL' then
1607 --
1608 -- person is ex_applicant on p_ex_apl_effective_date
1609 --
1610 if g_debug then
1611 hr_utility.set_location(l_proc,60);
1612 end if;
1613
1614 else -- person is APL
1615 if g_debug then
1616 hr_utility.set_location(l_proc,65);
1617 end if;
1618 --
1619 if l_ptu_rec.effective_end_date <> hr_api.g_eot then
1620 hr_per_type_usage_internal.maintain_person_type_usage
1621 (p_effective_date => p_ex_apl_effective_date
1622 ,p_person_id => p_person_id
1623 ,p_person_type_id => l_ptu_person_type_id -- EX_APL record
1624 ,p_datetrack_update_mode => hr_api.g_update_change_insert
1625 );
1626 else
1627 hr_per_type_usage_internal.maintain_person_type_usage
1628 (p_effective_date => p_ex_apl_effective_date
1629 ,p_person_id => p_person_id
1630 ,p_person_type_id => l_ptu_person_type_id -- EX_APL record
1631 ,p_datetrack_update_mode => hr_api.g_update
1632 );
1633 hr_per_type_usage_internal.maintain_person_type_usage
1634 (p_effective_date => p_apl_effective_date
1635 ,p_person_id => p_person_id
1636 ,p_person_type_id => l_ptu_rec.person_type_id -- APL record
1637 ,p_datetrack_update_mode => hr_api.g_update
1638 );
1639 end if;
1640 --
1641 end if;
1642 ELSE -- APL ptu record not found
1643 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
1644 hr_utility.set_message_token('PROCEDURE',l_proc);
1645 hr_utility.set_message_token('STEP',95);
1646 hr_utility.raise_error;
1647 END IF;
1648 if g_debug then
1649 hr_utility.set_location(' Leaving: '||l_proc,1000);
1650 end if;
1651 --
1652 -- Setting OUT parameters
1653 --
1654 p_per_effective_start_date := l_new_effective_date;
1655 p_per_effective_end_date := l_per_effective_end_date;
1656
1657 end Upd_person_EX_APL_and_APL;
1658
1659 -- -------------------------------------------------------------------------- +
1660 -- |--------------------< Update_APL_Assignments >--------------------------- |
1661 -- -------------------------------------------------------------------------- +
1662 PROCEDURE Update_APL_Assignments
1663 (p_business_group_id IN number
1664 ,p_old_application_id IN number
1665 ,p_new_application_id IN number
1666 )
1667 IS
1668 BEGIN
1669 hr_utility.trace('Update APL asg belonging to future applications');
1670 hr_utility.trace('OLD appl id = '||p_old_application_id);
1671 hr_utility.trace('New appl id = '||p_new_application_id);
1672
1673 UPDATE per_all_assignments_f
1674 set application_id = p_new_application_id
1675 where business_group_id = p_business_group_id
1676 and application_id is not null
1677 and application_id = p_old_application_id;
1678
1679 END Update_APL_Assignments;
1680 --
1681 -- -------------------------------------------------------------------------- +
1682 -- |----------------------< create_application >----------------------------- |
1683 -- -------------------------------------------------------------------------- +
1684 PROCEDURE Create_Application
1685 (p_application_id OUT nocopy number
1686 ,p_business_group_id IN number
1687 ,p_person_id IN number
1688 ,p_effective_date IN date
1689 ,p_date_received OUT nocopy date
1690 ,p_object_version_number OUT nocopy number
1691 ,p_appl_override_warning OUT nocopy boolean
1692 ,p_validate_df_flex IN boolean default true --4689836
1693 ) IS
1694
1695 cursor csr_fut_apl(cp_person_id number, cp_effective_date date) is
1696 select application_id, date_received, object_version_number
1697 from per_applications
1698 where person_id = cp_person_id
1699 and date_received > cp_effective_date
1700 order by date_received asc;
1701
1702 cursor csr_current_apl(cp_person_id number, cp_effective_date date) is
1703 select application_id, date_received, object_version_number
1704 from per_applications
1705 where person_id = cp_person_id
1706 and date_received <= cp_effective_date
1707 and nvl(date_end,hr_api.g_eot) >= cp_effective_date;
1708
1709 cursor csr_apl_yesterday(cp_person_id number, cp_effective_date date) is
1710 select application_id, date_received, object_version_number
1711 from per_applications
1712 where person_id = cp_person_id
1713 and date_end = cp_effective_date-1;
1714
1715 l_proc constant varchar2(100) := g_package||
1716 '.create_application';
1717 l_future_apl_id per_applications.application_id%TYPE;
1718 l_fut_apl_date_received per_applications.date_received%TYPE;
1719 l_fut_apl_ovn per_applications.object_version_number%TYPE;
1720
1721 l_current_apl_id per_applications.application_id%TYPE;
1722 l_yesterday_apl_id per_applications.application_id%TYPE;
1723
1724 l_application_id per_applications.application_id%TYPE;
1725 l_date_received per_applications.date_received%TYPE;
1726 l_apl_object_version_number per_applications.object_version_number%TYPE;
1727
1728 l_del_fut_apl_id per_applications.application_id%TYPE;
1729 l_del_fut_apl_date_received per_applications.date_received%TYPE;
1730 l_del_fut_apl_ovn per_applications.object_version_number%TYPE;
1731
1732 l_appl_override_warning boolean;
1733 l_rowcount number;
1734 l_effective_date date;
1735
1736 l_date_received_OUT per_applications.date_received%TYPE;
1737 l_application_id_OUT per_applications.application_id%TYPE;
1738 l_apl_ovn_OUT per_applications.object_version_number%TYPE;
1739
1740 begin
1741 if g_debug then
1742 hr_utility.set_location(' Entering: '||l_proc,10);
1743 end if;
1744 --
1745 l_appl_override_warning := FALSE;
1746 l_rowcount := 0;
1747 --
1748 l_effective_date := trunc(p_effective_date);
1749 --
1750 open csr_fut_apl(p_person_id, l_effective_date);
1751 fetch csr_fut_apl into l_future_apl_id, l_fut_apl_date_received
1752 ,l_fut_apl_ovn;
1753
1754 if csr_fut_apl%notfound then --no future
1755
1756 hr_utility.trace('NO FUTURE');
1757
1758 open csr_current_apl(p_person_id, l_effective_date) ;
1759 fetch csr_current_apl into l_current_apl_id, l_date_received
1760 ,l_apl_object_version_number;
1761
1762 if csr_current_apl%notfound then --no future, no current
1763
1764 hr_utility.trace('NO FUTURE, NO CURRENT');
1765 close csr_current_apl;
1766 open csr_apl_yesterday(p_person_id, l_effective_date);
1767 fetch csr_apl_yesterday into l_yesterday_apl_id, l_date_received
1768 ,l_apl_object_version_number;
1769
1770 if csr_apl_yesterday%notfound then --no future, no current, no yesterday
1771 close csr_apl_yesterday;
1772
1773 --insert brand new application
1774 hr_utility.trace('no future, no current, no yesterday');
1775 hr_utility.trace('Insert brand new application');
1776
1777 per_apl_ins.ins
1778 (p_application_id => l_application_id
1779 ,p_business_group_id => p_business_group_id
1780 ,p_person_id => p_person_id
1781 ,p_date_received => l_effective_date
1782 ,p_object_version_number => l_apl_object_version_number
1783 ,p_effective_date => l_effective_date
1784 ,p_validate_df_flex => false -- 4689836
1785 );
1786
1787 l_date_received_OUT := l_effective_date;
1788 l_application_id_OUT := l_application_id;
1789 l_apl_ovn_OUT := l_apl_object_version_number;
1790
1791 else -- no future, no current, yes yesterday
1792 close csr_apl_yesterday;
1793
1794 hr_utility.trace('no future, no current, yes yesterday');
1795 --
1796 -- set date_end to null where application_id=l_yesterday_apl_id
1797 --
1798 per_apl_upd.upd
1799 (p_application_id => l_yesterday_apl_id
1800 ,p_date_end => null
1801 ,p_object_version_number => l_apl_object_version_number
1802 ,p_effective_date => l_effective_date
1803 );
1804
1805 l_date_received_OUT := l_date_received;
1806 l_application_id_OUT := l_yesterday_apl_id;
1807 l_apl_ovn_OUT := l_apl_object_version_number;
1808
1809 end if;
1810 -- ----------------------------------------
1811 else --no future, yes current
1812 -- ----------------------------------------
1813 hr_utility.trace('no future, yes current');
1814
1815 close csr_current_apl;
1816 --
1817 --set date_end to null where application_id=l_current_apl_id
1818 --
1819 per_apl_upd.upd
1820 (p_application_id => l_current_apl_id
1821 ,p_date_end => null
1822 ,p_object_version_number => l_apl_object_version_number
1823 ,p_effective_date => l_effective_date
1824 );
1825
1826 l_date_received_OUT := l_date_received;
1827 l_application_id_OUT := l_current_apl_id;
1828 l_apl_ovn_OUT := l_apl_object_version_number;
1829
1830 end if;
1831 -- -------------------------------------------------------------------
1832 else --yes future
1833 -- -------------------------------------------------------------------
1834 hr_utility.trace('YES future');
1835
1836 open csr_current_apl(p_person_id, l_effective_date);
1837 fetch csr_current_apl into l_current_apl_id, l_date_received
1838 ,l_apl_object_version_number;
1839
1840 if csr_current_apl%notfound then --yes future, no current
1841 close csr_current_apl;
1842 --
1843 -- delete more future applications, but not the first one (merge)
1844 l_rowcount := 0;
1845 loop
1846 fetch csr_fut_apl into l_del_fut_apl_id, l_del_fut_apl_date_received
1847 ,l_del_fut_apl_ovn ;
1848 exit when csr_fut_apl%notfound;
1849
1850 Update_APL_assignments(p_business_group_id, l_del_fut_apl_id,l_future_apl_id);
1851
1852 per_apl_del.del
1853 (p_application_id => l_del_fut_apl_id
1854 ,p_object_version_number => l_del_fut_apl_ovn
1855 );
1856 l_rowcount := l_rowcount + 1;
1857 end loop;
1858 close csr_fut_apl;
1859
1860 if l_rowcount > 0 then
1861 l_appl_override_warning := TRUE;
1862 end if;
1863
1864 -- set date_received=p_effective_date where application_id=l_future_apl_id;
1865 per_apl_upd.upd
1866 (p_application_id => l_future_apl_id
1867 ,p_date_received => l_effective_date
1868 ,p_date_end => null
1869 ,p_object_version_number => l_fut_apl_ovn
1870 ,p_effective_date => l_effective_date
1871 );
1872
1873 l_date_received_OUT := l_fut_apl_date_received;
1874 l_application_id_OUT := l_future_apl_id;
1875 l_apl_ovn_OUT := l_fut_apl_ovn;
1876 -- ----------------------------------------
1877 else --yes future, yes current
1878 -- ----------------------------------------
1879 hr_utility.trace('yes future, yes current');
1880
1881 close csr_current_apl;
1882 --
1883 -- delete the first future apl we already fetched
1884 --
1885 Update_APL_assignments(p_business_group_id, l_future_apl_id,l_current_apl_id);
1886 --
1887 per_apl_del.del
1888 (p_application_id => l_future_apl_id
1889 ,p_object_version_number => l_fut_apl_ovn
1890 );
1891 --
1892 l_appl_override_warning := TRUE;
1893 --
1894 -- delete more future applications (merge)
1895 --
1896 loop
1897 fetch csr_fut_apl into l_del_fut_apl_id, l_del_fut_apl_date_received
1898 ,l_del_fut_apl_ovn ;
1899
1900 exit when csr_fut_apl%notfound;
1901
1902 Update_APL_assignments(p_business_group_id, l_del_fut_apl_id,l_current_apl_id);
1903
1904 per_apl_del.del
1905 (p_application_id => l_del_fut_apl_id
1906 ,p_object_version_number => l_del_fut_apl_ovn
1907 );
1908 --
1909 end loop;
1910 close csr_fut_apl;
1911 --
1912 -- set date_end to null where application_id=l_current_apl_id
1913 --
1914 per_apl_upd.upd
1915 (p_application_id => l_current_apl_id
1916 ,p_date_received => l_date_received
1917 ,p_date_end => null
1918 ,p_object_version_number => l_apl_object_version_number
1919 ,p_effective_date => l_effective_date
1920 );
1921
1922 l_application_id_OUT := l_current_apl_id;
1923 l_date_received_OUT := l_date_received;
1924 l_apl_ovn_OUT := l_apl_object_version_number;
1925 end if;
1926 --
1927 end if;
1928 --
1929 -- Setting up the OUT parameters
1930 --
1931 p_application_id := l_application_id_OUT;
1932 p_date_received := l_date_received_OUT;
1933 p_object_version_number := l_apl_ovn_OUT;
1934 p_appl_override_warning := l_appl_override_warning;
1935
1936 if g_debug then
1937 hr_utility.set_location(' Leaving: '||l_proc,1000);
1938 end if;
1939
1940 end Create_Application;
1941 --
1942 -- ------------------------------------------------------------------------- +
1943 -- --------------------< override_future_applications >--------------------- |
1944 -- ------------------------------------------------------------------------- +
1945 FUNCTION override_future_applications
1946 (p_person_id IN NUMBER
1947 ,p_effective_date IN DATE
1948 )
1949 RETURN VARCHAR2 IS
1950 --
1951 l_future_apl_id per_applications.application_id%type;
1952 l_current_apl_id per_applications.application_id%type;
1953 l_yesterday_apl_id per_applications.application_id%type;
1954 l_raise_warning VARCHAR2(10);
1955
1956 cursor csr_fut_apl is
1957 select application_id
1958 from per_applications
1959 where person_id = p_person_id
1960 and date_received > p_effective_date
1961 order by date_received asc;
1962
1963 cursor csr_current_apl is
1964 select application_id
1965 from per_applications
1966 where person_id = p_person_id
1967 and date_received < p_effective_date
1968 and nvl(date_end,hr_api.g_eot) >= p_effective_date;
1969
1970 cursor csr_apl_yesterday is
1971 select application_id
1972 from per_applications
1973 where person_id = p_person_id
1974 and date_end = p_effective_date-1;
1975
1976
1977
1978 BEGIN
1979 l_raise_warning := 'N';
1980 open csr_fut_apl;
1981 fetch csr_fut_apl into l_future_apl_id;
1982 if csr_fut_apl%found then
1983 open csr_current_apl;
1984 fetch csr_current_apl into l_current_apl_id;
1985 if csr_current_apl%notfound then --yes future, no current
1986 close csr_current_apl;
1987 fetch csr_fut_apl INTO l_future_apl_id;
1988 IF csr_fut_apl%FOUND then
1989 l_raise_warning := 'Y';
1990 end if;
1991 else --yes future, yes current
1992 close csr_current_apl;
1993 l_raise_warning := 'Y';
1994 END IF;
1995 end if;
1996 close csr_fut_apl;
1997
1998 RETURN l_raise_warning;
1999
2000 END override_future_applications;
2001 --
2002 -- ------------------------------------------------------------------------- +
2003 -- ------------------------< future_apl_asg_exist >------------------------- |
2004 -- ------------------------------------------------------------------------- +
2005 FUNCTION future_apl_asg_exist
2006 (p_person_id IN NUMBER
2007 ,p_effective_date IN DATE
2008 ,p_application_id IN NUMBER
2009 ) RETURN VARCHAR2 IS
2010 --
2011 cursor csr_future_apl_asg is
2012 select 'Y'
2013 from per_all_assignments_f paf
2014 where paf.person_id = p_person_id
2015 and paf.effective_start_date > p_effective_date
2016 and paf.assignment_type = 'A'
2017 and paf.application_id = p_application_id;
2018 --
2019 l_raise_warning varchar2(10);
2020 --
2021
2022 BEGIN
2023 l_raise_warning := 'N';
2024 open csr_future_apl_asg;
2025 fetch csr_future_apl_asg into l_raise_warning;
2026 close csr_future_apl_asg;
2027 l_raise_warning := nvl(l_raise_warning,'N');
2028
2029 RETURN l_raise_warning;
2030
2031 END future_apl_asg_exist;
2032 --
2033 --
2034 -- -------------------------------------------------------------------------- +
2035 -- |--------------------< create_applicant_anytime >------------------------- |
2036 -- -------------------------------------------------------------------------- +
2037 -- This creates an application with default information and transforms
2038 -- an existing person into an applicant.
2039 --
2040 -- To create a new person as an applicant then use the
2041 -- hr_applicant_api.create_applicant() API
2042 --
2043 procedure create_applicant_anytime
2044 (p_effective_date in date
2045 ,p_person_id in number
2046 ,p_applicant_number in out nocopy varchar2
2047 ,p_per_object_version_number in out nocopy number
2048 ,p_vacancy_id in number
2049 ,p_person_type_id in number
2050 ,p_assignment_status_type_id in number
2051 ,p_application_id out nocopy number
2052 ,p_assignment_id out nocopy number
2053 ,p_apl_object_version_number out nocopy number
2054 ,p_asg_object_version_number out nocopy number
2055 ,p_assignment_sequence out nocopy number
2056 ,p_per_effective_start_date out nocopy date
2057 ,p_per_effective_end_date out nocopy DATE
2058 ,p_appl_override_warning OUT NOCOPY boolean
2059 ) is
2060 --
2061 -- declare local variables
2062 --
2063 l_proc constant varchar2(72) := g_package||'create_applicant_anytime';
2064 l_business_group_id per_people_f.business_group_id%type;
2065 l_name_combination_warning boolean;
2066 l_dob_null_warning boolean;
2067 l_orig_hire_warning boolean;
2068 l_organization_id per_business_groups.organization_id%type;
2069 l_legislation_code per_business_groups.legislation_code%type;
2070 l_person_type_id per_people_f.person_type_id%type;
2071 l_application_id per_applications.application_id%type;
2072 l_comment_id per_assignments_f.comment_id%type;
2073 l_assignment_sequence per_assignments_f.assignment_sequence%type;
2074 l_assignment_id per_assignments_f.assignment_id%type;
2075 l_object_version_number per_assignments_f.object_version_number%type;
2076 l_current_applicant_flag per_people_f.current_applicant_flag%type;
2077 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2078 l_current_employee_flag per_people_f.current_employee_flag%type;
2079 l_employee_number per_people_f.employee_number%type;
2080 l_applicant_number per_people_f.applicant_number%TYPE;
2081 l_npw_number per_people_f.npw_number%TYPE;
2082 l_per_object_version_number per_people_f.object_version_number%TYPE;
2083 l_full_name per_people_f.full_name%type;
2084 l_system_person_type per_person_types.system_person_type%type;
2085 l_effective_date date;
2086 l_effective_start_date date;
2087 l_effective_end_date date;
2088 l_default_start_time per_business_groups.default_start_time%type;
2089 l_default_end_time per_business_groups.default_end_time%type;
2090 l_normal_hours number;
2091 l_frequency per_business_groups.frequency%type;
2092 l_recruiter_id per_vacancies.recruiter_id%type;
2093 l_grade_id per_vacancies.grade_id%type;
2094 l_position_id per_vacancies.position_id%type;
2095 l_job_id per_vacancies.job_id%type;
2096 l_location_id per_vacancies.location_id%type;
2097 l_people_group_id per_vacancies.people_group_id%type;
2098 l_vac_organization_id per_vacancies.organization_id%type;
2099 l_vac_business_group_id per_vacancies.business_group_id%type;
2100 l_group_name pay_people_groups.group_name%type;
2101 --
2102 --
2103 l_apl_object_version_number number;
2104 l_apl_date_received DATE;
2105 l_asg_object_version_number number;
2106 l_per_effective_start_date date;
2107 l_per_effective_end_date date;
2108 l_appl_override_warning BOOLEAN;
2109 l_per_party_id per_all_people_f.party_id%TYPE;
2110 l_per_dob per_all_people_f.date_of_birth%TYPE;
2111 l_per_start_date per_all_people_f.effective_start_date%TYPE;
2112 --
2113 --
2114 -- select and validate the person
2115 --
2116 -- now returns person details
2117 --
2118 cursor csr_chk_person_exists is
2119 select ppf.business_group_id
2120 ,ppf.employee_number
2121 ,ppf.npw_number
2122 ,ppf.date_of_birth
2123 ,ppf.party_id
2124 ,ppf.effective_start_date
2125 ,ppt.system_person_type
2126 from per_person_types ppt
2127 ,per_people_f ppf
2128 where ppf.person_id = p_person_id
2129 and ppt.person_type_id = ppf.person_type_id
2130 and ppt.business_group_id + 0 = ppf.business_group_id
2131 and (l_effective_date
2132 between ppf.effective_start_date
2133 and ppf.effective_end_date or ppf.effective_start_date > l_effective_date)
2134 order by ppf.effective_start_date ASC;
2135 --
2136 -- Get organization id for business group.
2137 --
2138 cursor csr_get_organization_id is
2139 select organization_id
2140 ,legislation_code
2141 ,default_start_time
2142 ,default_end_time
2143 ,fnd_number.canonical_to_number(working_hours)
2144 ,frequency
2145 from per_business_groups
2146 where business_group_id = l_business_group_id;
2147 --
2148 -- Get vacancy information.
2149 --
2150 cursor csr_get_vacancy_details is
2151 select recruiter_id
2152 ,grade_id
2153 ,position_id
2154 ,job_id
2155 ,location_id
2156 ,people_group_id
2157 ,organization_id -- added org id to cursor. thayden 7/10.
2158 ,business_group_id -- added business_group_id to cursor lma 7/11
2159 from per_vacancies
2160 where vacancy_id = p_vacancy_id;
2161 --
2162 CURSOR csr_lock_person(cp_person_id number, cp_termination_date date) IS
2163 SELECT null
2164 FROM per_all_people_f
2165 WHERE person_id = cp_person_id
2166 AND (effective_start_date > cp_termination_date
2167 OR
2168 cp_termination_date between effective_start_date
2169 and effective_end_date)
2170 for update nowait;
2171 --
2172 CURSOR csr_lock_ptu(cp_person_id number, cp_termination_date date) IS
2173 SELECT null
2174 FROM per_person_type_usages_f ptu
2175 ,per_person_types ppt
2176 WHERE person_id = cp_person_id
2177 AND (effective_start_date > cp_termination_date
2178 OR
2179 cp_termination_date between effective_start_date
2180 and effective_end_date)
2181 AND ptu.person_type_id = ppt.person_type_id
2182 AND ppt.system_person_type in ('APL','EX_APL')
2183 for update nowait;
2184 -- ------------------------------------------------------------------------ +
2185 -- ------------------------<< BEGIN >>------------------------------------ |
2186 -- ------------------------------------------------------------------------ +
2187 BEGIN
2188 --
2189 if g_debug then
2190 hr_utility.set_location('Entering:'|| l_proc, 5);
2191 end if;
2192 --
2193 -- Truncate p_effective_date
2194 --
2195 l_effective_date := trunc(p_effective_date);
2196 l_person_type_id := p_person_type_id;
2197 --
2198 hr_utility.trace(' l_effective_date = '||to_char(l_effective_date));
2199 --
2200 l_applicant_number := p_applicant_number;
2201 l_per_object_version_number := p_per_object_version_number;
2202 --
2203 -- Validation Logic
2204 --
2205 -- Ensure that the mandatory parameter, p_person_id is not null
2206 --
2207 hr_api.mandatory_arg_error
2208 (p_api_name => l_proc
2209 ,p_argument => 'person id'
2210 ,p_argument_value => p_person_id);
2211 --
2212 if g_debug then
2213 hr_utility.set_location(' Leaving:'||l_proc, 10);
2214 end if;
2215 --
2216 -- Check that this person (p_person_id) exists as of l_effective_date
2217 -- the current person type (per_people_f.person_type_id) has a
2218 -- corresponding system person type.
2219 --
2220 open csr_chk_person_exists;
2221 fetch csr_chk_person_exists into
2222 l_business_group_id
2223 ,l_employee_number
2224 ,l_npw_number
2225 ,l_per_dob
2226 ,l_per_party_id
2227 ,l_per_start_date
2228 ,l_system_person_type;
2229 if csr_chk_person_exists%notfound then
2230 close csr_chk_person_exists;
2231 hr_utility.set_message(800, 'HR_51011_PER_NOT_EXIST_DATE');
2232 hr_utility.raise_error;
2233 end if;
2234 close csr_chk_person_exists;
2235 --
2236 if g_debug then
2237 hr_utility.set_location(l_proc, 15);
2238 end if;
2239 --
2240 per_per_bus.chk_person_type
2241 (p_person_type_id => l_person_type_id,
2242 p_business_group_id => l_business_group_id,
2243 p_expected_sys_type => 'APL');
2244 --
2245 if g_debug then
2246 hr_utility.set_location(l_proc, 20);
2247 end if;
2248 --
2249 -- Get organization id
2250 --
2251 open csr_get_organization_id;
2252 fetch csr_get_organization_id into
2253 l_organization_id
2254 ,l_legislation_code
2255 ,l_default_start_time
2256 ,l_default_end_time
2257 ,l_normal_hours
2258 ,l_frequency;
2259 if csr_get_organization_id%notfound then
2260 close csr_get_organization_id;
2261 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2262 hr_utility.raise_error;
2263 end if;
2264 close csr_get_organization_id;
2265 --
2266 -- Get vacancy details.
2267 --
2268 if p_vacancy_id is not null then
2269 open csr_get_vacancy_details;
2270 fetch csr_get_vacancy_details into
2271 l_recruiter_id
2272 ,l_grade_id
2273 ,l_position_id
2274 ,l_job_id
2275 ,l_location_id
2276 ,l_people_group_id
2277 ,l_vac_organization_id
2278 ,l_vac_business_group_id;
2279 if csr_get_vacancy_details%notfound then
2280 close csr_get_vacancy_details;
2281 hr_utility.set_message(801, 'HR_51001_THE_VAC_NOT_FOUND');
2282 hr_utility.raise_error;
2283 end if;
2284 close csr_get_vacancy_details;
2285 --
2286 if l_vac_organization_id is null then
2287 l_vac_organization_id := l_vac_business_group_id;
2288 end if;
2289 else
2290 l_vac_organization_id := l_business_group_id;
2291 end if;
2292 --
2293 if g_debug then
2294 hr_utility.set_location(l_proc, 30);
2295 end if;
2296 --
2297 -- Validate applicant number
2298 -- Get number if one exists and parameter is NULL
2299 --
2300 hr_applicant_internal.generate_applicant_number
2301 (p_business_group_id => l_business_group_id
2302 ,p_person_id => p_person_id
2303 ,p_effective_date => l_effective_date
2304 ,p_party_id => l_per_party_id
2305 ,p_date_of_birth => l_per_dob
2306 ,p_start_date => l_per_start_date
2307 ,p_applicant_number => l_applicant_number);
2308
2309 if g_debug then
2310 hr_utility.set_location(l_proc, 33);
2311 end if;
2312 -- ------------------------------------------------------------------------ +
2313 -- ----------------------<< MAIN PROCESS >>-------------------------------- |
2314 -- ------------------------------------------------------------------------ +
2315 --
2316 -- Lock person records
2317 open csr_lock_person(p_person_id, l_effective_date);
2318 close csr_lock_person;
2319 -- Lock ptu records
2320 open csr_lock_ptu(p_person_id, l_effective_date);
2321 close csr_lock_ptu;
2322 --
2323 if g_debug then
2324 hr_utility.set_location(l_proc, 40);
2325 end if;
2326 --
2327 -- Update Person and PTU Records:
2328 --
2329 Update_PER_PTU_Records
2330 (p_business_group_id => l_business_group_id
2331 ,p_person_id => p_person_id
2332 ,p_effective_date => l_effective_date
2333 ,p_applicant_number => l_applicant_number
2334 ,P_APL_person_type_id => l_person_type_id
2335 ,p_per_effective_start_date => l_per_effective_start_date
2336 ,p_per_effective_end_date => l_per_effective_end_date
2337 ,p_per_object_version_number => l_per_object_version_number --BUG4081676
2338 );
2339 --
2340 Create_Application
2341 (p_application_id => l_application_id
2342 ,p_business_group_id => l_business_group_id
2343 ,p_person_id => p_person_id
2344 ,p_date_received => l_apl_date_received
2345 ,p_effective_date => l_effective_date
2346 ,p_object_version_number => l_apl_object_version_number
2347 ,p_appl_override_warning => l_appl_override_warning
2348 ,p_validate_df_flex => false --4689836
2349 );
2350 --
2351 if g_debug then
2352 hr_utility.set_location(l_proc, 50);
2353 end if;
2354 --
2355 -- create an applicant assignment
2356 --
2357 hr_assignment_internal.create_apl_asg
2358 (p_effective_date => l_effective_date
2359 ,p_legislation_code => l_legislation_code
2360 ,p_business_group_id => l_business_group_id
2361 ,p_person_id => p_person_id
2362 ,p_assignment_status_type_id => p_assignment_status_type_id
2363 ,p_organization_id => l_vac_organization_id
2364 ,p_application_id => l_application_id
2365 ,p_recruiter_id => l_recruiter_id
2366 ,p_grade_id => l_grade_id
2367 ,p_position_id => l_position_id
2368 ,p_job_id => l_job_id
2369 ,p_location_id => l_location_id
2370 ,p_people_group_id => l_people_group_id
2371 ,p_vacancy_id => p_vacancy_id
2372 ,p_frequency => l_frequency
2373 ,p_manager_flag => 'N'
2374 ,p_normal_hours => l_normal_hours
2375 ,p_time_normal_finish => l_default_end_time
2376 ,p_time_normal_start => l_default_start_time
2377 ,p_assignment_id => l_assignment_id
2378 ,p_object_version_number => l_asg_object_version_number
2379 ,p_effective_start_date => l_effective_start_date
2380 ,p_effective_end_date => l_effective_end_date
2381 ,p_assignment_sequence => l_assignment_sequence
2382 ,p_comment_id => l_comment_id
2383 --
2384 -- START bug# 4610369 added the parameter by risgupta for not to validate
2385 -- DFFs when assignment created internally.
2386 --
2387 ,p_validate_df_flex => false
2388 --
2389 -- END bug# 4610369
2390 --
2391 );
2392
2393 if g_debug then
2394 hr_utility.set_location(l_proc, 60);
2395 end if;
2396 --
2397 -- add to the security list
2398 --
2399 hr_security_internal.add_to_person_list(l_effective_date,l_assignment_id);
2400 -- ------------------------------------------------------------------------ +
2401 -- ---------------------<< END MAIN PROCESS >>----------------------------- |
2402 -- ------------------------------------------------------------------------ +
2403 --
2404 -- Set all output arguments
2405 --
2406 p_application_id := l_application_id;
2407 p_applicant_number := l_applicant_number;
2408 p_assignment_id := l_assignment_id;
2409 p_apl_object_version_number := l_apl_object_version_number;
2410 p_asg_object_version_number := l_asg_object_version_number;
2411 p_assignment_sequence := l_assignment_sequence;
2412 p_per_effective_start_date := l_per_effective_start_date;
2413 p_per_effective_end_date := l_per_effective_end_date;
2414 p_appl_override_warning := l_appl_override_warning;
2415 p_per_object_version_number := l_per_object_version_number; --BUG4081676
2416 --
2417 --
2418 if g_debug then
2419 hr_utility.set_location(' Leaving:'||l_proc, 50);
2420 end if;
2421
2422 end create_applicant_anytime;
2423 --
2424 --
2425 end hr_applicant_internal;