[Home] [Help]
PACKAGE BODY: APPS.BSC_SIMULATION_VIEW_PVT
Source
1 PACKAGE BODY BSC_SIMULATION_VIEW_PVT AS
2 /* $Header: BSCSIMVB.pls 120.6.12000000.1 2007/07/17 07:44:29 appldev noship $ */
3
4 FUNCTION Get_Default_Node
5 (
6 p_indicator IN BSC_KPIS_B.indicator%TYPE
7 )RETURN NUMBER IS
8 l_node_id BSC_SYS_DATASETS_VL.dataset_id%TYPE;
9 BEGIN
10
11 SELECT property_value
12 INTO l_node_id
13 FROM bsc_kpi_properties
14 WHERE indicator = p_indicator
15 AND property_code = BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID;
16
17 RETURN l_node_id;
18
19 EXCEPTION
20 WHEN OTHERS THEN
21 RETURN NULL;
22 END Get_Default_Node;
23
24
25
26 FUNCTION Get_Format
27 (
28 p_format_Id IN VARCHAR2
29 ) RETURN VARCHAR2 IS
30
31 l_attribute7 AK_REGION_ITEMS_VL.attribute7%TYPE;
32 l_number_format v$nls_parameters.value%TYPE;
33 l_replace_format VARCHAR2(10);
34 l_Sql VARCHAR2(32000);
35 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
36 l_id NUMBER;
37 l_name VARCHAR(100);
38 l_grouping_separator VARCHAR2(10);
39 l_decimal_separator VARCHAR2(10);
40
41
42 BEGIN
43
44 l_Sql:=' SELECT format_id, '||
45 ' name, '||
46 ' REPLACE(FORMAT,''$'',( '||
47 ' SELECT NVL(PROPERTY_VALUE,''$'') NLS_CURRENCY '||
48 ' FROM BSC_SYS_INIT '||
49 ' WHERE PROPERTY_CODE =''NLS_CURRENCY'') '||
50 ' ) format '||
51 'FROM ( '||
52 ' SELECT format_id, '||
53 ' name,REPLACE(DECODE(dotpos,0,REPLACE(format,'','',:1), '||
54 ' REPLACE(SUBSTR(format,0,dotpos-1),'','',:2) || REPLACE(SUBSTR(format,dotpos),''.'',:3)),''#'',''9'')FORMAT '||
55 ' FROM (SELECT format_id,name,format,INSTR(format,''||l_replace_format||'') dotpos '||
56 ' FROM bsc_sys_formats)) '||
57 ' WHERE format_id ='||p_format_Id;
58
59 SELECT value
60 INTO l_number_format
61 FROM v$nls_parameters
62 WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
63
64 IF(l_number_format IS NOT NULL) THEN
65 l_decimal_separator := SUBSTR(TRIM(l_number_format),0,1);
66 l_grouping_separator := SUBSTR(TRIM(l_number_format),2,2);
67
68 ELSE
69 l_grouping_separator := BSC_SIMULATION_VIEW_PVT.C_COMMA;
70 l_decimal_separator := BSC_SIMULATION_VIEW_PVT.C_DOT;
71 END IF;
72
73 OPEN l_cursor FOR l_sql USING l_grouping_separator,l_grouping_separator,
74 l_decimal_separator;
75 LOOP
76 FETCH l_cursor INTO l_id,l_name,l_attribute7 ;
77 EXIT WHEN l_cursor%NOTFOUND;
78 END LOOP;
79
80 --PMVs requirement is to replace , with G and . with D
81
82 l_attribute7 := REPLACE(l_attribute7,BSC_SIMULATION_VIEW_PVT.C_COMMA,'G');
83 l_attribute7 := REPLACE(l_attribute7,BSC_SIMULATION_VIEW_PVT.C_DOT,'D');
84
85 RETURN l_attribute7;
86
87 END Get_Format;
88
89
90 FUNCTION Get_dup_dataset_id
91 (
92 p_tarInd IN NUMBER
93 , p_attribute_code IN AK_REGION_ITEMS_VL.attribute_code%TYPE
94 )RETURN NUMBER IS
95
96 l_attribute_code AK_REGION_ITEMS_VL.attribute_code%TYPE;
97 l_region_Code AK_REGION_ITEMS_VL.region_code%TYPE;
98 l_Actual_Data_Source BIS_INDICATORS.ACTUAL_DATA_SOURCE%TYPE;
99 l_short_name AK_REGION_ITEMS_VL.attribute2%TYPE;
100 l_dataset_id BSC_SYS_DATASETS_VL.dataset_id%TYPE;
101 BEGIN
102 SELECT short_name
103 INTO l_region_Code
104 FROM bsc_kpis_b
105 WHERE config_Type =BSC_SIMULATION_VIEW_PUB.c_TYPE
106 AND indicator = p_tarInd;
107
108 SELECT dat.dataset_id
109 INTO l_dataset_id
110 FROM ak_region_items_vl ak
111 ,bis_indicators dat
112 WHERE dat.short_name = ak.attribute2
113 AND ak.region_code =l_region_Code
114 AND ak.attribute1 ='MEASURE_NOTARGET'
115 AND ak.attribute_code = p_attribute_code;
116
117 RETURN l_dataset_id;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 RETURN NULL;
122 END Get_dup_dataset_id;
123
124
125 PROCEDURE Init_Sim_Tables_Array
126 (
127 p_copy_Ak_Tables IN VARCHAR
128 ,x_Table_Number OUT NOCOPY NUMBER
129 ,x_kpi_metadata_tables OUT NOCOPY BSC_DESIGNER_PVT.t_kpi_metadata_tables
130 )
131 IS
132 BEGIN
133 x_Table_Number := 0;
134
135 x_Table_Number := x_Table_Number + 1;
136 x_kpi_metadata_tables(x_Table_Number).table_name := 'BSC_SYS_IMAGES_MAP_TL';
137 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_SYSTEM_TABLE ;
138 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_SOURCE_CODE;
139 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
140 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.YES;
141
142
143 x_Table_Number := x_Table_Number + 1;
144 x_kpi_metadata_tables(x_Table_Number).table_name := 'BSC_SYS_IMAGES';
145 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_SYSTEM_TABLE;
146 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_IMAGE_ID;
147 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
148 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.NO;
149
150
151 x_Table_Number := x_Table_Number + 1;
152 x_kpi_metadata_tables(x_Table_Number).table_name := 'BSC_TAB_VIEW_LABELS_B';
153 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_TAB_VIEW_TABLE ;
154 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_TAB_VIEW ;
155 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
156 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.NO;
157
158 x_Table_Number := x_Table_Number + 1;
159 x_kpi_metadata_tables(x_Table_Number).table_name := 'BSC_TAB_VIEW_LABELS_TL';
160 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_TAB_VIEW_TABLE;
161 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_TAB_VIEW;
162 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
163 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.YES;
164
165 x_Table_Number := x_Table_Number + 1;
166 x_kpi_metadata_tables(x_Table_Number).table_name := 'BSC_KPI_TREE_NODES_B';
167 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_KPI_TABLE ;
168 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_INDICATOR ;
169 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
170 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.NO;
171
172
173 x_Table_Number := x_Table_Number + 1;
174 x_kpi_metadata_tables(x_Table_Number).table_name := 'BSC_KPI_TREE_NODES_TL';
175 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_KPI_TABLE ;
176 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_INDICATOR ;
177 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
178 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.YES;
179
180 IF(p_copy_Ak_Tables=FND_API.G_TRUE)THEN
181
182 x_Table_Number := x_Table_Number + 1;
183 x_kpi_metadata_tables(x_Table_Number).table_name := 'AK_REGIONS';
184 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
185 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
186 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
187 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.NO;
188
189
190 x_Table_Number := x_Table_Number + 1;
191 x_kpi_metadata_tables(x_Table_Number).table_name := 'AK_REGIONS_TL';
192 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
193 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
194 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
195 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.YES;
196
197
198 x_Table_Number := x_Table_Number + 1;
199 x_kpi_metadata_tables(x_Table_Number).table_name := 'AK_REGION_ITEMS';
200 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
201 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
202 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
203 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.NO;
204
205
206 x_Table_Number := x_Table_Number + 1;
207 x_kpi_metadata_tables(x_Table_Number).table_name := 'AK_REGION_ITEMS_TL';
208 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
209 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
210 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
211 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.YES;
212
213 x_Table_Number := x_Table_Number + 1;
214 x_kpi_metadata_tables(x_Table_Number).table_name := 'AK_CUSTOMIZATIONS';
215 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
216 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
217 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.NO;
218
219
220 x_Table_Number := x_Table_Number + 1;
221 x_kpi_metadata_tables(x_Table_Number).table_name := 'AK_CUSTOMIZATIONS_TL';
222 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
223 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
224 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
225 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.YES;
226
227
228 x_Table_Number := x_Table_Number + 1;
229 x_kpi_metadata_tables(x_Table_Number).table_name := 'AK_CUSTOM_REGIONS';
230 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
231 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
232 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
233 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.NO;
234
235
236 x_Table_Number := x_Table_Number + 1;
237 x_kpi_metadata_tables(x_Table_Number).table_name := 'AK_CUSTOM_REGIONS_TL';
238 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
239 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
240 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
241 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.YES;
242
243 x_Table_Number := x_Table_Number + 1;
244 x_kpi_metadata_tables(x_Table_Number).table_name := 'AK_CUSTOM_REGION_ITEMS';
245 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
246 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
247 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
248 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.NO;
249
250
251 x_Table_Number := x_Table_Number + 1;
252 x_kpi_metadata_tables(x_Table_Number).table_name := 'AK_CUSTOM_REGION_ITEMS_TL';
253 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
254 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
255 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
256 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.YES;
257
258
259 x_Table_Number := x_Table_Number + 1;
260 x_kpi_metadata_tables(x_Table_Number).table_name := 'BIS_AK_CUSTOM_REGION_ITEMS';
261 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
262 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
263 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
264 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.NO;
265
266 x_Table_Number := x_Table_Number + 1;
267 x_kpi_metadata_tables(x_Table_Number).table_name := 'BIS_AK_CUSTOM_REGIONS';
268 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
269 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
270 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
271 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.NO;
272
273 x_Table_Number := x_Table_Number + 1;
274 x_kpi_metadata_tables(x_Table_Number).table_name := 'BIS_AK_REGION_EXTENSION';
275 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
276 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
277 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
278 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.NO;
279
280 x_Table_Number := x_Table_Number + 1;
281 x_kpi_metadata_tables(x_Table_Number).table_name := 'BIS_AK_REGION_ITEM_EXTENSION';
282 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_AK_TABLE ;
283 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_AK_COLUMN ;
284 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
285 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.NO;
286
287
288 --////////////Fnd form functions table //////////////
289 x_Table_Number := x_Table_Number + 1;
290 x_kpi_metadata_tables(x_Table_Number).table_name := 'FND_FORM_FUNCTIONS';
291 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_FORM_TABLE ;
292 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_FORM_COLUMN ;
293 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
294 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.NO;
295
296
297 x_Table_Number := x_Table_Number + 1;
298 x_kpi_metadata_tables(x_Table_Number).table_name := 'FND_FORM_FUNCTIONS_TL';
299 x_kpi_metadata_tables(x_Table_Number).table_type := BSC_SIMULATION_VIEW_PVT.C_FORM_TABLE ;
300 x_kpi_metadata_tables(x_Table_Number).table_column := BSC_SIMULATION_VIEW_PVT.C_FORM_COLUMN ;
301 x_kpi_metadata_tables(x_Table_Number).duplicate_data := bsc_utility.YES;
302 x_kpi_metadata_tables(x_Table_Number).mls_table := bsc_utility.YES;
303
304 END IF;
305
306
307 END Init_Sim_Tables_Array;
308
309 --//Copy_Ak_Record_Table
310
311 PROCEDURE Copy_Ak_Record_Table
312 ( p_table_name IN VARCHAR2
313 , p_table_type IN VARCHAR2
314 , p_table_column IN VARCHAR2
315 , p_Src_kpi IN NUMBER
316 , p_Trg_kpi IN NUMBER
317 , p_new_region_code IN VARCHAR2
318 , p_new_form_function IN VARCHAR2
319 , p_DbLink_Name IN VARCHAR2 := NULL
320 )IS
321
322
323 h_colum VARCHAR2(100);
324 h_key_name VARCHAR2(30);
325 h_condition VARCHAR2(1000);
326 h_sql VARCHAR2(32000);
327 x_arr_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
328 x_num_columns NUMBER;
329 l_new_region_code AK_REGIONS.region_code%TYPE;
330 l_region_code AK_REGIONS.region_code%TYPE;
331 l_count NUMBER;
332 l_owner all_tab_columns.owner%TYPE;
333 l_new_function_id FND_FORM_FUNCTIONS.function_id%TYPE;
334 l_old_function_id FND_FORM_FUNCTIONS.function_id%TYPE;
335 l_parameters FND_FORM_FUNCTIONS.parameters%TYPE;
336 cd BSC_BIS_LOCKS_PUB.t_cursor;
337
338
339 CURSOR c_column IS
340 SELECT column_name
341 FROM all_tab_columns
342 WHERE table_name = p_table_name
343 AND owner = l_owner
344 ORDER BY column_name;
345
346
347 BEGIN
348
349 BSC_APPS.Init_Bsc_Apps;
350
351 IF(INSTR(p_table_name,'BIS')>0)THEN
352 SELECT DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema('BIS'),USER)
353 INTO l_owner FROM DUAL;
354 ELSIF(INSTR(p_table_name,'FND')>0) THEN
355 SELECT DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema('FND'),USER)
356 INTO l_owner FROM DUAL;
357 ELSE
358 SELECT DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema('AK'),USER)
359 INTO l_owner FROM DUAL;
360 END IF;
361
362
363 IF(p_Src_kpi IS NOT NULL AND p_Trg_kpi IS NOT NULL) THEN
364
365 IF p_DbLink_Name IS NULL THEN
366 h_sql := 'SELECT short_name FROM bsc_kpis_b WHERE indicator = :1';
367 ELSE
368 h_sql := 'SELECT short_name FROM bsc_kpis_b@'|| p_DbLink_Name || ' WHERE indicator = :1';
369 END IF;
370 OPEN cd FOR h_sql USING p_Src_kpi;
371 FETCH cd INTO l_region_code;
372 CLOSE cd;
373
374 l_new_region_code := p_new_region_code;
375 IF(p_table_type =BSC_SIMULATION_VIEW_PVT.C_FORM_TABLE)THEN
376 h_key_name := 'FUNCTION_NAME';
377 l_new_function_id := p_new_form_function;
378 h_condition := 'FUNCTION_NAME =''' || l_region_code || '''';
379
380 IF(p_table_name = 'FND_FORM_FUNCTIONS_TL')THEN
381 IF p_DbLink_Name IS NULL THEN
382 h_sql := 'SELECT function_id FROM fnd_form_functions_vl WHERE function_name = :1';
383 ELSE
384 h_sql := 'SELECT function_id FROM fnd_form_functions_vl@'|| p_DbLink_Name || ' WHERE function_name = :1';
385 END IF;
386 OPEN cd FOR h_sql USING l_region_code;
387 FETCH cd INTO l_old_function_id;
388 CLOSE cd;
389
390 h_condition := 'FUNCTION_ID ='|| l_old_function_id;
391 END IF;
392 ELSE
393 h_key_name := 'REGION_CODE';
394 h_condition := 'REGION_CODE =''' || l_region_code || '''';
395 END IF;
396
397 x_num_columns :=0;
398 OPEN c_column;
399 FETCH c_column INTO h_colum;
400 WHILE c_column%FOUND LOOP
401 x_num_columns := x_num_columns + 1;
402 x_arr_columns(x_num_columns) := h_colum;
403 FETCH c_column INTO h_colum;
404 END LOOP;
405 CLOSE c_column;
406
407 IF x_num_columns > 0 THEN
408
409 h_sql:= 'INSERT INTO ( SELECT ';
410 FOR i IN 1..x_num_columns LOOP
411 IF i <> 1 THEN
412 h_sql:= h_sql || ',';
413 END IF;
414 h_sql:= h_sql || x_arr_columns(i);
415 END LOOP;
416 h_sql:= h_sql || ' FROM ' || p_table_name;
417 h_sql:= h_sql || ' )';
418 h_sql:= h_sql || ' SELECT ';
419 FOR i IN 1..x_num_columns LOOP
420 IF i <> 1 THEN
421 h_sql:= h_sql || ',';
422 END IF;
423
424 IF UPPER(x_arr_columns(i)) = h_key_name THEN
425 h_sql:= h_sql || ''''||l_new_region_code ||''''|| ' AS ' || x_arr_columns(i);
426 ELSIF(UPPER(x_arr_columns(i)) = 'FUNCTION_ID') THEN
427 h_sql:= h_sql || l_new_function_id || ' AS ' || x_arr_columns(i);
428 ELSE
429 h_sql:= h_sql || x_arr_columns(i) || ' AS ' || x_arr_columns(i);
430 END IF;
431 END LOOP;
432 IF p_DbLink_Name IS NULL THEN
433 h_sql:= h_sql || ' FROM ' || p_table_name;
434 ELSE
435 h_sql:= h_sql || ' FROM ' || p_table_name || '@'||p_DbLink_Name;
436 END IF;
437 h_sql:= h_sql || ' WHERE ' || h_condition;
438
439 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
440 END IF;
441
442 IF(p_table_name = 'FND_FORM_FUNCTIONS')THEN
443 UPDATE FND_FORM_FUNCTIONS
444 SET PARAMETERS = REPLACE(PARAMETERS,''||l_region_code||'',''|| l_new_region_code || '')
445 WHERE FUNCTION_ID = l_new_function_id;
446 END IF;
447
448 END IF;
449 END Copy_Ak_Record_Table;
450
451 --/////////////////////////////////End for copy of ak tables////////////////////////
452
453 PROCEDURE Copy_Record_Table
454 ( p_table_name IN VARCHAR2
455 , p_table_type IN VARCHAR2
456 , p_table_column IN VARCHAR2
457 , p_Src_kpi IN NUMBER
458 , p_Trg_kpi IN NUMBER
459 )IS
460
461 CURSOR c_column IS
462 SELECT column_name
463 FROM all_tab_columns
464 WHERE table_name = p_table_name
465 AND owner = DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema,USER)
466 ORDER BY column_name;
467
468 h_colum VARCHAR2(100);
469 h_key_name VARCHAR2(30);
470 h_condition VARCHAR2(1000);
471 h_sql VARCHAR2(32000);
472 x_arr_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
473 x_num_columns NUMBER;
474 l_next_image_id BSC_SYS_IMAGES_MAP_TL.image_id%TYPE;
475 l_image_id BSC_SYS_IMAGES_MAP_TL.image_id%TYPE;
476
477
478 BEGIN
479
480 BSC_APPS.Init_Bsc_Apps;
481
482 h_key_name := 'TAB_VIEW_ID';
483 IF( p_table_column = BSC_SIMULATION_VIEW_PVT.C_SOURCE_CODE )THEN
484 h_key_name := 'SOURCE_CODE';
485 ELSIF (p_table_column = BSC_SIMULATION_VIEW_PVT.C_INDICATOR) THEN
486 h_key_name := 'INDICATOR';
487 END IF;
488
489
490
491 x_num_columns :=0;
492 OPEN c_column;
493 FETCH c_column INTO h_colum;
494 WHILE c_column%FOUND LOOP
495 x_num_columns := x_num_columns + 1;
496 x_arr_columns(x_num_columns) := h_colum;
497 FETCH c_column INTO h_colum;
498 END LOOP;
499 CLOSE c_column;
500
501 IF x_num_columns > 0 THEN
502 IF(h_key_name = 'SOURCE_CODE') THEN
503 h_condition := 'SOURCE_TYPE = 2 AND ' || h_key_name || '=' || p_Src_kpi;
504 ELSIF(h_key_name = 'INDICATOR') THEN
505 h_condition := 'INDICATOR =' || p_Src_kpi;
506 ELSE
507 h_condition := 'TAB_ID =-999 AND '|| p_table_column ||' = ' || p_Src_kpi;
508 END IF;
509
510 h_sql:= 'INSERT INTO ( SELECT ';
511 FOR i IN 1..x_num_columns LOOP
512 IF i <> 1 THEN
513 h_sql:= h_sql || ',';
514 END IF;
515 h_sql:= h_sql || x_arr_columns(i);
516 END LOOP;
517 h_sql:= h_sql || ' FROM ' || p_table_name;
518 h_sql:= h_sql || ' )';
519 h_sql:= h_sql || ' SELECT ';
520 FOR i IN 1..x_num_columns LOOP
521 IF i <> 1 THEN
522 h_sql:= h_sql || ',';
523 END IF;
524
525 IF(p_table_name='BSC_SYS_IMAGES_MAP_TL' AND UPPER(x_arr_columns(i)) = 'IMAGE_ID') THEN
526
527 SELECT bsc_sys_image_id_s.nextval
528 INTO l_next_image_id
529 FROM dual;
530 h_sql:= h_sql || l_next_image_id || ' AS ' || x_arr_columns(i);
531 ELSIF(p_table_name='BSC_SYS_IMAGES' AND UPPER(x_arr_columns(i)) = 'IMAGE_ID' )THEN
532
533 SELECT DISTINCT image_id
534 INTO l_image_id
535 FROM BSC_SYS_IMAGES_MAP_TL
536 WHERE SOURCE_TYPE =2
537 AND SOURCE_CODE =p_Src_kpi;
538
539 h_condition := p_table_column ||' = ' || l_image_id;
540
541 SELECT distinct image_id
542 INTO l_image_id
543 FROM BSC_SYS_IMAGES_MAP_TL
544 WHERE SOURCE_TYPE =2
545 AND SOURCE_CODE =p_Trg_kpi;
546
547 h_sql:= h_sql || l_image_id || ' AS ' || x_arr_columns(i);
548
549
550 ELSIF UPPER(x_arr_columns(i)) = h_key_name THEN
551 h_sql:= h_sql || p_Trg_kpi || ' AS ' || x_arr_columns(i);
552 ELSE
553 h_sql:= h_sql || x_arr_columns(i) || ' AS ' || x_arr_columns(i);
554 END IF;
555 END LOOP;
556 h_sql:= h_sql || ' FROM ' || p_table_name;
557 h_sql:= h_sql || ' WHERE ' || h_condition;
558
559 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
560
561 END IF;
562 END Copy_Record_Table;
563
564
565
566 PROCEDURE Duplicate_sim_metadata
567 (
568 p_source_kpi IN NUMBER
569 ,p_target_kpi IN NUMBER
570 ,x_return_status OUT NOCOPY VARCHAR2
571 ,x_msg_count OUT NOCOPY NUMBER
572 ,x_msg_data OUT NOCOPY VARCHAR2
573 )IS
574
575 l_Table_Number NUMBER;
576 l_kpi_metadata_tables BSC_DESIGNER_PVT.t_kpi_metadata_tables;
577 l_count NUMBER;
578 l_link_id NUMBER;
579 l_type BIS_INDICATORS.measure_type%TYPE;
580 l_short_name BIS_INDICATORS.short_name%TYPE;
581 l_attribute_code AK_REGION_ITEMS_VL.attribute_code%TYPE;
582 l_region_Code AK_REGION_ITEMS_VL.region_code%TYPE;
583 l_Actual_Data_Source BIS_INDICATORS.actual_data_source%TYPE;
584 l_dataset_id BIS_INDICATORS.dataset_id%TYPE;
585 l_node_id BIS_INDICATORS.dataset_id%TYPE;
586
587 CURSOR c_cust_labels IS
588 SELECT DISTINCT link_id
589 FROM bsc_tab_view_labels_vl
590 WHERE tab_id =BSC_SIMULATION_VIEW_PUB.c_TAB_ID
591 AND tab_view_id = p_source_kpi
592 AND label_type
593 IN ( BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure,
594 BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure_actual,BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure_change,BSC_SIMULATION_VIEW_PUB.c_TYPE_MEASURE_COLOR,BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure_slider
595 );
596
597
598 BEGIN
599 SAVEPOINT Duplicatekpimetadata;
600 FND_MSG_PUB.INITIALIZE;
601 x_return_status := FND_API.G_RET_STS_SUCCESS;
602
603
604 --Need to check if the records already exists for duplicate objective
605 --if yes then don't need to copy any records
606
607 SELECT COUNT(0)
608 INTO l_count
609 FROM bsc_tab_view_labels_vl
610 WHERE tab_id =BSC_SIMULATION_VIEW_PUB.c_TAB_ID
611 AND tab_view_id = p_target_kpi;
612
613 IF( l_count =0 )THEN
614 BSC_APPS.Init_Bsc_Apps;
615
616 BSC_SIMULATION_VIEW_PVT.Init_Sim_Tables_Array
617 (
618 p_copy_Ak_Tables => FND_API.G_FALSE
619 ,x_Table_Number => l_Table_Number
620 ,x_kpi_metadata_tables => l_kpi_metadata_tables
621 );
622
623
624 FOR i_index IN 1..l_Table_Number LOOP
625 IF(l_kpi_metadata_tables(i_index).duplicate_data = bsc_utility.YES) THEN
626 Copy_Record_Table(l_kpi_metadata_tables(i_index).table_name,l_kpi_metadata_tables(i_index).table_type,l_kpi_metadata_tables(i_index).table_column, p_source_kpi, p_target_kpi);
627 END IF;
628 END LOOP;
629
630 --Here we need to update the link id of duplicate objective with the dataset ids of
631 -- of the calculated kpis which were created for the duplicate objective
632
633 SELECT short_name
634 INTO l_region_Code
635 FROM bsc_kpis_b
636 WHERE config_Type =BSC_SIMULATION_VIEW_PUB.c_TYPE
637 AND indicator = p_source_kpi;
638
639 FOR cd IN c_cust_labels LOOP
640 l_link_id := cd.link_id;
641
642 SELECT measure_type,actual_data_source
643 INTO l_type,l_Actual_Data_Source
644 FROM bis_indicators
645 WHERE dataset_id = l_link_id;
646
647 IF(l_type=BSC_SIMULATION_VIEW_PUB.c_CALCULATED_KPI)THEN
648
649 l_attribute_code := SUBSTR(l_Actual_Data_Source, INSTR(l_Actual_Data_Source, '.') + 1,LENGTH(l_Actual_Data_Source));
650 l_dataset_id := Get_dup_dataset_id
651 (
652 p_tarInd => p_target_kpi
653 , p_attribute_code => l_attribute_code
654 );
655 IF(l_dataset_id IS NULL) THEN
656 l_dataset_id:= l_link_id;
657 END IF;
658
659 UPDATE bsc_tab_view_labels_b
660 SET link_id= l_dataset_id
661 WHERE tab_id = BSC_SIMULATION_VIEW_PUB.c_TAB_ID
662 AND tab_view_id = p_target_kpi
663 AND link_id =l_link_id
664 AND label_type
665 IN ( BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure,
666 BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure_actual,BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure_change,BSC_SIMULATION_VIEW_PUB.c_TYPE_MEASURE_COLOR,BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure_slider
667 );
668
669 UPDATE bsc_kpi_tree_nodes_b
670 SET node_id =l_dataset_id
671 WHERE indicator =p_target_kpi
672 AND node_id =l_link_id;
673
674 UPDATE bsc_kpi_tree_nodes_tl
675 SET node_id =l_dataset_id
676 WHERE indicator =p_target_kpi
677 AND node_id =l_link_id;
678 END IF;
679 END LOOP;
680
681 --now set the default node id for the duplicate objective
682
683 SELECT a.source ,a.dataset_id
684 INTO l_type ,l_node_id
685 FROM bsc_sys_datasets_b a
686 ,bsc_kpi_properties b
687 WHERE b.property_code = BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID
688 AND b.property_value =a.dataset_id
689 AND indicator =p_source_kpi;
690
691 l_dataset_id := l_node_id;
692
693 IF(l_type=BSC_SIMULATION_VIEW_PUB.c_CALC_KPI)THEN
694
695 SELECT actual_data_source
696 INTO l_Actual_Data_Source
697 FROM bis_indicators
698 WHERE dataset_id = l_node_id;
699
700 l_attribute_code := SUBSTR(l_Actual_Data_Source, INSTR(l_Actual_Data_Source, '.') + 1,LENGTH(l_Actual_Data_Source));
701 l_dataset_id := Get_dup_dataset_id
702 (
703 p_tarInd => p_target_kpi
704 , p_attribute_code => l_attribute_code
705 );
706 IF(l_dataset_id IS NULL) THEN
707 l_dataset_id:= l_node_id;
708 END IF;
709 END IF;
710
711 BSC_SIMULATION_VIEW_PVT.set_default_node
712 (
713 p_indicator => p_target_kpi
714 ,p_default_node => 1
715 ,p_dataset_id => l_dataset_id
716 ,x_return_status => x_return_status
717 ,x_msg_count => x_msg_count
718 ,x_msg_data => x_msg_data
719 );
720 IF(x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
722 END IF;
723 END IF;
724
725 EXCEPTION
726 WHEN FND_API.G_EXC_ERROR THEN
727 ROLLBACK TO Duplicatekpimetadata;
728 FND_MSG_PUB.Count_And_Get
729 ( p_encoded => FND_API.G_FALSE
730 , p_count => x_msg_count
731 , p_data => x_msg_data
732 );
733 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
734 x_return_status := FND_API.G_RET_STS_ERROR;
735 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
736 ROLLBACK TO Duplicatekpimetadata;
737 FND_MSG_PUB.Count_And_Get
738 ( p_encoded => FND_API.G_FALSE
739 , p_count => x_msg_count
740 , p_data => x_msg_data
741 );
742 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
743 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
744 WHEN NO_DATA_FOUND THEN
745 ROLLBACK TO Duplicatekpimetadata;
746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
747 IF (x_msg_data IS NOT NULL) THEN
748 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Duplicate_kpi_metadata ';
749 ELSE
750 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Duplicate_kpi_metadata ';
751 END IF;
752 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
753 WHEN OTHERS THEN
754 ROLLBACK TO Duplicatekpimetadata;
755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
756 IF (x_msg_data IS NOT NULL) THEN
757 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Duplicate_kpi_metadata ';
758 ELSE
759 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Duplicate_kpi_metadata ';
760 END IF;
761 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
762
763 END Duplicate_sim_metadata;
764
765
766 PROCEDURE Add_Or_Update_YTD
767 (
768 p_indicator IN NUMBER
769 ,p_YTD IN VARCHAR2
770 ,p_prev_YTD IN VARCHAR2
771 ,x_return_status OUT NOCOPY VARCHAR2
772 ,x_msg_count OUT NOCOPY NUMBER
773 ,x_msg_data OUT NOCOPY VARCHAR2
774 )IS
775 l_count NUMBER;
776 l_region_code ak_regions.region_code%TYPE;
777 l_region_app_id ak_regions.region_application_id%TYPE;
778 l_YTD bis_ak_region_extension.attribute21%TYPE;
779
780 l_attribute21 bis_ak_region_extension.attribute21%TYPE;
781
782 CURSOR c_kpi IS
783 SELECT A.region_code,A.region_application_id
784 FROM bsc_kpis_b B,
785 ak_regions A
786 WHERE A.region_code =B.short_name
787 AND B.indicator = p_indicator
788 AND B.config_type = BSC_SIMULATION_VIEW_PUB.c_TYPE;
789
790 BEGIN
791 SAVEPOINT AddOrUpdateYTD;
792 FND_MSG_PUB.INITIALIZE;
793 x_return_status := FND_API.G_RET_STS_SUCCESS;
794
795 l_YTD :=p_YTD;
796
797 --We need to check if the record exists in BIS_AK_REGION_EXTENSION table
798 -- if not then we need to create the record else update the record
799 FOR cd IN c_kpi LOOP
800
801 IF(cd.region_code IS NOT NULL) THEN
802 SELECT COUNT(0)
803 INTO l_count
804 FROM BIS_AK_REGION_EXTENSION
805 WHERE region_code =cd.region_code;
806
807 IF(l_YTD IS NULL) THEN
808 l_YTD := BSC_SIMULATION_VIEW_PUB.c_YEAR_TO_DATE_DISABLED;
809 END IF;
810
811
812 IF(l_count=0) THEN
813
814 BIS_REGION_EXTENSION_PVT.CREATE_REGION_EXTN_RECORD
815 (
816 p_commit => FND_API.G_FALSE
817 ,pRegionCode => cd.region_code
818 ,pRegionAppId => cd.region_application_id
819 ,pAttribute16 => NULL
820 ,pAttribute17 => NULL
821 ,pAttribute18 => NULL
822 ,pAttribute19 => NULL
823 ,pAttribute20 => NULL
824 ,pAttribute21 => l_YTD
825 ,pAttribute22 => NULL
826 ,pAttribute23 => NULL
827 ,pAttribute24 => NULL
828 ,pAttribute25 => NULL
829 ,pAttribute26 => NULL
830 ,pAttribute27 => NULL
831 ,pAttribute28 => NULL
832 ,pAttribute29 => NULL
833 ,pAttribute30 => NULL
834 ,pAttribute31 => NULL
835 ,pAttribute32 => NULL
836 ,pAttribute33 => NULL
837 ,pAttribute34 => NULL
838 ,pAttribute35 => NULL
839 ,pAttribute36 => NULL
840 ,pAttribute37 => NULL
841 ,pAttribute38 => NULL
842 ,pAttribute39 => NULL
843 ,pAttribute40 => NULL
844 );
845
846 ELSE
847 BIS_REGION_EXTENSION_PVT.UPDATE_REGION_EXTN_RECORD
848 (
849 p_commit => FND_API.G_FALSE
850 ,pRegionCode => cd.region_code
851 ,pRegionAppId => cd.region_application_id
852 ,pAttribute16 => NULL
853 ,pAttribute17 => NULL
854 ,pAttribute18 => NULL
855 ,pAttribute19 => NULL
856 ,pAttribute20 => NULL
857 ,pAttribute21 => l_YTD
858 ,pAttribute22 => NULL
859 ,pAttribute23 => NULL
860 ,pAttribute24 => NULL
861 ,pAttribute25 => NULL
862 ,pAttribute26 => NULL
863 ,pAttribute27 => NULL
864 ,pAttribute28 => NULL
865 ,pAttribute29 => NULL
866 ,pAttribute30 => NULL
867 ,pAttribute31 => NULL
868 ,pAttribute32 => NULL
869 ,pAttribute33 => NULL
870 ,pAttribute34 => NULL
871 ,pAttribute35 => NULL
872 ,pAttribute36 => NULL
873 ,pAttribute37 => NULL
874 ,pAttribute38 => NULL
875 ,pAttribute39 => NULL
876 ,pAttribute40 => NULL
877 );
878 -- update the record
879 END IF;
880
881 --Changing the YTD will set the prototype_flag to 7 for both production mode
882 --objectives and kpis
883
884 IF(l_count=0 AND NOT (l_YTD = BSC_SIMULATION_VIEW_PUB.c_YEAR_TO_DATE_DISABLED))THEN
885 BSC_DESIGNER_PVT.ActionFlag_Change(p_indicator, BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color);
886 ELSIF(l_count>0 AND p_prev_YTD <> l_YTD) THEN
887 BSC_DESIGNER_PVT.ActionFlag_Change(p_indicator, BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color);
888 END IF;
889 --For simulation only 1 calculation is enabled and that is
890 --Year To Date
891
892 UPDATE bsc_kpi_calculations
893 SET user_level0 =BSC_SIMULATION_VIEW_PUB.c_HIDE
894 ,user_level1 =BSC_SIMULATION_VIEW_PUB.c_HIDE
895 WHERE indicator = p_indicator;
896
897 UPDATE bsc_kpi_calculations
898 SET user_level0 =BSC_SIMULATION_VIEW_PUB.c_VISIBLE
899 ,user_level1 =BSC_SIMULATION_VIEW_PUB.c_VISIBLE
900 WHERE indicator = p_indicator
901 AND calculation_id =BSC_SIMULATION_VIEW_PUB.c_YTD_CALC;
902
903 END IF;
904 END LOOP;
905
906 EXCEPTION
907 WHEN FND_API.G_EXC_ERROR THEN
908 ROLLBACK TO AddOrUpdateYTD;
909 FND_MSG_PUB.Count_And_Get
910 ( p_encoded => FND_API.G_FALSE
911 , p_count => x_msg_count
912 , p_data => x_msg_data
913 );
914 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
915 x_return_status := FND_API.G_RET_STS_ERROR;
916 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
917 ROLLBACK TO AddOrUpdateYTD;
918 FND_MSG_PUB.Count_And_Get
919 ( p_encoded => FND_API.G_FALSE
920 , p_count => x_msg_count
921 , p_data => x_msg_data
922 );
923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
924 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
925 WHEN NO_DATA_FOUND THEN
926 ROLLBACK TO AddOrUpdateYTD;
927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
928 IF (x_msg_data IS NOT NULL) THEN
929 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Add_Or_Update_YTD ';
930 ELSE
931 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Add_Or_Update_YTD ';
932 END IF;
933 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
934 WHEN OTHERS THEN
935 ROLLBACK TO AddOrUpdateYTD;
936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
937 IF (x_msg_data IS NOT NULL) THEN
938 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Add_Or_Update_YTD ';
939 ELSE
940 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Add_Or_Update_YTD ';
941 END IF;
942 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
943 END Add_Or_Update_YTD;
944
945
946 PROCEDURE Set_Kpi_Color_Flag
947 (
948 p_indicator IN NUMBER
949 ,p_dataset_id IN NUMBER
950 ,p_color_flag IN VARCHAR2
951 ,p_color_by_total IN NUMBER
952 ,x_return_status OUT NOCOPY VARCHAR2
953 ,x_msg_count OUT NOCOPY NUMBER
954 ,x_msg_data OUT NOCOPY VARCHAR2
955 )IS
956 l_kpi_measure_id bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE;
957 l_kpi_measure_rec BSC_KPI_MEASURE_PROPS_PUB.kpi_measure_props_rec;
958 BEGIN
959 SAVEPOINT SetKpiColorFlag;
960 FND_MSG_PUB.INITIALIZE;
961 x_return_status := FND_API.G_RET_STS_SUCCESS;
962
963 l_kpi_measure_id := BSC_SIMULATION_VIEW_PUB.Get_Kpi_Measure_Id
964 (
965 p_indicator => p_indicator
966 , p_dataset_id => p_dataset_id
967 );
968 --'T' Means color is disabled and 'F' means it is not disbaled
969 --so Change 1 to 'F' and 0 to 'T'
970 --This flag is used to show the color of the sim node in the objective layout page.
971 --As of now I am passing the value as 'F' i.e to show the color always.
972 --DECODE(p_color_flag,1,'F','Y')
973
974 SELECT DECODE(p_color_flag,1,BSC_SIMULATION_VIEW_PVT.C_SHOW_COLOR,BSC_SIMULATION_VIEW_PVT.C_DISABLE_COLOR)
975 INTO l_kpi_measure_rec.disable_color
976 FROM DUAL;
977
978 l_kpi_measure_rec.objective_id := p_indicator;
979 l_kpi_measure_rec.kpi_measure_id := l_kpi_measure_id;
980 l_kpi_measure_rec.apply_color_flag := 1;
981 l_kpi_measure_rec.color_by_total := p_color_by_total;
982
983
984 BSC_KPI_MEASURE_PROPS_PUB.Update_Kpi_Measure_Props (
985 p_commit => FND_API.G_FALSE
986 , p_kpi_measure_rec => l_kpi_measure_rec
987 , p_cascade_shared => TRUE
988 , x_return_status => x_return_status
989 , x_msg_count => x_msg_count
990 , x_msg_data => x_msg_data
991 );
992
993 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS)THEN
994 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
995 END IF;
996
997 EXCEPTION
998 WHEN FND_API.G_EXC_ERROR THEN
999 ROLLBACK TO SetKpiColorFlag;
1000 FND_MSG_PUB.Count_And_Get
1001 ( p_encoded => FND_API.G_FALSE
1002 , p_count => x_msg_count
1003 , p_data => x_msg_data
1004 );
1005 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1006 x_return_status := FND_API.G_RET_STS_ERROR;
1007 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1008 ROLLBACK TO SetKpiColorFlag;
1009 FND_MSG_PUB.Count_And_Get
1010 ( p_encoded => FND_API.G_FALSE
1011 , p_count => x_msg_count
1012 , p_data => x_msg_data
1013 );
1014 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1015 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1016 WHEN NO_DATA_FOUND THEN
1017 ROLLBACK TO SetKpiColorFlag;
1018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1019 IF (x_msg_data IS NOT NULL) THEN
1020 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Set_Kpi_Color_Flag ';
1021 ELSE
1022 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Set_Kpi_Color_Flag ';
1023 END IF;
1024 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1025 WHEN OTHERS THEN
1026 ROLLBACK TO SetKpiColorFlag;
1027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1028 IF (x_msg_data IS NOT NULL) THEN
1029 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Set_Kpi_Color_Flag ';
1030 ELSE
1031 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Set_Kpi_Color_Flag ';
1032 END IF;
1033 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1034
1035 END Set_Kpi_Color_Flag;
1036
1037
1038
1039 PROCEDURE Set_Kpi_Color_Method
1040 (
1041 p_indicator IN NUMBER
1042 ,p_dataset_id IN NUMBER
1043 ,p_color_method IN NUMBER
1044 ,x_return_status OUT NOCOPY VARCHAR2
1045 ,x_msg_count OUT NOCOPY NUMBER
1046 ,x_msg_data OUT NOCOPY VARCHAR2
1047 )IS
1048
1049 CURSOR c_def_node IS
1050 SELECT property_value
1051 FROM bsc_kpi_properties
1052 WHERE indicator =p_indicator
1053 AND property_code =BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID;
1054
1055 l_count NUMBER;
1056 l_kpi_measure_id bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE;
1057
1058 BEGIN
1059 SAVEPOINT SetKpiColorMethod;
1060 FND_MSG_PUB.INITIALIZE;
1061 x_return_status := FND_API.G_RET_STS_SUCCESS;
1062
1063 l_count :=0;
1064
1065 SELECT COUNT(0)
1066 INTO l_count
1067 FROM bsc_sys_datasets_b
1068 WHERE dataset_id= p_dataset_id
1069 AND color_method =p_color_method;
1070
1071 UPDATE bsc_sys_datasets_b
1072 SET color_method =p_color_method
1073 WHERE dataset_id = p_dataset_id;
1074
1075 l_kpi_measure_id := BSC_SIMULATION_VIEW_PUB.Get_Kpi_Measure_Id
1076 (
1077 p_indicator => p_indicator
1078 , p_dataset_id => p_dataset_id
1079 );
1080 --If the color method has been changed then set the kpi prototype_flag to 7
1081 -- if it also the default node then change the prototype_flag of objective to 7
1082 FOR cd IN c_def_node LOOP
1083 IF( l_count =0)THEN
1084
1085 BSC_KPI_COLOR_PROPERTIES_PUB.Kpi_Prototype_Flag_Change
1086 (
1087 p_objective_id => p_indicator
1088 , p_kpi_measure_id => l_kpi_measure_id
1089 , p_prototype_flag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
1090 , x_return_status => x_return_status
1091 , x_msg_count => x_msg_count
1092 , x_msg_data => x_msg_data
1093 );
1094
1095 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097 END IF;
1098
1099 IF(cd.property_value =p_dataset_id) THEN
1100 BSC_KPI_COLOR_PROPERTIES_PUB.Obj_Prototype_Flag_Change
1101 (
1102 p_objective_id => p_indicator
1103 , p_prototype_flag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
1104 , x_return_status => x_return_status
1105 , x_msg_count => x_msg_count
1106 , x_msg_data => x_msg_data
1107 );
1108 IF(x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1110 END IF;
1111 END IF;
1112
1113 END IF;
1114 END LOOP;
1115
1116
1117 EXCEPTION
1118 WHEN FND_API.G_EXC_ERROR THEN
1119 ROLLBACK TO SetKpiColorMethod;
1120 FND_MSG_PUB.Count_And_Get
1121 ( p_encoded => FND_API.G_FALSE
1122 , p_count => x_msg_count
1123 , p_data => x_msg_data
1124 );
1125 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1126 x_return_status := FND_API.G_RET_STS_ERROR;
1127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1128 ROLLBACK TO SetKpiColorMethod;
1129 FND_MSG_PUB.Count_And_Get
1130 ( p_encoded => FND_API.G_FALSE
1131 , p_count => x_msg_count
1132 , p_data => x_msg_data
1133 );
1134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1135 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1136 WHEN NO_DATA_FOUND THEN
1137 ROLLBACK TO SetKpiColorMethod;
1138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1139 IF (x_msg_data IS NOT NULL) THEN
1140 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Set_Kpi_Color_Method ';
1141 ELSE
1142 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Set_Kpi_Color_Method ';
1143 END IF;
1144 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1145 WHEN OTHERS THEN
1146 ROLLBACK TO SetKpiColorMethod;
1147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1148 IF (x_msg_data IS NOT NULL) THEN
1149 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Set_Kpi_Color_Method ';
1150 ELSE
1151 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Set_Kpi_Color_Method ';
1152 END IF;
1153 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1154 END Set_Kpi_Color_Method;
1155
1156
1157
1158 PROCEDURE set_default_node
1159 (
1160 p_indicator IN NUMBER
1161 ,p_default_node IN NUMBER
1162 ,p_dataset_id IN NUMBER
1163 ,x_return_status OUT NOCOPY VARCHAR2
1164 ,x_msg_count OUT NOCOPY NUMBER
1165 ,x_msg_data OUT NOCOPY VARCHAR2
1166 )IS
1167 l_prev_default_node NUMBER;
1168 l_count NUMBER;
1169 BEGIN
1170 IF(p_default_node=1) THEN
1171 SELECT COUNT(0)
1172 INTO l_count
1173 FROM bsc_kpi_properties
1174 WHERE indicator = p_indicator
1175 AND property_code =BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID;
1176
1177 IF(l_count =0)THEN
1178 -- INSERT DEFAULT NODE
1179 INSERT INTO bsc_kpi_properties
1180 ( indicator
1181 , property_code
1182 , property_value
1183 , secondary_value
1184 ) VALUES
1185 ( p_indicator
1186 ,BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID
1187 ,p_dataset_id
1188 ,NULL
1189 );
1190 ELSE
1191 SELECT property_value
1192 INTO l_prev_default_node
1193 FROM bsc_kpi_properties
1194 WHERE indicator =p_indicator
1195 AND property_code =BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID;
1196
1197 IF(l_prev_default_node <> p_dataset_id) THEN
1198 UPDATE bsc_kpi_properties
1199 SET property_value = p_dataset_id
1200 WHERE indicator =p_indicator
1201 AND property_code =BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID;
1202
1203 --BSC_DESIGNER_PVT.ActionFlag_Change(p_indicator, BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color);
1204 --Changing the default node should only set the prototype_flag of the objective
1205 -- and not of the kpis.
1206
1207 BSC_KPI_COLOR_PROPERTIES_PUB.Obj_Prototype_Flag_Change
1208 (
1209 p_objective_id => p_indicator
1210 , p_prototype_flag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
1211 , x_return_status => x_return_status
1212 , x_msg_count => x_msg_count
1213 , x_msg_data => x_msg_data
1214 );
1215 IF(x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1217 END IF;
1218
1219 END IF;
1220 END IF;
1221 END IF;
1222 EXCEPTION
1223 WHEN FND_API.G_EXC_ERROR THEN
1224 FND_MSG_PUB.Count_And_Get
1225 ( p_encoded => FND_API.G_FALSE
1226 , p_count => x_msg_count
1227 , p_data => x_msg_data
1228 );
1229 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1230 x_return_status := FND_API.G_RET_STS_ERROR;
1231 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1232 FND_MSG_PUB.Count_And_Get
1233 ( p_encoded => FND_API.G_FALSE
1234 , p_count => x_msg_count
1235 , p_data => x_msg_data
1236 );
1237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1238 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1239 WHEN NO_DATA_FOUND THEN
1240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1241 IF (x_msg_data IS NOT NULL) THEN
1242 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.set_default_node ';
1243 ELSE
1244 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.set_default_node ';
1245 END IF;
1246 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1247 WHEN OTHERS THEN
1248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1249 IF (x_msg_data IS NOT NULL) THEN
1250 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.set_default_node ';
1251 ELSE
1252 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.set_default_node ';
1253 END IF;
1254 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1255
1256 END set_default_node;
1257
1258
1259
1260 PROCEDURE copy_sim_metadata
1261 (
1262 p_source_kpi IN NUMBER
1263 ,p_target_kpi IN NUMBER
1264 ,x_return_status OUT NOCOPY VARCHAR2
1265 ,x_msg_count OUT NOCOPY NUMBER
1266 ,x_msg_data OUT NOCOPY VARCHAR2
1267 )IS
1268
1269 l_Table_Number NUMBER;
1270 l_kpi_metadata_tables BSC_DESIGNER_PVT.t_kpi_metadata_tables;
1271 l_new_region_code AK_REGIONS.region_code%TYPE;
1272 l_count NUMBER;
1273
1274 CURSOR c_sim_nodes IS
1275 SELECT node_id
1276 FROM bsc_kpi_tree_nodes_vl
1277 WHERE indicator =p_target_kpi;
1278
1279 l_new_function_id FND_FORM_FUNCTIONS.function_id%TYPE;
1280
1281
1282 BEGIN
1283 SAVEPOINT copysimmetadata;
1284 FND_MSG_PUB.INITIALIZE;
1285 x_return_status := FND_API.G_RET_STS_SUCCESS;
1286
1287 BSC_APPS.Init_Bsc_Apps;
1288
1289 BSC_SIMULATION_VIEW_PVT.Init_Sim_Tables_Array
1290 (
1291 p_copy_Ak_Tables => FND_API.G_TRUE
1292 ,x_Table_Number => l_Table_Number
1293 ,x_kpi_metadata_tables => l_kpi_metadata_tables
1294 );
1295
1296 l_new_region_code := BSC_BIS_KPI_CRUD_PUB.Generate_Unique_Region_Code();
1297
1298 SELECT FND_FORM_FUNCTIONS_S.NEXTVAL
1299 INTO l_new_function_id
1300 FROM dual;
1301
1302
1303 FOR cd IN c_sim_nodes LOOP
1304 BSC_KPI_TREE_NODES_PKG.DELETE_ROW
1305 (
1306 X_INDICATOR => p_target_kpi
1307 ,X_NODE_ID => cd.node_id
1308 );
1309 END LOOP;
1310
1311 FOR i_index IN 1..l_Table_Number LOOP
1312
1313 IF(l_kpi_metadata_tables(i_index).duplicate_data = bsc_utility.YES AND (l_kpi_metadata_tables(i_index).table_type<>BSC_SIMULATION_VIEW_PVT.C_AK_TABLE AND l_kpi_metadata_tables(i_index).table_type<>BSC_SIMULATION_VIEW_PVT.C_FORM_TABLE)) THEN
1314 Copy_Record_Table(l_kpi_metadata_tables(i_index).table_name,l_kpi_metadata_tables(i_index).table_type,l_kpi_metadata_tables(i_index).table_column, p_source_kpi, p_target_kpi);
1315 ELSIF(l_kpi_metadata_tables(i_index).duplicate_data = bsc_utility.YES AND l_kpi_metadata_tables(i_index).table_type=BSC_SIMULATION_VIEW_PVT.C_AK_TABLE) THEN
1316 Copy_Ak_Record_Table(l_kpi_metadata_tables(i_index).table_name,l_kpi_metadata_tables(i_index).table_type,l_kpi_metadata_tables(i_index).table_column, p_source_kpi, p_target_kpi,l_new_region_code,NULL);
1317 ELSIF(l_kpi_metadata_tables(i_index).duplicate_data = bsc_utility.YES AND l_kpi_metadata_tables(i_index).table_type=BSC_SIMULATION_VIEW_PVT.C_FORM_TABLE)THEN
1318 Copy_Ak_Record_Table(l_kpi_metadata_tables(i_index).table_name,l_kpi_metadata_tables(i_index).table_type,l_kpi_metadata_tables(i_index).table_column, p_source_kpi, p_target_kpi,l_new_region_code,l_new_function_id);
1319 END IF;
1320 END LOOP;
1321
1322 UPDATE bsc_kpis_b
1323 SET short_name =l_new_region_code
1324 WHERE indicator =p_target_kpi;
1325
1326 UPDATE ak_regions
1327 SET attribute8 = p_target_kpi || '.'||BSC_SIMULATION_VIEW_PVT.C_DEFAULT_ANA_OPTION
1328 WHERE region_code =l_new_region_code;
1329
1330 UPDATE bsc_kpi_analysis_options_b
1331 SET short_name =l_new_region_code
1332 WHERE indicator =p_target_kpi;
1333
1334 END copy_sim_metadata;
1335
1336
1337 PROCEDURE Set_Ak_Format_Id
1338 (
1339 p_indicator IN BSC_KPIS_B.indicator%TYPE
1340 ,p_dataset_Id IN BSC_SYS_DATASETS_VL.dataset_id%TYPE
1341 ,p_format_Id IN BSC_KPI_TREE_NODES_VL.format_id%TYPE
1342 ,x_return_status OUT NOCOPY VARCHAR2
1343 ,x_msg_count OUT NOCOPY NUMBER
1344 ,x_msg_data OUT NOCOPY VARCHAR2
1345 ) IS
1346 l_region_code AK_REGIONS.region_code%TYPE;
1347 l_attribute_code AK_REGION_ITEMS_VL.attribute_code%TYPE;
1348 l_meas_short_name BIS_INDICATORS.short_name%TYPE;
1349 l_number_format v$nls_parameters.value%TYPE;
1350 l_replace_format VARCHAR2(10);
1351 l_attribute7 AK_REGION_ITEMS_VL.attribute7%TYPE;
1352
1353 CURSOR c_ind IS
1354 SELECT short_name
1355 FROM bsc_kpis_vl
1356 WHERE indicator =p_indicator;
1357
1358 CURSOR c_ak_items IS
1359 SELECT a.attribute_code,b.attribute_code AS childAttrCode
1360 FROM ak_region_items_vl a,ak_region_items_vl b
1361 WHERE a.region_code =b.region_code
1362 AND b.attribute2(+)=a.attribute_code
1363 AND a.REGION_CODE = l_region_code
1364 AND a.attribute1=BSC_SIMULATION_VIEW_PVT.C_MEASURE_NOTARGET
1365 AND a.attribute2= l_meas_short_name;
1366
1367 BEGIN
1368 SAVEPOINT SetAkFormatId;
1369 FND_MSG_PUB.INITIALIZE;
1370 x_return_status := FND_API.G_RET_STS_SUCCESS;
1371
1372 FOR cd IN c_ind LOOP
1373
1374 l_region_code := cd.short_name;
1375 SELECT short_name
1376 INTO l_meas_short_name
1377 FROM bis_indicators
1378 WHERE dataset_id =p_dataset_Id;
1379
1380 l_attribute7 := Get_Format(p_format_Id => p_format_Id);
1381
1382 FOR cd_c IN c_ak_items LOOP
1383
1384 UPDATE ak_region_items
1385 SET attribute7= l_attribute7
1386 WHERE region_code =l_region_code
1387 AND attribute_code= cd_c.attribute_code;
1388
1389
1390 UPDATE ak_region_items
1391 SET attribute7= l_attribute7
1392 WHERE region_code =l_region_code
1393 AND attribute_code= cd_c.childAttrCode;
1394
1395 END LOOP;
1396
1397 END LOOP;
1398
1399 EXCEPTION
1400 WHEN FND_API.G_EXC_ERROR THEN
1401 ROLLBACK TO SetAkFormatId;
1402 FND_MSG_PUB.Count_And_Get
1403 ( p_encoded => FND_API.G_FALSE
1404 , p_count => x_msg_count
1405 , p_data => x_msg_data
1406 );
1407 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1408 x_return_status := FND_API.G_RET_STS_ERROR;
1409 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1410 ROLLBACK TO SetAkFormatId;
1411 FND_MSG_PUB.Count_And_Get
1412 ( p_encoded => FND_API.G_FALSE
1413 , p_count => x_msg_count
1414 , p_data => x_msg_data
1415 );
1416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1417 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1418 WHEN NO_DATA_FOUND THEN
1419 ROLLBACK TO SetAkFormatId;
1420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1421 IF (x_msg_data IS NOT NULL) THEN
1422 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Set_Ak_Format_Id ';
1423 ELSE
1424 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Set_Ak_Format_Id ';
1425 END IF;
1426 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1427 WHEN OTHERS THEN
1428 ROLLBACK TO SetAkFormatId;
1429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1430 IF (x_msg_data IS NOT NULL) THEN
1431 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Set_Ak_Format_Id ';
1432 ELSE
1433 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Set_Ak_Format_Id ';
1434 END IF;
1435 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1436
1437 END Set_Ak_Format_Id;
1438
1439
1440
1441 PROCEDURE Handle_Shared_Objectives
1442 (
1443 p_indicator IN BSC_KPIS_B.indicator%TYPE
1444 ,x_return_status OUT NOCOPY VARCHAR2
1445 ,x_msg_count OUT NOCOPY NUMBER
1446 ,x_msg_data OUT NOCOPY VARCHAR2
1447 ) IS
1448
1449 CURSOR c_kpi_ids IS
1450 SELECT indicator,short_name
1451 FROM BSC_KPIS_B
1452 WHERE Source_Indicator = p_indicator
1453 AND config_type =7
1454 AND Prototype_Flag <> 2;
1455
1456 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
1457 l_Table_Number NUMBER;
1458 l_kpi_metadata_tables BSC_DESIGNER_PVT.t_kpi_metadata_tables;
1459 l_region_code AK_REGIONS.region_code%TYPE;
1460 l_function_id FND_FORM_FUNCTIONS.function_id%TYPE;
1461 l_source_kpi BSC_KPIS_B.indicator%TYPE;
1462 l_target_kpi BSC_KPIS_B.indicator%TYPE;
1463 l_default_node BSC_SYS_DATASETS_VL.dataset_id%TYPE;
1464 l_count NUMBER;
1465 l_shared_Obj_Tbl BSC_SIMULATION_VIEW_PVT.Bsc_Shared_Obj_Tbl_Type ;
1466 l_function_name FND_FORM_FUNCTIONS.function_name%TYPE;
1467
1468 BEGIN
1469
1470 --First delete the entries from bsc_tab_view_labels,bsc_kpi_tree_nodes,ak_region tables
1471 --
1472 --then copy the data to the shared objectives
1473 --flag the objective to the same flag as master
1474 -- also copy the default node value to the shared objective
1475 SAVEPOINT HandleSharedObject;
1476 FND_MSG_PUB.INITIALIZE;
1477 x_return_status := FND_API.G_RET_STS_SUCCESS;
1478 l_count := 0;
1479
1480 FOR cd IN c_kpi_ids LOOP
1481
1482 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := cd.indicator;
1483 l_shared_Obj_Tbl(l_count).region_code := cd.short_name;
1484 l_shared_Obj_Tbl(l_count).target_kpi := l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
1485
1486 l_shared_Obj_Tbl(l_count).function_id := BSC_BIS_KPI_CRUD_PUB.Get_Function_Id_By_Name
1487 (
1488 p_kpi_portlet_function_name => cd.short_name
1489 );
1490 l_count := l_count + 1;
1491
1492 BSC_KPI_PUB.Delete_Sim_Tree_Data
1493 (
1494 p_commit => FND_API.G_FALSE
1495 , p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
1496 , x_return_status => x_return_status
1497 , x_msg_count => x_msg_count
1498 , x_msg_data => x_msg_data
1499 );
1500
1501 IF (x_return_status IS NOT NULL AND x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1503 END IF;
1504
1505 END LOOP;
1506
1507 IF(l_count >0) THEN
1508
1509 --Now we will initialize the tables from where we need to copy the data to sim objectives
1510
1511 BSC_APPS.Init_Bsc_Apps;
1512
1513 BSC_SIMULATION_VIEW_PVT.Init_Sim_Tables_Array
1514 (
1515 p_copy_Ak_Tables => FND_API.G_TRUE
1516 ,x_Table_Number => l_Table_Number
1517 ,x_kpi_metadata_tables => l_kpi_metadata_tables
1518 );
1519
1520 l_source_kpi := p_indicator;
1521 l_default_node := Get_Default_Node(p_indicator => p_indicator);
1522
1523 FOR i IN 0..l_shared_Obj_Tbl.COUNT - 1 LOOP
1524 l_region_code := l_shared_Obj_Tbl(i).region_code;
1525 l_target_kpi := l_shared_Obj_Tbl(i).target_kpi;
1526 l_function_id := l_shared_Obj_Tbl(i).function_id ;
1527
1528 FOR i_index IN 1..l_Table_Number LOOP
1529 IF(l_kpi_metadata_tables(i_index).duplicate_data = bsc_utility.YES AND (l_kpi_metadata_tables(i_index).table_type<>BSC_SIMULATION_VIEW_PVT.C_AK_TABLE AND l_kpi_metadata_tables(i_index).table_type<>BSC_SIMULATION_VIEW_PVT.C_FORM_TABLE)) THEN
1530 Copy_Record_Table(l_kpi_metadata_tables(i_index).table_name,l_kpi_metadata_tables(i_index).table_type,l_kpi_metadata_tables(i_index).table_column, l_source_kpi, l_target_kpi);
1531 ELSIF(l_kpi_metadata_tables(i_index).duplicate_data = bsc_utility.YES AND l_kpi_metadata_tables(i_index).table_type=BSC_SIMULATION_VIEW_PVT.C_AK_TABLE) THEN
1532 Copy_Ak_Record_Table(l_kpi_metadata_tables(i_index).table_name,l_kpi_metadata_tables(i_index).table_type,l_kpi_metadata_tables(i_index).table_column, l_source_kpi, l_target_kpi,l_region_code,NULL);
1533 ELSIF(l_kpi_metadata_tables(i_index).duplicate_data = bsc_utility.YES AND l_kpi_metadata_tables(i_index).table_type=BSC_SIMULATION_VIEW_PVT.C_FORM_TABLE)THEN
1534 Copy_Ak_Record_Table(l_kpi_metadata_tables(i_index).table_name,l_kpi_metadata_tables(i_index).table_type,l_kpi_metadata_tables(i_index).table_column, l_source_kpi, l_target_kpi,l_region_code,l_function_id);
1535 END IF;
1536 END LOOP;
1537
1538 UPDATE ak_regions
1539 SET attribute8 = l_target_kpi || '.'||BSC_SIMULATION_VIEW_PVT.C_DEFAULT_ANA_OPTION
1540 WHERE region_code =l_region_code;
1541
1542 IF(l_default_node IS NOT NULL) THEN
1543
1544 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Code := BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID;
1545 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Value := l_default_node;
1546 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Secondary_Value := BSC_SIMULATION_VIEW_PUB.C_EMPTY;
1547 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := l_target_kpi;
1548
1549 BSC_KPI_PVT.Update_Kpi_Properties
1550 ( p_commit => FND_API.G_FALSE
1551 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
1552 ,x_return_status => x_return_status
1553 ,x_msg_count => x_msg_count
1554 ,x_msg_data => x_msg_data
1555 );
1556 IF (x_return_status IS NOT NULL AND x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1557 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1558 END IF;
1559 END IF;
1560 END LOOP;
1561 END IF;
1562 EXCEPTION
1563 WHEN FND_API.G_EXC_ERROR THEN
1564 ROLLBACK TO HandleSharedObject;
1565 FND_MSG_PUB.Count_And_Get
1566 ( p_encoded => FND_API.G_FALSE
1567 , p_count => x_msg_count
1568 , p_data => x_msg_data
1569 );
1570 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1571 x_return_status := FND_API.G_RET_STS_ERROR;
1572 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1573 ROLLBACK TO HandleSharedObject;
1574 FND_MSG_PUB.Count_And_Get
1575 ( p_encoded => FND_API.G_FALSE
1576 , p_count => x_msg_count
1577 , p_data => x_msg_data
1578 );
1579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1580 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1581 WHEN NO_DATA_FOUND THEN
1582 ROLLBACK TO HandleSharedObject;
1583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1584 IF (x_msg_data IS NOT NULL) THEN
1585 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Handle_Shared_Objectives ';
1586 ELSE
1587 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Handle_Shared_Objectives ';
1588 END IF;
1589 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1590 WHEN OTHERS THEN
1591 ROLLBACK TO HandleSharedObject;
1592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1593 IF (x_msg_data IS NOT NULL) THEN
1594 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Handle_Shared_Objectives ';
1595 ELSE
1596 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Handle_Shared_Objectives ';
1597 END IF;
1598 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1599 END Handle_Shared_Objectives;
1600
1601 /************************************************************************************
1602 -- API name : Copy_Dimension_Group
1603 -- Type : Private
1604 -- Function :
1605 -- This API creates the dimension group for the Simulation Report
1606 -- It will also attach all the chosen dimension objects to this group
1607 ************************************************************************************/
1608
1609 PROCEDURE Copy_Dimension_Group (
1610 p_commit IN VARCHAR2 := FND_API.G_FALSE
1611 , p_Indicator IN NUMBER
1612 , p_Region_Code IN VARCHAR2
1613 , p_Old_Region_Code IN VARCHAR2
1614 , p_New_Dim_Levels IN FND_TABLE_OF_NUMBER
1615 , p_DbLink_Name IN VARCHAR2
1616 , x_return_status OUT NOCOPY VARCHAR2
1617 , x_msg_count OUT NOCOPY NUMBER
1618 , x_msg_data OUT NOCOPY VARCHAR2
1619 ) IS
1620 l_Count NUMBER := 0;
1621 l_colum VARCHAR2(100);
1622 l_key_name VARCHAR2(30);
1623 l_table_name all_tables.table_name%TYPE;
1624 l_condition VARCHAR2(1000);
1625 l_arr_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1626 l_num_columns NUMBER;
1627 i NUMBER;
1628 l_Dim_Group_Id NUMBER;
1629 l_sql VARCHAR2(32000);
1630 TYPE c_cur_type IS REF CURSOR;
1631 c_cursor c_cur_type;
1632 l_DimObj_Sht_Names VARCHAR2(32000);
1633 l_kpi_metadata_tables BSC_DESIGNER_PVT.t_kpi_metadata_tables;
1634 l_Bsc_Group_Id bsc_sys_dim_groups_tl.dim_group_id%TYPE;
1635 l_Bis_Group_Id bis_dimensions.dimension_id%TYPE;
1636
1637
1638 CURSOR c_DimObjShtNames IS
1639 SELECT
1640 short_name
1641 FROM
1642 bsc_sys_dim_levels_vl
1643 WHERE
1644 dim_level_id IN (SELECT DISTINCT
1645 column_value
1646 FROM
1647 TABLE(CAST(p_New_Dim_Levels AS FND_TABLE_OF_NUMBER)));
1648
1649 BEGIN
1650 FND_MSG_PUB.Initialize;
1651 x_return_status := FND_API.G_RET_STS_SUCCESS;
1652
1653 SAVEPOINT BscSimCopyDimGrp;
1654
1655 SELECT
1656 COUNT(1)
1657 INTO
1658 l_Count
1659 FROM
1660 bsc_sys_dim_groups_vl
1661 WHERE
1662 short_name = p_Region_Code;
1663
1664 IF l_Count > 0 THEN
1665 BSC_APPS.Write_Line_Log('Dimension with short Name[ ' ||p_Region_Code||'] already exists' , BSC_APPS.OUTPUT_FILE);
1666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1667 END IF;
1668
1669 BSC_BIS_DIMENSION_PUB.Create_Dimension (
1670 p_commit => FND_API.G_FALSE
1671 ,p_dim_short_name => p_Region_Code
1672 ,p_display_name => p_Region_Code
1673 ,p_description => p_Region_Code
1674 ,p_dim_obj_short_names => NULL
1675 ,p_application_id => 271
1676 ,p_create_view => 1
1677 ,p_hide => FND_API.G_TRUE
1678 ,x_return_status => x_return_status
1679 ,x_msg_count => x_msg_count
1680 ,x_msg_data => x_msg_data
1681 );
1682 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1683 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1684 END IF;
1685
1686 FOR cd IN c_DimObjShtNames LOOP
1687 IF l_DimObj_Sht_Names IS NULL THEN
1688 l_DimObj_Sht_Names := cd.short_name || ',';
1689 ELSE
1690 l_DimObj_Sht_Names := l_DimObj_Sht_Names || cd.short_name || ',';
1691 END IF;
1692 END LOOP;
1693 IF LENGTH(l_DimObj_Sht_Names) > 1 THEN
1694
1695 BSC_BIS_DIMENSION_PUB.Assign_Dimension_Objects (
1696 p_commit => FND_API.G_FALSE
1697 ,p_dim_short_name => p_Region_Code
1698 ,p_dim_obj_short_names => l_DimObj_Sht_Names
1699 ,p_create_view => 1
1700 ,p_Restrict_Dim_Validate => NULL
1701 ,x_return_status => x_return_status
1702 ,x_msg_count => x_msg_count
1703 ,x_msg_data => x_msg_data
1704 );
1705 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1707 END IF;
1708
1709 END IF;
1710
1711 IF (p_commit = FND_API.G_TRUE) THEN
1712 COMMIT;
1713 END IF;
1714
1715 EXCEPTION
1716 WHEN FND_API.G_EXC_ERROR THEN
1717 ROLLBACK TO BscSimCopyDimGrp;
1718 IF (x_msg_data IS NULL) THEN
1719 FND_MSG_PUB.Count_And_Get
1720 ( p_encoded => FND_API.G_FALSE
1721 , p_count => x_msg_count
1722 , p_data => x_msg_data
1723 );
1724 END IF;
1725 x_return_status := FND_API.G_RET_STS_ERROR;
1726 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1727 ROLLBACK TO BscSimCopyDimGrp;
1728 IF (x_msg_data IS NULL) THEN
1729 FND_MSG_PUB.Count_And_Get
1730 ( p_encoded => FND_API.G_FALSE
1731 , p_count => x_msg_count
1732 , p_data => x_msg_data
1733 );
1734 END IF;
1735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1736 WHEN NO_DATA_FOUND THEN
1737 ROLLBACK TO BscSimCopyDimGrp;
1738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1739 IF (x_msg_data IS NOT NULL) THEN
1740 x_msg_data := x_msg_data || ' ->BSC_SIMULATION_VIEW_PVT.Copy_Dimension_Group ';
1741 ELSE
1742 x_msg_data := SQLERRM || 'at BSC_SIMULATION_VIEW_PVT.Copy_Dimension_Group ';
1743 END IF;
1744 WHEN OTHERS THEN
1745 ROLLBACK TO BscSimCopyDimGrp;
1746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1747 IF (x_msg_data IS NOT NULL) THEN
1748 x_msg_data := x_msg_data || ' ->BSC_SIMULATION_VIEW_PVT.Copy_Dimension_Group ';
1749 ELSE
1750 x_msg_data := SQLERRM || ' at BSC_SIMULATION_VIEW_PVT.Copy_Dimension_Group ';
1751 END IF;
1752 END Copy_Dimension_Group;
1753
1754 /***************************************
1755 Set_Sim_Key_Values : API used to set the key item value for the simulation tree
1756 input : Takes Sim Objective short_name
1757 creator : ashankar 26-03-07
1758 /****************************************/
1759
1760 PROCEDURE Set_Sim_Key_Values
1761 (
1762 p_ind_Sht_Name IN BSC_KPIS_B.short_name%TYPE
1763 ,p_indicator IN BSC_KPIS_B.indicator%TYPE
1764 ,x_return_status OUT NOCOPY VARCHAR2
1765 ,x_msg_count OUT NOCOPY NUMBER
1766 ,x_msg_data OUT NOCOPY VARCHAR2
1767 ) IS
1768
1769 CURSOR c_form_functions IS
1770 SELECT parameters
1771 FROM fnd_form_functions_vl
1772 WHERE function_name =p_ind_Sht_Name;
1773
1774 l_parameters FND_FORM_FUNCTIONS_VL.parameters%TYPE;
1775 l_dim_dimobjs_record BSC_BIS_KPI_CRUD_PUB.BSC_VARCHAR2_TBL_TYPE;
1776 l_non_time_counter NUMBER;
1777 l_attribute2 ak_region_items_vl.attribute2%TYPE;
1778 l_default_value NUMBER;
1779 l_dim_sht_name BSC_SYS_DIM_GROUPS_VL.short_name%TYPE;
1780 l_dim_obj_sht_name BSC_SYS_DIM_LEVELS_VL.short_name%TYPE;
1781 l_dim_set_id NUMBER :=0;
1782
1783 BEGIN
1784 FND_MSG_PUB.INITIALIZE;
1785 x_return_status := FND_API.G_RET_STS_SUCCESS;
1786 IF(p_ind_Sht_Name IS NOT NULL)THEN
1787
1788 FOR cd IN c_form_functions LOOP
1789 l_parameters := cd.parameters;
1790 END LOOP;
1791 IF(l_parameters IS NOT NULL)THEN
1792
1793 BSC_BIS_KPI_CRUD_PUB.Get_Non_Time_Dim_And_DimObjs
1794 (
1795 p_region_code => p_ind_Sht_Name
1796 ,x_non_time_dim_dimObjs => l_dim_dimobjs_record
1797 ,x_non_time_counter => l_non_time_counter
1798 );
1799 IF(l_non_time_counter >0)THEN
1800 FOR l_Index IN 1..l_non_time_counter LOOP
1801 l_attribute2 := l_dim_dimobjs_record(l_Index);
1802 l_default_value := BIS_UTIL.Get_Default_Value_From_Params
1803 (
1804 p_parameters => l_parameters
1805 ,p_attribute2 => l_attribute2
1806 );
1807 --IF l_default_value IS NULL it means key items are removed.
1808 l_dim_obj_sht_name:=SUBSTR(l_attribute2,INSTR(l_attribute2,BIS_UTIL.
1809 C_CHAR_PLUS)+1,LENGTH(l_attribute2));
1810
1811 BSC_DEFAULT_KEY_ITEM_PUB.Set_Key_Item_Value
1812 (
1813 p_indicator => p_indicator
1814 , p_dim_id => BSC_SIMULATION_VIEW_PUB.c_SIM_DIM_SET
1815 , p_dim_obj_sht_name=> l_dim_obj_sht_name
1816 , p_key_value => l_default_value
1817 , x_return_status => x_return_status
1818 , x_msg_count => x_msg_count
1819 , x_msg_data => x_msg_data
1820 );
1821
1822 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS)THEN
1823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1824 END IF;
1825 END LOOP;
1826 END IF;
1827 END IF;
1828 END IF;
1829 EXCEPTION
1830 WHEN FND_API.G_EXC_ERROR THEN
1831 FND_MSG_PUB.Count_And_Get
1832 ( p_encoded => FND_API.G_FALSE
1833 , p_count => x_msg_count
1834 , p_data => x_msg_data
1835 );
1836 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1837 x_return_status := FND_API.G_RET_STS_ERROR;
1838 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1839 FND_MSG_PUB.Count_And_Get
1840 ( p_encoded => FND_API.G_FALSE
1841 , p_count => x_msg_count
1842 , p_data => x_msg_data
1843 );
1844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1845 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1846 WHEN NO_DATA_FOUND THEN
1847 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1848 IF (x_msg_data IS NOT NULL) THEN
1849 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Set_Sim_Key_Values ';
1850 ELSE
1851 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Set_Sim_Key_Values ';
1852 END IF;
1853 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1854 WHEN OTHERS THEN
1855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1856 IF (x_msg_data IS NOT NULL) THEN
1857 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Set_Sim_Key_Values ';
1858 ELSE
1859 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Set_Sim_Key_Values ';
1860 END IF;
1861 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1862
1863 END Set_Sim_Key_Values;
1864
1865 END BSC_SIMULATION_VIEW_PVT;