4 -- NAME
1 PACKAGE BODY amv_category_pvt AS
2 /* $Header: amvvcatb.pls 120.1 2005/12/06 09:34:37 mkettle noship $ */
3 --
8 -- 07/19/1999 SLKRISHN CREATED
5 -- AMV_CATEGORY_PVT
6 --
7 -- HISTORY
9 -- 12/20/2002 Kalyan Modified pls refer bug#2626331,2720397
10 --
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMV_CATEGORY_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amvvcatb.pls';
13
14 G_RESOURCE_ID CONSTANT NUMBER := -1;
15 G_USER_ID CONSTANT NUMBER := -1;
16 G_LOGIN_USER_ID CONSTANT NUMBER := -1;
17 --
18 ----------------------------- Private Portion ---------------------------------
19 --------------------------------------------------------------------------------
20 -- We use the following private utility procedures
21 --
22 --------------------------------------------------------------------------------
23 --------------------------------------------------------------------------------
24 PROCEDURE Validate_CategoryStatus
25 (
26 x_return_status OUT NOCOPY VARCHAR2,
27 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
28 p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
29 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
30 p_parent_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
31 x_exist_flag OUT NOCOPY VARCHAR2,
32 x_category_id OUT NOCOPY NUMBER,
33 x_error_msg OUT NOCOPY VARCHAR2,
34 x_error_token OUT NOCOPY VARCHAR2
35 );
36 --
37 FUNCTION Get_CategoryId
38 (
39 p_category_name IN VARCHAR2,
40 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM
41 ) RETURN NUMBER;
42 --
43 FUNCTION Get_CategoryName
44 (
45 p_category_id IN NUMBER
46 ) RETURN VARCHAR2;
47 --
48 PROCEDURE Get_CategoryHierarchy
49 (
50 p_category_id IN NUMBER,
51 p_category_level IN NUMBER,
52 p_category_hierarchy IN OUT NOCOPY AMV_CAT_HIERARCHY_VARRAY_TYPE
53 );
54 --
55 PROCEDURE Get_CategoryParents
56 (
57 p_category_id IN NUMBER,
58 p_category_level IN NUMBER,
59 p_category_hierarchy IN OUT NOCOPY AMV_CAT_HIERARCHY_VARRAY_TYPE
60 );
61 --
62 --------------------------------------------------------------------------------
63 --
64 -- Start of comments
65 -- API name : Validate_CategoryStatus
66 -- Type : Private
67 -- Pre-reqs : None
68 -- Function : check if category (p_category_id/p_category_name) exist
69 -- return the category id if existing.
70 -- Parameters :
71 -- p_category_id IN NUMBER Optional
72 -- category id. Default = FND_API.G_MISS_NUM
73 -- p_category_name IN VARCHAR2 Optional
74 -- category name. Default = FND_API.G_MISS_CHAR
75 -- Either pass the category id (preferred) or category name
76 -- to identify the category.
77 -- p_parent_category_id IN NUMBER Optional
78 -- parent category id. Default = FND_API.G_MISS_NUM
79 -- p_parent_category_name IN VARCHAR2 Optional
80 -- parent category name. Default = FND_API.G_MISS_CHAR
81 -- Pass parent category id or name along with category name
82 -- to identify sub category
83 -- OUT : x_return_status OUT VARCHAR2
84 -- x_exist_flag OUT VARCHAR2
85 -- category existent flag
86 -- x_category_id OUT NUMBER
87 -- category id which is valid if x_exist_flag is true.
88 -- x_error_msg OUT VARCHAR2
89 -- error message
90 -- x_error_token OUT VARCHAR2
91 -- error token
92 -- Version : Current version 1.0
93 -- Previous version 1.0
94 -- Initial version 1.0
95 -- Notes :
96 -- End of comments
97 --
98 PROCEDURE Validate_CategoryStatus
99 (
100 x_return_status OUT NOCOPY VARCHAR2,
101 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
102 p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
103 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
104 p_parent_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
105 x_exist_flag OUT NOCOPY VARCHAR2,
106 x_category_id OUT NOCOPY NUMBER,
107 x_error_msg OUT NOCOPY VARCHAR2,
108 x_error_token OUT NOCOPY VARCHAR2) IS
109 --
110 l_category_id number;
111 --
112 BEGIN
113 IF (p_category_id IS NULL OR p_category_name IS NULL) THEN
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 END IF;
116 --
117 IF p_category_id <> FND_API.G_MISS_NUM THEN
118 IF p_category_name <> FND_API.G_MISS_CHAR THEN
119 -- passed both id and name. id taken by default.
120 x_error_msg:='AMV_CAT_ID_AND_NAME_PASSED';
121 END IF;
122 x_return_status := FND_API.G_RET_STS_SUCCESS;
123 IF AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) THEN
124 -- Category Id exists
125 x_exist_flag := FND_API.G_TRUE;
126 x_category_id := p_category_id;
127 x_error_msg:='AMV_CAT_ID_EXISTS';
128 x_error_token := p_category_id;
129 ELSE
130 -- Invalid category id
131 x_exist_flag := FND_API.G_FALSE;
135 END IF;
132 x_category_id := FND_API.G_MISS_NUM;
133 x_error_msg:='AMV_CAT_ID_NOT_EXIST';
134 x_error_token := p_category_id;
136 ELSE
137 IF p_category_name <> FND_API.G_MISS_CHAR THEN
138 x_return_status := FND_API.G_RET_STS_SUCCESS;
139 IF p_parent_category_id = FND_API.G_MISS_NUM OR
140 p_parent_category_id IS NULL
141 THEN
142 IF p_parent_category_name = FND_API.G_MISS_CHAR THEN
143 x_category_id :=Get_CategoryId(p_category_name);
144 IF x_category_id <> FND_API.G_MISS_NUM THEN
145 x_exist_flag := FND_API.G_TRUE;
146 x_error_msg:='AMV_CAT_NAME_EXISTS';
147 x_error_token := p_category_name;
148 ELSE
149 -- Invalid category name
150 x_exist_flag := FND_API.G_FALSE;
151 x_error_msg:='AMV_CAT_NAME_NOT_EXIST';
152 x_error_token := p_category_name;
153 END IF;
154 ELSE
155 -- validation for sub category
156 l_category_id := Get_CategoryId(p_parent_category_name);
157 IF l_category_id <> FND_API.G_MISS_NUM THEN
158 x_category_id :=Get_CategoryId(p_category_name,
159 l_category_id);
160 IF x_category_id <> FND_API.G_MISS_NUM THEN
161 x_exist_flag := FND_API.G_TRUE;
162 x_error_msg :='AMV_CAT_NAME_EXISTS';
163 x_error_token := p_category_name;
164 ELSE
165 -- Invalid subcategory name
166 x_exist_flag := FND_API.G_FALSE;
167 x_error_msg:='AMV_CAT_NAME_NOT_EXIST';
168 x_error_token := p_category_name;
169 END IF;
170 ELSE
171 -- Invalid parent name for subcategory
172 x_exist_flag := FND_API.G_FALSE;
173 x_error_msg := 'AMV_CAT_NAME_NOT_EXIST';
174 x_error_token := p_parent_category_name;
175 END IF;
176 END IF;
177 ELSE
178 -- validation for sub category
179 IF p_parent_category_name <> FND_API.G_MISS_CHAR THEN
180 -- passed both id and name. id taken by default.
181 x_error_msg := 'AMV_CAT_ID_AND_NAME_PASSED';
182 END IF;
183 -- check if parent category id is valid
184 IF AMV_UTILITY_PVT.Is_CategoryIdValid(p_parent_category_id) THEN
185 x_category_id :=Get_CategoryId( p_category_name,
186 p_parent_category_id);
187 IF x_category_id <> FND_API.G_MISS_NUM THEN
188 x_exist_flag := FND_API.G_TRUE;
189 x_error_msg := 'AMV_CAT_NAME_EXISTS';
190 x_error_token := p_category_name;
191 ELSE
192 -- Invalid subcategory name
193 x_exist_flag := FND_API.G_FALSE;
194 x_error_msg := 'AMV_CAT_NAME_NOT_EXIST';
195 x_error_token := p_category_name;
196 END IF;
197 ELSE
198 -- Invalid parent category id
199 x_exist_flag := FND_API.G_FALSE;
200 x_error_msg := 'AMV_CAT_ID_NOT_EXIST';
201 x_error_token := p_parent_category_id;
202 END IF;
203 END IF;
204 ELSE
205 -- 'Must pass either category id or category name to identify'
206 RAISE FND_API.G_EXC_ERROR;
207 END IF;
208 END IF;
209 --
210 EXCEPTION
211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
213 x_exist_flag := FND_API.G_FALSE;
214 x_category_id := FND_API.G_MISS_NUM;
215 x_error_msg := 'AMV_CAT_ID_OR_NAME_NULL';
216 x_error_token := null;
217 WHEN FND_API.G_EXC_ERROR THEN
218 x_return_status := FND_API.G_RET_STS_ERROR;
219 x_exist_flag := FND_API.G_FALSE;
220 x_category_id := FND_API.G_MISS_NUM;
221 x_error_msg := 'AMV_CAT_ID_AND_NAME_MISS';
222 x_error_token := null;
223 WHEN OTHERS THEN
224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
225 x_exist_flag := FND_API.G_FALSE;
226 x_category_id := FND_API.G_MISS_NUM;
227 x_error_msg := 'AMV_CAT_VALIDATION_FAILED';
228 x_error_token := substrb(sqlerrm, 1, 80);
229 END Validate_CategoryStatus;
230 --
231 --------------------------------------------------------------------------------
232 --
233 -- Start of comments
234 -- API name : Get_CategoryId
235 -- Type : Private
236 -- Pre-reqs : None
237 -- Function : returns category id for a category or subcategory name.
238 -- Parameters :
239 -- IN : p_category_name IN VARCHAR2 Required
240 -- (sub)category id
241 -- p_parent_category_id IN NUMBER Optional
242 -- Default = FND_API.G_MISS_NUM
243 -- parent category id
244 -- OUT : None
248 -- Notes :
245 -- Version : Current version 1.0
246 -- Previous version 1.0
247 -- Initial version 1.0
249 -- End of comments
250 --
251 FUNCTION Get_CategoryId
252 (
253 p_category_name IN VARCHAR2,
254 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM
255 ) RETURN NUMBER
256 IS
257 l_category_id number;
258 --
259 CURSOR Get_CategoryId is
260 select b.channel_category_id
261 from amv_c_categories_b b, amv_c_categories_tl tl
262 where tl.channel_category_name = p_category_name
263 and tl.language = userenv('lang')
264 and tl.channel_category_id = b.channel_category_id
265 and b.parent_channel_category_id is null;
266 --
267 CURSOR Get_SubCategoryId is
268 select b.channel_category_id
269 from amv_c_categories_b b, amv_c_categories_tl tl
270 where tl.channel_category_name = p_category_name
271 and tl.language = userenv('lang')
272 and tl.channel_category_id = b.channel_category_id
273 and b.parent_channel_category_id = p_parent_category_id;
274 --
275 BEGIN
276 IF p_category_name = FND_API.G_MISS_CHAR OR
277 p_category_name IS NULL
278 THEN
279 l_category_id := FND_API.G_MISS_NUM;
280 RETURN l_category_id;
281 END IF;
282 --
283 IF p_parent_category_id = FND_API.G_MISS_NUM OR
284 p_parent_category_id IS NULL THEN
285 -- get category id
286 OPEN Get_CategoryId;
287 FETCH Get_CategoryId INTO l_category_id;
288 IF Get_CategoryId%NOTFOUND THEN
289 l_category_id := FND_API.G_MISS_NUM;
290 END IF;
291 CLOSE Get_CategoryId;
292 ELSE
293 -- get sub category id by catgeory id
294 OPEN Get_SubCategoryId;
295 FETCH Get_SubCategoryId INTO l_category_id;
296 IF Get_SubCategoryId%NOTFOUND THEN
300 END IF;
297 l_category_id := FND_API.G_MISS_NUM;
298 END IF;
299 CLOSE Get_SubCategoryId;
301 --
302 RETURN l_category_id;
303 --
304 END Get_CategoryId;
305 --
306 --------------------------------------------------------------------------------
307 --
308 -- Start of comments
309 -- API name : Get_CategoryName
310 -- Type : Private
311 -- Pre-reqs : None
312 -- Function : returns category name for a category id.
313 -- Parameters :
314 -- IN : p_category_id IN NUMBER Required
315 -- (sub)category id
316 -- OUT : None
317 -- Version : Current version 1.0
318 -- Previous version 1.0
319 -- Initial version 1.0
320 -- Notes :
321 -- End of comments
322 --
323 FUNCTION Get_CategoryName
324 (
325 p_category_id IN NUMBER
326 )
327 RETURN VARCHAR2
328 IS
329 l_category_name varchar2(80);
330 --
331 CURSOR Get_CategoryName IS
332 select channel_category_name
333 from amv_c_categories_tl
334 where channel_category_id = p_category_id
335 and language = userenv('lang');
336 BEGIN
337 IF p_category_id = FND_API.G_MISS_NUM OR
338 p_category_id IS NULL THEN
339 l_category_name := FND_API.G_MISS_CHAR;
340 RETURN l_category_name;
341 END IF;
342 --
343 OPEN Get_CategoryName;
344 FETCH Get_CategoryName INTO l_category_name;
345 IF Get_CategoryName%NOTFOUND THEN
346 l_category_name := FND_API.G_MISS_CHAR;
347 END IF;
348 CLOSE Get_CategoryName;
349 --
350 RETURN l_category_name;
351 --
352 END Get_CategoryName;
353 --
354 --------------------------------------------------------------------------------
355 --
356 -- Start of comments
357 -- API name : Get_CategoryHierarchy
358 -- Type : Private
359 -- Pre-reqs : None
360 -- Function : returns the sub category hierarchy for a category id.
361 -- Parameters :
362 -- IN : p_category_id IN NUMBER Required
363 -- (sub)category id
364 -- p_category_level IN NUMBER Required
365 -- category hierarchy level
366 -- p_category_hierarchy IN OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
367 -- Required,
368 -- array of category id and level
369 -- OUT : None
370 -- Version : Current version 1.0
371 -- Previous version 1.0
372 -- Initial version 1.0
373 -- Notes :
374 -- End of comments
375 --
376 PROCEDURE Get_CategoryHierarchy
377 (
378 p_category_id IN NUMBER,
379 p_category_level IN NUMBER,
380 p_category_hierarchy IN OUT NOCOPY AMV_CAT_HIERARCHY_VARRAY_TYPE
381 )
382 IS
383 --
384 l_cat_id number := p_category_id;
385 l_cat_hr number := p_category_level;
386 l_counter number ;
387 l_cat_name varchar2(80);
388 l_temp_cat_name varchar2(80);
389 --
390 cursor scat_id is
391 select A.channel_category_id, channel_category_name
392 from amv_c_categories_b A, amv_c_categories_tl B
393 where parent_channel_category_id = l_cat_id
394 and A.channel_category_id = B.channel_category_id
395 and B.language = USERENV('LANG')
396 order by channel_category_name;
397 --
398 BEGIN
399
400 l_counter := p_category_hierarchy.count + 1;
401 l_cat_name := Get_CategoryName(l_cat_id);
402 p_category_hierarchy.extend;
403 p_category_hierarchy(l_counter).hierarchy_level := l_cat_hr;
404 p_category_hierarchy(l_counter).id := l_cat_id;
405 p_category_hierarchy(l_counter).name := l_cat_name;
406 /*
407 p_category_hierarchy(l_counter):=amv_cat_hierarchy_obj_type(l_cat_hr,
408 l_cat_id,
409 l_cat_name);
410 */
411 l_cat_hr := l_cat_hr + 1;
412
413 if AMV_UTILITY_PVT.Is_CategoryIdValid(l_cat_id) then
414 open scat_id;
415 loop
416 fetch scat_id into l_cat_id, l_temp_cat_name;
417 exit when scat_id%notfound;
418 Get_CategoryHierarchy(l_cat_id, l_cat_hr, p_category_hierarchy);
419 end loop;
420 close scat_id;
421 end if;
422
423 END Get_CategoryHierarchy;
424 --
428 -- API name : Get_CategoryParents
425 --------------------------------------------------------------------------------
426 --
427 -- Start of comments
429 -- Type : Private
430 -- Pre-reqs : None
431 -- Function : returns the sub category hierarchy for a category id.
432 -- Parameters :
433 -- IN : p_category_id IN NUMBER Required
434 -- (sub)category id
435 -- p_category_level IN NUMBER Required
436 -- category hierarchy level
437 -- p_category_hierarchy IN OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
438 -- Required,
439 -- array of category id and level
440 -- OUT : None
441 -- Version : Current version 1.0
442 -- Previous version 1.0
443 -- Initial version 1.0
444 -- Notes :
445 -- End of comments
446 --
447 PROCEDURE Get_CategoryParents
448 (
449 p_category_id IN NUMBER,
450 p_category_level IN NUMBER,
451 p_category_hierarchy IN OUT NOCOPY AMV_CAT_HIERARCHY_VARRAY_TYPE
452 )
453 IS
454 --
455 l_cat_id number := p_category_id;
456 l_cat_hr number := p_category_level;
457 l_cat_name varchar2(80);
458 --
459 cursor get_parent is
460 select parent_channel_category_id
461 from amv_c_categories_b
462 where channel_category_id = l_cat_id;
463 --
464 BEGIN
465
466 l_cat_name := Get_CategoryName(l_cat_id);
467 p_category_hierarchy.extend;
468 p_category_hierarchy(l_cat_hr).hierarchy_level := l_cat_hr;
469 p_category_hierarchy(l_cat_hr).id := l_cat_id;
470 p_category_hierarchy(l_cat_hr).name := l_cat_name;
471 /*
472 p_category_hierarchy(l_cat_hr):=amv_cat_hierarchy_obj_type(l_cat_hr,
476
473 l_cat_id,
474 l_cat_name);
475 */
477 l_cat_hr := l_cat_hr + 1;
478
479 if AMV_UTILITY_PVT.Is_CategoryIdValid(l_cat_id) then
480 open get_parent;
481 fetch get_parent into l_cat_id;
482 if l_cat_id is not null then
483 Get_CategoryParents(l_cat_id, l_cat_hr, p_category_hierarchy);
484 end if;
485 close get_parent;
486 end if;
487
488 END Get_CategoryParents;
489 --
490 --------------------------------------------------------------------------------
491 --------------------------------------------------------------------------------
492 --
493 -- Start of comments
494 -- API name : Add_Category
495 -- Type : Private
496 -- Pre-reqs : None
497 -- Function : Create channel (sub)category.
498 -- Parameters :
499 -- IN : p_api_version IN NUMBER Required
500 -- p_init_msg_list IN VARCHAR2 Optional
501 -- Default = FND_API.G_FALSE
502 -- p_commit IN VARCHAR2 Optional
503 -- Default = FND_API.G_FALSE
504 -- p_validation_level IN NUMBER Optional
505 -- Default = FND_API.G_VALID_LEVEL_FULL
506 -- p_application_id IN NUMBER Optional
507 -- Default = AMV_UTILITY_PVT.G_AMV_APP_ID (520)
508 -- p_category_name IN VARCHAR2 Required
509 -- the channel (sub)category name. Have to be unique.
510 -- p_description IN VARCHAR2 Optional
511 -- the channel (sub)category description.
512 -- p_parent_category_id IN NUMBER Optional
513 -- Default = FND_API.G_MISS_NUM
514 -- p_parent_category_name IN VARCHAR2 Optional
515 -- Default = FND_API.G_MISS_CHAR
516 -- parent id or name required for creating sub categories.
517 -- p_order IN NUMBER Optional
518 -- Default = FND_API.G_MISS_NUM
519 -- the order of this (sub)category among all the categories
520 -- OUT : x_return_status OUT VARCHAR2
521 -- x_msg_count OUT NUMBER
522 -- x_msg_data OUT VARCHAR2
523 -- x_chan_category_id OUT NUMBER
524 -- Version : Current version 1.0
525 -- Previous version 1.0
526 -- Initial version 1.0
527 -- Notes :
528 --
529 -- End of comments
530 --
531 PROCEDURE Add_Category
532 (
533 p_api_version IN NUMBER,
537 x_return_status OUT NOCOPY VARCHAR2,
534 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
535 p_commit IN VARCHAR2 := FND_API.G_FALSE,
536 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
538 x_msg_count OUT NOCOPY NUMBER,
539 x_msg_data OUT NOCOPY VARCHAR2,
540 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
541 p_application_id IN NUMBER := AMV_UTILITY_PVT.G_AMV_APP_ID,
542 p_category_name IN VARCHAR2,
543 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
544 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
545 p_parent_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
546 p_order IN NUMBER := FND_API.G_MISS_NUM,
547 x_category_id OUT NOCOPY NUMBER
548 )
549 IS
550 l_api_name CONSTANT VARCHAR2(30) := 'Add_Category';
551 l_api_version CONSTANT NUMBER := 1.0;
552 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
553 --
554 l_resource_id number;
555 l_user_id number;
556 l_login_user_id number;
557 l_login_user_status varchar2(30);
558 l_Error_Msg varchar2(2000);
559 l_Error_Token varchar2(80);
560 l_object_version_number number := 1;
561 --
562 l_row_id varchar2(30);
563 l_category_id number;
564 l_parent_category_id number;
565 l_category_exist_flag varchar2(1);
566 l_setup_result varchar2(1);
567 l_order number;
568 --
569
570 CURSOR CategoryId_Seq IS
571 SELECT amv_c_categories_b_s.nextval
572 FROM dual;
573
574 CURSOR Max_CategoryOrder IS
575 SELECT NVL(MAX(channel_category_order) + 1, 1)
576 FROM amv_c_categories_b
577 WHERE parent_channel_category_id is null
578 and application_id = p_application_id;
579
580 CURSOR Max_SubCategoryOrder IS
581 SELECT NVL(MAX(channel_category_order) + 1, 1)
582 FROM amv_c_categories_b
583 WHERE parent_channel_category_id = l_parent_category_id;
584
585 BEGIN
586 -- Standard begin of API savepoint
587 SAVEPOINT Add_Category_PVT;
588 -- Standard call to check for call compatibility.
589 IF NOT FND_API.Compatible_API_Call (
590 l_api_version,
591 p_api_version,
592 l_api_name,
593 G_PKG_NAME)
594 THEN
595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
596 END IF;
597 -- Debug Message
598 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
599 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
600 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
601 FND_MSG_PUB.Add;
602 END IF;
603 --Initialize message list if p_init_msg_list is TRUE.
604 IF FND_API.To_Boolean (p_init_msg_list) THEN
605 FND_MSG_PUB.initialize;
606 END IF;
607 -- Get the current (login) user id.
608 AMV_UTILITY_PVT.Get_UserInfo(
609 x_resource_id => l_resource_id,
610 x_user_id => l_user_id,
611 x_login_id => l_login_user_id,
612 x_user_status => l_login_user_status
613 );
614 -- check login user
615 IF (p_check_login_user = FND_API.G_TRUE) THEN
616 -- Check if user is login and has the required privilege.
617 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
618 -- User is not login.
619 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
620 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
621 FND_MSG_PUB.Add;
622 END IF;
623 RAISE FND_API.G_EXC_ERROR;
624 END IF;
625 END IF;
629 l_user_id := g_user_id;
626 -- This fix is for executing api in sqlplus mode
627 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
628 l_login_user_id := g_login_user_id;
630 l_resource_id := g_resource_id;
631 END IF;
632 -- Initialize API return status to sucess
633 x_return_status := FND_API.G_RET_STS_SUCCESS;
634
635 -- check if the parent category id and name are valid
636 Validate_CategoryStatus(
637 x_return_status => x_return_status,
638 p_category_id => p_parent_category_id,
639 p_category_name => p_parent_category_name,
640 x_exist_flag => l_category_exist_flag,
641 x_category_id => l_parent_category_id,
642 x_error_msg => l_Error_Msg,
643 x_error_token => l_Error_Token
644 );
645 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
646 IF (l_category_exist_flag = FND_API.G_FALSE) THEN
647 -- parent id or name is not valid
651 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
648 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
649 THEN
650 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
652 FND_MSG_PUB.Add;
653 END IF;
654 RAISE FND_API.G_EXC_ERROR;
655 END IF;
656 ELSE
657 -- parent id or name is valid
658 x_return_status := FND_API.G_RET_STS_SUCCESS;
659 END IF;
660
661 -- check users privilege to create category
662 AMV_USER_PVT.Can_SetupCategory(
663 p_api_version => l_api_version,
664 p_init_msg_list => FND_API.G_FALSE,
665 p_validation_level => p_validation_level,
666 x_return_status => x_return_status,
667 x_msg_count => x_msg_count,
668 x_msg_data => x_msg_data,
669 p_check_login_user => FND_API.G_FALSE,
670 p_resource_id => l_resource_id,
671 p_include_group_flag => FND_API.G_TRUE,
672 x_result_flag => l_setup_result
673 );
674
675 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
676 IF l_setup_result = FND_API.G_TRUE THEN
677 -- check if the (sub)category name exists
678 Validate_CategoryStatus (
679 x_return_status => x_return_status,
680 p_category_name => p_category_name,
681 p_parent_category_id => l_parent_category_id,
682 x_exist_flag => l_category_exist_flag,
683 x_category_id => l_category_id,
684 x_error_msg => l_Error_Msg,
685 x_error_token => l_Error_Token
686 );
687 -- Add category if it does not exist
688 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
689 IF (l_category_exist_flag = FND_API.G_TRUE) THEN
690 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
691 THEN
692 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
693 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
694 FND_MSG_PUB.Add;
695 END IF;
696 RAISE FND_API.G_EXC_ERROR;
697 ELSE
698 -- set the category order
699 IF p_order = FND_API.G_MISS_NUM THEN
700 IF l_parent_category_id = FND_API.G_MISS_NUM THEN
701 OPEN Max_CategoryOrder;
702 FETCH Max_CategoryOrder INTO l_order;
703 CLOSE Max_CategoryOrder;
704 ELSE
705 OPEN Max_SubCategoryOrder;
706 FETCH Max_SubCategoryOrder INTO l_order;
707 CLOSE Max_SubCategoryOrder;
708 END IF;
709 ELSE
710 l_order := p_order;
711 END IF;
712 -- Set parent id to null if none
713 IF l_parent_category_id = FND_API.G_MISS_NUM THEN
714 l_parent_category_id := null;
715 END IF;
716 -- Select the channel category sequence
717 OPEN CategoryId_Seq;
718 FETCH CategoryId_Seq INTO l_category_id;
719 CLOSE CategoryId_Seq;
720 --
721 -- Create a new channel category
722 BEGIN
723 AMV_C_CATEGORIES_PKG.INSERT_ROW(
724 X_ROWID => l_row_id,
725 X_CHANNEL_CATEGORY_ID => l_category_id ,
726 X_APPLICATION_ID => p_application_id,
727 X_OBJECT_VERSION_NUMBER => l_object_version_number,
728 X_CHANNEL_CATEGORY_ORDER => l_order,
729 X_PARENT_CHANNEL_CATEGORY_ID => l_parent_category_id,
730 X_CHANNEL_COUNT => 0,
731 X_CHANNEL_CATEGORY_NAME => p_category_name,
732 X_DESCRIPTION => p_description,
733 X_CREATION_DATE => sysdate,
734 X_CREATED_BY => l_user_id,
735 X_LAST_UPDATE_DATE => sysdate,
736 X_LAST_UPDATED_BY => l_user_id,
737 X_LAST_UPDATE_LOGIN => l_login_user_id
738 );
739 EXCEPTION
740 WHEN OTHERS THEN
741 --will log the error
742 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
743 THEN
744 FND_MESSAGE.Set_Name('AMV', 'AMV_TABLE_HANDLER_ERROR');
745 FND_MESSAGE.Set_Token('ACTION', 'Adding');
746 FND_MESSAGE.Set_Token('TABLE', 'Categories');
747 FND_MSG_PUB.Add;
748 END IF;
749 RAISE FND_API.G_EXC_ERROR;
750 END;
751 --
752 -- Pass the channel category id created
758 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
753 x_category_id := l_category_id;
754 END IF;
755 ELSE
756 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
757 THEN
759 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
760 FND_MSG_PUB.Add;
761 END IF;
762 RAISE FND_API.G_EXC_ERROR;
763 END IF;
764 ELSE
765 -- user does not have privelege to create category
766 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
767 THEN
768 FND_MESSAGE.Set_Name('AMV', 'AMV_NO_ACCESS_ERROR');
769 FND_MESSAGE.Set_Token('LEVEL','Category');
770 FND_MSG_PUB.Add;
771 END IF;
772 RAISE FND_API.G_EXC_ERROR;
773 END IF;
774 ELSE
775 -- error while checking for user privilege
776 -- error in Can_SetupCategory
777 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
778 THEN
779 FND_MESSAGE.Set_Name('AMV', 'AMV_SETUP_CHECK_ERROR');
780 FND_MESSAGE.Set_Token('LEVEL','Category');
781 FND_MSG_PUB.Add;
782 END IF;
783 RAISE FND_API.G_EXC_ERROR;
784 END IF;
785 --
786
787 -- Success message
788 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
789 THEN
790 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
791 FND_MESSAGE.Set_Token('ROW', l_full_name);
792 FND_MSG_PUB.Add;
793 END IF;
794 --Standard check of commit
795 IF FND_API.To_Boolean ( p_commit ) THEN
796 COMMIT WORK;
797 END IF;
798 -- Debug Message
799 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
800 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
801 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
802 FND_MSG_PUB.Add;
803 END IF;
804 --Standard call to get message count and if count=1, get the message
805 FND_MSG_PUB.Count_And_Get (
806 p_encoded => FND_API.G_FALSE,
807 p_count => x_msg_count,
808 p_data => x_msg_data
809 );
810 EXCEPTION
811 WHEN FND_API.G_EXC_ERROR THEN
812 ROLLBACK TO Add_Category_PVT;
813 x_return_status := FND_API.G_RET_STS_ERROR;
814 -- Standard call to get message count and if count=1, get the message
815 FND_MSG_PUB.Count_And_Get (
816 p_encoded => FND_API.G_FALSE,
817 p_count => x_msg_count,
818 p_data => x_msg_data
819 );
820 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
821 ROLLBACK TO Add_Category_PVT;
822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
823 -- Standard call to get message count and if count=1, get the message
824 FND_MSG_PUB.Count_And_Get (
825 p_encoded => FND_API.G_FALSE,
826 p_count => x_msg_count,
827 p_data => x_msg_data
828 );
829 WHEN OTHERS THEN
830 ROLLBACK TO Add_Category_PVT;
831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
832 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
833 THEN
834 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
835 END IF;
836 -- Standard call to get message count and if count=1, get the message
837 FND_MSG_PUB.Count_And_Get (
838 p_encoded => FND_API.G_FALSE,
839 p_count => x_msg_count,
840 p_data => x_msg_data
841 );
842 --
843 END Add_Category;
844 --------------------------------------------------------------------------------
845 -- Start of comments
846 -- API name : Delete_Category
847 -- Type : Private
848 -- Pre-reqs : None
849 -- Function : Delete channel (sub)category given the
850 -- p_category_id(preferred) or p_category_name.
851 -- Parameters :
852 -- IN p_api_version IN NUMBER Required
853 -- p_init_msg_list IN VARCHAR2 Optional
854 -- Default = FND_API.G_FALSE
855 -- p_commit IN VARCHAR2 Optional
856 -- Default = FND_API.G_FALSE
857 -- p_validation_level IN NUMBER Optional
858 -- Default = FND_API.G_VALID_LEVEL_FULL
859 -- p_category_id IN NUMBER Optional
860 -- Default = FND_API.G_MISS_NUM
864 -- channel (sub)category name.
861 -- channel (sub)category id.
862 -- p_category_name IN VARCHAR2 Optional
863 -- Default = FND_API.G_MISS_CHAR
865 -- p_parent_category_id IN NUMBER Optional
866 -- Default = FND_API.G_MISS_NUM
867 -- p_parent_category_name IN VARCHAR2 Optional
868 -- Default = FND_API.G_MISS_CHAR
869 -- Either pass the channel (sub)category id (preferred)
870 -- or channel (sub)category name
871 -- to identify the channel (sub)category.
872 -- OUT : x_return_status OUT VARCHAR2
873 -- x_msg_count OUT NUMBER
874 -- x_msg_data OUT VARCHAR2
875 -- Version : Current version 1.0
876 -- Previous version 1.0
877 -- Initial version 1.0
878 -- Notes :
879 --
880 -- End of comments
881 --
882 PROCEDURE Delete_Category
883 ( p_api_version IN NUMBER,
884 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
885 p_commit IN VARCHAR2 := FND_API.G_FALSE,
886 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
887 x_return_status OUT NOCOPY VARCHAR2,
888 x_msg_count OUT NOCOPY NUMBER,
889 x_msg_data OUT NOCOPY VARCHAR2,
890 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
891 p_application_id IN NUMBER := AMV_UTILITY_PVT.G_AMV_APP_ID,
892 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
893 p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
894 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
895 p_parent_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR
896 )
897 IS
898 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Category';
899 l_api_version CONSTANT NUMBER := 1.0;
903 l_user_id number;
900 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
901 --
902 l_resource_id number;
904 l_login_user_id number;
905 l_login_user_status varchar2(30);
906 l_Error_Msg varchar2(2000);
907 l_Error_Token varchar2(80);
908 l_object_version_number number;
909 l_application_id number;
910 --
911 l_category_id number;
912 l_subcategory_id number;
913 l_category_exist_flag varchar2(1);
914 l_channel_id number;
915 l_category_level number := 1;
916 l_category_hr amv_cat_hierarchy_varray_type;
917 l_setup_result varchar2(1);
918 l_delete_category_flag varchar2(1);
919 --
920
921 CURSOR Get_CategoryChannels IS
922 select channel_id
923 from amv_c_channels_b
924 where channel_category_id = l_category_id;
925 --
926 BEGIN
927 -- Standard begin of API savepoint
928 SAVEPOINT Delete_Category_PVT;
929 -- Standard call to check for call compatibility.
930 IF NOT FND_API.Compatible_API_Call (
931 l_api_version,
932 p_api_version,
933 l_api_name,
934 G_PKG_NAME)
935 THEN
936 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
937 END IF;
938 -- Debug Message
939 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
940 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
941 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
942 FND_MSG_PUB.Add;
943 END IF;
944 --Initialize message list if p_init_msg_list is TRUE.
945 IF FND_API.To_Boolean (p_init_msg_list) THEN
946 FND_MSG_PUB.initialize;
947 END IF;
948 -- Get the current (login) user id.
949 AMV_UTILITY_PVT.Get_UserInfo(
950 x_resource_id => l_resource_id,
951 x_user_id => l_user_id,
952 x_login_id => l_login_user_id,
953 x_user_status => l_login_user_status
954 );
955 -- check login user
956 IF (p_check_login_user = FND_API.G_TRUE) THEN
957 -- Check if user is login and has the required privilege.
958 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
959 -- User is not login.
960 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
961 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
962 FND_MSG_PUB.Add;
963 END IF;
964 RAISE FND_API.G_EXC_ERROR;
965 END IF;
966 END IF;
967 -- This fix is for executing api in sqlplus mode
968 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
969 l_login_user_id := g_login_user_id;
970 l_user_id := g_user_id;
974 x_return_status := FND_API.G_RET_STS_SUCCESS;
971 l_resource_id := g_resource_id;
972 END IF;
973 -- Initialize API return status to sucess
975
976 --
977 -- Check channel id and status for a given channel id or channel name
978 Validate_CategoryStatus (
979 x_return_status => x_return_status,
980 p_category_id => p_category_id,
981 p_category_name => p_category_name,
982 p_parent_category_id => p_parent_category_id,
983 p_parent_category_name => p_parent_category_name,
984 x_exist_flag => l_category_exist_flag,
985 x_category_id => l_category_id,
986 x_error_msg => l_Error_Msg,
987 x_error_token => l_Error_Token
988 );
989 -- check if channel exists
990 IF (l_category_exist_flag = FND_API.G_FALSE) THEN
991 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
992 THEN
993 FND_MESSAGE.Set_Name('AMV', 'l_Error_Msg');
994 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
995 FND_MSG_PUB.Add;
996 END IF;
997 RAISE FND_API.G_EXC_ERROR;
998 ELSE
999 -- check if the user has privilege to delete category
1000 --
1001 AMV_USER_PVT.Can_SetupCategory (
1002 p_api_version => l_api_version,
1003 p_init_msg_list => FND_API.G_FALSE,
1004 p_validation_level => p_validation_level,
1005 x_return_status => x_return_status,
1006 x_msg_count => x_msg_count,
1007 x_msg_data => x_msg_data,
1008 p_check_login_user => FND_API.G_FALSE,
1009 p_resource_id => l_resource_id,
1010 p_include_group_flag => FND_API.G_TRUE,
1011 x_result_flag => l_setup_result
1012 );
1013
1014 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1015 IF (l_setup_result = FND_API.G_TRUE) THEN
1016 l_delete_category_flag := FND_API.G_TRUE;
1017 ELSE
1018 IF (AMV_UTILITY_PVT.Get_DeleteCategoryStatus(
1019 l_category_id,
1020 l_resource_id,
1021 AMV_UTILITY_PVT.G_USER) )
1022 THEN
1023 l_delete_category_flag := FND_API.G_TRUE;
1024 ELSE
1025 -- user does not have privilege to create category
1026 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1027 THEN
1028 FND_MESSAGE.Set_Name('AMV', 'AMV_NO_ACCESS_ERROR');
1029 FND_MESSAGE.Set_Token('LEVEL','Category');
1030 FND_MSG_PUB.Add;
1031 END IF;
1032 RAISE FND_API.G_EXC_ERROR;
1033 END IF;
1034 END IF;
1035 ELSE
1036 -- error while user privilege check in Can_SetupCategory
1037 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1038 THEN
1039 FND_MESSAGE.Set_Name('AMV', 'AMV_SETUP_CHECK_ERROR');
1040 FND_MESSAGE.Set_Token('LEVEL','Category');
1041 FND_MSG_PUB.Add;
1042 END IF;
1043 RAISE FND_API.G_EXC_ERROR;
1044 END IF;
1045 END IF;
1046 --
1047
1048 --
1049 IF l_delete_category_flag = FND_API.G_TRUE THEN
1050 l_category_hr := amv_cat_hierarchy_varray_type();
1051
1052 Get_CategoryHierarchy(l_category_id,l_category_level,l_category_hr);
1053
1054 FOR i IN REVERSE 1..l_category_hr.count LOOP
1055 OPEN Get_CategoryChannels;
1056 LOOP
1057 FETCH Get_CategoryChannels INTO l_channel_id;
1058 EXIT WHEN Get_CategoryChannels%NOTFOUND;
1059
1060 -- Remove channel from mychannels
1061 DELETE FROM amv_u_my_channels
1062 WHERE subscribing_to_id = l_channel_id
1063 AND subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL;
1064
1065 -- Remove access given to this channel
1066 DELETE FROM amv_u_access
1067 WHERE access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
1068 AND access_to_table_record_id = l_channel_id;
1069
1070 -- Remove channel from authors
1071 DELETE FROM amv_c_authors
1072 WHERE channel_id = l_channel_id;
1073 -- Remove channel from keywords
1074 DELETE FROM amv_c_keywords
1075 WHERE channel_id = l_channel_id;
1076 -- Remove channel from content types
1077 DELETE FROM amv_c_content_types
1078 WHERE channel_id = l_channel_id;
1079 -- Remove channel from perspectives
1080 DELETE FROM amv_c_chl_perspectives
1081 WHERE channel_id = l_channel_id;
1082 -- Remove channel from item types
1083 DELETE FROM amv_c_item_types
1084 WHERE channel_id = l_channel_id;
1085
1086 -- Remove channels
1090 -- Remove channel item matches
1087 AMV_C_CHANNELS_PKG.DELETE_ROW( l_channel_id);
1088 END LOOP;
1089 CLOSE Get_CategoryChannels;
1091 DELETE FROM amv_c_chl_item_match
1092 WHERE channel_category_id = l_category_id;
1093
1094 -- Remove category from my channels
1095 DELETE FROM amv_u_my_channels
1096 --WHERE subscribing_to_id = l_category_id pls refer the bug# 2626331,2720397
1097 WHERE subscribing_to_id = l_category_hr(i).id
1098 AND subscribing_to_type = AMV_UTILITY_PVT.G_CATEGORY;
1099
1100 -- Remove channel category
1101 AMV_C_CATEGORIES_PKG.DELETE_ROW(l_category_hr(i).id);
1102 END LOOP;
1103 END IF;
1104 --
1105
1106 -- Success message
1107 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1108 THEN
1109 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1110 FND_MESSAGE.Set_Token('ROW', l_full_name);
1111 FND_MSG_PUB.Add;
1112 END IF;
1113 --Standard check of commit
1114 IF FND_API.To_Boolean ( p_commit ) THEN
1115 COMMIT WORK;
1116 END IF;
1117 -- Debug Message
1118 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1119 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1120 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1121 FND_MSG_PUB.Add;
1122 END IF;
1123 --Standard call to get message count and if count=1, get the message
1124 FND_MSG_PUB.Count_And_Get (
1125 p_encoded => FND_API.G_FALSE,
1126 p_count => x_msg_count,
1127 p_data => x_msg_data
1128 );
1129 EXCEPTION
1130 WHEN FND_API.G_EXC_ERROR THEN
1131 ROLLBACK TO Delete_Category_PVT;
1132 x_return_status := FND_API.G_RET_STS_ERROR;
1133 -- Standard call to get message count and if count=1, get the message
1134 FND_MSG_PUB.Count_And_Get (
1135 p_encoded => FND_API.G_FALSE,
1136 p_count => x_msg_count,
1137 p_data => x_msg_data
1138 );
1139 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1140 ROLLBACK TO Delete_Category_PVT;
1141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1142 -- Standard call to get message count and if count=1, get the message
1143 FND_MSG_PUB.Count_And_Get (
1144 p_encoded => FND_API.G_FALSE,
1145 p_count => x_msg_count,
1146 p_data => x_msg_data
1147 );
1148 WHEN OTHERS THEN
1149 ROLLBACK TO Delete_Category_PVT;
1150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1151 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1152 THEN
1153 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1154 END IF;
1155 -- Standard call to get message count and if count=1, get the message
1156 FND_MSG_PUB.Count_And_Get (
1157 p_encoded => FND_API.G_FALSE,
1158 p_count => x_msg_count,
1159 p_data => x_msg_data
1160 );
1161 --
1162 END Delete_Category;
1163 --------------------------------------------------------------------------------
1164 -- Start of comments
1165 -- API name : Sort_Category
1166 -- Type : Private
1167 -- Pre-reqs : None
1168 -- Function : sort (sub)category list in ascending or descending order
1169 -- Parameters :
1170 -- IN p_api_version IN NUMBER Required
1171 -- p_init_msg_list IN VARCHAR2 Optional
1172 -- Default = FND_API.G_FALSE
1173 -- p_commit IN VARCHAR2 Optional
1174 -- Default = FND_API.G_FALSE
1175 -- p_validation_level IN NUMBER Optional
1176 -- Default = FND_API.G_VALID_LEVEL_FULL
1177 -- p_sort_order IN VARCHAR2 Optional
1178 -- Default = AMV_CATEGORY_PVT.G_ASC_ORDER
1179 -- Ascending(ASC) or Descending(DESC) Order.
1180 -- p_parent_category_id IN NUMBER Optional
1181 -- Default = FND_API.G_MISS_NUM
1182 -- parent id for sub categories
1183 -- p_parent_category_name IN VARCHAR2 Optional
1184 -- Default = FND_API.G_MISS_CHAR
1185 -- category name for sub categories
1186 -- category name or parent category id should be
1187 -- supplied for sorting sub categories
1188 -- OUT : x_return_status OUT VARCHAR2
1189 -- x_msg_count OUT NUMBER
1193 -- Initial version 1.0
1190 -- x_msg_data OUT VARCHAR2
1191 -- Version : Current version 1.0
1192 -- Previous version 1.0
1194 -- Notes :
1195 --
1196 -- End of comments
1197 --
1198 PROCEDURE Sort_Category
1199 ( p_api_version IN NUMBER,
1200 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1201 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1202 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1203 x_return_status OUT NOCOPY VARCHAR2,
1204 x_msg_count OUT NOCOPY NUMBER,
1205 x_msg_data OUT NOCOPY VARCHAR2,
1206 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1207 p_application_id IN NUMBER := AMV_UTILITY_PVT.G_AMV_APP_ID,
1208 p_sort_order IN VARCHAR2 := AMV_CATEGORY_PVT.G_ASC_ORDER,
1209 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
1210 p_parent_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR
1211 )
1212 IS
1213 l_api_name CONSTANT VARCHAR2(30) := 'Sort_Category';
1214 l_api_version CONSTANT NUMBER := 1.0;
1215 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1216 --
1217 l_resource_id number;
1218 l_user_id number;
1219 l_login_user_id number;
1220 l_login_user_status varchar2(30);
1221 l_Error_Msg varchar2(2000);
1222 l_Error_Token varchar2(80);
1223 l_object_version_number number;
1224 l_application_id number;
1225 --
1226 l_category_id number;
1227 l_parent_category_id number;
1228 l_category_exist_flag varchar2(1);
1229 l_order number;
1230 l_record_count number := 1;
1231 l_channel_count number;
1232 l_category_array AMV_NUMBER_VARRAY_TYPE;
1233
1234 CURSOR Get_CategoryOrder IS
1235 SELECT b.channel_category_id
1236 FROM amv_c_categories_b b, amv_c_categories_tl tl
1237 WHERE b.parent_channel_category_id is null
1238 and b.application_id = p_application_id
1239 ORDER BY tl.channel_category_name;
1240
1241 CURSOR Get_SubCategoryOrder IS
1242 SELECT b.channel_category_id
1243 FROM amv_c_categories_b b, amv_c_categories_tl tl
1244 WHERE b.parent_channel_category_id = l_parent_category_id
1245 ORDER BY tl.channel_category_name;
1246
1247 CURSOR Get_CatRec_csr IS
1248 SELECT application_id
1249 , channel_count
1250 FROM amv_c_categories_b
1251 WHERE channel_category_id = l_category_id;
1252
1253 BEGIN
1254 -- Standard begin of API savepoint
1255 SAVEPOINT Sort_Category_PVT;
1256 -- Standard call to check for call compatibility.
1257 IF NOT FND_API.Compatible_API_Call (
1258 l_api_version,
1259 p_api_version,
1260 l_api_name,
1261 G_PKG_NAME)
1262 THEN
1263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1264 END IF;
1265 -- Debug Message
1266 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1267 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1268 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1269 FND_MSG_PUB.Add;
1270 END IF;
1271 --Initialize message list if p_init_msg_list is TRUE.
1272 IF FND_API.To_Boolean (p_init_msg_list) THEN
1273 FND_MSG_PUB.initialize;
1274 END IF;
1275 -- Get the current (login) user id.
1276 AMV_UTILITY_PVT.Get_UserInfo(
1277 x_resource_id => l_resource_id,
1278 x_user_id => l_user_id,
1279 x_login_id => l_login_user_id,
1280 x_user_status => l_login_user_status
1281 );
1282 -- check login user
1283 IF (p_check_login_user = FND_API.G_TRUE) THEN
1284 -- Check if user is login and has the required privilege.
1285 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1286 -- User is not login.
1287 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1288 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1289 FND_MSG_PUB.Add;
1290 END IF;
1291 RAISE FND_API.G_EXC_ERROR;
1292 END IF;
1293 END IF;
1294 -- This fix is for executing api in sqlplus mode
1295 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1296 l_login_user_id := g_login_user_id;
1297 l_user_id := g_user_id;
1298 l_resource_id := g_resource_id;
1299 END IF;
1300 -- Initialize API return status to sucess
1301 x_return_status := FND_API.G_RET_STS_SUCCESS;
1302
1303 --
1304 Validate_CategoryStatus (
1305 x_return_status => x_return_status,
1306 p_category_id => p_parent_category_id,
1307 p_category_name => p_parent_category_name,
1308 x_exist_flag => l_category_exist_flag,
1309 x_category_id => l_parent_category_id,
1310 x_error_msg => l_Error_Msg,
1311 x_error_token => l_Error_Token
1312 );
1313 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1314 IF (l_category_exist_flag = FND_API.G_FALSE) THEN
1315 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1316 THEN
1317 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
1318 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
1319 FND_MSG_PUB.Add;
1320 END IF;
1321 RAISE FND_API.G_EXC_ERROR;
1322 END IF;
1323 ELSE
1324 x_return_status := FND_API.G_RET_STS_SUCCESS;
1325 END IF;
1326
1330 LOOP
1327 IF l_parent_category_id = FND_API.G_MISS_NUM THEN
1328 OPEN Get_CategoryOrder;
1329 l_category_array := AMV_NUMBER_VARRAY_TYPE();
1331 FETCH Get_CategoryOrder INTO l_category_id;
1332 EXIT WHEN Get_CategoryOrder%NOTFOUND;
1333 l_category_array.extend;
1334 l_category_array(l_record_count) := l_category_id;
1335 l_record_count := l_record_count + 1;
1336 END LOOP;
1337 CLOSE Get_CategoryOrder;
1338 l_parent_category_id := null;
1339 ELSE
1340 OPEN Get_SubCategoryOrder;
1341 l_category_array := AMV_NUMBER_VARRAY_TYPE();
1342 LOOP
1343 FETCH Get_SubCategoryOrder INTO l_category_id;
1344 EXIT WHEN Get_SubCategoryOrder%NOTFOUND;
1345 l_category_array.extend;
1346 l_category_array(l_record_count) := l_category_id;
1347 l_record_count := l_record_count + 1;
1348 END LOOP;
1349 CLOSE Get_SubCategoryOrder;
1350 END IF;
1351
1352 -- update (sub)category order
1353 FOR i IN 1..l_category_array.count LOOP
1354 l_category_id := l_category_array(i);
1355 -- fetch item count for category being updated
1356 OPEN Get_CatRec_csr;
1357 FETCH Get_CatRec_csr INTO l_application_id, l_channel_count;
1358 CLOSE Get_CatRec_csr;
1359 -- set the category order
1360 IF p_sort_order = G_ASC_ORDER THEN
1361 l_order := i;
1362 ELSE
1363 l_order := l_category_array.count - i + 1;
1364 END IF;
1365
1366 BEGIN
1367 AMV_C_CATEGORIES_PKG.UPDATE_B_ROW(
1368 X_CHANNEL_CATEGORY_ID => l_category_id,
1369 X_APPLICATION_ID => l_application_id,
1370 X_OBJECT_VERSION_NUMBER => l_object_version_number,
1371 X_CHANNEL_CATEGORY_ORDER => l_order,
1372 X_PARENT_CHANNEL_CATEGORY_ID => l_parent_category_id,
1373 X_CHANNEL_COUNT => l_channel_count,
1374 X_LAST_UPDATE_DATE => sysdate,
1375 X_LAST_UPDATED_BY => l_user_id,
1376 X_LAST_UPDATE_LOGIN => l_login_user_id
1377 );
1378 EXCEPTION
1379 WHEN OTHERS THEN
1380 --will log the error
1381 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1382 THEN
1383 FND_MESSAGE.Set_Name('AMV', 'AMV_TABLE_HANDLER_ERROR');
1384 FND_MESSAGE.Set_Token('ACTION', 'Sorting');
1385 FND_MESSAGE.Set_Token('TABLE', 'Categories');
1386 FND_MSG_PUB.Add;
1387 END IF;
1388 RAISE FND_API.G_EXC_ERROR;
1389 END;
1390 END LOOP;
1391 --
1392
1393 -- Success message
1394 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1395 THEN
1396 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1397 FND_MESSAGE.Set_Token('ROW', l_full_name);
1398 FND_MSG_PUB.Add;
1399 END IF;
1400 --Standard check of commit
1401 IF FND_API.To_Boolean ( p_commit ) THEN
1402 COMMIT WORK;
1403 END IF;
1404 -- Debug Message
1405 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1406 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1407 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1408 FND_MSG_PUB.Add;
1409 END IF;
1410 --Standard call to get message count and if count=1, get the message
1411 FND_MSG_PUB.Count_And_Get (
1412 p_encoded => FND_API.G_FALSE,
1413 p_count => x_msg_count,
1414 p_data => x_msg_data
1415 );
1416 EXCEPTION
1417 WHEN FND_API.G_EXC_ERROR THEN
1418 ROLLBACK TO Sort_Category_PVT;
1419 x_return_status := FND_API.G_RET_STS_ERROR;
1420 -- Standard call to get message count and if count=1, get the message
1421 FND_MSG_PUB.Count_And_Get (
1422 p_encoded => FND_API.G_FALSE,
1423 p_count => x_msg_count,
1424 p_data => x_msg_data
1425 );
1426 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1427 ROLLBACK TO Sort_Category_PVT;
1428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1429 -- Standard call to get message count and if count=1, get the message
1430 FND_MSG_PUB.Count_And_Get (
1431 p_encoded => FND_API.G_FALSE,
1432 p_count => x_msg_count,
1433 p_data => x_msg_data
1434 );
1435 WHEN OTHERS THEN
1436 ROLLBACK TO Sort_Category_PVT;
1437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1438 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1439 THEN
1440 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1441 END IF;
1442 -- Standard call to get message count and if count=1, get the message
1443 FND_MSG_PUB.Count_And_Get (
1444 p_encoded => FND_API.G_FALSE,
1445 p_count => x_msg_count,
1446 p_data => x_msg_data
1447 );
1448 END Sort_Category;
1449 --
1450 --------------------------------------------------------------------------------
1451 -- Start of comments
1452 -- API name : Reorder_Category
1453 -- Type : Private
1454 -- Pre-reqs : None
1455 -- Function : Reorder channel (sub)category list
1456 -- Parameters :
1457 -- IN p_api_version IN NUMBER Required
1458 -- p_init_msg_list IN VARCHAR2 Optional
1459 -- Default = FND_API.G_FALSE
1463 -- Default = FND_API.G_VALID_LEVEL_FULL
1460 -- p_commit IN VARCHAR2 Optional
1461 -- Default = FND_API.G_FALSE
1462 -- p_validation_level NUMBER Optional
1464 -- p_category_id_array IN AMV_NUMBER_VARRAY_TYPE
1465 -- Required
1466 -- p_category_new_order IN AMV_NUMBER_VARRAY_TYPE
1467 -- Required
1468 -- OUT : x_return_status OUT VARCHAR2
1469 -- x_msg_count OUT NUMBER
1470 -- x_msg_data OUT VARCHAR2
1471 -- Version : Current version 1.0
1472 -- Previous version 1.0
1473 -- Initial version 1.0
1474 -- Notes :
1475 --
1476 -- End of comments
1477 --
1478 PROCEDURE Reorder_Category
1479 ( p_api_version IN NUMBER,
1480 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1481 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1482 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1483 x_return_status OUT NOCOPY VARCHAR2,
1484 x_msg_count OUT NOCOPY NUMBER,
1485 x_msg_data OUT NOCOPY VARCHAR2,
1486 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1487 p_application_id IN NUMBER := AMV_UTILITY_PVT.G_AMV_APP_ID,
1488 p_category_id_array IN AMV_NUMBER_VARRAY_TYPE,
1489 p_category_new_order IN AMV_NUMBER_VARRAY_TYPE
1490 )
1491 IS
1492 l_api_name CONSTANT VARCHAR2(30) := 'Reorder_Category';
1493 l_api_version CONSTANT NUMBER := 1.0;
1494 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1495 --
1496 l_resource_id number;
1497 l_user_id number;
1498 l_login_user_id number;
1499 l_login_user_status varchar2(30);
1500 l_Error_Msg varchar2(2000);
1504 --
1501 l_Error_Token varchar2(80);
1502 l_object_version_number number;
1503 l_application_id number;
1505 l_category_id number;
1506 l_parent_category_id number;
1507 l_channel_count number;
1508
1509 CURSOR Get_CatRec_csr IS
1510 select application_id,
1511 parent_channel_category_id,
1512 channel_count
1513 from amv_c_categories_b
1514 where channel_category_id = l_category_id;
1515 BEGIN
1516 -- Standard begin of API savepoint
1517 SAVEPOINT Reorder_Category_PVT;
1518 -- Standard call to check for call compatibility.
1519 IF NOT FND_API.Compatible_API_Call (
1520 l_api_version,
1521 p_api_version,
1522 l_api_name,
1523 G_PKG_NAME)
1524 THEN
1525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1526 END IF;
1527 -- Debug Message
1528 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1529 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1530 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1531 FND_MSG_PUB.Add;
1532 END IF;
1533 --Initialize message list if p_init_msg_list is TRUE.
1534 IF FND_API.To_Boolean (p_init_msg_list) THEN
1535 FND_MSG_PUB.initialize;
1536 END IF;
1537 -- Get the current (login) user id.
1538 AMV_UTILITY_PVT.Get_UserInfo(
1539 x_resource_id => l_resource_id,
1540 x_user_id => l_user_id,
1541 x_login_id => l_login_user_id,
1542 x_user_status => l_login_user_status
1543 );
1544 -- check login user
1545 IF (p_check_login_user = FND_API.G_TRUE) THEN
1546 -- Check if user is login and has the required privilege.
1547 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1548 -- User is not login.
1549 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1550 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1551 FND_MSG_PUB.Add;
1552 END IF;
1553 RAISE FND_API.G_EXC_ERROR;
1554 END IF;
1555 END IF;
1556 -- This fix is for executing api in sqlplus mode
1560 l_resource_id := g_resource_id;
1557 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1558 l_login_user_id := g_login_user_id;
1559 l_user_id := g_user_id;
1561 END IF;
1562 -- Initialize API return status to sucess
1563 x_return_status := FND_API.G_RET_STS_SUCCESS;
1564
1565 --
1566 -- Check if the number of categories and new order count are same
1567 IF p_category_id_array.count = p_category_new_order.count THEN
1568 FOR i in 1..p_category_id_array.count LOOP
1569 l_category_id := p_category_id_array(i);
1570 -- fetch the item count for category being updated
1571 OPEN Get_CatRec_csr;
1572 FETCH Get_CatRec_csr INTO l_application_id,
1573 l_parent_category_id,
1574 l_channel_count;
1575 CLOSE Get_CatRec_csr;
1576 -- Change the category order
1577 BEGIN
1578 AMV_C_CATEGORIES_PKG.UPDATE_B_ROW(
1579 X_CHANNEL_CATEGORY_ID => l_category_id,
1580 X_APPLICATION_ID => l_application_id,
1581 X_OBJECT_VERSION_NUMBER => l_object_version_number,
1582 X_CHANNEL_CATEGORY_ORDER => p_category_new_order(i),
1583 X_PARENT_CHANNEL_CATEGORY_ID => l_parent_category_id,
1584 X_CHANNEL_COUNT => l_channel_count,
1585 X_LAST_UPDATE_DATE => sysdate,
1586 X_LAST_UPDATED_BY => l_user_id,
1587 X_LAST_UPDATE_LOGIN => l_login_user_id
1588 );
1589 EXCEPTION
1590 WHEN OTHERS THEN
1591 --will log the error
1592 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1593 THEN
1594 FND_MESSAGE.Set_Name('AMV', 'AMV_TABLE_HANDLER_ERROR');
1595 FND_MESSAGE.Set_Token('ACTION', 'Reordering');
1596 FND_MESSAGE.Set_Token('TABLE', 'Categories');
1597 FND_MSG_PUB.Add;
1598 END IF;
1599 RAISE FND_API.G_EXC_ERROR;
1600 END;
1601 END LOOP;
1602 ELSE
1603 --Category count and new order count must be equal
1604 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1605 THEN
1606 FND_MESSAGE.Set_Name('AMV', 'AMV_CATEGORY_REORDER_ERROR');
1607 FND_MSG_PUB.Add;
1608 END IF;
1609 RAISE FND_API.G_EXC_ERROR;
1610 END IF;
1611 --
1612
1613 -- Success message
1614 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1615 THEN
1616 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1617 FND_MESSAGE.Set_Token('ROW', l_full_name);
1618 FND_MSG_PUB.Add;
1619 END IF;
1620 --Standard check of commit
1621 IF FND_API.To_Boolean ( p_commit ) THEN
1622 COMMIT WORK;
1623 END IF;
1624 -- Debug Message
1625 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1626 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1627 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1628 FND_MSG_PUB.Add;
1629 END IF;
1630 --Standard call to get message count and if count=1, get the message
1631 FND_MSG_PUB.Count_And_Get (
1632 p_encoded => FND_API.G_FALSE,
1633 p_count => x_msg_count,
1634 p_data => x_msg_data
1635 );
1636 EXCEPTION
1637 WHEN FND_API.G_EXC_ERROR THEN
1638 ROLLBACK TO Reorder_Category_PVT;
1639 x_return_status := FND_API.G_RET_STS_ERROR;
1640 -- Standard call to get message count and if count=1, get the message
1641 FND_MSG_PUB.Count_And_Get (
1642 p_encoded => FND_API.G_FALSE,
1643 p_count => x_msg_count,
1644 p_data => x_msg_data
1645 );
1646 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1647 ROLLBACK TO Reorder_Category_PVT;
1648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1649 -- Standard call to get message count and if count=1, get the message
1650 FND_MSG_PUB.Count_And_Get (
1651 p_encoded => FND_API.G_FALSE,
1652 p_count => x_msg_count,
1653 p_data => x_msg_data
1654 );
1655 WHEN OTHERS THEN
1656 ROLLBACK TO Reorder_Category_PVT;
1657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1658 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1659 THEN
1660 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1664 p_encoded => FND_API.G_FALSE,
1661 END IF;
1662 -- Standard call to get message count and if count=1, get the message
1663 FND_MSG_PUB.Count_And_Get (
1665 p_count => x_msg_count,
1666 p_data => x_msg_data
1667 );
1668 --
1669 END Reorder_Category;
1670 --------------------------------------------------------------------------------
1671 -- Start of comments
1672 -- API name : Update_Category
1673 -- Type : Private
1674 -- Pre-reqs : None
1675 -- Function : Update channel (sub)category given (sub)category id or name
1676 -- Parameters :
1677 -- IN p_api_version IN NUMBER Required
1678 -- p_init_msg_list IN VARCHAR2 Optional
1679 -- Default = FND_API.G_FALSE
1680 -- p_commit IN VARCHAR2 Optional
1681 -- Default = FND_API.G_FALSE
1682 -- p_validation_level IN NUMBER Optional
1683 -- Default = FND_API.G_VALID_LEVEL_FULL
1684 -- p_object_version_number IN NUMBER Required
1685 -- object version number
1686 -- p_category_id IN NUMBER Optional
1687 -- channel category id.
1688 -- p_category_name IN VARCHAR2 Optional
1689 -- channel category name.
1690 -- (sub)category id or name is required
1691 -- p_parent_category_id IN NUMBER Optional
1692 -- Default = FND_API.G_MISS_NUM
1693 -- channel category id.
1694 -- p_parent_category_name IN VARCHAR2 Optional
1695 -- Default = FND_API.G_MISS_CHAR
1696 -- channel category name.
1697 -- takes either parent id or name. id taken if both passed
1698 -- p_category_order IN NUMBER Optional
1699 -- new channel category order
1700 -- p_category_new_name IN VARCHAR2 Optional
1701 -- new channel category name. New name has to be unique
1702 -- p_description IN VARCHAR2 Optional
1703 -- Default = FND_API.G_MISS_CHAR
1704 -- channel category description.
1705 -- OUT : x_return_status OUT VARCHAR2
1706 -- x_msg_count OUT NUMBER
1707 -- x_msg_data OUT VARCHAR2
1708 -- Version : Current version 1.0
1709 -- Previous version 1.0
1710 -- Initial version 1.0
1711 -- Notes :
1712 --
1713 -- End of comments
1714 --
1715 PROCEDURE Update_Category
1716 ( p_api_version IN NUMBER,
1717 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1718 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1719 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1720 x_return_status OUT NOCOPY VARCHAR2,
1721 x_msg_count OUT NOCOPY NUMBER,
1722 x_msg_data OUT NOCOPY VARCHAR2,
1723 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1724 p_object_version_number IN NUMBER,
1725 p_application_id IN NUMBER := AMV_UTILITY_PVT.G_AMV_APP_ID,
1726 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
1727 p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1728 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
1729 p_parent_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1730 p_category_order IN NUMBER := FND_API.G_MISS_NUM,
1731 p_category_new_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1732 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR
1733 )
1734 IS
1735 l_api_name CONSTANT VARCHAR2(30) := 'Update_Category';
1736 l_api_version CONSTANT NUMBER := 1.0;
1737 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1738 --
1739 l_resource_id number;
1740 l_user_id number;
1741 l_login_user_id number;
1742 l_login_user_status varchar2(30);
1743 l_Error_Msg varchar2(2000);
1744 l_Error_Token varchar2(80);
1745 l_object_version_number number;
1746 l_application_id number;
1747 --
1748 l_category_id number;
1749 l_parent_category_id number;
1750 l_channel_count number;
1751 l_category_order number;
1752 l_category_exist_flag varchar2(1);
1753 l_category_current_name varchar2(80);
1754 l_new_category_id number;
1755 l_description varchar2(2000);
1756 l_setup_result varchar2(1);
1757 l_update_category_flag varchar2(1);
1758
1759 CURSOR Get_CatRec_csr IS
1760 select object_version_number,
1761 application_id,
1762 channel_category_order,
1763 parent_channel_category_id,
1764 channel_count
1765 from amv_c_categories_b
1766 where channel_category_id = l_category_id;
1767
1768 CURSOR Get_CatDesc_csr IS
1769 select description
1770 from amv_c_categories_tl
1771 where channel_category_id = l_category_id
1772 and language = userenv('lang');
1773
1774 --
1775 --
1776 BEGIN
1777 -- Standard begin of API savepoint
1778 SAVEPOINT Update_Category_PVT;
1782 p_api_version,
1779 -- Standard call to check for call compatibility.
1780 IF NOT FND_API.Compatible_API_Call (
1781 l_api_version,
1783 l_api_name,
1784 G_PKG_NAME)
1785 THEN
1786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1787 END IF;
1788 -- Debug Message
1789 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1790 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1791 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1792 FND_MSG_PUB.Add;
1793 END IF;
1794 --Initialize message list if p_init_msg_list is TRUE.
1795 IF FND_API.To_Boolean (p_init_msg_list) THEN
1796 FND_MSG_PUB.initialize;
1797 END IF;
1798 -- Get the current (login) user id.
1799 AMV_UTILITY_PVT.Get_UserInfo(
1800 x_resource_id => l_resource_id,
1801 x_user_id => l_user_id,
1802 x_login_id => l_login_user_id,
1803 x_user_status => l_login_user_status
1804 );
1805 -- check login user
1806 IF (p_check_login_user = FND_API.G_TRUE) THEN
1807 -- Check if user is login and has the required privilege.
1808 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1812 FND_MSG_PUB.Add;
1809 -- User is not login.
1810 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1811 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1813 END IF;
1814 RAISE FND_API.G_EXC_ERROR;
1815 END IF;
1816 END IF;
1817 -- This fix is for executing api in sqlplus mode
1818 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1819 l_login_user_id := g_login_user_id;
1820 l_user_id := g_user_id;
1821 l_resource_id := g_resource_id;
1822 END IF;
1823 -- Initialize API return status to sucess
1824 x_return_status := FND_API.G_RET_STS_SUCCESS;
1825
1826 -- check if the (sub)category id/name are valid and get (sub)category id
1827 Validate_CategoryStatus(
1828 x_return_status => x_return_status,
1829 p_category_id => p_category_id,
1830 p_category_name => p_category_name,
1831 p_parent_category_id => p_parent_category_id,
1832 p_parent_category_name => p_parent_category_name,
1833 x_exist_flag => l_category_exist_flag,
1834 x_category_id => l_category_id,
1835 x_error_msg => l_Error_Msg,
1836 x_error_token => l_Error_Token
1837 );
1838 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1839 IF (l_category_exist_flag = FND_API.G_FALSE) THEN
1840 -- (sub)catgeory id or name is not valid
1841 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1842 THEN
1843 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
1844 FND_MESSAGE.Set_Token('TKN', l_Error_Token);
1845 FND_MSG_PUB.Add;
1846 END IF;
1847 RAISE FND_API.G_EXC_ERROR;
1848 END IF;
1849 ELSE
1850 -- setting the flag to success for checking again
1851 -- NOTE check this flag setting again
1852 x_return_status := FND_API.G_RET_STS_SUCCESS;
1853 END IF;
1854
1855 -- get the current (sub)category name
1856 l_category_current_name := Get_CategoryName(l_category_id);
1857
1858 IF p_category_new_name <> FND_API.G_MISS_CHAR THEN
1859 -- check new name if current and new (sub)category are not same
1860 IF UPPER(p_category_new_name) <> UPPER(l_category_current_name) THEN
1861 -- get parent category id if parent name is passed
1862 IF p_parent_category_id <> FND_API.G_MISS_NUM THEN
1863 l_parent_category_id := p_parent_category_id;
1864 ELSE
1865 l_parent_category_id := Get_CategoryId(p_parent_category_name);
1866 END IF;
1867 -- check if new (sub)category name exists
1868 Validate_CategoryStatus (
1869 x_return_status => x_return_status,
1870 p_category_name => p_category_new_name,
1871 p_parent_category_id => l_parent_category_id,
1872 x_exist_flag => l_category_exist_flag,
1873 x_category_id => l_new_category_id,
1874 x_error_msg => l_Error_Msg,
1875 x_error_token => l_Error_Token
1876 );
1877
1881 END IF;
1878 l_category_current_name := p_category_new_name;
1879 ELSE
1880 l_category_exist_flag := FND_API.G_FALSE;
1882 ELSE
1883 l_category_exist_flag := FND_API.G_FALSE;
1884 END IF;
1885 --
1886
1887 -- update category if it does not exist
1888 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1889 IF (l_category_exist_flag = FND_API.G_TRUE) THEN
1890 -- (sub)category with the new name already exists
1891 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1892 THEN
1893 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
1894 FND_MESSAGE.Set_Token('TKN', l_Error_Token);
1895 FND_MSG_PUB.Add;
1896 END IF;
1897 RAISE FND_API.G_EXC_ERROR;
1898 ELSE
1899 --
1900 -- check if the user has privilege to update category
1901 AMV_USER_PVT.Can_SetupCategory (
1902 p_api_version => l_api_version,
1903 p_init_msg_list => FND_API.G_FALSE,
1904 p_validation_level => p_validation_level,
1905 x_return_status => x_return_status,
1906 x_msg_count => x_msg_count,
1907 x_msg_data => x_msg_data,
1908 p_check_login_user => FND_API.G_FALSE,
1909 p_resource_id => l_resource_id,
1910 p_include_group_flag => FND_API.G_TRUE,
1911 x_result_flag => l_setup_result
1912 );
1913
1914 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1915 IF (l_setup_result = FND_API.G_TRUE) THEN
1916 l_update_category_flag := FND_API.G_TRUE;
1917 ELSE
1918 IF (AMV_UTILITY_PVT.Get_UpdateCategoryStatus(
1919 l_category_id,
1920 l_resource_id,
1921 AMV_UTILITY_PVT.G_USER) )
1922 THEN
1923 l_update_category_flag := FND_API.G_TRUE;
1924 ELSE
1925 -- user does not have privilege to create category
1926 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1927 THEN
1928 FND_MESSAGE.Set_Name('AMV', 'AMV_NO_ACCESS_ERROR');
1929 FND_MESSAGE.Set_Token('LEVEL','Category');
1930 FND_MSG_PUB.Add;
1931 END IF;
1932 RAISE FND_API.G_EXC_ERROR;
1933 END IF;
1934 END IF;
1935 ELSE
1936 -- error while user privilege check in Can_SetupCategory
1937 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1938 THEN
1939 FND_MESSAGE.Set_Name('AMV', 'AMV_SETUP_CHECK_ERROR');
1940 FND_MESSAGE.Set_Token('LEVEL','Category');
1941 FND_MSG_PUB.Add;
1942 END IF;
1943 RAISE FND_API.G_EXC_ERROR;
1944 END IF;
1945 --
1946
1947 --
1951 FETCH Get_CatRec_csr INTO l_object_version_number,
1948 IF l_update_category_flag = FND_API.G_TRUE THEN
1949 -- get the category record in database
1950 OPEN Get_CatRec_csr;
1952 l_application_id,
1953 l_category_order,
1954 l_parent_category_id,
1955 l_channel_count;
1956 CLOSE Get_CatRec_csr;
1957
1958 OPEN Get_CatDesc_csr;
1959 FETCH Get_CatDesc_csr INTO l_description;
1960 CLOSE Get_CatDesc_csr;
1961
1962 -- check category order
1963 IF p_category_order <> FND_API.G_MISS_NUM THEN
1964 l_category_order := p_category_order;
1965 END IF;
1966
1967 -- check category description
1968 IF p_description <> FND_API.G_MISS_CHAR THEN
1969 l_description := p_description;
1970 END IF;
1971
1972 -- update if the version is greater equal to one in db
1973 IF p_object_version_number = l_object_version_number THEN
1974 BEGIN
1975 AMV_C_CATEGORIES_PKG.UPDATE_ROW(
1976 X_CHANNEL_CATEGORY_ID => l_category_id,
1980 X_PARENT_CHANNEL_CATEGORY_ID => l_parent_category_id,
1977 X_APPLICATION_ID => l_application_id,
1978 X_OBJECT_VERSION_NUMBER => p_object_version_number + 1,
1979 X_CHANNEL_CATEGORY_ORDER => l_category_order,
1981 X_CHANNEL_COUNT => l_channel_count,
1982 X_CHANNEL_CATEGORY_NAME => l_category_current_name,
1983 X_DESCRIPTION => l_description,
1984 X_LAST_UPDATE_DATE => sysdate,
1985 X_LAST_UPDATED_BY => l_user_id,
1986 X_LAST_UPDATE_LOGIN => l_login_user_id
1987 );
1988 EXCEPTION
1989 WHEN OTHERS THEN
1990 --will log the error
1991 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1992 THEN
1993 FND_MESSAGE.Set_Name('AMV', 'AMV_TABLE_HANDLER_ERROR');
1994 FND_MESSAGE.Set_Token('ACTION', 'Updating');
1995 FND_MESSAGE.Set_Token('TABLE', 'Categories');
1996 FND_MSG_PUB.Add;
1997 END IF;
1998 RAISE FND_API.G_EXC_ERROR;
1999 END;
2000 ELSE
2001 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2002 THEN
2003 FND_MESSAGE.Set_Name('AMV', 'AMV_CAT_VERSION_CHANGE');
2004 FND_MSG_PUB.Add;
2005 END IF;
2006 RAISE FND_API.G_EXC_ERROR;
2007 END IF;
2008 END IF;
2009 END IF;
2010 --
2011 ELSE
2012 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2013 THEN
2014 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
2015 FND_MESSAGE.Set_Token('TKN', l_Error_Token);
2016 FND_MSG_PUB.Add;
2017 END IF;
2018 RAISE FND_API.G_EXC_ERROR;
2019 END IF;
2020 --
2021
2022 -- Success message
2023 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2024 THEN
2025 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
2026 FND_MESSAGE.Set_Token('ROW', l_full_name);
2027 FND_MSG_PUB.Add;
2028 END IF;
2029 --Standard check of commit
2030 IF FND_API.To_Boolean ( p_commit ) THEN
2031 COMMIT WORK;
2032 END IF;
2033 -- Debug Message
2034 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2035 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2036 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
2037 FND_MSG_PUB.Add;
2038 END IF;
2039 --Standard call to get message count and if count=1, get the message
2040 FND_MSG_PUB.Count_And_Get (
2041 p_encoded => FND_API.G_FALSE,
2042 p_count => x_msg_count,
2043 p_data => x_msg_data
2044 );
2045 EXCEPTION
2046 WHEN FND_API.G_EXC_ERROR THEN
2047 ROLLBACK TO Update_Category_PVT;
2048 x_return_status := FND_API.G_RET_STS_ERROR;
2049 -- Standard call to get message count and if count=1, get the message
2050 FND_MSG_PUB.Count_And_Get (
2051 p_encoded => FND_API.G_FALSE,
2052 p_count => x_msg_count,
2053 p_data => x_msg_data
2054 );
2055 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2056 ROLLBACK TO Update_Category_PVT;
2057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2058 -- Standard call to get message count and if count=1, get the message
2059 FND_MSG_PUB.Count_And_Get (
2060 p_encoded => FND_API.G_FALSE,
2061 p_count => x_msg_count,
2062 p_data => x_msg_data
2063 );
2064 WHEN OTHERS THEN
2065 ROLLBACK TO Update_Category_PVT;
2066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2067 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2068 THEN
2069 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2070 END IF;
2071 -- Standard call to get message count and if count=1, get the message
2072 FND_MSG_PUB.Count_And_Get (
2073 p_encoded => FND_API.G_FALSE,
2074 p_count => x_msg_count,
2075 p_data => x_msg_data
2076 );
2077 --
2078 END Update_Category;
2079 --------------------------------------------------------------------------------
2080 -- Start of comments
2081 -- API name : Find_Categories
2082 -- Type : Private
2083 -- Pre-reqs : None
2084 -- Function : Search and return channel (sub)categories
2085 -- Parameters
2086 -- IN p_api_version IN NUMBER Required
2087 -- p_init_msg_list IN VARCHAR2 Optional
2088 -- Default = FND_API.G_FALSE
2089 -- p_validation_level IN NUMBER Optional
2093 -- application creating the channel
2090 -- Default = FND_API.G_VALID_LEVEL_FULL
2091 -- p_application_id IN NUMBER Optional
2092 -- Default = AMV_UTILITY_PVT.G_AMV_APP_ID (520)
2094 -- p_category_name IN VARCHAR2 Optional
2095 -- Search criteria by name. Default = '%' (everything)
2096 -- p_parent_category_id IN NUMBER Optional
2097 -- Default = FND_API.G_MISS_NUM
2098 -- parent id for sub categories
2099 -- p_parent_category_name IN VARCHAR2 Optional
2100 -- Default = FND_API.G_MISS_CHAR
2101 -- parent name for sub categories
2102 -- takes either parent id or name. id taken if both passed
2103 -- OUT : x_return_status OUT VARCHAR2
2104 -- x_msg_count OUT NUMBER
2105 -- x_msg_data OUT VARCHAR2
2106 -- x_chan_category_rec_array OUT AMV_CATEGORY_VARRAY_TYPE
2107 -- Version : Current version 1.0
2108 -- Previous version 1.0
2109 -- Initial version 1.0
2110 -- Notes :
2111 --
2112 -- End of comments
2113 --
2114 PROCEDURE Find_Categories
2115 ( p_api_version IN NUMBER,
2116 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2117 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2118 x_return_status OUT NOCOPY VARCHAR2,
2119 x_msg_count OUT NOCOPY NUMBER,
2120 x_msg_data OUT NOCOPY VARCHAR2,
2121 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2122 p_application_id IN NUMBER := AMV_UTILITY_PVT.G_AMV_APP_ID,
2123 p_category_name IN VARCHAR2 := '%',
2124 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
2125 p_parent_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
2126 p_ignore_hierarchy IN VARCHAR2 := FND_API.G_FALSE,
2127 p_request_obj IN AMV_REQUEST_OBJ_TYPE,
2128 x_return_obj OUT NOCOPY AMV_RETURN_OBJ_TYPE,
2129 x_chan_category_rec_array OUT NOCOPY AMV_CATEGORY_VARRAY_TYPE
2130 )
2131 IS
2132 l_api_name CONSTANT VARCHAR2(30) := 'Find_Categories';
2133 l_api_version CONSTANT NUMBER := 1.0;
2134 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2135 --
2136 l_resource_id number;
2137 l_user_id number;
2138 l_login_user_id number;
2139 l_login_user_status varchar2(30);
2140 l_Error_Msg varchar2(2000);
2141 l_Error_Token varchar2(80);
2142 l_object_version_number number;
2143 l_application_id number;
2144 --
2145 l_category_id number;
2146 l_pcategory_id number;
2147 l_parent_category_id number;
2148 l_category_exist_flag varchar2(1);
2149 l_category_order number;
2150 l_channel_count number;
2151 l_record_count NUMBER := 0;
2152 l_counter NUMBER := 1;
2153 l_category_name varchar2(80);
2154 l_description varchar2(2000);
2155 l_count number := 0;
2156 --
2157
2158 CURSOR Get_AllCategories IS
2159 select channel_category_id,
2160 object_version_number,
2161 parent_channel_category_id,
2162 channel_category_order,
2163 nvl(channel_count,0),
2164 channel_category_name,
2165 description
2166 from amv_c_categories_vl
2167 where channel_category_name like p_category_name
2168 and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
2169 and application_id = p_application_id
2170 order by channel_category_name;
2171
2172 CURSOR Get_Categories IS
2173 select channel_category_id,
2174 object_version_number,
2175 parent_channel_category_id,
2176 channel_category_order,
2177 nvl(channel_count,0),
2178 channel_category_name,
2179 description
2180 from amv_c_categories_vl
2181 where channel_category_name like p_category_name
2185 order by channel_category_order;
2182 and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
2183 and application_id = p_application_id
2184 and parent_channel_category_id is null
2186
2187 CURSOR Get_SubCategories IS
2188 select channel_category_id,
2189 object_version_number,
2190 parent_channel_category_id,
2191 channel_category_order,
2192 nvl(channel_count,0),
2193 channel_category_name,
2194 description
2195 from amv_c_categories_vl
2196 where channel_category_name like p_category_name
2197 and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
2198 and application_id = p_application_id
2199 and parent_channel_category_id = l_parent_category_id
2200 order by channel_category_order;
2201
2202 CURSOR Count_AllCategories_csr IS
2203 select count(channel_category_id)
2204 from amv_c_categories_vl
2205 where channel_category_name like p_category_name
2206 and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
2207 and application_id = p_application_id;
2208
2209 CURSOR Count_ParentCategories_csr IS
2210 select count(channel_category_id)
2211 from amv_c_categories_vl
2212 where channel_category_name like p_category_name
2213 and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
2214 and application_id = p_application_id
2215 and parent_channel_category_id is null;
2216
2217 CURSOR Count_SubCategories_csr IS
2218 select count(channel_category_id)
2219 from amv_c_categories_vl
2220 where channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
2221 and application_id = p_application_id
2222 and parent_channel_category_id = l_category_id;
2223
2224 CURSOR Count_Channels_csr IS
2225 select count(channel_id)
2226 from amv_c_channels_b
2227 where channel_category_id = l_category_id;
2228 --
2229 BEGIN
2230 -- Standard begin of API savepoint
2231 SAVEPOINT Find_Categories_PVT;
2232 -- Standard call to check for call compatibility.
2233 IF NOT FND_API.Compatible_API_Call (
2234 l_api_version,
2235 p_api_version,
2236 l_api_name,
2237 G_PKG_NAME)
2238 THEN
2239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2240 END IF;
2241 -- Debug Message
2242 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2243 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2244 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
2245 FND_MSG_PUB.Add;
2246 END IF;
2250 END IF;
2247 --Initialize message list if p_init_msg_list is TRUE.
2248 IF FND_API.To_Boolean (p_init_msg_list) THEN
2249 FND_MSG_PUB.initialize;
2251 -- Get the current (login) user id.
2252 AMV_UTILITY_PVT.Get_UserInfo(
2253 x_resource_id => l_resource_id,
2254 x_user_id => l_user_id,
2255 x_login_id => l_login_user_id,
2256 x_user_status => l_login_user_status
2257 );
2258 -- check login user
2262 -- User is not login.
2259 IF (p_check_login_user = FND_API.G_TRUE) THEN
2260 -- Check if user is login and has the required privilege.
2261 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2263 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2264 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2265 FND_MSG_PUB.Add;
2266 END IF;
2267 RAISE FND_API.G_EXC_ERROR;
2268 END IF;
2269 END IF;
2270 -- This fix is for executing api in sqlplus mode
2271 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2272 l_login_user_id := g_login_user_id;
2273 l_user_id := g_user_id;
2274 l_resource_id := g_resource_id;
2275 END IF;
2276 -- Initialize API return status to sucess
2277 x_return_status := FND_API.G_RET_STS_SUCCESS;
2278
2279 --
2280 -- check parent category name or id and get parent category id
2281 Validate_CategoryStatus (
2282 x_return_status => x_return_status,
2283 p_category_id => p_parent_category_id,
2284 p_category_name => p_parent_category_name,
2285 x_exist_flag => l_category_exist_flag,
2286 x_category_id => l_parent_category_id,
2287 x_error_msg => l_Error_Msg,
2288 x_error_token => l_Error_Token
2289 );
2290 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2291 IF (l_category_exist_flag = FND_API.G_FALSE) THEN
2292 -- parent id or name is not valid
2293 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2294 THEN
2295 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
2296 FND_MESSAGE.Set_Token('TKN', l_Error_Token);
2297 FND_MSG_PUB.Add;
2298 END IF;
2299 RAISE FND_API.G_EXC_ERROR;
2300 END IF;
2301 ELSE
2302 -- parent id or name is valid
2303 x_return_status := FND_API.G_RET_STS_SUCCESS;
2304 END IF;
2305
2306 IF p_ignore_hierarchy = FND_API.G_TRUE THEN
2307 -- fetch all categories
2308 OPEN Get_AllCategories;
2309 x_chan_category_rec_array := AMV_CATEGORY_VARRAY_TYPE();
2310 LOOP
2311 FETCH Get_AllCategories INTO
2312 l_category_id,
2313 l_object_version_number,
2314 l_pcategory_id,
2315 l_category_order,
2316 l_channel_count,
2317 l_category_name,
2318 l_description;
2319 EXIT WHEN Get_AllCategories%NOTFOUND;
2320 IF l_category_name not in ('AMV_GROUP','AMV_PRIVATE') THEN
2321 OPEN Count_AllCategories_csr;
2322 FETCH Count_AllCategories_csr INTO l_count;
2323 CLOSE Count_AllCategories_csr;
2324 OPEN Count_Channels_csr;
2325 FETCH Count_Channels_csr INTO l_channel_count;
2326 CLOSE Count_Channels_csr;
2327 IF (l_counter >= p_request_obj.start_record_position) AND
2328 (l_record_count <= p_request_obj.records_requested)
2329 THEN
2330 l_record_count := l_record_count + 1;
2331 x_chan_category_rec_array.extend;
2332 x_chan_category_rec_array(l_record_count).category_id :=
2333 l_category_id;
2334 x_chan_category_rec_array(l_record_count).object_version_number := l_object_version_number;
2335 x_chan_category_rec_array(l_record_count).parent_category_id :=
2336 l_pcategory_id;
2337 x_chan_category_rec_array(l_record_count).category_order :=
2338 l_category_order;
2339 x_chan_category_rec_array(l_record_count).channel_count :=
2340 l_channel_count;
2341 x_chan_category_rec_array(l_record_count).category_name :=
2342 l_category_name;
2343 x_chan_category_rec_array(l_record_count).description :=
2344 l_description;
2345 x_chan_category_rec_array(l_record_count).count := l_count;
2346 /*
2347 x_chan_category_rec_array(l_record_count) :=
2348 amv_category_obj_type(
2349 l_category_id,
2350 l_object_version_number,
2351 l_pcategory_id,
2352 l_category_order,
2353 l_channel_count,
2354 l_category_name,
2355 l_description,
2356 l_count);
2357 */
2358 END IF;
2359 l_counter := l_counter + 1;
2360 EXIT WHEN p_request_obj.records_requested = l_record_count;
2361 END IF;
2362 END LOOP;
2363 CLOSE Get_AllCategories;
2364 ELSE
2365 IF l_parent_category_id = FND_API.G_MISS_NUM OR
2366 l_parent_category_id IS NULL
2367 THEN
2368 -- fetch all root level categories
2369 OPEN Get_Categories;
2370 x_chan_category_rec_array := AMV_CATEGORY_VARRAY_TYPE();
2371 LOOP
2372 FETCH Get_Categories INTO
2373 l_category_id,
2374 l_object_version_number,
2375 l_pcategory_id,
2376 l_category_order,
2377 l_channel_count,
2378 l_category_name,
2379 l_description;
2380 EXIT WHEN Get_Categories%NOTFOUND;
2381 IF l_category_name not in ('AMV_GROUP','AMV_PRIVATE') THEN
2382 OPEN Count_ParentCategories_csr;
2383 FETCH Count_ParentCategories_csr INTO l_count;
2384 CLOSE Count_ParentCategories_csr;
2385 OPEN Count_Channels_csr;
2386 FETCH Count_Channels_csr INTO l_channel_count;
2387 CLOSE Count_Channels_csr;
2388 IF (l_counter >= p_request_obj.start_record_position) AND
2389 (l_record_count <= p_request_obj.records_requested)
2390 THEN
2391 l_record_count := l_record_count + 1;
2392 x_chan_category_rec_array.extend;
2393 x_chan_category_rec_array(l_record_count).category_id :=
2394 l_category_id;
2395 x_chan_category_rec_array(l_record_count).object_version_number := l_object_version_number;
2396 x_chan_category_rec_array(l_record_count).parent_category_id :=
2397 l_pcategory_id;
2398 x_chan_category_rec_array(l_record_count).category_order :=
2399 l_category_order;
2400 x_chan_category_rec_array(l_record_count).channel_count :=
2401 l_channel_count;
2402 x_chan_category_rec_array(l_record_count).category_name :=
2403 l_category_name;
2404 x_chan_category_rec_array(l_record_count).description :=
2405 l_description;
2406 x_chan_category_rec_array(l_record_count).count := l_count;
2407 /*
2408 x_chan_category_rec_array(l_record_count) :=
2409 amv_category_obj_type(
2410 l_category_id,
2411 l_object_version_number,
2412 l_pcategory_id,
2413 l_category_order,
2414 l_channel_count,
2415 l_category_name,
2416 l_description,
2417 l_count);
2418 */
2419 END IF;
2420 l_counter := l_counter + 1;
2421 EXIT WHEN p_request_obj.records_requested = l_record_count;
2422 END IF;
2423 END LOOP;
2427 OPEN Get_SubCategories;
2424 CLOSE Get_Categories;
2425 ELSE
2426 -- fetch all sub-categories for a category
2428 x_chan_category_rec_array := AMV_CATEGORY_VARRAY_TYPE();
2429 LOOP
2430 FETCH Get_SubCategories INTO
2431 l_category_id,
2432 l_object_version_number,
2433 l_pcategory_id,
2434 l_category_order,
2435 l_channel_count,
2436 l_category_name,
2437 l_description;
2438 EXIT WHEN Get_SubCategories%NOTFOUND;
2439 IF l_category_name not in ('AMV_GROUP','AMV_PRIVATE') THEN
2440 OPEN Count_SubCategories_csr;
2441 FETCH Count_SubCategories_csr INTO l_count;
2442 CLOSE Count_SubCategories_csr;
2443 OPEN Count_Channels_csr;
2444 FETCH Count_Channels_csr INTO l_channel_count;
2445 CLOSE Count_Channels_csr;
2446 IF (l_counter >= p_request_obj.start_record_position) AND
2447 (l_record_count <= p_request_obj.records_requested)
2448 THEN
2449 l_record_count := l_record_count + 1;
2450 x_chan_category_rec_array.extend;
2451 x_chan_category_rec_array(l_record_count).category_id :=
2452 l_category_id;
2453 x_chan_category_rec_array(l_record_count).object_version_number := l_object_version_number;
2454 x_chan_category_rec_array(l_record_count).parent_category_id :=
2455 l_pcategory_id;
2456 x_chan_category_rec_array(l_record_count).category_order :=
2457 l_category_order;
2458 x_chan_category_rec_array(l_record_count).channel_count :=
2459 l_channel_count;
2460 x_chan_category_rec_array(l_record_count).category_name :=
2461 l_category_name;
2462 x_chan_category_rec_array(l_record_count).description :=
2463 l_description;
2464 x_chan_category_rec_array(l_record_count).count := l_count;
2465
2466 /*
2467 x_chan_category_rec_array(l_record_count) :=
2468 amv_category_obj_type(
2469 l_category_id,
2470 l_object_version_number,
2471 l_pcategory_id,
2472 l_category_order,
2473 l_channel_count,
2474 l_category_name,
2475 l_description,
2476 l_count);
2477 */
2478 END IF;
2479 l_counter := l_counter + 1;
2480 EXIT WHEN p_request_obj.records_requested = l_record_count;
2481 END IF;
2482 END LOOP;
2483 CLOSE Get_SubCategories;
2484 END IF;
2485 END IF;
2486 x_return_obj.returned_record_count := l_record_count;
2487 x_return_obj.next_record_position := l_counter;
2488 x_return_obj.total_record_count := l_count;
2489 /*
2490 x_return_obj := amv_return_obj_type( l_record_count,
2491 l_counter,
2492 l_count);
2493 */
2494 --
2495
2496 -- Debug Message
2497 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2498 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2499 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
2500 FND_MSG_PUB.Add;
2501 END IF;
2502 --Standard call to get message count and if count=1, get the message
2503 FND_MSG_PUB.Count_And_Get (
2504 p_encoded => FND_API.G_FALSE,
2505 p_count => x_msg_count,
2506 p_data => x_msg_data
2507 );
2508 EXCEPTION
2509 WHEN FND_API.G_EXC_ERROR THEN
2510 ROLLBACK TO Find_Categories_PVT;
2511 x_return_status := FND_API.G_RET_STS_ERROR;
2512 -- Standard call to get message count and if count=1, get the message
2513 FND_MSG_PUB.Count_And_Get (
2514 p_encoded => FND_API.G_FALSE,
2515 p_count => x_msg_count,
2516 p_data => x_msg_data
2517 );
2518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2519 ROLLBACK TO Find_Categories_PVT;
2520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2521 -- Standard call to get message count and if count=1, get the message
2522 FND_MSG_PUB.Count_And_Get (
2523 p_encoded => FND_API.G_FALSE,
2524 p_count => x_msg_count,
2525 p_data => x_msg_data
2526 );
2527 WHEN OTHERS THEN
2528 ROLLBACK TO Find_Categories_PVT;
2529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2530 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2531 THEN
2532 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2533 END IF;
2534 -- Standard call to get message count and if count=1, get the message
2535 FND_MSG_PUB.Count_And_Get (
2536 p_encoded => FND_API.G_FALSE,
2537 p_count => x_msg_count,
2538 p_data => x_msg_data
2539 );
2540 --
2541 END Find_Categories;
2542 --------------------------------------------------------------------------------
2543 -- Start of comments
2544 -- API name : Get_ChannelsPerCategory
2545 -- Type : Private
2546 -- Pre-reqs : None
2547 -- Function : Return all channels directly under
2548 -- a content channel (sub)category
2549 -- Parameters :
2550 -- IN p_api_version IN NUMBER Required
2551 -- p_init_msg_list IN VARCHAR2 Optional
2552 -- Default = FND_API.G_FALSE
2553 -- p_validation_level IN NUMBER Optional
2554 -- Default = FND_API.G_VALID_LEVEL_FULL
2555 -- p_category_id IN NUMBER Required
2556 -- p_include_subcats IN VARCHAR2 Optional
2557 -- Default = FND_API.G_FALSE
2558 -- OUT : x_return_status OUT VARCHAR2
2559 -- x_msg_count OUT NUMBER
2560 -- x_msg_data OUT VARCHAR2
2561 -- x_content_chan_array OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
2562 -- Version : Current version 1.0
2563 -- Previous version 1.0
2564 -- Initial version 1.0
2565 -- Notes :
2566 --
2567 -- End of comments
2568 --
2569 PROCEDURE Get_ChannelsPerCategory
2570 ( p_api_version IN NUMBER,
2571 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2575 x_msg_data OUT NOCOPY VARCHAR2,
2572 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2573 x_return_status OUT NOCOPY VARCHAR2,
2574 x_msg_count OUT NOCOPY NUMBER,
2576 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2577 p_category_id IN NUMBER,
2578 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
2579 x_content_chan_array OUT NOCOPY AMV_CAT_HIERARCHY_VARRAY_TYPE
2580 )
2581 IS
2582 l_api_name CONSTANT VARCHAR2(30) := 'Get_ChannelsPerCategory';
2583 l_api_version CONSTANT NUMBER := 1.0;
2584 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2585 --
2586 l_resource_id number;
2587 l_user_id number;
2588 l_login_user_id number;
2589 l_login_user_status varchar2(30);
2590 l_Error_Msg varchar2(2000);
2591 l_Error_Token varchar2(80);
2592 l_object_version_number number;
2593 l_application_id number;
2594 --
2595 l_record_count NUMBER := 1;
2596 l_category_id number;
2597 l_category_level number := 1;
2598 l_category_hr amv_cat_hierarchy_varray_type;
2599 l_channel_id number;
2600 l_channel_name varchar2(80);
2601
2602 CURSOR Get_CategoryChannels IS
2603 select b.channel_id
2604 , b.channel_name
2605 from amv_c_channels_vl b
2606 where b.channel_category_id = l_category_id
2607 and b.effective_start_date <= sysdate
2608 and nvl(b.expiration_date, sysdate) >= sysdate
2609 order by b.channel_name;
2610 --order by b.creation_date desc;
2611
2612 BEGIN
2613 -- Standard begin of API savepoint
2614 SAVEPOINT Get_ChansPerCategory_PVT;
2615 -- Standard call to check for call compatibility.
2616 IF NOT FND_API.Compatible_API_Call (
2617 l_api_version,
2618 p_api_version,
2619 l_api_name,
2620 G_PKG_NAME)
2621 THEN
2622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2623 END IF;
2624 -- Debug Message
2625 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2626 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2627 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
2628 FND_MSG_PUB.Add;
2629 END IF;
2630 --Initialize message list if p_init_msg_list is TRUE.
2631 IF FND_API.To_Boolean (p_init_msg_list) THEN
2632 FND_MSG_PUB.initialize;
2633 END IF;
2634 -- Get the current (login) user id.
2635 AMV_UTILITY_PVT.Get_UserInfo(
2636 x_resource_id => l_resource_id,
2637 x_user_id => l_user_id,
2638 x_login_id => l_login_user_id,
2639 x_user_status => l_login_user_status
2640 );
2641 -- check login user
2642 IF (p_check_login_user = FND_API.G_TRUE) THEN
2643 -- Check if user is login and has the required privilege.
2644 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2645 -- User is not login.
2646 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2647 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2648 FND_MSG_PUB.Add;
2649 END IF;
2650 RAISE FND_API.G_EXC_ERROR;
2651 END IF;
2652 END IF;
2656 l_user_id := g_user_id;
2653 -- This fix is for executing api in sqlplus mode
2654 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2655 l_login_user_id := g_login_user_id;
2657 l_resource_id := g_resource_id;
2658 END IF;
2659 -- Initialize API return status to sucess
2660 x_return_status := FND_API.G_RET_STS_SUCCESS;
2661
2662 --
2663 IF AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) THEN
2664 x_content_chan_array := AMV_CAT_HIERARCHY_VARRAY_TYPE();
2665 IF p_include_subcats = FND_API.G_FALSE THEN
2666 l_category_id := p_category_id;
2667 OPEN Get_CategoryChannels;
2668 LOOP
2669 FETCH Get_CategoryChannels INTO l_channel_id, l_channel_name;
2670 EXIT WHEN Get_CategoryChannels%NOTFOUND;
2671 x_content_chan_array.extend;
2672 x_content_chan_array(l_record_count).hierarchy_level
2673 := l_category_id;
2674 x_content_chan_array(l_record_count).id := l_channel_id;
2675 x_content_chan_array(l_record_count).name := l_channel_name;
2676 /*
2677 x_content_chan_array(l_record_count) :=
2678 amv_cat_hierarchy_obj_type( l_category_id,
2679 l_channel_id,
2680 l_channel_name);
2681 */
2682 l_record_count := l_record_count + 1;
2683 END LOOP;
2684 CLOSE Get_CategoryChannels;
2685 ELSE
2686 l_category_hr := amv_cat_hierarchy_varray_type();
2687 Get_CategoryHierarchy(p_category_id, l_category_level, l_category_hr);
2688
2689 FOR i IN 1..l_category_hr.count LOOP
2690 l_category_id := l_category_hr(i).id;
2691 OPEN Get_CategoryChannels;
2692 LOOP
2693 FETCH Get_CategoryChannels INTO l_channel_id, l_channel_name;
2694 EXIT WHEN Get_CategoryChannels%NOTFOUND;
2695 x_content_chan_array.extend;
2696 x_content_chan_array(l_record_count).hierarchy_level
2697 := l_category_id;
2698 x_content_chan_array(l_record_count).id := l_channel_id;
2699 x_content_chan_array(l_record_count).name := l_channel_name;
2700 /*
2701 x_content_chan_array(l_record_count) :=
2702 amv_cat_hierarchy_obj_type( l_category_id,
2703 l_channel_id,
2704 l_channel_name);
2705 */
2706 l_record_count := l_record_count + 1;
2707 END LOOP;
2708 CLOSE Get_CategoryChannels;
2709 END LOOP;
2710 END IF;
2711 ELSE
2712 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2713 THEN
2714 FND_MESSAGE.Set_Name('AMV', 'AMV_CATEGORY_ID_INVALID');
2715 FND_MESSAGE.Set_Token('TKN',p_category_id);
2716 FND_MSG_PUB.Add;
2717 END IF;
2718 RAISE FND_API.G_EXC_ERROR;
2719 END IF;
2720 --
2721
2722 -- Debug Message
2723 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2724 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2725 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
2726 FND_MSG_PUB.Add;
2727 END IF;
2728 --Standard call to get message count and if count=1, get the message
2729 FND_MSG_PUB.Count_And_Get (
2730 p_encoded => FND_API.G_FALSE,
2731 p_count => x_msg_count,
2732 p_data => x_msg_data
2733 );
2734 EXCEPTION
2735 WHEN FND_API.G_EXC_ERROR THEN
2736 ROLLBACK TO Get_ChansPerCategory_PVT;
2740 p_encoded => FND_API.G_FALSE,
2737 x_return_status := FND_API.G_RET_STS_ERROR;
2738 -- Standard call to get message count and if count=1, get the message
2739 FND_MSG_PUB.Count_And_Get (
2741 p_count => x_msg_count,
2742 p_data => x_msg_data
2743 );
2744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2745 ROLLBACK TO Get_ChansPerCategory_PVT;
2746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2747 -- Standard call to get message count and if count=1, get the message
2748 FND_MSG_PUB.Count_And_Get (
2749 p_encoded => FND_API.G_FALSE,
2750 p_count => x_msg_count,
2751 p_data => x_msg_data
2752 );
2753 WHEN OTHERS THEN
2754 ROLLBACK TO Get_ChansPerCategory_PVT;
2755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2756 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2757 THEN
2758 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2759 END IF;
2760 -- Standard call to get message count and if count=1, get the message
2761 FND_MSG_PUB.Count_And_Get (
2762 p_encoded => FND_API.G_FALSE,
2763 p_count => x_msg_count,
2764 p_data => x_msg_data
2765 );
2766 --
2767 END Get_ChannelsPerCategory;
2768 --------------------------------------------------------------------------------
2769 --------------------------------------------------------------------------------
2770 -- Start of comments
2771 -- API name : Get_ItemsPerCategory
2772 -- Type : Private
2773 -- Pre-reqs : None
2774 -- Function : Return all items directly under
2775 -- a content channel (sub)category
2776 -- Parameters :
2777 -- IN p_api_version IN NUMBER Required
2778 -- p_init_msg_list IN VARCHAR2 Optional
2779 -- Default = FND_API.G_FALSE
2780 -- p_validation_level IN NUMBER Optional
2781 -- Default = FND_API.G_VALID_LEVEL_FULL
2782 -- p_category_id IN NUMBER Required
2783 -- p_include_subcats IN VARCHAR2 Optional
2784 -- Default = FND_API.G_FALSE
2785 -- OUT : x_return_status OUT VARCHAR2
2786 -- x_msg_count OUT NUMBER
2787 -- x_msg_data OUT VARCHAR2
2788 -- x_items_array OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
2789 -- Version : Current version 1.0
2793 --
2790 -- Previous version 1.0
2791 -- Initial version 1.0
2792 -- Notes :
2794 -- End of comments
2795 --
2796 PROCEDURE Get_ItemsPerCategory
2797 ( p_api_version IN NUMBER,
2798 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2799 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2800 x_return_status OUT NOCOPY VARCHAR2,
2801 x_msg_count OUT NOCOPY NUMBER,
2805 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
2802 x_msg_data OUT NOCOPY VARCHAR2,
2803 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2804 p_category_id IN NUMBER,
2806 x_items_array OUT NOCOPY AMV_CAT_HIERARCHY_VARRAY_TYPE
2807 )
2808 IS
2809 l_api_name CONSTANT VARCHAR2(30) := 'Get_ItemsPerCategory';
2810 l_api_version CONSTANT NUMBER := 1.0;
2811 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2812 --
2813 l_resource_id number;
2814 l_user_id number;
2815 l_login_user_id number;
2816 l_login_user_status varchar2(30);
2817 l_Error_Msg varchar2(2000);
2818 l_Error_Token varchar2(80);
2819 l_object_version_number number;
2820 l_application_id number;
2821 --
2822 l_record_count NUMBER := 1;
2823 l_category_id number;
2824 l_category_level number := 1;
2825 l_category_hr amv_cat_hierarchy_varray_type;
2826 l_item_id number;
2827 l_item_name varchar2(240);
2828
2829 CURSOR Get_CategoryItems_csr IS
2830 select ib.item_id
2831 , ib.item_name
2832 from amv_c_chl_item_match cim
2833 , jtf_amv_items_vl ib
2834 where cim.channel_category_id = l_category_id
2835 and cim.channel_id is null
2836 and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
2837 and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
2838 and cim.available_for_channel_date <= sysdate
2839 and cim.item_id = ib.item_id
2840 and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
2841 and nvl(ib.expiration_date, sysdate) >= sysdate
2842 order by ib.effective_start_date;
2843
2844 BEGIN
2845 -- Standard begin of API savepoint
2846 SAVEPOINT Get_ItemsPerCategory_PVT;
2847 -- Standard call to check for call compatibility.
2848 IF NOT FND_API.Compatible_API_Call (
2849 l_api_version,
2850 p_api_version,
2851 l_api_name,
2852 G_PKG_NAME)
2853 THEN
2854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2855 END IF;
2856 -- Debug Message
2857 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2858 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2859 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
2860 FND_MSG_PUB.Add;
2861 END IF;
2862 --Initialize message list if p_init_msg_list is TRUE.
2863 IF FND_API.To_Boolean (p_init_msg_list) THEN
2864 FND_MSG_PUB.initialize;
2865 END IF;
2866 -- Get the current (login) user id.
2867 AMV_UTILITY_PVT.Get_UserInfo(
2868 x_resource_id => l_resource_id,
2869 x_user_id => l_user_id,
2870 x_login_id => l_login_user_id,
2871 x_user_status => l_login_user_status
2872 );
2873 -- check login user
2874 IF (p_check_login_user = FND_API.G_TRUE) THEN
2875 -- Check if user is login and has the required privilege.
2876 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2877 -- User is not login.
2878 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2879 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2880 FND_MSG_PUB.Add;
2881 END IF;
2882 RAISE FND_API.G_EXC_ERROR;
2883 END IF;
2884 END IF;
2885 -- This fix is for executing api in sqlplus mode
2886 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2887 l_login_user_id := g_login_user_id;
2888 l_user_id := g_user_id;
2889 l_resource_id := g_resource_id;
2890 END IF;
2891 -- Initialize API return status to sucess
2892 x_return_status := FND_API.G_RET_STS_SUCCESS;
2893
2894 --
2895 IF AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) THEN
2896 x_items_array := AMV_CAT_HIERARCHY_VARRAY_TYPE();
2897 IF p_include_subcats = FND_API.G_FALSE THEN
2898 l_category_id := p_category_id;
2899 OPEN Get_CategoryItems_csr;
2900 LOOP
2901 FETCH Get_CategoryItems_csr INTO l_item_id, l_item_name;
2902 EXIT WHEN Get_CategoryItems_csr%NOTFOUND;
2903 x_items_array.extend;
2904 x_items_array(l_record_count).hierarchy_level := l_category_id;
2905 x_items_array(l_record_count).id := l_item_id;
2906 x_items_array(l_record_count).name := l_item_name;
2907 /*
2908 x_items_array(l_record_count) :=
2909 amv_cat_hierarchy_obj_type( l_category_id,
2910 l_item_id,
2911 l_item_name);
2912 */
2913 l_record_count := l_record_count + 1;
2914 END LOOP;
2915 CLOSE Get_CategoryItems_csr;
2916 ELSE
2917 l_category_hr := amv_cat_hierarchy_varray_type();
2918 Get_CategoryHierarchy(p_category_id, l_category_level, l_category_hr);
2919
2920 FOR i IN 1..l_category_hr.count LOOP
2921 l_category_id := l_category_hr(i).id;
2922 OPEN Get_CategoryItems_csr;
2923 LOOP
2924 FETCH Get_CategoryItems_csr INTO l_item_id, l_item_name;
2925 EXIT WHEN Get_CategoryItems_csr%NOTFOUND;
2926 x_items_array.extend;
2927 x_items_array(l_record_count).hierarchy_level := l_category_id;
2928 x_items_array(l_record_count).id := l_item_id;
2929 x_items_array(l_record_count).name := l_item_name;
2930 /*
2931 x_items_array(l_record_count) :=
2932 amv_cat_hierarchy_obj_type( l_category_id,
2933 l_item_id,
2934 l_item_name);
2935 */
2936 l_record_count := l_record_count + 1;
2937 END LOOP;
2938 CLOSE Get_CategoryItems_csr;
2939 END LOOP;
2940 END IF;
2941 ELSE
2942 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2943 THEN
2944 FND_MESSAGE.Set_Name('AMV', 'AMV_CATEGORY_ID_INVALID');
2945 FND_MESSAGE.Set_Token('TKN',p_category_id);
2946 FND_MSG_PUB.Add;
2947 END IF;
2948 RAISE FND_API.G_EXC_ERROR;
2949 END IF;
2950 --
2951
2952 -- Debug Message
2953 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2954 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2955 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
2956 FND_MSG_PUB.Add;
2957 END IF;
2958 --Standard call to get message count and if count=1, get the message
2959 FND_MSG_PUB.Count_And_Get (
2960 p_encoded => FND_API.G_FALSE,
2961 p_count => x_msg_count,
2962 p_data => x_msg_data
2963 );
2964 EXCEPTION
2965 WHEN FND_API.G_EXC_ERROR THEN
2966 ROLLBACK TO Get_ItemsPerCategory_PVT;
2967 x_return_status := FND_API.G_RET_STS_ERROR;
2968 -- Standard call to get message count and if count=1, get the message
2969 FND_MSG_PUB.Count_And_Get (
2970 p_encoded => FND_API.G_FALSE,
2971 p_count => x_msg_count,
2972 p_data => x_msg_data
2973 );
2974 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2975 ROLLBACK TO Get_ItemsPerCategory_PVT;
2976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2977 -- Standard call to get message count and if count=1, get the message
2978 FND_MSG_PUB.Count_And_Get (
2979 p_encoded => FND_API.G_FALSE,
2980 p_count => x_msg_count,
2981 p_data => x_msg_data
2982 );
2983 WHEN OTHERS THEN
2984 ROLLBACK TO Get_ItemsPerCategory_PVT;
2985 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2986 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2987 THEN
2988 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2989 END IF;
2990 -- Standard call to get message count and if count=1, get the message
2991 FND_MSG_PUB.Count_And_Get (
2992 p_encoded => FND_API.G_FALSE,
2993 p_count => x_msg_count,
2994 p_data => x_msg_data
2995 );
2996 --
2997 END Get_ItemsPerCategory;
2998 --------------------------------------------------------------------------------
2999 --------------------------------------------------------------------------------
3000 -- Start of comments
3001 -- API name : Get_ItemsPerCategory
3002 -- Type : Private
3003 -- Pre-reqs : None
3004 -- Function : Return all items directly under
3005 -- a content channel (sub)category
3006 -- Parameters :
3007 -- IN p_api_version IN NUMBER Required
3008 -- p_init_msg_list IN VARCHAR2 Optional
3009 -- Default = FND_API.G_FALSE
3010 -- p_validation_level IN NUMBER Optional
3011 -- Default = FND_API.G_VALID_LEVEL_FULL
3012 -- p_category_id IN NUMBER Required
3013 -- p_include_subcats IN VARCHAR2 Optional
3014 -- Default = FND_API.G_FALSE
3015 -- OUT : x_return_status OUT VARCHAR2
3016 -- x_msg_count OUT NUMBER
3017 -- x_msg_data OUT VARCHAR2
3018 -- x_items_array OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
3019 -- Version : Current version 1.0
3020 -- Previous version 1.0
3021 -- Initial version 1.0
3022 -- Notes :
3023 --
3024 -- End of comments
3025 --
3026 PROCEDURE Get_ItemsPerCategory
3027 ( p_api_version IN NUMBER,
3028 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3029 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3030 x_return_status OUT NOCOPY VARCHAR2,
3031 x_msg_count OUT NOCOPY NUMBER,
3032 x_msg_data OUT NOCOPY VARCHAR2,
3033 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3034 p_category_id IN NUMBER,
3035 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
3036 p_request_obj IN AMV_REQUEST_OBJ_TYPE,
3037 p_category_sort IN AMV_SORT_OBJ_TYPE,
3038 x_return_obj OUT NOCOPY AMV_RETURN_OBJ_TYPE,
3039 x_items_array OUT NOCOPY AMV_CAT_HIERARCHY_VARRAY_TYPE
3040 )
3041 IS
3042 l_api_name CONSTANT VARCHAR2(30) := 'Get_ItemsPerCategory';
3043 l_api_version CONSTANT NUMBER := 1.0;
3044 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3045 --
3046 l_resource_id number;
3047 l_user_id number;
3048 l_login_user_id number;
3049 l_login_user_status varchar2(30);
3050 l_Error_Msg varchar2(2000);
3051 l_Error_Token varchar2(80);
3052 l_object_version_number number;
3053 l_application_id number;
3054 --
3055 l_record_count NUMBER := 0;
3056 l_total_count NUMBER := 0;
3057 l_temp_total NUMBER := 0;
3058 l_counter NUMBER := 1;
3059 l_category_id number;
3060 l_category_level number := 1;
3061 l_category_hr amv_cat_hierarchy_varray_type;
3062 l_item_id number;
3063 l_item_name varchar2(240);
3064 l_sort_dir varchar2(10);
3065 l_sort_col varchar2(80);
3066
3067 CURSOR Get_CategoryItems_csr IS
3068 select ib.item_id
3069 , ib.item_name
3070 from amv_c_chl_item_match cim
3071 , jtf_amv_items_vl ib
3072 where cim.channel_category_id = l_category_id
3073 and cim.channel_id is null
3074 and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
3075 and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
3076 and cim.available_for_channel_date <= sysdate
3077 and cim.item_id = ib.item_id
3078 and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
3079 and nvl(ib.expiration_date, sysdate) >= sysdate
3080 order by l_sort_col ||' '||l_sort_dir;
3081
3082 CURSOR Get_ItemsTotal_csr IS
3083 select count(cim.item_id)
3084 from amv_c_chl_item_match cim
3085 , jtf_amv_items_vl ib
3086 where cim.channel_category_id = l_category_id
3087 and cim.channel_id is null
3088 and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
3089 and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
3093 and nvl(ib.expiration_date, sysdate) >= sysdate;
3090 and cim.available_for_channel_date <= sysdate
3091 and cim.item_id = ib.item_id
3092 and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
3094
3095 BEGIN
3096 -- Standard begin of API savepoint
3097 SAVEPOINT Get_ItemsPerCategory_PVT;
3098 -- Standard call to check for call compatibility.
3099 IF NOT FND_API.Compatible_API_Call (
3100 l_api_version,
3101 p_api_version,
3102 l_api_name,
3103 G_PKG_NAME)
3104 THEN
3105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3106 END IF;
3107 -- Debug Message
3108 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3109 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3110 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
3111 FND_MSG_PUB.Add;
3112 END IF;
3113 --Initialize message list if p_init_msg_list is TRUE.
3114 IF FND_API.To_Boolean (p_init_msg_list) THEN
3115 FND_MSG_PUB.initialize;
3116 END IF;
3117 -- Get the current (login) user id.
3118 AMV_UTILITY_PVT.Get_UserInfo(
3119 x_resource_id => l_resource_id,
3120 x_user_id => l_user_id,
3121 x_login_id => l_login_user_id,
3122 x_user_status => l_login_user_status
3123 );
3124 -- check login user
3125 IF (p_check_login_user = FND_API.G_TRUE) THEN
3126 -- Check if user is login and has the required privilege.
3127 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3128 -- User is not login.
3129 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3130 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3131 FND_MSG_PUB.Add;
3132 END IF;
3133 RAISE FND_API.G_EXC_ERROR;
3134 END IF;
3135 END IF;
3136 -- This fix is for executing api in sqlplus mode
3137 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3138 l_login_user_id := g_login_user_id;
3139 l_user_id := g_user_id;
3140 l_resource_id := g_resource_id;
3141 END IF;
3142 -- Initialize API return status to sucess
3143 x_return_status := FND_API.G_RET_STS_SUCCESS;
3144
3145 --
3146 IF AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) THEN
3147 x_items_array := AMV_CAT_HIERARCHY_VARRAY_TYPE();
3148 IF p_include_subcats = FND_API.G_FALSE THEN
3149 l_category_id := p_category_id;
3150 IF ( p_category_sort.sort_col = null ) THEN
3151 l_sort_col := 'ib.'||p_category_sort.sort_col;
3152 ELSE
3153 l_sort_col := 'ib.effective_start_date';
3154 END IF;
3155
3156 l_sort_dir := nvl(p_category_sort.sort_dir, 'DESC');
3157
3158 -- get the total number of items in category
3159 OPEN Get_ItemsTotal_csr;
3160 FETCH Get_ItemsTotal_csr INTO l_total_count;
3161 CLOSE Get_ItemsTotal_csr;
3162
3163 OPEN Get_CategoryItems_csr;
3164 LOOP
3165 FETCH Get_CategoryItems_csr INTO l_item_id, l_item_name;
3166 EXIT WHEN Get_CategoryItems_csr%NOTFOUND;
3167 IF (l_counter >= p_request_obj.start_record_position) AND
3168 (l_record_count <= p_request_obj.records_requested)
3169 THEN
3170 l_record_count := l_record_count + 1;
3171 x_items_array.extend;
3172 x_items_array(l_record_count).hierarchy_level := l_category_id;
3173 x_items_array(l_record_count).id := l_item_id;
3174 x_items_array(l_record_count).name := l_item_name;
3175 /*
3176 x_items_array(l_record_count) :=
3177 amv_cat_hierarchy_obj_type( l_category_id,
3178 l_item_id,
3182 EXIT WHEN l_record_count = p_request_obj.records_requested;
3179 l_item_name);
3180 */
3181 END IF;
3183 l_counter := l_counter + 1;
3184 END LOOP;
3185 CLOSE Get_CategoryItems_csr;
3186 ELSE
3187 l_category_hr := amv_cat_hierarchy_varray_type();
3188 Get_CategoryHierarchy(p_category_id, l_category_level, l_category_hr);
3189
3190 FOR i IN 1..l_category_hr.count LOOP
3191 l_category_id := l_category_hr(i).id;
3192
3193 -- get the total number of items in category
3194 OPEN Get_ItemsTotal_csr;
3195 FETCH Get_ItemsTotal_csr INTO l_temp_total;
3196 CLOSE Get_ItemsTotal_csr;
3197 l_total_count := l_total_count + l_temp_total;
3198
3199 OPEN Get_CategoryItems_csr;
3200 LOOP
3201 FETCH Get_CategoryItems_csr INTO l_item_id, l_item_name;
3202 EXIT WHEN Get_CategoryItems_csr%NOTFOUND;
3203 IF (l_counter >= p_request_obj.start_record_position) AND
3204 (l_record_count <= p_request_obj.records_requested)
3205 THEN
3206 l_record_count := l_record_count + 1;
3207 x_items_array.extend;
3208 x_items_array(l_record_count).hierarchy_level := l_category_id;
3209 x_items_array(l_record_count).id := l_item_id;
3210 x_items_array(l_record_count).name := l_item_name;
3211 /*
3212 x_items_array(l_record_count) :=
3213 amv_cat_hierarchy_obj_type( l_category_id,
3214 l_item_id,
3215 l_item_name);
3216 */
3217 END IF;
3218 EXIT WHEN l_record_count = p_request_obj.records_requested;
3219 IF p_request_obj.start_record_position > l_temp_total THEN
3220 exit;
3221 END IF;
3222 l_counter := l_counter + 1;
3223 END LOOP;
3224 CLOSE Get_CategoryItems_csr;
3225
3226 END LOOP;
3227 END IF;
3228 x_return_obj.returned_record_count := l_record_count;
3229 x_return_obj.next_record_position := l_counter + 1;
3230 x_return_obj.total_record_count := l_total_count;
3231 /*
3232 x_return_obj := amv_return_obj_type( l_record_count,
3233 l_counter + 1,
3234 l_total_count);
3235 */
3236 ELSE
3237 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3238 THEN
3239 FND_MESSAGE.Set_Name('AMV', 'AMV_CATEGORY_ID_INVALID');
3240 FND_MESSAGE.Set_Token('TKN',p_category_id);
3241 FND_MSG_PUB.Add;
3242 END IF;
3243 RAISE FND_API.G_EXC_ERROR;
3244 END IF;
3245 --
3246
3247 -- Debug Message
3248 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3249 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3250 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
3251 FND_MSG_PUB.Add;
3252 END IF;
3253 --Standard call to get message count and if count=1, get the message
3254 FND_MSG_PUB.Count_And_Get (
3255 p_encoded => FND_API.G_FALSE,
3256 p_count => x_msg_count,
3257 p_data => x_msg_data
3258 );
3259 EXCEPTION
3260 WHEN FND_API.G_EXC_ERROR THEN
3261 ROLLBACK TO Get_ItemsPerCategory_PVT;
3262 x_return_status := FND_API.G_RET_STS_ERROR;
3263 -- Standard call to get message count and if count=1, get the message
3264 FND_MSG_PUB.Count_And_Get (
3265 p_encoded => FND_API.G_FALSE,
3266 p_count => x_msg_count,
3267 p_data => x_msg_data
3268 );
3269 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3270 ROLLBACK TO Get_ItemsPerCategory_PVT;
3271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3272 -- Standard call to get message count and if count=1, get the message
3273 FND_MSG_PUB.Count_And_Get (
3274 p_encoded => FND_API.G_FALSE,
3275 p_count => x_msg_count,
3276 p_data => x_msg_data
3277 );
3278 WHEN OTHERS THEN
3279 ROLLBACK TO Get_ItemsPerCategory_PVT;
3280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3281 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3282 THEN
3283 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3284 END IF;
3285 -- Standard call to get message count and if count=1, get the message
3286 FND_MSG_PUB.Count_And_Get (
3287 p_encoded => FND_API.G_FALSE,
3288 p_count => x_msg_count,
3289 p_data => x_msg_data
3290 );
3291 --
3292 END Get_ItemsPerCategory;
3293 --------------------------------------------------------------------------------
3294 --------------------------------------------------------------------------------
3295 --
3296 -- Start of comments
3297 -- API name : Fetch_CategoryId
3298 -- Type : Private
3299 -- Pre-reqs : None
3300 -- Function : returns category id for a category or subcategory name.
3301 --
3302 -- Start of comments
3303 -- API name : Fetch_CategoryId
3304 -- Type : Private
3305 -- Pre-reqs : None
3309 -- p_init_msg_list IN VARCHAR2 Optional
3306 -- Function : returns category id for a category or subcategory name.
3307 -- Parameters :
3308 -- IN p_api_version IN NUMBER Required
3310 -- Default = FND_API.G_FALSE
3311 -- p_validation_level IN NUMBER Optional
3312 -- Default = FND_API.G_VALID_LEVEL_FULL
3313 -- p_category_name IN VARCHAR2 Required
3314 -- (sub)category id
3315 -- p_parent_category_id IN NUMBER Optional
3316 -- Default = FND_API.G_MISS_NUM
3317 -- parent category id
3318 -- p_parent_category_name IN VARCHAR2 Optional
3319 -- Default = FND_API.G_MISS_CHAR
3320 -- parent category name
3321 -- parent id or name required for subcategory name
3322 -- OUT : x_return_status OUT VARCHAR2
3323 -- x_msg_count OUT NUMBER
3324 -- x_msg_data OUT VARCHAR2
3325 -- x_category_id OUT NUMBER
3326 -- Version : Current version 1.0
3327 -- Previous version 1.0
3328 -- Initial version 1.0
3329 -- Notes :
3330 -- End of comments
3331 --
3332 --
3333 PROCEDURE Fetch_CategoryId
3334 ( p_api_version IN NUMBER,
3335 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3336 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3337 x_return_status OUT NOCOPY VARCHAR2,
3338 x_msg_count OUT NOCOPY NUMBER,
3339 x_msg_data OUT NOCOPY VARCHAR2,
3340 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3341 p_application_id IN NUMBER := AMV_UTILITY_PVT.G_AMV_APP_ID,
3342 p_category_name IN VARCHAR2,
3343 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
3344 p_parent_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3345 x_category_id OUT NOCOPY NUMBER
3346 )
3347 IS
3348 l_api_name CONSTANT VARCHAR2(30) := 'Fetch_CategoryId';
3349 l_api_version CONSTANT NUMBER := 1.0;
3350 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3351 --
3352 l_resource_id number;
3353 l_user_id number;
3354 l_login_user_id number;
3355 l_login_user_status varchar2(30);
3356 l_Error_Msg varchar2(2000);
3357 l_Error_Token varchar2(80);
3358 l_object_version_number number;
3359 l_application_id number;
3360 --
3361 l_category_id number;
3362 l_category_exist_flag varchar2(1);
3363 --
3364 BEGIN
3365 -- Standard begin of API savepoint
3366 SAVEPOINT Fetch_CategoryId_PVT;
3367 -- Standard call to check for call compatibility.
3368 IF NOT FND_API.Compatible_API_Call (
3369 l_api_version,
3370 p_api_version,
3371 l_api_name,
3372 G_PKG_NAME)
3373 THEN
3374 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3375 END IF;
3376 -- Debug Message
3377 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3378 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3379 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
3380 FND_MSG_PUB.Add;
3381 END IF;
3382 --Initialize message list if p_init_msg_list is TRUE.
3383 IF FND_API.To_Boolean (p_init_msg_list) THEN
3384 FND_MSG_PUB.initialize;
3385 END IF;
3386 -- Get the current (login) user id.
3387 AMV_UTILITY_PVT.Get_UserInfo(
3388 x_resource_id => l_resource_id,
3389 x_user_id => l_user_id,
3390 x_login_id => l_login_user_id,
3391 x_user_status => l_login_user_status
3392 );
3393 -- check login user
3394 IF (p_check_login_user = FND_API.G_TRUE) THEN
3395 -- Check if user is login and has the required privilege.
3396 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3397 -- User is not login.
3398 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3399 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3400 FND_MSG_PUB.Add;
3401 END IF;
3402 RAISE FND_API.G_EXC_ERROR;
3403 END IF;
3404 END IF;
3405 -- This fix is for executing api in sqlplus mode
3406 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3407 l_login_user_id := g_login_user_id;
3408 l_user_id := g_user_id;
3409 l_resource_id := g_resource_id;
3410 END IF;
3411 -- Initialize API return status to sucess
3412 x_return_status := FND_API.G_RET_STS_SUCCESS;
3413
3414 --
3415 Validate_CategoryStatus(
3416 x_return_status => x_return_status,
3417 p_category_name => p_category_name,
3418 p_parent_category_id => p_parent_category_id,
3419 p_parent_category_name => p_parent_category_name,
3420 x_exist_flag => l_category_exist_flag,
3421 x_category_id => x_category_id,
3422 x_error_msg => l_Error_Msg,
3423 x_error_token => l_Error_Token
3424 );
3425 --
3426 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3427 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3428 THEN
3429 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
3430 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
3431 FND_MSG_PUB.Add;
3432 END IF;
3433 RAISE FND_API.G_EXC_ERROR;
3434 END IF;
3435
3436 -- Debug Message
3437 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3438 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3439 FND_MESSAGE.Set_Token('ROW',l_full_name||': END');
3440 FND_MSG_PUB.Add;
3441 END IF;
3442 --Standard call to get message count and if count=1, get the message
3443 FND_MSG_PUB.Count_And_Get (
3444 p_encoded => FND_API.G_FALSE,
3445 p_count => x_msg_count,
3446 p_data => x_msg_data
3447 );
3448 EXCEPTION
3449 WHEN FND_API.G_EXC_ERROR THEN
3450 ROLLBACK TO Fetch_CategoryId_PVT;
3451 x_return_status := FND_API.G_RET_STS_ERROR;
3452 -- Standard call to get message count and if count=1, get the message
3453 FND_MSG_PUB.Count_And_Get (
3454 p_encoded => FND_API.G_FALSE,
3455 p_count => x_msg_count,
3456 p_data => x_msg_data
3457 );
3458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3459 ROLLBACK TO Fetch_CategoryId_PVT;
3460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3461 -- Standard call to get message count and if count=1, get the message
3462 FND_MSG_PUB.Count_And_Get (
3463 p_encoded => FND_API.G_FALSE,
3464 p_count => x_msg_count,
3465 p_data => x_msg_data
3466 );
3467 WHEN OTHERS THEN
3468 ROLLBACK TO Fetch_CategoryId_PVT;
3469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3470 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3471 THEN
3472 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3473 END IF;
3474 -- Standard call to get message count and if count=1, get the message
3475 FND_MSG_PUB.Count_And_Get (
3476 p_encoded => FND_API.G_FALSE,
3477 p_count => x_msg_count,
3478 p_data => x_msg_data
3479 );
3480 --
3481 END Fetch_CategoryId;
3482 --------------------------------------------------------------------------------
3483 --------------------------------------------------------------------------------
3484 --
3485 -- Start of comments
3486 -- API name : Get_CatParentsHierarchy
3487 -- Type : Private
3488 -- Pre-reqs : None
3489 -- Function : Return parents hierarchy of category name and ids
3490 -- for a category id.
3491 -- Parameters :
3492 -- IN p_api_version IN NUMBER Required
3493 -- p_init_msg_list IN VARCHAR2 Optional
3494 -- Default = FND_API.G_FALSE
3495 -- p_validation_level IN NUMBER Optional
3496 -- Default = FND_API.G_VALID_LEVEL_FULL
3497 -- p_category_id IN NUMBER Required
3498 -- (sub)category id
3499 -- OUT : x_return_status OUT VARCHAR2
3500 -- x_msg_count OUT NUMBER
3501 -- x_msg_data OUT VARCHAR2
3502 -- x_category_hierarchy OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
3503 -- Version : Current version 1.0
3504 -- Previous version 1.0
3505 -- Initial version 1.0
3506 -- Notes :
3507 -- End of comments
3508 --
3509 --
3510 PROCEDURE Get_CatParentsHierarchy
3511 ( p_api_version IN NUMBER,
3512 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3513 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3514 x_return_status OUT NOCOPY VARCHAR2,
3515 x_msg_count OUT NOCOPY NUMBER,
3516 x_msg_data OUT NOCOPY VARCHAR2,
3517 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3518 p_category_id IN NUMBER,
3519 x_category_hierarchy OUT NOCOPY AMV_CAT_HIERARCHY_VARRAY_TYPE
3520 )
3521 IS
3522 l_api_name CONSTANT VARCHAR2(30) := 'Get_CatParentsHierarchy';
3523 l_api_version CONSTANT NUMBER := 1.0;
3524 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3525 --
3526 l_resource_id number;
3527 l_user_id number;
3528 l_login_user_id number;
3529 l_login_user_status varchar2(30);
3530 l_Error_Msg varchar2(2000);
3531 l_Error_Token varchar2(80);
3532 l_object_version_number number;
3533 l_application_id number;
3534 --
3535 l_category_level number := 1;
3536 --
3537 BEGIN
3538 -- Standard begin of API savepoint
3539 SAVEPOINT Get_CatParentsHrPVT;
3540 -- Standard call to check for call compatibility.
3541 IF NOT FND_API.Compatible_API_Call (
3542 l_api_version,
3543 p_api_version,
3544 l_api_name,
3545 G_PKG_NAME)
3546 THEN
3547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3548 END IF;
3549 -- Debug Message
3550 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3551 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3552 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
3553 FND_MSG_PUB.Add;
3554 END IF;
3555 --Initialize message list if p_init_msg_list is TRUE.
3556 IF FND_API.To_Boolean (p_init_msg_list) THEN
3557 FND_MSG_PUB.initialize;
3558 END IF;
3559 -- Get the current (login) user id.
3560 AMV_UTILITY_PVT.Get_UserInfo(
3561 x_resource_id => l_resource_id,
3562 x_user_id => l_user_id,
3563 x_login_id => l_login_user_id,
3564 x_user_status => l_login_user_status
3565 );
3566 -- check login user
3567 IF (p_check_login_user = FND_API.G_TRUE) THEN
3568 -- Check if user is login and has the required privilege.
3569 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3570 -- User is not login.
3571 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3572 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3576 END IF;
3573 FND_MSG_PUB.Add;
3574 END IF;
3575 RAISE FND_API.G_EXC_ERROR;
3577 END IF;
3578 -- This fix is for executing api in sqlplus mode
3579 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3580 l_login_user_id := g_login_user_id;
3581 l_user_id := g_user_id;
3582 l_resource_id := g_resource_id;
3583 END IF;
3584 -- Initialize API return status to sucess
3585 x_return_status := FND_API.G_RET_STS_SUCCESS;
3586
3587 --
3588 IF AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) THEN
3589
3590 x_category_hierarchy := amv_cat_hierarchy_varray_type();
3591
3592 Get_CategoryParents(p_category_id,
3593 l_category_level,
3594 x_category_hierarchy);
3595
3596 ELSE
3597 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3598 THEN
3599 FND_MESSAGE.Set_Name('AMV', 'AMV_CATEGORY_ID_INVALID');
3600 FND_MESSAGE.Set_Token('TKN',p_category_id);
3601 FND_MSG_PUB.Add;
3602 END IF;
3603 RAISE FND_API.G_EXC_ERROR;
3604 END IF;
3605 --
3606
3607 -- Debug Message
3608 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3609 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3610 FND_MESSAGE.Set_Token('ROW',l_full_name||': END');
3611 FND_MSG_PUB.Add;
3612 END IF;
3613 --Standard call to get message count and if count=1, get the message
3614 FND_MSG_PUB.Count_And_Get (
3615 p_encoded => FND_API.G_FALSE,
3616 p_count => x_msg_count,
3617 p_data => x_msg_data
3618 );
3619 EXCEPTION
3620 WHEN FND_API.G_EXC_ERROR THEN
3621 ROLLBACK TO Get_CatParentsHrPVT;
3622 x_return_status := FND_API.G_RET_STS_ERROR;
3623 -- Standard call to get message count and if count=1, get the message
3624 FND_MSG_PUB.Count_And_Get (
3625 p_encoded => FND_API.G_FALSE,
3626 p_count => x_msg_count,
3627 p_data => x_msg_data
3628 );
3629 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3630 ROLLBACK TO Get_CatParentsHrPVT;
3631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3632 -- Standard call to get message count and if count=1, get the message
3633 FND_MSG_PUB.Count_And_Get (
3634 p_encoded => FND_API.G_FALSE,
3635 p_count => x_msg_count,
3636 p_data => x_msg_data
3637 );
3638 WHEN OTHERS THEN
3639 ROLLBACK TO Get_CatParentsHrPVT;
3640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3641 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3642 THEN
3643 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3644 END IF;
3645 -- Standard call to get message count and if count=1, get the message
3646 FND_MSG_PUB.Count_And_Get (
3647 p_encoded => FND_API.G_FALSE,
3648 p_count => x_msg_count,
3649 p_data => x_msg_data
3650 );
3651 --
3652 END Get_CatParentsHierarchy;
3653 --------------------------------------------------------------------------------
3654 --------------------------------------------------------------------------------
3655 --
3656 -- Start of comments
3657 -- API name : Get_CatChildrenHierarchy
3658 -- Type : Private
3659 -- Pre-reqs : None
3660 -- Function : Return children hierarchy of category name and ids
3661 -- for a category id.
3662 -- Parameters :
3663 -- IN p_api_version IN NUMBER Required
3664 -- p_init_msg_list IN VARCHAR2 Optional
3665 -- Default = FND_API.G_FALSE
3666 -- p_validation_level IN NUMBER Optional
3667 -- Default = FND_API.G_VALID_LEVEL_FULL
3668 -- p_category_id IN NUMBER Required
3669 -- (sub)category id
3670 -- OUT : x_return_status OUT VARCHAR2
3671 -- x_msg_count OUT NUMBER
3672 -- x_msg_data OUT VARCHAR2
3673 -- x_category_hierarchy OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
3674 -- Version : Current version 1.0
3675 -- Previous version 1.0
3676 -- Initial version 1.0
3677 -- Notes :
3678 -- End of comments
3679 --
3680 --
3681 PROCEDURE Get_CatChildrenHierarchy
3682 ( p_api_version IN NUMBER,
3683 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3684 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3685 x_return_status OUT NOCOPY VARCHAR2,
3686 x_msg_count OUT NOCOPY NUMBER,
3687 x_msg_data OUT NOCOPY VARCHAR2,
3688 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3689 p_category_id IN NUMBER,
3690 x_category_hierarchy OUT NOCOPY AMV_CAT_HIERARCHY_VARRAY_TYPE
3691 )
3692 IS
3693 l_api_name CONSTANT VARCHAR2(30) := 'Get_CatChildrenHierarchy';
3694 l_api_version CONSTANT NUMBER := 1.0;
3695 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3696 --
3697 l_resource_id number;
3698 l_user_id number;
3699 l_login_user_id number;
3700 l_login_user_status varchar2(30);
3701 l_Error_Msg varchar2(2000);
3702 l_Error_Token varchar2(80);
3703 l_object_version_number number;
3704 l_application_id number;
3705 --
3706 l_category_level number := 1;
3707 --
3708 BEGIN
3712 IF NOT FND_API.Compatible_API_Call (
3709 -- Standard begin of API savepoint
3710 SAVEPOINT Get_CatChildrenHrPVT;
3711 -- Standard call to check for call compatibility.
3713 l_api_version,
3714 p_api_version,
3715 l_api_name,
3716 G_PKG_NAME)
3717 THEN
3718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3719 END IF;
3720 -- Debug Message
3721 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3722 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3723 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
3724 FND_MSG_PUB.Add;
3725 END IF;
3726 --Initialize message list if p_init_msg_list is TRUE.
3727 IF FND_API.To_Boolean (p_init_msg_list) THEN
3728 FND_MSG_PUB.initialize;
3729 END IF;
3730 -- Get the current (login) user id.
3731 AMV_UTILITY_PVT.Get_UserInfo(
3732 x_resource_id => l_resource_id,
3733 x_user_id => l_user_id,
3734 x_login_id => l_login_user_id,
3735 x_user_status => l_login_user_status
3736 );
3737 -- check login user
3738 IF (p_check_login_user = FND_API.G_TRUE) THEN
3739 -- Check if user is login and has the required privilege.
3740 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3741 -- User is not login.
3742 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3743 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3744 FND_MSG_PUB.Add;
3745 END IF;
3746 RAISE FND_API.G_EXC_ERROR;
3747 END IF;
3748 END IF;
3749 -- This fix is for executing api in sqlplus mode
3750 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3751 l_login_user_id := g_login_user_id;
3752 l_user_id := g_user_id;
3753 l_resource_id := g_resource_id;
3754 END IF;
3755 -- Initialize API return status to sucess
3756 x_return_status := FND_API.G_RET_STS_SUCCESS;
3757
3758 --
3759 IF AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) THEN
3760
3761 x_category_hierarchy := amv_cat_hierarchy_varray_type();
3762
3763 Get_CategoryHierarchy(p_category_id,
3764 l_category_level,
3765 x_category_hierarchy);
3766
3767 ELSE
3768 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3769 THEN
3770 FND_MESSAGE.Set_Name('AMV', 'AMV_CATEGORY_ID_INVALID');
3771 FND_MESSAGE.Set_Token('TKN',p_category_id);
3772 FND_MSG_PUB.Add;
3773 END IF;
3774 RAISE FND_API.G_EXC_ERROR;
3775 END IF;
3776 --
3777
3778 -- Debug Message
3779 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3780 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3781 FND_MESSAGE.Set_Token('ROW',l_full_name||': END');
3782 FND_MSG_PUB.Add;
3783 END IF;
3784 --Standard call to get message count and if count=1, get the message
3785 FND_MSG_PUB.Count_And_Get (
3786 p_encoded => FND_API.G_FALSE,
3787 p_count => x_msg_count,
3788 p_data => x_msg_data
3789 );
3790 EXCEPTION
3791 WHEN FND_API.G_EXC_ERROR THEN
3792 ROLLBACK TO Get_CatChildrenHrPVT;
3793 x_return_status := FND_API.G_RET_STS_ERROR;
3794 -- Standard call to get message count and if count=1, get the message
3795 FND_MSG_PUB.Count_And_Get (
3796 p_encoded => FND_API.G_FALSE,
3797 p_count => x_msg_count,
3798 p_data => x_msg_data
3799 );
3800 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3801 ROLLBACK TO Get_CatChildrenHrPVT;
3802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3803 -- Standard call to get message count and if count=1, get the message
3804 FND_MSG_PUB.Count_And_Get (
3805 p_encoded => FND_API.G_FALSE,
3806 p_count => x_msg_count,
3807 p_data => x_msg_data
3808 );
3809 WHEN OTHERS THEN
3810 ROLLBACK TO Get_CatChildrenHrPVT;
3811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3812 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3813 THEN
3814 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3815 END IF;
3816 -- Standard call to get message count and if count=1, get the message
3817 FND_MSG_PUB.Count_And_Get (
3818 p_encoded => FND_API.G_FALSE,
3819 p_count => x_msg_count,
3820 p_data => x_msg_data
3821 );
3822 --
3823 END Get_CatChildrenHierarchy;
3824 --------------------------------------------------------------------------------
3825 --------------------------------------------------------------------------------
3826 --
3827 -- Start of comments
3828 -- API name : Get_ChnCategoryHierarchy
3829 -- Type : Private
3830 -- Pre-reqs : None
3831 -- Function : Return parents hierarchy of category name and ids
3832 -- for a channel id.
3833 -- Parameters :
3834 -- IN p_api_version IN NUMBER Required
3835 -- p_init_msg_list IN VARCHAR2 Optional
3836 -- Default = FND_API.G_FALSE
3837 -- p_validation_level IN NUMBER Optional
3838 -- Default = FND_API.G_VALID_LEVEL_FULL
3839 -- p_channel_id IN NUMBER Required
3840 -- channel id
3841 -- OUT : x_return_status OUT VARCHAR2
3842 -- x_msg_count OUT NUMBER
3846 -- Version : Current version 1.0
3843 -- x_msg_data OUT VARCHAR2
3844 -- x_channel_name OUT VARCHAR2
3845 -- x_category_hierarchy OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
3847 -- Previous version 1.0
3848 -- Initial version 1.0
3849 -- Notes :
3850 -- End of comments
3851 --
3852 --
3853 PROCEDURE Get_ChnCategoryHierarchy
3854 ( p_api_version IN NUMBER,
3855 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3856 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3857 x_return_status OUT NOCOPY VARCHAR2,
3858 x_msg_count OUT NOCOPY NUMBER,
3859 x_msg_data OUT NOCOPY VARCHAR2,
3860 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3861 p_channel_id IN NUMBER,
3862 x_channel_name OUT NOCOPY VARCHAR2,
3863 x_category_hierarchy OUT NOCOPY AMV_CAT_HIERARCHY_VARRAY_TYPE
3864 )
3865 IS
3866 l_api_name CONSTANT VARCHAR2(30) := 'Get_ChnCategoryHierarchy';
3867 l_api_version CONSTANT NUMBER := 1.0;
3868 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3869 --
3870 l_resource_id number;
3871 l_user_id number;
3872 l_login_user_id number;
3873 l_login_user_status varchar2(30);
3874 l_Error_Msg varchar2(2000);
3875 l_Error_Token varchar2(80);
3876 l_object_version_number number;
3877 l_application_id number;
3878 --
3879 l_category_level number := 1;
3880 l_category_id number;
3881 --
3882 CURSOR Get_ChannelData IS
3883 select tl.channel_name
3884 , b.channel_category_id
3885 from amv_c_channels_b b
3886 , amv_c_channels_tl tl
3887 where b.channel_id = p_channel_id
3888 and b.channel_id = tl.channel_id
3889 and tl.language = userenv('lang');
3890 --
3891 BEGIN
3892 -- Standard begin of API savepoint
3893 SAVEPOINT Get_ChnCategoryHierarchy;
3894 -- Standard call to check for call compatibility.
3895 IF NOT FND_API.Compatible_API_Call (
3896 l_api_version,
3897 p_api_version,
3898 l_api_name,
3899 G_PKG_NAME)
3900 THEN
3901 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3902 END IF;
3903 -- Debug Message
3904 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3905 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3906 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
3907 FND_MSG_PUB.Add;
3908 END IF;
3909 --Initialize message list if p_init_msg_list is TRUE.
3910 IF FND_API.To_Boolean (p_init_msg_list) THEN
3911 FND_MSG_PUB.initialize;
3912 END IF;
3913 -- Get the current (login) user id.
3914 AMV_UTILITY_PVT.Get_UserInfo(
3915 x_resource_id => l_resource_id,
3916 x_user_id => l_user_id,
3917 x_login_id => l_login_user_id,
3918 x_user_status => l_login_user_status
3919 );
3920 -- check login user
3921 IF (p_check_login_user = FND_API.G_TRUE) THEN
3922 -- Check if user is login and has the required privilege.
3923 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3924 -- User is not login.
3925 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3926 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3927 FND_MSG_PUB.Add;
3928 END IF;
3929 RAISE FND_API.G_EXC_ERROR;
3930 END IF;
3931 END IF;
3932 -- This fix is for executing api in sqlplus mode
3933 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3934 l_login_user_id := g_login_user_id;
3935 l_user_id := g_user_id;
3936 l_resource_id := g_resource_id;
3937 END IF;
3941 --
3938 -- Initialize API return status to sucess
3939 x_return_status := FND_API.G_RET_STS_SUCCESS;
3940
3942 IF AMV_UTILITY_PVT.Is_ChannelIdValid(p_channel_id) THEN
3943 OPEN Get_ChannelData;
3944 FETCH Get_ChannelData INTO x_channel_name, l_category_id;
3945 CLOSE Get_ChannelData;
3946
3947 x_category_hierarchy := amv_cat_hierarchy_varray_type();
3948
3949 Get_CategoryParents(l_category_id,
3950 l_category_level,
3951 x_category_hierarchy);
3952 ELSE
3953 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3954 THEN
3955 FND_MESSAGE.Set_Name('AMV', 'AMV_CHANNEL_ID_INVALID');
3956 FND_MESSAGE.Set_Token('TKN',p_channel_id);
3957 FND_MSG_PUB.Add;
3958 END IF;
3959 RAISE FND_API.G_EXC_ERROR;
3960 END IF;
3961 --
3962
3963 -- Debug Message
3964 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3965 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3966 FND_MESSAGE.Set_Token('ROW',l_full_name||': END');
3967 FND_MSG_PUB.Add;
3968 END IF;
3969 --Standard call to get message count and if count=1, get the message
3970 FND_MSG_PUB.Count_And_Get (
3971 p_encoded => FND_API.G_FALSE,
3972 p_count => x_msg_count,
3973 p_data => x_msg_data
3974 );
3975 EXCEPTION
3976 WHEN FND_API.G_EXC_ERROR THEN
3977 ROLLBACK TO Get_ChnCategoryHierarchy;
3978 x_return_status := FND_API.G_RET_STS_ERROR;
3979 -- Standard call to get message count and if count=1, get the message
3980 FND_MSG_PUB.Count_And_Get (
3981 p_encoded => FND_API.G_FALSE,
3982 p_count => x_msg_count,
3983 p_data => x_msg_data
3984 );
3985 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3986 ROLLBACK TO Get_ChnCategoryHierarchy;
3987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3988 -- Standard call to get message count and if count=1, get the message
3989 FND_MSG_PUB.Count_And_Get (
3990 p_encoded => FND_API.G_FALSE,
3991 p_count => x_msg_count,
3992 p_data => x_msg_data
3993 );
3994 WHEN OTHERS THEN
3995 ROLLBACK TO Get_ChnCategoryHierarchy;
3996 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3997 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3998 THEN
3999 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4000 END IF;
4001 -- Standard call to get message count and if count=1, get the message
4002 FND_MSG_PUB.Count_And_Get (
4003 p_encoded => FND_API.G_FALSE,
4004 p_count => x_msg_count,
4005 p_data => x_msg_data
4006 );
4007 --
4008 END Get_ChnCategoryHierarchy;
4009 --------------------------------------------------------------------------------
4010 --------------------------------------------------------------------------------
4011 --
4012 -- Start of comments
4013 -- API name : Add_CategoryParent
4014 -- Type : Private
4015 -- Pre-reqs : None
4016 -- Function : attaches a category to a parent category
4017 -- Parameters :
4018 -- IN p_api_version IN NUMBER Required
4019 -- p_init_msg_list IN VARCHAR2 Optional
4020 -- Default = FND_API.G_FALSE
4021 -- p_validation_level IN NUMBER Optional
4022 -- Default = FND_API.G_VALID_LEVEL_FULL
4023 -- p_category_id IN NUMBER Required
4024 -- category id
4025 -- p_parent_category_id IN NUMBER Required
4026 -- parent category id
4027 -- p_replace_existing IN VARCHAR2 Optional
4028 -- Default = FND_API.G_FALSE
4029 -- OUT : x_return_status OUT VARCHAR2
4030 -- x_msg_count OUT NUMBER
4031 -- x_msg_data OUT VARCHAR2
4032 -- Version : Current version 1.0
4033 -- Previous version 1.0
4034 -- Initial version 1.0
4035 -- Notes :
4036 -- End of comments
4037 --
4038 --
4039 PROCEDURE Add_CategoryParent
4040 ( p_api_version IN NUMBER,
4041 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4042 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4043 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4044 x_return_status OUT NOCOPY VARCHAR2,
4045 x_msg_count OUT NOCOPY NUMBER,
4046 x_msg_data OUT NOCOPY VARCHAR2,
4047 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
4048 p_object_version_number IN NUMBER,
4049 p_category_id IN NUMBER,
4050 p_parent_category_id IN NUMBER,
4051 p_replace_existing IN VARCHAR2 := FND_API.G_FALSE
4052 )
4053 IS
4054 l_api_name CONSTANT VARCHAR2(30) := 'Add_CategoryParent';
4055 l_api_version CONSTANT NUMBER := 1.0;
4056 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
4057 --
4058 l_resource_id number;
4059 l_user_id number;
4060 l_login_user_id number;
4061 l_login_user_status varchar2(30);
4062 l_Error_Msg varchar2(2000);
4063 l_Error_Token varchar2(80);
4064 --
4065 l_category_level number := 1;
4066 l_category_hr amv_cat_hierarchy_varray_type;
4067 l_parent_category_id number;
4068 l_object_version_number number;
4069 l_application_id number;
4073 l_subcat_name varchar2(80);
4070 l_order number;
4071 l_channel_count number;
4072 l_update_flag varchar2(1) := FND_API.G_FALSE;
4074 l_cat_name varchar2(80);
4075 --
4076 CURSOR Get_ParentId IS
4077 select parent_channel_category_id
4078 , channel_category_name
4079 , object_version_number
4080 , application_id
4081 , channel_category_order
4082 , channel_count
4083 from amv_c_categories_vl
4084 where channel_category_id = p_category_id;
4085
4086 CURSOR Get_SubCatName IS
4087 select channel_category_name
4088 from amv_c_categories_vl
4089 where parent_channel_category_id = p_parent_category_id;
4090
4091 --
4092 BEGIN
4093 -- Standard begin of API savepoint
4094 SAVEPOINT Add_CategoryParent;
4095 -- Standard call to check for call compatibility.
4096 IF NOT FND_API.Compatible_API_Call (
4097 l_api_version,
4098 p_api_version,
4099 l_api_name,
4100 G_PKG_NAME)
4101 THEN
4102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4103 END IF;
4104 -- Debug Message
4105 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4106 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
4107 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
4108 FND_MSG_PUB.Add;
4109 END IF;
4110 --Initialize message list if p_init_msg_list is TRUE.
4111 IF FND_API.To_Boolean (p_init_msg_list) THEN
4112 FND_MSG_PUB.initialize;
4113 END IF;
4114 -- Get the current (login) user id.
4115 AMV_UTILITY_PVT.Get_UserInfo(
4116 x_resource_id => l_resource_id,
4117 x_user_id => l_user_id,
4118 x_login_id => l_login_user_id,
4119 x_user_status => l_login_user_status
4120 );
4121 -- check login user
4122 IF (p_check_login_user = FND_API.G_TRUE) THEN
4123 -- Check if user is login and has the required privilege.
4124 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
4125 -- User is not login.
4126 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4127 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
4128 FND_MSG_PUB.Add;
4129 END IF;
4130 RAISE FND_API.G_EXC_ERROR;
4131 END IF;
4132 END IF;
4133 -- This fix is for executing api in sqlplus mode
4134 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
4135 l_login_user_id := g_login_user_id;
4136 l_user_id := g_user_id;
4137 l_resource_id := g_resource_id;
4138 END IF;
4139 -- Initialize API return status to sucess
4140 x_return_status := FND_API.G_RET_STS_SUCCESS;
4141
4142 --
4143 IF AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) THEN
4144 IF AMV_UTILITY_PVT.Is_CategoryIdValid(p_parent_category_id) THEN
4145 OPEN Get_ParentId;
4146 FETCH Get_ParentId INTO l_parent_category_id,
4147 l_cat_name,
4148 l_object_version_number,
4149 l_application_id,
4150 l_order,
4151 l_channel_count;
4152 CLOSE Get_ParentId;
4153 IF l_parent_category_id is null THEN
4154 l_update_flag := FND_API.G_TRUE;
4155 ELSE
4156 IF p_replace_existing = FND_API.G_TRUE THEN
4157 l_update_flag := FND_API.G_TRUE;
4158 ELSE
4159 l_update_flag := FND_API.G_FALSE;
4160 END IF;
4161 END IF;
4162 ELSE
4163 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4164 THEN
4165 FND_MESSAGE.Set_Name('AMV', 'AMV_CATEGORY_ID_INVALID');
4166 FND_MESSAGE.Set_Token('TKN',p_parent_category_id);
4167 FND_MSG_PUB.Add;
4168 END IF;
4169 RAISE FND_API.G_EXC_ERROR;
4170 END IF;
4171 ELSE
4172 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4173 THEN
4174 FND_MESSAGE.Set_Name('AMV', 'AMV_CATEGORY_ID_INVALID');
4175 FND_MESSAGE.Set_Token('TKN',p_category_id);
4176 FND_MSG_PUB.Add;
4177 END IF;
4178 RAISE FND_API.G_EXC_ERROR;
4179 END IF;
4180
4181
4182 -- update parent
4183 IF l_update_flag = FND_API.G_TRUE THEN
4184 IF p_object_version_number = l_object_version_number THEN
4185 -- check to see if parent is not its child
4186 l_category_hr := amv_cat_hierarchy_varray_type();
4187 Get_CategoryHierarchy(p_category_id, l_category_level, l_category_hr);
4188 FOR i in 1..l_category_hr.count LOOP
4189 IF l_category_hr(i).id = p_parent_category_id THEN
4190 l_update_flag := FND_API.G_FALSE;
4191 END IF;
4192 EXIT WHEN l_category_hr(i).id = p_parent_category_id;
4193 END LOOP;
4194 IF l_update_flag = FND_API.G_FALSE THEN
4195 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4196 THEN
4197 FND_MESSAGE.Set_Name('AMV', 'AMV_CAT_PARENT_LOOPING');
4198 FND_MESSAGE.Set_Token('TKN',p_parent_category_id);
4199 FND_MSG_PUB.Add;
4200 END IF;
4204 -- check to see if no other category exist with same name under parent
4201 RAISE FND_API.G_EXC_ERROR;
4202 END IF;
4203
4205 OPEN Get_SubCatName;
4206 LOOP
4207 FETCH Get_SubCatName INTO l_subcat_name;
4208 EXIT WHEN Get_SubCatName%NOTFOUND;
4209 IF UPPER(l_subcat_name) = UPPER(l_cat_name) THEN
4210 l_update_flag := FND_API.G_FALSE;
4211 END IF;
4212 EXIT WHEN UPPER(l_subcat_name) = UPPER(l_cat_name);
4213 END LOOP;
4214 CLOSE Get_SubCatName;
4215 IF l_update_flag = FND_API.G_FALSE THEN
4216 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4217 THEN
4218 FND_MESSAGE.Set_Name('AMV', 'AMV_CAT_NAME_EXISTS');
4219 FND_MESSAGE.Set_Token('TKN',p_category_id);
4220 FND_MSG_PUB.Add;
4221 END IF;
4222 RAISE FND_API.G_EXC_ERROR;
4223 END IF;
4224
4225 BEGIN
4226 AMV_C_CATEGORIES_PKG.UPDATE_B_ROW(
4227 X_CHANNEL_CATEGORY_ID => p_category_id,
4228 X_APPLICATION_ID => l_application_id,
4229 X_OBJECT_VERSION_NUMBER => p_object_version_number + 1,
4230 X_CHANNEL_CATEGORY_ORDER => l_order,
4231 X_PARENT_CHANNEL_CATEGORY_ID => p_parent_category_id,
4232 X_CHANNEL_COUNT => l_channel_count,
4233 X_LAST_UPDATE_DATE => sysdate,
4234 X_LAST_UPDATED_BY => l_user_id,
4235 X_LAST_UPDATE_LOGIN => l_login_user_id
4236 );
4237 EXCEPTION
4238 WHEN OTHERS THEN
4239 --will log the error
4240 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4241 THEN
4242 FND_MESSAGE.Set_Name('AMV', 'AMV_TABLE_HANDLER_ERROR');
4243 FND_MESSAGE.Set_Token('ACTION', 'Updating');
4244 FND_MESSAGE.Set_Token('TABLE', 'Categories');
4245 FND_MSG_PUB.Add;
4246 END IF;
4247 RAISE FND_API.G_EXC_ERROR;
4248 END;
4249 ELSE
4250 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4251 THEN
4252 FND_MESSAGE.Set_Name('AMV', 'AMV_CAT_VERSION_CHANGE');
4253 FND_MSG_PUB.Add;
4254 END IF;
4255 RAISE FND_API.G_EXC_ERROR;
4256 END IF;
4257 ELSE
4258 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4259 THEN
4260 FND_MESSAGE.Set_Name('AMV', 'AMV_CAT_PARENT_EXISTS');
4261 FND_MESSAGE.Set_Token('TKN',l_parent_category_id);
4262 FND_MSG_PUB.Add;
4263 END IF;
4264 RAISE FND_API.G_EXC_ERROR;
4265 END IF;
4266 --
4267
4268 --Standard check of commit
4269 IF FND_API.To_Boolean ( p_commit ) THEN
4270 COMMIT WORK;
4271 END IF;
4272
4273 -- Debug Message
4274 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4275 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
4276 FND_MESSAGE.Set_Token('ROW',l_full_name||': END');
4277 FND_MSG_PUB.Add;
4278 END IF;
4279 --Standard call to get message count and if count=1, get the message
4280 FND_MSG_PUB.Count_And_Get (
4281 p_encoded => FND_API.G_FALSE,
4282 p_count => x_msg_count,
4283 p_data => x_msg_data
4284 );
4285 EXCEPTION
4286 WHEN FND_API.G_EXC_ERROR THEN
4287 ROLLBACK TO Add_CategoryParent;
4288 x_return_status := FND_API.G_RET_STS_ERROR;
4289 -- Standard call to get message count and if count=1, get the message
4290 FND_MSG_PUB.Count_And_Get (
4291 p_encoded => FND_API.G_FALSE,
4292 p_count => x_msg_count,
4293 p_data => x_msg_data
4294 );
4295 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4296 ROLLBACK TO Add_CategoryParent;
4297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4298 -- Standard call to get message count and if count=1, get the message
4299 FND_MSG_PUB.Count_And_Get (
4300 p_encoded => FND_API.G_FALSE,
4301 p_count => x_msg_count,
4302 p_data => x_msg_data
4303 );
4304 WHEN OTHERS THEN
4305 ROLLBACK TO Add_CategoryParent;
4306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4307 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4308 THEN
4309 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4310 END IF;
4311 -- Standard call to get message count and if count=1, get the message
4312 FND_MSG_PUB.Count_And_Get (
4313 p_encoded => FND_API.G_FALSE,
4314 p_count => x_msg_count,
4315 p_data => x_msg_data
4316 );
4317 --
4318 END Add_CategoryParent;
4319 --------------------------------------------------------------------------------
4320 --------------------------------------------------------------------------------
4321 --
4322 -- Start of comments
4326 -- Function : removes a category to from a parent category
4323 -- API name : Remove_CategoryParent
4324 -- Type : Private
4325 -- Pre-reqs : None
4327 -- Parameters :
4328 -- IN p_api_version IN NUMBER Required
4329 -- p_init_msg_list IN VARCHAR2 Optional
4330 -- Default = FND_API.G_FALSE
4331 -- p_validation_level IN NUMBER Optional
4332 -- Default = FND_API.G_VALID_LEVEL_FULL
4333 -- p_category_id IN NUMBER Required
4334 -- category id
4335 -- OUT : x_return_status OUT VARCHAR2
4336 -- x_msg_count OUT NUMBER
4337 -- x_msg_data OUT VARCHAR2
4338 -- Version : Current version 1.0
4339 -- Previous version 1.0
4340 -- Initial version 1.0
4341 -- Notes :
4342 -- End of comments
4343 --
4344 --
4345 PROCEDURE Remove_CategoryParent
4346 ( p_api_version IN NUMBER,
4347 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4348 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4349 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4350 x_return_status OUT NOCOPY VARCHAR2,
4351 x_msg_count OUT NOCOPY NUMBER,
4352 x_msg_data OUT NOCOPY VARCHAR2,
4353 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
4354 p_object_version_number IN NUMBER,
4355 p_category_id IN NUMBER
4356 )
4357 IS
4358 l_api_name CONSTANT VARCHAR2(30) := 'Remove_CategoryParent';
4359 l_api_version CONSTANT NUMBER := 1.0;
4360 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
4361 --
4362 l_resource_id number;
4363 l_user_id number;
4364 l_login_user_id number;
4365 l_login_user_status varchar2(30);
4366 l_Error_Msg varchar2(2000);
4367 l_Error_Token varchar2(80);
4368 --
4369 l_parent_category_id number;
4370 l_object_version_number number;
4371 l_application_id number;
4372 l_order number;
4373 l_channel_count number;
4374 --
4375 CURSOR Get_ParentId IS
4376 select b.parent_channel_category_id
4377 , b.object_version_number
4378 , b.application_id
4379 , b.channel_category_order
4380 , b.channel_count
4381 from amv_c_categories_b b
4382 where b.channel_category_id = p_category_id;
4383 --
4384 BEGIN
4385 -- Standard begin of API savepoint
4386 SAVEPOINT Remove_CategoryParent;
4387 -- Standard call to check for call compatibility.
4388 IF NOT FND_API.Compatible_API_Call (
4389 l_api_version,
4390 p_api_version,
4391 l_api_name,
4392 G_PKG_NAME)
4393 THEN
4394 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4395 END IF;
4396 -- Debug Message
4397 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4398 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
4399 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
4400 FND_MSG_PUB.Add;
4401 END IF;
4402 --Initialize message list if p_init_msg_list is TRUE.
4403 IF FND_API.To_Boolean (p_init_msg_list) THEN
4404 FND_MSG_PUB.initialize;
4405 END IF;
4406 -- Get the current (login) user id.
4407 AMV_UTILITY_PVT.Get_UserInfo(
4408 x_resource_id => l_resource_id,
4412 );
4409 x_user_id => l_user_id,
4410 x_login_id => l_login_user_id,
4411 x_user_status => l_login_user_status
4413 -- check login user
4414 IF (p_check_login_user = FND_API.G_TRUE) THEN
4415 -- Check if user is login and has the required privilege.
4416 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
4417 -- User is not login.
4418 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4419 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
4420 FND_MSG_PUB.Add;
4421 END IF;
4422 RAISE FND_API.G_EXC_ERROR;
4423 END IF;
4424 END IF;
4425 -- This fix is for executing api in sqlplus mode
4426 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
4427 l_login_user_id := g_login_user_id;
4428 l_user_id := g_user_id;
4429 l_resource_id := g_resource_id;
4430 END IF;
4431 -- Initialize API return status to sucess
4432 x_return_status := FND_API.G_RET_STS_SUCCESS;
4433
4434 --
4435 IF AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) THEN
4436 OPEN Get_ParentId;
4437 FETCH Get_ParentId INTO l_parent_category_id,
4438 l_object_version_number,
4439 l_application_id,
4440 l_order,
4441 l_channel_count;
4442 CLOSE Get_ParentId;
4443 --
4444 IF p_object_version_number = l_object_version_number THEN
4445 l_parent_category_id := null;
4446 BEGIN
4447 AMV_C_CATEGORIES_PKG.UPDATE_B_ROW(
4448 X_CHANNEL_CATEGORY_ID => p_category_id,
4449 X_APPLICATION_ID => l_application_id,
4450 X_OBJECT_VERSION_NUMBER => p_object_version_number + 1,
4451 X_CHANNEL_CATEGORY_ORDER => l_order,
4452 X_PARENT_CHANNEL_CATEGORY_ID => l_parent_category_id,
4453 X_CHANNEL_COUNT => l_channel_count,
4454 X_LAST_UPDATE_DATE => sysdate,
4455 X_LAST_UPDATED_BY => l_user_id,
4456 X_LAST_UPDATE_LOGIN => l_login_user_id
4457 );
4458 EXCEPTION
4459 WHEN OTHERS THEN
4460 --will log the error
4461 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4462 THEN
4463 FND_MESSAGE.Set_Name('AMV', 'AMV_TABLE_HANDLER_ERROR');
4464 FND_MESSAGE.Set_Token('ACTION', 'Updating');
4465 FND_MESSAGE.Set_Token('TABLE', 'Categories');
4466 FND_MSG_PUB.Add;
4467 END IF;
4468 RAISE FND_API.G_EXC_ERROR;
4469 END;
4470 ELSE
4471 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4472 THEN
4473 FND_MESSAGE.Set_Name('AMV', 'AMV_CAT_VERSION_CHANGE');
4474 FND_MSG_PUB.Add;
4475 END IF;
4476 RAISE FND_API.G_EXC_ERROR;
4477 END IF;
4478 ELSE
4479 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4480 THEN
4481 FND_MESSAGE.Set_Name('AMV', 'AMV_CATEGORY_ID_INVALID');
4482 FND_MESSAGE.Set_Token('TKN',p_category_id);
4483 FND_MSG_PUB.Add;
4484 END IF;
4485 RAISE FND_API.G_EXC_ERROR;
4486 END IF;
4487 --
4488
4489 --Standard check of commit
4490 IF FND_API.To_Boolean ( p_commit ) THEN
4491 COMMIT WORK;
4492 END IF;
4493
4494 -- Debug Message
4495 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4496 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
4497 FND_MESSAGE.Set_Token('ROW',l_full_name||': END');
4498 FND_MSG_PUB.Add;
4499 END IF;
4500 --Standard call to get message count and if count=1, get the message
4501 FND_MSG_PUB.Count_And_Get (
4502 p_encoded => FND_API.G_FALSE,
4503 p_count => x_msg_count,
4504 p_data => x_msg_data
4505 );
4506 EXCEPTION
4507 WHEN FND_API.G_EXC_ERROR THEN
4508 ROLLBACK TO Remove_CategoryParent;
4509 x_return_status := FND_API.G_RET_STS_ERROR;
4510 -- Standard call to get message count and if count=1, get the message
4511 FND_MSG_PUB.Count_And_Get (
4512 p_encoded => FND_API.G_FALSE,
4513 p_count => x_msg_count,
4514 p_data => x_msg_data
4515 );
4516 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4517 ROLLBACK TO Remove_CategoryParent;
4518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4519 -- Standard call to get message count and if count=1, get the message
4520 FND_MSG_PUB.Count_And_Get (
4521 p_encoded => FND_API.G_FALSE,
4522 p_count => x_msg_count,
4523 p_data => x_msg_data
4524 );
4525 WHEN OTHERS THEN
4526 ROLLBACK TO Remove_CategoryParent;
4527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4528 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4529 THEN
4530 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4531 END IF;
4532 -- Standard call to get message count and if count=1, get the message
4533 FND_MSG_PUB.Count_And_Get (
4534 p_encoded => FND_API.G_FALSE,
4535 p_count => x_msg_count,
4536 p_data => x_msg_data
4537 );
4538 --
4539 END Remove_CategoryParent;
4540 --------------------------------------------------------------------------------
4541 --------------------------------------------------------------------------------
4542 --
4543 END amv_category_pvt;
[Home] [Help]