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