1 PACKAGE BODY fnd_preference AS
2 /* $Header: AFTCPRFB.pls 120.1 2005/07/02 04:19:42 appldev ship $ */
3
4
5 -- Saves information for one preference.
6
7
8
9
10 -- Get the value of the given preference.
11
12 FUNCTION get(p_user_name IN VARCHAR2,
13 p_module_name IN VARCHAR2,
14 p_pref_name IN VARCHAR2) RETURN VARCHAR2
15 IS
16 l_pref_value fnd_user_preferences.preference_value%TYPE;
17 BEGIN
18 SELECT preference_value INTO l_pref_value
19 FROM fnd_user_preferences
20 WHERE user_name=p_user_name
21 AND module_name=p_module_name
22 AND preference_name=p_pref_name;
23 RETURN l_pref_value;
24 EXCEPTION WHEN no_data_found THEN
25 RETURN NULL;
26 END;
27
28 -- Get the value of the given encrypted preference.
29 -- Note: length of p_key value must be an exact multiple of 8.
30
31 FUNCTION eget(p_user_name in varchar2,
32 p_module_name in varchar2,
33 p_pref_name in varchar2,
34 p_key in varchar2) return varchar2 is
35 eval varchar2(240);
36 val varchar2(240);
37 begin
38 if (mod(length(p_key), 8) <> 0) then
39 return null;
40 end if;
41
42 eval := fnd_preference.get(p_user_name, p_module_name, p_pref_name);
43 if (eval is null) then
44 -- Don't decrypt, dbms_obfuscation_toolkit can't handle nulls --
45 return null;
46 else
47 dbms_obfuscation_toolkit.desdecrypt(input_string => eval,
48 key_string => p_key,
49 decrypted_string => val);
50 return rtrim(val, ' ');
51 end if;
52 end;
53
54 -- Updates the value of the preference if it exists, otherwise creates it.
55
56 PROCEDURE put(p_user_name IN VARCHAR2,
57 p_module_name IN VARCHAR2,
58 p_pref_name IN VARCHAR2,
59 p_pref_value IN VARCHAR2)
60 IS
61 BEGIN
62
63 UPDATE fnd_user_preferences
64 SET preference_value=p_pref_value
65 WHERE user_name=p_user_name
66 AND module_name=p_module_name
67 AND preference_name=p_pref_name;
68
69 IF SQL%notfound THEN
70
71 INSERT INTO fnd_user_preferences
72 (
73 user_name,
74 module_name,
75 preference_name,
76 preference_value
77 )
78 VALUES
79 (
80 p_user_name,
81 p_module_name,
82 p_pref_name,
83 p_pref_value
84 );
85
86 END IF;
87
88 -- Bug 3203225
89 IF ((UPPER(p_pref_name) = 'MAILTYPE') and (UPPER(p_module_name) = 'WF')) then
90 FND_USER_PKG.User_synch(p_user_name);
91 END IF;
92
93 END;
94
95
96 -- Updates the value of the encrypted preference if it exists,
97 -- otherwise creates it.
98 -- Note: length of p_key value must be an exact multiple of 8.
99 -- This routine ensures pref_value is also exact multiple of 8
100 -- and encrypts.
101
102 PROCEDURE eput(p_user_name in varchar2,
103 p_module_name in varchar2,
104 p_pref_name in varchar2,
105 p_pref_value in varchar2,
106 p_key in varchar2) is
107 mymod number;
108 padval varchar2(240);
109 eval varchar2(240);
110 begin
111 if (mod(length(p_key), 8) <> 0) then
112 return;
113 end if;
114
115 if (p_pref_value is null) then
116 -- Don't encrypt it, dbms_obfuscation_toolkit can't handle nulls --
117 eval := p_pref_value;
118 else
119 -- Pad the value and encrypt --
120 mymod := mod(length(p_pref_value), 8);
121
122 if (mymod = 0) then
123 padval := p_pref_value;
124 else
125 padval := rpad(p_pref_value, length(p_pref_value) + 8 - mymod, ' ');
126 end if;
127
128 dbms_obfuscation_toolkit.desencrypt(input_string => padval,
129 key_string => p_key,
130 encrypted_string => eval);
131 end if;
132
133 fnd_preference.put(p_user_name, p_module_name, p_pref_name, eval);
134 end;
135
136
137 FUNCTION exists(p_user_name IN VARCHAR2,
138 p_module_name IN VARCHAR2,
139 p_pref_name IN VARCHAR2) RETURN BOOLEAN
140 IS
141 l_count INTEGER ;
142 BEGIN
143 SELECT COUNT(1) INTO l_count
144 FROM fnd_user_preferences
145 WHERE user_name=p_user_name
146 AND module_name=p_module_name
147 AND preference_name=p_pref_name;
148 IF(l_count=0) THEN
149 RETURN FALSE;
150 ELSE
151 RETURN TRUE;
152 END IF;
153 END;
154
155 PROCEDURE remove(p_user_name IN VARCHAR2,
156 p_module_name IN VARCHAR2,
157 p_pref_name IN VARCHAR2)
158 IS
159 BEGIN
160 DELETE FROM fnd_user_preferences
161 WHERE user_name=p_user_name
162 AND module_name=p_module_name
163 AND preference_name=p_pref_name;
164
165 END;
166
167 PROCEDURE save_change(p_user_name IN VARCHAR2,
168 p_module_name IN VARCHAR2,
169 p_preference_name IN VARCHAR2,
170 p_preference_value IN VARCHAR2,
171 p_action IN VARCHAR2)
172 IS
173 BEGIN
174
175
176 IF(p_action='D') THEN
177 remove (p_user_name,p_module_name,p_preference_name);
178
179 ELSIF((p_action='I')OR(p_action='U')) THEN
180 put(p_user_name,
181 p_module_name,
182 p_preference_name,
183 p_preference_value);
184
185 ELSIF (p_action='C') THEN
186 delete_all(p_user_name,p_module_name);
187
188 END IF;
189
190 END;
191
192
193 PROCEDURE save_changes(p_user_name IN VARCHAR2,
194 p_module_name IN VARCHAR2,
195 p_prefs_tab IN prefs_tab_type)
196 IS
197 BEGIN
198
199 FOR i IN 1..p_prefs_tab.COUNT LOOP
200 save_change(p_user_name,p_module_name,p_prefs_tab(i).name,p_prefs_tab(i).value,p_prefs_tab(i).action);
201 END LOOP;
202 END;
203
204 PROCEDURE delete_all(p_user_name IN VARCHAR2,
205 p_module_name IN VARCHAR2)
206 IS
207 BEGIN
208 DELETE FROM fnd_user_preferences
209 WHERE user_name=p_user_name
210 AND module_name=p_module_name;
211 END;
212
213 -- Get the value of a preference.
214 -- Note: Returns *NULL* for blank preferences
215 -- *UNKNOWN* for missing preferences
216
217 FUNCTION GetDefined(p_user_name IN VARCHAR2,
218 p_module_name IN VARCHAR2,
219 p_pref_name IN VARCHAR2) RETURN VARCHAR2 IS
220 l_pref_value fnd_user_preferences.preference_value%TYPE;
221 begin
222 select nvl(preference_value,'*NULL*') into l_pref_value
223 from fnd_user_preferences
224 where user_name = p_user_name
225 and module_name = p_module_name
226 and preference_name = p_pref_name;
227
228 return l_pref_value;
229 exception when no_data_found then
230 return '*UNKNOWN*';
231 end;
232
233 -- Updates the value of the preference if it exists, otherwise creates it
234 -- If pref_value is *NULL*, blanks out value
235 -- If pref_value is *UNKNOWN*, does nothing
236
237 PROCEDURE putDefined(p_user_name IN VARCHAR2,
238 p_module_name IN VARCHAR2,
239 p_pref_name IN VARCHAR2,
240 p_pref_value IN VARCHAR2) is
241 begin
242 if (p_pref_value = '*UNKNOWN*') then
243 return;
244 end if;
245
246 update fnd_user_preferences
247 set preference_value = decode(p_pref_value, '*NULL*', null, p_pref_value)
248 where user_name = p_user_name
249 and module_name = p_module_name
250 and preference_name = p_pref_name;
251
252 if SQL%notfound then
253 insert into fnd_user_preferences (
254 user_name,
255 module_name,
256 preference_name,
257 preference_value)
258 values
259 (p_user_name,
260 p_module_name,
261 p_pref_name,
262 decode(p_pref_value, '*NULL*', null, p_pref_value));
263 end if;
264 end;
265 ------------------------------
266 END;