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