[Home] [Help]
PACKAGE BODY: APPS.IRC_COMMUNICATIONS_API
Source
1 Package Body IRC_COMMUNICATIONS_API as
2 /* $Header: ircomapi.pkb 120.13 2011/10/20 14:23:00 ggkrishn ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'IRC_COMMUNICATIONS_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< DEFINE_COMM_PROPERTIES >-----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure DEFINE_COMM_PROPERTIES
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_object_type in varchar2
16 ,p_object_id in number
17 ,p_default_comm_status in varchar2
18 ,p_allow_attachment_flag in varchar2
19 ,p_auto_notification_flag in varchar2
20 ,p_allow_add_recipients in varchar2
21 ,p_default_moderator in varchar2
22 ,p_attribute_category in varchar2 default null
23 ,p_attribute1 in varchar2 default null
24 ,p_attribute2 in varchar2 default null
25 ,p_attribute3 in varchar2 default null
26 ,p_attribute4 in varchar2 default null
27 ,p_attribute5 in varchar2 default null
28 ,p_attribute6 in varchar2 default null
29 ,p_attribute7 in varchar2 default null
30 ,p_attribute8 in varchar2 default null
31 ,p_attribute9 in varchar2 default null
32 ,p_attribute10 in varchar2 default null
33 ,p_information_category in varchar2 default null
34 ,p_information1 in varchar2 default null
35 ,p_information2 in varchar2 default null
36 ,p_information3 in varchar2 default null
37 ,p_information4 in varchar2 default null
38 ,p_information5 in varchar2 default null
39 ,p_information6 in varchar2 default null
40 ,p_information7 in varchar2 default null
41 ,p_information8 in varchar2 default null
42 ,p_information9 in varchar2 default null
43 ,p_information10 in varchar2 default null
44 ,p_communication_property_id out nocopy number
45 ,p_object_version_number out nocopy number
46 ) is
47 --
48 -- Declare cursors and local variables
49 --
50 l_proc varchar2(72) := g_package||'DEFINE_COMM_PROPERTIES';
51 l_communication_property_id number;
52 l_object_version_number number(9);
53 begin
54 hr_utility.set_location('Entering:'|| l_proc, 10);
55 --
56 -- Issue a savepoint
57 --
58 savepoint DEFINE_COMM_PROPERTIES;
59
60 --
61 -- Call Before Process User Hook
62 --
63 begin
64 irc_communications_bk1.define_comm_properties_b
65 (p_effective_date => p_effective_date
66 ,p_object_type => p_object_type
67 ,p_object_id => p_object_id
68 ,p_default_comm_status => p_default_comm_status
69 ,p_allow_attachment_flag => p_allow_attachment_flag
70 ,p_auto_notification_flag => p_auto_notification_flag
71 ,p_allow_add_recipients => p_allow_add_recipients
72 ,p_default_moderator => p_default_moderator
73 ,p_attribute_category => p_attribute_category
74 ,p_attribute1 => p_attribute1
75 ,p_attribute2 => p_attribute2
76 ,p_attribute3 => p_attribute3
77 ,p_attribute4 => p_attribute4
78 ,p_attribute5 => p_attribute5
79 ,p_attribute6 => p_attribute6
80 ,p_attribute7 => p_attribute7
81 ,p_attribute8 => p_attribute8
82 ,p_attribute9 => p_attribute9
83 ,p_attribute10 => p_attribute10
84 ,p_information_category => p_information_category
85 ,p_information1 => p_information1
86 ,p_information2 => p_information2
87 ,p_information3 => p_information3
88 ,p_information4 => p_information4
89 ,p_information5 => p_information5
90 ,p_information6 => p_information6
91 ,p_information7 => p_information7
92 ,p_information8 => p_information8
93 ,p_information9 => p_information9
94 ,p_information10 => p_information10
95 );
96
97 exception
98 when hr_api.cannot_find_prog_unit then
99 hr_api.cannot_find_prog_unit_error
100 (p_module_name => 'DEFINE_COMM_PROPERTIES'
101 ,p_hook_type => 'BP'
102 );
103 end;
104 --
105 -- Process Logic
106 --
107 irc_cmp_ins.ins
108 (p_effective_date => p_effective_date
109 ,p_object_type => p_object_type
110 ,p_object_id => p_object_id
111 ,p_default_comm_status => p_default_comm_status
112 ,p_allow_attachment_flag => p_allow_attachment_flag
113 ,p_auto_notification_flag => p_auto_notification_flag
114 ,p_allow_add_recipients => p_allow_add_recipients
115 ,p_default_moderator => p_default_moderator
116 ,p_attribute_category => p_attribute_category
117 ,p_attribute1 => p_attribute1
118 ,p_attribute2 => p_attribute2
119 ,p_attribute3 => p_attribute3
120 ,p_attribute4 => p_attribute4
121 ,p_attribute5 => p_attribute5
122 ,p_attribute6 => p_attribute6
123 ,p_attribute7 => p_attribute7
124 ,p_attribute8 => p_attribute8
125 ,p_attribute9 => p_attribute9
126 ,p_attribute10 => p_attribute10
127 ,p_information_category => p_information_category
128 ,p_information1 => p_information1
129 ,p_information2 => p_information2
130 ,p_information3 => p_information3
131 ,p_information4 => p_information4
132 ,p_information5 => p_information5
133 ,p_information6 => p_information6
134 ,p_information7 => p_information7
135 ,p_information8 => p_information8
136 ,p_information9 => p_information9
137 ,p_information10 => p_information10
138 ,p_communication_property_id => l_communication_property_id
139 ,p_object_version_number => l_object_version_number
140 );
141 --
142 -- Call After Process User Hook
143 --
144 begin
145 irc_communications_bk1.define_comm_properties_a
146 (p_effective_date => p_effective_date
147 ,p_object_type => p_object_type
148 ,p_object_id => p_object_id
149 ,p_default_comm_status => p_default_comm_status
150 ,p_allow_attachment_flag => p_allow_attachment_flag
151 ,p_auto_notification_flag => p_auto_notification_flag
152 ,p_allow_add_recipients => p_allow_add_recipients
153 ,p_default_moderator => p_default_moderator
154 ,p_attribute_category => p_attribute_category
155 ,p_attribute1 => p_attribute1
156 ,p_attribute2 => p_attribute2
157 ,p_attribute3 => p_attribute3
158 ,p_attribute4 => p_attribute4
159 ,p_attribute5 => p_attribute5
160 ,p_attribute6 => p_attribute6
161 ,p_attribute7 => p_attribute7
162 ,p_attribute8 => p_attribute8
163 ,p_attribute9 => p_attribute9
164 ,p_attribute10 => p_attribute10
165 ,p_information_category => p_information_category
166 ,p_information1 => p_information1
167 ,p_information2 => p_information2
168 ,p_information3 => p_information3
169 ,p_information4 => p_information4
170 ,p_information5 => p_information5
171 ,p_information6 => p_information6
172 ,p_information7 => p_information7
173 ,p_information8 => p_information8
174 ,p_information9 => p_information9
175 ,p_information10 => p_information10
176 ,p_communication_property_id => l_communication_property_id
177 ,p_object_version_number => l_object_version_number
178 );
179
180 exception
181 when hr_api.cannot_find_prog_unit then
182 hr_api.cannot_find_prog_unit_error
183 (p_module_name => 'DEFINE_COMM_PROPERTIES'
184 ,p_hook_type => 'AP'
185 );
186 end;
187 --
188 -- When in validation only mode raise the Validate_Enabled exception
189 --
190 if p_validate then
191 raise hr_api.validate_enabled;
192 end if;
193 --
194 -- Set all output arguments
195 --
196 p_communication_property_id := l_communication_property_id;
197 p_object_version_number := l_object_version_number;
198 hr_utility.set_location(' Leaving:'||l_proc, 70);
199 exception
200 when hr_api.validate_enabled then
201 --
202 -- As the Validate_Enabled exception has been raised
203 -- we must rollback to the savepoint
204 --
205 rollback to DEFINE_COMM_PROPERTIES;
206 --
207 -- Only set output warning arguments
208 -- (Any key or derived arguments must be set to null
209 -- when validation only mode is being used.)
210 --
211 p_communication_property_id := null;
212 p_object_version_number := null;
213 hr_utility.set_location(' Leaving:'||l_proc, 80);
214 when others then
215 --
216 -- A validation or unexpected error has occured
217 --
218 rollback to DEFINE_COMM_PROPERTIES;
219 -- Reset IN OUT Parameters and set OUT parameters
220 --
221 p_communication_property_id := null;
222 p_object_version_number := null;
223 --
224 hr_utility.set_location(' Leaving:'||l_proc, 90);
225 raise;
226 end DEFINE_COMM_PROPERTIES;
227 --
228 --
229 -- ----------------------------------------------------------------------------
230 -- |----------------------------< UPDATE_COMM_PROPERTIES >---------------------------|
231 -- ----------------------------------------------------------------------------
232 --
233 procedure UPDATE_COMM_PROPERTIES
234 (p_validate in boolean default false
235 ,p_effective_date in date
236 ,p_object_type in varchar2
237 ,p_object_id in number
238 ,p_default_comm_status in varchar2
239 ,p_allow_attachment_flag in varchar2
240 ,p_auto_notification_flag in varchar2
241 ,p_allow_add_recipients in varchar2
242 ,p_default_moderator in varchar2
243 ,p_attribute_category in varchar2 default null
244 ,p_attribute1 in varchar2 default null
245 ,p_attribute2 in varchar2 default null
246 ,p_attribute3 in varchar2 default null
247 ,p_attribute4 in varchar2 default null
248 ,p_attribute5 in varchar2 default null
249 ,p_attribute6 in varchar2 default null
250 ,p_attribute7 in varchar2 default null
251 ,p_attribute8 in varchar2 default null
252 ,p_attribute9 in varchar2 default null
253 ,p_attribute10 in varchar2 default null
254 ,p_information_category in varchar2 default null
255 ,p_information1 in varchar2 default null
256 ,p_information2 in varchar2 default null
257 ,p_information3 in varchar2 default null
258 ,p_information4 in varchar2 default null
259 ,p_information5 in varchar2 default null
260 ,p_information6 in varchar2 default null
261 ,p_information7 in varchar2 default null
262 ,p_information8 in varchar2 default null
263 ,p_information9 in varchar2 default null
264 ,p_information10 in varchar2 default null
265 ,p_communication_property_id in number
266 ,p_object_version_number in out nocopy number
267 ) is
268 --
269 -- Declare cursors and local variables
270 --
271 l_proc varchar2(72) := g_package||'UPDATE_COMM_PROPERTIES';
272 l_object_version_number number(9);
273 --
274 -- Define cursors
275 --
276
277 begin
278 hr_utility.set_location('Entering:'|| l_proc, 10);
279 --
280 -- Issue a savepoint
281 --
282 savepoint UPDATE_COMM_PROPERTIES;
283 --
284
285 --
286 -- Call Before Process User Hook
287 --
288 begin
289 irc_communications_bk2.update_comm_properties_b
290 (p_effective_date => p_effective_date
291 ,p_object_type => p_object_type
292 ,p_object_id => p_object_id
293 ,p_default_comm_status => p_default_comm_status
294 ,p_allow_attachment_flag => p_allow_attachment_flag
295 ,p_auto_notification_flag => p_auto_notification_flag
296 ,p_allow_add_recipients => p_allow_add_recipients
297 ,p_default_moderator => p_default_moderator
298 ,p_attribute_category => p_attribute_category
299 ,p_attribute1 => p_attribute1
300 ,p_attribute2 => p_attribute2
301 ,p_attribute3 => p_attribute3
302 ,p_attribute4 => p_attribute4
303 ,p_attribute5 => p_attribute5
304 ,p_attribute6 => p_attribute6
305 ,p_attribute7 => p_attribute7
306 ,p_attribute8 => p_attribute8
307 ,p_attribute9 => p_attribute9
308 ,p_attribute10 => p_attribute10
309 ,p_information_category => p_information_category
310 ,p_information1 => p_information1
311 ,p_information2 => p_information2
312 ,p_information3 => p_information3
313 ,p_information4 => p_information4
314 ,p_information5 => p_information5
315 ,p_information6 => p_information6
316 ,p_information7 => p_information7
317 ,p_information8 => p_information8
318 ,p_information9 => p_information9
319 ,p_information10 => p_information10
320 ,p_communication_property_id => p_communication_property_id
321 ,p_object_version_number => p_object_version_number
322 );
323
324 exception
325 when hr_api.cannot_find_prog_unit then
326 hr_api.cannot_find_prog_unit_error
327 (p_module_name => 'UPDATE_COMM_PROPERTIES'
328 ,p_hook_type => 'BP'
329 );
330 end;
331 --
332 -- Process Logic
333 --
334 l_object_version_number := p_object_version_number;
335 irc_cmp_upd.upd
336 (p_effective_date => p_effective_date
337 ,p_object_type => p_object_type
338 ,p_object_id => p_object_id
339 ,p_default_comm_status => p_default_comm_status
340 ,p_allow_attachment_flag => p_allow_attachment_flag
341 ,p_auto_notification_flag => p_auto_notification_flag
342 ,p_allow_add_recipients => p_allow_add_recipients
343 ,p_default_moderator => p_default_moderator
344 ,p_attribute_category => p_attribute_category
345 ,p_attribute1 => p_attribute1
346 ,p_attribute2 => p_attribute2
347 ,p_attribute3 => p_attribute3
348 ,p_attribute4 => p_attribute4
349 ,p_attribute5 => p_attribute5
350 ,p_attribute6 => p_attribute6
351 ,p_attribute7 => p_attribute7
352 ,p_attribute8 => p_attribute8
353 ,p_attribute9 => p_attribute9
354 ,p_attribute10 => p_attribute10
355 ,p_information_category => p_information_category
356 ,p_information1 => p_information1
357 ,p_information2 => p_information2
358 ,p_information3 => p_information3
359 ,p_information4 => p_information4
360 ,p_information5 => p_information5
361 ,p_information6 => p_information6
362 ,p_information7 => p_information7
363 ,p_information8 => p_information8
364 ,p_information9 => p_information9
365 ,p_information10 => p_information10
366 ,p_communication_property_id => p_communication_property_id
367 ,p_object_version_number => l_object_version_number
368 );
369
370 --
371 -- Call After Process User Hook
372 --
373 begin
374 irc_communications_bk2.update_comm_properties_a
375 (p_effective_date => p_effective_date
376 ,p_object_type => p_object_type
377 ,p_object_id => p_object_id
378 ,p_default_comm_status => p_default_comm_status
379 ,p_allow_attachment_flag => p_allow_attachment_flag
380 ,p_auto_notification_flag => p_auto_notification_flag
381 ,p_allow_add_recipients => p_allow_add_recipients
382 ,p_default_moderator => p_default_moderator
383 ,p_attribute_category => p_attribute_category
384 ,p_attribute1 => p_attribute1
385 ,p_attribute2 => p_attribute2
386 ,p_attribute3 => p_attribute3
387 ,p_attribute4 => p_attribute4
388 ,p_attribute5 => p_attribute5
389 ,p_attribute6 => p_attribute6
390 ,p_attribute7 => p_attribute7
391 ,p_attribute8 => p_attribute8
392 ,p_attribute9 => p_attribute9
393 ,p_attribute10 => p_attribute10
394 ,p_information_category => p_information_category
395 ,p_information1 => p_information1
396 ,p_information2 => p_information2
397 ,p_information3 => p_information3
398 ,p_information4 => p_information4
399 ,p_information5 => p_information5
400 ,p_information6 => p_information6
401 ,p_information7 => p_information7
402 ,p_information8 => p_information8
403 ,p_information9 => p_information9
404 ,p_information10 => p_information10
405 ,p_communication_property_id => p_communication_property_id
406 ,p_object_version_number => l_object_version_number
407 );
408
409 exception
410 when hr_api.cannot_find_prog_unit then
411 hr_api.cannot_find_prog_unit_error
412 (p_module_name => 'UPDATE_COMM_PROPERTIES'
413 ,p_hook_type => 'AP'
414 );
415 end;
416 --
417 -- When in validation only mode raise the Validate_Enabled exception
418 --
419 if p_validate then
420 raise hr_api.validate_enabled;
421 end if;
422 --
423 -- Set all output arguments
424 --
425 p_object_version_number := l_object_version_number;
426 hr_utility.set_location(' Leaving:'||l_proc, 70);
427 exception
428 when hr_api.validate_enabled then
429 --
430 -- As the Validate_Enabled exception has been raised
431 -- we must rollback to the savepoint
432 --
433 rollback to UPDATE_COMM_PROPERTIES;
434 --
435 -- Only set output warning arguments
436 -- (Any key or derived arguments must be set to null
437 -- when validation only mode is being used.)
438 --
439 hr_utility.set_location(' Leaving:'||l_proc, 80);
440 when others then
441 --
442 -- A validation or unexpected error has occured
443 --
444 rollback to UPDATE_COMM_PROPERTIES;
445 --
446 hr_utility.set_location(' Leaving:'||l_proc, 90);
447 raise;
448 end UPDATE_COMM_PROPERTIES;
449 --
450 --
451 -- ----------------------------------------------------------------------------
452 -- |----------------------------< CREATE_COMMUNICATION >---------------------|
453 -- ----------------------------------------------------------------------------
454 --
455 procedure create_communication
456 (p_validate in boolean default false
457 ,p_effective_date in date
458 ,p_communication_property_id in number
459 ,p_object_type in varchar2
460 ,p_object_id in number
461 ,p_status in varchar2
462 ,p_start_date in date
463 ,p_object_version_number out nocopy number
464 ,p_communication_id out nocopy number
465 ) is
466 --
467 -- Declare cursors and local variables
468 --
469 l_proc varchar2(72) := g_package||'CREATE_COMMUNICATION';
470 l_object_version_number number;
471 l_communication_id number;
472 l_start_date date;
473
474 begin
475 hr_utility.set_location('Entering:'|| l_proc, 10);
476 --
477 -- Issue a savepoint
478 --
479 savepoint CREATE_COMMUNICATION;
480 --
481 --if start_date is null then start_date is equal to effective_date
482 l_start_date := nvl(p_start_date, p_effective_date);
483 --
484 -- Call Before Process User Hook
485 --
486 begin
487 irc_communications_bk3.create_communication_b
488 (p_effective_date => p_effective_date
489 ,p_communication_property_id => p_communication_property_id
490 ,p_object_type => p_object_type
491 ,p_object_id => p_object_id
492 ,p_status => p_status
493 ,p_start_date => l_start_date
494 );
495
496 exception
497 when hr_api.cannot_find_prog_unit then
498 hr_api.cannot_find_prog_unit_error
499 (p_module_name => 'CREATE_COMMUNICATION'
500 ,p_hook_type => 'BP'
501 );
502 end;
503 --
504 -- Process Logic
505 --
506 l_object_version_number := p_object_version_number;
507 irc_cmc_ins.ins
508 (p_effective_date => p_effective_date
509 ,p_communication_property_id => p_communication_property_id
510 ,p_object_type => p_object_type
511 ,p_object_id => p_object_id
512 ,p_status => p_status
513 ,p_start_date => l_start_date
514 ,p_object_version_number => l_object_version_number
515 ,p_communication_id => l_communication_id
516 );
517 --
518 -- Call After Process User Hook
519 --
520 begin
521 irc_communications_bk3.create_communication_a
522 (p_effective_date => p_effective_date
523 ,p_communication_property_id => p_communication_property_id
524 ,p_object_type => p_object_type
525 ,p_object_id => p_object_id
526 ,p_status => p_status
527 ,p_start_date => l_start_date
528 ,p_object_version_number => l_object_version_number
529 ,p_communication_id => l_communication_id
530 );
531
532 exception
533 when hr_api.cannot_find_prog_unit then
534 hr_api.cannot_find_prog_unit_error
535 (p_module_name => 'CREATE_COMMUNICATION'
536 ,p_hook_type => 'AP'
537 );
538 end;
539 --
540 --
541 -- When in validation only mode raise the Validate_Enabled exception
542 --
543 if p_validate then
544 raise hr_api.validate_enabled;
545 end if;
546 --
547 -- Set all output arguments
548 --
549 p_object_version_number := l_object_version_number;
550 p_communication_id := l_communication_id;
551 hr_utility.set_location(' Leaving:'||l_proc, 70);
552 --
553 exception
554 when hr_api.validate_enabled then
555 --
556 -- As the Validate_Enabled exception has been raised
557 -- we must rollback to the savepoint
558 --
559 rollback to CREATE_COMMUNICATION;
560 --
561 -- Only set output warning arguments
562 -- (Any key or derived arguments must be set to null
563 -- when validation only mode is being used.)
564 --
565 p_object_version_number := l_object_version_number;
566 p_communication_id := l_communication_id;
567 hr_utility.set_location(' Leaving:'||l_proc, 80);
568 when others then
569 --
570 -- A validation or unexpected error has occured
571 --
572 rollback to CREATE_COMMUNICATION;
573 --
574 p_object_version_number := l_object_version_number;
575 p_communication_id := l_communication_id;
576 hr_utility.set_location(' Leaving:'||l_proc, 90);
577 raise;
578 --
579 end CREATE_COMMUNICATION;
580 --
581 --
582 -- ----------------------------------------------------------------------------
583 -- |----------------------------< START_COMMUNICATION >---------------------|
584 -- ----------------------------------------------------------------------------
585 --
586 procedure start_communication
587 (p_validate in boolean default false
588 ,p_effective_date in date
589 ,p_communication_property_id in number
590 ,p_object_type in varchar2
591 ,p_object_id in number
592 ,p_start_date in date
593 ,p_object_version_number out nocopy number
594 ,p_communication_id out nocopy number
595 ) is
596 --
597 -- Declare cursors and local variables
598 --
599 l_proc varchar2(72) := g_package||'START_COMMUNICATION';
600 l_object_version_number number;
601 l_communication_id number;
602
603 begin
604 hr_utility.set_location('Entering:'|| l_proc, 10);
605 --
606 -- Issue a savepoint
607 --
608 savepoint START_COMMUNICATION;
609 --
610 -- Process Logic
611 --
612 l_object_version_number := p_object_version_number;
613 CREATE_COMMUNICATION
614 (p_effective_date => p_effective_date
615 ,p_communication_property_id => p_communication_property_id
616 ,p_object_type => p_object_type
617 ,p_object_id => p_object_id
618 ,p_status => 'OPEN'
619 ,p_start_date => p_start_date
620 ,p_object_version_number => l_object_version_number
621 ,p_communication_id => l_communication_id
622 );
623 --
624 --
625 -- When in validation only mode raise the Validate_Enabled exception
626 --
627 if p_validate then
628 raise hr_api.validate_enabled;
629 end if;
630 --
631 -- Set all output arguments
632 --
633 p_object_version_number := l_object_version_number;
634 p_communication_id := l_communication_id;
635 hr_utility.set_location(' Leaving:'||l_proc, 70);
636 --
637 exception
638 when hr_api.validate_enabled then
639 --
640 -- As the Validate_Enabled exception has been raised
641 -- we must rollback to the savepoint
642 --
643 rollback to START_COMMUNICATION;
644 --
645 -- Only set output warning arguments
646 -- (Any key or derived arguments must be set to null
647 -- when validation only mode is being used.)
648 --
649 p_object_version_number := l_object_version_number;
650 p_communication_id := l_communication_id;
651 hr_utility.set_location(' Leaving:'||l_proc, 80);
652 when others then
653 --
654 -- A validation or unexpected error has occured
655 --
656 rollback to START_COMMUNICATION;
657 --
658 p_object_version_number := l_object_version_number;
659 p_communication_id := l_communication_id;
660 hr_utility.set_location(' Leaving:'||l_proc, 90);
661 raise;
662 --
663 end START_COMMUNICATION;
664 --
665 -- ----------------------------------------------------------------------------
666 -- |----------------------------< UPDATE_COMMUNICATION >---------------------|
667 -- ----------------------------------------------------------------------------
668 --
669 procedure update_communication
670 (p_validate in boolean default false
671 ,p_effective_date in date
672 ,p_communication_property_id in number
673 ,p_object_type in varchar2
674 ,p_object_id in number
675 ,p_status in varchar2
676 ,p_start_date in date
677 ,p_end_date in date
678 ,p_communication_id in number
679 ,p_object_version_number in out nocopy number
680 ) is
681 --
682 -- Declare cursors and local variables
683 --
684 l_proc varchar2(72) := g_package||'UPDATE_COMMUNICATION';
685 l_object_version_number number;
686 l_end_date date;
687
688 begin
689 hr_utility.set_location('Entering:'|| l_proc, 10);
690 --
691 -- Issue a savepoint
692 --
693 savepoint UPDATE_COMMUNICATION;
694 --
695 --if start_date is null then start_date is equal to effective_date
696 l_end_date := nvl(p_end_date, p_effective_date);
697 --
698 -- Call Before Process User Hook
699 --
700 begin
701 irc_communications_bk4.update_communication_b
702 (p_effective_date => p_effective_date
703 ,p_communication_property_id => p_communication_property_id
704 ,p_communication_id => p_communication_id
705 ,p_object_type => p_object_type
706 ,p_object_id => p_object_id
707 ,p_status => p_status
708 ,p_start_date => p_start_date
709 ,p_end_date => l_end_date
710 ,p_object_version_number => p_object_version_number
711 );
712
713 exception
714 when hr_api.cannot_find_prog_unit then
715 hr_api.cannot_find_prog_unit_error
716 (p_module_name => 'UPDATE_COMMUNICATION'
717 ,p_hook_type => 'BP'
718 );
719 end;
720 --
721 -- Process Logic
722 --
723 l_object_version_number := p_object_version_number;
724 irc_cmc_upd.upd
725 (p_effective_date => p_effective_date
726 ,p_communication_property_id => p_communication_property_id
727 ,p_object_type => p_object_type
728 ,p_object_id => p_object_id
729 ,p_status => p_status
730 ,p_start_date => p_start_date
731 ,p_end_date => l_end_date
732 ,p_object_version_number => l_object_version_number
733 ,p_communication_id => p_communication_id
734 );
735 --
736 -- Call After Process User Hook
737 --
738 begin
739 irc_communications_bk4.update_communication_a
740 (p_effective_date => p_effective_date
741 ,p_communication_property_id => p_communication_property_id
742 ,p_object_type => p_object_type
743 ,p_object_id => p_object_id
744 ,p_status => p_status
745 ,p_start_date => p_start_date
746 ,p_end_date => l_end_date
747 ,p_object_version_number => l_object_version_number
748 ,p_communication_id => p_communication_id
749 );
750
751 exception
752 when hr_api.cannot_find_prog_unit then
753 hr_api.cannot_find_prog_unit_error
754 (p_module_name => 'UPDATE_COMMUNICATION'
755 ,p_hook_type => 'AP'
756 );
757 end;
758 --
759 --
760 -- When in validation only mode raise the Validate_Enabled exception
761 --
762 if p_validate then
763 raise hr_api.validate_enabled;
764 end if;
765 --
766 -- Set all output arguments
767 --
768 p_object_version_number := l_object_version_number;
769 hr_utility.set_location(' Leaving:'||l_proc, 70);
770 --
771 exception
772 when hr_api.validate_enabled then
773 --
774 -- As the Validate_Enabled exception has been raised
775 -- we must rollback to the savepoint
776 --
777 rollback to UPDATE_COMMUNICATION;
778 --
779 -- Only set output warning arguments
780 -- (Any key or derived arguments must be set to null
781 -- when validation only mode is being used.)
782 --
783 hr_utility.set_location(' Leaving:'||l_proc, 80);
784 when others then
785 --
786 -- A validation or unexpected error has occured
787 --
788 rollback to UPDATE_COMMUNICATION;
789 --
790 hr_utility.set_location(' Leaving:'||l_proc, 90);
791 raise;
792 --
793 end UPDATE_COMMUNICATION;
794 --
795 --
796 -- ----------------------------------------------------------------------------
797 -- |----------------------------< CLOSE_COMMUNICATION >---------------------|
798 -- ----------------------------------------------------------------------------
799 --
800 procedure close_communication
801 (p_validate in boolean default false
802 ,p_effective_date in date
803 ,p_communication_property_id in number
804 ,p_object_type in varchar2
805 ,p_object_id in number
806 ,p_start_date in date
807 ,p_end_date in date
808 ,p_communication_id in number
809 ,p_object_version_number in out nocopy number
810 ) is
811 --
812 -- Declare cursors and local variables
813 --
814 l_proc varchar2(72) := g_package||'CLOSE_COMMUNICATION';
815 l_object_version_number number;
816 l_end_date date;
817
818 begin
819 hr_utility.set_location('Entering:'|| l_proc, 10);
820 --
821 -- Issue a savepoint
822 --
823 savepoint CLOSE_COMMUNICATION;
824 --
825 --if start_date is null then start_date is equal to effective_date
826 l_end_date := nvl(p_end_date, p_effective_date);
827 --
828 -- Process Logic
829 --
830 l_object_version_number := p_object_version_number;
831 update_communication
832 (p_effective_date => p_effective_date
833 ,p_communication_property_id => p_communication_property_id
834 ,p_object_type => p_object_type
835 ,p_object_id => p_object_id
836 ,p_status => 'CLOSED'
837 ,p_start_date => p_start_date
838 ,p_end_date => l_end_date
839 ,p_object_version_number => l_object_version_number
840 ,p_communication_id => p_communication_id
841 );
842 --
843 --
844 -- When in validation only mode raise the Validate_Enabled exception
845 --
846 if p_validate then
847 raise hr_api.validate_enabled;
848 end if;
849 --
850 -- Set all output arguments
851 --
852 p_object_version_number := l_object_version_number;
853 hr_utility.set_location(' Leaving:'||l_proc, 70);
854 --
855 exception
856 when hr_api.validate_enabled then
857 --
858 -- As the Validate_Enabled exception has been raised
859 -- we must rollback to the savepoint
860 --
861 rollback to CLOSE_COMMUNICATION;
862 --
863 -- Only set output warning arguments
864 -- (Any key or derived arguments must be set to null
865 -- when validation only mode is being used.)
866 --
867 p_object_version_number := l_object_version_number;
868 hr_utility.set_location(' Leaving:'||l_proc, 80);
869 when others then
870 --
871 -- A validation or unexpected error has occured
872 --
873 rollback to CLOSE_COMMUNICATION;
874 --
875 p_object_version_number := l_object_version_number;
876 hr_utility.set_location(' Leaving:'||l_proc, 90);
877 raise;
878 --
879 end CLOSE_COMMUNICATION;
880 --
881 -- ----------------------------------------------------------------------------
882 -- |-------------------------< delete_comm_properties >-------------------------|
883 -- ----------------------------------------------------------------------------
884 --
885 procedure delete_comm_properties
886 (
887 p_validate in boolean default false
888 , p_object_version_number in number
889 , p_communication_property_id in number
890 , p_effective_date in date default null
891 )
892 is
893 --
894 -- Declare cursors and local variables
895 --
896 l_proc varchar2(72) := g_package||'delete_comm_properties';
897 --
898 begin
899 hr_utility.set_location('Entering:'|| l_proc, 10);
900 --
901 -- Issue a savepoint
902 --
903 savepoint DELETE_COMM_PROPERTIES;
904 --
905 --
906 -- Validation in addition to Row Handlers
907 --
908 irc_cmp_del.del
909 (
910 p_communication_property_id => p_communication_property_id
911 ,p_object_version_number => P_OBJECT_VERSION_NUMBER
912 );
913 --
914 -- Process Logic
915 --
916 --if a communiucation exist with this property do not delete
917 --
918 -- When in validation only mode raise the Validate_Enabled exception
919 --
920 if p_validate then
921 raise hr_api.validate_enabled;
922 end if;
923 --
924 -- Set all output arguments
925 --
926 --
927 hr_utility.set_location(' Leaving:'||l_proc, 50);
928 exception
929 when hr_api.validate_enabled then
930 --
931 -- As the Validate_Enabled exception has been raised
932 -- we must rollback to the savepoint
933 --
934 rollback to delete_comm_properties;
935 --
936 -- Only set output warning arguments
937 -- (Any key or derived arguments must be set to null
938 -- when validation only mode is being used.)
939 --
940 hr_utility.set_location(' Leaving:'||l_proc, 60);
941 when others then
942 --
943 -- A validation or unexpected error has occured
944 --
945 rollback to delete_comm_properties;
946 hr_utility.set_location(' Leaving:'||l_proc, 70);
947 raise;
948 end delete_comm_properties;
949 --
950 -- ----------------------------------------------------------------------------
951 -- |----------------------------< CREATE_COMM_TOPIC >------------------------------|
952 -- ----------------------------------------------------------------------------
953 --
954 procedure create_comm_topic
955 (p_validate in boolean default false
956 ,p_effective_date in date
957 ,p_communication_id in number
958 ,p_subject in varchar2
959 ,p_status in varchar2
960 ,p_communication_topic_id out nocopy number
961 ,p_object_version_number out nocopy number
962 ) is
963 --
964 -- Declare cursors and local variables
965 --
966 l_proc varchar2(72) := g_package||'CREATE_COMM_TOPIC';
967 l_object_version_number number;
968 l_communication_topic_id number;
969 l_start_date date;
970
971 begin
972 hr_utility.set_location('Entering:'|| l_proc, 10);
973 --
974 -- Issue a savepoint
975 --
976 savepoint CREATE_COMM_TOPIC;
977 --
978 -- Call Before Process User Hook
979 --
980 begin
981 irc_communications_bk5.create_comm_topic_b
982 (p_effective_date => p_effective_date
983 ,p_communication_id => p_communication_id
984 ,p_subject => p_subject
985 ,p_status => p_status
986 );
987
988 exception
989 when hr_api.cannot_find_prog_unit then
990 hr_api.cannot_find_prog_unit_error
991 (p_module_name => 'CREATE_COMM_TOPIC'
992 ,p_hook_type => 'BP'
993 );
994 end;
995 --
996 -- Process Logic
997 --
998 l_object_version_number := p_object_version_number;
999 irc_cmt_ins.ins
1000 (p_effective_date => p_effective_date
1001 ,p_communication_id => p_communication_id
1002 ,p_subject => p_subject
1003 ,p_status => p_status
1004 ,p_communication_topic_id => l_communication_topic_id
1005 ,p_object_version_number => l_object_version_number
1006 );
1007 --
1008 -- Call After Process User Hook
1009 --
1010 begin
1011 irc_communications_bk5.create_comm_topic_a
1012 (p_effective_date => p_effective_date
1013 ,p_communication_id => p_communication_id
1014 ,p_subject => p_subject
1015 ,p_status => p_status
1016 ,p_communication_topic_id => l_communication_topic_id
1017 ,p_object_version_number => l_object_version_number
1018 );
1019
1020 exception
1021 when hr_api.cannot_find_prog_unit then
1022 hr_api.cannot_find_prog_unit_error
1023 (p_module_name => 'CREATE_COMM_TOPIC'
1024 ,p_hook_type => 'AP'
1025 );
1026 end;
1027 --
1028 --
1029 -- When in validation only mode raise the Validate_Enabled exception
1030 --
1031 if p_validate then
1032 raise hr_api.validate_enabled;
1033 end if;
1034 --
1035 -- Set all output arguments
1036 --
1037 p_object_version_number := l_object_version_number;
1038 p_communication_topic_id := l_communication_topic_id;
1039 hr_utility.set_location(' Leaving:'||l_proc, 70);
1040 --
1041 exception
1042 when hr_api.validate_enabled then
1043 --
1044 -- As the Validate_Enabled exception has been raised
1045 -- we must rollback to the savepoint
1046 --
1047 rollback to CREATE_COMM_TOPIC;
1048 --
1049 -- Only set output warning arguments
1050 -- (Any key or derived arguments must be set to null
1051 -- when validation only mode is being used.)
1052 --
1053 p_object_version_number := l_object_version_number;
1054 p_communication_topic_id := l_communication_topic_id;
1055 hr_utility.set_location(' Leaving:'||l_proc, 80);
1056 when others then
1057 --
1058 -- A validation or unexpected error has occured
1059 --
1060 rollback to CREATE_COMM_TOPIC;
1061 --
1062 p_object_version_number := l_object_version_number;
1063 p_communication_topic_id := l_communication_topic_id;
1064 hr_utility.set_location(' Leaving:'||l_proc, 90);
1065 raise;
1066 --
1067 end create_comm_topic;
1068 --
1069 --
1070 -- ----------------------------------------------------------------------------
1071 -- |----------------------------< CREATE_MESSAGE >---------------------|
1072 -- ----------------------------------------------------------------------------
1073 --
1074 procedure create_message
1075 (p_validate in boolean default false
1076 ,p_effective_date in date
1077 ,p_communication_topic_id in number
1078 ,p_parent_id in number
1079 ,p_message_subject in varchar2
1080 ,p_message_post_date in date
1081 ,p_sender_type in varchar2
1082 ,p_sender_id in number
1083 ,p_message_body in varchar2
1084 ,p_document_type in varchar2
1085 ,p_document_id in number
1086 ,p_deleted_flag in varchar2
1087 ,p_communication_message_id out nocopy number
1088 ,p_object_version_number out nocopy number
1089 ) is
1090 --
1091 -- Declare cursors and local variables
1092 --
1093 l_proc varchar2(72) := g_package||'CREATE_MESSAGE';
1094 l_object_version_number number;
1095 l_communication_message_id number;
1096 l_message_post_date date;
1097
1098 begin
1099 hr_utility.set_location('Entering:'|| l_proc, 10);
1100 --
1101 -- Issue a savepoint
1102 --
1103 savepoint CREATE_MESSAGE;
1104 --
1105 --if message_post_date is null then message_post_date is equal to effective_date
1106 l_message_post_date := nvl(p_message_post_date, p_effective_date);
1107 --
1108 -- Call Before Process User Hook
1109 --
1110 begin
1111 irc_communications_bk6.create_message_b
1112 (p_effective_date => p_effective_date
1113 ,p_communication_topic_id => p_communication_topic_id
1114 ,p_parent_id => p_parent_id
1115 ,p_message_subject => p_message_subject
1116 ,p_message_post_date => l_message_post_date
1117 ,p_sender_type => p_sender_type
1118 ,p_sender_id => p_sender_id
1119 ,p_message_body => p_message_body
1120 ,p_document_type => p_document_type
1121 ,p_document_id => p_document_id
1122 ,p_deleted_flag => p_deleted_flag
1123 );
1124
1125 exception
1126 when hr_api.cannot_find_prog_unit then
1127 hr_api.cannot_find_prog_unit_error
1128 (p_module_name => 'CREATE_MESSAGE'
1129 ,p_hook_type => 'BP'
1130 );
1131 end;
1132 --
1133 -- Process Logic
1134 --
1135 l_object_version_number := p_object_version_number;
1136 irc_cmm_ins.ins
1137 (p_effective_date => p_effective_date
1138 ,p_communication_topic_id => p_communication_topic_id
1139 ,p_parent_id => p_parent_id
1140 ,p_message_subject => p_message_subject
1141 ,p_message_post_date => l_message_post_date
1142 ,p_sender_type => p_sender_type
1143 ,p_sender_id => p_sender_id
1144 ,p_message_body => p_message_body
1145 ,p_document_type => p_document_type
1146 ,p_document_id => p_document_id
1147 ,p_deleted_flag => p_deleted_flag
1148 ,p_communication_message_id => l_communication_message_id
1149 ,p_object_version_number => l_object_version_number
1150 );
1151 --
1152 -- Call After Process User Hook
1153 --
1154 begin
1155 irc_communications_bk6.create_message_a
1156 (p_effective_date => p_effective_date
1157 ,p_communication_topic_id => p_communication_topic_id
1158 ,p_parent_id => p_parent_id
1159 ,p_message_subject => p_message_subject
1160 ,p_message_post_date => l_message_post_date
1161 ,p_sender_type => p_sender_type
1162 ,p_sender_id => p_sender_id
1163 ,p_message_body => p_message_body
1164 ,p_document_type => p_document_type
1165 ,p_document_id => p_document_id
1166 ,p_deleted_flag => p_deleted_flag
1167 ,p_communication_message_id => l_communication_message_id
1168 ,p_object_version_number => l_object_version_number
1169 );
1170
1171 exception
1172 when hr_api.cannot_find_prog_unit then
1173 hr_api.cannot_find_prog_unit_error
1174 (p_module_name => 'CREATE_MESSAGE'
1175 ,p_hook_type => 'AP'
1176 );
1177 end;
1178 --
1179 --
1180 -- When in validation only mode raise the Validate_Enabled exception
1181 --
1182 if p_validate then
1183 raise hr_api.validate_enabled;
1184 end if;
1185 --
1186 -- Set all output arguments
1187 --
1188 p_object_version_number := l_object_version_number;
1189 p_communication_message_id := l_communication_message_id;
1190 hr_utility.set_location(' Leaving:'||l_proc, 70);
1191 --
1192 exception
1193 when hr_api.validate_enabled then
1194 --
1195 -- As the Validate_Enabled exception has been raised
1196 -- we must rollback to the savepoint
1197 --
1198 rollback to CREATE_MESSAGE;
1199 --
1200 -- Only set output warning arguments
1201 -- (Any key or derived arguments must be set to null
1202 -- when validation only mode is being used.)
1203 --
1204 p_object_version_number := l_object_version_number;
1205 p_communication_message_id := l_communication_message_id;
1206 hr_utility.set_location(' Leaving:'||l_proc, 80);
1207 when others then
1208 --
1209 -- A validation or unexpected error has occured
1210 --
1211 rollback to CREATE_MESSAGE;
1212 --
1213 p_object_version_number := l_object_version_number;
1214 p_communication_message_id := l_communication_message_id;
1215 hr_utility.set_location(' Leaving:'||l_proc, 90);
1216 raise;
1217 --
1218 end create_message;
1219 --
1220 -- ----------------------------------------------------------------------------
1221 -- |----------------------------< UPDATE_MESSAGE >----------------------------|
1222 -- ----------------------------------------------------------------------------
1223 --
1224 procedure update_message
1225 (p_validate in boolean default false
1226 ,p_effective_date in date
1227 ,p_deleted_flag in varchar2
1228 ,p_communication_message_id in number
1229 ,p_object_version_number in out nocopy number
1230 ) is
1231 --
1232 -- Declare cursors and local variables
1233 --
1234 l_proc varchar2(72) := g_package||'UPDATE_MESSAGE';
1235 l_object_version_number number;
1236
1237 begin
1238 hr_utility.set_location('Entering:'|| l_proc, 10);
1239 --
1240 -- Issue a savepoint
1241 --
1242 savepoint UPDATE_MESSAGE;
1243 --
1244 -- Call Before Process User Hook
1245 --
1246 begin
1247 irc_communications_bk7.update_message_b
1248 (p_effective_date => p_effective_date
1249 ,p_deleted_flag => p_deleted_flag
1250 ,p_communication_message_id => p_communication_message_id
1251 ,p_object_version_number => l_object_version_number
1252 );
1253
1254 exception
1255 when hr_api.cannot_find_prog_unit then
1256 hr_api.cannot_find_prog_unit_error
1257 (p_module_name => 'UPDATE_MESSAGE'
1258 ,p_hook_type => 'BP'
1259 );
1260 end;
1261 --
1262 -- Process Logic
1263 --
1264 l_object_version_number := p_object_version_number;
1265 irc_cmm_upd.upd
1266 (p_effective_date => p_effective_date
1267 ,p_deleted_flag => p_deleted_flag
1268 ,p_communication_message_id => p_communication_message_id
1269 ,p_object_version_number => l_object_version_number
1270 );
1271 --
1272 -- Call After Process User Hook
1273 --
1274 begin
1275 irc_communications_bk7.update_message_a
1276 (p_effective_date => p_effective_date
1277 ,p_deleted_flag => p_deleted_flag
1278 ,p_communication_message_id => p_communication_message_id
1279 ,p_object_version_number => l_object_version_number
1280 );
1281
1282 exception
1283 when hr_api.cannot_find_prog_unit then
1284 hr_api.cannot_find_prog_unit_error
1285 (p_module_name => 'UPDATE_MESSAGE'
1286 ,p_hook_type => 'AP'
1287 );
1288 end;
1289 --
1290 --
1291 -- When in validation only mode raise the Validate_Enabled exception
1292 --
1293 if p_validate then
1294 raise hr_api.validate_enabled;
1295 end if;
1296 --
1297 -- Set all output arguments
1298 --
1299 p_object_version_number := l_object_version_number;
1300 hr_utility.set_location(' Leaving:'||l_proc, 70);
1301 --
1302 exception
1303 when hr_api.validate_enabled then
1304 --
1305 -- As the Validate_Enabled exception has been raised
1306 -- we must rollback to the savepoint
1307 --
1308 rollback to UPDATE_MESSAGE;
1309 --
1310 -- Only set output warning arguments
1311 -- (Any key or derived arguments must be set to null
1312 -- when validation only mode is being used.)
1313 --
1314 p_object_version_number := l_object_version_number;
1315 hr_utility.set_location(' Leaving:'||l_proc, 80);
1316 when others then
1317 --
1318 -- A validation or unexpected error has occured
1319 --
1320 rollback to UPDATE_MESSAGE;
1321 --
1322 p_object_version_number := l_object_version_number;
1323 hr_utility.set_location(' Leaving:'||l_proc, 90);
1324 raise;
1325 --
1326 end update_message;
1327 --
1328 --
1329 -- ----------------------------------------------------------------------------
1330 -- |----------------------------< ADD_RECIPIENT >-----------------------------|
1331 -- ----------------------------------------------------------------------------
1332 --
1333 procedure add_recipient
1334 (p_validate in boolean default false
1335 ,p_effective_date in date
1336 ,p_communication_object_type in varchar2
1337 ,p_communication_object_id in number
1338 ,p_recipient_type in varchar2
1339 ,p_recipient_id in number
1340 ,p_start_date_active in date
1341 ,p_end_date_active in date
1342 ,p_primary_flag in varchar2
1343 ,p_communication_recipient_id out nocopy number
1344 ,p_object_version_number out nocopy number
1345 ) is
1346 --
1347 -- Declare cursors and local variables
1348 --
1349 l_proc varchar2(72) := g_package||'ADD_RECIPIENT';
1350 l_object_version_number number;
1351 l_communication_recipient_id number;
1352 l_start_date_active date;
1353
1354 begin
1355 hr_utility.set_location('Entering:'|| l_proc, 10);
1356 --
1357 -- Issue a savepoint
1358 --
1359 savepoint ADD_RECIPIENT;
1360 --
1361 --if start_date_active is null then start_date_active is equal to effective_date
1362 l_start_date_active := nvl(p_start_date_active, p_effective_date);
1363 --
1364 -- Call Before Process User Hook
1365 --
1366 begin
1367 irc_communications_bk8.ADD_RECIPIENT_b
1368 (p_effective_date => p_effective_date
1369 ,p_communication_object_type => p_communication_object_type
1370 ,p_communication_object_id => p_communication_object_id
1371 ,p_recipient_type => p_recipient_type
1372 ,p_recipient_id => p_recipient_id
1373 ,p_start_date_active => l_start_date_active
1374 ,p_end_date_active => p_end_date_active
1375 ,p_primary_flag => p_primary_flag
1376 );
1377
1378 exception
1379 when hr_api.cannot_find_prog_unit then
1380 hr_api.cannot_find_prog_unit_error
1381 (p_module_name => 'ADD_RECIPIENT'
1382 ,p_hook_type => 'BP'
1383 );
1384 end;
1385 --
1386 -- Process Logic
1387 --
1388 l_object_version_number := p_object_version_number;
1389 irc_cmr_ins.ins
1390 (p_effective_date => p_effective_date
1391 ,p_communication_object_type => p_communication_object_type
1392 ,p_communication_object_id => p_communication_object_id
1393 ,p_recipient_type => p_recipient_type
1394 ,p_recipient_id => p_recipient_id
1395 ,p_start_date_active => l_start_date_active
1396 ,p_end_date_active => p_end_date_active
1397 ,p_primary_flag => p_primary_flag
1398 ,p_communication_recipient_id => l_communication_recipient_id
1399 ,p_object_version_number => p_object_version_number
1400 );
1401 --
1402 -- Call After Process User Hook
1403 --
1404 begin
1405 irc_communications_bk8.add_recipient_a
1406 (p_effective_date => p_effective_date
1407 ,p_communication_object_type => p_communication_object_type
1408 ,p_communication_object_id => p_communication_object_id
1409 ,p_recipient_type => p_recipient_type
1410 ,p_recipient_id => p_recipient_id
1411 ,p_start_date_active => l_start_date_active
1412 ,p_end_date_active => p_end_date_active
1413 ,p_primary_flag => p_primary_flag
1414 ,p_communication_recipient_id => l_communication_recipient_id
1415 ,p_object_version_number => p_object_version_number
1416 );
1417
1418 exception
1419 when hr_api.cannot_find_prog_unit then
1420 hr_api.cannot_find_prog_unit_error
1421 (p_module_name => 'ADD_RECIPIENT'
1422 ,p_hook_type => 'AP'
1423 );
1424 end;
1425 --
1426 --
1427 -- When in validation only mode raise the Validate_Enabled exception
1428 --
1429 if p_validate then
1430 raise hr_api.validate_enabled;
1431 end if;
1432 --
1433 -- Set all output arguments
1434 --
1435 p_object_version_number := l_object_version_number;
1436 p_communication_recipient_id := l_communication_recipient_id;
1437 hr_utility.set_location(' Leaving:'||l_proc, 70);
1438 --
1439 exception
1440 when hr_api.validate_enabled then
1441 --
1442 -- As the Validate_Enabled exception has been raised
1443 -- we must rollback to the savepoint
1444 --
1445 rollback to ADD_RECIPIENT;
1446 --
1447 -- Only set output warning arguments
1448 -- (Any key or derived arguments must be set to null
1449 -- when validation only mode is being used.)
1450 --
1451 p_object_version_number := l_object_version_number;
1452 p_communication_recipient_id := l_communication_recipient_id;
1453 hr_utility.set_location(' Leaving:'||l_proc, 80);
1454 when others then
1455 --
1456 -- A validation or unexpected error has occured
1457 --
1458 rollback to ADD_RECIPIENT;
1459 --
1460 p_object_version_number := l_object_version_number;
1461 p_communication_recipient_id := l_communication_recipient_id;
1462 hr_utility.set_location(' Leaving:'||l_proc, 90);
1463 raise;
1464 --
1465 end add_recipient;
1466 --
1467 -- ----------------------------------------------------------------------------
1468 -- |----------------------------< GET_RECIPIENT_LIST >---------------------|
1469 -- ----------------------------------------------------------------------------
1470 --
1471 function get_rcpt_list(p_object_id IN number,filter varchar2 ) return varchar2 is
1472 --
1473 Cursor cur_vac_id(p_topic_id irc_comm_topics.communication_topic_id%TYPE) is
1474 --get vacancy_id from topic_id
1475 select icp.object_id
1476 from
1477 irc_comm_properties icp,
1478 irc_communications ic,
1479 irc_comm_topics ict
1480 where
1481 ict.communication_topic_id = p_topic_id
1482 and ic.communication_id = ict.communication_id
1483 and icp.communication_property_id = ic.communication_property_id
1484 and icp.object_type = 'VACANCY' ;
1485 --
1486 Cursor cur_rec_mgr(p_vacancy_id per_all_vacancies.vacancy_id%TYPE) is
1487 --get recruiter_id and hiring_manager_id for a given vacancy
1488 select pav.recruiter_id
1489 ,pav.manager_id
1490 from
1491 per_all_vacancies pav
1492 where
1493 pav.vacancy_id = p_vacancy_id;
1494 --
1495 Cursor cur_team(p_vacancy_id per_all_vacancies.vacancy_id%TYPE) is
1496 --get recruiting team members for a given vacancy
1497 select team.person_id
1498 from
1499 irc_rec_team_members team
1500 where
1501 team.vacancy_id = p_vacancy_id;
1502 --
1503 Cursor cur_apl(p_topic_id irc_comm_topics.communication_topic_id%TYPE) is
1504 --get applicant for application attached to the given topic
1505 select paf.person_id
1506 from
1507 per_all_assignments_f paf,
1508 irc_communications ic,
1509 irc_comm_topics ict
1510 where
1511 ict.communication_topic_id = p_topic_id
1512 and ic.communication_id = ict.communication_id
1513 and paf.assignment_id = ic.object_id
1514 and ic.object_type = 'APPL';
1515 --
1516 Cursor cur_tpc_frm_msg(p_message_id irc_comm_messages.communication_message_id%TYPE) is
1517 --get topic_id from message_id
1518 select msg.communication_topic_id
1519 from
1520 irc_comm_messages msg
1521 where
1522 msg.communication_message_id = p_message_id;
1523 --
1524 Cursor cur_tpc_frm_rcpt(p_recipient_id irc_comm_recipients.recipient_id%TYPE) is
1525 --get topic_id from recipient_id
1526 select icr.communication_object_id
1527 from
1528 irc_comm_recipients icr
1529 where
1530 icr.communication_recipient_id = p_recipient_id
1531 and icr.communication_object_type = 'TOPIC';
1532 --
1533 Cursor cur_ppf(p_person_id per_all_people_f.person_id%TYPE) is
1534 --get person full name
1535 select ppf.full_name
1536 from per_all_people_f ppf
1537 where
1538 ppf.person_id = p_person_id
1539 and trunc(sysdate) between Effective_start_date and Effective_end_date;
1540 --
1541 --get agency name
1542 Cursor cur_agency(l_recipient_id po_vendors.vendor_id%TYPE) is
1543 select pov.vendor_name
1544 from
1545 po_vendors pov
1546 where
1547 pov.vendor_id = l_recipient_id;
1548 --
1549 l_list varchar2(32767);
1550 l_vacancy_id number;
1551 l_recipient_id number;
1552 l_recipient_type irc_comm_messages.sender_type%TYPE;
1553 l_topic_id number;
1554 l_rec_id number;
1555 l_mgr_id number;
1556 l_apl_id number;
1557 l_team_id number;
1558 l_full_name per_all_people_f.full_name%TYPE;
1559 l_meaning_rec hr_lookups.meaning%TYPE;
1560 l_meaning_mgr hr_lookups.meaning%TYPE;
1561 l_meaning_apl hr_lookups.meaning%TYPE;
1562 l_meaning_tm hr_lookups.meaning%TYPE;
1563 l_meaning_agncy hr_lookups.meaning%TYPE;
1564 l_role varchar2(32767);
1565 --
1566 TYPE cur_typ IS REF CURSOR;
1567 cur_rcpt cur_typ;
1568 rec_team cur_team%ROWTYPE;
1569 l_found boolean;
1570 query_str VARCHAR2(32767);
1571 --
1572 begin
1573 ----------------
1574
1575 --fetching lookup meaning
1576 l_meaning_rec := get_lookup_meaning('REC','IRC_VAC_ROLE');
1577 l_meaning_mgr := get_lookup_meaning('MGR','IRC_VAC_ROLE');
1578 l_meaning_apl := get_lookup_meaning('APL','IRC_VAC_ROLE');
1579 l_meaning_tm := get_lookup_meaning('TM','IRC_VAC_ROLE');
1580 l_meaning_agncy := get_lookup_meaning('AGNCY','IRC_VAC_ROLE');
1581
1582 -----------------------Recipient query making start------------------------------
1583
1584 --get recipients for a given topic_id
1585 query_str := ' select icr.recipient_id, icr.recipient_type' ||
1586 ' from' ||
1587 ' irc_comm_recipients icr'||
1588 ' where icr.communication_object_id = :1' ||
1589 ' and icr.communication_object_type= ''TOPIC''';
1590
1591 ----------Fetch only selected recipients of a topic
1592 ----------or message based on given filter-value----
1593
1594 --get topic creator
1595 if filter = 'CREATOR' then
1596 query_str := query_str || ' and icr.primary_flag=''Y'' ' ||
1597 ' and icr.communication_object_type = ''TOPIC''';
1598 end if;
1599
1600 --get all recipients of topic
1601 if filter = 'TOPIC_ALL' then
1602 query_str := query_str || ' and icr.communication_object_type = ''TOPIC'''||
1603 ' order by icr.communication_recipient_id';
1604 end if;
1605
1606 --get message sender
1607 if filter = 'SENDER' then
1608 query_str := ' select icm.sender_id, icm.sender_type' ||
1609 ' from' ||
1610 ' irc_comm_messages icm'||
1611 ' where icm.communication_message_id =:1' ;
1612 end if;
1613
1614 --get all recipients of message
1615 if filter = 'MESSAGE_ALL' then
1616
1617 query_str :=' select icr.recipient_id,icr.recipient_type' ||
1618 ' from' ||
1619 ' irc_comm_messages icm,' ||
1620 ' irc_comm_recipients icr' ||
1621 ' where' ||
1622 ' icm.communication_message_id= :1' ||
1623 ' and icr.communication_object_id = icm.communication_topic_id' ||
1624 ' and icr.communication_object_type = ''TOPIC''' ||
1625 ' and icr.recipient_id<>icm.sender_id' ||
1626 ' and icm.message_post_date' ||
1627 ' between ' ||
1628 ' icr.start_date_active'||
1629 ' and nvl(icr.end_date_active,icm.message_post_date)'||
1630 ' order by icr.communication_recipient_id';
1631
1632 end if;
1633
1634 if filter = 'ROLE' then
1635 query_str :=' select icr.recipient_id,icr.recipient_type' ||
1636 ' from' ||
1637 ' irc_comm_recipients icr' ||
1638 ' where' ||
1639 ' icr.communication_recipient_id =:1';
1640 end if;
1641 -----------------------Recipient query making finish------------------------------
1642
1643 --if filter is 'SENDER' or 'MESSAGE_ALL' then p_object_id is message_id.
1644 --if filter is 'CREATOR' or 'TOPIC_ALL' then p_object_id is topic_id.
1645 --if filter is 'ROLE' then p_object_id is recipient_id and just role
1646 --is returned for given recipient.
1647
1648 --getting topic_id for given message_id
1649 if filter = 'SENDER' OR filter = 'MESSAGE_ALL' then
1650 OPEN cur_tpc_frm_msg(p_object_id);
1651 FETCH cur_tpc_frm_msg INTO l_topic_id;
1652 CLOSE cur_tpc_frm_msg;
1653 end if;
1654
1655 --getting topic_id for given recipient_id
1656 if filter = 'ROLE' then
1657 OPEN cur_tpc_frm_rcpt(p_object_id);
1658 FETCH cur_tpc_frm_rcpt INTO l_topic_id;
1659 CLOSE cur_tpc_frm_rcpt;
1660 end if;
1661
1662
1663 if filter='CREATOR' or filter = 'TOPIC_ALL' then
1664 l_topic_id := p_object_id;
1665 end if;
1666
1667 --fetch vacancy_id for a given topic
1668 OPEN cur_vac_id(l_topic_id);
1669 FETCH cur_vac_id INTO l_vacancy_id;
1670 CLOSE cur_vac_id;
1671
1672 --fetch recruiter and hiring manager for a given vacancy
1673 OPEN cur_rec_mgr(l_vacancy_id);
1674 FETCH cur_rec_mgr INTO l_rec_id, l_mgr_id;
1675 CLOSE cur_rec_mgr;
1676
1677 --fetch applicant for a given application with which topic is attached
1678 OPEN cur_apl(l_topic_id);
1679 FETCH cur_apl INTO l_apl_id;
1680 CLOSE cur_apl;
1681
1682 --Looping through all recipients and appending their full-name with
1683 --their role in context of vacancy with which topic/message attached.
1684
1685 -----------START OF LOOP FOR PROCESSING RECIPIENTS--------------------------------------
1686 l_list := '';
1687
1688 OPEN cur_rcpt FOR query_str USING p_object_id;
1689 LOOP
1690 FETCH cur_rcpt INTO l_recipient_id, l_recipient_type ;
1691 EXIT WHEN cur_rcpt%NOTFOUND;
1692 l_found := false;
1693 l_role := '';
1694 l_full_name := '';
1695
1696 --If person_type is 'PERSON' then following role-possibilities are checked
1697 --recruiter, hiring manager, applicant, recruiting team-member.
1698 --If none of the above match is successfull then just name returned for given
1699 --recipient_id(person_id)
1700
1701 if l_recipient_type='PERSON' then
1702
1703 --------Comparing with team-members
1704 FOR rec_team IN cur_team(l_vacancy_id)
1705 LOOP
1706 if rec_team.person_id = l_recipient_id then
1707
1708 l_found := true;
1709
1710 OPEN cur_ppf(l_recipient_id);
1711 FETCH cur_ppf INTO l_full_name;
1712
1713 if cur_ppf%FOUND then
1714 l_role := l_meaning_tm;
1715 end if;
1716
1717 CLOSE cur_ppf;
1718
1719 end if;
1720 END LOOP;
1721
1722 --------Comparing with recruiter
1723 if l_rec_id = l_recipient_id then
1724 l_found := true;
1725
1726 OPEN cur_ppf(l_recipient_id);
1727 FETCH cur_ppf INTO l_full_name;
1728
1729 if cur_ppf%FOUND then
1730 l_role := l_meaning_rec;
1731 end if;
1732
1733 CLOSE cur_ppf;
1734
1735 end if;
1736 --------Comparing with manager
1737 if l_mgr_id = l_recipient_id then
1738
1739 l_found := true;
1740
1741 OPEN cur_ppf(l_recipient_id);
1742 FETCH cur_ppf INTO l_full_name;
1743
1744 if cur_ppf%FOUND then
1745 l_role := l_meaning_mgr;
1746 end if;
1747
1748 CLOSE cur_ppf;
1749 end if;
1750
1751 --------Comparing with applicant
1752 if l_apl_id = l_recipient_id then
1753 l_found := true;
1754 OPEN cur_ppf(l_recipient_id);
1755 FETCH cur_ppf INTO l_full_name;
1756
1757 if cur_ppf%FOUND then
1758 l_role := l_meaning_apl;
1759 end if;
1760
1761 CLOSE cur_ppf;
1762 end if;
1763
1764 -------If not matched with anyone then just fetch name
1765 if l_found = false then
1766 OPEN cur_ppf(l_recipient_id);
1767 FETCH cur_ppf INTO l_full_name;
1768 CLOSE cur_ppf;
1769 end if;
1770
1771
1772 end if; ----end for checking roles when person_type='PERSON'
1773
1774
1775 ---if person_type is agency
1776 if l_recipient_type = 'AGENCY' then
1777 OPEN cur_agency(l_recipient_id);
1778 FETCH cur_agency INTO l_full_name;
1779 if cur_agency%FOUND then
1780 l_role := l_meaning_agncy;
1781 end if;
1782 CLOSE cur_agency;
1783 end if;
1784
1785 if filter <> 'ROLE' then
1786 l_list := l_list ||l_role ||':'|| l_full_name || ' ';
1787 end if;
1788
1789 if filter='ROLE' then
1790 l_list := l_role;
1791 end if;
1792
1793 -----------END OF LOOP FOR PROCESSING RECIPIENTS--------------------------------------
1794
1795 END LOOP;
1796
1797 close cur_rcpt;
1798
1799 if filter='MESSAGE_ALL' and length(l_list||'a')=1 then
1800 l_list := fnd_message.GET_STRING('PER','IRC_412532_SELF_MESSAGE');
1801 end if;
1802
1803 RETURN l_list;
1804
1805 EXCEPTION
1806 WHEN others THEN
1807 RETURN '';
1808
1809 END;
1810
1811 --
1812 -- ----------------------------------------------------------------------------
1813 -- |----------------------------< GET_LOOKUP_MEANING >------------------------|
1814 -- ----------------------------------------------------------------------------
1815 --
1816
1817 function get_lookup_meaning(
1818 p_lookup_code hr_lookups.lookup_code%TYPE
1819 ,p_lookup_type hr_lookups.lookup_type%TYPE)
1820 return varchar2 is
1821 --
1822 Cursor cur_lookup(p_lookup_code hr_lookups.lookup_code%TYPE,p_lookup_type hr_lookups.lookup_type%TYPE) is
1823 --get lookup-meaning from lookup-code,lookup-type
1824 select hr_lookups.meaning
1825 from hr_lookups
1826 where lookup_code = p_lookup_code
1827 and lookup_type = p_lookup_type
1828 and enabled_flag = 'Y'
1829 and sysdate between
1830 nvl(start_date_active, sysdate)
1831 and nvl(end_date_active, sysdate);
1832 --
1833 l_lookup_meaning hr_lookups.meaning%TYPE;
1834 --
1835 BEGIN
1836 --fetch vacancy_id for a given topic
1837 OPEN cur_lookup(p_lookup_code, p_lookup_type);
1838 FETCH cur_lookup INTO l_lookup_meaning;
1839 CLOSE cur_lookup;
1840
1841 RETURN l_lookup_meaning;
1842
1843 EXCEPTION
1844 WHEN others THEN
1845 RETURN '';
1846 END;
1847
1848
1849 --
1850 -- ----------------------------------------------------------------------------
1851 -- |----------------------------< TOKENIZER >----------------------------------|
1852 -- ----------------------------------------------------------------------------
1853 --
1854 -- This is a private procedure and is meant to be called from getIdArray
1855 -- procedure only
1856 --
1857 procedure tokenizer
1858 (
1859 p_nStartIn IN NUMBER,
1860 p_sDelimeterIn in VARCHAR2,
1861 p_sDelimetedListIn in VARCHAR2,
1862 p_sTokenOut OUT nocopy VARCHAR2,
1863 p_nNextPosOut OUT nocopy NUMBER
1864 )
1865 AS
1866 l_nPos1 number;
1867 l_nPos2 number;
1868
1869 BEGIN
1870 p_sTokenOut :='NULL' ;
1871
1872 l_nPos1 := Instr (p_sDelimetedListIn ,p_sDelimeterIn ,p_nStartIn);
1873
1874 IF l_nPos1 = 0 then
1875 p_sTokenOut :='NULL' ;
1876 ELSE
1877 l_nPos2 := Instr (p_sDelimetedListIn ,p_sDelimeterIn ,l_nPos1 + 1);
1878 IF l_nPos2 = 0 then
1879 p_sTokenOut := Rtrim(Ltrim(Substr(p_sDelimetedListIn,l_nPos1 + 1)));
1880 p_nNextPosOut := l_nPos2;
1881 else
1882 p_sTokenOut := Rtrim(Ltrim(Substr(p_sDelimetedListIn ,l_nPos1 + 1 , l_nPos2 - l_nPos1 - 1)));
1883 p_nNextPosOut := l_nPos2;
1884 END IF;
1885
1886 END IF;
1887
1888 END tokenizer ;
1889
1890
1891 --
1892 -- ----------------------------------------------------------------------------
1893 -- |----------------------------< GETIDARRAY>----------------------------------|
1894 -- ----------------------------------------------------------------------------
1895 --
1896 --This procedure returns an array of assignmentId/recipientId when
1897 --a comma-separated assignIdList/recipientIdList is passed to it.
1898 --The list is expected to be of format ",1" or ",1,24,34"
1899
1900 procedure getIdArray
1901 (
1902 sbuf in varchar2,
1903 data out nocopy assoc_arr
1904 )
1905 as
1906 sepr varchar2(1);
1907 sres varchar2(200);
1908 pos number;
1909 istart number;
1910 num number;
1911 listLength number;
1912 begin
1913 num:=1;
1914 sepr := ',';
1915 istart := 1;
1916 tokenizer (istart ,sepr,sbuf,sres,pos);
1917
1918 if sres<>'NULL' then
1919 data(1) := sres;
1920 end if;
1921
1922 while (pos <> 0)
1923 loop
1924 num := num+1;
1925 istart := pos;
1926 tokenizer (istart ,sepr,sbuf,sres,pos );
1927 data(num) := sres;
1928 end loop;
1929
1930 END getIdArray;
1931 --
1932 -- ----------------------------------------------------------------------------
1933 -- |----------------------------< COMMUNICATION_EXISTS>------------------------|
1934 -- ----------------------------------------------------------------------------
1935 --
1936 function communication_exists
1937 (
1938 p_assignmentIdIn in number
1939 ,p_communicationIdOut out nocopy number
1940 ,p_communicationStatusOut out nocopy varchar2
1941 ,p_object_version_numberOut out nocopy number
1942 ,p_object_typeOut out nocopy varchar2
1943 ,p_start_dateOut out nocopy date
1944 ) return boolean is
1945
1946 cursor cur_get_communication is
1947 select communication_id, status, object_version_number, object_type, start_date from irc_communications
1948 where object_type ='APPL' and object_id = p_assignmentIdIn;
1949
1950 begin
1951 open cur_get_communication;
1952 fetch cur_get_communication into p_communicationIdOut,p_communicationStatusOut
1953 ,p_object_version_numberOut,p_object_typeOut
1954 ,p_start_dateOut ;
1955
1956 if cur_get_communication%notfound then
1957 close cur_get_communication;
1958 return false;
1959 else
1960 close cur_get_communication;
1961 return true;
1962 end if;
1963 end;
1964 --
1965 -- ----------------------------------------------------------------------------
1966 -- |----------------------------< START_MASS_COMMUNICATION---------------------|
1967 -- ----------------------------------------------------------------------------
1968 --
1969 -- This procedure takes a comma-separated list of assignmentIds
1970 -- eg.(,234,235) as input.
1971 --
1972 procedure start_mass_communication
1973 (
1974 p_assignmentIdListIn in varchar2
1975 ) is
1976 PRAGMA AUTONOMOUS_TRANSACTION;
1977 --
1978 --Cursor to get the communication property. This is required in case
1979 --communication status needs to be updated or a new communication
1980 --needs to be created.
1981 --
1982 cursor cur_get_comm_property(p_assignment_id number) is
1983 select communication_property_id from irc_comm_properties where
1984 object_id = (select distinct vacancy_id from per_all_assignments_f where
1985 assignment_id =p_assignment_id )
1986 and object_type ='VACANCY' ;
1987
1988 cursor cur_get_default_comm_status(p_assignment_id number) is
1989 select default_comm_status from irc_comm_properties where
1990 object_id = (select distinct vacancy_id from per_all_assignments_f where
1991 assignment_id =p_assignment_id )
1992 and object_type ='VACANCY' ;
1993
1994
1995 l_assignmentIdArray assoc_arr;
1996 l_listLength number;
1997 l_communication_id irc_communications.communication_id%type;
1998 l_communication_status irc_communications.status%type;
1999 l_assignmentId irc_communications.object_id%type;
2000 l_comm_property_id irc_comm_properties.communication_property_id%type;
2001 l_object_version_number number;
2002 l_object_type irc_communications.object_type%type;
2003 l_start_date date;
2004 begin
2005 --
2006 --Get assignmentId array
2007 --
2008 getIdArray(p_assignmentIdListIn,l_assignmentIdArray);
2009 l_listLength := l_assignmentIdArray.count;
2010 --
2011 --Loop through all assignments
2012 --
2013 for tempIndex in 1 .. l_listLength
2014 loop
2015 l_assignmentId := l_assignmentIdArray(tempIndex);
2016 --Check if communication exists
2017 if(communication_exists(p_assignmentIdIn=>l_assignmentId
2018 ,p_communicationIdOut=>l_communication_id
2019 ,p_communicationStatusOut=>l_communication_status
2020 ,p_object_version_numberOut=> l_object_version_number
2021 ,p_object_typeOut=>l_object_type
2022 ,p_start_dateOut=>l_start_date ))
2023 then
2024 --If communication exists and is 'CLOSED', then update the status to 'OPEN'
2025 if l_communication_status<>'OPEN' then
2026
2027 open cur_get_comm_property(l_assignmentId);
2028 fetch cur_get_comm_property into l_comm_property_id ;
2029 close cur_get_comm_property;
2030
2031 update_communication
2032 (
2033 p_effective_date => trunc(sysdate)
2034 ,p_communication_property_id => l_comm_property_id
2035 ,p_object_type => l_object_type
2036 ,p_object_id => l_assignmentId
2037 ,p_status => 'OPEN'
2038 ,p_start_date => l_start_date
2039 ,p_end_date => null
2040 ,p_communication_id => l_communication_id
2041 ,p_object_version_number => l_object_version_number
2042 );
2043
2044 end if;
2045
2046 else
2047 --If communication does not exist then create a communication with status
2048 --as 'OPEN' only if default-communication-status is not already 'OPEN'
2049 --
2050 --
2051 open cur_get_default_comm_status(l_assignmentId);
2052 fetch cur_get_default_comm_status into l_communication_status ;
2053 close cur_get_default_comm_status;
2054 --
2055 --
2056 if l_communication_status<>'OPEN' then
2057
2058 open cur_get_comm_property(l_assignmentId);
2059 fetch cur_get_comm_property into l_comm_property_id ;
2060 close cur_get_comm_property;
2061
2062 create_communication
2063 (
2064 p_effective_date => trunc(sysdate)
2065 ,p_communication_property_id => l_comm_property_id
2066 ,p_object_type => 'APPL'
2067 ,p_object_id => l_assignmentId
2068 ,p_status => 'OPEN'
2069 ,p_start_date => trunc(sysdate)
2070 ,p_object_version_number => l_object_version_number
2071 ,p_communication_id => l_communication_id
2072 );
2073
2074 end if;
2075
2076 end if;
2077
2078 end loop;
2079
2080 commit;
2081
2082 end;
2083 --
2084 -- ----------------------------------------------------------------------------
2085 -- |----------------------------< CLOSE_MASS_COMMUNICATION>--------------------|
2086 -- ----------------------------------------------------------------------------
2087 --
2088 -- Called from stop_mass_communication_gui
2089 --
2090 procedure close_mass_communication
2091 (
2092 p_assignmentIdListIn in varchar2
2093 ) is
2094 PRAGMA AUTONOMOUS_TRANSACTION;
2095 --
2096 --Cursor to get the communication property. This is required in case
2097 --communication status needs to be updated or a new communication
2098 --needs to be created.
2099 --
2100 cursor cur_get_comm_property(p_assignment_id number) is
2101 select communication_property_id from irc_comm_properties where
2102 object_id = (select distinct vacancy_id from per_all_assignments_f where
2103 assignment_id =p_assignment_id )
2104 and object_type ='VACANCY' ;
2105 --
2106 --
2107 cursor cur_get_default_comm_status(p_assignment_id number) is
2108 select default_comm_status from irc_comm_properties where
2109 object_id = (select distinct vacancy_id from per_all_assignments_f where
2110 assignment_id =p_assignment_id )
2111 and object_type ='VACANCY';
2112 --
2113 l_assignmentIdArray assoc_arr;
2114 l_listLength number;
2115 l_communication_id irc_communications.communication_id%type;
2116 l_communication_status irc_communications.status%type;
2117 l_assignmentId irc_communications.object_id%type;
2118 l_comm_property_id irc_comm_properties.communication_property_id%type;
2119 l_object_version_number number;
2120 l_object_type irc_communications.object_type%type;
2121 l_start_date date;
2122 begin
2123 --
2124 --Get assignmentId array
2125 --
2126 getIdArray(p_assignmentIdListIn,l_assignmentIdArray);
2127 l_listLength := l_assignmentIdArray.count;
2128 --
2129 --Loop through all assignments
2130 --
2131 for tempIndex in 1 .. l_listLength
2132 loop
2133 l_assignmentId := l_assignmentIdArray(tempIndex);
2134
2135 if(communication_exists(p_assignmentIdIn=>l_assignmentId
2136 ,p_communicationIdOut=>l_communication_id
2137 ,p_communicationStatusOut=>l_communication_status
2138 ,p_object_version_numberOut=> l_object_version_number
2139 ,p_object_typeOut=>l_object_type
2140 ,p_start_dateOut=>l_start_date))
2141 then
2142 --If communication exists and is 'OPEN', then update the status to 'CLOSED'
2143 if l_communication_status<>'CLOSED' then
2144
2145 open cur_get_comm_property(l_assignmentId);
2146 fetch cur_get_comm_property into l_comm_property_id ;
2147 close cur_get_comm_property;
2148
2149 update_communication
2150 (
2151 p_effective_date => trunc(sysdate)
2152 ,p_communication_property_id => l_comm_property_id
2153 ,p_object_type => l_object_type
2154 ,p_object_id => l_assignmentId
2155 ,p_status => 'CLOSED'
2156 ,p_start_date => l_start_date
2157 ,p_end_date => null
2158 ,p_communication_id => l_communication_id
2159 ,p_object_version_number => l_object_version_number
2160 );
2161
2162 end if;
2163
2164 else
2165 --
2166 --
2167 open cur_get_default_comm_status(l_assignmentId);
2168 fetch cur_get_default_comm_status into l_communication_status ;
2169 close cur_get_default_comm_status;
2170 --
2171 --
2172 if l_communication_status='OPEN' then
2173
2174 open cur_get_comm_property(l_assignmentId);
2175 fetch cur_get_comm_property into l_comm_property_id ;
2176 close cur_get_comm_property;
2177 --If communication does not exist then create a communication with status
2178 --as 'CLOSED' only if default-communication-status is not already 'CLOSED'
2179 create_communication
2180 (
2181 p_effective_date => trunc(sysdate)
2182 ,p_communication_property_id => l_comm_property_id
2183 ,p_object_type => 'APPL'
2184 ,p_object_id => l_assignmentId
2185 ,p_status => 'CLOSED'
2186 ,p_start_date => trunc(sysdate)
2187 ,p_object_version_number => l_object_version_number
2188 ,p_communication_id => l_communication_id
2189 );
2190
2191 end if;
2192
2193 end if;
2194
2195 end loop;
2196
2197 commit;
2198
2199 end;
2200 --
2201 --
2202 procedure handle_attachments_on_commit
2203 (
2204 p_message_list in varchar2
2205 ,p_dummy_attachment_id number
2206 ) is
2207 PRAGMA AUTONOMOUS_TRANSACTION;
2208 l_proc varchar2(72) := g_package || 'handle_attachments_on_commit';
2209 l_messageIdArray assoc_arr;
2210 l_listLength number;
2211 l_messageId number;
2212 begin
2213 hr_utility.set_location(' Entering:' || l_proc,10);
2214
2215 --
2216 --Get assignmentId array
2217 --
2218 getIdArray(p_message_list,l_messageIdArray);
2219 l_listLength := l_messageIdArray.count;
2220 --
2221 --Loop through all assignments
2222 --
2223 for tempIndex in 1 .. l_listLength
2224 loop
2225 l_messageId := l_messageIdArray(tempIndex);
2226 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name => 'commDummyMsgMap',
2227 X_from_pk1_value => p_dummy_attachment_id,
2228 X_to_entity_name => 'commMsgMap',
2229 X_to_pk1_value => l_messageId,
2230 X_created_by => fnd_global.user_id,
2231 X_last_update_login=> fnd_global.login_id
2232 );
2233 end loop;
2234
2235 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'commDummyMsgMap',X_pk1_value=>p_dummy_attachment_id,X_delete_document_flag=>'Y');
2236 hr_utility.set_location(' Exiting:' || l_proc,20);
2237 commit;
2238 end;
2239 --
2240 --
2241 procedure copy_comm_to_apl_asg
2242 (
2243 p_target_asg_id in number
2244 ,p_source_asg_id in number
2245 ) is
2246
2247 l_proc varchar2(72) := g_package || 'copy_comm_to_apl_asg';
2248 l_comm_rec irc_communications%rowtype;
2249 l_comm_recp_rec irc_comm_recipients%rowtype;
2250 l_comm_topics irc_comm_topics%rowtype;
2251 irc_comm_msgs irc_comm_messages%rowtype;
2252 l_src_asgn_id number:=p_source_asg_id;
2253 l_eff_date date:=sysdate;
2254 l_target_asgn_id number:=p_target_asg_id;
2255 l_comm_id number;
2256 l_comm_topic_id number;
2257 l_comm_msg_id number;
2258 l_comm_recp_id number;
2259 l_obj_version_number number;
2260 l_src_person_id number;
2261 l_trg_person_id number;
2262 l_dummy_person_id number;
2263 cursor csrGetCommForAsg(src_asgn_id in Number) is
2264 select * from irc_communications
2265 where object_id = src_asgn_id;
2266
2267 Cursor csrGetCommRec(comm_topic_id in number) is
2268 select * from irc_comm_recipients
2269 where communication_object_id = comm_topic_id;
2270
2271 cursor csrGetCommTopics(comm_id in number) is
2272 select * from irc_comm_topics
2273 where communication_id = comm_id;
2274
2275 cursor csrGetCommMessage(topic_id in number) is
2276 select * from irc_comm_messages
2277 where communication_topic_id = topic_id;
2278
2279 begin
2280 --
2281 hr_utility.set_location(' Entering:' || l_proc,10);
2282 --
2283 select person_id into l_src_person_id from per_all_assignments_f paf
2284 where assignment_id = l_src_asgn_id and l_eff_date between paf.effective_start_date and paf.effective_end_date;
2285 --
2286 select person_id into l_trg_person_id from per_all_assignments_f paf
2287 where assignment_id = l_target_asgn_id and l_eff_date between paf.effective_start_date and paf.effective_end_date;
2288 --
2289 open csrGetCommForAsg(l_src_asgn_id);
2290 fetch csrGetCommForAsg into l_comm_rec;
2291 --
2292 if(csrGetCommForAsg%notfound)
2293 then
2294 return;
2295 end if;
2296 --
2297 close csrGetCommForAsg;
2298 --
2299 hr_utility.set_location(' Create Communication: ' || l_proc,20);
2300 --
2301 irc_communications_api.create_communication
2302 (p_effective_date => l_eff_date
2303 ,p_communication_property_id => l_comm_rec.communication_property_id
2304 ,p_object_type => l_comm_rec.object_type
2305 ,p_object_id => l_target_asgn_id
2306 ,p_status => l_comm_rec.status
2307 ,p_start_date => l_comm_rec.start_date
2308 ,p_object_version_number => l_obj_version_number
2309 ,p_communication_id => l_comm_id
2310 );
2311 --
2312 FOR commTopicRec IN csrGetCommTopics(l_comm_rec.communication_id)
2313 LOOP
2314 --
2315 hr_utility.set_location(' Create topic : ' || l_proc,30);
2316 --
2317 irc_communications_api.create_comm_topic
2318 (p_effective_date => l_eff_date
2319 ,p_communication_id => l_comm_id
2320 ,p_subject => commTopicRec.subject
2321 ,p_status => commTopicRec.status
2322 ,p_communication_topic_id => l_comm_topic_id
2323 ,p_object_version_number => l_obj_version_number
2324 );
2325 --
2326 For commMessageRec in csrGetCommMessage(commTopicRec.communication_topic_id)
2327 Loop
2328 --
2329 hr_utility.set_location(' Create message : ' || l_proc,40);
2330 --
2331 irc_communications_api.create_message
2332 (p_effective_date => l_eff_date
2333 ,p_communication_topic_id => l_comm_topic_id
2334 ,p_parent_id => commMessageRec.parent_id
2335 ,p_message_subject => commMessageRec.message_subject
2336 ,p_message_post_date => commMessageRec.message_post_date
2337 ,p_sender_type => commMessageRec.sender_type
2338 ,p_sender_id => commMessageRec.sender_id
2339 ,p_message_body => commMessageRec.message_body
2340 ,p_document_type => commMessageRec.document_type
2341 ,p_document_id => commMessageRec.document_id
2342 ,p_deleted_flag => commMessageRec.deleted_flag
2343 ,p_communication_message_id => l_comm_msg_id
2344 ,p_object_version_number => l_obj_version_number
2345 );
2346 --
2347 END LOOP;
2348 For commRecpRec in csrGetCommRec(commTopicRec.communication_topic_id)
2349 LOOP
2350 --
2351 l_dummy_person_id := commRecpRec.recipient_id;
2352 --
2353 if(commRecpRec.recipient_id = l_src_person_id)
2354 then
2355 l_dummy_person_id := l_trg_person_id;
2356 end if;
2357 --
2358 hr_utility.set_location(' Create recipient : ' || l_proc,30);
2359 --
2360 irc_communications_api.add_recipient
2361 (p_effective_date => l_eff_date
2362 ,p_communication_object_type => commRecpRec.communication_object_type
2363 ,p_communication_object_id => l_comm_topic_id
2364 ,p_recipient_type => commRecpRec.recipient_type
2365 ,p_recipient_id => l_dummy_person_id
2366 ,p_start_date_active => commRecpRec.start_date_active
2367 ,p_end_date_active => commRecpRec.end_date_active
2368 ,p_primary_flag => commRecpRec.primary_flag
2369 ,p_communication_recipient_id => l_comm_recp_id
2370 ,p_object_version_number => l_obj_version_number
2371 );
2372 --
2373 END LOOP;
2374 --
2375 END LOOP;
2376 --
2377 exception
2378 when others then
2379 hr_utility.set_location(' Error : '||sqlErrm || l_proc,30);
2380 end copy_comm_to_apl_asg;
2381 end IRC_COMMUNICATIONS_API;