1 package body APP_SESSION as
2 /* $Header: AFSCSESB.pls 120.7 2012/04/04 19:06:31 jwsmith ship $ */
3
4 --
5 -- Initialize
6 -- Initialize session in apps schema
7 --
8 procedure Initialize
9 is
10 l_apps_sname varchar2(30);
11 begin
12 -- Get the apps schema name
13
14 select oracle_username
15 into l_apps_sname
16 from fnd_oracle_userid
17 where oracle_id = 900;
18
19 -- Set the session schema
20
21 execute immediate 'alter session set current_schema = ' ||l_apps_sname;
22 end Initialize;
23
24 --
25 -- Get_Icx_Cookie_Name
26 -- Get the name of the cookie containing the icx_session_id
27 -- RETURNS
28 -- Cookie Name
29 --
30 function Get_Icx_Cookie_Name return varchar2
31 is
32 begin
33 return (fnd_session_management.getSessionCookieName);
34 end Get_Icx_Cookie_Name;
35
36 --
37 -- Create_Icx_Session
38 -- Create or re-establish an ICX session for the identified user.
39 -- IN
40 -- p_sso_guid - the user's SSO guid (required)
41 -- p_old_icx_cookie_value - the user's previous cookie value, if
42 -- trying to re-establish an existing session (optional)
43 -- p_resp_appl_short_name - the application short name of the responsibility
44 -- p_responsibility_key - the responsibility key
45 -- p_security_group_key - the security group key
46 -- OUT
47 -- p_icx_cookie_value - the new ICX session cookie value
48 -- RAISES
49 -- SSO_USER_UNKNOWN - if there is no user corresponding to the SSO guid
50 -- SESSION_CREATION_FAILED - if a session could not be created
51 -- SECURITY_CONTEXT_INVALID - if the user, responsibility application
52 -- short name, responsibility, and security group do not form a valid
53 -- security context
54 --
55 procedure Create_Icx_Session(
56 p_sso_guid in varchar2,
57 p_old_icx_cookie_value in varchar2 default null,
58 p_resp_appl_short_name in varchar2 default null,
59 p_responsibility_key in varchar2 default null,
60 p_security_group_key in varchar2 default null,
61 p_icx_cookie_value out nocopy varchar2)
62 is
63 l_session_id number; -- session id from cookie
64 l_user_guid varchar2(32); -- guid of current session user
65 l_user_record Apps_User_Type;
66 l_session_valid boolean := false; -- flag if current session is still valid
67
68 l_user_id number := null;
69 l_resp_appl_id number;
70 l_responsibility_id number;
71 l_security_group_id number;
72 l_status varchar2(30);
73
74 begin
75 if(p_resp_appl_short_name is not null or
76 p_responsibility_key is not null or
77 p_security_group_key is not null) then
78
79 -- get the application_id, responsibility_id, and security_group_id
80
81 begin
82 select application_id
83 into l_resp_appl_id
84 from fnd_application
85 where application_short_name = p_resp_appl_short_name;
86
87 select responsibility_id
88 into l_responsibility_id
89 from fnd_responsibility
90 where application_id = l_resp_appl_id
91 and responsibility_key = p_responsibility_key;
92
93 select security_group_id
94 into l_security_group_id
95 from fnd_security_groups
96 where security_group_key = p_security_group_key;
97 exception
98 when no_data_found then
99 raise SECURITY_CONTEXT_INVALID;
100 end;
101 end if;
102
103 if (p_old_icx_cookie_value is not null) then
104 -- try to re-establish the existing session
105 -- check that the session is still valid and that the users match
106
107 l_session_id := -1;
108
109 begin
110 -- turn the cookie into a session id
111
112 l_session_id := fnd_session_utilities.XSID_to_SessionID(
113 p_old_icx_cookie_value);
114 exception
115 when others then
116 null;
117 end;
118
119 if(l_session_id <> -1) then
120 -- get the user guid and user id for the session
121
122 begin
123 select fu.user_guid,
124 fu.user_id
125 into l_user_guid,
126 l_user_id
127 from icx_sessions ses,
128 fnd_user fu
129 where ses.user_id = fu.user_id
130 and ses.session_id = l_session_id;
131
132 if(l_user_guid = p_sso_guid) then
133 -- the session user matches the SSO guid
134
135 if(l_responsibility_id is not null) then
136 -- validate the security context
137
138 fnd_user_resp_groups_api.validate_security_context(
139 p_user_id => l_user_id,
140 p_resp_appl_id => l_resp_appl_id,
141 p_responsibility_id => l_responsibility_id,
142 p_security_group_id => l_security_group_id,
143 x_status => l_status);
144
145 if(l_status <> 'Y') then
146 raise SECURITY_CONTEXT_INVALID;
147 end if;
148 end if;
149
150 -- check session status
151
152 l_status := fnd_session_management.check_session(l_session_id);
153
154 if (l_status = 'VALID') then
155 -- copy the old cookie value to the new so that
156 -- the cookie value is retained
157
158 p_icx_cookie_value := p_old_icx_cookie_value;
159
160 l_session_valid := true;
161 elsif (l_status = 'EXPIRED') then
162 -- reset the existing session
163
164 fnd_session_management.reset_session(l_session_id);
165
166 -- copy the old cookie value to the new so that
167 -- the cookie value is retained
168
169 p_icx_cookie_value := p_old_icx_cookie_value;
170
171 l_session_valid := true;
172 elsif (l_status = 'INVALID') then
173 -- do nothing; a new session will be created
174
175 null;
176 elsif (l_status = 'ERROR') then
177 -- do nothing; a new session will be created
178
179 null;
180 else
181 -- do nothing; a new session will be created
182
183 null;
184 end if;
185 end if;
186 exception
187 when no_data_found then
188 -- session no longer exists, can't reactivate
189
190 null;
191 end;
192 end if;
193 end if;
194
195 if(not l_session_valid) then
196 -- old session not given or unable to reactivate it
197 -- create a new session
198
199 -- get the default user_id for this SSO user
200 l_user_record := App_Session.Get_Default_User(p_sso_guid);
201 l_user_id := l_user_record.user_id;
202
203 if(l_responsibility_id is not null) then
204 -- validate the security context
205
206 fnd_user_resp_groups_api.validate_security_context(
207 p_user_id => l_user_id,
208 p_resp_appl_id => l_resp_appl_id,
209 p_responsibility_id => l_responsibility_id,
210 p_security_group_id => l_security_group_id,
211 x_status => l_status);
212
213 if(l_status <> 'Y') then
214 raise SECURITY_CONTEXT_INVALID;
215 end if;
216 end if;
217
218 -- create a new session for the user
219
220 l_session_id := fnd_session_management.createSession(l_user_id);
221
222 if(l_session_id = -1) then
223 raise SESSION_CREATION_FAILED;
224 end if;
225
226 -- turn the session id into a cookie value
227
228 p_icx_cookie_value := fnd_session_utilities.sessionID_to_XSID(l_session_id);
229 end if;
230
231 if(l_responsibility_id is not null) then
232 -- ICX initialization
233
234 fnd_session_management.initializeSSWAGlobals(
235 p_session_id => l_session_id,
236 p_resp_appl_id => l_resp_appl_id,
237 p_responsibility_id => l_responsibility_id,
238 p_security_group_id => l_security_group_id);
239
240 -- FND initialization
241
242 fnd_session_management.setSessionPrivate(
243 p_user_id => l_user_id,
244 p_responsibility_id => l_responsibility_id,
245 p_resp_appl_id => l_resp_appl_id,
246 p_security_group_id => l_security_group_id,
247 p_date_format => fnd_session_management.g_date_format,
248 p_language => fnd_session_management.g_language,
249 p_date_language => fnd_session_management.g_date_language,
250 p_numeric_characters => fnd_session_management.g_numeric_characters,
251 p_nls_sort => fnd_session_management.g_nls_sort,
252 p_nls_territory => fnd_session_management.g_nls_territory);
253 end if;
254 end Create_Icx_Session;
255
256 --
257 -- Validate_Icx_Session
258 -- Validates an ICX session
259 -- IN
260 -- p_icx_cookie_value - the ICX session cookie value
261 -- RETURNS
262 -- Nothing. No exception means session is valid.
263 -- RAISES
264 -- SESSION_DOES_NOT_EXIST
265 -- SESSION_NOT_VALID
266 -- SESSION_EXPIRED
267 --
268 procedure Validate_Icx_Session(
269 p_icx_cookie_value in varchar2)
270 is
271 l_ses_status varchar2(30); -- Session status code
272 begin
273 -- Check session status
274
275 declare
276 l_session_id number;
277 l_transaction_id number;
278 l_user_id number;
279 l_responsibility_id number;
280 l_resp_appl_id number;
281 l_security_group_id number;
282 l_language_code varchar2(30);
283 l_nls_language varchar2(30);
284 l_date_format_mask varchar2(80);
285 l_nls_date_language varchar2(30);
286 l_nls_numeric_characters varchar2(30);
287 l_nls_sort varchar2(30);
288 l_nls_territory varchar2(30);
289 begin
290 l_ses_status := fnd_session_management.validateSessionPrivate(
291 c_XSID => p_icx_cookie_value,
292 session_id => l_session_id,
293 transaction_id => l_transaction_id,
294 user_id => l_user_id,
295 responsibility_id => l_responsibility_id,
296 resp_appl_id => l_resp_appl_id,
297 security_group_id => l_security_group_id,
298 language_code => l_language_code,
299 nls_language => l_nls_language,
300 date_format_mask => l_date_format_mask,
301 nls_date_language => l_nls_date_language,
302 nls_numeric_characters => l_nls_numeric_characters,
303 nls_sort => l_nls_sort,
304 nls_territory => l_nls_territory);
305 exception
306 when others then
307 raise SESSION_DOES_NOT_EXIST;
308 end;
309
310 if (l_ses_status = 'VALID') then
311 return;
312 elsif (l_ses_status = 'EXPIRED') then
313 raise SESSION_EXPIRED;
314 elsif (l_ses_status = 'INVALID') then
315 raise SESSION_NOT_VALID;
316 elsif (l_ses_status = 'ERROR') then
317 raise SESSION_NOT_VALID;
318 else
319 raise SESSION_NOT_VALID;
320 end if;
321 end Validate_Icx_Session;
322
323 --
324 -- Get_All_Linked_Users
325 -- Return a list of all FND users linked to an SSO guid
326 -- IN
327 -- p_sso_guid - the user's SSO guid (required)
328 -- RETURNS
329 -- An array of users linked to this guid
330 -- RAISES
331 -- SSO_USER_UNKNOWN - if no users are linked to this GUID
332 --
333 function Get_All_Linked_Users(
334 p_sso_guid in varchar2)
335 return Apps_User_Table
336 is
337 l_user_list Apps_User_Table;
338 found boolean := FALSE;
339
340 -- Fetch users linked to guid
341 -- Default 'N' as a placeholder for default_user flag in the fetch.
342 -- Default_user needs to be calculated using preferences.
343 --
344 -- Bug 5556799 - added user_guid TYPE to cursor as per recommendation
345 -- of ATG Perf Team (AAlomari), to bind with RAW index in GSI DB
346 cursor guid_users(l_sso_guid in fnd_user.user_guid%TYPE) is
347 select fu.user_id, fu.user_name, 'N'
348 from fnd_user fu
349 where fu.user_guid = l_sso_guid
350 order by user_id;
351
352 begin
353 -- Fetch all users linked to guid
354 open guid_users (p_sso_guid);
355 fetch guid_users bulk collect into l_user_list;
356 close guid_users;
357
358 -- Check for no linked rows and raise error
359 if (l_user_list.FIRST is null or l_user_list.FIRST <> 1) then
360 raise SSO_USER_UNKNOWN;
361 end if;
362
363 -- Calculate default_user flag
364 -- Check for a preference
365 for i in l_user_list.FIRST .. l_user_list.LAST loop
366 if (upper(substr(fnd_preference.get(l_user_list(i).user_name, 'APPS_SSO',
367 'DEFAULT_USER'),1,1)) = 'Y') then
368 l_user_list(i).default_user := 'Y';
369 found := TRUE;
370 exit;
371 end if;
372 end loop;
373
374 -- If no user has a preference, the use the
375 -- first user found as the "default default".
376 if (not found) then
377 l_user_list(l_user_list.FIRST).default_user := 'Y';
378 end if;
379
380 return l_user_list;
381 end Get_All_Linked_Users;
382
383 --
384 -- Get_Default_User
385 -- Get the default FND user linked to an SSO guid
386 -- IN
387 -- p_sso_guid - the user's SSO guid (required)
388 -- RETURNS
389 -- A record for default user linked to this guid
390 -- RAISES
391 -- SSO_USER_UNKNOWN - if no users are linked to this GUID
392 --
393 function Get_Default_User(
394 p_sso_guid in varchar2)
395 return Apps_User_Type
396 is
397 l_user_list Apps_User_Table;
398 begin
399 -- Get all users
400 l_user_list := Get_All_Linked_Users(p_sso_guid);
401
402 -- Find the default and return it
403 for i in l_user_list.FIRST .. l_user_list.LAST loop
404 if (l_user_list(i).default_user = 'Y') then
405 return (l_user_list(i));
406 end if;
407 end loop;
408
409 -- Should never happen, but just in case...
410 return l_user_list(l_user_list.FIRST);
411 end Get_Default_User;
412
413 end APP_SESSION;
[Home] [Help]