1 PACKAGE BODY HR_BPL_ALERT_TRNSLT AS
2 /* $Header: perbatsl.pkb 115.3 2003/06/03 16:00:47 jrstewar noship $ */
3 --
4 --------------------------------------------------------------------------------
5 -- Variables and Cursors Required by the decode_lookup functions
6 --
7 --
8 TYPE lookup_value_pair_rec IS RECORD(
9 lookup_type fnd_lookup_values.lookup_type%TYPE,
10 lookup_code fnd_lookup_values.lookup_code%TYPE,
11 language fnd_lookup_values.language%TYPE,
12 meaning fnd_lookup_values.meaning%TYPE);
13
14 TYPE fnd_lookups_cache_tabtype IS table OF lookup_value_pair_rec
15 INDEX BY BINARY_INTEGER;
16 g_lookups_tab fnd_lookups_cache_tabtype;
17 -- define the security group
18 g_security_group_id fnd_lookup_values.security_group_id%TYPE;
19 -- define a global meaning
20 g_meaning fnd_lookup_values.meaning%TYPE;
21 -- define a holder for the hash number
22 g_language fnd_lookup_values.language%TYPE;
23 g_hash_number BINARY_INTEGER;
24 -- define the bis_decode_lookup cursors
25
26 --
27 CURSOR g_csr_lookup_default_sg(c_lookup_type VARCHAR2
28 ,c_lookup_code VARCHAR2
29 ,c_language VARCHAR2)
30 IS
31 SELECT flv.meaning
32 FROM fnd_lookup_values flv
33 WHERE flv.lookup_code = c_lookup_code
34 AND flv.lookup_type = c_lookup_type
35 AND flv.language = c_language
36 AND flv.view_application_id = 3
37 AND flv.security_group_id = 0;
38
39 --
40 -- -----------------------------------------------------------------------------
41 --
42 -- Get's an email address for a given person_id
43 --
44 PROCEDURE Cache_organization_details(p_language IN VARCHAR2
45 ,p_organization_id IN NUMBER)
46 IS
47 --
48 CURSOR c_organization_details
49 ( cp_organization_id NUMBER
50 , cp_language VARCHAR2 )
51 IS
52 SELECT org1.name
53 ,org1.language
54 FROM hr_all_organization_units_tl org1
55 WHERE org1.organization_id = cp_organization_id
56 AND ((org1.language = cp_language) OR
57 ((NOT EXISTS (SELECT 'x'
58 FROM hr_all_organization_units_tl org2
59 WHERE org1.organization_id = org2.organization_id
60 AND org2.language = cp_language)) AND
61 (org1.language = org1.source_lang)));
62 --
63 BEGIN
64 --
65 -- If we don't have an id then we can't get a location_code
66 --
67 IF p_organization_id IS NULL THEN
68 --
69 g_organization_name := 'unkown';
70 g_organization_language := 'unknown';
71 --
72 RETURN;
73 --
74 END IF;
75 --
76 -- If we already have the email address use it.
77 --
78 IF p_organization_id = g_organization_id THEN
79 --
80 RETURN;
81 --
82 END IF;
83 --
84 g_organization_id := p_organization_id;
85 g_organization_name := 'unknown';
86 g_organization_language := 'unknown';
87 --
88 OPEN c_organization_details(
89 p_organization_id
90 , p_language );
91 --
92 FETCH c_organization_details
93 INTO g_organization_name
94 ,g_organization_language;
95 --
96 CLOSE c_organization_details;
97 --
98 RETURN;
99 --
100 EXCEPTION
101 WHEN OTHERS THEN
102 --
103 CLOSE c_organization_details;
104 --
105 g_organization_name := 'unknown';
106 g_organization_language := 'unknown';
107 --
108 RETURN;
109 --
110 --
111 END Cache_organization_details;
112 -- -----------------------------------------------------------------------------
113 --
114 -- Get's an email address for a given person_id
115 --
116 PROCEDURE Cache_location_details(p_language IN VARCHAR2
117 ,p_location_id IN NUMBER)
118 IS
119 --
120 CURSOR c_location_details
121 ( cp_location_id NUMBER
122 , cp_language VARCHAR2 )
123 IS
124 SELECT loc1.location_code
125 ,loc1.language
126 FROM hr_locations_all_tl loc1
127 WHERE loc1.location_id = cp_location_id
128 AND ((loc1.language = cp_language) OR
129 ((NOT EXISTS (SELECT 'x'
130 FROM hr_locations_all_tl loc2
131 WHERE loc1.location_id = loc2.location_id
132 AND language = cp_language)) AND
133 (loc1.language = loc1.source_lang)));
134 --
135 BEGIN
136 --
137 -- If we don't have an id then we can't get a location_code
138 --
139 IF p_location_id IS NULL THEN
140 --
141 g_location_code := 'unknown';
142 g_location_language := 'unknown';
143 --
144 RETURN;
145 --
146 END IF;
147 --
148 -- If we already have the email address use it.
149 --
150 IF p_location_id = g_location_id THEN
151 --
152 RETURN;
153 --
154 END IF;
155 --
156 g_location_id := p_location_id;
157 g_location_code := 'unknown';
158 g_location_language := 'unknown';
159 --
160 OPEN c_location_details
161 ( p_location_id
162 , p_language );
163 --
164 FETCH c_location_details
165 INTO g_location_code
166 ,g_location_language;
167 --
168 CLOSE c_location_details;
169 --
170 RETURN;
171 --
172 EXCEPTION
173 WHEN OTHERS THEN
174 --
175 CLOSE c_location_details;
176 --
177 g_location_code := 'unknown';
178 g_location_language := 'unknown';
179 --
180 RETURN;
181 --
182 --
183 END Cache_location_details;
184 --
185 -- -----------------------------------------------------------------------------
186 --
187 -- This function caches job details
188 --
189 PROCEDURE Cache_job_details(p_job_id IN NUMBER)
190 IS
191 --
192 CURSOR c_job_details
193 ( cp_job_id NUMBER )
194 IS
195 SELECT job.job_id
196 ,job.name
197 FROM per_jobs job
198 WHERE job.job_id = cp_job_id;
199 --
200 BEGIN
201 --
202 -- If we don't have an id then we can't get a job_name
203 --
204
205 IF p_job_id IS NULL THEN
206
207 --
208 g_job_id := NULL;
209 g_job_code := NULL;
210 g_job_name := NULL;
211 --
212 RETURN;
213 --
214 END IF;
215 --
216 -- If we already have the job_name use it.
217 --
218
219 IF p_job_id = g_job_id THEN
220 --
221
222 RETURN;
223 --
224 END IF;
225
226 --
227 g_job_id := p_job_id;
228 g_job_code := ' ';
229 g_job_name := ' ';
230 --
231 OPEN c_job_details
232 ( p_job_id );
233 --
234 FETCH c_job_details
235 INTO g_job_code
236 ,g_job_name;
237 --
238 CLOSE c_job_details;
239 --
240 RETURN;
241 --
242 EXCEPTION
243 WHEN OTHERS THEN
244 --
245 CLOSE c_job_details;
246 --
247 RETURN;
248 --
249 --
250 END Cache_job_details;
251 --
252 -- -----------------------------------------------------------------------------
253 --
254 -- This function caches position details
255 --
256 PROCEDURE Cache_position_details(p_position_id IN NUMBER)
257 IS
258 --
259 CURSOR c_position_details
260 ( cp_position_id NUMBER )
261 IS
262 SELECT pos.position_id
263 ,pos.name
264 FROM per_positions pos
265 WHERE pos.position_id = cp_position_id;
266 --
267 BEGIN
268 --
269 -- If we don't have an id then we can't get a location_code
270 --
271 IF p_position_id IS NULL THEN
272 --
273 g_position_id := NULL;
274 g_position_code := NULL;
275 g_position_name := NULL;
276 --
277 RETURN;
278 --
279 END IF;
280 --
281 -- If we already have the email address use it.
282 --
283 IF p_position_id = g_position_id THEN
284 --
285 RETURN;
286 --
287 END IF;
288 --
289 g_position_id := p_position_id;
290 g_position_code := ' ';
291 g_position_name := ' ';
292 --
293 OPEN c_position_details
294 ( p_position_id );
295 --
296 FETCH c_position_details
297 INTO g_position_code
298 ,g_position_name;
299 --
300 CLOSE c_position_details;
301 --
302 RETURN;
303 --
304 EXCEPTION
305 WHEN OTHERS THEN
306 --
307 CLOSE c_position_details;
308 --
309 RETURN;
310 --
311 --
312 END Cache_position_details;
313 --
314 -- -----------------------------------------------------------------------------
315 --
316 -- This function caches grade details
317 --
318 PROCEDURE Cache_grade_details(p_grade_id IN NUMBER)
319 IS
320 --
321 CURSOR c_grade_details
322 ( cp_grade_id NUMBER )
323 IS
324 SELECT grd.grade_id
325 ,grd.name
326 FROM per_grades grd
327 WHERE grd.grade_id = cp_grade_id;
328 --
329 BEGIN
330 --
331 -- If we don't have an id then we can't get a location_code
332 --
333 IF p_grade_id IS NULL THEN
334 --
335 g_grade_id := NULL;
336 g_grade_code := NULL;
337 g_grade_name := NULL;
338 --
339 RETURN;
340 --
341 END IF;
342 --
343 -- If we already have the email address use it.
344 --
345 IF p_grade_id = g_grade_id THEN
346 --
347 RETURN;
348 --
349 END IF;
350 --
351 g_grade_id := p_grade_id;
352 g_grade_code := ' ';
353 g_grade_name := ' ';
354 --
355 OPEN c_grade_details
356 ( p_grade_id );
357 --
358 FETCH c_grade_details
359 INTO g_grade_code
360 ,g_grade_name;
361 --
362 CLOSE c_grade_details;
363 --
364 RETURN;
365 --
366 EXCEPTION
367 WHEN OTHERS THEN
368 --
369 CLOSE c_grade_details;
370 --
371 RETURN;
372 --
373 --
374 END Cache_grade_details;
375 --
376 -- -----------------------------------------------------------------------------
377 --
378 -- Get's a location_code for a language and location_id
379 --
380 FUNCTION location(p_language IN VARCHAR2
381 ,p_location_id IN NUMBER)
382 RETURN VARCHAR2 IS
383 --
384 BEGIN
385 --
386 Cache_location_details(p_language
387 ,p_location_id);
388 --
389 RETURN g_location_code;
390 --
391 END location;
392 --
393 --
394 -- -----------------------------------------------------------------------------
395 --
396 -- Get's an Organization Name for a language and organization_id
397 --
398 FUNCTION organization(p_language IN VARCHAR2
399 ,p_organization_id IN NUMBER)
400 RETURN VARCHAR2 IS
401 --
402 BEGIN
403 --
404 Cache_organization_details(p_language
405 ,p_organization_id);
406 --
407 RETURN g_location_code;
408 --
409 END organization;
410 --
411 -- -----------------------------------------------------------------------------
412 --
413 -- Get's a job_name for a job_id
414 --
415 FUNCTION job(p_job_id IN NUMBER)
416 RETURN VARCHAR2 IS
417 --
418 BEGIN
419 --
420 Cache_job_details(p_job_id);
421 --
422 RETURN g_job_name;
423 --
424 END job;
425 --
426 -- -----------------------------------------------------------------------------
427 --
428 -- Get's a position_name for a position_id
429 --
430 FUNCTION position(p_position_id IN NUMBER)
431 RETURN VARCHAR2 IS
432 --
433 BEGIN
434 --
435 Cache_position_details(p_position_id);
436 --
437 RETURN g_position_name;
438 --
439 END position;
440 --
441 -- -----------------------------------------------------------------------------
442 --
443 -- Get's a grade_name for a grade_id
444 --
445 FUNCTION grade(p_grade_id IN NUMBER)
446 RETURN VARCHAR2 IS
447 --
448 BEGIN
449 --
450 Cache_grade_details(p_grade_id);
451 --
452 RETURN g_grade_name;
453 --
454 END grade;
455 --
456 --------------------------------------------------------------------------------
457 -- Returns Lookup Meaning for a given person_ids language.
458 --
459 --
460 FUNCTION psn_lng_decode_lookup(p_lookup_type IN VARCHAR2
461 ,p_lookup_code IN VARCHAR2
462 ,p_person_id IN NUMBER)
463 RETURN fnd_lookup_values.meaning%TYPE IS
464
465 BEGIN
466 -- check to ensure the lookup type/code combo is NOT NULL
467 IF p_lookup_type IS NULL
468 OR p_lookup_code IS NULL
469 OR p_person_id IS NULL
470 THEN
471 -- exit incomplete type/code combo
472 RETURN (NULL);
473 END IF;
474 -- determine hash number for plsql table index.
475 g_hash_number :=
476 DBMS_UTILITY.get_hash_value(p_lookup_code || ':' || p_lookup_type || ':' || p_person_id
477 ,1
478 ,1048576);
479 g_language := hr_bpl_alert_recipient.get_psn_lng(p_person_id);
480 -- (2^20)
481 BEGIN
482 -- check if we have a hash number generated that is for a different
483 -- lookup_type and lookup_code combination
484 -- very rare but possible from the hashing algorithm
485 -- on the first call to this if statement for any given g_hash_number
486 -- the if condition will raise a no_data_found before the else condition
487 -- because the plsql will not contain any values at the start of the session
488 IF g_lookups_tab(g_hash_number).lookup_type = p_lookup_type
489 AND g_lookups_tab(g_hash_number).lookup_code = p_lookup_code
490 AND g_lookups_tab(g_hash_number).language = g_language
491 THEN
492 -- cache hit
493 -- return the value from the plsql table
494 RETURN (g_lookups_tab(g_hash_number).meaning);
495 ELSE
496 -- cache miss
497 RAISE NO_DATA_FOUND;
498 END IF;
499
500 EXCEPTION
501 WHEN NO_DATA_FOUND
502 THEN
503 -- cache miss, determine value, and place in cache for next retrieval
504 -- use the default security group
505 OPEN g_csr_lookup_default_sg(p_lookup_type, p_lookup_code,g_language);
506 FETCH g_csr_lookup_default_sg INTO g_meaning;
507 IF g_csr_lookup_default_sg%NOTFOUND THEN
508 -- lookup type/code combo not found so return NULL
509 CLOSE g_csr_lookup_default_sg;
510 RETURN (NULL);
511 END IF;
512 CLOSE g_csr_lookup_default_sg;
513 -- add to plsql table
514 g_lookups_tab(g_hash_number).lookup_type := p_lookup_type;
515 g_lookups_tab(g_hash_number).lookup_code := p_lookup_code;
516 g_lookups_tab(g_hash_number).language := g_language;
517 g_lookups_tab(g_hash_number).meaning := g_meaning;
518
519 RETURN (g_meaning);
520 END;
521 EXCEPTION
522 -- unexpected error
523 WHEN OTHERS THEN
524 -- check to see if a cursor is open
525 IF g_csr_lookup_default_sg%ISOPEN THEN
526 CLOSE g_csr_lookup_default_sg;
527 END IF;
528 RETURN (NULL);
529 END psn_lng_decode_lookup;
530
531 --
532 -- -----------------------------------------------------------------------------
533 --
534 -- Get's a location_code for a person and location_id
535 --
536 FUNCTION psn_lng_location(p_person_id IN NUMBER
537 ,p_location_id IN NUMBER)
538 RETURN VARCHAR2 IS
539 --
540 BEGIN
541 --
542 Cache_location_details(hr_bpl_alert_recipient.Get_psn_lng(p_person_id)
543 ,p_location_id);
544 --
545 RETURN g_location_code;
546 --
547 END psn_lng_location;
548 --
549 -- -----------------------------------------------------------------------------
550 --
551 -- Get's a Organization Name for a person and organization_id
552 --
553 FUNCTION psn_lng_organization(p_person_id IN NUMBER
554 ,p_organization_id IN NUMBER)
555 RETURN VARCHAR2 IS
556 --
557 BEGIN
558 --
559 Cache_organization_details(hr_bpl_alert_recipient.Get_psn_lng(p_person_id)
560 ,p_organization_id);
561 --
562 RETURN g_organization_name;
563 --
564 END psn_lng_organization;
565 --
566 -- -----------------------------------------------------------------------------
567 --
568 -- Get's a location_code for the primary assignment supervisor of a person_id
569 -- for a given location_id
570 --
571 FUNCTION psn_sup_lng_location(p_person_id IN NUMBER
572 ,p_location_id IN NUMBER)
573 RETURN VARCHAR2 IS
574 --
575 BEGIN
576 --
577 Cache_location_details(hr_bpl_alert_recipient.Get_psn_sup_psn_lng(
578 p_person_id)
579 ,p_location_id);
580
581 --
582 RETURN g_location_code;
583 --
584 END psn_sup_lng_location;
585 --
586 -- -----------------------------------------------------------------------------
587 --
588 -- Get's a Organization Name for the primary assignment supervisor of
589 -- a person_id for a given organization_id
590 --
591 FUNCTION psn_sup_lng_organization(p_person_id IN NUMBER
592 ,p_organization_id IN NUMBER)
593 RETURN VARCHAR2 IS
594 --
595 BEGIN
596 --
597 Cache_organization_details(hr_bpl_alert_recipient.Get_psn_sup_psn_lng(
598 p_person_id)
599 ,p_organization_id);
600
601 --
602 RETURN g_organization_name;
603 --
604 END psn_sup_lng_organization;
605 --
606 -- -----------------------------------------------------------------------------
607 --
608 -- Get's a job_name for a job_id
609 --
610 FUNCTION psn_lng_job(p_person_id IN NUMBER
611 ,p_job_id IN NUMBER)
612 RETURN VARCHAR2 IS
613 --
614 BEGIN
615 --
616 Cache_job_details(p_job_id);
617 --
618 RETURN g_job_name;
619 --
620 END psn_lng_job;
621 --
622 -- -----------------------------------------------------------------------------
623 --
624 -- Get's a job_name for a job_id
625 --
626 FUNCTION psn_sup_lng_job(p_person_id IN NUMBER
627 ,p_job_id IN NUMBER)
628 RETURN VARCHAR2 IS
629 --
630 BEGIN
631 --
632 Cache_job_details(p_job_id);
633 --
634 RETURN g_job_name;
635 --
636 END psn_sup_lng_job;
637 --
638 -- -----------------------------------------------------------------------------
639 --
640 -- Get's a position_name for a position_id
641 --
642 FUNCTION psn_lng_position(p_person_id IN NUMBER
643 ,p_position_id IN NUMBER)
644 RETURN VARCHAR2 IS
645 --
646 BEGIN
647 --
648 Cache_position_details(p_position_id);
649 --
650 RETURN g_position_name;
651 --
652 END psn_lng_position;
653 --
654 -- -----------------------------------------------------------------------------
655 --
656 -- Get's a position_name for a position_id
657 --
658 FUNCTION psn_sup_lng_position(p_person_id IN NUMBER
659 ,p_position_id IN NUMBER)
660 RETURN VARCHAR2 IS
661 --
662 BEGIN
663 --
664 Cache_position_details(p_position_id);
665 --
666 RETURN g_position_name;
667 --
668 END psn_sup_lng_position;
669 --
670 -- -----------------------------------------------------------------------------
671 --
672 -- Get's a grade_name for a grade_id
673 --
674 FUNCTION psn_lng_grade(p_person_id IN NUMBER
675 ,p_grade_id IN NUMBER)
676 RETURN VARCHAR2 IS
677 --
678 BEGIN
679 --
680 Cache_grade_details(p_grade_id);
681 --
682 RETURN g_grade_name;
683 --
684 END psn_lng_grade;
685 --
686 -- -----------------------------------------------------------------------------
687 --
688 -- Get's a grade_name for a grade_id
689 --
690 FUNCTION psn_sup_lng_grade(p_person_id IN NUMBER
691 ,p_grade_id IN NUMBER)
692 RETURN VARCHAR2 IS
693 --
694 BEGIN
695 --
696 Cache_grade_details(p_grade_id);
697 --
698 RETURN g_grade_name;
699 --
700 END psn_sup_lng_grade;
701 --
702 --------------------------------------------------------------------------------
703 -- Returns Lookup Meaning for a given assignment language.
704 --
705 --
706
707 FUNCTION asg_lng_decode_lookup(p_lookup_type IN VARCHAR2
708 ,p_lookup_code IN VARCHAR2
709 ,p_assignment_id IN NUMBER)
710 RETURN fnd_lookup_values.meaning%TYPE IS
711
712 BEGIN
713 -- check to ensure the lookup type/code combo is NOT NULL
714 IF p_lookup_type IS NULL
715 OR p_lookup_code IS NULL
716 OR p_assignment_id IS NULL
717 THEN
718 -- exit incomplete type/code combo
719 RETURN (NULL);
720 END IF;
721 -- determine hash number for plsql table index.
722 g_hash_number :=
723 DBMS_UTILITY.get_hash_value(p_lookup_code || ':' || p_lookup_type || ':' || p_assignment_id
724 ,1
725 ,1048576);
726 g_language := hr_bpl_alert_recipient.get_asg_psn_lng(p_assignment_id);
727 -- (2^20)
728 BEGIN
729 -- check if we have a hash number generated that is for a different
730 -- lookup_type and lookup_code combination
731 -- very rare but possible from the hashing algorithm
732 -- on the first call to this if statement for any given g_hash_number
733 -- the if condition will raise a no_data_found before the else condition
734 -- because the plsql will not contain any values at the start of the session
735 IF g_lookups_tab(g_hash_number).lookup_type = p_lookup_type
736 AND g_lookups_tab(g_hash_number).lookup_code = p_lookup_code
737 AND g_lookups_tab(g_hash_number).language = g_language
738 THEN
739 -- cache hit
740 -- return the value from the plsql table
741 RETURN (g_lookups_tab(g_hash_number).meaning);
742 ELSE
743 -- cache miss
744 RAISE NO_DATA_FOUND;
745 END IF;
746
747 EXCEPTION
748 WHEN NO_DATA_FOUND
749 THEN
750 -- cache miss, determine value, and place in cache for next retrieval
751 -- use the default security group
752 OPEN g_csr_lookup_default_sg(p_lookup_type, p_lookup_code,g_language);
753 FETCH g_csr_lookup_default_sg INTO g_meaning;
754 IF g_csr_lookup_default_sg%NOTFOUND THEN
755 -- lookup type/code combo not found so return NULL
756 CLOSE g_csr_lookup_default_sg;
757 RETURN (NULL);
758 END IF;
759 CLOSE g_csr_lookup_default_sg;
760 -- add to plsql table
761 g_lookups_tab(g_hash_number).lookup_type := p_lookup_type;
762 g_lookups_tab(g_hash_number).lookup_code := p_lookup_code;
763 g_lookups_tab(g_hash_number).language := g_language;
764 g_lookups_tab(g_hash_number).meaning := g_meaning;
765
766 RETURN (g_meaning);
767 END;
768 EXCEPTION
769 -- unexpected error
770 WHEN OTHERS THEN
771 -- check to see if a cursor is open
772 IF g_csr_lookup_default_sg%ISOPEN THEN
773 CLOSE g_csr_lookup_default_sg;
774 END IF;
775 RETURN (NULL);
776 END asg_lng_decode_lookup;
777 --
778 -- -----------------------------------------------------------------------------
779 --
780 -- Get's a location_code for a given assignment_id and location_id
781 --
782 FUNCTION asg_lng_location(p_assignment_id IN NUMBER
783 ,p_location_id IN NUMBER)
784 RETURN VARCHAR2 IS
785 --
786 BEGIN
787 --
788 Cache_location_details(hr_bpl_alert_recipient.Get_asg_psn_lng(
789 p_assignment_id)
790 ,p_location_id);
791 --
792 RETURN g_location_code;
793 --
794 END asg_lng_location;
795 --
796 -- -----------------------------------------------------------------------------
797 --
798 -- Get's a Organization Name in the language required by for the assignment_id
799 -- and organization_id
800 --
801 FUNCTION asg_lng_organization(p_assignment_id IN NUMBER
802 ,p_organization_id IN NUMBER)
803 RETURN VARCHAR2 IS
804 --
805 BEGIN
806 --
807 Cache_organization_details(hr_bpl_alert_recipient.Get_asg_psn_lng(
808 p_assignment_id)
809 ,p_organization_id);
810 --
811 RETURN g_organization_name;
812 --
813 END asg_lng_organization;
814 --
815 --------------------------------------------------------------------------------
816 -- Returns Lookup Meaning for a given language for a assignment supervisor.
817 --
818 --
819
820 FUNCTION asg_sup_lng_decode_lookup(p_lookup_type IN VARCHAR2
821 ,p_lookup_code IN VARCHAR2
822 ,p_assignment_id IN NUMBER)
823 RETURN fnd_lookup_values.meaning%TYPE IS
824
825 BEGIN
826 -- check to ensure the lookup type/code combo is NOT NULL
827 IF p_lookup_type IS NULL
828 OR p_lookup_code IS NULL
829 OR p_assignment_id IS NULL
830 THEN
831 -- exit incomplete type/code combo
832 RETURN (NULL);
833 END IF;
834 -- determine hash number for plsql table index.
835 g_hash_number :=
836 DBMS_UTILITY.get_hash_value(p_lookup_code || ':' || p_lookup_type || ':' || p_assignment_id
837 ,1
838 ,1048576);
839 g_language := hr_bpl_alert_recipient.get_asg_sup_lng(p_assignment_id);
840 -- (2^20)
841 BEGIN
842 -- check if we have a hash number generated that is for a different
843 -- lookup_type and lookup_code combination
844 -- very rare but possible from the hashing algorithm
845 -- on the first call to this if statement for any given g_hash_number
846 -- the if condition will raise a no_data_found before the else condition
847 -- because the plsql will not contain any values at the start of the session
848 IF g_lookups_tab(g_hash_number).lookup_type = p_lookup_type
849 AND g_lookups_tab(g_hash_number).lookup_code = p_lookup_code
850 AND g_lookups_tab(g_hash_number).language = g_language
851 THEN
852 -- cache hit
853 -- return the value from the plsql table
854 RETURN (g_lookups_tab(g_hash_number).meaning);
855 ELSE
856 -- cache miss
857 RAISE NO_DATA_FOUND;
858 END IF;
859
860 EXCEPTION
861 WHEN NO_DATA_FOUND
862 THEN
863 -- cache miss, determine value, and place in cache for next retrieval
864 -- use the default security group
865 OPEN g_csr_lookup_default_sg(p_lookup_type, p_lookup_code,g_language);
866 FETCH g_csr_lookup_default_sg INTO g_meaning;
867 IF g_csr_lookup_default_sg%NOTFOUND THEN
868 -- lookup type/code combo not found so return NULL
869 CLOSE g_csr_lookup_default_sg;
870 RETURN (NULL);
871 END IF;
872 CLOSE g_csr_lookup_default_sg;
873 -- add to plsql table
874 g_lookups_tab(g_hash_number).lookup_type := p_lookup_type;
875 g_lookups_tab(g_hash_number).lookup_code := p_lookup_code;
876 g_lookups_tab(g_hash_number).language := g_language;
877 g_lookups_tab(g_hash_number).meaning := g_meaning;
878
879 RETURN (g_meaning);
880 END;
881 EXCEPTION
882 -- unexpected error
883 WHEN OTHERS THEN
884 -- check to see if a cursor is open
885 IF g_csr_lookup_default_sg%ISOPEN THEN
886 CLOSE g_csr_lookup_default_sg;
887 END IF;
888 RETURN (NULL);
889 END asg_sup_lng_decode_lookup;
890 --
891 -- -----------------------------------------------------------------------------
892 --
893 -- Get's a location_code for the assignment supervisor of an assignment_id
894 -- for a given location_id
895 --
896 FUNCTION asg_sup_lng_location(p_assignment_id IN NUMBER
897 ,p_location_id IN NUMBER)
898 RETURN VARCHAR2 IS
899 --
900 BEGIN
901 --
902 Cache_location_details(hr_bpl_alert_recipient.Get_asg_sup_lng(
903 p_assignment_id)
904 ,p_location_id);
905
906 --
907 RETURN g_location_code;
908 --
909 END asg_sup_lng_location;
910 --
911 -- -----------------------------------------------------------------------------
912 --
913 -- Get's a Organization Name in the language required by the assignment
914 -- supervisor for a given assignment_id and organization_id
915 --
916 FUNCTION asg_sup_lng_organization(p_assignment_id IN NUMBER
917 ,p_organization_id IN NUMBER)
918 RETURN VARCHAR2 IS
919 --
920 BEGIN
921 --
922 Cache_organization_details(hr_bpl_alert_recipient.Get_asg_sup_lng(
923 p_assignment_id)
924 ,p_organization_id);
925
926 --
927 RETURN g_organization_name;
928 --
929 END asg_sup_lng_organization;
930 --
931 -- -----------------------------------------------------------------------------
932 --
933 -- Get's a job_name for a job_id
934 --
935 FUNCTION asg_sup_lng_job(p_assignment_id IN NUMBER
936 ,p_job_id IN NUMBER)
937 RETURN VARCHAR2 IS
938 --
939 BEGIN
940 --
941 Cache_job_details(p_job_id);
942 --
943 RETURN g_job_name;
944 --
945 END asg_sup_lng_job;
946 --
947 -- -----------------------------------------------------------------------------
948 --
949 -- Get's a position_name for a position_id
950 --
951 FUNCTION asg_sup_lng_position(p_assignment_id IN NUMBER
952 ,p_position_id IN NUMBER)
953 RETURN VARCHAR2 IS
954 --
955 BEGIN
956 --
957 Cache_position_details(p_position_id);
958 --
959 RETURN g_position_name;
960 --
961 END asg_sup_lng_position;
962 --
963 -- -----------------------------------------------------------------------------
964 --
965 -- Get's a grade_name for a grade_id
966 --
967 FUNCTION asg_sup_lng_grade(p_assignment_id IN NUMBER
968 ,p_grade_id IN NUMBER)
969 RETURN VARCHAR2 IS
970 --
971 BEGIN
972 --
973 Cache_grade_details(p_grade_id);
974 --
975 RETURN g_grade_name;
976 --
977 END asg_sup_lng_grade;
978
979 --
980 --------------------------------------------------------------------------------
981 -- Returns Lookup Meaning for a given language for a primary assignment
982 -- supervisor.
983 --
984 --
985
986 FUNCTION pasg_sup_lng_decode_lookup(p_lookup_type IN VARCHAR2
987 ,p_lookup_code IN VARCHAR2
988 ,p_assignment_id IN NUMBER)
989 RETURN fnd_lookup_values.meaning%TYPE IS
990
991 BEGIN
992 -- check to ensure the lookup type/code combo is NOT NULL
993 IF p_lookup_type IS NULL
994 OR p_lookup_code IS NULL
995 OR p_assignment_id IS NULL
996 THEN
997 -- exit incomplete type/code combo
998 RETURN (NULL);
999 END IF;
1000 -- get the security group
1001 -- determine hash number for plsql table index.
1002 g_hash_number :=
1003 DBMS_UTILITY.get_hash_value(p_lookup_code || ':' || p_lookup_type || ':' || p_assignment_id
1004 ,1
1005 ,1048576);
1006 g_language := hr_bpl_alert_recipient.get_pasg_sup_lng(p_assignment_id);
1007 -- (2^20)
1008 BEGIN
1009 -- check if we have a hash number generated that is for a different
1010 -- lookup_type and lookup_code combination
1011 -- very rare but possible from the hashing algorithm
1012 -- on the first call to this if statement for any given g_hash_number
1013 -- the if condition will raise a no_data_found before the else condition
1014 -- because the plsql will not contain any values at the start of the session
1015 IF g_lookups_tab(g_hash_number).lookup_type = p_lookup_type
1016 AND g_lookups_tab(g_hash_number).lookup_code = p_lookup_code
1017 AND g_lookups_tab(g_hash_number).language = g_language
1018 THEN
1019 -- cache hit
1020 -- return the value from the plsql table
1021 RETURN (g_lookups_tab(g_hash_number).meaning);
1022 ELSE
1023 -- cache miss
1024 RAISE NO_DATA_FOUND;
1025 END IF;
1026
1027 EXCEPTION
1028 WHEN NO_DATA_FOUND
1029 THEN
1030 -- cache miss, determine value, and place in cache for next retrieval
1031 -- use the default security group
1032 OPEN g_csr_lookup_default_sg(p_lookup_type, p_lookup_code,g_language);
1033 FETCH g_csr_lookup_default_sg INTO g_meaning;
1034 IF g_csr_lookup_default_sg%NOTFOUND THEN
1035 -- lookup type/code combo not found so return NULL
1036 CLOSE g_csr_lookup_default_sg;
1037 RETURN (NULL);
1038 END IF;
1039 CLOSE g_csr_lookup_default_sg;
1040 -- add to plsql table
1041 g_lookups_tab(g_hash_number).lookup_type := p_lookup_type;
1042 g_lookups_tab(g_hash_number).lookup_code := p_lookup_code;
1043 g_lookups_tab(g_hash_number).language := g_language;
1044 g_lookups_tab(g_hash_number).meaning := g_meaning;
1045
1046 RETURN (g_meaning);
1047 END;
1048 EXCEPTION
1049 -- unexpected error
1050 WHEN OTHERS THEN
1051 -- check to see if a cursor is open
1052 IF g_csr_lookup_default_sg%ISOPEN THEN
1053 CLOSE g_csr_lookup_default_sg;
1054 END IF;
1055 RETURN (NULL);
1056 END pasg_sup_lng_decode_lookup;
1057 --
1058 -- -----------------------------------------------------------------------------
1059 --
1060 -- Get's a location_code for the primary assignment supervisor of an
1061 -- assignment_id for a given location_id
1062 --
1063 FUNCTION pasg_sup_lng_location(p_assignment_id IN NUMBER
1064 ,p_location_id IN NUMBER)
1065 RETURN VARCHAR2 IS
1066 --
1067 BEGIN
1068 --
1069 Cache_location_details(hr_bpl_alert_recipient.Get_pasg_sup_lng(
1070 p_assignment_id)
1071 ,p_location_id);
1072
1073 --
1074 RETURN g_location_code;
1075 --
1076 END pasg_sup_lng_location;
1077 --
1078 -- -----------------------------------------------------------------------------
1079 --
1080 -- Get's a Organization Name in the language required by the primary assignment
1081 -- supervisor for a given assignment_id and organization_id
1082 --
1083 FUNCTION pasg_sup_lng_organization(p_assignment_id IN NUMBER
1084 ,p_organization_id IN NUMBER)
1085 RETURN VARCHAR2 IS
1086 --
1087 BEGIN
1088 --
1089 Cache_organization_details(hr_bpl_alert_recipient.Get_pasg_sup_lng(
1090 p_assignment_id)
1091 ,p_organization_id);
1092
1093 --
1094 RETURN g_organization_name;
1095 --
1096 END pasg_sup_lng_organization;
1097 --
1098 -- -----------------------------------------------------------------------------
1099 --
1100 -- Get's a job_name for a job_id
1101 --
1102 FUNCTION pasg_sup_lng_job(p_assignment_id IN NUMBER
1103 ,p_job_id IN NUMBER)
1104 RETURN VARCHAR2 IS
1105 --
1106 BEGIN
1107 --
1108 Cache_job_details(p_job_id);
1109 --
1110 RETURN g_job_name;
1111 --
1112 END pasg_sup_lng_job;
1113 --
1114 -- -----------------------------------------------------------------------------
1115 --
1116 -- Get's a position_name for a position_id
1117 --
1118 FUNCTION pasg_sup_lng_position(p_assignment_id IN NUMBER
1119 ,p_position_id IN NUMBER)
1120 RETURN VARCHAR2 IS
1121 --
1122 BEGIN
1123 --
1124 Cache_position_details(p_position_id);
1125 --
1126 RETURN g_position_name;
1127 --
1128 END pasg_sup_lng_position;
1129 --
1130 -- -----------------------------------------------------------------------------
1131 --
1132 -- Get's a grade_name for a grade_id
1133 --
1134 FUNCTION pasg_sup_lng_grade(p_assignment_id IN NUMBER
1135 ,p_grade_id IN NUMBER)
1136 RETURN VARCHAR2 IS
1137 --
1138 BEGIN
1139 --
1140 Cache_grade_details(p_grade_id);
1141 --
1142 RETURN g_grade_name;
1143 --
1144 END pasg_sup_lng_grade;
1145 --
1146 END HR_BPL_ALERT_TRNSLT;
[Home] [Help]