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