1 PACKAGE BODY PER_BULK_APP_ASG_CHANGE_PKG AS
2 /* $Header: peasg03t.pkb 115.13 2004/06/16 01:07:57 adudekul ship $ */
3 --
4 -- PRIVATE FUNCTIONS
5 --
6 -- This procedure used during development only
7 procedure Message( p_msg in varchar2 ) is
8 begin
9 --dbms_output.put_line ( p_msg ) ;
10 null;
11 end message ;
12 --
13 -- Name
14 -- exists_other_active_asg
15 -- Purpose
16 -- Returns TRUE if there are other active assignments for this
17 -- application
18 -- Arguments
19 -- p_assignment_id
20 --
21 function exists_other_active_asg ( p_application_id in number,
22 p_person_id in number,
23 p_assignment_id in number )
24 return boolean is
25 --
26 l_return_status boolean ;
27 l_dummy_date date ;
28 --
29 -- Retrieves the latest end date of any applicant assignment for the
30 -- application which is not the current assignment.
31 --
32 cursor c1 is
33 select max(effective_end_date)
34 from per_all_assignments_f
35 where person_id = p_person_id
36 and application_id = p_application_id
37 and assignment_id <> p_assignment_id
38 and assignment_type = 'A' ;
39 begin
40 open c1 ;
41 fetch c1 into l_dummy_date ;
42 close c1 ;
43 if ( l_dummy_date is not null
44 and l_dummy_date = hr_general.end_of_time ) then
45 l_return_status := TRUE ;
46 else
47 l_return_status := FALSE ;
48 end if;
49 --
50 return( l_return_status ) ;
51 --
52 end exists_other_active_asg ;
53 --
54 --
55 -- Name
56 -- chk_future_asg_changes
57 -- Purpose
58 -- Checks whether there are any future assignment changes to
59 -- the given assignment. If there are any then an error is raised.
60 -- Arguments
61 -- p_assignment_id
62 --
63 procedure chk_future_asg_changes ( p_assignment_id in number ) is
64 l_dummy number ;
65 cursor c1 is
66 select 1
67 from per_all_assignments_f a,
68 fnd_sessions f
69 where a.assignment_id = p_assignment_id
70 and f.session_id = userenv('sessionid')
71 and a.effective_start_date > f.effective_date ;
72 begin
73 --
74 message('checking for future assignment changes');
75 open c1 ;
76 fetch c1 into l_dummy ;
77 if c1%found then
78 close c1 ;
79 hr_utility.set_message ( 801, 'HR_6408_APPS_NO_UPDATE' ) ;
80 hr_utility.set_message_token ( 'TYPE' , 'recruiter or status');
81 hr_utility.raise_error ;
82 end if;
83 close c1 ;
84 --
85 end chk_future_asg_changes ;
86 --
87
88 -- Name
89 -- chk_asg_status_change
90 -- Purpose
91 -- Validates change to assignment status type
92 -- Checks the following :
93 -- 1. The assignment must not have already ended.
94 -- 2. If this is the first status for the assignment it must be
95 -- ACTIVE_APL. ie if the datetrack update mode is correction then
96 -- the current row must not be the first for that assignment.
97 -- 3. If the new system status is TERM_APL then
98 -- i) There must be no future person changes.
99 -- ii) Assignment Continuity must not be broken.
100 -- Arguments
101 procedure chk_asg_status_change(p_application_id in number,
102 p_person_id in number,
103 p_assignment_id in number,
104 p_per_system_status in varchar2,
105 p_asg_status_type_id in varchar2,
106 p_dt_update_mode in varchar2,
107 p_business_group_id in number ) is
108 --
109 --
110 procedure chk_assignment_current ( p_assignment_id in number ) is
111 l_asg_max_end_date date ;
112 -- Retrieve latest end date of assignment
113 cursor c1 is
114 select max(effective_end_date)
115 from per_all_assignments_f
116 where assignment_id = p_assignment_id ;
117 begin
118 --
119 open c1 ;
120 fetch c1 into l_asg_max_end_date ;
121 close c1 ;
122 --
123 if ( l_asg_max_end_date < hr_general.end_of_time ) then
124 hr_utility.set_message(801, 'HR_6751_APP_TERM_ALREADY');
125 hr_utility.raise_error;
126 end if;
127 --
128 end chk_assignment_current ;
129 --
130 --
131 --
132 procedure term_apl_checks ( p_application_id in number,
133 p_person_id in number,
134 p_assignment_id in number,
135 p_business_group_id in number ) is
136 l_dummy_date date ;
137 l_effective_date date ;
138 -- Commented out as part of fix to bug 677744.
139 -- l_max_end_date date ;
140 --
141 -- Check to see whether there any changes to the person table
142 -- after the current date
143 --
144 cursor c1 is
145 select f.effective_date
146 from per_people_f p,
147 fnd_sessions f
148 where p.person_id = p_person_id
149 and p.effective_start_date > f.effective_date
150 and f.session_id = userenv('sessionid') ;
151 --
152 --
153 -- Retrieve the effective date
154 cursor c3 is
155 select effective_date
156 from fnd_sessions
157 where session_id = userenv('sessionid');
158 --
159 --
160 -- This cursor retrieves the day before the earliest start date for
161 -- the given assignment
162 --
163 -- Commented out as part of fix for bug 677744. This cursor is no longer
164 -- required.
165 -- cursor c4 is
166 -- select min(effective_start_date) - 1
167 -- from per_all_assignments_f
168 -- where assignment_id = p_assignment_id ;
169 -- End of this part of fix.
170 --
171 begin
172 --
173 -- Check that there are no future person changes.
174 --
175 open c1 ;
176 fetch c1 into l_dummy_date ;
177 if ( c1%found ) then
178 close c1 ;
179 hr_utility.set_message(801,'HR_6382_APP_TERM_FUTURE_PPT');
180 hr_utility.set_message_token( 'DATE' , to_char(l_dummy_date));
181 hr_utility.raise_error ;
182 end if;
183 close c1 ;
184 --
185 --
186 -- If there is more than one assignment for the given application
187 -- then check that assignment continuity for the application will
188 -- not be broken for the application by ending the current assignment
189 --
190 if ( exists_other_active_asg( p_application_id,
191 p_person_id,
192 p_assignment_id ) ) then
193 --
194 -- Retrieve the effective date
195 open c3 ;
196 fetch c3 into l_effective_date ;
197 close c3 ;
198 --
199 -- Commented out as part of fix for bug 677744.
200 --
201 -- Retrieve the earliest start date - 1 for the current assignment
202 -- open c4 ;
203 -- fetch c4 into l_max_end_date ;
204 -- close c4 ;
205 --
206 -- Check assignment continuity will not be broken by ending the current
207 -- row
208 --
209 -- The call below was commented out as part of fix for bug 677744.
210 -- the line 'p_max_end_date => l_max_end_date,'
211 -- was replaced with 'p_max_end_date => l_effective_date'
212 --
213 per_app_asg_pkg.check_assignment_continuity (
214 p_business_group_id => p_business_group_id,
215 p_assignment_id => p_assignment_id,
216 p_person_id => p_person_id,
217 p_max_end_date => l_effective_date,
218 p_session_date => l_effective_date ) ;
219 --
220 -- End of fix.
221 end if;
222 --
223 end term_apl_checks ;
224 --
225 begin
226 --
227 chk_assignment_current (p_assignment_id);
228 --
229 --
230 --
231 if ( p_per_system_status = 'TERM_APL' ) then
232 --
233 term_apl_checks(p_application_id,
234 p_person_id,
235 p_assignment_id,
236 p_business_group_id ) ;
237 --
238 end if;
239 --
240 end chk_asg_status_change ;
241 --
242 -- PUBLIC FUNCTIONS
243 --
244 procedure get_db_defaults ( p_business_group_id in number,
245 p_grade_structure in out nocopy number,
246 p_people_group_structure in out nocopy number,
247 p_job_structure in out nocopy number,
248 p_position_structure in out nocopy number ) is
249 --
250 cursor c1 is
251 select grade_structure,
252 people_group_structure,
253 job_structure,
254 position_structure
255 from per_business_groups
256 where business_group_id = p_business_group_id ;
257 --
258 begin
259
260 open c1 ;
261 fetch c1 into p_grade_structure,
262 p_people_group_structure,
263 p_job_structure,
264 p_position_structure ;
265 close c1 ;
266
267 end get_db_defaults ;
268 --
269 procedure validate_asg_change ( p_application_id in number,
270 p_person_id in number,
271 p_assignment_id in number,
272 p_status_changed in boolean,
273 p_new_system_status in varchar2,
274 p_new_asg_status_type_id in number,
275 p_recruiter_id in number,
276 p_dt_update_mode in varchar2,
277 p_business_group_id in number) is
278 begin
279 --
280 -- No changes are allowed if there are future assignment changes
281 chk_future_asg_changes ( p_assignment_id ) ;
282 --
283 --
284 -- Perform validation specific to the Assignment Status changing
285 if ( p_status_changed = TRUE ) then
286 chk_asg_status_change ( p_application_id,
287 p_person_id,
288 p_assignment_id,
289 p_new_system_status,
290 p_new_asg_status_type_id,
291 p_dt_update_mode,
292 p_business_group_id) ;
293 end if;
294 --
295 --
296 --
297 end validate_asg_change ;
298 --
299 procedure update_row ( p_rowid in varchar2,
300 p_application_id in number,
301 p_person_id in number,
302 p_assignment_id in number,
303 p_status_changed in boolean,
304 p_new_system_status in varchar2,
305 p_new_asg_status_type_id in number,
306 p_recruiter_id in number,
307 p_dt_update_mode in varchar2,
308 p_effective_date in date,
309 p_effective_start_date in date,
310 p_validation_start_date in date,
311 p_business_group_id in number ) is
312 --
313 l_end_row varchar2(5) := 'FALSE' ; -- Should the assignment row's
314 -- end date be set to the session date
315 l_max_asg_end_date date ;
316
317 -- Variables added for fix 3355901
318 l_assignment_status_id number;
319 l_object_version_number number;
320
321 -- Name
322 -- get_max_asg_date
323 -- Purpose
324 -- Finds the greatest end date for an assignment other than the current
325 -- one
326 function get_max_asg_date ( p_application_id in number,
327 p_person_id in number,
328 p_assignment_id in number,
329 p_effective_date in date ) return date is
330 cursor c1 is
331 select nvl(max(a.effective_end_date),p_effective_date)
332 from per_all_assignments_f a
333 where a.person_id = p_person_id
334 and a.application_id = p_application_id
335 and a.assignment_id <> p_assignment_id
336 and a.assignment_type = 'A'
337 and p_effective_date between a.effective_start_date
338 and a.effective_end_date ;
339 l_return_value date ;
340 begin
341 --
342 open c1 ;
343 fetch c1 into l_return_value;
344 close c1 ;
345 --
346 return ( l_return_value ) ;
347 --
348 end get_max_asg_date ;
349
350 -- Name
351 -- term_apl_sec_statuses
352 -- Purpose
353 -- Called when terminating an assignment.
354 -- Ends any current secondary statuses.
355 -- Deletes any future secondary statuses.
356 -- Arguments
357 -- p_assignment_id
358 -- p_effective_date
359 --
360 procedure term_apl_sec_statuses ( p_assignment_id in number,
361 p_effective_date in date ) is
362 begin
363 --
364 delete from per_secondary_ass_statuses
365 where assignment_id = p_assignment_id
366 and start_date > p_effective_date ;
367 --
368 update per_secondary_ass_statuses
369 set end_date = p_effective_date
370 where assignment_id = p_assignment_id
371 and p_effective_date between start_date
372 and nvl(end_date,p_effective_date) ;
373 end term_apl_sec_statuses ;
374 --
375 -- Name
376 -- delete_pending_letters
377 -- Purpose
378 -- Removes any pending letter requests lines which are not for the
379 -- assignments new status which were automatically generated.
380 -- If there are no letter request lines for a given request then
381 -- remove the request
382 -- Arguments
383 -- p_assignment_id
384 -- p_assignment_status_type_id
385 -- p_business_group_id
386 procedure delete_pending_letters ( p_assignment_id in number,
387 p_assignment_status_type_id in number,
388 p_business_group_id in number) is
389 begin
390 --
391 delete from per_letter_request_lines l
392 where l.assignment_id = p_assignment_id
393 and l.assignment_status_type_id <> p_assignment_status_type_id
394 and exists ( select 1
395 from per_letter_requests r
396 where r.letter_request_id = l.letter_request_id
397 and r.request_status = 'PENDING'
398 and r.auto_or_manual = 'AUTO' ) ;
399 message('DELETED '||to_char(sql%rowcount)||' ROWS FROM REQUEST LINES');
400 --
401 --
402 -- Remove any 'empty' requests in the current business group
403 --
404 delete from per_letter_requests r
405 where r.business_group_id = p_business_group_id
406 and r.request_status = 'PENDING'
407 and r.auto_or_manual = 'AUTO'
408 and not exists ( select 1
409 from per_letter_request_lines l
410 where l.letter_request_id = r.letter_request_id
411 ) ;
412 message('DELETED '||to_char(sql%rowcount)||' ROWS FROM LETTER REQUESTS');
413 --
414 end delete_pending_letters ;
415 --
416 -- Name
417 -- delete_events
418 -- Purpose
419 -- Removes scheduled events,interviews and bookings
420 -- for the given assignment. Called when terminating the
421 -- assignment.
422 -- Arguments
423 -- p_assignment_id
424 -- p_effective_date
425 procedure delete_events ( p_assignment_id in number,
426 p_effective_date in date ) is
427 begin
428 --
429 delete from per_bookings b
430 where b.event_id in ( select e.event_id
431 from per_events e
432 where e.assignment_id = p_assignment_id
433 and e.date_start > p_effective_date
434 ) ;
435 --
436 delete from per_events e
437 where e.assignment_id = p_assignment_id
438 and e.date_start > p_effective_date ;
439 --
440 end delete_events ;
441 --
442 -- Name
443 -- chk_letters
444 -- Purpose
445 -- Peforms letter request processing. Used when the assignments status
446 -- changes
447 -- Arguments
448 --
449 procedure chk_letters ( p_assignment_id in number,
450 p_person_id in number,
451 p_per_system_status in varchar2,
452 p_assignment_status_type_id in number,
453 p_business_group_id in number,
454 p_effective_date in date,
455 p_validation_start_date in date) is
456
457 cursor csr_vacancy_id is
458 Select vacancy_id
459 From per_all_assignments_f
460 Where assignment_id = p_assignment_id
461 And p_effective_date between effective_start_date and effective_end_date;
462
463 l_vacancy_id number;
464 begin
465 --
466 --
467 -- Remove any pending letters
468 --
469 delete_pending_letters ( p_assignment_id,
470 p_assignment_status_type_id,
471 p_business_group_id ) ;
472 --
473 -- Create any new letter requests for new status
474 --
475 open csr_vacancy_id;
476 fetch csr_vacancy_id into l_vacancy_id;
477 if csr_vacancy_id%NOTFOUND then null;
478 end if;
479 close csr_vacancy_id;
480 --
481 per_applicant_pkg.check_for_letter_requests(p_business_group_id,
482 p_per_system_status,
483 p_assignment_status_type_id,
484 p_person_id,
485 p_assignment_id,
486 p_effective_date,
487 p_validation_start_date,
488 l_vacancy_id) ;
489 --
490 end chk_letters ;
491 --
492 begin -- main procedure starts here
493 --
494 --
495 -- Check that the update is still OK
496 --
497 validate_asg_change ( p_application_id,
498 p_person_id,
499 p_assignment_id,
500 p_status_changed,
501 p_new_system_status,
502 p_new_asg_status_type_id,
503 p_recruiter_id,
504 p_dt_update_mode,
505 p_business_group_id);
506 --
507 --
508 -- Update the row
509 -- If the user is updating the status to TERM_APL then
510 -- set the end date on the row but don't change the status.
511 --
512 if ( p_status_changed and p_new_system_status = 'TERM_APL' ) then
513 l_end_row := 'TRUE' ;
514 end if ;
515 --
516 update per_all_assignments_f
517 set assignment_status_type_id = decode( l_end_row,
518 'TRUE',
519 assignment_status_type_id,
520 p_new_asg_status_type_id ),
521 recruiter_id = p_recruiter_id,
522 effective_start_date = p_effective_start_date,
523 effective_end_date = decode(l_end_row,
524 'TRUE',
525 p_effective_date,
526 effective_end_date )
527 where rowid = p_rowid ;
528 --
529 -- Perform 3rd party updates if the status is being changed
530 --
531 if ( p_status_changed = TRUE ) then
532 --
533 -- Fix for bug 3355901 Start
534 IRC_ASG_STATUS_API.create_irc_asg_status
535 ( p_validate => FALSE
536 , p_assignment_id => p_assignment_id
537 , p_assignment_status_type_id => p_new_asg_status_type_id
538 , p_status_change_date => p_effective_date
539 , p_assignment_status_id => l_assignment_status_id
540 , p_object_version_number => l_object_version_number
541 );
542 -- Fix for bug 3355901 End
543 --
544 if ( p_new_system_status = 'TERM_APL' ) then
545 --
546 if ( not exists_other_active_asg( p_application_id,
547 p_person_id,
548 p_assignment_id ) ) then
549 --
550 -- Terminate the Application
551 --
552 message('TERMINATING APPLICATION');
553 l_max_asg_end_date := get_max_asg_date( p_application_id,
554 p_person_id,
555 p_assignment_id,
556 p_effective_date ) ;
557 --
558 per_applications_pkg.maintain_ppt_term ( p_business_group_id,
559 p_person_id,
560 l_max_asg_end_date,
561 hr_general.end_of_time,
562 null,
563 null ) ;
564 --
565 update per_applications
566 set date_end = l_max_asg_end_date
567 where application_id = p_application_id ;
568
569 -- Bug fix for 1222139
570 --
571 -- Now maintain the PTU data...
572 --
573 -- PTU Changes
574 -- hr_per_type_usage_internal.maintain_ptu(
575 -- p_action => 'TERM_APL',
576 -- p_person_id => p_person_id,
577 -- p_actual_termination_date => l_max_asg_end_date);
578 --
579 -- Changed p_System_person_type from EX_EMP to EX_APL
580 -- as part of fix for bug 2330287
581 --
582 hr_per_type_usage_internal.maintain_person_type_usage
583 ( p_effective_date => l_max_asg_end_date+1
584 ,p_person_id => p_person_id
585 ,p_person_type_id =>
586 hr_person_type_usage_info.get_default_person_type_id
587 (p_business_group_id => p_business_group_id
588 ,p_system_person_type => 'EX_APL')
589 ,p_datetrack_update_mode => 'UPDATE');
590
591
592 -- PTU Changes
593 --
594 --
595 end if;
596 --
597 message('REMOVE SECONDARY STATUSES');
598 term_apl_sec_statuses( p_assignment_id,
599 p_effective_date ) ;
600 --
601 message('REMOVE BOOKINGS,INTERVIEWS AND EVENTS');
602 delete_events ( p_assignment_id,
603 p_effective_date ) ;
604 end if;
605 --
606 message('DO LETTERS PROCESSING');
607 chk_letters ( p_assignment_id => p_assignment_id,
608 p_person_id => p_person_id,
609 p_per_system_status => p_new_system_status,
610 p_assignment_status_type_id => p_new_asg_status_type_id,
611 p_business_group_id => p_business_group_id,
612 p_effective_date => p_effective_date,
613 p_validation_start_date => p_validation_start_date ) ;
614 --
615 end if ;
616 end update_row ;
617 --
618 END PER_BULK_APP_ASG_CHANGE_PKG ;
[Home] [Help]