1 package body bsc_sec_utility AS
2 /*$Header: BSCSECUB.pls 120.0 2005/06/01 14:30:55 appldev noship $*/
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.7=120.0):~PROD:~PATH:~FILE
5
6
7 G_PKG_NAME VARCHAR2(30) := 'BSC_SEC_UTILITY';
8 g_current_user_id NUMBER := FND_GLOBAL.User_id;
9 g_current_login_id NUMBER := FND_GLOBAL.Login_id;
10
11 function get_item_value(p_level_view_name varchar2,p_level_value varchar2) return varchar2 is
12 l_itemvalue varchar2(30);
13 l_sql varchar2(2000);
14 l_dummy varchar2(1);
15 cursor c_view_exist is
16 select '1'
17 from dual
18 where exists
19 (select '1'
20 from user_objects
21 where object_name=p_level_view_name
22 and object_type='VIEW');
23
24 begin
25 open c_view_exist;
26 fetch c_view_exist into l_dummy;
27
28 if c_view_exist%notfound then
29 return 'No level view';
30 else
31
32 l_sql:='SELECT name FROM '||p_level_view_name||' WHERE code = :1';
33 EXECUTE IMMEDIATE l_sql INTO l_itemvalue USING p_level_value;
34
35 return l_itemvalue;
36 end if;
37 close c_view_exist;
38 exception
39 when no_data_found then
40 return p_level_value||'(item value not found)';
41 when others then
42 raise;
43 end;
44
45 /**
46 the logic for this function is changed per bug 3172873
47 **/
48 function get_lowest_dim_ind(p_tab_id varchar2,p_resp_id varchar2) return number is
49 l_dim_ind number;
50
51 cursor c_dim_values is
52 select
53 DIM_LEVEL_INDEX,
54 DIM_LEVEL_VALUE
55 from bsc_user_list_access
56 where RESPONSIBILITY_ID=p_resp_id
57 and TAB_ID=p_tab_id
58 order by DIM_LEVEL_INDEX desc;
59
60 l_dim_values_rec c_dim_values%rowtype;
61
62 begin
63 l_dim_ind:=null;
64 for l_dim_values_rec in c_dim_values loop
65 if l_dim_values_rec.DIM_LEVEL_VALUE<>to_char(0) then
66 l_dim_ind:=l_dim_values_rec.DIM_LEVEL_INDEX;
67 exit;
68 end if;
69 end loop;
70 if l_dim_ind is null then
71 l_dim_ind:=0;
72 end if;
73
74 /** select
75 max(DIM_LEVEL_INDEX)
76 into
77 l_dim_ind
78 from
79 bsc_sys_com_dim_levels
80 where tab_id=p_tab_id;
81 **/
82 return l_dim_ind;
83 exception
84 when no_data_found then
85 return null;
86 when others then
87 raise;
88
89 end;
90
91 function get_parent_value(p_tab_id number,p_level_index number,p_level_value varchar2) return varchar2
92 is
93 cursor c_level_view is
94 select
95 b.LEVEL_VIEW_NAME
96 from
97 bsc_sys_com_dim_levels a,
98 bsc_sys_dim_levels_vl b
99 where
100 a.tab_id=p_tab_id
101 and a.DIM_LEVEL_INDEX=p_level_index
102 and a.DIM_LEVEL_ID=b.dim_level_id;
103
104 cursor c_parent_pk is
105 select
106 b.LEVEL_PK_COL
107 from
108 bsc_sys_com_dim_levels a,
109 bsc_sys_dim_levels_vl b
110 where a.tab_id=p_tab_id
111 and a.dim_level_index=p_level_index
112 and a.PARENT_DIM_LEVEL_ID=b.dim_level_id;
113
114 l_level_view varchar2(30);
115 l_parent_pk varchar2(30);
116 l_parent_value varchar2(400);
117 l_sql varchar2(2000);
118 begin
119 if p_level_index=0 then
120 return p_level_value;
121 else
122 open c_level_view;
123 fetch c_level_view into l_level_view;
124 close c_level_view;
125 open c_parent_pk;
126 fetch c_parent_pk into l_parent_pk ;
127 close c_parent_pk;
128
129 l_sql:='SELECT to_char('||l_parent_pk||') FROM '||l_level_view||' WHERE code= :1';
130 EXECUTE IMMEDIATE l_sql INTO l_parent_value USING p_level_value;
131 return l_parent_value;
132 end if;
133 exception
134 when others then
135 raise;
136 end;
137
138
139 procedure Update_tab_access (
140 P_ROWID in ROWID := null,
141 P_RESP_ID in number,
142 P_TAB_ID in number,
143 P_START_DATE in date,
144 P_END_DATE in date,
145 P_LAST_UPDATED_BY in NUMBER := null,
146 P_LAST_UPDATE_LOGIN in NUMBER := null,
147 P_LAST_UPDATE_DATE in DATE := null,
148 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
149 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
150 x_return_status OUT NOCOPY VARCHAR2,
151 x_errorcode OUT NOCOPY NUMBER,
152 x_msg_count OUT NOCOPY NUMBER,
153 x_msg_data OUT NOCOPY VARCHAR2
154 ) is
155
156 l_sysdate DATE := sysdate;
157 l_api_name CONSTANT VARCHAR2(30) := 'Update_tab_access';
158 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
159 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
160 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
161
162
163 cursor tab_indicators is
164 select
165 indicator
166 from
167 bsc_tab_indicators
168 where tab_id=p_tab_id;
169
170 l_indicators_rec tab_indicators%rowtype;
171
172 begin
173
174 -- Initialize API message list if necessary.
175 -- Initialize message list if p_init_msg_list is set to TRUE.
176 IF FND_API.to_Boolean( p_init_msg_list ) THEN
177 FND_MSG_PUB.initialize;
178 END IF;
179
180 if (P_ROWID is not null) then
181 update bsc_user_tab_access
182 set responsibility_id = P_RESP_ID,
183 tab_id = P_TAB_ID,
184 start_date = P_START_DATE,
185 end_date = P_END_DATE,
186 last_updated_by = l_last_updated_by,
187 last_update_login = l_last_update_login,
188 last_update_date = l_last_update_date
189 where rowid = P_ROWID;
190 else
191 UPDATE bsc_user_tab_access
192 SET
193 start_date = P_START_DATE,
194 end_date = P_END_DATE,
195 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
196 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
197 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
198 WHERE responsibility_id = P_RESP_ID
199 AND tab_id = P_TAB_ID ;
200 END IF;
201
202
203 -----if p_end_date is not null, and p_end_date<=sysdate, we need to
204 ----- remove the indicators of the tab from bsc_user_kpi_access
205 -----if p_end_date is null or p_end_date >sysdate need to reassign indicators to the resp
206 if ((p_end_date is not null and p_end_date <=sysdate)
207 or (p_start_date is not null and p_start_date >sysdate)) then
208 for l_indicators_rec in tab_indicators loop
209 remove_kpi_access(
210 p_resp_id =>p_resp_id,
211 p_indicator =>l_indicators_rec.indicator,
212 x_return_status => x_return_status ,
213 x_errorcode => x_errorcode ,
214 x_msg_count => x_msg_count ,
215 x_msg_data => x_msg_data ) ;
216 end loop;
217 end if;
218 commit;
219 if ( p_start_date<=sysdate and (p_end_date is null or nvl(p_end_date,sysdate)>sysdate )) then
220 for l_indicators_rec in tab_indicators loop
221 insert_kpi_access (
222 P_RESP_ID =>p_resp_id,
223 P_INDICATOR =>l_indicators_rec.indicator,
224 P_START_DATE =>p_start_date,
225 P_END_DATE =>p_end_date,
226 x_return_status =>x_return_status,
227 x_errorcode => x_errorcode,
228 x_msg_count => x_msg_count,
229 x_msg_data => x_msg_data
230 ) ;
231 end loop;
232 end if;
233
234 x_return_status := FND_API.G_RET_STS_SUCCESS;
235
236 -----------------------------------
237 -- Make a standard call to get message count
238 -- and if count is 1, get message info.
239 -- The client will directly display the x_msg_data (which is already
240 -- translated) if the x_msg_count = 1;
241 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
242 -- Server-side procedure to access the messages, and consolidate them
243 -- and display them all at once or display one message after another.
244
245 FND_MSG_PUB.Count_And_Get
246 ( p_count => x_msg_count,
247 p_data => x_msg_data
248 );
249
250 exception
251 when no_data_found then
252 return;
253 when others then
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255 IF FND_MSG_PUB.Check_Msg_Level
256 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
257 THEN
258 FND_MSG_PUB.Add_Exc_Msg
259 ( G_PKG_NAME,
260 l_api_name
261 );
262 END IF;
263 FND_MSG_PUB.Count_And_Get
264 ( p_count => x_msg_count,
265 p_data => x_msg_data
266 );
267 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
268
269 end Update_tab_access;
270
271
272 procedure Update_list_access (
273 P_ROWID in ROWID := null,
274 P_RESP_ID in number,
275 P_TAB_ID in number,
276 P_DIM_LEVEL_INDEX in number,
277 P_DIM_LEVEL_VALUE in VARCHAR2,
278 P_LAST_UPDATED_BY in NUMBER := null,
279 P_LAST_UPDATE_LOGIN in NUMBER := null,
280 P_LAST_UPDATE_DATE in DATE := null,
281 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
282 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_errorcode OUT NOCOPY NUMBER,
285 x_msg_count OUT NOCOPY NUMBER,
286 x_msg_data OUT NOCOPY VARCHAR2
287 ) is
288
289 l_sysdate DATE := sysdate;
290 l_api_name CONSTANT VARCHAR2(30) := 'Update_list_access';
291 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
292 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
293 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
294 begin
295
296 -- Initialize API message list if necessary.
297 -- Initialize message list if p_init_msg_list is set to TRUE.
298 IF FND_API.to_Boolean( p_init_msg_list ) THEN
299 FND_MSG_PUB.initialize;
300 END IF;
301
302 if (P_ROWID is not null) then
303 update bsc_user_list_access
304 set responsibility_id = P_RESP_ID,
305 tab_id = P_TAB_ID,
306 DIM_LEVEL_INDEX = P_DIM_LEVEL_INDEX,
307 DIM_LEVEL_VALUE = P_DIM_LEVEL_VALUE,
308 last_updated_by = l_last_updated_by,
309 last_update_login = l_last_update_login,
310 last_update_date = l_last_update_date
311 where rowid = P_ROWID;
312 else
313 UPDATE bsc_user_list_access
314 SET
315 DIM_LEVEL_VALUE = P_DIM_LEVEL_VALUE,
316 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
317 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
318 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
319 WHERE responsibility_id = P_RESP_ID
320 AND tab_id = P_TAB_ID
321 and dim_level_index = P_DIM_LEVEL_INDEX ;
322 END IF;
323
324 x_return_status := FND_API.G_RET_STS_SUCCESS;
325
326 -----------------------------------
327 -- Make a standard call to get message count
328 -- and if count is 1, get message info.
329 -- The client will directly display the x_msg_data (which is already
330 -- translated) if the x_msg_count = 1;
331 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
332 -- Server-side procedure to access the messages, and consolidate them
333 -- and display them all at once or display one message after another.
334
335 FND_MSG_PUB.Count_And_Get
336 ( p_count => x_msg_count,
337 p_data => x_msg_data
338 );
339
340 exception
341 when no_data_found then
342 return;
343 when others then
344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
345 IF FND_MSG_PUB.Check_Msg_Level
346 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
347 THEN
348 FND_MSG_PUB.Add_Exc_Msg
349 ( G_PKG_NAME,
350 l_api_name
351 );
352 END IF;
353 FND_MSG_PUB.Count_And_Get
354 ( p_count => x_msg_count,
355 p_data => x_msg_data
356 );
357 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
358
359 end update_list_access;
360
361
362 procedure insert_list_access (
363 P_RESP_ID in number,
364 P_TAB_ID in number,
365 P_DIM_LEVEL_INDEX in number,
366 P_DIM_LEVEL_VALUE in VARCHAR2,
367 P_CREATION_DATE in date :=null,
368 p_CREATED_BY in number :=null,
369 P_LAST_UPDATED_BY in NUMBER := null,
370 P_LAST_UPDATE_LOGIN in NUMBER := null,
371 P_LAST_UPDATE_DATE in DATE := null,
372 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
373 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
374 x_return_status OUT NOCOPY VARCHAR2,
375 x_errorcode OUT NOCOPY NUMBER,
376 x_msg_count OUT NOCOPY NUMBER,
377 x_msg_data OUT NOCOPY VARCHAR2
378 ) is
379
380 l_sysdate DATE := sysdate;
381 l_api_name CONSTANT VARCHAR2(30) := 'Insert_list_access';
382 l_creation_date date :=nvl(p_creation_date,l_Sysdate);
383 l_created_by number :=nvl(p_created_by,g_current_user_id);
384 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
385 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
386 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
387 begin
388
389 -- Initialize API message list if necessary.
390 -- Initialize message list if p_init_msg_list is set to TRUE.
391 IF FND_API.to_Boolean( p_init_msg_list ) THEN
392 FND_MSG_PUB.initialize;
393 END IF;
394
395 insert into bsc_user_list_access
396 (
397 RESPONSIBILITY_ID ,
398 TAB_ID ,
399 DIM_LEVEL_INDEX ,
400 DIM_LEVEL_VALUE ,
401 CREATION_DATE ,
402 CREATED_BY ,
403 LAST_UPDATE_DATE ,
404 LAST_UPDATED_BY ,
405 LAST_UPDATE_LOGIN
406 )
407 values
408 (
409 P_RESP_ID ,
410 P_TAB_ID ,
411 P_DIM_LEVEL_INDEX ,
412 P_DIM_LEVEL_VALUE ,
413 l_creation_date,
414 l_created_by,
415 l_last_update_date,
416 l_last_updated_by,
417 l_last_update_login
418 );
419
420 x_return_status := FND_API.G_RET_STS_SUCCESS;
421
422 -----------------------------------
423 -- Make a standard call to get message count
424 -- and if count is 1, get message info.
425 -- The client will directly display the x_msg_data (which is already
426 -- translated) if the x_msg_count = 1;
427 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
428 -- Server-side procedure to access the messages, and consolidate them
432 ( p_count => x_msg_count,
429 -- and display them all at once or display one message after another.
430
431 FND_MSG_PUB.Count_And_Get
433 p_data => x_msg_data
434 );
435
436 exception
437 when no_data_found then
438 return;
439 when others then
440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
441 IF FND_MSG_PUB.Check_Msg_Level
442 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
443 THEN
444 FND_MSG_PUB.Add_Exc_Msg
445 ( G_PKG_NAME,
446 l_api_name
447 );
448 END IF;
449 FND_MSG_PUB.Count_And_Get
450 ( p_count => x_msg_count,
451 p_data => x_msg_data
452 );
453 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
454
455 end insert_list_access;
456
457
458
459 function has_access_level(p_tab_id in varchar2) return varchar2 is
460 cursor c_exist is
461 select 'Y'
462 from dual
463 where exists (select TAB_ID from bsc_sys_com_dim_levels where tab_id=p_tab_id);
464 l_dummy varchar2(1);
465 begin
466 l_dummy:=null;
467 open c_exist;
468 fetch c_exist into l_dummy;
469 if c_exist%notfound then
470 l_dummy:='N';
471 end if;
472 close c_exist;
473 return l_dummy;
474 exception
475 when others then
476 raise;
477 end;
478
479 function exist_user_list_access(p_resp_id in number,p_tab_id in number) return varchar2 is
480 cursor exist_user_list_access is
481 select 'Y'
482 from dual
483 where exists
484 (select 'Y' from bsc_user_list_access
485 where RESPONSIBILITY_ID=p_resp_id
486 and TAB_ID=p_tab_id);
487 l_dummy varchar2(1);
488 begin
489 l_dummy:=null;
490 open exist_user_list_access;
491 fetch exist_user_list_access into l_dummy;
492 if exist_user_list_access%notfound then
493 l_dummy:='N';
494 end if;
495 close exist_user_list_access;
496 return l_dummy;
497 exception
498 when others then
499 raise;
500 end;
501
502
503
504
505 -----This api will be called when the user check the check box at UI
506 -----We need to grant the access to this scorecard
507 --- Before insert, we need to check if this scorecard had been granted before
508 ----if so, we just need to reset the end date to null
509 procedure insert_tab_access (
510 P_RESP_ID in number,
511 P_TAB_ID in number,
512 P_START_DATE in date,
513 P_END_DATE in date,
514 P_CREATED_BY in NUMBER := null,
515 P_CREATION_DATE in DATE := null,
516 P_LAST_UPDATED_BY in NUMBER := null,
517 P_LAST_UPDATE_LOGIN in NUMBER := null,
518 P_LAST_UPDATE_DATE in DATE := null,
519 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
520 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
521 x_return_status OUT NOCOPY VARCHAR2,
522 x_errorcode OUT NOCOPY NUMBER,
523 x_msg_count OUT NOCOPY NUMBER,
524 x_msg_data OUT NOCOPY VARCHAR2
525 ) is
526
527 l_sysdate DATE := sysdate;
528 l_api_name CONSTANT VARCHAR2(30) := 'insert_tab_access';
529 l_created_by NUMBER := nvl(P_CREATED_BY,g_current_user_id);
530 l_creation_date DATE := nvl(P_CREATION_DATE, l_Sysdate);
531 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
532 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
533 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
534
535 cursor row_exists is
536 select 'Y'
537 from dual
538 where exists
539 (select 'Y'
540 from bsc_user_tab_access
541 where responsibility_id=p_resp_id
542 and tab_id=p_tab_id);
543
544 l_dummy varchar2(1);
545
546 cursor tab_indicators is
547 select
548 indicator
549 from
550 bsc_tab_indicators
551 where tab_id=p_tab_id;
552
553 l_indicators_rec tab_indicators%rowtype;
554
555 cursor comm_dim_value is
556 select distinct
557 DIM_LEVEL_INDEX DIM_LEVEL_INDEX,
558 '0' DIM_LEVEL_VALUE ---default value 'ALL'
559 from
560 bsc_sys_com_dim_levels
561 where tab_id=p_tab_id;
562
563 l_comm_dim_value_rec comm_dim_value%rowtype;
564
565
566 begin
567 l_dummy:=null;
568 -- Initialize API message list if necessary.
569 -- Initialize message list if p_init_msg_list is set to TRUE.
570 IF FND_API.to_Boolean( p_init_msg_list ) THEN
571 FND_MSG_PUB.initialize;
572 END IF;
573
574 open row_exists;
575 fetch row_exists into l_dummy;
576 if row_exists%notfound then
577 l_dummy:='N';
578 end if;
579 close row_exists;
580
581 if l_dummy='Y' then
582 ---update the end_date to null
583 ----the start date (sysdate) and end_date (null) values will be set at middle tier
584 Update_tab_access (
585 P_RESP_ID =>p_resp_id,
586 P_TAB_ID =>p_tab_id,
587 P_START_DATE =>p_start_date,
588 P_END_DATE =>p_end_date,
592 x_msg_data =>x_msg_data
589 x_return_status =>x_return_status,
590 x_errorcode =>x_errorcode,
591 x_msg_count =>x_msg_count,
593 ) ;
594
595 else
596 ---insert a new row
597 insert into bsc_user_tab_access (
598 responsibility_id,
599 tab_id,
600 start_date,
601 end_date,
602 CREATED_BY,
603 CREATION_DATE,
604 LAST_UPDATED_BY,
605 LAST_UPDATE_LOGIN,
606 LAST_UPDATE_DATE)
607 values(
608 P_resp_id,
609 P_tab_id,
610 P_start_date,
611 P_end_date,
612 L_CREATED_BY,
613 L_CREATION_DATE,
614 L_LAST_UPDATED_BY,
615 L_LAST_UPDATE_LOGIN,
616 L_LAST_UPDATE_DATE);
617
618 ----Need to assign all indicators in this tab to the responsibility
619 ---
620 for l_indicators_rec in tab_indicators loop
621 insert_kpi_access (
622 P_RESP_ID =>p_resp_id,
623 P_INDICATOR =>l_indicators_rec.indicator,
624 P_START_DATE =>p_start_date,
625 P_END_DATE =>p_end_date,
626 x_return_status =>x_return_status,
627 x_errorcode => x_errorcode,
628 x_msg_count => x_msg_count,
629 x_msg_data => x_msg_data
630 ) ;
631 end loop;
632
633 end if;
634
635 -----insert into bsc_user_List_access
636 if has_access_level(p_tab_id) ='Y' then
637 if exist_user_list_access(p_resp_id,p_tab_id)='N' then
638 for l_comm_dim_value_rec in comm_dim_value loop
639 insert_list_access (
640 P_RESP_ID=>p_resp_id,
641 P_TAB_ID=>p_tab_id ,
642 P_DIM_LEVEL_INDEX=>l_comm_dim_value_rec.DIM_LEVEL_INDEX,
643 P_DIM_LEVEL_VALUE=>l_comm_dim_value_rec.DIM_LEVEL_value,
644 x_return_status => x_return_status ,
645 x_errorcode => x_errorcode ,
646 x_msg_count => x_msg_count ,
647 x_msg_data => x_msg_data );
648 end loop;
649 end if;
650
651 end if;
652
653
654 x_return_status := FND_API.G_RET_STS_SUCCESS;
655
656 -----------------------------------
657 -- Make a standard call to get message count
658 -- and if count is 1, get message info.
659 -- The client will directly display the x_msg_data (which is already
660 -- translated) if the x_msg_count = 1;
661 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
662 -- Server-side procedure to access the messages, and consolidate them
663 -- and display them all at once or display one message after another.
664
665 FND_MSG_PUB.Count_And_Get
666 ( p_count => x_msg_count,
667 p_data => x_msg_data
668 );
669
670 exception
671 when no_data_found then
672 return;
673 when others then
674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675 IF FND_MSG_PUB.Check_Msg_Level
676 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
677 THEN
678 FND_MSG_PUB.Add_Exc_Msg
679 ( G_PKG_NAME,
680 l_api_name
681 );
682 END IF;
683 FND_MSG_PUB.Count_And_Get
684 ( p_count => x_msg_count,
685 p_data => x_msg_data
686 );
687 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
688
689
690 end insert_tab_access;
691
692
693 procedure insert_kpi_access (
694 P_RESP_ID in number,
695 P_INDICATOR in number,
696 P_START_DATE in date,
697 P_END_DATE in date,
698 P_CREATED_BY in NUMBER := null,
699 P_CREATION_DATE in DATE := null,
700 P_LAST_UPDATED_BY in NUMBER := null,
701 P_LAST_UPDATE_LOGIN in NUMBER := null,
702 P_LAST_UPDATE_DATE in DATE := null,
703 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
704 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
705 x_return_status OUT NOCOPY VARCHAR2,
706 x_errorcode OUT NOCOPY NUMBER,
707 x_msg_count OUT NOCOPY NUMBER,
708 x_msg_data OUT NOCOPY VARCHAR2
709 ) is
710
711 l_sysdate DATE := sysdate;
712 l_api_name CONSTANT VARCHAR2(30) := 'insert_kpi_access';
713 l_created_by NUMBER := nvl(P_CREATED_BY,g_current_user_id);
714 l_creation_date DATE := nvl(P_CREATION_DATE, l_Sysdate);
715 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
716 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
717 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
718
719 cursor row_exists is
720 select 'Y'
721 from dual
722 where exists
723 (select 'Y'
724 from bsc_user_kpi_access
725 where responsibility_id=p_resp_id
726 and indicator=p_indicator);
727
728 l_dummy varchar2(1);
729
730 begin
731 l_dummy:=null;
732 -- Initialize API message list if necessary.
733 -- Initialize message list if p_init_msg_list is set to TRUE.
734 IF FND_API.to_Boolean( p_init_msg_list ) THEN
735 FND_MSG_PUB.initialize;
736 END IF;
737
738 open row_exists;
739 fetch row_exists into l_dummy;
743 close row_exists;
740 if row_exists%notfound then
741 l_dummy:='N';
742 end if;
744
745 if l_dummy='Y' then
746 ----update dates for the existing row
747 Update_kpi_access (
748 P_RESP_ID =>p_resp_id,
749 P_INDICATOR =>p_indicator,
750 P_START_DATE =>p_start_date,
751 P_END_DATE =>p_end_date,
752 x_return_status =>x_return_status,
753 x_errorcode =>x_errorcode,
754 x_msg_count =>x_msg_count,
755 x_msg_data =>x_msg_data
756 ) ;
757
758 else
759 ---insert a new row
760 insert into bsc_user_kpi_access (
761 responsibility_id,
762 indicator,
763 start_date,
764 end_date,
765 CREATED_BY,
766 CREATION_DATE,
767 LAST_UPDATED_BY,
768 LAST_UPDATE_LOGIN,
769 LAST_UPDATE_DATE)
770 values(
771 P_resp_id,
772 P_indicator,
773 P_start_date,
774 P_end_date,
775 L_CREATED_BY,
776 L_CREATION_DATE,
777 L_LAST_UPDATED_BY,
778 L_LAST_UPDATE_LOGIN,
779 L_LAST_UPDATE_DATE);
780 end if;
781
782 x_return_status := FND_API.G_RET_STS_SUCCESS;
783
784 -----------------------------------
785 -- Make a standard call to get message count
786 -- and if count is 1, get message info.
787 -- The client will directly display the x_msg_data (which is already
788 -- translated) if the x_msg_count = 1;
789 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
790 -- Server-side procedure to access the messages, and consolidate them
791 -- and display them all at once or display one message after another.
792
793 FND_MSG_PUB.Count_And_Get
794 ( p_count => x_msg_count,
795 p_data => x_msg_data
796 );
797
798 exception
799 when no_data_found then
800 return;
801 when others then
802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
803 IF FND_MSG_PUB.Check_Msg_Level
804 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
805 THEN
806 FND_MSG_PUB.Add_Exc_Msg
807 ( G_PKG_NAME,
808 l_api_name
809 );
810 END IF;
811 FND_MSG_PUB.Count_And_Get
812 ( p_count => x_msg_count,
813 p_data => x_msg_data
814 );
815 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
816
817 end insert_kpi_access;
818
819
820
821 procedure Update_kpi_access (
822 P_ROWID in ROWID := null,
823 P_RESP_ID in number,
824 P_INDICATOR in number,
825 P_START_DATE in date,
826 P_END_DATE in date,
827 P_LAST_UPDATED_BY in NUMBER := null,
828 P_LAST_UPDATE_LOGIN in NUMBER := null,
829 P_LAST_UPDATE_DATE in DATE := null,
830 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
831 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
832 x_return_status OUT NOCOPY VARCHAR2,
833 x_errorcode OUT NOCOPY NUMBER,
834 x_msg_count OUT NOCOPY NUMBER,
835 x_msg_data OUT NOCOPY VARCHAR2
836 ) is
837
838 l_sysdate DATE := sysdate;
839 l_api_name CONSTANT VARCHAR2(30) := 'Update_kpi_access';
840 l_last_updated_by NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
841 l_last_update_login NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
842 l_last_update_date DATE := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
843 begin
844
845 -- Initialize API message list if necessary.
846 -- Initialize message list if p_init_msg_list is set to TRUE.
847 IF FND_API.to_Boolean( p_init_msg_list ) THEN
848 FND_MSG_PUB.initialize;
849 END IF;
850
851 if (P_ROWID is not null) then
852 update bsc_user_kpi_access
853 set responsibility_id = P_RESP_ID,
854 indicator = P_INDICATOR,
855 start_date = P_START_DATE,
856 end_date = P_END_DATE,
857 last_updated_by = l_last_updated_by,
858 last_update_login = l_last_update_login,
859 last_update_date = l_last_update_date
860 where rowid = P_ROWID;
861 else
862 UPDATE bsc_user_kpi_access
863 SET
864 start_date = P_START_DATE,
865 end_date = P_END_DATE,
866 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
867 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
868 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
869 WHERE responsibility_id = P_RESP_ID
870 AND indicator = P_INDICATOR ;
871 END IF;
872
873 x_return_status := FND_API.G_RET_STS_SUCCESS;
874
875 -----------------------------------
876 -- Make a standard call to get message count
877 -- and if count is 1, get message info.
878 -- The client will directly display the x_msg_data (which is already
879 -- translated) if the x_msg_count = 1;
880 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
881 -- Server-side procedure to access the messages, and consolidate them
882 -- and display them all at once or display one message after another.
883
884 FND_MSG_PUB.Count_And_Get
885 ( p_count => x_msg_count,
886 p_data => x_msg_data
887 );
888
889 exception
890 when no_data_found then
891 return;
892 when others then
893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
894 IF FND_MSG_PUB.Check_Msg_Level
895 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
896 THEN
897 FND_MSG_PUB.Add_Exc_Msg
898 ( G_PKG_NAME,
899 l_api_name
900 );
901 END IF;
902 FND_MSG_PUB.Count_And_Get
903 ( p_count => x_msg_count,
904 p_data => x_msg_data
905 );
906 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
907
908 end Update_kpi_access;
909
910
911 procedure remove_kpi_access(
912 p_resp_id in number,
913 p_indicator in number,
914 p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE,
915 p_commit IN VARCHAR2 := fnd_api.g_FALSE,
916 x_return_status OUT NOCOPY VARCHAR2,
917 x_errorcode OUT NOCOPY NUMBER,
918 x_msg_count OUT NOCOPY NUMBER,
919 x_msg_data OUT NOCOPY VARCHAR2
920 ) is
921 l_api_name CONSTANT VARCHAR2(30) := 'remove_kpi_access';
922 begin
923 -- Initialize API message list if necessary.
924 -- Initialize message list if p_init_msg_list is set to TRUE.
925 IF FND_API.to_Boolean( p_init_msg_list ) THEN
926 FND_MSG_PUB.initialize;
927 END IF;
928
929 delete from bsc_user_kpi_access
930 where responsibility_id=p_resp_id
931 and indicator=p_indicator;
932
933
934 x_return_status := FND_API.G_RET_STS_SUCCESS;
935
936 -----------------------------------
937 -- Make a standard call to get message count
938 -- and if count is 1, get message info.
939 -- The client will directly display the x_msg_data (which is already
940 -- translated) if the x_msg_count = 1;
941 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
942 -- Server-side procedure to access the messages, and consolidate them
943 -- and display them all at once or display one message after another.
944
945 FND_MSG_PUB.Count_And_Get
946 ( p_count => x_msg_count,
947 p_data => x_msg_data
948 );
949
950 exception
951 when no_data_found then
952 return;
953 when others then
954 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
955 IF FND_MSG_PUB.Check_Msg_Level
956 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
957 THEN
958 FND_MSG_PUB.Add_Exc_Msg
959 ( G_PKG_NAME,
960 l_api_name
961 );
962 END IF;
963 FND_MSG_PUB.Count_And_Get
964 ( p_count => x_msg_count,
965 p_data => x_msg_data
966 );
967 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
968
969 end remove_kpi_access;
970
971
972 END bsc_sec_utility;
[Home] [Help]