[Home] [Help]
PACKAGE BODY: APPS.PER_DB_PER_ADDITIONAL
Source
1 PACKAGE BODY per_db_per_additional AS
2 /* $Header: peadditn.pkb 120.0 2005/05/31 04:56:03 appldev noship $ */
3 /*
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
8 * Chertsey, England. *
9 * *
10 * All rights reserved. *
11 * *
12 * This material has been provided pursuant to an agreement *
13 * containing restrictions on its use. The material is also *
14 * protected by copyright law. No part of this material may *
15 * be copied or distributed, transmitted or transcribed, in *
16 * any form or by any means, electronic, mechanical, magnetic, *
17 * manual, or otherwise, or disclosed to third parties without *
18 * the express written permission of Oracle Corporation UK Ltd, *
19 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
20 * England. *
21 * *
22 ****************************************************************** */
23 /*
24 Name : per_db_per_additional (BODY)
25
26 Description : This package defines procedures required to
27 create all additional entities in Personnel.
28 That is:
29
30 Applicants,
31 Employees,
32 Secondary assignments,
33 Others,
34 Contacts.
35 Change List
36 -----------
37 Version Date Author ER/CR No. Description of Change
38 -------+---------+----------+---------+--------------------------
39 70.0 11-JAN-93 SZWILLIA Date Created
40 70.1 12-JAN-93 SZWILLIA Changed SELECTS to DISTINCT
41 where necessary.
42 70.3 19-JAN-93 SZWILLIA Changed status type selects
43 to UNIONS with amendments.
44 70.4 20-JAN-93 SZWILLIA Corrected error handling and
45 warning setting.
46 70.6 04-MAR-93 SZWILLIA Changed parameters to DATEs.
47 70.7 10-MAR-93 SZWILLIA Changed parameter lists for
48 employees and applicants.
49 Made significant changes to
50 allow for vacancies and locations.
51 Changed insert_assignment to
52 perform third party population
53 of letters and budget values.
54 70.8 11-MAR-93 NKHAN Added 'exit' at the end
55 70.9 18-MAR-93 SZWILLIA Changed interface to create_applicant
56 create_employee and
57 create_secondary_assign.
58 70.10 19-MAR-93 SZWILLIA Minor corrections.
59 70.11 19-MAR-93 SZWILLIA Re-instated exit.
60 70.12 29-MAR-93 SZWILLIA Changed default for expense check
61 to match domain.
62 70.13 26-APR-93 TMATHERS Changed call to derive_full_name
63 to account for it's change
64 to a procedure.
65 70.14 11-JUN-93 TMathers Changed call to validate_dob
66 removed the current_emp_or_apl_flag
67 parameter.
68 70.15 13-JUN-93 TMathers Changed call to generate_number
69 added l_person_id parameter.
70 70.20 23-NOV-94 rfine Suppressed index on business_group_id
71 in all where clauses.
72 110.1 2-APR-98 SASmith Change to procedure call to hr_assignment.load_budget_values
73 to include the assignment's effective start and end dates.
74 This is required as the budget values table is now date tracked.
75 110.2 5-FEB-99 LSIGRIST Checked, upadated MLS and date formats
76 for release 11.5 compliancy.
77 115.3 22-APR-99 ALogue Canonical number support for
78 normal_hours in insert_assignment.
79 115.4 24-AUG-99 JPBard Remove work_telephone from insert list
80 115.5 26-JUN-00 CCarter Changed per_jobs to per_jobs_v for
81 Job Groups.
82 115.11 15-SEP-00 GPERRY Leapfrog of 115.9 with fix for
83 WWBUG 1390173.
84 115.12 19-OCT-00 GPERRY Fixed WWBUG 1408379.
85 Added hook calls to OAB so life event triggers
86 work.
87 115.13 29-MAY-01 A.Sahay PTU Changes
88 115.13 19-JUN-01 GPERRY Performance fixes for WWBUG 1833930.
89 Changed references from hr_locations to
90 hr_locations_all. This is done because
91 of the join using location_code which
92 is consistent with hr_locations_all and
93 not hz_locations.
94 115.14 06-JUL-01 PBODLA Bug 1877018 - Passed final_process_date
95 while calling ben_pps_trg.ler_chk
96 to detect potential life events.
97 115.18 20-JUN-02 vbanner changes in call to generate_number
98 and validate_unique_number
99 to allow compilation
100 Bug 2426235.
101 115.19 09-Dec-02 joward MLS enabled grade name
102 115.20 15-Jan-03 pkakar updated function insert assignment to
103 include vacancy_id
104 115.21 29-Jun-04 vanantha Performance fixes(Bug 3648477)
105 115.22 20-jan-05 irgonzal 3889584 Added call to new routine to
106 derive person names.
107 ================================================================= */
108 --
109 --
110 FUNCTION insert_period_of_service
111 (p_person_id NUMBER
112 ,p_business_group_id NUMBER
113 ,p_date_start DATE
114 ,p_accepted_termination_date DATE
115 ,p_actual_termination_date DATE
116 ,p_final_process_date DATE
117 ,p_last_standard_process_date DATE
118 ,p_leaving_reason VARCHAR2
119 ,p_notified_termination_date DATE
120 ,p_projected_termination_date DATE
121 ,p_term_accepted_person_id NUMBER)
122 return NUMBER IS
123 --
124 l_period_of_service_id NUMBER;
125 l_emp_check VARCHAR2(1);
126 --
127 -- START WWBUG fix for 1390173
128 --
129 l_old ben_pps_ler.g_pps_ler_rec;
130 l_new ben_pps_ler.g_pps_ler_rec;
131 --
132 -- END WWBUG fix for 1390173
133 --
134 --
135 begin
136 --
137 --
138 hr_utility.set_location('per_db_per_additional.insert_period_of_service',1);
139 SELECT per_periods_of_service_s.nextval
140 INTO l_period_of_service_id
141 FROM sys.dual;
142 --
143 hr_utility.set_location('per_db_per_additional.insert_period_of_service',2);
144 --
145 -- Note : this check will only work for NEW employees - ie no
146 -- previous periods of service.
147 --
148 SELECT 'Y'
149 INTO l_emp_check
150 FROM sys.dual
151 WHERE p_date_start = (SELECT min(effective_start_date)
152 FROM per_people_f pp
153 WHERE pp.person_id = p_person_id
154 AND pp.current_employee_flag = 'Y');
155 --
156 hr_utility.set_location('per_db_per_additional.insert_period_of_service',5);
157 INSERT INTO per_periods_of_service
158 (period_of_service_id
159 ,business_group_id
160 ,person_id
161 ,date_start
162 ,accepted_termination_date
163 ,actual_termination_date
164 ,final_process_date
165 ,last_standard_process_date
166 ,leaving_reason
167 ,notified_termination_date
168 ,projected_termination_date
169 ,termination_accepted_person_id
170 ,last_update_date
171 ,last_updated_by
172 ,last_update_login
173 ,created_by
174 ,creation_date)
175 values
176 (l_period_of_service_id
177 ,p_business_group_id
178 ,p_person_id
179 ,p_date_start
180 ,p_accepted_termination_date
181 ,p_actual_termination_date
182 ,p_final_process_date
183 ,p_last_standard_process_date
184 ,p_leaving_reason
185 ,p_notified_termination_date
186 ,p_projected_termination_date
187 ,p_term_accepted_person_id
188 ,SYSDATE
189 ,0
190 ,0
191 ,0
192 ,SYSDATE);
193 --
194 --
195 -- START WWBUG fix for 1390173
196 --
197 l_new.PERSON_ID := p_person_id;
198 l_new.BUSINESS_GROUP_ID := p_business_group_id;
199 l_new.DATE_START := p_date_start;
200 l_new.ACTUAL_TERMINATION_DATE := p_actual_termination_date;
201 l_new.LEAVING_REASON := p_leaving_reason;
202 l_new.ADJUSTED_SVC_DATE := null;
203 l_new.ATTRIBUTE1 := null;
204 l_new.ATTRIBUTE2 := null;
205 l_new.ATTRIBUTE3 := null;
206 l_new.ATTRIBUTE4 := null;
207 l_new.ATTRIBUTE5 := null;
208 l_new.final_process_date := p_final_process_date;
209 --
210 ben_pps_ler.ler_chk(p_old => l_old
211 ,p_new => l_new
212 ,p_event => 'INSERTING'
213 ,p_effective_date => p_date_start);
214 --
215 -- END WWBUG fix for 1390173
216 --
217 --
218 return l_period_of_service_id;
219 --
220 end insert_period_of_service;
221 --
222 --
223 FUNCTION insert_application
224 (p_person_id NUMBER
225 ,p_business_group_id NUMBER
226 ,p_date_received DATE
227 ,p_date_end DATE
228 ,p_current_employer VARCHAR2
229 ,p_projected_hire_date DATE
230 ,p_termination_reason VARCHAR2)
231 return NUMBER IS
232 --
233 l_application_id NUMBER;
234 l_apl_check VARCHAR2(1);
235 l_termination_reason VARCHAR2(80) := null;
236 --
237 begin
238 --
239 --
240 hr_utility.set_location('per_db_per_additional.insert_application',1);
241 SELECT per_applications_s.nextval
242 INTO l_application_id
243 FROM sys.dual;
244 --
245 hr_utility.set_location('per_db_per_additional.insert_application',2);
246 --
247 -- Note : this check will only work for NEW applicants - ie no
248 -- previous applications .
249 --
250 SELECT 'Y'
251 INTO l_apl_check
252 FROM sys.dual
253 WHERE p_date_received = (SELECT min(effective_start_date)
254 FROM per_people_f pp
255 WHERE pp.person_id = p_person_id
256 AND pp.current_applicant_flag = 'Y');
257 --
258 hr_utility.set_location('per_db_per_additional.insert_application',5);
259 INSERT INTO per_applications
260 (application_id
261 ,business_group_id
262 ,person_id
263 ,date_received
264 ,date_end
265 ,current_employer
266 ,projected_hire_date
267 ,termination_reason
268 ,last_update_date
269 ,last_updated_by
270 ,last_update_login
271 ,created_by
272 ,creation_date)
273 values
274 (l_application_id
275 ,p_business_group_id
276 ,p_person_id
277 ,p_date_received
278 ,p_date_end
279 ,p_current_employer
280 ,p_projected_hire_date
281 ,l_termination_reason
282 ,SYSDATE
283 ,0
284 ,0
285 ,0
286 ,SYSDATE);
287 --
288 return l_application_id;
289 --
290 end insert_application;
291 --
292 --
293 FUNCTION insert_person
294 (p_effective_start_date DATE
295 ,p_effective_end_date DATE
296 ,p_business_group_id NUMBER
297 ,p_person_type_id NUMBER
298 ,p_last_name VARCHAR2
299 ,p_applicant_number VARCHAR2
300 ,p_current_applicant_flag VARCHAR2
301 ,p_current_employee_flag VARCHAR2
302 ,p_current_emp_or_apl_flag VARCHAR2
303 ,p_employee_data_verified DATE
304 ,p_date_of_birth DATE
305 ,p_employee_number VARCHAR2
306 ,p_expense_chk_send_to_address VARCHAR2
307 ,p_first_name VARCHAR2
308 ,p_known_as VARCHAR2
309 ,p_marital_status VARCHAR2
310 ,p_middle_names VARCHAR2
311 ,p_nationality VARCHAR2
312 ,p_national_identifier VARCHAR2
313 ,p_previous_last_name VARCHAR2
314 ,p_registered_disabled_flag VARCHAR2
315 ,p_sex VARCHAR2
316 ,p_title VARCHAR2
317 ,p_work_telephone VARCHAR2)
318 return NUMBER
319 IS
320 --
321 l_person_id NUMBER;
322 l_applicant_number VARCHAR2(30) ;
323 l_employee_number VARCHAR2(30);
324 l_npw_number VARCHAR2(30) := NULL;
325 l_full_name VARCHAR2(240);
326 l_order_name varchar2(240);
327 l_global_name varchar2(240);
328 l_local_name varchar2(240);
329 l_dup_name VARCHAR2(1);
330 l_current_npw VARCHAR2(30) := NULL;
331 --
332 begin
333 --
334 hr_utility.set_location('per_db_per_additional.insert_person',1);
335 --
336 l_applicant_number := p_applicant_number;
337 l_employee_number := p_employee_number;
338 --
339 SELECT per_people_s.nextval
340 INTO l_person_id
341 FROM sys.dual;
342 --
343 hr_utility.set_location('per_db_per_additional.insert_person',2);
344 hr_person.validate_national_identifier(p_national_identifier,
345 l_person_id,
346 p_business_group_id);
347 --
348 if hr_utility.check_warning then
349 hr_utility.raise_error;
350 end if;
351 --
352 hr_utility.set_location('per_db_per_additional.insert_person',3);
353 hr_person.validate_dob(p_date_of_birth,
354 p_effective_start_date);
355 --
356 --
357 hr_utility.set_location('per_db_per_additional.insert_person',4);
358 --
359 -- this is replaced by new call below
360 --
361 --hr_person.derive_full_name(p_first_name,
362 -- p_middle_names, p_last_name, p_known_as,
363 -- p_title, p_date_of_birth,
364 -- l_person_id, p_business_group_id
365 -- ,l_full_name,l_dup_name);
366
367 --
368 if hr_utility.check_warning then
369 hr_utility.raise_error;
370 end if;
371 --
372 hr_person.generate_number(p_current_employee_flag,
373 p_current_applicant_flag,
374 l_current_npw, --current_npw_flag
375 p_national_identifier,
376 p_business_group_id ,
377 l_person_id,
378 l_employee_number,
379 l_applicant_number,
380 l_npw_number);
381 --
382 --
383 hr_utility.set_location('per_db_per_additional.insert_person',5);
384 hr_person.validate_sex_and_title(p_current_employee_flag
385 ,p_sex
386 ,p_title);
387 if hr_utility.check_warning then
388 hr_utility.raise_error;
389 end if;
390 --
391 hr_person.validate_unique_number(l_person_id
392 ,p_business_group_id
393 ,l_employee_number
394 ,l_applicant_number
395 ,l_npw_number --npw_number
396 ,p_current_employee_flag
397 ,p_current_applicant_flag
398 ,l_current_npw --current_npw_flag
399 );
400 --
401 hr_utility.set_location('per_db_per_additional.insert_person',6);
402 --
403 hr_person_name.derive_person_names -- #3889584
404 (p_format_name => NULL, -- derive all names
405 p_business_group_id => p_business_group_id,
406 p_person_id => l_person_id,
407 p_first_name => p_first_name,
408 p_middle_names => p_middle_names,
409 p_last_name => p_last_name,
410 p_known_as => p_known_as,
411 p_title => p_title,
412 p_suffix => NULL,
413 p_pre_name_adjunct => NULL,
414 p_date_of_birth => p_date_of_birth,
415 p_previous_last_name => p_previous_last_name ,
416 --
417 p_employee_number => l_employee_number ,
418 p_applicant_number => l_applicant_number ,
419 p_npw_number => l_npw_number,
420 p_full_name => l_full_name,
421 p_order_name => l_order_name,
422 p_global_name => l_global_name,
423 p_local_name => l_local_name,
424 p_duplicate_flag => l_dup_name
425 );
426 --
427 hr_utility.set_location('per_db_per_additional.insert_person',7);
428 --
429 INSERT INTO per_people_f
430 (person_id
431 ,effective_start_date
432 ,effective_end_date
433 ,business_group_id
434 ,person_type_id
435 ,last_name
436 ,start_date
437 ,applicant_number
438 ,current_applicant_flag
439 ,current_employee_flag
440 ,current_emp_or_apl_flag
441 ,date_employee_data_verified
442 ,date_of_birth
443 ,employee_number
444 ,expense_check_send_to_address
445 ,first_name
446 ,full_name
447 ,known_as
448 ,marital_status
449 ,middle_names
450 ,nationality
451 ,national_identifier
452 ,previous_last_name
453 ,registered_disabled_flag
454 ,sex
455 ,title
456 ,order_name
457 ,global_name
458 ,local_name
459 -- ,work_telephone
460 ,last_update_date
461 ,last_updated_by
462 ,last_update_login
463 ,created_by
464 ,creation_date)
465 values
466 (l_person_id
467 ,p_effective_start_date
468 ,p_effective_end_date
469 ,p_business_group_id
470 ,p_person_type_id
471 ,p_last_name
472 ,p_effective_start_date
473 ,l_applicant_number
474 ,p_current_applicant_flag
475 ,p_current_employee_flag
476 ,p_current_emp_or_apl_flag
477 ,p_employee_data_verified
478 ,p_date_of_birth
479 ,l_employee_number
480 ,p_expense_chk_send_to_address
481 ,p_first_name
482 ,l_full_name
483 ,p_known_as
484 ,p_marital_status
485 ,p_middle_names
486 ,p_nationality
487 ,p_national_identifier
488 ,p_previous_last_name
489 ,p_registered_disabled_flag
490 ,p_sex
491 ,p_title
492 ,l_order_name
493 ,l_global_name
494 ,l_local_name
495 -- ,p_work_telephone
496 ,SYSDATE
497 ,0
498 ,0
499 ,0
500 ,SYSDATE);
501 --
502 --
503 return l_person_id;
504 --
505 end insert_person;
506 --
507 --
508 FUNCTION insert_assignment
509 (p_effective_start_date DATE
510 ,p_effective_end_date DATE
511 ,p_business_group_id NUMBER
512 ,p_person_id NUMBER
513 ,p_assignment_type VARCHAR2
514 ,p_organization_id NUMBER
515 ,p_grade_id NUMBER
516 ,p_job_id NUMBER
517 ,p_position_id NUMBER
518 ,p_payroll_id NUMBER
519 ,p_location_id NUMBER
520 ,p_vacancy_id NUMBER
521 ,p_people_group_id NUMBER
522 ,p_soft_coding_keyflex_id NUMBER
523 ,p_assignment_status_type_id NUMBER
524 ,p_primary_flag VARCHAR2
525 ,p_manager_flag VARCHAR2
526 ,p_change_reason VARCHAR2
527 ,p_date_probation_end DATE
528 ,p_frequency VARCHAR2
529 ,p_internal_address_line VARCHAR2
530 ,p_normal_hours VARCHAR2
531 ,p_probation_period VARCHAR2
532 ,p_probation_unit VARCHAR2
533 ,p_recruiter_id NUMBER
534 ,p_special_ceiling_step_id NUMBER
535 ,p_supervisor_id NUMBER
536 ,p_recruitment_activity_id NUMBER
537 ,p_person_referred_by_id NUMBER
538 ,p_source_organization_id NUMBER
539 ,p_time_normal_finish VARCHAR2
540 ,p_time_normal_start VARCHAR2)
541 return NUMBER IS
542 --
543 l_assignment_id NUMBER;
544 l_application_id NUMBER := null;
545 l_period_of_service_id NUMBER := null;
546 l_assignment_sequence NUMBER;
547 l_assignment_number VARCHAR2(30) := null;
548 l_employee_number VARCHAR2(30) := null;
549 l_letter_type_id NUMBER;
550 l_letter_request_id NUMBER;
551 --
552 begin
553 --
554 --
555 hr_utility.set_location('per_db_per_additional.insert_assignment',1);
556 SELECT per_assignments_s.nextval
557 , pp.employee_number
558 INTO l_assignment_id
559 , l_employee_number
560 FROM per_people_f pp
561 WHERE pp.person_id = p_person_id
562 AND p_effective_start_date BETWEEN pp.effective_start_date
563 AND pp.effective_end_date;
564 --
565 hr_utility.set_location('per_db_per_additional.insert_assignment',20);
566 if p_assignment_type = 'E' then -- Employee
567 SELECT period_of_service_id
568 INTO l_period_of_service_id
569 FROM per_periods_of_service
570 WHERE person_id = p_person_id
571 AND p_effective_start_date
572 BETWEEN date_start AND
573 nvl(actual_termination_date,to_date('4712/12/31','YYYY/MM/DD'))
574 AND p_effective_end_date <=
575 nvl(actual_termination_date,to_date('4712/12/31','YYYY/MM/DD'));
576 else -- Applicant
577 SELECT application_id
578 INTO l_application_id
579 FROM per_applications
580 WHERE person_id = p_person_id
581 AND p_effective_start_date BETWEEN date_received AND
582 nvl(date_end,to_date('4712/12/31','YYYY/MM/DD'))
583 AND p_effective_end_date <=
584 nvl(date_end,to_date('4712/12/31','YYYY/MM/DD'));
585 end if;
586 --
587 hr_utility.set_location('per_db_per_additional.insert_assignment',2);
588 hr_assignment.gen_new_ass_sequence(p_person_id,
589 p_assignment_type,
590 l_assignment_sequence);
591 --
592 --
593 if p_assignment_type = 'E' then -- Employee
594 hr_utility.set_location('per_db_per_additional.insert_assignment',3);
595 hr_assignment.gen_new_ass_number(l_assignment_id,
596 p_business_group_id,
597 l_employee_number,
598 l_assignment_sequence,
599 l_assignment_number);
600 --
601 end if;
602 --
603 hr_utility.set_location('per_db_per_additional.insert_assignment',4);
604 hr_assignment.check_hours(p_frequency,
605 fnd_number.canonical_to_number(p_normal_hours));
606 --
607 --
608 hr_utility.set_location('per_db_per_additional.insert_assignment',5);
609 INSERT INTO per_all_assignments_f
610 (assignment_id
611 ,effective_start_date
612 ,effective_end_date
613 ,business_group_id
614 ,grade_id
615 ,position_id
616 ,job_id
617 ,assignment_status_type_id
618 ,payroll_id
619 ,location_id
620 ,person_id
621 ,organization_id
622 ,people_group_id
623 ,soft_coding_keyflex_id
624 ,vacancy_id
625 ,assignment_sequence
626 ,assignment_type
627 ,manager_flag
628 ,primary_flag
629 ,application_id
630 ,assignment_number
631 ,change_reason
632 ,date_probation_end
633 ,frequency
634 ,internal_address_line
635 ,normal_hours
636 ,period_of_service_id
637 ,probation_period
638 ,probation_unit
639 ,recruiter_id
640 ,special_ceiling_step_id
641 ,supervisor_id
642 ,recruitment_activity_id
643 ,person_referred_by_id
644 ,source_organization_id
645 ,time_normal_finish
646 ,time_normal_start
647 ,last_update_date
648 ,last_updated_by
649 ,last_update_login
650 ,created_by
651 ,creation_date)
652 values (l_assignment_id
653 , p_effective_start_date
654 , p_effective_end_date
655 , p_business_group_id
656 , p_grade_id
657 , p_position_id
658 , p_job_id
659 , p_assignment_status_type_id
660 , p_payroll_id
661 , p_location_id
662 , p_person_id
663 , p_organization_id
664 , p_people_group_id
665 , p_soft_coding_keyflex_id
666 , p_vacancy_id
667 , l_assignment_sequence
668 , p_assignment_type
669 , nvl(p_manager_flag,'N')
670 , p_primary_flag
671 , l_application_id
672 , l_assignment_number
673 , p_change_reason
674 , p_date_probation_end
675 , p_frequency
676 , p_internal_address_line
677 , fnd_number.canonical_to_number(p_normal_hours)
678 , l_period_of_service_id
679 , p_probation_period
680 , p_probation_unit
681 , p_recruiter_id
682 , p_special_ceiling_step_id
683 , p_supervisor_id
684 , p_recruitment_activity_id
685 , p_person_referred_by_id
686 , p_source_organization_id
687 , p_time_normal_finish
688 , p_time_normal_start
689 , SYSDATE
690 , 0
691 , 0
692 , 0
693 , SYSDATE);
694 --
695 -- Add effective start and end dates.
696 --SASmith 2-APR-98
697 --
698 hr_utility.set_location('per_db_per_additional.insert_assignment',8);
699 hr_assignment.load_budget_values(l_assignment_id
700 ,p_business_group_id
701 ,0
702 ,0
703 ,p_effective_start_date
704 ,p_effective_end_date);
705 --
706 hr_utility.set_location('per_db_per_additional.insert_assignment',10);
707 if p_assignment_type = 'E' then null;
708 else -- assignment type = 'A'
709 hr_utility.set_location('per_db_per_additional.insert_assignment',15);
710 begin
711 SELECT letter_type_id
712 INTO l_letter_type_id
713 FROM per_letter_gen_statuses
714 WHERE business_group_id + 0 = p_business_group_id
715 AND assignment_status_type_id = p_assignment_status_type_id
716 AND enabled_flag = 'Y';
717 --
718 exception when NO_DATA_FOUND then null;
719 end;
720 --
721 hr_utility.set_location('per_db_per_additional.insert_assignment',20);
722 if l_letter_type_id IS NOT NULL then
723 begin
724 hr_utility.set_location('per_db_per_additional.insert_assignment',25);
725 SELECT letter_request_id
726 INTO l_letter_request_id
727 FROM per_letter_requests
728 WHERE letter_type_id = l_letter_type_id
729 AND business_group_id + 0 = p_business_group_id
730 AND vacancy_id = p_vacancy_id
731 AND request_status = 'PENDING' ;
732 --
733 exception when NO_DATA_FOUND then null;
734 end;
735 --
736 hr_utility.set_location('per_db_per_additional.insert_assignment',30);
737 if l_letter_request_id IS NOT NULL then null;
738 else
739 hr_utility.set_location('per_db_per_additional.insert_assignment',35);
740 SELECT per_letter_requests_s.nextval
741 INTO l_letter_request_id
742 FROM sys.dual;
743 --
744 hr_utility.set_location('per_db_per_additional.insert_assignment',40);
745 INSERT INTO per_letter_requests
746 (letter_request_id
747 ,business_group_id
748 ,letter_type_id
749 ,date_from
750 ,request_status
751 ,auto_or_manual
752 ,last_update_date
753 ,last_updated_by
754 ,last_update_login
755 ,created_by
756 ,creation_date
757 ,vacancy_id)
758 VALUES
759 (l_letter_request_id
760 ,p_business_group_id
761 ,l_letter_type_id
762 ,p_effective_start_date
763 ,'PENDING'
764 ,'AUTO'
765 ,SYSDATE
766 ,0
767 ,0
768 ,0
769 ,SYSDATE
770 ,p_vacancy_id);
771 --
772 end if;
773 --
774 hr_utility.set_location('per_db_per_additional.insert_assignment',45);
775 INSERT INTO per_letter_request_lines
776 (letter_request_line_id
777 ,business_group_id
778 ,letter_request_id
779 ,person_id
780 ,assignment_id
781 ,assignment_status_type_id
782 ,date_from
783 ,last_update_date
784 ,last_updated_by
785 ,last_update_login
786 ,created_by
787 ,creation_date)
788 VALUES
789 (per_letter_request_lines_s.nextval
790 ,p_business_group_id
791 ,l_letter_request_id
792 ,p_person_id
793 ,l_assignment_id
794 ,p_assignment_status_type_id
795 ,p_effective_start_date
796 ,SYSDATE
797 ,0
798 ,0
799 ,0
800 ,SYSDATE);
801 --
802 end if; -- letter type not null
803 --
804 end if; -- assignment type = 'A'
805 --
806 return l_assignment_id;
807 --
808 end insert_assignment;
809 --
810 --
811 FUNCTION create_applicant
812 (p_effective_start_date DATE default null
813 ,p_effective_end_date DATE default null
814 ,p_business_group VARCHAR2
815 ,p_last_name VARCHAR2
816 ,p_applicant_number VARCHAR2 default null
817 ,p_organization VARCHAR2 default null
818 ,p_position VARCHAR2 default null
819 ,p_job VARCHAR2 default null
820 ,p_grade VARCHAR2 default null
821 ,p_location VARCHAR2 default null
822 ,p_vacancy VARCHAR2 default null
823 ,p_people_group_id NUMBER default null
824 ,p_start_date DATE default null
825 ,p_date_of_birth DATE default null
826 ,p_first_name VARCHAR2 default null
827 ,p_known_as VARCHAR2 default null
828 ,p_marital_status VARCHAR2 default 'S'
829 ,p_middle_names VARCHAR2 default null
830 ,p_nationality VARCHAR2 default null
831 ,p_previous_last_name VARCHAR2 default null
832 ,p_registered_disabled_flag VARCHAR2 default 'N'
833 ,p_sex VARCHAR2 default 'M'
834 ,p_title VARCHAR2 default 'MR.'
835 ,p_work_telephone VARCHAR2 default null
836 ,p_frequency VARCHAR2 default 'W'
837 ,p_normal_hours VARCHAR2 default '37.5'
838 ,p_current_employer VARCHAR2 default null
839 ,p_projected_hire_date DATE default null
840 ,p_recruitment_activity_id NUMBER default null
841 ,p_person_referred_by_id NUMBER default null
842 ,p_source_organization_id NUMBER default null
843 ,p_time_normal_start VARCHAR2 default '08:00'
844 ,p_time_normal_finish VARCHAR2 default '17:30'
845 ,p_probation_period VARCHAR2 default null
846 ,p_probation_unit VARCHAR2 default null
847 ,p_recruiter_id NUMBER default null
848 ,p_internal_address_line VARCHAR2 default null
849 ,p_change_reason VARCHAR2 default null)
850 return NUMBER
851 IS
852 --
853 -- local variables
854 --
855 l_business_group_id NUMBER(15);
856 l_person_type_id NUMBER(15);
857 l_person_id NUMBER(15);
858 l_full_name VARCHAR2(240);
859 l_current_applicant_flag VARCHAR2(1);
860 l_current_employee_flag VARCHAR2(1);
861 l_current_emp_or_apl_flag VARCHAR2(1);
862 l_effective_start_date DATE;
863 l_effective_end_date DATE;
864 l_date_of_birth DATE;
865 l_application_id NUMBER(15);
866 l_assignment_id NUMBER(15);
867 l_assignment_status_type_id NUMBER(15);
868 --
869 l_job_id NUMBER(15) := null;
870 l_position_id NUMBER(15) := null;
871 l_grade_id NUMBER(15) := null;
872 l_organization_id NUMBER(15) := null;
873 l_vacancy_id NUMBER(15) := null;
874 l_location_id NUMBER(15) := null;
875 l_recruiter_id NUMBER(15) := null;
876 l_people_group_id NUMBER(15) := null;
877 l_recruitment_activity_id NUMBER;
878 l_person_referred_by_id NUMBER;
879 l_source_organization_id NUMBER;
880 l_people_group_structure NUMBER;
881 --
882 --
883 begin -- FUNCTION create_applicant
884 --
885 --
886 hr_utility.set_location('per_db_per_additional.create_applicant',1);
887 --
888 SELECT business_group_id
889 , people_group_structure
890 INTO l_business_group_id
891 , l_people_group_structure
892 FROM per_business_groups
893 WHERE name = p_business_group;
894 --
895 hr_utility.set_location('per_db_per_additional.create_applicant',2);
896 --
897 SELECT person_type_id
898 INTO l_person_type_id
899 FROM per_person_types
900 WHERE business_group_id = l_business_group_id --Bug fix 3648477
901 AND system_person_type = 'APL'
902 AND default_flag = 'Y';
903 --
904 hr_utility.set_location('per_db_per_additional.create_applicant',4);
905 SELECT assignment_status_type_id
906 INTO l_assignment_status_type_id
907 FROM per_ass_status_type_amends
908 WHERE business_group_id + 0 = l_business_group_id
909 AND default_flag = 'Y'
910 AND per_system_status = 'ACTIVE_APL'
911 UNION
912 SELECT ast.assignment_status_type_id
913 FROM per_assignment_status_types ast
914 WHERE nvl(ast.business_group_id,l_business_group_id)
915 = l_business_group_id
916 AND ast.default_flag = 'Y'
917 AND ast.per_system_status = 'ACTIVE_APL'
918 AND NOT EXISTS (SELECT null
919 FROM per_ass_status_type_amends ast1
920 WHERE ast1.business_group_id + 0 = l_business_group_id
921 AND ast1.default_flag = 'Y'
922 AND ast1.assignment_status_type_id =
923 ast.assignment_status_type_id) ;
924 --
925 hr_utility.set_location('per_db_per_additional.create_applicant',6);
926 l_current_employee_flag := null;
927 l_current_applicant_flag := 'Y';
928 l_current_emp_or_apl_flag := 'Y';
929 l_effective_start_date := nvl(p_effective_start_date, trunc(SYSDATE));
930 l_effective_end_date := nvl(p_effective_end_date,
931 to_date('4712/12/31','YYYY/MM/DD'));
932 l_date_of_birth := nvl(p_date_of_birth,
933 to_date('1958/01/01','YYYY/MM/DD'));
934 --
935 hr_utility.set_location('per_db_per_additional.create_applicant',8);
936 --
937 l_person_id := insert_person(l_effective_start_date
938 ,l_effective_end_date
939 ,l_business_group_id
940 ,l_person_type_id
941 ,p_last_name
942 ,p_applicant_number
943 ,l_current_applicant_flag
944 ,l_current_employee_flag
945 ,l_current_emp_or_apl_flag
946 ,null
947 ,l_date_of_birth
948 ,null
949 ,null
950 ,p_first_name
951 ,p_known_as
952 ,p_marital_status
953 ,p_middle_names
954 ,p_nationality
955 ,null
956 ,p_previous_last_name
957 ,p_registered_disabled_flag
958 ,p_sex
959 ,p_title
960 ,p_work_telephone );
961 --
962 --
963 --
964 hr_utility.set_location('per_db_per_additional.create_applicant',10);
965 l_application_id := insert_application(l_person_id
966 ,l_business_group_id
967 ,l_effective_start_date
968 ,null
969 ,p_current_employer
970 ,p_projected_hire_date
971 ,null);
972 --
973 -- PTU : Following code added for PTU
974
975 hr_per_type_usage_internal.maintain_person_type_usage
976 ( p_effective_date => l_effective_start_date
977 ,p_person_id => l_person_id
978 ,p_person_type_id => l_person_type_id
979 );
980
981 -- End of PTU Changes
982
983 --
984 hr_utility.set_location('per_db_per_additional.create_applicant',12);
985 --
986 -- WWBUG 1833930, changed hr_locations to hr_locations_all
987 --
988 if p_vacancy IS NULL then
989 --
990 if p_location IS NULL then null;
991 else
992 hr_utility.set_location('per_db_per_additional.create_applicant',14);
993 SELECT location_id
994 INTO l_location_id
995 FROM hr_locations_all
996 WHERE location_code = p_location
997 AND l_effective_start_date <= nvl(inactive_date,to_date('4712/12/31',
998 'YYYY/MM/DD'));
999 end if;
1000 --
1001 --
1002 hr_utility.set_location('per_db_per_additional.create_applicant',16);
1003 if p_position IS NULL then -- job and organization
1004 --
1005 if p_organization IS NULL then
1006 l_organization_id := l_business_group_id;
1007 else hr_utility.set_location('per_db_per_additional.create_applicant',18);
1008 SELECT organization_id
1009 , location_id
1010 INTO l_organization_id
1011 , l_location_id
1012 FROM hr_organization_units
1013 WHERE name = p_organization
1014 AND business_group_id + 0 = l_business_group_id
1015 AND l_effective_start_date BETWEEN date_from
1016 AND nvl(date_to,to_date('4712/12/31',
1017 'YYYY/MM/DD'));
1018 end if;
1019 --
1020 hr_utility.set_location('per_db_per_additional.create_applicant',1);
1021 if p_job IS NULL then null;
1022 else hr_utility.set_location('per_db_per_additional.create_applicant',20);
1023 SELECT job_id
1024 INTO l_job_id
1025 FROM per_jobs_v
1026 WHERE name = p_job
1027 AND business_group_id + 0 = l_business_group_id
1028 AND l_effective_start_date BETWEEN date_from
1029 AND nvl(date_to,to_date('4712/12/31',
1030 'YYYY/MM/DD'));
1031 end if;
1032 --
1033 else -- p_position is not null (position overrides job and org)
1034 hr_utility.set_location('per_db_per_additional.create_applicant',22);
1035 SELECT pos.position_id
1036 , pos.job_id
1037 , pos.organization_id
1038 , nvl(pos.location_id,nvl(org.location_id,l_location_id))
1039 INTO l_position_id
1040 , l_job_id
1041 , l_organization_id
1042 , l_location_id
1043 FROM per_organization_units org
1044 , per_positions pos
1045 WHERE pos.name = p_position
1046 AND pos.business_group_id + 0 = l_business_group_id
1047 AND pos.organization_id = org.organization_id
1048 AND l_effective_start_date BETWEEN pos.date_effective
1049 AND nvl(pos.date_end,to_date('4712/12/31',
1050 'YYYY/MM/DD'));
1051 end if;
1052 --
1053 if p_grade IS NULL then null;
1054 else
1055 hr_utility.set_location('per_db_per_additional.create_applicant',24);
1056 SELECT grade_id
1057 INTO l_grade_id
1058 FROM per_grades_vl
1059 WHERE name = p_grade
1060 AND business_group_id + 0 = l_business_group_id
1061 AND l_effective_start_date BETWEEN date_from
1062 AND nvl(date_to,to_date('4712/12/31',
1063 'YYYY/MM/DD'));
1064 end if;
1065 --
1066 else -- p_vacancy is not null (vacancy overrides all other columns)
1067 --
1068 hr_utility.set_location('per_db_per_additional.create_applicant',26);
1069 SELECT vacancy_id
1070 , position_id
1071 , job_id
1072 , organization_id
1073 , grade_id
1074 , people_group_id
1075 , location_id
1076 INTO l_vacancy_id
1077 , l_position_id
1078 , l_job_id
1079 , l_organization_id
1080 , l_grade_id
1081 , l_people_group_id
1082 , l_location_id
1083 FROM per_vacancies
1084 WHERE name = p_vacancy
1085 AND l_effective_start_date BETWEEN date_from
1086 AND nvl(date_to,to_date('4712/12/31','YYYY/MM/DD'));
1087 --
1088 hr_utility.set_location('per_db_per_additional.create_applicant',28);
1089 if l_organization_id IS NULL then
1090 l_organization_id := l_business_group_id; -- mandatory column
1091 end if;
1092 --
1093 hr_utility.set_location('per_db_per_additional.create_applicant',30);
1094 if p_recruitment_activity_id IS NULL then
1095 l_recruitment_activity_id := null;
1096 else
1097 hr_utility.set_location('per_db_per_additional.create_applicant',32);
1098 SELECT recruitment_activity_id
1099 INTO l_recruitment_activity_id
1100 FROM per_recruitment_activity_for
1101 WHERE business_group_id + 0 = l_business_group_id
1102 AND vacancy_id = l_vacancy_id;
1103 end if;
1104 --
1105 end if;
1106 --
1107 hr_utility.set_location('per_db_per_additional.create_applicant',34);
1108 if p_people_group_id IS NULL then
1109 l_people_group_id := null;
1110 else
1111 SELECT people_group_id
1112 INTO l_people_group_id
1113 FROM pay_people_groups
1114 WHERE people_group_id = p_people_group_id
1115 AND id_flex_num = l_people_group_structure;
1116 end if;
1117 --
1118 hr_utility.set_location('per_db_per_additional.create_applicant',36);
1119 if p_source_organization_id IS NULL then null;
1120 else
1121 SELECT organization_id
1122 INTO l_source_organization_id
1123 FROM per_organization_units
1124 WHERE organization_id = p_source_organization_id
1125 AND business_group_id + 0 = l_business_group_id
1126 AND l_effective_start_date BETWEEN date_from
1127 AND nvl(date_to,to_date('4712/12/31','YYYY/MM/DD'));
1128 end if;
1129 --
1130 hr_utility.set_location('per_db_per_additional.create_applicant',38);
1131 if p_recruiter_id IS NULL then null;
1132 else
1133 SELECT person_id
1134 INTO l_recruiter_id
1135 FROM per_people_f
1136 WHERE (business_group_id = l_business_group_id or
1137 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'Y')
1138 AND person_id = p_recruiter_id
1139 AND current_employee_flag = 'Y'
1140 AND l_effective_start_date BETWEEN effective_start_date
1141 AND effective_end_date;
1142 end if;
1143 --
1144 hr_utility.set_location('per_db_per_additional.create_applicant',40);
1145 if p_person_referred_by_id IS NULL then null;
1146 else
1147 SELECT person_id
1148 INTO l_person_referred_by_id
1149 FROM per_people_f
1150 WHERE (business_group_id = l_business_group_id or
1151 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'Y')
1152 AND person_id = p_person_referred_by_id
1153 AND current_employee_flag = 'Y'
1154 AND l_effective_start_date BETWEEN effective_start_date
1155 AND effective_end_date;
1156 end if;
1157 --
1158 hr_utility.set_location('per_db_per_additional.create_applicant',42);
1159 l_assignment_id := insert_assignment(l_effective_start_date
1160 , l_effective_end_date
1161 , l_business_group_id
1162 , l_person_id
1163 , 'A'
1164 , l_organization_id
1165 , l_grade_id
1166 , l_job_id
1167 , l_position_id
1168 , null
1169 , l_location_id
1170 , l_vacancy_id
1171 , l_people_group_id
1172 , null
1173 , l_assignment_status_type_id
1174 , 'Y'
1175 , null
1176 , p_change_reason
1177 , null
1178 , p_frequency
1179 , p_internal_address_line
1180 , p_normal_hours
1181 , null
1182 , null
1183 , l_recruiter_id
1184 , null
1185 , null
1186 , l_recruitment_activity_id
1187 , l_person_referred_by_id
1188 , l_source_organization_id
1189 , p_time_normal_finish
1190 , p_time_normal_start);
1191 --
1192 hr_utility.set_location('per_db_per_additional.create_applicant',45);
1193
1194 --
1195 return l_person_id;
1196 --
1197 --
1198 --
1199 end create_applicant;
1200 --
1201 --
1202 FUNCTION create_employee
1203 (p_effective_start_date DATE default null
1204 ,p_effective_end_date DATE default null
1205 ,p_business_group VARCHAR2
1206 ,p_last_name VARCHAR2
1207 ,p_national_identifier VARCHAR2
1208 ,p_employee_number VARCHAR2 default null
1209 ,p_tax_code VARCHAR2 default '50T'
1210 ,p_tax_basis VARCHAR2 default 'C' -- cumulative
1211 ,p_organization VARCHAR2 default null
1212 ,p_position VARCHAR2 default null
1213 ,p_job VARCHAR2 default null
1214 ,p_grade VARCHAR2 default null
1215 ,p_payroll VARCHAR2 default null
1216 ,p_location VARCHAR2 default null
1217 ,p_people_group_id NUMBER default null
1218 ,p_cost_allocation_keyflex_id NUMBER default null
1219 ,p_start_date DATE default null
1220 ,p_date_of_birth DATE default null
1221 ,p_employee_data_verified DATE default null
1222 ,p_expense_chk_send_to_address VARCHAR2 default 'H'
1223 ,p_first_name VARCHAR2 default null
1224 ,p_known_as VARCHAR2 default null
1225 ,p_marital_status VARCHAR2 default 'S'
1226 ,p_middle_names VARCHAR2 default null
1227 ,p_nationality VARCHAR2 default null
1228 ,p_previous_last_name VARCHAR2 default null
1229 ,p_registered_disabled_flag VARCHAR2 default 'N'
1230 ,p_sex VARCHAR2 default 'M'
1231 ,p_title VARCHAR2 default 'MR.'
1232 ,p_work_telephone VARCHAR2 default null
1233 ,p_frequency VARCHAR2 default 'W'
1234 ,p_normal_hours VARCHAR2 default '37.5'
1235 ,p_time_normal_start VARCHAR2 default '08:00'
1236 ,p_time_normal_finish VARCHAR2 default '17:30'
1237 ,p_probation_period VARCHAR2 default null
1238 ,p_probation_unit VARCHAR2 default null
1239 ,p_date_probation_end DATE default null
1240 ,p_manager_flag VARCHAR2 default 'N'
1241 ,p_supervisor_id NUMBER default null
1242 ,p_special_ceiling_step_id NUMBER default null
1243 ,p_internal_address_line VARCHAR2 default null
1244 ,p_change_reason VARCHAR2 default null)
1245 return NUMBER
1246 IS
1247 --
1248 -- local variables
1249 --
1250 l_business_group_id NUMBER;
1251 l_person_type_id NUMBER;
1252 l_person_id NUMBER;
1253 l_full_name VARCHAR2(240);
1254 l_current_applicant_flag VARCHAR2(1);
1255 l_current_employee_flag VARCHAR2(1);
1256 l_current_emp_or_apl_flag VARCHAR2(1);
1257 l_effective_start_date DATE;
1258 l_effective_end_date DATE;
1259 l_date_of_birth DATE;
1260 l_period_of_service_id NUMBER;
1261 l_assignment_id NUMBER;
1262 l_assignment_status_type_id NUMBER;
1263 --
1264 l_job_id NUMBER := null;
1265 l_position_id NUMBER := null;
1266 l_grade_id NUMBER := null;
1267 l_organization_id NUMBER := null;
1268 l_payroll_id NUMBER := null;
1269 l_location_id NUMBER := null;
1270 l_supervisor_id NUMBER := null;
1271 l_special_ceiling_step_id NUMBER := null; -- not yet validated
1272 l_cost_allocation_keyflex_id NUMBER;
1273 l_cost_allocation_structure NUMBER;
1274 l_people_group_id NUMBER;
1275 l_people_group_structure NUMBER;
1276 --
1277 --
1278 begin -- FUNCTION create_employee
1279 --
1280 --
1281 hr_utility.set_location('per_db_per_additional.create_employee',1);
1282 --
1283 SELECT business_group_id
1284 , people_group_structure
1285 , cost_allocation_structure
1286 INTO l_business_group_id
1287 , l_people_group_structure
1288 , l_cost_allocation_structure
1289 FROM per_business_groups
1290 WHERE name = p_business_group;
1291 --
1292 hr_utility.set_location('per_db_per_additional.create_employee',2);
1293 --
1294 SELECT person_type_id
1295 INTO l_person_type_id
1296 FROM per_person_types
1297 WHERE business_group_id = l_business_group_id --Bug fix 3648477
1298 AND system_person_type = 'EMP'
1299 AND default_flag = 'Y';
1300 --
1301 hr_utility.set_location('per_db_per_additional.create_employee',4);
1302 SELECT assignment_status_type_id
1303 INTO l_assignment_status_type_id
1304 FROM per_ass_status_type_amends
1305 WHERE business_group_id + 0 = l_business_group_id
1306 AND default_flag = 'Y'
1307 AND per_system_status = 'ACTIVE_ASSIGN'
1308 UNION
1309 SELECT ast.assignment_status_type_id
1310 FROM per_assignment_status_types ast
1311 WHERE nvl(ast.business_group_id,l_business_group_id)
1312 = l_business_group_id
1313 AND ast.default_flag = 'Y'
1314 AND ast.per_system_status = 'ACTIVE_ASSIGN'
1315 AND NOT EXISTS (SELECT null
1316 FROM per_ass_status_type_amends ast1
1317 WHERE ast1.business_group_id + 0 = l_business_group_id
1318 AND ast1.default_flag = 'Y'
1319 AND ast1.assignment_status_type_id =
1320 ast.assignment_status_type_id) ;
1321 --
1322 hr_utility.set_location('per_db_per_additional.create_employee',6);
1323 l_current_employee_flag := 'Y';
1324 l_current_applicant_flag := null;
1325 l_current_emp_or_apl_flag := 'Y';
1326 l_effective_start_date := nvl(p_effective_start_date, trunc(SYSDATE));
1327 l_effective_end_date := nvl(p_effective_end_date,
1328 to_date('4712/12/31','YYYY/MM/DD'));
1329 l_date_of_birth := nvl(p_date_of_birth,
1330 to_date('1958/01/01','YYYY/MM/DD'));
1331 --
1332 hr_utility.set_location('per_db_per_additional.create_employee',8);
1333 --
1334 l_person_id := insert_person(l_effective_start_date
1335 ,l_effective_end_date
1336 ,l_business_group_id
1337 ,l_person_type_id
1338 ,p_last_name
1339 ,null
1340 ,l_current_applicant_flag
1341 ,l_current_employee_flag
1342 ,l_current_emp_or_apl_flag
1343 ,p_employee_data_verified
1344 ,l_date_of_birth
1345 ,p_employee_number
1346 ,p_expense_chk_send_to_address
1347 ,p_first_name
1348 ,p_known_as
1349 ,p_marital_status
1350 ,p_middle_names
1351 ,p_nationality
1352 ,p_national_identifier
1353 ,p_previous_last_name
1354 ,p_registered_disabled_flag
1355 ,p_sex
1356 ,p_title
1357 ,p_work_telephone );
1358 --
1359 --
1360 -- PTU : Following code added for PTU
1361
1362 hr_per_type_usage_internal.maintain_person_type_usage
1363 ( p_effective_date => l_effective_start_date
1364 ,p_person_id => l_person_id
1365 ,p_person_type_id => l_person_type_id
1366 );
1367
1368 -- End of PTU Changes
1369 --
1370 hr_utility.set_location('per_db_per_additional.create_employee',10);
1371 l_period_of_service_id := insert_period_of_service(l_person_id
1372 ,l_business_group_id
1373 ,l_effective_start_date
1374 ,null
1375 ,null
1376 ,null
1377 ,null
1378 ,null
1379 ,null
1380 ,null
1381 ,null);
1382 --
1383 hr_utility.set_location('per_db_per_additional.create_employee',12);
1384 -- Validate position before using
1385 --
1386 -- WWBUG 1833930, changed hr_locations to hr_locations_all
1387 --
1388 if p_location IS NULL then null;
1389 else
1390 hr_utility.set_location('per_db_per_additional.create_employee',14);
1391 SELECT location_id
1392 INTO l_location_id
1393 FROM hr_locations_all
1394 WHERE location_code = p_location
1395 AND l_effective_start_date <= nvl(inactive_date,to_date('4712/12/31',
1396 'YYYY/MM/DD'));
1397 end if;
1398 --
1399 hr_utility.set_location('per_db_per_additional.create_employee',16);
1400 if p_position IS NULL then -- position overrides job and organization
1401 --
1402 if p_organization IS NULL then
1403 hr_utility.set_location('per_db_per_additional.create_employee',18);
1404 l_organization_id := l_business_group_id;
1405 else hr_utility.set_location('per_db_per_additional.create_employee',20);
1406 SELECT organization_id
1407 , nvl(location_id, l_location_id)
1408 INTO l_organization_id
1409 , l_location_id
1410 FROM hr_organization_units
1411 WHERE name = p_organization
1412 AND business_group_id + 0 = l_business_group_id
1413 AND l_effective_start_date BETWEEN date_from
1414 AND nvl(date_to,to_date('4712/12/31',
1415 'YYYY/MM/DD'));
1416 end if;
1417 --
1418 hr_utility.set_location('per_db_per_additional.create_employee',22);
1419 if p_job IS NULL then null;
1420 else hr_utility.set_location('per_db_per_additional.create_employee',24);
1421 SELECT job_id
1422 INTO l_job_id
1423 FROM per_jobs_v
1424 WHERE name = p_job
1425 AND business_group_id + 0 = l_business_group_id
1426 AND l_effective_start_date BETWEEN date_from
1427 AND nvl(date_to,to_date('4712/12/31',
1428 'YYYY/MM/DD'));
1429 end if;
1430 --
1431 else hr_utility.set_location('per_db_per_additional.create_employee',26);
1432 SELECT pos.position_id
1433 , pos.job_id
1434 , pos.organization_id
1435 , nvl(pos.location_id,nvl(org.location_id,l_location_id))
1436 INTO l_position_id
1437 , l_job_id
1438 , l_organization_id
1439 , l_location_id
1440 FROM per_organization_units org
1441 , per_positions pos
1442 WHERE pos.name = p_position
1443 AND pos.business_group_id + 0 = l_business_group_id
1444 AND pos.organization_id = org.organization_id
1445 AND l_effective_start_date BETWEEN pos.date_effective
1446 AND nvl(pos.date_end,to_date('4712/12/31',
1447 'YYYY/MM/DD'));
1448 end if;
1449 --
1450 hr_utility.set_location('per_db_per_additional.create_employee',28);
1451 if p_grade IS NULL then null;
1452 else
1453 hr_utility.set_location('per_db_per_additional.create_employee',30);
1454 SELECT grade_id
1455 INTO l_grade_id
1456 FROM per_grades_vl
1457 WHERE name = p_grade
1458 AND business_group_id + 0 = l_business_group_id
1459 AND l_effective_start_date BETWEEN date_from
1460 AND nvl(date_to,to_date('4712/12/31',
1461 'YYYY/MM/DD'));
1462 end if;
1463 --
1464 hr_utility.set_location('per_db_per_additional.create_employee',32);
1465 if p_payroll IS NULL then null;
1466 else
1467 hr_utility.set_location('per_db_per_additional.create_employee',34);
1468 SELECT pa.payroll_id
1469 INTO l_payroll_id
1470 FROM pay_payrolls_f pa
1471 WHERE pa.payroll_name = p_payroll
1472 AND pa.business_group_id + 0 = l_business_group_id
1473 AND l_effective_start_date BETWEEN pa.effective_start_date
1474 AND pa.effective_end_date
1475 AND l_effective_end_date <= (SELECT max(pa1.effective_end_date)
1476 FROM pay_payrolls_f pa1
1477 WHERE pa1.business_group_id + 0 =
1478 l_business_group_id
1479 AND pa1.payroll_id = pa.payroll_id) ;
1480 end if;
1481 --
1482 hr_utility.set_location('per_db_per_additional.create_employee',36);
1483 if p_supervisor_id IS NULL then null;
1484 else
1485 SELECT person_id
1486 INTO l_supervisor_id
1487 FROM per_people_f
1488 WHERE (business_group_id = l_business_group_id OR
1489 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
1490 AND person_id = p_supervisor_id
1491 AND current_employee_flag = 'Y'
1492 AND l_effective_start_date BETWEEN effective_start_date
1493 AND effective_end_date;
1494 end if;
1495 --
1496 hr_utility.set_location('per_db_per_additional.create_employee',38);
1497 if p_cost_allocation_keyflex_id IS NULL then
1498 l_cost_allocation_keyflex_id := null;
1499 else
1500 hr_utility.set_location('per_db_per_additional.create_employee',40);
1501 SELECT cost_allocation_keyflex_id
1502 INTO l_cost_allocation_keyflex_id
1503 FROM pay_cost_allocation_keyflex
1504 WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
1505 AND id_flex_num = l_cost_allocation_structure;
1506 end if;
1507 --
1508 hr_utility.set_location('per_db_per_additional.create_employee',42);
1509 if p_people_group_id IS NULL then
1510 l_people_group_id := null;
1511 else
1512 hr_utility.set_location('per_db_per_additional.create_employee',44);
1513 SELECT people_group_id
1514 INTO l_people_group_id
1515 FROM pay_people_groups
1516 WHERE people_group_id = p_people_group_id
1517 AND id_flex_num = l_people_group_structure;
1518 end if;
1519 --
1520 --
1521 hr_utility.set_location('per_db_per_additional.create_employee',46);
1522 l_assignment_id := insert_assignment(l_effective_start_date
1523 , l_effective_end_date
1524 , l_business_group_id
1525 , l_person_id
1526 , 'E'
1527 , l_organization_id
1528 , l_grade_id
1529 , l_job_id
1530 , l_position_id
1531 , l_payroll_id
1532 , l_location_id
1533 , l_cost_allocation_keyflex_id
1534 , l_people_group_id
1535 , null
1536 , l_assignment_status_type_id
1537 , 'Y'
1538 , p_manager_flag
1539 , p_change_reason
1540 , p_date_probation_end
1541 , p_frequency
1542 , p_internal_address_line
1543 , p_normal_hours
1544 , p_probation_period
1545 , p_probation_unit
1546 , null
1547 , l_special_ceiling_step_id
1548 , l_supervisor_id
1549 , null
1550 , null
1551 , null
1552 , p_time_normal_finish
1553 , p_time_normal_start);
1554 --
1555 --
1556 return l_person_id;
1557 --
1558 --
1559 --
1560 end create_employee;
1561 --
1562 --
1563 FUNCTION create_other
1564 (p_effective_start_date DATE default null
1565 ,p_effective_end_date DATE default null
1566 ,p_business_group VARCHAR2
1567 ,p_last_name VARCHAR2
1568 ,p_date_of_birth DATE default null
1569 ,p_expense_chk_send_to_address VARCHAR2 default 'H'
1570 ,p_first_name VARCHAR2 default null
1571 ,p_known_as VARCHAR2 default null
1572 ,p_marital_status VARCHAR2 default 'S'
1573 ,p_middle_names VARCHAR2 default null
1574 ,p_nationality VARCHAR2 default null
1575 ,p_national_identifier VARCHAR2
1576 ,p_previous_last_name VARCHAR2 default null
1577 ,p_registered_disabled_flag VARCHAR2 default 'N'
1578 ,p_sex VARCHAR2 default null
1579 ,p_title VARCHAR2 default null
1580 ,p_work_telephone VARCHAR2 default null)
1581 return NUMBER
1582 IS
1583 --
1584 -- local variables
1585 --
1586 l_business_group_id NUMBER;
1587 l_person_type_id NUMBER;
1588 l_person_id NUMBER;
1589 l_current_applicant_flag VARCHAR2(1);
1590 l_current_employee_flag VARCHAR2(1);
1591 l_current_emp_or_apl_flag VARCHAR2(1);
1592 l_effective_start_date DATE;
1593 l_effective_end_date DATE;
1594 --
1595 --
1596 --
1597 begin -- FUNCTION create_other
1598 --
1599 --
1600 hr_utility.set_location('per_db_per_additional.create_other',1);
1601 --
1602 SELECT business_group_id
1603 INTO l_business_group_id
1604 FROM per_business_groups
1605 WHERE name = p_business_group;
1606 --
1607 hr_utility.set_location('per_db_per_additional.create_other',2);
1608 --
1609 SELECT person_type_id
1610 INTO l_person_type_id
1611 FROM per_person_types
1612 WHERE business_group_id = l_business_group_id --Bug fix 3648477
1613 AND system_person_type = 'OTHER'
1614 AND default_flag = 'Y';
1615 --
1616 l_current_employee_flag := null;
1617 l_current_applicant_flag := null;
1618 l_current_emp_or_apl_flag := null;
1619 l_effective_start_date := nvl(p_effective_start_date,
1620 trunc(SYSDATE));
1621 l_effective_end_date := nvl(p_effective_end_date,
1622 to_date('4712/12/31','YYYY/MM/DD'));
1623 --
1624 hr_utility.set_location('per_db_per_additional.create_other',3);
1625 --
1626 l_person_id := insert_person(l_effective_start_date
1627 ,l_effective_end_date
1628 ,l_business_group_id
1629 ,l_person_type_id
1630 ,p_last_name
1631 ,null
1632 ,l_current_applicant_flag
1633 ,l_current_employee_flag
1634 ,l_current_emp_or_apl_flag
1635 ,null
1636 ,p_date_of_birth
1637 ,null
1638 ,p_expense_chk_send_to_address
1639 ,p_first_name
1640 ,p_known_as
1641 ,p_marital_status
1642 ,p_middle_names
1643 ,p_nationality
1644 ,p_national_identifier
1645 ,p_previous_last_name
1646 ,p_registered_disabled_flag
1647 ,p_sex
1648 ,p_title
1649 ,p_work_telephone );
1650 --
1651 --
1652 -- PTU : Following code added for PTU
1653
1654 hr_per_type_usage_internal.maintain_person_type_usage
1655 ( p_effective_date => l_effective_start_date
1656 ,p_person_id => l_person_id
1657 ,p_person_type_id => l_person_type_id
1658 );
1659
1660 -- End of PTU Changes
1661 --
1662 --
1663 return l_person_id;
1664 --
1665 --
1666 --
1667 end create_other;
1668 --
1669 --
1670 FUNCTION create_secondary_assign
1671 (p_effective_start_date DATE DEFAULT null
1672 ,p_effective_end_date DATE DEFAULT null
1673 ,p_business_group VARCHAR2
1674 ,p_person_id NUMBER
1675 ,p_assignment_type VARCHAR2
1676 ,p_organization VARCHAR2 DEFAULT null
1677 ,p_grade VARCHAR2 DEFAULT null
1678 ,p_job VARCHAR2 DEFAULT null
1679 ,p_position VARCHAR2 DEFAULT null
1680 ,p_payroll VARCHAR2 DEFAULT null
1681 ,p_location VARCHAR2 DEFAULT null
1682 ,p_vacancy VARCHAR2 DEFAULT null
1683 ,p_people_group_id NUMBER DEFAULT null
1684 ,p_cost_allocation_keyflex_id NUMBER DEFAULT null
1685 ,p_manager_flag VARCHAR2 DEFAULT null
1686 ,p_change_reason VARCHAR2 DEFAULT null
1687 ,p_date_probation_end DATE DEFAULT null
1688 ,p_frequency VARCHAR2 DEFAULT 'W'
1689 ,p_internal_address_line VARCHAR2 DEFAULT null
1690 ,p_normal_hours VARCHAR2 DEFAULT '37.5'
1691 ,p_probation_period VARCHAR2 DEFAULT null
1692 ,p_probation_unit VARCHAR2 DEFAULT null
1693 ,p_recruiter_id NUMBER DEFAULT null
1694 ,p_special_ceiling_step_id NUMBER DEFAULT null
1695 ,p_supervisor_id NUMBER DEFAULT null
1696 ,p_recruitment_activity_id NUMBER DEFAULT null
1697 ,p_person_referred_by_id NUMBER DEFAULT null
1698 ,p_source_organization_id NUMBER DEFAULT null
1699 ,p_time_normal_finish VARCHAR2 DEFAULT '08:00'
1700 ,p_time_normal_start VARCHAR2 DEFAULT '17:30')
1701 return NUMBER IS
1702 --
1703 l_type_check VARCHAR2(1) := null;
1704 l_assignment_id NUMBER;
1705 l_assignment_status_type_id NUMBER;
1706 l_effective_start_date DATE;
1707 l_effective_end_date DATE;
1708 l_business_group_id NUMBER;
1709 l_organization_id NUMBER;
1710 l_grade_id NUMBER;
1711 l_job_id NUMBER;
1712 l_position_id NUMBER;
1713 l_payroll_id NUMBER;
1714 l_location_id NUMBER;
1715 l_vacancy_id NUMBER;
1716 l_people_group_id NUMBER;
1717 l_recruiter_id NUMBER;
1718 l_supervisor_id NUMBER;
1719 l_cost_allocation_structure NUMBER;
1720 l_cost_allocation_keyflex_id NUMBER;
1721 l_people_group_structure NUMBER;
1722 l_recruitment_activity_id NUMBER;
1723 l_person_referred_by_id NUMBER;
1724 l_source_organization_id NUMBER;
1725 --
1726 begin
1727 --
1728 --
1729 hr_utility.set_location('per_db_per_additional.create_secondary_assign',1);
1730 SELECT business_group_id
1731 , cost_allocation_structure
1732 , people_group_structure
1733 INTO l_business_group_id
1734 , l_cost_allocation_structure
1735 , l_people_group_structure
1736 FROM per_business_groups
1737 WHERE name = p_business_group;
1738 --
1739 hr_utility.set_location('per_db_per_additional.create_secondary_assign',2);
1740 l_effective_start_date := nvl(p_effective_start_date,trunc(SYSDATE));
1741 l_effective_end_date := nvl(p_effective_end_date,
1742 to_date('4712/12/31','YYYY/MM/DD'));
1743 --
1744 hr_utility.set_location('per_db_per_additional.create_secondary_assign',3);
1745 SELECT 'Y'
1746 INTO l_type_check
1747 FROM per_people_f pp
1748 WHERE pp.person_id = p_person_id
1749 AND l_effective_start_date BETWEEN pp.effective_start_date
1750 AND pp.effective_end_date
1751 AND ((p_assignment_type = 'E'
1752 AND pp.current_employee_flag = 'Y')
1753 OR (p_assignment_type = 'A'
1754 AND pp.current_applicant_flag = 'Y'));
1755 --
1756 --
1757 if p_assignment_type = 'E' then -- Employee
1758 hr_utility.set_location('per_db_per_additional.create_secondary_assign',4);
1759 SELECT assignment_status_type_id
1760 INTO l_assignment_status_type_id
1761 FROM per_ass_status_type_amends
1762 WHERE business_group_id + 0 = l_business_group_id
1763 AND default_flag = 'Y'
1764 AND per_system_status = 'ACTIVE_ASSIGN'
1765 UNION
1766 SELECT ast.assignment_status_type_id
1767 FROM per_assignment_status_types ast
1768 WHERE nvl(ast.business_group_id,l_business_group_id)
1769 = l_business_group_id
1770 AND ast.default_flag = 'Y'
1771 AND ast.per_system_status = 'ACTIVE_ASSIGN'
1772 AND NOT EXISTS (SELECT null
1773 FROM per_ass_status_type_amends ast1
1774 WHERE ast1.business_group_id + 0 = l_business_group_id
1775 AND ast1.default_flag = 'Y'
1776 AND ast1.assignment_status_type_id =
1777 ast.assignment_status_type_id) ;
1778 --
1779 else -- Applicant
1780 hr_utility.set_location('per_db_per_additional.create_secondary_assign',7);
1781 SELECT assignment_status_type_id
1782 INTO l_assignment_status_type_id
1783 FROM per_ass_status_type_amends
1784 WHERE business_group_id + 0 = l_business_group_id
1785 AND default_flag = 'Y'
1786 AND per_system_status = 'ACTIVE_APL'
1787 UNION
1788 SELECT ast.assignment_status_type_id
1789 FROM per_assignment_status_types ast
1790 WHERE nvl(ast.business_group_id,l_business_group_id)
1791 = l_business_group_id
1792 AND ast.default_flag = 'Y'
1793 AND ast.per_system_status = 'ACTIVE_APL'
1794 AND NOT EXISTS (SELECT null
1795 FROM per_ass_status_type_amends ast1
1796 WHERE ast1.business_group_id + 0 = l_business_group_id
1797 AND ast1.default_flag = 'Y'
1798 AND ast1.assignment_status_type_id =
1799 ast.assignment_status_type_id) ;
1800 --
1801 end if;
1802 --
1803 --
1804 if p_assignment_type = 'E' then
1805 --
1806 hr_utility.set_location('per_db_per_additional.create_secondary_assign',9);
1807 -- Validate position before using
1808 --
1809 -- WWBUG 1833930, changed hr_locations to hr_locations_all
1810 --
1811 if p_location IS NULL then null;
1812 else
1813 hr_utility.set_location('per_db_per_additional.create_secondary_assign',11);
1814 SELECT location_id
1815 INTO l_location_id
1816 FROM hr_locations_all
1817 WHERE location_code = p_location
1818 AND l_effective_start_date <= nvl(inactive_date,to_date('4712/12/31',
1819 'YYYY/MM/DD'));
1820 end if;
1821 --
1822 if p_position IS NULL then -- position overrides job and organization
1823 --
1824 if p_organization IS NULL then
1825 hr_utility.set_location('per_db_per_additional.create_secondary_assign',13);
1826 l_organization_id := l_business_group_id;
1827 else
1828 hr_utility.set_location('per_db_per_additional.create_secondary_assign',15);
1829 SELECT organization_id
1830 , nvl(location_id, l_location_id)
1831 INTO l_organization_id
1832 , l_location_id
1833 FROM hr_organization_units
1834 WHERE name = p_organization
1835 AND business_group_id + 0 = l_business_group_id
1836 AND l_effective_start_date BETWEEN date_from
1837 AND nvl(date_to,to_date('4712/12/31',
1838 'YYYY/MM/DD'));
1839 end if;
1840 --
1841 hr_utility.set_location('per_db_per_additional.create_secondary_assign',17);
1842 if p_job IS NULL then null;
1843 else
1844 hr_utility.set_location('per_db_per_additional.create_secondary_assign',19);
1845 SELECT job_id
1846 INTO l_job_id
1847 FROM per_jobs_v
1848 WHERE name = p_job
1849 AND business_group_id + 0 = l_business_group_id
1850 AND l_effective_start_date BETWEEN date_from
1851 AND nvl(date_to,to_date('4712/12/31',
1852 'YYYY/MM/DD'));
1853 end if;
1854 --
1855 else
1856 hr_utility.set_location('per_db_per_additional.create_secondary_assign',21);
1857 SELECT pos.position_id
1858 , pos.job_id
1859 , pos.organization_id
1860 , nvl(pos.location_id,nvl(org.location_id,l_location_id))
1861 INTO l_position_id
1862 , l_job_id
1863 , l_organization_id
1864 , l_location_id
1865 FROM per_organization_units org
1866 , per_positions pos
1867 WHERE pos.name = p_position
1868 AND pos.business_group_id + 0 = l_business_group_id
1869 AND pos.organization_id = org.organization_id
1870 AND l_effective_start_date BETWEEN pos.date_effective
1871 AND nvl(pos.date_end,to_date('4712/12/31',
1872 'YYYY/MM/DD'));
1873 end if;
1874 --
1875 hr_utility.set_location('per_db_per_additional.create_secondary_assign',23);
1876 if p_grade IS NULL then null;
1877 else
1878 hr_utility.set_location('per_db_per_additional.create_secondary_assign',25);
1879 SELECT grade_id
1880 INTO l_grade_id
1881 FROM per_grades_vl
1882 WHERE name = p_grade
1883 AND business_group_id + 0 = l_business_group_id
1884 AND l_effective_start_date BETWEEN date_from
1885 AND nvl(date_to,to_date('4712/12/31',
1886 'YYYY/MM/DD'));
1887 end if;
1888 --
1889 hr_utility.set_location('per_db_per_additional.create_secondary_assign',27);
1890 if p_payroll IS NULL then null;
1891 else
1892 hr_utility.set_location('per_db_per_additional.create_secondary_assign',29);
1893 SELECT pa.payroll_id
1894 INTO l_payroll_id
1895 FROM pay_payrolls_f pa
1896 WHERE pa.payroll_name = p_payroll
1897 AND pa.business_group_id + 0 = l_business_group_id
1898 AND l_effective_start_date BETWEEN pa.effective_start_date
1899 AND pa.effective_end_date
1900 AND l_effective_end_date <= (SELECT max(pa1.effective_end_date)
1901 FROM pay_payrolls_f pa1
1902 WHERE pa1.business_group_id + 0 =
1903 l_business_group_id
1904 AND pa1.payroll_id = pa.payroll_id) ;
1905 end if;
1906 --
1907 hr_utility.set_location('per_db_per_additional.create_secondary_assign',31);
1908 if p_supervisor_id IS NULL then null;
1909 else
1910 SELECT person_id
1911 INTO l_supervisor_id
1912 FROM per_people_f
1913 WHERE business_group_id + 0 = l_business_group_id
1914 AND person_id = p_supervisor_id
1915 AND current_employee_flag = 'Y'
1916 AND l_effective_start_date BETWEEN effective_start_date
1917 AND effective_end_date;
1918 end if;
1919 --
1920 --
1921 hr_utility.set_location('per_db_per_additional.create_secondary_assign',33);
1922 if p_cost_allocation_keyflex_id IS NULL then
1923 l_cost_allocation_keyflex_id := null;
1924 else
1925 hr_utility.set_location('per_db_per_additional.create_secondary_assign',35);
1926 SELECT cost_allocation_keyflex_id
1927 INTO l_cost_allocation_keyflex_id
1928 FROM pay_cost_allocation_keyflex
1929 WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
1930 AND id_flex_num = l_cost_allocation_structure;
1931 end if;
1932 --
1933 hr_utility.set_location('per_db_per_additional.create_secondary_assign',37);
1934 if p_people_group_id IS NULL then
1935 l_people_group_id := null;
1936 else
1937 hr_utility.set_location('per_db_per_additional.create_secondary_assign',39);
1938 SELECT people_group_id
1939 INTO l_people_group_id
1940 FROM pay_people_groups
1941 WHERE people_group_id = p_people_group_id
1942 AND id_flex_num = l_people_group_structure;
1943 end if;
1944 --
1945 else -- if p_assignment_type = 'A'
1946 hr_utility.set_location('per_db_per_additional.create_secondary_assign',41);
1947 --
1948 -- WWBUG 1833930, changed hr_locations to hr_locations_all
1949 --
1950 if p_vacancy IS NULL then
1951 --
1952 if p_location IS NULL then null;
1953 else
1954 hr_utility.set_location('per_db_per_additional.create_secondary_assign',43);
1955 SELECT location_id
1956 INTO l_location_id
1957 FROM hr_locations_all
1958 WHERE location_code = p_location
1959 AND l_effective_start_date <= nvl(inactive_date,to_date('4712/12/31',
1960 'YYYY/MM/DD'));
1961 end if;
1962 --
1963 --
1964 hr_utility.set_location('per_db_per_additional.create_secondary_assign',44);
1965 if p_position IS NULL then -- job and organization
1966 --
1967 if p_organization IS NULL then
1968 l_organization_id := l_business_group_id;
1969 else
1970 hr_utility.set_location('per_db_per_additional.create_secondary_assign',46);
1971 SELECT organization_id
1972 , location_id
1973 INTO l_organization_id
1974 , l_location_id
1975 FROM hr_organization_units
1976 WHERE name = p_organization
1977 AND business_group_id + 0 = l_business_group_id
1978 AND l_effective_start_date BETWEEN date_from
1979 AND nvl(date_to,to_date('4712/12/31',
1980 'YYYY/MM/DD'));
1981 end if;
1982 --
1983 if p_job IS NULL then null;
1984 else
1985 hr_utility.set_location('per_db_per_additional.create_secondary_assign',48);
1986 SELECT job_id
1987 INTO l_job_id
1988 FROM per_jobs_v
1989 WHERE name = p_job
1990 AND business_group_id + 0 = l_business_group_id
1991 AND l_effective_start_date BETWEEN date_from
1992 AND nvl(date_to,to_date('4712/12/31',
1993 'YYYY/MM/DD'));
1994 end if;
1995 --
1996 else -- p_position is not null (position overrides job and org)
1997 hr_utility.set_location('per_db_per_additional.create_secondary_assign',50);
1998 SELECT pos.position_id
1999 , pos.job_id
2000 , pos.organization_id
2001 , nvl(pos.location_id,nvl(org.location_id,l_location_id))
2002 INTO l_position_id
2003 , l_job_id
2004 , l_organization_id
2005 , l_location_id
2006 FROM per_organization_units org
2007 , per_positions pos
2008 WHERE pos.name = p_position
2009 AND pos.business_group_id + 0 = l_business_group_id
2010 AND pos.organization_id = org.organization_id
2011 AND l_effective_start_date BETWEEN pos.date_effective
2012 AND nvl(pos.date_end,to_date('4712/12/31',
2013 'YYYY/MM/DD'));
2014 end if;
2015 --
2016 if p_grade IS NULL then null;
2017 else
2018 hr_utility.set_location('per_db_per_additional.create_secondary_assign',52);
2019 SELECT grade_id
2020 INTO l_grade_id
2021 FROM per_grades_vl
2022 WHERE name = p_grade
2023 AND business_group_id + 0 = l_business_group_id
2024 AND l_effective_start_date BETWEEN date_from
2025 AND nvl(date_to,to_date('4712/12/31',
2026 'YYYY/MM/DD'));
2027 end if;
2028 --
2029 else -- p_vacancy is not null (vacancy overrides all other columns)
2030 --
2031 hr_utility.set_location('per_db_per_additional.create_secondary_assign',54);
2032 SELECT vacancy_id
2033 , position_id
2034 , job_id
2035 , organization_id
2036 , grade_id
2037 , people_group_id
2038 , location_id
2039 INTO l_vacancy_id
2040 , l_position_id
2041 , l_job_id
2042 , l_organization_id
2043 , l_grade_id
2044 , l_people_group_id
2045 , l_location_id
2046 FROM per_vacancies
2047 WHERE name = p_vacancy
2048 AND l_effective_start_date BETWEEN date_from
2049 AND nvl(date_to,to_date('4712/12/31','YYYY/MM/DD'));
2050 --
2051 hr_utility.set_location('per_db_per_additional.create_secondary_assign',56);
2052 if l_organization_id IS NULL then
2053 l_organization_id := l_business_group_id; -- mandatory column
2054 end if;
2055 --
2056 hr_utility.set_location('per_db_per_additional.create_secondary_assign',58);
2057 if p_recruitment_activity_id IS NULL then
2058 l_recruitment_activity_id := null;
2059 else
2060 hr_utility.set_location('per_db_per_additional.create_secondary_assign',60);
2061 SELECT recruitment_activity_id
2062 INTO l_recruitment_activity_id
2063 FROM per_recruitment_activity_for
2064 WHERE business_group_id + 0 = l_business_group_id
2065 AND vacancy_id = l_vacancy_id;
2066 end if;
2067 --
2068 end if; -- vacancy not null within applicant
2069 --
2070 hr_utility.set_location('per_db_per_additional.create_secondary_assign',62);
2071 if p_source_organization_id IS NULL then null;
2072 else
2073 SELECT organization_id
2074 INTO l_organization_id
2075 FROM per_organization_units
2076 WHERE organization_id = p_source_organization_id
2077 AND business_group_id + 0 = l_business_group_id
2078 AND l_effective_start_date BETWEEN date_from
2079 AND nvl(date_to,to_date('4712/12/31','YYYY/MM/DD'));
2080 end if;
2081 --
2082 hr_utility.set_location('per_db_per_additional.create_secondary_assign',64);
2083 if p_recruiter_id IS NULL then null;
2084 else
2085 SELECT person_id
2086 INTO l_recruiter_id
2087 FROM per_people_f
2088 WHERE (business_group_id = l_business_group_id or
2089 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'Y')
2090 AND person_id = p_recruiter_id
2091 AND current_employee_flag = 'Y'
2092 AND l_effective_start_date BETWEEN effective_start_date
2093 AND effective_end_date;
2094 end if;
2095 --
2096 hr_utility.set_location('per_db_per_additional.create_secondary_assign',66);
2097 if p_person_referred_by_id IS NULL then null;
2098 else
2099 SELECT person_id
2100 INTO l_person_referred_by_id
2101 FROM per_people_f
2102 WHERE (business_group_id = l_business_group_id or
2103 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'Y')
2104 AND person_id = p_person_referred_by_id
2105 AND current_employee_flag = 'Y'
2106 AND l_effective_start_date BETWEEN effective_start_date
2107 AND effective_end_date;
2108 end if;
2109 --
2110 end if; -- split by assignment type
2111 --
2112 hr_utility.set_location('per_db_per_additional.create_secondary_assign',68);
2113 if p_people_group_id IS NULL then
2114 l_people_group_id := null;
2115 else
2116 hr_utility.set_location('per_db_per_additional.create_secondary_assign',70);
2117 SELECT people_group_id
2118 INTO l_people_group_id
2119 FROM pay_people_groups
2120 WHERE people_group_id = p_people_group_id
2121 AND id_flex_num = l_people_group_structure;
2122 end if;
2123 --
2124 hr_utility.set_location('per_db_per_additional.create_secondary_assign',72);
2125 l_assignment_id := insert_assignment(l_effective_start_date
2126 ,l_effective_end_date
2127 ,l_business_group_id
2128 ,p_person_id
2129 ,p_assignment_type
2130 ,l_organization_id
2131 ,l_grade_id
2132 ,l_job_id
2133 ,l_position_id
2134 ,l_payroll_id
2135 ,l_location_id
2136 ,l_vacancy_id
2137 ,l_people_group_id
2138 ,l_cost_allocation_keyflex_id
2139 ,l_assignment_status_type_id
2140 ,'N'
2141 ,p_manager_flag
2142 ,p_change_reason
2143 ,p_date_probation_end
2144 ,p_frequency
2145 ,p_internal_address_line
2146 ,p_normal_hours
2147 ,p_probation_period
2148 ,p_probation_unit
2149 ,l_recruiter_id
2150 ,p_special_ceiling_step_id
2151 ,l_supervisor_id
2152 ,l_recruitment_activity_id
2153 ,l_person_referred_by_id
2154 ,l_source_organization_id
2155 ,p_time_normal_finish
2156 ,p_time_normal_start );
2157 --
2158 --
2159 return l_assignment_id;
2160 --
2161 end create_secondary_assign;
2162 --
2163 --
2164 FUNCTION create_contact
2165 (p_effective_start_date DATE default null
2166 ,p_effective_end_date DATE default null
2167 ,p_employee_number VARCHAR2
2168 ,p_contact_person_id VARCHAR2 default null
2169 ,p_relationship VARCHAR2 default null
2170 ,p_primary_flag VARCHAR2 default 'N'
2171 ,p_dependent_flag VARCHAR2 default 'N'
2172 ,p_business_group VARCHAR2
2173 ,p_last_name VARCHAR2
2174 ,p_date_of_birth DATE default null
2175 ,p_expense_chk_send_to_address VARCHAR2 default 'H'
2176 ,p_first_name VARCHAR2 default null
2177 ,p_known_as VARCHAR2 default null
2178 ,p_marital_status VARCHAR2 default 'S'
2179 ,p_middle_names VARCHAR2 default null
2180 ,p_nationality VARCHAR2 default null
2181 ,p_national_identifier VARCHAR2
2182 ,p_previous_last_name VARCHAR2 default null
2183 ,p_registered_disabled_flag VARCHAR2 default 'N'
2184 ,p_sex VARCHAR2 default null
2185 ,p_title VARCHAR2 default null
2186 ,p_work_telephone VARCHAR2 default null)
2187 return NUMBER
2188 IS
2189 --
2190 l_contact_person_id NUMBER;
2191 l_person_id NUMBER;
2192 l_business_group_id NUMBER;
2193 l_contact_relationship_id NUMBER;
2194 l_contact_type VARCHAR2(10);
2195 l_effective_start_date DATE;
2196 l_effective_end_date DATE;
2197 --
2198 --
2199 -- Start of Fix for WWBUG 1408379
2200 --
2201 l_old ben_con_ler.g_con_ler_rec;
2202 l_new ben_con_ler.g_con_ler_rec;
2203 --
2204 -- End of Fix for WWBUG 1408379
2205 --
2206 --
2207 begin -- function create_contact
2208 --
2209 --
2210 hr_utility.set_location('per_db_per_additional.create_contact',1);
2211 SELECT business_group_id
2212 INTO l_business_group_id
2213 FROM per_business_groups
2214 WHERE name = p_business_group;
2215 --
2216 l_effective_start_date := nvl(p_effective_start_date,trunc(SYSDATE));
2217 l_effective_end_date := nvl(p_effective_end_date,
2218 to_date('4712/12/31','YYYY/MM/DD'));
2219 --
2220 hr_utility.set_location('per_db_per_additional.create_contact',2);
2221 SELECT pp.person_id
2222 INTO l_person_id
2223 FROM per_periods_of_service pos
2224 , per_people_f pp
2225 WHERE pp.business_group_id + 0 = l_business_group_id
2226 AND pp.current_employee_flag = 'Y'
2227 AND pp.employee_number = p_employee_number
2228 AND pp.person_id = pos.person_id
2229 AND l_effective_start_date BETWEEN
2230 pp.effective_start_date AND pp.effective_end_date
2231 AND l_effective_start_date BETWEEN pos.date_start
2232 AND nvl(pos.actual_termination_date,to_date('4712/12/31','YYYY/MM/DD'))
2233 AND l_effective_end_date <=
2234 nvl(pos.actual_termination_date,to_date('4712/12/31','YYYY/MM/DD'));
2235 --
2236 --
2237 if p_contact_person_id IS NULL then
2238 hr_utility.set_location('per_db_per_additional.create_contact',3);
2239 l_contact_person_id := per_db_per_additional.create_other
2240 (p_effective_start_date
2241 ,p_effective_end_date
2242 ,p_business_group
2243 ,p_last_name
2244 ,p_date_of_birth
2245 ,p_expense_chk_send_to_address
2246 ,p_first_name
2247 ,p_known_as
2248 ,p_marital_status
2249 ,p_middle_names
2250 ,p_nationality
2251 ,p_national_identifier
2252 ,p_previous_last_name
2253 ,p_registered_disabled_flag
2254 ,p_sex
2255 ,p_title
2256 ,p_work_telephone);
2257 else
2258 hr_utility.set_location('per_db_per_additional.create_contact',4);
2259 SELECT DISTINCT person_id
2260 INTO l_contact_person_id
2261 FROM per_people_f
2262 WHERE business_group_id + 0 = l_business_group_id
2263 AND person_id = p_contact_person_id;
2264 end if;
2265 --
2266 if p_relationship IS NULL then
2267 hr_utility.set_location('per_db_per_additional.create_contact',5);
2268 l_contact_type := 'O';
2269 else
2270 hr_utility.set_location('per_db_per_additional.create_contact',6);
2271 SELECT lookup_code
2272 INTO l_contact_type
2273 FROM hr_lookups
2274 WHERE lookup_type = 'CONTACT'
2275 AND meaning = p_relationship;
2276 end if;
2277 --
2278 hr_utility.set_location('per_db_per_additional.create_contact',7);
2279 SELECT per_contact_relationships_s.nextval
2280 INTO l_contact_relationship_id
2281 FROM sys.dual;
2282 --
2283 --
2284 hr_utility.set_location('per_db_per_additional.create_contact',8);
2285 INSERT INTO per_contact_relationships
2286 (contact_relationship_id
2287 ,business_group_id
2288 ,person_id
2289 ,contact_person_id
2290 ,contact_type
2291 ,dependent_flag
2292 ,primary_contact_flag
2293 ,last_update_date
2294 ,last_updated_by
2295 ,last_update_login
2296 ,created_by
2297 ,creation_date)
2298 values
2299 (l_contact_relationship_id
2300 ,l_business_group_id
2301 ,l_person_id
2302 ,l_contact_person_id
2303 ,l_contact_type
2304 ,p_dependent_flag
2305 ,p_primary_flag
2306 ,SYSDATE
2307 ,0
2308 ,0
2309 ,0
2310 ,SYSDATE);
2311 --
2312 --
2313 -- Start of Fix for WWBUG 1408379
2314 --
2315 l_new.person_id := l_person_id;
2316 l_new.contact_person_id := l_contact_person_id;
2317 l_new.business_group_id := l_business_group_id;
2318 l_new.date_start := null;
2319 l_new.date_end := null;
2320 l_new.contact_type := l_contact_type;
2321 l_new.personal_flag := null;
2322 l_new.start_life_reason_id := null;
2323 l_new.end_life_reason_id := null;
2324 l_new.rltd_per_rsds_w_dsgntr_flag := null;
2325 l_new.contact_relationship_id := l_contact_relationship_id;
2326 --
2327 ben_con_ler.ler_chk(p_old => l_old,
2328 p_new => l_new,
2329 p_effective_date => l_effective_start_date);
2330 --
2331 -- End of Fix for WWBUG 1408379
2332 --
2333 return l_contact_person_id;
2334 --
2335 --
2336 end create_contact;
2337 --
2338 --
2339 end per_db_per_additional;