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