1 PACKAGE BODY BSC_CUSTOM_VIEW_PVT AS
2 /* $Header: BSCCVDVB.pls 120.0 2005/06/01 16:15:52 appldev noship $ */
3 /*********************************************************************************/
4
5
6 /*
7 Added get_Tab_Id_Count for Performace Bug #3236356
8 */
9
10 FUNCTION get_Tab_Id_Count
11 (
12 p_Tab_Id IN NUMBER
13 )RETURN NUMBER;
14
15
16 FUNCTION Is_More
17 ( p_cust_Views IN OUT NOCOPY VARCHAR2
18 , p_cust_View OUT NOCOPY VARCHAR2
19 ) RETURN BOOLEAN
20 IS
21 l_pos_ids NUMBER;
22 l_pos_rel_types NUMBER;
23 l_pos_rel_columns NUMBER;
24 BEGIN
25 IF (p_cust_Views IS NOT NULL) THEN
26 l_pos_ids := INSTR(p_cust_Views, ',');
27 IF (l_pos_ids > 0) THEN
28 p_cust_View := TRIM(SUBSTR(p_cust_Views, 1, l_pos_ids - 1));
29 p_cust_Views := TRIM(SUBSTR(p_cust_Views, l_pos_ids + 1));
30 ELSE
31 p_cust_View := TRIM(p_cust_Views);
32 p_cust_Views := NULL;
33 END IF;
34 RETURN TRUE;
35 ELSE
36 RETURN FALSE;
37 END IF;
38 END Is_More;
39
40
41 /******************************************************************************************
42 This fucntion will return the enabled flag for the tabview.
43 If it returns 1 it means the view is enabled for the view.
44 0 means the view is not enabled for the view.
45 /******************************************************************************************/
46
47 FUNCTION get_enabled_flag_for_View
48 (
49 p_tab_id IN NUMBER
50 ,p_tab_view_id IN NUMBER
51 ) RETURN NUMBER
52 IS
53 l_enabled BSC_TAB_VIEWS_B.enabled_flag%TYPE;
54 BEGIN
55
56 --DBMS_OUTPUT.PUT_LINE\n('p_tab_id-->'|| p_tab_id);
57 --DBMS_OUTPUT.PUT_LINE\n('p_tab_view_id-->'|| p_tab_view_id);
58
59 IF ((p_tab_view_id > -1)AND(p_tab_view_id<2)) THEN
60
61 IF (p_tab_view_id=0) THEN
62 -- for scorecard view
63
64 SELECT KPI_MODEL
65 INTO l_enabled
66 FROM BSC_TABS_B
67 WHERE tab_id =p_tab_id;
68
69 ELSE
70 SELECT BSC_MODEL
71 INTO l_enabled
72 FROM BSC_TABS_B
73 WHERE tab_id =p_tab_id;
74 END IF;
75
76 ELSE
77
78 SELECT enabled_flag
79 INTO l_enabled
80 FROM BSC_TAB_VIEWS_B
81 WHERE tab_id =p_tab_id
82 AND tab_view_id = p_tab_view_id;
83
84 END IF;
85
86 RETURN l_enabled;
87
88 --DBMS_OUTPUT.PUT_LINE\n('l_enabled-->'|| l_enabled);
89
90 END get_enabled_flag_for_View;
91
92 /***************************************************************************
93 This function validates if particular view exist for the tab or not.
94 if not then the count will be 0 otherwise it will be greater than 0
95
96
97 /***************************************************************************/
98
99
100 FUNCTION Validate_Tab_View
101 (
102 p_tab_id IN NUMBER
103 ,p_tab_view_id IN NUMBER
104 ) RETURN NUMBER
105 IS
106 l_count NUMBER;
107 BEGIN
108
109 SELECT COUNT(0)
110 INTO l_count
111 FROM BSC_TAB_VIEWS_B
112 WHERE tab_id = p_tab_id
113 AND tab_view_id =p_tab_view_id;
114
115 RETURN l_count;
116
117 END Validate_Tab_View;
118
119 /*******************************************************************************
120 Decription :- This procedure will Assign the custom views for the tab.
121 It means it will set the enabled_flag =1 in BSC_TAB_VIEWS_B table
122 for tree view, detail_view and custom views.
123 For scorecard view, strategy map view it will set KPI_MODEL =1
124 and BSC_MODEL =1 in BSC_TABS_B table.
125 Input :- Comma separated views ids which needs to be Assigned.
126 Created by :- ashankar 27-Oct-2003
127 /*******************************************************************************/
128
129
130 PROCEDURE Assign_Cust_Views
131 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
132 ,p_tab_id IN NUMBER
133 ,p_assign_views IN VARCHAR2
134 ,x_return_status OUT NOCOPY VARCHAR2
135 ,x_msg_count OUT NOCOPY NUMBER
136 ,x_msg_data OUT NOCOPY VARCHAR2
137 )IS
138 l_count NUMBER;
139 l_cust_views VARCHAR2(32000);
140 l_cust_View VARCHAR2(10);
141 l_Tab_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Tab_Rec_Type;
142 l_Tab_View_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type;
143 l_tab_view_id NUMBER;
144 BEGIN
145 FND_MSG_PUB.Initialize;
146 x_return_status := FND_API.G_RET_STS_SUCCESS;
147
148 IF (p_tab_id IS NOT NULL) THEN
149
150 -- Bug #3236356
151 l_count := get_Tab_Id_Count(p_tab_id);
152
153 IF(l_count =0) THEN
154 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SCORECARD_ID');
155 FND_MESSAGE.SET_TOKEN('BSC_SCORECARD', p_tab_id);
156 FND_MSG_PUB.ADD;
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159 ELSE
160 FND_MESSAGE.SET_NAME('BSC','BSC_NO_SCORECARD_ID_ENTERED');
161 FND_MSG_PUB.ADD;
162 RAISE FND_API.G_EXC_ERROR;
163 END IF;
164
165 IF(p_assign_views IS NOT NULL) THEN
166 l_cust_views := p_assign_views;
167 WHILE (Is_More( p_cust_Views => l_cust_views
168 ,p_cust_View => l_cust_View)
169 ) LOOP
170 l_tab_view_id := TO_NUMBER(l_cust_View);
171
172 IF(l_tab_view_id>-1 AND l_tab_view_id<2) THEN
173
174 l_Tab_Rec.Bsc_Tab_Id := p_tab_id;
175
176 IF(l_tab_view_id =0) THEN
177 l_Tab_Rec.Bsc_Kpi_Model :=1;
178 ELSE
179 l_Tab_Rec.Bsc_Bsc_Model :=1;
180 END IF;
181
182 BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View
183 (
184 p_Tab_Rec => l_Tab_Rec
185 ,x_return_status => x_return_status
186 ,x_msg_count => x_msg_count
187 ,x_msg_data => x_msg_data
188 );
189 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
190 ----DBMS_OUTPUT.PUT_LINE\n('BSC_CUSTOM_VIEW_PVT.Update_default_View Failed: at BSC_CUSTOM_VIEW_PVT.Assign_Cust_Views <'||x_msg_data||'>');
191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
192 END IF;
193
194 ELSE
195 l_Tab_View_Rec.Bsc_Tab_Id := p_tab_id;
196 l_Tab_View_Rec.Bsc_Tab_View_Id := l_tab_view_id;
197 l_Tab_View_Rec.Bsc_Enabled_Flag :=1;
198
199 -- VALIDATE FOR -2 AND -1 that whether the record for it exists or not.
200 --If no records exists then no need to insert the record into the data base
201 -- just skip it.
202 l_count := -1;
203 IF (l_Tab_View_Rec.Bsc_Tab_View_Id < 0) THEN
204 l_count := BSC_CUSTOM_VIEW_PVT.Validate_Tab_View
205 (
206 p_tab_id => l_Tab_View_Rec.Bsc_Tab_Id
207 , p_tab_view_id => l_Tab_View_Rec.Bsc_Tab_View_Id
208 );
209
210 END IF;
211 IF ((l_count = -1) OR (l_count > 0)) THEN
212 BSC_CUSTOM_VIEW_PVT.Update_Tab_View
213 (
214 p_Tab_View_Rec => l_Tab_View_Rec
215 ,x_return_status => x_return_status
216 ,x_msg_count => x_msg_count
217 ,x_msg_data => x_msg_data
218 );
219 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
220 ----DBMS_OUTPUT.PUT_LINE\n('BSC_CUSTOM_VIEW_PVT.Update_Tab_View Failed: at BSC_CUSTOM_VIEW_PVT.Assign_Cust_Views <'||x_msg_data||'>');
221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
222 END IF;
223 END IF;
224 END IF;
225 END LOOP;
226 END IF;
227
228 EXCEPTION
229 WHEN FND_API.G_EXC_ERROR THEN
230 IF (x_msg_data IS NULL) THEN
231 FND_MSG_PUB.Count_And_Get
232 ( p_encoded => FND_API.G_FALSE
233 ,p_count => x_msg_count
234 ,p_data => x_msg_data
235 );
236 END IF;
237
238 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
239 x_return_status := FND_API.G_RET_STS_ERROR;
240
241 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
242 IF (x_msg_data IS NULL) THEN
243 FND_MSG_PUB.Count_And_Get
244 ( p_encoded => FND_API.G_FALSE
245 , p_count => x_msg_count
246 , p_data => x_msg_data
247 );
248 END IF;
249
250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
251 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
252
253 WHEN NO_DATA_FOUND THEN
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255 IF (x_msg_data IS NOT NULL) THEN
256 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views ';
257 ELSE
258 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views ';
259 END IF;
260
261 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION NO_DATA_FOUND '||x_msg_data);
262
263 WHEN OTHERS THEN
264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
265 IF (x_msg_data IS NOT NULL) THEN
266 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views ';
267 ELSE
268 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views ';
269 END IF;
270
271 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION OTHERS '||x_msg_data);
272 END Assign_Cust_Views;
273
274 /*******************************************************************************
275 Decription :- This procedure will unassign the custom views for the tab.
276 It means it will set the enabled_flag =0 in BSC_TAB_VIEWS_B table
277 for tree view, detail_view and custom views.
278 For scorecard view, strategy map view it will set KPI_MODEL =0
279 and BSC_MODEL =0 in BSC_TABS_B table.
280 Input :- Comma separated views ids which needs to be unassigned.
281 Created by :- ashankar 23-Oct-2003
282 /*******************************************************************************/
283
284 PROCEDURE Unassign_Cust_Views
285 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
286 ,p_tab_id IN NUMBER
287 ,p_unassign_views IN VARCHAR2
288 ,x_return_status OUT NOCOPY VARCHAR2
289 ,x_msg_count OUT NOCOPY NUMBER
290 ,x_msg_data OUT NOCOPY VARCHAR2
291 )IS
292
293 l_count NUMBER;
294 l_cust_views VARCHAR2(32000);
295 l_cust_View VARCHAR2(10);
296 l_Tab_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Tab_Rec_Type;
297 l_Tab_View_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type;
298 l_tab_view_id NUMBER;
299 BEGIN
300 FND_MSG_PUB.Initialize;
301 x_return_status := FND_API.G_RET_STS_SUCCESS;
302
303 IF (p_tab_id IS NOT NULL) THEN
304 -- Bug #3236356
305 l_count := get_Tab_Id_Count(p_tab_id);
306
307 IF(l_count =0) THEN
308 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SCORECARD_ID');
309 FND_MESSAGE.SET_TOKEN('BSC_SCORECARD', p_tab_id);
310 FND_MSG_PUB.ADD;
311 RAISE FND_API.G_EXC_ERROR;
312 END IF;
313 ELSE
314 FND_MESSAGE.SET_NAME('BSC','BSC_NO_SCORECARD_ID_ENTERED');
315 FND_MSG_PUB.ADD;
316 RAISE FND_API.G_EXC_ERROR;
317 END IF;
318
319 IF(p_unassign_views IS NOT NULL) THEN
320 l_cust_Views := p_unassign_views;
321 WHILE (Is_More( p_cust_Views => l_cust_views
322 ,p_cust_View => l_cust_View)
323 ) LOOP
324 l_tab_view_id := TO_NUMBER(l_cust_View);
325
326 ----DBMS_OUTPUT.PUT_LINE\n(' l_tab_view_id--> '|| l_tab_view_id) ;
327
328 IF(l_tab_view_id>-1 AND l_tab_view_id<2) THEN
329
330 l_Tab_Rec.Bsc_Tab_Id := p_tab_id;
331 IF(l_tab_view_id =0) THEN
332 l_Tab_Rec.Bsc_Kpi_Model :=0;
333 ELSE
334 l_Tab_Rec.Bsc_Bsc_Model :=0;
335 END IF;
336
337 ----DBMS_OUTPUT.PUT_LINE\n(' inside if l_tab_view_id--> '|| l_tab_view_id) ;
338 BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View
339 (
340 p_Tab_Rec => l_Tab_Rec
341 ,x_return_status => x_return_status
342 ,x_msg_count => x_msg_count
343 ,x_msg_data => x_msg_data
344 );
345 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
346 ----DBMS_OUTPUT.PUT_LINE\n('BSC_CUSTOM_VIEW_PVT.Update_default_View Failed: at BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views <'||x_msg_data||'>');
347 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
348 END IF;
349
350 ELSE
351
352 ----DBMS_OUTPUT.PUT_LINE\n(' outside if l_tab_view_id--> '|| l_tab_view_id) ;
353 l_Tab_View_Rec.Bsc_Tab_Id := p_tab_id;
354 l_Tab_View_Rec.Bsc_Tab_View_Id := l_tab_view_id;
355 l_Tab_View_Rec.Bsc_Enabled_Flag := 0;
356
357 -- Check if the records are there for the tab view id -2 and -1.
358 -- if not then create a record for -2 and -1 and setthe enabled flag to 0
359 IF (l_Tab_View_Rec.Bsc_Tab_View_Id < 0) THEN
360 l_count := BSC_CUSTOM_VIEW_PVT.Validate_Tab_View
361 (
362 p_tab_id => l_Tab_View_Rec.Bsc_Tab_Id
363 , p_tab_view_id => l_Tab_View_Rec.Bsc_Tab_View_Id
364 );
365
366 IF (l_count = 0) THEN
367
368 IF (l_Tab_View_Rec.Bsc_Tab_View_Id =-1) THEN
369
370 IF (BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DETAILED_VIEW') IS NOT NULL)THEN
371 l_Tab_View_Rec.Bsc_Name := BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DETAILED_VIEW');
372 l_Tab_View_Rec.Bsc_Help := l_Tab_View_Rec.Bsc_Name;
373 ELSE
374 l_Tab_View_Rec.Bsc_Name := 'Detailed View';
375 l_Tab_View_Rec.Bsc_Help := l_Tab_View_Rec.Bsc_Name;
376 END IF;
377
378 ELSE
379
380 IF (BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TREE_VIEW') IS NOT NULL)THEN
384 l_Tab_View_Rec.Bsc_Name := 'Tree View';
381 l_Tab_View_Rec.Bsc_Name := BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TREE_VIEW');
382 l_Tab_View_Rec.Bsc_Help := l_Tab_View_Rec.Bsc_Name;
383 ELSE
385 l_Tab_View_Rec.Bsc_Help := l_Tab_View_Rec.Bsc_Name;
386 END IF;
387 END IF;
388
389 BSC_CUSTOM_VIEW_PVT.Create_Tab_View
390 (
391 p_Tab_View_Rec => l_Tab_View_Rec
392 ,x_return_status => x_return_status
393 ,x_msg_count => x_msg_count
394 ,x_msg_data => x_msg_data
395 );
396 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
397 ----DBMS_OUTPUT.PUT_LINE\n('BSC_CUSTOM_VIEW_PVT.Update_Tab_View Failed: at BSC_CUSTOM_VIEW_PVT.Create_Tab_View <'||x_msg_data||'>');
398 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
399 END IF;
400 END IF;
401 END IF;
402
403 BSC_CUSTOM_VIEW_PVT.Update_Tab_View
404 (
405 p_Tab_View_Rec => l_Tab_View_Rec
406 ,x_return_status => x_return_status
407 ,x_msg_count => x_msg_count
408 ,x_msg_data => x_msg_data
409 );
410 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
411 ----DBMS_OUTPUT.PUT_LINE\n('BSC_CUSTOM_VIEW_PVT.Update_Tab_View Failed: at BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views <'||x_msg_data||'>');
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
413 END IF;
414 END IF;
415 END LOOP;
416 END IF;
417
418 EXCEPTION
419 WHEN FND_API.G_EXC_ERROR THEN
420 IF (x_msg_data IS NULL) THEN
421 FND_MSG_PUB.Count_And_Get
422 ( p_encoded => FND_API.G_FALSE
423 , p_count => x_msg_count
424 , p_data => x_msg_data
425 );
426 END IF;
427
428 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
429 x_return_status := FND_API.G_RET_STS_ERROR;
430
431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
432 IF (x_msg_data IS NULL) THEN
433 FND_MSG_PUB.Count_And_Get
434 ( p_encoded => FND_API.G_FALSE
435 , p_count => x_msg_count
436 , p_data => x_msg_data
437 );
438 END IF;
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
440 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
441
442 WHEN NO_DATA_FOUND THEN
443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
444 IF (x_msg_data IS NOT NULL) THEN
445 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views ';
446 ELSE
447 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views ';
448 END IF;
449 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION NO_DATA_FOUND '||x_msg_data);
450
451 WHEN OTHERS THEN
452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
453 IF (x_msg_data IS NOT NULL) THEN
454 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views ';
455 ELSE
456 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views ';
457 END IF;
458 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION OTHERS '||x_msg_data);
459 END Unassign_Cust_Views;
460
461 /******************************************************************************************
462 This procedure will do the following.
463 For the current tab what views need to be shown.
464 All the assign_Views which are coming from the UI should be visible in the popdown list
465 in IViewer and shown as seleceted in the table.
466 The default view needs to be updated in BSC_TABS_B.
467 If scorecard view and startegy map view are enabled then they should go into
468 BSC_TABS_B COLUMNS KPI_MODEL and BSC_MODEL.
469 For other views like Tree View/Detail View and other custom views should be
470 updated in BSC_TAB_VIEWS_B with enabled flag set to 1/0.
471
472 if user unassigns all the views we have to set scorecard view as default.
473 The default view should be updated in the last only.
474 if we are trying to set the default view of the view which is disabled then we have to throw
475 the exception.
476
477 /******************************************************************************************/
478
479
480 PROCEDURE Assign_Unassign_Views(
481 p_commit IN VARCHAR2 := FND_API.G_FALSE
482 ,p_tab_id IN NUMBER
483 ,p_default_value IN NUMBER
484 ,p_assign_views IN VARCHAR2
485 ,p_unassign_views IN VARCHAR2
486 ,x_return_status OUT NOCOPY VARCHAR2
487 ,x_msg_count OUT NOCOPY NUMBER
488 ,x_msg_data OUT NOCOPY VARCHAR2
489 )IS
490 l_count NUMBER;
491 l_Tab_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Tab_Rec_Type;
495 x_return_status := FND_API.G_RET_STS_SUCCESS;
492 l_Tab_View_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type;
493 BEGIN
494 FND_MSG_PUB.Initialize;
496
497 IF (p_tab_id IS NULL) THEN
498 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SCORECARD_ID');
499 FND_MESSAGE.SET_TOKEN('BSC_SCORECARD', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TAB_ID'), TRUE);
500 FND_MSG_PUB.ADD;
501 RAISE FND_API.G_EXC_ERROR;
502 END IF;
503
504 IF (p_default_value IS NULL) THEN
505 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DEFAULT_ID');
506 FND_MESSAGE.SET_TOKEN('BSC_DEF_ID', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DEFAULT_TAB_ID'), TRUE);
507 FND_MSG_PUB.ADD;
508 RAISE FND_API.G_EXC_ERROR;
509 END IF;
510
511 -- call the unassign first and then assign the views APIS.
512
513 --DBMS_OUTPUT.PUT_LINE\n('p_tab_id--->' || p_tab_id);
514 --DBMS_OUTPUT.PUT_LINE\n('p_default_value--->' || p_default_value);
515 --DBMS_OUTPUT.PUT_LINE\n('p_assign_views--->' || p_assign_views);
516 --DBMS_OUTPUT.PUT_LINE\n('p_unassign_views--->' || p_unassign_views);
517
518 IF (p_unassign_views IS NOT NULL) THEN
519
520 BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views
521 ( p_commit => FND_API.G_FALSE
522 ,p_tab_id => p_tab_id
523 ,p_unassign_views => p_unassign_views
524 ,x_return_status => x_return_status
525 ,x_msg_count => x_msg_count
526 ,x_msg_data => x_msg_data
527 );
528
529 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
530 ----DBMS_OUTPUT.PUT_LINE\n('BSC_CUSTOM_VIEW_PVT.Assign_Unassign_Views Failed: at BSC_CUSTOM_VIEW_PVT.Assign_Unassign_Views');
531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
532 END IF;
533 END IF;
534
535 --DBMS_OUTPUT.PUT_LINE\n('aFTER SC_CUSTOM_VIEW_PVT.Unassign_Cust_Views');
536 -- call the assign view API
537
538 IF (p_assign_views IS NOT NULL) THEN
539
540 BSC_CUSTOM_VIEW_PVT.Assign_Cust_Views
541 ( p_commit => FND_API.G_FALSE
542 ,p_tab_id => p_tab_id
543 ,p_assign_views => p_assign_views
544 ,x_return_status => x_return_status
545 ,x_msg_count => x_msg_count
546 ,x_msg_data => x_msg_data
547 );
548
549 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
550 ----DBMS_OUTPUT.PUT_LINE\n('BSC_CUSTOM_VIEW_PVT.Assign_Cust_Views Failed: at BSC_CUSTOM_VIEW_PVT.Assign_Cust_Views');
551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
552 END IF;
553 END IF;
554 --DBMS_OUTPUT.PUT_LINE\n('aFTER SC_CUSTOM_VIEW_PVT.Assign_Cust_Views');
555
556 --Now set the default view.
557 --Check if the view we are going to set as default has enabled flag set to 1.
558 IF (p_default_value < 0) THEN
559
560 l_Tab_View_Rec.Bsc_Tab_Id := p_tab_id;
561 l_Tab_View_Rec.Bsc_Tab_View_Id := p_default_value ;
562 l_Tab_View_Rec.Bsc_Enabled_Flag := 1;
563
564 l_count := BSC_CUSTOM_VIEW_PVT.Validate_Tab_View
565 (
566 p_tab_id => l_Tab_View_Rec.Bsc_Tab_Id
567 , p_tab_view_id => l_Tab_View_Rec.Bsc_Tab_View_Id
568 );
569
570 IF (l_count = 0) THEN
571 IF (l_Tab_View_Rec.Bsc_Tab_View_Id =-1) THEN
572 l_Tab_View_Rec.Bsc_Name := BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DETAILED_VIEW');
573 ELSE
574 l_Tab_View_Rec.Bsc_Name := BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TREE_VIEW');
575 END IF;
576
577 l_Tab_View_Rec.Bsc_Help := l_Tab_View_Rec.Bsc_Name;
578
579 BSC_CUSTOM_VIEW_PVT.Create_Tab_View
580 (
581 p_Tab_View_Rec => l_Tab_View_Rec
582 ,x_return_status => x_return_status
583 ,x_msg_count => x_msg_count
584 ,x_msg_data => x_msg_data
585 );
586 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
587 ----DBMS_OUTPUT.PUT_LINE\n('BSC_CUSTOM_VIEW_PVT.Update_Tab_View Failed: at BSC_CUSTOM_VIEW_PVT.Create_Tab_View <'||x_msg_data||'>');
588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
589 END IF;
590 END IF;
591 END IF;
592
593 l_count := BSC_CUSTOM_VIEW_PVT.get_enabled_flag_for_View
594 (
595 p_tab_id => p_tab_id
596 , p_tab_view_id => p_default_value
597 );
598
599 IF(l_count=0) THEN
600 FND_MESSAGE.SET_NAME('BSC','BSC_NOTSET_DEFAULT_ID');
601 FND_MSG_PUB.ADD;
602 RAISE FND_API.G_EXC_ERROR;
603 END IF;
604
605 l_Tab_Rec.Bsc_Tab_Id := p_tab_id;
606 l_Tab_Rec.Bsc_Default_Model := p_default_value;
607 l_Tab_Rec.Bsc_Last_Update_Date := SYSDATE;
608
609 BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View
610 (
611 p_Tab_Rec => l_Tab_Rec
612 ,x_return_status => x_return_status
613 ,x_msg_count => x_msg_count
614 ,x_msg_data => x_msg_data
615 );
619 WHEN FND_API.G_EXC_ERROR THEN
616
617
618 EXCEPTION
620 IF (x_msg_data IS NULL) THEN
621 FND_MSG_PUB.Count_And_Get
622 ( p_encoded => FND_API.G_FALSE
623 , p_count => x_msg_count
624 , p_data => x_msg_data
625 );
626 END IF;
627
628 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
629 x_return_status := FND_API.G_RET_STS_ERROR;
630
631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
632 IF (x_msg_data IS NULL) THEN
633 FND_MSG_PUB.Count_And_Get
634 ( p_encoded => FND_API.G_FALSE
635 , p_count => x_msg_count
636 , p_data => x_msg_data
637 );
638 END IF;
639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
640 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
641
642 WHEN NO_DATA_FOUND THEN
643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 IF (x_msg_data IS NOT NULL) THEN
645 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views ';
646 ELSE
647 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views ';
648 END IF;
649 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION NO_DATA_FOUND '||x_msg_data);
650
651 WHEN OTHERS THEN
652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
653 IF (x_msg_data IS NOT NULL) THEN
654 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views ';
655 ELSE
656 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Unassign_Cust_Views ';
657 END IF;
658 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION OTHERS '||x_msg_data);
659 END Assign_Unassign_Views;
660
661 /******************************************************************************************
662 This procedure will create a new record into the BSC_TAB_VIEWS_B and BSC_TAB_VIEWS_TL table.
663
664 /******************************************************************************************/
665
666 PROCEDURE Create_Tab_View
667 (
668 p_commit IN VARCHAR2 := FND_API.G_FALSE
669 ,p_Tab_View_Rec IN BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type
670 ,x_return_status OUT NOCOPY VARCHAR2
671 ,x_msg_count OUT NOCOPY NUMBER
672 ,x_msg_data OUT NOCOPY VARCHAR2
673 )IS
674 l_Tab_View_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type;
675 l_count NUMBER;
676 BEGIN
677 SAVEPOINT CreateTabView;
678 FND_MSG_PUB.Initialize;
679 x_return_status := FND_API.G_RET_STS_SUCCESS;
680
681 l_Tab_View_Rec := p_Tab_View_Rec;
682
683 IF (l_Tab_View_Rec.Bsc_Tab_Id IS NOT NULL) THEN
684 -- Bug #3236356
685 l_count := get_Tab_Id_Count(l_Tab_View_Rec.Bsc_Tab_Id);
686
687 IF(l_count =0) THEN
688 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SCORECARD_ID');
689 FND_MESSAGE.SET_TOKEN('BSC_SCORECARD', l_Tab_View_Rec.Bsc_Tab_Id);
690 FND_MSG_PUB.ADD;
691 RAISE FND_API.G_EXC_ERROR;
692 END IF;
693 ELSE
694 FND_MESSAGE.SET_NAME('BSC','BSC_NO_SCORECARD_ID_ENTERED');
695 FND_MSG_PUB.ADD;
696 RAISE FND_API.G_EXC_ERROR;
697 END IF;
698
699
700 INSERT INTO BSC_TAB_VIEWS_B
701 ( tab_id
702 ,tab_view_id
703 ,enabled_flag
704 ,created_by
705 ,creation_date
706 ,last_updated_by
707 ,last_update_date
708 ,last_update_login
709 )VALUES
710 ( l_Tab_View_Rec.Bsc_Tab_Id
711 ,l_Tab_View_Rec.Bsc_Tab_View_Id
712 ,l_Tab_View_Rec.Bsc_Enabled_Flag
713 ,fnd_global.USER_ID
714 ,SYSDATE
715 ,fnd_global.USER_ID
716 ,SYSDATE
717 ,fnd_global.LOGIN_ID
718 );
719
720 INSERT INTO BSC_TAB_VIEWS_TL
721 ( tab_id
722 ,tab_view_id
723 ,language
724 ,source_lang
725 ,name
726 ,help
727 ,created_by
728 ,creation_date
729 ,last_updated_by
730 ,last_update_date
731 ,last_update_login
732 )
733 SELECT l_Tab_View_Rec.Bsc_Tab_Id
734 ,l_Tab_View_Rec.Bsc_Tab_View_Id
735 ,L.LANGUAGE_CODE
736 ,USERENV('LANG')
737 ,l_Tab_View_Rec.Bsc_Name
738 ,l_Tab_View_Rec.Bsc_Help
739 ,fnd_global.USER_ID
740 ,sysdate
741 ,fnd_global.USER_ID
745 WHERE L.INSTALLED_FLAG IN ('I', 'B')
742 ,sysdate
743 ,fnd_global.LOGIN_ID
744 FROM FND_LANGUAGES L
746 AND NOT EXISTS
747 ( SELECT NULL
748 FROM BSC_TAB_VIEWS_TL T
749 WHERE T.tab_id = l_Tab_View_Rec.Bsc_Tab_Id
750 AND T.tab_view_id = l_Tab_View_Rec.Bsc_Tab_View_Id
751 AND T.LANGUAGE = L.LANGUAGE_CODE);
752
753
754 IF (p_commit =FND_API.G_TRUE) THEN
755 commit;
756 END IF;
757
758 EXCEPTION
759 WHEN FND_API.G_EXC_ERROR THEN
760 ROLLBACK TO CreateTabView;
761 x_return_status := FND_API.G_RET_STS_ERROR;
762 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
763 ,p_data => x_msg_data);
764 RAISE;
765
766 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
767 ROLLBACK TO CreateTabView;
768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
769 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
770 ,p_data => x_msg_data);
771 RAISE;
772
773 WHEN NO_DATA_FOUND THEN
774 ROLLBACK TO CreateTabView;
775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
776 IF (x_msg_data IS NOT NULL) THEN
777 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Retrieve_Tab';
778 ELSE
779 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Retrieve_Tab';
780 END IF;
781 RAISE;
782 WHEN OTHERS THEN
783 ROLLBACK TO CreateTabView;
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785 IF (x_msg_data IS NOT NULL) THEN
786 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Create_Tab_View';
787 ELSE
788 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Create_Tab_View';
789 END IF;
790 RAISE;
791
792 END Create_Tab_View;
793
794
795 /****************************************************************************
796 This procedure retrives the data corresponding to the particula tab_id
797 It will be used when we will be updating the tab record.
798 /*****************************************************************************/
799
800 PROCEDURE Retrieve_Tab(
801 p_Tab_Rec IN BSC_CUSTOM_VIEW_PUB.Bsc_Tab_Rec_Type
802 ,x_Tab_Rec IN OUT NOCOPY BSC_CUSTOM_VIEW_PUB.Bsc_Tab_Rec_Type
803 ,x_return_status OUT NOCOPY VARCHAR2
804 ,x_msg_count OUT NOCOPY NUMBER
805 ,x_msg_data OUT NOCOPY VARCHAR2
806 ) IS
807 BEGIN
808 FND_MSG_PUB.Initialize;
809 x_return_status := FND_API.G_RET_STS_SUCCESS;
810
811 SELECT a.tab_id
812 ,a.kpi_model
813 ,a.bsc_model
814 ,a.cross_model
815 ,a.default_model
816 ,a.zoom_factor
817 ,a.created_by
818 ,a.creation_date
819 ,a.last_updated_by
820 ,a.last_update_date
821 ,a.last_update_login
822 ,a.tab_index
823 ,a.parent_tab_id
824 ,a.owner_id
825 ,a.short_name
826 INTO x_Tab_Rec.Bsc_Tab_Id
827 ,x_Tab_Rec.Bsc_Kpi_Model
828 ,x_Tab_Rec.Bsc_Bsc_Model
829 ,x_Tab_Rec.Bsc_Cross_Model
830 ,x_Tab_Rec.Bsc_Default_Model
831 ,x_Tab_Rec.Bsc_Zoom_Factor
832 ,x_Tab_Rec.Bsc_Created_By
833 ,x_Tab_Rec.Bsc_Creation_Date
834 ,x_Tab_Rec.Bsc_Last_updated_By
835 ,x_Tab_Rec.Bsc_Last_update_Date
836 ,x_Tab_Rec.Bsc_Last_update_Login
837 ,x_Tab_Rec.Bsc_Tab_Index
838 ,x_Tab_Rec.Bsc_Parent_Tab_id
839 ,x_Tab_Rec.Bsc_Owner_Id
840 ,x_Tab_Rec.Bsc_Short_Name
841 FROM BSC_TABS_B a
842 WHERE a.tab_id = p_Tab_Rec.Bsc_Tab_Id;
843
844 EXCEPTION
845 WHEN FND_API.G_EXC_ERROR THEN
846 x_return_status := FND_API.G_RET_STS_ERROR;
847 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
848 ,p_data => x_msg_data);
849 RAISE;
850 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
853 ,p_data => x_msg_data);
854 RAISE;
855 WHEN NO_DATA_FOUND THEN
856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
857 IF (x_msg_data IS NOT NULL) THEN
858 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Retrieve_Tab ';
859 ELSE
860 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Retrieve_Tab ';
861 END IF;
862 RAISE;
863 WHEN OTHERS THEN
864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
865 IF (x_msg_data IS NOT NULL) THEN
866 x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
867 ELSE
871 END Retrieve_Tab;
868 x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
869 END IF;
870 RAISE;
872
873 /*************************************************************************************
874 Description :- This procedure retrieves the data from BSC_TAB_VIEWS_B table.
875 Input :- p_Tab_View_Rec.Bsc_Tab_Id and p_Tab_View_Rec.Bsc_Tab_View_Id
876 OutPut :- x_Tab_View_Rec
877 Created By :- ashankar 23-Oct-2003
878 /**************************************************************************************/
879
880 PROCEDURE Retrieve_Tab_View
881 ( p_Tab_View_Rec IN BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type
882 ,x_Tab_View_Rec IN OUT NOCOPY BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type
883 ,x_return_status OUT NOCOPY VARCHAR2
884 ,x_msg_count OUT NOCOPY NUMBER
885 ,x_msg_data OUT NOCOPY VARCHAR2
886 )IS
887 BEGIN
888 FND_MSG_PUB.Initialize;
889 x_return_status := FND_API.G_RET_STS_SUCCESS;
890
891 ----DBMS_OUTPUT.PUT_LINE\n('p_Tab_View_Rec.Bsc_Tab_Id --->' || p_Tab_View_Rec.Bsc_Tab_Id);
892 ----DBMS_OUTPUT.PUT_LINE\n('p_Tab_View_Rec.Bsc_Tab_View_Id --->' || p_Tab_View_Rec.Bsc_Tab_View_Id);
893
894 SELECT tab_id
895 ,tab_view_id
896 ,enabled_flag
897 ,created_by
898 ,creation_date
899 ,last_updated_by
900 ,last_update_date
901 ,last_update_login
902 INTO x_Tab_View_Rec.Bsc_Tab_Id
903 ,x_Tab_View_Rec.Bsc_Tab_View_Id
904 ,x_Tab_View_Rec.Bsc_Enabled_Flag
905 ,x_Tab_View_Rec.Bsc_Created_By
906 ,x_Tab_View_Rec.Bsc_Creation_Date
907 ,x_Tab_View_Rec.Bsc_Last_Updated_By
908 ,x_Tab_View_Rec.Bsc_Last_Update_Date
909 ,x_Tab_View_Rec.Bsc_Last_Update_Login
910 FROM BSC_TAB_VIEWS_B
911 WHERE tab_id = p_Tab_View_Rec.Bsc_Tab_Id
912 AND tab_view_id =p_Tab_View_Rec.Bsc_Tab_View_Id;
913
914 EXCEPTION
915 WHEN FND_API.G_EXC_ERROR THEN
916 x_return_status := FND_API.G_RET_STS_ERROR;
917 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
918 ,p_data => x_msg_data);
919 RAISE;
920 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
922 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
923 ,p_data => x_msg_data);
924 RAISE;
925 WHEN NO_DATA_FOUND THEN
926 ----DBMS_OUTPUT.PUT_LINE\n('insdei exception p_Tab_View_Rec.Bsc_Tab_Id --->' || p_Tab_View_Rec.Bsc_Tab_Id);
927 ----DBMS_OUTPUT.PUT_LINE\n('p_Tab_View_Rec.Bsc_Tab_View_Id --->' || p_Tab_View_Rec.Bsc_Tab_View_Id);x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
928 IF (x_msg_data IS NOT NULL) THEN
929 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Retrieve_Tab_View ';
930 ELSE
931 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Retrieve_Tab_View ';
932 END IF;
933 RAISE;
934 WHEN OTHERS THEN
935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
936 IF (x_msg_data IS NOT NULL) THEN
937 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Retrieve_Tab_View ';
938 ELSE
939 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Retrieve_Tab_View ';
940 END IF;
941 RAISE;
942 END Retrieve_Tab_View;
943
944
945 /*****************************************************************************************
946 Description :- This procedure updates the BSC_TABS_B table.
947 This procedure should be called from assign and unassign views.
948 Input :- p_tab_View_rec
949 Ouput :- Updates the BSC_TABS_B
950 Created By :- ashankar 23-Oct-2003
951 /******************************************************************************************/
952 PROCEDURE Update_Tab_View
953 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
954 ,p_Tab_View_Rec IN BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type
955 ,x_return_status OUT NOCOPY VARCHAR2
956 ,x_msg_count OUT NOCOPY NUMBER
957 ,x_msg_data OUT NOCOPY VARCHAR2
958
959 )IS
960 l_Tab_View_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type;
961 l_Tab_View_Out_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type;
962 l_count NUMBER;
963
964 BEGIN
965 SAVEPOINT UpdateTabView;
966 FND_MSG_PUB.Initialize;
967 x_return_status := FND_API.G_RET_STS_SUCCESS;
968
969 l_Tab_View_Rec.Bsc_Tab_Id := p_Tab_View_Rec.Bsc_Tab_Id;
970
971 IF(l_Tab_View_Rec.Bsc_Tab_Id IS NOT NULL) THEN
972 -- Bug #3236356
973 l_count := get_Tab_Id_Count(l_Tab_View_Rec.Bsc_Tab_Id);
974
975 IF(l_count =0) THEN
976 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SCORECARD_ID');
977 FND_MESSAGE.SET_TOKEN('BSC_SCORECARD', l_Tab_View_Rec.Bsc_Tab_Id);
978 FND_MSG_PUB.ADD;
979 RAISE FND_API.G_EXC_ERROR;
980 END IF;
981
982 ELSE
983
984 FND_MESSAGE.SET_NAME('BSC','BSC_NO_SCORECARD_ID_ENTERED');
985 FND_MSG_PUB.ADD;
986 RAISE FND_API.G_EXC_ERROR;
987
988 END IF;
989
993 ----DBMS_OUTPUT.PUT_LINE\n('l_Tab_View_Rec.Bsc_Tab_Id --->'||l_Tab_View_Rec.Bsc_Tab_Id );
990 l_Tab_View_Rec.Bsc_Tab_View_Id := p_Tab_View_Rec.Bsc_Tab_View_Id;
991
992 ----DBMS_OUTPUT.PUT_LINE\n('l_Tab_View_Rec.Bsc_Tab_View_Id-->' || l_Tab_View_Rec.Bsc_Tab_View_Id);
994
995 Retrieve_Tab_View
996 (
997 p_Tab_View_Rec => l_Tab_View_Rec
998 ,x_Tab_View_Rec => l_Tab_View_Out_Rec
999 ,x_return_status => x_return_status
1000 ,x_msg_count => x_msg_count
1001 ,x_msg_data => x_msg_data
1002
1003 );
1004
1005 IF (p_Tab_View_Rec.Bsc_Enabled_Flag IS NOT NULL) THEN
1006 l_Tab_View_Out_Rec.Bsc_Enabled_Flag := p_Tab_View_Rec.Bsc_Enabled_Flag;
1007 END IF;
1008
1009 IF (p_Tab_View_Rec.Bsc_Created_By IS NOT NULL) THEN
1010 l_Tab_View_Out_Rec.Bsc_Created_By := p_Tab_View_Rec.Bsc_Created_By;
1011 END IF;
1012
1013 IF (p_Tab_View_Rec.Bsc_Creation_Date IS NOT NULL) THEN
1014 l_Tab_View_Out_Rec.Bsc_Creation_Date := p_Tab_View_Rec.Bsc_Creation_Date;
1015 END IF;
1016
1017 IF (p_Tab_View_Rec.Bsc_Last_Updated_By IS NOT NULL) THEN
1018 l_Tab_View_Out_Rec.Bsc_Last_Updated_By := p_Tab_View_Rec.Bsc_Last_Updated_By;
1019 END IF;
1020
1021 IF (p_Tab_View_Rec.Bsc_Last_Update_Date IS NOT NULL) THEN
1022 l_Tab_View_Out_Rec.Bsc_Last_Update_Date := p_Tab_View_Rec.Bsc_Last_Update_Date;
1023 END IF;
1024
1025 IF (p_Tab_View_Rec.Bsc_Last_Update_Login IS NOT NULL) THEN
1026 l_Tab_View_Out_Rec.Bsc_Last_Update_Login := p_Tab_View_Rec.Bsc_Last_Update_Login;
1027 END IF;
1028
1029 UPDATE BSC_TAB_VIEWS_B
1030 SET Enabled_Flag= l_Tab_View_Out_Rec.Bsc_Enabled_Flag
1031 ,Created_By= l_Tab_View_Out_Rec.Bsc_Created_By
1032 ,Creation_Date=l_Tab_View_Out_Rec.Bsc_Creation_Date
1033 ,Last_Updated_By= l_Tab_View_Out_Rec.Bsc_Last_Updated_By
1034 ,Last_Update_Date= l_Tab_View_Out_Rec.Bsc_Last_Update_Date
1035 ,Last_Update_Login=l_Tab_View_Out_Rec.Bsc_Last_Update_Login
1036 WHERE tab_id =l_Tab_View_Rec.Bsc_Tab_Id
1037 AND tab_view_id = l_Tab_View_Rec.Bsc_Tab_View_Id;
1038
1039 IF (p_commit = FND_API.G_TRUE) THEN
1040 commit;
1041 END IF;
1042
1043 EXCEPTION
1044
1045 WHEN FND_API.G_EXC_ERROR THEN
1046 ROLLBACK TO UpdateTabView;
1047 IF (x_msg_data IS NULL) THEN
1048 FND_MSG_PUB.Count_And_Get
1049 ( p_encoded => FND_API.G_FALSE
1050 , p_count => x_msg_count
1051 , p_data => x_msg_data
1052 );
1053 END IF;
1054 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1055 x_return_status := FND_API.G_RET_STS_ERROR;
1056 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1057 ROLLBACK TO UpdateTabView;
1058 IF (x_msg_data IS NULL) THEN
1059 FND_MSG_PUB.Count_And_Get
1060 ( p_encoded => FND_API.G_FALSE
1061 , p_count => x_msg_count
1062 , p_data => x_msg_data
1063 );
1064 END IF;
1065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1066 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1067 WHEN NO_DATA_FOUND THEN
1068 ROLLBACK TO UpdateTabView;
1069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1070 IF (x_msg_data IS NOT NULL) THEN
1071 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Update_Tab_View ';
1072 ELSE
1073 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Update_Tab_View ';
1074 END IF;
1075 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1076 WHEN OTHERS THEN
1077 ROLLBACK TO UpdateTabView;
1078 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1079 IF (x_msg_data IS NOT NULL) THEN
1080 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Update_Tab_View ';
1081 ELSE
1082 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Update_Tab_View ';
1083 END IF;
1084 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION OTHERS '||x_msg_data);
1085 END Update_Tab_View;
1086
1087
1088 /****************************************************************************
1089 This procedure is used to update the default view of the tab.
1090 User can change the default view of the tab.
1091 /*****************************************************************************/
1092
1093
1094 PROCEDURE Update_Tab_default_View
1095 (
1096 p_commit IN VARCHAR2 := FND_API.G_FALSE
1097 ,p_Tab_Rec IN BSC_CUSTOM_VIEW_PUB.Bsc_Tab_Rec_Type
1098 ,x_return_status OUT NOCOPY VARCHAR2
1099 ,x_msg_count OUT NOCOPY NUMBER
1100 ,x_msg_data OUT NOCOPY VARCHAR2
1101
1102 )IS
1103
1104 l_Tab_Ret_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Tab_Rec_Type;
1105 l_Tab_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Tab_Rec_Type;
1106 l_count NUMBER;
1107
1108 BEGIN
1109 SAVEPOINT UpdateTabdefaultView;
1110 FND_MSG_PUB.Initialize;
1111 x_return_status := FND_API.G_RET_STS_SUCCESS;
1112
1113 l_Tab_Rec.Bsc_Tab_Id := p_Tab_Rec.Bsc_Tab_Id;
1114
1115 IF(l_Tab_Rec.Bsc_Tab_Id IS NOT NULL) THEN
1116
1120 IF(l_count =0) THEN
1117 -- Bug #3236356
1118 l_count := get_Tab_Id_Count(l_Tab_Rec.Bsc_Tab_Id);
1119
1121 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SCORECARD_ID');
1122 FND_MESSAGE.SET_TOKEN('BSC_SCORECARD', l_Tab_Rec.Bsc_Tab_Id);
1123 FND_MSG_PUB.ADD;
1124 RAISE FND_API.G_EXC_ERROR;
1125 END IF;
1126
1127 ELSE
1128
1129 FND_MESSAGE.SET_NAME('BSC','BSC_NO_SCORECARD_ID_ENTERED');
1130 FND_MSG_PUB.ADD;
1131 RAISE FND_API.G_EXC_ERROR;
1132
1133 END IF;
1134
1135 Retrieve_Tab
1136 (
1137 p_Tab_Rec => l_Tab_Rec
1138 ,x_Tab_Rec => l_Tab_Ret_Rec
1139 ,x_return_status => x_return_status
1140 ,x_msg_count => x_msg_count
1141 ,x_msg_data => x_msg_data
1142
1143 );
1144
1145 IF (p_Tab_Rec.Bsc_Kpi_Model IS NOT NULL) THEN
1146 l_Tab_Ret_Rec.Bsc_Kpi_Model := p_Tab_Rec.Bsc_Kpi_Model;
1147 END IF;
1148
1149 IF (p_Tab_Rec.Bsc_Bsc_Model IS NOT NULL) THEN
1150 l_Tab_Ret_Rec.Bsc_Bsc_Model := p_Tab_Rec.Bsc_Bsc_Model;
1151 END IF;
1152
1153 IF (p_Tab_Rec.Bsc_Cross_Model IS NOT NULL) THEN
1154 l_Tab_Ret_Rec.Bsc_Cross_Model := p_Tab_Rec.Bsc_Cross_Model;
1155 END IF;
1156
1157 IF (p_Tab_Rec.Bsc_Default_Model IS NOT NULL) THEN
1158 l_Tab_Ret_Rec.Bsc_Default_Model := p_Tab_Rec.Bsc_Default_Model;
1159 END IF;
1160
1161 IF (p_Tab_Rec.Bsc_Zoom_Factor IS NOT NULL) THEN
1162 l_Tab_Ret_Rec.Bsc_Zoom_Factor := p_Tab_Rec.Bsc_Zoom_Factor;
1163 END IF;
1164
1165 IF (p_Tab_Rec.Bsc_Created_By IS NOT NULL) THEN
1166 l_Tab_Ret_Rec.Bsc_Created_By := p_Tab_Rec.Bsc_Created_By;
1167 END IF;
1168
1169 IF (p_Tab_Rec.Bsc_Creation_Date IS NOT NULL) THEN
1170 l_Tab_Ret_Rec.Bsc_Creation_Date := p_Tab_Rec.Bsc_Creation_Date;
1171 END IF;
1172
1173 IF (p_Tab_Rec.Bsc_Last_updated_By IS NOT NULL) THEN
1174 l_Tab_Ret_Rec.Bsc_Last_updated_By := p_Tab_Rec.Bsc_Last_updated_By;
1175 END IF;
1176
1177 IF (p_Tab_Rec.Bsc_Last_update_Date IS NOT NULL) THEN
1178 l_Tab_Ret_Rec.Bsc_Last_update_Date := p_Tab_Rec.Bsc_Last_update_Date ;
1179 END IF;
1180
1181 IF (p_Tab_Rec.Bsc_Last_update_Login IS NOT NULL) THEN
1182 l_Tab_Ret_Rec.Bsc_Last_update_Login := p_Tab_Rec.Bsc_Last_update_Login;
1183 END IF;
1184
1185 IF (p_Tab_Rec.Bsc_Tab_Index IS NOT NULL) THEN
1186 l_Tab_Ret_Rec.Bsc_Tab_Index := p_Tab_Rec.Bsc_Tab_Index;
1187 END IF;
1188
1189 IF (p_Tab_Rec.Bsc_Parent_Tab_id IS NOT NULL) THEN
1190 l_Tab_Ret_Rec.Bsc_Parent_Tab_id := p_Tab_Rec.Bsc_Parent_Tab_id;
1191 END IF;
1192
1193 IF (p_Tab_Rec.Bsc_Owner_Id IS NOT NULL) THEN
1194 l_Tab_Ret_Rec.Bsc_Owner_Id := p_Tab_Rec.Bsc_Owner_Id ;
1195 END IF;
1196
1197 IF (p_Tab_Rec.Bsc_Short_Name IS NOT NULL) THEN
1198 l_Tab_Ret_Rec.Bsc_Short_Name := p_Tab_Rec.Bsc_Short_Name;
1199 END IF;
1200
1201 UPDATE BSC_TABS_B
1202 SET Kpi_Model=l_Tab_Ret_Rec.Bsc_Kpi_Model
1203 ,Bsc_Model=l_Tab_Ret_Rec.Bsc_Bsc_Model
1204 ,Cross_Model=l_Tab_Ret_Rec.Bsc_Cross_Model
1205 ,Default_Model=l_Tab_Ret_Rec.Bsc_Default_Model
1206 ,Zoom_Factor=l_Tab_Ret_Rec.Bsc_Zoom_Factor
1207 ,Created_By =l_Tab_Ret_Rec.Bsc_Created_By
1208 ,Creation_Date =l_Tab_Ret_Rec.Bsc_Creation_Date
1209 ,Last_updated_By=l_Tab_Ret_Rec.Bsc_Last_updated_By
1210 ,Last_update_Date=l_Tab_Ret_Rec.Bsc_Last_update_Date
1211 ,Last_update_Login=l_Tab_Ret_Rec.Bsc_Last_update_Login
1212 ,Tab_Index=l_Tab_Ret_Rec.Bsc_Tab_Index
1213 ,Parent_Tab_id=l_Tab_Ret_Rec.Bsc_Parent_Tab_id
1214 ,Owner_Id=l_Tab_Ret_Rec.Bsc_Owner_Id
1215 ,Short_Name=l_Tab_Ret_Rec.Bsc_Short_Name
1216 WHERE Tab_Id = l_Tab_Ret_Rec.Bsc_Tab_Id;
1217
1218 IF (p_commit = FND_API.G_TRUE) THEN
1219 commit;
1220 END IF;
1221
1222 EXCEPTION
1223
1224 WHEN FND_API.G_EXC_ERROR THEN
1225 ROLLBACK TO UpdateTabdefaultView;
1226 IF (x_msg_data IS NULL) THEN
1227 FND_MSG_PUB.Count_And_Get
1228 ( p_encoded => FND_API.G_FALSE
1229 , p_count => x_msg_count
1230 , p_data => x_msg_data
1231 );
1232 END IF;
1233 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1234 x_return_status := FND_API.G_RET_STS_ERROR;
1235 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1236 ROLLBACK TO UpdateTabdefaultView;
1237 IF (x_msg_data IS NULL) THEN
1238 FND_MSG_PUB.Count_And_Get
1239 ( p_encoded => FND_API.G_FALSE
1240 , p_count => x_msg_count
1241 , p_data => x_msg_data
1242 );
1243 END IF;
1244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1245 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1246 WHEN NO_DATA_FOUND THEN
1247 ROLLBACK TO UpdateTabdefaultView;
1248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1249 IF (x_msg_data IS NOT NULL) THEN
1253 END IF;
1250 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View ';
1251 ELSE
1252 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View ';
1254 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1255 WHEN OTHERS THEN
1256 ROLLBACK TO UpdateTabdefaultView;
1257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1258 IF (x_msg_data IS NOT NULL) THEN
1259 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View ';
1260 ELSE
1261 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View ';
1262 END IF;
1263 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION OTHERS '||x_msg_data);
1264
1265 END Update_Tab_default_View;
1266
1267 /*******************************************************************************
1268 This fucntion returns the default view which is attached with the tab id.
1269 INPUT :- p_Tab_ID
1270 OutPut :- default_view_id
1271 /********************************************************************************/
1272
1273 FUNCTION get_Tab_Default_View
1274 (
1275 p_Tab_Id IN BSC_TABS_B.tab_id%TYPE
1276 )RETURN NUMBER
1277 IS
1278 l_default_view BSC_TABS_B.default_model%TYPE;
1279 BEGIN
1280
1281 SELECT default_model
1282 INTO l_default_view
1283 FROM BSC_TABS_B
1284 WHERE tab_id = p_Tab_Id;
1285
1286 RETURN l_default_view;
1287
1288 END get_Tab_Default_View;
1289
1290
1291
1292 /*****************************************************************************
1293 Name :- delete_Custom_View
1294 Description :- This procedure will delete the custom view from bsc_tab_views_b table.
1295 It will do the following validations.
1296 1. Before deleting the custom view it will verify if it is the default
1297 view which is being deleted. If yes then it will set scorecard view
1298 as default view and delete the custom view.
1299 Otherwise it will update the last update date of the tab.
1300 This is required for Granular locking purpose.
1301
1302 The entry will be deleted from the following tables.
1303 1.BSC_TAB_VIEWS_B
1304 2.BSC_TAB_VIEWS_TL
1305 3.BSC_TAB_VIEW_KPI_TL
1306 4.BSC_TAB_VIEW_LABELS_B
1307 5.BSC_TAB_VIEW_LABELS_TL
1308 6.BSC_SYS_IMAGES_MAP_TL
1309 7.BSC_SYS_IMAGES (need for cascading)
1310 8.Form functoins defined in each custom view upon creation in BSC_CUSTOM_VIEW_UI_WRAPPER.create_function
1311
1312 Input :- p_CustView_Rec
1313 Creator/Modified by :- ashankar 10-NOV-2003
1314 /******************************************************************************/
1315 PROCEDURE delete_Custom_View
1316 (
1317 p_commit IN VARCHAR2 := FND_API.G_FALSE
1318 ,p_CustView_Rec IN BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type
1319 ,x_return_status OUT NOCOPY VARCHAR2
1320 ,x_msg_count OUT NOCOPY NUMBER
1321 ,x_msg_data OUT NOCOPY VARCHAR2
1322 ) IS
1323
1324 l_CustView_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type;
1325 l_count NUMBER;
1326 l_Tab_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Tab_Rec_Type;
1327 l_default_view BSC_TABS_B.default_model%TYPE;
1328
1329 CURSOR c_sys_images IS
1330 SELECT image_id
1331 FROM BSC_SYS_IMAGES
1332 WHERE image_id NOT IN
1333 ( SELECT DISTINCT(image_id)
1334 FROM BSC_SYS_IMAGES_MAP_TL);
1335
1336 BEGIN
1337
1338 SAVEPOINT deleteCustomView;
1339 ----DBMS_OUTPUT.PUT_LINE\n('Entered inside BSC_CUSTOM_VIEW_PUB.delete_Custom_View ');
1340 FND_MSG_PUB.Initialize;
1341
1342 l_CustView_Rec.Bsc_Tab_Id := p_CustView_Rec.Bsc_Tab_Id;
1343 l_CustView_Rec.Bsc_Tab_View_Id := p_CustView_Rec.Bsc_Tab_View_Id;
1344
1345 IF(l_CustView_Rec.Bsc_Tab_Id IS NOT NULL) THEN
1346 -- Bug #3236356
1347 l_count := get_Tab_Id_Count(l_CustView_Rec.Bsc_Tab_Id);
1348
1349 IF(l_count =0) THEN
1350 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SCORECARD_ID');
1351 FND_MESSAGE.SET_TOKEN('BSC_SCORECARD', l_CustView_Rec.Bsc_Tab_Id);
1352 FND_MSG_PUB.ADD;
1353 RAISE FND_API.G_EXC_ERROR;
1354 END IF;
1355
1356 ELSE
1357
1358 FND_MESSAGE.SET_NAME('BSC','BSC_NO_SCORECARD_ID_ENTERED');
1359 FND_MSG_PUB.ADD;
1360 RAISE FND_API.G_EXC_ERROR;
1361
1362 END IF;
1363
1364 IF((l_CustView_Rec.Bsc_Tab_Id IS NOT NULL) AND (l_CustView_Rec.Bsc_Tab_View_Id IS NOT NULL)) THEN
1365
1366 SELECT count(0)
1367 INTO l_count
1368 FROM bsc_tab_views_b
1369 WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
1370 AND tab_view_id = l_CustView_Rec.Bsc_Tab_View_Id;
1371
1372 IF (l_count =0) THEN
1373 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SCORECARD_ID');
1374 FND_MESSAGE.SET_TOKEN('BSC_SCORECARD', l_CustView_Rec.Bsc_Tab_Id);
1375 FND_MSG_PUB.ADD;
1376 RAISE FND_API.G_EXC_ERROR;
1377 END IF;
1378
1382
1379 -- Before deleting the custom view check if it was the default view with the scorecard.
1380 -- if yes then set the default view to scorecard view and then delete the custom view.
1381 -- FIRST SET THE ENABLED FLAG TO SCORECARD CARD VIEW AND ALSO SET IT AS DEFAULT
1383
1384 l_Tab_Rec.Bsc_Tab_Id := l_CustView_Rec.Bsc_Tab_Id;
1385 l_default_view := get_Tab_Default_View(l_Tab_Rec.Bsc_Tab_Id);
1386
1387 IF (l_default_view = l_CustView_Rec.Bsc_Tab_View_Id) THEN
1388
1389 l_Tab_Rec.Bsc_Default_Model :=0;
1390 l_Tab_Rec.Bsc_Kpi_Model :=1;
1391 l_Tab_Rec.Bsc_Last_update_Date := SYSDATE;
1392
1393 BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View
1394 (
1395 p_Tab_Rec => l_Tab_Rec
1396 ,x_return_status => x_return_status
1397 ,x_msg_count => x_msg_count
1398 ,x_msg_data => x_msg_data
1399 );
1400 ELSE
1401
1402 l_Tab_Rec.Bsc_Last_update_Date := SYSDATE;
1403
1404 BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View
1405 (
1406 p_Tab_Rec => l_Tab_Rec
1407 ,x_return_status => x_return_status
1408 ,x_msg_count => x_msg_count
1409 ,x_msg_data => x_msg_data
1410 );
1411 END IF;
1412
1413 -- delete form function defined for custom view
1414 BSC_CUSTOM_VIEW_UI_WRAPPER.delete_function( p_tab_id => l_CustView_Rec.Bsc_Tab_Id
1415 ,p_tab_view_id => l_CustView_Rec.Bsc_Tab_View_Id
1416 ,x_return_status => x_return_status
1417 ,x_msg_count => x_msg_count
1418 ,x_msg_data => x_msg_data);
1419
1420 -- now delete the tab view id
1421
1422 DELETE
1423 FROM BSC_TAB_VIEWS_B
1424 WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
1425 AND tab_view_id = l_CustView_Rec.Bsc_Tab_View_Id;
1426
1427 DELETE
1428 FROM BSC_TAB_VIEWS_TL
1429 WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
1430 AND tab_view_id = l_CustView_Rec.Bsc_Tab_View_Id;
1431
1432
1433 DELETE
1434 FROM BSC_TAB_VIEW_KPI_TL
1435 WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
1436 AND tab_view_id = l_CustView_Rec.Bsc_Tab_View_Id;
1437
1438 DELETE
1439 FROM BSC_TAB_VIEW_LABELS_B
1440 WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
1441 AND tab_view_id = l_CustView_Rec.Bsc_Tab_View_Id;
1442
1443 DELETE
1444 FROM BSC_TAB_VIEW_LABELS_B
1445 WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
1446 AND label_type = 1
1447 AND link_id = l_CustView_Rec.Bsc_Tab_View_Id;
1448
1449 DELETE
1450 FROM BSC_TAB_VIEW_LABELS_TL
1451 WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
1452 AND tab_view_id = l_CustView_Rec.Bsc_Tab_View_Id;
1453
1454
1455 DELETE
1456 FROM BSC_SYS_IMAGES_MAP_TL
1457 WHERE SOURCE_TYPE = 1
1458 AND SOURCE_CODE = l_CustView_Rec.Bsc_Tab_Id
1459 AND TYPE = l_CustView_Rec.Bsc_Tab_View_Id;
1460
1461 -- now check if there are any unwanted images in the system and not being
1462 -- used by any of the scorecard then delete them
1463
1464 FOR cd IN c_sys_images LOOP
1465 l_CustView_Rec.Bsc_Image_Id := cd.image_id;
1466
1467 DELETE
1468 FROM BSC_SYS_IMAGES
1469 WHERE IMAGE_ID = l_CustView_Rec.Bsc_Image_Id;
1470
1471 END LOOP;
1472 END IF;
1473
1474 EXCEPTION
1475 WHEN FND_API.G_EXC_ERROR THEN
1476 ROLLBACK TO deleteCustomView;
1477 IF (x_msg_data IS NULL) THEN
1478 FND_MSG_PUB.Count_And_Get
1479 ( p_encoded => FND_API.G_FALSE
1480 , p_count => x_msg_count
1481 , p_data => x_msg_data
1482 );
1483 END IF;
1484
1485 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1486 x_return_status := FND_API.G_RET_STS_ERROR;
1487
1488 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1489 ROLLBACK TO deleteCustomView;
1490 IF (x_msg_data IS NULL) THEN
1491 FND_MSG_PUB.Count_And_Get
1492 ( p_encoded => FND_API.G_FALSE
1493 , p_count => x_msg_count
1494 , p_data => x_msg_data
1495 );
1496 END IF;
1497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1498 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1499
1500 WHEN NO_DATA_FOUND THEN
1501 ROLLBACK TO deleteCustomView;
1502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1503 IF (x_msg_data IS NOT NULL) THEN
1504 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PUB.delete_Custom_View ';
1505 ELSE
1506 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PUB.delete_Custom_View ';
1507 END IF;
1508 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1509
1510 WHEN OTHERS THEN
1511 ROLLBACK TO deleteCustomView;
1512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1513 IF (x_msg_data IS NOT NULL) THEN
1514 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PUB.delete_Custom_View ';
1515 ELSE
1516 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PUB.delete_Custom_View ';
1517 END IF;
1518 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION OTHERS '||x_msg_data);
1519
1520
1521
1522 END delete_Custom_View;
1523
1524 /*
1525 Added get_Tab_Id_Count for Bug #3236356
1526 */
1527
1528 FUNCTION get_Tab_Id_Count
1529 (
1530 p_Tab_Id IN NUMBER
1531 )RETURN NUMBER IS
1532 l_count NUMBER := 0;
1533 BEGIN
1534
1535 SELECT COUNT(0)
1536 INTO l_count
1537 FROM BSC_TABS_B
1538 WHERE TAB_ID = p_Tab_Id;
1539
1540 RETURN l_count;
1541
1542 END get_Tab_Id_Count;
1543
1544
1545 /********************************************************************************
1546 DELETE CUSTOM VIEW LINKS
1547 /*******************************************************************************/
1548
1549 PROCEDURE Delete_Custom_View_Links
1550 (
1551 p_commit IN VARCHAR2 := FND_API.G_FALSE
1552 , p_tab_id IN NUMBER
1553 , p_obj_id IN NUMBER
1554 , x_return_status OUT NOCOPY VARCHAR2
1555 , x_msg_count OUT NOCOPY NUMBER
1556 , x_msg_data OUT NOCOPY VARCHAR2
1557 ) IS
1558 l_Count NUMBER;
1559
1560 CURSOR c_CachedData IS
1561 SELECT tab_id,
1562 tab_view_id,
1563 label_id
1564 FROM BSC_TAB_VIEW_LABELS_B
1565 WHERE tab_id = p_tab_id
1566 AND link_id = p_obj_id;
1567
1568 BEGIN
1569 SAVEPOINT DeleteCustomViewLinks;
1570 FND_MSG_PUB.Initialize;
1571
1572 IF(p_tab_id IS NOT NULL) THEN
1573 l_count := get_Tab_Id_Count(p_tab_id);
1574
1578 FND_MSG_PUB.ADD;
1575 IF(l_count =0) THEN
1576 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_SCORECARD_ID');
1577 FND_MESSAGE.SET_TOKEN('BSC_SCORECARD', p_tab_id);
1579 RAISE FND_API.G_EXC_ERROR;
1580 END IF;
1581 ELSE
1582 FND_MESSAGE.SET_NAME('BSC','BSC_NO_SCORECARD_ID_ENTERED');
1583 FND_MSG_PUB.ADD;
1584 RAISE FND_API.G_EXC_ERROR;
1585 END IF;
1586
1587 l_Count := 0;
1588
1589 SELECT COUNT(0)
1590 INTO l_Count
1591 FROM BSC_TAB_VIEW_KPI_VL
1592 WHERE Tab_Id = p_tab_id
1593 AND Indicator = p_obj_id;
1594
1595 IF(l_Count>0) THEN
1596
1597 DELETE
1598 FROM BSC_TAB_VIEW_KPI_TL
1599 WHERE tab_id = p_tab_id
1600 AND indicator = p_obj_id;
1601 END IF;
1602
1603 /*********************************************
1604 After the Enhancement adding Actual and Change to the objectives
1605 the entries corresponding to Actual and Change labels were added in
1606 BSC_TAB_VIEW_LABLES_B and BSC_TAB_VIEW_LABLES_TL table with label_type
1607 as 4,5 and 6 corresponding to Objective label,Actual label and Change label.
1608
1609 So when the objective is deleted we have to cascade these changes in
1610 BSC_TAB_VIEW_LABELS_B and _TL table.
1611
1612 So following is the LOGIC
1613
1614 To delete from TL table we need to cache the TAB_ID,TAB_VIEW_ID and LABEL_ID.
1615
1616 To delete from _B table we need tab_id and LINK_ID
1617 *********************************************/
1618
1619
1620 FOR cd IN c_CachedData LOOP
1621 DELETE
1622 FROM BSC_TAB_VIEW_LABELS_TL
1623 WHERE tab_id = cd.tab_id
1624 AND tab_view_id = cd.tab_view_id
1625 AND label_id = cd.label_id;
1626 END LOOP;
1627
1628 DELETE
1629 FROM BSC_TAB_VIEW_LABELS_B
1630 WHERE tab_id = p_tab_id
1631 AND LINK_ID =p_obj_id;
1632
1633 IF (p_commit = FND_API.G_TRUE) THEN
1634 COMMIT;
1635 END IF;
1636
1637 EXCEPTION
1638
1639 WHEN FND_API.G_EXC_ERROR THEN
1640 ROLLBACK TO DeleteCustomViewLinks;
1641 IF (x_msg_data IS NULL) THEN
1642 FND_MSG_PUB.Count_And_Get
1643 ( p_encoded => FND_API.G_FALSE
1644 , p_count => x_msg_count
1645 , p_data => x_msg_data
1646 );
1647 END IF;
1648
1649 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1650 x_return_status := FND_API.G_RET_STS_ERROR;
1651
1652 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1653 ROLLBACK TO DeleteCustomViewLinks;
1654 IF (x_msg_data IS NULL) THEN
1655 FND_MSG_PUB.Count_And_Get
1656 ( p_encoded => FND_API.G_FALSE
1657 , p_count => x_msg_count
1658 , p_data => x_msg_data
1659 );
1660 END IF;
1661 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1662 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1663
1664 WHEN NO_DATA_FOUND THEN
1665 ROLLBACK TO DeleteCustomViewLinks;
1666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1667 IF (x_msg_data IS NOT NULL) THEN
1668 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Delete_Custom_View_Links ';
1669 ELSE
1670 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Delete_Custom_View_Links ';
1671 END IF;
1672 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1673 WHEN OTHERS THEN
1674 ROLLBACK TO DeleteCustomViewLinks;
1675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1676 IF (x_msg_data IS NOT NULL) THEN
1677 x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Delete_Custom_View_Links ';
1678 ELSE
1679 x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Delete_Custom_View_Links ';
1680 END IF;
1681 ----DBMS_OUTPUT.PUT_LINE\n('EXCEPTION OTHERS '||x_msg_data);
1682 END Delete_Custom_View_Links;
1683
1684 END BSC_CUSTOM_VIEW_PVT;
[Home] [Help]