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