1 package body BSC_DATASETS_PUB as
2 /* $Header: BSCPDTSB.pls 120.2 2007/02/08 13:31:27 akoduri ship $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_DATASETS_PUB';
5
6 --: This procedure creates a BSC measure. This is the entry point for the
7 --: Data Set API.
8 --: This procedure is part of the Data Set API.
9 -- mdamle 03/12/2003 - PMD - Measure Definer - Added x_dataset_id
10 procedure Create_Measures (
11 p_commit IN varchar2 := FND_API.G_FALSE
12 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
13 ,x_Dataset_Id OUT NOCOPY number
14 ,x_return_status OUT NOCOPY varchar2
15 ,x_msg_count OUT NOCOPY number
16 ,x_msg_data OUT NOCOPY varchar2
17 ) is
18
19 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
20 l_measure_col BSC_SYS_MEASURES.MEASURE_COL%TYPE;
21
22 begin
23 x_return_status := FND_API.G_RET_STS_SUCCESS;
24 -- Assign all values in the passed "Record" parameter to the locally defined
25 -- "Record" variable.
26 l_Dataset_Rec := p_Dataset_Rec;
27
28 -- Assign certain default values if they are currently null.
29 if l_Dataset_Rec.Bsc_Dataset_Autoscale_Flag is null then
30 l_Dataset_Rec.Bsc_Dataset_Autoscale_Flag := 0;
31 end if;
32 if l_Dataset_Rec.Bsc_Dataset_Color_Method is null then
33 l_Dataset_Rec.Bsc_Dataset_Color_Method := 1;
34 end if;
35 if l_Dataset_Rec.Bsc_Dataset_Format_Id is null then
36 l_Dataset_Rec.Bsc_Dataset_Format_Id := 5;
37 end if;
38 if l_Dataset_Rec.Bsc_Dataset_Projection_Flag is null then
39 l_Dataset_Rec.Bsc_Dataset_Projection_Flag := 1;
40 end if;
41 if l_Dataset_Rec.Bsc_Measure_Group_Id is null then
42 l_Dataset_Rec.Bsc_Measure_Group_Id := -1;
43 end if;
44 if l_Dataset_Rec.Bsc_Measure_Help is null then
45 l_Dataset_Rec.Bsc_Measure_Help := 'Help: ' || l_Dataset_Rec.Bsc_Measure_Short_Name;
46 end if;
47 if l_Dataset_Rec.Bsc_Measure_Max_Act_Value is null then
48 l_Dataset_Rec.Bsc_Measure_Max_Act_Value := 1500;
49 end if;
50 if l_Dataset_Rec.Bsc_Measure_Max_Bud_Value is null then
51 l_Dataset_Rec.Bsc_Measure_Max_Bud_Value := 1500;
52 end if;
53 if l_Dataset_Rec.Bsc_Measure_Min_Act_Value is null then
54 l_Dataset_Rec.Bsc_Measure_Min_Act_Value := 1000;
55 end if;
56 if l_Dataset_Rec.Bsc_Measure_Min_Bud_Value is null then
57 l_Dataset_Rec.Bsc_Measure_Min_Bud_Value := 1000;
58 end if;
59 if l_Dataset_Rec.Bsc_Measure_Projection_Id is null then
60 l_Dataset_Rec.Bsc_Measure_Projection_Id := 3;
61 end if;
62 if l_Dataset_Rec.Bsc_Measure_Random_Style is null then
63 l_Dataset_Rec.Bsc_Measure_Random_Style := 1;
64 end if;
65 if l_Dataset_Rec.Bsc_Meas_Type is null then
66 l_Dataset_Rec.Bsc_Meas_Type := 0;
67 end if;
68
69 if l_Dataset_Rec.Bsc_Measure_Created_By is null then
70 l_Dataset_Rec.Bsc_Measure_Created_By := fnd_global.USER_ID;
71 end if;
72 if l_Dataset_Rec.Bsc_Measure_Last_Update_By is null then
73 l_Dataset_Rec.Bsc_Measure_Last_Update_By := fnd_global.USER_ID;
74 end if;
75 if l_Dataset_Rec.Bsc_Measure_Last_Update_Login is null then
76 l_Dataset_Rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
77 end if;
78
79
80 -- If Measure does not exist then create it.
81 if BSC_DATASETS_PVT.Validate_Measure(l_Dataset_Rec.Bsc_Measure_Short_Name) < 1 then
82
83 -- Get the next ID in measure table to assign to current measure.
84 l_Dataset_Rec.Bsc_Measure_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value( 'BSC_SYS_MEASURES'
85 ,'measure_id');
86
87 -- Call the following procedure.
88 Create_Dataset( p_commit
89 ,l_Dataset_Rec
90 ,x_Dataset_Id
91 ,x_return_status
92 ,x_msg_count
93 ,x_msg_data);
94
95 -- mdamle 04/23/2003 - PMD - Measure Definer
96 -- Switched order of create_dataset and create_measures as well.
97 if l_Dataset_Rec.Bsc_Measure_Short_Name is null then
98 l_Dataset_Rec.Bsc_Measure_Short_Name := BSC_BIS_MEASURE_PUB.c_PMD || x_Dataset_id;
99 end if;
100 if l_Dataset_Rec.Bsc_Measure_Col is null then
101 l_measure_col := BSC_BIS_MEASURE_PUB.get_measure_col(l_Dataset_Rec.Bsc_Dataset_Name, NULL, l_Dataset_Rec.Bsc_Measure_Id,l_Dataset_Rec.Bsc_Measure_Short_Name);
102 if (l_measure_col is not null) then
103 l_Dataset_Rec.Bsc_Measure_Col := l_measure_col;
104 else
105 l_Dataset_Rec.Bsc_Measure_Col := l_Dataset_Rec.Bsc_Measure_Short_Name;
106 end if;
107 end if;
108
109 -- Call private version of the procedure.
110 BSC_DATASETS_PVT.Create_Measures( p_commit
111 ,l_Dataset_Rec
112 ,x_return_status
113 ,x_msg_count
114 ,x_msg_data);
115
116
117 end if;
118
119
120 EXCEPTION
121 WHEN FND_API.G_EXC_ERROR THEN
122 IF (x_msg_data IS NULL) THEN
123 FND_MSG_PUB.Count_And_Get
124 ( p_encoded => FND_API.G_FALSE
125 , p_count => x_msg_count
126 , p_data => x_msg_data
127 );
128 END IF;
129 x_return_status := FND_API.G_RET_STS_ERROR;
130 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
131 IF (x_msg_data IS NULL) THEN
132 FND_MSG_PUB.Count_And_Get
133 ( p_encoded => FND_API.G_FALSE
134 , p_count => x_msg_count
135 , p_data => x_msg_data
136 );
137 END IF;
138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
139 WHEN NO_DATA_FOUND THEN
140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
141 IF (x_msg_data IS NOT NULL) THEN
142 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Create_Measures ';
143 ELSE
144 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Create_Measures ';
145 END IF;
146 WHEN OTHERS THEN
147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
148 IF (x_msg_data IS NOT NULL) THEN
149 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Create_Measures ';
150 ELSE
151 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Create_Measures ';
152 END IF;
153 end Create_Measures;
154
155 /************************************************************************************
156 ************************************************************************************/
157
158 -- ADRAO : Overloaded for iBuilder
159 procedure Create_Measures (
160 p_commit IN varchar2 := FND_API.G_FALSE
161 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
162 ,x_return_status OUT NOCOPY varchar2
163 ,x_msg_count OUT NOCOPY number
164 ,x_msg_data OUT NOCOPY varchar2
165 ) is
166
167 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
168 l_measure_col BSC_SYS_MEASURES.MEASURE_COL%TYPE;
169
170 begin
171 x_return_status := FND_API.G_RET_STS_SUCCESS;
172 -- Assign all values in the passed "Record" parameter to the locally defined
173 -- "Record" variable.
174 l_Dataset_Rec := p_Dataset_Rec;
175
176 -- Assign certain default values if they are currently null.
177 if l_Dataset_Rec.Bsc_Dataset_Autoscale_Flag is null then
178 l_Dataset_Rec.Bsc_Dataset_Autoscale_Flag := 0;
179 end if;
180 if l_Dataset_Rec.Bsc_Dataset_Color_Method is null then
181 l_Dataset_Rec.Bsc_Dataset_Color_Method := 1;
182 end if;
183 if l_Dataset_Rec.Bsc_Dataset_Format_Id is null then
184 l_Dataset_Rec.Bsc_Dataset_Format_Id := 5;
185 end if;
186 if l_Dataset_Rec.Bsc_Dataset_Projection_Flag is null then
187 l_Dataset_Rec.Bsc_Dataset_Projection_Flag := 1;
188 end if;
189 if l_Dataset_Rec.Bsc_Measure_Group_Id is null then
190 l_Dataset_Rec.Bsc_Measure_Group_Id := -1;
191 end if;
192 if l_Dataset_Rec.Bsc_Measure_Help is null then
193 l_Dataset_Rec.Bsc_Measure_Help := 'Help: ' || l_Dataset_Rec.Bsc_Measure_Short_Name;
194 end if;
195 if l_Dataset_Rec.Bsc_Measure_Max_Act_Value is null then
196 l_Dataset_Rec.Bsc_Measure_Max_Act_Value := 1500;
197 end if;
198 if l_Dataset_Rec.Bsc_Measure_Max_Bud_Value is null then
199 l_Dataset_Rec.Bsc_Measure_Max_Bud_Value := 3500;
200 end if;
201 if l_Dataset_Rec.Bsc_Measure_Min_Act_Value is null then
202 l_Dataset_Rec.Bsc_Measure_Min_Act_Value := -100;
203 end if;
204 if l_Dataset_Rec.Bsc_Measure_Min_Bud_Value is null then
205 l_Dataset_Rec.Bsc_Measure_Min_Bud_Value := 50;
206 end if;
207 if l_Dataset_Rec.Bsc_Measure_Projection_Id is null then
208 l_Dataset_Rec.Bsc_Measure_Projection_Id := 3;
209 end if;
210 if l_Dataset_Rec.Bsc_Measure_Random_Style is null then
211 l_Dataset_Rec.Bsc_Measure_Random_Style := 1;
212 end if;
213 if l_Dataset_Rec.Bsc_Meas_Type is null then
214 l_Dataset_Rec.Bsc_Meas_Type := 0;
215 end if;
216
217 if l_Dataset_Rec.Bsc_Measure_Created_By is null then
218 l_Dataset_Rec.Bsc_Measure_Created_By := fnd_global.USER_ID;
219 end if;
220 if l_Dataset_Rec.Bsc_Measure_Last_Update_By is null then
221 l_Dataset_Rec.Bsc_Measure_Last_Update_By := fnd_global.USER_ID;
222 end if;
223 if l_Dataset_Rec.Bsc_Measure_Last_Update_Login is null then
224 l_Dataset_Rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
225 end if;
226
227
228 -- If Measure does not exist then create it.
229 if BSC_DATASETS_PVT.Validate_Measure(l_Dataset_Rec.Bsc_Measure_Short_Name) < 1 then
230
231 -- Get the next ID in measure table to assign to current measure.
232 l_Dataset_Rec.Bsc_Measure_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value( 'BSC_SYS_MEASURES'
233 ,'measure_id');
234
235 -- Call the following procedure.
236 Create_Dataset( p_commit
237 ,l_Dataset_Rec
238 ,x_return_status
239 ,x_msg_count
240 ,x_msg_data);
241
242 -- mdamle 04/23/2003 - PMD - Measure Definer
243 -- Switched order of create_dataset and create_measures as well.
244 if l_Dataset_Rec.Bsc_Measure_Col is null then
245 l_measure_col := BSC_BIS_MEASURE_PUB.get_measure_col(l_Dataset_Rec.Bsc_Dataset_Name, NULL, l_Dataset_Rec.Bsc_Measure_Id,l_Dataset_Rec.Bsc_Measure_Short_Name);
246 if (l_measure_col is not null) then
247 l_Dataset_Rec.Bsc_Measure_Col := l_measure_col;
248 else
249 l_Dataset_Rec.Bsc_Measure_Col := l_Dataset_Rec.Bsc_Measure_Short_Name;
250 end if;
251 --l_Dataset_Rec.Bsc_Measure_Col := l_Dataset_Rec.Bsc_Measure_Short_Name;
252 end if;
253
254 -- Call private version of the procedure.
255 BSC_DATASETS_PVT.Create_Measures( p_commit
256 ,l_Dataset_Rec
257 ,x_return_status
258 ,x_msg_count
259 ,x_msg_data);
260
261
262 end if;
263
264
265 EXCEPTION
266 WHEN FND_API.G_EXC_ERROR THEN
267 IF (x_msg_data IS NULL) THEN
268 FND_MSG_PUB.Count_And_Get
269 ( p_encoded => FND_API.G_FALSE
270 , p_count => x_msg_count
271 , p_data => x_msg_data
272 );
273 END IF;
274 x_return_status := FND_API.G_RET_STS_ERROR;
275 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
276 IF (x_msg_data IS NULL) THEN
277 FND_MSG_PUB.Count_And_Get
278 ( p_encoded => FND_API.G_FALSE
279 , p_count => x_msg_count
280 , p_data => x_msg_data
281 );
282 END IF;
283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
284 WHEN NO_DATA_FOUND THEN
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286 IF (x_msg_data IS NOT NULL) THEN
287 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Create_Measures ';
288 ELSE
289 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Create_Measures ';
290 END IF;
291 WHEN OTHERS THEN
292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
293 IF (x_msg_data IS NOT NULL) THEN
294 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Create_Measures ';
295 ELSE
296 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Create_Measures ';
297 END IF;
298
299 end Create_Measures;
300
301 /************************************************************************************
302 ************************************************************************************/
303 -- ADRAO : Overloaded for iBuilder
304
305 procedure Retrieve_Measures(
306 p_commit IN varchar2 := FND_API.G_FALSE
307 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
308 ,x_Dataset_Rec IN OUT NOCOPY BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
309 ,x_return_status OUT NOCOPY varchar2
310 ,x_msg_count OUT NOCOPY number
311 ,x_msg_data OUT NOCOPY varchar2
312 ) is
313
314 begin
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316 BSC_DATASETS_PVT.Retrieve_Measures( p_commit
317 ,p_Dataset_Rec
318 ,x_Dataset_Rec
319 ,x_return_status
320 ,x_msg_count
321 ,x_msg_data);
322
323 Retrieve_Dataset( p_commit
324 ,p_Dataset_Rec
325 ,x_Dataset_Rec
326 ,x_return_status
327 ,x_msg_count
328 ,x_msg_data);
329
330 EXCEPTION
331 WHEN FND_API.G_EXC_ERROR THEN
332 IF (x_msg_data IS NULL) THEN
333 FND_MSG_PUB.Count_And_Get
334 ( p_encoded => FND_API.G_FALSE
335 , p_count => x_msg_count
336 , p_data => x_msg_data
337 );
338 END IF;
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341 IF (x_msg_data IS NULL) THEN
342 FND_MSG_PUB.Count_And_Get
343 ( p_encoded => FND_API.G_FALSE
344 , p_count => x_msg_count
345 , p_data => x_msg_data
346 );
347 END IF;
348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
349 WHEN NO_DATA_FOUND THEN
350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
351 IF (x_msg_data IS NOT NULL) THEN
352 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Retrieve_Measures ';
353 ELSE
354 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Retrieve_Measures ';
355 END IF;
356 WHEN OTHERS THEN
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
358 IF (x_msg_data IS NOT NULL) THEN
359 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Retrieve_Measures ';
360 ELSE
361 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Retrieve_Measures ';
362 END IF;
363 end Retrieve_Measures;
364
365 /************************************************************************************
366 ************************************************************************************/
367
368 procedure Update_Measures(
369 p_commit IN varchar2 := FND_API.G_FALSE
370 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
371 ,x_return_status OUT NOCOPY varchar2
372 ,x_msg_count OUT NOCOPY number
373 ,x_msg_data OUT NOCOPY varchar2
374 ) is
375
376 begin
377 x_return_status := FND_API.G_RET_STS_SUCCESS;
378 BSC_DATASETS_PVT.Update_Measures( p_commit
379 ,p_Dataset_Rec
380 ,x_return_status
381 ,x_msg_count
382 ,x_msg_data);
383
384 Update_Dataset( p_commit
385 ,p_Dataset_Rec
386 ,x_return_status
387 ,x_msg_count
388 ,x_msg_data);
389
390 EXCEPTION
391 WHEN FND_API.G_EXC_ERROR THEN
392 IF (x_msg_data IS NULL) THEN
393 FND_MSG_PUB.Count_And_Get
394 ( p_encoded => FND_API.G_FALSE
395 , p_count => x_msg_count
396 , p_data => x_msg_data
397 );
398 END IF;
399 x_return_status := FND_API.G_RET_STS_ERROR;
400 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
401 IF (x_msg_data IS NULL) THEN
402 FND_MSG_PUB.Count_And_Get
403 ( p_encoded => FND_API.G_FALSE
404 , p_count => x_msg_count
405 , p_data => x_msg_data
406 );
407 END IF;
408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
409 WHEN NO_DATA_FOUND THEN
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
411 IF (x_msg_data IS NOT NULL) THEN
412 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Update_Measures ';
413 ELSE
414 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Update_Measures ';
415 END IF;
416 WHEN OTHERS THEN
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
418 IF (x_msg_data IS NOT NULL) THEN
419 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Update_Measures ';
420 ELSE
421 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Update_Measures ';
422 END IF;
423 end Update_Measures;
424
425 /************************************************************************************
426 ************************************************************************************/
427
428 -- mdamle 04/23/2003 - PMD - Measure Definer - Added p_update_dset_calc
429 procedure Update_Measures(
430 p_commit IN varchar2 := FND_API.G_FALSE
431 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
432 ,p_update_dset_calc IN BOOLEAN
433 ,x_return_status OUT NOCOPY varchar2
434 ,x_msg_count OUT NOCOPY number
435 ,x_msg_data OUT NOCOPY varchar2
436 )is
437
438 begin
439 x_return_status := FND_API.G_RET_STS_SUCCESS;
440 BSC_DATASETS_PVT.Update_Measures( p_commit
441 ,p_Dataset_Rec
442 ,x_return_status
443 ,x_msg_count
444 ,x_msg_data);
445
446 Update_Dataset( p_commit
447 ,p_Dataset_Rec
448 ,p_update_dset_calc
449 ,x_return_status
450 ,x_msg_count
451 ,x_msg_data);
452
453 EXCEPTION
454 WHEN FND_API.G_EXC_ERROR THEN
455 IF (x_msg_data IS NULL) THEN
456 FND_MSG_PUB.Count_And_Get
457 ( p_encoded => FND_API.G_FALSE
458 , p_count => x_msg_count
459 , p_data => x_msg_data
460 );
461 END IF;
462 x_return_status := FND_API.G_RET_STS_ERROR;
463 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
464 IF (x_msg_data IS NULL) THEN
465 FND_MSG_PUB.Count_And_Get
466 ( p_encoded => FND_API.G_FALSE
467 , p_count => x_msg_count
468 , p_data => x_msg_data
469 );
470 END IF;
471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
472 WHEN NO_DATA_FOUND THEN
473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
474 IF (x_msg_data IS NOT NULL) THEN
475 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Update_Measures ';
476 ELSE
477 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Update_Measures ';
478 END IF;
479 WHEN OTHERS THEN
480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
481 IF (x_msg_data IS NOT NULL) THEN
482 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Update_Measures ';
483 ELSE
484 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Update_Measures ';
485 END IF;
486 end Update_Measures;
487
488 /************************************************************************************
489 ************************************************************************************/
490
491 procedure Delete_Measures(
492 p_commit IN varchar2 := FND_API.G_FALSE
493 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
494 ,x_return_status OUT NOCOPY varchar2
495 ,x_msg_count OUT NOCOPY number
496 ,x_msg_data OUT NOCOPY varchar2
497 ) is
498
499 begin
500 x_return_status := FND_API.G_RET_STS_SUCCESS;
501 Delete_Dataset( p_commit
502 ,p_Dataset_Rec
503 ,x_return_status
504 ,x_msg_count
505 ,x_msg_data);
506
507 IF ( (x_return_status = FND_API.G_RET_STS_SUCCESS) OR (x_return_status IS NULL) ) THEN
508 BSC_DATASETS_PVT.Delete_Measures( p_commit
509 ,p_Dataset_Rec
510 ,x_return_status
511 ,x_msg_count
512 ,x_msg_data);
513 END IF;
514
515 EXCEPTION
516 WHEN FND_API.G_EXC_ERROR THEN
517 IF (x_msg_data IS NULL) THEN
518 FND_MSG_PUB.Count_And_Get
519 ( p_encoded => FND_API.G_FALSE
520 , p_count => x_msg_count
521 , p_data => x_msg_data
522 );
523 END IF;
524 x_return_status := FND_API.G_RET_STS_ERROR;
525 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
526 IF (x_msg_data IS NULL) THEN
527 FND_MSG_PUB.Count_And_Get
528 ( p_encoded => FND_API.G_FALSE
529 , p_count => x_msg_count
530 , p_data => x_msg_data
531 );
532 END IF;
533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
534 WHEN NO_DATA_FOUND THEN
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
536 IF (x_msg_data IS NOT NULL) THEN
537 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Delete_Measures ';
538 ELSE
539 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Delete_Measures ';
540 END IF;
541 WHEN OTHERS THEN
542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
543 IF (x_msg_data IS NOT NULL) THEN
544 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Delete_Measures ';
545 ELSE
546 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Delete_Measures ';
547 END IF;
548 end Delete_Measures;
549
550 /************************************************************************************
551 ************************************************************************************/
552 /*
553
554 procedure Create_Formats(
555 p_commit IN varchar2 := FND_API.G_FALSE
556 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
557 ,x_return_status OUT NOCOPY varchar2
558 ,x_msg_count OUT NOCOPY number
559 ,x_msg_data OUT NOCOPY varchar2
560 ) is
561
562 begin
563 x_return_status := FND_API.G_RET_STS_SUCCESS;
564 EXCEPTION
565 WHEN FND_API.G_EXC_ERROR THEN
566 IF (x_msg_data IS NULL) THEN
567 FND_MSG_PUB.Count_And_Get
568 ( p_encoded => FND_API.G_FALSE
569 , p_count => x_msg_count
570 , p_data => x_msg_data
571 );
572 END IF;
573 x_return_status := FND_API.G_RET_STS_ERROR;
574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
575 IF (x_msg_data IS NULL) THEN
576 FND_MSG_PUB.Count_And_Get
577 ( p_encoded => FND_API.G_FALSE
578 , p_count => x_msg_count
579 , p_data => x_msg_data
580 );
581 END IF;
582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
583 WHEN NO_DATA_FOUND THEN
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585 IF (x_msg_data IS NOT NULL) THEN
586 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Create_Formats ';
587 ELSE
588 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Create_Formats ';
589 END IF;
590 WHEN OTHERS THEN
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592 IF (x_msg_data IS NOT NULL) THEN
593 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Create_Formats ';
594 ELSE
595 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Create_Formats ';
596 END IF;
597 end Create_Formats;
598 */
599
600 /************************************************************************************
601 ************************************************************************************/
602 /*
603
604 procedure Delete_Formats(
605 p_commit IN varchar2 := FND_API.G_FALSE
606 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
607 ,x_return_status OUT NOCOPY varchar2
608 ,x_msg_count OUT NOCOPY number
609 ,x_msg_data OUT NOCOPY varchar2
610 ) is
611
612 begin
613 x_return_status := FND_API.G_RET_STS_SUCCESS;
614 EXCEPTION
615 WHEN FND_API.G_EXC_ERROR THEN
616 IF (x_msg_data IS NULL) THEN
617 FND_MSG_PUB.Count_And_Get
618 ( p_encoded => FND_API.G_FALSE
619 , p_count => x_msg_count
620 , p_data => x_msg_data
621 );
622 END IF;
623 x_return_status := FND_API.G_RET_STS_ERROR;
624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
625 IF (x_msg_data IS NULL) THEN
626 FND_MSG_PUB.Count_And_Get
627 ( p_encoded => FND_API.G_FALSE
628 , p_count => x_msg_count
629 , p_data => x_msg_data
630 );
631 END IF;
632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
633 WHEN NO_DATA_FOUND THEN
634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
635 IF (x_msg_data IS NOT NULL) THEN
636 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Delete_Formats ';
637 ELSE
638 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Delete_Formats ';
639 END IF;
640 WHEN OTHERS THEN
641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
642 IF (x_msg_data IS NOT NULL) THEN
643 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Delete_Formats ';
644 ELSE
645 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Delete_Formats ';
646 END IF;
647 end Delete_Formats;
648 */
649
650 /************************************************************************************
651 ************************************************************************************/
652
653 --: This procedure creates a dataset for the given measure.
654 --: This procedure is part of the Data Set API.
655 -- mdamle 04/23/2003 - PMD - Measure Definer - Added x_dataset_id
656 procedure Create_Dataset(
657 p_commit IN varchar2 := FND_API.G_FALSE
658 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
659 ,x_Dataset_Id OUT NOCOPY number
660 ,x_return_status OUT NOCOPY varchar2
661 ,x_msg_count OUT NOCOPY number
662 ,x_msg_data OUT NOCOPY varchar2
663 ) is
664
665 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
666
667 l_count number;
668
669 begin
670 x_return_status := FND_API.G_RET_STS_SUCCESS;
671 -- Assign all values in the passed "Record" parameter to the locally defined
672 -- "Record" variable.
673 l_Dataset_Rec := p_Dataset_Rec;
674
675
676 -- If dataset name is null then give it the measure short name.
677 if l_Dataset_Rec.Bsc_Dataset_Name is null then
678 l_Dataset_Rec.Bsc_Dataset_Name := l_Dataset_Rec.Bsc_Measure_Short_Name;
679 end if;
680
681 -- mdamle 07/24/2003 - Default null description to dataset name
682 if l_Dataset_Rec.Bsc_Dataset_Help is null then
683 l_Dataset_Rec.Bsc_Dataset_Help := l_Dataset_Rec.Bsc_Dataset_Name;
684 end if;
685
686 -- 16-JUN-2003 ADRAO added for who columns
687 if l_Dataset_Rec.Bsc_Dataset_Created_By is null then
688 l_Dataset_Rec.Bsc_Dataset_Created_By := fnd_global.USER_ID;
689 end if;
690 if l_Dataset_Rec.Bsc_Dataset_Last_Update_By is null then
691 l_Dataset_Rec.Bsc_Dataset_Last_Update_By := fnd_global.USER_ID;
692 end if;
693 if l_Dataset_Rec.Bsc_Dataset_Last_Update_Login is null then
694 l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := fnd_global.LOGIN_ID;
695 end if;
696
697
698 /* Remove following validation, in case there are measures with the same name.
699 -- Determine if this Data Set already exists.
700 select count(*)
701 into l_count
702 from BSC_SYS_DATASETS_TL a
703 ,BSC_SYS_DATASETS_B b
704 where upper(a.name) = upper(l_Dataset_Rec.Bsc_Dataset_Name)
705 and upper(b.source) = upper(l_Dataset_Rec.Bsc_Source)
706 and a.dataset_id = b.dataset_id;
707 */
708
709 -- If Data set does not exist then create it, else do nothing.
710 -- if l_count = 0 then
711
712 -- Get the next ID value in the datasets table for the current data set.
713 l_Dataset_Rec.Bsc_Dataset_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value( 'BSC_SYS_DATASETS_TL'
714 ,'dataset_id');
715
716 -- mdamle 04/23/2003 - PMD - Measure Definer
717 x_Dataset_Id := l_Dataset_Rec.Bsc_Dataset_Id;
718
719 -- Call private version of the procedure.
720 BSC_DATASETS_PVT.Create_Dataset( p_commit
721 ,l_Dataset_Rec
722 ,x_return_status
723 ,x_msg_count
724 ,x_msg_data);
725
726 -- mdamle 07/24/2003 - In the new PMD Measure Definer, the
727 -- defaults are taken care of in the UI, and the
728 -- update of this table is handled separately.
729 -- Call the following procedure.
730 /*
731 Create_Dataset_Calc( p_commit
732 ,l_Dataset_Rec
733 ,x_return_status
734 ,x_msg_count
735 ,x_msg_data);
736 */
737 -- end if;
738
739 EXCEPTION
740 WHEN FND_API.G_EXC_ERROR THEN
741 IF (x_msg_data IS NULL) THEN
742 FND_MSG_PUB.Count_And_Get
743 ( p_encoded => FND_API.G_FALSE
744 , p_count => x_msg_count
745 , p_data => x_msg_data
746 );
747 END IF;
748 x_return_status := FND_API.G_RET_STS_ERROR;
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750 IF (x_msg_data IS NULL) THEN
751 FND_MSG_PUB.Count_And_Get
752 ( p_encoded => FND_API.G_FALSE
753 , p_count => x_msg_count
754 , p_data => x_msg_data
755 );
756 END IF;
757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
758 WHEN NO_DATA_FOUND THEN
759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
760 IF (x_msg_data IS NOT NULL) THEN
761 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Create_Dataset ';
762 ELSE
763 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Create_Dataset ';
764 END IF;
765 WHEN OTHERS THEN
766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
767 IF (x_msg_data IS NOT NULL) THEN
768 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Create_Dataset ';
769 ELSE
770 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Create_Dataset ';
771 END IF;
772 end Create_Dataset;
773
774 /************************************************************************************
775 ************************************************************************************/
776 -- ADRAO : Overloaded for iBuilder
777 procedure Create_Dataset(
778 p_commit IN varchar2 := FND_API.G_FALSE
779 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
780 ,x_return_status OUT NOCOPY varchar2
781 ,x_msg_count OUT NOCOPY number
782 ,x_msg_data OUT NOCOPY varchar2
783 ) is
784
785 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
786
787 l_count number;
788
789 begin
790 x_return_status := FND_API.G_RET_STS_SUCCESS;
791 -- Assign all values in the passed "Record" parameter to the locally defined
792 -- "Record" variable.
793 l_Dataset_Rec := p_Dataset_Rec;
794
795
796 -- If dataset name is null then give it the measure short name.
797 if l_Dataset_Rec.Bsc_Dataset_Name is null then
798 l_Dataset_Rec.Bsc_Dataset_Name := l_Dataset_Rec.Bsc_Measure_Short_Name;
799 end if;
800
801 if l_Dataset_Rec.Bsc_Dataset_Help is null then
802 l_Dataset_Rec.Bsc_Dataset_Help := 'No Help comment provided.';
803 end if;
804
805 -- 16-JUN-2003 ADRAO added for who columns
806 if l_Dataset_Rec.Bsc_Dataset_Created_By is null then
807 l_Dataset_Rec.Bsc_Dataset_Created_By := fnd_global.USER_ID;
808 end if;
809 if l_Dataset_Rec.Bsc_Dataset_Last_Update_By is null then
810 l_Dataset_Rec.Bsc_Dataset_Last_Update_By := fnd_global.USER_ID;
811 end if;
812 if l_Dataset_Rec.Bsc_Dataset_Last_Update_Login is null then
813 l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := fnd_global.LOGIN_ID;
814 end if;
815
816
817 /* Remove following validation, in case there are measures with the same name.
818 -- Determine if this Data Set already exists.
819 select count(*)
820 into l_count
821 from BSC_SYS_DATASETS_TL a
822 ,BSC_SYS_DATASETS_B b
823 where upper(a.name) = upper(l_Dataset_Rec.Bsc_Dataset_Name)
824 and upper(b.source) = upper(l_Dataset_Rec.Bsc_Source)
825 and a.dataset_id = b.dataset_id;
826 */
827
828 -- If Data set does not exist then create it, else do nothing.
829 -- if l_count = 0 then
830
831 -- Get the next ID value in the datasets table for the current data set.
832 l_Dataset_Rec.Bsc_Dataset_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value( 'BSC_SYS_DATASETS_TL'
833 ,'dataset_id');
834
835 -- mdamle 04/23/2003 - PMD - Measure Definer
836 -- x_Dataset_Id := l_Dataset_Rec.Bsc_Dataset_Id;
837
838 -- Call private version of the procedure.
839 BSC_DATASETS_PVT.Create_Dataset( p_commit
840 ,l_Dataset_Rec
841 ,x_return_status
842 ,x_msg_count
843 ,x_msg_data);
844
845 -- Call the following procedure.
846 Create_Dataset_Calc( p_commit
847 ,l_Dataset_Rec
848 ,x_return_status
849 ,x_msg_count
850 ,x_msg_data);
851
852 -- end if;
853
854 EXCEPTION
855 WHEN FND_API.G_EXC_ERROR THEN
856 IF (x_msg_data IS NULL) THEN
857 FND_MSG_PUB.Count_And_Get
858 ( p_encoded => FND_API.G_FALSE
859 , p_count => x_msg_count
860 , p_data => x_msg_data
861 );
862 END IF;
863 x_return_status := FND_API.G_RET_STS_ERROR;
864 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
865 IF (x_msg_data IS NULL) THEN
866 FND_MSG_PUB.Count_And_Get
867 ( p_encoded => FND_API.G_FALSE
868 , p_count => x_msg_count
869 , p_data => x_msg_data
870 );
871 END IF;
872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
873 WHEN NO_DATA_FOUND THEN
874 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
875 IF (x_msg_data IS NOT NULL) THEN
876 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Create_Dataset ';
877 ELSE
878 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Create_Dataset ';
879 END IF;
880 WHEN OTHERS THEN
881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
882 IF (x_msg_data IS NOT NULL) THEN
883 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Create_Dataset ';
884 ELSE
885 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Create_Dataset ';
886 END IF;
887 end Create_Dataset;
888
889 -- ADRAO : Overloaded for iBuilder
890
891 /************************************************************************************
892 ************************************************************************************/
893
894
895 procedure Retrieve_Dataset(
896 p_commit IN varchar2 := FND_API.G_FALSE
897 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
898 ,x_Dataset_Rec IN OUT NOCOPY BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
899 ,x_return_status OUT NOCOPY varchar2
900 ,x_msg_count OUT NOCOPY number
901 ,x_msg_data OUT NOCOPY varchar2
902 ) is
903
904 begin
905 x_return_status := FND_API.G_RET_STS_SUCCESS;
906 BSC_DATASETS_PVT.Retrieve_Dataset( p_commit
907 ,p_Dataset_Rec
908 ,x_Dataset_Rec
909 ,x_return_status
910 ,x_msg_count
911 ,x_msg_data);
912
913 /*Retrieve_Dataset_Calc( p_commit
914 ,p_Dataset_Rec
915 ,x_Dataset_Rec
916 ,x_return_status
917 ,x_msg_count
918 ,x_msg_data);*/
919
920 EXCEPTION
921 WHEN FND_API.G_EXC_ERROR THEN
922 IF (x_msg_data IS NULL) THEN
923 FND_MSG_PUB.Count_And_Get
924 ( p_encoded => FND_API.G_FALSE
925 , p_count => x_msg_count
926 , p_data => x_msg_data
927 );
928 END IF;
929 x_return_status := FND_API.G_RET_STS_ERROR;
930 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
931 IF (x_msg_data IS NULL) THEN
932 FND_MSG_PUB.Count_And_Get
933 ( p_encoded => FND_API.G_FALSE
934 , p_count => x_msg_count
935 , p_data => x_msg_data
936 );
937 END IF;
938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
939 WHEN NO_DATA_FOUND THEN
940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
941 IF (x_msg_data IS NOT NULL) THEN
942 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Retrieve_Dataset ';
943 ELSE
944 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Retrieve_Dataset ';
945 END IF;
946 WHEN OTHERS THEN
947 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
948 IF (x_msg_data IS NOT NULL) THEN
949 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Retrieve_Dataset ';
950 ELSE
951 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Retrieve_Dataset ';
952 END IF;
953 end Retrieve_Dataset;
954
955 /************************************************************************************
956 ************************************************************************************/
957 /*
958 procedure Update_Dataset(
959 p_commit IN varchar2 := FND_API.G_FALSE
960 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
961 ,x_return_status OUT NOCOPY varchar2
962 ,x_msg_count OUT NOCOPY number
963 ,x_msg_data OUT NOCOPY varchar2
964 ) is
965
966 begin
967 x_return_status := FND_API.G_RET_STS_SUCCESS;
968 BSC_DATASETS_PVT.Update_Dataset( p_commit
969 ,p_Dataset_Rec
970 ,x_return_status
971 ,x_msg_count
972 ,x_msg_data);
973
974 Update_Dataset_Calc( p_commit
975 ,p_Dataset_Rec
976 ,x_return_status
977 ,x_msg_count
978 ,x_msg_data);
979
980 EXCEPTION
981 WHEN FND_API.G_EXC_ERROR THEN
982 IF (x_msg_data IS NULL) THEN
983 FND_MSG_PUB.Count_And_Get
984 ( p_encoded => FND_API.G_FALSE
985 , p_count => x_msg_count
986 , p_data => x_msg_data
987 );
988 END IF;
989 x_return_status := FND_API.G_RET_STS_ERROR;
990 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
991 IF (x_msg_data IS NULL) THEN
992 FND_MSG_PUB.Count_And_Get
993 ( p_encoded => FND_API.G_FALSE
994 , p_count => x_msg_count
995 , p_data => x_msg_data
996 );
997 END IF;
998 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
999 WHEN NO_DATA_FOUND THEN
1000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1001 IF (x_msg_data IS NOT NULL) THEN
1002 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Update_Dataset ';
1003 ELSE
1004 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Update_Dataset ';
1005 END IF;
1006 WHEN OTHERS THEN
1007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1008 IF (x_msg_data IS NOT NULL) THEN
1009 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Update_Dataset ';
1010 ELSE
1011 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Update_Dataset ';
1012 END IF;
1013 end Update_Dataset; */
1014
1015 procedure Update_Dataset(
1016 p_commit IN varchar2 := FND_API.G_FALSE
1017 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1018 ,p_update_dset_calc IN BOOLEAN
1019 ,x_return_status OUT NOCOPY varchar2
1020 ,x_msg_count OUT NOCOPY number
1021 ,x_msg_data OUT NOCOPY varchar2
1022 ) is
1023
1024 begin
1025 x_return_status := FND_API.G_RET_STS_SUCCESS;
1026 BSC_DATASETS_PVT.Update_Dataset( p_commit
1027 ,p_Dataset_Rec
1028 ,x_return_status
1029 ,x_msg_count
1030 ,x_msg_data);
1031 IF (p_update_dset_calc) THEN
1032 Update_Dataset_Calc( p_commit
1033 ,p_Dataset_Rec
1034 ,x_return_status
1035 ,x_msg_count
1036 ,x_msg_data);
1037 END IF;
1038
1039 EXCEPTION
1040 WHEN FND_API.G_EXC_ERROR THEN
1041 IF (x_msg_data IS NULL) THEN
1042 FND_MSG_PUB.Count_And_Get
1043 ( p_encoded => FND_API.G_FALSE
1044 , p_count => x_msg_count
1045 , p_data => x_msg_data
1046 );
1047 END IF;
1048 x_return_status := FND_API.G_RET_STS_ERROR;
1049 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1050 IF (x_msg_data IS NULL) THEN
1051 FND_MSG_PUB.Count_And_Get
1052 ( p_encoded => FND_API.G_FALSE
1053 , p_count => x_msg_count
1054 , p_data => x_msg_data
1055 );
1056 END IF;
1057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1058 WHEN NO_DATA_FOUND THEN
1059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1060 IF (x_msg_data IS NOT NULL) THEN
1061 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Update_Dataset ';
1062 ELSE
1063 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Update_Dataset ';
1064 END IF;
1065 WHEN OTHERS THEN
1066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1067 IF (x_msg_data IS NOT NULL) THEN
1068 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Update_Dataset ';
1069 ELSE
1070 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Update_Dataset ';
1071 END IF;
1072 end Update_Dataset;
1073
1074
1075
1076 /****************************/
1077 procedure Update_Dataset(
1078 p_commit IN varchar2 := FND_API.G_FALSE
1079 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1080 ,x_return_status OUT NOCOPY varchar2
1081 ,x_msg_count OUT NOCOPY number
1082 ,x_msg_data OUT NOCOPY varchar2
1083 ) is
1084
1085 begin
1086 x_return_status := FND_API.G_RET_STS_SUCCESS;
1087 Update_Dataset(
1088 p_commit => p_commit
1089 ,p_Dataset_Rec => p_Dataset_Rec
1090 ,p_update_dset_calc => TRUE
1091 ,x_return_status => x_return_status
1092 ,x_msg_count => x_msg_count
1093 ,x_msg_data => x_msg_data
1094 );
1095
1096 EXCEPTION
1097 WHEN FND_API.G_EXC_ERROR THEN
1098 IF (x_msg_data IS NULL) THEN
1099 FND_MSG_PUB.Count_And_Get
1100 ( p_encoded => FND_API.G_FALSE
1101 , p_count => x_msg_count
1102 , p_data => x_msg_data
1103 );
1104 END IF;
1105 x_return_status := FND_API.G_RET_STS_ERROR;
1106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1107 IF (x_msg_data IS NULL) THEN
1108 FND_MSG_PUB.Count_And_Get
1109 ( p_encoded => FND_API.G_FALSE
1110 , p_count => x_msg_count
1111 , p_data => x_msg_data
1112 );
1113 END IF;
1114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1115 WHEN NO_DATA_FOUND THEN
1116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1117 IF (x_msg_data IS NOT NULL) THEN
1118 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Update_Dataset ';
1119 ELSE
1120 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Update_Dataset ';
1121 END IF;
1122 WHEN OTHERS THEN
1123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1124 IF (x_msg_data IS NOT NULL) THEN
1125 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Update_Dataset ';
1126 ELSE
1127 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Update_Dataset ';
1128 END IF;
1129 end Update_Dataset;
1130
1131
1132 /************************************************************************************
1133 ************************************************************************************/
1134
1135 procedure Delete_Dataset(
1136 p_commit IN varchar2 := FND_API.G_FALSE
1137 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1138 ,x_return_status OUT NOCOPY varchar2
1139 ,x_msg_count OUT NOCOPY number
1140 ,x_msg_data OUT NOCOPY varchar2
1141 ) is
1142
1143 begin
1144 x_return_status := FND_API.G_RET_STS_SUCCESS;
1145 -- mdamle 04/23/2003 - PMD - Measure Definer - Changed the order of deletion
1146 -- Delete from child table before deleting master record.
1147 Delete_Dataset_Calc( p_commit
1148 ,p_Dataset_Rec
1149 ,x_return_status
1150 ,x_msg_count
1151 ,x_msg_data);
1152
1153
1154 BSC_DATASETS_PVT.Delete_Dataset( p_commit
1155 ,p_Dataset_Rec
1156 ,x_return_status
1157 ,x_msg_count
1158 ,x_msg_data);
1159
1160
1161 EXCEPTION
1162 WHEN FND_API.G_EXC_ERROR THEN
1163 IF (x_msg_data IS NULL) THEN
1164 FND_MSG_PUB.Count_And_Get
1165 ( p_encoded => FND_API.G_FALSE
1166 , p_count => x_msg_count
1167 , p_data => x_msg_data
1168 );
1169 END IF;
1170 x_return_status := FND_API.G_RET_STS_ERROR;
1171 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1172 IF (x_msg_data IS NULL) THEN
1173 FND_MSG_PUB.Count_And_Get
1174 ( p_encoded => FND_API.G_FALSE
1175 , p_count => x_msg_count
1176 , p_data => x_msg_data
1177 );
1178 END IF;
1179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1180 WHEN NO_DATA_FOUND THEN
1181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1182 IF (x_msg_data IS NOT NULL) THEN
1183 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Delete_Dataset ';
1184 ELSE
1185 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Delete_Dataset ';
1186 END IF;
1187 WHEN OTHERS THEN
1188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1189 IF (x_msg_data IS NOT NULL) THEN
1190 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Delete_Dataset ';
1191 ELSE
1192 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Delete_Dataset ';
1193 END IF;
1194 end Delete_Dataset;
1195
1196 /************************************************************************************
1197 ************************************************************************************/
1198
1199 --: This procedure creates the necessary values for the disabled calc id
1200 --: for the given dimension.
1201 --: This procedure is part of the Data Set API.
1202
1203 procedure Create_Dataset_Calc(
1204 p_commit IN varchar2 := FND_API.G_FALSE
1205 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1206 ,x_return_status OUT NOCOPY varchar2
1207 ,x_msg_count OUT NOCOPY number
1208 ,x_msg_data OUT NOCOPY varchar2
1209 ) is
1210
1211 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
1212
1213 begin
1214 x_return_status := FND_API.G_RET_STS_SUCCESS;
1215 -- Assign all values in the passed "Record" parameter to the locally defined
1216 -- "Record" variable.
1217 l_Dataset_Rec := p_Dataset_Rec;
1218
1219 -- Loop for values between 3 and 9.
1220 for i in 3..9 loop
1221 -- Do not call procedure for value 5.
1222 if i <> 5 then
1223 -- Set the value for Disabled_Calc_Id equal to i.
1224 l_Dataset_Rec.Bsc_Disabled_Calc_Id := i;
1225 -- Call the private version of the procedure.
1226 BSC_DATASETS_PVT.Create_Dataset_Calc( p_commit
1227 ,l_Dataset_Rec
1228 ,x_return_status
1229 ,x_msg_count
1230 ,x_msg_data);
1231 end if;
1232 end loop;
1233
1234 EXCEPTION
1235 WHEN FND_API.G_EXC_ERROR THEN
1236 IF (x_msg_data IS NULL) THEN
1237 FND_MSG_PUB.Count_And_Get
1238 ( p_encoded => FND_API.G_FALSE
1239 , p_count => x_msg_count
1240 , p_data => x_msg_data
1241 );
1242 END IF;
1243 x_return_status := FND_API.G_RET_STS_ERROR;
1244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1245 IF (x_msg_data IS NULL) THEN
1246 FND_MSG_PUB.Count_And_Get
1247 ( p_encoded => FND_API.G_FALSE
1248 , p_count => x_msg_count
1249 , p_data => x_msg_data
1250 );
1251 END IF;
1252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1253 WHEN NO_DATA_FOUND THEN
1254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1255 IF (x_msg_data IS NOT NULL) THEN
1256 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Create_Dataset_Calc ';
1257 ELSE
1258 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Create_Dataset_Calc ';
1259 END IF;
1260 WHEN OTHERS THEN
1261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1262 IF (x_msg_data IS NOT NULL) THEN
1263 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Create_Dataset_Calc ';
1264 ELSE
1265 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Create_Dataset_Calc ';
1266 END IF;
1267 end Create_Dataset_Calc;
1268
1269 /************************************************************************************
1270 ************************************************************************************/
1271
1272 procedure Retrieve_Dataset_Calc(
1273 p_commit IN varchar2 := FND_API.G_FALSE
1274 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1275 ,x_Dataset_Rec IN OUT NOCOPY BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1276 ,x_return_status OUT NOCOPY varchar2
1277 ,x_msg_count OUT NOCOPY number
1278 ,x_msg_data OUT NOCOPY varchar2
1279 ) is
1280
1281 begin
1282 x_return_status := FND_API.G_RET_STS_SUCCESS;
1283 BSC_DATASETS_PVT.Retrieve_Dataset_Calc( p_commit
1284 ,p_Dataset_Rec
1285 ,x_Dataset_Rec
1286 ,x_return_status
1287 ,x_msg_count
1288 ,x_msg_data);
1289
1290 EXCEPTION
1291 WHEN FND_API.G_EXC_ERROR THEN
1292 IF (x_msg_data IS NULL) THEN
1293 FND_MSG_PUB.Count_And_Get
1294 ( p_encoded => FND_API.G_FALSE
1295 , p_count => x_msg_count
1296 , p_data => x_msg_data
1297 );
1298 END IF;
1299 x_return_status := FND_API.G_RET_STS_ERROR;
1300 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1301 IF (x_msg_data IS NULL) THEN
1302 FND_MSG_PUB.Count_And_Get
1303 ( p_encoded => FND_API.G_FALSE
1304 , p_count => x_msg_count
1305 , p_data => x_msg_data
1306 );
1307 END IF;
1308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1309 WHEN NO_DATA_FOUND THEN
1310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1311 IF (x_msg_data IS NOT NULL) THEN
1312 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Retrieve_Dataset_Calc ';
1313 ELSE
1314 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Retrieve_Dataset_Calc ';
1315 END IF;
1316 WHEN OTHERS THEN
1317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1318 IF (x_msg_data IS NOT NULL) THEN
1319 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Retrieve_Dataset_Calc ';
1320 ELSE
1321 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Retrieve_Dataset_Calc ';
1322 END IF;
1323 end Retrieve_Dataset_Calc;
1324
1325 /************************************************************************************
1326 ************************************************************************************/
1327
1328 procedure Update_Dataset_Calc(
1329 p_commit IN varchar2 := FND_API.G_FALSE
1330 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1331 ,x_return_status OUT NOCOPY varchar2
1332 ,x_msg_count OUT NOCOPY number
1333 ,x_msg_data OUT NOCOPY varchar2
1334 ) is
1335
1336 begin
1337 x_return_status := FND_API.G_RET_STS_SUCCESS;
1338 BSC_DATASETS_PVT.Update_Dataset_Calc( p_commit
1339 ,p_Dataset_Rec
1340 ,x_return_status
1341 ,x_msg_count
1342 ,x_msg_data);
1343
1344 EXCEPTION
1345 WHEN FND_API.G_EXC_ERROR THEN
1346 IF (x_msg_data IS NULL) THEN
1347 FND_MSG_PUB.Count_And_Get
1348 ( p_encoded => FND_API.G_FALSE
1349 , p_count => x_msg_count
1350 , p_data => x_msg_data
1351 );
1352 END IF;
1353 x_return_status := FND_API.G_RET_STS_ERROR;
1354 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1355 IF (x_msg_data IS NULL) THEN
1356 FND_MSG_PUB.Count_And_Get
1357 ( p_encoded => FND_API.G_FALSE
1358 , p_count => x_msg_count
1359 , p_data => x_msg_data
1360 );
1361 END IF;
1362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1363 WHEN NO_DATA_FOUND THEN
1364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1365 IF (x_msg_data IS NOT NULL) THEN
1366 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Update_Dataset_Calc ';
1367 ELSE
1368 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Update_Dataset_Calc ';
1369 END IF;
1370 WHEN OTHERS THEN
1371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1372 IF (x_msg_data IS NOT NULL) THEN
1373 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Update_Dataset_Calc ';
1374 ELSE
1375 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Update_Dataset_Calc ';
1376 END IF;
1377 end Update_Dataset_Calc;
1378
1379 /************************************************************************************
1380 ************************************************************************************/
1381
1382 procedure Delete_Dataset_Calc(
1383 p_commit IN varchar2 := FND_API.G_FALSE
1384 ,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1385 ,x_return_status OUT NOCOPY varchar2
1386 ,x_msg_count OUT NOCOPY number
1387 ,x_msg_data OUT NOCOPY varchar2
1388 ) is
1389
1390 begin
1391 x_return_status := FND_API.G_RET_STS_SUCCESS;
1392 BSC_DATASETS_PVT.Delete_Dataset_Calc( p_commit
1393 ,p_Dataset_Rec
1394 ,x_return_status
1395 ,x_msg_count
1396 ,x_msg_data);
1397
1398 EXCEPTION
1399 WHEN FND_API.G_EXC_ERROR THEN
1400 IF (x_msg_data IS NULL) THEN
1401 FND_MSG_PUB.Count_And_Get
1402 ( p_encoded => FND_API.G_FALSE
1403 , p_count => x_msg_count
1404 , p_data => x_msg_data
1405 );
1406 END IF;
1407 x_return_status := FND_API.G_RET_STS_ERROR;
1408 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1409 IF (x_msg_data IS NULL) THEN
1410 FND_MSG_PUB.Count_And_Get
1411 ( p_encoded => FND_API.G_FALSE
1412 , p_count => x_msg_count
1413 , p_data => x_msg_data
1414 );
1415 END IF;
1416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1417 WHEN NO_DATA_FOUND THEN
1418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1419 IF (x_msg_data IS NOT NULL) THEN
1420 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Delete_Dataset_Calc ';
1421 ELSE
1422 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Delete_Dataset_Calc ';
1423 END IF;
1424 WHEN OTHERS THEN
1425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1426 IF (x_msg_data IS NOT NULL) THEN
1427 x_msg_data := x_msg_data||' -> BSC_DATASETS_PUB.Delete_Dataset_Calc ';
1428 ELSE
1429 x_msg_data := SQLERRM||' at BSC_DATASETS_PUB.Delete_Dataset_Calc ';
1430 END IF;
1431 end Delete_Dataset_Calc;
1432
1433 /************************************************************************************
1434 ************************************************************************************/
1435 -- Code added for PMF uptaking PMD APIs
1436 --=============================================================================
1437 PROCEDURE Translate_Measure
1438 ( p_commit IN VARCHAR2
1439 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
1440 , p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1441 , x_return_status OUT NOCOPY VARCHAR2
1442 , x_msg_count OUT NOCOPY NUMBER
1443 , x_msg_data OUT NOCOPY VARCHAR2
1444 ) IS
1445
1446 BEGIN
1447 BSC_DATASETS_PVT.Translate_Measure(
1448 p_commit => FND_API.G_FALSE
1449 ,p_measure_rec => p_measure_rec
1450 ,p_Dataset_Rec => p_Dataset_Rec
1451 ,x_return_status => x_return_status
1452 ,x_msg_count => x_msg_count
1453 ,x_msg_data => x_msg_data
1454 );
1455
1456 EXCEPTION
1457 WHEN FND_API.G_EXC_ERROR THEN
1458 x_return_status := FND_API.G_RET_STS_ERROR;
1459 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1460 ,p_data => x_msg_data);
1461 RAISE;
1462 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1464 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1465 ,p_data => x_msg_data);
1466 RAISE;
1467 WHEN NO_DATA_FOUND 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 OTHERS 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
1478 END Translate_Measure;
1479 --============================================================================
1480
1481 -- mdamle 09/25/2003 - Sync up measures for all installed languages
1482 PROCEDURE Translate_Measure_By_Lang
1483 ( p_commit IN VARCHAR2
1484 , p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1485 , p_lang IN VARCHAR2
1486 , p_source_lang IN VARCHAR2
1487 , x_return_status OUT NOCOPY VARCHAR2
1488 , x_msg_count OUT NOCOPY NUMBER
1489 , x_msg_data OUT NOCOPY VARCHAR2
1490 ) IS
1491
1492 BEGIN
1493 BSC_DATASETS_PVT.Translate_Measure_By_Lang(
1494 p_commit => FND_API.G_FALSE
1495 ,p_Dataset_Rec => p_Dataset_Rec
1496 ,p_lang => p_lang
1497 ,p_source_lang => p_source_lang
1498 ,x_return_status => x_return_status
1499 ,x_msg_count => x_msg_count
1500 ,x_msg_data => x_msg_data
1501 );
1502
1503 EXCEPTION
1504 WHEN FND_API.G_EXC_ERROR THEN
1505 x_return_status := FND_API.G_RET_STS_ERROR;
1506 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1507 ,p_data => x_msg_data);
1508
1509 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1511 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1512 ,p_data => x_msg_data);
1513 WHEN NO_DATA_FOUND THEN
1514 x_return_status := FND_API.G_RET_STS_ERROR;
1515 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1516 ,p_data => x_msg_data);
1517 WHEN OTHERS THEN
1518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1519 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1520 ,p_data => x_msg_data);
1521 END Translate_Measure_By_Lang;
1522
1523 FUNCTION Get_DataSet_Name(
1524 p_DataSet_Id IN NUMBER
1525 ) RETURN VARCHAR2 IS
1526 CURSOR c_dataset_name IS
1527 SELECT name
1528 FROM bsc_sys_datasets_vl
1529 WHERE dataset_id = p_DataSet_Id;
1530 l_Name bsc_sys_datasets_vl.name%TYPE := NULL;
1531 BEGIN
1532
1533 OPEN c_dataset_name;
1534 FETCH c_dataset_name INTO l_Name;
1535 CLOSE c_dataset_name;
1536
1537 RETURN l_Name;
1538 EXCEPTION
1539 WHEN OTHERS THEN
1540 RETURN NULL;
1541 END Get_DataSet_Name;
1542
1543 FUNCTION Get_DataSet_Source(
1544 p_DataSet_Id IN NUMBER
1545 ) RETURN VARCHAR2 IS
1546 CURSOR c_dataset_source IS
1547 SELECT NVL(source,'BSC')
1548 FROM bsc_sys_datasets_vl
1549 WHERE dataset_id = p_DataSet_Id;
1550 l_Source bsc_sys_datasets_vl.source%TYPE;
1551 BEGIN
1552
1553 OPEN c_dataset_source;
1554 FETCH c_dataset_source INTO l_Source;
1555 CLOSE c_dataset_source;
1556
1557 RETURN l_Source;
1558 EXCEPTION
1559 WHEN OTHERS THEN
1560 RETURN 'BSC';
1561 END Get_DataSet_Source;
1562
1563 FUNCTION Get_DataSet_Full_Name(
1564 p_DataSet_Id IN NUMBER
1565 ) RETURN VARCHAR2
1566 IS
1567 l_Name VARCHAR2(1000);
1568 l_No_Report_Message fnd_new_messages.message_text%TYPE;
1569 CURSOR c_DataSet_Name(p_No_Report_Message VARCHAR2) IS
1570 SELECT d.name || ' [' ||
1571 NVL(DECODE(i.function_name, NULL,
1572 DECODE(i.actual_data_source, NULL, NULL,
1573 (SELECT user_function_name
1574 FROM fnd_form_functions_vl
1575 WHERE (
1576 (type = 'JSP' AND (web_html_call LIKE 'bisviewm.jsp%'
1577 OR web_html_call LIKE 'OA.jsp?page=/oracle/apps/bis/report/webui/BISReportPG%')
1578 )
1579 OR (type = 'WWW' AND LOWER(web_html_call) LIKE 'bisviewer.showreport%')
1580 )
1581 AND UPPER(parameters) LIKE '%PREGIONCODE=' ||
1582 UPPER(SUBSTR(i.actual_data_source, 1, INSTR(i.actual_data_source, '.') - 1)) || '%'
1583 AND type is not null
1584 AND parameters is not null
1585 AND web_html_call is not null
1586 AND rownum < 2
1587 )
1588 ),
1589 (SELECT ff.user_function_name FROM fnd_form_functions_vl ff WHERE ff.function_name = i.function_name)
1590 ) , p_No_Report_Message) || ']' name
1591 FROM
1592 bis_indicators i,bsc_sys_datasets_vl d
1593 WHERE
1594 i.dataset_id = d.dataset_id AND
1595 d.source = 'PMF' AND
1596 i.dataset_id = p_DataSet_Id
1597 UNION
1598 SELECT
1599 d.Name
1600 FROM
1601 bsc_sys_datasets_vl d
1602 WHERE
1603 d.dataset_id = p_DataSet_Id AND
1604 NVL(d.source,'BSC') <> 'PMF';
1605
1606 BEGIN
1607 l_No_Report_Message := fnd_message.get_string('BSC','BSC_NO_REPORT_AVAILABLE');
1608 OPEN c_DataSet_Name(l_No_Report_Message);
1609 FETCH c_DataSet_Name INTO l_Name;
1610 CLOSE c_DataSet_Name;
1611
1612 RETURN l_Name;
1613
1614 EXCEPTION
1615 WHEN OTHERS THEN
1616 RETURN NULL;
1617 END Get_DataSet_Full_Name;
1618
1619 end BSC_DATASETS_PUB;
[Home] [Help]