[Home] [Help]
PACKAGE BODY: APPS.PER_PEOPLE11_PKG
Source
1 PACKAGE BODY PER_PEOPLE11_PKG AS
2 /* $Header: peper11t.pkb 120.2 2006/01/26 22:52:10 ghshanka noship $ */
3 --
4 procedure insert_row1(p_rowid in out nocopy VARCHAR2
5 ,p_person_id in out nocopy NUMBER
6 ,p_effective_start_date DATE
7 ,p_effective_end_date DATE
8 ,p_business_group_id NUMBER
9 ,p_person_type_id NUMBER
10 ,p_last_name VARCHAR2
11 ,p_start_date DATE
12 ,p_applicant_number IN OUT NOCOPY VARCHAR2
13 ,p_comment_id NUMBER
14 ,p_current_applicant_flag VARCHAR2
15 ,p_current_emp_or_apl_flag VARCHAR2
16 ,p_current_employee_flag VARCHAR2
17 ,p_date_employee_data_verified DATE
18 ,p_date_of_birth DATE
19 ,p_email_address VARCHAR2
20 ,p_employee_number IN OUT NOCOPY VARCHAR2
21 ,p_expense_check_send_to_addr VARCHAR2
22 ,p_first_name VARCHAR2
23 ,p_full_name VARCHAR2
24 ,p_known_as VARCHAR2
25 ,p_marital_status VARCHAR2
26 ,p_middle_names VARCHAR2
27 ,p_nationality VARCHAR2
28 ,p_national_identifier VARCHAR2
29 ,p_previous_last_name VARCHAR2
30 ,p_registered_disabled_flag VARCHAR2
31 ,p_sex VARCHAR2
32 ,p_title VARCHAR2
33 ,p_suffix VARCHAR2
34 ,p_vendor_id NUMBER
35 ,p_work_telephone VARCHAR2
36 ,p_request_id NUMBER
37 ,p_program_application_id NUMBER
38 ,p_program_id NUMBER
39 ,p_program_update_date DATE
40 ,p_a_cat VARCHAR2
41 ,p_a1 VARCHAR2
42 ,p_a2 VARCHAR2
43 ,p_a3 VARCHAR2
44 ,p_a4 VARCHAR2
45 ,p_a5 VARCHAR2
46 ,p_a6 VARCHAR2
47 ,p_a7 VARCHAR2
48 ,p_a8 VARCHAR2
49 ,p_a9 VARCHAR2
50 ,p_a10 VARCHAR2
51 ,p_a11 VARCHAR2
52 ,p_a12 VARCHAR2
53 ,p_a13 VARCHAR2
54 ,p_a14 VARCHAR2
55 ,p_a15 VARCHAR2
56 ,p_a16 VARCHAR2
57 ,p_a17 VARCHAR2
58 ,p_a18 VARCHAR2
59 ,p_a19 VARCHAR2
60 ,p_a20 VARCHAR2
61 ,p_a21 VARCHAR2
62 ,p_a22 VARCHAR2
63 ,p_a23 VARCHAR2
64 ,p_a24 VARCHAR2
65 ,p_a25 VARCHAR2
66 ,p_a26 VARCHAR2
67 ,p_a27 VARCHAR2
68 ,p_a28 VARCHAR2
69 ,p_a29 VARCHAR2
70 ,p_a30 VARCHAR2
71 ,p_last_update_date DATE
72 ,p_last_updated_by NUMBER
73 ,p_last_update_login NUMBER
74 ,p_created_by NUMBER
75 ,p_creation_date DATE
76 ,p_i_cat VARCHAR2
77 ,p_i1 VARCHAR2
78 ,p_i2 VARCHAR2
79 ,p_i3 VARCHAR2
80 ,p_i4 VARCHAR2
81 ,p_i5 VARCHAR2
82 ,p_i6 VARCHAR2
83 ,p_i7 VARCHAR2
84 ,p_i8 VARCHAR2
85 ,p_i9 VARCHAR2
86 ,p_i10 VARCHAR2
87 ,p_i11 VARCHAR2
88 ,p_i12 VARCHAR2
89 ,p_i13 VARCHAR2
90 ,p_i14 VARCHAR2
91 ,p_i15 VARCHAR2
92 ,p_i16 VARCHAR2
93 ,p_i17 VARCHAR2
94 ,p_i18 VARCHAR2
95 ,p_i19 VARCHAR2
96 ,p_i20 VARCHAR2
97 ,p_i21 VARCHAR2
98 ,p_i22 VARCHAR2
99 ,p_i23 VARCHAR2
100 ,p_i24 VARCHAR2
101 ,p_i25 VARCHAR2
102 ,p_i26 VARCHAR2
103 ,p_i27 VARCHAR2
104 ,p_i28 VARCHAR2
105 ,p_i29 VARCHAR2
106 ,p_i30 VARCHAR2
107 ,p_app_ass_status_type_id NUMBER
108 ,p_emp_ass_status_type_id NUMBER
109 ,p_create_defaults_for VARCHAR2
110 ,p_work_schedule VARCHAR2
111 ,p_correspondence_language VARCHAR2
112 ,p_student_status VARCHAR2
113 ,p_fte_capacity NUMBER
114 ,p_on_military_service VARCHAR2
115 ,p_second_passport_exists VARCHAR2
116 ,p_background_check_status VARCHAR2
117 ,p_background_date_check DATE
118 ,p_blood_type VARCHAR2
119 ,p_last_medical_test_date DATE
120 ,p_last_medical_test_by VARCHAR2
121 ,p_rehire_recommendation VARCHAR2
122 ,p_rehire_reason VARCHAR2
123 ,p_resume_exists VARCHAR2
124 ,p_resume_last_updated DATE
125 ,p_office_number VARCHAR2
126 ,p_internal_location VARCHAR2
127 ,p_mailstop VARCHAR2
128 ,p_honors VARCHAR2
129 ,p_pre_name_adjunct VARCHAR2
130 ,p_hold_applicant_date_until DATE
131 ,p_benefit_group_id NUMBER
132 ,p_receipt_of_death_cert_date DATE
133 ,p_coord_ben_med_pln_no VARCHAR2
134 ,p_coord_ben_no_cvg_flag VARCHAR2
135 ,p_uses_tobacco_flag VARCHAR2
136 ,p_dpdnt_adoption_date DATE
137 ,p_dpdnt_vlntry_svce_flag VARCHAR2
138 ,p_date_of_death DATE
139 ,p_original_date_of_hire DATE
140 ,p_adjusted_svc_date DATE
141 ,p_town_of_birth VARCHAR2
142 ,p_region_of_birth VARCHAR2
143 ,p_country_of_birth VARCHAR2
144 ,p_global_person_id VARCHAR2
145 ,p_fast_path_employee VARCHAR2 default null
146 ,p_rehire_authorizor VARCHAR2 default null
147 ,p_party_id number default null
148 ,p_npw_number IN OUT NOCOPY VARCHAR2
149 ,p_current_npw_flag VARCHAR2 default null
150 ,p_order_name IN VARCHAR2
151 ,p_global_name IN VARCHAR2
152 ,p_local_name IN VARCHAR2
153 ) is
154 --
155 -- Define Cursor.
156 --
157 cursor c1 is select per_people_s.nextval
158 from sys.dual;
159 --
160 cursor c2 is select rowid
161 from per_all_people_f
162 where effective_start_date = p_effective_start_date
163 and effective_end_date = p_effective_end_date
164 and person_id = p_person_id;
165 --
166 cursor c_person is
167 select *
168 from per_all_people_f
169 where person_id = p_person_id
170 and p_effective_start_date
171 between effective_start_date
172 and effective_end_date;
173 --
174 l_person per_all_people_f%rowtype;
175 --
176 begin
177 --
178 -- Test current numbers are not used by
179 -- the system already.
180 --
181 hr_utility.set_location('per_people11_pkg.insert_row1',10);
182 hr_person.validate_unique_number(p_person_id =>p_person_id
183 , p_business_group_id => p_business_group_id
184 , p_employee_number => p_employee_number
185 , p_applicant_number => p_applicant_number
186 , p_npw_number => null --p_npw_number
187 , p_current_employee => p_current_employee_flag
188 , p_current_applicant => p_current_applicant_flag
189 , p_current_npw => null --p_current_npw_flag
190 );
191
192 hr_utility.set_location('Employee Number = '||p_employee_number,20);
193 hr_utility.set_location('Applicant Number = '||p_applicant_number,30);
194
195 --Start of fix for Bug 2167668
196
197 IF p_start_date > p_hold_applicant_date_until THEN
198 hr_utility.set_message('800', 'PER_289796_HOLD_UNTIL_DATE');
199 hr_utility.set_message_token('HOLD_DATE', p_start_date );
200 hr_utility.raise_error;
201 END IF;
202 -- End of fix for Bug 2167668
203
204 -- #345205
205 -- A new person id is only selected from the sequence if the value passed is
206 -- not null already. This is used in Applicant Quick Entry when creating an
207 -- applicant with an initial status of TERM_APL - this requires two person
208 -- rows on successive days.
209 -- DK 27-FEB-96
210 if ( p_person_id is null ) then
211 open c1;
212 fetch c1 into p_person_id;
213 close c1;
214 hr_utility.set_location('per_people11_pkg.insert_row1',40);
215 end if;
216 --
217 /* BEGIN OF WWBUG 1975359 */
218 if p_party_id is not null then
219 --
220 per_per_bus.chk_party_id
221 (p_person_id => p_person_id
222 ,p_party_id => p_party_id
223 ,p_effective_date => p_effective_start_date
224 ,p_object_version_number => null);
225 --
226 end if;
227 --
228 /* END OF WWBUG 1975359 */
229 --
230 insert into PER_ALL_PEOPLE_F
231 (person_id
232 ,effective_start_date
233 ,effective_end_date
234 ,business_group_id
235 ,person_type_id
236 ,last_name
237 ,start_date
238 ,applicant_number
239 ,comment_id
240 ,current_applicant_flag
241 ,current_emp_or_apl_flag
242 ,current_employee_flag
243 ,date_employee_data_verified
244 ,date_of_birth
245 ,town_of_birth
246 ,region_of_birth
247 ,country_of_birth
248 ,global_person_id
249 ,party_id
250 ,email_address
251 ,employee_number
252 ,expense_check_send_to_address
253 ,first_name
254 ,full_name
255 ,known_as
256 ,marital_status
257 ,middle_names
258 ,nationality
259 ,national_identifier
260 ,previous_last_name
261 ,registered_disabled_flag
262 ,sex
263 ,title
264 ,suffix
265 ,vendor_id
266 -- ,work_telephone
267 ,request_id
268 ,program_application_id
269 ,program_id
270 ,program_update_date
271 ,attribute_category
272 ,attribute1
273 ,attribute2
274 ,attribute3
275 ,attribute4
276 ,attribute5
277 ,attribute6
278 ,attribute7
279 ,attribute8
280 ,attribute9
281 ,attribute10
282 ,attribute11
283 ,attribute12
284 ,attribute13
285 ,attribute14
286 ,attribute15
287 ,attribute16
288 ,attribute17
289 ,attribute18
290 ,attribute19
291 ,attribute20
292 ,attribute21
293 ,attribute22
294 ,attribute23
295 ,attribute24
296 ,attribute25
297 ,attribute26
298 ,attribute27
299 ,attribute28
300 ,attribute29
301 ,attribute30
302 ,last_update_date
303 ,last_updated_by
304 ,last_update_login
305 ,created_by
306 ,creation_date
307 ,per_information_category
308 ,per_information1
309 ,per_information2
310 ,per_information3
311 ,per_information4
312 ,per_information5
313 ,per_information6
314 ,per_information7
315 ,per_information8
316 ,per_information9
317 ,per_information10
318 ,per_information11
319 ,per_information12
320 ,per_information13
321 ,per_information14
322 ,per_information15
323 ,per_information16
324 ,per_information17
325 ,per_information18
326 ,per_information19
327 ,per_information20
328 ,per_information21
329 ,per_information22
330 ,per_information23
331 ,per_information24
332 ,per_information25
333 ,per_information26
334 ,per_information27
335 ,per_information28
336 ,per_information29
337 ,per_information30
338 ,work_schedule
339 ,correspondence_language
340 ,student_status
341 ,fte_capacity
342 ,on_military_service
343 ,second_passport_exists
344 ,background_check_status
345 ,background_date_check
346 ,blood_type
347 ,last_medical_test_date
348 ,last_medical_test_by
349 ,rehire_recommendation
350 ,rehire_reason
351 ,resume_exists
352 ,resume_last_updated
353 ,office_number
354 ,internal_location
355 ,mailstop
356 ,honors
357 ,pre_name_adjunct
358 ,hold_applicant_date_until
359 ,benefit_group_id
360 ,receipt_of_death_cert_date
361 ,coord_ben_med_pln_no
362 ,coord_ben_no_cvg_flag
363 ,uses_tobacco_flag
364 ,dpdnt_adoption_date
365 ,dpdnt_vlntry_svce_flag
366 ,date_of_death
367 ,original_date_of_hire
368 ,fast_path_employee
369 ,rehire_authorizor
370 ,npw_number
371 ,current_npw_flag
372 ,order_name
373 ,global_name
374 ,local_name
375 )
376 values
377 (p_person_id
378 ,p_effective_start_date
379 ,p_effective_end_date
380 ,p_business_group_id
381 -- ,p_person_type_id
382 ,hr_person_type_usage_info.get_default_person_type_id(p_person_type_id)
383 ,p_last_name
384 ,p_start_date
385 ,p_applicant_number
386 ,p_comment_id
387 ,p_current_applicant_flag
388 ,p_current_emp_or_apl_flag
389 ,p_current_employee_flag
390 ,p_date_employee_data_verified
391 ,p_date_of_birth
392 ,p_town_of_birth
393 ,p_region_of_birth
394 ,p_country_of_birth
395 ,p_global_person_id
396 ,p_party_id
397 ,p_email_address
398 ,p_employee_number
399 ,p_expense_check_send_to_addr
400 ,p_first_name
401 ,p_full_name
402 ,p_known_as
403 ,p_marital_status
404 ,p_middle_names
405 ,p_nationality
406 ,p_national_identifier
407 ,p_previous_last_name
408 ,p_registered_disabled_flag
409 ,p_sex
410 ,p_title
411 ,p_suffix
412 ,p_vendor_id
413 -- ,p_work_telephone
414 ,p_request_id
415 ,p_program_application_id
416 ,p_program_id
417 ,p_program_update_date
418 ,p_a_cat
419 ,p_a1
420 ,p_a2
421 ,p_a3
422 ,p_a4
423 ,p_a5
424 ,p_a6
425 ,p_a7
426 ,p_a8
427 ,p_a9
428 ,p_a10
429 ,p_a11
430 ,p_a12
431 ,p_a13
432 ,p_a14
433 ,p_a15
434 ,p_a16
435 ,p_a17
436 ,p_a18
437 ,p_a19
438 ,p_a20
439 ,p_a21
440 ,p_a22
441 ,p_a23
442 ,p_a24
443 ,p_a25
444 ,p_a26
445 ,p_a27
446 ,p_a28
447 ,p_a29
448 ,p_a30
449 ,p_last_update_date
450 ,p_last_updated_by
451 ,p_last_update_login
452 ,p_created_by
453 ,p_creation_date
454 ,p_i_cat
455 ,p_i1
456 ,p_i2
457 ,p_i3
458 ,p_i4
459 ,p_i5
460 ,p_i6
461 ,p_i7
462 ,p_i8
463 ,p_i9
464 ,p_i10
465 ,p_i11
466 ,p_i12
467 ,p_i13
468 ,p_i14
469 ,p_i15
470 ,p_i16
471 ,p_i17
472 ,p_i18
473 ,p_i19
474 ,p_i20
475 ,p_i21
476 ,p_i22
477 ,p_i23
478 ,p_i24
479 ,p_i25
480 ,p_i26
481 ,p_i27
482 ,p_i28
483 ,p_i29
484 ,p_i30
485 ,p_work_schedule
486 ,p_correspondence_language
487 ,p_student_status
488 ,p_fte_capacity
489 ,p_on_military_service
490 ,p_second_passport_exists
491 ,p_background_check_status
492 ,p_background_date_check
493 ,p_blood_type
494 ,p_last_medical_test_date
495 ,p_last_medical_test_by
496 ,p_rehire_recommendation
497 ,p_rehire_reason
498 ,p_resume_exists
499 ,p_resume_last_updated
500 ,p_office_number
501 ,p_internal_location
502 ,p_mailstop
503 ,p_honors
504 ,p_pre_name_adjunct
505 ,p_hold_applicant_date_until
506 ,p_benefit_group_id
507 ,p_receipt_of_death_cert_date
508 ,p_coord_ben_med_pln_no
509 ,p_coord_ben_no_cvg_flag
510 ,p_uses_tobacco_flag
511 ,p_dpdnt_adoption_date
512 ,p_dpdnt_vlntry_svce_flag
513 ,p_date_of_death
517 ,p_npw_number
514 ,p_original_date_of_hire
515 ,p_fast_path_employee
516 ,p_rehire_authorizor
518 ,p_current_npw_flag
519 ,p_order_name
520 ,p_global_name
521 ,p_local_name
522 );
523 --
524 -- Earlier version comments were moved from this place to
525 -- after the command close c_person.
526 --
527 open c_person;
528 --
529 fetch c_person into l_person;
530 --
531 close c_person;
532 --
533 /* BEGIN OF WWBUG 1975359 */
534 /* ------------------------------------------------
535 -- BEGIN TCA_UNMERGE CHANGES
536 --
537 per_hrtca_merge.create_tca_person(p_rec => l_person);
538 --
539 hr_utility.set_location('Updating party id',10);
540 --
541 -- Now assign the resulting party id back to the record.
542 --
543 update per_all_people_f
544 set party_id = l_person.party_id
545 where person_id = p_person_id;
546 --
547 -- END TCA_UNMERGE CHANGES
548 */
549 /* END OF WWBUG 1975359 */
550 --
551 -- HR/WF synchronization call
552 --
553 /* commenting out and moving to after ptu. Bug 3297591.
554 per_hrwf_synch.per_per_wf(p_rec => l_person,
555 p_action => 'INSERT');
556 */
557 --
558 --
559 hr_utility.set_location('per_people11_pkg.insert_row1',50);
560 --
561 open c2;
562 --
563 fetch c2 into p_rowid;
564 --
565 hr_utility.set_location('per_people11_pkg.insert_row1',60);
566 close c2;
567 --
568 ben_dt_trgr_handle.person(p_rowid => null
569 ,p_business_group_id => p_business_group_id
570 ,p_person_id => p_person_id
571 ,p_effective_start_date => p_effective_start_date
572 ,p_effective_end_date => p_effective_end_date
573 ,p_date_of_birth => p_date_of_birth
574 ,p_date_of_death => p_date_of_death
575 ,p_marital_status => p_marital_status
576 ,p_on_military_service => p_on_military_service
577 ,p_registered_disabled_flag => p_registered_disabled_flag
578 ,p_sex => p_sex
579 ,p_student_status => p_student_status
580 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
581 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
582 ,p_uses_tobacco_flag => p_uses_tobacco_flag
583 ,p_benefit_group_id => p_benefit_group_id
584 ,p_per_information10 => p_i10
585 ,p_original_date_of_hire => p_original_date_of_hire
586 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
587 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
588 ,p_attribute1 => p_a1
589 ,p_attribute2 =>p_a2
590 ,p_attribute3 =>p_a3
591 ,p_attribute4 =>p_a4
592 ,p_attribute5 =>p_a5
593 ,p_attribute6 =>p_a6
594 ,p_attribute7 =>p_a7
595 ,p_attribute8 =>p_a8
596 ,p_attribute9 =>p_a9
597 ,p_attribute10 =>p_a10
598 ,p_attribute11 =>p_a11
599 ,p_attribute12 =>p_a12
600 ,p_attribute13 =>p_a13
601 ,p_attribute14 =>p_a14
602 ,p_attribute15 =>p_a15
603 ,p_attribute16 =>p_a16
604 ,p_attribute17 =>p_a17
605 ,p_attribute18 =>p_a18
606 ,p_attribute19 =>p_a19
607 ,p_attribute20 =>p_a20
608 ,p_attribute21 =>p_a21
609 ,p_attribute22 =>p_a22
610 ,p_attribute23 =>p_a23
611 ,p_attribute24 =>p_a24
612 ,p_attribute25 =>p_a25
613 ,p_attribute26 =>p_a26
614 ,p_attribute27 =>p_a27
615 ,p_attribute28 =>p_a28
616 ,p_attribute29 =>p_a29
617 ,p_attribute30 =>p_a30
618 );
619 --
620 --2448642: now we are securing by contacts, the security list maintenance must be done for all
621 --inserts (note CWK are done in their own API anyway, not here)
622 --2462779: Modification to avoce ref'd fix. Call populate_new_person for
623 -- EMPs and APLs but call populate_new_contact for new OTHERs.
624 --
625 hr_utility.set_location('per_people11_pkg.insert_row1',70);
626 --
627 if p_create_defaults_for in ('EMP','APL') then
628 hr_security_internal.populate_new_person
629 (p_business_group_id=>p_business_group_id
630 ,p_person_id =>p_person_id);
631 else
632 hr_security_internal.populate_new_contact
633 (p_business_group_id=>p_business_group_id
634 ,p_person_id =>p_person_id);
635 end if;
636 --
637 hr_utility.set_location('per_people11_pkg.insert_row1',75);
638 --
639 if p_create_defaults_for in ('EMP','APL') then
640 --
641 -- #317298 We must insert a row into per_person_list for the new EMP
642 -- or APL, otherwise secure users won't be able to see them until LISTGEN
643 -- has next been run. This should be revisited as part of a wider security
644 -- review. For example, #294004 points out that all users will be able
648 -- For the time being, just put a row into per_person_list for the
645 -- to see the new person created here in the default business group, until
646 -- their Org or Position is changed on the assignment AND LISTGEN is run.
647 --
649 -- default business group if it doesn't have the VIEW_ALL_FLAG set. The
650 -- 'not exists' check is there for defensive coding, and should never
651 -- arise, as we're only dealing with new people.
652 -- RMF 02-Feb-96.
653 --
654 --hr_security_internal.populate_new_person
655 --(p_business_group_id=>p_business_group_id
656 --,p_person_id =>p_person_id);
657 --
658 if p_create_defaults_for = 'EMP' then
659 --
660 hr_utility.set_location('per_people11_pkg.insert_row1',80);
661 -- insert employee rows.
662 --
663 -- VT 08/13/96
664 per_people9_pkg.insert_employee_rows(p_person_id => p_person_id
665 ,p_effective_start_date => p_effective_start_date
666 ,p_effective_end_date => p_effective_end_date
667 ,p_business_group_id =>p_business_group_id
668 ,p_emp_ass_status_type_id => p_emp_ass_status_type_id
669 ,p_employee_number => p_employee_number
670 ,p_request_id => p_request_id
671 ,p_program_application_id => p_program_application_id
672 ,p_program_id => p_program_id
673 ,p_program_update_date => p_program_update_date
674 ,p_last_update_date => p_last_update_date
675 ,p_last_updated_by => p_last_updated_by
676 ,p_last_update_login => p_last_update_login
677 ,p_created_by => p_created_by
678 ,p_creation_date => p_creation_date
679 ,p_adjusted_svc_date =>p_adjusted_svc_date);
680
681 else
682 --
683 hr_utility.set_location('per_people11_pkg.insert_row1',90);
684 -- do insert applicant rows.
685 --
686 -- VT 08/13/96
687 per_people9_pkg.insert_applicant_rows(p_person_id => p_person_id
688 ,p_effective_start_date => p_effective_start_date
689 ,p_effective_end_date => p_effective_end_date
690 ,p_business_group_id =>p_business_group_id
691 ,p_app_ass_status_type_id => p_app_ass_status_type_id
692 ,p_request_id => p_request_id
693 ,p_program_application_id => p_program_application_id
694 ,p_program_id => p_program_id
695 ,p_program_update_date => p_program_update_date
696 ,p_last_update_date => p_last_update_date
697 ,p_last_updated_by => p_last_updated_by
698 ,p_last_update_login => p_last_update_login
699 ,p_created_by => p_created_by
700 ,p_creation_date => p_creation_date
701 );
702 end if;
703 hr_utility.set_location('per_people11_pkg.insert_row1',100);
704 end if;
705 --
706 -- PTU : Start of Changes
707 --
708 hr_utility.set_location('per_people11_pkg.insert_row1',110);
709 hr_per_type_usage_internal.maintain_person_type_usage
710 (p_effective_date => p_effective_start_date
711 ,p_person_id => p_person_id
712 ,p_person_type_id => p_person_type_id
713 );
714 -- PTU : End of Changes
715 --
716 -- moved synch here - bug 3297591.
717 --
718 per_hrwf_synch.per_per_wf(p_rec => l_person,
719 p_action => 'INSERT');
720 --
721 hr_utility.set_location('Leaving: per_people11_pkg.insert_row1',120);
722 end insert_row1;
723 --
724 procedure delete_row1(p_rowid VARCHAR2) is
725
726 -- bug 4635241 starts here
727 --
728 cursor c_person is
729 select *
730 from per_all_people_f
731 where rowid=chartorowid(p_rowid);
732
733 cursor c_person1(p_esd per_all_people_f.effective_start_date%type
734 ,personid per_all_people_f.person_id%type) is
735 select *
736 from per_all_people_f
737 where effective_start_date between p_esd and sysdate
738 and person_id=personid
739 and effective_end_date > sysdate;
740
741 cursor c_person2(p_esd per_all_people_f.effective_start_date%type
742 ,personid per_all_people_f.person_id%type) is
743 select *
744 from per_all_people_f
745 where effective_start_date < p_esd
746 and person_id=personid
747 and effective_end_date > sysdate;
748
749 l_person_rec per_all_people_f%rowtype;
750 l_person_rec1 per_all_people_f%rowtype :=NULL;
751 --
752 -- bug 4635241 ends here
753
754 begin
755 --
756 -- bug 4635241 starts here
757 --
758 open c_person;
759 fetch c_person into l_person_rec;
760 close c_person;
761
762 -- bug 4635241 end here
763 --
764 delete from per_all_people_f
765 where rowid=chartorowid(p_rowid);
766 --
767 -- bug 4635241 starts here
768 --
769 if ( l_person_rec.effective_start_date <= sysdate ) then
770 open c_person1(l_person_rec.effective_start_date,
771 l_person_rec.person_id);
772 fetch c_person1 into l_person_rec1;
773 if c_person1 % notfound then
774 close c_person1;
775 open c_person2(l_person_rec.effective_start_date,
776 l_person_rec.person_id);
777 fetch c_person2 into l_person_rec1;
778 close c_person2;
779 end if ;
780 close c_person1;
781 --
782 if (l_person_rec1.person_id is not null) then
783 per_hrwf_synch.per_per_wf(l_person_rec1,'INSERT');
784 end if;
785 --
786 end if;
787 -- bug 4635241 ends here
788 end delete_row1;
789 --
790 --
791 END PER_PEOPLE11_PKG;