[Home] [Help]
PACKAGE BODY: APPS.AMS_ACTCATEGORY_PVT
Source
1 PACKAGE BODY AMS_ActCategory_PVT as
2 /*$Header: amsvactb.pls 120.1 2005/06/15 01:31:45 appldev $*/
3
4 -- NAME
5 -- AMS_ActCategory_PVT
6 --
7 -- HISTORY
8 -- 11/8/99 sugupta CREATED
9 --
10 G_PACKAGE_NAME CONSTANT VARCHAR2(30):='AMS_ActCategory_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvactb.pls';
12
13 -- Debug mode
14 g_debug boolean := FALSE;
15 --g_debug boolean := TRUE;
16
17 --
18 -- Procedure and function declarations.
19
20 /*****************************************************************************************/
21 -- Start of Comments
22 --
23 -- NAME
24 -- Create_Act_Category
25 --
26 -- PURPOSE
27 -- This procedure is to create a category record that satisfy caller needs
28 --
29 -- HISTORY
30 -- 11/8/1999 sugupta created
31 -- End of Comments
32
33 PROCEDURE Create_Act_Category
34 ( p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE,
37 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41
42 p_act_category_rec IN act_category_rec_type,
43 x_act_category_id OUT NOCOPY NUMBER
44 ) IS
45
46 l_api_name CONSTANT VARCHAR2(30) := 'Create_Act_Category';
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME || '.' || l_api_name;
49
50 -- Status Local Variables
51 l_return_status VARCHAR2(1); -- Return value from procedures
52 l_act_category_rec act_category_rec_type := p_act_category_rec;
53
54 l_act_category_id NUMBER;
55
56 CURSOR C_act_category_id IS
57 SELECT ams_act_categories_s.NEXTVAL
58 FROM dual;
59
60 BEGIN
61
62 -- Standard Start of API savepoint
63 SAVEPOINT Create_Act_Category_PVT;
64
65 -- Standard call to check for call compatibility.
66 IF NOT FND_API.Compatible_API_Call ( l_api_version,
67 p_api_version,
68 l_api_name,
69 G_PACKAGE_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 -- Initialize API return status to success
81 x_return_status := FND_API.G_RET_STS_SUCCESS;
82
83 --
84 -- API body
85 --
86 ----------------------- validate -----------------------
87 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
88
89 Validate_Act_Category
90 ( p_api_version => 1.0
91 ,p_init_msg_list => p_init_msg_list
92 ,p_validation_level => p_validation_level
93 ,x_return_status => l_return_status
94 ,x_msg_count => x_msg_count
95 ,x_msg_data => x_msg_data
96
97 ,p_act_category_rec => l_act_category_rec
98 );
99
100 -- If any errors happen abort API.
101 IF l_return_status = FND_API.G_RET_STS_ERROR
102 THEN
103 RAISE FND_API.G_EXC_ERROR;
104 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
105 THEN
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 END IF;
108
109 --
110 -- Get ID for activity category from sequence.
111 OPEN c_act_category_id;
112 FETCH c_act_category_id INTO l_act_category_rec.activity_category_id;
113 CLOSE c_act_category_id;
114
115
116 INSERT INTO AMS_ACT_CATEGORIES
117 (activity_category_id,
118
119 -- standard who columns
120 last_update_date,
121 last_updated_by,
122 creation_date,
123 created_by,
124 last_update_login,
125
126 -- other columns
127 object_version_number,
128 act_category_used_by_id,
129 arc_act_category_used_by,
130 category_id,
131
132 attribute_category,
133 attribute1,
134 attribute2,
135 attribute3,
136 attribute4,
137 attribute5,
138 attribute6,
139 attribute7,
140 attribute8,
141 attribute9,
142 attribute10,
143 attribute11,
144 attribute12,
145 attribute13,
146 attribute14,
147 attribute15
148
149 )
150 VALUES
151 (
152 l_act_category_rec.activity_category_id,
153
154 -- standard who columns
155 sysdate,
156 FND_GLOBAL.User_Id,
157 sysdate,
158 FND_GLOBAL.User_Id,
159 FND_GLOBAL.Conc_Login_Id,
160
161 1, -- object_version_number
162 l_act_category_rec.act_category_used_by_id,
163 l_act_category_rec.arc_act_category_used_by,
164 l_act_category_rec.category_id,
165
166 l_act_category_rec.attribute_category,
167 l_act_category_rec.attribute1,
168 l_act_category_rec.attribute2,
169 l_act_category_rec.attribute3,
170 l_act_category_rec.attribute4,
171 l_act_category_rec.attribute5,
172 l_act_category_rec.attribute6,
173 l_act_category_rec.attribute7,
174 l_act_category_rec.attribute8,
175 l_act_category_rec.attribute9,
176 l_act_category_rec.attribute10,
177 l_act_category_rec.attribute11,
178 l_act_category_rec.attribute12,
179 l_act_category_rec.attribute13,
180 l_act_category_rec.attribute14,
181 l_act_category_rec.attribute15
182
183 );
184
185 -- set OUT value
186 x_act_category_id := l_act_category_rec.activity_category_id;
187
188 --
189 -- END of API body.
190 --
191
192 -- Standard check of p_commit.
193 IF FND_API.To_Boolean ( p_commit )
194 THEN
195 COMMIT WORK;
196 END IF;
197
198 -- Standard call to get message count AND IF count is 1, get message info.
199 FND_MSG_PUB.Count_AND_Get
200 ( p_count => x_msg_count,
201 p_data => x_msg_data,
202 p_encoded => FND_API.G_FALSE
203 );
204
205 EXCEPTION
206
207 WHEN FND_API.G_EXC_ERROR THEN
208
209 ROLLBACK TO Create_Act_Category_PVT;
210 x_return_status := FND_API.G_RET_STS_ERROR ;
211
212 FND_MSG_PUB.Count_AND_Get
213 ( p_count => x_msg_count,
214 p_data => x_msg_data,
215 p_encoded => FND_API.G_FALSE
216 );
217
218
219 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
220
221 ROLLBACK TO Create_Act_Category_PVT;
222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
223
224 FND_MSG_PUB.Count_AND_Get
225 ( p_count => x_msg_count,
226 p_data => x_msg_data,
227 p_encoded => FND_API.G_FALSE
228 );
229
230
231 WHEN OTHERS THEN
232
233 ROLLBACK TO Create_Act_Category_PVT;
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
235
236 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
237 THEN
238 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
239 END IF;
240
241 FND_MSG_PUB.Count_AND_Get
242 ( p_count => x_msg_count,
243 p_data => x_msg_data,
244 p_encoded => FND_API.G_FALSE
245 );
246
247 END Create_Act_Category;
248
249 /*****************************************************************************************/
250 -- Start of Comments
251 --
252 -- NAME
253 -- Update_Act_Category
254 --
255 -- PURPOSE
256 -- This procedure is to update a category record that satisfy caller needs
257 --
258 -- HISTORY
259 -- 11/8/1999 sugupta created
260 -- End of Comments
261
262 PROCEDURE Update_Act_Category
263 ( p_api_version IN NUMBER,
264 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
265 p_commit IN VARCHAR2 := FND_API.G_FALSE,
266 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
267 x_return_status OUT NOCOPY VARCHAR2,
268 x_msg_count OUT NOCOPY NUMBER,
269 x_msg_data OUT NOCOPY VARCHAR2,
270
271 p_act_category_rec IN act_category_rec_type
272 ) IS
273 l_api_name CONSTANT VARCHAR2(30) := 'Update_Act_Category';
274 l_api_version CONSTANT NUMBER := 1.0;
275
276 -- Status Local Variables
277 l_return_status VARCHAR2(1); -- Return value from procedures
278 l_act_category_rec act_category_rec_type;
279
280 BEGIN
281
282 -- Standard Start of API savepoint
283 SAVEPOINT Update_Act_Category_PVT;
284
285 -- Standard call to check for call compatibility.
286 IF NOT FND_API.Compatible_API_Call ( l_api_version,
287 p_api_version,
288 l_api_name,
289 G_PACKAGE_NAME)
290 THEN
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292 END IF;
293
294 -- Initialize message list IF p_init_msg_list is set to TRUE.
295 IF FND_API.to_Boolean( p_init_msg_list ) THEN
296 FND_MSG_PUB.initialize;
297 END IF;
298
299 -- Initialize API return status to success
300 x_return_status := FND_API.G_RET_STS_SUCCESS;
301
302 --
303 -- API body
304 --
305 complete_act_category_rec(
306 p_act_category_rec,
307 l_act_category_rec
308 );
309
310 -- Perform the database operation
311
312 Validate_Act_Category
313 ( p_api_version => 1.0
314 ,p_init_msg_list => p_init_msg_list
315 ,p_validation_level => p_validation_level
316 ,x_return_status => l_return_status
317 ,x_msg_count => x_msg_count
318 ,x_msg_data => x_msg_data
319 ,p_act_category_rec => l_act_category_rec
320 );
321
322 -- If any errors happen abort API.
323 IF l_return_status = FND_API.G_RET_STS_ERROR
324 THEN
325 RAISE FND_API.G_EXC_ERROR;
326 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
327 THEN
328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
329 END IF;
330
331 update AMS_ACT_CATEGORIES
332 set
333 last_update_date = sysdate
334 ,last_updated_by = FND_GLOBAL.User_Id
335 ,last_update_login = FND_GLOBAL.Conc_Login_Id
336
337 ,object_version_number = l_act_category_rec.object_version_number+1
338 ,act_category_used_by_id = l_act_category_rec.act_category_used_by_id
339 ,arc_act_category_used_by = l_act_category_rec.arc_act_category_used_by
340 ,category_id = l_act_category_rec.activity_category_id
341 ,attribute_category = l_act_category_rec.attribute_category
342 ,attribute1 = l_act_category_rec.attribute1
343 ,attribute2 = l_act_category_rec.attribute2
344 ,attribute3 = l_act_category_rec.attribute3
345 ,attribute4 = l_act_category_rec.attribute4
346 ,attribute5 = l_act_category_rec.attribute5
347 ,attribute6 = l_act_category_rec.attribute6
348 ,attribute7 = l_act_category_rec.attribute7
349 ,attribute8 = l_act_category_rec.attribute8
350 ,attribute9 = l_act_category_rec.attribute9
351 ,attribute10 = l_act_category_rec.attribute10
352 ,attribute11 = l_act_category_rec.attribute11
353 ,attribute12 = l_act_category_rec.attribute12
354 ,attribute13 = l_act_category_rec.attribute13
355 ,attribute14 = l_act_category_rec.attribute14
356 ,attribute15 = l_act_category_rec.attribute15
357
358 where activity_category_id = l_act_category_rec.activity_category_id
359 and object_version_number = l_act_category_rec.object_version_number;
360
361 IF (SQL%NOTFOUND)
362 THEN
363 -- Error, check the msg level and added an error message to the
364 -- API message list
365 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
366 THEN -- MMSG
367 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
368 FND_MSG_PUB.Add;
369 END IF;
370
371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
372 END IF;
373
374 --
375 -- END of API body.
376 --
377
378 -- Standard check of p_commit.
379 IF FND_API.To_Boolean ( p_commit )
380 THEN
381 COMMIT WORK;
382 END IF;
383
384 -- Standard call to get message count AND IF count is 1, get message info.
385 FND_MSG_PUB.Count_AND_Get
386 ( p_count => x_msg_count,
387 p_data => x_msg_data,
388 p_encoded => FND_API.G_FALSE
389 );
390
391 EXCEPTION
392
393 WHEN FND_API.G_EXC_ERROR THEN
394
395 ROLLBACK TO Update_Act_Category_PVT;
396 x_return_status := FND_API.G_RET_STS_ERROR ;
397
398 FND_MSG_PUB.Count_AND_Get
399 ( p_count => x_msg_count,
400 p_data => x_msg_data,
401 p_encoded => FND_API.G_FALSE
402 );
403
404
405 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
406
407 ROLLBACK TO Update_Act_Category_PVT;
408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
409
410 FND_MSG_PUB.Count_AND_Get
411 ( p_count => x_msg_count,
412 p_data => x_msg_data,
413 p_encoded => FND_API.G_FALSE
414 );
415
416
417 WHEN OTHERS THEN
418
419 ROLLBACK TO Update_Act_Category_PVT;
420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
421
425 END IF;
422 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
423 THEN
424 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
426
427 FND_MSG_PUB.Count_AND_Get
428 ( p_count => x_msg_count,
429 p_data => x_msg_data,
430 p_encoded => FND_API.G_FALSE
431 );
432
433 END Update_Act_Category;
434
435 /*****************************************************************************************/
436 -- Start of Comments
437 --
438 -- NAME
439 -- Delete_Act_Category
440 --
441 -- PURPOSE
442 -- This procedure is to delete a category record that satisfy caller needs
443 --
444 -- HISTORY
445 -- 11/8/1999 sugupta created
446 -- End of Comments
447
448 PROCEDURE Delete_Act_Category
449 ( p_api_version IN NUMBER,
450 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
451 p_commit IN VARCHAR2 := FND_API.G_FALSE,
452 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
453 x_return_status OUT NOCOPY VARCHAR2,
454 x_msg_count OUT NOCOPY NUMBER,
455 x_msg_data OUT NOCOPY VARCHAR2,
456
457 p_act_category_id IN NUMBER,
458 p_object_version IN NUMBER
459 ) IS
460
461 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Act_Category';
462 l_api_version CONSTANT NUMBER := 1.0;
463
464 -- Status Local Variables
465 l_return_status VARCHAR2(1); -- Return value from procedures
466 l_act_category_id NUMBER := p_act_category_id;
467 BEGIN
468
469 -- Standard Start of API savepoint
470 SAVEPOINT Delete_Act_Category_PVT;
471
472 -- Standard call to check for call compatibility.
473 IF NOT FND_API.Compatible_API_Call ( l_api_version,
474 p_api_version,
475 l_api_name,
476 G_PACKAGE_NAME)
477 THEN
478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
479 END IF;
480
481 -- Initialize message list IF p_init_msg_list is set to TRUE.
482 IF FND_API.to_Boolean( p_init_msg_list )
483 THEN
484 FND_MSG_PUB.initialize;
485 END IF;
486
487 -- Initialize API return status to success
488 x_return_status := FND_API.G_RET_STS_SUCCESS;
489
490 --
491 -- API body
492 --
493
494 -- Perform the database operation
495
496 -- Delete header data
497 DELETE FROM AMS_ACT_CATEGORIES
498 WHERE activity_category_id = l_act_category_id
499 and object_version_number = p_object_version;
500
501 IF SQL%NOTFOUND THEN
502 --
503 -- Add error message to API message list.
504 --
505 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
506 THEN
507 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
508 FND_MSG_PUB.add;
509 END IF;
510
511 RAISE FND_API.G_EXC_ERROR;
512 END IF;
513
514 --
515 -- END of API body.
516 --
517
518 -- Standard check of p_commit.
519 IF FND_API.To_Boolean ( p_commit )
520 THEN
521 COMMIT WORK;
522 END IF;
523
524 -- Standard call to get message count AND IF count is 1, get message info.
525 FND_MSG_PUB.Count_AND_Get
526 ( p_count => x_msg_count,
527 p_data => x_msg_data,
528 p_encoded => FND_API.G_FALSE
529 );
530
531 EXCEPTION
532
533 WHEN FND_API.G_EXC_ERROR THEN
534
535 ROLLBACK TO Delete_Act_Category_PVT;
536 x_return_status := FND_API.G_RET_STS_ERROR ;
537
538 FND_MSG_PUB.Count_AND_Get
539 ( p_count => x_msg_count,
540 p_data => x_msg_data,
541 p_encoded => FND_API.G_FALSE
542 );
543
544
545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
546
547 ROLLBACK TO Delete_Act_Category_PVT;
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
549
550 FND_MSG_PUB.Count_AND_Get
551 ( p_count => x_msg_count,
552 p_data => x_msg_data,
553 p_encoded => FND_API.G_FALSE
554 );
555
556
557 WHEN OTHERS THEN
558
559 ROLLBACK TO Delete_Act_Category_PVT;
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
561
562 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
563 THEN
564 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
565 END IF;
566
567 FND_MSG_PUB.Count_AND_Get
568 ( p_count => x_msg_count,
569 p_data => x_msg_data,
570 p_encoded => FND_API.G_FALSE
571 );
572
573 END Delete_Act_Category;
574
575 /*****************************************************************************************/
576 -- Start of Comments
577 --
578 -- NAME
582 -- This procedure is to lock a category record that satisfy caller needs
579 -- Lock_Act_Category
580 --
581 -- PURPOSE
583 --
584 -- HISTORY
585 -- 11/8/1999 sugupta created
586 -- End of Comments
587
588 PROCEDURE Lock_Act_Category
589 ( p_api_version IN NUMBER,
590 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
591 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
592 x_return_status OUT NOCOPY VARCHAR2,
593 x_msg_count OUT NOCOPY NUMBER,
594 x_msg_data OUT NOCOPY VARCHAR2,
595
596 p_act_category_id IN NUMBER,
597 p_object_version IN NUMBER
598 ) IS
599
600 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Act_Category';
601 l_api_version CONSTANT NUMBER := 1.0;
602
603 -- Status Local Variables
604 l_return_status VARCHAR2(1); -- Return value from procedures
605 l_act_category_id NUMBER;
606
607 CURSOR c_act_category IS
608 SELECT activity_category_id
609 FROM AMS_ACT_CATEGORIES
610 WHERE activity_category_id = p_act_category_id
611 and object_version_number = p_object_version
612 FOR UPDATE of activity_category_id NOWAIT;
613
614 BEGIN
615
616 -- Standard call to check for call compatibility.
617 IF NOT FND_API.Compatible_API_Call ( l_api_version,
618 p_api_version,
619 l_api_name,
620 G_PACKAGE_NAME)
621 THEN
622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
623 END IF;
624
625 -- Initialize message list if p_init_msg_list is set to TRUE.
626 IF FND_API.to_Boolean( p_init_msg_list )
627 THEN
628 FND_MSG_PUB.initialize;
629 END IF;
630
631 -- Initialize API return status to success
632 x_return_status := FND_API.G_RET_STS_SUCCESS;
633
634 --
635 -- API body
636 --
637
638 -- Perform the database operation
639 OPEN c_act_category;
640 FETCH c_act_category INTO l_act_category_id;
641 IF (c_act_category%NOTFOUND) THEN
642 CLOSE c_act_category;
643 -- Error, check the msg level and added an error message to the
644 -- API message list
645 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
646 THEN -- MMSG
647
648 FND_MESSAGE.Set_Name('AMS', 'AMS_API_RECORD_NOT_FOUND');
649 FND_MSG_PUB.Add;
650 END IF;
651
652 RAISE FND_API.G_EXC_ERROR;
653 END IF;
654
655 CLOSE c_act_category;
656
657 --
658 -- END of API body.
659 --
660
661 -- Standard call to get message count AND IF count is 1, get message info.
662 FND_MSG_PUB.Count_AND_Get
663 ( p_count => x_msg_count,
664 p_data => x_msg_data,
665 p_encoded => FND_API.G_FALSE
666 );
667
668 EXCEPTION
669
670 WHEN FND_API.G_EXC_ERROR THEN
671
672 x_return_status := FND_API.G_RET_STS_ERROR ;
673
674 FND_MSG_PUB.Count_AND_Get
675 ( p_count => x_msg_count,
676 p_data => x_msg_data,
677 p_encoded => FND_API.G_FALSE
678 );
679
680
681 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
682
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
684
685 FND_MSG_PUB.Count_AND_Get
686 ( p_count => x_msg_count,
687 p_data => x_msg_data,
688 p_encoded => FND_API.G_FALSE
689 );
690
691 WHEN AMS_Utility_PVT.resource_locked THEN
692 x_return_status := FND_API.g_ret_sts_error;
693 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
694 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
695 FND_MSG_PUB.add;
696 END IF;
697
698 FND_MSG_PUB.Count_AND_Get
699 ( p_count => x_msg_count,
700 p_data => x_msg_data,
701 p_encoded => FND_API.G_FALSE
702 );
703
704 WHEN OTHERS THEN
705
706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
707
708 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
709 THEN
710 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
711 END IF;
712
713 FND_MSG_PUB.Count_AND_Get
714 ( p_count => x_msg_count,
715 p_data => x_msg_data,
716 p_encoded => FND_API.G_FALSE
717 );
718
719 END Lock_Act_Category;
720
721
722 /*****************************************************************************************/
723 -- Start of Comments
724 --
725 -- NAME
726 -- Validate_Act_Category
727 --
728 -- PURPOSE
729 -- This procedure is to validate an activity category record
730 --
731 -- HISTORY
732 -- 11/8/1999 sugupta created
733 -- End of Comments
734
738 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
735 PROCEDURE Validate_Act_Category
736 ( p_api_version IN NUMBER,
737 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
739 x_return_status OUT NOCOPY VARCHAR2,
740 x_msg_count OUT NOCOPY NUMBER,
741 x_msg_data OUT NOCOPY VARCHAR2,
742
743 p_act_category_rec IN act_category_rec_type
744 ) IS
745
746 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Act_Category';
747 l_api_version CONSTANT NUMBER := 1.0;
748 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME || '.' || l_api_name;
749
750 -- Status Local Variables
751 l_return_status VARCHAR2(1); -- Return value from procedures
752 l_act_category_rec act_category_rec_type := p_act_category_rec;
753 l_default_act_cty_rec act_category_rec_type;
754 l_act_category_id NUMBER;
755 l_dummy NUMBER;
756
757 CURSOR c_act_ctg_id_exists(ctg_id_in IN NUMBER,
758 arc_used_by in VARCHAR2,
759 arc_used_id_in in NUMBER) IS
760 SELECT 1 from dual WHERE EXISTS(select 1 FROM AMS_ACT_CATEGORIES
761 WHERE category_id = ctg_id_in
762 and ARC_ACT_CATEGORY_USED_BY = arc_used_by
763 and ACT_CATEGORY_USED_BY_ID = arc_used_id_in);
764
765
766 BEGIN
767
768 -- Standard call to check for call compatibility.
769 IF NOT FND_API.Compatible_API_Call ( l_api_version,
770 p_api_version,
771 l_api_name,
772 G_PACKAGE_NAME)
773 THEN
774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
775 END IF;
776
777 -- Initialize message list if p_init_msg_list is set to TRUE.
778 IF FND_API.to_Boolean( p_init_msg_list ) THEN
779 FND_MSG_PUB.initialize;
780 END IF;
781
782
783 -- Initialize API return status to success
784 x_return_status := FND_API.G_RET_STS_SUCCESS;
785
786 --
787 -- API body
788 --
789 AMS_Utility_PVT.debug_message(l_full_name||': check items');
790
791 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item
792 THEN
793
794 Validate_Act_Cty_Items
795 ( p_act_category_rec => l_act_category_rec,
796 p_validation_mode => JTF_PLSQL_API.g_create,
797 x_return_status => l_return_status
798 );
799
800 -- If any errors happen abort API.
801 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
802 THEN
803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
804 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
805 THEN
806 RAISE FND_API.G_EXC_ERROR;
807 END IF;
808 END IF;
809
810 -- Perform cross attribute validation and missing attribute checks. Record
811 -- level validation.
812
813 AMS_Utility_PVT.debug_message(l_full_name||': check record level');
814
815
816 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record
817 THEN
818
819 Validate_Act_Cty_Record(
820 p_act_category_rec => l_act_category_rec,
821 x_return_status => l_return_status
822 );
823
824 -- If any errors happen abort API.
825 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
826 RAISE FND_API.G_EXC_ERROR;
827 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
829 END IF;
830 END IF;
831
832 --
833 -- END of API body.
834 --
835
836 -- some logic
837 open c_act_ctg_id_exists(p_act_category_rec.CATEGORY_ID,
838 p_act_category_rec.ARC_ACT_CATEGORY_USED_BY,
839 p_act_category_rec.ACT_CATEGORY_USED_BY_ID);
840 fetch c_act_ctg_id_exists into l_dummy;
841 close c_act_ctg_id_exists;
842 IF l_dummy = 1 THEN
843 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
844 FND_MESSAGE.set_name ('AMS', 'AMS_CTG_ACT_DUP');
845 FND_MSG_PUB.add;
846 END IF;
847 x_return_status := FND_API.g_ret_sts_error;
848 RETURN;
849 END IF;
850 -------------------- finish --------------------------
851 FND_MSG_PUB.count_and_get(
852 p_encoded => FND_API.g_false,
853 p_count => x_msg_count,
854 p_data => x_msg_data
855 );
856
857 EXCEPTION
858
859 WHEN FND_API.G_EXC_ERROR THEN
860
861 x_return_status := FND_API.G_RET_STS_ERROR ;
862
863 FND_MSG_PUB.Count_AND_Get
864 ( p_count => x_msg_count,
865 p_data => x_msg_data,
866 p_encoded => FND_API.G_FALSE
867 );
868
869
870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
871
872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
873
874 FND_MSG_PUB.Count_AND_Get
875 ( p_count => x_msg_count,
876 p_data => x_msg_data,
877 p_encoded => FND_API.G_FALSE
878 );
879
880
881 WHEN OTHERS THEN
882
883 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
884
888 END IF;
885 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
886 THEN
887 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
889
890 FND_MSG_PUB.Count_AND_Get
891 ( p_count => x_msg_count,
892 p_data => x_msg_data,
893 p_encoded => FND_API.G_FALSE
894 );
895
896 END Validate_Act_Category;
897
898 /*****************************************************************************************/
899 -- Start of Comments
900 --
901 -- NAME
902 -- Validate_Act_Cty_Items
903 --
904 -- PURPOSE
905 -- This procedure is to validate category items
906 -- End of Comments
907
908 PROCEDURE Validate_Act_Cty_Items
909 ( p_act_category_rec IN act_category_rec_type,
910 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
911 x_return_status OUT NOCOPY VARCHAR2
912 ) IS
913
914 l_dummy NUMBER;
915 l_table_name VARCHAR2(30);
916 l_pk_name VARCHAR2(30);
917 l_pk_value VARCHAR2(30);
918
919 CURSOR c_act_ctg_id_exists(ctg_id_in IN NUMBER,
920 obj_ver_in IN NUMBER,
921 arc_used_by in VARCHAR2,
922 arc_used_id_in in NUMBER) IS
923 SELECT 1 from dual WHERE EXISTS(select 1 FROM AMS_ACT_CATEGORIES
924 WHERE category_id = ctg_id_in
925 and object_version_number = obj_ver_in
926 and ARC_ACT_CATEGORY_USED_BY = arc_used_by
927 and ACT_CATEGORY_USED_BY_ID = arc_used_id_in);
928
929 BEGIN
930 -- Initialize API/Procedure return status to success
931 x_return_status := FND_API.G_RET_STS_SUCCESS;
932
933 -- Check required parameters
934
935 IF (p_act_category_rec.ACT_CATEGORY_USED_BY_ID = FND_API.G_MISS_NUM OR
936 p_act_category_rec.ACT_CATEGORY_USED_BY_ID IS NULL)
937 THEN
938 -- missing required fields
939 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
940 THEN -- MMSG
941 FND_MESSAGE.set_name('AMS', 'AMS_ACT_CAT_NO_USEDBYID');
942 FND_MSG_PUB.add;
943 END IF;
944
945 x_return_status := FND_API.G_RET_STS_ERROR;
946
947 -- If any error happens abort API.
948 RETURN;
949 END IF;
950
951 -- ARC_ACT_CATEGORY_USED_BY
952
953 IF (p_act_category_rec.ARC_ACT_CATEGORY_USED_BY = FND_API.G_MISS_CHAR OR
954 p_act_category_rec.ARC_ACT_CATEGORY_USED_BY IS NULL)
955 THEN
956 -- missing required fields
957 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
958 THEN -- MMSG
959 FND_MESSAGE.set_name('AMS', 'AMS_ACT_CAT_NO_USEDBY');
960 FND_MSG_PUB.add;
961 END IF;
962
963 x_return_status := FND_API.G_RET_STS_ERROR;
964
965 -- If any error happens abort API.
966 RETURN;
967 END IF;
968
969 IF (p_act_category_rec.CATEGORY_ID = FND_API.G_MISS_NUM OR
970 p_act_category_rec.CATEGORY_ID IS NULL)
971 THEN
972
973 -- missing required fields
974 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
975 THEN -- MMSG
976 FND_MESSAGE.set_name('AMS', 'AMS_ACT_CAT_NO_CATEGORY_ID');
977 FND_MSG_PUB.add;
978 END IF;
979
980 x_return_status := FND_API.G_RET_STS_ERROR;
981
982 -- If any error happens abort API.
983 RETURN;
984 END IF;
985
986 -- Validate uniqueness
987 IF p_validation_mode = JTF_PLSQL_API.g_create
988 AND p_act_category_rec.activity_category_id IS NOT NULL
989 THEN
990 IF AMS_Utility_PVT.check_uniqueness(
991 'ams_act_categories',
992 'activity_category_id = ' || p_act_category_rec.activity_category_id
993 ) = FND_API.g_false
994 THEN
995 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
996 THEN
997 FND_MESSAGE.set_name('AMS', 'AMS_ACT_CAT_DUPLICATE_ID');
998 FND_MSG_PUB.add;
999 END IF;
1000 x_return_status := FND_API.g_ret_sts_error;
1001 RETURN;
1002 END IF;
1003 END IF;
1004
1005 --
1006 -- Begin Validate Referential
1007 --
1008 /* code for chenging ACT_CATEGORY_USED_BY_ID is wrong rewritten by mukumar
1009 on 04/10/2001
1010 -- Check FK parameter: ACT_CATEGORY_USED_BY_ID #1
1011 IF p_act_category_rec.ACT_CATEGORY_USED_BY_ID <> FND_API.g_miss_num
1012 THEN
1013 l_table_name := 'AMS_ACT_CATEGORIES';
1014 l_pk_name := 'ACT_CATEGORY_USED_BY_ID';
1015 l_pk_value := p_act_category_rec.ACT_CATEGORY_USED_BY_ID;
1016
1017 IF AMS_Utility_PVT.Check_FK_Exists (
1018 p_table_name => l_table_name
1019 ,p_pk_name => l_pk_name
1020 ,p_pk_value => l_pk_value
1021 ) = FND_API.G_FALSE
1022 THEN
1023 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1024 THEN
1025 FND_MESSAGE.set_name('AMS', 'AMS_ACT_CAT_INVALID_EVEH_REF');
1026 FND_MSG_PUB.add;
1027 END IF;
1028 x_return_status := FND_API.G_RET_STS_ERROR;
1032 END IF; -- check_fk_exists
1029 -- If any errors happen abort API/Procedure.
1030 RETURN;
1031
1033 END IF;
1034
1035 -- Check FK parameter: ACT_CATEGORY_USED_BY_ID #3
1036 IF p_act_category_rec.ACT_CATEGORY_USED_BY_ID <> FND_API.g_miss_num
1037 THEN
1038 l_table_name := 'AMS_ACT_CATEGORIES';
1039 l_pk_name := 'ACT_CATEGORY_USED_BY_ID';
1040 l_pk_value := p_act_category_rec.ACT_CATEGORY_USED_BY_ID;
1041
1042 IF AMS_Utility_PVT.Check_FK_Exists (
1043 p_table_name => l_table_name
1044 ,p_pk_name => l_pk_name
1045 ,p_pk_value => l_pk_value
1046 ) = FND_API.G_FALSE
1047 THEN
1048 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1049 THEN
1050 FND_MESSAGE.set_name('AMS', 'AMS_ACT_CAT_INVALID_EVEO_REF');
1051 FND_MSG_PUB.add;
1052 END IF;
1053 x_return_status := FND_API.G_RET_STS_ERROR;
1054 -- If any errors happen abort API/Procedure.
1055 RETURN;
1056
1057 END IF; -- check_fk_exists
1058 END IF;
1059 */
1060 IF p_act_category_rec.ACT_CATEGORY_USED_BY_ID <> FND_API.g_miss_num
1061 THEN
1062 if(p_act_category_rec.ARC_ACT_CATEGORY_USED_BY = 'EVEH') THEN
1063 l_table_name := 'AMS_EVENT_HEADERS_ALL_B';
1064 l_pk_name := 'EVENT_HEADER_ID';
1065 l_pk_value := p_act_category_rec.ACT_CATEGORY_USED_BY_ID;
1066 elsif(p_act_category_rec.ARC_ACT_CATEGORY_USED_BY = 'EVEO' OR p_act_category_rec.ARC_ACT_CATEGORY_USED_BY = 'EONE') THEN
1067 l_table_name := 'AMS_EVENT_OFFERS_ALL_B';
1068 l_pk_name := 'EVENT_OFFER_ID';
1069 l_pk_value := p_act_category_rec.ACT_CATEGORY_USED_BY_ID;
1070 else
1071 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1072 THEN
1073 FND_MESSAGE.set_name('AMS', 'AMS_ACT_CAT_BAD_USEDBY');
1074 FND_MSG_PUB.add;
1075 END IF;
1076 x_return_status := FND_API.G_RET_STS_ERROR;
1077 -- If any errors happen abort API/Procedure.
1078 RETURN;
1079 end if;
1080 IF AMS_Utility_PVT.Check_FK_Exists (
1081 p_table_name => l_table_name
1082 ,p_pk_name => l_pk_name
1083 ,p_pk_value => l_pk_value
1084 ) = FND_API.G_FALSE
1085 THEN
1086 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1087 THEN
1088 FND_MESSAGE.set_name('AMS', 'AMS_ACT_CAT_INVALID_EVEH_REF');
1089 FND_MSG_PUB.add;
1090 END IF;
1091 x_return_status := FND_API.G_RET_STS_ERROR;
1092 -- If any errors happen abort API/Procedure.
1093 RETURN;
1094
1095 END IF; -- check_fk_exists
1096 END IF;
1097 -- Check FK parameter: CATEGORY_ID
1098 IF p_act_category_rec.CATEGORY_ID <> FND_API.g_miss_num
1099 THEN
1100 l_table_name := 'AMS_CATEGORIES_B';
1101 l_pk_name := 'CATEGORY_ID';
1102 l_pk_value := p_act_category_rec.CATEGORY_ID;
1103
1104 IF AMS_Utility_PVT.Check_FK_Exists (
1105 p_table_name => l_table_name
1106 ,p_pk_name => l_pk_name
1107 ,p_pk_value => l_pk_value
1108 ) = FND_API.G_FALSE
1109 THEN
1110 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1111 THEN
1112 FND_MESSAGE.set_name('AMS', 'AMS_ACT_CAT_INVALID_CAT_REF');
1113 FND_MSG_PUB.add;
1114 END IF;
1115 x_return_status := FND_API.G_RET_STS_ERROR;
1116 -- If any errors happen abort API/Procedure.
1117 RETURN;
1118
1119 END IF; -- check_fk_exists
1120 END IF;
1121
1122 -- check for lookups....
1123
1124 IF p_act_category_rec.arc_act_category_used_by <> FND_API.g_miss_char THEN
1125 IF AMS_Utility_PVT.check_lookup_exists(
1126 p_lookup_type => 'AMS_SYS_ARC_QUALIFIER',
1127 p_lookup_code => p_act_category_rec.arc_act_category_used_by
1128 ) = FND_API.g_false
1129 THEN
1130 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1131 THEN
1132 FND_MESSAGE.set_name('AMS', 'AMS_ACT_CAT_BAD_USEDBY');
1133 FND_MSG_PUB.add;
1134 END IF;
1135
1136 x_return_status := FND_API.g_ret_sts_error;
1137 RETURN;
1138 END IF;
1139 END IF;
1140
1141 -- check for flags...no flags.
1142 -- some logic
1143 open c_act_ctg_id_exists(TO_NUMBER(p_act_category_rec.CATEGORY_ID),
1144 TO_NUMBER(p_act_category_rec.OBJECT_VERSION_NUMBER),
1145 p_act_category_rec.ARC_ACT_CATEGORY_USED_BY,
1146 TO_NUMBER(p_act_category_rec.ACT_CATEGORY_USED_BY_ID));
1147 fetch c_act_ctg_id_exists into l_dummy;
1148 close c_act_ctg_id_exists;
1149 IF l_dummy = 1 THEN
1150 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1151 FND_MESSAGE.set_name ('AMS', 'AMS_CTG_ACT_DUP');
1152 FND_MSG_PUB.add;
1153 END IF;
1154 x_return_status := FND_API.g_ret_sts_error;
1155 RETURN;
1156 END IF;
1157 END Validate_Act_Cty_Items;
1158
1159 /*****************************************************************************************/
1160 -- Start of Comments
1161 --
1162 -- NAME
1163 -- Validate_Act_Cty_Record
1164 --
1165 -- PURPOSE
1166 -- This procedure is to validate category record
1167 --
1168 -- NOTES
1169 -- End of Comments
1170
1171 PROCEDURE Validate_Act_Cty_Record(
1172 p_act_category_rec IN act_category_rec_type,
1176 l_api_version CONSTANT NUMBER := 1.0;
1173 x_return_status OUT NOCOPY VARCHAR2
1174 ) IS
1175 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Act_Cty_Record';
1177
1178 -- Status Local Variables
1179 l_return_status VARCHAR2(1); -- Return value from procedures
1180 BEGIN
1181
1182 -- Standard call to check for call compatibility.
1183 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1184 l_api_version,
1185 l_api_name,
1186 G_PACKAGE_NAME)
1187 THEN
1188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1189 END IF;
1190
1191
1192 -- Initialize API return status to success
1193 x_return_status := FND_API.G_RET_STS_SUCCESS;
1194
1195 --
1196 -- API body
1197
1198 NULL;
1199
1200 --
1201 -- END of API body.
1202 --
1203 END Validate_Act_Cty_Record;
1204
1205 PROCEDURE complete_act_category_rec(
1206 p_act_category_rec IN act_category_rec_type,
1207 x_act_category_rec OUT NOCOPY act_category_rec_type
1208 ) IS
1209
1210 CURSOR c_cat IS
1211 SELECT *
1212 FROM ams_act_categories
1213 WHERE activity_category_id = p_act_category_rec.activity_category_id;
1214
1215 l_act_category_rec c_cat%ROWTYPE;
1216
1217 BEGIN
1218
1219 x_act_category_rec := p_act_category_rec;
1220
1221 OPEN c_cat;
1222 FETCH c_cat INTO l_act_category_rec;
1223 IF c_cat%NOTFOUND THEN
1224 CLOSE c_cat;
1225 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1226 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1227 FND_MSG_PUB.add;
1228 END IF;
1229 RAISE FND_API.g_exc_error;
1230 END IF;
1231 CLOSE c_cat;
1232
1233 IF p_act_category_rec.ACT_CATEGORY_USED_BY_ID = FND_API.g_miss_num THEN
1234 x_act_category_rec.ACT_CATEGORY_USED_BY_ID := l_act_category_rec.ACT_CATEGORY_USED_BY_ID;
1235 END IF;
1236 IF p_act_category_rec.ARC_ACT_CATEGORY_USED_BY = FND_API.g_miss_char THEN
1237 x_act_category_rec.ARC_ACT_CATEGORY_USED_BY := l_act_category_rec.ARC_ACT_CATEGORY_USED_BY;
1238 END IF;
1239 IF p_act_category_rec.CATEGORY_ID = FND_API.g_miss_num THEN
1240 x_act_category_rec.CATEGORY_ID := l_act_category_rec.CATEGORY_ID;
1241 END IF;
1242 IF p_act_category_rec.ATTRIBUTE_CATEGORY = FND_API.g_miss_char THEN
1243 x_act_category_rec.ATTRIBUTE_CATEGORY := l_act_category_rec.ATTRIBUTE_CATEGORY;
1244 END IF;
1245 IF p_act_category_rec.ATTRIBUTE1 = FND_API.g_miss_char THEN
1246 x_act_category_rec.ATTRIBUTE1 := l_act_category_rec.ATTRIBUTE1;
1247 END IF;
1248 IF p_act_category_rec.ATTRIBUTE2 = FND_API.g_miss_char THEN
1249 x_act_category_rec.ATTRIBUTE2 := l_act_category_rec.ATTRIBUTE2;
1250 END IF;
1251 IF p_act_category_rec.ATTRIBUTE3 = FND_API.g_miss_char THEN
1252 x_act_category_rec.ATTRIBUTE3 := l_act_category_rec.ATTRIBUTE3;
1253 END IF;
1254 IF p_act_category_rec.ATTRIBUTE4 = FND_API.g_miss_char THEN
1255 x_act_category_rec.ATTRIBUTE4 := l_act_category_rec.ATTRIBUTE4;
1256 END IF;
1257 IF p_act_category_rec.ATTRIBUTE5 = FND_API.g_miss_char THEN
1258 x_act_category_rec.ATTRIBUTE5 := l_act_category_rec.ATTRIBUTE5;
1259 END IF;
1260 IF p_act_category_rec.ATTRIBUTE6 = FND_API.g_miss_char THEN
1261 x_act_category_rec.ATTRIBUTE6 := l_act_category_rec.ATTRIBUTE6;
1262 END IF;
1263 IF p_act_category_rec.ATTRIBUTE7 = FND_API.g_miss_char THEN
1264 x_act_category_rec.ATTRIBUTE7 := l_act_category_rec.ATTRIBUTE7;
1265 END IF;
1266 IF p_act_category_rec.ATTRIBUTE8 = FND_API.g_miss_char THEN
1267 x_act_category_rec.ATTRIBUTE8 := l_act_category_rec.ATTRIBUTE8;
1268 END IF;
1269 IF p_act_category_rec.ATTRIBUTE9 = FND_API.g_miss_char THEN
1270 x_act_category_rec.ATTRIBUTE9 := l_act_category_rec.ATTRIBUTE9;
1271 END IF;
1272 IF p_act_category_rec.ATTRIBUTE10 = FND_API.g_miss_char THEN
1273 x_act_category_rec.ATTRIBUTE10 := l_act_category_rec.ATTRIBUTE10;
1274 END IF;
1275
1276 IF p_act_category_rec.ATTRIBUTE11 = FND_API.g_miss_char THEN
1277 x_act_category_rec.ATTRIBUTE11 := l_act_category_rec.ATTRIBUTE11;
1278 END IF;
1279 IF p_act_category_rec.ATTRIBUTE11 = FND_API.g_miss_char THEN
1280 x_act_category_rec.ATTRIBUTE11 := l_act_category_rec.ATTRIBUTE11;
1281 END IF;
1282 IF p_act_category_rec.ATTRIBUTE12 = FND_API.g_miss_char THEN
1283 x_act_category_rec.ATTRIBUTE12 := l_act_category_rec.ATTRIBUTE12;
1284 END IF;
1285 IF p_act_category_rec.ATTRIBUTE13 = FND_API.g_miss_char THEN
1286 x_act_category_rec.ATTRIBUTE13 := l_act_category_rec.ATTRIBUTE13;
1287 END IF;
1288 IF p_act_category_rec.ATTRIBUTE14 = FND_API.g_miss_char THEN
1289 x_act_category_rec.ATTRIBUTE14 := l_act_category_rec.ATTRIBUTE14;
1290 END IF;
1291 IF p_act_category_rec.ATTRIBUTE15 = FND_API.g_miss_char THEN
1292 x_act_category_rec.ATTRIBUTE15 := l_act_category_rec.ATTRIBUTE15;
1293 END IF;
1294
1295 END complete_act_category_rec;
1296
1297 /*********************** server side TEST CASE ***************************/
1298
1299 -- Start of Comments
1300 --
1301 -- NAME
1302 -- Unit_Test_Insert
1303 -- Unit_Test_Delete
1304 -- Unit_Test_Update
1308 -- These procedures are to test each procedure that satisfy caller needs
1305 -- Unit_Test_Lock
1306 --
1307 -- PURPOSE
1309 --
1310 -- NOTES
1311 --
1312 --
1313 -- HISTORY
1314 -- 11/13/1999 sugupta created
1315 -- End of Comments
1316
1317 --********************************************************
1318 /* 0614
1319 PROCEDURE Unit_Test_Insert
1320 IS
1321
1322 -- local variables
1323 l_act_category_rec AMS_CATEGORIES_VL%ROWTYPE;
1324 l_return_status VARCHAR2(1);
1325 l_msg_count NUMBER;
1326 l_msg_data VARCHAR2(200);
1327 l_category_id AMS_CATEGORIES_VL.CATEGORY_ID%TYPE;
1328
1329 l_category_req_item_rec category_validate_rec_type;
1330 l_Category_validate_item_rec category_validate_rec_type;
1331 l_Category_default_item_rec category_validate_rec_type;
1332 l_Category_validate_row_rec category_validate_rec_type;
1333
1334 BEGIN
1335
1336 -- turned on debug mode
1337 IF AMS_ActCategory_PVT.g_debug = TRUE THEN
1338
1339 l_category_rec.CATEGORY_ID := 1234;
1340 l_category_rec.ARC_CATEGORY_CREATED_FOR := 'hung';
1341 l_category_rec.CATEGORY_NAME := 'sugupta_category';
1342
1343
1344 AMS_ActCategory_PVT.Create_Category (
1345 p_api_version => 1.0 -- p_api_version
1346 ,p_init_msg_list => FND_API.G_FALSE
1347 ,p_commit => FND_API.G_FALSE
1348 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1349 ,x_return_status => l_return_status
1350 ,x_msg_count => l_msg_count
1351 ,x_msg_data => l_msg_data
1352
1353 ,p_PK => FND_API.G_TRUE
1354 ,p_default => FND_API.G_TRUE
1355 ,p_Category_req_item_rec => l_Category_req_item_rec
1356 ,p_Category_validate_item_rec => l_Category_validate_item_rec
1357 ,p_Category_default_item_rec => l_Category_default_item_rec
1358 ,p_Category_validate_row_rec => l_Category_validate_row_rec
1359 ,p_category_rec => l_category_rec
1360 ,x_category_id => l_category_id
1361 );
1362
1363 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1364 ELSE
1365 commit work;
1366 END IF;
1367
1368 NULL;
1369
1370 ELSE
1371 END IF;
1372
1373
1374 END Unit_Test_Insert;
1375
1376 --********************************************************
1377
1378 PROCEDURE Unit_Test_Delete
1379 IS
1380
1381 -- local variables
1382 l_category_rec AMS_CATEGORIES_VL%ROWTYPE;
1383 l_return_status VARCHAR2(1);
1384 l_msg_count NUMBER;
1385 l_msg_data VARCHAR2(200);
1386 l_category_id AMS_CATEGORIES_VL.CATEGORY_ID%TYPE;
1387
1388 l_Category_req_item_rec category_validate_rec_type;
1389 l_Category_validate_item_rec category_validate_rec_type;
1390 l_Category_default_item_rec category_validate_rec_type;
1391 l_Category_validate_row_rec category_validate_rec_type;
1392
1393 BEGIN
1394
1395 -- turned on debug mode
1396 IF AMS_ActCategory_PVT.g_debug = TRUE
1397 THEN
1398
1399 l_category_rec.category_id := 1234;
1400
1401
1402 AMS_ActCategory_PVT.Delete_Category (
1403 p_api_version => 1.0 -- p_api_version
1404 ,p_init_msg_list => FND_API.G_FALSE
1405 ,p_commit => FND_API.G_FALSE
1406 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1407 ,p_category_rec => l_category_rec
1408
1409 ,x_return_status => l_return_status
1410 ,x_msg_count => l_msg_count
1411 ,x_msg_data => l_msg_data
1412 );
1413
1414 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1415 ELSE
1416 commit work;
1417 END IF;
1418
1419 NULL;
1420
1421 ELSE
1422 END IF;
1423
1424
1425 END Unit_Test_Delete;
1426
1427
1428 --********************************************************
1429
1430 PROCEDURE Unit_Test_Update
1431 IS
1432
1433 -- local variables
1434 l_category_rec AMS_CATEGORIES_VL%ROWTYPE;
1435 l_return_status VARCHAR2(1);
1436 l_msg_count NUMBER;
1437 l_msg_data VARCHAR2(200);
1438 l_category_id AMS_CATEGORIES_VL.CATEGORY_ID%TYPE;
1439
1440 l_Category_req_item_rec category_validate_rec_type;
1441 l_Category_validate_item_rec category_validate_rec_type;
1442 l_Category_default_item_rec category_validate_rec_type;
1443 l_Category_validate_row_rec category_validate_rec_type;
1444
1445 cursor C(my_category_id NUMBER) is
1446 select *
1447 from AMS_CATEGORIES_VL
1448 WHERE CATEGORY_ID = my_category_id;
1449 BEGIN
1450
1451 -- turned on debug mode
1452 IF AMS_ActCategory_PVT.g_debug = TRUE
1453 THEN
1454
1455 l_category_id := 1234;
1456 OPEN C(l_category_id);
1457 FETCH C INTO l_category_rec;
1458
1459 l_category_rec.NOTES := 'NOTES UPDATED1';
1460
1461 AMS_ActCategory_PVT.Update_Category (
1462 p_api_version => 1.0 -- p_api_version
1463 ,p_init_msg_list => FND_API.G_FALSE
1464 ,p_commit => FND_API.G_FALSE
1465 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1469 ,x_msg_count => l_msg_count
1466 ,p_category_rec => l_category_rec
1467
1468 ,x_return_status => l_return_status
1470 ,x_msg_data => l_msg_data
1471 );
1472
1473 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1474 THEN
1475 ELSE
1476 commit work;
1477 END IF;
1478
1479 NULL;
1480
1481 ELSE
1482 END IF;
1483
1484
1485 END Unit_Test_Update;
1486
1487
1488 --********************************************************
1489
1490
1491 PROCEDURE Unit_Test_Lock
1492 IS
1493
1494 -- local variables
1495 l_category_rec AMS_CATEGORIES_VL%ROWTYPE;
1496 l_return_status VARCHAR2(1);
1497 l_msg_count NUMBER;
1498 l_msg_data VARCHAR2(200);
1499 l_category_id AMS_CATEGORIES_VL.CATEGORY_ID%TYPE;
1500
1501 l_Category_req_item_rec category_validate_rec_type;
1502 l_Category_validate_item_rec category_validate_rec_type;
1503 l_Category_default_item_rec category_validate_rec_type;
1504 l_Category_validate_row_rec category_validate_rec_type;
1505
1506
1507 cursor C(my_category_id NUMBER) is
1508 select * from AMS_CATEGORIES_B WHERE CATEGORY_ID = my_category_id;
1509 BEGIN
1510
1511 -- turned on debug mode
1512 IF AMS_ActCategory_PVT.g_debug = TRUE
1513 THEN
1514
1515 l_category_rec.category_id := 1234;
1516 l_category_rec.NOTES := 'server side test';
1517
1518 AMS_ActCategory_PVT.Lock_Category (
1519 p_api_version => 1.0 -- p_api_version
1520 ,p_init_msg_list => FND_API.G_FALSE
1521 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1522 ,p_category_rec => l_category_rec
1523
1524 ,x_return_status => l_return_status
1525 ,x_msg_count => l_msg_count
1526 ,x_msg_data => l_msg_data
1527 );
1528
1529 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1530 --RAISE FND_API.G_EXC_ERROR;
1531 END IF;
1532
1533 NULL;
1534
1535 ELSE
1536 END IF;
1537
1538
1539 END Unit_Test_Lock;
1540
1541 *********************** server side TEST CASE *****************************************
1542
1543 -- Start of Comments
1544 --
1545 -- NAME
1546 -- Unit_Test_Act_Insert
1547 -- Unit_Test_Act_Delete
1548 -- Unit_Test_Act_Update
1549 -- Unit_Test_Act_Lock
1550 --
1551 -- PURPOSE
1552 -- This procedure is to test each procedure that satisfy caller needs
1553 --
1554 -- NOTES
1555 --
1556 --
1557 -- HISTORY
1558 -- 11/8/1999 sugupta created
1559 -- End of Comments
1560
1561 PROCEDURE Unit_Test_Act_Insert
1562 is
1563
1564 -- local variables
1565 l_act_category_rec act_category_rec_type;
1566 l_return_status VARCHAR2(1);
1567 l_msg_count NUMBER;
1568 l_msg_data VARCHAR2(200);
1569 l_act_category_id AMS_ACT_CATEGORIES.ACTIVITY_CATEGORY_ID%TYPE;
1570
1571 l_act_cty_validate_item_rec Act_category_validate_rec_type;
1572 l_act_cty_default_item_rec Act_category_validate_rec_type;
1573 l_act_cty_validate_row_rec Act_category_validate_rec_type;
1574
1575 BEGIN
1576
1577 -- turned on debug mode
1578 IF AMS_ActCategory_PVT.G_DEBUG = TRUE THEN
1579
1580 -- Insert case 1
1581
1582 l_act_category_rec.ACTIVITY_CATEGORY_ID := 1234;
1583 l_act_category_rec.ACT_CATEGORY_USED_BY_ID := 1000;
1584 l_act_category_rec.ARC_ACT_CATEGORY_USED_BY := 1000;
1585 l_act_category_rec.CATEGORY_ID := 1234;
1586
1587
1588 AMS_ActCategory_PVT.Create_Act_Category (
1589 p_api_version => 1.0 -- p_api_version
1590 ,p_init_msg_list => FND_API.G_FALSE
1591 ,p_commit => FND_API.G_FALSE
1592 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1593 ,x_return_status => l_return_status
1594 ,x_msg_count => l_msg_count
1595 ,x_msg_data => l_msg_data
1596
1597 ,p_PK => FND_API.G_TRUE
1598 ,p_default => FND_API.G_TRUE
1599 ,p_Category_req_item_rec => l_act_category_req_item_rec
1600 ,p_Category_validate_item_rec => l_act_cty_validate_item_rec
1601 ,p_Category_default_item_rec => l_act_cty_default_item_rec
1602 ,p_Category_validate_row_rec => l_act_cty_validate_row_rec
1603 ,p_category_rec => l_act_category_rec
1604 ,x_act_category_id => l_act_category_id
1605 );
1606
1607 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1608 THEN
1609 ELSE
1610 commit work;
1611 END IF;
1612
1613 null;
1614
1615 ELSE
1616 END IF;
1617
1618 END Unit_Test_Act_Insert;
1619
1620
1621 PROCEDURE Unit_Test_Act_Delete
1622 is
1623
1624 -- local variables
1625 l_act_category_rec act_category_rec_type;
1626 l_return_status VARCHAR2(1);
1627 l_msg_count NUMBER;
1628 l_msg_data VARCHAR2(200);
1629 l_act_category_id AMS_ACT_CATEGORIES.ACTIVITY_CATEGORY_ID%TYPE;
1630
1631 l_act_category_req_item_rec act_category_validate_rec_type;
1632 l_act_cty_validate_item_rec act_category_validate_rec_type;
1633 l_act_cty_default_item_rec act_category_validate_rec_type;
1637
1634 l_act_cty_validate_row_rec act_category_validate_rec_type;
1635
1636 BEGIN
1638 -- turned on debug mode
1639 IF AMS_ActCategory_PVT.G_DEBUG = TRUE THEN
1640
1641
1642 -- Delete test case 1
1643 l_act_category_rec.activity_category_id := 1234;
1644 AMS_ActCategory_PVT.Delete_Act_Category (
1645 p_api_version => 1.0 -- p_api_version
1646 ,p_init_msg_list => FND_API.G_FALSE
1647 ,p_commit => FND_API.G_FALSE
1648 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1649 ,p_category_rec => l_act_category_rec
1650
1651 ,x_return_status => l_return_status
1652 ,x_msg_count => l_msg_count
1653 ,x_msg_data => l_msg_data
1654 );
1655
1656 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1657 ELSE
1658 commit work;
1659 end if;
1660
1661 null;
1662
1663 ELSE
1664 END IF;
1665
1666 END Unit_Test_Act_Delete;
1667
1668
1669
1670 PROCEDURE Unit_Test_Act_Update
1671 is
1672
1673 -- local variables
1674 l_act_category_rec act_category_rec_type;
1675 l_return_status VARCHAR2(1);
1676 l_msg_count NUMBER;
1677 l_msg_data VARCHAR2(200);
1678 l_act_category_id AMS_ACT_CATEGORIES.ACTIVITY_CATEGORY_ID%TYPE;
1679
1680 l_act_category_req_item_rec act_category_validate_rec_type;
1681 l_act_cty_validate_item_rec act_category_validate_rec_type;
1682 l_act_cty_default_item_rec act_category_validate_rec_type;
1683 l_act_cty_validate_row_rec act_category_validate_rec_type;
1684
1685 CURSOR C(my_act_category_id NUMBER) is
1686 SELECT *
1687 FROM AMS_ACT_CATEGORIES
1688 WHERE ACTIVITY_CATEGORY_ID = my_act_category_id;
1689
1690 BEGIN
1691
1692 -- turned on debug mode
1693 IF AMS_ActCategory_PVT.G_DEBUG = TRUE THEN
1694
1695
1696 -- Update test case 1
1697
1698 l_act_category_id := 1234;
1699 OPEN C(l_act_category_id);
1700 FETCH C INTO l_act_category_rec;
1701
1702 l_act_category_rec.ATTRIBUTE1 := 'ATTRIBUTE1 UPDATED1';
1703
1704 AMS_ActCategory_PVT.Update_Act_Category (
1705 p_api_version => 1.0 -- p_api_version
1706 ,p_init_msg_list => FND_API.G_FALSE
1707 ,p_commit => FND_API.G_FALSE
1708 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1709 ,p_category_rec => l_act_category_rec
1710
1711 ,x_return_status => l_return_status
1712 ,x_msg_count => l_msg_count
1713 ,x_msg_data => l_msg_data
1714 );
1715
1716 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1717 THEN
1718 ELSE
1719 commit work;
1720 END IF;
1721 CLOSE C;
1722
1723 null;
1724
1725 ELSE
1726 END IF;
1727
1728 END Unit_Test_Act_Update;
1729
1730
1731 PROCEDURE Unit_Test_Act_Lock
1732 is
1733
1734 -- local variables
1735 l_act_category_rec act_category_rec_type;
1736 l_return_status VARCHAR2(1);
1737 l_msg_count NUMBER;
1738 l_msg_data VARCHAR2(200);
1739 l_act_category_id AMS_ACT_CATEGORIES.ACTIVITY_CATEGORY_ID%TYPE;
1740
1741 l_act_category_req_item_rec act_category_validate_rec_type;
1742 l_act_cty_validate_item_rec act_category_validate_rec_type;
1743 l_act_cty_default_item_rec act_category_validate_rec_type;
1744 l_act_cty_validate_row_rec act_category_validate_rec_type;
1745
1746 BEGIN
1747
1748 -- turned on debug mode
1749 IF AMS_ActCategory_PVT.G_DEBUG = TRUE THEN
1750
1751
1752 -- Lock test case 1
1753
1754 l_act_category_rec.activity_category_id := 1234;
1755
1756 AMS_ActCategory_PVT.Lock_Act_Category (
1757 p_api_version => 1.0 -- p_api_version
1758 ,p_init_msg_list => FND_API.G_FALSE
1759 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1760 ,p_category_rec => l_act_category_rec
1761
1762 ,x_return_status => l_return_status
1763 ,x_msg_count => l_msg_count
1764 ,x_msg_data => l_msg_data
1765 );
1766
1767 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1768 THEN
1769 --RAISE FND_API.G_EXC_ERROR;
1770 END IF;
1771
1772
1773 null;
1774
1775 ELSE
1776 END IF;
1777
1778 END Unit_Test_Act_Lock;
1779 */--0614
1780 END AMS_ActCategory_PVT;