[Home] [Help]
PACKAGE BODY: APPS.BSC_SCORECARD_PVT
Source
1 package body BSC_SCORECARD_PVT as
2 /* $Header: BSCVTABB.pls 120.0.12000000.2 2007/05/31 07:42:53 ashankar ship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCVTABB.pls |
10 | |
11 | Creation Date: |
12 | October 22, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | Private Body version. |
19 | This package creates a BSC Scorecard/Tab. |
20 | |
21 | History: |
22 | 13-JAN-2003 ASHANKAR Bug Fix #2742973 Runtime Error "3021" when clicking on |
23 | the next button in VB-Builder. |
24 | 04-MAR-2003 PAJOHRI MLS Bug #2721899 |
25 | 1. Modified Update Query for BSC_TABS_TL, BSC_TAB_CSF_TL |
26 | 30-APR-2003 PWALI Bug #2926199 |
27 | 1. Modified Retrieve_Tab(), to change the Query filter |
28 | 13-MAY-2003 PWALI Bug #2942895, SQL BIND COMPLIANCE |
29 | 18-MAY-04 adrao Modified PL/SQL records and CRUD to accept SHORT_NAME |
30 | 02-SEP-04 ashankar fix for the bug 3866577 |
31 | 28-OCT-04 wleung modified delete_tab() adding delete_function() logic enh 3934298 |
32 | 29-Mar-2005 kyadamak bug#4268439
33 | 30-May-2007 ashankar ER#TGSS 5844382 |
34 +======================================================================================+
35 */
36 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_SCORECARD_PVT';
37 g_db_object varchar2(30) := null;
38
39 procedure Create_Tab(
40 p_commit IN varchar2 := FND_API.G_FALSE
41 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
42 ,x_return_status OUT NOCOPY varchar2
43 ,x_msg_count OUT NOCOPY number
44 ,x_msg_data OUT NOCOPY varchar2
45 ) is
46
47 l_count number;
48
49 l_language varchar2(4);
50
51 CURSOR c_language IS
52 SELECT language_code
53 FROM fnd_languages
54 WHERE installed_flag IN ('I','B');
55
56 begin
57 FND_MSG_PUB.Initialize;
58 x_return_status := FND_API.G_RET_STS_SUCCESS;
59 SAVEPOINT CreateBSCTabPVT;
60 -- Check Tab Id does not exist.
61 IF p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id IS NOT NULL THEN
62
63 SELECT COUNT(1) INTO l_Count
64 FROM BSC_TABS_B T
65 WHERE T.TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
66
67 IF l_Count <> 0 THEN
68 FND_MESSAGE.SET_NAME('BSC','BSC_TAB_ID_EXISTS');
69 FND_MESSAGE.SET_TOKEN('BSC_TAB', p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id);
70 FND_MSG_PUB.ADD;
71 RAISE FND_API.G_EXC_ERROR;
72 END IF;
73
74 -- Check for duplicate short_name
75 IF p_Bsc_Tab_Entity_Rec.Bsc_Short_Name IS NOT NULL THEN
76 SELECT COUNT(1) INTO l_Count
77 FROM BSC_TABS_B T
78 WHERE T.SHORT_NAME = p_Bsc_Tab_Entity_Rec.Bsc_Short_Name;
79
80 IF l_Count <> 0 THEN
81 FND_MESSAGE.SET_NAME('BSC','BSC_TAB_SHORT_NAME_NOT_UNIQUE');
82 FND_MSG_PUB.ADD;
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85 END IF;
86
87 ELSE
88 FND_MESSAGE.SET_NAME('BSC','BSC_NO_TAB_ID_ENTERED');
89 FND_MESSAGE.SET_TOKEN('BSC_TAB', p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id);
90 FND_MSG_PUB.ADD;
91 RAISE FND_API.G_EXC_ERROR;
92 END IF;
93
94 g_db_object := 'BSC_TABS_B';
95
96 -- if there are no errors up to this point then create tab.
97 INSERT INTO BSC_TABS_B( TAB_ID
98 ,KPI_MODEL
99 ,BSC_MODEL
100 ,CROSS_MODEL
101 ,DEFAULT_MODEL
102 ,ZOOM_FACTOR
103 ,CREATED_BY
104 ,CREATION_DATE
105 ,LAST_UPDATED_BY
106 ,LAST_UPDATE_DATE
107 ,LAST_UPDATE_LOGIN
108 ,TAB_INDEX
109 ,PARENT_TAB_ID
110 ,OWNER_ID
111 ,SHORT_NAME)
112 VALUES( p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
113 ,p_Bsc_Tab_Entity_Rec.Bsc_Kpi_Model
114 ,p_Bsc_Tab_Entity_Rec.Bsc_Bsc_Model
115 ,p_Bsc_Tab_Entity_Rec.Bsc_Cross_Model
116 ,p_Bsc_Tab_Entity_Rec.Bsc_Default_Model
117 ,p_Bsc_Tab_Entity_Rec.Bsc_Zoom_Factor
118 ,p_Bsc_Tab_Entity_Rec.Bsc_Created_By
119 ,SYSDATE
120 ,p_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By
121 ,SYSDATE
122 ,p_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login
123 ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Index
124 ,p_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id
125 ,p_Bsc_Tab_Entity_Rec.Bsc_Owner_Id
126 ,p_Bsc_Tab_Entity_Rec.Bsc_Short_Name);
127
128
129 g_db_object := 'BSC_TABS_TL';
130
131 IF (c_language%ISOPEN) THEN
132 CLOSE c_language;
133 END IF;
134
135 OPEN c_language;
136 LOOP
137 FETCH c_language INTO l_language;
138 EXIT WHEN c_language%NOTFOUND;
139
140 INSERT INTO BSC_TABS_TL( TAB_ID
141 ,LANGUAGE
142 ,SOURCE_LANG
143 ,NAME
144 ,HELP
145 ,ADDITIONAL_INFO)
146 VALUES( p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
147 ,l_Language
148 ,USERENV('LANG')
149 ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Name
150 ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Help
151 ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Info);
152
153 END LOOP;
154 CLOSE c_language;
155
156 g_db_object := 'BSC_TAB_CSF_B';
157
158 INSERT INTO BSC_TAB_CSF_B( TAB_ID
159 ,CSF_ID
160 ,CSF_TYPE
161 ,INTERMEDIATE_FLAG)
162 VALUES( p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
163 ,p_Bsc_Tab_Entity_Rec.Bsc_Csf_Id
164 ,p_Bsc_Tab_Entity_Rec.Bsc_Csf_Type
165 ,p_Bsc_Tab_Entity_Rec.Bsc_Intermediate_Flag);
166
167 g_db_object := 'BSC_TAB_CSF_TL';
168
169 IF (c_language%ISOPEN) THEN
170 CLOSE c_language;
171 END IF;
172
173 OPEN c_language;
174 LOOP
175 FETCH c_language INTO l_language;
176 EXIT WHEN c_language%NOTFOUND;
177
178 INSERT INTO BSC_TAB_CSF_TL( TAB_ID
179 ,CSF_ID
180 ,LANGUAGE
181 ,SOURCE_LANG
182 ,NAME
183 ,HELP)
184 VALUES( p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
185 ,p_Bsc_Tab_Entity_Rec.Bsc_Csf_Id
186 ,l_language
187 ,USERENV('LANG')
188 ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Name
189 ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Help);
190
191 END LOOP;
192 CLOSE c_language;
193
194 IF (p_commit = FND_API.G_TRUE) THEN
195 COMMIT;
196 END IF;
197
198 EXCEPTION
199 WHEN FND_API.G_EXC_ERROR THEN
200 ROLLBACK TO CreateBSCTabPVT;
201 FND_MSG_PUB.Count_And_Get
202 ( p_encoded => FND_API.G_FALSE
203 , p_count => x_msg_count
204 , p_data => x_msg_data
205 );
206 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
207 x_return_status := FND_API.G_RET_STS_ERROR;
208 RAISE;
209 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
210 ROLLBACK TO CreateBSCTabPVT;
211 FND_MSG_PUB.Count_And_Get
212 ( p_encoded => FND_API.G_FALSE
213 , p_count => x_msg_count
214 , p_data => x_msg_data
215 );
216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
218 RAISE;
219 WHEN NO_DATA_FOUND THEN
220 ROLLBACK TO CreateBSCTabPVT;
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
222 IF (x_msg_data IS NOT NULL) THEN
223 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Create_Tab ';
224 ELSE
225 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Create_Tab ';
226 END IF;
227 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
228 RAISE;
229 WHEN OTHERS THEN
230 ROLLBACK TO CreateBSCTabPVT;
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
232 IF (x_msg_data IS NOT NULL) THEN
233 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Create_Tab ';
234 ELSE
235 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Create_Tab ';
236 END IF;
237 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
238 RAISE;
239 end Create_Tab;
240
241 /************************************************************************************
242 ************************************************************************************/
243
244 procedure Retrieve_Tab(
245 p_commit IN varchar2 := FND_API.G_FALSE
246 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
247 ,x_Bsc_Tab_Entity_Rec IN OUT NOCOPY BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
248 ,x_return_status OUT NOCOPY VARCHAR2
249 ,x_msg_count OUT NOCOPY NUMBER
250 ,x_msg_data OUT NOCOPY VARCHAR2
251 ) is
252
253 begin
254
255 g_db_object := 'Retrieve_Tab';
256
257 SELECT DISTINCT A.KPI_MODEL
258 ,A.BSC_MODEL
259 ,A.CROSS_MODEL
260 ,A.DEFAULT_MODEL
261 ,A.ZOOM_FACTOR
262 ,A.CREATED_BY
263 ,A.CREATION_DATE
264 ,A.LAST_UPDATED_BY
265 ,A.LAST_UPDATE_DATE
266 ,A.LAST_UPDATE_LOGIN
267 ,A.TAB_INDEX
268 ,A.PARENT_TAB_ID
269 ,A.OWNER_ID
270 ,A.SHORT_NAME
271 ,B.NAME
272 ,B.HELP
273 ,B.ADDITIONAL_INFO
274 ,C.CSF_ID
275 ,C.CSF_TYPE
276 ,C.INTERMEDIATE_FLAG
277 ,D.CSF_ID
278 INTO x_Bsc_Tab_Entity_Rec.Bsc_Kpi_Model
279 ,x_Bsc_Tab_Entity_Rec.Bsc_Bsc_Model
280 ,x_Bsc_Tab_Entity_Rec.Bsc_Cross_Model
281 ,x_Bsc_Tab_Entity_Rec.Bsc_Default_Model
282 ,x_Bsc_Tab_Entity_Rec.Bsc_Zoom_Factor
283 ,x_Bsc_Tab_Entity_Rec.Bsc_Created_By
284 ,x_Bsc_Tab_Entity_Rec.Bsc_Creation_Date
285 ,x_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By
286 ,x_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Date
287 ,x_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login
288 ,x_Bsc_Tab_Entity_Rec.Bsc_Tab_Index
289 ,x_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id
290 ,x_Bsc_Tab_Entity_Rec.Bsc_Owner_Id
291 ,x_Bsc_Tab_Entity_Rec.Bsc_Short_Name
292 ,x_Bsc_Tab_Entity_Rec.Bsc_Tab_Name
293 ,x_Bsc_Tab_Entity_Rec.Bsc_Tab_Help
294 ,x_Bsc_Tab_Entity_Rec.Bsc_Tab_Info
295 ,x_Bsc_Tab_Entity_Rec.Bsc_Csf_Id
296 ,x_Bsc_Tab_Entity_Rec.Bsc_Csf_Type
297 ,x_Bsc_Tab_Entity_Rec.Bsc_Intermediate_Flag
298 ,x_Bsc_Tab_Entity_Rec.Bsc_Csf_Id
299 FROM BSC_TABS_B A
300 ,BSC_TABS_TL B
301 ,BSC_TAB_CSF_B C
302 ,BSC_TAB_CSF_TL D
303 WHERE A.TAB_ID = B.TAB_ID
304 AND A.TAB_ID = C.TAB_ID
305 AND C.TAB_ID = D.TAB_ID
306 AND A.TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
307 AND B.LANGUAGE = USERENV('LANG')
308 AND D.LANGUAGE = USERENV('LANG');
309 EXCEPTION
310 WHEN FND_API.G_EXC_ERROR THEN
311 FND_MSG_PUB.Count_And_Get
312 ( p_encoded => FND_API.G_FALSE
313 , p_count => x_msg_count
314 , p_data => x_msg_data
315 );
316 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
317 x_return_status := FND_API.G_RET_STS_ERROR;
318 RAISE;
319 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
320 FND_MSG_PUB.Count_And_Get
321 ( p_encoded => FND_API.G_FALSE
322 , p_count => x_msg_count
323 , p_data => x_msg_data
324 );
325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
326 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
327 RAISE;
328 WHEN NO_DATA_FOUND THEN
329 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
330 IF (x_msg_data IS NOT NULL) THEN
331 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Retrieve_Tab ';
332 ELSE
333 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Retrieve_Tab ';
334 END IF;
335 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
336 RAISE;
337 WHEN OTHERS THEN
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
339 IF (x_msg_data IS NOT NULL) THEN
340 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Retrieve_Tab ';
341 ELSE
342 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Retrieve_Tab ';
343 END IF;
344 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
345 RAISE;
346
347 end Retrieve_Tab;
348
349 /************************************************************************************
350 ************************************************************************************/
351
352 procedure Update_Tab(
353 p_commit IN varchar2 := FND_API.G_FALSE
354 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
355 ,x_return_status OUT NOCOPY varchar2
356 ,x_msg_count OUT NOCOPY number
357 ,x_msg_data OUT NOCOPY varchar2
358 ) is
359
360 l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
361 l_Bsc_Tab_Entity_Tbl BSC_SCORECARD_PUB.Bsc_Tab_Entity_Tbl;
362
363 l_count number;
364 l_move_flag number := 0; --Flag to move Tabs.
365
366 begin
367 FND_MSG_PUB.Initialize;
368 x_return_status := FND_API.G_RET_STS_SUCCESS;
369 SAVEPOINT UpdateBSCTabPVT;
370 -- Check that valid Tab id was entered.
371 if p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id is not null then
372 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_TABS_B'
373 ,'tab_id'
374 ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id);
375 if l_count = 0 then
376 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_TAB_ID');
377 FND_MESSAGE.SET_TOKEN('BSC_TAB', p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id);
378 FND_MSG_PUB.ADD;
379 RAISE FND_API.G_EXC_ERROR;
380 end if;
381 else
382 FND_MESSAGE.SET_NAME('BSC','BSC_NO_TAB_ID_ENTERED');
383 FND_MESSAGE.SET_TOKEN('BSC_TAB', p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id);
384 FND_MSG_PUB.ADD;
385 RAISE FND_API.G_EXC_ERROR;
386 end if;
387
388 -- Not all values will be passed. We need to make sure values not passed are not
389 -- changed by procedure, therefore we get what is there before we do any updates.
390 Retrieve_Tab( p_commit
391 ,p_Bsc_Tab_Entity_Rec
392 ,l_Bsc_Tab_Entity_Rec
393 ,x_return_status
394 ,x_msg_count
395 ,x_msg_data);
396
397
398
399 -- update LOCAL language ,source language and Tab Id values with PASSED values.
400 l_Bsc_Tab_Entity_Rec.Bsc_Language := p_Bsc_Tab_Entity_Rec.Bsc_Language;
401 l_Bsc_Tab_Entity_Rec.Bsc_Source_Language := p_Bsc_Tab_Entity_Rec.Bsc_Source_Language;
402 l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id := p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
403
404
405 -- Copy PASSED Record values into LOCAL Record values for the PASSED Record values
406 -- which are NOT NULL.
407 if p_Bsc_Tab_Entity_Rec.Bsc_Kpi_Model is not null then
408 l_Bsc_Tab_Entity_Rec.Bsc_Kpi_Model := p_Bsc_Tab_Entity_Rec.Bsc_Kpi_Model;
409 end if;
410 if p_Bsc_Tab_Entity_Rec.Bsc_Bsc_Model is not null then
411 l_Bsc_Tab_Entity_Rec.Bsc_Bsc_Model := p_Bsc_Tab_Entity_Rec.Bsc_Bsc_Model;
412 end if;
413 if p_Bsc_Tab_Entity_Rec.Bsc_Cross_Model is not null then
414 l_Bsc_Tab_Entity_Rec.Bsc_Cross_Model := p_Bsc_Tab_Entity_Rec.Bsc_Cross_Model;
415 end if;
416 if p_Bsc_Tab_Entity_Rec.Bsc_Default_Model is not null then
417 l_Bsc_Tab_Entity_Rec.Bsc_Default_Model := p_Bsc_Tab_Entity_Rec.Bsc_Default_Model;
418 end if;
419 if p_Bsc_Tab_Entity_Rec.Bsc_Zoom_Factor is not null then
420 l_Bsc_Tab_Entity_Rec.Bsc_Zoom_Factor := p_Bsc_Tab_Entity_Rec.Bsc_Zoom_Factor;
421 end if;
422 if p_Bsc_Tab_Entity_Rec.Bsc_Created_By is not null then
423 l_Bsc_Tab_Entity_Rec.Bsc_Created_By := p_Bsc_Tab_Entity_Rec.Bsc_Created_By;
424 end if;
425 if p_Bsc_Tab_Entity_Rec.Bsc_Creation_Date is not null then
426 l_Bsc_Tab_Entity_Rec.Bsc_Creation_Date := p_Bsc_Tab_Entity_Rec.Bsc_Creation_Date;
427 end if;
428 if p_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By is not null then
429 l_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By := p_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By;
430 end if;
431 if p_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Date is not null then
432 l_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Date := p_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Date;
433 end if;
434 if p_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login is not null then
435 l_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login := p_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login;
436 end if;
437 /* This was the Bug */
438 if p_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id = -2 then
439 l_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id := null;
440 end if;
441 if p_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id is not null then
442 if p_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id = -2 then
443 l_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id := null;
444 else
445 l_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id := p_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id;
446 end if;
447 end if;
448 if p_Bsc_Tab_Entity_Rec.Bsc_Owner_Id is not null then
449 l_Bsc_Tab_Entity_Rec.Bsc_Owner_Id := p_Bsc_Tab_Entity_Rec.Bsc_Owner_Id;
450 end if;
451 if p_Bsc_Tab_Entity_Rec.Bsc_Tab_Name is not null then
452 l_Bsc_Tab_Entity_Rec.Bsc_Tab_Name := p_Bsc_Tab_Entity_Rec.Bsc_Tab_Name;
453 end if;
454 if p_Bsc_Tab_Entity_Rec.Bsc_Tab_Help is not null then
455 l_Bsc_Tab_Entity_Rec.Bsc_Tab_Help := p_Bsc_Tab_Entity_Rec.Bsc_Tab_Help;
456 end if;
457 if p_Bsc_Tab_Entity_Rec.Bsc_Tab_Info is not null then
458 l_Bsc_Tab_Entity_Rec.Bsc_Tab_Info := p_Bsc_Tab_Entity_Rec.Bsc_Tab_Info;
459 end if;
460 if p_Bsc_Tab_Entity_Rec.Bsc_Csf_Id is not null then
461 l_Bsc_Tab_Entity_Rec.Bsc_Csf_Id := p_Bsc_Tab_Entity_Rec.Bsc_Csf_Id;
462 end if;
463 if p_Bsc_Tab_Entity_Rec.Bsc_Csf_Type is not null then
464 l_Bsc_Tab_Entity_Rec.Bsc_Csf_Type := p_Bsc_Tab_Entity_Rec.Bsc_Csf_Type;
465 end if;
466 if p_Bsc_Tab_Entity_Rec.Bsc_Intermediate_Flag is not null then
467 l_Bsc_Tab_Entity_Rec.Bsc_Intermediate_Flag := p_Bsc_Tab_Entity_Rec.Bsc_Intermediate_Flag;
468 end if;
469
470 -- Check to see if the Index has changed. If it has then all Tabs need to be moved.
471 if p_Bsc_Tab_Entity_Rec.Bsc_Tab_Index is not null then
472 if p_Bsc_Tab_Entity_Rec.Bsc_Tab_Index <> l_Bsc_Tab_Entity_Rec.Bsc_Tab_Index then
473 l_move_flag := 1;
474 end if;
475 l_Bsc_Tab_Entity_Rec.Bsc_Tab_Index := p_Bsc_Tab_Entity_Rec.Bsc_Tab_Index;
476 end if;
477
478 update BSC_TABS_B
479 set kpi_model = l_Bsc_Tab_Entity_Rec.Bsc_Kpi_Model
480 ,bsc_model = l_Bsc_Tab_Entity_Rec.Bsc_Bsc_Model
481 ,cross_model = l_Bsc_Tab_Entity_Rec.Bsc_Cross_Model
482 ,default_model = l_Bsc_Tab_Entity_Rec.Bsc_Default_Model
483 ,zoom_factor = l_Bsc_Tab_Entity_Rec.Bsc_Zoom_Factor
484 ,created_by = l_Bsc_Tab_Entity_Rec.Bsc_Created_By
485 ,creation_date = l_Bsc_Tab_Entity_Rec.Bsc_Creation_Date
486 ,last_updated_by = l_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By
487 ,last_update_date = l_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Date
488 ,last_update_login = l_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login
489 ,tab_index = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Index
490 ,parent_tab_id = l_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id
491 ,owner_id = l_Bsc_Tab_Entity_Rec.Bsc_Owner_Id
492 where tab_id = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
493
494 update BSC_TABS_TL
495 set name = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Name
496 ,help = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Help
497 ,ADDITIONAL_INFO = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Info
498 ,SOURCE_LANG = userenv('LANG')
499 where tab_id = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
500 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
501
502 update BSC_TAB_CSF_B
503 set csf_id = l_Bsc_Tab_Entity_Rec.Bsc_Csf_Id
504 ,csf_type = l_Bsc_Tab_Entity_Rec.Bsc_Csf_Type
505 ,intermediate_flag = l_Bsc_Tab_Entity_Rec.Bsc_Intermediate_Flag
506 where tab_id = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
507
508 update BSC_TAB_CSF_TL
509 set csf_id = l_Bsc_Tab_Entity_Rec.Bsc_Csf_Id
510 ,name = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Name
511 ,help = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Help
512 ,SOURCE_LANG = userenv('LANG')
513 where tab_id = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
514 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
515
516 --If the move flag is set to 1 then move all tabs.
517 if l_move_flag = 1 then
518 Move_Tab( p_commit
519 ,l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
520 ,l_Bsc_Tab_Entity_Rec.Bsc_Tab_Index
521 ,x_return_status
522 ,x_msg_count
523 ,x_msg_data);
524 end if;
525
526 IF (p_commit = FND_API.G_TRUE) THEN
527 COMMIT;
528 END IF;
529
530 EXCEPTION
531 WHEN FND_API.G_EXC_ERROR THEN
532 ROLLBACK TO UpdateBSCTabPVT;
533 FND_MSG_PUB.Count_And_Get
534 ( p_encoded => FND_API.G_FALSE
535 , p_count => x_msg_count
536 , p_data => x_msg_data
537 );
538 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
539 x_return_status := FND_API.G_RET_STS_ERROR;
540 RAISE;
541 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
542 ROLLBACK TO UpdateBSCTabPVT;
543 FND_MSG_PUB.Count_And_Get
544 ( p_encoded => FND_API.G_FALSE
545 , p_count => x_msg_count
546 , p_data => x_msg_data
547 );
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
550 RAISE;
551 WHEN NO_DATA_FOUND THEN
552 ROLLBACK TO UpdateBSCTabPVT;
553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
554 IF (x_msg_data IS NOT NULL) THEN
555 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Update_Tab ';
556 ELSE
557 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Update_Tab ';
558 END IF;
559 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
560 RAISE;
561 WHEN OTHERS THEN
562 ROLLBACK TO UpdateBSCTabPVT;
563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
564 IF (x_msg_data IS NOT NULL) THEN
565 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Update_Tab ';
566 ELSE
567 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Update_Tab ';
568 END IF;
569 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
570 RAISE;
571
572 end Update_Tab;
573
574 /************************************************************************************
575 ************************************************************************************/
576
577 procedure Delete_Tab(
578 p_commit IN VARCHAR2 := FND_API.G_FALSE
579 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
580 ,x_return_status OUT NOCOPY VARCHAR2
581 ,x_msg_count OUT NOCOPY NUMBER
582 ,x_msg_data OUT NOCOPY VARCHAR2
583 ) is
584
585 TYPE Recdc_value IS REF CURSOR;
586 dc_value Recdc_value;
587
588 l_sql VARCHAR2(1000);
589 l_child_tab NUMBER;
590 l_count NUMBER;
591 l_tab_index NUMBER;
592
593 CURSOR c_sys_images IS
594 SELECT image_id
595 FROM BSC_SYS_IMAGES
596 WHERE image_id NOT IN
597 ( SELECT DISTINCT(image_id)
598 FROM BSC_SYS_IMAGES_MAP_TL);
599
600 CURSOR c_indic_in_tab IS
601 SELECT INDICATOR
602 FROM BSC_TAB_INDICATORS
603 WHERE TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
604
605 CURSOR c_tab_views IS
606 SELECT tab_id, tab_view_id
607 FROM BSC_TAB_VIEWS_B
608 WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
609
610
611
612 begin
613 FND_MSG_PUB.Initialize;
614 x_return_status := FND_API.G_RET_STS_SUCCESS;
615 SAVEPOINT DeleteBSCTabPVT;
616 -- Check that valid Tab id was entered.
617 if p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id is not null then
618
619 SELECT COUNT(0)
620 INTO l_count
621 FROM BSC_TABS_B
622 WHERE Tab_Id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
623
624 if l_count = 0 then
625 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_TAB_ID');
626 FND_MESSAGE.SET_TOKEN('BSC_TAB', p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id);
627 FND_MSG_PUB.ADD;
628 RAISE FND_API.G_EXC_ERROR;
629 end if;
630 else
631 FND_MESSAGE.SET_NAME('BSC','BSC_NO_TAB_ID_ENTERED');
632 FND_MESSAGE.SET_TOKEN('BSC_TAB', p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id);
633 FND_MSG_PUB.ADD;
634 RAISE FND_API.G_EXC_ERROR;
635 end if;
636
637 -- Before we delete the tab we need to reset the tab id parent and tab_index
638 -- for the children of current tab.
639 -- get the index.
640 select max(tab_index)
641 into l_tab_index
642 from BSC_TABS_B
643 where tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
644
645 l_sql := 'select tab_id ' ||
646 ' from BSC_TABS_B ' ||
647 ' where parent_tab_id = :1';
648
649 open dc_value for l_sql using p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
650 loop
651 fetch dc_value into l_child_tab;
652 exit when dc_value%NOTFOUND;
653
654 -- update the index
655 l_tab_index := l_tab_index + 1;
656
657 update BSC_TABS_B
658 set parent_tab_id = null
659 ,tab_index = l_tab_index
660 where tab_id = l_child_tab;
661
662 end loop;
663 close dc_value;
664
665 /*
666
667 DELETE FROM BSC_TAB_INDICATORS
668 WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;*/
669
670
671 -- Unassingn the indicator from the tab first
672 -- The rules for unassign will apply while deleting tab also
673 FOR CD IN c_indic_in_tab LOOP
674
675 BSC_PMF_UI_WRAPPER.Unassign_KPI(
676 p_commit => FND_API.G_FALSE
677 ,p_kpi_id => CD.INDICATOR
678 ,p_tab_id => p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
679 ,x_return_status => x_return_status
680 ,x_msg_count => x_msg_count
681 ,x_msg_data => x_msg_data
682 );
683 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
685 END IF;
686
687 END LOOP;
688
689 IF(c_indic_in_tab%ISOPEN ) THEN
690 CLOSE c_indic_in_tab;
691 END IF;
692
693 -- delete form function defined for each custom view
694 FOR cd IN c_tab_views LOOP
695 BSC_CUSTOM_VIEW_UI_WRAPPER.delete_function( p_tab_id => cd.tab_id
696 ,p_tab_view_id => cd.tab_view_id
697 ,x_return_status => x_return_status
698 ,x_msg_count => x_msg_count
699 ,x_msg_data => x_msg_data);
700 END LOOP;
701 IF (c_tab_views%ISOPEN) THEN
702 CLOSE c_tab_views;
703 END IF;
704
705 -- delete pertinent values from pertinent tables.
706 DELETE FROM BSC_TABS_B
707 WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
708
709 DELETE FROM BSC_TABS_TL
710 WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
711
712 DELETE FROM BSC_TAB_IND_GROUPS_B
713 WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
714
715 DELETE FROM BSC_TAB_IND_GROUPS_TL
716 WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
717
718 DELETE FROM BSC_TAB_CSF_B
719 WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
720
721 DELETE FROM BSC_TAB_CSF_TL
722 WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
723
724 DELETE FROM BSC_USER_TAB_ACCESS
725 WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
726
727 DELETE FROM BSC_TAB_VIEW_LABELS_B
728 WHERE TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
729
730 DELETE FROM BSC_TAB_VIEW_LABELS_TL
731 WHERE TAB_ID =p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
732
733 DELETE FROM BSC_TAB_VIEWS_B
734 WHERE TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
735
736 DELETE FROM BSC_TAB_VIEWS_TL
737 WHERE TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
738
739 DELETE
740 FROM BSC_SYS_IMAGES_MAP_TL
741 WHERE SOURCE_TYPE IN (1,3)
742 AND SOURCE_CODE = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
743
744 FOR cd IN c_sys_images LOOP
745
746 DELETE
747 FROM BSC_SYS_IMAGES
748 WHERE IMAGE_ID = cd.image_id;
749
750 END LOOP;
751
752 DELETE FROM BSC_SYS_COM_DIM_LEVELS
753 WHERE TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
754
755
756 -- Role-based scorecard security
757 Remove_Scorecard_Grants(p_tab_id => p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id);
758
759 IF (p_commit = FND_API.G_TRUE) THEN
760 COMMIT;
761 END IF;
762
763 EXCEPTION
764 WHEN FND_API.G_EXC_ERROR THEN
765 ROLLBACK TO DeleteBSCTabPVT;
766 IF(c_indic_in_tab%ISOPEN ) THEN
767 CLOSE c_indic_in_tab;
768 END IF;
769 FND_MSG_PUB.Count_And_Get
770 ( p_encoded => FND_API.G_FALSE
771 , p_count => x_msg_count
772 , p_data => x_msg_data
773 );
774 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 RAISE;
777 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
778 ROLLBACK TO DeleteBSCTabPVT;
779 IF(c_indic_in_tab%ISOPEN ) THEN
780 CLOSE c_indic_in_tab;
781 END IF;
782
783 FND_MSG_PUB.Count_And_Get
784 ( p_encoded => FND_API.G_FALSE
785 , p_count => x_msg_count
786 , p_data => x_msg_data
787 );
788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
789 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
790 RAISE;
791 WHEN NO_DATA_FOUND THEN
792 ROLLBACK TO DeleteBSCTabPVT;
793 IF(c_indic_in_tab%ISOPEN ) THEN
794 CLOSE c_indic_in_tab;
795 END IF;
796
797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
798 IF (x_msg_data IS NOT NULL) THEN
799 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Delete_Tab ';
800 ELSE
801 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Delete_Tab ';
802 END IF;
803 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
804 RAISE;
805 WHEN OTHERS THEN
806 ROLLBACK TO DeleteBSCTabPVT;
807 IF(c_indic_in_tab%ISOPEN ) THEN
808 CLOSE c_indic_in_tab;
809 END IF;
810
811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
812 IF (x_msg_data IS NOT NULL) THEN
813 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Delete_Tab ';
814 ELSE
815 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Delete_Tab ';
816 END IF;
817 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
818 RAISE;
819
820 end Delete_Tab;
821
822 /************************************************************************************
823 ************************************************************************************/
824
825 procedure Create_Tab_Access(
826 p_commit IN varchar2 := FND_API.G_FALSE
827 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
828 ,x_return_status OUT NOCOPY varchar2
829 ,x_msg_count OUT NOCOPY number
830 ,x_msg_data OUT NOCOPY varchar2
831 ) is
832
833 l_count number;
834
835 begin
836 FND_MSG_PUB.Initialize;
837 x_return_status := FND_API.G_RET_STS_SUCCESS;
838 SAVEPOINT CreateBSCTabAccessPVT;
839 -- Check that valid Tab id was entered.
840 if p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id is not null then
841 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_TABS_B'
842 ,'tab_id'
843 ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id);
844 if l_count = 0 then
845 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_TAB_ID');
846 FND_MESSAGE.SET_TOKEN('BSC_TAB', p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id);
847 FND_MSG_PUB.ADD;
848 RAISE FND_API.G_EXC_ERROR;
849 end if;
850 else
851 FND_MESSAGE.SET_NAME('BSC','BSC_NO_TAB_ID_ENTERED');
852 FND_MESSAGE.SET_TOKEN('BSC_TAB', p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id);
853 FND_MSG_PUB.ADD;
854 RAISE FND_API.G_EXC_ERROR;
855 end if;
856
857 g_db_object := 'BSC_USER_TAB_ACCESS';
858
859 insert into BSC_USER_TAB_ACCESS( responsibility_id
860 ,tab_id
861 ,creation_date
862 ,created_by
863 ,last_update_date
864 ,last_updated_by
865 ,last_update_login
866 ,start_date
867 ,end_date)
868 values( p_Bsc_Tab_Entity_Rec.Bsc_Responsibility_Id
869 ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
870 ,sysdate
871 ,p_Bsc_Tab_Entity_Rec.Bsc_Created_By
872 ,sysdate
873 ,p_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By
874 ,p_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login
875 ,sysdate
876 ,p_Bsc_Tab_Entity_Rec.Bsc_Resp_End_Date);
877
878 IF (p_commit = FND_API.G_TRUE) THEN
879 COMMIT;
880 END IF;
881
882 EXCEPTION
883 WHEN FND_API.G_EXC_ERROR THEN
884 ROLLBACK TO CreateBSCTabAccessPVT;
885 FND_MSG_PUB.Count_And_Get
886 ( p_encoded => FND_API.G_FALSE
887 , p_count => x_msg_count
888 , p_data => x_msg_data
889 );
890 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
891 x_return_status := FND_API.G_RET_STS_ERROR;
892 RAISE;
893 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
894 ROLLBACK TO CreateBSCTabAccessPVT;
895 FND_MSG_PUB.Count_And_Get
896 ( p_encoded => FND_API.G_FALSE
897 , p_count => x_msg_count
898 , p_data => x_msg_data
899 );
900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
901 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
902 RAISE;
903 WHEN NO_DATA_FOUND THEN
904 ROLLBACK TO CreateBSCTabAccessPVT;
905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
906 IF (x_msg_data IS NOT NULL) THEN
907 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Create_Tab_Access ';
908 ELSE
909 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Create_Tab_Access ';
910 END IF;
911 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
912 RAISE;
913 WHEN OTHERS THEN
914 ROLLBACK TO CreateBSCTabAccessPVT;
915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
916 IF (x_msg_data IS NOT NULL) THEN
917 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Create_Tab_Access ';
918 ELSE
919 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Create_Tab_Access ';
920 END IF;
921 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
922 RAISE;
923
924 end Create_Tab_Access;
925
926 /************************************************************************************
927 ************************************************************************************/
928
929 -- Role-based scorecard security.
930 -- Initally, grant admin access to scorecard creater, and view access to all other designers.
931 procedure Create_Tab_Grants(
932 p_commit IN varchar2 := FND_API.G_FALSE
933 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
934 ,x_return_status OUT NOCOPY varchar2
935 ,x_msg_count OUT NOCOPY number
936 ,x_msg_data OUT NOCOPY varchar2
937 ) is
938
939 l_count number;
940
941 begin
942 FND_MSG_PUB.Initialize;
943 x_return_status := FND_API.G_RET_STS_SUCCESS;
944 SAVEPOINT CreateBSCTabGrantsPVT;
945
946 Insert_Scorecard_Grants(
947 p_tab_id => p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
948 ,p_user_name => FND_GLOBAL.USER_NAME);
949
950 IF (p_commit = FND_API.G_TRUE) THEN
951 COMMIT;
952 END IF;
953
954 EXCEPTION
955 WHEN FND_API.G_EXC_ERROR THEN
956 ROLLBACK TO CreateBSCTabGrantsPVT;
957 FND_MSG_PUB.Count_And_Get
958 ( p_encoded => FND_API.G_FALSE
959 , p_count => x_msg_count
960 , p_data => x_msg_data
961 );
962 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
963 x_return_status := FND_API.G_RET_STS_ERROR;
964 RAISE;
965 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
966 ROLLBACK TO CreateBSCTabGrantsPVT;
967 FND_MSG_PUB.Count_And_Get
968 ( p_encoded => FND_API.G_FALSE
969 , p_count => x_msg_count
970 , p_data => x_msg_data
971 );
972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
973 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
974 RAISE;
975 WHEN NO_DATA_FOUND THEN
976 ROLLBACK TO CreateBSCTabGrantsPVT;
977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
978 IF (x_msg_data IS NOT NULL) THEN
979 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Create_Tab_Grants ';
980 ELSE
981 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Create_Tab_Grants ';
982 END IF;
983 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
984 RAISE;
985 WHEN OTHERS THEN
986 ROLLBACK TO CreateBSCTabGrantsPVT;
987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
988 IF (x_msg_data IS NOT NULL) THEN
989 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Create_Tab_Grants ';
990 ELSE
991 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Create_Tab_Grants ';
992 END IF;
993 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
994 RAISE;
995
996 end Create_Tab_Grants;
997
998 /************************************************************************************
999 ************************************************************************************/
1000
1001 procedure Move_Tab(
1002 p_commit IN varchar2 := FND_API.G_FALSE
1003 ,p_tab_id IN number
1004 ,p_tab_index IN number
1005 ,x_return_status OUT NOCOPY varchar2
1006 ,x_msg_count OUT NOCOPY number
1007 ,x_msg_data OUT NOCOPY varchar2
1008 ) is
1009
1010 l_Bsc_Tab_Entity_Tbl BSC_SCORECARD_PUB.Bsc_Tab_Entity_Tbl;
1011
1012 TYPE Recdc_value IS REF CURSOR;
1013 dc_value Recdc_value;
1014
1015 l_cnt number;
1016
1017 l_sql varchar2(2000);
1018
1019 begin
1020 FND_MSG_PUB.Initialize;
1021 x_return_status := FND_API.G_RET_STS_SUCCESS;
1022 SAVEPOINT MoveBSCTabPVT;
1023 l_sql := 'select distinct tab_id, tab_index ' ||
1024 ' from BSC_TABS_B ' ||
1025 ' where tab_index >= :1' ||
1026 ' and tab_id <> :2' ||
1027 ' order by tab_index asc';
1028
1029 l_cnt := 0;
1030 open dc_value for l_sql using p_tab_index, p_tab_id;
1031 loop
1032 fetch dc_value into l_Bsc_Tab_Entity_Tbl(l_cnt + 1).Bsc_Tab_Id,
1033 l_Bsc_Tab_Entity_Tbl(l_cnt + 1).Bsc_Tab_Index;
1034 exit when dc_value%NOTFOUND;
1035 l_cnt := l_cnt + 1;
1036 end loop;
1037 close dc_value;
1038
1039 for i in 1..l_Bsc_Tab_Entity_Tbl.count loop
1040 update BSC_TABS_B
1041 set tab_index = l_Bsc_Tab_Entity_Tbl(i).Bsc_Tab_Index + 1
1042 where tab_id = l_Bsc_Tab_Entity_Tbl(i).Bsc_Tab_Id;
1043 end loop;
1044
1045 IF (p_commit = FND_API.G_TRUE) THEN
1046 COMMIT;
1047 END IF;
1048
1049 EXCEPTION
1050 WHEN FND_API.G_EXC_ERROR THEN
1051 ROLLBACK TO MoveBSCTabPVT;
1052 FND_MSG_PUB.Count_And_Get
1053 ( p_encoded => FND_API.G_FALSE
1054 , p_count => x_msg_count
1055 , p_data => x_msg_data
1056 );
1057 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1058 x_return_status := FND_API.G_RET_STS_ERROR;
1059 RAISE;
1060 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1061 ROLLBACK TO MoveBSCTabPVT;
1062 FND_MSG_PUB.Count_And_Get
1063 ( p_encoded => FND_API.G_FALSE
1064 , p_count => x_msg_count
1065 , p_data => x_msg_data
1066 );
1067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1068 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1069 RAISE;
1070 WHEN NO_DATA_FOUND THEN
1071 ROLLBACK TO MoveBSCTabPVT;
1072 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1073 IF (x_msg_data IS NOT NULL) THEN
1074 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Move_Tab ';
1075 ELSE
1076 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Move_Tab ';
1077 END IF;
1078 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1079 RAISE;
1080 WHEN OTHERS THEN
1081 ROLLBACK TO MoveBSCTabPVT;
1082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1083 IF (x_msg_data IS NOT NULL) THEN
1084 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Move_Tab ';
1085 ELSE
1086 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Move_Tab ';
1087 END IF;
1088 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1089 RAISE;
1090
1091 end Move_Tab;
1092
1093 /************************************************************************************
1094 ************************************************************************************/
1095
1096 procedure Update_Tab_Time_Stamp(
1097 p_commit IN varchar2 := FND_API.G_FALSE
1098 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
1099 ,x_return_status OUT NOCOPY varchar2
1100 ,x_msg_count OUT NOCOPY number
1101 ,x_msg_data OUT NOCOPY varchar2
1102 ) is
1103
1104 begin
1105 FND_MSG_PUB.Initialize;
1106 x_return_status := FND_API.G_RET_STS_SUCCESS;
1107 SAVEPOINT UpdateBSCTabTimStmPVT;
1108 update BSC_TABS_B
1109 set last_update_date = sysdate
1110 where tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
1111
1112 IF (p_commit = FND_API.G_TRUE) THEN
1113 COMMIT;
1114 END IF;
1115
1116 EXCEPTION
1117 WHEN FND_API.G_EXC_ERROR THEN
1118 ROLLBACK TO UpdateBSCTabTimStmPVT;
1119 FND_MSG_PUB.Count_And_Get
1120 ( p_encoded => FND_API.G_FALSE
1121 , p_count => x_msg_count
1122 , p_data => x_msg_data
1123 );
1124 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1125 x_return_status := FND_API.G_RET_STS_ERROR;
1126 RAISE;
1127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1128 ROLLBACK TO UpdateBSCTabTimStmPVT;
1129 FND_MSG_PUB.Count_And_Get
1130 ( p_encoded => FND_API.G_FALSE
1131 , p_count => x_msg_count
1132 , p_data => x_msg_data
1133 );
1134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1135 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1136 RAISE;
1137 WHEN NO_DATA_FOUND THEN
1138 ROLLBACK TO UpdateBSCTabTimStmPVT;
1139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1140 IF (x_msg_data IS NOT NULL) THEN
1141 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Update_Tab_Time_Stamp ';
1142 ELSE
1143 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Update_Tab_Time_Stamp ';
1144 END IF;
1145 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1146 RAISE;
1147 WHEN OTHERS THEN
1148 ROLLBACK TO UpdateBSCTabTimStmPVT;
1149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1150 IF (x_msg_data IS NOT NULL) THEN
1151 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Update_Tab_Time_Stamp ';
1152 ELSE
1153 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Update_Tab_Time_Stamp ';
1154 END IF;
1155 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1156 RAISE;
1157
1158 end Update_Tab_Time_Stamp;
1159
1160 /************************************************************************************
1161 ************************************************************************************/
1162
1163 procedure Update_System_Time_Stamp(
1164 p_commit IN varchar2 := FND_API.G_FALSE
1165 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
1166 ,x_return_status OUT NOCOPY varchar2
1167 ,x_msg_count OUT NOCOPY number
1168 ,x_msg_data OUT NOCOPY varchar2
1169 ) is
1170
1171 begin
1172 FND_MSG_PUB.Initialize;
1173 x_return_status := FND_API.G_RET_STS_SUCCESS;
1174 SAVEPOINT UpdateBSCTabSysTimStmPVT;
1175 update BSC_SYS_INIT
1176 set last_update_date = sysdate
1177 where property_code = 'LOCK_SYSTEM';
1178
1179 IF (p_commit = FND_API.G_TRUE) THEN
1180 COMMIT;
1181 END IF;
1182
1183 EXCEPTION
1184 WHEN FND_API.G_EXC_ERROR THEN
1185 ROLLBACK TO UpdateBSCTabSysTimStmPVT;
1186 FND_MSG_PUB.Count_And_Get
1187 ( p_encoded => FND_API.G_FALSE
1188 , p_count => x_msg_count
1189 , p_data => x_msg_data
1190 );
1191 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1192 x_return_status := FND_API.G_RET_STS_ERROR;
1193 RAISE;
1194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1195 ROLLBACK TO UpdateBSCTabSysTimStmPVT;
1196 FND_MSG_PUB.Count_And_Get
1197 ( p_encoded => FND_API.G_FALSE
1198 , p_count => x_msg_count
1199 , p_data => x_msg_data
1200 );
1201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1202 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1203 RAISE;
1204 WHEN NO_DATA_FOUND THEN
1205 ROLLBACK TO UpdateBSCTabSysTimStmPVT;
1206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1207 IF (x_msg_data IS NOT NULL) THEN
1208 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Update_System_Time_Stamp ';
1209 ELSE
1210 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Update_System_Time_Stamp ';
1211 END IF;
1212 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1213 RAISE;
1214 WHEN OTHERS THEN
1215 ROLLBACK TO UpdateBSCTabSysTimStmPVT;
1216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1217 IF (x_msg_data IS NOT NULL) THEN
1218 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PVT.Update_System_Time_Stamp ';
1219 ELSE
1220 x_msg_data := SQLERRM||' at BSC_SCORECARD_PVT.Update_System_Time_Stamp ';
1221 END IF;
1222 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1223 RAISE;
1224
1225 end Update_System_Time_Stamp;
1226
1227 /************************************************************************************
1228 ************************************************************************************/
1229
1230 function Validate_Tab(
1231 p_Tab_Name IN varchar2
1232 ) return number is
1233
1234 l_count number;
1235
1236 begin
1237
1238 select count(*)
1239 into l_count
1240 from BSC_TABS_TL
1241 where upper(name) = upper(p_Tab_Name);
1242
1243 return l_count;
1244
1245 EXCEPTION
1246 when others then
1247 rollback;
1248
1249 end Validate_Tab;
1250
1251 /************************************************************************************
1252 ************************************************************************************/
1253
1254 function Validate_Kpi_Group(
1255 p_Kpi_Group_Name IN varchar2
1256 ) return number is
1257
1258 l_count number;
1259
1260 begin
1261
1262 select count(*)
1263 into l_count
1264 from BSC_TAB_IND_GROUPS_TL
1265 where upper(name) = upper(p_Kpi_Group_Name);
1266
1267 return l_count;
1268
1269 EXCEPTION
1270 when others then
1271 rollback;
1272
1273 end Validate_Kpi_Group;
1274
1275 /************************************************************************************
1276 ************************************************************************************/
1277
1278 function Validate_Kpi(
1279 p_Kpi_Name IN varchar2
1280 ) return number is
1281
1282 l_count number;
1283
1284 begin
1285
1286 select count(*)
1287 into l_count
1288 from BSC_KPIS_TL
1289 where upper(name) = upper(p_Kpi_Name);
1290
1291 return l_count;
1292
1293 EXCEPTION
1294 when others then
1295 rollback;
1296
1297 end Validate_Kpi;
1298
1299 /************************************************************************************
1300 ************************************************************************************
1301 PROCEDURE Grant_Scorecard_Access
1302 Description :
1303 This procedure grants access to the users.For the user who is creating
1304 the Scorecard will have administartor access, while other users
1305 within the BSC_Manager and BSC_DESIGNER responsibility will have view
1306 access.
1307
1308 Input : p_tab_id,
1309 p_user_name
1310 Creator :
1311 ashankar 05-05-04
1312 Note: This API is called from VB part.. so don't change the exception block.
1313 Any exception raised will be logged into BSC_Messages and will be checked in
1314 VB part.
1315 /***********************************************************************************/
1316
1317
1318 PROCEDURE Insert_Scorecard_Grants
1319 (
1320 p_tab_id IN NUMBER
1321 , p_user_name IN VARCHAR2
1322 )IS
1323
1324 CURSOR c_BscUserPool IS
1325 SELECT distinct usr.user_name
1326 FROM fnd_user_resp_groups ur,
1327 fnd_responsibility r,
1328 fnd_user usr
1329 WHERE ur.responsibility_id = r.responsibility_id
1330 AND usr.user_id = ur.user_id
1331 AND ur.responsibility_application_id = r.application_id
1332 AND r.application_id = 271
1333 AND r.responsibility_key IN ('BSC_DESIGNER' ,'BSC_Manager')
1334 AND SYSDATE BETWEEN usr.Start_Date AND NVL(usr.End_Date, SYSDATE)
1335 AND SYSDATE BETWEEN r.Start_Date AND NVL(r.End_Date, SYSDATE)
1336 AND SYSDATE BETWEEN ur.Start_Date AND NVL(ur.End_Date, SYSDATE);
1337
1338
1339 l_count NUMBER;
1340 l_grant_guid FND_GRANTS.grant_guid%TYPE;
1341 l_success VARCHAR2(5);
1342 l_errorcode NUMBER;
1343 l_user_name VARCHAR2(256);
1344
1345 BEGIN
1346
1347 IF(p_tab_id IS NOT NULL ) THEN
1348
1349 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_TABS_B','tab_id',p_tab_id);
1350 IF l_count = 0 THEN
1351 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_TAB_ID');
1352 FND_MESSAGE.SET_TOKEN('BSC_TAB', p_tab_id);
1353 FND_MSG_PUB.ADD;
1354 RAISE FND_API.G_EXC_ERROR;
1355 END IF;
1356 ELSE
1357 FND_MESSAGE.SET_NAME('BSC','BSC_NO_TAB_ID_ENTERED');
1358 FND_MESSAGE.SET_TOKEN('BSC_TAB', p_tab_id);
1359 FND_MSG_PUB.ADD;
1360 RAISE FND_API.G_EXC_ERROR;
1361 END IF;
1362
1363 /*User name can never be null, so no need to check for null condition*/
1364
1365 FND_GRANTS_PKG.GRANT_FUNCTION
1366 (
1367 p_api_version => 1.0
1368 ,p_menu_name => BSC_ADMIN_ACCESS
1369 ,p_object_name => BSC_OBJECT_NAME
1370 ,p_instance_type => BSC_INSTANCE_TYPE
1371 ,p_instance_pk1_value => to_char(p_tab_id)
1372 ,p_grantee_type => BSC_GRANTEE_TYPE
1373 ,p_grantee_key => UPPER(p_user_name)
1374 ,p_start_date => SYSDATE
1375 ,p_end_date => NULL
1376 ,p_program_name => BSC_PROGRAM_NAME
1377 ,x_grant_guid => l_grant_guid
1378 ,x_success => l_success
1379 ,x_errorcode => l_errorcode
1380 );
1381 IF (l_success <> FND_API.G_TRUE) THEN
1382 --DBMS_OUTPUT.PUT_LINE('BSC_SCORECARD_PVT.Grant_Scorecard_Access Failed: at FND_GRANTS_DELETE_PKG.GRANT_FUNCTION );
1383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1384 END IF;
1385
1386 /****************************************************
1387 Except the current user other users will have the Viewer
1388 access, so we have to filter out the current user from the
1389 list of users returned by the cursor.
1390 /***************************************************/
1391
1392 FOR user_pool IN c_BscUserPool LOOP
1393 l_user_name := user_pool.user_name;
1394 IF(UPPER(l_user_name)<>UPPER(p_user_name)) THEN
1395 FND_GRANTS_PKG.GRANT_FUNCTION
1396 (
1397 p_api_version => 1.0
1398 ,p_menu_name => BSC_VIEWER_ACCESS
1399 ,p_object_name => BSC_OBJECT_NAME
1400 ,p_instance_type => BSC_INSTANCE_TYPE
1401 ,p_instance_pk1_value => TO_CHAR(p_tab_id)
1402 ,p_grantee_type => BSC_GRANTEE_TYPE
1403 ,p_grantee_key => UPPER(l_user_name)
1404 ,p_start_date => SYSDATE
1405 ,p_end_date => NULL
1406 ,p_program_name => BSC_PROGRAM_NAME
1407 ,x_grant_guid => l_grant_guid
1408 ,x_success => l_success
1409 ,x_errorcode => l_errorcode
1410 );
1411 IF (l_success <> FND_API.G_TRUE) THEN
1412 --DBMS_OUTPUT.PUT_LINE('BSC_SCORECARD_PVT.Grant_Scorecard_Access Failed: at FND_GRANTS_DELETE_PKG.GRANT_FUNCTION );
1413 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1414 END IF;
1415 END IF;
1416 END LOOP;
1417
1418
1419 EXCEPTION
1420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1421 BSC_MESSAGE.Add
1422 ( x_message => SQLERRM
1423 , x_source => 'BSC_SCORECARD_PVT.Insert_Scorecard_Grants'
1424 , x_mode => 'I'
1425 );
1426
1427
1428 WHEN FND_API.G_EXC_ERROR THEN
1429 BSC_MESSAGE.Add
1430 ( x_message => SQLERRM
1431 , x_source => 'BSC_SCORECARD_PVT.Insert_Scorecard_Grants'
1432 , x_mode => 'I'
1433 );
1434
1435 WHEN OTHERS THEN
1436 BSC_MESSAGE.Add
1437 ( x_message => SQLERRM
1438 , x_source => 'BSC_SCORECARD_PVT.Insert_Scorecard_Grants'
1439 , x_mode => 'I'
1440 );
1441
1442 END Insert_Scorecard_Grants;
1443
1444 /************************************************************************************
1445 PROCEDURE Remove_Scorecard_Grants
1446 Description :
1447 This procedure Should be CALLED TO remove the records from FND_Grants table
1448 after the scorecard is deleted.
1449
1450 Input : p_tab_id,
1451 p_user_name
1452 Creator :
1453 ashankar 05-05-04
1454 Note: This API is called from VB and PMD part.. so don't change the exception block.
1455 Any exception raised will be logged into BSC_Messages and will be checked in
1456 VB part.
1457 /***********************************************************************************/
1458
1459 PROCEDURE Remove_Scorecard_Grants
1460 (
1461 p_tab_id IN NUMBER
1462 )IS
1463 l_success VARCHAR2(5);
1464 l_errorcode NUMBER;
1465 l_object_id NUMBER;
1466 /*CURSOR c_BscUserPool IS
1467 SELECT DISTINCT U.USER_ID,U.USER_NAME,U.FULL_NAME
1468 FROM bsc_apps_users_v U,
1469 FND_USER_RESP_GROUPS fug,
1470 FND_RESPONSIBILITY rd
1471 WHERE U.BSC_VALID_FLAG =1
1472 AND U.USER_ID = fug.USER_ID
1473 AND fug.RESPONSIBILITY_ID = rd.RESPONSIBILITY_ID
1474 AND UPPER(RESPONSIBILITY_KEY) IN ('BSC_MANAGER','BSC_DESIGNER') ;*/
1475
1476 BEGIN
1477
1478
1479 /*FOR user_pool IN c_BscUserPool LOOP
1480
1481 FND_GRANTS_PKG.delete_grant
1482 (
1483 p_grantee_type => BSC_GRANTEE_TYPE
1484 , p_object_name => BSC_OBJECT_NAME
1485 , p_grantee_key => upper(user_pool.user_name)
1486 , p_instance_type => BSC_INSTANCE_TYPE
1487 , p_instance_pk1_value => to_char(p_tab_id)
1488 , p_instance_pk2_value => '*NULL*'
1489 , p_instance_pk3_value => '*NULL*'
1490 , p_instance_pk4_value => '*NULL*'
1491 , p_instance_pk5_value => '*NULL*'
1492 , p_program_name => BSC_PROGRAM_NAME
1493 , x_success => l_success
1494 , x_errcode => l_errorcode
1495 );
1496
1497 IF (l_success <> FND_API.G_TRUE) THEN
1498 --DBMS_OUTPUT.PUT_LINE('BSC_SCORECARD_PVT.Remove_Scorecard_Grants Failed: at FND_GRANTS_DELETE_PKG.delete_grant );
1499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1500 END IF;
1501 END LOOP; */
1502
1503 SELECT OBJECT_ID
1504 INTO l_object_id
1505 FROM FND_OBJECTS
1506 WHERE OBJ_NAME = 'BSC_TAB';
1507
1508 DELETE FROM FND_GRANTS
1509 WHERE OBJECT_ID = TO_CHAR(l_object_id)
1510 AND INSTANCE_TYPE = 'INSTANCE'
1511 AND INSTANCE_PK1_VALUE = TO_CHAR(p_tab_id)
1512 AND PROGRAM_NAME = 'BSC_PMD_GRANTS';
1513
1514 EXCEPTION
1515 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1516 BSC_MESSAGE.Add
1517 (
1518 x_message => SQLERRM
1519 , x_source => 'BSC_SCORECARD_PVT.Remove_Scorecard_Grants'
1520 , x_mode => 'I'
1521 );
1522
1523 WHEN OTHERS THEN
1524 BSC_MESSAGE.Add
1525 (
1526 x_message => SQLERRM
1527 , x_source => 'BSC_SCORECARD_PVT.Remove_Scorecard_Grants'
1528 , x_mode => 'I'
1529
1530 );
1531 END Remove_Scorecard_Grants;
1532 procedure REVOKE_GRANT
1533 ( p_commit IN VARCHAR2
1534 , p_api_version IN NUMBER
1535 , p_grant_guid IN VARCHAR2
1536 , x_success OUT NOCOPY VARCHAR2
1537 , x_errorcode OUT NOCOPY NUMBER
1538 ) IS
1539
1540 xa_success VARCHAR2(32000);
1541 xa_errorcode NUMBER;
1542 l_grant_guids VARCHAR2(32000);
1543 l_single_grant_guid VARCHAR2(32000);
1544 BEGIN
1545 l_grant_guids := p_grant_guid;
1546 WHILE (is_more( p_grant_uids => l_grant_guids
1547 , p_grant_uid => l_single_grant_guid)
1548 ) LOOP
1549 begin
1550 FND_GRANTS_PKG.REVOKE_GRANT( p_api_version => p_api_version
1551 , p_grant_guid => l_single_grant_guid
1552 , x_success => xa_success
1553 , x_errorcode => xa_errorcode
1554 );
1555 EXCEPTION WHEN OTHERS THEN NULL; END;
1556 END LOOP;
1557 IF (p_commit = 'T') THEN
1558 COMMIT;
1559 END IF;
1560 EXCEPTION
1561 when others then
1562 BSC_MESSAGE.Add(x_message => sqlerrm,
1563 x_source => 'reovkeaccess',
1564 x_mode => 'I');
1565
1566
1567 END REVOKE_GRANT;
1568
1569
1570 FUNCTION Is_More
1571 ( p_grant_uids IN OUT NOCOPY VARCHAR2
1572 , p_grant_uid OUT NOCOPY VARCHAR2
1573 ) RETURN BOOLEAN
1574 IS
1575 l_pos_ids NUMBER;
1576 l_pos_rel_types NUMBER;
1577 l_pos_rel_columns NUMBER;
1578 BEGIN
1579 IF (p_grant_uids IS NOT NULL) THEN
1580 l_pos_ids := INSTR(p_grant_uids, ',');
1581 IF (l_pos_ids > 0) THEN
1582 p_grant_uid := TRIM(SUBSTR(p_grant_uids, 1, l_pos_ids - 1));
1583 p_grant_uids := TRIM(SUBSTR(p_grant_uids, l_pos_ids + 1));
1584 ELSE
1585 p_grant_uid := TRIM(p_grant_uids);
1586 p_grant_uids := NULL;
1587 END IF;
1588 RETURN TRUE;
1589 ELSE
1590 RETURN FALSE;
1591 END IF;
1592 END Is_More;
1593 end BSC_SCORECARD_PVT;