4 --
1 PACKAGE BODY PER_PEOPLE3_PKG AS
2 /* $Header: peper03t.pkb 120.3.12020000.2 2013/01/18 07:11:34 srannama ship $ */
3 --
5 procedure get_number_generation_property(p_business_group_id NUMBER
6 ,p_property_on NUMBER
10 --
7 ,p_property_off NUMBER
8 ,p_employee_property in out nocopy NUMBER
9 ,p_applicant_property in out nocopy NUMBER) is
11 -- Returns Number Generation properties of the current business group
12 -- takes in forms 'NUMBERS' property_on and property_off.
13 -- setting the values of p_employee_property,p_applicant_property to
14 -- be property_off when an automatic method is used.
15 --
16 begin
17 --
18 select decode(pbg.METHOD_OF_GENERATION_EMP_NUM,'A'
19 , p_property_off , p_property_on)
20 ,decode(pbg.METHOD_OF_GENERATION_APL_NUM,'A'
21 , p_property_off, p_property_on)
22 into p_employee_property
23 , p_applicant_property
24 from per_business_groups pbg
25 where pbg.business_group_id = p_business_group_id;
26 --
27 exception
28 when no_data_found then
29 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
30 hr_utility.set_message_token('PROCEDURE'
31 ,'per_people3_pkg.get_number_generation_property');
32 hr_utility.set_message_token('STEP','2');
33 when too_many_rows then
34 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
35 hr_utility.set_message_token('PROCEDURE'
36 ,'per_people3_pkg.get_number_generation_property');
37 hr_utility.set_message_token('STEP','2');
38 end get_number_generation_property;
39 --
40 procedure get_legislative_ages(p_business_group_id NUMBER
41 ,p_minimum_age IN OUT NOCOPY NUMBER
42 ,p_maximum_age IN OUT NOCOPY NUMBER) is
43 begin
44 select hoi1.org_information12
45 , hoi1.org_information13
46 into p_minimum_age
47 , p_maximum_age
48 from hr_organization_information hoi1
49 where p_business_group_id +0 = hoi1.organization_id
50 and hoi1.org_information_context = 'Business Group Information';
51 --
52 exception
53 when no_data_found then
54 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
55 hr_utility.set_message_token('PROCEDURE'
56 ,'per_people3_pkg.get_legislative_ages');
57 hr_utility.set_message_token('STEP','1');
58 when too_many_rows then
59 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
60 hr_utility.set_message_token('PROCEDURE'
61 ,'per_people3_pkg.get_legislative_ages');
62 hr_utility.set_message_token('STEP','2');
63 end get_legislative_ages;
64 --
65
66 procedure get_default_person_type(p_required_type VARCHAR2
67 ,p_business_group_id NUMBER
68 ,p_legislation_code VARCHAR2
69 ,p_person_type IN OUT NOCOPY NUMBER) is
70 --
71 -- Define Cursor.
72 --
73 cursor per_type is
74 select past.assignment_status_type_id
75 from per_assignment_status_types past
76 , per_ass_status_type_amends pasa
77 where pasa.assignment_status_type_id(+) = past.assignment_status_type_id
78 and pasa.business_group_id(+) + 0 = p_business_group_id
79 and nvl(past.business_group_id,p_business_group_id) = p_business_group_id
80 and nvl(past.legislation_code, p_legislation_code) =p_legislation_code
81 and nvl(pasa.active_flag,past.active_flag) = 'Y'
82 and nvl(pasa.default_flag,past.default_flag) = 'Y'
83 and nvl(pasa.per_system_status,past.per_system_status) = p_required_type;
84 --
85 begin
86 open per_type;
87 fetch per_type into p_person_type;
88 --
89 if per_type%ROWCOUNT <>1 then
90 hr_utility.set_message('800','HR_289296_SEC_PROF_SETUP_ERR');
91 hr_utility.raise_error;
92 end if;
93 --
94 close per_type;
95 end get_default_person_type;
96 --
97 procedure get_ddf_exists(p_legislation_code VARCHAR2
98 ,p_ddf_exists IN OUT NOCOPY VARCHAR2) is
99 cursor ddf is
100 select 'Y'
101 from sys.dual
102 where exists( select 1 from FND_DESCR_FLEX_CONTEXTS fdfc
103 where fdfc.APPLICATION_ID = 800
104 and fdfc.DESCRIPTIVE_FLEXFIELD_NAME = 'Person Developer DF'
105 and fdfc.enabled_flag = 'Y'
106 and fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_legislation_code);
107 begin
108 open ddf;
109 fetch ddf into p_ddf_exists;
110 if ddf%notfound then
111 p_ddf_exists := 'N';
112 close ddf;
113 end if;
114 end get_ddf_exists;
115 --
116 -- Verifies if PER_PEOPLE descriptive flexfield has enabled segments
117 -- #1799586
118 procedure get_people_ddf_exists(p_legislation_code VARCHAR2
119 ,p_people_ddf_exists IN OUT NOCOPY VARCHAR2) is
120 cursor ddf is
121 select 'Y'
122 from sys.dual
123 where exists( select 1 from fnd_descr_flex_column_usages fdfc
124 where fdfc.APPLICATION_ID = 800
125 and fdfc.DESCRIPTIVE_FLEXFIELD_NAME = 'PER_PEOPLE'
126 and fdfc.enabled_flag = 'Y');
127 begin
128 open ddf;
129 fetch ddf into p_people_ddf_exists;
130 if ddf%notfound then
131 p_people_ddf_exists := 'N';
132 end if;
133 close ddf;
134
135 end get_people_ddf_exists;
136 --
137 --
138 procedure initialize(p_business_group_id NUMBER
139 ,p_legislation_code VARCHAR2
140 ,p_ddf_exists IN OUT NOCOPY VARCHAR2
141 ,p_property_on NUMBER
142 ,p_property_off NUMBER
143 ,p_employee_property IN OUT NOCOPY NUMBER
147 ,p_emp_person_type IN OUT NOCOPY NUMBER
144 ,p_applicant_property IN OUT NOCOPY NUMBER
145 ,p_required_emp_type VARCHAR2
146 ,p_required_app_type VARCHAR2
148 ,p_app_person_type IN OUT NOCOPY NUMBER
149 ,p_minimum_age IN OUT NOCOPY NUMBER
150 ,p_maximum_age IN OUT NOCOPY NUMBER) is
151 l_people_ddf_exists varchar2(1);
152 begin
153
154 per_people3_pkg.initialize(p_business_group_id
155 ,p_legislation_code
156 ,p_ddf_exists
157 ,p_property_on
158 ,p_property_off
159 ,p_employee_property
160 ,p_applicant_property
161 ,p_required_emp_type
162 ,p_required_app_type
163 ,p_emp_person_type
164 ,p_app_person_type
165 ,p_minimum_age
166 ,p_maximum_age
167 ,l_people_ddf_exists);
168 end initialize;
169 --
170 procedure initialize(p_business_group_id NUMBER
171 ,p_legislation_code VARCHAR2
172 ,p_ddf_exists IN OUT NOCOPY VARCHAR2
173 ,p_property_on NUMBER
174 ,p_property_off NUMBER
175 ,p_employee_property IN OUT NOCOPY NUMBER
176 ,p_applicant_property IN OUT NOCOPY NUMBER
177 ,p_required_emp_type VARCHAR2
178 ,p_required_app_type VARCHAR2
179 ,p_emp_person_type IN OUT NOCOPY NUMBER
180 ,p_app_person_type IN OUT NOCOPY NUMBER) is
181 l_minimum_age NUMBER;
182 l_maximum_age NUMBER;
183 begin
184 per_people3_pkg.initialize(p_business_group_id
185 ,p_legislation_code
186 ,p_ddf_exists
187 ,p_property_on
188 ,p_property_off
189 ,p_employee_property
190 ,p_applicant_property
191 ,p_required_emp_type
192 ,p_required_app_type
193 ,p_emp_person_type
194 ,p_app_person_type
195 ,l_minimum_age
196 ,l_maximum_age);
197 end initialize;
198 --
199 procedure initialize(p_business_group_id NUMBER
200 ,p_legislation_code VARCHAR2
201 ,p_ddf_exists IN OUT NOCOPY VARCHAR2
202 ,p_property_on NUMBER
203 ,p_property_off NUMBER
204 ,p_employee_property IN OUT NOCOPY NUMBER
205 ,p_applicant_property IN OUT NOCOPY NUMBER
206 ,p_required_emp_type VARCHAR2
207 ,p_required_app_type VARCHAR2
208 ,p_emp_person_type IN OUT NOCOPY NUMBER
209 ,p_app_person_type IN OUT NOCOPY NUMBER
210 ,p_minimum_age IN OUT NOCOPY NUMBER
211 ,p_maximum_age IN OUT NOCOPY NUMBER
212 ,p_people_ddf_exists IN OUT NOCOPY VARCHAR2) is
213 --
214 begin
215 --
216 -- Get the item properties for employee and applicant number
217 --
218 per_people3_pkg.get_number_generation_property(
219 p_business_group_id => p_business_group_id
220 ,p_property_on => p_property_on
221 ,p_property_off => p_property_off
222 ,p_employee_property => p_employee_property
223 ,p_applicant_property => p_applicant_property);
224 --
225 per_people3_pkg.get_legislative_ages(p_business_group_id
226 ,p_minimum_age
227 ,p_maximum_age);
228 --
229 -- Get the default person_type_id's for employee.
230 --
231 per_people3_pkg.get_default_person_type(p_required_type => p_required_emp_type
232 ,p_business_group_id => p_business_group_id
233 ,p_legislation_code => p_legislation_code
234 ,p_person_type => p_emp_person_type);
235 --
236 -- Get the default person_type_id for applicant.
237 --
238 per_people3_pkg.get_default_person_type(p_required_type => p_required_app_type
239 ,p_business_group_id => p_business_group_id
240 ,p_legislation_code => p_legislation_code
241 ,p_person_type => p_app_person_type);
242 --
243 -- Does a ddf exisrts for this legislation?
244 --
245 per_people3_pkg.get_ddf_exists(p_legislation_code => p_legislation_code
246 ,p_ddf_exists => p_ddf_exists);
247 --
248 -- #1799586
249 per_people3_pkg.get_people_ddf_exists
250 (p_legislation_code => p_legislation_code
251 ,p_people_ddf_exists => p_people_ddf_exists);
252 end initialize;
253 --
254 procedure check_future_apl(p_person_id NUMBER
255 ,p_hire_date DATE) is
256 --
257 -- Local Variables
258 --
259 l_dummy VARCHAR2(1);
260 --
261 cursor fut_apl is select 'Y'
262 from sys.dual
263 where exists (select 'future assignment exists'
264 from per_assignments_f paf
265 where paf.person_id = p_person_id
266 and paf.assignment_type = 'A'
270 fetch fut_apl into l_dummy;
267 and paf.effective_start_date >= p_hire_date);
268 begin
269 open fut_apl;
271 if fut_apl%FOUND then
272 hr_utility.set_message('801','HR_7975_ASG_INV_FUTURE_ASA');
273 app_exception.raise_exception;
274 end if;
275 close fut_apl;
276 end;
277 --
278 --overloaded procedure added for bug 5403222
279 procedure check_future_apl(p_person_id NUMBER
280 ,p_hire_date DATE
281 ,p_table HR_EMPLOYEE_APPLICANT_API.t_ApplTable ) is
282 --
283 -- Local Variables
284 --
285 l_dummy VARCHAR2(1);
286 l_index number;
287 l_max_ele number;
288 l_assignment_id per_all_assignments_f.assignment_id%type;
289 --
290 --
291 cursor fut_apl(l_assignment_id number) is select 'Y'
292 from sys.dual
293 where exists (select 'future assignment exists'
294 from per_assignments_f paf
295 where paf.assignment_id = l_assignment_id
296 and paf.assignment_type = 'A'
297 and paf.effective_start_date >= p_hire_date);
298 begin
299
300 l_index := 0;
301 l_max_ele := p_table.COUNT;
302
303 if l_max_ele > 0 then
304 l_index := 1;
305 loop
306 hr_utility.trace('p_table(l_index).process_flag >> '||p_table(l_index).process_flag);
307 hr_utility.trace('p_table(l_index).id >> '||p_table(l_index).id);
308 if nvl(p_table(l_index).process_flag,'E') <> 'R' then
309 open fut_apl(p_table(l_index).id);
310 fetch fut_apl into l_dummy;
311 if fut_apl%FOUND then
312 close fut_apl;
313 hr_utility.trace('12345');
314 hr_utility.set_message('801','HR_7975_ASG_INV_FUTURE_ASA');
315 app_exception.raise_exception;
316 end if;
317 hr_utility.trace('123458');
318 close fut_apl;
319 end if;
320 l_index := l_index + 1;
321 EXIT when l_index > l_max_ele ;
322 end loop;
323 end if;
324 end;
325
326 -- ER FPT
327 procedure fpt_check_future_apl(p_person_id NUMBER
328 ,p_hire_date DATE
329 ,p_assignment_id NUMBER default NULL) is
330 --
331 -- Local Variables
332 --
333 l_dummy VARCHAR2(1);
334 --
335 cursor fut_upd_asg is
336 select 'Y' from per_all_assignments_f
337 where person_id = p_person_id
338 and assignment_id = p_assignment_id
339 and effective_start_date >= p_hire_date;
340
341 cursor other_asg is
342 select 'Y' from per_all_assignments_f
343 where person_id = p_person_id
344 and effective_start_date >= p_hire_date
345 and assignment_type in ('E','C');
346
347 begin
348 if p_assignment_id is not null then
349 open fut_upd_asg;
350 fetch fut_upd_asg into l_dummy;
351 if fut_upd_asg%FOUND then
352 hr_utility.set_message('801','HR_7975_ASG_INV_FUTURE_ASA');
353 app_exception.raise_exception;
354 end if;
355 close fut_upd_asg;
356 end if;
357
358 open other_asg;
359 fetch other_asg into l_dummy;
360 if other_asg%FOUND then
361 hr_utility.set_message('801','HR_7975_ASG_INV_FUTURE_ASA');
362 app_exception.raise_exception;
363 end if;
364 close other_asg;
365
366 end;
367
368 procedure fpt_check_future_apl(p_person_id NUMBER
369 ,p_hire_date DATE
370 ,p_table HR_EMPLOYEE_APPLICANT_API.t_ApplTable ) is
371 --
372 -- Local Variables
373 --
374 l_dummy VARCHAR2(1);
375 l_index number;
376 l_max_ele number;
377 l_assignment_id per_all_assignments_f.assignment_id%type;
378 cursor fut_apl(l_assignment_id number) is select 'Y'
379 from sys.dual
380 where exists (select 'future assignment exists'
381 from per_assignments_f paf
382 where paf.assignment_id = l_assignment_id
383 and paf.assignment_type = 'A'
384 and paf.effective_start_date >= p_hire_date);
385
386 l_start_date date;
387 cursor min_eff_date(p_assignment_id number) is
388 select min(effective_start_date)
389 from per_all_assignments_f
390 where person_id = p_person_id
391 and assignment_id = p_assignment_id
392 and assignment_type = 'A';
393
394 begin
395
396 l_index := 0;
397 l_max_ele := p_table.COUNT;
398
399 if l_max_ele > 0 then
400 l_index := 1;
401 loop
402 hr_utility.trace('p_table(l_index).process_flag >> '||p_table(l_index).process_flag);
403 hr_utility.trace('p_table(l_index).id >> '||p_table(l_index).id);
404 if nvl(p_table(l_index).process_flag,'E') <> 'R' then
405
406 open min_eff_date(p_table(l_index).id);
407 fetch min_eff_date into l_start_date;
408 close min_eff_date;
409 if l_start_date <= p_hire_date then
410 open fut_apl(p_table(l_index).id);
411 fetch fut_apl into l_dummy;
412 if fut_apl%FOUND then
413 close fut_apl;
414 hr_utility.trace('12345');
415 hr_utility.set_message('801','HR_7975_ASG_INV_FUTURE_ASA');
416 app_exception.raise_exception;
417 end if;
418 hr_utility.trace('123458');
419 close fut_apl;
420 end if;
421 end if;
422 l_index := l_index + 1;
423 EXIT when l_index > l_max_ele ;
424 end loop;
425 end if;
426 end;
427 -- ER FPT
431 ,p_new_hire_date date
428
429 procedure update_period(p_person_id number
430 ,p_hire_date date
432 ,p_adjusted_svc_date in date ) is
433 --
434 -- Update Period of serivice start date when Hire_date
435 -- has changed and Person_type has not.
436 --
437 -- Define Cursor.
438 --
439 cursor pps is select rowid,pps.*
440 from per_periods_of_service pps
441 where person_id = p_person_id
442 and date_start = p_hire_date
443 for update of date_start nowait;
444 --
445 -- Local Variables.
446 --
447 pps_rec pps%rowtype;
448 l_adjusted_svc_date DATE;
449 --
450 begin
451 --
452 open pps;
453 <<pps_loop>>
454 loop
455 exit pps_loop when pps%NOTFOUND;
456 fetch pps into pps_rec;
457 end loop pps_loop;
458 --
459 if pps%rowcount <>1 then
460 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
461 hr_utility.set_message_token('PROCEDURE','per_people3_pkg.UPDATE_PERIOD');
462 hr_utility.raise_error;
463 end if;
464 --
465 close pps;
466 --
467 -- # 1573563
468 if nvl(p_adjusted_svc_date,hr_general.end_of_time) <> hr_api.g_date then
469 l_adjusted_svc_date := p_adjusted_svc_date;
470 else
471 l_adjusted_svc_date := pps_rec.adjusted_svc_date;
472 end if;
473 -- Now update the row;
474 --
475 per_periods_of_service_pkg.update_row(p_row_id => pps_rec.rowid
476 ,p_period_of_service_id => pps_rec.PERIOD_OF_SERVICE_ID
477 ,p_business_group_id => pps_rec.BUSINESS_GROUP_ID
478 ,p_person_id => pps_rec.PERSON_ID
479 ,p_date_start => p_new_hire_date
480 ,p_termination_accepted_per_id => pps_rec.TERMINATION_ACCEPTED_PERSON_ID
481 ,p_accepted_termination_date => pps_rec.ACCEPTED_TERMINATION_DATE
482 ,p_actual_termination_date => pps_rec.ACTUAL_TERMINATION_DATE
483 ,p_comments => pps_rec.COMMENTS
484 ,p_final_process_date => pps_rec.FINAL_PROCESS_DATE
485 ,p_last_standard_process_date => pps_rec.LAST_STANDARD_PROCESS_DATE
486 ,p_leaving_reason => pps_rec.LEAVING_REASON
487 ,p_notified_termination_date => pps_rec.NOTIFIED_TERMINATION_DATE
488 ,p_projected_termination_date => pps_rec.PROJECTED_TERMINATION_DATE
489 ,p_request_id => pps_rec.REQUEST_ID
490 ,p_program_application_id => pps_rec.PROGRAM_APPLICATION_ID
491 ,p_program_id => pps_rec.PROGRAM_ID
492 ,p_program_update_date => pps_rec.PROGRAM_UPDATE_DATE
493 ,p_attribute_category => pps_rec.ATTRIBUTE_CATEGORY
494 ,p_attribute1 => pps_rec.ATTRIBUTE1
495 ,p_attribute2 => pps_rec.ATTRIBUTE2
496 ,p_attribute3 => pps_rec.ATTRIBUTE3
497 ,p_attribute4 => pps_rec.ATTRIBUTE4
498 ,p_attribute5 => pps_rec.ATTRIBUTE5
499 ,p_attribute6 => pps_rec.ATTRIBUTE6
500 ,p_attribute7 => pps_rec.ATTRIBUTE7
501 ,p_attribute8 => pps_rec.ATTRIBUTE8
502 ,p_attribute9 => pps_rec.ATTRIBUTE9
503 ,p_attribute10 => pps_rec.ATTRIBUTE10
504 ,p_attribute11 => pps_rec.ATTRIBUTE11
505 ,p_attribute12 => pps_rec.ATTRIBUTE12
506 ,p_attribute13 => pps_rec.ATTRIBUTE13
507 ,p_attribute14 => pps_rec.ATTRIBUTE14
508 ,p_attribute15 => pps_rec.ATTRIBUTE15
509 ,p_attribute16 => pps_rec.ATTRIBUTE16
510 ,p_attribute17 => pps_rec.ATTRIBUTE17
511 ,p_attribute18 => pps_rec.ATTRIBUTE18
512 ,p_attribute19 => pps_rec.ATTRIBUTE19
513 ,p_attribute20 => pps_rec.ATTRIBUTE20
514 ,p_pds_information_category => pps_rec.PDS_INFORMATION_CATEGORY
515 ,p_pds_information1 => pps_rec.PDS_INFORMATION1
516 ,p_pds_information2 => pps_rec.PDS_INFORMATION2
517 ,p_pds_information3 => pps_rec.PDS_INFORMATION3
518 ,p_pds_information4 => pps_rec.PDS_INFORMATION4
519 ,p_pds_information5 => pps_rec.PDS_INFORMATION5
520 ,p_pds_information6 => pps_rec.PDS_INFORMATION6
521 ,p_pds_information7 => pps_rec.PDS_INFORMATION7
522 ,p_pds_information8 => pps_rec.PDS_INFORMATION8
523 ,p_pds_information9 => pps_rec.PDS_INFORMATION9
524 ,p_pds_information10 => pps_rec.PDS_INFORMATION10
525 ,p_pds_information11 => pps_rec.PDS_INFORMATION11
526 ,p_pds_information12 => pps_rec.PDS_INFORMATION12
527 ,p_pds_information13 => pps_rec.PDS_INFORMATION13
528 ,p_pds_information14 => pps_rec.PDS_INFORMATION14
529 ,p_pds_information15 => pps_rec.PDS_INFORMATION15
530 ,p_pds_information16 => pps_rec.PDS_INFORMATION16
531 ,p_pds_information17 => pps_rec.PDS_INFORMATION17
532 ,p_pds_information18 => pps_rec.PDS_INFORMATION18
533 ,p_pds_information19 => pps_rec.PDS_INFORMATION19
534 ,p_pds_information20 => pps_rec.PDS_INFORMATION20
535 ,p_pds_information21 => pps_rec.PDS_INFORMATION21
536 ,p_pds_information22 => pps_rec.PDS_INFORMATION22
537 ,p_pds_information23 => pps_rec.PDS_INFORMATION23
538 ,p_pds_information24 => pps_rec.PDS_INFORMATION24
539 ,p_pds_information25 => pps_rec.PDS_INFORMATION25
540 ,p_pds_information26 => pps_rec.PDS_INFORMATION26
544 ,p_pds_information30 => pps_rec.PDS_INFORMATION30
541 ,p_pds_information27 => pps_rec.PDS_INFORMATION27
542 ,p_pds_information28 => pps_rec.PDS_INFORMATION28
543 ,p_pds_information29 => pps_rec.PDS_INFORMATION29
545 ,p_adjusted_svc_date => l_adjusted_svc_date);
546 --
547 end update_period;
548 --
549 procedure run_alu_ee(p_alu_mode VARCHAR2
550 ,p_business_group_id NUMBER
551 ,p_person_id NUMBER
552 ,p_old_start DATE
553 ,p_start_date date) is
554 --
555 -- Checks the assignment link usages and Element_entries
556 -- code for changes in assignment and Personal qualifying criteria
557 --
558 -- Local Variables.
559 --
560 l_assignment_id number; -- assignment_id of employee assignment.
561 l_validation_start_date date; -- End date_of Assignment.
562 l_validation_end_date date; -- End date_of Assignment.
563 l_entries_changed VARCHAR2(1);
564 --
565 -- Cursor
566 --
567 cursor ass_cur is
568 select assignment_id
569 from per_all_assignments_f paf
570 where paf.person_id = p_person_id
571 and paf.assignment_type = 'E'
572 and p_start_date between
573 paf.effective_start_date and paf.effective_end_date;
574 --
575 begin
576 -- Set the correct validation start and end dates for
577 -- the assignments. These are the same for all
578 -- assignments of a multiple assignment person.
579 if(p_start_date > p_old_start) then
580 -- We have moved the hire date forwards.
581 l_validation_start_date := p_old_start;
582 l_validation_end_date := (p_start_date - 1);
583 elsif(p_start_date < p_old_start) then
584 -- We have moved the hire date backwards.
585 l_validation_start_date := p_start_date;
586 l_validation_end_date := (p_old_start - 1);
587 end if;
588 --
589 open ass_cur;
590 loop
591 fetch ass_cur into l_assignment_id;
592 exit when ass_cur%NOTFOUND;
593 if p_alu_mode = 'ASG_CRITERIA' then
594 -- changed cal to use p_old_payroll_id => 2
595 -- and p_new_payroll_id=> 1 so that the NR entries get updated.
596 if(p_start_date <> p_old_start) then
597 -- Only call this if the hire dates have actually changed.
598 hrentmnt.maintain_entries_asg
599 (p_assignment_id =>l_assignment_id
600 ,p_old_payroll_id =>2
601 ,p_new_payroll_id =>1
602 ,p_business_group_id =>p_business_group_id
603 ,p_operation =>p_alu_mode
604 ,p_actual_term_date => NULL
605 ,p_last_standard_date =>NULL
606 ,p_final_process_date => NULL
607 ,p_validation_start_date => l_validation_start_date
608 ,p_validation_end_date => l_validation_end_date
609 ,p_dt_mode =>'CORRECTION'
610 ,p_old_hire_date => p_old_start
611 ,p_entries_changed =>l_entries_changed);
612 end if;
613 end if;
614 --
615 hrentmnt.maintain_entries_asg(l_assignment_id
616 ,p_business_group_id
617 ,'CHANGE_PQC'
618 ,NULL
619 ,NULL
620 ,NULL
621 ,NULL
622 ,NULL
623 ,NULL);
624 end loop;
625 close ass_cur;
626 end;
627 --
628 procedure vacancy_chk(p_person_id NUMBER
629 ,p_fire_warning in out nocopy VARCHAR2
630 ,p_vacancy_id in out nocopy NUMBER
631 -- #2381925
632 ,p_table IN HR_EMPLOYEE_APPLICANT_API.t_ApplTable
633 --
634 ) is
635 --
636 -- Check all Vacanicies person has applied for and check for
637 -- them being over-subscribed.
638 --
639 -- Local Variables
640 --
641 l_vacancy_name VARCHAR2(30);-- Name of returned vacancy.
642 l_dummy_id NUMBER(15); -- Dummy variable.
643 over_subscribed EXCEPTION; -- Over-subscribed exception.
644 --
645 l_asg_id number; -- #2381925: assignment id returned
646 --
647 -- Cursor.
648 -- note p_last_vacancy is a parameter to the cursor defineition.
649 --
650 cursor app_ass(p_last_vacancy number) is
651 select pav.vacancy_id,pav.name
652 ,pa.assignment_id -- #2381925
653 from per_assignments pa, per_all_vacancies pav
654 , per_assignment_status_types pas
655 where person_id = p_person_id
656 and pav.vacancy_id = pa.vacancy_id
657 and pa.assignment_status_type_id = pas.assignment_status_type_id
658 and pas.per_system_status = 'ACCEPTED'
659 and pa.assignment_type = 'A'
660 and pav.vacancy_id >nvl(p_last_vacancy,0)
661 order by pav.vacancy_id asc;
662 --
663 begin
664 --
665 -- set warning to not fire.
666 --
667 p_fire_warning := 'N';
668 --
669 -- Get all vacancies that employee has applied for.
670 --
671 open app_ass(p_vacancy_id);
672 loop
673 fetch app_ass into p_vacancy_id,l_vacancy_name, l_asg_id; --#2381925
674 exit when app_ass%NOTFOUND;
675 begin
676 -- 2381925: Verify if vacancy is over-subscribed ONLY if applicant
677 -- assignment is being hired.
678 --
679 if hr_employee_applicant_api.is_convert(p_table,l_asg_id) then
680
684 where pav.number_of_openings <
681 select vacancy_id
682 into l_dummy_id
683 from per_all_vacancies pav
685 (select count(distinct assignment_id) + 1
686 from per_all_assignments_f paf
687 where paf.vacancy_id = pav.vacancy_id
688 and paf.assignment_type = 'E')
689 and pav.vacancy_id = p_vacancy_id;
690 --
691 -- If a row is returned then the vacancy is over-subscribed
692 -- set message and warning flag.
693 -- raise exception.
694 --
695 fnd_message.set_name('PER','HR_EMP_VAC_FILLED');
696 fnd_message.set_token('VAC',l_vacancy_name);
697 p_fire_warning := 'Y';
698 raise over_subscribed;
699 end if; -- is asg hired ?
700 exception
701 when no_data_found then
702 null;
703 when too_many_rows then
704 fnd_message.set_name('PER','HR_EMP_VAC_FILLED');
705 fnd_message.set_token('VAC','Too many rows');
706 app_exception.raise_exception;
707 end;
708 end loop;
709 --
710 close app_ass;
711 --
712 exception
713 when over_subscribed then
714 close app_ass;
715 when no_data_found then
716 if app_ass%rowcount < 1 then
717 raise;
718 end if;
719 close app_ass;
720 when too_many_rows then
721 raise;
722 end;
723 --
724 procedure get_accepted_appls(p_person_id NUMBER
725 ,p_num_accepted_appls in out nocopy NUMBER
726 ,p_new_primary_id in out nocopy NUMBER) is
727 --
728 no_accepted_assign exception;
729 --
730 begin
731 --
732 -- Get the number of currently accepted assignments.
733 --
734 select count(pa.assignment_id)
735 into p_num_accepted_appls
736 from per_assignments pa
737 , per_assignment_status_types past
738 where pa.person_id = p_person_id
739 and pa.assignment_status_type_id = past.assignment_status_type_id
740 and past.per_system_status = 'ACCEPTED';
741 --
742 -- Test to see how many there are.
743 --
744 if p_num_accepted_appls = 0 then
745 raise no_accepted_assign;
746 elsif p_num_accepted_appls = 1 then
747 --
748 -- If there is only one return it's value.
749 --
750 begin
751 select pa.assignment_id
752 into p_new_primary_id
753 from per_assignments pa
754 , per_assignment_status_types past
755 where pa.person_id = p_person_id
756 and pa.assignment_status_type_id = past.assignment_status_type_id
757 and past.per_system_status = 'ACCEPTED';
758 exception
759 when no_data_found then
760 raise no_accepted_assign;
761 when too_many_rows then
762 raise;
763 end;
764 end if;
765 --
766 exception
767 when no_accepted_assign then
768 hr_utility.set_message('801','HR_6428_EMP_NO_ACCEPT_ASS');
769 hr_utility.raise_error;
770 when others then
771 raise;
772 end;
773 --
774 procedure get_all_current_appls(p_person_id NUMBER
775 ,p_num_appls in out nocopy NUMBER) is
776 --
777 begin
778 --
779 -- Get the number of application assignments
780 -- which are current.
781 --
782 select count(pa.assignment_id)
783 into p_num_appls
784 from per_assignments pa
785 where pa.person_id =p_person_id
786 and pa.assignment_type = 'A';
787 end;
788 --
789 procedure get_date_range(p_person_id in number
790 ,p_min_start in out nocopy date
791 ,p_max_end in out nocopy date) is
792 --
793 -- Get the absolute date ranges that datetrack can
794 -- use to change the session date
795 --
796 cursor get_dates is
797 select min(effective_start_date), max(effective_end_date)
798 from per_people_f
799 where person_id = p_person_id;
800 begin
801 open get_dates;
802 fetch get_dates into p_min_start, p_max_end;
803 if get_dates%NOTFOUND then
804 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
805 hr_utility.set_message_token('PROCEDURE','get_date_range');
806 hr_utility.set_message_token('STEP','1');
807 hr_utility.raise_error;
808 end if;
809 close get_dates;
810 end;
811 --
812 procedure get_asg_date_range(p_assignment_id in number
813 ,p_min_start in out nocopy date
814 ,p_max_end in out nocopy date) is
815 --
816 -- Get the absolute date ranges that datetrack can
817 -- use to change the session date
818 --
819 cursor get_dates is
820 select min(effective_start_date), max(effective_end_date)
821 from per_assignments_f
822 where assignment_id = p_assignment_id;
823 begin
824 open get_dates;
825 fetch get_dates into p_min_start, p_max_end;
826 if get_dates%NOTFOUND then
827 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
828 hr_utility.set_message_token('PROCEDURE','get_asg_date_range');
829 hr_utility.set_message_token('STEP','1');
830 hr_utility.raise_error;
831 end if;
832 close get_dates;
833 end;
834 --
835 procedure form_post_query(p_ethnic_code IN VARCHAR2
839 ,p_veteran_code IN VARCHAR2
836 ,p_ethnic_meaning IN OUT NOCOPY VARCHAR2
837 ,p_visa_code IN VARCHAR2
838 ,p_visa_meaning IN OUT NOCOPY VARCHAR2
840 ,p_veteran_meaning IN OUT NOCOPY VARCHAR2
841 ,p_i9_code IN VARCHAR2
842 ,p_i9_meaning IN OUT NOCOPY VARCHAR2
843 ,p_legislation_code IN VARCHAR2)
844 IS
845 l_new_hire_code VARCHAR2(30);
846 l_new_hire_meaning VARCHAR2(80);
847 l_reason_for_code VARCHAR2(30);
848 l_reason_for_meaning VARCHAR2(80);
849 l_ethnic_disc_code VARCHAR2(30);
850 l_ethnic_disc_meaning VARCHAR2(80);
851 begin
852 per_people3_pkg.form_post_query(
853 p_ethnic_code
854 ,p_ethnic_meaning
855 ,p_visa_code
856 ,p_visa_meaning
857 ,p_veteran_code
858 ,p_veteran_meaning
859 ,p_i9_code
860 ,p_i9_meaning
861 ,l_new_hire_code
862 ,l_new_hire_meaning
863 ,l_reason_for_code
864 ,l_reason_for_meaning
865 ,l_ethnic_disc_code
866 ,l_ethnic_disc_meaning
867 ,p_legislation_code
868 );
869 end;
870 --
871 procedure form_post_query(p_ethnic_code IN VARCHAR2
872 ,p_ethnic_meaning IN OUT NOCOPY VARCHAR2
873 ,p_visa_code IN VARCHAR2
874 ,p_visa_meaning IN OUT NOCOPY VARCHAR2
875 ,p_veteran_code IN VARCHAR2
876 ,p_veteran_meaning IN OUT NOCOPY VARCHAR2
877 ,p_i9_code IN VARCHAR2
878 ,p_i9_meaning IN OUT NOCOPY VARCHAR2
879 ,p_new_hire_code IN VARCHAR2
880 ,p_new_hire_meaning IN OUT NOCOPY VARCHAR2
881 ,p_reason_for_code IN VARCHAR2
882 ,p_reason_for_meaning IN OUT NOCOPY VARCHAR2
883 ,p_legislation_code IN VARCHAR2)
884 IS
885 l_ethnic_disc_code VARCHAR2(30);
886 l_ethnic_disc_meaning VARCHAR2(80);
887 begin
888 per_people3_pkg.form_post_query(
889 p_ethnic_code
890 ,p_ethnic_meaning
891 ,p_visa_code
892 ,p_visa_meaning
893 ,p_veteran_code
894 ,p_veteran_meaning
895 ,p_i9_code
896 ,p_i9_meaning
897 ,p_new_hire_code
898 ,p_new_hire_meaning
899 ,p_reason_for_code
900 ,p_reason_for_meaning
901 ,l_ethnic_disc_code
902 ,l_ethnic_disc_meaning
903 ,p_legislation_code
904 );
905 end;
906 --
907 procedure form_post_query(p_ethnic_code IN VARCHAR2
908 ,p_ethnic_meaning IN OUT NOCOPY VARCHAR2
909 ,p_visa_code IN VARCHAR2
910 ,p_visa_meaning IN OUT NOCOPY VARCHAR2
911 ,p_veteran_code IN VARCHAR2
912 ,p_veteran_meaning IN OUT NOCOPY VARCHAR2
913 ,p_i9_code IN VARCHAR2
914 ,p_i9_meaning IN OUT NOCOPY VARCHAR2
915 ,p_new_hire_code IN VARCHAR2
916 ,p_new_hire_meaning IN OUT NOCOPY VARCHAR2
917 ,p_reason_for_code IN VARCHAR2
918 ,p_reason_for_meaning IN OUT NOCOPY VARCHAR2
919 ,p_ethnic_disc_code IN VARCHAR2
920 ,p_ethnic_disc_meaning IN OUT NOCOPY VARCHAR2
921 ,p_legislation_code IN VARCHAR2
922 ) is
923 l_vets100A_code VARCHAR2(30);
924 l_vets100A_meaning VARCHAR2(80);
925 begin
926 per_people3_pkg.form_post_query(
927 p_ethnic_code
928 ,p_ethnic_meaning
929 ,p_visa_code
930 ,p_visa_meaning
931 ,p_veteran_code
932 ,p_veteran_meaning
933 ,p_i9_code
934 ,p_i9_meaning
935 ,p_new_hire_code
936 ,p_new_hire_meaning
937 ,p_reason_for_code
938 ,p_reason_for_meaning
939 ,p_ethnic_disc_code
940 ,p_ethnic_disc_meaning
941 ,l_vets100A_code
942 ,l_vets100A_meaning
943 ,p_legislation_code
944 );
945 end;
946 --
947 -- Overloaded procedure for bug 7608613
948 procedure form_post_query(p_ethnic_code IN VARCHAR2
949 ,p_ethnic_meaning IN OUT NOCOPY VARCHAR2
950 ,p_visa_code IN VARCHAR2
951 ,p_visa_meaning IN OUT NOCOPY VARCHAR2
952 ,p_veteran_code IN VARCHAR2
953 ,p_veteran_meaning IN OUT NOCOPY VARCHAR2
954 ,p_i9_code IN VARCHAR2
955 ,p_i9_meaning IN OUT NOCOPY VARCHAR2
956 ,p_new_hire_code IN VARCHAR2
957 ,p_new_hire_meaning IN OUT NOCOPY VARCHAR2
958 ,p_reason_for_code IN VARCHAR2
959 ,p_reason_for_meaning IN OUT NOCOPY VARCHAR2
960 ,p_ethnic_disc_code IN VARCHAR2
961 ,p_ethnic_disc_meaning IN OUT NOCOPY VARCHAR2
962 ,p_vets100A_code IN VARCHAR2
963 ,p_vets100A_meaning IN OUT NOCOPY VARCHAR2
964 ,p_legislation_code IN VARCHAR2
965 ) is
966 begin
967 if (p_legislation_code = 'US') then
968 if (p_ethnic_code is not null) then
969 select fcl.meaning
970 into p_ethnic_meaning
971 from fnd_common_lookups fcl
972 where fcl.lookup_type = 'US_ETHNIC_GROUP'
973 and application_id = 800
974 and fcl.lookup_code = p_ethnic_code;
975 end if;
976 if (p_visa_code is not null) then
977 select fcl.meaning
978 into p_visa_meaning
979 from fnd_common_lookups fcl
980 where fcl.lookup_type = 'US_VISA_TYPE'
981 and application_id = 800
982 and fcl.lookup_code = p_visa_code;
983 end if;
984 if (p_veteran_code is not null) then
985 select fcl.meaning
986 into p_veteran_meaning
987 from fnd_common_lookups fcl
988 where fcl.lookup_type = 'US_VETERAN_STATUS'
989 and application_id = 800
990 and fcl.lookup_code = p_veteran_code;
991 end if;
992 if (p_i9_code is not null) then
993 select fcl.meaning
994 into p_i9_meaning
995 from fnd_common_lookups fcl
996 where fcl.lookup_type = 'PER_US_I9_STATE'
997 and application_id = 800
998 and fcl.lookup_code = p_i9_code;
999 end if;
1000 if (p_new_hire_code is not null) then
1001 select fcl.meaning
1002 into p_new_hire_meaning
1003 from fnd_common_lookups fcl
1004 where fcl.lookup_type = 'US_NEW_HIRE_STATUS'
1005 and application_id = 800
1006 and fcl.lookup_code = p_new_hire_code;
1007 end if;
1008 if (p_reason_for_code is not null) then
1009 select fcl.meaning
1010 into p_reason_for_meaning
1011 from fnd_common_lookups fcl
1012 where fcl.lookup_type = 'US_NEW_HIRE_EXCEPTIONS'
1013 and application_id = 800
1014 and fcl.lookup_code = p_reason_for_code;
1015 end if;
1016 if (p_ethnic_disc_code is not null) then
1017 select fcl.meaning
1018 into p_ethnic_disc_meaning
1019 from fnd_common_lookups fcl
1020 where fcl.lookup_type = 'US_ETHNIC_DISCLOSURE'
1021 and application_id = 800
1022 and fcl.lookup_code = p_ethnic_disc_code;
1023 end if;
1024 if (p_vets100A_code is not null) then
1025 select fcl.meaning
1026 into p_vets100A_meaning
1027 from fnd_common_lookups fcl
1028 where fcl.lookup_type = 'US_VETERAN_STATUS_VETS100A'
1029 and application_id = 800
1030 and fcl.lookup_code = p_vets100A_code;
1031 end if;
1032 elsif (p_legislation_code = 'GB') then
1033 if (p_ethnic_code is not null) then
1034 select fcl.meaning
1035 into p_ethnic_meaning
1036 from fnd_common_lookups fcl
1037 where fcl.lookup_type = 'ETH_TYPE'
1038 and application_id = 800
1039 and fcl.lookup_code = p_ethnic_code;
1040 end if;
1041 end if;
1042 end;
1043 --
1044 function chk_events_exist(p_person_id number
1045 ,p_business_group_id number
1046 ,p_hire_date date ) return boolean is
1047 --
1048 l_temp VARCHAR2(1);
1049 --
1050 begin
1051 select 'X'
1052 into l_temp
1053 from sys.dual
1054 where exists ( select 'Events rows exist'
1055 from per_events pe
1056 , per_assignments_f a
1057 where pe.business_group_id +0 = p_business_group_id
1058 and pe.assignment_id = a.assignment_id
1059 and pe.date_start
1060 between a.effective_start_date and a.effective_end_date
1061 and pe.date_start > p_hire_date
1062 and a.person_id = p_person_id
1063 );
1064 --
1065 return true;
1066 --
1067 exception
1068 when no_data_found then
1069 return false;
1070 end chk_events_exist;
1071 --
1072 --
1073 END per_people3_pkg;
[Home] [Help]