1 package body FND_AOLJ_UTIL as
2 /* $Header: AFAJUTLB.pls 120.4 2006/09/05 11:48:45 stadepal ship $ */
3
4
5 --
6 -- GENERIC_ERROR (Internal)
7 --
8 -- Set error message and raise exception for unexpected sql errors
9 --
10 procedure GENERIC_ERROR(routine in varchar2,
11 errcode in number,
12 errmsg in varchar2) is
13 begin
14 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
15 fnd_message.set_token('ROUTINE', routine);
16 fnd_message.set_token('ERRNO', errcode);
17 fnd_message.set_token('REASON', errmsg);
18 app_exception.raise_exception;
19 end;
20
21
22 /*
23 ** MULTI_PROFILE_VALUE_SPECIFIC -
24 ** Get profile values in group for a specific user/resp/appl combo
25 ** Default is user/resp/appl is current login.
26 */
27
28 function MULTI_PROFILE_VALUE_SPECIFIC(
29 NUMOFNAMES in number default 1,
30 NAMES in varchar2,
31 USER_ID in number default null,
32 RESPONSIBILITY_ID in number default null,
33 APPLICATION_ID in number default null)
34 return varchar2 is
35
36 profvalues VARCHAR2(32767);
37 profname VARCHAR2(80);
38 idx INTEGER;
39 namestart INTEGER;
40 nextdelim INTEGER;
41 savenames VARCHAR2(32767);
42 profnames VARCHAR2(32767);
43 profvalue VARCHAR2(128);
44
45 BEGIN
46 namestart := 0;
47 profnames := NAMES;
48 profvalues := '';
49 FOR idx IN 1..NUMOFNAMES LOOP
50 nextdelim :=INSTR(profnames,';');
51 profname:=SUBSTR(profnames,0, nextdelim-1);
52 profnames:=SUBSTR(profnames,nextdelim+1,LENGTH(names)-nextdelim);
53 profvalue := FND_PROFILE.VALUE_SPECIFIC(profname,USER_ID,RESPONSIBILITY_ID, APPLICATION_ID);
54 profvalues := profvalues || profvalue || ';' ;
55 END LOOP;
56 return profvalues;
57
58 end MULTI_PROFILE_VALUE_SPECIFIC;
59
60
61 /*
62 -- SET_NLS_CONTEXT
63 --
64 -- Description: Calls alter session to set the following values in DB.
65 -- NLS_LANGUAGE, NLS_DATE_FORMAT,NLS_DATE_LANGUAGE, NLS_SORT
66 -- NLS_TERRITORY,NLS_NUMERIC_CHARACTERS
67 */
68
69 PROCEDURE set_nls_context( p_nls_language IN VARCHAR2 DEFAULT NULL,
70 p_nls_date_format IN VARCHAR2 DEFAULT NULL,
71 p_nls_date_language IN VARCHAR2 DEFAULT NULL,
72 p_nls_numeric_characters IN VARCHAR2 DEFAULT NULL,
73 p_nls_sort IN VARCHAR2 DEFAULT NULL,
74 p_nls_territory IN VARCHAR2 DEFAULT NULL,
75 p_db_nls_language OUT NOCOPY VARCHAR2,
76 p_db_nls_date_format OUT NOCOPY VARCHAR2,
77 p_db_nls_date_language OUT NOCOPY VARCHAR2,
78 p_db_nls_numeric_characters OUT NOCOPY VARCHAR2,
79 p_db_nls_sort OUT NOCOPY VARCHAR2,
80 p_db_nls_territory OUT NOCOPY VARCHAR2,
81 p_db_nls_charset OUT NOCOPY VARCHAR2
82 ) IS
83 BEGIN
84 fnd_global.set_nls(
85 p_nls_language,
86 p_nls_date_format,
87 p_nls_date_language,
88 p_nls_numeric_characters,
89 p_nls_sort,
90 p_nls_territory,
91 p_db_nls_language,
92 p_db_nls_date_format,
93 p_db_nls_date_language,
94 p_db_nls_numeric_characters,
95 p_db_nls_sort,
96 p_db_nls_territory,
97 p_db_nls_charset
98 );
99 END set_nls_context;
100
101
102
103 /* -- getClassVersionFromDB
104 --
105 -- Prints out version information for Java classes stored in the database
106 --
107 -- getClassVersionFromDB(p_classname VARCHAR2) -- Print out the version for a single class
108 -- getClassVersionFromDB -- Print out version information for all Java classes
109 --
110 -- Calls a Java stored procedure which writes to System.out, so when used from SQL*Plus,
111 -- SET SERVEROUTPUT ON needs to be used.
112 --
113 -- EX: To display the version of Log.java from SQL*Plus:
114 --
115 -- SQL> set serveroutput on
116 -- SQL> execute fnd_aolj_util.getClassVersionFromDB('oracle.apps.fnd.common.Log');
117 -- >>> Class: oracle.apps.fnd.common.Log
118 -- ... : Log.java 115.21 2002/02/08 19:20:06 mskees ship $
119 --
120 -- PL/SQL procedure successfully completed.
121 */
122
123
124
125 /* PLSQL Wrapper for Java stored procedure of same name */
126 PROCEDURE displayClassVersion(p_classname VARCHAR2)
127 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.common.VersionInfo.displayClassVersion(java.lang.String)';
128
129
130 /* Print out version information for a single class */
131 PROCEDURE getClassVersionFromDB(p_classname VARCHAR2) IS
132 BEGIN
133
134 dbms_java.set_output(20000);
135
136 /* get the full name for the class, and replace all slashes with periods */
137 displayClassVersion(replace(dbms_java.longname(p_classname), '/', '.'));
138
139 END getClassVersionFromDB;
140
141
142 /* Print out version information for all Java classes owned by this user */
143 PROCEDURE getClassVersionFromDB IS
144
145 cursor c_classes IS
146 SELECT object_name
147 from user_objects
148 WHERE object_type = 'JAVA CLASS'
149 ORDER BY dbms_java.longname(object_name);
150
151 BEGIN
152
153 FOR rec IN c_classes LOOP
154 getClassVersionFromDB(rec.object_name);
155 END LOOP;
156
157 END getClassVersionFromDB;
158
159 /*
160 added for bug 4082741, to do session creation and validation in
161 one roundtrip. and added p_language_code parameter.
162 */
163 function createSession(
164 p_user_id in number,
165 p_server_id in varchar2,
166 p_language_code in varchar2,
167 p_function_code in varchar2,
168 p_validate_only in varchar2,
169 p_commit in boolean,
170 p_update in boolean,
171 p_responsibility_id in number,
172 p_function_id in number,
173 p_resp_appl_id in number,
174 p_security_group_id in number,
175 p_home_url in varchar2,
176 p_proxy_user in number,
177 mode_code in out nocopy varchar2,
178 session_id out nocopy number,
179 transaction_id out nocopy number,
180 user_id out nocopy number,
181 responsibility_id out nocopy number,
182 resp_appl_id out nocopy number,
183 security_group_id out nocopy number,
184 language_code out nocopy varchar2,
185 nls_language out nocopy varchar2,
186 date_format_mask out nocopy varchar2,
187 nls_date_language out nocopy varchar2,
188 nls_numeric_characters out nocopy varchar2,
189 nls_sort out nocopy varchar2,
190 nls_territory out nocopy varchar2,
191 login_id out nocopy number,
192 xsid out nocopy varchar2
193 ) return VARCHAR2 is
194 l_session_id number;
195 l_sso varchar2(2048);
196 l_xsid varchar2(32);
197 l_result varchar2(30);
198 l_user_id number;
199 begin
200 begin
201 select user_id into l_user_id from fnd_user
202 where user_id = p_user_id and
203 (start_date <= sysdate) and
204 (end_date is null or end_date>sysdate);
205 exception
206 when no_data_found then return 'N';
207 end;
208 -- first create a new icx session
209 if (mode_code is null) then
210 l_sso := fnd_profile.value('APPS_SSO');
211 -- todo: should default to 115P?
212 select decode(l_sso,
213 'SSWA', '115P',
214 'SSWA_SSO', '115J',
215 '115X') into mode_code from dual;
216 end if;
217 l_session_id := fnd_session_management.createSession(
218 p_user_id => p_user_id,
219 c_mode_code => mode_code,
220 p_server_id => p_server_id,
221 p_language_code => p_language_code,
222 p_home_url => p_home_url,
223 p_proxy_user => p_proxy_user);
224 if l_session_id = -1 then
225 return 'N';
226 end if;
227
228 -- then validate the session
229 l_xsid := FND_SESSION_UTILITIES.SessionID_to_XSID(l_session_id);
230 -- todo: l_xsid should not be NULL, but what if it is? return?
231 if l_xsid is null then
232 return 'N';
233 end if;
234 xsid := l_xsid;
235
236 l_result := is_Valid_ICX(
237 l_xsid,
238 p_function_code,
239 p_validate_only,
240 p_commit,
241 p_update,
242 p_responsibility_id,
243 p_function_id,
244 p_resp_appl_id,
245 p_security_group_id,
246 'N',
247 NULL,
248 session_id,
249 transaction_id,
250 user_id,
251 responsibility_id,
252 resp_appl_id,
253 security_group_id,
254 language_code,
255 nls_language,
256 date_format_mask,
257 nls_date_language,
258 nls_numeric_characters,
259 nls_sort,
260 nls_territory,
261 login_id,
262 true);
263 if l_result <> 'VALID' then
264 return 'N';
265 else
266 return 'Y';
267 end if;
268 end createSession;
269
270 function convertGuestSession(
271 p_user_id in number,
272 p_server_id in varchar2,
273 p_session_id in varchar2,
274 p_language_code in varchar2,
275 p_function_code in varchar2,
276 p_validate_only in varchar2,
277 p_commit in boolean,
278 p_update in boolean,
279 p_responsibility_id in number,
280 p_function_id in number,
281 p_resp_appl_id in number,
282 p_security_group_id in number,
283 p_home_url in varchar2,
284 p_mode_code in out nocopy varchar2,
285 session_id out nocopy number,
286 transaction_id out nocopy number,
287 user_id out nocopy number,
288 responsibility_id out nocopy number,
289 resp_appl_id out nocopy number,
290 security_group_id out nocopy number,
291 language_code out nocopy varchar2,
292 nls_language out nocopy varchar2,
293 date_format_mask out nocopy varchar2,
294 nls_date_language out nocopy varchar2,
295 nls_numeric_characters out nocopy varchar2,
296 nls_sort out nocopy varchar2,
297 nls_territory out nocopy varchar2,
298 login_id out nocopy number
299 ) return VARCHAR2 is
300 l_convert_result varchar2(1);
301 l_xsid varchar2(32);
302 l_result varchar2(30);
303 begin
304 -- first update icx session
305 l_convert_result := fnd_session_management.convertGuestSession(
306 p_user_id => p_user_id,
307 p_server_id => p_server_id,
308 p_session_id => p_session_id,
309 p_language_code => p_language_code,
310 p_home_url => p_home_url,
311 p_mode_code => p_mode_code);
312 if (l_convert_result = 'N') then
313 return 'N';
314 end if;
315
316 -- then validate the session
317 l_result := is_Valid_ICX(
318 p_session_id,
319 p_function_code,
320 p_validate_only,
321 p_commit,
322 p_update,
323 p_responsibility_id,
324 p_function_id,
325 p_resp_appl_id,
326 p_security_group_id,
327 'N',
328 NULL,
329 session_id,
330 transaction_id,
331 user_id,
332 responsibility_id,
333 resp_appl_id,
334 security_group_id,
335 language_code,
336 nls_language,
337 date_format_mask,
338 nls_date_language,
339 nls_numeric_characters,
340 nls_sort,
341 nls_territory,
342 login_id,
343 true);
344 if (p_mode_code is null) then
345 p_mode_code := fnd_session_management.g_mode_code;
346 end if;
347 if (l_result <> 'VALID') then
348 return 'N';
349 else
350 return 'Y';
351 end if;
352 end convertGuestSession;
353
354 /* -- is_Valid_ICX() -- For AOL INTERNAL USE ONLY!!!!
355 This function is a wrapper to ICX_SEC.validateSessionPrivate and is added for
356 bug 2246010, to synchronise with ICX changes and to provide for a single call
357 interface from all WebAppsContext.validateSession() methods via the method
358 WebAppsContext.doValidateSession().
359 */
360 function is_Valid_ICX(
361 p_session_id in varchar2,
362 p_function_code in varchar2,
363 p_validate_only in varchar2,
364 p_commit in boolean,
365 p_update in boolean,
366 p_responsibility_id in number,
367 p_function_id in number,
368 p_resp_appl_id in number,
369 p_security_group_id in number,
370 p_validate_mode_on in varchar2,
371 p_transaction_id in varchar2,
372 session_id out nocopy number,
376 resp_appl_id out nocopy number,
373 transaction_id out nocopy number,
374 user_id out nocopy number,
375 responsibility_id out nocopy number,
377 security_group_id out nocopy number,
378 language_code out nocopy varchar2,
379 nls_language out nocopy varchar2,
380 date_format_mask out nocopy varchar2,
381 nls_date_language out nocopy varchar2,
382 nls_numeric_characters out nocopy varchar2,
383 nls_sort out nocopy varchar2,
384 nls_territory out nocopy varchar2,
385 login_id out nocopy number,
386 p_isEncrypt in boolean ) return varchar2 is
387
388 l_result varchar2(30);
389 l_encrypted_session_id varchar2(2048);
390 l_encrypted_tranx_id varchar2(2048);
391 BEGIN
392
393 /* Added code to disable tracing for bug 3310943. */
394
395 if fnd_trace.is_trace_enabled(fnd_trace.SQL_REGULAR) then
396 fnd_trace.stop_trace;
397 end if;
398
399 /* If the strings for session_id and tranx_id are already encrypted just pass
400 them through else covert back to numbers and encrypt using the routine that
401 validateSessionPrivate will use internally - this seems wastefull, but it is
402 the only way to unify the many WebAppsContext.validateSession() methods to
403 a single call to ICX_SEC.validateSessionPrivate() */
404 if p_isEncrypt then
405 l_encrypted_session_id := p_session_id;
406 l_encrypted_tranx_id := p_transaction_id;
407 else
408 if p_session_id is null then
409 l_encrypted_session_id := p_session_id;
410 else
411 l_encrypted_session_id := fnd_session_utilities.sessionID_to_xsid( TO_NUMBER(p_session_id) );
412
413 end if;
414 if p_transaction_id is null then
415 l_encrypted_tranx_id := p_transaction_id;
416 else
417 l_encrypted_tranx_id := fnd_session_utilities.transactionID_to_xtid( TO_NUMBER(p_transaction_id) );
418 end if;
419 end if;
420
421 l_result := fnd_session_management.validateSessionPrivate(
422 c_xsid => l_encrypted_session_id,
423 c_function_code => p_function_code,
424 c_commit => p_commit,
425 c_update => p_update,
426 c_responsibility_id => p_responsibility_id,
427 c_function_id => p_function_id,
428 c_resp_appl_id => p_resp_appl_id,
429 c_security_group_id => p_security_group_id,
430 c_validate_mode_on => p_validate_mode_on,
431 c_xtid => l_encrypted_tranx_id,
432 session_id => session_id,
433 transaction_id => transaction_id,
434 user_id => user_id,
435 responsibility_id => responsibility_id,
436 resp_appl_id => resp_appl_id,
437 security_group_id => security_group_id,
438 language_code => language_code,
439 nls_language => nls_language,
440 date_format_mask => date_format_mask,
441 nls_date_language => nls_date_language,
442 nls_numeric_characters => nls_numeric_characters,
443 nls_sort => nls_sort,
444 nls_territory => nls_territory);
445
446 login_id := fnd_session_management.g_login_id;
447 return l_result;
448
449 END is_Valid_ICX;
450
451
452 /* PLSQL Wrapper for Java stored procedure of same name */
453 PROCEDURE AOLJ_RUP(dummy VARCHAR2)
454 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.common.VersionInfo.AOLJ_RUP(java.lang.String)';
455
456 /* Print out version information for the AOL/J RUP */
457 PROCEDURE display_AOLJ_RUP IS
458 BEGIN
459
460 dbms_java.set_output(20000);
461
462 AOLJ_RUP('');
463
464 END display_AOLJ_RUP;
465
466 end FND_AOLJ_UTIL;
[Home] [Help]