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