[Home] [Help]
PACKAGE BODY: APPS.BSC_CUSTOM_VIEW_UI_WRAPPER
Source
1 PACKAGE BODY BSC_CUSTOM_VIEW_UI_WRAPPER AS
2 /* $Header: BSCCVDPB.pls 120.13 2007/03/15 10:42:34 ashankar ship $ */
3
4 PROCEDURE add_or_update_kpi_trend(
5 p_tab_id IN NUMBER
6 ,p_tab_view_id IN NUMBER
7 ,p_object_id IN NUMBER
8 ,p_text_flag IN NUMBER
9 ,p_label_text IN VARCHAR2
10 ,p_font_size IN NUMBER
11 ,p_font_style IN NUMBER
12 ,p_font_color IN NUMBER
13 ,p_left IN NUMBER
14 ,p_top IN NUMBER
15 ,p_width IN NUMBER
16 ,p_height IN NUMBER
17 ,p_kpi_id IN NUMBER
18 ,p_function_id IN NUMBER
19 ,x_return_status OUT NOCOPY VARCHAR2
20 ,x_msg_count OUT NOCOPY NUMBER
21 ,x_msg_data OUT NOCOPY VARCHAR2
22 );
23
24
25 FUNCTION Is_More
26 ( p_list_ids IN OUT NOCOPY VARCHAR2
27 ,p_id OUT NOCOPY VARCHAR2
28 ) RETURN BOOLEAN
29 IS
30 l_pos_ids NUMBER;
31 l_pos_rel_types NUMBER;
32 l_pos_rel_columns NUMBER;
33 BEGIN
34 IF (p_list_ids IS NOT NULL) THEN
35 l_pos_ids := INSTR(p_list_ids, ',');
36 IF (l_pos_ids > 0) THEN
37 p_id := TRIM(SUBSTR(p_list_ids, 1, l_pos_ids - 1));
38 p_list_ids := TRIM(SUBSTR(p_list_ids, l_pos_ids + 1));
39 ELSE
40 p_id := TRIM(p_list_ids);
41 p_list_ids := NULL;
42 END IF;
43 RETURN TRUE;
44 ELSE
45 RETURN FALSE;
46 END IF;
47 END Is_More;
48
49 --Compact all label ids in BSC_TAB_VIEW_LABELS_TL and BSC_TAB_VIEW_LABELS_B to be in consecutive order
50 PROCEDURE compact_custom_view_labels(
51 p_tab_id IN NUMBER
52 ,p_tab_view_id IN NUMBER
53 ,x_return_status OUT NOCOPY VARCHAR2
54 ,x_msg_count OUT NOCOPY NUMBER
55 ,x_msg_data OUT NOCOPY VARCHAR2
56 ) IS
57 l_count NUMBER;
58
59 CURSOR LABEL_CUR IS
60 SELECT label_id
61 FROM BSC_TAB_VIEW_LABELS_B
62 WHERE tab_id = p_tab_id
63 AND tab_view_id = p_tab_view_id
64 ORDER BY label_id;
65 l_label_cur LABEL_CUR%ROWTYPE;
66
67 BEGIN
68 SAVEPOINT RollBackPt;
69 fnd_msg_pub.initialize;
70 x_return_status := FND_API.G_RET_STS_SUCCESS;
71
72 l_count := 0;
73
74 FOR l_label_cur IN LABEL_CUR LOOP
75 UPDATE BSC_TAB_VIEW_LABELS_B
76 SET label_id = l_count
77 WHERE tab_id = p_tab_id
78 AND tab_view_id = p_tab_view_id
79 AND label_id = l_label_cur.label_id;
80
81 UPDATE BSC_TAB_VIEW_LABELS_TL
82 SET label_id = l_count
83 WHERE tab_id = p_tab_id
84 AND tab_view_id = p_tab_view_id
85 AND label_id = l_label_cur.label_id;
86
87 l_count := l_count + 1;
88 END LOOP;
89
90 EXCEPTION
91 WHEN others THEN
92 ROLLBACK TO RollBackPt;
93 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
94 x_msg_data := SQLERRM;
95 END compact_custom_view_labels;
96
97 -- Clear BSC_TAB_VIEW_LABELS_TL, BSC_TAB_VIEW_LABELS_B and BSC_TAB_VIEW_KPI_TL with given tab_id and tab_view_id
98 PROCEDURE clear_custom_view_canvas(
99 p_tab_id IN NUMBER
100 ,p_tab_view_id IN NUMBER
101 ,x_return_status OUT NOCOPY VARCHAR2
102 ,x_msg_count OUT NOCOPY NUMBER
103 ,x_msg_data OUT NOCOPY VARCHAR2
104 ) IS
105 BEGIN
106 SAVEPOINT RollBackPt;
107 fnd_msg_pub.initialize;
108 x_return_status := FND_API.G_RET_STS_SUCCESS;
109
110 DELETE FROM BSC_TAB_VIEW_LABELS_TL
111 WHERE TAB_ID = p_tab_id
112 AND TAB_VIEW_ID = p_tab_view_id;
113
114 DELETE FROM BSC_TAB_VIEW_LABELS_B
115 WHERE TAB_ID = p_tab_id
116 AND TAB_VIEW_ID = p_tab_view_id;
117
118 DELETE FROM BSC_TAB_VIEW_KPI_TL
119 WHERE TAB_ID = p_tab_id
120 AND TAB_VIEW_ID = p_tab_view_id;
121
122 EXCEPTION
123 WHEN others THEN
124 ROLLBACK TO RollBackPt;
125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
126 x_msg_data := SQLERRM;
127 END clear_custom_view_canvas;
128
129 --Remove all items specified in removedKPIs and removedLabels
130 --Format of removedKPIs and removedLabels are id1,id2,id3,...,idN
131 PROCEDURE remove_custom_view_items(
132 p_tab_id IN NUMBER
133 ,p_tab_view_id IN NUMBER
134 ,p_kpis IN VARCHAR2
135 ,p_labels IN VARCHAR2
136 ,x_return_status OUT NOCOPY VARCHAR2
137 ,x_msg_count OUT NOCOPY NUMBER
138 ,x_msg_data OUT NOCOPY VARCHAR2
139 ) IS
140 TYPE index_table_type IS TABLE OF NUMBER INDEX BY binary_integer;
141 l_kpis_table index_table_type;
142 l_lables_table index_table_type;
143 l_id NUMBER;
144 l_kpis VARCHAR2(5000);
145 l_labels VARCHAR2(5000);
146
147 CURSOR kpi_cur IS
148 SELECT indicator
149 FROM bsc_tab_view_kpi_vl
150 WHERE tab_id = p_tab_id
151 AND tab_view_id = p_tab_view_id;
152 l_kpi_cur kpi_cur%ROWTYPE;
153
154 CURSOR label_cur IS
155 SELECT label_id
156 FROM bsc_tab_view_labels_vl
157 WHERE tab_id = p_tab_id
158 AND tab_view_id = p_tab_view_id;
159 l_label_cur label_cur%ROWTYPE;
160
161 BEGIN
162 SAVEPOINT RollBackPt;
163 fnd_msg_pub.initialize;
164 x_return_status := FND_API.G_RET_STS_SUCCESS;
165
166 --convert string of IDs to table lookup
167 l_kpis := p_kpis;
168 WHILE (Is_More(p_list_ids => l_kpis, p_id => l_id))
169 LOOP
170 l_kpis_table(l_id) := 1;
171 END LOOP;
172
173 l_labels := p_labels;
174 WHILE (Is_More(p_list_ids => l_labels, p_id => l_id))
175 LOOP
176 l_lables_table(l_id) := 1;
177 END LOOP;
178
179 --loop for all existing KPIs and remove all those that are not in the l_kpis
180 FOR l_kpi_cur IN kpi_cur
181 LOOP
182 IF (l_kpis_table.exists(l_kpi_cur.indicator) = FALSE) THEN
183 BSC_TAB_VIEW_KPI_PKG.DELETE_ROW (
184 X_TAB_ID => p_tab_id,
185 X_TAB_VIEW_ID => p_tab_view_id,
186 X_INDICATOR => l_kpi_cur.indicator
187 );
188 END IF;
189 END LOOP;
190
191 --loop for all existing labels and remove all those that are not in the l_labels
192 FOR l_label_cur IN label_cur
193 LOOP
194 IF (l_lables_table.exists(l_label_cur.label_id) = FALSE) THEN
195 BSC_TAB_VIEW_LABELS_PKG.DELETE_ROW (
196 X_TAB_ID => p_tab_id,
197 X_TAB_VIEW_ID => p_tab_view_id,
198 X_LABEL_ID => l_label_cur.label_id
199 );
200 END IF;
201 END LOOP;
202
203 EXCEPTION
204 WHEN others THEN
205 ROLLBACK TO RollBackPt;
206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
207 x_msg_data := SQLERRM;
208 END remove_custom_view_items;
209
210 -- Add specified label to BSC_TAB_VIEW_LABELS_PKG
211 PROCEDURE add_or_update_kpi_actual(
212 p_tab_id IN NUMBER
213 ,p_tab_view_id IN NUMBER
214 ,p_object_id IN NUMBER
215 ,p_text_flag IN NUMBER
216 ,p_label_text IN VARCHAR2
217 ,p_font_size IN NUMBER
218 ,p_font_style IN NUMBER
219 ,p_font_color IN NUMBER
220 ,p_left IN NUMBER
221 ,p_top IN NUMBER
222 ,p_width IN NUMBER
223 ,p_height IN NUMBER
224 ,p_kpi_id IN NUMBER
225 ,p_function_id IN NUMBER
226 ,x_return_status OUT NOCOPY VARCHAR2
227 ,x_msg_count OUT NOCOPY NUMBER
228 ,x_msg_data OUT NOCOPY VARCHAR2
229 ) IS
230 BEGIN
231 SAVEPOINT RollBackPt;
232 fnd_msg_pub.initialize;
233 x_return_status := FND_API.G_RET_STS_SUCCESS;
234
235 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label(
236 p_tab_id => p_tab_id
237 ,p_tab_view_id => p_tab_view_id
238 ,p_object_id => p_object_id
239 ,p_object_type => c_type_kpi_actual
240 ,p_label_text => p_label_text
241 ,p_text_flag => p_text_flag
242 ,p_font_color => p_font_color
243 ,p_font_size => p_font_size
244 ,p_font_style => p_font_style
245 ,p_left => p_left
246 ,p_top => p_top
247 ,p_width => p_width
248 ,p_height => p_height
249 ,p_note_text => NULL
250 ,p_link_id => p_kpi_id
251 ,p_function_id => p_function_id
252 ,x_return_status => x_return_status
253 ,x_msg_count => x_msg_count
254 ,x_msg_data => x_msg_data
255 );
256
257 EXCEPTION
258 WHEN others THEN
259 ROLLBACK TO RollBackPt;
260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
261 x_msg_data := SQLERRM;
262 END add_or_update_kpi_actual;
263
264 -- Add specified label to BSC_TAB_VIEW_LABELS_PKG
265 PROCEDURE add_or_update_kpi_change(
266 p_tab_id IN NUMBER
267 ,p_tab_view_id IN NUMBER
268 ,p_object_id IN NUMBER
269 ,p_text_flag IN NUMBER
270 ,p_label_text IN VARCHAR2
271 ,p_font_size IN NUMBER
272 ,p_font_style IN NUMBER
273 ,p_font_color IN NUMBER
274 ,p_left IN NUMBER
275 ,p_top IN NUMBER
276 ,p_width IN NUMBER
277 ,p_height IN NUMBER
278 ,p_kpi_id IN NUMBER
279 ,p_function_id IN NUMBER
280 ,x_return_status OUT NOCOPY VARCHAR2
281 ,x_msg_count OUT NOCOPY NUMBER
282 ,x_msg_data OUT NOCOPY VARCHAR2
283 ) IS
284 BEGIN
285 SAVEPOINT RollBackPt;
286 fnd_msg_pub.initialize;
287 x_return_status := FND_API.G_RET_STS_SUCCESS;
288
289 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label(
290 p_tab_id => p_tab_id
291 ,p_tab_view_id => p_tab_view_id
292 ,p_object_id => p_object_id
293 ,p_object_type => c_type_kpi_change
294 ,p_label_text => p_label_text
295 ,p_text_flag => p_text_flag
296 ,p_font_color => p_font_color
297 ,p_font_size => p_font_size
298 ,p_font_style => p_font_style
299 ,p_left => p_left
300 ,p_top => p_top
301 ,p_width => p_width
302 ,p_height => p_height
303 ,p_note_text => NULL
304 ,p_link_id => p_kpi_id
305 ,p_function_id => p_function_id
306 ,x_return_status => x_return_status
307 ,x_msg_count => x_msg_count
308 ,x_msg_data => x_msg_data
309 );
310
311 EXCEPTION
312 WHEN others THEN
313 ROLLBACK TO RollBackPt;
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
315 x_msg_data := SQLERRM;
316 END add_or_update_kpi_change;
317
318 -- Add specified label to BSC_TAB_VIEW_LABELS_PKG
319 PROCEDURE add_or_update_kpi_label(
320 p_tab_id IN NUMBER
321 ,p_tab_view_id IN NUMBER
322 ,p_object_id IN NUMBER
323 ,p_text_flag IN NUMBER
324 ,p_label_text IN VARCHAR2
325 ,p_font_size IN NUMBER
326 ,p_font_style IN NUMBER
327 ,p_font_color IN NUMBER
328 ,p_left IN NUMBER
329 ,p_top IN NUMBER
330 ,p_width IN NUMBER
331 ,p_height IN NUMBER
332 ,p_kpi_id IN NUMBER
333 ,p_function_id IN NUMBER
334 ,x_return_status OUT NOCOPY VARCHAR2
335 ,x_msg_count OUT NOCOPY NUMBER
336 ,x_msg_data OUT NOCOPY VARCHAR2
337 ) IS
338 BEGIN
339 SAVEPOINT RollBackPt;
340 fnd_msg_pub.initialize;
341 x_return_status := FND_API.G_RET_STS_SUCCESS;
342
343 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label(
344 p_tab_id => p_tab_id
345 ,p_tab_view_id => p_tab_view_id
346 ,p_object_id => p_object_id
347 ,p_object_type => c_type_kpi
348 ,p_label_text => p_label_text
349 ,p_text_flag => p_text_flag
350 ,p_font_color => p_font_color
351 ,p_font_size => p_font_size
352 ,p_font_style => p_font_style
353 ,p_left => p_left
354 ,p_top => p_top
355 ,p_width => p_width
356 ,p_height => p_height
357 ,p_note_text => NULL
358 ,p_link_id => p_kpi_id
359 ,p_function_id => p_function_id
360 ,x_return_status => x_return_status
361 ,x_msg_count => x_msg_count
362 ,x_msg_data => x_msg_data
363 );
364
365 EXCEPTION
366 WHEN others THEN
367 ROLLBACK TO RollBackPt;
368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
369 x_msg_data := SQLERRM;
370 END add_or_update_kpi_label;
371
372 -- insert to the following tables:
373 -- BSC_TAB_VIEW_KPI_TL (1 row for kpi)
374 -- BSC_TAB_VIEW_LABELS_B (1 row for kpi, 1 row for actual, 1 row for change)
375 -- BSC_TAB_VIEW_LABELS_TL (1 row for kpi, 1 row for actual, 1 row for change)
376 PROCEDURE add_or_update_kpi(
377 p_tab_id IN NUMBER
378 ,p_tab_view_id IN NUMBER
379 ,p_object_id IN NUMBER
380 ,p_kpi_id IN NUMBER
381 ,p_text_flag IN NUMBER
382 ,p_font_size IN NUMBER
383 ,p_font_style IN NUMBER
384 ,p_font_color IN NUMBER
385 ,p_hotspot_left IN NUMBER
386 ,p_hotspot_top IN NUMBER
387 ,p_hotspot_width IN NUMBER
388 ,p_hotspot_height IN NUMBER
389 ,p_alarm_left IN NUMBER
390 ,p_alarm_top IN NUMBER
391 ,p_alarm_width IN NUMBER
392 ,p_alarm_height IN NUMBER
393 ,p_actual_object_id IN NUMBER
394 ,p_actual_flag IN NUMBER
395 ,p_actual_left IN NUMBER
396 ,p_actual_top IN NUMBER
397 ,p_actual_width IN NUMBER
398 ,p_actual_height IN NUMBER
399 ,p_change_object_id IN NUMBER
400 ,p_change_flag IN NUMBER
401 ,p_change_left IN NUMBER
402 ,p_change_top IN NUMBER
403 ,p_change_width IN NUMBER
404 ,p_change_height IN NUMBER
405 ,p_link_function_id IN NUMBER
406 ,p_trend_object_id IN NUMBER
407 ,p_trend_flag IN NUMBER
408 ,p_trend_left IN NUMBER
409 ,p_trend_top IN NUMBER
410 ,p_trend_width IN NUMBER
411 ,p_trend_height IN NUMBER
412 ,x_return_status OUT NOCOPY VARCHAR2
413 ,x_msg_count OUT NOCOPY NUMBER
414 ,x_msg_data OUT NOCOPY VARCHAR2
415 ) IS
416 BEGIN
417 SAVEPOINT RollBackPt;
418 fnd_msg_pub.initialize;
419 x_return_status := FND_API.G_RET_STS_SUCCESS;
420
421 -- Add specified entry for objectives to BSC_TAB_VIEW_LABELS_PKG
422 -- position and color info is stored in BSC_TAB_VIEW_KPI_TL entries
423 add_or_update_kpi_label(
424 p_tab_id => p_tab_id
425 ,p_tab_view_id => p_tab_view_id
426 ,p_object_id => p_object_id
427 ,p_text_flag => p_text_flag
428 ,p_label_text => c_kpi
429 ,p_font_size => p_font_size
430 ,p_font_color => p_font_color
431 ,p_font_style => p_font_style
432 ,p_left => p_hotspot_left
433 ,p_top => p_hotspot_top
434 ,p_width => p_hotspot_width
435 ,p_height => p_hotspot_height
436 ,p_kpi_id => p_kpi_id
437 ,p_function_id => p_link_function_id
438 ,x_return_status => x_return_status
439 ,x_msg_count => x_msg_count
440 ,x_msg_data => x_msg_data
441 );
442
443 -- Add specified kpi to BSC_TAB_VIEW_KPI_TL
444 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_kpi(
445 p_tab_id => p_tab_id
446 ,p_tab_view_id => p_tab_view_id
447 ,p_kpi_id => p_kpi_id
448 ,p_text_flag => p_text_flag
449 ,p_font_size => p_font_size
450 ,p_font_style => p_font_style
451 ,p_font_color => p_font_color
452 ,p_hotspot_left => p_hotspot_left
453 ,p_hotspot_top => p_hotspot_top
454 ,p_hotspot_width => p_hotspot_width
455 ,p_hotspot_height => p_hotspot_height
456 ,p_alarm_left => p_alarm_left
457 ,p_alarm_top => p_alarm_top
458 ,p_alarm_width => p_alarm_width
459 ,p_alarm_height => p_alarm_height
460 ,x_return_status => x_return_status
461 ,x_msg_count => x_msg_count
462 ,x_msg_data => x_msg_data
463 );
464
465 -- Add specified entry for actual to BSC_TAB_VIEW_LABELS_PKG
466 add_or_update_kpi_actual(
467 p_tab_id => p_tab_id
468 ,p_tab_view_id => p_tab_view_id
469 ,p_object_id => p_actual_object_id
470 ,p_text_flag => p_actual_flag
471 ,p_label_text => c_kpi_actual
472 ,p_font_size => p_font_size
473 ,p_font_color => p_font_color
474 ,p_font_style => p_font_style
475 ,p_left => p_actual_left
476 ,p_top => p_actual_top
477 ,p_width => p_actual_width
478 ,p_height => p_actual_height
479 ,p_kpi_id => p_kpi_id
480 ,p_function_id => p_link_function_id
481 ,x_return_status => x_return_status
482 ,x_msg_count => x_msg_count
483 ,x_msg_data => x_msg_data
484 );
485
486 -- Add specified entry for change to BSC_TAB_VIEW_LABELS_PKG
487 add_or_update_kpi_change(
488 p_tab_id => p_tab_id
489 ,p_tab_view_id => p_tab_view_id
490 ,p_object_id => p_change_object_id
491 ,p_text_flag => p_change_flag
492 ,p_label_text => c_kpi_change
493 ,p_font_size => p_font_size
494 ,p_font_color => p_font_color
495 ,p_font_style => p_font_style
496 ,p_left => p_change_left
497 ,p_top => p_change_top
498 ,p_width => p_change_width
499 ,p_height => p_change_height
500 ,p_kpi_id => p_kpi_id
501 ,p_function_id=> p_link_function_id
502 ,x_return_status => x_return_status
503 ,x_msg_count => x_msg_count
504 ,x_msg_data => x_msg_data
505 );
506
507 -- Add Specified entry for trend to BSC_TAB_VIEW_LABELS_PKG
508
509 add_or_update_kpi_trend(
510 p_tab_id => p_tab_id
511 ,p_tab_view_id => p_tab_view_id
512 ,p_object_id => p_trend_object_id
513 ,p_text_flag => p_trend_flag
514 ,p_label_text => c_kpi_trend
515 ,p_font_size => p_font_size
516 ,p_font_color => p_font_color
517 ,p_font_style => p_font_style
518 ,p_left => p_trend_left
519 ,p_top => p_trend_top
520 ,p_width => p_trend_width
521 ,p_height => p_trend_height
522 ,p_kpi_id => p_kpi_id
523 ,p_function_id=> p_link_function_id
524 ,x_return_status => x_return_status
525 ,x_msg_count => x_msg_count
526 ,x_msg_data => x_msg_data
527 );
528
529
530 EXCEPTION
531 WHEN others THEN
532 ROLLBACK TO RollBackPt;
533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
534 x_msg_data := SQLERRM;
535 END add_or_update_kpi;
536
537
538 -- Add specified label to BSC_TAB_VIEW_LABELS_PKG
539 PROCEDURE add_or_update_label(
540 p_tab_id IN NUMBER
541 ,p_tab_view_id IN NUMBER
542 ,p_object_id IN NUMBER
543 ,p_label_text IN VARCHAR2
544 ,p_font_size IN NUMBER
545 ,p_font_style IN NUMBER
546 ,p_font_color IN NUMBER
547 ,p_left IN NUMBER
548 ,p_top IN NUMBER
549 ,p_width IN NUMBER
550 ,p_height IN NUMBER
551 ,x_return_status OUT NOCOPY VARCHAR2
552 ,x_msg_count OUT NOCOPY NUMBER
553 ,x_msg_data OUT NOCOPY VARCHAR2
554 ) IS
555 BEGIN
556 SAVEPOINT RollBackPt;
557 fnd_msg_pub.initialize;
558 x_return_status := FND_API.G_RET_STS_SUCCESS;
559
560 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label(
561 p_tab_id => p_tab_id
562 ,p_tab_view_id => p_tab_view_id
563 ,p_object_id => p_object_id
564 ,p_object_type => c_type_label
565 ,p_label_text => p_label_text
566 ,p_text_flag => 1
567 ,p_font_color => p_font_color
568 ,p_font_size => p_font_size
569 ,p_font_style => p_font_style
570 ,p_left => p_left
571 ,p_top => p_top
572 ,p_width => p_width
573 ,p_height => p_height
574 ,p_note_text => NULL
575 ,p_link_id => NULL
576 ,p_function_id => NULL
577 ,x_return_status => x_return_status
578 ,x_msg_count => x_msg_count
579 ,x_msg_data => x_msg_data
580 );
581
582 EXCEPTION
583 WHEN others THEN
584 ROLLBACK TO RollBackPt;
585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
586 x_msg_data := SQLERRM;
587 END add_or_update_label;
588
589 -- Add specified hotspot to BSC_TAB_VIEW_LABELS_PKG
590 PROCEDURE add_or_update_hotspot(
591 p_tab_id IN NUMBER
592 ,p_tab_view_id IN NUMBER
593 ,p_object_id IN NUMBER
594 ,p_label_text IN VARCHAR2
595 ,p_left IN NUMBER
596 ,p_top IN NUMBER
597 ,p_width IN NUMBER
598 ,p_height IN NUMBER
599 ,x_return_status OUT NOCOPY VARCHAR2
600 ,x_msg_count OUT NOCOPY NUMBER
601 ,x_msg_data OUT NOCOPY VARCHAR2
602 ) IS
603 BEGIN
604 SAVEPOINT RollBackPt;
605 fnd_msg_pub.initialize;
606 x_return_status := FND_API.G_RET_STS_SUCCESS;
607
608 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label(
609 p_tab_id => p_tab_id
610 ,p_tab_view_id => p_tab_view_id
611 ,p_object_id => p_object_id
612 ,p_object_type => c_type_hotspot
613 ,p_label_text => p_label_text
614 ,p_text_flag => 0
615 ,p_font_color => 1
616 ,p_font_size => 1
617 ,p_font_style => 1
618 ,p_left => p_left
619 ,p_top => p_top
620 ,p_width => p_width
621 ,p_height => p_height
622 ,p_note_text => NULL
623 ,p_link_id => NULL
624 ,p_function_id => NULL
625 ,x_return_status => x_return_status
626 ,x_msg_count => x_msg_count
627 ,x_msg_data => x_msg_data
628 );
629
630 EXCEPTION
631 WHEN others THEN
632 ROLLBACK TO RollBackPt;
633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
634 x_msg_data := SQLERRM;
635 END add_or_update_hotspot;
636
637 -- Add specified custom view link to BSC_TAB_VIEW_LABELS_PKG
638 PROCEDURE add_or_update_custom_view_link(
639 p_tab_id IN NUMBER
640 ,p_tab_view_id IN NUMBER
641 ,p_object_id IN NUMBER
642 ,p_text_flag IN NUMBER
643 ,p_label_text IN VARCHAR2
644 ,p_font_size IN NUMBER
645 ,p_font_style IN NUMBER
646 ,p_font_color IN NUMBER
647 ,p_left IN NUMBER
648 ,p_top IN NUMBER
649 ,p_width IN NUMBER
650 ,p_height IN NUMBER
651 ,p_link_tab_view_id IN NUMBER
652 ,x_return_status OUT NOCOPY VARCHAR2
653 ,x_msg_count OUT NOCOPY NUMBER
654 ,x_msg_data OUT NOCOPY VARCHAR2
655 ) IS
656 BEGIN
657 SAVEPOINT RollBackPt;
658 fnd_msg_pub.initialize;
659 x_return_status := FND_API.G_RET_STS_SUCCESS;
660
661 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label(
662 p_tab_id => p_tab_id
663 ,p_tab_view_id => p_tab_view_id
664 ,p_object_id => p_object_id
665 ,p_object_type => c_type_link
666 ,p_label_text => p_label_text
667 ,p_text_flag => p_text_flag
668 ,p_font_color => p_font_color
669 ,p_font_size => p_font_size
670 ,p_font_style => p_font_style
671 ,p_left => p_left
672 ,p_top => p_top
673 ,p_width => p_width
674 ,p_height => p_height
675 ,p_note_text => NULL
676 ,p_link_id => p_link_tab_view_id
677 ,p_function_id => NULL
678 ,x_return_status => x_return_status
679 ,x_msg_count => x_msg_count
680 ,x_msg_data => x_msg_data
681 );
682
683 EXCEPTION
684 WHEN others THEN
685 ROLLBACK TO RollBackPt;
686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
687 x_msg_data := SQLERRM;
688 END add_or_update_custom_view_link;
689
690 -- Add specified launchpad to BSC_TAB_VIEW_LABELS_PKG
691 PROCEDURE add_or_update_launch_pad(
692 p_tab_id IN NUMBER
693 ,p_tab_view_id IN NUMBER
694 ,p_object_id IN NUMBER
695 ,p_label_text IN VARCHAR2
696 ,p_note_text IN VARCHAR2
697 ,p_left IN NUMBER
698 ,p_top IN NUMBER
699 ,p_width IN NUMBER
700 ,p_height IN NUMBER
701 ,p_menu_id IN NUMBER
702 ,x_return_status OUT NOCOPY VARCHAR2
703 ,x_msg_count OUT NOCOPY NUMBER
704 ,x_msg_data OUT NOCOPY VARCHAR2
705 ) IS
706 BEGIN
707 SAVEPOINT RollBackPt;
708 fnd_msg_pub.initialize;
709 x_return_status := FND_API.G_RET_STS_SUCCESS;
710
711 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label(
712 p_tab_id => p_tab_id
713 ,p_tab_view_id => p_tab_view_id
714 ,p_object_id => p_object_id
715 ,p_object_type => c_type_launch_pad
716 ,p_label_text => p_label_text
717 ,p_text_flag => 0
718 ,p_font_color => -16777216
719 ,p_font_size => 1
720 ,p_font_style => 0
721 ,p_left => p_left
722 ,p_top => p_top
723 ,p_width => p_width
724 ,p_height => p_height
725 ,p_note_text => p_note_text
726 ,p_link_id => p_menu_id
727 ,p_function_id => NULL
728 ,x_return_status => x_return_status
729 ,x_msg_count => x_msg_count
730 ,x_msg_data => x_msg_data
731 );
732
733 EXCEPTION
734 WHEN others THEN
735 ROLLBACK TO RollBackPt;
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737 x_msg_data := SQLERRM;
738 END add_or_update_launch_pad;
739
740 -- Add specified measure (existing kpi) to BSC_TAB_VIEW_LABELS_PKG
741 PROCEDURE add_or_update_measure(
742 p_tab_id IN NUMBER
743 ,p_tab_view_id IN NUMBER
744 ,p_text_object_id IN NUMBER
745 ,p_text_flag IN NUMBER
746 ,p_font_size IN NUMBER
747 ,p_font_style IN NUMBER
748 ,p_font_color IN NUMBER
749 ,p_text_left IN NUMBER
750 ,p_text_top IN NUMBER
751 ,p_text_width IN NUMBER
752 ,p_text_height IN NUMBER
753 ,p_slider_object_id IN NUMBER
754 ,p_slider_flag IN NUMBER
755 ,p_slider_left IN NUMBER
756 ,p_slider_top IN NUMBER
757 ,p_slider_width IN NUMBER
758 ,p_slider_height IN NUMBER
759 ,p_actual_object_id IN NUMBER
760 ,p_actual_flag IN NUMBER
761 ,p_actual_left IN NUMBER
762 ,p_actual_top IN NUMBER
763 ,p_actual_width IN NUMBER
764 ,p_actual_height IN NUMBER
765 ,p_change_object_id IN NUMBER
766 ,p_change_flag IN NUMBER
767 ,p_change_left IN NUMBER
768 ,p_change_top IN NUMBER
769 ,p_change_width IN NUMBER
770 ,p_change_height IN NUMBER
771 ,p_indicator_id IN NUMBER
772 ,p_function_id IN NUMBER
773 ,x_return_status OUT NOCOPY VARCHAR2
774 ,x_msg_count OUT NOCOPY NUMBER
775 ,x_msg_data OUT NOCOPY VARCHAR2
776 ) IS
777 BEGIN
778 SAVEPOINT RollBackPt;
779 fnd_msg_pub.initialize;
780 x_return_status := FND_API.G_RET_STS_SUCCESS;
781
782 --add name/hotspot
783 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label(
784 p_tab_id => p_tab_id
785 ,p_tab_view_id => p_tab_view_id
786 ,p_object_id => p_text_object_id
787 ,p_object_type => c_type_measure
788 ,p_label_text => c_measure
789 ,p_text_flag => p_text_flag
790 ,p_font_color => p_font_color
791 ,p_font_size => p_font_size
792 ,p_font_style => p_font_style
793 ,p_left => p_text_left
794 ,p_top => p_text_top
795 ,p_width => p_text_width
796 ,p_height => p_text_height
797 ,p_note_text => NULL
798 ,p_link_id => p_indicator_id
799 ,p_function_id => p_function_id
800 ,x_return_status => x_return_status
801 ,x_msg_count => x_msg_count
802 ,x_msg_data => x_msg_data
803 );
804
805 --add actual
806 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label(
807 p_tab_id => p_tab_id
808 ,p_tab_view_id => p_tab_view_id
809 ,p_object_id => p_actual_object_id
810 ,p_object_type => c_type_measure_actual
811 ,p_label_text => c_measure_actual
812 ,p_text_flag => p_actual_flag
813 ,p_font_color => p_font_color
814 ,p_font_size => p_font_size
815 ,p_font_style => p_font_style
816 ,p_left => p_actual_left
817 ,p_top => p_actual_top
818 ,p_width => p_actual_width
819 ,p_height => p_actual_height
820 ,p_note_text => NULL
821 ,p_link_id => p_indicator_id
822 ,p_function_id => p_function_id
823 ,x_return_status => x_return_status
824 ,x_msg_count => x_msg_count
825 ,x_msg_data => x_msg_data
826 );
827
828 --add change
829 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label(
830 p_tab_id => p_tab_id
831 ,p_tab_view_id => p_tab_view_id
832 ,p_object_id => p_change_object_id
833 ,p_object_type => c_type_measure_change
834 ,p_label_text => c_measure_change
835 ,p_text_flag => p_change_flag
836 ,p_font_color => p_font_color
837 ,p_font_size => p_font_size
838 ,p_font_style => p_font_style
839 ,p_left => p_change_left
840 ,p_top => p_change_top
841 ,p_width => p_change_width
842 ,p_height => p_change_height
843 ,p_note_text => NULL
844 ,p_link_id => p_indicator_id
845 ,p_function_id => p_function_id
846 ,x_return_status => x_return_status
847 ,x_msg_count => x_msg_count
848 ,x_msg_data => x_msg_data
849 );
850
851 --add slider
852 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label(
853 p_tab_id => p_tab_id
854 ,p_tab_view_id => p_tab_view_id
855 ,p_object_id => p_slider_object_id
856 ,p_object_type => c_type_measure_slider
857 ,p_label_text => c_measure_slider
858 ,p_text_flag => p_slider_flag
859 ,p_font_color => p_font_color
860 ,p_font_size => p_font_size
861 ,p_font_style => p_font_style
862 ,p_left => p_slider_left
863 ,p_top => p_slider_top
864 ,p_width => p_slider_width
865 ,p_height => p_slider_height
866 ,p_note_text => NULL
867 ,p_link_id => p_indicator_id
868 ,p_function_id => p_function_id
869 ,x_return_status => x_return_status
870 ,x_msg_count => x_msg_count
871 ,x_msg_data => x_msg_data
872 );
873
874 EXCEPTION
875 WHEN others THEN
876 ROLLBACK TO RollBackPt;
877 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
878 x_msg_data := SQLERRM;
879 END add_or_update_measure;
880
881 -- Wrapper for calling BSC_TAB_VIEW_LABELS_PKG procedures
882 PROCEDURE add_or_update_tab_view_label(
883 p_tab_id IN NUMBER
884 ,p_tab_view_id IN NUMBER
885 ,p_object_id IN NUMBER
886 ,p_object_type IN NUMBER
887 ,p_label_text IN VARCHAR2
888 ,p_text_flag IN NUMBER
889 ,p_font_color IN NUMBER
890 ,p_font_size IN NUMBER
891 ,p_font_style IN NUMBER
892 ,p_left IN NUMBER
893 ,p_top IN NUMBER
894 ,p_width IN NUMBER
895 ,p_height IN NUMBER
896 ,p_note_text IN VARCHAR2
897 ,p_link_id IN NUMBER
898 ,p_function_id IN NUMBER
899 ,x_return_status OUT NOCOPY VARCHAR2
900 ,x_msg_count OUT NOCOPY NUMBER
901 ,x_msg_data OUT NOCOPY VARCHAR2
902 ) IS
903 l_str VARCHAR2(100);
904 l_count NUMBER;
905 l_object_id BSC_TAB_VIEW_LABELS_B.LABEL_ID%TYPE;
906 BEGIN
907 SAVEPOINT RollBackPt;
908 fnd_msg_pub.initialize;
909 x_return_status := FND_API.G_RET_STS_SUCCESS;
910
911 SELECT count(1) INTO l_count
912 FROM bsc_tab_view_labels_vl
913 WHERE tab_id = p_tab_id
914 AND tab_view_id = p_tab_view_id
915 AND label_id = p_object_id;
916
917 IF (l_count = 0) THEN
918 --create
919
920 --find the next label_id
921 SELECT max(label_id)+1 INTO l_object_id
922 FROM bsc_tab_view_labels_vl
923 WHERE tab_id = p_tab_id
924 AND tab_view_id = p_tab_view_id;
925
926 IF (l_object_id IS NULL) THEN
927 l_object_id := 0;
928 END IF;
929
930 BSC_TAB_VIEW_LABELS_PKG.INSERT_ROW (
931 X_ROWID => l_str,
932 X_TAB_ID => p_tab_id,
933 X_TAB_VIEW_ID => p_tab_view_id,
934 X_LABEL_ID => l_object_id,
935 X_LABEL_TYPE => p_object_type,
936 X_LINK_ID => p_link_id,
937 X_NAME => p_label_text,
938 X_NOTE => p_note_text,
939 X_TEXT_FLAG => p_text_flag,
940 X_LEFT_POSITION => p_left,
941 X_TOP_POSITION => p_top,
942 X_WIDTH => p_width,
943 X_HEIGHT => p_height,
944 X_FONT_SIZE => p_font_size,
945 X_FONT_STYLE => p_font_style,
946 X_FONT_COLOR => p_font_color,
947 X_URL => null,
948 X_FUNCTION_ID => p_function_id,
949 X_CREATION_DATE => SYSDATE,
950 X_CREATED_BY => fnd_global.user_id,
951 X_LAST_UPDATE_DATE => SYSDATE,
952 X_LAST_UPDATED_BY => fnd_global.user_id,
953 X_LAST_UPDATE_LOGIN => fnd_global.login_id
954 );
955 ELSE
956 --update
957 BSC_TAB_VIEW_LABELS_PKG.UPDATE_ROW (
958 X_TAB_ID => p_tab_id,
959 X_TAB_VIEW_ID => p_tab_view_id,
960 X_LABEL_ID => p_object_id,
961 X_LABEL_TYPE => p_object_type,
962 X_LINK_ID => p_link_id,
963 X_NAME => p_label_text,
964 X_NOTE => p_note_text,
965 X_TEXT_FLAG => p_text_flag,
966 X_LEFT_POSITION => p_left,
967 X_TOP_POSITION => p_top,
968 X_WIDTH => p_width,
969 X_HEIGHT => p_height,
970 X_FONT_SIZE => p_font_size,
971 X_FONT_STYLE => p_font_style,
972 X_FONT_COLOR => p_font_color,
973 X_URL => null,
974 X_FUNCTION_ID => p_function_id,
975 X_CREATION_DATE => SYSDATE,
976 X_CREATED_BY => fnd_global.user_id,
977 X_LAST_UPDATE_DATE => SYSDATE,
978 X_LAST_UPDATED_BY => fnd_global.user_id,
979 X_LAST_UPDATE_LOGIN => fnd_global.login_id
980 );
981 END IF;
982
983 EXCEPTION
984 WHEN others THEN
985 ROLLBACK TO RollBackPt;
986 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
987 x_msg_data := SQLERRM;
988 END add_or_update_tab_view_label;
989
990 -- Wrappers for calling BSC_TAB_VIEW_KPI_PKG
991 PROCEDURE add_or_update_tab_view_kpi(
992 p_tab_id IN NUMBER
993 ,p_tab_view_id IN NUMBER
994 ,p_kpi_id IN NUMBER
995 ,p_text_flag IN NUMBER
996 ,p_font_size IN NUMBER
997 ,p_font_style IN NUMBER
998 ,p_font_color IN NUMBER
999 ,p_hotspot_left IN NUMBER
1000 ,p_hotspot_top IN NUMBER
1001 ,p_hotspot_width IN NUMBER
1002 ,p_hotspot_height IN NUMBER
1003 ,p_alarm_left IN NUMBER
1004 ,p_alarm_top IN NUMBER
1005 ,p_alarm_width IN NUMBER
1006 ,p_alarm_height IN NUMBER
1007 ,x_return_status OUT NOCOPY VARCHAR2
1008 ,x_msg_count OUT NOCOPY NUMBER
1009 ,x_msg_data OUT NOCOPY VARCHAR2
1010 ) IS
1011 l_count NUMBER;
1012 l_str VARCHAR2(100);
1013 BEGIN
1014 SAVEPOINT RollBackPt;
1015 fnd_msg_pub.initialize;
1016 x_return_status := FND_API.G_RET_STS_SUCCESS;
1017
1018 SELECT count(1) INTO l_count
1019 FROM BSC_TAB_VIEW_KPI_VL
1020 WHERE TAB_ID = p_tab_id
1021 AND TAB_VIEW_ID = p_tab_view_id
1022 AND INDICATOR = p_kpi_id;
1023
1024 IF (l_count = 0) THEN
1025 --create
1026 BSC_TAB_VIEW_KPI_PKG.INSERT_ROW (
1027 X_ROWID => l_str,
1028 X_TAB_ID => p_tab_id,
1029 X_TAB_VIEW_ID => p_tab_view_id,
1030 X_INDICATOR => p_kpi_id,
1031 X_TEXT_FLAG => p_text_flag,
1032 X_LEFT_POSITION => p_hotspot_left,
1033 X_TOP_POSITION => p_hotspot_top,
1034 X_WIDTH => p_hotspot_width,
1035 X_HEIGHT => p_hotspot_height,
1036 X_FONT_SIZE => p_font_size,
1037 X_FONT_STYLE => p_font_style,
1038 X_FONT_COLOR => p_font_color,
1039 X_COLOR_LEFT_POSITION => p_alarm_left,
1040 X_COLOR_TOP_POSITION => p_alarm_top,
1041 X_COLOR_WIDTH => p_alarm_width,
1042 X_COLOR_HEIGHT => p_alarm_height,
1043 X_COLOR_SIZE => 0,
1044 X_CREATION_DATE => SYSDATE,
1045 X_CREATED_BY => fnd_global.user_id,
1046 X_LAST_UPDATE_DATE => SYSDATE,
1047 X_LAST_UPDATED_BY => fnd_global.user_id,
1048 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1049 );
1050 ELSE
1051 --update
1052 BSC_TAB_VIEW_KPI_PKG.UPDATE_ROW(
1053 X_TAB_ID => p_tab_id,
1054 X_TAB_VIEW_ID => p_tab_view_id,
1055 X_INDICATOR => p_kpi_id,
1056 X_TEXT_FLAG => p_text_flag,
1057 X_LEFT_POSITION => p_hotspot_left,
1058 X_TOP_POSITION => p_hotspot_top,
1059 X_WIDTH => p_hotspot_width,
1060 X_HEIGHT => p_hotspot_height,
1061 X_FONT_SIZE => p_font_size,
1062 X_FONT_STYLE => p_font_style,
1063 X_FONT_COLOR => p_font_color,
1064 X_COLOR_LEFT_POSITION => p_alarm_left,
1065 X_COLOR_TOP_POSITION => p_alarm_top,
1066 X_COLOR_WIDTH => p_alarm_width,
1067 X_COLOR_HEIGHT => p_alarm_height,
1068 X_COLOR_SIZE => 0,
1069 X_CREATION_DATE => SYSDATE,
1070 X_CREATED_BY => fnd_global.user_id,
1071 X_LAST_UPDATE_DATE => SYSDATE,
1072 X_LAST_UPDATED_BY => fnd_global.user_id,
1073 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1074 );
1075 END IF;
1076
1077 EXCEPTION
1078 WHEN others THEN
1079 ROLLBACK TO RollBackPt;
1080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1081 x_msg_data := SQLERRM;
1082 END add_or_update_tab_view_kpi;
1083
1084 --Create a new image for the specified p_tab_id and p_tab_view_id
1085 PROCEDURE create_tab_view_bg (
1086 p_tab_id IN NUMBER
1087 ,p_tab_view_id IN NUMBER
1088 ,p_file_name IN VARCHAR2
1089 ,p_description IN VARCHAR2
1090 ,p_width IN NUMBER
1091 ,p_height IN NUMBER
1092 ,p_mime_type IN VARCHAR2
1093 ,x_image_id OUT NOCOPY NUMBER
1094 ,x_return_status OUT NOCOPY VARCHAR2
1095 ,x_msg_count OUT NOCOPY NUMBER
1096 ,x_msg_data OUT NOCOPY VARCHAR2
1097 ) IS
1098 l_next_image_id NUMBER;
1099 l_str VARCHAR2(100);
1100 BEGIN
1101 SAVEPOINT RollBackPt;
1102 fnd_msg_pub.initialize;
1103 x_return_status := FND_API.G_RET_STS_SUCCESS;
1104
1105 SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL INTO l_next_image_id FROM dual;
1106 x_image_id := l_next_image_id;
1107
1108 BEGIN
1109 BSC_SYS_IMAGES_PKG.INSERT_ROW (
1110 X_IMAGE_ID => l_next_image_id,
1111 X_FILE_NAME => p_file_name,
1112 X_DESCRIPTION => p_description,
1113 X_WIDTH => p_width,
1114 X_HEIGHT => p_height,
1115 X_MIME_TYPE => p_mime_type,
1116 X_CREATED_BY => fnd_global.user_id,
1117 X_LAST_UPDATED_BY => fnd_global.user_id,
1118 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1119 );
1120
1121 EXCEPTION
1122 WHEN others THEN
1123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1124 x_msg_data := 'Insertion to BSC_SYS_IMAGES_PKG failed' || SQLERRM;
1125 RAISE;
1126 END;
1127
1128 BSC_SYS_IMAGES_MAP_PKG.INSERT_ROW (
1129 X_ROWID => l_str,
1130 X_SOURCE_TYPE => 1,
1131 X_SOURCE_CODE => p_tab_id,
1132 X_TYPE => p_tab_view_id,
1133 X_IMAGE_ID => l_next_image_id,
1134 X_CREATION_DATE => SYSDATE,
1135 X_CREATED_BY => fnd_global.user_id,
1136 X_LAST_UPDATE_DATE => SYSDATE,
1137 X_LAST_UPDATED_BY => fnd_global.user_id,
1138 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1139 );
1140 EXCEPTION
1141 WHEN others THEN
1142 ROLLBACK TO RollBackPt;
1143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1144 x_msg_data := SQLERRM;
1145 RAISE;
1146 END create_tab_view_bg;
1147
1148
1149 -- Create or udpate tab view's background in BSC_SYS_IMAGES and BSC_SYS_IMAGES_MAP_PKG
1150 PROCEDURE add_or_update_tab_view_bg (
1151 p_tab_id IN NUMBER
1152 ,p_tab_view_id IN NUMBER
1153 ,p_image_id IN NUMBER
1154 ,p_file_name IN VARCHAR2
1155 ,p_description IN VARCHAR2
1156 ,p_width IN NUMBER
1157 ,p_height IN NUMBER
1158 ,p_mime_type IN VARCHAR2
1159 ,x_image_id OUT NOCOPY NUMBER
1160 ,x_return_status OUT NOCOPY VARCHAR2
1161 ,x_msg_count OUT NOCOPY NUMBER
1162 ,x_msg_data OUT NOCOPY VARCHAR2
1163 ) IS
1164 l_count NUMBER;
1165 l_next_image_id NUMBER;
1166 l_str VARCHAR2(100);
1167 l_temp VARCHAR2(100);
1168 BEGIN
1169 SAVEPOINT RollBackPt;
1170 fnd_msg_pub.initialize;
1171 x_return_status := FND_API.G_RET_STS_SUCCESS;
1172
1173 SELECT count(1) INTO l_count
1174 FROM BSC_SYS_IMAGES bsi, BSC_SYS_IMAGES_MAP_VL bsim
1175 WHERE bsim.source_code = p_tab_id
1176 AND bsim.type = p_tab_view_id
1177 AND bsim.image_id = p_image_id
1178 AND bsim.image_id = bsi.image_id;
1179
1180 if (l_count > 0)
1181 THEN
1182 --check if the image is owned by current NLS session
1183 SELECT count(1) INTO l_count
1184 FROM BSC_SYS_IMAGES_MAP_TL
1185 WHERE source_code = p_tab_id
1186 AND type = p_tab_view_id
1187 AND image_id = p_image_id
1188 AND source_lang = userenv('LANG');
1189
1190 IF (l_count > 0) THEN
1191 --image owned by this NLS session, just simply update the same image
1192 x_image_id := p_image_id;
1193
1194 BEGIN
1195 UPDATE BSC_SYS_IMAGES
1196 SET FILE_NAME = p_file_name,
1197 DESCRIPTION = p_description,
1198 WIDTH = p_width,
1199 HEIGHT = p_height,
1200 MIME_TYPE = p_mime_type,
1201 LAST_UPDATE_DATE = SYSDATE,
1202 LAST_UPDATED_BY = fnd_global.user_id,
1203 LAST_UPDATE_LOGIN = fnd_global.login_id,
1204 FILE_BODY = EMPTY_BLOB()
1205 WHERE IMAGE_ID = p_image_id
1206 AND LAST_UPDATE_DATE <= SYSDATE;
1207 EXCEPTION
1208 WHEN others THEN
1209 ROLLBACK TO RollBackPt;
1210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1211 x_msg_data := 'Update to BSC_SYS_IMAGES failed' || SQLERRM;
1212 RETURN;
1213 END;
1214
1215 BSC_SYS_IMAGES_MAP_PKG.UPDATE_ROW (
1216 X_SOURCE_TYPE => 1,
1217 X_SOURCE_CODE => p_tab_id,
1218 X_TYPE => p_tab_view_id,
1219 X_IMAGE_ID => p_image_id,
1220 X_CREATION_DATE => SYSDATE,
1221 X_CREATED_BY => fnd_global.user_id,
1222 X_LAST_UPDATE_DATE => SYSDATE,
1223 X_LAST_UPDATED_BY => fnd_global.user_id,
1224 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1225 );
1226
1227 ELSE
1228 --image not owned by this NLS session, need to create a new image and update the image map
1229 SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL INTO l_next_image_id FROM dual;
1230 x_image_id := l_next_image_id;
1231
1232 BEGIN
1233 BSC_SYS_IMAGES_PKG.INSERT_ROW (
1234 X_IMAGE_ID => l_next_image_id,
1235 X_FILE_NAME => p_file_name,
1236 X_DESCRIPTION => p_description,
1237 X_WIDTH => p_width,
1238 X_HEIGHT => p_height,
1239 X_MIME_TYPE => p_mime_type,
1240 X_CREATED_BY => fnd_global.user_id,
1241 X_LAST_UPDATED_BY => fnd_global.user_id,
1242 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1243 );
1244
1245 EXCEPTION
1246 WHEN others THEN
1247 ROLLBACK TO RollBackPt;
1248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1249 x_msg_data := 'Insertion to BSC_SYS_IMAGES_PKG failed' || SQLERRM;
1250 RETURN;
1251 END;
1252
1253 BSC_SYS_IMAGES_MAP_PKG.UPDATE_ROW (
1254 X_SOURCE_TYPE => 1,
1255 X_SOURCE_CODE => p_tab_id,
1256 X_TYPE => p_tab_view_id,
1257 X_IMAGE_ID => l_next_image_id,
1258 X_CREATION_DATE => SYSDATE,
1259 X_CREATED_BY => fnd_global.user_id,
1260 X_LAST_UPDATE_DATE => SYSDATE,
1261 X_LAST_UPDATED_BY => fnd_global.user_id,
1262 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1263 );
1264 END IF;
1265
1266 ELSE
1267 --create a new image for this custom view
1268 create_tab_view_bg (
1269 p_tab_id => p_tab_id
1270 ,p_tab_view_id => p_tab_view_id
1271 ,p_file_name => p_file_name
1272 ,p_description => p_description
1273 ,p_width => p_width
1274 ,p_height => p_height
1275 ,p_mime_type => p_mime_type
1276 ,x_image_id => x_image_id
1277 ,x_return_status => x_return_status
1278 ,x_msg_count => x_msg_count
1279 ,x_msg_data => x_msg_data
1280 );
1281 END IF;
1282
1283 EXCEPTION
1284 WHEN others THEN
1285 ROLLBACK TO RollBackPt;
1286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1287 x_msg_data := SQLERRM;
1288 END add_or_update_tab_view_bg;
1289
1290 -- Create or update tab view properties in BSC_TAB_VIEWS_PKG
1291 PROCEDURE add_or_update_tab_view (
1292 p_tab_id IN NUMBER
1293 ,p_tab_view_id IN NUMBER
1294 ,p_name IN VARCHAR2
1295 ,p_func_area_short_name IN VARCHAR2
1296 ,p_internal_name IN VARCHAR2
1297 ,p_description IN VARCHAR2
1298 ,p_enable_flag IN NUMBER
1299 ,p_create_form_func IN VARCHAR2
1300 ,p_last_update_date IN VARCHAR2
1301 ,x_return_status OUT NOCOPY VARCHAR2
1302 ,x_msg_count OUT NOCOPY NUMBER
1303 ,x_msg_data OUT NOCOPY VARCHAR2
1304 ) IS
1305 l_count NUMBER;
1306 l_str VARCHAR2(100);
1307 l_flag NUMBER;
1308 l_function_id NUMBER;
1309 l_enabled_flag BSC_TAB_VIEWS_B.enabled_flag%TYPE;
1310 BEGIN
1311 SAVEPOINT RollBackPt;
1312 fnd_msg_pub.initialize;
1313 x_return_status := FND_API.G_RET_STS_SUCCESS;
1314 l_flag := 0;
1315
1316 IF (is_tab_view_exist(p_tab_id, p_tab_view_id) = 'Y')
1317 THEN
1318 /********************************************
1319 Because tab_view id already exists then first get
1320 the enbaled flag for this custom view.
1321 /*******************************************/
1322 SELECT NVL(ENABLED_FLAG,1)
1323 INTO l_enabled_flag
1324 FROM BSC_TAB_VIEWS_VL
1325 WHERE tab_id = p_tab_id
1326 AND tab_view_id = p_tab_view_id;
1327
1328
1329 BSC_TAB_VIEWS_PKG.UPDATE_ROW(
1330 X_TAB_ID => p_tab_id,
1331 X_TAB_VIEW_ID => p_tab_view_id,
1332 X_ENABLED_FLAG => l_enabled_flag,
1333 X_NAME => p_name,
1334 X_HELP => p_description,
1335 X_LAST_UPDATE_DATE => SYSDATE,
1336 X_LAST_UPDATED_BY => fnd_global.user_id,
1337 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1338 );
1339 ELSE
1340 BEGIN
1341 BSC_TAB_VIEWS_PKG.INSERT_ROW(
1342 X_ROWID => l_str,
1343 X_TAB_ID => p_tab_id,
1344 X_TAB_VIEW_ID => p_tab_view_id,
1345 X_ENABLED_FLAG => p_enable_flag,
1346 X_NAME => p_name,
1347 X_HELP => p_description,
1348 X_CREATION_DATE => SYSDATE,
1349 X_CREATED_BY => fnd_global.user_id,
1350 X_LAST_UPDATE_DATE => SYSDATE,
1351 X_LAST_UPDATED_BY => fnd_global.user_id,
1352 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1353 );
1354
1355 EXCEPTION
1356 WHEN others THEN
1357 ROLLBACK TO RollBackPt;
1358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1359 x_msg_data := 'Insertion to BSC_TAB_VIEWS_PKG failed' || SQLERRM;
1360 RETURN;
1361 END;
1362
1363 UPDATE BSC_TABS_B
1364 SET LAST_UPDATE_DATE = SYSDATE,
1365 LAST_UPDATED_BY = fnd_global.user_id,
1366 LAST_UPDATE_LOGIN = fnd_global.login_id
1367 WHERE TAB_ID = p_tab_id;
1368 END IF;
1369
1370 -- Enh 3934298, for each new custom view will create/update a form function to use in DBI
1371 /*****************************************************************
1372 For custom view simulation Enhancement,following are the requirements.
1373 1.Making CVD an Independent Entity,so that it can be called from Report Designer also.
1374 2.When called from Report Designer we don't need to create the form function
1375 p_create_form_Func is added to check if we need to create the form fucntion or not.
1376 If (p_create_form_Func = FND_API.G_FALSE)Then we will not create the form function
1377 and when it is set to FND_API.G_TRUE then we will create the form fucntion
1378 /*****************************************************************/
1379 IF (p_create_form_func = FND_API.G_TRUE) THEN
1380 -- Enh 3934298, for each new custom view will create/update a form function to use in DBI
1381 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_function
1382 (p_tab_id => p_tab_id,
1383 p_tab_view_id => p_tab_view_id,
1384 p_name => p_name,
1385 p_internal_name => p_internal_name,
1386 p_description => p_description,
1387 x_function_id => l_function_id,
1388 x_return_status => x_return_status ,
1389 x_msg_count => x_msg_count,
1390 x_msg_data => x_msg_data
1391 );
1392
1393 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1394 --DBMS_OUTPUT.PUT_LINE('BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view Failed: at BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_function');
1395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1396 END IF;
1397
1398 -- Maps Functional area short name.
1399 IF (p_Func_Area_Short_Name IS NOT NULL) THEN
1400 BIS_OBJECT_EXTENSIONS_PUB.Object_Funct_Area_Map
1401 (p_Api_Version => 1.0,
1402 p_Commit => FND_API.G_FALSE,
1403 p_Obj_Type => BSC_UTILITY.BSC_CUSTOM_VIEW,
1404 p_Obj_Name => p_internal_name,
1405 p_App_Id => BSC_UTILITY.BSC_APP_ID,
1406 p_Func_Area_Sht_Name =>p_func_area_short_name,
1407 x_return_status => x_return_status,
1408 x_msg_count => x_msg_count,
1409 x_msg_data => x_msg_data
1410 );
1411
1412 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1413 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1414 END IF;
1415 END IF;
1416
1417 END IF;
1418 EXCEPTION
1419 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1420 ROLLBACK TO RollBackPt;
1421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1422 if (l_flag = 1)
1423 THEN
1424 x_msg_data := 'INVALID_TIMESTAMP';
1425 ELSE
1426 x_msg_data := SQLERRM;
1427 END IF;
1428
1429 WHEN others THEN
1430 ROLLBACK TO RollBackPt;
1431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1432 x_msg_data := SQLERRM;
1433
1434 END add_or_update_tab_view;
1435
1436 -- Create or update tab view properties in BSC_TAB_VIEWS_PKG
1437 -- Called from UI for the extra original Name
1438 PROCEDURE add_or_update_tab_view (
1439 p_tab_id IN NUMBER
1440 ,p_tab_view_id IN NUMBER
1441 ,p_name IN VARCHAR2
1442 ,p_func_area_short_name IN VARCHAR2
1443 ,p_internal_name IN VARCHAR2
1444 ,p_description IN VARCHAR2
1445 ,p_enable_flag IN NUMBER
1446 ,p_is_default_int_name IN VARCHAR2
1447 ,p_create_form_func IN VARCHAR2
1448 ,p_last_update_date IN VARCHAR2
1449 ,x_return_status OUT NOCOPY VARCHAR2
1450 ,x_msg_count OUT NOCOPY NUMBER
1451 ,x_msg_data OUT NOCOPY VARCHAR2
1452 ) IS
1453 l_count NUMBER;
1454 l_internal_name FND_FORM_FUNCTIONS_VL.FUNCTION_NAME%TYPE;
1455 BEGIN
1456
1457 l_internal_name := p_internal_name;
1458 -- for the new view check if the internal name already exists
1459 IF (is_tab_view_exist(p_tab_id, p_tab_view_id) = 'N') THEN
1460 SELECT COUNT(0)
1461 INTO l_count
1462 FROM FND_FORM_FUNCTIONS_VL
1463 WHERE FUNCTION_NAME = p_internal_name;
1464
1465 IF (l_count <> 0) THEN
1466 -- If the user has not changed the default internal name then insert the next available
1467 IF (p_is_default_int_name = FND_API.G_TRUE) THEN
1468 l_internal_name := 'BSC_PORTLET_CUSTOM_VIEW_'||p_tab_id||'_'||next_custom_view_id(p_tab_id);
1469 ELSE
1470 -- If the user has changed the internal name and it already exists then will show warning
1471 x_msg_data := BSC_UTILITY.INVALID_CUST_VIEW_NAME;
1472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1473 END IF;
1474
1475 END IF;
1476 END IF;
1477 add_or_update_tab_view (
1478 p_tab_id => p_tab_id
1479 ,p_tab_view_id => p_tab_view_id
1480 ,p_name => p_name
1481 ,p_func_area_short_name => p_func_area_short_name
1482 ,p_internal_name => l_internal_name
1483 ,p_description => p_description
1484 ,p_enable_flag => p_enable_flag
1485 ,p_create_form_func => p_create_form_func
1486 ,p_last_update_date => p_last_update_date
1487 ,x_return_status => x_return_status
1488 ,x_msg_count => x_msg_count
1489 ,x_msg_data => x_msg_data
1490 );
1491
1492 EXCEPTION
1493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1495 WHEN others THEN
1496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1497 END add_or_update_tab_view;
1498
1499 -- Check if the given tab view exists, return 'Y' if it exists, 'N' otherwise
1500 FUNCTION is_tab_view_exist (
1501 p_tab_id IN NUMBER
1502 ,p_tab_view_id IN NUMBER
1503 ) RETURN VARCHAR2 IS
1504 l_count NUMBER;
1505 BEGIN
1506
1507 SELECT count(tab_id) INTO l_count
1508 FROM bsc_tab_views_vl
1509 WHERE tab_id = p_tab_id AND tab_view_id = p_tab_view_id;
1510
1511 IF (l_count > 0)
1512 THEN
1513 RETURN 'Y';
1514 ELSE
1515 RETURN 'N';
1516 END IF;
1517
1518 END is_tab_view_exist;
1519
1520 -- Compare given tab view timestamp with that in DB. Return 0 if it is the
1521 -- same, 1 otherwise.
1522 FUNCTION compare_tab_view_timestamp (
1523 p_tab_id IN NUMBER
1524 ,p_tab_view_id IN NUMBER
1525 ,p_last_update_date IN VARCHAR2
1526 ) RETURN NUMBER IS
1527 l_last_update_date VARCHAR2(100);
1528 BEGIN
1529
1530 SELECT to_char(last_update_date,'YY/MM/DD-HH24:MM:SS') into l_last_update_date
1531 FROM bsc_tab_views_vl
1532 WHERE tab_id = p_tab_id AND tab_view_id = p_tab_view_id;
1533
1534 IF (p_last_update_date <> l_last_update_date)
1535 THEN
1536 RETURN 1;
1537 ELSE
1538 RETURN 0;
1539 END IF;
1540
1541 END compare_tab_view_timestamp;
1542
1543 FUNCTION get_function_name_string (
1544 p_tab_id IN NUMBER
1545 ,p_tab_view_id IN NUMBER
1546 ) RETURN VARCHAR2 IS
1547
1548 x_function_name VARCHAR(30);
1549
1550 BEGIN
1551 x_function_name := 'BSC_PORTLET_CUSTOM_VIEW_' || p_tab_id || '_' || p_tab_view_id;
1552 return x_function_name;
1553
1554 end get_function_name_string;
1555
1556 FUNCTION get_function_params_string (
1557 p_tab_id IN NUMBER
1558 ,p_tab_view_id IN NUMBER
1559 ) RETURN VARCHAR2 IS
1560
1561 x_parameters VARCHAR(2000);
1562
1563 BEGIN
1564 x_parameters := 'pRequestType=C&pTabId=' || p_tab_id || '&pViewId=' || p_tab_view_id;
1565 return x_parameters;
1566
1567 end get_function_params_string;
1568
1569 -- Returns the Parameter String
1570 FUNCTION get_param_search_string (
1571 p_tab_id IN NUMBER
1572 ,p_tab_view_id IN NUMBER
1573 ) RETURN VARCHAR2 IS
1574
1575 x_parameters VARCHAR(2000);
1576
1577 BEGIN
1578 x_parameters := '%pTabId=' || p_tab_id || '&pViewId=' || p_tab_view_id||'%';
1579 return x_parameters;
1580
1581 end get_param_search_string;
1582
1583 procedure add_or_update_function (
1584 p_tab_id IN NUMBER
1585 ,p_tab_view_id IN NUMBER
1586 ,p_name IN VARCHAR2
1587 ,p_internal_name IN VARCHAR2 := NULL
1588 ,p_description IN VARCHAR2
1589 ,x_function_id OUT NOCOPY NUMBER
1590 ,x_return_status OUT NOCOPY VARCHAR2
1591 ,x_msg_count OUT NOCOPY NUMBER
1592 ,x_msg_data OUT NOCOPY VARCHAR2
1593 ) is
1594
1595 l_rowid VARCHAR2(30);
1596 l_new_function_id NUMBER;
1597 l_parameters VARCHAR2(2000);
1598 l_function_name VARCHAR2(30);
1599 l_count NUMBER;
1600
1601 begin
1602
1603 l_parameters := get_function_params_string(p_tab_id,p_tab_view_id);
1604
1605 -- assign default function name if internal name is null
1606 IF (p_internal_name IS NULL) THEN
1607 l_function_name := get_function_name_string(p_tab_id, p_tab_view_id);
1608 ELSE
1609 l_function_name := p_internal_name;
1610 END IF;
1611
1612 select count(FUNCTION_ID) into l_count
1613 from FND_FORM_FUNCTIONS
1614 where FUNCTION_NAME = l_function_name;
1615
1616 -- check if function has already been created
1617 IF (l_count =0) THEN
1618 select FND_FORM_FUNCTIONS_S.NEXTVAL into l_new_function_id from dual;
1619
1620 FND_FORM_FUNCTIONS_PKG.INSERT_ROW(
1621 X_ROWID => l_ROWID,
1622 X_FUNCTION_ID => l_new_function_id,
1623 X_WEB_HOST_NAME => null,
1624 X_WEB_AGENT_NAME => null,
1625 X_WEB_HTML_CALL => C_FUNC_WEB_HTML_CALL,
1626 X_WEB_ENCRYPT_PARAMETERS => 'N',
1627 X_WEB_SECURED => 'N',
1628 X_WEB_ICON => null,
1629 X_OBJECT_ID => null,
1630 X_REGION_APPLICATION_ID => C_FUNC_REGION_APPLICATION_ID,
1631 X_REGION_CODE => C_FUNC_REGION_CODE,
1632 X_FUNCTION_NAME => l_function_name,
1633 X_APPLICATION_ID => null,
1634 X_FORM_ID => null,
1635 X_PARAMETERS => l_parameters,
1636 X_TYPE => C_FUNC_TYPE,
1637 X_USER_FUNCTION_NAME => p_name,
1638 X_DESCRIPTION => p_description,
1639 X_CREATION_DATE => sysdate,
1640 X_CREATED_BY => fnd_global.user_id,
1641 X_LAST_UPDATE_DATE => sysdate,
1642 X_LAST_UPDATED_BY => fnd_global.user_id,
1643 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
1644 X_MAINTENANCE_MODE_SUPPORT => NULL,
1645 X_CONTEXT_DEPENDENCE => NULL);
1646
1647 if l_ROWID is not null then
1648 x_function_id := l_new_function_id;
1649 end if;
1650 ELSE
1651 select FUNCTION_ID into x_function_id from FND_FORM_FUNCTIONS where FUNCTION_NAME = l_function_name;
1652
1653 FND_FORM_FUNCTIONS_PKG.UPDATE_ROW
1654 (
1655 X_FUNCTION_ID => x_function_id
1656 ,X_WEB_HOST_NAME => NULL
1657 ,X_WEB_AGENT_NAME => NULL
1658 ,X_WEB_HTML_CALL => C_FUNC_WEB_HTML_CALL
1659 ,X_WEB_ENCRYPT_PARAMETERS => 'N'
1660 ,X_WEB_SECURED => 'N'
1661 ,X_WEB_ICON => NULL
1662 ,X_OBJECT_ID => NULL
1663 ,X_REGION_APPLICATION_ID => C_FUNC_REGION_APPLICATION_ID
1664 ,X_REGION_CODE => C_FUNC_REGION_CODE
1665 ,X_FUNCTION_NAME => l_function_name
1666 ,X_APPLICATION_ID => NULL
1667 ,X_FORM_ID => NULL
1668 ,X_PARAMETERS => l_parameters
1669 ,X_TYPE => C_FUNC_TYPE
1670 ,X_USER_FUNCTION_NAME => p_name
1671 ,X_DESCRIPTION => p_description
1672 ,X_LAST_UPDATE_DATE => SYSDATE
1673 ,X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
1674 ,X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
1675 );
1676 END IF;
1677
1678 x_return_status := FND_API.G_RET_STS_SUCCESS;
1679
1680 EXCEPTION
1681 WHEN FND_API.G_EXC_ERROR THEN
1682 x_return_status := FND_API.G_RET_STS_ERROR;
1683 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1684 ,p_data => x_msg_data);
1685 WHEN OTHERS THEN
1686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1687 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1688 ,p_data => x_msg_data);
1689 if (x_msg_data is null) then
1690 x_msg_data := 'BSC_CUSTOM_VIEW_UI_WRAPPER.create_function' || SQLERRM;
1691 end if;
1692
1693 end add_or_update_function;
1694
1695 procedure delete_function (
1696 p_tab_id IN NUMBER
1697 ,p_tab_view_id IN NUMBER
1698 ,x_return_status OUT NOCOPY VARCHAR2
1699 ,x_msg_count OUT NOCOPY NUMBER
1700 ,x_msg_data OUT NOCOPY VARCHAR2
1701 ) is
1702
1703 l_function_id NUMBER;
1704 l_param_search_string FND_FORM_FUNCTIONS_VL.PARAMETERS%TYPE;
1705 l_object_name FND_FORM_FUNCTIONS_VL.FUNCTION_NAME%TYPE;
1706 l_count NUMBER;
1707
1708 CURSOR c_verify_function IS
1709 SELECT FUNCTION_ID, FUNCTION_NAME
1710 FROM FND_FORM_FUNCTIONS
1711 WHERE PARAMETERS LIKE l_param_search_string;
1712
1713 BEGIN
1714 fnd_msg_pub.initialize;
1715 x_return_status := FND_API.G_RET_STS_SUCCESS;
1716 -- Constracts the PARAMETER to find out FUNCTION_NAME
1717 l_param_search_string := get_param_search_string(p_tab_id,p_tab_view_id);
1718
1719 FOR cd IN c_verify_function LOOP
1720 l_function_id := cd.function_id;
1721 l_object_name := cd.function_name;
1722
1723 FND_FORM_FUNCTIONS_PKG.DELETE_ROW(l_function_id);
1724 -- Checks if the functional_area alredy exists.
1725 SELECT COUNT(0)
1726 INTO l_count
1727 FROM bis_form_function_extension_vl
1728 WHERE object_name= l_object_name;
1729
1730 IF(l_count > 0) THEN
1731 BIS_OBJECT_EXTENSIONS_PUB.Object_Funct_Area_Map( p_Api_Version => 1.0,
1732 p_Commit => FND_API.G_FALSE,
1733 p_Obj_Type => BSC_UTILITY.BSC_CUSTOM_VIEW,
1734 p_Obj_Name => l_object_name,
1735 p_App_Id => BSC_UTILITY.BSC_APP_ID,
1736 p_Func_Area_Sht_Name =>NULL,
1737 x_return_status => x_return_status,
1738 x_msg_count => x_msg_count,
1739 x_msg_data => x_msg_data);
1740
1741 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1743 END IF;
1744 END IF;
1745 END LOOP;
1746
1747 EXCEPTION
1748 WHEN NO_DATA_FOUND THEN
1749 x_return_status := FND_API.G_RET_STS_ERROR;
1750 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1751 ,p_data => x_msg_data);
1752 if (x_msg_data is null) then
1753 x_msg_data := 'BSC_CUSTOM_VIEW_UI_WRAPPER.delete_function: no Row Found: ' || SQLERRM;
1754 end if;
1755
1756 WHEN FND_API.G_EXC_ERROR THEN
1757 x_return_status := FND_API.G_RET_STS_ERROR;
1758 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1759 ,p_data => x_msg_data);
1760 WHEN OTHERS THEN
1761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1762 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1763 ,p_data => x_msg_data);
1764 if (x_msg_data is null) then
1765 x_msg_data := 'BSC_CUSTOM_VIEW_UI_WRAPPER.delete_function' || SQLERRM;
1766 end if;
1767
1768 end delete_function;
1769
1770 -- Returns the next_custom_view_id
1771 FUNCTION next_custom_view_id (
1772 p_tab_id IN NUMBER
1773 ) RETURN NUMBER IS
1774 l_view_id BSC_TAB_VIEWS_VL.tab_view_id%TYPE;
1775 l_internal_name FND_FORM_FUNCTIONS_VL.function_name%TYPE;
1776
1777 BEGIN
1778 SELECT NVL(MAX(tab_view_id),1)
1779 INTO l_view_id
1780 FROM BSC_TAB_VIEWS_B
1781 WHERE tab_id = p_tab_id;
1782
1783 RETURN l_view_id;
1784 END next_custom_view_id;
1785
1786 /**********************************************************
1787 Name : Get_Or_CreateNew_Scorecard
1788 Description : This API Checks if the scorecard was created for the report or not.
1789 If not then it it will create a new scorecard and return the tab_id
1790 Input Parameters : p_report_sht_name --> Report short name
1791 p_resp_Id --> Reposibility
1792 x_tab_Id --> tab id of the scorecard.
1793
1794 Created By : ashankar
1795 /*********************************************************/
1796
1797 PROCEDURE Get_Or_CreateNew_Scorecard
1798 (
1799 p_report_sht_name IN VARCHAR
1800 , p_resp_Id IN NUMBER
1801 , p_time_stamp IN VARCHAR2
1802 , p_Application_Id IN NUMBER
1803 , x_time_stamp OUT NOCOPY VARCHAR2
1804 , x_tab_Id OUT NOCOPY NUMBER
1805 , x_return_status OUT NOCOPY VARCHAR2
1806 , x_msg_count OUT NOCOPY NUMBER
1807 , x_msg_data OUT NOCOPY VARCHAR2
1808 )IS
1809 l_count NUMBER;
1810 l_tabId BSC_TABS_B.tab_Id%TYPE;
1811 l_tab_name BSC_TABS_VL.name%TYPE;
1812 l_Time_Stamp DATE;
1813 l_last_update_date DATE;
1814 -- Date Format used in Report Designer.
1815 l_last_upd_format VARCHAR2(25):= 'YYYY/MM/DD-HH24:MI:SS';
1816 BEGIN
1817 FND_MSG_PUB.INITIALIZE;
1818 x_return_status := FND_API.G_RET_STS_SUCCESS;
1819
1820 IF (p_time_stamp IS NOT NULL) THEN
1821 SELECT LAST_UPDATE_DATE
1822 INTO l_last_update_date
1823 FROM AK_REGIONS
1824 WHERE REGION_CODE = p_report_sht_name
1825 AND REGION_APPLICATION_ID = p_Application_Id;
1826
1827 IF(p_time_stamp <> TO_CHAR(l_last_update_date, l_last_upd_format)) THEN
1828 FND_MSG_PUB.Initialize;
1829 FND_MESSAGE.SET_NAME('BSC', 'BSC_INVALID_RPT_TIMESTAMP');
1830 FND_MSG_PUB.ADD;
1831 x_return_status := FND_API.G_RET_STS_ERROR;
1832 x_msg_data := 'BSC_INVALID_RPT_TIMESTAMP';
1833 RAISE FND_API.G_EXC_ERROR;
1834 END IF;
1835 END IF;
1836
1837 l_Time_Stamp := SYSDATE;
1838 --DBMS_OUTPUT.PUT_LINE('latest timestamp = ' || TO_CHAR(l_Time_Stamp, l_last_upd_format));
1839
1840 UPDATE AK_REGIONS A
1841 SET A.LAST_UPDATE_DATE = l_Time_Stamp
1842 WHERE A.REGION_CODE = p_report_sht_name
1843 AND REGION_APPLICATION_ID = p_Application_Id;
1844
1845
1846 SELECT COUNT(0)
1847 INTO l_count
1848 FROM BSC_TABS_B
1849 WHERE SHORT_NAME = p_report_sht_name;
1850
1851 IF(l_count=0)THEN
1852
1853 SELECT Name
1854 INTO l_tab_name
1855 FROM AK_REGIONS_VL
1856 WHERE REGION_CODE = p_report_sht_name;
1857
1858 l_tab_name := BSC_BIS_CUSTOM_KPI_UTIL_PUB.Get_Unqiue_Tab_Name(l_tab_name);
1859 BSC_PMF_UI_WRAPPER.Create_Tab
1860 (
1861 p_Commit => FND_API.G_FALSE
1862 , p_Responsibility_Id => p_resp_Id
1863 , p_Parent_Tab_Id => NULL
1864 , p_Owner_Id => NULL
1865 , p_Short_Name => p_report_sht_name
1866 , x_Tab_Id => l_tabId
1867 , x_Return_Status => x_return_status
1868 , x_Msg_Count => x_msg_count
1869 , x_Msg_Data => x_msg_data
1870 , p_Tab_Name => l_tab_name
1871 , p_Tab_Help => NULL
1872 , p_Tab_Info => NULL
1873 );
1874 IF (x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1876 END IF;
1877 ELSE
1878 SELECT tab_Id
1879 INTO l_tabId
1880 FROM BSC_TABS_B
1881 WHERE SHORT_NAME = p_report_sht_name;
1882 END IF;
1883
1884 x_tab_Id := l_tabId;
1885 x_time_stamp := TO_CHAR(l_Time_Stamp, l_last_upd_format);
1886
1887 EXCEPTION
1888 WHEN FND_API.G_EXC_ERROR THEN
1889 FND_MSG_PUB.Count_And_Get
1890 ( p_encoded => FND_API.G_FALSE
1891 , p_count => x_msg_count
1892 , p_data => x_msg_data
1893 );
1894 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1895 x_return_status := FND_API.G_RET_STS_ERROR;
1896 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1897 FND_MSG_PUB.Count_And_Get
1898 ( p_encoded => FND_API.G_FALSE
1899 , p_count => x_msg_count
1900 , p_data => x_msg_data
1901 );
1902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1903 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1904 WHEN NO_DATA_FOUND THEN
1905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1906 IF (x_msg_data IS NOT NULL) THEN
1907 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_UI_WRAPPER.Get_Or_CreateNew_Scorecard ';
1908 ELSE
1909 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_UI_WRAPPER.Get_Or_CreateNew_Scorecard ';
1910 END IF;
1911 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1912 WHEN OTHERS THEN
1913 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1914 IF (x_msg_data IS NOT NULL) THEN
1915 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_UI_WRAPPER.Get_Or_CreateNew_Scorecard ';
1916 ELSE
1917 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_UI_WRAPPER.Get_Or_CreateNew_Scorecard ';
1918 END IF;
1919 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1920 END Get_Or_CreateNew_Scorecard;
1921
1922 /*************************************************************
1923 Name : Get_Measure_Display_Name
1924 Description : This API is used to get the measure display name
1925 Input : p_region_code --> Region code
1926 p_dataset_id --> dataset id
1927 Output : x_meas_disp_name --> measure display name
1928 Created By : ashankar 09-JUN-2005
1929 /************************************************************/
1930
1931 PROCEDURE Get_Measure_Display_Name
1932 (
1933 p_region_code IN VARCHAR
1934 ,p_dataset_id IN NUMBER
1935 ,x_meas_disp_name OUT NOCOPY VARCHAR
1936 ) IS
1937 l_display_name AK_REGION_ITEMS_VL.attribute_label_long%TYPE;
1938 l_measure_type BIS_INDICATORS_VL.MEASURE_TYPE%TYPE;
1939 l_source BSC_SYS_DATASETS_B.SOURCE%TYPE;
1940 l_item_type AK_REGION_ITEMS_VL.ATTRIBUTE1%TYPE;
1941 BEGIN
1942 Get_Measure_Prop(p_region_code, p_dataset_id, l_display_name, l_measure_type, l_source, l_item_type);
1943 x_meas_disp_name := l_display_name;
1944
1945 END Get_Measure_Display_Name;
1946
1947 /*************************************************************
1948 Name : Get_Measure_Prop
1949 Description : This API is used to get the measure properties.
1950 If p_region_code is null, it retrieves information from bis_display_measures_v.
1951 Input : p_region_code --> Region code
1952 p_dataset_id --> dataset id
1953 Output : x_meas_disp_name --> measure display name
1954 : x_measure_type --> measure type in bis_indicators_vl
1955 : x_source --> data source in bsc_sys_datasets_b
1956 : x_item_type --> attribute1 in ak_region_items
1957 Created By : sawu 30-JUN-2005
1958 Modified By : ashankar 07-JUL-05 Filtering Weighted KPIs
1959 /************************************************************/
1960
1961 PROCEDURE Get_Measure_Prop
1962 (
1963 p_region_code IN VARCHAR
1964 ,p_dataset_id IN NUMBER
1965 ,x_meas_disp_name OUT NOCOPY AK_REGION_ITEMS_VL.ATTRIBUTE_LABEL_LONG%TYPE
1966 ,x_measure_type OUT NOCOPY BIS_INDICATORS_VL.MEASURE_TYPE%TYPE
1967 ,x_source OUT NOCOPY BSC_SYS_DATASETS_B.SOURCE%TYPE
1968 ,x_item_type OUT NOCOPY AK_REGION_ITEMS_VL.ATTRIBUTE1%TYPE
1969 ) IS
1970 l_display_name AK_REGION_ITEMS_VL.attribute_label_long%TYPE;
1971
1972 CURSOR measure_cur IS
1973 SELECT DISTINCT NVL(V.ATTRIBUTE_LABEL_LONG,B.NAME) NAME,
1974 B.MEASURE_TYPE,
1975 D.SOURCE,
1976 v.attribute1 ITEM_TYPE
1977 FROM BIS_INDICATORS_VL B,
1978 BSC_SYS_DATASETS_B D,
1979 AK_REGION_ITEMS_VL V,
1980 AK_REGIONS C
1981 WHERE C.REGION_CODE = V.REGION_CODE
1982 AND V.ATTRIBUTE_CATEGORY ='BIS PM Viewer'
1983 AND V.ATTRIBUTE1 LIKE '%MEASURE%'
1984 AND V.ATTRIBUTE1 NOT IN ('COMPARE_TO_MEASURE_NO_TARGET','CHANGE_MEASURE_NO_TARGET')
1985 AND NVL(B.MEASURE_TYPE,D.SOURCE) <>'CDS_SCORE'
1986 AND V.ATTRIBUTE2 = B.SHORT_NAME
1987 AND B.DATASET_ID = D.DATASET_ID
1988 AND B.DATASET_ID = p_dataset_id
1989 AND C.REGION_CODE = p_region_code;
1990 l_measure_cur measure_cur%ROWTYPE;
1991
1992 CURSOR dataset_cur IS
1993 SELECT V.NAME, V.MEASURE_TYPE, D.SOURCE
1994 FROM BIS_DISPLAY_MEASURES_V V,
1995 BIS_INDICATORS B,
1996 BSC_SYS_DATASETS_B D
1997 WHERE D.DATASET_ID = p_dataset_id
1998 AND D.DATASET_ID = B.DATASET_ID
1999 AND B.SHORT_NAME = V.SHORT_NAME;
2000 l_dataset_cur dataset_cur%ROWTYPE;
2001
2002 BEGIN
2003 IF (p_region_code IS NOT NULL) THEN
2004 OPEN measure_cur;
2005 FETCH measure_cur INTO l_measure_cur;
2006 IF measure_cur%FOUND THEN
2007 x_meas_disp_name := l_measure_cur.name;
2008 x_measure_type := l_measure_cur.measure_type;
2009 x_source := l_measure_cur.source;
2010 x_item_type := l_measure_cur.item_type;
2011 END IF;
2012 CLOSE measure_cur;
2013 ELSE
2014 OPEN dataset_cur;
2015 FETCH dataset_cur INTO l_dataset_cur;
2016 IF dataset_cur%FOUND THEN
2017 x_meas_disp_name := l_dataset_cur.name;
2018 x_measure_type := l_dataset_cur.measure_type;
2019 x_source := l_dataset_cur.source;
2020 x_item_type := '';
2021 END IF;
2022 CLOSE dataset_cur;
2023 END IF;
2024 EXCEPTION
2025 WHEN OTHERS THEN
2026 IF measure_cur%ISOPEN THEN
2027 CLOSE measure_cur;
2028 END IF;
2029 IF dataset_cur%ISOPEN THEN
2030 CLOSE dataset_cur;
2031 END IF;
2032 END Get_Measure_Prop;
2033
2034 /**************************************************
2035 NAME : Get_Functional_Area_Code
2036 DESCRIPTION : This API will return the Functional Area code
2037 This is being used in CustomViewCanvasSessionManager.java
2038 /**************************************************/
2039 FUNCTION Get_Functional_Area_Code
2040 RETURN VARCHAR2 IS
2041 BEGIN
2042 RETURN BSC_CUSTOM_VIEW_UI_WRAPPER.C_FUNCTIONAL_AREA;
2043 END Get_Functional_Area_Code;
2044
2045 /**************************************************
2046 NAME : Get_Form_Function_Code
2047 DESCRIPTION : This API will return the Form Function Code
2048 This is being used in CustomViewCanvasSessionManager.java
2049 /**************************************************/
2050 FUNCTION Get_Form_Function_Code
2051 RETURN VARCHAR2 IS
2052 BEGIN
2053 RETURN BSC_CUSTOM_VIEW_UI_WRAPPER.C_FORM_FUNCTION;
2054 END Get_Form_Function_Code;
2055
2056 /**************************************************
2057 NAME : Get_Tab_Fun_Fa_Prop
2058 DESCRIPTION : This API returns the Function name and Functional Area shortName
2059 based on the p_type value.
2060 If the value of p_type is C_FUNCTIONAL_AREA then it returns the
2061 Functional Area shortname.
2062 If the value of p_type is C_FORM_FUNCTION then it returns the
2063 Form Functio name.
2064 This is being used in CustomViewCanvasSessionManager.java
2065 INPUT : p_tab_id
2066 : p_tab_view_id
2067 : p_type
2068 /**************************************************/
2069
2070 FUNCTION Get_Tab_Fun_Fa_Prop
2071 (
2072 p_tab_id IN NUMBER
2073 , p_tab_view_id IN NUMBER
2074 , p_type IN VARCHAR
2075 ) RETURN VARCHAR2 IS
2076
2077 l_fun_name FND_FORM_FUNCTIONS_VL.function_name%TYPE;
2078 l_short_name BIS_FUNCTIONAL_AREAS_VL.short_name%TYPE;
2079 l_name VARCHAR2(100);
2080
2081 BEGIN
2082 SELECT ff.function_name,fa.short_name
2083 INTO l_fun_name,l_short_name
2084 FROM bis_form_function_extension ext,
2085 bis_functional_areas_vl fa,
2086 fnd_form_functions_vl ff
2087 WHERE
2088 ff.parameters LIKE '%pTabId_'||p_tab_id|| '&pViewId_'||p_tab_view_id||'%'
2089 AND ext.functional_area_id = fa.functional_area_id (+)
2090 AND ff.function_name = ext.object_name (+);
2091
2092 IF(BSC_CUSTOM_VIEW_UI_WRAPPER.C_FUNCTIONAL_AREA=p_type) THEN
2093 l_name:=l_short_name;
2094 ELSIF(BSC_CUSTOM_VIEW_UI_WRAPPER.C_FORM_FUNCTION=p_type)THEN
2095 l_name:=l_fun_name;
2096 END IF;
2097
2098 RETURN l_name;
2099 END Get_Tab_Fun_Fa_Prop;
2100
2101 /**************************************************
2102 NAME : add_or_update_kpi_trend
2103 DESCRIPTION : This api inserts trend information into BSC_TAB_VIEW_LABELS table for objectives.
2104 /**************************************************/
2105
2106 PROCEDURE add_or_update_kpi_trend(
2107 p_tab_id IN NUMBER
2108 ,p_tab_view_id IN NUMBER
2109 ,p_object_id IN NUMBER
2110 ,p_text_flag IN NUMBER
2111 ,p_label_text IN VARCHAR2
2112 ,p_font_size IN NUMBER
2113 ,p_font_style IN NUMBER
2114 ,p_font_color IN NUMBER
2115 ,p_left IN NUMBER
2116 ,p_top IN NUMBER
2117 ,p_width IN NUMBER
2118 ,p_height IN NUMBER
2119 ,p_kpi_id IN NUMBER
2120 ,p_function_id IN NUMBER
2121 ,x_return_status OUT NOCOPY VARCHAR2
2122 ,x_msg_count OUT NOCOPY NUMBER
2123 ,x_msg_data OUT NOCOPY VARCHAR2
2124 ) IS
2125 BEGIN
2126 SAVEPOINT addorupdatekpitrend;
2127 fnd_msg_pub.initialize;
2128 x_return_status := FND_API.G_RET_STS_SUCCESS;
2129
2130 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label(
2131 p_tab_id => p_tab_id
2132 ,p_tab_view_id => p_tab_view_id
2133 ,p_object_id => p_object_id
2134 ,p_object_type => c_type_kpi_trend
2135 ,p_label_text => p_label_text
2136 ,p_text_flag => p_text_flag
2137 ,p_font_color => p_font_color
2138 ,p_font_size => p_font_size
2139 ,p_font_style => p_font_style
2140 ,p_left => p_left
2141 ,p_top => p_top
2142 ,p_width => p_width
2143 ,p_height => p_height
2144 ,p_note_text => NULL
2145 ,p_link_id => p_kpi_id
2146 ,p_function_id => p_function_id
2147 ,x_return_status => x_return_status
2148 ,x_msg_count => x_msg_count
2149 ,x_msg_data => x_msg_data
2150 );
2151
2152 EXCEPTION
2153 WHEN OTHERS THEN
2154 ROLLBACK TO addorupdatekpitrend;
2155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2156 x_msg_data := SQLERRM;
2157 END add_or_update_kpi_trend;
2158
2159
2160 END BSC_CUSTOM_VIEW_UI_WRAPPER;