[Home] [Help]
PACKAGE BODY: APPS.AMV_MYCHANNEL_PVT
Source
1 PACKAGE BODY amv_mychannel_pvt AS
2 /* $Header: amvvmycb.pls 120.1 2005/06/30 12:57:29 appldev ship $ */
3 --
4 --
5 -- NAME
6 -- AMV_MYCHANNEL_PVT
7 -- PURPOSE
8 --
9 -- HISTORY
10 -- 08/16/1999 SLKRISHN CREATED
11 -- 11/09/2001 VICHO UPDATED: Added a procedure to lauch channel
12 -- subscription workflow.
13 -- 07/22/2003 SHARMA Updated : fix for bug 2987210
14 -- 03/02/2004 SHARMA Updated : bug fix 3260137
15 --
16 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMV_MYCHANNEL_PVT';
17 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amvvmycb.pls';
18 --
19 --------------------------------------------------------------------------------
20 -- We use the following private utility procedures
21 --
22 ----------------------------- Private Portion ---------------------------------
23 --------------------------------------------------------------------------------
24 --------------------------------------------------------------------------------
25 PROCEDURE Validate_Subscription
26 ( x_return_status OUT NOCOPY VARCHAR2,
27 p_user_or_group_id IN NUMBER,
28 p_user_or_group_type IN VARCHAR2,
29 p_subscribing_to_id IN NUMBER,
30 p_subscribing_to_type IN VARCHAR2,
31 x_valid_flag OUT NOCOPY VARCHAR2,
32 x_mychannel_id OUT NOCOPY NUMBER,
33 x_error_msg OUT NOCOPY VARCHAR2,
34 x_error_token OUT NOCOPY VARCHAR2);
35 --------------------------------------------------------------------------------
36 --
37 -- Start of comments
38 -- API name : Validate_Subscription
39 -- Type : Private
40 -- Pre-reqs : None
41 -- Function : check if subscription parameters are valid
42 -- Parameters :
43 -- p_user_or_group_id IN NUMBER Required
44 -- p_user_or_group_type IN VARCHAR2 Required
45 -- p_subscribing_to_id IN NUMBER Required
46 -- p_subscribing_to_type IN VARCHAR2 Required
47 -- OUT : x_return_status OUT VARCHAR2
48 -- x_valid_flag OUT VARCHAR2
49 -- subscribtion parameters valid flag
50 -- x_mychannel_id OUT NUMBER,
51 -- x_error_msg OUT VARCHAR2
52 -- error message
53 -- x_error_token OUT VARCHAR2
54 -- error token
55 -- Version : Current version 1.0
56 -- Previous version 1.0
57 -- Initial version 1.0
58 -- Notes :
59 -- End of comments
60 --
61 PROCEDURE Validate_Subscription
62 ( x_return_status OUT NOCOPY VARCHAR2,
63 p_user_or_group_id NUMBER,
64 p_user_or_group_type VARCHAR2,
65 p_subscribing_to_id NUMBER,
66 p_subscribing_to_type VARCHAR2,
67 x_valid_flag OUT NOCOPY VARCHAR2,
68 x_mychannel_id OUT NOCOPY NUMBER,
69 x_error_msg OUT NOCOPY VARCHAR2,
70 x_error_token OUT NOCOPY VARCHAR2
71 )
72 IS
73 --
74 CURSOR My_Channel IS
75 select my_channel_id
76 from amv_u_my_channels
77 where user_or_group_id = p_user_or_group_id
78 and user_or_group_type = p_user_or_group_type
79 and subscribing_to_id = p_subscribing_to_id
80 and subscribing_to_type = p_subscribing_to_type;
81
82 BEGIN
83
84 -- Initialize return status to sucess
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86
87 -- check if user or group is valid
88 IF UPPER(p_user_or_group_type) = AMV_UTILITY_PVT.G_USER THEN
89 IF NOT AMV_UTILITY_PVT.Is_UserIdValid(p_user_or_group_id) THEN
90 x_error_msg := 'AMV_RESOURCE_ID_INVALID';
91 x_error_token := p_user_or_group_id;
92 RAISE FND_API.G_EXC_ERROR;
93 END IF;
94 ELSIF UPPER(p_user_or_group_type) = AMV_UTILITY_PVT.G_GROUP THEN
95 IF NOT AMV_UTILITY_PVT.Is_GroupIdValid(p_user_or_group_id) THEN
96 x_error_msg := 'AMV_GROUP_ID_INVALID';
97 x_error_token := p_user_or_group_id;
98 RAISE FND_API.G_EXC_ERROR;
99 END IF;
100 ELSE
101 x_error_msg := 'AMV_USER_OR_GROUP_TYPE_INVALID';
102 x_error_token := p_user_or_group_type;
103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
104 END IF;
105
106 -- check if channel or category exists
107 IF UPPER(p_subscribing_to_type) = AMV_UTILITY_PVT.G_CATEGORY THEN
108 IF NOT AMV_UTILITY_PVT.Is_CategoryIdValid(p_subscribing_to_id) THEN
109 x_error_msg := 'AMV_CATEGORY_ID_INVALID';
110 x_error_token := p_subscribing_to_id;
111 RAISE FND_API.G_EXC_ERROR;
112 END IF;
113 ELSIF UPPER(p_subscribing_to_type) = AMV_UTILITY_PVT.G_CHANNEL THEN
114 IF NOT AMV_UTILITY_PVT.Is_ChannelIdValid(p_subscribing_to_id) THEN
115 x_error_msg := 'AMV_CHANNEL_ID_INVALID';
116 x_error_token := p_subscribing_to_id;
117 RAISE FND_API.G_EXC_ERROR;
118 END IF;
119 ELSE
120 x_error_msg := 'AMV_SUB_TO_TYPE_INVALID';
121 x_error_token := p_subscribing_to_type;
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124
125 OPEN My_Channel;
126 FETCH My_Channel INTO x_mychannel_id;
127 IF My_Channel%NOTFOUND THEN
128 x_mychannel_id := FND_API.G_MISS_NUM;
129 END IF;
130 CLOSE My_Channel;
131
132 -- set validity to true
133 x_valid_flag := FND_API.G_TRUE;
134
135 EXCEPTION
136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138 x_valid_flag := FND_API.G_FALSE;
139 WHEN FND_API.G_EXC_ERROR THEN
140 x_return_status := FND_API.G_RET_STS_ERROR;
141 x_valid_flag := FND_API.G_FALSE;
142 WHEN OTHERS THEN
143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144 x_valid_flag := FND_API.G_FALSE;
145 x_error_msg := 'AMV_SUB_VALIDATION_FAILED';
146 END Validate_Subscription;
147
148 --------------------------------------------------------------------------------
149 --------------------------------------------------------------------------------
150 -- Start of comments
151 -- API name : Subscribe_Channel
152 -- Type : Private
153 -- Pre-reqs : None
154 -- Function : Launch workflow to subscribe a channel for an user
155 -- Parameters :
156 -- IN p_api_version IN NUMBER Required
157 -- p_init_msg_list IN VARCHAR2 Optional
158 -- Default = FND_API.G_FALSE
159 -- p_commit IN VARCHAR2 Optional
160 -- Default = FND_API.G_FALSE
161 -- p_validation_level IN NUMBER Optional
162 -- Default = FND_API.G_VALID_LEVEL_FULL
163 -- p_check_login_user IN VARCHAR2 Optional
164 -- Default = FND_API.G_TRUE
165 -- p_channel_id IN NUMBER Required
166 -- OUT : x_return_status OUT VARCHAR2
167 -- x_msg_count OUT NUMBER
168 -- x_msg_data OUT VARCHAR2
169 -- Version : Current version 1.0
170 -- Previous version 1.0
171 -- Initial version 1.0
172 -- Notes :
173 --
174 -- End of comments
175 PROCEDURE Subscribe_Channel
176 ( p_api_version IN NUMBER,
177 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178 p_commit IN VARCHAR2 := FND_API.G_FALSE,
179 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
181 p_channel_id IN NUMBER,
182 x_return_status OUT NOCOPY VARCHAR2,
183 x_msg_count OUT NOCOPY NUMBER,
184 x_msg_data OUT NOCOPY VARCHAR2
185 )
186 IS
187 l_api_name CONSTANT VARCHAR2(30) := 'Subscribe_Channel';
188 l_api_version CONSTANT NUMBER := 1.0;
189 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
190 --
191 l_resource_id number;
192 l_user_id number;
193 l_login_user_id number := FND_API.G_MISS_NUM;
194 l_login_user_status varchar2(30);
195 --
196 l_owner_name varchar2(100);
197 l_owner_id number;
198 --
199 CURSOR Get_OwnerIDAndName_csr (res_id IN NUMBER)IS
200 select u.user_name, r.resource_id
201 From amv_rs_all_res_extns_vl r, fnd_user u
202 where r.resource_id = res_id
203 and u.user_id = r.user_id;
204 --
205 BEGIN
206 -- Standard begin of API savepoint
207 SAVEPOINT Subscribe_Channel_PVT;
208 -- Standard call to check for call compatibility.
209 IF NOT FND_API.Compatible_API_Call (
210 l_api_version,
211 p_api_version,
212 l_api_name,
213 G_PKG_NAME)
214 THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 END IF;
217 -- Debug Message
218 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
219 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
220 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
221 FND_MSG_PUB.Add;
222 END IF;
223 --Initialize message list if p_init_msg_list is TRUE.
224 IF FND_API.To_Boolean (p_init_msg_list) THEN
225 FND_MSG_PUB.initialize;
226 END IF;
227 -- Get the current (login) user id.
228 AMV_UTILITY_PVT.Get_UserInfo(
229 x_resource_id => l_resource_id,
230 x_user_id => l_user_id,
231 x_login_id => l_login_user_id,
232 x_user_status => l_login_user_status
233 );
234 -- check login user
235 IF (p_check_login_user = FND_API.G_TRUE) THEN
236 -- Check if user is login and has the required privilege.
237 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
238 -- User is not login.
239 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
240 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
241 FND_MSG_PUB.Add;
242 END IF;
243 RAISE FND_API.G_EXC_ERROR;
244 END IF;
245 END IF;
246 -- This fix is for executing api in sqlplus mode
247 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
248 l_login_user_id := -1;
249 l_user_id := -1;
250 l_resource_id := -1;
251 END IF;
252 -- Initialize API return status to sucess
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 OPEN Get_OwnerIDAndName_csr (l_resource_id);
256 FETCH Get_OwnerIDAndName_csr INTO l_owner_name, l_owner_id;
257 IF (Get_OwnerIDAndName_csr%NOTFOUND) THEN
258 CLOSE Get_OwnerIDAndName_csr;
259 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
260 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
261 FND_MESSAGE.Set_Token('RECORD', 'AMV_CHANNEL_TK', TRUE);
262 FND_MESSAGE.Set_Token('ID', to_char(nvl(l_resource_id,-1)));
263 FND_MSG_PUB.Add;
264 END IF;
265 RAISE FND_API.G_EXC_ERROR;
266 END IF;
267 CLOSE Get_OwnerIDAndName_csr;
268
269 -- Lauch WorkFlow Process: 'AMV_SUBSCRIPTION_APPROVAL'
270 AMV_WFAPPROVAL_PVT.StartProcess (
271 RequestorId => l_resource_id,
272 ChannelId => p_channel_id,
273 ProcessOwner => l_owner_name,
274 Workflowprocess => 'AMV_SUBSCRIPTION_APPROVAL'
275 );
276
277 -- Success message
278 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
279 THEN
280 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
281 FND_MESSAGE.Set_Token('ROW', l_full_name);
282 FND_MSG_PUB.Add;
283 END IF;
284 -- Standard check of commit
285 IF FND_API.To_Boolean ( p_commit ) THEN
286 COMMIT WORK;
287 END IF;
288 -- Debug Message
289 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
290 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
291 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
292 FND_MSG_PUB.Add;
293 END IF;
294 --Standard call to get message count and if count=1, get the message
295 FND_MSG_PUB.Count_And_Get (
296 p_encoded => FND_API.G_FALSE,
297 p_count => x_msg_count,
298 p_data => x_msg_data
299 );
300 EXCEPTION
301 WHEN FND_API.G_EXC_ERROR THEN
302 ROLLBACK TO Subscribe_Channel_PVT;
303 x_return_status := FND_API.G_RET_STS_ERROR;
304 -- Standard call to get message count and if count=1, get the message
305 FND_MSG_PUB.Count_And_Get (
306 p_encoded => FND_API.G_FALSE,
307 p_count => x_msg_count,
308 p_data => x_msg_data
309 );
310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
311 ROLLBACK TO Subscribe_Channel_PVT;
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 -- Standard call to get message count and if count=1, get the message
314 FND_MSG_PUB.Count_And_Get (
315 p_encoded => FND_API.G_FALSE,
316 p_count => x_msg_count,
317 p_data => x_msg_data
318 );
319 WHEN OTHERS THEN
320 ROLLBACK TO Subscribe_Channel_PVT;
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
322 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
323 THEN
324 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
325 END IF;
326 -- Standard call to get message count and if count=1, get the message
327 FND_MSG_PUB.Count_And_Get (
328 p_encoded => FND_API.G_FALSE,
329 p_count => x_msg_count,
330 p_data => x_msg_data
331 );
332 --
333 END Subscribe_Channel;
334
335
336
337
338 --------------------------------------------------------------------------------
339 --------------------------------------------------------------------------------
340 -- Start of comments
341 -- API name : Add_Subscription
342 -- Type : Private
343 -- Pre-reqs : None
344 -- Function : Subscribe a channel/category for an user
345 -- Parameters :
346 -- IN p_api_version IN NUMBER Required
347 -- p_init_msg_list IN VARCHAR2 Optional
348 -- Default = FND_API.G_FALSE
349 -- p_commit IN VARCHAR2 Optional
350 -- Default = FND_API.G_FALSE
351 -- p_validation_level IN NUMBER Optional
352 -- Default = FND_API.G_VALID_LEVEL_FULL
353 -- p_check_login_user IN VARCHAR2 Optional
354 -- Default = FND_API.G_TRUE
355 -- p_mychannel_obj IN
356 -- AMV_MY_CHANNEL_OBJ_TYPE Required
357 -- MyChannel data to be created.
358 -- OUT : x_return_status OUT VARCHAR2
359 -- x_msg_count OUT NUMBER
360 -- x_msg_data OUT VARCHAR2
361 -- x_mychannel_id OUT NUMBER
362 -- Version : Current version 1.0
363 -- Previous version 1.0
364 -- Initial version 1.0
365 -- Notes :
366 --
367 -- End of comments
368 --
369 PROCEDURE Add_Subscription
370 ( p_api_version IN NUMBER,
371 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
372 p_commit IN VARCHAR2 := FND_API.G_FALSE,
373 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
374 x_return_status OUT NOCOPY VARCHAR2,
375 x_msg_count OUT NOCOPY NUMBER,
376 x_msg_data OUT NOCOPY VARCHAR2,
377 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
378 p_mychannel_obj IN AMV_MY_CHANNEL_OBJ_TYPE,
379 x_mychannel_id OUT NOCOPY NUMBER
380 )
381 IS
382 l_api_name CONSTANT VARCHAR2(30) := 'Add_Subscribtion';
383 l_api_version CONSTANT NUMBER := 1.0;
384 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
385 --
386 l_resource_id number;
387 l_user_id number;
388 l_login_user_id number := FND_API.G_MISS_NUM;
389 l_login_user_status varchar2(30);
390 l_Error_Msg varchar2(2000);
391 l_Error_Token varchar2(80);
392 l_object_version_number number := 1;
393 --
394 l_mychannel_id number;
395 l_expiration_date date;
396 l_order number;
397 l_valid_flag varchar2(1);
398 --
399 CURSOR MyChannelId_Seq IS
400 select amv_u_my_channels_s.nextval
401 from dual;
402
403 CURSOR MyChannelOrder IS
404 SELECT NVL(MAX(order_number) + 1, 1)
405 FROM amv_u_my_channels
406 WHERE user_or_group_id = p_mychannel_obj.user_or_group_id
407 and user_or_group_type = p_mychannel_obj.user_or_group_type;
408 --
409 BEGIN
410 -- Standard begin of API savepoint
411 SAVEPOINT Add_Subscription_PVT;
412 -- Standard call to check for call compatibility.
413 IF NOT FND_API.Compatible_API_Call (
414 l_api_version,
415 p_api_version,
416 l_api_name,
417 G_PKG_NAME)
418 THEN
419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420 END IF;
421 -- Debug Message
422 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
423 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
424 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
425 FND_MSG_PUB.Add;
426 END IF;
427 --Initialize message list if p_init_msg_list is TRUE.
428 IF FND_API.To_Boolean (p_init_msg_list) THEN
429 FND_MSG_PUB.initialize;
430 END IF;
431
432 -- check login user
433 IF (p_check_login_user = FND_API.G_TRUE) THEN
434 -- Get the current (login) user id.
435 AMV_UTILITY_PVT.Get_UserInfo(
436 x_resource_id => l_resource_id,
437 x_user_id => l_user_id,
438 x_login_id => l_login_user_id,
439 x_user_status => l_login_user_status
440 );
441 -- Check if user is login and has the required privilege.
442 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
443 -- User is not login.
444 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
445 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
446 FND_MSG_PUB.Add;
447 END IF;
448 RAISE FND_API.G_EXC_ERROR;
449 END IF;
450 END IF;
451 -- This fix is for executing api in sqlplus mode
452 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
453 l_login_user_id := -1;
454 l_user_id := -1;
455 l_resource_id := -1;
456 END IF;
457 -- Initialize API return status to sucess
458 x_return_status := FND_API.G_RET_STS_SUCCESS;
459 --
460
461 -- Validate the subscription
462 Validate_Subscription (
463 x_return_status => x_return_status,
464 p_user_or_group_id => p_mychannel_obj.user_or_group_id,
465 p_user_or_group_type => p_mychannel_obj.user_or_group_type,
466 p_subscribing_to_id => p_mychannel_obj.subscribing_to_id,
467 p_subscribing_to_type => p_mychannel_obj.subscribing_to_type,
468 x_valid_flag => l_valid_flag,
469 x_mychannel_id => l_mychannel_id,
470 x_error_msg => l_Error_Msg,
471 x_error_token => l_Error_Token
472 );
473
474 -- create subscription after validation
475 IF l_valid_flag = FND_API.G_TRUE THEN
476 IF l_mychannel_id <> FND_API.G_MISS_NUM THEN
477 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
478 THEN
479 FND_MESSAGE.Set_Name('AMV', 'AMV_SUBSCRIPTION_EXISTS');
480 FND_MESSAGE.Set_Token('TKN',p_mychannel_obj.user_or_group_type);
481 FND_MSG_PUB.Add;
482 END IF;
483 RAISE FND_API.G_EXC_ERROR;
484 ELSE
485 -- set mychannel order
486 IF p_mychannel_obj.order_number = FND_API.G_MISS_NUM THEN
487 OPEN MyChannelOrder;
488 FETCH MyChannelOrder INTO l_order;
489 CLOSE MyChannelOrder;
490 ELSE
491 l_order := p_mychannel_obj.order_number;
492 END IF;
493
494 -- Remove end date if already existing?
495 -- Select the channel sequence
496 OPEN MyChannelId_Seq;
497 FETCH MyChannelId_Seq INTO l_mychannel_id;
498 CLOSE MyChannelId_Seq;
499
500 -- Add a record in the mychannel
501 INSERT INTO amv_u_my_channels (
502 my_channel_id,
503 object_version_number,
504 last_update_date,
505 last_updated_by,
506 creation_date,
507 created_by,
508 last_update_login,
509 user_or_group_id,
510 user_or_group_type,
511 subscribing_to_id,
512 subscribing_to_type,
513 subscription_reason_type,
514 order_number,
515 status,
516 notify_flag,
517 notification_interval_type
518 )
519 VALUES (
520 l_mychannel_id,
521 l_object_version_number,
522 sysdate,
523 l_user_id,
524 sysdate,
525 l_user_id,
526 l_login_user_id,
527 p_mychannel_obj.user_or_group_id,
528 p_mychannel_obj.user_or_group_type,
529 p_mychannel_obj.subscribing_to_id,
530 p_mychannel_obj.subscribing_to_type,
531 p_mychannel_obj.subscription_reason_type,
532 l_order,
533 p_mychannel_obj.status,
534 p_mychannel_obj.notify_flag,
535 p_mychannel_obj.notification_interval_type
536 );
537 END IF;
538 ELSE
539 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
540 THEN
541 -- NOTE change token name CATEGORY to generic name
542 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
543 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
544 FND_MSG_PUB.Add;
545 END IF;
546 RAISE FND_API.G_EXC_ERROR;
547 END IF;
548 --
549 -- Success message
550 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
551 THEN
552 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
553 FND_MESSAGE.Set_Token('ROW', l_full_name);
554 FND_MSG_PUB.Add;
555 END IF;
556 --Standard check of commit
557 IF FND_API.To_Boolean ( p_commit ) THEN
558 COMMIT WORK;
559 END IF;
560 -- Debug Message
561 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
562 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
563 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
564 FND_MSG_PUB.Add;
565 END IF;
566 --Standard call to get message count and if count=1, get the message
567 FND_MSG_PUB.Count_And_Get (
568 p_encoded => FND_API.G_FALSE,
569 p_count => x_msg_count,
570 p_data => x_msg_data
571 );
572 EXCEPTION
573 WHEN FND_API.G_EXC_ERROR THEN
574 ROLLBACK TO Add_Subscription_PVT;
575 x_return_status := FND_API.G_RET_STS_ERROR;
576 -- Standard call to get message count and if count=1, get the message
577 FND_MSG_PUB.Count_And_Get (
578 p_encoded => FND_API.G_FALSE,
579 p_count => x_msg_count,
580 p_data => x_msg_data
581 );
582 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
583 ROLLBACK TO Add_Subscription_PVT;
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585 -- Standard call to get message count and if count=1, get the message
586 FND_MSG_PUB.Count_And_Get (
587 p_encoded => FND_API.G_FALSE,
588 p_count => x_msg_count,
589 p_data => x_msg_data
590 );
591 WHEN OTHERS THEN
592 ROLLBACK TO Add_Subscription_PVT;
593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
594 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
595 THEN
596 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
597 END IF;
598 -- Standard call to get message count and if count=1, get the message
599 FND_MSG_PUB.Count_And_Get (
600 p_encoded => FND_API.G_FALSE,
601 p_count => x_msg_count,
602 p_data => x_msg_data
603 );
604 --
605 END Add_Subscription;
606 --------------------------------------------------------------------------------
607 -- Start of comments
608 -- API name : Remove_Subscription
609 -- Type : Private
610 -- Pre-reqs : None
611 -- Function : Delete the subscription given p_mychannel_id.
612 -- Parameters :
613 -- IN p_api_version IN NUMBER Required
614 -- p_init_msg_list IN VARCHAR2 Optional
615 -- Default = FND_API.G_FALSE
616 -- p_commit IN VARCHAR2 Optional
617 -- Default = FND_API.G_FALSE
618 -- p_validation_level IN NUMBER Optional
619 -- Default = FND_API.G_VALID_LEVEL_FULL
620 -- p_check_login_user IN VARCHAR2 Optional
621 -- Default = FND_API.G_TRUE
622 -- p_mychannel_id IN NUMBER Required
623 -- MyChannel Id
624 -- p_user_or_group_id IN NUMBER Optional
625 -- Default = FND_API.G_MISS_NUM
626 -- p_user_or_group_type IN VARCHAR2 Optional
627 -- Default = FND_API.G_MISS_CHAR
628 -- p_subscribing_to_id IN NUMBER Optional
629 -- Default = FND_API.G_MISS_NUM
630 -- p_subscribing_to_type IN VARCHAR2 Optional
631 -- Default = FND_API.G_MISS_CHAR
632 -- Pass the user and subscription id and types
633 -- instead of mychannel id.
634 -- Must pass the mychannel id or the joint key of
635 -- above four values
636 -- OUT : x_return_status OUT VARCHAR2
637 -- x_msg_count OUT NUMBER
638 -- x_msg_data OUT VARCHAR2
639 -- Version : Current version 1.0
640 -- Previous version 1.0
641 -- Initial version 1.0
642 -- Notes :
643 --
644 -- End of comments
645 --
646 PROCEDURE Remove_Subscription
647 ( p_api_version IN NUMBER,
648 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
649 p_commit IN VARCHAR2 := FND_API.G_FALSE,
650 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
651 x_return_status OUT NOCOPY VARCHAR2,
652 x_msg_count OUT NOCOPY NUMBER,
653 x_msg_data OUT NOCOPY VARCHAR2,
654 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
655 p_mychannel_id IN NUMBER := FND_API.G_MISS_NUM,
656 p_user_or_group_id IN NUMBER := FND_API.G_MISS_NUM,
657 p_user_or_group_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
658 p_subscribing_to_id IN NUMBER := FND_API.G_MISS_NUM,
659 p_subscribing_to_type IN VARCHAR2 := FND_API.G_MISS_CHAR
660 )
661 IS
662 l_api_name CONSTANT VARCHAR2(30) := 'Remove_Subscription';
663 l_api_version CONSTANT NUMBER := 1.0;
664 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
665 --
666 l_resource_id number;
667 l_user_id number;
668 l_login_user_id number;
669 l_login_user_status varchar2(30);
670 l_Error_Msg varchar2(2000);
671 l_Error_Token varchar2(80);
672 --
673 l_mychannel_id number;
674 l_myuser_id number;
675 l_myuser_type varchar2(30) := 'USER';
676 l_sub_to_id number;
677 l_sub_to_type varchar2(30);
678 l_valid_flag varchar2(1);
679 --
680 CURSOR MyChannel_Info IS
681 select user_or_group_id
682 , user_or_group_type
683 , subscribing_to_id
684 , subscribing_to_type
685 from amv_u_my_channels
686 where my_channel_id = p_mychannel_id;
687 --
688 BEGIN
689 -- Standard begin of API savepoint
690 SAVEPOINT Remove_Subscription_PVT;
691 -- Standard call to check for call compatibility.
692 IF NOT FND_API.Compatible_API_Call (
693 l_api_version,
694 p_api_version,
695 l_api_name,
696 G_PKG_NAME)
697 THEN
698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
699 END IF;
700 -- Debug Message
701 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
702 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
703 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
704 FND_MSG_PUB.Add;
705 END IF;
706 --Initialize message list if p_init_msg_list is TRUE.
707 IF FND_API.To_Boolean (p_init_msg_list) THEN
708 FND_MSG_PUB.initialize;
709 END IF;
710 -- Get the current (login) user id.
711 AMV_UTILITY_PVT.Get_UserInfo(
712 x_resource_id => l_resource_id,
713 x_user_id => l_user_id,
714 x_login_id => l_login_user_id,
715 x_user_status => l_login_user_status
716 );
717 -- check login user
718 IF (p_check_login_user = FND_API.G_TRUE) THEN
719 -- Check if user is login and has the required privilege.
720 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
721 -- User is not login.
722 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
723 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
724 FND_MSG_PUB.Add;
725 END IF;
726 RAISE FND_API.G_EXC_ERROR;
727 END IF;
728 END IF;
729 -- This fix is for executing api in sqlplus mode
730 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
731 l_login_user_id := -1;
732 l_user_id := -1;
733 l_resource_id := -1;
734 END IF;
735 -- Initialize API return status to sucess
736 x_return_status := FND_API.G_RET_STS_SUCCESS;
737
738 --
739 IF p_mychannel_id = FND_API.G_MISS_NUM OR
740 p_mychannel_id IS NULL
741 THEN
742 -- Validate the subscription
743 Validate_Subscription (
744 x_return_status => x_return_status,
745 p_user_or_group_id => p_user_or_group_id,
746 p_user_or_group_type => p_user_or_group_type,
747 p_subscribing_to_id => p_subscribing_to_id,
748 p_subscribing_to_type => p_subscribing_to_type,
749 x_valid_flag => l_valid_flag,
750 x_mychannel_id => l_mychannel_id,
751 x_error_msg => l_Error_Msg,
752 x_error_token => l_Error_Token
753 );
754 IF l_valid_flag = FND_API.G_FALSE THEN
755 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
756 THEN
757 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
758 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
759 FND_MSG_PUB.Add;
760 END IF;
761 RAISE FND_API.G_EXC_ERROR;
762 END IF;
763 -- set to local variables
764 l_myuser_id := p_user_or_group_id;
765 l_myuser_type := p_user_or_group_type;
766 l_sub_to_id := p_subscribing_to_id;
767 l_sub_to_type := p_subscribing_to_type;
768 ELSE
769 -- Get the user info for this subscription
770 OPEN MyChannel_Info;
771 FETCH MyChannel_Info INTO
772 l_myuser_id, l_myuser_type, l_sub_to_id, l_sub_to_type;
773 CLOSE MyChannel_Info;
774 -- assign mychannel value to local variable
775 l_mychannel_id := p_mychannel_id;
776 END IF;
777
778 -- Remove subscription
779 DELETE FROM amv_u_my_channels
780 WHERE my_channel_id = l_mychannel_id;
781 --
782
783 -- Success message
784 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
785 THEN
786 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
787 FND_MESSAGE.Set_Token('ROW', l_full_name);
788 FND_MSG_PUB.Add;
789 END IF;
790 --Standard check of commit
791 IF FND_API.To_Boolean ( p_commit ) THEN
792 COMMIT WORK;
793 END IF;
794 -- Debug Message
795 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
796 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
797 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
798 FND_MSG_PUB.Add;
799 END IF;
800 --Standard call to get message count and if count=1, get the message
801 FND_MSG_PUB.Count_And_Get (
802 p_encoded => FND_API.G_FALSE,
803 p_count => x_msg_count,
804 p_data => x_msg_data
805 );
806 EXCEPTION
807 WHEN FND_API.G_EXC_ERROR THEN
808 ROLLBACK TO Remove_Subscription_PVT;
809 x_return_status := FND_API.G_RET_STS_ERROR;
810 -- Standard call to get message count and if count=1, get the message
811 FND_MSG_PUB.Count_And_Get (
812 p_encoded => FND_API.G_FALSE,
813 p_count => x_msg_count,
814 p_data => x_msg_data
815 );
816 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
817 ROLLBACK TO Remove_Subscription_PVT;
818 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
819 -- Standard call to get message count and if count=1, get the message
820 FND_MSG_PUB.Count_And_Get (
821 p_encoded => FND_API.G_FALSE,
822 p_count => x_msg_count,
823 p_data => x_msg_data
824 );
825 WHEN OTHERS THEN
826 ROLLBACK TO Remove_Subscription_PVT;
827 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
828 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
829 THEN
830 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
831 END IF;
832 -- Standard call to get message count and if count=1, get the message
833 FND_MSG_PUB.Count_And_Get (
834 p_encoded => FND_API.G_FALSE,
835 p_count => x_msg_count,
836 p_data => x_msg_data
837 );
838 --
839 END Remove_Subscription;
840 --------------------------------------------------------------------------------
841 -- Start of comments
842 -- API name : Update_MyChannel
843 -- Type : Private
844 -- Pre-reqs : None
845 -- Function : Update the mychannel given p_mychannel_id.
846 -- Parameters :
847 -- IN p_api_version IN NUMBER Required
848 -- p_init_msg_list IN VARCHAR2 Optional
849 -- Default = FND_API.G_FALSE
850 -- p_commit IN VARCHAR2 Optional
851 -- Default = FND_API.G_FALSE
852 -- p_validation_level IN NUMBER Optional
853 -- Default = FND_API.G_VALID_LEVEL_FULL
854 -- p_check_login_user IN VARCHAR2 Optional
855 -- Default = FND_API.G_TRUE
856 -- p_mychannel_obj IN AMV_MY_CHANNEL_OBJ_TYPE Required
857 -- OUT : x_return_status OUT VARCHAR2
858 -- x_msg_count OUT NUMBER
859 -- x_msg_data OUT VARCHAR2
860 -- Version : Current version 1.0
861 -- Previous version 1.0
862 -- Initial version 1.0
863 -- Notes :
864 --
865 -- End of comments
866 --
867 PROCEDURE Update_MyChannel
868 ( p_api_version IN NUMBER,
869 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
870 p_commit IN VARCHAR2 := FND_API.G_FALSE,
871 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
872 x_return_status OUT NOCOPY VARCHAR2,
873 x_msg_count OUT NOCOPY NUMBER,
874 x_msg_data OUT NOCOPY VARCHAR2,
875 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
876 p_mychannel_obj IN AMV_MY_CHANNEL_OBJ_TYPE
877 )
878 IS
879 l_api_name CONSTANT VARCHAR2(30) := 'Update_MyChannel';
880 l_api_version CONSTANT NUMBER := 1.0;
881 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
882 --
883 l_resource_id number;
884 l_user_id number;
885 l_login_user_id number;
886 l_login_user_status varchar2(30);
887 l_Error_Msg varchar2(2000);
888 l_Error_Token varchar2(80);
889 --
890 l_valid_flag varchar2(1);
891 l_mychannel_id number;
892 l_order_number number;
893 l_status varchar2(30);
894 l_notify_flag varchar2(1);
895 l_notif_interval_type varchar2(30);
896 --
897 CURSOR MyChannel IS
898 select order_number
899 , status
900 , notify_flag
901 , notification_interval_type
902 from amv_u_my_channels
903 where my_channel_id = l_mychannel_id;
904 --
905 BEGIN
906 -- Standard begin of API savepoint
907 SAVEPOINT Update_MyChannel_PVT;
908 -- Standard call to check for call compatibility.
909 IF NOT FND_API.Compatible_API_Call (
910 l_api_version,
911 p_api_version,
912 l_api_name,
913 G_PKG_NAME)
914 THEN
915 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
916 END IF;
917 -- Debug Message
918 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
919 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
920 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
921 FND_MSG_PUB.Add;
922 END IF;
923 --Initialize message list if p_init_msg_list is TRUE.
924 IF FND_API.To_Boolean (p_init_msg_list) THEN
925 FND_MSG_PUB.initialize;
926 END IF;
927 -- Get the current (login) user id.
928 AMV_UTILITY_PVT.Get_UserInfo(
929 x_resource_id => l_resource_id,
930 x_user_id => l_user_id,
931 x_login_id => l_login_user_id,
932 x_user_status => l_login_user_status
933 );
934 -- check login user
935 IF (p_check_login_user = FND_API.G_TRUE) THEN
936 -- Check if user is login and has the required privilege.
937 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
938 -- User is not login.
939 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
940 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
941 FND_MSG_PUB.Add;
942 END IF;
943 RAISE FND_API.G_EXC_ERROR;
944 END IF;
945 END IF;
946 -- This fix is for executing api in sqlplus mode
947 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
948 l_login_user_id := -1;
949 l_user_id := -1;
950 l_resource_id := -1;
951 END IF;
952 -- Initialize API return status to sucess
953 x_return_status := FND_API.G_RET_STS_SUCCESS;
954
955 --
956 IF p_mychannel_obj.my_channel_id = FND_API.G_MISS_NUM OR
957 p_mychannel_obj.my_channel_id IS NULL
958 THEN
959 -- Validate the subscription
960 Validate_Subscription (
961 x_return_status => x_return_status,
962 p_user_or_group_id => p_mychannel_obj.user_or_group_id,
963 p_user_or_group_type => p_mychannel_obj.user_or_group_type,
964 p_subscribing_to_id => p_mychannel_obj.subscribing_to_id,
965 p_subscribing_to_type => p_mychannel_obj.subscribing_to_type,
966 x_valid_flag => l_valid_flag,
967 x_mychannel_id => l_mychannel_id,
968 x_error_msg => l_Error_Msg,
969 x_error_token => l_Error_Token
970 );
971 IF l_valid_flag = FND_API.G_TRUE THEN
972 IF l_mychannel_id = FND_API.G_MISS_NUM THEN
973 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
974 THEN
975 FND_MESSAGE.Set_Name('AMV', 'AMV_SUBSCRIPTION_INVALID');
976 FND_MESSAGE.Set_Token('TKN',
977 p_mychannel_obj.subscribing_to_id);
978 FND_MSG_PUB.Add;
979 END IF;
980 RAISE FND_API.G_EXC_ERROR;
981 END IF;
982 ELSE
983 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
984 THEN
985 FND_MESSAGE.Set_Name('AMV', l_Error_Msg);
986 FND_MESSAGE.Set_Token('TKN',l_Error_Token);
987 FND_MSG_PUB.Add;
988 END IF;
989 RAISE FND_API.G_EXC_ERROR;
990 END IF;
991 ELSE
992 -- check if my channel exists
993 IF AMV_UTILITY_PVT.Is_MyChannelIdValid(p_mychannel_obj.my_channel_id)
994 THEN
995 l_mychannel_id := p_mychannel_obj.my_channel_id;
996 END IF;
997 END IF;
998
999 -- Initialize with old values
1000 OPEN MyChannel;
1001 FETCH MyChannel INTO l_order_number,
1002 l_status,
1003 l_notify_flag,
1004 l_notif_interval_type;
1005 CLOSE MyChannel;
1006
1007 IF p_mychannel_obj.order_number <> FND_API.G_MISS_NUM THEN
1008 l_order_number := p_mychannel_obj.order_number;
1009 END IF;
1010
1011 IF p_mychannel_obj.status <> FND_API.G_MISS_CHAR OR
1012 p_mychannel_obj.status IS NOT NULL
1013 THEN
1014 l_status := p_mychannel_obj.status;
1015 END IF;
1016
1017 IF p_mychannel_obj.notify_flag <> FND_API.G_MISS_CHAR OR
1018 p_mychannel_obj.notify_flag IS NOT NULL
1019 THEN
1020 l_notify_flag := p_mychannel_obj.notify_flag;
1021 END IF;
1022
1023 IF p_mychannel_obj.notification_interval_type = FND_API.G_MISS_CHAR THEN
1024 l_notif_interval_type := p_mychannel_obj.notification_interval_type;
1025 END IF;
1026
1027 UPDATE amv_u_my_channels
1028 SET order_number = l_order_number
1029 , status = l_status
1030 , notify_flag = l_notify_flag
1031 , notification_interval_type = l_notif_interval_type
1032 WHERE my_channel_id = l_mychannel_id;
1033 --
1034
1035 -- Success message
1036 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1037 THEN
1038 FND_MESSAGE.Set_Name('AMV', 'AMV_API_SUCCESS_MESSAGE');
1039 FND_MESSAGE.Set_Token('ROW',l_full_name) ;
1040 FND_MSG_PUB.Add;
1041 END IF;
1042 --Standard check of commit
1043 IF FND_API.To_Boolean ( p_commit ) THEN
1044 COMMIT WORK;
1045 END IF;
1046 -- Debug Message
1047 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1048 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1049 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1050 FND_MSG_PUB.Add;
1051 END IF;
1052 --Standard call to get message count and if count=1, get the message
1053 FND_MSG_PUB.Count_And_Get (
1054 p_encoded => FND_API.G_FALSE,
1055 p_count => x_msg_count,
1056 p_data => x_msg_data
1057 );
1058 EXCEPTION
1059 WHEN FND_API.G_EXC_ERROR THEN
1060 ROLLBACK TO Update_MyChannel_PVT;
1061 x_return_status := FND_API.G_RET_STS_ERROR;
1062 -- Standard call to get message count and if count=1, get the message
1063 FND_MSG_PUB.Count_And_Get (
1064 p_encoded => FND_API.G_FALSE,
1065 p_count => x_msg_count,
1066 p_data => x_msg_data
1067 );
1068 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1069 ROLLBACK TO Update_MyChannel_PVT;
1070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1071 -- Standard call to get message count and if count=1, get the message
1072 FND_MSG_PUB.Count_And_Get (
1073 p_encoded => FND_API.G_FALSE,
1074 p_count => x_msg_count,
1075 p_data => x_msg_data
1076 );
1077 WHEN OTHERS THEN
1078 ROLLBACK TO Update_MyChannel_PVT;
1079 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1080 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1081 THEN
1082 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1083 END IF;
1084 -- Standard call to get message count and if count=1, get the message
1085 FND_MSG_PUB.Count_And_Get (
1086 p_encoded => FND_API.G_FALSE,
1087 p_count => x_msg_count,
1088 p_data => x_msg_data
1089 );
1090 --
1091 END Update_MyChannel;
1092 --------------------------------------------------------------------------------
1093 -- Start of comments
1094 -- API name : Get_MyChannels
1095 -- Type : Private
1096 -- Pre-reqs : None
1097 -- Function : Get all the channels for a given user (p_user_id)
1098 -- Parameters :
1099 -- IN p_api_version IN NUMBER Required
1100 -- p_init_msg_list IN VARCHAR2 Optional
1101 -- Default = FND_API.G_FALSE
1102 -- p_validation_level IN NUMBER Optional
1103 -- Default = FND_API.G_VALID_LEVEL_FULL
1104 -- p_check_login_user IN VARCHAR2 Optional
1105 -- Default = FND_API.G_TRUE
1106 -- p_user_id IN NUMBER Required
1107 -- the given user
1108 -- OUT : x_return_status OUT VARCHAR2
1109 -- x_msg_count OUT NUMBER
1110 -- x_msg_data OUT VARCHAR2
1111 -- x_mychannel_array OUT AMV_MY_CHANNEL_VARRAY_TYPE
1112 -- Version : Current version 1.0
1113 -- Previous version 1.0
1114 -- Initial version 1.0
1115 -- Notes :
1116 --
1117 -- End of comments
1118 --
1119 PROCEDURE Get_MyChannels
1120 ( p_api_version IN NUMBER,
1121 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1122 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1123 x_return_status OUT NOCOPY VARCHAR2,
1124 x_msg_count OUT NOCOPY NUMBER,
1125 x_msg_data OUT NOCOPY VARCHAR2,
1126 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1127 p_user_id IN NUMBER,
1128 x_mychannel_array OUT NOCOPY AMV_MY_CHANNEL_VARRAY_TYPE
1129 )
1130 IS
1131 l_api_name CONSTANT VARCHAR2(30) := 'Get_MyChannels';
1132 l_api_version CONSTANT NUMBER := 1.0;
1133 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1134 --
1135 l_resource_id number;
1136 l_user_id number;
1137 l_login_user_id number;
1138 l_login_user_status varchar2(30);
1139 l_Error_Msg varchar2(2000);
1140 l_Error_Token varchar2(80);
1141 --
1142 l_mychannel_id number;
1143 l_user_or_group_id number;
1144 l_user_or_group_type varchar2(30);
1145 l_subscribing_to_id number;
1146 l_subscribing_to_type varchar2(30);
1147 l_subscription_reason_type varchar2(30);
1148 l_order_number number;
1149 l_status varchar2(30);
1150 l_notify_flag varchar2(1);
1151 l_notification_interval_type varchar2(30);
1152 l_record_count number := 1;
1153 l_channel_type varchar2(30);
1154 l_access_level_type varchar2(30);
1155 l_channel_id number;
1156 l_start_date date;
1157 l_end_date date;
1158 --
1159 CURSOR My_Channels IS
1160 select my_channel_id
1161 , user_or_group_id
1162 , user_or_group_type
1163 , subscribing_to_id
1164 , subscribing_to_type
1165 , subscription_reason_type
1166 , order_number
1167 , status
1168 , notify_flag
1169 , notification_interval_type
1170 from amv_u_my_channels
1171 where user_or_group_id = p_user_id
1172 and user_or_group_type = AMV_UTILITY_PVT.G_USER
1173 union
1174 select my_channel_id
1175 , user_or_group_id
1176 , user_or_group_type
1177 , subscribing_to_id
1178 , subscribing_to_type
1179 , subscription_reason_type
1180 , order_number
1181 , status
1182 , notify_flag
1183 , notification_interval_type
1184 from amv_u_my_channels
1185 where user_or_group_id in (select group_id
1186 from jtf_rs_group_members
1187 where resource_id = p_user_id and
1188 delete_flag = 'N')
1189 and user_or_group_type = AMV_UTILITY_PVT.G_GROUP;
1190
1191 CURSOR Get_ChannelType_csr IS
1192 select channel_type
1193 , access_level_type
1194 , effective_start_date
1195 , nvl(expiration_date,sysdate)
1196 from amv_c_channels_b
1197 where channel_id = l_channel_id;
1198 --
1199 BEGIN
1200 -- Standard begin of API savepoint
1201 SAVEPOINT Get_MyChannels_PVT;
1202 -- Standard call to check for call compatibility.
1203 IF NOT FND_API.Compatible_API_Call (
1204 l_api_version,
1205 p_api_version,
1206 l_api_name,
1207 G_PKG_NAME)
1208 THEN
1209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1210 END IF;
1211 -- Debug Message
1212 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1213 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1214 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1215 FND_MSG_PUB.Add;
1216 END IF;
1217 --Initialize message list if p_init_msg_list is TRUE.
1218 IF FND_API.To_Boolean (p_init_msg_list) THEN
1219 FND_MSG_PUB.initialize;
1220 END IF;
1221 -- Get the current (login) user id.
1222 AMV_UTILITY_PVT.Get_UserInfo(
1223 x_resource_id => l_resource_id,
1224 x_user_id => l_user_id,
1225 x_login_id => l_login_user_id,
1226 x_user_status => l_login_user_status
1227 );
1228 -- check login user
1229 IF (p_check_login_user = FND_API.G_TRUE) THEN
1230 -- Check if user is login and has the required privilege.
1231 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1232 -- User is not login.
1233 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1234 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1235 FND_MSG_PUB.Add;
1236 END IF;
1237 RAISE FND_API.G_EXC_ERROR;
1238 END IF;
1239 END IF;
1240 -- This fix is for executing api in sqlplus mode
1241 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1242 l_login_user_id := -1;
1243 l_user_id := -1;
1244 l_resource_id := -1;
1245 END IF;
1246 -- Initialize API return status to sucess
1247 x_return_status := FND_API.G_RET_STS_SUCCESS;
1248
1249 --
1250 IF AMV_UTILITY_PVT.Is_UserIdValid(p_user_id) THEN
1251 x_mychannel_array := AMV_MY_CHANNEL_VARRAY_TYPE();
1252 OPEN My_Channels;
1253 LOOP
1254 FETCH My_Channels INTO l_mychannel_id,
1255 l_user_or_group_id,
1256 l_user_or_group_type,
1257 l_subscribing_to_id,
1258 l_subscribing_to_type,
1259 l_subscription_reason_type,
1260 l_order_number,
1261 l_status,
1262 l_notify_flag,
1263 l_notification_interval_type;
1264 EXIT WHEN My_Channels%NOTFOUND;
1265 IF l_subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL THEN
1266 l_channel_id := l_subscribing_to_id;
1267 OPEN Get_ChannelType_csr;
1268 FETCH Get_ChannelType_csr INTO l_channel_type,
1269 l_access_level_type,
1270 l_start_date,
1271 l_end_date;
1272 CLOSE Get_ChannelType_csr;
1273 ELSE
1274 l_channel_type := null;
1275 l_access_level_type := null;
1276 l_start_date := sysdate;
1277 l_end_date := sysdate;
1278 END IF;
1279 IF (l_start_date <= sysdate) AND
1280 (l_end_date >= sysdate)
1281 THEN
1282 x_mychannel_array.extend;
1283 x_mychannel_array(l_record_count).my_channel_id := l_mychannel_id;
1284 x_mychannel_array(l_record_count).channel_type := l_channel_type;
1285 x_mychannel_array(l_record_count).access_level_type :=
1286 l_access_level_type;
1287 x_mychannel_array(l_record_count).user_or_group_id :=
1288 l_user_or_group_id;
1289 x_mychannel_array(l_record_count).user_or_group_type :=
1290 l_user_or_group_type;
1291 x_mychannel_array(l_record_count).subscribing_to_id :=
1292 l_subscribing_to_id;
1293 x_mychannel_array(l_record_count).subscribing_to_type:=
1294 l_subscribing_to_type;
1295 x_mychannel_array(l_record_count).subscription_reason_type :=
1296 l_subscription_reason_type;
1297 x_mychannel_array(l_record_count).order_number := l_order_number;
1298 x_mychannel_array(l_record_count).status :=l_status;
1299 x_mychannel_array(l_record_count).notify_flag := l_notify_flag;
1300 x_mychannel_array(l_record_count).notification_interval_type :=
1301 l_notification_interval_type;
1302 /*
1303 x_mychannel_array(l_record_count) :=
1304 amv_my_channel_obj_type(
1305 l_mychannel_id,
1306 l_channel_type,
1307 l_access_level_type,
1308 l_user_or_group_id,
1309 l_user_or_group_type,
1310 l_subscribing_to_id,
1311 l_subscribing_to_type,
1312 l_subscription_reason_type,
1313 l_order_number,
1314 l_status,
1315 l_notify_flag,
1316 l_notification_interval_type);
1317 */
1318 l_record_count := l_record_count + 1;
1319 END IF;
1320 END LOOP;
1321 CLOSE My_Channels;
1322
1323 ELSE
1324 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1325 THEN
1326 FND_MESSAGE.Set_Name('AMV', 'AMV_RESOURCE_ID_INVALID');
1327 FND_MESSAGE.Set_Token('TKN',p_user_id);
1328 FND_MSG_PUB.Add;
1329 END IF;
1330 RAISE FND_API.G_EXC_ERROR;
1331 END IF;
1332 --
1333
1334 -- Debug Message
1335 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1336 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1337 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1338 FND_MSG_PUB.Add;
1339 END IF;
1340 --Standard call to get message count and if count=1, get the message
1341 FND_MSG_PUB.Count_And_Get (
1342 p_encoded => FND_API.G_FALSE,
1343 p_count => x_msg_count,
1344 p_data => x_msg_data
1345 );
1346 EXCEPTION
1347 WHEN FND_API.G_EXC_ERROR THEN
1348 ROLLBACK TO Get_MyChannels_PVT;
1349 x_return_status := FND_API.G_RET_STS_ERROR;
1350 -- Standard call to get message count and if count=1, get the message
1351 FND_MSG_PUB.Count_And_Get (
1352 p_encoded => FND_API.G_FALSE,
1353 p_count => x_msg_count,
1354 p_data => x_msg_data
1355 );
1356 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1357 ROLLBACK TO Get_MyChannels_PVT;
1358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1359 -- Standard call to get message count and if count=1, get the message
1360 FND_MSG_PUB.Count_And_Get (
1361 p_encoded => FND_API.G_FALSE,
1362 p_count => x_msg_count,
1363 p_data => x_msg_data
1364 );
1365 WHEN OTHERS THEN
1366 ROLLBACK TO Get_MyChannels_PVT;
1367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1368 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1369 THEN
1370 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1371 END IF;
1372 -- Standard call to get message count and if count=1, get the message
1373 FND_MSG_PUB.Count_And_Get (
1374 p_encoded => FND_API.G_FALSE,
1375 p_count => x_msg_count,
1376 p_data => x_msg_data
1377 );
1378 --
1379 END Get_MyChannels;
1380 --------------------------------------------------------------------------------
1381 -- Start of comments
1382 -- API name : Get_MyChannelsPerCategory
1383 -- Type : Private
1384 -- Pre-reqs : None
1385 -- Function : Get all channels in a category which an user has access to
1386 -- Parameters :
1387 -- IN p_api_version IN NUMBER Required
1388 -- p_init_msg_list IN VARCHAR2 Optional
1389 -- Default = FND_API.G_FALSE
1390 -- p_validation_level IN NUMBER Optional
1391 -- Default = FND_API.G_VALID_LEVEL_FULL
1392 -- p_check_login_user IN VARCHAR2 Optional
1393 -- Default = FND_API.G_TRUE
1394 -- p_user_id IN NUMBER Required
1395 -- the given user
1396 -- p_category_id IN NUMBER Required
1397 -- OUT : x_return_status OUT VARCHAR2
1398 -- x_msg_count OUT NUMBER
1399 -- x_msg_data OUT VARCHAR2
1400 -- x_channel_array OUT AMV_NUMBER_VARRAY_TYPE
1401 -- Version : Current version 1.0
1402 -- Previous version 1.0
1403 -- Initial version 1.0
1404 -- Notes :
1405 --
1406 -- End of comments
1407 --
1408 PROCEDURE Get_MyChannelsPerCategory
1409 ( p_api_version IN NUMBER,
1410 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1411 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1412 x_return_status OUT NOCOPY VARCHAR2,
1413 x_msg_count OUT NOCOPY NUMBER,
1414 x_msg_data OUT NOCOPY VARCHAR2,
1415 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1416 p_user_id IN NUMBER,
1417 p_category_id IN NUMBER,
1418 x_channel_array OUT NOCOPY AMV_NUMBER_VARRAY_TYPE
1419 )
1420 IS
1421 l_api_name CONSTANT VARCHAR2(30) := 'Get_MyChannelsPerCategory';
1422 l_api_version CONSTANT NUMBER := 1.0;
1423 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1424 --
1425 l_resource_id number;
1426 l_user_id number;
1427 l_login_user_id number;
1428 l_login_user_status varchar2(30);
1429 l_Error_Msg varchar2(2000);
1430 l_Error_Token varchar2(80);
1431 --
1432 l_channel_id number;
1433 l_record_count number;
1434 --
1435 -- NOTE Channels should be based on user privilege
1436 CURSOR Get_CategoryChannels IS
1437 select channel_id
1438 from amv_c_channels_b
1439 where channel_category_id in (
1440 select b1.channel_category_id
1441 from amv_c_categories_b b1
1442 where b1.channel_category_id = p_category_id
1443 or b1.channel_category_id in (
1444 select b2.channel_category_id
1445 from amv_c_categories_b b2
1446 where b2.parent_channel_category_id = p_category_id))
1447 and channel_type = AMV_UTILITY_PVT.G_CONTENT
1448 and access_level_type = AMV_UTILITY_PVT.G_PUBLIC;
1449 --
1450 BEGIN
1451 -- Standard begin of API savepoint
1452 SAVEPOINT Get_MyChannelsPerCategory_PVT;
1453 -- Standard call to check for call compatibility.
1454 IF NOT FND_API.Compatible_API_Call (
1455 l_api_version,
1456 p_api_version,
1457 l_api_name,
1458 G_PKG_NAME)
1459 THEN
1460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1461 END IF;
1462 -- Debug Message
1463 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1464 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1465 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1466 FND_MSG_PUB.Add;
1467 END IF;
1468 --Initialize message list if p_init_msg_list is TRUE.
1469 IF FND_API.To_Boolean (p_init_msg_list) THEN
1470 FND_MSG_PUB.initialize;
1471 END IF;
1472 -- Get the current (login) user id.
1473 AMV_UTILITY_PVT.Get_UserInfo(
1474 x_resource_id => l_resource_id,
1475 x_user_id => l_user_id,
1476 x_login_id => l_login_user_id,
1477 x_user_status => l_login_user_status
1478 );
1479 -- check login user
1480 IF (p_check_login_user = FND_API.G_TRUE) THEN
1481 -- Check if user is login and has the required privilege.
1482 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1483 -- User is not login.
1484 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1485 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1486 FND_MSG_PUB.Add;
1487 END IF;
1488 RAISE FND_API.G_EXC_ERROR;
1489 END IF;
1490 END IF;
1491 -- This fix is for executing api in sqlplus mode
1492 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1493 l_login_user_id := -1;
1494 l_user_id := -1;
1495 l_resource_id := -1;
1496 END IF;
1497 -- Initialize API return status to sucess
1498 x_return_status := FND_API.G_RET_STS_SUCCESS;
1499
1500 --
1501 OPEN Get_CategoryChannels;
1502 x_channel_array := AMV_NUMBER_VARRAY_TYPE();
1503 LOOP
1504 FETCH Get_CategoryChannels INTO l_channel_id;
1505 EXIT WHEN Get_CategoryChannels%NOTFOUND;
1506 x_channel_array.extend;
1507 x_channel_array(l_record_count) := l_channel_id;
1508 l_record_count := l_record_count + 1;
1509 END LOOP;
1510 CLOSE Get_CategoryChannels;
1511 --
1512
1513 -- Debug Message
1514 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1515 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1516 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1517 FND_MSG_PUB.Add;
1518 END IF;
1519 --Standard call to get message count and if count=1, get the message
1520 FND_MSG_PUB.Count_And_Get (
1521 p_encoded => FND_API.G_FALSE,
1522 p_count => x_msg_count,
1523 p_data => x_msg_data
1524 );
1525 EXCEPTION
1526 WHEN FND_API.G_EXC_ERROR THEN
1527 ROLLBACK TO Get_MyChannelsPerCategory_PVT;
1528 x_return_status := FND_API.G_RET_STS_ERROR;
1529 -- Standard call to get message count and if count=1, get the message
1530 FND_MSG_PUB.Count_And_Get (
1531 p_encoded => FND_API.G_FALSE,
1532 p_count => x_msg_count,
1533 p_data => x_msg_data
1534 );
1535 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1536 ROLLBACK TO Get_MyChannelsPerCategory_PVT;
1537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1538 -- Standard call to get message count and if count=1, get the message
1539 FND_MSG_PUB.Count_And_Get (
1540 p_encoded => FND_API.G_FALSE,
1541 p_count => x_msg_count,
1542 p_data => x_msg_data
1543 );
1544 WHEN OTHERS THEN
1545 ROLLBACK TO Get_MyChannelsPerCategory_PVT;
1546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1547 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1548 THEN
1549 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1550 END IF;
1551 -- Standard call to get message count and if count=1, get the message
1552 FND_MSG_PUB.Count_And_Get (
1553 p_encoded => FND_API.G_FALSE,
1554 p_count => x_msg_count,
1555 p_data => x_msg_data
1556 );
1557 --
1558 END Get_MyChannelsPerCategory;
1559 --------------------------------------------------------------------------------
1560 --------------------------------------------------------------------------------
1561 -- Start of comments
1562 -- API name : Get_MyNotifications
1563 -- Type : Private
1564 -- Pre-reqs : None
1565 -- Function : Get all channels in a category which an user has access to
1566 -- Parameters :
1567 -- IN p_api_version IN NUMBER Required
1568 -- p_init_msg_list IN VARCHAR2 Optional
1569 -- Default = FND_API.G_FALSE
1570 -- p_validation_level IN NUMBER Optional
1571 -- Default = FND_API.G_VALID_LEVEL_FULL
1572 -- p_check_login_user IN VARCHAR2 Optional
1573 -- Default = FND_API.G_TRUE
1574 -- p_resource_id IN NUMBER Optional
1575 -- Default = FND_API.G_MISS_NUM
1576 -- resource manager user id
1577 -- p_user_id IN NUMBER Optional
1578 -- Default = FND_API.G_MISS_NUM
1579 -- fnd user id
1580 -- p_user_name IN VARCHAR2 Optional
1581 -- Default = FND_API.G_MISS_CHAR
1582 -- fnd user name
1583 -- p_notification_type IN VARCHAR2 Optional
1584 -- OUT : x_return_status OUT VARCHAR2
1585 -- x_msg_count OUT NUMBER
1586 -- x_msg_data OUT VARCHAR2
1587 -- x_notifications_array OUT AMV_WF_NOTIF_VARRAY_TYPE
1588 -- Version : Current version 1.0
1589 -- Previous version 1.0
1590 -- Initial version 1.0
1591 -- Notes :
1592 --
1593 -- End of comments
1594 --
1595 PROCEDURE Get_MyNotifications
1596 ( p_api_version IN NUMBER,
1597 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1598 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1599 x_return_status OUT NOCOPY VARCHAR2,
1600 x_msg_count OUT NOCOPY NUMBER,
1601 x_msg_data OUT NOCOPY VARCHAR2,
1602 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1603 p_resource_id IN NUMBER := FND_API.G_MISS_NUM,
1604 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
1605 p_user_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1606 p_notification_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
1607 x_notification_url OUT NOCOPY VARCHAR2,
1608 x_notifications_array OUT NOCOPY AMV_WF_NOTIF_VARRAY_TYPE
1609 )
1610 IS
1611 l_api_name CONSTANT VARCHAR2(30) := 'Get_MyNotifications';
1612 l_api_version CONSTANT NUMBER := 1.0;
1613 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1614 --
1615 l_resource_id number;
1616 l_user_id number;
1617 l_login_user_id number;
1618 l_login_user_status varchar2(30);
1619 l_Error_Msg varchar2(2000);
1620 l_Error_Token varchar2(80);
1621 --
1622 l_notification_id number;
1623 l_subject varchar2(4000);
1624 l_begin_date date;
1625 l_end_date date;
1626 l_due_date date;
1627 l_status varchar2(30);
1628 l_priority number;
1629 l_type varchar2(30);
1630 l_recipient_role varchar2(30);
1631 l_ntf_type varchar2(8);
1632 l_msg1_type varchar2(30);
1633 l_msg2_type varchar2(30);
1634 l_rec_num number := 1;
1635 --
1636 CURSOR Get_Approvals IS
1637 SELECT N.NOTIFICATION_ID
1638 FROM WF_NOTIFICATIONS N
1639 WHERE N.RECIPIENT_ROLE = l_recipient_role
1640 AND N.MESSAGE_TYPE = 'AMV_APPR'
1641 AND N.STATUS = l_status
1642 AND EXISTS
1643 (SELECT NULL
1644 FROM WF_MESSAGE_ATTRIBUTES MA
1645 WHERE N.MESSAGE_TYPE = MA.MESSAGE_TYPE
1646 AND N.MESSAGE_NAME = MA.MESSAGE_NAME
1647 AND MA.SUBTYPE = 'RESPOND');
1648
1649 CURSOR Get_Notifications IS
1650 select DISTINCT N.NOTIFICATION_ID
1651 FROM WF_NOTIFICATIONS_VIEW N
1652 , WF_MESSAGE_ATTRIBUTES_VL MA
1653 WHERE N.RECIPIENT_ROLE = l_recipient_role
1654 AND N.MESSAGE_TYPE = MA.MESSAGE_TYPE
1655 AND N.MESSAGE_NAME = MA.MESSAGE_NAME
1656 AND MA.MESSAGE_NAME IN (
1657 select x.message_name
1658 from wf_message_attributes_vl x
1659 where x.subtype = 'SEND'
1660 and NOT EXISTS(
1661 select '1'
1662 from wf_message_attributes_vl b
1663 where b.subtype = 'RESPOND'
1664 and b.message_name = MA.MESSAGE_NAME)
1665 )
1666 AND N.MESSAGE_TYPE = 'AMV_APPR'
1667 AND N.STATUS = l_status;
1668
1669 CURSOR Get_NotifDetails IS
1670 select SUBJECT
1671 , BEGIN_DATE
1672 , END_DATE
1673 , DUE_DATE
1674 , STATUS
1675 , PRIORITY
1676 FROM WF_NOTIFICATIONS_VIEW
1677 WHERE NOTIFICATION_ID = l_notification_id
1678 AND RECIPIENT_ROLE = l_recipient_role
1679 AND STATUS = l_status
1680 ORDER BY BEGIN_DATE;
1681
1682 CURSOR Get_ResourceUserName IS
1683 select FND.USER_NAME
1684 from JTF_RS_RESOURCE_EXTNS RD
1685 , FND_USER FND
1686 where RD.USER_ID = FND.USER_ID
1687 and RD.RESOURCE_ID = p_resource_id;
1688
1689 CURSOR Get_FndUserName IS
1690 select USER_NAME
1691 from FND_USER
1692 where USER_ID = p_user_id;
1693
1694 -- Getting workflow web agent
1695 CURSOR Get_WebAgent IS
1696 select text from wf_resources
1697 where name = 'WF_WEB_AGENT'
1698 and language = 'US';
1699
1700 --
1701 BEGIN
1702 -- Standard begin of API savepoint
1703 SAVEPOINT Get_MyNotifications;
1704 -- Standard call to check for call compatibility.
1705 IF NOT FND_API.Compatible_API_Call (
1706 l_api_version,
1707 p_api_version,
1708 l_api_name,
1709 G_PKG_NAME)
1710 THEN
1711 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1712 END IF;
1713 -- Debug Message
1714 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1715 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1716 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
1717 FND_MSG_PUB.Add;
1718 END IF;
1719 --Initialize message list if p_init_msg_list is TRUE.
1720 IF FND_API.To_Boolean (p_init_msg_list) THEN
1721 FND_MSG_PUB.initialize;
1722 END IF;
1723 -- Get the current (login) user id.
1724 AMV_UTILITY_PVT.Get_UserInfo(
1725 x_resource_id => l_resource_id,
1726 x_user_id => l_user_id,
1727 x_login_id => l_login_user_id,
1728 x_user_status => l_login_user_status
1729 );
1730 -- check login user
1731 IF (p_check_login_user = FND_API.G_TRUE) THEN
1732 -- Check if user is login and has the required privilege.
1733 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1734 -- User is not login.
1735 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1736 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1737 FND_MSG_PUB.Add;
1738 END IF;
1739 RAISE FND_API.G_EXC_ERROR;
1740 END IF;
1741 END IF;
1742 -- This fix is for executing api in sqlplus mode
1743 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
1744 l_login_user_id := -1;
1745 l_user_id := -1;
1746 l_resource_id := -1;
1747 END IF;
1748 -- Initialize API return status to sucess
1749 x_return_status := FND_API.G_RET_STS_SUCCESS;
1750
1751 x_notification_url := wf_core.translate('WF_WEB_AGENT');
1752 --
1753 IF p_user_id = FND_API.G_MISS_NUM OR
1754 p_user_id is null
1755 THEN
1756 IF p_resource_id = FND_API.G_MISS_NUM OR
1757 p_resource_id is null
1758 THEN
1759 IF p_user_name is not null THEN
1760 l_recipient_role := p_user_name;
1761 ELSE
1762 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1763 THEN
1764 FND_MESSAGE.Set_Name('AMV', 'AMV_NOTIF_NO_VALS');
1765 FND_MSG_PUB.Add;
1766 END IF;
1767 RAISE FND_API.G_EXC_ERROR;
1768 END IF;
1769 ELSE
1770 OPEN Get_ResourceUserName;
1771 FETCH Get_ResourceUserName INTO l_recipient_role;
1772 CLOSE Get_ResourceUserName;
1773 IF l_recipient_role is null THEN
1774 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1775 THEN
1776 -- NOTE change token name CATEGORY to generic name
1777 FND_MESSAGE.Set_Name('AMV', 'AMV_RESOURCE_ID_INVALID');
1778 FND_MESSAGE.Set_Token('TKN',p_resource_id);
1779 FND_MSG_PUB.Add;
1780 END IF;
1781 RAISE FND_API.G_EXC_ERROR;
1782 END IF;
1783 END IF;
1784 ELSE
1785 OPEN Get_FndUserName;
1786 FETCH Get_FndUserName INTO l_recipient_role;
1787 CLOSE Get_FndUserName;
1788 IF l_recipient_role is null THEN
1789 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1790 THEN
1791 -- NOTE change token name CATEGORY to generic name
1792 FND_MESSAGE.Set_Name('AMV', 'AMV_INVALID_USER_ID');
1793 FND_MESSAGE.Set_Token('TKN',p_user_id);
1794 FND_MSG_PUB.Add;
1795 END IF;
1796 RAISE FND_API.G_EXC_ERROR;
1797 END IF;
1798 END IF;
1799
1800 IF p_notification_type = 'NOTIFICATION' THEN
1801 l_status := 'OPEN';
1802 x_notifications_array := AMV_WF_NOTIF_VARRAY_TYPE();
1803 OPEN Get_Notifications;
1804 LOOP
1805 FETCH Get_Notifications INTO l_notification_id;
1806 EXIT WHEN Get_Notifications%NOTFOUND;
1807
1808 OPEN Get_NotifDetails;
1809 FETCH Get_NotifDetails INTO
1810 l_subject,
1811 l_begin_date,
1812 l_end_date,
1813 l_due_date,
1814 l_status,
1815 l_priority;
1816 CLOSE Get_NotifDetails;
1817 x_notifications_array.extend;
1818 x_notifications_array(l_rec_num).notification_id := l_notification_id;
1819 x_notifications_array(l_rec_num).subject := l_subject;
1820 x_notifications_array(l_rec_num).begin_date := l_begin_date;
1821 x_notifications_array(l_rec_num).end_date := l_end_date;
1822 x_notifications_array(l_rec_num).due_date := l_due_date;
1823 x_notifications_array(l_rec_num).status := l_status;
1824 x_notifications_array(l_rec_num).priority := l_priority;
1825 x_notifications_array(l_rec_num).type := p_notification_type;
1826 /*
1827 x_notifications_array(l_rec_num) :=
1828 amv_wf_notif_obj_type(
1829 l_notification_id,
1830 l_subject,
1831 l_begin_date,
1832 l_end_date,
1833 l_due_date,
1834 l_status,
1835 l_priority,
1836 p_notification_type );
1837 */
1838 l_rec_num := l_rec_num + 1;
1839 END LOOP;
1840 CLOSE Get_Notifications;
1841 ELSIF p_notification_type = 'APPROVAL' THEN
1842 l_status := 'OPEN';
1843 x_notifications_array := AMV_WF_NOTIF_VARRAY_TYPE();
1844 OPEN Get_Approvals;
1845 LOOP
1846 FETCH Get_Approvals INTO l_notification_id;
1847 EXIT WHEN Get_Approvals%NOTFOUND;
1848
1849 OPEN Get_NotifDetails;
1850 FETCH Get_NotifDetails INTO
1851 l_subject,
1852 l_begin_date,
1853 l_end_date,
1854 l_due_date,
1855 l_status,
1856 l_priority;
1857 CLOSE Get_NotifDetails;
1858 x_notifications_array.extend;
1859 x_notifications_array(l_rec_num).notification_id := l_notification_id;
1860 x_notifications_array(l_rec_num).subject := l_subject;
1861 x_notifications_array(l_rec_num).begin_date := l_begin_date;
1862 x_notifications_array(l_rec_num).end_date := l_end_date;
1863 x_notifications_array(l_rec_num).due_date := l_due_date;
1864 x_notifications_array(l_rec_num).status := l_status;
1865 x_notifications_array(l_rec_num).priority := l_priority;
1866 x_notifications_array(l_rec_num).type := p_notification_type;
1867 /*
1868 x_notifications_array(l_rec_num) :=
1869 amv_wf_notif_obj_type(
1870 l_notification_id,
1871 l_subject,
1872 l_begin_date,
1873 l_end_date,
1874 l_due_date,
1875 l_status,
1876 l_priority,
1877 p_notification_type );
1878 */
1879 l_rec_num := l_rec_num + 1;
1880 END LOOP;
1881 CLOSE Get_Approvals;
1882 END IF;
1883 --
1884 --
1885
1886 -- Debug Message
1887 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1888 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
1889 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1890 FND_MSG_PUB.Add;
1891 END IF;
1892 --Standard call to get message count and if count=1, get the message
1893 FND_MSG_PUB.Count_And_Get (
1894 p_encoded => FND_API.G_FALSE,
1895 p_count => x_msg_count,
1896 p_data => x_msg_data
1897 );
1898 EXCEPTION
1899 WHEN FND_API.G_EXC_ERROR THEN
1900 ROLLBACK TO Get_MyNotifications;
1901 x_return_status := FND_API.G_RET_STS_ERROR;
1902 -- Standard call to get message count and if count=1, get the message
1903 FND_MSG_PUB.Count_And_Get (
1904 p_encoded => FND_API.G_FALSE,
1905 p_count => x_msg_count,
1906 p_data => x_msg_data
1907 );
1908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1909 ROLLBACK TO Get_MyNotifications;
1910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1911 -- Standard call to get message count and if count=1, get the message
1912 FND_MSG_PUB.Count_And_Get (
1913 p_encoded => FND_API.G_FALSE,
1914 p_count => x_msg_count,
1915 p_data => x_msg_data
1916 );
1917 WHEN OTHERS THEN
1918 ROLLBACK TO Get_MyNotifications;
1919 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1920 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1921 THEN
1922 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1923 END IF;
1924 -- Standard call to get message count and if count=1, get the message
1925 FND_MSG_PUB.Count_And_Get (
1926 p_encoded => FND_API.G_FALSE,
1927 p_count => x_msg_count,
1928 p_data => x_msg_data
1929 );
1930 --
1931 END Get_MyNotifications;
1932 --------------------------------------------------------------------------------
1933 --------------------------------------------------------------------------------
1934 -- Start of comments
1935 -- API name : Get_ItemsPerUser
1936 -- Type : Private
1937 -- Pre-reqs : None
1938 -- Function : Return all items a user can access based channel/cat access
1939 -- Parameters :
1940 -- IN p_api_version IN NUMBER Required
1941 -- p_init_msg_list IN VARCHAR2 Optional
1942 -- Default = FND_API.G_FALSE
1943 -- p_validation_level IN NUMBER Optional
1944 -- Default = FND_API.G_VALID_LEVEL_FULL
1945 -- p_category_id IN NUMBER Required
1946 -- p_include_subcats IN VARCHAR2 Optional
1947 -- Default = FND_API.G_FALSE
1948 -- OUT : x_return_status OUT VARCHAR2
1949 -- x_msg_count OUT NUMBER
1950 -- x_msg_data OUT VARCHAR2
1951 -- x_items_array OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
1952 -- Version : Current version 1.0
1953 -- Previous version 1.0
1954 -- Initial version 1.0
1955 -- Notes :
1956 --
1957 -- End of comments
1958 --
1959 PROCEDURE Get_ItemsPerUser
1960 ( p_api_version IN NUMBER,
1961 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1962 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1963 x_return_status OUT NOCOPY VARCHAR2,
1964 x_msg_count OUT NOCOPY NUMBER,
1965 x_msg_data OUT NOCOPY VARCHAR2,
1966 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1967 p_user_id IN NUMBER,
1968 p_request_obj IN AMV_REQUEST_OBJ_TYPE,
1969 x_return_obj OUT NOCOPY AMV_RETURN_OBJ_TYPE,
1970 x_items_array OUT NOCOPY AMV_CAT_HIERARCHY_VARRAY_TYPE
1971 )
1972 IS
1973 l_api_name CONSTANT VARCHAR2(30) := 'Get_ItemsPerUser';
1974 l_api_version CONSTANT NUMBER := 1.0;
1975 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1976 --
1977 l_resource_id number;
1978 l_user_id number;
1979 l_login_user_id number;
1980 l_login_user_status varchar2(30);
1981 l_Error_Msg varchar2(2000);
1982 l_Error_Token varchar2(80);
1983 l_object_version_number number;
1984 l_application_id number;
1985 --
1986 l_record_count NUMBER := 0;
1987 l_total_count NUMBER := 0;
1988 l_temp_total NUMBER := 0;
1989 l_counter NUMBER := 1;
1990 l_channel_id number;
1991 l_category_level number := 1;
1992 l_category_hr amv_cat_hierarchy_varray_type;
1993 l_item_id number;
1994 l_item_name varchar2(240);
1995
1996 -- NOTE not used now reason bug fix 3260137
1997 CURSOR Get_MyChannels IS
1998 select subscribing_to_id
1999 from amv_u_my_channels
2000 where user_or_group_id in (select group_id
2001 from jtf_rs_group_members
2002 where resource_id = p_user_id)
2003 and user_or_group_type = AMV_UTILITY_PVT.G_GROUP
2004 and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL
2005 union
2006 select subscribing_to_id
2007 from amv_u_my_channels
2008 where user_or_group_id = p_user_id
2009 and user_or_group_type = AMV_UTILITY_PVT.G_USER
2010 and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL;
2011
2012 -- NOTE currently not used
2013 CURSOR Get_MyCategories IS
2014 select subscribing_to_id
2015 from amv_u_my_channels
2016 where user_or_group_id in (select group_id
2017 from jtf_rs_group_members
2018 where resource_id = p_user_id
2019 and delete_flag <> 'Y')
2020 and user_or_group_type = AMV_UTILITY_PVT.G_GROUP
2021 and subscribing_to_type = AMV_UTILITY_PVT.G_CATEGORY
2022 union
2023 select subscribing_to_id
2024 from amv_u_my_channels
2025 where user_or_group_id = p_user_id
2026 and user_or_group_type = AMV_UTILITY_PVT.G_USER
2027 and subscribing_to_type = AMV_UTILITY_PVT.G_CATEGORY;
2028
2029 CURSOR Get_CategoryItems_csr IS
2030 select ib.item_id
2031 , ib.item_name
2032 , cim.channel_id
2033 from amv_c_chl_item_match cim
2034 , jtf_amv_items_vl ib
2035 where cim.channel_id in (select subscribing_to_id
2036 from amv_u_my_channels
2037 where user_or_group_id in (select group_id
2038 from jtf_rs_group_members
2039 where resource_id = p_user_id)
2040 and user_or_group_type = AMV_UTILITY_PVT.G_GROUP
2041 and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL
2042 union
2043 select subscribing_to_id
2044 from amv_u_my_channels
2045 where user_or_group_id = p_user_id
2046 and user_or_group_type = AMV_UTILITY_PVT.G_USER
2047 and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL )
2048 and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
2049 and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
2050 and cim.available_for_channel_date <= sysdate
2051 and cim.item_id = ib.item_id
2052 and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
2053 and nvl(ib.expiration_date, sysdate) >= sysdate
2054 order by ib.effective_start_date desc;
2055
2056 CURSOR Get_ItemsTotal_csr IS
2057 select count(cim.item_id)
2058 from amv_c_chl_item_match cim
2059 , jtf_amv_items_vl ib
2060 where cim.channel_id in (select subscribing_to_id
2061 from amv_u_my_channels
2062 where user_or_group_id in (select group_id
2063 from jtf_rs_group_members
2064 where resource_id = p_user_id)
2065 and user_or_group_type = AMV_UTILITY_PVT.G_GROUP
2066 and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL
2067 union
2068 select subscribing_to_id
2069 from amv_u_my_channels
2070 where user_or_group_id = p_user_id
2071 and user_or_group_type = AMV_UTILITY_PVT.G_USER
2072 and subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL )
2073 and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
2074 and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
2075 and cim.available_for_channel_date <= sysdate
2076 and cim.item_id = ib.item_id
2077 and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
2078 and nvl(ib.expiration_date, sysdate) >= sysdate;
2079
2080 BEGIN
2081 -- Standard begin of API savepoint
2082 SAVEPOINT Get_ItemsPerUser;
2083 -- Standard call to check for call compatibility.
2084 IF NOT FND_API.Compatible_API_Call (
2085 l_api_version,
2086 p_api_version,
2087 l_api_name,
2088 G_PKG_NAME)
2089 THEN
2090 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2091 END IF;
2092 -- Debug Message
2093 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2094 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2095 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
2096 FND_MSG_PUB.Add;
2097 END IF;
2098 --Initialize message list if p_init_msg_list is TRUE.
2099 IF FND_API.To_Boolean (p_init_msg_list) THEN
2100 FND_MSG_PUB.initialize;
2101 END IF;
2102 -- Get the current (login) user id.
2103 AMV_UTILITY_PVT.Get_UserInfo(
2104 x_resource_id => l_resource_id,
2105 x_user_id => l_user_id,
2106 x_login_id => l_login_user_id,
2107 x_user_status => l_login_user_status
2108 );
2109 -- check login user
2110 IF (p_check_login_user = FND_API.G_TRUE) THEN
2111 -- Check if user is login and has the required privilege.
2112 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2113 -- User is not login.
2114 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2115 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2116 FND_MSG_PUB.Add;
2117 END IF;
2118 RAISE FND_API.G_EXC_ERROR;
2119 END IF;
2120 END IF;
2121 -- This fix is for executing api in sqlplus mode
2122 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2123 l_login_user_id := -1;
2124 l_user_id := -1;
2125 l_resource_id := -1;
2126 END IF;
2127 -- Initialize API return status to sucess
2128 x_return_status := FND_API.G_RET_STS_SUCCESS;
2129
2130 --
2131 x_items_array := AMV_CAT_HIERARCHY_VARRAY_TYPE();
2132
2133
2134
2135 -- get the total number of items in category
2136 OPEN Get_ItemsTotal_csr;
2137 FETCH Get_ItemsTotal_csr INTO l_total_count;
2138 CLOSE Get_ItemsTotal_csr;
2139
2140 IF l_total_count < p_request_obj.start_record_position THEN
2141 l_counter := l_total_count + 1;
2142 ELSE
2143 OPEN Get_CategoryItems_csr;
2144 LOOP
2145 FETCH Get_CategoryItems_csr INTO l_item_id, l_item_name, l_channel_id;
2146 EXIT WHEN Get_CategoryItems_csr%NOTFOUND;
2147
2148 IF (l_counter >= p_request_obj.start_record_position) AND
2149 (l_record_count <= p_request_obj.records_requested)
2150 THEN
2151 l_record_count := l_record_count + 1;
2152 x_items_array.extend;
2153 x_items_array(l_record_count).hierarchy_level := l_channel_id;
2154 x_items_array(l_record_count).id := l_item_id;
2155 x_items_array(l_record_count).name := l_item_name;
2156 /*
2157 x_items_array(l_record_count) :=
2158 amv_cat_hierarchy_obj_type( l_channel_id,
2159 l_item_id,
2160 l_item_name);
2161 */
2162 END IF;
2163 EXIT WHEN l_record_count = p_request_obj.records_requested;
2164 l_counter := l_counter + 1;
2165 END LOOP;
2166 CLOSE Get_CategoryItems_csr;
2167 END IF;
2168 x_return_obj.returned_record_count := l_record_count;
2169 x_return_obj.next_record_position :=
2170 p_request_obj.start_record_position + l_record_count;
2171 x_return_obj.total_record_count := l_total_count;
2172 /*
2173 x_return_obj := amv_return_obj_type(
2174 l_record_count,
2175 p_request_obj.start_record_position + l_record_count,
2176 l_total_count);
2177 */
2178 --
2179
2180 -- Debug Message
2181 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2182 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2183 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
2184 FND_MSG_PUB.Add;
2185 END IF;
2186 --Standard call to get message count and if count=1, get the message
2187 FND_MSG_PUB.Count_And_Get (
2188 p_encoded => FND_API.G_FALSE,
2189 p_count => x_msg_count,
2190 p_data => x_msg_data
2191 );
2192 EXCEPTION
2193 WHEN FND_API.G_EXC_ERROR THEN
2194 ROLLBACK TO Get_ItemsPerUser;
2195 x_return_status := FND_API.G_RET_STS_ERROR;
2196 -- Standard call to get message count and if count=1, get the message
2197 FND_MSG_PUB.Count_And_Get (
2198 p_encoded => FND_API.G_FALSE,
2199 p_count => x_msg_count,
2200 p_data => x_msg_data
2201 );
2202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2203 ROLLBACK TO Get_ItemsPerUser;
2204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2205 -- Standard call to get message count and if count=1, get the message
2206 FND_MSG_PUB.Count_And_Get (
2207 p_encoded => FND_API.G_FALSE,
2208 p_count => x_msg_count,
2209 p_data => x_msg_data
2210 );
2211 WHEN OTHERS THEN
2212 ROLLBACK TO Get_ItemsPerUser;
2213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2214 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2215 THEN
2216 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2217 END IF;
2218 -- Standard call to get message count and if count=1, get the message
2219 FND_MSG_PUB.Count_And_Get (
2220 p_encoded => FND_API.G_FALSE,
2221 p_count => x_msg_count,
2222 p_data => x_msg_data
2223 );
2224 --
2225 END Get_ItemsPerUser;
2226 --------------------------------------------------------------------------------
2227 --------------------------------------------------------------------------------
2228 -- Start of comments
2229 -- API name : Get_UserItems
2230 -- Type : Private
2231 -- Pre-reqs : None
2232 -- Function : Return all items user owns
2233 -- Parameters :
2234 -- IN p_api_version IN NUMBER Required
2235 -- p_init_msg_list IN VARCHAR2 Optional
2236 -- Default = FND_API.G_FALSE
2237 -- p_validation_level IN NUMBER Optional
2238 -- Default = FND_API.G_VALID_LEVEL_FULL
2239 -- p_category_id IN NUMBER Required
2240 -- p_include_subcats IN VARCHAR2 Optional
2241 -- Default = FND_API.G_FALSE
2242 -- OUT : x_return_status OUT VARCHAR2
2243 -- x_msg_count OUT NUMBER
2244 -- x_msg_data OUT VARCHAR2
2245 -- x_items_array OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
2246 -- Version : Current version 1.0
2247 -- Previous version 1.0
2248 -- Initial version 1.0
2249 -- Notes :
2250 --
2251 -- End of comments
2252 --
2253 PROCEDURE Get_UserItems
2254 ( p_api_version IN NUMBER,
2255 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2256 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2257 x_return_status OUT NOCOPY VARCHAR2,
2258 x_msg_count OUT NOCOPY NUMBER,
2259 x_msg_data OUT NOCOPY VARCHAR2,
2260 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2261 p_application_id IN NUMBER,
2262 p_user_id IN NUMBER,
2263 p_request_obj IN AMV_REQUEST_OBJ_TYPE,
2264 x_return_obj OUT NOCOPY AMV_RETURN_OBJ_TYPE,
2265 x_items_array OUT NOCOPY AMV_ITEMDISPLAY_VARRAY_TYPE
2266 )
2267 IS
2268 l_api_name CONSTANT VARCHAR2(30) := 'Get_UserItems';
2269 l_api_version CONSTANT NUMBER := 1.0;
2270 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2271 --
2272 l_resource_id number;
2273 l_user_id number;
2274 l_login_user_id number;
2275 l_login_user_status varchar2(30);
2276 l_Error_Msg varchar2(2000);
2277 l_Error_Token varchar2(80);
2278 l_object_version_number number;
2279 l_application_id number;
2280 --
2281 l_record_count NUMBER := 0;
2282 l_total_count NUMBER := 0;
2283 l_counter NUMBER := 1;
2284 l_channel_id number;
2285 l_item_id number;
2286 l_item_name varchar2(240);
2287 l_description varchar2(2000);
2288 l_item_type varchar2(30);
2289
2290 -- fixed bug 3415777
2291 CURSOR Get_MyItems_csr IS
2292 select item_id
2293 , item_name
2294 , description
2295 , item_type
2296 from jtf_amv_items_vl
2297 where owner_id = p_user_id
2298 and application_id = p_application_id
2299 and nvl( expiration_date, sysdate ) >= sysdate
2300 and nvl(effective_start_date, sysdate) <= sysdate
2301 order by item_name;
2302
2303 CURSOR Get_ItemsTotal_csr IS
2304 select count(item_id)
2305 from jtf_amv_items_vl
2306 where owner_id = p_user_id
2307 and nvl( expiration_date, sysdate ) >= sysdate
2308 and nvl(effective_start_date, sysdate) <= sysdate
2309 and application_id = p_application_id;
2310
2311 BEGIN
2312 -- Standard begin of API savepoint
2313 SAVEPOINT Get_UserItems;
2314 -- Standard call to check for call compatibility.
2315 IF NOT FND_API.Compatible_API_Call (
2316 l_api_version,
2317 p_api_version,
2318 l_api_name,
2319 G_PKG_NAME)
2320 THEN
2321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2322 END IF;
2323 -- Debug Message
2324 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2325 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2326 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
2327 FND_MSG_PUB.Add;
2328 END IF;
2329 --Initialize message list if p_init_msg_list is TRUE.
2330 IF FND_API.To_Boolean (p_init_msg_list) THEN
2331 FND_MSG_PUB.initialize;
2332 END IF;
2333 -- Get the current (login) user id.
2334 AMV_UTILITY_PVT.Get_UserInfo(
2335 x_resource_id => l_resource_id,
2336 x_user_id => l_user_id,
2337 x_login_id => l_login_user_id,
2338 x_user_status => l_login_user_status
2339 );
2340 -- check login user
2341 IF (p_check_login_user = FND_API.G_TRUE) THEN
2342 -- Check if user is login and has the required privilege.
2343 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2344 -- User is not login.
2345 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2346 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2347 FND_MSG_PUB.Add;
2348 END IF;
2349 RAISE FND_API.G_EXC_ERROR;
2350 END IF;
2351 END IF;
2352 -- This fix is for executing api in sqlplus mode
2353 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2354 l_login_user_id := -1;
2355 l_user_id := -1;
2356 l_resource_id := -1;
2357 END IF;
2358 -- Initialize API return status to sucess
2359 x_return_status := FND_API.G_RET_STS_SUCCESS;
2360
2361 --
2362 x_items_array := AMV_ITEMDISPLAY_VARRAY_TYPE();
2363
2364 -- get the total number of items in category
2365 OPEN Get_ItemsTotal_csr;
2366 FETCH Get_ItemsTotal_csr INTO l_total_count;
2367 CLOSE Get_ItemsTotal_csr;
2368
2369 IF l_total_count < p_request_obj.start_record_position THEN
2370 l_counter := l_total_count;
2371 ELSE
2372 OPEN Get_MyItems_csr;
2373 LOOP
2374 FETCH Get_MyItems_csr INTO l_item_id,l_item_name,l_description,l_item_type;
2375 EXIT WHEN Get_MyItems_csr%NOTFOUND;
2376 IF (l_counter >= p_request_obj.start_record_position) AND
2377 (l_record_count <= p_request_obj.records_requested)
2378 THEN
2379 l_record_count := l_record_count + 1;
2380 x_items_array.extend;
2381 x_items_array(l_record_count).id := l_item_id;
2382 x_items_array(l_record_count).name := l_item_name;
2383 x_items_array(l_record_count).description := l_description;
2384 x_items_array(l_record_count).type := l_item_type;
2385 END IF;
2386 EXIT WHEN l_record_count = p_request_obj.records_requested;
2387 l_counter := l_counter + 1;
2388 END LOOP;
2389 CLOSE Get_MyItems_csr;
2390 END IF;
2391
2392 x_return_obj.returned_record_count := l_record_count;
2393 x_return_obj.next_record_position :=
2394 p_request_obj.start_record_position + l_record_count;
2395 x_return_obj.total_record_count := l_total_count;
2396 --
2397
2398 -- Debug Message
2399 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2400 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2401 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
2402 FND_MSG_PUB.Add;
2403 END IF;
2404 --Standard call to get message count and if count=1, get the message
2405 FND_MSG_PUB.Count_And_Get (
2406 p_encoded => FND_API.G_FALSE,
2407 p_count => x_msg_count,
2408 p_data => x_msg_data
2409 );
2410 EXCEPTION
2411 WHEN FND_API.G_EXC_ERROR THEN
2412 ROLLBACK TO Get_UserItems;
2413 x_return_status := FND_API.G_RET_STS_ERROR;
2414 -- Standard call to get message count and if count=1, get the message
2415 FND_MSG_PUB.Count_And_Get (
2416 p_encoded => FND_API.G_FALSE,
2417 p_count => x_msg_count,
2418 p_data => x_msg_data
2419 );
2420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2421 ROLLBACK TO Get_UserItems;
2422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2423 -- Standard call to get message count and if count=1, get the message
2424 FND_MSG_PUB.Count_And_Get (
2425 p_encoded => FND_API.G_FALSE,
2426 p_count => x_msg_count,
2427 p_data => x_msg_data
2428 );
2429 WHEN OTHERS THEN
2430 ROLLBACK TO Get_UserItems;
2431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2432 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2433 THEN
2434 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2435 END IF;
2436 -- Standard call to get message count and if count=1, get the message
2437 FND_MSG_PUB.Count_And_Get (
2438 p_encoded => FND_API.G_FALSE,
2439 p_count => x_msg_count,
2440 p_data => x_msg_data
2441 );
2442 --
2443 END Get_UserItems;
2444 --------------------------------------------------------------------------------
2445 --------------------------------------------------------------------------------
2446 -- Start of comments
2447 -- API name : Can_UserMaintainChannel
2448 -- Type : Private
2449 -- Pre-reqs : None
2450 -- Function : Return all items published by the user
2451 -- Parameters :
2452 -- IN p_api_version IN NUMBER Required
2453 -- p_init_msg_list IN VARCHAR2 Optional
2454 -- Default = FND_API.G_FALSE
2455 -- p_validation_level IN NUMBER Optional
2456 -- Default = FND_API.G_VALID_LEVEL_FULL
2457 -- p_user_id IN NUMBER Required
2458 -- p_include_subcats IN VARCHAR2 Optional
2459 -- Default = FND_API.G_FALSE
2460 -- OUT : x_return_status OUT VARCHAR2
2461 -- x_msg_count OUT NUMBER
2462 -- x_msg_data OUT VARCHAR2
2463 -- x_items_array OUT AMV_CAT_HIERARCHY_VARRAY_TYPE
2464 -- Version : Current version 1.0
2465 -- Previous version 1.0
2466 -- Initial version 1.0
2467 -- Notes :
2468 --
2469 -- End of comments
2470 --
2471 PROCEDURE Can_UserMaintainChannel
2472 ( p_api_version IN NUMBER,
2473 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2474 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2475 x_return_status OUT NOCOPY VARCHAR2,
2476 x_msg_count OUT NOCOPY NUMBER,
2477 x_msg_data OUT NOCOPY VARCHAR2,
2478 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2479 p_user_id IN NUMBER,
2480 p_channel_id IN NUMBER,
2481 x_maintain_flag OUT NOCOPY VARCHAR2
2482 )
2483 IS
2484 l_api_name CONSTANT VARCHAR2(30) := 'Can_UserMaintainChannel';
2485 l_api_version CONSTANT NUMBER := 1.0;
2486 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2487 --
2488 l_resource_id number;
2489 l_user_id number;
2490 l_login_user_id number;
2491 l_login_user_status varchar2(30);
2492 l_Error_Msg varchar2(2000);
2493 l_Error_Token varchar2(80);
2494 l_object_version_number number;
2495 l_application_id number;
2496 --
2497 l_owner_id number;
2498 l_approver_id number;
2499 l_approver_flag varchar2(1);
2500
2501 CURSOR Get_OwnerAppr_csr IS
2502 select owner_user_id
2503 , default_approver_user_id
2504 from amv_c_channels_vl
2505 where channel_id = p_channel_id;
2506
2507 CURSOR Get_SecAppr_csr IS
2508 select chl_approver_flag
2509 from amv_u_access
2510 where access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
2511 and access_to_table_record_id = p_channel_id
2512 and user_or_group_type = AMV_UTILITY_PVT.G_USER
2513 and user_or_group_id = p_user_id
2514 and effective_start_date <= sysdate
2515 and nvl(expiration_date, sysdate) >= sysdate;
2516
2517 BEGIN
2518 -- Standard call to check for call compatibility.
2519 IF NOT FND_API.Compatible_API_Call (
2520 l_api_version,
2521 p_api_version,
2522 l_api_name,
2523 G_PKG_NAME)
2524 THEN
2525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2526 END IF;
2527 -- Debug Message
2528 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2529 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2530 FND_MESSAGE.Set_Token('ROW',l_full_name||': Start');
2531 FND_MSG_PUB.Add;
2532 END IF;
2533 --Initialize message list if p_init_msg_list is TRUE.
2534 IF FND_API.To_Boolean (p_init_msg_list) THEN
2535 FND_MSG_PUB.initialize;
2536 END IF;
2537 -- Get the current (login) user id.
2538 AMV_UTILITY_PVT.Get_UserInfo(
2539 x_resource_id => l_resource_id,
2540 x_user_id => l_user_id,
2541 x_login_id => l_login_user_id,
2542 x_user_status => l_login_user_status
2543 );
2544 -- check login user
2545 IF (p_check_login_user = FND_API.G_TRUE) THEN
2546 -- Check if user is login and has the required privilege.
2547 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2548 -- User is not login.
2549 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2550 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2551 FND_MSG_PUB.Add;
2552 END IF;
2553 RAISE FND_API.G_EXC_ERROR;
2554 END IF;
2555 END IF;
2556 -- This fix is for executing api in sqlplus mode
2557 IF (l_login_user_id = FND_API.G_MISS_NUM) THEN
2558 l_login_user_id := -1;
2559 l_user_id := -1;
2560 l_resource_id := -1;
2561 END IF;
2562 -- Initialize API return status to sucess
2563 x_return_status := FND_API.G_RET_STS_SUCCESS;
2564
2565 --
2566 OPEN Get_OwnerAppr_csr;
2567 FETCH Get_OwnerAppr_csr INTO l_approver_id, l_owner_id;
2568 CLOSE Get_OwnerAppr_csr;
2569
2570 IF l_approver_id = p_user_id THEN
2571 x_maintain_flag := FND_API.G_TRUE;
2572 ELSIF l_owner_id = p_user_id THEN
2573 x_maintain_flag := FND_API.G_TRUE;
2574 ELSE
2575 OPEN Get_SecAppr_csr;
2576 FETCH Get_SecAppr_csr INTO x_maintain_flag;
2577 CLOSE Get_SecAppr_csr;
2578
2579 IF x_maintain_flag <> FND_API.G_TRUE THEN
2580 x_maintain_flag := FND_API.G_FALSE;
2581 END IF;
2582 END IF;
2583 --
2584
2585 -- Debug Message
2586 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2587 FND_MESSAGE.Set_Name('AMV','AMV_API_DEBUG_MESSAGE');
2588 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
2589 FND_MSG_PUB.Add;
2590 END IF;
2591 --Standard call to get message count and if count=1, get the message
2592 FND_MSG_PUB.Count_And_Get (
2593 p_encoded => FND_API.G_FALSE,
2594 p_count => x_msg_count,
2595 p_data => x_msg_data
2596 );
2597 EXCEPTION
2598 WHEN FND_API.G_EXC_ERROR THEN
2599 x_return_status := FND_API.G_RET_STS_ERROR;
2600 -- Standard call to get message count and if count=1, get the message
2601 FND_MSG_PUB.Count_And_Get (
2602 p_encoded => FND_API.G_FALSE,
2603 p_count => x_msg_count,
2604 p_data => x_msg_data
2605 );
2606 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2608 -- Standard call to get message count and if count=1, get the message
2609 FND_MSG_PUB.Count_And_Get (
2610 p_encoded => FND_API.G_FALSE,
2611 p_count => x_msg_count,
2612 p_data => x_msg_data
2613 );
2614 WHEN OTHERS THEN
2615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2616 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2617 THEN
2618 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2619 END IF;
2620 -- Standard call to get message count and if count=1, get the message
2621 FND_MSG_PUB.Count_And_Get (
2622 p_encoded => FND_API.G_FALSE,
2623 p_count => x_msg_count,
2624 p_data => x_msg_data
2625 );
2626 --
2627 END Can_UserMaintainChannel;
2628 --------------------------------------------------------------------------------
2629 --
2630 END amv_mychannel_pvt;