[Home] [Help]
PACKAGE BODY: APPS.PER_QH_ACTION
Source
1 PACKAGE BODY per_qh_action as
2 /* $Header: peqhactn.pkb 120.2.12000000.2 2007/04/12 06:04:20 pdkundu noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' per_qh_action.';
7 procedure quick_hire_applicant
8 (p_validate in boolean default false,
9 p_hire_date in date,
10 p_person_id in per_all_people_f.person_id%TYPE,
11 p_assignment_id in number default null,
12 p_primary_assignment_id in number default null,
13 p_overwrite_primary in varchar2 default 'N',
14 p_person_type_id in number default null,
15 p_national_identifier in per_all_people_f.national_identifier%type default hr_api.g_varchar2,
16 p_per_object_version_number in out nocopy per_all_people_f.object_version_number%TYPE,
17 p_employee_number in out nocopy per_all_people_f.employee_number%TYPE,
18 p_per_effective_start_date out nocopy date,
19 p_per_effective_end_date out nocopy date,
20 p_unaccepted_asg_del_warning out nocopy boolean,
21 p_assign_payroll_warning out nocopy boolean,
22 p_oversubscribed_vacancy_id out nocopy number
23 )
24 is
25 --
26 -- Declare cursors and local variables
27 --
28 l_proc varchar2(72) := g_package||'quick_hire_applicant';
29 l_dummy number;
30 l_system_person_type per_person_types.system_person_type%TYPE;
31 l_system_person_type2 per_person_types.system_person_type%TYPE;
32 l_business_group_id per_all_people_f.business_group_id%TYPE;
33 l_legislation_code per_business_groups.legislation_code%TYPE;
34 l_application_id per_applications.application_id%TYPE;
35 l_apl_object_version_number per_applications.application_id%TYPE;
36 l_per_effective_start_date per_all_people_f.effective_start_date%type;
37 l_per_effective_start_date2 per_all_people_f.effective_start_date%type;
38 l_per_effective_end_date per_all_people_f.effective_end_date%type;
39 l_asg_effective_start_date per_all_assignments_f.effective_start_date%type;
40 l_asg_effective_end_date per_all_assignments_f.effective_end_date%type;
41 l_comment_id per_all_people_f.comment_id%type;
42 l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
43 l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%type;
44 l_current_employee_flag per_all_people_f.current_employee_flag%type;
45 l_full_name per_all_people_f.full_name%type;
46 l_per_object_version_number per_all_people_f.object_version_number%type;
47 l_per_object_version_number2 per_all_people_f.object_version_number%type;
48 l_asg_object_version_number per_all_assignments_f.object_version_number%type;
49 l_per_system_status per_assignment_status_types.per_system_status%type;
50 l_employee_number per_all_people_f.employee_number%type;
51 l_applicant_number per_all_people_f.applicant_number%type;
52 l_datetrack_update_mode varchar2(30);
53 l_dummyb boolean;
54 --
55 l_hire_date date;
56 l_moved boolean;
57 --
58 --
59 cursor csr_future_asg_changes is
60 select 1
61 from per_assignments_f asg
62 where asg.person_id = p_person_id
63 and asg.effective_start_date >= p_hire_date; -- bug 4681265 changed the condition from ' > ' to " >= " .
64 --
65 cursor csr_get_per_details(p_date date) is
66 select ppt.system_person_type,
67 per.effective_start_date,
68 per.object_version_number
69 from per_all_people_f per,
70 per_person_types ppt
71 where per.person_type_id = ppt.person_type_id
72 and per.person_id = p_person_id
73 and p_date between per.effective_start_date
74 and per.effective_end_date;
75 --
76 cursor get_assignments(p_person_id number,p_date date) is
77 select asg.assignment_id
78 ,asg.object_version_number
79 from per_all_assignments_f asg
80 where asg.person_id=p_person_id
81 and p_date=asg.effective_start_date;
82 --
83 cursor csr_asg_status(p_assignment_id number, p_date date) is
84 select pas.per_system_status
85 , asg.object_version_number
86 , asg.effective_start_date
87 from per_assignments_f asg,
88 per_assignment_status_types pas
89 where asg.assignment_id=p_assignment_id
90 and asg.assignment_status_type_id = pas.assignment_status_type_id
91 and p_date between asg.effective_start_date
92 and asg.effective_end_date;
93 --
94 -- Start of Fix for WWBUG 1408379
95 --
96 cursor c1 is
97 select *
98 from per_contact_relationships
99 where person_id=p_person_id
100 and date_start=l_hire_date;
101 --
102 cursor c2 is
103 select *
104 from per_contact_relationships
105 where contact_person_id=p_person_id
106 and date_start=l_hire_date;
107 --
108 l_c1 c1%rowtype;
109 l_c2 c2%rowtype;
110 l_old ben_con_ler.g_con_ler_rec;
111 l_new ben_con_ler.g_con_ler_rec;
112 l_rows_found boolean := false;
113 --
114 -- End of Fix for WWBUG 1408379
115 --
116 -- Bug 4755015 Starts
117 cursor get_business_group(p_asg_id number) is
118 select distinct PAAF.business_group_id
119 from per_all_assignments_f PAAF
120 where PAAF.assignment_id=p_asg_id;
121 l_bg_id number;
122 -- Bug 4755015 Ends
123 begin
124 --
125 hr_utility.set_location('Entering:'|| l_proc, 10);
126 --
127 savepoint quick_hire_applicant;
128 l_hire_date := trunc(p_hire_date);
129 l_per_object_version_number := p_per_object_version_number;
130 --
131 -- Check that there are not any future changes to the assignment
132 --
133 open csr_future_asg_changes;
134 fetch csr_future_asg_changes into l_dummy;
135 --
136 if csr_future_asg_changes%FOUND then
137 --
138 hr_utility.set_location(l_proc,30);
139 close csr_future_asg_changes;
140 --
141 hr_utility.set_message(801,'HR_7975_ASG_INV_FUTURE_ASA');
142 hr_utility.raise_error;
143 --
144 end if;
145 --
146 hr_utility.set_location(l_proc,40);
147 --
148 -- Get the derived details for the person DT instance
149 --
150 open csr_get_per_details(l_hire_date);
151 fetch csr_get_per_details
152 into l_system_person_type,
153 l_per_effective_start_date,
154 l_per_object_version_number;
155 if csr_get_per_details%NOTFOUND
156 then
157 --
158 hr_utility.set_location(l_proc,50);
159 --
160 close csr_get_per_details;
161 --
162 hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
163 hr_utility.raise_error;
164 --
165 else
166 close csr_get_per_details;
167 end if;
168 --
169 if l_system_person_type in ('APL','APL_EX_APL','EX_EMP_APL') then
170 hr_utility.set_location(l_proc,50);
171 --
172 -- if we have an applicant then look to see if they are being hired at the start of their records
173 --
174 if l_per_effective_start_date=l_hire_date then
175 hr_utility.set_location(l_proc,60);
176 --
177 -- the hire date is at the start of their record, so need to do some moving
178 --
179 open csr_get_per_details(l_hire_date-1);
180 fetch csr_get_per_details
181 into l_system_person_type2,
182 l_per_effective_start_date2,
183 l_per_object_version_number2;
184 if csr_get_per_details%NOTFOUND then
185 close csr_get_per_details;
186 hr_utility.set_location(l_proc,70);
187 --
188 -- there is no record on the previous day, so move everything back 1 day
189 --
190 update per_all_people_f
191 set
192 effective_start_date=l_hire_date-1
193 ,start_date=l_hire_date-1
194 ,original_date_of_hire=decode(original_date_of_hire
195 ,l_hire_date,l_hire_date-1
196 ,original_date_of_hire)
197 where person_id=p_person_id
198 and effective_start_date=l_hire_date;
199 --
200 l_per_object_version_number:=l_per_object_version_number+1;
201 --
202 update per_applications
203 set date_received=l_hire_date-1
204 where person_id=p_person_id
205 and date_received=l_hire_date;
206 --
207 -- move associated letter requests
208 --
209 update per_letter_request_lines
210 set date_from=l_hire_date-1
211 where assignment_id=(select asg2.assignment_id
212 from per_all_assignments_f asg2
213 where asg2.person_id=p_person_id
214 and asg2.assignment_type='A'
215 and asg2.effective_start_date=l_hire_date)
216 and date_from=l_hire_date;
217 --
218 -- Fix for WWBUG 1408379
219 --
220 open c1;
221 --
222 loop
223 --
224 fetch c1 into l_c1;
225 exit when c1%notfound;
226 --
227 update per_contact_relationships
228 set date_start=l_hire_date-1
229 where person_id=p_person_id
230 and date_start=l_hire_date
231 and contact_relationship_id = l_c1.contact_relationship_id;
232 --
233 -- Call life event routine
234 --
235 l_old.person_id := l_c1.person_id;
236 l_old.contact_person_id := l_c1.contact_person_id;
237 l_old.business_group_id := l_c1.business_group_id;
238 l_old.date_start := l_c1.date_start;
239 l_old.date_end := l_c1.date_end;
240 l_old.contact_type := l_c1.contact_type;
241 l_old.personal_flag := l_c1.personal_flag;
242 l_old.start_life_reason_id := l_c1.start_life_reason_id;
243 l_old.end_life_reason_id := l_c1.end_life_reason_id;
244 l_old.rltd_per_rsds_w_dsgntr_flag := l_c1.rltd_per_rsds_w_dsgntr_flag;
245 l_old.contact_relationship_id := l_c1.contact_relationship_id;
246 l_new.person_id := l_c1.person_id;
247 l_new.contact_person_id := l_c1.contact_person_id;
248 l_new.business_group_id := l_c1.business_group_id;
249 l_new.date_start := l_hire_date-1;
250 l_new.date_end := l_c1.date_end;
251 l_new.contact_type := l_c1.contact_type;
252 l_new.personal_flag := l_c1.personal_flag;
253 l_new.start_life_reason_id := l_c1.start_life_reason_id;
254 l_new.end_life_reason_id := l_c1.end_life_reason_id;
255 l_new.rltd_per_rsds_w_dsgntr_flag := l_c1.rltd_per_rsds_w_dsgntr_flag;
256 l_new.contact_relationship_id := l_c1.contact_relationship_id;
257 --
258 ben_con_ler.ler_chk(p_old => l_old,
259 p_new => l_new,
260 p_effective_date => l_hire_date-1);
261 --
262 end loop;
263 --
264 close c1;
265 --
266 open c2;
267 --
268 loop
269 --
270 fetch c2 into l_c2;
271 exit when c2%notfound;
272 --
273 update per_contact_relationships
274 set date_start=l_hire_date-1
275 where contact_person_id=p_person_id
276 and date_start=l_hire_date
277 and contact_relationship_id = l_c2.contact_relationship_id;
278 --
279 -- Call life event routine
280 --
281 l_old.person_id := l_c2.person_id;
282 l_old.contact_person_id := l_c2.contact_person_id;
283 l_old.business_group_id := l_c2.business_group_id;
284 l_old.date_start := l_c2.date_start;
285 l_old.date_end := l_c2.date_end;
286 l_old.contact_type := l_c2.contact_type;
287 l_old.personal_flag := l_c2.personal_flag;
288 l_old.start_life_reason_id := l_c2.start_life_reason_id;
289 l_old.end_life_reason_id := l_c2.end_life_reason_id;
290 l_old.rltd_per_rsds_w_dsgntr_flag := l_c2.rltd_per_rsds_w_dsgntr_flag;
291 l_old.contact_relationship_id := l_c2.contact_relationship_id;
292 l_new.person_id := l_c2.person_id;
293 l_new.contact_person_id := l_c2.contact_person_id;
294 l_new.business_group_id := l_c2.business_group_id;
295 l_new.date_start := l_hire_date;
296 l_new.date_end := l_c2.date_end;
297 l_new.contact_type := l_c2.contact_type;
298 l_new.personal_flag := l_c2.personal_flag;
299 l_new.start_life_reason_id := l_c2.start_life_reason_id;
300 l_new.end_life_reason_id := l_c2.end_life_reason_id;
301 l_new.rltd_per_rsds_w_dsgntr_flag := l_c2.rltd_per_rsds_w_dsgntr_flag;
302 l_new.contact_relationship_id := l_c1.contact_relationship_id;
303 --
304 ben_con_ler.ler_chk(p_old => l_old,
305 p_new => l_new,
306 p_effective_date => l_hire_date);
307 --
308 end loop;
309 --
310 close c2;
311 --
312 -- we only need to move the start date, there can be no end dated asg before it.
313 --
314 update per_all_assignments_f
315 set effective_start_date=l_hire_date-1
316 where person_id=p_person_id
317 and assignment_type='A'
318 and effective_start_date=l_hire_date;
319 --
320 l_moved:=true;
321 --
322 hr_utility.set_location(l_proc,80);
323 --
324 else
325 close csr_get_per_details;
326 hr_utility.set_location(l_proc,90);
327 --
328 -- the applicant did exist yesterday too, so look to see if we can or need to move their data
329 if (l_per_effective_start_date2 = l_hire_date-1)
330 and l_system_person_type2<>l_system_person_type then
331 --
332 -- we have an effective start date on the hire date and on the day before.
333 -- and the person was not an applicant yesterday, so we cannot move the records
334 hr_utility.set_message(800,'PER_52621_CANNOT_MOVE_DATA');
335 hr_utility.raise_error;
336 end if;
337 end if;
338 end if;
339 --
340 -- the person is an applicant today and yesterday, so we can hire today,
341 -- but now we must check that the assignment chosen is accepted today, or move it.
342 --
343 if p_assignment_id is not null then
344 hr_utility.set_location(l_proc,100);
345 open csr_asg_status(p_assignment_id,l_hire_date);
346 fetch csr_asg_status into l_per_system_status
347 ,l_asg_object_version_number
348 ,l_asg_effective_start_date;
349 if csr_asg_status%notfound then
350 close csr_asg_status;
351 hr_utility.set_message(800,'PER_52099_ASG_INV_ASG_ID');
352 hr_utility.raise_error;
353 else
354 close csr_asg_status;
355 hr_utility.set_location(l_proc,110);
356 end if;
357 if l_asg_effective_start_date=l_hire_date then
358 hr_utility.set_location(l_proc,120);
359 --
360 -- the assignment starts today, so we must look to see what the status was yesterday
361 --
362 open csr_asg_status(p_assignment_id,l_hire_date-1);
363 fetch csr_asg_status into l_per_system_status
364 ,l_asg_object_version_number
365 ,l_asg_effective_start_date;
366 if csr_asg_status%notfound then
367 close csr_asg_status;
368 hr_utility.set_location(l_proc,130);
369 --
370 -- the assignment started today, so move it back one day to accept it yesterday
371 --
372 --
373 -- move associated letter requests
374 --
375 update per_letter_request_lines
376 set date_from=l_hire_date-1
377 where assignment_id=(select asg2.assignment_id
378 from per_all_assignments_f asg2
379 where asg2.person_id=p_person_id
380 and asg2.assignment_type='A'
381 and asg2.effective_start_date=l_hire_date)
382 and date_from=l_hire_date;
383 --
384 update per_all_assignments_f
385 set effective_start_date=l_hire_date-1
386 where person_id=p_person_id
387 and assignment_type='A'
388 and effective_start_date=l_hire_date;
389 --
390 l_moved:=true;
391 hr_utility.set_location(l_proc,140);
392 else
393 close csr_asg_status;
394 hr_utility.set_location(l_proc,150);
395 if l_asg_effective_start_date=l_hire_date-1 then
396 --
397 -- there was an assignment change yesterday too, so we cannot move backwards
398 --
399 hr_utility.set_message(800,'PER_52621_CANNOT_MOVE_DATA');
400 hr_utility.raise_error;
401 else
402 hr_utility.set_location(l_proc,160);
403 --
404 -- the earlier assignment row starts before yesterday, so we can move the
405 -- recent end date back one day
406 --
407 -- move associated letter requests
408 --
409 update per_letter_request_lines
410 set date_from=l_hire_date-1
411 where assignment_id=(select asg2.assignment_id
412 from per_all_assignments_f asg2
413 where asg2.person_id=p_person_id
414 and asg2.assignment_type='A'
415 and asg2.effective_start_date=l_hire_date)
416 and date_from=l_hire_date;
417 --
418 update per_all_assignments_f
419 set effective_start_date=l_hire_date-1
420 where person_id=p_person_id
421 and assignment_type='A'
422 and effective_start_date=l_hire_date;
423 --
424 update per_all_assignments_f
425 set effective_end_date=l_hire_date-2
426 where person_id=p_person_id
427 and assignment_type='A'
428 and effective_end_date=l_hire_date-1;
429 --
430 l_moved:=true;
431 --
432 hr_utility.set_location(l_proc,170);
433 end if;
434 end if; -- there was a previous row
435 end if; -- this row starting today
436 end if;
437 hr_utility.set_location(l_proc,180);
438 --
439 -- we now have a person who was an applicant yesterday or before and
440 -- if an assignment is specified then it started yesterday or before
441 -- now we must accept the assignment if it is not already accepted.
442 if p_assignment_id is not null then
443 hr_utility.set_location(l_proc,190);
444 open csr_asg_status(p_assignment_id,l_hire_date-1);
445 fetch csr_asg_status into l_per_system_status
446 ,l_asg_object_version_number
447 ,l_asg_effective_start_date;
448 if l_per_system_status<>'ACCEPTED' then
449 hr_utility.set_location(l_proc,200);
450 if l_asg_effective_start_date=l_hire_date-1 then
451 l_datetrack_update_mode:='CORRECTION';
452 else
453 l_datetrack_update_mode:='UPDATE';
454 end if;
455 hr_utility.set_location(l_proc,210);
456 hr_assignment_api.accept_apl_asg
457 (p_validate => false
458 ,p_effective_date => l_hire_date-1
459 ,p_datetrack_update_mode => l_datetrack_update_mode
460 ,p_assignment_id => p_assignment_id
461 ,p_object_version_number => l_asg_object_version_number
462 ,p_assignment_status_type_id => null
463 ,p_change_reason => null
464 ,p_effective_start_date => l_asg_effective_start_date
465 ,p_effective_end_date => l_asg_effective_end_date
466 );
467 end if; -- is it not accepted
468 end if; -- is it specified
469 hr_utility.set_location(l_proc,220);
470 --
471 -- now that they are moved and accepted, do the hire
472 --
473 hr_applicant_api.hire_applicant
474 (p_validate => FALSE
475 ,p_hire_date => l_hire_date
476 ,p_person_id => p_person_id
477 ,p_assignment_id => p_primary_assignment_id
478 ,p_person_type_id => p_person_type_id
479 ,p_national_identifier => p_national_identifier
480 ,p_per_object_version_number => l_per_object_version_number
481 ,p_employee_number => p_employee_number
482 ,p_per_effective_start_date => p_per_effective_start_date
483 ,p_per_effective_end_date => p_per_effective_end_date
484 ,p_unaccepted_asg_del_warning => p_unaccepted_asg_del_warning
485 ,p_assign_payroll_warning => p_assign_payroll_warning
486 ,p_oversubscribed_vacancy_id => p_oversubscribed_vacancy_id
487 );
488 -- Bug 4755015 Starts
489 open get_business_group(p_primary_assignment_id);
490 fetch get_business_group into l_bg_id;
491 --
492 if get_business_group%NOTFOUND then
493 close get_business_group;
494 l_bg_id := hr_general.get_business_group_id;
495 else
496 close get_business_group;
497 end if;
498 --
499 hrentmnt.maintain_entries_asg (
500 p_assignment_id => p_primary_assignment_id,
501 p_business_group_id => l_bg_id,
502 p_operation => 'ASG_CRITERIA',
503 p_actual_term_date => null,
504 p_last_standard_date => null,
505 p_final_process_date => null,
506 p_dt_mode => 'UPDATE',
507 p_validation_start_date => p_per_effective_start_date,
508 p_validation_end_date => p_per_effective_end_date
509 );
510 --
511 -- Bug 4755015 Ends
512 --
513 elsif l_system_person_type ='EMP_APL' then
514 hr_utility.set_location(l_proc,230);
515 --
516 -- if we have an emp_apl then look to see if they are being hired at the
517 -- start of the current record
518 --
519 if l_per_effective_start_date=l_hire_date then
520 hr_utility.set_location(l_proc,240);
521 --
522 -- the hire date is at the start of their record, so need to do some moving
523 --
524 open csr_get_per_details(l_hire_date-1);
525 fetch csr_get_per_details
526 into l_system_person_type2,
527 l_per_effective_start_date2,
528 l_per_object_version_number2;
529 close csr_get_per_details;
530 hr_utility.set_location(l_proc,250);
531 --
532 -- look to see if they were not an emp-apl yesterday
533 --
534 if l_system_person_type2<>'EMP_APL' then
535 hr_utility.set_location(l_proc,260);
536 --
537 if (l_per_effective_start_date2 = l_hire_date-1) then
538 --
539 -- we have an effective start date on the hire date and on the day before.
540 -- and the person was not an emp_apl yesterday, so we cannot move the records
541 hr_utility.set_message(800,'PER_52621_CANNOT_MOVE_DATA');
542 hr_utility.raise_error;
543 end if;
544 hr_utility.set_location(l_proc,270);
545 --
546 -- they weren't an emp-apl yesterday, but there is room to move that backwards
547 --
548 update per_all_people_f
549 set effective_start_date=l_hire_date-1
550 where person_id=p_person_id
551 and effective_start_date=l_hire_date;
552 --
553 l_per_object_version_number:=l_per_object_version_number+1;
554 --
555 update per_all_people_f
556 set effective_end_date=l_hire_date-2
557 where person_id=p_person_id
558 and effective_end_date=l_hire_date-1;
559 --
560 l_moved:=true;
561 --
562 hr_utility.set_location(l_proc,280);
563 end if;
564 end if;
565 hr_utility.set_location(l_proc,290);
566 --
567 -- the person is an emp_apl today and yesterday, so we can hire today,
568 -- but now we must check that the assignment chosen is accepted today, or move it.
569 --
570 if p_assignment_id is not null and p_overwrite_primary='N' then
571 hr_utility.set_location(l_proc,300);
572 open csr_asg_status(p_assignment_id,l_hire_date);
573 fetch csr_asg_status into l_per_system_status
574 ,l_asg_object_version_number
575 ,l_asg_effective_start_date;
576 if csr_asg_status%notfound then
577 close csr_asg_status;
578 hr_utility.set_message(800,'PER_52099_ASG_INV_ASG_ID');
579 hr_utility.raise_error;
580 else
581 close csr_asg_status;
582 hr_utility.set_location(l_proc,310);
583 end if;
584 if l_asg_effective_start_date=l_hire_date then
585 hr_utility.set_location(l_proc,320);
586 --
587 -- the assignment starts today, so we must look to see what the status was yesterday
588 --
589 open csr_asg_status(p_assignment_id,l_hire_date-1);
590 fetch csr_asg_status into l_per_system_status
591 ,l_asg_object_version_number
592 ,l_asg_effective_start_date;
593 if csr_asg_status%notfound then
594 close csr_asg_status;
595 hr_utility.set_location(l_proc,330);
596 --
597 -- the assignment started today, so move it back one day to accept it yesterday
598 --
599 update per_all_assignments_f
600 set effective_start_date=l_hire_date-1
601 where person_id=p_person_id
602 and assignment_type='A'
603 and effective_start_date=l_hire_date;
604 --
605 -- the application may need moving
606 --
607 begin
608 update per_applications
609 set date_received=l_hire_date-1
610 where person_id=p_person_id
611 and date_received=l_hire_date;
612 exception
613 when no_data_found then
614 null;
615 when others then
616 raise;
617 end;
618 --
619 l_moved:=true;
620 hr_utility.set_location(l_proc,340);
621 else
622 close csr_asg_status;
623 hr_utility.set_location(l_proc,350);
624 if l_asg_effective_start_date=l_hire_date-1 then
625 --
626 -- there was an assignment change yesterday too, so we cannot move backwards
627 --
628 hr_utility.set_message(800,'PER_52621_CANNOT_MOVE_DATA');
629 hr_utility.raise_error;
630 else
631 hr_utility.set_location(l_proc,360);
632 --
633 -- the earlier assignment row starts before yesterday, so we can move the
634 -- recent end date back one day
635 --
636 update per_all_assignments_f
637 set effective_start_date=l_hire_date-1
638 where person_id=p_person_id
639 and assignment_type='A'
640 and effective_start_date=l_hire_date;
641 --
642 update per_all_assignments_f
643 set effective_end_date=l_hire_date-2
644 where person_id=p_person_id
645 and assignment_type='A'
646 and effective_end_date=l_hire_date-1;
647 --
648 l_moved:=true;
649 --
650 hr_utility.set_location(l_proc,370);
651 end if;
652 end if; -- there was a previous row
653 end if; -- this row starting today
654 end if;
655 hr_utility.set_location(l_proc,380);
656 --
657 if p_overwrite_primary IN ('Y','V') then
658 hr_utility.set_location(l_proc,385);
659 -- we should make sure that any applications that start on the hire date
660 -- are moved back one day, along with the application
661 begin
662 update per_all_assignments_f
663 set effective_start_date=l_hire_date-1
664 where person_id=p_person_id
665 and assignment_type='A'
666 and effective_start_date=l_hire_date;
667 exception
668 when no_data_found then
669 null;
670 when others then
671 raise;
672 end;
673 --
674 -- the application may need moving
675 --
676 begin
677 update per_applications
678 set date_received=l_hire_date-1
679 where person_id=p_person_id
680 and date_received=l_hire_date;
681 exception
682 when no_data_found then
683 null;
684 when others then
685 raise;
686 end;
687 --
688 end if;
689 --
690 hr_utility.set_location(l_proc,385);
691 --
692 -- we now have a person who was an emp_apl yesterday or before and
693 -- if an assignment is specified then it started yesterday or before
694 -- now we must accept the assignment if it is not already accepted.
695 if p_assignment_id is not null then
696 hr_utility.set_location(l_proc,390);
697 open csr_asg_status(p_assignment_id,l_hire_date-1);
698 fetch csr_asg_status into l_per_system_status
699 ,l_asg_object_version_number
700 ,l_asg_effective_start_date;
701 if l_per_system_status<>'ACCEPTED' then
702 hr_utility.set_location(l_proc,400);
703 if l_asg_effective_start_date=l_hire_date-1 then
704 l_datetrack_update_mode:='CORRECTION';
705 else
706 l_datetrack_update_mode:='UPDATE';
707 end if;
708 hr_utility.set_location(l_proc,410);
709 hr_assignment_api.accept_apl_asg
710 (p_validate => false
711 ,p_effective_date => l_hire_date-1
712 ,p_datetrack_update_mode => l_datetrack_update_mode
713 ,p_assignment_id => p_assignment_id
714 ,p_object_version_number => l_asg_object_version_number
715 ,p_assignment_status_type_id => null
716 ,p_change_reason => null
717 ,p_effective_start_date => l_asg_effective_start_date
718 ,p_effective_end_date => l_asg_effective_end_date
719 );
720 end if; -- is it not accepted
721 end if; -- is it specified
722 hr_utility.set_location(l_proc,420);
723 --
724 -- now that they are moved and accepted, do the hire
725 --
726 hr_employee_applicant_api.hire_employee_applicant
727 (p_validate => FALSE
728 ,p_hire_date => l_hire_date
729 ,p_person_id => p_person_id
730 ,p_primary_assignment_id => p_primary_assignment_id
731 ,p_person_type_id => p_person_type_id
732 ,p_overwrite_primary => p_overwrite_primary
733 ,p_per_object_version_number => l_per_object_version_number
734 ,p_per_effective_start_date => p_per_effective_start_date
735 ,p_per_effective_end_date => p_per_effective_end_date
736 ,p_unaccepted_asg_del_warning => p_unaccepted_asg_del_warning
737 ,p_assign_payroll_warning => p_assign_payroll_warning
738 ,p_oversubscribed_vacancy_id => p_oversubscribed_vacancy_id
739 );
740 --
741 end if;
742 --
743 hr_utility.set_location('Leaving '||l_proc,430);
744
745 end quick_hire_applicant;
746 --
747
748 function set_notification
749 (p_notification wf_messages.name%type
750 ,p_wf_name wf_item_types.name%type
751 ,p_role varchar2 --wf_roles.name%type Fix for bug 2741492
752 ,p_person_id per_all_people_f.person_id%type
753 ,p_assignment_id per_all_assignments_f.assignment_id%type
754 ,p_effective_date date
755 ,p_hire_date per_periods_of_service.date_start%type
756 ,p_full_name per_all_people_f.full_name%type
757 ,p_per_effective_start_date per_all_people_f.effective_start_date%type
758 ,p_title per_alL_people_f.title%type
759 ,p_first_name per_all_people_f.first_name%type
760 ,p_last_name per_all_people_f.last_name%type
761 ,p_employee_number per_all_people_f.employee_number%type
762 ,p_applicant_number per_all_people_f.applicant_number%type
763 ,p_national_identifier per_all_people_f.national_identifier%type
764 ,p_asg_effective_start_date per_all_assignments_f.effective_start_date%type
765 ,p_organization hr_all_organization_units.name%type
766 ,p_grade per_grades.name%type
767 ,p_job per_jobs.name%type
768 ,p_position hr_all_positions_f.name%type
769 ,p_payroll pay_all_payrolls_f.payroll_name%type
770 ,p_vacancy per_vacancies.name%type
771 ,p_supervisor per_all_people_f.full_name%type
772 ,p_location hr_locations.location_code%type
773 ,p_salary per_pay_proposals.proposed_salary_n%type
774 ,p_salary_currency pay_element_types_f.input_currency_code%type
775 ,p_pay_basis hr_lookups.meaning%type
776 ,p_date_probation_end per_all_assignments_f.date_probation_end%type
777 ,p_npw_number per_all_people_f.npw_number%type
778 ,p_vendor po_vendors.vendor_name%type
779 ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
780 ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
781 ,p_grade_ladder ben_pgm_f.name%type
782 ) return number is
783 l_nid number;
784 l_ff1 varchar2(240);
785 l_ff2 varchar2(240);
786 l_ff3 varchar2(240);
787 l_ff4 varchar2(240);
788 l_ff5 varchar2(240);
789 --
790 cursor csr_formula_id(p_name VARCHAR2)is
791 select formula_id
792 from ff_formulas_f fff
793 , ff_formula_types fft
794 where fff.formula_name = p_name
795 and p_effective_date between fff.effective_start_date and fff.effective_end_date
796 and fff.formula_type_id=fft.formula_type_id
797 and fft.formula_type_name='People Management Message';
798 --
799 l_formula_id ff_formulas_f.formula_id%type;
800 l_formula_inputs ff_exec.inputs_t;
801 l_formula_outputs ff_exec.outputs_t;
802 l_index_number NUMBER;
803 --
804 l_proc varchar2(72) := g_package||'set_notification';
805 l_user_name varchar2(50); -- # 3295399
806 begin
807 --
808 hr_utility.set_location('Entering:'||l_proc||'/'||p_wf_Name, 10);
809 --
810 l_nid:=wf_notification.send(p_role
811 ,p_wf_name
812 ,p_notification
813 ,null
814 ,null
815 ,null
816 ,null
817 );
818 --
819 hr_utility.set_location(l_proc, 20);
820 --
821 wf_notification.setAttrDate(l_nid,'HIRE_DATE',p_hire_date);
822 wf_notification.setAttrText(l_nid,'FULL_NAME',p_full_name);
823 wf_notification.setAttrDate(l_nid,'PER_EFFECTIVE_START_DATE',p_per_effective_start_date);
824 wf_notification.setAttrText(l_nid,'TITLE',p_title);
825 wf_notification.setAttrText(l_nid,'FIRST_NAME',p_first_name);
826 wf_notification.setAttrText(l_nid,'LAST_NAME',p_last_name);
827 wf_notification.setAttrText(l_nid,'EMPLOYEE_NUMBER',p_employee_number);
828 wf_notification.setAttrText(l_nid,'APPLICANT_NUMBER',p_applicant_number);
829 wf_notification.setAttrText(l_nid,'NATIONAL_IDENTIFIER',p_national_identifier);
830 wf_notification.setAttrDate(l_nid,'ASG_EFFECTIVE_START_DATE',p_asg_effective_start_date);
831 wf_notification.setAttrText(l_nid,'ORGANIZATION',p_organization);
832 wf_notification.setAttrText(l_nid,'GRADE',p_grade);
833 wf_notification.setAttrText(l_nid,'JOB',p_job);
834 wf_notification.setAttrText(l_nid,'POSITION',p_position);
835 wf_notification.setAttrText(l_nid,'PAYROLL',p_payroll);
836 wf_notification.setAttrText(l_nid,'VACANCY',p_vacancy);
837 wf_notification.setAttrText(l_nid,'SUPERVISOR',p_supervisor);
838 wf_notification.setAttrText(l_nid,'LOCATION',p_location);
839 wf_notification.setAttrNumber(l_nid,'SALARY',p_salary);
840 wf_notification.setAttrText(l_nid,'SALARY_CURRENCY',p_salary_currency);
841 wf_notification.setAttrText(l_nid,'PAY_BASIS',p_pay_basis);
842 wf_notification.setAttrDate(l_nid,'DATE_PROBATION_END',p_date_probation_end);
843
844 IF p_wf_name <> 'PECWKNOT' THEN -- # 3295399
845 wf_notification.setAttrText(l_nid,'GRADE_LADDER',p_grade_ladder);
846 END IF; -- # 3295399
847 --
848 -- Attributes for CWK seeded WORKFLOW.
849 --
850 IF p_wf_name = 'PECWKNOT' THEN
851 --
852 hr_utility.set_location(l_proc, 25);
853 --
854 wf_notification.setAttrText(l_nid,'NPW_NUMBER',p_npw_number);
855 wf_notification.setAttrText(l_nid,'SUPPLIER_NAME',p_vendor);
856 wf_notification.setAttrText(l_nid,'SUPPLIER_REFERENCE',p_supplier_reference);
857 wf_notification.setAttrDate(l_nid,'START_DATE',p_placement_date_start);
858 -- # 3295399 Start
859 if p_notification ='NEW_CWK' then
860 l_user_name := fnd_profile.value('USERNAME');
861 wf_notification.setAttrText(l_nid,'#FROM_ROLE',l_user_name);
862 End if;
863 -- # 3295399 End
864 --
865 END IF;
866 --
867 hr_utility.set_location(l_proc, 30);
868 --
869 open csr_formula_id(p_notification);
870 fetch csr_formula_id into l_formula_id;
871 if csr_formula_id%found then
872 --
873 hr_utility.set_location(l_proc, 40);
874 --
875 close csr_formula_id;
876 ff_exec.init_formula(l_formula_id,p_effective_date,l_formula_inputs,l_formula_outputs);
877 l_index_number := l_formula_inputs.FIRST;
878 WHILE (l_index_number IS NOT NULL)
879 loop
880 if (l_formula_inputs(l_index_number).name = 'ASSIGNMENT_ID') then
881 l_formula_inputs(l_index_number).value := p_assignment_id;
882 elsif (l_formula_inputs(l_index_number).name = 'PERSON_ID') then
883 l_formula_inputs(l_index_number).value := p_person_id;
884 elsif (l_formula_inputs(l_index_number).name = 'DATE_EARNED') then
885 l_formula_inputs(l_index_number).value := fnd_date.date_to_canonical(p_effective_date);
886 end if;
887 l_index_number := l_formula_inputs.NEXT(l_index_number);
888 end loop;
889 --
890 hr_utility.set_location(l_proc, 50);
891 --
892 ff_exec.run_formula(l_formula_inputs,l_formula_outputs);
893 l_index_number := l_formula_outputs.FIRST;
894 WHILE (l_index_number IS NOT NULL)
895 loop
896 if (l_formula_outputs(l_index_number).name = 'FF1') then
897 l_ff1 := l_formula_outputs(l_index_number).value;
898 elsif (l_formula_outputs(l_index_number).name = 'FF2') then
899 l_ff2 := l_formula_outputs(l_index_number).value;
900 elsif (l_formula_outputs(l_index_number).name = 'FF3') then
901 l_ff3 := l_formula_outputs(l_index_number).value;
902 elsif (l_formula_outputs(l_index_number).name = 'FF4') then
903 l_ff4 := l_formula_outputs(l_index_number).value;
904 elsif (l_formula_outputs(l_index_number).name = 'FF5') then
905 l_ff5 := l_formula_outputs(l_index_number).value;
906 end if;
907 l_index_number := l_formula_outputs.NEXT(l_index_number);
908 end loop;
909 --
910 hr_utility.set_location(l_proc, 60);
911 --
912 else
913 --
914 hr_utility.set_location(l_proc, 70);
915 --
916 close csr_formula_id;
917 end if;
918 --
919 wf_notification.setAttrText(l_nid,'FF1',l_ff1);
920 wf_notification.setAttrText(l_nid,'FF2',l_ff2);
921 wf_notification.setAttrText(l_nid,'FF3',l_ff3);
922 wf_notification.setAttrText(l_nid,'FF4',l_ff4);
923 wf_notification.setAttrText(l_nid,'FF5',l_ff5);
924 --
925 hr_utility.set_location(l_proc, 80);
926 --
927 return l_nid;
928 --
929 hr_utility.set_location('Leaving:'|| l_proc, 90);
930 --
931 exception
932 when others then
933 hr_utility.set_location('Leaving:'|| l_proc, 100);
934 l_nid:=null;
935 return l_nid;
936 end set_notification;
937
938 procedure send_notification
939 (p_notification wf_messages.name%type
940 ,p_wf_name wf_item_types.name%type
941 ,p_role varchar2 --wf_roles.name%type Fix for bug 2741492
942 ,p_person_id per_all_people_f.person_id%type
943 ,p_assignment_id per_all_assignments_f.assignment_id%type
944 ,p_effective_date date
945 ,p_hire_date per_periods_of_service.date_start%type
946 ,p_full_name per_all_people_f.full_name%type
947 ,p_per_effective_start_date per_all_people_f.effective_start_date%type
948 ,p_title per_alL_people_f.title%type
949 ,p_first_name per_all_people_f.first_name%type
950 ,p_last_name per_all_people_f.last_name%type
951 ,p_employee_number per_all_people_f.employee_number%type
952 ,p_applicant_number per_all_people_f.applicant_number%type
953 ,p_national_identifier per_all_people_f.national_identifier%type
954 ,p_asg_effective_start_date per_all_assignments_f.effective_start_date%type
955 ,p_organization hr_all_organization_units.name%type
956 ,p_grade per_grades.name%type
957 ,p_job per_jobs.name%type
958 ,p_position hr_all_positions_f.name%type
959 ,p_payroll pay_all_payrolls_f.payroll_name%type
960 ,p_vacancy per_vacancies.name%type
961 ,p_supervisor per_all_people_f.full_name%type
962 ,p_location hr_locations.location_code%type
963 ,p_salary per_pay_proposals.proposed_salary_n%type
964 ,p_salary_currency pay_element_types_f.input_currency_code%type
965 ,p_pay_basis hr_lookups.meaning%type
966 ,p_date_probation_end per_all_assignments_f.date_probation_end%type
967 ,p_npw_number per_all_people_f.npw_number%type
968 ,p_vendor po_vendors.vendor_name%type
969 ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
970 ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
971 ,p_grade_ladder ben_pgm_f.name%type
972 ) is
973 pragma autonomous_transaction;
974 l_nid number;
975 l_proc varchar2(72) := g_package||'send_notification';
976 begin
977 --
978 hr_utility.set_location('Entering:'|| l_proc, 10);
979 --
980 l_nid:=set_notification
981 (p_notification => p_notification
982 ,p_wf_name => p_wf_name
983 ,p_role => p_role
984 ,p_person_id => p_person_id
985 ,p_assignment_id => p_assignment_id
986 ,p_effective_date => p_effective_date
987 ,p_hire_date => p_hire_date
988 ,p_full_name => p_full_name
989 ,p_per_effective_start_date => p_per_effective_start_date
990 ,p_title => p_title
991 ,p_first_name => p_first_name
992 ,p_last_name => p_last_name
993 ,p_employee_number => p_employee_number
994 ,p_applicant_number => p_applicant_number
995 ,p_national_identifier => p_national_identifier
996 ,p_asg_effective_start_date => p_asg_effective_start_date
997 ,p_organization => p_organization
998 ,p_grade => p_grade
999 ,p_job => p_job
1000 ,p_position => p_position
1001 ,p_payroll => p_payroll
1002 ,p_vacancy => p_vacancy
1003 ,p_supervisor => p_supervisor
1004 ,p_location => p_location
1005 ,p_salary => p_salary
1006 ,p_salary_currency => p_salary_currency
1007 ,p_pay_basis => p_pay_basis
1008 ,p_date_probation_end => p_date_probation_end
1009 ,p_npw_number => p_npw_number
1010 ,p_vendor => p_vendor
1011 ,p_supplier_reference => p_supplier_reference
1012 ,p_placement_date_start => p_placement_date_start
1013 ,p_grade_ladder => p_grade_ladder
1014 );
1015 hr_utility.set_location(l_proc, 20);
1016 --
1017 --Added for bug 5586890
1018 WF_NOTIFICATION.Denormalize_Notification(l_nid);
1019 commit;
1020 --
1021 hr_utility.set_location('Leaving:'|| l_proc, 30);
1022 --
1023 end send_notification;
1024 --
1025 procedure get_notification_preview
1026 (p_notification in wf_messages.name%type
1027 ,p_wf_name in wf_item_types.name%type
1028 ,p_role in varchar2 --wf_roles.name%type Fix for bug 2741492
1029 ,p_person_id in per_all_people_f.person_id%type
1030 ,p_assignment_id in per_all_assignments_f.assignment_id%type
1031 ,p_effective_date in date
1032 ,p_hire_date in per_periods_of_service.date_start%type
1033 ,p_full_name in per_all_people_f.full_name%type
1034 ,p_per_effective_start_date in per_all_people_f.effective_start_date%type
1035 ,p_title in per_alL_people_f.title%type
1036 ,p_first_name in per_all_people_f.first_name%type
1037 ,p_last_name in per_all_people_f.last_name%type
1038 ,p_employee_number in per_all_people_f.employee_number%type
1039 ,p_applicant_number in per_all_people_f.applicant_number%type
1040 ,p_national_identifier in per_all_people_f.national_identifier%type
1041 ,p_asg_effective_start_date in per_all_assignments_f.effective_start_date%type
1042 ,p_organization in hr_all_organization_units.name%type
1043 ,p_grade in per_grades.name%type
1044 ,p_job in per_jobs.name%type
1045 ,p_position in hr_all_positions_f.name%type
1046 ,p_payroll in pay_all_payrolls_f.payroll_name%type
1047 ,p_vacancy in per_vacancies.name%type
1048 ,p_supervisor in per_all_people_f.full_name%type
1049 ,p_location in hr_locations.location_code%type
1050 ,p_salary in per_pay_proposals.proposed_salary_n%type
1051 ,p_salary_currency in pay_element_types_f.input_currency_code%type
1052 ,p_pay_basis in hr_lookups.meaning%type
1053 ,p_date_probation_end in per_all_assignments_f.date_probation_end%type
1054 ,p_npw_number in per_all_people_f.npw_number%type
1055 ,p_vendor in po_vendors.vendor_name%type
1056 ,p_supplier_reference in per_all_assignments_f.vendor_employee_number%type
1057 ,p_placement_date_start in per_all_assignments_f.period_of_placement_date_start%type
1058 ,p_grade_ladder in ben_pgm_f.name%type
1059 ,p_subject out nocopy varchar2
1060 ,p_body out nocopy varchar2
1061 ) is
1062 pragma autonomous_transaction;
1063 l_nid number;
1064 l_subject varchar2(240);
1065 l_body varchar2(4000);
1066 l_proc varchar2(72) := g_package||'get_notification_preview';
1067 begin
1068 --
1069 hr_utility.set_location('Entering:'|| l_proc, 10);
1070 --
1071 l_nid:=set_notification
1072 (p_notification => p_notification
1073 ,p_wf_name => p_wf_name
1074 ,p_role => p_role
1075 ,p_person_id => p_person_id
1076 ,p_assignment_id => p_assignment_id
1077 ,p_effective_date => p_effective_date
1078 ,p_hire_date => p_hire_date
1079 ,p_full_name => p_full_name
1080 ,p_per_effective_start_date => p_per_effective_start_date
1081 ,p_title => p_title
1082 ,p_first_name => p_first_name
1083 ,p_last_name => p_last_name
1084 ,p_employee_number => p_employee_number
1085 ,p_applicant_number => p_applicant_number
1086 ,p_national_identifier => p_national_identifier
1087 ,p_asg_effective_start_date => p_asg_effective_start_date
1088 ,p_organization => p_organization
1089 ,p_grade => p_grade
1090 ,p_job => p_job
1091 ,p_position => p_position
1092 ,p_payroll => p_payroll
1093 ,p_vacancy => p_vacancy
1094 ,p_supervisor => p_supervisor
1095 ,p_location => p_location
1096 ,p_salary => p_salary
1097 ,p_salary_currency => p_salary_currency
1098 ,p_pay_basis => p_pay_basis
1099 ,p_date_probation_end => p_date_probation_end
1100 ,p_npw_number => p_npw_number
1101 ,p_vendor => p_vendor
1102 ,p_supplier_reference => p_supplier_reference
1103 ,p_placement_date_start => p_placement_date_start
1104 ,p_grade_ladder => p_grade_ladder
1105 );
1106 hr_utility.set_location(l_proc, 20);
1107 --
1108 if l_nid is not null then
1109 hr_utility.set_location(l_proc, 30);
1110 l_subject:=wf_notification.getSubject(l_nid);
1111 l_body:=wf_notification.getBody(l_nid);
1112 else
1113 hr_utility.set_location(l_proc, 40);
1114 l_subject:=null;
1115 l_body:=null;
1116 end if;
1117 --
1118 rollback;
1119 --
1120 p_subject:=l_subject;
1121 p_body:=l_body;
1122 --
1123 hr_utility.set_location('Leaving:'|| l_proc, 50);
1124 --
1125 end get_notification_preview;
1126 --
1127 end per_qh_action;