1 PACKAGE BODY hr_bis AS
2 /* $Header: hr_bis.pkb 120.1 2005/08/11 10:09:42 cbridge noship $ */
3
4 TYPE lookup_value_pair_rec IS RECORD(
5 lookup_type fnd_lookup_values.lookup_type%TYPE,
6 lookup_code fnd_lookup_values.lookup_code%TYPE,
7 meaning fnd_lookup_values.meaning%TYPE);
8
9 TYPE fnd_lookups_cache_tabtype IS table OF lookup_value_pair_rec
10 INDEX BY BINARY_INTEGER;
11 g_lookups_tab fnd_lookups_cache_tabtype;
12 -- define the client info
13 g_client_info VARCHAR2(64) := USERENV('CLIENT_INFO'); -- bug 1533907
14 -- define the security group
15 g_security_group_id fnd_lookup_values.security_group_id%TYPE;
16 -- define a global meaning
17 g_meaning fnd_lookup_values.meaning%TYPE;
18 -- define a holder for the hash number
19 g_hash_number BINARY_INTEGER;
20 -- define the bis_decode_lookup cursors
21 CURSOR g_csr_lookup_select_sg(
22 c_lookup_type VARCHAR2,
23 c_lookup_code VARCHAR2) IS
24 SELECT flv.meaning
25 FROM fnd_lookup_values flv
26 WHERE flv.lookup_code = c_lookup_code
27 AND flv.lookup_type = c_lookup_type
28 AND flv.language = USERENV('LANG')
29 AND flv.view_application_id = 3
30 AND flv.security_group_id =
31 fnd_global.lookup_security_group(
32 flv.lookup_type,
33 flv.view_application_id);
34 --
35 CURSOR g_csr_lookup_default_sg(
36 c_lookup_type VARCHAR2,
37 c_lookup_code VARCHAR2) IS
38 SELECT flv.meaning
39 FROM fnd_lookup_values flv
40 WHERE flv.lookup_code = c_lookup_code
41 AND flv.lookup_type = c_lookup_type
42 AND flv.language = USERENV('LANG')
43 AND flv.view_application_id = 3
44 AND flv.security_group_id = 0;
45 --
46 --******************************************************************************
47 --* Returns the meaning for a lookup code of a specified type.
48 --******************************************************************************
49 FUNCTION bis_decode_lookup(
50 p_lookup_type VARCHAR2,
51 p_lookup_code VARCHAR2)
52 RETURN fnd_lookup_values.meaning%TYPE IS
53 BEGIN
54 -- Note the language, security group and legislation code cannot
55 -- be changed within a Discoverer session
56 -- check to ensure the lookup type/code combo is NOT NULL
57 IF p_lookup_type IS NULL
58 OR p_lookup_code IS NULL THEN
59 -- exit incomplete type/code combo
60 RETURN (NULL);
61 END IF;
62 -- get the security group
63 IF g_security_group_id IS NULL THEN
64 -- the global security group has not been set
65 IF NVL(SUBSTRB(g_client_info, 55, 1), '0') = '0'
66 OR NVL(SUBSTRB(g_client_info, 55, 1), '0') = ' ' THEN
67 g_security_group_id := 0;
68 ELSE
69 g_security_group_id := TO_NUMBER(SUBSTRB(g_client_info, 55, 10));
70 END IF;
71 END IF;
72 -- determine hash number for plsql table index.
73 g_hash_number :=
74 DBMS_UTILITY.get_hash_value(
75 p_lookup_code || ':' || p_lookup_type,
76 1,
77 1048576); -- (2^20)
78 BEGIN
79 -- check if we have a hash number generated that is for a different
80 -- lookup_type and lookup_code combination
81 -- very rare but possible from the hashing algorithm
82
83 -- on the first call to this if statement for any given g_hash_number
84 -- the if condition will raise a no_data_found before the else condition
85 -- because the plsql will not contain any values at the start of the session
86 IF g_lookups_tab(g_hash_number).lookup_type = p_lookup_type
87 AND g_lookups_tab(g_hash_number).lookup_code = p_lookup_code THEN
88
89 -- cache hit
90 -- return the value from the plsql table
91 RETURN (g_lookups_tab(g_hash_number).meaning);
92 ELSE /* bug 1548213 */
93 -- cache miss
94 RAISE NO_DATA_FOUND;
95 END IF;
96 EXCEPTION
97 WHEN NO_DATA_FOUND THEN
98 -- cache miss, determine value, and place in cache for next retrieval
99 IF g_security_group_id = 0 THEN
100 -- use the default security group
101 OPEN g_csr_lookup_default_sg(p_lookup_type, p_lookup_code);
102 FETCH g_csr_lookup_default_sg INTO g_meaning;
103 IF g_csr_lookup_default_sg%NOTFOUND THEN
104 -- lookup type/code combo not found so return NULL
105 CLOSE g_csr_lookup_default_sg;
106 RETURN (NULL);
107 END IF;
108 CLOSE g_csr_lookup_default_sg;
109 ELSE
110 -- not using the default security group
111 OPEN g_csr_lookup_select_sg(p_lookup_type, p_lookup_code);
112 FETCH g_csr_lookup_select_sg INTO g_meaning;
113 IF g_csr_lookup_select_sg%NOTFOUND THEN
114 -- lookup type/code combo not found so check for default sec. group.
115 CLOSE g_csr_lookup_select_sg;
116
117 -- bug 2204602 start of changes. 30-JAN-2002
118 -- use the default security group
119 OPEN g_csr_lookup_default_sg(p_lookup_type, p_lookup_code);
120 FETCH g_csr_lookup_default_sg INTO g_meaning;
121 IF g_csr_lookup_default_sg%NOTFOUND THEN
122 -- lookup type/code combo not found so return NULL
123 CLOSE g_csr_lookup_default_sg;
124 RETURN (NULL);
125 END IF;
126 CLOSE g_csr_lookup_default_sg;
127
128 -- bug 2204602 end of changes. 30-JAN-2002
129
130 --RETURN (NULL);
131 END IF;
132 CLOSE g_csr_lookup_select_sg;
133 END IF;
134 -- add to plsql table
135 g_lookups_tab(g_hash_number).lookup_type := p_lookup_type;
136 g_lookups_tab(g_hash_number).lookup_code := p_lookup_code;
137 g_lookups_tab(g_hash_number).meaning := g_meaning;
138
139 RETURN (g_meaning);
140 END;
141 EXCEPTION
142 -- unexpected error
143 WHEN OTHERS THEN
144 -- check to see if a cursor is open
145 IF g_csr_lookup_default_sg%ISOPEN THEN
146 CLOSE g_csr_lookup_default_sg;
147 ELSIF g_csr_lookup_select_sg%ISOPEN THEN
148 CLOSE g_csr_lookup_select_sg;
149 END IF;
150 RETURN (NULL);
151 END bis_decode_lookup;
152
153 /******************************************************************************/
154 /* Returns business group id of the current security profile */
155 /******************************************************************************/
156 FUNCTION get_sec_profile_bg_id
157 RETURN per_security_profiles.business_group_id%TYPE IS
158
159 BEGIN
160
161 RETURN hr_security.get_sec_profile_bg_id;
162
163 END get_sec_profile_bg_id;
164
165 /*******************************************************************************/
166 /* Returns legislation code of the current secuirty profile */
167 /*******************************************************************************/
168 FUNCTION get_legislation_code
169 RETURN VARCHAR2 IS
170 --
171 -- local parameters
172 --
173 l_legislation_code VARCHAR2(150);
174 --
175 BEGIN
176 --
177 -- get the legislation code
178 --
179 SELECT org_info.org_information9 INTO l_legislation_code
180 FROM hr_organization_information org_info
181 WHERE org_info.org_information_context = 'Business Group Information'
182 AND org_info.organization_id = hr_security.get_sec_profile_bg_id;
183 --
184 RETURN (l_legislation_code);
185 --
186 EXCEPTION
187 WHEN NO_DATA_FOUND THEN
188 --
189 RETURN (NULL);
190 --
191 END get_legislation_code;
192 --
193
194 /*******************************************************************************/
195 /* Returns a fnd lookup meaning, for a given fnd_language */
196 /*******************************************************************************/
197
198 FUNCTION decode_lang_lookup(p_language VARCHAR2,
199 p_lookup_type VARCHAR2,
200 p_lookup_code VARCHAR2)
201 RETURN fnd_lookup_values.meaning%TYPE IS
202
203 l_meaning fnd_lookup_values.meaning%TYPE := TO_CHAR(NULL);
204
205 -- define the bis_decode_lookup cursors
206 CURSOR csr_lookup_select_sg(
207 c_language VARCHAR2,
208 c_lookup_type VARCHAR2,
209 c_lookup_code VARCHAR2) IS
210 SELECT flv.meaning
211 FROM fnd_lookup_values flv
212 WHERE flv.lookup_code = c_lookup_code
213 AND flv.lookup_type = c_lookup_type
214 AND flv.language = c_language
215 AND flv.view_application_id = 3
216 AND flv.security_group_id = 0;
217
218 BEGIN
219
220 IF ( p_language IS NULL
221 OR p_lookup_type IS NULL
222 OR p_lookup_code IS NULL ) THEN
223 RETURN TO_CHAR(NULL);
224 END IF;
225
226 -- call existing function if language requested is same as
227 -- the current session language
228 IF ( p_language = USERENV('LANG') ) THEN
229 RETURN hr_bis.bis_decode_lookup(p_lookup_type, p_lookup_code);
230 END IF;
231
232
233 OPEN csr_lookup_select_sg(p_language, p_lookup_type, p_lookup_code);
234 FETCH csr_lookup_select_sg INTO l_meaning;
235 IF csr_lookup_select_sg%NOTFOUND THEN
236 -- lookup language/type/code combo not found so return NULL
237 CLOSE csr_lookup_select_sg;
238 RETURN (TO_CHAR(NULL));
239 END IF;
240 CLOSE csr_lookup_select_sg;
241
242 RETURN l_meaning;
243
244 EXCEPTION
245 WHEN NO_DATA_FOUND THEN
246 RETURN TO_CHAR(NULL);
247
248 END decode_lang_lookup;
249
250
251 END hr_bis;
[Home] [Help]