1 PACKAGE BODY PER_PEOPLE3_PKG AS
2 /* $Header: peper03t.pkb 120.0.12000000.2 2007/04/09 10:35:47 pdkundu noship $ */
3 --
4 --
5 procedure get_number_generation_property(p_business_group_id NUMBER
6 ,p_property_on NUMBER
7 ,p_property_off NUMBER
8 ,p_employee_property in out nocopy NUMBER
9 ,p_applicant_property in out nocopy NUMBER) is
10 --
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
144 ,p_applicant_property IN OUT NOCOPY NUMBER
145 ,p_required_emp_type VARCHAR2
146 ,p_required_app_type VARCHAR2
147 ,p_emp_person_type IN OUT NOCOPY NUMBER
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'
267 and paf.effective_start_date >= p_hire_date);
268 begin
269 open fut_apl;
270 fetch fut_apl into l_dummy;
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 procedure update_period(p_person_id number
326 ,p_hire_date date
327 ,p_new_hire_date date
328 ,p_adjusted_svc_date in date ) is
329 --
330 -- Update Period of serivice start date when Hire_date
331 -- has changed and Person_type has not.
332 --
333 -- Define Cursor.
334 --
335 cursor pps is select rowid,pps.*
336 from per_periods_of_service pps
337 where person_id = p_person_id
338 and date_start = p_hire_date
339 for update of date_start nowait;
340 --
341 -- Local Variables.
342 --
343 pps_rec pps%rowtype;
344 l_adjusted_svc_date DATE;
345 --
346 begin
347 --
348 open pps;
349 <<pps_loop>>
350 loop
351 exit pps_loop when pps%NOTFOUND;
352 fetch pps into pps_rec;
353 end loop pps_loop;
354 --
355 if pps%rowcount <>1 then
356 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
360 --
357 hr_utility.set_message_token('PROCEDURE','per_people3_pkg.UPDATE_PERIOD');
358 hr_utility.raise_error;
359 end if;
361 close pps;
362 --
363 -- # 1573563
364 if nvl(p_adjusted_svc_date,hr_general.end_of_time) <> hr_api.g_date then
365 l_adjusted_svc_date := p_adjusted_svc_date;
366 else
367 l_adjusted_svc_date := pps_rec.adjusted_svc_date;
368 end if;
369 -- Now update the row;
370 --
371 per_periods_of_service_pkg.update_row(p_row_id => pps_rec.rowid
372 ,p_period_of_service_id => pps_rec.PERIOD_OF_SERVICE_ID
373 ,p_business_group_id => pps_rec.BUSINESS_GROUP_ID
374 ,p_person_id => pps_rec.PERSON_ID
375 ,p_date_start => p_new_hire_date
376 ,p_termination_accepted_per_id => pps_rec.TERMINATION_ACCEPTED_PERSON_ID
377 ,p_accepted_termination_date => pps_rec.ACCEPTED_TERMINATION_DATE
378 ,p_actual_termination_date => pps_rec.ACTUAL_TERMINATION_DATE
379 ,p_comments => pps_rec.COMMENTS
380 ,p_final_process_date => pps_rec.FINAL_PROCESS_DATE
381 ,p_last_standard_process_date => pps_rec.LAST_STANDARD_PROCESS_DATE
382 ,p_leaving_reason => pps_rec.LEAVING_REASON
383 ,p_notified_termination_date => pps_rec.NOTIFIED_TERMINATION_DATE
384 ,p_projected_termination_date => pps_rec.PROJECTED_TERMINATION_DATE
385 ,p_request_id => pps_rec.REQUEST_ID
386 ,p_program_application_id => pps_rec.PROGRAM_APPLICATION_ID
387 ,p_program_id => pps_rec.PROGRAM_ID
388 ,p_program_update_date => pps_rec.PROGRAM_UPDATE_DATE
389 ,p_attribute_category => pps_rec.ATTRIBUTE_CATEGORY
390 ,p_attribute1 => pps_rec.ATTRIBUTE1
391 ,p_attribute2 => pps_rec.ATTRIBUTE2
392 ,p_attribute3 => pps_rec.ATTRIBUTE3
393 ,p_attribute4 => pps_rec.ATTRIBUTE4
394 ,p_attribute5 => pps_rec.ATTRIBUTE5
395 ,p_attribute6 => pps_rec.ATTRIBUTE6
396 ,p_attribute7 => pps_rec.ATTRIBUTE7
397 ,p_attribute8 => pps_rec.ATTRIBUTE8
398 ,p_attribute9 => pps_rec.ATTRIBUTE9
399 ,p_attribute10 => pps_rec.ATTRIBUTE10
400 ,p_attribute11 => pps_rec.ATTRIBUTE11
401 ,p_attribute12 => pps_rec.ATTRIBUTE12
402 ,p_attribute13 => pps_rec.ATTRIBUTE13
403 ,p_attribute14 => pps_rec.ATTRIBUTE14
404 ,p_attribute15 => pps_rec.ATTRIBUTE15
405 ,p_attribute16 => pps_rec.ATTRIBUTE16
406 ,p_attribute17 => pps_rec.ATTRIBUTE17
407 ,p_attribute18 => pps_rec.ATTRIBUTE18
408 ,p_attribute19 => pps_rec.ATTRIBUTE19
409 ,p_attribute20 => pps_rec.ATTRIBUTE20
410 ,p_pds_information_category => pps_rec.PDS_INFORMATION_CATEGORY
411 ,p_pds_information1 => pps_rec.PDS_INFORMATION1
412 ,p_pds_information2 => pps_rec.PDS_INFORMATION2
413 ,p_pds_information3 => pps_rec.PDS_INFORMATION3
414 ,p_pds_information4 => pps_rec.PDS_INFORMATION4
415 ,p_pds_information5 => pps_rec.PDS_INFORMATION5
416 ,p_pds_information6 => pps_rec.PDS_INFORMATION6
417 ,p_pds_information7 => pps_rec.PDS_INFORMATION7
418 ,p_pds_information8 => pps_rec.PDS_INFORMATION8
419 ,p_pds_information9 => pps_rec.PDS_INFORMATION9
420 ,p_pds_information10 => pps_rec.PDS_INFORMATION10
421 ,p_pds_information11 => pps_rec.PDS_INFORMATION11
422 ,p_pds_information12 => pps_rec.PDS_INFORMATION12
423 ,p_pds_information13 => pps_rec.PDS_INFORMATION13
424 ,p_pds_information14 => pps_rec.PDS_INFORMATION14
425 ,p_pds_information15 => pps_rec.PDS_INFORMATION15
426 ,p_pds_information16 => pps_rec.PDS_INFORMATION16
427 ,p_pds_information17 => pps_rec.PDS_INFORMATION17
428 ,p_pds_information18 => pps_rec.PDS_INFORMATION18
429 ,p_pds_information19 => pps_rec.PDS_INFORMATION19
430 ,p_pds_information20 => pps_rec.PDS_INFORMATION20
431 ,p_pds_information21 => pps_rec.PDS_INFORMATION21
432 ,p_pds_information22 => pps_rec.PDS_INFORMATION22
433 ,p_pds_information23 => pps_rec.PDS_INFORMATION23
434 ,p_pds_information24 => pps_rec.PDS_INFORMATION24
435 ,p_pds_information25 => pps_rec.PDS_INFORMATION25
436 ,p_pds_information26 => pps_rec.PDS_INFORMATION26
437 ,p_pds_information27 => pps_rec.PDS_INFORMATION27
438 ,p_pds_information28 => pps_rec.PDS_INFORMATION28
439 ,p_pds_information29 => pps_rec.PDS_INFORMATION29
440 ,p_pds_information30 => pps_rec.PDS_INFORMATION30
441 ,p_adjusted_svc_date => l_adjusted_svc_date);
442 --
443 end update_period;
444 --
445 procedure run_alu_ee(p_alu_mode VARCHAR2
446 ,p_business_group_id NUMBER
447 ,p_person_id NUMBER
448 ,p_old_start DATE
449 ,p_start_date date) is
450 --
451 -- Checks the assignment link usages and Element_entries
452 -- code for changes in assignment and Personal qualifying criteria
453 --
454 -- Local Variables.
455 --
459 l_entries_changed VARCHAR2(1);
456 l_assignment_id number; -- assignment_id of employee assignment.
457 l_validation_start_date date; -- End date_of Assignment.
458 l_validation_end_date date; -- End date_of Assignment.
460 --
461 -- Cursor
462 --
463 cursor ass_cur is
464 select assignment_id
465 from per_all_assignments_f paf
466 where paf.person_id = p_person_id
467 and paf.assignment_type = 'E'
468 and p_start_date between
469 paf.effective_start_date and paf.effective_end_date;
470 --
471 begin
472 -- Set the correct validation start and end dates for
473 -- the assignments. These are the same for all
474 -- assignments of a multiple assignment person.
475 if(p_start_date > p_old_start) then
476 -- We have moved the hire date forwards.
477 l_validation_start_date := p_old_start;
478 l_validation_end_date := (p_start_date - 1);
479 elsif(p_start_date < p_old_start) then
480 -- We have moved the hire date backwards.
481 l_validation_start_date := p_start_date;
482 l_validation_end_date := (p_old_start - 1);
483 end if;
484 --
485 open ass_cur;
486 loop
487 fetch ass_cur into l_assignment_id;
488 exit when ass_cur%NOTFOUND;
489 if p_alu_mode = 'ASG_CRITERIA' then
490 -- changed cal to use p_old_payroll_id => 2
491 -- and p_new_payroll_id=> 1 so that the NR entries get updated.
492 if(p_start_date <> p_old_start) then
493 -- Only call this if the hire dates have actually changed.
494 hrentmnt.maintain_entries_asg
495 (p_assignment_id =>l_assignment_id
496 ,p_old_payroll_id =>2
497 ,p_new_payroll_id =>1
498 ,p_business_group_id =>p_business_group_id
499 ,p_operation =>p_alu_mode
500 ,p_actual_term_date => NULL
501 ,p_last_standard_date =>NULL
502 ,p_final_process_date => NULL
503 ,p_validation_start_date => l_validation_start_date
504 ,p_validation_end_date => l_validation_end_date
505 ,p_dt_mode =>'CORRECTION'
506 ,p_old_hire_date => p_old_start
507 ,p_entries_changed =>l_entries_changed);
508 end if;
509 end if;
510 --
511 hrentmnt.maintain_entries_asg(l_assignment_id
512 ,p_business_group_id
513 ,'CHANGE_PQC'
514 ,NULL
515 ,NULL
516 ,NULL
517 ,NULL
518 ,NULL
519 ,NULL);
520 end loop;
521 close ass_cur;
522 end;
523 --
524 procedure vacancy_chk(p_person_id NUMBER
525 ,p_fire_warning in out nocopy VARCHAR2
526 ,p_vacancy_id in out nocopy NUMBER
527 -- #2381925
528 ,p_table IN HR_EMPLOYEE_APPLICANT_API.t_ApplTable
529 --
530 ) is
531 --
532 -- Check all Vacanicies person has applied for and check for
533 -- them being over-subscribed.
534 --
535 -- Local Variables
536 --
537 l_vacancy_name VARCHAR2(30);-- Name of returned vacancy.
538 l_dummy_id NUMBER(15); -- Dummy variable.
539 over_subscribed EXCEPTION; -- Over-subscribed exception.
540 --
541 l_asg_id number; -- #2381925: assignment id returned
542 --
543 -- Cursor.
544 -- note p_last_vacancy is a parameter to the cursor defineition.
545 --
546 cursor app_ass(p_last_vacancy number) is
547 select pav.vacancy_id,pav.name
548 ,pa.assignment_id -- #2381925
549 from per_assignments pa, per_all_vacancies pav
550 , per_assignment_status_types pas
551 where person_id = p_person_id
552 and pav.vacancy_id = pa.vacancy_id
553 and pa.assignment_status_type_id = pas.assignment_status_type_id
554 and pas.per_system_status = 'ACCEPTED'
555 and pa.assignment_type = 'A'
556 and pav.vacancy_id >nvl(p_last_vacancy,0)
557 order by pav.vacancy_id asc;
558 --
559 begin
560 --
561 -- set warning to not fire.
562 --
563 p_fire_warning := 'N';
564 --
565 -- Get all vacancies that employee has applied for.
566 --
567 open app_ass(p_vacancy_id);
568 loop
569 fetch app_ass into p_vacancy_id,l_vacancy_name, l_asg_id; --#2381925
570 exit when app_ass%NOTFOUND;
571 begin
572 -- 2381925: Verify if vacancy is over-subscribed ONLY if applicant
573 -- assignment is being hired.
574 --
575 if hr_employee_applicant_api.is_convert(p_table,l_asg_id) then
576
577 select vacancy_id
578 into l_dummy_id
579 from per_all_vacancies pav
580 where pav.number_of_openings <
581 (select count(distinct assignment_id) + 1
582 from per_all_assignments_f paf
583 where paf.vacancy_id = pav.vacancy_id
584 and paf.assignment_type = 'E')
585 and pav.vacancy_id = p_vacancy_id;
586 --
590 --
587 -- If a row is returned then the vacancy is over-subscribed
588 -- set message and warning flag.
589 -- raise exception.
591 fnd_message.set_name('PER','HR_EMP_VAC_FILLED');
592 fnd_message.set_token('VAC',l_vacancy_name);
593 p_fire_warning := 'Y';
594 raise over_subscribed;
595 end if; -- is asg hired ?
596 exception
597 when no_data_found then
598 null;
599 when too_many_rows then
600 fnd_message.set_name('PER','HR_EMP_VAC_FILLED');
601 fnd_message.set_token('VAC','Too many rows');
602 app_exception.raise_exception;
603 end;
604 end loop;
605 --
606 close app_ass;
607 --
608 exception
609 when over_subscribed then
610 close app_ass;
611 when no_data_found then
612 if app_ass%rowcount < 1 then
613 raise;
614 end if;
615 close app_ass;
616 when too_many_rows then
617 raise;
618 end;
619 --
620 procedure get_accepted_appls(p_person_id NUMBER
621 ,p_num_accepted_appls in out nocopy NUMBER
622 ,p_new_primary_id in out nocopy NUMBER) is
623 --
624 no_accepted_assign exception;
625 --
626 begin
627 --
628 -- Get the number of currently accepted assignments.
629 --
630 select count(pa.assignment_id)
631 into p_num_accepted_appls
632 from per_assignments pa
633 , per_assignment_status_types past
634 where pa.person_id = p_person_id
635 and pa.assignment_status_type_id = past.assignment_status_type_id
636 and past.per_system_status = 'ACCEPTED';
637 --
638 -- Test to see how many there are.
639 --
640 if p_num_accepted_appls = 0 then
641 raise no_accepted_assign;
642 elsif p_num_accepted_appls = 1 then
643 --
644 -- If there is only one return it's value.
645 --
646 begin
647 select pa.assignment_id
648 into p_new_primary_id
649 from per_assignments pa
650 , per_assignment_status_types past
651 where pa.person_id = p_person_id
652 and pa.assignment_status_type_id = past.assignment_status_type_id
653 and past.per_system_status = 'ACCEPTED';
654 exception
655 when no_data_found then
656 raise no_accepted_assign;
657 when too_many_rows then
658 raise;
659 end;
660 end if;
661 --
662 exception
663 when no_accepted_assign then
664 hr_utility.set_message('801','HR_6428_EMP_NO_ACCEPT_ASS');
665 hr_utility.raise_error;
666 when others then
667 raise;
668 end;
669 --
670 procedure get_all_current_appls(p_person_id NUMBER
671 ,p_num_appls in out nocopy NUMBER) is
672 --
673 begin
674 --
675 -- Get the number of application assignments
676 -- which are current.
677 --
678 select count(pa.assignment_id)
679 into p_num_appls
680 from per_assignments pa
681 where pa.person_id =p_person_id
682 and pa.assignment_type = 'A';
683 end;
684 --
685 procedure get_date_range(p_person_id in number
686 ,p_min_start in out nocopy date
687 ,p_max_end in out nocopy date) is
688 --
689 -- Get the absolute date ranges that datetrack can
690 -- use to change the session date
691 --
692 cursor get_dates is
693 select min(effective_start_date), max(effective_end_date)
694 from per_people_f
695 where person_id = p_person_id;
696 begin
697 open get_dates;
698 fetch get_dates into p_min_start, p_max_end;
699 if get_dates%NOTFOUND then
700 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
701 hr_utility.set_message_token('PROCEDURE','get_date_range');
702 hr_utility.set_message_token('STEP','1');
703 hr_utility.raise_error;
704 end if;
705 close get_dates;
706 end;
707 --
708 procedure get_asg_date_range(p_assignment_id in number
709 ,p_min_start in out nocopy date
710 ,p_max_end in out nocopy date) is
711 --
712 -- Get the absolute date ranges that datetrack can
713 -- use to change the session date
714 --
715 cursor get_dates is
716 select min(effective_start_date), max(effective_end_date)
717 from per_assignments_f
718 where assignment_id = p_assignment_id;
719 begin
720 open get_dates;
721 fetch get_dates into p_min_start, p_max_end;
722 if get_dates%NOTFOUND then
723 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
724 hr_utility.set_message_token('PROCEDURE','get_asg_date_range');
725 hr_utility.set_message_token('STEP','1');
726 hr_utility.raise_error;
727 end if;
728 close get_dates;
729 end;
730 --
731 procedure form_post_query(p_ethnic_code IN VARCHAR2
732 ,p_ethnic_meaning IN OUT NOCOPY VARCHAR2
733 ,p_visa_code IN VARCHAR2
734 ,p_visa_meaning IN OUT NOCOPY VARCHAR2
735 ,p_veteran_code IN VARCHAR2
739 ,p_legislation_code IN VARCHAR2)
736 ,p_veteran_meaning IN OUT NOCOPY VARCHAR2
737 ,p_i9_code IN VARCHAR2
738 ,p_i9_meaning IN OUT NOCOPY VARCHAR2
740 IS
741 l_new_hire_code VARCHAR2(30);
742 l_new_hire_meaning VARCHAR2(80);
743 l_reason_for_code VARCHAR2(30);
744 l_reason_for_meaning VARCHAR2(80);
745 l_ethnic_disc_code VARCHAR2(30);
746 l_ethnic_disc_meaning VARCHAR2(80);
747 begin
748 per_people3_pkg.form_post_query(
749 p_ethnic_code
750 ,p_ethnic_meaning
751 ,p_visa_code
752 ,p_visa_meaning
753 ,p_veteran_code
754 ,p_veteran_meaning
755 ,p_i9_code
756 ,p_i9_meaning
757 ,l_new_hire_code
758 ,l_new_hire_meaning
759 ,l_reason_for_code
760 ,l_reason_for_meaning
761 ,l_ethnic_disc_code
762 ,l_ethnic_disc_meaning
763 ,p_legislation_code
764 );
765 end;
766 --
767 procedure form_post_query(p_ethnic_code IN VARCHAR2
768 ,p_ethnic_meaning IN OUT NOCOPY VARCHAR2
769 ,p_visa_code IN VARCHAR2
770 ,p_visa_meaning IN OUT NOCOPY VARCHAR2
771 ,p_veteran_code IN VARCHAR2
772 ,p_veteran_meaning IN OUT NOCOPY VARCHAR2
773 ,p_i9_code IN VARCHAR2
774 ,p_i9_meaning IN OUT NOCOPY VARCHAR2
775 ,p_new_hire_code IN VARCHAR2
776 ,p_new_hire_meaning IN OUT NOCOPY VARCHAR2
777 ,p_reason_for_code IN VARCHAR2
778 ,p_reason_for_meaning IN OUT NOCOPY VARCHAR2
779 ,p_legislation_code IN VARCHAR2)
780 IS
781 l_ethnic_disc_code VARCHAR2(30);
782 l_ethnic_disc_meaning VARCHAR2(80);
783 begin
784 per_people3_pkg.form_post_query(
785 p_ethnic_code
786 ,p_ethnic_meaning
787 ,p_visa_code
788 ,p_visa_meaning
789 ,p_veteran_code
790 ,p_veteran_meaning
791 ,p_i9_code
792 ,p_i9_meaning
793 ,p_new_hire_code
794 ,p_new_hire_meaning
795 ,p_reason_for_code
796 ,p_reason_for_meaning
797 ,l_ethnic_disc_code
798 ,l_ethnic_disc_meaning
799 ,p_legislation_code
800 );
801 end;
802 --
803 procedure form_post_query(p_ethnic_code IN VARCHAR2
804 ,p_ethnic_meaning IN OUT NOCOPY VARCHAR2
805 ,p_visa_code IN VARCHAR2
806 ,p_visa_meaning IN OUT NOCOPY VARCHAR2
807 ,p_veteran_code IN VARCHAR2
808 ,p_veteran_meaning IN OUT NOCOPY VARCHAR2
809 ,p_i9_code IN VARCHAR2
810 ,p_i9_meaning IN OUT NOCOPY VARCHAR2
811 ,p_new_hire_code IN VARCHAR2
812 ,p_new_hire_meaning IN OUT NOCOPY VARCHAR2
813 ,p_reason_for_code IN VARCHAR2
814 ,p_reason_for_meaning IN OUT NOCOPY VARCHAR2
815 ,p_ethnic_disc_code IN VARCHAR2
816 ,p_ethnic_disc_meaning IN OUT NOCOPY VARCHAR2
817 ,p_legislation_code IN VARCHAR2
818 ) is
819 begin
820 if (p_legislation_code = 'US') then
821 if (p_ethnic_code is not null) then
822 select fcl.meaning
823 into p_ethnic_meaning
824 from fnd_common_lookups fcl
825 where fcl.lookup_type = 'US_ETHNIC_GROUP'
826 and application_id = 800
827 and fcl.lookup_code = p_ethnic_code;
828 end if;
829 if (p_visa_code is not null) then
830 select fcl.meaning
831 into p_visa_meaning
832 from fnd_common_lookups fcl
833 where fcl.lookup_type = 'US_VISA_TYPE'
834 and application_id = 800
835 and fcl.lookup_code = p_visa_code;
836 end if;
837 if (p_veteran_code is not null) then
838 select fcl.meaning
839 into p_veteran_meaning
840 from fnd_common_lookups fcl
841 where fcl.lookup_type = 'US_VETERAN_STATUS'
842 and application_id = 800
843 and fcl.lookup_code = p_veteran_code;
844 end if;
845 if (p_i9_code is not null) then
846 select fcl.meaning
847 into p_i9_meaning
848 from fnd_common_lookups fcl
849 where fcl.lookup_type = 'PER_US_I9_STATE'
850 and application_id = 800
851 and fcl.lookup_code = p_i9_code;
852 end if;
853 if (p_new_hire_code is not null) then
854 select fcl.meaning
855 into p_new_hire_meaning
856 from fnd_common_lookups fcl
857 where fcl.lookup_type = 'US_NEW_HIRE_STATUS'
858 and application_id = 800
859 and fcl.lookup_code = p_new_hire_code;
860 end if;
861 if (p_reason_for_code is not null) then
862 select fcl.meaning
863 into p_reason_for_meaning
864 from fnd_common_lookups fcl
865 where fcl.lookup_type = 'US_NEW_HIRE_EXCEPTIONS'
866 and application_id = 800
867 and fcl.lookup_code = p_reason_for_code;
868 end if;
869 if (p_ethnic_disc_code is not null) then
870 select fcl.meaning
871 into p_ethnic_disc_meaning
872 from fnd_common_lookups fcl
873 where fcl.lookup_type = 'US_ETHNIC_DISCLOSURE'
874 and application_id = 800
875 and fcl.lookup_code = p_ethnic_disc_code;
876 end if;
877 elsif (p_legislation_code = 'GB') then
878 if (p_ethnic_code is not null) then
879 select fcl.meaning
880 into p_ethnic_meaning
881 from fnd_common_lookups fcl
882 where fcl.lookup_type = 'ETH_TYPE'
883 and application_id = 800
884 and fcl.lookup_code = p_ethnic_code;
885 end if;
886 end if;
887 end;
888 --
889 function chk_events_exist(p_person_id number
890 ,p_business_group_id number
891 ,p_hire_date date ) return boolean is
892 --
893 l_temp VARCHAR2(1);
894 --
895 begin
896 select 'X'
897 into l_temp
898 from sys.dual
899 where exists ( select 'Events rows exist'
900 from per_events pe
901 , per_assignments_f a
902 where pe.business_group_id +0 = p_business_group_id
903 and pe.assignment_id = a.assignment_id
904 and pe.date_start
905 between a.effective_start_date and a.effective_end_date
906 and pe.date_start > p_hire_date
907 and a.person_id = p_person_id
908 );
909 --
910 return true;
911 --
912 exception
913 when no_data_found then
914 return false;
915 end chk_events_exist;
916 --
917 --
918 END per_people3_pkg;
[Home] [Help]