1 package body BSC_SCORECARD_PUB as
2 /* $Header: BSCPTABB.pls 120.6 2007/12/10 11:19:39 bijain ship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCPTABB.pls |
10 | |
11 | Creation Date: |
12 | October 22, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | Public Body version |
19 | This package creates a BSC Scorecard/Tab. |
20 | |
21 | 20-MAR-03 PWALI for bug #2843082 |
22 | 24-JUL-03 Adeulgao fixed bug#3047536 granted access of tabs to BSC_PMD_USER |
23 | 15-DEC-03 Aditya Rao removed Dynamic SQLs for Bug #3236356 |
24 | 10-MAR-04 jxyu Modified for enhancement #3493589 |
25 | 06-MAY-04 ADRAO added code to handle BIS_DBI_ADMIN responsibility |
26 | 18-MAY-04 adrao Modified PL/SQL records and CRUD to accept SHORT_NAME |
27 | 20-JUL-04 adrao added PMU Responsibility for all S2E KPIs created from |
28 | Configure Region link from any responsibility. Bug#3775876 |
29 | 10-SEP-04 adrao modified Create_Tab_User_Access for Bug#3877636 |
30 | 13-JUL-05 akoduri Bug #4368221 Added the function Get_Custom_View_Name |
31 | 12-AGU-05 Kyadamak Bug#4462346 Modified function Check_Tab_UserAccess() |
32 | 23-AUG-05 visuri Added Validate_Scorecard_Revoke(),Chk_Child_Scd_Has_Access() |
33 | Validate_Scorecard_Access() for bug 4103395 |
34 | 01-SEP-05 Aditya Rao fixed Bug#4563456 in API Create_Tab_Access () |
35 | 02-NOV-07 bijain BugFix 6340598 |
36 +======================================================================================+
37 */
38
39 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_SCORECARD_PUB';
40
41
42 --New procedure with OUT parameter
43 procedure Create_Tab(
44 p_commit IN varchar2 := FND_API.G_FALSE
45 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
46 ,x_Bsc_Tab_Entity_Rec OUT NOCOPY BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
47 ,x_return_status OUT NOCOPY varchar2
48 ,x_msg_count OUT NOCOPY number
49 ,x_msg_data OUT NOCOPY varchar2
50 ) is
51
52 l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
53
54 begin
55 FND_MSG_PUB.Initialize;
56 x_return_status := FND_API.G_RET_STS_SUCCESS;
57 -- Assign all values in the passed "Record" parameter to the locally defined
58 -- "Record" variable.
59 l_Bsc_Tab_Entity_Rec := p_Bsc_Tab_Entity_Rec;
60
61 -- Check that this Tab name does not exist.
62 if l_Bsc_Tab_Entity_Rec.Bsc_Tab_Name is not null then
63 if BSC_SCORECARD_PVT.Validate_Tab(l_Bsc_Tab_Entity_Rec.Bsc_Tab_Name) > 0 then
64 FND_MESSAGE.SET_NAME('BSC','BSC_TAB_NAME_EXISTS');
65 FND_MESSAGE.SET_TOKEN('BSC_TAB', l_Bsc_Tab_Entity_Rec.Bsc_Tab_Name);
66 FND_MSG_PUB.ADD;
67 RAISE FND_API.G_EXC_ERROR;
68 end if;
69 end if;
70
71 -- Get the next id available for the current Tab.
72 -- Bug #3236356
73 SELECT (NVL(MAX(TAB_ID), 0) + 1)
74 INTO l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
75 FROM BSC_TABS_B;
76
77 --DBMS_OUTPUT.PUT_LINE('tab _id ==============' || l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id);
78
79 -- If tab name is null then assign default name.
80 if l_Bsc_Tab_Entity_Rec.Bsc_Tab_Name is null then
81 l_Bsc_Tab_Entity_Rec.Bsc_Tab_Name := BSC_APPS.GET_LOOKUP_VALUE('BSC_UI_BUILDER', 'TAB')|| ' ' || l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
82 end if;
83
84 -- Bug #3236356
85 SELECT (NVL(MAX(TAB_INDEX), 0) + 1)
86 INTO l_Bsc_Tab_Entity_Rec.Bsc_Tab_Index
87 FROM BSC_TABS_B;
88 -- The Tab needs an Index (location within BSC Tabs) get the next index.
89
90
91
92 --DBMS_OUTPUT.PUT_LINE('parent tab id==============' || l_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id);
93 --DBMS_OUTPUT.PUT_LINE('tab index ==============' || l_Bsc_Tab_Entity_Rec.Bsc_Tab_Index);
94 --DBMS_OUTPUT.PUT_LINE('tab name ==============' || l_Bsc_Tab_Entity_Rec.Bsc_Tab_Name);
95
96
97
98 BSC_SCORECARD_PVT.Create_Tab( p_commit
99 ,l_Bsc_Tab_Entity_Rec
100 ,x_return_status
101 ,x_msg_count
102 ,x_msg_data);
103 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
105 END IF;
106
107 IF(l_Bsc_Tab_Entity_Rec.Bsc_Short_Name is NULL) THEN
108 Create_Tab_Access( p_commit
109 ,l_Bsc_Tab_Entity_Rec
110 ,x_return_status
111 ,x_msg_count
112 ,x_msg_data);
113 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 END IF;
116
117 -- Role-based scorecard security.
118 -- Initally, grant admin access to scorecard creater, and view access to all other designers.
119 Create_Tab_Grants( p_commit
120 ,l_Bsc_Tab_Entity_Rec
121 ,x_return_status
122 ,x_msg_count
123 ,x_msg_data);
124
125 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
127 END IF;
128 END IF;
129 x_Bsc_Tab_Entity_Rec := l_Bsc_Tab_Entity_Rec;
130
131 EXCEPTION
132 WHEN FND_API.G_EXC_ERROR THEN
133 IF (x_msg_data IS NULL) THEN
134 FND_MSG_PUB.Count_And_Get
135 ( p_encoded => FND_API.G_FALSE
136 , p_count => x_msg_count
137 , p_data => x_msg_data
138 );
139 END IF;
140 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
141 x_return_status := FND_API.G_RET_STS_ERROR;
142 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
143 IF (x_msg_data IS NULL) THEN
144 FND_MSG_PUB.Count_And_Get
145 ( p_encoded => FND_API.G_FALSE
146 , p_count => x_msg_count
147 , p_data => x_msg_data
148 );
149 END IF;
150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
151 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
152 WHEN NO_DATA_FOUND THEN
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154 IF (x_msg_data IS NOT NULL) THEN
155 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Create_Tab with parameter x_Bsc_Tab_Entity_Rec ';
156 ELSE
157 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Create_Tab with parameter x_Bsc_Tab_Entity_Rec ';
158 END IF;
159 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
160 WHEN OTHERS THEN
161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
162 IF (x_msg_data IS NOT NULL) THEN
163 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Create_Tab with parameter x_Bsc_Tab_Entity_Rec ';
164 ELSE
165 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Create_Tab with parameter x_Bsc_Tab_Entity_Rec ';
166 END IF;
167 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
168
169 end Create_Tab;
170
171
172 /************************************************************************************
173 ************************************************************************************/
174 --Modified procedure without OUT parameter
175 procedure Create_Tab(
176 p_commit IN varchar2 := FND_API.G_FALSE
177 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
178 ,x_return_status OUT NOCOPY varchar2
179 ,x_msg_count OUT NOCOPY number
180 ,x_msg_data OUT NOCOPY varchar2
181 ) is
182
183 l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
184 l_Bsc_Tab_Entity_Rec_Out BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
185
186 begin
187 FND_MSG_PUB.Initialize;
188 x_return_status := FND_API.G_RET_STS_SUCCESS;
189 l_Bsc_Tab_Entity_Rec := p_Bsc_Tab_Entity_Rec;
190
191 BSC_SCORECARD_PUB.Create_Tab(
192 p_commit => p_commit
193 ,p_Bsc_Tab_Entity_Rec => l_Bsc_Tab_Entity_Rec
194 ,x_Bsc_Tab_Entity_Rec => l_Bsc_Tab_Entity_Rec_Out
195 ,x_return_status => x_return_status
196 ,x_msg_count => x_msg_count
197 ,x_msg_data => x_msg_data
198 );
199 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
201 END IF;
202
203
204 EXCEPTION
205 WHEN FND_API.G_EXC_ERROR THEN
206 IF (x_msg_data IS NULL) THEN
207 FND_MSG_PUB.Count_And_Get
208 ( p_encoded => FND_API.G_FALSE
209 , p_count => x_msg_count
210 , p_data => x_msg_data
211 );
212 END IF;
213 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
214 x_return_status := FND_API.G_RET_STS_ERROR;
215 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
216 IF (x_msg_data IS NULL) THEN
217 FND_MSG_PUB.Count_And_Get
218 ( p_encoded => FND_API.G_FALSE
219 , p_count => x_msg_count
220 , p_data => x_msg_data
221 );
222 END IF;
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
225 WHEN NO_DATA_FOUND THEN
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 IF (x_msg_data IS NOT NULL) THEN
228 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Create_Tab ';
229 ELSE
230 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Create_Tab ';
231 END IF;
232 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
233 WHEN OTHERS THEN
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235 IF (x_msg_data IS NOT NULL) THEN
236 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Create_Tab ';
237 ELSE
238 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Create_Tab ';
239 END IF;
240 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
241
242 end Create_Tab;
243
244
245 /************************************************************************************
246 ************************************************************************************/
247 --new procedure. Initializing the Tab Entity record.
248 procedure Initialize_Tab_Entity_Rec(
249 p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
250 ,x_Bsc_Tab_Entity_Rec OUT NOCOPY BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
251 ,x_return_status OUT NOCOPY varchar2
252 ,x_msg_count OUT NOCOPY number
253 ,x_msg_data OUT NOCOPY varchar2
254 ) is
255
256 begin
257 FND_MSG_PUB.Initialize;
258 x_return_status := FND_API.G_RET_STS_SUCCESS;
259
260 x_Bsc_Tab_Entity_Rec := p_Bsc_Tab_Entity_Rec;
261
262 --set some default values
263 x_Bsc_Tab_Entity_Rec.Bsc_Kpi_Model := 1;
264 x_Bsc_Tab_Entity_Rec.Bsc_Bsc_Model := 0;
265 x_Bsc_Tab_Entity_Rec.Bsc_Default_Model := 0;
266 x_Bsc_Tab_Entity_Rec.Bsc_Created_By := 0;
267 x_Bsc_Tab_Entity_Rec.Bsc_Csf_Id := 0;
268 x_Bsc_Tab_Entity_Rec.Bsc_Csf_Type := 0;
269 x_Bsc_Tab_Entity_Rec.Bsc_Intermediate_Flag := 0;
270 x_Bsc_Tab_Entity_Rec.Bsc_Language := 'US';
271 x_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By := 0;
272 x_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login := 0;
273 x_Bsc_Tab_Entity_Rec.Bsc_Source_Language := 'US';
274
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279 IF (x_msg_data IS NOT NULL) THEN
280 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Initialize_Tab_Entity_Rec ';
281 ELSE
282 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Initialize_Tab_Entity_Rec ';
283 END IF;
284 RAISE;
285 end Initialize_Tab_Entity_Rec;
286
287
288 /************************************************************************************
289 ************************************************************************************/
290
291 procedure Retrieve_Tab(
292 p_commit IN varchar2 := FND_API.G_FALSE
293 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
294 ,x_Bsc_Tab_Entity_Rec IN OUT NOCOPY BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
295 ,x_return_status OUT NOCOPY varchar2
296 ,x_msg_count OUT NOCOPY number
297 ,x_msg_data OUT NOCOPY varchar2
298 ) is
299
300 begin
301 FND_MSG_PUB.Initialize;
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303 BSC_SCORECARD_PVT.Retrieve_Tab( p_commit
304 ,p_Bsc_Tab_Entity_Rec
305 ,x_Bsc_Tab_Entity_Rec
306 ,x_return_status
307 ,x_msg_count
308 ,x_msg_data);
309
310 EXCEPTION
311 WHEN FND_API.G_EXC_ERROR THEN
312 IF (x_msg_data IS NULL) THEN
313 FND_MSG_PUB.Count_And_Get
314 ( p_encoded => FND_API.G_FALSE
315 , p_count => x_msg_count
316 , p_data => x_msg_data
317 );
318 END IF;
319 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
320 x_return_status := FND_API.G_RET_STS_ERROR;
321 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
322 IF (x_msg_data IS NULL) THEN
323 FND_MSG_PUB.Count_And_Get
324 ( p_encoded => FND_API.G_FALSE
325 , p_count => x_msg_count
326 , p_data => x_msg_data
327 );
328 END IF;
329 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
330 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
331 WHEN NO_DATA_FOUND THEN
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
333 IF (x_msg_data IS NOT NULL) THEN
334 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Retrieve_Tab ';
335 ELSE
336 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Retrieve_Tab ';
337 END IF;
338 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
339 WHEN OTHERS THEN
340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
341 IF (x_msg_data IS NOT NULL) THEN
342 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Retrieve_Tab ';
343 ELSE
344 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Retrieve_Tab ';
345 END IF;
346 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
347
348 end Retrieve_Tab;
349
350 /************************************************************************************
351 ************************************************************************************/
352
353 procedure Update_Tab(
354 p_commit IN varchar2 := FND_API.G_FALSE
355 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
356 ,x_return_status OUT NOCOPY varchar2
357 ,x_msg_count OUT NOCOPY number
358 ,x_msg_data OUT NOCOPY varchar2
359 ) is
360
361 begin
362 FND_MSG_PUB.Initialize;
363 x_return_status := FND_API.G_RET_STS_SUCCESS;
364 BSC_SCORECARD_PVT.Update_Tab( p_commit
365 ,p_Bsc_Tab_Entity_Rec
366 ,x_return_status
367 ,x_msg_count
368 ,x_msg_data);
369
370 EXCEPTION
371 WHEN FND_API.G_EXC_ERROR THEN
372 IF (x_msg_data IS NULL) THEN
373 FND_MSG_PUB.Count_And_Get
374 ( p_encoded => FND_API.G_FALSE
375 , p_count => x_msg_count
376 , p_data => x_msg_data
377 );
378 END IF;
379 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
382 IF (x_msg_data IS NULL) THEN
383 FND_MSG_PUB.Count_And_Get
384 ( p_encoded => FND_API.G_FALSE
385 , p_count => x_msg_count
386 , p_data => x_msg_data
387 );
388 END IF;
389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
390 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
391 WHEN NO_DATA_FOUND THEN
392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
393 IF (x_msg_data IS NOT NULL) THEN
394 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Update_Tab ';
395 ELSE
396 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Update_Tab ';
397 END IF;
398 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
399 WHEN OTHERS THEN
400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
401 IF (x_msg_data IS NOT NULL) THEN
402 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Update_Tab ';
403 ELSE
404 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Update_Tab ';
405 END IF;
406 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
407
408 end Update_Tab;
409
410 /************************************************************************************
411 ************************************************************************************/
412
413 procedure Delete_Tab(
414 p_commit IN varchar2 := FND_API.G_FALSE
415 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
416 ,x_return_status OUT NOCOPY varchar2
417 ,x_msg_count OUT NOCOPY number
418 ,x_msg_data OUT NOCOPY varchar2
419 ) is
420
421 begin
422 FND_MSG_PUB.Initialize;
423 x_return_status := FND_API.G_RET_STS_SUCCESS;
424 BSC_SCORECARD_PVT.Delete_Tab( p_commit
425 ,p_Bsc_Tab_Entity_Rec
426 ,x_return_status
427 ,x_msg_count
428 ,x_msg_data);
429
430 EXCEPTION
431 WHEN FND_API.G_EXC_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 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
440 x_return_status := FND_API.G_RET_STS_ERROR;
441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
442 IF (x_msg_data IS NULL) THEN
443 FND_MSG_PUB.Count_And_Get
444 ( p_encoded => FND_API.G_FALSE
445 , p_count => x_msg_count
446 , p_data => x_msg_data
447 );
448 END IF;
449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
450 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
451 WHEN NO_DATA_FOUND 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_SCORECARD_PUB.Delete_Tab ';
455 ELSE
456 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Delete_Tab ';
457 END IF;
458 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
459 WHEN OTHERS THEN
460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
461 IF (x_msg_data IS NOT NULL) THEN
462 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Delete_Tab ';
463 ELSE
464 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Delete_Tab ';
465 END IF;
466 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
467
468 end Delete_Tab;
469 /************************************************************************************
470 Function : Create_Tab_Access_For_Resp
471 Description : This function will assign a scorecard to a given responsibility
472 ***********************************************************************************/
473 PROCEDURE Create_Tab_Access_For_Resp(
474 p_Resposibility_Key IN VARCHAR2
475 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
476 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
477 ,x_return_status OUT NOCOPY VARCHAR2
478 ,x_msg_count OUT NOCOPY NUMBER
479 ,x_msg_data OUT NOCOPY VARCHAR2
480 )IS
481 l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
482 l_Count NUMBER;
483
484 CURSOR c_Resp_Ids IS
485 SELECT responsibility_id
486 FROM fnd_responsibility
487 WHERE INSTR(','||p_Resposibility_Key||',',','||responsibility_key||',') > 0;
488
489 BEGIN
490 FND_MSG_PUB.Initialize;
491 x_return_status := FND_API.G_RET_STS_SUCCESS;
492 l_Bsc_Tab_Entity_Rec := p_Bsc_Tab_Entity_Rec;
493
494 FOR CD IN c_Resp_Ids LOOP
495 SELECT COUNT(1)
496 INTO l_Count
497 FROM bsc_user_tab_access
498 WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
499 AND responsibility_id = CD.responsibility_id;
500 --dbms_output.put_line(' resp id :-' || CD.responsibility_id);
501 l_Bsc_Tab_Entity_Rec.Bsc_Responsibility_Id := CD.responsibility_id;
502
503 IF(l_Count = 0) THEN
504 --dbms_output.put_line(' calling create tab_access for resp id :-' || l_Bsc_Tab_Entity_Rec.Bsc_Responsibility_Id);
505 BSC_SCORECARD_PVT.Create_Tab_Access
506 ( p_commit
507 , l_Bsc_Tab_Entity_Rec
508 , x_return_status
509 , x_msg_count
510 , x_msg_data
511 );
512 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
514 END IF;
515 END IF;
516
517 END LOOP;
518
519 --dbms_output.put_line(' end loop:-' );
520
521 EXCEPTION
522 WHEN FND_API.G_EXC_ERROR THEN
523 IF (x_msg_data IS NULL) THEN
524 FND_MSG_PUB.Count_And_Get
525 ( p_encoded => FND_API.G_FALSE
526 , p_count => x_msg_count
527 , p_data => x_msg_data
528 );
529 END IF;
530 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
531 x_return_status := FND_API.G_RET_STS_ERROR;
532 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
533 IF (x_msg_data IS NULL) THEN
534 FND_MSG_PUB.Count_And_Get
535 ( p_encoded => FND_API.G_FALSE
536 , p_count => x_msg_count
537 , p_data => x_msg_data
538 );
539 END IF;
540 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
541 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
542 WHEN NO_DATA_FOUND THEN
543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
544 IF (x_msg_data IS NOT NULL) THEN
545 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Create_Tab_Access_For_Resp ';
546 ELSE
547 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Create_Tab_Access_For_Resp ';
548 END IF;
549 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
550 WHEN OTHERS THEN
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
552 IF (x_msg_data IS NOT NULL) THEN
553 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Create_Tab_Access_For_Resp ';
554 ELSE
555 x_msg_data := SQLERRM||' AT BSC_SCORECARD_PUB.Create_Tab_Access_For_Resp ';
556 END IF;
557 END Create_Tab_Access_For_Resp;
558
559
560 /************************************************************************************
561 ************************************************************************************/
562
563 PROCEDURE Create_Tab_Access(
564 p_commit IN VARCHAR2 := FND_API.G_FALSE
565 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
566 ,x_return_status OUT NOCOPY VARCHAR2
567 ,x_msg_count OUT NOCOPY NUMBER
568 ,x_msg_data OUT NOCOPY VARCHAR2
569 ) IS
570
571 l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
572 l_Responsibility_Key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
573 l_Comma_Sep_Resp_Key VARCHAR2(32000):= NULL;
574 l_Tab_Short_Name BSC_TABS_B.SHORT_NAME%TYPE := NULL;
575
576 BEGIN
577 FND_MSG_PUB.Initialize;
578 x_return_status := FND_API.G_RET_STS_SUCCESS;
579 l_Bsc_Tab_Entity_Rec := p_Bsc_Tab_Entity_Rec;
580
581 BEGIN
582 SELECT T.SHORT_NAME
583 INTO l_Tab_Short_Name
584 FROM BSC_TABS_B T
585 WHERE T.TAB_ID = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
586 EXCEPTION
587 WHEN NO_DATA_FOUND THEN
588 l_Tab_Short_Name := NULL;
589 END;
590
591 -- added for Bug#4563456
592 l_Responsibility_Key := BSC_UTILITY.Get_Responsibility_Key;
593
594 IF UPPER(l_Responsibility_Key) = 'BSC_DESIGNER' OR upper(l_Responsibility_Key) = 'BSC_MANAGER' OR l_Tab_Short_Name IS NOT NULL THEN
595 l_Comma_Sep_Resp_Key := l_Comma_Sep_Resp_Key ||','||
596 bsc_utility.c_BSC_Manager ||','||
597 bsc_utility.c_BSC_DESIGNER ||','||
598 bsc_utility.c_BSC_PMD_USER ||','||
599 bsc_utility.c_BIS_BID_RESP ||','||
600 bsc_utility.c_BIS_DBI_ADMIN||','||
601 l_Responsibility_Key;
602 ELSE
603 l_Comma_Sep_Resp_Key := l_Responsibility_Key;
604 END IF;
605
606 Create_Tab_Access_For_Resp
607 ( p_Resposibility_Key => l_Comma_Sep_Resp_Key
608 , p_commit => p_commit
609 , p_Bsc_Tab_Entity_Rec => l_Bsc_Tab_Entity_Rec
610 , x_return_status => x_return_status
611 , x_msg_count => x_msg_count
612 , x_msg_data => x_msg_data
613 );
614 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
615 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
616 END IF;
617
618 EXCEPTION
619 WHEN FND_API.G_EXC_ERROR THEN
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 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
630 IF (x_msg_data IS NULL) THEN
631 FND_MSG_PUB.Count_And_Get
632 ( p_encoded => FND_API.G_FALSE
633 , p_count => x_msg_count
634 , p_data => x_msg_data
635 );
636 END IF;
637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
638 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
639 WHEN NO_DATA_FOUND THEN
640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
641 IF (x_msg_data IS NOT NULL) THEN
642 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Create_Tab_Access ';
643 ELSE
644 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Create_Tab_Access ';
645 END IF;
646 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
647 WHEN OTHERS THEN
648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
649 IF (x_msg_data IS NOT NULL) THEN
650 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Create_Tab_Access ';
651 ELSE
652 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Create_Tab_Access ';
653 END IF;
654 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
655
656 end Create_Tab_Access;
657
658 /************************************************************************************
659 ************************************************************************************/
660
661 procedure Create_Tab_Grants(
662 p_commit IN varchar2 := FND_API.G_FALSE
663 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
664 ,x_return_status OUT NOCOPY varchar2
665 ,x_msg_count OUT NOCOPY number
666 ,x_msg_data OUT NOCOPY varchar2
667 ) is
668
669 l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
670
671 l_responsibility_key varchar2(30);
672
673 begin
674 FND_MSG_PUB.Initialize;
675 x_return_status := FND_API.G_RET_STS_SUCCESS;
676 -- set local record equal to the one passed.
677 l_Bsc_Tab_Entity_Rec := p_Bsc_Tab_Entity_Rec;
678
679 -- Insert the record for the current responsibility.
680 BSC_SCORECARD_PVT.Create_Tab_Grants( p_commit
681 ,l_Bsc_Tab_Entity_Rec
682 ,x_return_status
683 ,x_msg_count
684 ,x_msg_data);
685
686 EXCEPTION
687 WHEN FND_API.G_EXC_ERROR THEN
688 IF (x_msg_data IS NULL) THEN
689 FND_MSG_PUB.Count_And_Get
690 ( p_encoded => FND_API.G_FALSE
691 , p_count => x_msg_count
692 , p_data => x_msg_data
693 );
694 END IF;
695 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
696 x_return_status := FND_API.G_RET_STS_ERROR;
697 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
698 IF (x_msg_data IS NULL) THEN
699 FND_MSG_PUB.Count_And_Get
700 ( p_encoded => FND_API.G_FALSE
701 , p_count => x_msg_count
702 , p_data => x_msg_data
703 );
704 END IF;
705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
706 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
707 WHEN NO_DATA_FOUND THEN
708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
709 IF (x_msg_data IS NOT NULL) THEN
710 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Create_Tab_Grants ';
711 ELSE
712 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Create_Tab_Grants ';
713 END IF;
714 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
715 WHEN OTHERS THEN
716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
717 IF (x_msg_data IS NOT NULL) THEN
718 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Create_Tab_Grants ';
719 ELSE
720 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Create_Tab_Grants ';
721 END IF;
722 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
723
724 end Create_Tab_Grants;
725
726 /************************************************************************************
727 ************************************************************************************/
728
729 procedure Update_System_Time_Stamp(
730 p_commit IN varchar2 := FND_API.G_FALSE
731 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
732 ,x_return_status OUT NOCOPY varchar2
733 ,x_msg_count OUT NOCOPY number
734 ,x_msg_data OUT NOCOPY varchar2
735 ) is
736
737 begin
738 FND_MSG_PUB.Initialize;
739 x_return_status := FND_API.G_RET_STS_SUCCESS;
740 BSC_SCORECARD_PVT.Update_System_Time_Stamp( p_commit
741 ,p_Bsc_Tab_Entity_Rec
742 ,x_return_status
743 ,x_msg_count
744 ,x_msg_data);
745
746 EXCEPTION
747 WHEN FND_API.G_EXC_ERROR THEN
748 IF (x_msg_data IS NULL) THEN
749 FND_MSG_PUB.Count_And_Get
750 ( p_encoded => FND_API.G_FALSE
751 , p_count => x_msg_count
752 , p_data => x_msg_data
753 );
754 END IF;
755 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
756 x_return_status := FND_API.G_RET_STS_ERROR;
757 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
758 IF (x_msg_data IS NULL) THEN
759 FND_MSG_PUB.Count_And_Get
760 ( p_encoded => FND_API.G_FALSE
761 , p_count => x_msg_count
762 , p_data => x_msg_data
763 );
764 END IF;
765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
766 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
767 WHEN NO_DATA_FOUND THEN
768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
769 IF (x_msg_data IS NOT NULL) THEN
770 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Update_System_Time_Stamp ';
771 ELSE
772 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Update_System_Time_Stamp ';
773 END IF;
774 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
775 WHEN OTHERS THEN
776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
777 IF (x_msg_data IS NOT NULL) THEN
778 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Update_System_Time_Stamp ';
779 ELSE
780 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Update_System_Time_Stamp ';
781 END IF;
782 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
783
784 end Update_System_Time_Stamp;
785
786 /************************************************************************************
787 ************************************************************************************/
788
789 procedure Update_Tab_Time_Stamp(
790 p_commit IN varchar2 := FND_API.G_FALSE
791 ,p_Bsc_Tab_Entity_Rec IN BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
792 ,x_return_status OUT NOCOPY varchar2
793 ,x_msg_count OUT NOCOPY number
794 ,x_msg_data OUT NOCOPY varchar2
795 ) is
796
797 begin
798 FND_MSG_PUB.Initialize;
799 x_return_status := FND_API.G_RET_STS_SUCCESS;
800 BSC_SCORECARD_PVT.Update_Tab_Time_Stamp( p_commit
801 ,p_Bsc_Tab_Entity_Rec
802 ,x_return_status
803 ,x_msg_count
804 ,x_msg_data);
805
806 EXCEPTION
807 WHEN FND_API.G_EXC_ERROR THEN
808 IF (x_msg_data IS NULL) THEN
809 FND_MSG_PUB.Count_And_Get
810 ( p_encoded => FND_API.G_FALSE
811 , p_count => x_msg_count
812 , p_data => x_msg_data
813 );
814 END IF;
815 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
816 x_return_status := FND_API.G_RET_STS_ERROR;
817 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
818 IF (x_msg_data IS NULL) THEN
819 FND_MSG_PUB.Count_And_Get
820 ( p_encoded => FND_API.G_FALSE
821 , p_count => x_msg_count
822 , p_data => x_msg_data
823 );
824 END IF;
825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
826 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
827 WHEN NO_DATA_FOUND THEN
828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
829 IF (x_msg_data IS NOT NULL) THEN
830 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Update_Tab_Time_Stamp ';
831 ELSE
832 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Update_Tab_Time_Stamp ';
833 END IF;
834 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
835 WHEN OTHERS THEN
836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
837 IF (x_msg_data IS NOT NULL) THEN
838 x_msg_data := x_msg_data||' -> BSC_SCORECARD_PUB.Update_Tab_Time_Stamp ';
839 ELSE
840 x_msg_data := SQLERRM||' at BSC_SCORECARD_PUB.Update_Tab_Time_Stamp ';
841 END IF;
842 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
843
844 end Update_Tab_Time_Stamp;
845
846 /************************************************************************************ 392
847 ************************************************************************************/
848
849 function is_child_tab_of(
850 p_child_tab_id IN number
851 ,p_parent_tab_id IN number
852 ) return varchar2 is
853
854 l_tab_parent number;
855 l_tab_child number;
856 l_return_value varchar2(10);
857
858 begin
859
860 --DBMS_OUTPUT.PUT_LINE('Begin is_child_tab_of' );
861 --DBMS_OUTPUT.PUT_LINE(' is_child_tab_of p_child_tab_id = ' || p_child_tab_id );
862 --DBMS_OUTPUT.PUT_LINE(' is_child_tab_of p_parent_tab_id = ' || p_parent_tab_id );
863 l_return_value := FND_API.G_FALSE;
864
865 l_tab_child := p_child_tab_id;
866 while l_tab_child is not null loop
867 select PARENT_TAB_ID into l_tab_parent
868 from BSC_TABS_VL where TAB_ID = l_tab_child;
869 --DBMS_OUTPUT.PUT_LINE(' is_child_tab_of l_tab_parent = ' || l_tab_parent );
870
871 if l_tab_parent is not null then
872 if l_tab_parent = p_parent_tab_id then
873 l_return_value := FND_API.G_TRUE;
874 exit;
875 end if;
876 end if;
877 l_tab_child := l_tab_parent;
878 end loop;
879
880 --DBMS_OUTPUT.PUT_LINE('is_child_tab_of x_return_status = ' || x_return_status );
881 --DBMS_OUTPUT.PUT_LINE('End is_child_tab_of' );
882
883 return l_return_value;
884
885 EXCEPTION
886 WHEN OTHERS THEN
887 return l_return_value;
888
889 end is_child_tab_of;
890 /**************************************************************************
891 Check_Tab_UserAccess_Func_Only checks the accessibility of the user
892 for the function passed, without taking into account the user responsibility
893 **************************************************************************/
894
895 FUNCTION Check_Tab_UserAccess_Func_Only(
896 p_tab_id IN NUMBER
897 ,p_user_name IN VARCHAR2
898 ,p_user_access IN VARCHAR2
899 )return VARCHAR2 IS
900
901 l_isaccess boolean ;
902 l_function_id NUMBER;
903 l_count NUMBER;
904 l_user_name VARCHAR2(256);
905
906 BEGIN
907
908 IF(p_user_name IS NULL) THEN
909 l_user_name := FND_GLOBAL.USER_NAME;
910 ELSE
911 l_user_name := p_user_name;
912 END IF;
913
914 SELECT function_id INTO l_function_id
915 FROM fnd_form_functions
916 WHERE function_name= p_user_access;
917
918 SELECT COUNT(GNT.grant_guid)
919 INTO l_count
920 FROM FND_GRANTS GNT,
921 FND_OBJECTS b,
922 FND_MENUS m,
923 FND_MENU_ENTRIES e
924 WHERE GNT.PROGRAM_NAME = 'BSC_PMD_GRANTS'
925 AND b.OBJ_NAME = 'BSC_TAB'
926 AND GNT.OBJECT_ID = b.OBJECT_ID
927 AND GNT.INSTANCE_PK1_VALUE = to_char(p_tab_id)
928 AND ( GNT.GRANTEE_TYPE = 'USER' AND GNT.GRANTEE_KEY = UPPER(l_user_name))
929 AND GNT.START_DATE <= sysdate
930 AND (GNT.END_DATE IS NULL OR GNT.END_DATE >= sysdate )
931 AND m.MENU_ID = GNT.MENU_ID
932 AND e.MENU_ID = m.MENU_ID
933 AND e.function_id = l_function_id;
934
935 IF(l_count = 0) THEN
936 RETURN 'N';
937 ELSE
938 RETURN 'Y';
939 END IF;
940 END Check_Tab_UserAccess_Func_Only;
941
942
943 /************************************************************************************
944 Function : CheckTabViewAccess
945 Description : This fucntion will validate if particular user has BSC_SCORECARD_VIEWER
946 access.If yes then it return 'Y'.It means the user can only view
947 the scorecards and its contents. He cannot do any upadte/delete
948 operations.
949 Input parameters : p_tab_id,p_user_name
950 output : 'Y' indicating user has View Acces otherwsie not.He comes under
951 designer and administrator access. so he can do update , view and delete
952 operations
953 ***********************************************************************************/
954 FUNCTION Check_Tab_UserAccess(
955 p_tab_id IN NUMBER
956 ,p_user_name IN VARCHAR2
957 ,p_user_access IN VARCHAR2
958 )return VARCHAR2 IS
959
960 l_isaccess boolean ;
961 l_function_id NUMBER;
962 l_count NUMBER;
963 l_user_name VARCHAR2(256);
964 l_resp_id NUMBER;
965 l_resp_count NUMBER:=0;
966
967 BEGIN
968 -- Default the user name if not passed in.
969 l_resp_id:= FND_GLOBAL.RESP_ID;
970 IF(p_user_name IS NULL) THEN
971 l_user_name := FND_GLOBAL.USER_NAME;
972 ELSE
973 l_user_name := p_user_name;
974 END IF;
975
976 SELECT function_id INTO l_function_id
977 FROM fnd_form_functions
978 WHERE function_name= p_user_access;
979
980 SELECT COUNT(GNT.grant_guid)
981 INTO l_count
982 FROM FND_GRANTS GNT,
983 FND_OBJECTS b,
984 FND_MENUS m,
985 FND_MENU_ENTRIES e
986 WHERE GNT.PROGRAM_NAME = 'BSC_PMD_GRANTS'
987 AND b.OBJ_NAME = 'BSC_TAB'
988 AND GNT.OBJECT_ID = b.OBJECT_ID
989 AND GNT.INSTANCE_PK1_VALUE = to_char(p_tab_id)
990 AND ( GNT.GRANTEE_TYPE = 'USER' AND GNT.GRANTEE_KEY = UPPER(l_user_name))
991 AND GNT.START_DATE <= sysdate
992 AND (GNT.END_DATE IS NULL OR GNT.END_DATE >= sysdate )
993 AND m.MENU_ID = GNT.MENU_ID
994 AND e.MENU_ID = m.MENU_ID
995 AND e.function_id = l_function_id;
996
997 SELECT COUNT(1)
998 INTO l_resp_count
999 FROM bsc_user_tab_access
1000 WHERE tab_id = p_tab_id
1001 AND responsibility_id =l_resp_id
1002 AND (SYSDATE BETWEEN NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE));
1003 --Scorecard security is both at user , responsibility levels
1004 -- So while deciding to show up scorecard or not we need to consider responsibility also
1005 IF(l_count = 0 OR l_resp_count = 0) THEN
1006 RETURN 'N';
1007 ELSE
1008 RETURN 'Y';
1009 END IF;
1010 END Check_Tab_UserAccess;
1011
1012 /***********************************************************************
1013 Validate_Scorecard_Access ensures that if a scorecard is added in the
1014 access list for a particular user, then all its parent scorecards have
1015 atleast 'User' access for that scorecard.
1016
1017 It returns comma separated list of names for tabs which need to be added
1018 in the user's access list.
1019 ************************************************************************/
1020 PROCEDURE Validate_Scorecard_Access (
1021 p_tab_id IN NUMBER
1022 ,p_user_name IN VARCHAR2
1023 ,x_par_tab_name OUT NOCOPY VARCHAR2
1024 ,x_par_tabname_list OUT NOCOPY VARCHAR2
1025 ) IS
1026 l_parent_tab_id bsc_tabs_vl.tab_id%TYPE;
1027 l_parent_tab_id1 bsc_tabs_vl.tab_id%TYPE;
1028 l_par_tabname_list VARCHAR2(4000);
1029 l_name bsc_tabs_vl.name%TYPE;
1030 BEGIN
1031
1032 SELECT parent_tab_id,name
1033 INTO l_parent_tab_id, x_par_tab_name
1034 FROM bsc_tabs_vl
1035 WHERE tab_id = p_tab_id;
1036
1037 WHILE (l_parent_tab_id IS NOT NULL) LOOP
1038 IF(Check_Tab_UserAccess_Func_Only(l_parent_tab_id, p_user_name, 'BSC_SCORECARD_ACCESS_VIEW' ) = 'N') THEN
1039
1040 SELECT name
1041 INTO l_name
1042 FROM BSC_TABS_VL
1043 WHERE tab_id = l_parent_tab_id;
1044
1045 IF (l_par_tabname_list IS NULL) THEN
1046 l_par_tabname_list := l_name;
1047
1048 ELSE
1049 l_par_tabname_list := l_par_tabname_list ||', '|| l_name;
1050
1051 END IF;
1052 END IF;
1053
1054 l_parent_tab_id1 := l_parent_tab_id;
1055
1056 SELECT parent_tab_id
1057 INTO l_parent_tab_id
1058 FROM bsc_tabs_b
1059 WHERE tab_id = l_parent_tab_id1;
1060
1061 END LOOP;
1062
1063 x_par_tabname_list := l_par_tabname_list;
1064
1065 END Validate_Scorecard_Access;
1066
1067
1068 /***********************************************************************
1069 Chk_Child_Scd_Has_Access returns list of all children of current
1070 scorecard, for which the current user has access
1071 ************************************************************************/
1072
1073 FUNCTION Chk_Child_Scd_Has_Access(
1074 p_tab_id IN NUMBER
1075 ,p_user IN VARCHAR2
1076 ) RETURN VARCHAR2 IS
1077
1078 CURSOR c_chid_scorecards IS
1079 SELECT tab_id,name
1080 FROM bsc_tabs_vl
1081 WHERE parent_tab_id = p_tab_id;
1082
1083 l_tablist_name VARCHAR2(4000);
1084 l_return_tablist VARCHAR2(4000);
1085
1086 BEGIN
1087
1088 FOR cd IN c_chid_scorecards LOOP
1089
1090 IF (BSC_SCORECARD_PUB.Check_Tab_UserAccess_Func_Only(cd.tab_id,p_user,'BSC_SCORECARD_ACCESS_VIEW') ='Y') THEN
1091
1092 IF (l_tablist_name IS NULL) THEN
1093 l_tablist_name := cd.name ;
1094 ELSE
1095 l_tablist_name := l_tablist_name ||', '||cd.name;
1096 END IF;
1097 END IF;
1098
1099 l_return_tablist := Chk_Child_Scd_Has_Access(cd.tab_id,p_user);
1100
1101 IF (l_return_tablist IS NOT NULL) THEN
1102
1103 IF (l_tablist_name IS NULL) THEN
1104 l_tablist_name := l_return_tablist ;
1105 ELSE
1106 l_tablist_name := l_tablist_name ||', '||l_return_tablist;
1107 END IF;
1108 END IF;
1109 END LOOP;
1110
1111 RETURN l_tablist_name;
1112
1113 END Chk_Child_Scd_Has_Access;
1114
1115 /********************************************************************
1116 Validate_Scorecard_Revoke() ensures that if a scorecard access has to
1117 be revoked, then there is no other scorecard which is a child of the
1118 current scorecard for which the user still has access
1119 *******************************************************************/
1120
1121 PROCEDURE Validate_Scorecard_Revoke (
1122 p_grant_guids IN VARCHAR2
1123 ,x_chd_tabname_list OUT NOCOPY VARCHAR2
1124 ) IS
1125
1126 l_grant_guids VARCHAR2(32000);
1127 l_grantee_key FND_GRANTS.GRANTEE_KEY%TYPE;
1128 l_tab_id FND_GRANTS.INSTANCE_PK1_VALUE%TYPE;
1129 l_single_grant_guid FND_GRANTS.GRANT_GUID%TYPE;
1130 l_tablist_name VARCHAR2(4000);
1131 l_check_children VARCHAR2(4000);
1132 l_check_child BSC_TABS_VL.NAME%TYPE;
1133
1134 BEGIN
1135 l_grant_guids := p_grant_guids;
1136
1137 WHILE (BSC_SCORECARD_PVT.Is_More( p_grant_uids => l_grant_guids
1138 , p_grant_uid => l_single_grant_guid)) LOOP
1139
1140 SELECT grantee_key,instance_pk1_value
1141 INTO l_grantee_key, l_tab_id
1142 FROM fnd_grants
1143 WHERE grant_guid = l_single_grant_guid;
1144
1145 l_check_children := Chk_Child_Scd_Has_Access(l_tab_id,l_grantee_key);
1146
1147 IF (l_tablist_name IS NULL AND l_check_children IS NOT NULL) THEN
1148 l_tablist_name := l_check_children;
1149
1150 ELSIF (l_check_children IS NOT NULL) THEN
1151 WHILE (BSC_SCORECARD_PVT.Is_More( p_grant_uids => l_check_children
1152 , p_grant_uid => l_check_child)) LOOP
1153 IF(INSTR(', '||l_tablist_name||', ',', '||l_check_child||', ') = 0) THEN
1154 l_tablist_name := l_tablist_name ||', '||l_check_child;
1155
1156 END IF;
1157 END LOOP;
1158 END IF;
1159 END LOOP;
1160
1161 x_chd_tabname_list := l_tablist_name;
1162
1163 END Validate_Scorecard_Revoke;
1164 /*********************************************************************
1165 Function : is_Tab_Ordering_Enabled
1166 Description : This function will check if ordering of the scorecards
1167 is enabled or not
1168
1169 1.Check if the p_tab_id is null.if yes then it means that it is called from
1170 the root VO of the Hgrid page.
1171 If null then do verify if any of the parent_tabs are having update access.
1172 if no then return false whcih will disbale the re-ordering button
1173 on the top of the VO.
1174
1175 2.IF not null then check if it having the child or not.
1176 if not then return empty it means re-ordering is not to be shown.
1177 3. if it is the parent then verify if all the childs are having the update access.
1178 if all the childs are having the update access then only the reordering button should be enabled.
1179 else it will be disabled.
1180 /********************************************************************/
1181
1182
1183 FUNCTION is_Tab_Ordering_Enabled(
1184 p_tab_id IN NUMBER
1185 ,p_user_name IN VARCHAR2
1186 )RETURN VARCHAR2 IS
1187
1188 CURSOR c_root_tab_ids IS
1189 SELECT tab_id
1190 FROM BSC_TABS_VL
1191 WHERE PARENT_TAB_ID IS NULL;
1192
1193
1194
1195 CURSOR c_child_tab_ids IS
1196 SELECT tab_id
1197 FROM BSC_TABS_VL
1198 WHERE PARENT_TAB_ID = p_tab_id;
1199
1200 l_istaborderEnabled VARCHAR2(3);
1201
1202 BEGIN
1203 l_istaborderEnabled := 'N';
1204
1205 IF(p_tab_id IS NULL) THEN
1206 FOR root_tabs IN c_root_tab_ids LOOP
1207 l_istaborderEnabled := BSC_SCORECARD_PUB.Check_Tab_UserAccess
1208 (
1209 p_tab_id => root_tabs.tab_id
1210 ,p_user_name => p_user_name
1211 ,p_user_access => 'BSC_SCORECARD_ACCESS_UPDATE'
1212 );
1213 EXIT WHEN (l_istaborderEnabled<>'Y');
1214 END LOOP;
1215 ELSE
1216 FOR child_tabs IN c_child_tab_ids LOOP
1217 l_istaborderEnabled := BSC_SCORECARD_PUB.Check_Tab_UserAccess
1218 (
1219 p_tab_id => child_tabs.tab_id
1220 ,p_user_name => p_user_name
1221 ,p_user_access => 'BSC_SCORECARD_ACCESS_UPDATE'
1222 );
1223 EXIT WHEN (l_istaborderEnabled<>'Y');
1224 END LOOP;
1225 END IF;
1226
1227 RETURN l_istaborderEnabled;
1228
1229 END is_Tab_Ordering_Enabled;
1230
1231
1232 end BSC_SCORECARD_PUB;
[Home] [Help]