[Home] [Help]
PACKAGE BODY: APPS.HR_APPLICANT_INTERNAL
Source
1 Package Body hr_applicant_internal as
2 /* $Header: peaplbsi.pkb 120.6.12020000.3 2013/05/24 13:39:14 srannama ship $ */
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 -- ER FPT
631 l_person_type_id1 number;
632 l_exists varchar2(1) := 'N';
633 l_datetrack_mode varchar2(30);
634
635 cursor fpt_apl is
636 select date_received,date_end
637 from per_applications
638 where person_id = p_person_id
639 and date_received >= p_effective_date
640 order by date_received;
641
642
643 cursor fpt_ptu_details is
644 select ptu.person_type_id, effective_start_date, effective_end_date
645 from per_person_type_usages_f ptu, per_person_types ppt
646 where person_id = p_person_id
647 and effective_start_date >= p_effective_date
648 and ptu.person_type_id = ppt.person_type_id
649 and system_person_type in ('APL','EX_APL','EX_EMP');
650
651 cursor per_row_exists(cp_effective_start_date date) is
652 select 'Y' from per_all_people_f
653 where person_id = p_person_id
654 and effective_start_date = cp_effective_start_date;
655
656 cursor chk_ptu_exists(cp_effective_start_date date) is
657 select 'Y'
658 from per_person_type_usages_f ptu, per_person_types ppt
659 where person_id = p_person_id
660 and effective_start_date = cp_effective_start_date
661 and ptu.person_type_id = ppt.person_type_id
662 and ppt.system_person_type in ('APL','EX_APL');
663
664 begin
665 if g_debug then
666 hr_utility.set_location(' Entering: '||l_proc,10);
667 end if;
668 --
669 l_effective_date := trunc(p_effective_date);
670 --
671 l_ptu_person_type_id := p_person_type_id;
672 per_per_bus.chk_person_type
673 (p_person_type_id => l_ptu_person_type_id,
674 p_business_group_id => p_business_group_id,
675 p_expected_sys_type => 'EX_APL');
676 --
677 if g_debug then
678 hr_utility.set_location(' Entering: '||l_proc,10);
679 end if;
680 --
681 open csr_get_person_details(p_person_id, l_effective_date);
682 fetch csr_get_person_details into l_person_rec;
683 if csr_get_person_details%FOUND then
684
685 if g_debug then
686 hr_utility.set_location(l_proc,15);
687 end if;
688
689 open csr_get_person_type(l_person_rec.person_type_id);
690 fetch csr_get_person_type into l_system_person_type;
691 close csr_get_person_type;
692
693 get_new_EX_APL_person_type(l_person_rec.business_group_id
694 ,l_system_person_type
695 ,l_new_sys_person_type
696 ,l_new_person_type_id);
697
698 if hr_general2.is_person_type(
699 p_person_id => l_person_rec.person_id
700 ,p_person_type => 'EMP'
701 ,p_effective_date => l_effective_date) then
702 l_current_emp_or_apl_flag := 'Y';
703 else
704 l_current_emp_or_apl_flag := null;
705 end if;
706 l_current_applicant_flag := null;
707 l_ovn := l_person_rec.object_version_number;
708 l_new_effective_date := l_person_rec.effective_start_date;
709 l_per_effective_end_date := l_person_rec.effective_end_date;
710
711 if l_new_sys_person_type = l_system_person_type then
712 --
713 -- person is ex_applicant; do nothing
714 --
715 if g_debug then
716 hr_utility.set_location(l_proc,20);
717 end if;
718 --
719 else
720 --
721 -- update current record to ex_applicant
722 --
723 if l_person_rec.effective_start_date = l_effective_date then
724 --
725 -- Update current record, simulate 'CORRECTION' mode
726 --
727 if g_debug then
728 hr_utility.set_location(l_proc,25);
729 end if;
730 --
731 Update_Person_Rec
732 (p_person_id => l_person_rec.person_id
733 ,p_effective_start_date => l_person_rec.effective_start_date
734 ,p_effective_end_date => l_person_rec.effective_end_date
735 ,p_person_type_id => l_new_person_type_id
736 ,p_applicant_number => l_person_rec.applicant_number
737 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
738 ,p_current_apl_flag => l_current_applicant_flag
739 ,p_object_version_number => l_ovn);
740
741 else
742 --
743 -- DT update: person becomes ex_applicant on effective date
744 --
745 if g_debug then
746 hr_utility.set_location(l_proc,30);
747 end if;
748
749 l_new_effective_date := l_effective_date;
750 l_per_effective_end_date := l_person_rec.effective_end_date;
751 --
752 -- End date current record
753 UPDATE per_all_people_f
754 set effective_end_date = l_effective_date -1
755 where person_id = l_person_rec.person_id
756 and effective_start_date = l_person_rec.effective_start_date
757 and effective_end_date = l_person_rec.effective_end_date;
758
759 -- Create the new DT update using new person type
760 --
761 Insert_Person_Rec
762 (p_rec => l_person_rec
763 ,p_person_id => p_person_id
764 ,p_effective_start_date => l_effective_date
765 ,p_effective_end_date => l_person_rec.effective_end_date
766 ,p_person_type_id => l_new_person_type_id
767 ,p_applicant_number => l_person_rec.applicant_number
768 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
769 ,p_current_apl_flag => l_current_applicant_flag
770 ,p_current_employee_flag => l_person_rec.current_employee_flag
771 ,p_current_npw_flag => l_person_rec.current_npw_flag
772 ,p_object_version_number => l_ovn);
773 end if;
774 end if;
775 --
776 if g_debug then
777 hr_utility.set_location(l_proc,40);
778 end if;
779 --
780 -- process future person records using "CORRECTION" mode
781 --
782 LOOP
783 fetch csr_get_person_details into l_person_rec;
784
785 exit when csr_get_person_details%NOTFOUND;
786
787 open csr_get_person_type(l_person_rec.person_type_id);
788 fetch csr_get_person_type into l_system_person_type;
789 close csr_get_person_type;
790
791 l_ovn := l_person_rec.object_version_number;
792
793 get_new_EX_APL_person_type(l_person_rec.business_group_id
794 , l_system_person_type
795 , l_new_sys_person_type
796 , l_new_person_type_id);
797 if hr_general2.is_person_type
798 (p_person_id => l_person_rec.person_id
799 ,p_person_type => 'EMP'
800 ,p_effective_date => l_person_rec.effective_start_date) then
801 l_current_emp_or_apl_flag := 'Y';
802 else
803 l_current_emp_or_apl_flag := null;
804 end if;
805
806 if l_new_sys_person_type <> l_system_person_type then
807
808 Update_Person_Rec
809 (p_person_id => l_person_rec.person_id
810 ,p_effective_start_date => l_person_rec.effective_start_date
811 ,p_effective_end_date => l_person_rec.effective_end_date
812 ,p_person_type_id => l_new_person_type_id
813 ,p_applicant_number => l_person_rec.applicant_number
814 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
815 ,p_current_apl_flag => l_current_applicant_flag
816 ,p_object_version_number => l_ovn);
817
818 end if; -- person type is different
819
820 END LOOP;
821 if g_debug then
822 hr_utility.set_location(l_proc,45);
823 end if;
824
825 else
826 -- person details not found: ABNORMAL condition
827 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
828 hr_utility.set_message_token('PROCEDURE',l_proc);
829 hr_utility.set_message_token('STEP',80);
830 hr_utility.raise_error;
831 end if;
832 close csr_get_person_details;
833
834
835 -- ---------------------------------------------------------------------- +
836 -- ------------------------ PTU UPDATES --------------------------------- |
837 -- ---------------------------------------------------------------------- +
838 if g_debug then
839 hr_utility.set_location(l_proc,50);
840 hr_utility.trace(' ==> person record became EX_APL on '||
841 to_char(l_new_effective_date));
842 end if;
843 --
844 open csr_ptu_details(p_person_id, l_new_effective_date);
845 fetch csr_ptu_details into l_ptu_rec;
846 if csr_ptu_details%FOUND then
847
848 if l_ptu_rec.system_person_type = 'EX_APL' then
849 --
850 -- person is ex_applicant on new_effective_date
851 --
852 if g_debug then
853 hr_utility.set_location(l_proc,60);
854 end if;
855
856 else -- person is APL
857 if g_debug then
858 hr_utility.set_location(l_proc,65);
859 end if;
860 --
861 if l_ptu_rec.effective_end_date <> hr_api.g_eot then
862 hr_per_type_usage_internal.maintain_person_type_usage
863 (p_effective_date => l_new_effective_date
864 ,p_person_id => p_person_id
865 ,p_person_type_id => l_ptu_person_type_id
866 ,p_datetrack_update_mode => hr_api.g_update_override
867 );
868 else
869 hr_per_type_usage_internal.maintain_person_type_usage
870 (p_effective_date => l_new_effective_date
871 ,p_person_id => p_person_id
872 ,p_person_type_id => l_ptu_person_type_id
873 ,p_datetrack_update_mode => hr_api.g_update
874 );
875 end if;
876 -- ER FPT
877 if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
878 for apl_rec in fpt_apl loop
879 -- updating PTU with the future APL and EX-APL records
880 hr_utility.set_location('Creating APL record on '||apl_rec.date_received,70);
881 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id(p_Business_Group_Id,'APL');
882
883 open chk_ptu_exists(apl_rec.date_received);
884 fetch chk_ptu_exists into l_exists;
885 if chk_ptu_exists%found then
886 l_datetrack_mode := hr_api.g_correction;
887 else
888 l_datetrack_mode := hr_api.g_update;
889 end if;
890 close chk_ptu_exists;
891
892 hr_per_type_usage_internal.maintain_person_type_usage
893 (p_effective_date => apl_rec.date_received
894 ,p_person_id => p_person_id
895 ,p_person_type_id => l_person_type_id1
896 ,p_datetrack_update_mode => l_datetrack_mode);
897
898 if apl_rec.date_end is not null then
899 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id(p_Business_Group_Id,'EX_APL');
900 hr_per_type_usage_internal.maintain_person_type_usage
901 (p_effective_date => apl_rec.date_end + 1
902 ,p_person_id => p_person_id
903 ,p_person_type_id => l_person_type_id1);
904 end if;
905
906 end loop;
907
908 for ptu_rec in fpt_ptu_details loop
909 -- updating per_all_people_f for the future PTU records
910 open csr_get_person_details(p_person_id, ptu_rec.effective_start_date);
911 fetch csr_get_person_details into l_person_rec;
912 close csr_get_person_details;
913
914 -- end dating the current PER record
915 update per_all_people_f
916 set effective_end_date = ptu_rec.effective_start_date - 1
917 where person_id = p_person_id
918 and effective_start_date < ptu_rec.effective_start_date
919 and effective_end_date > ptu_rec.effective_start_date;
920
921 -- person type ID is initially set as APL or EX-APL depending on the PTU record
922 l_person_type_id1 := ptu_rec.person_type_id;
923 l_ovn := l_person_rec.object_version_number;
924 l_current_emp_or_apl_flag := null;
925
926 -- setting the flags if he's person of type - APL or EMP
927 if hr_general2.is_person_type
928 (p_person_id => p_person_id
929 ,p_person_type => 'EMP'
930 ,p_effective_date => ptu_rec.effective_start_date) then
931
932 l_current_employee_flag := 'Y';
933 l_current_emp_or_apl_flag := 'Y';
934 else
935 l_current_employee_flag := null;
936 end if;
937
938 if hr_general2.is_person_type
939 (p_person_id => p_person_id
940 ,p_person_type => 'APL'
941 ,p_effective_date => ptu_rec.effective_start_date) then
942
943 l_current_applicant_flag := 'Y';
944 l_current_emp_or_apl_flag := 'Y';
945 else
946 l_current_applicant_flag := null;
947 end if;
948
949 -- person type ID is modified if he's an EMP or EX-EMP
950 if hr_general2.is_person_type(p_person_id,'EX_EMP',ptu_rec.effective_start_date) then
951 if hr_general2.is_person_type(p_person_id,'APL',ptu_rec.effective_start_date) then
952 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id(l_person_rec.business_group_id,'EX_EMP_APL');
953 elsif hr_general2.is_person_type(p_person_id,'EX_APL',ptu_rec.effective_start_date) then
954 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id(l_person_rec.business_group_id,'EX_EMP');
955 end if;
956
957 elsif hr_general2.is_person_type(p_person_id,'EMP',ptu_rec.effective_start_date) then
958 if hr_general2.is_person_type(p_person_id,'APL',ptu_rec.effective_start_date) then
959 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id(l_person_rec.business_group_id,'EMP_APL');
960 elsif hr_general2.is_person_type(p_person_id,'EX_APL',ptu_rec.effective_start_date) then
961 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id(l_person_rec.business_group_id,'EMP');
962 end if;
963 end if;
964
965 open per_row_exists(ptu_rec.effective_start_date);
966 fetch per_row_exists into l_exists;
967 if per_row_exists%found then
968 -- Updating the record on PER table
969 hr_utility.set_location('Updating person record on '||ptu_rec.effective_start_date,75);
970
971 Update_Person_Rec
972 (p_person_id => l_person_rec.person_id
973 ,p_effective_start_date => ptu_rec.effective_start_date
974 ,p_effective_end_date => l_person_rec.effective_end_date
975 ,p_person_type_id => l_person_type_id1
976 ,p_applicant_number => l_person_rec.applicant_number
977 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
978 ,p_current_apl_flag => l_current_applicant_flag
979 ,p_object_version_number => l_ovn);
980
981 else
982
983 -- Inserting the record into PER table
984 hr_utility.set_location('Inserting person record on '||ptu_rec.effective_start_date,75);
985 Insert_Person_Rec
986 (p_rec => l_person_rec
987 ,p_person_id => p_person_id
988 ,p_effective_start_date => ptu_rec.effective_start_date
989 ,p_effective_end_date => ptu_rec.effective_end_date
990 ,p_person_type_id => l_person_type_id1
991 ,p_applicant_number => l_person_rec.applicant_number
992 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
993 ,p_current_apl_flag => l_current_applicant_flag
994 ,p_current_employee_flag => l_current_employee_flag
995 ,p_current_npw_flag => l_person_rec.current_npw_flag
996 ,p_object_version_number => l_ovn);
997 end if;
998 close per_row_exists;
999
1000 end loop;
1001 end if;
1002 -- ER FPT
1003 --
1004 end if;
1005 ELSE -- APL ptu record not found
1006 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
1007 hr_utility.set_message_token('PROCEDURE',l_proc);
1008 hr_utility.set_message_token('STEP',85);
1009 hr_utility.raise_error;
1010 END IF;
1011 if g_debug then
1012 hr_utility.set_location(' Leaving: '||l_proc,1000);
1013 end if;
1014 --
1015 -- Setting OUT parameters
1016 --
1017 p_per_effective_start_date := l_new_effective_date;
1018 p_per_effective_end_date := l_per_effective_end_date;
1019 --
1020 end Update_PER_PTU_to_EX_APL;
1021 --
1022 --
1023 -- -------------------------------------------------------------------------- +
1024 -- |---------------------< Update_PER_PTU_Records >-------------------------- |
1025 -- -------------------------------------------------------------------------- +
1026 PROCEDURE Update_PER_PTU_Records
1027 (p_business_group_id IN number
1028 ,p_person_id IN number
1029 ,p_effective_date IN date
1030 ,p_applicant_number IN varchar2
1031 ,p_APL_person_type_id IN number
1032 ,p_per_effective_start_date out nocopy date
1033 ,p_per_effective_end_date out nocopy DATE
1034 ,p_per_object_version_number in out nocopy number -- BUG4081676
1035 )
1036 IS
1037 --
1038 cursor csr_get_person_details(cp_person_id number, cp_effective_date date)
1039 IS
1040 select *
1041 from per_all_people_f peo
1042 where person_id = cp_person_id
1043 and (effective_start_date >= cp_effective_date
1044 OR
1045 cp_effective_date between effective_start_date
1046 and effective_end_date)
1047 order by peo.effective_start_date ASC
1048 for update of person_type_id;
1049
1050 cursor csr_get_person_type(cp_person_type_id number) IS
1051 select ppt.system_person_type
1052 from per_person_types ppt
1053 where ppt.person_type_id = cp_person_type_id;
1054
1055 cursor csr_ptu_details(cp_person_id number, cp_effective_date date) is
1056 select ptu.person_type_id, ppt.system_person_type
1057 ,ptu.effective_start_date, ptu.effective_end_date
1058 from per_person_type_usages_f ptu
1059 ,per_person_types ppt
1060 where ptu.person_id = cp_person_id
1061 and (cp_effective_date between ptu.effective_start_date
1062 and ptu.effective_end_date
1063 or
1064 effective_start_date > cp_effective_date)
1065 and ppt.person_type_id = ptu.person_type_id
1066 and ppt.system_person_type in ('APL','EX_APL')
1067 order by effective_start_date ASC;
1068
1069
1070 l_proc constant varchar2(100) := g_package||'Update_PER_PTU_Records';
1071 l_effective_date date;
1072
1073 l_business_group_id per_all_people_f.business_group_id%TYPE;
1074 l_effective_start_date per_all_people_f.effective_start_date%TYPE;
1075 l_effective_end_date per_all_people_f.effective_end_date%TYPE;
1076 l_system_person_type per_person_types.system_person_type%TYPE;
1077 l_ovn per_all_people_f.object_version_number%TYPE;
1078
1079 l_ptu_person_type_id per_person_types.person_type_id%TYPE;
1080 l_new_person_type_id per_person_types.person_type_id%TYPE;
1081 l_new_sys_person_type per_person_types.system_person_type%TYPE;
1082 l_first_person_type_id per_person_types.person_type_id%TYPE;
1083 l_current_person_type per_person_types.person_type_id%TYPE;
1084 l_start_date date;
1085
1086 l_new_effective_date DATE;
1087 --
1088 l_per_effective_start_date date;
1089 l_per_effective_end_date date;
1090 l_name_combination_warning boolean;
1091 l_dob_null_warning boolean;
1092 l_orig_hire_warning boolean;
1093 l_comment_id number;
1094
1095 l_current_applicant_flag per_people_f.current_applicant_flag%type;
1096 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
1097 l_current_employee_flag per_people_f.current_employee_flag%type;
1098 l_employee_number per_people_f.employee_number%type;
1099 l_applicant_number per_people_f.applicant_number%TYPE;
1100 l_npw_number per_people_f.npw_number%TYPE;
1101
1102 l_full_name per_people_f.full_name%type;
1103
1104 l_person_rec csr_get_person_details%ROWTYPE;
1105 l_future_person_rec csr_get_person_details%ROWTYPE;
1106 l_ptu_rec csr_ptu_details%ROWTYPE;
1107
1108 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
1109 l_ptu_ovn per_person_type_usages_f.object_version_number%TYPE;
1110 l_ptu_eff_start_date per_person_type_usages_f.effective_start_date%TYPE;
1111 l_ptu_eff_end_date per_person_type_usages_f.effective_end_date%TYPE;
1112 -- BUG4081676
1113 cursor csr_get_per_ovn is
1114 select object_version_number
1115 from per_all_people_f
1116 where person_id = p_person_id
1117 and effective_start_date = l_new_effective_date
1118 and effective_end_date = l_per_effective_end_date;
1119 --
1120 begin
1121 --
1122 if g_debug then
1123 hr_utility.set_location(' Entering: '||l_proc,10);
1124 end if;
1125 --
1126 l_effective_date := trunc(p_effective_date);
1127 --
1128 l_ptu_person_type_id := p_APL_person_type_id;
1129 per_per_bus.chk_person_type
1130 (p_person_type_id => l_ptu_person_type_id,
1131 p_business_group_id => p_business_group_id,
1132 p_expected_sys_type => 'APL');
1133 --
1134 open csr_get_person_details(p_person_id, l_effective_date);
1135 fetch csr_get_person_details into l_person_rec;
1136 if csr_get_person_details%FOUND then
1137
1138 if g_debug then
1139 hr_utility.set_location(l_proc,15);
1140 end if;
1141 --
1142 open csr_get_person_type(l_person_rec.person_type_id);
1143 fetch csr_get_person_type into l_system_person_type;
1144 close csr_get_person_type;
1145 --
1146 get_new_APL_person_type(l_person_rec.business_group_id
1147 ,l_system_person_type
1148 ,l_new_sys_person_type
1149 ,l_new_person_type_id);
1150 --
1151 if l_person_rec.effective_start_date > l_effective_date then
1152 --
1153 -- person becomes applicant before first created in the system
1154 --
1155 if g_debug then
1156 hr_utility.set_location(l_proc,16);
1157 end if;
1158 --
1159 l_ovn := l_person_rec.object_version_number;
1160
1161 l_new_effective_date := l_effective_date;
1162 l_per_effective_end_date := l_person_rec.effective_start_date - 1;
1163 --
1164 Insert_Person_Rec
1165 (p_rec => l_person_rec
1166 ,p_person_id => p_person_id
1167 ,p_effective_start_date => l_effective_date
1168 ,p_effective_end_date => l_person_rec.effective_start_date - 1
1169 ,p_person_type_id => l_ptu_person_type_id
1170 ,p_applicant_number => p_applicant_number
1171 ,p_current_emp_apl_flag => 'Y'
1172 ,p_current_apl_flag => 'Y'
1173 ,p_current_employee_flag => NULL
1174 ,p_current_npw_flag => NULL
1175 ,p_object_version_number => l_ovn); -- BUG4081676
1176 --
1177 if l_person_rec.applicant_number is null then
1178 l_applicant_number := p_applicant_number;
1179 else
1180 l_applicant_number := l_person_rec.applicant_number;
1181 end if;
1182 if l_system_person_type <> l_new_sys_person_type then
1183 Update_Person_Rec
1184 (p_person_id => l_person_rec.person_id
1185 ,p_effective_start_date => l_person_rec.effective_start_date
1186 ,p_effective_end_date => l_person_rec.effective_end_date
1187 ,p_person_type_id => l_new_person_type_id
1188 ,p_applicant_number => l_applicant_number
1189 ,p_current_emp_apl_flag => 'Y'
1190 ,p_current_apl_flag => 'Y'
1191 ,p_object_version_number => l_ovn); -- BUG4081676
1192 end if;
1193 -- --------------------------------------------------------------------+
1194 else
1195 -- --------------------------------------------------------------------+
1196 l_ovn := l_person_rec.object_version_number;
1197 l_new_effective_date := l_person_rec.effective_start_date;
1198 l_per_effective_end_date := l_person_rec.effective_end_date;
1199 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
1200 if l_new_sys_person_type = l_system_person_type then
1201 --
1202 -- person is applicant; do nothing
1203 --
1204 if g_debug then
1205 hr_utility.set_location(l_proc,20);
1206 end if;
1207 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
1208 else
1209 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
1210 -- update current record to applicant
1211 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1212 if l_person_rec.effective_start_date = l_effective_date then
1213 --
1214 -- Update current record, simulate 'CORRECTION' mode
1215 --
1216 if g_debug then
1217 hr_utility.set_location(l_proc,25);
1218 end if;
1219 --
1220 Update_Person_Rec
1221 (p_person_id => l_person_rec.person_id
1222 ,p_effective_start_date => l_person_rec.effective_start_date
1223 ,p_effective_end_date => l_person_rec.effective_end_date
1224 ,p_person_type_id => l_new_person_type_id
1225 ,p_applicant_number => p_applicant_number
1226 ,p_current_emp_apl_flag => 'Y'
1227 ,p_current_apl_flag => 'Y'
1228 ,p_object_version_number => l_ovn); -- BUG4081676
1229 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1230 else
1231 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1232 -- DT update: person becomes applicant on effective date
1233 --
1234 if g_debug then
1235 hr_utility.set_location(l_proc,30);
1236 end if;
1237
1238 l_new_effective_date := l_effective_date;
1239 l_per_effective_end_date := l_person_rec.effective_end_date;
1240 --
1241 -- End date current record
1242 UPDATE per_all_people_f
1243 set effective_end_date = l_effective_date -1
1244 where person_id = l_person_rec.person_id
1245 and effective_start_date = l_person_rec.effective_start_date
1246 and effective_end_date = l_person_rec.effective_end_date;
1247
1248 -- Create the new DT update using new person type
1249 --
1250 Insert_Person_Rec
1251 (p_rec => l_person_rec
1252 ,p_person_id => p_person_id
1253 ,p_effective_start_date => l_effective_date
1254 ,p_effective_end_date => l_person_rec.effective_end_date
1255 ,p_person_type_id => l_new_person_type_id
1256 ,p_applicant_number => p_applicant_number
1257 ,p_current_emp_apl_flag => 'Y'
1258 ,p_current_apl_flag => 'Y'
1259 ,p_current_employee_flag => l_person_rec.current_employee_flag
1260 ,p_current_npw_flag => l_person_rec.current_npw_flag
1261 ,p_object_version_number => l_ovn); -- BUG4081676
1262
1263 end if; -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++
1264 end if; -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
1265 end if; -- person became applicant before first created?
1266 --
1267 if g_debug then
1268 hr_utility.set_location(l_proc,40);
1269 end if;
1270 --
1271 -- process future person records using "CORRECTION" mode
1272 --
1273 LOOP
1274 fetch csr_get_person_details into l_person_rec;
1275
1276 exit when csr_get_person_details%NOTFOUND;
1277
1278 open csr_get_person_type(l_person_rec.person_type_id);
1279 fetch csr_get_person_type into l_system_person_type;
1280 close csr_get_person_type;
1281
1282 -- l_ovn := l_person_rec.object_version_number;
1283
1284 get_new_APL_person_type(l_person_rec.business_group_id
1285 , l_system_person_type
1286 , l_new_sys_person_type
1287 , l_new_person_type_id);
1288
1289 if l_new_sys_person_type <> l_system_person_type then
1290 if l_person_rec.applicant_number is null then
1291 l_applicant_number := p_applicant_number;
1292 else
1293 l_applicant_number := l_person_rec.applicant_number;
1294 end if;
1295 --
1296 Update_Person_Rec
1297 (p_person_id => l_person_rec.person_id
1298 ,p_effective_start_date => l_person_rec.effective_start_date
1299 ,p_effective_end_date => l_person_rec.effective_end_date
1300 ,p_person_type_id => l_new_person_type_id
1301 ,p_applicant_number => l_applicant_number
1302 ,p_current_emp_apl_flag => 'Y'
1303 ,p_current_apl_flag => 'Y'
1304 ,p_object_version_number => l_ovn); -- BUG4081676
1305
1306 end if; -- person type is different
1307
1308 END LOOP;
1309 if g_debug then
1310 hr_utility.set_location(l_proc,45);
1311 end if;
1312
1313 else
1314 -- person details not found: ABNORMAL condition
1315 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
1316 hr_utility.set_message_token('PROCEDURE',l_proc);
1317 hr_utility.set_message_token('STEP',50);
1318 hr_utility.raise_error;
1319 end if;
1320 close csr_get_person_details;
1321
1322 -- ---------------------------------------------------------------------- +
1323 -- ------------------------ PTU UPDATES --------------------------------- |
1324 -- ---------------------------------------------------------------------- +
1325 if g_debug then
1326 hr_utility.set_location(l_proc,50);
1327 hr_utility.trace(' ==> person record became APL on '||
1328 to_char(l_new_effective_date));
1329 end if;
1330 -- get default APL person type for PTU updates
1331 l_new_person_type_id := l_ptu_person_type_id;
1332
1333 open csr_ptu_details(p_person_id, l_new_effective_date);
1334 fetch csr_ptu_details into l_ptu_rec;
1335 if csr_ptu_details%FOUND then
1336
1337 if l_ptu_rec.effective_start_date > l_new_effective_date then
1338 -- APL is created in the future, so change the start date
1339 -- and cancel first EX_APL + future rows after EX_APL
1340 --
1341 if l_ptu_rec.system_person_type <> 'APL' then
1342 -- person should be an applicant otherwise it is an abnormal condition
1343 close csr_ptu_details;
1344 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
1345 hr_utility.set_message_token('PROCEDURE',l_proc);
1346 hr_utility.set_message_token('STEP',60);
1347 hr_utility.raise_error;
1348 else
1349 --
1350 l_first_person_type_id := l_ptu_rec.person_type_id;
1351
1352 hr_per_type_usage_internal.change_hire_date_ptu
1353 (p_date_start => l_new_effective_date
1354 ,p_old_date_start => l_ptu_rec.effective_start_date
1355 ,p_person_id => p_person_id
1356 ,p_system_person_type => l_ptu_rec.system_person_type
1357 );
1358 l_start_date := l_ptu_rec.effective_start_date;
1359 Loop
1360 fetch csr_ptu_details into l_ptu_rec;
1361 exit when csr_ptu_details%NOTFOUND;
1362
1363 if l_ptu_rec.system_person_type = 'EX_APL' then
1364 hr_per_type_usage_internal.maintain_person_type_usage
1365 (p_effective_date => l_start_date
1366 ,p_person_id => p_person_id
1367 ,p_person_type_id => l_first_person_type_id
1368 ,p_datetrack_delete_mode => hr_api.g_future_change
1369 );
1370 exit;
1371 else
1372 l_start_date := l_ptu_rec.effective_start_date;
1373 l_first_person_type_id := l_ptu_rec.person_type_id;
1374 end if;
1375 end loop;
1376 close csr_ptu_details;
1377
1378 end if; -- person is APL
1379 --
1380 else -- start_date <= new effective date
1381 --
1382 if l_ptu_rec.system_person_type = 'APL' then
1383 --
1384 -- person is applicant on new_effective_date
1385 --
1386 if g_debug then
1387 hr_utility.set_location(l_proc,60);
1388 end if;
1389 l_first_person_type_id := l_ptu_rec.person_type_id;
1390 --
1391 -- check whether future changes exist, if yes delete otherwise do nothing
1392 --
1393 l_start_date := l_ptu_rec.effective_start_date;
1394 l_current_person_type := l_ptu_rec.person_type_id;
1395 Loop
1396 fetch csr_ptu_details into l_ptu_rec;
1397 exit when csr_ptu_details%NOTFOUND;
1398
1399 if l_ptu_rec.system_person_type = 'EX_APL' then
1400 hr_per_type_usage_internal.maintain_person_type_usage -- 3962781
1401 (p_effective_date => l_start_date
1402 ,p_person_id => p_person_id
1403 ,p_person_type_id => l_current_person_type
1404 ,p_datetrack_delete_mode => hr_api.g_future_change
1405 );
1406 --
1407 exit;
1408 else
1409 l_start_date := l_ptu_rec.effective_start_date;
1410 l_current_person_type := l_ptu_rec.person_type_id;
1411 end if;
1412 end loop;
1413 close csr_ptu_details;
1414 --
1415 else -- person is EX_APL
1416 if g_debug then
1417 hr_utility.set_location(l_proc,65);
1418 end if;
1419 --
1420 if l_ptu_rec.effective_start_date = l_new_effective_date then
1421 close csr_ptu_details;
1422 open csr_ptu_details(p_person_id, l_new_effective_date -1);
1423 fetch csr_ptu_details into l_ptu_rec;
1424 close csr_ptu_details;
1425 --
1426 hr_per_type_usage_internal.maintain_person_type_usage -- 3962781
1427 (p_effective_date => l_new_effective_date -1
1428 ,p_person_id => p_person_id
1429 ,p_person_type_id => l_ptu_rec.person_type_id --l_new_person_type_id
1430 ,p_datetrack_delete_mode => hr_api.g_future_change
1431 );
1432 hr_per_type_usage_internal.maintain_person_type_usage
1433 (p_effective_date => l_new_effective_date
1434 ,p_person_id => p_person_id
1435 ,p_person_type_id => l_new_person_type_id
1436 ,p_datetrack_update_mode => hr_api.g_update
1437 );
1438 else
1439 if g_debug then
1440 hr_utility.set_location(l_proc,75);
1441 end if;
1442 close csr_ptu_details;
1443 if l_ptu_rec.effective_end_date <> hr_api.g_eot then
1444 hr_per_type_usage_internal.maintain_person_type_usage
1445 (p_effective_date => l_new_effective_date
1446 ,p_person_id => p_person_id
1447 ,p_person_type_id => l_new_person_type_id
1448 ,p_datetrack_update_mode => hr_api.g_update_override
1449 );
1450 else
1451 hr_per_type_usage_internal.maintain_person_type_usage
1452 (p_effective_date => l_new_effective_date
1453 ,p_person_id => p_person_id
1454 ,p_person_type_id => l_new_person_type_id
1455 ,p_datetrack_update_mode => hr_api.g_update
1456 );
1457 end if;
1458 end if;
1459 --
1460 end if;
1461 end if;
1462 ELSE -- APL ptu record not found
1463 ---
1464 -- person needs to be transformed into applicant on effective_date
1465 --
1466 if g_debug then
1467 hr_utility.set_location(l_proc,80);
1468 end if;
1469 hr_per_type_usage_internal.create_person_type_usage
1470 (p_person_id => p_person_id
1471 ,p_person_type_id => l_new_person_type_id
1472 ,p_effective_date => l_new_effective_date
1473 ,p_person_type_usage_id => l_person_type_usage_id
1474 ,p_object_version_number => l_ptu_ovn
1475 ,p_effective_start_date => l_ptu_eff_start_date
1476 ,p_effective_end_date => l_ptu_eff_end_date
1477 );
1478
1479 END IF;
1480
1481 -- Get the person's object version number BUG4081676
1482 open csr_get_per_ovn;
1483 fetch csr_get_per_ovn into l_ovn;
1484 close csr_get_per_ovn;
1485
1486 if g_debug then
1487 hr_utility.trace(' l_ovn : '||l_ovn);
1488 hr_utility.set_location(' Leaving: '||l_proc,1000);
1489 end if;
1490 --
1491 -- Setting OUT parameters
1492 --
1493 p_per_effective_start_date := l_new_effective_date;
1494 p_per_effective_end_date := l_per_effective_end_date;
1495 p_per_object_version_number := l_ovn; -- BUG4081676
1496
1497 end Update_PER_PTU_Records;
1498 --
1499 -- -------------------------------------------------------------------------- +
1500 -- |-------------------< Upd_person_EX_APL_and_APL >------------------------- |
1501 -- -------------------------------------------------------------------------- +
1502 PROCEDURE Upd_person_EX_APL_and_APL
1503 (p_business_group_id IN number
1504 ,p_person_id IN number
1505 ,p_ex_apl_effective_date IN date -- date person becomes EX_APL
1506 ,p_apl_effective_date IN date -- date person becomes APL
1507 ,p_per_effective_start_date out nocopy date
1508 ,p_per_effective_end_date out nocopy DATE
1509 )
1510 IS
1511 --
1512 cursor csr_get_person_details(cp_person_id number, cp_ex_apl_date date, cp_apl_date date) is
1513 select *
1514 from per_all_people_f peo
1515 where person_id = cp_person_id
1516 and (cp_ex_apl_date between effective_start_date
1517 and effective_end_date -- becomes ex-apl on this date
1518 or cp_apl_date between effective_start_date
1519 and effective_end_date -- is apl on this date
1520 )
1521 order by peo.effective_start_date ASC;
1522 --
1523 cursor csr_get_person_type(cp_person_type_id number) IS
1524 select ppt.system_person_type
1525 from per_person_types ppt
1526 where ppt.person_type_id = cp_person_type_id;
1527
1528 cursor csr_ptu_details(cp_person_id number, cp_effective_date date) is
1529 select ptu.person_type_id, ppt.system_person_type
1530 ,ptu.effective_start_date, ptu.effective_end_date
1531 from per_person_type_usages_f ptu
1532 ,per_person_types ppt
1533 where ptu.person_id = cp_person_id
1534 and cp_effective_date between ptu.effective_start_date
1535 and ptu.effective_end_date
1536 and ppt.person_type_id = ptu.person_type_id
1537 and ppt.system_person_type in ('APL','EX_APL')
1538 order by effective_start_date ASC;
1539 --
1540 l_proc constant varchar2(100) := g_package||'Upd_person_EX_APL_and_APL';
1541 l_effective_date date;
1542
1543 l_business_group_id per_all_people_f.business_group_id%TYPE;
1544 l_effective_start_date per_all_people_f.effective_start_date%TYPE;
1545 l_effective_end_date per_all_people_f.effective_end_date%TYPE;
1546 l_system_person_type per_person_types.system_person_type%TYPE;
1547 l_ovn per_all_people_f.object_version_number%TYPE;
1548
1549 l_new_person_type_id per_person_types.person_type_id%TYPE;
1550 l_ptu_person_type_id per_person_types.person_type_id%TYPE;
1551 l_new_sys_person_type per_person_types.system_person_type%TYPE;
1552
1553 l_new_effective_date DATE;
1554 --
1555 l_per_effective_start_date date;
1556 l_per_effective_end_date date;
1557 l_name_combination_warning boolean;
1558 l_dob_null_warning boolean;
1559 l_orig_hire_warning boolean;
1560 l_comment_id number;
1561
1562 l_current_applicant_flag per_people_f.current_applicant_flag%type;
1563 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
1564 l_current_employee_flag per_people_f.current_employee_flag%type;
1565 l_employee_number per_people_f.employee_number%type;
1566 l_applicant_number per_people_f.applicant_number%TYPE;
1567 l_npw_number per_people_f.npw_number%TYPE;
1568
1569 l_full_name per_people_f.full_name%type;
1570
1571 l_person_rec csr_get_person_details%ROWTYPE;
1572 l_future_person_rec csr_get_person_details%ROWTYPE;
1573 l_ptu_rec csr_ptu_details%ROWTYPE;
1574
1575 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
1576 l_ptu_ovn per_person_type_usages_f.object_version_number%TYPE;
1577 l_ptu_eff_start_date per_person_type_usages_f.effective_start_date%TYPE;
1578 l_ptu_eff_end_date per_person_type_usages_f.effective_end_date%TYPE;
1579 --
1580 l_ex_per_rec csr_get_person_details%ROWTYPE;
1581 l_apl_per_rec csr_get_person_details%ROWTYPE;
1582 --
1583 PROCEDURE Upd_person
1584 (p_mode varchar2
1585 ,p_ex_apl_rec csr_get_person_details%ROWTYPE
1586 ,p_apl_rec csr_get_person_details%ROWTYPE
1587 ,p_ex_apl_date date
1588 ,p_apl_date date
1589 ,p_person_type number
1590 ,p_current_emp_apl_flag varchar2
1591 ,p_current_apl_flag varchar2
1592 ,p_object_version_number in out nocopy number
1593 ) IS
1594 BEGIN
1595 if p_mode = 'CORRECTION' then
1596 UPDATE per_all_people_f
1597 SET person_type_id = p_person_type
1598 WHERE person_id = p_ex_apl_rec.person_id
1599 AND effective_start_date = p_ex_apl_rec.effective_start_date
1600 AND effective_end_date = p_ex_apl_rec.effective_end_date;
1601 else
1602 -- the UPDATE scenarios will:
1603 -- a. end current applicant record
1604 -- b. create the ex_applicant record effective on p_ex_apl_date
1605 -- c. insert/update APL record depending on scenario
1606 --
1607 -- a. End APL record
1608 UPDATE per_all_people_f
1609 SET effective_end_date = p_ex_apl_date - 1
1610 WHERE person_id = p_ex_apl_rec.person_id
1611 AND effective_start_date = p_ex_apl_rec.effective_start_date
1612 AND effective_end_date = p_ex_apl_rec.effective_end_date;
1613 --
1614 -- b. Insert EX_APL record as of p_ex_apl_date
1615 Insert_Person_Rec(
1616 p_rec => p_ex_apl_rec
1617 ,p_person_id => p_ex_apl_rec.person_id
1618 ,p_effective_start_date => p_ex_apl_date
1619 ,p_effective_end_date => p_apl_date - 1
1620 ,p_person_type_id => p_person_type
1621 ,p_applicant_number => p_ex_apl_rec.applicant_number
1622 ,p_current_emp_apl_flag => p_current_emp_apl_flag
1623 ,p_current_apl_flag => p_current_apl_flag
1624 ,p_current_employee_flag => p_ex_apl_rec.current_employee_flag
1625 ,p_current_npw_flag => p_ex_apl_rec.current_npw_flag
1626 ,p_object_version_number => p_object_version_number -- BUG4081676
1627 );
1628 -- c. Insert/Update next APL record
1629 if p_mode = 'UPDATE_CHANGE_INSERT' then
1630 -- insert APL record as of p_apl_date
1631 Insert_Person_Rec(
1632 p_rec => p_ex_apl_rec
1633 ,p_person_id => p_ex_apl_rec.person_id
1634 ,p_effective_start_date => p_apl_date
1635 ,p_effective_end_date => p_ex_apl_rec.effective_end_date
1636 ,p_person_type_id => p_ex_apl_rec.person_type_id
1637 ,p_applicant_number => p_ex_apl_rec.applicant_number
1638 ,p_current_emp_apl_flag => p_ex_apl_rec.current_emp_or_apl_flag
1639 ,p_current_apl_flag => p_ex_apl_rec.current_applicant_flag
1640 ,p_current_employee_flag => p_ex_apl_rec.current_employee_flag
1641 ,p_current_npw_flag => p_ex_apl_rec.current_npw_flag
1642 ,p_object_version_number => p_object_version_number -- BUG4081676
1643 );
1644 elsif p_mode = 'UPDATE_OVERRIDE' then
1645 -- insert APL record as of p_apl_date
1646 UPDATE per_all_people_f
1647 SET effective_start_date = p_apl_date
1648 WHERE person_id = p_apl_rec.person_id
1649 AND effective_start_date = p_apl_rec.effective_start_date
1650 AND effective_end_date = p_apl_rec.effective_end_date;
1651 end if;
1652 end if;
1653 END Upd_person;
1654 --
1655 BEGIN
1656 if g_debug then
1657 hr_utility.set_location(' Entering: '||l_proc,10);
1658 end if;
1659 --
1660 open csr_get_person_details (p_person_id, p_ex_apl_effective_date, p_apl_effective_date);
1661 fetch csr_get_person_details into l_ex_per_rec;
1662 --
1663 if csr_get_person_details%FOUND then -- yes current
1664 --
1665 open csr_get_person_type(l_ex_per_rec.person_type_id);
1666 fetch csr_get_person_type into l_system_person_type;
1667 close csr_get_person_type;
1668
1669 get_new_EX_APL_person_type(l_ex_per_rec.business_group_id
1670 ,l_system_person_type
1671 ,l_new_sys_person_type
1672 ,l_new_person_type_id);
1673 --
1674 if hr_general2.is_person_type(
1675 p_person_id => l_ex_per_rec.person_id
1676 ,p_person_type => 'EMP'
1677 ,p_effective_date => l_effective_date) then
1678 l_current_emp_or_apl_flag := 'Y';
1679 else
1680 l_current_emp_or_apl_flag := null;
1681 end if;
1682 l_current_applicant_flag := null;
1683 l_ovn := l_ex_per_rec.object_version_number;
1684 l_new_effective_date := l_ex_per_rec.effective_start_date;
1685 l_per_effective_end_date := l_ex_per_rec.effective_end_date;
1686 --
1687 if l_ex_per_rec.effective_start_date = p_ex_apl_effective_date then
1688 -- correct current: only update person type
1689 close csr_get_person_details;
1690 Upd_person( p_mode => 'CORRECTION'
1691 ,p_ex_apl_rec => l_ex_per_rec
1692 ,p_apl_rec => NULL
1693 ,p_ex_apl_date => p_ex_apl_effective_date
1694 ,p_apl_date => NULL
1695 ,p_person_type => l_new_person_type_id
1696 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
1697 ,p_current_apl_flag => l_current_applicant_flag
1698 ,p_object_version_number => l_ovn -- BUG4081676
1699 );
1700 else
1701 fetch csr_get_person_details into l_apl_per_rec;
1702 if csr_get_person_details%NOTFOUND then -- next not found
1703 --
1704 -- |----APL----> OR
1705 -- |----APL----|---APL--->
1706 -- |----| << the EX_APL period does not expand current APL record
1707 --
1708 -- end date current, DT update insert (ex_apl) before apl date,
1709 -- insert APL on apl date
1710 --
1711 close csr_get_person_details;
1712 Upd_person(p_mode => 'UPDATE_CHANGE_INSERT'
1713 ,p_ex_apl_rec => l_ex_per_rec
1714 ,p_apl_rec => NULL
1715 ,p_ex_apl_date => p_ex_apl_effective_date
1716 ,p_apl_date => p_apl_effective_date
1717 ,p_person_type => l_new_person_type_id
1718 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
1719 ,p_current_apl_flag => l_current_applicant_flag
1720 ,p_object_version_number => l_ovn -- BUG4081676
1721 );
1722
1723 else
1724 -- next found
1725 -- |----APL----|---APL--->
1726 -- |----| << the EX_APL period expands two APL records
1727 --
1728 -- end date current, insert DT update (ex_apl), move start date of next record
1729 --
1730 close csr_get_person_details;
1731 Upd_person(p_mode => 'UPDATE_OVERRIDE'
1732 ,p_ex_apl_rec => l_ex_per_rec
1733 ,p_apl_rec => l_apl_per_rec
1734 ,p_ex_apl_date => p_ex_apl_effective_date
1735 ,p_apl_date => p_apl_effective_date
1736 ,p_person_type => l_new_person_type_id
1737 ,p_current_emp_apl_flag => l_current_emp_or_apl_flag
1738 ,p_current_apl_flag => l_current_applicant_flag
1739 ,p_object_version_number => l_ovn -- BUG4081676
1740 );
1741 end if;
1742 --
1743 end if;
1744 --
1745 else
1746 close csr_get_person_details;
1747 -- abnormal: record not found
1748 null;
1749 end if;
1750 -- ---------------------------------------------------------------------- +
1751 -- ------------------------ PTU UPDATES --------------------------------- |
1752 -- ---------------------------------------------------------------------- +
1753 if g_debug then
1754 hr_utility.set_location(l_proc,50);
1755 hr_utility.trace(' ==> person record became EX_APL on '||
1756 to_char(l_new_effective_date));
1757 end if;
1758 --
1759 l_ptu_person_type_id := hr_person_type_usage_info.get_default_person_type_id
1760 (p_business_group_id, 'EX_APL');
1761
1762 open csr_ptu_details(p_person_id, p_ex_apl_effective_date);
1763 fetch csr_ptu_details into l_ptu_rec;
1764 IF csr_ptu_details%FOUND THEN
1765
1766 if l_ptu_rec.system_person_type = 'EX_APL' then
1767 --
1768 -- person is ex_applicant on p_ex_apl_effective_date
1769 --
1770 if g_debug then
1771 hr_utility.set_location(l_proc,60);
1772 end if;
1773
1774 else -- person is APL
1775 if g_debug then
1776 hr_utility.set_location(l_proc,65);
1777 end if;
1778 --
1779 if l_ptu_rec.effective_end_date <> hr_api.g_eot then
1780 hr_per_type_usage_internal.maintain_person_type_usage
1781 (p_effective_date => p_ex_apl_effective_date
1782 ,p_person_id => p_person_id
1783 ,p_person_type_id => l_ptu_person_type_id -- EX_APL record
1784 ,p_datetrack_update_mode => hr_api.g_update_change_insert
1785 );
1786 else
1787 hr_per_type_usage_internal.maintain_person_type_usage
1788 (p_effective_date => p_ex_apl_effective_date
1789 ,p_person_id => p_person_id
1790 ,p_person_type_id => l_ptu_person_type_id -- EX_APL record
1791 ,p_datetrack_update_mode => hr_api.g_update
1792 );
1793 hr_per_type_usage_internal.maintain_person_type_usage
1794 (p_effective_date => p_apl_effective_date
1795 ,p_person_id => p_person_id
1796 ,p_person_type_id => l_ptu_rec.person_type_id -- APL record
1797 ,p_datetrack_update_mode => hr_api.g_update
1798 );
1799 end if;
1800 --
1801 end if;
1802 ELSE -- APL ptu record not found
1803 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
1804 hr_utility.set_message_token('PROCEDURE',l_proc);
1805 hr_utility.set_message_token('STEP',95);
1806 hr_utility.raise_error;
1807 END IF;
1808 if g_debug then
1809 hr_utility.set_location(' Leaving: '||l_proc,1000);
1810 end if;
1811 --
1812 -- Setting OUT parameters
1813 --
1814 p_per_effective_start_date := l_new_effective_date;
1815 p_per_effective_end_date := l_per_effective_end_date;
1816
1817 end Upd_person_EX_APL_and_APL;
1818
1819 -- -------------------------------------------------------------------------- +
1820 -- |--------------------< Update_APL_Assignments >--------------------------- |
1821 -- -------------------------------------------------------------------------- +
1822 PROCEDURE Update_APL_Assignments
1823 (p_business_group_id IN number
1824 ,p_old_application_id IN number
1825 ,p_new_application_id IN number
1826 )
1827 IS
1828 BEGIN
1829 hr_utility.trace('Update APL asg belonging to future applications');
1830 hr_utility.trace('OLD appl id = '||p_old_application_id);
1831 hr_utility.trace('New appl id = '||p_new_application_id);
1832
1833 UPDATE per_all_assignments_f
1834 set application_id = p_new_application_id
1835 where business_group_id = p_business_group_id
1836 and application_id is not null
1837 and application_id = p_old_application_id;
1838
1839 END Update_APL_Assignments;
1840 --
1841 -- -------------------------------------------------------------------------- +
1842 -- |----------------------< create_application >----------------------------- |
1843 -- -------------------------------------------------------------------------- +
1844 PROCEDURE Create_Application
1845 (p_application_id OUT nocopy number
1846 ,p_business_group_id IN number
1847 ,p_person_id IN number
1848 ,p_effective_date IN date
1849 ,p_date_received OUT nocopy date
1850 ,p_object_version_number OUT nocopy number
1851 ,p_appl_override_warning OUT nocopy boolean
1852 ,p_validate_df_flex IN boolean default true --4689836
1853 ) IS
1854
1855 cursor csr_fut_apl(cp_person_id number, cp_effective_date date) is
1856 select application_id, date_received, object_version_number
1857 from per_applications
1858 where person_id = cp_person_id
1859 and date_received > cp_effective_date
1860 order by date_received asc;
1861
1862 cursor csr_current_apl(cp_person_id number, cp_effective_date date) is
1863 select application_id, date_received, object_version_number
1864 from per_applications
1865 where person_id = cp_person_id
1866 and date_received <= cp_effective_date
1867 and nvl(date_end,hr_api.g_eot) >= cp_effective_date;
1868
1869 cursor csr_apl_yesterday(cp_person_id number, cp_effective_date date) is
1870 select application_id, date_received, object_version_number
1871 from per_applications
1872 where person_id = cp_person_id
1873 and date_end = cp_effective_date-1;
1874
1875 -- start changes for bug 8337406
1876 cursor csr_chk_EMP_or_CWK(cp_person_id number, cp_effective_date date) is
1877 select 1
1878 from per_person_types ppt, per_person_type_usages_f ptu
1879 where ptu.person_type_id = ppt.person_type_id
1880 and ppt.business_group_id = p_business_group_id
1881 AND ptu.person_id = p_person_id
1882 and ppt.system_person_type in ('EMP','CWK')
1883 and ptu.effective_start_date = cp_effective_date;
1884
1885 l_dummy number;
1886 -- start changes for bug 8337406
1887
1888 l_proc constant varchar2(100) := g_package||
1889 '.create_application';
1890 l_future_apl_id per_applications.application_id%TYPE;
1891 l_fut_apl_date_received per_applications.date_received%TYPE;
1892 l_fut_apl_ovn per_applications.object_version_number%TYPE;
1893
1894 l_current_apl_id per_applications.application_id%TYPE;
1895 l_yesterday_apl_id per_applications.application_id%TYPE;
1896
1897 l_application_id per_applications.application_id%TYPE;
1898 l_date_received per_applications.date_received%TYPE;
1899 l_apl_object_version_number per_applications.object_version_number%TYPE;
1900
1901 l_del_fut_apl_id per_applications.application_id%TYPE;
1902 l_del_fut_apl_date_received per_applications.date_received%TYPE;
1903 l_del_fut_apl_ovn per_applications.object_version_number%TYPE;
1904
1905 l_appl_override_warning boolean;
1906 l_rowcount number;
1907 l_effective_date date;
1908
1909 l_date_received_OUT per_applications.date_received%TYPE;
1910 l_application_id_OUT per_applications.application_id%TYPE;
1911 l_apl_ovn_OUT per_applications.object_version_number%TYPE;
1912
1913 begin
1914 if g_debug then
1915 hr_utility.set_location(' Entering: '||l_proc,10);
1916 end if;
1917 --
1918 l_appl_override_warning := FALSE;
1919 l_rowcount := 0;
1920 --
1921 l_effective_date := trunc(p_effective_date);
1922 --
1923 open csr_fut_apl(p_person_id, l_effective_date);
1924 fetch csr_fut_apl into l_future_apl_id, l_fut_apl_date_received
1925 ,l_fut_apl_ovn;
1926
1927 if csr_fut_apl%notfound then --no future
1928
1929 hr_utility.trace('NO FUTURE');
1930
1931 open csr_current_apl(p_person_id, l_effective_date) ;
1932 fetch csr_current_apl into l_current_apl_id, l_date_received
1933 ,l_apl_object_version_number;
1934
1935 if csr_current_apl%notfound then --no future, no current
1936
1937 hr_utility.trace('NO FUTURE, NO CURRENT');
1938 close csr_current_apl;
1939 open csr_apl_yesterday(p_person_id, l_effective_date);
1940 fetch csr_apl_yesterday into l_yesterday_apl_id, l_date_received
1941 ,l_apl_object_version_number;
1942
1943 if csr_apl_yesterday%notfound then --no future, no current, no yesterday
1944 close csr_apl_yesterday;
1945
1946 --insert brand new application
1947 hr_utility.trace('no future, no current, no yesterday');
1948 hr_utility.trace('Insert brand new application');
1949
1950 per_apl_ins.ins
1951 (p_application_id => l_application_id
1952 ,p_business_group_id => p_business_group_id
1953 ,p_person_id => p_person_id
1954 ,p_date_received => l_effective_date
1955 ,p_object_version_number => l_apl_object_version_number
1956 ,p_effective_date => l_effective_date
1957 ,p_validate_df_flex => false -- 4689836
1958 );
1959
1960 l_date_received_OUT := l_effective_date;
1961 l_application_id_OUT := l_application_id;
1962 l_apl_ovn_OUT := l_apl_object_version_number;
1963
1964 else -- no future, no current, yes yesterday
1965
1966 close csr_apl_yesterday;
1967
1968 -- start changes for bug 8337406
1969 -- in case if a person becomes a EMP or CWK on the same day then
1970 -- system will create a new application.
1971
1972 open csr_chk_EMP_or_CWK(p_person_id, l_effective_date);
1973 fetch csr_chk_EMP_or_CWK into l_dummy;
1974 if csr_chk_EMP_or_CWK%found then
1975
1976 hr_utility.trace('EMP or CWK on the same day');
1977 hr_utility.trace('Insert new application');
1978
1979 CLOSE csr_chk_EMP_or_CWK;
1980
1981 per_apl_ins.ins
1982 (p_application_id => l_application_id
1983 ,p_business_group_id => p_business_group_id
1984 ,p_person_id => p_person_id
1985 ,p_date_received => l_effective_date
1986 ,p_object_version_number => l_apl_object_version_number
1987 ,p_effective_date => l_effective_date
1988 ,p_validate_df_flex => false
1989 );
1990
1991 l_date_received_OUT := l_effective_date;
1992 l_application_id_OUT := l_application_id;
1993 l_apl_ovn_OUT := l_apl_object_version_number;
1994
1995 else
1996
1997 hr_utility.trace('No EMP or CWK on the same');
1998 CLOSE csr_chk_EMP_or_CWK;
1999 -- end changes for bug 8337406
2000
2001 --
2002 -- set date_end to null where application_id=l_yesterday_apl_id
2003 --
2004 per_apl_upd.upd
2005 (p_application_id => l_yesterday_apl_id
2006 ,p_date_end => null
2007 --bug 4369122 starts here
2008 ,p_termination_reason =>null
2009 --bug 4369122 ends here
2010 ,p_object_version_number => l_apl_object_version_number
2011 ,p_effective_date => l_effective_date
2012 );
2013
2014 l_date_received_OUT := l_date_received;
2015 l_application_id_OUT := l_yesterday_apl_id;
2016 l_apl_ovn_OUT := l_apl_object_version_number;
2017 END IF;
2018
2019 end if; -- added for bug 8337406
2020 -- ----------------------------------------
2021 else --no future, yes current
2022 -- ----------------------------------------
2023 hr_utility.trace('no future, yes current');
2024
2025 close csr_current_apl;
2026 --
2027 --set date_end to null where application_id=l_current_apl_id
2028 --
2029 per_apl_upd.upd
2030 (p_application_id => l_current_apl_id
2031 ,p_date_end => null
2032 ,p_object_version_number => l_apl_object_version_number
2033 ,p_effective_date => l_effective_date
2034 );
2035
2036 l_date_received_OUT := l_date_received;
2037 l_application_id_OUT := l_current_apl_id;
2038 l_apl_ovn_OUT := l_apl_object_version_number;
2039
2040 end if;
2041 -- -------------------------------------------------------------------
2042 else --yes future
2043 -- -------------------------------------------------------------------
2044 hr_utility.trace('YES future');
2045
2046 open csr_current_apl(p_person_id, l_effective_date);
2047 fetch csr_current_apl into l_current_apl_id, l_date_received
2048 ,l_apl_object_version_number;
2049
2050 if csr_current_apl%notfound then --yes future, no current
2051 close csr_current_apl;
2052 --
2053 -- delete more future applications, but not the first one (merge)
2054 l_rowcount := 0;
2055 loop
2056 fetch csr_fut_apl into l_del_fut_apl_id, l_del_fut_apl_date_received
2057 ,l_del_fut_apl_ovn ;
2058 exit when csr_fut_apl%notfound;
2059
2060 Update_APL_assignments(p_business_group_id, l_del_fut_apl_id,l_future_apl_id);
2061
2062 per_apl_del.del
2063 (p_application_id => l_del_fut_apl_id
2064 ,p_object_version_number => l_del_fut_apl_ovn
2065 );
2066 l_rowcount := l_rowcount + 1;
2067 end loop;
2068 close csr_fut_apl;
2069
2070 if l_rowcount > 0 then
2071 l_appl_override_warning := TRUE;
2072 end if;
2073
2074 -- set date_received=p_effective_date where application_id=l_future_apl_id;
2075 per_apl_upd.upd
2076 (p_application_id => l_future_apl_id
2077 ,p_date_received => l_effective_date
2078 ,p_date_end => null
2079 ,p_object_version_number => l_fut_apl_ovn
2080 ,p_effective_date => l_effective_date
2081 );
2082
2083 l_date_received_OUT := l_fut_apl_date_received;
2084 l_application_id_OUT := l_future_apl_id;
2085 l_apl_ovn_OUT := l_fut_apl_ovn;
2086 -- ----------------------------------------
2087 else --yes future, yes current
2088 -- ----------------------------------------
2089 hr_utility.trace('yes future, yes current');
2090
2091 close csr_current_apl;
2092 --
2093 -- delete the first future apl we already fetched
2094 --
2095 Update_APL_assignments(p_business_group_id, l_future_apl_id,l_current_apl_id);
2096 --
2097 per_apl_del.del
2098 (p_application_id => l_future_apl_id
2099 ,p_object_version_number => l_fut_apl_ovn
2100 );
2101 --
2102 l_appl_override_warning := TRUE;
2103 --
2104 -- delete more future applications (merge)
2105 --
2106 loop
2107 fetch csr_fut_apl into l_del_fut_apl_id, l_del_fut_apl_date_received
2108 ,l_del_fut_apl_ovn ;
2109
2110 exit when csr_fut_apl%notfound;
2111
2112 Update_APL_assignments(p_business_group_id, l_del_fut_apl_id,l_current_apl_id);
2113
2114 per_apl_del.del
2115 (p_application_id => l_del_fut_apl_id
2116 ,p_object_version_number => l_del_fut_apl_ovn
2117 );
2118 --
2119 end loop;
2120 close csr_fut_apl;
2121 --
2122 -- set date_end to null where application_id=l_current_apl_id
2123 --
2124 per_apl_upd.upd
2125 (p_application_id => l_current_apl_id
2126 ,p_date_received => l_date_received
2127 ,p_date_end => null
2128 ,p_object_version_number => l_apl_object_version_number
2129 ,p_effective_date => l_effective_date
2130 );
2131
2132 l_application_id_OUT := l_current_apl_id;
2133 l_date_received_OUT := l_date_received;
2134 l_apl_ovn_OUT := l_apl_object_version_number;
2135 end if;
2136 --
2137 end if;
2138 --
2139 -- Setting up the OUT parameters
2140 --
2141 p_application_id := l_application_id_OUT;
2142 p_date_received := l_date_received_OUT;
2143 p_object_version_number := l_apl_ovn_OUT;
2144 p_appl_override_warning := l_appl_override_warning;
2145
2146 if g_debug then
2147 hr_utility.set_location(' Leaving: '||l_proc,1000);
2148 end if;
2149
2150 end Create_Application;
2151 --
2152 -- ------------------------------------------------------------------------- +
2153 -- --------------------< override_future_applications >--------------------- |
2154 -- ------------------------------------------------------------------------- +
2155 FUNCTION override_future_applications
2156 (p_person_id IN NUMBER
2157 ,p_effective_date IN DATE
2158 )
2159 RETURN VARCHAR2 IS
2160 --
2161 l_future_apl_id per_applications.application_id%type;
2162 l_current_apl_id per_applications.application_id%type;
2163 l_yesterday_apl_id per_applications.application_id%type;
2164 l_raise_warning VARCHAR2(10);
2165
2166 cursor csr_fut_apl is
2167 select application_id
2168 from per_applications
2169 where person_id = p_person_id
2170 and date_received > p_effective_date
2171 order by date_received asc;
2172
2173 cursor csr_current_apl is
2174 select application_id
2175 from per_applications
2176 where person_id = p_person_id
2177 and date_received < p_effective_date
2178 and nvl(date_end,hr_api.g_eot) >= p_effective_date;
2179
2180 cursor csr_apl_yesterday is
2181 select application_id
2182 from per_applications
2183 where person_id = p_person_id
2184 and date_end = p_effective_date-1;
2185
2186
2187
2188 BEGIN
2189 l_raise_warning := 'N';
2190 open csr_fut_apl;
2191 fetch csr_fut_apl into l_future_apl_id;
2192 if csr_fut_apl%found then
2193 open csr_current_apl;
2194 fetch csr_current_apl into l_current_apl_id;
2195 if csr_current_apl%notfound then --yes future, no current
2196 close csr_current_apl;
2197 fetch csr_fut_apl INTO l_future_apl_id;
2198 IF csr_fut_apl%FOUND then
2199 l_raise_warning := 'Y';
2200 end if;
2201 else --yes future, yes current
2202 close csr_current_apl;
2203 l_raise_warning := 'Y';
2204 END IF;
2205 end if;
2206 close csr_fut_apl;
2207
2208 RETURN l_raise_warning;
2209
2210 END override_future_applications;
2211 --
2212 -- ------------------------------------------------------------------------- +
2213 -- ------------------------< future_apl_asg_exist >------------------------- |
2214 -- ------------------------------------------------------------------------- +
2215 FUNCTION future_apl_asg_exist
2216 (p_person_id IN NUMBER
2217 ,p_effective_date IN DATE
2218 ,p_application_id IN NUMBER
2219 ) RETURN VARCHAR2 IS
2220 --
2221 -- ER FPT
2222 cursor csr_future_apl_asg is
2223 select 'Y'
2224 from per_all_assignments_f paf
2225 where paf.person_id = p_person_id
2226 and paf.effective_start_date > p_effective_date
2227 and paf.assignment_type = 'A'
2228 and paf.application_id = p_application_id
2229 and paf.assignment_id IN
2230 (SELECT assignment_id
2231 FROM per_all_assignments_f asg
2232 WHERE asg.person_id = p_person_id
2233 AND asg.application_id = p_application_id
2234 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date);
2235 --
2236 l_raise_warning varchar2(10);
2237 --
2238
2239 BEGIN
2240 l_raise_warning := 'N';
2241 open csr_future_apl_asg;
2242 fetch csr_future_apl_asg into l_raise_warning;
2243 close csr_future_apl_asg;
2244 l_raise_warning := nvl(l_raise_warning,'N');
2245
2246 RETURN l_raise_warning;
2247
2248 END future_apl_asg_exist;
2249 --
2250 --
2251 -- -------------------------------------------------------------------------- +
2252 -- |--------------------< create_applicant_anytime >------------------------- |
2253 -- -------------------------------------------------------------------------- +
2254 -- This creates an application with default information and transforms
2255 -- an existing person into an applicant.
2256 --
2257 -- To create a new person as an applicant then use the
2258 -- hr_applicant_api.create_applicant() API
2259 --
2260 procedure create_applicant_anytime
2261 (p_effective_date in date
2262 ,p_person_id in number
2263 ,p_applicant_number in out nocopy varchar2
2264 ,p_per_object_version_number in out nocopy number
2265 ,p_vacancy_id in number
2266 ,p_person_type_id in number
2267 ,p_assignment_status_type_id in number
2268 ,p_application_id out nocopy number
2269 ,p_assignment_id out nocopy number
2270 ,p_apl_object_version_number out nocopy number
2271 ,p_asg_object_version_number out nocopy number
2272 ,p_assignment_sequence out nocopy number
2273 ,p_per_effective_start_date out nocopy date
2274 ,p_per_effective_end_date out nocopy DATE
2275 ,p_appl_override_warning OUT NOCOPY boolean
2276 ) is
2277 --
2278 -- declare local variables
2279 --
2280 l_proc constant varchar2(72) := g_package||'create_applicant_anytime';
2281 l_business_group_id per_people_f.business_group_id%type;
2282 l_name_combination_warning boolean;
2283 l_dob_null_warning boolean;
2284 l_orig_hire_warning boolean;
2285 l_organization_id per_business_groups.organization_id%type;
2286 l_legislation_code per_business_groups.legislation_code%type;
2287 l_person_type_id per_people_f.person_type_id%type;
2288 l_application_id per_applications.application_id%type;
2289 l_comment_id per_assignments_f.comment_id%type;
2290 l_assignment_sequence per_assignments_f.assignment_sequence%type;
2291 l_assignment_id per_assignments_f.assignment_id%type;
2292 l_object_version_number per_assignments_f.object_version_number%type;
2293 l_current_applicant_flag per_people_f.current_applicant_flag%type;
2294 l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
2295 l_current_employee_flag per_people_f.current_employee_flag%type;
2296 l_employee_number per_people_f.employee_number%type;
2297 l_applicant_number per_people_f.applicant_number%TYPE;
2298 l_npw_number per_people_f.npw_number%TYPE;
2299 l_per_object_version_number per_people_f.object_version_number%TYPE;
2300 l_full_name per_people_f.full_name%type;
2301 l_system_person_type per_person_types.system_person_type%type;
2302 l_effective_date date;
2303 l_effective_start_date date;
2304 l_effective_end_date date;
2305 l_default_start_time per_business_groups.default_start_time%type;
2306 l_default_end_time per_business_groups.default_end_time%type;
2307 l_normal_hours number;
2308 l_frequency per_business_groups.frequency%type;
2309 l_recruiter_id per_vacancies.recruiter_id%type;
2310 l_grade_id per_vacancies.grade_id%type;
2311 l_position_id per_vacancies.position_id%type;
2312 l_job_id per_vacancies.job_id%type;
2313 l_location_id per_vacancies.location_id%type;
2314 l_people_group_id per_vacancies.people_group_id%type;
2315 l_vac_organization_id per_vacancies.organization_id%type;
2316 l_vac_business_group_id per_vacancies.business_group_id%type;
2317 l_group_name pay_people_groups.group_name%type;
2318 --
2319 --
2320 l_apl_object_version_number number;
2321 l_apl_date_received DATE;
2322 l_asg_object_version_number number;
2323 l_per_effective_start_date date;
2324 l_per_effective_end_date date;
2325 l_appl_override_warning BOOLEAN;
2326 l_per_party_id per_all_people_f.party_id%TYPE;
2327 l_per_dob per_all_people_f.date_of_birth%TYPE;
2328 l_per_start_date per_all_people_f.effective_start_date%TYPE;
2329 --
2330 --
2331 -- select and validate the person
2332 --
2333 -- now returns person details
2334 --
2335 cursor csr_chk_person_exists is
2336 select ppf.business_group_id
2337 ,ppf.employee_number
2338 ,ppf.npw_number
2339 ,ppf.date_of_birth
2340 ,ppf.party_id
2341 ,ppf.effective_start_date
2342 ,ppt.system_person_type
2343 from per_person_types ppt
2344 ,per_people_f ppf
2345 where ppf.person_id = p_person_id
2346 and ppt.person_type_id = ppf.person_type_id
2347 and ppt.business_group_id + 0 = ppf.business_group_id
2348 and (l_effective_date
2349 between ppf.effective_start_date
2350 and ppf.effective_end_date or ppf.effective_start_date > l_effective_date)
2351 order by ppf.effective_start_date ASC;
2352 --
2353 -- Get organization id for business group.
2354 --
2355 cursor csr_get_organization_id is
2356 select organization_id
2357 ,legislation_code
2358 ,default_start_time
2359 ,default_end_time
2360 ,fnd_number.canonical_to_number(working_hours)
2361 ,frequency
2362 from per_business_groups
2363 where business_group_id = l_business_group_id;
2364 --
2365 -- Get vacancy information.
2366 --
2367 cursor csr_get_vacancy_details is
2368 select recruiter_id
2369 ,grade_id
2370 ,position_id
2371 ,job_id
2372 ,location_id
2373 ,people_group_id
2374 ,organization_id -- added org id to cursor. thayden 7/10.
2375 ,business_group_id -- added business_group_id to cursor lma 7/11
2376 from per_vacancies
2377 where vacancy_id = p_vacancy_id;
2378 --
2379 CURSOR csr_lock_person(cp_person_id number, cp_termination_date date) IS
2380 SELECT null
2381 FROM per_all_people_f
2382 WHERE person_id = cp_person_id
2383 AND (effective_start_date > cp_termination_date
2384 OR
2385 cp_termination_date between effective_start_date
2386 and effective_end_date)
2387 for update nowait;
2388 --
2389 CURSOR csr_lock_ptu(cp_person_id number, cp_termination_date date) IS
2390 SELECT null
2391 FROM per_person_type_usages_f ptu
2392 ,per_person_types ppt
2393 WHERE person_id = cp_person_id
2394 AND (effective_start_date > cp_termination_date
2395 OR
2396 cp_termination_date between effective_start_date
2397 and effective_end_date)
2398 AND ptu.person_type_id = ppt.person_type_id
2399 AND ppt.system_person_type in ('APL','EX_APL')
2400 for update of ptu.person_id nowait;
2401 -- ------------------------------------------------------------------------ +
2402 -- ------------------------<< BEGIN >>------------------------------------ |
2403 -- ------------------------------------------------------------------------ +
2404 BEGIN
2405 --
2406 if g_debug then
2407 hr_utility.set_location('Entering:'|| l_proc, 5);
2408 end if;
2409 --
2410 -- Truncate p_effective_date
2411 --
2412 l_effective_date := trunc(p_effective_date);
2413 l_person_type_id := p_person_type_id;
2414 --
2415 hr_utility.trace(' l_effective_date = '||to_char(l_effective_date));
2416 --
2417 l_applicant_number := p_applicant_number;
2418 l_per_object_version_number := p_per_object_version_number;
2419 --
2420 -- Validation Logic
2421 --
2422 -- Ensure that the mandatory parameter, p_person_id is not null
2423 --
2424 hr_api.mandatory_arg_error
2425 (p_api_name => l_proc
2426 ,p_argument => 'person id'
2427 ,p_argument_value => p_person_id);
2428 --
2429 if g_debug then
2430 hr_utility.set_location(' Leaving:'||l_proc, 10);
2431 end if;
2432 --
2433 -- Check that this person (p_person_id) exists as of l_effective_date
2434 -- the current person type (per_people_f.person_type_id) has a
2435 -- corresponding system person type.
2436 --
2437 open csr_chk_person_exists;
2438 fetch csr_chk_person_exists into
2439 l_business_group_id
2440 ,l_employee_number
2441 ,l_npw_number
2442 ,l_per_dob
2443 ,l_per_party_id
2444 ,l_per_start_date
2445 ,l_system_person_type;
2446 if csr_chk_person_exists%notfound then
2447 close csr_chk_person_exists;
2448 hr_utility.set_message(800, 'HR_51011_PER_NOT_EXIST_DATE');
2449 hr_utility.raise_error;
2450 end if;
2451 close csr_chk_person_exists;
2452 --
2453 if g_debug then
2454 hr_utility.set_location(l_proc, 15);
2455 end if;
2456 --
2457 per_per_bus.chk_person_type
2458 (p_person_type_id => l_person_type_id,
2459 p_business_group_id => l_business_group_id,
2460 p_expected_sys_type => 'APL');
2461 --
2462 if g_debug then
2463 hr_utility.set_location(l_proc, 20);
2464 end if;
2465 --
2466 -- Get organization id
2467 --
2468 open csr_get_organization_id;
2469 fetch csr_get_organization_id into
2470 l_organization_id
2471 ,l_legislation_code
2472 ,l_default_start_time
2473 ,l_default_end_time
2474 ,l_normal_hours
2475 ,l_frequency;
2476 if csr_get_organization_id%notfound then
2477 close csr_get_organization_id;
2478 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
2479 hr_utility.raise_error;
2480 end if;
2481 close csr_get_organization_id;
2482 --
2483 -- Get vacancy details.
2484 --
2485 if p_vacancy_id is not null then
2486 open csr_get_vacancy_details;
2487 fetch csr_get_vacancy_details into
2488 l_recruiter_id
2489 ,l_grade_id
2490 ,l_position_id
2491 ,l_job_id
2492 ,l_location_id
2493 ,l_people_group_id
2494 ,l_vac_organization_id
2495 ,l_vac_business_group_id;
2496 if csr_get_vacancy_details%notfound then
2497 close csr_get_vacancy_details;
2498 hr_utility.set_message(801, 'HR_51001_THE_VAC_NOT_FOUND');
2499 hr_utility.raise_error;
2500 end if;
2501 close csr_get_vacancy_details;
2502 --
2503 if l_vac_organization_id is null then
2504 l_vac_organization_id := l_vac_business_group_id;
2505 end if;
2506 else
2507 l_vac_organization_id := l_business_group_id;
2508 end if;
2509 --
2510 if g_debug then
2511 hr_utility.set_location(l_proc, 30);
2512 end if;
2513 --
2514 -- Validate applicant number
2515 -- Get number if one exists and parameter is NULL
2516 --
2517 hr_applicant_internal.generate_applicant_number
2518 (p_business_group_id => l_business_group_id
2519 ,p_person_id => p_person_id
2520 ,p_effective_date => l_effective_date
2521 ,p_party_id => l_per_party_id
2522 ,p_date_of_birth => l_per_dob
2523 ,p_start_date => l_per_start_date
2524 ,p_applicant_number => l_applicant_number);
2525
2526 if g_debug then
2527 hr_utility.set_location(l_proc, 33);
2528 end if;
2529 -- ------------------------------------------------------------------------ +
2530 -- ----------------------<< MAIN PROCESS >>-------------------------------- |
2531 -- ------------------------------------------------------------------------ +
2532 --
2533 -- Lock person records
2534 open csr_lock_person(p_person_id, l_effective_date);
2535 close csr_lock_person;
2536 -- Lock ptu records
2537 open csr_lock_ptu(p_person_id, l_effective_date);
2538 close csr_lock_ptu;
2539 --
2540 if g_debug then
2541 hr_utility.set_location(l_proc, 40);
2542 end if;
2543 --
2544 -- Update Person and PTU Records:
2545 --
2546 Update_PER_PTU_Records
2547 (p_business_group_id => l_business_group_id
2548 ,p_person_id => p_person_id
2549 ,p_effective_date => l_effective_date
2550 ,p_applicant_number => l_applicant_number
2551 ,P_APL_person_type_id => l_person_type_id
2552 ,p_per_effective_start_date => l_per_effective_start_date
2553 ,p_per_effective_end_date => l_per_effective_end_date
2554 ,p_per_object_version_number => l_per_object_version_number --BUG4081676
2555 );
2556 --
2557 Create_Application
2558 (p_application_id => l_application_id
2559 ,p_business_group_id => l_business_group_id
2560 ,p_person_id => p_person_id
2561 ,p_date_received => l_apl_date_received
2562 ,p_effective_date => l_effective_date
2563 ,p_object_version_number => l_apl_object_version_number
2564 ,p_appl_override_warning => l_appl_override_warning
2565 ,p_validate_df_flex => false --4689836
2566 );
2567 --
2568 if g_debug then
2569 hr_utility.set_location(l_proc, 50);
2570 end if;
2571 --
2572 -- create an applicant assignment
2573 --
2574 hr_assignment_internal.create_apl_asg
2575 (p_effective_date => l_effective_date
2576 ,p_legislation_code => l_legislation_code
2577 ,p_business_group_id => l_business_group_id
2578 ,p_person_id => p_person_id
2579 ,p_assignment_status_type_id => p_assignment_status_type_id
2580 ,p_organization_id => l_vac_organization_id
2581 ,p_application_id => l_application_id
2582 ,p_recruiter_id => l_recruiter_id
2583 ,p_grade_id => l_grade_id
2584 ,p_position_id => l_position_id
2585 ,p_job_id => l_job_id
2586 ,p_location_id => l_location_id
2587 ,p_people_group_id => l_people_group_id
2588 ,p_vacancy_id => p_vacancy_id
2589 ,p_frequency => l_frequency
2590 ,p_manager_flag => 'N'
2591 ,p_normal_hours => l_normal_hours
2592 ,p_time_normal_finish => l_default_end_time
2593 ,p_time_normal_start => l_default_start_time
2594 ,p_assignment_id => l_assignment_id
2595 ,p_object_version_number => l_asg_object_version_number
2596 ,p_effective_start_date => l_effective_start_date
2597 ,p_effective_end_date => l_effective_end_date
2598 ,p_assignment_sequence => l_assignment_sequence
2599 ,p_comment_id => l_comment_id
2600 --
2601 -- START bug# 4610369 added the parameter by risgupta for not to validate
2602 -- DFFs when assignment created internally.
2603 --
2604 ,p_validate_df_flex => false
2605 --
2606 -- END bug# 4610369
2607 --
2608 );
2609
2610 if g_debug then
2611 hr_utility.set_location(l_proc, 60);
2612 end if;
2613 --
2614 -- add to the security list
2615 --
2616 hr_security_internal.add_to_person_list(l_effective_date,l_assignment_id);
2617 -- ------------------------------------------------------------------------ +
2618 -- ---------------------<< END MAIN PROCESS >>----------------------------- |
2619 -- ------------------------------------------------------------------------ +
2620 --
2621 -- Set all output arguments
2622 --
2623 p_application_id := l_application_id;
2624 p_applicant_number := l_applicant_number;
2625 p_assignment_id := l_assignment_id;
2626 p_apl_object_version_number := l_apl_object_version_number;
2627 p_asg_object_version_number := l_asg_object_version_number;
2628 p_assignment_sequence := l_assignment_sequence;
2629 p_per_effective_start_date := l_per_effective_start_date;
2630 p_per_effective_end_date := l_per_effective_end_date;
2631 p_appl_override_warning := l_appl_override_warning;
2632 p_per_object_version_number := l_per_object_version_number; --BUG4081676
2633 --
2634 --
2635 if g_debug then
2636 hr_utility.set_location(' Leaving:'||l_proc, 50);
2637 end if;
2638
2639 end create_applicant_anytime;
2640 --
2641 --
2642 end hr_applicant_internal;