1 package body BSC_KPI_DIM_LEVELS_PKG as
2 /* $Header: BSCKDIMB.pls 115.7 2003/02/12 14:25:48 adrao ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_INDICATOR in NUMBER,
6 X_DIM_SET_ID in NUMBER,
7 X_DIM_LEVEL_INDEX in NUMBER,
8 X_LEVEL_TABLE_NAME in VARCHAR2,
9 X_LEVEL_VIEW_NAME in VARCHAR2,
10 X_FILTER_COLUMN in VARCHAR2,
11 X_FILTER_VALUE in NUMBER,
12 X_DEFAULT_VALUE in VARCHAR2,
13 X_DEFAULT_TYPE in NUMBER,
14 X_VALUE_ORDER_BY in NUMBER,
15 X_COMP_ORDER_BY in NUMBER,
16 X_LEVEL_PK_COL in VARCHAR2,
17 X_PARENT_LEVEL_INDEX in NUMBER,
18 X_PARENT_LEVEL_REL in VARCHAR2,
19 X_TABLE_RELATION in VARCHAR2,
20 X_PARENT_LEVEL_INDEX2 in NUMBER,
21 X_PARENT_LEVEL_REL2 in VARCHAR2,
22 X_STATUS in NUMBER,
23 X_PARENT_IN_TOTAL in NUMBER,
24 X_POSITION in NUMBER,
25 X_TOTAL0 in NUMBER,
26 X_LEVEL_DISPLAY in NUMBER,
27 X_NO_ITEMS in NUMBER,
28 X_DEFAULT_KEY_VALUE in NUMBER,
29 X_USER_LEVEL0 in NUMBER,
30 X_USER_LEVEL1 in NUMBER,
31 X_USER_LEVEL1_DEFAULT in NUMBER,
32 X_USER_LEVEL2 in NUMBER,
33 X_USER_LEVEL2_DEFAULT in NUMBER,
34 X_NAME in VARCHAR2,
35 X_HELP in VARCHAR2,
36 X_TOTAL_DISP_NAME in VARCHAR2,
37 X_COMP_DISP_NAME in VARCHAR2
38 ) is
39 cursor C is select ROWID from BSC_KPI_DIM_LEVELS_B
40 where INDICATOR = X_INDICATOR
41 and DIM_SET_ID = X_DIM_SET_ID
42 and DIM_LEVEL_INDEX = X_DIM_LEVEL_INDEX
43 ;
44 begin
45 insert into BSC_KPI_DIM_LEVELS_B (
46 INDICATOR,
47 DIM_SET_ID,
48 DIM_LEVEL_INDEX,
49 LEVEL_TABLE_NAME,
50 LEVEL_VIEW_NAME,
51 FILTER_COLUMN,
52 FILTER_VALUE,
53 DEFAULT_VALUE,
54 DEFAULT_TYPE,
55 VALUE_ORDER_BY,
56 COMP_ORDER_BY,
57 LEVEL_PK_COL,
58 PARENT_LEVEL_INDEX,
59 PARENT_LEVEL_REL,
60 TABLE_RELATION,
61 PARENT_LEVEL_INDEX2,
62 PARENT_LEVEL_REL2,
63 STATUS,
64 PARENT_IN_TOTAL,
65 POSITION,
66 TOTAL0,
67 LEVEL_DISPLAY,
68 NO_ITEMS,
69 DEFAULT_KEY_VALUE,
70 USER_LEVEL0,
71 USER_LEVEL1,
72 USER_LEVEL1_DEFAULT,
73 USER_LEVEL2,
74 USER_LEVEL2_DEFAULT
75 ) values (
76 X_INDICATOR,
77 X_DIM_SET_ID,
78 X_DIM_LEVEL_INDEX,
79 X_LEVEL_TABLE_NAME,
80 X_LEVEL_VIEW_NAME,
81 X_FILTER_COLUMN,
82 X_FILTER_VALUE,
83 X_DEFAULT_VALUE,
84 X_DEFAULT_TYPE,
85 X_VALUE_ORDER_BY,
86 X_COMP_ORDER_BY,
87 X_LEVEL_PK_COL,
88 X_PARENT_LEVEL_INDEX,
89 X_PARENT_LEVEL_REL,
90 X_TABLE_RELATION,
91 X_PARENT_LEVEL_INDEX2,
92 X_PARENT_LEVEL_REL2,
93 X_STATUS,
94 X_PARENT_IN_TOTAL,
95 X_POSITION,
96 X_TOTAL0,
97 X_LEVEL_DISPLAY,
98 X_NO_ITEMS,
99 X_DEFAULT_KEY_VALUE,
100 X_USER_LEVEL0,
101 X_USER_LEVEL1,
102 X_USER_LEVEL1_DEFAULT,
103 X_USER_LEVEL2,
104 X_USER_LEVEL2_DEFAULT
105 );
106
107 insert into BSC_KPI_DIM_LEVELS_TL (
108 INDICATOR,
109 DIM_SET_ID,
110 DIM_LEVEL_INDEX,
111 NAME,
112 HELP,
113 TOTAL_DISP_NAME,
114 COMP_DISP_NAME,
115 LANGUAGE,
116 SOURCE_LANG
117 ) select
118 X_INDICATOR,
119 X_DIM_SET_ID,
120 X_DIM_LEVEL_INDEX,
121 X_NAME,
122 X_HELP,
123 X_TOTAL_DISP_NAME,
124 X_COMP_DISP_NAME,
125 L.LANGUAGE_CODE,
126 userenv('LANG')
127 from FND_LANGUAGES L
128 where L.INSTALLED_FLAG in ('I', 'B')
129 and not exists
130 (select NULL
131 from BSC_KPI_DIM_LEVELS_TL T
132 where T.INDICATOR = X_INDICATOR
133 and T.DIM_SET_ID = X_DIM_SET_ID
134 and T.DIM_LEVEL_INDEX = X_DIM_LEVEL_INDEX
135 and T.LANGUAGE = L.LANGUAGE_CODE);
136
137 open c;
138 fetch c into X_ROWID;
139 if (c%notfound) then
140 close c;
141 raise no_data_found;
142 end if;
143 close c;
144
145 end INSERT_ROW;
146
147 procedure LOCK_ROW (
148 X_INDICATOR in NUMBER,
149 X_DIM_SET_ID in NUMBER,
150 X_DIM_LEVEL_INDEX in NUMBER,
151 X_LEVEL_TABLE_NAME in VARCHAR2,
152 X_LEVEL_VIEW_NAME in VARCHAR2,
153 X_FILTER_COLUMN in VARCHAR2,
154 X_FILTER_VALUE in NUMBER,
155 X_DEFAULT_VALUE in VARCHAR2,
156 X_DEFAULT_TYPE in NUMBER,
157 X_VALUE_ORDER_BY in NUMBER,
158 X_COMP_ORDER_BY in NUMBER,
159 X_LEVEL_PK_COL in VARCHAR2,
160 X_PARENT_LEVEL_INDEX in NUMBER,
161 X_PARENT_LEVEL_REL in VARCHAR2,
162 X_TABLE_RELATION in VARCHAR2,
163 X_PARENT_LEVEL_INDEX2 in NUMBER,
164 X_PARENT_LEVEL_REL2 in VARCHAR2,
165 X_STATUS in NUMBER,
166 X_PARENT_IN_TOTAL in NUMBER,
167 X_POSITION in NUMBER,
168 X_TOTAL0 in NUMBER,
169 X_LEVEL_DISPLAY in NUMBER,
170 X_NO_ITEMS in NUMBER,
171 X_DEFAULT_KEY_VALUE in NUMBER,
172 X_USER_LEVEL0 in NUMBER,
173 X_USER_LEVEL1 in NUMBER,
174 X_USER_LEVEL1_DEFAULT in NUMBER,
175 X_USER_LEVEL2 in NUMBER,
176 X_USER_LEVEL2_DEFAULT in NUMBER,
177 X_NAME in VARCHAR2,
178 X_HELP in VARCHAR2,
179 X_TOTAL_DISP_NAME in VARCHAR2,
180 X_COMP_DISP_NAME in VARCHAR2
181 ) is
182 cursor c is select
183 LEVEL_TABLE_NAME,
184 LEVEL_VIEW_NAME,
185 FILTER_COLUMN,
186 FILTER_VALUE,
187 DEFAULT_VALUE,
188 DEFAULT_TYPE,
189 VALUE_ORDER_BY,
190 COMP_ORDER_BY,
191 LEVEL_PK_COL,
192 PARENT_LEVEL_INDEX,
193 PARENT_LEVEL_REL,
194 TABLE_RELATION,
195 PARENT_LEVEL_INDEX2,
196 PARENT_LEVEL_REL2,
197 STATUS,
198 PARENT_IN_TOTAL,
199 POSITION,
200 TOTAL0,
201 LEVEL_DISPLAY,
202 NO_ITEMS,
203 DEFAULT_KEY_VALUE,
204 USER_LEVEL0,
205 USER_LEVEL1,
206 USER_LEVEL1_DEFAULT,
207 USER_LEVEL2,
208 USER_LEVEL2_DEFAULT
209 from BSC_KPI_DIM_LEVELS_B
210 where INDICATOR = X_INDICATOR
211 and DIM_SET_ID = X_DIM_SET_ID
212 and DIM_LEVEL_INDEX = X_DIM_LEVEL_INDEX
213 for update of INDICATOR nowait;
214 recinfo c%rowtype;
215
216 cursor c1 is select
217 NAME,
218 HELP,
219 TOTAL_DISP_NAME,
220 COMP_DISP_NAME,
221 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
222 from BSC_KPI_DIM_LEVELS_TL
223 where INDICATOR = X_INDICATOR
224 and DIM_SET_ID = X_DIM_SET_ID
225 and DIM_LEVEL_INDEX = X_DIM_LEVEL_INDEX
226 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
227 for update of INDICATOR nowait;
228 begin
229 open c;
230 fetch c into recinfo;
231 if (c%notfound) then
232 close c;
233 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
234 app_exception.raise_exception;
235 end if;
236 close c;
237 if ( (recinfo.LEVEL_TABLE_NAME = X_LEVEL_TABLE_NAME)
238 AND ((recinfo.LEVEL_VIEW_NAME = X_LEVEL_VIEW_NAME)
239 OR ((recinfo.LEVEL_VIEW_NAME is null) AND (X_LEVEL_VIEW_NAME is null)))
240 AND ((recinfo.FILTER_COLUMN = X_FILTER_COLUMN)
241 OR ((recinfo.FILTER_COLUMN is null) AND (X_FILTER_COLUMN is null)))
242 AND ((recinfo.FILTER_VALUE = X_FILTER_VALUE)
243 OR ((recinfo.FILTER_VALUE is null) AND (X_FILTER_VALUE is null)))
244 AND ((recinfo.DEFAULT_VALUE = X_DEFAULT_VALUE)
245 OR ((recinfo.DEFAULT_VALUE is null) AND (X_DEFAULT_VALUE is null)))
246 AND ((recinfo.DEFAULT_TYPE = X_DEFAULT_TYPE)
247 OR ((recinfo.DEFAULT_TYPE is null) AND (X_DEFAULT_TYPE is null)))
248 AND (recinfo.VALUE_ORDER_BY = X_VALUE_ORDER_BY)
249 AND (recinfo.COMP_ORDER_BY = X_COMP_ORDER_BY)
250 AND (recinfo.LEVEL_PK_COL = X_LEVEL_PK_COL)
251 AND ((recinfo.PARENT_LEVEL_INDEX = X_PARENT_LEVEL_INDEX)
252 OR ((recinfo.PARENT_LEVEL_INDEX is null) AND (X_PARENT_LEVEL_INDEX is null)))
253 AND ((recinfo.PARENT_LEVEL_REL = X_PARENT_LEVEL_REL)
254 OR ((recinfo.PARENT_LEVEL_REL is null) AND (X_PARENT_LEVEL_REL is null)))
255 AND ((recinfo.TABLE_RELATION = X_TABLE_RELATION)
256 OR ((recinfo.TABLE_RELATION is null) AND (X_TABLE_RELATION is null)))
257 AND ((recinfo.PARENT_LEVEL_INDEX2 = X_PARENT_LEVEL_INDEX2)
258 OR ((recinfo.PARENT_LEVEL_INDEX2 is null) AND (X_PARENT_LEVEL_INDEX2 is null)))
259 AND ((recinfo.PARENT_LEVEL_REL2 = X_PARENT_LEVEL_REL2)
260 OR ((recinfo.PARENT_LEVEL_REL2 is null) AND (X_PARENT_LEVEL_REL2 is null)))
261 AND (recinfo.STATUS = X_STATUS)
262 AND ((recinfo.PARENT_IN_TOTAL = X_PARENT_IN_TOTAL)
263 OR ((recinfo.PARENT_IN_TOTAL is null) AND (X_PARENT_IN_TOTAL is null)))
264 AND (recinfo.POSITION = X_POSITION)
265 AND ((recinfo.TOTAL0 = X_TOTAL0)
266 OR ((recinfo.TOTAL0 is null) AND (X_TOTAL0 is null)))
267 AND ((recinfo.LEVEL_DISPLAY = X_LEVEL_DISPLAY)
268 OR ((recinfo.LEVEL_DISPLAY is null) AND (X_LEVEL_DISPLAY is null)))
269 AND ((recinfo.NO_ITEMS = X_NO_ITEMS)
270 OR ((recinfo.NO_ITEMS is null) AND (X_NO_ITEMS is null)))
271 AND ((recinfo.DEFAULT_KEY_VALUE = X_DEFAULT_KEY_VALUE)
272 OR ((recinfo.DEFAULT_KEY_VALUE is null) AND (X_DEFAULT_KEY_VALUE is null)))
273 AND ((recinfo.USER_LEVEL0 = X_USER_LEVEL0)
274 OR ((recinfo.USER_LEVEL0 is null) AND (X_USER_LEVEL0 is null)))
275 AND ((recinfo.USER_LEVEL1 = X_USER_LEVEL1)
276 OR ((recinfo.USER_LEVEL1 is null) AND (X_USER_LEVEL1 is null)))
277 AND ((recinfo.USER_LEVEL1_DEFAULT = X_USER_LEVEL1_DEFAULT)
278 OR ((recinfo.USER_LEVEL1_DEFAULT is null) AND (X_USER_LEVEL1_DEFAULT is null)))
279 AND ((recinfo.USER_LEVEL2 = X_USER_LEVEL2)
280 OR ((recinfo.USER_LEVEL2 is null) AND (X_USER_LEVEL2 is null)))
281 AND ((recinfo.USER_LEVEL2_DEFAULT = X_USER_LEVEL2_DEFAULT)
282 OR ((recinfo.USER_LEVEL2_DEFAULT is null) AND (X_USER_LEVEL2_DEFAULT is null)))
283 ) then
284 null;
285 else
286 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
287 app_exception.raise_exception;
288 end if;
289
290 for tlinfo in c1 loop
291 if (tlinfo.BASELANG = 'Y') then
292 if ( (tlinfo.NAME = X_NAME)
293 AND (tlinfo.HELP = X_HELP)
294 AND ((tlinfo.TOTAL_DISP_NAME = X_TOTAL_DISP_NAME)
295 OR ((tlinfo.TOTAL_DISP_NAME is null) AND (X_TOTAL_DISP_NAME is null)))
296 AND ((tlinfo.COMP_DISP_NAME = X_COMP_DISP_NAME)
297 OR ((tlinfo.COMP_DISP_NAME is null) AND (X_COMP_DISP_NAME is null)))
298 ) then
299 null;
300 else
301 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
302 app_exception.raise_exception;
303 end if;
304 end if;
305 end loop;
306 return;
307 end LOCK_ROW;
308
309 procedure UPDATE_ROW (
310 X_INDICATOR in NUMBER,
311 X_DIM_SET_ID in NUMBER,
312 X_DIM_LEVEL_INDEX in NUMBER,
313 X_LEVEL_TABLE_NAME in VARCHAR2,
314 X_LEVEL_VIEW_NAME in VARCHAR2,
315 X_FILTER_COLUMN in VARCHAR2,
316 X_FILTER_VALUE in NUMBER,
317 X_DEFAULT_VALUE in VARCHAR2,
318 X_DEFAULT_TYPE in NUMBER,
319 X_VALUE_ORDER_BY in NUMBER,
320 X_COMP_ORDER_BY in NUMBER,
321 X_LEVEL_PK_COL in VARCHAR2,
322 X_PARENT_LEVEL_INDEX in NUMBER,
323 X_PARENT_LEVEL_REL in VARCHAR2,
324 X_TABLE_RELATION in VARCHAR2,
325 X_PARENT_LEVEL_INDEX2 in NUMBER,
326 X_PARENT_LEVEL_REL2 in VARCHAR2,
327 X_STATUS in NUMBER,
328 X_PARENT_IN_TOTAL in NUMBER,
329 X_POSITION in NUMBER,
330 X_TOTAL0 in NUMBER,
331 X_LEVEL_DISPLAY in NUMBER,
332 X_NO_ITEMS in NUMBER,
333 X_DEFAULT_KEY_VALUE in NUMBER,
334 X_USER_LEVEL0 in NUMBER,
335 X_USER_LEVEL1 in NUMBER,
336 X_USER_LEVEL1_DEFAULT in NUMBER,
337 X_USER_LEVEL2 in NUMBER,
338 X_USER_LEVEL2_DEFAULT in NUMBER,
339 X_NAME in VARCHAR2,
340 X_HELP in VARCHAR2,
341 X_TOTAL_DISP_NAME in VARCHAR2,
342 X_COMP_DISP_NAME in VARCHAR2
343 ) is
344 begin
345 update BSC_KPI_DIM_LEVELS_B set
346 LEVEL_TABLE_NAME = X_LEVEL_TABLE_NAME,
347 LEVEL_VIEW_NAME = X_LEVEL_VIEW_NAME,
348 FILTER_COLUMN = X_FILTER_COLUMN,
349 FILTER_VALUE = X_FILTER_VALUE,
350 DEFAULT_VALUE = X_DEFAULT_VALUE,
351 DEFAULT_TYPE = X_DEFAULT_TYPE,
352 VALUE_ORDER_BY = X_VALUE_ORDER_BY,
353 COMP_ORDER_BY = X_COMP_ORDER_BY,
354 LEVEL_PK_COL = X_LEVEL_PK_COL,
355 PARENT_LEVEL_INDEX = X_PARENT_LEVEL_INDEX,
356 PARENT_LEVEL_REL = X_PARENT_LEVEL_REL,
357 TABLE_RELATION = X_TABLE_RELATION,
358 PARENT_LEVEL_INDEX2 = X_PARENT_LEVEL_INDEX2,
359 PARENT_LEVEL_REL2 = X_PARENT_LEVEL_REL2,
360 STATUS = X_STATUS,
361 PARENT_IN_TOTAL = X_PARENT_IN_TOTAL,
362 POSITION = X_POSITION,
363 TOTAL0 = X_TOTAL0,
364 LEVEL_DISPLAY = X_LEVEL_DISPLAY,
365 NO_ITEMS = X_NO_ITEMS,
366 DEFAULT_KEY_VALUE = X_DEFAULT_KEY_VALUE,
367 USER_LEVEL0 = X_USER_LEVEL0,
368 USER_LEVEL1 = X_USER_LEVEL1,
369 USER_LEVEL1_DEFAULT = X_USER_LEVEL1_DEFAULT,
370 USER_LEVEL2 = X_USER_LEVEL2,
371 USER_LEVEL2_DEFAULT = X_USER_LEVEL2_DEFAULT
372 where INDICATOR = X_INDICATOR
373 and DIM_SET_ID = X_DIM_SET_ID
374 and DIM_LEVEL_INDEX = X_DIM_LEVEL_INDEX;
375
376 if (sql%notfound) then
377 raise no_data_found;
378 end if;
379
380 update BSC_KPI_DIM_LEVELS_TL set
381 NAME = X_NAME,
382 HELP = X_HELP,
383 TOTAL_DISP_NAME = X_TOTAL_DISP_NAME,
384 COMP_DISP_NAME = X_COMP_DISP_NAME,
385 SOURCE_LANG = userenv('LANG')
386 where INDICATOR = X_INDICATOR
387 and DIM_SET_ID = X_DIM_SET_ID
388 and DIM_LEVEL_INDEX = X_DIM_LEVEL_INDEX
389 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
390
391 if (sql%notfound) then
392 raise no_data_found;
393 end if;
394 end UPDATE_ROW;
395
396 procedure DELETE_ROW (
400 ) is
397 X_INDICATOR in NUMBER,
398 X_DIM_SET_ID in NUMBER,
399 X_DIM_LEVEL_INDEX in NUMBER
401 begin
402 delete from BSC_KPI_DIM_LEVELS_TL
403 where INDICATOR = X_INDICATOR
404 and DIM_SET_ID = X_DIM_SET_ID
405 and DIM_LEVEL_INDEX = X_DIM_LEVEL_INDEX;
406
407 if (sql%notfound) then
408 raise no_data_found;
409 end if;
410
411 delete from BSC_KPI_DIM_LEVELS_B
412 where INDICATOR = X_INDICATOR
413 and DIM_SET_ID = X_DIM_SET_ID
414 and DIM_LEVEL_INDEX = X_DIM_LEVEL_INDEX;
415
416 if (sql%notfound) then
417 raise no_data_found;
418 end if;
419 end DELETE_ROW;
420
421 procedure ADD_LANGUAGE
422 is
423 begin
424 delete from BSC_KPI_DIM_LEVELS_TL T
425 where not exists
426 (select NULL
427 from BSC_KPI_DIM_LEVELS_B B
428 where B.INDICATOR = T.INDICATOR
429 and B.DIM_SET_ID = T.DIM_SET_ID
430 and B.DIM_LEVEL_INDEX = T.DIM_LEVEL_INDEX
431 );
432
433 update BSC_KPI_DIM_LEVELS_TL T set (
434 NAME,
435 HELP,
436 TOTAL_DISP_NAME,
437 COMP_DISP_NAME
438 ) = (select
439 B.NAME,
440 B.HELP,
441 B.TOTAL_DISP_NAME,
442 B.COMP_DISP_NAME
443 from BSC_KPI_DIM_LEVELS_TL B
444 where B.INDICATOR = T.INDICATOR
445 and B.DIM_SET_ID = T.DIM_SET_ID
446 and B.DIM_LEVEL_INDEX = T.DIM_LEVEL_INDEX
447 and B.LANGUAGE = T.SOURCE_LANG)
448 where (
449 T.INDICATOR,
450 T.DIM_SET_ID,
451 T.DIM_LEVEL_INDEX,
452 T.LANGUAGE
453 ) in (select
454 SUBT.INDICATOR,
455 SUBT.DIM_SET_ID,
456 SUBT.DIM_LEVEL_INDEX,
457 SUBT.LANGUAGE
458 from BSC_KPI_DIM_LEVELS_TL SUBB, BSC_KPI_DIM_LEVELS_TL SUBT
459 where SUBB.INDICATOR = SUBT.INDICATOR
460 and SUBB.DIM_SET_ID = SUBT.DIM_SET_ID
461 and SUBB.DIM_LEVEL_INDEX = SUBT.DIM_LEVEL_INDEX
462 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
463 and (SUBB.NAME <> SUBT.NAME
464 or SUBB.HELP <> SUBT.HELP
465 or SUBB.TOTAL_DISP_NAME <> SUBT.TOTAL_DISP_NAME
466 or (SUBB.TOTAL_DISP_NAME is null and SUBT.TOTAL_DISP_NAME is not null)
467 or (SUBB.TOTAL_DISP_NAME is not null and SUBT.TOTAL_DISP_NAME is null)
468 or SUBB.COMP_DISP_NAME <> SUBT.COMP_DISP_NAME
469 or (SUBB.COMP_DISP_NAME is null and SUBT.COMP_DISP_NAME is not null)
470 or (SUBB.COMP_DISP_NAME is not null and SUBT.COMP_DISP_NAME is null)
471 ));
472
473 insert into BSC_KPI_DIM_LEVELS_TL (
474 INDICATOR,
475 DIM_SET_ID,
476 DIM_LEVEL_INDEX,
477 NAME,
478 HELP,
479 TOTAL_DISP_NAME,
480 COMP_DISP_NAME,
481 LANGUAGE,
482 SOURCE_LANG
483 ) select
484 B.INDICATOR,
485 B.DIM_SET_ID,
486 B.DIM_LEVEL_INDEX,
487 B.NAME,
488 B.HELP,
489 B.TOTAL_DISP_NAME,
490 B.COMP_DISP_NAME,
491 L.LANGUAGE_CODE,
492 B.SOURCE_LANG
493 from BSC_KPI_DIM_LEVELS_TL B, FND_LANGUAGES L
494 where L.INSTALLED_FLAG in ('I', 'B')
495 and B.LANGUAGE = userenv('LANG')
496 and not exists
497 (select NULL
498 from BSC_KPI_DIM_LEVELS_TL T
499 where T.INDICATOR = B.INDICATOR
500 and T.DIM_SET_ID = B.DIM_SET_ID
501 and T.DIM_LEVEL_INDEX = B.DIM_LEVEL_INDEX
502 and T.LANGUAGE = L.LANGUAGE_CODE);
503 end ADD_LANGUAGE;
504
505 end BSC_KPI_DIM_LEVELS_PKG;