[Home] [Help]
PACKAGE BODY: APPS.HR_BPL_ALERT_RECIPIENT
Source
1 PACKAGE BODY HR_BPL_ALERT_RECIPIENT AS
2 /* $Header: perbarpt.pkb 115.13 2003/07/07 19:12:35 ssherloc noship $ */
3 --
4 -- -----------------------------------------------------------------------------
5 --
6 -- Validate an email address is in the correct format RETURNING BOOLEAN
7 --
8 FUNCTION validate_email_address(p_email_address VARCHAR2)
9 RETURN BOOLEAN
10 IS
11 --
12 c_count NUMBER(15);
13 c_char VARCHAR2(4);
14 l_length NUMBER(15);
15 --
16 -- Counts
17 --
18 at_count NUMBER(3) DEFAULT 0;
19 fst_at_pos NUMBER(3) DEFAULT 0;
20 dot_before_at_count NUMBER(3) DEFAULT 0;
21 dot_after_at_count NUMBER(3) DEFAULT 0;
22 in_token BOOLEAN;
23 --
24 BEGIN
25 --
26 IF p_email_address IS NULL
27 THEN
28 --
29 RETURN FALSE;
30 --
31 END IF;
32 --
33 l_length := length(p_email_address) + 1;
34 c_count := 0;
35 c_char := '';
36 in_token := FALSE;
37 --
38 WHILE c_count < l_length LOOP
39 --
40 c_char := substr(p_email_address,c_count,1);
41 --
42 IF NVL(c_char,'&') = ' '
43 THEN
44 --
45 RETURN FALSE;
46 --
47 ELSIF NVL(c_char,'&') = '@'
48 THEN
49 --
50 IF NOT in_token
51 THEN
52 --
53 RETURN FALSE;
54 --
55 END IF;
56 --
57 IF fst_at_pos = 0
58 THEN
59 --
60 fst_at_pos := c_count;
61 --
62 END IF;
63 --
64 at_count := at_count + 1;
65 --
66 in_token := FALSE;
67 --
68 ELSIF NVL(c_char,'&') = '.'
69 THEN
70 --
71 IF NOT in_token
72 THEN
73 --
74 RETURN FALSE;
75 --
76 END IF;
77 --
78 IF at_count > 0
79 THEN
80 --
81 dot_after_at_count := dot_before_at_count + 1;
82 --
83 END IF;
84 --
85 in_token := FALSE;
86 --
87 ELSE
88 --
89 in_token := TRUE;
90 --
91 END IF;
92 --
93 c_count := c_count + 1;
94 --
95 END LOOP;
96 --
97 IF dot_after_at_count = 0
98 THEN
99 --
100 RETURN FALSE;
101 --
102 ELSIF fst_at_pos = 0
103 THEN
104 --
105 RETURN FALSE;
106 --
107 ELSIF NOT in_token
108 THEN
109 --
110 RETURN FALSE;
111 --
112 ELSE
113 --
114 RETURN TRUE;
115 --
116 END IF;
117 --
118 END validate_email_address;
119 --
120 -- -----------------------------------------------------------------------------
121 --
122 -- Validate an email address is in the correct format RETURNING CHAR
123 --
124 FUNCTION c_validate_email_address(p_email_address VARCHAR2)
125 RETURN VARCHAR2
126 IS
127 --
128 BEGIN
129 --
130 IF validate_email_address(p_email_address)
131 THEN
132 --
133 RETURN 'TRUE';
134 --
135 ELSE
136 --
137 RETURN 'FALSE';
138 --
139 END IF;
140 --
141 END c_validate_email_address;
142 --
143 -- -----------------------------------------------------------------------------
144 --
145 -- Get details for the person who owns a given assignment
146 --
147 PROCEDURE cache_pasg_sup_details(p_assignment_id IN NUMBER)
148 IS
149 --
150 CURSOR c_pasg_sup_details
151 ( cp_assignment_id NUMBER )
152 IS
153 SELECT psn.email_address
154 ,DECODE(psn.email_address,NULL,-1,p_assignment_id)
155 ,DECODE(psn.person_id,NULL,-1,p_assignment_id)
156 ,psn.person_id
157 ,psn.full_name
158 ,psn.correspondence_language
159 ,psn.business_group_id
160 FROM per_all_people_f psn
161 ,per_all_assignments_f asg
162 ,per_all_assignments_f asg2
163 ,per_assignment_status_types ast
164 ,per_assignment_status_types ast2
165 WHERE asg.assignment_id = cp_assignment_id
166 AND asg.assignment_status_type_id = ast.assignment_status_type_id
167 AND asg.person_id = asg2.person_id
168 AND asg2.primary_flag = 'Y'
169 AND asg2.supervisor_id = psn.person_id
170 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
171 /* Ensures only current primary assignment is used */
172 AND ((asg.effective_start_date
173 BETWEEN asg2.effective_start_date
174 AND asg2.effective_end_date ) OR
175 (asg2.effective_start_date
176 BETWEEN asg.effective_start_date
177 AND asg.effective_end_date))
178 AND ((psn.effective_start_date
179 BETWEEN asg2.effective_start_date
180 AND asg2.effective_end_date ) OR
181 (asg2.effective_start_date
182 BETWEEN psn.effective_start_date
183 AND psn.effective_end_date))
184 /* Make sure that the Recipient is a current Worker */
185 AND ((psn.current_employee_flag = 'Y') OR
186 (psn.current_npw_flag = 'Y'))
187 AND (
188 (TRUNC(SYSDATE) BETWEEN asg.effective_start_date
189 AND asg.effective_end_date
190 AND
191 TRUNC(SYSDATE) BETWEEN psn.effective_start_date
192 AND psn.effective_end_date)
193 OR
194 (
195 (NOT EXISTS (SELECT 'X'
196 FROM per_all_assignments_f asg2
197 WHERE asg2.assignment_id = asg.assignment_id
198 AND ((asg2.assignment_type = 'E') OR
199 (asg2.assignment_type = 'C'))
200 AND TRUNC(SYSDATE) BETWEEN asg2.effective_start_date
201 AND asg2.effective_end_date))
202 AND
203 (asg.effective_start_date IN
204 (
205 SELECT MIN(asg3.effective_start_date)
206 FROM per_all_assignments_f asg3
207 WHERE asg3.assignment_id = asg.assignment_id
208 AND ((asg3.assignment_type = 'E') OR
209 (asg3.assignment_type = 'C'))
210 AND asg3.effective_start_date > TRUNC(SYSDATE)
211 )
212 )
213 )
214 )
215 /* Return active assignment status types only */
216 AND ast.per_system_status IN ('ACCEPTED','ACTIVE_APL'
217 ,'ACTIVE_ASSIGN','ACTIVE_CWK','END'
218 ,'INTERVIEW1','INTERVIEW2'
219 ,'OFFER','SUSP_ASSIGN','SUSP_CWK_ASG')
220 AND ast2.per_system_status IN ('ACCEPTED','ACTIVE_APL'
221 ,'ACTIVE_ASSIGN','ACTIVE_CWK','END'
222 ,'INTERVIEW1','INTERVIEW2'
223 ,'OFFER','SUSP_ASSIGN','SUSP_CWK_ASG')
224
225 ;
226 --
227 l_count NUMBER(7);
228 --
229 BEGIN
230 --
231 -- If we don't have an id then we can't get an address
232 --
233 IF p_assignment_id IS NULL THEN
234 --
235 g_pasg_sup_person_email := NULL;
236 g_pasg_sup_person_email_set := NULL;
237 g_pasg_sup_person_available := NULL;
238 g_pasg_sup_sup_person_id := NULL;
239 g_pasg_sup_person_name := NULL;
240 g_pasg_sup_person_lang := NULL;
241 g_pasg_sup_business_group_id := NULL;
242 --
243 RETURN;
244 --
245 END IF;
246 --
247 -- If we already have the email address use it.
248 --
249 IF p_assignment_id = g_pasg_sup_assignment_id THEN
250 --
251 RETURN;
252 --
253 END IF;
254 --
255 g_pasg_sup_assignment_id := p_assignment_id;
256 --
257 OPEN c_pasg_sup_details(
258 p_assignment_id );
259 --
260 FETCH c_pasg_sup_details
261 INTO g_pasg_sup_person_email
262 ,g_pasg_sup_person_email_set
263 ,g_pasg_sup_person_available
264 ,g_pasg_sup_sup_person_id
265 ,g_pasg_sup_person_name
266 ,g_pasg_sup_person_lang
267 ,g_pasg_sup_business_group_id;
268 --
269 l_count := c_pasg_sup_details%rowcount;
270 --
271 CLOSE c_pasg_sup_details;
272 --
273 IF l_count = 0
274 THEN
275 --
276 g_pasg_sup_person_email := NULL;
277 g_pasg_sup_person_email_set := NULL;
278 g_pasg_sup_person_available := NULL;
279 g_pasg_sup_sup_person_id := NULL;
280 g_pasg_sup_person_name := NULL;
281 g_pasg_sup_person_lang := NULL;
282 g_pasg_sup_business_group_id := NULL;
283 --
284 ELSIF NOT validate_email_address(g_pasg_sup_person_email)
285 THEN
286 --
287 g_pasg_sup_person_email_set := NULL;
288 --
289 END IF;
290 --
291 EXCEPTION
292 WHEN OTHERS THEN
293 --
294 CLOSE c_pasg_sup_details;
295 --
296 g_pasg_sup_person_email := NULL;
297 g_pasg_sup_person_email_set := NULL;
298 g_pasg_sup_person_available := NULL;
299 g_pasg_sup_sup_person_id := NULL;
300 g_pasg_sup_person_name := NULL;
301 g_pasg_sup_person_lang := NULL;
302 g_pasg_sup_business_group_id := NULL;
303 --
304 --
305 END cache_pasg_sup_details;
306 --
307 -- -----------------------------------------------------------------------------
308 --
309 -- Get details for the person who supervises a given assignment
310 --
311 PROCEDURE cache_asg_sup_details(p_assignment_id IN NUMBER)
312 IS
313 --
314 CURSOR c_asg_sup_details
315 ( cp_assignment_id NUMBER )
316 IS
317 SELECT psn.email_address
318 ,DECODE(psn.email_address,NULL,-1,p_assignment_id)
319 ,DECODE(psn.person_id,NULL,-1,p_assignment_id)
320 ,psn.person_id
321 ,psn.full_name
322 ,psn.correspondence_language
323 ,psn.business_group_id
324 FROM per_all_people_f psn
325 ,per_all_assignments_f asg
326 ,per_assignment_status_types ast
327 WHERE asg.assignment_id = cp_assignment_id
328 AND asg.supervisor_id = psn.person_id
329 AND asg.assignment_status_type_id = ast.assignment_status_type_id
330 /* Ensures only current person and assignment used */
331 AND ((psn.effective_start_date
332 BETWEEN asg.effective_start_date
333 AND asg.effective_end_date ) OR
334 (asg.effective_start_date
335 BETWEEN psn.effective_start_date
336 AND psn.effective_end_date))
337 /* Make sure that the Recipient is a current Worker */
338 AND ((psn.current_employee_flag = 'Y') OR
339 (psn.current_npw_flag = 'Y'))
340 AND (
341 (TRUNC(SYSDATE) BETWEEN asg.effective_start_date
342 AND asg.effective_end_date
343 AND
344 TRUNC(SYSDATE) BETWEEN psn.effective_start_date
345 AND psn.effective_end_date)
346 OR
347 (
348 (NOT EXISTS (SELECT 'X'
349 FROM per_all_assignments_f asg2
350 WHERE asg2.assignment_id = asg.assignment_id
351 AND ((asg2.assignment_type = 'E') OR
352 (asg2.assignment_type = 'C'))
353 AND TRUNC(SYSDATE) BETWEEN asg2.effective_start_date
354 AND asg2.effective_end_date))
355 AND
356 (asg.effective_start_date IN
357 (
358 SELECT MIN(asg3.effective_start_date)
359 FROM per_all_assignments_f asg3
360 WHERE asg3.assignment_id = asg.assignment_id
361 AND ((asg3.assignment_type = 'E') OR
362 (asg3.assignment_type = 'C'))
363 AND asg3.effective_start_date > TRUNC(SYSDATE)
364 )
365 )
366 )
367 )
368 AND ast.per_system_status IN ('ACCEPTED','ACTIVE_APL'
369 ,'ACTIVE_ASSIGN','ACTIVE_CWK','END'
370 ,'INTERVIEW1','INTERVIEW2'
371 ,'OFFER','SUSP_ASSIGN','SUSP_CWK_ASG') ;
372 --
373 l_count NUMBER(7);
374 --
375 BEGIN
376 --
377 -- If we don't have an id then we can't get an address
378 --
379 IF p_assignment_id IS NULL THEN
380 --
381 g_asg_sup_person_email := NULL;
382 g_asg_sup_person_email_set := NULL;
383 g_asg_sup_person_available := NULL;
384 g_asg_sup_sup_person_id := NULL;
385 g_asg_sup_person_name := NULL;
386 g_asg_sup_person_lang := NULL;
387 g_asg_sup_business_group_id := NULL;
388 --
389 RETURN;
390 --
391 END IF;
392 --
393 -- If we already have the email address use it.
394 --
395 IF p_assignment_id = g_asg_sup_assignment_id THEN
396 --
397 RETURN;
398 --
399 END IF;
400 --
401 g_asg_sup_assignment_id := p_assignment_id;
402 --
403 OPEN c_asg_sup_details(
404 p_assignment_id );
405 --
406 FETCH c_asg_sup_details
407 INTO g_asg_sup_person_email
408 ,g_asg_sup_person_email_set
409 ,g_asg_sup_person_available
410 ,g_asg_sup_sup_person_id
411 ,g_asg_sup_person_name
412 ,g_asg_sup_person_lang
413 ,g_asg_sup_business_group_id;
414 --
415 l_count := c_asg_sup_details%rowcount;
416 --
417 CLOSE c_asg_sup_details;
418 --
419 IF l_count = 0
420 THEN
421 --
422 g_asg_sup_person_email := NULL;
423 g_asg_sup_person_email_set := NULL;
424 g_asg_sup_person_available := NULL;
425 g_asg_sup_sup_person_id := NULL;
426 g_asg_sup_person_name := NULL;
427 g_asg_sup_person_lang := NULL;
428 g_asg_sup_business_group_id := NULL;
429 --
430 ELSIF NOT validate_email_address(g_asg_sup_person_email)
431 THEN
435 END IF;
432 --
433 g_asg_sup_person_email_set := NULL;
434 --
436 --
437 EXCEPTION
438 WHEN OTHERS THEN
439 --
440 CLOSE c_asg_sup_details;
441 --
442 g_asg_sup_person_email := NULL;
443 g_asg_sup_person_email_set := NULL;
444 g_asg_sup_person_available := NULL;
445 g_asg_sup_sup_person_id := NULL;
446 g_asg_sup_person_name := NULL;
447 g_asg_sup_person_lang := NULL;
448 g_asg_sup_business_group_id := NULL;
449 --
450 --
451 END cache_asg_sup_details;
452 --
453 -- -----------------------------------------------------------------------------
454 --
455 -- Get details for the person who owns a given assignment
456 --
457 PROCEDURE cache_asg_psn_details(p_assignment_id IN NUMBER)
458 IS
459 --
460 CURSOR c_asg_psn_details
461 ( cp_assignment_id NUMBER)
462 IS
463 SELECT psn.email_address
464 ,DECODE(psn.email_address,NULL,-1,p_assignment_id)
465 ,psn.person_id
466 ,psn.full_name
467 ,psn.correspondence_language
468 ,psn.business_group_id
469 FROM per_all_people_f psn
470 ,per_all_assignments_f asg
471 ,per_assignment_status_types ast
472 WHERE asg.assignment_id = cp_assignment_id
473 AND asg.assignment_status_type_id = ast.assignment_status_type_id
474 AND asg.person_id = psn.person_id
475 /* Ensures only current person and assignment used */
476 AND ((psn.effective_start_date
477 BETWEEN asg.effective_start_date
478 AND asg.effective_end_date ) OR
479 (asg.effective_start_date
480 BETWEEN psn.effective_start_date
481 AND psn.effective_end_date))
482 /* Make sure that the Recipient is a current Worker */
483 AND ((psn.current_employee_flag = 'Y') OR
484 (psn.current_npw_flag = 'Y'))
485 AND (
486 (TRUNC(SYSDATE) BETWEEN asg.effective_start_date
487 AND asg.effective_end_date
488 AND
489 TRUNC(SYSDATE) BETWEEN psn.effective_start_date
490 AND psn.effective_end_date)
491 OR
492 (
493 (NOT EXISTS (SELECT 'X'
494 FROM per_all_assignments_f asg2
495 WHERE asg2.assignment_id = asg.assignment_id
496 AND ((asg2.assignment_type = 'E') OR
497 (asg2.assignment_type = 'C'))
498 AND TRUNC(SYSDATE) BETWEEN asg2.effective_start_date
499 AND asg2.effective_end_date))
500 AND
501 (asg.effective_start_date IN
502 (
503 SELECT MIN(asg3.effective_start_date)
504 FROM per_all_assignments_f asg3
505 WHERE asg3.assignment_id = asg.assignment_id
506 AND ((asg3.assignment_type = 'E') OR
507 (asg3.assignment_type = 'C'))
508 AND asg3.effective_start_date > TRUNC(SYSDATE)
509 )
510 )
511 )
512 )
513 AND ast.per_system_status IN ('ACCEPTED','ACTIVE_APL'
514 ,'ACTIVE_ASSIGN','ACTIVE_CWK','END'
515 ,'INTERVIEW1','INTERVIEW2'
516 ,'OFFER','SUSP_ASSIGN','SUSP_CWK_ASG') ;
517 --
518 l_count NUMBER(7);
519 --
520 BEGIN
521 --
522 -- If we don't have an id then we can't get an address
523 --
524 IF p_assignment_id IS NULL THEN
525 --
526 g_asg_person_email := NULL;
527 g_asg_person_email_set := NULL;
528 g_asg_sup_person_id := NULL;
529 g_asg_person_name := NULL;
530 g_asg_person_lang := NULL;
531 g_asg_business_group_id := NULL;
532 --
533 RETURN;
534 --
535 END IF;
536 --
537 -- If we already have the email address use it.
538 --
539 IF p_assignment_id = g_assignment_id THEN
540 --
541 RETURN;
542 --
543 END IF;
544 --
545 g_assignment_id := p_assignment_id;
546 --
547 OPEN c_asg_psn_details(
548 p_assignment_id );
549 --
550 FETCH c_asg_psn_details
551 INTO g_asg_person_email
552 ,g_asg_person_email_set
553 ,g_asg_sup_person_id
554 ,g_asg_person_name
555 ,g_asg_person_lang
556 ,g_asg_business_group_id;
557 --
558 CLOSE c_asg_psn_details;
559 --
560 IF l_count = 0
561 THEN
562 --
563 g_asg_person_email := NULL;
564 g_asg_person_email_set := NULL;
565 g_asg_sup_person_id := NULL;
566 g_asg_person_name := NULL;
567 g_asg_person_lang := NULL;
568 g_asg_business_group_id := NULL;
569 --
570 ELSIF NOT validate_email_address(g_asg_person_email)
571 THEN
572 --
573 g_asg_person_email_set := NULL;
574 --
575 END IF;
576 --
577 EXCEPTION
578 WHEN OTHERS THEN
579 --
583 g_asg_person_email_set := NULL;
580 CLOSE c_asg_psn_details;
581 --
582 g_asg_person_email := NULL;
584 g_asg_sup_person_id := NULL;
585 g_asg_person_name := NULL;
586 g_asg_person_lang := NULL;
587 g_asg_business_group_id := NULL;
588 --
589 --
590 END cache_asg_psn_details;
591 --
592 -- -----------------------------------------------------------------------------
593 --
594 -- Get's an email address for a given person_id
595 --
596 PROCEDURE Cache_psn_details(p_person_id IN NUMBER)
597 IS
601 IS
598 --
599 CURSOR c_psn_details
600 ( cp_person_id NUMBER )
602 SELECT email_address
603 ,DECODE(email_address,NULL,-1,p_person_id)
604 ,full_name
605 ,correspondence_language
606 ,business_group_id
607 FROM per_all_people_f psn
608 WHERE person_id = cp_person_id
609 AND ((psn.current_employee_flag = 'Y') OR
610 (psn.current_npw_flag = 'Y'))
611 AND (
612 (TRUNC(SYSDATE) BETWEEN psn.effective_start_date
613 AND psn.effective_end_date)
614 OR
615 (
616 (NOT EXISTS (SELECT 'X'
617 FROM per_all_people_f psn2
618 WHERE psn2.person_id = psn.person_id
619 AND ((psn2.current_employee_flag = 'Y') OR
620 (psn2.current_npw_flag = 'Y'))
621 AND TRUNC(SYSDATE) BETWEEN psn2.effective_start_date
622 AND psn2.effective_end_date))
623 AND
624 (psn.effective_start_date IN
625 (
626 SELECT MIN(psn3.effective_start_date)
627 FROM per_all_people_f psn3
628 WHERE psn3.person_id = psn.person_id
629 AND ((psn3.current_employee_flag = 'Y') OR
630 (psn3.current_npw_flag = 'Y'))
631 AND psn3.effective_start_date > TRUNC(SYSDATE)
632 )
633 )
634 )
635 );
636 --
637 l_count NUMBER(7);
638 --
639 BEGIN
640 --
641 -- If we don't have an id then we can't get an address
642 --
643 --
644 IF p_person_id IS NULL THEN
645 --
646 g_person_email := NULL;
647 g_person_email_set := NULL;
648 g_person_name := NULL;
649 g_person_lang := NULL;
650 g_person_bg_id := NULL;
651 --
652 RETURN;
653 --
654 END IF;
655 --
656 -- If we already have the email address use it.
657 --
658 IF p_person_id = g_person_id THEN
659 --
660 RETURN;
664 g_person_id := p_person_id;
661 --
662 END IF;
663 --
665 --
666 OPEN c_psn_details(
667 p_person_id );
668 --
669 FETCH c_psn_details
670 INTO g_person_email
671 ,g_person_email_set
672 ,g_person_name
673 ,g_person_lang
674 ,g_person_bg_id;
675 --
676 l_count := c_psn_details%rowcount;
677 --
678 CLOSE c_psn_details;
679 --
680 IF l_count = 0
681 THEN
682 --
683 g_person_email := NULL;
684 g_person_email_set := NULL;
685 g_person_name := NULL;
686 g_person_lang := NULL;
687 g_person_bg_id := NULL;
688 --
689 ELSIF NOT validate_email_address(g_person_email)
690 THEN
691 --
692 g_person_email_set := NULL;
693 --
694 END IF;
695 --
696 RETURN;
697 --
698 EXCEPTION
699 WHEN OTHERS THEN
700 --
701 CLOSE c_psn_details;
702 --
703 g_person_email := NULL;
704 g_person_email_set := NULL;
705 g_person_name := NULL;
706 g_person_lang := NULL;
707 g_person_bg_id := NULL;
708 --
709 RETURN;
710 --
711 --
712 END Cache_psn_details;
713
714 --
715 -- -----------------------------------------------------------------------------
716 --
717 -- Get's an email address for a given person_id
718 --
719 PROCEDURE cache_all_psn_details(p_person_id IN NUMBER)
720 IS
721 --
722 CURSOR c_all_psn_details
723 ( cp_person_id NUMBER )
724 IS
725 SELECT email_address
726 ,DECODE(email_address,NULL,-1,p_person_id)
727 ,full_name
728 ,correspondence_language
729 ,business_group_id
730 FROM per_all_people_f psn
731 WHERE person_id = cp_person_id
732 --AND ((psn.current_employee_flag = 'Y') OR
733 -- (psn.current_npw_flag = 'Y'))
734 AND (
735 (TRUNC(SYSDATE) BETWEEN psn.effective_start_date
736 AND psn.effective_end_date)
737 OR
741 WHERE psn2.person_id = psn.person_id
738 (
739 (NOT EXISTS (SELECT 'X'
740 FROM per_all_people_f psn2
742 AND ((psn2.current_employee_flag = 'Y') OR
743 (psn2.current_npw_flag = 'Y'))
744 AND TRUNC(SYSDATE) BETWEEN psn2.effective_start_date
745 AND psn2.effective_end_date))
746 AND
747 (psn.effective_start_date IN
748 (
749 SELECT MIN(psn3.effective_start_date)
750 FROM per_all_people_f psn3
751 WHERE psn3.person_id = psn.person_id
752 AND ((psn3.current_employee_flag = 'Y') OR
753 (psn3.current_npw_flag = 'Y'))
754 AND psn3.effective_start_date > TRUNC(SYSDATE)
755 )
756 )
757 )
758 );
759 --
760 l_count NUMBER(7);
761 --
762 BEGIN
763 --
764 -- If we don't have an id then we can't get an address
765 --
766 --
767 IF p_person_id IS NULL THEN
768 --
769 g_all_person_email := NULL;
770 g_all_person_email_set := NULL;
771 g_all_person_name := NULL;
772 g_all_person_lang := NULL;
773 g_all_person_bg_id := NULL;
774 --
775 RETURN;
776 --
777 END IF;
778 --
779 -- If we already have the email address use it.
780 --
781 IF p_person_id = g_all_person_id THEN
782 --
783 RETURN;
784 --
785 END IF;
786 --
787 g_all_person_id := p_person_id;
788 --
789 OPEN c_all_psn_details(
790 p_person_id );
791 --
792 FETCH c_all_psn_details
793 INTO g_all_person_email
794 ,g_all_person_email_set
795 ,g_all_person_name
796 ,g_all_person_lang
797 ,g_all_person_bg_id;
798 --
799 l_count := c_all_psn_details%rowcount;
800 --
801 CLOSE c_all_psn_details;
802 --
803 IF l_count = 0
804 THEN
805 --
806 g_all_person_email := NULL;
807 g_all_person_email_set := NULL;
808 g_all_person_name := NULL;
809 g_all_person_lang := NULL;
810 g_all_person_bg_id := NULL;
811 --
812 ELSIF NOT validate_email_address(g_all_person_email)
813 THEN
814 --
815 g_all_person_email_set := NULL;
816 --
817 END IF;
818 --
819 RETURN;
820 --
821 EXCEPTION
822 WHEN OTHERS THEN
823 --
824 CLOSE c_all_psn_details;
825 --
826 g_all_person_email := NULL;
827 g_all_person_email_set := NULL;
828 g_all_person_name := NULL;
832 RETURN;
829 g_all_person_lang := NULL;
830 g_all_person_bg_id := NULL;
831 --
833 --
834 --
835 END cache_all_psn_details;
836 --
837 -- -----------------------------------------------------------------------------
838 --
839 -- Get's a person's name for a given person_id
840 --
841 FUNCTION Get_psn_prsn_nm(p_person_id IN NUMBER)
842 RETURN VARCHAR2 IS
843 --
844 BEGIN
845 --
846 Cache_psn_details(p_person_id);
847 --
848 RETURN g_person_name;
849 --
850 END Get_psn_prsn_nm;
851 --
852 -- -----------------------------------------------------------------------------
853 --
854 -- Get's a person's name for a given person_id
855 --
856 FUNCTION Get_all_psn_prsn_nm(p_person_id IN NUMBER)
857 RETURN VARCHAR2 IS
858 --
859 BEGIN
860 --
861 Cache_all_psn_details(p_person_id);
862 --
863 RETURN g_all_person_name;
864 --
865 END Get_all_psn_prsn_nm;
866 --
867 -- -----------------------------------------------------------------------------
868 --
869 -- Get's an email address for a given person_id
870 --
871 FUNCTION Get_psn_eml_addrss(p_person_id IN NUMBER)
872 RETURN VARCHAR2 IS
873 --
874 BEGIN
875 --
876 Cache_psn_details(p_person_id);
877 --
881 --
878 RETURN g_person_email;
879 --
880 END Get_psn_eml_addrss;
882 -- -----------------------------------------------------------------------------
883 --
884 -- Get details from the per_all_people_f for the primary
885 -- assignment supervisor for a given person_id
886 --
887 PROCEDURE cache_psn_sup_psn_details(p_person_id IN NUMBER)
888 IS
889 --
890 CURSOR c_psn_sup_psn_details
891 ( cp_person_id NUMBER )
892 IS
893 SELECT psn.email_address
894 ,DECODE(psn.email_address,NULL,-1,p_person_id)
895 ,psn.person_id
896 ,psn.full_name
897 ,psn.correspondence_language
898 ,psn.business_group_id
899 FROM per_all_people_f psn
900 ,per_all_assignments_f asg
901 WHERE asg.person_id = cp_person_id
902 AND asg.primary_flag = 'Y'
903 AND asg.supervisor_id = psn.person_id
904 /* Ensures only current person and assignment used */
905 AND ((psn.effective_start_date
906 BETWEEN asg.effective_start_date
907 AND asg.effective_end_date ) OR
908 (asg.effective_start_date
909 BETWEEN psn.effective_start_date
910 AND psn.effective_end_date))
911 /* Make sure that the Recipient is a current Worker */
912 AND ((psn.current_employee_flag = 'Y') OR
913 (psn.current_npw_flag = 'Y'))
914 AND (
915 (TRUNC(SYSDATE) BETWEEN asg.effective_start_date
916 AND asg.effective_end_date
917 AND
918 TRUNC(SYSDATE) BETWEEN psn.effective_start_date
919 AND psn.effective_end_date)
920 OR
921 (
922 (NOT EXISTS (SELECT 'X'
923 FROM per_all_assignments_f asg2
924 WHERE asg2.assignment_id = asg.assignment_id
925 AND ((asg2.assignment_type = 'E') OR
926 (asg2.assignment_type = 'C'))
927 AND TRUNC(SYSDATE) BETWEEN asg2.effective_start_date
928 AND asg2.effective_end_date))
932 SELECT MIN(asg3.effective_start_date)
929 AND
930 (asg.effective_start_date IN
931 (
933 FROM per_all_assignments_f asg3
934 WHERE asg3.assignment_id = asg.assignment_id
935 AND ((asg3.assignment_type = 'E') OR
936 (asg3.assignment_type = 'C'))
937 AND asg3.effective_start_date > TRUNC(SYSDATE)
938 )
939 )
940 )
941 );
942 --
943 l_count NUMBER(7);
944 --
945 BEGIN
946 --
947 -- If we don't have an id then we can't get an address
948 --
949 IF p_person_id IS NULL THEN
950 --
951 g_sup_person_email := NULL;
952 g_sup_person_email_set := NULL;
953 g_psn_sup_person_id := NULL;
954 g_sup_person_name := NULL;
955 g_sup_person_lang := NULL;
956 g_sup_business_group_id := NULL;
957 --
958 RETURN;
959 --
960 END IF;
961 --
962 -- If we already have the email address use it.
963 --
964 IF p_person_id = g_sup_person_id THEN
965 --
966 RETURN;
967 --
968 END IF;
969 --
970 g_sup_person_id := p_person_id;
971 --
972 OPEN c_psn_sup_psn_details(
973 p_person_id );
974 --
975 FETCH c_psn_sup_psn_details
976 INTO g_sup_person_email
977 ,g_sup_person_email_set
978 ,g_psn_sup_person_id
979 ,g_sup_person_name
980 ,g_sup_person_lang
981 ,g_sup_business_group_id;
982 --
983 l_count := c_psn_sup_psn_details%rowcount;
984 --
985 CLOSE c_psn_sup_psn_details;
986 --
987 IF l_count = 0
988 THEN
989 --
990 g_sup_person_email := NULL;
991 g_sup_person_email_set := NULL;
992 g_psn_sup_person_id := NULL;
993 g_sup_person_name := NULL;
994 g_sup_person_lang := NULL;
995 g_sup_business_group_id := NULL;
996 --
997 ELSIF NOT validate_email_address(g_sup_person_email)
998 THEN
999 --
1000 g_sup_person_email_set := NULL;
1001 --
1002 END IF;
1003 --
1004 EXCEPTION
1005 WHEN OTHERS THEN
1006 --
1007 CLOSE c_psn_sup_psn_details;
1008 --
1009 g_sup_person_email := NULL;
1010 g_sup_person_email_set := NULL;
1011 g_psn_sup_person_id := NULL;
1012 g_sup_person_name := NULL;
1013 g_sup_person_lang := NULL;
1014 g_sup_business_group_id := NULL;
1015 --
1016 --
1020 --
1017 END cache_psn_sup_psn_details;
1018 --
1019 -- -----------------------------------------------------------------------------
1021 -- Get's an email address for a given person_id
1022 --
1023 FUNCTION Get_psn_sup_psn_eml_addrss(p_person_id IN NUMBER)
1024 RETURN VARCHAR2 IS
1025 --
1026 BEGIN
1027 --
1028 cache_psn_sup_psn_details(p_person_id);
1029 --
1030 RETURN g_sup_person_email;
1031 --
1032 END Get_psn_sup_psn_eml_addrss;
1033 --
1034 -- -----------------------------------------------------------------------------
1035 --
1036 -- Get's a primary assignment supervisor name for a given person_id
1037 --
1038 FUNCTION Get_psn_sup_psn_nm(p_person_id IN NUMBER)
1039 RETURN VARCHAR2 IS
1040 --
1041 BEGIN
1042 --
1043 cache_psn_sup_psn_details(p_person_id);
1044 --
1045 RETURN g_sup_person_name;
1046 --
1047 END Get_psn_sup_psn_nm;
1048 --
1049 -- -----------------------------------------------------------------------------
1050 --
1051 -- Gets a bg's language for a given p_business_group_id
1052 --
1053 -- !!! Currently defaults language to US if no BG language available !!!
1054 -- !!! THIS MUST BE CORRECTED !!!
1055 --
1056 FUNCTION Get_bg_lng(p_business_group_id IN NUMBER)
1057 RETURN VARCHAR2 IS
1058 --
1059 CURSOR c_bg_details
1060 ( cp_business_group_id NUMBER )
1061 IS
1062 SELECT org_information9 bg_lang
1063 FROM hr_organization_information
1064 WHERE org_information_context = 'Business Group Information'
1065 AND organization_id = cp_business_group_id;
1066 --
1067 l_lang VARCHAR2(10) DEFAULT NULL;
1068 --
1069 BEGIN
1070 --
1071 OPEN c_bg_details
1072 ( p_business_group_id );
1073 --
1074 FETCH c_bg_details
1075 INTO l_lang;
1076 --
1077 CLOSE c_bg_details;
1078 --
1079 IF l_lang IS NULL THEN
1080 --
1081 l_lang := 'US';
1082 --
1083 END IF;
1084 --
1085 RETURN l_lang;
1086 --
1087 EXCEPTION
1088 WHEN OTHERS THEN
1089 --
1090 CLOSE c_bg_details;
1091 --
1092 RETURN 'US';
1093 --
1094 END Get_bg_lng;
1095
1096 --
1097 -- -----------------------------------------------------------------------------
1098 --
1099 -- Get's a language for a given person_id
1100 --
1101 FUNCTION Get_psn_lng(p_person_id IN NUMBER)
1102 RETURN VARCHAR2 IS
1103 --
1104 BEGIN
1105 --
1106 cache_psn_details(p_person_id);
1107 --
1108 -- if the person does not have a language set
1109 --
1110 IF g_person_lang IS NULL THEN
1111 --
1112 g_person_lang := Get_bg_lng(g_person_bg_id);
1113 --
1114 END IF;
1115 --
1116 RETURN g_person_lang;
1117 --
1118 END Get_psn_lng;
1119 --
1120 -- -----------------------------------------------------------------------------
1121 --
1122 -- Get's the primary assignment supervisor's language for a given person_id
1123 --
1124 FUNCTION Get_psn_sup_psn_lng(p_person_id IN NUMBER)
1125 RETURN VARCHAR2 IS
1126 --
1127 BEGIN
1128 --
1129 cache_psn_sup_psn_details(p_person_id);
1130 --
1131 IF g_sup_person_lang IS NULL THEN
1132 --
1133 g_sup_person_lang := Get_bg_lng(g_sup_business_group_id);
1134 --
1135 END IF;
1136 --
1137 RETURN g_sup_person_lang;
1138 --
1139 END Get_psn_sup_psn_lng;
1140 --
1141 -- -----------------------------------------------------------------------------
1142 --
1143 -- Find out if we have an email address for a person's primary
1144 -- assignment supervisor, if we have return 'Y' otherwise 'N'
1145 --
1146 FUNCTION Check_sup_person_in_scope(p_person_id IN NUMBER)
1147 RETURN NUMBER IS
1148 --
1149 BEGIN
1150 --
1151 cache_psn_sup_psn_details(p_person_id);
1152 --
1153 RETURN g_sup_person_email_set;
1154 --
1155 END Check_sup_person_in_scope;
1156 --
1157 -- -----------------------------------------------------------------------------
1158 --
1159 -- Find out if we have an email address for a person. If we have
1160 -- return 'Y' otherwise 'N'.
1161 --
1162 FUNCTION Check_person_in_scope(p_person_id IN NUMBER)
1163 RETURN NUMBER IS
1164 --
1165 BEGIN
1166 --
1167 cache_psn_details(p_person_id);
1168 --
1169 RETURN g_person_email_set;
1170 --
1171 END Check_person_in_scope;
1172 --
1173 -- -----------------------------------------------------------------------------
1174 --
1175 -- Get's an email address for a supervisor of a given assignment_id
1176 --
1177 FUNCTION Get_asg_sup_eml_addrss(p_assignment_id IN NUMBER)
1178 RETURN VARCHAR2 IS
1179 --
1180 BEGIN
1181 --
1182 cache_asg_sup_details(p_assignment_id);
1183 --
1184 RETURN g_asg_sup_person_email;
1185 --
1186 END Get_asg_sup_eml_addrss;
1187 --
1188 -- -----------------------------------------------------------------------------
1189 --
1190 -- Get's a primary assignment supervisor name for a given person_id
1191 --
1192 FUNCTION Get_asg_sup_nm(p_assignment_id IN NUMBER)
1193 RETURN VARCHAR2 IS
1194 --
1195 BEGIN
1196 --
1197 cache_asg_sup_details(p_assignment_id);
1198 --
1199 RETURN g_asg_sup_person_name;
1200 --
1201 END Get_asg_sup_nm;
1202 --
1206 --
1203 -- -----------------------------------------------------------------------------
1204 --
1205 -- Get's the primary assignment supervisor's language for a given person_id
1207 FUNCTION Get_asg_sup_lng(p_assignment_id IN NUMBER)
1208 RETURN VARCHAR2 IS
1209 --
1210 BEGIN
1211 --
1212 cache_asg_sup_details(p_assignment_id);
1213 --
1214 IF g_asg_sup_person_lang IS NULL THEN
1215 --
1216 g_asg_sup_person_lang := Get_bg_lng(g_asg_sup_business_group_id);
1217 --
1218 END IF;
1219 --
1220 RETURN g_asg_sup_person_lang;
1221 --
1222 END Get_asg_sup_lng;
1223 --
1224 -- -----------------------------------------------------------------------------
1225 --
1226 -- Find out if we have an email address for a person's primary
1227 -- assignment supervisor, if we have return 'Y' otherwise 'N'
1228 --
1229 FUNCTION Check_asg_sup_in_scope(p_assignment_id IN NUMBER)
1230 RETURN NUMBER IS
1231 --
1232 BEGIN
1233 --
1234 cache_asg_sup_details(p_assignment_id);
1235 --
1236 RETURN g_asg_sup_person_email_set;
1237 --
1238 END Check_asg_sup_in_scope;
1239 --
1240 -- -----------------------------------------------------------------------------
1241 --
1242 -- Get's an email address for a supervisor of a given assignment_id
1243 --
1244 FUNCTION Get_pasg_sup_eml_addrss(p_assignment_id IN NUMBER)
1245 RETURN VARCHAR2 IS
1246 --
1247 BEGIN
1248 --
1249 cache_pasg_sup_details(p_assignment_id);
1250 --
1251 RETURN g_pasg_sup_person_email;
1252 --
1253 END Get_pasg_sup_eml_addrss;
1254 --
1255 -- -----------------------------------------------------------------------------
1256 --
1257 -- Get's a primary assignment supervisor name for a given person_id
1258 --
1259 FUNCTION Get_pasg_sup_nm(p_assignment_id IN NUMBER)
1260 RETURN VARCHAR2 IS
1261 --
1262 BEGIN
1263 --
1264 cache_pasg_sup_details(p_assignment_id);
1265 --
1266 RETURN g_pasg_sup_person_name;
1267 --
1268 END Get_pasg_sup_nm;
1269 --
1270 -- -----------------------------------------------------------------------------
1271 --
1272 -- Get's the primary assignment supervisor's language for a given person_id
1273 --
1274 FUNCTION Get_pasg_sup_lng(p_assignment_id IN NUMBER)
1275 RETURN VARCHAR2 IS
1276 --
1277 BEGIN
1278 --
1279 cache_pasg_sup_details(p_assignment_id);
1280 --
1281 IF g_pasg_sup_person_lang IS NULL THEN
1282 --
1283 g_pasg_sup_person_lang := Get_bg_lng(g_pasg_sup_business_group_id);
1284 --
1285 END IF;
1286 --
1287 RETURN g_pasg_sup_person_lang;
1288 --
1289 END Get_pasg_sup_lng;
1290 --
1291 -- -----------------------------------------------------------------------------
1292 --
1293 -- Find out if we have an email address for a person's primary
1294 -- assignment supervisor, if we have return 'Y' otherwise 'N'
1295 --
1296 FUNCTION Check_pasg_sup_in_scope(p_assignment_id IN NUMBER)
1297 RETURN NUMBER IS
1298 --
1299 BEGIN
1300 --
1301 cache_pasg_sup_details(p_assignment_id);
1302 --
1303 RETURN g_pasg_sup_person_email_set;
1304 --
1305 END Check_pasg_sup_in_scope;
1306 --
1307 -- -----------------------------------------------------------------------------
1308 --
1309 -- Get's an email address for a supervisor of a given assignment_id
1310 --
1311 FUNCTION Get_asg_psn_eml_addrss(p_assignment_id IN NUMBER)
1312 RETURN VARCHAR2 IS
1313 --
1314 BEGIN
1315 --
1316 cache_asg_psn_details(p_assignment_id);
1317 --
1318 RETURN g_asg_person_email;
1319 --
1320 END Get_asg_psn_eml_addrss;
1321 --
1322 -- -----------------------------------------------------------------------------
1323 --
1324 -- Get's a primary assignment supervisor name for a given person_id
1325 --
1326 FUNCTION Get_asg_psn_nm(p_assignment_id IN NUMBER)
1327 RETURN VARCHAR2 IS
1328 --
1329 BEGIN
1330 --
1331 cache_asg_psn_details(p_assignment_id);
1332 --
1333 RETURN g_asg_person_name;
1334 --
1335 END Get_asg_psn_nm;
1336 --
1337 -- -----------------------------------------------------------------------------
1338 --
1339 -- Get's the primary assignment supervisor's language for a given person_id
1340 --
1341 FUNCTION Get_asg_psn_lng(p_assignment_id IN NUMBER)
1342 RETURN VARCHAR2 IS
1343 --
1344 BEGIN
1345 --
1346 cache_asg_psn_details(p_assignment_id);
1347 --
1348 IF (g_asg_person_lang IS NULL) OR
1349 (g_asg_person_lang = '')
1350 THEN
1351 --
1352 g_asg_person_lang := Get_bg_lng(g_asg_business_group_id);
1353 --
1354 END IF;
1355 --
1356 RETURN g_asg_person_lang;
1357 --
1358 END Get_asg_psn_lng;
1359 --
1360 -- -----------------------------------------------------------------------------
1361 --
1362 -- Find out if we have an email address for a person's primary
1363 -- assignment supervisor, if we have return 'Y' otherwise 'N'
1364 --
1365 FUNCTION Check_asg_psn_in_scope(p_assignment_id IN NUMBER)
1366 RETURN NUMBER IS
1367 --
1368 BEGIN
1369 --
1370 cache_asg_psn_details(p_assignment_id);
1374 END Check_asg_psn_in_scope;
1371 --
1372 RETURN g_asg_person_email_set;
1373 --
1375 --
1376 -- -----------------------------------------------------------------------------
1377 --
1378 -- Find out if the assignment has a current primary assignment supervisor
1379 --
1380 FUNCTION Check_pasg_sup_available(p_assignment_id IN NUMBER)
1381 RETURN NUMBER IS
1382 --
1383 BEGIN
1384 --
1385 cache_pasg_sup_details(p_assignment_id);
1386 --
1387 RETURN g_pasg_sup_person_available;
1388 --
1389 END Check_pasg_sup_available;
1390 --
1391 -- -----------------------------------------------------------------------------
1392 --
1393 -- Find out if the assignment has a current supervisor
1394 --
1395 FUNCTION Check_asg_sup_available(p_assignment_id IN NUMBER)
1396 RETURN NUMBER IS
1397 --
1398 BEGIN
1399 --
1400 cache_asg_sup_details(p_assignment_id);
1401 --
1402 RETURN g_asg_sup_person_available;
1403 --
1404 END Check_asg_sup_available;
1405 --
1406 END HR_BPL_ALERT_RECIPIENT;