1 PACKAGE BODY HRI_BPL_SECURITY AS
2 /* $Header: hribpscr.pkb 120.1 2006/11/16 11:35:59 anmajumd noship $ */
3
4 /* simply returns the profile option 'HRI_DBI_CHO_NMD_USR' value */
5 FUNCTION get_named_user_profile_value RETURN NUMBER IS
6
7 BEGIN
8
9 RETURN (fnd_profile.value('HRI_DBI_CHO_NMD_USR'));
10
11 END get_named_user_profile_value;
12
13 /* wrapper function, checks the value for the new CHO
14 profile option and returns that if its set
15 otherwise returns fnd_global.employee_id */
16 FUNCTION get_apps_signin_person_id RETURN NUMBER IS
17
18 CURSOR check_cho_resp IS
19 SELECT 1
20 FROM fnd_responsibility
21 WHERE application_id = 453
22 AND responsibility_key = 'HRI_DBI_CHIEF_HR_OFFICER'
23 AND fnd_global.resp_id = responsibility_id;
24
25 l_cho_resp_ind NUMBER :=0;
26 l_person_id NUMBER := fnd_global.employee_id;
27
28 BEGIN
29
30 OPEN check_cho_resp;
31 FETCH check_cho_resp INTO l_cho_resp_ind;
32 CLOSE check_cho_resp;
33
34 IF l_cho_resp_ind = 1 THEN
35 -- return the CHO named user profile option person_id
36 l_person_id := NVL(get_named_user_profile_value,fnd_global.employee_id);
37 ELSE
38 l_person_id := fnd_global.employee_id;
39 END IF;
40
41 RETURN l_person_id;
42
43 EXCEPTION WHEN OTHERS THEN
44 IF check_cho_resp%ISOPEN THEN
45 CLOSE check_cho_resp;
46 END IF;
47 RETURN l_person_id;
48
49 END get_apps_signin_person_id;
50 --
51 --
52 -- Function takes reponsibility id and application id as input and returns
53 -- the responsibility key
54 --
55 FUNCTION get_resp_key(p_resp_id IN NUMBER,
56 p_resp_appl_id IN NUMBER) RETURN VARCHAR2 IS
57 --
58 l_resp_key VARCHAR2(30);
59 --
60 CURSOR cur_resp_key
61 IS
62 SELECT responsibility_key
63 FROM FND_RESPONSIBILITY
64 WHERE responsibility_id = p_resp_id
65 AND application_id = p_resp_appl_id;
66 --
67 BEGIN
68 --
69 OPEN cur_resp_key;
70 FETCH cur_resp_key INTO l_resp_key;
71 CLOSE cur_resp_key;
72 --
73 -- If valid responsibility does not exist for the input parameters then
74 -- return NA_EDW
75 --
76 IF l_resp_key IS NOT NULL THEN
77 --
78 RETURN(l_resp_key);
79 --
80 ELSE
81 --
82 RETURN('NA_EDW');
83 --
84 END IF;
85 --
86 EXCEPTION
87 --
88 WHEN OTHERS THEN
89 --
90 IF cur_resp_key%ISOPEN THEN
91 --
92 CLOSE cur_resp_key;
93 --
94 END IF;
95 --
96 RETURN('NA_EDW');
97 --
98 END get_resp_key;
99 --
100 --
101 -- Function to get the manager id when a user tries to login to OBIEE. The user
102 -- gets to see the data as this manager
103 --
104 FUNCTION get_mgr_id(p_employee_id IN NUMBER,
105 p_resp_id IN NUMBER,
106 p_resp_appl_id IN NUMBER) RETURN NUMBER IS
107 --
108 l_return_value NUMBER(15);
109 l_mgr_id_all NUMBER(15);
110 l_mgr_id_anlyst NUMBER(15);
111 --
112 BEGIN
113 --
114 -- Find the responsibility key when logged in from a new responsibility
115 --
116 IF (p_resp_id <> g_resp_id) OR g_resp_key IS NULL OR g_resp_key='NA_EDW' THEN
117 --
118 g_resp_id := p_resp_id;
119 --
120 g_resp_key := get_resp_key(p_resp_id, p_resp_appl_id);
121 --
122 END IF;
123 --
124 -- If logged in through Line Manager responsibility then return the employee id
125 --
126 IF g_resp_key = 'HRI_OBI_ALL_MGRH' THEN
127 --
128 l_mgr_id_all := p_employee_id;
129 --
130 IF l_mgr_id_all IS NULL THEN
131 --
132 l_mgr_id_all:= -1;
133 --
134 END IF;
135 --
136 l_return_value := l_mgr_id_all;
137 --
138 --
139 -- If logged in through the HR Manager By Analyst responsibility, then
140 -- return the value of the profile HRI:HR Analyst(Manager View) Top
141 --
142 ELSIF g_resp_key = 'HRI_OBIEE_WRKFC_MGRH' THEN
143 --
144 l_mgr_id_anlyst := fnd_profile.value('HRI_OBIEE_WRKFC_MGRH_TOP');
145 --
146 IF l_mgr_id_anlyst IS NULL THEN
147 --
148 l_mgr_id_anlyst := -1;
149 --
150 END IF;
151 --
152 l_return_value:= l_mgr_id_anlyst;
153 --
154 ELSE
155 --
156 -- Return -1 for all other responsibilities other than Line Manager and
157 -- HR Manager By Analyst responsibility
158 --
159 l_return_value:= -1;
160 --
161 END IF;
162 --
163 RETURN(l_return_value);
164 --
165 EXCEPTION
166 --
167 WHEN OTHERS THEN
168 --
169 RETURN(-1);
170 --
171 END get_mgr_id;
172 --
173 -- Function to get the organization id when a user tries to login to OBIEE.
174 -- The user gets to see the data of this organization
175 --
176 FUNCTION get_org_id(p_employee_id IN NUMBER,
177 p_resp_id IN NUMBER,
178 p_resp_appl_id IN NUMBER) RETURN NUMBER IS
179 --
180 l_return_value NUMBER(15);
181 l_org_id_anlyst NUMBER(15);
182 l_org_id_all NUMBER(15);
183 --
184 BEGIN
185 --
186 IF (p_resp_id <> g_resp_id) OR g_resp_key IS NULL OR g_resp_key = 'NA_EDW' THEN
187 --
188 g_resp_id := p_resp_id;
189 --
190 g_resp_key := get_resp_key(p_resp_id, p_resp_appl_id);
191 --
192 END IF;
193 --
194 -- If logged in through HR Analyst by Organization responsibility then
195 -- return the value of profile HRI:HR Analyst (Organization View) Top
196 -- for the user
197 --
198 IF g_resp_key = 'HRI_OBIEE_WRKFC_ORGH' THEN
199 --
200 l_org_id_anlyst := fnd_profile.value('HRI_OBIEE_WRKFC_ORGH_TOP');
201 --
202 --
203 IF l_org_id_anlyst IS NULL THEN
204 --
205 l_org_id_anlyst := -1;
206 --
207 END IF;
208 --
209 l_return_value:= l_org_id_anlyst;
210 --
211 --
212 -- If logged in through the Department manager responsibility then
213 -- return the value of profile HRI:Line Manager (Organization View) Top
214 -- for the user
215 --
216 ELSIF g_resp_key = 'HRI_OBI_ALL_ORGH' THEN
217 --
218 l_org_id_all := fnd_profile.value('HRI_OBI_ALL_ORGH_TOP');
219 --
220 IF l_org_id_all IS NULL THEN
221 --
222 l_org_id_all := -1;
223 --
224 END IF;
225 --
226 l_return_value:= l_org_id_all;
227 --
228 ELSE
229 --
230 -- Return -1 for all other responsibilities other than Department Manager
231 -- and HR Analyst By Organization responsibility
232 --
233 l_return_value:= -1;
234 --
235 END IF;
236 --
237 RETURN(l_return_value);
238 --
239 EXCEPTION
240 --
241 WHEN OTHERS THEN
242 --
243 RETURN(-1);
244 --
245 END get_org_id;
246 --
247 -- Overloaded version of get_mgr_id. It takes no parameter as input and uses
248 -- FND packages to set the parameters
249 --
250 FUNCTION get_mgr_id RETURN NUMBER IS
251 --
252 l_mgr_id NUMBER(15);
253 --
254 BEGIN
255 --
256 l_mgr_id := get_mgr_id(fnd_global.employee_id, fnd_global.resp_id, fnd_global.resp_appl_id);
257 --
258 RETURN(l_mgr_id);
259 --
260 END get_mgr_id;
261 --
262 -- Overloaded version of get_org_id. It takes no parameter as input and uses
263 -- FND packages to set the parameters
264 --
265 FUNCTION get_org_id RETURN NUMBER IS
266 --
267 l_org_id NUMBER(15);
268 --
269 BEGIN
270 --
271 l_org_id := get_org_id(fnd_global.employee_id, fnd_global.resp_id, fnd_global.resp_appl_id);
272 --
273 RETURN(l_org_id);
274 --
275 END get_org_id;
276 --
277 END HRI_BPL_SECURITY;
[Home] [Help]