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