[Home] [Help]
PACKAGE BODY: APPS.AMS_CATEGORY_PVT
Source
1 PACKAGE BODY AMS_Category_PVT as
2 /* $Header: amsvctyb.pls 120.2.12020000.2 2013/02/15 07:12:28 rsatyava ship $ */
3 --
4 -- NAME
5 -- AMS_Category_PVT
6 --
7 -- HISTORY
8 -- 01/04/2000 sugupta CREATED
9 -- 06/01/2000 khung add two new columns accrued_liability_account
10 -- and ded_adjustment_account
11 -- 07/10/2000 khung add columns in complete_category_rec for bug
12 -- 1349969 fix
13 -- 06/19/2001 musman checking the foreign exists for category_id in
14 -- ams_deliverables_all_b table for bug 1794454 fix.
15 -- 07/11/2001 musman changed the message name from AMS_CAT_CANNOT_MODIFY_SEED
16 -- to AMS_CAT_CANNOT_MOD_SEED for bug 1877146 fix.
20 -- Ams_deliv_offerings_b,since was not existing anymore.Bug fix for #1966294
17 -- 07/26/2001 musman In the validate_cty_records the return_status was set to
18 -- Expected error though its not an error.Bug fix for #1880798
19 -- 08/30/2001 musman In the Validate_Cty_Child_Enty the commented out the foreign key check to
21 -- 11/02/2001 musman Implementing that the categories can be disabled only if the
22 -- child categories are disabled.
23 -- 12/20/2004 vmodur Bug 3847393 in 11.5.11
24 -- 09/30/2005 vmodur Added Ledger_Id
25 -- 11/23/2005 vmodur Missed Bug 4064984 in R12 shipped using Bug 4755142
26
27 --
28 G_PACKAGE_NAME CONSTANT VARCHAR2(30):='AMS_Category_PVT';
29 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvctyb.pls';
30
31 -- Debug mode
32 --g_debug boolean := FALSE;
33 g_debug boolean := TRUE;
34
35 --
36 -- Procedure and function declarations.
37
38
39 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
40 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
41 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
42
43 PROCEDURE Validate_Category_Cross_Record
44 ( p_category_rec IN category_rec_type,
45 x_return_status OUT NOCOPY VARCHAR2
46 );
47
48 PROCEDURE Validate_Category_Cross_Entity
49 ( p_category_rec IN category_rec_type,
50 x_return_status OUT NOCOPY VARCHAR2
51 );
52
53 -- Start of Comments
54 --
55 -- NAME
56 -- Create_Category
57 --
58 -- PURPOSE
59 -- This procedure is to create a category record that satisfy caller needs
60 --
61 -- NOTES
62 --
63 --
64 -- HISTORY
65 -- 01/04/2000 sugupta created
66 -- End of Comments
67
68 PROCEDURE Create_Category
69 ( p_api_version IN NUMBER,
70 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
71 p_commit IN VARCHAR2 := FND_API.g_false,
72 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_msg_count OUT NOCOPY NUMBER,
75 x_msg_data OUT NOCOPY VARCHAR2,
76
77 p_category_rec IN category_rec_type,
78 x_category_id OUT NOCOPY NUMBER
79 ) IS
80
81 l_api_name CONSTANT VARCHAR2(30) := 'Create_Category';
82 l_api_version CONSTANT NUMBER := 1.0;
83 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME ||'.'|| l_api_name;
84
85 -- Status Local Variables
86 l_return_status VARCHAR2(1); -- Return value from procedures
87 l_category_rec category_rec_type := p_category_rec;
88 l_count NUMBER;
89
90 CURSOR C_category_seq IS
91 SELECT ams_categories_b_s.NEXTVAL
92 FROM dual;
93
94 CURSOR C_category_count(my_category_id VARCHAR2) IS
95 SELECT COUNT(*)
96 FROM AMS_CATEGORIES_B
97 WHERE category_id = my_category_id;
98
99 CURSOR c_findParentofParent ( l_parent_parent_id IN NUMBER ) IS
100 SELECT parent_category_id
101 FROM AMS_CATEGORIES_B
102 WHERE category_id = l_parent_parent_id;
103
104 l_parent_id NUMBER;
105
106 BEGIN
107
108 -- Standard Start of API savepoint
109 SAVEPOINT Create_Category_PVT;
113 END IF;
110 IF (AMS_DEBUG_HIGH_ON) THEN
111
112 AMS_Utility_PVT.debug_message(l_full_name||': start');
114
115 -- Standard call to check for call compatibility.
116 IF NOT FND_API.Compatible_API_Call ( l_api_version,
117 p_api_version,
118 l_api_name,
119 G_PACKAGE_NAME)
120 THEN
121 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
122 END IF;
123
124 -- Initialize message list IF p_init_msg_list is set to TRUE.
125 IF FND_API.to_Boolean( p_init_msg_list )
126 THEN
127 FND_MSG_PUB.initialize;
128 END IF;
129
130
131 -- Initialize API return status to success
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 ----------------------- validate -----------------------
135 IF (AMS_DEBUG_HIGH_ON) THEN
136
137 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
138 END IF;
139
140 Validate_Category
141 ( p_api_version => 1.0
142 ,p_init_msg_list => p_init_msg_list
143 ,p_validation_level => p_validation_level
144 ,x_return_status => l_return_status
145 ,x_msg_count => x_msg_count
146 ,x_msg_data => x_msg_data
147
148 ,p_category_rec => l_category_rec
149 );
150
151 -- If any errors happen abort API.
152 IF l_return_status = FND_API.G_RET_STS_ERROR
153 THEN
154 RAISE FND_API.G_EXC_ERROR;
155
156 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
157 THEN
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159 END IF;
160 -------------------------- insert --------------------------
161 IF (AMS_DEBUG_HIGH_ON) THEN
162
163 AMS_Utility_PVT.debug_message(l_full_name ||': insert');
164 END IF;
165
166 -- Set the value for the PK.
167
168 IF l_category_rec.category_id IS NULL THEN
169 LOOP
170 OPEN C_category_seq;
171 FETCH C_category_seq INTO l_category_rec.category_id;
172 CLOSE C_category_seq;
173
174 OPEN C_category_count(l_category_rec.category_id);
175 FETCH C_category_count INTO l_count;
176 CLOSE C_category_count;
177
178 EXIT WHEN l_count = 0;
179 END LOOP;
180 END IF;
181
182 ----------- For DELV and METR , we can only have one level of hierarchy --------
183 ----------- code added by abhola START ----------------------------------------
184 if (L_CATEGORY_REC.PARENT_CATEGORY_ID IS NOT NULL ) AND
185 (L_CATEGORY_REC.ARC_CATEGORY_CREATED_FOR in ('DELV','METR'))
186 then
187
188 OPEN c_findParentofParent(L_CATEGORY_REC.PARENT_CATEGORY_ID);
189 FETCH c_findParentofParent INTO l_parent_id;
190 CLOSE c_findParentofParent;
191
192 if ( l_parent_id IS NOT NULL ) then
193 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
194 FND_MESSAGE.set_name('AMS', 'AMS_INVALID_CAT_LEVEL');
195 FND_MSG_PUB.add;
196 END IF;
197 RAISE FND_API.g_exc_error;
198 end if;
199
200 end if;
201
202 ------------- code added by abhola END ------------------------------------------
203 --
204 -- Insert into the base table.
205 --
206 INSERT INTO AMS_CATEGORIES_B (
207 CATEGORY_ID,
208 CREATION_DATE,
209 CREATED_BY,
210 LAST_UPDATE_DATE,
211 LAST_UPDATED_BY,
212 LAST_UPDATE_LOGIN,
213 OBJECT_VERSION_NUMBER,
214 ARC_CATEGORY_CREATED_FOR,
215 ENABLED_FLAG,
216 PARENT_CATEGORY_ID
217 ,ATTRIBUTE_CATEGORY
218 ,ATTRIBUTE1
219 ,ATTRIBUTE2
220 ,ATTRIBUTE3
221 ,ATTRIBUTE4
222 ,ATTRIBUTE5
223 ,ATTRIBUTE6
224 ,ATTRIBUTE7
225 ,ATTRIBUTE8
226 ,ATTRIBUTE9
227 ,ATTRIBUTE10
228 ,ATTRIBUTE11
229 ,ATTRIBUTE12
230 ,ATTRIBUTE13
231 ,ATTRIBUTE14
232 ,ATTRIBUTE15
233 ,ACCRUED_LIABILITY_ACCOUNT
234 ,DED_ADJUSTMENT_ACCOUNT
235 ,BUDGET_CODE_SUFFIX
236 ,LEDGER_ID
237 ) VALUES (
238 L_CATEGORY_REC.CATEGORY_ID,
239 SYSDATE,
240 FND_GLOBAL.user_id,
241 SYSDATE,
242 FND_GLOBAL.user_id,
243 FND_GLOBAL.conc_login_id,
244 1, -- object_version_number
245 L_CATEGORY_REC.ARC_CATEGORY_CREATED_FOR,
246 nvl(L_CATEGORY_REC.ENABLED_FLAG,'Y'),
247 L_CATEGORY_REC.PARENT_CATEGORY_ID,
248 L_CATEGORY_REC.ATTRIBUTE_CATEGORY,
249 L_CATEGORY_REC.ATTRIBUTE1,
250 L_CATEGORY_REC.ATTRIBUTE2,
251 L_CATEGORY_REC.ATTRIBUTE3,
252 L_CATEGORY_REC.ATTRIBUTE4,
253 L_CATEGORY_REC.ATTRIBUTE5,
254 L_CATEGORY_REC.ATTRIBUTE6,
255 L_CATEGORY_REC.ATTRIBUTE7,
256 L_CATEGORY_REC.ATTRIBUTE8,
257 L_CATEGORY_REC.ATTRIBUTE9,
258 L_CATEGORY_REC.ATTRIBUTE10,
259 L_CATEGORY_REC.ATTRIBUTE11,
260 L_CATEGORY_REC.ATTRIBUTE12,
261 L_CATEGORY_REC.ATTRIBUTE13,
262 L_CATEGORY_REC.ATTRIBUTE14,
263 L_CATEGORY_REC.ATTRIBUTE15,
264 L_CATEGORY_REC.ACCRUED_LIABILITY_ACCOUNT,
265 L_CATEGORY_REC.DED_ADJUSTMENT_ACCOUNT,
269
266 L_CATEGORY_REC.BUDGET_CODE_SUFFIX,
267 L_CATEGORY_REC.LEDGER_ID
268 );
270 INSERT INTO AMS_CATEGORIES_TL (
271 CATEGORY_NAME,
272 DESCRIPTION,
273 LAST_UPDATE_DATE,
274 LAST_UPDATED_BY,
275 CREATION_DATE,
276 CREATED_BY,
277 LAST_UPDATE_LOGIN,
278 CATEGORY_ID,
279 LANGUAGE,
280 SOURCE_LANG
281 ) SELECT
282 l_category_rec.CATEGORY_NAME,
283 l_category_rec.DESCRIPTION,
284 sysdate,
285 FND_GLOBAL.User_Id,
286 sysdate,
287 FND_GLOBAL.User_Id,
288 FND_GLOBAL.Conc_Login_Id,
289 l_category_rec.category_id,
290 L.LANGUAGE_CODE,
291 userenv('LANG')
292 FROM FND_LANGUAGES L
293 WHERE L.INSTALLED_FLAG in ('I', 'B')
294 AND NOT EXISTS
295 (SELECT NULL
296 FROM AMS_CATEGORIES_TL T
297 WHERE T.CATEGORY_ID = l_category_rec.category_id
298 AND T.LANGUAGE = L.LANGUAGE_CODE);
299
300 -- set OUT value
301 x_category_id := l_category_rec.category_id;
302
303 -- Standard check of p_commit.
304 IF FND_API.To_Boolean ( p_commit )
305 THEN
306 COMMIT WORK;
307 END IF;
308
309 -- Standard call to get message count AND IF count is 1, get message info.
310 FND_MSG_PUB.Count_AND_Get
311 ( p_count => x_msg_count,
312 p_data => x_msg_data,
313 p_encoded => FND_API.G_FALSE
314 );
315
316 IF (AMS_DEBUG_HIGH_ON) THEN
317
318
319
320 AMS_Utility_PVT.debug_message(l_full_name ||': end');
321
322 END IF;
323
324 EXCEPTION
325
326 WHEN FND_API.G_EXC_ERROR
327 THEN
328 ROLLBACK TO Create_Category_PVT;
329 x_return_status := FND_API.G_RET_STS_ERROR ;
330
331 FND_MSG_PUB.Count_AND_Get
332 ( p_count => x_msg_count,
333 p_data => x_msg_data,
334 p_encoded => FND_API.G_FALSE
335 );
336
337 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
338 THEN
339 ROLLBACK TO Create_Category_PVT;
340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
341
342 FND_MSG_PUB.Count_AND_Get
343 ( p_count => x_msg_count,
344 p_data => x_msg_data,
345 p_encoded => FND_API.G_FALSE
346 );
347
348 WHEN OTHERS THEN
349
350 ROLLBACK TO Create_Category_PVT;
351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
352
353 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
354 THEN
355 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
356 END IF;
357
358 FND_MSG_PUB.Count_AND_Get
359 ( p_count => x_msg_count,
360 p_data => x_msg_data,
361 p_encoded => FND_API.G_FALSE
362 );
363
364 END Create_Category;
365
366
367 -- Start of Comments
368 --
369 -- NAME
370 -- Update_category
371 --
372 -- PURPOSE
373 -- This procedure is to update a category record that satisfy caller needs
374 --
375 -- NOTES
376 --
377 --
378 -- HISTORY
379 -- 01/04/2000 sugupta created
380 -- End of Comments
381
382 PROCEDURE Update_Category
383 ( p_api_version IN NUMBER,
384 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
385 p_commit IN VARCHAR2 := FND_API.g_false,
386 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
387 x_return_status OUT NOCOPY VARCHAR2,
388 x_msg_count OUT NOCOPY NUMBER,
389 x_msg_data OUT NOCOPY VARCHAR2,
390
391 p_category_rec IN category_rec_type
392 ) IS
393 l_api_name CONSTANT VARCHAR2(30) := 'Update_Category';
394 l_api_version CONSTANT NUMBER := 1.0;
395 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME ||'.'|| l_api_name;
396
397 -- Status Local Variables
398 l_return_status VARCHAR2(1); -- Return value from procedures
399 l_category_rec category_rec_type;
400
401
402 CURSOR c_findParentofParent ( l_parent_parent_id IN NUMBER ) IS
403 SELECT parent_category_id
404 FROM AMS_CATEGORIES_B
405 WHERE category_id = l_parent_parent_id;
406
407 l_parent_id NUMBER;
408
409 BEGIN
410
411 -- Standard Start of API savepoint
412 SAVEPOINT Update_Category_PVT;
413 --IF (AMS_DEBUG_HIGH_ON) THENAMS_Utility_PVT.debug_message(l_full_name||': start');END IF;
414
415 -- Standard call to check for call compatibility.
416 IF NOT FND_API.Compatible_API_Call ( l_api_version,
417 p_api_version,
421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
418 l_api_name,
419 G_PACKAGE_NAME)
420 THEN
422 END IF;
423
424 -- Initialize message list IF p_init_msg_list is set to TRUE.
425 IF FND_API.to_Boolean( p_init_msg_list )
426 THEN
427 FND_MSG_PUB.initialize;
428 END IF;
429
430 -- Initialize API return status to success
431 x_return_status := FND_API.G_RET_STS_SUCCESS;
432
433 ----------------------- validate ----------------------
434 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(l_full_name ||': validate'); END IF;
435
436 -- replace g_miss_char/num/date with current column values
437 complete_category_rec(p_category_rec, l_category_rec);
438
439 Validate_Category
440 ( p_api_version => 1.0
441 ,p_init_msg_list => p_init_msg_list
442 ,p_validation_level => p_validation_level
443 ,x_return_status => l_return_status
444 ,x_msg_count => x_msg_count
445 ,x_msg_data => x_msg_data
446 ,p_category_rec => l_category_rec
447 );
448
449 -- If any errors happen abort API.
450 IF l_return_status = FND_API.G_RET_STS_ERROR
451 THEN
452 RAISE FND_API.G_EXC_ERROR;
453 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
454
455 THEN
456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 END IF;
458
459
460 ----------- For DELV and METR , we can only have one level of hierarchy --------
461 ----------- code added by abhola START ----------------------------------------
462 if (L_CATEGORY_REC.PARENT_CATEGORY_ID IS NOT NULL ) AND
463 (L_CATEGORY_REC.ARC_CATEGORY_CREATED_FOR in ('DELV','METR'))
464 then
465
466 OPEN c_findParentofParent(L_CATEGORY_REC.PARENT_CATEGORY_ID);
467 FETCH c_findParentofParent INTO l_parent_id;
468 CLOSE c_findParentofParent;
469
470 if ( l_parent_id IS NOT NULL ) then
471 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
472 FND_MESSAGE.set_name('AMS', 'AMS_INVALID_CAT_LEVEL');
473 FND_MSG_PUB.add;
474 END IF;
475 RAISE FND_API.g_exc_error;
476 end if;
477
478 end if;
479
480 ------------- code added by abhola END ------------------------------------------
481
482
483 -------------------------- update --------------------
484 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(l_full_name ||': update before SEED Data Check'); END IF;
485
486 -- seeded category if category_id < 10000
487 -- and created_by = 0 or 1 -- Bug 4064984
488 -- user cannot modify or delete seeded category
489 -- exception: enabled_flag can be modified
490 IF (l_category_rec.created_by IN (0,1) AND l_category_rec.category_id < 10000) THEN
491 UPDATE AMS_CATEGORIES_B
492 SET
493 LAST_UPDATE_DATE = SYSDATE,
494 LAST_UPDATED_BY = FND_GLOBAL.user_id,
495 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
496 OBJECT_VERSION_NUMBER = L_CATEGORY_REC.OBJECT_VERSION_NUMBER + 1,
497 ENABLED_FLAG = L_CATEGORY_REC.ENABLED_FLAG
498 WHERE
499 CATEGORY_ID = l_category_rec.category_id;
500
501 IF (SQL%NOTFOUND) THEN
502 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
503 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
504 FND_MSG_PUB.add;
505 END IF;
506 RAISE FND_API.g_exc_error;
507 END IF;
508
509 UPDATE AMS_CATEGORIES_TL
510 SET
511 LAST_UPDATE_DATE = sysdate,
512 LAST_UPDATED_BY = FND_GLOBAL.User_Id,
513 LAST_UPDATE_LOGIN = FND_GLOBAL.Conc_Login_Id,
514 SOURCE_LANG = userenv('LANG')
515 WHERE
516 CATEGORY_ID = p_category_rec.category_id
517 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
518
519 IF (SQL%NOTFOUND) THEN
520 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
521 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
522 FND_MSG_PUB.add;
523 END IF;
524 RAISE FND_API.g_exc_error;
525 END IF;
526
527 ELSE -- l_category_rec.l_category_id >= 10000 or non-seeded with seq < 10000
528
529 UPDATE AMS_CATEGORIES_B
530 SET
531 LAST_UPDATE_DATE = SYSDATE,
532 LAST_UPDATED_BY = FND_GLOBAL.user_id,
533 LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
534 OBJECT_VERSION_NUMBER = L_CATEGORY_REC.OBJECT_VERSION_NUMBER + 1,
535 ARC_CATEGORY_CREATED_FOR = L_CATEGORY_REC.ARC_CATEGORY_CREATED_FOR,
536 ENABLED_FLAG = L_CATEGORY_REC.ENABLED_FLAG,
537 PARENT_CATEGORY_ID = L_CATEGORY_REC.PARENT_CATEGORY_ID,
538 ATTRIBUTE_CATEGORY = L_CATEGORY_REC.ATTRIBUTE_CATEGORY,
539 ATTRIBUTE1 = L_CATEGORY_REC.ATTRIBUTE1,
540 ATTRIBUTE2 = L_CATEGORY_REC.ATTRIBUTE2,
541 ATTRIBUTE3 = L_CATEGORY_REC.ATTRIBUTE3,
542 ATTRIBUTE4 = L_CATEGORY_REC.ATTRIBUTE4,
543 ATTRIBUTE5 = L_CATEGORY_REC.ATTRIBUTE5,
544 ATTRIBUTE6 = L_CATEGORY_REC.ATTRIBUTE6,
545 ATTRIBUTE7 = L_CATEGORY_REC.ATTRIBUTE7,
546 ATTRIBUTE8 = L_CATEGORY_REC.ATTRIBUTE8,
547 ATTRIBUTE9 = L_CATEGORY_REC.ATTRIBUTE9,
548 ATTRIBUTE10 = L_CATEGORY_REC.ATTRIBUTE10,
549 ATTRIBUTE11 = L_CATEGORY_REC.ATTRIBUTE11,
550 ATTRIBUTE12 = L_CATEGORY_REC.ATTRIBUTE12,
554 ACCRUED_LIABILITY_ACCOUNT = L_CATEGORY_REC.ACCRUED_LIABILITY_ACCOUNT,
551 ATTRIBUTE13 = L_CATEGORY_REC.ATTRIBUTE13,
552 ATTRIBUTE14 = L_CATEGORY_REC.ATTRIBUTE14,
553 ATTRIBUTE15 = L_CATEGORY_REC.ATTRIBUTE15,
555 DED_ADJUSTMENT_ACCOUNT = L_CATEGORY_REC.DED_ADJUSTMENT_ACCOUNT,
556 BUDGET_CODE_SUFFIX = L_CATEGORY_REC.BUDGET_CODE_SUFFIX,
557 LEDGER_ID = L_CATEGORY_REC.LEDGER_ID
558 WHERE
559 CATEGORY_ID = l_category_rec.category_id;
560
561 IF (SQL%NOTFOUND) THEN
562 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
563 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
564 FND_MSG_PUB.add;
565 END IF;
566 RAISE FND_API.g_exc_error;
567 END IF;
568
569 UPDATE AMS_CATEGORIES_TL
570 SET
571 CATEGORY_NAME = l_category_rec.CATEGORY_NAME,
572 DESCRIPTION = l_category_rec.DESCRIPTION,
573 LAST_UPDATE_DATE = sysdate,
574 LAST_UPDATED_BY = FND_GLOBAL.User_Id,
575 LAST_UPDATE_LOGIN = FND_GLOBAL.Conc_Login_Id,
576 SOURCE_LANG = userenv('LANG')
577 WHERE
578 CATEGORY_ID = p_category_rec.category_id
579 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
580
581 IF (SQL%NOTFOUND) THEN
582 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
583 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
584 FND_MSG_PUB.add;
585 END IF;
586 RAISE FND_API.g_exc_error;
587 END IF;
588
589 END IF;
590
591 -------------------- finish --------------------------
592
593 --IF (AMS_DEBUG_HIGH_ON) THENAMS_Utility_PVT.debug_message(l_full_name ||': After update');END IF;
594 -- Standard check of p_commit.
595 IF FND_API.To_Boolean ( p_commit )
596 THEN
597 COMMIT WORK;
598 END IF;
599
600 -- Standard call to get message count AND IF count is 1, get message info.
601 FND_MSG_PUB.Count_AND_Get
602 ( p_count => x_msg_count,
603 p_data => x_msg_data,
604 p_encoded => FND_API.G_FALSE
605 );
606 IF (AMS_DEBUG_HIGH_ON) THEN
607
608 AMS_Utility_PVT.debug_message(l_full_name ||': end');
609 END IF;
610
611 EXCEPTION
612
613 WHEN FND_API.G_EXC_ERROR
614 THEN
615 ROLLBACK TO Update_Category_PVT;
616 x_return_status := FND_API.G_RET_STS_ERROR ;
617
618 FND_MSG_PUB.Count_AND_Get
619 ( p_count => x_msg_count,
620 p_data => x_msg_data,
621 p_encoded => FND_API.G_FALSE
622 );
623
624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
625 THEN
626 ROLLBACK TO Update_Category_PVT;
627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
628
629 FND_MSG_PUB.Count_AND_Get
630 ( p_count => x_msg_count,
631 p_data => x_msg_data,
632 p_encoded => FND_API.G_FALSE
633 );
634
635 /*
636 WHEN OTHERS THEN
637
638 ROLLBACK TO Update_Category_PVT;
639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
640
641 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
642 THEN
643 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
644 END IF;
645
646 FND_MSG_PUB.Count_AND_Get
647 ( p_count => x_msg_count,
648 p_data => x_msg_data,
649 p_encoded => FND_API.G_FALSE
650 );
651 */
652
653 END Update_Category;
654
655 -- Start of Comments
656 --
657 -- NAME
658 -- Delete_category
659 --
660 -- PURPOSE
661 -- This procedure is to delete a category record that satisfy caller needs
662 --
663 -- NOTES
664 --
665 --
666 -- HISTORY
667 -- 01/04/2000 sugupta created
668 -- End of Comments
669
670 PROCEDURE Delete_Category
671 ( p_api_version IN NUMBER,
672 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
673 p_commit IN VARCHAR2 := FND_API.g_false,
674 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
675 x_return_status OUT NOCOPY VARCHAR2,
676 x_msg_count OUT NOCOPY NUMBER,
677 x_msg_data OUT NOCOPY VARCHAR2,
678
679 p_category_id IN NUMBER,
680 p_object_version IN NUMBER
681 ) IS
682
683 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Category';
684 l_api_version CONSTANT NUMBER := 1.0;
685 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME ||'.'|| l_api_name;
686
687 -- Status Local Variables
688 l_return_status VARCHAR2(1); -- Return value from procedures
689 l_return_val VARCHAR2(1);
690 l_category_id NUMBER := p_category_id;
691
692 BEGIN
693
694 -- Standard Start of API savepoint
695 SAVEPOINT Delete_Category_PVT;
696 IF (AMS_DEBUG_HIGH_ON) THEN
697
698 AMS_Utility_PVT.debug_message(l_full_name||': start');
699 END IF;
700
701 -- Standard call to check for call compatibility.
702 IF NOT FND_API.Compatible_API_Call ( l_api_version,
703 p_api_version,
707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
704 l_api_name,
705 G_PACKAGE_NAME)
706 THEN
708 END IF;
709
710 -- Initialize message list IF p_init_msg_list is set to TRUE.
711 IF FND_API.to_Boolean( p_init_msg_list )
712 THEN
713 FND_MSG_PUB.initialize;
714 END IF;
715
716 -- Initialize API return status to success
717 x_return_status := FND_API.G_RET_STS_SUCCESS;
718
719 ------------------------ delete ------------------------
720 IF (AMS_DEBUG_HIGH_ON) THEN
721
722 AMS_Utility_PVT.debug_message(l_full_name ||': delete');
723 END IF;
724
725 -- Perform the database operation
726
727 -- Check all child tables if data exists (child entities validation)
728
729 Validate_Cty_Child_Enty
730 ( p_category_id => l_category_id,
731 x_return_status => l_return_status
732 );
733
734 -- If any errors happen abort API.
735 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
736 THEN
737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
738 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
739 THEN
740 RAISE FND_API.G_EXC_ERROR;
741 END IF;
742
743 -- seeded category if category_id < 10000
744 -- user cannot modify or delete seeded category
745 IF (l_category_id < 10000 ) THEN
746 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
747 THEN
748 FND_MESSAGE.set_name('AMS', 'AMS_CAT_CANNOT_MOD_SEED');
749 FND_MSG_PUB.add;
750 END IF;
751 RAISE FND_API.g_exc_error;
752 END IF;
753
754 delete from AMS_CATEGORIES_B
755 where category_id = l_category_id
756 and object_version_number = p_object_version;
757
758 IF (SQL%NOTFOUND) THEN
759 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
760 THEN
761 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
762 FND_MSG_PUB.add;
763 END IF;
764 RAISE FND_API.g_exc_error;
765 END IF;
766
767
768 delete from AMS_CATEGORIES_TL
769 where category_id = l_category_id
770 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
771
772 IF (SQL%NOTFOUND) THEN
773 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
774 THEN
775 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
776 FND_MSG_PUB.add;
777 END IF;
778 RAISE FND_API.g_exc_error;
779 END IF;
780
781 -- Call Private API to cascade delete any children data if necessary
782
783 -------------------- finish --------------------------
784 -- Standard check of p_commit.
785 IF FND_API.To_Boolean ( p_commit )
786 THEN
787 COMMIT WORK;
788 END IF;
789
790 -- Standard call to get message count AND IF count is 1, get message info.
791 FND_MSG_PUB.Count_AND_Get
792 ( p_count => x_msg_count,
793 p_data => x_msg_data,
794 p_encoded => FND_API.G_FALSE
795 );
796 IF (AMS_DEBUG_HIGH_ON) THEN
797
798 AMS_Utility_PVT.debug_message(l_full_name ||': end');
799 END IF;
800
801 EXCEPTION
802
803 WHEN FND_API.G_EXC_ERROR
804 THEN
805 ROLLBACK TO Delete_Category_PVT;
806 x_return_status := FND_API.G_RET_STS_ERROR ;
807
808 FND_MSG_PUB.Count_AND_Get
809 ( p_count => x_msg_count,
810 p_data => x_msg_data,
811 p_encoded => FND_API.G_FALSE
812 );
813
814
815 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
816 THEN
817 ROLLBACK TO Delete_Category_PVT;
818 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
819
820 FND_MSG_PUB.Count_AND_Get
821 ( p_count => x_msg_count,
822 p_data => x_msg_data,
823 p_encoded => FND_API.G_FALSE
824 );
825
826 WHEN OTHERS THEN
827
828 ROLLBACK TO Delete_Category_PVT;
829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
830
831 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
832 THEN
833 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
834 END IF;
835
836 FND_MSG_PUB.Count_AND_Get
837 ( p_count => x_msg_count,
838 p_data => x_msg_data,
839 p_encoded => FND_API.G_FALSE
840 );
841
842 END Delete_Category;
843
844 -- Start of Comments
845 --
846 -- NAME
847 -- Lock_category
848 --
849 -- PURPOSE
850 -- This procedure is to lock a category record that satisfy caller needs
851 --
852 -- NOTES
853 --
854 --
855 -- HISTORY
856 -- 01/04/2000 sugupta created
857 -- End of Comments
858
862 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
859 PROCEDURE Lock_Category
860 ( p_api_version IN NUMBER,
861 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
863 x_return_status OUT NOCOPY VARCHAR2,
864 x_msg_count OUT NOCOPY NUMBER,
865 x_msg_data OUT NOCOPY VARCHAR2,
866
867 p_category_id IN NUMBER,
868 p_object_version IN NUMBER
869 ) IS
870
871 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Category';
872 l_api_version CONSTANT NUMBER := 1.0;
873 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME ||'.'|| l_api_name;
874
875 -- Status Local Variables
876 l_return_status VARCHAR2(1); -- Return value from procedures
877
878 CURSOR C_ams_categories_b IS
879 SELECT ARC_CATEGORY_CREATED_FOR,
880 PARENT_CATEGORY_ID
881 FROM AMS_CATEGORIES_B
882 WHERE category_id = p_category_id
883 and object_version_number = p_object_version
884 FOR UPDATE of category_id NOWAIT;
885 Recinfo C_ams_categories_b%ROWTYPE;
886
887 CURSOR C_ams_categories_tl IS
888 SELECT CATEGORY_NAME,
889 DESCRIPTION,
890 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
891 FROM AMS_CATEGORIES_TL
892 WHERE CATEGORY_ID = p_CATEGORY_ID
893 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
894 FOR UPDATE OF CATEGORY_ID NOWAIT;
895 Tlinfo C_ams_categories_tl%ROWTYPE;
896
897 BEGIN
898 IF (AMS_DEBUG_HIGH_ON) THEN
899
900 AMS_Utility_PVT.debug_message(l_full_name||': start');
901 END IF;
902 -- Standard call to check for call compatibility.
903 IF NOT FND_API.Compatible_API_Call ( l_api_version,
904 p_api_version,
905 l_api_name,
906 G_PACKAGE_NAME)
907 THEN
908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
909 END IF;
910
911 -- Initialize message list if p_init_msg_list is set to TRUE.
912 IF FND_API.to_Boolean( p_init_msg_list ) THEN
913 FND_MSG_PUB.initialize;
914 END IF;
915 -- Initialize API return status to success
916 x_return_status := FND_API.G_RET_STS_SUCCESS;
917
918 ------------------------ lock -------------------------
919 IF (AMS_DEBUG_HIGH_ON) THEN
920
921 AMS_Utility_PVT.debug_message(l_full_name||': lock');
922 END IF;
923
924 -- Perform the database operation
925 OPEN C_ams_categories_b;
926 FETCH C_ams_categories_b INTO Recinfo;
927 IF (C_ams_categories_b%NOTFOUND) THEN
928 CLOSE C_ams_categories_b;
929 -- Error, check the msg level and added an error message to the
930 -- API message list
931 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
932 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
933 FND_MSG_PUB.add;
934 END IF;
935
936 RAISE FND_API.G_EXC_ERROR;
937 END IF;
938
939 CLOSE C_ams_categories_b;
940
941 open C_ams_categories_tl ;
942 close C_ams_categories_tl;
943
944 -- Standard call to get message count AND IF count is 1, get message info.
945 FND_MSG_PUB.Count_AND_Get
946 ( p_count => x_msg_count,
947 p_data => x_msg_data,
948 p_encoded => FND_API.G_FALSE
949 );
950 IF (AMS_DEBUG_HIGH_ON) THEN
951
952 AMS_Utility_PVT.debug_message(l_full_name ||': end');
953 END IF;
954
955 EXCEPTION
956
957 WHEN FND_API.G_EXC_ERROR
958 THEN
959 x_return_status := FND_API.G_RET_STS_ERROR ;
960
961 FND_MSG_PUB.Count_AND_Get
962 ( p_count => x_msg_count,
963 p_data => x_msg_data,
964 p_encoded => FND_API.G_FALSE
965 );
966
967 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
968 THEN
969 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
970
971 FND_MSG_PUB.Count_AND_Get
972 ( p_count => x_msg_count,
973 p_data => x_msg_data,
974 p_encoded => FND_API.G_FALSE
975 );
976
977 WHEN AMS_Utility_PVT.RESOURCE_LOCKED
978 THEN
979 x_return_status := FND_API.G_RET_STS_ERROR ;
980
981 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
982 THEN -- MMSG
983 FND_MESSAGE.SET_NAME('AMS','AMS_API_RESOURCE_LOCKED');
984 FND_MSG_PUB.Add;
985 END IF;
986
987 FND_MSG_PUB.Count_AND_Get
988 ( p_count => x_msg_count,
989 p_data => x_msg_data,
990 p_encoded => FND_API.G_FALSE
991 );
992
993 WHEN OTHERS THEN
994
995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
996
997 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
998 THEN
999 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
1003 ( p_count => x_msg_count,
1000 END IF;
1001
1002 FND_MSG_PUB.Count_AND_Get
1004 p_data => x_msg_data,
1005 p_encoded => FND_API.G_FALSE
1006 );
1007
1008 END Lock_Category;
1009
1010 -- Start of Comments
1011 --
1012 -- NAME
1013 -- Validate_Category
1014 --
1015 -- PURPOSE
1016 -- This procedure is to validate a category record that satisfy caller needs
1017 --
1018 -- NOTES
1019 --
1020 --
1021 -- HISTORY
1022 -- 01/04/2000 sugupta created
1023 -- End of Comments
1024
1025 PROCEDURE Validate_Category
1026 ( p_api_version IN NUMBER,
1027 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1028 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1029 x_return_status OUT NOCOPY VARCHAR2,
1030 x_msg_count OUT NOCOPY NUMBER,
1031 x_msg_data OUT NOCOPY VARCHAR2,
1032
1033 p_category_rec IN category_rec_type
1034 ) IS
1035
1036 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Category';
1037 l_api_version CONSTANT NUMBER := 1.0;
1038 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME ||'.'|| l_api_name;
1039
1040 -- Status Local Variables
1041 l_return_status VARCHAR2(1); -- Return value from procedures
1042 l_category_rec category_rec_type := p_category_rec;
1043 l_category_id AMS_CATEGORIES_VL.CATEGORY_ID%TYPE;
1044
1045 BEGIN
1046 ----------------------- initialize --------------------
1047 IF (AMS_DEBUG_HIGH_ON) THEN
1048
1049 AMS_Utility_PVT.debug_message(l_full_name||': start');
1050 END IF;
1051
1052 -- Standard call to check for call compatibility.
1053 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1054 p_api_version,
1055 l_api_name,
1056 G_PACKAGE_NAME)
1057 THEN
1058 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1059 END IF;
1060
1061 -- Initialize message list if p_init_msg_list is set to TRUE.
1062 IF FND_API.to_Boolean( p_init_msg_list )
1063 THEN
1064 FND_MSG_PUB.initialize;
1065 END IF;
1066
1067 -- Initialize API return status to success
1068 x_return_status := FND_API.G_RET_STS_SUCCESS;
1069
1070 -- step 1
1071 -- Validate all required parameters -- combined with step 2
1072 -- Note: We need to pass all columns when you call Update_Category API.
1073 -- This means that we always need to validate required parameters even in
1074 -- update mode.
1075
1076 -- step2
1077 -- Validate all non missing attributes (Item level validation)
1078
1079 IF p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_ITEM
1080 THEN
1081
1082 Check_Req_Cty_Items
1083 ( p_category_rec => l_category_rec,
1084 x_return_status => l_return_status
1085 );
1086
1087 -- If any errors happen abort API.
1088 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1089 THEN
1090 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1091
1092 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1093 THEN
1094 RAISE FND_API.G_EXC_ERROR;
1095 END IF;
1096
1097 Validate_Cty_Items
1098 ( p_category_rec => l_category_rec,
1099 x_return_status => l_return_status
1100 );
1101
1102 -- If any errors happen abort API.
1103 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1104 THEN
1105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1106
1107 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1108 THEN
1109 RAISE FND_API.G_EXC_ERROR;
1110 END IF;
1111
1112 Validate_Category_Cross_Record
1113 ( p_category_rec => l_category_rec
1114 ,x_return_status => l_return_status
1115 );
1116
1117 -- If any errors happen abort API.
1118 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1119 RAISE FND_API.G_EXC_ERROR;
1120 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1121 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1122 END IF;
1123
1124 IF (AMS_DEBUG_HIGH_ON) THEN
1125
1126
1127
1128 AMS_UTILITY_PVT.debug_message('the return status after Validate_Category_Cross_Rec :'||x_return_status);
1129
1130 END IF;
1131 END IF;
1132
1133 -- Step 3.
1134 -- Perform cross attribute validation and missing attribute checks. Record
1135 -- level validation.
1136 IF p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_RECORD
1137 THEN
1138
1139 Validate_Cty_Record
1140 ( p_api_version => 1.0,
1141 p_init_msg_list => FND_API.G_FALSE,
1142 x_return_status => l_return_status,
1143 x_msg_count => x_msg_count,
1147 );
1144 x_msg_data => x_msg_data,
1145
1146 p_category_rec => l_category_rec
1148
1149 -- If any errors happen abort API.
1150 IF l_return_status = FND_API.G_RET_STS_ERROR
1151 THEN
1152 RAISE FND_API.G_EXC_ERROR;
1153
1154 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1155 THEN
1156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1157 END IF;
1158
1159 -- Step 4.
1160 -- Perform cross record validation. Cross Record level validation.
1161
1162 Validate_Category_Cross_Record
1163 ( p_category_rec => l_category_rec
1164 ,x_return_status => l_return_status
1165 );
1166
1167 -- If any errors happen abort API.
1168 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1169 RAISE FND_API.G_EXC_ERROR;
1170 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1172 END IF;
1173
1174
1175 -- Step 5.
1176 -- Perform cross entity validation. Cross entity level validation.
1177
1178 Validate_Category_Cross_Entity
1179 ( p_category_rec => l_category_rec
1180 ,x_return_status => l_return_status
1181 );
1182
1183 -- If any errors happen abort API.
1184 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1185 RAISE FND_API.G_EXC_ERROR;
1186 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1188 END IF;
1189
1190 --
1191 END IF;
1192 -- Standard call to get message count AND IF count is 1, get message info.
1193 FND_MSG_PUB.Count_AND_Get
1194 ( p_count => x_msg_count,
1195 p_data => x_msg_data,
1196 p_encoded => FND_API.G_FALSE
1197 );
1198
1199 EXCEPTION
1200
1201 WHEN FND_API.G_EXC_ERROR
1202 THEN
1203 x_return_status := FND_API.G_RET_STS_ERROR ;
1204
1205 FND_MSG_PUB.Count_AND_Get
1206 ( p_count => x_msg_count,
1207 p_data => x_msg_data,
1208 p_encoded => FND_API.G_FALSE
1209 );
1210
1211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1212 THEN
1213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1214
1215 FND_MSG_PUB.Count_AND_Get
1216 ( p_count => x_msg_count,
1217 p_data => x_msg_data,
1218 p_encoded => FND_API.G_FALSE
1219 );
1220
1221 WHEN OTHERS THEN
1222
1223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1224
1225 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1226 THEN
1227 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
1228 END IF;
1229
1230 FND_MSG_PUB.Count_AND_Get
1231 ( p_count => x_msg_count,
1232 p_data => x_msg_data,
1233 p_encoded => FND_API.G_FALSE
1237
1234 );
1235
1236 END Validate_Category;
1238 -- Start of Comments
1239 --
1240 -- NAME
1241 -- Validate_Cty_Items
1242 --
1243 -- PURPOSE
1244 -- This procedure is to validate category items
1245 --
1246 -- NOTES
1247 --
1248 --
1249 -- HISTORY
1250 -- 01/04/2000 sugupta created
1251 -- End of Comments
1252
1253 PROCEDURE Validate_Cty_Items
1254 ( p_category_rec IN category_rec_type,
1255 x_return_status OUT NOCOPY VARCHAR2
1256 ) IS
1257
1258 l_table_name VARCHAR2(30);
1259 l_pk_name VARCHAR2(30);
1260 l_pk_value VARCHAR2(30);
1261 l_additional_where_clause VARCHAR2(4000) := ' enabled_flag = ''Y''';
1262
1263 BEGIN
1264 -- Initialize API/Procedure return status to success
1265 x_return_status := FND_API.G_RET_STS_SUCCESS;
1266
1267 -- Check FK parameter: PARENT_CATEGORY_ID
1268 -- Do not validate FK if NULL
1269
1270 IF p_category_rec.PARENT_CATEGORY_ID <> FND_API.g_miss_num AND
1271 p_category_rec.PARENT_CATEGORY_ID is NOT NULL THEN
1272
1273 l_table_name := 'AMS_CATEGORIES_B';
1274 l_pk_name := 'CATEGORY_ID';
1275 l_pk_value := p_category_rec.PARENT_CATEGORY_ID;
1276
1277 IF AMS_Utility_PVT.Check_FK_Exists (
1278 p_table_name => l_table_name
1279 ,p_pk_name => l_pk_name
1280 ,p_pk_value => l_pk_value
1281 ,p_additional_where_clause => l_additional_where_clause -- Bug 3847393 in 11.5.10.1R
1282 ) = FND_API.G_FALSE
1283 THEN
1284 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1285 THEN
1286 FND_MESSAGE.set_name('AMS', 'AMS_CAT_BAD_PARENT_CAT_ID');
1287 FND_MSG_PUB.add;
1288 END IF;
1289 x_return_status := FND_API.G_RET_STS_ERROR;
1290 -- If any errors happen abort API/Procedure.
1291 RETURN;
1292
1293 END IF; -- check_fk_exists
1294 END IF;
1295 IF (AMS_DEBUG_HIGH_ON) THEN
1296
1297 AMS_UTILITY_PVT.debug_message('the return status after primary check :'||x_return_status);
1298 END IF;
1299
1300 IF p_category_rec.enabled_flag <> FND_API.g_miss_char
1301 AND p_category_rec.enabled_flag IS NOT NULL
1302 THEN
1303 IF AMS_Utility_PVT.is_Y_or_N(p_category_rec.enabled_flag) = FND_API.g_false
1304 THEN
1305 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1306 THEN
1307 FND_MESSAGE.set_name('AMS', 'AMS_CAT_BAD_ENABLED_FLAG');
1308 FND_MSG_PUB.add;
1309 END IF;
1310 x_return_status := FND_API.g_ret_sts_error;
1311 RETURN;
1312 END IF;
1313 END IF;
1314 IF (AMS_DEBUG_HIGH_ON) THEN
1315
1316 AMS_UTILITY_PVT.debug_message('the return status after enabled flag :'||x_return_status);
1317 END IF;
1318
1319 END Validate_Cty_Items;
1320
1321 -- Start of Comments
1322 --
1323 -- NAME
1324 -- Validate_Cty_Record
1325 --
1326 -- PURPOSE
1327 -- This procedure is to validate category record
1328 --
1329 -- NOTES
1330 --
1331 --
1332 -- HISTORY
1333 -- 01/04/2000 sugupta created
1334 -- End of Comments
1335
1336 PROCEDURE Validate_Cty_Record
1337 ( p_api_version IN NUMBER,
1338 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1339 x_return_status OUT NOCOPY VARCHAR2,
1340 x_msg_count OUT NOCOPY NUMBER,
1341 x_msg_data OUT NOCOPY VARCHAR2,
1342
1343 p_category_rec IN category_rec_type
1344 ) IS
1345
1346 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Cty_Record';
1347 l_api_version CONSTANT NUMBER := 1.0;
1348 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME ||'.'|| l_api_name;
1349
1350 -- Status Local Variables
1351 l_return_status VARCHAR2(1); -- Return value from procedures
1352 --l_category_rec category_rec_type := p_category_rec;
1353
1354 cursor get_parent_det (l_parent_cat_id IN NUMBER ) is
1355 select ARC_CATEGORY_CREATED_FOR
1356 from AMS_CATEGORIES_B
1357 where category_id = l_parent_cat_id;
1358
1359 l_parent_cr_for varchar2(30);
1360
1361 CURSOR get_enabled_child (l_parent_cat_id IN NUMBER)
1362 IS
1363 SELECT COUNT(*)
1364 FROM ams_categories_b
1365 WHERE parent_category_id = l_parent_cat_id
1366 AND enabled_flag = 'Y';
1367
1368
1369 l_count NUMBER := 0;
1370
1371 BEGIN
1372
1373 -- Standard call to check for call compatibility.
1374 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1375 p_api_version,
1376 l_api_name,
1377 G_PACKAGE_NAME)
1378 THEN
1379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1380 END IF;
1381
1382 -- Initialize message list if p_init_msg_list is set to TRUE.
1383 IF FND_API.to_Boolean( p_init_msg_list )
1384 THEN
1385 FND_MSG_PUB.initialize;
1389
1386 END IF;
1387 -- Initialize API return status to success
1388 x_return_status := FND_API.G_RET_STS_SUCCESS;
1390
1391 /* Cannot disable Category if it is being used anywhere */
1392 /*
1393 -- Its commented out because this is not the intended functionality.
1394
1395 IF ( p_category_rec.PARENT_CATEGORY_ID <> FND_API.g_miss_num AND
1396 p_category_rec.PARENT_CATEGORY_ID is NOT NULL AND
1397 p_category_rec.ENABLED_FLAG = 'N' ) THEN
1398
1399 IF
1400 (AMS_Utility_PVT.Check_FK_Exists
1401 ( p_table_name => 'AMS_DELIVERABLES_ALL_B'
1402 ,p_pk_name => 'CATEGORY_TYPE_ID'
1403 ,p_pk_value => p_category_rec.PARENT_CATEGORY_ID
1404 ) = FND_API.G_TRUE )
1405
1406 OR
1407
1408 (AMS_Utility_PVT.Check_FK_Exists
1409 ( p_table_name => 'AMS_ACT_CATEGORIES'
1410 ,p_pk_name => 'CATEGORY_ID'
1411 ,p_pk_value => p_category_rec.PARENT_CATEGORY_ID
1412 ) = FND_API.G_TRUE )
1413
1414 OR
1415
1416 (AMS_Utility_PVT.Check_FK_Exists
1417 ( p_table_name => 'AMS_METRICS_ALL_B'
1418 ,p_pk_name => 'METRIC_CATEGORY'
1419 ,p_pk_value => p_category_rec.PARENT_CATEGORY_ID
1420 ) = FND_API.G_TRUE )
1421
1422 OR
1423
1424 (AMS_Utility_PVT.Check_FK_Exists
1425 ( p_table_name => 'OZF_FUNDS_ALL_B'
1426 ,p_pk_name => 'CATEGORY_ID'
1427 ,p_pk_value => p_category_rec.PARENT_CATEGORY_ID
1428 ) = FND_API.G_TRUE )
1429
1430
1431 THEN
1432 -- FK checking
1433 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1434 THEN -- MMSG
1435 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_CANT_DISABLE');
1436 FND_MSG_PUB.Add;
1437 END IF;
1438 x_return_status := FND_API.g_ret_sts_error;
1439 RETURN;
1440 END IF;
1441
1442
1443 END IF;
1444 */
1445 /* Cannot disable a category if it has the enabled sub -category */
1446 IF ( p_category_rec.CATEGORY_ID <> FND_API.g_miss_num
1447 AND p_category_rec.CATEGORY_ID is NOT NULL
1448 AND p_category_rec.ENABLED_FLAG = 'N' )
1449 THEN
1450
1451
1452 OPEN get_enabled_child(p_category_rec.CATEGORY_ID);
1453 FETCH get_enabled_child INTO l_count;
1454 CLOSE get_enabled_child;
1455
1456 IF l_count > 0 THEN
1457
1458 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1459 THEN -- MMSG
1460 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_CANT_DISABLE_PARENT');
1461 FND_MSG_PUB.Add;
1462 END IF;
1463
1464 x_return_status := FND_API.g_ret_sts_error;
1465 RETURN;
1466 END IF;
1467 END IF;
1468
1469 /* Parent Category created for has to be the same as category created for */
1470 IF (p_category_rec.PARENT_CATEGORY_ID IS NOT NULL) THEN
1471 open get_parent_det(p_category_rec.PARENT_CATEGORY_ID);
1472 fetch get_parent_det into l_parent_cr_for;
1473
1474 IF (l_parent_cr_for <> p_category_rec.ARC_CATEGORY_CREATED_FOR)
1475 THEN
1476 FND_MESSAGE.Set_Name('AMS', 'AMS_INV_PARENT_CR_FOR');
1477 FND_MSG_PUB.Add;
1478
1479 x_return_status := FND_API.g_ret_sts_error;
1480 RETURN;
1481 END IF;
1482
1483 /* bug fix for #1880798 */
1484 -- x_return_status := FND_API.get_ret_sts_error;
1485 -- RETURN;
1486
1487 END IF;
1488
1489 -- Standard call to get message count AND IF count is 1, get message info.
1490 FND_MSG_PUB.Count_AND_Get
1491 ( p_count => x_msg_count,
1492 p_data => x_msg_data,
1493 p_encoded => FND_API.G_FALSE
1494 );
1495
1496 EXCEPTION
1497
1498 WHEN FND_API.G_EXC_ERROR
1499 THEN
1500 x_return_status := FND_API.G_RET_STS_ERROR ;
1501
1502 FND_MSG_PUB.Count_AND_Get
1503 ( p_count => x_msg_count,
1504 p_data => x_msg_data,
1505 p_encoded => FND_API.G_FALSE
1506 );
1507
1508 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1509 THEN
1510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1511 FND_MSG_PUB.Count_AND_Get
1512 ( p_count => x_msg_count,
1513 p_data => x_msg_data,
1514 p_encoded => FND_API.G_FALSE
1515 );
1516
1520
1517 WHEN OTHERS THEN
1518
1519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1521 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1522 THEN
1523 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
1524 END IF;
1525
1526 FND_MSG_PUB.Count_AND_Get
1527 ( p_count => x_msg_count,
1528 p_data => x_msg_data,
1529 p_encoded => FND_API.G_FALSE
1530 );
1531
1532 END Validate_Cty_Record;
1533
1534 -- Start of Comments
1535 --
1536 -- NAME
1537 -- Validate_Category_Cross_Record
1538 --
1539 -- PURPOSE
1540 -- This procedure is to validate cross record AMS_Category_VL items
1541 --
1542 -- NOTES
1543 --
1544 --
1545 -- HISTORY
1546 -- 01/04/2000 sugupta created
1547 -- End of Comments
1548
1549 PROCEDURE Validate_Category_Cross_Record
1550 ( p_category_rec IN category_rec_type,
1551 x_return_status OUT NOCOPY VARCHAR2
1552 ) IS
1553
1554 -- Status Local Variables
1555 l_return_status VARCHAR2(1); -- Return value from procedures
1556 l_item_name VARCHAR2(30); -- Return value from procedures
1557 l_dummy NUMBER;
1558 cursor c_ctg_name_crt(ctg_name_in IN VARCHAR2, ctg_id_in IN NUMBER,ctg_arc_in IN VARCHAR2) IS
1559 SELECT 1 FROM DUAL WHERE EXISTS (select 1 from AMS_CATEGORIES_TL t,
1560 AMS_CATEGORIES_B b
1561 where t.category_name = ctg_name_in
1562 and b.arc_category_created_for = ctg_arc_in
1563 and language = userenv('LANG')
1564 and t.category_id = b.category_id
1565 --and t.category_id = ctg_id_in
1566 --and b.category_id = ctg_id_in
1567 );
1568 cursor c_ctg_name_updt(ctg_name_in IN VARCHAR2, ctg_id_in IN NUMBER,ctg_arc_in IN VARCHAR2) IS
1569 SELECT 1 FROM DUAL WHERE EXISTS (select 1 from AMS_CATEGORIES_TL t,
1570 AMS_CATEGORIES_B b
1571 where t.category_name = ctg_name_in
1572 and b.arc_category_created_for = ctg_arc_in
1573 and language = userenv('LANG')
1574 and t.category_id = b.category_id
1575 and t.category_id <> ctg_id_in
1576 and b.category_id <> ctg_id_in);
1577
1578
1579 BEGIN
1580 -- Initialize API/Procedure return status to success
1581 x_return_status := FND_API.G_RET_STS_SUCCESS;
1582 l_item_name := 'CATEGORY_NAME, LANGUAGE';
1583
1584
1585 -- Check unique keys: CATEGORY_NAME, LANGUAGE
1586
1587 -- Insert mode
1588 IF p_category_rec.category_id IS NULL
1589 THEN
1590
1591 /* bug 1490374
1592 if AMS_Utility_PVT.Check_Uniqueness
1593 (p_table_name => 'AMS_CATEGORIES_TL',
1594 p_where_clause => 'category_name = '
1595 || ''''
1596 || p_category_rec.category_name
1597 || ''''
1598 || ' and language = userenv('
1599 || ''''
1600 || 'LANG'
1601 || ''''
1602 || ')'
1603 ) = FND_API.G_FALSE then
1604 */
1605 open c_ctg_name_crt(p_category_rec.category_name, p_category_rec.category_id, p_category_rec.arc_category_created_for);
1606 fetch c_ctg_name_crt into l_dummy;
1607 close c_ctg_name_crt;
1608 IF l_dummy = 1 THEN
1609
1610 -- invalid item
1611 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1612 THEN -- MMSG
1613 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_DUPLICATE_NAME');
1614 FND_MSG_PUB.Add;
1615 END IF;
1616 x_return_status := FND_API.G_RET_STS_ERROR;
1617 -- If any errors happen abort API/Procedure.
1618 return;
1619 END IF;
1620
1621 -- Update mode
1622 ELSE
1623 /* bug # 1490374
1624 if AMS_Utility_PVT.Check_Uniqueness
1625 (p_table_name => 'AMS_CATEGORIES_TL',
1626 p_where_clause => 'category_name = '
1627 || ''''
1628 || p_category_rec.category_name
1629 || ''''
1630 || ' and language = userenv('
1631 || ''''
1632 || 'LANG'
1633 || ''''
1634 || ')'
1635 || ' and category_id <>'
1636 || p_category_rec.category_id
1637 ) = FND_API.G_FALSE then
1638 */
1639 open c_ctg_name_updt(p_category_rec.category_name, p_category_rec.category_id,p_category_rec.arc_category_created_for);
1640 fetch c_ctg_name_updt into l_dummy;
1641 close c_ctg_name_updt;
1642 IF l_dummy = 1 THEN
1643
1644 -- invalid item
1648 FND_MSG_PUB.Add;
1645 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1646 THEN -- MMSG
1647 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_DUPLICATE_NAME');
1649 END IF;
1650 x_return_status := FND_API.G_RET_STS_ERROR;
1651 -- If any errors happen abort API/Procedure.
1652 return;
1653 END IF;
1654
1655 END IF;
1656
1657
1658 END Validate_Category_Cross_Record;
1659
1660 -- Start of Comments
1661 --
1662 -- NAME
1663 -- Validate_Category_Cross_Entity
1664 --
1665 -- PURPOSE
1666 -- This procedure is to validate cross entity AMS_CATEGORIES_VL items
1667 --
1668 -- NOTES
1669 --CATEGORY_ID
1670 --
1671 -- HISTORY
1672 -- 01/04/2000 cklee created
1673 -- End of Comments
1674
1675 PROCEDURE Validate_Category_Cross_Entity
1676 ( p_category_rec IN category_rec_type,
1677 x_return_status OUT NOCOPY VARCHAR2
1678 ) IS
1679
1680 BEGIN
1681 -- Initialize API/Procedure return status to success
1682 x_return_status := FND_API.G_RET_STS_SUCCESS;
1683
1684 END Validate_Category_Cross_Entity;
1685
1686
1687 -- Start of Comments
1688 --
1689 -- NAME
1690 -- Validate_Cty_Child_Enty
1691 --
1692 -- PURPOSE
1693 -- This procedure is to check if category child table's data exists
1694 --
1695 -- NOTES
1696 -- This procedure is an example for referential integrity check
1697 --
1698 --
1699 -- HISTORY
1700 -- 01/04/2000 sugupta created
1701 -- End of Comments
1702
1703 PROCEDURE Validate_Cty_Child_Enty
1704 ( p_category_id IN NUMBER,
1705 x_return_status OUT NOCOPY VARCHAR2
1706 ) IS
1707
1708 l_category_id NUMBER := p_category_id;
1709
1710 l_message_name VARCHAR2(255);
1711 l_pk_value VARCHAR2(30);
1712
1713 BEGIN
1714
1715 -- Initialize API/Procedure return status to success
1716 x_return_status := FND_API.G_RET_STS_SUCCESS;
1717 l_pk_value := l_category_id;
1718
1719 IF AMS_Utility_PVT.Check_FK_Exists
1720 ( p_table_name => 'AMS_CATEGORIES_B'
1721 ,p_pk_name => 'PARENT_CATEGORY_ID'
1722 ,p_pk_value => l_pk_value
1723 ) = FND_API.G_TRUE
1724 THEN
1725 -- FK checking
1726 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1727 THEN -- MMSG
1728 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_CANT_DEL_PARENT');
1729 FND_MSG_PUB.Add;
1730 END IF;
1731 x_return_status := FND_API.g_ret_sts_error;
1732 RETURN;
1733 END IF;
1734
1735 IF AMS_Utility_PVT.Check_FK_Exists
1736 ( p_table_name => 'AMS_ACT_CATEGORIES'
1737 ,p_pk_name => 'CATEGORY_ID'
1738 ,p_pk_value => l_pk_value
1739 ) = FND_API.G_TRUE
1740 THEN
1741 -- FK checking
1742 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1743 THEN -- MMSG
1744 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_CANT_DEL_ACT');
1745 FND_MSG_PUB.Add;
1746 END IF;
1747 x_return_status := FND_API.g_ret_sts_error;
1748 RETURN;
1749 END IF;
1750
1751
1752 IF AMS_Utility_PVT.Check_FK_Exists
1753 ( p_table_name => 'AMS_METRICS_ALL_B'
1754 ,p_pk_name => 'METRIC_CATEGORY'
1755 ,p_pk_value => l_pk_value
1756 ) = FND_API.G_TRUE
1757 THEN
1758 -- FK checking
1759 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1760 THEN -- MMSG
1761 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_CANT_DEL_METRICS');
1762 FND_MSG_PUB.Add;
1763 END IF;
1764 x_return_status := FND_API.g_ret_sts_error;
1765 RETURN;
1766 END IF;
1767
1768 IF AMS_Utility_PVT.Check_FK_Exists
1769 ( p_table_name => 'AMS_METRICS_ALL_B'
1770 ,p_pk_name => 'METRIC_SUB_CATEGORY'
1771 ,p_pk_value => l_pk_value
1772 ) = FND_API.G_TRUE
1773 THEN
1774 -- FK checking
1775 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1776 THEN -- MMSG
1777 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_CANT_DEL_METRICS');
1778 FND_MSG_PUB.Add;
1779 END IF;
1780 x_return_status := FND_API.g_ret_sts_error;
1781 RETURN;
1782 END IF;
1783 /********
1784 --commented by musman for bug fix # 1966294
1785 -- this AMS_DELIV_OFFERINGS_B table doesn't exists.
1786
1787 IF AMS_Utility_PVT.Check_FK_Exists
1791 ) = FND_API.G_TRUE
1788 ( p_table_name => 'AMS_DELIV_OFFERINGS_B'
1789 ,p_pk_name => 'CATEGORY_TYPE_ID'
1790 ,p_pk_value => l_pk_value
1792 THEN
1793 -- FK checking
1794 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1795 THEN -- MMSG
1796 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_CANT_DEL_DELV');
1797 FND_MSG_PUB.Add;
1798 END IF;
1799 x_return_status := FND_API.g_ret_sts_error;
1800 RETURN;
1801 END IF;
1802 *********************************/
1803 /* added by abhola */
1804 /* added for DELV and FUNDs */
1805
1806
1807 IF (AMS_DEBUG_HIGH_ON) THEN
1808
1809
1810
1811
1812
1813 AMS_UTILITY_PVT.debug_message(' checking the AMS_DELIVERABLES_ALL_B for cat ');
1814
1815
1816 END IF;
1817
1818 IF AMS_Utility_PVT.Check_FK_Exists
1819 ( p_table_name => 'AMS_DELIVERABLES_ALL_B'
1820 ,p_pk_name => 'CATEGORY_TYPE_ID'
1821 ,p_pk_value => l_pk_value
1822 ) = FND_API.G_TRUE
1823 THEN
1824 -- FK checking
1825 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1826 THEN -- MMSG
1827 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_CANT_DEL_DELV');
1828 FND_MSG_PUB.Add;
1829 END IF;
1830 x_return_status := FND_API.g_ret_sts_error;
1831 RETURN;
1832 END IF;
1833
1834 /* added by musman fix for 1794454 */
1835 IF (AMS_DEBUG_HIGH_ON) THEN
1836
1837 AMS_UTILITY_PVT.debug_message(' checking the AMS_DELIVERABLES_ALL_B for sub cat ');
1838 END IF;
1839 IF AMS_Utility_PVT.Check_FK_Exists
1840 ( p_table_name => 'AMS_DELIVERABLES_ALL_B'
1841 ,p_pk_name => 'CATEGORY_SUB_TYPE_ID'
1842 ,p_pk_value => l_pk_value
1843 ) = FND_API.G_TRUE
1844 THEN
1845 -- FK checking
1846 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1847 THEN -- MMSG
1848 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_CANT_DEL_DELV');
1849 FND_MSG_PUB.Add;
1850 END IF;
1851 x_return_status := FND_API.g_ret_sts_error;
1852 RETURN;
1853 END IF;
1854
1855
1856 IF (AMS_DEBUG_HIGH_ON) THEN
1857
1858
1859
1860
1861
1862 AMS_UTILITY_PVT.debug_message(' checking the ozf_funds_all_b for cat ');
1863
1864
1865 END IF;
1866 IF (AMS_DEBUG_HIGH_ON) THEN
1867
1868 AMS_UTILITY_PVT.debug_message(' the val of AMS_Utility_PVT.Check_FK_Exists : '||AMS_Utility_PVT.Check_FK_Exists
1869 ( p_table_name => 'OZF_FUNDS_ALL_B'
1870 ,p_pk_name => 'CATEGORY_ID'
1871 ,p_pk_value => l_pk_value
1872 ));
1873 END IF;
1874 IF AMS_Utility_PVT.Check_FK_Exists
1875 ( p_table_name => 'OZF_FUNDS_ALL_B'
1876 ,p_pk_name => 'CATEGORY_ID'
1877 ,p_pk_value => l_pk_value
1878 ) = FND_API.G_TRUE
1879 THEN
1880 -- FK checking
1881 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1882 THEN -- MMSG
1883 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_CANT_DEL_ACT');
1884 FND_MSG_PUB.Add;
1885 END IF;
1886 x_return_status := FND_API.g_ret_sts_error;
1887 RETURN;
1888 END IF;
1889 /******** end addition by abhola ***************/
1890
1891 /****** commented by ABHOLA
1892
1893 IF AMS_Utility_PVT.Check_FK_Exists
1894 ( p_table_name => 'AMS_DELIV_OFFERINGS_B'
1895 ,p_pk_name => 'CATEGORY_SUB_TYPE_ID'
1896 ,p_pk_value => l_pk_value
1897 ) = FND_API.G_TRUE
1898 THEN
1899 -- FK checking
1900 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1901 THEN -- MMSG
1902 FND_MESSAGE.Set_Name('AMS', 'AMS_CAT_CANT_DEL_DELV');
1903 FND_MSG_PUB.Add;
1904 END IF;
1905 x_return_status := FND_API.g_ret_sts_error;
1906 RETURN;
1907 END IF;
1908 *************** end by ABHOLA ****************/
1909 /*
1910 EXCEPTION
1911 WHEN others THEN
1912 null;
1913 */
1914
1915 END Validate_Cty_Child_Enty;
1916
1917
1918 -- Start of Comments
1919 --
1920 -- NAME
1921 -- Check_Req_Cty_Items
1922 --
1923 -- PURPOSE
1924 -- This procedure is to check required parameters that satisfy caller needs
1925 --
1926 -- NOTES
1927 --
1928 --
1932
1929 -- HISTORY
1930 -- 01/04/2000 sugupta created
1931 -- End of Comments
1933 PROCEDURE Check_Req_Cty_Items
1934 ( p_category_rec IN category_rec_type,
1935 x_return_status OUT NOCOPY VARCHAR2
1936 ) IS
1937
1938 BEGIN
1939
1940 -- Initialize API/Procedure return status to success
1941 x_return_status := FND_API.G_RET_STS_SUCCESS;
1942
1943 -- Check required parameters
1944 --
1945 -- CATEGORY_NAME
1946
1947 IF (p_category_rec.CATEGORY_NAME = FND_API.G_MISS_CHAR OR
1948 p_category_rec.CATEGORY_NAME IS NULL)
1949 THEN
1950
1951 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1952 THEN
1953 FND_MESSAGE.set_name('AMS', 'AMS_CAT_NO_CAT_NAME');
1954 FND_MSG_PUB.add;
1955 END IF;
1956 x_return_status := FND_API.g_ret_sts_error;
1957 RETURN;
1958 END IF;
1959
1960 EXCEPTION
1961 WHEN OTHERS THEN
1962 NULL;
1963
1964 END Check_Req_Cty_Items;
1965
1966 PROCEDURE complete_category_rec(
1967 p_category_rec IN category_rec_type,
1968 x_complete_rec OUT NOCOPY category_rec_type
1969 ) IS
1970
1971 CURSOR c_cat IS
1972 SELECT *
1973 FROM ams_categories_vl
1974 WHERE category_id = p_category_rec.category_id;
1975
1976 l_category_rec c_cat%ROWTYPE;
1977
1978 BEGIN
1979 x_complete_rec := p_category_rec;
1980
1981 OPEN c_cat;
1982 FETCH c_cat INTO l_category_rec;
1983 IF c_cat%NOTFOUND THEN
1984 CLOSE c_cat;
1985 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1986 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1987 FND_MSG_PUB.add;
1988 END IF;
1989 RAISE FND_API.g_exc_error;
1990 END IF;
1991 CLOSE c_cat;
1992
1993 IF p_category_rec.ARC_CATEGORY_CREATED_FOR = FND_API.g_miss_char THEN
1994 x_complete_rec.ARC_CATEGORY_CREATED_FOR := l_category_rec.ARC_CATEGORY_CREATED_FOR;
1995 END IF;
1996 IF p_category_rec.ENABLED_FLAG = FND_API.g_miss_char THEN
1997 x_complete_rec.ENABLED_FLAG := l_category_rec.ENABLED_FLAG;
1998 END IF;
1999
2000 IF p_category_rec.PARENT_CATEGORY_ID = FND_API.g_miss_num THEN
2001 x_complete_rec.PARENT_CATEGORY_ID := l_category_rec.PARENT_CATEGORY_ID;
2002 END IF;
2003
2004 IF p_category_rec.CATEGORY_NAME = FND_API.g_miss_char THEN
2005 x_complete_rec.CATEGORY_NAME := l_category_rec.CATEGORY_NAME;
2006 END IF;
2007
2008 IF p_category_rec.DESCRIPTION = FND_API.g_miss_char THEN
2009 x_complete_rec.DESCRIPTION := l_category_rec.DESCRIPTION;
2010 END IF;
2011
2012 IF p_category_rec.ACCRUED_LIABILITY_ACCOUNT = FND_API.g_miss_num THEN
2013 -- x_complete_rec.ACCRUED_LIABILITY_ACCOUNT := l_category_rec.ACCRUED_LIABILITY_ACCOUNT;
2014 x_complete_rec.ACCRUED_LIABILITY_ACCOUNT := null;
2015 END IF;
2016
2017 IF p_category_rec.DED_ADJUSTMENT_ACCOUNT = FND_API.g_miss_num THEN
2018 -- x_complete_rec.DED_ADJUSTMENT_ACCOUNT := l_category_rec.DED_ADJUSTMENT_ACCOUNT;
2019 x_complete_rec.DED_ADJUSTMENT_ACCOUNT :=null;
2020 END IF;
2021
2022 END complete_category_rec;
2023
2024 /*********************** server side TEST CASE *****************************************/
2025
2026 -- Start of Comments
2027 --
2028 -- NAME
2029 -- Unit_Test_Insert
2030 -- Unit_Test_Delete
2031 -- Unit_Test_Update
2032 -- Unit_Test_Lock
2033 --
2034 -- PURPOSE
2035 -- These procedures are to test each procedure that satisfy caller needs
2036 --
2037 -- NOTES
2038 --
2039 --
2040 -- HISTORY
2041 -- 01/04/2000 sugupta created
2042 -- End of Comments
2043
2044 --********************************************************
2045 /* 0614
2046 PROCEDURE Unit_Test_Insert
2047 IS
2048
2049 -- local variables
2050 l_act_category_rec category_rec_type;
2051 l_return_status VARCHAR2(1);
2052 l_msg_count NUMBER;
2053 l_msg_data VARCHAR2(200);
2054 l_category_id AMS_CATEGORIES_VL.CATEGORY_ID%TYPE;
2055
2056 l_category_req_item_rec category_rec_type;
2057 l_Category_validate_item_rec category_rec_type;
2058 l_Category_default_item_rec category_rec_type;
2059 l_Category_validate_row_rec category_rec_type;
2060
2061 BEGIN
2062
2063 -- turned on debug mode
2064 IF AMS_Category_PVT.g_debug = TRUE THEN
2065
2066 l_category_rec.CATEGORY_ID := 1234;
2067 l_category_rec.ARC_CATEGORY_CREATED_FOR := 'hung';
2068 l_category_rec.CATEGORY_NAME := 'sugupta_category';
2069
2070
2071 AMS_Category_PVT.Create_Category (
2072 p_api_version => 1.0 -- p_api_version
2073 ,p_init_msg_list => FND_API.G_FALSE
2074 ,p_commit => FND_API.G_FALSE
2075 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2076 ,x_return_status => l_return_status
2077 ,x_msg_count => l_msg_count
2078 ,x_msg_data => l_msg_data
2079
2080 ,p_PK => FND_API.G_TRUE
2081 ,p_default => FND_API.G_TRUE
2082 ,p_Category_req_item_rec => l_Category_req_item_rec
2083 ,p_Category_validate_item_rec => l_Category_validate_item_rec
2084 ,p_Category_default_item_rec => l_Category_default_item_rec
2088 );
2085 ,p_Category_validate_row_rec => l_Category_validate_row_rec
2086 ,p_category_rec => l_category_rec
2087 ,x_category_id => l_category_id
2089
2090 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2091 ELSE
2092 commit work;
2093 END IF;
2094
2095 NULL;
2096
2097 ELSE
2098 END IF;
2099
2100
2101 END Unit_Test_Insert;
2102
2103 --********************************************************
2104
2105 PROCEDURE Unit_Test_Delete
2106 IS
2107
2108 -- local variables
2109 l_category_rec category_rec_type;
2110 l_return_status VARCHAR2(1);
2111 l_msg_count NUMBER;
2112 l_msg_data VARCHAR2(200);
2113 l_category_id AMS_CATEGORIES_VL.CATEGORY_ID%TYPE;
2114
2115 l_Category_req_item_rec category_rec_type;
2116 l_Category_validate_item_rec category_rec_type;
2117 l_Category_default_item_rec category_rec_type;
2118 l_Category_validate_row_rec category_rec_type;
2119
2120 BEGIN
2121
2122 -- turned on debug mode
2123 IF AMS_Category_PVT.g_debug = TRUE
2124 THEN
2125
2126 l_category_rec.category_id := 1234;
2127
2128
2129 AMS_Category_PVT.Delete_Category (
2130 p_api_version => 1.0 -- p_api_version
2131 ,p_init_msg_list => FND_API.G_FALSE
2132 ,p_commit => FND_API.G_FALSE
2133 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2134 ,p_category_rec => l_category_rec
2135
2136 ,x_return_status => l_return_status
2137 ,x_msg_count => l_msg_count
2138 ,x_msg_data => l_msg_data
2139 );
2140
2141 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2142 ELSE
2143 commit work;
2144 END IF;
2145
2146 NULL;
2147
2148 ELSE
2149 END IF;
2150
2151
2152 END Unit_Test_Delete;
2153
2154
2155 --********************************************************
2156
2157 PROCEDURE Unit_Test_Update
2158 IS
2159
2160 -- local variables
2161 l_category_rec category_rec_type;
2162 l_return_status VARCHAR2(1);
2163 l_msg_count NUMBER;
2164 l_msg_data VARCHAR2(200);
2165 l_category_id AMS_CATEGORIES_VL.CATEGORY_ID%TYPE;
2166
2167 l_Category_req_item_rec category_rec_type;
2168 l_Category_validate_item_rec category_rec_type;
2169 l_Category_default_item_rec category_rec_type;
2170 l_Category_validate_row_rec category_rec_type;
2171
2172 cursor C(my_category_id NUMBER) is
2173 select *
2174 from AMS_CATEGORIES_VL
2175 WHERE CATEGORY_ID = my_category_id;
2176 BEGIN
2177
2178 -- turned on debug mode
2179 IF AMS_Category_PVT.g_debug = TRUE
2180 THEN
2181
2182 l_category_id := 1234;
2183 OPEN C(l_category_id);
2184 FETCH C INTO l_category_rec;
2185
2186 l_category_rec.NOTES := 'NOTES UPDATED1';
2187
2188
2189 AMS_Category_PVT.Update_Category (
2190 p_api_version => 1.0 -- p_api_version
2191 ,p_init_msg_list => FND_API.G_FALSE
2192 ,p_commit => FND_API.G_FALSE
2193 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2194 ,p_category_rec => l_category_rec
2195
2196 ,x_return_status => l_return_status
2197 ,x_msg_count => l_msg_count
2198 ,x_msg_data => l_msg_data
2199 );
2200
2201 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2202 THEN
2203 ELSE
2204 commit work;
2205 END IF;
2206
2207 NULL;
2208
2209 ELSE
2210 END IF;
2211
2212
2213 END Unit_Test_Update;
2214
2215
2216 --********************************************************
2217
2218
2219 PROCEDURE Unit_Test_Lock
2220 IS
2221
2222 -- local variables
2223 l_category_rec category_rec_type;
2224 l_return_status VARCHAR2(1);
2225 l_msg_count NUMBER;
2226 l_msg_data VARCHAR2(200);
2227 l_category_id AMS_CATEGORIES_VL.CATEGORY_ID%TYPE;
2228
2229 l_Category_req_item_rec category_rec_type;
2230 l_Category_validate_item_rec category_rec_type;
2231 l_Category_default_item_rec category_rec_type;
2232 l_Category_validate_row_rec category_rec_type;
2233
2234
2235 cursor C(my_category_id NUMBER) is
2236 select * from AMS_CATEGORIES_B WHERE CATEGORY_ID = my_category_id;
2237 BEGIN
2238
2239 -- turned on debug mode
2240 IF AMS_Category_PVT.g_debug = TRUE
2241 THEN
2242
2243 l_category_rec.category_id := 1234;
2244 l_category_rec.NOTES := 'server side test';
2245
2246
2247 AMS_Category_PVT.Lock_Category (
2248 p_api_version => 1.0 -- p_api_version
2249 ,p_init_msg_list => FND_API.G_FALSE
2250 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2251 ,p_category_rec => l_category_rec
2252
2253 ,x_return_status => l_return_status
2254 ,x_msg_count => l_msg_count
2255 ,x_msg_data => l_msg_data
2256 );
2257
2261
2258 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2259 --RAISE FND_API.G_EXC_ERROR;
2260 END IF;
2262 NULL;
2263
2264 ELSE
2265 END IF;
2266
2267
2268 END Unit_Test_Lock;
2269
2270 /*********************** server side TEST CASE *****************************************/
2271
2272 /*
2273
2274 PROCEDURE Unit_Test_Act_Insert
2275 is
2276
2277 -- local variables
2278 l_act_category_rec AMS_ACT_CATEGORIES%ROWTYPE;
2279 l_return_status VARCHAR2(1);
2280 l_msg_count NUMBER;
2281 l_msg_data VARCHAR2(200);
2282 l_act_category_id AMS_ACT_CATEGORIES.ACTIVITY_CATEGORY_ID%TYPE;
2283
2284 l_act_category_req_item_rec Act_category_rec_type;
2285 l_act_cty_validate_item_rec Act_category_rec_type;
2286 l_act_cty_default_item_rec Act_category_rec_type;
2287 l_act_cty_validate_row_rec Act_category_rec_type;
2288
2289 BEGIN
2290
2291 -- turned on debug mode
2292 IF AMS_Category_PVT.G_DEBUG = TRUE THEN
2293
2294 --********************************************************
2295 -- Insert case 1
2296
2297 l_act_category_rec.ACTIVITY_CATEGORY_ID := 1234;
2298 l_act_category_rec.ACT_CATEGORY_USED_BY_ID := 1000;
2299 l_act_category_rec.ARC_ACT_CATEGORY_USED_BY := 1000;
2300 l_act_category_rec.CATEGORY_ID := 1234;
2301
2302
2303 AMS_Category_PVT.Create_Act_Category (
2304 p_api_version => 1.0 -- p_api_version
2305 ,p_init_msg_list => FND_API.G_FALSE
2306 ,p_commit => FND_API.G_FALSE
2307 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2308 ,x_return_status => l_return_status
2309 ,x_msg_count => l_msg_count
2310 ,x_msg_data => l_msg_data
2311
2312 ,p_PK => FND_API.G_TRUE
2313 ,p_default => FND_API.G_TRUE
2314 ,p_Category_req_item_rec => l_act_category_req_item_rec
2315 ,p_Category_validate_item_rec => l_act_cty_validate_item_rec
2316 ,p_Category_default_item_rec => l_act_cty_default_item_rec
2317 ,p_Category_validate_row_rec => l_act_cty_validate_row_rec
2318 ,p_category_rec => l_act_category_rec
2319 ,x_act_category_id => l_act_category_id
2320 );
2321
2322 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2323 THEN
2324 ELSE
2325 commit work;
2326 END IF;
2327
2328 null;
2329
2330 ELSE
2331 END IF;
2332
2333 END Unit_Test_Act_Insert;
2334
2335
2336 PROCEDURE Unit_Test_Act_Delete
2337 is
2338
2339 -- local variables
2340 l_act_category_rec AMS_ACT_CATEGORIES%ROWTYPE;
2341 l_return_status VARCHAR2(1);
2342 l_msg_count NUMBER;
2343 l_msg_data VARCHAR2(200);
2344 l_act_category_id AMS_ACT_CATEGORIES.ACTIVITY_CATEGORY_ID%TYPE;
2345
2346 l_act_category_req_item_rec act_category_rec_type;
2347 l_act_cty_validate_item_rec act_category_rec_type;
2348 l_act_cty_default_item_rec act_category_rec_type;
2349 l_act_cty_validate_row_rec act_category_rec_type;
2350
2351 BEGIN
2352
2353 -- turned on debug mode
2354 IF AMS_Category_PVT.G_DEBUG = TRUE THEN
2355
2356
2357 -- Delete test case 1
2358 l_act_category_rec.activity_category_id := 1234;
2359 AMS_Category_PVT.Delete_Act_Category (
2360 p_api_version => 1.0 -- p_api_version
2361 ,p_init_msg_list => FND_API.G_FALSE
2362 ,p_commit => FND_API.G_FALSE
2363 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2364 ,p_category_rec => l_act_category_rec
2365
2366 ,x_return_status => l_return_status
2367 ,x_msg_count => l_msg_count
2368 ,x_msg_data => l_msg_data
2369 );
2370
2371 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2372 ELSE
2373 commit work;
2374 END IF;
2375
2376 null;
2377
2378 ELSE
2379 END IF;
2380
2381 END Unit_Test_Act_Delete;
2382
2383
2384
2385 PROCEDURE Unit_Test_Act_Update
2386 is
2387
2388 -- local variables
2389 l_act_category_rec AMS_ACT_CATEGORIES%ROWTYPE;
2390 l_return_status VARCHAR2(1);
2391 l_msg_count NUMBER;
2392 l_msg_data VARCHAR2(200);
2393 l_act_category_id AMS_ACT_CATEGORIES.ACTIVITY_CATEGORY_ID%TYPE;
2394
2395 l_act_category_req_item_rec act_category_rec_type;
2396 l_act_cty_validate_item_rec act_category_rec_type;
2397 l_act_cty_default_item_rec act_category_rec_type;
2398 l_act_cty_validate_row_rec act_category_rec_type;
2399
2400 CURSOR C(my_act_category_id NUMBER) is
2401 SELECT *
2402 FROM AMS_ACT_CATEGORIES
2403 WHERE ACTIVITY_CATEGORY_ID = my_act_category_id;
2404
2405 BEGIN
2406
2407 -- turned on debug mode
2408 IF AMS_Category_PVT.G_DEBUG = TRUE THEN
2409
2410
2411 -- Update test case 1
2412
2413 l_act_category_id := 1234;
2414 OPEN C(l_act_category_id);
2418
2415 FETCH C INTO l_act_category_rec;
2416
2417 l_act_category_rec.ATTRIBUTE1 := 'ATTRIBUTE1 UPDATED1';
2419
2420 AMS_Category_PVT.Update_Act_Category (
2421 p_api_version => 1.0 -- p_api_version
2422 ,p_init_msg_list => FND_API.G_FALSE
2423 ,p_commit => FND_API.G_FALSE
2424 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2425 ,p_category_rec => l_act_category_rec
2426
2427 ,x_return_status => l_return_status
2428 ,x_msg_count => l_msg_count
2429 ,x_msg_data => l_msg_data
2430 );
2431
2432 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2433 THEN
2434 ELSE
2435 commit work;
2436 END IF;
2437 CLOSE C;
2438
2439 null;
2440
2441 ELSE
2442 END IF;
2443
2444 END Unit_Test_Act_Update;
2445
2446
2447 PROCEDURE Unit_Test_Act_Lock
2448 is
2449
2450 -- local variables
2451 l_act_category_rec AMS_ACT_CATEGORIES%ROWTYPE;
2452 l_return_status VARCHAR2(1);
2453 l_msg_count NUMBER;
2454 l_msg_data VARCHAR2(200);
2455 l_act_category_id AMS_ACT_CATEGORIES.ACTIVITY_CATEGORY_ID%TYPE;
2456
2457 l_act_category_req_item_rec act_category_rec_type;
2458 l_act_cty_validate_item_rec act_category_rec_type;
2459 l_act_cty_default_item_rec act_category_rec_type;
2460 l_act_cty_validate_row_rec act_category_rec_type;
2461
2462 BEGIN
2463
2464 -- turned on debug mode
2465 IF AMS_Category_PVT.G_DEBUG = TRUE THEN
2466
2467
2468 --********************************************************
2469 -- Lock test case 1
2470
2471 l_act_category_rec.activity_category_id := 1234;
2472
2473
2474 AMS_Category_PVT.Lock_Act_Category (
2475 p_api_version => 1.0 -- p_api_version
2476 ,p_init_msg_list => FND_API.G_FALSE
2477 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2478 ,p_category_rec => l_act_category_rec
2479
2480 ,x_return_status => l_return_status
2481 ,x_msg_count => l_msg_count
2482 ,x_msg_data => l_msg_data
2483 );
2484
2485 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2486 THEN
2487 --RAISE FND_API.G_EXC_ERROR;
2488 END IF;
2489
2490
2491 null;
2492
2493 ELSE
2494 END IF;
2495
2496 END Unit_Test_Act_Lock;
2497 */
2498 END AMS_Category_PVT;