[Home] [Help]
PACKAGE BODY: APPS.AMS_DMPERFORMANCE_PVT
Source
1 PACKAGE BODY AMS_DMPerformance_PVT as
2 /* $Header: amsvdpfb.pls 115.8 2002/12/09 11:20:59 choang ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_DMPerformance_PVT
7 -- Purpose
8 --
9 -- History
10 -- 26-Jan-2001 choang Added increment of object ver num in update api.
11 -- 07-Jan-2002 choang Removed security group id
12 -- 17-May-2002 choang bug 2380113: removed g_user_id and g_login_id
13 -- 11-Jun-2002 choang Fixed gscc error for bug 2380113.
14 --
15 -- NOTE
16 --
17 -- End of Comments
18 -- ===============================================================
19
20
21 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_DMPerformance_PVT';
22 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvdpfb.pls';
23
24 /***
25 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
26 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
27 ***/
28
29 -- Hint: Primary key needs to be returned.
30 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
31 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
32 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
33
34 PROCEDURE Create_Performance(
35 p_api_version_number IN NUMBER,
36 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
37 p_commit IN VARCHAR2 := FND_API.G_FALSE,
38 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
39
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_msg_data OUT NOCOPY VARCHAR2,
43
44 p_performance_rec IN performance_rec_type := g_miss_performance_rec,
45 x_performance_id OUT NOCOPY NUMBER
46 )
47 IS
48 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Performance';
49 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
50 l_return_status_full VARCHAR2(1);
51 l_object_version_number NUMBER := 1;
52 l_org_id NUMBER := FND_API.G_MISS_NUM;
53 l_PERFORMANCE_ID NUMBER;
54 l_dummy NUMBER;
55
56 CURSOR c_id IS
57 SELECT AMS_DM_PERFORMANCE_s.NEXTVAL
58 FROM dual;
59
60 CURSOR c_id_exists (l_id IN NUMBER) IS
61 SELECT 1 FROM dual
62 WHERE EXISTS (SELECT 1 FROM AMS_DM_PERFORMANCE
63 WHERE PERFORMANCE_ID = l_id);
64
65 BEGIN
66 -- Standard Start of API savepoint
67 SAVEPOINT Create_Performance_PVT;
68
69 -- Standard call to check for call compatibility.
70 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
71 p_api_version_number,
72 l_api_name,
73 G_PKG_NAME)
74 THEN
75 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
76 END IF;
77
78 -- Initialize message list if p_init_msg_list is set to TRUE.
79 IF FND_API.to_Boolean( p_init_msg_list )
80 THEN
81 FND_MSG_PUB.initialize;
82 END IF;
83
84 -- Debug Message
85 IF (AMS_DEBUG_HIGH_ON) THEN
86
87 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
88 END IF;
89
90
91 -- Initialize API return status to SUCCESS
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93
94 -- Local variable initialization
95
96 IF p_performance_rec.PERFORMANCE_ID IS NULL OR p_performance_rec.PERFORMANCE_ID = FND_API.g_miss_num THEN
97 LOOP
98 l_dummy := NULL;
99 OPEN c_id;
100 FETCH c_id INTO l_PERFORMANCE_ID;
101 CLOSE c_id;
102
103 OPEN c_id_exists(l_PERFORMANCE_ID);
104 FETCH c_id_exists INTO l_dummy;
105 CLOSE c_id_exists;
106 EXIT WHEN l_dummy IS NULL;
107 END LOOP;
108 END IF;
109
110 -- =========================================================================
111 -- Validate
112 -- =========================================================================
113
114 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
115 THEN
116 -- Debug message
117 IF (AMS_DEBUG_HIGH_ON) THEN
118
119 AMS_UTILITY_PVT.debug_message('Private API: Validate_Performance');
120 END IF;
121
122 -- Invoke validation procedures
123 Validate_Performance(
124 p_api_version_number => 1.0,
125 p_init_msg_list => FND_API.G_FALSE,
126 p_validation_level => p_validation_level,
127 p_validation_mode => JTF_PLSQL_API.g_create,
128 p_performance_rec => p_performance_rec,
129 x_return_status => x_return_status,
130 x_msg_count => x_msg_count,
131 x_msg_data => x_msg_data);
132 END IF;
133
134 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
135 RAISE FND_API.G_EXC_ERROR;
136 END IF;
137
138
139 -- Debug Message
140 IF (AMS_DEBUG_HIGH_ON) THEN
141
142 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
143 END IF;
144
145 -- Invoke table handler(AMS_DM_PERFORMANCE_PKG.Insert_Row)
146 AMS_DM_PERFORMANCE_PKG.Insert_Row(
147 px_performance_id => l_performance_id,
148 p_last_update_date => SYSDATE,
149 p_last_updated_by => FND_GLOBAL.USER_ID,
150 p_creation_date => SYSDATE,
151 p_created_by => FND_GLOBAL.USER_ID,
152 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
153 px_object_version_number => l_object_version_number,
154 p_predicted_value => p_performance_rec.predicted_value,
155 p_actual_value => p_performance_rec.actual_value,
156 p_evaluated_records => p_performance_rec.evaluated_records,
157 p_total_records_predicted => p_performance_rec.total_records_predicted,
158 p_model_id => p_performance_rec.model_id);
159 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
160 RAISE FND_API.G_EXC_ERROR;
161 END IF;
162
163 x_performance_id := l_performance_id;
164 --
165 -- End of API body
166 --
167
168 -- Standard check for p_commit
169 IF FND_API.to_Boolean( p_commit )
170 THEN
171 COMMIT WORK;
172 END IF;
173
174
175 -- Debug Message
176 IF (AMS_DEBUG_HIGH_ON) THEN
177
178 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
179 END IF;
180
181 -- Standard call to get message count and if count is 1, get message info.
182 FND_MSG_PUB.Count_And_Get
183 (p_count => x_msg_count,
184 p_data => x_msg_data
185 );
186 EXCEPTION
187
188 WHEN AMS_Utility_PVT.resource_locked THEN
189 x_return_status := FND_API.g_ret_sts_error;
190 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
191 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
192 FND_MSG_PUB.add;
193 END IF;
194
195 WHEN FND_API.G_EXC_ERROR THEN
196 ROLLBACK TO Create_Performance_PVT;
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 -- Standard call to get message count and if count=1, get the message
199 FND_MSG_PUB.Count_And_Get (
200 p_encoded => FND_API.G_FALSE,
201 p_count => x_msg_count,
202 p_data => x_msg_data
203 );
204
205 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
206 ROLLBACK TO Create_Performance_PVT;
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
208 -- Standard call to get message count and if count=1, get the message
209 FND_MSG_PUB.Count_And_Get (
210 p_encoded => FND_API.G_FALSE,
211 p_count => x_msg_count,
212 p_data => x_msg_data
213 );
214
215 WHEN OTHERS THEN
216 ROLLBACK TO Create_Performance_PVT;
217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
218 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
219 THEN
220 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
221 END IF;
222 -- Standard call to get message count and if count=1, get the message
223 FND_MSG_PUB.Count_And_Get (
224 p_encoded => FND_API.G_FALSE,
225 p_count => x_msg_count,
226 p_data => x_msg_data
227 );
228 End Create_Performance;
229
230
231 PROCEDURE Update_Performance(
232 p_api_version_number IN NUMBER,
233 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
234 p_commit IN VARCHAR2 := FND_API.G_FALSE,
235 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
236
237 x_return_status OUT NOCOPY VARCHAR2,
238 x_msg_count OUT NOCOPY NUMBER,
239 x_msg_data OUT NOCOPY VARCHAR2,
240
241 p_performance_rec IN performance_rec_type,
242 x_object_version_number OUT NOCOPY NUMBER
243 )
244 IS
245 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Performance';
246 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
247
248 -- Local Variables
249 l_object_version_number NUMBER;
250 l_PERFORMANCE_ID NUMBER;
251 l_tar_performance_rec AMS_DMPerformance_PVT.performance_rec_type := P_performance_rec;
252
253 CURSOR c_get_dmperformance(p_performance_id NUMBER) IS
254 SELECT *
255 FROM AMS_DM_PERFORMANCE
256 WHERE performance_id = p_performance_id;
257 l_ref_performance_rec c_get_Dmperformance%ROWTYPE ;
258 BEGIN
259 -- Standard Start of API savepoint
260 SAVEPOINT Update_Performance_PVT;
261
262 -- Standard call to check for call compatibility.
263 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
264 p_api_version_number,
265 l_api_name,
266 G_PKG_NAME)
267 THEN
268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
269 END IF;
270
271 -- Initialize message list if p_init_msg_list is set to TRUE.
272 IF FND_API.to_Boolean( p_init_msg_list )
273 THEN
274 FND_MSG_PUB.initialize;
275 END IF;
276
277 -- Debug Message
278 IF (AMS_DEBUG_HIGH_ON) THEN
279
280 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
281 END IF;
282
283
284 -- Initialize API return status to SUCCESS
285 x_return_status := FND_API.G_RET_STS_SUCCESS;
286
287 -- Debug Message
288 IF (AMS_DEBUG_HIGH_ON) THEN
289
290 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
291 END IF;
292
293 OPEN c_get_Dmperformance( l_tar_performance_rec.performance_id);
294
295 FETCH c_get_Dmperformance INTO l_ref_performance_rec ;
296
297 If ( c_get_Dmperformance%NOTFOUND) THEN
298 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
299 THEN
300 FND_MESSAGE.Set_Name('AMS', 'API_MISSING_UPDATE_TARGET');
301 FND_MESSAGE.Set_Token ('INFO', 'Dmperformance', FALSE);
302 FND_MSG_PUB.Add;
303 END IF;
304 RAISE FND_API.G_EXC_ERROR;
305 END IF;
306 -- Debug Message
307 IF (AMS_DEBUG_HIGH_ON) THEN
308
309 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
310 END IF;
311 CLOSE c_get_Dmperformance;
312
313 -- Check Whether record has been changed by someone else
314 If (l_tar_performance_rec.object_version_number <> l_ref_performance_rec.object_version_number) Then
315 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
316 THEN
317 FND_MESSAGE.Set_Name('AMS', 'API_RECORD_CHANGED');
318 FND_MESSAGE.Set_Token('INFO', 'Dmperformance', FALSE);
319 FND_MSG_PUB.ADD;
320 END IF;
321 raise FND_API.G_EXC_ERROR;
322 End if;
323 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
324 THEN
325 -- Debug message
326 IF (AMS_DEBUG_HIGH_ON) THEN
327
328 AMS_UTILITY_PVT.debug_message('Private API: Validate_Performance');
329 END IF;
330
331 -- Invoke validation procedures
332 Validate_Performance(
333 p_api_version_number => 1.0,
334 p_init_msg_list => FND_API.G_FALSE,
335 p_validation_level => p_validation_level,
336 p_validation_mode => JTF_PLSQL_API.g_update,
337 p_performance_rec => p_performance_rec,
338 x_return_status => x_return_status,
339 x_msg_count => x_msg_count,
340 x_msg_data => x_msg_data);
341 END IF;
342
343 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
344 RAISE FND_API.G_EXC_ERROR;
345 END IF;
346
347
348 -- Debug Message
349 IF (AMS_DEBUG_HIGH_ON) THEN
350
351 AMS_UTILITY_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
352 END IF;
353
354 -- Invoke table handler(AMS_DM_PERFORMANCE_PKG.Update_Row)
355 AMS_DM_PERFORMANCE_PKG.Update_Row(
359 p_creation_date => SYSDATE,
356 p_performance_id => p_performance_rec.performance_id,
357 p_last_update_date => SYSDATE,
358 p_last_updated_by => FND_GLOBAL.USER_ID,
360 p_created_by => l_ref_performance_rec.created_by,
361 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
362 p_object_version_number => p_performance_rec.object_version_number + 1,
363 p_predicted_value => p_performance_rec.predicted_value,
364 p_actual_value => p_performance_rec.actual_value,
365 p_evaluated_records => p_performance_rec.evaluated_records,
366 p_total_records_predicted => p_performance_rec.total_records_predicted,
367 p_model_id => p_performance_rec.model_id
368 );
369
370 --
371 -- End of API body.
372 --
373
374 -- Standard check for p_commit
375 IF FND_API.to_Boolean( p_commit )
376 THEN
377 COMMIT WORK;
378 END IF;
379
380
381 -- Debug Message
382 IF (AMS_DEBUG_HIGH_ON) THEN
383
384 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
385 END IF;
386
387 -- Standard call to get message count and if count is 1, get message info.
388 FND_MSG_PUB.Count_And_Get
389 (p_count => x_msg_count,
390 p_data => x_msg_data
391 );
392 EXCEPTION
393
394 WHEN AMS_Utility_PVT.resource_locked THEN
395 x_return_status := FND_API.g_ret_sts_error;
396 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
397 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
398 FND_MSG_PUB.add;
399 END IF;
400
401 WHEN FND_API.G_EXC_ERROR THEN
402 ROLLBACK TO Update_Performance_PVT;
403 x_return_status := FND_API.G_RET_STS_ERROR;
404 -- Standard call to get message count and if count=1, get the message
405 FND_MSG_PUB.Count_And_Get (
406 p_encoded => FND_API.G_FALSE,
407 p_count => x_msg_count,
408 p_data => x_msg_data
409 );
410
411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
412 ROLLBACK TO Update_Performance_PVT;
413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
414 -- Standard call to get message count and if count=1, get the message
415 FND_MSG_PUB.Count_And_Get (
416 p_encoded => FND_API.G_FALSE,
417 p_count => x_msg_count,
418 p_data => x_msg_data
419 );
420
421 WHEN OTHERS THEN
422 ROLLBACK TO Update_Performance_PVT;
423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
424 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
425 THEN
426 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
427 END IF;
428 -- Standard call to get message count and if count=1, get the message
429 FND_MSG_PUB.Count_And_Get (
430 p_encoded => FND_API.G_FALSE,
431 p_count => x_msg_count,
432 p_data => x_msg_data
433 );
434 End Update_Performance;
435
436
437 PROCEDURE Delete_Performance(
438 p_api_version_number IN NUMBER,
439 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
440 p_commit IN VARCHAR2 := FND_API.G_FALSE,
441 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
442 x_return_status OUT NOCOPY VARCHAR2,
443 x_msg_count OUT NOCOPY NUMBER,
444 x_msg_data OUT NOCOPY VARCHAR2,
445 p_performance_id IN NUMBER,
446 p_object_version_number IN NUMBER
447 )
448
449 IS
450 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Performance';
451 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
452 l_object_version_number NUMBER;
453
454 BEGIN
455 -- Standard Start of API savepoint
456 SAVEPOINT Delete_Performance_PVT;
457
458 -- Standard call to check for call compatibility.
459 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
460 p_api_version_number,
461 l_api_name,
462 G_PKG_NAME)
463 THEN
464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
465 END IF;
466
467 -- Initialize message list if p_init_msg_list is set to TRUE.
468 IF FND_API.to_Boolean( p_init_msg_list )
469 THEN
470 FND_MSG_PUB.initialize;
471 END IF;
472
473 -- Debug Message
474 IF (AMS_DEBUG_HIGH_ON) THEN
475
476 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
477 END IF;
478
479
480 -- Initialize API return status to SUCCESS
481 x_return_status := FND_API.G_RET_STS_SUCCESS;
482
483 --
484 -- Api body
485 --
486 -- Debug Message
487 IF (AMS_DEBUG_HIGH_ON) THEN
488
489 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
490 END IF;
491
495 --
492 -- Invoke table handler(AMS_DM_PERFORMANCE_PKG.Delete_Row)
493 AMS_DM_PERFORMANCE_PKG.Delete_Row(
494 p_PERFORMANCE_ID => p_PERFORMANCE_ID);
496 -- End of API body
497 --
498
499 -- Standard check for p_commit
500 IF FND_API.to_Boolean( p_commit )
501 THEN
502 COMMIT WORK;
503 END IF;
504
505
506 -- Debug Message
507 IF (AMS_DEBUG_HIGH_ON) THEN
508
509 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
510 END IF;
511
512 -- Standard call to get message count and if count is 1, get message info.
513 FND_MSG_PUB.Count_And_Get
514 (p_count => x_msg_count,
515 p_data => x_msg_data
516 );
517 EXCEPTION
518
519 WHEN AMS_Utility_PVT.resource_locked THEN
520 x_return_status := FND_API.g_ret_sts_error;
521 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
522 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
523 FND_MSG_PUB.add;
524 END IF;
525
526 WHEN FND_API.G_EXC_ERROR THEN
527 ROLLBACK TO Delete_Performance_PVT;
528 x_return_status := FND_API.G_RET_STS_ERROR;
529 -- Standard call to get message count and if count=1, get the message
530 FND_MSG_PUB.Count_And_Get (
531 p_encoded => FND_API.G_FALSE,
532 p_count => x_msg_count,
533 p_data => x_msg_data
534 );
535
536 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
537 ROLLBACK TO Delete_Performance_PVT;
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539 -- Standard call to get message count and if count=1, get the message
540 FND_MSG_PUB.Count_And_Get (
541 p_encoded => FND_API.G_FALSE,
542 p_count => x_msg_count,
543 p_data => x_msg_data
544 );
545
546 WHEN OTHERS THEN
547 ROLLBACK TO Delete_Performance_PVT;
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
550 THEN
551 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
552 END IF;
553 -- Standard call to get message count and if count=1, get the message
554 FND_MSG_PUB.Count_And_Get (
555 p_encoded => FND_API.G_FALSE,
556 p_count => x_msg_count,
557 p_data => x_msg_data
558 );
559 End Delete_Performance;
560
561
562
563 -- Hint: Primary key needs to be returned.
564 PROCEDURE Lock_Performance(
565 p_api_version_number IN NUMBER,
566 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
567
568 x_return_status OUT NOCOPY VARCHAR2,
569 x_msg_count OUT NOCOPY NUMBER,
570 x_msg_data OUT NOCOPY VARCHAR2,
571
572 p_performance_id IN NUMBER,
573 p_object_version IN NUMBER
574 )
575
576 IS
577 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Performance';
578 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
579 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
580 l_PERFORMANCE_ID NUMBER;
581
582 CURSOR c_Dmperformance IS
583 SELECT PERFORMANCE_ID
584 FROM AMS_DM_PERFORMANCE
585 WHERE PERFORMANCE_ID = p_PERFORMANCE_ID
586 AND object_version_number = p_object_version
587 FOR UPDATE NOWAIT;
588
589 BEGIN
590
591 -- Debug Message
592 IF (AMS_DEBUG_HIGH_ON) THEN
593
594 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
595 END IF;
596
597 -- Initialize message list if p_init_msg_list is set to TRUE.
598 IF FND_API.to_Boolean( p_init_msg_list )
599 THEN
600 FND_MSG_PUB.initialize;
601 END IF;
602
603 -- Standard call to check for call compatibility.
604 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
605 p_api_version_number,
606 l_api_name,
607 G_PKG_NAME)
608 THEN
609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
610 END IF;
611
612
613 -- Initialize API return status to SUCCESS
614 x_return_status := FND_API.G_RET_STS_SUCCESS;
615
616
617 ------------------------ lock -------------------------
618
619 IF (AMS_DEBUG_HIGH_ON) THEN
620
621
622
623 AMS_Utility_PVT.debug_message(l_full_name||': start');
624
625 END IF;
626 OPEN c_Dmperformance;
627
628 FETCH c_Dmperformance INTO l_PERFORMANCE_ID;
629
630 IF (c_Dmperformance%NOTFOUND) THEN
631 CLOSE c_Dmperformance;
632 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
633 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
634 FND_MSG_PUB.add;
635 END IF;
636 RAISE FND_API.g_exc_error;
637 END IF;
638
639 CLOSE c_Dmperformance;
640
644 p_count => x_msg_count,
641 -------------------- finish --------------------------
642 FND_MSG_PUB.count_and_get(
643 p_encoded => FND_API.g_false,
645 p_data => x_msg_data);
646 IF (AMS_DEBUG_HIGH_ON) THEN
647
648 AMS_Utility_PVT.debug_message(l_full_name ||': end');
649 END IF;
650 EXCEPTION
651
652 WHEN AMS_Utility_PVT.resource_locked THEN
653 x_return_status := FND_API.g_ret_sts_error;
654 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
655 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
656 FND_MSG_PUB.add;
657 END IF;
658
659 WHEN FND_API.G_EXC_ERROR THEN
660 ROLLBACK TO Lock_Performance_PVT;
661 x_return_status := FND_API.G_RET_STS_ERROR;
662 -- Standard call to get message count and if count=1, get the message
663 FND_MSG_PUB.Count_And_Get (
664 p_encoded => FND_API.G_FALSE,
665 p_count => x_msg_count,
666 p_data => x_msg_data
667 );
668
669 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
670 ROLLBACK TO Lock_Performance_PVT;
671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
672 -- Standard call to get message count and if count=1, get the message
673 FND_MSG_PUB.Count_And_Get (
674 p_encoded => FND_API.G_FALSE,
675 p_count => x_msg_count,
676 p_data => x_msg_data
677 );
678
679 WHEN OTHERS THEN
680 ROLLBACK TO Lock_Performance_PVT;
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
683 THEN
684 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
685 END IF;
686 -- Standard call to get message count and if count=1, get the message
687 FND_MSG_PUB.Count_And_Get (
688 p_encoded => FND_API.G_FALSE,
689 p_count => x_msg_count,
690 p_data => x_msg_data
691 );
692 End Lock_Performance;
693
694
695 PROCEDURE check_performance_uk_items(
696 p_performance_rec IN performance_rec_type,
697 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
698 x_return_status OUT NOCOPY VARCHAR2)
699 IS
700 l_valid_flag VARCHAR2(1);
701
702 BEGIN
703 x_return_status := FND_API.g_ret_sts_success;
704 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
705 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
706 'AMS_DM_PERFORMANCE',
707 'PERFORMANCE_ID = ''' || p_performance_rec.PERFORMANCE_ID ||''''
708 );
709 ELSE
710 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
711 'AMS_DM_PERFORMANCE',
712 'PERFORMANCE_ID = ''' || p_performance_rec.PERFORMANCE_ID ||
713 ''' AND PERFORMANCE_ID <> ' || p_performance_rec.PERFORMANCE_ID
714 );
715 END IF;
716
717 IF l_valid_flag = FND_API.g_false THEN
718 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
719 THEN
720 FND_MESSAGE.set_name('AMS', 'AMS_PERFORMANCE_ID_DUPLICATE');
721 FND_MSG_PUB.add;
722 END IF;
723 x_return_status := FND_API.g_ret_sts_error;
724 RETURN;
725 END IF;
726
727 END check_performance_uk_items;
728
729 PROCEDURE check_performance_req_items(
730 p_performance_rec IN performance_rec_type,
731 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
732 x_return_status OUT NOCOPY VARCHAR2
733 )
734 IS
735 BEGIN
736 x_return_status := FND_API.g_ret_sts_success;
737
738 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
739 IF p_performance_rec.MODEL_ID = FND_API.g_miss_num OR p_performance_rec.MODEL_ID IS NULL THEN
740 AMS_Utility_PVT.error_message ('AMS_PERF_NO_MODEL_ID');
741 x_return_status := FND_API.g_ret_sts_error;
742 END IF;
743
744 -- predicated value
745 IF p_performance_rec.predicted_value = FND_API.g_miss_num OR p_performance_rec.predicted_value IS NULL THEN
746 AMS_Utility_PVT.error_message ('AMS_PERF_NO_PREDICTED_VALUE');
747 x_return_status := FND_API.g_ret_sts_error;
748 END IF;
749
750 -- actual value
751 IF p_performance_rec.actual_value = FND_API.g_miss_num OR p_performance_rec.actual_value IS NULL THEN
752 AMS_Utility_PVT.error_message ('AMS_PERF_NO_ACTUAL_VALUE');
753 x_return_status := FND_API.g_ret_sts_error;
754 END IF;
755
756 -- evaluated records
757 IF p_performance_rec.evaluated_records = FND_API.g_miss_num OR p_performance_rec.evaluated_records IS NULL THEN
758 AMS_Utility_PVT.error_message ('AMS_PERF_NO_EVAL_RECORDS');
759 x_return_status := FND_API.g_ret_sts_error;
760 END IF;
761
762 -- total records
763 IF p_performance_rec.total_records_predicted = FND_API.g_miss_num OR p_performance_rec.total_records_predicted IS NULL THEN
764 AMS_Utility_PVT.error_message ('AMS_PERF_NO_TOTAL_RECORDS');
765 x_return_status := FND_API.g_ret_sts_error;
766 END IF;
767 ELSE -- need ID when updating
768 IF p_performance_rec.performance_id = FND_API.g_miss_num OR p_performance_rec.performance_id IS NULL THEN
772
769 AMS_Utility_PVT.error_message ('AMS_PERF_NO_PERF_ID');
770 x_return_status := FND_API.g_ret_sts_error;
771 END IF;
773 IF p_performance_rec.MODEL_ID IS NULL THEN
774 AMS_Utility_PVT.error_message ('AMS_PERF_NO_MODEL_ID');
775 x_return_status := FND_API.g_ret_sts_error;
776 END IF;
777
778 -- predicated value
779 IF p_performance_rec.predicted_value IS NULL THEN
780 AMS_Utility_PVT.error_message ('AMS_PERF_NO_PREDICTED_VALUE');
781 x_return_status := FND_API.g_ret_sts_error;
782 END IF;
783
784 -- actual value
785 IF p_performance_rec.actual_value IS NULL THEN
786 AMS_Utility_PVT.error_message ('AMS_PERF_NO_ACTUAL_VALUE');
787 x_return_status := FND_API.g_ret_sts_error;
788 END IF;
789
790 -- evaluated records
791 IF p_performance_rec.evaluated_records IS NULL THEN
792 AMS_Utility_PVT.error_message ('AMS_PERF_NO_EVAL_RECORDS');
793 x_return_status := FND_API.g_ret_sts_error;
794 END IF;
795
796 -- total records
797 IF p_performance_rec.total_records_predicted IS NULL THEN
798 AMS_Utility_PVT.error_message ('AMS_PERF_NO_TOTAL_RECORDS');
799 x_return_status := FND_API.g_ret_sts_error;
800 END IF;
801 END IF;
802
803 END check_performance_req_items;
804
805 PROCEDURE check_performance_FK_items(
806 p_performance_rec IN performance_rec_type,
807 x_return_status OUT NOCOPY VARCHAR2
808 )
809 IS
810 BEGIN
811 x_return_status := FND_API.g_ret_sts_success;
812
813 IF p_performance_rec.model_id <> FND_API.g_miss_num THEN
814 IF AMS_Utility_PVT.check_fk_exists (
815 'AMS_DM_MODELS_ALL_B',
816 'MODEL_ID',
817 p_performance_rec.model_id
818 ) = FND_API.g_false THEN
819 AMS_Utility_PVT.error_message ('AMS_DM_PERF_BAD_MODEL_ID');
820 x_return_status := FND_API.g_ret_sts_error;
821 END IF;
822 END IF;
823 END check_performance_FK_items;
824
825 PROCEDURE check_performance_Lookup_items(
826 p_performance_rec IN performance_rec_type,
827 x_return_status OUT NOCOPY VARCHAR2
828 )
829 IS
830 BEGIN
831 x_return_status := FND_API.g_ret_sts_success;
832
833 -- Enter custom code here
834
835 END check_performance_Lookup_items;
836
837 PROCEDURE Check_performance_Items (
838 P_performance_rec IN performance_rec_type,
839 p_validation_mode IN VARCHAR2,
840 x_return_status OUT NOCOPY VARCHAR2
841 )
842 IS
843 BEGIN
844
845 -- Check Items Uniqueness API calls
846
847 check_performance_uk_items(
848 p_performance_rec => p_performance_rec,
849 p_validation_mode => p_validation_mode,
850 x_return_status => x_return_status);
851 IF x_return_status <> FND_API.g_ret_sts_success THEN
852 RETURN;
853 END IF;
854
855 -- Check Items Required/NOT NULL API calls
856
857 check_performance_req_items(
858 p_performance_rec => p_performance_rec,
859 p_validation_mode => p_validation_mode,
860 x_return_status => x_return_status);
861 IF x_return_status <> FND_API.g_ret_sts_success THEN
862 RETURN;
863 END IF;
864 -- Check Items Foreign Keys API calls
865
866 check_performance_FK_items(
867 p_performance_rec => p_performance_rec,
868 x_return_status => x_return_status);
869 IF x_return_status <> FND_API.g_ret_sts_success THEN
870 RETURN;
871 END IF;
872 -- Check Items Lookups
873
874 check_performance_Lookup_items(
875 p_performance_rec => p_performance_rec,
876 x_return_status => x_return_status);
877 IF x_return_status <> FND_API.g_ret_sts_success THEN
878 RETURN;
879 END IF;
880
881 END Check_performance_Items;
882
883
884 PROCEDURE Complete_performance_Rec (
885 P_performance_rec IN performance_rec_type,
886 x_complete_rec OUT NOCOPY performance_rec_type
887 )
888 IS
889 BEGIN
890
891 --
892 -- Check Items API calls
893 NULL;
894 --
895
896 END Complete_performance_Rec;
897
898 PROCEDURE Validate_Performance(
899 p_api_version_number IN NUMBER,
900 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
901 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
902 p_validation_mode IN VARCHAR2,
903 p_performance_rec IN performance_rec_type,
904 x_return_status OUT NOCOPY VARCHAR2,
905 x_msg_count OUT NOCOPY NUMBER,
906 x_msg_data OUT NOCOPY VARCHAR2
907 )
908 IS
909 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Performance';
910 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
911 l_object_version_number NUMBER;
912 l_performance_rec AMS_DMPerformance_PVT.performance_rec_type;
913 BEGIN
914 -- Standard Start of API savepoint
915 SAVEPOINT Validate_Performance_;
916
917 -- Standard call to check for call compatibility.
918 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
922 THEN
919 p_api_version_number,
920 l_api_name,
921 G_PKG_NAME)
923 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
924 END IF;
925
926 -- Initialize message list if p_init_msg_list is set to TRUE.
927 IF FND_API.to_Boolean( p_init_msg_list )
928 THEN
929 FND_MSG_PUB.initialize;
930 END IF;
931 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
932 Check_performance_Items(
933 p_performance_rec => p_performance_rec,
934 p_validation_mode => p_validation_mode,
935 x_return_status => x_return_status
936 );
937
938 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
939 RAISE FND_API.G_EXC_ERROR;
940 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
941 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
942 END IF;
943 END IF;
944
945 Complete_performance_Rec(
946 p_performance_rec => p_performance_rec,
947 x_complete_rec => l_performance_rec
948 );
949
950 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
951 Validate_performance_Rec(
952 p_api_version_number => 1.0,
953 p_init_msg_list => FND_API.G_FALSE,
954 x_return_status => x_return_status,
955 x_msg_count => x_msg_count,
956 x_msg_data => x_msg_data,
957 p_performance_rec => l_performance_rec);
958
959 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
960 RAISE FND_API.G_EXC_ERROR;
961 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
963 END IF;
964 END IF;
965
966
967 -- Debug Message
968 IF (AMS_DEBUG_HIGH_ON) THEN
969
970 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
971 END IF;
972
973
974 -- Initialize API return status to SUCCESS
975 x_return_status := FND_API.G_RET_STS_SUCCESS;
976
977
978 -- Debug Message
979 IF (AMS_DEBUG_HIGH_ON) THEN
980
981 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
982 END IF;
983
984 -- Standard call to get message count and if count is 1, get message info.
985 FND_MSG_PUB.Count_And_Get
986 (p_count => x_msg_count,
987 p_data => x_msg_data
988 );
989 EXCEPTION
990
991 WHEN AMS_Utility_PVT.resource_locked THEN
992 x_return_status := FND_API.g_ret_sts_error;
993 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
994 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
995 FND_MSG_PUB.add;
996 END IF;
997
998 WHEN FND_API.G_EXC_ERROR THEN
999 ROLLBACK TO Validate_Performance_;
1000 x_return_status := FND_API.G_RET_STS_ERROR;
1001 -- Standard call to get message count and if count=1, get the message
1002 FND_MSG_PUB.Count_And_Get (
1003 p_encoded => FND_API.G_FALSE,
1004 p_count => x_msg_count,
1005 p_data => x_msg_data
1006 );
1007
1008 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1009 ROLLBACK TO Validate_Performance_;
1010 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1011 -- Standard call to get message count and if count=1, get the message
1012 FND_MSG_PUB.Count_And_Get (
1013 p_encoded => FND_API.G_FALSE,
1014 p_count => x_msg_count,
1015 p_data => x_msg_data
1016 );
1017
1018 WHEN OTHERS THEN
1019 ROLLBACK TO Validate_Performance_;
1020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1021 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1022 THEN
1023 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1024 END IF;
1025 -- Standard call to get message count and if count=1, get the message
1026 FND_MSG_PUB.Count_And_Get (
1027 p_encoded => FND_API.G_FALSE,
1028 p_count => x_msg_count,
1029 p_data => x_msg_data
1030 );
1031 End Validate_Performance;
1032
1033
1034 PROCEDURE Validate_performance_rec(
1035 p_api_version_number IN NUMBER,
1036 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1037 x_return_status OUT NOCOPY VARCHAR2,
1038 x_msg_count OUT NOCOPY NUMBER,
1039 x_msg_data OUT NOCOPY VARCHAR2,
1040 p_performance_rec IN performance_rec_type
1041 )
1042 IS
1043 BEGIN
1044 -- Initialize message list if p_init_msg_list is set to TRUE.
1045 IF FND_API.to_Boolean( p_init_msg_list )
1046 THEN
1047 FND_MSG_PUB.initialize;
1048 END IF;
1049
1050 -- Initialize API return status to SUCCESS
1051 x_return_status := FND_API.G_RET_STS_SUCCESS;
1052
1053 -- Hint: Validate data
1054 -- If data not valid
1055 -- THEN
1056 -- x_return_status := FND_API.G_RET_STS_ERROR;
1057
1058 -- Debug Message
1059 IF (AMS_DEBUG_HIGH_ON) THEN
1060
1061 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1062 END IF;
1063 -- Standard call to get message count and if count is 1, get message info.
1064 FND_MSG_PUB.Count_And_Get
1065 (p_count => x_msg_count,
1066 p_data => x_msg_data
1067 );
1068 END Validate_performance_Rec;
1069
1070 PROCEDURE get_performance_indices(
1071 p_model_id IN NUMBER,
1072 x_actlPos_PredPos_Count OUT NOCOPY NUMBER,
1073 x_actlPos_PredNeg_Count OUT NOCOPY NUMBER,
1074 x_actlNeg_PredPos_Count OUT NOCOPY NUMBER,
1075 x_actlNeg_PredNeg_Count OUT NOCOPY NUMBER
1076 ) IS
1077 CURSOR c_get_perf_rows(p_model_id NUMBER) IS
1078 SELECT perf.predicted_value
1079 , perf. actual_value
1080 , perf.evaluated_records
1081 , model.target_positive_value
1082 FROM ams_dm_performance perf
1083 , ams_dm_models_all_b model
1084 WHERE model.model_id = p_model_id
1085 AND perf.model_id = p_model_id;
1086 l_predicted_value NUMBER;
1087 l_actual_value NUMBER;
1088 l_evaluated_records NUMBER;
1089 l_target_positive_value NUMBER;
1090
1091 BEGIN
1092 x_actlPos_PredPos_Count := 0;
1093 x_actlPos_PredNeg_Count := 0;
1094 x_actlNeg_PredPos_Count := 0;
1095 x_actlNeg_PredNeg_Count := 0;
1096
1097 OPEN c_get_perf_rows(p_model_id);
1098 LOOP
1099 FETCH c_get_perf_rows INTO l_predicted_value
1100 , l_actual_value
1101 , l_evaluated_records
1102 , l_target_positive_value;
1103 EXIT WHEN c_get_perf_rows%NOTFOUND;
1104 IF NVL(l_predicted_value,-1) = NVL(l_target_positive_value,1)
1105 AND NVL(l_actual_value,-1) = NVL(l_target_positive_value,1) THEN
1106 x_actlPos_PredPos_Count := l_evaluated_records;
1107 ELSIF NVL(l_predicted_value,-1) <> NVL(l_target_positive_value,1)
1108 AND NVL(l_actual_value,-1) = NVL(l_target_positive_value,1) THEN
1109 x_actlPos_PredNeg_Count := l_evaluated_records;
1110 ELSIF NVL(l_predicted_value,-1) = NVL(l_target_positive_value,1)
1111 AND NVL(l_actual_value,-1) <> NVL(l_target_positive_value,1) THEN
1112 x_actlNeg_PredPos_Count := l_evaluated_records;
1113 ELSIF NVL(l_predicted_value,-1) <> NVL(l_target_positive_value,1)
1114 AND NVL(l_actual_value,-1) <> NVL(l_target_positive_value,1) THEN
1115 x_actlNeg_PredNeg_Count := l_evaluated_records;
1116 END IF;
1117 END LOOP;
1118 CLOSE c_get_perf_rows;
1119 END get_performance_indices;
1120
1121 END AMS_DMPerformance_PVT;