[Home] [Help]
PACKAGE BODY: APPS.PER_PER_INS
Source
1 Package Body per_per_ins as
2 /* $Header: peperrhi.pkb 120.20.12020000.5 2013/05/16 10:39:02 srannama ship $ */
3 -- -- ------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
6 --
7 g_package varchar2(33) := ' per_per_ins.'; -- Global package name
8 --
9 -- The following global variables are only to be used by
10 -- the set_base_key_value and pre_insert procedures.
11 --
12 g_person_id_i number default null;
13 --
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_person_id in number) is
20 --
21 l_proc varchar2(72) := g_package||'set_base_key_value';
22 --
23 Begin
24 hr_utility.set_location('Entering:'||l_proc, 10);
25 --
26 per_per_ins.g_person_id_i := p_person_id;
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 20);
29 End set_base_key_value;
30 --
31 --
32
33 -- ----------------------------------------------------------------------------
34 -- |----------------------------< dt_insert_dml >-----------------------------|
35 -- ----------------------------------------------------------------------------
36 -- {Start Of Comments}
37 --
38 -- Description:
39 -- This procedure controls the actual dml insert logic for datetrack. The
40 -- functions of this procedure are as follows:
41 -- 1) Get the object_version_number.
42 -- 2) To set the effective start and end dates to the corresponding
43 -- validation start and end dates. Also, the object version number
44 -- record attribute is set.
45 -- 3) To set and unset the g_api_dml status as required (as we are about to
46 -- perform dml).
47 -- 4) To insert the row into the schema with the derived effective start
48 -- and end dates and the object version number.
49 -- 5) To trap any constraint violations that may have occurred.
50 -- 6) To raise any other errors.
51 --
52 -- Pre Conditions:
53 -- This is an internal private procedure which must be called from the
54 -- insert_dml and pre_update (logic permitting) procedure and must have
55 -- all mandatory arguments set.
56 --
57 -- In Arguments:
58 -- A Pl/Sql record structre.
59 --
60 -- Post Success:
61 -- The specified row will be inserted into the schema.
62 --
63 -- Post Failure:
64 -- On the insert dml failure it is important to note that we always reset the
65 -- g_api_dml status to false.
66 -- If a check or unique integrity constraint violation is raised the
67 -- constraint_error procedure will be called.
68 -- If any other error is reported, the error will be raised after the
69 -- g_api_dml status is reset.
70 --
71 -- Developer Implementation Notes:
72 -- This is an internal datetrack maintenance procedure which should
73 -- not be modified in anyway.
74 --
75 -- Access Status:
76 -- Internal Table Handler Use Only.
77 --
78 -- {End Of Comments}
79 -- ----------------------------------------------------------------------------
80 Procedure dt_insert_dml
81 (p_rec in out nocopy per_per_shd.g_rec_type,
82 p_effective_date in date,
83 p_datetrack_mode in varchar2,
84 p_validation_start_date in date,
85 p_validation_end_date in date) is
86 --
87 -- Cursor to select 'old' created AOL who column values
88 --
89 Cursor C_Sel1 Is
90 select t.created_by,
91 t.creation_date
92 from per_all_people_f t
93 where t.person_id = p_rec.person_id
94 and t.effective_start_date =
95 per_per_shd.g_old_rec.effective_start_date
96 and t.effective_end_date = (p_validation_start_date - 1);
97 --
98 l_proc varchar2(72) := g_package||'dt_insert_dml';
99 l_created_by per_all_people_f.created_by%TYPE;
100 l_creation_date per_all_people_f.creation_date%TYPE;
101 l_last_update_date per_all_people_f.last_update_date%TYPE;
102 l_last_updated_by per_all_people_f.last_updated_by%TYPE;
103 l_last_update_login per_all_people_f.last_update_login%TYPE;
104 --
105 cursor c1 is
106 select *
107 from per_all_people_f
108 where person_id = p_rec.person_id
109 and p_effective_date
110 between effective_start_date
111 and effective_end_date;
112 --
113 l_person per_all_people_f%rowtype;
114 --
115 Begin
116 hr_utility.set_location('Entering:'||l_proc, 5);
117 --
118 -- Get the object version number for the insert
119 --
120 p_rec.object_version_number :=
121 dt_api.get_object_version_number
122 (p_base_table_name => 'per_all_people_f',
123 p_base_key_column => 'person_id',
124 p_base_key_value => p_rec.person_id);
125 --
126 -- Set the effective start and end dates to the corresponding
127 -- validation start and end dates
128 --
129 p_rec.effective_start_date := p_validation_start_date;
130 p_rec.effective_end_date := p_validation_end_date;
131 --
132 -- If the datetrack_mode is not INSERT then we must populate the WHO
133 -- columns with the 'old' creation values and 'new' updated values.
134 --
135 If (p_datetrack_mode <> 'INSERT') then
136 hr_utility.set_location(l_proc, 10);
137 --
138 -- Select the 'old' created values
139 --
140 Open C_Sel1;
141 Fetch C_Sel1 Into l_created_by, l_creation_date;
142 If C_Sel1%notfound Then
143 --
144 -- The previous 'old' created row has not been found. We need
145 -- to error as an internal datetrack problem exists.
146 --
147 Close C_Sel1;
148 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
149 hr_utility.set_message_token('PROCEDURE', l_proc);
150 hr_utility.set_message_token('STEP','10');
151 hr_utility.raise_error;
152 End If;
153 Close C_Sel1;
154 --
155 -- Set the AOL updated WHO values
156 --
157 l_last_update_date := sysdate;
158 l_last_updated_by := fnd_global.user_id;
159 l_last_update_login := fnd_global.login_id;
160 End If;
161 --
162 per_per_shd.g_api_dml := true; -- Set the api dml status
163 --
164 -- Insert the row into: per_all_people_f
165 --
166 insert into per_all_people_f
167 ( person_id,
168 effective_start_date,
169 effective_end_date,
170 business_group_id,
171 person_type_id,
172 last_name,
173 start_date,
174 applicant_number,
175 comment_id,
176 current_applicant_flag,
177 current_emp_or_apl_flag,
178 current_employee_flag,
179 date_employee_data_verified,
180 date_of_birth,
181 email_address,
182 employee_number,
183 expense_check_send_to_address,
184 first_name,
185 full_name,
186 known_as,
187 marital_status,
188 middle_names,
189 nationality,
190 national_identifier,
191 previous_last_name,
192 registered_disabled_flag,
193 sex,
194 title,
195 vendor_id,
196 -- work_telephone,
197 request_id,
198 program_application_id,
199 program_id,
200 program_update_date,
201 attribute_category,
202 attribute1,
203 attribute2,
204 attribute3,
205 attribute4,
206 attribute5,
207 attribute6,
208 attribute7,
209 attribute8,
210 attribute9,
211 attribute10,
212 attribute11,
213 attribute12,
214 attribute13,
215 attribute14,
216 attribute15,
217 attribute16,
218 attribute17,
219 attribute18,
220 attribute19,
221 attribute20,
222 attribute21,
223 attribute22,
224 attribute23,
225 attribute24,
226 attribute25,
227 attribute26,
228 attribute27,
229 attribute28,
230 attribute29,
231 attribute30,
232 per_information_category,
233 per_information1,
234 per_information2,
235 per_information3,
236 per_information4,
237 per_information5,
238 per_information6,
239 per_information7,
240 per_information8,
241 per_information9,
242 per_information10,
243 per_information11,
244 per_information12,
245 per_information13,
246 per_information14,
247 per_information15,
248 per_information16,
249 per_information17,
250 per_information18,
251 per_information19,
252 per_information20,
253 object_version_number,
254 suffix,
255 DATE_OF_DEATH,
256 BACKGROUND_CHECK_STATUS ,
257 BACKGROUND_DATE_CHECK ,
258 BLOOD_TYPE ,
259 CORRESPONDENCE_LANGUAGE ,
260 FAST_PATH_EMPLOYEE ,
261 FTE_CAPACITY ,
262 HOLD_APPLICANT_DATE_UNTIL ,
263 HONORS ,
264 INTERNAL_LOCATION ,
265 LAST_MEDICAL_TEST_BY ,
266 LAST_MEDICAL_TEST_DATE ,
267 MAILSTOP ,
268 OFFICE_NUMBER ,
269 ON_MILITARY_SERVICE ,
270 ORDER_NAME ,
271 PRE_NAME_ADJUNCT ,
272 PROJECTED_START_DATE ,
273 REHIRE_AUTHORIZOR ,
274 REHIRE_RECOMMENDATION ,
275 RESUME_EXISTS ,
276 RESUME_LAST_UPDATED ,
277 SECOND_PASSPORT_EXISTS ,
278 STUDENT_STATUS ,
279 WORK_SCHEDULE ,
280 PER_INFORMATION21 ,
281 PER_INFORMATION22 ,
282 PER_INFORMATION23 ,
283 PER_INFORMATION24 ,
284 PER_INFORMATION25 ,
285 PER_INFORMATION26 ,
286 PER_INFORMATION27 ,
287 PER_INFORMATION28 ,
288 PER_INFORMATION29 ,
289 PER_INFORMATION30 ,
290 REHIRE_REASON ,
291 benefit_group_id ,
292 receipt_of_death_cert_date ,
293 coord_ben_med_pln_no ,
294 coord_ben_no_cvg_flag ,
295 COORD_BEN_MED_EXT_ER,
296 COORD_BEN_MED_PL_NAME,
297 COORD_BEN_MED_INSR_CRR_NAME,
298 COORD_BEN_MED_INSR_CRR_IDENT,
299 COORD_BEN_MED_CVG_STRT_DT,
300 COORD_BEN_MED_CVG_END_DT,
301 uses_tobacco_flag ,
302 dpdnt_adoption_date ,
303 dpdnt_vlntry_svce_flag ,
304 original_date_of_hire ,
305 town_of_birth ,
306 region_of_birth ,
307 country_of_birth ,
308 global_person_id ,
309 party_id ,
310 npw_number,
311 current_npw_flag,
312 global_name,
313 local_name,
314 created_by,
315 creation_date,
316 last_update_date,
317 last_updated_by,
318 last_update_login
319 )
320 Values
321 ( p_rec.person_id,
322 p_rec.effective_start_date,
323 p_rec.effective_end_date,
324 p_rec.business_group_id,
325 p_rec.person_type_id,
326 p_rec.last_name,
327 p_rec.start_date,
328 p_rec.applicant_number,
329 p_rec.comment_id,
330 p_rec.current_applicant_flag,
331 p_rec.current_emp_or_apl_flag,
332 p_rec.current_employee_flag,
333 p_rec.date_employee_data_verified,
334 p_rec.date_of_birth,
335 p_rec.email_address,
336 p_rec.employee_number,
337 p_rec.expense_check_send_to_address,
338 p_rec.first_name,
339 p_rec.full_name,
340 p_rec.known_as,
341 p_rec.marital_status,
342 p_rec.middle_names,
343 p_rec.nationality,
344 p_rec.national_identifier,
345 p_rec.previous_last_name,
346 p_rec.registered_disabled_flag,
347 p_rec.sex,
348 p_rec.title,
349 p_rec.vendor_id,
350 -- p_rec.work_telephone,
351 p_rec.request_id,
352 p_rec.program_application_id,
353 p_rec.program_id,
354 p_rec.program_update_date,
355 p_rec.attribute_category,
356 p_rec.attribute1,
357 p_rec.attribute2,
358 p_rec.attribute3,
359 p_rec.attribute4,
360 p_rec.attribute5,
361 p_rec.attribute6,
362 p_rec.attribute7,
363 p_rec.attribute8,
364 p_rec.attribute9,
365 p_rec.attribute10,
366 p_rec.attribute11,
367 p_rec.attribute12,
368 p_rec.attribute13,
369 p_rec.attribute14,
370 p_rec.attribute15,
371 p_rec.attribute16,
372 p_rec.attribute17,
373 p_rec.attribute18,
374 p_rec.attribute19,
375 p_rec.attribute20,
376 p_rec.attribute21,
377 p_rec.attribute22,
378 p_rec.attribute23,
379 p_rec.attribute24,
380 p_rec.attribute25,
381 p_rec.attribute26,
382 p_rec.attribute27,
383 p_rec.attribute28,
384 p_rec.attribute29,
385 p_rec.attribute30,
386 p_rec.per_information_category,
387 p_rec.per_information1,
388 p_rec.per_information2,
389 p_rec.per_information3,
390 p_rec.per_information4,
391 p_rec.per_information5,
392 p_rec.per_information6,
393 p_rec.per_information7,
394 p_rec.per_information8,
395 p_rec.per_information9,
396 p_rec.per_information10,
397 p_rec.per_information11,
398 p_rec.per_information12,
399 p_rec.per_information13,
400 p_rec.per_information14,
401 p_rec.per_information15,
402 p_rec.per_information16,
403 p_rec.per_information17,
404 p_rec.per_information18,
405 p_rec.per_information19,
406 p_rec.per_information20,
407 p_rec.object_version_number,
408 p_rec.suffix,
409 p_rec.DATE_OF_DEATH ,
410 p_rec.BACKGROUND_CHECK_STATUS ,
411 p_rec.BACKGROUND_DATE_CHECK ,
412 p_rec.BLOOD_TYPE ,
413 p_rec.CORRESPONDENCE_LANGUAGE ,
414 p_rec.FAST_PATH_EMPLOYEE ,
415 p_rec.FTE_CAPACITY ,
416 p_rec.HOLD_APPLICANT_DATE_UNTIL ,
417 p_rec.HONORS ,
418 p_rec.INTERNAL_LOCATION ,
419 p_rec.LAST_MEDICAL_TEST_BY ,
420 p_rec.LAST_MEDICAL_TEST_DATE ,
421 p_rec.MAILSTOP ,
422 p_rec.OFFICE_NUMBER ,
423 p_rec.ON_MILITARY_SERVICE ,
424 p_rec.ORDER_NAME ,
425 p_rec.PRE_NAME_ADJUNCT ,
426 p_rec.PROJECTED_START_DATE ,
427 p_rec.REHIRE_AUTHORIZOR ,
428 p_rec.REHIRE_RECOMMENDATION ,
429 p_rec.RESUME_EXISTS ,
430 p_rec.RESUME_LAST_UPDATED ,
431 p_rec.SECOND_PASSPORT_EXISTS ,
432 p_rec.STUDENT_STATUS ,
433 p_rec.WORK_SCHEDULE ,
434 p_rec.PER_INFORMATION21 ,
435 p_rec.PER_INFORMATION22 ,
436 p_rec.PER_INFORMATION23 ,
437 p_rec.PER_INFORMATION24 ,
438 p_rec.PER_INFORMATION25 ,
439 p_rec.PER_INFORMATION26 ,
440 p_rec.PER_INFORMATION27 ,
441 p_rec.PER_INFORMATION28 ,
442 p_rec.PER_INFORMATION29 ,
443 p_rec.PER_INFORMATION30 ,
444 p_rec.REHIRE_REASON ,
445 p_rec.BENEFIT_GROUP_ID ,
446 p_rec.RECEIPT_OF_DEATH_CERT_DATE ,
447 p_rec.COORD_BEN_MED_PLN_NO ,
448 p_rec.COORD_BEN_NO_CVG_FLAG ,
449 p_rec.COORD_BEN_MED_EXT_ER,
450 p_rec.COORD_BEN_MED_PL_NAME,
451 p_rec.COORD_BEN_MED_INSR_CRR_NAME,
452 p_rec.COORD_BEN_MED_INSR_CRR_IDENT,
453 p_rec.COORD_BEN_MED_CVG_STRT_DT,
454 p_rec.COORD_BEN_MED_CVG_END_DT ,
455 p_rec.USES_TOBACCO_FLAG ,
456 p_rec.DPDNT_ADOPTION_DATE ,
457 p_rec.DPDNT_VLNTRY_SVCE_FLAG ,
458 p_rec.ORIGINAL_DATE_OF_HIRE ,
459 p_rec.town_of_birth ,
460 p_rec.region_of_birth ,
461 p_rec.country_of_birth ,
462 p_rec.global_person_id ,
463 p_rec.party_id ,
464 p_rec.npw_number,
465 p_rec.current_npw_flag,
466 p_rec.global_name,
467 p_rec.local_name,
468 l_created_by,
469 l_creation_date,
470 l_last_update_date,
471 l_last_updated_by,
472 l_last_update_login
473 );
474 --
475 hr_utility.set_location('Select last created record',10);
476 --
477 -- Now we need to get the personwho was created or updated and pass that
478 -- info to TCA for synchronization across business groups or possible
479 -- creation through TCA.
480 --
481 open c1;
482 --
483 fetch c1 into l_person;
484 --
485 close c1;
486 --
487 if p_datetrack_mode = 'INSERT' then
488 --
489 null;
490 /* commented out as part of the TCA unmerge enhancement
491 this is handled in per_person_type_usage_internal.maintain_person_type_usag
492 --
493 per_hrtca_merge.create_tca_person(p_rec => l_person);
494 --
495 hr_utility.set_location('Updating party id',10);
496 --
497 -- Now assign the resulting party id back to the record.
498 --
499 -- WWBUG 2450297.
500 --
501 update per_all_people_f
502 set party_id = l_person.party_id
503 where person_id = p_rec.person_id;
504 --
505 End of comment for tca unmerge
506 */
507 else
508 --
509 per_hrtca_merge.update_tca_person(p_rec => l_person);
510 --
511 hr_utility.set_location('Updating party id',10);
512 --
513 end if;
514 --
515 per_per_shd.g_api_dml := false; -- Unset the api dml status
516 hr_utility.set_location(' Leaving:'||l_proc, 15);
517 --
518 Exception
519 When hr_api.check_integrity_violated Then
520 -- A check constraint has been violated
521 per_per_shd.g_api_dml := false; -- Unset the api dml status
522 per_per_shd.constraint_error
523 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
524 When hr_api.unique_integrity_violated Then
525 -- Unique integrity has been violated
526 per_per_shd.g_api_dml := false; -- Unset the api dml status
527 per_per_shd.constraint_error
528 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
529 When Others Then
530 per_per_shd.g_api_dml := false; -- Unset the api dml status
531 Raise;
532 End dt_insert_dml;
533 --
534 -- ----------------------------------------------------------------------------
535 -- |------------------------------< insert_dml >------------------------------|
536 -- ----------------------------------------------------------------------------
537 Procedure insert_dml
538 (p_rec in out nocopy per_per_shd.g_rec_type,
539 p_effective_date in date,
540 p_datetrack_mode in varchar2,
541 p_validation_start_date in date,
542 p_validation_end_date in date) is
543 --
544 l_proc varchar2(72) := g_package||'insert_dml';
545 --
546 Begin
547 hr_utility.set_location('Entering:'||l_proc, 5);
548 --
549 dt_insert_dml(p_rec => p_rec,
550 p_effective_date => p_effective_date,
551 p_datetrack_mode => p_datetrack_mode,
552 p_validation_start_date => p_validation_start_date,
553 p_validation_end_date => p_validation_end_date);
554 --
555 hr_utility.set_location(' Leaving:'||l_proc, 10);
556 End insert_dml;
557 --
558 -- ----------------------------------------------------------------------------
559 -- |------------------------------< pre_insert >------------------------------|
560 -- ----------------------------------------------------------------------------
561 -- {Start Of Comments}
562 --
563 -- Description:
564 -- This private procedure contains any processing which is required before
565 -- the insert dml. Presently, if the entity has a corresponding primary
566 -- key which is maintained by an associating sequence, the primary key for
567 -- the entity will be populated with the next sequence value in
568 -- preparation for the insert dml.
569 -- Also, if comments are defined for this entity, the comments insert
570 -- logic will also be called, generating a comment_id if required.
571 --
572 -- Pre Conditions:
573 -- This is an internal procedure which is called from the ins procedure.
574 --
575 -- In Arguments:
576 -- A Pl/Sql record structre.
577 --
578 -- Post Success:
579 -- Processing continues.
580 --
581 -- Post Failure:
582 -- If an error has occurred, an error message and exception will be raised
583 -- but not handled.
584 --
585 -- Developer Implementation Notes:
586 -- Any pre-processing required before the insert dml is issued should be
587 -- coded within this procedure. As stated above, a good example is the
588 -- generation of a primary key number via a corresponding sequence.
589 -- It is important to note that any 3rd party maintenance should be reviewed
590 -- before placing in this procedure.
591 --
592 -- Access Status:
593 -- Internal table Handler Use Only.
594 --
595 -- {End Of Comments}
596 -- ----------------------------------------------------------------------------
597 Procedure pre_insert
598 (p_rec in out nocopy per_per_shd.g_rec_type,
599 p_effective_date in date,
600 p_datetrack_mode in varchar2,
601 p_validation_start_date in date,
602 p_validation_end_date in date) is
603 --
604 l_proc varchar2(72) := g_package||'pre_insert';
605 l_exists varchar2(1);
606 --
607 Cursor C_Sel1 is select per_people_s.nextval from sys.dual;
608 --
609 Cursor C_Sel2 is
610 Select null
611 from per_all_people_f
612 where person_id =
613 per_per_ins.g_person_id_i;
614 --
615 Begin
616 hr_utility.set_location('Entering:'||l_proc, 5);
617 --
618 --
619 -- Select the next sequence number
620 --
621 If (per_per_ins.g_person_id_i is not null)
622 then
623 --
624 -- Verify registered primary key values not already in use
625 --
626 Open C_Sel2;
627 Fetch C_Sel2 into l_exists;
628 If C_Sel2%found Then
629 Close C_Sel2;
630 --
631 -- The primary key values are already in use.
632 --
633 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
634 fnd_message.set_token('TABLE_NAME','PER_ALL_PEOPLE_F');
635 fnd_message.raise_error;
636 End If;
637 Close C_Sel2;
638 --
639 -- Use registered key values and clear globals
640 --
641 p_rec.person_id :=
642 per_per_ins.g_person_id_i;
643 per_per_ins.g_person_id_i := null;
644 Else
645 Open C_Sel1;
646 Fetch C_Sel1 Into p_rec.person_id;
647 Close C_Sel1;
648 End If;
649 --
650 --
651 -- Insert the comment text if comments exist
652 --
653 If (p_rec.comments is not null) then
654 hr_utility.set_location(l_proc,7);
655 hr_comm_api.ins(p_comment_id => p_rec.comment_id,
656 p_source_table_name => 'per_all_people_f',
657 p_comment_text => p_rec.comments);
658 End If;
659 hr_utility.set_location(' Leaving:'||l_proc, 10);
660 --
661 End pre_insert;
662 --
663 -- ----------------------------------------------------------------------------
664 -- |-----------------------------< post_insert >------------------------------|
665 -- ----------------------------------------------------------------------------
666 -- {Start Of Comments}
667 --
668 -- Description:
669 -- This private procedure contains any processing which is required after the
670 -- insert dml.
671 --
672 -- Pre Conditions:
673 -- This is an internal procedure which is called from the ins procedure.
674 --
675 -- In Arguments:
676 -- A Pl/Sql record structre.
677 --
678 -- Post Success:
679 -- Processing continues.
680 --
681 -- Post Failure:
682 -- If an error has occurred, an error message and exception will be raised
683 -- but not handled.
684 --
685 -- Developer Implementation Notes:
686 -- Any post-processing required after the insert dml is issued should be
687 -- coded within this procedure. It is important to note that any 3rd party
688 -- maintenance should be reviewed before placing in this procedure.
689 --
690 -- Access Status:
691 -- Internal Table Handler Use Only.
692 --
693 -- {End Of Comments}
694 -- ----------------------------------------------------------------------------
695 Procedure post_insert
696 (p_rec in per_per_shd.g_rec_type,
697 p_effective_date in date,
698 p_datetrack_mode in varchar2,
699 p_validation_start_date in date,
700 p_validation_end_date in date,
701 p_name_combination_warning in boolean,
702 p_dob_null_warning in boolean,
703 p_orig_hire_warning in boolean) is
704 --
705 l_proc varchar2(72) := g_package||'post_insert';
706 --
707 cursor l_per_cur is
708 select *
709 from per_all_people_f
710 where person_id = p_rec.person_id
711 and effective_start_date = p_rec.effective_start_date
712 and effective_end_date = p_rec.effective_end_date;
713 l_per_rec per_all_people_f%rowtype;
714 --
715 Begin
716 hr_utility.set_location('Entering:'||l_proc, 5);
717 --
718 ben_dt_trgr_handle.person
719 (p_rowid => null
720 ,p_business_group_id => p_rec.business_group_id
721 ,p_person_id => p_rec.person_id
722 ,p_effective_start_date => p_rec.effective_start_date
723 ,p_effective_end_date => p_rec.effective_end_date
724 ,p_date_of_birth => p_rec.date_of_birth
725 ,p_date_of_death => p_rec.date_of_death
726 ,p_marital_status => p_rec.marital_status
727 ,p_on_military_service => p_rec.on_military_service
728 ,p_registered_disabled_flag => p_rec.registered_disabled_flag
729 ,p_sex => p_rec.sex
730 ,p_student_status => p_rec.student_status
731 ,p_coord_ben_med_pln_no => p_rec.coord_ben_med_pln_no
732 ,p_coord_ben_no_cvg_flag => p_rec.coord_ben_no_cvg_flag
733 ,p_uses_tobacco_flag => p_rec.uses_tobacco_flag
734 ,p_benefit_group_id => p_rec.benefit_group_id
735 ,p_per_information10 => p_rec.per_information10
736 ,p_original_date_of_hire => p_rec.original_date_of_hire
737 ,p_dpdnt_vlntry_svce_flag => p_rec.dpdnt_vlntry_svce_flag
738 ,p_receipt_of_death_cert_date => p_rec.receipt_of_death_cert_date
739 ,p_attribute1 => p_rec.attribute1
740 ,p_attribute2 => p_rec.attribute2
741 ,p_attribute3 => p_rec.attribute3
742 ,p_attribute4 => p_rec.attribute4
743 ,p_attribute5 => p_rec.attribute5
744 ,p_attribute6 => p_rec.attribute6
745 ,p_attribute7 => p_rec.attribute7
746 ,p_attribute8 => p_rec.attribute8
747 ,p_attribute9 => p_rec.attribute9
748 ,p_attribute10 => p_rec.attribute10
749 ,p_attribute11 => p_rec.attribute11
750 ,p_attribute12 => p_rec.attribute12
751 ,p_attribute13 => p_rec.attribute13
752 ,p_attribute14 => p_rec.attribute14
753 ,p_attribute15 => p_rec.attribute15
754 ,p_attribute16 => p_rec.attribute16
755 ,p_attribute17 => p_rec.attribute17
756 ,p_attribute18 => p_rec.attribute18
757 ,p_attribute19 => p_rec.attribute19
758 ,p_attribute20 => p_rec.attribute20
759 ,p_attribute21 => p_rec.attribute21
760 ,p_attribute22 => p_rec.attribute22
761 ,p_attribute23 => p_rec.attribute23
762 ,p_attribute24 => p_rec.attribute24
763 ,p_attribute25 => p_rec.attribute25
764 ,p_attribute26 => p_rec.attribute26
765 ,p_attribute27 => p_rec.attribute27
766 ,p_attribute28 => p_rec.attribute28
767 ,p_attribute29 => p_rec.attribute29
768 ,p_attribute30 => p_rec.attribute30
769 );
770 -- Start of API User Hook for post_insert.
771 begin
772 per_per_rki.after_insert
773 (p_effective_date => p_effective_date
774 ,p_name_combination_warning => p_name_combination_warning
775 ,p_dob_null_warning => p_dob_null_warning
776 ,p_orig_hire_warning => p_orig_hire_warning
777 ,p_validation_start_date => p_validation_start_date
778 ,p_validation_end_date => p_validation_end_date
779 ,p_person_id => p_rec.person_id
780 ,p_effective_start_date => p_rec.effective_start_date
781 ,p_effective_end_date => p_rec.effective_end_date
782 ,p_business_group_id => p_rec.business_group_id
783 ,p_person_type_id => p_rec.person_type_id
784 ,p_last_name => p_rec.last_name
785 ,p_start_date => p_rec.start_date
786 ,p_applicant_number => p_rec.applicant_number
787 ,p_comment_id => p_rec.comment_id
788 ,p_current_applicant_flag => p_rec.current_applicant_flag
789 ,p_current_emp_or_apl_flag => p_rec.current_emp_or_apl_flag
790 ,p_current_employee_flag => p_rec.current_employee_flag
791 ,p_date_employee_data_verified => p_rec.date_employee_data_verified
792 ,p_date_of_birth => p_rec.date_of_birth
793 ,p_email_address => p_rec.email_address
794 ,p_employee_number => p_rec.employee_number
795 ,p_expense_check_send_to_addres => p_rec.expense_check_send_to_address
796 ,p_first_name => p_rec.first_name
797 ,p_full_name => p_rec.full_name
798 ,p_known_as => p_rec.known_as
799 ,p_marital_status => p_rec.marital_status
800 ,p_middle_names => p_rec.middle_names
801 ,p_nationality => p_rec.nationality
802 ,p_national_identifier => p_rec.national_identifier
803 ,p_previous_last_name => p_rec.previous_last_name
804 ,p_registered_disabled_flag => p_rec.registered_disabled_flag
805 ,p_sex => p_rec.sex
806 ,p_title => p_rec.title
807 ,p_vendor_id => p_rec.vendor_id
808 ,p_work_telephone => p_rec.work_telephone
809 ,p_request_id => p_rec.request_id
810 ,p_program_application_id => p_rec.program_application_id
811 ,p_program_id => p_rec.program_id
812 ,p_program_update_date => p_rec.program_update_date
813 ,p_attribute_category => p_rec.attribute_category
814 ,p_attribute1 => p_rec.attribute1
815 ,p_attribute2 => p_rec.attribute2
816 ,p_attribute3 => p_rec.attribute3
817 ,p_attribute4 => p_rec.attribute4
818 ,p_attribute5 => p_rec.attribute5
819 ,p_attribute6 => p_rec.attribute6
820 ,p_attribute7 => p_rec.attribute7
821 ,p_attribute8 => p_rec.attribute8
822 ,p_attribute9 => p_rec.attribute9
823 ,p_attribute10 => p_rec.attribute10
824 ,p_attribute11 => p_rec.attribute11
825 ,p_attribute12 => p_rec.attribute12
826 ,p_attribute13 => p_rec.attribute13
827 ,p_attribute14 => p_rec.attribute14
828 ,p_attribute15 => p_rec.attribute15
829 ,p_attribute16 => p_rec.attribute16
830 ,p_attribute17 => p_rec.attribute17
831 ,p_attribute18 => p_rec.attribute18
832 ,p_attribute19 => p_rec.attribute19
833 ,p_attribute20 => p_rec.attribute20
834 ,p_attribute21 => p_rec.attribute21
835 ,p_attribute22 => p_rec.attribute22
836 ,p_attribute23 => p_rec.attribute23
837 ,p_attribute24 => p_rec.attribute24
838 ,p_attribute25 => p_rec.attribute25
839 ,p_attribute26 => p_rec.attribute26
840 ,p_attribute27 => p_rec.attribute27
841 ,p_attribute28 => p_rec.attribute28
842 ,p_attribute29 => p_rec.attribute29
843 ,p_attribute30 => p_rec.attribute30
844 ,p_per_information_category => p_rec.per_information_category
845 ,p_per_information1 => p_rec.per_information1
846 ,p_per_information2 => p_rec.per_information2
847 ,p_per_information3 => p_rec.per_information3
848 ,p_per_information4 => p_rec.per_information4
849 ,p_per_information5 => p_rec.per_information5
850 ,p_per_information6 => p_rec.per_information6
851 ,p_per_information7 => p_rec.per_information7
852 ,p_per_information8 => p_rec.per_information8
853 ,p_per_information9 => p_rec.per_information9
854 ,p_per_information10 => p_rec.per_information10
855 ,p_per_information11 => p_rec.per_information11
856 ,p_per_information12 => p_rec.per_information12
857 ,p_per_information13 => p_rec.per_information13
858 ,p_per_information14 => p_rec.per_information14
859 ,p_per_information15 => p_rec.per_information15
860 ,p_per_information16 => p_rec.per_information16
861 ,p_per_information17 => p_rec.per_information17
862 ,p_per_information18 => p_rec.per_information18
863 ,p_per_information19 => p_rec.per_information19
864 ,p_per_information20 => p_rec.per_information20
865 ,p_suffix => p_rec.suffix
866 ,p_DATE_OF_DEATH => p_rec.DATE_OF_DEATH
867 ,p_BACKGROUND_CHECK_STATUS => p_rec.BACKGROUND_CHECK_STATUS
868 ,p_BACKGROUND_DATE_CHECK => p_rec.BACKGROUND_DATE_CHECK
869 ,p_BLOOD_TYPE => p_rec.BLOOD_TYPE
870 ,p_CORRESPONDENCE_LANGUAGE => p_rec.CORRESPONDENCE_LANGUAGE
871 ,p_FAST_PATH_EMPLOYEE => p_rec.FAST_PATH_EMPLOYEE
872 ,p_FTE_CAPACITY => p_rec.FTE_CAPACITY
873 ,p_HOLD_APPLICANT_DATE_UNTIL => p_rec.HOLD_APPLICANT_DATE_UNTIL
874 ,p_HONORS => p_rec.HONORS
875 ,p_INTERNAL_LOCATION => p_rec.INTERNAL_LOCATION
876 ,p_LAST_MEDICAL_TEST_BY => p_rec.LAST_MEDICAL_TEST_BY
877 ,p_LAST_MEDICAL_TEST_DATE => p_rec.LAST_MEDICAL_TEST_DATE
878 ,p_MAILSTOP => p_rec.MAILSTOP
879 ,p_OFFICE_NUMBER => p_rec.OFFICE_NUMBER
880 ,p_ON_MILITARY_SERVICE => p_rec.ON_MILITARY_SERVICE
881 ,p_ORDER_NAME => p_rec.ORDER_NAME
882 ,p_PRE_NAME_ADJUNCT => p_rec.PRE_NAME_ADJUNCT
883 ,p_PROJECTED_START_DATE => p_rec.PROJECTED_START_DATE
884 ,p_REHIRE_AUTHORIZOR => p_rec.REHIRE_AUTHORIZOR
885 ,p_REHIRE_RECOMMENDATION => p_rec.REHIRE_RECOMMENDATION
886 ,p_RESUME_EXISTS => p_rec.RESUME_EXISTS
887 ,p_RESUME_LAST_UPDATED => p_rec.RESUME_LAST_UPDATED
888 ,p_SECOND_PASSPORT_EXISTS => p_rec.SECOND_PASSPORT_EXISTS
889 ,p_STUDENT_STATUS => p_rec.STUDENT_STATUS
890 ,p_WORK_SCHEDULE => p_rec.WORK_SCHEDULE
891 ,p_PER_INFORMATION21 => p_rec.PER_INFORMATION21
892 ,p_PER_INFORMATION22 => p_rec.PER_INFORMATION22
893 ,p_PER_INFORMATION23 => p_rec.PER_INFORMATION23
894 ,p_PER_INFORMATION24 => p_rec.PER_INFORMATION24
895 ,p_PER_INFORMATION25 => p_rec.PER_INFORMATION25
896 ,p_PER_INFORMATION26 => p_rec.PER_INFORMATION26
897 ,p_PER_INFORMATION27 => p_rec.PER_INFORMATION27
898 ,p_PER_INFORMATION28 => p_rec.PER_INFORMATION28
899 ,p_PER_INFORMATION29 => p_rec.PER_INFORMATION29
900 ,p_PER_INFORMATION30 => p_rec.PER_INFORMATION30
901 ,p_REHIRE_REASON => p_rec.REHIRE_REASON
902 ,p_BENEFIT_GROUP_ID => p_rec.BENEFIT_GROUP_ID
903 ,p_RECEIPT_OF_DEATH_CERT_DATE => p_rec.RECEIPT_OF_DEATH_CERT_DATE
904 ,p_COORD_BEN_MED_PLN_NO => p_rec.COORD_BEN_MED_PLN_NO
905 ,p_COORD_BEN_NO_CVG_FLAG => p_rec.COORD_BEN_NO_CVG_FLAG
906 ,p_coord_ben_med_ext_er => p_rec.coord_ben_med_ext_er
907 ,p_coord_ben_med_pl_name => p_rec.coord_ben_med_pl_name
908 ,p_coord_ben_med_insr_crr_name => p_rec.coord_ben_med_insr_crr_name
909 ,p_coord_ben_med_insr_crr_ident => p_rec.coord_ben_med_insr_crr_ident
910 ,p_coord_ben_med_cvg_strt_dt => p_rec.coord_ben_med_cvg_strt_dt
911 ,p_coord_ben_med_cvg_end_dt => p_rec.coord_ben_med_cvg_end_dt
912 ,p_USES_TOBACCO_FLAG => p_rec.USES_TOBACCO_FLAG
913 ,p_DPDNT_ADOPTION_DATE => p_rec.DPDNT_ADOPTION_DATE
914 ,p_DPDNT_VLNTRY_SVCE_FLAG => p_rec.DPDNT_VLNTRY_SVCE_FLAG
915 ,p_ORIGINAL_DATE_OF_HIRE => p_rec.ORIGINAL_DATE_OF_HIRE
916 ,p_town_of_birth => p_rec.town_of_birth
917 ,p_region_of_birth => p_rec.region_of_birth
918 ,p_country_of_birth => p_rec.country_of_birth
919 ,p_global_person_id => p_rec.global_person_id
920 ,p_party_id => p_rec.party_id
921 ,p_npw_number => p_rec.npw_number
922 ,p_current_npw_flag => p_rec.current_npw_flag
923 ,p_global_name => p_rec.global_name
924 ,p_local_name => p_rec.local_name
925 ,p_object_version_number => p_rec.object_version_number
926 );
927 exception
928 when hr_api.cannot_find_prog_unit then
929 hr_api.cannot_find_prog_unit_error
930 (p_module_name => 'per_all_people_f'
931 ,p_hook_type => 'AI'
932 );
933 end;
934 -- End of API User Hook for post_insert.
935 --
936 --
937 --Start HR/WF Synchronization
938 --
939 open l_per_cur;
940 fetch l_per_cur into l_per_rec;
941 close l_per_cur;
942 --
943 per_hrwf_synch.per_per_wf(
944 p_rec => l_per_rec,
945 p_action => 'INSERT');
946 --
947 -- End HR/WF Synchronization
948 --
949 hr_utility.set_location(' Leaving:'||l_proc, 10);
950 End post_insert;
951 --
952 -- ----------------------------------------------------------------------------
953 -- |-------------------------------< ins_lck >--------------------------------|
954 -- ----------------------------------------------------------------------------
955 -- {Start Of Comments}
956 --
957 -- Description:
958 -- The ins_lck process has one main function to perform. When inserting
959 -- a datetracked row, we must validate the DT mode.
960 -- be manipulated.
961 --
962 -- Pre Conditions:
963 -- This procedure can only be called for the datetrack mode of INSERT.
964 --
965 -- In Arguments:
966 --
967 -- Post Success:
968 -- On successful completion of the ins_lck process the parental
969 -- datetracked rows will be locked providing the p_enforce_foreign_locking
970 -- argument value is TRUE.
971 -- If the p_enforce_foreign_locking argument value is FALSE then the
972 -- parential rows are not locked.
973 --
974 -- Post Failure:
975 -- The Lck process can fail for:
976 -- 1) When attempting to lock the row the row could already be locked by
977 -- another user. This will raise the HR_Api.Object_Locked exception.
978 -- 2) When attempting to the lock the parent which doesn't exist.
979 -- For the entity to be locked the parent must exist!
980 --
981 -- Developer Implementation Notes:
982 -- None.
983 --
984 -- Access Status:
985 -- Internal Table Handler Use Only.
986 --
987 -- {End Of Comments}
988 -- ----------------------------------------------------------------------------
989 Procedure ins_lck
990 (p_effective_date in date,
991 p_datetrack_mode in varchar2,
992 p_rec in per_per_shd.g_rec_type,
993 p_validation_start_date out nocopy date,
994 p_validation_end_date out nocopy date) is
995 --
996 l_proc varchar2(72) := g_package||'ins_lck';
997 l_validation_start_date date;
998 l_validation_end_date date;
999 --
1000 Begin
1001 hr_utility.set_location('Entering:'||l_proc, 5);
1002 --
1003 -- Validate the datetrack mode mode getting the validation start
1004 -- and end dates for the specified datetrack operation.
1005 --
1006 dt_api.validate_dt_mode
1007 (p_effective_date => p_effective_date,
1008 p_datetrack_mode => p_datetrack_mode,
1009 p_base_table_name => 'per_people_f',
1010 p_base_key_column => 'person_id',
1011 p_base_key_value => p_rec.person_id,
1012 p_enforce_foreign_locking => true,
1013 p_validation_start_date => l_validation_start_date,
1014 p_validation_end_date => l_validation_end_date);
1015 --
1016 -- Set the validation start and end date OUT arguments
1017 --
1018 p_validation_start_date := l_validation_start_date;
1019 p_validation_end_date := l_validation_end_date;
1020 --
1021 hr_utility.set_location(' Leaving:'||l_proc, 10);
1022 --
1023 End ins_lck;
1024 --
1025 -- ----------------------------------------------------------------------------
1026 -- |---------------------------------< ins >----------------------------------|
1027 -- ----------------------------------------------------------------------------
1028 Procedure ins
1029 (
1030 p_rec in out nocopy per_per_shd.g_rec_type,
1031 p_effective_date in date,
1032 p_validate in boolean default false,
1033 p_name_combination_warning out nocopy boolean,
1034 p_dob_null_warning out nocopy boolean,
1035 p_orig_hire_warning out nocopy boolean
1036 ) is
1037 --
1038 l_proc varchar2(72) := g_package||'ins';
1039 l_datetrack_mode varchar2(30) := 'INSERT';
1040 l_validation_start_date date;
1041 l_validation_end_date date;
1042 l_name_combination_warning boolean;
1043 l_dob_null_warning boolean;
1044 l_orig_hire_warning boolean;
1045 --
1046 cursor c1 is
1047 select *
1048 from per_all_people_f
1049 where party_id = p_rec.party_id
1050 and p_effective_date
1051 between effective_start_date
1052 and effective_end_date;
1053 --
1054 l_c1 c1%rowtype;
1055 --
1056 --
1057 Begin
1058 hr_utility.set_location('Entering:'||l_proc, 5);
1059 --
1060 -- Determine if the business process is to be validated.
1061 --
1062 -- For HR/TCA Merge we have to deafult certain parameters for cases where
1063 -- a duplicate party exists.
1064 --
1065 -- WWBUG 2059244.
1066 --
1067 if fnd_profile.value('HR_PROPAGATE_DATA_CHANGES') = 'Y' then
1068 --
1069 open c1;
1070 --
1071 fetch c1 into l_c1;
1072 if c1%found then
1073 --
1074 if p_rec.date_of_birth is null then
1075 p_rec.date_of_birth := l_c1.date_of_birth; --Fix For Bug 10330298 to_date(to_char(l_c1.date_of_birth),'DD/MM/RRRR');
1076 end if;
1077 if p_rec.first_name is null then
1078 p_rec.first_name := l_c1.first_name;
1079 end if;
1080 if p_rec.known_as is null then
1081 p_rec.known_as := l_c1.known_as;
1082 end if;
1083 if p_rec.marital_status is null then
1084 p_rec.marital_status := l_c1.marital_status;
1085 end if;
1086 if p_rec.middle_names is null then
1087 p_rec.middle_names := l_c1.middle_names;
1088 end if;
1089 if p_rec.nationality is null then
1090 p_rec.nationality := l_c1.nationality;
1091 end if;
1092 if p_rec.sex is null then
1093 p_rec.sex := l_c1.sex;
1094 end if;
1095 if p_rec.title is null then
1096 p_rec.title := l_c1.title;
1097 end if;
1098 if p_rec.blood_type is null then
1099 p_rec.blood_type := l_c1.blood_type;
1100 end if;
1101 if p_rec.correspondence_language is null then
1102 p_rec.correspondence_language := l_c1.correspondence_language;
1103 end if;
1104 if p_rec.honors is null then
1105 p_rec.honors := l_c1.honors;
1106 end if;
1107 if p_rec.pre_name_adjunct is null then
1108 p_rec.pre_name_adjunct := l_c1.pre_name_adjunct;
1109 end if;
1110 if p_rec.rehire_authorizor is null then
1111 p_rec.rehire_authorizor := l_c1.rehire_authorizor;
1112 end if;
1113 if p_rec.rehire_recommendation is null then
1114 p_rec.rehire_recommendation := l_c1.rehire_recommendation;
1115 end if;
1116 if p_rec.resume_exists is null then
1117 p_rec.resume_exists := l_c1.resume_exists;
1118 end if;
1119 if p_rec.resume_last_updated is null then
1120 p_rec.resume_last_updated := l_c1.resume_last_updated; --Fix For Bug # 10330298 to_date(to_char(l_c1.resume_last_updated),'DD/MM/RRRR');
1121 end if;
1122 if p_rec.second_passport_exists is null then
1123 p_rec.second_passport_exists := l_c1.second_passport_exists;
1124 end if;
1125 if p_rec.student_status is null then
1126 p_rec.student_status := l_c1.student_status;
1127 end if;
1128 if p_rec.suffix is null then
1129 p_rec.suffix := l_c1.suffix;
1130 end if;
1131 if p_rec.date_of_death is null then
1132 p_rec.date_of_death := l_c1.date_of_death; --Fix For Bug # 10330298 to_date(to_char(l_c1.date_of_death),'DD/MM/RRRR');
1133 end if;
1134 if p_rec.uses_tobacco_flag is null then
1135 p_rec.uses_tobacco_flag := l_c1.uses_tobacco_flag;
1136 end if;
1137 if p_rec.town_of_birth is null then
1138 p_rec.town_of_birth := l_c1.town_of_birth;
1139 end if;
1140 if p_rec.region_of_birth is null then
1141 p_rec.region_of_birth := l_c1.region_of_birth;
1142 end if;
1143 if p_rec.country_of_birth is null then
1144 p_rec.country_of_birth := l_c1.country_of_birth;
1145 end if;
1146 if p_rec.fast_path_employee is null then
1147 p_rec.fast_path_employee := l_c1.fast_path_employee;
1148 end if;
1149 if p_rec.email_address is null then
1150 p_rec.email_address := l_c1.email_address;
1151 end if;
1152 if p_rec.fte_capacity is null then
1153 p_rec.fte_capacity := l_c1.fte_capacity;
1154 end if;
1155 --
1156 end if;
1157 --
1158 close c1;
1159 --
1160 end if;
1161 --
1162 -- End of WWBUG 2059244.
1163 --
1164 If p_validate then
1165 --
1166 -- Issue the savepoint.
1167 --
1168 SAVEPOINT ins_per_per;
1169 End If;
1170 --
1171 -- Call the lock operation
1172 --
1173 ins_lck
1174 (p_effective_date => p_effective_date,
1175 p_datetrack_mode => l_datetrack_mode,
1176 p_rec => p_rec,
1177 p_validation_start_date => l_validation_start_date,
1178 p_validation_end_date => l_validation_end_date);
1179 --
1180 -- Call the supporting insert validate operations
1181 --
1182 per_per_bus.insert_validate
1183 (p_rec => p_rec,
1184 p_effective_date => p_effective_date,
1185 p_datetrack_mode => l_datetrack_mode,
1186 p_validation_start_date => l_validation_start_date,
1187 p_validation_end_date => l_validation_end_date,
1188 p_name_combination_warning => l_name_combination_warning,
1189 p_dob_null_warning => l_dob_null_warning,
1190 p_orig_hire_warning => l_orig_hire_warning);
1191 --
1192 hr_multi_message.end_validation_set;
1193 -- Call the supporting pre-insert operation
1194 --
1195 pre_insert
1196 (p_rec => p_rec,
1197 p_effective_date => p_effective_date,
1198 p_datetrack_mode => l_datetrack_mode,
1199 p_validation_start_date => l_validation_start_date,
1200 p_validation_end_date => l_validation_end_date);
1201 --
1202 -- Insert the row
1203 --
1204 insert_dml
1205 (p_rec => p_rec,
1206 p_effective_date => p_effective_date,
1207 p_datetrack_mode => l_datetrack_mode,
1208 p_validation_start_date => l_validation_start_date,
1209 p_validation_end_date => l_validation_end_date);
1210 --
1211 -- Call the supporting post-insert operation
1212 --
1213 post_insert
1214 (p_rec => p_rec,
1215 p_effective_date => p_effective_date,
1216 p_datetrack_mode => l_datetrack_mode,
1217 p_validation_start_date => l_validation_start_date,
1218 p_validation_end_date => l_validation_end_date,
1219 p_name_combination_warning
1220 => l_name_combination_warning,
1221 p_dob_null_warning => l_dob_null_warning,
1222 p_orig_hire_warning => l_orig_hire_warning);
1223 --
1224 -- If we are validating then raise the Validate_Enabled exception
1225 hr_multi_message.end_validation_set;
1226 --
1227 If p_validate then
1228 Raise HR_Api.Validate_Enabled;
1229 End If;
1230 --
1231 -- Set the out arguments
1232 --
1233 p_name_combination_warning := l_name_combination_warning;
1234 p_dob_null_warning := l_dob_null_warning;
1235 p_orig_hire_warning := l_orig_hire_warning;
1236 --
1237 if l_orig_hire_warning = FALSE then
1238 hr_utility.set_location(l_proc,997);
1239 elsif l_orig_hire_warning = TRUE then
1240 hr_utility.set_location(l_proc,998);
1241 else
1242 hr_utility.set_location(l_proc,999);
1243 end if;
1244 --
1245 hr_utility.set_location(' Leaving:'||l_proc, 10);
1246 Exception
1247 When HR_Api.Validate_Enabled Then
1248 --
1249 -- As the Validate_Enabled exception has been raised
1250 -- we must rollback to the savepoint
1251 --
1252 ROLLBACK TO ins_per_per;
1253 end ins;
1254 --
1255 -- ----------------------------------------------------------------------------
1256 -- |---------------------------------< ins >----------------------------------|
1257 -- ----------------------------------------------------------------------------
1258 Procedure ins
1259 (
1260 p_person_id out nocopy number,
1261 p_effective_start_date out nocopy date,
1262 p_effective_end_date out nocopy date,
1263 p_business_group_id in number,
1264 p_person_type_id in number,
1265 p_last_name in varchar2,
1266 p_start_date in date,
1267 p_applicant_number in out nocopy varchar2,
1268 p_comment_id out nocopy number,
1269 p_comments in varchar2 default null,
1270 p_current_applicant_flag out nocopy varchar2,
1271 p_current_emp_or_apl_flag out nocopy varchar2,
1272 p_current_employee_flag out nocopy varchar2,
1273 p_date_employee_data_verified in date default null,
1274 p_date_of_birth in date default null,
1275 p_email_address in varchar2 default null,
1276 p_employee_number in out nocopy varchar2,
1277 p_expense_check_send_to_addres in varchar2 default null,
1278 p_first_name in varchar2 default null,
1279 p_full_name out nocopy varchar2,
1280 p_known_as in varchar2 default null,
1281 p_marital_status in varchar2 default null,
1282 p_middle_names in varchar2 default null,
1283 p_nationality in varchar2 default null,
1284 p_national_identifier in varchar2 default null,
1285 p_previous_last_name in varchar2 default null,
1286 p_registered_disabled_flag in varchar2 default null,
1287 p_sex in varchar2 default null,
1288 p_title in varchar2 default null,
1289 p_vendor_id in number default null,
1290 p_work_telephone in varchar2 default null,
1291 p_request_id in number default null,
1292 p_program_application_id in number default null,
1293 p_program_id in number default null,
1294 p_program_update_date in date default null,
1295 p_attribute_category in varchar2 default null,
1296 p_attribute1 in varchar2 default null,
1297 p_attribute2 in varchar2 default null,
1298 p_attribute3 in varchar2 default null,
1299 p_attribute4 in varchar2 default null,
1300 p_attribute5 in varchar2 default null,
1301 p_attribute6 in varchar2 default null,
1302 p_attribute7 in varchar2 default null,
1303 p_attribute8 in varchar2 default null,
1304 p_attribute9 in varchar2 default null,
1305 p_attribute10 in varchar2 default null,
1306 p_attribute11 in varchar2 default null,
1307 p_attribute12 in varchar2 default null,
1308 p_attribute13 in varchar2 default null,
1309 p_attribute14 in varchar2 default null,
1310 p_attribute15 in varchar2 default null,
1311 p_attribute16 in varchar2 default null,
1312 p_attribute17 in varchar2 default null,
1313 p_attribute18 in varchar2 default null,
1314 p_attribute19 in varchar2 default null,
1315 p_attribute20 in varchar2 default null,
1316 p_attribute21 in varchar2 default null,
1317 p_attribute22 in varchar2 default null,
1318 p_attribute23 in varchar2 default null,
1319 p_attribute24 in varchar2 default null,
1320 p_attribute25 in varchar2 default null,
1321 p_attribute26 in varchar2 default null,
1322 p_attribute27 in varchar2 default null,
1323 p_attribute28 in varchar2 default null,
1324 p_attribute29 in varchar2 default null,
1325 p_attribute30 in varchar2 default null,
1326 p_per_information_category in varchar2 default null,
1327 p_per_information1 in varchar2 default null,
1328 p_per_information2 in varchar2 default null,
1329 p_per_information3 in varchar2 default null,
1330 p_per_information4 in varchar2 default null,
1331 p_per_information5 in varchar2 default null,
1332 p_per_information6 in varchar2 default null,
1333 p_per_information7 in varchar2 default null,
1334 p_per_information8 in varchar2 default null,
1335 p_per_information9 in varchar2 default null,
1336 p_per_information10 in varchar2 default null,
1337 p_per_information11 in varchar2 default null,
1338 p_per_information12 in varchar2 default null,
1339 p_per_information13 in varchar2 default null,
1340 p_per_information14 in varchar2 default null,
1341 p_per_information15 in varchar2 default null,
1342 p_per_information16 in varchar2 default null,
1343 p_per_information17 in varchar2 default null,
1344 p_per_information18 in varchar2 default null,
1345 p_per_information19 in varchar2 default null,
1346 p_per_information20 in varchar2 default null,
1347 p_suffix in varchar2 default null,
1348 p_DATE_OF_DEATH in date default null,
1349 p_BACKGROUND_CHECK_STATUS in varchar2 default null,
1350 p_BACKGROUND_DATE_CHECK in date default null,
1351 p_BLOOD_TYPE in varchar2 default null,
1352 p_CORRESPONDENCE_LANGUAGE in varchar2 default null,
1353 p_FAST_PATH_EMPLOYEE in varchar2 default null,
1354 p_FTE_CAPACITY in number default null,
1355 p_HOLD_APPLICANT_DATE_UNTIL in date default null,
1356 p_HONORS in varchar2 default null,
1357 p_INTERNAL_LOCATION in varchar2 default null,
1358 p_LAST_MEDICAL_TEST_BY in varchar2 default null,
1359 p_LAST_MEDICAL_TEST_DATE in date default null,
1360 p_MAILSTOP in varchar2 default null,
1361 p_OFFICE_NUMBER in varchar2 default null,
1362 p_ON_MILITARY_SERVICE in varchar2 default null,
1363 p_ORDER_NAME in varchar2 default null,
1364 p_PRE_NAME_ADJUNCT in varchar2 default null,
1365 p_PROJECTED_START_DATE in date default null,
1366 p_REHIRE_AUTHORIZOR in varchar2 default null,
1367 p_REHIRE_RECOMMENDATION in varchar2 default null,
1368 p_RESUME_EXISTS in varchar2 default null,
1369 p_RESUME_LAST_UPDATED in date default null,
1370 p_SECOND_PASSPORT_EXISTS in varchar2 default null,
1371 p_STUDENT_STATUS in varchar2 default null,
1372 p_WORK_SCHEDULE in varchar2 default null,
1373 p_PER_INFORMATION21 in varchar2 default null,
1374 p_PER_INFORMATION22 in varchar2 default null,
1375 p_PER_INFORMATION23 in varchar2 default null,
1376 p_PER_INFORMATION24 in varchar2 default null,
1377 p_PER_INFORMATION25 in varchar2 default null,
1378 p_PER_INFORMATION26 in varchar2 default null,
1379 p_PER_INFORMATION27 in varchar2 default null,
1380 p_PER_INFORMATION28 in varchar2 default null,
1381 p_PER_INFORMATION29 in varchar2 default null,
1382 p_PER_INFORMATION30 in varchar2 default null,
1383 p_REHIRE_REASON in varchar2 default null,
1384 p_benefit_group_id in number default null,
1385 p_receipt_of_death_cert_date in date default null,
1386 p_coord_ben_med_pln_no in varchar2 default null,
1387 p_coord_ben_no_cvg_flag in varchar2 default 'N',
1388 p_coord_ben_med_ext_er in varchar2 default null,
1389 p_coord_ben_med_pl_name in varchar2 default null,
1390 p_coord_ben_med_insr_crr_name in varchar2 default null,
1391 p_coord_ben_med_insr_crr_ident in varchar2 default null,
1392 p_coord_ben_med_cvg_strt_dt in date default null,
1393 p_coord_ben_med_cvg_end_dt in date default null,
1394 p_uses_tobacco_flag in varchar2 default null,
1395 p_dpdnt_adoption_date in date default null,
1396 p_dpdnt_vlntry_svce_flag in varchar2 default 'N',
1397 p_original_date_of_hire in date default null,
1398 p_town_of_birth in varchar2 default null,
1399 p_region_of_birth in varchar2 default null,
1400 p_country_of_birth in varchar2 default null,
1401 p_global_person_id in varchar2 default null,
1402 p_party_id in number default null,
1403 p_npw_number in out nocopy varchar2,
1404 p_current_npw_flag in varchar2 default null,
1405 p_object_version_number out nocopy number,
1406 p_effective_date in date,
1407 p_validate in boolean default false,
1408 p_name_combination_warning out nocopy boolean,
1409 p_dob_null_warning out nocopy boolean,
1410 p_orig_hire_warning out nocopy boolean
1411 )
1412 is
1413 --
1414 l_rec per_per_shd.g_rec_type;
1415 l_proc varchar2(72) := g_package||'ins';
1416 --
1417 Begin
1418 hr_utility.set_location('Entering:'||l_proc, 5);
1419 --
1420 -- Call conversion function to turn arguments into the
1421 -- p_rec structure.
1422 --
1423 l_rec :=
1424 per_per_shd.convert_args
1425 (
1426 null,
1427 null,
1428 null,
1429 p_business_group_id,
1430 p_person_type_id,
1431 p_last_name,
1432 p_start_date,
1433 p_applicant_number,
1434 null,
1435 p_comments,
1436 null,
1437 null,
1438 null,
1439 p_date_employee_data_verified,
1440 p_date_of_birth,
1441 p_email_address,
1442 p_employee_number,
1443 p_expense_check_send_to_addres,
1444 p_first_name,
1445 null,
1446 p_known_as,
1447 p_marital_status,
1448 p_middle_names,
1449 p_nationality,
1450 p_national_identifier,
1451 p_previous_last_name,
1452 p_registered_disabled_flag,
1453 p_sex,
1454 p_title,
1455 p_vendor_id,
1456 p_work_telephone,
1457 p_request_id,
1458 p_program_application_id,
1459 p_program_id,
1460 p_program_update_date,
1461 p_attribute_category,
1462 p_attribute1,
1463 p_attribute2,
1464 p_attribute3,
1465 p_attribute4,
1466 p_attribute5,
1467 p_attribute6,
1468 p_attribute7,
1469 p_attribute8,
1470 p_attribute9,
1471 p_attribute10,
1472 p_attribute11,
1473 p_attribute12,
1474 p_attribute13,
1475 p_attribute14,
1476 p_attribute15,
1477 p_attribute16,
1478 p_attribute17,
1479 p_attribute18,
1480 p_attribute19,
1481 p_attribute20,
1482 p_attribute21,
1483 p_attribute22,
1484 p_attribute23,
1485 p_attribute24,
1486 p_attribute25,
1487 p_attribute26,
1488 p_attribute27,
1489 p_attribute28,
1490 p_attribute29,
1491 p_attribute30,
1492 p_per_information_category,
1493 p_per_information1,
1494 p_per_information2,
1495 p_per_information3,
1496 p_per_information4,
1497 p_per_information5,
1498 p_per_information6,
1499 p_per_information7,
1500 p_per_information8,
1501 p_per_information9,
1502 p_per_information10,
1503 p_per_information11,
1504 p_per_information12,
1505 p_per_information13,
1506 p_per_information14,
1507 p_per_information15,
1508 p_per_information16,
1509 p_per_information17,
1510 p_per_information18,
1511 p_per_information19,
1512 p_per_information20,
1513 null,
1514 p_suffix,
1515 p_DATE_OF_DEATH ,
1516 p_BACKGROUND_CHECK_STATUS ,
1517 p_BACKGROUND_DATE_CHECK ,
1518 p_BLOOD_TYPE ,
1519 p_CORRESPONDENCE_LANGUAGE ,
1520 p_FAST_PATH_EMPLOYEE ,
1521 p_FTE_CAPACITY ,
1522 p_HOLD_APPLICANT_DATE_UNTIL ,
1523 p_HONORS ,
1524 p_INTERNAL_LOCATION ,
1525 p_LAST_MEDICAL_TEST_BY ,
1526 p_LAST_MEDICAL_TEST_DATE ,
1527 p_MAILSTOP ,
1528 p_OFFICE_NUMBER ,
1529 p_ON_MILITARY_SERVICE ,
1530 p_ORDER_NAME ,
1531 p_PRE_NAME_ADJUNCT ,
1532 p_PROJECTED_START_DATE ,
1533 p_REHIRE_AUTHORIZOR ,
1534 p_REHIRE_RECOMMENDATION ,
1535 p_RESUME_EXISTS ,
1536 p_RESUME_LAST_UPDATED ,
1537 p_SECOND_PASSPORT_EXISTS ,
1538 p_STUDENT_STATUS ,
1539 p_WORK_SCHEDULE ,
1540 p_PER_INFORMATION21 ,
1541 p_PER_INFORMATION22 ,
1542 p_PER_INFORMATION23 ,
1543 p_PER_INFORMATION24 ,
1544 p_PER_INFORMATION25 ,
1545 p_PER_INFORMATION26 ,
1546 p_PER_INFORMATION27 ,
1547 p_PER_INFORMATION28 ,
1548 p_PER_INFORMATION29 ,
1549 p_PER_INFORMATION30 ,
1550 p_REHIRE_REASON ,
1551 p_BENEFIT_GROUP_ID ,
1552 p_RECEIPT_OF_DEATH_CERT_DATE ,
1553 p_COORD_BEN_MED_PLN_NO ,
1554 p_COORD_BEN_NO_CVG_FLAG ,
1555 p_coord_ben_med_ext_er ,
1556 p_coord_ben_med_pl_name ,
1557 p_coord_ben_med_insr_crr_name ,
1558 p_coord_ben_med_insr_crr_ident ,
1559 p_coord_ben_med_cvg_strt_dt ,
1560 p_coord_ben_med_cvg_end_dt ,
1561 p_USES_TOBACCO_FLAG ,
1562 p_DPDNT_ADOPTION_DATE ,
1563 p_DPDNT_VLNTRY_SVCE_FLAG ,
1564 p_ORIGINAL_DATE_OF_HIRE ,
1565 p_town_of_birth,
1566 p_region_of_birth,
1567 p_country_of_birth,
1568 p_global_person_id,
1569 p_party_id,
1570 p_npw_number,
1571 p_current_npw_flag,
1572 null, -- global_name
1573 null -- local_name
1574 );
1575 --
1576 -- Having converted the arguments into the per_per_rec
1577 -- plsql record structure we call the corresponding record
1578 -- business process.
1579 --
1580 ins(l_rec, p_effective_date, p_validate, p_name_combination_warning,
1581 p_dob_null_warning, p_orig_hire_warning
1582 );
1583 --
1584 -- Set the OUT arguments.
1585 --
1586 p_person_id := l_rec.person_id;
1587 p_effective_start_date := l_rec.effective_start_date;
1588 p_effective_end_date := l_rec.effective_end_date;
1589 p_employee_number := l_rec.employee_number;
1590 p_applicant_number := l_rec.applicant_number;
1591 p_object_version_number := l_rec.object_version_number;
1592 p_comment_id := l_rec.comment_id;
1593 p_current_applicant_flag := l_rec.current_applicant_flag;
1594 p_current_emp_or_apl_flag := l_rec.current_emp_or_apl_flag;
1595 p_current_employee_flag := l_rec.current_employee_flag;
1596 p_full_name := l_rec.full_name;
1597 p_npw_number := l_rec.npw_number;
1598 --
1599 hr_utility.set_location(' Leaving:'||l_proc, 10);
1600 End ins;
1601 --
1602 end per_per_ins;