[Home] [Help]
PACKAGE BODY: APPS.WF_OID
Source
1 package body WF_OID as
2 /* $Header: WFOIDB.pls 120.3 2006/08/09 14:18:40 rsantis noship $ */
3 --
4 -- Start of Package Globals
5 --
6 G_MODULE_SOURCE constant varchar2(80) := 'fnd.plsql.oid.wf_oid.';
7 pending_sub_add_event LDAP_EVENT := NULL;
8 key_guid varchar2(8):= NULL;
9 --
10 -- End of Package Globals
11 --
12 -------------------------------------------------------------------------------
13 FUNCTION get_oid_session return dbms_ldap.session is
14
15 retval pls_integer;
16 my_host varchar2(256);
17 my_port varchar2(256);
18 my_user varchar2(256);
19 my_pwd varchar2(256);
20 my_session dbms_ldap.session;
21
22 begin
23 retval := -1;
24
25 dbms_ldap.use_exception := TRUE;
26
27 my_host := fnd_preference.get('#INTERNAL', 'LDAP_SYNCH', 'HOST');
28 my_port := fnd_preference.get('#INTERNAL', 'LDAP_SYNCH', 'PORT');
29 my_user := fnd_preference.get('#INTERNAL', 'LDAP_SYNCH', 'USERNAME');
30 my_pwd := fnd_preference.eget('#INTERNAL','LDAP_SYNCH', 'EPWD', 'LDAP_PWD');
31 my_session := DBMS_LDAP.init(my_host, my_port);
32
33 retval := dbms_ldap.simple_bind_s(my_session, my_user, my_pwd);
34
35 return my_session;
36
37 exception
38 when others then
39 wf_core.context('WF_OID', 'get_oid_session',
40 'Error code: '||to_char(sqlcode),
41 'Error Message: '||substr(sqlerrm, 1, 238));
42 wf_core.raise('ICX_PREF_DESC');
43 end;
44 --
45 -------------------------------------------------------------------------------
46 PROCEDURE unbind(p_session in out nocopy dbms_ldap.session)
47 is
48 retval pls_integer;
49 begin
50 retval := -1;
51 retval := DBMS_LDAP.unbind_s(p_session);
52 exception
53 when others then null;
54 end;
55 --
56 -------------------------------------------------------------------------------
57 procedure PutOIDEvent(
58 event in ldap_event
59 , event_status out nocopy ldap_event_status) is
60
61 l_module_source varchar2(256);
62 l_null_event_exp exception;
63 l_user_profiles fnd_oid_util.apps_sso_user_profiles_type;
64
65 begin
66 l_module_source := G_MODULE_SOURCE || 'PutOIDEvent: ';
67 fnd_global.apps_initialize(0, -1, -1);
68
69 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
70 then
71 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
72 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
73 , 'event = ' || fnd_oid_util.GET_LDAP_EVENT_STR(event));
74 end if;
75
76 if (event is null)
77 then
78 raise l_null_event_exp;
79 end if;
80
81 if (event.event_type = wf_oid.IDENTITY_ADD)
82 then
83 fnd_oid_util.process_identity_add(event);
84
85 elsif (event.event_type = wf_oid.IDENTITY_MODIFY)
86 then
87 fnd_oid_util.process_identity_modify(event);
88
89 elsif (event.event_type = wf_oid.IDENTITY_DELETE)
90 then
91 fnd_oid_util.process_identity_delete(event);
92
93 elsif (event.event_type = wf_oid.SUBSCRIPTION_ADD)
94 then
95 fnd_oid_util.process_subscription_add(event);
96
97 elsif (event.event_type = wf_oid.SUBSCRIPTION_DELETE)
98 then
99 fnd_oid_util.process_subscription_delete(event);
100
101 else
102 --fnd_oid_util.process_event_resend(event_status);
103 wf_entity_mgr.put_attribute_value(fnd_oid_util.G_USER, event.object_name,
104 fnd_oid_util.G_ORCLGUID, event.object_guid);
105
106 fnd_oid_util.SAVE_TO_CACHE(
107 p_ldap_attr_list => event.attr_list
108 , p_entity_type => fnd_oid_util.G_USER
109 , p_entity_key_value => event.object_name);
110
111 wf_entity_mgr.process_changes(fnd_oid_util.G_USER, event.object_name,
112 fnd_oid_util.G_OID);
113
114 end if;
115
116 event_status := ldap_event_status(event.event_id, null,
117 0, null, wf_oid.EVENT_SUCCESS);
118
119 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
120 then
121 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
122 end if;
123
124 exception
125 when l_null_event_exp then
126 event_status := LDAP_EVENT_STATUS(null, null, 0,
127 'Received Null Event', wf_oid.EVENT_RESEND);
128 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
129 then
130 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source,
131 'Receiced Null Event. Sending wf_oid.EVENT_RESEND');
132 end if;
133
134 when others then
135 event_status := LDAP_EVENT_STATUS(event.event_id, null, to_char(sqlcode),
136 sqlerrm, wf_oid.EVENT_ERROR);
137 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
138 then
139 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, sqlerrm);
140 end if;
141 end PutOIDEvent;
142 --
143 -------------------------------------------------------------------------------
144 function GetAppEvent(event out nocopy ldap_event)
145 return number is
146
147 l_module_source varchar2(256);
148 l_entity_changes_rec fnd_oid_util.wf_entity_changes_rec_type;
149 l_ldap_key fnd_oid_util.ldap_key_type;
150 l_ldap_attr_list ldap_attr_list;
151 l_entity_id number;
152 my_temp_id number; -- for extra sub_add event --
153 l_local_login varchar2(30);
154 l_allow_sync varchar2(1);
155 l_profile_defined boolean;
156 l_copy_event ldap_event;
157 l_apps_username_key fnd_oid_util.apps_user_key_type;
158
159 begin
160 l_module_source := G_MODULE_SOURCE || 'GetAppEvent: ';
161 fnd_global.apps_initialize(0, -1, -1);
162
163 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
164 then
165 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
166 end if;
167 fnd_ldap_mapper.map_entity_changes_rec(l_entity_changes_rec);
168 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
169 then
170 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
171 'l_entity_changes_rec = ' ||
172 fnd_oid_util.GET_ENTITY_CHANGES_REC_STR(l_entity_changes_rec));
173 end if;
174 l_apps_username_key := fnd_oid_util.get_fnd_user(p_user_name => l_entity_changes_rec.entity_key_value);
175 fnd_profile.get_specific(
176 name_z => 'APPS_SSO_LOCAL_LOGIN',
177 user_id_z => l_apps_username_key.user_id,
178 val_z => l_local_login,
179 defined_z => l_profile_defined);
180
181 if (not l_profile_defined or l_local_login = fnd_oid_util.G_LOCAL) then
182 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
183 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
184 'We don''t sync users with profile APPS_SSO_LOCAL_LOGIN=LOCAL: '
185 || l_entity_changes_rec.entity_key_value);
186 end if;
187 --Remove from wf_entity_changes to fix bug 4233358
188 delete from wf_entity_changes
189 where entity_key_value = l_entity_changes_rec.entity_key_value
190 and entity_type = l_entity_changes_rec.entity_type;
191 raise fnd_oid_util.event_not_found_exp;
192 end if;
193
194 fnd_ldap_mapper.map_ldap_attr_list(
195 l_entity_changes_rec.entity_type
196 , l_entity_changes_rec.entity_key_value
197 , l_ldap_key
198 , l_ldap_attr_list);
199 l_copy_event := event;
200
201 fnd_ldap_mapper.map_oid_event(l_ldap_key, l_entity_changes_rec
202 , l_ldap_attr_list, event);
203
204 if (event.event_type = wf_oid.IDENTITY_MODIFY) then
205 --APPS_SSO_LDAP_SYNC profile is considered for MODIFY events only
206 fnd_profile.get_specific(
207 name_z => 'APPS_SSO_LDAP_SYNC',
208 user_id_z => l_apps_username_key.user_id,
209 val_z => l_allow_sync,
210 defined_z => l_profile_defined);
211 if (not l_profile_defined or l_allow_sync = fnd_oid_util.G_N) then
212 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
213 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
214 'We don''t modify users with profile APPS_SSO_LDAP_SYNC=N: '
215 || l_entity_changes_rec.entity_key_value);
216 end if;
217 event := l_copy_event;
218 delete from wf_entity_changes
219 where entity_key_value = l_entity_changes_rec.entity_key_value
220 and entity_type = l_entity_changes_rec.entity_type;
221 raise fnd_oid_util.event_not_found_exp;
222 end if;
223 end if;
224
225 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
226 then
227 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
228 , fnd_oid_util.get_ldap_event_str(event));
229 end if;
230
231 l_ldap_attr_list.delete;
232
233 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
234 then
235 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
236 end if;
237
238 return wf_oid.EVENT_FOUND;
239
240 exception
241 when fnd_oid_util.event_not_found_exp then
242 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
243 then
244 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
245 , 'No more events to send.Sending wf_oid.EVENT_NOT_FOUND');
246 end if;
247
248 return wf_oid.EVENT_NOT_FOUND;
249
250 when others then
251 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
252 then
253 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, sqlerrm);
254 end if;
255
256 return wf_oid.EVENT_NOT_FOUND;
257 end GetAppEvent;
258 --
259 -------------------------------------------------------------------------------
260 procedure PutAppEventStatus(event_status in ldap_event_status) is
261
262 l_module_source varchar2(256);
263 l_login_profile varchar2(30);
264 l_profile_defined boolean;
265 l_profiles fnd_oid_util.apps_sso_user_profiles_type;
266 l_entity_key_value wf_entity_changes.entity_key_value%type;
267 null_event_status_exp exception;
268
269 begin
270 l_module_source := G_MODULE_SOURCE || 'PutAppEventStatus: ';
271
272 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
273 then
274 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
275 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
276 then
277 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
278 , 'event_status = ' || fnd_oid_util.get_ldap_event_status_str(event_status));
279 end if;
280 end if;
281
282 if (event_status is null)
283 then
284 raise null_event_status_exp;
285 end if;
286
287 fnd_oid_util.get_entity_key_value(event_status.event_id, l_entity_key_value);
288
289 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
290 then
291 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
292 'l_entity_key_value = ' || l_entity_key_value);
293 end if;
294
295
296 if (event_status.error_disposition = wf_oid.EVENT_SUCCESS)
297 then
298 -- OID sometimes sends dummy event with user_name=guid
299 if l_entity_key_value <> '' || event_status.orclguid then
300 update fnd_user
301 set user_guid = event_status.orclguid
302 where user_name = l_entity_key_value;
303 else
304 return;
305 end if;
306 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
307 then
308 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
309 , 'Update FND_USER where user_name = ' || l_entity_key_value ||
310 ' with user_guid = ' || event_status.orclguid);
311 end if;
312
313 l_profiles := fnd_ldap_mapper.MAP_SSO_USER_PROFILES(l_entity_key_value);
314
315 if (l_profiles.local_login = 'SSO')
316 then
317 update fnd_user
318 set encrypted_user_password = fnd_oid_util.G_EXTERNAL
319 where user_name = l_entity_key_value;
320 end if;
321
322 delete from wf_entity_changes
323 where entity_id = event_status.event_id;
324
325 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
326 then
327 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
328 , 'Deleted WF_ENTITY_CHANGES where entity_id = ' ||
329 event_status.event_id);
330 end if;
331 else
332 -- RDESPOTO, 09/02/2004, if not success
333 fnd_oid_util.process_no_success_event(event_status);
334
335 end if;
336
337
338 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
339 then
340 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
341 end if;
342
343 exception
344 -- FIXME: Review the behavior with OID team
345 when null_event_status_exp then
346 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
347 then
348 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source,
349 'Received null_event_status_exp from OiD');
350 end if;
351 when no_data_found then
352 -- FIXME: Review the behavior with OID team
353 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
354 then
355 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source,
356 'No Data Found');
357 end if;
358 --RDESPOTO, 09/23/2004
359 if(fnd_log.LEVEL_UNEXPECTED >=
360 fnd_log.G_CURRENT_RUNTIME_LEVEL) then
361 fnd_message.SET_NAME('FND', 'FND_SSO_EVENT_ERROR');
362 fnd_message.SET_TOKEN('USER_NAME', l_entity_key_value);
363 fnd_message.SET_TOKEN('ENTITY_ID', event_status.event_id);
364 fnd_message.SET_TOKEN('ORCLGUID', event_status.orclguid);
365 fnd_log.MESSAGE(fnd_log.LEVEL_UNEXPECTED,
366 l_module_source, TRUE);
367 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,
368 'Synchronization of user definiton between E-Business Suite'||
369 ' and Oracle Internet Directory has failed for user:' || l_entity_key_value ||
370 ', event id:' || event_status.event_id || ', guid:' || event_status.orclguid);
371 end if;
372 when others then
373 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
374 then
375 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
376 end if;
377 --RDESPOTO, 09/23/2004
378 if(fnd_log.LEVEL_UNEXPECTED >=
379 fnd_log.G_CURRENT_RUNTIME_LEVEL) then
380 fnd_message.SET_NAME('FND', 'FND_SSO_EVENT_ERROR');
381 fnd_message.SET_TOKEN('USER_NAME', l_entity_key_value);
382 fnd_message.SET_TOKEN('ENTITY_ID', event_status.event_id);
383 fnd_message.SET_TOKEN('ORCLGUID', event_status.orclguid);
384 fnd_log.MESSAGE(fnd_log.LEVEL_UNEXPECTED,
385 l_module_source, TRUE);
386 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,
387 'Synchronization of user definiton between E-Business Suite'||
388 ' and Oracle Internet Directory has failed for user:' || l_entity_key_value ||
389 ', event id:' || event_status.event_id || ', guid:' || event_status.orclguid);
390 end if;
391 end PutAppEventStatus;
392 --
393 -------------------------------------------------------------------------------
394 FUNCTION user_change(p_subscription_guid in raw,
395 p_event in out nocopy wf_event_t)
396 return varchar2 is
397
398 begin
399 -- No point re-processing our own changes --
400 if (p_event.GetValueForParameter('CHANGE_SOURCE') = 'OID') then
401 return 'SUCCESS';
402 end if;
403
404 fnd_oid_util.entity_changes(p_event.getEventKey());
405 return wf_rule.default_rule(p_subscription_guid, p_event);
406 end;
407 --
408 -------------------------------------------------------------------------------
409 PROCEDURE future_callback(p_parameters in wf_parameter_list_t default null) is
410 begin
411 fnd_oid_util.entity_changes(
412 wf_event.GetValueForParameter('USER_NAME', p_parameters));
413 end;
414
415 ------------------------------------------------------------------------------
416 ------------------------------------------------------------------------------
417 end WF_OID;