1 PACKAGE BODY AD_UPDATE_PREFS_PKG AS
2 -- $Header: adupprfb.pls 115.3 2004/09/17 07:37:36 msailoz ship $
3
4 PROCEDURE DEFINE_PREFERENCE(
5 p_owner in VARCHAR2,
6 p_name in VARCHAR2,
7 p_description in VARCHAR2 DEFAULT NULL ,
8 p_default_value in VARCHAR2 DEFAULT NULL )
9 IS
10 l_owner ad_update_preferences.owner%TYPE ;
11 l_name ad_update_preferences.name%TYPE ;
12 p_pref_id ad_update_preferences.preference_id%TYPE ;
13
14 BEGIN
15 l_owner := upper(p_owner);
16 l_name := upper(p_name);
17
18 -- Check if the preference already exists
19
20 SELECT preference_id
21 INTO p_pref_id
22 FROM ad_update_preferences
23 WHERE name = l_name
24 AND owner= l_owner;
25
26 IF SQL%ROWCOUNT > 0 THEN
27 RAISE_APPLICATION_ERROR(-20001,'Preference "'|| p_pref_id ||' : '|| l_name ||'" already exists');
28 END IF;
29
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32
33 -- Insert a new record to define the new preference
34
35 INSERT INTO ad_update_preferences(
36 preference_id,
37 owner,
38 name,
39 description,
40 default_value,
41 creation_date,
42 created_by,
43 last_update_date,
44 last_updated_by)
45 VALUES(
46 AD_UPDATE_PREFERENCES_S.NEXTVAL ,
47 l_owner,
48 l_name,
49 p_description,
50 p_default_value,
51 SYSDATE,
52 SYSADMIN_VALUE,
53 SYSDATE,
54 SYSADMIN_VALUE);
55
56 COMMIT ;
57
58 END DEFINE_PREFERENCE;
59
60 PROCEDURE UPDATE_DEF_PREFERENCE(
61 p_owner IN VARCHAR2,
62 p_name IN VARCHAR2,
63 p_description IN VARCHAR2,
64 p_default_value IN VARCHAR2,
65 p_pref_id OUT NOCOPY NUMBER )
66 IS
67 l_owner AD_UPDATE_PREFERENCES.owner%TYPE ;
68 l_name AD_UPDATE_PREFERENCES.name%TYPE ;
69 BEGIN
70 l_owner := upper(p_owner);
71 l_name := upper(p_name);
72
73 -- Check if the preference already exists
74
75 SELECT preference_id
76 INTO p_pref_id
77 FROM ad_update_preferences
78 WHERE name = l_name
79 AND owner= l_owner;
80
81
82 UPDATE ad_update_preferences
83 SET description = p_description,
84 default_value= p_default_value,
85 last_update_date = SYSDATE,
86 last_updated_by = SYSADMIN_VALUE
87 WHERE name = l_name
88 AND owner= l_owner;
89
90 COMMIT ;
91
92 EXCEPTION
93 WHEN NO_DATA_FOUND THEN
94 RAISE_APPLICATION_ERROR(-20001,'Preference '||'l_name'||' does not exist');
95
96 END UPDATE_DEF_PREFERENCE;
97
98
99
100 FUNCTION GET_PREFERENCE_ID(
101 p_owner IN VARCHAR2,
102 p_name IN VARCHAR2)
103 RETURN NUMBER
104 IS
105 l_pref_id ad_update_preferences.preference_id%TYPE ;
106 BEGIN
107
108 SELECT preference_id INTO l_pref_id
109 FROM ad_update_preferences
110 WHERE owner = upper(p_owner)
111 AND name = upper(p_name) ;
112
113 RETURN l_pref_id;
114
115 EXCEPTION
116 --Return NULL if the preference is not found
117
118 WHEN NO_DATA_FOUND THEN
119 RETURN NULL;
120
121 END GET_PREFERENCE_ID;
122
123
124
125 FUNCTION GET_PREFERENCE_VALUE(
126 p_owner IN VARCHAR2,
127 p_name IN VARCHAR2,
128 p_session_id IN NUMBER DEFAULT NULL)
129 RETURN VARCHAR2
130 IS
131 l_value ad_update_pref_values.value%TYPE ;
132 BEGIN
133
134 SELECT value INTO l_value
135 FROM ad_update_pref_values v,ad_update_preferences p
136 WHERE v.preference_id=p.preference_id
137 AND owner = upper(p_owner)
138 AND name = upper(p_name)
139 --Check for global or session preference
140 AND pref_level = decode(p_session_id,NULL,'G','S')
141 --Match the session id if session preference otherwise always true for global preference
142 AND NVL (pref_level_value,0) = NVL (p_session_id, NVL(pref_level_value, 0) );
143
144 RETURN l_value;
145
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148
149 BEGIN
150 --If Session level preference is not found get the global preference
151
152 SELECT value INTO l_value
153 FROM ad_update_pref_values v,ad_update_preferences p
154 WHERE v.preference_id=p.preference_id
155 AND owner = upper(p_owner)
156 AND name = upper(p_name)
157 AND pref_level = 'G';
158 RETURN l_value;
159
160 EXCEPTION
161 --Return undefined value if global preference is also not found
162
163 WHEN NO_DATA_FOUND THEN
164 RETURN UNDEF_VALUE;
165
166 END;
167
168 END GET_PREFERENCE_VALUE;
169
170
171 FUNCTION GET_SESSION_PREFERENCE_VALUE(
172 p_owner IN VARCHAR2,
173 p_name IN VARCHAR2,
174 p_session_id IN NUMBER)
175 RETURN VARCHAR2
176 IS
177 l_value ad_update_pref_values.value%TYPE ;
178 BEGIN
179 --Get the session level preference value for the given parameters
180
181 SELECT value INTO l_value
182 FROM ad_update_pref_values v,ad_update_preferences p
183 WHERE v.preference_id=p.preference_id
184 AND owner = upper(p_owner)
185 AND name=upper(p_name )
186 AND pref_level = 'S'
187 AND pref_level_value=p_session_id;
188
189 RETURN l_value;
190
191 EXCEPTION
192 --Return undefined value if the session preference is not found
193
194 WHEN NO_DATA_FOUND THEN
195 RETURN UNDEF_VALUE;
196
197 END GET_SESSION_PREFERENCE_VALUE;
198
199
200
201 PROCEDURE UPDATE_PREFERENCE_VALUE(
202 p_owner IN VARCHAR2,
203 p_name IN VARCHAR2,
204 p_value IN VARCHAR2)
205 IS
206 p_pref_id AD_UPDATE_PREFERENCES.preference_id%TYPE ;
207 l_owner AD_UPDATE_PREFERENCES.owner%TYPE ;
208 l_name AD_UPDATE_PREFERENCES.name%TYPE ;
209 l_value AD_UPDATE_PREF_VALUES.value%TYPE ;
210 BEGIN
211 l_owner := upper(p_owner);
212 l_name := upper(p_name);
213 -- Check the existence of the preference
214 p_pref_id := get_preference_id (l_owner,l_name);
215 IF p_pref_id IS NULL THEN
216 RAISE_APPLICATION_ERROR(-20001,'Preference Not Found');
217 END IF;
218 -- Check for the record for the Global Preference Value
219 SELECT value
220 INTO l_value
221 FROM AD_UPDATE_PREF_VALUES
222 WHERE preference_id = p_pref_id
223 AND pref_level = 'G';
224
225 -- Update the value for the Global Preference
226 UPDATE ad_update_pref_values
227 SET value = p_value,
228 last_update_date = SYSDATE,
229 last_updated_by = SYSADMIN_VALUE
230 WHERE preference_id=p_pref_id;
231
232 COMMIT ;
233
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN
236 RAISE_APPLICATION_ERROR(-20001,'Global Preference value"'|| p_pref_id ||': '||
237 l_name ||'" does not exist');
238
239 END UPDATE_PREFERENCE_VALUE;
240
241
242 PROCEDURE CREATE_PREFERENCE_VALUE(
243 p_owner IN VARCHAR2,
244 p_name IN VARCHAR2,
245 p_value IN VARCHAR2)
246 IS
247 p_pref_id ad_update_preferences.preference_id%TYPE ;
248 l_owner AD_UPDATE_PREFERENCES.owner%TYPE ;
249 l_name AD_UPDATE_PREFERENCES.name%TYPE ;
250 BEGIN
251 l_owner := upper(p_owner);
252 l_name := upper(p_name);
253 -- Check the existence of the preference
254 p_pref_id := get_preference_id (l_owner,l_name);
255 IF p_pref_id IS NULL THEN
256 RAISE_APPLICATION_ERROR(-20001,'Preference Not Found');
257 END IF;
258
259 SELECT preference_id
260 INTO p_pref_id
261 FROM ad_update_pref_values
262 WHERE preference_id = p_pref_id
263 AND pref_level = 'G';
264 -- Check if the record for the Global Preference Value already exists
265
266 IF SQL%ROWCOUNT > 0 THEN
267 RAISE_APPLICATION_ERROR(-20001,'Global Preference value for "'|| l_name ||'" already exists');
268 END IF;
269
270 EXCEPTION
271 WHEN NO_DATA_FOUND THEN
272
273 -- Insert a new record to define the new Global preference value
274
275 INSERT INTO ad_update_pref_values(
276 preference_id,
277 pref_level,
278 pref_level_value,
279 value,
280 creation_date,
281 created_by,
282 last_update_date,
283 last_updated_by)
284 VALUES(
285 p_pref_id,
286 'G',
287 GLOBAL_SESSION_VALUE,
288 p_value,
289 SYSDATE,
290 SYSADMIN_VALUE,
291 SYSDATE,
292 SYSADMIN_VALUE);
293
294 COMMIT ;
295
296 END CREATE_PREFERENCE_VALUE;
297
298 PROCEDURE SET_SESSION_PREFERENCE_VALUE(
299 p_owner IN VARCHAR2,
300 p_name IN VARCHAR2,
301 p_session_id IN NUMBER,
302 p_value IN VARCHAR2)
303 IS
304 l_pref_id ad_update_pref_values.preference_id%TYPE ;
305 BEGIN
306 --Get the preference_id for the given owner,name of the preference
307
308 SELECT preference_id INTO l_pref_id
309 FROM ad_update_preferences WHERE
310 owner = upper(p_owner)
311 AND name = upper(p_name);
312
313 --Update the preference value for that particular session
314 UPDATE ad_update_pref_values
315 SET value = p_value,
316 last_update_date = SYSDATE,
317 last_updated_by =SYSADMIN_VALUE
318 WHERE
319 preference_id = l_pref_id
320 AND pref_level = 'S'
321 AND pref_level_value=p_session_id;
322
323 IF SQL%ROWCOUNT = 0 THEN
324 -- Create new value record if the value record is not updated
325 INSERT INTO ad_update_pref_values(
326 preference_id,
327 pref_level,
328 pref_level_value,
329 value,
330 creation_date,
331 created_by,
332 last_update_date,
333 last_updated_by)
334 VALUES (
335 l_pref_id,
336 'S',
337 p_session_id,
338 p_value,
339 SYSDATE ,
340 SYSADMIN_VALUE,
341 SYSDATE ,
342 SYSADMIN_VALUE);
343 END IF ;
344 COMMIT;
345 EXCEPTION WHEN NO_DATA_FOUND then
346 --Raise an exception
347 RAISE_APPLICATION_ERROR (-20001,'Preference does not exist');
348 END SET_SESSION_PREFERENCE_VALUE;
349
350 END AD_UPDATE_PREFS_PKG;
[Home] [Help]