[Home] [Help]
PACKAGE BODY: APPS.FND_OID_SUBSCRIPTIONS
Source
1 package body fnd_oid_subscriptions as
2 /* $Header: AFSCOSBB.pls 120.19 2007/01/03 21:49:39 rsantis noship $ */
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 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
432 then
433 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
434 end if;
435
436 return(wf_rule.default_rule(p_subscription_guid, p_event));
437
438 exception
439 when others then
440 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
441 then
442 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
443 end if;
444 return(wf_rule.error_rule(p_subscription_guid, p_event));
445
446 end identity_delete;
447 --
448 -------------------------------------------------------------------------------
449 /**
450 * This subscription handles the following events:
451 * OID -> User subscribing to an EBIZ instance
452 * EBIZ -> Linking of an FND_USER to OID user
453 * In both instances a SUBSCRIPTION_ADD event is raised. The change_source
454 * attribute is however different as below:
455 * OID -> change_source attribute is OID
456 * EBIZ -> change_source attribute is EBIZ
457 * Please make sure that the change_source is indeed EBIZ and not FND_USER
458 * when the event is raised by EBIZ.
459 */
460 function subscription_add(
461 p_subscription_guid in raw
462 , p_event in out nocopy wf_event_t)
463 return varchar2 is
464
465 l_module_source varchar2(256);
466 l_sso_user_profiles fnd_oid_util.apps_sso_user_profiles_type;
467 l_event_name varchar2(256);
468 l_event_key varchar2(256);
469 l_change_source varchar2(256);
470 l_user_id number;
471 l_orcl_guid fnd_user.user_guid%type;
472
473 begin
474 l_module_source := G_MODULE_SOURCE || 'subscription_add: ';
475
476 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
477 then
478 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
479 end if;
480 l_event_key := p_event.GetEventKey;
481 l_event_name := WF_OID.SUBSCRIPTION_ADD;
482
483 l_change_source := p_event.GetValueForParameter(
484 fnd_oid_util.G_CHANGE_SOURCE);
485
486 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
487 then
488 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
489 , 'l_event_key = ' || l_event_key ||
490 ', l_event_name = ' || l_event_name ||
491 ', l_change_source = ' || l_change_source);
492 end if;
493
494 if (l_change_source = fnd_oid_util.G_OID) then
495 l_orcl_guid := p_event.GetValueForParameter(
496 fnd_oid_util.G_ORCLGUID);
497 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
498 then
499 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'User_guid is:'
500 || l_orcl_guid || ', user_name is ' || l_event_key);
501 end if;
502 fnd_create_update(
503 p_wf_event => p_event
504 , p_event_type => wf_oid.SUBSCRIPTION_ADD
505 , p_user_name => l_event_key
506 , p_user_guid => l_orcl_guid
507 , x_user_id => l_user_id
508 );
509 end if;
510
511 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
512 then
513 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
514 end if;
515
516 return(wf_rule.default_rule(p_subscription_guid, p_event));
517
518 exception
519 when others then
520 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
521 then
522 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
523 end if;
524 return(wf_rule.error_rule(p_subscription_guid, p_event));
525
526 end subscription_add;
527 --
528 -------------------------------------------------------------------------------
529 /**
530 * This subscription handles the following events:
531 * OID -> User unsubscribing to an EBIZ instance
532 * EBIZ -> Uninking of an FND_USER with the OID user
533 * In both instances a SUBSCRIPTION_DELETE event is raises. The change_source
534 * attribute is however different as below:
535 * OID -> change_source attribute is OID
536 * EBIZ -> change_source attribute is EBIZ
537 * Please make sure that the change_source is indeed EBIZ and not FND_USER
538 * when the event is raised by EBIZ.
539 */
540 function subscription_delete(
541 p_subscription_guid in raw
542 , p_event in out nocopy wf_event_t)
543 return varchar2 is
544
545 l_module_source varchar2(256);
546 l_sso_user_profiles fnd_oid_util.apps_sso_user_profiles_type;
547 l_event_name varchar2(256);
548 l_event_key varchar2(256);
549 l_change_source varchar2(256);
550 l_user_id number;
551 l_orcl_guid fnd_user.user_guid%type;
552
553
554 begin
555 l_module_source := G_MODULE_SOURCE || 'subscription_delete: ';
556
557 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
558 then
559 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
560 end if;
561
562 l_event_key := p_event.GetEventKey;
563 l_event_name := WF_OID.SUBSCRIPTION_DELETE;
564 l_change_source := p_event.GetValueForParameter(
565 fnd_oid_util.G_CHANGE_SOURCE);
566
567 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
568 then
569 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
570 , 'l_event_key = ' || l_event_key ||
571 ', l_event_name = ' || l_event_name ||
572 ', l_change_source = ' || l_change_source);
573 end if;
574
575 l_sso_user_profiles := fnd_ldap_mapper.map_sso_user_profiles(l_event_key);
576
577 if (l_change_source = fnd_oid_util.G_EBIZ)
578 then
579 insert into wf_entity_changes(
580 entity_type, entity_key_value, flavor, change_date)
581 values(
582 wf_oid.SUBSCRIPTION_DELETE, l_event_key, l_change_source, sysdate);
583
584 else
585 --Rada, 01/31/2005
586 l_orcl_guid := p_event.GetValueForParameter(
587 fnd_oid_util.G_ORCLGUID);
588 /*fnd_create_update(
589 p_event_type => l_event_name
590 , p_user_name => p_event.GetEventKey
591 , p_user_guid => l_orcl_guid
592 , p_owner => fnd_oid_util.G_CUST
593 , p_unencrypted_password => null
594 , p_description => null
595 , p_email_address => null
596 , p_fax => null
597 , p_start_date => fnd_oid_util.G_NULL
598 , p_end_date => fnd_oid_util.G_NULL
599 , x_user_id => l_user_id);*/
600 fnd_create_update(
601 p_wf_event => p_event
602 , p_event_type => wf_oid.SUBSCRIPTION_DELETE
603 , p_user_name => l_event_key
604 , p_user_guid => l_orcl_guid
605 , x_user_id => l_user_id
606 );
607 end if;
608
609 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
610 then
611 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
612 end if;
613
614 return(wf_rule.default_rule(p_subscription_guid, p_event));
615
616 exception
617 when others then
618 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
619 then
620 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
621 end if;
622 return(wf_rule.error_rule(p_subscription_guid, p_event));
623 end subscription_delete;
624 -------------------------------------------------------------------------------
625 --
626 function synch_oid_to_tca(
627 p_subscription_guid in raw
628 , p_event in out nocopy wf_event_t)
629 return varchar2 is
630
631 cursor cur_attribute_cache(p_user_name in wf_attribute_cache.entity_key_value%type) is
632 select attribute_name
633 , attribute_value
634 from wf_attribute_cache
635 where entity_type = fnd_oid_util.G_USER
636 and entity_key_value = p_user_name
637 and attribute_name <> fnd_oid_util.G_CACHE_CHANGED;
638
639 l_module_source varchar2(256);
640 l_user_name fnd_user.user_name%type;
641 l_result pls_integer;
642
643 l_old_user_guid fnd_user.user_guid%type;
644 l_user_guid fnd_user.user_guid%type;
645 l_user_guid_changed boolean;
646
647 l_old_person_party_id fnd_user.person_party_id%type;
648 l_person_party_id fnd_user.person_party_id%type;
649 l_person_party_id_changed boolean;
650 l_apps_sso_link_truth_src varchar2(5);
651 l_profile_defined boolean;
652 begin
653 l_module_source := G_MODULE_SOURCE || ' synch_oid_to_tca: ';
654
655 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
656 then
657 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
658 end if;
659
660 -- Ignore TCA's own changes
661 -- bug 4411121
662 -- If the change source is HZ_PARTY. Further processing is stopped
663 -- This has to be addressed later. If this was HZ change we have to push it to OID.
664 if (p_event.GetValueForParameter('CHANGE_SOURCE') = 'HZ_PARTY') then
665
666 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
667 then
668 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'changeSrc is TCA no processing required');
669 end if;
670
671 return 'SUCCESS';
672 end if;
673
674
675 l_user_guid_changed := false;
676 l_person_party_id_changed := false;
677
678 l_user_name := p_event.getEventKey();
679
680 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
681 then
682 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
683 'USER_NAME: '||l_user_name);
684 end if;
685
686 for l_rec in cur_attribute_cache(l_user_name)
687 loop
688 if (l_rec.attribute_name = 'OLD_PERSON_PARTY_ID')
689 then
690 l_old_person_party_id := l_rec.attribute_value;
691
692 elsif(l_rec.attribute_name = 'OLD_ORCLGUID')
693 then
694 l_old_user_guid := l_rec.attribute_value;
695
696 elsif(l_rec.attribute_name = 'ORCLGUID')
697 then
698 l_user_guid := l_rec.attribute_value;
699
700 else
701 if(l_rec.attribute_name = 'PERSON_PARTY_ID')
702 then
703 l_person_party_id := l_rec.attribute_value;
704 end if;
705 end if;
706 end loop;
707
708 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
709 then
710 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
711 ' OLD_PERSON_PARTY_ID:: '||l_old_person_party_id||'::'||
712 ' PERSON_PARTY_ID:: '||l_person_party_id||'::'||
713 ' OLD_ORCLGUID::'||l_old_user_guid||'::'||
714 ' ORCLGUID::'||l_user_guid||'::');
715 end if;
716
717 --verify if the "*NULL*" case needs to be handled separately. !!scheruku
718
719 if(l_old_person_party_id IS NULL and l_person_party_id IS NOT NULL) or
720 (l_old_person_party_id <> l_person_party_id)
721 then
722 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
723 then
724 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
725 'person party id changed');
726 end if;
727 l_person_party_id_changed := true;
728 end if;
729
730 if(l_old_user_guid IS NULL and l_user_guid IS NOT NULL) or (l_old_user_guid <> l_user_guid)
731 then
732 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
733 then
734 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
735 'GUID changed');
736 end if;
737 l_user_guid_changed := true;
738 end if;
739
740
741 if(l_person_party_id_changed or l_user_guid_changed)
742 then
743 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
744 then
745 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
746 ' Either GUID or person party id has changed:');
747 end if;
748
749 fnd_profile.get_specific(
750 name_z => 'APPS_SSO_LINK_TRUTH_SRC',
751 val_z => l_apps_sso_link_truth_src,
752 defined_z => l_profile_defined);
753
754
755 if(l_apps_sso_link_truth_src is NULL or l_apps_sso_link_truth_src = fnd_oid_util.G_OID)
756 then
757 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
758 then
759 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
760 ' OID is source of Truth during linking ');
761 end if;
762 fnd_oid_util.synch_user_from_LDAP_NO_AUTO(p_user_name => l_user_name,
763 p_result => l_result);
764
765 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
766 then
767 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
768 ' After synch l_result: '||l_result);
769 end if;
770 else
771 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
772 then
773 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
774 ' Apps is source of Truth during linking ');
775 end if;
776 ---Fix me as and when available add code here to fetch attributes from TCA/HR and send to OID. !!scheruku
777 end if;
778 end if;
779
780
781 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
782 then
783 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
784 end if;
785
786 return(wf_rule.default_rule(p_subscription_guid, p_event));
787 exception
788 when others then
789 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
790 then
791 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
792 end if;
793 return(wf_rule.error_rule(p_subscription_guid, p_event));
794 end synch_oid_to_tca;
795
796 -------------------------------------------------------------------------------
797 --
798 function on_demand_user_create(
799 p_subscription_guid in raw
800 , p_event in out nocopy wf_event_t)
801 return varchar2 is
802
803 l_module_source varchar2(256);
804 l_user_name fnd_user.user_name%type;
805 l_user_guid fnd_user.user_guid%type;
806 l_result pls_integer;
807
808 begin
809 l_module_source := G_MODULE_SOURCE || ' on_demand_user_create: ';
810
811 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
812 then
813 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
814 end if;
815 l_user_name:= p_event.GetValueForParameter('USER_NAME');
816 l_user_guid:= p_event.GetValueForParameter('ORCLGUID');
817
818 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
819 then
820 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
821 'username: '||l_user_name||' user_guid: '||l_user_guid);
822 end if;
823
824 -- Replaced by subscribing assign_def_resp to "oracle.apps.fnd.ondemand.create" event
825 -- assign_default_resp(
826 -- p_user_name=>l_user_name
827 -- );
828
829 fnd_oid_util.synch_user_from_LDAP_NO_AUTO(p_user_name => l_user_name,
830 p_result => l_result);
831 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
832 then
833 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
834 ' After synch l_result: '||l_result);
835 end if;
836
837 return(wf_rule.default_rule(p_subscription_guid, p_event));
838
839 exception
840 when others then
841 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
842 then
843 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
844 end if;
845 return(wf_rule.error_rule(p_subscription_guid, p_event));
846
847 end on_demand_user_create;
848 -------------------------------------------------------------------------------
849 --
850 function event_error(
851 p_subscription_guid in raw
852 , p_event in out nocopy wf_event_t)
853 return varchar2 is
854
855 l_module_source varchar2(256);
856 l_event_id wf_entity_changes.entity_id%type;
857 l_user_name fnd_user.user_name%type;
858 l_user_guid fnd_user.user_guid%type;
859
860 begin
861 l_module_source := G_MODULE_SOURCE || 'event_error: ';
862
863 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
864 then
865 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
866 end if;
867 --RDESPOTO, 09/07/2004
868 --need constants for these in FND_OID_UTIL
869 l_event_id := p_event.GetValueForParameter('ENTITY_ID');
870 l_user_name:= p_event.GetValueForParameter('USER_NAME');
871 l_user_guid:= p_event.GetValueForParameter('ORCLGUID');
872
873
874 if(fnd_log.LEVEL_UNEXPECTED >=
875 fnd_log.G_CURRENT_RUNTIME_LEVEL) then
876
877 fnd_message.SET_NAME('FND', 'FND_SSO_EVENT_ERROR');
878 fnd_message.SET_TOKEN('USER_NAME', l_user_name);
879 fnd_message.SET_TOKEN('ENTITY_ID', l_event_id);
880 fnd_message.SET_TOKEN('ORCLGUID', l_user_guid);
881 fnd_log.MESSAGE(fnd_log.LEVEL_UNEXPECTED,
882 l_module_source, TRUE);
883 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,
884 'Synchronization of user definiton between E-Business Suite'||
885 ' and Oracle Internet Directory has failed for user:' || l_user_name ||
886 ', event id:' || l_event_id || ', guid:' || l_user_guid);
887 end if;
888
889 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
890 then
891 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
892 end if;
893
894 return(wf_rule.default_rule(p_subscription_guid, p_event));
895
896 exception
897 when others then
898 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
899 then
900 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
901 end if;
902 return(wf_rule.error_rule(p_subscription_guid, p_event));
903 end event_error;
904 --
905 -------------------------------------------------------------------------------
906 function event_resend(
907 p_subscription_guid in raw
908 , p_event in out nocopy wf_event_t)
909 return varchar2 is
910
911 l_module_source varchar2(256);
912 l_event_id wf_entity_changes.entity_id%type;
913 l_user_name fnd_user.user_name%type;
914 l_user_guid fnd_user.user_guid%type;
915
916 begin
917 l_module_source := G_MODULE_SOURCE || 'event_resend: ';
918
919 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
920 then
921 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
922 end if;
923 --RDESPOTO, 09/07/2004
924 --need constants for these in FND_OID_UTIL
925 l_event_id := p_event.GetValueForParameter('ENTITY_ID');
926 l_user_name:= p_event.GetValueForParameter('USER_NAME');
927 l_user_guid:= p_event.GetValueForParameter('ORCLGUID');
928
929
930 if(fnd_log.LEVEL_UNEXPECTED >=
931 fnd_log.G_CURRENT_RUNTIME_LEVEL) then
932
933 fnd_message.SET_NAME('FND', 'FND_SSO_EVENT_RESEND');
934 fnd_message.SET_TOKEN('USER_NAME', l_user_name);
935 fnd_message.SET_TOKEN('ENTITY_ID', l_event_id);
936 fnd_message.SET_TOKEN('ORCLGUID', l_user_guid);
937 fnd_log.MESSAGE(fnd_log.LEVEL_UNEXPECTED,
938 l_module_source, TRUE);
939 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,
940 'Synchronization event between E-Business Suite'
941 || ' and Oracle Internet Directory has to be resent for user '
942 || l_user_name || ', event id:' || l_event_id || ', guid:' || l_user_guid);
943 end if;
944
945 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
946 then
947 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
948 end if;
949
950 return(wf_rule.default_rule(p_subscription_guid, p_event));
951
952 exception
953 when others then
954 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
955 then
956 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
957 end if;
958 return(wf_rule.error_rule(p_subscription_guid, p_event));
959 end event_resend;
960 --
961 -------------------------------------------------------------------------------
962 function hz_identity_add(
963 p_subscription_guid in raw
964 , p_event in out nocopy wf_event_t)
965 return varchar2 is
966
967 l_module_source varchar2(256);
968 l_return_status varchar2(1);
969 l_change_source varchar2(256);
970
971 begin
972 l_module_source := G_MODULE_SOURCE || 'hz_identity_add: ';
973
974 if (fnd_log.LEVEL_EVENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
975 then
976 fnd_log.string(fnd_log.LEVEL_EVENT, l_module_source,
977 'EBIZ is NOW capable of understanding IDENTITY_ADD.');
978 end if;
979 --RDESPOTO, Add IDENTITY_ADD, 11/09/2004
980 l_change_source := p_event.GetValueForParameter(
981 fnd_oid_util.G_CHANGE_SOURCE);
982 --Change_source has to be OID
983 if (l_change_source = fnd_oid_util.G_OID) then
984 hz_create_update(
985 p_wf_event => p_event
986 , p_event_type => wf_oid.IDENTITY_ADD
987 , p_return_status => l_return_status);
988 --RDESPOTO, End IDENTITY_ADD
989 end if;
990
991 return(wf_rule.default_rule(p_subscription_guid, p_event));
992
993 exception
994 when others then
995 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
996 then
997 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
998 end if;
999 return(wf_rule.error_rule(p_subscription_guid, p_event));
1000 end hz_identity_add;
1001 --
1002 -------------------------------------------------------------------------------
1003 function hz_identity_modify(
1004 p_subscription_guid in raw
1005 , p_event in out nocopy wf_event_t)
1006 return varchar2 is
1007
1008 l_module_source varchar2(256);
1009 l_ldap_message fnd_oid_util.ldap_message_type;
1010 l_return_status varchar2(1);
1011 hz_failed_exp exception;
1012
1013 begin
1014 l_module_source := G_MODULE_SOURCE || 'hz_identity_modify: ';
1015
1016 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1017 then
1018 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1019 end if;
1020
1021 hz_create_update(
1022 p_wf_event => p_event
1023 , p_event_type => wf_oid.IDENTITY_MODIFY
1024 , p_return_status => l_return_status);
1025
1026 if (l_return_status <> fnd_api.G_RET_STS_SUCCESS)
1027 then
1028 raise hz_failed_exp;
1029 end if;
1030
1031 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1032 then
1033 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1034 end if;
1035
1036 return(wf_rule.default_rule(p_subscription_guid, p_event));
1037
1038 exception
1039 when hz_failed_exp then
1040 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1041 then
1042 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source,
1043 'hz_failed_exp: l_return_status = ' ||
1044 l_return_status);
1045 end if;
1046 return(wf_rule.error_rule(p_subscription_guid, p_event));
1047
1048 when others then
1049 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1050 then
1051 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1052 end if;
1053 return(wf_rule.error_rule(p_subscription_guid, p_event));
1054 end hz_identity_modify;
1055 --
1056 -------------------------------------------------------------------------------
1057 function hz_identity_delete(
1058 p_subscription_guid in raw
1059 , p_event in out nocopy wf_event_t)
1060 return varchar2 is
1061
1062 l_module_source varchar2(256);
1063
1064 begin
1065 l_module_source := G_MODULE_SOURCE || 'hz_identity_delete: ';
1066
1067 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1068 then
1069 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source,
1070 'This is a no-op for now. This subscription should be disabled. ' ||
1071 ' Please contact your System administrator to disable subscription');
1072 end if;
1073
1074 return(wf_rule.default_rule(p_subscription_guid, p_event));
1075
1076 exception
1077 when others then
1078 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1079 then
1080 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1081 end if;
1082 return(wf_rule.error_rule(p_subscription_guid, p_event));
1083 end hz_identity_delete;
1084 --
1085 -------------------------------------------------------------------------------
1086 function hz_subscription_add(
1087 p_subscription_guid in raw
1088 , p_event in out nocopy wf_event_t)
1089 return varchar2 is
1090
1091 l_module_source varchar2(256);
1092 l_ldap_message fnd_oid_util.ldap_message_type;
1093 l_return_status varchar2(1);
1094 hz_failed_exp exception;
1095
1096 begin
1097 l_module_source := G_MODULE_SOURCE || 'hz_subscription_add: ';
1098
1099 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1100 then
1101 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1102 end if;
1103
1104 hz_create_update(
1105 p_wf_event => p_event
1106 , p_event_type => wf_oid.SUBSCRIPTION_ADD
1107 , p_return_status => l_return_status);
1108
1109 if (l_return_status <> fnd_api.G_RET_STS_SUCCESS)
1110 then
1111 raise hz_failed_exp;
1112 end if;
1113
1114 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1115 then
1116 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1117 end if;
1118
1119 return(wf_rule.default_rule(p_subscription_guid, p_event));
1120
1121 exception
1122 when hz_failed_exp then
1123 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1124 then
1125 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source,
1126 'hz_failed_exp: l_return_status = ' ||
1127 l_return_status);
1128 end if;
1129 return(wf_rule.error_rule(p_subscription_guid, p_event));
1130
1131 when others then
1132 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1133 then
1134 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1135 end if;
1136 return(wf_rule.error_rule(p_subscription_guid, p_event));
1137 end hz_subscription_add;
1138 --
1139 -------------------------------------------------------------------------------
1140 function hz_subscription_delete(
1141 p_subscription_guid in raw
1142 , p_event in out nocopy wf_event_t)
1143 return varchar2 is
1144
1145 l_module_source varchar2(256);
1146
1147 begin
1148 l_module_source := G_MODULE_SOURCE || 'hz_subscription_delete: ';
1149
1150 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1151 then
1152 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source,
1153 'This is a no-op for now. This subscription should be disabled. ' ||
1154 ' Please contact your System administrator to disable subscription');
1155 end if;
1156
1157 return(wf_rule.default_rule(p_subscription_guid, p_event));
1158
1159 exception
1160 when others then
1161 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1162 then
1163 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1164 end if;
1165 return(wf_rule.error_rule(p_subscription_guid, p_event));
1166 end hz_subscription_delete;
1167 --
1168 -------------------------------------------------------------------------------
1169 procedure hz_create_update(
1170 p_wf_event in wf_event_t
1171 , p_event_type in varchar2
1172 , p_return_status out nocopy varchar2
1173 ) is
1174
1175 l_module_source varchar2(256);
1176 l_ldap_message fnd_oid_util.ldap_message_type;
1177 l_return_status varchar2(1);
1178 l_count number;
1179 l_event_key varchar2(256);
1180
1181 begin
1182 l_module_source := G_MODULE_SOURCE || 'hz_create_update: ';
1183
1184 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1185 then
1186 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1187 end if;
1188
1189 fnd_ldap_mapper.map_ldap_message(p_wf_event, p_event_type, l_ldap_message);
1190
1191 if (fnd_oid_util.person_party_exists(p_wf_event.GetEventKey))
1192 then
1193 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1194 then
1195 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
1196 'Person Party exists in FND_USER');
1197 end if;
1198 fnd_oid_users.hz_update(
1199 p_ldap_message => l_ldap_message
1200 , x_return_status => p_return_status);
1201 else
1202 l_event_key := p_wf_event.GetEventKey;
1203 select count(*) into l_count
1204 from fnd_user
1205 where user_name = l_event_key
1206 and user_guid is not null;
1207
1208 if (l_count > 0) then
1209 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1210 then
1211 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
1212 'Person Party does NOT exist in FND_USER, creating a new TCA entry');
1213 end if;
1214 fnd_oid_users.hz_create(
1215 p_ldap_message => l_ldap_message
1216 , x_return_status => p_return_status);
1217 else
1218 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1219 then
1220 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,
1221 'FND User is not linked to OID user, therefore not creating TCA party');
1222 end if;
1223 end if;
1224
1225 end if;
1226
1227 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1228 then
1229 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1230 end if;
1231
1232 exception
1233 when others then
1234 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1235 then
1236 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1237 end if;
1238 end hz_create_update;
1239 --
1240 -------------------------------------------------------------------------------
1241 procedure get_resp_app_id(p_resp_key in fnd_responsibility.responsibility_key%type
1242 , x_responsibility_id out nocopy fnd_responsibility.responsibility_id%type
1243 , x_application_id out nocopy fnd_responsibility.application_id%type) is
1244
1245 l_module_source varchar2(256);
1246 l_responsibility_id fnd_responsibility.responsibility_id%type;
1247 l_application_id fnd_responsibility.application_id%type;
1248 l_found boolean;
1249
1250 cursor cur_fnd_responsibility is
1251 SELECT responsibility_id, application_id
1252 from fnd_responsibility
1253 where RESPONSIBILITY_KEY = p_resp_key;
1254 begin
1255 l_module_source := G_MODULE_SOURCE || 'get_resp_app_id ';
1256
1257 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1258 then
1259 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1260 end if;
1261
1262 open cur_fnd_responsibility;
1263 fetch cur_fnd_responsibility into l_responsibility_id, l_application_id;
1264 l_found := cur_fnd_responsibility%found;
1265
1266 if (not l_found)
1267 then
1268 l_responsibility_id := null;
1269 l_application_id := null;
1270 end if;
1271 close cur_fnd_responsibility;
1272
1273 x_responsibility_id := l_responsibility_id;
1274 x_application_id := l_application_id;
1275
1276 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1277 then
1278 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1279 end if;
1280
1281 exception
1282 when others then
1283 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1284 then
1285 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1286 end if;
1287 raise;
1288 end get_resp_app_id;
1289 --
1290 -------------------------------------------------------------------------------
1291 procedure assign_default_resp(p_user_name in varchar2) is
1292
1293 l_module_source varchar2(256);
1294 l_apps_user_key fnd_oid_util.apps_user_key_type;
1295 l_found boolean := false;
1296
1297 l_responsibility_id fnd_responsibility.responsibility_id%type;
1298 l_application_id fnd_responsibility.application_id%type;
1299 l_resp_key fnd_responsibility.responsibility_key%type;
1300
1301 begin
1302 l_module_source := G_MODULE_SOURCE || 'assign_default_resp: ';
1303
1304 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1305 then
1306 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1307 end if;
1308
1309 l_apps_user_key := fnd_oid_util.get_fnd_user(p_user_name => p_user_name);
1310
1311 l_resp_key := 'PREFERENCES';
1312 get_resp_app_id(p_resp_key => l_resp_key
1313 , x_responsibility_id=>l_responsibility_id
1314 , x_application_id=>l_application_id
1315 );
1316
1317 l_found := fnd_user_resp_groups_api.assignment_exists(
1318 user_id => l_apps_user_key.user_id
1319 , responsibility_id => l_responsibility_id
1320 , responsibility_application_id => l_application_id
1321 , security_group_id => null);
1322
1323 if (not l_found)
1324 then
1325 fnd_user_resp_groups_api.insert_assignment(
1326 user_id => l_apps_user_key.user_id
1327 , responsibility_id => l_responsibility_id
1328 , responsibility_application_id => l_application_id
1329 , security_group_id => null
1330 , start_date => sysdate
1331 , end_date => null
1332 , description => 'Default Assignment for OID User'
1333 );
1334 end if;
1335
1336 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1337 then
1338 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1339 end if;
1340
1341 exception
1342 when others then
1343 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1344 then
1345 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1346 end if;
1347 end assign_default_resp;
1348 --
1349 -------------------------------------------------------------------------------
1350 /* This function assigns default "Preference SSWA" responsibility to any
1351 user created in OID or Ebiz */
1352
1353 function assign_def_resp(
1354 p_subscription_guid in raw
1355 , p_event in out nocopy wf_event_t)
1356 return varchar2 is
1357
1358 l_module_source varchar2(256);
1359 l_event_name varchar2(256);
1360 l_event_key varchar2(256);
1361 l_change_source varchar2(256);
1362 l_apps_user_key fnd_oid_util.apps_user_key_type;
1363 l_found boolean := false;
1364
1365 l_responsibility_id fnd_responsibility.responsibility_id%type;
1366 l_application_id fnd_responsibility.application_id%type;
1367 l_resp_key fnd_responsibility.responsibility_key%type;
1368
1369 begin
1370 l_module_source := G_MODULE_SOURCE || 'assign_def_resp: ';
1371
1372 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1373 then
1374 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1375 end if;
1376
1377 l_event_key := p_event.GetEventKey;
1378 l_event_name := WF_OID.SUBSCRIPTION_ADD;
1379 l_change_source := p_event.GetValueForParameter(
1380 fnd_oid_util.G_CHANGE_SOURCE);
1381
1382 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1383 then
1384 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source
1385 , 'l_event_key = ' || l_event_key ||
1386 ', l_event_name = ' || l_event_name ||
1387 ', l_change_source = ' || l_change_source);
1388 end if;
1389
1390 l_apps_user_key := fnd_oid_util.get_fnd_user(p_user_name => l_event_key);
1391
1392 l_resp_key := 'PREFERENCES';
1393 get_resp_app_id(p_resp_key => l_resp_key
1394 , x_responsibility_id=>l_responsibility_id
1395 , x_application_id=>l_application_id
1396 );
1397
1398
1399 /* check whether the user is already assigned the responsibility */
1400
1401
1402 l_found := fnd_user_resp_groups_api.assignment_exists(
1403 user_id => l_apps_user_key.user_id
1404 , responsibility_id => l_responsibility_id
1405 , responsibility_application_id => l_application_id
1406 , security_group_id => null);
1407
1408 /* If user is not assigned the responsibility,assign the default responsibility */
1409
1410 if (not l_found)
1411 then
1412 fnd_user_resp_groups_api.insert_assignment(
1413 user_id => l_apps_user_key.user_id
1414 , responsibility_id => l_responsibility_id
1415 , responsibility_application_id => l_application_id
1416 , security_group_id => null
1417 , start_date => sysdate
1418 , end_date => null
1419 , description => 'Default Assignment for OID and Ebiz User'
1420 );
1421 end if;
1422
1423
1424 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1425 then
1426 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1427 end if;
1428
1429 return(wf_rule.default_rule(p_subscription_guid, p_event));
1430
1431 exception
1432 when others then
1433 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1434 then
1435 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1436 end if;
1437 return(wf_rule.error_rule(p_subscription_guid, p_event));
1438
1439 end assign_def_resp;
1440
1441 --
1442 ------------------------------------------------------------------------
1443 end fnd_oid_subscriptions;