[Home] [Help]
PACKAGE BODY: APPS.FND_OID_UTIL
Source
1 package body fnd_oid_util as
2 /* $Header: AFSCOUTB.pls 120.36.12020000.2 2012/07/23 15:18:37 ctilley ship $ */
3 --
4 -- Start of Package Globals
5
6 G_MODULE_SOURCE constant varchar2(80) := 'fnd.plsql.oid.fnd_oid_util.';
7
8 -- FIXME: For compiling get_key, PutOIDEvent only - Should be removed
9 key_guid varchar2(8);
10 procedure validate_OID_preferences (
11 my_host varchar2,
12 my_port varchar2,
13 my_user varchar2,
14 my_pwd varchar2
15 );
16 procedure validate_preference (
17 my_preference_name varchar2,
18 my_preference_value varchar2
19 );
20
21 -- End of Package Globals
22 --
23 -------------------------------------------------------------------------------
24 function unbind(p_session in out nocopy dbms_ldap.session) return pls_integer
25 is
26 retval pls_integer;
27 begin
28 retval := dbms_ldap.unbind_s(p_session);
29 return retval;
30 exception
31 when others then return null;
32 end;
33 --
34 -------------------------------------------------------------------------------
35 function get_orclappname return varchar2 is
36
37 l_module_source varchar2(256);
38 orclAppName varchar2(256);
39
40 begin
41
42 l_module_source := G_MODULE_SOURCE || 'get_orclappname: ';
43
44 -- Performance bug 5001849 - now using the FND API
45
46 orclAppName := fnd_preference.get(p_user_name => '#INTERNAL',
47 p_module_name => 'LDAP_SYNCH',
48 p_pref_name => 'USERNAME');
49
50
51 return orclAppName;
52 exception
53 when others then
54 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
55 then
56 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
57 end if;
58 raise;
59
60 end get_orclappname;
61 --
62 -------------------------------------------------------------------------------
63 procedure entity_changes(p_username in varchar2) is
64
65 l_module_source varchar2(256);
66 my_flavor varchar2(5);
67 l_user_name varchar2(100);
68 my_userid number;
69 my_start date;
70 my_end date;
71 my_parms wf_parameter_list_t;
72 l_allow_sync varchar2(1);
73 l_profile_defined boolean;
74
75 begin
76 l_module_source := G_MODULE_SOURCE || 'entity_changes: ';
77
78 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
79 then
80 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
81 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
82 , 'p_username = ' || p_username);
83 end if;
84 -- FIXME: Need to move this out as a separate function
85 begin
86 select decode(employee_id,
87 null, decode(customer_id, null, 'FND', 'TCA'),
88 'HR'),
89 user_id, start_date, end_date
90 into my_flavor, my_userid, my_start, my_end
91 from fnd_user
92 where user_name = p_username;
93 exception
94 when no_data_found then
95 my_flavor := 'FND';
96 my_userid := NULL;
97 end;
98 -- Fix bug 4245881, don't sync inactive users
99 if (my_start <= sysdate and (my_end is null or my_end > sysdate)) then
100 insert into wf_entity_changes(
101 entity_type, entity_key_value, flavor, change_date)
102 values('USER', p_username, my_flavor, sysdate);
103 elsif my_start > sysdate then
104 wf_event.AddParameterToList('USER_NAME', p_username, my_parms);
105 wf_util.call_me_later(
106 p_callback => 'wf_oid.future_callback',
107 p_when => my_start,
108 p_parameters => my_parms);
109 elsif my_end < sysdate then
110 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
111 then
112 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'We don''t sync end-dated users to OID: ' || p_username);
113 end if;
114 end if;
115 --We don't propagate end-dating events to OID in this asynchronous process.
116 --This code can be uncommented when supporting logic for propagating end-dating events
117 --is implemented.
118 /*if (my_end > sysdate)
119 wf_event.AddParameterToList('USER_NAME', p_username, my_parms);
120 wf_util.call_me_later(
121 p_callback => 'wf_oid.future_callback',
122 p_when => my_end,
123 p_parameters => my_parms);
124 end if;*/
125 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
126 then
127 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
128 end if;
129
130 exception
131 when others then
132 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
133 then
134 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
135 end if;
136 raise;
137 end entity_changes;
138 --
139 -------------------------------------------------------------------------------
140 function get_user_attributes(
141 p_userguid in varchar2
142 , p_user_name out nocopy varchar2
143 ) return ldap_attr_list is
144
145 l_module_source varchar2(256);
146 l_session dbms_ldap.session;
147 l_result_message dbms_ldap.message;
148 l_attrs dbms_ldap.string_collection;
149 l_entry_message dbms_ldap.message;
150 l_ber_element dbms_ldap.ber_element;
151 l_values dbms_ldap.string_collection;
152 l_attribute_name varchar2(256);
153 l_attribute_value varchar2(4000);
154 l_attribute_list ldap_attr_list;
155 l_index number;
156 l_retval pls_integer;
157 l_orclcommonnicknameattr varchar2(256);
158 flag pls_integer;
159 l_session_flag boolean := false;
160 begin
161 l_module_source := G_MODULE_SOURCE || 'get_user_attributes: ';
162 l_index := 1;
163 l_retval := -1;
164
165 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
166 then
167 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source || 'Begin'
168 , 'p_userguid = ' || p_userguid);
169 end if;
170
171 l_session := fnd_ldap_util.c_get_oid_session(flag);
172 l_session_flag := true; /* fix for bug 8271359 */
173 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
174 then
175 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag = true' );
176 end if;
177
178 -- 8580552
179 p_user_name := fnd_ldap_user.get_username_from_guid(p_userguid);
180 -- l_orclcommonnicknameattr := upper(fnd_ldap_util.get_orclcommonnicknameattr(p_user_name));
181 -- Query up the user's attributes from OID using user's GUID
182 l_retval := dbms_ldap.search_s(
183 ld => l_session
184 , base => ' '
185 , scope => dbms_ldap.scope_subtree
186 , filter => 'orclguid=' || p_userguid
187 , attrs => l_attrs
188 , attronly => 0
189 , res => l_result_message);
190
191 -- walk the results and convert to an ldap_attr_list
192 l_attribute_list := ldap_attr_list();
193 l_entry_message := dbms_ldap.first_entry(
194 ld => l_session
195 , msg => l_result_message);
196
197 if (l_entry_message is not null)
198 then
199 l_attribute_name := dbms_ldap.first_attribute(
200 ld => l_session
201 , ldapentry => l_entry_message
202 , ber_elem => l_ber_element);
203
204 while (l_attribute_name is not null)
205 loop
206 l_values := dbms_ldap.get_values(
207 ld => l_session
208 , ldapentry => l_entry_message
209 , attr => l_attribute_name);
210
211 if (l_values.count > 0)
212 then
213 l_attribute_value := substr(l_values(l_values.first), 1, 4000);
214 else
215 l_attribute_value := null;
216 end if;
217
218
219 l_attribute_list.extend;
220 l_attribute_list(l_index) := ldap_attr(
221 attr_name => l_attribute_name
222 , attr_value => l_attribute_value
223 , attr_bvalue => null
224 , attr_value_len => length(l_attribute_value)
225 , attr_type => 0
226 , attr_mod_op => 2);
227 l_index := l_index+1;
228
229 l_attribute_name := dbms_ldap.next_attribute(
230 ld => l_session
231 , ldapentry => l_entry_message
232 , ber_elem => l_ber_element);
233
234 end loop;
235 end if;
236
237 fnd_ldap_util.c_unbind(l_session,flag);
238 l_session_flag := false;
239
240 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
241 then
242 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag : = false ' );
243 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'LDAP SESSION CLOSED NORMALLY : ' );
244 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
245 end if;
246
247 return(l_attribute_list);
248
249 exception
250 when others then
251 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
252 then
253 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
254 end if;
255 /* Fix for 8271359*/
256 if l_session_flag = true then
257
258 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
259 then
260 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closing in EXCEPTION BLOCK - START ' );
261 end if;
262
263 fnd_ldap_util.c_unbind(l_session,flag);
264
265 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
266 then
267 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closed in EXCEPTION BLOCK - END ');
268 end if;
269 end if;
270 raise;
271 return null;
272 end get_user_attributes;
273 --
274 -------------------------------------------------------------------------------
275 function get_ldap_event_str(p_ldap_event in ldap_event)
276 return varchar2 is
277
278 l_module_source varchar2(256);
279 l_str varchar2(4000);
280
281 begin
282 l_module_source := G_MODULE_SOURCE || 'get_ldap_event_str: ';
283
284 if (p_ldap_event is not null) then
285 l_str := 'event_type: ' || p_ldap_event.event_type;
286 l_str := l_str || ', event_id: ' || p_ldap_event.event_id;
287 l_str := l_str || ', event_src: ' || p_ldap_event.event_src;
288 l_str := l_str || ', event_time: ' || p_ldap_event.event_time;
289 l_str := l_str || ', object_name: ' || p_ldap_event.object_name;
290 l_str := l_str || ', object_type: ' || p_ldap_event.object_type;
291 l_str := l_str || ', object_guid: ' || p_ldap_event.object_guid;
292 l_str := l_str || ', object_dn: ' || p_ldap_event.object_dn;
293 l_str := l_str || ', profile_id: ' || p_ldap_event.profile_id;
294
295 end if;
296
297 return l_str;
298
299 exception
300 when others then
301 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
302 then
303 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
304 end if;
305 raise;
306 return null;
307 end get_ldap_event_str;
308 --
309 -------------------------------------------------------------------------------
310 function get_ldap_attr_str(p_ldap_attr in ldap_attr)
311 return varchar2 is
312
313 l_str varchar2(4000);
314 l_module_source varchar2(256);
315
316 begin
317 l_module_source := G_MODULE_SOURCE || 'get_ldap_attr_str: ';
318
319 if (p_ldap_attr is not null) then
320 l_str := 'attr_name : ' || p_ldap_attr.attr_name;
321 l_str := l_str || ', attr_value: ' || p_ldap_attr.attr_value;
322 l_str := l_str || ', attr_value_len: ' || p_ldap_attr.attr_value_len;
323 l_str := l_str || ', attr_type: ' || p_ldap_attr.attr_type;
324 l_str := l_str || ', attr_mod_op: ' || p_ldap_attr.attr_mod_op;
325 end if;
326
327 return (l_str);
328
329 exception
330 when others then
331 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
332 then
333 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
334 end if;
335
336 raise;
337 return null;
338 end get_ldap_attr_str;
339 --
340 -------------------------------------------------------------------------------
341 function get_ldap_event_status_str(p_ldap_event_status in ldap_event_status)
342 return varchar2 is
343
344 l_module_source varchar2(256);
345 l_str varchar2(4000);
346
347 begin
348 l_module_source := G_MODULE_SOURCE || 'get_ldap_event_status_str: ';
349
350 if (p_ldap_event_status is not null) then
351 l_str := 'event_id : ' || p_ldap_event_status.event_id;
352 l_str := l_str || ', orclguid: ' || p_ldap_event_status.orclguid;
353 l_str := l_str || ', error_code: ' || p_ldap_event_status.error_code;
354 l_str := l_str || ', error_String: ' || p_ldap_event_status.error_String;
355 l_str := l_str || ', error_disposition: ' ||
356 p_ldap_event_status.error_disposition;
357 end if;
358
359 return (l_str);
360 exception
361 when others then
362 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
363 then
364 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
365 end if;
366
367 raise;
368 return null;
369 end get_ldap_event_status_str;
370 --
371 -------------------------------------------------------------------------------
372 function get_fnd_user(p_user_guid in varchar2)
373 return apps_user_key_type is
374
375
376 cursor cur_fnd_users is
377 select user_id, user_name, user_guid, person_party_id
378 from fnd_user
379 where user_guid = hextoraw(p_user_guid);
380
381 l_module_source varchar2(256);
382 l_apps_user_key apps_user_key_type;
383 l_found boolean;
384
385 begin
386 l_module_source := G_MODULE_SOURCE || 'get_fnd_user: ';
387 l_found := false;
388
389 open cur_fnd_users;
390 fetch cur_fnd_users into l_apps_user_key.user_id, l_apps_user_key.user_name
391 , l_apps_user_key.user_guid, l_apps_user_key.person_party_id;
392 l_found := cur_fnd_users%found;
393
394 if (not l_found)
395 then
396 l_apps_user_key.user_guid := null;
397 l_apps_user_key.user_id := null;
398 l_apps_user_key.user_name := null;
399 l_apps_user_key.person_party_id := null;
400 end if;
401 close cur_fnd_users;
402
403 return (l_apps_user_key);
404
405 exception
406 when others then
407 if (cur_fnd_users%isopen)
408 then
409 close cur_fnd_users;
410 end if;
411
412 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
413 then
414 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
415 end if;
416
417 raise;
418 return null;
419 end get_fnd_user;
420 --
421 -------------------------------------------------------------------------------
422 function get_fnd_user(p_user_name in varchar2)
423 return apps_user_key_type is
424
425
426 cursor cur_fnd_users is
427 select user_id, user_name, user_guid, person_party_id
428 from fnd_user
429 where user_name = upper(p_user_name);
430
431 l_module_source varchar2(256);
432 l_apps_user_key apps_user_key_type;
433 l_found boolean;
434
435 begin
436 l_module_source := G_MODULE_SOURCE || 'get_fnd_user: ';
437 l_found := false;
438
439 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
440 then
441 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
442 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
443 , 'p_user_name = ' || p_user_name);
444 end if;
445
446 open cur_fnd_users;
447 fetch cur_fnd_users into l_apps_user_key.user_id, l_apps_user_key.user_name
448 , l_apps_user_key.user_guid, l_apps_user_key.person_party_id;
449 l_found := cur_fnd_users%found;
450
451 if (not l_found)
452 then
453 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
454 then
455 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
456 ,'User not found: ' || p_user_name);
457 end if;
458 l_apps_user_key.user_guid := null;
459 l_apps_user_key.user_id := null;
460 l_apps_user_key.user_name := null;
461 l_apps_user_key.person_party_id := null;
462 else
463 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
464 then
465 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
466 ,'User found: user_guid: '||l_apps_user_key.user_guid ||
467 ' user_name: '||l_apps_user_key.user_name);
468 end if;
469 end if;
470 close cur_fnd_users;
471
472 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
473 then
474 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
475 end if;
476
477 return (l_apps_user_key);
478
479 exception
480 when others then
481 if (cur_fnd_users%isopen)
482 then
483 close cur_fnd_users;
484 end if;
485
486 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
487 then
488 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
489 end if;
490
491 raise;
492 return null;
493 end get_fnd_user;
494 --
495 -------------------------------------------------------------------------------
496 function isUserEnabled(p_ldap_attr_list in ldap_attr_list) return boolean is
497
498 l_module_source varchar2(256);
499 l_user_enabled boolean;
500 l_inactive_start boolean;
501 l_inactive_end boolean;
502 l_start_date date;
503 l_start_date_oid_tz date;
504 l_end_date date;
505 l_end_date_oid_tz date;
506 begin
507
508 l_module_source := G_MODULE_SOURCE || 'isUserEnabled: ';
509
510 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
511 then
512 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
513 end if;
514 --default l_user_enable to true, if OID does not pass OrclisEnabled, we will synch the user.
515 l_user_enabled := true;
516 l_inactive_start := false;
517 l_inactive_end := false;
518
519 if (p_ldap_attr_list is not null AND p_ldap_attr_list.count > 0) then
520 for j in p_ldap_attr_list.first .. p_ldap_attr_list.last loop
521 if(upper(p_ldap_attr_list(j).attr_name) = G_ORCLISENABLED) then
522 if(p_ldap_attr_list(j).attr_value = G_DISABLED) then
523 l_user_enabled := false;
524 fnd_message.set_name ('FND', 'FND_SSO_USER_DISABLED');
525
526 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
527 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
528 'OrclisuserEnabled: Disabled');
529 end if;
530 end if;
531 end if;
532 -- if start greater than sysdate or end date less than equal sysdate
533 -- user is disabled.
534
535 if(upper(p_ldap_attr_list(j).attr_name) = G_ORCLACTIVESTARTDATE) then
536
537 if(p_ldap_attr_list(j).attr_value is not null) then
538
539 l_start_date_oid_tz := to_date(substr(p_ldap_attr_list(j).attr_value,1,14),G_YYYYMMDDHH24MISS);
540
541 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
542 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
543 'Start date in OID time zone:: '||to_char(l_start_date_oid_tz, 'YYYY-MM-DD HH:MI:SS PM'));
544 end if;
545
546 l_start_date := fnd_timezones_pvt.adjust_datetime(l_start_date_oid_tz, 'GMT', fnd_timezones.get_server_timezone_code);
547
548 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
549 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
550 'Start date in Ebiz time zone:: '||to_char(l_start_date, 'YYYY-MM-DD HH:MI:SS PM'));
551 end if;
552
553
554 if(l_start_date > sysdate) then
555 l_inactive_start := true;
556 fnd_message.set_name ('FND', 'FND_SSO_USER_FUTURE_DATE');
557
558 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
559 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OrclActive start date, greater than sysdate');
560 end if;
561 end if;
562
563 /* start date = null, is still a valid user */
564 /* else
565 * -- start date is null which is not a valid user
566 * l_inactive_start := true;
567 * fnd_message.set_name ('FND', 'FND_SSO_USER_DISABLED');*/
568
569 end if;
570 end if;
571
572 if(upper(p_ldap_attr_list(j).attr_name) = G_ORCLACTIVEENDDATE) then
573
574 if(p_ldap_attr_list(j).attr_value is not null) then
575
576 l_end_date_oid_tz := to_date(substr(p_ldap_attr_list(j).attr_value,1,14),G_YYYYMMDDHH24MISS);
577 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
578 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
579 'End date in OID time zone:: '||to_char(l_end_date_oid_tz, 'YYYY-MM-DD HH:MI:SS PM'));
580 end if;
581
582 l_end_date := fnd_timezones_pvt.adjust_datetime(l_end_date_oid_tz,
583 'GMT',
584 fnd_timezones.get_server_timezone_code);
585 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
586 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
587 'End date in Ebiz time zone:: '||to_char(l_end_date, 'YYYY-MM-DD HH:MI:SS PM'));
588 end if;
589
590 if(l_end_date < sysdate) then
591 l_inactive_end := true;
592 fnd_message.set_name ('FND', 'FND_SSO_USER_END_DATE');
593 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
594 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
595 'OrclActive end date, less than sysdate');
596 end if;
597 end if;
598
599 end if;
600 end if;
601
602 end loop;
603
604 else
605
606 -- parameter list not passed cannot figure user enabled or disabled.
607 if (fnd_log.LEVEL_EVENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)then
608 fnd_log.string(fnd_log.LEVEL_EVENT, l_module_source
609 , 'Parameter list not passed in event from OID cannot process further.');
610 end if;
611 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
612 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END->false');
613 end if;
614 return false;
615
616 end if;
617
618 -- return false if l_user_enabled is false or L_inactive_start is true or l_inactive_end is true.
619
620 if( not l_user_enabled or l_inactive_start or l_inactive_end) then
621 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
622 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Return false, user is disabled');
623 end if;
624 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
625 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END->false');
626 end if;
627 return false;
628 else
629 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
630 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Return true, user is enabled');
631 end if;
632 end if;
633
634 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
635 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END->true');
636 end if;
637 return true;
638
639
640 end isUserEnabled;
641 --
642 -------------------------------------------------------------------------------
643 procedure process_identity_add(p_event in ldap_event) is
644
645 l_module_source varchar2(256);
646 l_ldap_attr_list ldap_attr_list;
647 l_user_name fnd_user.user_name%type;
648 my_ent_type varchar2(50);
649 my_parms wf_parameter_list_t;
650 l_allow_identity_add varchar2(1);
651 l_profile_defined boolean;
652 l_user_enabled boolean;
653
654 begin
655 l_module_source := G_MODULE_SOURCE || 'process_identity_add: ';
656 l_ldap_attr_list := get_user_attributes(p_event.object_guid, l_user_name);
657
658 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
659 then
660 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
661 , 'Begin');
662 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
663 , 'p_event = ' || get_ldap_event_str(p_event));
664 end if;
665
666 --check for OrclIsEnabled flag and stop further processing
667 -- bug fix for bug #4583452
668
669 if (isUserEnabled (p_event.attr_list))
670 then
671
672 --RDESPOTO, Add IDENTITY_ADD, 11/09/2004
673 --Check site profile APPS_SSO_OID_IDENTITY
674 fnd_profile.get_specific(
675 name_z => 'APPS_SSO_OID_IDENTITY',
676 user_id_z => null,
677 val_z => l_allow_identity_add,
678 defined_z => l_profile_defined);
679 -- Check whether profile is defined
680 -- We don't receive IDENTITY_ADD events when application is registered
681 if (l_profile_defined and l_allow_identity_add = 'Y') then
682 -- Raise oracle.apps.identity.add
683 wf_event.AddParameterToList('CHANGE_SOURCE', G_OID, my_parms);
684 wf_event.AddParameterToList('ORCLGUID', p_event.object_guid, my_parms);
685 wf_event.AddParameterToList('CHANGE_TYPE', G_LOAD, my_parms);
686 save_to_cache(
687 p_ldap_attr_list => l_ldap_attr_list
688 , p_entity_type => wf_oid.IDENTITY_ADD
689 , p_entity_key_value => l_user_name);
690 wf_event.raise('oracle.apps.fnd.identity.add',
691 upper(l_user_name), null, my_parms);
692 -- Raise SUBSCRIPTION_ADD
693 send_subscription_add_to_OID
694 (p_orcl_guid => p_event.object_guid);
695
696 else
697 if (fnd_log.LEVEL_EVENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
698 fnd_log.string(fnd_log.LEVEL_EVENT, l_module_source
699 , 'APPS_SSO_OID_IDENTITY profile is Disabled.');
700 end if;
701
702 end if;
703
704
705 else
706
707 -- user is disabled do not synch. log it
708 if (fnd_log.LEVEL_EVENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
709 then
710 fnd_log.string(fnd_log.LEVEL_EVENT, l_module_source
711 , 'Orclisenabled is Disabled. so stopping further processing');
712 end if;
713
714 end if; --user is disabled
715
716 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
717 then
718 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
719 , 'End');
720 end if;
721
722 exception
723 when others then
724 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
725 then
726 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
727 end if;
728
729 raise;
730 end process_identity_add;
731 --
732 -------------------------------------------------------------------------------
733 -------------------------------------------------------------------------------
734 procedure process_identity_modify(p_event in ldap_event) is
735
736 cursor cur_fnd_users(p_user_guid in varchar2) is
737 select user_name, user_id
738 from fnd_user
739 where user_guid = hextoraw(p_user_guid);
740
741 l_module_source varchar2(256);
742 l_profiles apps_sso_user_profiles_type;
743 l_user_name fnd_user.user_name%type;
744 l_oid_user_name fnd_user.user_name%type;
745 l_user_id fnd_user.user_id%type;
746 l_ldap_attr_list ldap_attr_list;
747 l_count pls_integer:= 0;
748
749 begin
750 l_module_source := G_MODULE_SOURCE || 'process_identity_modify: ';
751 l_ldap_attr_list := get_user_attributes(p_event.object_guid, l_oid_user_name);
752
753 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
754 then
755 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
756 , 'Begin');
757 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
758 , 'p_event = ' || get_ldap_event_str(p_event));
759 end if;
760
761
762 open cur_fnd_users(p_event.object_guid);
763 loop
764 fetch cur_fnd_users into l_user_name, l_user_id;
765 exit when cur_fnd_users%notfound;
766 l_count := l_count+1;
767
768 l_profiles := fnd_ldap_mapper.map_sso_user_profiles(l_user_name);
769 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
770 then
771 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
772 , 'username:'||l_user_name||' LDAP_SYNC:'||l_profiles.ldap_sync||'
773 login_type:'||l_profiles.local_login );
774 end if;
775
776 if ( (l_profiles.ldap_sync = G_Y)
777 and (l_profiles.local_login <> G_LOCAL) )
778 then
779 --For AOl/J consumption
780 wf_entity_mgr.put_attribute_value(G_USER, l_user_name,
781 G_ORCLGUID, p_event.object_guid);
782 save_to_cache(
783 p_ldap_attr_list => p_event.attr_list
784 , p_entity_type => G_USER
785 , p_entity_key_value => l_user_name);
786 wf_entity_mgr.process_changes(G_USER, l_user_name, G_OID);
787 --For our consumption so that only we update TCA tables
788 save_to_cache(
789 p_ldap_attr_list => l_ldap_attr_list
790 , p_entity_type => wf_oid.IDENTITY_MODIFY
791 , p_entity_key_value => l_user_name);
792 wf_event.raise('oracle.apps.fnd.identity.modify',
793 upper(l_user_name), null, null);
794
795 end if;
796 end loop;
797 close cur_fnd_users;
798
799 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)THEN
800 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
801 , 'events risen:'||l_count);
802 END IF;
803
804 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
805 then
806 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
807 , 'End');
808 end if;
809
810 exception
811 when others then
812 if (cur_fnd_users%isopen)
813 then
814 close cur_fnd_users;
815 end if;
816
817 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
818 then
819 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
820 end if;
821
822 raise;
823 end process_identity_modify;
824 --
825 --
826 -------------------------------------------------------------------------------
827 procedure process_identity_delete(p_event in ldap_event) is
828
829 cursor cur_fnd_users(p_user_guid in varchar2) is
830 select user_name, user_id
831 from fnd_user
832 where user_guid = hextoraw(p_user_guid);
833
834 l_module_source varchar2(256);
835 l_profiles apps_sso_user_profiles_type;
836 l_user_name fnd_user.user_name%type;
837 l_user_id fnd_user.user_id%type;
838 my_parms wf_parameter_list_t;
839
840 begin
841 l_module_source := G_MODULE_SOURCE || 'process_identity_delete: ';
842
843 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
844 then
845 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
846 , 'Begin');
847 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
848 , 'p_event = ' || get_ldap_event_str(p_event));
849 end if;
850
851 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
852 then
853 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Get users');
854 end if;
855
856 open cur_fnd_users(p_event.object_guid);
857 loop
858 fetch cur_fnd_users into l_user_name, l_user_id;
859 exit when cur_fnd_users%notfound;
860
861 l_profiles := fnd_ldap_mapper.map_sso_user_profiles(l_user_name);
862
863 if ( (l_profiles.ldap_sync = G_Y)
864 and (l_profiles.local_login <> G_LOCAL) )
865 then
866 /*wf_entity_mgr.put_attribute_value(G_USER, l_user_name,
867 G_CACHE_CHANGED, G_YES);
868 -- Fix bug 4231145
869 wf_entity_mgr.put_attribute_value(G_USER, l_user_name,
870 G_ORCLGUID, p_event.object_guid);
871 wf_entity_mgr.process_changes(G_USER, l_user_name,
872 G_OID, G_DELETE);
873 */
874
875 /* Bug 13829710 - Raise the oracle.apps.fnd.identity.delete event so that
876 * administrators can add custom subscription
877 */
878
879 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
880 then
881 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Raise identity_delete event for user: '||l_user_name);
882 end if;
883
884 wf_entity_mgr.put_attribute_value(G_USER,l_user_name,G_CACHE_CHANGED,G_YES);
885 wf_event.AddParameterToList('CHANGE_SOURCE', G_OID, my_parms);
886 wf_event.AddParameterToList('ORCLGUID', p_event.object_guid, my_parms);
887 wf_event.AddParameterToList('CHANGE_TYPE', G_DELETE, my_parms);
888
889 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
890 then
891 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'added parameters');
892 end if;
893
894 save_to_cache(
895 p_ldap_attr_list => p_event.attr_list
896 , p_entity_type => wf_oid.IDENTITY_DELETE
897 , p_entity_key_value => l_user_name);
898
899 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
900 then
901 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'saved to cache...now raise event');
902 end if;
903
904 wf_event.raise('oracle.apps.fnd.identity.delete', upper(l_user_name), null, null);
905
906 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
907 then
908 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Unlink user');
909 end if;
910
911 -- don't call fnd_user_pkg.DisableUser(), it'd fail because user is deleted on OID
912
913 -- Bug 13829710: Moved the end dating of the user to the fnd_oid_subscriptions.identity_delete
914 -- Nulling out of user_guid should occur regardless
915 update fnd_user
916 set user_guid = null
917 where user_name = l_user_name;
918
919 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
920 then
921 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Successfully unlinked user and raised event');
922 end if;
923
924 end if;
925 end loop;
926 close cur_fnd_users;
927
928 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
929 then
930 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
931 , 'End');
932 end if;
933
934 exception
935 when others then
936 if (cur_fnd_users%isopen)
937 then
938 close cur_fnd_users;
939 end if;
940
941 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
942 then
943 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
944 end if;
945
946 raise;
947 end process_identity_delete;
948 --
949 -------------------------------------------------------------------------------
950 procedure process_subscription_add(p_event in ldap_event) is
951
952 l_module_source varchar2(256);
953 l_ldap_attr_list ldap_attr_list;
954 l_user_name fnd_user.user_name%type;
955 my_ent_type varchar2(50);
956 my_parms wf_parameter_list_t;
957
958 begin
959 l_module_source := G_MODULE_SOURCE || 'process_subscription_add: ';
960
961 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
962 then
963 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
964 , 'Begin');
965 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
966 , 'p_event = ' || get_ldap_event_str(p_event));
967 end if;
968
969 l_ldap_attr_list := get_user_attributes(p_event.object_guid, l_user_name);
970 if( isUserEnabled(l_ldap_attr_list)) then
971
972 -- Moving it out since we need the orclisenabled
973 -- l_ldap_attr_list := get_user_attributes(p_event.object_guid, l_user_name);
974 wf_entity_mgr.put_attribute_value(wf_oid.SUBSCRIPTION_ADD, l_user_name,
975 G_CACHE_CHANGED, G_YES);
976
977 save_to_cache(
978 p_ldap_attr_list => l_ldap_attr_list
979 , p_entity_type => wf_oid.SUBSCRIPTION_ADD
980 , p_entity_key_value => l_user_name);
981 my_ent_type := upper(wf_oid.SUBSCRIPTION_ADD);
982 wf_entity_mgr.put_attribute_value(my_ent_type, l_user_name,
983 'CACHE_CHANGED', 'NO');
984 wf_event.AddParameterToList('CHANGE_SOURCE', G_OID, my_parms);
985 wf_event.AddParameterToList('ORCLGUID', p_event.object_guid, my_parms);
986 wf_event.AddParameterToList('CHANGE_TYPE', G_LOAD, my_parms);
987 wf_event.raise('oracle.apps.fnd.subscription.add',
988 upper(l_user_name), null, my_parms);
989
990 else
991
992 if (fnd_log.LEVEL_EVENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
993 then
994 fnd_log.string(fnd_log.LEVEL_EVENT, l_module_source
995 , 'Orcluserenabled is disabled');
996 end if;
997
998 end if;
999
1000 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1001 then
1002 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1003 , 'End');
1004 end if;
1005
1006
1007
1008 exception
1009 when others then
1010 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1011 then
1012 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1013 end if;
1014
1015 raise;
1016 end process_subscription_add;
1017 --
1018 -------------------------------------------------------------------------------
1019 procedure process_subscription_delete(p_event in ldap_event) is
1020
1021 l_module_source varchar2(256);
1022
1023 begin
1024 l_module_source := G_MODULE_SOURCE || 'process_subscription_delete: ';
1025
1026 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1027 then
1028 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1029 , 'Begin');
1030 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1031 , 'p_event = ' || get_ldap_event_str(p_event));
1032 end if;
1033
1034 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1035 then
1036 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1037 , 'End');
1038 end if;
1039
1040 exception
1041 when others then
1042 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1043 then
1044 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1045 end if;
1046
1047 raise;
1048 end process_subscription_delete;
1049 --
1050 -------------------------------------------------------------------------------
1051 procedure synch_user_from_LDAP(
1052 p_user_name in fnd_user.user_name%type
1053 , p_result out nocopy pls_integer
1054 ) is
1055 l_module_source varchar2(256);
1056 l_apps_user_key apps_user_key_type;
1057 l_user_name fnd_user.user_name%type;
1058 l_ldap_attr_list ldap_attr_list;
1059 l_ldap_message fnd_oid_util.ldap_message_type;
1060 l_return_status varchar2(1);
1061
1062 PRAGMA AUTONOMOUS_TRANSACTION;
1063 begin
1064 l_module_source := G_MODULE_SOURCE || 'synch_user_from_LDAP: ';
1065
1066 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1067 then
1068 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1069 end if;
1070
1071 if(p_user_name is null)
1072 then
1073 raise user_name_null_exp;
1074 end if;
1075
1076 l_user_name := p_user_name;
1077 l_apps_user_key := fnd_oid_util.get_fnd_user(p_user_name => l_user_name);
1078
1079 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1080 then
1081 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1082 'l_user_name:'||l_user_name);
1083 end if;
1084
1085
1086 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1087 then
1088 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1089 'Trying to get ldap attribute list for GUID: '||l_apps_user_key.user_guid||'::');
1090 end if;
1091
1092
1093 if(l_apps_user_key.user_guid is null)
1094 then
1095 raise user_guid_null_exp;
1096 end if;
1097
1098 l_ldap_attr_list := fnd_oid_util.get_user_attributes(p_userguid => l_apps_user_key.user_guid,
1099 p_user_name => l_user_name);
1100 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1101 then
1102 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1103 'Got the ldap attribute list');
1104 end if;
1105
1106 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1107 then
1108 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1109 'Before calling map_ldap_message: l_user_name:'||l_user_name||' p_user_name:'||p_user_name||
1110 ' from l_apps_user_key:'||l_apps_user_key.user_name);
1111 end if;
1112
1113 fnd_ldap_mapper.map_ldap_message(p_user_name => p_user_name
1114 , p_ldap_attr_list => l_ldap_attr_list
1115 , p_ldap_message => l_ldap_message);
1116
1117 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1118 then
1119 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1120 'Got the ldap message ldap_message object name::'||l_ldap_message.object_name||'::');
1121 end if;
1122
1123 if (l_apps_user_key.person_party_id is not null)
1124 then
1125 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1126 then
1127 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1128 'Person Party exists in FND_USER for user:'||l_apps_user_key.user_name);
1129 end if;
1130 fnd_oid_users.hz_update(
1131 p_ldap_message => l_ldap_message
1132 , x_return_status => l_return_status);
1133
1134 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1135 then
1136 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1137 'after hz_update return Status: '||l_return_status);
1138 end if;
1139 else
1140 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1141 then
1142 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1143 'Person Party does NOT exist in FND_USER for user:'||l_apps_user_key.user_name||', creating a new TCA entry');
1144 end if;
1145 if (isTCAenabled('ADD')) then
1146 fnd_oid_users.hz_create(
1147 p_ldap_message => l_ldap_message
1148 , x_return_status => l_return_status);
1149 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1150 then
1151 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1152 'after hz_create return Status: '||l_return_status);
1153 end if;
1154 end if;
1155 end if;
1156 commit;
1157 if(l_return_status = FND_API.G_RET_STS_SUCCESS)
1158 then
1159 p_result := fnd_ldap_wrapper.G_SUCCESS;
1160 else
1161 p_result := fnd_ldap_wrapper.G_FAILURE;
1162 end if;
1163
1164 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1165 then
1166 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1167 end if;
1168
1169 exception
1170 when user_name_null_exp then
1171 rollback;
1172 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1173 then
1174 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source
1175 , 'Cannot call synch_user_from_LDAP will null username');
1176 end if;
1177 p_result := fnd_ldap_wrapper.G_FAILURE;
1178 when user_guid_null_exp then
1179 rollback;
1180 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1181 then
1182 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source
1183 , 'call to synch_user_from_LDAP failed since GUID is NULL');
1184 end if;
1185 p_result := fnd_ldap_wrapper.G_FAILURE;
1186 when others then
1187 rollback;
1188 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1189 then
1190 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1191 end if;
1192 raise;
1193 p_result := fnd_ldap_wrapper.G_FAILURE;
1194 end synch_user_from_LDAP;
1195 --
1196 -------------------------------------------------------------------------------
1197 procedure synch_user_from_LDAP_NO_AUTO(
1198 p_user_name in fnd_user.user_name%type
1199 , p_result out nocopy pls_integer
1200 ) is
1201 l_module_source varchar2(256);
1202 l_apps_user_key apps_user_key_type;
1203 l_user_name fnd_user.user_name%type;
1204 l_ldap_attr_list ldap_attr_list;
1205 l_ldap_message fnd_oid_util.ldap_message_type;
1206 l_return_status varchar2(1);
1207 begin
1208 l_module_source := G_MODULE_SOURCE || 'synch_user_from_LDAP_NO_AUTO: ';
1209
1210 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1211 then
1212 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1213 end if;
1214
1215 if(p_user_name is null)
1216 then
1217 raise user_name_null_exp;
1218 end if;
1219
1220 l_user_name := p_user_name;
1221 l_apps_user_key := fnd_oid_util.get_fnd_user(p_user_name => l_user_name);
1222
1223 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1224 then
1225 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1226 'l_user_name:'||l_user_name);
1227 end if;
1228
1229
1230 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1231 then
1232 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1233 'Trying to get ldap attribute list for GUID: '||l_apps_user_key.user_guid||'::');
1234 end if;
1235
1236
1237 if(l_apps_user_key.user_guid is null)
1238 then
1239 raise user_guid_null_exp;
1240 end if;
1241
1242 l_ldap_attr_list := fnd_oid_util.get_user_attributes(p_userguid => l_apps_user_key.user_guid,
1243 p_user_name => l_user_name);
1244 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1245 then
1246 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1247 'Got the ldap attribute list');
1248 end if;
1249
1250 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1251 then
1252 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1253 'Before calling map_ldap_message: l_user_name:'||l_user_name||' p_user_name:'||p_user_name||
1254 ' from l_apps_user_key:'||l_apps_user_key.user_name);
1255 end if;
1256
1257 fnd_ldap_mapper.map_ldap_message(p_user_name => p_user_name
1258 , p_ldap_attr_list => l_ldap_attr_list
1259 , p_ldap_message => l_ldap_message);
1260
1261 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1262 then
1263 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1264 'Got the ldap message ldap_message object name::'||l_ldap_message.object_name||'::');
1265 end if;
1266
1267 if (l_apps_user_key.person_party_id is not null)
1268 then
1269 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1270 then
1271 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1272 'Person Party exists in FND_USER for user:'||l_apps_user_key.user_name);
1273 end if;
1274 fnd_oid_users.hz_update(
1275 p_ldap_message => l_ldap_message
1276 , x_return_status => l_return_status);
1277
1278 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1279 then
1280 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1281 'after hz_update return Status: '||l_return_status);
1282 end if;
1283 else
1284 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1285 then
1286 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1287 'Person Party does NOT exist in FND_USER for user:'||l_apps_user_key.user_name||', creating a new TCA entry');
1288 end if;
1289 if (isTCAenabled('ADD')) then
1290 fnd_oid_users.hz_create(
1291 p_ldap_message => l_ldap_message
1292 , x_return_status => l_return_status);
1293 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1294 then
1295 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1296 'after hz_create return Status: '||l_return_status);
1297 end if;
1298 end if;
1299 end if;
1300 if(l_return_status = FND_API.G_RET_STS_SUCCESS)
1301 then
1302 p_result := fnd_ldap_wrapper.G_SUCCESS;
1303 else
1304 p_result := fnd_ldap_wrapper.G_FAILURE;
1305 end if;
1306
1307 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1308 then
1309 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1310 end if;
1311
1312 exception
1313 when user_name_null_exp then
1314 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1315 then
1316 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source
1317 , 'Cannot call synch_user_from_LDAP will null username');
1318 end if;
1319 p_result := fnd_ldap_wrapper.G_FAILURE;
1320 when user_guid_null_exp then
1321 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1322 then
1323 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source
1324 , 'call to synch_user_from_LDAP failed since GUID is NULL');
1325 end if;
1326 p_result := fnd_ldap_wrapper.G_FAILURE;
1327 when others then
1328 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1329 then
1330 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1331 end if;
1332 raise;
1333 p_result := fnd_ldap_wrapper.G_FAILURE;
1334 end synch_user_from_LDAP_NO_AUTO;
1335 --
1336 -------------------------------------------------------------------------------
1337 procedure on_demand_user_create(
1338 p_user_name in varchar2,
1339 p_user_guid in varchar2
1340 ) is
1341
1342 cursor cur_fnd_users is
1343 select user_id, start_date, end_date, encrypted_user_password
1344 from fnd_user
1345 where user_name = p_user_name
1346 and (user_guid is NULL or user_guid = hextoraw(p_user_guid))
1347 and sysdate >= start_date
1348 and (end_date is NULL or end_date > sysdate);
1349
1350 l_module_source varchar2(256);
1351 l_event_name varchar2(80);
1352 l_parmeter_list wf_parameter_list_t;
1353 l_result pls_integer;
1354 l_sub_add_result pls_integer;
1355 l_ldap_attr_list ldap_attr_list;
1356 l_ldap_message ldap_message_type;
1357 l_user_name fnd_user.user_name%type;
1358 l_rec cur_fnd_users%rowtype;
1359 l_found boolean;
1360 l_local_login varchar2(10);
1361 l_profile_defined boolean;
1362 l_user_id number;
1363 begin
1364 -- Make sure the event is seeded and downloaded
1365 l_module_source := G_MODULE_SOURCE || 'on_demand_user_create: ';
1366 l_event_name := 'oracle.apps.fnd.ondemand.create';
1367
1368 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1369 then
1370 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1371 end if;
1372
1373 if(p_user_name is null or p_user_guid is null)
1374 then
1375 raise user_name_null_exp;
1376 end if;
1377 if(p_user_guid is null)
1378 then
1379 raise user_guid_null_exp;
1380 end if;
1381
1382
1383 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1384 then
1385 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1386 'Before calling get_user_attributes username: '||p_user_name||' GUID: '||p_user_guid);
1387 end if;
1388 l_user_name := p_user_name;
1389
1390 -- Adding the following login for updating FAX and Email from OID when users are creared onDemand
1391 -- Refer to Bug 4411170
1392 l_ldap_attr_list := fnd_oid_util.get_user_attributes(p_userguid => p_user_guid,
1393 p_user_name => l_user_name);
1394
1395 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1396 then
1397 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Got ldap_attr_list');
1398 end if;
1399
1400 fnd_ldap_mapper.map_ldap_message(p_user_name => l_user_name
1401 , p_ldap_attr_list => l_ldap_attr_list
1402 , p_ldap_message => l_ldap_message);
1403
1404
1405 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1406 then
1407 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'After calling map_ldap_message');
1408 end if;
1409
1410 open cur_fnd_users;
1411 fetch cur_fnd_users into l_rec;
1412 l_found := cur_fnd_users%found;
1413 close cur_fnd_users;
1414
1415 if (l_found)
1416 then
1417 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1418 then
1419 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Found a linakble user: ');
1420 end if;
1421
1422 fnd_profile.get_specific(name_z => G_APPS_SSO_LOCAL_LOGIN,
1423 user_id_z => l_rec.user_id,
1424 val_z => l_local_login,
1425 defined_z => l_profile_defined);
1426 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1427 then
1428 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'APPS_SSO_LOCAL_LOGIN: '||l_local_login);
1429 end if;
1430 if(l_local_login <>G_LOCAL)
1431 then
1432 if(l_local_login = G_SSO)
1433 then
1434 fnd_user_pkg.UpdateUser(
1435 x_user_name=>p_user_name
1436 , x_owner=>null
1437 , x_unencrypted_password=>fnd_web_sec.EXTERNAL_PWD
1438 , x_email_address => l_ldap_message.mail
1439 , x_fax => l_ldap_message.facsimileTelephoneNumber
1440 , x_user_guid=>p_user_guid
1441 , x_change_source => fnd_user_pkg.change_source_oid
1442 );
1443
1444 else
1445 fnd_user_pkg.UpdateUser(
1446 x_user_name=>p_user_name
1447 , x_owner=>null
1448 , x_email_address => l_ldap_message.mail
1449 , x_fax => l_ldap_message.facsimileTelephoneNumber
1450 , x_user_guid=>p_user_guid
1451 , x_change_source => fnd_user_pkg.change_source_oid
1452 );
1453 end if;
1454 end if;
1455 else
1456 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1457 then
1458 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Could not find a linkable user: ');
1459 end if;
1460 -- Changed to CreateUserId - we need the user_id to set the profile.
1461 l_user_id := fnd_user_pkg.CreateUserId(
1462 x_user_name=>p_user_name
1463 , x_owner=>null
1464 , x_unencrypted_password=>fnd_web_sec.EXTERNAL_PWD
1465 , x_email_address => l_ldap_message.mail
1466 , x_fax => l_ldap_message.facsimileTelephoneNumber
1467 , x_user_guid=>p_user_guid
1468 , x_change_source => fnd_user_pkg.change_source_oid
1469 );
1470
1471 -- Bug 4880490 New users should have the local login profile set to SSO
1472 l_found := fnd_profile.save(x_name => 'APPS_SSO_LOCAL_LOGIN'
1473 , x_value => 'SSO'
1474 , x_level_name => 'USER'
1475 , x_level_value => l_user_id);
1476 if not l_found then
1477 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1478 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
1479 'Unable to set APPS_SSO_LOCAL_LOGIN profile value to SSO for user ' || p_user_name);
1480 end if;
1481 end if;
1482
1483 end if;
1484
1485
1486 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1487 then
1488 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1489 'After calling CreateUser or UpdateUser username: '||p_user_name||' GUID: '||p_user_guid);
1490 end if;
1491
1492 -- send_subscription_add_to_OID(p_orcl_guid=>p_user_guid);
1493 add_user_to_OID_sub_list(p_orclguid => p_user_guid, x_result => l_sub_add_result);
1494
1495
1496 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1497 then
1498 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'After calling send_subscription_add_to_OID '||
1499 'to send the subscription to OID');
1500 end if;
1501 wf_event.AddParameterToList('ORCLGUID', p_user_guid, l_parmeter_list);
1502 wf_event.AddParameterToList('USER_NAME', p_user_name, l_parmeter_list);
1503 wf_event.raise(l_event_name, p_user_name, null, l_parmeter_list);
1504
1505 -- Create a subscription that will add the preferences responsiblity
1506 -- See fnd_oid_subscriptions.assign_default_resp
1507
1508 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1509 then
1510 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1511 end if;
1512
1513 exception
1514 when user_guid_null_exp then
1515 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1516 then
1517 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source
1518 , 'Cannot call on_demand_user_create will null GUID');
1519 end if;
1520 if (cur_fnd_users%isopen)
1521 then
1522 close cur_fnd_users;
1523 end if;
1524 raise;
1525
1526 when user_name_null_exp then
1527 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1528 then
1529 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source
1530 , 'Cannot call on_demand_user_create will null username');
1531 end if;
1532 if (cur_fnd_users%isopen)
1533 then
1534 close cur_fnd_users;
1535 end if;
1536 raise;
1537
1538 when others then
1539 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1540 then
1541 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1542 end if;
1543 if (cur_fnd_users%isopen)
1544 then
1545 close cur_fnd_users;
1546 end if;
1547
1548 raise;
1549 end on_demand_user_create;
1550 --
1551 -------------------------------------------------------------------------------
1552 procedure process_no_success_event(p_event_status in ldap_event_status) is
1553
1554 l_module_source varchar2(256);
1555 l_entity_key_value wf_entity_changes.entity_key_value%type;
1556 l_event_name varchar2(80);
1557 my_ent_type varchar2(50);
1558 my_parms wf_parameter_list_t;
1559
1560 begin
1561 l_module_source := G_MODULE_SOURCE || 'process_no_success_event: ';
1562
1563 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1564 then
1565 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1566 end if;
1567
1568 if (p_event_status.error_disposition = wf_oid.EVENT_ERROR)
1569 then
1570 l_event_name := 'oracle.apps.fnd.oidsync.error';
1571
1572 elsif (p_event_status.error_disposition = wf_oid.EVENT_RESEND)
1573 then
1574 l_event_name := 'oracle.apps.fnd.oidsync.resend';
1575 end if;
1576 -- Get the fnd_user.user_name
1577 fnd_oid_util.get_entity_key_value(p_event_status.event_id
1578 , l_entity_key_value);
1579
1580 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1581 then
1582 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1583 'p_event_status.error_disposition = ' ||
1584 p_event_status.error_disposition ||
1585 ', l_entity_key_value = ' || l_entity_key_value ||
1586 ', l_event_name = ' || l_event_name);
1587 end if;
1588 --RDESPOTO, 09/02/2004, add ENTITY_ID parameter
1589 --similar to wf_entity_mgr.process_changes()
1590 if (fnd_log.LEVEL_EVENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1591 then
1592 fnd_log.string(fnd_log.LEVEL_EVENT, l_module_source, 'About to '
1593 || 'raise event ' || l_event_name || ' with the following '
1594 || 'parameters: CACHE_CHANGED=NO, CHANGE_SOURCE=' || G_OID ||
1595 ', CHANGE_TYPE=' || G_LOAD || ', ORCLGUID=' || p_event_status.orclguid ||
1596 ', USER_NAME=' || l_entity_key_value || ', ENTITY_ID=' || p_event_status.event_id);
1597 end if;
1598 my_ent_type := upper(p_event_status.error_disposition);
1599 wf_entity_mgr.put_attribute_value(my_ent_type, l_entity_key_value,
1600 'CACHE_CHANGED', 'NO');
1601 wf_event.AddParameterToList('CHANGE_SOURCE', G_OID, my_parms);
1602 wf_event.AddParameterToList('CHANGE_TYPE', G_LOAD, my_parms);
1603 wf_event.AddParameterToList('ORCLGUID', p_event_status.orclguid, my_parms);
1604 wf_event.AddParameterToList('USER_NAME', l_entity_key_value, my_parms);
1605 wf_event.AddParameterToList('ENTITY_ID', p_event_status.event_id, my_parms);
1606 wf_event.raise(l_event_name, upper(l_entity_key_value), null, my_parms);
1607
1608 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1609 then
1610 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1611 end if;
1612
1613 exception
1614 when others then
1615 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1616 then
1617 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1618 end if;
1619 raise;
1620 end process_no_success_event;
1621 --
1622 -------------------------------------------------------------------------------
1623 procedure save_to_cache(
1624 p_ldap_attr_list in ldap_attr_list
1625 , p_entity_type in varchar2
1626 , p_entity_key_value in varchar2
1627 ) is
1628
1629 l_module_source varchar2(256);
1630
1631 begin
1632 l_module_source := G_MODULE_SOURCE || 'save_to_cache: ';
1633
1634 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1635 then
1636 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1637 , 'Begin');
1638 end if;
1639
1640 if (p_ldap_attr_list is not null AND p_ldap_attr_list.count > 0)
1641 then
1642 for j in p_ldap_attr_list.first .. p_ldap_attr_list.last
1643 loop
1644
1645 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1646 then
1647 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1648 , 'p_ldap_attr_list(' || j || ') = ' ||
1649 get_ldap_attr_str(p_ldap_attr_list(j)));
1650 end if;
1651
1652 if ((upper(p_ldap_attr_list(j).attr_name) <> G_USERPASSWORD)
1653 -- AND (upper(p_ldap_attr_list(j).attr_name) <> G_ORCLISENABLED)
1654 AND (upper(p_ldap_attr_list(j).attr_name) <> G_OBJECTCLASS))
1655 then
1656 wf_entity_mgr.put_attribute_value(p_entity_type, p_entity_key_value,
1657 p_ldap_attr_list(j).attr_name, p_ldap_attr_list(j).attr_value);
1658 end if;
1659 end loop;
1660 end if;
1661
1662 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1663 then
1664 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1665 , 'End');
1666 end if;
1667
1668 exception
1669 when others then
1670 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1671 then
1672 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1673 end if;
1674
1675 raise;
1676 end save_to_cache;
1677 --
1678 -------------------------------------------------------------------------------
1679 procedure get_entity_key_value(
1680 p_event_id in wf_entity_changes.entity_id%type
1681 , p_entity_key_value out nocopy wf_entity_changes.entity_key_value%type
1682 ) is
1683
1684 cursor cur_entity_changes is
1685 select entity_key_value
1686 from wf_entity_changes
1687 where entity_id = p_event_id;
1688
1689 l_module_source varchar2(256);
1690
1691 begin
1692 l_module_source := G_MODULE_SOURCE || 'get_entity_key_value: ';
1693
1694 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1695 then
1696 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1697 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1698 , 'p_event_id = ' || p_event_id);
1699 end if;
1700
1701 open cur_entity_changes;
1702 fetch cur_entity_changes into p_entity_key_value;
1703 close cur_entity_changes;
1704
1705 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1706 then
1707 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1708 end if;
1709
1710 exception
1711 when no_data_found then
1712 if (cur_entity_changes%isopen)
1713 then
1714 close cur_entity_changes;
1715 end if;
1716 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1717 then
1718 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source
1719 , 'Could not find matching entity for entity_id ' || p_event_id);
1720 end if;
1721 raise;
1722
1723 when others then
1724 if (cur_entity_changes%isopen)
1725 then
1726 close cur_entity_changes;
1727 end if;
1728
1729 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1730 then
1731 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1732 end if;
1733 raise;
1734
1735 end get_entity_key_value;
1736 --
1737 -------------------------------------------------------------------------------
1738 function get_key return varchar2 is
1739
1740 l_module_source varchar2(256);
1741 my_ident varchar2(256);
1742 retval pls_integer;
1743 my_session dbms_ldap.session;
1744 my_results dbms_ldap.message;
1745 my_attrs dbms_ldap.string_collection;
1746 my_entry dbms_ldap.message;
1747 my_vals dbms_ldap.string_collection;
1748
1749 begin
1750 l_module_source := G_MODULE_SOURCE || 'get_key: ';
1751 retval := -1;
1752
1753 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1754 then
1755 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1756 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1757 , 'key_guid = ' || key_guid);
1758 end if;
1759
1760 if (key_guid is null) then
1761 -- go to OID and get it --
1762 my_session := get_oid_session();
1763 my_ident := fnd_preference.get('#INTERNAL', 'LDAP_SYNCH', 'USERNAME');
1764 validate_preference('USERNAME', my_ident);
1765 my_attrs(1) := 'orclguid';
1766
1767 /*************
1768 When available, get key from "orclODIPEncryptedAttrKey" . It will be
1769 an attribute in the profile. The profile DN is of the form
1770 "<AppGUID>_<OrgGuid>,cn=provisioning profiles,cn=changelog,cn=oracle
1771 internet directory". Instead of 8 byte key , we should then shoot for
1772 32 byte key.
1773 *************/
1774
1775 retval := dbms_ldap.search_s(my_session,
1776 my_ident,
1777 DBMS_LDAP.SCOPE_BASE,
1778 'objectclass=*',
1779 my_attrs,
1780 0, -- retrieve both types AND values
1781 my_results);
1782
1783 my_entry := dbms_ldap.first_entry(my_session, my_results);
1784 if (my_entry IS NOT NULL)
1785 then
1786 my_vals := dbms_ldap.get_values(my_session, my_entry, 'orclguid');
1787
1788 if (my_vals.COUNT > 0)
1789 then
1790 key_guid := substr(my_vals(my_vals.FIRST),1,8);
1791 else
1792 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1793 then
1794 fnd_log.string(FND_LOG.LEVEL_STATEMENT, 'get_key',
1795 'orclguid attribute not found');
1796 end if;
1797 key_guid := null;
1798 end if;
1799
1800 else
1801 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1802 then
1803 fnd_log.string(FND_LOG.LEVEL_STATEMENT, 'get_key',
1804 'Application Identity '||my_ident||' not found');
1805 end if;
1806 key_guid := null;
1807 end if;
1808
1809 wf_oid.unbind(my_session);
1810 end if;
1811
1812 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1813 then
1814 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1815 end if;
1816
1817 return key_guid;
1818
1819 exception
1820 when others then
1821 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1822 then
1823 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1824 end if;
1825 raise;
1826 return null;
1827 end get_key;
1828 --
1829 -------------------------------------------------------------------------------
1830 function get_oid_session
1831 return dbms_ldap.session is
1832
1833 l_module_source varchar2(256);
1834 l_retval pls_integer;
1835 l_host varchar2(256);
1836 l_port varchar2(256);
1837 l_user varchar2(256);
1838 l_pwd varchar2(256);
1839 l_ldap_auth varchar2(256);
1840 l_db_wlt_url varchar2(256);
1841 l_db_wlt_pwd varchar2(256);
1842 l_session dbms_ldap.session;
1843
1844 begin
1845 l_module_source := G_MODULE_SOURCE || 'get_oid_session: ';
1846 -- change it to FAILURE if open_ssl fails, else let the simple_bind_s
1847 -- go through
1848 l_retval := dbms_ldap.SUCCESS;
1849
1850 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1851 then
1852 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1853 end if;
1854
1855 dbms_ldap.use_exception := TRUE;
1856
1857 l_host := fnd_preference.get(fnd_ldap_util.G_INTERNAL, fnd_ldap_util.G_LDAP_SYNCH, fnd_ldap_util.G_HOST);
1858 l_port := fnd_preference.get(fnd_ldap_util.G_INTERNAL, fnd_ldap_util.G_LDAP_SYNCH, fnd_ldap_util.G_PORT);
1859 l_user := fnd_preference.get(fnd_ldap_util.G_INTERNAL, fnd_ldap_util.G_LDAP_SYNCH, fnd_ldap_util.G_USERNAME);
1860 l_pwd := fnd_preference.eget(fnd_ldap_util.G_INTERNAL, fnd_ldap_util.G_LDAP_SYNCH, fnd_ldap_util.G_EPWD, fnd_ldap_util.G_LDAP_PWD);
1861 l_ldap_auth := fnd_preference.get(fnd_ldap_util.G_INTERNAL, fnd_ldap_util.G_LDAP_SYNCH, fnd_ldap_util.G_DBLDAPAUTHLEVEL);
1862 l_db_wlt_url := fnd_preference.get(fnd_ldap_util.G_INTERNAL, fnd_ldap_util.G_LDAP_SYNCH, fnd_ldap_util.G_DBWALLETDIR);
1863 l_db_wlt_pwd := fnd_preference.eget(fnd_ldap_util.G_INTERNAL, fnd_ldap_util.G_LDAP_SYNCH, fnd_ldap_util.G_DBWALLETPASS, fnd_ldap_util.G_LDAP_PWD);
1864
1865 --Fix bug 4233320, raise both exception and alert when preferences are missing
1866 validate_OID_preferences (l_host, l_port, l_user, l_pwd);
1867
1868 l_session := DBMS_LDAP.init(l_host, l_port);
1869
1870 -- Elan, 04/27/2004, Not disclosing the password - gets saved to the database
1871 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1872 then
1873 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1874 , 'l_host = ' || l_host || ', l_port = ' || l_port ||
1875 ', l_ldap_auth = ' || l_ldap_auth || ', l_db_wlt_url = ' ||
1876 l_db_wlt_url ||
1877 ', l_user = ' || l_user || ', l_pwd = ****');
1878 end if;
1879
1880 if ( l_ldap_auth > 0 )
1881 then
1882 l_retval := dbms_ldap.open_ssl
1883 (l_session, 'file:'||l_db_wlt_url, l_db_wlt_pwd, l_ldap_auth);
1884 end if;
1885
1886 if (l_retval = dbms_ldap.SUCCESS) then
1887 l_retval := dbms_ldap.simple_bind_s(l_session, l_user, l_pwd);
1888 else
1889 fnd_message.set_name ('FND', 'FND_SSO_SSL_ERROR');
1890 raise_application_error(-20002, 'FND_SSO_SSL_ERROR');
1891 end if;
1892
1893 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1894 then
1895 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1896 end if;
1897
1898 return l_session;
1899
1900 exception
1901 when dbms_ldap.invalid_session then
1902 fnd_message.set_name ('FND', 'FND_SSO_INV_SESSION');
1903 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1904 then
1905 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1906 end if;
1907 raise;
1908 when dbms_ldap.invalid_ssl_wallet_loc then
1909 fnd_message.set_name ('FND', 'FND_SSO_WALLET_LOC');
1910 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1911 then
1912 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1913 end if;
1914 raise;
1915 when dbms_ldap.invalid_ssl_wallet_passwd then
1916 fnd_message.set_name ('FND', 'FND_SSO_WALLET_PWD');
1917 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1918 then
1919 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1920 end if;
1921 raise;
1922 when dbms_ldap.invalid_ssl_auth_mode then
1923 fnd_message.set_name ('FND', 'FND_SSO_INV_AUTH_MODE');
1924 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1925 then
1926 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1927 end if;
1928 raise;
1929 when others then
1930 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1931 then
1932 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1933 end if;
1934 raise;
1935 end;
1936 --
1937 -------------------------------------------------------------------------------
1938 function get_entity_changes_rec_str(
1939 p_entity_changes_rec in wf_entity_changes_rec_type)
1940 return varchar2 is
1941
1942 l_module_source varchar2(256);
1943 l_str varchar2(4000);
1944
1945 begin
1946 l_module_source := G_MODULE_SOURCE || 'get_entity_changes_rec_str: ';
1947
1948 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1949 then
1950 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1951 end if;
1952
1953 l_str := 'entity_type: ' || p_entity_changes_rec.entity_type ||
1954 ', entity_key_value: ' || p_entity_changes_rec.entity_key_value ||
1955 ', flavor: ' || p_entity_changes_rec.flavor ||
1956 ', change_date: ' || p_entity_changes_rec.change_date ||
1957 ', entity_id: ' || p_entity_changes_rec.entity_id ||
1958 ', change_date_in_char: ' || p_entity_changes_rec.change_date_in_char;
1959
1960 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1961 then
1962 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1963 end if;
1964
1965 return (l_str);
1966
1967 exception
1968 when others then
1969 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1970 then
1971 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1972 end if;
1973 raise;
1974
1975 end get_entity_changes_rec_str;
1976 --
1977 -------------------------------------------------------------------------------
1978 function get_oid_nickname(p_user_guid in fnd_user.user_guid%type)
1979 return varchar2 is
1980
1981 l_module_source varchar2(256);
1982 result pls_integer;
1983 l_message dbms_ldap.message := null;
1984 l_entry dbms_ldap.message := null;
1985 l_attrs dbms_ldap.string_collection;
1986 subsNode varchar2(1000);
1987 l_nickname_attr varchar2(256);
1988 l_nickname_value varchar2(2000);
1989 ldapSession dbms_ldap.session;
1990
1991 begin
1992 l_module_source := G_MODULE_SOURCE || 'get_oid_nickname: ';
1993 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1994 then
1995 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1996 end if;
1997 l_nickname_value := FND_LDAP_USER.get_username_from_guid(p_user_guid);
1998
1999 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2000 then
2001 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
2002 end if;
2003 return l_nickname_value;
2004
2005 exception
2006 when others then
2007 l_nickname_value := '';
2008 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2009 then
2010 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Error occured: '
2011 || sqlcode || ', ' || sqlerrm);
2012 end if;
2013 return l_nickname_value;
2014 end get_oid_nickname;
2015
2016 --
2017 -------------------------------------------------------------------------------
2018 function person_party_exists(p_user_name in varchar2)
2019 return boolean is
2020
2021 l_module_source varchar2(256);
2022 l_retval boolean;
2023 l_apps_user_key apps_user_key_type;
2024
2025 begin
2026 l_module_source := G_MODULE_SOURCE || 'person_party_exists: ';
2027 l_retval := false;
2028
2029 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2030 then
2031 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
2032 end if;
2033
2034 l_apps_user_key := get_fnd_user(p_user_name => p_user_name);
2035
2036 if (l_apps_user_key.person_party_id is not null)
2037 then
2038 l_retval := true;
2039 end if;
2040
2041 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2042 then
2043 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
2044 end if;
2045
2046 return (l_retval);
2047
2048 exception
2049 when others then
2050 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2051 then
2052 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
2053 end if;
2054 return (l_retval);
2055 end person_party_exists;
2056 --
2057 -------------------------------------------------------------------------------
2058 procedure set_ldap_message_attr is
2059
2060 l_module_source varchar2(256);
2061
2062 begin
2063 l_module_source := G_MODULE_SOURCE || 'set_ldap_message_attr: ';
2064
2065 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2066 then
2067 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
2068 end if;
2069
2070 G_LDAP_MESSAGE_ATTR.object_name := 'OBJECT_NAME';
2071 G_LDAP_MESSAGE_ATTR.cn := 'CN';
2072 G_LDAP_MESSAGE_ATTR.sn := 'SN';
2073 G_LDAP_MESSAGE_ATTR.userPassword := 'USERPASSWORD';
2074 G_LDAP_MESSAGE_ATTR.telephoneNumber := 'TELEPHONENUMBER';
2075 G_LDAP_MESSAGE_ATTR.street := 'STREET';
2076 G_LDAP_MESSAGE_ATTR.postalCode := 'POSTALCODE';
2077 G_LDAP_MESSAGE_ATTR.physicalDeliveryOfficeName :=
2078 'PHYSICALDELIVERYOFFICENAME';
2079 G_LDAP_MESSAGE_ATTR.st := 'ST';
2080 G_LDAP_MESSAGE_ATTR.l := 'L';
2081 G_LDAP_MESSAGE_ATTR.displayName := 'DISPLAYNAME';
2082 G_LDAP_MESSAGE_ATTR.givenName := 'GIVENNAME';
2083 G_LDAP_MESSAGE_ATTR.homePhone := 'HOMEPHONE';
2084 G_LDAP_MESSAGE_ATTR.mail := 'MAIL';
2085 G_LDAP_MESSAGE_ATTR.c := 'C';
2086 G_LDAP_MESSAGE_ATTR.facsimileTelephoneNumber := 'FACSIMILETELEPHONENUMBER';
2087 G_LDAP_MESSAGE_ATTR.description := 'DESCRIPTION';
2088 G_LDAP_MESSAGE_ATTR.orclisEnabled := 'ORCLISENABLED';
2089 G_LDAP_MESSAGE_ATTR.orclActiveStartDate := 'ORCLACTIVESTARTDATE';
2090 G_LDAP_MESSAGE_ATTR.orclActiveEndDate := 'ORCLACTIVEENDDATE';
2091 G_LDAP_MESSAGE_ATTR.orclGUID := 'ORCLGUID';
2092
2093 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2094 then
2095 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
2096 end if;
2097
2098 exception
2099 when others then
2100 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2101 then
2102 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
2103 end if;
2104 end set_ldap_message_attr;
2105 --
2106 -------------------------------------------------------------------------------
2107 function is_guid_already_subscribed(p_orclguid in
2108 fnd_user.user_guid%type,ldapSession in dbms_ldap.session) return BOOLEAN
2109 is
2110 l_module_source varchar(256);
2111 l_attrs dbms_ldap.string_collection;
2112 subsNode varchar2(1000);
2113 l_message1 dbms_ldap.message := null;
2114 l_message2 dbms_ldap.message := null;
2115 usersDN varchar2(1000);
2116 l_result pls_integer;
2117
2118 l_result1 BOOLEAN := FALSE;
2119 l_result2 BOOLEAN := FALSE;
2120
2121 begin
2122 l_module_source := G_MODULE_SOURCE || 'is_guid_already_subscribed' ;
2123
2124 subsNode := 'cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,' || fnd_ldap_util.get_orclappname;
2125
2126 --Search the ldap with orclOwnerGUID attribute filter
2127 l_result := dbms_ldap.search_s(ld => ldapSession, base => subsNode,
2128 scope => dbms_ldap.SCOPE_SUBTREE, filter =>
2129 'orclOwnerGUID=' || p_orclguid, attrs => l_attrs, attronly => 0,
2130 res => l_message1);
2131
2132 IF DBMS_LDAP.count_entries(ld => ldapSession, msg => l_message1) > 0 THEN
2133 l_result1 := TRUE;
2134 if(fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2135 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'orclOwnerGUID present');
2136 end if;
2137 ELSE
2138 if(fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2139 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'orclOwnerGUID not present');
2140 end if;
2141 END IF;
2142
2143 --Search the ldap with uniquemember filter
2144 usersDN := fnd_ldap_util.get_dn_for_guid(p_orclguid => p_orclguid);
2145 l_result := dbms_ldap.search_s(ld => ldapSession, base => subsNode,
2146 scope => dbms_ldap.SCOPE_SUBTREE, filter =>
2147 'uniquemember=' || usersDN, attrs => l_attrs,attronly => 0,
2148 res => l_message2);
2149
2150 IF DBMS_LDAP.count_entries(ld => ldapSession, msg => l_message2) > 0 THEN
2151 l_result2 := TRUE;
2152 if(fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2153 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'uniquemember present');
2154 end if;
2155 ELSE
2156 if(fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2157 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'uniquemember not present');
2158 end if;
2159 END IF;
2160
2161 --If both results have some values means - user subscribed
2162 --If both results are null means - user NOT subscribed
2163 --If only one of the results has a value - Data corrupted - throw exception
2164
2165 if( l_result1 and l_result2 ) then
2166 return TRUE;
2167 elsif( (NOT l_result1) and (NOT l_result2) ) then
2168 return FALSE;
2169 else
2170 raise user_subs_data_corrupt_exp;
2171 end if;
2172 exception
2173 when user_subs_data_corrupt_exp then
2174 if(fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2175 then
2176 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'Subscription data is corrupt');
2177 end if;
2178 raise;
2179 when others then
2180 if(fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2181 then
2182 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
2183 end if;
2184 return FALSE;
2185 end;
2186
2187
2188 procedure add_user_to_OID_sub_list(p_orclguid in fnd_user.user_guid%type, x_result out nocopy pls_integer) is
2189
2190 l_module_source varchar2(256);
2191 usersDN varchar2(1000);
2192 subsNode varchar2(1000);
2193 l_registration pls_integer;
2194 result pls_integer;
2195 retval pls_integer;
2196 ldapSession dbms_ldap.session;
2197 modArray dbms_ldap.mod_array;
2198 modmultivalues dbms_ldap.string_collection;
2199 guid_subscribed BOOLEAN;
2200 err varchar2(1000);
2201
2202 begin
2203 l_module_source := G_MODULE_SOURCE || 'add_user_to_OID_sub_list ';
2204
2205 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2206 then
2207 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
2208 , 'Begin');
2209 end if;
2210
2211 fnd_ldap_wrapper.get_registration(x_registration => l_registration);
2212
2213 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2214 then
2215 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
2216 , 'Registration :: '||l_registration);
2217 end if;
2218
2219 if (l_registration = FND_LDAP_WRAPPER.G_VALID_REGISTRATION)
2220 then
2221 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2222 then
2223 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
2224 ,'Valid registration');
2225 end if;
2226
2227 ldapSession := fnd_ldap_util.get_oid_session;
2228 --check if the entry exists in ldap for a particular GUID.If NOT then create the entry.
2229 guid_subscribed := is_guid_already_subscribed(p_orclguid, ldapSession);
2230
2231 if guid_subscribed
2232 then
2233 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2234 then
2235 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'User already subscribed.');
2236 end if;
2237 x_result := fnd_ldap_util.G_SUCCESS;
2238 return;
2239 else
2240 subsNode := 'cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,' || fnd_ldap_util.get_orclappname;
2241
2242 modArray := dbms_ldap.create_mod_array(num => 1);
2243
2244 modmultivalues(0) := 'orclServiceSubscriptionDetail';
2245 dbms_ldap.populate_mod_array(modptr => modArray, mod_op => dbms_ldap.mod_add,
2246 mod_type => 'objectclass', modval => modmultivalues);
2247 subsNode := 'orclOwnerGUID=' || p_orclguid || ',' || subsNode;
2248 retval := dbms_ldap.add_s(ld => ldapSession, entrydn => subsNode, modptr => modArray);
2249 end if;
2250
2251 if (retval = dbms_ldap.SUCCESS)
2252 then
2253 usersDN := fnd_ldap_util.get_dn_for_guid(p_orclguid => p_orclguid);
2254 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2255 then
2256 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
2257 ,'Adding unique member :: '||usersDN);
2258 end if;
2259 modArray := dbms_ldap.create_mod_array(num => 1);
2260 modmultivalues(0) := usersDN;
2261 dbms_ldap.populate_mod_array(modptr => modArray,
2262 mod_op => dbms_ldap.mod_add,
2263 mod_type => 'uniquemember',
2264 modval => modmultivalues);
2265 subsNode := 'cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,' || fnd_ldap_util.get_orclappname;
2266 retval := dbms_ldap.modify_s(ld => ldapSession, entrydn => subsNode, modptr => modArray);
2267 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2268 then
2269 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
2270 ,'Completed modify');
2271 end if;
2272
2273 if (retval = dbms_ldap.SUCCESS)
2274 then
2275 retval := fnd_ldap_util.G_SUCCESS;
2276 else
2277 retval := fnd_ldap_util.G_FAILURE;
2278 end if;
2279 else
2280 retval := fnd_ldap_util.G_FAILURE;
2281 end if;
2282
2283 dbms_ldap.free_mod_array(modptr => modArray);
2284 result := fnd_ldap_util.unbind(ldapSession);
2285
2286 else
2287 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2288 then
2289 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
2290 ,'No registration or invalid registration');
2291 end if;
2292 retval := fnd_ldap_util.G_FAILURE;
2293 end if;
2294
2295 x_result := retval;
2296 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2297 then
2298 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
2299 , 'End');
2300 end if;
2301 exception
2302 when others
2303 then
2304 err := sqlerrm;
2305 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2306 then
2307 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, err);
2308 end if;
2309
2310 if (instr(err,'Already exists. Object already exists')>1)
2311 then
2312 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2313 then
2314 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
2315 'User already subscribed');
2316 end if;
2317 x_result := fnd_ldap_util.G_SUCCESS;
2318 else
2319 raise;
2320 end if;
2321
2322 end add_user_to_OID_sub_list;
2323 --
2324 -------------------------------------------------------------------------------
2325 procedure send_subscription_add_to_OID
2326 (p_orcl_guid fnd_user.user_guid%type)
2327 is
2328 l_module_source varchar2(256);
2329 l_apps_user_key apps_user_key_type;
2330 l_user_name fnd_user.user_name%type;
2331 my_parms wf_parameter_list_t;
2332 begin
2333 l_module_source := G_MODULE_SOURCE || 'send_subscription_add_to_OID: ';
2334
2335 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2336 then
2337 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
2338 , 'Begin');
2339 end if;
2340 -- Cache attributes are queried in wf_oid.GetAppEvent based on FND user_name
2341 l_apps_user_key:= get_fnd_user(p_user_guid => p_orcl_guid);
2342 l_user_name := l_apps_user_key.user_name;
2343 if (l_user_name is null) then
2344 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2345 then
2346 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
2347 , 'Cannot send SUBSCRIPTION_ADD to OID because user ' ||
2348 'does not exist in FND_USER');
2349 end if;
2350 return;
2351 end if;
2352 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2353 then
2354 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
2355 , 'User name for SUBSCRIPTION_ADD is ' || l_user_name);
2356 end if;
2357 -- Insert guid only so that SUBSCRIPTION_ADD sends guid to OID
2358 wf_entity_mgr.put_attribute_value(wf_oid.SUBSCRIPTION_ADD, l_user_name,
2359 G_CACHE_CHANGED, G_YES);
2360 wf_entity_mgr.put_attribute_value(wf_oid.SUBSCRIPTION_ADD, l_user_name,
2361 G_ORCLGUID, p_orcl_guid);
2362 insert into wf_entity_changes(
2363 entity_type, entity_key_value, flavor, change_date)
2364 values(wf_oid.SUBSCRIPTION_ADD, upper(l_user_name), 'FND', sysdate);
2365 wf_entity_mgr.put_attribute_value(upper(wf_oid.SUBSCRIPTION_ADD), l_user_name,
2366 'CACHE_CHANGED', 'NO');
2367 commit;
2368
2369 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2370 then
2371 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
2372 , 'End');
2373 end if;
2374
2375 exception
2376 when others then
2377 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2378 then
2379 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
2380 end if;
2381
2382 raise;
2383 end send_subscription_add_to_OID;
2384 --
2385 -------------------------------------------------------------------------------
2386 procedure validate_OID_preferences (
2387 my_host varchar2,
2388 my_port varchar2,
2389 my_user varchar2,
2390 my_pwd varchar2
2391 )
2392 is
2393 partial_registration exception;
2394 l_module_source varchar2(256);
2395 begin
2396 l_module_source := G_MODULE_SOURCE || 'validate_OID_preferences: ';
2397
2398 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2399 then
2400 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
2401 end if;
2402 validate_preference('HOST', my_host);
2403 validate_preference('PORT', my_port);
2404 validate_preference('USERNAME', my_user);
2405 validate_preference('EPWD', my_pwd);
2406 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2407 then
2408 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
2409 end if;
2410 end;
2411 ---
2412 ---------------------------------------------------------------------
2413
2414 function isTCAEnabled (p_action in varchar2) return boolean IS
2415
2416 l_module_source varchar2(256);
2417 l_status varchar2(10);
2418
2419 begin
2420
2421 l_module_source := G_MODULE_SOURCE || 'isTCAEnabled: ';
2422
2423 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2424 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,'Begin');
2425 end if;
2426
2427
2428 if (p_action = 'ADD') then
2429 select status into l_status from wf_event_subscriptions
2430 where rule_function = 'fnd_oid_subscriptions.hz_identity_add';
2431 elsif (p_action = 'MODIFY') then
2432 select status into l_status from wf_event_subscriptions
2433 where rule_function = 'fnd_oid_subscriptions.hz_identity_modify';
2434 elsif (p_action = 'DELETE') then
2435 select status into l_status from wf_event_subscriptions
2436 where rule_function = 'fnd_oid_subscriptions.hz_identity_delete';
2437 else
2438 -- Invalid action return true by default
2439 l_status := 'ENABLED';
2440 end if;
2441
2442 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2443 then
2444 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'For action:
2445 '||p_action||' status is: '||l_status );
2446 end if;
2447
2448
2449 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2450 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,'END');
2451 end if;
2452
2453 if (l_status = 'ENABLED') then
2454 return TRUE;
2455 else
2456 return FALSE;
2457 end if;
2458
2459
2460 exception
2461 when no_data_found then
2462 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2463 then
2464 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'Subscription does
2465 not exist for '||p_action);
2466 end if;
2467
2468 return false;
2469 when others then
2470 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2471 then
2472 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
2473 end if;
2474
2475 return false;
2476
2477 end;
2478 ----------------------------------------------------------------------------------
2479
2480 procedure validate_preference (
2481 my_preference_name varchar2,
2482 my_preference_value varchar2
2483 )
2484 is
2485 l_module_source varchar2(256);
2486 begin
2487 l_module_source := G_MODULE_SOURCE || 'validate_preference: ';
2488 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2489 then
2490 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
2491 end if;
2492 if my_preference_value is null then
2493 if(fnd_log.LEVEL_UNEXPECTED >=
2494 fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2495 fnd_message.SET_NAME('FND', 'FND_SSO_PARTIAL_PREFERENCES');
2496 fnd_message.SET_TOKEN('PARAMETER', my_preference_name);
2497 fnd_log.MESSAGE(fnd_log.LEVEL_UNEXPECTED, l_module_source, TRUE);
2498 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,
2499 my_preference_name || ' parameter is missing in preferences table.');
2500 end if;
2501 raise_application_error(-20100, my_preference_name || ' parameter is missing'
2502 || ' in the E-Business preferences table. Please re-register your application' ||
2503 ' with Oracle Internet Directory to populate missing parameters."');
2504 end if;
2505 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2506 then
2507 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
2508 end if;
2509 end;
2510
2511 begin
2512 set_ldap_message_attr;
2513 end fnd_oid_util;