1 PACKAGE BODY OE_PARAMETERS_DEF_UTIL AS
2 /* $Header: OEXUPADB.pls 120.1 2005/10/20 00:22:29 ppnair noship $ */
3
4 -- Start of comments
5 -- API name : Insert_Row
6 -- Type : Public
7 -- Description : Inserts record in oe_sys_parameter_def_b and oe_sys_parameter_def_tl table
8 -- Parameters :
9 -- IN : p_sys_param_def_rec IN
10 -- OE_PARAMETERS_DEF_UTIL.sys_param_def_rec_type Required
11 --
12 -- End of Comments
13 PROCEDURE Insert_Row(p_sys_param_def_rec IN OE_PARAMETERS_DEF_UTIL.sys_param_def_rec_type)
14 IS
15 --
16 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
17 --
18 BEGIN
19 --- Inserting row to OE_SYS_PARAMETER_DEF_B table
20 INSERT INTO oe_sys_parameter_def_b (
21 parameter_code,
22 creation_date,
23 created_by,
24 last_update_date,
25 last_updated_by,
26 last_update_login,
27 category_code,
28 value_set_id,
29 open_orders_check_flag,
30 enabled_flag,
31 Seeded_flag)
32 VALUES (
33 p_sys_param_def_rec.parameter_code,
34 p_sys_param_def_rec.creation_date,
35 p_sys_param_def_rec.created_by,
36 p_sys_param_def_rec.last_update_date,
37 p_sys_param_def_rec.last_updated_by,
38 p_sys_param_def_rec.last_update_login,
39 p_sys_param_def_rec.category_code,
40 p_sys_param_def_rec.value_set_id,
41 p_sys_param_def_rec.open_orders_check_flag,
42 p_sys_param_def_rec.enabled_flag,
43 p_sys_param_def_rec.seeded_flag);
44 --- Insertng row to OE_SYS_PARAMETER_DEF_TL table
45 INSERT INTO oe_sys_parameter_def_tl (
46 parameter_code,
47 creation_date,
48 created_by,
49 last_update_date,
50 last_updated_by,
51 last_update_login,
52 language,
53 source_lang,
54 name,
55 description)
56 SELECT
57 p_sys_param_def_rec.parameter_code,
58 p_sys_param_def_rec.creation_date,
59 p_sys_param_def_rec.created_by,
60 p_sys_param_def_rec.last_update_date,
61 p_sys_param_def_rec.last_updated_by,
62 p_sys_param_def_rec.last_update_login,
63 L.language_code,
64 userenv('LANG'),
65 p_sys_param_def_rec.name,
66 p_sys_param_def_rec.description
67 FROM fnd_languages L
68 WHERE L.installed_flag IN ('I', 'B')
69 AND NOT EXISTS
70 (SELECT NULL
71 FROM oe_sys_parameter_def_tl T
72 where T.parameter_code = p_sys_param_def_rec.parameter_code
73 and T.language = L.language_code);
74 END Insert_Row;
75
76 -- Start of comments
77 -- API name : Delete_Row
78 -- Type : Public
79 -- Description : Delete Parameter definition record in oe_sys_parameter_def_b and oe_sys_parameter_def_tl table
80 -- Parameters :
81 -- IN : p_parameter_code IN VARCHAR2 Required
82 --
83 -- End of Comments
84
85 PROCEDURE Delete_Row(p_parameter_code IN VARCHAR2)
86 IS
87 --
88 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
89 --
90 BEGIN
91 DELETE FROM oe_sys_parameter_def_tl
92 WHERE parameter_code = p_parameter_code;
93 IF (sql%notfound) THEN
94 RAISE no_data_found;
95 END IF;
96
97 DELETE FROM oe_sys_parameter_def_b
98 WHERE parameter_code = p_parameter_code;
99 IF (sql%notfound) THEN
100 RAISE no_data_found;
101 END IF;
102 END Delete_Row;
103
104 -- Start of comments
105 -- API name : Update_Row
106 -- Type : Public
107 -- Description : Update Parameter definition record in oe_sys_parameter_def_b and oe_sys_parameter_def_tl table
108 -- Parameters :
109 -- IN : p_sys_param_def_rec IN
110 -- OE_PARAMETERS_DEF_UTIL.sys_param_def_rec_type Required
111 --
112 -- End of Comments
113 PROCEDURE Update_Row(p_sys_param_def_rec IN OE_PARAMETERS_DEF_UTIL.sys_param_def_rec_type)
114 IS
115 --
116 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
117 --
118 BEGIN
119 UPDATE oe_sys_parameter_def_b
120 SET last_update_date = p_sys_param_def_rec.last_update_date,
121 last_updated_by = p_sys_param_def_rec.last_updated_by,
122 last_update_login = p_sys_param_def_rec.last_update_login,
123 category_code = p_sys_param_def_rec.category_code,
124 value_set_id = p_sys_param_def_rec.value_set_id,
125 open_orders_check_flag = p_sys_param_def_rec.open_orders_check_flag,
126 enabled_flag =p_sys_param_def_rec.enabled_flag,
127 seeded_flag =p_sys_param_def_rec.seeded_flag
128 WHERE parameter_code = p_sys_param_def_rec.parameter_code;
129 IF (sql%notfound) THEN
130 RAISE no_data_found;
131 END IF;
132
133 UPDATE oe_sys_parameter_def_tl
134 SET last_update_date = p_sys_param_def_rec.last_update_date,
135 last_updated_by = p_sys_param_def_rec.last_updated_by,
136 last_update_login = p_sys_param_def_rec.last_update_login,
137 name = p_sys_param_def_rec.name,
138 description = p_sys_param_def_rec.description,
139 source_lang = userenv('LANG')
140 WHERE parameter_code = p_sys_param_def_rec.parameter_code
141 AND userenv('LANG') in (language, source_lang);
142 IF (sql%notfound) THEN
143 RAISE no_data_found;
144 END IF;
145 END Update_Row;
146
147 -- Start of comments
148 -- API name : Lock_Row
149 -- Type : Public
150 -- Description : Lock Parameter definition record in oe_sys_parameter_def_b and oe_sys_parameter_def_tl table
151 -- Parameters :
152 -- IN : p_sys_param_def_rec IN
153 -- OE_PARAMETERS_DEF_UTIL.sys_param_def_rec_type Required
154 --
155 -- End of Comments
156 PROCEDURE Lock_Row(p_parameter_code IN VARCHAR2)
157 IS
158 CURSOR param_def IS
159 SELECT parameter_code,
160 category_code,
161 value_set_id,
162 open_orders_check_flag,
163 enabled_flag
164 FROM oe_sys_parameter_def_b
165 WHERE parameter_code = p_parameter_code
166 FOR UPDATE OF value_set_id NOWAIT;
167
168 CURSOR param_tl IS
169 SELECT name,
170 description,
171 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') baselang
172 FROM oe_sys_parameter_def_tl
173 WHERE parameter_code = p_parameter_code
174 AND userenv('LANG') IN (language, source_lang)
175 FOR UPDATE OF name NOWAIT;
176
177 l_recinfo param_def%rowtype;
178 l_found VARCHAR2(1) := 'N';
179 --
180 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
181 --
182 BEGIN
183 OPEN param_def;
184 FETCH param_def INTO l_recinfo;
185 IF (param_def%notfound) THEN
186 CLOSE param_def;
187 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
188 app_exception.raise_exception;
189 END IF;
190 CLOSE param_def;
191
192 FOR tlinfo IN param_tl LOOP
193 l_found := 'Y';
194 END LOOP;
195 IF l_found = 'N' THEN
196 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
197 app_exception.raise_exception;
198 END IF;
199
200 END Lock_Row;
201
202 FUNCTION Upload_Test(p_file_upb IN NUMBER
203 ,p_file_upd IN DATE
204 ,p_db_upb IN NUMBER
205 ,p_db_upd IN DATE)
206 RETURN BOOLEAN
207 IS
208 l_db_upb NUMBER;
209 l_file_upb NUMBER;
210 l_original_seed_data_window DATE;
211 l_retcode BOOLEAN;
212 BEGIN
213 -- Check file row for SEED/version
214 l_file_upb := p_file_upb;
215 IF ((l_file_upb in (0,1))
216 AND (p_file_upd = TRUNC(p_file_upd))
217 AND (p_file_upd < sysdate - .1)) THEN
218 l_file_upb := 2;
219 END IF;
220
221 -- Check db row for SEED/version.
222 -- NOTE: if db ludate < seed_data_window, then consider this to be
223 -- original seed data, never touched by FNDLOAD, even if it doesn't
224 -- have a timestamp.
225 l_db_upb := p_db_upb;
226 l_original_seed_data_window := to_date('01/01/1990','MM/DD/YYYY');
227 IF ((l_db_upb in (0,1))
228 AND (p_db_upd = trunc(p_db_upd))
229 AND (p_db_upd > l_original_seed_data_window)) THEN
230 l_db_upb := 2;
231 END IF;
232
233 IF (l_file_upb in (0,1)) THEN
234 -- File owner is old FNDLOAD.
235 IF (l_db_upb in (0,1)) THEN
236 -- DB owner is also old FNDLOAD.
237 -- Over-write, but only if file ludate >= db ludate.
238 IF (p_file_upd >= p_db_upd) THEN
239 l_retcode := TRUE;
240 ELSE
241 l_retcode := FALSE;
242 END IF;
243 ELSE
244 l_retcode := FALSE;
245 END IF;
246 ELSIF (l_file_upb = 2) THEN
247 -- File owner is new FNDLOAD. Over-write if:
248 -- 1. Db owner is old FNDLOAD, or
249 -- 2. Db owner is new FNDLOAD, and file date >= db date
250 IF ((l_db_upb in (0,1))
251 OR ((l_db_upb = 2) AND (p_file_upd >= p_db_upd))) THEN
252 l_retcode := TRUE;
253 ELSE
254 l_retcode := FALSE;
255 END IF;
256 ELSE
257 -- File owner is USER. Over-write if:
258 -- 1. Db owner is old or new FNDLOAD, or
259 -- 2. File date >= db date
260 IF ((l_db_upb in (0,1,2)) OR
261 (p_file_upd >= p_db_upd)) THEN
262 l_retcode := TRUE;
263 ELSE
264 l_retcode := FALSE;
265 END IF;
266 END IF;
267 IF (l_retcode = FALSE) THEN
268 fnd_message.set_name('FND', 'FNDLOAD_CUSTOMIZED');
269 END IF;
270 RETURN l_retcode;
271 END Upload_Test;
272
273 PROCEDURE Translate_Row(p_parameter_code IN VARCHAR2,
274 p_name IN VARCHAR2,
275 p_description IN VARCHAR2,
276 p_updated_by IN NUMBER,
277 p_update_login IN NUMBER,
278 p_custom_mode in varchar2 default null)
279 IS
280 l_last_upd_by NUMBER;
281 l_last_upd_dt DATE;
282 BEGIN
283 SELECT last_updated_by,last_update_date
284 INTO l_last_upd_by, l_last_upd_dt
285 FROM oe_sys_parameter_def_tl
286 WHERE parameter_code = p_parameter_code
287 AND LANGUAGE = userenv('LANG');
288
289 /*IF upload_test(p_file_upb => p_updated_by
290 ,p_file_upd => sysdate
291 ,p_db_upb => l_last_upd_by
292 ,p_db_upd => l_last_upd_dt) THEN*/
293 IF fnd_load_util.upload_test(p_file_id =>p_updated_by,
294 p_file_lud => sysdate,
295 p_db_id=> l_last_upd_by,
296 p_db_lud => l_last_upd_dt,
297 p_custom_mode=>p_custom_mode) then
298 UPDATE oe_sys_parameter_def_tl
299 SET name = p_name,
300 description = p_description,
301 last_update_date = sysdate,
302 last_updated_by = p_updated_by,
303 last_update_login = p_update_login,
304 source_lang = userenv('LANG')
305 WHERE parameter_code = p_parameter_code
306 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
307 END IF;
308 EXCEPTION
309 WHEN OTHERS THEN
310 NULL;
311 END Translate_Row;
312
313 PROCEDURE Load_Row(p_parameter_code IN VARCHAR2,
314 p_name IN VARCHAR2,
315 p_description IN VARCHAR2,
316 p_updated_by IN NUMBER,
317 p_update_login IN NUMBER,
318 p_category_code IN VARCHAR2,
319 p_value_set IN VARCHAR2,
320 p_open_orders_check_flag IN VARCHAR2,
321 p_enabled_flag IN VARCHAR2,
322 p_seeded_flag IN VARCHAR2,
323 p_custom_mode IN VARCHAR2 default null) --Seed data changes)
324 IS
325 CURSOR get_value_set_id IS
326 SELECT flex_value_set_id
327 FROM fnd_flex_value_sets
328 WHERE flex_value_set_name= p_value_set;
329
330 CURSOR get_category IS
331 SELECT lookup_code
332 FROM oe_lookups
333 WHERE lookup_type ='OM_PARAMETER_CATEGORY'
334 AND lookup_code =p_category_code;
335
336 l_sys_param_def_rec OE_PARAMETERS_DEF_UTIL.sys_param_def_rec_type;
337 l_value_set_id NUMBER;
338 l_category VARCHAR2(30);
339 l_last_upd_by NUMBER;
340 l_last_upd_dt DATE;
341 INVALID_CATEGORY EXCEPTION;
342 BEGIN
343
344 IF p_value_set IS NULL THEN
345 l_value_set_id := NULL;
346 ELSE
347 OPEN get_value_set_id;
348 FETCH get_value_set_id INTO l_value_set_id;
349 CLOSE get_value_set_id;
350 IF l_value_set_id IS NULL THEN
351 RAISE INVALID_CATEGORY;
352 END IF;
353 END IF;
354 OPEN get_category;
355 FETCH get_category INTO l_category;
356 CLOSE get_category;
357 IF l_category IS NULL THEN
358 RAISE INVALID_CATEGORY;
359 END IF;
360 l_sys_param_def_rec.parameter_code := p_parameter_code;
361 l_sys_param_def_rec.Last_update_date := sysdate;
362 l_sys_param_def_rec.last_updated_by := p_updated_by;
363 l_sys_param_def_rec.last_update_login := 0;
364 l_sys_param_def_rec.Creation_date := sysdate;
365 l_sys_param_def_rec.Created_By := p_updated_by;
366 l_sys_param_def_rec.Name := p_name;
367 l_sys_param_def_rec.Description := p_description;
368 l_sys_param_def_rec.category_code := p_category_code;
369 l_sys_param_def_rec.value_set_id := l_value_set_id;
370 l_sys_param_def_rec.open_orders_check_flag := p_open_orders_check_flag;
371 l_sys_param_def_rec.enabled_flag := p_enabled_flag;
372 l_sys_param_def_rec.seeded_flag := p_seeded_flag;
373
374 SELECT last_updated_by,last_update_date
375 INTO l_last_upd_by, l_last_upd_dt
376 FROM oe_sys_parameter_def_vl
377 WHERE parameter_code = p_parameter_code;
378
379 /*IF upload_test(p_file_upb => p_updated_by
380 ,p_file_upd => sysdate
381 ,p_db_upb => l_last_upd_by
382 ,p_db_upd => l_last_upd_dt) THEN*/
383 IF fnd_load_util.upload_test(p_file_id =>l_sys_param_def_rec.last_updated_by
384 ,p_file_lud => sysdate
385 ,p_db_id => l_last_upd_by
386 ,p_db_lud => l_last_upd_dt
387 ,p_custom_mode=>p_custom_mode) THEN
388 Update_Row(p_sys_param_def_rec => l_sys_param_def_rec);
389 END IF;
390
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 BEGIN
394 Insert_Row(p_sys_param_def_rec => l_sys_param_def_rec);
395 EXCEPTION
396 WHEN OTHERS THEN
397 RAISE;
398 END;
399 WHEN INVALID_CATEGORY THEN
400 NULL;
401 END Load_Row;
402
403 PROCEDURE Add_Language
404 IS
405 BEGIN
406 DELETE FROM oe_sys_parameter_def_tl t
407 WHERE NOT EXISTS
408 (SELECT null
409 FROM oe_sys_parameter_def_b b
410 where b.parameter_code = t.parameter_code);
411
412 UPDATE oe_sys_parameter_def_tl t
413 SET
414 (
415 name,
416 description
417 ) = (
418 SELECT
419 b.name,
420 b.description
421 FROM oe_sys_parameter_def_tl b
422 WHERE b.parameter_code = t.parameter_code
423 AND b.language = t.source_lang
424 )
425 where
426 (
427 t.parameter_code,
428 t.language
429 ) IN (
430 SELECT
431 subt.parameter_code,
432 subt.language
433 FROM oe_sys_parameter_def_tl subb,
434 oe_sys_parameter_def_tl subt
435 WHERE subb.parameter_code = subt.parameter_code
436 AND subb.language = subt.source_lang
437 AND(subb.name <> subt.name
438 OR subb.DESCRIPTION <> subt.description
439 OR (subb.description IS null
440 AND subt.description IS NOT null)
441 OR (subb.description IS NOT null
442 AND subt.description IS null)
443 )
444 );
445
446 INSERT INTO oe_sys_parameter_def_tl
447 (
448 parameter_code,
449 creation_date,
450 created_by,
451 last_update_date,
452 last_updated_by,
453 last_update_login,
454 language,
455 source_lang,
456 name,
457 description
458 )
459 SELECT
460 b.parameter_code,
461 b.creation_date,
462 b.created_by,
463 b.last_update_date,
464 b.last_updated_by,
465 b.last_update_login,
466 l.language_code,
467 b.source_lang,
468 b.name,
469 b.description
470 FROM oe_sys_parameter_def_tl b, fnd_languages l
471 WHERE l.installed_flag IN ('I', 'B')
472 AND b.language = USERENV('LANG')
473 AND NOT EXISTS
474 ( SELECT null
475 FROM oe_sys_parameter_def_tl t
476 WHERE t.parameter_code = b.parameter_code
477 AND t.language = l.language_code);
478 END Add_Language;
479
480 END OE_PARAMETERS_DEF_UTIL;
[Home] [Help]