[Home] [Help]
PACKAGE BODY: APPS.BSC_DATASETS_PVT
Source
1 package body BSC_DATASETS_PVT as
2 /* $Header: BSCVDTSB.pls 120.9 2007/06/28 06:53:54 ashankar ship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCVDTSB.pls |
10 | |
11 | Creation Date: |
12 | October 10, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | Private Body version. |
19 | This package creates a BSC Dataset. |
20 | |
21 | History: |
22 | 04-APR-03 Ashankar fix bug # 2883880 |
23 | 23-APR-03 mdamle PMD - Measure Definer Support |
24 | Removed all rollbacks - all rollbacks will be taken |
25 | care of on the java side by BC4J whenever an error is |
26 | raised. |
27 | 07-Jul-03 mdamle PMD - Added Y Axis Title |
28 | 03-Sep-03 adrao Fixed Bug #3123509 (Update_Dataset) |
29 | 05-Sep-03 mdamle Fixed Bug #3123558 Added check for duplicate measure_col |
30 | 07-Sep-03 arhegde bug# 3123901 Propogate error to outer layers. |
31 | 25-SEP-03 mdamle Bug#3160325 - Sync up measures for all installed |
32 | languages |
33 | 29-SEP-03 adrao Bug#3160325 - Sync up measures for all installed |
34 | source languages |
35 | 25-SEP-03 mdamle Bug#3170184 - Check for duplicate source column by source |
36 | type |
37 | 28-OCT-03 PAJOHRI Bug #3184408, removed TRIM from Create_Measures & |
38 | Update_Measures API |
39 | 27-NOV-03 adrao Bug#3238554 - Modifed procedure Update_Measure and added |
40 | condition to perform incremental changes |
41 | 02-DEC-03 ashankar Bug#3291278 - Modifed procedure Update_Measure and created |
42 | cursor to get the value of the Type column from bsc_sys_meas|
43 | ures for the measure. |
44 | 11-DEC-03 PAJOHRI Bug #3309050 |
45 | 06-JAN-04 PAJOHRI Bug #3349897, modified procedure Update_Measures to fix |
46 | record l_Dataset_Rec.Bsc_Measure_Color_Formula|
47 | to flag prototype_flag = 4 if value is changed|
48 | 24-FEB-04 KYADAMAK Bug #3439942 space not allowed for PMF Measures |
49 | 02-MAR-04 ANKGOEL Bug #3464470 Forward port fix of bug#3450505 |
50 | 24-MAR-04 ADRAO Bug #3528425 Perform structural change, when Data Group is |
51 | changed for any measure |
52 | 24-MAY-04 ADRAO Bug #3628113 Removed Measure Columns based on MEASURE_ID2 |
53 | in Delete_Measure API |
54 | 27-JUL-04 sawu Added logic to set WHO columns in create/update api |
55 | 28-JUL-04 adrao Bug#3781176 Added logic in Delete_Measures(), whenever |
56 | the same source column is referenced in both |
57 | BSC_SYS_DATASETS_B.MEASURE_ID1/MEASURE_ID2 |
58 | 17-AUG-04 visuri Bug#3681116 Added logic in Update_Dataset() API to ensure |
59 | that numeric format change of any measure also |
60 | updates the default format of indicators for |
61 | which that measure is a default measure. |
62 | 24-AUG-2004 ashankar Bug#3844190 Creating unique measure col across the system. |
63 | 20-Dec-2004 sawu Bug#4045278: updated update_measure and update_dataset to |
64 | populate last_update_date from record structure|
65 | Updated create_measure and create_dataset to |
66 | populate creation_date and LUD also. |
67 | 20-Sep-2005 akoduri Bug#4613172: CDS type measures should not get populated into|
68 | bsc_db_measure_cols_tl |
69 | 05-JAN-06 ppandey Enh#4860106 Handled structureal and non-structural |
70 | formula change |
71 | 13-JAN-06 ppandey Enh#4860106 Reverting due to open Bug #4941403 from backend.|
72 | 24-JAN-06 ankgoel Bug#4954663 Show Info text for AG to PL/SQL or VB conversion|
73 | 04-AUG-06 akoduri Enh#5416542 Cause Effect Phase2 |
74 | 14-Feb-07 rkumar Bug#5877454 Changed l_indicator length to 32000 |
75 | 24-MAY-07 ppandey Bug#5954147 Changing goal type will reset thresholds, as |
76 | thresholds are at Kpi level with color enh. |
77 | 27-JUN-07 ashankar Bug#6134461 Filtered out P&L objectives when GOAL type is changed |
78 +======================================================================================+
79 */
80 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_DATASETS_PVT';
81 g_db_object varchar2(30) := null;
82 TYPE string_tabletype IS
83 TABLE OF VARCHAR2(300) INDEX BY BINARY_INTEGER;
84
85 --: This procedure creates a BSC measure. This is the entry point for the
86 --: Data Set API.
87 --: This procedure is part of the Data Set API.
88
89 procedure Create_Measures(
90 p_commit IN varchar2 := FND_API.G_FALSE
91 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
92 ,x_return_status OUT NOCOPY varchar2
93 ,x_msg_count OUT NOCOPY number
94 ,x_msg_data OUT NOCOPY varchar2
95 ) is
96
97 l_count number;
98 l_count_mescol number;
99 l_color_formula varchar2(200);
100 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
101
102 begin
103 l_Dataset_Rec := p_Dataset_Rec;
104
105 -- Set who columns
106 if l_Dataset_Rec.Bsc_Measure_Created_By is null then
107 l_Dataset_Rec.Bsc_Measure_Created_By := fnd_global.USER_ID;
108 end if;
109 if l_Dataset_Rec.Bsc_Measure_Last_Update_By is null then
110 l_Dataset_Rec.Bsc_Measure_Last_Update_By := fnd_global.USER_ID;
111 end if;
112 if l_Dataset_Rec.Bsc_Measure_Last_Update_Login is null then
113 l_Dataset_Rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
114 end if;
115 if l_Dataset_Rec.Bsc_Measure_Creation_Date is null then
116 l_Dataset_Rec.Bsc_Measure_Creation_Date := sysdate;
117 end if;
118 if l_Dataset_Rec.Bsc_Measure_Last_Update_Date is null then
119 l_Dataset_Rec.Bsc_Measure_Last_Update_Date := sysdate;
120 end if;
121
122 -- Verify that measure id does not exist.
123 select count(1)
124 into l_count
125 from BSC_SYS_MEASURES
126 where measure_id = l_Dataset_Rec.Bsc_Measure_Id;
127
128 -- If measure id does not exist then go ahead and create it, if it does then raise
129 -- an error.
130 if l_count = 0 then
131
132 g_db_object := 'BSC_SYS_MEASURES';
133
134 -- Check if measure_col already exists
135 select count(1) into l_count_mescol
136 from BSC_DB_MEASURE_COLS_VL
137 where upper(measure_col) = upper(l_Dataset_Rec.Bsc_Measure_Col);
138 if (l_count_mescol > 0) then
139 FND_MESSAGE.SET_NAME('BSC','BSC_MEASURE_SOURCE_NAME');
140 FND_MESSAGE.SET_TOKEN('MEASURE', p_Dataset_Rec.Bsc_Dataset_Name);
141 FND_MSG_PUB.ADD;
142 RAISE FND_API.G_EXC_ERROR;
143 end if;
144
145
146 -- Insert pertaining values into table bsc_sys_measures.
147 -- Reminder: Some values are hard coded. Find source.
148 insert into BSC_SYS_MEASURES( measure_id
149 ,measure_col
150 ,operation
151 ,type
152 ,min_actual_value
153 ,max_actual_value
154 ,min_budget_value
155 ,max_budget_value
156 ,random_style
157 ,edw_flag
158 ,edw_fact_id
159 ,edw_meas_id
160 ,short_name
161 ,source
162 ,s_color_formula
163 ,created_by -- PMD
164 ,creation_date -- PMD
165 ,last_updated_by -- PMD
166 ,last_update_date -- PMD
167 ,last_update_login) -- PMD
168 values( l_Dataset_Rec.Bsc_Measure_Id
169 ,l_Dataset_Rec.Bsc_Measure_Col
170 ,l_Dataset_Rec.Bsc_Measure_Operation
171 ,l_Dataset_Rec.Bsc_Meas_Type
172 ,l_Dataset_Rec.Bsc_Measure_Min_Act_Value
173 ,l_Dataset_Rec.Bsc_Measure_Max_Act_Value
174 ,l_Dataset_Rec.Bsc_Measure_Min_Bud_Value
175 ,l_Dataset_Rec.Bsc_Measure_Max_Bud_Value
176 ,l_Dataset_Rec.Bsc_Measure_Random_Style
177 ,0
178 ,null
179 ,null
180 ,l_Dataset_Rec.Bsc_Measure_Short_Name
181 ,l_Dataset_Rec.Bsc_Source
182 ,l_Dataset_Rec.Bsc_Measure_Color_Formula
183 ,l_Dataset_Rec.Bsc_Measure_Created_By -- PMD
184 ,l_Dataset_Rec.Bsc_Measure_Creation_Date -- PMD
185 ,l_Dataset_Rec.Bsc_Measure_Last_Update_By -- PMD
186 ,l_Dataset_Rec.Bsc_Measure_Last_Update_Date -- PMD
187 ,l_Dataset_Rec.Bsc_Measure_Last_Update_Login);-- PMD
188
189 -- Insert pertaining values into table bsc_db_measure_cols_tl.
190 /*
191 insert into BSC_DB_MEASURE_COLS_TL( measure_col
192 ,language
193 ,source_lang
194 ,help
195 ,measure_group_id
196 ,projection_id
197 ,measure_type)
198 values( p_Dataset_Rec.Bsc_Measure_Col
199 ,p_Dataset_Rec.Bsc_Language
200 ,p_Dataset_Rec.Bsc_Source_Language
201 ,p_Dataset_Rec.Bsc_Measure_Help
202 ,p_Dataset_Rec.Bsc_Measure_Group_Id
203 ,p_Dataset_Rec.Bsc_Measure_Projection_Id
204 ,p_Dataset_Rec.Bsc_Measure_Type);
205 */
206
207 if (p_commit = FND_API.G_TRUE) then
208 commit;
209 end if;
210
211 else
212 FND_MESSAGE.SET_NAME('BSC','BSC_MEAS_ID_EXISTS');
213 FND_MESSAGE.SET_TOKEN('BSC_MEAS', l_Dataset_Rec.Bsc_Measure_Id);
214 FND_MSG_PUB.ADD;
215 RAISE FND_API.G_EXC_ERROR;
216 end if;
217
218
219 EXCEPTION
220 WHEN FND_API.G_EXC_ERROR THEN
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
223 ,p_data => x_msg_data);
224 raise;
225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
228 ,p_data => x_msg_data);
229 raise;
230 WHEN NO_DATA_FOUND THEN
231 x_return_status := FND_API.G_RET_STS_ERROR;
232 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
233 ,p_data => x_msg_data);
234 raise;
235 WHEN OTHERS THEN
236 FND_MSG_PUB.Initialize;
237 if (SQLCODE = -01400) then
238 FND_MESSAGE.SET_NAME('BSC','BSC_TABLE_NULL_VALUE');
239 FND_MESSAGE.SET_TOKEN('BSC_OBJECT', g_db_object);
240 FND_MSG_PUB.ADD;
241 RAISE FND_API.G_EXC_ERROR;
242 end if;
243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
244 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
245 ,p_data => x_msg_data);
246
247 raise;
248 end Create_Measures;
249
250 /************************************************************************************
251 ************************************************************************************/
252
253 procedure Retrieve_Measures(
254 p_commit IN varchar2 := FND_API.G_FALSE
255 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
256 ,x_Dataset_Rec IN OUT NOCOPY BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
257 ,x_return_status OUT NOCOPY varchar2
258 ,x_msg_count OUT NOCOPY number
259 ,x_msg_data OUT NOCOPY varchar2
260 ) is
261
262 begin
263
264 g_db_object := 'Retrieve_Measures';
265 -- added measure_type for Bug #3238554
266 -- added NVL, since measure_type is a nullable column.
267 -- added Bsc_Measure_Group_id for Bug#3528425
268 select distinct a.measure_col
269 ,a.operation
270 ,a.type
271 ,a.min_actual_value
272 ,a.max_actual_value
273 ,a.min_budget_value
274 ,a.max_budget_value
275 ,a.random_style
276 ,a.s_color_formula
277 ,a.source
278 ,a.created_by -- PMD
279 ,a.creation_date -- PMD
280 ,a.last_updated_by -- PMD
281 ,a.last_update_date -- PMD
282 ,a.last_update_login -- PMD
283 ,b.projection_id
284 ,nvl(b.measure_type, 0)
285 ,nvl(b.measure_group_id, -1)
286 into x_Dataset_Rec.Bsc_Measure_Col
287 ,x_Dataset_Rec.Bsc_Measure_Operation
288 ,x_Dataset_Rec.Bsc_Meas_Type
289 ,x_Dataset_Rec.Bsc_Measure_Min_Act_Value
290 ,x_Dataset_Rec.Bsc_Measure_Max_Act_Value
291 ,x_Dataset_Rec.Bsc_Measure_Min_Bud_Value
292 ,x_Dataset_Rec.Bsc_Measure_Max_Bud_Value
293 ,x_Dataset_Rec.Bsc_Measure_Random_Style
294 ,x_Dataset_Rec.Bsc_measure_color_formula
295 ,x_Dataset_Rec.Bsc_Source
296 ,x_Dataset_Rec.Bsc_Measure_Created_By -- PMD
297 ,x_Dataset_Rec.Bsc_Measure_Creation_Date -- PMD
298 ,x_Dataset_Rec.Bsc_Measure_Last_Update_By -- PMD
299 ,x_Dataset_Rec.Bsc_Measure_Last_Update_Date -- PMD
300 ,x_Dataset_Rec.Bsc_Measure_Last_Update_Login -- PMD
301 ,x_Dataset_Rec.Bsc_Measure_Projection_Id
302 ,x_Dataset_Rec.Bsc_Measure_Type
303 ,x_Dataset_Rec.Bsc_Measure_Group_Id
304 from BSC_SYS_MEASURES a
305 ,bsc_db_measure_cols_vl b
306 where a.measure_id = p_Dataset_Rec.Bsc_Measure_Id
307 and a.measure_col = b.Measure_Col(+);
308
309 if (p_commit = FND_API.G_TRUE) then
310 commit;
311 end if;
312
313 EXCEPTION
314 WHEN FND_API.G_EXC_ERROR THEN
315 x_return_status := FND_API.G_RET_STS_ERROR;
316 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
317 ,p_data => x_msg_data);
318 raise;
319 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
321 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
322 ,p_data => x_msg_data);
323 raise;
324 WHEN NO_DATA_FOUND THEN
325 FND_MSG_PUB.Initialize;
326 FND_MESSAGE.SET_NAME('BSC','BSC_NO_VALUE_FOUND');
327 FND_MESSAGE.SET_TOKEN('BSC_OBJECT', g_db_object);
328 FND_MSG_PUB.ADD;
329 RAISE FND_API.G_EXC_ERROR;
330 WHEN OTHERS THEN
331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
332 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
333 ,p_data => x_msg_data);
334 raise;
335
336 end Retrieve_Measures;
337
338 /************************************************************************************
339 ************************************************************************************/
340 FUNCTION is_Number (
341 char_in VARCHAR2
342 ) RETURN BOOLEAN
343 IS
344 n NUMBER;
345 BEGIN
346 n := TO_NUMBER(char_in);
347 RETURN TRUE;
348 EXCEPTION
349 WHEN OTHERS THEN
350 RETURN FALSE;
351 END is_number;
352
353 FUNCTION get_Formula_Table (
354 p_Formula IN VARCHAR2
355 ,x_Count OUT NOCOPY NUMBER
356 ) RETURN string_tabletype
357 IS
358 l_Formula VARCHAR2(300);
359 formula_Table string_tabletype;
360 l_Formula_entity VARCHAR2(300);
361 l_Count NUMBER;
362 l_already_Exists BOOLEAN;
363 l_Is_Number BOOLEAN;
364 BEGIN
365 l_Formula := REPLACE (p_Formula, ' ');
366 l_Formula := REPLACE (l_Formula, '(',',');
367 l_Formula := REPLACE (l_Formula, ')',',');
368 l_Formula := REPLACE (l_Formula, '+',',');
369 l_Formula := REPLACE (l_Formula, '-',',');
370 l_Formula := REPLACE (l_Formula, '*',',');
371 l_Formula := REPLACE (l_Formula, '/',',');
372 l_Count := 0;
373 WHILE (bsc_utility.Is_More(l_Formula, l_Formula_entity)) LOOP
374 l_already_Exists := FALSE;
375 l_Is_Number := is_Number(l_Formula_entity);
376 IF (NOT l_Is_Number) THEN
377 FOR counter IN 1..l_Count LOOP
378 IF (l_Formula_entity = formula_Table (counter)) THEN
379 l_already_Exists := TRUE;
380 END IF;
381 END LOOP;
382 END IF;
383 IF (NOT l_Is_Number AND NOT l_already_Exists) THEN
384 l_Count := l_Count + 1;
385 formula_Table (l_Count) := l_Formula_entity;
386 END IF;
387 END LOOP;
388 x_Count := l_Count;
389 RETURN formula_Table;
390 END get_Formula_Table;
391
392
393 FUNCTION Is_Structure_change (
394 p_old_formula IN varchar2
395 ,p_new_formula IN varchar2
396 ) RETURN BOOLEAN
397 IS
398 l_Structure_Change BOOLEAN;
399 l_Old_Formula VARCHAR2(4000);
400 l_New_Formula VARCHAR2(4000);
401 l_New_Formula_Table string_tabletype;
402 l_Old_Formula_Table string_tabletype;
403 l_Old_Measure_Count NUMBER;
404 l_New_Measure_Count NUMBER;
405 l_Found BOOLEAN;
406 l_Entity VARCHAR2(300);
407 BEGIN
408 l_Structure_Change := FALSE;
409
410 --Following code added for temporary change, it needs to be removed for Bug #4860106
411 IF (p_old_formula <> p_new_formula) THEN
412 l_Structure_Change := TRUE;
413 END IF;
414
415 -- Actual fix for Bug #Bug #4860106 (Pending for Bug #4941403)- Don't remove it.- ppandey
416 /*IF (BSC_BIS_MEASURE_PUB.Is_Formula_Type(p_old_formula)=FND_API.G_TRUE AND BSC_BIS_MEASURE_PUB.Is_Formula_Type(p_new_formula)=FND_API.G_TRUE) THEN
417 l_Old_Formula_Table := get_Formula_Table(p_old_formula, l_Old_Measure_Count);
418 l_New_Formula_Table := get_Formula_Table(p_new_formula, l_New_Measure_Count);
419 IF (l_Old_Measure_Count <> l_New_Measure_Count) THEN
420 l_Structure_Change := TRUE;
421 ELSE
422 FOR counter1 IN 1..l_Old_Measure_Count LOOP
423 l_Found := FALSE;
424 l_Entity := l_Old_Formula_Table(counter1);
425 FOR counter2 IN 1..l_New_Measure_Count LOOP
426 IF (l_Entity = l_New_Formula_Table(counter2)) THEN
427 l_Found := TRUE;
428 END IF;
429 END LOOP;
430 IF (NOT l_Found) THEN
431 l_Structure_Change := TRUE;
432 END IF;
433 END LOOP;
434 END IF;
435 ELSE
436 l_Structure_Change := TRUE;
437 END IF;*/
438
439 RETURN l_Structure_Change;
440 END Is_Structure_change;
441
442
443 /************************************************************************************
444 ************************************************************************************/
445
446 procedure Update_Measures(
447 p_commit IN varchar2 := FND_API.G_FALSE
448 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
449 ,x_return_status OUT NOCOPY varchar2
450 ,x_msg_count OUT NOCOPY number
451 ,x_msg_data OUT NOCOPY varchar2
452 ) is
453
454 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
455
456 l_count number;
457 l_color_formula varchar2(200);
458 l_count_mescol number;
459 l_kpi_flag number := -1;
460 l_indicator_table BSC_NUM_LIST;
461 l_current_formula VARCHAR2(32000);
462 l_prototype_flag BSC_NUM_LIST;
463
464 CURSOR indicators_cursor is
465 SELECT am.indicator, kpi.prototype_flag
466 FROM bsc_kpi_analysis_measures_b am,
467 bsc_kpis_b kpi
468 WHERE kpi.indicator = am.indicator
469 AND dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
470
471 CURSOR c_measures_col IS
472 SELECT Type
473 FROM BSC_SYS_MEASURES
474 WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id;
475
476 begin
477
478 -- Check that valid measure id was entered.
479 if p_Dataset_Rec.Bsc_Measure_Id is not null then
480 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_MEASURES'
481 ,'measure_id'
482 ,p_Dataset_Rec.Bsc_Measure_Id);
483 if l_count = 0 then
484 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_MEAS_ID');
485 FND_MESSAGE.SET_TOKEN('BSC_MEAS', p_Dataset_Rec.Bsc_Measure_Id);
486 FND_MSG_PUB.ADD;
487 RAISE FND_API.G_EXC_ERROR;
488 end if;
489 else
490 FND_MESSAGE.SET_NAME('BSC','BSC_NO_MEAS_ID_ENTERED');
491 FND_MSG_PUB.ADD;
492 RAISE FND_API.G_EXC_ERROR;
493 end if;
494
495 SELECT MEASURE_COL
496 INTO l_current_formula
497 FROM BSC_SYS_MEASURES
498 WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id;
499
500 IF(l_current_formula <> p_Dataset_Rec.Bsc_Measure_Col) THEN
501 select count(1) into l_count_mescol
502 from BSC_DB_MEASURE_COLS_VL
503 where upper(measure_col) = upper(p_Dataset_Rec.Bsc_Measure_Col);
504 if (l_count_mescol > 0) then
505 FND_MESSAGE.SET_NAME('BSC','BSC_MEASURE_SOURCE_NAME');
506 FND_MESSAGE.SET_TOKEN('MEASURE', p_Dataset_Rec.Bsc_Dataset_Name);
507 FND_MSG_PUB.ADD;
508 RAISE FND_API.G_EXC_ERROR;
509 end if;
510 END IF;
511
512
513 -- Not all values will be passed. We need to make sure values not passed are not
514 -- changed by procedure, therefore we get what is there before we do any updates.
515 Retrieve_Measures( p_commit
516 ,p_Dataset_Rec
517 ,l_Dataset_Rec
518 ,x_return_status
519 ,x_msg_count
520 ,x_msg_data);
521
522 -- update LOCAL language ,source language and level Id values with PASSED values.
523 l_Dataset_Rec.Bsc_Language := p_Dataset_Rec.Bsc_Language;
524 l_Dataset_Rec.Bsc_Source_Language := p_Dataset_Rec.Bsc_Source_Language;
525 l_Dataset_Rec.Bsc_Measure_Id := p_Dataset_Rec.Bsc_Measure_Id;
526
527 --sawu: update WHO column info with PASSED values
528 l_Dataset_Rec.Bsc_Measure_Last_Update_By := p_Dataset_Rec.Bsc_Measure_Last_Update_By;
529 l_Dataset_Rec.Bsc_Measure_Last_Update_Date := p_Dataset_Rec.Bsc_Measure_Last_Update_Date;
530 l_Dataset_Rec.Bsc_Measure_Last_Update_Login := p_Dataset_Rec.Bsc_Measure_Last_Update_Login;
531
532 -- mdamle 04/23/2003 - PMD - Measure Definer - Update flag in KPI for specific updates in the dataset
533 -- adrao added check for Bsc_Measure_Type for Incremental Changes to all indicators.
534 -- associated with the current measure, when type is changed from Activity -> Balance
535 -- and vice-versa Bug 3238554
536
537 -- adrao added Bsc_Measure_Group_Id to make Structural Changes, when Measure Group is changed.
538 -- for Bug#3528425
539
540 -- ppandey -Set prototype flag based on formula change or column group change
541 IF (p_Dataset_Rec.Bsc_Measure_Group_Id <> l_Dataset_Rec.Bsc_Measure_Group_Id) THEN
542 l_kpi_flag := BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure;
543 ELSIF (p_Dataset_Rec.Bsc_Measure_Col <> l_Dataset_Rec.Bsc_Measure_Col) THEN
544 IF (Is_Structure_change(p_Dataset_Rec.Bsc_Measure_Col, l_Dataset_Rec.Bsc_Measure_Col)) THEN
545 l_kpi_flag := BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure;
546 ELSE
547 l_kpi_flag := BSC_DESIGNER_PVT.G_ActionFlag.GAA_Update;
548 END IF;
549 END IF;
550
551 IF (l_kpi_flag <> -1) THEN
552 open indicators_cursor;
553 fetch indicators_cursor bulk collect into l_indicator_table, l_prototype_flag;
554 if indicators_cursor%ISOPEN THEN
555 CLOSE indicators_cursor;
556 end if;
557 for i in 1..l_indicator_table.count loop
558 BSC_DESIGNER_PVT.ActionFlag_Change(l_indicator_table(i), l_kpi_flag);
559 end loop;
560
561 ELSE
562 if (p_Dataset_Rec.Bsc_Measure_Operation <> l_Dataset_Rec.Bsc_Measure_Operation or
563 p_Dataset_Rec.Bsc_Measure_color_formula <> l_Dataset_Rec.Bsc_Measure_color_formula or
564 p_Dataset_Rec.Bsc_Dataset_Operation <> l_Dataset_Rec.Bsc_Dataset_operation or
565 p_Dataset_Rec.Bsc_Measure_Projection_Id <> l_Dataset_Rec.Bsc_Measure_Projection_Id or
566 p_Dataset_Rec.Bsc_Measure_Type <> l_Dataset_Rec.Bsc_Measure_Type) then
567 l_kpi_flag := BSC_DESIGNER_PVT.G_ActionFlag.GAA_Update;
568
569 open indicators_cursor;
570 fetch indicators_cursor bulk collect into l_indicator_table, l_prototype_flag;
571 if indicators_cursor%ISOPEN THEN
572 CLOSE indicators_cursor;
573 end if;
574 for i in 1..l_indicator_table.count loop
575 BSC_DESIGNER_PVT.ActionFlag_Change(l_indicator_table(i), l_kpi_flag);
576 end loop;
577 end if;
578 END IF;
579
580 -- Copy PASSED Record values into LOCAL Record values for the PASSED Record values
581 -- which are NOT NULL.
582 -- mdamle 03/12/2003 - PMD - Measure Definer
583 if p_Dataset_Rec.Bsc_Measure_Col is not null then
584 l_Dataset_Rec.Bsc_Measure_Col := p_Dataset_Rec.Bsc_Measure_Col;
585 end if;
586
587 IF (p_Dataset_Rec.Bsc_Source = BSC_BIS_MEASURE_PUB.c_PMF AND l_Dataset_Rec.Bsc_Source = BSC_BIS_MEASURE_PUB.c_BSC) THEN
588 l_Dataset_Rec.Bsc_Source := p_Dataset_Rec.Bsc_Source;
589 END IF;
590
591 if p_Dataset_Rec.Bsc_Measure_Short_Name is not null then
592 l_Dataset_Rec.Bsc_Measure_Short_Name := p_Dataset_Rec.Bsc_Measure_Short_Name;
593 end if;
594 if p_Dataset_Rec.Bsc_Measure_Operation is not null then
595 l_Dataset_Rec.Bsc_Measure_Operation := p_Dataset_Rec.Bsc_Measure_Operation;
596 end if;
597 if p_Dataset_Rec.Bsc_Meas_Type is not null then
598 l_Dataset_Rec.Bsc_Meas_Type := p_Dataset_Rec.Bsc_Meas_Type;
599 end if;
600 if p_Dataset_Rec.Bsc_Measure_Min_Act_Value is not null then
601 l_Dataset_Rec.Bsc_Measure_Min_Act_Value := p_Dataset_Rec.Bsc_Measure_Min_Act_Value;
602 end if;
603 if p_Dataset_Rec.Bsc_Measure_Max_Act_Value is not null then
604 l_Dataset_Rec.Bsc_Measure_Max_Act_Value := p_Dataset_Rec.Bsc_Measure_Max_Act_Value;
605 end if;
606 if p_Dataset_Rec.Bsc_Measure_color_formula is not null then
607 l_Dataset_Rec.Bsc_Measure_color_formula := p_Dataset_Rec.Bsc_Measure_color_formula;
608 end if;
609 if p_Dataset_Rec.Bsc_Measure_Min_Bud_Value is not null then
610 l_Dataset_Rec.Bsc_Measure_Min_Bud_Value := p_Dataset_Rec.Bsc_Measure_Min_Bud_Value;
611 end if;
612 if p_Dataset_Rec.Bsc_Measure_Max_Bud_Value is not null then
613 l_Dataset_Rec.Bsc_Measure_Max_Bud_Value := p_Dataset_Rec.Bsc_Measure_Max_Bud_Value;
614 end if;
615 if p_Dataset_Rec.Bsc_Measure_Random_Style is not null then
616 l_Dataset_Rec.Bsc_Measure_Random_Style := p_Dataset_Rec.Bsc_Measure_Random_Style;
617 end if;
618 if p_Dataset_Rec.Bsc_Measure_Help is not null then
619 l_Dataset_Rec.Bsc_Measure_Help := p_Dataset_Rec.Bsc_Measure_Help;
620 end if;
621 if p_Dataset_Rec.Bsc_Measure_Group_Id is not null then
622 l_Dataset_Rec.Bsc_Measure_Group_Id := p_Dataset_Rec.Bsc_Measure_Group_Id;
623 end if;
624 if p_Dataset_Rec.Bsc_Measure_Projection_Id is not null then
625 l_Dataset_Rec.Bsc_Measure_Projection_Id := p_Dataset_Rec.Bsc_Measure_Projection_Id;
626 end if;
627
628 if p_Dataset_Rec.Bsc_Measure_Last_Update_By is null then
629 l_Dataset_Rec.Bsc_Measure_Last_Update_By := fnd_global.USER_ID;
630 end if;
631 if p_Dataset_Rec.Bsc_Measure_Last_Update_Date is null then
632 l_Dataset_Rec.Bsc_Measure_Last_Update_Date := SYSDATE;
633 end if;
634 if p_Dataset_Rec.Bsc_Measure_Last_Update_Login is null then
635 l_Dataset_Rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
636 end if;
637 -- PMD
638
639 /* IF(c_measures_col%ISOPEN) THEN
640 CLOSE c_measures_col;
641 END IF;
642
643 OPEN c_measures_col;
644 FETCH c_measures_col INTO l_Dataset_Rec.Bsc_Measure_Type;
645 IF(c_measures_col%NOTFOUND) THEN
646 l_Dataset_Rec.Bsc_Measure_Type := 0;
647 END IF;
648 CLOSE c_measures_col;*/
649
650
651 UPDATE BSC_SYS_MEASURES
652 -- mdamle 03/12/2003 - PMD - Measure Definer
653 -- Changed set measure_col = l_Dataset_Rec.Bsc_Measure_Short_Name
654 SET measure_col = l_Dataset_Rec.Bsc_Measure_Col
655 ,operation = l_Dataset_Rec.Bsc_Measure_Operation
656 ,type = l_Dataset_Rec.Bsc_Meas_Type
657 ,min_actual_value = l_Dataset_Rec.Bsc_Measure_Min_Act_Value
658 ,max_actual_value = l_Dataset_Rec.Bsc_Measure_Max_Act_Value
659 ,min_budget_value = l_Dataset_Rec.Bsc_Measure_Min_Bud_Value
660 ,max_budget_value = l_Dataset_Rec.Bsc_Measure_Max_Bud_Value
661 ,random_style = l_Dataset_Rec.Bsc_Measure_Random_Style
662 ,s_color_formula = l_Dataset_Rec.Bsc_Measure_color_formula
663 ,source = l_Dataset_Rec.Bsc_Source
664 ,last_updated_by = l_Dataset_Rec.Bsc_Measure_Last_Update_By -- PMD
665 ,last_update_date = l_Dataset_Rec.Bsc_Measure_Last_Update_Date -- PMD
666 ,last_update_login = l_Dataset_Rec.Bsc_Measure_Last_Update_Login -- PMD
667 WHERE measure_id = l_Dataset_Rec.Bsc_Measure_Id;
668
669 if (p_commit = FND_API.G_TRUE) then
670 commit;
671 end if;
672
673 EXCEPTION
674 WHEN FND_API.G_EXC_ERROR THEN
675 IF(c_measures_col%ISOPEN) THEN
676 CLOSE c_measures_col;
677 END IF;
678 IF indicators_cursor%ISOPEN THEN
679 CLOSE indicators_cursor;
680 END IF;
681 x_return_status := FND_API.G_RET_STS_ERROR;
682 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
683 ,p_data => x_msg_data);
684 RAISE;
685 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
686 IF(c_measures_col%ISOPEN) THEN
687 CLOSE c_measures_col;
688 END IF;
689 IF indicators_cursor%ISOPEN THEN
690 CLOSE indicators_cursor;
691 END IF;
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
694 ,p_data => x_msg_data);
695 RAISE;
696 WHEN NO_DATA_FOUND THEN
697 IF(c_measures_col%ISOPEN) THEN
698 CLOSE c_measures_col;
699 END IF;
700 IF indicators_cursor%ISOPEN THEN
701 CLOSE indicators_cursor;
702 END IF;
703 x_return_status := FND_API.G_RET_STS_ERROR;
704 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
705 ,p_data => x_msg_data);
706 RAISE;
707 WHEN OTHERS THEN
708 IF(c_measures_col%ISOPEN) THEN
709 CLOSE c_measures_col;
710 END IF;
711 IF indicators_cursor%ISOPEN THEN
712 CLOSE indicators_cursor;
713 END IF;
714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
715 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
716 ,p_data => x_msg_data);
717 RAISE;
718
719 end Update_Measures;
720
721 /************************************************************************************
722 ************************************************************************************/
723
724 PROCEDURE Delete_Measures(
725 p_commit IN VARCHAR2 := FND_API.G_FALSE
726 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
727 ,x_return_status OUT NOCOPY VARCHAR2
728 ,x_msg_count OUT NOCOPY NUMBER
729 ,x_msg_data OUT NOCOPY VARCHAR2
730 ) IS
731
732 l_Count NUMBER;
733 l_Measure_Col VARCHAR2(320);
734
735 BEGIN
736
737 -- Check that measure is valid
738 IF p_Dataset_Rec.Bsc_Measure_Id IS NOT NULL THEN
739
740 SELECT COUNT(1) INTO l_Count
741 FROM BSC_SYS_MEASURES
742 WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id;
743
744 IF l_count = 0 THEN
745 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_MEAS_ID');
746 FND_MESSAGE.SET_TOKEN('BSC_MEAS', p_Dataset_Rec.Bsc_Measure_Id);
747 FND_MSG_PUB.ADD;
748 RAISE FND_API.G_EXC_ERROR;
749 END IF;
750
751 -- If the Meeasure_Id1 is not null (A+B Formula)
752 IF p_Dataset_Rec.Bsc_Measure_Id2 IS NOT NULL THEN
753 SELECT COUNT(1) INTO l_Count
754 FROM BSC_SYS_MEASURES
755 WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id2;
756
757 IF l_count = 0 THEN
758 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_MEAS_ID');
759 FND_MESSAGE.SET_TOKEN('BSC_MEAS', p_Dataset_Rec.Bsc_Measure_Id2);
760 FND_MSG_PUB.ADD;
761 RAISE FND_API.G_EXC_ERROR;
762 END IF;
763 END IF;
764
765 -- Only delete base measure if there are no datasets referencing it.
766 -- Delete the MEASURE_ID1 Measure
767 SELECT COUNT(DATASET_ID)
768 INTO l_Count
769 FROM BSC_SYS_DATASETS_B
770 WHERE MEASURE_ID1 = p_Dataset_Rec.Bsc_Measure_Id
771 OR MEASURE_ID2 = p_Dataset_Rec.Bsc_Measure_Id;
772
773 IF l_Count = 0 THEN
774 -- mdamle 04/23/2003 - PMD - Measure Definer - Delete db column if not being used by any other measure
775 SELECT MEASURE_COL INTO l_Measure_Col
776 FROM BSC_SYS_MEASURES
777 WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id;
778
779 DELETE FROM BSC_SYS_MEASURES
780 WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id;
781
782 -- mdamle 04/23/2003 - PMD - Measure Definer - Delete db column if not being used by any other measure
783 -- Delete column if no other dataset is using it.
784 SELECT COUNT(1) INTO l_count
785 FROM BSC_SYS_MEASURES
786 WHERE SOURCE = BSC_BIS_MEASURE_PUB.c_BSC
787 AND MEASURE_COL LIKE '%' || l_Measure_Col || '%';
788
789 IF l_Count = 0 THEN
790 SELECT COUNT(1) INTO l_count
791 FROM BSC_DB_MEASURE_COLS_VL
792 WHERE MEASURE_COL = l_Measure_Col;
793 IF l_Count > 0 THEN
794 BSC_DB_MEASURE_COLS_PKG.delete_row(l_Measure_Col);
795 END IF;
796 END IF;
797
798 IF (p_Commit = FND_API.G_TRUE) THEN
799 COMMIT;
800 END IF;
801
802 -- mdamle 04/23/2003 - PMD - Measure Definer - No need to raise error, just don't delete the from bsc_sys_measures
803 END IF;
804
805 -- Delete the Formulae based MEASURE_ID2 if not used in any
806 -- Dataset based formula.
807
808 IF p_Dataset_Rec.Bsc_Measure_Id2 IS NOT NULL THEN
809 SELECT COUNT(DATASET_ID)
810 INTO l_Count
811 FROM BSC_SYS_DATASETS_B
812 WHERE MEASURE_ID1 = p_Dataset_Rec.Bsc_Measure_Id2
813 OR MEASURE_ID2 = p_Dataset_Rec.Bsc_Measure_Id2;
814
815 -- Bug#3781176
816 -- We can have both Meaaure_Id1 and Measure_Id2 same, in that case measure_id1
817 -- would have been delete already and the following code can give no-data-found issue
818 IF ((l_Count = 0) AND (p_Dataset_Rec.Bsc_Measure_Id <> p_Dataset_Rec.Bsc_Measure_Id2)) THEN
819 SELECT MEASURE_COL INTO l_Measure_Col
820 FROM BSC_SYS_MEASURES
821 WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id2;
822
823 DELETE FROM BSC_SYS_MEASURES
824 WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id2;
825
826 -- Delete column if no other dataset is using it.
827 SELECT COUNT(1) INTO l_Count
828 FROM BSC_SYS_MEASURES
829 WHERE SOURCE = BSC_BIS_MEASURE_PUB.c_BSC
830 AND MEASURE_COL LIKE '%' || l_Measure_Col || '%';
831
832 IF l_Count = 0 THEN
833 SELECT COUNT(1) INTO l_count
834 FROM BSC_DB_MEASURE_COLS_VL
835 WHERE MEASURE_COL = l_Measure_Col;
836 IF l_Count > 0 THEN
837 BSC_DB_MEASURE_COLS_PKG.delete_row(l_Measure_Col);
838 END IF;
839 END IF;
840
841 IF (p_Commit = FND_API.G_TRUE) THEN
842 COMMIT;
843 END IF;
844 END IF;
845 END IF;
846
847 ELSE
848 FND_MESSAGE.SET_NAME('BSC','BSC_NO_MEAS_ID_ENTERED');
849 FND_MSG_PUB.ADD;
850 RAISE FND_API.G_EXC_ERROR;
851 END IF;
852
853 EXCEPTION
854 WHEN FND_API.G_EXC_ERROR THEN
855 x_return_status := FND_API.G_RET_STS_ERROR;
856 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
857 ,p_data => x_msg_data);
858 RAISE;
859 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
861 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
862 ,p_data => x_msg_data);
863 RAISE;
864 WHEN NO_DATA_FOUND THEN
865 x_return_status := FND_API.G_RET_STS_ERROR;
866 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
867 ,p_data => x_msg_data);
868 RAISE;
869 WHEN OTHERS THEN
870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
871 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
872 ,p_data => x_msg_data);
873 RAISE;
874
875 END Delete_Measures;
876
877
878 /************************************************************************************
879 ************************************************************************************/
880 /*
881
882 procedure Create_Formats(
883 p_commit IN varchar2 := FND_API.G_FALSE
884 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
885 ,x_return_status OUT NOCOPY varchar2
886 ,x_msg_count OUT NOCOPY number
887 ,x_msg_data OUT NOCOPY varchar2
888 ) is
889
890 begin
891
892 if (p_commit = FND_API.G_TRUE) then
893 commit;
894 end if;
895
896 EXCEPTION
897 WHEN FND_API.G_EXC_ERROR THEN
898 x_return_status := FND_API.G_RET_STS_ERROR;
899 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
900 ,p_data => x_msg_data);
901 raise;
902 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
904 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
905 ,p_data => x_msg_data);
906 raise;
907 WHEN NO_DATA_FOUND THEN
908 x_return_status := FND_API.G_RET_STS_ERROR;
909 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
910 ,p_data => x_msg_data);
911 raise;
912 WHEN OTHERS THEN
913 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
914 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
915 ,p_data => x_msg_data);
916 raise;
917
918 end Create_Formats;
919 */
920
921 /************************************************************************************
922 ************************************************************************************/
923 /*
924
925 procedure Delete_Formats(
926 p_commit IN varchar2 := FND_API.G_FALSE
927 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
928 ,x_return_status OUT NOCOPY varchar2
929 ,x_msg_count OUT NOCOPY number
930 ,x_msg_data OUT NOCOPY varchar2
931 ) is
932
933 begin
934
935 if (p_commit = FND_API.G_TRUE) then
936 commit;
937 end if;
938
939 EXCEPTION
940 WHEN FND_API.G_EXC_ERROR THEN
941 x_return_status := FND_API.G_RET_STS_ERROR;
942 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
943 ,p_data => x_msg_data);
944 raise;
945 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
947 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
948 ,p_data => x_msg_data);
949 raise;
950 WHEN NO_DATA_FOUND THEN
951 x_return_status := FND_API.G_RET_STS_ERROR;
952 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
953 ,p_data => x_msg_data);
954 raise;
955 WHEN OTHERS THEN
956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
957 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
958 ,p_data => x_msg_data);
959 raise;
960
961 end Delete_Formats;
962 */
963
964 /************************************************************************************
965 ************************************************************************************/
966
967 --: This procedure creates a dataset for the given measure.
968 --: This procedure is part of the Data Set API.
969
970 procedure Create_Dataset(
971 p_commit IN varchar2 := FND_API.G_FALSE
972 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
973 ,x_return_status OUT NOCOPY varchar2
974 ,x_msg_count OUT NOCOPY number
975 ,x_msg_data OUT NOCOPY varchar2
976 ) is
977
978 l_count number;
979 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
980
981 begin
982 l_Dataset_Rec := p_Dataset_Rec;
983
984 -- Set who columns accordingly
985 if l_Dataset_Rec.Bsc_Dataset_Created_By is null then
986 l_Dataset_Rec.Bsc_Dataset_Created_By := fnd_global.USER_ID;
987 end if;
988 if l_Dataset_Rec.Bsc_Dataset_Last_Update_By is null then
989 l_Dataset_Rec.Bsc_Dataset_Last_Update_By := fnd_global.USER_ID;
990 end if;
991 if l_Dataset_Rec.Bsc_Dataset_Last_Update_Login is null then
992 l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := fnd_global.LOGIN_ID;
993 end if;
994 if l_Dataset_Rec.Bsc_Dataset_Creation_Date is null then
995 l_Dataset_Rec.Bsc_Dataset_Creation_Date := sysdate;
996 end if;
997 if l_Dataset_Rec.Bsc_Dataset_Last_Update_Date is null then
998 l_Dataset_Rec.Bsc_Dataset_Last_Update_Date := sysdate;
999 end if;
1000
1001 -- Verify that dataset does not exist.
1002 select count(1)
1003 into l_count
1004 from BSC_SYS_DATASETS_B
1005 where dataset_id = l_Dataset_Rec.Bsc_Dataset_Id;
1006
1007 -- If dataset does not exist then create it, else raise an error.
1008
1009 if l_count = 0 then
1010
1011 -- Insert the pertaining values into table bsc_sys_datasets_b.
1012 insert into BSC_SYS_DATASETS_B( dataset_id
1013 ,measure_id1
1014 ,operation
1015 ,measure_id2
1016 ,format_id
1017 ,color_method
1018 ,projection_flag
1019 ,edw_flag
1020 ,autoscale_flag
1021 ,source
1022 ,created_by -- PMD
1023 ,creation_date -- PMD
1024 ,last_updated_by -- PMD
1025 ,last_update_date -- PMD
1026 ,last_update_login) -- PMD
1027 values( l_Dataset_Rec.Bsc_Dataset_Id
1028 ,l_Dataset_Rec.Bsc_Measure_Id
1029 -- mdamle 03/12/2003 - PMD - Measure Definer
1030 -- Changed from Measure_operation to Dataset_Operation
1031 ,l_Dataset_Rec.Bsc_Dataset_operation
1032 ,l_Dataset_Rec.Bsc_Measure_Id2
1033 ,l_Dataset_Rec.Bsc_Dataset_Format_Id
1034 ,l_Dataset_Rec.Bsc_Dataset_Color_Method
1035 ,l_Dataset_Rec.Bsc_Dataset_Projection_Flag
1036 ,0
1037 ,l_Dataset_Rec.Bsc_Dataset_Autoscale_Flag
1038 ,l_Dataset_Rec.Bsc_Source
1039 ,l_Dataset_Rec.Bsc_Dataset_Created_By -- PMD
1040 ,l_Dataset_Rec.Bsc_Dataset_Creation_Date -- PMD
1041 ,l_Dataset_Rec.Bsc_Dataset_Last_Update_By -- PMD
1042 ,l_Dataset_Rec.Bsc_Dataset_Last_Update_Date -- PMD
1043 ,l_Dataset_Rec.Bsc_Dataset_Last_Update_Login);-- PMD
1044
1045
1046 -- Insert the pertaining values into table bsc_sys_datasets_tl.
1047 insert into BSC_SYS_DATASETS_TL( dataset_id
1048 ,language
1049 ,source_lang
1050 ,name
1051 ,help
1052 ,y_axis_title)
1053 select l_Dataset_Rec.Bsc_Dataset_Id
1054 ,L.LANGUAGE_CODE
1055 ,userenv('LANG')
1056 ,l_Dataset_Rec.Bsc_Dataset_Name
1057 ,l_Dataset_Rec.Bsc_Dataset_Help
1058 ,l_Dataset_Rec.Bsc_y_axis_title
1059 from FND_LANGUAGES L
1060 where L.INSTALLED_FLAG in ('I', 'B')
1061 and not exists
1062 (select NULL
1063 from BSC_SYS_DATASETS_TL T
1064 where T.dataset_id = l_Dataset_Rec.Bsc_Dataset_Id
1065 and T.LANGUAGE = L.LANGUAGE_CODE);
1066
1067 if (p_commit = FND_API.G_TRUE) then
1068 commit;
1069 end if;
1070
1071 else
1072 FND_MESSAGE.SET_NAME('BSC','BSC_DSET_ID_EXISTS');
1073 FND_MESSAGE.SET_TOKEN('BSC_DATASET', l_Dataset_Rec.Bsc_Dataset_Id);
1074 FND_MSG_PUB.ADD;
1075 RAISE FND_API.G_EXC_ERROR;
1076 end if;
1077
1078
1079 EXCEPTION
1080 WHEN FND_API.G_EXC_ERROR THEN
1081 x_return_status := FND_API.G_RET_STS_ERROR;
1082 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1083 ,p_data => x_msg_data);
1084 raise;
1085 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1086 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1087 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1088 ,p_data => x_msg_data);
1089 raise;
1090 WHEN NO_DATA_FOUND THEN
1091 x_return_status := FND_API.G_RET_STS_ERROR;
1092 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1093 ,p_data => x_msg_data);
1094 raise;
1095 WHEN OTHERS THEN
1096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1097 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1098 ,p_data => x_msg_data);
1099 raise;
1100
1101 end Create_Dataset;
1102
1103 /************************************************************************************
1104 ************************************************************************************/
1105
1106 procedure Retrieve_Dataset(
1107 p_commit IN varchar2 := FND_API.G_FALSE
1108 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1109 ,x_Dataset_Rec IN OUT NOCOPY BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1110 ,x_return_status OUT NOCOPY varchar2
1111 ,x_msg_count OUT NOCOPY number
1112 ,x_msg_data OUT NOCOPY varchar2
1113 ) is
1114
1115 begin
1116
1117 select distinct measure_id1
1118 ,operation
1119 ,measure_id2
1120 ,format_id
1121 ,color_method
1122 ,projection_flag
1123 ,autoscale_flag
1124 ,name
1125 ,help
1126 ,y_axis_title
1127 ,source
1128 ,created_by -- PMD
1129 ,creation_date -- PMD
1130 ,last_updated_by -- PMD
1131 ,last_update_date -- PMD
1132 ,last_update_login -- PMD
1133 into x_Dataset_Rec.Bsc_Measure_Id
1134 -- mdamle 03/12/2003 - PMD - Measure Definer
1135 -- Changed from Measure_operation to Dataset_Operation
1136 ,x_Dataset_Rec.Bsc_Dataset_Operation
1137 ,x_Dataset_Rec.Bsc_Measure_Id2
1138 ,x_Dataset_Rec.Bsc_Dataset_Format_Id
1139 ,x_Dataset_Rec.Bsc_Dataset_Color_Method
1140 ,x_Dataset_Rec.Bsc_Dataset_Projection_Flag
1141 ,x_Dataset_Rec.Bsc_Dataset_Autoscale_Flag
1142 ,x_Dataset_Rec.Bsc_Dataset_Name
1143 ,x_Dataset_Rec.Bsc_Dataset_Help
1144 ,x_Dataset_Rec.Bsc_y_axis_title
1145 ,x_Dataset_Rec.Bsc_Source
1146 ,x_Dataset_Rec.Bsc_Dataset_Created_By -- PMD
1147 ,x_Dataset_Rec.Bsc_Dataset_Creation_Date -- PMD
1148 ,x_Dataset_Rec.Bsc_Dataset_Last_Update_By -- PMD
1149 ,x_Dataset_Rec.Bsc_Dataset_Last_Update_Date -- PMD
1150 ,x_Dataset_Rec.Bsc_Dataset_Last_Update_Login -- PMD
1151
1152 from BSC_SYS_DATASETS_VL
1153 where dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
1154
1155 if (p_commit = FND_API.G_TRUE) then
1156 commit;
1157 end if;
1158
1159 EXCEPTION
1160 WHEN FND_API.G_EXC_ERROR THEN
1161 x_return_status := FND_API.G_RET_STS_ERROR;
1162 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1163 ,p_data => x_msg_data);
1164 raise;
1165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1167 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1168 ,p_data => x_msg_data);
1169 raise;
1170 WHEN NO_DATA_FOUND THEN
1171 x_return_status := FND_API.G_RET_STS_ERROR;
1172 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1173 ,p_data => x_msg_data);
1174 raise;
1175 WHEN OTHERS THEN
1176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1177 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1178 ,p_data => x_msg_data);
1179 raise;
1180
1181 end Retrieve_Dataset;
1182
1183 /************************************************************************************
1184 ************************************************************************************/
1185
1186 procedure Update_Dataset(
1187 p_commit IN varchar2 := FND_API.G_FALSE
1188 , p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1189 ,x_return_status OUT NOCOPY varchar2
1190 ,x_msg_count OUT NOCOPY number
1191 ,x_msg_data OUT NOCOPY varchar2
1192 ) is
1193
1194 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
1195
1196 l_count number;
1197 l_Old_Format_id number;
1198 l_indicator_table BSC_NUM_LIST;
1199
1200 CURSOR c_Default_Measure_In_Indicator IS
1201 SELECT DISTINCT INDICATOR
1202 FROM BSC_KPI_ANALYSIS_MEASURES_b
1203 WHERE DATASET_ID =p_Dataset_Rec.Bsc_Dataset_Id;
1204
1205
1206
1207 CURSOR indicators_cursor IS
1208 SELECT b.indicator
1209 FROM bsc_kpi_analysis_measures_b b,
1210 bsc_kpis_b a
1211 WHERE a.indicator =b.indicator
1212 AND a.config_type <>3
1213 AND b.dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
1214
1215 l_kpi_flag number;
1216 l_color_Method_flag boolean;
1217 l_kpi_measure_id BSC_KPI_ANALYSIS_MEASURES_B.KPI_MEASURE_ID%TYPE;
1218
1219 begin
1220
1221 l_color_Method_flag := false;
1222
1223 -- Check that valid dataset id was entered.
1224 if p_Dataset_Rec.Bsc_Dataset_Id is not null then
1225 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_DATASETS_B'
1226 ,'dataset_id'
1227 ,p_Dataset_Rec.Bsc_Dataset_Id);
1228 if l_count = 0 then
1229 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DTSET_ID');
1230 FND_MESSAGE.SET_TOKEN('BSC_DATASET', p_Dataset_Rec.Bsc_Dataset_Id);
1231 FND_MSG_PUB.ADD;
1232 RAISE FND_API.G_EXC_ERROR;
1233 end if;
1234 else
1235 FND_MESSAGE.SET_NAME('BSC','BSC_NO_DTSET_ID_ENTERED');
1236 FND_MESSAGE.SET_TOKEN('BSC_DATASET', p_Dataset_Rec.Bsc_Dataset_Id);
1237 FND_MSG_PUB.ADD;
1238 RAISE FND_API.G_EXC_ERROR;
1239 end if;
1240
1241
1242 /* commented, apparently not needed.
1243 -- Check that valid measure id was entered.
1244 if p_Dataset_Rec.Bsc_Measure_Id is not null then
1245 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_MEASURES'
1246 ,'measure_id'
1247 ,p_Dataset_Rec.Bsc_Measure_Id);
1248 if l_count = 0 then
1249 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_MEAS_ID');
1250 FND_MESSAGE.SET_TOKEN('BSC_MEAS', p_Dataset_Rec.Bsc_Measure_Id);
1251 FND_MSG_PUB.ADD;
1252 RAISE FND_API.G_EXC_ERROR;
1253 end if;
1254 else
1255 FND_MESSAGE.SET_NAME('BSC','BSC_NO_MEAS_ID_ENTERED');
1256 FND_MESSAGE.SET_TOKEN('BSC_MEAS', p_Dataset_Rec.Bsc_Measure_Id);
1257 FND_MSG_PUB.ADD;
1258 RAISE FND_API.G_EXC_ERROR;
1259 end if;
1260
1261 -- Check that valid 2nd measure id was entered.
1262 if p_Dataset_Rec.Bsc_Measure_Id2 is not null and
1263 p_Dataset_Rec.Bsc_Measure_Id2 <> 0 then
1264 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_MEASURES'
1265 ,'measure_id'
1266 ,p_Dataset_Rec.Bsc_Measure_Id2);
1267 if l_count = 0 then
1268 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_MEAS_ID');
1269 FND_MESSAGE.SET_TOKEN('BSC_MEAS', p_Dataset_Rec.Bsc_Measure_Id);
1270 FND_MSG_PUB.ADD;
1271 RAISE FND_API.G_EXC_ERROR;
1272 end if;
1273
1274 end if;
1275 */
1276
1277
1278 -- Not all values will be passed. We need to make sure values not passed are not
1279 -- changed by procedure, therefore we get what is there before we do any updates.
1280 Retrieve_Dataset( p_commit
1281 ,p_Dataset_Rec
1282 ,l_Dataset_Rec
1283 ,x_return_status
1284 ,x_msg_count
1285 ,x_msg_data);
1286
1287 -- mdamle 04/23/2003 - PMD - Measure Definer - Update flag in KPI for specific updates in the dataset
1288 -- fix bug 4185504 - (ppandey)Reverting this bug change for 6.1
1289 if p_Dataset_Rec.Bsc_Dataset_Color_Method is not null then
1290 if l_Dataset_Rec.Bsc_Dataset_Color_Method <> p_Dataset_Rec.Bsc_Dataset_Color_Method then
1291 l_color_Method_flag := true;
1292 end if;
1293 l_Dataset_Rec.Bsc_Dataset_Color_Method := p_Dataset_Rec.Bsc_Dataset_Color_Method;
1294 end if;
1295
1296 if ( p_Dataset_Rec.Bsc_Measure_Id <> l_Dataset_Rec.Bsc_Measure_Id or
1297 p_Dataset_Rec.Bsc_Measure_Id2 <> l_Dataset_Rec.Bsc_Measure_Id2) then
1298 l_kpi_flag := BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure;
1299 end if;
1300
1301 -- update LOCAL language ,source language and level Id values with PASSED values.
1302 l_Dataset_Rec.Bsc_Language := p_Dataset_Rec.Bsc_Language;
1303
1304 --sawu: update WHO column info with PASSED values
1305 l_Dataset_Rec.Bsc_Dataset_Last_Update_By := p_Dataset_Rec.Bsc_Dataset_Last_Update_By;
1306 l_Dataset_Rec.Bsc_Dataset_Last_Update_Date := p_Dataset_Rec.Bsc_Dataset_Last_Update_Date;
1307 l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := p_Dataset_Rec.Bsc_Dataset_Last_Update_Login;
1308
1309 --Fix for the bug 2883880
1310
1311 IF (p_Dataset_Rec.Bsc_Source_Language IS NULL)THEN
1312 l_Dataset_Rec.Bsc_Source_Language := USERENV('LANG');
1313 ELSE
1314 l_Dataset_Rec.Bsc_Source_Language := p_Dataset_Rec.Bsc_Source_Language;
1315 END IF;
1316 -- adrao. since we have other modules using this API, we cannot allow null Measure_Id
1317 l_Dataset_Rec.Bsc_Dataset_Id := p_Dataset_Rec.Bsc_Dataset_Id;
1318 --l_Dataset_Rec.Bsc_Measure_Id := p_Dataset_Rec.Bsc_Measure_Id;
1319 --if l_color_Method_flag = false then
1320 l_Dataset_Rec.Bsc_Measure_Id2 := p_Dataset_Rec.Bsc_Measure_Id2;
1321 l_Dataset_Rec.Bsc_Dataset_Operation := p_Dataset_Rec.Bsc_Dataset_Operation;
1322 l_Dataset_Rec.Bsc_Y_Axis_Title := p_Dataset_Rec.Bsc_Y_Axis_Title;
1323 --end if;
1324 -- mdamle 04/23/2003 - PMD - Measure Definer
1325 -- Checking for not null will not work if the user has actually tried to blank the value in a Null allowed column
1326 -- Copy PASSED Record values into LOCAL Record values for the PASSED Record values
1327 -- which are NOT NULL.
1328 -- adrao fixed bug #3123509
1329 if p_Dataset_Rec.Bsc_Measure_Id is not null then
1330 l_Dataset_Rec.Bsc_Measure_Id := p_Dataset_Rec.Bsc_Measure_Id;
1331 end if;
1332 /* if p_Dataset_Rec.Bsc_Measure_Id2 is not null then
1333
1334 l_Dataset_Rec.Bsc_Measure_Id2 := p_Dataset_Rec.Bsc_Measure_Id2;
1335 end if;
1336 if p_Dataset_Rec.Bsc_Dataset_Operation is not null then
1337 l_Dataset_Rec.Bsc_Dataset_Operation := p_Dataset_Rec.Bsc_Dataset_Operation;
1338 end if;
1339 */
1340
1341 if p_Dataset_Rec.Bsc_Dataset_Format_Id is not null then
1342
1343 l_Old_Format_id := l_Dataset_Rec.Bsc_Dataset_Format_Id;
1344 l_Dataset_Rec.Bsc_Dataset_Format_Id := p_Dataset_Rec.Bsc_Dataset_Format_Id;
1345 end if;
1346 if p_Dataset_Rec.Bsc_Dataset_Projection_Flag is not null then
1347 l_Dataset_Rec.Bsc_Dataset_Projection_Flag := p_Dataset_Rec.Bsc_Dataset_Projection_Flag;
1348 end if;
1349 if p_Dataset_Rec.Bsc_Dataset_Autoscale_Flag is not null then
1350 l_Dataset_Rec.Bsc_Dataset_Autoscale_Flag := p_Dataset_Rec.Bsc_Dataset_Autoscale_Flag;
1351 end if;
1352 if p_Dataset_Rec.Bsc_Dataset_Name is not null then
1353 l_Dataset_Rec.Bsc_Dataset_Name := p_Dataset_Rec.Bsc_Dataset_Name;
1354 end if;
1355 if p_Dataset_Rec.Bsc_Dataset_Help is not null then
1356 l_Dataset_Rec.Bsc_Dataset_Help := p_Dataset_Rec.Bsc_Dataset_Help;
1357 end if;
1358 if p_Dataset_Rec.Bsc_Measure_Long_Name is not null then
1359 l_Dataset_Rec.Bsc_Measure_Long_Name := p_Dataset_Rec.Bsc_Measure_Long_Name;
1360 end if;
1361 -- PMD
1362 if p_Dataset_Rec.Bsc_Dataset_Last_Update_By is null then
1363 l_Dataset_Rec.Bsc_Dataset_Last_Update_By := fnd_global.USER_ID;
1364 end if;
1365 if p_Dataset_Rec.Bsc_Dataset_Last_Update_Date is null then
1366 l_Dataset_Rec.Bsc_Dataset_Last_Update_Date := SYSDATE;
1367 end if;
1368 if p_Dataset_Rec.Bsc_Dataset_Last_Update_Login is null then
1369 l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := fnd_global.LOGIN_ID;
1370 end if;
1371
1372 IF (p_Dataset_Rec.Bsc_Source = BSC_BIS_MEASURE_PUB.c_PMF AND l_Dataset_Rec.Bsc_Source = BSC_BIS_MEASURE_PUB.c_BSC) THEN
1373 l_Dataset_Rec.Bsc_Source := p_Dataset_Rec.Bsc_Source;
1374 END IF;
1375
1376 -- PMD
1377 -- mdamle 03/12/2003 - PMD - Measure Definer
1378 -- Changed from Measure_operation to Dataset_Operation
1379 -- Added Measure_id1 and Measure_Id2
1380 update BSC_SYS_DATASETS_B
1381 set operation = l_Dataset_Rec.Bsc_Dataset_Operation
1382 ,format_id = l_Dataset_Rec.Bsc_Dataset_Format_Id
1383 ,color_method = l_Dataset_Rec.Bsc_Dataset_Color_Method
1384 ,projection_flag = l_Dataset_Rec.Bsc_Dataset_Projection_Flag
1385 ,autoscale_flag = l_Dataset_Rec.Bsc_Dataset_Autoscale_Flag
1386 ,measure_id1 = l_Dataset_Rec.Bsc_Measure_Id
1387 ,measure_id2 = l_Dataset_Rec.Bsc_Measure_Id2
1388 ,source = l_Dataset_Rec.Bsc_Source
1389 ,last_updated_by = l_Dataset_Rec.Bsc_Dataset_Last_Update_By -- PMD
1390 ,last_update_date = l_Dataset_Rec.Bsc_Dataset_Last_Update_Date -- PMD
1391 ,last_update_login = l_Dataset_Rec.Bsc_Dataset_Last_Update_Login -- PMD
1392 where dataset_id = l_Dataset_Rec.Bsc_Dataset_Id;
1393
1394 ----Fix for the bug 2883880
1395
1396 update BSC_SYS_DATASETS_TL
1397 set name = l_Dataset_Rec.Bsc_Dataset_Name
1398 ,help = l_Dataset_Rec.Bsc_Dataset_Help
1399 ,y_axis_title = l_Dataset_Rec.Bsc_y_axis_title
1400 ,source_lang = l_Dataset_Rec.Bsc_Source_Language
1401 where dataset_id = l_Dataset_Rec.Bsc_Dataset_Id
1402 and l_Dataset_Rec.Bsc_Source_Language in (LANGUAGE, SOURCE_LANG);
1403
1404 -- Following logic brings code dependency from BSC
1405 -- But this is acceptable as with R12 BIS/BSC will always go together.
1406 IF (l_kpi_flag IS NOT NULL OR l_color_Method_flag) THEN
1407 OPEN indicators_cursor;
1408 FETCH indicators_cursor BULK COLLECT INTO l_indicator_table;
1409 IF indicators_cursor%ISOPEN THEN CLOSE indicators_cursor; END IF;
1410
1411 FOR i IN 1..l_indicator_table.COUNT LOOP
1412 IF (l_kpi_flag IS NOT NULL) THEN
1413 BSC_DESIGNER_PVT.ActionFlag_Change(l_indicator_table(i), l_kpi_flag);
1414 END IF;
1415 IF (l_color_Method_flag) THEN
1416 SELECT kpi_measure_id
1417 INTO l_kpi_measure_id
1418 FROM bsc_kpi_analysis_measures_b
1419 WHERE indicator = l_indicator_table(i)
1420 AND dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
1421
1422 BSC_COLOR_RANGES_PUB.Delete_Color_Prop_Ranges (p_objective_id => l_indicator_table(i)
1423 ,p_kpi_measure_id => l_kpi_measure_id
1424 ,p_cascade_shared => TRUE
1425 ,x_return_status => x_return_status
1426 ,x_msg_count => x_msg_count
1427 ,x_msg_data => x_msg_data);
1428
1429 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1430 RAISE FND_API.G_EXC_ERROR;
1431 END IF;
1432
1433 BSC_COLOR_RANGES_PUB.Create_Def_Color_Prop_Ranges(p_objective_id => l_indicator_table(i)
1434 ,p_kpi_measure_id => l_kpi_measure_id
1435 ,p_cascade_shared => TRUE
1436 ,x_return_status => x_return_status
1437 ,x_msg_count => x_msg_count
1438 ,x_msg_data => x_msg_data);
1439
1440 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1441 RAISE FND_API.G_EXC_ERROR;
1442 END IF;
1443 END IF;
1444 END LOOP;
1445 END IF;
1446
1447 if (p_commit = FND_API.G_TRUE) then
1448 commit;
1449 end if;
1450
1451
1452 -- visuri fixed bug 3681116
1453 -- Update of Numeric Format of measure will change the default numeric format of all Indicators for which
1454 -- that measure is a default measure. This update will take place in BSC_KPI_DEFAULTS_B table
1455
1456 if ( l_Old_Format_id <> p_Dataset_Rec.Bsc_Dataset_Format_Id and p_Dataset_Rec.Bsc_Dataset_Format_Id is not null ) then
1457
1458 FOR cd IN c_Default_Measure_In_Indicator LOOP
1459 BSC_DESIGNER_PVT.Deflt_RefreshKpi(cd.INDICATOR);
1460
1461 END LOOP;
1462
1463 end if;
1464
1465
1466 EXCEPTION
1467 WHEN FND_API.G_EXC_ERROR THEN
1468 x_return_status := FND_API.G_RET_STS_ERROR;
1469 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1470 ,p_data => x_msg_data);
1471 raise;
1472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1474 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1475 ,p_data => x_msg_data);
1476 raise;
1477 WHEN NO_DATA_FOUND THEN
1478 x_return_status := FND_API.G_RET_STS_ERROR;
1479 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1480 ,p_data => x_msg_data);
1481 raise;
1482 WHEN OTHERS THEN
1483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1484 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1485 ,p_data => x_msg_data);
1486 raise;
1487
1488 end Update_Dataset;
1489
1490 /************************************************************************************
1491 ************************************************************************************/
1492
1493 procedure Delete_Dataset(
1494 p_commit IN varchar2 := FND_API.G_FALSE
1495 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1496 ,x_return_status OUT NOCOPY varchar2
1497 ,x_msg_count OUT NOCOPY number
1498 ,x_msg_data OUT NOCOPY varchar2
1499 ) is
1500
1501 cursor indicators_cursor IS
1502 select distinct k.name
1503 from bsc_kpi_analysis_measures_vl am, bsc_kpis_vl k
1504 where am.indicator = k.indicator
1505 and dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
1506
1507 l_short_name bis_indicators.short_name%TYPE;
1508 CURSOR c_short_name(l_dataset_id NUMBER)
1509 IS
1510 SELECT
1511 short_name
1512 FROM
1513 bis_indicators
1514 WHERE dataset_id = l_dataset_id;
1515
1516 l_indicators varchar2(32000);
1517
1518 l_count number;
1519
1520 begin
1521 x_return_status := FND_API.G_RET_STS_SUCCESS;
1522 -- Check that a valid dataset id was entered.
1523 if p_Dataset_Rec.Bsc_Dataset_Id is not null then
1524 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_DATASETS_B'
1525 ,'dataset_id'
1526 ,p_Dataset_Rec.Bsc_Dataset_Id);
1527 if l_count = 0 then
1528 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DTSET_ID');
1529 FND_MESSAGE.SET_TOKEN('BSC_DATASET', p_Dataset_Rec.Bsc_Dataset_Id);
1530 FND_MSG_PUB.ADD;
1531 RAISE FND_API.G_EXC_ERROR;
1532 end if;
1533
1534 -- mdamle 04/23/2003 - PMD - Measure Definer - Check if assigned to indicator
1535 for cr in indicators_cursor loop
1536 if (l_indicators is null) then
1537 l_indicators := cr.name;
1538 else
1539 l_indicators := l_indicators || ', ' || cr.name;
1540 end if;
1541 end loop;
1542
1543 if indicators_cursor%ISOPEN THEN
1544 CLOSE indicators_cursor;
1545 end if;
1546
1547 if l_indicators is not null then
1548 FND_MESSAGE.SET_NAME('BSC','BSC_DELETE_MEASURE_IND_ERR_TXT');
1549 FND_MESSAGE.SET_TOKEN('BSC_INDICATORS', l_indicators);
1550 FND_MSG_PUB.ADD;
1551 RAISE FND_API.G_EXC_ERROR;
1552 end if;
1553 else
1554 FND_MESSAGE.SET_NAME('BSC','BSC_NO_DTSET_ID_ENTERED');
1555 FND_MESSAGE.SET_TOKEN('BSC_DATASET', p_Dataset_Rec.Bsc_Dataset_Id);
1556 FND_MSG_PUB.ADD;
1557 RAISE FND_API.G_EXC_ERROR;
1558 end if;
1559
1560 OPEN c_short_name(p_Dataset_Rec.Bsc_Dataset_Id);
1561 FETCH c_short_name INTO l_short_name;
1562 CLOSE c_short_name;
1563
1564 DELETE FROM bis_custom_cause_effect_rels
1565 WHERE cause_short_name = l_short_name OR effect_short_name = l_short_name;
1566
1567 -- mdamle 04/23/2003 - PMD - Measure Definer - Delete Cause and Effect relationships
1568 BSC_CAUSE_EFFECT_REL_PUB.Delete_All_Cause_Effect_Rels(
1569 p_commit => p_commit
1570 ,p_indicator => p_Dataset_Rec.Bsc_Dataset_Id
1571 ,p_level => BSC_BIS_MEASURE_PUB.c_LEVEL
1572 ,x_return_status => x_return_status
1573 ,x_msg_count => x_msg_count
1574 ,x_msg_data => x_msg_data);
1575
1576 delete from BSC_SYS_DATASETS_B
1577 where dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
1578
1579 delete from BSC_SYS_DATASETS_TL
1580 where dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
1581
1582 if (p_commit = FND_API.G_TRUE) then
1583 commit;
1584 end if;
1585
1586 EXCEPTION
1587 WHEN FND_API.G_EXC_ERROR THEN
1588 x_return_status := FND_API.G_RET_STS_ERROR;
1589 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE
1590 ,p_count => x_msg_count
1591 ,p_data => x_msg_data);
1592 raise;
1593 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1595 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE
1596 ,p_count => x_msg_count
1597 ,p_data => x_msg_data);
1598 raise;
1599 WHEN NO_DATA_FOUND THEN
1600 x_return_status := FND_API.G_RET_STS_ERROR;
1601 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE
1602 ,p_count => x_msg_count
1603 ,p_data => x_msg_data);
1604 raise;
1605 WHEN OTHERS THEN
1606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1607 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE
1608 ,p_count => x_msg_count
1609 ,p_data => x_msg_data);
1610 raise;
1611
1612 end Delete_Dataset;
1613
1614 /************************************************************************************
1615 ************************************************************************************/
1616
1617 --: This procedure creates the necessary values for the disabled calc id
1618 --: for the given dimension.
1619 --: This procedure is part of the Data Set API.
1620
1621 procedure Create_Dataset_Calc(
1622 p_commit IN varchar2 := FND_API.G_FALSE
1623 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1624 ,x_return_status OUT NOCOPY varchar2
1625 ,x_msg_count OUT NOCOPY number
1626 ,x_msg_data OUT NOCOPY varchar2
1627 ) is
1628
1629 l_count number;
1630
1631 begin
1632
1633
1634 -- Check that valid dataset id was entered.
1635 if p_Dataset_Rec.Bsc_Dataset_Id is not null then
1636 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_DATASETS_B'
1637 ,'dataset_id'
1638 ,p_Dataset_Rec.Bsc_Dataset_Id);
1639 if l_count = 0 then
1640 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DTSET_ID');
1641 FND_MESSAGE.SET_TOKEN('BSC_DATASET', p_Dataset_Rec.Bsc_Dataset_Id);
1642 FND_MSG_PUB.ADD;
1643 RAISE FND_API.G_EXC_ERROR;
1644 else -- Check that combination dataset id and calc id does not exist.
1645 select count(1)
1646 into l_count
1647 from BSC_SYS_DATASET_CALC
1648 where dataset_id = p_Dataset_Rec.Bsc_Dataset_Id
1649 and disabled_calc_id = p_Dataset_Rec.Bsc_Disabled_Calc_Id;
1650 if l_count <> 0 then
1651 FND_MESSAGE.SET_NAME('BSC','BSC_DTSET_CALC_EXISTSD');
1652 FND_MESSAGE.SET_TOKEN('BSC_DATASET', p_Dataset_Rec.Bsc_Disabled_Calc_Id);
1653 FND_MSG_PUB.ADD;
1654 RAISE FND_API.G_EXC_ERROR;
1655 end if;
1656
1657 end if;
1658
1659 else
1660 FND_MESSAGE.SET_NAME('BSC','BSC_NO_DTSET_ID_ENTERED');
1661 FND_MESSAGE.SET_TOKEN('BSC_DATASET', p_Dataset_Rec.Bsc_Dataset_Id);
1662 FND_MSG_PUB.ADD;
1663 RAISE FND_API.G_EXC_ERROR;
1664 end if;
1665
1666 -- Insert pertaining values into table bsc_sys_dataset_calc.
1667 insert into BSC_SYS_DATASET_CALC( dataset_id
1668 ,disabled_calc_id)
1669 values( p_Dataset_Rec.Bsc_Dataset_Id
1670 ,p_Dataset_Rec.Bsc_Disabled_Calc_Id);
1671
1672 if (p_commit = FND_API.G_TRUE) then
1673 commit;
1674 end if;
1675
1676 EXCEPTION
1677 WHEN FND_API.G_EXC_ERROR THEN
1678 x_return_status := FND_API.G_RET_STS_ERROR;
1679 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1680 ,p_data => x_msg_data);
1681 raise;
1682 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1684 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1685 ,p_data => x_msg_data);
1686 raise;
1687 WHEN NO_DATA_FOUND THEN
1688 x_return_status := FND_API.G_RET_STS_ERROR;
1689 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1690 ,p_data => x_msg_data);
1691 raise;
1692 WHEN OTHERS THEN
1693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1694 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1695 ,p_data => x_msg_data);
1696 raise;
1697
1698 end Create_Dataset_Calc;
1699
1700 /************************************************************************************
1701 ************************************************************************************/
1702
1703 procedure Retrieve_Dataset_Calc(
1704 p_commit IN varchar2 := FND_API.G_FALSE
1705 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1706 ,x_Dataset_Rec IN OUT NOCOPY BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1707 ,x_return_status OUT NOCOPY varchar2
1708 ,x_msg_count OUT NOCOPY number
1709 ,x_msg_data OUT NOCOPY varchar2
1710 ) is
1711
1712 begin
1713
1714 select distinct disabled_calc_id
1715 into x_Dataset_Rec.Bsc_Disabled_Calc_Id
1716 from BSC_SYS_DATASET_CALC
1717 where dataset_id = x_Dataset_Rec.Bsc_Dataset_Id;
1718
1719 if (p_commit = FND_API.G_TRUE) then
1720 commit;
1721 end if;
1722
1723 EXCEPTION
1724 WHEN FND_API.G_EXC_ERROR THEN
1725 x_return_status := FND_API.G_RET_STS_ERROR;
1726 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1727 ,p_data => x_msg_data);
1728 raise;
1729 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1731 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1732 ,p_data => x_msg_data);
1733 raise;
1734 WHEN NO_DATA_FOUND THEN
1735 x_return_status := FND_API.G_RET_STS_ERROR;
1736 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1737 ,p_data => x_msg_data);
1738 raise;
1739 WHEN OTHERS THEN
1740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1741 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1742 ,p_data => x_msg_data);
1743 raise;
1744
1745 end Retrieve_Dataset_Calc;
1746
1747 /************************************************************************************
1748 ************************************************************************************/
1749
1750 procedure Update_Dataset_Calc(
1751 p_commit IN varchar2 := FND_API.G_FALSE
1752 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1753 ,x_return_status OUT NOCOPY varchar2
1754 ,x_msg_count OUT NOCOPY number
1755 ,x_msg_data OUT NOCOPY varchar2
1756 ) is
1757
1758 l_count number;
1759
1760 begin
1761
1762 -- Check that valid dataset id was entered.
1763 if p_Dataset_Rec.Bsc_Dataset_Id is not null then
1764 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_DATASETS_B'
1765 ,'dataset_id'
1766 ,p_Dataset_Rec.Bsc_Dataset_Id);
1767 if l_count = 0 then
1768 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DTSET_ID');
1769 FND_MESSAGE.SET_TOKEN('BSC_DATASET', p_Dataset_Rec.Bsc_Dataset_Id);
1770 FND_MSG_PUB.ADD;
1771 RAISE FND_API.G_EXC_ERROR;
1772 end if;
1773 else
1774 FND_MESSAGE.SET_NAME('BSC','BSC_NO_DTSET_ID_ENTERED');
1775 FND_MESSAGE.SET_TOKEN('BSC_DATASET', p_Dataset_Rec.Bsc_Dataset_Id);
1776 FND_MSG_PUB.ADD;
1777 RAISE FND_API.G_EXC_ERROR;
1778 end if;
1779
1780 update BSC_SYS_DATASET_CALC
1781 set disabled_calc_id = p_Dataset_Rec.Bsc_Disabled_Calc_Id
1782 where dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
1783
1784 if (p_commit = FND_API.G_TRUE) then
1785 commit;
1786 end if;
1787
1788 EXCEPTION
1789 WHEN FND_API.G_EXC_ERROR THEN
1790 x_return_status := FND_API.G_RET_STS_ERROR;
1791 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1792 ,p_data => x_msg_data);
1793 raise;
1794 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1796 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1797 ,p_data => x_msg_data);
1798 raise;
1799 WHEN NO_DATA_FOUND THEN
1800 x_return_status := FND_API.G_RET_STS_ERROR;
1801 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1802 ,p_data => x_msg_data);
1803 raise;
1804 WHEN OTHERS THEN
1805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1806 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1807 ,p_data => x_msg_data);
1808 raise;
1809
1810 end Update_Dataset_Calc;
1811
1812 /************************************************************************************
1813 ************************************************************************************/
1814
1815 procedure Delete_Dataset_Calc(
1816 p_commit IN varchar2 := FND_API.G_FALSE
1817 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1818 ,x_return_status OUT NOCOPY varchar2
1819 ,x_msg_count OUT NOCOPY number
1820 ,x_msg_data OUT NOCOPY varchar2
1821 ) is
1822
1823 l_count number;
1824
1825 begin
1826
1827 -- Check that valid dataset id was entered.
1828 if p_Dataset_Rec.Bsc_Dataset_Id is not null then
1829
1830 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_DATASETS_B'
1831 ,'dataset_id'
1832 ,p_Dataset_Rec.Bsc_Dataset_Id);
1833
1834 if l_count = 0 then
1835 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DTSET_ID');
1836 FND_MESSAGE.SET_TOKEN('BSC_DATASET', p_Dataset_Rec.Bsc_Dataset_Id);
1837 FND_MSG_PUB.ADD;
1838 RAISE FND_API.G_EXC_ERROR;
1839 end if;
1840 else
1841 FND_MESSAGE.SET_NAME('BSC','BSC_NO_DTSET_ID_ENTERED');
1842 FND_MESSAGE.SET_TOKEN('BSC_DATASET', p_Dataset_Rec.Bsc_Dataset_Id);
1843 FND_MSG_PUB.ADD;
1844 RAISE FND_API.G_EXC_ERROR;
1845 end if;
1846
1847 delete from BSC_SYS_DATASET_CALC
1848 where dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
1849
1850 if (p_commit = FND_API.G_TRUE) then
1851 commit;
1852 end if;
1853
1854 EXCEPTION
1855 WHEN FND_API.G_EXC_ERROR THEN
1856 x_return_status := FND_API.G_RET_STS_ERROR;
1857 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1858 ,p_data => x_msg_data);
1859 raise;
1860 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1862 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1863 ,p_data => x_msg_data);
1864 raise;
1865 WHEN NO_DATA_FOUND THEN null;
1866 /*
1867 x_return_status := FND_API.G_RET_STS_ERROR;
1868 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1869 ,p_data => x_msg_data);
1870 raise;
1871 */
1872 WHEN OTHERS THEN
1873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1874 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1875 ,p_data => x_msg_data);
1876 raise;
1877
1878 end Delete_Dataset_Calc;
1879
1880 /************************************************************************************
1881 ************************************************************************************/
1882
1883 --: This function gets the count of rows for the name for the given measure.
1884 --: This function is used as a validation method.
1885
1886 function Validate_Measure(
1887 p_Measure_Name varchar2
1888 ) return number is
1889
1890 l_count number;
1891
1892 begin
1893 -- mdamle 04/23/2003 - PMD - Measure Definer - short_name not used, instead dataset_id added to bis_indicators
1894 select count(1)
1895 into l_count
1896 -- from BSC_SYS_MEASURES
1897 from bis_indicators i, bsc_sys_datasets_vl d
1898 where short_name = p_Measure_Name
1899 and i.dataset_id = d.dataset_id;
1900
1901 return l_count;
1902
1903 EXCEPTION
1904 when NO_DATA_FOUND then
1905 null;
1906 end Validate_Measure;
1907
1908
1909 /************************************************************************************
1910 ************************************************************************************/
1911 --=============================================================================
1912 PROCEDURE Translate_Measure
1913 ( p_commit IN VARCHAR2
1914 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
1915 , p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1916 , x_return_status OUT NOCOPY VARCHAR2
1917 , x_msg_count OUT NOCOPY NUMBER
1918 , x_msg_data OUT NOCOPY VARCHAR2
1919 )
1920 IS
1921
1922 BEGIN
1923 x_return_status := FND_API.G_RET_STS_SUCCESS;
1924
1925 FND_MSG_PUB.Initialize;
1926
1927 UPDATE bsc_sys_datasets_tl
1928 SET name = p_Dataset_Rec.Bsc_Dataset_Name
1929 ,help = p_Dataset_Rec.Bsc_Dataset_Help
1930 ,y_axis_title = p_Dataset_Rec.Bsc_y_axis_title
1931 ,source_lang = userenv('LANG')
1932 WHERE dataset_id = p_Dataset_Rec.Bsc_Dataset_Id
1933 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1934
1935 IF (p_commit = FND_API.G_TRUE) THEN
1936 COMMIT;
1937 END IF;
1938
1939 EXCEPTION
1940 WHEN FND_API.G_EXC_ERROR THEN
1941 x_return_status := FND_API.G_RET_STS_ERROR;
1942 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1943 ,p_data => x_msg_data);
1944 RAISE;
1945 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1947 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1948 ,p_data => x_msg_data);
1949 RAISE;
1950 WHEN NO_DATA_FOUND THEN
1951 x_return_status := FND_API.G_RET_STS_ERROR;
1952 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1953 ,p_data => x_msg_data);
1954 RAISE;
1955 WHEN OTHERS THEN
1956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1957 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1958 ,p_data => x_msg_data);
1959 RAISE;
1960
1961 END Translate_Measure;
1962 --=============================================================================
1963
1964 -- mdamle 09/25/2003 - Sync up measures for all installed languages
1965 PROCEDURE Translate_Measure_By_lang
1966 ( p_commit IN VARCHAR2
1967 , p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1968 , p_lang IN VARCHAR2
1969 , p_source_lang IN VARCHAR2
1970 , x_return_status OUT NOCOPY VARCHAR2
1971 , x_msg_count OUT NOCOPY NUMBER
1972 , x_msg_data OUT NOCOPY VARCHAR2
1973 )
1974 IS
1975
1976 BEGIN
1977 SAVEPOINT TransMeasByLangBsc;
1978
1979 x_return_status := FND_API.G_RET_STS_SUCCESS;
1980
1981 FND_MSG_PUB.Initialize;
1982
1983 UPDATE BSC_SYS_DATASETS_TL
1984 SET name = p_Dataset_Rec.Bsc_Dataset_Name
1985 ,help = p_Dataset_Rec.Bsc_Dataset_Help
1986 ,y_axis_title = p_Dataset_Rec.Bsc_y_axis_title
1987 ,source_lang = p_source_lang
1988 WHERE dataset_id = p_Dataset_Rec.Bsc_Dataset_Id
1989 and LANGUAGE = p_lang;
1990
1991 IF (p_commit = FND_API.G_TRUE) THEN
1992 COMMIT;
1993 END IF;
1994
1995 EXCEPTION
1996 WHEN FND_API.G_EXC_ERROR THEN
1997 ROLLBACK TO TransMeasByLangBsc;
1998 x_return_status := FND_API.G_RET_STS_ERROR;
1999 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2000 ,p_data => x_msg_data);
2001 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2002 ROLLBACK TO TransMeasByLangBsc;
2003 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2004 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2005 ,p_data => x_msg_data);
2006 WHEN NO_DATA_FOUND THEN
2007 ROLLBACK TO TransMeasByLangBsc;
2008 x_return_status := FND_API.G_RET_STS_ERROR;
2009 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2010 ,p_data => x_msg_data);
2011 WHEN OTHERS THEN
2012 ROLLBACK TO TransMeasByLangBsc;
2013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2014 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2015 ,p_data => x_msg_data);
2016
2017 END Translate_Measure_By_Lang;
2018 --=============================================================================
2019
2020 end BSC_DATASETS_PVT;