[Home] [Help]
PACKAGE BODY: APPS.BIS_MEASURE_PUB
Source
1 PACKAGE BODY BIS_MEASURE_PUB AS
2 /* $Header: BISPMEAB.pls 120.0 2005/06/01 17:02:39 appldev noship $ */
3 --
4 /*
5 REM +=======================================================================+
6 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
7 REM | All rights reserved. |
8 REM +=======================================================================+
9 REM | FILENAME |
10 REM | BISPMEAB.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Public API for creating and managing Performance Measurements
14 REM |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | 28-NOV-98 irchen Creation
19 REM |
20 REM | 27-JAN-03 arhegde For having different local variables for IN and OUT |
21 REM | parameters (bug#2758428) |
22 REM | 25-SEP-03 mdamle Bug#3160325 - Sync up measures for all installed |
23 REM | languages |
24 REM | 29-SEP-2003 adrao Bug#3160325 - Sync up measures for all installed |
25 REM | source languages |
26 REM | 12-NOV-2003 smargand added new function to determine whether the |
27 REM | given indicator is customized |
28 REM | 27-JUL-2004 sawu Propagated p_owner to PVT apis for create/update|
29 REM +=======================================================================+
30 */
31 --
32 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_MEASURE_PUB';
33
34
35 -- creates one Measure, with the dimensions sequenced in the order
36 -- they are passed in
37 Procedure Create_Measure
38 ( p_api_version IN NUMBER
39 , p_commit IN VARCHAR2 := FND_API.G_FALSE
40 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
41 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
42 , x_return_status OUT NOCOPY VARCHAR2
43 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
44 )
45 IS
46 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
47 l_measure_rec_p BIS_MEASURE_PUB.Measure_Rec_Type;
48 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
49 BEGIN
50
51 if (BIS_UTILITIES_PUB.Value_Missing
52 (p_Measure_Rec.Measure_id) = FND_API.G_TRUE
53 OR BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Measure_id)
54 = FND_API.G_TRUE) then
55 --changed to call Measure_Value_ID_Conversion
56 BIS_Measure_PVT.Measure_Value_ID_Conversion
57 ( p_api_version => p_api_version
58 , p_Measure_Rec => p_Measure_Rec
59 , x_Measure_Rec => l_Measure_Rec
60 , x_return_status => x_return_status
61 , x_error_Tbl => x_error_Tbl
62 );
63 --Added this check
64 IF( x_return_status = FND_API.G_RET_STS_SUCCESS) then
65 l_error_tbl := x_error_tbl;
66 BIS_UTILITIES_PVT.Add_Error_Message
67 ( p_error_msg_name => 'BIS_MEASURE_SHORT_NAME_UNIQUE'
68 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
69 , p_error_proc_name => G_PKG_NAME||'.Create_Measure'
70 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
71 , p_error_table => l_error_tbl
72 , x_error_table => x_error_tbl
73 );
74 RAISE FND_API.G_EXC_ERROR;
75 END IF;
76 END IF;
77
78
79
80 -- used the following proc instead and added the if check
81 l_measure_rec_p := l_Measure_Rec;
82 BIS_MEASURE_PVT.Dimension_Value_ID_Conversion
83 ( p_api_version => p_api_version
84 , p_Measure_Rec => l_measure_rec_p
85 , x_Measure_Rec => l_Measure_Rec
86 , x_return_status => x_return_status
87 , x_error_Tbl => x_error_tbl
88 );
89
90 --Added this check
91 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
92 RAISE FND_API.G_EXC_ERROR;
93 END IF;
94
95 BIS_Measure_PVT.Create_Measure
96 ( p_api_version => p_api_version
97 , p_commit => p_commit
98 , p_Measure_Rec => l_Measure_Rec
99 , p_owner => p_owner
100 , x_return_status => x_return_status
101 , x_error_Tbl => x_error_Tbl
102 );
103
104
105 EXCEPTION
106 when FND_API.G_EXC_ERROR then
107 x_return_status := FND_API.G_RET_STS_ERROR ;
108 when FND_API.G_EXC_UNEXPECTED_ERROR then
109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
110 when others then
111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
112 l_error_tbl := x_error_tbl;
113 BIS_UTILITIES_PVT.Add_Error_Message
114 ( p_error_msg_id => SQLCODE
115 , p_error_description => SQLERRM
116 , p_error_proc_name => G_PKG_NAME||'.Create_Measure'
117 , p_error_table => l_error_tbl
118 , x_error_table => x_error_tbl
119 );
120
121 END Create_Measure;
122 --
123 --
124 -- Gets All Performance Measures
125 -- If information about the dimensions are not required, set all_info to
126 -- FALSE
127 Procedure Retrieve_Measures
128 ( p_api_version IN NUMBER
129 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
130 , x_Measure_tbl OUT NOCOPY BIS_MEASURE_PUB.Measure_Tbl_Type
131 , x_return_status OUT NOCOPY VARCHAR2
132 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
133 )
134 IS
135 l_Measure_Rec BIS_Measure_PUB.Measure_Rec_Type;
136 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
137 BEGIN
138
139 BIS_Measure_PVT.Retrieve_Measures
140 ( p_api_version => p_api_version
141 , p_all_info => p_all_info
142 , x_Measure_Tbl => x_Measure_Tbl
143 , x_return_status => x_return_status
144 , x_error_Tbl => x_error_Tbl
145 );
146
147
148 EXCEPTION
149 when FND_API.G_EXC_ERROR then
150 x_return_status := FND_API.G_RET_STS_ERROR ;
151 when FND_API.G_EXC_UNEXPECTED_ERROR then
152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
153 when others then
154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
155 l_error_tbl := x_error_tbl;
156 BIS_UTILITIES_PVT.Add_Error_Message
157 ( p_error_msg_id => SQLCODE
158 , p_error_description => SQLERRM
159 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Measures'
160 , p_error_table => l_error_tbl
161 , x_error_table => x_error_tbl
162 );
163
164 END Retrieve_Measures;
165 --
166 --
167 -- Gets Information for One Performance Measure
168 -- If information about the dimension are not required, set all_info to FALSE.
169 Procedure Retrieve_Measure
170 ( p_api_version IN NUMBER
171 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
172 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
173 , x_Measure_Rec IN OUT NOCOPY BIS_MEASURE_PUB.Measure_Rec_Type
174 , x_return_status OUT NOCOPY VARCHAR2
175 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
176 )
177 IS
178
179 l_Measure_Rec BIS_Measure_PUB.Measure_Rec_Type;
180 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
181
182 BEGIN
183 l_measure_rec := p_measure_rec;
184
185 if (BIS_UTILITIES_PUB.Value_Missing
186 (p_Measure_Rec.Measure_id) = FND_API.G_TRUE
187 OR
188 BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Measure_id) = FND_API.G_TRUE)
189 THEN
190 --changed to call Measure_Value_ID_Conversion
191 BIS_Measure_PVT.Measure_Value_ID_Conversion
192 ( p_api_version => p_api_version
193 , p_Measure_Rec => p_Measure_Rec
194 , x_Measure_Rec => l_Measure_Rec
195 , x_return_status => x_return_status
196 , x_error_Tbl => x_error_Tbl
197 );
198
199 --Added this check
200 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
201 RAISE FND_API.G_EXC_ERROR;
202 END IF;
203 END IF;
204
205 BIS_Measure_PVT.Retrieve_Measure
206 ( p_api_version => p_api_version
207 , p_Measure_Rec => l_Measure_Rec
208 , p_all_info => p_all_info
209 , x_Measure_Rec => x_Measure_Rec
210 , x_return_status => x_return_status
211 , x_error_Tbl => x_error_Tbl
212 );
213
214
215 EXCEPTION
216 when FND_API.G_EXC_ERROR then
217 x_return_status := FND_API.G_RET_STS_ERROR ;
218 when FND_API.G_EXC_UNEXPECTED_ERROR then
219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
220 when others then
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
222 l_error_tbl := x_error_tbl;
223 BIS_UTILITIES_PVT.Add_Error_Message
224 ( p_error_msg_id => SQLCODE
225 , p_error_description => SQLERRM
226 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Measure'
227 , p_error_table => l_error_tbl
228 , x_error_table => x_error_tbl
229 );
230
231 END Retrieve_Measure;
232 --
233 --
234 -- PLEASE VERIFY COMMENT BELOW
235 -- Update_Measures one Measure if
236 -- 1) no Measure levels or targets exist
237 -- 2) no users have selected to see actuals for the Measure
238 Procedure Update_Measure
239 ( p_api_version IN NUMBER
240 , p_commit IN VARCHAR2 := FND_API.G_FALSE
241 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
242 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
243 , x_return_status OUT NOCOPY VARCHAR2
244 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
245 )
246 IS
247 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
248 l_measure_rec_p BIS_MEASURE_PUB.Measure_Rec_Type;
249 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
250 BEGIN
251
252 l_measure_rec := p_measure_rec;
253
254 if (BIS_UTILITIES_PUB.Value_Missing
255 (p_Measure_Rec.Measure_id) = FND_API.G_TRUE
256 OR BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Measure_id)
257 = FND_API.G_TRUE) then
258 --changed to call Measure_Value_ID_Conversion
259 BIS_Measure_PVT.Measure_Value_ID_Conversion
260 ( p_api_version => p_api_version
261 , p_Measure_Rec => p_Measure_Rec
262 , x_Measure_Rec => l_Measure_Rec
263 , x_return_status => x_return_status
264 , x_error_Tbl => x_error_Tbl
265 );
266 --added this check
267 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
268 RAISE FND_API.G_EXC_ERROR;
269 END IF;
270 END IF;
271
272 --added this call
273 l_measure_rec_p := l_Measure_Rec;
274 BIS_Measure_PVT.Dimension_Value_ID_Conversion
275 ( p_api_version => p_api_version
276 , p_Measure_Rec => l_measure_rec_p
277 , x_Measure_Rec => l_Measure_Rec
278 , x_return_status => x_return_status
279 , x_error_Tbl => x_error_Tbl
280 );
281 --added this check
282 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
283 RAISE FND_API.G_EXC_ERROR;
284 END IF;
285
286 BIS_Measure_PVT.Update_Measure
287 ( p_api_version => p_api_version
288 , p_commit => p_commit
289 , p_Measure_Rec => l_Measure_Rec
290 , p_owner => p_owner
291 , x_return_status => x_return_status
292 , x_error_Tbl => x_error_Tbl
293 );
294
295
296 EXCEPTION
297 when FND_API.G_EXC_ERROR then
298 x_return_status := FND_API.G_RET_STS_ERROR ;
299 when FND_API.G_EXC_UNEXPECTED_ERROR then
300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
301 when others then
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
303 l_error_tbl := x_error_tbl;
304 BIS_UTILITIES_PVT.Add_Error_Message
305 ( p_error_msg_id => SQLCODE
306 , p_error_description => SQLERRM
307 , p_error_proc_name => G_PKG_NAME||'.Update_Measure'
308 , p_error_table => l_error_tbl
309 , x_error_table => x_error_tbl
310 );
311
312 END Update_Measure;
313 --
314 --
315 -- PLEASE VERIFY COMMENT BELOW
316 -- deletes one Measure if
317 -- 1) no Measure levels, targets exist and
318 -- 2) the Measure access has not been granted to a resonsibility
319 -- 3) no users have selected to see actuals for the Measure
320 Procedure Delete_Measure
321 ( p_api_version IN NUMBER
322 , p_commit IN VARCHAR2 := FND_API.G_FALSE
323 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
324 , x_return_status OUT NOCOPY VARCHAR2
325 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
326 )
327 IS
328 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
329 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
330 BEGIN
331
332 l_Measure_Rec := p_Measure_rec;
333 if (BIS_UTILITIES_PUB.Value_Missing
334 (p_Measure_Rec.Measure_id) = FND_API.G_TRUE
335 OR BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Measure_id)
336 = FND_API.G_TRUE) then
337 --changed to call Measure_Value_ID_Conversion
338 --used to call Value_ID_Conv with shortname
339 BIS_Measure_PVT.Measure_Value_ID_Conversion
340 ( p_api_version => p_api_version
341 , p_Measure_Rec => p_Measure_Rec
342 , x_Measure_Rec => l_Measure_Rec
343 , x_return_status => x_return_status
344 , x_error_Tbl => x_error_Tbl
345 );
346 --added this check
347 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
348 RAISE FND_API.G_EXC_ERROR;
349 END IF;
350
351 end if;
352
353 BIS_Measure_PVT.Delete_Measure
354 ( p_api_version => p_api_version
355 , p_commit => p_commit
356 , p_Measure_Rec => l_Measure_Rec
357 , x_return_status => x_return_status
358 , x_error_Tbl => x_error_Tbl
359 );
360
361 EXCEPTION
362 when FND_API.G_EXC_ERROR then
363 x_return_status := FND_API.G_RET_STS_ERROR ;
364 when FND_API.G_EXC_UNEXPECTED_ERROR then
365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
366 when others then
367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
368 l_error_tbl := x_error_tbl;
369 BIS_UTILITIES_PVT.Add_Error_Message
370 ( p_error_msg_id => SQLCODE
371 , p_error_description => SQLERRM
372 , p_error_proc_name => G_PKG_NAME||'.Delete_Measure'
373 , p_error_table => l_error_tbl
374 , x_error_table => x_error_tbl
375 );
376
377 END Delete_Measure;
378 --
379 --
380 -- Validates measure
381 PROCEDURE Validate_Measure
382 ( p_api_version IN NUMBER
383 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
384 , x_return_status OUT NOCOPY VARCHAR2
385 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
386 )
387 IS
388 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
389 l_measure_rec_p BIS_MEASURE_PUB.Measure_Rec_Type;
390 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
391 BEGIN
392 l_Measure_Rec := p_Measure_Rec;
393 --changed to call Measure_Value_ID_Conversion
394 BIS_Measure_PVT.Measure_Value_ID_Conversion
395 ( p_api_version => p_api_version
396 , p_Measure_Rec => p_Measure_Rec
397 , x_Measure_Rec => l_Measure_Rec
398 , x_return_status => x_return_status
399 , x_error_Tbl => x_error_Tbl
400 );
401 --added this check
402 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
406 --added this call
403 RAISE FND_API.G_EXC_ERROR;
404 END IF;
405
407 l_measure_rec_p := l_Measure_Rec;
408 BIS_Measure_PVT.Dimension_Value_ID_Conversion
409 ( p_api_version => p_api_version
410 , p_Measure_Rec => l_measure_rec_p
411 , x_Measure_Rec => l_Measure_Rec
412 , x_return_status => x_return_status
413 , x_error_Tbl => x_error_Tbl
414 );
415 --added this check
416 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
417 RAISE FND_API.G_EXC_ERROR;
418 END IF;
419
420 BIS_Measure_PVT.Validate_Measure
421 ( p_api_version => p_api_version
422 , p_Measure_Rec => l_Measure_Rec
423 , x_return_status => x_return_status
424 , x_error_Tbl => x_error_Tbl
425 );
426
427
428 EXCEPTION
429 when FND_API.G_EXC_ERROR then
430 x_return_status := FND_API.G_RET_STS_ERROR ;
431 when FND_API.G_EXC_UNEXPECTED_ERROR then
432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
433 when others then
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
435 l_error_tbl := x_error_tbl;
436 BIS_UTILITIES_PVT.Add_Error_Message
437 ( p_error_msg_id => SQLCODE
438 , p_error_description => SQLERRM
439 , p_error_proc_name => G_PKG_NAME||'.Validate_Measure'
440 , p_error_table => l_error_tbl
441 , x_error_table => x_error_tbl
442 );
443
444 END Validate_Measure;
445 --
446 Procedure Retrieve_Measure_Dimensions
447 ( p_api_version IN NUMBER
448 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
449 , x_dimension_Tbl OUT NOCOPY BIS_DIMENSION_PUB.Dimension_Tbl_Type
450 , x_return_status OUT NOCOPY VARCHAR2
451 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
452 )
453 IS
454 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
455 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
456 BEGIN
457
458 l_measure_rec := p_measure_rec;
459
460 if (BIS_UTILITIES_PUB.Value_Missing
461 (l_Measure_Rec.Measure_id) = FND_API.G_TRUE
462 OR BIS_UTILITIES_PUB.Value_NULL(l_Measure_Rec.Measure_id)
463 = FND_API.G_TRUE) then
464 --changed to call Measure_Value_ID_Conversion
465 --used to call Value_ID_Conversion with short name
466 BIS_MEASURE_PVT.Measure_Value_ID_Conversion
467 ( p_api_version => p_api_version
468 , p_Measure_Rec => p_Measure_Rec
469 , x_Measure_Rec => l_Measure_Rec
470 , x_return_status => x_return_status
471 , x_error_Tbl => x_error_Tbl
472 );
473 --added this check
474 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
475 RAISE FND_API.G_EXC_ERROR;
476 END IF;
477 end if;
478
479 BIS_Measure_PVT.Retrieve_Measure_Dimensions
480 ( p_api_version => p_api_version
481 , p_Measure_Rec => l_Measure_Rec
482 , x_dimension_Tbl => x_dimension_Tbl
483 , x_return_status => x_return_status
484 , x_error_Tbl => x_error_Tbl
485 );
486
487
488 EXCEPTION
489 when FND_API.G_EXC_ERROR then
490 x_return_status := FND_API.G_RET_STS_ERROR ;
491 when FND_API.G_EXC_UNEXPECTED_ERROR then
492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
493 when others then
494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
495 l_error_tbl := x_error_tbl;
496 BIS_UTILITIES_PVT.Add_Error_Message
497 ( p_error_msg_id => SQLCODE
498 , p_error_description => SQLERRM
499 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Measure_Dimensions'
500 , p_error_table => l_error_tbl
501 , x_error_table => x_error_tbl
502 );
503
504 END Retrieve_Measure_Dimensions;
505 --
506 --
507 Procedure Translate_Measure
508 ( p_api_version IN NUMBER
509 , p_commit IN VARCHAR2 := FND_API.G_FALSE
510 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
511 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
512 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
513 , x_return_status OUT NOCOPY VARCHAR2
514 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
515 )
516 IS
517 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
518 l_measure_rec_p BIS_MEASURE_PUB.Measure_Rec_Type;
519 l_return_status VARCHAR2(10);
520 l_return_msg VARCHAR2(32000);
521 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
522 BEGIN
523
524 bis_utilities_pvt.init_debug_flag -- 2694978
525 ( x_return_status => l_return_status
526 , x_return_msg => l_return_msg
527 );
528
529 l_Measure_Rec := p_Measure_Rec;
530
531 --changed to call Measure_Value_ID_Conversion
532 BIS_MEASURE_PVT.Measure_Value_ID_Conversion
533 ( p_api_version => p_api_version
534 , p_Measure_Rec => p_Measure_Rec
535 , x_Measure_Rec => l_Measure_Rec
536 , x_return_status => x_return_status
537 , x_error_Tbl => x_error_tbl
538 );
539
540 --just if check
541 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
542 RAISE FND_API.G_EXC_ERROR;
543 end if;
544
545 --added this call
546 l_measure_rec_p := l_Measure_Rec;
550 , x_Measure_Rec => l_Measure_Rec
547 BIS_Measure_PVT.Dimension_Value_ID_Conversion
548 ( p_api_version => p_api_version
549 , p_Measure_Rec => l_measure_rec_p
551 , x_return_status => x_return_status
552 , x_error_Tbl => x_error_Tbl
553 );
554
555 --added this check
556 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
557 RAISE FND_API.G_EXC_ERROR;
558 END IF;
559
560 BIS_MEASURE_PVT.Translate_Measure
561 ( p_api_version => p_api_version
562 , p_commit => p_commit
563 , p_validation_level => p_validation_level
564 , p_Measure_Rec => l_Measure_Rec
565 , p_owner => p_owner
566 , x_return_status => x_return_status
567 , x_error_Tbl => x_error_Tbl
568 );
569
570 EXCEPTION
571 WHEN NO_DATA_FOUND THEN
572 x_return_status := FND_API.G_RET_STS_ERROR ;
573 when FND_API.G_EXC_ERROR then
574 x_return_status := FND_API.G_RET_STS_ERROR ;
575 when FND_API.G_EXC_UNEXPECTED_ERROR then
576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
577 when others then
578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
579 l_error_tbl := x_error_tbl;
580 BIS_UTILITIES_PVT.Add_Error_Message
581 ( p_error_msg_id => SQLCODE
582 , p_error_description => SQLERRM
583 , p_error_proc_name => G_PKG_NAME||'.Translate_Measure'
584 , p_error_table => l_error_tbl
585 , x_error_table => x_error_tbl
586 );
587
588 END Translate_Measure ;
589 --
590 Procedure Load_Measure
591 ( p_api_version IN NUMBER
592 , p_commit IN VARCHAR2 := FND_API.G_FALSE
593 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
594 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
595 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
596 , x_return_status OUT NOCOPY VARCHAR2
597 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
598 )
599 IS
600 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
601 l_measure_rec_p BIS_MEASURE_PUB.Measure_Rec_Type;
602 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
603 BEGIN
604
605 l_measure_rec := p_measure_rec;
606 x_return_status := FND_API.G_RET_STS_SUCCESS;
607 BEGIN
608 --added check
609 if (BIS_UTILITIES_PUB.Value_Missing
610 (p_Measure_Rec.Measure_id) = FND_API.G_TRUE
611 OR BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Measure_id)
612 = FND_API.G_TRUE) then
613 --changed to call Measure_Value_ID_Conversion
614 BIS_MEASURE_PVT.Measure_Value_ID_Conversion
615 ( p_api_version => p_api_version
616 , p_Measure_Rec => p_Measure_Rec
617 , x_Measure_Rec => l_Measure_Rec
618 , x_return_status => x_return_status
619 , x_error_Tbl => x_error_tbl
620 );
621 end if;
622
623 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
624 l_measure_rec_p := l_Measure_Rec;
625 --added this call
626 BIS_Measure_PVT.Dimension_Value_ID_Conversion
627 ( p_api_version => p_api_version
628 , p_Measure_Rec => l_measure_rec_p
629 , x_Measure_Rec => l_Measure_Rec
630 , x_return_status => x_return_status
631 , x_error_Tbl => x_error_Tbl
632 );
633 --added this check
634 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
635 RAISE FND_API.G_EXC_ERROR;
636 END IF;
637
638 BIS_MEASURE_PVT.Create_Measure
639 ( p_api_version => p_api_version
640 , p_commit => p_commit
641 , p_validation_level => p_validation_level
642 , p_Measure_Rec => l_Measure_Rec
643 , p_owner => p_owner
644 , x_return_status => x_return_status
645 , x_error_Tbl => x_error_Tbl
646 );
647 ELSE
648 --added this call
649 l_measure_rec_p := l_Measure_Rec;
650 BIS_Measure_PVT.Dimension_Value_ID_Conversion
651 ( p_api_version => p_api_version
652 , p_Measure_Rec => l_measure_rec_p
653 , x_Measure_Rec => l_Measure_Rec
654 , x_return_status => x_return_status
655 , x_error_Tbl => x_error_Tbl
656 );
657 --added this check
658 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
659 RAISE FND_API.G_EXC_ERROR;
660 END IF;
661
662 BIS_MEASURE_PVT.Update_Measure
663 ( p_api_version => p_api_version
664 , p_commit => p_commit
665 , p_validation_level => p_validation_level
666 , p_Measure_Rec => l_Measure_Rec
667 , p_owner => p_owner
668 , x_return_status => x_return_status
669 , x_error_Tbl => x_error_Tbl
670 );
671
672 END IF;
673
674 END;
675
676 EXCEPTION
677 WHEN NO_DATA_FOUND THEN
678 x_return_status := FND_API.G_RET_STS_ERROR ;
679 when FND_API.G_EXC_ERROR then
680 x_return_status := FND_API.G_RET_STS_ERROR ;
681 when FND_API.G_EXC_UNEXPECTED_ERROR then
682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
683 when others then
684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
688 , p_error_description => SQLERRM
685 l_error_tbl := x_error_tbl;
686 BIS_UTILITIES_PVT.Add_Error_Message
687 ( p_error_msg_id => SQLCODE
689 , p_error_proc_name => G_PKG_NAME||'.Load_Measure'
690 , p_error_table => l_error_tbl
691 , x_error_table => x_error_tbl
692 );
693
694 END Load_Measure ;
695
696
697 --
698 --Overload Load_Measure so that old data model ldts can be uploaded using
699 --The latest lct file. The lct file can call this overloaded procedure
700 --by passing in Org and Time dimension short_names also
701 Procedure Load_Measure
702 ( p_api_version IN NUMBER
703 , p_commit IN VARCHAR2 := FND_API.G_FALSE
704 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
705 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
706 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
707 , p_Org_Dimension_Short_Name IN VARCHAR2
708 , p_Time_Dimension_Short_Name IN VARCHAR2
709 , x_return_status OUT NOCOPY VARCHAR2
710 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
711 )
712 IS
713 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
714 l_measure_rec_p BIS_MEASURE_PUB.Measure_Rec_Type;
715 l_Org_Dimension_ID NUMBER;
716 l_Time_Dimension_ID NUMBER;
717 l_msg VARCHAR2(3000);
718 l_return_status VARCHAR2(10);
719 l_return_msg VARCHAR2(32000);
720 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
721
722 BEGIN
723
724 bis_utilities_pvt.init_debug_flag -- 2694978
725 ( x_return_status => l_return_status
726 , x_return_msg => l_return_msg
727 );
728
729 -- BIS_UTILITIES_PUB.put_line(p_text =>'XXXXXXXXX');
730
731 /*
732 fnd_message.set_name('BIS', 'BIS_KPI_NOT_CREATED');
733 fnd_message.set_token('NAME', p_measure_rec.measure_name);
734 l_msg := fnd_message.get;
735 */
736
737 l_msg := 'The Performance Measure ' || p_measure_rec.measure_name ;
738 l_msg := l_msg || ' could not be created/updated.';
739
740
741 l_measure_rec := p_measure_rec;
742 x_return_status := FND_API.G_RET_STS_SUCCESS;
743
744
745 IF (BIS_UTILITIES_PUB.Value_Missing
746 (p_Measure_Rec.Measure_id) = FND_API.G_TRUE
747 OR BIS_UTILITIES_PUB.Value_NULL(p_Measure_Rec.Measure_id)
748 = FND_API.G_TRUE) then
749 BIS_MEASURE_PVT.Measure_Value_ID_Conversion
750 ( p_api_version => p_api_version
751 , p_Measure_Rec => p_Measure_Rec
752 , x_Measure_Rec => l_Measure_Rec
753 , x_return_status => x_return_status
754 , x_error_Tbl => x_error_tbl
755 );
756 END IF;
757
758
759 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
760 l_measure_rec_p := l_Measure_Rec;
761 BIS_Measure_PVT.Dimension_Value_ID_Conversion
762 ( p_api_version => p_api_version
763 , p_Measure_Rec => l_measure_rec_p
764 , x_Measure_Rec => l_Measure_Rec
765 , x_return_status => x_return_status
766 , x_error_Tbl => x_error_Tbl
767 );
768
769 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
770 RAISE FND_API.G_EXC_ERROR;
771 END IF;
772
773
774 IF (BIS_UTILITIES_PUB.Value_Not_Missing
775 (p_Org_Dimension_Short_Name) = FND_API.G_TRUE
776 AND BIS_UTILITIES_PUB.Value_Not_NULL(p_Org_Dimension_Short_Name)
777 = FND_API.G_TRUE) then
778 BIS_DIMENSION_PVT.Value_ID_Conversion
779 ( p_api_version => p_api_version
780 , p_Dimension_Short_Name => p_Org_Dimension_Short_Name
781 , p_Dimension_Name => FND_API.G_MISS_CHAR
782 , x_Dimension_ID => l_Org_Dimension_ID
783 , x_return_status => x_return_status
784 , x_error_Tbl => x_error_Tbl
785 );
786
787 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
788 RAISE FND_API.G_EXC_ERROR;
789 END IF;
790
791 END IF;
792
793 -- added this call for getting Time Dimension Id
794 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_Time_Dimension_Short_Name) = FND_API.G_TRUE
795 AND BIS_UTILITIES_PUB.Value_Not_NULL(p_Time_Dimension_Short_Name)
796 = FND_API.G_TRUE) THEN
797 BIS_DIMENSION_PVT.Value_ID_Conversion
798 ( p_api_version => p_api_version
799 , p_Dimension_Short_Name => p_Time_Dimension_Short_Name
800 , p_Dimension_Name => FND_API.G_MISS_CHAR
801 , x_Dimension_ID => l_Time_Dimension_ID
802 , x_return_status => x_return_status
803 , x_error_Tbl => x_error_Tbl
804 );
805
806 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
807 RAISE FND_API.G_EXC_ERROR;
808 END IF;
809
810 END IF;
811
812 -- Changed to call the new overloaded Create_Measure
813 BIS_MEASURE_PVT.Create_Measure
814 ( p_api_version => p_api_version
815 , p_commit => p_commit
816 , p_validation_level => p_validation_level
817 , p_Measure_Rec => l_Measure_Rec
818 , p_owner => p_owner
819 , p_Org_Dimension_ID => l_Org_Dimension_ID
820 , p_Time_Dimension_ID => l_Time_Dimension_ID
821 , x_return_status => x_return_status
822 , x_error_Tbl => x_error_Tbl
823 );
824
825 ELSE
826 l_measure_rec_p := l_Measure_Rec;
827 BIS_Measure_PVT.Dimension_Value_ID_Conversion
828 ( p_api_version => p_api_version
829 , p_Measure_Rec => l_measure_rec_p
830 , x_Measure_Rec => l_Measure_Rec
831 , x_return_status => x_return_status
832 , x_error_Tbl => x_error_Tbl
833 );
834
835 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
836 RAISE FND_API.G_EXC_ERROR;
837 END IF;
838
839 -- added this call for getting Org Dimension Id
840 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_Org_Dimension_Short_Name) = FND_API.G_TRUE
841 AND BIS_UTILITIES_PUB.Value_Not_NULL(p_Org_Dimension_Short_Name)
842 = FND_API.G_TRUE) THEN
843 BIS_DIMENSION_PVT.Value_ID_Conversion
844 ( p_api_version => p_api_version
845 , p_Dimension_Short_Name => p_Org_Dimension_Short_Name
846 , p_Dimension_Name => FND_API.G_MISS_CHAR
847 , x_Dimension_ID => l_Org_Dimension_ID
848 , x_return_status => x_return_status
849 , x_error_Tbl => x_error_Tbl
850 );
851
852 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
853 RAISE FND_API.G_EXC_ERROR;
854 END IF;
855
856 END IF;
857
858 -- added this call for getting Time Dimension Id
859 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_Time_Dimension_Short_Name) = FND_API.G_TRUE
860 AND BIS_UTILITIES_PUB.Value_Not_NULL(p_Time_Dimension_Short_Name)
861 = FND_API.G_TRUE) THEN
862
863 BIS_DIMENSION_PVT.Value_ID_Conversion
864 ( p_api_version => p_api_version
865 , p_Dimension_Short_Name => p_Time_Dimension_Short_Name
866 , p_Dimension_Name => FND_API.G_MISS_CHAR
867 , x_Dimension_ID => l_Time_Dimension_ID
868 , x_return_status => x_return_status
869 , x_error_Tbl => x_error_Tbl
870 );
871
872 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
873 RAISE FND_API.G_EXC_ERROR;
874 END IF;
875
876 END IF;
877
878 -- Changed to call the new overloaded Create_Measure
879 BIS_MEASURE_PVT.Update_Measure
880 ( p_api_version => p_api_version
881 , p_commit => p_commit
882 , p_validation_level => p_validation_level
883 , p_Measure_Rec => l_Measure_Rec
884 , p_owner => p_owner
885 , p_Org_Dimension_ID => l_Org_Dimension_ID
886 , p_Time_Dimension_ID => l_Time_Dimension_ID
887 , x_return_status => x_return_status
888 , x_error_Tbl => x_error_Tbl
889 );
890
891 END IF;
892
893 EXCEPTION
894 WHEN NO_DATA_FOUND THEN
895 BIS_UTILITIES_PUB.put_line(p_text =>l_msg);
896 x_return_status := FND_API.G_RET_STS_ERROR ;
897 when FND_API.G_EXC_ERROR then
898 BIS_UTILITIES_PUB.put_line(p_text =>l_msg);
899 x_return_status := FND_API.G_RET_STS_ERROR ;
900 when FND_API.G_EXC_UNEXPECTED_ERROR then
901 BIS_UTILITIES_PUB.put_line(p_text =>l_msg);
902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
903 when others then
904 BIS_UTILITIES_PUB.put_line(p_text =>l_msg);
905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
906 l_error_tbl := x_error_tbl;
907 BIS_UTILITIES_PVT.Add_Error_Message
908 ( p_error_msg_id => SQLCODE
909 , p_error_description => SQLERRM
910 , p_error_proc_name => G_PKG_NAME||'.Load_Measure'
911 , p_error_table => l_error_tbl
912 , x_error_table => x_error_tbl
913 );
914
915 END Load_Measure ;
916 --
917 -- Given a performance measure short name update the
918 -- bis_indicators, bis_indicators_tl and bis_indicator_dimensions
919 -- for last_updated_by , created_by as 1
920 PROCEDURE updt_pm_owner(p_pm_short_name IN VARCHAR2
921 ,x_return_status OUT NOCOPY VARCHAR2) AS
922 BEGIN
923 BIS_MEASURE_PVT.updt_pm_owner(p_pm_short_name => p_pm_short_name
924 ,x_return_status => x_return_status);
925 EXCEPTION
926 WHEN OTHERS THEN
927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
928 END updt_pm_owner;
929
930 -- mdamle 09/25/2003 - Sync up measures for all installed languages
931 Procedure Translate_Measure_By_lang
932 ( p_api_version IN NUMBER
933 , p_commit IN VARCHAR2 := FND_API.G_FALSE
934 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
935 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
936 , p_lang IN VARCHAR2
937 , p_source_lang IN VARCHAR2
938 , x_return_status OUT NOCOPY VARCHAR2
939 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
940 )
941 IS
942
943 BEGIN
944
945 BIS_MEASURE_PVT.Translate_Measure_By_lang
946 ( p_api_version => p_api_version
947 , p_commit => p_commit
948 , p_Measure_Rec => p_Measure_Rec
949 , p_owner => p_owner
950 , p_lang => p_lang
951 , p_source_lang => p_source_lang
952 , x_return_status => x_return_status
953 , x_error_Tbl => x_error_Tbl
954 );
955
956 EXCEPTION
957 WHEN NO_DATA_FOUND THEN
958 x_return_status := FND_API.G_RET_STS_ERROR ;
959 when FND_API.G_EXC_ERROR then
960 x_return_status := FND_API.G_RET_STS_ERROR ;
961 when FND_API.G_EXC_UNEXPECTED_ERROR then
962 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
963 when others then
964 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
965 BIS_UTILITIES_PVT.Add_Error_Message
966 ( p_error_msg_id => SQLCODE
967 , p_error_description => SQLERRM
968 , p_error_proc_name => G_PKG_NAME||'.Translate_Measure'
969 , p_error_table => x_error_tbl
970 , x_error_table => x_error_tbl
971 );
972
973 END Translate_Measure_by_lang ;
974 --added new function to determine whether the given indicator is customized
975
976 FUNCTION GET_CUSTOMIZED_ENABLED( p_indicator_id IN NUMBER )
977 RETURN VARCHAR2
978 IS
979 BEGIN
980 RETURN BIS_MEASURE_PVT.GET_CUSTOMIZED_ENABLED (p_indicator_id => p_indicator_id);
981
982 END GET_CUSTOMIZED_ENABLED;
983
984 END BIS_MEASURE_PUB;