[Home] [Help]
PACKAGE BODY: APPS.PER_PEOPLE9_PKG
Source
1 PACKAGE BODY PER_PEOPLE9_PKG AS
2 /* $Header: peper09t.pkb 120.1 2006/03/03 05:49:29 lsilveir noship $ */
3 --
4 procedure insert_applicant_rows(p_person_id NUMBER
5 ,p_effective_start_date DATE
6 ,p_effective_end_date DATE
7 ,p_business_group_id NUMBER
8 ,p_app_ass_status_type_id NUMBER
9 ,p_request_id NUMBER
10 ,p_program_application_id NUMBER
11 ,p_program_id NUMBER
12 ,p_program_update_date DATE
13 ,p_last_update_date DATE
14 ,p_last_updated_by NUMBER
15 ,p_last_update_login NUMBER
16 ,p_created_by NUMBER
17 ,p_creation_date DATE) is
18 --
19 -- Inserts default applicant assignment
20 --
21 --
22 -- local variables
23 --
24 l_row_id VARCHAR2(30); -- Dummy strorage for ROWID.
25 l_location_id NUMBER(15); -- Location_id of Business_group
26 l_time_normal_start VARCHAR2(30);-- BG's start time from Work day defaults.
27 l_time_normal_end VARCHAR2(30); -- BG's end time from Work day defaults.
28 l_normal_hours number; -- BG's normal hours from Work day defaults.
29 l_frequency VARCHAR2(30); -- BG's frequency from Work day defaults.
30 l_application_id NUMBER; -- Dummy return for application id.
31 l_assignment_id NUMBER; -- Dummy return for assignment_id.
32 l_assignment_sequence NUMBER; -- Dummy return for assignment_sequence.
33 --
34 begin
35 hr_utility.set_location('per_people9_pkg.insert_applicant_rows',1);
36 per_applications_pkg.insert_Row(
37 p_Rowid =>l_row_id,
38 p_Application_Id =>l_application_id,
39 p_Business_Group_Id =>p_business_group_id,
40 p_Person_Id =>p_person_id,
41 p_Date_Received => p_effective_start_date,
42 p_Comments =>NULL,
43 p_Current_Employer =>NULL,
44 p_Date_End =>NULL,
45 p_Projected_Hire_Date =>NULL,
46 p_Successful_Flag =>NULL,
47 p_Termination_Reason =>NULL,
48 p_Appl_Attribute_Category =>NULL,
49 p_Appl_Attribute1 =>NULL,
50 p_Appl_Attribute2 =>NULL,
51 p_Appl_Attribute3 =>NULL,
52 p_Appl_Attribute4 =>NULL,
53 p_Appl_Attribute5 =>NULL,
54 p_Appl_Attribute6 =>NULL,
55 p_Appl_Attribute7 =>NULL,
56 p_Appl_Attribute8 =>NULL,
57 p_Appl_Attribute9 =>NULL,
58 p_Appl_Attribute10 =>NULL,
59 p_Appl_Attribute11 =>NULL,
60 p_Appl_Attribute12 =>NULL,
61 p_Appl_Attribute13 =>NULL,
62 p_Appl_Attribute14 =>NULL,
63 p_Appl_Attribute15 =>NULL,
64 p_Appl_Attribute16 =>NULL,
65 p_Appl_Attribute17 =>NULL,
66 p_Appl_Attribute18 =>NULL,
67 p_Appl_Attribute19 =>NULL,
68 p_Appl_Attribute20 =>NULL,
69 p_Last_Update_Date =>NULL,
70 p_Last_Updated_By =>NULL,
71 p_Last_Update_Login =>NULL,
72 p_Created_By =>NULL,
73 p_Creation_Date =>NULL);
74 -- Insert the applicant assignment.
75 --
76 hr_utility.set_location('per_people9_pkg.insert_applicant_assignment',2);
77 hr_assignment.gen_new_ass_sequence(p_person_id
78 ,'A'
79 ,l_assignment_sequence);
80 begin
81 --
82 -- get the default location and times.
83 --
84 select pbg.location_id
85 , pbg.default_start_time
86 , pbg.default_end_time
87 , fnd_number.canonical_to_number(pbg.working_hours)
88 , pbg.frequency
89 into l_location_id
90 , l_time_normal_start
91 , l_time_normal_end
92 , l_normal_hours
93 , l_frequency
94 from per_business_groups pbg
95 where pbg.business_group_id = p_business_group_id;
96 --
97 exception
98 when no_data_found then
99 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
100 hr_utility.set_message_token('PROCEDURE','Insert Applicant Rows');
101 hr_utility.set_message_token('STEP','1');
102 hr_utility.raise_error;
103 hr_utility.set_location('per_people9_pkg.insert_applicant_assignment',3);
104 end;
105 --
106 -- Insert the Applicant assignment
107 --
108 --
109 -- 340022 Made p_source_organiation_id = NULL
110 -- Rather than p_business_group_id
111 -- 311758 Assignment_number now nulled rather than set to 1.
112 -- TM 08-mar-1996.
113 per_assignments_f_pkg.insert_row(
114 p_row_id =>l_row_id
115 ,p_assignment_id => l_assignment_id
116 ,p_effective_start_date => p_effective_start_date
117 ,p_effective_end_date => p_effective_end_date
118 ,p_business_group_id => p_business_group_id
119 ,p_recruiter_id =>NULL
120 ,p_grade_id =>NULL
121 ,p_position_id =>NULL
122 ,p_job_id =>NULL
123 ,p_assignment_status_type_id => p_app_ass_status_type_id
124 ,p_payroll_id =>NULL
125 ,p_location_id =>l_location_id
126 ,p_person_referred_by_id => NULL
127 ,p_supervisor_id => NULL
128 ,p_special_ceiling_step_id => NULL
129 ,p_person_id => p_person_id
130 ,p_recruitment_activity_id => NULL
131 ,p_source_organization_id => NULL
132 ,p_organization_id => p_business_group_id
133 ,p_people_group_id => NULL
134 ,p_soft_coding_keyflex_id => NULL
135 ,p_vacancy_id => NULL
136 ,p_assignment_sequence => l_assignment_sequence
137 ,p_assignment_type =>'A'
138 ,p_primary_flag => 'N'
139 ,p_application_id => l_application_id
140 ,p_assignment_number => NULL
141 ,p_change_reason => NULL
142 ,p_comment_id => NULL
143 ,p_date_probation_end => NULL
144 ,p_default_code_comb_id => NULL
145 ,p_frequency => l_frequency
146 ,p_internal_address_line => NULL
147 ,p_manager_flag => 'N'
148 ,p_normal_hours => l_normal_hours
149 ,p_period_of_service_id => NULL
150 ,p_probation_period => NULL
151 ,p_probation_unit => NULL
152 ,p_set_of_books_id => NULL
153 ,p_source_type => NULL
154 ,p_time_normal_finish => l_time_normal_end
155 ,p_time_normal_start => l_time_normal_start
156 ,p_request_id => p_request_id
157 ,p_program_application_id => p_program_application_id
158 ,p_program_id => p_program_id
159 ,p_program_update_date => p_program_update_date
160 ,p_ass_attribute_category => NULL
161 ,p_ass_attribute1 => NULL
162 ,p_ass_attribute2 => NULL
163 ,p_ass_attribute3 => NULL
164 ,p_ass_attribute4 => NULL
165 ,p_ass_attribute5 => NULL
166 ,p_ass_attribute6 => NULL
167 ,p_ass_attribute7 => NULL
168 ,p_ass_attribute8 => NULL
169 ,p_ass_attribute9 => NULL
170 ,p_ass_attribute10 => NULL
171 ,p_ass_attribute11 => NULL
172 ,p_ass_attribute12 => NULL
173 ,p_ass_attribute13 => NULL
174 ,p_ass_attribute14 => NULL
175 ,p_ass_attribute15 => NULL
176 ,p_ass_attribute16 => NULL
177 ,p_ass_attribute17 => NULL
178 ,p_ass_attribute18 => NULL
179 ,p_ass_attribute19 => NULL
180 ,p_ass_attribute20 => NULL
181 ,p_ass_attribute21 => NULL
182 ,p_ass_attribute22 => NULL
183 ,p_ass_attribute23 => NULL
184 ,p_ass_attribute24 => NULL
185 ,p_ass_attribute25 => NULL
186 ,p_ass_attribute26 => NULL
187 ,p_ass_attribute27 => NULL
188 ,p_ass_attribute28 => NULL
189 ,p_ass_attribute29 => NULL
190 ,p_ass_attribute30 => NULL
191 ,p_sal_review_period => NULL
192 ,p_sal_review_period_frequency => NULL
193 ,p_perf_review_period => NULL
194 ,p_perf_review_period_frequency => NULL
195 ,p_pay_basis_id => NULL
196 ,p_employment_category => NULL
197 ,p_bargaining_unit_code => NULL
198 ,p_labour_union_member_flag => NULL
199 ,p_hourly_salaried_code => NULL
200 );
201
202 --
203 -- BUg 346814 changed the order of insert letter
204 -- and insert_assignment calls.
205 -- TM 05-03-96
206 --
207 --
208 -- if application assignment insert ok
209 --
210 -- Fix for bug 3612059 starts here.
211 -- Use check_for_letter_requests procedure call instead of the following call.
212 --
213 /*
214 per_applications_pkg.insert_letter_term(p_business_group_id =>p_business_group_id
215 ,p_application_id => l_application_id
216 ,p_person_id =>p_person_id
217 ,p_session_date =>p_effective_start_date
218 ,p_Last_Updated_By => NULL
219 ,p_Last_Update_Login => NULL
220 ,p_assignment_status_type_id =>
221 p_app_ass_status_type_id);
222 */
223 --
224 per_applicant_pkg.check_for_letter_requests (
225 p_business_group_id => p_business_group_id,
226 p_per_system_status => NULL,
227 p_assignment_status_type_id => p_app_ass_status_type_id,
228 p_person_id => p_person_id,
229 p_assignment_id => l_assignment_id,
230 p_effective_start_date => p_effective_start_date,
231 p_validation_start_date => p_effective_start_date,
232 p_vacancy_id => NULL ) ;
233 --
234 -- Fix for bug 3612059 ends here.
235 --
236 -- load the default budget values
237 --
238 hr_assignment.load_budget_values(l_assignment_id
239 ,p_business_group_id
240 ,p_last_updated_by
241 ,p_last_update_login
242 ,p_effective_start_date
243 ,p_effective_end_date);
244 --
245 end;
246 --
247 procedure insert_employee_rows(p_person_id NUMBER
248 ,p_effective_start_date DATE
249 ,p_effective_end_date DATE
250 ,p_business_group_id NUMBER
251 ,p_emp_ass_status_type_id NUMBER
252 ,p_employee_number VARCHAR2
253 ,p_request_id NUMBER
254 ,p_program_application_id NUMBER
255 ,p_program_id NUMBER
256 ,p_program_update_date DATE
257 ,p_last_update_date DATE
258 ,p_last_updated_by NUMBER
259 ,p_last_update_login NUMBER
260 ,p_created_by NUMBER
261 ,p_creation_date DATE
262 ,p_adjusted_svc_date DATE) is
263 --
264 -- Inserts default employee assignment
265 --
266 --
267 -- local variables
268 --
269 l_row_id VARCHAR2(30); -- Dummy strorage for ROWID.
270 l_location_id NUMBER(15); -- Location_id of Business_group
271 l_time_normal_start VARCHAR2(30);-- BG's start time from Work day defaults.
272 l_time_normal_end VARCHAR2(30); -- BG's end time from Work day defaults.
273 l_normal_hours NUMBER; -- BG's normal hours from Work day defaults.
274 l_frequency VARCHAR2(30); -- BG's frequency from Work day defaults.
275 l_period_of_service_id NUMBER; -- Dummy return for period_of_service_id.
276 l_assignment_id NUMBER; -- Dummy return for assignment_id.
277 l_assignment_id_temp NUMBER; -- Dummy return for assignment_id.
278 l_assignment_sequence NUMBER; -- Dummy return for assignment_sequence.
279 l_assignment_number VARCHAR2(30);-- Dummy return for assignment_number.
280 l_primary_flag VARCHAR2(1); -- Dummy return for primary_flag
281 l_warning VARCHAR2(1); -- Dummy return for warning;
282 --
283 --start WWBUG 2130950 hrwf synchronization --tpapired
284 l_asg_rec per_all_assignments_f%rowtype;
285 cursor l_asg_cur is
286 select *
287 from per_all_assignments_f
288 where assignment_id = L_ASSIGNMENT_ID
289 and effective_start_date = P_EFFECTIVE_START_DATE
290 and effective_end_date = P_EFFECTIVE_END_DATE;
291 --End WWBUG 2130950 for testing hrwf synchronization -tpapired
292 --
293 begin
294 begin
295 --
296 -- get the default location and times.
297 --
298 hr_utility.set_location('per_people9_pkg.insert_employee_rows',1);
299 select pbg.location_id
300 , pbg.default_start_time
301 , pbg.default_end_time
302 , fnd_number.canonical_to_number(pbg.working_hours)
303 , pbg.frequency
304 into l_location_id
305 , l_time_normal_start
306 , l_time_normal_end
307 , l_normal_hours
308 , l_frequency
309 from per_business_groups pbg
310 where pbg.business_group_id = p_business_group_id;
311 --
312 exception
313 when no_data_found then
314 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
315 hr_utility.set_message_token('PROCEDURE','Insert Employee rows');
316 hr_utility.set_message_token('STEP','1');
317 hr_utility.raise_error;
318 end;
319 --
320 -- Insert Period of service.
321 --
322 hr_utility.set_location('per_people9_pkg.insert_employee_rows',2);
323 per_periods_of_service_pkg.insert_row(l_row_id
324 ,p_period_of_service_id => l_period_of_service_id
325 ,p_business_group_id => p_business_group_id
326 ,p_person_id => p_person_id
327 ,p_date_start => p_effective_start_date
328 ,p_termination_accepted_per_id =>NULL
329 ,p_accepted_termination_date =>NULL
330 ,p_actual_termination_date =>NULL
331 ,p_comments =>NULL
332 ,p_final_process_date =>NULL
333 ,p_last_standard_process_date =>NULL
334 ,p_leaving_reason =>NULL
335 ,p_notified_termination_date =>NULL
336 ,p_projected_termination_date =>NULL
337 ,p_request_id => p_request_id
338 ,p_program_application_id => p_program_application_id
339 ,p_program_id => p_program_id
340 ,p_program_update_date => p_program_update_date
341 ,p_attribute_category =>NULL
342 ,p_attribute1 =>NULL
343 ,p_attribute2 =>NULL
344 ,p_attribute3 =>NULL
345 ,p_attribute4 =>NULL
346 ,p_attribute5 =>NULL
347 ,p_attribute6 =>NULL
348 ,p_attribute7 =>NULL
349 ,p_attribute8 =>NULL
350 ,p_attribute9 =>NULL
351 ,p_attribute10 =>NULL
352 ,p_attribute11 =>NULL
353 ,p_attribute12 =>NULL
354 ,p_attribute13 =>NULL
355 ,p_attribute14 =>NULL
356 ,p_attribute15 =>NULL
357 ,p_attribute16 =>NULL
358 ,p_attribute17 =>NULL
359 ,p_attribute18 =>NULL
360 ,p_attribute19 =>NULL
361 ,p_attribute20 =>NULL
362 ,p_adjusted_svc_date =>p_adjusted_svc_date);
363 --
364 -- If period of service entered ok
365 --
366 -- Then enter employee assignment
367 --
368 hr_utility.set_location('per_people9_pkg.insert_employee_rows',3);
369 hr_assignment.gen_new_ass_sequence(p_person_id
370 ,'E'
371 ,l_assignment_sequence);
372 --
373 hr_utility.set_location('per_people9_pkg.insert_employee_rows',4);
374 hr_assignment.gen_new_ass_number(''
375 ,p_business_group_id
376 ,p_employee_number
377 ,l_assignment_sequence
378 ,l_assignment_number);
379 --
380 -- 340021 Made p_source_organiation_id = NULL
381 -- Rather than p_business_group_id
382 -- TM 08-mar-1996.
383 --
384 hr_utility.set_location('per_people9_pkg.insert_employee_rows',5);
385 per_assignments_f_pkg.insert_row(p_row_id =>l_row_id
386 ,p_assignment_id => l_assignment_id
387 ,p_effective_start_date => p_effective_start_date
388 ,p_effective_end_date => p_effective_end_date
389 ,p_business_group_id => p_business_group_id
390 ,p_recruiter_id =>NULL
391 ,p_grade_id =>NULL
392 ,p_position_id =>NULL
393 ,p_job_id =>NULL
394 ,p_assignment_status_type_id => p_emp_ass_status_type_id
395 ,p_payroll_id =>NULL
396 ,p_location_id =>l_location_id
397 ,p_person_referred_by_id => NULL
398 ,p_supervisor_id => NULL
399 ,p_special_ceiling_step_id => NULL
400 ,p_person_id => p_person_id
401 ,p_recruitment_activity_id => NULL
402 ,p_source_organization_id => NULL
403 ,p_organization_id => p_business_group_id
404 ,p_people_group_id => NULL
405 ,p_soft_coding_keyflex_id => NULL
406 ,p_vacancy_id => NULL
407 ,p_assignment_sequence => l_assignment_sequence
408 ,p_assignment_type =>'E'
409 ,p_primary_flag =>'Y'
410 ,p_application_id => NULL
411 ,p_assignment_number => l_assignment_number
412 ,p_change_reason => NULL
413 ,p_comment_id => NULL
414 ,p_date_probation_end => NULL
415 ,p_default_code_comb_id => NULL
416 ,p_frequency => l_frequency
417 ,p_internal_address_line => NULL
418 ,p_manager_flag => 'N'
419 ,p_normal_hours => l_normal_hours
420 ,p_period_of_service_id => l_period_of_service_id
421 ,p_probation_period => NULL
422 ,p_probation_unit => NULL
423 ,p_set_of_books_id => NULL
424 ,p_source_type => NULL
425 ,p_time_normal_finish => l_time_normal_end
426 ,p_time_normal_start => l_time_normal_start
427 ,p_request_id => p_request_id
428 ,p_program_application_id => p_program_application_id
429 ,p_program_id => p_program_id
430 ,p_program_update_date => p_program_update_date
431 ,p_ass_attribute_category => NULL
432 ,p_ass_attribute1 => NULL
433 ,p_ass_attribute2 => NULL
434 ,p_ass_attribute3 => NULL
435 ,p_ass_attribute4 => NULL
436 ,p_ass_attribute5 => NULL
437 ,p_ass_attribute6 => NULL
438 ,p_ass_attribute7 => NULL
439 ,p_ass_attribute8 => NULL
440 ,p_ass_attribute9 => NULL
441 ,p_ass_attribute10 => NULL
442 ,p_ass_attribute11 => NULL
443 ,p_ass_attribute12 => NULL
444 ,p_ass_attribute13 => NULL
445 ,p_ass_attribute14 => NULL
446 ,p_ass_attribute15 => NULL
447 ,p_ass_attribute16 => NULL
448 ,p_ass_attribute17 => NULL
449 ,p_ass_attribute18 => NULL
450 ,p_ass_attribute19 => NULL
451 ,p_ass_attribute20 => NULL
452 ,p_ass_attribute21 => NULL
453 ,p_ass_attribute22 => NULL
454 ,p_ass_attribute23 => NULL
455 ,p_ass_attribute24 => NULL
456 ,p_ass_attribute25 => NULL
457 ,p_ass_attribute26 => NULL
458 ,p_ass_attribute27 => NULL
459 ,p_ass_attribute28 => NULL
460 ,p_ass_attribute29 => NULL
461 ,p_ass_attribute30 => NULL
462 ,p_sal_review_period => NULL
463 ,p_sal_review_period_frequency => NULL
464 ,p_perf_review_period => NULL
465 ,p_perf_review_period_frequency => NULL
466 ,p_pay_basis_id => NULL
467 ,p_employment_category => NULL
468 ,p_bargaining_unit_code => NULL
469 ,p_labour_union_member_flag => NULL
470 ,p_hourly_salaried_code => NULL);
471
472 --
473 -- 115.10 (START)
474 --
475 --
476 -- Handle potentially overlapping PDS due to rehire before FPD
477 --
478 hr_employee_api.manage_rehire_primary_asgs
479 (p_person_id => p_person_id
480 ,p_rehire_date => p_effective_start_date
481 ,p_cancel => 'N'
482 );
483 --
484 -- 115.10 (END)
485 --
486
487 --START WWBUG 2130950 HR/WF SYNCH -- tpapired
488 --
489 open l_asg_cur;
490 fetch l_asg_cur into l_asg_rec;
491 close l_asg_cur;
492
493 per_hrwf_synch.per_asg_wf(
494 p_rec => l_asg_rec,
495 p_action => 'INSERT');
496 --
497 --END WWBUG 2130950 HR/WF SYNCH -- tpapired
498 -- Populate security list for new employee.
499 hr_security_internal.populate_new_person(
500 p_business_group_id=>p_business_group_id,
501 p_person_id =>p_person_id);
502
503 -- Load Budget value defaults
504 l_primary_flag := 'Y';
505 hr_utility.set_location('per_people9_pkg.insert_employee_rows',6);
506 PER_ASSIGNMENTS_F1_PKG.post_insert(p_prim_change_flag =>l_primary_flag
507 ,p_val_st_date => p_effective_start_date
508 ,p_new_end_date =>p_effective_end_date
509 ,p_eot =>p_effective_end_date
510 ,p_pd_os_id => NULL
511 ,p_ass_id =>l_assignment_id
512 ,p_new_prim_ass_id =>l_assignment_id_temp
513 ,p_pg_id => NULL
514 ,p_group_name => NULL
515 ,p_bg_id => p_business_group_id
516 ,p_dt_upd_mode => NULL
517 ,p_dt_del_mode => NULL
518 ,p_per_sys_st => NULL
519 ,p_sess_date =>p_effective_start_date
520 ,p_val_end_date =>p_effective_end_date
521 ,p_new_pay_id => NULL
522 ,p_old_pay_id => NULL
523 ,p_scl_id => NULL
524 ,p_scl_concat => NULL
525 ,p_warning =>l_warning );
526 --
527 end;
528 --
529 procedure update_old_person_row(p_person_id NUMBER
530 ,p_session_date DATE
531 ,p_effective_start_date DATE) is
532 l_rowid VARCHAR2(18);
533 cursor old_per is
534 select rowid
535 from per_people_f
536 where person_id = p_person_id
537 and effective_end_date = p_session_date -1
538 for update of effective_end_date;
539 begin
540 open old_per;
541 fetch old_per into l_rowid;
542 if old_per%NOTFOUND then
543 return;
544 end if;
545 close old_per;
546 begin
547 update per_people_f
548 set effective_end_date = p_effective_start_date -1
549 where rowid = l_rowid;
550 --
551 if sql%rowcount <> 1 then
552 fnd_message.set_name('PAY','HR_6153_ALL_PROCEDURE_FAIL');
553 fnd_message.set_token('PROCEDURE','update_old_person');
554 fnd_message.set_token('STEP','2');
555 app_exception.raise_exception;
556 end if;
557 end;
558 end update_old_person_row;
559 --
560 --
561 END per_people9_pkg;