[Home] [Help]
PACKAGE BODY: APPS.BSC_BIS_MEASURE_PUB
Source
1 PACKAGE BODY BSC_BIS_MEASURE_PUB AS
2 /* $Header: BSCPBMSB.pls 120.15 2007/06/08 08:59:32 akoduri ship $ */
3 --- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
4 --- All rights reserved.
5 ---
6 ---==========================================================================
7 --- FILENAME
8 ---
9 --- BSCPBMSB.pls
10 ---
11 --- DESCRIPTION
12 --- Package Body File for Measure transactions
13 ---
14 --- NOTES
15 ---
16 --- HISTORY
17 ---
18 --- 23-Apr-2003 mdamle Created
19 --- 14-JUN-2003 adrao Added Granular Locking for measures.
20 --- 07-JUL-2003 mdamle Added Indicator Dimensions
21 --- 24-JUL-2003 mdamle Bug#3064436 - Fix in create measure when user selects
22 --- existing datasource
23 --- 01-Aug-2003 mdamle Bug#3055812 - Trim display and internal name
24 --- 18-Aug-2003 mdamle Bug#3096594 - Added check for same cause and effect measure
25 --- 21-Aug-2003 adrao Modfied Delete_Measure to make Multiuser delete
26 --- generic
27 -- 21-AUG-2003 mahrao Fix for granular locking to come up while upload of ldt
28 -- 25-AUG-2003 mahrao Added procedure Ret_Dataset_Fr_Meas_Shrt_Name and
29 -- Order_Dimensions_For_Ldt
30 -- 01-SEP-2003 PAJOHRI Bug #3122612
31 -- Updated the API so that whenever any measure is created/updated
32 -- BSC_SYS_MEASURES.Type must be 0, for BSC type of measures
33 -- 03-Sep-03 mdamle Fixed Bug #3123734, 3123558 - Get measure col, isFormula
34 -- 07-Sep-03 arhegde bug# 3123901 Propogate error to outer layers.
35 -- 11-Sep-03 mdamle BSC_SYS_MEASURES.Type is different from BSC_DB_MEASURE_COLS.MEASURE_TYPE
36 -- 26-Sep-03 adrao Removed the logic to generate Duplicate Display Name for Measured
37 -- for Bug #3163366
38 -- 01-Oct-03 mdamle Bug#3163261 - Don't remove beginning numbers from source column name
39 -- 07-Oct-03 mdamle Bug#3170184 - For BSC type measure, always use short name in PMF display name
40 -- 21-Oct-03 PAJOHRI Bug #3184408, added two procedures get_Next_Alias and get_Measure_Name
41 -- to get the new diplay name for BIS and BSC.
42 -- 21-Oct-03 Adeulgao fixed Bug#3237284, modified update_measure API
43 -- 21-Nov-03 adrao fixed Bug#3255382
44 -- 27-Nov-03 adrao fixed Modifed Update_Measure to move the FETCH CURSOR out the
45 -- IF condition to check for %FOUND for cursor - Bug#3284277
46 -- 28-NOV-03 adrao Bug#3238554 - Modifed procedure Update_Measure and added
47 -- condition to perform incremental changes. Also modified Get_Incr_Trigger
48 -- to return a warning message, when Measure type is changed.
49 -- 03-DEC-03 adrao Bug#3292146 - Fixed Create_Measure, to handle Measure_id = -1, when
50 -- default Datasource Values are selected.
51 -- 04-DEC-03 adrao Bug#3296451 - Fixed API Get_Incr_Trigger to return null when
52 -- exception is raised.
53 -- 06-JAN-04 PAJOHRI Bug #3349897, modified procedure Update_Measure to get the previous
54 -- value of s_Color_Formula and function getColorFormula
55 -- to use s_Color_Formula original value.
56 -- 24-FEB-04 KYADAMAK Bug #3439942 space not allowed for PMF Measures
57 -- 24-MAR-04 adrao Bug#3528425 - passed Bsc_Measure_Group_Id to lower APIs
58 -- 25-APR-04 arhegde bug# 3546722 - removed NVL in update_measure call from load_measure API
59 -- 08-APR-04 ankgoel Modified for bug#3557236
60 -- 13-APR-04 ppandey Bug# 3530050- Dynamically generating unique measure col if not unique
61 -- 11-MAY-04 kyadamak Bug# 3616756 - Changed query to get KPIs affected while changing the color method
62 -- 13-MAY-04 adrao Added Exception after BIS_MEASURE_PUB.Create_Measure in Create_Measure API
63 -- 24-MAY-04 adrao Delete unwanted Measure Columns based on BSC_SYS_DATASETS_VL.MEASURE_ID2
64 -- for Bug#3628113
65 -- 25-MAY-04 PAJOHRI Bug #3642186
66 -- 05-JUL-04 ankgoel Bug#3700439 Made changed for rollback issues
67 -- 28-JUL-04 adrao Bug#3798834 Made Aggr. Method change to render a Color warning
68 -- instead of Structural changes warning
69 -- 28-JUL-04 sawu Modified create/update/translate api to populate WHO column info and p_owner
70 -- 29-JUL-04 adrao Bug#3781176 - removed dangling source columns, whenever a measure
71 -- is updated with an alternative source column (datasource)
72 -- 09-AUG-04 ashankar Bug#3809014 Made chnages in Create_Measure,Update_Measure and get_Measure_Name
73 -- procedures.
74 -- 09-AUG-04 sawu Added create_measure wrapper to handle default internal name
75 -- 24-AUH-04 ashankar Bug#3844190 While deleting the measure added source =BSC.
76 -- 26-AUG-04 ankgoel Bug#3856618 Error message picked from FND stack only if NULL
77 -- 26-AUG-04 sawu Bug#3813603: added Is_Unique_Measure_Display_Name()
78 -- 30-AUG-04 ankgoel Modified Order_Dimensions_For_Ldt for bug#3846068
79 -- 01-SEP-04 sawu Bug#3859267: added region, source/compare column app
80 -- id to create/update api
81 -- 06-SEP-04 kyadamak modified get_measure_col()for bug# 3852463
82 -- 23-SEP-04 adrao modified gen_name_for_column() bug#3894955
83 -- 18-OCT-04 adrao Modified Create_Measure, Update_Measure signatures by added
84 -- p_measure_col_help to the APIs for POSCO Bug#3817894
85 -- 17-Nov-04 sawu Bug#4015015: added api Is_Numeric_Column api
86 -- 17-Dec-04 sawu Bug#4045287: added Upload_Test, added p_custom_mode
87 -- to Load_Measure() and Translate_Measure(). Overloaded
88 -- Create_Measure() and Update_Measure().
89 -- 27-Dec-04 rpenneru Bug#4080204: added Func_Area_Short_name field to create_measure()
90 -- and update_measure() methods
91 -- 03-Feb-05 krishna Bug#4080716 Modified get_measure_col_API compatiable to 8i
92 -- 09-FEB-04 skchoudh Enh#4141738 Added Functiona Area Combobox to MD
93 -- 10-Feb-05 sawu Bug#4157795: modified gen_name_for_column to trim leading underscore
94 -- 21-Feb-05 rpenneru Enh#4059160, Add FA as property to Custom KPIs|
95 -- 21-FEB-05 ankagarw changed dataset name and description column length for enh.#3862703
96 -- 05/22/05 akoduri Enhancement#3865711 -- Obsolete Seeded Objects --
97 -- 03-MAY-05 akoduri Enh #4268374 -- Weighted Average Measures --
98 -- 23-May-05 visuri Bug#3994115 Added Get_Meas_With_Src_Col() and Get_Sing_Par_Meas_DS()
99 -- 17-JUL-05 sawu Bug#4482736: Added Get_Primary_Data_Source
100 -- 20-Sep-05 akoduri Bug#4613172: CDS type measures should not get populated into
101 -- bsc_db_measure_cols_tl
102 -- 22-Sep-05 ashankar Bug#4605142:Modified the API Get_Incr_Truigger
103 -- 21-oct-05 ashankar Bug#4630974 Modified the API Get_Incr_Trigger
104 -- by moving the check for structural modifications
105 -- before color changes
106 -- 17-Nov-05 adrao added API Is_Formula_Type() Bug#4617140
107 -- 05-JAN-06 ppandey Enh#4860106 - Handled structureal/non-structural formula change
108 -- 12-JAN-06 ppandey Bug #4938364 - Color Warning for BIS Measure (AG)
109 -- 29-MAR-06 adrao Bug#5071121 - added additional conditions when converting a report
110 -- from single source to formula type in Update_Measure();
111 -- 04-AUG-06 akoduri Enh#5416542 Cause Effect Phase2
112 -- 14-Feb-07 rkumar Bug#5877454 increased the variable lengths to
113 -- support larger kpi names
114 -- 06-JUN-2007 akoduri Bug 5958688 Enable YTD as default at KPI
115 ---===========================================================================
116
117 /*
118 ***************************************************
119 function remove_percent()
120 ***************************************************
121 */
122
123 function remove_percent(
124 p_input in varchar2
125 ) return number;
126
127 FUNCTION gen_name_for_column(
128 p_name IN VARCHAR2
129 )RETURN VARCHAR2;
130
131 FUNCTION is_Valid_AlphaNum
132 (
133 p_name IN VARCHAR2
134 ) RETURN BOOLEAN;
135
136 FUNCTION getMeasureAutoGenKpis (
137 p_dataset_id IN NUMBER
138 ) RETURN VARCHAR2;
139
140
141 /******************* PAJOHRI ADDED Bug #3184408*************************/
142 FUNCTION get_Next_Alias
143 (
144 p_Alias IN VARCHAR2
145 ) RETURN VARCHAR2
146 IS
147 l_alias VARCHAR2(3);
148 l_return VARCHAR2(3);
149 l_count NUMBER;
150 BEGIN
151 IF (p_Alias IS NULL) THEN
152 l_return := 'A';
153 ELSE
154 l_count := LENGTH(p_Alias);
155 IF (l_count = 1) THEN
156 l_return := 'A0';
157 ELSIF (l_count > 1) THEN
158 l_alias := SUBSTR(p_Alias, 2);
159 l_count := TO_NUMBER(l_alias)+1;
160 l_return := 'A'||TO_CHAR(l_count);
161 END IF;
162 END IF;
163 RETURN l_return;
164
165 END get_Next_Alias;
166
167 /************************************************************************/
168 FUNCTION Validate_Conditions
169 ( p_Bsc_source IN VARCHAR2 -- BSC or PMF
170 , p_Pmf_Old_source IN VARCHAR2 -- OLTP, EDW (NULL means OLTP)
171 , p_Bsc_Old_Source IN VARCHAR2 -- BSC or PMF
172 ) RETURN BOOLEAN IS
173 BEGIN
174 IF ((p_Bsc_source = p_Bsc_Old_Source) AND (p_Bsc_source = c_PMF)) THEN
175 IF (NVL(p_Pmf_Old_source, 'OLTP') = 'OLTP') THEN -- from PMD only OLTP types are created
176 --raise exception
177 RETURN FALSE;
178 ELSE
179 RETURN TRUE;
180 END IF;
181 ELSIF (p_Bsc_source = p_Bsc_Old_Source) THEN
182 --raise exception
183 RETURN FALSE;
184 END IF;
185 RETURN TRUE;
186 END Validate_Conditions;
187 /************************************************************************/
188 PROCEDURE get_Measure_Name
189 ( p_dataset_id IN NUMBER -- if NULL it means Create otherwise update
190 , p_ui_flag IN VARCHAR2
191 , p_dataset_source IN VARCHAR2 -- BSC or PMF
192 , p_dataset_name IN VARCHAR2 -- passed measure name
193 , x_measure_name OUT NOCOPY VARCHAR2 -- trimmed output measure name
194 ) IS
195 l_Flag BOOLEAN;
196 l_Count NUMBER;
197
198
199 CURSOR c_Create_Measure IS
200 SELECT DISTINCT BSC_MEAS.Source Bsc_Source
201 , BSC_DSET.Name Bsc_Name
202 , BIS_TAR.Source Bis_Source
203 , BIS_IND.Indicator_Id Bis_Ind_Id
204 , BIS_IND.Actual_Data_Source Bis_Act_Source
205 FROM BIS_INDICATORS BIS_IND
206 , BSC_SYS_MEASURES BSC_MEAS
207 , BSC_SYS_DATASETS_VL BSC_DSET
208 , BIS_TARGET_LEVELS BIS_TAR
209 WHERE UPPER(TRIM(BSC_DSET.Name)) = UPPER(x_measure_name)
210 AND BIS_IND.Indicator_Id = BIS_TAR.Indicator_Id(+)
211 AND BIS_IND.Short_Name = BSC_MEAS.Short_Name
212 AND BSC_MEAS.Measure_Id = BSC_DSET.Measure_Id1;
213
214 CURSOR c_Update_Measure IS
215 SELECT DISTINCT BSC_MEAS.Source Bsc_Source
216 , BSC_DSET.Name Bsc_Name
217 , BIS_TAR.Source Bis_Source
218 , BIS_IND.Indicator_Id Bis_Ind_Id
219 , BIS_IND.Actual_Data_Source Bis_Act_Source
220 FROM BIS_INDICATORS BIS_IND
221 , BSC_SYS_MEASURES BSC_MEAS
222 , BSC_SYS_DATASETS_VL BSC_DSET
223 , BIS_TARGET_LEVELS BIS_TAR
224 WHERE UPPER(TRIM(BSC_DSET.Name)) = UPPER(x_measure_name)
225 AND BIS_IND.Indicator_Id = BIS_TAR.Indicator_Id(+)
226 AND BIS_IND.Short_Name = BSC_MEAS.Short_Name
227 AND BSC_MEAS.Measure_Id = BSC_DSET.Measure_Id1
228 AND BSC_DSET.Dataset_Id <> p_dataset_id;
229
230 BEGIN
231 -- x_measure_name := TRIM(p_dataset_name);
232 l_Flag := FALSE;
233
234 IF (p_dataset_id IS NULL) THEN -- called from update API
235 IF(p_ui_flag = 'Y') THEN
236 x_measure_name := TRIM(p_dataset_name);
237 ELSE
238 x_measure_name := p_dataset_name;
239 END IF;
240
241 IF(p_dataset_source = c_BSC) THEN
242
243 SELECT COUNT(0)
244 INTO l_Count
245 FROM BSC_SYS_DATASETS_VL
246 WHERE UPPER(TRIM(Name)) =UPPER(x_measure_name)
247 AND Source = c_BSC;
248
249 IF(l_Count>0) THEN
250 FND_MESSAGE.SET_NAME('BIS','BIS_MEASURE_NAME_UNIQUE');
251 FND_MSG_PUB.ADD;
252 RAISE FND_API.G_EXC_ERROR;
253 END IF;
254 ELSE
255 FOR cd IN c_Create_Measure LOOP
256 l_Flag := BSC_BIS_MEASURE_PUB.Validate_Conditions
257 ( p_Bsc_source => p_dataset_source
258 , p_Pmf_Old_source => cd.Bis_Source
259 , p_Bsc_Old_Source => cd.Bsc_Source
260 );
261 IF (NOT l_Flag) THEN
262 FND_MESSAGE.SET_NAME('BIS','BIS_MEASURE_NAME_UNIQUE');
263 FND_MSG_PUB.ADD;
264 RAISE FND_API.G_EXC_ERROR;
265 END IF;
266 END LOOP;
267 END IF;
268 ELSE -- called from create API
269 x_measure_name := TRIM(p_dataset_name);
270 IF(p_dataset_source = c_BSC) THEN
271
272 SELECT COUNT(0)
273 INTO l_Count
274 FROM BSC_SYS_DATASETS_VL
275 WHERE UPPER(TRIM(Name)) =UPPER(x_measure_name)
276 AND Source = c_BSC
277 AND Dataset_id <> p_dataset_id;
278
279 IF(l_Count>0) THEN
280 FND_MESSAGE.SET_NAME('BIS','BIS_MEASURE_NAME_UNIQUE');
281 FND_MSG_PUB.ADD;
282 RAISE FND_API.G_EXC_ERROR;
283 END IF;
284 ELSE
285 FOR cd IN c_Update_Measure LOOP
286 l_Flag := BSC_BIS_MEASURE_PUB.Validate_Conditions
287 ( p_Bsc_source => p_dataset_source
288 , p_Pmf_Old_source => cd.Bis_Source
289 , p_Bsc_Old_Source => cd.Bsc_Source
290 );
291 IF (NOT l_Flag) THEN
292 FND_MESSAGE.SET_NAME('BIS','BIS_MEASURE_NAME_UNIQUE');
293 FND_MSG_PUB.ADD;
294 RAISE FND_API.G_EXC_ERROR;
295 END IF;
296 END LOOP;
297 END IF;
298 END IF;
299
300 END get_Measure_Name;
301 /************************************************************************/
302
303 /************************************************************************/
304 -- wrapper of Create_Measure that takes in p_default_short_name parameter
305 /************************************************************************/
306 procedure Create_Measure(
307 p_commit IN VARCHAR2 := FND_API.G_FALSE
308 ,x_dataset_id OUT NOCOPY NUMBER
309 ,p_dataset_source IN VARCHAR2
310 ,p_dataset_name IN VARCHAR2
311 ,p_dataset_help IN VARCHAR2 := NULL
312 ,p_dataset_measure_id1 IN NUMBER := NULL
313 ,p_dataset_operation IN VARCHAR2 := NULL
314 ,p_dataset_measure_id2 IN NUMBER := NULL
315 ,p_dataset_format_id IN NUMBER := NULL
316 ,p_dataset_color_method IN NUMBER := NULL
317 ,p_dataset_autoscale_flag IN NUMBER := NULL
318 ,p_dataset_projection_flag IN NUMBER := NULL
319 ,p_measure_short_name IN VARCHAR2
320 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type := -1
321 ,p_source_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
322 ,p_compare_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
323 ,p_measure_act_data_src_type IN VARCHAR2 := NULL
324 ,p_measure_act_data_src IN VARCHAR2 := NULL
325 ,p_measure_comparison_source IN VARCHAR2 := NULL
326 ,p_measure_operation IN VARCHAR2 := c_SUM
327 ,p_measure_uom_class IN VARCHAR2 := NULL
328 ,p_measure_increase_in_measure IN VARCHAR2 := NULL
329 ,p_measure_random_style IN NUMBER := NULL
330 ,p_measure_min_act_value IN NUMBER := NULL
331 ,p_measure_max_act_value IN NUMBER := NULL
332 ,p_measure_min_bud_value IN NUMBER := NULL
333 ,p_measure_max_bud_value IN NUMBER := NULL
334 ,p_measure_app_id IN NUMBER := NULL
335 ,p_measure_col IN VARCHAR2 := NULL
336 ,p_measure_col_help IN VARCHAR2 := NULL
337 ,p_measure_group_id IN NUMBER := NULL
338 ,p_measure_projection_id IN NUMBER := NULL
339 ,p_measure_type IN NUMBER := NULL
340 ,p_measure_apply_rollup IN VARCHAR2 := NULL
341 ,p_measure_function_name IN VARCHAR2 := NULL
342 ,p_measure_enable_link IN VARCHAR2 := NULL
343 ,p_measure_obsolete IN VARCHAR2 := FND_API.G_FALSE
344 ,p_type IN VARCHAR2 := NULL -- This is used for weighted kpis,This can take values CDS_SCORE,CDS_PERF or Null
345 ,p_measure_is_validate IN VARCHAR2 := FND_API.G_TRUE -- ankgoel: bug#3557236
346 ,p_dimension1_id IN NUMBER
347 ,p_dimension2_id IN NUMBER
348 ,p_dimension3_id IN NUMBER
349 ,p_dimension4_id IN NUMBER
350 ,p_dimension5_id IN NUMBER
351 ,p_dimension6_id IN NUMBER
352 ,p_dimension7_id IN NUMBER
353 ,p_y_axis_title IN VARCHAR2 := NULL
354 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
355 ,p_ui_flag IN VARCHAR2
356 ,p_is_default_short_name IN VARCHAR2
357 ,p_func_area_short_name IN VARCHAR2 := NULL
358 ,x_return_status OUT NOCOPY VARCHAR2
359 ,x_msg_count OUT NOCOPY NUMBER
360 ,x_msg_data OUT NOCOPY VARCHAR2
361 ) is
362 l_measure_short_name BIS_INDICATORS.short_name%TYPE;
363 l_temp_var BIS_INDICATORS.short_name%TYPE;
364 l_alias VARCHAR2(5);
365 l_flag BOOLEAN;
366 l_count NUMBER;
367 begin
368 SAVEPOINT SP_CREATE_MEASURE;
369
370 x_return_status := FND_API.G_RET_STS_SUCCESS;
371 fnd_msg_pub.initialize;
372
373 l_measure_short_name := p_measure_short_name;
374 IF (UPPER(p_is_default_short_name) = 'T') THEN
375 --check for unqiue short name, if not unique, provide a unique one
376 l_flag := TRUE;
377 l_alias := NULL;
378 l_temp_var := l_measure_short_name;
379 WHILE (l_flag) LOOP
380 SELECT count(1) INTO l_count
381 FROM BIS_INDICATORS
382 WHERE UPPER(TRIM(Short_Name)) = UPPER(TRIM(l_temp_var));
383 IF (l_count = 0) THEN
384 l_flag := FALSE;
385 l_measure_short_name := l_temp_var;
386 END IF;
387 l_alias := BSC_BIS_MEASURE_PUB.get_Next_Alias(l_alias);
388 l_temp_var := l_measure_short_name||l_alias;
389 END LOOP;
390 END IF;
391
392 --dispatch create_measure
393 BSC_BIS_MEASURE_PUB.Create_Measure(
394 p_commit => p_commit
395 ,x_dataset_id => x_dataset_id
396 ,p_dataset_source => p_dataset_source
397 ,p_dataset_name => p_dataset_name
398 ,p_dataset_help => p_dataset_help
399 ,p_dataset_measure_id1 => p_dataset_measure_id1
400 ,p_dataset_operation => p_dataset_operation
401 ,p_dataset_measure_id2 => p_dataset_measure_id2
402 ,p_dataset_format_id => p_dataset_format_id
403 ,p_dataset_color_method => p_dataset_color_method
404 ,p_dataset_autoscale_flag => p_dataset_autoscale_flag
405 ,p_dataset_projection_flag => p_dataset_projection_flag
406 ,p_measure_short_name => l_measure_short_name
407 ,p_region_app_id => p_region_app_id
408 ,p_source_column_app_id => p_source_column_app_id
409 ,p_compare_column_app_id => p_compare_column_app_id
410 ,p_measure_act_data_src_type => p_measure_act_data_src_type
411 ,p_measure_act_data_src => p_measure_act_data_src
412 ,p_measure_comparison_source => p_measure_comparison_source
413 ,p_measure_operation => p_measure_operation
414 ,p_measure_uom_class => p_measure_uom_class
415 ,p_measure_increase_in_measure => p_measure_increase_in_measure
416 ,p_measure_random_style => p_measure_random_style
417 ,p_measure_min_act_value => p_measure_min_act_value
418 ,p_measure_max_act_value => p_measure_max_act_value
419 ,p_measure_min_bud_value => p_measure_min_bud_value
420 ,p_measure_max_bud_value => p_measure_max_bud_value
421 ,p_measure_app_id => p_measure_app_id
422 ,p_measure_col => p_measure_col
423 ,p_measure_col_help => p_measure_col_help
424 ,p_measure_group_id => p_measure_group_id
425 ,p_measure_projection_id => p_measure_projection_id
426 ,p_measure_type => p_measure_type
427 ,p_measure_apply_rollup => p_measure_apply_rollup
428 ,p_measure_function_name => p_measure_function_name
429 ,p_measure_enable_link => p_measure_enable_link
430 ,p_measure_obsolete => p_measure_obsolete
431 ,p_type => p_type
432 ,p_measure_is_validate => p_measure_is_validate
433 ,p_dimension1_id => p_dimension1_id
434 ,p_dimension2_id => p_dimension2_id
435 ,p_dimension3_id => p_dimension3_id
436 ,p_dimension4_id => p_dimension4_id
437 ,p_dimension5_id => p_dimension5_id
438 ,p_dimension6_id => p_dimension6_id
439 ,p_dimension7_id => p_dimension7_id
440 ,p_y_axis_title => p_y_axis_title
441 ,p_owner => p_owner
442 ,p_ui_flag => p_ui_flag
443 ,p_last_update_date => sysdate
444 ,p_func_area_short_name => p_func_area_short_name
445 ,x_return_status => x_return_status
446 ,x_msg_count => x_msg_count
447 ,x_msg_data => x_msg_data
448 );
449 EXCEPTION
450 WHEN FND_API.G_EXC_ERROR THEN
451 IF (x_msg_data IS NULL) THEN
452 FND_MSG_PUB.Count_And_Get
453 ( p_encoded => FND_API.G_FALSE
454 , p_count => x_msg_count
455 , p_data => x_msg_data
456 );
457 END IF;
458 ROLLBACK TO SP_CREATE_MEASURE;
459 x_return_status := FND_API.G_RET_STS_ERROR;
460 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
461 IF (x_msg_data IS NULL) THEN
462 FND_MSG_PUB.Count_And_Get
463 ( p_encoded => FND_API.G_FALSE
464 , p_count => x_msg_count
465 , p_data => x_msg_data
466 );
467 END IF;
468 ROLLBACK TO SP_CREATE_MEASURE;
469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
470 WHEN NO_DATA_FOUND THEN
471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
472 IF (x_msg_data IS NOT NULL) THEN
473 x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Create_Measure ';
474 ELSE
475 x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Create_Measure ';
476 END IF;
477 ROLLBACK TO SP_CREATE_MEASURE;
478 WHEN OTHERS THEN
479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
480 IF (x_msg_data IS NOT NULL) THEN
481 x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Create_Measure ';
482 ELSE
483 x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Create_Measure ';
484 END IF;
485 ROLLBACK TO SP_CREATE_MEASURE;
486 end Create_Measure;
487 /************************End Create_Measure wrapper****************************/
488
489
490 procedure Create_Measure(
491 p_commit IN VARCHAR2 := FND_API.G_FALSE
492 ,x_dataset_id OUT NOCOPY NUMBER
493 ,p_dataset_source IN VARCHAR2
494 ,p_dataset_name IN VARCHAR2
495 ,p_dataset_help IN VARCHAR2 := NULL
496 ,p_dataset_measure_id1 IN NUMBER := NULL
497 ,p_dataset_operation IN VARCHAR2 := NULL
498 ,p_dataset_measure_id2 IN NUMBER := NULL
499 ,p_dataset_format_id IN NUMBER := NULL
500 ,p_dataset_color_method IN NUMBER := NULL
501 ,p_dataset_autoscale_flag IN NUMBER := NULL
502 ,p_dataset_projection_flag IN NUMBER := NULL
503 ,p_measure_short_name IN VARCHAR2
504 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type := -1
505 ,p_source_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
506 ,p_compare_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
507 ,p_measure_act_data_src_type IN VARCHAR2 := NULL
508 ,p_measure_act_data_src IN VARCHAR2 := NULL
509 ,p_measure_comparison_source IN VARCHAR2 := NULL
510 ,p_measure_operation IN VARCHAR2 := c_SUM
511 ,p_measure_uom_class IN VARCHAR2 := NULL
512 ,p_measure_increase_in_measure IN VARCHAR2 := NULL
513 ,p_measure_random_style IN NUMBER := NULL
514 ,p_measure_min_act_value IN NUMBER := NULL
515 ,p_measure_max_act_value IN NUMBER := NULL
516 ,p_measure_min_bud_value IN NUMBER := NULL
517 ,p_measure_max_bud_value IN NUMBER := NULL
518 ,p_measure_app_id IN NUMBER := NULL
519 ,p_measure_col IN VARCHAR2 := NULL
520 ,p_measure_col_help IN VARCHAR2 := NULL
521 ,p_measure_group_id IN NUMBER := NULL
522 ,p_measure_projection_id IN NUMBER := NULL
523 ,p_measure_type IN NUMBER := NULL
524 ,p_measure_apply_rollup IN VARCHAR2 := NULL
525 ,p_measure_function_name IN VARCHAR2 := NULL
526 ,p_measure_enable_link IN VARCHAR2 := NULL
527 ,p_measure_obsolete IN VARCHAR2 := FND_API.G_FALSE
528 ,p_type IN VARCHAR2 := NULL -- This is used for weighted kpis,This can take values CDS_SCORE,CDS_PERF or Null
529 ,p_measure_is_validate IN VARCHAR2 := FND_API.G_TRUE -- ankgoel: bug#3557236
530 ,p_dimension1_id IN NUMBER
531 ,p_dimension2_id IN NUMBER
532 ,p_dimension3_id IN NUMBER
533 ,p_dimension4_id IN NUMBER
534 ,p_dimension5_id IN NUMBER
535 ,p_dimension6_id IN NUMBER
536 ,p_dimension7_id IN NUMBER
537 ,p_y_axis_title IN VARCHAR2 := NULL
538 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
539 ,p_ui_flag IN VARCHAR2
540 ,p_func_area_short_name IN VARCHAR2 := NULL
541 ,x_return_status OUT NOCOPY VARCHAR2
542 ,x_msg_count OUT NOCOPY NUMBER
543 ,x_msg_data OUT NOCOPY VARCHAR2
544 ) is
545 begin
546 SAVEPOINT SP_CREATE_MEASURE;
547
548 x_return_status := FND_API.G_RET_STS_SUCCESS;
549 fnd_msg_pub.initialize;
550
551 --dispatch create_measure
552 BSC_BIS_MEASURE_PUB.Create_Measure(
553 p_commit => p_commit
554 ,x_dataset_id => x_dataset_id
555 ,p_dataset_source => p_dataset_source
556 ,p_dataset_name => p_dataset_name
557 ,p_dataset_help => p_dataset_help
558 ,p_dataset_measure_id1 => p_dataset_measure_id1
559 ,p_dataset_operation => p_dataset_operation
560 ,p_dataset_measure_id2 => p_dataset_measure_id2
561 ,p_dataset_format_id => p_dataset_format_id
562 ,p_dataset_color_method => p_dataset_color_method
563 ,p_dataset_autoscale_flag => p_dataset_autoscale_flag
564 ,p_dataset_projection_flag => p_dataset_projection_flag
565 ,p_measure_short_name => p_measure_short_name
566 ,p_region_app_id => p_region_app_id
567 ,p_source_column_app_id => p_source_column_app_id
568 ,p_compare_column_app_id => p_compare_column_app_id
569 ,p_measure_act_data_src_type => p_measure_act_data_src_type
570 ,p_measure_act_data_src => p_measure_act_data_src
571 ,p_measure_comparison_source => p_measure_comparison_source
572 ,p_measure_operation => p_measure_operation
573 ,p_measure_uom_class => p_measure_uom_class
574 ,p_measure_increase_in_measure => p_measure_increase_in_measure
575 ,p_measure_random_style => p_measure_random_style
576 ,p_measure_min_act_value => p_measure_min_act_value
577 ,p_measure_max_act_value => p_measure_max_act_value
578 ,p_measure_min_bud_value => p_measure_min_bud_value
579 ,p_measure_max_bud_value => p_measure_max_bud_value
580 ,p_measure_app_id => p_measure_app_id
581 ,p_measure_col => p_measure_col
582 ,p_measure_col_help => p_measure_col_help
583 ,p_measure_group_id => p_measure_group_id
584 ,p_measure_projection_id => p_measure_projection_id
585 ,p_measure_type => p_measure_type
586 ,p_measure_apply_rollup => p_measure_apply_rollup
587 ,p_measure_function_name => p_measure_function_name
588 ,p_measure_enable_link => p_measure_enable_link
589 ,p_measure_obsolete => p_measure_obsolete
590 ,p_type => p_type
591 ,p_measure_is_validate => p_measure_is_validate
592 ,p_dimension1_id => p_dimension1_id
593 ,p_dimension2_id => p_dimension2_id
594 ,p_dimension3_id => p_dimension3_id
595 ,p_dimension4_id => p_dimension4_id
596 ,p_dimension5_id => p_dimension5_id
597 ,p_dimension6_id => p_dimension6_id
598 ,p_dimension7_id => p_dimension7_id
599 ,p_y_axis_title => p_y_axis_title
600 ,p_owner => p_owner
601 ,p_ui_flag => p_ui_flag
602 ,p_last_update_date => sysdate
603 ,p_func_area_short_name => p_func_area_short_name
604 ,x_return_status => x_return_status
605 ,x_msg_count => x_msg_count
606 ,x_msg_data => x_msg_data
607 );
608 EXCEPTION
609 WHEN FND_API.G_EXC_ERROR THEN
610 IF (x_msg_data IS NULL) THEN
611 FND_MSG_PUB.Count_And_Get
612 ( p_encoded => FND_API.G_FALSE
613 , p_count => x_msg_count
614 , p_data => x_msg_data
615 );
616 END IF;
617 ROLLBACK TO SP_CREATE_MEASURE;
618 x_return_status := FND_API.G_RET_STS_ERROR;
619 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
620 IF (x_msg_data IS NULL) THEN
621 FND_MSG_PUB.Count_And_Get
622 ( p_encoded => FND_API.G_FALSE
623 , p_count => x_msg_count
624 , p_data => x_msg_data
625 );
626 END IF;
627 ROLLBACK TO SP_CREATE_MEASURE;
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
629 WHEN NO_DATA_FOUND THEN
630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
631 IF (x_msg_data IS NOT NULL) THEN
632 x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Create_Measure ';
633 ELSE
634 x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Create_Measure ';
635 END IF;
636 ROLLBACK TO SP_CREATE_MEASURE;
637 WHEN OTHERS THEN
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 IF (x_msg_data IS NOT NULL) THEN
640 x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Create_Measure ';
641 ELSE
642 x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Create_Measure ';
643 END IF;
644 ROLLBACK TO SP_CREATE_MEASURE;
645 end CREATE_MEASURE;
646
647 --Bug#4045278: Wrapper for Create_Measure that takes in last_update_date
648 procedure Create_Measure(
649 p_commit IN VARCHAR2 := FND_API.G_FALSE
650 ,x_dataset_id OUT NOCOPY NUMBER
651 ,p_dataset_source IN VARCHAR2
652 ,p_dataset_name IN VARCHAR2
653 ,p_dataset_help IN VARCHAR2 := NULL
654 ,p_dataset_measure_id1 IN NUMBER := NULL
655 ,p_dataset_operation IN VARCHAR2 := NULL
656 ,p_dataset_measure_id2 IN NUMBER := NULL
657 ,p_dataset_format_id IN NUMBER := NULL
658 ,p_dataset_color_method IN NUMBER := NULL
659 ,p_dataset_autoscale_flag IN NUMBER := NULL
660 ,p_dataset_projection_flag IN NUMBER := NULL
661 ,p_measure_short_name IN VARCHAR2
662 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type := -1
663 ,p_source_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
664 ,p_compare_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
665 ,p_measure_act_data_src_type IN VARCHAR2 := NULL
666 ,p_measure_act_data_src IN VARCHAR2 := NULL
667 ,p_measure_comparison_source IN VARCHAR2 := NULL
668 ,p_measure_operation IN VARCHAR2 := c_SUM
669 ,p_measure_uom_class IN VARCHAR2 := NULL
670 ,p_measure_increase_in_measure IN VARCHAR2 := NULL
671 ,p_measure_random_style IN NUMBER := NULL
672 ,p_measure_min_act_value IN NUMBER := NULL
673 ,p_measure_max_act_value IN NUMBER := NULL
674 ,p_measure_min_bud_value IN NUMBER := NULL
675 ,p_measure_max_bud_value IN NUMBER := NULL
676 ,p_measure_app_id IN NUMBER := NULL
677 ,p_measure_col IN VARCHAR2 := NULL
678 ,p_measure_col_help IN VARCHAR2 := NULL
679 ,p_measure_group_id IN NUMBER := NULL
680 ,p_measure_projection_id IN NUMBER := NULL
681 ,p_measure_type IN NUMBER := NULL
682 ,p_measure_apply_rollup IN VARCHAR2 := NULL
683 ,p_measure_function_name IN VARCHAR2 := NULL
684 ,p_measure_enable_link IN VARCHAR2 := NULL
685 ,p_measure_obsolete IN VARCHAR2 := FND_API.G_FALSE
686 ,p_type IN VARCHAR2 := NULL -- This is used for weighted kpis,This can take values CDS_SCORE,CDS_PERF or Null
687 ,p_measure_is_validate IN VARCHAR2 := FND_API.G_TRUE -- ankgoel: bug#3557236
688 ,p_dimension1_id IN NUMBER
689 ,p_dimension2_id IN NUMBER
690 ,p_dimension3_id IN NUMBER
691 ,p_dimension4_id IN NUMBER
692 ,p_dimension5_id IN NUMBER
693 ,p_dimension6_id IN NUMBER
694 ,p_dimension7_id IN NUMBER
695 ,p_y_axis_title IN VARCHAR2 := NULL
696 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
697 ,p_ui_flag IN VARCHAR2 := c_UI_FLAG
698 ,p_last_update_date IN BIS_INDICATORS.LAST_UPDATE_DATE%TYPE
699 ,p_func_area_short_name IN VARCHAR2 := NULL
700 ,x_return_status OUT NOCOPY VARCHAR2
701 ,x_msg_count OUT NOCOPY NUMBER
702 ,x_msg_data OUT NOCOPY VARCHAR2
703 ) is
704
705 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
706 l_measure_rec BIS_MEASURE_PUB.Measure_rec_type;
707 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
708 l_Measure_Col_Help VARCHAR2(150);
709 l_count NUMBER;
710 l_last_update_date BIS_INDICATORS.LAST_UPDATE_DATE%TYPE;
711 begin
712 SAVEPOINT SP_CREATE_MEASURE;
713
714 x_return_status := FND_API.G_RET_STS_SUCCESS;
715 fnd_msg_pub.initialize;
716
717 l_Dataset_Rec.Bsc_Source := p_dataset_source;
718 l_Dataset_Rec.Bsc_Dataset_Help := p_dataset_help;
719 l_Dataset_Rec.Bsc_Measure_Id := p_dataset_measure_id1;
720 l_Dataset_Rec.Bsc_Measure_Id2 := p_dataset_measure_id2;
721 l_Dataset_Rec.Bsc_Dataset_Format_Id := p_dataset_format_id;
722 l_Dataset_Rec.Bsc_Dataset_Color_Method := p_dataset_color_method;
723 l_Dataset_Rec.Bsc_Dataset_Autoscale_Flag := p_dataset_autoscale_flag;
724 l_Dataset_Rec.Bsc_Dataset_Projection_Flag := p_dataset_projection_flag;
725 l_Dataset_Rec.Bsc_Dataset_Operation := p_dataset_operation;
726 l_Dataset_Rec.Bsc_Measure_Long_Name := l_Dataset_Rec.Bsc_Dataset_Name;
727 IF (l_Dataset_Rec.Bsc_Source = c_BSC) THEN
728 l_Dataset_Rec.Bsc_Meas_Type := 0;
729 END IF;
730 l_Dataset_Rec.Bsc_Measure_Projection_Id := p_measure_projection_id;
731 l_Dataset_Rec.Bsc_y_axis_Title := p_y_axis_title;
732
733 l_Dataset_Rec.Bsc_Measure_Random_Style := p_measure_random_style;
734 l_Dataset_Rec.Bsc_Measure_Max_Act_Value := p_measure_max_act_value;
735 l_Dataset_Rec.Bsc_Measure_Max_Bud_Value := p_measure_max_bud_value;
736 l_Dataset_Rec.Bsc_Measure_Min_Act_Value := p_measure_min_act_value;
737 l_Dataset_Rec.Bsc_Measure_Min_Bud_Value := p_measure_min_bud_value;
738
739 --sawu: populate WHO column
740 l_last_update_date := nvl(p_last_update_date, sysdate);
741
742 l_Dataset_Rec.Bsc_Dataset_Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
743 l_Dataset_Rec.Bsc_Dataset_Last_Update_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
744 l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := fnd_global.LOGIN_ID;
745 l_Dataset_Rec.Bsc_Dataset_Creation_Date := l_last_update_date;
746 l_Dataset_Rec.Bsc_Dataset_Last_Update_Date := l_last_update_date;
747
748 l_Dataset_Rec.Bsc_Measure_Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
749 l_Dataset_Rec.Bsc_Measure_Last_Update_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
750 l_Dataset_Rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
751 l_Dataset_Rec.Bsc_Measure_Creation_Date := l_last_update_date;
752 l_Dataset_Rec.Bsc_Measure_Last_Update_Date := l_last_update_date;
753
754 -- We dont need to lock here, since the data_source has
755 -- not been created yet.
756 IF (p_measure_short_name IS NOT NULL) THEN
757
758 l_Dataset_Rec.Bsc_Measure_Short_Name := UPPER(TRIM(p_measure_short_name));
759
760 IF (NOT is_Valid_AlphaNum(l_Dataset_Rec.Bsc_Measure_Short_Name)) THEN
761 FND_MESSAGE.SET_NAME('BSC','BSC_ALPHA_NUM_REQUIRED');
762 FND_MESSAGE.SET_TOKEN('VALUE', l_Dataset_Rec.Bsc_Measure_Short_Name);
763 FND_MESSAGE.SET_TOKEN('NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_BUILDER', 'MEASURE_SHORT_NAME'), TRUE);
764 FND_MSG_PUB.ADD;
765 RAISE FND_API.G_EXC_ERROR;
766 END IF;
767
768 SELECT COUNT(Short_Name) INTO l_count
769 FROM BIS_INDICATORS
770 WHERE UPPER(TRIM(Short_Name)) = l_Dataset_Rec.Bsc_Measure_Short_Name;
771 IF (l_count > 0) THEN
772 FND_MESSAGE.SET_NAME('BIS','BIS_MEASURE_SHORT_NAME_UNIQUE');
773 FND_MSG_PUB.ADD;
774 RAISE FND_API.G_EXC_ERROR;
775 END IF;
776 END IF;
777
778
779 /******************* PAJOHRI ADDED Bug #3184408*************************/
780 BSC_BIS_MEASURE_PUB.get_Measure_Name
781 ( p_dataset_id => NULL
782 , p_ui_flag => p_ui_flag
783 , p_dataset_source => p_dataset_source
784 , p_dataset_name => p_dataset_name
785 , x_measure_name => l_measure_rec.Measure_Name
786 );
787 l_Dataset_Rec.Bsc_Dataset_Name := l_measure_rec.Measure_Name;
788 /******************************************/
789
790 if p_measure_col is null then
791 -- mdamle 09/03/2003 - get measure col
792
793 l_Dataset_Rec.Bsc_Measure_Col := get_measure_col(l_Dataset_Rec.Bsc_Dataset_Name, p_dataset_source, NULL,l_Dataset_Rec.Bsc_Measure_Short_Name);
794 else
795 l_Dataset_Rec.Bsc_Measure_Col := p_measure_col;
796 end if;
797
798 -- Bug#3817894; SUBSTR is used since BSC_DB_MEASURE_COLS_TL.HELP is of VARCHAR2(150) type.
799 IF p_Measure_Col_Help IS NULL THEN
800 -- SUBSTR does not work with pseudo-translated chars. We would pass NULL then.
801 BEGIN
802 IF (p_Dataset_Help IS NOT NULL) THEN
803 l_Dataset_Rec.Bsc_Measure_Col_Help := SUBSTR(p_Dataset_Help, 1, 150);
804 ELSE
805 l_Dataset_Rec.Bsc_Measure_Col_Help := SUBSTR(l_Dataset_Rec.Bsc_Measure_Col, 1, 150);
806 END IF;
807 EXCEPTION
808 WHEN OTHERS THEN
809 l_Dataset_Rec.Bsc_Measure_Col_Help := SUBSTR(l_Dataset_Rec.Bsc_Measure_Col, 1, 150);
810 END;
811 ELSE
812 l_Dataset_Rec.Bsc_Measure_Col_Help := p_Measure_Col_Help;
813 END IF;
814
815 if p_measure_operation is null then
816 l_Dataset_Rec.Bsc_Measure_Operation := c_SUM;
817 else
818 l_Dataset_Rec.Bsc_Measure_Operation := p_measure_operation;
819 end if;
820
821 -- 1.) Need to place this line after l_Dataset_Rec.Bsc_Measure_Col is set
822 -- 2.) Need to place this line after l_Dataset_Rec.Bsc_Measure_Operation is set
823 l_Dataset_Rec.Bsc_Measure_color_formula := getColorFormula(l_Dataset_Rec, p_measure_apply_rollup);
824
825 if (l_Dataset_Rec.Bsc_Measure_operation = c_AVGL_CODE) then
826 l_Dataset_Rec.Bsc_Measure_operation := 'AVG';
827 end if;
828
829 -- Insert the Dataset and Measure Record
830 -- When DataSource (measure_id1) is passed as -1 from the UI then Bug #3292146
831 SELECT COUNT(Measure_Id)
832 INTO l_count
833 FROM BSC_SYS_MEASURES
834 WHERE Measure_Id = -1
835 AND Measure_Col = l_Dataset_Rec.Bsc_Measure_Col;
836
837 if (l_Dataset_Rec.Bsc_Measure_id = -1) and (l_count = 0) then
838 l_Dataset_Rec.Bsc_Measure_id := NULL;
839 end if;
840
841 if (l_Dataset_Rec.Bsc_Measure_id is null) then
842
843 -- Insert into BSC tables
844
845 BSC_DATASETS_PUB.Create_Measures(
846 p_commit => p_commit
847 ,p_Dataset_Rec => l_Dataset_Rec
848 ,x_Dataset_Id => x_Dataset_Id
849 ,x_return_status => x_return_status
850 ,x_msg_count => x_msg_count
851 ,x_msg_data => x_msg_data);
852 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
853 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at BSC_DATASETS_PUB.Create_Measures');
854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
855 END IF;
856
857 else
858 -- START Granular Locking
859 -- you need to lock both the Datasources (1 and 2), since
860 -- you would not want someone to delete it whenever
861 -- it is being assigned to the datasets/
862 -- Lock the first Data Source
863 if (l_Dataset_Rec.Bsc_Measure_Id is not null) then
864 BSC_BIS_LOCKS_PUB.LOCK_DATASOURCE(
865 p_measure_id => l_Dataset_Rec.Bsc_Measure_Id
866 ,p_time_stamp => NULL
867 ,x_return_status => x_return_status
868 ,x_msg_count => x_msg_count
869 ,x_msg_data => x_msg_data
870 ) ;
871
872 if ((x_return_status = FND_API.G_RET_STS_ERROR) or (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) then
873 raise FND_API.G_EXC_UNEXPECTED_ERROR;
874 end if;
875 end if;
876
877 -- Lock the second Data Source
878 if (l_Dataset_Rec.Bsc_Measure_Id2 is not null) then
879 BSC_BIS_LOCKS_PUB.LOCK_DATASOURCE(
880 p_measure_id => l_Dataset_Rec.Bsc_Measure_Id2
881 ,p_time_stamp => NULL
882 ,x_return_status => x_return_status
883 ,x_msg_count => x_msg_count
884 ,x_msg_data => x_msg_data
885 ) ;
886 if ((x_return_status = FND_API.G_RET_STS_ERROR) or (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) then
887 raise FND_API.G_EXC_UNEXPECTED_ERROR;
888 end if;
889 end if;
890
891 -- END Granular Locking
892
893
894 -- Now, create the dataset.
895 BSC_DATASETS_PUB.Create_Dataset(
896 p_commit => FND_API.G_FALSE
897 ,p_Dataset_Rec => l_Dataset_Rec
898 ,x_Dataset_Id => x_Dataset_Id
899 ,x_return_status => x_return_status
900 ,x_msg_count => x_msg_count
901 ,x_msg_data => x_msg_data);
902 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
903 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at BSC_DATASETS_PUB.Create_Dataset');
904 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
905 END IF;
906
907 -- POSCO Bug#3817894
908 -- Update the measure column if a different source column
909 -- has been chosen from the LOV and
910
911 IF (NOT isFormula(l_Dataset_Rec.Bsc_Measure_Col) AND l_Dataset_Rec.Bsc_Source = c_BSC) THEN
912 BSC_DB_MEASURE_COLS_PKG.Update_Measure_Column_Help (
913 p_Measure_Col => l_Dataset_Rec.Bsc_Measure_Col
914 , p_Help => l_Dataset_Rec.Bsc_Measure_Col_Help
915 , x_Return_Status => x_return_status
916 , x_Msg_Count => x_msg_count
917 , x_Msg_Data => x_msg_data
918 );
919 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
921 END IF;
922 END IF;
923
924 -- START Granular Locking
925
926 -- Change the time stamp of the Current Datasource (1)
927 IF (l_Dataset_Rec.Bsc_Measure_Id is not null) THEN
928 BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASOURCE(
929 p_measure_id => l_Dataset_Rec.Bsc_Measure_Id
930 ,p_lud => l_Dataset_Rec.Bsc_Dataset_Last_Update_Date
931 ,x_return_status => x_return_status
932 ,x_msg_count => x_msg_count
933 ,x_msg_data => x_msg_data
934 ) ;
935
936 IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
938 END IF;
939 END IF;
940
941 -- Change the time stamp of the Current Datasource (2)
942 IF (l_Dataset_Rec.Bsc_Measure_Id2 is not null) THEN
943 BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASOURCE(
944 p_measure_id => l_Dataset_Rec.Bsc_Measure_Id2
945 ,p_lud => l_Dataset_Rec.Bsc_Dataset_Last_Update_Date
946 ,x_return_status => x_return_status
947 ,x_msg_count => x_msg_count
948 ,x_msg_data => x_msg_data
949 ) ;
950
951 IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
953 END IF;
954 END IF;
955
956 -- END Granular Locking
957
958 end if;
959
960 l_Dataset_Rec.Bsc_dataset_id := x_dataset_id;
961
962 -- Insert into PMF tables
963 l_measure_rec.Dataset_id := x_dataset_id;
964
965 if l_Dataset_Rec.Bsc_Measure_Short_Name is null then
966 l_Dataset_Rec.Bsc_Measure_Short_Name := c_PMD || x_Dataset_id;
967 end if;
968
969 l_measure_rec.Measure_Short_Name := l_Dataset_Rec.Bsc_Measure_Short_Name;
970
971 -- mdamle 10/07/2003 - Bug#3170184 - For BSC type measure, always use short name in PMF display name
972 -- PAJOHRI Commented
973 /*if (l_Dataset_Rec.Bsc_Source = c_BSC) then
974 l_measure_rec.Measure_Name := l_measure_rec.Measure_Short_Name;
975 else
976 l_measure_rec.Measure_Name := l_Dataset_Rec.Bsc_Dataset_Name;
977 end if;*/
978 l_measure_rec.Description := p_dataset_help;
979 l_measure_rec.Unit_Of_Measure_Class := p_measure_uom_class;
980 l_measure_rec.actual_data_source_type := p_measure_act_data_src_type ;
981 l_measure_rec.actual_data_source := p_measure_act_data_src;
982 l_measure_rec.comparison_source := p_measure_comparison_source;
983 l_measure_rec.increase_in_measure := p_measure_increase_in_measure;
984 l_measure_rec.function_name := p_measure_function_name;
985 l_measure_rec.enable_link := p_measure_enable_link;
986 l_measure_rec.obsolete := p_measure_obsolete;
987 l_measure_rec.measure_type:= p_type;
988 l_measure_rec.is_validate := p_measure_is_validate;
989
990 --sawu: 9/1/04: populates region_app_id and attribute_code_app_id for ak_region_items also
991 l_measure_rec.Region_App_Id := p_region_app_id;
992 l_measure_rec.Source_Column_App_Id := p_source_column_app_id;
993 l_measure_rec.Compare_Column_App_Id := p_compare_column_app_id;
994
995 if (p_measure_app_id is null) then
996 l_measure_rec.Application_Id := 271;
997 else
998 l_measure_rec.Application_Id := p_measure_app_id;
999 end if;
1000
1001 -- mdamle 07/07/2003 - Added indicator dimensions
1002 l_Measure_rec.Dimension1_Id := p_Dimension1_id;
1003 l_Measure_rec.Dimension2_Id := p_Dimension2_id;
1004 l_Measure_rec.Dimension3_Id := p_Dimension3_id;
1005 l_Measure_rec.Dimension4_Id := p_Dimension4_id;
1006 l_Measure_rec.Dimension5_Id := p_Dimension5_id;
1007 l_Measure_rec.Dimension6_Id := p_Dimension6_id;
1008 l_Measure_rec.Dimension7_Id := p_Dimension7_id;
1009
1010 --sawu: populate WHO column
1011 l_Measure_rec.Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1012 l_Measure_rec.Creation_Date := l_last_update_date;
1013 l_Measure_rec.Last_Updated_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1014 l_Measure_rec.Last_Update_Login := fnd_global.LOGIN_ID;
1015 l_Measure_rec.Last_Update_Date := l_last_update_date;
1016
1017 -- rpenneru 12/20/2004 - Add Functional Area short name
1018 l_Measure_rec.Func_Area_Short_Name := p_func_area_short_name;
1019
1020 BIS_MEASURE_PUB.Create_Measure(
1021 p_api_version => 1.0
1022 ,p_commit => p_commit
1023 ,p_Measure_Rec => l_measure_rec
1024 ,p_owner => p_owner
1025 ,x_return_status => x_return_status
1026 ,x_error_tbl => l_error_tbl);
1027
1028 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1029 IF (l_error_tbl.COUNT > 0) THEN
1030 x_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
1031 IF(INSTR(x_msg_data, ' ') = 0 ) THEN
1032 FND_MESSAGE.SET_NAME('BIS',x_msg_data);
1033 FND_MSG_PUB.ADD;
1034 x_msg_data := NULL;
1035 END IF;
1036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1037 END IF;
1038 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1039 END IF;
1040
1041
1042 BSC_UTILITY.Add_To_Fnd_Msg_Stack(
1043 p_error_tbl => l_error_tbl
1044 ,x_return_status => x_return_status
1045 ,x_msg_count => x_msg_count
1046 ,x_msg_data => x_msg_data);
1047
1048 -- visuri removed l_Dataset_Rec.Bsc_Measure_id is null from if condition for bug 3284190
1049 -- Bug#3817894 - Pass the source column values
1050 -- Aditya Rao relaxed creation of Measure Columns
1051 -- if (not isFormula(l_Dataset_Rec.Bsc_Measure_Col) and l_Dataset_Rec.Bsc_Source = c_BSC) then
1052 IF NOT (isFormula(l_Dataset_Rec.Bsc_Measure_Col) OR l_Dataset_Rec.Bsc_Source = c_CDS) THEN
1053 bsc_db_measure_cols_pkg.insert_row(
1054 l_Dataset_Rec.Bsc_Measure_Col
1055 ,p_measure_group_id
1056 ,l_Dataset_Rec.Bsc_Measure_Projection_Id
1057 ,p_Measure_Type
1058 ,l_Dataset_Rec.Bsc_Measure_Col_Help);
1059 end if;
1060
1061
1062 EXCEPTION
1063 WHEN FND_API.G_EXC_ERROR THEN
1064 IF (x_msg_data IS NULL) THEN
1065 FND_MSG_PUB.Count_And_Get
1066 ( p_encoded => FND_API.G_FALSE
1067 , p_count => x_msg_count
1068 , p_data => x_msg_data
1069 );
1070 END IF;
1071 ROLLBACK TO SP_CREATE_MEASURE;
1072 x_return_status := FND_API.G_RET_STS_ERROR;
1073 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1074 IF (x_msg_data IS NULL) THEN
1075 FND_MSG_PUB.Count_And_Get
1076 ( p_encoded => FND_API.G_FALSE
1077 , p_count => x_msg_count
1078 , p_data => x_msg_data
1079 );
1080 END IF;
1081 ROLLBACK TO SP_CREATE_MEASURE;
1082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1083 WHEN NO_DATA_FOUND THEN
1084 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1085 IF (x_msg_data IS NOT NULL) THEN
1086 x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Create_Measure ';
1087 ELSE
1088 x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Create_Measure ';
1089 END IF;
1090 ROLLBACK TO SP_CREATE_MEASURE;
1091 WHEN OTHERS THEN
1092 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1093 IF (x_msg_data IS NOT NULL) THEN
1094 x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Create_Measure ';
1095 ELSE
1096 x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Create_Measure ';
1097 END IF;
1098 ROLLBACK TO SP_CREATE_MEASURE;
1099 end CREATE_MEASURE;
1100
1101
1102 procedure Update_Measure(
1103 p_commit IN VARCHAR2 := FND_API.G_FALSE
1104 ,p_dataset_id IN NUMBER
1105 ,p_dataset_source IN VARCHAR2
1106 ,p_dataset_name IN VARCHAR2
1107 ,p_dataset_help IN VARCHAR2 := NULL
1108 ,p_dataset_measure_id1 IN NUMBER := NULL
1109 ,p_dataset_operation IN VARCHAR2 := NULL
1110 ,p_dataset_measure_id2 IN NUMBER := NULL
1111 ,p_dataset_format_id IN NUMBER := NULL
1112 ,p_dataset_color_method IN NUMBER := NULL
1113 ,p_dataset_autoscale_flag IN NUMBER := NULL
1114 ,p_dataset_projection_flag IN NUMBER := NULL
1115 ,p_measure_short_name IN VARCHAR2
1116 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type := -1
1117 ,p_source_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
1118 ,p_compare_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
1119 ,p_measure_act_data_src_type IN VARCHAR2 := NULL
1120 ,p_measure_act_data_src IN VARCHAR2 := NULL
1121 ,p_measure_comparison_source IN VARCHAR2 := NULL
1122 ,p_measure_operation IN VARCHAR2 := c_SUM
1123 ,p_measure_uom_class IN VARCHAR2 := NULL
1124 ,p_measure_increase_in_measure IN VARCHAR2 := NULL
1125 ,p_measure_random_style IN NUMBER := NULL
1126 ,p_measure_min_act_value IN NUMBER := NULL
1127 ,p_measure_max_act_value IN NUMBER := NULL
1128 ,p_measure_min_bud_value IN NUMBER := NULL
1129 ,p_measure_max_bud_value IN NUMBER := NULL
1130 ,p_measure_app_id IN NUMBER := NULL
1131 ,p_measure_col IN VARCHAR2 := NULL
1132 ,p_measure_col_help IN VARCHAR2 := NULL
1133 ,p_measure_group_id IN NUMBER := NULL
1134 ,p_measure_projection_id IN NUMBER := NULL
1135 ,p_measure_type IN NUMBER := NULL
1136 ,p_measure_apply_rollup IN VARCHAR2 := NULL
1137 ,p_measure_function_name IN VARCHAR2 := NULL
1138 ,p_measure_enable_link IN VARCHAR2 := NULL
1139 ,p_measure_obsolete IN VARCHAR2 := FND_API.G_FALSE
1140 ,p_type IN VARCHAR2 := NULL -- This is used for weighted kpis,This can take values CDS_SCORE,CDS_PERF or Null
1141 ,p_measure_is_validate IN VARCHAR2 := FND_API.G_TRUE -- ankgoel: bug#3557236
1142 ,p_time_stamp IN VARCHAR2 := NULL -- Added for Granular Locking
1143 ,p_dimension1_id IN NUMBER
1144 ,p_dimension2_id IN NUMBER
1145 ,p_dimension3_id IN NUMBER
1146 ,p_dimension4_id IN NUMBER
1147 ,p_dimension5_id IN NUMBER
1148 ,p_dimension6_id IN NUMBER
1149 ,p_dimension7_id IN NUMBER
1150 ,p_y_axis_title IN VARCHAR2 := NULL
1151 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
1152 ,p_ui_flag IN VARCHAR2
1153 ,p_func_area_short_name IN VARCHAR2 := NULL
1154 ,x_return_status OUT NOCOPY VARCHAR2
1155 ,x_msg_count OUT NOCOPY NUMBER
1156 ,x_msg_data OUT NOCOPY VARCHAR2
1157 ) is
1158 begin
1159 SAVEPOINT SP_UPDATE_MEASURE;
1160 x_return_status := FND_API.G_RET_STS_SUCCESS;
1161 fnd_msg_pub.initialize;
1162
1163 Update_Measure(
1164 p_commit => p_commit
1165 ,p_dataset_id => p_dataset_id
1166 ,p_dataset_source => p_dataset_source
1167 ,p_dataset_name => p_dataset_name
1168 ,p_dataset_help => p_dataset_help
1169 ,p_dataset_measure_id1 => p_dataset_measure_id1
1170 ,p_dataset_operation => p_dataset_operation
1171 ,p_dataset_measure_id2 => p_dataset_measure_id2
1172 ,p_dataset_format_id => p_dataset_format_id
1173 ,p_dataset_color_method => p_dataset_color_method
1174 ,p_dataset_autoscale_flag => p_dataset_autoscale_flag
1175 ,p_dataset_projection_flag => p_dataset_projection_flag
1176 ,p_measure_short_name => p_measure_short_name
1177 ,p_region_app_id => p_region_app_id
1178 ,p_source_column_app_id => p_source_column_app_id
1179 ,p_compare_column_app_id => p_compare_column_app_id
1180 ,p_measure_act_data_src_type => p_measure_act_data_src_type
1181 ,p_measure_act_data_src => p_measure_act_data_src
1182 ,p_measure_comparison_source => p_measure_comparison_source
1183 ,p_measure_operation => p_measure_operation
1184 ,p_measure_uom_class => p_measure_uom_class
1185 ,p_measure_increase_in_measure => p_measure_increase_in_measure
1186 ,p_measure_random_style => p_measure_random_style
1187 ,p_measure_min_act_value => p_measure_min_act_value
1188 ,p_measure_max_act_value => p_measure_max_act_value
1189 ,p_measure_min_bud_value => p_measure_min_bud_value
1190 ,p_measure_max_bud_value => p_measure_max_bud_value
1191 ,p_measure_app_id => p_measure_app_id
1192 ,p_measure_col => p_measure_col
1193 ,p_measure_col_help => p_measure_col_help
1194 ,p_measure_group_id => p_measure_group_id
1195 ,p_measure_projection_id => p_measure_projection_id
1196 ,p_measure_type => p_measure_type
1197 ,p_measure_apply_rollup => p_measure_apply_rollup
1198 ,p_measure_function_name => p_measure_function_name
1199 ,p_measure_enable_link => p_measure_enable_link
1200 ,p_measure_obsolete => p_measure_obsolete
1201 ,p_type => p_type
1202 ,p_measure_is_validate => p_measure_is_validate
1203 ,p_time_stamp => p_time_stamp
1204 ,p_dimension1_id => p_dimension1_id
1205 ,p_dimension2_id => p_dimension2_id
1206 ,p_dimension3_id => p_dimension3_id
1207 ,p_dimension4_id => p_dimension4_id
1208 ,p_dimension5_id => p_dimension5_id
1209 ,p_dimension6_id => p_dimension6_id
1210 ,p_dimension7_id => p_dimension7_id
1211 ,p_y_axis_title => p_y_axis_title
1212 ,p_owner => p_owner
1213 ,p_ui_flag => p_ui_flag
1214 ,p_last_update_date => sysdate
1215 ,p_func_area_short_name => p_func_area_short_name
1216 ,x_return_status => x_return_status
1217 ,x_msg_count => x_msg_count
1218 ,x_msg_data => x_msg_data
1219 );
1220 EXCEPTION
1221 WHEN FND_API.G_EXC_ERROR THEN
1222 IF (x_msg_data IS NULL) THEN
1223 FND_MSG_PUB.Count_And_Get
1224 ( p_encoded => FND_API.G_FALSE
1225 , p_count => x_msg_count
1226 , p_data => x_msg_data
1227 );
1228 END IF;
1229 ROLLBACK TO SP_UPDATE_MEASURE;
1230 x_return_status := FND_API.G_RET_STS_ERROR;
1231 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1232 IF (x_msg_data IS NULL) THEN
1233 FND_MSG_PUB.Count_And_Get
1234 ( p_encoded => FND_API.G_FALSE
1235 , p_count => x_msg_count
1236 , p_data => x_msg_data
1237 );
1238 END IF;
1239 ROLLBACK TO SP_UPDATE_MEASURE;
1240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1241 WHEN NO_DATA_FOUND THEN
1242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1243 IF (x_msg_data IS NOT NULL) THEN
1244 x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Update_measure ';
1245 ELSE
1246 x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Update_measure ';
1247 END IF;
1248 ROLLBACK TO SP_UPDATE_MEASURE;
1249 WHEN OTHERS THEN
1250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1251 IF (x_msg_data IS NOT NULL) THEN
1252 x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Update_measure ';
1253 ELSE
1254 x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Update_measure ';
1255 END IF;
1256 ROLLBACK TO SP_UPDATE_MEASURE;
1257 end Update_measure;
1258
1259 --Bug#4045278: Wrapper for Update_Measure that takes in last_update_date
1260
1261 FUNCTION Is_MeasureCol_In_Formula (
1262 p_measureCol IN VARCHAR2,
1263 p_formula IN VARCHAR2
1264 ) return boolean
1265 IS
1266 l_start number;
1267 l_length number;
1268
1269 BEGIN
1270 l_start := INSTR(p_formula, p_measureCol);
1271 l_length := LENGTH(p_measureCol);
1272
1273 IF (l_start>0) THEN
1274
1275 IF ((l_start = 1 ) AND INSTR('+-/*()',SUBSTR(p_formula,(l_length+1),1))>0) THEN
1276 /*The Formula p_formula starts with measure col p_measureCol. It is of the form X+Y where X=p_measureCol
1277 One character after the source Column X in the formula should be an operator of type +-/*()
1278 */
1279 RETURN TRUE;
1280 ELSIF ((INSTR('+-/*()',SUBSTR(p_formula,l_start-1,1))>0) AND ((INSTR('+-/*()',SUBSTR(p_formula,l_start+l_length,1))>0)OR(LENGTH(p_formula)=l_start+l_length-1))) THEN
1281 /*The Formula p_formula either ends with measure col p_measureCol or has p_measureCol in it.
1282 It is of the form Y+X where X=p_measureCol or A+X+B where X=p_measureCol.
1283
1284 If it is of type Y+X then 1 character before the Measure Column X should be an operator of type +-/*()
1285 If it is of type A+X+Y then the 1 character before the Measure Column X and one character after the
1286 measure column X should be an operator of type +-/*()
1287 */
1288 RETURN TRUE;
1289 END IF;
1290 END IF;
1291
1292 RETURN FALSE;
1293
1294 END Is_MeasureCol_In_Formula;
1295
1296
1297 FUNCTION Is_Src_Col_In_Formulas(
1298 p_Source_Col IN VARCHAR2
1299 ) RETURN BOOLEAN IS
1300
1301 CURSOR c_All_Formula IS
1302 SELECT MEASURE_COL
1303 FROM BSC_SYS_MEASURES;
1304
1305 BEGIN
1306
1307 FOR cd in c_All_Formula LOOP
1308 IF (isFormula(cd.MEASURE_COL) AND Is_MeasureCol_In_Formula(p_Source_Col,cd.MEASURE_COL) ) THEN
1309 RETURN TRUE;
1310 END IF;
1311 END LOOP;
1312
1313 RETURN FALSE;
1314 END Is_Src_Col_In_Formulas;
1315
1316 PROCEDURE Update_Single_To_Formula(
1317 p_commit IN VARCHAR2 := FND_API.G_FALSE
1318 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1319 ,p_Dataset_Rec_db IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1320 ,x_return_status OUT NOCOPY VARCHAR2
1321 ,x_msg_count OUT NOCOPY NUMBER
1322 ,x_msg_data OUT NOCOPY VARCHAR2
1323
1324 ) IS
1325 l_kpi_flag number := -1;
1326 l_indicator_table BSC_NUM_LIST;
1327 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
1328
1329 CURSOR indicators_cursor is
1330 SELECT distinct indicator
1331 FROM bsc_kpi_analysis_measures_b
1332 WHERE dataset_id = l_Dataset_Rec.Bsc_Dataset_Id;
1333
1334 BEGIN
1335 l_Dataset_Rec := p_Dataset_Rec;
1336 SAVEPOINT SP_UPD_TO_FORMULA;
1337
1338 l_Dataset_Rec.Bsc_Measure_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value( 'BSC_SYS_MEASURES'
1339 ,'measure_id');
1340 if l_Dataset_Rec.Bsc_Measure_Col IS NULL then
1341 l_Dataset_Rec.Bsc_Measure_Col := p_Dataset_Rec_db.Bsc_Measure_Col;
1342 end if;
1343 if l_Dataset_Rec.Bsc_Measure_Operation IS NULL then
1344 l_Dataset_Rec.Bsc_Measure_Operation := p_Dataset_Rec_db.Bsc_Measure_Operation;
1345 end if;
1346
1347 if l_Dataset_Rec.Bsc_Meas_Type IS NULL then
1348 l_Dataset_Rec.Bsc_Meas_Type := p_Dataset_Rec_db.Bsc_Meas_Type;
1349 end if;
1350
1351 if l_Dataset_Rec.Bsc_Measure_Min_Act_Value IS NULL then
1352 l_Dataset_Rec.Bsc_Measure_Min_Act_Value := p_Dataset_Rec_db.Bsc_Measure_Min_Act_Value;
1353 end if;
1354
1355 if l_Dataset_Rec.Bsc_Measure_Max_Act_Value IS NULL then
1356 l_Dataset_Rec.Bsc_Measure_Max_Act_Value := p_Dataset_Rec_db.Bsc_Measure_Max_Act_Value;
1357 end if;
1358
1359 if l_Dataset_Rec.Bsc_Measure_Min_Bud_Value IS NULL then
1360 l_Dataset_Rec.Bsc_Measure_Min_Bud_Value := p_Dataset_Rec_db.Bsc_Measure_Min_Bud_Value;
1361 end if;
1362
1363 if l_Dataset_Rec.Bsc_Measure_Max_Bud_Value IS NULL then
1364 l_Dataset_Rec.Bsc_Measure_Max_Bud_Value := p_Dataset_Rec_db.Bsc_Measure_Max_Bud_Value;
1365 end if;
1366
1367 if l_Dataset_Rec.Bsc_Measure_Random_Style IS NULL then
1368 l_Dataset_Rec.Bsc_Measure_Random_Style := p_Dataset_Rec_db.Bsc_Measure_Random_Style;
1369 end if;
1370
1371 if l_Dataset_Rec.Bsc_Measure_Short_Name IS NULL then
1372 l_Dataset_Rec.Bsc_Measure_Short_Name := p_Dataset_Rec_db.Bsc_Measure_Short_Name;
1373 end if;
1374
1375 if l_Dataset_Rec.Bsc_Source IS NULL then
1376 l_Dataset_Rec.Bsc_Source := p_Dataset_Rec_db.Bsc_Source;
1377 end if;
1378
1379 if l_Dataset_Rec.Bsc_Measure_color_formula IS NULL then
1380 l_Dataset_Rec.Bsc_Measure_color_formula := p_Dataset_Rec_db.Bsc_Measure_color_formula;
1381 end if;
1382
1383 if l_Dataset_Rec.Bsc_Measure_Created_By IS NULL then
1384 l_Dataset_Rec.Bsc_Measure_Created_By := p_Dataset_Rec_db.Bsc_Measure_Created_By;
1385 end if;
1386
1387 if l_Dataset_Rec.Bsc_Measure_Last_Update_By is null then
1388 l_Dataset_Rec.Bsc_Measure_Last_Update_By := fnd_global.USER_ID;
1389 end if;
1390
1391 if l_Dataset_Rec.Bsc_Measure_Last_Update_Login is null then
1392 l_Dataset_Rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
1393 end if;
1394
1395
1396 BSC_DATASETS_PVT.Create_Measures(
1397 p_commit
1398 ,l_Dataset_Rec
1399 ,x_return_status
1400 ,x_msg_count
1401 ,x_msg_data);
1402
1403
1404 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1405 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at BSC_DATASETS_PUB.Create_Measures');
1406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1407 END IF;
1408
1409 --Need to update Dataset Info with the new Measure Id generated
1410
1411 BSC_DATASETS_PUB.Update_Dataset(
1412 p_commit => p_commit
1413 ,p_Dataset_Rec => l_Dataset_Rec
1414 ,p_update_dset_calc => false
1415 ,x_return_status => x_return_status
1416 ,x_msg_count => x_msg_count
1417 ,x_msg_data => x_msg_data);
1418
1419 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1420 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at BSC_DATASETS_PUB.Update_Measures');
1421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1422 END IF;
1423
1424 /*Checking for Structural changes in indicators*/
1425 l_kpi_flag := BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure;
1426 open indicators_cursor;
1427 fetch indicators_cursor bulk collect into l_indicator_table;
1428 if indicators_cursor%ISOPEN THEN
1429 CLOSE indicators_cursor;
1430 end if;
1431 for i in 1..l_indicator_table.count loop
1432 BSC_DESIGNER_PVT.ActionFlag_Change(l_indicator_table(i), l_kpi_flag);
1433 end loop;
1434
1435 EXCEPTION
1436
1437 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1438
1439 IF(indicators_cursor%ISOPEN) THEN
1440 CLOSE indicators_cursor;
1441 END IF;
1442
1443 IF (x_msg_data IS NULL) THEN
1444 FND_MSG_PUB.Count_And_Get
1445 ( p_encoded => FND_API.G_FALSE
1446 , p_count => x_msg_count
1447 , p_data => x_msg_data
1448 );
1449 END IF;
1450 ROLLBACK TO SP_UPD_TO_FORMULA;
1451 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1452
1453 WHEN OTHERS THEN
1454
1455 IF(indicators_cursor%ISOPEN) THEN
1456 CLOSE indicators_cursor;
1457 END IF;
1458
1459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1460
1461 IF (x_msg_data IS NOT NULL) THEN
1462 x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Upd_Sing_To_Formula ';
1463 ELSE
1464 x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Upd_Sing_To_Formula ';
1465 END IF;
1466
1467 ROLLBACK TO SP_UPD_TO_FORMULA;
1468 END Update_Single_To_Formula;
1469
1470
1471 procedure Update_Measure(
1472 p_commit IN VARCHAR2 := FND_API.G_FALSE
1473 ,p_dataset_id IN NUMBER
1474 ,p_dataset_source IN VARCHAR2
1475 ,p_dataset_name IN VARCHAR2
1476 ,p_dataset_help IN VARCHAR2 := NULL
1477 ,p_dataset_measure_id1 IN NUMBER := NULL
1478 ,p_dataset_operation IN VARCHAR2 := NULL
1479 ,p_dataset_measure_id2 IN NUMBER := NULL
1480 ,p_dataset_format_id IN NUMBER := NULL
1481 ,p_dataset_color_method IN NUMBER := NULL
1482 ,p_dataset_autoscale_flag IN NUMBER := NULL
1483 ,p_dataset_projection_flag IN NUMBER := NULL
1484 ,p_measure_short_name IN VARCHAR2
1485 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type := -1
1486 ,p_source_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
1487 ,p_compare_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
1488 ,p_measure_act_data_src_type IN VARCHAR2 := NULL
1489 ,p_measure_act_data_src IN VARCHAR2 := NULL
1490 ,p_measure_comparison_source IN VARCHAR2 := NULL
1491 ,p_measure_operation IN VARCHAR2 := c_SUM
1492 ,p_measure_uom_class IN VARCHAR2 := NULL
1493 ,p_measure_increase_in_measure IN VARCHAR2 := NULL
1494 ,p_measure_random_style IN NUMBER := NULL
1495 ,p_measure_min_act_value IN NUMBER := NULL
1496 ,p_measure_max_act_value IN NUMBER := NULL
1497 ,p_measure_min_bud_value IN NUMBER := NULL
1498 ,p_measure_max_bud_value IN NUMBER := NULL
1499 ,p_measure_app_id IN NUMBER := NULL
1500 ,p_measure_col IN VARCHAR2 := NULL
1501 ,p_measure_col_help IN VARCHAR2 := NULL
1502 ,p_measure_group_id IN NUMBER := NULL
1503 ,p_measure_projection_id IN NUMBER := NULL
1504 ,p_measure_type IN NUMBER := NULL
1505 ,p_measure_apply_rollup IN VARCHAR2 := NULL
1506 ,p_measure_function_name IN VARCHAR2 := NULL
1507 ,p_measure_enable_link IN VARCHAR2 := NULL
1508 ,p_measure_obsolete IN VARCHAR2 := FND_API.G_FALSE
1509 ,p_type IN VARCHAR2 := NULL -- This is used for weighted kpis,This can take values CDS_SCORE,CDS_PERF or Null
1510 ,p_measure_is_validate IN VARCHAR2 := FND_API.G_TRUE -- ankgoel: bug#3557236
1511 ,p_time_stamp IN VARCHAR2 := NULL -- Added for Granular Locking
1512 ,p_dimension1_id IN NUMBER
1513 ,p_dimension2_id IN NUMBER
1514 ,p_dimension3_id IN NUMBER
1515 ,p_dimension4_id IN NUMBER
1516 ,p_dimension5_id IN NUMBER
1517 ,p_dimension6_id IN NUMBER
1518 ,p_dimension7_id IN NUMBER
1519 ,p_y_axis_title IN VARCHAR2 := NULL
1520 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
1521 ,p_ui_flag IN VARCHAR2
1522 ,p_last_update_date IN BIS_INDICATORS.LAST_UPDATE_DATE%TYPE
1523 ,p_func_area_short_name IN VARCHAR2 := NULL
1524 ,x_return_status OUT NOCOPY VARCHAR2
1525 ,x_msg_count OUT NOCOPY NUMBER
1526 ,x_msg_data OUT NOCOPY VARCHAR2
1527 ) is
1528
1529 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
1530 l_Dataset_Rec_db BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
1531 l_measure_rec BIS_MEASURE_PUB.Measure_rec_type;
1532 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1533 l_measure_col_help VARCHAR2(150);
1534 l_old_measure_id NUMBER;
1535 l_count NUMBER;
1536
1537 l_measure_group_id BSC_DB_MEASURE_COLS_TL.Measure_Group_Id%TYPE;
1538 l_projection_id BSC_DB_MEASURE_COLS_TL.Projection_Id%TYPE;
1539 l_measure_type BSC_DB_MEASURE_COLS_TL.Measure_Type%TYPE;
1540
1541 l_Del_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
1542 l_Delete_Source BOOLEAN;
1543 l_Dataset_Name BSC_SYS_DATASETS_VL.Name%TYPE;
1544 l_Same_Name BOOLEAN := FALSE;
1545
1546 l_last_update_date BIS_INDICATORS.LAST_UPDATE_DATE%TYPE;
1547
1548 -- Added for Bug#4617140
1549 l_Old_Measure_Col BSC_SYS_MEASURES.MEASURE_COL%TYPE;
1550 l_Old_Measure_Id1 BSC_SYS_DATASETS_B.MEASURE_ID1%TYPE;
1551 l_Old_Measure_Id2 BSC_SYS_DATASETS_B.MEASURE_ID2%TYPE;
1552 l_Is_Converted_To_Formula_Type BOOLEAN;
1553 l_Report_Objectives VARCHAR2(2000);
1554
1555 CURSOR c_measure_col_cur( c_measure_col_name VARCHAR2 ) IS
1556 SELECT measure_group_id
1557 , projection_id
1558 , measure_type
1559 , help
1560 FROM bsc_db_measure_cols_vl
1561 WHERE measure_col = c_measure_col_name;
1562
1563 CURSOR c_Bsc_Measure_Color_Formula IS
1564 SELECT s_Color_Formula
1565 , Measure_Col
1566 FROM BSC_SYS_MEASURES
1567 WHERE Measure_Id = l_Dataset_Rec.Bsc_Measure_Id;
1568
1569 CURSOR c_Dataset_Measures IS
1570 SELECT MEASURE_ID1
1571 , MEASURE_ID2
1572 FROM BSC_SYS_DATASETS_B
1573 WHERE DATASET_ID = p_dataset_id;
1574
1575 CURSOR c_Bsc_Dataset_Name IS
1576 SELECT NAME
1577 FROM BSC_SYS_DATASETS_VL
1578 WHERE DATASET_ID = p_dataset_id;
1579
1580 CURSOR c_Bis_Measure_Name IS
1581 SELECT NAME
1582 FROM BIS_INDICATORS_VL
1583 WHERE DATASET_ID = p_dataset_id;
1584
1585 CURSOR c_Intial_Formula_Content IS
1586 SELECT D.MEASURE_ID1, D.MEASURE_ID2, M.MEASURE_COL
1587 FROM BSC_SYS_DATASETS_B D, BSC_SYS_MEASURES M
1588 WHERE D.DATASET_ID = p_Dataset_ID
1589 AND M.MEASURE_ID = D.MEASURE_ID1;
1590
1591 begin
1592 SAVEPOINT SP_UPDATE_MEASURE;
1593 x_return_status := FND_API.G_RET_STS_SUCCESS;
1594 fnd_msg_pub.initialize;
1595
1596 l_Dataset_Rec.Bsc_dataset_id := p_dataset_id;
1597 l_Dataset_Rec.Bsc_Source := p_dataset_source;
1598 l_Dataset_Rec.Bsc_Dataset_Help := p_dataset_help;
1599 l_Dataset_Rec.Bsc_Measure_Id := p_dataset_measure_id1;
1600 l_Dataset_Rec.Bsc_Measure_Id2 := p_dataset_measure_id2;
1601 l_Dataset_Rec.Bsc_Dataset_Format_Id := p_dataset_format_id;
1602 l_Dataset_Rec.Bsc_Dataset_Color_Method := p_dataset_color_method;
1603 l_Dataset_Rec.Bsc_Dataset_Autoscale_Flag := p_dataset_autoscale_flag;
1604 l_Dataset_Rec.Bsc_Dataset_Projection_Flag := p_dataset_projection_flag;
1605 l_Dataset_Rec.Bsc_Dataset_Operation := p_dataset_operation;
1606 l_Dataset_Rec.Bsc_y_axis_Title := p_y_axis_title;
1607
1608 --sawu: populate WHO column
1609 l_last_update_date := nvl(p_last_update_date, sysdate);
1610
1611 l_Dataset_Rec.Bsc_Dataset_Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1612 l_Dataset_Rec.Bsc_Dataset_Last_Update_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1613 l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := fnd_global.LOGIN_ID;
1614 l_Dataset_Rec.Bsc_Dataset_Last_Update_Date := l_last_update_date;
1615
1616 l_Dataset_Rec.Bsc_Measure_Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1617 l_Dataset_Rec.Bsc_Measure_Last_Update_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1618 l_Dataset_Rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
1619 l_Dataset_Rec.Bsc_Measure_Last_Update_Date := l_last_update_date;
1620
1621 if p_measure_short_name is null then
1622 l_Dataset_Rec.Bsc_Measure_Short_Name := c_PMD || p_Dataset_id;
1623 else
1624 if(p_dataset_source = c_BSC) then
1625 l_Dataset_Rec.Bsc_Measure_Short_Name := trim(p_measure_short_name);
1626 else
1627 l_Dataset_Rec.Bsc_Measure_Short_Name := p_measure_short_name;
1628 end if;
1629 end if;
1630
1631 /****************************************************************
1632 We need to Check if the display name was changed by the user.
1633 If yes then only call the uniqueness check of measure display names.
1634 *****************************************************************/
1635 IF(l_Dataset_Rec.Bsc_Source = c_PMF) THEN
1636 FOR cd_Bis_Name IN c_Bis_Measure_Name LOOP
1637 l_Dataset_Name := cd_Bis_Name.Name;
1638 IF(l_Dataset_Name= p_dataset_name) THEN
1639 l_Same_Name := TRUE;
1640 END IF;
1641 END LOOP;
1642 ELSE
1643 FOR cd_Bsc_Name IN c_Bsc_Dataset_Name LOOP
1644 l_Dataset_Name := cd_Bsc_Name.Name;
1645 IF(l_Dataset_Name= p_dataset_name) THEN
1646 l_Same_Name := TRUE;
1647 END IF;
1648 END LOOP;
1649 END IF;
1650
1651 IF(NOT l_Same_Name) THEN
1652 BSC_BIS_MEASURE_PUB.get_Measure_Name
1653 ( p_dataset_id => NVL(l_Dataset_Rec.Bsc_dataset_id, -1)
1654 , p_ui_flag => p_ui_flag
1655 , p_dataset_source => p_dataset_source
1656 , p_dataset_name => p_dataset_name
1657 , x_measure_name => l_measure_rec.Measure_Name
1658 );
1659 ELSE
1660 l_measure_rec.Measure_Name := p_dataset_name;
1661 END IF;
1662
1663 l_Dataset_Rec.Bsc_Dataset_Name := l_measure_rec.Measure_Name;
1664 /******************************************/
1665 l_Dataset_Rec.Bsc_Measure_Long_Name := l_Dataset_Rec.Bsc_Dataset_Name;
1666
1667 l_Dataset_Rec.Bsc_Measure_Projection_Id := p_measure_projection_id;
1668
1669 -- added for Bug#3238554, to ensure that the value is passed to lower APIs.
1670 l_Dataset_Rec.Bsc_Measure_Type := p_measure_type;
1671
1672 -- added for Bug#3528425 - ensure Bsc_Measure_Group_Id is passed to lower APIs
1673 l_Dataset_Rec.Bsc_Measure_Group_Id := p_measure_group_id;
1674
1675 l_Dataset_Rec.Bsc_Measure_Random_Style := p_measure_random_style;
1676 l_Dataset_Rec.Bsc_Measure_Max_Act_Value := p_measure_max_act_value;
1677 l_Dataset_Rec.Bsc_Measure_Max_Bud_Value := p_measure_max_bud_value;
1678 l_Dataset_Rec.Bsc_Measure_Min_Act_Value := p_measure_min_act_value;
1679 l_Dataset_Rec.Bsc_Measure_Min_Bud_Value := p_measure_min_bud_value;
1680
1681 if p_measure_operation is null then
1682 l_Dataset_Rec.Bsc_Measure_Operation := c_SUM;
1683 else
1684 l_Dataset_Rec.Bsc_Measure_Operation := p_measure_operation;
1685 end if;
1686
1687 -- 1.) Need to place this line after l_Dataset_Rec.Bsc_Measure_Col is set
1688 -- 2.) Need to place this line after l_Dataset_Rec.Bsc_Measure_Operation is set
1689 IF ( c_Bsc_Measure_Color_Formula%ISOPEN) THEN
1690 CLOSE c_Bsc_Measure_Color_Formula;
1691 END IF;
1692 OPEN c_Bsc_Measure_Color_Formula;
1693 FETCH c_Bsc_Measure_Color_Formula
1694 INTO l_Dataset_Rec.Bsc_Measure_color_formula
1695 ,l_Dataset_Rec.Bsc_Measure_Col;
1696 CLOSE c_Bsc_Measure_Color_Formula;
1697 l_Dataset_Rec.Bsc_Measure_color_formula := getColorFormula(l_Dataset_Rec, p_measure_apply_rollup);
1698
1699 IF (p_measure_col IS NOT NULL) THEN
1700 l_Dataset_Rec.Bsc_Measure_Col := p_measure_col;
1701 END IF;
1702
1703 if (l_Dataset_Rec.Bsc_Measure_operation = c_AVGL_CODE) then
1704 l_Dataset_Rec.Bsc_Measure_operation := 'AVG';
1705 end if;
1706
1707
1708 -- ADRAO: Added for Bug#4617140
1709 FOR cIFC IN c_Intial_Formula_Content LOOP
1710 l_Old_Measure_Col := cIFC.MEASURE_COL;
1711 l_Old_Measure_Id1 := cIFC.MEASURE_ID1;
1712 l_Old_Measure_Id2 := cIFC.MEASURE_ID2;
1713 END LOOP;
1714
1715 l_Is_Converted_To_Formula_Type := FALSE;
1716
1717 IF((NOT isFormula(l_Old_Measure_Col)) AND (l_Old_Measure_Id2 IS NULL) AND (p_Dataset_Source = 'BSC')) THEN
1718 IF(isFormula(p_Measure_Col) OR (p_Dataset_Measure_Id2 IS NOT NULL)) THEN
1719 l_Is_Converted_To_Formula_Type := TRUE;
1720 END IF;
1721 END IF;
1722
1723 IF (l_Is_Converted_To_Formula_Type) THEN
1724 l_Report_Objectives := Get_Report_Objectives(p_Dataset_Id);
1725 IF (l_Report_Objectives IS NOT NULL) THEN
1726 FND_MESSAGE.SET_NAME('BIS','BIS_KPI_NON_FORMULA_FOR_AGRPT');
1727 FND_MESSAGE.SET_TOKEN('OBJECTIVES', l_Report_Objectives);
1728 FND_MSG_PUB.ADD;
1729 RAISE FND_API.G_EXC_ERROR;
1730 END IF;
1731 END IF ;
1732
1733 -- START Granular Locking
1734 --DBMS_OUTPUT.PUT_LINE('calling BSC_BIS_LOCKS_PUB.LOCK_UPDATE_MEASURE');
1735 BSC_BIS_LOCKS_PUB.LOCK_UPDATE_MEASURE(
1736 p_dataset_id => l_Dataset_Rec.Bsc_dataset_id
1737 ,p_time_stamp => p_time_stamp
1738 ,x_return_status => x_return_status
1739 ,x_msg_count => x_msg_count
1740 ,x_msg_data => x_msg_data
1741 ) ;
1742
1743 -- The APIs should check for return status ...
1744 -- Raising an unexpected error.
1745 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1746 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at BSC_BIS_LOCKS_PUB.LOCK_UPDATE_MEASURE');
1747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1748 END IF;
1749 -- END Granular Locking
1750
1751 -- Fix for Bug#3781176
1752 /*
1753 The following logic ensures that no dangling entries in bsc_sys_measures
1754 remains within the system. When one Measure (bsc_sys_datasets_b) has its datasource
1755 changed, then we need to delete it during update and ensure that it is not orphened
1756 i.e its achieved using the Delete_Measures from the Private datasets package (BSC_DATASETS_PVT)
1757 */
1758
1759 l_Delete_Source := FALSE;
1760
1761 FOR cDM IN c_Dataset_Measures LOOP
1762 l_Del_Dataset_Rec.Bsc_Measure_Id := cDM.MEASURE_ID1;
1763 l_Del_Dataset_Rec.Bsc_Measure_Id2 := cDM.MEASURE_ID2;
1764 END LOOP;
1765
1766 -- we need to execute this only if whats coming from UI is different as in the DB.
1767 IF (l_Del_Dataset_Rec.Bsc_Measure_Id IS NOT NULL) THEN
1768 IF((l_Del_Dataset_Rec.Bsc_Measure_Id = NVL(p_Dataset_Measure_Id1, l_Del_Dataset_Rec.Bsc_Measure_Id))) THEN
1769 l_Del_Dataset_Rec.Bsc_Measure_Id := NULL;
1770 END IF;
1771
1772 IF (l_Del_Dataset_Rec.Bsc_Measure_Id2 IS NOT NULL) THEN
1773 IF((l_Del_Dataset_Rec.Bsc_Measure_Id2 = NVL(p_Dataset_Measure_Id2, l_Del_Dataset_Rec.Bsc_Measure_Id2))) THEN
1774 l_Del_Dataset_Rec.Bsc_Measure_Id2 := NULL;
1775 END IF;
1776 END IF;
1777
1778 IF (l_Del_Dataset_Rec.Bsc_Measure_Id IS NOT NULL) THEN
1779 l_Delete_Source := TRUE;
1780 ELSE
1781 IF (l_Del_Dataset_Rec.Bsc_Measure_Id2 IS NOT NULL) THEN
1782 -- We cannot pass NULL for measure_id1 in the lower APIS
1783 -- and p_Dataset_Measure_Id1 can never be null (otherwise lower API will raise an exception)
1784 l_Del_Dataset_Rec.Bsc_Measure_Id := p_Dataset_Measure_Id1;
1785 l_Delete_Source := TRUE;
1786 END IF;
1787 END IF;
1788 END IF;
1789
1790 /*
1791 Should check if present change is from Single Source Column to Formula based measure then
1792 a new Measure Record should be inserted in bsc_sys_measures
1793 */
1794
1795
1796 BSC_DATASETS_PUB.Retrieve_Measures(
1797 p_commit => p_commit
1798 ,p_Dataset_Rec => l_Dataset_Rec
1799 ,x_Dataset_Rec => l_Dataset_Rec_db
1800 ,x_return_status => x_return_status
1801 ,x_msg_count => x_msg_count
1802 ,x_msg_data => x_msg_data
1803 );
1804
1805 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1807 END IF;
1808 -- added an additional condition 'Is_Src_Col_In_Formulas' for Bug#5071121
1809 IF ((NOT isFormula(l_Dataset_Rec_db.Bsc_Measure_Col)) AND
1810 (isFormula(l_Dataset_Rec.Bsc_Measure_Col)) AND
1811 Is_Src_Col_In_Formulas(l_Dataset_Rec_db.Bsc_Measure_Col)) THEN
1812 /*Create a new entry in BSC_SYS_MEASURES if a Single Source Column is being changed to a Formula
1813 of A+B type while the source column is being used in other Formula. Then associate this new Measure Id
1814 as the Measure Id1 of the dataset*/
1815 BSC_BIS_MEASURE_PUB.Update_Single_To_Formula(
1816 p_commit => p_commit
1817 ,p_Dataset_Rec => l_Dataset_Rec
1818 ,p_Dataset_Rec_db => l_Dataset_Rec_db
1819 ,x_return_status => x_return_status
1820 ,x_msg_count => x_msg_count
1821 ,x_msg_data => x_msg_data
1822 );
1823 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1825 END IF;
1826
1827
1828 ELSE
1829
1830 -- Update the Dataset and Measure Record
1831 --DBMS_OUTPUT.PUT_LINE('calling BSC_DATASETS_PUB.Update_Measures <'||x_msg_data||'>');
1832 BSC_DATASETS_PUB.Update_Measures(
1833 p_commit => p_commit
1834 ,p_Dataset_Rec => l_Dataset_Rec
1835 ,p_update_dset_calc => false
1836 ,x_return_status => x_return_status
1837 ,x_msg_count => x_msg_count
1838 ,x_msg_data => x_msg_data);
1839
1840 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1841 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at BSC_DATASETS_PUB.Update_Measures');
1842 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1843 END IF;
1844 END IF;
1845
1846 -- Fix for Bug#3781176
1847 -- Delete the source columns that may no more be used and are dangling
1848 -- This is called after update, since the API checks if the passed measure_id is being used or not.
1849 IF (l_Delete_Source = TRUE) THEN
1850 -- this API deletes only the source columns (bsc_sys_measures) and not the actual measure itself.
1851 BSC_DATASETS_PVT.Delete_Measures(
1852 p_commit => p_commit
1853 ,p_Dataset_Rec => l_Del_Dataset_Rec
1854 ,x_return_status => x_return_status
1855 ,x_msg_count => x_msg_count
1856 ,x_msg_data => x_msg_data
1857 );
1858 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1859 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1860 END IF;
1861 END IF;
1862
1863 -- relaxed update condition for DB columns for all types of measures
1864 -- Enhancement Bug#4239216
1865 IF ( c_measure_col_cur%ISOPEN) THEN
1866 CLOSE c_measure_col_cur;
1867 END IF;
1868
1869 OPEN c_measure_col_cur(l_Dataset_Rec.Bsc_Measure_Col);
1870
1871 -- Moved the fetch cursor before the IF condition to ensure that
1872 -- the IF condition is satisified when the cursor has rows.
1873 -- fixed for Bug#3284277
1874
1875 FETCH c_measure_col_cur
1876 INTO l_measure_group_id, l_projection_id, l_measure_type, l_measure_col_help;
1877
1878 if (c_measure_col_cur%FOUND) then
1879
1880 -- when changing the measure from formula to single col
1881 -- measure group id, projection id and measure type is passed null
1882 -- retrieving from the BSC_DB_MEASURE_C0LS_VL
1883 -- Bug#3237284
1884
1885 -- Bug#3817894: Update the Measure columns
1886 IF ((p_Measure_Col_Help IS NOT NULL) AND (p_Measure_Col_Help <> l_Measure_Col_Help)) THEN
1887 l_Measure_Col_Help := p_Measure_Col_Help;
1888 END IF;
1889
1890 --DBMS_OUTPUT.PUT_LINE('calling bsc_db_measure_cols_pkg.update_row');
1891 BSC_DB_MEASURE_COLS_PKG.Update_Row
1892 ( x_Measure_Col => l_Dataset_Rec.Bsc_Measure_Col
1893 , x_Measure_Group_Id => NVL(p_measure_group_id, l_measure_group_id)
1894 , x_Projection_Id => NVL(l_Dataset_Rec.Bsc_Measure_Projection_Id,l_projection_id)
1895 , x_Measure_Type => NVL(p_Measure_Type, l_measure_type)
1896 , x_Help => NVL(l_measure_col_help, l_Dataset_Rec.Bsc_Measure_Col)
1897 );
1898 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1899 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at bsc_db_measure_cols_pkg.update_row');
1900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1901 END IF;
1902 else
1903 IF NOT (isFormula(l_Dataset_Rec.Bsc_Measure_Col) OR l_Dataset_Rec.Bsc_Source = c_CDS) THEN
1904 --DBMS_OUTPUT.PUT_LINE('calling bsc_db_measure_cols_pkg.insert_row');
1905 bsc_db_measure_cols_pkg.insert_row(
1906 l_Dataset_Rec.Bsc_Measure_Col
1907 ,p_measure_group_id
1908 ,l_Dataset_Rec.Bsc_Measure_Projection_Id
1909 ,p_Measure_Type
1910 ,l_measure_col_help);
1911 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1912 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at bsc_db_measure_cols_pkg.insert_row');
1913 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1914 END IF;
1915 end if;
1916 end if;
1917
1918 -- close the cursor c_measure_col_cur - Bug#3237284
1919 CLOSE c_measure_col_cur;
1920
1921
1922 -- Update PMF tables
1923 begin
1924 select pm.measure_id
1925 into l_Measure_Rec.Measure_Id
1926 from bisbv_performance_measures pm
1927 where dataset_id = p_dataset_id;
1928 exception
1929 when no_data_found then l_Measure_Rec.Measure_Id := null;
1930 end;
1931
1932 --- mdamle 07/07/2003 - Added indicator dimensions
1933 l_Measure_rec.Dimension1_Id := p_Dimension1_id;
1934 l_Measure_rec.Dimension2_Id := p_Dimension2_id;
1935 l_Measure_rec.Dimension3_Id := p_Dimension3_id;
1936 l_Measure_rec.Dimension4_Id := p_Dimension4_id;
1937 l_Measure_rec.Dimension5_Id := p_Dimension5_id;
1938 l_Measure_rec.Dimension6_Id := p_Dimension6_id;
1939 l_Measure_rec.Dimension7_Id := p_Dimension7_id;
1940
1941 l_measure_rec.Dataset_id := p_dataset_id;
1942 l_measure_rec.Measure_Short_Name := l_Dataset_Rec.Bsc_Measure_Short_Name;
1943
1944 l_measure_rec.Description := p_dataset_help;
1945 l_measure_rec.Unit_Of_Measure_Class := p_measure_uom_class;
1946 l_measure_rec.actual_data_source_type := p_measure_act_data_src_type ;
1947 l_measure_rec.actual_data_source := p_measure_act_data_src;
1948 l_measure_rec.comparison_source := p_measure_comparison_source;
1949 l_measure_rec.increase_in_measure := p_measure_increase_in_measure;
1950 l_measure_rec.function_name := p_measure_function_name;
1951 l_measure_rec.enable_link := p_measure_enable_link;
1952 l_measure_rec.obsolete := p_measure_obsolete;
1953 l_measure_rec.measure_type := p_type;
1954 l_measure_rec.is_validate := p_measure_is_validate;
1955
1956 --sawu: 9/1/04: populates region_app_id and attribute_code_app_id for ak_region_items also
1957 l_measure_rec.Region_App_Id := p_region_app_id;
1958 l_measure_rec.Source_Column_App_Id := p_source_column_app_id;
1959 l_measure_rec.Compare_Column_App_Id := p_compare_column_app_id;
1960
1961 --sawu: populate WHO column
1962 l_measure_rec.Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1963 l_measure_rec.Last_Updated_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1964 l_measure_rec.Last_Update_Login := fnd_global.LOGIN_ID;
1965 l_measure_rec.Last_Update_Date := l_last_update_date;
1966
1967 --rpenneru: 12/22/04 Populate Functional Area Short name
1968 l_measure_rec.Func_Area_Short_Name := p_func_area_short_name;
1969
1970 if (p_measure_app_id is null) then
1971 l_measure_rec.Application_Id := 271;
1972 else
1973 l_measure_rec.Application_Id := p_measure_app_id;
1974 end if;
1975 if (l_Measure_Rec.Measure_id is not null) then
1976 --DBMS_OUTPUT.PUT_LINE('calling BIS_MEASURE_PUB.Update_Measure');
1977 BIS_MEASURE_PUB.Update_Measure(
1978 p_api_version => 1.0
1979 ,p_commit => p_commit
1980 ,p_Measure_Rec => l_measure_rec
1981 ,p_owner => p_owner
1982 ,x_return_status => x_return_status
1983 ,x_error_tbl => l_error_tbl);
1984 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1985 IF (l_error_tbl.COUNT > 0) THEN
1986 x_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
1987 IF(INSTR(x_msg_data, ' ') = 0 ) THEN
1988 FND_MESSAGE.SET_NAME('BIS',x_msg_data);
1989 FND_MSG_PUB.ADD;
1990 x_msg_data := NULL;
1991 END IF;
1992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1993 END IF;
1994 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at BIS_MEASURE_PUB.Update_Measure');
1995 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1996 END IF;
1997 else
1998 -- Normally, PMF measure should always exist once BSC measure was created.
1999 -- Code should not reach here under normal circumstances.
2000 --DBMS_OUTPUT.PUT_LINE('calling BIS_MEASURE_PUB.Create_Measure');
2001 BIS_MEASURE_PUB.Create_Measure(
2002 p_api_version => 1.0
2003 ,p_commit => p_commit
2004 ,p_Measure_Rec => l_measure_rec
2005 ,p_owner => p_owner
2006 ,x_return_status => x_return_status
2007 ,x_error_tbl => l_error_tbl);
2008 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
2009 IF (l_error_tbl.COUNT > 0) THEN
2010 x_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
2011 IF(INSTR(x_msg_data, ' ') = 0 ) THEN
2012 FND_MESSAGE.SET_NAME('BIS',x_msg_data);
2013 FND_MSG_PUB.ADD;
2014 x_msg_data := NULL;
2015 END IF;
2016 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2017 END IF;
2018 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at BIS_MEASURE_PUB.Create_Measure');
2019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2020 END IF;
2021 end if;
2022
2023 -- At this point, the Measures would have been updated.
2024 -- need to change the TimeStamps.
2025
2026 -- START Granular Locking
2027
2028
2029 -- Change the time stamp of the Current Dataset (Measure)
2030 IF (l_Dataset_Rec.Bsc_dataset_id is not null) THEN
2031 --DBMS_OUTPUT.PUT_LINE('calling BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASET');
2032 BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASET(
2033 p_dataset_id => l_Dataset_Rec.Bsc_dataset_id
2034 ,p_lud => l_Dataset_Rec.Bsc_Dataset_Last_Update_Date
2035 ,x_return_status => x_return_status
2036 ,x_msg_count => x_msg_count
2037 ,x_msg_data => x_msg_data
2038 ) ;
2039
2040 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
2041 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASET');
2042 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2043 END IF;
2044 END IF;
2045 -- Change the time stamp of the Current Datasource (1)
2046 IF (l_Dataset_Rec.Bsc_Measure_Id is not null) THEN
2047 --DBMS_OUTPUT.PUT_LINE('calling BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASOURCE');
2048 BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASOURCE(
2049 p_measure_id => l_Dataset_Rec.Bsc_Measure_Id
2050 ,p_lud => l_Dataset_Rec.Bsc_Dataset_Last_Update_Date
2051 ,x_return_status => x_return_status
2052 ,x_msg_count => x_msg_count
2053 ,x_msg_data => x_msg_data
2054 ) ;
2055
2056 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
2057 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASOURCE');
2058 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2059 END IF;
2060 END IF;
2061 -- Change the time stamp of the Current Datasource (2)
2062 IF (l_Dataset_Rec.Bsc_Measure_Id2 is not null) THEN
2063 --DBMS_OUTPUT.PUT_LINE('calling BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASOURCE');
2064 BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASOURCE(
2065 p_measure_id => l_Dataset_Rec.Bsc_Measure_Id2
2066 ,p_lud => l_Dataset_Rec.Bsc_Dataset_Last_Update_Date
2067 ,x_return_status => x_return_status
2068 ,x_msg_count => x_msg_count
2069 ,x_msg_data => x_msg_data
2070 ) ;
2071 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
2072 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASOURCE');
2073 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2074 END IF;
2075 END IF;
2076 -- END Granular Locking
2077
2078 --DBMS_OUTPUT.PUT_LINE('calling BSC_UTILITY.Add_To_Fnd_Msg_Stack');
2079 BSC_UTILITY.Add_To_Fnd_Msg_Stack(
2080 p_error_tbl => l_error_tbl
2081 ,x_return_status => x_return_status
2082 ,x_msg_count => x_msg_count
2083 ,x_msg_data => x_msg_data);
2084 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
2085 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.BSC_BIS_MEASURE_PUB Failed: at BSC_UTILITY.Add_To_Fnd_Msg_Stack');
2086 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2087 END IF;
2088 EXCEPTION
2089 WHEN FND_API.G_EXC_ERROR THEN
2090 IF ( c_measure_col_cur%ISOPEN) THEN
2091 CLOSE c_measure_col_cur;
2092 END IF;
2093 IF ( c_Bsc_Measure_Color_Formula%ISOPEN) THEN
2094 CLOSE c_Bsc_Measure_Color_Formula;
2095 END IF;
2096 IF (x_msg_data IS NULL) THEN
2097 FND_MSG_PUB.Count_And_Get
2098 ( p_encoded => FND_API.G_FALSE
2099 , p_count => x_msg_count
2100 , p_data => x_msg_data
2101 );
2102 END IF;
2103 ROLLBACK TO SP_UPDATE_MEASURE;
2104 x_return_status := FND_API.G_RET_STS_ERROR;
2105 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2106 IF ( c_measure_col_cur%ISOPEN) THEN
2107 CLOSE c_measure_col_cur;
2108 END IF;
2109 IF ( c_Bsc_Measure_Color_Formula%ISOPEN) THEN
2110 CLOSE c_Bsc_Measure_Color_Formula;
2111 END IF;
2112 IF (x_msg_data IS NULL) THEN
2113 FND_MSG_PUB.Count_And_Get
2114 ( p_encoded => FND_API.G_FALSE
2115 , p_count => x_msg_count
2116 , p_data => x_msg_data
2117 );
2118 END IF;
2119 ROLLBACK TO SP_UPDATE_MEASURE;
2120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2121 WHEN NO_DATA_FOUND THEN
2122 IF ( c_measure_col_cur%ISOPEN) THEN
2123 CLOSE c_measure_col_cur;
2124 END IF;
2125 IF ( c_Bsc_Measure_Color_Formula%ISOPEN) THEN
2126 CLOSE c_Bsc_Measure_Color_Formula;
2127 END IF;
2128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2129 IF (x_msg_data IS NOT NULL) THEN
2130 x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Update_measure ';
2131 ELSE
2132 x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Update_measure ';
2133 END IF;
2134 ROLLBACK TO SP_UPDATE_MEASURE;
2135 WHEN OTHERS THEN
2136 IF ( c_measure_col_cur%ISOPEN) THEN
2137 CLOSE c_measure_col_cur;
2138 END IF;
2139 IF ( c_Bsc_Measure_Color_Formula%ISOPEN) THEN
2140 CLOSE c_Bsc_Measure_Color_Formula;
2141 END IF;
2142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2143 IF (x_msg_data IS NOT NULL) THEN
2144 x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Update_measure ';
2145 ELSE
2146 x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Update_measure ';
2147 END IF;
2148 ROLLBACK TO SP_UPDATE_MEASURE;
2149 end Update_measure;
2150
2151
2152 procedure Delete_measure(
2153 p_commit IN VARCHAR2 := FND_API.G_FALSE
2154 ,p_dataset_id IN NUMBER
2155 ,p_time_stamp IN VARCHAR2 := NULL -- Added for Granular Locking
2156 ,x_return_status OUT NOCOPY VARCHAR2
2157 ,x_msg_count OUT NOCOPY NUMBER
2158 ,x_msg_data OUT NOCOPY VARCHAR2
2159 ) is
2160
2161 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
2162 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
2163 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2164 l_count number;
2165 l_Meas_Extn_Rec BIS_OBJECT_EXTENSIONS_PUB.Measure_Extension_Type;
2166
2167 begin
2168 fnd_msg_pub.initialize;
2169
2170 l_Dataset_Rec.Bsc_dataset_id := p_dataset_id;
2171
2172 -- START : Granular Locking Needs do come on the top.- Fixed by ADRAO
2173 BSC_BIS_LOCKS_PUB.LOCK_DELETE_MEASURE(
2174 p_dataset_id => l_Dataset_Rec.Bsc_dataset_id
2175 ,p_time_stamp => p_time_stamp
2176 ,x_return_status => x_return_status
2177 ,x_msg_count => x_msg_count
2178 ,x_msg_data => x_msg_data
2179 ) ;
2180 -- Get the Measure Short Name
2181 SELECT short_name INTO l_Meas_Extn_Rec.Measure_Short_Name FROM bis_indicators WHERE dataset_id = p_dataset_id;
2182
2183
2184 -- Added measure_id2 to be passed to lower APIs Bug#3628113
2185 select measure_id1, measure_id2, pm.measure_id
2186 into l_Dataset_Rec.Bsc_Measure_Id, l_Dataset_Rec.Bsc_Measure_Id2, l_Measure_Rec.Measure_Id
2187 from bsc_sys_datasets_B d, bisbv_performance_measures pm
2188 where d.dataset_id = p_dataset_id
2189 and d.dataset_id = pm.dataset_id (+);
2190
2191
2192 -- The APIs should check for return status ...
2193 -- Raising an unexpected error.
2194 IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
2195 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_SET_PUB.CREATE_DIM_SET Failed: at BSC_DIMENSION_SETS_PUB.Create_Bsc_Kpi_Dim_Sets_Tl');
2196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2197 END IF;
2198
2199 -- END : Granular Locking
2200
2201 BSC_DATASETS_PUB.Delete_Measures(
2202 p_commit => p_commit
2203 ,p_Dataset_Rec => l_Dataset_Rec
2204 ,x_return_status => x_return_status
2205 ,x_msg_count => x_msg_count
2206 ,x_msg_data => x_msg_data);
2207
2208 IF ( (x_return_status = FND_API.G_RET_STS_SUCCESS) OR (x_return_status IS NULL) ) THEN
2209 -- Delete from PMF tables
2210 IF (l_Measure_Rec.Measure_Id IS NOT NULL) THEN
2211 BIS_MEASURE_PUB.Delete_Measure(
2212 p_api_version => 1.0
2213 ,p_commit => p_commit
2214 ,p_Measure_Rec => l_Measure_Rec
2215 ,x_return_status => x_return_status
2216 ,x_error_Tbl => l_error_tbl);
2217
2218 BSC_UTILITY.Add_To_Fnd_Msg_Stack(
2219 p_error_tbl => l_error_tbl
2220 ,x_return_status => x_return_status
2221 ,x_msg_count => x_msg_count
2222 ,x_msg_data => x_msg_data);
2223 END IF;
2224 END IF;
2225 -- Checks if the Functional Area exists
2226 SELECT
2227 COUNT(1) INTO l_count
2228 FROM
2229 BIS_MEASURES_EXTENSION_VL
2230 WHERE
2231 MEASURE_SHORT_NAME = l_Meas_Extn_Rec.Measure_Short_Name;
2232 -- If Functional Area Exists then removes that
2233 IF (l_count > 0) THEN
2234 BIS_OBJECT_EXTENSIONS_PUB.Delete_Measure_Extension(
2235 p_Api_Version =>1.0
2236 ,p_Commit => p_commit
2237 ,p_Meas_Extn_Rec => l_Meas_Extn_Rec
2238 ,x_Return_Status => x_Return_Status
2239 ,x_Msg_Count => x_Msg_Count
2240 ,x_Msg_Data => x_Msg_Data
2241 );
2242 END IF;
2243 EXCEPTION
2244 WHEN FND_API.G_EXC_ERROR THEN
2245 x_return_status := FND_API.G_RET_STS_ERROR;
2246 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2247 ,p_data => x_msg_data);
2248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2250 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2251 ,p_data => x_msg_data);
2252 WHEN NO_DATA_FOUND THEN
2253 x_return_status := FND_API.G_RET_STS_ERROR;
2254 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2255 ,p_data => x_msg_data);
2256 WHEN OTHERS THEN
2257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2258 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2259 ,p_data => x_msg_data);
2260 if (x_msg_data is null) then
2261 x_msg_data := SQLERRM;
2262 end if;
2263 end delete_measure;
2264
2265
2266 /************************************************************************************
2267 -- API name : Cascade_Disable_Calculation
2268 -- Type : Private
2269 ************************************************************************************/
2270 PROCEDURE Cascade_Disable_Calculation(
2271 p_commit IN VARCHAR2 := FND_API.G_FALSE
2272 ,p_dataset_id IN NUMBER
2273 ,p_disabled_calculation IN NUMBER
2274 ,x_return_status OUT NOCOPY VARCHAR2
2275 ,x_msg_count OUT NOCOPY NUMBER
2276 ,x_msg_data OUT NOCOPY VARCHAR2
2277 ) IS
2278
2279 l_kpi_measure_props_rec bsc_kpi_measure_props_pub.kpi_measure_props_rec;
2280 l_kpi_measure_id bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE;
2281
2282 CURSOR c_kpis IS
2283 SELECT
2284 km.indicator,
2285 km.analysis_option0,
2286 km.analysis_option1,
2287 km.analysis_option2,
2288 km.series_id
2289 FROM
2290 bsc_kpi_analysis_measures_b km,
2291 bsc_kpi_measure_props kp
2292 WHERE
2293 kp.indicator = km.indicator AND
2294 kp.kpi_measure_id = km.kpi_measure_id AND
2295 km.dataset_id = p_dataset_id AND
2296 kp.default_calculation IS NOT NULL AND
2297 kp.default_calculation = p_disabled_calculation;
2298
2299 BEGIN
2300
2301 SAVEPOINT Cascade_Disable_Calc_PUB;
2302 x_return_status := FND_API.G_RET_STS_SUCCESS;
2303 FND_MSG_PUB.Initialize;
2304
2305 FOR cd IN c_kpis LOOP
2306 BSC_KPI_SERIES_PUB.Save_Default_Calculation(
2307 p_commit => FND_API.G_FALSE
2308 ,p_Indicator => cd.indicator
2309 ,p_Analysis_Option0 => cd.analysis_option0
2310 ,p_Analysis_Option1 => cd.analysis_option1
2311 ,p_Analysis_Option2 => cd.analysis_option2
2312 ,p_Series_Id => cd.series_id
2313 ,p_default_calculation => NULL
2314 ,x_return_status => x_return_status
2315 ,x_msg_count => x_msg_count
2316 ,x_msg_data => x_msg_data
2317 );
2318 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2320 END IF;
2321 END LOOP;
2322
2323 IF FND_API.To_Boolean(p_Commit) THEN
2324 COMMIT;
2325 END IF;
2326 EXCEPTION
2327 WHEN FND_API.G_EXC_ERROR THEN
2328 ROLLBACK TO Cascade_Disable_Calc_PUB;
2329 IF (x_msg_data IS NULL) THEN
2330 FND_MSG_PUB.Count_And_Get
2331 ( p_encoded => FND_API.G_FALSE
2332 , p_count => x_msg_count
2333 , p_data => x_msg_data
2334 );
2335 END IF;
2336 x_return_status := FND_API.G_RET_STS_ERROR;
2337 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2338 ROLLBACK TO Cascade_Disable_Calc_PUB;
2339 IF (x_msg_data IS NULL) THEN
2340 FND_MSG_PUB.Count_And_Get
2341 ( p_encoded => FND_API.G_FALSE
2342 , p_count => x_msg_count
2343 , p_data => x_msg_data
2344 );
2345 END IF;
2346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2347 WHEN NO_DATA_FOUND THEN
2348 ROLLBACK TO Cascade_Disable_Calc_PUB;
2349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2350 IF (x_msg_data IS NOT NULL) THEN
2351 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Cascade_Disable_Calculation ';
2352 ELSE
2353 x_msg_data := SQLERRM||'BSC_KPI_SERIES_PUB.Cascade_Disable_Calculation ';
2354 END IF;
2355 WHEN OTHERS THEN
2356 ROLLBACK TO Cascade_Disable_Calc_PUB;
2357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2358 IF (x_msg_data IS NOT NULL) THEN
2359 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Cascade_Disable_Calculation ';
2360 ELSE
2361 x_msg_data := SQLERRM||' at BSC_KPI_SERIES_PUB.Cascade_Disable_Calculation ';
2362 END IF;
2363 END Cascade_Disable_Calculation;
2364
2365 procedure Apply_Dataset_Calc(
2366 p_commit IN VARCHAR2 := FND_API.G_FALSE
2367 ,p_dataset_id IN NUMBER
2368 ,p_disabled_calc_table IN BSC_NUM_LIST
2369 ,x_return_status OUT NOCOPY VARCHAR2
2370 ,x_msg_count OUT NOCOPY NUMBER
2371 ,x_msg_data OUT NOCOPY VARCHAR2
2372 ) is
2373
2374 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
2375 begin
2376
2377 fnd_msg_pub.initialize;
2378
2379 l_Dataset_Rec.Bsc_Dataset_Id:= p_dataset_id;
2380
2381 -- Disabled Calculations
2382 BSC_DATASETS_PUB.Delete_Dataset_Calc(
2383 p_commit => p_commit
2384 ,p_Dataset_Rec => l_Dataset_Rec
2385 ,x_return_status => x_return_status
2386 ,x_msg_count => x_msg_count
2387 ,x_msg_data => x_msg_data);
2388
2389 if p_disabled_calc_table is not null then
2390 for i in 1..p_disabled_calc_table.count loop
2391 l_Dataset_Rec.Bsc_Disabled_Calc_Id := p_disabled_calc_table(i);
2392 BSC_DATASETS_PVT.Create_Dataset_Calc(
2393 p_commit => p_commit
2394 ,p_Dataset_Rec => l_Dataset_Rec
2395 ,x_return_status => x_return_status
2396 ,x_msg_count => x_msg_count
2397 ,x_msg_data => x_msg_data);
2398
2399 Cascade_Disable_Calculation(
2400 p_commit => FND_API.G_FALSE
2401 ,p_dataset_id => p_dataset_id
2402 ,p_disabled_calculation => p_disabled_calc_table(i)
2403 ,x_return_status => x_return_status
2404 ,x_msg_count => x_msg_count
2405 ,x_msg_data => x_msg_data
2406 );
2407 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2409 END IF;
2410 end loop;
2411 end if;
2412
2413 -- Change the time stamp of the Current Dataset (Measure)
2414 IF (p_dataset_id is not null) THEN
2415 BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASET(
2416 p_dataset_id => p_dataset_id
2417 ,x_return_status => x_return_status
2418 ,x_msg_count => x_msg_count
2419 ,x_msg_data => x_msg_data
2420 ) ;
2421
2422 IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
2423 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_SET_PUB.CREATE_DIM_SET Failed: at BSC_DIMENSION_SETS_PUB.Create_Bsc_Kpi_Dim_Sets_Tl');
2424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2425 END IF;
2426 END IF;
2427
2428
2429 EXCEPTION
2430 WHEN FND_API.G_EXC_ERROR THEN
2431 x_return_status := FND_API.G_RET_STS_ERROR;
2432 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2433 ,p_data => x_msg_data);
2434 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2436 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2437 ,p_data => x_msg_data);
2438 WHEN NO_DATA_FOUND THEN
2439 x_return_status := FND_API.G_RET_STS_ERROR;
2440 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2441 ,p_data => x_msg_data);
2442 WHEN OTHERS THEN
2443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2444 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2445 ,p_data => x_msg_data);
2446 if (x_msg_data is null) then
2447 x_msg_data := SQLERRM;
2448 end if;
2449 end Apply_Dataset_Calc;
2450
2451
2452 PROCEDURE Apply_Cause_Effect_Rels(
2453 p_commit IN VARCHAR2 := FND_API.G_FALSE
2454 ,p_dataset_id IN NUMBER
2455 ,p_causes_table IN BSC_NUM_LIST
2456 ,p_effects_table IN BSC_NUM_LIST
2457 ,x_return_status OUT NOCOPY VARCHAR2
2458 ,x_msg_count OUT NOCOPY NUMBER
2459 ,x_msg_data OUT NOCOPY VARCHAR2
2460 ) is
2461
2462 l_Bsc_Cause_Effect_Rel_Rec BSC_CAUSE_EFFECT_REL_PUB.Bsc_Cause_Effect_Rel_Rec;
2463 l_measure_names varchar2(32000);
2464 l_name bsc_sys_datasets_tl.name%TYPE;
2465 l_max_count number := 1500;
2466 l_temp_dataset_id NUMBER;
2467 l_found BOOLEAN;
2468 CURSOR c_cause_list
2469 IS
2470 SELECT
2471 cause_indicator
2472 FROM
2473 bsc_kpi_cause_effect_rels
2474 WHERE
2475 effect_level = 'DATASET'
2476 AND effect_indicator = p_dataset_id;
2477
2478 CURSOR c_effect_list
2479 IS
2480 SELECT
2481 effect_indicator
2482 FROM
2483 bsc_kpi_cause_effect_rels
2484 WHERE
2485 cause_level = 'DATASET'
2486 AND cause_indicator = p_dataset_id;
2487
2488 begin
2489
2490 fnd_msg_pub.initialize;
2491
2492 -- mdamle 08/18/2003 - Check for same cause and effect measure
2493 if p_causes_table is not null and p_effects_table is not null then
2494 for i in 1..p_causes_table.count loop
2495 for j in 1..p_effects_table.count loop
2496 if p_effects_table(j) = p_causes_table(i) then
2497 select name into l_name
2498 from bsc_sys_datasets_vl
2499 where dataset_id = p_causes_table(i);
2500
2501 if (l_measure_names is null) then
2502 l_measure_names := l_name;
2503 else
2504 if(length(l_measure_names || ', ' || l_name) < l_max_count) then
2505 l_measure_names := l_measure_names || ', ' || l_name;
2506 end if;
2507 end if;
2508 end if;
2509 end loop;
2510 end loop;
2511
2512 if l_measure_names is not null then
2513 FND_MESSAGE.SET_NAME('BSC','BSC_CAE_USED_AT_SAME_TIME');
2514 FND_MESSAGE.SET_TOKEN('LIST', l_measure_names);
2515 FND_MSG_PUB.ADD;
2516 RAISE FND_API.G_EXC_ERROR;
2517 end if;
2518 end if;
2519
2520 OPEN c_cause_list ;
2521 LOOP
2522 FETCH c_cause_list INTO l_temp_dataset_id;
2523 EXIT WHEN c_cause_list%NOTFOUND;
2524 IF p_causes_table IS NOT NULL THEN
2525 l_found := FALSE;
2526 FOR i IN 1..p_causes_table.COUNT LOOP
2527 IF(l_temp_dataset_id = p_causes_table(i)) THEN
2528 l_found := TRUE;
2529 END IF;
2530 END LOOP;
2531 IF NOT l_found THEN
2532 BIS_CUSTOM_CAUSE_EFFECT_PVT.Delete_Custom_Cause_Effect_Rel(
2533 p_commit => p_commit
2534 , p_Cause_DataSetId => l_temp_dataset_id
2535 , p_Effect_DataSetId => p_dataset_id
2536 , x_return_status => x_return_status
2537 , x_msg_count => x_msg_count
2538 , x_msg_data => x_msg_data
2539 );
2540 END IF;
2541 END IF;
2542 END LOOP;
2543 CLOSE c_cause_list;
2544
2545
2546 OPEN c_effect_list ;
2547 LOOP
2548 FETCH c_effect_list INTO l_temp_dataset_id;
2549 EXIT WHEN c_effect_list%NOTFOUND;
2550 IF p_effects_table IS NOT NULL THEN
2551 l_found := FALSE;
2552 FOR i IN 1..p_effects_table.COUNT LOOP
2553 IF(l_temp_dataset_id = p_effects_table(i)) THEN
2554 l_found := TRUE;
2555 END IF;
2556 END LOOP;
2557 IF NOT l_found THEN
2558 --Delete Customizations
2559 BIS_CUSTOM_CAUSE_EFFECT_PVT.Delete_Custom_Cause_Effect_Rel(
2560 p_commit => p_commit
2561 , p_Cause_DataSetId => p_dataset_id
2562 , p_Effect_DataSetId => l_temp_dataset_id
2563 , x_return_status => x_return_status
2564 , x_msg_count => x_msg_count
2565 , x_msg_data => x_msg_data
2566 );
2567 END IF;
2568 END IF;
2569 END LOOP;
2570 CLOSE c_effect_list;
2571
2572 BSC_CAUSE_EFFECT_REL_PUB.Delete_All_Cause_Effect_Rels(
2573 p_commit => p_commit
2574 ,p_indicator => p_dataset_id
2575 ,p_level => BSC_BIS_MEASURE_PUB.c_LEVEL
2576 ,x_return_status => x_return_status
2577 ,x_msg_count => x_msg_count
2578 ,x_msg_data => x_msg_data);
2579
2580 if p_causes_table is not null then
2581 for i in 1..p_causes_table.count loop
2582 l_Bsc_Cause_Effect_Rel_Rec.Cause_Indicator := p_causes_table(i);
2583 l_Bsc_Cause_Effect_Rel_Rec.Cause_Level := BSC_BIS_MEASURE_PUB.c_LEVEL;
2584 l_Bsc_Cause_Effect_Rel_Rec.Effect_Indicator := p_dataset_id;
2585 l_Bsc_Cause_Effect_Rel_Rec.Effect_Level := BSC_BIS_MEASURE_PUB.c_LEVEL;
2586
2587 BSC_CAUSE_EFFECT_REL_PUB.Create_Cause_Effect_Rel(
2588 p_commit => p_commit
2589 ,p_Bsc_Cause_Effect_Rel_Rec => l_Bsc_Cause_Effect_Rel_Rec
2590 ,x_return_status => x_return_status
2591 ,x_msg_count => x_msg_count
2592 ,x_msg_data => x_msg_data);
2593
2594 end loop;
2595 end if;
2596
2597
2598 if p_effects_table is not null then
2599 for i in 1..p_effects_table.count loop
2600 l_Bsc_Cause_Effect_Rel_Rec.Cause_Indicator := p_dataset_id;
2601 l_Bsc_Cause_Effect_Rel_Rec.Cause_Level := BSC_BIS_MEASURE_PUB.c_LEVEL;
2602 l_Bsc_Cause_Effect_Rel_Rec.Effect_Indicator := p_effects_table(i);
2603 l_Bsc_Cause_Effect_Rel_Rec.Effect_Level := BSC_BIS_MEASURE_PUB.c_LEVEL;
2604
2605 BSC_CAUSE_EFFECT_REL_PUB.Create_Cause_Effect_Rel(
2606 p_commit => p_commit
2607 ,p_Bsc_Cause_Effect_Rel_Rec => l_Bsc_Cause_Effect_Rel_Rec
2608 ,x_return_status => x_return_status
2609 ,x_msg_count => x_msg_count
2610 ,x_msg_data => x_msg_data);
2611
2612 end loop;
2613 end if;
2614
2615 -- Change the time stamp of the Current Dataset (Measure)
2616 IF (p_dataset_id is not null) THEN
2617 BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASET(
2618 p_dataset_id => p_dataset_id
2619 ,x_return_status => x_return_status
2620 ,x_msg_count => x_msg_count
2621 ,x_msg_data => x_msg_data
2622 ) ;
2623
2624 IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
2625 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_DIM_SET_PUB.CREATE_DIM_SET Failed: at BSC_DIMENSION_SETS_PUB.Create_Bsc_Kpi_Dim_Sets_Tl');
2626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2627 END IF;
2628 END IF;
2629
2630
2631 EXCEPTION
2632 WHEN FND_API.G_EXC_ERROR THEN
2633 x_return_status := FND_API.G_RET_STS_ERROR;
2634 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2635 ,p_data => x_msg_data);
2636 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2638 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2639 ,p_data => x_msg_data);
2640 WHEN NO_DATA_FOUND THEN
2641 x_return_status := FND_API.G_RET_STS_ERROR;
2642 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2643 ,p_data => x_msg_data);
2644 WHEN OTHERS THEN
2645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2646 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2647 ,p_data => x_msg_data);
2648 if (x_msg_data is null) then
2649 x_msg_data := SQLERRM;
2650 end if;
2651 end Apply_Cause_Effect_Rels;
2652
2653
2654
2655 function getColorFormula(
2656 p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
2657 ,p_Measure_Apply_Rollup IN VARCHAR2) return varchar2
2658 is
2659 l_column_name varchar2(30);
2660 l_color_formula varchar2(4000) := NULL;
2661 begin
2662 if (p_Measure_apply_rollup is not null and p_Measure_apply_rollup = 'Y') then
2663
2664 if (BSC_APPS.Get_Property_Value(p_dataset_rec.Bsc_Measure_color_formula, c_FORMULA_SOURCE) is null) then
2665 select BSC_INTERNAL_COLUMN_S.nextval into l_column_name from dual;
2666 l_column_name := c_INTERNAL_COLUMN_NAME || l_column_name;
2667 l_color_formula := BSC_APPS.Set_Property_Value(NULL, c_FORMULA_SOURCE, l_column_name);
2668 ELSE
2669 l_color_formula := SUBSTR(p_dataset_rec.Bsc_Measure_color_formula, 0, (INSTR(p_dataset_rec.Bsc_Measure_color_formula, '&')-1));
2670 END IF;
2671 else
2672 if (p_Dataset_Rec.Bsc_Measure_operation = c_AVGL_CODE) and isFormula(p_Dataset_Rec.Bsc_Measure_Col) then
2673 -- Do not allow this condition
2674 FND_MESSAGE.SET_NAME('BSC','BSC_AVGLOWESTLEVEL_ERR_TXT');
2675 FND_MSG_PUB.ADD;
2676 RAISE FND_API.G_EXC_ERROR;
2677 end if;
2678 end if;
2679
2680 -- Insert pAvgL=...
2681 if (p_Dataset_Rec.Bsc_Measure_operation = c_AVGL_CODE) then
2682 l_color_formula := BSC_APPS.Set_Property_Value(l_color_formula, c_AVGL, 'Y');
2683 else
2684 l_color_formula := BSC_APPS.Set_Property_Value(l_color_formula, c_AVGL, 'N');
2685 end if;
2686
2687 return l_color_formula;
2688
2689 end getColorFormula;
2690
2691 --
2692 -- 16-JUN-2003 Ravi added for Assign Dimension to KPI enh
2693 --
2694
2695 FUNCTION GET_AO_NAME
2696 (
2697 p_indicator in NUMBER
2698 , p_a0 in NUMBER
2699 , p_a1 in NUMBER
2700 , p_a2 in NUMBER
2701 , p_group_id in NUMBER
2702 ) RETURN VARCHAR2 IS
2703 l_group_id NUMBER;
2704
2705 h_ag_count NUMBER;
2706 l_anal_name bsc_kpi_analysis_options_tl.name%TYPE := NULL; -- changed for bug 3165012
2707 h_ag1_depend NUMBER;
2708 h_ag2_depend NUMBER;
2709 h_ag_depend NUMBER;
2710 BEGIN
2711 l_group_id := p_group_id;
2712
2713 SELECT MAX( ANALYSIS_GROUP_ID)
2714 INTO h_ag_count
2715 FROM BSC_KPI_ANALYSIS_GROUPS
2716 WHERE INDICATOR = p_indicator;
2717
2718 IF (l_group_id= 0) THEN
2719
2720 SELECT NAME INTO l_anal_name
2721 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
2722 WHERE ANALYSIS_GROUP_ID =0
2723 AND OPTION_ID = p_a0
2724 AND INDICATOR = p_indicator;
2725 ELSIF(l_group_id =1 AND h_ag_count >0) THEN
2726 SELECT DEPENDENCY_FLAG INTO h_ag_depend
2727 FROM BSC_KPI_ANALYSIS_GROUPS
2728 WHERE ANALYSIS_GROUP_ID =1
2729 AND INDICATOR = p_indicator;
2730
2731 IF h_ag_depend = 0 THEN
2732 SELECT NAME INTO l_anal_name
2733 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
2734 WHERE ANALYSIS_GROUP_ID = 1
2735 AND OPTION_ID = p_a1
2736 AND INDICATOR = p_indicator;
2737 ELSE
2738
2739 BEGIN
2740 SELECT NAME INTO l_anal_name
2741 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
2742 WHERE ANALYSIS_GROUP_ID =1
2743 AND OPTION_ID = p_a1
2744 AND PARENT_OPTION_ID = p_a0
2745 AND INDICATOR = p_indicator;
2746
2747 EXCEPTION
2748 WHEN OTHERS THEN
2749 NULL;
2750 END;
2751 END IF;
2752 ELSIF((l_group_id =2 AND h_ag_count >1)) THEN
2753
2754 SELECT DEPENDENCY_FLAG
2755 INTO h_ag1_depend
2756 FROM BSC_KPI_ANALYSIS_GROUPS
2757 WHERE ANALYSIS_GROUP_ID =1
2758 AND INDICATOR = p_indicator;
2759
2760 SELECT DEPENDENCY_FLAG
2761 INTO h_ag2_depend
2762 FROM BSC_KPI_ANALYSIS_GROUPS
2763 WHERE ANALYSIS_GROUP_ID = 2
2764 AND INDICATOR = p_indicator;
2765
2766 IF h_ag2_depend = 0 THEN
2767
2768 SELECT NAME
2769 INTO l_anal_name
2770 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
2771 WHERE ANALYSIS_GROUP_ID =2
2772 AND OPTION_ID=p_a2
2773 AND INDICATOR=p_indicator;
2774 ELSE
2775 IF h_ag2_depend = 1 AND h_ag1_depend = 0 THEN
2776 BEGIN
2777 SELECT NAME
2778 INTO l_anal_name
2779 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
2780 WHERE ANALYSIS_GROUP_ID = 2
2781 AND OPTION_ID = p_a2
2782 AND PARENT_OPTION_ID = p_a1
2783 AND INDICATOR = p_indicator;
2784 EXCEPTION
2785 WHEN OTHERS
2786 THEN NULL;
2787 END;
2788 ELSE
2789 BEGIN
2790 SELECT NAME
2791 INTO l_anal_name
2792 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
2793 WHERE ANALYSIS_GROUP_ID = 2
2794 AND OPTION_ID = p_a2
2795 AND PARENT_OPTION_ID = p_a1
2796 AND GRANDPARENT_OPTION_ID = p_a0
2797 AND INDICATOR = p_indicator;
2798 EXCEPTION
2799 WHEN OTHERS THEN
2800 NULL;
2801 END;
2802 END IF;
2803 END IF;
2804 END IF;
2805 RETURN l_anal_name;
2806 EXCEPTION
2807 WHEN OTHERS THEN
2808 RETURN NULL;
2809 END GET_AO_NAME;
2810
2811
2812 --
2813 -- 16-JUN-2003 Ravi added for Assign Dimension to KPI enh
2814 --
2815
2816 FUNCTION GET_SERIES_COUNT
2817 (
2818 p_indicator IN NUMBER
2819 , p_a0 IN NUMBER
2820 , p_a1 IN NUMBER
2821 , p_a2 IN NUMBER
2822 ) RETURN NUMBER IS
2823
2824 l_count NUMBER := 0;
2825
2826 CURSOR c_SeriesCount IS
2827 SELECT COUNT(SERIES_ID)
2828 FROM BSC_KPI_ANALYSIS_MEASURES_VL
2829 WHERE INDICATOR = p_indicator
2830 AND ANALYSIS_OPTION0 = p_a0
2831 AND ANALYSIS_OPTION1 = p_a1
2832 AND ANALYSIS_OPTION2 = p_a2;
2833 BEGIN
2834 IF (c_SeriesCount%ISOPEN)THEN
2835 CLOSE c_SeriesCount;
2836 END IF;
2837
2838 OPEN c_SeriesCount;
2839 FETCH c_SeriesCount INTO l_count;
2840 CLOSE c_SeriesCount;
2841
2842 RETURN l_count;
2843 EXCEPTION
2844 WHEN OTHERS THEN
2845 RETURN 0;
2846 END GET_SERIES_COUNT;
2847
2848 --=============================================================================
2849 Procedure Load_Measure
2850 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
2851 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
2852 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
2853 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
2854 , p_custom_mode IN VARCHAR2 := NULL
2855 , p_application_short_name IN VARCHAR2
2856 , p_Org_Dimension_Short_Name IN VARCHAR2
2857 , p_Time_Dimension_Short_Name IN VARCHAR2
2858 , p_measure_group_name IN VARCHAR2
2859 , p_measure_apply_rollup IN VARCHAR2
2860 , x_return_status OUT NOCOPY VARCHAR2
2861 , x_msg_count OUT NOCOPY NUMBER
2862 , x_msg_data OUT NOCOPY VARCHAR2
2863 ) IS
2864
2865 --l_msg VARCHAR2(3000);
2866 l_measure_group_id NUMBER;
2867 l_measure_rec_p BIS_MEASURE_PUB.Measure_Rec_Type;
2868 l_measure_rec BIS_MEASURE_PUB.Measure_Rec_Type;
2869 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
2870 l_Application_rec_p BIS_Application_PVT.Application_Rec_Type;
2871 l_Application_rec BIS_Application_PVT.Application_Rec_Type;
2872 l_dataset_id NUMBER;
2873 l_measure_id1 NUMBER;
2874 l_measure_id2 NUMBER;
2875 l_return_status VARCHAR(10);
2876 l_msg_count NUMBER;
2877 l_msg_data VARCHAR2(2000);
2878 l_org_dimension_id NUMBER;
2879 l_time_dimension_id NUMBER;
2880 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2881 l_measure_rec_new BIS_MEASURE_PUB.Measure_Rec_Type;
2882 l_dataset_rec_db BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
2883 l_dataset_rec_db1 BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
2884 l_measure_rec_db BIS_MEASURE_PUB.Measure_Rec_Type;
2885 l_time_stamp VARCHAR2(200);
2886
2887 l_upload_test BOOLEAN := FALSE;
2888 l_is_create BOOLEAN := TRUE;
2889 BEGIN
2890 fnd_msg_pub.initialize;
2891 /*
2892 l_msg := 'The Performance Measure ' || p_measure_rec.measure_name ;
2893 l_msg := l_msg || ' could not be created/updated.';
2894 */
2895 l_measure_rec := p_measure_rec;
2896 l_dataset_rec := p_Dataset_Rec;
2897
2898 -- Incorporating the BISPMFLD.lct code changes here
2899
2900 -- First, check if the measure already exists.
2901 -- Below code is to move changes already done in BISPMFLD.lct
2902 -- into Load_Measure procedure
2903 -- No changes are as such done to already existing code (BISPMFLD.lct 115.37)
2904
2905 BIS_MEASURE_PUB.Retrieve_measure(
2906 p_api_version => 1.0
2907 ,p_Measure_Rec => l_measure_rec
2908 ,x_Measure_Rec => l_measure_rec_db
2909 ,x_return_status => x_return_status
2910 ,x_error_Tbl => l_error_Tbl
2911 );
2912 IF (l_measure_rec_db.dataset_id IS NOT NULL) THEN
2913 l_dataset_rec.Bsc_Dataset_Id := l_measure_rec_db.dataset_id;
2914 BSC_DATASETS_PUB.Retrieve_Dataset(
2915 p_commit => p_commit
2916 ,p_Dataset_Rec => l_dataset_rec
2917 ,x_Dataset_Rec => l_dataset_rec_db
2918 ,x_return_status => x_return_status
2919 ,x_msg_count => x_msg_count
2920 ,x_msg_data => x_msg_data
2921 );
2922 l_dataset_id := l_dataset_rec.Bsc_Dataset_Id;
2923 ELSE
2924 l_dataset_id := NULL;
2925 END IF;
2926
2927 -- Get the application details here
2928
2929 l_Application_rec_p.Application_Short_Name := p_Application_Short_Name;
2930
2931 BIS_APPLICATION_PVT.Value_Id_conversion
2932 ( p_api_version => 1.0
2933 , p_application_Rec => l_Application_rec_p
2934 , x_application_Rec => l_Application_rec
2935 , x_return_status => l_return_status
2936 , x_error_Tbl => l_error_tbl
2937 );
2938
2939
2940 -- BIS_MEASURE_PUB.Measure_Value_Id_Conversion and
2941 -- BIS_MEASURE_PUB.Dimension_Value_ID_Conversion are not called from Load_Measure
2942 -- as they will be called where BIS_MEASURE_PUB.Create_Measure is called from
2943 -- BSC_BIS_MEASURE_PUB.Create_Measure is called.
2944
2945 -- Give a call to BIS_MEASURE_PUB.Dimension_Value_ID_Conversion so that
2946 -- we have dimension ids populated.
2947 -- These Ids are used in giving a call to BIS_MEASURE_PVT.IS_OLD_DATA_MODEL
2948
2949 --
2950 BSC_BIS_MEASURE_PUB.Order_Dimensions_For_Ldt(
2951 p_Measure_Rec => l_measure_rec
2952 ,p_Org_Dimension_Short_Name => p_Org_Dimension_Short_Name
2953 ,p_Time_Dimension_Short_Name => p_Time_Dimension_Short_Name
2954 ,x_Measure_Rec => l_measure_rec_new
2955 ,x_return_status => x_return_status
2956 ,x_msg_count => x_msg_count
2957 ,x_msg_data => x_msg_data
2958 );
2959
2960 -- Call to BIS_MEASURE_PVT.Measure_Value_ID_Conversion is used to check
2961 -- if the measure should be created/updated during the upload of measure
2962
2963 IF (BIS_UTILITIES_PUB.Value_Missing
2964 (p_Measure_Rec.Measure_id) = FND_API.G_TRUE
2965 OR BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Measure_id)
2966 = FND_API.G_TRUE) THEN
2967 BIS_MEASURE_PVT.Measure_Value_ID_Conversion
2968 ( p_api_version => 1.0
2969 , p_Measure_Rec => l_Measure_Rec_new
2970 , x_Measure_Rec => l_Measure_Rec
2971 , x_return_status => x_return_status
2972 , x_error_Tbl => l_error_tbl
2973 );
2974 END IF;
2975
2976 --bug#4045278: perform upload test before any data is changed in the system
2977 IF ((x_return_status <> FND_API.G_RET_STS_SUCCESS) AND
2978 (l_dataset_id IS NULL)) THEN
2979 l_is_create := TRUE;
2980 ELSE
2981 l_is_create := FALSE;
2982 --bug#4045278: data versioning
2983 l_upload_test := BSC_BIS_MEASURE_PUB.Upload_Test(
2984 p_measure_short_name => p_Measure_Rec.Measure_Short_Name
2985 ,p_nls_mode => null
2986 ,p_file_lub => BIS_UTILITIES_PUB.Get_Owner_Id(p_owner)
2987 ,p_file_lud => p_Measure_Rec.Last_Update_Date
2988 ,p_custom_mode => p_custom_mode
2989 );
2990
2991 --if upload_test result is false, does not allow update of this record, throw exception
2992 IF (l_upload_test = FALSE) THEN
2993 FND_MESSAGE.SET_NAME('BIS','BIS_MEA_UPLOAD_TEST_FAILED');
2994 FND_MESSAGE.SET_TOKEN('SHORT_NAME', p_Measure_Rec.Measure_Short_Name);
2995 FND_MSG_PUB.ADD;
2996 RAISE FND_API.G_EXC_ERROR;
2997 END IF;
2998 END IF;
2999
3000 --bug#4045278: moved this portion to here so that we allow modifying measure_group tables if upload_test passed:
3001 -- No CRUD is available for this.
3002 -- That's why direct SELECT statement is used.
3003 -- This logic has been moved from lct to here
3004 IF p_measure_group_name IS NOT NULL THEN
3005 BEGIN
3006 SELECT min(measure_group_id) into l_measure_group_id
3007 FROM bsc_db_measure_groups_vl
3008 WHERE help = p_measure_group_name;
3009 EXCEPTION
3010 WHEN NO_DATA_FOUND THEN
3011 l_measure_group_id := null;
3012 END;
3013
3014 IF l_measure_group_id IS NULL THEN
3015 -- Create group
3016 bsc_db_measure_groups_pkg.insert_row(
3017 x_measure_group_id => l_measure_group_id
3018 ,x_help => p_measure_group_name);
3019 END IF;
3020 ELSE
3021 l_measure_group_id := -1;
3022 END IF;
3023
3024 -- Two conditions are used
3025 -- one for PMF and the other for BSC
3026 IF (l_is_create) THEN
3027 -- call create measure
3028
3029 BSC_BIS_MEASURE_PUB.Create_Measure(
3030 p_commit => FND_API.G_FALSE
3031 ,x_dataset_id => l_dataset_id
3032 ,p_dataset_source => nvl(l_dataset_rec.Bsc_Source, 'PMF')
3033 ,p_dataset_name => l_dataset_rec.Bsc_Dataset_Name
3034 ,p_dataset_help => l_dataset_rec.Bsc_Dataset_Help
3035 ,p_dataset_measure_id1 => NULL
3036 ,p_dataset_operation => l_dataset_rec.Bsc_Dataset_Operation
3037 ,p_dataset_measure_id2 => NULL
3038 ,p_dataset_format_id => l_dataset_rec.Bsc_Dataset_Format_Id
3039 ,p_dataset_color_method => l_dataset_rec.Bsc_Dataset_Color_Method
3040 ,p_dataset_autoscale_flag => l_dataset_rec.Bsc_Dataset_Autoscale_Flag
3041 ,p_dataset_projection_flag => l_dataset_rec.Bsc_Dataset_Projection_Flag
3042 ,p_measure_short_name => l_measure_rec.Measure_Short_Name
3043 ,p_measure_act_data_src_type => l_measure_rec.Actual_Data_Source_Type
3044 ,p_measure_act_data_src => l_measure_rec.Actual_Data_Source
3045 ,p_measure_comparison_source => l_measure_rec.Comparison_Source
3046 ,p_measure_operation => l_dataset_rec.Bsc_Measure_Operation
3047 ,p_measure_uom_class => l_measure_rec.Unit_Of_Measure_Class
3048 ,p_measure_increase_in_measure => l_measure_rec.Increase_In_Measure
3049 ,p_measure_random_style => l_dataset_rec.Bsc_Measure_Random_Style
3050 ,p_measure_min_act_value => l_dataset_rec.Bsc_Measure_Min_Act_Value
3051 ,p_measure_max_act_value => l_dataset_rec.Bsc_Measure_Max_Act_Value
3052 ,p_measure_min_bud_value => l_dataset_rec.Bsc_Measure_Min_Bud_Value
3053 ,p_measure_max_bud_value => l_dataset_rec.Bsc_Measure_Max_Bud_Value
3054 ,p_measure_app_id => l_Application_rec.Application_id
3055 ,p_measure_col => l_dataset_rec.Bsc_Measure_Col
3056 ,p_measure_group_id => l_dataset_rec.Bsc_Measure_Group_Id
3057 ,p_measure_projection_id => l_dataset_rec.Bsc_Measure_Projection_Id
3058 ,p_measure_type => l_dataset_rec.Bsc_Measure_Type
3059 ,p_measure_apply_rollup => p_measure_apply_rollup
3060 ,p_measure_function_name => l_measure_rec.Function_Name
3061 ,p_measure_enable_link => l_measure_rec.Enable_Link
3062 ,p_measure_obsolete => l_measure_rec.Obsolete
3063 ,p_type => l_measure_rec.Measure_Type
3064 ,p_measure_is_validate => l_measure_rec.is_validate -- ankgoel: bug#3557236
3065 ,p_dimension1_id => l_measure_rec.Dimension1_id
3066 ,p_dimension2_id => l_measure_rec.Dimension2_id
3067 ,p_dimension3_id => l_measure_rec.Dimension3_id
3068 ,p_dimension4_id => l_measure_rec.Dimension4_id
3069 ,p_dimension5_id => l_measure_rec.Dimension5_id
3070 ,p_dimension6_id => l_measure_rec.Dimension6_id
3071 ,p_dimension7_id => l_measure_rec.Dimension7_id
3072 ,p_y_axis_title => l_dataset_rec.Bsc_Y_Axis_Title
3073 ,p_owner => p_owner
3074 ,p_ui_flag => c_UI_FLAG
3075 ,p_last_update_date => p_measure_rec.Last_Update_Date
3076 ,p_func_area_short_name => l_measure_rec.Func_Area_Short_Name
3077 ,x_return_status => x_return_status
3078 ,x_msg_count => x_msg_count
3079 ,x_msg_data => x_msg_data
3080 );
3081 ELSE
3082 -- Get the time stamp using the dataset id so that it can be passed back
3083 -- to the update API
3084 -- Use the db record that is retrieved before for this.
3085
3086 l_time_stamp := BSC_BIS_LOCKS_PUB.GET_TIME_STAMP_DATASET(
3087 p_dataset_id => l_dataset_rec.bsc_dataset_id);
3088 -- Use BSC_DATASETS_PUB.Retrieve_Measures to get the measure details
3089 -- Previously l_dataset_rec has got all properties related to datasets
3090 -- Now measures properties are populated.
3091
3092 BSC_DATASETS_PUB.Retrieve_Measures(
3093 p_commit => p_commit
3094 ,p_Dataset_Rec => l_Dataset_Rec_db
3095 ,x_Dataset_Rec => l_Dataset_Rec_db1
3096 ,x_return_status => x_return_status
3097 ,x_msg_count => x_msg_count
3098 ,x_msg_data => x_msg_data
3099 );
3100
3101 -- Use NVL to fill up DB values in case values coming from
3102 -- ldt are NULL
3103
3104 -- Retrieve the DB record and apply changes to that
3105 -- These changes are to be applied to BIS record as well as BSC record.
3106
3107 -- Call the Update API
3108
3109 BSC_BIS_MEASURE_PUB.Update_Measure(
3110 p_commit => FND_API.G_FALSE
3111 ,p_dataset_id => l_dataset_id
3112 ,p_dataset_source => NVL(l_dataset_rec.Bsc_Source, 'PMF') -- :SOURCE
3113 ,p_dataset_name => NVL(l_dataset_rec.Bsc_Dataset_Name, l_dataset_rec_db.Bsc_Dataset_Name)
3114 ,p_dataset_help => NVL(l_dataset_rec.Bsc_Dataset_Help, l_dataset_rec_db.Bsc_Dataset_Help)
3115 ,p_dataset_measure_id1 => NVL(l_measure_id1, l_dataset_rec_db.Bsc_Measure_Id)
3116 ,p_dataset_operation => NVL(l_dataset_rec.Bsc_Dataset_Operation, l_dataset_rec_db.Bsc_Dataset_Operation)
3117 ,p_dataset_measure_id2 => NVL(l_measure_id2, l_dataset_rec_db.Bsc_Measure_Id2)
3118 ,p_dataset_format_id => NVL(l_dataset_rec.Bsc_Dataset_Format_Id, l_dataset_rec_db.Bsc_Dataset_Format_Id)
3119 ,p_dataset_color_method => NVL(l_dataset_rec.Bsc_Dataset_Color_Method, l_dataset_rec_db.Bsc_Dataset_Color_Method)
3120 ,p_dataset_autoscale_flag => NVL(l_dataset_rec.Bsc_Dataset_Autoscale_Flag, l_dataset_rec_db.Bsc_Dataset_Autoscale_Flag)
3121 ,p_dataset_projection_flag => NVL(l_dataset_rec.Bsc_Dataset_Projection_Flag, l_dataset_rec_db.Bsc_Dataset_Projection_Flag)
3122 ,p_measure_short_name => l_measure_rec.Measure_Short_Name
3123 ,p_measure_act_data_src_type => l_measure_rec.Actual_Data_Source_Type
3124 ,p_measure_act_data_src => l_measure_rec.Actual_Data_Source
3125 ,p_measure_comparison_source => l_measure_rec.Comparison_Source
3126 ,p_measure_operation => NVL(l_dataset_rec.Bsc_Measure_Operation, l_dataset_rec_db1.Bsc_Measure_Operation)
3127 ,p_measure_uom_class => l_measure_rec.Unit_Of_Measure_Class
3128 ,p_measure_increase_in_measure => l_measure_rec.Increase_In_Measure
3129 ,p_measure_random_style => NVL(l_dataset_rec.Bsc_Measure_Random_Style, l_dataset_rec_db1.Bsc_Measure_Random_Style)
3130 ,p_measure_min_act_value => NVL(l_dataset_rec.Bsc_Measure_Min_Act_Value, l_dataset_rec_db1.Bsc_Measure_Min_Act_Value)
3131 ,p_measure_max_act_value => NVL(l_dataset_rec.Bsc_Measure_Max_Act_Value, l_dataset_rec_db1.Bsc_Measure_Max_Act_Value)
3132 ,p_measure_min_bud_value => NVL(l_dataset_rec.Bsc_Measure_Min_Bud_Value, l_dataset_rec_db1.Bsc_Measure_Min_Bud_Value)
3133 ,p_measure_max_bud_value => NVL(l_dataset_rec.Bsc_Measure_Max_Bud_Value, l_dataset_rec_db1.Bsc_Measure_Max_Bud_Value)
3134 ,p_measure_app_id => l_Application_rec.Application_id
3135 ,p_measure_col => NVL(l_dataset_rec.Bsc_Measure_Col, l_dataset_rec_db1.Bsc_Measure_Col)
3136 ,p_measure_group_id => NVL(l_dataset_rec.Bsc_Measure_Group_Id, -1)
3137 ,p_measure_projection_id => NVL(l_dataset_rec.Bsc_Measure_Projection_Id, 3)
3138 ,p_measure_type => NVL(l_dataset_rec.Bsc_Measure_Type, l_dataset_rec_db1.Bsc_Measure_Type)
3139 ,p_measure_apply_rollup => p_measure_apply_rollup
3140 ,p_measure_function_name => l_measure_rec.Function_Name
3141 ,p_measure_enable_link => l_measure_rec.Enable_Link
3142 ,p_measure_obsolete => l_measure_rec.Obsolete
3143 ,p_type => l_measure_rec.Measure_Type
3144 ,p_measure_is_validate => l_measure_rec.is_validate -- ankgoel: bug#3557236
3145 ,p_time_stamp => l_time_stamp
3146 ,p_dimension1_id => l_measure_rec.Dimension1_id
3147 ,p_dimension2_id => l_measure_rec.Dimension2_id
3148 ,p_dimension3_id => l_measure_rec.Dimension3_id
3149 ,p_dimension4_id => l_measure_rec.Dimension4_id
3150 ,p_dimension5_id => l_measure_rec.Dimension5_id
3151 ,p_dimension6_id => l_measure_rec.Dimension6_id
3152 ,p_dimension7_id => l_measure_rec.Dimension7_id
3153 ,p_y_axis_title => NVL(l_dataset_rec.Bsc_Y_Axis_Title, l_dataset_rec_db.Bsc_Y_Axis_Title)
3154 ,p_owner => p_owner
3155 ,p_ui_flag => c_UI_FLAG
3156 ,p_last_update_date => p_measure_rec.Last_Update_Date
3157 ,p_func_area_short_name => l_measure_rec.Func_Area_Short_Name
3158 ,x_return_status => x_return_status
3159 ,x_msg_count => x_msg_count
3160 ,x_msg_data => x_msg_data
3161 );
3162 END IF;
3163
3164 EXCEPTION
3165 WHEN NO_DATA_FOUND THEN
3166 -- BIS_UTILITIES_PUB.put_line(p_text =>l_msg);
3167 x_return_status := FND_API.G_RET_STS_ERROR ;
3168 IF(x_msg_data IS NULL) THEN
3169 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
3170 ,p_count => x_msg_count
3171 ,p_data => x_msg_data);
3172 END IF;
3173 RAISE;
3174 WHEN FND_API.G_EXC_ERROR THEN
3175 -- BIS_UTILITIES_PUB.put_line(p_text =>l_msg);
3176 x_return_status := FND_API.G_RET_STS_ERROR ;
3177 IF(x_msg_data IS NULL) THEN
3178 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
3179 ,p_count => x_msg_count
3180 ,p_data => x_msg_data);
3181 END IF;
3182 RAISE;
3183 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3184 -- BIS_UTILITIES_PUB.put_line(p_text =>l_msg);
3185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3186 IF(x_msg_data IS NULL) THEN
3187 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
3188 ,p_count => x_msg_count
3189 ,p_data => x_msg_data);
3190 END IF;
3191 RAISE;
3192 WHEN others THEN
3193 -- BIS_UTILITIES_PUB.put_line(p_text =>l_msg);
3194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3195 IF(x_msg_data IS NULL) THEN
3196 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
3197 ,p_count => x_msg_count
3198 ,p_data => x_msg_data);
3199 END IF;
3200 RAISE;
3201 END Load_Measure;
3202 --=============================================================================
3203 PROCEDURE Translate_Measure
3204 (p_commit IN VARCHAR2 := FND_API.G_FALSE
3205 ,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
3206 , p_custom_mode IN VARCHAR2 := NULL
3207 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
3208 , p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
3209 , x_return_status OUT NOCOPY VARCHAR2
3210 , x_msg_count OUT NOCOPY NUMBER
3211 , x_msg_data OUT NOCOPY VARCHAR2
3212 )
3213 IS
3214
3215 l_dataset_rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
3216 l_measure_rec BIS_MEASURE_PUB.Measure_Rec_Type;
3217 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3218 l_Dataset_Id NUMBER;
3219 l_upload_test BOOLEAN := FALSE;
3220 BEGIN
3221
3222 x_return_status := FND_API.G_RET_STS_SUCCESS;
3223
3224 FND_MSG_PUB.Initialize;
3225
3226 --bug#4045278: data versioning
3227 l_upload_test := BSC_BIS_MEASURE_PUB.Upload_Test(
3228 p_measure_short_name => p_Measure_Rec.Measure_Short_Name
3229 ,p_nls_mode => 'NLS'
3230 ,p_file_lub => BIS_UTILITIES_PUB.Get_Owner_Id(p_owner)
3231 ,p_file_lud => p_Measure_Rec.Last_Update_Date
3232 ,p_custom_mode => p_custom_mode
3233 );
3234
3235 --if upload_test result is false, does not allow update of this record, throw exception
3236 IF (l_upload_test = FALSE) THEN
3237 FND_MESSAGE.SET_NAME('BIS','BIS_MEA_UPLOAD_TEST_FAILED');
3238 FND_MESSAGE.SET_TOKEN('SHORT_NAME', p_Measure_Rec.Measure_Short_Name);
3239 FND_MSG_PUB.ADD;
3240 RAISE FND_API.G_EXC_ERROR;
3241 END IF;
3242
3243 l_dataset_rec := p_Dataset_Rec;
3244 l_measure_rec := p_Measure_Rec;
3245
3246 BSC_BIS_MEASURE_PUB.Ret_Dataset_Fr_Meas_Shrt_Name(
3247 p_Measure_Short_Name => p_dataset_rec.Bsc_Measure_Short_Name
3248 ,x_Dataset_Id => l_Dataset_Id
3249 );
3250
3251 l_dataset_rec.Bsc_Dataset_Id := l_Dataset_Id;
3252
3253 --sawu: populate WHO column
3254 l_dataset_rec.Bsc_Dataset_Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
3255 l_dataset_rec.Bsc_Dataset_Last_Update_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
3256 l_dataset_rec.Bsc_Dataset_Last_Update_Login := fnd_global.LOGIN_ID;
3257
3258 l_dataset_rec.Bsc_Measure_Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
3259 l_dataset_rec.Bsc_Measure_Last_Update_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
3260 l_dataset_rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
3261
3262 l_measure_rec.Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
3263 l_measure_rec.Last_Updated_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
3264 l_measure_rec.Last_Update_Login := fnd_global.LOGIN_ID;
3265
3266 BSC_DATASETS_PUB.Translate_Measure(
3267 p_commit => FND_API.G_FALSE
3268 ,p_measure_rec => l_measure_rec
3269 ,p_Dataset_Rec => l_Dataset_Rec
3270 ,x_return_status => x_return_status
3271 ,x_msg_count => x_msg_count
3272 ,x_msg_data => x_msg_data
3273 );
3274
3275 BIS_MEASURE_PVT.Translate_measure
3276 ( p_api_version => 1.0 -- this is not of significance anymore
3277 , p_commit => p_commit
3278 , p_Measure_Rec => l_Measure_Rec
3279 , p_owner => p_owner
3280 , x_return_status => x_return_status
3281 , x_error_Tbl => l_error_Tbl
3282 );
3283
3284 EXCEPTION
3285 WHEN FND_API.G_EXC_ERROR THEN
3286 x_return_status := FND_API.G_RET_STS_ERROR;
3287 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
3288 ,p_count => x_msg_count
3289 ,p_data => x_msg_data);
3290 RAISE;
3291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3293 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
3294 ,p_count => x_msg_count
3295 ,p_data => x_msg_data);
3296 RAISE;
3297 WHEN NO_DATA_FOUND THEN
3298 x_return_status := FND_API.G_RET_STS_ERROR;
3299 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
3300 ,p_count => x_msg_count
3301 ,p_data => x_msg_data);
3302 RAISE;
3303 WHEN OTHERS THEN
3304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3305 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
3306 ,p_count => x_msg_count
3307 ,p_data => x_msg_data);
3308 RAISE;
3309
3310 END Translate_Measure;
3311 --=============================================================================
3312 --=============================================================================
3313 PROCEDURE Order_Dimensions_For_Ldt(
3314 p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
3315 ,p_Org_Dimension_Short_Name IN VARCHAR2
3316 ,p_Time_Dimension_Short_Name IN VARCHAR2
3317 ,x_Measure_Rec OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
3318 ,x_return_status OUT NOCOPY VARCHAR2
3319 ,x_msg_count OUT NOCOPY NUMBER
3320 ,x_msg_data OUT NOCOPY VARCHAR2
3321 ) IS
3322 l_measure_rec BIS_MEASURE_PUB.Measure_Rec_Type;
3323 l_measure_rec_new BIS_MEASURE_PUB.Measure_Rec_Type;
3324 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3325 l_org_dimension_id NUMBER;
3326 l_time_dimension_id NUMBER;
3327 l_flag VARCHAR2(10);
3328 BEGIN
3329
3330 BIS_Measure_PVT.Dimension_Value_ID_Conversion
3331 ( p_api_version => 1.0
3332 , p_Measure_Rec => p_measure_rec
3333 , x_Measure_Rec => l_Measure_Rec
3334 , x_return_status => x_return_status
3335 , x_error_Tbl => l_error_Tbl
3336 );
3337
3338 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3339 RAISE FND_API.G_EXC_ERROR;
3340 END IF;
3341
3342 IF (BIS_UTILITIES_PUB.Value_Not_Missing
3343 (p_Org_Dimension_Short_Name) = FND_API.G_TRUE
3344 AND BIS_UTILITIES_PUB.Value_Not_NULL(p_Org_Dimension_Short_Name)
3345 = FND_API.G_TRUE) THEN
3346 BIS_DIMENSION_PVT.Value_ID_Conversion
3347 ( p_api_version => 1.0
3348 , p_Dimension_Short_Name => p_Org_Dimension_Short_Name
3349 , p_Dimension_Name => FND_API.G_MISS_CHAR
3350 , x_Dimension_ID => l_Org_Dimension_ID
3351 , x_return_status => x_return_status
3352 , x_error_Tbl => l_error_Tbl
3353 );
3354
3355 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3356 RAISE FND_API.G_EXC_ERROR;
3357 END IF;
3358
3359 END IF;
3360
3361 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_Time_Dimension_Short_Name) = FND_API.G_TRUE
3362 AND BIS_UTILITIES_PUB.Value_Not_NULL(p_Time_Dimension_Short_Name)
3363 = FND_API.G_TRUE) THEN
3364 BIS_DIMENSION_PVT.Value_ID_Conversion
3365 ( p_api_version => 1.0
3366 , p_Dimension_Short_Name => p_Time_Dimension_Short_Name
3367 , p_Dimension_Name => FND_API.G_MISS_CHAR
3368 , x_Dimension_ID => l_Time_Dimension_ID
3369 , x_return_status => x_return_status
3370 , x_error_Tbl => l_error_Tbl
3371 );
3372
3373 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3374 RAISE FND_API.G_EXC_ERROR;
3375 END IF;
3376
3377 END IF;
3378
3379 l_measure_rec_new := l_measure_rec; -- added later
3380
3381 IF (BIS_MEASURE_PVT.IS_OLD_DATA_MODEL(
3382 l_Measure_Rec
3383 ,l_Org_Dimension_ID
3384 ,l_Time_Dimension_ID)) THEN
3385
3386 l_flag := FND_API.G_FALSE;
3387 IF((l_flag = FND_API.G_FALSE) AND (BIS_UTILITIES_PVT.Value_Missing_Or_Null(l_measure_rec.Dimension1_ID) = FND_API.G_TRUE)) THEN
3388 l_measure_rec_new.Dimension1_ID := l_Org_Dimension_ID;
3389 l_measure_rec_new.Dimension1_Short_Name := p_Org_Dimension_Short_Name;
3390 l_measure_rec_new.Dimension1_Name := NULL; -- Retrieve Org dimension name later
3391
3392 l_measure_rec_new.Dimension2_ID := l_Time_Dimension_ID;
3393 l_measure_rec_new.Dimension2_Short_Name := p_Time_Dimension_Short_Name;
3394 l_measure_rec_new.Dimension2_Name := NULL; -- Retrieve Time dimension name later
3395 l_flag := FND_API.G_TRUE;
3396 END IF;
3397 IF((l_flag = FND_API.G_FALSE) AND (BIS_UTILITIES_PVT.Value_Missing_Or_Null(l_measure_rec.Dimension2_ID) = FND_API.G_TRUE)) THEN
3398 l_measure_rec_new.Dimension2_ID := l_Org_Dimension_ID;
3399 l_measure_rec_new.Dimension2_Short_Name := p_Org_Dimension_Short_Name;
3400 l_measure_rec_new.Dimension2_Name := NULL; -- Retrieve Org dimension name later
3401
3402 l_measure_rec_new.Dimension3_ID := l_Time_Dimension_ID;
3403 l_measure_rec_new.Dimension3_Short_Name := p_Time_Dimension_Short_Name;
3404 l_measure_rec_new.Dimension3_Name := NULL; -- Retrieve Time dimension name later
3405 l_flag := FND_API.G_TRUE;
3406 END IF;
3407 IF((l_flag = FND_API.G_FALSE) AND (BIS_UTILITIES_PVT.Value_Missing_Or_Null(l_measure_rec.Dimension3_ID) = FND_API.G_TRUE)) THEN
3408 l_measure_rec_new.Dimension3_ID := l_Org_Dimension_ID;
3409 l_measure_rec_new.Dimension3_Short_Name := p_Org_Dimension_Short_Name;
3410 l_measure_rec_new.Dimension3_Name := NULL; -- Retrieve Org dimension name later
3411
3412 l_measure_rec_new.Dimension4_ID := l_Time_Dimension_ID;
3413 l_measure_rec_new.Dimension4_Short_Name := p_Time_Dimension_Short_Name;
3414 l_measure_rec_new.Dimension4_Name := NULL; -- Retrieve Time dimension name later
3415 l_flag := FND_API.G_TRUE;
3416 END IF;
3417 IF((l_flag = FND_API.G_FALSE) AND (BIS_UTILITIES_PVT.Value_Missing_Or_Null(l_measure_rec.Dimension4_ID) = FND_API.G_TRUE)) THEN
3418 l_measure_rec_new.Dimension4_ID := l_Org_Dimension_ID;
3419 l_measure_rec_new.Dimension4_Short_Name := p_Org_Dimension_Short_Name;
3420 l_measure_rec_new.Dimension4_Name := NULL; -- Retrieve Org dimension name later
3421
3422 l_measure_rec_new.Dimension5_ID := l_Time_Dimension_ID;
3423 l_measure_rec_new.Dimension5_Short_Name := p_Time_Dimension_Short_Name;
3424 l_measure_rec_new.Dimension5_Name := NULL; -- Retrieve Time dimension name later
3425 l_flag := FND_API.G_TRUE;
3426 END IF;
3427 IF((l_flag = FND_API.G_FALSE) AND (BIS_UTILITIES_PVT.Value_Missing_Or_Null(l_measure_rec.Dimension5_ID) = FND_API.G_TRUE)) THEN
3428 l_measure_rec_new.Dimension5_ID := l_Org_Dimension_ID;
3429 l_measure_rec_new.Dimension5_Short_Name := p_Org_Dimension_Short_Name;
3430 l_measure_rec_new.Dimension5_Name := NULL; -- Retrieve Org dimension name later
3431
3432 l_measure_rec_new.Dimension6_ID := l_Time_Dimension_ID;
3433 l_measure_rec_new.Dimension6_Short_Name := p_Time_Dimension_Short_Name;
3434 l_measure_rec_new.Dimension6_Name := NULL; -- Retrieve Time dimension name later
3435 l_flag := FND_API.G_TRUE;
3436 END IF;
3437 IF((l_flag = FND_API.G_FALSE) AND (BIS_UTILITIES_PVT.Value_Missing_Or_Null(l_measure_rec.Dimension6_ID) = FND_API.G_TRUE)) THEN
3438 l_measure_rec_new.Dimension6_ID := l_Org_Dimension_ID;
3439 l_measure_rec_new.Dimension6_Short_Name := p_Org_Dimension_Short_Name;
3440 l_measure_rec_new.Dimension6_Name := NULL; -- Retrieve Org dimension name later
3441
3442 l_measure_rec_new.Dimension7_ID := l_Time_Dimension_ID;
3443 l_measure_rec_new.Dimension7_Short_Name := p_Time_Dimension_Short_Name;
3444 l_measure_rec_new.Dimension7_Name := NULL; -- Retrieve Time dimension name later
3445 l_flag := FND_API.G_TRUE;
3446 END IF;
3447
3448 END IF;
3449 x_measure_rec := l_measure_rec_new;
3450
3451 EXCEPTION
3452 WHEN FND_API.G_EXC_ERROR THEN
3453 x_return_status := FND_API.G_RET_STS_ERROR;
3454 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3455 ,p_data => x_msg_data);
3456 RAISE;
3457 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3459 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3460 ,p_data => x_msg_data);
3461 RAISE;
3462 WHEN OTHERS THEN
3463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3464 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3465 ,p_data => x_msg_data);
3466 RAISE;
3467 END Order_Dimensions_For_Ldt;
3468 --=============================================================================
3469 --
3470 -- Get the dataset_id from measure short name so that the same
3471 -- can be used while updating the measure name and description
3472 -- in the BSC system while uploading of the ldt.
3473 --=============================================================================
3474 PROCEDURE Ret_Dataset_Fr_Meas_Shrt_Name(
3475 p_Measure_Short_Name IN VARCHAR2
3476 ,x_Dataset_Id OUT NOCOPY NUMBER
3477 ) IS
3478
3479 CURSOR c_dataset_id (cp_measure_short_name VARCHAR2)IS
3480 SELECT dataset_id
3481 FROM bis_indicators
3482 WHERE short_name = cp_measure_short_name;
3483
3484 BEGIN
3485
3486 IF (c_dataset_id%ISOPEN) THEN
3487 CLOSE c_dataset_id;
3488 END IF;
3489
3490 OPEN c_dataset_id(cp_measure_short_name => p_Measure_Short_Name);
3491 FETCH c_dataset_id INTO x_Dataset_Id;
3492 CLOSE c_dataset_id;
3493
3494 EXCEPTION
3495 WHEN OTHERS THEN
3496 IF (c_dataset_id%ISOPEN) THEN
3497 CLOSE c_dataset_id;
3498 END IF;
3499 END Ret_Dataset_Fr_Meas_Shrt_Name;
3500
3501 --=============================================================================
3502
3503 -- mdamle 09/03/03 - Get measure col
3504 function get_measure_col(
3505 p_dataset_name IN VARCHAR2
3506 ,p_source IN VARCHAR2
3507 ,p_measure_id IN NUMBER
3508 ,p_short_name IN VARCHAR2
3509 ) return VARCHAR2 is
3510 l_measure_col bsc_sys_datasets_tl.name%TYPE;
3511 l_flag BOOLEAN := FALSE;
3512 l_alias VARCHAR2(30);
3513 l_temp_var VARCHAR2(30);
3514 l_count NUMBER;
3515 l_is_mes_exist BOOLEAN := FALSE;
3516 l_count_col NUMBER;
3517 l_mes_short_name BSC_SYS_MEASURES.SHORT_NAME%TYPE;
3518 BEGIN
3519
3520 l_measure_col := gen_name_for_column(p_dataset_name);
3521
3522 IF(l_measure_col is null) THEN
3523 IF(p_short_name is null ) THEN
3524 l_mes_short_name := bsc_utility.Get_Default_Internal_Name(bsc_utility.c_BSC_MEASURE);
3525 ELSE
3526 l_mes_short_name := p_short_name;
3527 END IF;
3528 l_measure_col := gen_name_for_column(l_mes_short_name);
3529
3530 END IF;
3531
3532 l_temp_var := substr(l_measure_col, 1, 30);
3533
3534 l_flag := TRUE;
3535 l_alias := NULL;
3536
3537 WHILE (l_flag) LOOP
3538 l_is_mes_exist := FALSE;
3539 IF (p_measure_id IS NULL) THEN
3540 SELECT COUNT(1) INTO l_count
3541 FROM BSC_SYS_MEASURES
3542 WHERE UPPER(measure_col) = UPPER(l_temp_var);
3543
3544 SELECT COUNT(1) INTO l_count_col
3545 FROM BSC_DB_MEASURE_COLS_TL
3546 WHERE UPPER(measure_col) = UPPER(l_temp_var);
3547
3548 ELSE
3549 SELECT COUNT(1) INTO l_count
3550 FROM bsc_sys_measures
3551 WHERE UPPER(measure_col) = UPPER(l_temp_var)
3552 AND measure_id <> p_measure_id;
3553
3554 SELECT COUNT(1) INTO l_count_col
3555 FROM BSC_DB_MEASURE_COLS_TL
3556 WHERE UPPER(measure_col) = UPPER(l_temp_var);
3557 END IF;
3558
3559 IF(l_count_col > 0 OR l_count > 0) THEN
3560 l_is_mes_exist := TRUE;
3561 END IF;
3562
3563 IF (NOT l_is_mes_exist) THEN
3564 l_flag := FALSE;
3565 l_measure_col := l_temp_var;
3566 END IF;
3567 BEGIN
3568 l_alias := BSC_BIS_MEASURE_PUB.get_Next_Alias(l_alias);
3569 EXCEPTION
3570 WHEN OTHERS THEN
3571 l_measure_col := substr(l_measure_col, 1, 30);
3572 END;
3573 l_temp_var := SUBSTR(l_temp_var, 1, 27)||l_alias;
3574 END LOOP;
3575
3576 return l_measure_col;
3577 EXCEPTION
3578 when others then return null;
3579
3580 END get_measure_col;
3581
3582 -- mdamle 09/03/03 - Is Formula
3583 function isFormula
3584 (p_measure_col IN VARCHAR2) return boolean is
3585 BEGIN
3586
3587 if (instr(p_measure_col, '/') > 0) or
3588 (instr(p_measure_col, '(') > 0) or
3589 (instr(p_measure_col, ')') > 0) or
3590 (instr(p_measure_col, '+') > 0) or
3591 (instr(p_measure_col, '-') > 0) or
3592 (instr(p_measure_col, '*') > 0) or
3593 (instr(p_measure_col, ',') > 0) or
3594 (instr(p_measure_col, ' ') > 0) then
3595 return true;
3596 else
3597 return false;
3598 end if;
3599
3600 END isFormula;
3601
3602 /*
3603 ***************************************************
3604 procedure Get_Incr_Trigger()
3605
3606
3607 checks if the measure properties have been changed
3608 which will result in Strucutral changes to the KPIs
3609
3610 ***************************************************
3611 */
3612
3613 procedure Get_Incr_Trigger(
3614 p_commit in varchar2 := fnd_api.g_false
3615 ,p_dataset_id in number
3616 ,p_measure_projection_id in number := -1
3617 ,p_measure_type in number := -1
3618 ,p_is_ytd_enabled in varchar2 := null
3619 ,p_is_qtd_enabled in varchar2 := null
3620 ,p_is_xtd_enabled in varchar2 := null
3621 ,p_rollup_calc in varchar2 := null
3622 ,p_formula in varchar2 := null
3623 ,p_Measure_Group_Id IN VARCHAR2
3624 ,p_Check_Autogen_Only IN VARCHAR2 := null
3625 ,x_return_status out nocopy varchar2
3626 ,x_msg_count out nocopy number
3627 ,x_msg_data out nocopy varchar2
3628 ) is
3629 l_proj_id number;
3630 l_measure_type number;
3631 l_count number;
3632 l_is_ytd_enabled varchar2(3);
3633 l_is_qtd_enabled varchar2(3);
3634 l_is_xtd_enabled varchar2(3);
3635 l_rollup_calc varchar2(5);
3636 l_formula varchar2(320);
3637 l_kpis varchar2(4000);
3638 l_measure_group_id NUMBER;
3639
3640 CURSOR c_Meas_Grp IS
3641 SELECT measure_group_id
3642 FROM bsc_sys_datasets_vl d
3643 , bsc_sys_measures m
3644 , bsc_db_measure_cols_vl db
3645 WHERE d.dataset_id = p_dataset_id
3646 AND m.measure_id = d.measure_id1
3647 AND m.measure_col =db.measure_col;
3648
3649 begin
3650
3651 fnd_msg_pub.Initialize;
3652
3653 if bsc_utility.isBscInProductionMode then
3654 select count(c.projection_id)
3655 into l_count
3656 from bsc_sys_datasets_vl d, bsc_sys_measures m, bsc_db_measure_cols_vl c
3657 where m.measure_id = d.measure_id1
3658 and c.measure_col = m.measure_col
3659 and d.dataset_id = p_dataset_id;
3660
3661 -- incase the measure has a formula defined, we cannot have projection_id defined.
3662 -- hence the check is required.
3663
3664 if(l_count <> 0) then
3665 select c.projection_id, c.measure_type
3666 into l_proj_id, l_measure_type
3667 from bsc_sys_datasets_vl d, bsc_sys_measures m, bsc_db_measure_cols_vl c
3668 where m.measure_id = d.measure_id1
3669 and c.measure_col = m.measure_col
3670 and d.dataset_id = p_dataset_id;
3671 end if;
3672
3673 if(p_is_ytd_enabled is not null) then
3674 select decode(count(disabled_calc_id), 1, 'N', 0, 'Y', 'N') isDisabled
3675 into l_is_ytd_enabled
3676 from bsc_sys_dataset_calc
3677 where dataset_id = p_dataset_id
3678 and disabled_calc_id = c_YTD_CODE;
3679 end if;
3680
3681 if(p_is_qtd_enabled is not null) then
3682 select decode(count(disabled_calc_id), 1, 'N', 0, 'Y', 'N') isDisabled
3683 into l_is_qtd_enabled
3684 from bsc_sys_dataset_calc
3685 where dataset_id = p_dataset_id
3686 and disabled_calc_id = c_QTD_CODE;
3687 end if;
3688
3689 -- needed for XTD Enhancement
3690 if(p_is_xtd_enabled is not null) then
3691 select decode(count(disabled_calc_id), 1, 'N', 0, 'Y', 'N') isDisabled
3692 into l_is_xtd_enabled
3693 from bsc_sys_dataset_calc
3694 where dataset_id = p_dataset_id
3695 and disabled_calc_id = c_XTD_CODE;
3696 end if;
3697
3698 if(p_rollup_calc is not null) then
3699 select decode(nvl(BSC_APPS.Get_Property_Value(m.S_COLOR_FORMULA, 'pAvgL'), 'N'), 'Y', 'AVL', m.operation) rollup
3700 into l_rollup_calc
3701 from bsc_sys_datasets_vl d, bsc_sys_measures m
3702 where m.measure_id = d.measure_id1
3703 and d.dataset_id = p_dataset_id ;
3704 end if;
3705
3706 if(p_formula is not null) then
3707 select m.measure_col formula
3708 into l_formula
3709 from bsc_sys_datasets_vl d, bsc_sys_measures m
3710 where m.measure_id = d.measure_id1
3711 and d.dataset_id = p_dataset_id ;
3712 end if;
3713
3714 IF(p_Measure_Group_Id IS NOT NULL) THEN
3715 FOR cd IN c_Meas_Grp LOOP
3716 l_measure_group_id := cd.measure_group_id;
3717 END LOOP;
3718 END IF;
3719
3720 x_return_status := FND_API.G_RET_STS_SUCCESS;
3721
3722 IF (p_Check_Autogen_Only = 'Y') THEN
3723 l_kpis := getMeasureAutoGenKpis(p_dataset_id);
3724 ELSE
3725 l_kpis := getMeasureKpis(p_dataset_id);
3726 END IF;
3727
3728 if (l_kpis is not null) then
3729 -- Provide a structural changes warning first
3730 if((upper(p_is_xtd_enabled) <> l_is_xtd_enabled) and (p_is_xtd_enabled is not null)) then -- l_is_xtd_enabled is always caps.
3731 fnd_message.set_name('BSC','BSC_PMD_KPI_STRUCT_INVALID');
3732 fnd_message.set_token('INDICATORS', l_kpis);
3733 fnd_msg_pub.ADD;
3734 -- raise fnd_api.g_exc_error;
3735 end if;
3736
3737 if((upper(p_formula) <> upper(l_formula)) and (p_formula is not null)) then
3738 IF (BSC_DATASETS_PVT.Is_Structure_change(upper(p_formula), upper(l_formula))) THEN
3739 fnd_message.set_name('BSC','BSC_PMD_KPI_STRUCT_INVALID');
3740 fnd_message.set_token('INDICATORS', l_kpis);
3741 fnd_msg_pub.ADD;
3742 raise fnd_api.g_exc_error;
3743 ELSE
3744 fnd_message.set_name('BIS','BIS_PMD_KPI_NONSTRUCT_INVALID');
3745 fnd_message.set_token('OBJECTIVES', l_kpis);
3746 fnd_msg_pub.ADD;
3747 raise fnd_api.g_exc_error;
3748 END IF;
3749 end if;
3750
3751 IF((p_Measure_Group_Id IS NOT NULL ) AND (l_measure_group_id IS NOT NULL)) THEN
3752 IF(p_Measure_Group_Id <> l_measure_group_id) THEN
3753 FND_MESSAGE.SET_NAME('BSC','BSC_PMD_KPI_STRUCT_INVALID');
3754 FND_MESSAGE.SET_TOKEN('INDICATORS', l_kpis);
3755 FND_MSG_PUB.ADD;
3756 RAISE FND_API.G_EXC_ERROR;
3757 END IF;
3758 END IF;
3759
3760 if ((l_measure_type <> p_measure_type) and (p_measure_type <> -1)) then
3761 fnd_message.set_name('BSC','BSC_PMD_KPI_COLOR_INVALID');
3762 fnd_message.set_token('INDICATORS', l_kpis);
3763 fnd_msg_pub.ADD;
3764 raise fnd_api.g_exc_error;
3765 end if;
3766
3767 -- color changes is a sub-set change for structural changes, hence comes next
3768
3769 -- Fixed for bug#3798834
3770 if((upper(p_rollup_calc) <> upper(l_rollup_calc)) and (p_rollup_calc is not null)) then
3771 fnd_message.set_name('BSC','BSC_PMD_KPI_COLOR_INVALID');
3772 fnd_message.set_token('INDICATORS', l_kpis);
3773 fnd_msg_pub.ADD;
3774 raise fnd_api.g_exc_error;
3775 end if;
3776
3777
3778 if ((l_proj_id <> p_measure_projection_id) and (p_measure_projection_id <> -1)) then
3779 fnd_message.set_name('BSC','BSC_PMD_KPI_COLOR_INVALID');
3780 fnd_message.set_token('INDICATORS', l_kpis);
3781 fnd_msg_pub.ADD;
3782 raise fnd_api.g_exc_error;
3783 end if;
3784
3785 -- l_is_ytd_enabled is always caps.
3786 if((upper(p_is_ytd_enabled) <> l_is_ytd_enabled) and (p_is_ytd_enabled is not null)) then
3787 fnd_message.set_name('BSC','BSC_PMD_KPI_COLOR_INVALID');
3788 fnd_message.set_token('INDICATORS', l_kpis);
3789 fnd_msg_pub.ADD;
3790 raise fnd_api.g_exc_error;
3791 end if;
3792
3793 -- l_is_qtd_enabled is always caps.
3794 if((upper(p_is_qtd_enabled) <> l_is_qtd_enabled) and (p_is_qtd_enabled is not null)) then
3795 fnd_message.set_name('BSC','BSC_PMD_KPI_COLOR_INVALID');
3796 fnd_message.set_token('INDICATORS', l_kpis);
3797 fnd_msg_pub.ADD;
3798 raise fnd_api.g_exc_error;
3799 end if;
3800 END IF;-- END L_KPIS IS NOT NULL
3801 END IF;
3802 EXCEPTION
3803 WHEN FND_API.G_EXC_ERROR THEN
3804
3805 if (x_msg_data is null) then
3806 fnd_msg_pub.count_and_get
3807 ( p_encoded => fnd_api.g_false
3808 , p_count => x_msg_count
3809 , p_data => x_msg_data
3810 );
3811 end if;
3812
3813 when others then
3814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3815 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3816 ,p_data => x_msg_data);
3817
3818 -- fixed for Bug#3296451
3819 x_msg_data := null;
3820
3821 end Get_Incr_Trigger;
3822
3823
3824
3825 /*
3826 ***************************************************
3827 function getReturnMessage()
3828 ***************************************************
3829 */
3830
3831 function getReturnMessage (
3832 p_dataset_id in number
3833 , p_message in varchar2
3834 )
3835 return varchar2 is
3836 l_return varchar2(4000);
3837 l_message varchar2(2000);
3838 l_temp varchar2(4000);
3839
3840 l_length number;
3841 begin
3842
3843
3844 l_temp := bsc_bis_measure_pub.getMeasureKpis(p_dataset_id => p_dataset_id);
3845 l_message := bsc_apps.get_message(p_message);
3846 l_length := nvl(0, length(l_message));
3847
3848
3849 if((nvl(0, length(l_temp)) + l_length) > c_MAX_MSG_LENGTH) then
3850 l_temp := substr(l_temp, 1, (c_MAX_MSG_LENGTH - l_length - 3)); -- accomodate the colon
3851 end if;
3852
3853 l_return := null;
3854
3855 if (l_temp is not null) then
3856 l_return := l_message || l_temp;
3857 end if;
3858
3859 return l_return;
3860 exception
3861 when others then
3862 return null;
3863 end getReturnMessage;
3864
3865
3866 /*
3867 ***************************************************
3868 function getMeasureKpis()
3869 ***************************************************
3870 */
3871
3872 function getMeasureKpis (
3873 p_dataset_id in number
3874 ) return varchar2 is
3875
3876 l_return varchar2(32000);
3877 l_isFirst boolean := true;
3878
3879 cursor indicators_cursor is
3880 select k.name || ' [' || k.indicator || '] ' name
3881 from bsc_kpis_vl k
3882 where indicator in
3883 (
3884 select distinct indicator
3885 from bsc_kpi_analysis_measures_b d
3886 where dataset_id = p_dataset_id
3887 )
3888 and k.share_flag <> 2;
3889 begin
3890
3891 l_return := null;
3892
3893 for cd in indicators_cursor loop
3894 if(l_isFirst = true) then
3895 l_return := l_return || cd.name;
3896 l_isFirst := false;
3897 else
3898 l_return := l_return ||', '||cd.name;
3899 end if;
3900 end loop;
3901
3902 return l_return;
3903
3904 exception
3905 when others then return null;
3906
3907 END getMeasureKpis;
3908
3909
3910 /*
3911 ***************************************************
3912 function getMeasureAutoGenKpis()
3913 ***************************************************
3914 */
3915
3916 FUNCTION getMeasureAutoGenKpis (
3917 p_dataset_id IN NUMBER
3918 ) RETURN VARCHAR2 IS
3919
3920 l_return VARCHAR2(32000);
3921 l_isFirst BOOLEAN := TRUE;
3922
3923 CURSOR indicators_cursor IS
3924 SELECT k.name || ' [' || k.indicator || '] ' name
3925 FROM bsc_kpis_vl k
3926 WHERE indicator IN
3927 (
3928 SELECT DISTINCT d.indicator
3929 FROM bsc_kpi_analysis_measures_b d,
3930 bsc_kpis_b kpi
3931 WHERE d.dataset_id = p_dataset_id
3932 AND d.indicator = kpi.indicator
3933 AND kpi.short_name IS NOT NULL
3934 )
3935 AND k.share_flag <> 2;
3936 BEGIN
3937
3938 l_return := NULL;
3939
3940 FOR cd in indicators_cursor LOOP
3941 IF(l_isFirst = TRUE) THEN
3942 l_return := l_return || cd.name;
3943 l_isFirst := FALSE;
3944 ELSE
3945 l_return := l_return ||', '||cd.name;
3946 END IF;
3947 END LOOP;
3948
3949 RETURN l_return;
3950
3951 EXCEPTION
3952 WHEN others THEN RETURN NULL;
3953
3954 END getMeasureAutoGenKpis;
3955
3956 /*
3957 ***************************************************
3958 procedure get_Color_Change_Trigger()
3959 ***************************************************
3960 */
3961
3962 procedure get_Color_Change_Trigger(
3963 p_kpi_id in varchar2
3964 ,p_dataset_data in varchar2
3965 ,p_m1_accept in varchar2
3966 ,p_m1_marg in varchar2
3967 ,p_m2_accept in varchar2
3968 ,p_m2_marg in varchar2
3969 ,p_m3_upr_accept in varchar2
3970 ,p_m3_upr_marg in varchar2
3971 ,p_m3_lwr_accept in varchar2
3972 ,p_m3_lwr_marg in varchar2
3973 ,x_return_status out nocopy varchar2
3974 ,x_msg_count out nocopy number
3975 ,x_msg_data out nocopy varchar2
3976 ) is
3977
3978 l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
3979
3980 type dataset_method_data is RECORD(
3981 dataset_id number
3982 ,color_method number
3983 ,dataset_data varchar2(50)
3984 );
3985
3986 type dataset_method_data_tbl is table OF dataset_method_data
3987 index by BINARY_INTEGER;
3988
3989 l_dataset_list varchar2(5000);
3990
3991 cursor c_Indicators_Cursor is
3992 SELECT DISTINCT B.NAME||'['||B.INDICATOR||']' NAME
3993 FROM BSC_KPI_ANALYSIS_MEASURES_B A,
3994 BSC_KPIS_VL B,
3995 BSC_DB_COLOR_KPI_DEFAULTS_V D
3996 WHERE INSTR(L_DATASET_LIST, ','||A.DATASET_ID||',') > 0
3997 AND D.DATASET_ID = A.DATASET_ID
3998 AND A.INDICATOR =B.INDICATOR
3999 AND B.INDICATOR =D.INDICATOR
4000 AND B.PROTOTYPE_FLAG <> 2
4001 AND B.SHARE_FLAG <> 2;
4002
4003 cursor c_Dataset_Color is
4004 select B.DATASET_ID DATASET_ID, C.COLOR_METHOD COLOR_METHOD
4005 from BSC_OAF_ANALYSYS_OPT_COMB_V A,
4006 BSC_KPI_ANALYSIS_MEASURES_VL B,
4007 BSC_SYS_DATASETS_VL C
4008 where A.INDICATOR = B.INDICATOR
4009 and A.SERIES_ID = B.SERIES_ID
4010 and A.ANALYSIS_OPTION0 = B.ANALYSIS_OPTION0
4011 and A.ANALYSIS_OPTION1 = B.ANALYSIS_OPTION1
4012 and A.ANALYSIS_OPTION2 = B.ANALYSIS_OPTION2
4013 and B.DATASET_ID = C.DATASET_ID
4014 and A.INDICATOR = p_kpi_id
4015 order by B.DATASET_ID;
4016
4017 l_sql varchar2(2000);
4018 l_dataset_data varchar2(5000);
4019 l_dataset_dummy varchar2(100);
4020 l_indicators varchar2(32000);
4021 l_kpi_name varchar2(80);
4022 l_commit varchar2(10);
4023 l_isFirst boolean := true;
4024
4025 l_dataset_id number;
4026 l_color_method number;
4027 l_pos number;
4028 l_dt_data_cnt number;
4029 l_dt_data_length number;
4030
4031 l_m1_accept number;
4032 l_m1_marg number;
4033 l_m2_accept number;
4034 l_m2_marg number;
4035 l_m3_upr_accept number;
4036 l_m3_upr_marg number;
4037 l_m3_lwr_accept number;
4038 l_m3_lwr_marg number;
4039
4040 l2_m1_accept number;
4041 l2_m1_marg number;
4042 l2_m2_accept number;
4043 l2_m2_marg number;
4044 l2_m3_upr_accept number;
4045 l2_m3_upr_marg number;
4046 l2_m3_lwr_accept number;
4047 l2_m3_lwr_marg number;
4048
4049 dt_data dataset_method_data_tbl;
4050
4051 begin
4052
4053
4054 -- set the proper values for the color tolerance levels.
4055 fnd_msg_pub.Initialize;
4056
4057
4058 if bsc_utility.isBscInProductionMode then
4059 l_m1_accept := remove_percent(p_m1_accept);
4060 l_m1_marg := remove_percent(p_m1_marg);
4061 l_m2_accept := remove_percent(p_m2_accept);
4062 l_m2_marg := remove_percent(p_m2_marg);
4063 l_m3_upr_accept := remove_percent(p_m3_upr_accept);
4064 l_m3_upr_marg := remove_percent(p_m3_upr_marg);
4065 l_m3_lwr_accept := remove_percent(p_m3_lwr_accept);
4066 l_m3_lwr_marg := remove_percent(p_m3_lwr_marg);
4067
4068 l_dataset_data := p_dataset_data;
4069 l_dt_data_cnt := 0;
4070
4071 --dbms_output.put_line(' l_dataset_data ' || l_dataset_data);
4072
4073 while length(l_dataset_data) > 0 loop
4074 l_pos := instr(l_dataset_data, ';');
4075
4076 if l_pos > 0 then
4077 l_dataset_dummy := ltrim(rtrim(substr(l_dataset_data, 1, l_pos - 1)));
4078 l_dataset_data := substr(l_dataset_data, l_pos + 1, length(l_dataset_data));
4079 else
4080 l_dataset_dummy := ltrim(rtrim(l_dataset_data));
4081 l_dataset_data := '';
4082 end if;
4083
4084 if length(l_dataset_dummy) > 0 then
4085 l_dt_data_cnt := l_dt_data_cnt + 1;
4086 dt_data(l_dt_data_cnt).dataset_data := l_dataset_dummy;
4087 end if;
4088 end loop;
4089
4090 for i in 1..dt_data.count loop
4091 l_dt_data_length := length(dt_data(i).dataset_data);
4092 l_pos := instr(dt_data(i).dataset_data, ',');
4093 dt_data(i).dataset_id := substr(dt_data(i).dataset_data, 1, l_pos - 1);
4094 dt_data(i).color_method := substr(dt_data(i).dataset_data, l_pos + 1, l_dt_data_length);
4095 end loop;
4096
4097 for cr in c_Dataset_Color loop
4098 l_dataset_id := cr.DATASET_ID;
4099 l_color_method := cr.COLOR_METHOD;
4100 -- loop over TABLE type to determine if method has changed.
4101 for i in 1..dt_data.count loop
4102
4103 if ((l_dataset_id = dt_data(i).dataset_id) and (l_dataset_id <> -1)) then
4104 -- if datasets are the same.
4105 if (l_color_method <> dt_data(i).color_method) then
4106 -- if color methods are not the same.
4107 l_dataset_list := l_dataset_list || ',' || l_dataset_id ;
4108 end if;
4109 end if;
4110 end loop; -- end 1..dt_data.count loop
4111 end loop; -- end c_Dataset_Color
4112
4113 l_dataset_list := l_dataset_list || ',';
4114 --dbms_output.put_line(' LAST - l_dataset_list ' || l_dataset_list);
4115
4116 for cd in c_Indicators_Cursor loop -- this cursor uses l_dataset_list
4117 if(l_isFirst = true) then
4118 l_indicators := l_indicators || cd.name;
4119 l_isFirst := false;
4120 else
4121 l_indicators := l_indicators ||' , '||cd.name;
4122 end if;
4123 end loop;
4124
4125 -- determine if there's been a change in color triggers
4126
4127 select a.property_value, b.property_Value, c.property_value,
4128 d.property_value, e.property_Value, f.property_value,
4129 g.property_value, h.property_Value
4130 into l2_m1_accept, l2_m1_marg, l2_m2_accept,
4131 l2_m2_marg, l2_m3_upr_accept, l2_m3_upr_marg,
4132 l2_m3_lwr_accept, l2_m3_lwr_marg
4133 from bsc_kpi_properties a, bsc_kpi_properties b, bsc_kpi_properties c,
4134 bsc_kpi_properties d, bsc_kpi_properties e, bsc_kpi_properties f,
4135 bsc_kpi_properties g, bsc_kpi_properties h
4136 where a.property_code like 'COL_M1_LEVEL1'
4137 and b.property_code like 'COL_M1_LEVEL2'
4138 and c.property_code like 'COL_M2_LEVEL1'
4139 and d.property_code like 'COL_M2_LEVEL2'
4140 and e.property_code like 'COL_M3_LEVEL1'
4141 and f.property_code like 'COL_M3_LEVEL2'
4142 and g.property_code like 'COL_M3_LEVEL3'
4143 and h.property_code like 'COL_M3_LEVEL4'
4144 and a.indicator = p_kpi_id
4145 and b.indicator = p_kpi_id
4146 and c.indicator = p_kpi_id
4147 and d.indicator = p_kpi_id
4148 and e.indicator = p_kpi_id
4149 and f.indicator = p_kpi_id
4150 and g.indicator = p_kpi_id
4151 and h.indicator = p_kpi_id;
4152
4153
4154 -- if there is a color trigger change, then notify the current indicator
4155
4156 if (l2_m1_accept <> l_m1_accept) or (l2_m1_marg <> l_m1_marg) or
4157 (l2_m2_accept <> l_m2_accept) or (l2_m2_marg <> l_m2_marg) or
4158 (l2_m3_upr_accept <> l_m3_upr_accept) or (l2_m3_upr_marg <> l_m3_upr_marg) or
4159 (l2_m3_lwr_accept <> l_m3_lwr_accept) or (l2_m3_lwr_marg <> l_m3_lwr_marg) then
4160
4161
4162 select name || '[' || indicator || ']'
4163 into l_kpi_name
4164 from bsc_kpis_vl
4165 where indicator = p_kpi_id;
4166
4167 if ((instr(l_indicators, l_kpi_name) = 0) or (l_indicators is null)) then
4168 if(l_isFirst = true) then
4169 l_indicators := l_kpi_name;
4170 else
4171 l_indicators := l_indicators || ', ' || l_kpi_name;
4172 end if;
4173 end if;
4174 end if;
4175
4176 x_msg_data := null;
4177
4178 if (l_indicators is not null) then
4179 fnd_message.set_name('BSC','BSC_PMD_KPI_COLOR_INVALID');
4180 fnd_message.set_token('INDICATORS', l_indicators);
4181 fnd_msg_pub.ADD;
4182 raise fnd_api.g_exc_error;
4183 end if;
4184
4185 end if; -- end isBscInProductionMode
4186
4187 exception
4188 when fnd_api.g_exc_error then
4189 if (x_msg_data is null) then
4190 fnd_msg_pub.count_and_get
4191 ( p_encoded => fnd_api.g_false
4192 , p_count => x_msg_count
4193 , p_data => x_msg_data
4194 );
4195 end if;
4196
4197
4198 when others then
4199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4200 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
4201 ,p_data => x_msg_data);
4202
4203 x_msg_data := x_msg_data || sqlerrm;
4204
4205 end get_Color_Change_Trigger;
4206
4207 /*
4208 ***************************************************
4209 function remove_percent()
4210 ***************************************************
4211 */
4212
4213 function remove_percent(
4214 p_input in varchar2
4215 ) return number is
4216 begin
4217 if (substr(p_input, LENGTH(p_input), 1) = '%') then
4218 return substr(p_input, 1, length(p_input)-1);
4219 else
4220 return p_input;
4221 end if;
4222
4223 exception when others then
4224 return -999; -- fixed Bug#3255382
4225 end remove_percent;
4226
4227 /*******************************************************************************
4228 Return 'T' if measure exists with given display name and source type,
4229 return 'F' otherwise.
4230 Parameters:
4231 p_display_name := measure display name
4232 p_source_type := either BSC_BIS_MEASURE_PUB.c_BSC or
4233 BSC_BIS_MEASURE_PUB.c_PMF
4234 *******************************************************************************/
4235 FUNCTION Is_Unique_Measure_Display_Name(
4236 p_dataset_id NUMBER
4237 ,p_display_name VARCHAR2
4238 ,p_source_type VARCHAR2
4239 ) RETURN VARCHAR2 IS
4240 l_display_name BIS_INDICATORS_TL.NAME%TYPE;
4241 BEGIN
4242 BSC_BIS_MEASURE_PUB.get_Measure_Name
4243 ( p_dataset_id => p_dataset_id
4244 ,p_ui_flag => 'Y'
4245 ,p_dataset_source => p_source_type
4246 ,p_dataset_name => p_display_name
4247 ,x_measure_name => l_display_name
4248 );
4249 RETURN 'T';
4250 EXCEPTION
4251 WHEN OTHERS THEN RETURN 'F';
4252 END Is_Unique_Measure_Display_Name;
4253
4254 FUNCTION gen_name_for_column(
4255 p_name IN VARCHAR2
4256 )RETURN VARCHAR2 IS
4257
4258 l_asc number;
4259 l_measure_col bsc_sys_datasets_tl.name%TYPE;
4260 l_char varchar2(1);
4261 l_alias VARCHAR2(30);
4262 l_StartingWithInvalidType BOOLEAN; --invalid type => numeric and underscore
4263
4264 BEGIN
4265 -- Valid values - numbers/alphabets/underscore
4266
4267 l_StartingWithInvalidType := TRUE;
4268
4269 for i in 1..length(p_name) loop
4270 begin
4271 l_char := substr(p_name, i, 1);
4272 exception
4273 when others then
4274 l_char := ' ';/* comsuming this exception as substr() will throw exception for NLS charactes and whole procedure is not being executed */
4275 end;
4276
4277 l_asc := ascii(l_char);
4278 If ((l_asc >= 48 And l_asc <= 57)) or
4279 (l_asc >= 65 And l_asc <= 90) or
4280 (l_asc >= 97 And l_asc <= 122) or
4281 (l_asc = 95) Then
4282
4283 -- added for Bug#3894955 and bug#4157795
4284 IF (l_StartingWithInvalidType AND (l_asc < 48 OR l_asc > 57) AND (l_asc <> 95)) THEN
4285 l_StartingWithInvalidType := FALSE;
4286 END IF;
4287
4288 IF (NOT l_StartingWithInvalidType) THEN
4289 l_measure_col := l_measure_col || l_char;
4290 END IF;
4291
4292 end if;
4293 end loop;
4294
4295 RETURN l_measure_col;
4296
4297 EXCEPTION
4298 WHEN OTHERS THEN
4299 RETURN l_measure_col;
4300 END gen_name_for_column;
4301
4302 FUNCTION is_Valid_AlphaNum
4303 (
4304 p_name IN VARCHAR2
4305 ) RETURN BOOLEAN
4306 IS
4307 l_SQL_Ident VARCHAR2(30);
4308 BEGIN
4309 IF (p_name IS NULL) THEN
4310 RETURN FALSE;
4311 END IF;
4312 l_SQL_Ident := UPPER(p_name);
4313 IF (REPLACE(TRANSLATE(l_SQL_Ident, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_',
4314 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 'X', '') IS NOT NULL) THEN
4315 RETURN FALSE;
4316 END IF;
4317 RETURN TRUE;
4318 END is_Valid_AlphaNum;
4319
4320 /*
4321 * Return 'T' if specified column name in table is 'NUMBER',
4322 * return 'F' otherwise
4323 */
4324 FUNCTION Is_Numeric_Column(
4325 p_table_name IN VARCHAR2
4326 ,p_column_name IN VARCHAR2
4327 ) RETURN VARCHAR2
4328 IS
4329 l_ret_val VARCHAR2(1) := 'F';
4330 l_data_type USER_TAB_COLUMNS.DATA_TYPE%TYPE;
4331 BEGIN
4332 SELECT data_type INTO l_data_type
4333 FROM user_tab_columns
4334 WHERE table_name = p_table_name
4335 AND column_name = p_column_name;
4336
4337 IF (l_data_type = 'NUMBER') THEN
4338 l_ret_val := 'T';
4339 END IF;
4340
4341 RETURN l_ret_val;
4342 EXCEPTION
4343 WHEN OTHERS THEN
4344 RETURN 'F';
4345 END Is_Numeric_Column;
4346
4347
4348 --=============================================================================
4349 -- Wrapper for fnd_load_util.upload_test() that test whether a record should
4350 -- be updated based on Last_Update_Date.
4351 -- p_Measure_Short_Name: measure short name to be checked
4352 -- p_NLS_Mode: NLS_MODE for upload
4353 -- p_file_lub: last_update_by id from ldt file
4354 -- p_file_lud: last_update_date from ldt file
4355 -- p_custom_mode: 'FORCE' or none
4356 --=============================================================================
4357 FUNCTION Upload_Test (
4358 p_measure_short_name IN VARCHAR2
4359 ,p_nls_mode IN VARCHAR2
4360 ,p_file_lub IN NUMBER
4361 ,p_file_lud IN DATE
4362 ,p_custom_mode IN VARCHAR2
4363 ) RETURN BOOLEAN
4364 IS
4365 CURSOR mea_cur (cp_measure_short_name VARCHAR2) IS
4366 SELECT last_updated_by, last_update_date
4367 FROM bis_indicators_vl
4368 WHERE short_name = cp_measure_short_name;
4369
4370 CURSOR mea_tl_cur (cp_measure_short_name VARCHAR2) IS
4371 SELECT tl.last_updated_by, tl.last_update_date
4372 FROM bis_indicators b, bis_indicators_tl tl
4373 WHERE b.INDICATOR_ID = tl.indicator_id
4374 AND b.short_name = cp_measure_short_name
4375 AND tl.LANGUAGE = userenv('LANG');
4376
4377 l_db_lub BIS_INDICATORS.LAST_UPDATED_BY%TYPE;
4378 l_db_lud BIS_INDICATORS.LAST_UPDATE_DATE%TYPE;
4379 BEGIN
4380
4381 IF (mea_cur%ISOPEN) THEN
4382 CLOSE mea_cur;
4383 END IF;
4384 IF (mea_tl_cur%ISOPEN) THEN
4385 CLOSE mea_tl_cur;
4386 END IF;
4387
4388 IF (p_nls_mode = 'NLS') THEN
4389 OPEN mea_tl_cur(cp_measure_short_name => p_measure_short_name);
4390 FETCH mea_tl_cur INTO l_db_lub, l_db_lud;
4391 CLOSE mea_tl_cur;
4392 ELSE
4393 OPEN mea_cur(cp_measure_short_name => p_measure_short_name);
4394 FETCH mea_cur INTO l_db_lub, l_db_lud;
4395 CLOSE mea_cur;
4396 END IF;
4397
4398 RETURN fnd_load_util.upload_test(p_file_lub, p_file_lud, l_db_lub, l_db_lud, p_custom_mode);
4399
4400 EXCEPTION
4401 WHEN OTHERS THEN
4402 IF (mea_cur%ISOPEN) THEN
4403 CLOSE mea_cur;
4404 END IF;
4405 IF (mea_tl_cur%ISOPEN) THEN
4406 CLOSE mea_tl_cur;
4407 END IF;
4408 RETURN FALSE;
4409 END Upload_Test;
4410
4411
4412 FUNCTION Get_Meas_With_Src_Col(
4413 p_measure_col IN VARCHAR2
4414 ) RETURN VARCHAR2 IS
4415 l_Flag BOOLEAN;
4416 l_temp VARCHAR2(250);
4417 l_measure_ids VARCHAR2(250);
4418 l_measure_id VARCHAR2(30);
4419
4420 CURSOR c_chk_col_in_formula(p_measure_col VARCHAR2) IS
4421 SELECT measure_id,measure_col
4422 FROM bsc_sys_measures
4423 WHERE measure_col like '%'||p_measure_col||'%';
4424
4425
4426 CURSOR c_chk_measid_in_measure(p_measure_id VARCHAR2) IS
4427 SELECT name
4428 FROM bsc_sys_datasets_vl
4429 WHERE measure_id1 = p_measure_id
4430 OR measure_id2 = p_measure_id;
4431
4432 BEGIN
4433 l_Flag := FALSE;
4434 l_temp := NULL;
4435 l_measure_ids := NULL;
4436
4437
4438 FOR cd in c_chk_col_in_formula(p_measure_col) LOOP
4439 --for every formula retrieved checking if the measure column is a part of the formula.
4440 l_Flag := BSC_BIS_MEASURE_PUB.Is_MeasureCol_In_Formula(p_measure_col,cd.measure_col);
4441
4442 IF (l_Flag) THEN
4443
4444 IF (cd.measure_id) IS NOT NULL THEN
4445 FOR ccd in c_chk_measid_in_measure(cd.measure_id) LOOP
4446 --ccd.name need to make a comma separated list of these
4447 IF (l_temp IS NULL) THEN
4448 l_temp := ccd.name;
4449 ELSE
4450 l_temp := l_temp ||','|| ccd.name ;
4451 END IF;
4452 END LOOP;
4453 END IF;
4454 END IF;
4455 END LOOP;
4456
4457 RETURN l_temp;
4458
4459 END Get_Meas_With_Src_Col;
4460
4461
4462
4463
4464
4465 FUNCTION Get_Sing_Par_Meas_DS (
4466 p_measure_id IN VARCHAR2
4467 ) RETURN VARCHAR2
4468 IS
4469 l_count NUMBER;
4470 l_dataset_id VARCHAR2(250);
4471
4472 BEGIN
4473
4474 l_count := 0;
4475 l_dataset_id := NULL;
4476
4477 SELECT COUNT(1) into l_count
4478 FROM BSC_SYS_DATASETS_B
4479 WHERE MEASURE_ID1 =TO_NUMBER(p_measure_id)
4480 AND MEASURE_ID2 IS NULL;
4481
4482 IF (l_count=1) THEN
4483 SELECT DATASET_ID INTO l_dataset_id
4484 FROM BSC_SYS_DATASETS_B
4485 WHERE MEASURE_ID1 = TO_NUMBER(p_measure_id)
4486 AND MEASURE_ID2 IS NULL;
4487
4488 END IF;
4489
4490 RETURN l_dataset_id;
4491
4492 END Get_Sing_Par_Meas_DS;
4493
4494 --Return primary data source (i.e. region_code portion of actual_data_source) of the indicator
4495 FUNCTION Get_Primary_Data_Source (
4496 p_indicator_id IN BIS_INDICATORS.INDICATOR_ID%TYPE
4497 ) RETURN BIS_INDICATORS.ACTUAL_DATA_SOURCE%TYPE
4498 IS
4499 l_retval BIS_INDICATORS.ACTUAL_DATA_SOURCE%TYPE;
4500 BEGIN
4501 SELECT substr(actual_data_source, 1, instr(actual_data_source, '.') -1) INTO l_retval
4502 FROM bis_indicators
4503 WHERE indicator_id = p_indicator_id;
4504 RETURN l_retval;
4505 EXCEPTION
4506 WHEN OTHERS THEN
4507 RETURN l_retval;
4508 END Get_Primary_Data_Source;
4509
4510
4511 -- added for Bug#4617140
4512 FUNCTION Is_Formula_Type (p_measure_col IN VARCHAR2)
4513 RETURN VARCHAR2 IS
4514 BEGIN
4515 IF (instr(p_measure_col, '/') > 0) or
4516 (instr(p_measure_col, '(') > 0) or
4517 (instr(p_measure_col, ')') > 0) or
4518 (instr(p_measure_col, '+') > 0) or
4519 (instr(p_measure_col, '-') > 0) or
4520 (instr(p_measure_col, '*') > 0) or
4521 (instr(p_measure_col, ',') > 0) or
4522 (instr(p_measure_col, ' ') > 0) then
4523 RETURN FND_API.G_TRUE;
4524 ELSE
4525 RETURN FND_API.G_FALSE;
4526 END IF;
4527 EXCEPTION
4528 WHEN OTHERS THEN
4529 RETURN FND_API.G_FALSE;
4530 END Is_Formula_Type;
4531
4532 -- added for Bug#4617140
4533 FUNCTION Get_Report_Objectives (
4534 p_Dataset_Id IN NUMBER
4535 ) RETURN VARCHAR2 IS
4536 l_Objective_Names VARCHAR2(32000);
4537
4538 CURSOR c_Get_Obj_Names IS
4539 SELECT
4540 OB.NAME
4541 FROM
4542 BSC_KPIS_VL OB,
4543 BSC_KPI_ANALYSIS_MEASURES_B AM,
4544 BSC_SYS_DATASETS_B ME
4545 WHERE
4546 ME.DATASET_ID = p_Dataset_Id AND
4547 AM.DATASET_ID = ME.DATASET_ID AND
4548 OB.INDICATOR = AM.INDICATOR AND
4549 OB.SHORT_NAME IS NOT NULL;
4550 BEGIN
4551 FOR c_GON IN c_Get_Obj_Names LOOP
4552 IF (l_Objective_Names IS NULL) THEN
4553 l_Objective_Names := c_GON.NAME;
4554 ELSE
4555 l_Objective_Names := l_Objective_Names || ', ' || c_GON.NAME;
4556 END IF;
4557 END LOOP;
4558
4559
4560 RETURN l_Objective_Names;
4561 EXCEPTION
4562 WHEN OTHERS THEN
4563 RETURN NULL;
4564 END Get_Report_Objectives;
4565
4566 end BSC_BIS_MEASURE_PUB;