1 PACKAGE BODY PJI_PMV_UTIL AS
2 -- $Header: PJIRX04B.pls 120.21 2011/10/21 17:52:52 kmaddi ship $
3
4 --Bug 4599990. This Procedure returns the details of the top organization in the PJI hierarchy over which the user
5 --has security permissions. The parameters are described below
6 --> x_top_org_id : Id of the top org over which the user has security permissions. The id will be 0 if the
7 -- user has permissions to see all the orgz
8 --> x_top_org_name : Name of the top org.This will be populated only if x_insert_top_org is Y
9 --> x_user_assmt_flag : If the top org is obtained from the security profile then the value will be 'N'
10 -- If the top org is obtained from the user assignment then the value will be 'Y'
11 --> x_insert_top_org : Flag indicating whether the user has permissions to the see the top org in the
12 -- hierarchy
13 PROCEDURE get_top_org_details
14 (x_top_org_id OUT nocopy per_security_profiles.organization_id%TYPE,
15 x_top_org_name OUT nocopy hr_all_organization_units_tl.name%TYPE,
16 x_user_assmt_flag OUT nocopy VARCHAR2,
17 x_insert_top_org_flag OUT nocopy VARCHAR2 )
18 IS
19 l_security_profile_id per_security_profiles.security_profile_id%TYPE;
20 l_top_organization_id per_security_profiles.organization_id%TYPE;
21 l_view_all_org_flag per_security_profiles.view_all_organizations_flag%TYPE;
22 l_user_id NUMBER;
23 BEGIN
24
25 l_security_profile_id := fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL');
26 l_user_id := fnd_global.user_id;
27
28 SELECT organization_id,
29 view_all_organizations_flag ,
30 include_top_organization_flag
31 INTO l_top_organization_id,
32 l_view_all_org_flag,
33 x_insert_top_org_flag
34 FROM per_security_profiles
35 WHERE security_profile_id=l_security_profile_id;
36
37 x_user_assmt_flag := 'N';
38
39 IF l_view_all_org_flag = 'Y' THEN
40
41 l_top_organization_id :=0;
42
43 ELSIF l_top_organization_id IS NULL THEN
44
45 SELECT per.organization_id
46 INTO l_top_organization_id
47 FROM fnd_user fndu,
48 per_all_assignments_f per
49 WHERE fndu.user_id=l_user_id
50 AND fndu.employee_id=per.person_id
51 AND per.primary_flag='Y'
52 AND (SYSDATE BETWEEN per.effective_start_Date AND NVL(per.effective_end_date, SYSDATE + 1));
53
54 x_user_assmt_flag := 'Y';
55
56 END IF;
57
58 x_top_org_id := l_top_organization_id;
59
60 IF x_insert_top_org_flag = 'Y' THEN
61
62 SELECT name
63 INTO x_top_org_name
64 FROM hr_all_organization_units_tl
65 WHERE organization_id = x_top_org_id
66 AND language = USERENV('LANG');
67
68 END IF;
69
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72
73 x_top_org_id :=-1;
74 x_insert_top_org_flag:='N';
75
76 END get_top_org_details;
77
78
79 --This function takes org_id as input parameter and returns the same org_id if it is
80 --present in the list of PJI organizations. Otherwise it returns null
81 --This helps to check the access to Performance Reporting from Project List page.
82 FUNCTION PJI_ORGANIZATION_EXISTS(p_org_id IN NUMBER) RETURN NUMBER
83 IS
84 l_top_organization_id per_security_profiles.organization_id%TYPE;
85 l_org_id per_security_profiles.organization_id%TYPE;
86 l_top_org_name hr_all_organization_units_tl.name%TYPE;
87 l_user_assmt_flag VARCHAR2(1);
88 l_insert_top_org_flag VARCHAR2(1);
89
90 BEGIN
91
92 get_top_org_details(
93 x_top_org_id => l_top_organization_id,
94 x_top_org_name => l_top_org_name,
95 x_user_assmt_flag => l_user_assmt_flag,
96 x_insert_top_org_flag => l_insert_top_org_flag);
97
98 BEGIN
99 IF l_top_organization_id = 0 THEN
100 SELECT p_org_id INTO l_org_id FROM DUAL WHERE EXISTS
101 (SELECT orgd.organization_id_child
102 FROM
103 per_org_structure_elements orgd
104 , pji_system_settings pset
105 , hr_all_organization_units org
106 WHERE 1=1
107 AND orgd.org_structure_version_id = pset.org_structure_version_id
108 AND orgd.organization_id_child = org.organization_id
109 AND org.organization_id = p_org_id);
110
111 RETURN l_org_id;
112 ELSIF l_user_assmt_flag = 'N' THEN
113
114 SELECT p_org_id INTO l_org_id FROM DUAL WHERE EXISTS
115 (SELECT orgd.organization_id_child
116 FROM
117 per_org_structure_elements orgd
118 , pji_system_settings pset
119 , per_organization_list sec
120 , hr_all_organization_units org
121 WHERE 1=1
122 AND orgd.org_structure_version_id = pset.org_structure_version_id
123 AND orgd.organization_id_child = org.organization_id
124 AND org.organization_id = p_org_id
125 AND orgd.organization_id_child = sec.organization_id
126 AND sec.security_profile_id = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL'));
127
128 RETURN l_org_id;
129 ELSIF l_user_assmt_flag = 'Y' THEN
130
131 SELECT p_org_id INTO l_org_id FROM DUAL WHERE EXISTS
132 (SELECT orgd.organization_id_child
133 FROM
134 per_org_structure_elements orgd
135 , pji_system_settings pset
136 , hr_all_organization_units org
137 WHERE 1=1
138 AND orgd.org_structure_version_id = pset.org_structure_version_id
139 AND orgd.organization_id_child = org.organization_id
140 AND orgd.organization_id_child = p_org_id
141 START WITH orgd.organization_id_parent=l_top_organization_id
142 CONNECT BY PRIOR orgd.organization_id_child = orgd.organization_id_parent);
143
144 RETURN l_org_id;
145 END IF;
146
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 NULL;
150 END;
151
152 --Bug 4599990. Insert top org only when the security profile setting allows the user to see top org.
153 IF l_insert_top_org_flag= 'Y' THEN
154
155 --Bug 4599990.In case the user has permissions to see all the orgz then the top org has to be derived.
156 IF l_top_organization_id=0 THEN
157
158 SELECT p_org_id INTO l_org_id FROM DUAL WHERE EXISTS
159 (SELECT orgd.organization_id_child
160 FROM
161 (select distinct organization_id_parent organization_id_child, NULL organization_id_parent from
162 per_org_structure_elements p
163 , pji_system_settings pset
164 where p.org_structure_version_id = pset.org_structure_version_id
165 and not exists
166 (select 1 from
167 per_org_structure_elements c
168 where c.organization_id_child = p.organization_id_parent
169 and c.org_structure_version_id = p.org_structure_version_id)) orgd
170 , per_organization_list sec
171 , hr_all_organization_units org
172 , per_security_profiles prof
173 WHERE 1=1
174 AND orgd.organization_id_child = org.organization_id
175 AND orgd.organization_id_child = p_org_id
176 AND orgd.organization_id_child = sec.organization_id (+)
177 AND sec.security_profile_id(+) = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL')
178 AND fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL') IS NOT NULL
179 AND prof.security_profile_id = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL')
180 AND ( prof.view_all_organizations_flag = 'Y' OR sec.organization_id IS NOT NULL));
181
182 RETURN l_org_id;
183
184 ELSE
185 IF l_top_organization_id = p_org_id THEN
186 RETURN p_org_id;
187 END IF;
188
189 END IF;
190
191 END IF;--IF l_insert_top_org_flag= 'Y' THEN
192 RETURN l_org_id;
193 EXCEPTION
194 WHEN OTHERS THEN
195 RETURN NULL;
196 END PJI_ORGANIZATION_EXISTS;
197
198
199
200 END PJI_PMV_UTIL;
[Home] [Help]