1 PACKAGE BODY amv_utility_pvt AS
2 /* $Header: amvvutlb.pls 120.1 2005/06/21 16:54:22 appldev ship $ */
3 --
4 -- NAME
5 -- AMV_UTILITY_PVT
6 --
7 -- HISTORY
8 -- 06/01/1999 PWU CREATED
9 --
10 G_PKG_NAME CONSTANT VARCHAR2(30):='AMV_UTILITY_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(12):='amvvutlb.pls';
12
13 -- Debug mode
14 --g_debug boolean := FALSE;
15 g_debug boolean := TRUE;
16 G_VALID_LEVEL_LOGIN CONSTANT NUMBER := FND_API.G_VALID_LEVEL_FULL;
17 --
18
19 ------------------------------------
20 -- Functions and Procedures --
21 ------------------------------------
22 ---- (Real) Private Procedures ----
23 -- All these private procedures does not have real complete error handling.
24 --
25 --------------------------------------------------------------------------------
26 --------------------------------------------------------------------------------
27 --------------------------------------------------------------------------------
28 -- Start of comments
29 -- API name : Print_debug_message
30 -- Type : Private
31 -- Pre-reqs : None
32 -- Function : This procedure use
33 -- to print out the passed message
34 -- Reformat: each line is at most 70 characters long.
35 -- Parameters :
36 -- IN : p_message VARCHAR2 REQUIRED
37 -- Version : Current version 1.0
38 -- Previous version 1.0
39 -- Initial version 1.0
40 -- Note : This procedure will change to do nothing in production.
41 -- End of comments
42 --
43 PROCEDURE Print_debug_message
44 (
45 p_message IN VARCHAR2
46 ) AS
47 --
48 l_str_length INTEGER;
49 l_index INTEGER := 1;
50 l_linesize CONSTANT INTEGER := 70;
51 --
52 BEGIN
53 l_str_length := LENGTH(p_message);
54 FOR i in 1..l_str_length/l_linesize LOOP
55 l_index := l_index + l_linesize;
56 END LOOP;
57 --
58 END Print_debug_message;
59 --------------------------------------------------------------------------------
60 --------------------------------------------------------------------------------
61 -- Start of comments
62 -- API name : Get_ResourceId
63 -- Type : Private
64 -- Pre-reqs : None
65 -- Function : return the resource id associated with the user id.
66 -- basically, it translates the user id into resource id.
67 -- Parameters :
68 -- IN : p_user_id NUMBER required
69 -- OUT : x_resource_id NUMBER
70 -- Version : Current version 1.0
71 -- Previous version 1.0
72 -- Initial version 1.0
73 -- Note :
74 -- End of comments
75 --
76 PROCEDURE Get_ResourceId
77 (
78 p_user_id IN NUMBER,
79 x_resource_id OUT NOCOPY NUMBER
80 ) AS
81 CURSOR Get_resourceID_csr IS
82 Select resource_id
83 From jtf_rs_resource_extns
84 Where user_id = p_user_id
85 --And u.employee_id = r.source_id
86 --And r.category = 'EMPLOYEE'
87 ;
88
89 BEGIN
90 OPEN Get_resourceID_csr;
91 FETCH Get_resourceID_csr INTO x_resource_id;
92 IF (Get_resourceID_csr%NOTFOUND) THEN
93 x_resource_id := FND_API.G_MISS_NUM;
94 END IF;
95 CLOSE Get_resourceID_csr;
96 END Get_ResourceId;
97 --------------------------------------------------------------------------------
98 -- Start of comments
99 -- API name : Get_LoginUserId
100 -- Type : Private
101 -- Pre-reqs : None
102 -- Function : Call Standard API to check if user is login.
103 -- If so, it returns the login user id.
104 -- Otherwise, it returns FND_API.G_MISS_NUM.
105 -- Parameters :
106 -- IN : None
107 -- OUT : x_current_user_id NUMBER
108 -- the current (login) user id.
109 -- : x_current_login_id NUMBER
110 -- the current login id (unique per section).
111 -- Version : Current version 1.0
112 -- Previous version 1.0
113 -- Initial version 1.0
114 -- Note : This procedure return the global variable for currnet
115 -- user id and login id. It need further examined.
116 -- End of comments
117 --
118 PROCEDURE Get_LoginUserId
119 (
120 x_current_user_id OUT NOCOPY NUMBER,
121 x_current_login_id OUT NOCOPY NUMBER
122 ) AS
123 BEGIN
124 -- To be furhter examined.
125 x_current_user_id := FND_GLOBAL.User_Id;
126 x_current_login_id := FND_GLOBAL.Login_Id;
127 IF (x_current_user_id IS NULL OR x_current_user_id = -1) THEN
128 x_current_user_id := FND_API.G_MISS_NUM;
129 x_current_login_id := FND_API.G_MISS_NUM;
130 END IF;
131 END Get_LoginUserId;
132 --
133 --------------------------------------------------------------------------------
134 -- Start of comments
135 -- API name : Get_UserInfo
136 -- Type : Private
137 -- Pre-reqs : None
138 -- Function : Check and return (login) user status.
139 -- Parameters :
140 -- IN : p_user_id NUMBER Optional
141 -- user id. Default = FND_API.G_MISS_NUM
142 -- If user id is missing, use the current (login) user id.
143 -- OUT : x_resource_id NUMBER
144 -- resource id of the user if the user exist.
145 -- : x_user_id NUMBER
146 -- user id which is valid if user is exist.
147 -- : x_login_id NUMBER
148 -- the current login id (unique per section) if logined.
149 -- : x_user_status VARCHAR2
150 -- Version : Current version 1.0
151 -- Previous version 1.0
152 -- Initial version 1.0
153 -- Note :
154 -- End of comments
155 --
156 PROCEDURE Get_UserInfo
157 (
158 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
159 x_resource_id OUT NOCOPY NUMBER,
160 x_user_id OUT NOCOPY NUMBER,
161 x_login_id OUT NOCOPY NUMBER,
162 x_user_status OUT NOCOPY VARCHAR2
163 ) AS
164 l_user_id NUMBER;
165 l_login_id NUMBER;
166 --
167 CURSOR Fnd_User_csr (p_id in number) is
168 Select
169 user_name
170 From fnd_user
171 Where user_id = p_id;
172 l_fnd_user_info Fnd_User_csr%ROWTYPE;
173 --
174 BEGIN
175 --Get the current login user id.
176 Get_LoginUserId(l_user_id, l_login_id);
177 -- If p_user_id is not the current login user.
178 IF (p_user_id <> FND_API.G_MISS_NUM AND p_user_id <> l_user_id ) THEN
179 OPEN Fnd_User_csr(p_user_id);
180 FETCH Fnd_User_csr into l_fnd_user_info;
181 IF Fnd_User_csr%NOTFOUND THEN
182 x_user_status := G_USER_NOT_EXIST;
183 x_user_id := FND_API.G_MISS_NUM;
184 x_resource_id := FND_API.G_MISS_NUM;
185 x_login_id := FND_API.G_MISS_NUM;
186 ELSE
187 x_user_id := p_user_id;
188 x_login_id := FND_API.G_MISS_NUM;
189 x_user_status := G_USER_NOT_LOGIN;
190 --Get the resource id
191 Get_ResourceId ( p_user_id, x_resource_id);
192 IF (x_resource_id = FND_API.G_MISS_NUM) THEN
193 x_user_status := G_NO_RESOURCE;
194 END IF;
195 END IF;
196 CLOSE Fnd_User_csr;
197 ELSE
198 x_user_status := G_ACTIVE;
199 x_user_id := l_user_id;
200 x_login_id := l_login_id;
201 --Get the resource id
202 Get_ResourceId ( l_user_id, x_resource_id);
203 IF (x_resource_id = FND_API.G_MISS_NUM) THEN
204 x_user_status := G_NO_RESOURCE;
205 END IF;
206 END IF;
207 EXCEPTION
208 WHEN OTHERS THEN
209 x_user_status := G_USER_NOT_EXIST;
210 x_user_id := FND_API.G_MISS_NUM;
211 x_resource_id := FND_API.G_MISS_NUM;
212 x_login_id := FND_API.G_MISS_NUM;
213 END Get_UserInfo;
214 --
215 --------------------------------------------------------------------------------
216 -- Start of comments
217 -- API name : Get_UserStatus
218 -- Type : Private
219 -- Pre-reqs : None
220 -- Function : Check and return (login) user status.
221 -- Parameters :
222 -- IN : p_user_id NUMBER Optional
223 -- user id. Default = FND_API.G_MISS_NUM
224 -- If user id is missing, use the current (login) user id.
225 -- OUT : x_user_id NUMBER
226 -- user id which is valid if user is exist.
227 -- : x_login_id NUMBER
228 -- the current login id (unique per section) if logined.
229 -- : x_user_status NUMBER
230 -- values: G_ACTIVE, G_INACTIVE, G_NOT_EXIST.
231 -- Version : Current version 1.0
232 -- Previous version 1.0
233 -- Initial version 1.0
234 -- Note : If a user is in AOL (FND) but not on MES, this procedure
235 -- call a user profile api to do default setup in MES.
236 --
237 -- End of comments
238 --
239 PROCEDURE Get_UserStatus
240 (
241 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
242 x_user_id OUT NOCOPY NUMBER,
243 x_login_id OUT NOCOPY NUMBER,
244 x_user_status OUT NOCOPY NUMBER
245 ) AS
246 l_user_id NUMBER;
247 l_login_id NUMBER;
248 --
249 CURSOR Fnd_User_csr (p_id in number) is
250 Select
251 user_id, user_name
252 From fnd_user
253 Where user_id = p_id;
254 l_fnd_user_info Fnd_User_csr%ROWTYPE;
255 --
256 BEGIN
257 --Get the current login user id.
258 Get_LoginUserId(l_user_id, l_login_id);
259 -- If p_user_id is not the current login user.
260 IF (p_user_id <> FND_API.G_MISS_NUM AND
261 p_user_id <> l_user_id ) THEN
262 OPEN Fnd_User_csr(p_user_id);
263 FETCH Fnd_User_csr into l_fnd_user_info;
264 IF Fnd_User_csr%NOTFOUND THEN
265 x_user_status := G_NOT_EXIST;
266 x_user_id := FND_API.G_MISS_NUM;
267 x_login_id := FND_API.G_MISS_NUM;
268 ELSE
269 x_user_status := G_USER_ENABLE;
270 x_user_id := p_user_id;
271 x_login_id := FND_API.G_MISS_NUM;
272 END IF;
273 CLOSE Fnd_User_csr;
274 ELSE
275 x_user_status := G_USER_ENABLE;
276 x_user_id := l_user_id;
277 x_login_id := l_login_id;
278 END IF;
279 EXCEPTION
280 WHEN FND_API.G_EXC_ERROR THEN
281 x_user_status := G_NOT_EXIST;
282 x_user_id := FND_API.G_MISS_NUM;
283 x_login_id := FND_API.G_MISS_NUM;
284 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
285 x_user_status := G_NOT_EXIST;
286 x_user_id := FND_API.G_MISS_NUM;
287 x_login_id := FND_API.G_MISS_NUM;
288 WHEN OTHERS THEN
289 x_user_status := G_NOT_EXIST;
290 x_user_id := FND_API.G_MISS_NUM;
291 x_login_id := FND_API.G_MISS_NUM;
292 END Get_UserStatus;
293 --
294 --------------------------------------------------------------------------------
295 -- Start of comments
296 -- API name : Is_ItemIdValid
297 -- Type : Private
298 -- Pre-reqs : None
299 -- Function : Check if the passed item id is valid.
300 -- Parameters :
301 -- IN : p_item_id NUMBER Requried
302 -- OUT : None
303 -- Version : Current version 1.0
304 -- Previous version 1.0
305 -- Initial version 1.0
306 -- Note : This is a function returning a boolean. Not a procedure.
307 -- End of comments
308 --
309 FUNCTION Is_ItemIdValid
310 (
311 p_item_id IN NUMBER
312 ) RETURN Boolean AS
313 --
314 CURSOR Check_ItemID_csr is
315 Select item_id
316 From jtf_amv_items_vl
317 where item_id = p_item_id;
318 l_valid_flag BOOLEAN := FALSE;
319 l_tmp_number NUMBER;
320 --
321 BEGIN
322 OPEN Check_ItemID_csr;
323 FETCH Check_ItemID_csr INTO l_tmp_number;
324 IF (Check_ItemID_csr%NOTFOUND) THEN
325 l_valid_flag := FALSE;
326 ELSE
327 l_valid_flag := TRUE;
328 END IF;
329 CLOSE Check_ItemID_csr;
330 return l_valid_flag;
331 END Is_ItemIdValid;
332 --
333 --------------------------------------------------------------------------------
334 -- Start of comments
335 -- API name : Is_ChannelIdValid
336 -- Type : Private
337 -- Pre-reqs : None
338 -- Function : Check if the passed channel id is valid.
339 -- Parameters :
340 -- IN : p_channel_id NUMBER Requried
341 -- OUT : None
342 -- Version : Current version 1.0
343 -- Previous version 1.0
344 -- Initial version 1.0
345 -- Note : This is a function returning a boolean. Not a procedure.
346 -- End of comments
347 --
348 FUNCTION Is_ChannelIdValid
349 (
350 p_channel_id IN NUMBER
351 ) RETURN Boolean AS
352 --
353 CURSOR Check_ChannelID_csr is
354 Select channel_id
355 From amv_c_channels_b
356 where channel_id = p_channel_id;
357 l_valid_flag BOOLEAN := FALSE;
358 l_tmp_number NUMBER;
359 --
360 BEGIN
361 OPEN Check_ChannelID_csr;
362 FETCH Check_ChannelID_csr INTO l_tmp_number;
363 IF (Check_ChannelID_csr%NOTFOUND) THEN
364 l_valid_flag := FALSE;
365 ELSE
366 l_valid_flag := TRUE;
367 END IF;
368 CLOSE Check_ChannelID_csr;
369 return l_valid_flag;
370 END Is_ChannelIdValid;
371 --
372 --------------------------------------------------------------------------------
373 -- Start of comments
374 -- API name : Is_CategoryIdValid
375 -- Type : Private
376 -- Pre-reqs : None
377 -- Function : Check if the passed category id is valid.
378 -- Parameters :
379 -- IN : p_category_id NUMBER Requried
380 -- OUT : None
381 -- Version : Current version 1.0
382 -- Previous version 1.0
383 -- Initial version 1.0
384 -- Note : This is a function returning a boolean. Not a procedure.
385 -- End of comments
386 --
387 FUNCTION Is_CategoryIdValid
388 (
389 p_category_id IN NUMBER
390 ) RETURN Boolean AS
391 --
392 CURSOR Check_CategoryID_csr is
393 Select channel_category_id
394 From amv_c_categories_b
395 where channel_category_id = p_category_id;
396 l_valid_flag BOOLEAN := FALSE;
397 l_tmp_number NUMBER;
398 --
399 BEGIN
400 OPEN Check_CategoryID_csr;
401 FETCH Check_CategoryID_csr INTO l_tmp_number;
402 IF (Check_CategoryID_csr%NOTFOUND) THEN
403 l_valid_flag := FALSE;
404 ELSE
405 l_valid_flag := TRUE;
406 END IF;
407 CLOSE Check_CategoryID_csr;
408 return l_valid_flag;
409 END Is_CategoryIdValid;
410 --------------------------------------------------------------------------------
411 -- Start of comments
412 -- API name : Is_ApplIdValid
413 -- Type : Private
414 -- Pre-reqs : None
415 -- Function : Check if the passed application id is valid.
416 -- Parameters :
417 -- IN : p_application_id NUMBER Requried
418 -- OUT : None
419 -- Version : Current version 1.0
420 -- Previous version 1.0
421 -- Initial version 1.0
422 -- Note : This is a function returning a boolean. Not a procedure.
423 -- End of comments
424 --
425 FUNCTION Is_ApplIdValid
426 (
427 p_application_id IN NUMBER
428 ) RETURN Boolean AS
429 --
430 CURSOR Check_ApplicationID_csr is
431 Select application_id
432 From fnd_application
433 where application_id = p_application_id;
434 l_valid_flag BOOLEAN := FALSE;
435 l_tmp_number NUMBER;
436 --
437 BEGIN
438 OPEN Check_ApplicationID_csr;
439 FETCH Check_ApplicationID_csr INTO l_tmp_number;
440 IF (Check_ApplicationID_csr%NOTFOUND) THEN
441 l_valid_flag := FALSE;
442 ELSE
443 l_valid_flag := TRUE;
444 END IF;
445 CLOSE Check_ApplicationID_csr;
446 return l_valid_flag;
447 END Is_ApplIdValid;
448 --
449 --------------------------------------------------------------------------------
450 -- Start of comments
451 -- API name : Is_PerspectiveIdValid
452 -- Type : Private
453 -- Pre-reqs : None
454 -- Function : Check if the passed perspective id is valid.
455 -- Parameters :
456 -- IN : p_perspective_id NUMBER Requried
457 -- OUT : None
458 -- Version : Current version 1.0
459 -- Previous version 1.0
460 -- Initial version 1.0
461 -- Note : This is a function returning a boolean. Not a procedure.
462 -- End of comments
463 --
464 FUNCTION Is_PerspectiveIdValid
465 (
466 p_perspective_id IN NUMBER
467 ) RETURN Boolean AS
468 --
469 CURSOR Check_PerspectiveID_csr is
470 Select perspective_id
471 From amv_i_perspectives_b
472 where perspective_id = p_perspective_id;
473 l_valid_flag BOOLEAN := FALSE;
474 l_tmp_number NUMBER;
475 --
476 BEGIN
477 OPEN Check_PerspectiveID_csr;
478 FETCH Check_PerspectiveID_csr INTO l_tmp_number;
479 IF (Check_PerspectiveID_csr%NOTFOUND) THEN
480 l_valid_flag := FALSE;
481 ELSE
482 l_valid_flag := TRUE;
483 END IF;
484 CLOSE Check_PerspectiveID_csr;
485 return l_valid_flag;
486 END Is_PerspectiveIdValid;
487 --
488 --
489 -----------------------------------------------------------------------------
490 -- Start of comments
491 -- API name : Is_ContentTypeIdValid
492 -- Type : Private
493 -- Pre-reqs : None
494 -- Function : Check if the passed content type id is valid.
495 -- Parameters :
496 -- IN : p_content_type_id NUMBER Requried
497 -- OUT : None
498 -- Version : Current version 1.0
499 -- Previous version 1.0
500 -- Initial version 1.0
501 -- Note : This is a function returning a boolean. Not a procedure.
502 -- End of comments
503 --
504 FUNCTION Is_ContentTypeIdValid
505 (
506 p_content_type_Id IN NUMBER
507 ) RETURN Boolean AS
508 --
509 --
510 CURSOR Check_ContentTypeID_csr is
511 Select content_type_id
512 From amv_i_content_types_b
513 where content_type_id = p_content_type_id;
514 l_valid_flag BOOLEAN := FALSE;
515 l_tmp_number NUMBER;
516 --
517 BEGIN
518 OPEN Check_ContentTypeID_csr;
519 FETCH Check_ContentTypeID_csr INTO l_tmp_number;
520 IF (Check_ContentTypeID_csr%NOTFOUND) THEN
521 l_valid_flag := FALSE;
522 ELSE
523 l_valid_flag := TRUE;
524 END IF;
525 CLOSE Check_ContentTypeID_csr;
526 return l_valid_flag;
527 END Is_ContentTypeIdValid;
528 --
529 --------------------------------------------------------------------------------
530 -- Start of comments
531 -- API name : Is_RoleIdValid
532 -- Type : Private
533 -- Pre-reqs : None
534 -- Function : Check if the passed role id is valid.
535 -- Parameters :
536 -- IN : p_role_id NUMBER Requried
537 -- OUT : None
538 -- Version : Current version 1.0
539 -- Previous version 1.0
540 -- Initial version 1.0
541 -- Note : This is a function returning a boolean. Not a procedure.
542 -- End of comments
543 --
544 FUNCTION Is_RoleIdValid
545 (
546 p_role_id IN NUMBER
547 ) RETURN Boolean AS
548 CURSOR Check_RoleID_csr is
549 Select role_id
550 From jtf_rs_roles_vl
551 Where role_id = p_role_id
552 ; --And role_type_id = AMV_USER_PVT.G_MES_ROLE_TYPE_ID;
553 l_valid_flag BOOLEAN := FALSE;
554 l_tmp_number NUMBER;
555 --
556 BEGIN
557 OPEN Check_RoleID_csr;
558 FETCH Check_RoleID_csr INTO l_tmp_number;
559 IF (Check_RoleID_csr%NOTFOUND) THEN
560 l_valid_flag := FALSE;
561 ELSE
562 l_valid_flag := TRUE;
563 END IF;
564 CLOSE Check_RoleID_csr;
565 return l_valid_flag;
566 END Is_RoleIdValid;
567 --
568 --------------------------------------------------------------------------------
569 -- Start of comments
570 -- API name : Is_ResourceIdValid
571 -- Type : Private
572 -- Pre-reqs : None
573 -- Function : Check if the passed resource id is valid.
574 -- Parameters :
575 -- IN : p_resource_id NUMBER Requried
576 -- OUT : None
577 -- Version : Current version 1.0
578 -- Previous version 1.0
579 -- Initial version 1.0
580 -- Note : This is a function returning a boolean. Not a procedure.
581 -- End of comments
582 --
583 FUNCTION Is_ResourceIdValid
584 (
585 p_resource_id IN NUMBER
586 ) RETURN Boolean AS
587 CURSOR Check_ResourceID_csr is
588 Select resource_id
589 From jtf_rs_resource_extns
590 Where resource_id = p_resource_id
591 --And category = AMV_USER_PVT.G_RESOURCE_CATEGORY
592 ;
593 l_valid_flag BOOLEAN := FALSE;
594 l_tmp_number NUMBER;
595 --
596 BEGIN
597 OPEN Check_ResourceID_csr;
598 FETCH Check_ResourceID_csr INTO l_tmp_number;
599 IF (Check_ResourceID_csr%NOTFOUND) THEN
600 l_valid_flag := FALSE;
601 ELSE
602 l_valid_flag := TRUE;
603 END IF;
604 CLOSE Check_ResourceID_csr;
605 return l_valid_flag;
606 END Is_ResourceIdValid;
607 --
608 --------------------------------------------------------------------------------
609 -- Start of comments
610 -- API name : Is_NotificationIdValid
611 -- Type : Private
612 -- Pre-reqs : None
613 -- Function : Check if the passed notification id is valid.
614 -- Parameters :
615 -- IN : p_notification_id NUMBER Requried
616 -- OUT : None
617 -- Version : Current version 1.0
618 -- Previous version 1.0
619 -- Initial version 1.0
620 -- Note : This is a function returning a boolean. Not a procedure.
621 -- End of comments
622 --
623 FUNCTION Is_NotificationIdValid
624 (
625 p_notification_id IN NUMBER
626 ) RETURN Boolean AS
627 --
628 BEGIN
629 return TRUE;
630 END Is_NotificationIdValid;
631 --
632 --------------------------------------------------------------------------------
633 -- Start of comments
634 -- API name : Is_GroupIdValid
635 -- Type : Private
636 -- Pre-reqs : None
637 -- Function : Check if the passed group id is valid.
638 -- Parameters :
639 -- IN : p_group_id NUMBER Requried
640 -- OUT : None
641 -- Version : Current version 1.0
642 -- Previous version 1.0
643 -- Initial version 1.0
644 -- Note : This is a function returning a boolean. Not a procedure.
645 -- End of comments
646 --
647 FUNCTION Is_GroupIdValid
648 (
649 p_group_id IN NUMBER
650 ) RETURN Boolean AS
651 --
652 CURSOR C_Check_Group_id is
653 Select group_id
654 From jtf_rs_groups_vl
655 where group_id = p_group_id
656 --And usage = AMV_USER_PVT.G_MES_GROUP_USAGE
657 ;
658 l_valid_flag BOOLEAN := FALSE;
659 l_tmp_number NUMBER;
660 --
661 BEGIN
662 OPEN C_Check_Group_id;
663 FETCH C_Check_Group_id INTO l_tmp_number;
664 IF (C_Check_Group_id%NOTFOUND) THEN
665 l_valid_flag := FALSE;
666 ELSE
667 l_valid_flag := TRUE;
668 END IF;
669 CLOSE C_Check_Group_id;
670 return l_valid_flag;
671 END Is_GroupIdValid;
672 --
673 --------------------------------------------------------------------------------
674 --
675 -- Start of comments
676 -- API name : Is_UserIdValid
677 -- Type : Private
678 -- Pre-reqs : None
679 -- Function : check if user id is valid and return a boolean value.
680 -- Parameters :
681 -- IN : p_user_id IN NUMBER Required
682 -- user id
683 -- OUT : None
684 -- Version : Current version 1.0
685 -- Previous version 1.0
686 -- Initial version 1.0
687 -- Notes :
688 -- End of comments
689 --
690 FUNCTION Is_UserIdValid (
691 p_user_id IN NUMBER
692 )
693 RETURN Boolean
694 IS
695 l_valid_flag number;
696 CURSOR
697 Check_UserId_csr IS
698 select count(*)
699 from jtf_rs_resource_extns
700 where resource_id = p_user_id;
701 BEGIN
702 OPEN Check_UserId_csr;
703 FETCH Check_UserId_csr INTO l_valid_flag;
704 CLOSE Check_UserId_csr;
705 IF l_valid_flag > 0 THEN
706 return TRUE;
707 ELSE
708 return FALSE;
709 END IF;
710 END Is_UserIdValid;
711 --
712 --------------------------------------------------------------------------------
713 --
714 -- Start of comments
715 -- API name : Is_MyChannelIdValid
716 -- Type : Private
717 -- Pre-reqs : None
718 -- Function : check if my channel id is valid and return boolean.
719 -- Parameters :
720 -- IN : p_mychannel_id IN NUMBER Required
721 -- OUT : None
722 -- Version : Current version 1.0
723 -- Previous version 1.0
724 -- Initial version 1.0
725 -- Notes :
726 -- End of comments
727 --
728 FUNCTION Is_MyChannelIdValid (
729 p_mychannel_id IN NUMBER
730 )
731 RETURN Boolean
732 IS
733 l_valid_flag number := 1;
734 CURSOR
735 Check_MyChannelId_csr IS
736 select count(*)
737 from amv_u_my_channels
738 where my_channel_id = p_mychannel_id;
739 BEGIN
740 OPEN Check_MyChannelId_csr;
741 FETCH Check_MyChannelId_csr INTO l_valid_flag;
742 CLOSE Check_MyChannelId_csr;
743 IF l_valid_flag > 0 THEN
744 return TRUE;
745 ELSE
746 return FALSE;
747 END IF;
748 END Is_MyChannelIdValid;
749 --
750 --------------------------------------------------------------------------------
751 --------------------------------------------------------------------------------
752 -- Start of comments
753 -- API name : Get_DeleteChannelStatus
754 -- Type : Private
755 -- Pre-reqs : None
756 -- Function : check if an user or group can delete a channel
757 -- Parameters :
758 -- IN
759 -- p_channel_id IN NUMBER Required
760 -- p_user_or_group_id IN NUMBER Required
761 -- p_user_or_group_type IN NUMBER Required
762 -- OUT
763 -- Boolean
764 -- Version : Current version 1.0
765 -- Previous version 1.0
766 -- Initial version 1.0
767 -- Notes :
768 -- End of comments
769 --
770 --
771 FUNCTION Get_DeleteChannelStatus
772 ( p_channel_id IN NUMBER,
773 p_user_or_group_id IN NUMBER,
774 p_user_or_group_type IN VARCHAR2) return boolean
775 IS
776 --
777 l_flag varchar2(1);
778
779 CURSOR DelChanAccs_csr IS
780 select can_delete_flag
781 from amv_u_access
782 where access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
783 and access_to_table_record_id = p_channel_id
784 and user_or_group_id = p_user_or_group_id
785 and user_or_group_type = p_user_or_group_type;
786 --
787 BEGIN
788 --
789 OPEN DelChanAccs_csr;
790 FETCH DelChanAccs_csr INTO l_flag;
791 IF DelChanAccs_csr%NOTFOUND THEN
792 l_flag := FND_API.G_FALSE;
793 END IF;
794 CLOSE DelChanAccs_csr;
795
796 IF l_flag = FND_API.G_TRUE THEN
797 return TRUE;
798 ELSE
799 return FALSE;
800 END IF;
801 --
802 END Get_DeleteChannelStatus;
803 --
804 --------------------------------------------------------------------------------
805 --------------------------------------------------------------------------------
806 -- Start of comments
807 -- API name : Get_UpdateChannelStatus
808 -- Type : Private
809 -- Pre-reqs : None
810 -- Function : check if an user or group can update a channel
811 -- Parameters :
812 -- IN
813 -- p_channel_id IN NUMBER Required
814 -- p_user_or_group_id IN NUMBER Required
815 -- p_user_or_group_type IN NUMBER Required
816 -- OUT
817 -- Boolean
818 -- Version : Current version 1.0
819 -- Previous version 1.0
820 -- Initial version 1.0
821 -- Notes :
822 -- End of comments
823 --
824 FUNCTION Get_UpdateChannelStatus
825 ( p_channel_id IN NUMBER,
826 p_user_or_group_id IN NUMBER,
827 p_user_or_group_type IN VARCHAR2) return boolean
828 IS
829 --
830 l_flag varchar2(1);
831
832 CURSOR UpdChanAccs_csr IS
833 select can_update_flag
834 from amv_u_access
835 where access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
836 and access_to_table_record_id = p_channel_id
837 and user_or_group_id = p_user_or_group_id
838 and user_or_group_type = p_user_or_group_type;
839 --
840 BEGIN
841 --
842 OPEN UpdChanAccs_csr;
843 FETCH UpdChanAccs_csr INTO l_flag;
844 IF UpdChanAccs_csr%NOTFOUND THEN
845 l_flag := FND_API.G_FALSE;
846 END IF;
847 CLOSE UpdChanAccs_csr;
848
849 IF l_flag = FND_API.G_TRUE THEN
850 return TRUE;
851 ELSE
852 return FALSE;
853 END IF;
854 --
855 END Get_UpdateChannelStatus;
856 --
857 --------------------------------------------------------------------------------
858 --------------------------------------------------------------------------------
859 -- Start of comments
860 -- API name : Get_DeleteCategoryStatus
861 -- Type : Private
862 -- Pre-reqs : None
863 -- Function : check if an user or group can delete a category
864 -- Parameters :
865 -- IN
866 -- p_category_id IN NUMBER Required
867 -- p_user_or_group_id IN NUMBER Required
868 -- p_user_or_group_type IN NUMBER Required
869 -- OUT
870 -- Boolean
871 -- Version : Current version 1.0
872 -- Previous version 1.0
873 -- Initial version 1.0
874 -- Notes :
875 -- End of comments
876 --
877 --
878 FUNCTION Get_DeleteCategoryStatus
879 ( p_category_id IN NUMBER,
880 p_user_or_group_id IN NUMBER,
881 p_user_or_group_type IN VARCHAR2) return boolean
882 IS
883 --
884 l_flag varchar2(1);
885
886 CURSOR DelCatAccs_csr IS
887 select can_delete_flag
888 from amv_u_access
889 where access_to_table_code = AMV_UTILITY_PVT.G_CATEGORY
890 and access_to_table_record_id = p_category_id
891 and user_or_group_id = p_user_or_group_id
892 and user_or_group_type = p_user_or_group_type;
893 --
894 BEGIN
895 --
896 OPEN DelCatAccs_csr;
897 FETCH DelCatAccs_csr INTO l_flag;
898 IF DelCatAccs_csr%NOTFOUND THEN
899 l_flag := FND_API.G_FALSE;
900 END IF;
901 CLOSE DelCatAccs_csr;
902
903 IF l_flag = FND_API.G_TRUE THEN
904 return TRUE;
905 ELSE
906 return FALSE;
907 END IF;
908 --
909 END Get_DeleteCategoryStatus;
910 --
911 --------------------------------------------------------------------------------
912 --------------------------------------------------------------------------------
913 -- Start of comments
914 -- API name : Get_UpdateCategoryStatus
915 -- Type : Private
916 -- Pre-reqs : None
917 -- Function : check if an user or group can update a channel
918 -- Parameters :
919 -- IN
920 -- p_category_id IN NUMBER Required
921 -- p_user_or_group_id IN NUMBER Required
922 -- p_user_or_group_type IN NUMBER Required
923 -- OUT
924 -- Boolean
925 -- Version : Current version 1.0
926 -- Previous version 1.0
927 -- Initial version 1.0
928 -- Notes :
929 -- End of comments
930 --
931 FUNCTION Get_UpdateCategoryStatus
932 ( p_category_id IN NUMBER,
933 p_user_or_group_id IN NUMBER,
934 p_user_or_group_type IN VARCHAR2) return boolean
935 IS
936 --
937 l_flag varchar2(1);
938
939 CURSOR UpdCatAccs_csr IS
940 select can_update_flag
941 from amv_u_access
942 where access_to_table_code = AMV_UTILITY_PVT.G_CATEGORY
943 and access_to_table_record_id = p_category_id
944 and user_or_group_id = p_user_or_group_id
945 and user_or_group_type = p_user_or_group_type;
946 --
947 BEGIN
948 --
949 OPEN UpdCatAccs_csr;
950 FETCH UpdCatAccs_csr INTO l_flag;
951 IF UpdCatAccs_csr%NOTFOUND THEN
952 l_flag := FND_API.G_FALSE;
953 END IF;
954 CLOSE UpdCatAccs_csr;
955
956 IF l_flag = FND_API.G_TRUE THEN
957 return TRUE;
958 ELSE
959 return FALSE;
960 END IF;
961 --
962 END Get_UpdateCategoryStatus;
963 --
964 --------------------------------------------------------------------------------
965 END amv_utility_pvt;
[Home] [Help]