[Home] [Help]
PACKAGE BODY: APPS.AMV_CHANNEL_PVT
Source
1 PACKAGE BODY amv_channel_pvt AS
2 /* $Header: amvvchab.pls 120.1 2005/06/22 17:27:27 appldev ship $ */
3 --
4 -- NAME
5 -- AMV_CHANNEL_PVT
6 --
7 -- HISTORY
8 -- 08/04/1999 SLKRISHN CREATED
9 --
10 --
11 --
12 TYPE CursorType IS REF CURSOR;
13 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMV_CHANNEL_PVT';
14 G_RESOURCE_ID CONSTANT NUMBER := -1;
15 G_USER_ID CONSTANT NUMBER := -1;
16 G_LOGIN_USER_ID CONSTANT NUMBER := -1;
17 --
18 --
19 ----------------------------- Private Portinon ---------------------------------
20 --------------------------------------------------------------------------------
21 -- We use the following private helper procedure
22 --
23 --------------------------------------------------------------------------------
24 --------------------------------------------------------------------------------
25 PROCEDURE Get_ChannelStatus
26 (
27 x_return_status OUT NOCOPY VARCHAR2,
28 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
29 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
30 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
31 x_exist_flag OUT NOCOPY VARCHAR2,
32 x_channel_id OUT NOCOPY NUMBER,
33 x_error_msg OUT NOCOPY VARCHAR2,
34 x_error_token OUT NOCOPY VARCHAR2
35 );
36 --
37 PROCEDURE Get_ChannelRecord
38 ( p_channel_id IN NUMBER,
39 x_channel_obj OUT NOCOPY AMV_CHANNEL_OBJ_TYPE
40 );
41 --
42 FUNCTION Get_MatchOnStatus
43 ( p_channel_id IN NUMBER ) return boolean;
44 --
45 --------------------------------------------------------------------------------
46 --
47 -- Start of comments
48 -- API name : Get_ChannelStatus
49 -- Type : Private
50 -- Pre-reqs : None
51 -- Function : check if channel (p_channel_id/p_channel_name) exist
52 -- return the channel id if existing.
53 -- Parameters :
54 -- p_channel_id IN NUMBER Optional
55 -- channel id. Default = FND_API.G_MISS_NUM
56 -- p_channel_name IN VARCHAR2 Optional
57 -- channel name. Default = FND_API.G_MISS_CHAR
58 -- Either pass the channel id (preferred) or channel name
59 -- to identify the channel. When channel name passed
60 -- pass category id
61 -- p_category_id IN NUMBER Optional
62 -- category id. Default = FND_API.G_MISS_NUM
63 -- OUT : x_return_status OUT VARCHAR2
64 -- x_exist_flag OUT VARCHAR2
65 -- category existent flag
66 -- x_channel_id OUT NUMBER
67 -- category id which is valid if x_exist_flag is true.
68 -- x_error_msg OUT VARCHAR2
69 -- error message
70 -- x_error_token OUT VARCHAR2
71 -- error token
72 -- Version : Current version 1.0
73 -- Previous version 1.0
74 -- Initial version 1.0
75 -- Notes :
76 -- End of comments
77 --
78 PROCEDURE Get_ChannelStatus
79 (
80 x_return_status OUT NOCOPY VARCHAR2,
81 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
82 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
83 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
84 x_exist_flag OUT NOCOPY VARCHAR2,
85 x_channel_id OUT NOCOPY NUMBER,
86 x_error_msg OUT NOCOPY VARCHAR2,
87 x_error_token OUT NOCOPY VARCHAR2
88 ) IS
89 CURSOR Get_ChannelStatusByName is
90 select b.channel_id
91 from amv_c_channels_b b, amv_c_channels_tl tl
92 where tl.channel_name = p_channel_name
93 and tl.language = userenv('lang')
94 -- commented line below for bug no.2950840
95 --and b.channel_category_id = p_category_id
96 and tl.channel_id = b.channel_id;
97 --
98 BEGIN
99
100 IF (p_channel_id IS NULL OR p_channel_name IS NULL
101 OR p_category_id IS NULL) THEN
102 x_error_msg := 'AMV_CHN_ID_OR_NAME_NULL';
103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
104 END IF;
105 --
106 IF p_channel_id = FND_API.G_MISS_NUM THEN
107 IF p_channel_name = FND_API.G_MISS_CHAR THEN
108 -- Must pass either channel id or channel name to identify
109 x_error_msg := 'AMV_CHN_ID_AND_NAME_MISS';
110 RAISE FND_API.G_EXC_ERROR;
111 ELSE
112 IF p_category_id = FND_API.G_MISS_NUM THEN
113 -- Must pass category id to identify by channel name
114 x_error_msg := 'AMV_CHN_CAT_ID_MISS';
115 RAISE FND_API.G_EXC_ERROR;
116 ELSE
117 IF AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) THEN
118 x_return_status := FND_API.G_RET_STS_SUCCESS;
119 OPEN Get_ChannelStatusByName;
120 FETCH Get_ChannelStatusByName INTO x_channel_id;
121 IF Get_ChannelStatusByName%FOUND THEN
122 x_exist_flag := FND_API.G_TRUE;
123 x_error_msg := 'AMV_CHN_NAME_EXISTS';
124 x_error_token := p_channel_name;
125 ELSE
126 -- Invalid channel name
127 x_exist_flag := FND_API.G_FALSE;
128 x_channel_id := FND_API.G_MISS_NUM;
129 x_error_msg := 'AMV_CHN_NAME_NOT_EXIST';
130 x_error_token := p_channel_name;
131 END IF;
132 CLOSE Get_ChannelStatusByName;
133 ELSE
134 x_error_msg := 'AMV_CAT_ID_NOT_EXIST';
135 x_error_token := p_category_id;
136 RAISE FND_API.G_EXC_ERROR;
137 END IF;
138 END IF;
139 END IF;
140 ELSE
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142 IF AMV_UTILITY_PVT.Is_ChannelIdValid(p_channel_id) THEN
143 x_exist_flag := FND_API.G_TRUE;
144 x_channel_id := p_channel_id;
145 x_error_msg := 'AMV_CHN_ID_EXISTS';
146 x_error_token := p_channel_id;
147 ELSE
148 -- Invalid channel id
149 x_exist_flag := FND_API.G_FALSE;
150 x_channel_id := FND_API.G_MISS_NUM;
151 x_error_msg := 'AMV_CHN_ID_NOT_EXIST';
152 x_error_token := p_channel_id;
153 END IF;
154 END IF;
155 EXCEPTION
156 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158 x_exist_flag := FND_API.G_FALSE;
159 x_channel_id := FND_API.G_MISS_NUM;
160 WHEN FND_API.G_EXC_ERROR THEN
161 x_return_status := FND_API.G_RET_STS_ERROR;
162 x_exist_flag := FND_API.G_FALSE;
163 x_channel_id := FND_API.G_MISS_NUM;
164 END Get_ChannelStatus;
165 --
166 --------------------------------------------------------------------------------
167 --
168 -- Start of comments
169 -- API name : Get_MatchOnStatus
170 -- Type : Private
171 -- Pre-reqs : None
172 -- Function : return channel match on flags status for a channel id
173 -- Parameters :
174 -- IN
175 -- p_channel_id IN NUMBER Required
176 -- OUT
177 -- Boolean
178 -- Version : Current version 1.0
179 -- Previous version 1.0
180 -- Initial version 1.0
181 -- Notes :
182 -- End of comments
183 --
184 --
185 FUNCTION Get_MatchOnStatus
186 ( p_channel_id IN NUMBER )
187 return boolean
188 IS
189 l_match_on_author varchar2(1);
190 l_match_on_keyword varchar2(1);
191 l_match_on_perspective varchar2(1);
192 l_match_on_item_type varchar2(1);
193 l_match_on_content_type varchar2(1);
194
195 CURSOR Match_On_Flags_csr IS
196 select match_on_author_flag
197 , match_on_keyword_flag
198 , match_on_perspective_flag
199 , match_on_item_type_flag
200 , match_on_content_type_flag
201 from amv_c_channels_b
202 where channel_id = p_channel_id;
203
204 BEGIN
205
206 OPEN Match_On_Flags_csr;
207 FETCH Match_On_Flags_csr INTO l_match_on_author,
208 l_match_on_keyword,
209 l_match_on_perspective,
210 l_match_on_item_type,
211 l_match_on_content_type;
212
213 IF l_match_on_author = FND_API.G_TRUE THEN
214 return TRUE;
215 ELSIF l_match_on_keyword = FND_API.G_TRUE THEN
216 return TRUE;
217 ELSIF l_match_on_perspective = FND_API.G_TRUE THEN
218 return TRUE;
219 ELSIF l_match_on_item_type = FND_API.G_TRUE THEN
220 return TRUE;
221 ELSIF l_match_on_content_type = FND_API.G_TRUE THEN
222 return TRUE;
223 ELSE
224 return FALSE;
225 END IF;
226 CLOSE Match_On_Flags_csr;
227
228 END;
229 --
230 --------------------------------------------------------------------------------
231 --
232 -- Start of comments
233 -- API name : Get_ChannelRecord
234 -- Type : Private
235 -- Pre-reqs : None
236 -- Function : return channel record for an channel id
237 -- Parameters :
238 -- p_channel_id IN NUMBER Required
239 -- OUT : x_channel_obj OUT AMV_CHANNEL_OBJ_TYPE
240 -- Version : Current version 1.0
241 -- Previous version 1.0
242 -- Initial version 1.0
243 -- Notes :
244 -- End of comments
245 --
246 --
247 PROCEDURE Get_ChannelRecord
248 ( p_channel_id IN NUMBER,
249 x_channel_obj OUT NOCOPY AMV_CHANNEL_OBJ_TYPE
250 )
251 IS
252 --
253 l_channel_id NUMBER;
254 l_object_version_number NUMBER;
255 l_channel_name VARCHAR2(80);
256 l_description VARCHAR2(2000);
257 l_channel_type VARCHAR2(30);
258 l_channel_category_id NUMBER;
259 l_status VARCHAR2(30);
260 l_owner_user_id NUMBER;
261 l_default_approver_user_id NUMBER;
262 l_effective_start_date DATE;
263 l_expiration_date DATE;
264 l_access_level_type VARCHAR2(30);
265 l_pub_need_approval_flag VARCHAR2(1);
266 l_sub_need_approval_flag VARCHAR2(1);
267 l_match_on_all_criteria_flag VARCHAR2(1);
268 l_match_on_keyword_flag VARCHAR2(1);
269 l_match_on_author_flag VARCHAR2(1);
270 l_match_on_perspective_flag VARCHAR2(1);
271 l_match_on_item_type_flag VARCHAR2(1);
272 l_match_on_content_type_flag VARCHAR2(1);
273 l_match_on_time_flag VARCHAR2(1);
274 l_application_id NUMBER;
275 l_external_access_flag VARCHAR2(1);
276 l_item_match_count NUMBER;
277 l_last_match_time DATE;
278 l_notification_interval_type VARCHAR2(30);
279 l_last_notification_time DATE;
280 l_attribute_category VARCHAR2(30);
281 l_attribute1 VARCHAR2(150);
282 l_attribute2 VARCHAR2(150);
283 l_attribute3 VARCHAR2(150);
284 l_attribute4 VARCHAR2(150);
285 l_attribute5 VARCHAR2(150);
286 l_attribute6 VARCHAR2(150);
287 l_attribute7 VARCHAR2(150);
288 l_attribute8 VARCHAR2(150);
289 l_attribute9 VARCHAR2(150);
290 l_attribute10 VARCHAR2(150);
291 l_attribute11 VARCHAR2(150);
292 l_attribute12 VARCHAR2(150);
293 l_attribute13 VARCHAR2(150);
294 l_attribute14 VARCHAR2(150);
295 l_attribute15 VARCHAR2(150);
296 --
297 CURSOR C_ChannelRecord IS
298 select b.channel_id,
299 b.object_version_number,
300 tl.channel_name,
301 tl.description,
302 b.channel_type,
303 b.channel_category_id,
304 b.status,
305 b.owner_user_id,
306 b.default_approver_user_id,
307 b.effective_start_date,
308 b.expiration_date,
309 b.access_level_type,
310 b.pub_need_approval_flag,
311 b.sub_need_approval_flag,
312 b.match_on_all_criteria_flag,
313 b.match_on_keyword_flag,
314 b.match_on_author_flag,
315 b.match_on_perspective_flag,
316 b.match_on_item_type_flag,
317 b.match_on_content_type_flag,
318 b.match_on_time_flag,
319 b.application_id,
320 b.external_access_flag,
321 b.item_match_count,
322 b.last_match_time,
323 b.notification_interval_type,
324 b.last_notification_time,
325 b.attribute_category,
326 b.attribute1,
327 b.attribute2,
328 b.attribute3,
329 b.attribute4,
330 b.attribute5,
331 b.attribute6,
332 b.attribute7,
333 b.attribute8,
334 b.attribute9,
335 b.attribute10,
336 b.attribute11,
337 b.attribute12,
338 b.attribute13,
339 b.attribute14,
340 b.attribute15
341 from amv_c_channels_b b
342 , amv_c_channels_tl tl
343 where b.channel_id = p_channel_id
344 and tl.language = userenv('lang')
345 and tl.channel_id = b.channel_id;
346 --
347 BEGIN
348
349 OPEN C_ChannelRecord;
350 FETCH C_ChannelRecord INTO x_channel_obj;
351 /*
352 l_channel_id,
353 l_object_version_number,
354 l_channel_name,
355 l_description,
356 l_channel_type,
357 l_channel_category_id,
358 l_status,
359 l_owner_user_id,
360 l_default_approver_user_id,
361 l_effective_start_date,
362 l_expiration_date,
363 l_access_level_type,
364 l_pub_need_approval_flag,
365 l_sub_need_approval_flag,
366 l_match_on_all_criteria_flag,
367 l_match_on_keyword_flag,
368 l_match_on_author_flag,
369 l_match_on_perspective_flag,
370 l_match_on_item_type_flag,
371 l_match_on_content_type_flag,
372 l_match_on_time_flag,
373 l_application_id,
374 l_external_access_flag,
375 l_item_match_count,
376 l_last_match_time,
377 l_notification_interval_type,
378 l_last_notification_time,
379 l_attribute_category,
380 l_attribute1,
381 l_attribute2,
382 l_attribute3,
383 l_attribute4,
384 l_attribute5,
385 l_attribute6,
386 l_attribute7,
387 l_attribute8,
388 l_attribute9,
389 l_attribute10,
393 l_attribute14,
390 l_attribute11,
391 l_attribute12,
392 l_attribute13,
394 l_attribute15;
395
396 x_channel_obj := amv_channel_obj_type(
397 l_channel_id,
398 l_object_version_number,
399 l_channel_name,
400 l_description,
401 l_channel_type,
402 l_channel_category_id,
403 l_status,
404 l_owner_user_id,
405 l_default_approver_user_id,
406 l_effective_start_date,
407 l_expiration_date,
408 l_access_level_type,
409 l_pub_need_approval_flag,
410 l_sub_need_approval_flag,
411 l_match_on_all_criteria_flag,
412 l_match_on_keyword_flag,
413 l_match_on_author_flag,
414 l_match_on_perspective_flag,
415 l_match_on_item_type_flag,
416 l_match_on_content_type_flag,
417 l_match_on_time_flag,
418 l_application_id,
419 l_external_access_flag,
420 l_item_match_count,
421 l_last_match_time,
422 l_notification_interval_type,
423 l_last_notification_time,
424 l_attribute_category,
425 l_attribute1,
426 l_attribute2,
427 l_attribute3,
428 l_attribute4,
429 l_attribute5,
430 l_attribute6,
431 l_attribute7,
432 l_attribute8,
433 l_attribute9,
434 l_attribute10,
435 l_attribute11,
436 l_attribute12,
437 l_attribute13,
438 l_attribute14,
439 l_attribute15);
440 */
441 CLOSE C_ChannelRecord;
442
443 END Get_ChannelRecord;
444 --
445 --------------------------------------------------------------------------------
446 --------------------------------------------------------------------------------
447 -- Start of comments
448 -- API name : Add_Channel
449 -- Type : Private
450 -- Pre-reqs : None
451 -- Function : Create a new channel
452 -- Parameters :
453 -- IN p_api_version IN NUMBER Required
454 -- p_init_msg_list IN VARCHAR2 Optional
455 -- Default = FND_API.G_FALSE
456 -- p_commit IN VARCHAR2 Optional
457 -- Default = FND_API.G_FALSE
458 -- p_validation_level IN NUMBER Optional
459 -- Default = FND_API.G_VALID_LEVEL_FULL
460 -- p_channel_record IN AMV_CHANNEL_OBJ_TYPE Required
461 -- OUT : x_return_status OUT VARCHAR2
462 -- x_msg_count OUT NUMBER
463 -- x_msg_data OUT VARCHAR2
464 -- x_channel_id OUT NUMBER
465 -- Version : Current version 1.0
466 -- Previous version 1.0
467 -- Initial version 1.0
468 -- Notes :
469 --
470 -- End of comments
471 --
472 PROCEDURE Add_Channel
473 ( p_api_version IN NUMBER,
474 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
475 p_commit IN VARCHAR2 := FND_API.G_FALSE,
476 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
477 x_return_status OUT NOCOPY VARCHAR2,
478 x_msg_count OUT NOCOPY NUMBER,
479 x_msg_data OUT NOCOPY VARCHAR2,
480 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
481 p_channel_record IN AMV_CHANNEL_OBJ_TYPE,
482 x_channel_id OUT NOCOPY NUMBER
483 )
484 IS
485 l_api_name CONSTANT VARCHAR2(30) := 'Add_Channel';
486 l_api_version CONSTANT NUMBER := 1.0;
487 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
488 --
489 l_resource_id number;
490 l_user_id number;
491 l_login_user_id number;
492 l_login_user_status varchar2(30);
493 l_Error_Msg varchar2(2000);
494 l_Error_Token varchar2(80);
495 l_object_version_number number := 1;
496 --
497 l_channel_id number;
498 l_channel_exist_flag varchar2(1);
499 l_null_value varchar2(30) := null;
500 l_row_id varchar2(30);
501 l_mychannel_id number;
502 l_expiration_date date;
503 l_channel_obj AMV_CHANNEL_OBJ_TYPE;
504 l_error_flag varchar2(1);
505
506 CURSOR ChannelId_Seq IS
507 select amv_c_channels_b_s.nextval
508 from dual;
509 BEGIN
510 -- Standard begin of API savepoint
511 SAVEPOINT Add_Channel_PVT;
512 -- Standard call to check for call compatibility.
513 IF NOT FND_API.Compatible_API_Call (
514 l_api_version,
515 p_api_version,
516 l_api_name,
517 G_PKG_NAME)
518 THEN
519 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
520 END IF;
521 -- Debug Message
522 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
523 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
524 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
525 FND_MSG_PUB.Add;
526 END IF;
527 --Initialize message list if p_init_msg_list is TRUE.
528 IF FND_API.To_Boolean (p_init_msg_list) THEN
529 FND_MSG_PUB.initialize;
530 END IF;
531 -- Get the current (login) user id.
532 AMV_UTILITY_PVT.Get_UserInfo(
533 x_resource_id => l_resource_id,
534 x_user_id => l_user_id,
535 x_login_id => l_login_user_id,
539 IF (p_check_login_user = FND_API.G_TRUE) THEN
536 x_user_status => l_login_user_status
537 );
538 -- check login user
540 -- Check if user is login and has the required privilege.
541 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
542 -- User is not login.
543 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
544 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
545 FND_MSG_PUB.Add;
546 END IF;
547 RAISE FND_API.G_EXC_ERROR;
548 END IF;
549 END IF;
550 -- This fix is for executing api in sqlplus mode
551 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
552 l_login_user_id := g_login_user_id;
553 l_user_id := g_user_id;
554 l_resource_id := g_resource_id;
555 END IF;
556 -- Initialize API return status to sucess
557 x_return_status := FND_API.G_RET_STS_SUCCESS;
558
559 --
560 -- verify channel object
561 -- error causing channel columns
562 IF (p_channel_record.channel_type = FND_API.G_MISS_CHAR) THEN
563 l_error_flag := FND_API.G_TRUE;
564 END IF;
565 IF (p_channel_record.access_level_type = FND_API.G_MISS_CHAR) THEN
566 l_error_flag := FND_API.G_TRUE;
567 END IF;
568 IF (p_channel_record.owner_user_id = FND_API.G_MISS_NUM) THEN
569 l_error_flag := FND_API.G_TRUE;
570 END IF;
571 IF (p_channel_record.default_approver_user_id = FND_API.G_MISS_NUM) THEN
572 l_error_flag := FND_API.G_TRUE;
573 END IF;
574
575 -- raise exception if null values passed
576 IF l_error_flag = FND_API.G_TRUE THEN
577 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
578 THEN
579 FND_MESSAGE.Set_Name('AMV', 'AMV_INVALID_OBJ_VALUES');
580 FND_MESSAGE.Set_Token('TKN', 'Channel');
581 FND_MSG_PUB.Add;
582 END IF;
583 RAISE FND_API.G_EXC_ERROR;
584 END IF;
585
586 -- default values settings for inconsistant types
587 l_channel_obj := p_channel_record;
588
589 IF (p_channel_record.status = FND_API.G_MISS_CHAR) THEN
590 l_channel_obj.status := AMV_UTILITY_PVT.G_ACTIVE;
591 END IF;
592
593 IF (p_channel_record.effective_start_date = FND_API.G_MISS_DATE) THEN
594 l_channel_obj.effective_start_date := sysdate;
595 END IF;
596
597 IF (p_channel_record.pub_need_approval_flag = FND_API.G_MISS_CHAR) THEN
598 l_channel_obj.pub_need_approval_flag := FND_API.G_FALSE;
599 END IF;
600
601 IF (p_channel_record.sub_need_approval_flag = FND_API.G_MISS_CHAR) THEN
602 l_channel_obj.sub_need_approval_flag := FND_API.G_FALSE;
603 END IF;
604
605 IF (p_channel_record.match_on_all_criteria_flag = FND_API.G_MISS_CHAR) THEN
606 l_channel_obj.match_on_all_criteria_flag := FND_API.G_FALSE;
607 END IF;
608
609 IF (p_channel_record.match_on_keyword_flag = FND_API.G_MISS_CHAR) THEN
610 l_channel_obj.match_on_keyword_flag := FND_API.G_FALSE;
611 END IF;
612
613 IF (p_channel_record.match_on_author_flag = FND_API.G_MISS_CHAR) THEN
614 l_channel_obj.match_on_author_flag := FND_API.G_FALSE;
615 END IF;
616
617 IF (p_channel_record.match_on_perspective_flag = FND_API.G_MISS_CHAR) THEN
618 l_channel_obj.match_on_perspective_flag := FND_API.G_FALSE;
619 END IF;
620
621 IF (p_channel_record.match_on_content_type_flag = FND_API.G_MISS_CHAR) THEN
622 l_channel_obj.match_on_content_type_flag := FND_API.G_FALSE;
623 END IF;
624
625 IF (p_channel_record.match_on_item_type_flag = FND_API.G_MISS_CHAR) THEN
626 l_channel_obj.match_on_item_type_flag := FND_API.G_FALSE;
627 END IF;
628
629 IF (p_channel_record.match_on_time_flag = FND_API.G_MISS_CHAR) THEN
630 l_channel_obj.match_on_time_flag := FND_API.G_FALSE;
631 END IF;
632
633 IF (p_channel_record.application_id = FND_API.G_MISS_NUM) THEN
634 l_channel_obj.application_id := null;
635 END IF;
636
637 IF (p_channel_record.external_access_flag = FND_API.G_MISS_CHAR) THEN
638 l_channel_obj.external_access_flag := FND_API.G_FALSE;
639 END IF;
640
641 IF (p_channel_record.item_match_count = FND_API.G_MISS_NUM) THEN
642 l_channel_obj.item_match_count := 0;
643 END IF;
644
645 -- Check if channel name already exists
646 Get_ChannelStatus (
647 x_return_status => x_return_status,
648 p_channel_name => p_channel_record.channel_name,
649 p_category_id => p_channel_record.channel_category_id,
650 x_exist_flag => l_channel_exist_flag,
651 x_channel_id => l_channel_id,
652 x_error_msg => l_Error_Msg,
653 x_error_token => l_Error_Token
654 );
655
656 -- Add channel if it does not exist
657 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
658 IF (l_channel_exist_flag = FND_API.G_TRUE) THEN
659 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
660 THEN
661 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
662 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
663 FND_MSG_PUB.Add;
664 END IF;
665 RAISE FND_API.G_EXC_ERROR;
666 ELSE
667 -- set expiration date to null if none passed
671 l_expiration_date := p_channel_record.expiration_date;
668 IF p_channel_record.expiration_date = FND_API.G_MISS_DATE THEN
669 l_expiration_date := null;
670 ELSE
672 END IF;
673
674 -- Select the channel sequence
675 OPEN ChannelId_Seq;
676 FETCH ChannelId_Seq INTO l_channel_id;
677 CLOSE ChannelId_Seq;
678
679 -- Create a new channel
680 BEGIN
681 AMV_C_CHANNELS_PKG.INSERT_ROW(
682 X_ROWID => l_row_id,
683 X_CHANNEL_ID => l_channel_id,
684 X_OBJECT_VERSION_NUMBER => l_object_version_number,
685 X_CHANNEL_TYPE => p_channel_record.channel_type,
686 X_CHANNEL_CATEGORY_ID => p_channel_record.channel_category_id,
687 X_STATUS => l_channel_obj.status,
688 X_OWNER_USER_ID => p_channel_record.owner_user_id,
689 X_DEFAULT_APPROVER_USER_ID =>
690 p_channel_record.default_approver_user_id,
691 X_EFFECTIVE_START_DATE => l_channel_obj.effective_start_date,
692 X_EXPIRATION_DATE => l_expiration_date,
693 X_ACCESS_LEVEL_TYPE => p_channel_record.access_level_type,
694 X_PUB_NEED_APPROVAL_FLAG =>
695 l_channel_obj.pub_need_approval_flag,
696 X_SUB_NEED_APPROVAL_FLAG =>
697 l_channel_obj.sub_need_approval_flag,
698 X_MATCH_ON_ALL_CRITERIA_FLAG =>
699 l_channel_obj.match_on_all_criteria_flag,
700 X_MATCH_ON_KEYWORD_FLAG =>
701 l_channel_obj.match_on_keyword_flag,
702 X_MATCH_ON_AUTHOR_FLAG => l_channel_obj.match_on_author_flag,
703 X_MATCH_ON_PERSPECTIVE_FLAG =>
704 l_channel_obj.match_on_perspective_flag,
705 X_MATCH_ON_ITEM_TYPE_FLAG =>
706 l_channel_obj.match_on_item_type_flag,
707 X_MATCH_ON_CONTENT_TYPE_FLAG =>
708 l_channel_obj.match_on_content_type_flag,
709 X_MATCH_ON_TIME_FLAG => l_channel_obj.match_on_time_flag,
710 X_APPLICATION_ID => l_channel_obj.application_id,
711 X_EXTERNAL_ACCESS_FLAG => l_channel_obj.external_access_flag,
712 X_ITEM_MATCH_COUNT =>l_channel_obj.item_match_count,
713 X_LAST_MATCH_TIME => l_channel_obj.last_match_time,
714 X_NOTIFICATION_INTERVAL_TYPE =>
715 l_channel_obj.notification_interval_type,
716 X_LAST_NOTIFICATION_TIME =>
717 l_channel_obj.last_notification_time,
718 X_ATTRIBUTE_CATEGORY => l_channel_obj.attribute_category,
719 X_ATTRIBUTE1 => l_channel_obj.attribute1,
720 X_ATTRIBUTE2 => l_channel_obj.attribute2,
721 X_ATTRIBUTE3 => l_channel_obj.attribute3,
722 X_ATTRIBUTE4 => l_channel_obj.attribute4,
723 X_ATTRIBUTE5 => l_channel_obj.attribute5,
724 X_ATTRIBUTE6 => l_channel_obj.attribute6,
725 X_ATTRIBUTE7 => l_channel_obj.attribute7,
726 X_ATTRIBUTE8 => l_channel_obj.attribute8,
727 X_ATTRIBUTE9 => l_channel_obj.attribute9,
728 X_ATTRIBUTE10 => l_channel_obj.attribute10,
729 X_ATTRIBUTE11 => l_channel_obj.attribute11,
730 X_ATTRIBUTE12 => l_channel_obj.attribute12,
731 X_ATTRIBUTE13 => l_channel_obj.attribute13,
732 X_ATTRIBUTE14 => l_channel_obj.attribute14,
733 X_ATTRIBUTE15 => l_channel_obj.attribute15,
734 X_CHANNEL_NAME => l_channel_obj.channel_name,
735 X_DESCRIPTION => l_channel_obj.description,
736 X_CREATION_DATE => sysdate,
737 X_CREATED_BY => l_user_id,
738 X_LAST_UPDATE_DATE => sysdate,
739 X_LAST_UPDATED_BY => l_user_id,
740 X_LAST_UPDATE_LOGIN => l_login_user_id
741 );
742 EXCEPTION
743 WHEN OTHERS THEN
744 --will log the error
745 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
746 THEN
747 FND_MESSAGE.Set_Name('AMV', 'AMV_TABLE_HANDLER_ERROR');
748 FND_MESSAGE.Set_Token('ACTION', 'Adding Channel');
749 FND_MESSAGE.Set_Token('TABLE', sqlerrm);
750 FND_MSG_PUB.Add;
751 END IF;
752 RAISE FND_API.G_EXC_ERROR;
753 END;
754 -- Pass the channel id created
755 x_channel_id := l_channel_id;
756 END IF;
757 ELSE
758 -- exception errors from get_channelstatus
759 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
760 THEN
761 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
762 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
763 FND_MSG_PUB.Add;
764 END IF;
765 RAISE FND_API.G_EXC_ERROR;
766 END IF;
767 --
768
769 -- Success message
770 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
771 THEN
772 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
773 FND_MESSAGE.Set_Token('ROW', l_full_name);
774 FND_MSG_PUB.Add;
775 END IF;
776 --Standard check of commit
777 IF FND_API.To_Boolean ( p_commit ) THEN
778 COMMIT WORK;
779 END IF;
780 -- Debug Message
781 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
782 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
783 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
784 FND_MSG_PUB.Add;
785 END IF;
786 --Standard call to get message count and if count=1, get the message
787 FND_MSG_PUB.Count_And_Get (
788 p_encoded => FND_API.G_FALSE,
789 p_count => x_msg_count,
790 p_data => x_msg_data
791 );
792 EXCEPTION
793 WHEN FND_API.G_EXC_ERROR THEN
794 ROLLBACK TO Add_Channel_PVT;
798 p_encoded => FND_API.G_FALSE,
795 x_return_status := FND_API.G_RET_STS_ERROR;
796 -- Standard call to get message count and if count=1, get the message
797 FND_MSG_PUB.Count_And_Get (
799 p_count => x_msg_count,
800 p_data => x_msg_data
801 );
802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
803 ROLLBACK TO Add_Channel_PVT;
804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
805 -- Standard call to get message count and if count=1, get the message
806 FND_MSG_PUB.Count_And_Get (
807 p_encoded => FND_API.G_FALSE,
808 p_count => x_msg_count,
809 p_data => x_msg_data
810 );
811 WHEN OTHERS THEN
812 ROLLBACK TO Add_Channel_PVT;
813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
814 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
815 THEN
816 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
817 END IF;
818 -- Standard call to get message count and if count=1, get the message
819 FND_MSG_PUB.Count_And_Get (
820 p_encoded => FND_API.G_FALSE,
821 p_count => x_msg_count,
822 p_data => x_msg_data
823 );
824 --
825 END Add_Channel;
826 --------------------------------------------------------------------------------
827 -- Start of comments
828 -- API name : Add_PublicChannel
829 -- Type : Private
830 -- Pre-reqs : None
831 -- Function : Create a new content public channel
832 -- Parameters :
833 -- IN p_api_version IN NUMBER Required
834 -- p_init_msg_list IN VARCHAR2 Optional
835 -- Default = FND_API.G_FALSE
836 -- p_commit IN VARCHAR2 Optional
837 -- Default = FND_API.G_FALSE
838 -- p_validation_level IN NUMBER Optional
839 -- Default = FND_API.G_VALID_LEVEL_FULL
840 -- p_channel_record IN AMV_CHANNEL_OBJ_TYPE Required
841 -- OUT : x_return_status OUT VARCHAR2
842 -- x_msg_count OUT NUMBER
843 -- x_msg_data OUT VARCHAR2
844 -- x_channel_id OUT NUMBER
845 -- Version : Current version 1.0
846 -- Previous version 1.0
847 -- Initial version 1.0
848 -- Notes :
849 --
850 -- End of comments
851 --
852 PROCEDURE Add_PublicChannel
853 ( p_api_version IN NUMBER,
854 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
855 p_commit IN VARCHAR2 := FND_API.G_FALSE,
856 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
857 x_return_status OUT NOCOPY VARCHAR2,
858 x_msg_count OUT NOCOPY NUMBER,
859 x_msg_data OUT NOCOPY VARCHAR2,
860 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
861 p_channel_record IN AMV_CHANNEL_OBJ_TYPE,
862 x_channel_id OUT NOCOPY NUMBER
863 )
864 IS
865 l_api_name CONSTANT VARCHAR2(30) := 'Add_PublicChannel';
866 l_api_version CONSTANT NUMBER := 1.0;
867 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
868 --
869 l_resource_id number;
870 l_user_id number;
871 l_login_user_id number;
872 l_login_user_status varchar2(30);
873 l_Error_Msg varchar2(2000);
874 l_Error_Token varchar2(80);
875 l_object_version_number number := 1;
876 --
877 l_channel_id number;
878 l_channel_exist_flag varchar2(1);
879 l_setup_result varchar2(1);
880 l_null_value varchar2(30) := null;
881 l_row_id varchar2(30);
882 l_mychannel_id number;
883 l_expiration_date date;
884 l_mychannel_obj AMV_MYCHANNEL_PVT.AMV_MY_CHANNEL_OBJ_TYPE;
885
886 BEGIN
887 -- Standard begin of API savepoint
888 SAVEPOINT Add_PublicChannel_PVT;
889 -- Standard call to check for call compatibility.
890 IF NOT FND_API.Compatible_API_Call (
891 l_api_version,
892 p_api_version,
893 l_api_name,
894 G_PKG_NAME)
895 THEN
896 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
897 END IF;
898 -- Debug Message
899 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
900 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
901 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
902 FND_MSG_PUB.Add;
903 END IF;
904 --Initialize message list if p_init_msg_list is TRUE.
905 IF FND_API.To_Boolean (p_init_msg_list) THEN
906 FND_MSG_PUB.initialize;
907 END IF;
908 -- Get the current (login) user id.
909 AMV_UTILITY_PVT.Get_UserInfo(
910 x_resource_id => l_resource_id,
911 x_user_id => l_user_id,
912 x_login_id => l_login_user_id,
913 x_user_status => l_login_user_status
914 );
915 -- check login user
916 IF (p_check_login_user = FND_API.G_TRUE) THEN
917 -- Check if user is login and has the required privilege.
918 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
919 -- User is not login.
920 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
921 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
925 END IF;
922 FND_MSG_PUB.Add;
923 END IF;
924 RAISE FND_API.G_EXC_ERROR;
926 END IF;
927 -- This fix is for executing api in sqlplus mode
928 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
929 l_login_user_id := g_login_user_id;
930 l_user_id := g_user_id;
931 l_resource_id := g_resource_id;
932 END IF;
933 -- Initialize API return status to sucess
934 x_return_status := FND_API.G_RET_STS_SUCCESS;
935
936 --
937 AMV_USER_PVT.Can_SetupChannel (
938 p_api_version => l_api_version,
939 p_init_msg_list => FND_API.G_FALSE,
940 p_validation_level => p_validation_level,
941 x_return_status => x_return_status,
942 x_msg_count => x_msg_count,
943 x_msg_data => x_msg_data,
944 p_check_login_user => FND_API.G_FALSE,
945 p_resource_id => p_channel_record.owner_user_id,
946 p_include_group_flag => FND_API.G_TRUE,
947 x_result_flag => l_setup_result
948 );
949
950 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
951 IF (l_setup_result = FND_API.G_TRUE) THEN
952 IF p_channel_record.channel_type = AMV_UTILITY_PVT.G_CONTENT THEN
953 IF p_channel_record.access_level_type =
954 AMV_UTILITY_PVT.G_PUBLIC THEN
955 -- create the channel
956 AMV_CHANNEL_PVT.Add_Channel (
957 p_api_version => l_api_version,
958 p_init_msg_list => FND_API.G_FALSE,
959 p_validation_level => p_validation_level,
960 x_return_status => x_return_status,
961 x_msg_count => x_msg_count,
962 x_msg_data => x_msg_data,
963 p_check_login_user => FND_API.G_FALSE,
964 p_channel_record => p_channel_record,
965 x_channel_id => x_channel_id
966 );
967 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
969 END IF;
970 -- invoke macthing engine for populating items into channel
971 AMV_MATCH_PVT.Request_ChannelMatch
972 (
973 p_api_version => l_api_version,
974 x_return_status => x_return_status,
975 x_msg_count => x_msg_count,
976 x_msg_data => x_msg_data,
977 p_check_login_user => FND_API.G_FALSE,
978 p_channel_id => x_channel_id
979 );
980 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
982 END IF;
983 ELSE
984 -- Invalid channel type passed
985 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
986 THEN
987 FND_MESSAGE.Set_Name('AMV','AMV_INVALID_CHAN_TYPE');
988 FND_MESSAGE.Set_Token('TKN','Content');
989 FND_MSG_PUB.Add;
990 END IF;
991 END IF;
992 ELSE
993 -- Invalid channel type passed
994 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
995 THEN
996 FND_MESSAGE.Set_Name('AMV','AMV_INVALID_CHAN_ACCS');
997 FND_MESSAGE.Set_Token('TKN','Public');
998 FND_MSG_PUB.Add;
999 END IF;
1000 END IF;
1001 ELSE
1002 -- user does not have privilege to create public channel
1003 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1004 THEN
1005 FND_MESSAGE.Set_Name('AMV', 'AMV_NO_ACCESS_ERROR');
1006 FND_MESSAGE.Set_Token('LEVEL','Channel');
1007 FND_MSG_PUB.Add;
1008 END IF;
1009 RAISE FND_API.G_EXC_ERROR;
1010 END IF;
1011 ELSE
1012 -- error while user privilege check in Can_SetupChannel
1013 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1014 THEN
1015 FND_MESSAGE.Set_Name('AMV', 'AMV_SETUP_CHECK_ERROR');
1016 FND_MESSAGE.Set_Token('LEVEL','Channel');
1017 FND_MSG_PUB.Add;
1018 END IF;
1019 RAISE FND_API.G_EXC_ERROR;
1020 END IF;
1021 --
1022
1023 -- Success message
1024 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1025 THEN
1026 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1027 FND_MESSAGE.Set_Token('ROW', l_full_name);
1028 FND_MSG_PUB.Add;
1029 END IF;
1030 --Standard check of commit
1031 IF FND_API.To_Boolean ( p_commit ) THEN
1032 COMMIT WORK;
1033 END IF;
1034 -- Debug Message
1035 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1036 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1037 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1038 FND_MSG_PUB.Add;
1039 END IF;
1040 --Standard call to get message count and if count=1, get the message
1041 FND_MSG_PUB.Count_And_Get (
1042 p_encoded => FND_API.G_FALSE,
1043 p_count => x_msg_count,
1044 p_data => x_msg_data
1045 );
1046 EXCEPTION
1047 WHEN FND_API.G_EXC_ERROR THEN
1048 ROLLBACK TO Add_PublicChannel_PVT;
1049 x_return_status := FND_API.G_RET_STS_ERROR;
1050 -- Standard call to get message count and if count=1, get the message
1051 FND_MSG_PUB.Count_And_Get (
1052 p_encoded => FND_API.G_FALSE,
1053 p_count => x_msg_count,
1054 p_data => x_msg_data
1055 );
1056 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1060 FND_MSG_PUB.Count_And_Get (
1057 ROLLBACK TO Add_PublicChannel_PVT;
1058 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1059 -- Standard call to get message count and if count=1, get the message
1061 p_encoded => FND_API.G_FALSE,
1062 p_count => x_msg_count,
1063 p_data => x_msg_data
1064 );
1065 WHEN OTHERS THEN
1066 ROLLBACK TO Add_PublicChannel_PVT;
1067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1068 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1069 THEN
1070 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1071 END IF;
1072 -- Standard call to get message count and if count=1, get the message
1073 FND_MSG_PUB.Count_And_Get (
1074 p_encoded => FND_API.G_FALSE,
1075 p_count => x_msg_count,
1076 p_data => x_msg_data
1077 );
1078 --
1079 END Add_PublicChannel;
1080 --------------------------------------------------------------------------------
1081 --------------------------------------------------------------------------------
1082 -- Start of comments
1083 -- API name : Add_ProtectedChannel
1084 -- Type : Private
1085 -- Pre-reqs : None
1086 -- Function : Create a new content protected channel
1087 -- Parameters :
1088 -- IN p_api_version IN NUMBER Required
1089 -- p_init_msg_list IN VARCHAR2 Optional
1090 -- Default = FND_API.G_FALSE
1091 -- p_commit IN VARCHAR2 Optional
1092 -- Default = FND_API.G_FALSE
1093 -- p_validation_level IN NUMBER Optional
1094 -- Default = FND_API.G_VALID_LEVEL_FULL
1095 -- p_channel_record IN AMV_CHANNEL_OBJ_TYPE Required
1096 -- OUT : x_return_status OUT VARCHAR2
1097 -- x_msg_count OUT NUMBER
1098 -- x_msg_data OUT VARCHAR2
1099 -- x_channel_id OUT NUMBER
1100 -- Version : Current version 1.0
1101 -- Previous version 1.0
1102 -- Initial version 1.0
1103 -- Notes :
1104 --
1105 -- End of comments
1106 --
1107 PROCEDURE Add_ProtectedChannel
1108 ( p_api_version IN NUMBER,
1109 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1110 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1111 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1112 x_return_status OUT NOCOPY VARCHAR2,
1113 x_msg_count OUT NOCOPY NUMBER,
1114 x_msg_data OUT NOCOPY VARCHAR2,
1115 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1116 p_channel_record IN AMV_CHANNEL_OBJ_TYPE,
1117 x_channel_id OUT NOCOPY NUMBER
1118 )
1119 IS
1120 l_api_name CONSTANT VARCHAR2(30) := 'Add_ProtectedChannel';
1121 l_api_version CONSTANT NUMBER := 1.0;
1122 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1123 --
1124 l_resource_id number;
1125 l_user_id number;
1126 l_login_user_id number;
1127 l_login_user_status varchar2(30);
1128 l_Error_Msg varchar2(2000);
1129 l_Error_Token varchar2(80);
1130 l_object_version_number number := 1;
1131 --
1132 l_channel_id number;
1133 l_channel_exist_flag varchar2(1);
1134 l_setup_result varchar2(1);
1135 l_null_value varchar2(30) := null;
1136 l_row_id varchar2(30);
1137 l_mychannel_id number;
1138 l_expiration_date date;
1139 l_mychannel_obj AMV_MYCHANNEL_PVT.AMV_MY_CHANNEL_OBJ_TYPE;
1140
1141 BEGIN
1142 -- Standard begin of API savepoint
1143 SAVEPOINT Add_ProtectedChannel_PVT;
1144 -- Standard call to check for call compatibility.
1145 IF NOT FND_API.Compatible_API_Call (
1146 l_api_version,
1147 p_api_version,
1148 l_api_name,
1149 G_PKG_NAME)
1150 THEN
1151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1152 END IF;
1153 -- Debug Message
1154 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1155 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1156 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1157 FND_MSG_PUB.Add;
1158 END IF;
1159 --Initialize message list if p_init_msg_list is TRUE.
1160 IF FND_API.To_Boolean (p_init_msg_list) THEN
1161 FND_MSG_PUB.initialize;
1162 END IF;
1163 -- Get the current (login) user id.
1164 AMV_UTILITY_PVT.Get_UserInfo(
1165 x_resource_id => l_resource_id,
1166 x_user_id => l_user_id,
1167 x_login_id => l_login_user_id,
1168 x_user_status => l_login_user_status
1169 );
1170 -- check login user
1171 IF (p_check_login_user = FND_API.G_TRUE) THEN
1172 -- Check if user is login and has the required privilege.
1173 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1174 -- User is not login.
1175 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1176 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1177 FND_MSG_PUB.Add;
1178 END IF;
1179 RAISE FND_API.G_EXC_ERROR;
1180 END IF;
1181 END IF;
1182 -- This fix is for executing api in sqlplus mode
1186 l_resource_id := g_resource_id;
1183 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1184 l_login_user_id := g_login_user_id;
1185 l_user_id := g_user_id;
1187 END IF;
1188 -- Initialize API return status to sucess
1189 x_return_status := FND_API.G_RET_STS_SUCCESS;
1190
1191 --
1192 AMV_USER_PVT.Can_SetupChannel (
1193 p_api_version => l_api_version,
1194 p_init_msg_list => FND_API.G_FALSE,
1195 p_validation_level => p_validation_level,
1196 x_return_status => x_return_status,
1197 x_msg_count => x_msg_count,
1198 x_msg_data => x_msg_data,
1199 p_check_login_user => FND_API.G_FALSE,
1200 p_resource_id => p_channel_record.owner_user_id,
1201 p_include_group_flag => FND_API.G_TRUE,
1202 x_result_flag => l_setup_result
1203 );
1204
1205 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1206 IF (l_setup_result = FND_API.G_TRUE) THEN
1207 IF p_channel_record.channel_type = AMV_UTILITY_PVT.G_CONTENT THEN
1208 IF p_channel_record.access_level_type =
1209 AMV_UTILITY_PVT.G_PROTECTED THEN
1210 -- create the channel
1211 AMV_CHANNEL_PVT.Add_Channel (
1212 p_api_version => l_api_version,
1213 p_init_msg_list => FND_API.G_FALSE,
1214 p_validation_level => p_validation_level,
1215 x_return_status => x_return_status,
1216 x_msg_count => x_msg_count,
1217 x_msg_data => x_msg_data,
1218 p_check_login_user => FND_API.G_FALSE,
1219 p_channel_record => p_channel_record,
1220 x_channel_id => x_channel_id
1221 );
1222 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1224 END IF;
1225 -- invoke macthing engine for populating items into channel
1226 AMV_MATCH_PVT.Request_ChannelMatch
1227 (
1228 p_api_version => l_api_version,
1229 x_return_status => x_return_status,
1230 x_msg_count => x_msg_count,
1231 x_msg_data => x_msg_data,
1232 p_check_login_user => FND_API.G_FALSE,
1233 p_channel_id => x_channel_id
1234 );
1235 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1237 END IF;
1238 ELSE
1239 -- Invalid channel type passed
1240 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1241 THEN
1242 FND_MESSAGE.Set_Name('AMV','AMV_INVALID_CHAN_TYPE');
1243 FND_MESSAGE.Set_Token('TKN','Content');
1244 FND_MSG_PUB.Add;
1245 END IF;
1246 END IF;
1247 ELSE
1248 -- Invalid channel type passed
1249 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1250 THEN
1251 FND_MESSAGE.Set_Name('AMV','AMV_INVALID_CHAN_ACCS');
1252 FND_MESSAGE.Set_Token('TKN','Protected');
1253 FND_MSG_PUB.Add;
1254 END IF;
1255 END IF;
1256 ELSE
1257 -- user does not have privilege to create public channel
1258 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1259 THEN
1260 FND_MESSAGE.Set_Name('AMV', 'AMV_NO_ACCESS_ERROR');
1261 FND_MESSAGE.Set_Token('LEVEL','Channel');
1262 FND_MSG_PUB.Add;
1263 END IF;
1264 RAISE FND_API.G_EXC_ERROR;
1265 END IF;
1266 ELSE
1267 -- error while user privilege check in Can_SetupChannel
1268 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1269 THEN
1270 FND_MESSAGE.Set_Name('AMV', 'AMV_SETUP_CHECK_ERROR');
1271 FND_MESSAGE.Set_Token('LEVEL','Channel');
1272 FND_MSG_PUB.Add;
1273 END IF;
1274 RAISE FND_API.G_EXC_ERROR;
1275 END IF;
1276 --
1277
1278 -- Success message
1279 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1280 THEN
1281 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1282 FND_MESSAGE.Set_Token('ROW', l_full_name);
1283 FND_MSG_PUB.Add;
1284 END IF;
1285 --Standard check of commit
1286 IF FND_API.To_Boolean ( p_commit ) THEN
1287 COMMIT WORK;
1288 END IF;
1289 -- Debug Message
1290 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1291 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1292 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1293 FND_MSG_PUB.Add;
1294 END IF;
1295 --Standard call to get message count and if count=1, get the message
1296 FND_MSG_PUB.Count_And_Get (
1297 p_encoded => FND_API.G_FALSE,
1298 p_count => x_msg_count,
1299 p_data => x_msg_data
1300 );
1301 EXCEPTION
1302 WHEN FND_API.G_EXC_ERROR THEN
1303 ROLLBACK TO Add_ProtectedChannel_PVT;
1304 x_return_status := FND_API.G_RET_STS_ERROR;
1305 -- Standard call to get message count and if count=1, get the message
1306 FND_MSG_PUB.Count_And_Get (
1307 p_encoded => FND_API.G_FALSE,
1308 p_count => x_msg_count,
1309 p_data => x_msg_data
1310 );
1311 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1312 ROLLBACK TO Add_ProtectedChannel_PVT;
1313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1314 -- Standard call to get message count and if count=1, get the message
1315 FND_MSG_PUB.Count_And_Get (
1319 );
1316 p_encoded => FND_API.G_FALSE,
1317 p_count => x_msg_count,
1318 p_data => x_msg_data
1320 WHEN OTHERS THEN
1321 ROLLBACK TO Add_ProtectedChannel_PVT;
1322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1323 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1324 THEN
1325 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1326 END IF;
1327 -- Standard call to get message count and if count=1, get the message
1328 FND_MSG_PUB.Count_And_Get (
1329 p_encoded => FND_API.G_FALSE,
1330 p_count => x_msg_count,
1331 p_data => x_msg_data
1332 );
1333 --
1334 END Add_ProtectedChannel;
1335 --------------------------------------------------------------------------------
1336 --------------------------------------------------------------------------------
1337 -- Start of comments
1338 -- API name : Add_PrivateChannel
1339 -- Type : Private
1340 -- Pre-reqs : None
1341 -- Function : Create a new content private channel
1342 -- Parameters :
1343 -- IN p_api_version IN NUMBER Required
1344 -- p_init_msg_list IN VARCHAR2 Optional
1345 -- Default = FND_API.G_FALSE
1346 -- p_commit IN VARCHAR2 Optional
1347 -- Default = FND_API.G_FALSE
1348 -- p_validation_level IN NUMBER Optional
1349 -- Default = FND_API.G_VALID_LEVEL_FULL
1350 -- p_channel_record IN AMV_CHANNEL_OBJ_TYPE Required
1351 -- OUT : x_return_status OUT VARCHAR2
1352 -- x_msg_count OUT NUMBER
1353 -- x_msg_data OUT VARCHAR2
1354 -- x_channel_id OUT NUMBER
1355 -- Version : Current version 1.0
1356 -- Previous version 1.0
1357 -- Initial version 1.0
1358 -- Notes :
1359 --
1360 -- End of comments
1361 --
1362 PROCEDURE Add_PrivateChannel
1363 ( p_api_version IN NUMBER,
1364 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1365 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1366 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1367 x_return_status OUT NOCOPY VARCHAR2,
1368 x_msg_count OUT NOCOPY NUMBER,
1369 x_msg_data OUT NOCOPY VARCHAR2,
1370 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1371 p_channel_record IN AMV_CHANNEL_OBJ_TYPE,
1372 x_channel_id OUT NOCOPY NUMBER
1373 )
1374 IS
1375 l_api_name CONSTANT VARCHAR2(30) := 'Add_PrivateChannel';
1376 l_api_version CONSTANT NUMBER := 1.0;
1377 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1378 --
1379 l_resource_id number;
1380 l_user_id number;
1381 l_login_user_id number;
1382 l_login_user_status varchar2(30);
1383 l_Error_Msg varchar2(2000);
1384 l_Error_Token varchar2(80);
1385 l_object_version_number number := 1;
1386 --
1387 l_channel_id number;
1388 l_channel_exist_flag varchar2(1);
1389 l_null_value varchar2(30) := null;
1390 l_row_id varchar2(30);
1391 l_mychannel_id number;
1392 l_expiration_date date;
1393 l_mychannel_obj AMV_MYCHANNEL_PVT.AMV_MY_CHANNEL_OBJ_TYPE;
1394
1395 BEGIN
1396 -- Standard begin of API savepoint
1397 SAVEPOINT Add_PrivateChannel_PVT;
1398 -- Standard call to check for call compatibility.
1399 IF NOT FND_API.Compatible_API_Call (
1400 l_api_version,
1401 p_api_version,
1402 l_api_name,
1403 G_PKG_NAME)
1404 THEN
1405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1406 END IF;
1407 -- Debug Message
1408 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1409 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1410 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1411 FND_MSG_PUB.Add;
1412 END IF;
1413 --Initialize message list if p_init_msg_list is TRUE.
1414 IF FND_API.To_Boolean (p_init_msg_list) THEN
1415 FND_MSG_PUB.initialize;
1416 END IF;
1417 -- Get the current (login) user id.
1418 AMV_UTILITY_PVT.Get_UserInfo(
1419 x_resource_id => l_resource_id,
1420 x_user_id => l_user_id,
1421 x_login_id => l_login_user_id,
1422 x_user_status => l_login_user_status
1423 );
1424 -- check login user
1425 IF (p_check_login_user = FND_API.G_TRUE) THEN
1426 -- Check if user is login and has the required privilege.
1427 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1428 -- User is not login.
1429 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1430 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1431 FND_MSG_PUB.Add;
1432 END IF;
1433 RAISE FND_API.G_EXC_ERROR;
1434 END IF;
1435 END IF;
1436 -- This fix is for executing api in sqlplus mode
1437 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1438 l_login_user_id := g_login_user_id;
1439 l_user_id := g_user_id;
1440 l_resource_id := g_resource_id;
1441 END IF;
1442 -- Initialize API return status to sucess
1443 x_return_status := FND_API.G_RET_STS_SUCCESS;
1447 IF p_channel_record.access_level_type =
1444
1445 --
1446 IF p_channel_record.channel_type = AMV_UTILITY_PVT.G_CONTENT THEN
1448 AMV_UTILITY_PVT.G_PRIVATE THEN
1449 -- create the channel
1450 AMV_CHANNEL_PVT.Add_Channel (
1451 p_api_version => l_api_version,
1452 p_init_msg_list => FND_API.G_FALSE,
1453 p_validation_level => p_validation_level,
1454 x_return_status => x_return_status,
1455 x_msg_count => x_msg_count,
1456 x_msg_data => x_msg_data,
1457 p_check_login_user => FND_API.G_FALSE,
1458 p_channel_record => p_channel_record,
1459 x_channel_id => x_channel_id
1460 );
1461 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1463 END IF;
1464 -- create mychannel object
1465 l_mychannel_obj.my_channel_id := FND_API.G_MISS_NUM;
1466 l_mychannel_obj.channel_type := FND_API.G_MISS_CHAR;
1467 l_mychannel_obj.access_level_type := FND_API.G_MISS_CHAR;
1468 l_mychannel_obj.user_or_group_id :=
1469 p_channel_record.default_approver_user_id;
1470 l_mychannel_obj.user_or_group_type :=AMV_UTILITY_PVT.G_USER;
1471 l_mychannel_obj.subscribing_to_id := x_channel_id;
1472 l_mychannel_obj.subscribing_to_type :=AMV_UTILITY_PVT.G_CHANNEL;
1473 l_mychannel_obj.subscription_reason_type:=
1474 AMV_UTILITY_PVT.G_ENFORCED;
1475 l_mychannel_obj.order_number := FND_API.G_MISS_NUM;
1476 l_mychannel_obj.status := AMV_UTILITY_PVT.G_ACTIVE;
1477 l_mychannel_obj.notify_flag := FND_API.G_FALSE;
1478 l_mychannel_obj.notification_interval_type:= FND_API.G_MISS_CHAR;
1479 /*
1480 l_mychannel_obj := amv_my_channel_obj_type(
1481 FND_API.G_MISS_NUM,
1482 FND_API.G_MISS_CHAR,
1483 FND_API.G_MISS_CHAR,
1484 p_channel_record.default_approver_user_id,
1485 AMV_UTILITY_PVT.G_USER,
1486 x_channel_id,
1487 AMV_UTILITY_PVT.G_CHANNEL,
1488 AMV_UTILITY_PVT.G_ENFORCED,
1489 FND_API.G_MISS_NUM,
1490 AMV_UTILITY_PVT.G_ACTIVE,
1491 FND_API.G_FALSE,
1492 FND_API.G_MISS_CHAR);
1493 */
1494 -- add subscription
1495 AMV_MYCHANNEL_PVT.Add_Subscription
1496 (
1497 p_api_version => l_api_version,
1498 x_return_status => x_return_status,
1499 x_msg_count => x_msg_count,
1500 x_msg_data => x_msg_data,
1501 p_check_login_user => FND_API.G_FALSE,
1502 p_mychannel_obj => l_mychannel_obj,
1503 x_mychannel_id => l_mychannel_id
1504 );
1505 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1507 END IF;
1508 ELSE
1509 -- Invalid channel type passed
1510 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1511 THEN
1512 FND_MESSAGE.Set_Name('AMV','AMV_INVALID_CHAN_TYPE');
1513 FND_MESSAGE.Set_Token('TKN','Content');
1514 FND_MSG_PUB.Add;
1515 END IF;
1516 END IF;
1517 ELSE
1518 -- Invalid channel type passed
1519 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1520 THEN
1521 FND_MESSAGE.Set_Name('AMV','AMV_INVALID_CHAN_ACCS');
1522 FND_MESSAGE.Set_Token('TKN','Protected');
1523 FND_MSG_PUB.Add;
1524 END IF;
1525 END IF;
1526 --
1527
1528 -- Success message
1529 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1530 THEN
1531 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1532 FND_MESSAGE.Set_Token('ROW', l_full_name);
1533 FND_MSG_PUB.Add;
1534 END IF;
1535 --Standard check of commit
1536 IF FND_API.To_Boolean ( p_commit ) THEN
1537 COMMIT WORK;
1538 END IF;
1539 -- Debug Message
1540 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1541 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1542 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1543 FND_MSG_PUB.Add;
1544 END IF;
1545 --Standard call to get message count and if count=1, get the message
1546 FND_MSG_PUB.Count_And_Get (
1547 p_encoded => FND_API.G_FALSE,
1548 p_count => x_msg_count,
1549 p_data => x_msg_data
1550 );
1551 EXCEPTION
1552 WHEN FND_API.G_EXC_ERROR THEN
1553 ROLLBACK TO Add_PrivateChannel_PVT;
1554 x_return_status := FND_API.G_RET_STS_ERROR;
1555 -- Standard call to get message count and if count=1, get the message
1556 FND_MSG_PUB.Count_And_Get (
1557 p_encoded => FND_API.G_FALSE,
1558 p_count => x_msg_count,
1559 p_data => x_msg_data
1560 );
1561 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1562 ROLLBACK TO Add_PrivateChannel_PVT;
1563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1564 -- Standard call to get message count and if count=1, get the message
1565 FND_MSG_PUB.Count_And_Get (
1566 p_encoded => FND_API.G_FALSE,
1567 p_count => x_msg_count,
1568 p_data => x_msg_data
1569 );
1570 WHEN OTHERS THEN
1571 ROLLBACK TO Add_PrivateChannel_PVT;
1572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1573 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1577 -- Standard call to get message count and if count=1, get the message
1574 THEN
1575 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1576 END IF;
1578 FND_MSG_PUB.Count_And_Get (
1579 p_encoded => FND_API.G_FALSE,
1580 p_count => x_msg_count,
1581 p_data => x_msg_data
1582 );
1583 --
1584 END Add_PrivateChannel;
1585 --------------------------------------------------------------------------------
1586 --------------------------------------------------------------------------------
1587 -- Start of comments
1588 -- API name : Add_GroupChannel
1589 -- Type : Private
1590 -- Pre-reqs : None
1591 -- Function : Create a new content protected channel
1592 -- Parameters :
1593 -- IN p_api_version IN NUMBER Required
1594 -- p_init_msg_list IN VARCHAR2 Optional
1595 -- Default = FND_API.G_FALSE
1596 -- p_commit IN VARCHAR2 Optional
1597 -- Default = FND_API.G_FALSE
1598 -- p_validation_level IN NUMBER Optional
1599 -- Default = FND_API.G_VALID_LEVEL_FULL
1600 -- p_channel_record IN AMV_CHANNEL_OBJ_TYPE Required
1601 -- OUT : x_return_status OUT VARCHAR2
1602 -- x_msg_count OUT NUMBER
1603 -- x_msg_data OUT VARCHAR2
1604 -- x_channel_id OUT NUMBER
1605 -- Version : Current version 1.0
1606 -- Previous version 1.0
1607 -- Initial version 1.0
1608 -- Notes :
1609 --
1610 -- End of comments
1611 --
1612 PROCEDURE Add_GroupChannel
1613 ( p_api_version IN NUMBER,
1614 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1615 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1616 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1617 x_return_status OUT NOCOPY VARCHAR2,
1618 x_msg_count OUT NOCOPY NUMBER,
1619 x_msg_data OUT NOCOPY VARCHAR2,
1620 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1621 p_group_id IN NUMBER,
1622 p_channel_record IN AMV_CHANNEL_OBJ_TYPE,
1623 x_channel_id OUT NOCOPY NUMBER
1624 )
1625 IS
1626 l_api_name CONSTANT VARCHAR2(30) := 'Add_GroupChannel';
1627 l_api_version CONSTANT NUMBER := 1.0;
1628 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1629 --
1630 l_resource_id number;
1631 l_user_id number;
1632 l_login_user_id number;
1633 l_login_user_status varchar2(30);
1634 l_Error_Msg varchar2(2000);
1635 l_Error_Token varchar2(80);
1636 l_object_version_number number := 1;
1637 --
1638 l_channel_id number;
1639 l_category_id number;
1640 l_group_name varchar2(60);
1641 l_channel_exist_flag varchar2(1);
1642 l_null_value varchar2(30) := null;
1643 l_row_id varchar2(30);
1644 l_mychannel_id number;
1645 l_expiration_date date;
1646 l_mychannel_obj AMV_MYCHANNEL_PVT.AMV_MY_CHANNEL_OBJ_TYPE;
1647 l_channel_obj AMV_CHANNEL_OBJ_TYPE;
1648
1649 CURSOR Get_GroupCategory IS
1650 select channel_category_id
1651 from amv_c_categories_tl
1652 where channel_category_name = 'AMV_GROUP'
1653 and language = userenv('lang');
1654
1655 CURSOR Get_GroupName IS
1656 select group_name
1657 from JTF_RS_GROUPS_VL
1658 where group_id = p_group_id;
1659
1660 BEGIN
1661 -- Standard begin of API savepoint
1662 SAVEPOINT Add_GroupChannel_PVT;
1663 -- Standard call to check for call compatibility.
1664 IF NOT FND_API.Compatible_API_Call (
1665 l_api_version,
1666 p_api_version,
1667 l_api_name,
1668 G_PKG_NAME)
1669 THEN
1670 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1671 END IF;
1672 -- Debug Message
1673 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1674 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1675 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1676 FND_MSG_PUB.Add;
1677 END IF;
1678 --Initialize message list if p_init_msg_list is TRUE.
1679 IF FND_API.To_Boolean (p_init_msg_list) THEN
1680 FND_MSG_PUB.initialize;
1681 END IF;
1682 -- Get the current (login) user id.
1683 AMV_UTILITY_PVT.Get_UserInfo(
1684 x_resource_id => l_resource_id,
1685 x_user_id => l_user_id,
1686 x_login_id => l_login_user_id,
1687 x_user_status => l_login_user_status
1688 );
1689 -- check login user
1690 IF (p_check_login_user = FND_API.G_TRUE) THEN
1691 -- Check if user is login and has the required privilege.
1692 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1693 -- User is not login.
1694 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1695 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1696 FND_MSG_PUB.Add;
1697 END IF;
1698 RAISE FND_API.G_EXC_ERROR;
1699 END IF;
1700 END IF;
1701 -- This fix is for executing api in sqlplus mode
1702 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1703 l_login_user_id := g_login_user_id;
1704 l_user_id := g_user_id;
1705 l_resource_id := g_resource_id;
1709
1706 END IF;
1707 -- Initialize API return status to sucess
1708 x_return_status := FND_API.G_RET_STS_SUCCESS;
1710 OPEN Get_GroupCategory;
1711 FETCH Get_GroupCategory INTO l_category_id;
1712 CLOSE Get_GroupCategory;
1713 --
1714 OPEN Get_GroupName;
1715 FETCH Get_GroupName INTO l_group_name;
1716 CLOSE Get_GroupName;
1717 --
1718 IF l_group_name is null THEN
1719 -- Invalid channel type passed
1720 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1721 THEN
1722 FND_MESSAGE.Set_Name('AMV','AMV_INVALID_GROUP_ID');
1723 FND_MESSAGE.Set_Token('TKN',p_group_id);
1724 FND_MSG_PUB.Add;
1725 END IF;
1726 RAISE FND_API.G_EXC_ERROR;
1727 END IF;
1728
1729 l_channel_obj := p_channel_record;
1730 l_channel_obj.channel_category_id := l_category_id;
1731 l_channel_obj.channel_name := l_group_name;
1732 l_channel_obj.description := 'Group Channel for '||l_group_name;
1733
1734 --
1735 IF p_channel_record.channel_type = AMV_UTILITY_PVT.G_GROUP THEN
1736 -- create the channel
1737 AMV_CHANNEL_PVT.Add_Channel (
1738 p_api_version => l_api_version,
1739 p_init_msg_list => FND_API.G_FALSE,
1740 p_validation_level => p_validation_level,
1741 x_return_status => x_return_status,
1742 x_msg_count => x_msg_count,
1743 x_msg_data => x_msg_data,
1744 p_check_login_user => FND_API.G_FALSE,
1745 p_channel_record => l_channel_obj,
1746 x_channel_id => x_channel_id
1747 );
1748 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1749 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1750 END IF;
1751 -- create mychannel object
1752 -- NOTE check if p_group_id id null
1753 -- make channel name is the same as group name
1754 l_mychannel_obj.my_channel_id := FND_API.G_MISS_NUM;
1755 l_mychannel_obj.channel_type := FND_API.G_MISS_CHAR;
1756 l_mychannel_obj.access_level_type := FND_API.G_MISS_CHAR;
1757 l_mychannel_obj.user_or_group_id := p_group_id;
1758 l_mychannel_obj.user_or_group_type :=AMV_UTILITY_PVT.G_GROUP;
1759 l_mychannel_obj.subscribing_to_id := x_channel_id;
1760 l_mychannel_obj.subscribing_to_type :=AMV_UTILITY_PVT.G_CHANNEL;
1761 l_mychannel_obj.subscription_reason_type:=
1762 AMV_UTILITY_PVT.G_ENFORCED;
1763 l_mychannel_obj.order_number := FND_API.G_MISS_NUM;
1764 l_mychannel_obj.status := AMV_UTILITY_PVT.G_ACTIVE;
1765 l_mychannel_obj.notify_flag := FND_API.G_FALSE;
1766 l_mychannel_obj.notification_interval_type:= FND_API.G_MISS_CHAR;
1767 /*
1768 l_mychannel_obj := amv_my_channel_obj_type(
1769 FND_API.G_MISS_NUM,
1770 FND_API.G_MISS_CHAR,
1771 FND_API.G_MISS_CHAR,
1772 p_group_id,
1773 AMV_UTILITY_PVT.G_GROUP,
1774 x_channel_id,
1775 AMV_UTILITY_PVT.G_CHANNEL,
1776 AMV_UTILITY_PVT.G_ENFORCED,
1777 FND_API.G_MISS_NUM,
1778 AMV_UTILITY_PVT.G_ACTIVE,
1779 FND_API.G_FALSE,
1780 FND_API.G_MISS_CHAR);
1781 */
1782 -- add subscription
1783 AMV_MYCHANNEL_PVT.Add_Subscription
1784 (
1785 p_api_version => l_api_version,
1786 x_return_status => x_return_status,
1787 x_msg_count => x_msg_count,
1788 x_msg_data => x_msg_data,
1789 p_check_login_user => FND_API.G_FALSE,
1790 p_mychannel_obj => l_mychannel_obj,
1791 x_mychannel_id => l_mychannel_id
1792 );
1793 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1794 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1795 END IF;
1796 ELSE
1797 -- Invalid channel type passed
1798 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1799 THEN
1800 FND_MESSAGE.Set_Name('AMV','AMV_INVALID_CHAN_TYPE');
1801 FND_MESSAGE.Set_Token('TKN','Group');
1802 FND_MSG_PUB.Add;
1803 END IF;
1804 RAISE FND_API.G_EXC_ERROR;
1805 END IF;
1806 --
1807
1808 -- Success message
1809 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1810 THEN
1811 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1812 FND_MESSAGE.Set_Token('ROW', l_full_name);
1813 FND_MSG_PUB.Add;
1814 END IF;
1815 --Standard check of commit
1816 IF FND_API.To_Boolean ( p_commit ) THEN
1817 COMMIT WORK;
1818 END IF;
1819 -- Debug Message
1820 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1821 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1822 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1823 FND_MSG_PUB.Add;
1824 END IF;
1825 --Standard call to get message count and if count=1, get the message
1826 FND_MSG_PUB.Count_And_Get (
1827 p_encoded => FND_API.G_FALSE,
1828 p_count => x_msg_count,
1829 p_data => x_msg_data
1830 );
1831 EXCEPTION
1832 WHEN FND_API.G_EXC_ERROR THEN
1833 ROLLBACK TO Add_GroupChannel_PVT;
1834 x_return_status := FND_API.G_RET_STS_ERROR;
1835 -- Standard call to get message count and if count=1, get the message
1836 FND_MSG_PUB.Count_And_Get (
1837 p_encoded => FND_API.G_FALSE,
1838 p_count => x_msg_count,
1839 p_data => x_msg_data
1840 );
1844 -- Standard call to get message count and if count=1, get the message
1841 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1842 ROLLBACK TO Add_GroupChannel_PVT;
1843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1845 FND_MSG_PUB.Count_And_Get (
1846 p_encoded => FND_API.G_FALSE,
1847 p_count => x_msg_count,
1848 p_data => x_msg_data
1849 );
1850 WHEN OTHERS THEN
1851 ROLLBACK TO Add_GroupChannel_PVT;
1852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1853 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1854 THEN
1855 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1856 END IF;
1857 -- Standard call to get message count and if count=1, get the message
1858 FND_MSG_PUB.Count_And_Get (
1859 p_encoded => FND_API.G_FALSE,
1860 p_count => x_msg_count,
1861 p_data => x_msg_data
1862 );
1863 --
1864 END Add_GroupChannel;
1865 --------------------------------------------------------------------------------
1866 --------------------------------------------------------------------------------
1867 -- Start of comments
1868 -- API name : Delete_Channel
1869 -- Type : Private
1870 -- Pre-reqs : None
1871 -- Function : Delete the content channel given p_channel_id/p_channel_name
1872 -- Parameters :
1873 -- IN p_api_version IN NUMBER Required
1874 -- p_init_msg_list IN VARCHAR2 Optional
1875 -- Default = FND_API.G_FALSE
1876 -- p_commit IN VARCHAR2 Optional
1877 -- Default = FND_API.G_FALSE
1878 -- p_validation_level IN NUMBER Optional
1879 -- Default = FND_API.G_VALID_LEVEL_FULL
1880 -- p_channel_id IN NUMBER Optional
1881 -- the channel id. Default = FND_API.G_MISS_NUM
1882 -- p_channel_name IN VARCHAR2 Optional
1883 -- the channel name. Default = FND_API.G_MISS_CHAR
1884 -- Either pass the channe id (preferred) or channel name
1885 -- to identify the channel.
1886 -- p_category_id IN NUMBER Optional
1887 -- the category id. Default = FND_API.G_MISS_NUM
1888 -- OUT : x_return_status OUT VARCHAR2
1889 -- x_msg_count OUT NUMBER
1890 -- x_msg_data OUT VARCHAR2
1891 -- Version : Current version 1.0
1892 -- Previous version 1.0
1893 -- Initial version 1.0
1894 -- Notes :
1895 --
1896 -- End of comments
1897 --
1898 PROCEDURE Delete_Channel
1899 ( p_api_version IN NUMBER,
1900 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1901 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1902 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1903 x_return_status OUT NOCOPY VARCHAR2,
1904 x_msg_count OUT NOCOPY NUMBER,
1905 x_msg_data OUT NOCOPY VARCHAR2,
1906 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1907 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
1908 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1909 p_category_id IN NUMBER := FND_API.G_MISS_NUM
1910 )
1911 IS
1912 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Channel';
1913 l_api_version CONSTANT NUMBER := 1.0;
1914 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1915 --
1916 l_resource_id number;
1917 l_user_id number;
1918 l_login_user_id number;
1919 l_login_user_status varchar2(30);
1920 l_Error_Msg varchar2(2000);
1921 l_Error_Token varchar2(80);
1922 --
1923 l_channel_id number;
1924 l_channel_exist_flag varchar2(1);
1925 l_delete_channel_flag varchar2(1);
1926 l_setup_result varchar2(1);
1927 --
1928 BEGIN
1929 -- Standard begin of API savepoint
1930 SAVEPOINT Delete_Channel_PVT;
1931 -- Standard call to check for call compatibility.
1932 IF NOT FND_API.Compatible_API_Call (
1933 l_api_version,
1934 p_api_version,
1935 l_api_name,
1936 G_PKG_NAME)
1937 THEN
1938 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1939 END IF;
1940 -- Debug Message
1941 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1942 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1943 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1944 FND_MSG_PUB.Add;
1945 END IF;
1946 --Initialize message list if p_init_msg_list is TRUE.
1947 IF FND_API.To_Boolean (p_init_msg_list) THEN
1948 FND_MSG_PUB.initialize;
1949 END IF;
1950 -- Get the current (login) user id.
1951 AMV_UTILITY_PVT.Get_UserInfo(
1952 x_resource_id => l_resource_id,
1953 x_user_id => l_user_id,
1954 x_login_id => l_login_user_id,
1955 x_user_status => l_login_user_status
1956 );
1957 -- check login user
1958 IF (p_check_login_user = FND_API.G_TRUE) THEN
1959 -- Check if user is login and has the required privilege.
1963 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1960 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1961 -- User is not login.
1962 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1964 FND_MSG_PUB.Add;
1965 END IF;
1966 RAISE FND_API.G_EXC_ERROR;
1967 END IF;
1968 END IF;
1969 -- This fix is for executing api in sqlplus mode
1970 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1971 l_login_user_id := g_login_user_id;
1972 l_user_id := g_user_id;
1973 l_resource_id := g_resource_id;
1974 END IF;
1975 -- Initialize API return status to sucess
1976 x_return_status := FND_API.G_RET_STS_SUCCESS;
1977
1978 -- check if the user has privilege to delete channel
1979 --
1980 Get_ChannelStatus (
1981 x_return_status => x_return_status,
1982 p_channel_id => p_channel_id,
1983 p_channel_name => p_channel_name,
1984 p_category_id => p_category_id,
1985 x_exist_flag => l_channel_exist_flag,
1986 x_channel_id => l_channel_id,
1987 x_error_msg => l_Error_Msg,
1988 x_error_token => l_Error_Token
1989 );
1990 -- remove channel if it exists
1991 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
1992 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1993 THEN
1994 FND_MESSAGE.Set_Name('AMV', 'l_Error_Msg');
1995 FND_MESSAGE.Set_Token('CHN',l_Error_Token);
1996 FND_MSG_PUB.Add;
1997 END IF;
1998 RAISE FND_API.G_EXC_ERROR;
1999 ELSE
2000 --
2001 AMV_USER_PVT.Can_SetupChannel (
2002 p_api_version => l_api_version,
2003 p_init_msg_list => FND_API.G_FALSE,
2004 p_validation_level => p_validation_level,
2005 x_return_status => x_return_status,
2006 x_msg_count => x_msg_count,
2007 x_msg_data => x_msg_data,
2008 p_check_login_user => FND_API.G_FALSE,
2009 p_resource_id => l_resource_id,
2010 p_include_group_flag => FND_API.G_TRUE,
2011 x_result_flag => l_setup_result
2012 );
2013
2014 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2015 IF (l_setup_result = FND_API.G_TRUE) THEN
2016 l_delete_channel_flag := FND_API.G_TRUE;
2017 ELSE
2018 IF (AMV_UTILITY_PVT.Get_DeleteChannelStatus( l_channel_id,
2019 l_resource_id,
2020 AMV_UTILITY_PVT.G_USER) )
2021 THEN
2022 l_delete_channel_flag := FND_API.G_TRUE;
2023 ELSE
2024 -- user does not have privilege to create public channel
2025 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2026 THEN
2027 FND_MESSAGE.Set_Name('AMV', 'AMV_NO_ACCESS_ERROR');
2028 FND_MESSAGE.Set_Token('LEVEL','Channel');
2029 FND_MSG_PUB.Add;
2030 END IF;
2031 RAISE FND_API.G_EXC_ERROR;
2032 END IF;
2033 END IF;
2034 ELSE
2035 -- error while user privilege check in Can_SetupChannel
2036 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2037 THEN
2038 FND_MESSAGE.Set_Name('AMV', 'AMV_SETUP_CHECK_ERROR');
2039 FND_MESSAGE.Set_Token('LEVEL','Channel');
2040 FND_MSG_PUB.Add;
2041 END IF;
2042 RAISE FND_API.G_EXC_ERROR;
2043 END IF;
2044 END IF;
2045 --
2046
2047 -- delete channels if user has privilege
2048 IF l_delete_channel_flag = FND_API.G_TRUE THEN
2049 -- Remove channel from mychannels
2050 DELETE FROM amv_u_my_channels
2051 WHERE subscribing_to_id = l_channel_id
2052 AND subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL;
2053
2054 -- Remove access given to this channel
2055 DELETE FROM amv_u_access
2056 WHERE access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
2057 AND access_to_table_record_id = l_channel_id;
2058
2059 -- Remove channel from authors
2060 DELETE FROM amv_c_authors
2061 WHERE channel_id = l_channel_id;
2062 -- Remove channel from keywords
2063 DELETE FROM amv_c_keywords
2064 WHERE channel_id = l_channel_id;
2065 -- Remove channel from content type
2066 DELETE FROM amv_c_content_types
2067 WHERE channel_id = l_channel_id;
2068 -- Remove channel from perspectives
2069 DELETE FROM amv_c_chl_perspectives
2070 WHERE channel_id = l_channel_id;
2071 -- Remove channel from item types
2072 DELETE FROM amv_c_item_types
2073 WHERE channel_id = l_channel_id;
2074 -- Remove channel from item match
2075 DELETE FROM amv_c_chl_item_match
2076 WHERE channel_id = l_channel_id;
2077
2078 -- Remove channel from channels
2079 AMV_C_CHANNELS_PKG.DELETE_ROW (l_channel_id);
2080
2081 END IF;
2082 --
2083
2084 -- Success message
2085 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2086 THEN
2087 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
2088 FND_MESSAGE.Set_Token('ROW', l_full_name);
2089 FND_MSG_PUB.Add;
2090 END IF;
2091 --Standard check of commit
2092 IF FND_API.To_Boolean ( p_commit ) THEN
2093 COMMIT WORK;
2094 END IF;
2095 -- Debug Message
2096 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2100 END IF;
2097 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2098 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
2099 FND_MSG_PUB.Add;
2101 --Standard call to get message count and if count=1, get the message
2102 FND_MSG_PUB.Count_And_Get (
2103 p_encoded => FND_API.G_FALSE,
2104 p_count => x_msg_count,
2105 p_data => x_msg_data
2106 );
2107 EXCEPTION
2108 WHEN FND_API.G_EXC_ERROR THEN
2109 ROLLBACK TO Delete_Channel_PVT;
2110 x_return_status := FND_API.G_RET_STS_ERROR;
2111 -- Standard call to get message count and if count=1, get the message
2112 FND_MSG_PUB.Count_And_Get (
2113 p_encoded => FND_API.G_FALSE,
2114 p_count => x_msg_count,
2115 p_data => x_msg_data
2116 );
2117 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2118 ROLLBACK TO Delete_Channel_PVT;
2119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2120 -- Standard call to get message count and if count=1, get the message
2121 FND_MSG_PUB.Count_And_Get (
2122 p_encoded => FND_API.G_FALSE,
2123 p_count => x_msg_count,
2124 p_data => x_msg_data
2125 );
2126 WHEN OTHERS THEN
2127 ROLLBACK TO Delete_Channel_PVT;
2128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2129 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2130 THEN
2131 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2132 END IF;
2133 -- Standard call to get message count and if count=1, get the message
2134 FND_MSG_PUB.Count_And_Get (
2135 p_encoded => FND_API.G_FALSE,
2136 p_count => x_msg_count,
2137 p_data => x_msg_data
2138 );
2139 --
2140 END Delete_Channel;
2141 --------------------------------------------------------------------------------
2142 -- Start of comments
2143 -- API name : Update_Channel
2144 -- Type : Private
2145 -- Pre-reqs : None
2146 -- Function : Change the channel given by
2147 -- (channel) id or name in p_channel_record
2148 -- New data are specified in other members of p_channel_record
2149 -- Parameters :
2150 -- IN p_api_version IN NUMBER Required
2151 -- p_init_msg_list IN VARCHAR2 Optional
2152 -- Default = FND_API.G_FALSE
2153 -- p_commit IN VARCHAR2 Optional
2154 -- Default = FND_API.G_FALSE
2155 -- p_validation_level IN NUMBER Optional
2156 -- Default = FND_API.G_VALID_LEVEL_FULL
2157 -- p_channel_record IN AMV_CHANNEL_OBJ_TYPE Required
2158 -- OUT : x_return_status OUT VARCHAR2
2159 -- x_msg_count OUT NUMBER
2160 -- x_msg_data OUT VARCHAR2
2161 -- Version : Current version 1.0
2162 -- Previous version 1.0
2163 -- Initial version 1.0
2164 -- Notes :
2165 --
2166 -- End of comments
2167 --
2168 PROCEDURE Update_Channel
2169 ( p_api_version IN NUMBER,
2170 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2171 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2172 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2173 x_return_status OUT NOCOPY VARCHAR2,
2174 x_msg_count OUT NOCOPY NUMBER,
2175 x_msg_data OUT NOCOPY VARCHAR2,
2176 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2177 p_channel_record IN AMV_CHANNEL_OBJ_TYPE
2178 )
2179 IS
2180 l_api_name CONSTANT VARCHAR2(30) := 'Update_Channel';
2181 l_api_version CONSTANT NUMBER := 1.0;
2182 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2183 --
2184 l_resource_id number;
2185 l_user_id number;
2186 l_login_user_id number;
2187 l_login_user_status varchar2(30);
2188 l_Error_Msg varchar2(2000);
2189 l_Error_Token varchar2(80);
2190 l_object_version_number number;
2191 --
2192 l_channel_id number;
2193 l_channel_id_ex number;
2194 l_channel_exist_flag varchar2(1);
2195 l_update_channel_flag varchar2(1);
2196 l_setup_result varchar2(1);
2197 l_channel_obj AMV_CHANNEL_OBJ_TYPE;
2198
2199 CURSOR Get_ChnVersion_csr IS
2200 select object_version_number
2201 from amv_c_channels_b
2202 where channel_id = l_channel_id;
2203 --
2204 BEGIN
2205 -- Standard begin of API savepoint
2206 SAVEPOINT Update_Channel_PVT;
2207 -- Standard call to check for call compatibility.
2208 IF NOT FND_API.Compatible_API_Call (
2209 l_api_version,
2210 p_api_version,
2211 l_api_name,
2212 G_PKG_NAME)
2213 THEN
2214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2215 END IF;
2216 -- Debug Message
2217 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2218 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2219 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
2220 FND_MSG_PUB.Add;
2221 END IF;
2222 --Initialize message list if p_init_msg_list is TRUE.
2223 IF FND_API.To_Boolean (p_init_msg_list) THEN
2224 FND_MSG_PUB.initialize;
2225 END IF;
2226 -- Get the current (login) user id.
2230 x_login_id => l_login_user_id,
2227 AMV_UTILITY_PVT.Get_UserInfo(
2228 x_resource_id => l_resource_id,
2229 x_user_id => l_user_id,
2231 x_user_status => l_login_user_status
2232 );
2233 -- check login user
2234 IF (p_check_login_user = FND_API.G_TRUE) THEN
2235 -- Check if user is login and has the required privilege.
2236 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2237 -- User is not login.
2238 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2239 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2240 FND_MSG_PUB.Add;
2241 END IF;
2242 RAISE FND_API.G_EXC_ERROR;
2243 END IF;
2244 END IF;
2245 -- This fix is for executing api in sqlplus mode
2246 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2247 l_login_user_id := g_login_user_id;
2248 l_user_id := g_user_id;
2249 l_resource_id := g_resource_id;
2250 END IF;
2251 -- Initialize API return status to sucess
2252 x_return_status := FND_API.G_RET_STS_SUCCESS;
2253
2254 --
2255 -- Check channel id and status for a given channel id or channel name
2256 Get_ChannelStatus (
2257 x_return_status => x_return_status,
2258 p_channel_id => p_channel_record.channel_id,
2259 p_channel_name => p_channel_record.channel_name,
2260 p_category_id => p_channel_record.channel_category_id,
2261 x_exist_flag => l_channel_exist_flag,
2262 x_channel_id => l_channel_id,
2263 x_error_msg => l_Error_Msg,
2264 x_error_token => l_Error_Token
2265 );
2266 -- check if channel exists
2267 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
2268 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2269 THEN
2270 FND_MESSAGE.Set_Name('AMV', 'l_Error_Msg');
2271 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
2272 FND_MSG_PUB.Add;
2273 END IF;
2274 RAISE FND_API.G_EXC_ERROR;
2275 ELSE
2276 -- check if the user has privilege to update channel
2277 --
2278 AMV_USER_PVT.Can_SetupChannel (
2279 p_api_version => l_api_version,
2280 p_init_msg_list => FND_API.G_FALSE,
2281 p_validation_level => p_validation_level,
2282 x_return_status => x_return_status,
2283 x_msg_count => x_msg_count,
2284 x_msg_data => x_msg_data,
2285 p_check_login_user => FND_API.G_FALSE,
2286 p_resource_id => l_resource_id,
2287 p_include_group_flag => FND_API.G_TRUE,
2288 x_result_flag => l_setup_result
2289 );
2290
2291 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2292 IF (l_setup_result = FND_API.G_TRUE) THEN
2293 l_update_channel_flag := FND_API.G_TRUE;
2294 ELSE
2295 IF (AMV_UTILITY_PVT.Get_UpdateChannelStatus(
2296 l_channel_id,
2297 l_resource_id,
2298 AMV_UTILITY_PVT.G_USER) )
2299 THEN
2300 l_update_channel_flag := FND_API.G_TRUE;
2301 ELSE
2302 -- user does not have privilege to create public channel
2303 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2304 THEN
2305 FND_MESSAGE.Set_Name('AMV', 'AMV_NO_ACCESS_ERROR');
2306 FND_MESSAGE.Set_Token('LEVEL','Channel');
2307 FND_MSG_PUB.Add;
2308 END IF;
2309 RAISE FND_API.G_EXC_ERROR;
2310 END IF;
2311 END IF;
2312 ELSE
2313 -- error while user privilege check in Can_SetupChannel
2314 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2315 THEN
2316 FND_MESSAGE.Set_Name('AMV', 'AMV_SETUP_CHECK_ERROR');
2317 FND_MESSAGE.Set_Token('LEVEL','Channel');
2318 FND_MSG_PUB.Add;
2319 END IF;
2320 RAISE FND_API.G_EXC_ERROR;
2321 END IF;
2322 END IF;
2323 --
2324
2325 --
2326 IF l_update_channel_flag = FND_API.G_TRUE THEN
2327 -- Get the current channel record
2328 Get_ChannelRecord(l_channel_id, l_channel_obj);
2329
2330 -- Replace with new entries
2331 IF (p_channel_record.channel_type <> FND_API.G_MISS_CHAR) THEN
2332 l_channel_obj.channel_type := p_channel_record.channel_type;
2333 END IF;
2334 -- NOTE
2335 IF (p_channel_record.channel_category_id <> FND_API.G_MISS_NUM) THEN
2336 l_channel_obj.channel_category_id :=
2337 p_channel_record.channel_category_id;
2338 END IF;
2339 IF (p_channel_record.status <> FND_API.G_MISS_CHAR) THEN
2340 l_channel_obj.status := p_channel_record.status;
2341 END IF;
2342 IF (p_channel_record.owner_user_id <> FND_API.G_MISS_NUM) THEN
2343 l_channel_obj.owner_user_id := p_channel_record.owner_user_id;
2344 END IF;
2345 IF (p_channel_record.channel_name <> FND_API.G_MISS_CHAR) THEN
2346 l_channel_obj.channel_name :=
2347 p_channel_record.channel_name;
2348 END IF;
2349 IF (p_channel_record.default_approver_user_id <>FND_API.G_MISS_NUM) THEN
2350 l_channel_obj.default_approver_user_id :=
2351 p_channel_record.default_approver_user_id;
2352 END IF;
2353 IF (p_channel_record.effective_start_date <> FND_API.G_MISS_DATE) THEN
2354 l_channel_obj.effective_start_date :=
2355 p_channel_record.effective_start_date;
2356 END IF;
2357 IF (p_channel_record.access_level_type <> FND_API.G_MISS_CHAR) THEN
2361 IF (p_channel_record.pub_need_approval_flag <>
2358 l_channel_obj.access_level_type :=
2359 p_channel_record.access_level_type;
2360 END IF;
2362 FND_API.G_MISS_CHAR) THEN
2363 l_channel_obj.pub_need_approval_flag :=
2364 p_channel_record.pub_need_approval_flag;
2365
2366 END IF;
2367 IF (p_channel_record.match_on_all_criteria_flag <>
2368 FND_API.G_MISS_CHAR) THEN
2369 l_channel_obj.match_on_all_criteria_flag :=
2370 p_channel_record.match_on_all_criteria_flag;
2371 END IF;
2372 IF (p_channel_record.match_on_keyword_flag <> FND_API.G_MISS_CHAR) THEN
2373 l_channel_obj.match_on_keyword_flag :=
2374 p_channel_record.match_on_keyword_flag;
2375 END IF;
2376 IF (p_channel_record.match_on_author_flag <> FND_API.G_MISS_CHAR) THEN
2377 l_channel_obj.match_on_author_flag :=
2378 p_channel_record.match_on_author_flag;
2379 END IF;
2380 IF (p_channel_record.match_on_perspective_flag <>
2381 FND_API.G_MISS_CHAR) THEN
2382 l_channel_obj.match_on_perspective_flag :=
2383 p_channel_record.match_on_perspective_flag;
2384 END IF;
2385 IF (p_channel_record.match_on_content_type_flag <>
2386 FND_API.G_MISS_CHAR) THEN
2387 l_channel_obj.match_on_content_type_flag :=
2388 p_channel_record.match_on_content_type_flag;
2389 END IF;
2390 IF (p_channel_record.match_on_item_type_flag <>
2391 FND_API.G_MISS_CHAR) THEN
2392 l_channel_obj.match_on_item_type_flag :=
2393 p_channel_record.match_on_item_type_flag;
2394 END IF;
2395 IF (p_channel_record.match_on_time_flag <> FND_API.G_MISS_CHAR) THEN
2396 l_channel_obj.match_on_time_flag :=
2397 p_channel_record.match_on_time_flag;
2398 END IF;
2399 IF (p_channel_record.expiration_date <> FND_API.G_MISS_DATE) THEN
2400 l_channel_obj.expiration_date :=
2401 p_channel_record.expiration_date;
2402 END IF;
2403 IF (p_channel_record.application_id <> FND_API.G_MISS_NUM) THEN
2404 l_channel_obj.application_id := p_channel_record.application_id;
2405 END IF;
2406 IF (p_channel_record.external_access_flag <> FND_API.G_MISS_CHAR) THEN
2407 l_channel_obj.external_access_flag :=
2408 p_channel_record.external_access_flag;
2409 END IF;
2410 IF (p_channel_record.item_match_count <> FND_API.G_MISS_NUM) THEN
2411 l_channel_obj.item_match_count :=
2412 p_channel_record.item_match_count;
2413 END IF;
2414 IF (p_channel_record.last_match_time <> FND_API.G_MISS_DATE) THEN
2415 l_channel_obj.last_match_time :=
2416 p_channel_record.last_match_time;
2417 END IF;
2418 IF (p_channel_record.notification_interval_type <>
2419 FND_API.G_MISS_CHAR) THEN
2420 l_channel_obj.notification_interval_type :=
2421 p_channel_record.notification_interval_type;
2422 END IF;
2423 IF (p_channel_record.last_notification_time <> FND_API.G_MISS_DATE) THEN
2424 l_channel_obj.last_notification_time :=
2425 p_channel_record.last_notification_time;
2426 END IF;
2427 IF (p_channel_record.attribute_category <> FND_API.G_MISS_CHAR) THEN
2428 l_channel_obj.attribute_category :=p_channel_record.attribute_category;
2429 END IF;
2430 IF (p_channel_record.attribute1 <> FND_API.G_MISS_CHAR) THEN
2431 l_channel_obj.attribute1 := p_channel_record.attribute1;
2432 END IF;
2433 IF (p_channel_record.attribute2 <> FND_API.G_MISS_CHAR) THEN
2434 l_channel_obj.attribute2 := p_channel_record.attribute2;
2435 END IF;
2436 IF (p_channel_record.attribute3 <> FND_API.G_MISS_CHAR) THEN
2437 l_channel_obj.attribute3 := p_channel_record.attribute3;
2438 END IF;
2439 IF (p_channel_record.attribute4 <> FND_API.G_MISS_CHAR) THEN
2440 l_channel_obj.attribute4 := p_channel_record.attribute4;
2441 END IF;
2442 IF (p_channel_record.attribute5 <> FND_API.G_MISS_CHAR) THEN
2443 l_channel_obj.attribute5 := p_channel_record.attribute5;
2444 END IF;
2445 IF (p_channel_record.attribute6 <> FND_API.G_MISS_CHAR) THEN
2446 l_channel_obj.attribute6 := p_channel_record.attribute6;
2447 END IF;
2448 IF (p_channel_record.attribute7 <> FND_API.G_MISS_CHAR) THEN
2449 l_channel_obj.attribute7 := p_channel_record.attribute7;
2450 END IF;
2451 IF (p_channel_record.attribute8 <> FND_API.G_MISS_CHAR) THEN
2452 l_channel_obj.attribute8 := p_channel_record.attribute8;
2453 END IF;
2454 IF (p_channel_record.attribute9 <> FND_API.G_MISS_CHAR) THEN
2455 l_channel_obj.attribute9 := p_channel_record.attribute9;
2456 END IF;
2457 IF (p_channel_record.attribute10 <> FND_API.G_MISS_CHAR) THEN
2458 l_channel_obj.attribute10 := p_channel_record.attribute10;
2459 END IF;
2460 IF (p_channel_record.attribute11 <> FND_API.G_MISS_CHAR) THEN
2461 l_channel_obj.attribute11 := p_channel_record.attribute11;
2462 END IF;
2463 IF (p_channel_record.attribute12 <> FND_API.G_MISS_CHAR) THEN
2464 l_channel_obj.attribute12 := p_channel_record.attribute12;
2465 END IF;
2466 IF (p_channel_record.attribute13 <> FND_API.G_MISS_CHAR) THEN
2467 l_channel_obj.attribute13 := p_channel_record.attribute13;
2468 END IF;
2469 IF (p_channel_record.attribute14 <> FND_API.G_MISS_CHAR) THEN
2470 l_channel_obj.attribute14 := p_channel_record.attribute14;
2471 END IF;
2472 IF (p_channel_record.attribute15 <> FND_API.G_MISS_CHAR) THEN
2473 l_channel_obj.attribute15 := p_channel_record.attribute15;
2474 END IF;
2478
2475 IF (p_channel_record.description <> FND_API.G_MISS_CHAR) THEN
2476 l_channel_obj.description := p_channel_record.description;
2477 END IF;
2479 IF (p_channel_record.channel_name <> FND_API.G_MISS_CHAR) THEN
2480 IF (p_channel_record.channel_name <> l_channel_obj.channel_name) THEN
2481 -- Check if channel name exists
2482 Get_ChannelStatus (
2483 x_return_status => x_return_status,
2484 p_channel_name => p_channel_record.channel_name,
2485 p_category_id => l_channel_obj.channel_category_id,
2486 x_exist_flag => l_channel_exist_flag,
2487 x_channel_id => l_channel_id_ex,
2488 x_error_msg => l_Error_Msg,
2489 x_error_token => l_Error_Token
2490 );
2491 -- Update channel name if different
2492 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2493 IF l_channel_exist_flag = FND_API.G_FALSE THEN
2494 -- set channel name to new value only if it does not exist
2495 l_channel_obj.channel_name := p_channel_record.channel_name;
2496 ELSE
2497 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2498 THEN
2499 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
2500 FND_MESSAGE.Set_Token('TKN', l_Error_Token);
2501 FND_MSG_PUB.Add;
2502 END IF;
2503 RAISE FND_API.G_EXC_ERROR;
2504 END IF;
2505 ELSE
2506 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2507 THEN
2508 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
2509 FND_MESSAGE.Set_Token('TKN', l_Error_Token);
2510 FND_MSG_PUB.Add;
2511 END IF;
2512 RAISE FND_API.G_EXC_ERROR;
2513 END IF;
2514 END IF;
2515 END IF;
2516
2517 OPEN Get_ChnVersion_csr;
2518 FETCH Get_ChnVersion_csr INTO l_object_version_number;
2519 CLOSE Get_ChnVersion_csr;
2520
2521 IF l_channel_obj.object_version_number = l_object_version_number THEN
2522 -- Update channel record
2523 BEGIN
2524 AMV_C_CHANNELS_PKG.UPDATE_ROW(
2525 x_channel_id => l_channel_id,
2526 x_object_version_number => l_object_version_number + 1,
2527 x_channel_type => l_channel_obj.channel_type,
2528 x_channel_category_id => l_channel_obj.channel_category_id,
2529 x_status => l_channel_obj.status,
2530 x_owner_user_id => l_channel_obj.owner_user_id,
2531 x_default_approver_user_id =>l_channel_obj.default_approver_user_id,
2532 x_effective_start_date => l_channel_obj.effective_start_date,
2533 x_expiration_date => l_channel_obj.expiration_date,
2534 x_access_level_type => l_channel_obj.access_level_type,
2535 x_pub_need_approval_flag => l_channel_obj.pub_need_approval_flag,
2536 x_sub_need_approval_flag => l_channel_obj.sub_need_approval_flag,
2537 x_match_on_all_criteria_flag=>l_channel_obj.match_on_all_criteria_flag,
2538 x_match_on_keyword_flag => l_channel_obj.match_on_keyword_flag,
2539 x_match_on_author_flag => l_channel_obj.match_on_author_flag,
2540 x_match_on_perspective_flag => l_channel_obj.match_on_perspective_flag,
2541 x_match_on_item_type_flag => l_channel_obj.match_on_item_type_flag,
2542 x_match_on_content_type_flag=>l_channel_obj.match_on_content_type_flag,
2543 x_match_on_time_flag => l_channel_obj.match_on_time_flag,
2544 x_application_id => l_channel_obj.application_id,
2545 x_external_access_flag => l_channel_obj.external_access_flag,
2546 x_item_match_count => l_channel_obj.item_match_count,
2547 x_last_match_time => l_channel_obj.last_match_time,
2548 x_notification_interval_type=>l_channel_obj.notification_interval_type,
2549 x_last_notification_time => l_channel_obj.last_notification_time,
2550 x_attribute_category => l_channel_obj.attribute_category,
2551 x_attribute1 => l_channel_obj.attribute1,
2552 x_attribute2 => l_channel_obj.attribute2,
2553 x_attribute3 => l_channel_obj.attribute3,
2554 x_attribute4 => l_channel_obj.attribute4,
2555 x_attribute5 => l_channel_obj.attribute5,
2556 x_attribute6 => l_channel_obj.attribute6,
2557 x_attribute7 => l_channel_obj.attribute7,
2558 x_attribute8 => l_channel_obj.attribute8,
2559 x_attribute9 => l_channel_obj.attribute9,
2560 x_attribute10 => l_channel_obj.attribute10,
2561 x_attribute11 => l_channel_obj.attribute11,
2562 x_attribute12 => l_channel_obj.attribute12,
2563 x_attribute13 => l_channel_obj.attribute13,
2564 x_attribute14 => l_channel_obj.attribute14,
2565 x_attribute15 => l_channel_obj.attribute15,
2566 x_last_update_date => sysdate,
2567 x_last_updated_by => l_user_id,
2568 x_last_update_login => l_login_user_id,
2569 x_channel_name => l_channel_obj.channel_name,
2570 x_description => l_channel_obj.description
2571 );
2572 EXCEPTION
2573 WHEN OTHERS THEN
2574 --will log the error
2575 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2576 THEN
2577 FND_MESSAGE.Set_Name('AMV', 'AMV_TABLE_HANDLER_ERROR');
2578 FND_MESSAGE.Set_Token('ACTION', 'Updating');
2579 FND_MESSAGE.Set_Token('TABLE', 'Channel');
2580 FND_MSG_PUB.Add;
2581 END IF;
2582 RAISE FND_API.G_EXC_ERROR;
2583 END;
2584 -- invoke macthing engine for populating items into channel
2585 AMV_MATCH_PVT.Request_ChannelMatch
2589 x_msg_count => x_msg_count,
2586 (
2587 p_api_version => l_api_version,
2588 x_return_status => x_return_status,
2590 x_msg_data => x_msg_data,
2591 p_check_login_user => FND_API.G_FALSE,
2592 p_channel_id => l_channel_id
2593 );
2594 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2596 END IF;
2597 ELSE
2598 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2599 THEN
2600 FND_MESSAGE.Set_Name('AMV', 'AMV_CHN_VERSION_CHANGE');
2601 FND_MSG_PUB.Add;
2602 END IF;
2603 RAISE FND_API.G_EXC_ERROR;
2604 END IF;
2605 END IF;
2606 --
2607
2608 -- Success message
2609 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2610 THEN
2611 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
2612 FND_MESSAGE.Set_Token('ROW', l_full_name);
2613 FND_MSG_PUB.Add;
2614 END IF;
2615 --Standard check of commit
2616 IF FND_API.To_Boolean ( p_commit ) THEN
2617 COMMIT WORK;
2618 END IF;
2619 -- Debug Message
2620 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2621 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2622 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
2623 FND_MSG_PUB.Add;
2624 END IF;
2625 --Standard call to get message count and if count=1, get the message
2626 FND_MSG_PUB.Count_And_Get (
2627 p_encoded => FND_API.G_FALSE,
2628 p_count => x_msg_count,
2629 p_data => x_msg_data
2630 );
2631 EXCEPTION
2632 WHEN FND_API.G_EXC_ERROR THEN
2633 ROLLBACK TO Update_Channel_PVT;
2634 x_return_status := FND_API.G_RET_STS_ERROR;
2635 -- Standard call to get message count and if count=1, get the message
2636 FND_MSG_PUB.Count_And_Get (
2637 p_encoded => FND_API.G_FALSE,
2638 p_count => x_msg_count,
2639 p_data => x_msg_data
2640 );
2641 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2642 ROLLBACK TO Update_Channel_PVT;
2643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2644 -- Standard call to get message count and if count=1, get the message
2645 FND_MSG_PUB.Count_And_Get (
2646 p_encoded => FND_API.G_FALSE,
2647 p_count => x_msg_count,
2648 p_data => x_msg_data
2649 );
2650 WHEN OTHERS THEN
2651 ROLLBACK TO Update_Channel_PVT;
2652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2653 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2654 THEN
2655 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2656 END IF;
2657 -- Standard call to get message count and if count=1, get the message
2658 FND_MSG_PUB.Count_And_Get (
2659 p_encoded => FND_API.G_FALSE,
2660 p_count => x_msg_count,
2661 p_data => x_msg_data
2662 );
2663 --
2664 END Update_Channel;
2665 --------------------------------------------------------------------------------
2666 -- Start of comments
2667 -- API name : Get_Channel
2668 -- Type : Private
2669 -- Pre-reqs : None
2670 -- Function : Get the channel attributes of channel given by
2671 -- channel id (preferred) or channel name.
2672 -- Parameters :
2673 -- IN p_api_version IN NUMBER Required
2674 -- p_init_msg_list IN VARCHAR2 Optional
2675 -- Default = FND_API.G_FALSE
2676 -- p_validation_level IN NUMBER Optional
2677 -- Default = FND_API.G_VALID_LEVEL_FULL
2678 -- p_channel_id IN NUMBER Optional
2679 -- the channel id. Default = FND_API.G_MISS_NUM
2680 -- p_channel_name IN VARCHAR2 Optional
2681 -- the channel name. Default = FND_API.G_MISS_CHAR
2682 -- Either pass the channe id (preferred) or channel name
2683 -- to identify the channel.
2684 -- p_category_id IN NUMBER Optional
2685 -- the category id. Default = FND_API.G_MISS_NUM
2686 -- OUT : x_return_status OUT VARCHAR2
2687 -- x_msg_count OUT NUMBER
2688 -- x_msg_data OUT VARCHAR2
2689 -- x_channel_record OUT AMV_CHANNEL_OBJ_TYPE
2690 -- Version : Current version 1.0
2691 -- Previous version 1.0
2692 -- Initial version 1.0
2693 -- Notes :
2694 --
2695 -- End of comments
2696 --
2697 PROCEDURE Get_Channel
2698 ( p_api_version IN NUMBER,
2699 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2700 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2701 x_return_status OUT NOCOPY VARCHAR2,
2702 x_msg_count OUT NOCOPY NUMBER,
2703 x_msg_data OUT NOCOPY VARCHAR2,
2704 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2705 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
2706 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
2710 IS
2707 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
2708 x_channel_record OUT NOCOPY AMV_CHANNEL_OBJ_TYPE
2709 )
2711 l_api_name CONSTANT VARCHAR2(30) := 'Get_Channel';
2712 l_api_version CONSTANT NUMBER := 1.0;
2713 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2714 --
2715 l_resource_id number;
2716 l_user_id number;
2717 l_login_user_id number;
2718 l_login_user_status varchar2(30);
2719 l_Error_Msg varchar2(2000);
2720 l_Error_Token varchar2(80);
2721 --
2722 l_channel_id number;
2723 l_channel_exist_flag varchar2(1);
2724 --
2725 BEGIN
2726 -- Standard begin of API savepoint
2727 SAVEPOINT Get_Channel_PVT;
2728 -- Standard call to check for call compatibility.
2729 IF NOT FND_API.Compatible_API_Call (
2730 l_api_version,
2731 p_api_version,
2732 l_api_name,
2733 G_PKG_NAME)
2734 THEN
2735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2736 END IF;
2737 -- Debug Message
2738 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2739 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2740 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
2741 FND_MSG_PUB.Add;
2742 END IF;
2743 --Initialize message list if p_init_msg_list is TRUE.
2744 IF FND_API.To_Boolean (p_init_msg_list) THEN
2745 FND_MSG_PUB.initialize;
2746 END IF;
2747 -- Get the current (login) user id.
2748 AMV_UTILITY_PVT.Get_UserInfo(
2749 x_resource_id => l_resource_id,
2750 x_user_id => l_user_id,
2751 x_login_id => l_login_user_id,
2752 x_user_status => l_login_user_status
2753 );
2754 -- check login user
2755 IF (p_check_login_user = FND_API.G_TRUE) THEN
2756 -- Check if user is login and has the required privilege.
2757 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2758 -- User is not login.
2759 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2760 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2761 FND_MSG_PUB.Add;
2762 END IF;
2763 RAISE FND_API.G_EXC_ERROR;
2764 END IF;
2765 END IF;
2766 -- This fix is for executing api in sqlplus mode
2767 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2768 l_login_user_id := g_login_user_id;
2769 l_user_id := g_user_id;
2770 l_resource_id := g_resource_id;
2771 END IF;
2772 -- Initialize API return status to sucess
2773 x_return_status := FND_API.G_RET_STS_SUCCESS;
2774
2775 -- Check and return channel id for a given channel id or channel name
2776 Get_ChannelStatus (
2777 x_return_status => x_return_status,
2778 p_channel_id => p_channel_id,
2779 p_channel_name => p_channel_name,
2780 p_category_id => p_category_id,
2781 x_exist_flag => l_channel_exist_flag,
2782 x_channel_id => l_channel_id,
2783 x_error_msg => l_Error_Msg,
2784 x_error_token => l_Error_Token
2785 );
2786 -- check if channel exists
2787 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
2788 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2789 THEN
2790 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
2791 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
2792 FND_MSG_PUB.Add;
2793 END IF;
2794 RAISE FND_API.G_EXC_ERROR;
2795 ELSE
2796 Get_ChannelRecord(l_channel_id, x_channel_record);
2797 END IF;
2798 --
2799
2800 -- Debug Message
2801 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2802 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2803 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
2804 FND_MSG_PUB.Add;
2805 END IF;
2806 --Standard call to get message count and if count=1, get the message
2807 FND_MSG_PUB.Count_And_Get (
2808 p_encoded => FND_API.G_FALSE,
2809 p_count => x_msg_count,
2810 p_data => x_msg_data
2811 );
2812 EXCEPTION
2813 WHEN FND_API.G_EXC_ERROR THEN
2814 ROLLBACK TO Get_Channel_PVT;
2815 x_return_status := FND_API.G_RET_STS_ERROR;
2816 -- Standard call to get message count and if count=1, get the message
2817 FND_MSG_PUB.Count_And_Get (
2818 p_encoded => FND_API.G_FALSE,
2819 p_count => x_msg_count,
2820 p_data => x_msg_data
2821 );
2822 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2823 ROLLBACK TO Get_Channel_PVT;
2824 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2825 -- Standard call to get message count and if count=1, get the message
2826 FND_MSG_PUB.Count_And_Get (
2827 p_encoded => FND_API.G_FALSE,
2828 p_count => x_msg_count,
2829 p_data => x_msg_data
2830 );
2831 WHEN OTHERS THEN
2832 ROLLBACK TO Get_Channel_PVT;
2833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2834 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2835 THEN
2836 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2837 END IF;
2838 -- Standard call to get message count and if count=1, get the message
2842 p_data => x_msg_data
2839 FND_MSG_PUB.Count_And_Get (
2840 p_encoded => FND_API.G_FALSE,
2841 p_count => x_msg_count,
2843 );
2844 --
2845 END Get_Channel;
2846 --------------------------------------------------------------------------------
2847 -- Start of comments
2848 -- API name : Set_ChannelContentTypes
2849 -- Type : Private
2850 -- Pre-reqs : None
2851 -- Function : Change content categories of the content channel.
2852 -- Parameters :
2853 -- IN p_api_version IN NUMBER Required
2854 -- p_init_msg_list IN VARCHAR2 Optional
2855 -- Default = FND_API.G_FALSE
2856 -- p_commit IN VARCHAR2 Optional
2857 -- Default = FND_API.G_FALSE
2858 -- p_validation_level NUMBER Optional
2859 -- Default = FND_API.G_VALID_LEVEL_FULL
2860 -- p_channel_id IN NUMBER Optional
2861 -- the channel id. Default = FND_API.G_MISS_NUM
2862 -- p_channel_name IN VARCHAR2 Optional
2863 -- the channel name. Default = FND_API.G_MISS_CHAR
2864 -- Either pass the channe id (preferred) or channel name
2865 -- to identify the channel.
2866 -- p_category_id IN NUMBER Optional
2867 -- the category id. Default = FND_API.G_MISS_NUM
2868 -- p_content_type_id_array IN AMV_NUMBER_VARRAY_TYPE
2869 -- OUT : x_return_status OUT VARCHAR2
2870 -- x_msg_count OUT NUMBER
2871 -- x_msg_data OUT VARCHAR2
2872 -- Version : Current version 1.0
2873 -- Previous version 1.0
2874 -- Initial version 1.0
2875 -- Notes :
2876 --
2877 -- End of comments
2878 --
2879 PROCEDURE Set_ChannelContentTypes
2880 ( p_api_version IN NUMBER,
2881 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2882 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2883 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2884 x_return_status OUT NOCOPY VARCHAR2,
2885 x_msg_count OUT NOCOPY NUMBER,
2886 x_msg_data OUT NOCOPY VARCHAR2,
2887 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2888 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
2889 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
2890 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
2891 p_content_type_id_array IN AMV_NUMBER_VARRAY_TYPE
2892 )
2893 IS
2894 l_api_name CONSTANT VARCHAR2(30) := 'Set_ChannelContentTypes';
2895 l_api_version CONSTANT NUMBER := 1.0;
2896 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2897 --
2898 l_resource_id number;
2899 l_user_id number;
2900 l_login_user_id number;
2901 l_login_user_status varchar2(30);
2902 l_Error_Msg varchar2(2000);
2903 l_Error_Token varchar2(80);
2904 l_object_version_number number := 1;
2905 --
2906 l_channel_id number;
2907 l_channel_exist_flag varchar2(1);
2908 l_setup_result varchar2(1);
2909 l_update_channel_flag Varchar2(1);
2910 l_chl_content_type_id number;
2911 --
2912 CURSOR C_ChanContentType_Seq IS
2913 select amv_c_content_types_s.nextval
2914 from dual;
2915 BEGIN
2916 -- Standard begin of API savepoint
2917 SAVEPOINT Set_ChannelContent_PVT;
2918 -- Standard call to check for call compatibility.
2919 IF NOT FND_API.Compatible_API_Call (
2920 l_api_version,
2921 p_api_version,
2922 l_api_name,
2923 G_PKG_NAME)
2924 THEN
2925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2926 END IF;
2927 -- Debug Message
2928 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2929 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2930 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
2931 FND_MSG_PUB.Add;
2932 END IF;
2933 --Initialize message list if p_init_msg_list is TRUE.
2934 IF FND_API.To_Boolean (p_init_msg_list) THEN
2935 FND_MSG_PUB.initialize;
2936 END IF;
2937 -- Get the current (login) user id.
2938 AMV_UTILITY_PVT.Get_UserInfo(
2939 x_resource_id => l_resource_id,
2940 x_user_id => l_user_id,
2941 x_login_id => l_login_user_id,
2942 x_user_status => l_login_user_status
2943 );
2944 -- check login user
2945 IF (p_check_login_user = FND_API.G_TRUE) THEN
2946 -- Check if user is login and has the required privilege.
2947 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2948 -- User is not login.
2949 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2950 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2951 FND_MSG_PUB.Add;
2952 END IF;
2953 RAISE FND_API.G_EXC_ERROR;
2954 END IF;
2955 END IF;
2956 -- This fix is for executing api in sqlplus mode
2957 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2961 END IF;
2958 l_login_user_id := g_login_user_id;
2959 l_user_id := g_user_id;
2960 l_resource_id := g_resource_id;
2962 -- Initialize API return status to sucess
2963 x_return_status := FND_API.G_RET_STS_SUCCESS;
2964
2965 -- Check channel id and status for a given channel id or channel name
2966 Get_ChannelStatus (
2967 x_return_status => x_return_status,
2968 p_channel_id => p_channel_id,
2969 p_channel_name => p_channel_name,
2970 p_category_id => p_category_id,
2971 x_exist_flag => l_channel_exist_flag,
2972 x_channel_id => l_channel_id,
2973 x_error_msg => l_Error_Msg,
2974 x_error_token => l_Error_Token
2975 );
2976 -- change channel categories if the channel exists
2977 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
2978 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2979 THEN
2980 FND_MESSAGE.Set_Name('AMV', 'l_Error_Msg');
2981 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
2982 FND_MSG_PUB.Add;
2983 END IF;
2984 RAISE FND_API.G_EXC_ERROR;
2985 ELSE
2986 --
2987 AMV_USER_PVT.Can_SetupChannel (
2988 p_api_version => l_api_version,
2989 p_init_msg_list => FND_API.G_FALSE,
2990 p_validation_level => p_validation_level,
2991 x_return_status => x_return_status,
2992 x_msg_count => x_msg_count,
2993 x_msg_data => x_msg_data,
2994 p_check_login_user => FND_API.G_FALSE,
2995 p_resource_id => l_resource_id,
2996 p_include_group_flag => FND_API.G_TRUE,
2997 x_result_flag => l_setup_result
2998 );
2999
3000 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3001 IF (l_setup_result = FND_API.G_TRUE) THEN
3002 l_update_channel_flag := FND_API.G_TRUE;
3003 ELSE
3004 IF (AMV_UTILITY_PVT.Get_UpdateChannelStatus(
3005 l_channel_id,
3006 l_resource_id,
3007 AMV_UTILITY_PVT.G_USER) )
3008 THEN
3009 l_update_channel_flag := FND_API.G_TRUE;
3010 ELSE
3011 -- user does not have privilege to create public channel
3012 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3013 THEN
3014 FND_MESSAGE.Set_Name('AMV', 'AMV_NO_ACCESS_ERROR');
3015 FND_MESSAGE.Set_Token('LEVEL','Channel');
3016 FND_MSG_PUB.Add;
3017 END IF;
3018 RAISE FND_API.G_EXC_ERROR;
3019 END IF;
3020 END IF;
3021 ELSE
3022 -- error while user privilege check in Can_SetupChannel
3023 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3024 THEN
3025 FND_MESSAGE.Set_Name('AMV', 'AMV_SETUP_CHECK_ERROR');
3026 FND_MESSAGE.Set_Token('LEVEL','Channel');
3027 FND_MSG_PUB.Add;
3028 END IF;
3029 RAISE FND_API.G_EXC_ERROR;
3030 END IF;
3031 END IF;
3032 --
3033
3034 -- update channels if user has privilege
3035 IF l_update_channel_flag = FND_API.G_TRUE THEN
3036
3037 -- remove old categories for the channel
3038 DELETE FROM amv_c_content_types
3039 WHERE channel_id = l_channel_id;
3040
3041 -- set match on flag in channel table
3042 IF p_content_type_id_array.count > 0 THEN
3043 UPDATE amv_c_channels_b
3044 set match_on_content_type_flag = FND_API.G_TRUE
3045 where channel_id = l_channel_id;
3046 ELSE
3047 UPDATE amv_c_channels_b
3048 set match_on_content_type_flag = FND_API.G_FALSE
3049 where channel_id = l_channel_id;
3050 END IF;
3051
3052 -- set match on all criteria flag
3053 IF Get_MatchOnStatus(l_channel_id) THEN
3054 UPDATE amv_c_channels_b
3055 set match_on_all_criteria_flag = FND_API.G_TRUE
3056 where channel_id = l_channel_id;
3057 ELSE
3058 UPDATE amv_c_channels_b
3059 set match_on_all_criteria_flag = FND_API.G_FALSE
3060 where channel_id = l_channel_id;
3061 END IF;
3062
3063 -- insert the new category id's for the channel
3064 FOR i in 1..p_content_type_id_array.count LOOP
3065
3066 OPEN C_ChanContentType_Seq;
3067 FETCH C_ChanContentType_Seq into l_chl_content_type_id;
3068 CLOSE C_ChanContentType_Seq;
3069
3070 INSERT INTO amv_c_content_types
3071 (
3072 channel_content_type_id,
3073 object_version_number,
3074 last_update_date,
3075 last_updated_by,
3076 creation_date,
3077 created_by,
3078 last_update_login,
3079 channel_id,
3080 content_type_id
3081 )
3082 VALUES (
3083 l_chl_content_type_id,
3084 l_object_version_number,
3085 sysdate,
3086 l_user_id,
3087 sysdate,
3088 l_user_id,
3089 l_login_user_id,
3090 l_channel_id,
3091 p_content_type_id_array(i)
3092 );
3093 END LOOP;
3094 END IF;
3095 --
3096
3097 -- Success message
3098 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3099 THEN
3100 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
3101 FND_MESSAGE.Set_Token('ROW', l_full_name);
3102 FND_MSG_PUB.Add;
3103 END IF;
3104 --Standard check of commit
3105 IF FND_API.To_Boolean ( p_commit ) THEN
3106 COMMIT WORK;
3107 END IF;
3108 -- Debug Message
3112 FND_MSG_PUB.Add;
3109 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3110 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3111 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
3113 END IF;
3114 --Standard call to get message count and if count=1, get the message
3115 FND_MSG_PUB.Count_And_Get (
3116 p_encoded => FND_API.G_FALSE,
3117 p_count => x_msg_count,
3118 p_data => x_msg_data
3119 );
3120 EXCEPTION
3121 WHEN FND_API.G_EXC_ERROR THEN
3122 ROLLBACK TO Set_ChannelContent_PVT;
3123 x_return_status := FND_API.G_RET_STS_ERROR;
3124 -- Standard call to get message count and if count=1, get the message
3125 FND_MSG_PUB.Count_And_Get (
3126 p_encoded => FND_API.G_FALSE,
3127 p_count => x_msg_count,
3128 p_data => x_msg_data
3129 );
3130 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3131 ROLLBACK TO Set_ChannelContent_PVT;
3132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3133 -- Standard call to get message count and if count=1, get the message
3134 FND_MSG_PUB.Count_And_Get (
3135 p_encoded => FND_API.G_FALSE,
3136 p_count => x_msg_count,
3137 p_data => x_msg_data
3138 );
3139 WHEN OTHERS THEN
3140 ROLLBACK TO Set_ChannelContent_PVT;
3141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3142 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3143 THEN
3144 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3145 END IF;
3146 -- Standard call to get message count and if count=1, get the message
3147 FND_MSG_PUB.Count_And_Get (
3148 p_encoded => FND_API.G_FALSE,
3149 p_count => x_msg_count,
3150 p_data => x_msg_data
3151 );
3152 --
3153 END Set_ChannelContentTypes;
3154 --------------------------------------------------------------------------------
3155 -- Start of comments
3156 -- API name : Get_ChannelContentTypes
3157 -- Type : Private
3158 -- Pre-reqs : None
3159 -- Function : Get content categories of the content channel.
3160 -- Parameters :
3161 -- IN p_api_version IN NUMBER Required
3162 -- p_init_msg_list IN VARCHAR2 Optional
3163 -- Default = FND_API.G_FALSE
3164 -- p_validation_level NUMBER Optional
3165 -- Default = FND_API.G_VALID_LEVEL_FULL
3166 -- p_channel_id IN NUMBER Optional
3167 -- the channel id. Default = FND_API.G_MISS_NUM
3168 -- p_channel_name IN VARCHAR2 Optional
3169 -- the channel name. Default = FND_API.G_MISS_CHAR
3170 -- Either pass the channe id (preferred) or channel name
3171 -- to identify the channel.
3172 -- p_category_id IN NUMBER Optional
3173 -- the category id. Default = FND_API.G_MISS_NUM
3174 -- OUT : x_return_status OUT VARCHAR2
3175 -- x_msg_count OUT NUMBER
3176 -- x_msg_data OUT VARCHAR2
3177 -- x_content_type_id_array OUT AMV_NUMBER_VARRAY_TYPE
3178 -- Version : Current version 1.0
3179 -- Previous version 1.0
3180 -- Initial version 1.0
3181 -- Notes :
3182 --
3183 -- End of comments
3184 --
3185 PROCEDURE Get_ChannelContentTypes
3186 ( p_api_version IN NUMBER,
3187 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3188 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3189 x_return_status OUT NOCOPY VARCHAR2,
3190 x_msg_count OUT NOCOPY NUMBER,
3191 x_msg_data OUT NOCOPY VARCHAR2,
3192 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3193 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
3194 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3195 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
3196 x_content_type_id_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE
3197 )
3198 IS
3199 l_api_name CONSTANT VARCHAR2(30) := 'Get_ChannelContentTypes';
3200 l_api_version CONSTANT NUMBER := 1.0;
3201 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3202 --
3203 l_resource_id number;
3204 l_user_id number;
3205 l_login_user_id number;
3206 l_login_user_status varchar2(30);
3207 l_Error_Msg varchar2(2000);
3208 l_Error_Token varchar2(80);
3209 --
3210 l_channel_id number;
3211 l_channel_exist_flag varchar2(1);
3212 l_record_count number := 1;
3213 l_content_type_id number;
3214 --
3215 CURSOR Get_Categories IS
3216 select content_type_id
3217 from amv_c_content_types
3218 where channel_id = l_channel_id;
3219 --
3220 BEGIN
3221 -- Standard begin of API savepoint
3222 SAVEPOINT Get_ChannelContent_PVT;
3223 -- Standard call to check for call compatibility.
3224 IF NOT FND_API.Compatible_API_Call (
3225 l_api_version,
3226 p_api_version,
3227 l_api_name,
3228 G_PKG_NAME)
3229 THEN
3233 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3230 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3231 END IF;
3232 -- Debug Message
3234 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3235 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
3236 FND_MSG_PUB.Add;
3237 END IF;
3238 --Initialize message list if p_init_msg_list is TRUE.
3239 IF FND_API.To_Boolean (p_init_msg_list) THEN
3240 FND_MSG_PUB.initialize;
3241 END IF;
3242 -- Get the current (login) user id.
3243 AMV_UTILITY_PVT.Get_UserInfo(
3244 x_resource_id => l_resource_id,
3245 x_user_id => l_user_id,
3246 x_login_id => l_login_user_id,
3247 x_user_status => l_login_user_status
3248 );
3249 -- check login user
3250 IF (p_check_login_user = FND_API.G_TRUE) THEN
3251 -- Check if user is login and has the required privilege.
3252 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3253 -- User is not login.
3254 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3255 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3256 FND_MSG_PUB.Add;
3257 END IF;
3258 RAISE FND_API.G_EXC_ERROR;
3259 END IF;
3260 END IF;
3261 -- This fix is for executing api in sqlplus mode
3262 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3263 l_login_user_id := g_login_user_id;
3264 l_user_id := g_user_id;
3265 l_resource_id := g_resource_id;
3266 END IF;
3267 -- Initialize API return status to sucess
3268 x_return_status := FND_API.G_RET_STS_SUCCESS;
3269
3270 -- Check channel id and status for a given channel id or channel name
3271 Get_ChannelStatus (
3272 x_return_status => x_return_status,
3273 p_channel_id => p_channel_id,
3274 p_channel_name => p_channel_name,
3275 p_category_id => p_category_id,
3276 x_exist_flag => l_channel_exist_flag,
3277 x_channel_id => l_channel_id,
3278 x_error_msg => l_Error_Msg,
3279 x_error_token => l_Error_Token
3280 );
3281 -- get channel categories if the channel exists
3282 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
3283 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3284 THEN
3285 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
3286 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
3287 FND_MSG_PUB.Add;
3288 END IF;
3289 RAISE FND_API.G_EXC_ERROR;
3290 ELSE
3291 OPEN Get_Categories;
3292 x_content_type_id_array := AMV_NUMBER_VARRAY_TYPE();
3293 LOOP
3294 FETCH Get_Categories INTO l_content_type_id;
3295 EXIT WHEN Get_Categories%NOTFOUND;
3296 x_content_type_id_array.extend;
3297 x_content_type_id_array(l_record_count) := l_content_type_id;
3298 l_record_count := l_record_count + 1;
3299 END LOOP;
3300 CLOSE Get_Categories;
3301 END IF;
3302 --
3303
3304 -- Debug Message
3305 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3306 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3307 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
3308 FND_MSG_PUB.Add;
3309 END IF;
3310 --Standard call to get message count and if count=1, get the message
3311 FND_MSG_PUB.Count_And_Get (
3312 p_encoded => FND_API.G_FALSE,
3313 p_count => x_msg_count,
3314 p_data => x_msg_data
3315 );
3316 EXCEPTION
3317 WHEN FND_API.G_EXC_ERROR THEN
3318 ROLLBACK TO Get_ChannelContent_PVT;
3319 x_return_status := FND_API.G_RET_STS_ERROR;
3320 -- Standard call to get message count and if count=1, get the message
3321 FND_MSG_PUB.Count_And_Get (
3322 p_encoded => FND_API.G_FALSE,
3323 p_count => x_msg_count,
3324 p_data => x_msg_data
3325 );
3326 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3327 ROLLBACK TO Get_ChannelContent_PVT;
3328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3329 -- Standard call to get message count and if count=1, get the message
3330 FND_MSG_PUB.Count_And_Get (
3331 p_encoded => FND_API.G_FALSE,
3332 p_count => x_msg_count,
3333 p_data => x_msg_data
3334 );
3335 WHEN OTHERS THEN
3336 ROLLBACK TO Get_ChannelContent_PVT;
3337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3338 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3339 THEN
3340 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3341 END IF;
3342 -- Standard call to get message count and if count=1, get the message
3343 FND_MSG_PUB.Count_And_Get (
3344 p_encoded => FND_API.G_FALSE,
3345 p_count => x_msg_count,
3346 p_data => x_msg_data
3347 );
3348 --
3349 END Get_ChannelContentTypes;
3350 --------------------------------------------------------------------------------
3351 -- Start of comments
3352 -- API name : Set_ChannelPerspectives
3353 -- Type : Private
3354 -- Pre-reqs : None
3355 -- Function : Change perspectives of the content channel.
3356 -- Parameters :
3357 -- IN p_api_version IN NUMBER Required
3361 -- Default = FND_API.G_FALSE
3358 -- p_init_msg_list IN VARCHAR2 Optional
3359 -- Default = FND_API.G_FALSE
3360 -- p_commit IN VARCHAR2 Optional
3362 -- p_validation_level NUMBER Optional
3363 -- Default = FND_API.G_VALID_LEVEL_FULL
3364 -- p_channel_id IN NUMBER Optional
3365 -- the channel id. Default = FND_API.G_MISS_NUM
3366 -- p_channel_name IN VARCHAR2 Optional
3367 -- the channel name. Default = FND_API.G_MISS_CHAR
3368 -- Either pass the channe id (preferred) or channel name
3369 -- to identify the channel.
3370 -- p_category_id IN NUMBER Optional
3371 -- the category id. Default = FND_API.G_MISS_NUM
3372 -- p_perspective_id_array IN AMV_NUMBER_VARRAY_TYPE
3373 -- OUT : x_return_status OUT VARCHAR2
3374 -- x_msg_count OUT NUMBER
3375 -- x_msg_data OUT VARCHAR2
3376 -- Version : Current version 1.0
3377 -- Previous version 1.0
3378 -- Initial version 1.0
3379 -- Notes :
3380 --
3381 -- End of comments
3382 --
3383 PROCEDURE Set_ChannelPerspectives
3384 ( p_api_version IN NUMBER,
3385 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3386 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3387 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3388 x_return_status OUT NOCOPY VARCHAR2,
3389 x_msg_count OUT NOCOPY NUMBER,
3390 x_msg_data OUT NOCOPY VARCHAR2,
3391 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3392 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
3393 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3394 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
3395 p_perspective_id_array IN AMV_NUMBER_VARRAY_TYPE
3396 )
3397 IS
3398 l_api_name CONSTANT VARCHAR2(30) := 'Set_ChannelPerspectives';
3399 l_api_version CONSTANT NUMBER := 1.0;
3400 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3401 --
3402 l_resource_id number;
3403 l_user_id number;
3404 l_login_user_id number;
3405 l_login_user_status varchar2(30);
3406 l_Error_Msg varchar2(2000);
3407 l_Error_Token varchar2(80);
3408 l_object_version_number number := 1;
3409 --
3410 l_channel_id number;
3411 l_channel_exist_flag varchar2(1);
3412 l_setup_result varchar2(1);
3413 l_update_channel_flag Varchar2(1);
3414 l_chl_perspective_id number;
3415 --
3416 CURSOR C_ChanPerspective_Seq IS
3417 select amv_c_chl_perspectives_s.nextval
3418 from dual;
3419 BEGIN
3420 -- Standard begin of API savepoint
3421 SAVEPOINT Set_ChannelPerspectives_PVT;
3422 -- Standard call to check for call compatibility.
3423 IF NOT FND_API.Compatible_API_Call (
3424 l_api_version,
3425 p_api_version,
3426 l_api_name,
3427 G_PKG_NAME)
3428 THEN
3429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3430 END IF;
3431 -- Debug Message
3432 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3433 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3434 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
3435 FND_MSG_PUB.Add;
3436 END IF;
3437 --Initialize message list if p_init_msg_list is TRUE.
3438 IF FND_API.To_Boolean (p_init_msg_list) THEN
3439 FND_MSG_PUB.initialize;
3440 END IF;
3441 -- Get the current (login) user id.
3442 AMV_UTILITY_PVT.Get_UserInfo(
3443 x_resource_id => l_resource_id,
3444 x_user_id => l_user_id,
3445 x_login_id => l_login_user_id,
3446 x_user_status => l_login_user_status
3447 );
3448 -- check login user
3449 IF (p_check_login_user = FND_API.G_TRUE) THEN
3450 -- Check if user is login and has the required privilege.
3451 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3452 -- User is not login.
3453 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3454 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3455 FND_MSG_PUB.Add;
3456 END IF;
3457 RAISE FND_API.G_EXC_ERROR;
3458 END IF;
3459 END IF;
3460 -- This fix is for executing api in sqlplus mode
3461 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3462 l_login_user_id := g_login_user_id;
3463 l_user_id := g_user_id;
3464 l_resource_id := g_resource_id;
3465 END IF;
3466 -- Initialize API return status to sucess
3467 x_return_status := FND_API.G_RET_STS_SUCCESS;
3468
3469 -- Check channel id and status for a given channel id or channel name
3470 Get_ChannelStatus (
3471 x_return_status => x_return_status,
3472 p_channel_id => p_channel_id,
3473 p_channel_name => p_channel_name,
3474 p_category_id => p_category_id,
3475 x_exist_flag => l_channel_exist_flag,
3479 );
3476 x_channel_id => l_channel_id,
3477 x_error_msg => l_Error_Msg,
3478 x_error_token => l_Error_Token
3480 -- change perspectives if the channel exists
3481 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
3482 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3483 THEN
3484 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
3485 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
3486 FND_MSG_PUB.Add;
3487 END IF;
3488 RAISE FND_API.G_EXC_ERROR;
3489 ELSE
3490 --
3491 AMV_USER_PVT.Can_SetupChannel (
3492 p_api_version => l_api_version,
3493 p_init_msg_list => FND_API.G_FALSE,
3494 p_validation_level => p_validation_level,
3495 x_return_status => x_return_status,
3496 x_msg_count => x_msg_count,
3497 x_msg_data => x_msg_data,
3498 p_check_login_user => FND_API.G_FALSE,
3499 p_resource_id => l_resource_id,
3500 p_include_group_flag => FND_API.G_TRUE,
3501 x_result_flag => l_setup_result
3502 );
3503
3504 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3505 IF (l_setup_result = FND_API.G_TRUE) THEN
3506 l_update_channel_flag := FND_API.G_TRUE;
3507 ELSE
3508 IF (AMV_UTILITY_PVT.Get_UpdateChannelStatus(
3509 l_channel_id,
3510 l_resource_id,
3511 AMV_UTILITY_PVT.G_USER) )
3512 THEN
3513 l_update_channel_flag := FND_API.G_TRUE;
3514 ELSE
3515 -- user does not have privilege to create public channel
3516 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3517 THEN
3518 FND_MESSAGE.Set_Name('AMV', 'AMV_NO_ACCESS_ERROR');
3519 FND_MESSAGE.Set_Token('LEVEL','Channel');
3520 FND_MSG_PUB.Add;
3521 END IF;
3522 RAISE FND_API.G_EXC_ERROR;
3523 END IF;
3524 END IF;
3525 ELSE
3526 -- error while user privilege check in Can_SetupChannel
3527 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3528 THEN
3529 FND_MESSAGE.Set_Name('AMV', 'AMV_SETUP_CHECK_ERROR');
3530 FND_MESSAGE.Set_Token('LEVEL','Channel');
3531 FND_MSG_PUB.Add;
3532 END IF;
3533 RAISE FND_API.G_EXC_ERROR;
3534 END IF;
3535 END IF;
3536 --
3537
3538 -- update channels if user has privilege
3539 IF l_update_channel_flag = FND_API.G_TRUE THEN
3540 -- remove old perspectives for the channel
3541 DELETE FROM amv_c_chl_perspectives
3542 WHERE channel_id = l_channel_id;
3543
3544 -- set match on flag in channel table
3545 IF p_perspective_id_array.count > 0 THEN
3546 UPDATE amv_c_channels_b
3547 set match_on_perspective_flag = FND_API.G_TRUE
3548 where channel_id = l_channel_id;
3549 ELSE
3550 UPDATE amv_c_channels_b
3551 set match_on_perspective_flag = FND_API.G_FALSE
3552 where channel_id = l_channel_id;
3553 END IF;
3554
3555 -- set match on all criteria flag
3556 IF Get_MatchOnStatus(l_channel_id) THEN
3557 UPDATE amv_c_channels_b
3558 set match_on_all_criteria_flag = FND_API.G_TRUE
3559 where channel_id = l_channel_id;
3560 ELSE
3561 UPDATE amv_c_channels_b
3562 set match_on_all_criteria_flag = FND_API.G_FALSE
3563 where channel_id = l_channel_id;
3564 END IF;
3565
3566 -- insert the new perspectives id's for the channel
3567 FOR i in 1..p_perspective_id_array.count LOOP
3568
3569 OPEN C_ChanPerspective_Seq;
3570 FETCH C_ChanPerspective_Seq INTO l_chl_perspective_id;
3571 CLOSE C_ChanPerspective_Seq;
3572
3573 INSERT INTO amv_c_chl_perspectives
3574 (
3575 channel_perspective_id,
3576 object_version_number,
3577 last_update_date,
3578 last_updated_by,
3579 creation_date,
3580 created_by,
3581 last_update_login,
3582 channel_id,
3583 perspective_id
3584 )
3585 VALUES (
3586 l_chl_perspective_id,
3587 l_object_version_number,
3588 sysdate,
3589 l_user_id,
3590 sysdate,
3591 l_user_id,
3592 l_login_user_id,
3593 l_channel_id,
3594 p_perspective_id_array(i)
3595 );
3596 END LOOP;
3597 END IF;
3598 --
3599
3600 -- Success message
3601 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3602 THEN
3603 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
3604 FND_MESSAGE.Set_Token('ROW', l_full_name);
3605 FND_MSG_PUB.Add;
3606 END IF;
3607 --Standard check of commit
3608 IF FND_API.To_Boolean ( p_commit ) THEN
3609 COMMIT WORK;
3610 END IF;
3611 -- Debug Message
3612 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3613 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3614 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
3615 FND_MSG_PUB.Add;
3616 END IF;
3617 --Standard call to get message count and if count=1, get the message
3618 FND_MSG_PUB.Count_And_Get (
3619 p_encoded => FND_API.G_FALSE,
3620 p_count => x_msg_count,
3621 p_data => x_msg_data
3622 );
3623 EXCEPTION
3624 WHEN FND_API.G_EXC_ERROR THEN
3625 ROLLBACK TO Set_ChannelPerspectives_PVT;
3626 x_return_status := FND_API.G_RET_STS_ERROR;
3630 p_count => x_msg_count,
3627 -- Standard call to get message count and if count=1, get the message
3628 FND_MSG_PUB.Count_And_Get (
3629 p_encoded => FND_API.G_FALSE,
3631 p_data => x_msg_data
3632 );
3633 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3634 ROLLBACK TO Set_ChannelPerspectives_PVT;
3635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3636 -- Standard call to get message count and if count=1, get the message
3637 FND_MSG_PUB.Count_And_Get (
3638 p_encoded => FND_API.G_FALSE,
3639 p_count => x_msg_count,
3640 p_data => x_msg_data
3641 );
3642 WHEN OTHERS THEN
3643 ROLLBACK TO Set_ChannelPerspectives_PVT;
3644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3645 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3646 THEN
3647 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3648 END IF;
3649 -- Standard call to get message count and if count=1, get the message
3650 FND_MSG_PUB.Count_And_Get (
3651 p_encoded => FND_API.G_FALSE,
3652 p_count => x_msg_count,
3653 p_data => x_msg_data
3654 );
3655 --
3656 END Set_ChannelPerspectives;
3657 --------------------------------------------------------------------------------
3658 -- Start of comments
3659 -- API name : Get_ChannelPerspectives
3660 -- Type : Private
3661 -- Pre-reqs : None
3662 -- Function : Get perspectives of the content channel.
3663 -- Parameters :
3664 -- IN p_api_version IN NUMBER Required
3665 -- p_init_msg_list IN VARCHAR2 Optional
3666 -- Default = FND_API.G_FALSE
3667 -- p_validation_level NUMBER Optional
3668 -- Default = FND_API.G_VALID_LEVEL_FULL
3669 -- p_channel_id IN NUMBER Optional
3670 -- the channel id. Default = FND_API.G_MISS_NUM
3671 -- p_channel_name IN VARCHAR2 Optional
3672 -- the channel name. Default = FND_API.G_MISS_CHAR
3673 -- Either pass the channe id (preferred) or channel name
3674 -- to identify the channel.
3675 -- p_category_id IN NUMBER Optional
3676 -- the category id. Default = FND_API.G_MISS_NUM
3677 -- OUT : x_return_status OUT VARCHAR2
3678 -- x_msg_count OUT NUMBER
3679 -- x_msg_data OUT VARCHAR2
3680 -- x_perspective_id_array OUT AMV_NUMBER_VARRAY_TYPE
3681 -- Version : Current version 1.0
3682 -- Previous version 1.0
3683 -- Initial version 1.0
3684 -- Notes :
3685 --
3686 -- End of comments
3687 --
3688 PROCEDURE Get_ChannelPerspectives
3689 ( p_api_version IN NUMBER,
3690 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3691 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3692 x_return_status OUT NOCOPY VARCHAR2,
3693 x_msg_count OUT NOCOPY NUMBER,
3694 x_msg_data OUT NOCOPY VARCHAR2,
3695 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3696 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
3697 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3698 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
3699 x_perspective_id_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE
3700 )
3701 IS
3702 l_api_name CONSTANT VARCHAR2(30) := 'Get_ChannelPerspectives';
3703 l_api_version CONSTANT NUMBER := 1.0;
3704 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3705 --
3706 l_resource_id number;
3707 l_user_id number;
3708 l_login_user_id number;
3709 l_login_user_status varchar2(30);
3710 l_Error_Msg varchar2(2000);
3711 l_Error_Token varchar2(80);
3712 --
3713 l_channel_id number;
3714 l_channel_exist_flag varchar2(1);
3715 l_record_count number := 1;
3716 l_perspective_id number;
3717 --
3718 CURSOR Get_Perspectives IS
3719 select perspective_id
3720 from amv_c_chl_perspectives
3721 where channel_id = l_channel_id;
3722 --
3723 BEGIN
3724 -- Standard begin of API savepoint
3725 SAVEPOINT Get_ChannelPerspectives_PVT;
3726 -- Standard call to check for call compatibility.
3727 IF NOT FND_API.Compatible_API_Call (
3728 l_api_version,
3729 p_api_version,
3730 l_api_name,
3731 G_PKG_NAME)
3732 THEN
3733 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3734 END IF;
3735 -- Debug Message
3736 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3737 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3738 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
3739 FND_MSG_PUB.Add;
3740 END IF;
3741 --Initialize message list if p_init_msg_list is TRUE.
3742 IF FND_API.To_Boolean (p_init_msg_list) THEN
3743 FND_MSG_PUB.initialize;
3744 END IF;
3745 -- Get the current (login) user id.
3746 AMV_UTILITY_PVT.Get_UserInfo(
3747 x_resource_id => l_resource_id,
3748 x_user_id => l_user_id,
3752 -- check login user
3749 x_login_id => l_login_user_id,
3750 x_user_status => l_login_user_status
3751 );
3753 IF (p_check_login_user = FND_API.G_TRUE) THEN
3754 -- Check if user is login and has the required privilege.
3755 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3756 -- User is not login.
3757 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3758 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3759 FND_MSG_PUB.Add;
3760 END IF;
3761 RAISE FND_API.G_EXC_ERROR;
3762 END IF;
3763 END IF;
3764 -- This fix is for executing api in sqlplus mode
3765 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3766 l_login_user_id := g_login_user_id;
3767 l_user_id := g_user_id;
3768 l_resource_id := g_resource_id;
3769 END IF;
3770 -- Initialize API return status to sucess
3771 x_return_status := FND_API.G_RET_STS_SUCCESS;
3772
3773 -- Check channel id and status for a given channel id or channel name
3774 Get_ChannelStatus (
3775 x_return_status => x_return_status,
3776 p_channel_id => p_channel_id,
3777 p_channel_name => p_channel_name,
3778 p_category_id => p_category_id,
3779 x_exist_flag => l_channel_exist_flag,
3780 x_channel_id => l_channel_id,
3781 x_error_msg => l_Error_Msg,
3782 x_error_token => l_Error_Token
3783 );
3784 -- get channel perspectives if the channel exists
3785 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
3786 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3787 THEN
3788 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
3789 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
3790 FND_MSG_PUB.Add;
3791 END IF;
3792 RAISE FND_API.G_EXC_ERROR;
3793 ELSE
3794 OPEN Get_Perspectives;
3795 x_perspective_id_array := AMV_NUMBER_VARRAY_TYPE();
3796 LOOP
3797 FETCH Get_Perspectives INTO l_perspective_id;
3798 EXIT WHEN Get_Perspectives%NOTFOUND;
3799 x_perspective_id_array.extend;
3800 x_perspective_id_array(l_record_count) := l_perspective_id;
3801 l_record_count := l_record_count + 1;
3802 END LOOP;
3803 CLOSE Get_Perspectives;
3804 END IF;
3805 --
3806
3807 -- Debug Message
3808 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3809 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3810 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
3811 FND_MSG_PUB.Add;
3812 END IF;
3813 --Standard call to get message count and if count=1, get the message
3814 FND_MSG_PUB.Count_And_Get (
3815 p_encoded => FND_API.G_FALSE,
3816 p_count => x_msg_count,
3817 p_data => x_msg_data
3818 );
3819 EXCEPTION
3820 WHEN FND_API.G_EXC_ERROR THEN
3821 ROLLBACK TO Get_ChannelPerspectives_PVT;
3822 x_return_status := FND_API.G_RET_STS_ERROR;
3823 -- Standard call to get message count and if count=1, get the message
3824 FND_MSG_PUB.Count_And_Get (
3825 p_encoded => FND_API.G_FALSE,
3826 p_count => x_msg_count,
3827 p_data => x_msg_data
3828 );
3829 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3830 ROLLBACK TO Get_ChannelPerspectives_PVT;
3831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3832 -- Standard call to get message count and if count=1, get the message
3833 FND_MSG_PUB.Count_And_Get (
3834 p_encoded => FND_API.G_FALSE,
3835 p_count => x_msg_count,
3836 p_data => x_msg_data
3837 );
3838 WHEN OTHERS THEN
3839 ROLLBACK TO Get_ChannelPerspectives_PVT;
3840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3841 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3842 THEN
3843 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3844 END IF;
3845 -- Standard call to get message count and if count=1, get the message
3846 FND_MSG_PUB.Count_And_Get (
3847 p_encoded => FND_API.G_FALSE,
3848 p_count => x_msg_count,
3849 p_data => x_msg_data
3850 );
3851 --
3852 END Get_ChannelPerspectives;
3853 --------------------------------------------------------------------------------
3854 -- Start of comments
3855 -- API name : Set_ChannelItemTypes
3856 -- Type : Private
3857 -- Pre-reqs : None
3858 -- Function : Change user groups of the content channel.
3859 -- Parameters :
3860 -- IN p_api_version IN NUMBER Required
3861 -- p_init_msg_list IN VARCHAR2 Optional
3862 -- Default = FND_API.G_FALSE
3863 -- p_validation_level NUMBER Optional
3864 -- Default = FND_API.G_VALID_LEVEL_FULL
3865 -- p_commit IN VARCHAR2 Optional
3866 -- Default = FND_API.G_FALSE
3867 -- p_channel_id IN NUMBER Optional
3868 -- the channel id. Default = FND_API.G_MISS_NUM
3869 -- p_channel_name IN VARCHAR2 Optional
3870 -- the channel name. Default = FND_API.G_MISS_CHAR
3874 -- the category id. Default = FND_API.G_MISS_NUM
3871 -- Either pass the channe id (preferred) or channel name
3872 -- to identify the channel.
3873 -- p_category_id IN NUMBER Optional
3875 -- p_item_type_array IN AMV_CHAR_VARRAY_TYPE
3876 -- OUT : x_return_status OUT VARCHAR2
3877 -- x_msg_count OUT NUMBER
3878 -- x_msg_data OUT VARCHAR2
3879 -- Version : Current version 1.0
3880 -- Previous version 1.0
3881 -- Initial version 1.0
3882 -- Notes :
3883 --
3884 -- End of comments
3885 --
3886 PROCEDURE Set_ChannelItemTypes
3887 ( p_api_version IN NUMBER,
3888 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3889 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3890 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3891 x_return_status OUT NOCOPY VARCHAR2,
3892 x_msg_count OUT NOCOPY NUMBER,
3893 x_msg_data OUT NOCOPY VARCHAR2,
3894 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
3895 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
3896 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
3897 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
3898 p_item_type_array IN AMV_CHAR_VARRAY_TYPE
3899 )
3900 IS
3901 l_api_name CONSTANT VARCHAR2(30) := 'Set_ChannelItemTypes';
3902 l_api_version CONSTANT NUMBER := 1.0;
3903 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3904 --
3905 l_resource_id number;
3906 l_user_id number;
3907 l_login_user_id number;
3908 l_login_user_status varchar2(30);
3909 l_Error_Msg varchar2(2000);
3910 l_Error_Token varchar2(80);
3911 l_object_version_number number := 1;
3912 --
3913 l_channel_id number;
3914 l_channel_exist_flag varchar2(1);
3915 l_setup_result varchar2(1);
3916 l_update_channel_flag Varchar2(1);
3917 l_item_type_id number;
3918 --
3919 CURSOR C_ChanItemType_Seq IS
3920 select amv_c_item_types_s.nextval
3921 from dual;
3922 BEGIN
3923 -- Standard begin of API savepoint
3924 SAVEPOINT Set_ChannelItems_PVT;
3925 -- Standard call to check for call compatibility.
3926 IF NOT FND_API.Compatible_API_Call (
3927 l_api_version,
3928 p_api_version,
3929 l_api_name,
3930 G_PKG_NAME)
3931 THEN
3932 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3933 END IF;
3934 -- Debug Message
3935 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
3936 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
3937 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
3938 FND_MSG_PUB.Add;
3939 END IF;
3940 --Initialize message list if p_init_msg_list is TRUE.
3941 IF FND_API.To_Boolean (p_init_msg_list) THEN
3942 FND_MSG_PUB.initialize;
3943 END IF;
3944 -- Get the current (login) user id.
3945 AMV_UTILITY_PVT.Get_UserInfo(
3946 x_resource_id => l_resource_id,
3947 x_user_id => l_user_id,
3948 x_login_id => l_login_user_id,
3949 x_user_status => l_login_user_status
3950 );
3951 -- check login user
3952 IF (p_check_login_user = FND_API.G_TRUE) THEN
3953 -- Check if user is login and has the required privilege.
3954 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3955 -- User is not login.
3956 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3957 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
3958 FND_MSG_PUB.Add;
3959 END IF;
3960 RAISE FND_API.G_EXC_ERROR;
3961 END IF;
3962 END IF;
3963 -- This fix is for executing api in sqlplus mode
3964 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
3965 l_login_user_id := g_login_user_id;
3966 l_user_id := g_user_id;
3967 l_resource_id := g_resource_id;
3968 END IF;
3969 -- Initialize API return status to sucess
3970 x_return_status := FND_API.G_RET_STS_SUCCESS;
3971
3972 -- Check channel id and status for a given channel id or channel name
3973 Get_ChannelStatus (
3974 x_return_status => x_return_status,
3975 p_channel_id => p_channel_id,
3976 p_channel_name => p_channel_name,
3977 p_category_id => p_category_id,
3978 x_exist_flag => l_channel_exist_flag,
3979 x_channel_id => l_channel_id,
3980 x_error_msg => l_Error_Msg,
3981 x_error_token => l_Error_Token
3982 );
3983 -- change usergroups if the channel exists
3984 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
3985 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3986 THEN
3987 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
3988 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
3989 FND_MSG_PUB.Add;
3990 END IF;
3991 RAISE FND_API.G_EXC_ERROR;
3992 ELSE
3993 --
3994 AMV_USER_PVT.Can_SetupChannel (
3995 p_api_version => l_api_version,
3996 p_init_msg_list => FND_API.G_FALSE,
3997 p_validation_level => p_validation_level,
4001 p_check_login_user => FND_API.G_FALSE,
3998 x_return_status => x_return_status,
3999 x_msg_count => x_msg_count,
4000 x_msg_data => x_msg_data,
4002 p_resource_id => l_resource_id,
4003 p_include_group_flag => FND_API.G_TRUE,
4004 x_result_flag => l_setup_result
4005 );
4006
4007 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4008 IF (l_setup_result = FND_API.G_TRUE) THEN
4009 l_update_channel_flag := FND_API.G_TRUE;
4010 ELSE
4011 IF (AMV_UTILITY_PVT.Get_UpdateChannelStatus(
4012 l_channel_id,
4013 l_resource_id,
4014 AMV_UTILITY_PVT.G_USER) )
4015 THEN
4016 l_update_channel_flag := FND_API.G_TRUE;
4017 ELSE
4018 -- user does not have privilege to create public channel
4019 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4020 THEN
4021 FND_MESSAGE.Set_Name('AMV', 'AMV_NO_ACCESS_ERROR');
4022 FND_MESSAGE.Set_Token('LEVEL','Channel');
4023 FND_MSG_PUB.Add;
4024 END IF;
4025 RAISE FND_API.G_EXC_ERROR;
4026 END IF;
4027 END IF;
4028 ELSE
4029 -- error while user privilege check in Can_SetupChannel
4030 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4031 THEN
4032 FND_MESSAGE.Set_Name('AMV', 'AMV_SETUP_CHECK_ERROR');
4033 FND_MESSAGE.Set_Token('LEVEL','Channel');
4034 FND_MSG_PUB.Add;
4035 END IF;
4036 RAISE FND_API.G_EXC_ERROR;
4037 END IF;
4038 END IF;
4039 --
4040
4041 -- update channels if user has privilege
4042 IF l_update_channel_flag = FND_API.G_TRUE THEN
4043 -- remove old groups for the channel
4044 DELETE FROM amv_c_item_types
4045 WHERE channel_id = l_channel_id;
4046
4047 -- set match on flag in channel table
4048 IF p_item_type_array.count > 0 THEN
4049 UPDATE amv_c_channels_b
4050 set match_on_item_type_flag = FND_API.G_TRUE
4051 where channel_id = l_channel_id;
4052 ELSE
4053 UPDATE amv_c_channels_b
4054 set match_on_item_type_flag = FND_API.G_FALSE
4055 where channel_id = l_channel_id;
4056 END IF;
4057
4058 -- set match on all criteria flag
4059 IF Get_MatchOnStatus(l_channel_id) THEN
4060 UPDATE amv_c_channels_b
4061 set match_on_all_criteria_flag = FND_API.G_TRUE
4062 where channel_id = l_channel_id;
4063 ELSE
4064 UPDATE amv_c_channels_b
4065 set match_on_all_criteria_flag = FND_API.G_FALSE
4066 where channel_id = l_channel_id;
4067 END IF;
4068
4069 -- insert the new groups id's for the channel
4070 FOR i in 1..p_item_type_array.count LOOP
4071 OPEN C_ChanItemType_Seq;
4072 FETCH C_ChanItemType_Seq INTO l_item_type_id;
4073 CLOSE C_ChanItemType_Seq;
4074
4075 INSERT INTO amv_c_item_types
4076 (
4077 channel_item_type_id,
4078 object_version_number,
4079 last_update_date,
4080 last_updated_by,
4081 creation_date,
4082 created_by,
4083 last_update_login,
4084 channel_id,
4085 item_type
4086 )
4087 VALUES (
4088 l_item_type_id,
4089 l_object_version_number,
4090 sysdate,
4091 l_user_id,
4092 sysdate,
4093 l_user_id,
4094 l_login_user_id,
4095 l_channel_id,
4096 p_item_type_array(i)
4097 );
4098 END LOOP;
4099 END IF;
4100 --
4101
4102 -- Success message
4103 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
4104 THEN
4105 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
4106 FND_MESSAGE.Set_Token('ROW', l_full_name);
4107 FND_MSG_PUB.Add;
4108 END IF;
4109 --Standard check of commit
4110 IF FND_API.To_Boolean ( p_commit ) THEN
4111 COMMIT WORK;
4112 END IF;
4113 -- Debug Message
4114 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4115 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
4116 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
4117 FND_MSG_PUB.Add;
4118 END IF;
4119 --Standard call to get message count and if count=1, get the message
4120 FND_MSG_PUB.Count_And_Get (
4121 p_encoded => FND_API.G_FALSE,
4122 p_count => x_msg_count,
4123 p_data => x_msg_data
4124 );
4125 EXCEPTION
4126 WHEN FND_API.G_EXC_ERROR THEN
4127 ROLLBACK TO Set_ChannelItems_PVT;
4128 x_return_status := FND_API.G_RET_STS_ERROR;
4129 -- Standard call to get message count and if count=1, get the message
4130 FND_MSG_PUB.Count_And_Get (
4131 p_encoded => FND_API.G_FALSE,
4132 p_count => x_msg_count,
4133 p_data => x_msg_data
4134 );
4135 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4136 ROLLBACK TO Set_ChannelItems_PVT;
4137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4138 -- Standard call to get message count and if count=1, get the message
4139 FND_MSG_PUB.Count_And_Get (
4140 p_encoded => FND_API.G_FALSE,
4141 p_count => x_msg_count,
4142 p_data => x_msg_data
4143 );
4144 WHEN OTHERS THEN
4145 ROLLBACK TO Set_ChannelItems_PVT;
4146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4147 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4148 THEN
4152 FND_MSG_PUB.Count_And_Get (
4149 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4150 END IF;
4151 -- Standard call to get message count and if count=1, get the message
4153 p_encoded => FND_API.G_FALSE,
4154 p_count => x_msg_count,
4155 p_data => x_msg_data
4156 );
4157 --
4158 END Set_ChannelItemTypes;
4159 --------------------------------------------------------------------------------
4160 -- Start of comments
4161 -- API name : Get_ChannelItemTypes
4162 -- Type : Private
4163 -- Pre-reqs : None
4164 -- Function : Get user groups of the content channel.
4165 -- Parameters :
4166 -- IN p_api_version IN NUMBER Required
4167 -- p_init_msg_list IN VARCHAR2 Optional
4168 -- Default = FND_API.G_FALSE
4169 -- p_validation_level NUMBER Optional
4170 -- Default = FND_API.G_VALID_LEVEL_FULL
4171 -- p_channel_id IN NUMBER Optional
4172 -- the channel id. Default = FND_API.G_MISS_NUM
4173 -- p_channel_name IN VARCHAR2 Optional
4174 -- the channel name. Default = FND_API.G_MISS_CHAR
4175 -- Either pass the channe id (preferred) or channel name
4176 -- to identify the channel.
4177 -- p_category_id IN NUMBER Optional
4178 -- the category id. Default = FND_API.G_MISS_NUM
4179 -- OUT : x_return_status OUT VARCHAR2
4180 -- x_msg_count OUT NUMBER
4181 -- x_msg_data OUT VARCHAR2
4182 -- x_item_type_array OUT AMV_CHAR_VARRAY_TYPE
4183 -- Version : Current version 1.0
4184 -- Previous version 1.0
4185 -- Initial version 1.0
4186 -- Notes :
4187 --
4188 -- End of comments
4189 --
4190 PROCEDURE Get_ChannelItemTypes
4191 ( p_api_version IN NUMBER,
4192 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4193 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4194 x_return_status OUT NOCOPY VARCHAR2,
4195 x_msg_count OUT NOCOPY NUMBER,
4196 x_msg_data OUT NOCOPY VARCHAR2,
4197 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
4198 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
4199 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
4200 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
4201 x_item_type_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE
4202 )
4203 IS
4204 l_api_name CONSTANT VARCHAR2(30) := 'Get_ChannelItemTypes';
4205 l_api_version CONSTANT NUMBER := 1.0;
4206 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
4207 --
4208 l_resource_id number;
4209 l_user_id number;
4210 l_login_user_id number;
4211 l_login_user_status varchar2(30);
4212 l_Error_Msg varchar2(2000);
4213 l_Error_Token varchar2(80);
4214 --
4215 l_channel_id number;
4216 l_channel_exist_flag varchar2(1);
4217 l_record_count number := 1;
4218 l_item_type varchar2(30);
4219 --
4220 CURSOR Get_ItemTypes IS
4221 select item_type
4222 from amv_c_item_types
4223 where channel_id = l_channel_id;
4224 --
4225 BEGIN
4226 -- Standard begin of API savepoint
4227 SAVEPOINT Get_ChannelItems_PVT;
4228 -- Standard call to check for call compatibility.
4229 IF NOT FND_API.Compatible_API_Call (
4230 l_api_version,
4231 p_api_version,
4232 l_api_name,
4233 G_PKG_NAME)
4234 THEN
4235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4236 END IF;
4237 -- Debug Message
4238 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4239 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
4240 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
4241 FND_MSG_PUB.Add;
4242 END IF;
4243 --Initialize message list if p_init_msg_list is TRUE.
4244 IF FND_API.To_Boolean (p_init_msg_list) THEN
4245 FND_MSG_PUB.initialize;
4246 END IF;
4247 -- Get the current (login) user id.
4248 AMV_UTILITY_PVT.Get_UserInfo(
4249 x_resource_id => l_resource_id,
4250 x_user_id => l_user_id,
4251 x_login_id => l_login_user_id,
4252 x_user_status => l_login_user_status
4253 );
4254 -- check login user
4255 IF (p_check_login_user = FND_API.G_TRUE) THEN
4256 -- Check if user is login and has the required privilege.
4257 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
4258 -- User is not login.
4259 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4260 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
4261 FND_MSG_PUB.Add;
4262 END IF;
4263 RAISE FND_API.G_EXC_ERROR;
4264 END IF;
4265 END IF;
4266 -- This fix is for executing api in sqlplus mode
4267 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
4268 l_login_user_id := g_login_user_id;
4269 l_user_id := g_user_id;
4273 x_return_status := FND_API.G_RET_STS_SUCCESS;
4270 l_resource_id := g_resource_id;
4271 END IF;
4272 -- Initialize API return status to sucess
4274
4275 -- Check channel id and status for a given channel id or channel name
4276 Get_ChannelStatus (
4277 x_return_status => x_return_status,
4278 p_channel_id => p_channel_id,
4279 p_channel_name => p_channel_name,
4280 p_category_id => p_category_id,
4281 x_exist_flag => l_channel_exist_flag,
4282 x_channel_id => l_channel_id,
4283 x_error_msg => l_Error_Msg,
4284 x_error_token => l_Error_Token
4285 );
4286 -- get channel groups if the channel exists
4287 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
4288 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4289 THEN
4290 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
4291 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
4292 FND_MSG_PUB.Add;
4293 END IF;
4294 RAISE FND_API.G_EXC_ERROR;
4295 ELSE
4296 OPEN Get_ItemTypes;
4297 x_item_type_array := AMV_CHAR_VARRAY_TYPE();
4298 LOOP
4299 FETCH Get_ItemTypes INTO l_item_type;
4300 EXIT WHEN Get_ItemTypes%NOTFOUND;
4301 x_item_type_array.extend;
4302 x_item_type_array(l_record_count) := l_item_type;
4303 l_record_count := l_record_count + 1;
4304 END LOOP;
4305 CLOSE Get_ItemTypes;
4306 END IF;
4307 --
4308
4309 -- Debug Message
4310 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4311 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
4312 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
4313 FND_MSG_PUB.Add;
4314 END IF;
4315 --Standard call to get message count and if count=1, get the message
4316 FND_MSG_PUB.Count_And_Get (
4317 p_encoded => FND_API.G_FALSE,
4318 p_count => x_msg_count,
4319 p_data => x_msg_data
4320 );
4321 EXCEPTION
4322 WHEN FND_API.G_EXC_ERROR THEN
4323 ROLLBACK TO Get_Get_ChannelItems_PVT;
4324 x_return_status := FND_API.G_RET_STS_ERROR;
4325 -- Standard call to get message count and if count=1, get the message
4326 FND_MSG_PUB.Count_And_Get (
4327 p_encoded => FND_API.G_FALSE,
4328 p_count => x_msg_count,
4329 p_data => x_msg_data
4330 );
4331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4332 ROLLBACK TO Get_ChannelItems_PVT;
4333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4334 -- Standard call to get message count and if count=1, get the message
4335 FND_MSG_PUB.Count_And_Get (
4336 p_encoded => FND_API.G_FALSE,
4337 p_count => x_msg_count,
4338 p_data => x_msg_data
4339 );
4340 WHEN OTHERS THEN
4341 ROLLBACK TO Get_ChannelItems_PVT;
4342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4343 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4344 THEN
4345 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4346 END IF;
4347 -- Standard call to get message count and if count=1, get the message
4348 FND_MSG_PUB.Count_And_Get (
4349 p_encoded => FND_API.G_FALSE,
4350 p_count => x_msg_count,
4351 p_data => x_msg_data
4352 );
4353 --
4354 END Get_ChannelItemTypes;
4355 --------------------------------------------------------------------------------
4356 -- Start of comments
4357 -- API name : Set_ChannelKeywords
4358 -- Type : Private
4359 -- Pre-reqs : None
4360 -- Function : Change keywords of the content channel.
4361 -- Parameters :
4362 -- IN p_api_version IN NUMBER Required
4363 -- p_init_msg_list IN VARCHAR2 Optional
4364 -- Default = FND_API.G_FALSE
4365 -- p_commit IN VARCHAR2 Optional
4366 -- Default = FND_API.G_FALSE
4367 -- p_validation_level NUMBER Optional
4368 -- Default = FND_API.G_VALID_LEVEL_FULL
4369 -- p_channel_id IN NUMBER Optional
4370 -- the channel id. Default = FND_API.G_MISS_NUM
4371 -- p_channel_name IN VARCHAR2 Optional
4372 -- the channel name. Default = FND_API.G_MISS_CHAR
4373 -- Either pass the channe id (preferred) or channel name
4374 -- to identify the channel.
4375 -- p_category_id IN NUMBER Optional
4376 -- the category id. Default = FND_API.G_MISS_NUM
4377 -- p_keywords_array IN AMV_CHAR_VARRAY_TYPE
4378 -- OUT : x_return_status OUT VARCHAR2
4379 -- x_msg_count OUT NUMBER
4380 -- x_msg_data OUT VARCHAR2
4381 -- Version : Current version 1.0
4382 -- Previous version 1.0
4383 -- Initial version 1.0
4384 -- Notes :
4385 --
4386 -- End of comments
4387 --
4388 PROCEDURE Set_ChannelKeywords
4389 ( p_api_version IN NUMBER,
4393 x_return_status OUT NOCOPY VARCHAR2,
4390 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4391 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4392 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4394 x_msg_count OUT NOCOPY NUMBER,
4395 x_msg_data OUT NOCOPY VARCHAR2,
4396 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
4397 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
4398 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
4399 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
4400 p_keywords_array IN AMV_CHAR_VARRAY_TYPE
4401 )
4402 IS
4403 l_api_name CONSTANT VARCHAR2(30) := 'Set_ChannelKeywords';
4404 l_api_version CONSTANT NUMBER := 1.0;
4405 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
4406 --
4407 l_resource_id number;
4408 l_user_id number;
4409 l_login_user_id number;
4410 l_login_user_status varchar2(30);
4411 l_Error_Msg varchar2(2000);
4412 l_Error_Token varchar2(80);
4413 l_object_version_number number := 1;
4414 --
4415 l_channel_id number;
4416 l_channel_exist_flag varchar2(1);
4417 l_setup_result varchar2(1);
4418 l_update_channel_flag Varchar2(1);
4419 l_keyword_id number;
4420 --
4421 CURSOR C_ChanKeywordId_Seq IS
4422 select amv_c_keywords_s.nextval
4423 from dual;
4424 --
4425 BEGIN
4426 -- Standard begin of API savepoint
4427 SAVEPOINT Set_ChannelKeywords_PVT;
4428 -- Standard call to check for call compatibility.
4429 IF NOT FND_API.Compatible_API_Call (
4430 l_api_version,
4431 p_api_version,
4432 l_api_name,
4433 G_PKG_NAME)
4434 THEN
4435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4436 END IF;
4437 -- Debug Message
4438 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4439 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
4440 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
4441 FND_MSG_PUB.Add;
4442 END IF;
4443 --Initialize message list if p_init_msg_list is TRUE.
4444 IF FND_API.To_Boolean (p_init_msg_list) THEN
4445 FND_MSG_PUB.initialize;
4446 END IF;
4447 -- Get the current (login) user id.
4448 AMV_UTILITY_PVT.Get_UserInfo(
4449 x_resource_id => l_resource_id,
4450 x_user_id => l_user_id,
4451 x_login_id => l_login_user_id,
4452 x_user_status => l_login_user_status
4453 );
4454 -- check login user
4455 IF (p_check_login_user = FND_API.G_TRUE) THEN
4456 -- Check if user is login and has the required privilege.
4457 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
4458 -- User is not login.
4459 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4460 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
4461 FND_MSG_PUB.Add;
4462 END IF;
4463 RAISE FND_API.G_EXC_ERROR;
4464 END IF;
4465 END IF;
4466 -- This fix is for executing api in sqlplus mode
4467 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
4468 l_login_user_id := g_login_user_id;
4469 l_user_id := g_user_id;
4470 l_resource_id := g_resource_id;
4471 END IF;
4472 -- Initialize API return status to sucess
4473 x_return_status := FND_API.G_RET_STS_SUCCESS;
4474
4475 -- Check channel id and status for a given channel id or channel name
4476 Get_ChannelStatus (
4477 x_return_status => x_return_status,
4478 p_channel_id => p_channel_id,
4479 p_channel_name => p_channel_name,
4480 p_category_id => p_category_id,
4481 x_exist_flag => l_channel_exist_flag,
4482 x_channel_id => l_channel_id,
4483 x_error_msg => l_Error_Msg,
4484 x_error_token => l_Error_Token
4485 );
4486 -- change channel keywords if the channel exists
4487 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
4488 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4489 THEN
4490 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
4491 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
4492 FND_MSG_PUB.Add;
4493 END IF;
4494 RAISE FND_API.G_EXC_ERROR;
4495 ELSE
4496 --
4497 AMV_USER_PVT.Can_SetupChannel (
4498 p_api_version => l_api_version,
4499 p_init_msg_list => FND_API.G_FALSE,
4500 p_validation_level => p_validation_level,
4501 x_return_status => x_return_status,
4502 x_msg_count => x_msg_count,
4503 x_msg_data => x_msg_data,
4504 p_check_login_user => FND_API.G_FALSE,
4505 p_resource_id => l_resource_id,
4506 p_include_group_flag => FND_API.G_TRUE,
4507 x_result_flag => l_setup_result
4508 );
4509
4510 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4511 IF (l_setup_result = FND_API.G_TRUE) THEN
4512 l_update_channel_flag := FND_API.G_TRUE;
4513 ELSE
4514 IF (AMV_UTILITY_PVT.Get_UpdateChannelStatus(
4515 l_channel_id,
4516 l_resource_id,
4517 AMV_UTILITY_PVT.G_USER) )
4518 THEN
4519 l_update_channel_flag := FND_API.G_TRUE;
4520 ELSE
4521 -- user does not have privilege to create public channel
4525 FND_MESSAGE.Set_Token('LEVEL','Channel');
4522 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4523 THEN
4524 FND_MESSAGE.Set_Name('AMV', 'AMV_NO_ACCESS_ERROR');
4526 FND_MSG_PUB.Add;
4527 END IF;
4528 RAISE FND_API.G_EXC_ERROR;
4529 END IF;
4530 END IF;
4531 ELSE
4532 -- error while user privilege check in Can_SetupChannel
4533 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4534 THEN
4535 FND_MESSAGE.Set_Name('AMV', 'AMV_SETUP_CHECK_ERROR');
4536 FND_MESSAGE.Set_Token('LEVEL','Channel');
4537 FND_MSG_PUB.Add;
4538 END IF;
4539 RAISE FND_API.G_EXC_ERROR;
4540 END IF;
4541 END IF;
4542 --
4543
4544 -- update channels if user has privilege
4545 IF l_update_channel_flag = FND_API.G_TRUE THEN
4546 -- remove old keywords for the channel
4547 DELETE FROM amv_c_keywords
4548 WHERE channel_id = l_channel_id;
4549
4550 -- set match on flag in channel table
4551 IF p_keywords_array.count > 0 THEN
4552 UPDATE amv_c_channels_b
4553 set match_on_keyword_flag = FND_API.G_TRUE
4554 where channel_id = l_channel_id;
4555 ELSE
4556 UPDATE amv_c_channels_b
4557 set match_on_keyword_flag = FND_API.G_FALSE
4558 where channel_id = l_channel_id;
4559 END IF;
4560
4561 -- set match on all criteria flag
4562 IF Get_MatchOnStatus(l_channel_id) THEN
4563 UPDATE amv_c_channels_b
4564 set match_on_all_criteria_flag = FND_API.G_TRUE
4565 where channel_id = l_channel_id;
4566 ELSE
4567 UPDATE amv_c_channels_b
4568 set match_on_all_criteria_flag = FND_API.G_FALSE
4569 where channel_id = l_channel_id;
4570 END IF;
4571
4572 -- insert new keywords for the channel
4573 FOR i in 1..p_keywords_array.count LOOP
4574 OPEN C_ChanKeywordId_Seq;
4575 FETCH C_ChanKeywordId_Seq INTO l_keyword_id;
4576 CLOSE C_ChanKeywordId_Seq;
4577 INSERT INTO amv_c_keywords
4578 (
4579 channel_keyword_id,
4580 object_version_number,
4581 last_update_date,
4582 last_updated_by,
4583 creation_date,
4584 created_by,
4585 last_update_login,
4586 channel_id,
4587 keyword
4588 )
4589 VALUES (
4590 l_keyword_id,
4591 l_object_version_number,
4592 sysdate,
4593 l_user_id,
4594 sysdate,
4595 l_user_id,
4596 l_login_user_id,
4597 l_channel_id,
4598 initcap(p_keywords_array(i))
4599 );
4600 END LOOP;
4601 END IF;
4602 --
4603
4604 -- Success message
4605 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
4606 THEN
4607 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
4608 FND_MESSAGE.Set_Token('ROW', l_full_name);
4609 FND_MSG_PUB.Add;
4610 END IF;
4611 --Standard check of commit
4612 IF FND_API.To_Boolean ( p_commit ) THEN
4613 COMMIT WORK;
4614 END IF;
4615 -- Debug Message
4616 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4617 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
4618 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
4619 FND_MSG_PUB.Add;
4620 END IF;
4621 --Standard call to get message count and if count=1, get the message
4622 FND_MSG_PUB.Count_And_Get (
4623 p_encoded => FND_API.G_FALSE,
4624 p_count => x_msg_count,
4625 p_data => x_msg_data
4626 );
4627 EXCEPTION
4628 WHEN FND_API.G_EXC_ERROR THEN
4629 ROLLBACK TO Set_ChannelKeywords_PVT;
4630 x_return_status := FND_API.G_RET_STS_ERROR;
4631 -- Standard call to get message count and if count=1, get the message
4632 FND_MSG_PUB.Count_And_Get (
4633 p_encoded => FND_API.G_FALSE,
4634 p_count => x_msg_count,
4635 p_data => x_msg_data
4636 );
4637 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4638 ROLLBACK TO Set_ChannelKeywords_PVT;
4639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4640 -- Standard call to get message count and if count=1, get the message
4641 FND_MSG_PUB.Count_And_Get (
4642 p_encoded => FND_API.G_FALSE,
4643 p_count => x_msg_count,
4644 p_data => x_msg_data
4645 );
4646 WHEN OTHERS THEN
4647 ROLLBACK TO Set_ChannelKeywords_PVT;
4648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4649 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4650 THEN
4651 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4652 END IF;
4653 -- Standard call to get message count and if count=1, get the message
4654 FND_MSG_PUB.Count_And_Get (
4655 p_encoded => FND_API.G_FALSE,
4656 p_count => x_msg_count,
4657 p_data => x_msg_data
4658 );
4659 --
4660 END Set_ChannelKeywords;
4661 --------------------------------------------------------------------------------
4662 -- Start of comments
4663 -- API name : Get_ChannelKeywords
4664 -- Type : Private
4665 -- Pre-reqs : None
4666 -- Function : Get keywords of the content channel.
4667 -- Parameters :
4668 -- IN p_api_version IN NUMBER Required
4672 -- Default = FND_API.G_VALID_LEVEL_FULL
4669 -- p_init_msg_list IN VARCHAR2 Optional
4670 -- Default = FND_API.G_FALSE
4671 -- p_validation_level NUMBER Optional
4673 -- p_channel_id IN NUMBER Optional
4674 -- the channel id. Default = FND_API.G_MISS_NUM
4675 -- p_channel_name IN VARCHAR2 Optional
4676 -- the channel name. Default = FND_API.G_MISS_CHAR
4677 -- Either pass the channe id (preferred) or channel name
4678 -- to identify the channel.
4679 -- p_category_id IN NUMBER Optional
4680 -- the category id. Default = FND_API.G_MISS_NUM
4681 -- OUT : x_return_status OUT VARCHAR2
4682 -- x_msg_count OUT NUMBER
4683 -- x_msg_data OUT VARCHAR2
4684 -- x_keywords_array OUT AMV_CHAR_VARRAY_TYPE
4685 -- Version : Current version 1.0
4686 -- Previous version 1.0
4687 -- Initial version 1.0
4688 -- Notes :
4689 --
4690 -- End of comments
4691 --
4692 PROCEDURE Get_ChannelKeywords
4693 ( p_api_version IN NUMBER,
4694 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4695 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4696 x_return_status OUT NOCOPY VARCHAR2,
4697 x_msg_count OUT NOCOPY NUMBER,
4698 x_msg_data OUT NOCOPY VARCHAR2,
4699 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
4700 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
4701 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
4702 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
4703 x_keywords_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE
4704 )
4705 IS
4706 l_api_name CONSTANT VARCHAR2(30) := 'Get_ChannelKeywords';
4707 l_api_version CONSTANT NUMBER := 1.0;
4708 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
4709 --
4710 l_resource_id number;
4711 l_user_id number;
4712 l_login_user_id number;
4713 l_login_user_status varchar2(30);
4714 l_Error_Msg varchar2(2000);
4715 l_Error_Token varchar2(80);
4716 l_object_version_number number := 1;
4717 --
4718 l_channel_id number;
4719 l_channel_exist_flag varchar2(1);
4720 l_record_count number := 1;
4721 l_keywords varchar2(200);
4722 --
4723 CURSOR Get_Keywords IS
4724 select keyword
4725 from amv_c_keywords
4726 where channel_id = l_channel_id;
4727 --
4728 BEGIN
4729 -- Standard begin of API savepoint
4730 SAVEPOINT Get_ChannelKeywords_PVT;
4731 -- Standard call to check for call compatibility.
4732 IF NOT FND_API.Compatible_API_Call (
4733 l_api_version,
4734 p_api_version,
4735 l_api_name,
4736 G_PKG_NAME)
4737 THEN
4738 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4739 END IF;
4740 -- Debug Message
4741 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4742 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
4743 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
4744 FND_MSG_PUB.Add;
4745 END IF;
4746 --Initialize message list if p_init_msg_list is TRUE.
4747 IF FND_API.To_Boolean (p_init_msg_list) THEN
4748 FND_MSG_PUB.initialize;
4749 END IF;
4750 -- Get the current (login) user id.
4751 AMV_UTILITY_PVT.Get_UserInfo(
4752 x_resource_id => l_resource_id,
4753 x_user_id => l_user_id,
4754 x_login_id => l_login_user_id,
4755 x_user_status => l_login_user_status
4756 );
4757 -- check login user
4758 IF (p_check_login_user = FND_API.G_TRUE) THEN
4759 -- Check if user is login and has the required privilege.
4760 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
4761 -- User is not login.
4762 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4763 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
4764 FND_MSG_PUB.Add;
4765 END IF;
4766 RAISE FND_API.G_EXC_ERROR;
4767 END IF;
4768 END IF;
4769 -- This fix is for executing api in sqlplus mode
4770 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
4771 l_login_user_id := g_login_user_id;
4772 l_user_id := g_user_id;
4773 l_resource_id := g_resource_id;
4774 END IF;
4775 -- Initialize API return status to sucess
4776 x_return_status := FND_API.G_RET_STS_SUCCESS;
4777
4778 -- Check channel id and status for a given channel id or channel name
4779 Get_ChannelStatus (
4780 x_return_status => x_return_status,
4781 p_channel_id => p_channel_id,
4782 p_channel_name => p_channel_name,
4783 p_category_id => p_category_id,
4784 x_exist_flag => l_channel_exist_flag,
4785 x_channel_id => l_channel_id,
4786 x_error_msg => l_Error_Msg,
4787 x_error_token => l_Error_Token
4788 );
4789 -- get channel keywords if the channel exists
4790 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
4791 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4795 FND_MSG_PUB.Add;
4792 THEN
4793 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
4794 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
4796 END IF;
4797 RAISE FND_API.G_EXC_ERROR;
4798 ELSE
4799 OPEN Get_Keywords;
4800 x_keywords_array := AMV_CHAR_VARRAY_TYPE();
4801 LOOP
4802 FETCH Get_Keywords INTO l_keywords;
4803 EXIT WHEN Get_Keywords%NOTFOUND;
4804 x_keywords_array.extend;
4805 x_keywords_array(l_record_count) := l_keywords;
4806 l_record_count := l_record_count + 1;
4807 END LOOP;
4808 CLOSE Get_Keywords;
4809 END IF;
4810 --
4811
4812 -- Debug Message
4813 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4814 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
4815 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
4816 FND_MSG_PUB.Add;
4817 END IF;
4818 --Standard call to get message count and if count=1, get the message
4819 FND_MSG_PUB.Count_And_Get (
4820 p_encoded => FND_API.G_FALSE,
4821 p_count => x_msg_count,
4822 p_data => x_msg_data
4823 );
4824 EXCEPTION
4825 WHEN FND_API.G_EXC_ERROR THEN
4826 ROLLBACK TO Get_ChannelKeywords_PVT;
4827 x_return_status := FND_API.G_RET_STS_ERROR;
4828 -- Standard call to get message count and if count=1, get the message
4829 FND_MSG_PUB.Count_And_Get (
4830 p_encoded => FND_API.G_FALSE,
4831 p_count => x_msg_count,
4832 p_data => x_msg_data
4833 );
4834 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4835 ROLLBACK TO Get_ChannelKeywords_PVT;
4836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4837 -- Standard call to get message count and if count=1, get the message
4838 FND_MSG_PUB.Count_And_Get (
4839 p_encoded => FND_API.G_FALSE,
4840 p_count => x_msg_count,
4841 p_data => x_msg_data
4842 );
4843 WHEN OTHERS THEN
4844 ROLLBACK TO Get_ChannelKeywords_PVT;
4845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4846 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4847 THEN
4848 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4849 END IF;
4850 -- Standard call to get message count and if count=1, get the message
4851 FND_MSG_PUB.Count_And_Get (
4852 p_encoded => FND_API.G_FALSE,
4853 p_count => x_msg_count,
4854 p_data => x_msg_data
4855 );
4856 --
4857 END Get_ChannelKeywords;
4858 --------------------------------------------------------------------------------
4859 -- Start of comments
4860 -- API name : Set_ChannelAuthors
4861 -- Type : Private
4862 -- Pre-reqs : None
4863 -- Function : Change authors of the content channel.
4864 -- Parameters :
4865 -- IN p_api_version IN NUMBER Required
4866 -- p_init_msg_list IN VARCHAR2 Optional
4867 -- Default = FND_API.G_FALSE
4868 -- p_commit IN VARCHAR2 Optional
4869 -- Default = FND_API.G_FALSE
4870 -- p_validation_level NUMBER Optional
4871 -- Default = FND_API.G_VALID_LEVEL_FULL
4872 -- p_channel_id IN NUMBER Optional
4873 -- the channel id. Default = FND_API.G_MISS_NUM
4874 -- p_channel_name IN VARCHAR2 Optional
4875 -- the channel name. Default = FND_API.G_MISS_CHAR
4876 -- Either pass the channe id (preferred) or channel name
4877 -- to identify the channel.
4878 -- p_category_id IN NUMBER Optional
4879 -- the category id. Default = FND_API.G_MISS_NUM
4880 -- p_authors_array IN AMV_CHAR_VARRAY_TYPE
4881 -- OUT : x_return_status OUT VARCHAR2
4882 -- x_msg_count OUT NUMBER
4883 -- x_msg_data OUT VARCHAR2
4884 -- Version : Current version 1.0
4885 -- Previous version 1.0
4886 -- Initial version 1.0
4887 -- Notes :
4888 --
4889 -- End of comments
4890 --
4891 PROCEDURE Set_ChannelAuthors
4892 ( p_api_version IN NUMBER,
4893 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4894 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4895 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4896 x_return_status OUT NOCOPY VARCHAR2,
4897 x_msg_count OUT NOCOPY NUMBER,
4898 x_msg_data OUT NOCOPY VARCHAR2,
4899 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
4900 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
4901 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
4902 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
4903 p_authors_array IN AMV_CHAR_VARRAY_TYPE
4904 )
4905 IS
4906 l_api_name CONSTANT VARCHAR2(30) := 'Set_ChannelAuthors';
4907 l_api_version CONSTANT NUMBER := 1.0;
4908 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
4909 --
4913 l_login_user_status varchar2(30);
4910 l_resource_id number;
4911 l_user_id number;
4912 l_login_user_id number;
4914 l_Error_Msg varchar2(2000);
4915 l_Error_Token varchar2(80);
4916 l_object_version_number number := 1;
4917 --
4918 l_channel_id number;
4919 l_setup_result varchar2(1);
4920 l_update_channel_flag Varchar2(1);
4921 l_channel_exist_flag varchar2(1);
4922 l_author_id number;
4923 --
4924 CURSOR C_ChanAuthorId_Seq IS
4925 select amv_c_authors_s.nextval
4926 from dual;
4927 --
4928 BEGIN
4929 -- Standard begin of API savepoint
4930 SAVEPOINT Set_ChannelAuthors_PVT;
4931 -- Standard call to check for call compatibility.
4932 IF NOT FND_API.Compatible_API_Call (
4933 l_api_version,
4934 p_api_version,
4935 l_api_name,
4936 G_PKG_NAME)
4937 THEN
4938 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4939 END IF;
4940 -- Debug Message
4941 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
4942 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
4943 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
4944 FND_MSG_PUB.Add;
4945 END IF;
4946 --Initialize message list if p_init_msg_list is TRUE.
4947 IF FND_API.To_Boolean (p_init_msg_list) THEN
4948 FND_MSG_PUB.initialize;
4949 END IF;
4950 -- Get the current (login) user id.
4951 AMV_UTILITY_PVT.Get_UserInfo(
4952 x_resource_id => l_resource_id,
4953 x_user_id => l_user_id,
4954 x_login_id => l_login_user_id,
4955 x_user_status => l_login_user_status
4956 );
4957 -- check login user
4958 IF (p_check_login_user = FND_API.G_TRUE) THEN
4959 -- Check if user is login and has the required privilege.
4960 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
4961 -- User is not login.
4962 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4963 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
4964 FND_MSG_PUB.Add;
4965 END IF;
4966 RAISE FND_API.G_EXC_ERROR;
4967 END IF;
4968 END IF;
4969 -- This fix is for executing api in sqlplus mode
4970 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
4971 l_login_user_id := g_login_user_id;
4972 l_user_id := g_user_id;
4973 l_resource_id := g_resource_id;
4974 END IF;
4975 -- Initialize API return status to sucess
4976 x_return_status := FND_API.G_RET_STS_SUCCESS;
4977
4978 -- Check channel id and status for a given channel id or channel name
4979 Get_ChannelStatus (
4980 x_return_status => x_return_status,
4981 p_channel_id => p_channel_id,
4982 p_channel_name => p_channel_name,
4983 p_category_id => p_category_id,
4984 x_exist_flag => l_channel_exist_flag,
4985 x_channel_id => l_channel_id,
4986 x_error_msg => l_Error_Msg,
4987 x_error_token => l_Error_Token
4988 );
4989 -- change authors if the channel exists
4990 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
4991 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4992 THEN
4993 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
4994 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
4995 FND_MSG_PUB.Add;
4996 END IF;
4997 RAISE FND_API.G_EXC_ERROR;
4998 ELSE
4999 --
5000 AMV_USER_PVT.Can_SetupChannel (
5001 p_api_version => l_api_version,
5002 p_init_msg_list => FND_API.G_FALSE,
5003 p_validation_level => p_validation_level,
5004 x_return_status => x_return_status,
5005 x_msg_count => x_msg_count,
5006 x_msg_data => x_msg_data,
5007 p_check_login_user => FND_API.G_FALSE,
5008 p_resource_id => l_resource_id,
5009 p_include_group_flag => FND_API.G_TRUE,
5010 x_result_flag => l_setup_result
5011 );
5012
5013 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
5014 IF (l_setup_result = FND_API.G_TRUE) THEN
5015 l_update_channel_flag := FND_API.G_TRUE;
5016 ELSE
5017 IF (AMV_UTILITY_PVT.Get_UpdateChannelStatus(
5018 l_channel_id,
5019 l_resource_id,
5020 AMV_UTILITY_PVT.G_USER) )
5021 THEN
5022 l_update_channel_flag := FND_API.G_TRUE;
5023 ELSE
5024 -- user does not have privilege to create public channel
5025 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
5026 THEN
5027 FND_MESSAGE.Set_Name('AMV', 'AMV_NO_ACCESS_ERROR');
5028 FND_MESSAGE.Set_Token('LEVEL','Channel');
5029 FND_MSG_PUB.Add;
5030 END IF;
5031 RAISE FND_API.G_EXC_ERROR;
5032 END IF;
5033 END IF;
5034 ELSE
5035 -- error while user privilege check in Can_SetupChannel
5036 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
5037 THEN
5038 FND_MESSAGE.Set_Name('AMV', 'AMV_SETUP_CHECK_ERROR');
5039 FND_MESSAGE.Set_Token('LEVEL','Channel');
5040 FND_MSG_PUB.Add;
5041 END IF;
5042 RAISE FND_API.G_EXC_ERROR;
5043 END IF;
5044 END IF;
5045 --
5046
5047 -- update channels if user has privilege
5048 IF l_update_channel_flag = FND_API.G_TRUE THEN
5049 -- remove old authors for the channel
5053 -- set match on flag in channel table
5050 DELETE FROM amv_c_authors
5051 WHERE channel_id = l_channel_id;
5052
5054 IF p_authors_array.count > 0 THEN
5055 UPDATE amv_c_channels_b
5056 set match_on_author_flag = FND_API.G_TRUE
5057 where channel_id = l_channel_id;
5058 ELSE
5059 UPDATE amv_c_channels_b
5060 set match_on_author_flag = FND_API.G_FALSE
5061 where channel_id = l_channel_id;
5062 END IF;
5063
5064 -- set match on all criteria flag
5065 IF Get_MatchOnStatus(l_channel_id) THEN
5066 UPDATE amv_c_channels_b
5067 set match_on_all_criteria_flag = FND_API.G_TRUE
5068 where channel_id = l_channel_id;
5069 ELSE
5070 UPDATE amv_c_channels_b
5071 set match_on_all_criteria_flag = FND_API.G_FALSE
5072 where channel_id = l_channel_id;
5073 END IF;
5074
5075 -- insert the new authors for the channel
5076 FOR i in 1..p_authors_array.count LOOP
5077 OPEN C_ChanAuthorId_Seq;
5078 FETCH C_ChanAuthorId_Seq INTO l_author_id;
5079 CLOSE C_ChanAuthorId_Seq;
5080 INSERT INTO amv_c_authors
5081 (
5082 channel_author_id,
5083 object_version_number,
5084 last_update_date,
5085 last_updated_by,
5086 creation_date,
5087 created_by,
5088 last_update_login,
5089 channel_id,
5090 author
5091 )
5092 VALUES (
5093 l_author_id,
5094 l_object_version_number,
5095 sysdate,
5096 l_user_id,
5097 sysdate,
5098 l_user_id,
5099 l_login_user_id,
5100 l_channel_id,
5101 initcap(p_authors_array(i))
5102 );
5103 END LOOP;
5104 END IF;
5105 --
5106
5107 -- Success message
5108 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
5109 THEN
5110 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
5111 FND_MESSAGE.Set_Token('ROW', l_full_name);
5112 FND_MSG_PUB.Add;
5113 END IF;
5114 --Standard check of commit
5115 IF FND_API.To_Boolean ( p_commit ) THEN
5116 COMMIT WORK;
5117 END IF;
5118 -- Debug Message
5119 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
5120 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
5121 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
5122 FND_MSG_PUB.Add;
5123 END IF;
5124 --Standard call to get message count and if count=1, get the message
5125 FND_MSG_PUB.Count_And_Get (
5126 p_encoded => FND_API.G_FALSE,
5127 p_count => x_msg_count,
5128 p_data => x_msg_data
5129 );
5130 EXCEPTION
5131 WHEN FND_API.G_EXC_ERROR THEN
5132 ROLLBACK TO Set_ChannelAuthors_PVT;
5133 x_return_status := FND_API.G_RET_STS_ERROR;
5134 -- Standard call to get message count and if count=1, get the message
5135 FND_MSG_PUB.Count_And_Get (
5136 p_encoded => FND_API.G_FALSE,
5137 p_count => x_msg_count,
5138 p_data => x_msg_data
5139 );
5140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5141 ROLLBACK TO Set_ChannelAuthors_PVT;
5142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5143 -- Standard call to get message count and if count=1, get the message
5144 FND_MSG_PUB.Count_And_Get (
5145 p_encoded => FND_API.G_FALSE,
5146 p_count => x_msg_count,
5147 p_data => x_msg_data
5148 );
5149 WHEN OTHERS THEN
5150 ROLLBACK TO Set_ChannelAuthors_PVT;
5151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5152 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5153 THEN
5154 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5155 END IF;
5156 -- Standard call to get message count and if count=1, get the message
5157 FND_MSG_PUB.Count_And_Get (
5158 p_encoded => FND_API.G_FALSE,
5159 p_count => x_msg_count,
5160 p_data => x_msg_data
5161 );
5162 --
5163 END Set_ChannelAuthors;
5164 --------------------------------------------------------------------------------
5165 -- Start of comments
5166 -- API name : Get_ChannelAuthors
5167 -- Type : Private
5168 -- Pre-reqs : None
5169 -- Function : Get authors of the content channel.
5170 -- Parameters :
5171 -- IN p_api_version IN NUMBER Required
5172 -- p_init_msg_list IN VARCHAR2 Optional
5173 -- Default = FND_API.G_FALSE
5174 -- p_validation_level NUMBER Optional
5175 -- Default = FND_API.G_VALID_LEVEL_FULL
5176 -- p_channel_id IN NUMBER Optional
5177 -- the channel id. Default = FND_API.G_MISS_NUM
5178 -- p_channel_name IN VARCHAR2 Optional
5179 -- the channel name. Default = FND_API.G_MISS_CHAR
5180 -- Either pass the channe id (preferred) or channel name
5181 -- to identify the channel.
5182 -- p_category_id IN NUMBER Optional
5183 -- the category id. Default = FND_API.G_MISS_NUM
5184 -- OUT : x_return_status OUT VARCHAR2
5185 -- x_msg_count OUT NUMBER
5189 -- Previous version 1.0
5186 -- x_msg_data OUT VARCHAR2
5187 -- x_authors_array OUT AMV_CHAR_VARRAY_TYPE
5188 -- Version : Current version 1.0
5190 -- Initial version 1.0
5191 -- Notes :
5192 --
5193 -- End of comments
5194 --
5195 PROCEDURE Get_ChannelAuthors
5196 ( p_api_version IN NUMBER,
5197 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5198 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5199 x_return_status OUT NOCOPY VARCHAR2,
5200 x_msg_count OUT NOCOPY NUMBER,
5201 x_msg_data OUT NOCOPY VARCHAR2,
5202 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
5203 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
5204 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5205 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5206 x_authors_array OUT NOCOPY AMV_CHAR_VARRAY_TYPE
5207 )
5208 IS
5209 l_api_name CONSTANT VARCHAR2(30) := 'Get_ChannelAuthors';
5210 l_api_version CONSTANT NUMBER := 1.0;
5211 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
5212 --
5213 l_resource_id number;
5214 l_user_id number;
5215 l_login_user_id number;
5216 l_login_user_status varchar2(30);
5217 l_Error_Msg varchar2(2000);
5218 l_Error_Token varchar2(80);
5219 --
5220 l_channel_id number;
5221 l_channel_exist_flag varchar2(1);
5222 l_record_count number := 1;
5223 l_authors varchar2(200);
5224 --
5225 CURSOR Get_Authors IS
5226 select author
5227 from amv_c_authors
5228 where channel_id = l_channel_id;
5229 --
5230 BEGIN
5231 -- Standard begin of API savepoint
5232 SAVEPOINT Get_ChannelAuthors_PVT;
5233 -- Standard call to check for call compatibility.
5234 IF NOT FND_API.Compatible_API_Call (
5235 l_api_version,
5236 p_api_version,
5237 l_api_name,
5238 G_PKG_NAME)
5239 THEN
5240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5241 END IF;
5242 -- Debug Message
5243 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
5244 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
5245 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
5246 FND_MSG_PUB.Add;
5247 END IF;
5248 --Initialize message list if p_init_msg_list is TRUE.
5249 IF FND_API.To_Boolean (p_init_msg_list) THEN
5250 FND_MSG_PUB.initialize;
5251 END IF;
5252 -- Get the current (login) user id.
5253 AMV_UTILITY_PVT.Get_UserInfo(
5254 x_resource_id => l_resource_id,
5255 x_user_id => l_user_id,
5256 x_login_id => l_login_user_id,
5257 x_user_status => l_login_user_status
5258 );
5259 -- check login user
5260 IF (p_check_login_user = FND_API.G_TRUE) THEN
5261 -- Check if user is login and has the required privilege.
5262 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
5263 -- User is not login.
5264 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5265 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
5266 FND_MSG_PUB.Add;
5267 END IF;
5268 RAISE FND_API.G_EXC_ERROR;
5269 END IF;
5270 END IF;
5271 -- This fix is for executing api in sqlplus mode
5272 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
5273 l_login_user_id := g_login_user_id;
5274 l_user_id := g_user_id;
5275 l_resource_id := g_resource_id;
5276 END IF;
5277 -- Initialize API return status to sucess
5278 x_return_status := FND_API.G_RET_STS_SUCCESS;
5279
5280 -- Check channel id and status for a given channel id or channel name
5281 Get_ChannelStatus (
5282 x_return_status => x_return_status,
5283 p_channel_id => p_channel_id,
5284 p_channel_name => p_channel_name,
5285 p_category_id => p_category_id,
5286 x_exist_flag => l_channel_exist_flag,
5287 x_channel_id => l_channel_id,
5288 x_error_msg => l_Error_Msg,
5289 x_error_token => l_Error_Token
5290 );
5291 -- get channel authors if the channel exists
5292 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
5293 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
5294 THEN
5295 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
5296 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
5297 FND_MSG_PUB.Add;
5298 END IF;
5299 RAISE FND_API.G_EXC_ERROR;
5300 ELSE
5301 OPEN Get_Authors;
5302 x_authors_array := AMV_CHAR_VARRAY_TYPE();
5303 LOOP
5304 FETCH Get_Authors INTO l_authors;
5305 EXIT WHEN Get_Authors%NOTFOUND;
5306 x_authors_array.extend;
5307 x_authors_array(l_record_count) := l_authors;
5308 l_record_count := l_record_count + 1;
5309 END LOOP;
5310 CLOSE Get_Authors;
5311 END IF;
5312 --
5313
5314 -- Debug Message
5315 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
5316 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
5317 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
5318 FND_MSG_PUB.Add;
5319 END IF;
5323 p_count => x_msg_count,
5320 --Standard call to get message count and if count=1, get the message
5321 FND_MSG_PUB.Count_And_Get (
5322 p_encoded => FND_API.G_FALSE,
5324 p_data => x_msg_data
5325 );
5326 EXCEPTION
5327 WHEN FND_API.G_EXC_ERROR THEN
5328 ROLLBACK TO Get_ChannelAuthors_PVT;
5329 x_return_status := FND_API.G_RET_STS_ERROR;
5330 -- Standard call to get message count and if count=1, get the message
5331 FND_MSG_PUB.Count_And_Get (
5332 p_encoded => FND_API.G_FALSE,
5333 p_count => x_msg_count,
5334 p_data => x_msg_data
5335 );
5336 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5337 ROLLBACK TO Get_ChannelAuthors_PVT;
5338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5339 -- Standard call to get message count and if count=1, get the message
5340 FND_MSG_PUB.Count_And_Get (
5341 p_encoded => FND_API.G_FALSE,
5342 p_count => x_msg_count,
5343 p_data => x_msg_data
5344 );
5345 WHEN OTHERS THEN
5346 ROLLBACK TO Get_ChannelAuthors_PVT;
5347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5348 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5349 THEN
5350 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5351 END IF;
5352 -- Standard call to get message count and if count=1, get the message
5353 FND_MSG_PUB.Count_And_Get (
5354 p_encoded => FND_API.G_FALSE,
5355 p_count => x_msg_count,
5356 p_data => x_msg_data
5357 );
5358 --
5359 END Get_ChannelAuthors;
5360 --------------------------------------------------------------------------------
5361 -- Start of comments
5362 -- API name : Get_ItemsPerChannel
5363 -- Type : Private
5364 -- Pre-reqs : None
5365 -- Function : Get all documents matching the content channel given by
5366 -- channel id (preferred) or channel name.
5367 -- Parameters :
5368 -- IN p_api_version IN NUMBER Required
5369 -- p_init_msg_list IN VARCHAR2 Optional
5370 -- Default = FND_API.G_FALSE
5371 -- p_validation_level NUMBER Optional
5372 -- Default = FND_API.G_VALID_LEVEL_FULL
5373 -- p_channel_id IN NUMBER Optional
5374 -- the channel id. Default = FND_API.G_MISS_NUM
5375 -- p_channel_name IN VARCHAR2 Optional
5376 -- the channel name. Default = FND_API.G_MISS_CHAR
5377 -- Either pass the channe id (preferred) or channel name
5378 -- to identify the channel.
5379 -- p_category_id IN NUMBER Optional
5380 -- the category id. Default = FND_API.G_MISS_NUM
5381 -- p_subset_request_rec IN AMV_REQUEST_OBJ_TYPE
5382 -- Required
5383 -- OUT : x_return_status OUT VARCHAR2
5384 -- x_msg_count OUT NUMBER
5385 -- x_msg_data OUT VARCHAR2
5386 -- x_subset_return_rec OUT AMV_RETURN_OBJ_TYPE
5387 -- x_document_id_array OUT AMV_NUMBER_VARRAY_TYPE
5388 -- Version : Current version 1.0
5389 -- Previous version 1.0
5390 -- Initial version 1.0
5391 -- Notes :
5392 --
5393 -- End of comments
5394 --
5395 PROCEDURE Get_ItemsPerChannel
5396 ( p_api_version IN NUMBER,
5397 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5398 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5399 x_return_status OUT NOCOPY VARCHAR2,
5400 x_msg_count OUT NOCOPY NUMBER,
5401 x_msg_data OUT NOCOPY VARCHAR2,
5402 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
5403 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
5404 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5405 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5406 p_item_status IN VARCHAR2 := AMV_UTILITY_PVT.G_APPROVED,
5407 p_subset_request_rec IN AMV_REQUEST_OBJ_TYPE,
5408 x_subset_return_rec OUT NOCOPY AMV_RETURN_OBJ_TYPE,
5409 x_document_id_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE
5410 )
5411 IS
5412 l_api_name CONSTANT VARCHAR2(30) := 'Get_ItemsPerChannel';
5413 l_api_version CONSTANT NUMBER := 1.0;
5414 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
5415 --
5416 l_resource_id number;
5417 l_user_id number;
5418 l_login_user_id number;
5419 l_login_user_status varchar2(30);
5420 l_Error_Msg varchar2(2000);
5421 l_Error_Token varchar2(80);
5422 --
5423 l_channel_id NUMBER;
5424 l_channel_exist_flag varchar2(1);
5425 l_document_id NUMBER;
5426 l_itm_count NUMBER := 1;
5427 l_fetch_count NUMBER := 0;
5428 l_start_with NUMBER := 1;
5429 l_returned_record_count NUMBER;
5430 l_next_record_position NUMBER;
5431 l_total_record_count NUMBER;
5432 --
5433 CURSOR C_PendingItmCount IS
5434 select count(cim.item_id)
5435 from amv_c_chl_item_match cim
5436 , jtf_amv_items_vl ib
5437 where cim.channel_id = p_channel_id
5441 and cim.item_id = ib.item_id;
5438 and cim.approval_status_type = p_item_status
5439 and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
5440 and cim.available_for_channel_date <= sysdate
5442
5443 CURSOR Get_PendingItems IS
5444 select ib.item_id
5445 from amv_c_chl_item_match cim
5446 , jtf_amv_items_vl ib
5447 where cim.channel_id = p_channel_id
5448 and cim.approval_status_type = p_item_status
5449 and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
5450 and cim.item_id = ib.item_id
5451 order by ib.effective_start_date;
5452
5453 CURSOR C_TotalItmCount IS
5454 select count(cim.item_id)
5455 from amv_c_chl_item_match cim
5456 , jtf_amv_items_vl ib
5457 where cim.channel_id = p_channel_id
5458 and cim.approval_status_type = p_item_status
5459 and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
5460 and cim.available_for_channel_date <= sysdate
5461 and cim.item_id = ib.item_id
5462 and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
5463 and nvl(ib.expiration_date, sysdate) >= sysdate;
5464
5465 CURSOR Get_Items IS
5466 select ib.item_id
5467 from amv_c_chl_item_match cim
5468 , jtf_amv_items_vl ib
5469 where cim.channel_id = p_channel_id
5470 and cim.approval_status_type = p_item_status
5471 and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
5472 and cim.available_for_channel_date <= sysdate
5473 and cim.item_id = ib.item_id
5474 and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
5475 and nvl(ib.expiration_date, sysdate) >= sysdate
5476 order by ib.effective_start_date desc;
5477 --
5478 BEGIN
5479 -- Standard begin of API savepoint
5480 SAVEPOINT Get_ItemsPerChannel_PVT;
5481 -- Standard call to check for call compatibility.
5482 IF NOT FND_API.Compatible_API_Call (
5483 l_api_version,
5484 p_api_version,
5485 l_api_name,
5486 G_PKG_NAME)
5487 THEN
5488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5489 END IF;
5490 -- Debug Message
5491 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
5492 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
5493 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
5494 FND_MSG_PUB.Add;
5495 END IF;
5496 --Initialize message list if p_init_msg_list is TRUE.
5497 IF FND_API.To_Boolean (p_init_msg_list) THEN
5498 FND_MSG_PUB.initialize;
5499 END IF;
5500 -- Get the current (login) user id.
5501 AMV_UTILITY_PVT.Get_UserInfo(
5502 x_resource_id => l_resource_id,
5503 x_user_id => l_user_id,
5504 x_login_id => l_login_user_id,
5505 x_user_status => l_login_user_status
5506 );
5507 -- check login user
5508 IF (p_check_login_user = FND_API.G_TRUE) THEN
5509 -- Check if user is login and has the required privilege.
5510 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
5511 -- User is not login.
5512 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5513 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
5514 FND_MSG_PUB.Add;
5515 END IF;
5516 RAISE FND_API.G_EXC_ERROR;
5517 END IF;
5518 END IF;
5519 -- This fix is for executing api in sqlplus mode
5520 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
5521 l_login_user_id := g_login_user_id;
5522 l_user_id := g_user_id;
5523 l_resource_id := g_resource_id;
5524 END IF;
5525 -- Initialize API return status to sucess
5526 x_return_status := FND_API.G_RET_STS_SUCCESS;
5527
5528 IF (p_channel_id IS NULL OR p_channel_name IS NULL) THEN
5529 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
5530 THEN
5531 FND_MESSAGE.Set_Name('AMV', 'AMV_CHN_ID_OR_NAME_NULL');
5532 FND_MSG_PUB.Add;
5533 END IF;
5534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5535 END IF;
5536 --
5537 -- Check channel id and status for a given channel id or channel name
5538 Get_ChannelStatus (
5539 x_return_status => x_return_status,
5540 p_channel_id => p_channel_id,
5541 p_channel_name => p_channel_name,
5542 p_category_id => p_category_id,
5543 x_exist_flag => l_channel_exist_flag,
5544 x_channel_id => l_channel_id,
5545 x_error_msg => l_Error_Msg,
5546 x_error_token => l_Error_Token);
5547 -- get items if channel exists
5548 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
5549 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
5550 THEN
5551 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
5552 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
5553 FND_MSG_PUB.Add;
5554 END IF;
5555 RAISE FND_API.G_EXC_ERROR;
5556 ELSE
5557 x_return_status := FND_API.G_RET_STS_SUCCESS;
5558 --get the total items count:
5559 IF (p_subset_request_rec.return_total_count_flag = FND_API.G_TRUE) THEN
5560 IF p_item_status = AMV_UTILITY_PVT.G_APPROVED THEN
5561 OPEN C_TotalItmCount;
5562 FETCH C_TotalItmCount INTO l_total_record_count;
5563 CLOSE C_TotalItmCount;
5564 ELSE
5565 OPEN C_PendingItmCount;
5566 FETCH C_PendingItmCount INTO l_total_record_count;
5567 CLOSE C_PendingItmCount;
5568 END IF;
5572 l_start_with := p_subset_request_rec.start_record_position;
5569 END IF;
5570 -- set the starting position for return record
5571 IF (p_subset_request_rec.start_record_position IS NOT NULL) THEN
5573 END IF;
5574 -- fetch approved items
5575 IF p_item_status = AMV_UTILITY_PVT.G_APPROVED THEN
5576 OPEN Get_Items;
5577 x_document_id_array := AMV_NUMBER_VARRAY_TYPE();
5578 LOOP
5579 FETCH Get_Items INTO l_document_id;
5580 EXIT WHEN Get_Items%NOTFOUND;
5581 IF (l_start_with <= l_itm_count AND
5582 l_fetch_count < p_subset_request_rec.records_requested) THEN
5583 l_fetch_count := l_fetch_count + 1;
5584 x_document_id_array.extend;
5585 x_document_id_array(l_fetch_count) := l_document_id;
5586 END IF;
5587 IF (l_fetch_count >= p_subset_request_rec.records_requested) THEN
5588 exit;
5589 END IF;
5590 l_itm_count := l_itm_count + 1;
5591 END LOOP;
5592 CLOSE Get_Items;
5593 ELSE
5594 OPEN Get_PendingItems;
5595 x_document_id_array := AMV_NUMBER_VARRAY_TYPE();
5596 LOOP
5597 FETCH Get_PendingItems INTO l_document_id;
5598 EXIT WHEN Get_PendingItems%NOTFOUND;
5599 IF (l_start_with <= l_itm_count AND
5600 l_fetch_count < p_subset_request_rec.records_requested) THEN
5601 l_fetch_count := l_fetch_count + 1;
5602 x_document_id_array.extend;
5603 x_document_id_array(l_fetch_count) := l_document_id;
5604 END IF;
5605 IF (l_fetch_count >= p_subset_request_rec.records_requested) THEN
5606 exit;
5607 END IF;
5608 l_itm_count := l_itm_count + 1;
5609 END LOOP;
5610 CLOSE Get_PendingItems;
5611 END IF;
5612 l_returned_record_count := l_fetch_count;
5613 l_next_record_position := l_start_with + l_fetch_count;
5614 x_subset_return_rec.returned_record_count := l_returned_record_count;
5615 x_subset_return_rec.next_record_position := l_next_record_position;
5616 x_subset_return_rec.total_record_count := l_total_record_count;
5617 /*
5618 x_subset_return_rec := amv_return_obj_type(
5619 l_returned_record_count,
5620 l_next_record_position,
5621 l_total_record_count);
5622 */
5623 END IF;
5624 --
5625
5626 -- Debug Message
5627 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
5628 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
5629 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
5630 FND_MSG_PUB.Add;
5631 END IF;
5632 --Standard call to get message count and if count=1, get the message
5633 FND_MSG_PUB.Count_And_Get (
5634 p_encoded => FND_API.G_FALSE,
5635 p_count => x_msg_count,
5636 p_data => x_msg_data
5637 );
5638 EXCEPTION
5639 WHEN FND_API.G_EXC_ERROR THEN
5640 ROLLBACK TO Get_ItemsPerChannel_PVT;
5641 x_return_status := FND_API.G_RET_STS_ERROR;
5642 -- Standard call to get message count and if count=1, get the message
5643 FND_MSG_PUB.Count_And_Get (
5644 p_encoded => FND_API.G_FALSE,
5645 p_count => x_msg_count,
5646 p_data => x_msg_data
5647 );
5648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5649 ROLLBACK TO Get_ItemsPerChannel_PVT;
5650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5651 -- Standard call to get message count and if count=1, get the message
5652 FND_MSG_PUB.Count_And_Get (
5653 p_encoded => FND_API.G_FALSE,
5654 p_count => x_msg_count,
5655 p_data => x_msg_data
5656 );
5657 WHEN OTHERS THEN
5658 ROLLBACK TO Get_ItemsPerChannel_PVT;
5659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5660 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5661 THEN
5662 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5663 END IF;
5664 -- Standard call to get message count and if count=1, get the message
5665 FND_MSG_PUB.Count_And_Get (
5666 p_encoded => FND_API.G_FALSE,
5667 p_count => x_msg_count,
5668 p_data => x_msg_data
5669 );
5670 --
5671 END Get_ItemsPerChannel;
5672 --------------------------------------------------------------------------------
5673 -- Start of comments
5674 -- API name : Set_ChannelApprovalStatus
5675 -- Type : Private
5676 -- Pre-reqs : None
5677 -- Function : Set the approval status (approve, reject, need more info.)
5678 -- given a channel and a document.
5679 -- Parameters :
5680 -- IN p_api_version IN NUMBER Required
5681 -- p_init_msg_list IN VARCHAR2 Optional
5682 -- Default = FND_API.G_FALSE
5683 -- p_commit IN VARCHAR2 Optional
5684 -- Default = FND_API.G_FALSE
5685 -- p_validation_level NUMBER Optional
5686 -- Default = FND_API.G_VALID_LEVEL_FULL
5687 -- p_channel_id IN NUMBER Optional
5688 -- the channel id. Default = FND_API.G_MISS_NUM
5689 -- p_channel_name IN VARCHAR2 Optional
5693 -- p_category_id IN NUMBER Optional
5690 -- the channel name. Default = FND_API.G_MISS_CHAR
5691 -- Either pass the channe id (preferred) or channel name
5692 -- to identify the channel.
5694 -- the category id. Default = FND_API.G_MISS_NUM
5695 -- p_item_id IN NUMBER Required
5696 -- p_approval_status IN NUMBER Required
5697 -- OUT : x_return_status OUT VARCHAR2
5698 -- x_msg_count OUT NUMBER
5699 -- x_msg_data OUT VARCHAR2
5700 -- Version : Current version 1.0
5701 -- Previous version 1.0
5702 -- Initial version 1.0
5703 -- Notes :
5704 --
5705 -- End of comments
5706 --
5707 PROCEDURE Set_ChannelApprovalStatus
5708 ( p_api_version IN NUMBER,
5709 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5710 p_commit IN VARCHAR2 := FND_API.G_FALSE,
5711 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5712 x_return_status OUT NOCOPY VARCHAR2,
5713 x_msg_count OUT NOCOPY NUMBER,
5714 x_msg_data OUT NOCOPY VARCHAR2,
5715 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
5716 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
5717 p_channel_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5718 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
5719 p_item_id IN NUMBER,
5720 p_approval_status IN VARCHAR2
5721 )
5722 IS
5723 l_api_name CONSTANT VARCHAR2(30) := 'Set_ChannelApprovalStatus';
5724 l_api_version CONSTANT NUMBER := 1.0;
5725 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
5726 --
5727 l_resource_id number;
5728 l_user_id number;
5729 l_login_user_id number;
5730 l_login_user_status varchar2(30);
5731 l_Error_Msg varchar2(2000);
5732 l_Error_Token varchar2(80);
5733 --
5734 l_channel_id number;
5735 l_channel_exist_flag varchar2(1);
5736 --
5737 BEGIN
5738 -- Standard begin of API savepoint
5739 SAVEPOINT Set_ChannelApprovalStatus_PVT;
5740 -- Standard call to check for call compatibility.
5741 IF NOT FND_API.Compatible_API_Call (
5742 l_api_version,
5743 p_api_version,
5744 l_api_name,
5745 G_PKG_NAME)
5746 THEN
5747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5748 END IF;
5749 -- Debug Message
5750 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
5751 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
5752 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
5753 FND_MSG_PUB.Add;
5754 END IF;
5755 --Initialize message list if p_init_msg_list is TRUE.
5756 IF FND_API.To_Boolean (p_init_msg_list) THEN
5757 FND_MSG_PUB.initialize;
5758 END IF;
5759 -- Get the current (login) user id.
5760 AMV_UTILITY_PVT.Get_UserInfo(
5761 x_resource_id => l_resource_id,
5762 x_user_id => l_user_id,
5763 x_login_id => l_login_user_id,
5764 x_user_status => l_login_user_status
5765 );
5766 -- check login user
5767 IF (p_check_login_user = FND_API.G_TRUE) THEN
5768 -- Check if user is login and has the required privilege.
5769 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
5770 -- User is not login.
5771 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5772 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
5773 FND_MSG_PUB.Add;
5774 END IF;
5775 RAISE FND_API.G_EXC_ERROR;
5776 END IF;
5777 END IF;
5778 -- This fix is for executing api in sqlplus mode
5779 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
5780 l_login_user_id := g_login_user_id;
5781 l_user_id := g_user_id;
5782 l_resource_id := g_resource_id;
5783 END IF;
5784 -- Initialize API return status to sucess
5785 x_return_status := FND_API.G_RET_STS_SUCCESS;
5786
5787 -- Check channel id and status for a given channel id or channel name
5788 Get_ChannelStatus (
5789 x_return_status => x_return_status,
5790 p_channel_id => p_channel_id,
5791 p_channel_name => p_channel_name,
5792 p_category_id => p_category_id,
5793 x_exist_flag => l_channel_exist_flag,
5794 x_channel_id => l_channel_id,
5795 x_error_msg => l_Error_Msg,
5796 x_error_token => l_Error_Token
5797 );
5798 -- set channel approval status if it exists
5799 IF (l_channel_exist_flag = FND_API.G_FALSE) THEN
5800 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
5801 THEN
5802 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
5803 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
5804 FND_MSG_PUB.Add;
5805 END IF;
5806 RAISE FND_API.G_EXC_ERROR;
5807 ELSE
5808 UPDATE amv_c_chl_item_match
5809 SET approval_status_type = p_approval_status
5810 , approval_date = sysdate
5811 , last_update_date = sysdate
5812 , last_updated_by = l_user_id
5813 , last_update_login = l_login_user_id
5814 WHERE channel_id = l_channel_id
5818
5815 AND item_id = p_item_id;
5816 END IF;
5817 --
5819 -- Success message
5820 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
5821 THEN
5822 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
5823 FND_MESSAGE.Set_Token('ROW', l_full_name);
5824 FND_MSG_PUB.Add;
5825 END IF;
5826 --Standard check of commit
5827 IF FND_API.To_Boolean ( p_commit ) THEN
5828 COMMIT WORK;
5829 END IF;
5830 -- Debug Message
5831 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
5832 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
5833 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
5834 FND_MSG_PUB.Add;
5835 END IF;
5836 --Standard call to get message count and if count=1, get the message
5837 FND_MSG_PUB.Count_And_Get (
5838 p_encoded => FND_API.G_FALSE,
5839 p_count => x_msg_count,
5840 p_data => x_msg_data
5841 );
5842 EXCEPTION
5843 WHEN FND_API.G_EXC_ERROR THEN
5844 ROLLBACK TO Set_ChannelApprovalStatus_PVT;
5845 x_return_status := FND_API.G_RET_STS_ERROR;
5846 -- Standard call to get message count and if count=1, get the message
5847 FND_MSG_PUB.Count_And_Get (
5848 p_encoded => FND_API.G_FALSE,
5849 p_count => x_msg_count,
5850 p_data => x_msg_data
5851 );
5852 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5853 ROLLBACK TO Set_ChannelApprovalStatus_PVT;
5854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5855 -- Standard call to get message count and if count=1, get the message
5856 FND_MSG_PUB.Count_And_Get (
5857 p_encoded => FND_API.G_FALSE,
5858 p_count => x_msg_count,
5859 p_data => x_msg_data
5860 );
5861 WHEN OTHERS THEN
5862 ROLLBACK TO Set_ChannelApprovalStatus_PVT;
5863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5864 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5865 THEN
5866 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5867 END IF;
5868 -- Standard call to get message count and if count=1, get the message
5869 FND_MSG_PUB.Count_And_Get (
5870 p_encoded => FND_API.G_FALSE,
5871 p_count => x_msg_count,
5872 p_data => x_msg_data
5873 );
5874 --
5875 END Set_ChannelApprovalStatus;
5876 --------------------------------------------------------------------------------
5877 -- Start of comments
5878 -- API name : Find_Channels
5879 -- Type : Private
5880 -- Pre-reqs : None
5881 -- Function : Get all documents matching the input criteria.
5882 -- Parameters :
5883 -- IN p_api_version IN NUMBER Required
5884 -- p_init_msg_list IN VARCHAR2 Optional
5885 -- Default = FND_API.G_FALSE
5886 -- p_validation_level NUMBER Optional
5887 -- Default = FND_API.G_VALID_LEVEL_FULL
5888 -- p_criteria_rec IN
5889 -- AMV_CHANNEL_OBJ_TYPE Required
5890 -- p_sort_by IN VARCHAR2 Optional
5891 -- Default = FND_API.G_MISS_CHAR
5892 -- p_subset_request_rec IN AMV_REQUEST_OBJ_TYPE
5893 -- Required
5894 -- OUT : x_return_status OUT VARCHAR2
5895 -- x_msg_count OUT NUMBER
5896 -- x_msg_data OUT VARCHAR2
5897 -- x_subset_return_rec OUT AMV_RETURN_OBJ_TYPE
5898 -- x_content_chan_array OUT AMV_CHANNEL_VARRAY_TYPE
5899 -- Version : Current version 1.0
5900 -- Previous version 1.0
5901 -- Initial version 1.0
5902 -- Notes :
5903 --
5904 -- End of comments
5905 --
5906 PROCEDURE Find_Channels
5907 ( p_api_version IN NUMBER,
5908 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5909 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5910 x_return_status OUT NOCOPY VARCHAR2,
5911 x_msg_count OUT NOCOPY NUMBER,
5912 x_msg_data OUT NOCOPY VARCHAR2,
5913 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
5914 p_criteria_rec IN AMV_CHANNEL_OBJ_TYPE,
5915 p_sort_by IN VARCHAR2 := FND_API.G_MISS_CHAR,
5916 p_subset_request_rec IN AMV_REQUEST_OBJ_TYPE,
5917 x_subset_return_rec OUT NOCOPY AMV_RETURN_OBJ_TYPE,
5918 x_content_chan_array OUT NOCOPY AMV_CHANNEL_VARRAY_TYPE
5919 )
5920 IS
5921 l_api_name CONSTANT VARCHAR2(30) := 'Find_Channels';
5922 l_api_version CONSTANT NUMBER := 1.0;
5923 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
5924 --
5925 l_resource_id number;
5926 l_user_id number;
5927 l_login_user_id number;
5928 l_login_user_status varchar2(30);
5929 l_Error_Msg varchar2(2000);
5930 l_Error_Token varchar2(80);
5931 --
5932 l_cursor CursorType;
5933 l_sql_statement VARCHAR2(4000);
5934 l_sql_statement2 VARCHAR2(4000);
5935 l_where_clause VARCHAR2(4000) := '';
5936 l_keyword VARCHAR2(10) := ' WHERE ';
5940 l_next_position NUMBER;
5937 l_total_count NUMBER := 1;
5938 l_fetch_count NUMBER := 0;
5939 l_start_with NUMBER;
5941 l_total_record_count NUMBER;
5942 l_null varchar2(30) := null;
5943 --
5944 l_channel_id NUMBER;
5945 l_object_version_number NUMBER;
5946 l_channel_name VARCHAR2(80);
5947 l_description VARCHAR2(2000);
5948 l_channel_type VARCHAR2(30);
5949 l_channel_category_id NUMBER;
5950 l_status VARCHAR2(30);
5951 l_owner_user_id NUMBER;
5952 l_default_approver_user_id NUMBER;
5953 l_effective_start_date DATE;
5954 l_expiration_date DATE;
5955 l_access_level_type VARCHAR2(30);
5956 l_need_approval_flag VARCHAR2(1);
5957 l_pub_need_approval_flag VARCHAR2(1);
5958 l_sub_need_approval_flag VARCHAR2(1);
5959 l_match_on_all_criteria_flag VARCHAR2(1);
5960 l_match_on_keyword_flag VARCHAR2(1);
5961 l_match_on_author_flag VARCHAR2(1);
5962 l_match_on_perspective_flag VARCHAR2(1);
5963 l_match_on_item_type_flag VARCHAR2(1);
5964 l_match_on_content_type_flag VARCHAR2(1);
5965 l_match_on_time_flag VARCHAR2(1);
5966 l_application_id NUMBER;
5967 l_external_access_flag VARCHAR2(1);
5968 l_item_match_count NUMBER;
5969 l_last_match_time DATE;
5970 l_notification_interval_type VARCHAR2(30);
5971 l_last_notification_time DATE;
5972 --
5973 BEGIN
5974 -- Standard begin of API savepoint
5975 SAVEPOINT Find_Channels_PVT;
5976 -- Standard call to check for call compatibility.
5977 IF NOT FND_API.Compatible_API_Call (
5978 l_api_version,
5979 p_api_version,
5980 l_api_name,
5981 G_PKG_NAME)
5982 THEN
5983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5984 END IF;
5985 -- Debug Message
5986 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
5987 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
5988 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
5989 FND_MSG_PUB.Add;
5990 END IF;
5991 --Initialize message list if p_init_msg_list is TRUE.
5992 IF FND_API.To_Boolean (p_init_msg_list) THEN
5993 FND_MSG_PUB.initialize;
5994 END IF;
5995 -- Get the current (login) user id.
5996 AMV_UTILITY_PVT.Get_UserInfo(
5997 x_resource_id => l_resource_id,
5998 x_user_id => l_user_id,
5999 x_login_id => l_login_user_id,
6000 x_user_status => l_login_user_status
6001 );
6002 -- check login user
6003 IF (p_check_login_user = FND_API.G_TRUE) THEN
6004 -- Check if user is login and has the required privilege.
6005 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
6006 -- User is not login.
6007 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6008 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
6009 FND_MSG_PUB.Add;
6010 END IF;
6011 RAISE FND_API.G_EXC_ERROR;
6012 END IF;
6013 END IF;
6014 -- This fix is for executing api in sqlplus mode
6015 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
6016 l_login_user_id := g_login_user_id;
6017 l_user_id := g_user_id;
6018 l_resource_id := g_resource_id;
6019 END IF;
6020 -- Initialize API return status to sucess
6021 x_return_status := FND_API.G_RET_STS_SUCCESS;
6022
6023 --
6024 --Construct dynamic SQL statement based on the parameters.
6025 l_sql_statement := 'SELECT b.channel_id, ' ||
6026 ' b.object_version_number, ' ||
6027 ' tl.channel_name, ' ||
6028 ' tl.description, ' ||
6029 ' b.channel_type, ' ||
6030 ' b.channel_category_id, ' ||
6031 ' b.status, ' ||
6032 ' b.owner_user_id, ' ||
6033 ' b.default_approver_user_id, ' ||
6034 ' b.effective_start_date, ' ||
6035 ' b.expiration_date, ' ||
6036 ' b.access_level_type, ' ||
6037 ' b.pub_need_approval_flag, ' ||
6038 ' b.sub_need_approval_flag, ' ||
6039 ' b.match_on_all_criteria_flag, ' ||
6040 ' b.match_on_keyword_flag, ' ||
6041 ' b.match_on_author_flag, ' ||
6042 ' b.match_on_perspective_flag, ' ||
6043 ' b.match_on_item_type_flag, ' ||
6044 ' b.match_on_content_type_flag, ' ||
6045 ' b.match_on_time_flag, ' ||
6046 ' b.application_id, ' ||
6047 ' b.external_access_flag, ' ||
6048 ' b.item_match_count, ' ||
6049 ' b.last_match_time, ' ||
6050 ' b.notification_interval_type, ' ||
6051 ' b.last_notification_time ' ||
6052 'FROM amv_c_channels_b b ' ||
6053 ', amv_c_channels_tl tl ';
6054 --Construct SQL statement for getting totaL count
6055 l_sql_statement2 := 'Select count(*) ' ||
6056 'FROM amv_c_channels_b b ' ||
6057 ', amv_c_channels_tl tl ';
6058 --Construct the WHERE clause
6059 IF (p_criteria_rec.channel_id <> FND_API.G_MISS_NUM) THEN
6060 l_where_clause := l_where_clause || l_keyword ||
6061 'b.channel_id = ' || p_criteria_rec.channel_id;
6062 l_keyword := ' AND ';
6063 END IF;
6064 IF (p_criteria_rec.channel_name <> FND_API.G_MISS_CHAR) THEN
6065 IF (p_criteria_rec.channel_name = '%') THEN
6066 l_where_clause := l_where_clause || l_keyword ||
6067 'tl.channel_name like '''||
6068 p_criteria_rec.channel_name ||'''';
6069 ELSE
6070 l_where_clause := l_where_clause || l_keyword ||
6071 ' contains ( tl.channel_name , '''||
6075 END IF;
6072 p_criteria_rec.channel_name || ''', 1) > 0 ';
6073 END IF;
6074 l_keyword := ' AND ';
6076 IF (p_criteria_rec.description <> FND_API.G_MISS_CHAR) THEN
6077 l_where_clause := l_where_clause || l_keyword ||
6078 'tl.description like ''' ||
6079 p_criteria_rec.description||'''';
6080 l_keyword := ' AND ';
6081 END IF;
6082 IF (p_criteria_rec.channel_type <> FND_API.G_MISS_CHAR) THEN
6083 l_where_clause := l_where_clause || l_keyword ||
6084 'b.channel_type = '''|| p_criteria_rec.channel_type||'''';
6085 l_keyword := ' AND ';
6086 END IF;
6087 IF (p_criteria_rec.channel_category_id <> FND_API.G_MISS_NUM) THEN
6088 l_where_clause := l_where_clause || l_keyword ||
6089 'b.channel_category_id = ' || p_criteria_rec.channel_category_id;
6090 l_keyword := ' AND ';
6091 END IF;
6092 IF (p_criteria_rec.status <> FND_API.G_MISS_CHAR) THEN
6093 l_where_clause := l_where_clause || l_keyword ||
6094 'b.status = ''' || p_criteria_rec.status||'''';
6095 l_keyword := ' AND ';
6096 END IF;
6097 IF (p_criteria_rec.owner_user_id <> FND_API.G_MISS_NUM) THEN
6098 l_where_clause := l_where_clause || l_keyword ||
6099 'b.owner_user_id = ' || p_criteria_rec.owner_user_id;
6100 l_keyword := ' AND ';
6101 END IF;
6102 IF (p_criteria_rec.default_approver_user_id <> FND_API.G_MISS_NUM) THEN
6103 l_where_clause := l_where_clause || l_keyword ||
6104 'b.default_approver_user_id = ' ||
6105 p_criteria_rec.default_approver_user_id;
6106 l_keyword := ' AND ';
6107 END IF;
6108 IF (p_criteria_rec.effective_start_date <> FND_API.G_MISS_DATE) THEN
6109 l_where_clause := l_where_clause || l_keyword ||
6110 'to_char(b.effective_start_date,'||'''DD-MON-YY'''||') = '''||
6111 p_criteria_rec.effective_start_date||'''';
6112 l_keyword := ' AND ';
6113 END IF;
6114 IF (p_criteria_rec.expiration_date <> FND_API.G_MISS_DATE) THEN
6115 l_where_clause := l_where_clause || l_keyword ||
6116 'to_char(b.expiration_date,'||'''DD-MON-YY'''||') = '''||
6117 p_criteria_rec.expiration_date||'''';
6118 l_keyword := ' AND ';
6119 END IF;
6120 IF (p_criteria_rec.access_level_type <> FND_API.G_MISS_CHAR) THEN
6121 l_where_clause := l_where_clause || l_keyword ||
6122 'b.access_level_type = ''' ||p_criteria_rec.access_level_type||'''';
6123 l_keyword := ' AND ';
6124 END IF;
6125 IF (p_criteria_rec.pub_need_approval_flag<>FND_API.G_MISS_CHAR) THEN
6126 l_where_clause := l_where_clause || l_keyword ||
6127 'b.pub_need_approval_flag = ''' ||
6128 p_criteria_rec.pub_need_approval_flag||'''';
6129 l_keyword := ' AND ';
6130 END IF;
6131 IF (p_criteria_rec.sub_need_approval_flag<>FND_API.G_MISS_CHAR) THEN
6132 l_where_clause := l_where_clause || l_keyword ||
6133 'b.sub_need_approval_flag = ''' ||
6134 p_criteria_rec.sub_need_approval_flag||'''';
6135 l_keyword := ' AND ';
6136 END IF;
6137 IF (p_criteria_rec.match_on_all_criteria_flag<>FND_API.G_MISS_CHAR) THEN
6138 l_where_clause := l_where_clause || l_keyword ||
6139 'b.match_on_all_criteria_flag = ''' ||
6140 p_criteria_rec.match_on_all_criteria_flag||'''';
6141 l_keyword := ' AND ';
6142 END IF;
6143 IF (p_criteria_rec.match_on_keyword_flag <> FND_API.G_MISS_CHAR) THEN
6144 l_where_clause := l_where_clause || l_keyword ||
6145 'b.match_on_all_keyword_flag = ''' ||
6146 p_criteria_rec.match_on_keyword_flag||'''';
6147 l_keyword := ' AND ';
6148 END IF;
6149 IF (p_criteria_rec.match_on_author_flag <> FND_API.G_MISS_CHAR) THEN
6150 l_where_clause := l_where_clause || l_keyword ||
6151 'b.match_on_author_flag = ''' ||
6152 p_criteria_rec.match_on_author_flag||'''';
6153 l_keyword := ' AND ';
6154 END IF;
6155 IF (p_criteria_rec.match_on_perspective_flag <> FND_API.G_MISS_CHAR) THEN
6156 l_where_clause := l_where_clause || l_keyword ||
6157 'b.match_on_perspective_flag = ''' ||
6158 p_criteria_rec.match_on_perspective_flag||'''';
6159 l_keyword := ' AND ';
6160 END IF;
6161 IF (p_criteria_rec.match_on_item_type_flag <> FND_API.G_MISS_CHAR) THEN
6162 l_where_clause := l_where_clause || l_keyword ||
6163 'b.match_on_item_type_flag = ''' ||
6164 p_criteria_rec.match_on_item_type_flag||'''';
6165 l_keyword := ' AND ';
6166 END IF;
6167 IF (p_criteria_rec.match_on_content_type_flag <> FND_API.G_MISS_CHAR) THEN
6168 l_where_clause := l_where_clause || l_keyword ||
6169 'b.match_on_content_type_flag = ''' ||
6170 p_criteria_rec.match_on_content_type_flag||'''';
6171 l_keyword := ' AND ';
6172 END IF;
6173 IF (p_criteria_rec.match_on_time_flag <> FND_API.G_MISS_CHAR) THEN
6174 l_where_clause := l_where_clause || l_keyword ||
6175 'b.match_on_time_flag = ''' ||
6176 p_criteria_rec.match_on_time_flag||'''';
6177 l_keyword := ' AND ';
6178 END IF;
6179 -- IF (p_criteria_rec.application_id <> FND_API.G_MISS_NUM) THEN
6180 -- l_where_clause := l_where_clause || l_keyword ||
6181 -- 'b.application_id = ' || p_criteria_rec.application_id;
6182 -- l_keyword := ' AND ';
6183 -- END IF;
6184 IF (p_criteria_rec.external_access_flag <> FND_API.G_MISS_CHAR) THEN
6185 l_where_clause := l_where_clause || l_keyword ||
6186 'b.external_access_flag = ''' ||
6190 IF (p_criteria_rec.item_match_count <> FND_API.G_MISS_NUM) THEN
6187 p_criteria_rec.external_access_flag||'''';
6188 l_keyword := ' AND ';
6189 END IF;
6191 l_where_clause := l_where_clause || l_keyword ||
6192 'b.item_match_count = ' || p_criteria_rec.item_match_count;
6193 l_keyword := ' AND ';
6194 END IF;
6195 IF (p_criteria_rec.last_match_time <> FND_API.G_MISS_DATE) THEN
6196 l_where_clause := l_where_clause || l_keyword ||
6197 'to_char(b.last_match_time,'||'''DD-MON-YY'''||') = ''' ||
6198 p_criteria_rec.last_match_time||'''';
6199 l_keyword := ' AND ';
6200 END IF;
6201 IF (p_criteria_rec.notification_interval_type <> FND_API.G_MISS_CHAR) THEN
6202 l_where_clause := l_where_clause || l_keyword ||
6203 'b.notification_interval_type = ''' ||
6204 p_criteria_rec.notification_interval_type||'''';
6205 l_keyword := ' AND ';
6206 END IF;
6207 IF (p_criteria_rec.last_notification_time <> FND_API.G_MISS_DATE) THEN
6208 l_where_clause := l_where_clause || l_keyword ||
6209 'to_char(b.last_notification_time,'||'''DD-MON-YY'''||') = ''' ||
6210 p_criteria_rec.last_notification_time||'''';
6211 l_keyword := ' AND ';
6212 END IF;
6213 l_where_clause := l_where_clause || l_keyword ||
6214 ' tl.language = userenv(' || '''lang''' || ') ' ||
6215 'AND b.channel_id = tl.channel_id ';
6216 --
6217 l_sql_statement := l_sql_statement || l_where_clause;
6218 l_sql_statement2 := l_sql_statement2 || l_where_clause;
6219 -- Construct the ORDER BY clause
6220 IF (p_sort_by <> FND_API.G_MISS_CHAR) THEN
6221 l_sql_statement := l_sql_statement || ' ORDER BY ' || p_sort_by;
6222 ELSE
6223 l_sql_statement := l_sql_statement || ' ORDER BY tl.channel_name ';
6224 END IF;
6225 --Execute the SQL statements to get the total count:
6226 IF (p_subset_request_rec.return_total_count_flag = FND_API.G_TRUE) THEN
6227 OPEN l_cursor FOR l_sql_statement2;
6228 FETCH l_cursor INTO l_total_record_count;
6229 CLOSE l_cursor;
6230 END IF;
6231 -- Set the starting position for the record
6232 l_start_with := p_subset_request_rec.start_record_position;
6233 --Now execute the SQL statement:
6234 OPEN l_cursor FOR l_sql_statement;
6235 x_content_chan_array := AMV_CHANNEL_VARRAY_TYPE();
6236 -- NOTE change to fetch into obj
6237 LOOP
6238 FETCH l_cursor INTO
6239 l_channel_id,
6240 l_object_version_number,
6241 l_channel_name,
6242 l_description,
6243 l_channel_type,
6244 l_channel_category_id,
6245 l_status,
6246 l_owner_user_id,
6247 l_default_approver_user_id,
6248 l_effective_start_date,
6249 l_expiration_date,
6250 l_access_level_type,
6251 l_pub_need_approval_flag,
6252 l_sub_need_approval_flag,
6253 l_match_on_all_criteria_flag,
6254 l_match_on_keyword_flag,
6255 l_match_on_author_flag,
6256 l_match_on_perspective_flag,
6257 l_match_on_item_type_flag,
6258 l_match_on_content_type_flag,
6259 l_match_on_time_flag,
6260 l_application_id,
6261 l_external_access_flag,
6262 l_item_match_count,
6263 l_last_match_time,
6264 l_notification_interval_type,
6265 l_last_notification_time;
6266 EXIT WHEN l_cursor%NOTFOUND;
6267 IF (l_start_with <= l_total_count AND
6268 l_fetch_count < p_subset_request_rec.records_requested) THEN
6269 l_fetch_count := l_fetch_count + 1;
6270 x_content_chan_array.extend;
6271 x_content_chan_array(l_fetch_count).channel_id := l_channel_id;
6272 x_content_chan_array(l_fetch_count).object_version_number :=
6273 l_object_version_number;
6274 x_content_chan_array(l_fetch_count).channel_name :=l_channel_name;
6275 x_content_chan_array(l_fetch_count).description :=l_description;
6276 x_content_chan_array(l_fetch_count).channel_type :=l_channel_type;
6277 x_content_chan_array(l_fetch_count).channel_category_id:=
6278 l_channel_category_id;
6279 x_content_chan_array(l_fetch_count).status := l_status;
6280 x_content_chan_array(l_fetch_count).owner_user_id :=l_owner_user_id;
6281 x_content_chan_array(l_fetch_count).default_approver_user_id :=
6282 l_default_approver_user_id;
6283 x_content_chan_array(l_fetch_count).effective_start_date :=
6284 l_effective_start_date;
6285 x_content_chan_array(l_fetch_count).expiration_date:=l_expiration_date;
6286 x_content_chan_array(l_fetch_count).access_level_type :=
6287 l_access_level_type;
6288 x_content_chan_array(l_fetch_count).pub_need_approval_flag :=
6289 l_pub_need_approval_flag;
6290 x_content_chan_array(l_fetch_count).sub_need_approval_flag :=
6291 l_sub_need_approval_flag;
6292 x_content_chan_array(l_fetch_count).match_on_all_criteria_flag :=
6293 l_match_on_all_criteria_flag;
6294 x_content_chan_array(l_fetch_count).match_on_keyword_flag :=
6295 l_match_on_keyword_flag;
6296 x_content_chan_array(l_fetch_count).match_on_author_flag :=
6297 l_match_on_author_flag;
6298 x_content_chan_array(l_fetch_count).match_on_perspective_flag :=
6299 l_match_on_perspective_flag;
6303 l_match_on_content_type_flag;
6300 x_content_chan_array(l_fetch_count).match_on_item_type_flag :=
6301 l_match_on_item_type_flag;
6302 x_content_chan_array(l_fetch_count).match_on_content_type_flag :=
6304 x_content_chan_array(l_fetch_count).match_on_time_flag :=
6305 l_match_on_time_flag;
6306 x_content_chan_array(l_fetch_count).application_id :=l_application_id;
6307 x_content_chan_array(l_fetch_count).external_access_flag :=
6308 l_external_access_flag;
6309 x_content_chan_array(l_fetch_count).item_match_count:=
6310 l_item_match_count;
6311 x_content_chan_array(l_fetch_count).last_match_time :=
6312 l_last_match_time;
6313 x_content_chan_array(l_fetch_count).notification_interval_type :=
6314 l_notification_interval_type;
6315 x_content_chan_array(l_fetch_count).last_notification_time :=
6316 l_last_notification_time;
6317 x_content_chan_array(l_fetch_count).attribute_category :=l_null;
6318 x_content_chan_array(l_fetch_count).attribute1 := l_null;
6319 x_content_chan_array(l_fetch_count).attribute2 := l_null;
6320 x_content_chan_array(l_fetch_count).attribute3 := l_null;
6321 x_content_chan_array(l_fetch_count).attribute4 := l_null;
6322 x_content_chan_array(l_fetch_count).attribute5 := l_null;
6323 x_content_chan_array(l_fetch_count).attribute6 := l_null;
6324 x_content_chan_array(l_fetch_count).attribute7 := l_null;
6325 x_content_chan_array(l_fetch_count).attribute8 := l_null;
6326 x_content_chan_array(l_fetch_count).attribute9 := l_null;
6327 x_content_chan_array(l_fetch_count).attribute10 := l_null;
6328 x_content_chan_array(l_fetch_count).attribute11 := l_null;
6329 x_content_chan_array(l_fetch_count).attribute12 := l_null;
6330 x_content_chan_array(l_fetch_count).attribute13 := l_null;
6331 x_content_chan_array(l_fetch_count).attribute14 := l_null;
6332 x_content_chan_array(l_fetch_count).attribute15 := l_null;
6333 /*
6334 x_content_chan_array(l_fetch_count) :=
6335 amv_channel_obj_type(
6336 l_channel_id,
6337 l_object_version_number,
6338 l_channel_name,
6339 l_description,
6340 l_channel_type,
6341 l_channel_category_id,
6342 l_status,
6343 l_owner_user_id,
6344 l_default_approver_user_id,
6345 l_effective_start_date,
6346 l_expiration_date,
6347 l_access_level_type,
6348 l_pub_need_approval_flag,
6349 l_sub_need_approval_flag,
6350 l_match_on_all_criteria_flag,
6351 l_match_on_keyword_flag,
6352 l_match_on_author_flag,
6353 l_match_on_perspective_flag,
6354 l_match_on_item_type_flag,
6355 l_match_on_content_type_flag,
6356 l_match_on_time_flag,
6357 l_application_id,
6358 l_external_access_flag,
6359 l_item_match_count,
6360 l_last_match_time,
6361 l_notification_interval_type,
6362 l_last_notification_time,
6363 l_null,
6364 l_null,
6365 l_null,
6366 l_null,
6367 l_null,
6368 l_null,
6369 l_null,
6370 l_null,
6371 l_null,
6372 l_null,
6373 l_null,
6374 l_null,
6375 l_null,
6376 l_null,
6377 l_null,
6378 l_null);
6379 */
6380 END IF;
6381 IF (l_fetch_count >= p_subset_request_rec.records_requested) THEN
6382 exit;
6383 END IF;
6384 l_total_count := l_total_count + 1;
6385 END LOOP;
6386 CLOSE l_cursor;
6387
6388 l_next_position := p_subset_request_rec.start_record_position+l_fetch_count;
6389 x_subset_return_rec.returned_record_count := l_fetch_count;
6390 x_subset_return_rec.next_record_position := l_next_position;
6391 x_subset_return_rec.total_record_count := l_total_record_count;
6392 /*
6393 x_subset_return_rec := amv_return_obj_type (
6394 l_fetch_count,
6395 l_next_position,
6396 l_total_record_count);
6397 */
6398 --
6399
6400 -- Debug Message
6401 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
6402 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
6403 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
6404 FND_MSG_PUB.Add;
6405 END IF;
6406 --Standard call to get message count and if count=1, get the message
6407 FND_MSG_PUB.Count_And_Get (
6408 p_encoded => FND_API.G_FALSE,
6409 p_count => x_msg_count,
6410 p_data => x_msg_data
6411 );
6412 EXCEPTION
6413 WHEN FND_API.G_EXC_ERROR THEN
6414 ROLLBACK TO Find_Channels_PVT;
6415 x_return_status := FND_API.G_RET_STS_ERROR;
6416 -- Standard call to get message count and if count=1, get the message
6417 FND_MSG_PUB.Count_And_Get (
6418 p_encoded => FND_API.G_FALSE,
6419 p_count => x_msg_count,
6420 p_data => x_msg_data
6421 );
6422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6423 ROLLBACK TO Find_Channels_PVT;
6424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6425 -- Standard call to get message count and if count=1, get the message
6426 FND_MSG_PUB.Count_And_Get (
6427 p_encoded => FND_API.G_FALSE,
6428 p_count => x_msg_count,
6429 p_data => x_msg_data
6430 );
6431 WHEN OTHERS THEN
6432 ROLLBACK TO Find_Channels_PVT;
6433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6434 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6435 THEN
6436 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
6437 END IF;
6438 -- Standard call to get message count and if count=1, get the message
6439 FND_MSG_PUB.Count_And_Get (
6440 p_encoded => FND_API.G_FALSE,
6441 p_count => x_msg_count,
6442 p_data => x_msg_data
6443 );
6444 --
6445 END Find_Channels;
6446 --------------------------------------------------------------------------------
6447 --------------------------------------------------------------------------------
6448 --
6449 END amv_channel_pvt;