1 package body BSC_KPI_DEFAULTS_PKG as
2 /* $Header: BSCKPIDB.pls 115.6 2003/02/12 14:25:56 adrao ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_TAB_ID in NUMBER,
6 X_INDICATOR in NUMBER,
7 X_FORMAT_MASK in VARCHAR2,
8 X_COLOR_METHOD in NUMBER,
9 X_COLOR_LEVEL1 in NUMBER,
10 X_COLOR_LEVEL2 in NUMBER,
11 X_COLOR_LEVEL3 in NUMBER,
12 X_COLOR_LEVEL4 in NUMBER,
13 X_DIM_SET_ID in NUMBER,
14 X_DIM_LEVEL1_VALUE in NUMBER,
15 X_DIM_LEVEL2_VALUE in NUMBER,
16 X_DIM_LEVEL3_VALUE in NUMBER,
17 X_DIM_LEVEL4_VALUE in NUMBER,
18 X_DIM_LEVEL5_VALUE in NUMBER,
19 X_DIM_LEVEL6_VALUE in NUMBER,
20 X_DIM_LEVEL7_VALUE in NUMBER,
21 X_DIM_LEVEL8_VALUE in NUMBER,
22 X_ANALYSIS_OPTION0_NAME in VARCHAR2,
23 X_ANALYSIS_OPTION1_NAME in VARCHAR2,
24 X_ANALYSIS_OPTION2_NAME in VARCHAR2,
25 X_PERIOD_NAME in VARCHAR2,
26 X_SERIES_NAME in VARCHAR2,
27 X_DIM_LEVEL1_NAME in VARCHAR2,
28 X_DIM_LEVEL2_NAME in VARCHAR2,
29 X_DIM_LEVEL3_NAME in VARCHAR2,
30 X_DIM_LEVEL4_NAME in VARCHAR2,
31 X_DIM_LEVEL5_NAME in VARCHAR2,
32 X_DIM_LEVEL6_NAME in VARCHAR2,
33 X_DIM_LEVEL7_NAME in VARCHAR2,
34 X_DIM_LEVEL8_NAME in VARCHAR2,
35 X_DIM_LEVEL1_TEXT in VARCHAR2,
36 X_DIM_LEVEL2_TEXT in VARCHAR2,
37 X_DIM_LEVEL3_TEXT in VARCHAR2,
38 X_DIM_LEVEL4_TEXT in VARCHAR2,
39 X_DIM_LEVEL5_TEXT in VARCHAR2,
40 X_DIM_LEVEL6_TEXT in VARCHAR2,
41 X_DIM_LEVEL7_TEXT in VARCHAR2,
42 X_DIM_LEVEL8_TEXT in VARCHAR2
43 ) is
44 cursor C is select ROWID from BSC_KPI_DEFAULTS_B
45 where TAB_ID = X_TAB_ID
46 and INDICATOR = X_INDICATOR
47 ;
48 begin
49 insert into BSC_KPI_DEFAULTS_B (
50 TAB_ID,
51 INDICATOR,
52 FORMAT_MASK,
53 COLOR_METHOD,
54 COLOR_LEVEL1,
55 COLOR_LEVEL2,
56 COLOR_LEVEL3,
57 COLOR_LEVEL4,
58 DIM_SET_ID,
59 DIM_LEVEL1_VALUE,
60 DIM_LEVEL2_VALUE,
61 DIM_LEVEL3_VALUE,
62 DIM_LEVEL4_VALUE,
63 DIM_LEVEL5_VALUE,
64 DIM_LEVEL6_VALUE,
65 DIM_LEVEL7_VALUE,
66 DIM_LEVEL8_VALUE
67 ) values (
68 X_TAB_ID,
69 X_INDICATOR,
70 X_FORMAT_MASK,
71 X_COLOR_METHOD,
72 X_COLOR_LEVEL1,
73 X_COLOR_LEVEL2,
74 X_COLOR_LEVEL3,
75 X_COLOR_LEVEL4,
76 X_DIM_SET_ID,
77 X_DIM_LEVEL1_VALUE,
78 X_DIM_LEVEL2_VALUE,
79 X_DIM_LEVEL3_VALUE,
80 X_DIM_LEVEL4_VALUE,
81 X_DIM_LEVEL5_VALUE,
82 X_DIM_LEVEL6_VALUE,
83 X_DIM_LEVEL7_VALUE,
84 X_DIM_LEVEL8_VALUE
85 );
86
87 insert into BSC_KPI_DEFAULTS_TL (
88 TAB_ID,
89 INDICATOR,
90 ANALYSIS_OPTION0_NAME,
91 ANALYSIS_OPTION1_NAME,
92 ANALYSIS_OPTION2_NAME,
93 PERIOD_NAME,
94 SERIES_NAME,
95 DIM_LEVEL1_NAME,
96 DIM_LEVEL2_NAME,
97 DIM_LEVEL3_NAME,
98 DIM_LEVEL4_NAME,
99 DIM_LEVEL5_NAME,
100 DIM_LEVEL6_NAME,
101 DIM_LEVEL7_NAME,
102 DIM_LEVEL8_NAME,
103 DIM_LEVEL1_TEXT,
104 DIM_LEVEL2_TEXT,
105 DIM_LEVEL3_TEXT,
106 DIM_LEVEL4_TEXT,
107 DIM_LEVEL5_TEXT,
108 DIM_LEVEL6_TEXT,
109 DIM_LEVEL7_TEXT,
110 DIM_LEVEL8_TEXT,
111 LANGUAGE,
112 SOURCE_LANG
113 ) select
114 X_TAB_ID,
115 X_INDICATOR,
116 X_ANALYSIS_OPTION0_NAME,
117 X_ANALYSIS_OPTION1_NAME,
118 X_ANALYSIS_OPTION2_NAME,
119 X_PERIOD_NAME,
120 X_SERIES_NAME,
121 X_DIM_LEVEL1_NAME,
122 X_DIM_LEVEL2_NAME,
123 X_DIM_LEVEL3_NAME,
124 X_DIM_LEVEL4_NAME,
125 X_DIM_LEVEL5_NAME,
126 X_DIM_LEVEL6_NAME,
127 X_DIM_LEVEL7_NAME,
128 X_DIM_LEVEL8_NAME,
129 X_DIM_LEVEL1_TEXT,
130 X_DIM_LEVEL2_TEXT,
131 X_DIM_LEVEL3_TEXT,
132 X_DIM_LEVEL4_TEXT,
133 X_DIM_LEVEL5_TEXT,
134 X_DIM_LEVEL6_TEXT,
135 X_DIM_LEVEL7_TEXT,
136 X_DIM_LEVEL8_TEXT,
137 L.LANGUAGE_CODE,
138 userenv('LANG')
139 from FND_LANGUAGES L
140 where L.INSTALLED_FLAG in ('I', 'B')
141 and not exists
142 (select NULL
143 from BSC_KPI_DEFAULTS_TL T
144 where T.TAB_ID = X_TAB_ID
145 and T.INDICATOR = X_INDICATOR
146 and T.LANGUAGE = L.LANGUAGE_CODE);
147
148 open c;
149 fetch c into X_ROWID;
150 if (c%notfound) then
151 close c;
152 raise no_data_found;
153 end if;
154 close c;
155
156 end INSERT_ROW;
157
158 procedure LOCK_ROW (
159 X_TAB_ID in NUMBER,
160 X_INDICATOR in NUMBER,
161 X_FORMAT_MASK in VARCHAR2,
162 X_COLOR_METHOD in NUMBER,
163 X_COLOR_LEVEL1 in NUMBER,
164 X_COLOR_LEVEL2 in NUMBER,
165 X_COLOR_LEVEL3 in NUMBER,
166 X_COLOR_LEVEL4 in NUMBER,
167 X_DIM_SET_ID in NUMBER,
168 X_DIM_LEVEL1_VALUE in NUMBER,
169 X_DIM_LEVEL2_VALUE in NUMBER,
170 X_DIM_LEVEL3_VALUE in NUMBER,
171 X_DIM_LEVEL4_VALUE in NUMBER,
172 X_DIM_LEVEL5_VALUE in NUMBER,
173 X_DIM_LEVEL6_VALUE in NUMBER,
174 X_DIM_LEVEL7_VALUE in NUMBER,
175 X_DIM_LEVEL8_VALUE in NUMBER,
176 X_ANALYSIS_OPTION0_NAME in VARCHAR2,
177 X_ANALYSIS_OPTION1_NAME in VARCHAR2,
178 X_ANALYSIS_OPTION2_NAME in VARCHAR2,
179 X_PERIOD_NAME in VARCHAR2,
180 X_SERIES_NAME in VARCHAR2,
181 X_DIM_LEVEL1_NAME in VARCHAR2,
182 X_DIM_LEVEL2_NAME in VARCHAR2,
183 X_DIM_LEVEL3_NAME in VARCHAR2,
184 X_DIM_LEVEL4_NAME in VARCHAR2,
185 X_DIM_LEVEL5_NAME in VARCHAR2,
186 X_DIM_LEVEL6_NAME in VARCHAR2,
187 X_DIM_LEVEL7_NAME in VARCHAR2,
188 X_DIM_LEVEL8_NAME in VARCHAR2,
189 X_DIM_LEVEL1_TEXT in VARCHAR2,
190 X_DIM_LEVEL2_TEXT in VARCHAR2,
191 X_DIM_LEVEL3_TEXT in VARCHAR2,
192 X_DIM_LEVEL4_TEXT in VARCHAR2,
193 X_DIM_LEVEL5_TEXT in VARCHAR2,
194 X_DIM_LEVEL6_TEXT in VARCHAR2,
195 X_DIM_LEVEL7_TEXT in VARCHAR2,
196 X_DIM_LEVEL8_TEXT in VARCHAR2
197 ) is
198 cursor c is select
199 FORMAT_MASK,
200 COLOR_METHOD,
201 COLOR_LEVEL1,
202 COLOR_LEVEL2,
203 COLOR_LEVEL3,
204 COLOR_LEVEL4,
205 DIM_SET_ID,
206 DIM_LEVEL1_VALUE,
207 DIM_LEVEL2_VALUE,
208 DIM_LEVEL3_VALUE,
209 DIM_LEVEL4_VALUE,
210 DIM_LEVEL5_VALUE,
211 DIM_LEVEL6_VALUE,
212 DIM_LEVEL7_VALUE,
213 DIM_LEVEL8_VALUE
214 from BSC_KPI_DEFAULTS_B
215 where TAB_ID = X_TAB_ID
216 and INDICATOR = X_INDICATOR
217 for update of TAB_ID nowait;
218 recinfo c%rowtype;
219
220 cursor c1 is select
221 ANALYSIS_OPTION0_NAME,
222 ANALYSIS_OPTION1_NAME,
223 ANALYSIS_OPTION2_NAME,
224 PERIOD_NAME,
225 SERIES_NAME,
226 DIM_LEVEL1_NAME,
227 DIM_LEVEL2_NAME,
228 DIM_LEVEL3_NAME,
229 DIM_LEVEL4_NAME,
230 DIM_LEVEL5_NAME,
231 DIM_LEVEL6_NAME,
232 DIM_LEVEL7_NAME,
233 DIM_LEVEL8_NAME,
234 DIM_LEVEL1_TEXT,
235 DIM_LEVEL2_TEXT,
236 DIM_LEVEL3_TEXT,
237 DIM_LEVEL4_TEXT,
238 DIM_LEVEL5_TEXT,
239 DIM_LEVEL6_TEXT,
240 DIM_LEVEL7_TEXT,
241 DIM_LEVEL8_TEXT,
242 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
243 from BSC_KPI_DEFAULTS_TL
244 where TAB_ID = X_TAB_ID
245 and INDICATOR = X_INDICATOR
246 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
247 for update of TAB_ID nowait;
248 begin
249 open c;
250 fetch c into recinfo;
251 if (c%notfound) then
252 close c;
253 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
254 app_exception.raise_exception;
255 end if;
256 close c;
257 if ( ((recinfo.FORMAT_MASK = X_FORMAT_MASK)
258 OR ((recinfo.FORMAT_MASK is null) AND (X_FORMAT_MASK is null)))
259 AND ((recinfo.COLOR_METHOD = X_COLOR_METHOD)
260 OR ((recinfo.COLOR_METHOD is null) AND (X_COLOR_METHOD is null)))
261 AND ((recinfo.COLOR_LEVEL1 = X_COLOR_LEVEL1)
262 OR ((recinfo.COLOR_LEVEL1 is null) AND (X_COLOR_LEVEL1 is null)))
263 AND ((recinfo.COLOR_LEVEL2 = X_COLOR_LEVEL2)
264 OR ((recinfo.COLOR_LEVEL2 is null) AND (X_COLOR_LEVEL2 is null)))
265 AND ((recinfo.COLOR_LEVEL3 = X_COLOR_LEVEL3)
266 OR ((recinfo.COLOR_LEVEL3 is null) AND (X_COLOR_LEVEL3 is null)))
267 AND ((recinfo.COLOR_LEVEL4 = X_COLOR_LEVEL4)
268 OR ((recinfo.COLOR_LEVEL4 is null) AND (X_COLOR_LEVEL4 is null)))
269 AND ((recinfo.DIM_SET_ID = X_DIM_SET_ID)
270 OR ((recinfo.DIM_SET_ID is null) AND (X_DIM_SET_ID is null)))
271 AND ((recinfo.DIM_LEVEL1_VALUE = X_DIM_LEVEL1_VALUE)
272 OR ((recinfo.DIM_LEVEL1_VALUE is null) AND (X_DIM_LEVEL1_VALUE is null)))
273 AND ((recinfo.DIM_LEVEL2_VALUE = X_DIM_LEVEL2_VALUE)
274 OR ((recinfo.DIM_LEVEL2_VALUE is null) AND (X_DIM_LEVEL2_VALUE is null)))
275 AND ((recinfo.DIM_LEVEL3_VALUE = X_DIM_LEVEL3_VALUE)
276 OR ((recinfo.DIM_LEVEL3_VALUE is null) AND (X_DIM_LEVEL3_VALUE is null)))
277 AND ((recinfo.DIM_LEVEL4_VALUE = X_DIM_LEVEL4_VALUE)
278 OR ((recinfo.DIM_LEVEL4_VALUE is null) AND (X_DIM_LEVEL4_VALUE is null)))
279 AND ((recinfo.DIM_LEVEL5_VALUE = X_DIM_LEVEL5_VALUE)
280 OR ((recinfo.DIM_LEVEL5_VALUE is null) AND (X_DIM_LEVEL5_VALUE is null)))
281 AND ((recinfo.DIM_LEVEL6_VALUE = X_DIM_LEVEL6_VALUE)
282 OR ((recinfo.DIM_LEVEL6_VALUE is null) AND (X_DIM_LEVEL6_VALUE is null)))
283 AND ((recinfo.DIM_LEVEL7_VALUE = X_DIM_LEVEL7_VALUE)
284 OR ((recinfo.DIM_LEVEL7_VALUE is null) AND (X_DIM_LEVEL7_VALUE is null)))
285 AND ((recinfo.DIM_LEVEL8_VALUE = X_DIM_LEVEL8_VALUE)
286 OR ((recinfo.DIM_LEVEL8_VALUE is null) AND (X_DIM_LEVEL8_VALUE is null)))
287 ) then
288 null;
289 else
290 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
291 app_exception.raise_exception;
292 end if;
293
294 for tlinfo in c1 loop
295 if (tlinfo.BASELANG = 'Y') then
296 if ( ((tlinfo.ANALYSIS_OPTION0_NAME = X_ANALYSIS_OPTION0_NAME)
297 OR ((tlinfo.ANALYSIS_OPTION0_NAME is null) AND (X_ANALYSIS_OPTION0_NAME is null)))
298 AND ((tlinfo.ANALYSIS_OPTION1_NAME = X_ANALYSIS_OPTION1_NAME)
299 OR ((tlinfo.ANALYSIS_OPTION1_NAME is null) AND (X_ANALYSIS_OPTION1_NAME is null)))
300 AND ((tlinfo.ANALYSIS_OPTION2_NAME = X_ANALYSIS_OPTION2_NAME)
301 OR ((tlinfo.ANALYSIS_OPTION2_NAME is null) AND (X_ANALYSIS_OPTION2_NAME is null)))
302 AND ((tlinfo.PERIOD_NAME = X_PERIOD_NAME)
303 OR ((tlinfo.PERIOD_NAME is null) AND (X_PERIOD_NAME is null)))
304 AND ((tlinfo.SERIES_NAME = X_SERIES_NAME)
305 OR ((tlinfo.SERIES_NAME is null) AND (X_SERIES_NAME is null)))
306 AND ((tlinfo.DIM_LEVEL1_NAME = X_DIM_LEVEL1_NAME)
307 OR ((tlinfo.DIM_LEVEL1_NAME is null) AND (X_DIM_LEVEL1_NAME is null)))
308 AND ((tlinfo.DIM_LEVEL2_NAME = X_DIM_LEVEL2_NAME)
309 OR ((tlinfo.DIM_LEVEL2_NAME is null) AND (X_DIM_LEVEL2_NAME is null)))
310 AND ((tlinfo.DIM_LEVEL3_NAME = X_DIM_LEVEL3_NAME)
311 OR ((tlinfo.DIM_LEVEL3_NAME is null) AND (X_DIM_LEVEL3_NAME is null)))
312 AND ((tlinfo.DIM_LEVEL4_NAME = X_DIM_LEVEL4_NAME)
313 OR ((tlinfo.DIM_LEVEL4_NAME is null) AND (X_DIM_LEVEL4_NAME is null)))
314 AND ((tlinfo.DIM_LEVEL5_NAME = X_DIM_LEVEL5_NAME)
315 OR ((tlinfo.DIM_LEVEL5_NAME is null) AND (X_DIM_LEVEL5_NAME is null)))
316 AND ((tlinfo.DIM_LEVEL6_NAME = X_DIM_LEVEL6_NAME)
317 OR ((tlinfo.DIM_LEVEL6_NAME is null) AND (X_DIM_LEVEL6_NAME is null)))
318 AND ((tlinfo.DIM_LEVEL7_NAME = X_DIM_LEVEL7_NAME)
319 OR ((tlinfo.DIM_LEVEL7_NAME is null) AND (X_DIM_LEVEL7_NAME is null)))
320 AND ((tlinfo.DIM_LEVEL8_NAME = X_DIM_LEVEL8_NAME)
321 OR ((tlinfo.DIM_LEVEL8_NAME is null) AND (X_DIM_LEVEL8_NAME is null)))
322 AND ((tlinfo.DIM_LEVEL1_TEXT = X_DIM_LEVEL1_TEXT)
323 OR ((tlinfo.DIM_LEVEL1_TEXT is null) AND (X_DIM_LEVEL1_TEXT is null)))
324 AND ((tlinfo.DIM_LEVEL2_TEXT = X_DIM_LEVEL2_TEXT)
325 OR ((tlinfo.DIM_LEVEL2_TEXT is null) AND (X_DIM_LEVEL2_TEXT is null)))
326 AND ((tlinfo.DIM_LEVEL3_TEXT = X_DIM_LEVEL3_TEXT)
327 OR ((tlinfo.DIM_LEVEL3_TEXT is null) AND (X_DIM_LEVEL3_TEXT is null)))
328 AND ((tlinfo.DIM_LEVEL4_TEXT = X_DIM_LEVEL4_TEXT)
329 OR ((tlinfo.DIM_LEVEL4_TEXT is null) AND (X_DIM_LEVEL4_TEXT is null)))
330 AND ((tlinfo.DIM_LEVEL5_TEXT = X_DIM_LEVEL5_TEXT)
331 OR ((tlinfo.DIM_LEVEL5_TEXT is null) AND (X_DIM_LEVEL5_TEXT is null)))
332 AND ((tlinfo.DIM_LEVEL6_TEXT = X_DIM_LEVEL6_TEXT)
333 OR ((tlinfo.DIM_LEVEL6_TEXT is null) AND (X_DIM_LEVEL6_TEXT is null)))
334 AND ((tlinfo.DIM_LEVEL7_TEXT = X_DIM_LEVEL7_TEXT)
335 OR ((tlinfo.DIM_LEVEL7_TEXT is null) AND (X_DIM_LEVEL7_TEXT is null)))
336 AND ((tlinfo.DIM_LEVEL8_TEXT = X_DIM_LEVEL8_TEXT)
337 OR ((tlinfo.DIM_LEVEL8_TEXT is null) AND (X_DIM_LEVEL8_TEXT is null)))
338 ) then
339 null;
340 else
341 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
342 app_exception.raise_exception;
343 end if;
344 end if;
345 end loop;
346 return;
347 end LOCK_ROW;
348
349 procedure UPDATE_ROW (
350 X_TAB_ID in NUMBER,
351 X_INDICATOR in NUMBER,
352 X_FORMAT_MASK in VARCHAR2,
353 X_COLOR_METHOD in NUMBER,
354 X_COLOR_LEVEL1 in NUMBER,
355 X_COLOR_LEVEL2 in NUMBER,
356 X_COLOR_LEVEL3 in NUMBER,
357 X_COLOR_LEVEL4 in NUMBER,
358 X_DIM_SET_ID in NUMBER,
359 X_DIM_LEVEL1_VALUE in NUMBER,
360 X_DIM_LEVEL2_VALUE in NUMBER,
361 X_DIM_LEVEL3_VALUE in NUMBER,
362 X_DIM_LEVEL4_VALUE in NUMBER,
363 X_DIM_LEVEL5_VALUE in NUMBER,
364 X_DIM_LEVEL6_VALUE in NUMBER,
365 X_DIM_LEVEL7_VALUE in NUMBER,
366 X_DIM_LEVEL8_VALUE in NUMBER,
367 X_ANALYSIS_OPTION0_NAME in VARCHAR2,
368 X_ANALYSIS_OPTION1_NAME in VARCHAR2,
369 X_ANALYSIS_OPTION2_NAME in VARCHAR2,
370 X_PERIOD_NAME in VARCHAR2,
371 X_SERIES_NAME in VARCHAR2,
372 X_DIM_LEVEL1_NAME in VARCHAR2,
373 X_DIM_LEVEL2_NAME in VARCHAR2,
374 X_DIM_LEVEL3_NAME in VARCHAR2,
375 X_DIM_LEVEL4_NAME in VARCHAR2,
376 X_DIM_LEVEL5_NAME in VARCHAR2,
377 X_DIM_LEVEL6_NAME in VARCHAR2,
378 X_DIM_LEVEL7_NAME in VARCHAR2,
379 X_DIM_LEVEL8_NAME in VARCHAR2,
380 X_DIM_LEVEL1_TEXT in VARCHAR2,
381 X_DIM_LEVEL2_TEXT in VARCHAR2,
382 X_DIM_LEVEL3_TEXT in VARCHAR2,
383 X_DIM_LEVEL4_TEXT in VARCHAR2,
384 X_DIM_LEVEL5_TEXT in VARCHAR2,
385 X_DIM_LEVEL6_TEXT in VARCHAR2,
386 X_DIM_LEVEL7_TEXT in VARCHAR2,
387 X_DIM_LEVEL8_TEXT in VARCHAR2
388 ) is
389 begin
390 update BSC_KPI_DEFAULTS_B set
391 FORMAT_MASK = X_FORMAT_MASK,
392 COLOR_METHOD = X_COLOR_METHOD,
393 COLOR_LEVEL1 = X_COLOR_LEVEL1,
394 COLOR_LEVEL2 = X_COLOR_LEVEL2,
398 DIM_LEVEL1_VALUE = X_DIM_LEVEL1_VALUE,
395 COLOR_LEVEL3 = X_COLOR_LEVEL3,
396 COLOR_LEVEL4 = X_COLOR_LEVEL4,
397 DIM_SET_ID = X_DIM_SET_ID,
399 DIM_LEVEL2_VALUE = X_DIM_LEVEL2_VALUE,
400 DIM_LEVEL3_VALUE = X_DIM_LEVEL3_VALUE,
401 DIM_LEVEL4_VALUE = X_DIM_LEVEL4_VALUE,
402 DIM_LEVEL5_VALUE = X_DIM_LEVEL5_VALUE,
403 DIM_LEVEL6_VALUE = X_DIM_LEVEL6_VALUE,
404 DIM_LEVEL7_VALUE = X_DIM_LEVEL7_VALUE,
405 DIM_LEVEL8_VALUE = X_DIM_LEVEL8_VALUE
406 where TAB_ID = X_TAB_ID
407 and INDICATOR = X_INDICATOR;
408
409 if (sql%notfound) then
410 raise no_data_found;
411 end if;
412
413 update BSC_KPI_DEFAULTS_TL set
414 ANALYSIS_OPTION0_NAME = X_ANALYSIS_OPTION0_NAME,
415 ANALYSIS_OPTION1_NAME = X_ANALYSIS_OPTION1_NAME,
416 ANALYSIS_OPTION2_NAME = X_ANALYSIS_OPTION2_NAME,
417 PERIOD_NAME = X_PERIOD_NAME,
418 SERIES_NAME = X_SERIES_NAME,
419 DIM_LEVEL1_NAME = X_DIM_LEVEL1_NAME,
420 DIM_LEVEL2_NAME = X_DIM_LEVEL2_NAME,
421 DIM_LEVEL3_NAME = X_DIM_LEVEL3_NAME,
422 DIM_LEVEL4_NAME = X_DIM_LEVEL4_NAME,
423 DIM_LEVEL5_NAME = X_DIM_LEVEL5_NAME,
424 DIM_LEVEL6_NAME = X_DIM_LEVEL6_NAME,
425 DIM_LEVEL7_NAME = X_DIM_LEVEL7_NAME,
426 DIM_LEVEL8_NAME = X_DIM_LEVEL8_NAME,
427 DIM_LEVEL1_TEXT = X_DIM_LEVEL1_TEXT,
428 DIM_LEVEL2_TEXT = X_DIM_LEVEL2_TEXT,
429 DIM_LEVEL3_TEXT = X_DIM_LEVEL3_TEXT,
430 DIM_LEVEL4_TEXT = X_DIM_LEVEL4_TEXT,
431 DIM_LEVEL5_TEXT = X_DIM_LEVEL5_TEXT,
432 DIM_LEVEL6_TEXT = X_DIM_LEVEL6_TEXT,
433 DIM_LEVEL7_TEXT = X_DIM_LEVEL7_TEXT,
434 DIM_LEVEL8_TEXT = X_DIM_LEVEL8_TEXT,
435 SOURCE_LANG = userenv('LANG')
436 where TAB_ID = X_TAB_ID
437 and INDICATOR = X_INDICATOR
438 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
439
440 if (sql%notfound) then
441 raise no_data_found;
442 end if;
443 end UPDATE_ROW;
444
445 procedure DELETE_ROW (
446 X_TAB_ID in NUMBER,
447 X_INDICATOR in NUMBER
448 ) is
449 begin
450 delete from BSC_KPI_DEFAULTS_TL
451 where TAB_ID = X_TAB_ID
452 and INDICATOR = X_INDICATOR;
453
454 if (sql%notfound) then
455 raise no_data_found;
456 end if;
457
458 delete from BSC_KPI_DEFAULTS_B
459 where TAB_ID = X_TAB_ID
460 and INDICATOR = X_INDICATOR;
461
462 if (sql%notfound) then
463 raise no_data_found;
464 end if;
465 end DELETE_ROW;
466
467 procedure ADD_LANGUAGE
468 is
469 begin
470 delete from BSC_KPI_DEFAULTS_TL T
471 where not exists
472 (select NULL
473 from BSC_KPI_DEFAULTS_B B
474 where B.TAB_ID = T.TAB_ID
475 and B.INDICATOR = T.INDICATOR
476 );
477
478 update BSC_KPI_DEFAULTS_TL T set (
479 ANALYSIS_OPTION0_NAME,
480 ANALYSIS_OPTION1_NAME,
481 ANALYSIS_OPTION2_NAME,
482 PERIOD_NAME,
483 SERIES_NAME,
484 DIM_LEVEL1_NAME,
485 DIM_LEVEL2_NAME,
486 DIM_LEVEL3_NAME,
487 DIM_LEVEL4_NAME,
488 DIM_LEVEL5_NAME,
489 DIM_LEVEL6_NAME,
490 DIM_LEVEL7_NAME,
491 DIM_LEVEL8_NAME,
492 DIM_LEVEL1_TEXT,
493 DIM_LEVEL2_TEXT,
494 DIM_LEVEL3_TEXT,
495 DIM_LEVEL4_TEXT,
496 DIM_LEVEL5_TEXT,
497 DIM_LEVEL6_TEXT,
498 DIM_LEVEL7_TEXT,
499 DIM_LEVEL8_TEXT
500 ) = (select
501 B.ANALYSIS_OPTION0_NAME,
502 B.ANALYSIS_OPTION1_NAME,
503 B.ANALYSIS_OPTION2_NAME,
504 B.PERIOD_NAME,
505 B.SERIES_NAME,
506 B.DIM_LEVEL1_NAME,
507 B.DIM_LEVEL2_NAME,
508 B.DIM_LEVEL3_NAME,
509 B.DIM_LEVEL4_NAME,
510 B.DIM_LEVEL5_NAME,
511 B.DIM_LEVEL6_NAME,
512 B.DIM_LEVEL7_NAME,
513 B.DIM_LEVEL8_NAME,
514 B.DIM_LEVEL1_TEXT,
515 B.DIM_LEVEL2_TEXT,
516 B.DIM_LEVEL3_TEXT,
517 B.DIM_LEVEL4_TEXT,
518 B.DIM_LEVEL5_TEXT,
519 B.DIM_LEVEL6_TEXT,
520 B.DIM_LEVEL7_TEXT,
521 B.DIM_LEVEL8_TEXT
522 from BSC_KPI_DEFAULTS_TL B
523 where B.TAB_ID = T.TAB_ID
524 and B.INDICATOR = T.INDICATOR
525 and B.LANGUAGE = T.SOURCE_LANG)
526 where (
527 T.TAB_ID,
528 T.INDICATOR,
529 T.LANGUAGE
530 ) in (select
531 SUBT.TAB_ID,
532 SUBT.INDICATOR,
533 SUBT.LANGUAGE
534 from BSC_KPI_DEFAULTS_TL SUBB, BSC_KPI_DEFAULTS_TL SUBT
535 where SUBB.TAB_ID = SUBT.TAB_ID
536 and SUBB.INDICATOR = SUBT.INDICATOR
537 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
538 and (SUBB.ANALYSIS_OPTION0_NAME <> SUBT.ANALYSIS_OPTION0_NAME
539 or (SUBB.ANALYSIS_OPTION0_NAME is null and SUBT.ANALYSIS_OPTION0_NAME is not null)
540 or (SUBB.ANALYSIS_OPTION0_NAME is not null and SUBT.ANALYSIS_OPTION0_NAME is null)
541 or SUBB.ANALYSIS_OPTION1_NAME <> SUBT.ANALYSIS_OPTION1_NAME
542 or (SUBB.ANALYSIS_OPTION1_NAME is null and SUBT.ANALYSIS_OPTION1_NAME is not null)
543 or (SUBB.ANALYSIS_OPTION1_NAME is not null and SUBT.ANALYSIS_OPTION1_NAME is null)
544 or SUBB.ANALYSIS_OPTION2_NAME <> SUBT.ANALYSIS_OPTION2_NAME
545 or (SUBB.ANALYSIS_OPTION2_NAME is null and SUBT.ANALYSIS_OPTION2_NAME is not null)
549 or (SUBB.PERIOD_NAME is not null and SUBT.PERIOD_NAME is null)
546 or (SUBB.ANALYSIS_OPTION2_NAME is not null and SUBT.ANALYSIS_OPTION2_NAME is null)
547 or SUBB.PERIOD_NAME <> SUBT.PERIOD_NAME
548 or (SUBB.PERIOD_NAME is null and SUBT.PERIOD_NAME is not null)
550 or SUBB.SERIES_NAME <> SUBT.SERIES_NAME
551 or (SUBB.SERIES_NAME is null and SUBT.SERIES_NAME is not null)
552 or (SUBB.SERIES_NAME is not null and SUBT.SERIES_NAME is null)
553 or SUBB.DIM_LEVEL1_NAME <> SUBT.DIM_LEVEL1_NAME
554 or (SUBB.DIM_LEVEL1_NAME is null and SUBT.DIM_LEVEL1_NAME is not null)
555 or (SUBB.DIM_LEVEL1_NAME is not null and SUBT.DIM_LEVEL1_NAME is null)
556 or SUBB.DIM_LEVEL2_NAME <> SUBT.DIM_LEVEL2_NAME
557 or (SUBB.DIM_LEVEL2_NAME is null and SUBT.DIM_LEVEL2_NAME is not null)
558 or (SUBB.DIM_LEVEL2_NAME is not null and SUBT.DIM_LEVEL2_NAME is null)
559 or SUBB.DIM_LEVEL3_NAME <> SUBT.DIM_LEVEL3_NAME
560 or (SUBB.DIM_LEVEL3_NAME is null and SUBT.DIM_LEVEL3_NAME is not null)
561 or (SUBB.DIM_LEVEL3_NAME is not null and SUBT.DIM_LEVEL3_NAME is null)
562 or SUBB.DIM_LEVEL4_NAME <> SUBT.DIM_LEVEL4_NAME
563 or (SUBB.DIM_LEVEL4_NAME is null and SUBT.DIM_LEVEL4_NAME is not null)
564 or (SUBB.DIM_LEVEL4_NAME is not null and SUBT.DIM_LEVEL4_NAME is null)
565 or SUBB.DIM_LEVEL5_NAME <> SUBT.DIM_LEVEL5_NAME
566 or (SUBB.DIM_LEVEL5_NAME is null and SUBT.DIM_LEVEL5_NAME is not null)
567 or (SUBB.DIM_LEVEL5_NAME is not null and SUBT.DIM_LEVEL5_NAME is null)
568 or SUBB.DIM_LEVEL6_NAME <> SUBT.DIM_LEVEL6_NAME
569 or (SUBB.DIM_LEVEL6_NAME is null and SUBT.DIM_LEVEL6_NAME is not null)
570 or (SUBB.DIM_LEVEL6_NAME is not null and SUBT.DIM_LEVEL6_NAME is null)
571 or SUBB.DIM_LEVEL7_NAME <> SUBT.DIM_LEVEL7_NAME
572 or (SUBB.DIM_LEVEL7_NAME is null and SUBT.DIM_LEVEL7_NAME is not null)
573 or (SUBB.DIM_LEVEL7_NAME is not null and SUBT.DIM_LEVEL7_NAME is null)
574 or SUBB.DIM_LEVEL8_NAME <> SUBT.DIM_LEVEL8_NAME
575 or (SUBB.DIM_LEVEL8_NAME is null and SUBT.DIM_LEVEL8_NAME is not null)
576 or (SUBB.DIM_LEVEL8_NAME is not null and SUBT.DIM_LEVEL8_NAME is null)
577 or SUBB.DIM_LEVEL1_TEXT <> SUBT.DIM_LEVEL1_TEXT
578 or (SUBB.DIM_LEVEL1_TEXT is null and SUBT.DIM_LEVEL1_TEXT is not null)
579 or (SUBB.DIM_LEVEL1_TEXT is not null and SUBT.DIM_LEVEL1_TEXT is null)
580 or SUBB.DIM_LEVEL2_TEXT <> SUBT.DIM_LEVEL2_TEXT
581 or (SUBB.DIM_LEVEL2_TEXT is null and SUBT.DIM_LEVEL2_TEXT is not null)
582 or (SUBB.DIM_LEVEL2_TEXT is not null and SUBT.DIM_LEVEL2_TEXT is null)
583 or SUBB.DIM_LEVEL3_TEXT <> SUBT.DIM_LEVEL3_TEXT
584 or (SUBB.DIM_LEVEL3_TEXT is null and SUBT.DIM_LEVEL3_TEXT is not null)
585 or (SUBB.DIM_LEVEL3_TEXT is not null and SUBT.DIM_LEVEL3_TEXT is null)
586 or SUBB.DIM_LEVEL4_TEXT <> SUBT.DIM_LEVEL4_TEXT
587 or (SUBB.DIM_LEVEL4_TEXT is null and SUBT.DIM_LEVEL4_TEXT is not null)
588 or (SUBB.DIM_LEVEL4_TEXT is not null and SUBT.DIM_LEVEL4_TEXT is null)
589 or SUBB.DIM_LEVEL5_TEXT <> SUBT.DIM_LEVEL5_TEXT
590 or (SUBB.DIM_LEVEL5_TEXT is null and SUBT.DIM_LEVEL5_TEXT is not null)
591 or (SUBB.DIM_LEVEL5_TEXT is not null and SUBT.DIM_LEVEL5_TEXT is null)
592 or SUBB.DIM_LEVEL6_TEXT <> SUBT.DIM_LEVEL6_TEXT
593 or (SUBB.DIM_LEVEL6_TEXT is null and SUBT.DIM_LEVEL6_TEXT is not null)
594 or (SUBB.DIM_LEVEL6_TEXT is not null and SUBT.DIM_LEVEL6_TEXT is null)
595 or SUBB.DIM_LEVEL7_TEXT <> SUBT.DIM_LEVEL7_TEXT
596 or (SUBB.DIM_LEVEL7_TEXT is null and SUBT.DIM_LEVEL7_TEXT is not null)
597 or (SUBB.DIM_LEVEL7_TEXT is not null and SUBT.DIM_LEVEL7_TEXT is null)
598 or SUBB.DIM_LEVEL8_TEXT <> SUBT.DIM_LEVEL8_TEXT
599 or (SUBB.DIM_LEVEL8_TEXT is null and SUBT.DIM_LEVEL8_TEXT is not null)
600 or (SUBB.DIM_LEVEL8_TEXT is not null and SUBT.DIM_LEVEL8_TEXT is null)
601 ));
602
603 insert into BSC_KPI_DEFAULTS_TL (
604 TAB_ID,
605 INDICATOR,
606 ANALYSIS_OPTION0_NAME,
607 ANALYSIS_OPTION1_NAME,
608 ANALYSIS_OPTION2_NAME,
609 PERIOD_NAME,
610 SERIES_NAME,
611 DIM_LEVEL1_NAME,
612 DIM_LEVEL2_NAME,
613 DIM_LEVEL3_NAME,
614 DIM_LEVEL4_NAME,
615 DIM_LEVEL5_NAME,
616 DIM_LEVEL6_NAME,
617 DIM_LEVEL7_NAME,
618 DIM_LEVEL8_NAME,
619 DIM_LEVEL1_TEXT,
620 DIM_LEVEL2_TEXT,
621 DIM_LEVEL3_TEXT,
622 DIM_LEVEL4_TEXT,
623 DIM_LEVEL5_TEXT,
624 DIM_LEVEL6_TEXT,
625 DIM_LEVEL7_TEXT,
626 DIM_LEVEL8_TEXT,
627 LANGUAGE,
628 SOURCE_LANG
629 ) select
630 B.TAB_ID,
631 B.INDICATOR,
632 B.ANALYSIS_OPTION0_NAME,
633 B.ANALYSIS_OPTION1_NAME,
634 B.ANALYSIS_OPTION2_NAME,
635 B.PERIOD_NAME,
636 B.SERIES_NAME,
637 B.DIM_LEVEL1_NAME,
638 B.DIM_LEVEL2_NAME,
639 B.DIM_LEVEL3_NAME,
640 B.DIM_LEVEL4_NAME,
641 B.DIM_LEVEL5_NAME,
642 B.DIM_LEVEL6_NAME,
643 B.DIM_LEVEL7_NAME,
644 B.DIM_LEVEL8_NAME,
645 B.DIM_LEVEL1_TEXT,
646 B.DIM_LEVEL2_TEXT,
647 B.DIM_LEVEL3_TEXT,
648 B.DIM_LEVEL4_TEXT,
649 B.DIM_LEVEL5_TEXT,
650 B.DIM_LEVEL6_TEXT,
651 B.DIM_LEVEL7_TEXT,
652 B.DIM_LEVEL8_TEXT,
653 L.LANGUAGE_CODE,
654 B.SOURCE_LANG
655 from BSC_KPI_DEFAULTS_TL B, FND_LANGUAGES L
656 where L.INSTALLED_FLAG in ('I', 'B')
657 and B.LANGUAGE = userenv('LANG')
658 and not exists
659 (select NULL
660 from BSC_KPI_DEFAULTS_TL T
661 where T.TAB_ID = B.TAB_ID
662 and T.INDICATOR = B.INDICATOR
663 and T.LANGUAGE = L.LANGUAGE_CODE);
664 end ADD_LANGUAGE;
665
666 end BSC_KPI_DEFAULTS_PKG;
[Home] [Help]