[Home] [Help]
PACKAGE BODY: APPS.PER_APP_ASG_PKG
Source
1 package body PER_APP_ASG_PKG as
2 /* $Header: peasg02t.pkb 120.3 2006/05/17 19:22:31 irgonzal ship $ */
3 --
4 -- PRIVATE PROCEDURES --
5 --
6 --
7 -- Procedure
8 -- set_end_date
9 -- Purpose
10 -- Sets an end date on rows which are deleted with delete mode
11 -- FUTURE_CHANGES or NEXT_CHANGE
12 --
13 procedure set_end_date(
14 p_new_end_date date,
15 p_assignment_id number ) is
16 --
17 begin
18 --
19 update per_assignments_f a
20 set a.effective_end_date = p_new_end_date
21 where a.assignment_id = p_assignment_id
22 and a.effective_end_date = (
23 select max(a2.effective_end_date)
24 from per_assignments_f a2
25 where a2.assignment_id = a.assignment_id);
26 --
27 end set_end_date;
28
29
30 --
31 -- Private procedure. Called to ensure that child rows are removed before parent is removed from
32 -- the database. This is a new procedure as per_assignment_budget_values_f is now datetracked and
33 -- rows will need to be removed from this when the assignment is removed.
34 --
35 --
36 -- SASmith 31-March-1998
37
38 procedure delete_child ( p_assignment_id in number,
39 p_delete_mode in varchar2) is
40
41 p_del_flag VARCHAR2(1) := 'N';
42
43 --
44 BEGIN
45
46 -- hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_child' , 5) ;
47 --
48 BEGIN
49 select 'Y'
50 into p_del_flag
51 from sys.dual
52 where exists (
53 select null
54 from per_assignment_budget_values_f
55 where assignment_id = p_assignment_id
56 and p_delete_mode = 'ZAP');
57
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN NULL;
60 END;
61 --
62 IF p_del_flag = 'Y' and
63 p_delete_mode = 'ZAP' THEN
64 --
65 -- hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_child' , 10) ;
66
67 Delete per_assignment_budget_values_f
68 where assignment_id = p_assignment_id;
69
70 END IF;
71 --
72 END delete_child;
73
74
75
76
77 -- --
78 -- --
79 -- PUBLIC PROCEDURES --
80 -- Procedure
81 -- cleanup_letters
82 -- Purpose
83 -- Remove extra letters for the given assignment
84 -- Arguments
85 -- As below
86 procedure cleanup_letters ( p_assignment_id in number ) is
87 begin
88 --
89 delete from per_letter_request_lines p
90 where p.assignment_id = p_assignment_id
91 and exists
92 (select null
93 from per_letter_requests r2
94 where r2.letter_request_id = p.letter_request_id
95 and r2.request_status = 'PENDING')
96 and not exists
97 (select null
98 from per_assignments_f a
99 where assignment_id = p_assignment_id
100 and ( (a.effective_start_date = p.date_from
101 and
102 a.assignment_status_type_id = p.assignment_status_type_id)
103 or (a.effective_end_date =
104 (select max(a2.effective_end_date)
105 from per_assignments_f a2
106 where a2.assignment_id = p_assignment_id)
107 and a.effective_end_date = p.date_from ))) ;
108 --
109 end cleanup_letters ;
110 -- --
111 procedure insert_row(
112 p_row_id in out nocopy varchar2,
113 p_assignment_id in out nocopy number,
114 p_effective_start_date date,
115 p_effective_end_date date,
116 p_business_group_id number,
117 p_recruiter_id number,
118 p_grade_id number,
119 p_position_id number,
120 p_job_id number,
121 p_assignment_status_type_id number,
122 p_location_id number,
123 p_location_code in out nocopy varchar2,
124 p_person_referred_by_id number,
125 p_supervisor_id number,
126 p_person_id number,
127 p_recruitment_activity_id number,
128 p_source_organization_id number,
129 p_organization_id number,
130 p_people_group_id number,
131 p_people_group_name varchar2,
132 p_vacancy_id number,
133 p_assignment_sequence in out nocopy number,
134 p_assignment_type in out nocopy varchar2,
135 p_primary_flag in out nocopy varchar2,
136 p_application_id number,
137 p_change_reason varchar2,
138 p_comment_id number,
139 p_date_probation_end date,
140 p_frequency varchar2,
141 p_frequency_meaning in out nocopy varchar2,
142 p_manager_flag varchar2,
143 p_normal_hours number,
144 p_probation_period number,
145 p_probation_unit varchar2,
146 p_source_type varchar2,
147 p_time_normal_finish varchar2,
148 p_time_normal_start varchar2,
149 p_request_id number,
150 p_program_application_id number,
151 p_program_id number,
152 p_program_update_date date,
153 p_ass_attribute_category varchar2,
154 p_ass_attribute1 varchar2,
155 p_ass_attribute2 varchar2,
156 p_ass_attribute3 varchar2,
157 p_ass_attribute4 varchar2,
158 p_ass_attribute5 varchar2,
159 p_ass_attribute6 varchar2,
160 p_ass_attribute7 varchar2,
161 p_ass_attribute8 varchar2,
162 p_ass_attribute9 varchar2,
163 p_ass_attribute10 varchar2,
164 p_ass_attribute11 varchar2,
165 p_ass_attribute12 varchar2,
166 p_ass_attribute13 varchar2,
167 p_ass_attribute14 varchar2,
168 p_ass_attribute15 varchar2,
169 p_ass_attribute16 varchar2,
170 p_ass_attribute17 varchar2,
171 p_ass_attribute18 varchar2,
172 p_ass_attribute19 varchar2,
173 p_ass_attribute20 varchar2,
174 p_ass_attribute21 varchar2,
175 p_ass_attribute22 varchar2,
176 p_ass_attribute23 varchar2,
177 p_ass_attribute24 varchar2,
178 p_ass_attribute25 varchar2,
179 p_ass_attribute26 varchar2,
180 p_ass_attribute27 varchar2,
181 p_ass_attribute28 varchar2,
182 p_ass_attribute29 varchar2,
183 p_ass_attribute30 varchar2,
184 p_session_date date,
185 p_contract_id number default null,
186 p_cagr_id_flex_num number default null,
187 p_cagr_grade_def_id number default null,
188 p_establishment_id number default null,
189 p_collective_agreement_id number default null,
190 p_notice_period number default null,
191 p_notice_period_uom varchar2 default null,
192 p_employee_category varchar2 default null,
193 p_work_at_home varchar2 default null,
194 p_job_post_source_name varchar2 default null,
195 p_grade_ladder_pgm_id number default null,
196 p_supervisor_assignment_id number default null
197 ) is
198 cursor c1 is
199 select per_assignments_s.nextval
200 from sys.dual;
201 cursor c2 is
202 select rowid
203 from per_assignments_f
204 where assignment_id = P_ASSIGNMENT_ID
205 and effective_start_date = P_EFFECTIVE_START_DATE
206 and effective_end_date = P_EFFECTIVE_END_DATE;
207 --
208 l_assignment_status_id number; -- discards output from irc_asg_status api
209 l_object_version_number number; -- discards output from irc_asg_status api
210 --
211 begin
212 --
213 -- PRE-INSERT CHECKS
214 hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 1 ) ;
215 hr_utility.trace('p_grade_ladder_pgm_id : ' || p_grade_ladder_pgm_id);
216 check_apl_end_date ( p_application_id => p_application_id ) ;
217 hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 2 ) ;
218 check_current_applicant ( p_person_id => p_person_id,
219 p_session_date => p_session_date ) ;
220 --
221 hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 3 ) ;
222 open c1;
223 fetch c1 into P_ASSIGNMENT_ID;
224 close c1;
225 --
226 -- Set Assignment Type and Primary flag
227 p_assignment_type := 'A' ;
228 p_primary_flag := 'N' ;
229 --
230 --
231 -- Generate new assignment sequence
232 hr_assignment.gen_new_ass_sequence
233 ( p_person_id,
234 'A',
235 p_assignment_sequence
236 );
237 --
238 --
239 begin
240 hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 4 ) ;
241 insert into per_assignments_f (
242 assignment_id,
243 effective_start_date,
244 effective_end_date,
245 business_group_id,
246 recruiter_id,
247 grade_id,
248 position_id,
249 job_id,
250 assignment_status_type_id,
251 location_id,
252 person_referred_by_id,
253 supervisor_id,
254 person_id,
255 recruitment_activity_id,
256 source_organization_id,
257 organization_id,
258 people_group_id,
259 vacancy_id,
260 assignment_sequence,
261 assignment_type,
262 primary_flag,
263 application_id,
264 change_reason,
265 comment_id,
266 date_probation_end,
267 frequency,
268 manager_flag,
269 normal_hours,
270 probation_period,
271 probation_unit,
272 source_type,
273 time_normal_finish,
274 time_normal_start,
275 request_id,
276 program_application_id,
277 program_id,
278 program_update_date,
279 ass_attribute_category,
280 ass_attribute1,
281 ass_attribute2,
282 ass_attribute3,
283 ass_attribute4,
284 ass_attribute5,
285 ass_attribute6,
286 ass_attribute7,
287 ass_attribute8,
288 ass_attribute9,
289 ass_attribute10,
290 ass_attribute11,
291 ass_attribute12,
292 ass_attribute13,
293 ass_attribute14,
294 ass_attribute15,
295 ass_attribute16,
296 ass_attribute17,
297 ass_attribute18,
298 ass_attribute19,
299 ass_attribute20,
300 ass_attribute21,
301 ass_attribute22,
302 ass_attribute23,
303 ass_attribute24,
304 ass_attribute25,
305 ass_attribute26,
306 ass_attribute27,
307 ass_attribute28,
308 ass_attribute29,
309 ass_attribute30,
310 contract_id,
311 cagr_id_flex_num,
312 cagr_grade_def_id,
313 establishment_id,
314 collective_agreement_id,
315 notice_period,
316 notice_period_uom,
317 work_at_home,
318 employee_category,
319 job_post_source_name ,
320 grade_ladder_pgm_id,
321 supervisor_assignment_id )
322 values (
323 p_assignment_id,
324 p_effective_start_date,
325 p_effective_end_date,
326 p_business_group_id,
327 p_recruiter_id,
328 p_grade_id,
329 p_position_id,
330 p_job_id,
331 p_assignment_status_type_id,
332 p_location_id,
333 p_person_referred_by_id,
334 p_supervisor_id,
335 p_person_id,
336 p_recruitment_activity_id,
337 p_source_organization_id,
338 p_organization_id,
339 p_people_group_id,
340 p_vacancy_id,
341 p_assignment_sequence,
342 p_assignment_type,
343 p_primary_flag,
344 p_application_id,
345 p_change_reason,
346 p_comment_id,
347 p_date_probation_end,
348 p_frequency,
349 p_manager_flag,
350 p_normal_hours,
351 p_probation_period,
352 p_probation_unit,
353 p_source_type,
354 p_time_normal_finish,
355 p_time_normal_start,
356 p_request_id,
357 p_program_application_id,
358 p_program_id,
359 p_program_update_date,
360 p_ass_attribute_category,
361 p_ass_attribute1,
362 p_ass_attribute2,
363 p_ass_attribute3,
364 p_ass_attribute4,
365 p_ass_attribute5,
366 p_ass_attribute6,
367 p_ass_attribute7,
368 p_ass_attribute8,
369 p_ass_attribute9,
370 p_ass_attribute10,
371 p_ass_attribute11,
372 p_ass_attribute12,
373 p_ass_attribute13,
374 p_ass_attribute14,
375 p_ass_attribute15,
376 p_ass_attribute16,
377 p_ass_attribute17,
378 p_ass_attribute18,
379 p_ass_attribute19,
380 p_ass_attribute20,
381 p_ass_attribute21,
382 p_ass_attribute22,
383 p_ass_attribute23,
384 p_ass_attribute24,
385 p_ass_attribute25,
386 p_ass_attribute26,
387 p_ass_attribute27,
388 p_ass_attribute28,
389 p_ass_attribute29,
390 p_ass_attribute30,
391 p_contract_id,
392 p_cagr_id_flex_num,
393 p_cagr_grade_def_id,
394 p_establishment_id,
395 p_collective_agreement_id,
396 p_notice_period,
397 p_notice_period_uom,
398 p_work_at_home,
399 p_employee_category,
400 p_job_post_source_name ,
401 p_grade_ladder_pgm_id,
402 p_supervisor_assignment_id
403 ) ;
404 end;
405 --
406 open c2;
407 fetch c2 into P_ROW_ID;
408 close c2;
409 --
410 -- Update people group
411 --
412 hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 5 ) ;
413 per_applicant_pkg.update_group ( p_people_group_id,
414 p_people_group_name ) ;
415 --
416 -- Create letter request
417 --
418 hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 6 ) ;
419 per_applicant_pkg.check_for_letter_requests (
420 p_business_group_id => p_business_group_id,
421 p_per_system_status => NULL, --***TEMP
422 p_assignment_status_type_id => p_assignment_status_type_id,
423 p_person_id => p_person_id,
424 p_assignment_id => p_assignment_id,
425 p_effective_start_date => p_effective_start_date,
426 p_validation_start_date => p_effective_start_date,
427 p_vacancy_id => p_vacancy_id ) ;
428 --
429 -- Create default budget values
430 hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 7 ) ;
431 per_applicant_pkg.create_default_budget_values (
432 p_business_group_id,
433 p_assignment_id,
434 p_effective_start_date,
435 p_effective_end_date) ;
436 --
437 --
438 -- Set the location code if the location id is not null and the code is
439 -- null
440 hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 8 ) ;
441 if ( ( p_location_id is not null ) and ( p_location_code is null ) ) then
442 p_location_code := per_applicant_pkg.get_location_code ( p_location_id ) ;
443 end if;
444 --
445 --
446 -- Set the frequency meaning if the frequency is not null and the meaning is
447 -- null
448 hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 9 ) ;
449 if ( ( p_frequency is not null ) and ( p_frequency_meaning is null ) ) then
450 p_frequency_meaning := hr_general.decode_lookup( 'FREQUENCY',
451 p_frequency ) ;
452 end if;
453 --
454 --
455 -- Insert record into iRec Asg Statuses. Otherwise the applications applied
456 -- through PUI, will not visible in iRec for applicants created through iRec
457 -- Bug# 2985747
458 irc_asg_status_api.create_irc_asg_status
459 ( p_validate => FALSE
460 , p_assignment_id => p_assignment_id
461 , p_assignment_status_type_id => p_assignment_status_type_id
462 , p_status_change_date => p_effective_start_date
463 , p_status_change_reason => p_change_reason
464 , p_assignment_status_id => l_assignment_status_id
465 , p_object_version_number => l_object_version_number
466 );
467 --
468 -- Start of OAB code addition
469 -- Whenever Applicant Information is getting changed/inserted via Applicant forms
470 -- we need to trigger OAB Lifeevents. The following ben call will trigger LE
471 -- Bug 3506363
472
473 hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 10 ) ;
474 hr_utility.set_location ( 'Before OAB Call' , 11 ) ;
475 ben_dt_trgr_handle.assignment
476 (p_rowid => p_row_id
477 ,p_assignment_id => p_assignment_id
478 ,p_business_group_id => p_business_group_id
479 ,p_person_id => p_person_id
480 ,p_effective_start_date => p_effective_start_date
481 ,p_effective_end_date => p_effective_end_date
482 ,p_assignment_status_type_id => p_assignment_status_type_id
483 ,p_assignment_type => p_assignment_type
484 ,p_organization_id => p_organization_id
485 ,p_primary_flag => p_primary_flag
486 ,p_change_reason => p_change_reason
487 ,p_employment_category => null
488 ,p_frequency => p_frequency
489 ,p_grade_id => p_grade_id
490 ,p_job_id => p_job_id
491 ,p_position_id => p_position_id
492 ,p_location_id => p_location_id
493 ,p_normal_hours => p_normal_hours
494 ,p_payroll_id => null
495 ,p_pay_basis_id => null
496 ,p_bargaining_unit_code => null
497 ,p_labour_union_member_flag => null
498 ,p_hourly_salaried_code => null
499 ,p_people_group_id => p_people_group_id
500 ,p_ass_attribute1 => p_ass_attribute1
501 ,p_ass_attribute2 => p_ass_attribute2
502 ,p_ass_attribute3 => p_ass_attribute3
503 ,p_ass_attribute4 => p_ass_attribute4
504 ,p_ass_attribute5 => p_ass_attribute5
505 ,p_ass_attribute6 => p_ass_attribute6
506 ,p_ass_attribute7 => p_ass_attribute7
507 ,p_ass_attribute8 => p_ass_attribute8
508 ,p_ass_attribute9 => p_ass_attribute9
509 ,p_ass_attribute10 => p_ass_attribute10
510 ,p_ass_attribute11 => p_ass_attribute11
511 ,p_ass_attribute12 => p_ass_attribute12
512 ,p_ass_attribute13 => p_ass_attribute13
513 ,p_ass_attribute14 => p_ass_attribute14
514 ,p_ass_attribute15 => p_ass_attribute15
515 ,p_ass_attribute16 => p_ass_attribute16
516 ,p_ass_attribute17 => p_ass_attribute17
517 ,p_ass_attribute18 => p_ass_attribute18
518 ,p_ass_attribute19 => p_ass_attribute19
519 ,p_ass_attribute20 => p_ass_attribute20
520 ,p_ass_attribute21 => p_ass_attribute21
521 ,p_ass_attribute22 => p_ass_attribute22
522 ,p_ass_attribute23 => p_ass_attribute23
523 ,p_ass_attribute24 => p_ass_attribute24
524 ,p_ass_attribute25 => p_ass_attribute25
525 ,p_ass_attribute26 => p_ass_attribute26
526 ,p_ass_attribute27 => p_ass_attribute27
527 ,p_ass_attribute28 => p_ass_attribute28
528 ,p_ass_attribute29 => p_ass_attribute29
529 ,p_ass_attribute30 => p_ass_attribute30
530 );
531
532 hr_utility.set_location ( 'After OAB Call' , 11 ) ;
533 hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 11 ) ;
534
535
536
537 -- Bug 3506363
538 -- end of OAB Code change
539
540 --
541 end insert_row;
542 -----------------------------------------------------------------------------
543 --
544 -- Delete Procedure
545 --
546 procedure delete_row(p_row_id varchar2,
547 p_assignment_id number,
548 p_new_end_date date,
549 p_effective_end_date date,
550 p_validation_end_date date,
551 p_session_date date,
552 p_delete_mode varchar2 ) is
553
554 l_cost_warning boolean; -- used to catch the cost warning from tidy_up_ref_int
555 -- but as Apl asg's can't have costing records no need
556 -- to return to caller.
557 begin
558 --
559 -- Addition of call to delete_child to ensure child rows are removed when parent is removed on mode of
560 -- 'ZAP'
561 -- SASmith 31-March-1998
562
563 hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 5 ) ;
564 delete_child ( p_assignment_id
565 ,p_delete_mode);
566
567 delete from per_assignments_f a
568 where a.rowid = chartorowid(P_ROW_ID);
569 --
570
571
572 if ( p_delete_mode = 'ZAP' ) then
573 return ; -- This case is handled by the form at present
574 hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 10 ) ;
575
576 elsif ( p_delete_mode in ('FUTURE_CHANGE','DELETE_NEXT_CHANGE' ) ) then
577 if ( p_new_end_date is null ) then
578 if ( p_validation_end_date = hr_general.end_of_time ) then
579 hr_assignment.tidy_up_ref_int ( p_assignment_id,
580 'FUTURE',
581 p_validation_end_date,
582 p_effective_end_date,
583 null,
584 null ,
585 l_cost_warning) ;
586 hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 15 ) ;
587 end if;
588 else
589 hr_assignment.tidy_up_ref_int ( p_assignment_id,
590 'FUTURE',
591 p_new_end_date,
592 p_effective_end_date,
593 null,
594 null,
595 l_cost_warning ) ;
596 hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 20 ) ;
597 end if;
598 --
599 if ( p_new_end_date is not null ) then
600 set_end_date ( p_new_end_date , p_assignment_id ) ;
601 hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 25 ) ;
602 end if;
603 --
604 cleanup_letters ( p_assignment_id ) ;
605 else
606 app_exception.invalid_argument( 'per_app_asg_pkg.delete_row',
607 'p_delete_mode',
608 p_delete_mode ) ;
609 end if;
610 --
611 end delete_row ;
612 -----------------------------------------------------------------------------
613 --
614 -- Standard lock procedure
615 --
616 procedure lock_row(
617 p_row_id varchar2,
618 p_assignment_id number,
619 p_effective_start_date date,
620 p_effective_end_date date,
621 p_business_group_id number,
622 p_recruiter_id number,
623 p_grade_id number,
624 p_position_id number,
625 p_job_id number,
626 p_assignment_status_type_id number,
627 p_location_id number,
628 p_person_referred_by_id number,
629 p_supervisor_id number,
630 p_person_id number,
631 p_recruitment_activity_id number,
632 p_source_organization_id number,
633 p_organization_id number,
634 p_people_group_id number,
635 p_vacancy_id number,
636 p_assignment_sequence number,
637 p_assignment_type varchar2,
638 p_primary_flag varchar2,
639 p_application_id number,
640 p_change_reason varchar2,
641 p_comment_id number,
642 p_date_probation_end date,
643 p_frequency varchar2,
644 p_manager_flag varchar2,
645 p_normal_hours number,
646 p_probation_period number,
647 p_probation_unit varchar2,
648 p_source_type varchar2,
649 p_time_normal_finish varchar2,
650 p_time_normal_start varchar2,
651 p_request_id number,
652 p_program_application_id number,
653 p_program_id number,
654 p_program_update_date date,
655 p_ass_attribute_category varchar2,
656 p_ass_attribute1 varchar2,
657 p_ass_attribute2 varchar2,
658 p_ass_attribute3 varchar2,
659 p_ass_attribute4 varchar2,
660 p_ass_attribute5 varchar2,
661 p_ass_attribute6 varchar2,
662 p_ass_attribute7 varchar2,
663 p_ass_attribute8 varchar2,
664 p_ass_attribute9 varchar2,
665 p_ass_attribute10 varchar2,
666 p_ass_attribute11 varchar2,
667 p_ass_attribute12 varchar2,
668 p_ass_attribute13 varchar2,
669 p_ass_attribute14 varchar2,
670 p_ass_attribute15 varchar2,
671 p_ass_attribute16 varchar2,
672 p_ass_attribute17 varchar2,
673 p_ass_attribute18 varchar2,
674 p_ass_attribute19 varchar2,
675 p_ass_attribute20 varchar2,
676 p_ass_attribute21 varchar2,
677 p_ass_attribute22 varchar2,
678 p_ass_attribute23 varchar2,
679 p_ass_attribute24 varchar2,
680 p_ass_attribute25 varchar2,
681 p_ass_attribute26 varchar2,
682 p_ass_attribute27 varchar2,
683 p_ass_attribute28 varchar2,
684 p_ass_attribute29 varchar2,
685 p_ass_attribute30 varchar2,
686 p_contract_id number,
687 p_cagr_id_flex_num number,
688 p_cagr_grade_def_id number,
689 p_establishment_id number,
690 p_collective_agreement_id number,
691 p_notice_period number,
692 p_notice_period_uom varchar2,
693 p_employee_category varchar2,
694 p_work_at_home varchar2,
695 p_job_post_source_name varchar2,
696 p_grade_ladder_pgm_id number,
697 p_supervisor_assignment_id number ) is
698
699 cursor ASS_CUR is
700 select *
701 from per_assignments_f a
702 where a.rowid = chartorowid(P_ROW_ID)
703 FOR UPDATE OF ASSIGNMENT_ID NOWAIT;
704 --
705 ass_rec ASS_CUR%rowtype;
706 --
707 begin
708 --
709 open ASS_CUR;
710 --
711 fetch ASS_CUR into ASS_REC;
712 --
713 if ASS_CUR%notfound then
714 close ASS_CUR;
715 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
716 fnd_message.set_token('PROCEDURE',
717 'PER_APP_ASG_PKG.LOCK_ROW');
718 fnd_message.set_token('STEP', '1');
719 fnd_message.raise_error;
720 end if;
721 close ASS_CUR;
722 --
723 ass_rec.assignment_type := rtrim(ass_rec.assignment_type);
724 ass_rec.primary_flag := rtrim(ass_rec.primary_flag);
725 ass_rec.change_reason := rtrim(ass_rec.change_reason);
726 ass_rec.frequency := rtrim(ass_rec.frequency);
727 ass_rec.manager_flag := rtrim(ass_rec.manager_flag);
728 ass_rec.probation_unit := rtrim(ass_rec.probation_unit);
729 ass_rec.source_type := rtrim(ass_rec.source_type);
730 ass_rec.time_normal_finish := rtrim(ass_rec.time_normal_finish);
731 ass_rec.time_normal_start := rtrim(ass_rec.time_normal_start);
732 ass_rec.ass_attribute_category := rtrim(ass_rec.ass_attribute_category);
733 ass_rec.ass_attribute1 := rtrim(ass_rec.ass_attribute1);
734 ass_rec.ass_attribute2 := rtrim(ass_rec.ass_attribute2);
735 ass_rec.ass_attribute3 := rtrim(ass_rec.ass_attribute3);
736 ass_rec.ass_attribute4 := rtrim(ass_rec.ass_attribute4);
737 ass_rec.ass_attribute5 := rtrim(ass_rec.ass_attribute5);
738 ass_rec.ass_attribute6 := rtrim(ass_rec.ass_attribute6);
739 ass_rec.ass_attribute7 := rtrim(ass_rec.ass_attribute7);
740 ass_rec.ass_attribute8 := rtrim(ass_rec.ass_attribute8);
741 ass_rec.ass_attribute9 := rtrim(ass_rec.ass_attribute9);
742 ass_rec.ass_attribute10 := rtrim(ass_rec.ass_attribute10);
743 ass_rec.ass_attribute11 := rtrim(ass_rec.ass_attribute11);
744 ass_rec.ass_attribute12 := rtrim(ass_rec.ass_attribute12);
745 ass_rec.ass_attribute13 := rtrim(ass_rec.ass_attribute13);
746 ass_rec.ass_attribute14 := rtrim(ass_rec.ass_attribute14);
747 ass_rec.ass_attribute15 := rtrim(ass_rec.ass_attribute15);
748 ass_rec.ass_attribute16 := rtrim(ass_rec.ass_attribute16);
749 ass_rec.ass_attribute17 := rtrim(ass_rec.ass_attribute17);
750 ass_rec.ass_attribute18 := rtrim(ass_rec.ass_attribute18);
751 ass_rec.ass_attribute19 := rtrim(ass_rec.ass_attribute19);
752 ass_rec.ass_attribute20 := rtrim(ass_rec.ass_attribute20);
753 ass_rec.ass_attribute21 := rtrim(ass_rec.ass_attribute21);
754 ass_rec.ass_attribute22 := rtrim(ass_rec.ass_attribute22);
755 ass_rec.ass_attribute23 := rtrim(ass_rec.ass_attribute23);
756 ass_rec.ass_attribute24 := rtrim(ass_rec.ass_attribute24);
757 ass_rec.ass_attribute25 := rtrim(ass_rec.ass_attribute25);
758 ass_rec.ass_attribute26 := rtrim(ass_rec.ass_attribute26);
759 ass_rec.ass_attribute27 := rtrim(ass_rec.ass_attribute27);
760 ass_rec.ass_attribute28 := rtrim(ass_rec.ass_attribute28);
761 ass_rec.ass_attribute29 := rtrim(ass_rec.ass_attribute29);
762 ass_rec.ass_attribute30 := rtrim(ass_rec.ass_attribute30);
763 ass_rec.contract_id := rtrim(ass_rec.contract_id);
764 ass_rec.cagr_id_flex_num := rtrim(ass_rec.cagr_id_flex_num);
765 ass_rec.cagr_grade_def_id := rtrim(ass_rec.cagr_grade_def_id);
766 ass_rec.establishment_id := rtrim(ass_rec.establishment_id);
767 ass_rec.collective_agreement_id := rtrim(ass_rec.collective_agreement_id);
768 ass_rec.notice_period := rtrim(ass_rec.notice_period);
769 ass_rec.notice_period_uom := rtrim(ass_rec.notice_period_uom);
770 ass_rec.employee_category := rtrim(ass_rec.employee_category);
771 ass_rec.work_at_home := rtrim(ass_rec.work_at_home);
772 ass_rec.job_post_source_name := rtrim(ass_rec.job_post_source_name);
773 ass_rec.grade_ladder_pgm_id := rtrim(ass_rec.grade_ladder_pgm_id);
774
775 --
776 if ( ((ass_rec.assignment_id = p_assignment_id)
777 or (ass_rec.assignment_id is null
778 and (p_assignment_id is null)))
779 and ((ass_rec.effective_start_date = p_effective_start_date)
780 or (ass_rec.effective_start_date is null
781 and (p_effective_start_date is null)))
782 and ((ass_rec.effective_end_date = p_effective_end_date)
783 or (ass_rec.effective_end_date is null
784 and (p_effective_end_date is null)))
785 and ((ass_rec.notice_period = p_notice_period)
786 or (ass_rec.notice_period is null
787 and (p_notice_period is null)))
788 and ((ass_rec.notice_period_uom = p_notice_period_uom)
789 or (ass_rec.notice_period_uom is null
790 and (p_notice_period_uom is null)))
791 and ((ass_rec.work_at_home = p_work_at_home)
792 or (ass_rec.work_at_home is null
793 and (p_work_at_home is null)))
794 and ((ass_rec.employee_category = p_employee_category)
795 or (ass_rec.employee_category is null
796 and (p_employee_category is null)))
797 and ((ass_rec.job_post_source_name = p_job_post_source_name)
798 or (ass_rec.job_post_source_name is null
799 and (p_job_post_source_name is null)))
800 and ((ass_rec.grade_ladder_pgm_id = p_grade_ladder_pgm_id)
801 or (ass_rec.grade_ladder_pgm_id is null
802 and (p_grade_ladder_pgm_id is null)))
803 and ((ass_rec.contract_id = p_contract_id)
804 or (ass_rec.contract_id is null
805 and (p_contract_id is null)))
806 and ((ass_rec.collective_agreement_id = p_collective_agreement_id)
807 or (ass_rec.collective_agreement_id is null
808 and (p_collective_agreement_id is null)))
809 and ((ass_rec.establishment_id = p_establishment_id)
810 or (ass_rec.establishment_id is null
811 and (p_establishment_id is null)))
812 and ((ass_rec.cagr_grade_def_id = p_cagr_grade_def_id)
813 or (ass_rec.cagr_grade_def_id is null
814 and (p_cagr_grade_def_id is null)))
815 and ((ass_rec.cagr_id_flex_num = p_cagr_id_flex_num)
816 or (ass_rec.cagr_id_flex_num is null
817 and (p_cagr_id_flex_num is null)))
818 and ((ass_rec.business_group_id = p_business_group_id)
819 or (ass_rec.business_group_id is null
820 and (p_business_group_id is null)))
821 and ((ass_rec.recruiter_id = p_recruiter_id)
822 or (ass_rec.recruiter_id is null
823 and (p_recruiter_id is null)))
824 and ((ass_rec.grade_id = p_grade_id)
825 or (ass_rec.grade_id is null
826 and (p_grade_id is null)))
827 and ((ass_rec.position_id = p_position_id)
828 or (ass_rec.position_id is null
829 and (p_position_id is null)))
830 and ((ass_rec.job_id = p_job_id)
831 or (ass_rec.job_id is null
832 and (p_job_id is null)))
833 and ((ass_rec.assignment_status_type_id = p_assignment_status_type_id)
834 or (ass_rec.assignment_status_type_id is null
835 and (p_assignment_status_type_id is null)))
836 and ((ass_rec.location_id = p_location_id)
837 or (ass_rec.location_id is null
838 and (p_location_id is null)))
839 and ((ass_rec.person_referred_by_id = p_person_referred_by_id)
840 or (ass_rec.person_referred_by_id is null
841 and (p_person_referred_by_id is null)))
842 and ((ass_rec.supervisor_id = p_supervisor_id)
843 or (ass_rec.supervisor_id is null
844 and (p_supervisor_id is null)))
845 and ((ass_rec.person_id = p_person_id)
846 or (ass_rec.person_id is null
847 and (p_person_id is null)))
848 and ((ass_rec.recruitment_activity_id = p_recruitment_activity_id)
849 or (ass_rec.recruitment_activity_id is null
850 and (p_recruitment_activity_id is null)))
851 and ((ass_rec.source_organization_id = p_source_organization_id)
852 or (ass_rec.source_organization_id is null
853 and (p_source_organization_id is null)))
854 and ((ass_rec.organization_id = p_organization_id)
855 or (ass_rec.organization_id is null
856 and (p_organization_id is null)))
857 and ((ass_rec.people_group_id = p_people_group_id)
858 or (ass_rec.people_group_id is null
859 and (p_people_group_id is null)))
860 and ((ass_rec.vacancy_id = p_vacancy_id)
861 or (ass_rec.vacancy_id is null
862 and (p_vacancy_id is null)))
863 and ((ass_rec.assignment_sequence = p_assignment_sequence)
864 or (ass_rec.assignment_sequence is null
865 and (p_assignment_sequence is null)))
866 and ((ass_rec.assignment_type = p_assignment_type)
867 or (ass_rec.assignment_type is null
868 and (p_assignment_type is null)))
869 and ((ass_rec.primary_flag = p_primary_flag)
870 or (ass_rec.primary_flag is null
871 and (p_primary_flag is null)))
872 and ((ass_rec.application_id = p_application_id)
873 or (ass_rec.application_id is null
874 and (p_application_id is null)))
875 and ((ass_rec.change_reason = p_change_reason)
876 or (ass_rec.change_reason is null
877 and (p_change_reason is null)))
878 and ((ass_rec.comment_id = p_comment_id)
879 or (ass_rec.comment_id is null
880 and (p_comment_id is null)))
881 and ((ass_rec.date_probation_end = p_date_probation_end)
882 or (ass_rec.date_probation_end is null
883 and (p_date_probation_end is null)))
884 and ((ass_rec.frequency = p_frequency)
885 or (ass_rec.frequency is null
886 and (p_frequency is null)))
887 and ((ass_rec.manager_flag = p_manager_flag)
888 or (ass_rec.manager_flag is null
889 and (p_manager_flag is null)))
890 and ((ass_rec.normal_hours = p_normal_hours)
891 or (ass_rec.normal_hours is null
892 and (p_normal_hours is null)))
893 and ((ass_rec.probation_period = p_probation_period)
894 or (ass_rec.probation_period is null
895 and (p_probation_period is null)))
896 and ((ass_rec.probation_unit = p_probation_unit)
897 or (ass_rec.probation_unit is null
898 and (p_probation_unit is null)))
899 and ((ass_rec.source_type = p_source_type)
900 or (ass_rec.source_type is null
901 and (p_source_type is null)))
902 and ((ass_rec.time_normal_finish = p_time_normal_finish)
903 or (ass_rec.time_normal_finish is null
904 and (p_time_normal_finish is null)))
905 and ((ass_rec.time_normal_start = p_time_normal_start)
906 or (ass_rec.time_normal_start is null
907 and (p_time_normal_start is null)))
908 and ((ass_rec.request_id = p_request_id)
909 or (ass_rec.request_id is null
910 and (p_request_id is null)))
911 and ((ass_rec.program_application_id = p_program_application_id)
912 or (ass_rec.program_application_id is null
913 and (p_program_application_id is null)))
914 and ((ass_rec.program_id = p_program_id)
915 or (ass_rec.program_id is null
916 and (p_program_id is null)))
917 and ((ass_rec.program_update_date = p_program_update_date)
918 or (ass_rec.program_update_date is null
919 and (p_program_update_date is null)))) then
920 if ( ((ass_rec.ass_attribute_category = p_ass_attribute_category)
921 or (ass_rec.ass_attribute_category is null
922 and (p_ass_attribute_category is null)))
923 and ((ass_rec.ass_attribute1 = p_ass_attribute1)
924 or (ass_rec.ass_attribute1 is null
925 and (p_ass_attribute1 is null)))
926 and ((ass_rec.ass_attribute2 = p_ass_attribute2)
927 or (ass_rec.ass_attribute2 is null
928 and (p_ass_attribute2 is null)))
929 and ((ass_rec.ass_attribute3 = p_ass_attribute3)
930 or (ass_rec.ass_attribute3 is null
931 and (p_ass_attribute3 is null)))
932 and ((ass_rec.ass_attribute4 = p_ass_attribute4)
933 or (ass_rec.ass_attribute4 is null
934 and (p_ass_attribute4 is null)))
935 and ((ass_rec.ass_attribute5 = p_ass_attribute5)
936 or (ass_rec.ass_attribute5 is null
937 and (p_ass_attribute5 is null)))
938 and ((ass_rec.ass_attribute6 = p_ass_attribute6)
939 or (ass_rec.ass_attribute6 is null
940 and (p_ass_attribute6 is null)))
941 and ((ass_rec.ass_attribute7 = p_ass_attribute7)
942 or (ass_rec.ass_attribute7 is null
943 and (p_ass_attribute7 is null)))
944 and ((ass_rec.ass_attribute8 = p_ass_attribute8)
945 or (ass_rec.ass_attribute8 is null
946 and (p_ass_attribute8 is null)))
947 and ((ass_rec.ass_attribute9 = p_ass_attribute9)
948 or (ass_rec.ass_attribute9 is null
949 and (p_ass_attribute9 is null)))
950 and ((ass_rec.ass_attribute10 = p_ass_attribute10)
951 or (ass_rec.ass_attribute10 is null
952 and (p_ass_attribute10 is null)))
953 and ((ass_rec.ass_attribute11 = p_ass_attribute11)
954 or (ass_rec.ass_attribute11 is null
955 and (p_ass_attribute11 is null)))
956 and ((ass_rec.ass_attribute12 = p_ass_attribute12)
957 or (ass_rec.ass_attribute12 is null
958 and (p_ass_attribute12 is null)))
959 and ((ass_rec.ass_attribute13 = p_ass_attribute13)
960 or (ass_rec.ass_attribute13 is null
961 and (p_ass_attribute13 is null)))
962 and ((ass_rec.ass_attribute14 = p_ass_attribute14)
963 or (ass_rec.ass_attribute14 is null
964 and (p_ass_attribute14 is null)))
965 and ((ass_rec.ass_attribute15 = p_ass_attribute15)
966 or (ass_rec.ass_attribute15 is null
967 and (p_ass_attribute15 is null)))
968 and ((ass_rec.ass_attribute16 = p_ass_attribute16)
969 or (ass_rec.ass_attribute16 is null
970 and (p_ass_attribute16 is null)))
971 and ((ass_rec.ass_attribute17 = p_ass_attribute17)
972 or (ass_rec.ass_attribute17 is null
973 and (p_ass_attribute17 is null)))
974 and ((ass_rec.ass_attribute18 = p_ass_attribute18)
975 or (ass_rec.ass_attribute18 is null
976 and (p_ass_attribute18 is null)))
977 and ((ass_rec.ass_attribute19 = p_ass_attribute19)
978 or (ass_rec.ass_attribute19 is null
979 and (p_ass_attribute19 is null)))
980 and ((ass_rec.ass_attribute20 = p_ass_attribute20)
981 or (ass_rec.ass_attribute20 is null
982 and (p_ass_attribute20 is null)))
983 and ((ass_rec.ass_attribute21 = p_ass_attribute21)
984 or (ass_rec.ass_attribute21 is null
985 and (p_ass_attribute21 is null)))
986 and ((ass_rec.ass_attribute22 = p_ass_attribute22)
987 or (ass_rec.ass_attribute22 is null
988 and (p_ass_attribute22 is null)))
989 and ((ass_rec.ass_attribute23 = p_ass_attribute23)
990 or (ass_rec.ass_attribute23 is null
991 and (p_ass_attribute23 is null)))
992 and ((ass_rec.ass_attribute24 = p_ass_attribute24)
993 or (ass_rec.ass_attribute24 is null
994 and (p_ass_attribute24 is null)))
995 and ((ass_rec.ass_attribute25 = p_ass_attribute25)
996 or (ass_rec.ass_attribute25 is null
997 and (p_ass_attribute25 is null)))
998 and ((ass_rec.ass_attribute26 = p_ass_attribute26)
999 or (ass_rec.ass_attribute26 is null
1000 and (p_ass_attribute26 is null)))
1001 and ((ass_rec.ass_attribute27 = p_ass_attribute27)
1002 or (ass_rec.ass_attribute27 is null
1003 and (p_ass_attribute27 is null)))
1004 and ((ass_rec.ass_attribute28 = p_ass_attribute28)
1005 or (ass_rec.ass_attribute28 is null
1006 and (p_ass_attribute28 is null)))
1007 and ((ass_rec.ass_attribute29 = p_ass_attribute29)
1008 or (ass_rec.ass_attribute29 is null
1009 and (p_ass_attribute29 is null)))
1010 and ((ass_rec.ass_attribute30 = p_ass_attribute30)
1011 or (ass_rec.ass_attribute30 is null
1012 and (p_ass_attribute30 is null)))
1013 ) then
1014 return; -- Row successfully locked, no changes
1015 end if;
1016 end if;
1017 --
1018 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1019 app_exception.raise_exception;
1020 --
1021 end lock_row ;
1022 -----------------------------------------------------------------------------
1023 --
1024 -- Standard update procedure
1025 --
1026 procedure update_row(
1027 p_row_id varchar2,
1028 p_assignment_id number,
1029 p_effective_start_date date,
1030 p_effective_end_date date,
1031 p_validation_start_date date,
1032 p_business_group_id number,
1033 p_recruiter_id number,
1034 p_grade_id number,
1035 p_position_id number,
1036 p_job_id number,
1037 p_assignment_status_type_id number,
1038 p_per_system_status varchar2,
1039 p_location_id number,
1040 p_location_code in out nocopy varchar2,
1041 p_person_referred_by_id number,
1042 p_supervisor_id number,
1043 p_person_id number,
1044 p_recruitment_activity_id number,
1045 p_source_organization_id number,
1046 p_organization_id number,
1047 p_people_group_id number,
1048 p_vacancy_id number,
1049 p_assignment_sequence number,
1050 p_assignment_type varchar2,
1051 p_primary_flag varchar2,
1052 p_application_id number,
1053 p_change_reason varchar2,
1054 p_comment_id number,
1055 p_date_probation_end date,
1056 p_frequency varchar2,
1057 p_frequency_meaning in out nocopy varchar2,
1058 p_manager_flag varchar2,
1059 p_normal_hours number,
1060 p_probation_period number,
1061 p_probation_unit varchar2,
1062 p_source_type varchar2,
1063 p_time_normal_finish varchar2,
1064 p_time_normal_start varchar2,
1065 p_request_id number,
1066 p_program_application_id number,
1067 p_program_id number,
1068 p_program_update_date date,
1069 p_ass_attribute_category varchar2,
1070 p_ass_attribute1 varchar2,
1071 p_ass_attribute2 varchar2,
1072 p_ass_attribute3 varchar2,
1073 p_ass_attribute4 varchar2,
1074 p_ass_attribute5 varchar2,
1075 p_ass_attribute6 varchar2,
1076 p_ass_attribute7 varchar2,
1077 p_ass_attribute8 varchar2,
1078 p_ass_attribute9 varchar2,
1079 p_ass_attribute10 varchar2,
1080 p_ass_attribute11 varchar2,
1081 p_ass_attribute12 varchar2,
1082 p_ass_attribute13 varchar2,
1083 p_ass_attribute14 varchar2,
1084 p_ass_attribute15 varchar2,
1085 p_ass_attribute16 varchar2,
1086 p_ass_attribute17 varchar2,
1087 p_ass_attribute18 varchar2,
1088 p_ass_attribute19 varchar2,
1089 p_ass_attribute20 varchar2,
1090 p_ass_attribute21 varchar2,
1091 p_ass_attribute22 varchar2,
1092 p_ass_attribute23 varchar2,
1093 p_ass_attribute24 varchar2,
1094 p_ass_attribute25 varchar2,
1095 p_ass_attribute26 varchar2,
1096 p_ass_attribute27 varchar2,
1097 p_ass_attribute28 varchar2,
1098 p_ass_attribute29 varchar2,
1099 p_ass_attribute30 varchar2,
1100 p_session_date date,
1101 p_status_changed boolean,
1102 p_contract_id number default null,
1103 p_cagr_id_flex_num number default null,
1104 p_cagr_grade_def_id number default null,
1105 p_establishment_id number default null,
1106 p_collective_agreement_id number default null,
1107 p_notice_period number default null,
1108 p_notice_period_uom varchar2 default null,
1109 p_employee_category varchar2 default null,
1110 p_work_at_home varchar2 default null,
1111 p_job_post_source_name varchar2 default null,
1112 p_grade_ladder_pgm_id number default null,
1113 p_supervisor_assignment_id number default null,
1114 p_payroll_id number default null, --Bug 4861490
1115 p_pay_basis_id number default null --Bug 4861490
1116 ) is
1117
1118 l_cost_warning boolean; -- used to catch the cost warning from tidy_up_ref_int
1119 -- but as Apl asg's can't have costing records no need
1120 -- to return to caller.
1121 l_previous_asg_status number; -- used to check if asg status changed
1122 l_assignment_status_id number; -- discards output from irc_asg_status api
1123 l_object_version_number number; -- ditto
1124 l_previous_vacancy_id number; -- Added for bug 3680947.
1125 -- Start of fix 3634447
1126 -- Cursor to get the current organization
1127 cursor current_org is
1128 select paf.organization_id
1129 from per_all_assignments_f paf
1130 where assignment_id = p_assignment_id
1131 and p_effective_start_date between effective_start_date
1132 and effective_end_date;
1133 --
1134 l_old_org_id per_all_assignments_f.organization_id%Type;
1135 --
1136 -- End of fix 3634447
1137 begin
1138 --
1139 -- PRE-UPDATE-CHECKS
1140 --
1141 hr_utility.set_location('Entering : per_app_asg_pkg.update_row' ,10);
1142 hr_utility.trace('p_grade_ladder_pgm_id : ' || p_grade_ladder_pgm_id);
1143 /* TEMP MOVED TO CLIENT FOR DEVELOPMENT
1144 check_current_applicant ( p_person_id => p_person_id,
1145 p_session_date => p_session_date ) ;
1146 */
1147 --
1148 select assignment_status_type_id, vacancy_id
1149 into l_previous_asg_status, l_previous_vacancy_id
1150 from per_assignments_f where rowid = chartorowid(P_ROW_ID);
1151
1152 -- Start of OAB code addition
1153 -- Whenever Applicant Information is getting changed/inserted via Applicant forms
1154 -- we need to trigger OAB Lifeevents. The following ben call will trigger LE
1155 -- Bug 3506363
1156
1157 hr_utility.set_location ( 'PER_APP_ASG_PKG.update_row' , 11 ) ;
1158 hr_utility.set_location ( 'Before OAB Call' , 11 ) ;
1159 ben_dt_trgr_handle.assignment
1160 (p_rowid => p_row_id
1161 ,p_assignment_id => p_assignment_id
1162 ,p_business_group_id => p_business_group_id
1163 ,p_person_id => p_person_id
1164 ,p_effective_start_date => p_effective_start_date
1165 ,p_effective_end_date => p_effective_end_date
1166 ,p_assignment_status_type_id => p_assignment_status_type_id
1167 ,p_assignment_type => p_assignment_type
1168 ,p_organization_id => p_organization_id
1169 ,p_primary_flag => p_primary_flag
1170 ,p_change_reason => p_change_reason
1171 ,p_employment_category => null
1172 ,p_frequency => p_frequency
1173 ,p_grade_id => p_grade_id
1174 ,p_job_id => p_job_id
1175 ,p_position_id => p_position_id
1176 ,p_location_id => p_location_id
1177 ,p_normal_hours => p_normal_hours
1178 ,p_payroll_id => p_payroll_id --Bug 4861490
1179 ,p_pay_basis_id => p_pay_basis_id -- Bug 4861490
1180 ,p_bargaining_unit_code => null
1181 ,p_labour_union_member_flag => null
1182 ,p_hourly_salaried_code => null
1183 ,p_people_group_id => p_people_group_id
1184 ,p_ass_attribute1 => p_ass_attribute1
1185 ,p_ass_attribute2 => p_ass_attribute2
1186 ,p_ass_attribute3 => p_ass_attribute3
1187 ,p_ass_attribute4 => p_ass_attribute4
1188 ,p_ass_attribute5 => p_ass_attribute5
1189 ,p_ass_attribute6 => p_ass_attribute6
1190 ,p_ass_attribute7 => p_ass_attribute7
1191 ,p_ass_attribute8 => p_ass_attribute8
1192 ,p_ass_attribute9 => p_ass_attribute9
1193 ,p_ass_attribute10 => p_ass_attribute10
1194 ,p_ass_attribute11 => p_ass_attribute11
1195 ,p_ass_attribute12 => p_ass_attribute12
1196 ,p_ass_attribute13 => p_ass_attribute13
1197 ,p_ass_attribute14 => p_ass_attribute14
1198 ,p_ass_attribute15 => p_ass_attribute15
1199 ,p_ass_attribute16 => p_ass_attribute16
1200 ,p_ass_attribute17 => p_ass_attribute17
1201 ,p_ass_attribute18 => p_ass_attribute18
1202 ,p_ass_attribute19 => p_ass_attribute19
1203 ,p_ass_attribute20 => p_ass_attribute20
1204 ,p_ass_attribute21 => p_ass_attribute21
1205 ,p_ass_attribute22 => p_ass_attribute22
1206 ,p_ass_attribute23 => p_ass_attribute23
1207 ,p_ass_attribute24 => p_ass_attribute24
1208 ,p_ass_attribute25 => p_ass_attribute25
1209 ,p_ass_attribute26 => p_ass_attribute26
1210 ,p_ass_attribute27 => p_ass_attribute27
1211 ,p_ass_attribute28 => p_ass_attribute28
1212 ,p_ass_attribute29 => p_ass_attribute29
1213 ,p_ass_attribute30 => p_ass_attribute30
1214 );
1215
1216 hr_utility.set_location ( 'After OAB Call' , 11 ) ;
1217
1218 -- Bug 3506363
1219 -- end of OAB Code change
1220 update per_assignments_f a
1221 set a.assignment_id = P_ASSIGNMENT_ID,
1222 a.effective_start_date = P_EFFECTIVE_START_DATE,
1223 a.effective_end_date = P_EFFECTIVE_END_DATE,
1224 a.business_group_id = P_BUSINESS_GROUP_ID,
1225 a.recruiter_id = P_RECRUITER_ID,
1226 a.grade_id = P_GRADE_ID,
1227 a.position_id = P_POSITION_ID,
1228 a.job_id = P_JOB_ID,
1229 a.assignment_status_type_id = P_ASSIGNMENT_STATUS_TYPE_ID,
1230 a.location_id = P_LOCATION_ID,
1231 a.person_referred_by_id = P_PERSON_REFERRED_BY_ID,
1232 a.supervisor_id = P_SUPERVISOR_ID,
1233 a.person_id = P_PERSON_ID,
1234 a.recruitment_activity_id = P_RECRUITMENT_ACTIVITY_ID,
1235 a.source_organization_id = P_SOURCE_ORGANIZATION_ID,
1236 a.organization_id = P_ORGANIZATION_ID,
1237 a.people_group_id = P_PEOPLE_GROUP_ID,
1238 a.vacancy_id = P_VACANCY_ID,
1239 a.assignment_sequence = P_ASSIGNMENT_SEQUENCE,
1240 a.assignment_type = P_ASSIGNMENT_TYPE,
1241 a.primary_flag = P_PRIMARY_FLAG,
1242 a.application_id = P_APPLICATION_ID,
1243 a.change_reason = P_CHANGE_REASON,
1244 a.comment_id = P_COMMENT_ID,
1245 a.date_probation_end = P_DATE_PROBATION_END,
1246 a.frequency = P_FREQUENCY,
1247 a.manager_flag = P_MANAGER_FLAG,
1248 a.normal_hours = P_NORMAL_HOURS,
1249 a.probation_period = P_PROBATION_PERIOD,
1250 a.probation_unit = P_PROBATION_UNIT,
1251 a.source_type = P_SOURCE_TYPE,
1252 a.time_normal_finish = P_TIME_NORMAL_FINISH,
1253 a.time_normal_start = P_TIME_NORMAL_START,
1254 a.request_id = P_REQUEST_ID,
1255 a.program_application_id = P_PROGRAM_APPLICATION_ID,
1256 a.program_id = P_PROGRAM_ID,
1257 a.program_update_date = P_PROGRAM_UPDATE_DATE,
1258 a.ass_attribute_category = P_ASS_ATTRIBUTE_CATEGORY,
1259 a.ass_attribute1 = P_ASS_ATTRIBUTE1,
1260 a.ass_attribute2 = P_ASS_ATTRIBUTE2,
1261 a.ass_attribute3 = P_ASS_ATTRIBUTE3,
1262 a.ass_attribute4 = P_ASS_ATTRIBUTE4,
1263 a.ass_attribute5 = P_ASS_ATTRIBUTE5,
1264 a.ass_attribute6 = P_ASS_ATTRIBUTE6,
1265 a.ass_attribute7 = P_ASS_ATTRIBUTE7,
1266 a.ass_attribute8 = P_ASS_ATTRIBUTE8,
1267 a.ass_attribute9 = P_ASS_ATTRIBUTE9,
1268 a.ass_attribute10 = P_ASS_ATTRIBUTE10,
1269 a.ass_attribute11 = P_ASS_ATTRIBUTE11,
1270 a.ass_attribute12 = P_ASS_ATTRIBUTE12,
1271 a.ass_attribute13 = P_ASS_ATTRIBUTE13,
1272 a.ass_attribute14 = P_ASS_ATTRIBUTE14,
1273 a.ass_attribute15 = P_ASS_ATTRIBUTE15,
1274 a.ass_attribute16 = P_ASS_ATTRIBUTE16,
1275 a.ass_attribute17 = P_ASS_ATTRIBUTE17,
1276 a.ass_attribute18 = P_ASS_ATTRIBUTE18,
1277 a.ass_attribute19 = P_ASS_ATTRIBUTE19,
1278 a.ass_attribute20 = P_ASS_ATTRIBUTE20,
1279 a.ass_attribute21 = P_ASS_ATTRIBUTE21,
1280 a.ass_attribute22 = P_ASS_ATTRIBUTE22,
1281 a.ass_attribute23 = P_ASS_ATTRIBUTE23,
1282 a.ass_attribute24 = P_ASS_ATTRIBUTE24,
1283 a.ass_attribute25 = P_ASS_ATTRIBUTE25,
1284 a.ass_attribute26 = P_ASS_ATTRIBUTE26,
1285 a.ass_attribute27 = P_ASS_ATTRIBUTE27,
1286 a.ass_attribute28 = P_ASS_ATTRIBUTE28,
1287 a.ass_attribute29 = P_ASS_ATTRIBUTE29,
1288 a.ass_attribute30 = P_ASS_ATTRIBUTE30,
1289 a.collective_agreement_id = P_COLLECTIVE_AGREEMENT_ID,
1290 a.cagr_grade_def_id = P_CAGR_GRADE_DEF_ID,
1291 a.establishment_id = P_ESTABLISHMENT_ID,
1292 a.contract_id = P_CONTRACT_ID,
1293 a.cagr_id_flex_num = P_CAGR_ID_FLEX_NUM,
1294 a.notice_period = P_NOTICE_PERIOD,
1295 a.notice_period_uom = P_NOTICE_PERIOD_UOM,
1296 a.work_at_home = P_WORK_AT_HOME,
1297 a.employee_category = P_EMPLOYEE_CATEGORY,
1298 a.job_post_source_name = P_JOB_POST_SOURCE_NAME,
1299 a.grade_ladder_pgm_id = p_grade_ladder_pgm_id,
1300 a.supervisor_assignment_id = p_supervisor_assignment_id,
1301 a.payroll_id = p_payroll_id, --Bug 4861490
1302 a.pay_basis_id = p_pay_basis_id --Bug 4861490
1303 where a.rowid = chartorowid(P_ROW_ID);
1304
1305 -- Start of fix 3634447
1306 -- Get the current organization
1307 open current_org;
1308 fetch current_org into l_old_org_id;
1309 close current_org;
1310 -- Updating security permission when organization is changed
1311 -- from business group to other organization.
1312 if l_old_org_id = p_business_group_id and
1313 p_organization_id <> p_business_group_id then
1314 --
1315 hr_utility.set_location ('per_app_asg_pkg.update_row', 12);
1316 hr_security_internal.clear_from_person_list(
1317 p_person_id => p_person_id);
1318 --
1319 end if;
1320 hr_utility.set_location ('per_app_asg_pkg.update_row', 13);
1321 hr_security_internal.add_to_person_list(
1322 p_effective_date => p_effective_start_date,
1323 p_assignment_id => p_assignment_id);
1324 hr_utility.set_location ('per_app_asg_pkg.update_row', 14);
1325 -- End of fix 3634447
1326
1327 -- insert into irc_assignment_statuses if the assignment status has changed
1328 --
1329 if l_previous_asg_status <> p_assignment_status_type_id then
1330 --
1331 -- 3652025: if terminated, client already performed this step
1332 -- when calling terminate_apl_asg API.
1333 --
1334 if p_per_system_status <> 'TERM_APL' then
1335 IRC_ASG_STATUS_API.create_irc_asg_status
1336 ( p_validate => FALSE
1337 , p_assignment_id => p_assignment_id
1338 , p_assignment_status_type_id => p_assignment_status_type_id
1339 , p_status_change_date => p_effective_start_date -- 2754362 p_effective_end_date
1340 , p_status_change_reason => p_change_reason
1341 , p_assignment_status_id => l_assignment_status_id
1342 , p_object_version_number => l_object_version_number
1343 );
1344 end if;
1345 end if;
1346 --
1347 -- Fix for bug 3680947 starts here.
1348 --
1349 IF ( p_status_changed ) or ( p_per_system_status = 'TERM_APL' )
1350 OR nvl(l_previous_vacancy_id,-1) <> nvl(p_vacancy_id,-1)
1351 THEN
1352 --
1353 IF nvl(l_previous_vacancy_id,-1) <> nvl(p_vacancy_id,-1) THEN
1354 --
1355 delete from per_letter_request_lines plrl
1356 where plrl.assignment_id = p_assignment_id
1357 and plrl.assignment_status_type_id = p_assignment_status_type_id
1358 and exists
1359 (select null
1360 from per_letter_requests plr
1361 where plr.letter_request_id = plrl.letter_request_id
1362 and plr.request_status = 'PENDING'
1363 and plr.auto_or_manual = 'AUTO');
1364 --
1365 END IF;
1366 --
1367 cleanup_letters( p_assignment_id => p_assignment_id);
1368 --
1369 --Performance Fix done by removing +0
1370 delete from per_letter_requests plr
1371 where plr.business_group_id = p_business_group_id
1372 and plr.request_status = 'PENDING'
1373 and plr.auto_or_manual = 'AUTO'
1374 and not exists
1375 ( select 1
1376 from per_letter_request_lines plrl
1377 where plrl.letter_request_id = plr.letter_request_id
1378 ) ;
1379 --
1380 per_applicant_pkg.check_for_letter_requests
1381 (p_business_group_id => p_business_group_id
1382 ,p_per_system_status => p_per_system_status
1383 ,p_assignment_status_type_id => p_assignment_status_type_id
1384 ,p_person_id => p_person_id
1385 ,p_assignment_id => p_assignment_id
1386 ,p_effective_start_date => p_effective_start_date
1387 ,p_validation_start_date => p_validation_start_date
1388 ,p_vacancy_id => p_vacancy_id
1389 );
1390 --
1391 END IF;
1392 --
1393 -- Fix for bug 3680947 ends here.
1394 --
1395 -- Set the location code if the location id is not null and the code is
1396 -- null
1397 if ( ( p_location_id is not null ) and ( p_location_code is null ) ) then
1398 p_location_code := per_applicant_pkg.get_location_code ( p_location_id ) ;
1399 end if;
1400 --
1401 -- Set the frequency meaning if the frequency is not null and the meaning is
1402 -- null
1403 if ( ( p_frequency is not null ) and ( p_frequency_meaning is null ) ) then
1404 p_frequency_meaning := hr_general.decode_lookup( 'FREQUENCY',
1405 p_frequency ) ;
1406 end if;
1407 --
1408 --
1409 if ( p_per_system_status = 'TERM_APL' ) then
1410 hr_assignment.tidy_up_ref_int ( p_assignment_id ,
1411 'END',
1412 p_session_date,
1413 p_session_date,
1414 null,
1415 null,
1416 l_cost_warning ) ;
1417 end if;
1418
1419 --
1420
1421 hr_utility.set_location(' Leaving : per_app_asg_pkg.update_row' ,100);
1422 --
1423 end update_row;
1424 --
1425 -------------------------------------------------------------------------
1426 --- Validation Procedures ----
1427 -------------------------------------------------------------------------
1428 procedure check_apl_update_allowed( p_application_id in number,
1429 p_assignment_id in number,
1430 p_person_id in number,
1431 p_status out nocopy varchar2 ) is
1432
1433 cursor get_max_apl_date is
1434 select max(effective_end_date)
1435 from per_assignments_f paf,
1436 per_assignment_status_types past
1437 where paf.application_id = p_application_id
1438 and paf.assignment_id = p_assignment_id
1439 and paf.assignment_type = 'A'
1440 and paf.assignment_status_type_id = past.assignment_status_type_id
1441 and past.per_system_status in ('ACTIVE_APL','ACCEPTED',
1442 'INTERVIEW1','INTERVIEW2','OFFER');
1443
1444 max_apl_date date;
1445
1446 begin
1447
1448 p_status := 'UNKNOWN';
1449
1450 open get_max_apl_date;
1451 fetch get_max_apl_date into max_apl_date;
1452 if get_max_apl_date%notfound then
1453 close get_max_apl_date;
1454 hr_utility.set_message(800,'HR_52377_NOT_ACTIVE_APPLICANT');
1455 hr_utility.raise_error;
1456 end if;
1457 close get_max_apl_date;
1458
1459 if max_apl_date = hr_general.end_of_time
1460 then
1461 p_status := 'UPD_OR_CORR';
1462 elsif hr_general2.is_person_type(p_person_id,
1463 'EMP',
1464 max_apl_date + 1)
1465 then
1466 p_status := 'CORR_ONLY';
1467 else
1468 --
1469 -- Fix for bug 3306906 starts here.
1470 -- Pass p_status as CORR_ONLY for the terminated application.
1471 --
1472 p_status := 'CORR_ONLY';
1473 --hr_utility.set_message(800,'HR_52377_NOT_ACTIVE_APPLICANT');
1474 --hr_utility.raise_error;
1475 --
1476 -- Fix for bug 3306906 ends here.
1477 --
1478 end if;
1479
1480 end check_apl_update_allowed;
1481 --
1482 procedure check_apl_end_date ( p_application_id in number ) is
1483 l_dummy number ;
1484 cursor c1 is
1485 select 1
1486 from per_applications a
1487 where a.application_id = p_application_id
1488 and a.date_end is null ;
1489 begin
1490 --
1491 open c1 ;
1492 fetch c1 into l_dummy ;
1493 if c1%notfound then
1494 close c1 ;
1495 hr_utility.set_message(800,'HR_52377_NOT_ACTIVE_APPLICANT');
1496 hr_utility.raise_error ;
1497 end if;
1498 close c1 ;
1499 --
1500 end check_apl_end_date ;
1501 --
1502 procedure check_current_applicant ( p_person_id in number,
1503 p_session_date in date ) is
1504 l_dummy number ;
1505 cursor c1 is
1506 select 1
1507 from per_people_f
1508 where person_id = p_person_id
1509 and current_applicant_flag = 'Y'
1510 and p_session_date
1511 between effective_start_date and effective_end_date ;
1512 begin
1513 --
1514 open c1 ;
1515 fetch c1 into l_dummy ;
1516 if c1%notfound then
1517 close c1 ;
1518 hr_utility.set_message(801,'HR_6067_APP_ASS_APPL_ENDED');
1519 hr_utility.raise_error ;
1520 end if;
1521 close c1 ;
1522 --
1523 end check_current_applicant ;
1524 --
1525 procedure check_valid_asg_status ( p_business_group_id in number,
1526 p_legislation_code in varchar2,
1527 p_assignment_status_type_id in number,
1528 p_per_system_status in varchar2 ) is
1529 l_dummy number ;
1530 cursor c1 is
1531 select 1
1532 from per_assignment_status_types a
1533 , per_ass_status_type_amends b
1534 where b.assignment_status_type_id(+) = a.assignment_status_type_id
1535 and b.business_group_id(+) + 0 = p_business_group_id
1536 and nvl(a.business_group_id,p_business_group_id) = p_business_group_id
1537 and nvl(a.legislation_code,p_legislation_code) = p_legislation_code
1538 and nvl(b.active_flag,a.active_flag) = 'Y'
1539 and nvl(b.per_system_status,a.per_system_status) = p_per_system_status
1540 and a.assignment_status_type_id = p_assignment_status_type_id ;
1541 begin
1542 --
1543 open c1 ;
1544 fetch c1 into l_dummy ;
1545 if c1%notfound then
1546 close c1 ;
1547 -- ***TEMP hr_utility.set_message gives up with a value error
1548 -- for this message
1549 fnd_message.set_name('PAY','HR_6073_APP_ASS_INVALID_STATUS' );
1550 app_exception.raise_exception ;
1551 end if;
1552 close c1 ;
1553 --
1554 end check_valid_asg_status ;
1555 --
1556 procedure check_future_stat_change ( p_assignment_id in number ) is
1557 l_dummy number ;
1558 cursor c1 is
1559 select 1
1560 from per_assignments_f a
1561 where assignment_id = p_assignment_id
1562 and exists
1563 (select null
1564 from per_assignment_status_types b
1565 where b.per_system_status in ('TERM_APL','ACTIVE_ASSIGN')
1566 and a.assignment_status_type_id = b.assignment_status_type_id) ;
1567 begin
1568 --
1569 open c1 ;
1570 fetch c1 into l_dummy ;
1571 if c1%found then
1572 close c1 ;
1573 fnd_message.set_name ( 'PAY', 'HR_6083_APP_ASS_APPL_STAT_END' );
1574 app_exception.raise_exception ;
1575 end if;
1576 close c1 ;
1577 --
1578 end check_future_stat_change ;
1579 --
1580 procedure check_end_date ( p_assignment_id in number ,
1581 p_warning_set out nocopy boolean ) is
1582 l_dummy_date date := NULL ;
1583 cursor c1 is
1584 select max(effective_end_date)
1585 from per_assignments_f
1586 where assignment_id = p_assignment_id ;
1587 begin
1588 --
1589 open c1 ;
1590 fetch c1 into l_dummy_date ;
1591 close c1 ;
1592 if ( l_dummy_date < hr_general.end_of_time ) then
1593 hr_utility.set_message ( 801 , 'HR_ASS_FUTURE_END' ) ;
1594 hr_utility.set_warning ;
1595 p_warning_set := true ;
1596 else
1597 p_warning_set := false ;
1598 end if;
1599 --
1600 end check_end_date ;
1601 --
1602 procedure check_assignment_continuity ( p_business_group_id in number,
1603 p_assignment_id in number,
1604 p_person_id in number,
1605 p_max_end_date in date,
1606 p_session_date in date ) is
1607 l_dummy number ;
1608 l_target_end_date date := null ;
1609 l_max_end_date date := p_max_end_date ;
1610 --
1611 cursor c1 is
1612 select 1
1613 from sys.dual
1614 where exists ( select 1
1615 from per_assignments_f
1616 where assignment_id <> p_assignment_id ) ;
1617 --
1618 cursor c2 is
1619 select nvl(a.date_end,to_date('31/12/4712','DD/MM/YYYY'))
1620 from per_applications a
1621 where a.person_id = p_person_id
1622 and p_session_date
1623 between a.date_received
1624 and nvl(a.date_end,p_session_date)
1625 and a.business_group_id + 0 = p_business_group_id ;
1626 --
1627 cursor c3 is
1628 select max(a.effective_end_date)
1629 from per_all_assignments_f a
1630 where a.person_id = p_person_id
1631 and a.business_group_id + 0 = p_business_group_id
1632 and a.assignment_id <> p_assignment_id
1633 and a.assignment_type = 'A'
1634 and l_max_end_date + 1
1635 between a.effective_start_date
1636 and a.effective_end_date ;
1637 begin
1638 --
1639 -- Check that there is at least one other assignment for this person
1640 open c1 ;
1641 fetch c1 into l_dummy ;
1642 if c1%notfound then
1643 close c1 ;
1644 hr_utility.set_message(801,'HR_7075_APL_ASS_ONLY_ASS' ) ;
1645 hr_utility.raise_error ;
1646 end if;
1647 close c1 ;
1648 --
1649 -- Get the end date of the given application
1650 open c2 ;
1651 fetch c2 into l_target_end_date ;
1652 if c2%notfound then
1653 close c2 ;
1654 hr_utility.set_message(801,'HR_6078_ALL_ZONE_TRIGGER_FAIL');
1655 hr_utility.set_message_token('TRIGGER' , 'check_assignment_continuity' );
1656 hr_utility.raise_error ;
1657 end if;
1658 close c2 ;
1659 --
1660 --
1661 loop
1662 --
1663 exit when l_target_end_date = l_max_end_date ;
1664 --
1665 open c3 ;
1666 fetch c3 into l_max_end_date ;
1667 close c3 ;
1668 --
1669 if ( l_max_end_date is null ) then
1670 hr_utility.set_message(801,'HR_6069_APP_ASS_NO_CONTIN');
1671 hr_utility.raise_error ;
1672 end if ;
1673 --
1674 end loop ;
1675 --
1676 end check_assignment_continuity ;
1677 --
1678 --
1679 procedure process_end_status ( p_business_group_id in number,
1680 p_assignment_id in number,
1681 p_person_id in number,
1682 p_max_end_date in date,
1683 p_session_date in date,
1684 p_application_id in number) is
1685 --
1686 l_exists varchar2(10);
1687 --
1688 cursor csr_hire_exists(cp_asg_id number, cp_effective_date date) is
1689 select 'Y' from per_all_assignments_f
1690 where assignment_id = cp_asg_id
1691 and assignment_type = 'E'
1692 and effective_start_date > cp_effective_date;
1693 --
1694 cursor csr_other_asgs(cp_asg_id number, cp_appl_id number) is
1695 select 'Y' from per_assignments_f apl
1696 where apl.assignment_type = 'A'
1697 and apl.application_id = cp_appl_id
1698 and apl.assignment_id <> cp_asg_id
1699 and (apl.effective_end_date = hr_general.end_of_time
1700 or exists
1701 (select 'Y' from per_applications apa
1702 where apa.application_id = cp_appl_id
1703 and apa.date_end is not null
1704 and apa.date_end >= apl.effective_end_date));
1705
1706 begin
1707 --
1708 -- check_assignment_continuity ( p_business_group_id, -- 3652025
1709 -- p_assignment_id,
1710 -- p_person_id,
1711 -- p_max_end_date,
1712 -- p_session_date ) ;
1713 --
1714 --
1715 --
1716 -- check whether this assignment has been hired into EMP
1717 --
1718 l_exists := 'N';
1719 open csr_hire_exists(p_assignment_id, p_session_date+1);
1720 fetch csr_hire_exists into l_exists;
1721 if csr_hire_exists%FOUND then
1722 close csr_hire_exists;
1723 hr_utility.set_message(800,'HR_6071_APP_ASS_INVALID_END');
1724 hr_utility.raise_error;
1725 else
1726 close csr_hire_exists;
1727 end if;
1728 --
1729 -- check whether there are other assignments
1730 --
1731 open csr_other_asgs(p_assignment_id, p_application_id);
1732 fetch csr_other_asgs into l_exists;
1733 if csr_other_asgs%NOTFOUND then
1734 close csr_other_asgs;
1735 hr_utility.set_message(800,'HR_7075_APL_ASS_ONLY_ASS');
1736 hr_utility.raise_error;
1737 else
1738 close csr_other_asgs;
1739 end if;
1740 --
1741 hr_assignment.del_ref_int_check ( p_assignment_id,
1742 'END',
1743 p_session_date ) ;
1744 --
1745 end process_end_status ;
1746 --
1747 --
1748 -- ***temp OBSOLETE ?
1749 function rec_act_has_source_type ( p_recruitment_activity_id in number,
1750 p_source_type in varchar2 )
1751 return boolean is
1752 begin
1753 return true ;
1754 end ;
1755 --
1756 procedure key_delrec ( p_business_group_id in number,
1757 p_assignment_id in number,
1758 p_person_id in number,
1759 p_session_date in date,
1760 p_validation_start_date in date,
1761 p_delete_mode in varchar2 ) is
1762 l_max_end_date date ;
1763 cursor c1 is
1764 select min(effective_start_date)-1
1765 from per_assignments_f
1766 where assignment_id = p_assignment_id ;
1767 begin
1768 --
1769 if ( p_delete_mode in ( 'FUTURE_CHANGE' , 'DELETE_NEXT_CHANGE' ) ) then
1770 --
1771 check_future_stat_change( p_assignment_id => p_assignment_id ) ;
1772 --
1773 elsif ( p_delete_mode = 'ZAP' ) then
1774 --
1775 check_future_stat_change( p_assignment_id => p_assignment_id ) ;
1776 open c1 ;
1777 fetch c1 into l_max_end_date ;
1778 close c1 ;
1779 --
1780 check_assignment_continuity ( p_business_group_id,
1781 p_assignment_id,
1782 p_person_id,
1783 l_max_end_date,
1784 p_session_date ) ;
1785 --
1786 hr_assignment.del_ref_int_check ( p_assignment_id,
1787 'ZAP',
1788 p_validation_start_date ) ;
1789 --
1790 else app_exception.invalid_argument( 'PER_APP_ASG_PKG.KEY_DELREC',
1791 'p_delete_mode',
1792 p_delete_mode ) ;
1793 end if;
1794 --
1795 end key_delrec ;
1796 --
1797 procedure pre_delete_validation ( p_business_group_id in number,
1798 p_assignment_id in number,
1799 p_application_id in number,
1800 p_person_id in number,
1801 p_session_date in date,
1802 p_validation_start_date in date,
1803 p_validation_end_date in date,
1804 p_delete_mode in varchar2,
1805 p_new_end_date in out nocopy date ) is
1806 l_max_end_date date ;
1807 cursor c1 is
1808 select min(effective_start_date)-1
1809 from per_assignments_f
1810 where assignment_id = p_assignment_id ;
1811 --
1812 procedure check_appl_term ( p_application_id in number,
1813 p_validation_end_date in date ,
1814 p_new_end_date in out nocopy date ) is
1815 cursor c1 is
1816 select date_end
1817 from per_applications
1818 where application_id = p_application_id
1819 and nvl(date_end,to_date('31/12/4712','DD/MM/YYYY'))
1820 < p_validation_end_date ;
1821 begin
1822 --
1823 open c1 ;
1824 fetch c1 into p_new_end_date ;
1825 close c1 ;
1826 end check_appl_term ;
1827 --
1828 begin
1829 --
1830 if ( p_delete_mode in ( 'FUTURE_CHANGE' , 'DELETE_NEXT_CHANGE' ) ) then
1831 --
1832 check_future_stat_change( p_assignment_id => p_assignment_id ) ;
1833 check_appl_term ( p_application_id => p_application_id,
1834 p_validation_end_date => p_validation_end_date,
1835 p_new_end_date => p_new_end_date ) ;
1836 --
1837 elsif ( p_delete_mode = 'ZAP' ) then
1838 --
1839 check_future_stat_change( p_assignment_id => p_assignment_id ) ;
1840 open c1 ;
1841 fetch c1 into l_max_end_date ;
1842 close c1 ;
1843 --
1844 check_assignment_continuity ( p_business_group_id,
1845 p_assignment_id,
1846 p_person_id,
1847 l_max_end_date,
1848 p_session_date ) ;
1849 --
1850 hr_assignment.del_ref_int_check ( p_assignment_id,
1851 'ZAP',
1852 p_validation_start_date ) ;
1853 --
1854 else app_exception.invalid_argument( 'PER_APP_ASG_PKG.PRE_DELETE_VALIDATION',
1855 'p_delete_mode',
1856 p_delete_mode ) ;
1857 end if;
1858 --
1859 end pre_delete_validation ;
1860 --
1861 --
1862 procedure post_delete ( p_assignment_id in number,
1863 p_validation_start_date in date ) is
1864
1865 l_out_parameter boolean; -- Out parmater used to warn is future changes to the spinal
1866 -- point placements will be lost, as assignment this will
1867 -- not be used only catched locally.
1868 begin
1869 --
1870 hr_assignment.del_ref_int_delete ( p_assignment_id,
1871 NULL,
1872 'ZAP',
1873 p_validation_start_date,
1874 null,
1875 null,
1876 null,
1877 null,
1878 null,
1879 null,
1880 l_out_parameter
1881 ) ;
1882
1883 cleanup_letters ( p_assignment_id ) ;
1884 --
1885 end post_delete ;
1886 --
1887 --
1888 procedure chk_upd_mode ( p_event varchar2,
1889 p_object varchar2,
1890 p_assignment_id number,
1891 p_effective_start_date date,
1892 p_update_mode varchar2,
1893 p_record_status varchar2,
1894 p_per_system_status varchar2,
1895 p_allowed out nocopy varchar2 ) IS
1896 --
1897 l_dummy varchar2(1) := 'N';
1898 cursor csr_chk_mode is
1899 select 'Y' from per_all_assignments_f a
1900 where p_assignment_id = a.assignment_id
1901 and (a.effective_start_date < p_effective_start_date);
1902 --
1903 begin
1904 if p_object = 'ASSGT' then
1905 open csr_chk_mode;
1906 fetch csr_chk_mode into l_dummy;
1907 if p_event = 'WHEN-VALIDATE-ITEM'
1908 and ( p_update_mode = 'UPDATE' or p_update_mode is null OR l_dummy = 'Y' ) then
1909 --
1910 p_allowed := 'STATUSES_UPDATE';
1911 --
1912 elsif p_event = 'WHEN-NEW-RECORD-INSTANCE'
1913 and p_record_status in ( 'QUERY' , 'CHANGED' )
1914 and (l_dummy = 'Y' or p_update_mode = 'UPDATE'or p_update_mode is null) then
1915 --
1916 p_allowed := 'STATUSES_UPDATE';
1917 --
1918 else
1919 --
1920 p_allowed := 'STATUSES_INSERT';
1921 --
1922 end if;
1923 close csr_chk_mode;
1924 l_dummy := 'N';
1925 elsif ( p_event = 'WHEN-VALIDATE-ITEM' and p_object = 'STATUS' and p_per_system_status = 'TERM_APL') then
1926 open csr_chk_mode;
1927 fetch csr_chk_mode into l_dummy;
1928 if p_update_mode = 'CORRECTION' and l_dummy <> 'Y' then
1929 p_allowed := 'STATUSES_INSERT';
1930 -- 3652025: allow termination on the same day
1931 -- hr_utility.set_message(801,'HR_6006_APP_ASS_INVL_FIRST_STA' );
1932 -- hr_utility.raise_error ;
1933 else
1934 p_allowed := 'STATUSES_UPDATE';
1935 end if;
1936 close csr_chk_mode;
1937 l_dummy := 'N';
1938
1939 end if;
1940 end chk_upd_mode;
1941
1942
1943
1944
1945
1946 --
1947 end PER_APP_ASG_PKG ;