[Home] [Help]
PACKAGE BODY: APPS.IRC_ASG_STATUS_API
Source
1 Package Body IRC_ASG_STATUS_API as
2 /* $Header: iriasapi.pkb 120.13.12020000.2 2013/01/02 11:13:48 kkananth ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'IRC_ASG_STATUS_API.';
7
8
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------< dt_update_irc_asg_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 procedure dt_update_irc_asg_status
15 (
16 p_validate in boolean default false
17 , p_datetrack_mode in varchar2
18 , p_assignment_id in number
19 , p_assignment_status_type_id in number
20 , p_status_change_date in date
21 , p_status_change_reason in varchar2 default hr_api.g_varchar2
22 , p_assignment_status_id out nocopy number
23 , p_object_version_number out nocopy number
24 , p_status_change_comments in varchar2 default hr_api.g_varchar2
25 ) IS
26 --
27 cursor csr_after_date is
28 select assignment_status_id, object_Version_number
29 from irc_assignment_statuses
30 where assignment_id = p_assignment_id
31 and trunc(status_change_date) > trunc(p_status_change_date);
32 --
33 cursor csr_get_status is
34 select assignment_status_type_id
35 from irc_assignment_statuses
36 where assignment_id = p_assignment_id
37 and status_change_date = (select max(status_change_date)
38 from irc_assignment_statuses
39 where assignment_id = p_assignment_id);
40 -- and status_change_date < p_status_change_date);
41 -- Modified for 5838786
42 l_assignment_status_type_id irc_assignment_statuses.assignment_status_type_id%type;
43 l_status_change_comments irc_assignment_statuses.status_change_comments%type;
44 l_status_change_reason irc_assignment_statuses.status_change_reason%type;
45 l_proc varchar2(72) := g_package||'dt_update_irc_asg_status';
46 begin
47 hr_utility.set_location('Entering:'|| l_proc, 10);
48 if p_datetrack_mode = 'UPDATE_OVERRIDE' then
49 for c_rec in csr_after_date loop
50 delete_irc_asg_status
51 (p_assignment_status_id => c_rec.assignment_status_id,
52 p_object_version_number => c_rec.object_version_number);
53 end loop;
54 end if;
55 --+
56 l_assignment_status_type_id := hr_api.g_number;
57 --+
58 open csr_get_status;
59 fetch csr_get_Status into l_assignment_Status_Type_id;
60 close csr_get_status;
61 --+
62 if p_status_change_reason = hr_api.g_varchar2 then
63 l_status_change_reason := null;
64 end if;
65 --+
66 if l_assignment_status_type_id <> p_assignment_status_type_id then
67 if p_datetrack_mode ='CORRECTION'
68 and trunc(sysdate) <> trunc(p_status_change_date) then
69 update_irc_asg_status
70 (p_validate => p_validate
71 ,p_status_change_date => p_status_change_date
72 ,p_status_change_reason => l_status_change_reason
73 ,p_assignment_status_id => p_assignment_status_id
74 ,p_object_version_number => p_object_version_number
75 ,p_status_change_comments => p_status_change_comments);
76 --+
77 else
78 if l_status_change_comments = hr_api.g_varchar2 then
79 l_status_change_comments := null;
80 end if;
81 create_irc_asg_status
82 (p_validate => p_validate
83 ,p_assignment_id => p_assignment_id
84 ,p_assignment_status_type_id => p_assignment_status_type_id
85 ,p_status_change_date => p_status_change_date
86 ,p_status_change_reason => l_status_change_reason
87 ,p_assignment_status_id => p_assignment_status_id
88 ,p_object_version_number => p_object_version_number
89 ,p_status_change_comments => l_status_change_comments);
90 --+
91 end if;
92 end if;
93 -- Handle exception and set the out parameters to null and reraise the exception
94 hr_utility.set_location(' Leaving:'||l_proc, 20);
95 exception
96 when others then
97 p_assignment_status_id := null;
98 p_object_version_number := null;
99 raise;
100 end;
101 --
102 --
103 -- ----------------------------------------------------------------------------
104 -- |---------------------< dt_delete_irc_asg_status >-------------------------|
105 -- ----------------------------------------------------------------------------
106 --
107 procedure dt_delete_irc_asg_status
108 ( p_validate in boolean default false
109 , p_assignment_status_id in number
110 , p_object_version_number in number
111 , p_effective_date in date
112 , p_datetrack_mode in varchar2) IS
113 --
114 l_assignment_id irc_assignment_statuses.assignment_id%type;
115 cursor csr_ass is
116 select assignment_id from irc_assignment_statuses
117 where assignment_status_id = p_assignment_status_id;
118 --
119 cursor csr_after_date is
120 select assignment_status_id, object_Version_number
121 from irc_assignment_statuses
122 where assignment_id = l_assignment_id
123 and (trunc(status_change_date) > trunc(p_effective_date)
124 or p_datetrack_mode <> 'FUTURE_CHANGE')
125 and (trunc(status_change_date) >= trunc(p_effective_date)
126 or p_datetrack_mode <> 'DELETE')
127 and (p_datetrack_mode <> 'DELETE_NEXT_CHANGE'
128 or trunc(status_change_date) = (select trunc(min(status_change_date))
129 from irc_assignment_statuses
130 where assignment_id = l_assignment_id
131 and status_change_date >
132 p_effective_date));
133 --
134 begin
135 open csr_ass;
136 fetch csr_ass into l_assignment_id;
137 close csr_ass;
138 --
139 for c_rec in csr_after_date loop
140 delete_irc_asg_status
141 (p_assignment_status_id => c_rec.assignment_status_id,
142 p_object_version_number => c_rec.object_version_number);
143 end loop;
144 --
145 end;
146 --
147 -- ----------------------------------------------------------------------------
148 -- |---------------------< create_irc_asg_status >---------------------------|
149 -- ---------------------------------------------------------------------------
150 --
151 procedure create_irc_asg_status
152 ( p_validate in boolean default false
153 , p_assignment_id in number
154 , p_assignment_status_type_id in number
155 , p_status_change_date in date
156 , p_status_change_reason in varchar2 default null
157 , p_assignment_status_id out nocopy number
158 , p_object_version_number out nocopy number
159 , p_status_change_comments in varchar2 default null
160 ) is
161 --
162 -- Declare cursors and local variables
163 --
164 l_proc varchar2(72) := g_package||'create_irc_asg_status';
165 l_object_version_number number;
166 l_assignment_status_id irc_assignment_statuses.assignment_id%type;
167 l_status_change_date irc_assignment_statuses.status_change_date%type;
168 l_per_system_status per_assignment_status_types.per_system_status%type;
169 l_status_change_by irc_assignment_statuses.status_change_by%type;
170 l_status_change_reason irc_assignment_statuses.status_change_reason%type;
171
172 --
173 cursor c_status_type is
174 select per_system_status
175 from per_assignment_status_types
176 where assignment_status_type_id = p_assignment_status_type_id;
177 cursor c_max_status_change_date is
178 select max(status_change_date)
179 from irc_assignment_statuses
180 where assignment_id = p_assignment_id;
181 PROCEDURE UPDATE_INTERVIEW(
182 p_assignment_id in NUMBER
183 ,p_assignment_status_type_id in NUMBER
184 ) is
185 iid_rec irc_interview_details%rowtype;
186 l_return_status varchar2(30);
187 L_NOTIFY_PARAMS VARCHAR2(4000);
188 cursor cur_iid is
189 select iid.*
190 from irc_interview_details iid
191 ,per_events pe
192 where iid.event_id = pe.event_id
193 and iid.status not in ('COMPLETED','CANCELLED')
194 and sysdate between iid.start_date and iid.end_date
195 and pe.assignment_id = p_assignment_id;
196 begin
197 for iid_rec in cur_iid
198 loop
199 IRC_INTERVIEW_DETAILS_SWI.UPDATE_IRC_INTERVIEW_DETAILS(
200 P_STATUS => 'CANCELLED'
201 ,P_FEEDBACK => IID_REC.FEEDBACK
202 ,P_NOTES => IID_REC.NOTES
203 ,P_NOTES_TO_CANDIDATE => IID_REC.NOTES_TO_CANDIDATE
204 ,P_CATEGORY => IID_REC.CATEGORY
205 ,P_RESULT => IID_REC.RESULT
206 ,P_IID_INFORMATION_CATEGORY => IID_REC.IID_INFORMATION_CATEGORY
207 ,P_IID_INFORMATION1 => IID_REC.IID_INFORMATION1
208 ,P_IID_INFORMATION2 => IID_REC.IID_INFORMATION2
209 ,P_IID_INFORMATION3 => IID_REC.IID_INFORMATION3
210 ,P_IID_INFORMATION4 => IID_REC.IID_INFORMATION4
211 ,P_IID_INFORMATION5 => IID_REC.IID_INFORMATION5
212 ,P_IID_INFORMATION6 => IID_REC.IID_INFORMATION6
213 ,P_IID_INFORMATION7 => IID_REC.IID_INFORMATION7
214 ,P_IID_INFORMATION8 => IID_REC.IID_INFORMATION8
215 ,P_IID_INFORMATION9 => IID_REC.IID_INFORMATION9
216 ,P_IID_INFORMATION10 => IID_REC.IID_INFORMATION10
217 ,P_IID_INFORMATION11 => IID_REC.IID_INFORMATION11
218 ,P_IID_INFORMATION12 => IID_REC.IID_INFORMATION12
219 ,P_IID_INFORMATION13 => IID_REC.IID_INFORMATION13
220 ,P_IID_INFORMATION14 => IID_REC.IID_INFORMATION14
221 ,P_IID_INFORMATION15 => IID_REC.IID_INFORMATION15
222 ,P_IID_INFORMATION16 => IID_REC.IID_INFORMATION16
223 ,P_IID_INFORMATION17 => IID_REC.IID_INFORMATION17
224 ,P_IID_INFORMATION18 => IID_REC.IID_INFORMATION18
225 ,P_IID_INFORMATION19 => IID_REC.IID_INFORMATION19
226 ,P_IID_INFORMATION20 => IID_REC.IID_INFORMATION20
227 ,P_EVENT_ID => IID_REC.EVENT_ID
228 ,P_INTERVIEW_DETAILS_ID => IID_REC.INTERVIEW_DETAILS_ID
229 ,P_START_DATE => IID_REC.START_DATE
230 ,P_END_DATE => IID_REC.END_DATE
231 ,P_OBJECT_VERSION_NUMBER => IID_REC.OBJECT_VERSION_NUMBER
232 ,P_RETURN_STATUS => l_return_status
233 );
234 L_NOTIFY_PARAMS := 'IRC_INTVW_ID:'||IID_REC.INTERVIEW_DETAILS_ID||';IRC_INTVW_NEW_STATUS:'||'CANCELLED;IRC_INTVW_NEW_STATUS:'||IID_REC.STATUS;
235 IRC_NOTIFICATION_HELPER_PKG.raiseNotifyEvent(
236 p_eventName => 'INTW'
237 ,p_assignmentId => p_assignment_id
238 ,p_personId => NULL
239 ,params => L_NOTIFY_PARAMS
240 );
241 end loop;
242 exception
243 when others then
244 null;
245 end UPDATE_INTERVIEW;
246 --
247 begin
248 hr_utility.set_location('Entering:'|| l_proc, 10);
249 --
250 -- Issue a savepoint
251 --
252 savepoint create_irc_asg_status;
253 --
254 --
255 open c_max_status_change_date;
256 fetch c_max_status_change_date into l_status_change_date;
257 close c_max_status_change_date;
258
259 --
260 -- Do NOT Truncate the time portion from status_change_date
261 --
262 -- if the date is the same as the system date, the sysdate
263 -- including the time element is captured.
264
265 if trunc(p_status_change_date) = trunc(sysdate) then
266 l_status_change_date := sysdate;
267 elsif( p_status_change_date = trunc(l_status_change_date)) then
268 l_status_change_date := l_status_change_date + (1/1440);
269 else
270 l_status_change_date := p_status_change_date;
271 end if;
272 --
273 hr_utility.set_location('opening cursor c_status_type:'|| l_proc, 11);
274 --
275 open c_status_type;
276 fetch c_status_type into l_per_system_status;
277 close c_status_type;
278 --
279 hr_utility.set_location('value of the status_type: '||l_per_system_status||', '|| l_proc, 12);
280 --
281 if (l_per_system_status='TERM_APL') then
282 --
283 l_status_change_by := get_status_change_by(l_status_change_date,p_assignment_id);
284 --
285 end if;
286
287 --+
288 if p_status_change_reason = hr_api.g_varchar2 then
289 l_status_change_reason := null;
290 end if;
291 --+
292 --
293 -- Call Before Process User Hook
294 --
295 begin
296 irc_asg_status_bk1.create_irc_asg_status_b
297 (
298 p_assignment_id => p_assignment_id
299 , p_assignment_status_type_id => p_assignment_status_type_id
300 , p_status_change_reason => l_status_change_reason
301 , p_status_change_date => l_status_change_date
302 , p_status_change_comments => p_status_change_comments
303 , p_status_change_by => l_status_change_by
304 );
305 exception
306 when hr_api.cannot_find_prog_unit then
307 hr_api.cannot_find_prog_unit_error
308 (p_module_name => 'create_irc_asg_status'
309 ,p_hook_type => 'BP'
310 );
311 end;
312 --
313 begin
314 hr_utility.set_location('Entering block to call copy_candidate_details:'|| l_proc, 100);
315 if l_per_system_status='ACCEPTED' then
316 hr_utility.set_location('calling irc_utilities_pkg.copy_candidate_details :'|| l_proc, 110);
317 irc_utilities_pkg.copy_candidate_details(p_assignment_id);
318 hr_utility.set_location('After executing irc_utilities_pkg.copy_candidate_details :'|| l_proc, 120);
319 end if;
320 hr_utility.set_location('Leaving block to call copy_candidate_details:'|| l_proc, 130);
321 exception
322 when others then
323 hr_utility.set_location(' Exception occured: ' || l_proc, 140);
324 raise;
325 end;
326 --
327 -- Process Logic
328 --
329 irc_ias_ins.ins
330 (
331 p_assignment_id => p_assignment_id
332 , p_assignment_status_type_id => p_assignment_status_type_id
333 , p_status_change_reason => l_status_change_reason
334 , p_assignment_status_id => l_assignment_status_id
335 , p_object_version_number => l_object_version_number
336 , p_status_change_date => l_status_change_date
337 , p_status_change_comments => p_status_change_comments
338 , p_status_change_by => l_status_change_by
339 );
340 if( l_per_system_status='ACTIVE_ASSIGN'
341 or l_per_system_status='OFFER'
342 or l_per_system_status='ACCEPTED'
343 or l_per_system_status='TERM_APL') then
344 UPDATE_INTERVIEW(p_assignment_id => p_assignment_id
345 ,p_assignment_status_type_id => p_assignment_status_type_id
346 );
347 end if;
348 --
349 -- Call After Process User Hook
350 --
351 begin
352 irc_asg_status_bk1.create_irc_asg_status_a
353 (
354 p_assignment_id => p_assignment_id
355 , p_assignment_status_type_id => p_assignment_status_type_id
356 , p_status_change_reason => l_status_change_reason
357 , p_assignment_status_id => l_assignment_status_id
358 , p_object_version_number => l_object_version_number
359 , p_status_change_date => l_status_change_date
360 , p_status_change_comments => p_status_change_comments
361 , p_status_change_by => l_status_change_by
362 );
363 exception
364 when hr_api.cannot_find_prog_unit then
365 hr_api.cannot_find_prog_unit_error
366 (p_module_name => 'create_irc_asg_status'
367 ,p_hook_type => 'AP'
368 );
369 end;
370 --
371 -- When in validation only mode raise the Validate_Enabled exception
372 --
373 if p_validate then
374 raise hr_api.validate_enabled;
375 end if;
376 --
377 -- Set all output arguments
378 --
379 p_assignment_status_id := l_assignment_status_id;
380 p_object_version_number := l_object_version_number;
381 --
382 hr_utility.set_location(' Leaving:'||l_proc, 70);
383 exception
384 when hr_api.validate_enabled then
385 --
386 -- As the Validate_Enabled exception has been raised
387 -- we must rollback to the savepoint
388 --
389 rollback to create_irc_asg_status;
390 --
391 -- Reset IN OUT parameters and set OUT paramters
392 p_assignment_status_id := null;
393 p_object_version_number := null;
394 --
395 -- Only set output warning arguments
396 -- (Any key or derived arguments must be set to null
397 -- when validation only mode is being used.)
398 --
399 p_assignment_status_id := null;
400 p_object_version_number := null;
401 hr_utility.set_location(' Leaving:'||l_proc, 80);
402 when others then
403 --
404 -- A validation or unexpected error has occured
405 --
406 rollback to create_irc_asg_status;
407 -- Reset IN OUT parameters and set OUT paramters
408 --
409 p_assignment_status_id := null;
410 p_object_version_number := null;
411 --
412 hr_utility.set_location(' Leaving:'||l_proc, 90);
413 raise;
414 end create_irc_asg_status;
415 --
416 -- ----------------------------------------------------------------------------
417 -- |-------------------< update_irc_asg_status >------------------------------|
418 -- ----------------------------------------------------------------------------
419 --
420 procedure update_irc_asg_status
421 (
422 p_validate in boolean default false
423 , p_status_change_reason in varchar2 default hr_api.g_varchar2
424 , p_status_change_date in date
425 , p_assignment_status_id in number
426 , p_object_version_number in out nocopy number
427 , p_status_change_comments in varchar2 default hr_api.g_varchar2
428 ) is
429 --
430 -- Declare cursors and local variables
431 --
432 l_proc varchar2(72) := g_package||'update_irc_asg_status';
433 l_object_version_number number := p_object_version_number;
434 l_status_change_date irc_assignment_statuses.status_change_date%type;
435 l_status_change_reason irc_assignment_statuses.status_change_reason%type;
436 --
437 begin
438 hr_utility.set_location('Entering:'|| l_proc, 10);
439 --
440 -- Issue a savepoint
441 --
442 savepoint update_irc_asg_status;
443 --
444 -- Do NOT Truncate the time portion from status_change_date
445 --
446 -- if the date is the same as the system date, the sysdate
447 -- including the time element is captured.
448
449 if trunc(p_status_change_date) = trunc(sysdate) then
450 l_status_change_date := sysdate;
451 else
452 l_status_change_date := p_status_change_date;
453 end if;
454 --+
455 if p_status_change_reason = hr_api.g_varchar2 then
456 l_status_change_reason := null;
457 end if;
458 --+
459 --
460 -- Call Before Process User Hook
461 --
462 begin
463 irc_asg_status_bk2.update_irc_asg_status_b
464 (
465 p_status_change_reason => l_status_change_reason
466 , p_status_change_date => l_status_change_date
467 , p_assignment_status_id => p_assignment_status_id
468 , p_object_version_number => l_object_version_number
469 , p_status_change_comments => p_status_change_comments
470 );
471 exception
472 when hr_api.cannot_find_prog_unit then
473 hr_api.cannot_find_prog_unit_error
474 (p_module_name => 'update_irc_asg_status'
475 ,p_hook_type => 'BP'
476 );
477 end;
478 --
479 -- Process Logic
480 --
481 irc_ias_upd.upd
482 (
483 p_assignment_status_id => p_assignment_status_id
484 ,p_object_version_number => l_object_version_number
485 ,p_status_change_reason => l_status_change_reason
486 ,p_status_change_date => l_status_change_date
487 ,p_status_change_comments => p_status_change_comments
488 );
489 --
490 -- Call After Process User Hook
491 --
492 begin
493 irc_asg_status_bk2.update_irc_asg_status_a
494 (
495 p_status_change_reason => l_status_change_reason
496 , p_status_change_date => l_status_change_date
497 , p_assignment_status_id => p_assignment_status_id
498 , p_object_version_number => l_object_version_number
499 , p_status_change_comments => p_status_change_comments
500 );
501 exception
502 when hr_api.cannot_find_prog_unit then
503 hr_api.cannot_find_prog_unit_error
504 (p_module_name => 'update_irc_asg_status'
505 ,p_hook_type => 'AP'
506 );
507 end;
508 --
509 -- When in validation only mode raise the Validate_Enabled exception
510 --
511 if p_validate then
512 raise hr_api.validate_enabled;
513 end if;
514 --
515 -- Set all output arguments
516 --
517 p_object_version_number := l_object_version_number;
518 --
519 hr_utility.set_location(' Leaving:'||l_proc, 70);
520 --
521 exception
522 when hr_api.validate_enabled then
523 --
524 -- As the Validate_Enabled exception has been raised
525 -- we must rollback to the savepoint
526 --
527 rollback to update_irc_asg_status;
528 --
529 -- Reset IN OUT parameters and set OUT paramters
530 p_object_version_number := l_object_version_number;
531 -- Only set output warning arguments
532 -- (Any key or derived arguments must be set to null
533 -- when validation only mode is being used.)
534 --
535 hr_utility.set_location(' Leaving:'||l_proc, 80);
536 when others then
537 --
538 -- A validation or unexpected error has occured
539 --
540 rollback to update_irc_asg_status;
541 -- Reset IN OUT parameters and set OUT paramters
542 p_object_version_number := l_object_version_number;
543 --
544 hr_utility.set_location(' Leaving:'||l_proc, 90);
545 raise;
546 end update_irc_asg_status;
547 --
548 -- ----------------------------------------------------------------------------
549 -- |-------------------< delete_irc_asg_status >------------------------------|
550 -- ----------------------------------------------------------------------------
551 procedure delete_irc_asg_status
552 (
553 p_validate in boolean default false
554 , p_assignment_status_id in number
555 , p_object_version_number in number
556 ) is
557 --
558 -- Declare cursors and local variables
559 --
560 l_proc varchar2(72) := g_package||'delete_irc_asg_status';
561 --
562 begin
563 hr_utility.set_location('Entering:'|| l_proc, 10);
564 --
565 -- Issue a savepoint
566 --
567 savepoint delete_irc_asg_status;
568 --
569 -- Call Before Process User Hook
570 --
571 begin
572 irc_asg_status_bk3.delete_irc_asg_status_b
573 (
574 p_assignment_status_id => p_assignment_status_id
575 , p_object_version_number => p_object_version_number
576 );
577 exception
578 when hr_api.cannot_find_prog_unit then
579 hr_api.cannot_find_prog_unit_error
580 (p_module_name => 'delete_irc_asg_status'
581 ,p_hook_type => 'BP'
582 );
583 end;
584 --
585 -- Process Logic
586 --
587 irc_ias_del.del
588 (p_assignment_status_id => p_assignment_status_id
589 ,p_object_version_number => p_object_version_number
590 );
591 --
592 -- Call After Process User Hook
593 --
594 begin
595 irc_asg_status_bk3.delete_irc_asg_status_a
596 (
597 p_assignment_status_id => p_assignment_status_id
598 , p_object_version_number => p_object_version_number
599 );
600 exception
601 when hr_api.cannot_find_prog_unit then
602 hr_api.cannot_find_prog_unit_error
603 (p_module_name => 'delete_irc_asg_status'
604 ,p_hook_type => 'AP'
605 );
606 end;
607 --
608 -- When in validation only mode raise the Validate_Enabled exception
609 --
610 if p_validate then
611 raise hr_api.validate_enabled;
612 end if;
613 --
614 -- Set all output arguments
615 --
616 hr_utility.set_location(' Leaving:'||l_proc, 70);
617 exception
618 when hr_api.validate_enabled then
619 --
620 -- As the Validate_Enabled exception has been raised
621 -- we must rollback to the savepoint
622 --
623 rollback to delete_irc_asg_status;
624 --
625 -- Only set output warning arguments
626 -- (Any key or derived arguments must be set to null
627 -- when validation only mode is being used.)
628 --
629 hr_utility.set_location(' Leaving:'||l_proc, 80);
630 when others then
631 --
632 -- A validation or unexpected error has occured
633 --
634 rollback to delete_irc_asg_status;
635 hr_utility.set_location(' Leaving:'||l_proc, 90);
636 raise;
637 end delete_irc_asg_status;
638 --
639 --
640 --
641 -- ----------------------------------------------------------------------------
642 -- |-----------------------------< get_status_change_by >--------------------|
643 -- ----------------------------------------------------------------------------
644 --
645 --
646 function get_status_change_by
647 ( P_EFFECTIVE_DATE IN date
648 ,P_ASSIGNMENT_ID IN number
649 ) RETURN VARCHAR2 Is
650 l_proc varchar2(72) := g_package||'get_status_change_by';
651 l_manager_terminates varchar2(1);
652 l_status_change_by varchar2(240);
653 l_user_id varchar2(250);
654 --
655 CURSOR csr_applicant_userid
656 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
657 ,p_effective_date IN DATE
658 )
659 IS
660 select user_id
661 from per_all_assignments_f paf, fnd_user usr, per_all_people_f ppf,
662 per_all_people_f linkppf
663 where p_effective_date between paf.effective_start_date and
664 paf.effective_end_date
665 and p_effective_date between usr.start_date and
666 nvl(usr.end_date,p_effective_date)
667 and p_effective_date between ppf.effective_start_date and
668 ppf.effective_end_date
669 and p_effective_date between linkppf.effective_start_date and
670 linkppf.effective_end_date
671 and usr.employee_id=linkppf.person_id
672 and ppf.party_id = linkppf.party_id
673 and ppf.person_id = paf.person_id
674 and paf.assignment_id= p_assignment_id
675 and usr.user_id = fnd_global.user_id;
676 --
677 begin
678 --
679 hr_utility.set_location(' Entering: '|| l_proc, 10);
680 --
681 OPEN csr_applicant_userid
682 (p_assignment_id => p_assignment_id
683 ,p_effective_date => trunc(p_effective_date)
684 );
685 FETCH csr_applicant_userid INTO l_user_id;
686 IF csr_applicant_userid%NOTFOUND
687 THEN
688 l_manager_terminates:='Y';
689 END IF;
690 CLOSE csr_applicant_userid;
691 --
692 hr_utility.set_location('l_user_id: '||l_user_id,20);
693 hr_utility.set_location('g_user_id: '||fnd_global.user_id,30);
694 --
695 if l_user_id=fnd_global.user_id then
696 l_manager_terminates:='N';
697 else
698 l_manager_terminates:='Y';
699 end if;
700 --
701 if fnd_profile.value('IRC_AGENCY_NAME') is not null then
702 --
703 l_status_change_by := 'AGENCY';
704 --
705 elsif l_manager_terminates = 'Y' then
706 l_status_change_by := 'MANAGER';
707 else
708 l_status_change_by := 'CANDIDATE';
709 end if;
710 --
711 hr_utility.set_location(' l_status_change_by: '||l_status_change_by,40);
712 hr_utility.set_location(' Leaving: '|| l_proc, 50);
713 --
714 RETURN l_status_change_by;
715 end get_status_change_by;
716 --
717 end IRC_ASG_STATUS_API;