[Home] [Help]
PACKAGE BODY: APPS.IRC_ASG_STATUS_API
Source
1 Package Body IRC_ASG_STATUS_API as
2 /* $Header: iriasapi.pkb 120.3 2008/01/17 15:23:34 uuddavol noship $ */
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 ) IS
25
26 cursor csr_after_date is
27 select assignment_status_id, object_Version_number
28 from irc_assignment_statuses
29 where assignment_id = p_assignment_id
30 and trunc(status_change_date) > trunc(p_status_change_date);
31 cursor csr_get_status is
32 select assignment_status_type_id
33 from irc_assignment_statuses
34 where assignment_id = p_assignment_id
35 and status_change_date = (select max(status_change_date)
36 from irc_assignment_statuses
37 where assignment_id = p_assignment_id);
38 -- and status_change_date < p_status_change_date);
39 -- Modified for 5838786
40 l_assignment_status_type_id irc_assignment_statuses.assignment_status_type_id%type;
41 begin
42 if p_datetrack_mode = 'UPDATE_OVERRIDE' then
43 for c_rec in csr_after_date loop
44 delete_irc_asg_status
45 (p_assignment_status_id => c_rec.assignment_status_id,
46 p_object_version_number => c_rec.object_version_number);
47 end loop;
48 end if;
49
50 l_assignment_status_type_id := hr_api.g_number;
51
52 open csr_get_status;
53 fetch csr_get_Status into l_assignment_Status_Type_id;
54 close csr_get_status;
55
56 if l_assignment_status_type_id <> p_assignment_status_type_id then
57 if p_datetrack_mode ='CORRECTION'
58 and trunc(sysdate) <> trunc(p_status_change_date) then
59 update_irc_asg_status
60 ( p_validate => p_validate
61 , p_status_change_date => p_status_change_date
62 , p_status_change_reason => p_status_change_reason
63 , p_assignment_status_id => p_assignment_status_id
64 , p_object_version_number => p_object_version_number);
65
66 else
67 create_irc_asg_status
68 ( p_validate => p_validate
69 , p_assignment_id => p_assignment_id
70 , p_assignment_status_type_id => p_assignment_status_type_id
71 , p_status_change_date => p_status_change_date
72 , p_status_change_reason => p_status_change_reason
73 , p_assignment_status_id => p_assignment_status_id
74 , p_object_version_number => p_object_version_number
75 );
76
77
78 end if;
79 end if;
80 -- Handle exception and set the out parameters to null and reraise the exception
81 exception
82 when others then
83 p_assignment_status_id := null;
84 p_object_version_number := null;
85 raise;
86 end;
87
88 --
89 -- ----------------------------------------------------------------------------
90 -- |---------------------< dt_delete_irc_asg_status >-------------------------|
91 -- ----------------------------------------------------------------------------
92 --
93 procedure dt_delete_irc_asg_status
94 ( p_validate in boolean default false
95 , p_assignment_status_id in number
96 , p_object_version_number in number
97 , p_effective_date in date
98 , p_datetrack_mode in varchar2) IS
99
100 l_assignment_id irc_assignment_statuses.assignment_id%type;
101 cursor csr_ass is
102 select assignment_id from irc_assignment_statuses
103 where assignment_status_id = p_assignment_status_id;
104
105 cursor csr_after_date is
106 select assignment_status_id, object_Version_number
107 from irc_assignment_statuses
108 where assignment_id = l_assignment_id
109 and (trunc(status_change_date) > trunc(p_effective_date)
110 or p_datetrack_mode <> 'FUTURE_CHANGE')
111 and (trunc(status_change_date) >= trunc(p_effective_date)
112 or p_datetrack_mode <> 'DELETE')
113 and (p_datetrack_mode <> 'DELETE_NEXT_CHANGE'
114 or trunc(status_change_date) = (select trunc(min(status_change_date))
115 from irc_assignment_statuses
116 where assignment_id = l_assignment_id
117 and status_change_date >
118 p_effective_date));
119
120 begin
121 open csr_ass;
122 fetch csr_ass into l_assignment_id;
123 close csr_ass;
124
125 for c_rec in csr_after_date loop
126 delete_irc_asg_status
127 (p_assignment_status_id => c_rec.assignment_status_id,
128 p_object_version_number => c_rec.object_version_number);
129 end loop;
130
131
132 end;
133 --
134 -- ----------------------------------------------------------------------------
135 -- |---------------------< create_irc_asg_status >---------------------------|
136 -- ---------------------------------------------------------------------------
137 --
138 procedure create_irc_asg_status
139 ( p_validate in boolean default false
140 , p_assignment_id in number
141 , p_assignment_status_type_id in number
142 , p_status_change_date in date
143 , p_status_change_reason in varchar2 default null
144 , p_assignment_status_id out nocopy number
145 , p_object_version_number out nocopy number
146 ) is
147 --
148 -- Declare cursors and local variables
149 --
150 l_proc varchar2(72) := g_package||'create_irc_asg_status';
151 l_object_version_number number;
152 l_assignment_status_id irc_assignment_statuses.assignment_id%type;
153 l_status_change_date irc_assignment_statuses.status_change_date%type;
154 PROCEDURE UPDATE_INTERVIEW(
155 p_assignment_id in NUMBER
156 ,p_assignment_status_type_id in NUMBER
157 ) is
158 iid_rec irc_interview_details%rowtype;
159 l_return_status varchar2(30);
160 L_NOTIFY_PARAMS VARCHAR2(4000);
161 cursor cur_iid is
162 select iid.*
163 from irc_interview_details iid
164 ,per_events pe
165 where iid.event_id = pe.event_id
166 and iid.status not in ('COMPLETED','CANCELLED')
167 and sysdate between iid.start_date and iid.end_date
168 and pe.assignment_id = p_assignment_id;
169 begin
170 for iid_rec in cur_iid
171 loop
172 IRC_INTERVIEW_DETAILS_SWI.UPDATE_IRC_INTERVIEW_DETAILS(
173 P_STATUS => 'CANCELLED'
174 ,P_FEEDBACK => IID_REC.FEEDBACK
175 ,P_NOTES => IID_REC.NOTES
176 ,P_NOTES_TO_CANDIDATE => IID_REC.NOTES_TO_CANDIDATE
177 ,P_CATEGORY => IID_REC.CATEGORY
178 ,P_RESULT => IID_REC.RESULT
179 ,P_IID_INFORMATION_CATEGORY => IID_REC.IID_INFORMATION_CATEGORY
180 ,P_IID_INFORMATION1 => IID_REC.IID_INFORMATION1
181 ,P_IID_INFORMATION2 => IID_REC.IID_INFORMATION2
182 ,P_IID_INFORMATION3 => IID_REC.IID_INFORMATION3
183 ,P_IID_INFORMATION4 => IID_REC.IID_INFORMATION4
184 ,P_IID_INFORMATION5 => IID_REC.IID_INFORMATION5
185 ,P_IID_INFORMATION6 => IID_REC.IID_INFORMATION6
186 ,P_IID_INFORMATION7 => IID_REC.IID_INFORMATION7
187 ,P_IID_INFORMATION8 => IID_REC.IID_INFORMATION8
188 ,P_IID_INFORMATION9 => IID_REC.IID_INFORMATION9
189 ,P_IID_INFORMATION10 => IID_REC.IID_INFORMATION10
190 ,P_IID_INFORMATION11 => IID_REC.IID_INFORMATION11
191 ,P_IID_INFORMATION12 => IID_REC.IID_INFORMATION12
192 ,P_IID_INFORMATION13 => IID_REC.IID_INFORMATION13
193 ,P_IID_INFORMATION14 => IID_REC.IID_INFORMATION14
194 ,P_IID_INFORMATION15 => IID_REC.IID_INFORMATION15
195 ,P_IID_INFORMATION16 => IID_REC.IID_INFORMATION16
196 ,P_IID_INFORMATION17 => IID_REC.IID_INFORMATION17
197 ,P_IID_INFORMATION18 => IID_REC.IID_INFORMATION18
198 ,P_IID_INFORMATION19 => IID_REC.IID_INFORMATION19
199 ,P_IID_INFORMATION20 => IID_REC.IID_INFORMATION20
200 ,P_EVENT_ID => IID_REC.EVENT_ID
201 ,P_INTERVIEW_DETAILS_ID => IID_REC.INTERVIEW_DETAILS_ID
202 ,P_START_DATE => IID_REC.START_DATE
203 ,P_END_DATE => IID_REC.END_DATE
204 ,P_OBJECT_VERSION_NUMBER => IID_REC.OBJECT_VERSION_NUMBER
205 ,P_RETURN_STATUS => l_return_status
206 );
207 L_NOTIFY_PARAMS := 'IRC_INTVW_ID:'||IID_REC.INTERVIEW_DETAILS_ID||';IRC_INTVW_NEW_STATUS:'||'CANCELLED;IRC_INTVW_NEW_STATUS:'||IID_REC.STATUS;
208 IRC_NOTIFICATION_HELPER_PKG.raiseNotifyEvent(
209 p_eventName => 'INTW'
210 ,p_assignmentId => p_assignment_id
211 ,p_personId => NULL
212 ,params => L_NOTIFY_PARAMS
213 );
214 end loop;
215 exception
216 when others then
217 null;
218 end UPDATE_INTERVIEW;
219 --
220 begin
221 hr_utility.set_location('Entering:'|| l_proc, 10);
222 --
223 -- Issue a savepoint
224 --
225 savepoint create_irc_asg_status;
226 --
227 -- Do NOT Truncate the time portion from status_change_date
228 --
229 -- if the date is the same as the system date, the sysdate
230 -- including the time element is captured.
231
232 if trunc(p_status_change_date) = trunc(sysdate) then
233 l_status_change_date := sysdate;
234 else
235 l_status_change_date := p_status_change_date;
236 end if;
237
238 --
239 -- Call Before Process User Hook
240 --
241 begin
242 irc_asg_status_bk1.create_irc_asg_status_b
243 (
244 p_assignment_id =>p_assignment_id
245 , p_assignment_status_type_id =>p_assignment_status_type_id
246 , p_status_change_reason =>p_status_change_reason
247 , p_status_change_date =>l_status_change_date
248 );
249 exception
250 when hr_api.cannot_find_prog_unit then
251 hr_api.cannot_find_prog_unit_error
252 (p_module_name => 'create_irc_asg_status'
253 ,p_hook_type => 'BP'
254 );
255 end;
256 --
257 -- Process Logic
258 --
259 irc_ias_ins.ins
260 (
261 p_assignment_id => p_assignment_id
262 , p_assignment_status_type_id => p_assignment_status_type_id
263 , p_status_change_reason => p_status_change_reason
264 , p_assignment_status_id => l_assignment_status_id
265 , p_object_version_number => l_object_version_number
266 , p_status_change_date => l_status_change_date
267 );
268 if(p_assignment_status_type_id = 1
269 or p_assignment_status_type_id = 5
270 or p_assignment_status_type_id = 6
271 or p_assignment_status_type_id = 7) then
272 UPDATE_INTERVIEW(p_assignment_id => p_assignment_id
273 ,p_assignment_status_type_id => p_assignment_status_type_id
274 );
275 end if;
276 --
277 -- Call After Process User Hook
278 --
279 begin
280 irc_asg_status_bk1.create_irc_asg_status_a
281 (
282 p_assignment_id =>p_assignment_id
283 , p_assignment_status_type_id =>p_assignment_status_type_id
284 , p_status_change_reason =>p_status_change_reason
285 , p_assignment_status_id =>l_assignment_status_id
286 , p_object_version_number =>l_object_version_number
287 , p_status_change_date =>l_status_change_date
288 );
289 exception
290 when hr_api.cannot_find_prog_unit then
291 hr_api.cannot_find_prog_unit_error
292 (p_module_name => 'create_irc_asg_status'
293 ,p_hook_type => 'AP'
294 );
295 end;
296 --
297 -- When in validation only mode raise the Validate_Enabled exception
298 --
299 if p_validate then
300 raise hr_api.validate_enabled;
301 end if;
302 --
303 -- Set all output arguments
304 --
305 p_assignment_status_id := l_assignment_status_id;
306 p_object_version_number := l_object_version_number;
307 --
308 hr_utility.set_location(' Leaving:'||l_proc, 70);
309 exception
310 when hr_api.validate_enabled then
311 --
312 -- As the Validate_Enabled exception has been raised
313 -- we must rollback to the savepoint
314 --
315 rollback to create_irc_asg_status;
316 --
317 -- Reset IN OUT parameters and set OUT paramters
318 p_assignment_status_id := null;
319 p_object_version_number := null;
320 --
321 -- Only set output warning arguments
322 -- (Any key or derived arguments must be set to null
323 -- when validation only mode is being used.)
324 --
325 p_assignment_status_id := null;
326 p_object_version_number := null;
327 hr_utility.set_location(' Leaving:'||l_proc, 80);
328 when others then
329 --
330 -- A validation or unexpected error has occured
331 --
332 rollback to create_irc_asg_status;
333 -- Reset IN OUT parameters and set OUT paramters
334 --
335 p_assignment_status_id := null;
336 p_object_version_number := null;
337 --
338 hr_utility.set_location(' Leaving:'||l_proc, 90);
339 raise;
340 end create_irc_asg_status;
341 --
342 -- ----------------------------------------------------------------------------
343 -- |-------------------< update_irc_asg_status >------------------------------|
344 -- ----------------------------------------------------------------------------
345 --
346 procedure update_irc_asg_status
347 (
348 p_validate in boolean default false
349 , p_status_change_reason in varchar2 default hr_api.g_varchar2
350 , p_status_change_date in date
351 , p_assignment_status_id in number
352 , p_object_version_number in out nocopy number
353 ) is
354 --
355 -- Declare cursors and local variables
356 --
357 l_proc varchar2(72) := g_package||'update_irc_asg_status';
358 l_object_version_number number := p_object_version_number;
359 l_status_change_date irc_assignment_statuses.status_change_date%type;
360 --
361 begin
362 hr_utility.set_location('Entering:'|| l_proc, 10);
363 --
364 -- Issue a savepoint
365 --
366 savepoint update_irc_asg_status;
367 --
368 -- Do NOT Truncate the time portion from status_change_date
369 --
370 -- if the date is the same as the system date, the sysdate
371 -- including the time element is captured.
372
373 if trunc(p_status_change_date) = trunc(sysdate) then
374 l_status_change_date := sysdate;
375 else
376 l_status_change_date := p_status_change_date;
377 end if;
378 --
379 -- Call Before Process User Hook
380 --
384 p_status_change_reason => p_status_change_reason
381 begin
382 irc_asg_status_bk2.update_irc_asg_status_b
383 (
385 , p_status_change_date => l_status_change_date
386 , p_assignment_status_id => p_assignment_status_id
387 , p_object_version_number => l_object_version_number
388 );
389 exception
390 when hr_api.cannot_find_prog_unit then
391 hr_api.cannot_find_prog_unit_error
392 (p_module_name => 'update_irc_asg_status'
393 ,p_hook_type => 'BP'
394 );
395 end;
396 --
397 -- Process Logic
398 --
399 irc_ias_upd.upd
400 (
401 p_assignment_status_id => p_assignment_status_id
402 ,p_object_version_number => l_object_version_number
403 ,p_status_change_reason => p_status_change_reason
404 ,p_status_change_date => l_status_change_date
405 );
406 --
407 -- Call After Process User Hook
408 --
409 begin
410 irc_asg_status_bk2.update_irc_asg_status_a
411 (
412 p_status_change_reason => p_status_change_reason
413 , p_status_change_date => l_status_change_date
414 , p_assignment_status_id => p_assignment_status_id
415 , p_object_version_number => l_object_version_number
416 );
417 exception
418 when hr_api.cannot_find_prog_unit then
419 hr_api.cannot_find_prog_unit_error
420 (p_module_name => 'update_irc_asg_status'
421 ,p_hook_type => 'AP'
422 );
423 end;
424 --
425 -- When in validation only mode raise the Validate_Enabled exception
426 --
427 if p_validate then
428 raise hr_api.validate_enabled;
429 end if;
430 --
431 -- Set all output arguments
432 --
433 p_object_version_number := l_object_version_number;
434 --
435 hr_utility.set_location(' Leaving:'||l_proc, 70);
436 --
437 exception
438 when hr_api.validate_enabled then
439 --
440 -- As the Validate_Enabled exception has been raised
441 -- we must rollback to the savepoint
442 --
443 rollback to update_irc_asg_status;
444 --
445 -- Reset IN OUT parameters and set OUT paramters
446 p_object_version_number := l_object_version_number;
447 -- Only set output warning arguments
448 -- (Any key or derived arguments must be set to null
449 -- when validation only mode is being used.)
450 --
451 hr_utility.set_location(' Leaving:'||l_proc, 80);
452 when others then
453 --
454 -- A validation or unexpected error has occured
455 --
456 rollback to update_irc_asg_status;
457 -- Reset IN OUT parameters and set OUT paramters
458 p_object_version_number := l_object_version_number;
459 --
460 hr_utility.set_location(' Leaving:'||l_proc, 90);
461 raise;
462 end update_irc_asg_status;
463 --
464 -- ----------------------------------------------------------------------------
465 -- |-------------------< delete_irc_asg_status >------------------------------|
466 -- ----------------------------------------------------------------------------
467 procedure delete_irc_asg_status
468 (
469 p_validate in boolean default false
470 , p_assignment_status_id in number
471 , p_object_version_number in number
472 ) is
473 --
474 -- Declare cursors and local variables
475 --
476 l_proc varchar2(72) := g_package||'delete_irc_asg_status';
477 --
478 begin
479 hr_utility.set_location('Entering:'|| l_proc, 10);
480 --
481 -- Issue a savepoint
482 --
483 savepoint delete_irc_asg_status;
484 --
485 -- Call Before Process User Hook
486 --
487 begin
488 irc_asg_status_bk3.delete_irc_asg_status_b
492 );
489 (
490 p_assignment_status_id => p_assignment_status_id
491 , p_object_version_number => p_object_version_number
493 exception
494 when hr_api.cannot_find_prog_unit then
495 hr_api.cannot_find_prog_unit_error
496 (p_module_name => 'delete_irc_asg_status'
497 ,p_hook_type => 'BP'
498 );
499 end;
500 --
501 -- Process Logic
502 --
503 irc_ias_del.del
504 (p_assignment_status_id => p_assignment_status_id
505 ,p_object_version_number => p_object_version_number
506 );
507 --
508 -- Call After Process User Hook
509 --
510 begin
511 irc_asg_status_bk3.delete_irc_asg_status_a
512 (
513 p_assignment_status_id => p_assignment_status_id
514 , p_object_version_number => p_object_version_number
515 );
516 exception
517 when hr_api.cannot_find_prog_unit then
518 hr_api.cannot_find_prog_unit_error
519 (p_module_name => 'delete_irc_asg_status'
520 ,p_hook_type => 'AP'
521 );
522 end;
523 --
527 raise hr_api.validate_enabled;
524 -- When in validation only mode raise the Validate_Enabled exception
525 --
526 if p_validate then
528 end if;
529 --
530 -- Set all output arguments
531 --
532 hr_utility.set_location(' Leaving:'||l_proc, 70);
533 exception
534 when hr_api.validate_enabled then
535 --
536 -- As the Validate_Enabled exception has been raised
537 -- we must rollback to the savepoint
538 --
539 rollback to delete_irc_asg_status;
540 --
541 -- Only set output warning arguments
542 -- (Any key or derived arguments must be set to null
543 -- when validation only mode is being used.)
544 --
545 hr_utility.set_location(' Leaving:'||l_proc, 80);
546 when others then
547 --
548 -- A validation or unexpected error has occured
549 --
550 rollback to delete_irc_asg_status;
551 hr_utility.set_location(' Leaving:'||l_proc, 90);
552 raise;
553 end delete_irc_asg_status;
554 --
555 end IRC_ASG_STATUS_API;