1 PACKAGE BODY BSC_UPGRADES_GENERIC AS
2 /* $Header: BSCUPGNB.pls 120.0 2005/06/01 14:37:33 appldev noship $ */
3 --- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
4 --- All rights reserved.
5 ---
6 ---==========================================================================
7 --- FILENAME
8 ---
9 --- BSCUPGNB.pls
10 ---
11 --- DESCRIPTION
12 --- Package body File for Upgrade scripts on BSC side. bscup.sql will
13 -- call APIs from this package.
14 ---
15 --- NOTES
16 ---
17 --- HISTORY
18 ---
19 --- 05-Oct-2004 ankgoel bug#3933075 Created
20 --- 1-Mar-2005 sawu bug#4214181 Modified api call
21 --- 29-Mar-2005 kyadamak bug#4268439(fixed Upgrade_Role_To_Tabs performance)
22 ---===========================================================================
23
24
25 /*******************************************************************************************************
26 -- Upgrade for BSC 5.2 Roled Based Security Scorecard
27 -- This function will to insert initial grant role for all existing scorecards if a flag in bsc_sys_init table does not exist.
28 -- It will grant Designer Role (update Acess) to all design user (user with BSC_Manager or BSC_DESIGNER)
29 -- After this funciton executed, then it will set a flag in bsc_sys_init table, so it won't run next time.
30 *********************************************************************************************************/
31 FUNCTION Upgrade_Role_To_Tabs
32 (
33 x_error_msg OUT NOCOPY VARCHAR2
34 ) RETURN BOOLEAN IS
35 l_user_name VARCHAR2(256);
36 l_success VARCHAR2(5);
37 l_errorcode NUMBER;
38 l_msg_count NUMBER;
39 l_msg_data VARCHAR2(2000);
40 x_return_status VARCHAR2(5000);
41 x_msg_count NUMBER;
42 x_msg_data VARCHAR2(5000);
43 l_property_code bsc_sys_init.property_code%TYPE ;
44 l_property_value bsc_sys_init.property_value%TYPE ;
45 l_role_name VARCHAR2(256);
46 l_object_name VARCHAR2(256);
47 l_object_id NUMBER;
48 l_instance_type FND_GRANTS.instance_type%TYPE;
49 l_grantee_type FND_GRANTS.grantee_key%TYPE;
50 l_bsc_program_name FND_GRANTS.program_name%TYPE;
51 l_tab_id FND_GRANTS.instance_pk1_value%TYPE;
52 l_grant_guid FND_GRANTS.grant_guid%TYPE;
53 l_index NUMBER := 1;
54
55 TYPE integer_table_type IS TABLE OF NUMBER INDEX BY binary_integer;
56 l_all_tabs_tbl integer_table_type;
57
58 CURSOR c_BscUserPool IS
59 SELECT distinct usr.user_name
60 FROM fnd_user_resp_groups ur,
61 fnd_responsibility r,
62 fnd_user usr
63 WHERE ur.responsibility_id = r.responsibility_id
64 AND usr.user_id = ur.user_id
65 AND ur.responsibility_application_id = r.application_id
66 AND r.application_id = 271
67 AND r.responsibility_key IN ('BSC_DESIGNER' ,'BSC_Manager')
68 AND SYSDATE BETWEEN usr.Start_Date AND NVL(usr.End_Date, SYSDATE)
69 AND SYSDATE BETWEEN r.Start_Date AND NVL(r.End_Date, SYSDATE)
70 AND SYSDATE BETWEEN ur.Start_Date AND NVL(ur.End_Date, SYSDATE);
71
72
73 CURSOR c_all_tabs IS
74 SELECT tab_id
75 FROM bsc_tabs_b;
76
77 BEGIN
78 l_property_code := 'GRANT_ROLE_TAB';
79
80 -- check bsc_sys_init for flag
81 IF NOT BSC_UPDATE_UTIL.Get_Init_Variable_Value(l_property_code, l_property_value) THEN
82 RAISE FND_API.G_EXC_ERROR;
83 END IF;
84 --dbms_output.put_line(substr('Value of l_property_value='||l_property_value,1,255));
85
86 IF (l_property_value IS NULL) THEN -- run for the first time
87 l_role_name := 'BSC_SCORECARD_DESIGNER';
88 l_object_name := 'BSC_TAB';
89 l_instance_type := 'INSTANCE';
90 l_grantee_type := 'USER';
91 l_bsc_program_name := 'BSC_PMD_GRANTS';
92
93 SELECT OBJECT_ID into l_object_id FROM FND_OBJECTS WHERE OBJ_NAME = l_object_name;
94
95 -- clear all records from fnd_grants
96 DELETE FROM FND_GRANTS WHERE OBJECT_ID = to_char(l_object_id)
97 AND PROGRAM_NAME = 'BSC_PMD_GRANTS';
98
99 FOR all_tabs IN c_all_tabs LOOP
100 l_all_tabs_tbl( l_index) := all_tabs.tab_id;
101 l_index := l_index + 1;
102 END LOOP;
103
104 FOR user_pool IN c_BscUserPool LOOP
105 l_user_name := user_pool.user_name;
106 FOR i IN 1..l_all_tabs_tbl.COUNT LOOP
107 l_tab_id := l_all_tabs_tbl(i);
108
109 FND_GRANTS_PKG.GRANT_FUNCTION
110 (
111 p_api_version => 1.0
112 ,p_menu_name => l_role_name
113 ,p_object_name => l_object_name
114 ,p_instance_type => l_instance_type
115 ,p_instance_pk1_value => l_tab_id
116 ,p_grantee_type => l_grantee_type
117 ,p_grantee_key => l_user_name
118 ,p_start_date => SYSDATE
119 ,p_end_date => NULL
120 ,p_program_name => l_bsc_program_name
121 ,x_grant_guid => l_grant_guid
122 ,x_success => l_success
123 ,x_errorcode => l_errorcode
124 );
125 IF (l_success <> FND_API.G_TRUE) THEN
126 --DBMS_OUTPUT.PUT_LINE('BSC_UPGRADES.Update_Role_To_Tabs Failed: at FND_GRANTS_PKG.GRANT_FUNCTION );
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128 END IF;
129 END LOOP; -- end loop for users
130 END LOOP; -- end loop for tabs
131
132 -- register the flag, so it won't run next time
133 IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value(l_property_code, '0') THEN
134 RAISE FND_API.G_EXC_ERROR;
135 END IF;
136 END IF;
137
138 RETURN TRUE;
139
140 EXCEPTION
141 WHEN OTHERS THEN
142 ROLLBACK;
143
144 IF(c_BscUserPool%ISOPEN) THEN
145 CLOSE c_BscUserPool;
146 END IF;
147
148 IF(c_all_tabs%ISOPEN) THEN
149 CLOSE c_all_tabs;
150 END IF;
151
152 FND_MSG_PUB.Count_And_Get
153 ( p_count => l_msg_count
154 ,p_data => l_msg_data
155 );
156 IF (l_msg_data IS NULL) THEN
157 l_msg_data := SQLERRM;
158 end if;
159 x_error_msg := l_msg_data;
160 RETURN FALSE;
161 END Upgrade_Role_To_Tabs;
162
163 /*******************************************************************************************************
164 -- Upgrade for BSC 5.2 RUP Custom View Region Supporting DBI Pge
165 -- This function will loop through all existing custom view, and created a form function for earch vew
166 -- Those form functions will be used in Page Definer for DBI Page
167 *********************************************************************************************************/
168 FUNCTION Upgrade_Tab_View_Functions
169 (
170 x_error_msg OUT NOCOPY VARCHAR2
171 ) RETURN BOOLEAN IS
172 l_msg_count NUMBER;
173 l_msg_data VARCHAR2(2000);
174 x_return_status VARCHAR2(5000);
175 x_msg_count NUMBER;
176 x_msg_data VARCHAR2(5000);
177 l_property_code bsc_sys_init.property_code%TYPE ;
178 l_property_value bsc_sys_init.property_value%TYPE ;
179 l_tab_id NUMBER;
180 l_tab_view_id NUMBER;
181 l_name bsc_tab_views_vl.name%TYPE;
182 l_help bsc_tab_views_vl.help%TYPE;
183 l_function_id NUMBER;
184
185 CURSOR c_all_tab_views IS
186 SELECT tab_id, tab_view_id, name, help
187 FROM bsc_tab_views_vl v
188 WHERE v.tab_view_id > 1;
189
190 BEGIN
191 l_property_code := 'TAB_VIEW_FUNCS';
192
193 -- check bsc_sys_init for flag
194 IF NOT BSC_UPDATE_UTIL.Get_Init_Variable_Value(l_property_code, l_property_value) THEN
195 RAISE FND_API.G_EXC_ERROR;
196 END IF;
197 --dbms_output.put_line(substr('Value of l_property_value='||l_property_value,1,255));
198
199 IF (l_property_value IS NULL) THEN -- run for the first time
200 FOR all_tab_views IN c_all_tab_views LOOP
201 l_tab_id := all_tab_views.tab_id;
202 l_tab_view_id := all_tab_views.tab_view_id;
203 l_name := all_tab_views.name;
204 l_help := all_tab_views.help;
205
206 -- Enh 3934298, for each new custom view will create/update a form function to use in DBI
207 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_function(p_tab_id => l_tab_id,
208 p_tab_view_id => l_tab_view_id,
209 p_name => l_name,
210 p_description => l_help,
211 x_function_id => l_function_id,
212 x_return_status => x_return_status ,
213 x_msg_count => x_msg_count,
214 x_msg_data => x_msg_data);
215
216 END LOOP; -- end loop for tab views
217
218 -- register the flag, so it won't run next time
219 IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value(l_property_code, '0') THEN
220 RAISE FND_API.G_EXC_ERROR;
221 END IF;
222 END IF;
223
224 RETURN TRUE;
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 ROLLBACK;
229
230 IF(c_all_tab_views%ISOPEN) THEN
231 CLOSE c_all_tab_views;
232 END IF;
233
234 FND_MSG_PUB.Count_And_Get
235 ( p_count => l_msg_count
236 ,p_data => l_msg_data
237 );
238 IF (l_msg_data IS NULL) THEN
239 l_msg_data := SQLERRM;
240 end if;
241 x_error_msg := l_msg_data;
242 RETURN FALSE;
243 END Upgrade_Tab_View_Functions;
244
245 /*******************************************************************************************************
246 -- Upgrade for BSC 5.2 RUP Custom View Region Supporting DBI Pge
247 -- This function will loop through all entries in BSC_TAB_VIEW_KPI_TL and add an entry to BSC_TAB_VIEW_LABEL_B/TL
248 *********************************************************************************************************/
249 FUNCTION Upgrade_Tab_View_Kpi_Labels
250 (
251 x_error_msg OUT NOCOPY VARCHAR2
252 ) RETURN BOOLEAN IS
253 l_msg_count NUMBER;
254 l_msg_data VARCHAR2(2000);
255 x_return_status VARCHAR2(5000);
256 x_msg_count NUMBER;
257 x_msg_data VARCHAR2(5000);
258 l_property_code bsc_sys_init.property_code%TYPE ;
259 l_property_value bsc_sys_init.property_value%TYPE ;
260 l_label_id NUMBER;
261 l_count NUMBER;
262
263 CURSOR c_all_tab_view_kpis IS
264 select distinct tab_id, tab_view_id, indicator, text_flag, left_position,
265 top_position, width, height, font_size, font_style, font_color
266 from bsc_tab_view_kpi_tl;
267 BEGIN
268 l_property_code := 'TAB_VIEW_KPIS';
269
270 -- check bsc_sys_init for flag
271 IF NOT BSC_UPDATE_UTIL.Get_Init_Variable_Value(l_property_code, l_property_value) THEN
272 RAISE FND_API.G_EXC_ERROR;
273 END IF;
274 --dbms_output.put_line(substr('Value of l_property_value='||l_property_value,1,255));
275
276 IF (l_property_value IS NULL) THEN -- run for the first time
277 FOR all_tab_view_kpis IN c_all_tab_view_kpis LOOP
278 -- check if it exists already
279 select count(*) into l_count from bsc_tab_view_labels_b
280 where tab_id = all_tab_view_kpis.tab_id and tab_view_id=all_tab_view_kpis.tab_view_id
281 and label_type = BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_kpi and link_id = all_tab_view_kpis.indicator;
282
283 IF (l_count = 0) THEN
284 --dbms_output.put_line(substr('Value of all_tab_view_kpis.tab_id='||all_tab_view_kpis.tab_id,1,255));
285 --dbms_output.put_line(substr('Value of all_tab_view_kpis.tab_view_id='||all_tab_view_kpis.tab_view_id,1,255));
286 --dbms_output.put_line(substr('Value of all_tab_view_kpis.indicator='||all_tab_view_kpis.indicator,1,255));
287
288 -- find the next unique label_id
289 select DECODE(max(LABEL_ID),NULL,0,max(LABEL_ID)+1) into l_label_id
290 from bsc_tab_view_labels_b
291 where tab_id = all_tab_view_kpis.tab_id and tab_view_id=all_tab_view_kpis.tab_view_id;
292
293 -- Add specified entry for objectives to BSC_TAB_VIEW_LABELS_PKG
294 -- position and color info is stored in BSC_TAB_VIEW_KPI_TL entries
295 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_kpi_label(
296 p_tab_id => all_tab_view_kpis.tab_id
297 ,p_tab_view_id => all_tab_view_kpis.tab_view_id
298 ,p_object_id => l_label_id
299 ,p_text_flag => all_tab_view_kpis.text_flag
300 ,p_label_text => BSC_UPGRADES_GENERIC.c_kpi_label
301 ,p_font_size => all_tab_view_kpis.font_size
302 ,p_font_color => all_tab_view_kpis.font_color
303 ,p_font_style => all_tab_view_kpis.font_style
304 ,p_left => all_tab_view_kpis.left_position
305 ,p_top => all_tab_view_kpis.top_position
306 ,p_width => all_tab_view_kpis.width
307 ,p_height => all_tab_view_kpis.height
308 ,p_kpi_id => all_tab_view_kpis.indicator
309 ,p_function_id => BSC_UPGRADES_GENERIC.c_function_id
310 ,x_return_status => x_return_status
311 ,x_msg_count => x_msg_count
312 ,x_msg_data => x_msg_data
313 );
314
315 --dbms_output.put_line(substr('Value of x_return_status='||x_return_status,1,255));
316 --dbms_output.put_line(substr('Value of x_msg_count='||x_msg_count,1,255));
317 --dbms_output.put_line(substr('Value of x_msg_data='||x_msg_data,1,255));
318
319 END IF;
320
321 END LOOP; -- end loop for tab views
322
323 -- register the flag, so it won't run next time
324 IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value(l_property_code, '0') THEN
325 RAISE FND_API.G_EXC_ERROR;
326 END IF;
327 END IF;
328
329 RETURN TRUE;
330
331 EXCEPTION
332 WHEN OTHERS THEN
333 ROLLBACK;
334
335 IF(c_all_tab_view_kpis%ISOPEN) THEN
336 CLOSE c_all_tab_view_kpis;
337 END IF;
338
339 FND_MSG_PUB.Count_And_Get
340 ( p_count => l_msg_count
341 ,p_data => l_msg_data
342 );
343 IF (l_msg_data IS NULL) THEN
344 l_msg_data := SQLERRM;
345 end if;
346 x_error_msg := l_msg_data;
347 RETURN FALSE;
348 END Upgrade_Tab_View_Kpi_Labels;
349
350 END BSC_UPGRADES_GENERIC;
[Home] [Help]