[Home] [Help]
PACKAGE BODY: APPS.FND_DEBUG_OPTION_VALUES_PKG
Source
1 package body FND_DEBUG_OPTION_VALUES_PKG as
2 -- $Header: AFOAMDVB.pls 120.1 2005/07/02 03:02:55 appldev noship $
3
4 procedure LOAD_ROW (
5 X_DEBUG_OPTION_NAME in VARCHAR2,
6 X_DEBUG_OPTION_VALUE in VARCHAR2,
7 X_TRACE_FILE_ROUTINE in VARCHAR2,
8 X_TRACE_FILE_NODE in VARCHAR2,
9 X_ENABLE_ROUTINE in VARCHAR2,
10 X_DISABLE_ROUTINE in VARCHAR2,
11 X_IS_FILE_TOKEN in VARCHAR2,
12 X_DESCRIPTION in VARCHAR2,
13 X_OWNER in VARCHAR2
14
15
16 ) is
17 begin
18 FND_DEBUG_OPTION_VALUES_PKG.LOAD_ROW (
19 X_DEBUG_OPTION_NAME => X_DEBUG_OPTION_NAME,
20 X_DEBUG_OPTION_VALUE => X_DEBUG_OPTION_VALUE,
21 X_TRACE_FILE_ROUTINE => X_TRACE_FILE_ROUTINE,
22 X_TRACE_FILE_NODE =>X_TRACE_FILE_NODE,
23 X_ENABLE_ROUTINE =>X_ENABLE_ROUTINE,
24 X_DISABLE_ROUTINE=> X_DISABLE_ROUTINE,
25 X_IS_FILE_TOKEN => X_IS_FILE_TOKEN,
26 X_DESCRIPTION=>X_DESCRIPTION,
27 X_OWNER=>X_OWNER,
28 x_custom_mode => '',
29 x_last_update_date => '');
30
31 end;
32
33 procedure LOAD_ROW (
34 X_DEBUG_OPTION_NAME in VARCHAR2,
35 X_DEBUG_OPTION_VALUE in VARCHAR2,
36 X_TRACE_FILE_ROUTINE in VARCHAR2,
37 X_TRACE_FILE_NODE in VARCHAR2,
38 X_ENABLE_ROUTINE in VARCHAR2,
39 X_DISABLE_ROUTINE in VARCHAR2,
40 X_IS_FILE_TOKEN in VARCHAR2,
41 X_DESCRIPTION in VARCHAR2,
42 x_custom_mode in varchar2,
43 x_last_update_date in varchar2,
44 X_OWNER in VARCHAR2)is
45
46
47 f_luby number; -- entity owner in file
48 f_ludate date; -- entity update date in file
49 db_luby number:=0; -- entity owner in db
50 db_ludate date:=null; -- entity update date in db
51 row_id varchar2(64);
52
53 begin
54
55
56
57 -- Translate owner to file_last_updated_by
58 f_luby := fnd_load_util.owner_id(x_owner);
59
60 -- Translate char last_update_date to date
61 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
62
63 begin
64
65
66
67
68
69 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
70 db_ludate, X_CUSTOM_MODE)) then
71
72
73 FND_DEBUG_OPTION_VALUES_PKG.UPDATE_ROW (
74 X_DEBUG_OPTION_NAME => X_DEBUG_OPTION_NAME,
75 X_DEBUG_OPTION_VALUE => X_DEBUG_OPTION_VALUE,
76 X_TRACE_FILE_ROUTINE => X_TRACE_FILE_ROUTINE,
77 X_TRACE_FILE_NODE =>X_TRACE_FILE_NODE,
78 X_ENABLE_ROUTINE =>X_ENABLE_ROUTINE,
79 X_DISABLE_ROUTINE=> X_DISABLE_ROUTINE,
80 X_IS_FILE_TOKEN => X_IS_FILE_TOKEN,
81 X_DESCRIPTION=>X_DESCRIPTION,
82 X_LAST_UPDATE_DATE => f_ludate,
83 X_LAST_UPDATED_BY => f_luby,
84 X_LAST_UPDATE_LOGIN => 0
85 );
86
87
88 end if;
89 exception
90 when NO_DATA_FOUND then
91
92 FND_DEBUG_OPTION_VALUES_PKG.INSERT_ROW (
93 X_ROWID => row_id,
94 X_DEBUG_OPTION_NAME => X_DEBUG_OPTION_NAME,
95 X_DEBUG_OPTION_VALUE => X_DEBUG_OPTION_VALUE,
96 X_TRACE_FILE_ROUTINE => X_TRACE_FILE_ROUTINE,
97 X_TRACE_FILE_NODE =>X_TRACE_FILE_NODE,
98 X_ENABLE_ROUTINE =>X_ENABLE_ROUTINE,
99 X_DISABLE_ROUTINE=> X_DISABLE_ROUTINE,
100 X_IS_FILE_TOKEN => X_IS_FILE_TOKEN,
101 X_DESCRIPTION=>X_DESCRIPTION,
102 X_CREATION_DATE => f_ludate,
103 X_CREATED_BY => f_luby,
104 X_LAST_UPDATE_DATE => f_ludate,
105 X_LAST_UPDATED_BY => f_luby,
106 X_LAST_UPDATE_LOGIN => 0 );
107 end;
108 end LOAD_ROW;
109
110 procedure INSERT_ROW (
111 X_ROWID in out nocopy VARCHAR2,
112 X_DEBUG_OPTION_NAME in VARCHAR2,
113 X_DEBUG_OPTION_VALUE in VARCHAR2,
114 X_TRACE_FILE_ROUTINE in VARCHAR2,
115 X_TRACE_FILE_NODE in VARCHAR2,
116 X_ENABLE_ROUTINE in VARCHAR2,
117 X_DISABLE_ROUTINE in VARCHAR2,
118 X_IS_FILE_TOKEN in VARCHAR2,
119 X_DESCRIPTION in VARCHAR2,
120 X_CREATION_DATE in DATE,
121 X_CREATED_BY in NUMBER,
122 X_LAST_UPDATE_DATE in DATE,
123 X_LAST_UPDATED_BY in NUMBER,
124 X_LAST_UPDATE_LOGIN in NUMBER
125 ) is
126 cursor C is select ROWID from FND_DEBUG_OPTION_VALUES
127 where DEBUG_OPTION_NAME = X_DEBUG_OPTION_NAME
128 and DEBUG_OPTION_VALUE = X_DEBUG_OPTION_VALUE
129 ;
130 begin
131
132
133
134 insert into FND_DEBUG_OPTION_VALUES (
135 TRACE_FILE_ROUTINE,
136 TRACE_FILE_NODE,
137 DEBUG_OPTION_NAME,
138 DEBUG_OPTION_VALUE,
139 ENABLE_ROUTINE,
140 DISABLE_ROUTINE,
141 IS_FILE_TOKEN,
142 CREATION_DATE,
143 CREATED_BY,
144 LAST_UPDATE_DATE,
145 LAST_UPDATED_BY,
146 LAST_UPDATE_LOGIN
147 ) values (
148 X_TRACE_FILE_ROUTINE,
149 X_TRACE_FILE_NODE,
150 X_DEBUG_OPTION_NAME,
151 X_DEBUG_OPTION_VALUE,
152 X_ENABLE_ROUTINE,
153 X_DISABLE_ROUTINE,
154 X_IS_FILE_TOKEN,
155 X_CREATION_DATE,
156 X_CREATED_BY,
157 X_LAST_UPDATE_DATE,
158 X_LAST_UPDATED_BY,
159 X_LAST_UPDATE_LOGIN
160 );
161
162 insert into FND_DEBUG_OPTION_VALUES_TL (
163 DEBUG_OPTION_NAME,
164 DEBUG_OPTION_VALUE,
165 DESCRIPTION,
166 CREATION_DATE,
167 CREATED_BY,
168 LAST_UPDATE_DATE,
169 LAST_UPDATED_BY,
170 LAST_UPDATE_LOGIN,
171 LANGUAGE,
172 SOURCE_LANG
173 ) select
174 X_DEBUG_OPTION_NAME,
175 X_DEBUG_OPTION_VALUE,
176 X_DESCRIPTION,
177 X_CREATION_DATE,
178 X_CREATED_BY,
179 X_LAST_UPDATE_DATE,
180 X_LAST_UPDATED_BY,
181 X_LAST_UPDATE_LOGIN,
182 L.LANGUAGE_CODE,
183 userenv('LANG')
184 from FND_LANGUAGES L
185 where L.INSTALLED_FLAG in ('I', 'B')
186 and not exists
187 (select NULL
188 from FND_DEBUG_OPTION_VALUES_TL T
189 where T.DEBUG_OPTION_NAME = X_DEBUG_OPTION_NAME
190 and T.DEBUG_OPTION_VALUE = X_DEBUG_OPTION_VALUE
191 and T.LANGUAGE = L.LANGUAGE_CODE);
192
193 open c;
194 fetch c into X_ROWID;
195 if (c%notfound) then
196 close c;
197 raise no_data_found;
198 end if;
199 close c;
200
201 end INSERT_ROW;
202
203 procedure LOCK_ROW (
204 X_DEBUG_OPTION_NAME in VARCHAR2,
205 X_DEBUG_OPTION_VALUE in VARCHAR2,
206 X_TRACE_FILE_ROUTINE in VARCHAR2,
207 X_TRACE_FILE_NODE in VARCHAR2,
208 X_ENABLE_ROUTINE in VARCHAR2,
209 X_DISABLE_ROUTINE in VARCHAR2,
210 X_IS_FILE_TOKEN in VARCHAR2,
211 X_DESCRIPTION in VARCHAR2
212 ) is
213 cursor c is select
214 TRACE_FILE_ROUTINE,
215 TRACE_FILE_NODE,
216 ENABLE_ROUTINE,
217 DISABLE_ROUTINE,
218 IS_FILE_TOKEN
219 from FND_DEBUG_OPTION_VALUES
220 where DEBUG_OPTION_NAME = X_DEBUG_OPTION_NAME
221 and DEBUG_OPTION_VALUE = X_DEBUG_OPTION_VALUE
222 for update of DEBUG_OPTION_NAME nowait;
223 recinfo c%rowtype;
224
225 cursor c1 is select
226 DESCRIPTION,
227 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
228 from FND_DEBUG_OPTION_VALUES_TL
229 where DEBUG_OPTION_NAME = X_DEBUG_OPTION_NAME
230 and DEBUG_OPTION_VALUE = X_DEBUG_OPTION_VALUE
231 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
232 for update of DEBUG_OPTION_NAME nowait;
233 begin
234 open c;
235 fetch c into recinfo;
236 if (c%notfound) then
237 close c;
238 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
239 app_exception.raise_exception;
240 end if;
241 close c;
242 if ( ((recinfo.TRACE_FILE_ROUTINE = X_TRACE_FILE_ROUTINE)
243 OR ((recinfo.TRACE_FILE_ROUTINE is null) AND (X_TRACE_FILE_ROUTINE is null)))
244 AND ((recinfo.TRACE_FILE_NODE = X_TRACE_FILE_NODE)
245 OR ((recinfo.TRACE_FILE_NODE is null) AND (X_TRACE_FILE_NODE is null)))
246 AND ((recinfo.ENABLE_ROUTINE = X_ENABLE_ROUTINE)
247 OR ((recinfo.ENABLE_ROUTINE is null) AND (X_ENABLE_ROUTINE is null)))
248 AND ((recinfo.DISABLE_ROUTINE = X_DISABLE_ROUTINE)
249 OR ((recinfo.DISABLE_ROUTINE is null) AND (X_DISABLE_ROUTINE is null)))
250 AND ((recinfo.IS_FILE_TOKEN = X_IS_FILE_TOKEN)
251 OR ((recinfo.IS_FILE_TOKEN is null) AND (X_IS_FILE_TOKEN is null)))
252 ) then
253 null;
254 else
255 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
256 app_exception.raise_exception;
257 end if;
258
259 for tlinfo in c1 loop
260 if (tlinfo.BASELANG = 'Y') then
261 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
262 ) then
263 null;
264 else
265 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
266 app_exception.raise_exception;
267 end if;
268 end if;
269 end loop;
270 return;
271 end LOCK_ROW;
272
273 procedure UPDATE_ROW (
274 X_DEBUG_OPTION_NAME in VARCHAR2,
275 X_DEBUG_OPTION_VALUE in VARCHAR2,
276 X_TRACE_FILE_ROUTINE in VARCHAR2,
277 X_TRACE_FILE_NODE in VARCHAR2,
278 X_ENABLE_ROUTINE in VARCHAR2,
279 X_DISABLE_ROUTINE in VARCHAR2,
280 X_IS_FILE_TOKEN in VARCHAR2,
281 X_DESCRIPTION in VARCHAR2,
282 X_LAST_UPDATE_DATE in DATE,
283 X_LAST_UPDATED_BY in NUMBER,
284 X_LAST_UPDATE_LOGIN in NUMBER
285 ) is
286 begin
287 update FND_DEBUG_OPTION_VALUES set
288 TRACE_FILE_ROUTINE = X_TRACE_FILE_ROUTINE,
289 TRACE_FILE_NODE = X_TRACE_FILE_NODE,
290 ENABLE_ROUTINE = X_ENABLE_ROUTINE,
291 DISABLE_ROUTINE = X_DISABLE_ROUTINE,
292 IS_FILE_TOKEN = X_IS_FILE_TOKEN,
293 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
294 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
295 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
296 where DEBUG_OPTION_NAME = X_DEBUG_OPTION_NAME
297 and DEBUG_OPTION_VALUE = X_DEBUG_OPTION_VALUE;
298
299 if (sql%notfound) then
300 raise no_data_found;
301 end if;
302
303 update FND_DEBUG_OPTION_VALUES_TL set
304 DESCRIPTION = X_DESCRIPTION,
305 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
306 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
307 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
308 SOURCE_LANG = userenv('LANG')
309 where DEBUG_OPTION_NAME = X_DEBUG_OPTION_NAME
310 and DEBUG_OPTION_VALUE = X_DEBUG_OPTION_VALUE
311 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
312
313 if (sql%notfound) then
314 raise no_data_found;
315 end if;
316 end UPDATE_ROW;
317
318 procedure DELETE_ROW (
319 X_DEBUG_OPTION_NAME in VARCHAR2,
320 X_DEBUG_OPTION_VALUE in VARCHAR2
321 ) is
322 begin
323 delete from FND_DEBUG_OPTION_VALUES_TL
324 where DEBUG_OPTION_NAME = X_DEBUG_OPTION_NAME
325 and DEBUG_OPTION_VALUE = X_DEBUG_OPTION_VALUE;
326
327 if (sql%notfound) then
328 raise no_data_found;
329 end if;
330
331 delete from FND_DEBUG_OPTION_VALUES
332 where DEBUG_OPTION_NAME = X_DEBUG_OPTION_NAME
333 and DEBUG_OPTION_VALUE = X_DEBUG_OPTION_VALUE;
334
335 if (sql%notfound) then
336 raise no_data_found;
337 end if;
338 end DELETE_ROW;
339
340 procedure ADD_LANGUAGE
341 is
342 begin
343 delete from FND_DEBUG_OPTION_VALUES_TL T
344 where not exists
345 (select NULL
346 from FND_DEBUG_OPTION_VALUES B
347 where B.DEBUG_OPTION_NAME = T.DEBUG_OPTION_NAME
348 and B.DEBUG_OPTION_VALUE = T.DEBUG_OPTION_VALUE
349 );
350
351 update FND_DEBUG_OPTION_VALUES_TL T set (
352 DESCRIPTION
353 ) = (select
354 B.DESCRIPTION
355 from FND_DEBUG_OPTION_VALUES_TL B
356 where B.DEBUG_OPTION_NAME = T.DEBUG_OPTION_NAME
357 and B.DEBUG_OPTION_VALUE = T.DEBUG_OPTION_VALUE
358 and B.LANGUAGE = T.SOURCE_LANG)
359 where (
360 T.DEBUG_OPTION_NAME,
361 T.DEBUG_OPTION_VALUE,
362 T.LANGUAGE
363 ) in (select
364 SUBT.DEBUG_OPTION_NAME,
365 SUBT.DEBUG_OPTION_VALUE,
366 SUBT.LANGUAGE
367 from FND_DEBUG_OPTION_VALUES_TL SUBB, FND_DEBUG_OPTION_VALUES_TL SUBT
368 where SUBB.DEBUG_OPTION_NAME = SUBT.DEBUG_OPTION_NAME
369 and SUBB.DEBUG_OPTION_VALUE = SUBT.DEBUG_OPTION_VALUE
370 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
371 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
372 ));
373
374 insert into FND_DEBUG_OPTION_VALUES_TL (
375 DEBUG_OPTION_NAME,
376 DEBUG_OPTION_VALUE,
377 DESCRIPTION,
378 CREATION_DATE,
379 CREATED_BY,
380 LAST_UPDATE_DATE,
381 LAST_UPDATED_BY,
382 LAST_UPDATE_LOGIN,
383 LANGUAGE,
384 SOURCE_LANG
385 ) select /*+ ORDERED */
386 B.DEBUG_OPTION_NAME,
387 B.DEBUG_OPTION_VALUE,
388 B.DESCRIPTION,
389 B.CREATION_DATE,
390 B.CREATED_BY,
391 B.LAST_UPDATE_DATE,
392 B.LAST_UPDATED_BY,
393 B.LAST_UPDATE_LOGIN,
394 L.LANGUAGE_CODE,
395 B.SOURCE_LANG
396 from FND_DEBUG_OPTION_VALUES_TL B, FND_LANGUAGES L
397 where L.INSTALLED_FLAG in ('I', 'B')
398 and B.LANGUAGE = userenv('LANG')
399 and not exists
400 (select NULL
401 from FND_DEBUG_OPTION_VALUES_TL T
402 where T.DEBUG_OPTION_NAME = B.DEBUG_OPTION_NAME
403 and T.DEBUG_OPTION_VALUE = B.DEBUG_OPTION_VALUE
404 and T.LANGUAGE = L.LANGUAGE_CODE);
405 end ADD_LANGUAGE;
406
407
408
409
410
411
412
413
414 procedure TRANSLATE_ROW (
415 X_DEBUG_OPTION_NAME in VARCHAR2,
416 X_DEBUG_OPTION_VALUE in VARCHAR2,
417 X_OWNER in VARCHAR2,
418 X_DESCRIPTION in VARCHAR2) is
419 begin
420
421 FND_DEBUG_OPTION_VALUES_PKG.translate_row(
422 X_DEBUG_OPTION_NAME => X_DEBUG_OPTION_NAME,
423 X_DEBUG_OPTION_VALUE => X_DEBUG_OPTION_VALUE,
424 X_OWNER=>X_OWNER,
425 X_DESCRIPTION=>X_DESCRIPTION,
426 x_custom_mode => '',
427 x_last_update_date => '');
428
429 end TRANSLATE_ROW;
430
431
432
433
434
435 procedure TRANSLATE_ROW (
436 X_DEBUG_OPTION_NAME in VARCHAR2,
437 X_DEBUG_OPTION_VALUE in VARCHAR2,
438 X_OWNER in VARCHAR2,
439 X_DESCRIPTION in VARCHAR2,
440 X_CUSTOM_MODE in VARCHAR2,
441 X_LAST_UPDATE_DATE in VARCHAR2) is
442
443 f_luby number; -- entity owner in file
444 f_ludate date; -- entity update date in file
445 db_luby number:=0; -- entity owner in db
446 db_ludate date:=null; -- entity update date in db
447
448 begin
449
450 -- Translate owner to file_last_updated_by
451 f_luby := fnd_load_util.owner_id(x_owner);
452
453 -- Translate char last_update_date to date
454 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
455
456 begin
457
458 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
459 db_ludate, X_CUSTOM_MODE)) then
460
461 update fnd_debug_option_values_tl set
462 DESCRIPTION=nvl(X_DESCRIPTION,DESCRIPTION),
463 SOURCE_LANG=userenv('LANG'),
464 last_update_date = f_ludate,
465 last_updated_by = f_luby,
466 last_update_login = 0
467 where debug_option_name=X_DEBUG_OPTION_NAME
468 and debug_option_value=X_DEBUG_OPTION_VALUE
469 and userenv('LANG') in (language, source_lang);
470
471 end if;
472 exception
473 when no_data_found then
474 null;
475 end;
476
477 end TRANSLATE_ROW;
478
479
480 end FND_DEBUG_OPTION_VALUES_PKG;