[Home] [Help]
PACKAGE BODY: APPS.PER_APPLICANT_PKG
Source
1 package body PER_APPLICANT_PKG as
2 /* $Header: peper02t.pkb 120.1 2005/11/10 13:07:05 kramajey noship $ */
3 --
4 -- PRIVATE PROCEDURES
5 --
6 -- Name
7 -- get_default_working_conditions
8 -- Purpose
9 -- Derive the working conditions for the new assignment
10 -- Given the position and organization and business group of the new
11 -- assignment get the working conditions in that order. The first set
12 -- that are not null are returned
13 -- Parameters
14 -- See below
15 --
16 -- Note
17 -- The in out variables are not out in order to be able to test their
18 -- values.
19 procedure get_working_conditions ( p_business_group_id in number,
20 p_organization_id in number,
21 p_position_id in number,
22 p_hours in out nocopy number,
23 p_freq in out nocopy varchar2,
24 p_start in out nocopy varchar2,
25 p_finish in out nocopy varchar2,
26 p_probation_period in out nocopy number,
27 p_probation_unit in out nocopy varchar2 ) is
28 --
29 -- Changed 01-Oct-99 SCNair ( per_all_positions to hr_all_positions ) date track requirement
30 --
31 cursor get_pos_conditions is
32 select p.working_hours,
33 p.frequency,
34 p.time_normal_start,
35 p.time_normal_finish,
36 p.probation_period,
37 p.probation_period_unit_cd probation_period_units
38 from hr_all_positions p
39 where p.position_id = p_position_id ;
40 --
41 cursor get_org_conditions is
42 select fnd_number.canonical_to_number(o.working_hours),
43 o.frequency,
44 o.default_start_time,
45 o.default_end_time
46 from per_all_organization_units o
47 where o.organization_id = p_organization_id ;
48 --
49 cursor get_bg_conditions is
50 select fnd_number.canonical_to_number(b.working_hours),
51 b.frequency,
52 b.default_start_time,
53 b.default_end_time
54 from per_business_groups b
55 where b.business_group_id = p_business_group_id ;
56 --
57 begin
58 --
59 if ( p_position_id is not null ) then
60 open get_pos_conditions ;
61 fetch get_pos_conditions into p_hours,
62 p_freq,
63 p_start,
64 p_finish,
65 p_probation_period,
66 p_probation_unit ;
67 close get_pos_conditions ;
68 --
69 -- If there were some details then return
70 --
71 if ( p_hours is not null
72 and p_freq is not null
73 and p_start is not null
74 and p_freq is not null ) then
75 return ;
76 end if;
77 end if ;
78 --
79 --
80 if ( ( p_organization_id is not null )
81 and ( p_organization_id <> p_business_group_id ) ) then
82 --
83 --
84 open get_org_conditions ;
85 fetch get_org_conditions into p_hours,
86 p_freq,
87 p_start,
88 p_finish ;
89 close get_org_conditions ;
90 --
91 --
92 else
93 --
94 --
95 open get_bg_conditions ;
96 fetch get_bg_conditions into p_hours,
97 p_freq,
98 p_start,
99 p_finish ;
100 close get_bg_conditions ;
101 --
102 --
103 end if ;
104 --
105 end get_working_conditions ;
106 --
107 --
108 --
109 -- PUBLIC PROCEDURES
110 procedure insert_row ( p_rowid in out nocopy VARCHAR2
111 ,p_business_group_id NUMBER
112 ,p_person_type_id in out nocopy NUMBER
113 ,p_person_id in out nocopy NUMBER
114 ,p_effective_start_date DATE
115 ,p_effective_end_date DATE
116 ,p_last_name VARCHAR2
117 ,p_first_name VARCHAR2
118 ,p_title VARCHAR2
119 ,p_full_name VARCHAR2
120 ,p_sex VARCHAR2
121 ,p_work_telephone VARCHAR2
122 ,p_applicant_number in out nocopy VARCHAR2
123 ,p_assignment_status_type_id NUMBER
124 ,p_request_id NUMBER
125 ,p_program_application_id NUMBER
126 ,p_program_id NUMBER
127 ,p_program_update_date DATE
128 ,p_attribute_category VARCHAR2
129 ,p_attribute1 VARCHAR2
130 ,p_attribute2 VARCHAR2
131 ,p_attribute3 VARCHAR2
132 ,p_attribute4 VARCHAR2
133 ,p_attribute5 VARCHAR2
134 ,p_attribute6 VARCHAR2
135 ,p_attribute7 VARCHAR2
136 ,p_attribute8 VARCHAR2
137 ,p_attribute9 VARCHAR2
138 ,p_attribute10 VARCHAR2
139 ,p_attribute11 VARCHAR2
140 ,p_attribute12 VARCHAR2
141 ,p_attribute13 VARCHAR2
142 ,p_attribute14 VARCHAR2
143 ,p_attribute15 VARCHAR2
144 ,p_attribute16 VARCHAR2
145 ,p_attribute17 VARCHAR2
146 ,p_attribute18 VARCHAR2
147 ,p_attribute19 VARCHAR2
148 ,p_attribute20 VARCHAR2
149 ,p_attribute21 VARCHAR2
150 ,p_attribute22 VARCHAR2
151 ,p_attribute23 VARCHAR2
152 ,p_attribute24 VARCHAR2
153 ,p_attribute25 VARCHAR2
154 ,p_attribute26 VARCHAR2
155 ,p_attribute27 VARCHAR2
156 ,p_attribute28 VARCHAR2
157 ,p_attribute29 VARCHAR2
158 ,p_attribute30 VARCHAR2
159 ,p_per_information_category VARCHAR2
160 ,p_per_information1 VARCHAR2
161 ,p_per_information2 VARCHAR2
162 ,p_per_information3 VARCHAR2
163 ,p_per_information4 VARCHAR2
164 ,p_per_information5 VARCHAR2
165 ,p_per_information6 VARCHAR2
166 ,p_per_information7 VARCHAR2
167 ,p_per_information8 VARCHAR2
168 ,p_per_information9 VARCHAR2
169 ,p_per_information10 VARCHAR2
170 ,p_per_information11 VARCHAR2
171 ,p_per_information12 VARCHAR2
172 ,p_per_information13 VARCHAR2
173 ,p_per_information14 VARCHAR2
174 ,p_per_information15 VARCHAR2
175 ,p_per_information16 VARCHAR2
176 ,p_per_information17 VARCHAR2
177 ,p_per_information18 VARCHAR2
178 ,p_per_information19 VARCHAR2
179 ,p_per_information20 VARCHAR2
180 ,p_per_information21 VARCHAR2
181 ,p_per_information22 VARCHAR2
182 ,p_per_information23 VARCHAR2
183 ,p_per_information24 VARCHAR2
184 ,p_per_information25 VARCHAR2
185 ,p_per_information26 VARCHAR2
186 ,p_per_information27 VARCHAR2
187 ,p_per_information28 VARCHAR2
188 ,p_per_information29 VARCHAR2
189 ,p_per_information30 VARCHAR2
190 ,p_style VARCHAR2
191 ,p_address_line1 VARCHAR2
192 ,p_address_line2 VARCHAR2
193 ,p_address_line3 VARCHAR2
194 ,p_address_type VARCHAR2
195 ,p_country VARCHAR2
196 ,p_postal_code VARCHAR2
197 ,p_region_1 VARCHAR2
198 ,p_region_2 VARCHAR2
199 ,p_region_3 VARCHAR2
200 ,p_telephone_number_1 VARCHAR2
201 ,p_telephone_number_2 VARCHAR2
202 ,p_telephone_number_3 VARCHAR2
203 ,p_town_or_city VARCHAR2
204 ,p_addr_attribute_category VARCHAR2
205 ,p_addr_attribute1 VARCHAR2
206 ,p_addr_attribute2 VARCHAR2
207 ,p_addr_attribute3 VARCHAR2
208 ,p_addr_attribute4 VARCHAR2
209 ,p_addr_attribute5 VARCHAR2
210 ,p_addr_attribute6 VARCHAR2
211 ,p_addr_attribute7 VARCHAR2
212 ,p_addr_attribute8 VARCHAR2
213 ,p_addr_attribute9 VARCHAR2
214 ,p_addr_attribute10 VARCHAR2
215 ,p_addr_attribute11 VARCHAR2
216 ,p_addr_attribute12 VARCHAR2
217 ,p_addr_attribute13 VARCHAR2
218 ,p_addr_attribute14 VARCHAR2
219 ,p_addr_attribute15 VARCHAR2
220 ,p_addr_attribute16 VARCHAR2
221 ,p_addr_attribute17 VARCHAR2
222 ,p_addr_attribute18 VARCHAR2
223 ,p_addr_attribute19 VARCHAR2
224 ,p_addr_attribute20 VARCHAR2
225 -- ***** Start new code for bug 2711964 **************
226 ,p_add_information13 VARCHAR2
227 ,p_add_information14 VARCHAR2
228 ,p_add_information15 VARCHAR2
229 ,p_add_information16 VARCHAR2
230 ,p_add_information17 VARCHAR2
231 ,p_add_information18 VARCHAR2
232 ,p_add_information19 VARCHAR2
233 ,p_add_information20 VARCHAR2
234 -- ***** End new code for bug 2711964 ***************
235 ,p_date_received DATE
236 ,p_current_employer VARCHAR2
237 ,p_appl_attribute_category VARCHAR2
238 ,p_appl_attribute1 VARCHAR2
239 ,p_appl_attribute2 VARCHAR2
240 ,p_appl_attribute3 VARCHAR2
241 ,p_appl_attribute4 VARCHAR2
242 ,p_appl_attribute5 VARCHAR2
243 ,p_appl_attribute6 VARCHAR2
244 ,p_appl_attribute7 VARCHAR2
245 ,p_appl_attribute8 VARCHAR2
246 ,p_appl_attribute9 VARCHAR2
247 ,p_appl_attribute10 VARCHAR2
248 ,p_appl_attribute11 VARCHAR2
249 ,p_appl_attribute12 VARCHAR2
250 ,p_appl_attribute13 VARCHAR2
251 ,p_appl_attribute14 VARCHAR2
252 ,p_appl_attribute15 VARCHAR2
253 ,p_appl_attribute16 VARCHAR2
254 ,p_appl_attribute17 VARCHAR2
255 ,p_appl_attribute18 VARCHAR2
256 ,p_appl_attribute19 VARCHAR2
257 ,p_appl_attribute20 VARCHAR2
258 ,p_recruitment_activity_id NUMBER
259 ,p_source_organization_id NUMBER
260 ,p_person_referred_by_id NUMBER
261 ,p_vacancy_id NUMBER
262 ,p_recruiter_id NUMBER
263 ,p_organization_id NUMBER
264 ,p_people_group_id NUMBER
265 ,p_people_group_name VARCHAR2
266 ,p_job_id NUMBER
267 ,p_position_id NUMBER
268 ,p_grade_id NUMBER
269 ,p_location_id NUMBER
270 ,p_location_code in out nocopy VARCHAR2
271 ,p_source_type VARCHAR2
272 ,p_ass_attribute_category VARCHAR2
273 ,p_ass_attribute1 VARCHAR2
274 ,p_ass_attribute2 VARCHAR2
275 ,p_ass_attribute3 VARCHAR2
276 ,p_ass_attribute4 VARCHAR2
277 ,p_ass_attribute5 VARCHAR2
278 ,p_ass_attribute6 VARCHAR2
279 ,p_ass_attribute7 VARCHAR2
280 ,p_ass_attribute8 VARCHAR2
281 ,p_ass_attribute9 VARCHAR2
282 ,p_ass_attribute10 VARCHAR2
283 ,p_ass_attribute11 VARCHAR2
284 ,p_ass_attribute12 VARCHAR2
285 ,p_ass_attribute13 VARCHAR2
286 ,p_ass_attribute14 VARCHAR2
287 ,p_ass_attribute15 VARCHAR2
288 ,p_ass_attribute16 VARCHAR2
289 ,p_ass_attribute17 VARCHAR2
290 ,p_ass_attribute18 VARCHAR2
291 ,p_ass_attribute19 VARCHAR2
292 ,p_ass_attribute20 VARCHAR2
293 ,p_ass_attribute21 VARCHAR2
294 ,p_ass_attribute22 VARCHAR2
295 ,p_ass_attribute23 VARCHAR2
296 ,p_ass_attribute24 VARCHAR2
297 ,p_ass_attribute25 VARCHAR2
298 ,p_ass_attribute26 VARCHAR2
299 ,p_ass_attribute27 VARCHAR2
300 ,p_ass_attribute28 VARCHAR2
301 ,p_ass_attribute29 VARCHAR2
302 ,p_ass_attribute30 VARCHAR2
303 ,p_per_system_status VARCHAR2
304 ,p_address_set BOOLEAN
305 ,p_method_of_apl_num_gen VARCHAR2
306 ,p_party_id NUMBER
307 ,p_date_of_birth DATE
308 ,p_known_as VARCHAR2
309 ,p_marital_status VARCHAR2
310 ,p_middle_names VARCHAR2
311 ,p_nationality VARCHAR2
312 ,p_blood_type VARCHAR2
313 ,p_correspondence_language VARCHAR2
314 ,p_honors VARCHAR2
315 ,p_pre_name_adjunct VARCHAR2
316 ,p_rehire_authorizor VARCHAR2
317 ,p_rehire_recommendation VARCHAR2
318 ,p_resume_exists VARCHAR2
319 ,p_resume_last_updated DATE
320 ,p_second_passport_exists VARCHAR2
321 ,p_student_status VARCHAR2
322 ,p_suffix VARCHAR2
323 ,p_date_of_death DATE
324 ,p_uses_tobacco_flag VARCHAR2
325 ,p_town_of_birth VARCHAR2
326 ,p_region_of_birth VARCHAR2
327 ,p_country_of_birth VARCHAR2
328 ,p_fast_path_employee VARCHAR2
329 ,p_email_address VARCHAR2
330 ,p_fte_capacity VARCHAR2
331 ,p_national_identifier VARCHAR2 ) is
332 --
333 l_assignment_status_id number;
334 l_object_version_number number;
335 --
336 l_dummy_varchar2 varchar2(255) := NULL ; -- Used to discard IN/OUT variables
337 l_dummy2_varchar2 varchar2(255) := NULL ; -- Used to discard IN/OUT variables
338 l_dummy_number number := NULL ; -- Used to discard IN/OUT variables
339 l_application_id number := NULL ;
340 l_assignment_id number := NULL ;
341 l_apl_date_end date := NULL ; -- The end date for the application
342 l_asg_end_date date := NULL ; -- The end date for the assignment
343 --
344 -- Working conditions for the Assignment
345 l_hours number := NULL ;
346 l_freq varchar2(30) := NULL ;
347 l_start varchar2(5) := NULL ;
348 l_finish varchar2(5) := NULL ;
349 l_probation_period number := NULL ;
350 l_probation_unit varchar2(30) := NULL ;
351 l_date_probation_end date := NULL ; -- Added to fix the bug# 2314084
352
353 -- copied from peper01t.pkb ( bug 1368672 )
354 --
355 cursor c1 is select per_people_s.nextval
356 from sys.dual;
357 --
358 --
359 -- Retrieve the person type id if the applicant is terminated
360 function get_ex_apl_person_type_id ( p_business_group_id in number )
361 return number is
362 l_return_value number := NULL ;
363 --
364 -- Bug fix 3648715
365 -- cursor c1 modified - '+0' removed to improve performance.
366
367 cursor c1 is
368 select person_type_id
369 from per_person_types
370 where business_group_id = p_business_group_id
371 and system_person_type = 'EX_APL'
372 and active_flag = 'Y'
373 and default_flag = 'Y' ;
374 begin
375 --
376 open c1 ;
377 fetch c1 into l_return_value ;
378 close c1 ;
379 --
380 return (l_return_value ) ;
381 --
382 end ;
383 --
384 --
385 begin
386 --
387 --Create the PERSON record
388 --
389 hr_utility.set_location('PER_APPLICANT_PKG.INSERT_ROW' , 1 ) ;
390 if ( p_method_of_apl_num_gen = 'A' and p_applicant_number is null) then
391 hr_person.generate_number( p_current_employee => 'N',
392 p_current_applicant => 'Y',
393 p_current_npw => 'N',
394 p_business_group_id => p_business_group_id,
395 p_person_id => null,
396 p_employee_number => l_dummy2_varchar2,
397 p_applicant_number => p_applicant_number,
398 p_npw_number => l_dummy_varchar2,
399 p_national_identifier => p_national_identifier,
400 p_effective_date => p_effective_start_date,
401 p_party_id => p_party_id,
402 p_date_of_birth => p_date_of_birth
403 ,p_start_date => p_effective_start_date);
404 else
405 -- Check that the number is unique
406 hr_person.validate_unique_number ( p_person_id => NULL,
407 p_business_group_id => p_business_group_id,
408 p_employee_number => NULL,
409 p_applicant_number => p_applicant_number,
410 p_npw_number => NULL,
411 p_current_employee => 'N',
412 p_current_applicant => 'Y',
413 p_current_npw => 'N');
414 end if ;
415 --
416 -- If the location code is null and the location id is not null
417 -- then populate location code. This happens when the standard location
418 -- is accepted.
419 if ( ( p_location_id is not null )
420 and ( p_location_code is null ) ) then
421 p_location_code := get_location_code( p_location_id ) ;
422 end if ;
423
424 -- To resolve bug 1346121
425 -- Had to add the following if and move up the insert
426 -- to before the proc call
427 -- This is untidy but the clean way would have involved the
428 -- rewrite of this package
429
430 if ( p_person_id is null ) then
431 open c1;
432 fetch c1 into p_person_id;
433 close c1;
434 end if;
435
436 --
437 hr_security_internal.populate_new_person
438 (p_business_group_id => p_business_group_id
439 ,p_person_id => p_person_id);
440 --
441
442 per_people_pkg.insert_row(
443 p_rowid => p_rowid,
444 p_person_id => p_person_id ,
445 p_effective_start_date => p_effective_start_date,
446 p_effective_end_date => p_effective_end_date,
447 p_business_group_id => p_business_group_id ,
448 p_person_type_id => p_person_type_id ,
449 p_last_name => p_last_name,
450 p_start_date => p_date_received,
451 p_applicant_number => p_applicant_number ,
452 p_comment_id => null ,
453 p_current_applicant_flag => 'Y' ,
454 p_current_emp_or_apl_flag => 'Y' ,
455 p_current_employee_flag => NULL,
456 p_date_employee_data_verified => NULL,
457 p_date_of_birth => p_date_of_birth,
458 p_email_address => p_email_address,
459 p_employee_number => l_dummy_varchar2 ,
460 p_expense_check_send_to_addr => NULL ,
461 p_first_name => p_first_name,
462 p_full_name => p_full_name ,
463 p_known_as => p_known_as,
464 p_marital_status => p_marital_status,
465 p_middle_names => p_middle_names,
466 p_nationality => p_nationality,
467 p_national_identifier => p_national_identifier,
468 p_previous_last_name => NULL,
469 p_registered_disabled_flag => NULL,
470 p_sex => p_sex ,
471 p_title => p_title ,
472 p_suffix => p_suffix,
473 p_vendor_id => NULL,
474 p_work_telephone => p_work_telephone ,
475 p_request_id => p_request_id ,
476 p_program_application_id => p_program_application_id,
477 p_program_id => p_program_id ,
478 p_program_update_date => p_program_update_date ,
479 p_a_cat => p_attribute_category ,
480 p_a1 => p_attribute1,
481 p_a2 => p_attribute2,
482 p_a3 => p_attribute3,
483 p_a4 => p_attribute4,
484 p_a5 => p_attribute5,
485 p_a6 => p_attribute6,
486 p_a7 => p_attribute7,
487 p_a8 => p_attribute8,
488 p_a9 => p_attribute9,
489 p_a10 => p_attribute10,
490 p_a11 => p_attribute11,
491 p_a12 => p_attribute12,
492 p_a13 => p_attribute13,
493 p_a14 => p_attribute14,
494 p_a15 => p_attribute15,
495 p_a16 => p_attribute16,
496 p_a17 => p_attribute17,
497 p_a18 => p_attribute18,
498 p_a19 => p_attribute19,
499 p_a20 => p_attribute20,
500 p_a21 => p_attribute21,
501 p_a22 => p_attribute22,
502 p_a23 => p_attribute23,
503 p_a24 => p_attribute24,
504 p_a25 => p_attribute25,
505 p_a26 => p_attribute26,
506 p_a27 => p_attribute27,
507 p_a28 => p_attribute28,
508 p_a29 => p_attribute29,
509 p_a30 => p_attribute30,
510 p_last_update_date => null ,
511 p_last_updated_by => null ,
512 p_last_update_login => null,
513 p_created_by => null,
514 p_creation_date => null,
515 p_i_cat => p_per_information_category ,
516 p_i1 => p_per_information1 ,
517 p_i2 => p_per_information2,
518 p_i3 => p_per_information3,
519 p_i4 => p_per_information4,
520 p_i5 => p_per_information5,
521 p_i6 => p_per_information6,
522 p_i7 => p_per_information7,
523 p_i8 => p_per_information8,
524 p_i9 => p_per_information9,
525 p_i10 => p_per_information10,
526 p_i11 => p_per_information11,
527 p_i12 => p_per_information12,
528 p_i13 => p_per_information13,
529 p_i14 => p_per_information14,
530 p_i15 => p_per_information15,
531 p_i16 => p_per_information16,
532 p_i17 => p_per_information17,
533 p_i18 => p_per_information18,
534 p_i19 => p_per_information19,
535 p_i20 => p_per_information20,
536 p_i21 => p_per_information21,
537 p_i22 => p_per_information22,
538 p_i23 => p_per_information23,
539 p_i24 => p_per_information24,
540 p_i25 => p_per_information25,
541 p_i26 => p_per_information26,
542 p_i27 => p_per_information27,
543 p_i28 => p_per_information28,
544 p_i29 => p_per_information29,
545 p_i30 => p_per_information30,
546 p_app_ass_status_type_id => NULL,
547 p_emp_ass_status_type_id => NULL,
548 p_create_defaults_for => NULL,
549 p_party_id => p_party_id,
550 p_blood_type => p_blood_type
551 ,p_correspondence_language => p_correspondence_language
552 ,p_honors => p_honors
553 ,p_pre_name_adjunct =>p_pre_name_adjunct
554 ,p_rehire_authorizor =>p_rehire_authorizor
555 ,p_rehire_recommendation=>p_rehire_recommendation
556 ,p_resume_exists =>p_resume_exists
557 ,p_resume_last_updated =>p_resume_last_updated
558 ,p_second_passport_exists=>p_second_passport_exists
559 ,p_student_status =>p_student_status
560 ,p_date_of_death =>p_date_of_death
561 ,p_uses_tobacco_flag =>p_uses_tobacco_flag
562 ,p_town_of_birth =>p_town_of_birth
563 ,p_region_of_birth =>p_region_of_birth
564 ,p_country_of_birth =>p_country_of_birth
565 ,p_fast_path_employee=>p_fast_path_employee
566 ,p_fte_capacity =>p_fte_capacity ) ;
567
568
569 -- If the Application has been terminated then create a person starting
570 -- from the following day
571 if p_per_system_status = 'TERM_APL' then
572 p_person_type_id := get_ex_apl_person_type_id ( p_business_group_id ) ;
573 per_people_pkg.insert_row(
574 p_rowid => p_rowid,
575 p_person_id => p_person_id ,
576 p_effective_start_date => p_effective_start_date + 1,
577 p_effective_end_date => hr_general.end_of_time,
578 p_business_group_id => p_business_group_id ,
579 p_person_type_id => p_person_type_id ,
580 p_last_name => p_last_name,
581 p_start_date => p_date_received,
582 p_applicant_number => p_applicant_number ,
583 p_comment_id => null ,
584 p_current_applicant_flag => 'Y' ,
585 p_current_emp_or_apl_flag => 'Y' ,
586 p_current_employee_flag => NULL,
587 p_date_employee_data_verified => NULL,
588 p_date_of_birth => NULL,
589 p_email_address => NULL,
590 p_employee_number => l_dummy_varchar2 ,
591 p_expense_check_send_to_addr => NULL ,
592 p_first_name => p_first_name,
593 p_full_name => NULL ,
594 p_known_as => NULL,
595 p_marital_status => NULL,
596 p_middle_names => NULL,
597 p_nationality => NULL,
598 p_national_identifier => p_national_identifier,
599 p_previous_last_name => NULL,
600 p_registered_disabled_flag => NULL,
601 p_sex => p_sex ,
602 p_title => p_title ,
603 p_suffix => NULL,
604 p_vendor_id => NULL,
605 p_work_telephone => p_work_telephone ,
606 p_request_id => p_request_id ,
607 p_program_application_id => p_program_application_id,
608 p_program_id => p_program_id ,
609 p_program_update_date => p_program_update_date ,
610 p_a_cat => p_attribute_category ,
611 p_a1 => p_attribute1,
612 p_a2 => p_attribute2,
613 p_a3 => p_attribute3,
614 p_a4 => p_attribute4,
615 p_a5 => p_attribute5,
616 p_a6 => p_attribute6,
617 p_a7 => p_attribute7,
618 p_a8 => p_attribute8,
619 p_a9 => p_attribute9,
620 p_a10 => p_attribute10,
621 p_a11 => p_attribute11,
622 p_a12 => p_attribute12,
623 p_a13 => p_attribute13,
624 p_a14 => p_attribute14,
625 p_a15 => p_attribute15,
626 p_a16 => p_attribute16,
627 p_a17 => p_attribute17,
628 p_a18 => p_attribute18,
629 p_a19 => p_attribute19,
630 p_a20 => p_attribute20,
631 p_a21 => p_attribute21,
632 p_a22 => p_attribute22,
633 p_a23 => p_attribute23,
634 p_a24 => p_attribute24,
635 p_a25 => p_attribute25,
636 p_a26 => p_attribute26,
637 p_a27 => p_attribute27,
638 p_a28 => p_attribute28,
639 p_a29 => p_attribute29,
640 p_a30 => p_attribute30,
641 p_last_update_date => null ,
642 p_last_updated_by => null ,
643 p_last_update_login => null,
644 p_created_by => null,
645 p_creation_date => null,
646 p_i_cat => p_per_information_category ,
647 p_i1 => p_per_information1 ,
648 p_i2 => p_per_information2,
649 p_i3 => p_per_information3,
650 p_i4 => p_per_information4,
651 p_i5 => p_per_information5,
652 p_i6 => p_per_information6,
653 p_i7 => p_per_information7,
654 p_i8 => p_per_information8,
655 p_i9 => p_per_information9,
656 p_i10 => p_per_information10,
657 p_i11 => p_per_information11,
658 p_i12 => p_per_information12,
659 p_i13 => p_per_information13,
660 p_i14 => p_per_information14,
661 p_i15 => p_per_information15,
662 p_i16 => p_per_information16,
663 p_i17 => p_per_information17,
664 p_i18 => p_per_information18,
665 p_i19 => p_per_information19,
666 p_i20 => p_per_information20,
667 p_i21 => p_per_information21,
668 p_i22 => p_per_information22,
669 p_i23 => p_per_information23,
670 p_i24 => p_per_information24,
671 p_i25 => p_per_information25,
672 p_i26 => p_per_information26,
673 p_i27 => p_per_information27,
674 p_i28 => p_per_information28,
675 p_i29 => p_per_information29,
676 p_i30 => p_per_information30,
677 p_app_ass_status_type_id => NULL,
678 p_emp_ass_status_type_id => NULL,
679 p_create_defaults_for => NULL,
680 p_party_id => p_party_id
681 ,p_blood_type => p_blood_type
682 ,p_correspondence_language => p_correspondence_language
683 ,p_honors => p_honors
684 ,p_pre_name_adjunct =>p_pre_name_adjunct
685 ,p_rehire_authorizor =>p_rehire_authorizor
686 ,p_rehire_recommendation=>p_rehire_recommendation
687 ,p_resume_exists =>p_resume_exists
688 ,p_resume_last_updated =>p_resume_last_updated
689 ,p_second_passport_exists=>p_second_passport_exists
690 ,p_student_status =>p_student_status
691 ,p_date_of_death =>p_date_of_death
692 ,p_uses_tobacco_flag =>p_uses_tobacco_flag
693 ,p_town_of_birth =>p_town_of_birth
694 ,p_region_of_birth =>p_region_of_birth
695 ,p_country_of_birth =>p_country_of_birth
696 ,p_fast_path_employee=>p_fast_path_employee
697 ,p_fte_capacity =>p_fte_capacity
698 ) ;
699
700 end if ;
701 --
702 -- PTU Added call to create APL PTU record
703 -- Condition added to pass the person_type_id instead of the default value
704 -- for the applicant quick entry form enhancement
705 if p_person_type_id is null then
706 hr_per_type_usage_internal.maintain_person_type_usage
707 ( p_effective_date => p_effective_start_date
708 ,p_person_id => p_person_id
709 ,p_person_type_id => hr_person_type_usage_info.get_default_person_type_id
710 ( p_Business_Group_Id
711 ,'APL')
712 );
713 else
714 hr_per_type_usage_internal.maintain_person_type_usage
715 ( p_effective_date => p_effective_start_date
716 ,p_person_id => p_person_id
717 ,p_person_type_id => p_person_type_id);
718 end if;
719 --
720 if p_per_system_status = 'TERM_APL' then
721 hr_per_type_usage_internal.maintain_person_type_usage
722 ( p_effective_date => p_effective_start_date+1
723 ,p_person_id => p_person_id
724 ,p_person_type_id => p_person_type_id
725 );
726 end if;
727 --
728 -- End PTU Changes
729 --
730 if p_address_set then
731 -- **************************
732 -- Create the ADDRESS record
733 -- **************************
734 l_dummy_number := NULL ;
735 hr_utility.set_location('PER_APPLICANT_PKG.INSERT_ROW' , 2 ) ;
736 per_addresses_pkg.insert_row(
737 p_row_id => l_dummy_varchar2
738 ,p_address_id => l_dummy_number
739 ,p_business_group_id => p_business_group_id
740 ,p_person_id => p_person_id
741 ,p_date_from => p_date_received
742 ,p_primary_flag => 'Y'
743 ,p_style => p_style
744 ,p_address_line1 => p_address_line1
745 ,p_address_line2 => p_address_line2
746 ,p_address_line3 => p_address_line3
747 ,p_address_type => p_address_type
748 ,p_comments => NULL
749 ,p_country => p_country
750 ,p_date_to => NULL
751 ,p_postal_code => p_postal_code
752 ,p_region_1 => p_region_1
753 ,p_region_2 => p_region_2
754 ,p_region_3 => p_region_3
755 ,p_telephone_number_1 => p_telephone_number_1
756 ,p_telephone_number_2 => p_telephone_number_2
757 ,p_telephone_number_3 => p_telephone_number_3
758 ,p_town_or_city => p_town_or_city
759 ,p_request_id => p_request_id
760 ,p_program_application_id => p_program_application_id
761 ,p_program_id => p_program_id
762 ,p_program_update_date => p_program_update_date
763 ,p_addr_attribute_category => p_addr_attribute_category
764 ,p_addr_attribute1 => p_addr_attribute1
765 ,p_addr_attribute2 => p_addr_attribute2
766 ,p_addr_attribute3 => p_addr_attribute3
767 ,p_addr_attribute4 => p_addr_attribute4
768 ,p_addr_attribute5 => p_addr_attribute5
769 ,p_addr_attribute6 => p_addr_attribute6
770 ,p_addr_attribute7 => p_addr_attribute7
771 ,p_addr_attribute8 => p_addr_attribute8
772 ,p_addr_attribute9 => p_addr_attribute9
773 ,p_addr_attribute10 => p_addr_attribute10
774 ,p_addr_attribute11 => p_addr_attribute11
775 ,p_addr_attribute12 => p_addr_attribute12
776 ,p_addr_attribute13 => p_addr_attribute13
777 ,p_addr_attribute14 => p_addr_attribute14
778 ,p_addr_attribute15 => p_addr_attribute15
779 ,p_addr_attribute16 => p_addr_attribute16
780 ,p_addr_attribute17 => p_addr_attribute17
781 ,p_addr_attribute18 => p_addr_attribute18
782 ,p_addr_attribute19 => p_addr_attribute19
783 ,p_addr_attribute20 => p_addr_attribute20
784 -- ***** Start commented code for bug 2711964 ********
785 -- ,p_add_information17 => null
786 -- ,p_add_information18 => null
787 -- ,p_add_information19 => null
788 -- ,p_add_information20 => null
789 -- ***** End commented code for bug 2711964 **********
790 -- ***** Start new code for bug 2711964 **************
791 ,p_add_information13 => p_add_information13
792 ,p_add_information14 => p_add_information14
793 ,p_add_information15 => p_add_information15
794 ,p_add_information16 => p_add_information16
795 ,p_add_information17 => p_add_information17
796 ,p_add_information18 => p_add_information18
797 ,p_add_information19 => p_add_information19
798 ,p_add_information20 => p_add_information20
799 -- ***** End new code for bug 2711964 ***************
800 ,p_end_of_time => hr_general.end_of_time
801 );
802 end if ;
803 --
804 --
805 -- Create the APPLICATION record
806 -- If terminating application then set end date
807 if p_per_system_status = 'TERM_APL' then
808 l_apl_date_end := p_date_received ;
809 else
810 l_apl_date_end := NULL ;
811 end if ;
812 --
813 l_application_id := NULL ;
814 l_dummy_varchar2 := NULL ;
815 hr_utility.set_location('PER_APPLICANT_PKG.INSERT_ROW' , 3 ) ;
816 per_applications_pkg.Insert_Row(p_Rowid => l_dummy_varchar2,
817 p_Application_Id => l_application_id,
818 p_Business_Group_Id => p_business_group_id,
819 p_Person_Id => p_person_id,
820 p_Date_Received => p_date_received,
821 p_Comments => NULL,
822 p_Current_Employer => p_current_employer,
823 p_Date_End => l_apl_date_end,
824 p_Projected_Hire_Date => NULL,
825 p_Successful_Flag => NULL,
826 p_Termination_Reason => NULL,
827 p_Appl_Attribute_Category => p_appl_attribute_category,
828 p_Appl_Attribute1 => p_appl_attribute1,
829 p_Appl_Attribute2 => p_appl_attribute2,
830 p_Appl_Attribute3 => p_appl_attribute3,
831 p_Appl_Attribute4 => p_appl_attribute4,
832 p_Appl_Attribute5 => p_appl_attribute5,
833 p_Appl_Attribute6 => p_appl_attribute6,
834 p_Appl_Attribute7 => p_appl_attribute7,
835 p_Appl_Attribute8 => p_appl_attribute8,
836 p_Appl_Attribute9 => p_appl_attribute9,
837 p_Appl_Attribute10 => p_appl_attribute10,
838 p_Appl_Attribute11 => p_appl_attribute11,
839 p_Appl_Attribute12 => p_appl_attribute12,
840 p_Appl_Attribute13 => p_appl_attribute13,
841 p_Appl_Attribute14 => p_appl_attribute14,
842 p_Appl_Attribute15 => p_appl_attribute15,
843 p_Appl_Attribute16 => p_appl_attribute16,
844 p_Appl_Attribute17 => p_appl_attribute17,
845 p_Appl_Attribute18 => p_appl_attribute18,
846 p_Appl_Attribute19 => p_appl_attribute19,
847 p_Appl_Attribute20 => p_appl_attribute20,
848 p_Last_Update_Date => null,
849 p_Last_Updated_By => null,
850 p_Last_Update_Login => null,
851 p_Created_By => null,
852 p_Creation_Date => null ) ;
853 --
854 --
855 -- Create the ASSIGNMENT record
856 --
857 --
858 hr_utility.set_location('PER_APPLICANT_PKG.INSERT_ROW' , 4 ) ;
859 l_dummy_number := NULL ;
860 l_dummy_varchar2 := NULL ;
861 --
862 -- Set the end date of the assignment if the status is TERM_APL
863 --
864 if ( p_per_system_status = 'TERM_APL' ) then
865 l_asg_end_date := p_date_received ;
866 else
867 l_asg_end_date := hr_general.end_of_time ;
868 end if ;
869 --
870 --
871 -- Get the working conditions.
872 --
873 get_working_conditions ( p_business_group_id => p_business_group_id,
874 p_organization_id => p_organization_id,
875 p_position_id => p_position_id,
876 p_freq => l_freq,
877 p_hours => l_hours,
878 p_start => l_start,
879 p_finish => l_finish,
880 p_probation_period => l_probation_period,
881 p_probation_unit => l_probation_unit ) ;
882 --
883 -- Start of fix for bug# 2314084
884 --
885 if ((l_probation_period is not null) and (l_probation_unit is not null) ) then
886 hr_assignment.gen_probation_end
887 (p_assignment_id => l_assignment_id
888 ,p_probation_period => l_probation_period
889 ,p_probation_unit => l_probation_unit
890 ,p_start_date => p_date_received
891 ,p_date_probation_end => l_date_probation_end
892 );
893 end if;
894 --
895 -- End of fix for bug# 2314084
896 --
897 per_assignments_f_pkg.insert_row(
898 p_row_id => l_dummy_varchar2,
899 p_assignment_id => l_assignment_id,
900 p_effective_start_date => p_date_received,
901 p_effective_end_date => l_asg_end_date,
902 p_business_group_id => p_business_group_id,
903 p_recruiter_id => p_recruiter_id,
904 p_grade_id => p_grade_id,
905 p_position_id => p_position_id,
906 p_job_id => p_job_id,
907 p_assignment_status_type_id => p_assignment_status_type_id,
908 p_payroll_id => NULL,
909 p_location_id => p_location_id,
910 p_person_referred_by_id => p_person_referred_by_id,
911 p_supervisor_id => NULL,
912 p_special_ceiling_step_id => NULL,
913 p_person_id => p_person_id,
914 p_recruitment_activity_id => p_recruitment_activity_id,
915 p_source_organization_id => p_source_organization_id,
916 p_organization_id => nvl(p_organization_id,p_business_group_id
917 ),
918
919 p_people_group_id => p_people_group_id,
920 p_soft_coding_keyflex_id => NULL,
921 p_vacancy_id => p_vacancy_id,
922 p_assignment_sequence => 1,
923 p_assignment_type => 'A',
924 p_primary_flag => 'N',
925 p_application_id => l_application_id,
926 p_assignment_number => NULL,
927 p_change_reason => NULL,
928 p_comment_id => NULL,
929 p_date_probation_end => l_date_probation_end,
930 p_default_code_comb_id => NULL,
931 p_frequency => l_freq,
932 p_internal_address_line => NULL,
933 p_manager_flag => 'N',
934 p_normal_hours => l_hours,
935 p_period_of_service_id => NULL,
936 p_probation_period => l_probation_period,
937 p_probation_unit => l_probation_unit,
938 p_set_of_books_id => NULL,
939 p_source_type => p_source_type,
940 p_time_normal_finish => l_finish,
941 p_time_normal_start => l_start,
942 p_request_id => NULL,
943 p_program_application_id => NULL,
944 p_program_id => NULL,
945 p_program_update_date => NULL,
946 p_ass_attribute_category => p_ass_attribute_category,
947 p_ass_attribute1 => p_ass_attribute1,
948 p_ass_attribute2 => p_ass_attribute2,
949 p_ass_attribute3 => p_ass_attribute3,
950 p_ass_attribute4 => p_ass_attribute4,
951 p_ass_attribute5 => p_ass_attribute5,
952 p_ass_attribute6 => p_ass_attribute6,
953 p_ass_attribute7 => p_ass_attribute7,
954 p_ass_attribute8 => p_ass_attribute8,
955 p_ass_attribute9 => p_ass_attribute9,
956 p_ass_attribute10 => p_ass_attribute10,
957 p_ass_attribute11 => p_ass_attribute11,
958 p_ass_attribute12 => p_ass_attribute12,
959 p_ass_attribute13 => p_ass_attribute13,
960 p_ass_attribute14 => p_ass_attribute14,
961 p_ass_attribute15 => p_ass_attribute15,
962 p_ass_attribute16 => p_ass_attribute16,
963 p_ass_attribute17 => p_ass_attribute17,
964 p_ass_attribute18 => p_ass_attribute18,
965 p_ass_attribute19 => p_ass_attribute19,
966 p_ass_attribute20 => p_ass_attribute20,
967 p_ass_attribute21 => p_ass_attribute21,
968 p_ass_attribute22 => p_ass_attribute22,
969 p_ass_attribute23 => p_ass_attribute23,
970 p_ass_attribute24 => p_ass_attribute24,
971 p_ass_attribute25 => p_ass_attribute25,
972 p_ass_attribute26 => p_ass_attribute26,
973 p_ass_attribute27 => p_ass_attribute27,
974 p_ass_attribute28 => p_ass_attribute28,
975 p_ass_attribute29 => p_ass_attribute29,
976 p_ass_attribute30 => p_ass_attribute30,
977 p_sal_review_period => NULL,
978 p_sal_review_period_frequency => NULL,
979 p_perf_review_period => NULL,
980 p_perf_review_period_frequency => NULL,
981 p_pay_basis_id => NULL,
982 p_employment_category => NULL,
983 p_bargaining_unit_code => NULL,
984 p_labour_union_member_flag => NULL,
985 p_hourly_salaried_code => NULL
986 ) ;
987 --
988 /* Removing the call as this creates an additional record
989 -- Insert a row into irc_assignment_statuses for irecruitment
990
991 IRC_ASG_STATUS_API.create_irc_asg_status
992 ( p_validate => FALSE
993 , p_assignment_id => l_assignment_id
994 , p_assignment_status_type_id => p_assignment_status_type_id
995 , p_status_change_date => p_date_received --2754362 l_asg_end_date
996 , p_assignment_status_id => l_assignment_status_id
997 , p_object_version_number => l_object_version_number
998 );
999 */
1000 -- Create default budget values for the assignment
1001 create_default_budget_values ( p_business_group_id,
1002 l_assignment_id,
1003 p_effective_start_date => p_date_received,
1004 p_effective_end_date => l_asg_end_date) ;
1005 --
1006 hr_utility.set_location('PER_APPLICANT_PKG.INSERT_ROW' , 5 ) ;
1007 update_group ( p_people_group_id,
1008 p_people_group_name ) ;
1009 --
1010 -- Create letter request
1011 check_for_letter_requests ( p_business_group_id => p_business_group_id,
1012 p_per_system_status => p_per_system_status,
1013 p_assignment_status_type_id => p_assignment_status_type_id,
1014 p_person_id => p_person_id,
1015 p_assignment_id => l_assignment_id,
1016 p_effective_start_date => p_effective_start_date,
1017 p_validation_start_date=> p_effective_start_date,
1018 p_vacancy_id => p_vacancy_id
1019 ) ;
1020 --
1021 end insert_row ;
1022 --
1023 procedure lock_row(p_rowid VARCHAR2
1024 ,p_person_id NUMBER
1025 ,p_effective_start_date DATE
1026 ,p_effective_end_date DATE
1027 ,p_business_group_id NUMBER
1028 ,p_person_type_id NUMBER
1029 ,p_last_name VARCHAR2
1030 ,p_start_date DATE
1031 ,p_applicant_number VARCHAR2
1032 ,p_comment_id NUMBER
1033 ,p_current_applicant_flag VARCHAR2
1034 ,p_current_emp_or_apl_flag VARCHAR2
1035 ,p_current_employee_flag VARCHAR2
1036 ,p_date_employee_data_verified DATE
1037 ,p_date_of_birth DATE
1038 ,p_email_address VARCHAR2
1039 ,p_employee_number VARCHAR2
1040 ,p_expense_check_send_to_addr VARCHAR2
1041 ,p_first_name VARCHAR2
1042 ,p_full_name VARCHAR2
1043 ,p_known_as VARCHAR2
1044 ,p_marital_status VARCHAR2
1045 ,p_middle_names VARCHAR2
1046 ,p_nationality VARCHAR2
1047 ,p_national_identifier VARCHAR2
1048 ,p_previous_last_name VARCHAR2
1049 ,p_registered_disabled_flag VARCHAR2
1050 ,p_sex VARCHAR2
1051 ,p_title VARCHAR2
1052 ,p_vendor_id NUMBER
1053 ,p_work_telephone VARCHAR2
1054 ,p_attribute_category VARCHAR2
1055 ,p_attribute1 VARCHAR2
1056 ,p_attribute2 VARCHAR2
1057 ,p_attribute3 VARCHAR2
1058 ,p_attribute4 VARCHAR2
1059 ,p_attribute5 VARCHAR2
1060 ,p_attribute6 VARCHAR2
1061 ,p_attribute7 VARCHAR2
1062 ,p_attribute8 VARCHAR2
1063 ,p_attribute9 VARCHAR2
1064 ,p_attribute10 VARCHAR2
1065 ,p_attribute11 VARCHAR2
1066 ,p_attribute12 VARCHAR2
1067 ,p_attribute13 VARCHAR2
1068 ,p_attribute14 VARCHAR2
1069 ,p_attribute15 VARCHAR2
1070 ,p_attribute16 VARCHAR2
1071 ,p_attribute17 VARCHAR2
1072 ,p_attribute18 VARCHAR2
1073 ,p_attribute19 VARCHAR2
1074 ,p_attribute20 VARCHAR2
1075 ,p_attribute21 VARCHAR2
1076 ,p_attribute22 VARCHAR2
1077 ,p_attribute23 VARCHAR2
1078 ,p_attribute24 VARCHAR2
1079 ,p_attribute25 VARCHAR2
1080 ,p_attribute26 VARCHAR2
1081 ,p_attribute27 VARCHAR2
1082 ,p_attribute28 VARCHAR2
1083 ,p_attribute29 VARCHAR2
1084 ,p_attribute30 VARCHAR2
1085 ,p_per_information_category VARCHAR2
1086 ,p_per_information1 VARCHAR2
1087 ,p_per_information2 VARCHAR2
1088 ,p_per_information3 VARCHAR2
1089 ,p_per_information4 VARCHAR2
1090 ,p_per_information5 VARCHAR2
1091 ,p_per_information6 VARCHAR2
1092 ,p_per_information7 VARCHAR2
1093 ,p_per_information8 VARCHAR2
1094 ,p_per_information9 VARCHAR2
1095 ,p_per_information10 VARCHAR2
1096 ,p_per_information11 VARCHAR2
1097 ,p_per_information12 VARCHAR2
1098 ,p_per_information13 VARCHAR2
1099 ,p_per_information14 VARCHAR2
1100 ,p_per_information15 VARCHAR2
1101 ,p_per_information16 VARCHAR2
1102 ,p_per_information17 VARCHAR2
1103 ,p_per_information18 VARCHAR2
1104 ,p_per_information19 VARCHAR2
1105 ,p_per_information20 VARCHAR2
1106 ,p_per_information21 VARCHAR2
1107 ,p_per_information22 VARCHAR2
1108 ,p_per_information23 VARCHAR2
1109 ,p_per_information24 VARCHAR2
1110 ,p_per_information25 VARCHAR2
1111 ,p_per_information26 VARCHAR2
1112 ,p_per_information27 VARCHAR2
1113 ,p_per_information28 VARCHAR2
1114 ,p_per_information29 VARCHAR2
1115 ,p_per_information30 VARCHAR2) is
1116 begin
1117 --
1118 -- Call people lock row
1119 per_people_pkg.lock_row(
1120 p_rowid => p_rowid,
1121 p_person_id => p_person_id ,
1122 p_effective_start_date => p_effective_start_date,
1123 p_effective_end_date => p_effective_end_date,
1124 p_business_group_id => p_business_group_id ,
1125 p_person_type_id => p_person_type_id ,
1126 p_last_name => p_last_name,
1127 p_start_date => p_start_date,
1128 p_applicant_number => p_applicant_number ,
1129 p_comment_id => p_comment_id,
1130 p_current_applicant_flag => p_current_applicant_flag,
1131 p_current_emp_or_apl_flag => p_current_emp_or_apl_flag,
1132 p_current_employee_flag => p_current_employee_flag,
1133 p_date_employee_data_verified => p_date_employee_data_verified,
1134 p_date_of_birth => p_date_of_birth,
1135 p_email_address => p_email_address,
1136 p_employee_number => p_employee_number,
1137 p_expense_check_send_to_addr => p_expense_check_send_to_addr,
1138 p_first_name => p_first_name,
1139 p_full_name => p_full_name,
1140 p_known_as => p_known_as,
1141 p_marital_status => p_marital_status,
1142 p_middle_names => p_middle_names,
1143 p_nationality => p_nationality,
1144 p_national_identifier => p_national_identifier,
1145 p_previous_last_name => p_previous_last_name,
1146 p_registered_disabled_flag => p_registered_disabled_flag,
1147 p_sex => p_sex ,
1148 p_title => p_title ,
1149 p_suffix => NULL,
1150 p_vendor_id => p_vendor_id,
1151 p_work_telephone => p_work_telephone ,
1152 p_a_cat => p_attribute_category ,
1153 p_a1 => p_attribute1,
1154 p_a2 => p_attribute2,
1155 p_a3 => p_attribute3,
1156 p_a4 => p_attribute4,
1157 p_a5 => p_attribute5,
1158 p_a6 => p_attribute6,
1159 p_a7 => p_attribute7,
1160 p_a8 => p_attribute8,
1161 p_a9 => p_attribute9,
1162 p_a10 => p_attribute10,
1163 p_a11 => p_attribute11,
1164 p_a12 => p_attribute12,
1165 p_a13 => p_attribute13,
1166 p_a14 => p_attribute14,
1167 p_a15 => p_attribute15,
1168 p_a16 => p_attribute16,
1169 p_a17 => p_attribute17,
1170 p_a18 => p_attribute18,
1171 p_a19 => p_attribute19,
1172 p_a20 => p_attribute20,
1173 p_a21 => p_attribute21,
1174 p_a22 => p_attribute22,
1175 p_a23 => p_attribute23,
1176 p_a24 => p_attribute24,
1177 p_a25 => p_attribute25,
1178 p_a26 => p_attribute26,
1179 p_a27 => p_attribute27,
1180 p_a28 => p_attribute28,
1181 p_a29 => p_attribute29,
1182 p_a30 => p_attribute30,
1183 p_i_cat => p_per_information_category ,
1184 p_i1 => p_per_information1 ,
1185 p_i2 => p_per_information2,
1186 p_i3 => p_per_information3,
1187 p_i4 => p_per_information4,
1188 p_i5 => p_per_information5,
1189 p_i6 => p_per_information6,
1190 p_i7 => p_per_information7,
1191 p_i8 => p_per_information8,
1192 p_i9 => p_per_information9,
1193 p_i10 => p_per_information10,
1194 p_i11 => p_per_information11,
1195 p_i12 => p_per_information12,
1196 p_i13 => p_per_information13,
1197 p_i14 => p_per_information14,
1198 p_i15 => p_per_information15,
1199 p_i16 => p_per_information16,
1200 p_i17 => p_per_information17,
1201 p_i18 => p_per_information18,
1202 p_i19 => p_per_information19,
1203 p_i20 => p_per_information20,
1204 p_i21 => p_per_information21,
1205 p_i22 => p_per_information22,
1206 p_i23 => p_per_information23,
1207 p_i24 => p_per_information24,
1208 p_i25 => p_per_information25,
1209 p_i26 => p_per_information26,
1210 p_i27 => p_per_information27,
1211 p_i28 => p_per_information28,
1212 p_i29 => p_per_information29,
1213 p_i30 => p_per_information30
1214 ) ;
1215 --
1216 end lock_row ;
1217 --
1218 -- Fix for 3908271 starts here. Comment out the delete
1219 -- proc as a new API procedure hr_person_api.delete_person
1220 -- is avilable for deleting a person.
1221 --
1222 /*
1223 procedure delete_row ( p_person_id in number,
1224 p_session_date in date ) is
1225 begin
1226 --
1227 check_delete_allowed ( p_person_id => p_person_id,
1228 p_session_date => p_session_date ) ;
1229 --
1230 hr_person.applicant_default_deletes( p_person_id => p_person_id,
1231 p_form_call => TRUE);
1232 --
1233 end delete_row ;
1234 */
1235 --
1236 -- Fix for 3908271 ends here.
1237 --
1238 -- Retrieve the location code for the given location id
1239 function get_location_code ( p_location_id in number ) return varchar2 is
1240 -- length increased to 60 from 20 for UTF8 compatibility
1241 l_return_value varchar2(60) := NULL ;
1242 --
1243 cursor c1 is
1244 select location_code
1245 from hr_locations
1246 where location_id = p_location_id ;
1247 --
1248 begin
1249 --
1250 open c1 ;
1251 fetch c1 into l_return_value ;
1252 close c1 ;
1253 --
1254 return( l_return_value ) ;
1255 end get_location_code ;
1256 --
1257 -- overload provided for get_db_default_values
1258 --
1259 procedure get_db_default_values (p_business_group_id in number ,
1260 p_legislation_code in varchar2,
1261 p_bg_name in out nocopy varchar2,
1262 p_bg_location_id in out nocopy number,
1263 p_bg_working_hours in out nocopy number,
1264 p_bg_frequency in out nocopy varchar2,
1265 p_bg_default_start_time in out nocopy varchar2,
1266 p_bg_default_end_time in out nocopy varchar2,
1267 p_system_person_type in out nocopy varchar2,
1268 p_person_type_id in out nocopy number,
1269 p_ass_status_type_id in out nocopy number,
1270 p_ass_status_type_desc in out nocopy varchar2,
1271 p_ass_per_system_status in out nocopy varchar2,
1272 p_country_meaning in out nocopy varchar2,
1273 p_default_yes_desc in out nocopy varchar2,
1274 p_default_no_desc in out nocopy varchar2,
1275 p_people_group_structure in out nocopy varchar2,
1276 p_method_of_apl_gen in out nocopy varchar2) is
1277 --
1278 l_style fnd_descr_flex_contexts_vl.descriptive_flex_context_code%TYPE;
1279 --
1280 begin
1281 get_db_default_values (p_business_group_id => p_business_group_id
1282 ,p_legislation_code => p_legislation_code
1283 ,p_bg_name => p_bg_name
1284 ,p_bg_location_id => p_bg_location_id
1285 ,p_bg_working_hours => p_bg_working_hours
1286 ,p_bg_frequency => p_bg_frequency
1287 ,p_bg_default_start_time => p_bg_default_start_time
1288 ,p_bg_default_end_time => p_bg_default_end_time
1289 ,p_system_person_type => p_system_person_type
1290 ,p_person_type_id => p_person_type_id
1291 ,p_ass_status_type_id => p_ass_status_type_id
1292 ,p_ass_status_type_desc => p_ass_status_type_desc
1293 ,p_ass_per_system_status => p_ass_per_system_status
1294 ,p_country_meaning => p_country_meaning
1295 ,p_default_yes_desc => p_default_yes_desc
1296 ,p_default_no_desc => p_default_no_desc
1297 ,p_people_group_structure => p_people_group_structure
1298 ,p_method_of_apl_gen => p_method_of_apl_gen
1299 ,p_style => l_style);
1300 end;
1301 --
1302 procedure get_db_default_values (p_business_group_id in number ,
1303 p_legislation_code in varchar2,
1304 p_bg_name in out nocopy varchar2,
1305 p_bg_location_id in out nocopy number,
1306 p_bg_working_hours in out nocopy number,
1307 p_bg_frequency in out nocopy varchar2,
1308 p_bg_default_start_time in out nocopy varchar2,
1309 p_bg_default_end_time in out nocopy varchar2,
1310 p_system_person_type in out nocopy varchar2,
1311 p_person_type_id in out nocopy number,
1312 p_ass_status_type_id in out nocopy number,
1313 p_ass_status_type_desc in out nocopy varchar2,
1314 p_ass_per_system_status in out nocopy varchar2,
1315 p_country_meaning in out nocopy varchar2,
1316 p_default_yes_desc in out nocopy varchar2,
1317 p_default_no_desc in out nocopy varchar2,
1318 p_people_group_structure in out nocopy varchar2,
1319 p_method_of_apl_gen in out nocopy varchar2,
1320 p_style in out nocopy varchar2) is
1321 --
1322 l_geocodes_installed varchar2(1);
1323 l_default varchar2(80);
1324 l_default_code varchar2(30);
1325 --
1326 -- Bug fix 3648715
1327 -- cursor c1 modified - '+0' removed to improve performance.
1328
1329 cursor c1 is
1330 select person_type_id,
1331 system_person_type
1332 from per_person_types
1333 where business_group_id = p_business_group_id
1334 and system_person_type = 'APL'
1335 and active_flag = 'Y'
1336 and default_flag = 'Y' ;
1337 --
1338 cursor c2 is
1339 select a.assignment_status_type_id
1340 , nvl(btl.user_status,atl.user_status)
1341 , a.per_system_status
1342 from per_assignment_status_types_tl atl
1343 , per_assignment_status_types a
1344 , per_ass_status_type_amends_tl btl
1345 , per_ass_status_type_amends b
1346 where b.assignment_status_type_id(+) = a.assignment_status_type_id
1347 and b.business_group_id(+) + 0 = p_business_group_id
1348 and nvl(a.business_group_id,p_business_group_id) = p_business_group_id
1349 and nvl(a.legislation_code,p_legislation_code) = p_legislation_code
1350 and nvl(b.active_flag,a.active_flag) = 'Y'
1351 and nvl(b.default_flag,a.default_flag) = 'Y'
1352 and nvl(b.per_system_status,a.per_system_status) = 'ACTIVE_APL'
1353 and b.ass_status_type_amend_id = btl.ass_status_type_amend_id (+)
1354 and decode(btl.ass_status_type_amend_id, null, '1', userenv('LANG')) =
1355 decode(btl.ass_status_type_amend_id, null, '1', btl.LANGUAGE)
1356 and a.assignment_status_type_id = atl.assignment_status_type_id
1357 and atl.LANGUAGE = userenv('LANG');
1358 --
1359 -- Bug fix 3648715
1360 -- cursor c3 modified - "application_id = 800" added to improve performance.
1361
1362 cursor c3 is
1363 select descriptive_flex_context_name, descriptive_flex_context_code
1364 from fnd_descr_flex_contexts_vl
1365 where (descriptive_flex_context_code = p_legislation_code
1366 or (p_legislation_code = descriptive_flex_context_code
1367 and p_legislation_code in ('CA','US')
1368 and hr_general.chk_geocodes_installed = 'Y'))
1369 and descriptive_flexfield_name = 'Address Structure'
1370 and application_id = 800 -- Bug fix 3648715
1371 and enabled_flag = 'Y';
1372 --
1373 -- Bug fix 3648715
1374 -- cursor c6 modified - "application_id = 800" added to improve performance.
1375
1376 cursor c6 is
1377 select descriptive_flex_context_name,descriptive_flex_context_code
1378 from fnd_descr_flex_contexts_vl
1379 where substr(descriptive_flex_context_code,1,2)= p_legislation_code
1380 and descriptive_flexfield_name = 'Address Structure'
1381 and application_id = 800 -- Bug Fix 3648715
1382 and enabled_flag = 'Y';
1383 --
1384 cursor c4 is
1385 select l1.meaning,
1386 l2.meaning
1387 from hr_lookups l1,
1388 hr_lookups l2
1389 where l1.lookup_type = 'YES_NO'
1390 and l2.lookup_type = 'YES_NO'
1391 and l1.lookup_code = 'Y'
1392 and l2.lookup_code = 'N' ;
1393 --
1394 cursor c5 is
1395 select pbg.name,
1396 pbg.location_id,
1397 fnd_number.canonical_to_number(pbg.working_hours),
1398 pbg.frequency,
1399 pbg.default_start_time,
1400 pbg.default_end_time,
1401 pbg.people_group_structure,
1402 pbg.method_of_generation_apl_num
1403 from per_business_groups pbg
1404 where business_group_id = p_business_group_id ;
1405 begin
1406 --
1407 hr_utility.set_location('PER_APPLICANT_PKG.GET_DB_DEFAULT_VALUES' , 1 ) ;
1408 -- Retrieve the person type for a system person type of APL
1409 open c1 ;
1410 fetch c1 into p_person_type_id,
1411 p_system_person_type ;
1412 close c1 ;
1413 -- Retrieve the Assignment Status Type defaults
1414 open c2 ;
1415 fetch c2 into p_ass_status_type_id,
1416 p_ass_status_type_desc,
1417 p_ass_per_system_status ;
1418 close c2 ;
1419 --
1420 -- Retrieve the default country to use for addresses
1421 open c3;
1422 fetch c3 into l_default,l_default_code;
1423 if c3%notfound then
1424 open c6;
1425 fetch c6 into l_default,l_default_code;
1426 close c6;
1427 end if;
1428 close c3;
1429 --
1430 p_country_meaning := l_default;
1431 p_style := l_default_code;
1432 --
1433 --
1434 -- Retrieve the default yes and no descriptions
1435 open c4 ;
1436 fetch c4 into p_default_yes_desc,p_default_no_desc ;
1437 close c4 ;
1438 --
1439 -- Retrieve business group defaults
1440 open c5 ;
1441 fetch c5 into p_bg_name,
1442 p_bg_location_id,
1443 p_bg_working_hours,
1444 p_bg_frequency,
1445 p_bg_default_start_time,
1446 p_bg_default_end_time,
1447 p_people_group_structure ,
1448 p_method_of_apl_gen ;
1449 close c5 ;
1450 --
1451 end get_db_default_values ;
1452 --
1453 --
1454 --
1455 function vacancy_in_activity ( p_recruitment_activity_id in number,
1456 p_vacancy_id in number )
1457 return boolean is
1458 l_return_value boolean := FALSE ;
1459 l_dummy number ;
1460 cursor c1 is
1461 select 1
1462 from per_recruitment_activity_for
1463 where recruitment_activity_id = p_recruitment_activity_id
1464 and vacancy_id = p_vacancy_id ;
1465 begin
1466 --
1467 open c1 ;
1468 fetch c1 into l_dummy ;
1469 l_return_value := c1%found ;
1470 close c1 ;
1471 return ( l_return_value ) ;
1472 --
1473 end vacancy_in_activity ;
1474 --
1475 --
1476 --
1477 function uniq_vac_for_rec_act ( p_recruitment_activity_id in number,
1478 p_date_received in date )
1479 return number is
1480 cursor c1 is
1481 select min(r.vacancy_id)
1482 from per_recruitment_activity_for r,
1483 per_all_vacancies x
1484 where r.recruitment_activity_id = p_recruitment_activity_id
1485 and r.vacancy_id = x.vacancy_id
1486 and p_date_received between x.date_from
1487 and nvl(x.date_to,p_date_received)
1488 group by r.recruitment_activity_id
1489 having count(r.vacancy_id) = 1 ;
1490 --
1491 l_vacancy_id number := null ;
1492 --
1493 begin
1494 open c1 ;
1495 fetch c1 into l_vacancy_id ;
1496 if c1%notfound then
1497 l_vacancy_id := null ;
1498 end if ;
1499 close c1 ;
1500 --
1501 return ( l_vacancy_id ) ;
1502 --
1503 end uniq_vac_for_rec_act ;
1504 --
1505 --
1506 --
1507 procedure get_vacancy_details ( p_vacancy_id in number,
1508 p_vacancy_name in out nocopy varchar2,
1509 p_recruiter_id in out nocopy number,
1510 p_recruiter_name in out nocopy varchar2,
1511 p_org_id in out nocopy number,
1512 p_org_name in out nocopy varchar2,
1513 p_people_group_id in out nocopy number,
1514 p_job_id in out nocopy number,
1515 p_job_name in out nocopy varchar2,
1516 p_pos_id in out nocopy number,
1517 p_pos_name in out nocopy varchar2,
1518 p_grade_id in out nocopy number,
1519 p_grade_name in out nocopy varchar2,
1520 p_location_id in out nocopy number,
1521 p_location_code in out nocopy varchar2,
1522 p_recruiter_number in out nocopy varchar2 ) is
1523 cursor get_vacancy is
1524 select x.name,
1525 x.organization_id,
1526 x.people_group_id,
1527 x.job_id,
1528 x.position_id,
1529 x.grade_id,
1530 x.location_id,
1531 x.recruiter_id
1532 from per_all_vacancies x
1533 where x.vacancy_id = p_vacancy_id ;
1534 --
1535 cursor get_organization is
1536 select xtl.name
1537 from hr_all_organization_units_tl xtl,
1538 hr_all_organization_units x
1539 where xtl.organization_id = p_org_id
1540 and x.organization_id = xtl.organization_id
1541 and xtl.LANGUAGE = userenv('LANG');
1542 --
1543 cursor get_recruiter is
1544 select per.full_name
1545 ,per.employee_number
1546 from per_all_people per
1547 where per.person_id = p_recruiter_id;
1548 --
1549 cursor get_job is
1550 select x.name
1551 from per_jobs_vl x
1552 where x.job_id = p_job_id ;
1553 --
1554 -- Changed 01-Oct-99 SCNair (per_all_positions to hr_all_positions) date track requirement
1555 --
1556 cursor get_position is
1557 select x.name
1558 from hr_all_positions x
1559 where x.position_id = p_pos_id ;
1560 --
1561 cursor get_grade is
1562 select x.name
1563 from per_grades_vl x
1564 where x.grade_id = p_grade_id ;
1565 --
1566 cursor get_location is
1567 select l.location_code
1568 from hr_locations l
1569 where l.location_id = p_location_id ;
1570 --
1571 begin
1572 if p_vacancy_name is null then
1573 open get_vacancy ;
1574 fetch get_vacancy into p_vacancy_name,
1575 p_org_id,
1576 p_people_group_id,
1577 p_job_id,
1578 p_pos_id,
1579 p_grade_id,
1580 p_location_id,
1581 p_recruiter_id ;
1582 close get_vacancy ;
1583 end if ;
1584 --
1585 -- Retrieve descriptions
1586 --
1587 -- Recruiter
1588 if p_recruiter_id is not null then
1589 open get_recruiter ;
1590 fetch get_recruiter into p_recruiter_name
1591 ,p_recruiter_number ;
1592 close get_recruiter ;
1593 else
1594 p_recruiter_name := null ;
1595 p_recruiter_number := null;
1596 end if ;
1597 --
1598 -- Organization
1599 if p_org_id is not null then
1600 open get_organization ;
1601 fetch get_organization into p_org_name ;
1602 close get_organization ;
1603 else
1604 p_org_name := null ;
1605 end if ;
1606 --
1607 -- Job
1608 if p_job_id is not null then
1609 open get_job ;
1610 fetch get_job into p_job_name ;
1611 close get_job ;
1612 else
1613 p_job_name := null ;
1614 end if ;
1615 --
1616 -- Position
1617 if p_pos_id is not null then
1618 open get_position ;
1619 fetch get_position into p_pos_name ;
1620 close get_position ;
1621 else
1622 p_pos_name := null ;
1623 end if ;
1624 --
1625 -- Grade
1626 if p_grade_id is not null then
1627 open get_grade ;
1628 fetch get_grade into p_grade_name ;
1629 close get_grade ;
1630 else
1631 p_grade_name := null ;
1632 end if ;
1633 --
1634 -- Location
1635 if p_location_id is not null then
1636 open get_location ;
1637 fetch get_location into p_location_code ;
1638 close get_location ;
1639 else
1640 p_location_code := null ;
1641 end if ;
1642 --
1643 end get_vacancy_details ;
1644 --
1645 -- Provide overload for get_vacancy_details
1646 --
1647 procedure get_vacancy_details ( p_vacancy_id in number,
1648 p_vacancy_name in out nocopy varchar2,
1649 p_recruiter_id in out nocopy number,
1650 p_recruiter_name in out nocopy varchar2,
1651 p_org_id in out nocopy number,
1652 p_org_name in out nocopy varchar2,
1653 p_people_group_id in out nocopy number,
1654 p_job_id in out nocopy number,
1655 p_job_name in out nocopy varchar2,
1656 p_pos_id in out nocopy number,
1657 p_pos_name in out nocopy varchar2,
1658 p_grade_id in out nocopy number,
1659 p_grade_name in out nocopy varchar2,
1660 p_location_id in out nocopy number,
1661 p_location_code in out nocopy varchar2) is
1662
1663 l_recruiter_number PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE;
1664
1665 begin
1666 get_vacancy_details (p_vacancy_id => p_vacancy_id,
1667 p_vacancy_name => p_vacancy_name,
1668 p_recruiter_id => p_recruiter_id,
1669 p_recruiter_name => p_recruiter_name,
1670 p_org_id => p_org_id,
1671 p_org_name => p_org_name,
1672 p_people_group_id => p_people_group_id,
1673 p_job_id => p_job_id,
1674 p_job_name => p_job_name,
1675 p_pos_id => p_pos_id,
1676 p_pos_name => p_pos_name,
1677 p_grade_id => p_grade_id,
1678 p_grade_name => p_grade_name,
1679 p_location_id => p_location_id,
1680 p_location_code => p_location_code,
1681 p_recruiter_number => l_recruiter_number);
1682 end get_vacancy_details;
1683 --
1684 --
1685 procedure set_vac_from_rec_act ( p_recruitment_activity_id in number,
1686 p_date_received in date,
1687 p_vacancy_id in out nocopy number,
1688 p_vacancy_name in out nocopy varchar2,
1689 p_recruiter_id in out nocopy number,
1690 p_recruiter_name in out nocopy varchar2,
1691 p_org_id in out nocopy number,
1692 p_org_name in out nocopy varchar2,
1693 p_people_group_id in out nocopy number,
1694 p_job_id in out nocopy number,
1695 p_job_name in out nocopy varchar2,
1696 p_pos_id in out nocopy number,
1697 p_pos_name in out nocopy varchar2,
1698 p_grade_id in out nocopy number,
1699 p_grade_name in out nocopy varchar2,
1700 p_location_id in out nocopy number,
1701 p_location_code in out nocopy varchar2 ) is
1702 l_recruiter_number PER_ALL_PEOPLE.EMPLOYEE_NUMBER%TYPE;
1703
1704 begin
1705 --
1706 -- Check that the vacancy is unique
1707 p_vacancy_id := uniq_vac_for_rec_act ( p_recruitment_activity_id,
1708 p_date_received ) ;
1709 --
1710 -- Get the details if there is a unique vacancy
1711 --
1712 if ( p_vacancy_id is not null ) then
1713 get_vacancy_details ( p_vacancy_id => p_vacancy_id,
1714 p_vacancy_name => p_vacancy_name,
1715 p_recruiter_id => p_recruiter_id,
1716 p_recruiter_name => p_recruiter_name,
1717 p_org_id => p_org_id,
1718 p_org_name => p_org_name,
1719 p_people_group_id => p_people_group_id,
1720 p_job_id => p_job_id,
1721 p_job_name => p_job_name,
1722 p_pos_id => p_pos_id,
1723 p_pos_name => p_pos_name,
1724 p_grade_id => p_grade_id,
1725 p_grade_name => p_grade_name,
1726 p_location_id => p_location_id,
1727 p_location_code => p_location_code,
1728 p_recruiter_number=> l_recruiter_number ) ;
1729 end if ;
1730 --
1731 end set_vac_from_rec_act ;
1732 --
1733 --
1734 --
1735 function chk_job_org_pos_comb ( p_job_id in number,
1736 p_org_id in number,
1737 p_pos_id in number,
1738 p_date_received in date ) return boolean is
1739 l_return_value boolean := FALSE ;
1740 l_dummy number ;
1741 --
1742 -- Changed 01-Oct-99 SCNair (per_all_positions to hr_all_positions) date track requirement.
1743 cursor c1 is
1744 SELECT 1
1745 FROM HR_ALL_POSITIONS P
1746 WHERE P.JOB_ID = p_job_id
1747 AND P.ORGANIZATION_ID = p_org_id
1748 AND P.POSITION_ID = p_pos_id
1749 AND P.DATE_EFFECTIVE <= p_date_received
1750 AND ((P.DATE_END IS NULL)
1751 OR (P.DATE_END >= p_date_received )) ;
1752 begin
1753 --
1754 open c1 ;
1755 fetch c1 into l_dummy ;
1756 l_return_value := c1%found ;
1757 close c1 ;
1758 --
1759 return ( l_return_value ) ;
1760 --
1761 end chk_job_org_pos_comb ;
1762 --
1763 procedure update_group( p_pg_id number,
1764 p_group_name varchar2) is
1765 begin
1766 if p_pg_id <> -1 then
1767 --
1768 -- This is an existing desc flex record, update group_name held on
1769 -- combinations table.
1770 update pay_people_groups
1771 set group_name = P_GROUP_NAME
1772 where people_group_id = P_PG_ID
1773 and P_GROUP_NAME is not null; -- 4103321
1774 --
1775 if sql%rowcount = 0 then
1776 fnd_message.set_name('PAY','HR_6153_ALL_PROCEDURE_FAIL' );
1777 fnd_message.set_token('PROCEDURE','PER_APPLICANT_PKG.UPDATE_GROUP');
1778 fnd_message.set_token('STEP','1' ) ;
1779 fnd_message.raise_error ;
1780 end if ;
1781 end if;
1782 end update_group ;
1783 --
1784 procedure exists_val_grd_for_pos_and_job ( p_business_group_id in number,
1785 p_date_received in date,
1786 p_job_id in number,
1787 p_exists_grd_for_job out nocopy boolean,
1788 p_pos_id in number,
1789 p_exists_grd_for_pos out nocopy boolean ) is
1790 --
1791 l_dummy_number number ; -- Used to discard output from cursor
1792 --
1793 cursor c1 is
1794 SELECT 1
1795 FROM SYS.DUAL
1796 WHERE EXISTS
1797 (SELECT NULL
1798 FROM PER_VALID_GRADES
1799 WHERE business_group_id + 0 = p_business_group_id
1800 AND DATE_FROM <= p_date_received
1801 AND ((DATE_TO IS NULL)
1802 OR (DATE_TO >= p_date_received))
1803 AND JOB_ID = p_job_id ) ;
1804 --
1805 cursor c2 is
1806 SELECT 1
1807 FROM SYS.DUAL
1808 WHERE EXISTS
1809 (SELECT NULL
1810 FROM PER_VALID_GRADES
1811 WHERE business_group_id + 0 = p_business_group_id
1812 AND DATE_FROM <= p_date_received
1813 AND ((DATE_TO IS NULL)
1814 OR (DATE_TO >= p_date_received))
1815 AND POSITION_ID = p_pos_id) ;
1816 --
1817 --
1818 begin
1819 --
1820 open c1 ;
1821 fetch c1 into l_dummy_number ;
1822 p_exists_grd_for_job := c1%found ;
1823 close c1 ;
1824 --
1825 if p_pos_id is null then
1826 p_exists_grd_for_pos := FALSE ;
1827 else
1828 open c2 ;
1829 fetch c2 into l_dummy_number ;
1830 p_exists_grd_for_pos := c2%found ;
1831 close c2 ;
1832 end if ;
1833 --
1834 end exists_val_grd_for_pos_and_job ;
1835 --
1836 -- ***TEMP Probably obsolete
1837 procedure check_apl_num_unique( p_business_group_id in number,
1838 p_applicant_number in number ) is
1839 l_dummy_number number ;
1840 cursor c1 is
1841 SELECT 1
1842 FROM PER_ALL_PEOPLE_F
1843 WHERE APPLICANT_NUMBER = p_applicant_number
1844 AND business_group_id + 0 = p_business_group_id ;
1845 begin
1846 --
1847 open c1 ;
1848 fetch c1 into l_dummy_number ;
1849 if c1%found then
1850 close c1 ;
1851 hr_utility.set_message('PAY','HR_6413_APPL_NO_EXISTS');
1852 hr_utility.raise_error ;
1853 end if ;
1854 close c1 ;
1855 --
1856 end check_apl_num_unique ;
1857 --
1858 procedure check_delete_allowed ( p_person_id in number,
1859 p_session_date in date ) is
1860 --
1861 l_dummy_number number := NULL ;
1862 cursor c1 is
1863 select 1
1864 from per_all_people_f
1865 where person_id = p_person_id
1866 group by person_id having count(*) > 1 ;
1867 --
1868 cursor c2 is
1869 select 1
1870 from per_addresses
1871 where person_id = p_person_id
1872 group by person_id having count(*) > 1 ;
1873 begin
1874 --
1875 open c1 ;
1876 fetch c1 into l_dummy_number ;
1877 if c1%found then
1878 close c1 ;
1879 hr_utility.set_message(801,'HR_6414_APPL_DATE_EFFECTIVE' );
1880 hr_utility.raise_error ;
1881 end if;
1882 close c1 ;
1883 --
1884 open c2 ;
1885 fetch c2 into l_dummy_number ;
1886 if c2%found then
1887 close c2 ;
1888 hr_utility.set_message(801,'HR_6415_APPL_ADDRESS' );
1889 hr_utility.raise_error ;
1890 end if;
1891 close c2 ;
1892 --
1893 -- Fix for 3908271 starts here.
1894 -- The Applicant Quick entry form is using this validation proc.
1895 -- Now the form is using API proc hr_person_api.delete_person which
1896 -- includes the strong predel validations. So comment out here.
1897 --
1898 /*
1899 -- Standard strong delete check
1900 hr_person.strong_predel_validation ( p_person_id => p_person_id,
1901 p_session_date => p_session_date ) ;
1902 */
1903 --
1904 -- Fix for 3908271 ends here.
1905 --
1906 --
1907 end check_delete_allowed ;
1908 --
1909 procedure create_default_budget_values ( p_business_group_id in number,
1910 p_assignment_id in number,
1911 p_effective_start_date in date,
1912 p_effective_end_date in date) is
1913 begin
1914 --
1915 insert into per_assignment_budget_values_f
1916 (assignment_budget_value_id,
1917 effective_start_date,
1918 effective_end_date,
1919 business_group_id,
1920 assignment_id,
1921 unit,
1922 value)
1923 select per_assignment_budget_values_s.nextval,
1924 p_effective_start_date,
1925 p_effective_end_date,
1926 p_business_group_id,
1927 p_assignment_id,
1928 d.unit,
1929 d.value
1930 from per_default_budget_values d
1931 where d.business_group_id + 0 = p_business_group_id ;
1932 --
1933 end create_default_budget_values ;
1934 --
1935 procedure check_for_letter_requests ( p_business_group_id in number,
1936 p_per_system_status in varchar2,
1937 p_assignment_status_type_id in number,
1938 p_person_id in number,
1939 p_assignment_id in number,
1940 p_effective_start_date in date,
1941 p_validation_start_date in date,
1942 p_vacancy_id in number) is
1943 l_dummy_number number ;
1944 cursor check_statuses is
1945 select 1
1946 from per_letter_gen_statuses s
1947 where s.business_group_id + 0 = p_business_group_id
1948 and s.assignment_status_type_id = p_assignment_status_type_id
1949 and s.enabled_flag = 'Y' ;
1950 --
1951 -- Fix for bug 3680947 starts here.
1952 --
1953 CURSOR csr_check_manual_or_auto IS
1954 SELECT 1
1955 FROM PER_LETTER_REQUESTS PLR,
1956 PER_LETTER_GEN_STATUSES PLGS
1957 WHERE PLGS.business_group_id + 0 = p_business_group_id
1958 AND PLR.business_group_id +0 = p_business_group_id
1959 AND PLGS.assignment_status_type_id = p_assignment_status_type_id
1960 AND PLR.letter_type_id = PLGS.letter_type_id
1961 AND PLR.auto_or_manual = 'MANUAL';
1962 --
1963 -- Fix for bug 3680947 ends here.
1964 --
1965 begin
1966 --
1967 open check_statuses ;
1968 fetch check_statuses into l_dummy_number ;
1969 if check_statuses%notfound then
1970 close check_statuses ;
1971 return ;
1972 end if ;
1973 close check_statuses ;
1974 --
1975 -- Fix for bug 3680947 starts here.
1976 --
1977 open csr_check_manual_or_auto;
1978 fetch csr_check_manual_or_auto into l_dummy_number;
1979 if csr_check_manual_or_auto%found then
1980 close csr_check_manual_or_auto;
1981 return;
1982 end if;
1983 close csr_check_manual_or_auto;
1984 --
1985 -- Fix for bug 3680947 ends here.
1986 --
1987 if (nvl(fnd_profile.value('HR_LETTER_BY_VACANCY'),'N')='Y') then
1988
1989 hr_utility.set_location('HR_LETTER_BY_VACANCY = Y',10);
1990
1991 insert into per_letter_requests
1992 ( letter_request_id,
1993 business_group_id,
1994 letter_type_id,
1995 request_status,
1996 auto_or_manual,
1997 date_from,
1998 vacancy_id)
1999 select per_letter_requests_s.nextval,
2000 p_business_group_id,
2001 s.letter_type_id,
2002 'PENDING',
2003 'AUTO',
2004 p_effective_start_date,
2005 p_vacancy_id
2006 from PER_LETTER_GEN_STATUSES s
2007 where s.business_group_id + 0 = p_business_group_id
2008 and s.assignment_status_type_id = p_assignment_status_type_id
2009 and s.enabled_flag = 'Y'
2010 and not exists ( select null
2011 from per_letter_requests r
2012 where r.letter_type_id = s.letter_type_id
2013 and r.business_group_id + 0 = p_business_group_id
2014 and r.business_group_id + 0 = s.business_group_id + 0
2015 and nvl(r.vacancy_id,-1) = nvl(p_vacancy_id,-1)
2016 and r.request_status = 'PENDING'
2017 and r.auto_or_manual = 'AUTO' ) ;
2018 --
2019 -- Create a letter request line
2020 --
2021 -- Bug fix 3648715
2022 -- Insert statment modified - 'r.business_group_id +0' changed to
2023 -- r.business_group_id to improve performance.
2024
2025 insert into per_letter_request_lines
2026 (letter_request_line_id,
2027 business_group_id,
2028 letter_request_id,
2029 person_id,
2030 assignment_id,
2031 assignment_status_type_id,
2032 date_from )
2033 select per_letter_request_lines_s.nextval,
2034 p_business_group_id,
2035 r.letter_request_id,
2036 p_person_id,
2037 p_assignment_id,
2038 p_assignment_status_type_id,
2039 p_validation_start_date
2040 from per_letter_requests r
2041 where exists
2042 (select null
2043 from per_letter_gen_statuses s
2044 where s.letter_type_id = r.letter_type_id
2045 and s.business_group_id + 0 = r.business_group_id + 0
2046 and s.assignment_status_type_id= p_assignment_status_type_id
2047 and s.enabled_flag = 'Y')
2048 and not exists
2049 (select l.assignment_id
2050 from per_letter_request_lines l
2051 where l.letter_request_id = r.letter_request_id
2052 and l.assignment_id = p_assignment_id
2053 and l.business_group_id + 0 = r.business_group_id + 0
2054 and l.date_from = p_validation_start_date)
2055 and r.request_status = 'PENDING'
2056 and r.business_group_id = p_business_group_id -- bug fix 3648715
2057 and nvl(r.vacancy_id,-1) = nvl(p_vacancy_id,-1);
2058
2059 else
2060
2061 -- Profile HR: Letter by Vacancy has not been set to Yes
2062
2063 insert into per_letter_requests
2064 (letter_request_id,
2065 business_group_id,
2066 letter_type_id,
2067 request_status,
2068 auto_or_manual,
2069 date_from)
2070 select
2071 per_letter_requests_s.nextval,
2072 p_business_group_id,
2073 s.letter_type_id,
2074 'PENDING',
2075 'AUTO',
2076 p_effective_start_date
2077 from PER_LETTER_GEN_STATUSES s
2078 where s.business_group_id + 0 = p_business_group_id
2079 and s.assignment_status_type_id = p_assignment_status_type_id
2080 and s.enabled_flag = 'Y'
2081 and not exists ( select null
2082 from per_letter_requests r
2083 where r.letter_type_id = s.letter_type_id
2084 and r.business_group_id + 0 = p_business_group_id
2085 and r.business_group_id + 0 = s.business_group_id + 0
2086 and r.request_status = 'PENDING'
2087 and r.auto_or_manual = 'AUTO' ) ;
2088
2089 --
2090 -- Create a letter request line
2091 --
2092 -- bug fix 3648715
2093 -- Insert statment modified - 'r.business_group_id +0' changed to
2094 -- r.business_group_id to improve performance.
2095
2096 insert into per_letter_request_lines
2097 (letter_request_line_id,
2098 business_group_id,
2099 letter_request_id,
2100 person_id,
2101 assignment_id,
2102 assignment_status_type_id,
2103 date_from )
2104 select
2105 per_letter_request_lines_s.nextval,
2106 p_business_group_id,
2107 r.letter_request_id,
2108 p_person_id,
2109 p_assignment_id,
2110 p_assignment_status_type_id,
2111 p_validation_start_date
2112 from per_letter_requests r
2113 where exists
2114 (select null
2115 from per_letter_gen_statuses s
2116 where s.letter_type_id = r.letter_type_id
2117 and s.business_group_id + 0 = r.business_group_id + 0
2118 and s.assignment_status_type_id = p_assignment_status_type_id
2119 and s.enabled_flag = 'Y')
2120 and not exists
2121 (select l.assignment_id
2122 from per_letter_request_lines l
2123 where l.letter_request_id = r.letter_request_id
2124 and l.assignment_id = p_assignment_id
2125 and l.business_group_id + 0 = r.business_group_id + 0
2126 and l.date_from = p_validation_start_date)
2127 and r.request_status = 'PENDING'
2128 and r.business_group_id = p_business_group_id ; -- bug fix 3648715
2129
2130 end if;
2131 --
2132 --
2133 end check_for_letter_requests ;
2134 --
2135 procedure pre_update ( p_rowid in varchar2,
2136 p_person_id in number,
2137 p_business_group_id in number,
2138 p_date_received in date,
2139 p_old_date_received in out nocopy date ) is
2140 l_system_person_type varchar2(30) ;
2141 cursor c1 is
2142 select date_received
2143 from per_applications
2144 where rowid = p_rowid ;
2145 --
2146 cursor c2 is
2147 select ppt.system_person_type
2148 from per_people_f per,
2149 per_person_types ppt
2150 where per.person_id = p_person_id
2151 and per.person_type_id = ppt.person_type_id ;
2152 begin
2153 --
2154 open c1 ;
2155 fetch c1 into p_old_date_received ;
2156 close c1 ;
2157 --
2158 open c2 ;
2159 fetch c2 into l_system_person_type ;
2160 close c2 ;
2161 --
2162 if ( p_old_date_received <> p_date_received ) then
2163 hr_date_chk.check_apl_ref_int ( p_person_id,
2164 p_business_group_id,
2165 l_system_person_type,
2166 p_old_date_received,
2167 p_date_received ) ;
2168 end if;
2169 --
2170 end pre_update ;
2171
2172 function get_territory_short_name ( p_territory_code in varchar2 )
2173 return varchar2 is
2174 l_return_value fnd_territories_vl.territory_short_name%type := null ;
2175 --
2176 cursor get_territory_short_name is
2177 select territory_short_name
2178 from fnd_territories_vl
2179 where territory_code = p_territory_code ;
2180 begin
2181
2182 if ( p_territory_code is not null ) then
2183 open get_territory_short_name ;
2184 fetch get_territory_short_name into l_return_value ;
2185 close get_territory_short_name ;
2186 end if;
2187
2188 return(l_return_value);
2189
2190 end get_territory_short_name ;
2191 --
2192 function get_style_name (p_style in varchar2)
2193 return varchar2 is
2194 l_return_value fnd_descr_flex_contexts_vl.descriptive_flex_context_name%type := null;
2195 --
2196 cursor get_style_name is
2197 select descriptive_flex_context_name
2198 from fnd_descr_flex_contexts_vl
2199 where descriptive_flex_context_code = p_style;
2200 --
2201 begin
2202 if (p_style is not null) then
2203 open get_style_name;
2204 fetch get_style_name into l_return_value;
2205 close get_style_name;
2206 end if;
2207 --
2208 return(l_return_value);
2209 --
2210 end get_style_name;
2211 --
2212 end PER_APPLICANT_PKG ;