1 PACKAGE BODY PER_PEOPLE4_PKG AS
2 /* $Header: peper04t.pkb 120.7.12020000.2 2012/07/05 03:09:38 amnaraya ship $ */
3 --
4 g_package varchar2(30) := ' PER_PEOPLE4_PKG.'; -- increased the length (ver 115.26)
5 g_debug boolean; -- debug flag
6 --
7
8 procedure update_row(p_rowid VARCHAR2
9 ,p_person_id NUMBER
10 ,p_effective_start_date DATE
11 ,p_effective_end_date DATE
12 ,p_business_group_id NUMBER
13 ,p_person_type_id NUMBER
14 ,p_last_name VARCHAR2
15 ,p_start_date DATE
16 ,p_applicant_number IN OUT NOCOPY VARCHAR2
17 ,p_comment_id NUMBER
18 ,p_current_applicant_flag VARCHAR2
19 ,p_current_emp_or_apl_flag VARCHAR2
20 ,p_current_employee_flag VARCHAR2
21 ,p_date_employee_data_verified DATE
22 ,p_date_of_birth DATE
23 ,p_email_address VARCHAR2
24 ,p_employee_number IN OUT NOCOPY VARCHAR2
25 ,p_expense_check_send_to_addr VARCHAR2
26 ,p_first_name VARCHAR2
27 ,p_full_name VARCHAR2
28 ,p_known_as VARCHAR2
29 ,p_marital_status VARCHAR2
30 ,p_middle_names VARCHAR2
31 ,p_nationality VARCHAR2
32 ,p_national_identifier VARCHAR2
33 ,p_previous_last_name VARCHAR2
34 ,p_registered_disabled_flag VARCHAR2
35 ,p_sex VARCHAR2
36 ,p_title VARCHAR2
37 ,p_suffix VARCHAR2
38 ,p_vendor_id NUMBER
39 ,p_work_telephone VARCHAR2
40 ,p_request_id NUMBER
41 ,p_program_application_id NUMBER
42 ,p_program_id NUMBER
43 ,p_program_update_date DATE
44 ,p_a_cat VARCHAR2
45 ,p_a1 VARCHAR2
46 ,p_a2 VARCHAR2
47 ,p_a3 VARCHAR2
48 ,p_a4 VARCHAR2
49 ,p_a5 VARCHAR2
50 ,p_a6 VARCHAR2
51 ,p_a7 VARCHAR2
52 ,p_a8 VARCHAR2
53 ,p_a9 VARCHAR2
54 ,p_a10 VARCHAR2
55 ,p_a11 VARCHAR2
56 ,p_a12 VARCHAR2
57 ,p_a13 VARCHAR2
58 ,p_a14 VARCHAR2
59 ,p_a15 VARCHAR2
60 ,p_a16 VARCHAR2
61 ,p_a17 VARCHAR2
62 ,p_a18 VARCHAR2
63 ,p_a19 VARCHAR2
64 ,p_a20 VARCHAR2
65 ,p_a21 VARCHAR2
66 ,p_a22 VARCHAR2
67 ,p_a23 VARCHAR2
68 ,p_a24 VARCHAR2
69 ,p_a25 VARCHAR2
70 ,p_a26 VARCHAR2
71 ,p_a27 VARCHAR2
72 ,p_a28 VARCHAR2
73 ,p_a29 VARCHAR2
74 ,p_a30 VARCHAR2
75 ,p_last_update_date DATE
76 ,p_last_updated_by NUMBER
77 ,p_last_update_login NUMBER
78 ,p_created_by NUMBER
79 ,p_creation_date DATE
80 ,p_i_cat VARCHAR2
81 ,p_i1 VARCHAR2
82 ,p_i2 VARCHAR2
83 ,p_i3 VARCHAR2
84 ,p_i4 VARCHAR2
85 ,p_i5 VARCHAR2
86 ,p_i6 VARCHAR2
87 ,p_i7 VARCHAR2
88 ,p_i8 VARCHAR2
89 ,p_i9 VARCHAR2
90 ,p_i10 VARCHAR2
91 ,p_i11 VARCHAR2
92 ,p_i12 VARCHAR2
93 ,p_i13 VARCHAR2
94 ,p_i14 VARCHAR2
95 ,p_i15 VARCHAR2
96 ,p_i16 VARCHAR2
97 ,p_i17 VARCHAR2
98 ,p_i18 VARCHAR2
99 ,p_i19 VARCHAR2
100 ,p_i20 VARCHAR2
101 ,p_i21 VARCHAR2
102 ,p_i22 VARCHAR2
103 ,p_i23 VARCHAR2
104 ,p_i24 VARCHAR2
105 ,p_i25 VARCHAR2
106 ,p_i26 VARCHAR2
107 ,p_i27 VARCHAR2
108 ,p_i28 VARCHAR2
109 ,p_i29 VARCHAR2
110 ,p_i30 VARCHAR2
111 ,p_app_ass_status_type_id NUMBER
112 ,p_emp_ass_status_type_id NUMBER
113 ,p_system_person_type VARCHAR2
114 ,p_s_system_person_type VARCHAR2
115 ,p_hire_date DATE
116 ,p_s_hire_date DATE
117 ,p_s_date_of_birth DATE
118 ,p_status in out nocopy VARCHAR2
119 ,p_new_primary_id in out nocopy NUMBER
120 ,p_update_primary in out nocopy VARCHAR2
121 ,p_legislation_code VARCHAR2
122 ,p_vacancy_id IN OUT NOCOPY NUMBER
123 ,p_session_date date
124 ,p_end_of_time date) is
125 --
126 l_period_of_service_id number; -- Period of Service id.
127 l_employ_emp_apl varchar2(1); -- Are we employing an EMP_APL?
128 l_fire_warning varchar2(1); -- If set Y return to form displaying warning.
129 l_num_appls NUMBER; -- Number of applicants.
130 l_num_accepted_appls NUMBER; -- Number of accepted spplicant assignments
131 l_set_of_books_id NUMBER; -- Required for GL.
132 v_dummy NUMBER; -- For cursor fetch.
133 l_npw_number per_all_people_f.npw_number%type;
134 l_party_id per_all_people_f.party_id%type;
135 --
136 cursor get_pay_proposal
137 is
138 select PAY_PROPOSAL_ID
139 from per_pay_proposals
140 where change_date = p_s_hire_date
141 and assignment_id = (select assignment_id
142 from per_assignments_f
143 where person_id = p_person_id
144 and primary_flag = 'Y'
145 and effective_start_date = p_hire_date
146 and assignment_type = 'E'
147 );
148 --
149 cursor csr_partyId_details is -- Enh 3299580
150 select party_id
151 from per_all_people_f
152 where person_id = p_person_id
153 and p_session_date between effective_start_date
154 and effective_end_date;
155 --
156 l_proc varchar2(10) := 'update_row';
157 --
158 begin
159 g_debug := hr_utility.debug_enabled; -- get debug status
160 IF g_debug THEN
161 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
162 END IF;
163 --
164 -- p_status has the Value of where the code should start on re-entry.
165 -- on startup = 'BEGIN'( First time called from form)
166 -- other values depend on what meesages have been returned to the client
167 -- and the re-entry point on return from the client.
168 --
169 if p_status = 'BEGIN' then
170 --
171 -- Test to see if the hire_date_has changed
172 -- Providing Person type has not and it is emp.
173 -- Or that it has changed to EMP
174 --
175 if (p_hire_date <> p_s_hire_date)
176 and (p_s_hire_date is not null)
177 and (((p_system_person_type = p_s_system_person_type)
178 and p_system_person_type in('EMP','EMP_APL'))
179 or ((p_system_person_type = 'EMP'
180 and p_s_system_person_type in ('APL','APL_EX_APL','EX_EMP_APL'))
181 or (p_system_person_type = 'EMP_APL'
182 and p_s_system_person_type = 'APL')
183 or (p_system_person_type = 'EMP'
184 and p_s_system_person_type = 'EMP_APL'))) then
185 -- get the period_of_service_id
186 -- 303729 if person is a supervisor
187 -- test whether change to hire_date would invalidate this action
188 --
189 --
190 if ((p_hire_date <> p_s_hire_date)
191 and (p_system_person_type = p_s_system_person_type))
192 then
193 IF g_debug THEN
194 hr_utility.set_location(g_package || l_proc, 10);
195 END IF;
196 per_people4_pkg.check_not_supervisor(p_person_id
197 ,p_hire_date
198 ,p_s_hire_date);
199 end if;
200 IF g_debug THEN
201 hr_utility.set_location(g_package || l_proc, 20);
202 END IF;
203 begin
204 select pps.period_of_service_id
205 into l_period_of_service_id
206 from per_periods_of_service pps
207 where pps.person_id = p_person_id
208 and pps.date_start = p_s_hire_date;
209 --
210 exception
211 when no_data_found then
212 --
213 -- If no data found and a previous hire date existed
214 -- then raise an error;
215 --
216 if p_s_hire_date is not null then
217 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
218 hr_utility.set_message_token('PROCEDURE','Update_row');
219 hr_utility.raise_error;
220 end if;
221 end;
222 --
223 -- check the integrity of the date change.
224 -- Date may come in between a person type change.
225 --
226 IF g_debug THEN
227 hr_utility.set_location(g_package || l_proc, 30);
228 END IF;
229 hr_date_chk.check_hire_ref_int(p_person_id
230 ,p_business_group_id
231 ,l_period_of_service_id
232 ,p_s_hire_date
233 ,p_system_person_type
234 ,p_hire_date);
235 end if;
236 --
237 -- check session date and effective_start_date for differences
238 -- if any exists then ensure the person record is correct
239 -- i.e duplicate datetrack functionality as it currently uses
240 -- a global version of session date to update the rows (not good)
241 --
242 -- VT 08/13/96
243 if p_session_date <> p_effective_start_date then
244 IF g_debug THEN
245 hr_utility.set_location(g_package || l_proc, 40);
246 END IF;
247 per_people9_pkg.update_old_person_row(p_person_id =>p_person_id
248 ,p_session_date => p_session_date
249 ,p_effective_start_date=>p_effective_start_date);
250 end if;
251 --
252 -- get the Employee and applicant numbers if necessary
253 -- only returns values depending on values of
254 -- p_current_applicant_flag, p_current_applicant_flag
255 -- and whether p_employee_number and p_applicant_number
256 -- are null.
257 --
258 IF g_debug THEN
259 hr_utility.set_location(g_package || l_proc, 50);
260 END IF;
261 open csr_partyId_details; -- Enh 3299580
262 fetch csr_partyId_details into l_party_id;
263 close csr_partyId_details;
264 --
265 IF g_debug THEN
266 hr_utility.set_location(g_package || l_proc, 60);
267 END IF;
268 hr_person.generate_number(p_current_employee_flag
269 ,p_current_applicant_flag
270 ,NULL --p_current_npw_flag
271 ,p_national_identifier
272 ,p_business_group_id
273 ,p_person_id
274 ,p_employee_number
275 ,p_applicant_number
276 ,l_npw_number
277 -- Enh 3299580 --
278 ,p_session_date
279 ,l_party_id
280 ,p_date_of_birth
281 ,p_hire_date
282 );
283 --
284 -- Test current numbers are not used by
285 -- the system already.
286 --
287 IF g_debug THEN
288 hr_utility.set_location(g_package || l_proc, 70);
289 END IF;
290 hr_person.validate_unique_number(p_person_id =>p_person_id
291 , p_business_group_id => p_business_group_id
292 , p_employee_number => p_employee_number
293 , p_applicant_number => p_applicant_number
294 , p_npw_number => null --p_npw_number
295 , p_current_employee => p_current_employee_flag
296 , p_current_applicant => p_current_applicant_flag
297 , p_current_npw => null --p_current_npw_flag
298 );
299 p_status := 'VACANCY_CHECK'; -- Set status to next possible reentry point.
300 end if; -- End the First in section
301 --
302 -- Start of Person type changes.
303 --
304 -- Has the Person type changed to become that of an applicant?
305 --
306 if (p_system_person_type ='APL'
307 and p_s_system_person_type = 'OTHER')
308 or (p_system_person_type = 'APL_EX_APL'
309 and p_s_system_person_type = 'EX_APL')
310 or (p_system_person_type = 'EMP_APL'
311 and p_s_system_person_type = 'EMP')
312 or (p_system_person_type = 'EX_EMP_APL'
313 and p_s_system_person_type = 'EX_EMP') then
314 --
315 -- Ensure no future person_type_changes.
316 --
317 if hr_person.chk_future_person_type(p_s_system_person_type
318 ,p_person_id
319 ,p_business_group_id
320 ,p_effective_start_date) then
321 fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
322 app_exception.raise_exception;
323 end if;
324 --
325 -- Ensure there are no future applicant assignments
326 --
327 IF g_debug THEN
328 hr_utility.set_location(g_package || l_proc, 80);
329 END IF;
330 per_people3_pkg.check_future_apl(p_person_id => p_person_id
331 ,p_hire_date => greatest(p_hire_date
332 ,p_effective_start_date));
333 --
334 -- Insert the default applicant row and applicant
335 -- assignment.
336 --
337 -- VT 08/13/96
338 IF g_debug THEN
339 hr_utility.set_location(g_package || l_proc, 90);
340 END IF;
341 per_people9_pkg.insert_applicant_rows(p_person_id => p_person_id
342 ,p_effective_start_date => p_effective_start_date
343 ,p_effective_end_date => p_effective_end_date
344 ,p_business_group_id =>p_business_group_id
345 ,p_app_ass_status_type_id => p_app_ass_status_type_id
346 ,p_request_id => p_request_id
347 ,p_program_application_id => p_program_application_id
348 ,p_program_id => p_program_id
349 ,p_program_update_date => p_program_update_date
350 ,p_last_update_date => p_last_update_date
351 ,p_last_updated_by => p_last_updated_by
352 ,p_last_update_login => p_last_update_login
353 ,p_created_by => p_created_by
354 ,p_creation_date => p_creation_date);
355 --
356 -- Has the Person type changed to become that of an employee
357 -- when the previous type is not a current applicant?
358 --
359 elsif (p_system_person_type = 'EMP'
360 and ( p_s_system_person_type = 'OTHER'
361 or p_s_system_person_type = 'EX_EMP')) then
362 --
363 -- Ensure no future person_type_changes.
364 --
365 if hr_person.chk_future_person_type(p_s_system_person_type
366 ,p_person_id
367 ,p_business_group_id
368 ,p_effective_start_date) then
369 fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
370 app_exception.raise_exception;
371 end if;
372 --
373 if p_s_system_person_type = 'EX_EMP'
374 then
375 per_people4_pkg.check_rehire(p_person_id
376 ,p_hire_date);
377 end if;
378 IF g_debug THEN
379 hr_utility.set_location(g_package || l_proc, 100);
380 END IF;
381 per_people4_pkg.check_future_changes(p_person_id
382 ,p_effective_start_date);
383 --
384 -- Ensure there are no future applicant assignments
385 --
386 IF g_debug THEN
387 hr_utility.set_location(g_package || l_proc, 110);
388 END IF;
389 per_people3_pkg.check_future_apl(p_person_id => p_person_id
390 ,p_hire_date => greatest(p_hire_date
391 ,p_effective_start_date));
392 --
393 -- Insert the default period_of service and assignment
394 -- rows.
395 --
396 -- VT 08/13/96
397 IF g_debug THEN
398 hr_utility.set_location(g_package || l_proc, 120);
399 END IF;
400 per_people9_pkg.insert_employee_rows(p_person_id => p_person_id
401 ,p_effective_start_date => p_effective_start_date
402 ,p_effective_end_date => p_effective_end_date
403 ,p_business_group_id =>p_business_group_id
404 ,p_emp_ass_status_type_id => p_emp_ass_status_type_id
405 ,p_employee_number => p_employee_number
406 ,p_request_id => p_request_id
407 ,p_program_application_id => p_program_application_id
408 ,p_program_id => p_program_id
409 ,p_program_update_date => p_program_update_date
410 ,p_last_update_date => p_last_update_date
411 ,p_last_updated_by => p_last_updated_by
412 ,p_last_update_login => p_last_update_login
413 ,p_created_by => p_created_by
414 ,p_creation_date => p_creation_date
415 ,p_adjusted_svc_date => NULL);
416 --
417 -- Has the Person become an Employee or Employee applicant from being an
418 -- applicant or employee applicant?
419 --
420 elsif ((p_system_person_type = 'EMP'
421 and p_s_system_person_type in ('APL','APL_EX_APL','EX_EMP_APL'))
422 or (p_system_person_type = 'EMP_APL'
423 and p_s_system_person_type = 'APL')
424 or (p_system_person_type = 'EMP'
425 and p_s_system_person_type = 'EMP_APL')) then
426 --
427 -- Ensure no future person_type_changes.
428 --
429 if hr_person.chk_future_person_type(p_s_system_person_type
430 ,p_person_id
431 ,p_business_group_id
432 ,p_effective_start_date) then
433 fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
434 hr_utility.raise_error;
435 end if;
436 --
437 -- Ensure there are no future applicant assignments
438 --
439 IF g_debug THEN
440 hr_utility.set_location(g_package || l_proc, 130);
441 END IF;
442 per_people3_pkg.check_future_apl(p_person_id => p_person_id
443 ,p_hire_date => greatest(p_hire_date
444 ,p_effective_start_date));
445 --
446 -- Check if the person have open term_assignment records. These can be
447 -- found by checking if the person have a periods_of_service with
448 -- no FPD and a value for ATD. Bug 2881076
449 IF g_debug THEN
450 hr_utility.set_location(g_package || l_proc, 140);
451 END IF;
452 per_people12_pkg.check_rehire(p_person_id
453 ,p_hire_date);
454 --
455 -- Check that the change is valid.
456 --
457 if p_status = 'VACANCY_CHECK' then
458 loop
459 exit when p_status = 'BOOKINGS_EXIST';
460 --
461 -- Check each vacancy,if it is oversubscribed
462 -- l_fire_warning = 'Y', return to client
463 -- displaying relevant message.
464 -- on return l_vacancy_id starts the cursor at the
465 -- relevant point.
466 --
467 IF g_debug THEN
468 hr_utility.set_location(g_package || l_proc, 150);
469 END IF;
470 per_people3_pkg.vacancy_chk(p_person_id => p_person_id
471 ,p_fire_warning => l_fire_warning
472 ,p_vacancy_id => p_vacancy_id);
473 if l_fire_warning = 'Y' then
474 return;
475 elsif l_fire_warning = 'N' then
476 p_status := 'BOOKINGS_EXIST'; -- Set next possible re-entry point.
477 end if;
478 end loop;
479 end if; -- End of VACANCY_CHECK
480 --
481 if p_status = 'BOOKINGS_EXIST' then
482 -- VT 09/18/96 #288087, #380280, #2172590
483 if (per_people3_pkg.chk_events_exist(p_person_id =>p_person_id
484 ,p_business_group_id =>p_business_group_id
485 ,p_hire_date => greatest(p_hire_date,p_session_date))) then
486 return;
487 else
488 p_status := 'GET_APPLS'; -- Set next possible re-entry point.
489 end if;
490 end if;
491 if p_status = 'END_BOOKINGS' then
492 IF g_debug THEN
493 hr_utility.set_location(g_package || l_proc, 160);
494 END IF;
495 hrhirapl.end_bookings(p_person_id
496 , p_business_group_id
497 , p_hire_date);
498 p_status := 'GET_APPLS'; -- Set next possible re-entry point.
499 end if;
500 --
501 if p_status='GET_APPLS' then
502 --
503 -- Get all the accepted applicants
504 --
505 IF g_debug THEN
506 hr_utility.set_location(g_package || l_proc, 170);
507 END IF;
508 per_people3_pkg.get_accepted_appls(p_person_id => p_person_id
509 ,p_num_accepted_appls => l_num_accepted_appls
510 ,p_new_primary_id =>p_new_primary_id);
511 --
512 -- Get all current applicant assignments.
513 --
514 IF g_debug THEN
515 hr_utility.set_location(g_package || l_proc, 180);
516 END IF;
517 per_people3_pkg.get_all_current_appls(p_person_id => p_person_id
518 ,p_num_appls => l_num_appls);
519 --
520 if p_system_person_type = 'EMP_APL' then
521 --
522 -- If we have got this far then there must be > 0 Accepted
523 -- applications,therefore check p_system_person_type if EMP_APL
524 -- and number of accepted is equal to number of current assignments
525 -- then there is an error. Otherwise go around end_accepted
526 -- to multiple contracts.
527 --
528 if l_num_accepted_appls = l_num_appls then
529 hr_utility.set_message('801','HR_6791_EMP_APL_NO_ASG');
530 hr_utility.raise_error;
531 else
532 p_status := 'MULTIPLE_CONTRACTS';-- Set next re-entry point.
533 end if;
534 --
535 -- Number of accepted does not equal number of current then
536 -- end_accepted.
537 --
538 elsif l_num_accepted_appls <> l_num_appls then
539 hr_utility.set_message('801','HR_EMP_UNACCEPTED_APPL');
540 p_status := 'END_UNACCEPTED'; -- next code re-entry,
541 return;
542 --
543 -- Otherwise ignore end_accepted.
544 --
545 else
546 p_status := 'MULTIPLE_CONTRACTS'; -- next code re-entry.
547 end if;
548 end if; -- End of GET_APPLS
549 --
550 if p_status = 'END_UNACCEPTED' then
551 --
552 -- End the unaccepted assignments.
553 --
554 IF g_debug THEN
555 hr_utility.set_location(g_package || l_proc, 190);
556 END IF;
557 hrhirapl.end_unaccepted_app_assign(p_person_id
558 ,p_business_group_id
559 ,p_legislation_code
560 ,p_session_date);
561 p_status := 'MULTIPLE_CONTRACTS';
562 end if; -- End of END_UNACCEPTED
563 --
564 -- Test to see if multiple contracts are a possibility.
565 --
566 hr_utility.set_location('update_row - b4 MULTIPLE_CONTRACTS',1);
567 if p_status = 'MULTIPLE_CONTRACTS' then -- MULTIPLE_CONTRACTS
568 if l_num_accepted_appls >1 then
569 hr_utility.set_message('801','HR_EMP_MULTIPLE_CONTRACTS');
570 return;
571 else
572 p_status := 'CHOOSE_VAC'; -- next code re-entry.
573 end if;
574 end if; -- End of MULTIPLE_CONTRACTS
575 --
576 -- Choose whether to change the Primary assignment
577 -- and which vacancy is to be the primary if so.
578 --
579 hr_utility.set_location('update_row - b4 CHOOSE_VAC',1);
580 if p_status = 'CHOOSE_VAC' then
581 return;
582 end if; --End of CHOOSE_VAC
583 --
584 -- Can now hire the Person
585 -- Note HIRE status can only be set from client form
586 -- as interaction is generally required.
587 --
588 hr_utility.set_location('update_row - b4 HIRE',1);
589 if p_status = 'HIRE' then
590 --
591 -- If new is Emp and old was Emp_apl
592 -- then l_emp_emp_apl is set to Y
593 --
594 if p_system_person_type = 'EMP'
595 and p_s_system_person_type = 'EMP_APL' then
596 l_employ_emp_apl := 'Y';
597 else
598 l_employ_emp_apl := 'N';
599 end if;
600 --
601 -- Run the employ_applicant stored procedure
602 --
603 IF g_debug THEN
604 hr_utility.set_location(g_package || l_proc, 200);
605 END IF;
606 hrhirapl.employ_applicant(p_person_id
607 ,p_business_group_id
608 ,p_legislation_code
609 ,p_new_primary_id
610 ,p_emp_ass_status_type_id
611 ,p_last_updated_by
612 ,p_last_update_login
613 ,p_effective_start_date
614 ,p_end_of_time
615 ,p_last_update_date
616 ,p_update_primary
617 ,p_employee_number
618 ,l_set_of_books_id
619 ,l_employ_emp_apl
620 ,NULL
621 ,p_session_date); -- Bug 3564129
622 hr_utility.set_location('update_row - after hrhirapl',2);
623 end if; -- End of HIRE.
624 end if; -- Of Person type change checks.
625 --
626 IF g_debug THEN
627 hr_utility.set_location(g_package || l_proc, 210);
628 END IF;
629 update per_people_f ppf
630 set ppf.person_id = p_person_id
631 ,ppf.effective_start_date = p_effective_start_date
632 ,ppf.effective_end_date = p_effective_end_date
633 ,ppf.business_group_id = p_business_group_id
634 ,ppf.person_type_id = p_person_type_id
635 ,ppf.last_name = p_last_name
636 ,ppf.start_date = p_start_date
637 ,ppf.applicant_number = p_applicant_number
638 ,ppf.comment_id = p_comment_id
639 ,ppf.current_applicant_flag = p_current_applicant_flag
640 ,ppf.current_emp_or_apl_flag = p_current_emp_or_apl_flag
641 ,ppf.current_employee_flag = p_current_employee_flag
642 ,ppf.date_employee_data_verified = p_date_employee_data_verified
643 ,ppf.date_of_birth = p_date_of_birth
644 ,ppf.email_address = p_email_address
645 ,ppf.employee_number = p_employee_number
646 ,ppf.expense_check_send_to_address = p_expense_check_send_to_addr
647 ,ppf.first_name = p_first_name
648 ,ppf.full_name = p_full_name
649 ,ppf.known_as = p_known_as
650 ,ppf.marital_status = p_marital_status
651 ,ppf.middle_names = p_middle_names
652 ,ppf.nationality = p_nationality
653 ,ppf.national_identifier = p_national_identifier
654 ,ppf.previous_last_name = p_previous_last_name
655 ,ppf.registered_disabled_flag = p_registered_disabled_flag
656 ,ppf.sex = p_sex
657 ,ppf.title = p_title
658 ,ppf.suffix = p_suffix
659 ,ppf.vendor_id = p_vendor_id
660 -- ,ppf.work_telephone = p_work_telephone
661 ,ppf.request_id = p_request_id
662 ,ppf.program_application_id = p_program_application_id
663 ,ppf.program_id = p_program_id
664 ,ppf.program_update_date = p_program_update_date
665 ,ppf.attribute_category = p_a_cat
666 ,ppf.attribute1 = p_a1
667 ,ppf.attribute2 = p_a2
668 ,ppf.attribute3 = p_a3
669 ,ppf.attribute4 = p_a4
670 ,ppf.attribute5 = p_a5
671 ,ppf.attribute6 = p_a6
672 ,ppf.attribute7 = p_a7
673 ,ppf.attribute8 = p_a8
674 ,ppf.attribute9 = p_a9
675 ,ppf.attribute10 = p_a10
676 ,ppf.attribute11 = p_a11
677 ,ppf.attribute12 = p_a12
678 ,ppf.attribute13 = p_a13
679 ,ppf.attribute14 = p_a14
680 ,ppf.attribute15 = p_a15
681 ,ppf.attribute16 = p_a16
682 ,ppf.attribute17 = p_a17
683 ,ppf.attribute18 = p_a18
684 ,ppf.attribute19 = p_a19
685 ,ppf.attribute20 = p_a20
686 ,ppf.attribute21 = p_a21
687 ,ppf.attribute22 = p_a22
688 ,ppf.attribute23 = p_a23
689 ,ppf.attribute24 = p_a24
690 ,ppf.attribute25 = p_a25
691 ,ppf.attribute26 = p_a26
692 ,ppf.attribute27 = p_a27
693 ,ppf.attribute28 = p_a28
694 ,ppf.attribute29 = p_a29
695 ,ppf.attribute30 = p_a30
696 ,ppf.last_update_date = p_last_update_date
697 ,ppf.last_updated_by = p_last_updated_by
698 ,ppf.last_update_login = p_last_update_login
699 ,ppf.created_by = p_created_by
700 ,ppf.creation_date = p_creation_date
701 ,ppf.per_information_category = p_i_cat
702 ,ppf.per_information1 = p_i1
703 ,ppf.per_information2 = p_i2
704 ,ppf.per_information3 = p_i3
705 ,ppf.per_information4 = p_i4
706 ,ppf.per_information5 = p_i5
707 ,ppf.per_information6 = p_i6
708 ,ppf.per_information7 = p_i7
709 ,ppf.per_information8 = p_i8
710 ,ppf.per_information9 = p_i9
711 ,ppf.per_information10 = p_i10
712 ,ppf.per_information11 = p_i11
713 ,ppf.per_information12 = p_i12
714 ,ppf.per_information13 = p_i13
715 ,ppf.per_information14 = p_i14
716 ,ppf.per_information15 = p_i15
717 ,ppf.per_information16 = p_i16
718 ,ppf.per_information17 = p_i17
719 ,ppf.per_information18 = p_i18
720 ,ppf.per_information19 = p_i19
721 ,ppf.per_information20 = p_i20
722 ,ppf.per_information21 = p_i21
723 ,ppf.per_information22 = p_i22
724 ,ppf.per_information23 = p_i23
725 ,ppf.per_information24 = p_i24
726 ,ppf.per_information25 = p_i25
727 ,ppf.per_information26 = p_i26
728 ,ppf.per_information27 = p_i27
729 ,ppf.per_information28 = p_i28
730 ,ppf.per_information29 = p_i29
731 ,ppf.per_information30 = p_i30
732 where ppf.rowid = p_rowid;
733 --
734 if sql%rowcount <1 then
735 hr_utility.set_message(801,'HR_6001_ALL_MANDATORY_FIELD');
736 hr_utility.set_message_token('MISSING_FIELD','rowid is'||p_rowid);
737 hr_utility.raise_error;
738 end if;
739 --
740 -- Tests required post-update
741 --
742 hr_utility.set_location('update_row - after update',1);
743 --
744 -- Has the Date of Birth changed?
745 --
746 if p_date_of_birth is null and
747 p_s_date_of_birth is not null then
748 per_people4_pkg.check_birth_date(p_person_id);
749 end if;
750 if p_date_of_birth <> p_s_date_of_birth then
751 --
752 -- Run the assignment_link_usages and Element_entry
753 -- code for Change of Personal qualifying conditions.
754 --
755 --
756 IF g_debug THEN
757 hr_utility.set_location(g_package || l_proc, 220);
758 END IF;
759 per_people3_pkg.run_alu_ee(p_alu_mode => 'CHANGE_PQC'
760 ,p_business_group_id=>p_business_group_id
761 ,p_person_id =>p_person_id
762 ,p_old_start =>p_s_hire_date
763 ,p_start_date => p_last_update_date
764 );
765 end if;
766 --
767 hr_utility.set_location('update_row - after update',2);
768 --
769 -- test if hire_date has changed. and system person type has not.
770 --
771 if ((p_current_employee_flag = 'Y')
772 and (p_hire_date <> p_s_hire_date)
773 and (p_system_person_type = p_s_system_person_type)) then
774 --
775 -- Update the period of service for the employee
776 --
777 --
778 IF g_debug THEN
779 hr_utility.set_location(g_package || l_proc, 230);
780 END IF;
781 per_people3_pkg.update_period(p_person_id =>p_person_id
782 ,p_hire_date => p_s_hire_date
783 ,p_new_hire_date =>p_hire_date);
784 --
785 hr_utility.set_location('update_row - after update',3);
786 --
787 -- Update the hire records i.e
788 -- assignment etc.
789 --
790 --
791 IF g_debug THEN
792 hr_utility.set_location(g_package || l_proc, 240);
793 END IF;
794 hr_date_chk.update_hire_records(p_person_id
795 ,p_applicant_number
796 ,p_hire_date
797 ,p_s_hire_date
798 ,p_last_updated_by
799 ,p_last_update_login);
800 --
801 IF g_debug THEN
802 hr_utility.set_location(g_package || l_proc, 250);
803 END IF;
804 open get_pay_proposal;
805 fetch get_pay_proposal into v_dummy;
806 if get_pay_proposal%FOUND
807 then
808 close get_pay_proposal;
809 begin
810 IF g_debug THEN
811 hr_utility.set_location(g_package || l_proc, 260);
812 END IF;
813
814 update per_pay_proposals
815 set change_date = p_hire_date
816 where change_date = p_s_hire_date
817 and assignment_id = (select assignment_id
818 from per_assignments_f
819 where person_id = p_person_id
820 and primary_flag = 'Y'
821 and effective_start_date = p_hire_date
822 and assignment_type = 'E'
823 );
824 --
825 if sql%ROWCOUNT <> 1
826 then
827 raise NO_DATA_FOUND;
828 end if;
829 exception
830 when NO_DATA_FOUND then
831 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
832 hr_utility.set_message_token('PROCEDURE','Update_row');
833 hr_utility.set_message_token('STEP','4');
834 hr_utility.raise_error;
835 end;
836 else
837 close get_pay_proposal;
838 end if;
839 hr_utility.set_location('update_row - after update',5);
840 --
841 -- Run the assignment_link_usages and Element_entry
842 -- code for Assignment Criteria.
843 --
844 IF g_debug THEN
845 hr_utility.set_location(g_package || l_proc, 270);
846 END IF;
847 per_people3_pkg.run_alu_ee(p_alu_mode => 'ASG_CRITERIA'
848 ,p_business_group_id=>p_business_group_id
849 ,p_person_id =>p_person_id
850 ,p_old_start =>p_s_hire_date
851 ,p_start_date => p_hire_date);
852 --
853 --
854 end if;
855 --
856 p_status := 'END'; -- Status required to end update loop on server
857 --
858 IF g_debug THEN
859 hr_utility.set_location('Leaving: '|| g_package || l_proc, 280);
860 END IF;
861 end update_row;
862 procedure check_future_changes(p_person_id NUMBER
863 ,p_effective_start_date DATE)
864 is
865 --
866 l_dummy VARCHAR2(1);
867 --
868 cursor future_exists
869 is
870 select '1'
871 from sys.dual
872 where exists (
873 select 'future assignment exists'
874 from per_people_f ppf
875 where ppf.person_id = p_person_id
876 and ppf.effective_start_date > p_effective_start_date
877 );
878 --
879 l_proc varchar2(20) := 'check_future_changes';
880 --
881 begin
882 g_debug := hr_utility.debug_enabled; -- get debug status
883 IF g_debug THEN
884 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
885 END IF;
886 open future_exists;
887 fetch future_exists into l_dummy;
888 if future_exists%found then
889 fnd_message.set_name('PAY','HR_7510_PER_FUT_CHANGE');
890 app_exception.raise_exception;
891 end if;
892 close future_exists;
893 IF g_debug THEN
894 hr_utility.set_location('Leaving: '|| g_package || l_proc, 10);
895 END IF;
896 end;
897 --
898 procedure check_not_supervisor(p_person_id NUMBER
899 ,p_new_hire_date DATE
900 ,p_old_hire_date DATE)
901 is
902 l_dummy VARCHAR2(1);
903 --
904 cursor supervisor
905 is
906 select 'Y'
907 from per_assignments_f paf
908 where paf.assignment_type = 'E'
909 and paf.supervisor_id = p_person_id
910 and p_new_hire_date > paf.effective_start_date
911 and paf.effective_end_date >= p_old_hire_date;
912 --
913 l_proc varchar2(20) := 'check_not_supervisor';
914 --
915 begin
916 g_debug := hr_utility.debug_enabled; -- get debug status
917 IF g_debug THEN
918 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
919 END IF;
920 open supervisor;
921 fetch supervisor into l_dummy;
922 if supervisor%FOUND then
923 close supervisor;
924 fnd_message.set_name('PAY','HR_51031_INV_HIRE_CHG_IS_SUPER');
925 app_exception.raise_exception;
926 end if;
927 close supervisor;
928 IF g_debug THEN
929 hr_utility.set_location('Leaving: '|| g_package || l_proc, 10);
930 END IF;
931 end;
932 --
933 procedure check_rehire(p_person_id NUMBER
934 ,p_start_date DATE)
935 IS
936 cursor old_pps_exists
937 is
938 select 1
939 from per_periods_of_service pps
940 where pps.person_id = p_person_id
941 and pps.actual_termination_date is not null;
942 --
943 -- 70.11 nvl(pps.final_process_date,p_start_date)+1 < p_start_date;
944 --
945 cursor pps_not_ended
946 is
947 select pps.final_process_date
948 from per_periods_of_service pps
949 where pps.person_id = p_person_id
950 and pps.date_start = (select max(date_start)
951 from per_periods_of_service pps1
952 where pps1.person_id = pps.person_id
953 and pps1.date_start <p_start_date
954 )
955 and nvl(pps.final_process_date,p_start_date) >= p_start_date;
956 --
957 v_dummy INTEGER;
958 v_dummy_fpd date;
959 --
960 l_proc varchar2(12) := 'check_rehire';
961 --
962 begin
963 g_debug := hr_utility.debug_enabled; -- get debug status
964 IF g_debug THEN
965 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
966 END IF;
967 --
968 -- Check if old PPS row exists
969 --
970 open old_pps_exists;
971 fetch old_pps_exists into v_dummy;
972 if old_pps_exists%FOUND
973 then
974 close old_pps_exists;
975 --
976 -- if yes then check last PPS
977 -- has had it's FPD closed down and that the FPD + 1
978 -- is less than current hire date
979 -- if not error;
980 --
981 IF g_debug THEN
982 hr_utility.set_location(g_package || l_proc, 10);
983 END IF;
984 open pps_not_ended;
985 fetch pps_not_ended into v_dummy_fpd;
986 if pps_not_ended%FOUND then
987 close pps_not_ended;
988 if v_dummy_fpd is null then
989 hr_utility.set_message('800','HR_51032_EMP_PREV_FPD_OPEN');
990 else
991 hr_utility.set_message('800','PER_289308_FUTURE_ENDED_FPD');
992 end if;
993 hr_utility.raise_error;
994 end if;
995 close pps_not_ended;
996 else
997 close old_pps_exists;
998 end if;
999 IF g_debug THEN
1000 hr_utility.set_location('Leaving.' || g_package || l_proc, 20);
1001 END IF;
1002 end;
1003 --
1004 procedure check_birth_date(p_person_id NUMBER)
1005 is
1006 v_dummy NUMBER;
1007 --
1008 -- Cursor to check if any employee assignments have
1009 -- Payroll id set.
1010 --
1011 cursor get_payroll
1012 is
1013 select asg.assignment_id
1014 from per_assignments_f asg
1015 where asg.person_id = p_person_id
1016 and asg.payroll_id is not null;
1017 begin
1018 open get_payroll;
1019 fetch get_payroll into v_dummy;
1020 --
1021 -- If a row exists, flag an error to stop
1022 -- Date of birth being nulled when emp on payroll.
1023 --
1024 if get_payroll%FOUND
1025 then
1026 close get_payroll;
1027 hr_utility.set_message('801','HR_7950_PPM_NULL_DOB');
1028 hr_utility.raise_error;
1029 else
1030 close get_payroll;
1031 end if;
1032 end;
1033 --
1034
1035 procedure original_date_of_hire (p_person_id IN NUMBER
1036 ,p_original_date_of_hire IN DATE
1037 ,p_hire_date IN DATE
1038 ,p_business_group_id IN NUMBER
1039 ,p_person_type_id IN NUMBER
1040 ,p_period_of_service_id IN NUMBER
1041 ,p_system_person_type IN VARCHAR2
1042 ,p_orig_hire_warning IN OUT NOCOPY BOOLEAN
1043 )
1044 is
1045 --
1046 l_earliest_date date;
1047 l_end_date date; --Added for fix of #3632547
1048 l_session_date date; --Added for fix of #3632547
1049 l_period_of_service_id number;
1050
1051 -- Start changes for bug 11062773
1052 cursor csr_is_first_pps is
1053 select period_of_service_id
1054 from per_periods_of_service pps
1055 where period_of_service_id = p_period_of_service_id
1056 and 1 = (
1057 select count(1)
1058 from per_periods_of_service
1059 where person_id = p_person_id
1060 );
1061 -- End changes for bug 11062773
1062
1063 -- Start changes for bug 12669959
1064 cursor csr_person_type is
1065 select system_person_type
1066 from per_person_types
1067 where person_type_id = p_person_type_id;
1068
1069 l_system_person_type per_person_types.system_person_type%type;
1070 -- End changes for bug 12669959
1071
1072 --
1073 cursor csr_earliest_date is
1074 select min(date_start)
1075 from per_periods_of_service
1076 where p_person_id = person_id;
1077 --
1078 l_proc varchar2(21) := 'original_date_of_hire';
1079 --
1080 begin
1081 g_debug := hr_utility.debug_enabled; -- get debug status
1082
1083 IF g_debug THEN
1084 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
1085 hr_utility.Set_location('p_original_date_of_hire: '|| p_original_date_of_hire, 5);
1086 hr_utility.Set_location('p_hire_date: '|| p_hire_date, 5);
1087 hr_utility.Set_location('p_business_group_id: '|| p_business_group_id, 5);
1088 hr_utility.Set_location('p_person_type_id: '|| p_person_type_id, 5);
1089 hr_utility.Set_location('p_system_person_type: '|| p_system_person_type, 5);
1090 hr_utility.Set_location('p_period_of_service_id: '|| p_period_of_service_id, 5);
1091 END IF;
1092 --
1093 p_orig_hire_warning := FALSE;
1094 --
1095
1096 -- Start changes for bug 11062773
1097 --
1098 IF p_original_date_of_hire IS NOT NULL THEN
1099 --
1100 IF p_system_person_type IN ( 'EMP', 'EMP_APL', 'EX_EMP', 'EX_EMP_APL' )
1101 THEN
1102 ---
1103 OPEN csr_is_first_pps;
1104 FETCH csr_is_first_pps INTO l_period_of_service_id;
1105 CLOSE csr_is_first_pps;
1106
1107 -- Start changes for bug 12669959
1108 OPEN csr_person_type;
1109 FETCH csr_person_type INTO l_system_person_type;
1110 CLOSE csr_person_type;
1111 -- End changes for bug 12669959
1112
1113 IF g_debug THEN
1114 hr_utility.Set_location('l_period_of_service_id'||l_period_of_service_id, 10);
1115 END IF;
1116
1117 IF p_hire_date IS NOT NULL THEN
1118 IF l_period_of_service_id IS NOT NULL
1119 and l_system_person_type in('EMP','EMP_APL') -- added for bug 12669959, 14140116
1120 THEN
1121 ----
1122 IF p_original_date_of_hire > p_hire_date THEN
1123
1124 IF g_debug THEN
1125 hr_utility.Set_location('PER_52474_PER_ORIG_ST_DATE ', 20);
1126 END IF;
1127
1128 p_orig_hire_warning := true;
1129
1130 hr_utility.Set_message(800, 'PER_52474_PER_ORIG_ST_DATE');
1131 hr_utility.raise_error;
1132
1133 END IF;
1134 ----
1135 ELSE
1136
1137 OPEN csr_earliest_date;
1138 FETCH csr_earliest_date INTO l_earliest_date;
1139 CLOSE csr_earliest_date;
1140
1141 -----
1142 IF p_original_date_of_hire > l_earliest_date THEN
1143
1144 IF g_debug THEN
1145 hr_utility.Set_location('PER_52474_PER_ORIG_ST_DATE ', 30);
1146 END IF;
1147
1148 p_orig_hire_warning := true;
1149
1150 hr_utility.Set_message(800, 'PER_52474_PER_ORIG_ST_DATE');
1151 hr_utility.raise_error;
1152
1153 END IF;
1154 -----
1155 END IF;
1156 ELSE
1157 OPEN csr_earliest_date;
1158 FETCH csr_earliest_date INTO l_earliest_date;
1159 CLOSE csr_earliest_date;
1160
1161 -----
1162 IF p_original_date_of_hire > l_earliest_date THEN
1163
1164 IF g_debug THEN
1165 hr_utility.Set_location('PER_52474_PER_ORIG_ST_DATE ', 40);
1166 END IF;
1167
1168 p_orig_hire_warning := true;
1169
1170 hr_utility.Set_message(800, 'PER_52474_PER_ORIG_ST_DATE');
1171 hr_utility.raise_error;
1172
1173 END IF;
1174 -----
1175 END IF;
1176 ----
1177 END IF;
1178 ---
1179 END IF;
1180 --
1181
1182 /* if p_original_date_of_hire is NOT NULL then
1183 --
1184 if p_system_person_type in ('EMP','EMP_APL','EX_EMP','EX_EMP_APL') then
1185 --
1186 if p_hire_date is not null and p_original_date_of_hire > p_hire_date then
1187 hr_utility.set_message(800,'PER_52474_PER_ORIG_ST_DATE');
1188 hr_utility.raise_error;
1189 --
1190 elsif p_person_id is not null then
1191
1192 open csr_earliest_date;
1193 fetch csr_earliest_date into l_earliest_date;
1194
1195
1196 if p_original_date_of_hire > l_earliest_date then
1197 hr_utility.set_message(800,'PER_52474_PER_ORIG_ST_DATE');
1198 hr_utility.raise_error;
1199 end if;
1200 close csr_earliest_date;
1201 end if;
1202 --
1203 elsif p_system_person_type not in ('EMP','EMP_APL','EX_EMP','EX_EMP_APL')
1204 then p_orig_hire_warning := TRUE;
1205 end if;
1206 end if;*/
1207
1208 -- End changes for bug 11062773
1209
1210 IF g_debug THEN
1211 hr_utility.set_location('Leaving: '|| g_package || l_proc, 10);
1212 END IF;
1213 end;
1214 --
1215
1216 END PER_PEOPLE4_PKG;
[Home] [Help]