1 PACKAGE BODY BSC_LAUNCH_PAD_PVT AS
2 /* $Header: BSCLPADB.pls 120.1 2005/10/24 23:59:51 kyadamak noship $ */
3
4
5 /* --------------------------APPS MENUS -------------------------*/
6
7 /*===========================================================================+
8 |
9 | Name: INSERT_APP_MENU_VB
10 |
11 | Description: it is a wrapper for FND_MENUS_PKG.INSERT_ROW function
12 | This procedure is to be called from a VB program.
13 | If there is an error, the procedure inserts the error
14 | message in BSC_MESSAGE_LOGS table.
15 |
16 | Parameters: x_menu_id - Menu id
17 | x_menu_name - Menu Name
18 | x_user_menu_name - User Menu Name
19 | x_menu_type - Menu Type
20 | x_description - Description
21 | x_user id -User Id
22 |
23 | Notes:
24 |
25 +============================================================================*/
26 PROCEDURE INSERT_APP_MENU_VB(X_MENU_ID in NUMBER,
27 X_MENU_NAME in VARCHAR2,
28 X_USER_MENU_NAME in VARCHAR2,
29 X_MENU_TYPE in VARCHAR2,
30 X_DESCRIPTION in VARCHAR2,
31 X_USER_ID in NUMBER
32 ) IS
33 row_id VARCHAR2(30);
34 BEGIN
35 DELETE FND_MENUS WHERE MENU_ID = X_MENU_ID;
36 DELETE FND_MENUS_TL WHERE MENU_ID = X_MENU_ID;
37 FND_MENUS_PKG.INSERT_ROW( X_ROWID => row_id,
38 X_MENU_ID => X_MENU_ID,
39 X_MENU_NAME => X_MENU_NAME,
40 X_USER_MENU_NAME => X_USER_MENU_NAME,
41 X_MENU_TYPE => X_MENU_TYPE,
42 X_DESCRIPTION => X_DESCRIPTION,
43 X_CREATION_DATE => sysdate,
44 X_CREATED_BY => x_user_id,
45 X_LAST_UPDATE_DATE => sysdate,
46 X_LAST_UPDATED_BY => x_user_id,
47 X_LAST_UPDATE_LOGIN => 0 );
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 BSC_MESSAGE.Add(x_message => SQLERRM,
52 x_source => 'BSC_LAUNCH_PAD_PVT.INSERT_APP_MENU_VB',
53 x_mode => 'I');
54
55 END INSERT_APP_MENU_VB;
56 /*===========================================================================+
57 |
58 | Name: UPDATE_APP_MENU_VB
59 |
60 | Description: it is a wrapper for FND_MENUS_PKG.UPDATE_ROW function
61 | This procedure is to be called from a VB program.
62 | If there is an error, the procedure inserts the error
63 | message in BSC_MESSAGE_LOGS table.
64 |
65 | Parameters: x_menu_id - Menu id
66 | x_menu_name - Menu Name
67 | x_user_menu_name - User Menu Name
68 | x_menu_type - Menu Type
69 | x_description - Description
70 | x_user id -User Id
71 |
72 | Notes:
73 |
74 +============================================================================*/
75 PROCEDURE UPDATE_APP_MENU_VB(X_MENU_ID in NUMBER,
76 X_MENU_NAME in VARCHAR2,
77 X_USER_MENU_NAME in VARCHAR2,
78 X_MENU_TYPE in VARCHAR2,
79 X_DESCRIPTION in VARCHAR2,
80 X_USER_ID in NUMBER
81 ) IS
82 row_id VARCHAR2(30);
83 BEGIN
84 FND_MENUS_PKG.UPDATE_ROW(X_MENU_ID => X_MENU_ID,
85 X_MENU_NAME => X_MENU_NAME,
86 X_USER_MENU_NAME => X_USER_MENU_NAME,
87 X_MENU_TYPE => X_MENU_TYPE,
88 X_DESCRIPTION => X_DESCRIPTION,
89 X_LAST_UPDATE_DATE => sysdate,
90 X_LAST_UPDATED_BY => x_user_id,
91 X_LAST_UPDATE_LOGIN => 0 );
92
93 EXCEPTION
94 WHEN OTHERS THEN
95 BSC_MESSAGE.Add(x_message => SQLERRM,
96 x_source => 'BSC_LAUNCH_PAD_PVT.UPDATE_APP_MENU_VB',
97 x_mode => 'I');
98 END UPDATE_APP_MENU_VB;
99 /*===========================================================================+
100 |
101 | Name: DELETE_APP_MENU_VB
102 |
103 | Description: it is a wrapper for FND_MENUS_PKG.DELETE_ROW function
104 | This procedure is to be called from a VB program.
105 | If there is an error, the procedure inserts the error
106 | message in BSC_MESSAGE_LOGS table.
107 |
108 | Parameters: x_menu_id - Menu id
109 |
110 | Notes:
111 |
112 +============================================================================*/
113 PROCEDURE DELETE_APP_MENU_VB(X_MENU_ID in NUMBER
114 ) IS
115 row_id VARCHAR2(30);
116 BEGIN
117 FND_MENUS_PKG.DELETE_ROW(X_MENU_ID => X_MENU_ID);
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 BSC_MESSAGE.Add(x_message => SQLERRM,
122 x_source => 'BSC_LAUNCH_PAD_PVT.DELETE_APP_MENU_VB',
123 x_mode => 'I');
124 END DELETE_APP_MENU_VB;
125 /*===========================================================================+
126 |
127 | Name: CHECK_MENU_NAMES
128 |
129 | Description: Check if the menu name and User name are unique to
130 | insert as a new menu.
131 | Return : 'N' : Name Invalid, The name alreday exist
132 | 'U' : User Name Invalid, The user name alreday exist
133 | 'T' : True , The names don't exist. It can be added
134 | Parameters: X_MENU_ID Menu Id that will be inserted
135 | X_MENU_NAME Menu Name
136 | X_USER_MENU_NAME User Menu Name
137 +============================================================================*/
138 FUNCTION CHECK_MENU_NAMES(X_MENU_ID in NUMBER,
139 X_MENU_NAME in VARCHAR2,
140 X_USER_MENU_NAME in VARCHAR2
141 ) RETURN VARCHAR2 IS
142
143 h_count NUMBER;
144 h_val VARCHAR2(1);
145
146 BEGIN
147 -- Name
148 SELECT count(*)
149 INTO h_count
150 FROM FND_MENUS_VL
151 WHERE MENU_ID <> X_MENU_ID
152 AND (MENU_NAME = X_MENU_NAME);
153 IF h_count > 0 THEN
154 h_val := 'N';
155 RETURN h_val;
156 END IF;
157 -- User Name
158 SELECT count(*)
159 INTO h_count
160 FROM FND_MENUS_VL
161 WHERE MENU_ID <> X_MENU_ID
162 AND (upper(USER_MENU_NAME) = X_USER_MENU_NAME);
163 IF h_count > 0 THEN
164 h_val := 'U';
165 RETURN h_val;
166 END IF;
167 h_val := 'T';
168 RETURN h_val;
169 END CHECK_MENU_NAMES;
170
171 /* --------------------------FORM FUNCTIONS -------------------------*/
172 /*===========================================================================+
173 |
174 | Name: INSERT_FORM_FUNCTION_VB
175 |
176 | Description: it is a wrapper for FND_FORM_FUNCTIONS_PKG.INSERT_ROW function
177 | This procedure is to be called from a VB program.
178 | If there is an error, the procedure inserts the error
179 | message in BSC_MESSAGE_LOGS table.
180 |
181 | Parameters:
182 +============================================================================*/
183 PROCEDURE INSERT_FORM_FUNCTION_VB(X_FUNCTION_ID in NUMBER,
184 X_WEB_HOST_NAME in VARCHAR2,
185 X_WEB_AGENT_NAME in VARCHAR2,
186 X_WEB_HTML_CALL in VARCHAR2,
187 X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
188 X_WEB_SECURED in VARCHAR2,
189 X_WEB_ICON in VARCHAR2,
190 X_OBJECT_ID in NUMBER,
191 X_REGION_APPLICATION_ID in NUMBER,
192 X_REGION_CODE in VARCHAR2,
193 X_FUNCTION_NAME in VARCHAR2,
194 X_APPLICATION_ID in NUMBER,
195 X_FORM_ID in NUMBER,
196 X_PARAMETERS in VARCHAR2,
197 X_TYPE in VARCHAR2,
198 X_USER_FUNCTION_NAME in VARCHAR2,
199 X_DESCRIPTION in VARCHAR2,
200 X_USER_ID in NUMBER
201 ) IS
202 row_id VARCHAR2(30);
203
204 BEGIN
205 DELETE FND_FORM_FUNCTIONS WHERE FUNCTION_ID = X_FUNCTION_ID;
206 DELETE FND_FORM_FUNCTIONS_TL WHERE FUNCTION_ID = X_FUNCTION_ID;
207 fnd_form_functions_pkg.INSERT_ROW( X_ROWID => row_id,
208 X_FUNCTION_ID => X_FUNCTION_ID,
209 X_WEB_HOST_NAME => X_WEB_HOST_NAME,
210 X_WEB_AGENT_NAME => X_WEB_AGENT_NAME,
211 X_WEB_HTML_CALL => X_WEB_HTML_CALL,
212 X_WEB_ENCRYPT_PARAMETERS => X_WEB_ENCRYPT_PARAMETERS,
213 X_WEB_SECURED => X_WEB_SECURED,
214 X_WEB_ICON => X_WEB_ICON,
215 X_OBJECT_ID => X_OBJECT_ID,
216 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
217 X_REGION_CODE => X_REGION_CODE,
218 X_FUNCTION_NAME => X_FUNCTION_NAME,
219 X_APPLICATION_ID => X_APPLICATION_ID,
220 X_FORM_ID => X_FORM_ID,
221 X_PARAMETERS => X_PARAMETERS,
222 X_TYPE => X_TYPE,
223 X_USER_FUNCTION_NAME => X_USER_FUNCTION_NAME,
224 X_DESCRIPTION => X_DESCRIPTION,
225 X_CREATION_DATE => sysdate,
226 X_CREATED_BY => x_user_id,
227 X_LAST_UPDATE_DATE => sysdate,
228 X_LAST_UPDATED_BY => x_user_id,
229 X_LAST_UPDATE_LOGIN => 0 );
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 BSC_MESSAGE.Add(x_message => SQLERRM,
234 x_source => 'BSC_LAUNCH_PAD_PVT.INSERT_FORM_FUNCTION_VB',
235 x_mode => 'I');
236
237 END INSERT_FORM_FUNCTION_VB;
238 /*===========================================================================+
239 |
240 | Name: UPDATE_FORM_FUNCTION_VB
241 |
242 | Description: it is a wrapper for FND_FORM_FUNCTIONS_PKG.UPDATE_ROW function
243 | This procedure is to be called from a VB program.
244 | If there is an error, the procedure inserts the error
245 | message in BSC_MESSAGE_LOGS table.
246 |
247 | Parameters:
248 +============================================================================*/
249 PROCEDURE UPDATE_FORM_FUNCTION_VB(X_FUNCTION_ID in NUMBER,
250 X_WEB_HOST_NAME in VARCHAR2,
251 X_WEB_AGENT_NAME in VARCHAR2,
252 X_WEB_HTML_CALL in VARCHAR2,
253 X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
254 X_WEB_SECURED in VARCHAR2,
255 X_WEB_ICON in VARCHAR2,
256 X_OBJECT_ID in NUMBER,
257 X_REGION_APPLICATION_ID in NUMBER,
258 X_REGION_CODE in VARCHAR2,
259 X_FUNCTION_NAME in VARCHAR2,
260 X_APPLICATION_ID in NUMBER,
261 X_FORM_ID in NUMBER,
262 X_PARAMETERS in VARCHAR2,
263 X_TYPE in VARCHAR2,
264 X_USER_FUNCTION_NAME in VARCHAR2,
265 X_DESCRIPTION in VARCHAR2,
266 X_USER_ID in NUMBER
267 ) IS
268 row_id VARCHAR2(30);
269 BEGIN
270 fnd_form_functions_pkg.UPDATE_ROW(X_FUNCTION_ID => X_FUNCTION_ID,
271 X_WEB_HOST_NAME => X_WEB_HOST_NAME,
272 X_WEB_AGENT_NAME => X_WEB_AGENT_NAME,
273 X_WEB_HTML_CALL => X_WEB_HTML_CALL,
274 X_WEB_ENCRYPT_PARAMETERS => X_WEB_ENCRYPT_PARAMETERS,
275 X_WEB_SECURED => X_WEB_SECURED,
276 X_WEB_ICON => X_WEB_ICON,
277 X_OBJECT_ID => X_OBJECT_ID,
278 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
279 X_REGION_CODE => X_REGION_CODE,
280 X_FUNCTION_NAME => X_FUNCTION_NAME,
281 X_APPLICATION_ID => X_APPLICATION_ID,
282 X_FORM_ID => X_FORM_ID,
283 X_PARAMETERS => X_PARAMETERS,
284 X_TYPE => X_TYPE,
285 X_USER_FUNCTION_NAME => X_USER_FUNCTION_NAME,
286 X_DESCRIPTION => X_DESCRIPTION,
287 X_LAST_UPDATE_DATE => sysdate,
288 X_LAST_UPDATED_BY => x_user_id,
289 X_LAST_UPDATE_LOGIN => 0 );
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 BSC_MESSAGE.Add(x_message => SQLERRM,
294 x_source => 'BSC_LAUNCH_PAD_PVT.UPDATE_FORM_FUNCTION_VB',
295 x_mode => 'I');
296
297 END UPDATE_FORM_FUNCTION_VB;
298 /*===========================================================================+
299 |
300 | Name: DELETE_FORM_FUNCTION_VB
301 |
302 | Description: it is a wrapper for FND_FORM_FUNCTIONS_PKG.DELETE_ROW function
303 | This procedure is to be called from a VB program.
304 | If there is an error, the procedure inserts the error
305 | message in BSC_MESSAGE_LOGS table.
306 |
307 | Parameters:
308 +============================================================================*/
309 PROCEDURE DELETE_FORM_FUNCTION_VB(X_FUNCTION_ID in NUMBER
310 ) IS
311 row_id VARCHAR2(30);
312 BEGIN
313 fnd_form_functions_pkg.DELETE_ROW(X_FUNCTION_ID => X_FUNCTION_ID);
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 BSC_MESSAGE.Add(x_message => SQLERRM,
318 x_source => 'BSC_LAUNCH_PAD_PVT.DELETE_FORM_FUNCTION_VB',
319 x_mode => 'I');
320 END DELETE_FORM_FUNCTION_VB;
321 /*===========================================================================+
322 | FUNCTION CHECK_FUNCTION_NAMES
323 |
324 | Name: CHECK_FUNCTION_NAMES
325 |
326 | Description: Check if the fucntion name and User name are unique to
327 | insert as a new function.
328 | Return : 'N' : Name Invalid, The name alreday exist
329 | 'U' : User Name Invalid, The user name alreday exist
330 | 'T' : True , The names don't exist. It can be added
331 | Parameters: X_FUNCTION_ID Menu Id that will be inserted
332 | X_FUNCTION_NAME Menu Name
333 | X_USER_FUNCTION_NAME User Menu Name
334 +============================================================================*/
335
336 FUNCTION CHECK_FUNCTION_NAMES(X_FUNCTION_ID in NUMBER,
337 X_FUNCTION_NAME in VARCHAR2,
338 X_USER_FUNCTION_NAME in VARCHAR2
339 ) RETURN VARCHAR2 IS
340
341 h_count NUMBER;
342 h_val VARCHAR2(1);
343
344 BEGIN
345 -- Name
346 SELECT count(*)
347 INTO h_count
348 FROM FND_FORM_FUNCTIONS_VL
349 WHERE FUNCTION_ID <> X_FUNCTION_ID
350 AND (FUNCTION_NAME = X_FUNCTION_NAME);
351 IF h_count > 0 THEN
352 h_val := 'N';
353 RETURN h_val;
354 END IF;
355 -- User Name
356 SELECT count(*)
357 INTO h_count
358 FROM FND_FORM_FUNCTIONS_VL
359 WHERE FUNCTION_ID <> X_FUNCTION_ID
360 AND (USER_FUNCTION_NAME = X_USER_FUNCTION_NAME);
361 IF h_count > 0 THEN
362 h_val := 'U';
363 RETURN h_val;
364 END IF;
365 h_val := 'T';
366 RETURN h_val;
367 END CHECK_FUNCTION_NAMES;
368
369 /* --------------------------APPS MENU-ENTRIES -------------------------*/
370 /*===========================================================================+
371 |
372 | Name: INSERT_APP_MENU_ENTRIES_VB
373 |
374 | Description: it is a wrapper for FND_MENU_ENTRIES_PKG.INSERT_ROW function
375 | This procedure is to be called from a VB program.
376 | If there is an error, the procedure inserts the error
377 | message in BSC_MESSAGE_LOGS table.
378 |
379 | Parameters:
380 +============================================================================*/
381 PROCEDURE INSERT_APP_MENU_ENTRIES_VB(X_MENU_ID in NUMBER,
382 X_ENTRY_SEQUENCE in NUMBER,
383 X_SUB_MENU_ID in NUMBER,
384 X_FUNCTION_ID in NUMBER,
385 X_GRANT_FLAG in VARCHAR2,
386 X_PROMPT in VARCHAR2,
387 X_DESCRIPTION in VARCHAR2,
388 X_USER_ID in NUMBER
389 ) IS
390 row_id VARCHAR2(30);
391 BEGIN
392 DELETE FND_MENU_ENTRIES WHERE MENU_ID = X_MENU_ID AND ENTRY_SEQUENCE = X_ENTRY_SEQUENCE;
393 DELETE FND_MENU_ENTRIES_TL WHERE MENU_ID = X_MENU_ID AND ENTRY_SEQUENCE = X_ENTRY_SEQUENCE;
394
395 FND_MENU_ENTRIES_PKG.INSERT_ROW ( X_ROWID => row_id,
396 X_MENU_ID => X_MENU_ID,
397 X_ENTRY_SEQUENCE => X_ENTRY_SEQUENCE,
398 X_SUB_MENU_ID => X_SUB_MENU_ID,
399 X_FUNCTION_ID => X_FUNCTION_ID,
400 X_GRANT_FLAG => X_GRANT_FLAG,
401 X_PROMPT => X_PROMPT,
402 X_DESCRIPTION => X_DESCRIPTION,
403 X_CREATION_DATE => sysdate,
404 X_CREATED_BY => x_user_id,
405 X_LAST_UPDATE_DATE => sysdate,
406 X_LAST_UPDATED_BY => x_user_id,
407 X_LAST_UPDATE_LOGIN => 0 );
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 BSC_MESSAGE.Add(x_message => SQLERRM,
412 x_source => 'BSC_LAUNCH_PAD_PVT.INSERT_APP_MENU_ENTRIES_VB',
413 x_mode => 'I');
414
415 END INSERT_APP_MENU_ENTRIES_VB;
416 /*===========================================================================+
417 |
418 | Name: UPDATE_APP_MENU_ENTRIES_VB
419 |
420 | Description: it is a wrapper for FND_MENU_ENTRIES_PKG.UPDATE_ROW function
421 | This procedure is to be called from a VB program.
422 | If there is an error, the procedure inserts the error
423 | message in BSC_MESSAGE_LOGS table.
424 |
425 | Parameters:
426 +============================================================================*/
427 PROCEDURE UPDATE_APP_MENU_ENTRIES_VB(X_MENU_ID in NUMBER,
428 X_ENTRY_SEQUENCE in NUMBER,
429 X_SUB_MENU_ID in NUMBER,
430 X_FUNCTION_ID in NUMBER,
431 X_GRANT_FLAG in VARCHAR2,
432 X_PROMPT in VARCHAR2,
433 X_DESCRIPTION in VARCHAR2,
434 X_USER_ID in NUMBER
435 ) IS
436 BEGIN
437 FND_MENU_ENTRIES_PKG.UPDATE_ROW(X_MENU_ID => X_MENU_ID,
438 X_ENTRY_SEQUENCE => X_ENTRY_SEQUENCE,
439 X_SUB_MENU_ID => X_SUB_MENU_ID,
440 X_FUNCTION_ID => X_FUNCTION_ID,
441 X_GRANT_FLAG => X_GRANT_FLAG,
442 X_PROMPT => X_PROMPT,
443 X_DESCRIPTION => X_DESCRIPTION,
444 X_LAST_UPDATE_DATE => sysdate,
445 X_LAST_UPDATED_BY => x_user_id,
446 X_LAST_UPDATE_LOGIN => 0 );
447
448 EXCEPTION
449 WHEN OTHERS THEN
450 BSC_MESSAGE.Add(x_message => SQLERRM,
451 x_source => 'BSC_LAUNCH_PAD_PVT.UPDATE_APP_MENU_ENTRIES_VB',
452 x_mode => 'I');
453
454 END UPDATE_APP_MENU_ENTRIES_VB;
455 /*===========================================================================+
456 |
457 | Name: DELETE_APP_MENU_ENTRIES_VB
458 |
459 | Description: it is a wrapper for FND_MENU_ENTRIES_PKG.DELETE_ROW function
460 | This procedure is to be called from a VB program.
461 | If there is an error, the procedure inserts the error
462 | message in BSC_MESSAGE_LOGS table.
463 |
464 | Parameters:
465 +============================================================================*/
466 PROCEDURE DELETE_APP_MENU_ENTRIES_VB(X_MENU_ID in NUMBER,
467 X_ENTRY_SEQUENCE in NUMBER
468 ) IS
469 BEGIN
470 FND_MENU_ENTRIES_PKG.DELETE_ROW(X_MENU_ID => X_MENU_ID,
471 X_ENTRY_SEQUENCE => X_ENTRY_SEQUENCE);
472
473 EXCEPTION
474 WHEN OTHERS THEN
475 BSC_MESSAGE.Add(x_message => SQLERRM,
476 x_source => 'BSC_LAUNCH_PAD_PVT.DELETE_APP_MENU_ENTRIES_VB',
477 x_mode => 'I');
478 END DELETE_APP_MENU_ENTRIES_VB;
479
480
481 /*===========================================================================+
482 |
483 | Name: SECURITY_RULE_EXISTS_VB
484 |
485 | Description: it is a wrapper for FND_FUNCTION_SECURITY.SECURITY_RULE_EXISTS function
486 | This procedure is to be called from a VB program.
487 | If there is an error, the procedure inserts the error
488 | message in BSC_MESSAGE_LOGS table.
489 |
490 | Parameters:
491 +============================================================================*/
492 FUNCTION SECURITY_RULE_EXISTS_VB(responsibility_key in varchar2,
493 rule_type in varchar2 default 'F', -- F = Function, M = Menu
494 rule_name in varchar2
495 ) RETURN VARCHAR2 IS
496
497 h_val VARCHAR2(1);
498 BEGIN
499 -- Name
500 h_val := 'F';
501 IF FND_FUNCTION_SECURITY.SECURITY_RULE_EXISTS(responsibility_key,rule_type,rule_name) = TRUE THEN
502 h_val := 'T';
503 END IF;
504 RETURN h_val;
505 END SECURITY_RULE_EXISTS_VB;
506 /*===========================================================================+
507 | FUNCTION SECURITY_ACCESS_MENU
508 |
509 | Name: SECURITY_ACCESS_MENU
510 |
511 | Description: It verifies if a Responsibility has acces to a menu or not
512 | Return : 'T' : It has access
513 | 'F' : It doesn't have access
514 | Parameters: X_RESPO Responsibility
515 | X_MENU_ID Menu Id
516 +============================================================================*/
517
518 FUNCTION SECURITY_ACCESS_MENU(X_RESPO in NUMBER,
519 X_MENU_ID in NUMBER
520 ) RETURN VARCHAR2 IS
521
522 h_val VARCHAR2(1);
523 h_top_menu NUMBER;
524 h_count NUMBER;
525 cursor c_RESPO is
526 SELECT MENU_ID
527 FROM FND_RESPONSIBILITY_VL
528 WHERE RESPONSIBILITY_ID=X_RESPO;
529
530 BEGIN
531 -- Name
532 h_val := 'F';
533 h_count := 0;
534 -- Get The top Level Menu for the Responsbility
535 OPEN c_RESPO;
536 FETCH c_RESPO INTO h_top_menu;
537 IF (c_RESPO%notfound) THEN
538 h_top_menu := -1;
539 END IF;
540 CLOSE c_RESPO;
541 IF h_top_menu IS NOT NULL THEN
542 -- Check if the menu is in the Menu entries - It looks 3 levels down
543 SELECT COUNT(*) VAL
544 INTO h_count
545 FROM (
546 select SUB_MENU_ID from FND_MENU_ENTRIES_VL
547 WHERE MENU_ID = h_top_menu
548 UNION
549 select SUB_MENU_ID from FND_MENU_ENTRIES_VL
550 WHERE MENU_ID IN (select SUB_MENU_ID from FND_MENU_ENTRIES_VL
551 WHERE MENU_ID = h_top_menu)
552 UNION
553 select SUB_MENU_ID from FND_MENU_ENTRIES_VL
554 WHERE MENU_ID IN (select SUB_MENU_ID from FND_MENU_ENTRIES_VL
555 WHERE MENU_ID IN (select SUB_MENU_ID from FND_MENU_ENTRIES_VL
556 WHERE MENU_ID = h_top_menu))
557 ) MNS
558 WHERE MNS.SUB_MENU_ID=X_MENU_ID;
559 END IF;
560
561 IF h_count > 0 THEN
562 h_val := 'T';
563 END IF;
564 RETURN h_val;
565 END SECURITY_ACCESS_MENU;
566
567
568 /*===========================================================================+
569 | FUNCTION Migrate_Custom_Links
570 |
571 | Description: Migrate custom links from the source system.
572 | It creates the menu in the target system in case it does
573 | not exist and it is a BSC menu.
574 | It creates unexisting BSC functions inside the menus.
575 | It never update or delete an existing menu or function.
576 |
577 | Fixed Bug#2195153: Check user_menu_name/user_function_name
578 | to see if the menu/function already
579 | exist in the target system. This is after
580 | checking menu_name/function_name.
581 |
582 | Return : TRUE : no errors
583 | FALSE : error
584 |
585 | Parameters: x_src_db_link source db link.
586 +============================================================================*/
587 FUNCTION Migrate_Custom_Links(
588 x_src_db_link IN VARCHAR2
589 ) RETURN BOOLEAN IS
590
591 h_sql VARCHAR2(32000);
592 h_ret INTEGER;
593 h_cursor INTEGER;
594 h_ret1 INTEGER;
595 h_cursor1 INTEGER;
596 h_ret2 INTEGER;
597 h_cursor2 INTEGER;
598 h_i NUMBER;
599
600 h_menu_name VARCHAR2(30);
601 h_user_menu_name VARCHAR2(80);
602 h_menu_id_src NUMBER;
603
604 CURSOR c_menu_id IS
605 SELECT menu_id
606 FROM fnd_menus
607 WHERE menu_name = h_menu_name;
608
609 CURSOR c_menu_id_u IS
610 SELECT menu_id
611 FROM fnd_menus_vl
612 WHERE user_menu_name = h_user_menu_name;
613
614 h_menu_id NUMBER;
615 h_row_id VARCHAR2(30);
616 h_user_id NUMBER;
617 h_menu_type VARCHAR2(30);
618 h_description VARCHAR2(240);
619
620 h_function_name FND_FORM_FUNCTIONS.function_name%TYPE;
621 h_user_function_name VARCHAR2(80);
622 h_function_id_src NUMBER;
623
624 CURSOR c_function_id IS
625 SELECT function_id
626 FROM fnd_form_functions
627 WHERE function_name = h_function_name;
628
629 CURSOR c_function_id_u IS
630 SELECT function_id
631 FROM fnd_form_functions_vl
632 WHERE user_function_name = h_user_function_name;
633
634 h_function_id NUMBER;
635 h_web_host_name VARCHAR2(80);
636 h_web_agent_name VARCHAR2(80);
637 h_web_html_call VARCHAR2(240);
638 h_web_encrypt_parameters VARCHAR2(1);
639 h_web_secured VARCHAR2(1);
640 h_web_icon VARCHAR2(30);
641 h_object_id NUMBER;
642 h_region_application_id NUMBER;
643 h_region_code VARCHAR2(30);
644 h_application_id NUMBER;
645 h_form_id NUMBER;
646 h_parameters VARCHAR2(2000);
647 h_type VARCHAR2(30);
648
649 h_entry_sequence NUMBER;
650 h_sub_menu_id NUMBER;
651 h_grant_flag VARCHAR2(1);
652 h_prompt VARCHAR(60);
653
654 BEGIN
655
656 -- Get user id
657 -- Ref: bug#3482442 In corner cases this query can return more than one
658 -- row and it will fail. AUDSID is not PK. After meeting with
659 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
660 h_user_id := BSC_APPS.fnd_global_user_id;
661
662 -- Migrate menus with functions
663 h_sql := 'SELECT DISTINCT M.MENU_NAME, M.USER_MENU_NAME, T.LINK_ID'||
664 ' FROM BSC_TAB_VIEW_LABELS_B T, FND_MENUS_VL@'||x_src_db_link||' M'||
665 ' WHERE T.LABEL_TYPE = 2 AND NVL(T.LINK_ID, -1) <> -1 AND T.LINK_ID = M.MENU_ID';
666
667 h_cursor := DBMS_SQL.OPEN_CURSOR;
668 DBMS_SQL.PARSE(h_cursor, h_sql, DBMS_SQL.NATIVE);
669 DBMS_SQL.DEFINE_COLUMN(h_cursor, 1, h_menu_name, 30);
670 DBMS_SQL.DEFINE_COLUMN(h_cursor, 2, h_user_menu_name, 80);
671 DBMS_SQL.DEFINE_COLUMN(h_cursor, 3, h_menu_id_src);
672
673 h_ret := DBMS_SQL.EXECUTE(h_cursor);
674
675 WHILE DBMS_SQL.FETCH_ROWS(h_cursor) > 0 LOOP
676 DBMS_SQL.COLUMN_VALUE(h_cursor, 1, h_menu_name);
677 DBMS_SQL.COLUMN_VALUE(h_cursor, 2, h_user_menu_name);
678 DBMS_SQL.COLUMN_VALUE(h_cursor, 3, h_menu_id_src);
679
680 -- Get the menu id in the target
681 OPEN c_menu_id;
682 FETCH c_menu_id INTO h_menu_id;
683 IF c_menu_id%NOTFOUND THEN
684 h_menu_id := -1;
685 END IF;
686 CLOSE c_menu_id;
687
688 IF h_menu_id = -1 THEN
689 -- If the menu name does not exist,
690 -- we need to check for the user_menu_name
691 OPEN c_menu_id_u;
692 FETCH c_menu_id_u INTO h_menu_id;
693 IF c_menu_id_u%NOTFOUND THEN
694 h_menu_id := -1;
695 END IF;
696 CLOSE c_menu_id_u;
697 END IF;
698
699 IF h_menu_id = -1 THEN
700 -- menu does not exist in the target
701
702 -- Create the menu only if the menu is for BSC
703 IF UPPER(SUBSTR(h_menu_name,1,3)) = 'BSC' THEN
704 -- Get the menu_id
705 SELECT fnd_menus_s.nextval INTO h_menu_id FROM DUAL;
706
707 -- Get all info about the menu in the source to create it in the target
708 h_menu_type := NULL;
709 h_description := NULL;
710
711 h_sql := 'SELECT type, description'||
712 ' FROM fnd_menus_vl@'||x_src_db_link||
713 ' WHERE menu_name =:1';
714 /* ' WHERE menu_name = '''||h_menu_name||'''';*/
715
716
717 h_cursor1 := DBMS_SQL.OPEN_CURSOR;
718 DBMS_SQL.PARSE(h_cursor1, h_sql, DBMS_SQL.NATIVE);
719 DBMS_SQL.DEFINE_COLUMN(h_cursor1, 1, h_menu_type, 30);
720 DBMS_SQL.DEFINE_COLUMN(h_cursor1, 2, h_description, 240);
721 DBMS_SQL.BIND_VARIABLE(h_cursor1,':1',h_menu_name);
722 --DBMS_OUTPUT.PUT_LINE('DYNAMIC SQL --------------> '|| h_cursor1);
723 h_ret1 := DBMS_SQL.EXECUTE(h_cursor1);
724
725 -- We know that the menu exists in the source
726 IF DBMS_SQL.FETCH_ROWS(h_cursor1) > 0 THEN
727 DBMS_SQL.COLUMN_VALUE(h_cursor1, 1, h_menu_type);
728 DBMS_SQL.COLUMN_VALUE(h_cursor1, 2, h_description);
729 END IF;
730 DBMS_SQL.CLOSE_CURSOR(h_cursor1);
731
732 -- call the api to create the menu
733 FND_MENUS_PKG.INSERT_ROW(
734 X_ROWID => h_row_id,
735 X_MENU_ID => h_menu_id,
736 X_MENU_NAME => h_menu_name,
737 X_USER_MENU_NAME => h_user_menu_name,
738 X_MENU_TYPE => h_menu_type,
739 X_DESCRIPTION => h_description,
740 X_CREATION_DATE => sysdate,
741 X_CREATED_BY => h_user_id,
742 X_LAST_UPDATE_DATE => sysdate,
743 X_LAST_UPDATED_BY => h_user_id,
744 X_LAST_UPDATE_LOGIN => 0
745 );
746 END IF;
747
748 END IF;
749
750 -- Migrate functions only if the menu is for BSC
751 IF h_menu_id <> -1 THEN
752 IF UPPER(SUBSTR(h_menu_name,1,3)) = 'BSC' THEN
753 -- Get the menu entries (functions) associated to this menu in the source system
754 -- and are not associated with the same menu in the target system.
755 -- We need to create those entries in the target menu.
756 -- Only see BSC functions
757 h_sql := 'SELECT SF.FUNCTION_NAME, SF.USER_FUNCTION_NAME, SF.FUNCTION_ID'||
758 ' FROM FND_MENU_ENTRIES@'||x_src_db_link||' SE, FND_FORM_FUNCTIONS_VL@'||x_src_db_link||' SF'||
759 ' WHERE SE.MENU_ID = :1 AND SE.FUNCTION_ID = SF.FUNCTION_ID AND'||
760 ' SF.APPLICATION_ID = 271 AND SF.FUNCTION_NAME NOT IN ('||
761 ' SELECT TF.FUNCTION_NAME FROM FND_MENU_ENTRIES TE, FND_FORM_FUNCTIONS TF'||
762 ' WHERE TE.MENU_ID = :2 AND TE.FUNCTION_ID = TF.FUNCTION_ID AND TF.APPLICATION_ID = 271)';
763 /*' WHERE TE.MENU_ID = '||h_menu_id||' AND TE.FUNCTION_ID = TF.FUNCTION_ID AND TF.APPLICATION_ID = 271)';*/
764
765 h_cursor1 := DBMS_SQL.OPEN_CURSOR;
766 DBMS_SQL.PARSE(h_cursor1, h_sql, DBMS_SQL.NATIVE);
767 DBMS_SQL.DEFINE_COLUMN(h_cursor1, 1, h_function_name, 480);
768 DBMS_SQL.DEFINE_COLUMN(h_cursor1, 2, h_user_function_name, 80);
769 DBMS_SQL.DEFINE_COLUMN(h_cursor1, 3, h_function_id_src);
770 DBMS_SQL.BIND_VARIABLE(h_cursor1, ':1', h_menu_id_src);
771 DBMS_SQL.BIND_VARIABLE(h_cursor1, ':2', h_menu_id);
772 h_ret1 := DBMS_SQL.EXECUTE(h_cursor1);
773
774 WHILE DBMS_SQL.FETCH_ROWS(h_cursor1) > 0 LOOP
775 DBMS_SQL.COLUMN_VALUE(h_cursor1, 1, h_function_name);
776 DBMS_SQL.COLUMN_VALUE(h_cursor1, 2, h_user_function_name);
777 DBMS_SQL.COLUMN_VALUE(h_cursor1, 3, h_function_id_src);
778
779 -- Check if the function exists
780 OPEN c_function_id;
781 FETCH c_function_id INTO h_function_id;
782 IF c_function_id%NOTFOUND THEN
783 h_function_id := -1;
784 END IF;
785 CLOSE c_function_id;
786
787 IF h_function_id = -1 THEN
788 -- If the function name does not exist,
789 -- we need to check for the user_function_name
790 OPEN c_function_id_u;
791 FETCH c_function_id_u INTO h_function_id;
792 IF c_function_id_u%NOTFOUND THEN
793 h_function_id := -1;
794 END IF;
795 CLOSE c_function_id_u;
796 END IF;
797
798 -- Create the function if does not exist. We know that it is a BSC function.
799 IF h_function_id = -1 THEN
800 -- Get the function_id
801 SELECT fnd_form_functions_s.nextval INTO h_function_id FROM DUAL;
802
803 -- Get all info about the function in the source to create it in the target
804 h_web_host_name := NULL;
805 h_web_agent_name := NULL;
806 h_web_html_call := NULL;
807 h_web_encrypt_parameters := NULL;
808 h_web_secured := NULL;
809 h_web_icon := NULL;
810 h_object_id := NULL;
811 h_region_application_id := NULL;
812 h_region_code := NULL;
813 h_application_id := NULL;
814 h_form_id := NULL;
815 h_parameters := NULL;
816 h_type := NULL;
817 h_description := NULL;
818
819 h_sql := 'SELECT web_host_name, web_agent_name, web_html_call,'||
820 ' web_encrypt_parameters, web_secured, web_icon, object_id,'||
821 ' region_application_id, region_code, application_id,'||
822 ' form_id, parameters, type, description'||
823 ' FROM fnd_form_functions_vl@'||x_src_db_link||
824 ' WHERE function_name = :1';
825 /*' WHERE function_name = '''||h_function_name||'''';*/
826
827 h_cursor2 := DBMS_SQL.OPEN_CURSOR;
828 DBMS_SQL.PARSE(h_cursor2, h_sql, DBMS_SQL.NATIVE);
829 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 1, h_web_host_name, 80);
830 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 2, h_web_agent_name, 80);
831 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 3, h_web_html_call, 240);
832 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 4, h_web_encrypt_parameters, 1);
833 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 5, h_web_secured, 1);
834 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 6, h_web_icon, 30);
835 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 7, h_object_id);
836 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 8, h_region_application_id);
837 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 9, h_region_code, 30);
838 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 10, h_application_id);
839 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 11, h_form_id);
840 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 12, h_parameters, 2000);
841 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 13, h_type, 30);
842 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 14, h_description, 240);
843 DBMS_SQL.BIND_VARIABLE(h_cursor2, ':1', h_function_name);
844 h_ret2 := DBMS_SQL.EXECUTE(h_cursor2);
845
846 -- We know that the menu exists in the source
847 IF DBMS_SQL.FETCH_ROWS(h_cursor2) > 0 THEN
848 DBMS_SQL.COLUMN_VALUE(h_cursor2, 1, h_web_host_name);
849 DBMS_SQL.COLUMN_VALUE(h_cursor2, 2, h_web_agent_name);
850 DBMS_SQL.COLUMN_VALUE(h_cursor2, 3, h_web_html_call);
851 DBMS_SQL.COLUMN_VALUE(h_cursor2, 4, h_web_encrypt_parameters);
852 DBMS_SQL.COLUMN_VALUE(h_cursor2, 5, h_web_secured);
853 DBMS_SQL.COLUMN_VALUE(h_cursor2, 6, h_web_icon);
854 DBMS_SQL.COLUMN_VALUE(h_cursor2, 7, h_object_id);
855 DBMS_SQL.COLUMN_VALUE(h_cursor2, 8, h_region_application_id);
856 DBMS_SQL.COLUMN_VALUE(h_cursor2, 9, h_region_code);
857 DBMS_SQL.COLUMN_VALUE(h_cursor2, 10, h_application_id);
858 DBMS_SQL.COLUMN_VALUE(h_cursor2, 11, h_form_id);
859 DBMS_SQL.COLUMN_VALUE(h_cursor2, 12, h_parameters);
860 DBMS_SQL.COLUMN_VALUE(h_cursor2, 13, h_type);
861 DBMS_SQL.COLUMN_VALUE(h_cursor2, 14, h_description);
862 END IF;
863 DBMS_SQL.CLOSE_CURSOR(h_cursor2);
864
865 -- call the api to create the function
866 FND_FORM_FUNCTIONS_PKG.INSERT_ROW(
867 X_ROWID => h_row_id,
868 X_FUNCTION_ID => h_function_id,
869 X_WEB_HOST_NAME => h_web_host_name,
870 X_WEB_AGENT_NAME => h_web_agent_name,
871 X_WEB_HTML_CALL => h_web_html_call,
872 X_WEB_ENCRYPT_PARAMETERS => h_web_encrypt_parameters,
873 X_WEB_SECURED => h_web_secured,
874 X_WEB_ICON => h_web_icon,
875 X_OBJECT_ID => h_object_id,
876 X_REGION_APPLICATION_ID => h_region_application_id,
877 X_REGION_CODE => h_region_code,
878 X_FUNCTION_NAME => h_function_name,
879 X_APPLICATION_ID => h_application_id,
880 X_FORM_ID => h_form_id,
881 X_PARAMETERS => h_parameters,
882 X_TYPE => h_type,
883 X_USER_FUNCTION_NAME => h_user_function_name,
884 X_DESCRIPTION => h_description,
885 X_CREATION_DATE => sysdate,
886 X_CREATED_BY => h_user_id,
887 X_LAST_UPDATE_DATE => sysdate,
888 X_LAST_UPDATED_BY => h_user_id,
889 X_LAST_UPDATE_LOGIN => 0
890 );
891
892 END IF;
893
894
895 -- Create the menu entry in the target
896 -- Get the maximum entry sequence
897 SELECT nvl(max(entry_sequence)+1, 1) INTO h_entry_sequence
898 FROM fnd_menu_entries
899 WHERE menu_id = h_menu_id;
900
901 -- Get all info about the menu entry in the source to create it in the target
902 h_sub_menu_id := NULL;
903 h_grant_flag := NULL;
904 h_prompt := NULL;
905 h_description := NULL;
906
907 h_sql := 'SELECT sub_menu_id, grant_flag, prompt, description'||
908 ' FROM fnd_menu_entries_vl@'||x_src_db_link||
909 ' WHERE menu_id = :1 AND function_id = :2';
910 /*' WHERE menu_id = '||h_menu_id_src||' AND function_id = '||h_function_id_src;*/
911
912
913 h_cursor2 := DBMS_SQL.OPEN_CURSOR;
914 DBMS_SQL.PARSE(h_cursor2, h_sql, DBMS_SQL.NATIVE);
915 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 1, h_sub_menu_id);
916 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 2, h_grant_flag, 1);
917 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 3, h_prompt, 60);
918 DBMS_SQL.DEFINE_COLUMN(h_cursor2, 4, h_description, 240);
919 DBMS_SQL.BIND_VARIABLE(h_cursor2, ':1', h_menu_id_src);
920 DBMS_SQL.BIND_VARIABLE(h_cursor2, ':2', h_function_id_src);
921 h_ret2 := DBMS_SQL.EXECUTE(h_cursor2);
922
923 -- We know that the menu entry exists in the source
924 IF DBMS_SQL.FETCH_ROWS(h_cursor2) > 0 THEN
925 DBMS_SQL.COLUMN_VALUE(h_cursor2, 1, h_sub_menu_id);
926 DBMS_SQL.COLUMN_VALUE(h_cursor2, 2, h_grant_flag);
927 DBMS_SQL.COLUMN_VALUE(h_cursor2, 3, h_prompt);
928 DBMS_SQL.COLUMN_VALUE(h_cursor2, 4, h_description);
929 END IF;
930 DBMS_SQL.CLOSE_CURSOR(h_cursor2);
931
932 -- Create the menu entry
933 FND_MENU_ENTRIES_PKG.INSERT_ROW (
934 X_ROWID => h_row_id,
935 X_MENU_ID => h_menu_id,
936 X_ENTRY_SEQUENCE => h_entry_sequence,
937 X_SUB_MENU_ID => h_sub_menu_id,
938 X_FUNCTION_ID => h_function_id,
939 X_GRANT_FLAG => h_grant_flag,
940 X_PROMPT => h_prompt,
941 X_DESCRIPTION => h_description,
942 X_CREATION_DATE => sysdate,
943 X_CREATED_BY => h_user_id,
944 X_LAST_UPDATE_DATE => sysdate,
945 X_LAST_UPDATED_BY => h_user_id,
946 X_LAST_UPDATE_LOGIN => 0
947 );
948 END LOOP;
949 DBMS_SQL.CLOSE_CURSOR(h_cursor1);
950
951 END IF;
952 END IF;
953
954 END LOOP;
955 DBMS_SQL.CLOSE_CURSOR(h_cursor);
956
957
958 -- Now that all the menus have been migrated we can
959 -- update the link_id in BSC_TAB_VIEW_LABLES_B with the menu_id in the target
960 h_sql := 'UPDATE bsc_tab_view_labels_b l
961 SET link_id = NVL((SELECT t.menu_id
962 FROM fnd_menus t, fnd_menus@'||x_src_db_link||' s
963 WHERE t.menu_name = s.menu_name AND
964 l.link_id = s.menu_id),
965 NVL((SELECT t.menu_id
966 FROM fnd_menus_vl t, fnd_menus_vl@'||x_src_db_link||' s
967 WHERE t.user_menu_name = s.user_menu_name AND
968 l.link_id = s.menu_id),
969 -1))
970 WHERE label_type = 2';
971 BSC_APPS.Execute_Immediate(h_sql);
972
973 RETURN TRUE;
974
975 EXCEPTION
976 WHEN OTHERS THEN
977 BSC_MESSAGE.Add (x_message => SQLERRM,
978 x_source => 'BSC_LAUNCH_PAD_PVT.Migrate_Custom_Links');
979 RETURN FALSE;
980 END Migrate_Custom_Links;
981
982
983 /*===========================================================================+
984 | FUNCTION Migrate_Custom_Links_Security
985 |
986 | Description: Assing the custom links (menus) to the target responsibility
987 | according to the source responsibility.
988 | Only add BSC menus to the target responsibility.
989 |
990 | Return : TRUE : no errors
991 | FALSE : error
992 |
993 +============================================================================*/
994 FUNCTION Migrate_Custom_Links_Security(
995 x_trg_resp IN NUMBER,
996 x_src_resp IN NUMBER,
997 x_src_db_link IN VARCHAR2
998 ) RETURN BOOLEAN IS
999
1000 e_no_migrate EXCEPTION;
1001
1002 h_sql VARCHAR2(32000);
1003 h_ret INTEGER;
1004 h_cursor INTEGER;
1005
1006 CURSOR c_top_menu_id IS
1007 SELECT menu_id
1008 FROM fnd_responsibility_vl
1009 WHERE responsibility_id = x_trg_resp;
1010
1011 h_top_menu_id NUMBER;
1012 h_top_menu_id_src NUMBER;
1013 h_menu_id NUMBER;
1014 h_description VARCHAR2(240);
1015 h_entry_sequence NUMBER;
1016 h_row_id VARCHAR2(30) := NULL;
1017 h_function_id NUMBER := NULL;
1018 h_grant_flag VARCHAR2(1) := 'Y';
1019 h_prompt VARCHAR(60) := NULL;
1020 h_user_id NUMBER;
1021
1022 BEGIN
1023 -- Get user id
1024 -- Ref: bug#3482442 In corner cases this query can return more than one
1025 -- row and it will fail. AUDSID is not PK. After meeting with
1026 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
1027 h_user_id := BSC_APPS.fnd_global_user_id;
1028
1029 -- Get the top menu id of the source responsibility
1030 h_sql := 'SELECT MENU_ID'||
1031 ' FROM FND_RESPONSIBILITY_VL@'||x_src_db_link||
1032 /*' WHERE RESPONSIBILITY_ID = '||x_src_resp;*/
1033 ' WHERE RESPONSIBILITY_ID = :1';
1034
1035 h_cursor := DBMS_SQL.OPEN_CURSOR;
1036 DBMS_SQL.PARSE(h_cursor, h_sql, DBMS_SQL.NATIVE);
1037 DBMS_SQL.DEFINE_COLUMN(h_cursor, 1, h_top_menu_id_src);
1038 DBMS_SQL.BIND_VARIABLE(h_cursor, ':1', x_src_resp);
1039 h_ret := DBMS_SQL.EXECUTE(h_cursor);
1040
1041 IF DBMS_SQL.FETCH_ROWS(h_cursor) > 0 THEN
1042 DBMS_SQL.COLUMN_VALUE(h_cursor, 1, h_top_menu_id_src);
1043 ELSE
1044 -- The source responsibility does not have a top menu
1045 RAISE e_no_migrate;
1046 END IF;
1047 DBMS_SQL.CLOSE_CURSOR(h_cursor);
1048
1049
1050 -- Get the top menu id of the target responsibiity
1051 OPEN c_top_menu_id;
1052 FETCH c_top_menu_id INTO h_top_menu_id;
1053 IF c_top_menu_id%NOTFOUND THEN
1054 CLOSE c_top_menu_id;
1055 RAISE e_no_migrate;
1056 END IF;
1057 CLOSE c_top_menu_id;
1058
1059
1060 -- Get the information of the menus we need to assign to the top menu of the
1061 -- target responsibility.
1062 -- Those menus are the ones that the source responsibility has access to and
1063 -- are not already assigned to the top menu of the target responsibility and
1064 -- are BSC menus.
1065 h_sql := 'SELECT'||
1066 ' L.LINK_ID,'||
1067 ' M.DESCRIPTION'||
1068 ' FROM'||
1069 ' BSC_TAB_VIEW_LABELS_B L,'||
1070 ' BSC_TAB_VIEW_LABELS_B@'||x_src_db_link||' LS,'||
1071 ' FND_MENUS_VL M'||
1072 ' WHERE'||
1073 ' L.TAB_ID = LS.TAB_ID AND'||
1074 ' L.TAB_VIEW_ID = LS.TAB_VIEW_ID AND'||
1075 ' L.LABEL_ID = LS.LABEL_ID AND'||
1076 ' L.LABEL_TYPE = 2 AND'||
1077 ' NVL(L.LINK_ID, -1) <> -1 AND'||
1078 ' L.LINK_ID = M.MENU_ID AND'||
1079 ' UPPER(SUBSTR(MENU_NAME,1,3)) = ''BSC'' AND'||
1080 ' NOT (L.LINK_ID IN (SELECT SUB_MENU_ID'||
1081 ' FROM FND_MENU_ENTRIES_VL'||
1082 ' WHERE MENU_ID = :1 AND SUB_MENU_ID IS NOT NULL)) AND'||
1083 /*' WHERE MENU_ID = '||h_top_menu_id||' AND SUB_MENU_ID IS NOT NULL)) AND'||*/
1084 ' LS.LINK_ID IN (SELECT SUB_MENU_ID'||
1085 ' FROM FND_MENU_ENTRIES_VL@'||x_src_db_link||
1086 ' WHERE MENU_ID = :2 AND SUB_MENU_ID IS NOT NULL)';
1087 /* ' WHERE MENU_ID = '||h_top_menu_id_src||' AND SUB_MENU_ID IS NOT NULL)';*/
1088
1089
1090
1091 h_cursor := DBMS_SQL.OPEN_CURSOR;
1092 DBMS_SQL.PARSE(h_cursor, h_sql, DBMS_SQL.NATIVE);
1093 DBMS_SQL.DEFINE_COLUMN(h_cursor, 1, h_menu_id);
1094 DBMS_SQL.DEFINE_COLUMN(h_cursor, 2, h_description, 240);
1095 DBMS_SQL.BIND_VARIABLE(h_cursor, ':1', h_top_menu_id);
1096 DBMS_SQL.BIND_VARIABLE(h_cursor, ':2', h_top_menu_id_src);
1097 h_ret := DBMS_SQL.EXECUTE(h_cursor);
1098
1099 WHILE DBMS_SQL.FETCH_ROWS(h_cursor) > 0 LOOP
1100 DBMS_SQL.COLUMN_VALUE(h_cursor, 1, h_menu_id);
1101 DBMS_SQL.COLUMN_VALUE(h_cursor, 2, h_description);
1102
1103 -- Get the maximum entry sequence
1104 SELECT nvl(max(entry_sequence)+1, 1) INTO h_entry_sequence
1105 FROM fnd_menu_entries
1106 WHERE menu_id = h_top_menu_id;
1107
1108 -- Create the menu entry
1109 FND_MENU_ENTRIES_PKG.INSERT_ROW (
1110 X_ROWID => h_row_id,
1111 X_MENU_ID => h_top_menu_id,
1112 X_ENTRY_SEQUENCE => h_entry_sequence,
1113 X_SUB_MENU_ID => h_menu_id,
1114 X_FUNCTION_ID => h_function_id,
1115 X_GRANT_FLAG => h_grant_flag,
1116 X_PROMPT => h_prompt,
1117 X_DESCRIPTION => h_description,
1118 X_CREATION_DATE => sysdate,
1119 X_CREATED_BY => h_user_id,
1120 X_LAST_UPDATE_DATE => sysdate,
1121 X_LAST_UPDATED_BY => h_user_id,
1122 X_LAST_UPDATE_LOGIN => 0
1123 );
1124 END LOOP;
1125 DBMS_SQL.CLOSE_CURSOR(h_cursor);
1126
1127 RETURN TRUE;
1128
1129 EXCEPTION
1130 WHEN e_no_migrate THEN
1131 RETURN TRUE;
1132
1133 WHEN OTHERS THEN
1134 BSC_MESSAGE.Add (x_message => SQLERRM,
1135 x_source => 'BSC_LAUNCH_PAD_PVT.Migrate_Custom_Links_Security');
1136 RETURN FALSE;
1137 END Migrate_Custom_Links_Security;
1138
1139 /**********************************************************************
1140 Name :- is_Launch_Pad_Attached
1141 Description : -This fucntion will validate if the launchad is attached
1142 to the root application module or not.
1143 OutPut :- TRUE : launchpad is attached.
1144 FALSE : not attached.
1145 /*********************************************************************/
1146
1147 FUNCTION is_Launch_Pad_Attached
1148 (
1149 p_Menu_Id IN FND_MENUS.menu_id%TYPE
1150 , p_Sub_Menu_Id IN FND_MENUS.menu_id%TYPE
1151
1152 ) RETURN BOOLEAN
1153 IS
1154 l_count NUMBER;
1155 BEGIN
1156
1157 SELECT COUNT(0)
1158 INTO l_count
1159 FROM FND_MENU_ENTRIES
1160 WHERE MENU_ID = p_Menu_Id
1161 AND SUB_MENU_ID = p_Sub_Menu_Id;
1162
1163 IF (l_count<>0) THEN
1164 RETURN TRUE;
1165 ELSE
1166 RETURN FALSE;
1167 END IF;
1168 END is_Launch_Pad_Attached;
1169
1170 /**********************************************************************
1171 Name :- get_entry_sequence
1172 Description : -This fucntion returns the entry sequnce corresponding to
1173 to the root application menu and the launchpad menu id.
1174 /*********************************************************************/
1175
1176 FUNCTION get_entry_sequence
1177 (
1178 p_Menu_Id IN FND_MENUS.menu_id%TYPE
1179 , p_Sub_Menu_Id IN FND_MENUS.menu_id%TYPE
1180 ) RETURN NUMBER
1181 IS
1182 l_entry_sequence FND_MENU_ENTRIES.entry_sequence%TYPE;
1183
1184 CURSOR c_entry_sequnece IS
1185 SELECT ENTRY_SEQUENCE
1186 FROM FND_MENU_ENTRIES
1187 WHERE MENU_ID = p_Menu_Id
1188 AND SUB_MENU_ID = p_Sub_Menu_Id;
1189
1190 BEGIN
1191
1192 IF(c_entry_sequnece%ISOPEN) THEN
1193 CLOSE c_entry_sequnece;
1194 END IF;
1195
1196 OPEN c_entry_sequnece;
1197 FETCH c_entry_sequnece INTO l_entry_sequence;
1198 CLOSE c_entry_sequnece;
1199
1200 RETURN l_entry_sequence;
1201 END get_entry_sequence;
1202
1203
1204 END BSC_LAUNCH_PAD_PVT;
[Home] [Help]