1 PACKAGE BODY CSD_MIGRATE_FROM_12X_PKG8 AS
2 /* $Header: csdmig8b.pls 120.0 2011/04/19 21:23:01 swai noship $ */
3
4 /* Procedure Name: CSD_MENU_RESTRUCTURE_MIG8 */
5 /* This procedure updates the existing csd_ro_savedsearches table to */
6 /* support the iSupport multiparty enhancement. The party_id for the */
7 /* existing records will be updated to default party_id corresponding to*/
8 /* user_id. */
9 /* @param. None */
10
11 procedure CSD_MENU_RESTRUCTURE_MIG8 is
12 -- Cursors --
13 -- query to get all menu entries to be deleted
14 -- note: will only delete entry if it has not been modified
15 cursor c_get_menu_entries is
16 ( select me.menu_id, me.entry_sequence
17 from fnd_menu_entries me, fnd_menus m, fnd_form_functions f
18 where m.menu_id = me.menu_id
19 and me.function_id = f.function_id
20 and me.last_updated_by in (120,121,122)
21 and m.menu_name='CSD_DEPOT_MENU'
22 and f.function_name in ('CSDHVR', 'CSDSRRCL', 'MRPCHORG' )
23 union all
24 select me.menu_id, me.entry_sequence
25 from fnd_menu_entries me, fnd_menus m, fnd_form_functions f
26 where m.menu_id = me.menu_id
27 and me.function_id = f.function_id
28 and me.last_updated_by in (120,121,122)
29 and m.menu_name='CSD_SETUP_MENU'
30 and f.function_name in ('CSDRTSET', 'CSDDCSET', 'CSDSCSET', 'CSDRECSET',
31 'CSDFLSTS', 'CSDFSSET', 'CSDQLSET', 'CSDAGSET',
32 'CSDDEFCODE', 'CSDRBIRSSET', 'CSDRBIDRSET', 'CSDRBIETSET',
33 'CSDRECLSET', 'CSDRECSTMP', 'CSDFLSTS_RCL', 'CSD_REGION_GEOGRAPHY_SETUP')
34 union all
35 select me.menu_id, me.entry_sequence
36 from fnd_menu_entries me, fnd_menus m, fnd_form_functions f
37 where m.menu_id = me.menu_id
38 and me.function_id = f.function_id
39 and me.last_updated_by in (120,121,122)
40 and m.menu_name='CSD_OTHER_MENU'
41 and f.function_name in ('WF_WORKLIST', 'WF_SS_STATUS_MONITOR')
42 ); -- end cusor c_get_menu_entries
43
44 -- Constants --
45 MAX_BUFFER_SIZE NUMBER := 500;
46
47 -- Variables --
48 l_menu_id_arr MENU_ID_TBL;
49 l_menu_entry_id_arr MENU_ENTRY_ID_TBL;
50 BEGIN
51
52 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
53 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
54 'CSD.PLSQL.CSD_MIGRATE_FROM_121_PKG8.csd_menu_restructure_mig8',
55 'Begin CSD_MENU_RESTRUCTURE_MIG8');
56 end if;
57
58 OPEN c_get_menu_entries;
59
60 LOOP
61
62 FETCH c_get_menu_entries BULK COLLECT INTO l_menu_id_arr, l_menu_entry_id_arr LIMIT MAX_BUFFER_SIZE;
63
64 FOR i IN 1..l_menu_entry_id_arr.COUNT
65 LOOP
66 FND_MENU_ENTRIES_PKG.DELETE_ROW (
67 X_MENU_ID => l_menu_id_arr(i),
68 X_ENTRY_SEQUENCE => l_menu_entry_id_arr(i)
69 );
70 END LOOP;
71
72 COMMIT;
73
74 EXIT WHEN c_get_menu_entries%NOTFOUND;
75 END LOOP;
76
77 IF c_get_menu_entries%ISOPEN THEN
78 CLOSE c_get_menu_entries;
79 END IF;
80
81 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
82 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
83 'CSD.PLSQL.CSD_MIGRATE_FROM_121_PKG8.csd_menu_restructure_mig8',
84 'End CSD_MENU_RESTRUCTURE_MIG8');
85 end if;
86
87 COMMIT;
88
89 END CSD_MENU_RESTRUCTURE_MIG8;
90
91 END CSD_MIGRATE_FROM_12X_PKG8;