1 PACKAGE BODY PMI_SECURITY_PKG AS
2 /* $Header: PMISECPB.pls 115.10 2003/04/22 16:39:01 srpuri ship $ */
3
4 FUNCTION show_record
5 ( p_orgn_code VARCHAR2,
6 P_orgn_type NUMBER,
7 P_USER_ID NUMBER)
8
9 RETURN VARCHAR2 IS
10
11 CURSOR Cur_resp(P_user_id NUMBER) IS
12 SELECT Responsibility_id
13 FROM fnd_user_resp_groups
14 WHERE User_id = p_user_id
15 AND start_date <= SYSDATE
16 AND (end_date IS NULL OR
17 end_date >= SYSDATE)
18 AND responsibility_application_id = 558;
19
20 l_user_id NUMBER(15);
21 l_orgn_cnt NUMBER(15):= 0;
22 l_profile_value VARCHAR2(4);
23 l_responsibility_id NUMBER(15);
24 l_result BOOLEAN;
25
26 BEGIN
27
28 IF (p_orgn_code IS NULL) THEN
29 RETURN 'FALSE';
30 END IF;
31
32 IF p_user_id IS NULL THEN
33 l_user_id := FND_GLOBAL.USER_ID;
34 ELSE
35 l_user_id := p_user_id;
36 END IF;
37
38 /* 11/17/99 This condition is to validate the organizations for PMF. It is
39 a temporary fix. Decision needs to be made in the next release - Savita */
40
41 IF (FND_GLOBAL.USER_ID = -1) THEN
42 RETURN 'TRUE';
43 END IF;
44
45 IF (l_user_id IS NULL) THEN
46 RETURN 'FALSE';
47 END IF;
48
49 OPEN Cur_resp(l_user_id);
50
51 LOOP
52
53 FETCH Cur_resp into l_responsibility_id;
54 EXIT WHEN Cur_resp%NOTFOUND;
55
56 /* Check the All profile */
57
58 fnd_profile.get_specific('PMI$COMPANY_ALL',l_user_id,l_responsibility_id,558,
59 l_profile_value,l_result);
60
61
62 IF l_profile_value = 'ALL' THEN
63 RETURN 'TRUE';
64 END IF;
65
66 /* Check company profile for responsibility */
67
68 fnd_profile.get_specific('PMI$COMPANY',l_user_id,l_responsibility_id,558,l_profile_value,
69 l_result);
70
71 IF l_profile_value IS NOT NULL THEN
72 IF l_profile_value <> p_orgn_code THEN
73 IF p_orgn_type = 2 THEN
74 SELECT count(*)
75 INTO l_orgn_cnt
76 FROM sy_orgn_mst a
77 WHERE co_code = l_profile_value
78 AND a.orgn_code = p_orgn_code;
79 IF l_orgn_cnt > 0 THEN
80 RETURN 'TRUE';
81 END IF;
82 END IF;
83 ELSE
84 RETURN 'TRUE';
85 END IF;
86 END IF;
87 END LOOP;
88 IF p_orgn_type = 2 THEN
89 SELECT count(*)
90 INTO l_orgn_cnt
91 FROM sy_orgn_usr
92 WHERE user_id = l_user_id
93 AND orgn_code = p_orgn_code;
94
95 IF l_orgn_cnt > 0 THEN
96 RETURN 'TRUE';
97 ELSE
98 RETURN 'FALSE';
99 END IF;
100 ELSE
101 RETURN 'FALSE';
102 END IF;
103 RETURN 'FALSE';
104 END show_record;
105 END PMI_SECURITY_PKG ;
[Home] [Help]