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