[Home] [Help]
PACKAGE BODY: APPS.AMS_ADHOC_PARAM_PVT
Source
1 PACKAGE BODY AMS_Adhoc_Param_PVT as
2 /* $Header: amsvapvb.pls 120.0 2005/07/01 03:54:13 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Adhoc_Param_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_Adhoc_Param_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvapvb.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_Adhoc_Param(
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_adhoc_param_rec IN adhoc_param_rec_type := g_miss_adhoc_param_rec,
35 x_adhoc_param_id OUT NOCOPY NUMBER
36 )
37
38 IS
39 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Adhoc_Param';
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_ADHOC_PARAM_ID NUMBER;
45 l_dummy NUMBER;
46
47 CURSOR c_id IS
48 SELECT AMS_CTD_ADHOC_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_ADHOC_PARAM_VALUES
54 WHERE ADHOC_PARAM_ID = l_id;
55
56 BEGIN
57 -- Standard Start of API savepoint
58 SAVEPOINT CREATE_Adhoc_Param_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_adhoc_param_rec.ADHOC_PARAM_ID IS NULL OR p_adhoc_param_rec.ADHOC_PARAM_ID = FND_API.g_miss_num THEN
85 LOOP
86 l_dummy := NULL;
87 OPEN c_id;
88 FETCH c_id INTO l_ADHOC_PARAM_ID;
89 CLOSE c_id;
90
91 OPEN c_id_exists(l_ADHOC_PARAM_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_Adhoc_Param');
112
113 -- Invoke validation procedures
114 Validate_adhoc_param(
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_adhoc_param_rec => p_adhoc_param_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_ADHOC_PARAM_VALUES_PKG.Insert_Row)
133 AMS_CTD_ADHOC_PARAM_VALUES_PKG.Insert_Row(
134 px_adhoc_param_id => l_adhoc_param_id,
135 p_adhoc_param_code => p_adhoc_param_rec.adhoc_param_code,
136 p_adhoc_param_value => p_adhoc_param_rec.adhoc_param_value,
137 p_ctd_id => p_adhoc_param_rec.ctd_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_adhoc_param_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_Adhoc_Param_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_Adhoc_Param_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_Adhoc_Param_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_Adhoc_Param;
207
208
209 PROCEDURE Update_Adhoc_Param(
210 p_api_version_number IN NUMBER,
211 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
215 x_return_status OUT NOCOPY VARCHAR2,
212 p_commit IN VARCHAR2 := FND_API.G_FALSE,
213 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
214
216 x_msg_count OUT NOCOPY NUMBER,
217 x_msg_data OUT NOCOPY VARCHAR2,
218
219 p_adhoc_param_rec IN adhoc_param_rec_type,
220 x_object_version_number OUT NOCOPY NUMBER
221 )
222
223 IS
224 CURSOR c_get_adhoc_param(adhoc_param_id NUMBER) IS
225 SELECT *
226 FROM AMS_CTD_ADHOC_PARAM_VALUES;
227 -- Hint: Developer need to provide Where clause
228 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Adhoc_Param';
229 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
230 -- Local Variables
231 l_object_version_number NUMBER;
232 l_ADHOC_PARAM_ID NUMBER;
233 l_ref_adhoc_param_rec c_get_Adhoc_Param%ROWTYPE ;
234 l_tar_adhoc_param_rec AMS_Adhoc_Param_PVT.adhoc_param_rec_type := P_adhoc_param_rec;
235 l_rowid ROWID;
236
237 BEGIN
238 -- Standard Start of API savepoint
239 SAVEPOINT UPDATE_Adhoc_Param_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_Adhoc_Param( l_tar_adhoc_param_rec.adhoc_param_id);
268
269 FETCH c_get_Adhoc_Param INTO l_ref_adhoc_param_rec ;
270
271 If ( c_get_Adhoc_Param%NOTFOUND) THEN
272 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
273 p_token_name => 'INFO',
274 p_token_value => 'Adhoc_Param') ;
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_Adhoc_Param;
280 */
281
282
283 If (l_tar_adhoc_param_rec.object_version_number is NULL or
284 l_tar_adhoc_param_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_adhoc_param_rec.object_version_number <> l_ref_adhoc_param_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 => 'Adhoc_Param') ;
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_Adhoc_Param');
301
302 -- Invoke validation procedures
303 Validate_adhoc_param(
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_adhoc_param_rec => p_adhoc_param_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_ADHOC_PARAM_VALUES_PKG.Update_Row)
322 AMS_CTD_ADHOC_PARAM_VALUES_PKG.Update_Row(
323 p_adhoc_param_id => p_adhoc_param_rec.adhoc_param_id,
324 p_adhoc_param_code => p_adhoc_param_rec.adhoc_param_code,
325 p_adhoc_param_value => p_adhoc_param_rec.adhoc_param_value,
326 p_ctd_id => p_adhoc_param_rec.ctd_id,
327 p_object_version_number => p_adhoc_param_rec.object_version_number,
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_adhoc_param_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
355 WHEN AMS_Utility_PVT.resource_locked THEN
352 );
353 EXCEPTION
354
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_Adhoc_Param_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_Adhoc_Param_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_Adhoc_Param_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_Adhoc_Param;
393
394
395 PROCEDURE Delete_Adhoc_Param(
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_adhoc_param_id IN NUMBER,
404 p_object_version_number IN NUMBER
405 )
406
407 IS
408 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Adhoc_Param';
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_Adhoc_Param_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_ADHOC_PARAM_VALUES_PKG.Delete_Row)
445 AMS_CTD_ADHOC_PARAM_VALUES_PKG.Delete_Row(
446 p_ADHOC_PARAM_ID => p_ADHOC_PARAM_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
473 ROLLBACK TO DELETE_Adhoc_Param_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
476 FND_MSG_PUB.Count_And_Get (
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_Adhoc_Param_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_Adhoc_Param_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
503 p_data => x_msg_data
500 FND_MSG_PUB.Count_And_Get (
501 p_encoded => FND_API.G_FALSE,
502 p_count => x_msg_count,
504 );
505 End Delete_Adhoc_Param;
506
507
508
509 -- Hint: Primary key needs to be returned.
510 PROCEDURE Lock_Adhoc_Param(
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,
518 p_adhoc_param_id IN NUMBER,
515 x_msg_count OUT NOCOPY NUMBER,
516 x_msg_data OUT NOCOPY VARCHAR2,
517
519 p_object_version IN NUMBER
520 )
521
522 IS
523 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Adhoc_Param';
524 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
525 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
526 l_ADHOC_PARAM_ID NUMBER;
527
528 CURSOR c_Adhoc_Param IS
529 SELECT ADHOC_PARAM_ID
530 FROM AMS_CTD_ADHOC_PARAM_VALUES
531 WHERE ADHOC_PARAM_ID = p_ADHOC_PARAM_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_Adhoc_Param;
564
565 FETCH c_Adhoc_Param INTO l_ADHOC_PARAM_ID;
566
567 IF (c_Adhoc_Param%NOTFOUND) THEN
568 CLOSE c_Adhoc_Param;
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_Adhoc_Param;
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_Adhoc_Param_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_Adhoc_Param_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_Adhoc_Param_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 );
623 End Lock_Adhoc_Param;
624
625
626 PROCEDURE check_adhoc_param_uk_items(
630 IS
627 p_adhoc_param_rec IN adhoc_param_rec_type,
628 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
629 x_return_status OUT NOCOPY VARCHAR2)
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_ADHOC_PARAM_VALUES',
638 'ADHOC_PARAM_ID = ''' || p_adhoc_param_rec.ADHOC_PARAM_ID ||''''
639 );
640 ELSE
641 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
642 'AMS_CTD_ADHOC_PARAM_VALUES',
643 'ADHOC_PARAM_ID = ''' || p_adhoc_param_rec.ADHOC_PARAM_ID ||
644 ''' AND ADHOC_PARAM_ID <> ' || p_adhoc_param_rec.ADHOC_PARAM_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_ADHOC_PARAM_ID_DUPLICATE');
650 x_return_status := FND_API.g_ret_sts_error;
651 RETURN;
652 END IF;
653
654 END check_adhoc_param_uk_items;
655
656 PROCEDURE check_adhoc_param_req_items(
657 p_adhoc_param_rec IN adhoc_param_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_adhoc_param_rec.adhoc_param_id = FND_API.g_miss_num OR p_adhoc_param_rec.adhoc_param_id IS NULL THEN
669 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_adhoc_param_id');
670 x_return_status := FND_API.g_ret_sts_error;
671 RETURN;
672 END IF;
673
674
675 IF p_adhoc_param_rec.adhoc_param_code = FND_API.g_miss_char OR p_adhoc_param_rec.adhoc_param_code IS NULL THEN
676 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_adhoc_param_code');
677 x_return_status := FND_API.g_ret_sts_error;
678 RETURN;
679 END IF;
680
681
682 IF p_adhoc_param_rec.adhoc_param_value = FND_API.g_miss_char OR p_adhoc_param_rec.adhoc_param_value IS NULL THEN
683 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_adhoc_param_value');
684 x_return_status := FND_API.g_ret_sts_error;
685 RETURN;
686 END IF;
687
688
689 IF p_adhoc_param_rec.ctd_id = FND_API.g_miss_num OR p_adhoc_param_rec.ctd_id IS NULL THEN
690 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_ctd_id');
691 x_return_status := FND_API.g_ret_sts_error;
692 RETURN;
696 IF p_adhoc_param_rec.last_update_date = FND_API.g_miss_date OR p_adhoc_param_rec.last_update_date IS NULL THEN
693 END IF;
694
695
697 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_last_update_date');
698 x_return_status := FND_API.g_ret_sts_error;
699 RETURN;
700 END IF;
701
702
703 IF p_adhoc_param_rec.last_updated_by = FND_API.g_miss_num OR p_adhoc_param_rec.last_updated_by IS NULL THEN
704 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_last_updated_by');
705 x_return_status := FND_API.g_ret_sts_error;
706 RETURN;
707 END IF;
708
709
710 IF p_adhoc_param_rec.creation_date = FND_API.g_miss_date OR p_adhoc_param_rec.creation_date IS NULL THEN
711 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_creation_date');
712 x_return_status := FND_API.g_ret_sts_error;
713 RETURN;
714 END IF;
715
716
717 IF p_adhoc_param_rec.created_by = FND_API.g_miss_num OR p_adhoc_param_rec.created_by IS NULL THEN
718 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_created_by');
719 x_return_status := FND_API.g_ret_sts_error;
720 RETURN;
721 END IF;
722 ELSE
723
724
725 IF p_adhoc_param_rec.adhoc_param_id IS NULL THEN
726 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_adhoc_param_id');
727 x_return_status := FND_API.g_ret_sts_error;
728 RETURN;
729 END IF;
730
731
732 IF p_adhoc_param_rec.adhoc_param_code IS NULL THEN
733 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_adhoc_param_code');
734 x_return_status := FND_API.g_ret_sts_error;
735 RETURN;
736 END IF;
737
738
739 IF p_adhoc_param_rec.adhoc_param_value IS NULL THEN
740 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_adhoc_param_value');
741 x_return_status := FND_API.g_ret_sts_error;
742 RETURN;
743 END IF;
744
745
746 IF p_adhoc_param_rec.ctd_id IS NULL THEN
747 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_ctd_id');
748 x_return_status := FND_API.g_ret_sts_error;
749 RETURN;
750 END IF;
751
752
753 IF p_adhoc_param_rec.last_update_date IS NULL THEN
754 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_last_update_date');
755 x_return_status := FND_API.g_ret_sts_error;
756 RETURN;
757 END IF;
758
759
760 IF p_adhoc_param_rec.last_updated_by IS NULL THEN
761 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_last_updated_by');
762 x_return_status := FND_API.g_ret_sts_error;
763 RETURN;
764 END IF;
765
766
767 IF p_adhoc_param_rec.creation_date IS NULL THEN
768 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_creation_date');
769 x_return_status := FND_API.g_ret_sts_error;
770 RETURN;
771 END IF;
772
773
774 IF p_adhoc_param_rec.created_by IS NULL THEN
775 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_adhoc_param_NO_created_by');
776 x_return_status := FND_API.g_ret_sts_error;
777 RETURN;
778 END IF;
779 END IF;
780
781 END check_adhoc_param_req_items;
782
783 PROCEDURE check_adhoc_param_FK_items(
784 p_adhoc_param_rec IN adhoc_param_rec_type,
785 x_return_status OUT NOCOPY VARCHAR2
786 )
787 IS
788 BEGIN
789 x_return_status := FND_API.g_ret_sts_success;
790
791 -- Enter custom code here
792
793 END check_adhoc_param_FK_items;
794
795 PROCEDURE check_adhoc_param_Lookup_items(
796 p_adhoc_param_rec IN adhoc_param_rec_type,
797 x_return_status OUT NOCOPY VARCHAR2
798 )
799 IS
800 BEGIN
801 x_return_status := FND_API.g_ret_sts_success;
802
803 -- Enter custom code here
804
805 END check_adhoc_param_Lookup_items;
806
807 PROCEDURE Check_adhoc_param_Items (
808 P_adhoc_param_rec IN adhoc_param_rec_type,
809 p_validation_mode IN VARCHAR2,
810 x_return_status OUT NOCOPY VARCHAR2
811 )
812 IS
813 BEGIN
814
815 -- Check Items Uniqueness API calls
816
817 check_adhoc_param_uk_items(
818 p_adhoc_param_rec => p_adhoc_param_rec,
819 p_validation_mode => p_validation_mode,
820 x_return_status => x_return_status);
821 IF x_return_status <> FND_API.g_ret_sts_success THEN
822 RETURN;
823 END IF;
824
825 -- Check Items Required/NOT NULL API calls
826
827 check_adhoc_param_req_items(
828 p_adhoc_param_rec => p_adhoc_param_rec,
829 p_validation_mode => p_validation_mode,
830 x_return_status => x_return_status);
831 IF x_return_status <> FND_API.g_ret_sts_success THEN
832 RETURN;
833 END IF;
834 -- Check Items Foreign Keys API calls
835
836 check_adhoc_param_FK_items(
837 p_adhoc_param_rec => p_adhoc_param_rec,
838 x_return_status => x_return_status);
839 IF x_return_status <> FND_API.g_ret_sts_success THEN
840 RETURN;
841 END IF;
842 -- Check Items Lookups
843
844 check_adhoc_param_Lookup_items(
845 p_adhoc_param_rec => p_adhoc_param_rec,
846 x_return_status => x_return_status);
847 IF x_return_status <> FND_API.g_ret_sts_success THEN
848 RETURN;
849 END IF;
850
851 END Check_adhoc_param_Items;
852
853 PROCEDURE Complete_adhoc_param_Rec (
857 l_return_status VARCHAR2(1);
854 p_adhoc_param_rec IN adhoc_param_rec_type,
855 x_complete_rec OUT NOCOPY adhoc_param_rec_type)
856 IS
858
859 CURSOR c_complete IS
860 SELECT *
861 FROM ams_ctd_adhoc_param_values
862 WHERE adhoc_param_id = p_adhoc_param_rec.adhoc_param_id;
863 l_adhoc_param_rec c_complete%ROWTYPE;
864 BEGIN
865 x_complete_rec := p_adhoc_param_rec;
866
867
868 OPEN c_complete;
869 FETCH c_complete INTO l_adhoc_param_rec;
870 CLOSE c_complete;
871
872 -- adhoc_param_id
873 IF p_adhoc_param_rec.adhoc_param_id = FND_API.g_miss_num THEN
874 x_complete_rec.adhoc_param_id := l_adhoc_param_rec.adhoc_param_id;
875 END IF;
876
877 -- adhoc_param_code
878 IF p_adhoc_param_rec.adhoc_param_code = FND_API.g_miss_char THEN
879 x_complete_rec.adhoc_param_code := l_adhoc_param_rec.adhoc_param_code;
880 END IF;
881
882 -- adhoc_param_value
883 IF p_adhoc_param_rec.adhoc_param_value = FND_API.g_miss_char THEN
884 x_complete_rec.adhoc_param_value := l_adhoc_param_rec.adhoc_param_value;
885 END IF;
886
887 -- ctd_id
888 IF p_adhoc_param_rec.ctd_id = FND_API.g_miss_num THEN
889 x_complete_rec.ctd_id := l_adhoc_param_rec.ctd_id;
890 END IF;
891
892 -- object_version_number
893 IF p_adhoc_param_rec.object_version_number = FND_API.g_miss_num THEN
894 x_complete_rec.object_version_number := l_adhoc_param_rec.object_version_number;
895 END IF;
896
897 -- last_update_date
898 IF p_adhoc_param_rec.last_update_date = FND_API.g_miss_date THEN
899 x_complete_rec.last_update_date := l_adhoc_param_rec.last_update_date;
900 END IF;
901
902 -- last_updated_by
903 IF p_adhoc_param_rec.last_updated_by = FND_API.g_miss_num THEN
904 x_complete_rec.last_updated_by := l_adhoc_param_rec.last_updated_by;
905 END IF;
906
907 -- last_update_login
908 IF p_adhoc_param_rec.last_update_login = FND_API.g_miss_num THEN
909 x_complete_rec.last_update_login := l_adhoc_param_rec.last_update_login;
910 END IF;
911
912 -- creation_date
913 IF p_adhoc_param_rec.creation_date = FND_API.g_miss_date THEN
914 x_complete_rec.creation_date := l_adhoc_param_rec.creation_date;
915 END IF;
916
917 -- created_by
918 IF p_adhoc_param_rec.created_by = FND_API.g_miss_num THEN
919 x_complete_rec.created_by := l_adhoc_param_rec.created_by;
920 END IF;
921
922 -- security_group_id
923 IF p_adhoc_param_rec.security_group_id = FND_API.g_miss_num THEN
924 x_complete_rec.security_group_id := l_adhoc_param_rec.security_group_id;
925 END IF;
926 -- Note: Developers need to modify the procedure
927 -- to handle any business specific requirements.
928 END Complete_adhoc_param_Rec;
929 PROCEDURE Validate_adhoc_param(
930 p_api_version_number IN NUMBER,
931 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
932 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
933 p_adhoc_param_rec IN adhoc_param_rec_type,
934 x_return_status OUT NOCOPY VARCHAR2,
935 x_msg_count OUT NOCOPY NUMBER,
936 x_msg_data OUT NOCOPY VARCHAR2
937 )
938 IS
939 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Adhoc_Param';
940 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
941 l_object_version_number NUMBER;
942 l_adhoc_param_rec AMS_Adhoc_Param_PVT.adhoc_param_rec_type;
943
944 BEGIN
945 -- Standard Start of API savepoint
946 SAVEPOINT VALIDATE_Adhoc_Param_;
947
948 -- Standard call to check for call compatibility.
949 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
950 p_api_version_number,
951 l_api_name,
952 G_PKG_NAME)
953 THEN
954 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
955 END IF;
956
957 -- Initialize message list if p_init_msg_list is set to TRUE.
958 IF FND_API.to_Boolean( p_init_msg_list )
959 THEN
960 FND_MSG_PUB.initialize;
961 END IF;
962 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
963 Check_adhoc_param_Items(
964 p_adhoc_param_rec => p_adhoc_param_rec,
965 p_validation_mode => JTF_PLSQL_API.g_update,
966 x_return_status => x_return_status
967 );
968
969 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
970 RAISE FND_API.G_EXC_ERROR;
971 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
973 END IF;
974 END IF;
975
976 Complete_adhoc_param_Rec(
977 p_adhoc_param_rec => p_adhoc_param_rec,
978 x_complete_rec => l_adhoc_param_rec
979 );
980
981 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
982 Validate_adhoc_param_Rec(
983 p_api_version_number => 1.0,
984 p_init_msg_list => FND_API.G_FALSE,
985 x_return_status => x_return_status,
986 x_msg_count => x_msg_count,
987 x_msg_data => x_msg_data,
988 p_adhoc_param_rec => l_adhoc_param_rec);
989
990 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
991 RAISE FND_API.G_EXC_ERROR;
992 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
996
993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
994 END IF;
995 END IF;
997
998 -- Debug Message
999 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1000
1001
1002 -- Initialize API return status to SUCCESS
1003 x_return_status := FND_API.G_RET_STS_SUCCESS;
1004
1005
1006 -- Debug Message
1007 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1008
1009 -- Standard call to get message count and if count is 1, get message info.
1010 FND_MSG_PUB.Count_And_Get
1011 (p_count => x_msg_count,
1012 p_data => x_msg_data
1013 );
1014 EXCEPTION
1015
1016 WHEN AMS_Utility_PVT.resource_locked THEN
1017 x_return_status := FND_API.g_ret_sts_error;
1018 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1019
1020 WHEN FND_API.G_EXC_ERROR THEN
1021 ROLLBACK TO VALIDATE_Adhoc_Param_;
1022 x_return_status := FND_API.G_RET_STS_ERROR;
1023 -- Standard call to get message count and if count=1, get the message
1024 FND_MSG_PUB.Count_And_Get (
1025 p_encoded => FND_API.G_FALSE,
1026 p_count => x_msg_count,
1027 p_data => x_msg_data
1028 );
1029
1030 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1031 ROLLBACK TO VALIDATE_Adhoc_Param_;
1032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1033 -- Standard call to get message count and if count=1, get the message
1034 FND_MSG_PUB.Count_And_Get (
1035 p_encoded => FND_API.G_FALSE,
1036 p_count => x_msg_count,
1037 p_data => x_msg_data
1038 );
1039
1040 WHEN OTHERS THEN
1041 ROLLBACK TO VALIDATE_Adhoc_Param_;
1042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1043 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1044 THEN
1045 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1046 END IF;
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 End Validate_Adhoc_Param;
1054
1055
1056 PROCEDURE Validate_adhoc_param_rec(
1057 p_api_version_number IN NUMBER,
1058 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1059 x_return_status OUT NOCOPY VARCHAR2,
1060 x_msg_count OUT NOCOPY NUMBER,
1061 x_msg_data OUT NOCOPY VARCHAR2,
1062 p_adhoc_param_rec IN adhoc_param_rec_type
1063 )
1064 IS
1065 BEGIN
1066 -- Initialize message list if p_init_msg_list is set to TRUE.
1067 IF FND_API.to_Boolean( p_init_msg_list )
1068 THEN
1069 FND_MSG_PUB.initialize;
1070 END IF;
1071
1072 -- Initialize API return status to SUCCESS
1073 x_return_status := FND_API.G_RET_STS_SUCCESS;
1074
1075 -- Hint: Validate data
1076 -- If data not valid
1077 -- THEN
1078 -- x_return_status := FND_API.G_RET_STS_ERROR;
1079
1080 -- Debug Message
1081 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1082 -- Standard call to get message count and if count is 1, get message info.
1083 FND_MSG_PUB.Count_And_Get
1084 (p_count => x_msg_count,
1085 p_data => x_msg_data
1086 );
1087 END Validate_adhoc_param_Rec;
1088
1089 END AMS_Adhoc_Param_PVT;