[Home] [Help]
PACKAGE BODY: APPS.BIS_APPLICATION_MEASURE_PVT
Source
1 PACKAGE BODY BIS_Application_Measure_PVT AS
2 /* $Header: BISVAPMB.pls 120.0 2005/06/01 14:09:16 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 | BISVAPMB.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Public API for creating and managing Application Measures
14 REM |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | 28-NOV-98 irchen Creation
19 REM | 20-JAN-2003 rchandra added statements for OSERROR for GSCC
20 REM | 23-JAN-03 mahrao For having different local variables for IN and OUT
21 REM | parameters.
22 REM | 26-JUN-2003 rchandra bug 3004651, populated dataset_id in API |
23 REM | Retrieve_Application_Measures |
24 REM | 08-JUL-2003 mdamle Fix to support bad data |
25 REM | 08-JUL-2003 rchandra For bug 3008385,Only one record can be created in|
26 REM | BIS_APPICATION_MEASURES table for an indicator | |
27 REM | 27-JUL-2004 sawu Modified create/update application measure to use |
28 REM | BIS_UTILITIES_PUB.Get_Owner_Id to lookup user_id |
29 REM | 29-SEP-2004 ankgoel Added WHO columns in Rec for Bug#3891748 |
30 REM | 21-MAR-2005 ankagarw bug#4235732 - changing count(*) to count(1) |
31 REM | 01-JUN-2005 akoduri Modified for Bug #4397786 |
32 REM +=======================================================================+
33 */
34 --
35 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_APPLICATION_MEASURE_PVT';
36 --
37 --
38 PROCEDURE Create_Application_Measure
39 ( p_api_version IN NUMBER
40 , p_commit IN VARCHAR2 := FND_API.G_FALSE
41 , p_Application_Measure_Rec IN
42 BIS_Application_Measure_PVT.Application_Measure_Rec_Type
43 , x_return_status OUT NOCOPY VARCHAR2
44 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
45 )
46 IS
47 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
48 BEGIN
49
50 Create_Application_Measure
51 ( p_api_version => p_api_version
52 , p_commit => p_commit
53 , p_Application_Measure_Rec => p_Application_Measure_Rec
54 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
55 , x_return_status => x_return_status
56 , x_error_Tbl => x_error_Tbl
57 );
58
59 --commented RAISE
60 EXCEPTION
61 when others then
62 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
63 --added last two params
64 l_error_tbl := x_error_Tbl;
65 BIS_UTILITIES_PVT.Add_Error_Message
66 ( p_error_msg_id => SQLCODE
67 , p_error_description => SQLERRM
68 , p_error_proc_name => G_PKG_NAME||'.Create_Application_Measure'
69 , p_error_table => l_error_tbl
70 , x_error_table => x_error_tbl
71 );
72 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
73
74 END Create_Application_Measure;
75 --
76 --
77 PROCEDURE Create_Application_Measure
78 ( p_api_version IN NUMBER
79 , p_commit IN VARCHAR2 := FND_API.G_FALSE
80 , p_Application_Measure_Rec IN
81 BIS_Application_Measure_PVT.Application_Measure_Rec_Type
82 , p_owner IN VARCHAR2
83 , x_return_status OUT NOCOPY VARCHAR2
84 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
85 )
86 IS
87 l_error_count number;
88 l_user_id number;
89 l_login_id number;
90 l_rec BIS_Application_Measure_PVT.Application_Measure_Rec_Type;
91 l_rec_p BIS_Application_Measure_PVT.Application_Measure_Rec_Type;
92 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
93
94 CURSOR c_app_meas(cp_ind_id IN NUMBER) IS
95 SELECT count(1) FROM BIS_APPLICATION_MEASURES
96 WHERE indicator_id = cp_ind_id;
97 l_count NUMBER := 0;
98 BEGIN
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100 l_rec := p_Application_Measure_Rec;
101
102 l_rec_p := l_rec;
103 BIS_Application_Measure_PVT.Value_ID_Conversion
104 ( p_api_version
105 , l_rec_p
106 , l_rec
107 , x_return_status
108 , x_error_Tbl
109 );
110
111
112 BIS_Application_Measure_PVT.Validate_Application_Measure
113 ( p_api_version
114 , l_rec
115 , x_return_status
116 , x_error_Tbl
117 );
118
119 IF (c_app_meas%ISOPEN) THEN
120 CLOSE c_app_meas;
121 END IF;
122
123 OPEN c_app_meas(cp_ind_id => l_rec.Measure_ID);
124 FETCH c_app_meas INTO l_count;
125 CLOSE c_app_meas;
126
127 IF (l_count > 0) THEN
128 RAISE FND_API.G_EXC_ERROR ;
129 END IF;
130
131 IF ( l_Rec.APPLICATION_ID <> -1 ) THEN
132 l_Rec.OWNING_APPLICATION := FND_API.G_TRUE;
133 ELSE
134 l_Rec.OWNING_APPLICATION := FND_API.G_FALSE;
135 END IF;
136
137 -- ankgoel: bug#3891748 - Created_By will take precedence over Owner.
138 -- Last_Updated_By can be different from Created_By while creating measures
139 -- during sync-up
140 IF (l_Rec.Created_By IS NULL) THEN
141 l_Rec.Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
142 END IF;
143 IF (l_Rec.Last_Updated_By IS NULL) THEN
144 l_Rec.Last_Updated_By := l_Rec.Created_By;
145 END IF;
146 IF (l_Rec.Last_Update_Login IS NULL) THEN
147 l_Rec.Last_Update_Login := fnd_global.LOGIN_ID;
148 END IF;
149
150 -- dbms_output.put_line('Application Measures PVT: BEFORE INSERT');
151 -- dbms_output.put_line( l_Rec.Measure_ID);
152 -- dbms_output.put_line( l_Rec.APPLICATION_ID);
153 -- dbms_output.put_line( l_Rec.OWNING_APPLICATION);
154 -- dbms_output.put_line( l_user_id);
155 -- dbms_output.put_line(l_login_id );
156
157 insert into bis_application_measures
158 (
159 INDICATOR_ID
160 , APPLICATION_ID
161 , OWNING_APPLICATION
162 , CREATION_DATE
163 , CREATED_BY
164 , LAST_UPDATE_DATE
165 , LAST_UPDATED_BY
166 , LAST_UPDATE_LOGIN
167 )
168 values
169 ( l_Rec.Measure_ID
170 , l_Rec.APPLICATION_ID
171 , l_Rec.OWNING_APPLICATION
172 , SYSDATE
173 , l_Rec.Created_By
174 , SYSDATE
175 , l_Rec.Last_Updated_By
176 , l_Rec.Last_Update_Login
177 );
178
179 if (p_commit = FND_API.G_TRUE) then
180 COMMIT;
181 end if;
182
183 --commented RAISE
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 x_return_status := FND_API.G_RET_STS_ERROR ;
187 --RAISE FND_API.G_EXC_ERROR;
188 IF (c_app_meas%ISOPEN) THEN
189 CLOSE c_app_meas;
190 END IF;
191 when FND_API.G_EXC_ERROR then
192 x_return_status := FND_API.G_RET_STS_ERROR ;
193 --RAISE FND_API.G_EXC_ERROR;
194 IF (c_app_meas%ISOPEN) THEN
195 CLOSE c_app_meas;
196 END IF;
197 when FND_API.G_EXC_UNEXPECTED_ERROR then
198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
199 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
200 IF (c_app_meas%ISOPEN) THEN
201 CLOSE c_app_meas;
202 END IF;
203 when others then
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
205 l_error_tbl := x_error_Tbl;
206 IF (c_app_meas%ISOPEN) THEN
207 CLOSE c_app_meas;
208 END IF;
209 BIS_UTILITIES_PVT.Add_Error_Message
210 ( p_error_msg_id => SQLCODE
211 , p_error_description => SQLERRM
212 , p_error_proc_name => G_PKG_NAME||'.Create_Application_Measure'
213 , p_error_table => l_error_tbl
214 , x_error_table => x_error_tbl
215 );
216 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217
218 end Create_Application_Measure;
219 --
220 --
221 PROCEDURE Retrieve_Application_Measures
222 ( p_api_version IN NUMBER
223 , p_Measure_Rec IN BIS_Measure_PUB.Measure_Rec_Type
224 , p_all_info IN VARCHAR2
225 , x_Application_Measure_tbl OUT NOCOPY
226 BIS_Application_Measure_PVT.Application_Measure_Tbl_Type
227 , x_return_status OUT NOCOPY VARCHAR2
228 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
229 )
230 IS
231
232 CURSOR all_info_cursor is
233 select Measure_id
234 , MEASURE_SHORT_NAME
235 , MEASURE_NAME
236 , Application_id
237 , Application_Short_name
238 , Application_name
239 , owning_application
240 , Dataset_Id
241 from bisfv_application_measures
242 where measure_id = p_Measure_Rec.Measure_id;
243
244 CURSOR basic_info_cursor is
245 select Measure_id
246 , MEASURE_SHORT_NAME
247 , MEASURE_NAME
248 , Application_id
249 , owning_application
250 , Dataset_Id
251 from bisbv_application_measures
252 where measure_id = p_Measure_Rec.Measure_id;
253
254 l_rec BIS_Application_Measure_PVT.Application_Measure_Rec_Type;
255 l_flag number := 0;
256 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
257
258 BEGIN
259 x_return_status := FND_API.G_RET_STS_SUCCESS;
260
261 if (p_all_info = FND_API.G_FALSE) then
262 for cr in basic_info_cursor LOOP
263 l_flag := 1;
264 l_rec.Measure_id := cr.Measure_id;
265 l_rec.MEASURE_SHORT_NAME := cr.MEASURE_SHORT_NAME;
266 l_rec.MEASURE_NAME := cr.MEASURE_NAME;
267 l_rec.owning_application := cr.owning_application;
268 l_rec.Dataset_ID := cr.Dataset_Id;
269
270 x_Application_Measure_tbl(x_Application_Measure_tbl.COUNT+1) := l_rec;
271 end LOOP;
272 else
273 for cr in all_info_cursor LOOP
274 l_flag := 1;
275 l_rec.Measure_id := cr.Measure_id;
276 l_rec.MEASURE_SHORT_NAME := cr.MEASURE_SHORT_NAME;
277 l_rec.MEASURE_NAME := cr.MEASURE_NAME;
278 l_rec.Application_ID := cr.Application_id;
279 l_rec.Application_SHORT_NAME := cr.Application_SHORT_NAME;
280 l_rec.Application_NAME := cr.Application_NAME;
281 l_rec.owning_application := cr.owning_application;
282 l_rec.Dataset_ID := cr.Dataset_Id;
283
284 x_Application_Measure_tbl(x_Application_Measure_tbl.COUNT+1) := l_rec;
285 end LOOP;
286 end if;
287
288 IF basic_info_cursor%ISOPEN THEN CLOSE basic_info_cursor; END IF;
289 IF all_info_cursor%ISOPEN THEN CLOSE all_info_cursor; END IF;
290
291
292 --added this check
293 if (l_flag = 0) then
294 l_error_tbl := x_error_Tbl;
295 BIS_UTILITIES_PVT.Add_Error_Message
296 ( p_error_msg_name => 'BIS_INVALID_MEASURE_ID'
297 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
298 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Application_Measures'
299 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
300 , p_error_table => l_error_tbl
301 , x_error_table => x_error_tbl
302 );
303 RAISE FND_API.G_EXC_ERROR;
304 end if;
305
306 --commented RAISE
307 EXCEPTION
308 WHEN NO_DATA_FOUND THEN
309 x_return_status := FND_API.G_RET_STS_ERROR ;
310 IF basic_info_cursor%ISOPEN THEN CLOSE basic_info_cursor; END IF;
311 IF all_info_cursor%ISOPEN THEN CLOSE all_info_cursor; END IF;
312 --RAISE FND_API.G_EXC_ERROR;
313 when FND_API.G_EXC_ERROR then
314 x_return_status := FND_API.G_RET_STS_ERROR ;
315 IF basic_info_cursor%ISOPEN THEN CLOSE basic_info_cursor; END IF;
316 IF all_info_cursor%ISOPEN THEN CLOSE all_info_cursor; END IF;
317 -- RAISE FND_API.G_EXC_ERROR;
318 when FND_API.G_EXC_UNEXPECTED_ERROR then
319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
320 IF basic_info_cursor%ISOPEN THEN CLOSE basic_info_cursor; END IF;
321 IF all_info_cursor%ISOPEN THEN CLOSE all_info_cursor; END IF;
322 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
323 when others then
324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
325 IF basic_info_cursor%ISOPEN THEN CLOSE basic_info_cursor; END IF;
326 IF all_info_cursor%ISOPEN THEN CLOSE all_info_cursor; END IF;
327 l_error_tbl := x_error_Tbl;
328 BIS_UTILITIES_PVT.Add_Error_Message
329 ( p_error_msg_id => SQLCODE
330 , p_error_description => SQLERRM
331 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Application_Measures'
332 , p_error_table => l_error_tbl
333 , x_error_table => x_error_tbl
334 );
335 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
336
337 end Retrieve_Application_Measures;
338 --
339 --
340 PROCEDURE Update_Application_Measure
341 ( p_api_version IN NUMBER
342 , p_commit IN VARCHAR2 := FND_API.G_FALSE
343 , p_Application_Measure_Rec IN
344 BIS_Application_Measure_PVT.Application_Measure_Rec_Type
345 , x_return_status OUT NOCOPY VARCHAR2
346 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
347 )
348 IS
349 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
350 BEGIN
351
352 Update_Application_Measure
353 ( p_api_version => p_api_version
354 , p_commit => p_commit
355 , p_Application_Measure_Rec => p_Application_Measure_Rec
356 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
357 , x_return_status => x_return_status
358 , x_error_Tbl => x_error_Tbl
359 );
360
361 --commented RAISE
362 EXCEPTION
363 when others then
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
365 --added last two params
366 l_error_tbl := x_error_Tbl;
367 BIS_UTILITIES_PVT.Add_Error_Message
368 ( p_error_msg_id => SQLCODE
369 , p_error_description => SQLERRM
370 , p_error_proc_name => G_PKG_NAME||'.Update_Application_Measure'
371 , p_error_table => l_error_tbl
372 , x_error_table => x_error_tbl
373 );
374 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
375
376 END Update_Application_Measure;
377 --
378 --
379 PROCEDURE Update_Application_Measure
380 ( p_api_version IN NUMBER
381 , p_commit IN VARCHAR2 := FND_API.G_FALSE
382 , p_Application_Measure_Rec IN
383 BIS_Application_Measure_PVT.Application_Measure_Rec_Type
384 , p_owner IN VARCHAR2
385 , x_return_status OUT NOCOPY VARCHAR2
386 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
387 )
388 IS
389 l_user_id number;
390 l_login_id number;
394 l_count number;
391 l_rec BIS_Application_Measure_PVT.Application_Measure_Rec_Type;
392 l_rec_p BIS_Application_Measure_PVT.Application_Measure_Rec_Type;
393 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
395 BEGIN
396
397 x_return_status := FND_API.G_RET_STS_SUCCESS;
398 l_rec := p_Application_Measure_Rec;
399
400 if ( BIS_UTILITIES_PUB.Value_Missing(l_Rec.Owning_Application)=FND_API.G_TRUE
401 OR BIS_UTILITIES_PUB.Value_NULL(l_Rec.Owning_Application)=FND_API.G_TRUE
402 ) then
403 --added last two params
404 l_error_tbl := x_error_Tbl;
405 BIS_UTILITIES_PVT.Add_Error_Message
406 ( p_error_msg_name => 'BIS_INVALID_OWNING_APP_VALUE'
407 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
408 , p_error_proc_name => G_PKG_NAME||'.Update_Application_Measure'
409 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
410 , p_error_table => l_error_tbl
411 , x_error_table => x_error_tbl
412 );
413 RAISE FND_API.G_EXC_ERROR;
414 end if;
415 l_rec_p := l_rec;
416 BIS_Application_Measure_PVT.Value_ID_Conversion
417 ( p_api_version
418 , l_rec_p
419 , l_rec
420 , x_return_status
421 , x_error_Tbl
422 );
423
424 BIS_Application_Measure_PVT.Validate_Application_Measure
425 ( p_api_version
426 , p_Application_Measure_Rec
427 , x_return_status
428 , x_error_Tbl
429 );
430
431
432 l_user_id := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
433
434 l_login_id := fnd_global.LOGIN_ID;
435
436 -- mdamle 07/08/2003 - There have been occurrences of bad data when
437 -- an application_id record is missing for an indicator.
438 select count(1) into l_count
439 from bis_application_measures
440 where INDICATOR_ID = l_rec.Measure_Id;
441
442 if (l_count = 0) then
443 Create_Application_Measure
444 ( p_api_version => p_api_version
445 , p_commit => p_commit
446 , p_Application_Measure_Rec => l_rec
447 , p_owner => p_owner
448 , x_return_status => x_return_status
449 , x_error_tbl => x_error_tbl
450 );
451 else
452
453 IF ( l_Rec.APPLICATION_ID <> -1 ) THEN
454 l_Rec.OWNING_APPLICATION := FND_API.G_TRUE;
455 ELSE
456 l_Rec.OWNING_APPLICATION := FND_API.G_FALSE;
457 END IF;
458
459 Update bis_application_measures
460 set
461 OWNING_APPLICATION = l_Rec.OWNING_APPLICATION
462 , LAST_UPDATE_DATE = SYSDATE
463 , LAST_UPDATED_BY = l_user_id
464 , LAST_UPDATE_LOGIN = l_login_id
465 , APPLICATION_ID = l_Rec.Application_Id --2465354
466 where INDICATOR_ID = l_rec.Measure_Id ;
467 --AND Application_ID = l_Rec.Application_Id; --2465354
468 end if;
469 if (p_commit = FND_API.G_TRUE) then
470 COMMIT;
471 end if;
472
473 --commented RAISE
474 EXCEPTION
475 WHEN NO_DATA_FOUND THEN
476 x_return_status := FND_API.G_RET_STS_ERROR ;
477 --RAISE FND_API.G_EXC_ERROR;
478 when FND_API.G_EXC_ERROR then
479 x_return_status := FND_API.G_RET_STS_ERROR ;
480 --RAISE FND_API.G_EXC_ERROR;
481 when FND_API.G_EXC_UNEXPECTED_ERROR then
482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
483 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
484 when others then
485 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
486 l_error_tbl := x_error_Tbl;
487 BIS_UTILITIES_PVT.Add_Error_Message
488 ( p_error_msg_id => SQLCODE
489 , p_error_description => SQLERRM
490 , p_error_proc_name => G_PKG_NAME||'.Update_Application_Measure'
491 , p_error_table => l_error_tbl
492 , x_error_table => x_error_tbl
493 );
494 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
495
496 end Update_Application_Measure;
497 --
498 --
499 PROCEDURE Delete_Application_Measure
500 ( p_api_version IN NUMBER
501 , p_commit IN VARCHAR2 := FND_API.G_FALSE
502 , p_Application_Measure_Rec IN
503 BIS_Application_Measure_PVT.Application_Measure_Rec_Type
504 , x_return_status OUT NOCOPY VARCHAR2
505 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
506 )
507 IS
508 l_rec BIS_Application_Measure_PVT.Application_Measure_Rec_Type;
509 l_rec_p BIS_Application_Measure_PVT.Application_Measure_Rec_Type;
510 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
511
512 BEGIN
513 x_return_status := FND_API.G_RET_STS_SUCCESS;
514 l_rec := p_Application_Measure_Rec;
515 l_rec_p := l_rec;
516 BIS_Application_Measure_PVT.Value_ID_Conversion
517 ( p_api_version
518 , l_rec_p
519 , l_rec
520 , x_return_status
521 , x_error_Tbl
522 );
523
524 BIS_Application_Measure_PVT.Validate_Application_Measure
528 , x_error_Tbl
525 ( p_api_version
526 , l_rec
527 , x_return_status
529 );
530
531 delete from bis_application_measures
532 where APPLICATION_ID = l_Rec.Application_id
533 AND indicator_id = l_Rec.Measure_id;
534
535
536 if SQL%NOTFOUND then
537 RAISE NO_DATA_FOUND;
538 end if;
539
540 if (p_commit = FND_API.G_TRUE) then
541 COMMIT;
542 end if;
543
544 --commented RAISE
545 EXCEPTION
546 WHEN NO_DATA_FOUND THEN
547 x_return_status := FND_API.G_RET_STS_ERROR ;
548 --added more params
549 l_error_tbl := x_error_Tbl;
550 BIS_UTILITIES_PVT.Add_Error_Message
551 ( p_error_msg_name => 'BIS_INVALID_APPORMEASURE_ID'
552 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
553 , p_error_proc_name => G_PKG_NAME||'.Delete_Application_Measure'
554 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
555 , p_error_table => l_error_tbl
556 , x_error_table => x_error_tbl
557 );
558 --RAISE FND_API.G_EXC_ERROR;
559 when FND_API.G_EXC_ERROR then
560 x_return_status := FND_API.G_RET_STS_ERROR ;
561 --RAISE FND_API.G_EXC_ERROR;
562 when FND_API.G_EXC_UNEXPECTED_ERROR then
563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
564 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
565 when others then
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
567 l_error_tbl := x_error_Tbl;
568 BIS_UTILITIES_PVT.Add_Error_Message
569 ( p_error_msg_id => SQLCODE
570 , p_error_description => SQLERRM
571 , p_error_proc_name => G_PKG_NAME||'.Delete_Application_Measure'
572 , p_error_table => l_error_tbl
573 , x_error_table => x_error_tbl
574 );
575 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
576
577 end Delete_Application_Measure;
578 --
579 -- Validates measure
580 PROCEDURE Validate_Application_Measure
581 ( p_api_version IN NUMBER
582 ,p_Application_Measure_Rec IN
583 BIS_Application_Measure_PVT.Application_Measure_Rec_Type
584 , x_return_status OUT NOCOPY VARCHAR2
585 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
586 )
587 IS
588 l_measure_rec BIS_MEASURE_PUB.Measure_Rec_Type;
589 l_app_rec BIS_APPLICATION_PVT.Application_Rec_Type;
590 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
591 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
592 BEGIN
593 x_return_status := FND_API.G_RET_STS_SUCCESS;
594
595 -- if application id is -1 then we need to skip validating the application
596 if (p_Application_Measure_Rec.Application_id = -1) then
597 return;
598 end if;
599
600 BEGIN
601 l_measure_rec.measure_id := p_Application_Measure_Rec.Measure_id;
602 l_measure_rec.measure_short_name :=
603 p_Application_Measure_Rec.Measure_Short_name;
604 l_measure_rec.measure_name := p_Application_Measure_Rec.Measure_name;
605
606 BIS_MEASURE_PUB.Validate_Measure
607 ( p_api_version => 1.0
608 , p_Measure_Rec => l_measure_rec
609 , x_return_status => x_return_status
610 , x_error_Tbl => l_error_tbl
611 );
612
613 EXCEPTION
614 when FND_API.G_EXC_ERROR then
615 l_error_tbl_p := x_error_Tbl;
616 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
617 , l_error_Tbl
618 , x_error_tbl
619 );
620 x_return_status := FND_API.G_RET_STS_ERROR;
621 END;
622
623 BEGIN
624 l_app_rec.application_id := p_Application_Measure_Rec.Application_id;
625 l_app_rec.Application_short_name :=
626 p_Application_Measure_Rec.Application_Short_name;
627 l_app_rec.Application_name := p_Application_Measure_Rec.Application_name;
628
629 BIS_APPLICATION_PVT.Validate_Application
630 ( p_api_version => 1.0
631 , p_application_Rec => l_app_rec
632 , x_return_status => x_return_status
633 , x_error_Tbl => l_error_tbl
634 );
635 EXCEPTION
636 when FND_API.G_EXC_ERROR then
637 l_error_tbl_p := x_error_Tbl;
638 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
639 , l_error_Tbl
640 , x_error_tbl
641 );
642 x_return_status := FND_API.G_RET_STS_ERROR;
643 END;
644
645 --commented RAISE
646 EXCEPTION
647 WHEN NO_DATA_FOUND THEN
648 x_return_status := FND_API.G_RET_STS_ERROR ;
649 --RAISE FND_API.G_EXC_ERROR;
650 when FND_API.G_EXC_ERROR then
651 x_return_status := FND_API.G_RET_STS_ERROR ;
652 --RAISE FND_API.G_EXC_ERROR;
653 when FND_API.G_EXC_UNEXPECTED_ERROR then
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
655 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
656 when others then
657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
658 l_error_tbl_p := x_error_Tbl;
659 BIS_UTILITIES_PVT.Add_Error_Message
660 ( p_error_msg_id => SQLCODE
661 , p_error_description => SQLERRM
665 );
662 , p_error_proc_name => G_PKG_NAME||'.Validate_Application_Measure'
663 , p_error_table => l_error_tbl_p
664 , x_error_table => x_error_tbl
666 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667
668 end Validate_Application_Measure;
669 --
670 --
671 -- Value - ID conversion
672 PROCEDURE Value_ID_Conversion
673 ( p_api_version IN NUMBER
674 , p_Application_Measure_Rec IN
675 BIS_Application_Measure_PVT.Application_Measure_Rec_Type
676 , x_Application_Measure_Rec IN OUT NOCOPY
677 BIS_Application_Measure_PVT.Application_Measure_Rec_Type
678 , x_return_status OUT NOCOPY VARCHAR2
679 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
680 )
681 IS
682 -- l_error_count number;
683 l_measure_rec BIS_MEASURE_PUB.Measure_Rec_Type;
684 l_app_rec BIS_APPLICATION_PVT.Application_Rec_Type;
685 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
686 l_measure_rec_p BIS_MEASURE_PUB.Measure_Rec_Type;
687 l_app_rec_p BIS_APPLICATION_PVT.Application_Rec_Type;
688 BEGIN
689 x_return_status := FND_API.G_RET_STS_SUCCESS;
690
691 -- Not done because redundant
692 l_measure_rec.measure_id := p_Application_Measure_Rec.Measure_id;
693 l_measure_rec.measure_short_name
694 := p_Application_Measure_Rec.Measure_Short_name;
695 l_measure_rec.measure_name := p_Application_Measure_Rec.Measure_name;
696
697 if (BIS_UTILITIES_PUB.Value_Missing
698 (l_Measure_Rec.Measure_id) = FND_API.G_TRUE
699 OR BIS_UTILITIES_PUB.Value_NULL(l_Measure_Rec.Measure_id)
700 = FND_API.G_TRUE)
701 then
702 BEGIN
703 l_measure_rec_p := l_measure_rec;
704 BIS_MEASURE_PVT.Value_Id_conversion
705 ( p_api_version => 1.0
706 , p_Measure_Rec => l_measure_rec_p
707 , x_Measure_Rec => l_measure_rec
708 , x_return_status => x_return_status
709 , x_error_Tbl => l_error_tbl
710 );
711 EXCEPTION
712 WHEN FND_API.G_EXC_ERROR then
713 NULL;
714 END;
715 end if;
716
717 l_app_rec.application_id := p_Application_Measure_Rec.Application_id;
718 l_app_rec.Application_short_name :=
719 p_Application_Measure_Rec.Application_Short_name;
720 l_app_rec.Application_name := p_Application_Measure_Rec.Application_name;
721
722 if ( l_app_rec.application_id is NULL
723 OR BIS_UTILITIES_PUB.Value_Missing(l_app_rec.application_id) = FND_API.G_TRUE) then
724 l_app_rec_p := l_app_rec;
725 BIS_APPLICATION_PVT.Value_Id_conversion
726 ( p_api_version => 1.0
727 , p_application_Rec => l_app_rec_p
728 , x_application_Rec => l_app_rec
729 , x_return_status => x_return_status
730 , x_error_Tbl => l_error_tbl
731 );
732 end if;
733
734 x_application_measure_rec.Owning_Application
735 := p_Application_Measure_Rec.Owning_Application;
736
737 x_application_measure_rec.Measure_id := l_measure_rec.Measure_id;
738 x_application_measure_rec.measure_short_name
739 := l_measure_rec.measure_short_name;
740 x_application_measure_rec.measure_name := l_measure_rec.measure_name;
741
742 x_application_measure_rec.Application_id := l_app_rec.Application_id;
743 x_application_measure_rec.Application_Short_name
744 := l_app_rec.Application_Short_name;
745 x_application_measure_rec.Application_name := l_app_rec.Application_name;
746
747 --commented RAISE
748 EXCEPTION
749 WHEN NO_DATA_FOUND THEN
750 x_return_status := FND_API.G_RET_STS_ERROR ;
754 -- RAISE FND_API.G_EXC_ERROR;
751 --RAISE FND_API.G_EXC_ERROR;
752 when FND_API.G_EXC_ERROR then
753 x_return_status := FND_API.G_RET_STS_ERROR ;
755 when FND_API.G_EXC_UNEXPECTED_ERROR then
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
757 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
758 when others then
759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
760 l_error_tbl := x_error_Tbl;
761 BIS_UTILITIES_PVT.Add_Error_Message
762 ( p_error_msg_id => SQLCODE
763 , p_error_description => SQLERRM
764 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
765 , p_error_table => l_error_tbl
766 , x_error_table => x_error_tbl
767 );
768 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
769
770 end Value_ID_Conversion;
771 --
772 PROCEDURE Retrieve_Last_Update_Date
773 ( p_api_version IN NUMBER
774 , p_Application_Measure_Rec IN
775 BIS_Application_Measure_PVT.Application_Measure_Rec_Type
776 , x_last_update_date OUT NOCOPY DATE
777 , x_return_status OUT NOCOPY VARCHAR2
778 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
779 )
780 IS
781 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
782 BEGIN
783 x_return_status := FND_API.G_RET_STS_SUCCESS;
784 IF( BIS_UTILITIES_PUB.Value_Missing(p_Application_Measure_Rec.Measure_id)
785 = FND_API.G_FALSE
786 AND BIS_UTILITIES_PUB.Value_Missing(p_Application_Measure_Rec.Application_id)
787 = FND_API.G_FALSE
788 ) THEN
789 SELECT NVL(LAST_UPDATE_DATE, CREATION_DATE)
790 INTO x_last_update_date
791 FROM bis_application_measures
792 WHERE INDICATOR_ID = p_Application_Measure_Rec.Measure_id
793 AND Application_ID = p_Application_Measure_Rec.Application_id;
794
795 END IF;
796 --
797 --commented RAISE
798 EXCEPTION
799 WHEN NO_DATA_FOUND THEN
800 x_return_status := FND_API.G_RET_STS_ERROR;
801 --added this message
802 l_error_tbl := x_error_Tbl;
803 BIS_UTILITIES_PVT.Add_Error_Message
804 ( p_error_msg_name => 'BIS_INVALID_APPORMEASURE_ID'
805 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
806 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Last_Update_Date'
807 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
808 , p_error_table => l_error_tbl
809 , x_error_table => x_error_tbl
810 );
811 --RAISE FND_API.G_EXC_ERROR;
812 WHEN FND_API.G_EXC_ERROR THEN
813 x_return_status:= FND_API.G_RET_STS_ERROR;
814 -- RAISE;
815 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
816 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
817 -- RAISE;
818 WHEN OTHERS THEN
819 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
820 l_error_tbl := x_error_Tbl;
821 BIS_UTILITIES_PVT.Add_Error_Message
822 ( p_error_table => l_error_Tbl
823 , p_error_msg_id => SQLCODE
824 , p_error_description => SQLERRM
825 , x_error_table => x_error_Tbl
826 );
827 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
828 --
829 END Retrieve_Last_Update_Date;
830 --
831 PROCEDURE Lock_Record
832 ( p_api_version IN NUMBER
833 , p_Application_Measure_Rec IN
834 BIS_Application_Measure_PVT.Application_Measure_Rec_Type
835 , p_timestamp IN VARCHAR := NULL
836 , x_return_status OUT NOCOPY VARCHAR2
837 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
838 )
839 IS
840 --
841 l_form_date DATE;
842 l_last_update_date DATE;
843 l_Rec BIS_Application_Measure_PVT.Application_Measure_Rec_Type;
844 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
845 --
846 BEGIN
847 x_return_status := FND_API.G_RET_STS_SUCCESS;
848 --
849 l_rec := p_Application_Measure_Rec;
850 Retrieve_Last_Update_Date
851 ( p_api_version => 1.0
852 , p_Application_Measure_Rec => p_Application_Measure_Rec
853 , x_last_update_date => l_last_update_date
854 , x_return_status => x_return_status
855 , x_error_Tbl => x_error_Tbl
856 );
857
858 IF(p_timestamp IS NOT NULL) THEN
859 l_form_date := TO_DATE(p_timestamp, BIS_UTILITIES_PVT.G_DATE_FORMAT);
860 IF(l_form_date = l_last_update_date) THEN
861 x_return_status := FND_API.G_TRUE;
862 ELSE
863 x_return_status := FND_API.G_FALSE;
864 END IF;
865 ELSE
866 x_return_status := FND_API.G_FALSE;
867 END IF;
868 --
869
870 --commented RAISE
871 EXCEPTION
872 WHEN FND_API.G_EXC_ERROR THEN
873 x_return_status:= FND_API.G_RET_STS_ERROR;
874 --RAISE;
875 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
876 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
877 --RAISE;
878 WHEN OTHERS THEN
879 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
880 l_error_tbl := x_error_Tbl;
881 BIS_UTILITIES_PVT.Add_Error_Message
882 ( p_error_table => l_error_Tbl
883 , p_error_msg_id => SQLCODE
884 , p_error_description => SQLERRM
888
885 , x_error_table => x_error_Tbl
886 );
887 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
889 END Lock_Record;
890
891 PROCEDURE Update_Application_Measure
892 ( p_api_version IN NUMBER
893 , p_commit IN VARCHAR2 := FND_API.G_FALSE
894 , p_Measure_Short_Name IN BIS_INDICATORS.SHORT_NAME%TYPE
895 , p_Application_Id IN BIS_APPLICATION_MEASURES.APPLICATION_ID%TYPE
896 , x_return_status OUT NOCOPY VARCHAR2
897 , x_msg_count OUT NOCOPY NUMBER
898 , x_msg_data OUT NOCOPY VARCHAR2
899 )
900 IS
901 l_rec BIS_APPLICATION_MEASURE_PVT.Application_Measure_Rec_type;
902 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
906 SELECT indicator_id
903 l_measure_id BIS_INDICATORS.INDICATOR_ID%TYPE;
904 BEGIN
905 x_return_status := FND_API.G_RET_STS_SUCCESS;
907 INTO l_measure_id
908 FROM BIS_INDICATORS
909 WHERE SHORT_NAME = p_Measure_Short_Name;
910
911 l_rec.Measure_id := l_measure_id;
912 l_rec.Application_id := p_Application_Id;
913 l_rec.owning_application := FND_API.G_FALSE;
914
915 BIS_APPLICATION_MEASURE_PVT.Update_Application_Measure( p_api_version
916 , p_commit
917 , l_rec
918 , x_return_status
919 , l_error_tbl);
920 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
921 IF (l_error_tbl.COUNT > 0) THEN
922 x_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
923 IF(INSTR(x_msg_data, ' ') = 0 ) THEN
924 FND_MESSAGE.SET_NAME('BIS',x_msg_data);
925 FND_MSG_PUB.ADD;
926 x_msg_data := NULL;
927 END IF;
928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
929 END IF;
930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
931 END IF;
932
933 IF(p_Commit = FND_API.G_TRUE) THEN
934 COMMIT;
935 END IF;
936
937 EXCEPTION
938 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
939 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
940 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE
941 ,p_count => x_msg_count
942 ,p_data => x_msg_data);
943 WHEN OTHERS THEN
944 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
945 IF (x_msg_data IS NOT NULL) THEN
946 x_msg_data := x_msg_data||' -> BIS_APPLICATION_MEASURE_PVT.Update_Application_Measure ';
947 ELSE
948 x_msg_data := SQLERRM||' at BIS_APPLICATION_MEASURE_PVT.Update_Application_Measure ';
949 END IF;
950 END Update_Application_Measure;
951 --
952 END BIS_Application_Measure_PVT;