1 PACKAGE BODY BSC_COMMON_DIMENSIONS_PUB AS
2 /* $Header: BSCPLIBB.pls 120.3.12000000.1 2007/07/17 07:44:12 appldev noship $ */
3
4
5 -- The following API saves LIST BUTTON (Common Dimension) configuration
6 -- for a particular SCORECARD.
7 -- INPUT :
8 -- p_new_list_config A semicolon(;) seperated values of common dimension objects
9 -- that have to be saved.
10 -- p_old_list_config A semicolon(;) seperated values of common dimension objects
11 -- that were saved. When there are no common dimensions saved,
12 -- this is empty.
13 -- NOTE:1. Each common dimension object record contains a commma seperated list of the following
14 -- properties in order:
15 -- (dim_level_index, dim_level_id, parent_level_index, parent_level_id)
16 -- And then each such dimension object record is seperated by a semicolon.
17 -- 2. When we want to delete existing common dimension objects,
18 -- p_new_list_config should be an empty string.
19 -- 3. When there is no common dimension configuration, the p_old_list_config is empty
20
21 PROCEDURE save_list_button_config
22 (p_tab_id IN NUMBER
23 ,p_new_list_config IN VARCHAR2
24 ,p_old_list_config IN VARCHAR2
25 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
26 ,x_return_status OUT NOCOPY VARCHAR2
27 ,x_msg_count OUT NOCOPY NUMBER
28 ,x_msg_data OUT NOCOPY VARCHAR2
29 ) IS
30
31 BEGIN
32 IF (p_tab_id IS NOT NULL) THEN
33
34 BSC_COMMON_DIMENSIONS_PVT.reset_dim_default_value
35 (
36 p_Tab_Id => p_tab_id
37 ,x_return_status => x_return_status
38 ,x_msg_count => x_msg_count
39 ,x_msg_data => x_msg_data
40 );
41 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS ) THEN
42 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
43 END IF;
44
45 BSC_COMMON_DIMENSIONS_PVT.delete_common_dimensions(
46 p_tab_id => p_tab_id
47 ,p_commit => FND_API.G_FALSE
48 ,x_return_status => x_return_status
49 ,x_msg_count => x_msg_count
50 ,x_msg_data => x_msg_data
51 );
52
53 IF (p_new_list_config IS NOT NULL) THEN
54 BSC_COMMON_DIMENSIONS_PVT.insert_common_dimensions(
55 p_tab_id => p_tab_id
56 ,p_new_list_config => p_new_list_config
57 ,p_commit => FND_API.G_FALSE
58 ,x_return_status => x_return_status
59 ,x_msg_count => x_msg_count
60 ,x_msg_data => x_msg_data
61 );
62
63 END IF;
64
65 BSC_COMMON_DIMENSIONS_PUB.update_user_list_access(
66 p_tab_id => p_tab_id
67 ,p_new_list_config => p_new_list_config
68 ,p_old_list_config => p_old_list_config
69 ,p_commit => FND_API.G_FALSE
70 ,x_return_status => x_return_status
71 ,x_msg_count => x_msg_count
72 ,x_msg_data => x_msg_data
73 );
74
75
76 --VALIDATE common dimensions
77 BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels(
78 p_commit => FND_API.G_FALSE
79 ,p_Tab_Id => p_tab_id
80 ,x_return_status => x_return_status
81 ,x_msg_count => x_msg_count
82 ,x_msg_data => x_msg_data
83 );
84
85
86
87 END IF;
88 EXCEPTION
89 WHEN FND_API.G_EXC_ERROR THEN
90 FND_MSG_PUB.Count_And_Get
91 ( p_encoded => FND_API.G_FALSE
92 , p_count => x_msg_count
93 , p_data => x_msg_data
94 );
95
96 x_return_status := FND_API.G_RET_STS_ERROR;
97 RAISE;
98 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
99 FND_MSG_PUB.Count_And_Get
100 ( p_encoded => FND_API.G_FALSE
101 , p_count => x_msg_count
102 , p_data => x_msg_data
103 );
104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
105
106 RAISE;
107 WHEN NO_DATA_FOUND THEN
108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
109 IF (x_msg_data IS NOT NULL) THEN
110 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PUB.save_list_button_config ';
111 ELSE
112 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PUB.save_list_button_config ';
113 END IF;
114
115 RAISE;
116 WHEN OTHERS THEN
117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
118 IF (x_msg_data IS NOT NULL) THEN
119 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PUB.save_list_button_config ';
120 ELSE
121 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PUB.save_list_button_config ';
122 END IF;
123
124 RAISE;
125 END save_list_button_config;
126
127
128
129
130 -- The following API provides the access to common dimensions.
131 --
132 -- INPUT :
133 -- p_new_list_config A semicolon(;) seperated values of common dimension objects
134 -- that have to be saved.
135 -- p_old_list_config A semicolon(;) seperated values of common dimension objects
136 -- that were saved. When there are no common dimensions saved,
137 -- this is empty.
138 -- NOTE: Each common dimension object record contains a commma seperated list of the following
139 -- properties in order:
140 -- (dim_level_index, dim_level_id, parent_level_index, parent_level_id)
141
142
143 PROCEDURE update_user_list_access
144 (
145 p_tab_id IN NUMBER
146 ,p_new_list_config IN VARCHAR2
147 ,p_old_list_config IN VARCHAR2
148 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
149 ,x_return_status OUT NOCOPY VARCHAR2
150 ,x_msg_count OUT NOCOPY NUMBER
151 ,x_msg_data OUT NOCOPY VARCHAR2
152 ) IS
153
154 CURSOR c_new_list_config IS
155 SELECT *
156 FROM bsc_sys_com_dim_levels
157 WHERE tab_id = p_tab_id
158 ORDER BY dim_level_index;
159
160 CURSOR c_tab_responsibilities IS
161 SELECT responsibility_id
162 FROM BSC_USER_TAB_ACCESS
163 WHERE tab_id = p_tab_id
164 AND (end_date IS NULL OR end_date >= SYSDATE);
165
166 CURSOR c_tab_kpis IS
167 SELECT DISTINCT kpi_measure_id, indicator
168 FROM bsc_kpi_analysis_measures_b
169 WHERE indicator IN (SELECT DISTINCT ti.indicator
170 FROM bsc_tab_indicators ti
171 WHERE ti.tab_id = p_tab_id);
172
173 l_new_dim_obj_recs BSC_UTILITY.varchar_tabletype;
174 l_new_dim_obj_cnt NUMBER;
175 l_new_dim_props BSC_UTILITY.varchar_tabletype;
176 l_new_cnt NUMBER;
177
178 l_old_dim_obj_recs BSC_UTILITY.varchar_tabletype;
179 l_old_dim_obj_cnt NUMBER;
180 l_old_dim_props BSC_UTILITY.varchar_tabletype;
181 l_old_cnt NUMBER;
182
183 l_index NUMBER;
184 l_user_id NUMBER;
185 l_login_id NUMBER;
186 l_dim_level_index NUMBER;
187
188 BEGIN
189 IF (p_tab_id IS NOT NULL) THEN
190
191 IF (p_old_list_config IS NULL) THEN
192 l_old_dim_obj_cnt := 0;
193 ELSE
194 BSC_UTILITY.Parse_String(
195 p_List => p_old_list_config,
196 p_Separator => ';',
197 p_List_Data => l_old_dim_obj_recs,
198 p_List_number => l_old_dim_obj_cnt
199 );
200 END IF;
201
202 IF (p_new_list_config IS NULL) THEN
203 l_new_dim_obj_cnt := 0;
204 ELSE
205 BSC_UTILITY.Parse_String(
206 p_List => p_new_list_config,
207 p_Separator => ';',
208 p_List_Data => l_new_dim_obj_recs,
209 p_List_number => l_new_dim_obj_cnt
210 );
211 END IF;
212
213 IF (l_old_dim_obj_cnt = l_new_dim_obj_cnt) THEN
214 RETURN;
215 END IF;
216
217
218 BSC_COMMON_DIMENSIONS_PUB.change_prototype_flag(
219 p_prototype_flag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color,
220 p_tab_id => p_tab_id,
221 p_dim_level_id => NULL,
222 p_commit => p_commit,
223 x_return_status => x_return_status,
224 x_msg_count => x_msg_count,
225 x_msg_data => x_msg_data
226 );
227
228 FOR cd IN c_tab_kpis LOOP
229
230 IF (cd.indicator IS NOT NULL AND cd.kpi_measure_id IS NOT NULL) THEN
231 BSC_KPI_COLOR_PROPERTIES_PUB.Change_Prototype_Flag
232 ( p_objective_id => cd.indicator
233 , p_kpi_measure_id => cd.kpi_measure_id
234 , p_prototype_flag => 7
235 , x_return_status => x_return_status
236 , x_msg_count => x_msg_count
237 , x_msg_data => x_msg_data
238 );
239 END IF;
240 END LOOP;
241
242 IF (l_old_dim_obj_cnt > l_new_dim_obj_cnt) THEN
243 l_index := l_new_dim_obj_cnt;
244 ELSE
245 l_index := l_old_dim_obj_cnt;
246 END IF;
247 BSC_COMMON_DIMENSIONS_PVT.delete_user_list_access (
248 p_tab_id => p_tab_id
249 ,p_dim_level_index => l_index
250 ,p_commit => FND_API.G_FALSE
251 ,x_return_status => x_return_status
252 ,x_msg_count => x_msg_count
253 ,x_msg_data => x_msg_data
254 );
255
256 --DELETE FROM BSC_USER_LIST_ACCESS WHERE tab_id = p_tab_id AND DIM_LEVEL_INDEX >= (l_index-1);
257
258 l_user_id := fnd_global.USER_ID;
259 l_login_id := fnd_global.LOGIN_ID;
260 FOR cd IN c_tab_responsibilities LOOP
261 FOR i IN 1..l_new_dim_obj_cnt LOOP
262 l_new_cnt := 0;
263 BSC_UTILITY.Parse_String(
264 p_List => l_new_dim_obj_recs(i),
265 p_Separator => ',',
266 p_List_Data => l_new_dim_props,
267 p_List_number => l_new_cnt);
268
269 IF (l_new_cnt > 0) THEN
270
271 l_dim_level_index := TO_NUMBER(l_new_dim_props(1));
272
273 IF (l_dim_level_index IS NOT NULL) THEN
274 IF (l_dim_level_index > l_index-1) THEN
278 ,p_dim_level_index => l_dim_level_index
275 BSC_COMMON_DIMENSIONS_PVT.insert_user_list_access(
276 p_responsibility_id => cd.responsibility_id
277 ,p_tab_id => p_tab_id
279 ,p_dim_level_value => 0
280 ,p_creation_date => SYSDATE
281 ,p_created_by => l_user_id
282 ,p_last_update_date => SYSDATE
283 ,p_last_updated_by => l_login_id
284 ,p_last_update_login => NULL
285 ,p_commit => FND_API.G_FALSE
286 ,x_return_status => x_return_status
287 ,x_msg_count => x_msg_count
288 ,x_msg_data => x_msg_data
289 );
290 END IF;
291
292 --INSERT INTO bsc_user_list_access(responsibility_id,tab_id,dim_level_index,dim_level_value,creation_date,created_by,last_update_date,last_updated_by,last_update_login)
293 --VALUES (cd.responsibility_id, p_tab_id, l_dim_level_index, 0, SYSDATE, l_user_id,SYSDATE, l_login_id, null);
294
295 END IF;
296 END IF;
297 END LOOP;
298 END LOOP;
299 END IF;
300 EXCEPTION
301 WHEN FND_API.G_EXC_ERROR THEN
302 FND_MSG_PUB.Count_And_Get
303 ( p_encoded => FND_API.G_FALSE
304 , p_count => x_msg_count
305 , p_data => x_msg_data
306 );
307
308 x_return_status := FND_API.G_RET_STS_ERROR;
309 RAISE;
310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
311 FND_MSG_PUB.Count_And_Get
312 ( p_encoded => FND_API.G_FALSE
313 , p_count => x_msg_count
314 , p_data => x_msg_data
315 );
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
317
318 RAISE;
319 WHEN NO_DATA_FOUND THEN
320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
321 IF (x_msg_data IS NOT NULL) THEN
322 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PUB.update_user_list_access ';
323 ELSE
324 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PUB.update_user_list_access ';
325 END IF;
326
327 RAISE;
328 WHEN OTHERS THEN
329 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
330 IF (x_msg_data IS NOT NULL) THEN
331 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PUB.update_user_list_access ';
332 ELSE
333 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PUB.update_user_list_access ';
334 END IF;
335
336 RAISE;
337 END update_user_list_access;
338
339
340 -- The following API sets a particular prototype flag to all the
341 -- indicators under a scorecard, if the input parameter p_tab_id
342 -- is not null. If the input parameter p_dim_level_id is also not null,
343 -- then the prototype flag is set to all the indicators that contain
344 -- the dimension level in that scorecard.
345
346 PROCEDURE change_prototype_flag
347 (
348 p_prototype_flag IN NUMBER
349 ,p_tab_id IN NUMBER
350 ,p_dim_level_id IN NUMBER
351 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
352 ,x_return_status OUT NOCOPY VARCHAR2
353 ,x_msg_count OUT NOCOPY NUMBER
354 ,x_msg_data OUT NOCOPY VARCHAR2
355
356 ) IS
357
358 --CURSOR for all indicators in a tab
359 CURSOR c_inds IS
360 SELECT DISTINCT ti.indicator
361 FROM bsc_tab_indicators ti
362 WHERE ti.tab_id = p_tab_id;
363 --CURSOR for indicators that contain particular dim level
364 CURSOR c_inds_levels IS
365 SELECT DISTINCT ti.indicator
366 FROM bsc_tab_indicators ti, bsc_sys_dim_levels_b sd, bsc_kpi_dim_levels_b kd
367 WHERE ti.tab_id = p_tab_id AND sd.dim_level_id = p_dim_level_id AND
368 kd.indicator = ti.indicator AND kd.level_table_name = sd.level_table_name;
369
370 BEGIN
371 IF (p_tab_id IS NOT NULL) THEN
372
373 IF (p_dim_level_id IS NOT NULL) THEN
374 FOR cd IN c_inds_levels LOOP
375 BSC_DESIGNER_PVT.ActionFlag_Change(
376 x_indicator => cd.indicator,
377 x_newflag => p_prototype_flag
378 );
379 END LOOP;
380
381 ELSE
382 FOR cd IN c_inds LOOP
383 BSC_DESIGNER_PVT.ActionFlag_Change(
384 x_indicator => cd.indicator,
385 x_newflag => p_prototype_flag
386 );
387 END LOOP;
388 END IF;
389
390 END IF;
391
392 EXCEPTION
393 WHEN FND_API.G_EXC_ERROR THEN
394 FND_MSG_PUB.Count_And_Get
395 ( p_encoded => FND_API.G_FALSE
396 , p_count => x_msg_count
397 , p_data => x_msg_data
398 );
399
400 x_return_status := FND_API.G_RET_STS_ERROR;
401 RAISE;
402 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
403 FND_MSG_PUB.Count_And_Get
404 ( p_encoded => FND_API.G_FALSE
405 , p_count => x_msg_count
406 , p_data => x_msg_data
407 );
408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
409
410 RAISE;
411 WHEN NO_DATA_FOUND THEN
412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
413 IF (x_msg_data IS NOT NULL) THEN
414 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PUB.change_prototype_flag ';
415 ELSE
416 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PUB.change_prototype_flag ';
417 END IF;
418
419 RAISE;
420 WHEN OTHERS THEN
421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
422 IF (x_msg_data IS NOT NULL) THEN
423 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PUB.change_prototype_flag ';
424 ELSE
425 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PUB.change_prototype_flag ';
426 END IF;
427
428 RAISE;
429 END change_prototype_flag;
430
431 END BSC_COMMON_DIMENSIONS_PUB;
[Home] [Help]