[Home] [Help]
PACKAGE BODY: APPS.FND_OID_SUBSCRIPTIONS
Source
1 package body fnd_oid_subscriptions as
2 /* $Header: AFSCOSBB.pls 120.21 2012/04/12 19:59:13 ctilley ship $ */
3 --
4 /*****************************************************************************/
5 -- Start of Package Globals
6
7 G_MODULE_SOURCE constant varchar2(80) :=
8 'fnd.plsql.oid.fnd_oid_subscriptions.';
9
10 -- End of Package Globals
11 --
12 -------------------------------------------------------------------------------
13 -- Start of Private Program Units
14 /*
15 ** Name : fnd_create_update
16 ** Type : Public, FND Internal
17 ** Desc :
18 ** Pre-Reqs :
19 ** Parameters :
20 */
21 procedure fnd_create_update(
22 p_event_type in varchar2
23 , p_user_name in fnd_user.user_name%type
24 , p_owner in varchar2
25 , p_unencrypted_password in varchar2
26 , p_description in fnd_user.description%type
27 , p_email_address in fnd_user.email_address%type
28 , p_fax in fnd_user.fax%type
29 , p_start_date in varchar2
30 , p_end_date in varchar2
31 , p_isenabled in varchar2
32 , p_user_guid in fnd_user.user_guid%type
33 , x_user_id out nocopy fnd_user.user_id%type
34 ) is
35 l_module_source varchar2(256);
36 l_apps_username_key fnd_oid_util.apps_user_key_type;
37 l_apps_userguid_key fnd_oid_util.apps_user_key_type;
38 l_user_name fnd_user.user_name%type;
39 l_found boolean;
40 l_allow_sync varchar2(1);
41 l_user_profiles fnd_oid_util.apps_sso_user_profiles_type;
42 l_profile_defined boolean;
43 l_start_date date;
44 l_end_date date;
45 l_description fnd_user.description%type;
46 l_fax fnd_user.fax%type;
47 l_email_address fnd_user.email_address%type;
48
49
50 begin
51 l_module_source := G_MODULE_SOURCE || 'fnd_create_update: ';
52 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
53 then
54 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
55 end if;
56 if (p_event_type = wf_oid.IDENTITY_MODIFY) then
57 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
58 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'IDENTITY_MODIFY '
59 || 'event is handled by fnd_user_pkg.user_change().');
60 end if;
61 return;
62 end if;
63 --Check whether this guid already exists. Don't raise alerts because we receive echo
64 --from OID for each user that is created.
65 l_apps_userguid_key := fnd_oid_util.get_fnd_user(p_user_guid => p_user_guid);
66 -- Disable user in FND when user unsubscribed from the application in OID
67 if (p_event_type = wf_oid.SUBSCRIPTION_DELETE) then
68 update fnd_user set end_date= sysdate
69 where user_name = l_apps_userguid_key.user_name;
70 end if;
71 if (l_apps_userguid_key.user_id is not null) then
72 if(fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
73 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,
74 'GUID ' || p_user_guid || ' is already linked'
75 || ' to user_name ' || l_apps_userguid_key.user_name);
76 end if;
77 return;
78 end if;
79 -- Check whether this user_name already exists. We won't link (update user_guid) here.
80 -- We let Auto Link profile or the linking page controls who is linked to whom.
81 l_apps_username_key := fnd_oid_util.get_fnd_user(p_user_name => p_user_name);
82 if (l_apps_username_key.user_id is not null) then
83 -- Is this user linked to someone else? Raise alert because it's a security threat.
84 if (l_apps_username_key.user_guid is not null and l_apps_username_key.user_guid <> p_user_guid) then
85 if(fnd_log.LEVEL_UNEXPECTED >=
86 fnd_log.G_CURRENT_RUNTIME_LEVEL) then
87 fnd_message.SET_NAME('FND', 'FND_SSO_UNABLE_TO_LINK');
88 fnd_message.SET_TOKEN('USER_NAME', p_user_name);
89 fnd_message.SET_TOKEN('ORCLGUID', l_apps_username_key.user_guid);
90 fnd_log.MESSAGE(fnd_log.LEVEL_UNEXPECTED, l_module_source, TRUE);
91 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,
92 'User_name ' || p_user_name || '
93 already exists in E-Business and is linked to an OID account with GUID '
94 || l_apps_username_key.user_guid);
95 end if;
96 end if;
97 return;
98 end if;
99 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
100 then
101 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Enabled flag is: ' || p_isenabled);
102 end if;
103 -- Don't create user if disabled in OID
104 if (p_isenabled = 'INACTIVE' or p_isenabled = 'DISABLED') then
105 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
106 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
107 'User ' || p_user_name || ' will not be created in FND_USER, because ' ||
108 'it''s Disabled in OID.');
109 end if;
110 return;
111 end if;
112
113 --This means neither user name nor guid exist in fnd_user
114 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
115 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
116 'User does not exist in FND_USER. About to create a new user...');
117 end if;
118 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
119 then
120 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Start_date input: ' || p_start_date);
121 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End_date input: ' || p_end_date);
122 end if;
123 -- WF_ENTITY_MGR returns '*UNKNOWN*' string if the value was not found in attr_cache table
124 -- we should not populate the fnd_user table with this, for fax and description.
125
126 -- Bug 5347086 - fnd_user_pkg does not check for UNKNOWN when creating the user. Doing it here
127 if (p_description in ('*UNKNOWN*','*NULL*')) then
128 l_description := null;
129 else
130 l_description := p_description;
131 end if;
132
133 if (p_email_address in ('*UNKNOWN*','*NULL*')) then
134 l_email_address := null;
135 else
136 l_email_address := p_email_address;
137 end if;
138
139 if (p_fax in ('*UNKNOWN*','*NULL*')) then
140 l_fax := null;
141 else
142 l_fax := p_fax;
143 end if;
144
145 if (p_start_date is null or p_start_date = '*UNKNOWN*') then
146 l_start_date := sysdate;
147 else
148 l_start_date := to_date(substr(p_start_date, 1, 14), fnd_oid_util.G_YYYYMMDDHH24MISS);
149 end if;
150
151 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
152 then
153 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Start_date output: ' || l_start_date);
154 end if;
155
156 if (p_end_date is null or p_end_date = '*UNKNOWN*') then
157 l_end_date := null;
158 else
159 l_end_date := to_date(substr(p_end_date, 1, 14), fnd_oid_util.G_YYYYMMDDHH24MISS);
160 end if;
161
162 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
163 then
164 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End_date output: ' || l_end_date);
165 end if;
166
167 x_user_id := fnd_user_pkg.CreateUserId(
168 x_user_name => p_user_name
169 , x_owner => p_owner
170 , x_unencrypted_password => fnd_web_sec.EXTERNAL_PWD -- passowrd will be set to EXTERNAL
171 , x_description => l_description
172 , x_email_address => l_email_address
173 , x_start_date => l_start_date
174 , x_end_date => l_end_date
175 , x_fax => l_fax
176 , x_user_guid => p_user_guid
177 , x_change_source => fnd_user_pkg.change_source_oid
178 );
179
180 -- API to set user profile value;
181 l_found := fnd_profile.save(x_name => 'APPS_SSO_LOCAL_LOGIN'
182 , x_value => 'SSO'
183 , x_level_name => 'USER'
184 , x_level_value => x_user_id);
185 if not l_found then
186 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
187 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
188 'Unable to set APPS_SSO_LOCAL_LOGIN profile value to SSO for user ' || p_user_name);
189 end if;
190 end if;
191
192 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
193 then
194 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
195 end if;
196
197 exception
198 when others then
199 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
200 then
201 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
202 end if;
203 end fnd_create_update;
204
205 procedure fnd_create_update(
206 p_wf_event in wf_event_t
207 , p_event_type in varchar2
208 , p_user_name in fnd_user.user_name%type
209 , p_user_guid in fnd_user.user_guid%type
210 , x_user_id out nocopy fnd_user.user_id%type
211 ) is
212
213 l_module_source varchar2(256);
214 l_description fnd_user.description%type;
215 l_email_address fnd_user.email_address%type;
216 l_fax fnd_user.fax%type;
217 l_user_id number;
218 l_start_date varchar2(4000);
219 l_end_date varchar2(4000);
220 l_isenabled varchar2(4000);
221
222 begin
223 l_module_source := G_MODULE_SOURCE || 'fnd_create_update: ';
224
225 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
226 then
227 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
228 end if;
229
230 -- Read the values from wf_attribute_cache
231 l_description := wf_entity_mgr.get_attribute_value(p_event_type,
232 p_user_name, 'DESCRIPTION');
233 l_email_address := wf_entity_mgr.get_attribute_value(p_event_type,
234 p_user_name, 'MAIL');
235 l_fax := wf_entity_mgr.get_attribute_value(p_event_type,
236 p_user_name, 'FACSIMILETELEPHONENUMBER');
237 l_start_date := wf_entity_mgr.get_attribute_value(p_event_type,
238 p_user_name, 'ORCLACTIVESTARTDATE');
239 l_end_date := wf_entity_mgr.get_attribute_value(p_event_type,
240 p_user_name, 'ORCLACTIVEENDDATE');
241 l_isenabled := wf_entity_mgr.get_attribute_value(p_event_type,
242 p_user_name, 'ORCLISENABLED');
243 if (l_isenabled = '*UNKNOWN*') then
244 l_isenabled := wf_entity_mgr.get_attribute_value('USER',
245 p_user_name, 'ORCLISENABLED');
246 end if;
247
248 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
249 then
250 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
251 'l_description = ' || l_description ||
252 'l_email_address = ' || l_email_address ||
253 'l_fax = ' || l_fax
254 );
255 end if;
256
257 fnd_create_update(
258 p_event_type => p_event_type
259 , p_user_name => p_wf_event.GetEventKey
260 , p_user_guid => p_user_guid
261 , p_owner => fnd_oid_util.G_CUST
262 , p_unencrypted_password => null
263 , p_description => l_description
264 , p_email_address => l_email_address
265 , p_fax => l_fax
266 , p_start_date => l_start_date
267 , p_end_date => l_end_date
268 , p_isenabled => l_isenabled
269 , x_user_id => l_user_id);
270
271 x_user_id := l_user_id;
272
273 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
274 then
275 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
276 end if;
277 exception
278 when others then
279 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
280 then
281 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
282 end if;
283 end fnd_create_update;
284 --
285
286 -------------------------------------------------------------------------------
287 -------------------------------------------------------------------------------
288 /*
289 ** Name : hz_create_update
290 ** Type : Public, FND Internal
291 ** Desc :
292 ** Pre-Reqs :
293 ** Parameters :
294 */
295 procedure hz_create_update(
296 p_wf_event in wf_event_t
297 , p_event_type in varchar2
298 , p_return_status out nocopy varchar2
299 );
300 --
301 -- End of Private Program Units
302 --
303 -------------------------------------------------------------------------------
304 function identity_add(
305 p_subscription_guid in raw
306 , p_event in out nocopy wf_event_t)
307 return varchar2 is
308
309 l_module_source varchar2(256);
310 l_event_name varchar2(256);
311 l_event_key varchar2(256);
312 l_change_source varchar2(256);
313 l_user_id number;
314 l_orcl_guid fnd_user.user_guid%type;
315
316 begin
317 l_module_source := G_MODULE_SOURCE || 'identity_add: ';
318
319 if (fnd_log.LEVEL_EVENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
320 then
321 fnd_log.string(fnd_log.LEVEL_EVENT, l_module_source,
322 'EBIZ is NOW capable of understanding IDENTITY_ADD');
323 end if;
324 --RDESPOTO, Add IDENTITY_ADD, 11/09/2004
325 l_event_key := p_event.GetEventKey;
326 l_event_name := WF_OID.IDENTITY_ADD;
327
328 l_change_source := p_event.GetValueForParameter(
329 fnd_oid_util.G_CHANGE_SOURCE);
330
331 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
332 then
333 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
334 , 'l_event_key = ' || l_event_key ||
335 ', l_event_name = ' || l_event_name ||
336 ', l_change_source = ' || l_change_source);
337 end if;
338 --Change_source has to be OID
339 if (l_change_source = fnd_oid_util.G_OID) then
340 l_orcl_guid := p_event.GetValueForParameter(
341 fnd_oid_util.G_ORCLGUID);
342 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
343 then
344 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'User_guid is:'
345 || l_orcl_guid || ', user_name is ' || l_event_key);
346 end if;
347 -- Maintain IDENTITY_ADD event type to pick up email and fax correctly
348 fnd_create_update(
349 p_wf_event => p_event
350 , p_event_type => wf_oid.IDENTITY_ADD
351 , p_user_name => l_event_key
352 , p_user_guid => l_orcl_guid
353 , x_user_id => l_user_id
354 );
355 end if;
356 return(wf_rule.default_rule(p_subscription_guid, p_event));
357 exception
358 when others then
359 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
360 then
361 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
362 end if;
363 return(wf_rule.error_rule(p_subscription_guid, p_event));
364
365 end identity_add;
366 --
367 -------------------------------------------------------------------------------
368 function identity_modify(
369 p_subscription_guid in raw
370 , p_event in out nocopy wf_event_t)
371 return varchar2 is
372
373 l_module_source varchar2(256);
374 l_user_id number;
375 l_orcl_guid fnd_user.user_guid%type;
376
377 begin
378 l_module_source := G_MODULE_SOURCE || 'identity_modify: ';
379
380 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
381 then
382 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
383 end if;
384 --THIS SUBSCRIPTION IS NOT USED! fnd_user_pkg.user_change SUBSCRIPTION IS USED INSTEAD.
385 --Rada, 01/31/2005
386 l_orcl_guid := p_event.GetValueForParameter(
387 fnd_oid_util.G_ORCLGUID);
388 fnd_create_update(
389 p_wf_event => p_event
390 , p_event_type => WF_OID.IDENTITY_MODIFY
391 , p_user_name => p_event.GetEventKey()
392 , p_user_guid => l_orcl_guid
393 , x_user_id => l_user_id
394 );
395
396 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
397 then
398 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
399 end if;
400
401 return(wf_rule.default_rule(p_subscription_guid, p_event));
402
403 exception
404 when others then
405 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
406 then
407 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
408 end if;
409 return(wf_rule.error_rule(p_subscription_guid, p_event));
410
411 end identity_modify;
412 --
413 -------------------------------------------------------------------------------
414 function identity_delete(
415 p_subscription_guid in raw
416 , p_event in out nocopy wf_event_t)
417 return varchar2 is
418
419 l_module_source varchar2(256);
420
421 begin
422 l_module_source := G_MODULE_SOURCE || 'identity_delete: ';
423
424 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
425 then
426 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
427 end if;
428
429 -- Do something
430
431 -- Bug 13829710
432 -- Moving the end dating of the user to this subscription so it may be
433 -- disabled
434 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
435 then
436 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End dating user: '||p_event.GetEventKey);
437 end if;
438
439 update fnd_user
440 set end_date = sysdate
441 where user_name = p_event.GetEventKey;
442
443 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
444 then
445 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
446 end if;
447
448 return(wf_rule.default_rule(p_subscription_guid, p_event));
449
450 exception
451 when others then
452 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
453 then
454 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
455 end if;
456 return(wf_rule.error_rule(p_subscription_guid, p_event));
457
458 end identity_delete;
459 --
460 -------------------------------------------------------------------------------
461 /**
462 * This subscription handles the following events:
463 * OID -> User subscribing to an EBIZ instance
464 * EBIZ -> Linking of an FND_USER to OID user
465 * In both instances a SUBSCRIPTION_ADD event is raised. The change_source
466 * attribute is however different as below:
467 * OID -> change_source attribute is OID
468 * EBIZ -> change_source attribute is EBIZ
469 * Please make sure that the change_source is indeed EBIZ and not FND_USER
470 * when the event is raised by EBIZ.
471 */
472 function subscription_add(
473 p_subscription_guid in raw
474 , p_event in out nocopy wf_event_t)
475 return varchar2 is
476
477 l_module_source varchar2(256);
478 l_sso_user_profiles fnd_oid_util.apps_sso_user_profiles_type;
479 l_event_name varchar2(256);
480 l_event_key varchar2(256);
481 l_change_source varchar2(256);
482 l_user_id number;
483 l_orcl_guid fnd_user.user_guid%type;
484
485 begin
486 l_module_source := G_MODULE_SOURCE || 'subscription_add: ';
487
488 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
489 then
490 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
491 end if;
492 l_event_key := p_event.GetEventKey;
493 l_event_name := WF_OID.SUBSCRIPTION_ADD;
494
495 l_change_source := p_event.GetValueForParameter(
496 fnd_oid_util.G_CHANGE_SOURCE);
497
498 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
499 then
500 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
501 , 'l_event_key = ' || l_event_key ||
502 ', l_event_name = ' || l_event_name ||
503 ', l_change_source = ' || l_change_source);
504 end if;
505
506 if (l_change_source = fnd_oid_util.G_OID) then
507 l_orcl_guid := p_event.GetValueForParameter(
508 fnd_oid_util.G_ORCLGUID);
509 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
510 then
511 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'User_guid is:'
512 || l_orcl_guid || ', user_name is ' || l_event_key);
513 end if;
514 fnd_create_update(
515 p_wf_event => p_event
516 , p_event_type => wf_oid.SUBSCRIPTION_ADD
517 , p_user_name => l_event_key
518 , p_user_guid => l_orcl_guid
519 , x_user_id => l_user_id
520 );
521 end if;
522
523 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
524 then
525 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
526 end if;
527
528 return(wf_rule.default_rule(p_subscription_guid, p_event));
529
530 exception
531 when others then
532 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
533 then
534 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
535 end if;
536 return(wf_rule.error_rule(p_subscription_guid, p_event));
537
538 end subscription_add;
539 --
540 -------------------------------------------------------------------------------
541 /**
542 * This subscription handles the following events:
543 * OID -> User unsubscribing to an EBIZ instance
544 * EBIZ -> Uninking of an FND_USER with the OID user
545 * In both instances a SUBSCRIPTION_DELETE event is raises. The change_source
546 * attribute is however different as below:
547 * OID -> change_source attribute is OID
548 * EBIZ -> change_source attribute is EBIZ
549 * Please make sure that the change_source is indeed EBIZ and not FND_USER
550 * when the event is raised by EBIZ.
551 */
552 function subscription_delete(
553 p_subscription_guid in raw
554 , p_event in out nocopy wf_event_t)
555 return varchar2 is
556
557 l_module_source varchar2(256);
558 l_sso_user_profiles fnd_oid_util.apps_sso_user_profiles_type;
559 l_event_name varchar2(256);
560 l_event_key varchar2(256);
561 l_change_source varchar2(256);
562 l_user_id number;
563 l_orcl_guid fnd_user.user_guid%type;
564
565
566 begin
567 l_module_source := G_MODULE_SOURCE || 'subscription_delete: ';
568
569 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
570 then
571 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
572 end if;
573
574 l_event_key := p_event.GetEventKey;
575 l_event_name := WF_OID.SUBSCRIPTION_DELETE;
576 l_change_source := p_event.GetValueForParameter(
577 fnd_oid_util.G_CHANGE_SOURCE);
578
579 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
580 then
581 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
582 , 'l_event_key = ' || l_event_key ||
583 ', l_event_name = ' || l_event_name ||
584 ', l_change_source = ' || l_change_source);
585 end if;
586
587 l_sso_user_profiles := fnd_ldap_mapper.map_sso_user_profiles(l_event_key);
588
589 if (l_change_source = fnd_oid_util.G_EBIZ)
590 then
591 insert into wf_entity_changes(
592 entity_type, entity_key_value, flavor, change_date)
593 values(
594 wf_oid.SUBSCRIPTION_DELETE, l_event_key, l_change_source, sysdate);
595
596 else
597 --Rada, 01/31/2005
598 l_orcl_guid := p_event.GetValueForParameter(
599 fnd_oid_util.G_ORCLGUID);
600 /*fnd_create_update(
601 p_event_type => l_event_name
602 , p_user_name => p_event.GetEventKey
603 , p_user_guid => l_orcl_guid
604 , p_owner => fnd_oid_util.G_CUST
605 , p_unencrypted_password => null
606 , p_description => null
607 , p_email_address => null
608 , p_fax => null
609 , p_start_date => fnd_oid_util.G_NULL
610 , p_end_date => fnd_oid_util.G_NULL
611 , x_user_id => l_user_id);*/
612 fnd_create_update(
613 p_wf_event => p_event
614 , p_event_type => wf_oid.SUBSCRIPTION_DELETE
615 , p_user_name => l_event_key
616 , p_user_guid => l_orcl_guid
617 , x_user_id => l_user_id
618 );
619 end if;
620
621 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
622 then
623 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
624 end if;
625
626 return(wf_rule.default_rule(p_subscription_guid, p_event));
627
628 exception
629 when others then
630 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
631 then
632 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
633 end if;
634 return(wf_rule.error_rule(p_subscription_guid, p_event));
635 end subscription_delete;
636 -------------------------------------------------------------------------------
637 --
638 function synch_oid_to_tca(
639 p_subscription_guid in raw
640 , p_event in out nocopy wf_event_t)
641 return varchar2 is
642
643 cursor cur_attribute_cache(p_user_name in wf_attribute_cache.entity_key_value%type) is
644 select attribute_name
645 , attribute_value
646 from wf_attribute_cache
647 where entity_type = fnd_oid_util.G_USER
648 and entity_key_value = p_user_name
649 and attribute_name <> fnd_oid_util.G_CACHE_CHANGED;
650
651 l_module_source varchar2(256);
652 l_user_name fnd_user.user_name%type;
653 l_result pls_integer;
654
655 l_old_user_guid fnd_user.user_guid%type;
656 l_user_guid fnd_user.user_guid%type;
657 l_user_guid_changed boolean;
658
659 l_old_person_party_id fnd_user.person_party_id%type;
660 l_person_party_id fnd_user.person_party_id%type;
661 l_person_party_id_changed boolean;
662 l_apps_sso_link_truth_src varchar2(5);
663 l_profile_defined boolean;
664 begin
665 l_module_source := G_MODULE_SOURCE || ' synch_oid_to_tca: ';
666
667 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
668 then
669 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
670 end if;
671
672 -- Ignore TCA's own changes
673 -- bug 4411121
674 -- If the change source is HZ_PARTY. Further processing is stopped
675 -- This has to be addressed later. If this was HZ change we have to push it to OID.
676 if (p_event.GetValueForParameter('CHANGE_SOURCE') = 'HZ_PARTY') then
677
678 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
679 then
680 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'changeSrc is TCA no processing required');
681 end if;
682
683 return 'SUCCESS';
684 end if;
685
686
687 l_user_guid_changed := false;
688 l_person_party_id_changed := false;
689
690 l_user_name := p_event.getEventKey();
691
692 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
693 then
694 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
695 'USER_NAME: '||l_user_name);
696 end if;
697
698 for l_rec in cur_attribute_cache(l_user_name)
699 loop
700 if (l_rec.attribute_name = 'OLD_PERSON_PARTY_ID')
701 then
702 l_old_person_party_id := l_rec.attribute_value;
703
704 elsif(l_rec.attribute_name = 'OLD_ORCLGUID')
705 then
706 l_old_user_guid := l_rec.attribute_value;
707
708 elsif(l_rec.attribute_name = 'ORCLGUID')
709 then
710 l_user_guid := l_rec.attribute_value;
711
712 else
713 if(l_rec.attribute_name = 'PERSON_PARTY_ID')
714 then
715 l_person_party_id := l_rec.attribute_value;
716 end if;
717 end if;
718 end loop;
719
720 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
721 then
722 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
723 ' OLD_PERSON_PARTY_ID:: '||l_old_person_party_id||'::'||
724 ' PERSON_PARTY_ID:: '||l_person_party_id||'::'||
725 ' OLD_ORCLGUID::'||l_old_user_guid||'::'||
726 ' ORCLGUID::'||l_user_guid||'::');
727 end if;
728
729 --verify if the "*NULL*" case needs to be handled separately. !!scheruku
730
731 if(l_old_person_party_id IS NULL and l_person_party_id IS NOT NULL) or
732 (l_old_person_party_id <> l_person_party_id)
733 then
734 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
735 then
736 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
737 'person party id changed');
738 end if;
739 l_person_party_id_changed := true;
740 end if;
741
742 if(l_old_user_guid IS NULL and l_user_guid IS NOT NULL) or (l_old_user_guid <> l_user_guid)
743 then
744 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
745 then
746 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
747 'GUID changed');
748 end if;
749 l_user_guid_changed := true;
750 end if;
751
752
753 if(l_person_party_id_changed or l_user_guid_changed)
754 then
755 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
756 then
757 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
758 ' Either GUID or person party id has changed:');
759 end if;
760
761 fnd_profile.get_specific(
762 name_z => 'APPS_SSO_LINK_TRUTH_SRC',
763 val_z => l_apps_sso_link_truth_src,
764 defined_z => l_profile_defined);
765
766
767 if(l_apps_sso_link_truth_src is NULL or l_apps_sso_link_truth_src = fnd_oid_util.G_OID)
768 then
769 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
770 then
771 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
772 ' OID is source of Truth during linking ');
773 end if;
774 fnd_oid_util.synch_user_from_LDAP_NO_AUTO(p_user_name => l_user_name,
775 p_result => l_result);
776
777 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
778 then
779 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
780 ' After synch l_result: '||l_result);
781 end if;
782 else
783 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
784 then
785 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
786 ' Apps is source of Truth during linking ');
787 end if;
788 ---Fix me as and when available add code here to fetch attributes from TCA/HR and send to OID. !!scheruku
789 end if;
790 end if;
791
792
793 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
794 then
795 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
796 end if;
797
798 return(wf_rule.default_rule(p_subscription_guid, p_event));
799 exception
800 when others then
801 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
802 then
803 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
804 end if;
805 return(wf_rule.error_rule(p_subscription_guid, p_event));
806 end synch_oid_to_tca;
807
808 -------------------------------------------------------------------------------
809 --
810 function on_demand_user_create(
811 p_subscription_guid in raw
812 , p_event in out nocopy wf_event_t)
813 return varchar2 is
814
815 l_module_source varchar2(256);
816 l_user_name fnd_user.user_name%type;
817 l_user_guid fnd_user.user_guid%type;
818 l_result pls_integer;
819
820 begin
821 l_module_source := G_MODULE_SOURCE || ' on_demand_user_create: ';
822
823 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
824 then
825 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
826 end if;
827 l_user_name:= p_event.GetValueForParameter('USER_NAME');
828 l_user_guid:= p_event.GetValueForParameter('ORCLGUID');
829
830 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
831 then
832 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
833 'username: '||l_user_name||' user_guid: '||l_user_guid);
834 end if;
835
836 -- Replaced by subscribing assign_def_resp to "oracle.apps.fnd.ondemand.create" event
837 -- assign_default_resp(
838 -- p_user_name=>l_user_name
839 -- );
840
841 fnd_oid_util.synch_user_from_LDAP_NO_AUTO(p_user_name => l_user_name,
842 p_result => l_result);
843 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
844 then
845 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
846 ' After synch l_result: '||l_result);
847 end if;
848
849 return(wf_rule.default_rule(p_subscription_guid, p_event));
850
851 exception
852 when others then
853 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
854 then
855 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
856 end if;
857 return(wf_rule.error_rule(p_subscription_guid, p_event));
858
859 end on_demand_user_create;
860 -------------------------------------------------------------------------------
861 --
862 function event_error(
863 p_subscription_guid in raw
864 , p_event in out nocopy wf_event_t)
865 return varchar2 is
866
867 l_module_source varchar2(256);
868 l_event_id wf_entity_changes.entity_id%type;
869 l_user_name fnd_user.user_name%type;
870 l_user_guid fnd_user.user_guid%type;
871
872 begin
873 l_module_source := G_MODULE_SOURCE || 'event_error: ';
874
875 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
876 then
877 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
878 end if;
879 --RDESPOTO, 09/07/2004
880 --need constants for these in FND_OID_UTIL
881 l_event_id := p_event.GetValueForParameter('ENTITY_ID');
882 l_user_name:= p_event.GetValueForParameter('USER_NAME');
883 l_user_guid:= p_event.GetValueForParameter('ORCLGUID');
884
885
886 if(fnd_log.LEVEL_UNEXPECTED >=
887 fnd_log.G_CURRENT_RUNTIME_LEVEL) then
888
889 fnd_message.SET_NAME('FND', 'FND_SSO_EVENT_ERROR');
890 fnd_message.SET_TOKEN('USER_NAME', l_user_name);
891 fnd_message.SET_TOKEN('ENTITY_ID', l_event_id);
892 fnd_message.SET_TOKEN('ORCLGUID', l_user_guid);
893 fnd_log.MESSAGE(fnd_log.LEVEL_UNEXPECTED,
894 l_module_source, TRUE);
895 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,
896 'Synchronization of user definiton between E-Business Suite'||
897 ' and Oracle Internet Directory has failed for user:' || l_user_name ||
898 ', event id:' || l_event_id || ', guid:' || l_user_guid);
899 end if;
900
901 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
902 then
903 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
904 end if;
905
906 return(wf_rule.default_rule(p_subscription_guid, p_event));
907
908 exception
909 when others then
910 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
911 then
912 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
913 end if;
914 return(wf_rule.error_rule(p_subscription_guid, p_event));
915 end event_error;
916 --
917 -------------------------------------------------------------------------------
918 function event_resend(
919 p_subscription_guid in raw
920 , p_event in out nocopy wf_event_t)
921 return varchar2 is
922
923 l_module_source varchar2(256);
924 l_event_id wf_entity_changes.entity_id%type;
925 l_user_name fnd_user.user_name%type;
926 l_user_guid fnd_user.user_guid%type;
927
928 begin
929 l_module_source := G_MODULE_SOURCE || 'event_resend: ';
930
931 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
932 then
933 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
934 end if;
935 --RDESPOTO, 09/07/2004
936 --need constants for these in FND_OID_UTIL
937 l_event_id := p_event.GetValueForParameter('ENTITY_ID');
938 l_user_name:= p_event.GetValueForParameter('USER_NAME');
939 l_user_guid:= p_event.GetValueForParameter('ORCLGUID');
940
941
942 if(fnd_log.LEVEL_UNEXPECTED >=
943 fnd_log.G_CURRENT_RUNTIME_LEVEL) then
944
945 fnd_message.SET_NAME('FND', 'FND_SSO_EVENT_RESEND');
946 fnd_message.SET_TOKEN('USER_NAME', l_user_name);
947 fnd_message.SET_TOKEN('ENTITY_ID', l_event_id);
948 fnd_message.SET_TOKEN('ORCLGUID', l_user_guid);
949 fnd_log.MESSAGE(fnd_log.LEVEL_UNEXPECTED,
950 l_module_source, TRUE);
951 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,
952 'Synchronization event between E-Business Suite'
953 || ' and Oracle Internet Directory has to be resent for user '
954 || l_user_name || ', event id:' || l_event_id || ', guid:' || l_user_guid);
955 end if;
956
957 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
958 then
959 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
960 end if;
961
962 return(wf_rule.default_rule(p_subscription_guid, p_event));
963
964 exception
965 when others then
966 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
967 then
968 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
969 end if;
970 return(wf_rule.error_rule(p_subscription_guid, p_event));
971 end event_resend;
972 --
973 -------------------------------------------------------------------------------
974 function hz_identity_add(
975 p_subscription_guid in raw
976 , p_event in out nocopy wf_event_t)
977 return varchar2 is
978
979 l_module_source varchar2(256);
980 l_return_status varchar2(1);
981 l_change_source varchar2(256);
982
983 begin
984 l_module_source := G_MODULE_SOURCE || 'hz_identity_add: ';
985
986 if (fnd_log.LEVEL_EVENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
987 then
988 fnd_log.string(fnd_log.LEVEL_EVENT, l_module_source,
989 'EBIZ is NOW capable of understanding IDENTITY_ADD.');
990 end if;
991 --RDESPOTO, Add IDENTITY_ADD, 11/09/2004
992 l_change_source := p_event.GetValueForParameter(
993 fnd_oid_util.G_CHANGE_SOURCE);
994 --Change_source has to be OID
995 if (l_change_source = fnd_oid_util.G_OID) then
996 hz_create_update(
997 p_wf_event => p_event
998 , p_event_type => wf_oid.IDENTITY_ADD
999 , p_return_status => l_return_status);
1000 --RDESPOTO, End IDENTITY_ADD
1001 end if;
1002
1003 return(wf_rule.default_rule(p_subscription_guid, p_event));
1004
1005 exception
1006 when others then
1007 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1008 then
1009 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1010 end if;
1011 return(wf_rule.error_rule(p_subscription_guid, p_event));
1012 end hz_identity_add;
1013 --
1014 -------------------------------------------------------------------------------
1015 function hz_identity_modify(
1016 p_subscription_guid in raw
1017 , p_event in out nocopy wf_event_t)
1018 return varchar2 is
1019
1020 l_module_source varchar2(256);
1021 l_ldap_message fnd_oid_util.ldap_message_type;
1022 l_return_status varchar2(1);
1023 hz_failed_exp exception;
1024
1025 begin
1026 l_module_source := G_MODULE_SOURCE || 'hz_identity_modify: ';
1027
1028 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1029 then
1030 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1031 end if;
1032
1033 hz_create_update(
1034 p_wf_event => p_event
1035 , p_event_type => wf_oid.IDENTITY_MODIFY
1036 , p_return_status => l_return_status);
1037
1038 if (l_return_status <> fnd_api.G_RET_STS_SUCCESS)
1039 then
1040 raise hz_failed_exp;
1041 end if;
1042
1043 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1044 then
1045 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1046 end if;
1047
1048 return(wf_rule.default_rule(p_subscription_guid, p_event));
1049
1050 exception
1051 when hz_failed_exp then
1052 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1053 then
1054 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source,
1055 'hz_failed_exp: l_return_status = ' ||
1056 l_return_status);
1057 end if;
1058 return(wf_rule.error_rule(p_subscription_guid, p_event));
1059
1060 when others then
1061 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1062 then
1063 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1064 end if;
1065 return(wf_rule.error_rule(p_subscription_guid, p_event));
1066 end hz_identity_modify;
1067 --
1068 -------------------------------------------------------------------------------
1069 function hz_identity_delete(
1070 p_subscription_guid in raw
1071 , p_event in out nocopy wf_event_t)
1072 return varchar2 is
1073
1074 l_module_source varchar2(256);
1075
1076 begin
1077 l_module_source := G_MODULE_SOURCE || 'hz_identity_delete: ';
1078
1079 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1080 then
1081 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source,
1082 'This is a no-op for now. This subscription should be disabled. ' ||
1083 ' Please contact your System administrator to disable subscription');
1084 end if;
1085
1086 return(wf_rule.default_rule(p_subscription_guid, p_event));
1087
1088 exception
1089 when others then
1090 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1091 then
1092 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1093 end if;
1094 return(wf_rule.error_rule(p_subscription_guid, p_event));
1095 end hz_identity_delete;
1096 --
1097 -------------------------------------------------------------------------------
1098 function hz_subscription_add(
1099 p_subscription_guid in raw
1100 , p_event in out nocopy wf_event_t)
1101 return varchar2 is
1102
1103 l_module_source varchar2(256);
1104 l_ldap_message fnd_oid_util.ldap_message_type;
1105 l_return_status varchar2(1);
1106 hz_failed_exp exception;
1107
1108 begin
1109 l_module_source := G_MODULE_SOURCE || 'hz_subscription_add: ';
1110
1111 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1112 then
1113 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1114 end if;
1115
1116 hz_create_update(
1117 p_wf_event => p_event
1118 , p_event_type => wf_oid.SUBSCRIPTION_ADD
1119 , p_return_status => l_return_status);
1120
1121 if (l_return_status <> fnd_api.G_RET_STS_SUCCESS)
1122 then
1123 raise hz_failed_exp;
1124 end if;
1125
1126 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1127 then
1128 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1129 end if;
1130
1131 return(wf_rule.default_rule(p_subscription_guid, p_event));
1132
1133 exception
1134 when hz_failed_exp then
1135 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1136 then
1137 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source,
1138 'hz_failed_exp: l_return_status = ' ||
1139 l_return_status);
1140 end if;
1141 return(wf_rule.error_rule(p_subscription_guid, p_event));
1142
1143 when others then
1144 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1145 then
1146 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1147 end if;
1148 return(wf_rule.error_rule(p_subscription_guid, p_event));
1149 end hz_subscription_add;
1150 --
1151 -------------------------------------------------------------------------------
1152 function hz_subscription_delete(
1153 p_subscription_guid in raw
1154 , p_event in out nocopy wf_event_t)
1155 return varchar2 is
1156
1157 l_module_source varchar2(256);
1158
1159 begin
1160 l_module_source := G_MODULE_SOURCE || 'hz_subscription_delete: ';
1161
1162 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1163 then
1164 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source,
1165 'This is a no-op for now. This subscription should be disabled. ' ||
1166 ' Please contact your System administrator to disable subscription');
1167 end if;
1168
1169 return(wf_rule.default_rule(p_subscription_guid, p_event));
1170
1171 exception
1172 when others then
1173 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1174 then
1175 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1176 end if;
1177 return(wf_rule.error_rule(p_subscription_guid, p_event));
1178 end hz_subscription_delete;
1179 --
1180 -------------------------------------------------------------------------------
1181 procedure hz_create_update(
1182 p_wf_event in wf_event_t
1183 , p_event_type in varchar2
1184 , p_return_status out nocopy varchar2
1185 ) is
1186
1187 l_module_source varchar2(256);
1188 l_ldap_message fnd_oid_util.ldap_message_type;
1189 l_return_status varchar2(1);
1190 l_count number;
1191 l_event_key varchar2(256);
1192
1193 begin
1194 l_module_source := G_MODULE_SOURCE || 'hz_create_update: ';
1195
1196 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1197 then
1198 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1199 end if;
1200
1201 fnd_ldap_mapper.map_ldap_message(p_wf_event, p_event_type, l_ldap_message);
1202
1203 if (fnd_oid_util.person_party_exists(p_wf_event.GetEventKey))
1204 then
1205 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1206 then
1207 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
1208 'Person Party exists in FND_USER');
1209 end if;
1210 fnd_oid_users.hz_update(
1211 p_ldap_message => l_ldap_message
1212 , x_return_status => p_return_status);
1213 else
1214 l_event_key := p_wf_event.GetEventKey;
1215 select count(*) into l_count
1216 from fnd_user
1217 where user_name = l_event_key
1218 and user_guid is not null;
1219
1220 if (l_count > 0) then
1221 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1222 then
1223 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
1224 'Person Party does NOT exist in FND_USER, creating a new TCA entry');
1225 end if;
1226 fnd_oid_users.hz_create(
1227 p_ldap_message => l_ldap_message
1228 , x_return_status => p_return_status);
1229 else
1230 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1231 then
1232 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
1233 'FND User is not linked to OID user, therefore not creating TCA party');
1234 end if;
1235 end if;
1236
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 others 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, sqlerrm);
1249 end if;
1250 end hz_create_update;
1251 --
1252 -------------------------------------------------------------------------------
1253 procedure get_resp_app_id(p_resp_key in fnd_responsibility.responsibility_key%type
1254 , x_responsibility_id out nocopy fnd_responsibility.responsibility_id%type
1255 , x_application_id out nocopy fnd_responsibility.application_id%type) is
1256
1257 l_module_source varchar2(256);
1258 l_responsibility_id fnd_responsibility.responsibility_id%type;
1259 l_application_id fnd_responsibility.application_id%type;
1260 l_found boolean;
1261
1262 cursor cur_fnd_responsibility is
1263 SELECT responsibility_id, application_id
1264 from fnd_responsibility
1265 where RESPONSIBILITY_KEY = p_resp_key;
1266 begin
1267 l_module_source := G_MODULE_SOURCE || 'get_resp_app_id ';
1268
1269 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1270 then
1271 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1272 end if;
1273
1274 open cur_fnd_responsibility;
1275 fetch cur_fnd_responsibility into l_responsibility_id, l_application_id;
1276 l_found := cur_fnd_responsibility%found;
1277
1278 if (not l_found)
1279 then
1280 l_responsibility_id := null;
1281 l_application_id := null;
1282 end if;
1283 close cur_fnd_responsibility;
1284
1285 x_responsibility_id := l_responsibility_id;
1286 x_application_id := l_application_id;
1287
1288 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1289 then
1290 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1291 end if;
1292
1293 exception
1294 when others then
1295 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1296 then
1297 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1298 end if;
1299 raise;
1300 end get_resp_app_id;
1301 --
1302 -------------------------------------------------------------------------------
1303 procedure assign_default_resp(p_user_name in varchar2) is
1304
1305 l_module_source varchar2(256);
1306 l_apps_user_key fnd_oid_util.apps_user_key_type;
1307 l_found boolean := false;
1308
1309 l_responsibility_id fnd_responsibility.responsibility_id%type;
1310 l_application_id fnd_responsibility.application_id%type;
1311 l_resp_key fnd_responsibility.responsibility_key%type;
1312
1313 begin
1314 l_module_source := G_MODULE_SOURCE || 'assign_default_resp: ';
1315
1316 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1317 then
1318 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1319 end if;
1320
1321 l_apps_user_key := fnd_oid_util.get_fnd_user(p_user_name => p_user_name);
1322
1323 l_resp_key := 'PREFERENCES';
1324 get_resp_app_id(p_resp_key => l_resp_key
1325 , x_responsibility_id=>l_responsibility_id
1326 , x_application_id=>l_application_id
1327 );
1328
1329 l_found := fnd_user_resp_groups_api.assignment_exists(
1330 user_id => l_apps_user_key.user_id
1331 , responsibility_id => l_responsibility_id
1332 , responsibility_application_id => l_application_id
1333 , security_group_id => null);
1334
1335 if (not l_found)
1336 then
1337 fnd_user_resp_groups_api.insert_assignment(
1338 user_id => l_apps_user_key.user_id
1339 , responsibility_id => l_responsibility_id
1340 , responsibility_application_id => l_application_id
1341 , security_group_id => null
1342 , start_date => sysdate
1343 , end_date => null
1344 , description => 'Default Assignment for OID User'
1345 );
1346 end if;
1347
1348 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1349 then
1350 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1351 end if;
1352
1353 exception
1354 when others then
1355 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1356 then
1357 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1358 end if;
1359 end assign_default_resp;
1360 --
1361 -------------------------------------------------------------------------------
1362 /* This function assigns default "Preference SSWA" responsibility to any
1363 user created in OID or Ebiz */
1364
1365 function assign_def_resp(
1366 p_subscription_guid in raw
1367 , p_event in out nocopy wf_event_t)
1368 return varchar2 is
1369
1370 l_module_source varchar2(256);
1371 l_event_name varchar2(256);
1372 l_event_key varchar2(256);
1373 l_change_source varchar2(256);
1374 l_apps_user_key fnd_oid_util.apps_user_key_type;
1375 l_found boolean := false;
1376
1377 l_responsibility_id fnd_responsibility.responsibility_id%type;
1378 l_application_id fnd_responsibility.application_id%type;
1379 l_resp_key fnd_responsibility.responsibility_key%type;
1380
1381 begin
1382 l_module_source := G_MODULE_SOURCE || 'assign_def_resp: ';
1383
1384 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1385 then
1386 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1387 end if;
1388
1389 l_event_key := p_event.GetEventKey;
1390 l_event_name := WF_OID.SUBSCRIPTION_ADD;
1391 l_change_source := p_event.GetValueForParameter(
1392 fnd_oid_util.G_CHANGE_SOURCE);
1393
1394 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1395 then
1396 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
1397 , 'l_event_key = ' || l_event_key ||
1398 ', l_event_name = ' || l_event_name ||
1399 ', l_change_source = ' || l_change_source);
1400 end if;
1401
1402 l_apps_user_key := fnd_oid_util.get_fnd_user(p_user_name => l_event_key);
1403
1404 l_resp_key := 'PREFERENCES';
1405 get_resp_app_id(p_resp_key => l_resp_key
1406 , x_responsibility_id=>l_responsibility_id
1407 , x_application_id=>l_application_id
1408 );
1409
1410
1411 /* check whether the user is already assigned the responsibility */
1412
1413
1414 l_found := fnd_user_resp_groups_api.assignment_exists(
1415 user_id => l_apps_user_key.user_id
1416 , responsibility_id => l_responsibility_id
1417 , responsibility_application_id => l_application_id
1418 , security_group_id => null);
1419
1420 /* If user is not assigned the responsibility,assign the default responsibility */
1421
1422 if (not l_found)
1423 then
1424 fnd_user_resp_groups_api.insert_assignment(
1425 user_id => l_apps_user_key.user_id
1426 , responsibility_id => l_responsibility_id
1427 , responsibility_application_id => l_application_id
1428 , security_group_id => null
1429 , start_date => sysdate
1430 , end_date => null
1431 , description => 'Default Assignment for OID and Ebiz User'
1432 );
1433 end if;
1434
1435
1436 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1437 then
1438 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1439 end if;
1440
1441 return(wf_rule.default_rule(p_subscription_guid, p_event));
1442
1443 exception
1444 when others then
1445 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1446 then
1447 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1448 end if;
1449 return(wf_rule.error_rule(p_subscription_guid, p_event));
1450
1451 end assign_def_resp;
1452
1453 --
1454 ------------------------------------------------------------------------
1455 end fnd_oid_subscriptions;