[Home] [Help]
PACKAGE BODY: APPS.AMV_CATEGORY_GRP
Source
1 PACKAGE BODY amv_category_grp AS
2 /* $Header: amvgcatb.pls 120.1 2005/06/21 17:50:47 appldev ship $ */
3 --
4 -- NAME
5 -- AMV_CATEGORY_GRP
6 -- PURPOSE
7 --
8 -- HISTORY
9 -- 12/07/1999 SLKRISHN CREATED
10 --
11 --
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMV_CATEGORY_GRP';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amvgcatb.pls';
14 --
15 --
16 -- This package contains the following procedures
17 --
18 --------------------------------------------------------------------------------
19 --------------------------------------------------------------------------------
20 -- Start of comments
21 -- API name : Add_Category
22 -- Type : Private
23 -- Pre-reqs : None
24 -- Function : Create (sub)category.
25 -- Parameters :
26 -- IN p_api_version IN NUMBER Required
27 -- p_init_msg_list IN VARCHAR2 Optional
28 -- Default = FND_API.G_FALSE
29 -- p_commit IN VARCHAR2 Optional
30 -- Default = FND_API.G_FALSE
31 -- p_validation_level IN NUMBER Optional
32 -- Default = FND_API.G_VALID_LEVEL_FULL
33 -- p_application_id IN NUMBER Required
34 -- application creating the category
35 -- p_category_name IN VARCHAR2 Required
36 -- the channel category name. Have to be unique.
37 -- p_description IN VARCHAR2 Optional
38 -- the channel (sub)category description.
39 -- p_parent_category_id IN VARCHAR2 Required
40 -- p_order IN NUMBER Optional
41 -- Default = FND_API.G_MISS_NUM
42 -- the order of this catgory among all the categories
43 -- OUT : x_return_status OUT VARCHAR2
44 -- x_msg_count OUT NUMBER
45 -- x_msg_data OUT VARCHAR2
46 -- x_category_id OUT NUMBER
47 -- Version : Current version 1.0
48 -- Previous version 1.0
49 -- Initial version 1.0
50 -- Notes :
51 --
52 -- End of comments
53 --
54 PROCEDURE Add_Category
55 ( p_api_version IN NUMBER,
56 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
57 p_commit IN VARCHAR2 := FND_API.G_FALSE,
58 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
59 x_return_status OUT NOCOPY VARCHAR2,
60 x_msg_count OUT NOCOPY NUMBER,
61 x_msg_data OUT NOCOPY VARCHAR2,
62 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
63 p_application_id IN NUMBER,
64 p_category_name IN VARCHAR2,
65 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
66 p_parent_category_id IN NUMBER,
67 p_order IN NUMBER := FND_API.G_MISS_NUM,
68 x_category_id OUT NOCOPY NUMBER
69 )
70 IS
71 l_api_name varchar2(30) := 'Add_Category';
72 BEGIN
73 AMV_CATEGORY_PVT.Add_Category
74 (p_api_version => p_api_version,
75 p_init_msg_list => p_init_msg_list,
76 p_commit => p_commit,
77 p_validation_level => p_validation_level,
78 x_return_status => x_return_status,
79 x_msg_count => x_msg_count,
80 x_msg_data => x_msg_data,
81 p_check_login_user => p_check_login_user,
82 p_application_id => p_application_id,
83 p_category_name => p_category_name,
84 p_description => p_description,
85 p_parent_category_id => p_parent_category_id,
86 p_order => p_order,
87 x_category_id => x_category_id
88 );
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
93 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
94 THEN
95 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
96 END IF;
97 -- Standard call to get message count and if count=1, get the message
98 FND_MSG_PUB.Count_And_Get (
99 p_encoded => FND_API.G_FALSE,
100 p_count => x_msg_count,
101 p_data => x_msg_data
102 );
103 END Add_Category;
104 --
105 --------------------------------------------------------------------------------
106 -- Start of comments
107 -- API name : Delete_Category
108 -- Type : Private
109 -- Pre-reqs : None
110 -- Function : Delete (sub)category given the p_category_id or
111 -- p_category_name along with p_parent_category_id.
112 -- Parameters :
113 -- IN p_api_version IN NUMBER Required
114 -- p_init_msg_list IN VARCHAR2 Optional
115 -- Default = FND_API.G_FALSE
116 -- p_commit IN VARCHAR2 Optional
117 -- Default = FND_API.G_FALSE
118 -- p_validation_level IN NUMBER Optional
119 -- Default = FND_API.G_VALID_LEVEL_FULL
120 -- p_category_id IN NUMBER Optional
121 -- Default = FND_API.G_MISS_NUM
122 -- channel (sub)category id.
123 -- p_category_name IN VARCHAR2 Optional
124 -- Default = FND_API.G_MISS_CHAR
125 -- channel (sub)category name.
126 -- p_parent_category_id IN NUMBER Optional
127 -- Default = FND_API.G_MISS_NUM
128 -- parent id for (sub) category
129 -- OUT : x_return_status OUT VARCHAR2
130 -- x_msg_count OUT NUMBER
131 -- x_msg_data OUT VARCHAR2
132 -- Version : Current version 1.0
133 -- Previous version 1.0
134 -- Initial version 1.0
135 -- Notes :
136 --
137 -- End of comments
138 --
139 PROCEDURE Delete_Category
140 ( p_api_version IN NUMBER,
141 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
142 p_commit IN VARCHAR2 := FND_API.G_FALSE,
143 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_msg_count OUT NOCOPY NUMBER,
146 x_msg_data OUT NOCOPY VARCHAR2,
147 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
148 p_application_id IN NUMBER,
149 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
150 p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
151 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM
152 )
153 IS
154 l_api_name varchar2(30) := 'Delete_Category';
155 BEGIN
156
157 AMV_CATEGORY_PVT.Delete_Category
158 (p_api_version => p_api_version,
159 p_init_msg_list => p_init_msg_list,
160 p_commit => p_commit,
161 p_validation_level => p_validation_level,
162 x_return_status => x_return_status,
163 x_msg_count => x_msg_count,
164 x_msg_data => x_msg_data,
165 p_check_login_user => p_check_login_user,
166 p_application_id => p_application_id,
167 p_category_id => p_category_id,
168 p_category_name => p_category_name,
169 p_parent_category_id => p_parent_category_id
170 );
171 EXCEPTION
172 WHEN OTHERS THEN
173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
174 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
175 THEN
176 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
177 END IF;
178 -- Standard call to get message count and if count=1, get the message
179 FND_MSG_PUB.Count_And_Get (
180 p_encoded => FND_API.G_FALSE,
181 p_count => x_msg_count,
182 p_data => x_msg_data
183 );
184 END Delete_Category;
185 --
186 --------------------------------------------------------------------------------
187 -- Start of comments
188 -- API name : Sort_Category
189 -- Type : Private
190 -- Pre-reqs : None
191 -- Function : sort (sub)category list in ascending or descending order
192 -- Parameters :
193 -- IN p_api_version IN NUMBER Required
194 -- p_init_msg_list IN VARCHAR2 Optional
195 -- Default = FND_API.G_FALSE
196 -- p_commit IN VARCHAR2 Optional
197 -- Default = FND_API.G_FALSE
198 -- p_validation_level IN NUMBER Optional
199 -- Default = FND_API.G_VALID_LEVEL_FULL
200 -- p_sort_order IN VARCHAR2 Optional
201 -- Default = AMV_CATEGORY_PVT.G_ASC_ORDER
202 -- Ascending(ASC) or Descending(DESC) Order.
203 -- p_parent_category_id IN NUMBER Optional
204 -- Default = FND_API.G_MISS_NUM
205 -- parent id for sub categories
206 -- OUT : x_return_status OUT VARCHAR2
207 -- x_msg_count OUT NUMBER
208 -- x_msg_data OUT VARCHAR2
209 -- Version : Current version 1.0
210 -- Previous version 1.0
211 -- Initial version 1.0
212 -- Notes :
213 --
214 -- End of comments
215 --
216 PROCEDURE Sort_Category
217 ( p_api_version IN NUMBER,
218 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
219 p_commit IN VARCHAR2 := FND_API.G_FALSE,
220 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
221 x_return_status OUT NOCOPY VARCHAR2,
222 x_msg_count OUT NOCOPY NUMBER,
223 x_msg_data OUT NOCOPY VARCHAR2,
224 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
225 p_application_id IN NUMBER,
226 p_sort_order IN VARCHAR2 := AMV_CATEGORY_PVT.G_ASC_ORDER,
227 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM
228 )
229 IS
230 l_api_name varchar2(30) := 'Sort_Category';
231 BEGIN
232 AMV_CATEGORY_PVT.Sort_Category
233 (p_api_version => p_api_version,
234 p_init_msg_list => p_init_msg_list,
235 p_commit => p_commit,
236 p_validation_level => p_validation_level,
237 x_return_status => x_return_status,
238 x_msg_count => x_msg_count,
239 x_msg_data => x_msg_data,
240 p_check_login_user => p_check_login_user,
241 p_application_id => p_application_id,
242 p_sort_order => p_sort_order,
243 p_parent_category_id => p_parent_category_id
244 );
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
249 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
250 THEN
251 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
252 END IF;
253 -- Standard call to get message count and if count=1, get the message
254 FND_MSG_PUB.Count_And_Get (
255 p_encoded => FND_API.G_FALSE,
256 p_count => x_msg_count,
257 p_data => x_msg_data
258 );
259 END Sort_Category;
260 --
261 --------------------------------------------------------------------------------
262 -- Start of comments
263 -- API name : Reorder_Category
264 -- Type : Private
265 -- Pre-reqs : None
266 -- Function : Reorder (sub)category list
267 -- Parameters :
268 -- IN p_api_version IN NUMBER Required
269 -- p_init_msg_list IN VARCHAR2 Optional
270 -- Default = FND_API.G_FALSE
271 -- p_commit IN VARCHAR2 Optional
272 -- Default = FND_API.G_FALSE
273 -- p_validation_level NUMBER Optional
274 -- Default = FND_API.G_VALID_LEVEL_FULL
275 -- p_category_id_array IN AMV_NUMBER_VARRAY_TYPE
276 -- Required
277 -- p_category_new_order IN AMV_NUMBER_VARRAY_TYPE
278 -- Required
279 -- OUT : x_return_status OUT VARCHAR2
280 -- x_msg_count OUT NUMBER
281 -- x_msg_data OUT VARCHAR2
282 -- Version : Current version 1.0
283 -- Previous version 1.0
284 -- Initial version 1.0
285 -- Notes :
286 --
287 -- End of comments
288 --
289 PROCEDURE Reorder_Category
290 ( p_api_version IN NUMBER,
291 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
292 p_commit IN VARCHAR2 := FND_API.G_FALSE,
293 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
294 x_return_status OUT NOCOPY VARCHAR2,
295 x_msg_count OUT NOCOPY NUMBER,
296 x_msg_data OUT NOCOPY VARCHAR2,
297 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
298 p_application_id IN NUMBER,
299 p_category_id_array IN AMV_CATEGORY_PVT.AMV_NUMBER_VARRAY_TYPE,
300 p_category_new_order IN AMV_CATEGORY_PVT.AMV_NUMBER_VARRAY_TYPE
301 )
302 IS
303 l_api_name varchar2(30) := 'Reorder_Category';
304 BEGIN
305 AMV_CATEGORY_PVT.Reorder_Category
306 (p_api_version => p_api_version,
307 p_init_msg_list => p_init_msg_list,
308 p_commit => p_commit,
309 p_validation_level => p_validation_level,
310 x_return_status => x_return_status,
311 x_msg_count => x_msg_count,
312 x_msg_data => x_msg_data,
313 p_check_login_user => p_check_login_user,
314 p_application_id => p_application_id,
315 p_category_id_array => p_category_id_array,
316 p_category_new_order => p_category_new_order
317 );
318
319 EXCEPTION
320 WHEN OTHERS THEN
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
322 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
323 THEN
324 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
325 END IF;
326 -- Standard call to get message count and if count=1, get the message
327 FND_MSG_PUB.Count_And_Get (
328 p_encoded => FND_API.G_FALSE,
329 p_count => x_msg_count,
330 p_data => x_msg_data
331 );
332 END Reorder_Category;
333 --
334 --------------------------------------------------------------------------------
335 -- Start of comments
336 -- API name : Update_Category
337 -- Type : Private
338 -- Pre-reqs : None
339 -- Function : Update (sub)category given (sub)category id or name
340 -- Parameters :
341 -- IN p_api_version IN NUMBER Required
342 -- p_init_msg_list IN VARCHAR2 Optional
343 -- Default = FND_API.G_FALSE
344 -- p_commit IN VARCHAR2 Optional
345 -- Default = FND_API.G_FALSE
346 -- p_validation_level IN NUMBER Optional
347 -- Default = FND_API.G_VALID_LEVEL_FULL
348 -- p_object_version_number IN NUMBER Required
349 -- object version number
350 -- p_category_id IN NUMBER Required
351 -- channel category id.
352 -- p_category_name IN VARCHAR2 Optional
353 -- channel category name.
354 -- (sub)category id or name is required
355 -- p_parent_category_id IN NUMBER Optional
356 -- Default = FND_API.G_MISS_NUM
360 -- p_category_new_name IN VARCHAR2 Optional
357 -- channel category id.
358 -- p_category_order IN NUMBER Optional
359 -- new channel category order
361 -- new channel category name. New name has to be unique
362 -- p_description IN VARCHAR2 Optional
363 -- Default = FND_API.G_MISS_CHAR
364 -- channel category description.
365 -- OUT : x_return_status OUT VARCHAR2
366 -- x_msg_count OUT NUMBER
367 -- x_msg_data OUT VARCHAR2
368 -- Version : Current version 1.0
369 -- Previous version 1.0
370 -- Initial version 1.0
371 -- Notes :
372 --
373 -- End of comments
374 --
375 PROCEDURE Update_Category
376 ( p_api_version IN NUMBER,
377 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
378 p_commit IN VARCHAR2 := FND_API.G_FALSE,
379 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
380 x_return_status OUT NOCOPY VARCHAR2,
381 x_msg_count OUT NOCOPY NUMBER,
382 x_msg_data OUT NOCOPY VARCHAR2,
383 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
384 p_object_version_number IN NUMBER,
385 p_application_id IN NUMBER,
386 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
387 p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
388 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
389 p_category_order IN NUMBER := FND_API.G_MISS_NUM,
390 p_category_new_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
391 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR
392 )
393 IS
394 l_api_name varchar2(30) := 'Update_Category';
395 BEGIN
396 AMV_CATEGORY_PVT.Update_Category
397 (p_api_version => p_api_version,
398 p_init_msg_list => p_init_msg_list,
399 p_commit => p_commit,
400 p_validation_level => p_validation_level,
401 x_return_status => x_return_status,
402 x_msg_count => x_msg_count,
403 x_msg_data => x_msg_data,
404 p_check_login_user => p_check_login_user,
405 p_object_version_number => p_object_version_number,
406 p_application_id => p_application_id,
407 p_category_id => p_category_id,
408 p_category_name => p_category_name,
409 p_parent_category_id => p_parent_category_id,
410 p_category_order => p_category_order,
411 p_category_new_name => p_category_new_name,
412 p_description => p_description
413 );
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
418 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
419 THEN
420 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
421 END IF;
422 -- Standard call to get message count and if count=1, get the message
423 FND_MSG_PUB.Count_And_Get (
424 p_encoded => FND_API.G_FALSE,
425 p_count => x_msg_count,
426 p_data => x_msg_data
427 );
428 END Update_Category;
429 --
430 --------------------------------------------------------------------------------
431 -- Start of comments
432 -- API name : Find_Categories
433 -- Type : Private
434 -- Pre-reqs : None
435 -- Function : Search and return (sub)categories
436 -- Parameters :
437 -- IN p_api_version IN NUMBER Required
438 -- p_init_msg_list IN VARCHAR2 Optional
439 -- Default = FND_API.G_FALSE
440 -- p_validation_level NUMBER Optional
441 -- Default = FND_API.G_VALID_LEVEL_FULL
442 -- p_application_id IN NUMBER Required
443 -- application creating the channel
444 -- p_category_name IN VARCHAR2 Optional
445 -- Search criteria by name. Default = '%' (everything)
446 -- p_parent_category_id IN NUMBER Optional
447 -- Default = FND_API.G_MISS_NUM
448 -- parent id for sub categories
449 -- OUT : x_return_status OUT VARCHAR2
450 -- x_msg_count OUT NUMBER
451 -- x_msg_data OUT VARCHAR2
452 -- x_chan_category_rec_array OUT AMV_CATEGORY_VARRAY_TYPE
453 -- Version : Current version 1.0
454 -- Previous version 1.0
455 -- Initial version 1.0
456 -- Notes :
457 --
458 -- End of comments
459 --
460 PROCEDURE Find_Categories
461 ( p_api_version IN NUMBER,
462 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
463 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
464 x_return_status OUT NOCOPY VARCHAR2,
465 x_msg_count OUT NOCOPY NUMBER,
466 x_msg_data OUT NOCOPY VARCHAR2,
467 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
468 p_application_id IN NUMBER,
469 p_category_name IN VARCHAR2 := '%',
470 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
474 x_chan_category_rec_array OUT NOCOPY AMV_CATEGORY_PVT.AMV_CATEGORY_VARRAY_TYPE
471 p_ignore_hierarchy IN VARCHAR2 := FND_API.G_FALSE,
472 p_request_obj IN AMV_CATEGORY_PVT.AMV_REQUEST_OBJ_TYPE,
473 x_return_obj OUT NOCOPY AMV_CATEGORY_PVT.AMV_RETURN_OBJ_TYPE,
475 )
476 IS
477 l_api_name varchar2(30) := 'Find_Categories';
478 BEGIN
479 AMV_CATEGORY_PVT.Find_Categories
480 (p_api_version => p_api_version,
481 p_init_msg_list => p_init_msg_list,
482 p_validation_level => p_validation_level,
483 x_return_status => x_return_status,
484 x_msg_count => x_msg_count,
485 x_msg_data => x_msg_data,
486 p_check_login_user => p_check_login_user,
487 p_application_id => p_application_id,
488 p_category_name => p_category_name,
489 p_parent_category_id => p_parent_category_id,
490 p_ignore_hierarchy => p_ignore_hierarchy,
491 p_request_obj => p_request_obj,
492 x_return_obj => x_return_obj,
493 x_chan_category_rec_array => x_chan_category_rec_array
494 );
495
496 EXCEPTION
497 WHEN OTHERS THEN
498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
499 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
500 THEN
501 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
502 END IF;
503 -- Standard call to get message count and if count=1, get the message
504 FND_MSG_PUB.Count_And_Get (
505 p_encoded => FND_API.G_FALSE,
506 p_count => x_msg_count,
507 p_data => x_msg_data
508 );
509 END Find_Categories;
510 --------------------------------------------------------------------------------
511 -- Start of comments
512 -- API name : Get_ChannelsPerCategory
513 -- Type : Private
514 -- Pre-reqs : None
515 -- Function : Return all channels under a (sub)category
516 -- Parameters :
517 -- IN p_api_version IN NUMBER Required
518 -- p_init_msg_list IN VARCHAR2 Optional
519 -- Default = FND_API.G_FALSE
520 -- p_validation_level IN NUMBER Optional
521 -- Default = FND_API.G_VALID_LEVEL_FULL
522 -- p_category_id IN NUMBER Required
523 -- p_include_subcats IN VARCHAR2 Optional
524 -- Default = FND_API.G_FALSE
525 -- OUT : x_return_status OUT VARCHAR2
526 -- x_msg_count OUT NUMBER
527 -- x_msg_data OUT VARCHAR2
528 -- x_content_chan_array OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
529 -- Version : Current version 1.0
530 -- Previous version 1.0
531 -- Initial version 1.0
532 -- Notes :
533 --
534 -- End of comments
535 --
536 PROCEDURE Get_ChannelsPerCategory
537 ( p_api_version IN NUMBER,
538 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
539 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
540 x_return_status OUT NOCOPY VARCHAR2,
541 x_msg_count OUT NOCOPY NUMBER,
542 x_msg_data OUT NOCOPY VARCHAR2,
543 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
544 p_category_id IN NUMBER,
545 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
546 x_content_chan_array OUT NOCOPY AMV_CATEGORY_PVT.AMV_CAT_HIERARCHY_VARRAY_TYPE
547 )
548 IS
549 l_api_name varchar2(30) := 'Get_ChannelsPerCategory';
550 BEGIN
551 AMV_CATEGORY_PVT.Get_ChannelsPerCategory
552 (p_api_version => p_api_version,
553 p_init_msg_list => p_init_msg_list,
554 p_validation_level => p_validation_level,
555 x_return_status => x_return_status,
556 x_msg_count => x_msg_count,
557 x_msg_data => x_msg_data,
558 p_check_login_user => p_check_login_user,
559 p_category_id => p_category_id,
560 p_include_subcats => p_include_subcats,
561 x_content_chan_array => x_content_chan_array
562 );
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
567 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
568 THEN
569 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
570 END IF;
571 -- Standard call to get message count and if count=1, get the message
572 FND_MSG_PUB.Count_And_Get (
573 p_encoded => FND_API.G_FALSE,
574 p_count => x_msg_count,
575 p_data => x_msg_data
576 );
577 END Get_ChannelsPerCategory;
578 --------------------------------------------------------------------------------
579 --
580 -- Start of comments
581 -- API name : Fetch_CategoryId
582 -- Type : Private
583 -- Pre-reqs : None
584 -- Function : Return category id for a category or subcategory name.
585 -- Parameters :
586 -- IN p_api_version IN NUMBER Required
587 -- p_init_msg_list IN VARCHAR2 Optional
588 -- Default = FND_API.G_FALSE
589 -- p_validation_level IN NUMBER Optional
590 -- Default = FND_API.G_VALID_LEVEL_FULL
591 -- p_category_name IN VARCHAR2 Required
592 -- (sub)category id
596 -- OUT : x_return_status OUT VARCHAR2
593 -- p_parent_category_id IN NUMBER Optional
594 -- Default = FND_API.G_MISS_NUM
595 -- parent category id
597 -- x_msg_count OUT NUMBER
598 -- x_msg_data OUT VARCHAR2
599 -- x_category_id OUT NUMBER
600 -- Version : Current version 1.0
601 -- Previous version 1.0
602 -- Initial version 1.0
603 -- Notes :
604 -- End of comments
605 --
606 --
607 PROCEDURE Fetch_CategoryId
608 ( p_api_version IN NUMBER,
609 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
610 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
611 x_return_status OUT NOCOPY VARCHAR2,
612 x_msg_count OUT NOCOPY NUMBER,
613 x_msg_data OUT NOCOPY VARCHAR2,
614 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
615 p_application_id IN NUMBER,
616 p_category_name IN VARCHAR2,
617 p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
618 x_category_id OUT NOCOPY NUMBER
619 )
620 IS
621 l_api_name varchar2(30) := 'Fetch_CategoryId';
622 BEGIN
623 AMV_CATEGORY_PVT.Fetch_CategoryId
624 (p_api_version => p_api_version,
625 p_init_msg_list => p_init_msg_list,
626 p_validation_level => p_validation_level,
627 x_return_status => x_return_status,
628 x_msg_count => x_msg_count,
629 x_msg_data => x_msg_data,
630 p_check_login_user => p_check_login_user,
631 p_application_id => p_application_id,
632 p_category_name => p_category_name,
633 p_parent_category_id => p_parent_category_id,
634 x_category_id => x_category_id
635 );
636
637 EXCEPTION
638 WHEN OTHERS THEN
639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
640 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
641 THEN
642 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
643 END IF;
644 -- Standard call to get message count and if count=1, get the message
645 FND_MSG_PUB.Count_And_Get (
646 p_encoded => FND_API.G_FALSE,
647 p_count => x_msg_count,
648 p_data => x_msg_data
649 );
650 END Fetch_CategoryId;
651 --------------------------------------------------------------------------------
652 --------------------------------------------------------------------------------
653 --
654 -- Start of comments
655 -- API name : Get_CatParentsHierarchy
656 -- Type : Private
657 -- Pre-reqs : None
658 -- Function : Return parents hierarchy of category name and ids
659 -- for a category id.
660 -- Parameters :
661 -- IN p_api_version IN NUMBER Required
662 -- p_init_msg_list IN VARCHAR2 Optional
663 -- Default = FND_API.G_FALSE
664 -- p_validation_level IN NUMBER Optional
665 -- Default = FND_API.G_VALID_LEVEL_FULL
666 -- p_category_id IN NUMBER Required
667 -- (sub)category id
668 -- OUT : x_return_status OUT VARCHAR2
669 -- x_msg_count OUT NUMBER
670 -- x_msg_data OUT VARCHAR2
671 -- x_category_hierarchy OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
672 -- Version : Current version 1.0
673 -- Previous version 1.0
674 -- Initial version 1.0
675 -- Notes :
676 -- End of comments
677 --
678 --
679 PROCEDURE Get_CatParentsHierarchy
680 ( p_api_version IN NUMBER,
681 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
682 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
683 x_return_status OUT NOCOPY VARCHAR2,
684 x_msg_count OUT NOCOPY NUMBER,
685 x_msg_data OUT NOCOPY VARCHAR2,
686 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
687 p_category_id IN NUMBER,
688 x_category_hierarchy OUT NOCOPY AMV_CATEGORY_PVT.AMV_CAT_HIERARCHY_VARRAY_TYPE
689 )
690 IS
691 l_api_name varchar2(30) := 'Get_CatParentsHierarchy';
692 BEGIN
693 AMV_CATEGORY_PVT.Get_CatParentsHierarchy
694 (p_api_version => p_api_version,
695 p_init_msg_list => p_init_msg_list,
696 p_validation_level => p_validation_level,
697 x_return_status => x_return_status,
698 x_msg_count => x_msg_count,
699 x_msg_data => x_msg_data,
700 p_check_login_user => p_check_login_user,
701 p_category_id => p_category_id,
702 x_category_hierarchy => x_category_hierarchy
703 );
704
705 EXCEPTION
706 WHEN OTHERS THEN
707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
708 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
709 THEN
710 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
711 END IF;
712 -- Standard call to get message count and if count=1, get the message
713 FND_MSG_PUB.Count_And_Get (
714 p_encoded => FND_API.G_FALSE,
715 p_count => x_msg_count,
716 p_data => x_msg_data
717 );
718 END Get_CatParentsHierarchy;
722 -- Start of comments
719 --------------------------------------------------------------------------------
720 --------------------------------------------------------------------------------
721 --
723 -- API name : Get_CatChildrenHierarchy
724 -- Type : Private
725 -- Pre-reqs : None
726 -- Function : Return children hierarchy of category name and ids
727 -- for a category id.
728 -- Parameters :
729 -- IN p_api_version IN NUMBER Required
730 -- p_init_msg_list IN VARCHAR2 Optional
731 -- Default = FND_API.G_FALSE
732 -- p_validation_level IN NUMBER Optional
733 -- Default = FND_API.G_VALID_LEVEL_FULL
734 -- p_category_id IN NUMBER Required
735 -- (sub)category id
736 -- OUT : x_return_status OUT VARCHAR2
737 -- x_msg_count OUT NUMBER
738 -- x_msg_data OUT VARCHAR2
739 -- x_category_hierarchy OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
740 -- Version : Current version 1.0
741 -- Previous version 1.0
742 -- Initial version 1.0
743 -- Notes :
744 -- End of comments
745 --
746 --
747 PROCEDURE Get_CatChildrenHierarchy
748 ( p_api_version IN NUMBER,
749 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
750 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
751 x_return_status OUT NOCOPY VARCHAR2,
752 x_msg_count OUT NOCOPY NUMBER,
753 x_msg_data OUT NOCOPY VARCHAR2,
754 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
755 p_category_id IN NUMBER,
756 x_category_hierarchy OUT NOCOPY AMV_CATEGORY_PVT.AMV_CAT_HIERARCHY_VARRAY_TYPE
757 )
758 IS
759 l_api_name varchar2(30) := 'Get_CatChildrenHierarchy';
760 BEGIN
761 AMV_CATEGORY_PVT.Get_CatChildrenHierarchy
762 (p_api_version => p_api_version,
763 p_init_msg_list => p_init_msg_list,
764 p_validation_level => p_validation_level,
765 x_return_status => x_return_status,
766 x_msg_count => x_msg_count,
767 x_msg_data => x_msg_data,
768 p_check_login_user => p_check_login_user,
769 p_category_id => p_category_id,
770 x_category_hierarchy => x_category_hierarchy
771 );
772
773 EXCEPTION
774 WHEN OTHERS THEN
775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
776 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
777 THEN
778 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
779 END IF;
780 -- Standard call to get message count and if count=1, get the message
781 FND_MSG_PUB.Count_And_Get (
782 p_encoded => FND_API.G_FALSE,
783 p_count => x_msg_count,
784 p_data => x_msg_data
785 );
786 END Get_CatChildrenHierarchy;
787 --------------------------------------------------------------------------------
788 --------------------------------------------------------------------------------
789 --
790 -- Start of comments
791 -- API name : Get_ChnCategoryHierarchy
792 -- Type : Private
793 -- Pre-reqs : None
794 -- Function : Return parents hierarchy of category name and ids
795 -- for a channel id.
796 -- Parameters :
797 -- IN p_api_version IN NUMBER Required
798 -- p_init_msg_list IN VARCHAR2 Optional
799 -- Default = FND_API.G_FALSE
800 -- p_validation_level IN NUMBER Optional
801 -- Default = FND_API.G_VALID_LEVEL_FULL
802 -- p_channel_id IN NUMBER Required
803 -- channel id
804 -- OUT : x_return_status OUT VARCHAR2
805 -- x_msg_count OUT NUMBER
806 -- x_msg_data OUT VARCHAR2
807 -- x_channel_name OUT VARCHAR2
808 -- x_category_hierarchy OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
809 -- Version : Current version 1.0
810 -- Previous version 1.0
811 -- Initial version 1.0
812 -- Notes :
813 -- End of comments
814 --
815 --
816 PROCEDURE Get_ChnCategoryHierarchy
817 ( p_api_version IN NUMBER,
818 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
819 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
820 x_return_status OUT NOCOPY VARCHAR2,
821 x_msg_count OUT NOCOPY NUMBER,
822 x_msg_data OUT NOCOPY VARCHAR2,
823 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
824 p_channel_id IN NUMBER,
825 x_channel_name OUT NOCOPY VARCHAR2,
826 x_category_hierarchy OUT NOCOPY AMV_CATEGORY_PVT.AMV_CAT_HIERARCHY_VARRAY_TYPE
827 )
828 IS
829 l_api_name varchar2(30) := 'Get_ChnCategoryHierarchy';
830 BEGIN
831 AMV_CATEGORY_PVT.Get_ChnCategoryHierarchy
832 (p_api_version => p_api_version,
833 p_init_msg_list => p_init_msg_list,
834 p_validation_level => p_validation_level,
835 x_return_status => x_return_status,
836 x_msg_count => x_msg_count,
837 x_msg_data => x_msg_data,
838 p_check_login_user => p_check_login_user,
842 );
839 p_channel_id => p_channel_id,
840 x_channel_name => x_channel_name,
841 x_category_hierarchy => x_category_hierarchy
843
844 EXCEPTION
845 WHEN OTHERS THEN
846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
847 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
848 THEN
849 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
850 END IF;
851 -- Standard call to get message count and if count=1, get the message
852 FND_MSG_PUB.Count_And_Get (
853 p_encoded => FND_API.G_FALSE,
854 p_count => x_msg_count,
855 p_data => x_msg_data
856 );
857 END Get_ChnCategoryHierarchy;
858 --------------------------------------------------------------------------------
859 -- Start of comments
860 -- API name : Get_ItemsPerCategory
861 -- Type : Private
862 -- Pre-reqs : None
863 -- Function : Return all items directly under
864 -- a content channel (sub)category
865 -- Parameters :
866 -- IN p_api_version IN NUMBER Required
867 -- p_init_msg_list IN VARCHAR2 Optional
868 -- Default = FND_API.G_FALSE
869 -- p_validation_level IN NUMBER Optional
870 -- Default = FND_API.G_VALID_LEVEL_FULL
871 -- p_category_id IN NUMBER Required
872 -- p_include_subcats IN VARCHAR2 Optional
873 -- Default = FND_API.G_FALSE
874 -- OUT : x_return_status OUT VARCHAR2
875 -- x_msg_count OUT NUMBER
876 -- x_msg_data OUT VARCHAR2
877 -- x_items_array OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
878 -- Version : Current version 1.0
879 -- Previous version 1.0
880 -- Initial version 1.0
881 -- Notes :
882 --
883 -- End of comments
884 --
885 PROCEDURE Get_ItemsPerCategory
886 ( p_api_version IN NUMBER,
887 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
888 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
889 x_return_status OUT NOCOPY VARCHAR2,
890 x_msg_count OUT NOCOPY NUMBER,
891 x_msg_data OUT NOCOPY VARCHAR2,
892 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
893 p_category_id IN NUMBER,
894 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
895 p_request_obj IN AMV_CATEGORY_PVT.AMV_REQUEST_OBJ_TYPE,
896 p_category_sort IN AMV_CATEGORY_PVT.AMV_SORT_OBJ_TYPE,
897 x_return_obj OUT NOCOPY AMV_CATEGORY_PVT.AMV_RETURN_OBJ_TYPE,
898 x_items_array OUT NOCOPY AMV_CATEGORY_PVT.AMV_CAT_HIERARCHY_VARRAY_TYPE
899 )
900 IS
901 l_api_name varchar2(30) := 'Get_ItemsPerCategory';
902 BEGIN
903 AMV_CATEGORY_PVT.Get_ItemsPerCategory
904 (p_api_version => p_api_version,
905 p_init_msg_list => p_init_msg_list,
906 p_validation_level => p_validation_level,
907 x_return_status => x_return_status,
908 x_msg_count => x_msg_count,
909 x_msg_data => x_msg_data,
910 p_check_login_user => p_check_login_user,
911 p_category_id => p_category_id,
912 p_include_subcats => p_include_subcats,
913 p_request_obj => p_request_obj,
914 p_category_sort => p_category_sort,
915 x_return_obj => x_return_obj,
916 x_items_array => x_items_array
917 );
918
919 EXCEPTION
920 WHEN OTHERS THEN
921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
922 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
923 THEN
924 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
925 END IF;
926 -- Standard call to get message count and if count=1, get the message
927 FND_MSG_PUB.Count_And_Get (
928 p_encoded => FND_API.G_FALSE,
929 p_count => x_msg_count,
930 p_data => x_msg_data
931 );
932 END Get_ItemsPerCategory;
933 --------------------------------------------------------------------------------
934 --
935 -- Start of comments
936 -- API name : Add_CategoryParent
937 -- Type : Private
938 -- Pre-reqs : None
939 -- Function : attaches a category to a parent category
940 -- Parameters :
941 -- IN p_api_version IN NUMBER Required
942 -- p_init_msg_list IN VARCHAR2 Optional
943 -- Default = FND_API.G_FALSE
944 -- p_validation_level IN NUMBER Optional
945 -- Default = FND_API.G_VALID_LEVEL_FULL
946 -- p_category_id IN NUMBER Required
947 -- category id
948 -- p_parent_category_id IN NUMBER Required
949 -- parent category id
950 -- p_replace_existing IN VARCHAR2 Optional
951 -- Default = FND_API.G_FALSE
952 -- OUT : x_return_status OUT VARCHAR2
953 -- x_msg_count OUT NUMBER
954 -- x_msg_data OUT VARCHAR2
955 -- Version : Current version 1.0
956 -- Previous version 1.0
957 -- Initial version 1.0
958 -- Notes :
959 -- End of comments
960 --
961 --
965 p_commit IN VARCHAR2 := FND_API.G_FALSE,
962 PROCEDURE Add_CategoryParent
963 ( p_api_version IN NUMBER,
964 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
966 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
967 x_return_status OUT NOCOPY VARCHAR2,
968 x_msg_count OUT NOCOPY NUMBER,
969 x_msg_data OUT NOCOPY VARCHAR2,
970 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
971 p_object_version_number IN NUMBER,
972 p_category_id IN NUMBER,
973 p_parent_category_id IN NUMBER,
974 p_replace_existing IN VARCHAR2 := FND_API.G_FALSE
975 )
976 IS
977 l_api_name varchar2(30) := 'Add_CategoryParent';
978 BEGIN
979 AMV_CATEGORY_PVT.Add_CategoryParent
980 (p_api_version => p_api_version,
981 p_init_msg_list => p_init_msg_list,
982 p_commit => p_commit,
983 p_validation_level => p_validation_level,
984 x_return_status => x_return_status,
985 x_msg_count => x_msg_count,
986 x_msg_data => x_msg_data,
987 p_check_login_user => p_check_login_user,
988 p_object_version_number => p_object_version_number,
989 p_category_id => p_category_id,
990 p_parent_category_id => p_parent_category_id,
991 p_replace_existing => p_replace_existing
992 );
993
994 EXCEPTION
995 WHEN OTHERS THEN
996 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
997 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
998 THEN
999 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1000 END IF;
1001 -- Standard call to get message count and if count=1, get the message
1002 FND_MSG_PUB.Count_And_Get (
1003 p_encoded => FND_API.G_FALSE,
1004 p_count => x_msg_count,
1005 p_data => x_msg_data
1006 );
1007 END Add_CategoryParent;
1008 --------------------------------------------------------------------------------
1009 --------------------------------------------------------------------------------
1010 --
1011 -- Start of comments
1012 -- API name : Remove_CategoryParent
1013 -- Type : Private
1014 -- Pre-reqs : None
1015 -- Function : removes a category from a parent category
1016 -- Parameters :
1017 -- IN p_api_version IN NUMBER Required
1018 -- p_init_msg_list IN VARCHAR2 Optional
1019 -- Default = FND_API.G_FALSE
1020 -- p_validation_level IN NUMBER Optional
1021 -- Default = FND_API.G_VALID_LEVEL_FULL
1022 -- p_category_id IN NUMBER Required
1023 -- category id
1024 -- OUT : x_return_status OUT VARCHAR2
1025 -- x_msg_count OUT NUMBER
1026 -- x_msg_data OUT VARCHAR2
1027 -- Version : Current version 1.0
1028 -- Previous version 1.0
1029 -- Initial version 1.0
1030 -- Notes :
1031 -- End of comments
1032 --
1033 --
1034 PROCEDURE Remove_CategoryParent
1035 ( p_api_version IN NUMBER,
1036 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1037 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1038 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1039 x_return_status OUT NOCOPY VARCHAR2,
1040 x_msg_count OUT NOCOPY NUMBER,
1041 x_msg_data OUT NOCOPY VARCHAR2,
1042 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1043 p_object_version_number IN NUMBER,
1044 p_category_id IN NUMBER
1045 )
1046 IS
1047 l_api_name varchar2(30) := 'Remove_CategoryParent';
1048 BEGIN
1049 AMV_CATEGORY_PVT.Remove_CategoryParent
1050 (p_api_version => p_api_version,
1051 p_init_msg_list => p_init_msg_list,
1052 p_commit => p_commit,
1053 p_validation_level => p_validation_level,
1054 x_return_status => x_return_status,
1055 x_msg_count => x_msg_count,
1056 x_msg_data => x_msg_data,
1057 p_check_login_user => p_check_login_user,
1058 p_object_version_number => p_object_version_number,
1059 p_category_id => p_category_id
1060 );
1061
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1065 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1066 THEN
1067 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1068 END IF;
1069 -- Standard call to get message count and if count=1, get the message
1070 FND_MSG_PUB.Count_And_Get (
1071 p_encoded => FND_API.G_FALSE,
1072 p_count => x_msg_count,
1073 p_data => x_msg_data
1074 );
1075 END Remove_CategoryParent;
1076 --------------------------------------------------------------------------------
1077 --------------------------------------------------------------------------------
1078 --
1079 END amv_category_grp;