17: --
18: BEGIN
19: --
20:
21: IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
22: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
23: MODULE => g_module || '.' || l_procedure,
24: MESSAGE => 'Entering ' || l_procedure);
25: END IF;
18: BEGIN
19: --
20:
21: IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
22: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
23: MODULE => g_module || '.' || l_procedure,
24: MESSAGE => 'Entering ' || l_procedure);
25: END IF;
26:
28: INTO l_user
29: FROM fnd_user
30: WHERE user_id = p_user_id;
31:
32: IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
33: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
34: MODULE => g_module || '.' || l_procedure,
35: MESSAGE => 'sso user is ' || l_user);
36: END IF;
29: FROM fnd_user
30: WHERE user_id = p_user_id;
31:
32: IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
33: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
34: MODULE => g_module || '.' || l_procedure,
35: MESSAGE => 'sso user is ' || l_user);
36: END IF;
37:
35: MESSAGE => 'sso user is ' || l_user);
36: END IF;
37:
38: IF l_user IS NULL THEN
39: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
40: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
41: MODULE => g_module || '.' || l_procedure,
42: MESSAGE => 'sso user is not found for user_id ' ||
43: p_user_id);
36: END IF;
37:
38: IF l_user IS NULL THEN
39: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
40: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
41: MODULE => g_module || '.' || l_procedure,
42: MESSAGE => 'sso user is not found for user_id ' ||
43: p_user_id);
44: END IF;
43: p_user_id);
44: END IF;
45: END IF;
46:
47: IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
48: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
49: MODULE => g_module || '.' || l_procedure,
50: MESSAGE => 'Exiting ' || l_procedure);
51: END IF;
44: END IF;
45: END IF;
46:
47: IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
48: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
49: MODULE => g_module || '.' || l_procedure,
50: MESSAGE => 'Exiting ' || l_procedure);
51: END IF;
52:
53: RETURN(l_user);
54:
55: EXCEPTION
56: WHEN OTHERS THEN
57: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
58: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
59: MODULE => g_module || '.' || l_procedure,
60: MESSAGE => 'Error whilst finding SSO entry' ||
61: ' - ' || sqlerrm);
54:
55: EXCEPTION
56: WHEN OTHERS THEN
57: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
58: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
59: MODULE => g_module || '.' || l_procedure,
60: MESSAGE => 'Error whilst finding SSO entry' ||
61: ' - ' || sqlerrm);
62: END IF;
130: --
131: BEGIN
132: --
133:
134: IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
135: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
136: MODULE => g_module || '.' || l_procedure,
137: MESSAGE => 'Entering ' || l_procedure);
138: END IF;
131: BEGIN
132: --
133:
134: IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
135: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
136: MODULE => g_module || '.' || l_procedure,
137: MESSAGE => 'Entering ' || l_procedure);
138: END IF;
139:
168:
169: EXCEPTION
170: WHEN OTHERS THEN
171:
172: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
173: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
174: MODULE => g_module || '.' || l_procedure,
175: MESSAGE => 'No user found, using defaults');
176: END IF;
169: EXCEPTION
170: WHEN OTHERS THEN
171:
172: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
173: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
174: MODULE => g_module || '.' || l_procedure,
175: MESSAGE => 'No user found, using defaults');
176: END IF;
177:
176: END IF;
177:
178: END;
179:
180: IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
181: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
182: MODULE => g_module || '.' || l_procedure,
183: MESSAGE => 'APP_USER is ' || l_APP_USER);
184: END IF;
177:
178: END;
179:
180: IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
181: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
182: MODULE => g_module || '.' || l_procedure,
183: MESSAGE => 'APP_USER is ' || l_APP_USER);
184: END IF;
185:
189: -- create user for the first time
190: -- can not store a null password in the SSO
191: -- so user can not already exist
192:
193: IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
194: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
195: MODULE => g_module || '.' || l_procedure,
196: MESSAGE => 'creating new user');
197: END IF;
190: -- can not store a null password in the SSO
191: -- so user can not already exist
192:
193: IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
194: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
195: MODULE => g_module || '.' || l_procedure,
196: MESSAGE => 'creating new user');
197: END IF;
198:
246: p_user_prefs => l_USER_PREFS);
247:
248: EXCEPTION
249: WHEN OTHERS THEN
250: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
251: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
252: MODULE => g_module || '.' || l_procedure,
253: MESSAGE => 'new user error (pstore_add_userinfo) - '
254: || ' has the user ever signed in via Portal? - '
247:
248: EXCEPTION
249: WHEN OTHERS THEN
250: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
251: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
252: MODULE => g_module || '.' || l_procedure,
253: MESSAGE => 'new user error (pstore_add_userinfo) - '
254: || ' has the user ever signed in via Portal? - '
255: || sqlerrm);
289: p_user_prefs => l_USER_PREFS);
290:
291: EXCEPTION
292: WHEN OTHERS THEN
293: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
294: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
295: MODULE => g_module || '.' || l_procedure,
296: MESSAGE => 'existing user error - ' || sqlerrm);
297: END IF;
290:
291: EXCEPTION
292: WHEN OTHERS THEN
293: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
294: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
295: MODULE => g_module || '.' || l_procedure,
296: MESSAGE => 'existing user error - ' || sqlerrm);
297: END IF;
298: RAISE;
301:
302: COMMIT;
303:
304:
305: IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
306: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
307: MODULE => g_module || '.' || l_procedure,
308: MESSAGE => 'Exiting ' || l_procedure);
309: END IF;
302: COMMIT;
303:
304:
305: IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
306: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
307: MODULE => g_module || '.' || l_procedure,
308: MESSAGE => 'Exiting ' || l_procedure);
309: END IF;
310:
315: WHEN NO_DATA_FOUND THEN
316: -- if we raise no_data_found to a calling
317: -- select, it just interprets it as no data
318: -- and not as an exception
319: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
320: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
321: MODULE => g_module || '.' || l_procedure,
322: MESSAGE => 'Error whilst managing user entry' ||
323: ' - ' || sqlerrm);
316: -- if we raise no_data_found to a calling
317: -- select, it just interprets it as no data
318: -- and not as an exception
319: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
320: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
321: MODULE => g_module || '.' || l_procedure,
322: MESSAGE => 'Error whilst managing user entry' ||
323: ' - ' || sqlerrm);
324: END IF;
323: ' - ' || sqlerrm);
324: END IF;
325: RAISE program_error;
326: WHEN OTHERS THEN
327: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
328: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
329: MODULE => g_module || '.' || l_procedure,
330: MESSAGE => 'Error whilst managing user entry' ||
331: ' - ' || sqlerrm);
324: END IF;
325: RAISE program_error;
326: WHEN OTHERS THEN
327: IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
328: FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
329: MODULE => g_module || '.' || l_procedure,
330: MESSAGE => 'Error whilst managing user entry' ||
331: ' - ' || sqlerrm);
332: END IF;