[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_EVENT
Source
1 PACKAGE BODY MSD_DEM_EVENT AS
2 /* $Header: msddemevntb.pls 120.2.12010000.3 2008/09/05 09:03:13 nallkuma ship $ */
3
4
5 /* Private Function - Added for creating the SOP component user in Demantra */
6 FUNCTION SOP_USER_CHANGE (
7 p_subscription_guid in raw,
8 p_event in out nocopy wf_event_t)
9 RETURN VARCHAR2;
10
11 PROCEDURE log_debug (p_msg VARCHAR2)
12 IS
13 BEGIN
14 IF (fnd_profile.value ('MSD_DEM_DEBUG_MODE') = 'Y' OR 1=1)
15 THEN
16 RETURN;
17 END IF;
18
19 RETURN;
20
21 EXCEPTION
22 WHEN OTHERS THEN
23 RETURN;
24 END;
25
26
27 FUNCTION USER_CHANGE (
28 p_subscription_guid in raw,
29 p_event in out nocopy wf_event_t)
30 RETURN VARCHAR2
31 IS
32 eventName varchar2(100);
33 key varchar2(401);
34 userQuery varchar(10000);
35 DuserQuery varchar(1000);
36 DOlduserQuery varchar(1000);
37 p_user_name fnd_user.user_name%type;
38 p_user_pwd varchar2(10) := dbms_random.string('A', 10);
39 p_user_valid number;
40 p_user_resp number;
41 p_user_fname per_all_people_f.first_name%type;
42 p_user_lname per_all_people_f.last_name%type;
43 p_user_org_name hr_all_organization_units.name%type;
44 p_user_wrkphone per_all_people_f.work_telephone%type;
45 p_user_fax fnd_user.fax%type;
46 p_user_email fnd_user.email_address%type;
47 p_user_product number;
48 Duser_cnt number;
49 DOlduser_cnt number;
50 userid varchar2 (400);
51
52 x_schema VARCHAR2(30) := NULL;
53 x_user_permission VARCHAR2(10) := NULL;
54 x_component_name VARCHAR2(100) := NULL;
55
56 x_create_user_sql VARCHAR2(2000) := NULL;
57 x_update_user_sql VARCHAR2(2000) := NULL;
58 x_drop_user_sql VARCHAR2(2000) := NULL;
59 x_drop_old_user_sql VARCHAR2(2000) := NULL;
60
61 x_old_name VARCHAR2(320) := NULL;
62 x_curr_name VARCHAR2(320) := NULL;
63
64 x_ret_val VARCHAR2(255) := NULL;
65
66 x_table_name VARCHAR2(50) := NULL;
67
68 CURSOR c_is_mdp_matrix_present (p_schema_name VARCHAR2)
69 IS
70 SELECT table_name
71 FROM all_tables
72 WHERE owner = upper(p_schema_name)
73 AND table_name = 'MDP_MATRIX';
74
75 CURSOR c_get_user_id (p_user_name varchar2)
76 IS
77 SELECT to_char(user_id)
78 FROM fnd_user
79 WHERE user_name = p_user_name;
80
81 BEGIN
82
83 --Bug#7140524
84 /* Check if Demantra is installed before proceeding further */
85 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
86
87 log_debug ('Schema: ' || x_schema);
88
89 IF (x_schema IS NULL)
90 THEN
91 log ('msd_dem_event.user_change - Profile MSD_DEM_SCHEMA is not set.');
92 log_debug ('msd_dem_event.user_change - Profile MSD_DEM_SCHEMA is not set.');
93 RETURN SUCCESS;
94 ELSE
95 OPEN c_is_mdp_matrix_present (x_schema);
96 FETCH c_is_mdp_matrix_present INTO x_table_name;
97 CLOSE c_is_mdp_matrix_present;
98
99 IF (x_table_name IS NULL)
100 THEN
101 log ('msd_dem_event.user_change - Profile MSD_DEM_SCHEMA is incorrectly set or Demantra schema is not installed.');
102 log_debug ('msd_dem_event.user_change - Profile MSD_DEM_SCHEMA is incorrectly set or Demantra schema is not installed.');
103 RETURN SUCCESS;
104 END IF;
105 END IF;
106 --Bug#7140524
107
108 /* Create the SOP component user for Demantra */
109 x_ret_val := sop_user_change (p_subscription_guid, p_event);
110 IF x_ret_val <> SUCCESS
111 THEN
112 log ('Creation of SOP component user failed.');
113 log_debug ('Creation of SOP component user failed.');
114 END IF;
115
116 log('start');
117 log_debug('start');
118
119 eventName := p_event.getEventName();
120 key := p_event.getEventKey();
121
122 log_debug ('Event Name: ' || eventName);
123 log_debug ('Key: ' || key);
124
125 /*** BEGIN - Get the user name from the event ***/
126 If ( eventName = 'oracle.apps.fnd.wf.ds.userRole.created'
127 OR eventName = 'oracle.apps.fnd.wf.ds.userRole.updated'
128 OR eventName = 'oracle.apps.fnd.user.delete'
129 OR eventName = 'oracle.apps.fnd.wf.ds.user.updated'
130 OR eventName = 'oracle.apps.fnd.wf.ds.user.nameChanged')
131 THEN
132 x_curr_name := p_event.getValueForParameter('USER_NAME');
133 log_debug ('Current User Name: ' || x_curr_name);
134
135 IF (eventName = 'oracle.apps.fnd.wf.ds.user.nameChanged')
136 THEN
137 x_old_name := p_event.getValueForParameter('OLD_USER_NAME');
138 log_debug ('Old User Name: ' || x_old_name);
139 END IF;
140
141 ELSE
142 log_debug('Exiting msd_dem_event.user_change: Unknown Event');
143 RETURN SUCCESS;
144 END IF;
145
146 /*** END- Get the user name from the event ***/
147
148 /* Get the user id from the user name */
149 OPEN c_get_user_id (x_curr_name);
150 FETCH c_get_user_id INTO userid;
151 CLOSE c_get_user_id;
152
153 log_debug ('User Id: ' || userid);
154
155 IF (userid IS NULL)
156 THEN
157 log ('Unable to get user id for user name = ' || x_curr_name);
158 log_debug ('Unable to get user id for user name = ' || x_curr_name);
159 --Bug#7140524 : returning success in case user id not found, so that workflow calling this method will proceed without erroring out
160 return SUCCESS;
161 /*return ( handleError ( PKG_NAME
162 , 'msd_dem_event.user_change'
163 , p_event
164 , p_subscription_guid
165 , ERROR
166 ));*/
167 --Bug#7140524
168 END IF;
169
170 userQuery :=
171 ' select fu.user_name, decode(sign(fu.end_date - sysdate),-1,2,1),
172 (SELECT sum(a) FROM (SELECT 1 a
173 FROM dual
174 WHERE EXISTS ( SELECT 1
175 FROM fnd_user_resp_groups_all fug,
176 fnd_responsibility fr,
177 fnd_menu_entries fme,
178 fnd_form_functions fff
179 WHERE
180 fug.user_id = :user_id1
181 AND fug.responsibility_application_id = 722
182 AND decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
183 AND fr.application_id = 722
184 AND fr.responsibility_id = fug.responsibility_id
185 AND fme.menu_id = fr.menu_id
186 AND fme.grant_flag = ''Y''
187 AND fme.sub_menu_id IS NULL
188 AND fff.function_id = fme.function_id
189 AND fff.function_name = ''MSD_DEM_DEMPLANR'')
190 UNION ALL
191 SELECT 2 a
192 FROM dual
193 WHERE EXISTS ( SELECT 1
194 FROM fnd_user_resp_groups_all fug,
195 fnd_responsibility fr,
196 fnd_menu_entries fme,
197 fnd_form_functions fff
198 WHERE
199 fug.user_id = :user_id2
200 AND fug.responsibility_application_id = 722
201 AND decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
202 AND fr.application_id = 722
203 AND fr.responsibility_id = fug.responsibility_id
204 AND fme.menu_id = fr.menu_id
205 AND fme.grant_flag = ''Y''
206 AND fme.sub_menu_id IS NULL
207 AND fff.function_id = fme.function_id
208 AND fff.function_name = ''MSD_DEM_WF_MGR''))) user_type,
209 ( select first_name
210 from per_all_people_f
211 where ((person_id = fu.employee_id
212 and fu.employee_id is not null)
213 or
214 (party_id = fu.person_party_id
215 and fu.person_party_id is not null)
216 or (party_id = fu.supplier_id
217 and fu.supplier_id is not null)
218 or (party_id = fu.customer_id
219 and fu.customer_id is not null))
220 and rownum <2) first_name,
221 ( select last_name
222 from per_all_people_f
223 where ((person_id = fu.employee_id
224 and fu.employee_id is not null)
225 or
226 (party_id = fu.person_party_id
227 and fu.person_party_id is not null)
228 or (party_id = fu.supplier_id
229 and fu.supplier_id is not null)
230 or (party_id = fu.customer_id
231 and fu.customer_id is not null))
232 and rownum <2) last_name,
233 ( select name
234 from hr_all_organization_units
235 where business_group_id in
236 (select pap.business_group_id
237 from per_all_people_f pap
238 where (pap.person_id = fu.employee_id
239 and fu.employee_id is not null)
240 or
241 (pap.party_id = fu.person_party_id
242 and fu.person_party_id is not null)
243 or (pap.party_id = fu.supplier_id
244 and fu.supplier_id is not null)
245 or (pap.party_id = fu.customer_id
246 and fu.customer_id is not null))
247 and rownum < 2) company,
248 ( select work_telephone
249 from per_all_people_f
250 where ((person_id = fu.employee_id
251 and fu.employee_id is not null)
252 or
253 (party_id = fu.person_party_id
254 and fu.person_party_id is not null)
255 or (party_id = fu.supplier_id
256 and fu.supplier_id is not null)
257 or (party_id = fu.customer_id
258 and fu.customer_id is not null))
259 and rownum <2) phone_num,
260 fu.fax,
261 decode(fu.email_address,
262 null,
263 (select pap.email_address
264 from per_all_people_f pap
265 where ((person_id = fu.employee_id
266 and fu.employee_id is not null)
267 or
268 (party_id = fu.person_party_id
269 and fu.person_party_id is not null)
270 or (party_id = fu.supplier_id
271 and fu.supplier_id is not null)
272 or (party_id = fu.customer_id
273 and fu.customer_id is not null))
274 and pap.email_address is not null
275 and rownum <2),
276 fu.email_address) email_address,
277 1 product
278 from fnd_user fu
279 where fu.user_id = :userid3';
280
281 IF (eventName = 'oracle.apps.fnd.user.delete')
282 THEN
283 p_user_name := x_curr_name;
284 ELSE
285
286 begin
287 execute immediate userQuery
288 into p_user_name,p_user_valid,p_user_resp,p_user_fname,p_user_lname,p_user_org_name,
289 p_user_wrkphone,p_user_fax,p_user_email,p_user_product
290 using userid, userid, userid;
291 exception
292 when others then
293 log_debug (substr(SQLERRM,1,150));
294 log ('Query on user_id ' || to_char(userid) || ' failed. Please ignore if not an Demand Management User.');
295 return ( handleError ( PKG_NAME
296 , 'msd_dem_event.user_change'
297 , p_event
298 , p_subscription_guid
299 , ERROR
300 ));
301 end;
302 END IF;
303
304
305 /* Get the user type */
306 IF (eventName = 'oracle.apps.fnd.user.delete')
307 THEN
308 NULL;
309 ELSIF (p_user_resp = 1)
310 THEN
311 x_user_permission := 'DP';
312 ELSIF (p_user_resp = 3)
313 THEN
314 x_user_permission := 'DPA';
315 ELSIF ( eventName = 'oracle.apps.fnd.wf.ds.userRole.updated'
316 OR eventName = 'oracle.apps.fnd.wf.ds.user.updated')
317 THEN
318 eventName := 'oracle.apps.fnd.user.resp.delete';
319 ELSE
320 log ('Not a DM User');
321 RETURN SUCCESS;
322 END IF;
323
324 /* Get the Demand Management Component */
325 SELECT meaning
326 INTO x_component_name
327 FROM fnd_lookup_values_vl
328 WHERE lookup_type = 'MSD_DEM_COMPONENTS'
329 AND lookup_code = 'DEMAND_MANAGEMENT';
330
331 log_debug ('Component: ' || x_component_name);
332
333 /* CREATE USER */
334 x_create_user_sql := 'BEGIN ' || x_schema || '.API_CREATE_ORA_DEM_USER ( ' ||
335 ' ''' || p_user_name || ''' , ' ||
336 ' ''' || p_user_pwd || ''' , ' ||
337 ' ''' || x_user_permission || ''' , ' ||
338 ' ''' || p_user_fname || ''' , ' ||
339 ' ''' || p_user_lname || ''' , ' ||
340 ' ''' || p_user_org_name || ''' , ' ||
341 ' ''' || p_user_wrkphone || ''' , ' ||
342 ' ''' || p_user_fax || ''' , ' ||
343 ' ''' || p_user_email || ''' , ' ||
344 ' ''0'' , ' ||
345 ' null, ' ||
346 ' ''' || x_component_name || ''' , ' ||
347 ' null, ' ||
348 ' ''ADD''); END;';
349
350 /* UPDATE USER */
351 x_update_user_sql := 'BEGIN ' || x_schema || '.API_CREATE_ORA_DEM_USER ( ' ||
352 ' ''' || p_user_name || ''' , ' ||
356 ' ''' || p_user_lname || ''' , ' ||
353 ' ''' || p_user_pwd || ''' , ' ||
354 ' ''' || x_user_permission || ''' , ' ||
355 ' ''' || p_user_fname || ''' , ' ||
357 ' ''' || p_user_org_name || ''' , ' ||
358 ' ''' || p_user_wrkphone || ''' , ' ||
359 ' ''' || p_user_fax || ''' , ' ||
360 ' ''' || p_user_email || ''' , ' ||
361 ' ''0'' , ' ||
362 ' null, ' ||
363 ' ''' || x_component_name || ''' , ' ||
364 ' null, ' ||
365 ' ''UPDATE''); END;';
366
367 /* DROP USER */
368 x_drop_user_sql := 'BEGIN ' || x_schema || '.API_DROP_ORA_DEM_USER ( ''' || p_user_name || ''' ); END;';
369 x_drop_old_user_sql := 'BEGIN ' || x_schema || '.API_DROP_ORA_DEM_USER ( ''' || x_old_name || ''' ); END;';
370
371 /* USER EXISTS IN DEMANTRA */
372 DuserQuery := 'select count(user_name) from ' || x_schema || '.user_id where user_name = '''||p_user_name||'''';
373 log_debug ('User Query - ' || DuserQuery);
374
375 execute immediate DuserQuery Into Duser_cnt;
376 log_debug('User count - ' || to_char (Duser_cnt) );
377
378 /* OLD USER EXISTS IN DEMANTRA */
379 DOlduserQuery := 'select count(user_name) from ' || x_schema || '.user_id where user_name = '''||x_old_name||'''';
380 log_debug ('Old User Query - ' || DOlduserQuery);
381
382 log_debug ('Start processing the event - ' || eventName);
383
384 /* insert/Update responsibility for user */
385 If ( eventName = 'oracle.apps.fnd.wf.ds.userRole.created'
386 OR eventName = 'oracle.apps.fnd.wf.ds.userRole.updated') THEN
387
388 /* User does not exist in Demantra... Add the user */
389 If Duser_cnt = 0 Then
390
391 /* invoke API_CREATE_ORA_DEM_USER.(ADD) */
392 log_debug ('Insert/Update Responsibility - Creating User');
393 log_debug (x_create_user_sql);
394
395 EXECUTE IMMEDIATE x_create_user_sql;
396
397 /* User exists in Demantra, Update the responsibility */
398 Elsif Duser_cnt > 0 Then
399
400 /* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
401 log_debug ('Insert/Update Responsibility - Updating User');
402 log_debug (x_update_user_sql);
403
404 EXECUTE IMMEDIATE x_update_user_sql;
405
406 End if;
407
408 /* Update Existing User */
409 Elsif (eventName = 'oracle.apps.fnd.wf.ds.user.updated') THEN
410
411 /* Effective date has been disabled OR DM responsibilities have been disabled for user....delete user */
412 If (p_user_valid = 2 OR p_user_resp = 0) Then
413
414 /* invoke API_DROP_ORA_DEM_USER(user name) */
415 log_debug ('User Update - Deleting User');
416 log_debug (x_drop_user_sql);
417
418 EXECUTE IMMEDIATE x_drop_user_sql;
419
420 Else
421
422 /* User does not exist in Demantra but effective date is enabled....Add user */
423 If (Duser_cnt = 0) Then
424
425 /* invoke API_CREATE_ORA_DEM_USER.(ADD) */
426 log_debug ('User Update - Creating User');
427 log_debug (x_create_user_sql);
428
429 EXECUTE IMMEDIATE x_create_user_sql;
430
431 /* User exists in Demantra and effective date is enabled....Update user */
432 Else
433
434 /* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
435 log_debug('User Update - Updating User');
436 log_debug (x_update_user_sql);
437
438 EXECUTE IMMEDIATE x_update_user_sql;
439
440 End If;
441 End If;
442
443 /* Delete existing User */
444 Elsif (eventName = 'oracle.apps.fnd.user.delete') THEN
445
446 /* invoke API_DROP_ORA_DEM_USER(user name) */
447 log_debug('User Delete - Deleting User');
448 log_debug(x_drop_user_sql);
449
450 EXECUTE IMMEDIATE x_drop_user_sql;
451
452 /* Delete responsibility */
453 Elsif (eventName = 'oracle.apps.fnd.user.resp.delete') THEN
454
455 /* User exists in Demantra */
456 If (Duser_cnt > 0 AND x_user_permission IS NOT NULL) Then
457
458 /* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
459 log_debug ('Responsibility Delete - Updating user');
460 log_debug (x_update_user_sql);
461
462 EXECUTE IMMEDIATE x_update_user_sql;
463 Elsif (Duser_cnt > 0)
464 THEN
465 /* invoke API_DROP_ORA_DEM_USER(user name) */
466 log_debug('Responsibility Delete - Deleting user');
467 log_debug(x_drop_user_sql);
471
468
469 EXECUTE IMMEDIATE x_drop_user_sql;
470 End If;
472 Elsif (eventName = 'oracle.apps.fnd.wf.ds.user.nameChanged') Then
473
474 execute immediate DOlduserQuery Into DOlduser_cnt;
475 log_debug('Old User count - ' || to_char (DOlduser_cnt) );
476
477 /* Old User exists in Demantra */
478 IF (DOlduser_cnt > 0)
479 THEN
480
481 /* invoke API_DROP_ORA_DEM_USER(user name) */
482 log_debug('Drop User with Old Name');
483 log_debug(x_drop_old_user_sql);
484
485 EXECUTE IMMEDIATE x_drop_old_user_sql;
486 END IF;
487
488 IF (p_user_valid = 1)
489 THEN
490
491 /* invoke API_CREATE_ORA_DEM_USER.(ADD) */
492 log_debug ('Add User with New Name');
493 log_debug (x_create_user_sql);
494
495 EXECUTE IMMEDIATE x_create_user_sql;
496 END IF;
497
498 End if;
499
500 log('end');
501 RETURN SUCCESS;
502
503
504 EXCEPTION
505 WHEN NO_DATA_FOUND THEN
506 log_debug ('excep no data');
507 LOG('No rows returned');
508 return ( handleError ( PKG_NAME
509 , 'msd_dem_event.user_change'
510 , p_event
511 , p_subscription_guid
512 , ERROR
513 ));
514 WHEN OTHERS THEN
515 return ( handleError ( PKG_NAME
516 , 'msd_dem_event.user_change'
517 , p_event
518 , p_subscription_guid
519 , ERROR
520 ));
521
522 END USER_CHANGE;
523
524
525
526
527 function handleError ( p_pkg_name in varchar2
528 , p_function_name in varchar2
529 , p_event in out nocopy wf_event_t
530 , p_subscription_guid in raw
531 , p_error_type in varchar2
532 ) return varchar2 is
533
534 l_error_type varchar2(100);
535
536 begin
537 if p_error_type in (ERROR,WARNING) then
538 l_error_type := p_error_type;
539 else
540 l_error_type := p_error_type;
541 end if;
542 if l_error_type = WARNING then
543 wf_core.context ( p_pkg_name
544 , p_function_name
545 , p_event.getEventName()
546 , p_subscription_guid
547 );
548 wf_event.setErrorInfo (p_event, WARNING);
549 return WARNING;
550 else
551 wf_core.context ( p_pkg_name
552 , p_function_name
553 , p_event.getEventName()
554 , p_subscription_guid
555 );
556 wf_event.setErrorInfo (p_event, ERROR);
557 return ERROR;
558 end if;
559 end handleError;
560
561
562 procedure log (msg in varchar2) is
563 begin
564 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) then
565 fnd_log.string ( fnd_log.level_statement
566 , PKG_NAME
567 , msg
568 );
569 end if;
570 end log;
571
572
573 FUNCTION SOP_USER_CHANGE (
574 p_subscription_guid in raw,
575 p_event in out nocopy wf_event_t)
576 RETURN VARCHAR2
577 IS
578 eventName varchar2(100);
579 key varchar2(401);
580 userQuery varchar(10000);
581 DuserQuery varchar(1000);
582 DOlduserQuery varchar(1000);
583 p_user_name fnd_user.user_name%type;
584 p_user_pwd varchar2(10) := dbms_random.string('A', 10);
585 p_user_valid number;
586 p_user_resp number;
587 p_user_fname per_all_people_f.first_name%type;
588 p_user_lname per_all_people_f.last_name%type;
589 p_user_org_name hr_all_organization_units.name%type;
590 p_user_wrkphone per_all_people_f.work_telephone%type;
591 p_user_fax fnd_user.fax%type;
592 p_user_email fnd_user.email_address%type;
593 p_user_product number;
594 Duser_cnt number;
595 DOlduser_cnt number;
596 userid varchar2 (400);
597
598 x_schema VARCHAR2(30) := NULL;
599 x_user_permission VARCHAR2(10) := NULL;
600 x_component_name VARCHAR2(100) := NULL;
601
602 x_create_user_sql VARCHAR2(2000) := NULL;
603 x_update_user_sql VARCHAR2(2000) := NULL;
604 x_drop_user_sql VARCHAR2(2000) := NULL;
605 x_drop_old_user_sql VARCHAR2(2000) := NULL;
606
607 x_old_name VARCHAR2(320) := NULL;
608 x_curr_name VARCHAR2(320) := NULL;
609
610 CURSOR c_get_user_id (p_user_name varchar2)
611 IS
612 SELECT to_char(user_id)
613 FROM fnd_user
614 WHERE user_name = p_user_name;
615
616
617 BEGIN
618
619 log('start');
620 log_debug('start');
621
625 log_debug ('Event Name: ' || eventName);
622 eventName := p_event.getEventName();
623 key := p_event.getEventKey();
624
626 log_debug ('Key: ' || key);
627
628 /*** BEGIN - Get the user name from the event ***/
629 If ( eventName = 'oracle.apps.fnd.wf.ds.userRole.created'
630 OR eventName = 'oracle.apps.fnd.wf.ds.userRole.updated'
631 OR eventName = 'oracle.apps.fnd.user.delete'
632 OR eventName = 'oracle.apps.fnd.wf.ds.user.updated'
633 OR eventName = 'oracle.apps.fnd.wf.ds.user.nameChanged')
634 THEN
635 x_curr_name := p_event.getValueForParameter('USER_NAME');
636 log_debug ('Current User Name: ' || x_curr_name);
637
638 IF (eventName = 'oracle.apps.fnd.wf.ds.user.nameChanged')
639 THEN
640 x_old_name := p_event.getValueForParameter('OLD_USER_NAME');
641 log_debug ('Old User Name: ' || x_old_name);
642 END IF;
643
644 ELSE
645 log_debug('Exiting msd_dem_event.user_change: Unknown Event');
646 RETURN SUCCESS;
647 END IF;
648
649 /*** END- Get the user name from the event ***/
650
651 /* Get the user id from the user name */
652 OPEN c_get_user_id (x_curr_name);
653 FETCH c_get_user_id INTO userid;
654 CLOSE c_get_user_id;
655
656 log_debug ('User Id: ' || userid);
657
658 IF (userid IS NULL)
659 THEN
660 log ('Unable to get user id for user name = ' || x_curr_name);
661 log_debug ('Unable to get user id for user name = ' || x_curr_name);
662 --Bug#7140524 : returning success in case user id not found, so that workflow calling this method will proceed without erroring out
663 return SUCCESS;
664 /*return ( handleError ( PKG_NAME
665 , 'msd_dem_event.user_change'
666 , p_event
667 , p_subscription_guid
668 , ERROR
669 ));*/
670 --Bug#7140524
671 END IF;
672
673 userQuery :=
674 ' select fu.user_name, decode(sign(fu.end_date - sysdate),-1,2,1),
675 (SELECT sum(a) FROM (SELECT 1 a
676 FROM dual
677 WHERE EXISTS ( SELECT 1
678 FROM fnd_user_resp_groups_all fug,
679 fnd_responsibility fr,
680 fnd_menu_entries fme,
681 fnd_form_functions fff
682 WHERE
683 fug.user_id = :user_id1
684 AND fug.responsibility_application_id = 722
685 AND decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
686 AND fr.application_id = 722
687 AND fr.responsibility_id = fug.responsibility_id
688 AND fme.menu_id = fr.menu_id
689 AND fme.grant_flag = ''Y''
690 AND fme.sub_menu_id IS NULL
691 AND fff.function_id = fme.function_id
692 AND fff.function_name = ''MSD_DEM_SOP_SOPPLANR'')
693 UNION ALL
694 SELECT 2 a
695 FROM dual
696 WHERE EXISTS ( SELECT 1
697 FROM fnd_user_resp_groups_all fug,
698 fnd_responsibility fr,
699 fnd_menu_entries fme,
700 fnd_form_functions fff
701 WHERE
702 fug.user_id = :user_id2
703 AND fug.responsibility_application_id = 722
704 AND decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
705 AND fr.application_id = 722
706 AND fr.responsibility_id = fug.responsibility_id
707 AND fme.menu_id = fr.menu_id
708 AND fme.grant_flag = ''Y''
709 AND fme.sub_menu_id IS NULL
710 AND fff.function_id = fme.function_id
711 AND fff.function_name = ''MSD_DEM_SOP_WF_MGR''))) user_type,
712 ( select first_name
713 from per_all_people_f
714 where ((person_id = fu.employee_id
715 and fu.employee_id is not null)
716 or
717 (party_id = fu.person_party_id
718 and fu.person_party_id is not null)
719 or (party_id = fu.supplier_id
720 and fu.supplier_id is not null)
724 ( select last_name
721 or (party_id = fu.customer_id
722 and fu.customer_id is not null))
723 and rownum <2) first_name,
725 from per_all_people_f
726 where ((person_id = fu.employee_id
727 and fu.employee_id is not null)
728 or
729 (party_id = fu.person_party_id
730 and fu.person_party_id is not null)
731 or (party_id = fu.supplier_id
732 and fu.supplier_id is not null)
733 or (party_id = fu.customer_id
734 and fu.customer_id is not null))
735 and rownum <2) last_name,
736 ( select name
737 from hr_all_organization_units
738 where business_group_id in
739 (select pap.business_group_id
740 from per_all_people_f pap
741 where (pap.person_id = fu.employee_id
742 and fu.employee_id is not null)
743 or
744 (pap.party_id = fu.person_party_id
745 and fu.person_party_id is not null)
746 or (pap.party_id = fu.supplier_id
747 and fu.supplier_id is not null)
748 or (pap.party_id = fu.customer_id
749 and fu.customer_id is not null))
750 and rownum < 2) company,
751 ( select work_telephone
752 from per_all_people_f
753 where ((person_id = fu.employee_id
754 and fu.employee_id is not null)
755 or
756 (party_id = fu.person_party_id
757 and fu.person_party_id is not null)
758 or (party_id = fu.supplier_id
759 and fu.supplier_id is not null)
760 or (party_id = fu.customer_id
761 and fu.customer_id is not null))
762 and rownum <2) phone_num,
763 fu.fax,
764 decode(fu.email_address,
765 null,
766 (select pap.email_address
767 from per_all_people_f pap
768 where ((person_id = fu.employee_id
769 and fu.employee_id is not null)
770 or
771 (party_id = fu.person_party_id
772 and fu.person_party_id is not null)
773 or (party_id = fu.supplier_id
774 and fu.supplier_id is not null)
775 or (party_id = fu.customer_id
776 and fu.customer_id is not null))
777 and pap.email_address is not null
778 and rownum <2),
779 fu.email_address) email_address,
780 1 product
781 from fnd_user fu
782 where fu.user_id = :userid3';
783
784 IF (eventName = 'oracle.apps.fnd.user.delete')
785 THEN
786 p_user_name := x_curr_name;
787 ELSE
788
789 begin
790 execute immediate userQuery
791 into p_user_name,p_user_valid,p_user_resp,p_user_fname,p_user_lname,p_user_org_name,
792 p_user_wrkphone,p_user_fax,p_user_email,p_user_product
793 using userid, userid, userid;
794 exception
795 when others then
796 log_debug (substr(SQLERRM,1,150));
797 log ('Query on user_id ' || to_char(userid) || ' failed. Please ignore if not an Sales and Operations Planning User.');
798 return ( handleError ( PKG_NAME
799 , 'msd_dem_event.user_change'
800 , p_event
801 , p_subscription_guid
802 , ERROR
803 ));
804 end;
805 END IF;
806
807
808 /* Get the user type */
809 IF (eventName = 'oracle.apps.fnd.user.delete')
810 THEN
811 NULL;
812 ELSIF (p_user_resp = 1)
813 THEN
814 x_user_permission := 'DP';
815 ELSIF (p_user_resp = 3)
816 THEN
817 x_user_permission := 'DPA';
818 ELSIF ( eventName = 'oracle.apps.fnd.wf.ds.userRole.updated'
819 OR eventName = 'oracle.apps.fnd.wf.ds.user.updated')
820 THEN
821 eventName := 'oracle.apps.fnd.user.resp.delete';
822 ELSE
823 log ('Not a SOP User');
824 RETURN SUCCESS;
825 END IF;
826
827 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
828
829 /* Get the Sales and Operations Planning Component */
830 SELECT meaning
831 INTO x_component_name
832 FROM fnd_lookup_values_vl
833 WHERE lookup_type = 'MSD_DEM_COMPONENTS'
834 AND lookup_code = 'SOP';
835
836 log_debug ('Component: ' || x_component_name);
837
838 /* For SOP component user the string _SOP will be appended to the EBS user name */
839 p_user_name := p_user_name || '_SOP';
840 x_old_name := x_old_name || '_SOP';
841
842 /* CREATE USER */
843 x_create_user_sql := 'BEGIN ' || x_schema || '.API_CREATE_ORA_DEM_USER ( ' ||
844 ' ''' || p_user_name || ''' , ' ||
845 ' ''' || p_user_pwd || ''' , ' ||
849 ' ''' || p_user_org_name || ''' , ' ||
846 ' ''' || x_user_permission || ''' , ' ||
847 ' ''' || p_user_fname || ''' , ' ||
848 ' ''' || p_user_lname || ''' , ' ||
850 ' ''' || p_user_wrkphone || ''' , ' ||
851 ' ''' || p_user_fax || ''' , ' ||
852 ' ''' || p_user_email || ''' , ' ||
853 ' ''0'' , ' ||
854 ' null, ' ||
855 ' ''' || x_component_name || ''' , ' ||
856 ' null, ' ||
857 ' ''ADD''); END;';
858
859 /* UPDATE USER */
860 x_update_user_sql := 'BEGIN ' || x_schema || '.API_CREATE_ORA_DEM_USER ( ' ||
861 ' ''' || p_user_name || ''' , ' ||
862 ' ''' || p_user_pwd || ''' , ' ||
863 ' ''' || x_user_permission || ''' , ' ||
864 ' ''' || p_user_fname || ''' , ' ||
865 ' ''' || p_user_lname || ''' , ' ||
866 ' ''' || p_user_org_name || ''' , ' ||
867 ' ''' || p_user_wrkphone || ''' , ' ||
868 ' ''' || p_user_fax || ''' , ' ||
869 ' ''' || p_user_email || ''' , ' ||
870 ' ''0'' , ' ||
871 ' null, ' ||
872 ' ''' || x_component_name || ''' , ' ||
873 ' null, ' ||
874 ' ''UPDATE''); END;';
875
876 /* DROP USER */
877 x_drop_user_sql := 'BEGIN ' || x_schema || '.API_DROP_ORA_DEM_USER ( ''' || p_user_name || ''' ); END;';
878 x_drop_old_user_sql := 'BEGIN ' || x_schema || '.API_DROP_ORA_DEM_USER ( ''' || x_old_name || ''' ); END;';
879
880 /* USER EXISTS IN DEMANTRA */
881 DuserQuery := 'select count(user_name) from ' || x_schema || '.user_id where user_name = '''||p_user_name||'''';
882 log_debug ('User Query - ' || DuserQuery);
883
884 execute immediate DuserQuery Into Duser_cnt;
885 log_debug('User count - ' || to_char (Duser_cnt) );
886
887 /* OLD USER EXISTS IN DEMANTRA */
888 DOlduserQuery := 'select count(user_name) from ' || x_schema || '.user_id where user_name = '''||x_old_name||'''';
889 log_debug ('Old User Query - ' || DOlduserQuery);
890
891 log_debug ('Start processing the event - ' || eventName);
892
893 /* insert/Update responsibility for user */
894 If ( eventName = 'oracle.apps.fnd.wf.ds.userRole.created'
895 OR eventName = 'oracle.apps.fnd.wf.ds.userRole.updated') THEN
896
897 /* User does not exist in Demantra... Add the user */
898 If Duser_cnt = 0 Then
899
900 /* invoke API_CREATE_ORA_DEM_USER.(ADD) */
901 log_debug ('Insert/Update Responsibility - Creating User');
902 log_debug (x_create_user_sql);
903
904 EXECUTE IMMEDIATE x_create_user_sql;
905
906 /* User exists in Demantra, Update the responsibility */
907 Elsif Duser_cnt > 0 Then
908
909 /* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
910 log_debug ('Insert/Update Responsibility - Updating User');
911 log_debug (x_update_user_sql);
912
913 EXECUTE IMMEDIATE x_update_user_sql;
914
915 End if;
916
917 /* Update Existing User */
918 Elsif (eventName = 'oracle.apps.fnd.wf.ds.user.updated') THEN
919
920 /* Effective date has been disabled OR DM responsibilities have been disabled for user....delete user */
921 If (p_user_valid = 2 OR p_user_resp = 0) Then
922
923 /* invoke API_DROP_ORA_DEM_USER(user name) */
924 log_debug ('User Update - Deleting User');
925 log_debug (x_drop_user_sql);
926
927 EXECUTE IMMEDIATE x_drop_user_sql;
928
929 Else
930
931 /* User does not exist in Demantra but effective date is enabled....Add user */
932 If (Duser_cnt = 0) Then
933
934 /* invoke API_CREATE_ORA_DEM_USER.(ADD) */
935 log_debug ('User Update - Creating User');
936 log_debug (x_create_user_sql);
937
938 EXECUTE IMMEDIATE x_create_user_sql;
939
940 /* User exists in Demantra and effective date is enabled....Update user */
941 Else
942
943 /* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
944 log_debug('User Update - Updating User');
945 log_debug (x_update_user_sql);
946
947 EXECUTE IMMEDIATE x_update_user_sql;
948
949 End If;
950 End If;
951
952 /* Delete existing User */
953 Elsif (eventName = 'oracle.apps.fnd.user.delete') THEN
954
955 /* invoke API_DROP_ORA_DEM_USER(user name) */
956 log_debug('User Delete - Deleting User');
957 log_debug(x_drop_user_sql);
958
959 EXECUTE IMMEDIATE x_drop_user_sql;
960
961 /* Delete responsibility */
962 Elsif (eventName = 'oracle.apps.fnd.user.resp.delete') THEN
963
964 /* User exists in Demantra */
965 If (Duser_cnt > 0 AND x_user_permission IS NOT NULL) Then
966
967 /* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
968 log_debug ('Responsibility Delete - Updating user');
969 log_debug (x_update_user_sql);
970
971 EXECUTE IMMEDIATE x_update_user_sql;
972 Elsif (Duser_cnt > 0)
973 THEN
974 /* invoke API_DROP_ORA_DEM_USER(user name) */
975 log_debug('Responsibility Delete - Deleting user');
976 log_debug(x_drop_user_sql);
977
978 EXECUTE IMMEDIATE x_drop_user_sql;
979 End If;
980
981 Elsif (eventName = 'oracle.apps.fnd.wf.ds.user.nameChanged') Then
982
983 execute immediate DOlduserQuery Into DOlduser_cnt;
984 log_debug('Old User count - ' || to_char (DOlduser_cnt) );
985
986 /* Old User exists in Demantra */
987 IF (DOlduser_cnt > 0)
988 THEN
989
990 /* invoke API_DROP_ORA_DEM_USER(user name) */
991 log_debug('Drop User with Old Name');
992 log_debug(x_drop_old_user_sql);
993
994 EXECUTE IMMEDIATE x_drop_old_user_sql;
995 END IF;
996
997 IF (p_user_valid = 1)
998 THEN
999
1000 /* invoke API_CREATE_ORA_DEM_USER.(ADD) */
1001 log_debug ('Add User with New Name');
1002 log_debug (x_create_user_sql);
1003
1004 EXECUTE IMMEDIATE x_create_user_sql;
1005 END IF;
1006
1007 End if;
1008
1009 log('end');
1010 RETURN SUCCESS;
1011
1012
1013 EXCEPTION
1014 WHEN NO_DATA_FOUND THEN
1015 log_debug ('excep no data');
1016 LOG('No rows returned');
1017 return ( handleError ( PKG_NAME
1018 , 'msd_dem_event.user_change'
1019 , p_event
1020 , p_subscription_guid
1021 , ERROR
1022 ));
1023 WHEN OTHERS THEN
1024 return ( handleError ( PKG_NAME
1025 , 'msd_dem_event.user_change'
1026 , p_event
1027 , p_subscription_guid
1028 , ERROR
1029 ));
1030
1031 END SOP_USER_CHANGE;
1032
1033 end msd_dem_event;