[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_METVAL_PKG
Source
1 PACKAGE BODY fnd_oam_metval_pkg AS
2 /* $Header: AFOAMMTB.pls 120.2 2005/10/20 12:29:41 ilawler noship $ */
3
4 -- PRIVATE -----------------
5
6 --
7 -- To update only those columns that are non-customizable by the
8 -- user
9 --
10 procedure UPDATE_ROW_INTERNAL (
11 X_METRIC_SHORT_NAME in VARCHAR2,
12 X_GROUP_ID in NUMBER,
13 X_SEQUENCE in NUMBER,
14 X_NODE_NAME in VARCHAR2,
15 X_METRIC_TYPE in VARCHAR2,
16 X_IS_SUPPORTED in VARCHAR2,
17 X_METRIC_DISPLAY_NAME in VARCHAR2,
18 X_DESCRIPTION in VARCHAR2
19 ) is
20 begin
21 update FND_OAM_METVAL set
22 GROUP_ID = X_GROUP_ID,
23 SEQUENCE = X_SEQUENCE,
24 METRIC_TYPE = X_METRIC_TYPE,
25 IS_SUPPORTED = nvl(X_IS_SUPPORTED, 'Y')
26 where METRIC_SHORT_NAME = X_METRIC_SHORT_NAME
27 and (X_NODE_NAME is null or
28 (X_NODE_NAME is not null and NODE_NAME = X_NODE_NAME));
29
30 if (sql%notfound) then
31 raise no_data_found;
32 end if;
33
34 update FND_OAM_METS_TL set
35 METRIC_DISPLAY_NAME = X_METRIC_DISPLAY_NAME,
36 DESCRIPTION = X_DESCRIPTION,
37 SOURCE_LANG = userenv('LANG')
38 where METRIC_SHORT_NAME = X_METRIC_SHORT_NAME
39 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
40
41 if (sql%notfound) then
42 raise no_data_found;
43 end if;
44 end UPDATE_ROW_INTERNAL;
45
46 procedure LOAD_ROW (
47 X_METRIC_SHORT_NAME in VARCHAR2,
48 X_METRIC_VALUE in VARCHAR2,
49 X_STATUS_CODE in VARCHAR2,
50 X_GROUP_ID in VARCHAR2,
51 X_SEQUENCE in VARCHAR2,
52 X_NODE_NAME in VARCHAR2,
53 X_METRIC_TYPE in VARCHAR2,
54 X_THRESHOLD_OPERATOR in VARCHAR2,
55 X_THRESHOLD_VALUE in VARCHAR2,
56 X_ALERT_ENABLED_FLAG in VARCHAR2,
57 X_COLLECTION_ENABLED_FLAG in VARCHAR2,
58 X_LAST_COLLECTED_DATE in VARCHAR2,
59 X_IS_SUPPORTED in VARCHAR2,
60 X_IS_CUSTOMIZED in VARCHAR2,
61 X_INTERVAL_COUNTER in VARCHAR2,
62 X_OWNER in VARCHAR2,
63 X_METRIC_DISPLAY_NAME in VARCHAR2,
64 X_DESCRIPTION in VARCHAR2) is
65 begin
66
67 fnd_oam_metval_pkg.LOAD_ROW (
68 X_METRIC_SHORT_NAME => X_METRIC_SHORT_NAME,
69 X_METRIC_VALUE => X_METRIC_VALUE,
70 X_STATUS_CODE => X_STATUS_CODE,
71 X_GROUP_ID => X_GROUP_ID,
72 X_SEQUENCE => X_SEQUENCE,
73 X_NODE_NAME => X_NODE_NAME,
74 X_METRIC_TYPE => X_METRIC_TYPE,
75 X_THRESHOLD_OPERATOR => X_THRESHOLD_OPERATOR,
76 X_THRESHOLD_VALUE => X_THRESHOLD_VALUE,
77 X_ALERT_ENABLED_FLAG => X_ALERT_ENABLED_FLAG,
78 X_COLLECTION_ENABLED_FLAG => X_COLLECTION_ENABLED_FLAG,
79 X_LAST_COLLECTED_DATE => X_LAST_COLLECTED_DATE,
80 X_IS_SUPPORTED => X_IS_SUPPORTED,
81 X_IS_CUSTOMIZED => X_IS_CUSTOMIZED,
82 X_INTERVAL_COUNTER => X_INTERVAL_COUNTER,
83 X_OWNER => X_OWNER,
84 X_METRIC_DISPLAY_NAME => X_METRIC_DISPLAY_NAME,
85 X_DESCRIPTION => X_DESCRIPTION,
86 x_custom_mode => '',
87 x_last_update_date => '');
88
89 end LOAD_ROW;
90
91 procedure LOAD_ROW (
92 X_METRIC_SHORT_NAME in VARCHAR2,
93 X_METRIC_VALUE in VARCHAR2,
94 X_STATUS_CODE in VARCHAR2,
95 X_GROUP_ID in VARCHAR2,
96 X_SEQUENCE in VARCHAR2,
97 X_NODE_NAME in VARCHAR2,
98 X_METRIC_TYPE in VARCHAR2,
99 X_THRESHOLD_OPERATOR in VARCHAR2,
100 X_THRESHOLD_VALUE in VARCHAR2,
101 X_ALERT_ENABLED_FLAG in VARCHAR2,
102 X_COLLECTION_ENABLED_FLAG in VARCHAR2,
103 X_LAST_COLLECTED_DATE in VARCHAR2,
104 X_IS_SUPPORTED in VARCHAR2,
105 X_IS_CUSTOMIZED in VARCHAR2,
106 X_INTERVAL_COUNTER in VARCHAR2,
107 X_OWNER in VARCHAR2,
108 X_METRIC_DISPLAY_NAME in VARCHAR2,
109 X_DESCRIPTION in VARCHAR2,
110 x_custom_mode in varchar2,
111 x_last_update_date in varchar2) is
112
113 row_id varchar2(64);
114 f_luby number; -- entity owner in file
115 f_ludate date; -- entity update date in file
116 db_luby number; -- entity owner in db
117 db_ludate date; -- entity update date in db
118
119 -- columns that are user customizable
120 l_is_customized fnd_oam_metval.is_customized%TYPE;
121 l_metric_value fnd_oam_metval.metric_value%TYPE;
122 l_status_code fnd_oam_metval.status_code%TYPE;
123 l_threshold_operator fnd_oam_metval.threshold_operator%TYPE;
124 l_threshold_value fnd_oam_metval.threshold_value%TYPE;
125 l_alert_enabled_flag fnd_oam_metval.alert_enabled_flag%TYPE;
126 l_collection_enabled_flag fnd_oam_metval.collection_enabled_flag%TYPE;
127 l_last_collected_date fnd_oam_metval.last_collected_date%TYPE;
128 l_interval_counter fnd_oam_metval.interval_counter%TYPE;
129 begin
130 -- Translate owner to file_last_updated_by
131 f_luby := fnd_load_util.owner_id(x_owner);
132
133 -- Translate char last_update_date to date
134 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
135
136 begin
137 -- We need to distinguish between the case where node name is null
138 -- or not
139 -- because, metric_short_name and node_name will uniquely indentify a
140 -- particular metric (and node_name is allowed to be null).
141 select LAST_UPDATED_BY, LAST_UPDATE_DATE, is_customized,
142 metric_value, status_code, threshold_operator,
143 threshold_value, alert_enabled_flag, collection_enabled_flag,
144 last_collected_date, interval_counter
145 into db_luby, db_ludate, l_is_customized,
146 l_metric_value, l_status_code, l_threshold_operator,
147 l_threshold_value, l_alert_enabled_flag, l_collection_enabled_flag,
148 l_last_collected_date, l_interval_counter
149 from fnd_oam_metval
150 where metric_short_name = X_METRIC_SHORT_NAME
151 and (X_NODE_NAME is null or
152 (X_NODE_NAME is not null and NODE_NAME = X_NODE_NAME));
153
154 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
155 db_ludate, X_CUSTOM_MODE)) then
156 --dbms_output.put_line('fnd_load_util.upload_test -> true');
157 begin
158 if (l_is_customized is null or l_is_customized = 'N') then
159 l_is_customized := X_IS_CUSTOMIZED;
160 l_threshold_operator := X_THRESHOLD_OPERATOR;
161 l_threshold_value := X_THRESHOLD_VALUE;
162 l_alert_enabled_flag := X_ALERT_ENABLED_FLAG;
163 l_collection_enabled_flag := X_COLLECTION_ENABLED_FLAG;
164 end if;
165 fnd_oam_metval_pkg.UPDATE_ROW (
166 X_METRIC_SHORT_NAME => X_METRIC_SHORT_NAME,
167 X_METRIC_VALUE => l_metric_value,
168 X_STATUS_CODE => to_number(l_status_code),
169 X_GROUP_ID => to_number(X_GROUP_ID),
170 X_SEQUENCE => to_number(X_SEQUENCE),
171 X_NODE_NAME => X_NODE_NAME,
172 X_METRIC_TYPE => X_METRIC_TYPE,
173 X_THRESHOLD_OPERATOR => l_threshold_operator,
174 X_THRESHOLD_VALUE => l_threshold_value,
175 X_ALERT_ENABLED_FLAG => l_alert_enabled_flag,
176 X_COLLECTION_ENABLED_FLAG => l_collection_enabled_flag,
177 X_LAST_COLLECTED_DATE => to_date(l_last_collected_date, 'YYYY/MM/DD'),
178 X_IS_SUPPORTED => X_IS_SUPPORTED,
179 X_IS_CUSTOMIZED => l_is_customized,
180 X_INTERVAL_COUNTER => to_number(l_interval_counter),
181 X_METRIC_DISPLAY_NAME => X_METRIC_DISPLAY_NAME,
182 X_DESCRIPTION => X_DESCRIPTION,
183 X_LAST_UPDATE_DATE => f_ludate,
184 X_LAST_UPDATED_BY => f_luby,
185 X_LAST_UPDATE_LOGIN => 0 );
186 end;
187 else
188 -- dbms_output.put_line('fnd_load_util.upload_test -> false');
189 -- fnd_load_util.upload_test returned false,
190 -- which means the metric has been customized by the user
191 -- but we would
192 -- still like to update some columns that are not editable
193 -- by the user.
194 begin
195 UPDATE_ROW_INTERNAL(
196 X_METRIC_SHORT_NAME => X_METRIC_SHORT_NAME,
197 X_GROUP_ID => to_number(X_GROUP_ID),
198 X_SEQUENCE => to_number(X_SEQUENCE),
199 X_NODE_NAME => X_NODE_NAME,
200 X_METRIC_TYPE => X_METRIC_TYPE,
201 X_IS_SUPPORTED => X_IS_SUPPORTED,
202 X_METRIC_DISPLAY_NAME => X_METRIC_DISPLAY_NAME,
203 X_DESCRIPTION => X_DESCRIPTION);
204 exception
205 when no_data_found then
206 -- somehow this row does not yet exist?
207 -- this should never happen
208 null;
209 end;
210 end if;
211 exception
212 when NO_DATA_FOUND then
213
214 fnd_oam_metval_pkg.INSERT_ROW (
215 X_ROWID => row_id,
216 X_METRIC_SHORT_NAME => X_METRIC_SHORT_NAME,
217 X_METRIC_VALUE => X_METRIC_VALUE,
218 X_STATUS_CODE => to_number(X_STATUS_CODE),
219 X_GROUP_ID => to_number(X_GROUP_ID),
220 X_SEQUENCE => to_number(X_SEQUENCE),
221 X_NODE_NAME => X_NODE_NAME,
222 X_METRIC_TYPE => X_METRIC_TYPE,
223 X_THRESHOLD_OPERATOR => X_THRESHOLD_OPERATOR,
224 X_THRESHOLD_VALUE => X_THRESHOLD_VALUE,
225 X_ALERT_ENABLED_FLAG => X_ALERT_ENABLED_FLAG,
226 X_COLLECTION_ENABLED_FLAG => X_COLLECTION_ENABLED_FLAG,
227 X_LAST_COLLECTED_DATE => to_date(X_LAST_COLLECTED_DATE, 'YYYY/MM/DD'),
228 X_IS_SUPPORTED => X_IS_SUPPORTED,
229 X_IS_CUSTOMIZED => X_IS_CUSTOMIZED,
230 X_INTERVAL_COUNTER => to_number(X_INTERVAL_COUNTER),
231 X_METRIC_DISPLAY_NAME => X_METRIC_DISPLAY_NAME,
232 X_DESCRIPTION => X_DESCRIPTION,
233 X_CREATION_DATE => f_ludate,
234 X_CREATED_BY => f_luby,
235 X_LAST_UPDATE_DATE => f_ludate,
236 X_LAST_UPDATED_BY => f_luby,
237 X_LAST_UPDATE_LOGIN => 0 );
238 end;
239 end LOAD_ROW;
240
241 procedure TRANSLATE_ROW (
242 X_METRIC_SHORT_NAME in VARCHAR2,
243 X_OWNER in VARCHAR2,
244 X_METRIC_DISPLAY_NAME in VARCHAR2,
245 X_DESCRIPTION in VARCHAR2) is
246 begin
247
248 fnd_oam_metval_pkg.translate_row(
249 x_metric_short_name => x_metric_short_name,
250 x_owner => x_owner,
251 x_metric_display_name => x_metric_display_name,
252 x_description => x_description,
253 x_custom_mode => '',
254 x_last_update_date => '');
255
256 end TRANSLATE_ROW;
257
258
259 procedure TRANSLATE_ROW (
260 X_METRIC_SHORT_NAME in VARCHAR2,
261 X_OWNER in VARCHAR2,
262 X_METRIC_DISPLAY_NAME in VARCHAR2,
263 X_DESCRIPTION in VARCHAR2,
264 X_CUSTOM_MODE in VARCHAR2,
265 X_LAST_UPDATE_DATE in VARCHAR2) is
266
267 f_luby number; -- entity owner in file
268 f_ludate date; -- entity update date in file
269 db_luby number; -- entity owner in db
270 db_ludate date; -- entity update date in db
271
272 begin
273
274 -- Translate owner to file_last_updated_by
275 f_luby := fnd_load_util.owner_id(x_owner);
276
277 -- Translate char last_update_date to date
278 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
279
280 begin
281
282 select LAST_UPDATED_BY, LAST_UPDATE_DATE
283 into db_luby, db_ludate
284 from fnd_oam_mets_tl
285 where metric_short_name = X_METRIC_SHORT_NAME
286 and LANGUAGE = userenv('LANG');
287
288 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
289 db_ludate, X_CUSTOM_MODE)) then
290 update fnd_oam_mets_tl set
291 metric_display_name = nvl(X_METRIC_DISPLAY_NAME, metric_display_name),
292 description = nvl(X_DESCRIPTION, description),
293 source_lang = userenv('LANG'),
294 last_update_date = f_ludate,
295 last_updated_by = f_luby,
296 last_update_login = 0
297 where metric_short_name = X_METRIC_SHORT_NAME
298 and userenv('LANG') in (language, source_lang);
299 end if;
300 exception
301 when no_data_found then
302 null;
303 end;
304
305 end TRANSLATE_ROW;
306
307 procedure INSERT_ROW (
308 X_ROWID IN OUT NOCOPY VARCHAR2,
309 X_METRIC_SHORT_NAME in VARCHAR2,
310 X_METRIC_VALUE in VARCHAR2,
311 X_STATUS_CODE in NUMBER,
312 X_GROUP_ID in NUMBER,
313 X_SEQUENCE in VARCHAR2,
317 X_THRESHOLD_VALUE in VARCHAR2,
314 X_NODE_NAME in VARCHAR2,
315 X_METRIC_TYPE in VARCHAR2,
316 X_THRESHOLD_OPERATOR in VARCHAR2,
318 X_ALERT_ENABLED_FLAG in VARCHAR2,
319 X_COLLECTION_ENABLED_FLAG in VARCHAR2,
320 X_LAST_COLLECTED_DATE in DATE,
321 X_IS_SUPPORTED in VARCHAR2,
322 X_IS_CUSTOMIZED in VARCHAR2,
323 X_INTERVAL_COUNTER in NUMBER,
324 X_METRIC_DISPLAY_NAME in VARCHAR2,
325 X_DESCRIPTION in VARCHAR2,
326 X_CREATED_BY in NUMBER,
327 X_CREATION_DATE in DATE,
328 X_LAST_UPDATED_BY in NUMBER,
329 X_LAST_UPDATE_DATE in DATE,
330 X_LAST_UPDATE_LOGIN in NUMBER)
331 is
332 cursor C is select ROWID from FND_OAM_METVAL
333 where METRIC_SHORT_NAME = X_METRIC_SHORT_NAME
334 and (X_NODE_NAME is null or
335 (X_NODE_NAME is not null and NODE_NAME = X_NODE_NAME));
336 begin
337 insert into FND_OAM_METVAL (
338 METRIC_SHORT_NAME,
339 METRIC_VALUE,
340 STATUS_CODE,
341 GROUP_ID,
342 SEQUENCE,
343 NODE_NAME,
344 METRIC_TYPE,
345 THRESHOLD_OPERATOR,
346 THRESHOLD_VALUE,
347 ALERT_ENABLED_FLAG,
348 COLLECTION_ENABLED_FLAG,
349 LAST_COLLECTED_DATE,
350 IS_SUPPORTED,
351 IS_CUSTOMIZED,
352 INTERVAL_COUNTER,
353 CREATED_BY,
354 CREATION_DATE,
355 LAST_UPDATED_BY,
356 LAST_UPDATE_DATE,
357 LAST_UPDATE_LOGIN
358 ) values (
359 X_METRIC_SHORT_NAME,
360 X_METRIC_VALUE,
361 X_STATUS_CODE,
362 X_GROUP_ID,
363 X_SEQUENCE,
364 X_NODE_NAME,
365 X_METRIC_TYPE,
366 X_THRESHOLD_OPERATOR,
367 X_THRESHOLD_VALUE,
368 nvl(X_ALERT_ENABLED_FLAG, 'N'),
369 nvl(X_COLLECTION_ENABLED_FLAG, 'Y'),
370 X_LAST_COLLECTED_DATE,
371 nvl(X_IS_SUPPORTED, 'Y'),
375 X_CREATION_DATE,
372 nvl(X_IS_CUSTOMIZED, 'N'),
373 X_INTERVAL_COUNTER,
374 X_CREATED_BY,
376 X_LAST_UPDATED_BY,
377 X_LAST_UPDATE_DATE,
378 X_LAST_UPDATE_LOGIN);
379
380 insert into FND_OAM_METS_TL (
381 METRIC_SHORT_NAME,
382 METRIC_DISPLAY_NAME,
383 DESCRIPTION,
384 CREATED_BY,
385 CREATION_DATE,
386 LAST_UPDATED_BY,
387 LAST_UPDATE_DATE,
388 LAST_UPDATE_LOGIN,
389 LANGUAGE,
390 SOURCE_LANG
391 ) select
392 X_METRIC_SHORT_NAME,
393 X_METRIC_DISPLAY_NAME,
394 X_DESCRIPTION,
395 X_CREATED_BY,
396 X_CREATION_DATE,
397 X_LAST_UPDATED_BY,
398 X_LAST_UPDATE_DATE,
399 X_LAST_UPDATE_LOGIN,
400 L.LANGUAGE_CODE,
401 userenv('LANG')
402 from FND_LANGUAGES L
403 where L.INSTALLED_FLAG in ('I', 'B')
404 and not exists
405 (select NULL
406 from FND_OAM_METS_TL T
407 where T.METRIC_SHORT_NAME = X_METRIC_SHORT_NAME
408 and T.LANGUAGE = L.LANGUAGE_CODE);
409
410 open c;
411 fetch c into X_ROWID;
412 if (c%notfound) then
413 close c;
414 raise no_data_found;
415 end if;
416 close c;
417 END INSERT_ROW;
418
419 procedure UPDATE_ROW (
420 X_METRIC_SHORT_NAME in VARCHAR2,
421 X_METRIC_VALUE in VARCHAR2,
422 X_STATUS_CODE in NUMBER,
423 X_GROUP_ID in NUMBER,
424 X_SEQUENCE in NUMBER,
425 X_NODE_NAME in VARCHAR2,
426 X_METRIC_TYPE in VARCHAR2,
427 X_THRESHOLD_OPERATOR in VARCHAR2,
428 X_THRESHOLD_VALUE in VARCHAR2,
429 X_ALERT_ENABLED_FLAG in VARCHAR2,
430 X_COLLECTION_ENABLED_FLAG in VARCHAR2,
431 X_LAST_COLLECTED_DATE in DATE,
432 X_IS_SUPPORTED in VARCHAR2,
433 X_IS_CUSTOMIZED in VARCHAR2,
434 X_INTERVAL_COUNTER in NUMBER,
435 X_METRIC_DISPLAY_NAME in VARCHAR2,
436 X_DESCRIPTION in VARCHAR2,
437 X_LAST_UPDATE_DATE in DATE,
438 X_LAST_UPDATED_BY in NUMBER,
439 X_LAST_UPDATE_LOGIN in NUMBER
440 ) is
441 begin
442 update FND_OAM_METVAL set
443 METRIC_VALUE = X_METRIC_VALUE,
444 STATUS_CODE = X_STATUS_CODE,
445 GROUP_ID = X_GROUP_ID,
446 SEQUENCE = X_SEQUENCE,
447 METRIC_TYPE = X_METRIC_TYPE,
448 THRESHOLD_OPERATOR = X_THRESHOLD_OPERATOR,
449 THRESHOLD_VALUE = X_THRESHOLD_VALUE,
450 ALERT_ENABLED_FLAG = nvl(X_ALERT_ENABLED_FLAG,'N'),
451 COLLECTION_ENABLED_FLAG = nvl(X_COLLECTION_ENABLED_FLAG,'Y'),
452 LAST_COLLECTED_DATE = X_LAST_COLLECTED_DATE,
453 IS_SUPPORTED = nvl(X_IS_SUPPORTED,'Y'),
454 IS_CUSTOMIZED = nvl(X_IS_CUSTOMIZED,'N'),
455 INTERVAL_COUNTER = X_INTERVAL_COUNTER,
456 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
457 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
458 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
459 where METRIC_SHORT_NAME = X_METRIC_SHORT_NAME
460 and (X_NODE_NAME is null or
461 (X_NODE_NAME is not null and NODE_NAME = X_NODE_NAME));
462
463 if (sql%notfound) then
464 raise no_data_found;
465 end if;
466
467 update FND_OAM_METS_TL set
468 METRIC_DISPLAY_NAME = X_METRIC_DISPLAY_NAME,
469 DESCRIPTION = X_DESCRIPTION,
470 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
471 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
472 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
473 SOURCE_LANG = userenv('LANG')
474 where METRIC_SHORT_NAME = X_METRIC_SHORT_NAME
475 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
476
477 if (sql%notfound) then
478 raise no_data_found;
479 end if;
480 end UPDATE_ROW;
481
482 procedure DELETE_ROW (
483 X_METRIC_SHORT_NAME in VARCHAR2
484 ) is
485 begin
486 delete from FND_OAM_METS_TL
487 where METRIC_SHORT_NAME = X_METRIC_SHORT_NAME;
488
489 if (sql%notfound) then
490 raise no_data_found;
491 end if;
492
493 delete from FND_OAM_METVAL
494 where METRIC_SHORT_NAME = X_METRIC_SHORT_NAME;
495
496 if (sql%notfound) then
497 raise no_data_found;
498 end if;
499 end DELETE_ROW;
500
501 procedure ADD_LANGUAGE
502 is
503 begin
504
505 /* Mar/19/03 requested by Ric Ginsberg */
506 /* The following delete and update statements are commented out */
507 /* as a quick workaround to fix the time-consuming table handler issue */
511 where not exists
508 /* Eventually we'll need to turn them into a separate fix_language procedure */
509 /*
510 delete from FND_OAM_METS_TL T
512 (select NULL
513 from FND_OAM_METVAL B
514 where B.METRIC_SHORT_NAME = T.METRIC_SHORT_NAME
515 );
516
517 update FND_OAM_METS_TL T set (
518 METRIC_DISPLAY_NAME,
519 DESCRIPTION
520 ) = (select
521 B.METRIC_DISPLAY_NAME,
522 B.DESCRIPTION
523 from FND_OAM_METS_TL B
524 where B.METRIC_SHORT_NAME = T.METRIC_SHORT_NAME
525 and B.LANGUAGE = T.SOURCE_LANG)
526 where (
527 T.METRIC_SHORT_NAME,
528 T.LANGUAGE
529 ) in (select
530 SUBT.METRIC_SHORT_NAME,
531 SUBT.LANGUAGE
532 from FND_OAM_METS_TL SUBB, FND_OAM_METS_TL SUBT
533 where SUBB.METRIC_SHORT_NAME = SUBT.METRIC_SHORT_NAME
534 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
535 and (SUBB.METRIC_DISPLAY_NAME <> SUBT.METRIC_DISPLAY_NAME
536 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
537 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
538 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
539 ));
540 */
541
542 insert into FND_OAM_METS_TL (
543 METRIC_SHORT_NAME,
544 METRIC_DISPLAY_NAME,
545 DESCRIPTION,
546 CREATED_BY,
547 CREATION_DATE,
548 LAST_UPDATED_BY,
549 LAST_UPDATE_DATE,
550 LAST_UPDATE_LOGIN,
551 LANGUAGE,
552 SOURCE_LANG
553 ) select
554 B.METRIC_SHORT_NAME,
555 B.METRIC_DISPLAY_NAME,
556 B.DESCRIPTION,
557 B.CREATED_BY,
558 B.CREATION_DATE,
559 B.LAST_UPDATED_BY,
560 B.LAST_UPDATE_DATE,
561 B.LAST_UPDATE_LOGIN,
562 L.LANGUAGE_CODE,
563 B.SOURCE_LANG
564 from FND_OAM_METS_TL B, FND_LANGUAGES L
565 where L.INSTALLED_FLAG in ('I', 'B')
566 and B.LANGUAGE = userenv('LANG')
567 and not exists
568 (select NULL
569 from FND_OAM_METS_TL T
570 where T.METRIC_SHORT_NAME = B.METRIC_SHORT_NAME
571 and T.LANGUAGE = L.LANGUAGE_CODE);
572 end ADD_LANGUAGE;
573
574 END fnd_oam_metval_pkg;