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