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