[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_USERID_PKG
Source
1 PACKAGE BODY IGS_PE_USERID_PKG AS
2 /* $Header: IGSPE11B.pls 120.14 2006/09/21 13:06:35 gmaheswa ship $ */
3
4 /* +=======================================================================+
5 | Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | NAME |
9 | IGSVTFPB.pls |
10 | |
11 | DESCRIPTION |
12 | This package provides service functions and procedures to |
13 | support user name generation WF |
14 | |
15 | NOTES |
16 | |
17 | DEPENDENCIES |
18 | |
19 | USAGE |
20 | |
21 | HISTORY |
22 | 04-APR-2001 A Tereshenkov Created |
23 | Dec 19, 02 Sreedhar changed the process_group_id proc to add the |
24 | person_id as cursor paramter to c_email_info and |
25 | c_fnd_user_present. Also changed the c_email_info |
26 | to pick up the primary email address. bug 2712258 |
27 | |
28 | 27-JAN-2003 pkpatel (changed for Bug No 2753318, 2753728 )
29 | Create_Fnd_User, copy the password back to the workflow
30 | Generate_User, Added the validation that the password if entered should be at least 5 characters.
31 | Generate_Password, added the call of fnd_user_pkg.update_user if the primary email of the person has been changed.
32 | Create_Party, IGS_PE_TYP_INSTANCES_PKG.INSERT_row was commented
33 | 24-APR-2003 pkpatel Bug No: 2908802
34 | Modified Create_Fnd_User procedure
35 | 01-jul-2003 KUMMA, 2803555, Added the code to set the tokens for message IGS_PE_WF_EXISTS
36 | added statement to add the blank lines between successive messages inside procedure Process_Group_ID
37 | 23-JUL-2003 asbala Bug No:2667343 Replaced Hard coded strings populating l_msg_data and errbuf with
38 | new messages
39 | 28-OCT-2003 ssaleem Bug : 3198795 Part of the Dynamic/Static Person Groups modifications,
40 | Procedure Process_Group_ID is modified.
41 | 14-DEC-2004 pkpatel Bug 3316053 (Modified the logic for person number/alt id in generate_user.
42 | Set and Retrieve the new workflow item attribute in generate_user and ceate_party procedures.
43 | 23-APR-2003 asbala 3528702: Modified cursor c_resp. The job can now assign responsibilities other than those mapped too 'OTHER'.
44 | 23-JUN-2003 ssawhney bug 3713297 ..need to always select primary address and primary will always be ACTIVE
45 | 13-Apr-2005 ssaleem Bug 4293911 Fnd User customer Id replaced with person
46 | party id
47 | 21-SEP-2005 skpandey Bug: 3663505
48 | Description: Added ATTRIBUTES 21 TO 24 in create_party procedure to store additional information
49 | 19-Jan-06 gmaheswa 4869740: random number generators: dbms_random package is replaced by FND_CRYPTO for generating random numbers.
50 | 02-FEB-2006 skpandey Bug#4937960: Changed call from igs_get_dynamic_sql to get_dynamic_sql as a part of literal fix
51 | 04-May-2006 pkpatel Bug 5081932(Used the sequence IGS_PE_GEN_USER_S in AUTO_GENERATE_USERNAME method to pass unique value to the event_key)
52 | 17-May-2006 gmaheswa Bug#5250820, modified Validate_Person to remove the Mutual Exclusive logic of Party number and prefered alternate id
53 Also modified Dup_Person_Check to process applicant, alumni match not found condition, Multiple match.
54 Also created new function process_alumni_nomatch_event,generate_party_number.
55 introduced validate_password logic
56 | 21-Sep-2006 gmaheswa Bug 5546771 Modified generate_password logic to repeat for 500 times.
57 +=======================================================================+ */
58
59 l_prog_label CONSTANT VARCHAR2(500) :='igs.plsql.igs_pe_userid_pkg';
60 l_label VARCHAR2(4000);
61 l_debug_str VARCHAR2(32000);
62
63
64 FUNCTION Generate_Message RETURN VARCHAR2
65 IS
66 l_cur NUMBER;
67 l_msg_count NUMBER ;
68 l_msg_data VARCHAR2(32000) ;
69
70 BEGIN
71
72
73 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
74 p_data => l_msg_data );
75
76 IF l_msg_count >0 THEN
77 l_msg_data :='';
78
79 FOR l_cur IN 1..l_msg_count LOOP
80 l_msg_data :=l_msg_data||' '||l_cur||' '||FND_MSG_PUB.GET(l_cur,FND_API.G_FALSE);
81 END LOOP;
82 ELSE
83 l_msg_data := FND_MESSAGE.GET_STRING('IGS', 'IGS_PE_ERR_STACK_NO_DATA');
84 END IF;
85
86 RETURN l_msg_data;
87
88
89 END Generate_Message;
90
91 FUNCTION generate_password (
92 p_username IN VARCHAR2
93 ) RETURN VARCHAR2 IS
94
95 l_result varchar2(10);
96 v_counter BINARY_INTEGER := 1;
97 l_password_len int := 6;
98 x_password varchar2(40);
99 ascii_offset int := 65;
100 l_profile_pwd_len int;
101
102 BEGIN
103 l_profile_pwd_len := FND_PROFILE.VALUE('SIGNON_PASSWORD_LENGTH');
104 IF (l_profile_pwd_len IS NOT NULL) THEN
105 IF l_profile_pwd_len > l_password_len THEN
106 l_password_len := l_profile_pwd_len;
107 END IF;
108 END IF;
109
110 -- using the profile, determine the length of the random number
111 LOOP
112 x_password := null;
113 -- generate a random number to determine where to use an alphabet or a
114 -- numeric character for a given position in the password
115 FOR j IN 1..l_password_len LOOP
116 IF (MOD(ABS(FND_CRYPTO.RANDOMNUMBER),2) = 1) THEN
117 -- generate number
118 x_password := x_password || MOD(ABS(FND_CRYPTO.SMALLRANDOMNUMBER),10);
119 ELSE
120 -- generate character
121 x_password := x_password || FND_GLOBAL.LOCAL_CHR(MOD(ABS(FND_CRYPTO.SMALLRANDOMNUMBER),26)
122 + ascii_offset);
123 END IF;
124 END LOOP;
125 -- loop till password clears the validations
126 l_result := FND_WEB_SEC.VALIDATE_PASSWORD (p_username, x_password);
127 v_counter := v_counter + 1;
128 EXIT WHEN ((l_result = 'Y') OR (v_counter > 501));
129 END LOOP;
130
131 IF (v_counter > 500) THEN
132 -- return last generated passowrd conacteneted with 101 if generated password is not valid for 1000 times
133 RETURN x_password||'501';
134 ELSE
135 RETURN x_password;
136 END IF;
137
138 END generate_password;
139
140 /*
141 This procedure provides the default functionality for user name generation
142 */
143
144 FUNCTION GENERATE_USERNAME (
145 P_SUBSCRIPTION_GUID in raw,
146 P_EVENT in out NOCOPY wf_event_t
147 ) return varchar2 is
148 ------------------------------------------------------------------------------
149 l_result varchar2(100);
150
151 l_wf_name VARCHAR2(8) ;
152 l_process_name VARCHAR2(255) ;
153 l_item_key VARCHAR(255);
154 l_party_id hz_parties.party_id%TYPE;
155 --skpandey Bug#4937960, changed c_name cursor definition to optimize query
156 CURSOR c_name(cp_party_id hz_parties.party_id%TYPE) IS
157 SELECT upper(substr(person_last_name,1,12)||'.'||substr(person_first_name,1,14))
158 FROM hz_parties
159 WHERE party_id = cp_party_id;
160
161 l_name VARCHAR2(30);
162 l_user_name FND_USER.USER_NAME%TYPE;
163 l_person_number HZ_PARTIES.PARTY_NUMBER%TYPE;
164 l_first_name HZ_PARTIES.PERSON_FIRST_NAME%TYPE;
165 l_last_name HZ_PARTIES. PERSON_LAST_NAME%TYPE;
166 l_middle_name HZ_PARTIES. PERSON_MIDDLE_NAME%TYPE;
167 l_pref_name HZ_PARTIES.KNOWN_AS%TYPE;
168 l_pref_alt_id IGS_PE_ALT_PERS_ID.API_PERSON_ID%TYPE;
169 l_title HZ_PERSON_PROFILES.PERSON_TITLE%TYPE;
170 l_prefix HZ_PERSON_PROFILES.PERSON_PRE_NAME_ADJUNCT%TYPE;
171 l_suffix HZ_PARTIES.PERSON_NAME_SUFFIX%TYPE;
172 l_gender HZ_PERSON_PROFILES.GENDER%TYPE;
173 l_birth_date DATE;
174 l_email_address HZ_CONTACT_POINTS.EMAIL_ADDRESS%TYPE;
175 l_email_format HZ_CONTACT_POINTS.EMAIL_FORMAT%TYPE;
176 l_test_user_name FND_USER.USER_NAME%TYPE;
177 l_number NUMBER;
178 l_init_user_name FND_USER.USER_NAME%TYPE;
179 l_count NUMBER := 0;
180 BEGIN
181 l_wf_name :='IGSPEGEN';
182 l_process_name :='MAIN_PROCESS';
183
184 l_item_key := P_EVENT.GetValueForParameter('ITEM_KEY');
185 l_party_id := P_EVENT.GetValueForParameter('PARTY_ID');
186
187 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
188 l_label := 'igs.plsql.igs_pe_userid_pkg.GENERATE_USERNAME';
189 l_debug_str := 'Begin for Party ID:'||l_party_id;
190 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
191 END IF;
192
193 /*
194 If l_party_id IS null that means the event is raised for Self-Service Registration
195 If l_party_id IS not Null then that means the event is raised from the Batch user creation process
196 */
197 IF l_party_id IS NULL THEN
198
199 l_user_name := P_EVENT.GetValueForParameter('USER_NAME');
200 l_person_number := P_EVENT.GetValueForParameter('PERSON_NUMBER');
201 l_first_name := P_EVENT.GetValueForParameter('GIVEN_NAME');
202 l_last_name := P_EVENT.GetValueForParameter('SURNAME');
203 l_middle_name := P_EVENT.GetValueForParameter('MIDDLE_NAME');
204 l_pref_name := P_EVENT.GetValueForParameter('PREF_NAME');
205 l_pref_alt_id := P_EVENT.GetValueForParameter('ALT_ID');
206 l_title := P_EVENT.GetValueForParameter('TITLE');
207 l_prefix := P_EVENT.GetValueForParameter('PREFIX');
208 l_suffix := P_EVENT.GetValueForParameter('SUFFIX');
209 l_gender := P_EVENT.GetValueForParameter('GENDER');
210 l_birth_date := P_EVENT.GetValueForParameter('BIRTH_DATE');
211 l_email_address := P_EVENT.GetValueForParameter('USER_EMAIL');
212 l_email_format := P_EVENT.GetValueForParameter('EMAIL_FORMAT');
213 l_init_user_name := UPPER(SUBSTR(l_last_name,1,12)||'.'||SUBSTR(l_first_name,1,14));
214 ELSE
215 OPEN c_name(l_party_id);
216 FETCH c_name INTO l_init_user_name;
217 CLOSE c_name;
218 END IF;
219
220 l_init_user_name := replace(l_init_user_name, '/','');
221 l_init_user_name := replace(l_init_user_name, '"','');
222 l_init_user_name := replace(l_init_user_name, '(','');
223 l_init_user_name := replace(l_init_user_name, ')','');
224 l_init_user_name := replace(l_init_user_name, '*','');
225 l_init_user_name := replace(l_init_user_name, '+','');
226 l_init_user_name := replace(l_init_user_name, ',','');
227 l_init_user_name := replace(l_init_user_name, ';','');
228 l_init_user_name := replace(l_init_user_name, '<','');
229 l_init_user_name := replace(l_init_user_name, '>','');
230 l_init_user_name := replace(l_init_user_name, '\','');
231 l_init_user_name := replace(l_init_user_name, '~','');
232 l_init_user_name := replace(l_init_user_name, ':','');
233
234 l_user_name := l_init_user_name;
235 l_test_user_name := fnd_user_pkg.TestUserName(l_user_name);
236
237 WHILE (l_test_user_name <> fnd_user_pkg.USER_OK_CREATE AND l_count <= 100)
238 LOOP
239 l_number := FND_CRYPTO.RANDOMNUMBER;
240 IF l_number<0 THEN
241 l_number:=-l_number;
242 END IF;
243 l_user_name := SUBSTR(l_init_user_name||SUBSTR(l_number,1,5),1,100);
244 l_test_user_name := fnd_user_pkg.TestUserName(l_user_name);
245 l_count := l_count+1;
246 END LOOP;
247
248 IF (l_count > 100) THEN
249 FND_MESSAGE.SET_NAME('IGS', 'IGS_PE_UNAME_GEN_FAIL');
250 FND_MSG_PUB.Add;
251 RAISE FND_API.G_EXC_ERROR;
252 END IF;
253
254 IF l_party_id IS NULL THEN
255 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
256 l_label := 'igs.plsql.igs_pe_userid_pkg.GENERATE_USERNAME';
257 l_debug_str := 'Self Service Reg, User Name: '||l_user_name;
258 fnd_log.string(fnd_log.level_procedure,l_label,l_debug_str);
259 END IF;
260 p_event.addParametertoList('USER_NAME', l_user_name);
261 ELSE
262 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
263 l_label := 'igs.plsql.igs_pe_userid_pkg.GENERATE_USERNAME';
264 l_debug_str := 'Batch user creation, User Name: '||l_user_name;
265 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
266 END IF;
267
268 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'USER_NAME',l_user_name);
269 END IF;
270
271 l_result := wf_rule.default_rule(p_subscription_guid, p_event);
272
273 return(l_result);
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
278 l_label := 'igs.plsql.igs_pe_userid_pkg.GENERATE_USERNAME';
279 l_debug_str := 'EXCEPTION: '||SQLERRM;
280 fnd_log.string(fnd_log.level_procedure,l_label,l_debug_str);
281 END IF;
282
283 WF_CORE.CONTEXT('IGS_PE_USERID_PKG','GENERATE_USERNAME',p_event.event_name,p_event.event_key, sqlerrm,sqlcode);
284 wf_event.setErrorInfo(p_event,'ERROR');
285 RAISE;
286 return('ERROR');
287 end;
288
289
290
291 PROCEDURE Check_Setup
292 (
293 itemtype IN VARCHAR2,
294 itemkey IN VARCHAR2,
295 actid IN NUMBER,
296 funcmode IN VARCHAR2,
297 resultout OUT NOCOPY VARCHAR2
298 )IS
299 l_api_name VARCHAR2(30) ;
300 l_return_status VARCHAR2(1);
301
302 l_count NUMBER(9);
303
304 /* Select not defined person types */
305 CURSOR c_setup IS
306 SELECT 1
307 FROM igs_lookup_values
308 WHERE lookup_type = 'SYSTEM_PERSON_TYPES'
309 AND lookup_code NOT IN
310 ( SELECT s_person_type
311 FROM igs_pe_typ_rsp_dflt );
312
313
314 BEGIN
315 l_api_name := 'Check_Setup' ;
316
317
318 IF ( funcmode = 'RUN' ) THEN
319
320 OPEN c_setup;
321 FETCH c_setup INTO l_count;
322
323 IF c_setup%FOUND THEN
324
325 resultout := 'COMPLETE:N' ;
326
327 ELSE
328
329 resultout := 'COMPLETE:Y' ;
330
331 END IF;
332
333 CLOSE c_setup;
334 return;
335 END IF ;
336
337 IF ( funcmode = 'CANCEL' ) THEN
338 resultout := 'COMPLETE' ;
339 return;
340 END IF;
341
342 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
343 resultout := '' ;
344 return;
345 END IF;
346
347 EXCEPTION
348
349 WHEN OTHERS THEN
350
351 wf_core.context('IGS_PE_USERID_PKG', l_api_name,
352 itemtype, itemkey, to_char(actid), funcmode,Generate_Message());
353 RAISE ;
354
355 END Check_Setup ;
356
357 PROCEDURE get_person_number(p_person_number OUT NOCOPY VARCHAR2)
358 IS
359
360 l_return_status VARCHAR2(1);
361 l_count NUMBER(9);
362 l_event_t wf_event_t;
363 l_parameter_list_t wf_parameter_list_t;
364 l_wf_name VARCHAR2(8) ;
365 itemkey VARCHAR2(100);
366
367 BEGIN
368 itemkey :=substr(FND_CRYPTO.RANDOMNUMBER,1,5);
369 l_wf_name :='IGSPEGEN';
370 -- initialize the parameter list.
371 wf_event_t.Initialize(l_event_t);
372
373 -- set the parameters. This parameter is added with null value to initialize the parameter list.
374 wf_event.AddParameterToList ( p_name => 'PARAMETER_DUMMY',
375 p_value => NULL,
376 p_parameterlist => l_parameter_list_t);
377
378 WF_EVENT.RAISE3(p_event_name => 'oracle.apps.igs.pe.party_number.generate',
379 p_event_key => 'GEN_PARTY_NUM'||itemkey,
380 p_event_data => NULL,
381 p_parameter_list => l_parameter_list_t,
382 p_send_date => sysdate
383 );
384 p_person_number := UPPER(WF_EVENT.getValueForParameter('PERSON_NUMBER',l_parameter_list_t));
385 l_parameter_list_t.delete;
386
387 END get_person_number ;
388
389 FUNCTION generate_party_number (
390 P_SUBSCRIPTION_GUID in raw,
391 P_EVENT in out NOCOPY wf_event_t
392 ) return varchar2 is
393 /*
394 || Created By : GMAHESWA
395 || Created On : 4-May-2006
396 || Purpose : to generate party number when HZ: Generate Party Number profile is Set to 'N'
397 || Known limitations, enhancements or remarks :
398 || Change History :
399 || Who When What
400 || (reverse chronological order - newest change first)
401 */
402
403 CURSOR c_party_number(cp_party_num VARCHAR2) IS
404 SELECT 'X'
405 FROM hz_parties
406 WHERE party_number = cp_party_num;
407
408 l_party_found VARCHAR2(1);
409 l_person_number VARCHAR2(30);
410 l_count NUMBER := 0;
411 l_result VARCHAR2(100);
412
413 BEGIN
414 l_person_number := 'IGS-'||SUBSTR(FND_CRYPTO.RANDOMNUMBER,1,5);
415
416 OPEN c_party_number(l_person_number);
417 FETCH c_party_number INTO l_party_found;
418 CLOSE c_party_number;
419 WHILE (l_party_found IS NOT NULL AND l_count <= 10)
420 LOOP
421 l_party_found := NULL;
422 l_person_number := 'IGS-'||SUBSTR(FND_CRYPTO.RANDOMNUMBER,1,5);
423 l_count := l_count+1;
424 OPEN c_party_number(l_person_number);
425 FETCH c_party_number INTO l_party_found;
426 CLOSE c_party_number;
427 END LOOP;
428
429 IF l_count > 10 THEN
430 l_person_number := NULL;
431 END IF;
432 P_EVENT.addParametertoList('PERSON_NUMBER', l_person_number);
433
434 l_result := wf_rule.default_rule(p_subscription_guid, p_event);
435 RETURN(l_result);
436
437 EXCEPTION
438 WHEN OTHERS THEN
439 WF_CORE.CONTEXT('IGS_PE_USERID_PKG','GENERATE_PARTY_NUMBER',p_event.event_name,p_event.event_key, sqlerrm,sqlcode);
440 wf_event.setErrorInfo(p_event,'ERROR');
441 RAISE;
442 RETURN('ERROR');
443 END generate_party_number;
444
445 -- Stubbed as part of UMX uptake
446 PROCEDURE Create_Party
447 (
448 itemtype IN VARCHAR2,
449 itemkey IN VARCHAR2,
450 actid IN NUMBER,
451 funcmode IN VARCHAR2,
452 resultout OUT NOCOPY VARCHAR2
453 )
454 IS
455
456 BEGIN
457
458 NULL;
459
460 END Create_Party ;
461
462 PROCEDURE Create_Fnd_User
463 (
464 itemtype IN VARCHAR2,
465 itemkey IN VARCHAR2,
466 actid IN NUMBER,
467 funcmode IN VARCHAR2,
468 resultout OUT NOCOPY VARCHAR2
469 ) IS
470 /*
471 || Created By :
472 || Created On :
473 || Purpose :
474 || Known limitations, enhancements or remarks :
475 || Change History :
476 || Who When What
477 || (reverse chronological order - newest change first)
478 || pkpatel 15-JAN-2003 Bug NO: 2753728
479 || Passed the password back to the workflow once it is generated.
480 || pkpatel 24-APR-2003 Bug No: 2908802
481 || Modified the cursor c_resp for performance. Since the responsibility to be attached are the only mapped to 'OTHER'.
482 || No need to verify the existing responsibility attached.
483 || asbala 23-APR-2003 3528702: Modified cursor c_resp. The job can now assign responsibilities other than those mapped too 'OTHER'.
484 || This bug resulted in a regression in funtionality.
485 || Combinedly c_get_Sys_typ, c_resp and c_get_assigned_resp achieve the same functionality as c_resp did before 2908802 changes.
486 || gmaheswa 19-Jan-06 4869740: depreciated api's: fnd_user_pvt package is replaced by fnd_user_pkg.
487 */
488 l_api_name CONSTANT VARCHAR2(30) := 'Create_Fnd_User' ;
489 l_return_status VARCHAR2(1);
490 l_user_id fnd_user.user_id%TYPE;
491 l_user_name VARCHAR2(255);
492 l_user_password VARCHAR2(255);
493 l_email_address VARCHAR2(255);
494 l_party_id hz_parties.party_id%TYPE;
495
496 BEGIN
497
498
499 IF ( funcmode = 'RUN' ) THEN
500
501 l_user_name := upper(wf_engine.GetItemAttrText(itemtype,itemkey,'USER_NAME' ));
502 l_user_password := wf_engine.GetItemAttrText(itemtype,itemkey,'USER_PASSWORD' );
503 l_email_address := wf_engine.GetItemAttrText(itemtype,itemkey,'USER_EMAIL' );
504 l_party_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'PARTY_ID' );
505
506 IF l_user_password IS NULL OR length(l_user_password) <5 THEN
507 l_user_password := generate_password(l_user_name);
508 END IF;
509
510 -- Pass the password back to the workflow.
511 wf_engine.SetItemAttrText(itemtype,itemkey,'USER_PASSWORD', l_user_password );
512
513 -- Create a user
514 l_user_id := fnd_user_pkg.CreateUserIdParty (
515 x_user_name => l_user_name,
516 x_owner => 'CUST',
517 x_unencrypted_password => l_user_password,
518 x_email_address => l_email_address,
519 x_person_party_id => l_party_id
520 );
521 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
522 raise FND_API.G_EXC_ERROR;
523 END IF;
524
525 -- This logic assigns responsibilities to the user depending on the person_types he is assigned.
526
527 IF l_party_id IS NOT NULL THEN
528
529 assign_responsibility(l_party_id, l_user_id);
530
531 END IF;
532
533 UPDATE igs_pe_hz_parties
534 SET oracle_username =l_user_name
535 WHERE party_id = l_party_id;
536
537 resultout := 'COMPLETE:S' ;
538 return;
539 END IF ;
540
541 IF ( funcmode = 'CANCEL' ) THEN
542 resultout := 'COMPLETE' ;
543 return;
544 END IF;
545
546 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
547 resultout := '' ;
548 return;
549 END IF;
550
551 EXCEPTION
552
553 WHEN OTHERS THEN
554
555 wf_core.context('IGS_PE_USERID_PKG', l_api_name,
556 itemtype, itemkey, to_char(actid), funcmode,Generate_Message(),l_user_name,sqlerrm,fnd_message.get);
557 RAISE ;
558
559 END Create_Fnd_User ;
560
561
562 -- Stubbed as part of UMX uptake
563 PROCEDURE Validate_Username
564 (
565 itemtype IN VARCHAR2,
566 itemkey IN VARCHAR2,
567 actid IN NUMBER,
568 funcmode IN VARCHAR2,
569 resultout OUT NOCOPY VARCHAR2
570 )
571 IS
572 BEGIN
573
574 NULL;
575
576 END Validate_Username ;
577
578
579
580 PROCEDURE Generate_User
581 (
582 p_api_version IN NUMBER ,
583 p_init_msg_list IN VARCHAR2 ,
584 p_commit IN VARCHAR2 ,
585 p_validation_level IN NUMBER ,
586 x_return_status OUT NOCOPY VARCHAR2 ,
587 x_msg_count OUT NOCOPY NUMBER ,
588 x_msg_data OUT NOCOPY VARCHAR2 ,
589 p_title IN VARCHAR2 ,
590 p_number IN VARCHAR2 ,
591 p_prefix IN VARCHAR2 ,
592 p_alt_id IN VARCHAR2 ,
593 p_given_name IN VARCHAR2 ,
594 p_pref_name IN VARCHAR2 ,
595 p_middle_name IN VARCHAR2 ,
596 p_gender IN VARCHAR2 ,
597 p_surname IN VARCHAR2 ,
598 p_birth IN VARCHAR2 ,
599 p_suffix IN VARCHAR2 ,
600 p_user_name IN VARCHAR2 ,
601 p_user_password IN VARCHAR2 ,
602 p_email_format IN VARCHAR2 ,
603 p_email_address IN VARCHAR2
604 )
605 IS
606 /******************************************************************
607 Created By :
608 Date Created By :
609 Purpose : Combined call to all Inquiry related procedure
610 Known limitations,
611 enhancements,
612 remarks :
613 Change History
614 Who When What
615 pkpatel 21-JAN-2003 Bug No: 2753728
616 Added the validation that the password if entered should be at least 5 characters.
617 pkpatel 6-JAn-2004 Bug No: 3316053 (Modified the validation for passing Person Number/Preferred Alternate ID)
618 gmaheswa 23-Jan-2006 Bug: 4869740. Modified cursor c_hz_parties to use igs_pe_pers_base_v instead of igs_pe_person_v. Performan issue.
619 ******************************************************************/
620 l_api_name CONSTANT VARCHAR2(30) := 'Generate_User' ;
621 l_api_version CONSTANT NUMBER := 1.0 ;
622 l_return_status VARCHAR2(1) ;
623 l_msg_count NUMBER ;
624 l_msg_data VARCHAR2(2000) ;
625 l_item_key VARCHAR2(255);
626 l_party_id hz_parties.party_id%TYPE;
627 l_user_name VARCHAR2(30);
628 l_wf_name VARCHAR2(8) ;
629 l_process_name VARCHAR2(255) ;
630
631 l_local_inst VARCHAR2(30) ;
632 l_user_id NUMBER;
633 l_test_user_name pls_integer;
634
635 --Find if there is an incomplete request filed
636 CURSOR c_check_wf (cp_wf_name VARCHAR2, cp_item_key VARCHAR2) IS
637 SELECT 1
638 FROM wf_items
639 WHERE item_type = item_type
640 AND item_key like cp_item_key||'%'
641 AND END_DATE IS NULL;
642
643 -- Find the unique name
644 CURSOR c_get_wf_name(cp_wf_name VARCHAR2, cp_item_key VARCHAR2) IS
645 SELECT l_item_key||(max(NVL(substr(item_key,length(l_item_key)+1,10),0))+1)
646 FROM wf_items
647 WHERE item_type = cp_wf_name
648 AND item_key like cp_item_key||'%' ;
649
650 CURSOR c_hz_parties(cp_person_number VARCHAR2) IS
651 SELECT party_id
652 FROM hz_parties
653 WHERE party_number = cp_person_number;
654
655 CURSOR c_inst(cp_party_number hz_parties.party_number%TYPE) IS
656 SELECT party_name
657 FROM igs_or_inst_org_base_v
658 WHERE party_number = cp_party_number
659 AND inst_org_ind = 'I';
660
661 BEGIN
662 l_wf_name :='IGSPEGEN';
663 l_process_name :='MAIN_PROCESS';
664 l_user_name := UPPER(p_user_name);
665 l_local_inst := FND_PROFILE.VALUE('IGS_OR_LOCAL_INST');
666
667 SAVEPOINT Generate_User;
668 --
669 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
670 FND_MSG_PUB.initialize ;
671 END IF;
672 --
673 x_return_status := FND_API.G_RET_STS_SUCCESS ;
674
675 -- Create a unique WF process key
676
677 l_item_key := p_given_name||'.'|| p_surname||'.'||p_birth||'.'||NVL(p_alt_id,p_number)||'.'||p_gender;
678 -- Check if the process with this name exists:
679 OPEN c_check_wf(l_wf_name, l_item_key);
680 FETCH c_check_wf INTO l_party_id;
681 CLOSE c_check_wf;
682
683 IF l_party_id IS NOT NULL THEN
684
685 -- Active WF for the given user found
686 --kumma, 2803555, Added code to set the tokens for the following message
687
688 FND_MESSAGE.SET_NAME('IGS', 'IGS_PE_WF_EXISTS');
689 FND_MESSAGE.SET_TOKEN('GIVEN_NAME', p_given_name);
690 FND_MESSAGE.SET_TOKEN('LAST_NAME', p_surname);
691 FND_MSG_PUB.Add;
692 RAISE FND_API.G_EXC_ERROR;
693
694 END IF;
695
696 --Generate WF name
697
698 OPEN c_get_wf_name(l_wf_name, l_item_key);
699 FETCH c_get_wf_name INTO l_item_key;
700 CLOSE c_get_wf_name;
701
702
703 -- Check HZ parties
704 OPEN c_hz_parties(p_number);
705 FETCH c_hz_parties INTO l_party_id;
706 CLOSE c_hz_parties;
707
708 --Everything is ok - proceed to submition
709
710 wf_engine.CreateProcess ( ItemType => l_wf_name,
711 ItemKey => l_item_key,
712 Process => l_process_name );
713
714
715 WF_Engine.SetItemUserKey
716 (
717 ItemType => l_wf_name ,
718 ItemKey => l_item_key ,
719 UserKey => l_item_key
720 );
721
722
723 FOR c_inst_rec IN c_inst(l_local_inst) LOOP
724 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'INSTITUTION_NAME', c_inst_rec.party_name );
725 END LOOP;
726
727 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'TITLE', p_title );
728 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'PREFIX', p_prefix );
729 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'ALT_ID', p_alt_id );
730 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'PERSON_NUMBER', p_number );
731 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'GIVEN_NAME', p_given_name );
732 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'PREF_NAME', p_pref_name );
733 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'MIDDLE_NAME', p_middle_name );
734 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'GENDER', p_gender );
735 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'SURNAME', p_surname );
736 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'BIRTH_DATE', p_birth );
737 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'SUFFIX', p_suffix );
738 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'USER_NAME', l_user_name );
739 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'USER_PASSWORD', p_user_password );
740 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'EMAIL_FORMAT', p_email_format );
741 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'USER_EMAIL', p_email_address );
742 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'ITEM_KEY', l_item_key );
743 wf_engine.SetItemAttrNumber(l_wf_name,l_item_key,'PARTY_ID', l_party_id );
744
745 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'APPROVER', FND_PROFILE.VALUE('IGS_PE_USER_APPROVER') );
746 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'EVENTKEY',l_item_key );
747 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'APPROVAL_REQ', NVL(FND_PROFILE.VALUE('IGS_PE_APPROVAL_REQUIRED'),'N') );
748 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'GENERATE_USER', 'Y'); --NVL(FND_PROFILE.VALUE('IGS_PE_GENERATE_USER'),'N'));
749 wf_engine.SetItemAttrText(l_wf_name,l_item_key,'ADMIN_USERNAME',FND_PROFILE.VALUE('IGS_PE_USER_ADMIN'));
750
751
752 wf_engine.StartProcess ( ItemType => l_wf_name,
753 ItemKey => l_item_key );
754
755
756 COMMIT WORK;
757
758
759 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
760 p_data => x_msg_data );
761
762 EXCEPTION
763 WHEN FND_API.G_EXC_ERROR THEN
764 Rollback to Generate_User;
765 x_return_status := FND_API.G_RET_STS_ERROR;
766
767 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
768 p_data => x_msg_data );
769
770 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
771 Rollback to Generate_User;
772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
773
774 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
775 p_data => x_msg_data );
776
777 WHEN OTHERS THEN
778
779
780 Rollback to Generate_User;
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782
783 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
784 FND_MSG_PUB.Add_Exc_Msg ('IGS_PE_USERID_PKG', l_api_name);
785 END IF;
786
787 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
788 p_data => x_msg_data );
789
790 END Generate_User ;
791
792
793 FUNCTION get_id_name RETURN VARCHAR2
794 IS
795
796 CURSOR c_id_type IS
797 SELECT description
798 FROM igs_pe_person_id_typ
799 WHERE preferred_ind ='Y';
800
801 l_id_type igs_pe_person_id_typ.description%TYPE;
802
803
804 BEGIN
805
806 OPEN c_id_type;
807 FETCH c_id_type INTO l_id_type;
808 CLOSE c_id_type;
809
810 RETURN l_id_type;
811
812 END get_id_name;
813
814
815
816 PROCEDURE Generate_Password
817 (
818 p_api_version IN NUMBER ,
819 p_init_msg_list IN VARCHAR2 ,
820 p_commit IN VARCHAR2 ,
821 p_validation_level IN NUMBER ,
822 x_return_status OUT NOCOPY VARCHAR2 ,
823 x_msg_count OUT NOCOPY NUMBER ,
824 x_msg_data OUT NOCOPY VARCHAR2 ,
825 p_user_name IN VARCHAR2
826 )
827 IS
828 /*
829 || Created By :
830 || Created On :
831 || Purpose :
832 || Known limitations, enhancements or remarks :
833 || Change History :
834 || Who When What
835 || (reverse chronological order - newest change first)
836 || pkpatel 23-JAN-2003 Bug NO: 2753318
837 || The fnd user was updated with the primary email address from hz_contact points
838 || so that the mail will go to the person's primary email.
839 || gmaheswa 19-Jna-06 4869740: Stubbed
840 */
841 BEGIN
842 NULL;
843 END Generate_Password;
844
845 PROCEDURE Process_Group_ID (p_api_version IN NUMBER,
846 p_init_msg_list IN VARCHAR2 ,
847 p_commit IN VARCHAR2 ,
848 p_valid_lvl IN NUMBER ,
849 x_return_status OUT NOCOPY VARCHAR2,
850 p_person_group_id IN NUMBER
851 );
852
853 /******************************************************************
854 Created By : Don Shellito
855
856 Date Created By : 27-Jan-2002
857
858 Purpose : The following procedures are created for the
859 purpose of creating user IDs based upon the
860 Person ID Group that has been chosen by the
861 user of the Concurrent Request.
862
863 remarks :
864
865 Change History
866
867 Who When What
868 ---------------------------------------------------------------
869 Don Shellito 27-Jan-2002 New Package Created.
870 gmaheswa 5-Jan-2006 4869737: Added call to igs_ge_gen_003.set_org_id to disable OSS from R12.
871 ******************************************************************/
872 PROCEDURE Create_Batch_Users (errbuf OUT NOCOPY VARCHAR2,
873 retcode OUT NOCOPY NUMBER,
874 p_group_id IN NUMBER,
875 p_org_id IN VARCHAR2
876 ) IS
877
878 l_msg_count NUMBER;
879 l_msg_data VARCHAR2(20000);
880 l_error_text VARCHAR2(20000);
881 l_api_name CONSTANT VARCHAR2(30) := 'Create_Batch_Users';
882 l_api_version NUMBER ;
883 l_return_status VARCHAR2 (1);
884 l_group_cd VARCHAR2(10);
885
886 CURSOR c_person_id_group (cp_group_id NUMBER) IS
887 SELECT pe.group_cd
888 FROM igs_pe_persid_group_all pe
889 WHERE pe.group_id = cp_group_id
890 AND pe.closed_ind = 'N'
891 AND pe.create_dt <= SYSDATE;
892
893 BEGIN
894
895 igs_ge_gen_003.set_org_id;
896 l_api_version := 1.0;
897 --
898 -- Initialize the message stack for any messages that could be created prior to the processing
899 --
900 Fnd_Msg_Pub.Initialize;
901
902 -- If as per customer setup User provisioning(user creation/updation) is not allowed in any instances in any of the
903 -- product interfaces then log the error message in the log file and return
904 IF NOT FND_SSO_MANAGER.IsUserCreateUpdateAllowed THEN
905
906 FND_MESSAGE.SET_NAME('IGS', 'IGS_PE_USER_CRT_N_ALLOWED');
907 l_error_text := FND_MESSAGE.GET;
908 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_text);
909 errbuf := l_error_text;
910 retcode := 2;
911
912 RETURN;
913 END IF;
914 --
915 -- Validate that the Person ID group is valid and exists
916 --
917 OPEN c_person_id_group(p_group_id);
918 FETCH c_person_id_group
919 INTO l_group_cd;
920
921 IF (c_person_id_group%FOUND) THEN
922
923 CLOSE c_person_id_group;
924 --
925 -- Begin the processing of the users in the Group ID given
926 --
927 Process_Group_ID (p_api_version => l_api_version,
928 p_init_msg_list => Fnd_Api.G_FALSE,
929 p_commit => Fnd_Api.G_FALSE,
930 p_valid_lvl => Fnd_Api.G_VALID_LEVEL_FULL,
931 x_return_status => l_return_status,
932 p_person_group_id => p_group_id);
933
934 --
935 -- Determine if the processing of the group ID was successful
936 --
937 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
938 errbuf := FND_MESSAGE.GET_STRING('IGS', 'IGS_PE_NRML_CMPLTN_REQ');
939 retcode := 0;
940 ELSE
941 errbuf := FND_MESSAGE.GET_STRING('IGS', 'IGS_PE_ERR_ON_COMPLETION');
942 retcode := 2;
943 END IF;
944
945 ELSE
946
947 --
948 -- Person ID group could not be found. Log message invalid value given
949 --
950 CLOSE c_person_id_group;
951 Fnd_Message.SET_NAME ('IGS','IGS_PE_NO_GROUP_ID');
952 Fnd_Message.SET_TOKEN('PERSON_GROUP_ID',p_group_id);
953 Fnd_Msg_Pub.ADD;
954
955 END IF;
956
957 --
958 -- Determine if there are messages that need to be output into the request log file
959 -- for the user to view.
960 --
961 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
962 p_data => l_msg_data );
963
964 IF (l_msg_count > 0 AND retcode <> 0 ) THEN
965
966 l_error_text := '';
967 FOR l_cur IN 1..l_msg_count LOOP
968 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
969 fnd_file.put_line (FND_FILE.LOG, l_error_text);
970 END LOOP;
971
972 END IF;
973
974 RETURN;
975
976 -- --------------------------------------------------------------------
977 -- Exception handler section for the Archive_Purge_CBC_Request procedure.
978 -- --------------------------------------------------------------------
979 EXCEPTION
980
981 WHEN OTHERS THEN
982 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_PE_ABNRML_CMPLTN_REQ');
983 retcode := 2;
984 IF (c_person_id_group%ISOPEN) THEN
985 CLOSE c_person_id_group;
986 END IF;
987 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
988 FND_MSG_PUB.Add_Exc_Msg ('IGS_PE_USERID_PKG',
989 l_api_name);
990 END IF;
991
992 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
993 p_data => l_msg_data );
994
995 IF (l_msg_count > 0) THEN
996
997 l_error_text := '';
998 FOR l_cur IN 1..l_msg_count LOOP
999 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
1000 fnd_file.put_line (FND_FILE.LOG, l_error_text);
1001 END LOOP;
1002
1003 END IF;
1004 RETURN;
1005
1006 END Create_Batch_Users;
1007
1008
1009 PROCEDURE Process_Group_ID (p_api_version IN NUMBER,
1010 p_init_msg_list IN VARCHAR2 ,
1011 p_commit IN VARCHAR2 ,
1012 p_valid_lvl IN NUMBER ,
1013 x_return_status OUT NOCOPY VARCHAR2,
1014 p_person_group_id IN NUMBER
1015 ) IS
1016
1017 l_api_name CONSTANT VARCHAR2(30) := 'Process_Group_ID';
1018 l_api_version NUMBER ;
1019 l_return_status VARCHAR2(1);
1020 l_msg_count NUMBER;
1021 l_msg_data VARCHAR2(20000);
1022 l_error_text VARCHAR2(20000);
1023 l_email_address igs_pe_contacts_v.email_address%TYPE;
1024 l_email_format igs_pe_contacts_v.email_format%TYPE;
1025 l_user_name fnd_user.user_name%TYPE;
1026 l_user_end_date fnd_user.end_date%TYPE;
1027
1028 l_commit VARCHAR2(100);
1029 l_init_msg_list VARCHAR2(100);
1030 l_valid_lvl NUMBER;
1031 -- changed the cursor to pick up only active members of the group
1032
1033 CURSOR cur_person_grp_desc(cp_group_id igs_pe_persid_group_all.group_id%TYPE) IS
1034 SELECT description
1035 FROM igs_pe_persid_group_all
1036 WHERE group_id = cp_group_id;
1037
1038 l_select VARCHAR2(32767);
1039 l_str VARCHAR2(32767);
1040 l_status VARCHAR2(1);
1041 grp_desc_rec cur_person_grp_desc%ROWTYPE;
1042 l_group_type IGS_PE_PERSID_GROUP_V.group_type%TYPE;
1043 TYPE pgroup_query IS REF CURSOR;
1044 pgroup_refcur pgroup_query;
1045
1046 TYPE member_type IS RECORD( person_id igs_pe_person_base_v.person_id%TYPE,
1047 person_number igs_pe_person_base_v.person_number%TYPE,
1048 full_name igs_pe_person_base_v.full_name%TYPE,
1049 sex igs_pe_person_base_v.gender%TYPE,
1050 birth_date igs_pe_person_base_v.birth_date%TYPE,
1051 title igs_pe_person_base_v.title%TYPE,
1052 surname igs_pe_person_base_v.last_name%TYPE,
1053 given_name igs_pe_person_base_v.first_name%TYPE,
1054 preferred_name igs_pe_person_base_v.known_as%TYPE,
1055 suffix igs_pe_person_base_v.suffix%TYPE,
1056 prefix igs_pe_person_base_v.pre_name_adjunct%TYPE,
1057 middle_name igs_pe_person_base_v.middle_name%TYPE,
1058 alternate_id igs_pe_person_id_type_v.api_person_id%TYPE);
1059
1060 l_member_rec member_type;
1061
1062 -- changed the cursor to pick up the primary email and removed the
1063 -- active clause, since the primary email is always active.
1064
1065 CURSOR c_email_info(cp_person_id IGS_PE_PERSON.PERSON_ID%TYPE) IS
1066 SELECT email_address
1067 FROM hz_parties
1068 WHERE party_id = cp_person_id;
1069
1070 CURSOR c_fnd_user_present(p_person_id IGS_PE_PERSON.PERSON_ID%TYPE) IS
1071 SELECT fnd.user_name,end_date
1072 FROM fnd_user fnd
1073 WHERE fnd.person_party_id = p_person_id;
1074
1075 BEGIN
1076 -- use local variables instead of the parameters (since parameters cannot be initialised here)
1077 l_init_msg_list := FND_API.G_FALSE;
1078 IF p_commit IS NULL THEN
1079 l_commit := Fnd_Api.G_FALSE;
1080 ELSE
1081 l_commit := p_commit;
1082 END IF;
1083 IF p_valid_lvl IS NULL THEN
1084 l_valid_lvl := Fnd_Api.G_VALID_LEVEL_FULL;
1085 ELSE
1086 l_valid_lvl := p_valid_lvl;
1087 END IF;
1088
1089 l_api_version := 1.0;
1090 l_select := ' SELECT ' ||
1091 ' p.person_id , ' ||
1092 ' p.person_number , ' ||
1093 ' p.full_name, ' ||
1094 ' p.gender sex , ' ||
1095 ' p.birth_date, ' ||
1096 ' p.title, ' ||
1097 ' p.last_name surname, ' ||
1098 ' p.first_name given_name , ' ||
1099 ' p.known_as preferred_name , ' ||
1100 ' p.suffix, ' ||
1101 ' p.pre_name_adjunct prefix, ' ||
1102 ' p.middle_name, ' ||
1103 ' pit.api_person_id alternate_id ' ||
1104 ' FROM ' ||
1105 ' igs_pe_person_base_v p, ' ||
1106 ' igs_pe_person_id_type_v pit ' ||
1107 ' WHERE ' ||
1108 ' p.person_id = pit.pe_person_id (+) AND ' ||
1109 ' p.person_id IN ';
1110
1111 l_str := igs_pe_dynamic_persid_group.get_dynamic_sql(p_person_group_id, l_status, l_group_type);
1112
1113 OPEN cur_person_grp_desc(p_person_group_id);
1114 FETCH cur_person_grp_desc INTO grp_desc_rec;
1115 CLOSE cur_person_grp_desc;
1116
1117 l_select := l_select || '(' || l_str || ')';
1118
1119 IF l_status <> 'S' THEN
1120 RAISE NO_DATA_FOUND;
1121 END IF;
1122
1123 --
1124 -- Set the return status as success for the api
1125 --
1126 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1127
1128 --
1129 -- Savepoint initialization
1130 --
1131 SAVEPOINT Process_Group_ID_PVT;
1132
1133 --
1134 -- Make sure that the appropriate version is being used and initialize
1135 -- the message stack if required.
1136 --
1137 IF (NOT FND_API.Compatible_API_Call ( l_api_version,
1138 p_api_version,
1139 l_api_name,
1140 'IGS_PE_USERID_PKG' )) THEN
1141 raise FND_API.G_EXC_UNEXPECTED_ERROR ;
1142 END IF;
1143
1144 --
1145 -- Obtain all the user or members of the group ID given
1146 --
1147 --skpandey, Bug#4937960: Added logic as a part of Literal Fix
1148 IF l_group_type = 'STATIC' THEN
1149 OPEN pgroup_refcur FOR l_select USING p_person_group_id ;
1150 ELSIF l_group_type = 'DYNAMIC' THEN
1151 OPEN pgroup_refcur FOR l_select;
1152 END IF;
1153
1154 LOOP
1155 FETCH pgroup_refcur INTO l_member_rec;
1156 EXIT WHEN pgroup_refcur%NOTFOUND;
1157
1158 FND_MSG_PUB.initialize;
1159 --
1160 -- Check to determine if the person already has a user name assigned in fnd_user
1161 --
1162 l_user_name := NULL;
1163 l_user_end_date := NULL;
1164
1165 OPEN c_fnd_user_present(l_member_rec.person_id);
1166 FETCH c_fnd_user_present
1167 INTO l_user_name, l_user_end_date;
1168
1169 IF (c_fnd_user_present%NOTFOUND) THEN
1170
1171 --
1172 -- Ensure that the person has email information setup.
1173 --
1174 l_email_address := NULL;
1175
1176 OPEN c_email_info(l_member_rec.person_id);
1177 FETCH c_email_info
1178 INTO l_email_address;
1179
1180 IF (c_email_info%FOUND) THEN
1181
1182 --
1183 -- Call the appropriate procedure that will handle the user creation
1184 --
1185
1186 Generate_User (p_api_version => l_api_version,
1187 p_init_msg_list => FND_API.G_TRUE,
1188 p_commit => FND_API.G_TRUE,
1189 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1190 x_return_status => l_return_status,
1191 x_msg_count => l_msg_count,
1192 x_msg_data => l_msg_data,
1193 p_title => l_member_rec.title,
1194 p_number => l_member_rec.person_number,
1195 p_prefix => l_member_rec.prefix,
1196 p_alt_id => l_member_rec.alternate_id,
1197 p_given_name => l_member_rec.given_name,
1198 p_pref_name => l_member_rec.preferred_name,
1199 p_middle_name => l_member_rec.middle_name,
1200 p_gender => l_member_rec.sex,
1201 p_surname => l_member_rec.surname,
1202 p_birth => TO_CHAR(l_member_rec.birth_date,'DD/MM/RRRR'),
1203 p_suffix => l_member_rec.suffix,
1204 p_user_name => NULL,
1205 p_user_password => NULL,
1206 p_email_format => l_email_format,
1207 p_email_address => l_email_address
1208 );
1209
1210 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1211 Fnd_Message.SET_NAME ('IGS','IGS_PE_PRSN_ACCT_NOT_CREATED');
1212 Fnd_Message.SET_TOKEN('USERNAME', l_member_rec.full_name);
1213 Fnd_Msg_Pub.ADD;
1214 ELSE
1215 Fnd_Message.SET_NAME ('IGS','IGS_PE_PRSN_ACCT_CREATED');
1216 Fnd_Message.SET_TOKEN('USERNAME', l_member_rec.full_name);
1217 Fnd_Message.SET_TOKEN('GRP_DESCRIPTION', grp_desc_rec.description);
1218 Fnd_Msg_Pub.ADD;
1219 END IF;
1220
1221 ELSE
1222 Fnd_Message.SET_NAME ('IGS','IGS_PE_PRSN_NO_EMAIL_INFO');
1223 Fnd_Message.SET_TOKEN('USERNAME', l_member_rec.full_name);
1224 Fnd_Msg_Pub.ADD;
1225 END IF;
1226
1227 ELSE
1228 -- this section for user account found
1229 -- if the user account has been inactivated for any reasons indicate in the log
1230 IF l_user_end_date IS NOT NULL AND l_user_end_date < sysdate THEN
1231 FND_MESSAGE.SET_NAME('IGS','IGS_PE_USERID_EXIST_INACTIVE');
1232 FND_MESSAGE.SET_TOKEN('USERNAME',l_member_rec.full_name);
1233 FND_MSG_PUB.ADD;
1234 ELSE
1235 -- the user account exists and is active as on the date this job is run
1236 -- so the user is not re-processed
1237 Fnd_Message.SET_NAME ('IGS','IGS_PE_USERID_NOT_PROCESSED');
1238 Fnd_Message.SET_TOKEN('USERNAME', l_member_rec.full_name);
1239 Fnd_Message.SET_TOKEN('GRP_DESCRIPTION', grp_desc_rec.description);
1240 Fnd_Msg_Pub.ADD;
1241 END IF;
1242 END IF;
1243
1244 --
1245 -- Make sure that all messages are taken from the stack to be output to the log file.
1246 -- The message stack is initialized for each loop iteration.
1247 --
1248 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
1249 p_data => l_msg_data );
1250
1251
1252 IF (l_msg_count > 0) THEN
1253
1254 l_error_text := '';
1255 FOR l_cur IN 1..l_msg_count LOOP
1256 l_error_text := ' ' || FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
1257 fnd_file.put_line (FND_FILE.LOG, l_error_text);
1258 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1259 END LOOP;
1260
1261 END IF;
1262
1263 --
1264 -- Ensure that all the cursors used are closed
1265 --
1266 IF (c_email_info%ISOPEN) THEN
1267 CLOSE c_email_info;
1268 END IF;
1269 IF (c_fnd_user_present%ISOPEN) THEN
1270 CLOSE c_fnd_user_present;
1271 END IF;
1272
1273 END LOOP;
1274 CLOSE pgroup_refcur;
1275
1276 RETURN;
1277
1278 --
1279 -- Exception handler section for the Process_Group_ID procedure.
1280 --
1281 EXCEPTION
1282 WHEN NO_DATA_FOUND THEN
1283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1284 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1285 fnd_message.set_name('IGS','IGS_PE_PERSID_GROUP_EXP');
1286 fnd_file.put_line (FND_FILE.LOG,fnd_message.get);
1287 END IF;
1288 IF (pgroup_refcur%ISOPEN) THEN
1289 CLOSE pgroup_refcur;
1290 END IF;
1291 RETURN;
1292 WHEN OTHERS THEN
1293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1294 ROLLBACK TO Process_Group_ID_PVT;
1295 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1296 FND_MSG_PUB.Add_Exc_Msg ('IGS_PE_USERID_PKG', l_api_name);
1297 END IF;
1298 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
1299 p_data => l_msg_data );
1300
1301 IF (l_msg_count > 0) THEN
1302
1303 l_error_text := '';
1304 FOR l_cur IN 1..l_msg_count LOOP
1305 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
1306 fnd_file.put_line (FND_FILE.LOG, l_error_text);
1307 END LOOP;
1308
1309 END IF;
1310 RETURN;
1311
1312 END Process_Group_ID;
1313
1314 PROCEDURE assign_responsibility (p_person_id NUMBER, p_user_id NUMBER)
1315 IS
1316 /******************************************************************
1317 Created By : Prabhat Patel
1318 Date Created By : 21-Mar-2006
1319 Purpose : This procedure checks the person types assigned to a person. Then the responsibilities are
1320 assigned to the user associated to the person as per OSS Person Type/Responsibility mapping
1321 Change History
1322 Who When What
1323 ******************************************************************/
1324
1325 -- Cursor to get all the active person types associated with the person
1326 CURSOR c_get_sys_typ(cp_party_id hz_parties.party_id%TYPE, cp_sysdate DATE) IS
1327 SELECT c.system_type
1328 FROM igs_pe_typ_instances c
1329 WHERE c.person_id = cp_party_id
1330 AND cp_sysdate BETWEEN c.start_date AND NVL(c.end_date, cp_sysdate);
1331
1332 -- Cursor to get the responsibilities associated with a particular person type
1333 CURSOR c_resp(cp_system_type igs_pe_person_types.system_type%TYPE) IS
1334 SELECT st.responsibility_key resp_name,
1335 st.application_short_name apps_name,
1336 rsp.application_id apps_id,
1337 rsp.responsibility_id resp_id
1338 FROM igs_pe_typ_rsp_dflt st,
1339 fnd_responsibility rsp,
1340 fnd_application ap
1341 WHERE st.s_person_type = cp_system_type
1342 AND st.responsibility_key=rsp.responsibility_key
1343 AND ap.application_id =rsp.application_id
1344 AND ap.application_short_name = st.application_short_name;
1345
1346 -- Cursor to check whether a particular responsibility is assigned to a person
1347 CURSOR c_get_assigned_resp (cp_user_id fnd_user_resp_groups_direct.user_id%TYPE,
1348 cp_responsibility_id fnd_user_resp_groups_direct.responsibility_id%TYPE,
1349 cp_resp_app_id fnd_user_resp_groups_direct.responsibility_application_id%TYPE) IS
1350 SELECT responsibility_id
1351 FROM fnd_user_resp_groups_direct
1352 WHERE user_id = cp_user_id
1353 AND responsibility_id = cp_responsibility_id
1354 AND responsibility_application_id = cp_resp_app_id;
1355
1356 c_get_sys_typ_rec c_get_sys_typ%ROWTYPE;
1357 c_get_assigned_resp_rec c_get_assigned_resp%ROWTYPE;
1358 l_sysdate DATE;
1359
1360 BEGIN
1361 l_sysdate := TRUNC(SYSDATE);
1362
1363 -- Check the person type assigned
1364 OPEN c_get_sys_typ(p_person_id, l_sysdate);
1365 LOOP
1366 FETCH c_get_sys_typ INTO c_get_sys_typ_rec;
1367 EXIT WHEN c_get_sys_typ%NOTFOUND;
1368
1369 -- Check the responsibilities mapped with the person type
1370 FOR c_resp_rec IN c_resp(c_get_sys_typ_rec.system_type) LOOP
1371
1372 -- Check whether the responsibility is already assigned to the person
1373 OPEN c_get_assigned_resp(p_user_id, c_resp_rec.resp_id, c_resp_rec.apps_id);
1374 FETCH c_get_assigned_resp INTO c_get_assigned_resp_rec;
1375 IF c_get_assigned_resp%NOTFOUND THEN
1376
1377 --Create a resp
1378 FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT(
1379 user_id => p_user_id,
1380 responsibility_id => c_resp_rec.resp_id,
1381 responsibility_application_id => c_resp_rec.apps_id,
1382 security_group_id => 0,
1383 start_date => l_sysdate,
1384 end_date => null,
1385 description => 'IGS WF autoassign');
1386 END IF;
1387 CLOSE c_get_assigned_resp;
1388 END LOOP;
1389 END LOOP;
1390 CLOSE c_get_sys_typ;
1391
1392 END assign_responsibility;
1393
1394 FUNCTION umx_business_logic(
1395 p_subscription_guid IN RAW,
1396 p_event IN OUT NOCOPY WF_EVENT_T
1397 ) RETURN VARCHAR2
1398 IS
1399
1400 l_result VARCHAR2(100);
1401 l_event_key VARCHAR(255);
1402 l_event_context VARCHAR2(30);
1403 l_person_id NUMBER;
1404 l_user_id NUMBER;
1405
1406 l_api_name CONSTANT VARCHAR2(30) := 'Create_Party' ;
1407 l_return_status VARCHAR2(1);
1408 l_person_number VARCHAR2(255);
1409 l_title VARCHAR2(255);
1410 l_number VARCHAR2(255);
1411 l_prefix VARCHAR2(255);
1412 l_alt_id VARCHAR2(255);
1413 l_given_name VARCHAR2(255);
1414 l_pref_name VARCHAR2(255);
1415 l_middle_name VARCHAR2(255);
1416 l_gender VARCHAR2(255);
1417 l_surname VARCHAR2(255);
1418 l_birth VARCHAR2(255);
1419 l_birth_dt DATE;
1420 l_suffix VARCHAR2(255);
1421 l_user_name VARCHAR2(255);
1422 l_email_format VARCHAR2(255);
1423 l_email_address VARCHAR2(255);
1424 l_new_address VARCHAR2(255);
1425 l_rowid VARCHAR2(255);
1426 l_msg_count NUMBER;
1427 l_msg_data VARCHAR2(255);
1428 l_contact_point_id hz_contact_points.contact_point_id%TYPE;
1429 l_date DATE;
1430 l_id_type igs_pe_person_id_typ.person_id_type%TYPE;
1431 l_count NUMBER(9);
1432
1433 CURSOR c_id_type IS
1434 SELECT person_id_type
1435 FROM igs_pe_person_id_typ
1436 WHERE preferred_ind ='Y';
1437
1438 CURSOR c_email(cp_person_id NUMBER) IS
1439 SELECT email_address
1440 FROM hz_parties
1441 WHERE party_id = cp_person_id;
1442
1443 l_default_date DATE := TRUNC(SYSDATE);
1444
1445 CURSOR c_dt_format(cp_date VARCHAR2) IS
1446 SELECT fnd_date.canonical_to_date(cp_date)
1447 FROM DUAL;
1448
1449 l_object_version_number NUMBER;
1450 l_contact_point_ovn NUMBER;
1451
1452 BEGIN
1453 l_event_context := p_event.getvalueforparameter ('UMX_CUSTOM_EVENT_CONTEXT');
1454
1455 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1456 l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic';
1457 l_debug_str := 'Begin for UMX_CUSTOM_EVENT_CONTEXT:'||l_event_context;
1458 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1459 END IF;
1460
1461 IF l_event_context = UMX_PUB.BEFORE_ACT_ACTIVATION THEN
1462 l_title := p_event.getvalueforparameter ('IGS_TITLE');
1463 l_prefix := p_event.getvalueforparameter ('IGS_PREFIX');
1464 l_alt_id := p_event.getvalueforparameter ('IGS_PREF_ALT_ID');
1465 l_person_number := p_event.getvalueforparameter ('IGS_PERSON_NUMBER');
1466 l_given_name := p_event.getvalueforparameter ('FIRST_NAME');
1467 l_pref_name := p_event.getvalueforparameter ('IGS_PREF_NAME');
1468 l_middle_name := p_event.getvalueforparameter ('MIDDLE_NAME' );
1469 l_gender := p_event.getvalueforparameter ('IGS_GENDER');
1470 l_surname := p_event.getvalueforparameter ('LAST_NAME');
1471 l_birth := p_event.getvalueforparameter ('IGS_BIRTH_DATE');
1472 l_suffix := p_event.getvalueforparameter ('PERSON_SUFFIX' );
1473 l_user_name := p_event.getvalueforparameter ('REQUESTED_USERNAME');
1474 l_email_format := p_event.getvalueforparameter ('EMAIL_FORMAT');
1475 l_email_address := p_event.getvalueforparameter ('EMAIL_ADDRESS' );
1476 l_person_id := p_event.getvalueforparameter ('PERSON_PARTY_ID');
1477
1478 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1479 l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic: UMX_PUB.BEFORE_ACT_ACTIVATION';
1480 l_debug_str := 'Event Parameters: IGS_TITLE: '||l_title||' IGS_PREFIX: '||l_prefix||' IGS_PREF_ALT_ID: '||l_alt_id
1481 ||' IGS_PERSON_NUMBER: '||l_person_number||' FIRST_NAME: '||l_given_name||' IGS_PREF_NAME: '||l_pref_name
1482 ||' MIDDLE_NAME: '||l_middle_name||' IGS_GENDER: '||l_gender||' LAST_NAME: '||l_surname
1483 ||' IGS_BIRTH_DATE: '||l_birth||' PERSON_SUFFIX: '||l_suffix||' REQUESTED_USERNAME: '||l_user_name
1484 ||' EMAIL_FORMAT: '||l_email_format||' EMAIL_ADDRESS: '||l_email_address||' PERSON_PARTY_ID: '||l_person_id;
1485 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1486 END IF;
1487
1488 IF l_birth IS NOT NULL THEN
1489 OPEN c_dt_format(l_birth);
1490 FETCH c_dt_format INTO l_birth_dt;
1491 CLOSE c_dt_format;
1492 END IF;
1493
1494 IF l_person_id IS NULL THEN
1495
1496 OPEN c_id_type;
1497 FETCH c_id_type INTO l_id_type;
1498 CLOSE c_id_type;
1499
1500 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1501 l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic: person_id IS NULL';
1502 l_debug_str := 'Before Person Insert';
1503 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1504 END IF;
1505
1506 igs_pe_person_pkg.insert_row(
1507 x_msg_count => l_msg_count,
1508 x_msg_data => l_msg_data,
1509 x_rowid => l_rowid,
1510 x_return_status => l_return_status,
1511 x_person_id => l_person_id,
1512 x_person_number => l_person_number,
1513 x_surname => l_surname,
1514 x_middle_name => l_middle_name,
1515 x_given_names => l_given_name,
1516 x_sex => l_gender,
1517 x_title => l_title,
1518 x_staff_member_ind => '',
1519 x_deceased_ind => 'N',
1520 x_suffix => l_suffix,
1521 x_pre_name_adjunct => l_prefix,
1522 x_archive_exclusion_ind => '',
1523 x_archive_dt => '',
1524 x_purge_exclusion_ind => '',
1525 x_purge_dt => '',
1526 x_deceased_date => null,
1527 x_proof_of_ins => '',
1528 x_proof_of_immu => '',
1529 x_birth_dt => l_birth_dt,
1530 x_salutation => '',
1531 x_oracle_username => l_user_name,
1532 x_preferred_given_name => l_pref_name,
1533 x_email_addr => l_email_address,
1534 x_level_of_qual_id => '',
1535 x_military_service_reg => '',
1536 x_veteran => '',
1537 x_hz_parties_ovn => l_object_version_number,
1538 x_attribute_category => '',
1539 x_attribute1 => '',
1540 x_attribute2 => '',
1541 x_attribute3 => '',
1542 x_attribute4 => '',
1543 x_attribute5 => '',
1544 x_attribute6 => '',
1545 x_attribute7 => '',
1546 x_attribute8 => '',
1547 x_attribute9 => '',
1548 x_attribute10 => '',
1549 x_attribute11 => '',
1550 x_attribute12 => '',
1551 x_attribute13 => '',
1552 x_attribute14 => '',
1553 x_attribute15 => '',
1554 x_attribute16 => '',
1555 x_attribute17 => '',
1556 x_attribute18 => '',
1557 x_attribute19 => '',
1558 x_attribute20 => '',
1559 x_person_id_type => l_id_type,
1560 x_api_person_id => l_alt_id,
1561 x_status => 'A',
1562 x_attribute21 => '',
1563 x_attribute22 => '',
1564 x_attribute23 => '',
1565 x_attribute24 => ''
1566 );
1567
1568 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1569 l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic: person_id IS NULL';
1570 l_debug_str := 'After Person Insert Call: l_return_status: '||l_return_status||'***l_person_id: '||l_person_id||'****l_msg_data/sqlerrm/l_msg_count:'||l_msg_data||'/'||l_msg_count;
1571 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1572 END IF;
1573 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1574 UMX_PUB.updateWfAttribute (p_event => p_event,
1575 P_ATTR_NAME => 'PERSON_PARTY_ID',
1576 P_ATTR_VALUE => l_person_id);
1577 END IF;
1578 ELSE
1579 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1580 l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic: person_id IS NOT NULL';
1581 l_debug_str := 'l_person_id: '||l_person_id;
1582 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1583 END IF;
1584 UMX_PUB.updateWfAttribute (p_event => p_event,
1585 P_ATTR_NAME => 'PERSON_PARTY_ID',
1586 P_ATTR_VALUE => l_person_id);
1587 END IF;
1588
1589 -- Check if contact point exist for the user.
1590
1591 IF l_return_status IS NULL or l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1592 OPEN c_email(l_person_id);
1593 FETCH c_email INTO l_new_address;
1594 CLOSE c_email;
1595 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1596 l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic: After Successful Insert of Person';
1597 l_debug_str := 'Before insert/update of Email Address: l_new_address: '||l_new_address||'***l_email_address: '||l_email_address;
1598 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1599 END IF;
1600
1601 IF l_new_address IS NULL OR l_email_address <> l_new_address THEN
1602 IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKE(
1603 p_action => 'INSERT',
1604 p_rowid => l_rowid ,
1605 p_status => 'A',
1606 p_owner_table_name => 'HZ_PARTIES',
1607 p_owner_table_id => l_person_id,
1608 P_primary_flag => 'Y',
1609 p_email_format => l_email_format,
1610 p_email_address => l_email_address,
1611 p_return_status => l_return_status,
1612 p_msg_data => l_msg_data,
1613 p_last_update_date => l_date,
1614 p_contact_point_id => l_contact_point_id,
1615 p_contact_point_ovn => l_contact_point_ovn
1616 ) ;
1617 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1618 l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic';
1619 l_debug_str := 'After IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKE: l_return_status: '||l_return_status||'***l_msg_data/sqlerrm:'||l_msg_data;
1620 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1621 END IF;
1622 END IF;
1623
1624 END IF;
1625 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1626 l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic';
1627 l_debug_str := 'END OF UMX_PUB.BEFORE_ACT_ACTIVATION';
1628 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1629 END IF;
1630
1631 ELSIF l_event_context = UMX_PUB.AFTER_ACT_ACTIVATION THEN
1632
1633 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1634 l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic';
1635 l_debug_str := 'Start Of UMX_PUB.AFTER_ACT_ACTIVATION';
1636 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1637 END IF;
1638
1639 l_person_id := p_event.getvalueforparameter ('PERSON_PARTY_ID');
1640 l_user_id := p_event.getvalueforparameter ('REQUESTED_FOR_USER_ID');
1641
1642 assign_responsibility(l_person_id, l_user_id);
1643 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1644 l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic';
1645 l_debug_str := 'End Of UMX_PUB.AFTER_ACT_ACTIVATION: l_person_id: '||l_person_id||'***l_user_id: '||l_user_id;
1646 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1647 END IF;
1648 END IF;
1649
1650 l_result := wf_rule.default_rule(p_subscription_guid, p_event);
1651
1652 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1653 l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic';
1654 l_debug_str := 'End Of umx_business_logic: l_result: '||l_result;
1655 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1656 END IF;
1657
1658 RETURN(l_result);
1659
1660 EXCEPTION
1661 WHEN OTHERS THEN
1662
1663 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1664 l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic';
1665 l_debug_str := 'Exception: SQLERRM :'||SQLERRM||'***TRACE : '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
1666 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1667 END IF;
1668 WF_CORE.CONTEXT('IGS_PE_USERID_PKG','UMX_BUSINESS_LOGIC',p_event.geteventname, p_subscription_guid, sqlerrm, sqlcode);
1669 wf_event.setErrorInfo(p_event,'ERROR');
1670 RAISE;
1671 RETURN('ERROR');
1672 END umx_business_logic;
1673
1674 PROCEDURE TestUserName
1675 (
1676 p_user_name IN VARCHAR2,
1677 x_return_status OUT NOCOPY VARCHAR2 ,
1678 x_message_app_name OUT NOCOPY VARCHAR2 ,
1679 x_message_name OUT NOCOPY VARCHAR2 ,
1680 x_message_text OUT NOCOPY VARCHAR2
1681 ) IS
1682
1683 l_encoded_message VARCHAR2 (32100);
1684
1685 BEGIN
1686 x_return_status := fnd_user_pkg.TestUserName (x_user_name => p_user_name);
1687 IF NOT (x_return_status = fnd_user_pkg.USER_OK_CREATE) THEN
1688 l_encoded_message := fnd_message.get_encoded;
1689 fnd_message.parse_encoded (encoded_message => l_encoded_message,
1690 app_short_name => x_message_app_name,
1691 message_name => x_message_name);
1692 fnd_message.set_encoded (l_encoded_message);
1693 x_message_text := fnd_message.get;
1694 END IF;
1695 END TestUserName;
1696
1697 PROCEDURE Dup_Person_Check
1698 (
1699 p_first_name IN VARCHAR2,
1700 p_last_name IN VARCHAR2,
1701 p_birth_date IN DATE,
1702 p_gender IN VARCHAR2,
1703 p_person_num IN VARCHAR2,
1704 p_pref_alt_id IN VARCHAR2,
1705 p_isApplicant IN VARCHAR2,
1706 p_Zipcode IN VARCHAR2,
1707 p_phoneCountry IN VARCHAR2,
1708 p_phoneArea IN VARCHAR2,
1709 p_phoneNumber IN VARCHAR2,
1710 p_email_address IN VARCHAR2,
1711 x_return_status OUT NOCOPY VARCHAR2,
1712 x_message_name OUT NOCOPY VARCHAR2,
1713 p_person_id OUT NOCOPY NUMBER
1714 ) AS
1715 /*
1716 || Created By :
1717 || Created On :
1718 || Purpose :
1719 || Known limitations, enhancements or remarks :
1720 || Change History :
1721 || Who When What
1722 || (reverse chronological order - newest change first)
1723 || vskumar 12-Jun-2006 added p_isApplicant, p_Zipcode , p_phoneCountry, p_phoneArea,
1724 || p_phoneNumber,p_person_id. wrote new curser c_dup_zip and logic to
1725 || check duplicate person. include new validations depending upon zip code.
1726 */
1727 PRAGMA AUTONOMOUS_TRANSACTION;
1728 l_default_date DATE := TRUNC(SYSDATE);
1729
1730 -- Hz parties check
1731 CURSOR cur_hz_parties (cp_first_name VARCHAR2, cp_last_name VARCHAR2,cp_birth_date DATE,
1732 cp_gender VARCHAR2, cp_person_num VARCHAR2, cp_pref_alt_id VARCHAR2) IS
1733 SELECT count(*), max(person_id)
1734 FROM igs_pe_person_base_v PE, IGS_PE_PERSON_ID_TYPE_V ALT
1735 WHERE pe.person_id = alt.pe_person_id(+)
1736 AND UPPER(pe.first_name) = UPPER(cp_first_name)
1737 AND UPPER(pe.last_name) = UPPER(cp_last_name)
1738 AND NVL(pe.birth_date,l_default_date) = NVL(cp_birth_date,l_default_date)
1739 AND (cp_pref_alt_id IS NULL OR alt.api_person_id = cp_pref_alt_id )
1740 AND (cp_person_num IS NULL OR pe.person_number = cp_person_num )
1741 AND NVL(pe.gender,'X') = NVL(cp_gender,'X');
1742
1743 -- FND user check
1744 CURSOR c_fnd_user(cp_party_id NUMBER) IS
1745 SELECT user_id
1746 FROM fnd_user
1747 WHERE person_party_id = cp_party_id;
1748
1749 l_count NUMBER;
1750 l_user_id FND_USER.USER_ID%TYPE;
1751 l_party_id HZ_PARTIES.PARTY_ID%TYPE;
1752
1753 l_zip_count NUMBER;
1754 l_zip_exact_match BOOLEAN;
1755 l_zip_no_match BOOLEAN;
1756 l_zip_party_id HZ_PARTIES.PARTY_ID%TYPE;
1757
1758 CURSOR c_pref_alt_id(cp_alt_id VARCHAR2) IS
1759 SELECT 1
1760 FROM igs_pe_alt_pers_id alt, igs_pe_person_id_typ typ
1761 WHERE alt.api_person_id = cp_alt_id
1762 AND typ.person_id_type = alt.person_id_type
1763 AND typ.preferred_ind = 'Y';
1764 l_alt_id_found NUMBER;
1765
1766 CURSOR c_dup_zip(cp_first_name VARCHAR2, cp_last_name VARCHAR2, cp_birth_date VARCHAR2, cp_pref_alt_id VARCHAR2,
1767 cp_person_num VARCHAR2, cp_gender VARCHAR2, cp_zipcode VARCHAR2) IS
1768 SELECT count(*), max(hz.party_id)
1769 FROM igs_pe_person_id_type_v alt, hz_parties hz, hz_person_profiles hzp
1770 WHERE hz.party_id = alt.pe_person_id(+)
1771 AND UPPER(hz.person_first_name) = UPPER(cp_first_name)
1772 AND UPPER(hz.person_last_name) = UPPER(cp_last_name)
1773 AND NVL(hzp.date_of_birth,l_default_date) = NVL(cp_birth_date,l_default_date)
1774 AND (cp_pref_alt_id IS NULL OR alt.api_person_id = cp_pref_alt_id )
1775 AND (cp_person_num IS NULL OR hz.party_number = cp_person_num )
1776 AND NVL(hzp.gender,'X') = NVL(cp_gender,'X')
1777 AND hzp.party_id = hz.party_id
1778 AND SYSDATE BETWEEN hzp.effective_start_date
1779 AND NVL(hzp.effective_end_date,sysdate)
1780 AND hz.POSTAL_CODE= cp_zipcode;
1781
1782 l_wf_event_t WF_EVENT_T;
1783 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
1784
1785 CURSOR c_seq_num IS
1786 SELECT IGS_PE_GEN_USER_S.nextval
1787 FROM DUAL;
1788
1789 ln_seq_val NUMBER;
1790
1791 BEGIN
1792 l_count :=0;
1793 l_zip_exact_match := FALSE;
1794 l_zip_no_match := FALSE;
1795
1796 OPEN cur_hz_parties(p_first_name,p_last_name,p_birth_date, p_gender,p_person_num, p_pref_alt_id);
1797 FETCH cur_hz_parties INTO l_count, l_party_id;
1798 CLOSE cur_hz_parties;
1799
1800 IF l_count > 1 THEN
1801 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1802 l_label := 'igs.plsql.igs_pe_userid_pkg.Validate_Person.Dup_Person_Check';
1803 l_debug_str := 'Multiple Matche records found with out considering ZipCode.';
1804 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1805 END IF;
1806 -- Too many users
1807 IF p_Zipcode IS NULL THEN
1808 x_return_status := 'E';
1809 FND_MESSAGE.SET_NAME('IGS', 'IGS_PE_ENTER_ZIPCODE');
1810 FND_MSG_PUB.Add;
1811 RAISE FND_API.G_EXC_ERROR;
1812 ELSE
1813 OPEN c_dup_zip(p_first_name, p_last_name, p_birth_date,
1814 p_pref_alt_id, p_person_num, p_gender, p_zipcode);
1815 FETCH c_dup_zip INTO l_zip_count, l_party_id;
1816 CLOSE c_dup_zip;
1817 IF l_zip_count = 1 THEN
1818 l_zip_exact_match := TRUE;
1819 ELSE -- No match found or Multiple matche found. Both the cases processing is same.
1820 l_zip_no_match := TRUE;
1821 END IF;
1822 END IF;
1823 END IF;
1824
1825 IF (l_count = 1 OR l_zip_exact_match = TRUE) THEN
1826 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1827 l_label := 'igs.plsql.igs_pe_userid_pkg.Validate_Person.Dup_Person_Check';
1828 l_debug_str := 'Exact match found: Person_Id = '||l_party_id;
1829 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1830 END IF;
1831 OPEN c_fnd_user(l_party_id);
1832 FETCH c_fnd_user INTO l_user_id;
1833 CLOSE c_fnd_user;
1834
1835 p_person_id := l_party_id;
1836 IF l_user_id IS NOT NULL THEN
1837 assign_responsibility(l_party_id, l_user_id);
1838 x_return_status := 'W';
1839 x_message_name := 'IGS_PE_USER_NAME_EXISTS';
1840 COMMIT;
1841 RETURN;
1842 ELSE
1843 x_return_status := 'S';
1844 RETURN;
1845 END IF;
1846 END IF;
1847
1848 IF (l_count = 0 OR l_zip_no_match) THEN
1849 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1850 l_label := 'igs.plsql.igs_pe_userid_pkg.Validate_Person.Dup_Person_Check';
1851 l_debug_str := 'Exact match not found for '||p_isApplicant;
1852 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
1853 END IF;
1854
1855 IF p_isApplicant = 'APPLICANT_YES' THEN
1856 RETURN;
1857 ELSE
1858 -- Raise business event 'oracle.apps.igs.pe.accountrequest.alumni_nomatch' setting all parameters.
1859 WF_EVENT_T.Initialize(l_wf_event_t);
1860 l_wf_event_t.setEventName( pEventName => 'oracle.apps.igs.pe.accountrequest.alumni_nomatch');
1861 wf_event.AddParameterToList ( p_name => 'PERSON_NUMBER',
1862 p_value => p_person_num,
1863 p_parameterlist => l_wf_parameter_list_t);
1864 wf_event.AddParameterToList ( p_name => 'ALT_ID',
1865 p_value => p_pref_alt_id,
1866 p_parameterlist => l_wf_parameter_list_t);
1867 wf_event.AddParameterToList ( p_name => 'GENDER',
1868 p_value => p_gender,
1869 p_parameterlist => l_wf_parameter_list_t);
1870 wf_event.AddParameterToList ( p_name => 'BIRTH_DATE',
1871 p_value => p_birth_date,
1872 p_parameterlist => l_wf_parameter_list_t);
1873 wf_event.AddParameterToList ( p_name => 'SURNAME',
1874 p_value => p_last_name,
1875 p_parameterlist => l_wf_parameter_list_t);
1876 wf_event.AddParameterToList ( p_name => 'GIVEN_NAME',
1877 p_value => p_first_name,
1878 p_parameterlist => l_wf_parameter_list_t);
1879 wf_event.AddParameterToList ( p_name => 'USER_EMAIL',
1880 p_value => p_email_address,
1881 p_parameterlist => l_wf_parameter_list_t);
1882 wf_event.AddParameterToList ( p_name => 'ZIP_CODE',
1883 p_value => p_Zipcode,
1884 p_parameterlist => l_wf_parameter_list_t);
1885 wf_event.AddParameterToList ( p_name => 'PHONE_COUNTRY_CODE',
1886 p_value => p_phoneCountry,
1887 p_parameterlist => l_wf_parameter_list_t);
1888 wf_event.AddParameterToList ( p_name => 'PHONE_AREA_CODE',
1889 p_value => p_phoneArea,
1890 p_parameterlist => l_wf_parameter_list_t);
1891 wf_event.AddParameterToList ( p_name => 'PHONE_NUMBER',
1892 p_value => p_phoneNumber,
1893 p_parameterlist => l_wf_parameter_list_t);
1894 wf_event.AddParameterToList ( p_name => 'ADMIN_USERNAME',
1895 p_value => FND_PROFILE.VALUE('IGS_PE_USER_ADMIN'),
1896 p_parameterlist => l_wf_parameter_list_t);
1897
1898 -- get the sequence value to be added to EVENT KEY to make it unique.
1899 OPEN c_seq_num;
1900 FETCH c_seq_num INTO ln_seq_val;
1901 CLOSE c_seq_num ;
1902
1903 wf_event.raise (
1904 p_event_name => 'oracle.apps.igs.pe.accountrequest.alumni_nomatch',
1905 p_event_key => ln_seq_val,
1906 p_parameters => l_wf_parameter_list_t,
1907 p_event_data => NULL
1908 );
1909
1910 x_return_status := 'W';
1911 x_message_name := 'IGS_PE_NO_MATCH_FOUND';
1912 COMMIT;
1913 RETURN;
1914 END IF;
1915 END IF;
1916
1917 END Dup_Person_Check;
1918
1919 PROCEDURE Validate_Person
1920 (
1921 p_first_name IN VARCHAR2,
1922 p_last_name IN VARCHAR2,
1923 p_birth_date IN DATE,
1924 p_gender IN VARCHAR2,
1925 p_person_num IN OUT NOCOPY VARCHAR2,
1926 p_pref_alt_id IN VARCHAR2,
1927 p_isApplicant IN VARCHAR2,
1928 p_Zipcode IN VARCHAR2,
1929 p_phoneCountry IN VARCHAR2,
1930 p_phoneArea IN VARCHAR2,
1931 p_phoneNumber IN VARCHAR2,
1932 p_email_address IN VARCHAR2,
1933 x_return_status OUT NOCOPY VARCHAR2,
1934 x_message_name OUT NOCOPY VARCHAR2,
1935 p_person_id OUT NOCOPY NUMBER
1936 ) AS
1937 /*
1938 || Created By :
1939 || Created On :
1940 || Purpose :
1941 || Known limitations, enhancements or remarks :
1942 || Change History :
1943 || Who When What
1944 || (reverse chronological order - newest change first)
1945 || vskumar 12-Jun-2006 added p_isApplicant, p_Zipcode , p_phoneCountry, p_phoneArea,
1946 || p_phoneNumber,p_person_id
1947 */
1948 CURSOR c_pref_alt_type IS
1949 SELECT person_id_type, unique_ind, description, format_mask
1950 FROM igs_pe_person_id_typ
1951 WHERE preferred_ind ='Y';
1952
1953 CURSOR c_seq_num IS
1954 SELECT IGS_PE_GEN_USER_S.nextval
1955 FROM DUAL;
1956
1957 lv_UniqueInd igs_pe_person_id_typ.UNIQUE_IND%TYPE;
1958 l_person_number hz_parties.party_number%TYPE;
1959
1960 l_person_id_typ igs_pe_person_id_typ.PERSON_ID_TYPE%TYPE;
1961 l_format igs_pe_person_id_typ.format_mask%type;
1962 l_exists VARCHAR2(1);
1963 l_alt_id_desc igs_pe_person_id_typ.description%TYPE;
1964
1965 l_event_t wf_event_t;
1966 l_parameter_list_t wf_parameter_list_t;
1967 l_wf_name VARCHAR2(8) ;
1968 ln_seq_val NUMBER;
1969
1970 BEGIN
1971 FND_MSG_PUB.INITIALIZE;
1972 Dup_Person_Check(
1973 p_first_name,
1974 p_last_name,
1975 p_birth_date,
1976 p_gender,
1977 p_person_num,
1978 p_pref_alt_id,
1979 p_isApplicant,
1980 p_Zipcode,
1981 p_phoneCountry,
1982 p_phoneArea,
1983 p_phoneNumber,
1984 p_email_address,
1985 x_return_status,
1986 x_message_name,
1987 p_person_id
1988 );
1989
1990 /*
1991 -- The return value of Dup_Person_Check is Not Null means further processing is not required since
1992 -- either with the entered values Multiple Matched persons were found in the System
1993 -- OR an exactly matched person is found.
1994 -- The further validation on the entered data data should be done in the case there is no duplicate
1995 -- person record is found and a new person needs to be created.
1996 */
1997 IF x_return_status IS NOT NULL THEN
1998 RETURN;
1999 END IF;
2000
2001 OPEN c_pref_alt_type;
2002 FETCH c_pref_alt_type INTO l_person_id_typ, lv_UniqueInd, l_alt_id_desc, l_format;
2003 CLOSE c_pref_alt_type;
2004
2005 IF TRUNC(p_birth_date) > TRUNC(SYSDATE) THEN
2006 x_return_status := 'E';
2007 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_BIRTH_DT');
2008 FND_MSG_PUB.Add;
2009 RAISE FND_API.G_EXC_ERROR;
2010 END IF;
2011
2012 IF (p_isApplicant = 'APPLICANT_YES') AND (FND_PROFILE.VALUE('HZ_GENERATE_PARTY_NUMBER') = 'N') THEN
2013 -- initialize the parameter list.
2014 wf_event_t.Initialize(l_event_t);
2015
2016 OPEN c_seq_num;
2017 FETCH c_seq_num INTO ln_seq_val;
2018 CLOSE c_seq_num ;
2019 -- set the parameters. This parameter is added with null value to initialize the parameter list.
2020 wf_event.AddParameterToList ( p_name => 'PARAMETER_DUMMY',
2021 p_value => NULL,
2022 p_parameterlist => l_parameter_list_t);
2023
2024 WF_EVENT.RAISE3(p_event_name => 'oracle.apps.igs.pe.party_number.generate',
2025 p_event_key => ln_seq_val,
2026 p_event_data => NULL,
2027 p_parameter_list => l_parameter_list_t,
2028 p_send_date => sysdate
2029 );
2030
2031 p_person_num := UPPER(WF_EVENT.getValueForParameter('PERSON_NUMBER',l_parameter_list_t));
2032 l_parameter_list_t.delete;
2033 IF (p_person_num IS NULL) THEN
2034 x_return_status := 'E';
2035 FND_MESSAGE.SET_NAME('IGS', 'IGS_PE_ACCREQ_SETUP_N_COMPL');
2036 FND_MSG_PUB.Add;
2037 RAISE FND_API.G_EXC_ERROR;
2038 END IF;
2039 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2040 l_label := 'igs.plsql.igs_pe_userid_pkg.Validate_Person';
2041 l_debug_str := 'Auto Generated Person Number is '||p_person_num;
2042 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
2043 END IF;
2044 END IF;
2045
2046 x_return_status := 'S';
2047 x_message_name := NULL;
2048 EXCEPTION
2049 WHEN OTHERS THEN
2050 x_return_status := 'E';
2051 x_message_name := SQLERRM;
2052 RAISE;
2053 END Validate_Person;
2054
2055 PROCEDURE AUTO_GENERATE_USERNAME
2056 (
2057 p_user_name OUT NOCOPY VARCHAR2 ,
2058 p_person_number IN VARCHAR2,
2059 p_first_name IN VARCHAR2,
2060 p_last_name IN VARCHAR2,
2061 p_middle_name IN VARCHAR2,
2062 p_pref_name IN VARCHAR2,
2063 p_pref_alt_id IN VARCHAR2,
2064 p_title IN VARCHAR2,
2065 p_prefix IN VARCHAR2,
2066 p_suffix IN VARCHAR2,
2067 p_gender IN VARCHAR2,
2068 p_birth_date IN DATE,
2069 p_email_address IN VARCHAR2,
2070 p_email_format IN VARCHAR2
2071 ) AS
2072
2073 CURSOR c_seq_num IS
2074 SELECT IGS_PE_GEN_USER_S.nextval
2075 FROM DUAL;
2076
2077 ln_seq_val NUMBER;
2078 l_event_t wf_event_t;
2079 l_parameter_list_t wf_parameter_list_t;
2080
2081 BEGIN
2082 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2083 l_label := 'igs.plsql.igs_pe_userid_pkg.auto_generate_username';
2084 l_debug_str := 'p_person_number: '||p_person_number||' p_first_name: '||p_first_name||' p_last_name: '||p_last_name
2085 ||' p_middle_name: '||p_middle_name||' p_pref_name: '||p_pref_name||'p_pref_alt_id: '||p_pref_alt_id
2086 ||' p_title: '||p_title||' p_prefix: '||p_prefix||' p_suffix: '||p_suffix||'p_gender'||p_gender
2087 ||' p_birth_date: '||p_birth_date||' p_email_address: '||p_email_address||' p_email_format: '||p_email_format;
2088 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
2089 END IF;
2090
2091 -- initialize the parameter list.
2092 wf_event_t.Initialize(l_event_t);
2093
2094 -- set the parameters.
2095 wf_event.AddParameterToList ( p_name => 'PERSON_NUMBER',
2096 p_value => p_person_number,
2097 p_parameterlist => l_parameter_list_t);
2098 wf_event.AddParameterToList ( p_name => 'GIVEN_NAME',
2099 p_value => p_first_name,
2100 p_parameterlist => l_parameter_list_t);
2101 wf_event.AddParameterToList ( p_name => 'SURNAME',
2102 p_value => p_last_name,
2103 p_parameterlist => l_parameter_list_t);
2104 wf_event.AddParameterToList ( p_name => 'MIDDLE_NAME',
2105 p_value => p_middle_name,
2106 p_parameterlist => l_parameter_list_t);
2107 wf_event.AddParameterToList ( p_name => 'PREF_NAME',
2108 p_value => p_pref_name,
2109 p_parameterlist => l_parameter_list_t);
2110 wf_event.AddParameterToList ( p_name => 'ALT_ID',
2111 p_value => p_pref_alt_id,
2112 p_parameterlist => l_parameter_list_t);
2113 wf_event.AddParameterToList ( p_name => 'TITLE',
2114 p_value => p_title,
2115 p_parameterlist => l_parameter_list_t);
2116 wf_event.AddParameterToList ( p_name => 'PREFIX',
2117 p_value => p_prefix,
2118 p_parameterlist => l_parameter_list_t);
2119 wf_event.AddParameterToList ( p_name => 'SUFFIX',
2120 p_value => p_suffix,
2121 p_parameterlist => l_parameter_list_t);
2122 wf_event.AddParameterToList ( p_name => 'GENDER',
2123 p_value => p_gender,
2124 p_parameterlist => l_parameter_list_t);
2125 wf_event.AddParameterToList ( p_name => 'BIRTH_DATE',
2126 p_value => p_birth_date,
2127 p_parameterlist => l_parameter_list_t);
2128 wf_event.AddParameterToList ( p_name => 'USER_EMAIL',
2129 p_value => p_email_address,
2130 p_parameterlist => l_parameter_list_t);
2131 wf_event.AddParameterToList ( p_name => 'EMAIL_FORMAT',
2132 p_value => p_email_format,
2133 p_parameterlist => l_parameter_list_t);
2134
2135 -- get the sequence value to be added to EVENT KEY to make it unique.
2136 OPEN c_seq_num;
2137 FETCH c_seq_num INTO ln_seq_val;
2138 CLOSE c_seq_num ;
2139
2140 WF_EVENT.RAISE3(p_event_name => 'oracle.apps.igs.pe.genusr',
2141 p_event_key => 'GENERATE_USERNAME'||ln_seq_val,
2142 p_event_data => NULL,
2143 p_parameter_list => l_parameter_list_t,
2144 p_send_date => sysdate
2145 );
2146
2147 p_user_name := UPPER(WF_EVENT.getValueForParameter ('USER_NAME',l_parameter_list_t));
2148
2149 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
2150 l_label := 'igs.plsql.igs_pe_userid_pkg.auto_generate_username: End';
2151 l_debug_str := 'p_user_name: '||p_user_name;
2152 fnd_log.string( fnd_log.level_procedure,l_label,l_debug_str);
2153 END IF;
2154 END AUTO_GENERATE_USERNAME;
2155
2156 PROCEDURE process_alumni_nomatch_event
2157 ( itemtype IN VARCHAR2,
2158 itemkey IN VARCHAR2,
2159 actid IN NUMBER,
2160 funcmode IN VARCHAR2,
2161 resultout OUT NOCOPY VARCHAR
2162 ) IS
2163
2164 CURSOR cur_gender (cp_gender VARCHAR2) IS
2165 SELECT meaning
2166 FROM fnd_lookup_values
2167 WHERE lookup_type = 'HZ_GENDER'
2168 AND view_application_id = 222
2169 AND language = USERENV('LANG')
2170 AND security_group_id = 0
2171 AND lookup_code = cp_gender;
2172
2173 CURSOR cur_person_id_type (cp_pers_id_type VARCHAR2) IS
2174 SELECT description
2175 FROM igs_pe_person_id_typ
2176 WHERE person_id_type = cp_pers_id_type;
2177
2178 l_gender FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
2179 l_phone_area_code HZ_CONTACT_POINTS.PHONE_AREA_CODE%TYPE;
2180 l_phone_country_code HZ_CONTACT_POINTS.PHONE_COUNTRY_CODE%TYPE;
2181 l_phone_number HZ_CONTACT_POINTS.PHONE_NUMBER%TYPE;
2182 l_gender_meaning FND_LOOKUP_VALUES.MEANING%TYPE;
2183 l_person_id_type IGS_PE_PERSON_ID_TYP.DESCRIPTION%TYPE;
2184 l_formatted_phone VARCHAR2(100);
2185 x_return_status VARCHAR2(1);
2186 x_msg_count NUMBER;
2187 x_msg_data VARCHAR2(2000);
2188
2189 BEGIN
2190 l_gender := wf_engine.GetItemAttrText (itemtype => itemtype,
2191 itemkey => itemkey,
2192 aname => 'GENDER');
2193 l_phone_area_code := wf_engine.GetItemAttrText (itemtype => itemtype,
2194 itemkey => itemkey,
2195 aname => 'PHONE_AREA_CODE');
2196 l_phone_country_code := wf_engine.GetItemAttrText (itemtype => itemtype,
2197 itemkey => itemkey,
2198 aname => 'PHONE_COUNTRY_CODE');
2199 l_phone_number := wf_engine.GetItemAttrText (itemtype => itemtype,
2200 itemkey => itemkey,
2201 aname => 'PHONE_NUMBER');
2202 OPEN cur_gender(l_gender);
2203 FETCH cur_gender INTO l_gender_meaning;
2204 CLOSE cur_gender;
2205
2206 OPEN cur_person_id_type(FND_PROFILE.VALUE('IGS_PE_ACC_REG_PERS_IDTYPE'));
2207 FETCH cur_person_id_type INTO l_person_id_type;
2208 CLOSE cur_person_id_type;
2209
2210 HZ_FORMAT_PHONE_V2PUB.phone_display (
2211 p_phone_country_code => l_phone_country_code,
2212 p_phone_area_code => l_phone_area_code,
2213 p_phone_number => l_phone_number,
2214 x_formatted_phone_number => l_formatted_phone,
2215 x_return_status => x_return_status,
2216 x_msg_count => x_msg_count,
2217 x_msg_data => x_msg_data );
2218
2219
2220 wf_engine.SetItemAttrText (itemtype => itemtype,
2221 itemkey => itemkey,
2222 aname => 'GENDER_MEANING',
2223 avalue => l_gender_meaning);
2224
2225 wf_engine.SetItemAttrText (itemtype => itemtype,
2226 itemkey => itemkey,
2227 aname => 'PERSON_ID_TYPE',
2228 avalue => l_person_id_type );
2229
2230 wf_engine.SetItemAttrText (itemtype => itemtype,
2231 itemkey => itemkey,
2232 aname => 'FORMATTED_PHONE',
2233 avalue => l_formatted_phone);
2234
2235 END process_alumni_nomatch_event;
2236
2237 PROCEDURE validate_password
2238 (
2239 p_user_name IN VARCHAR2,
2240 p_password IN VARCHAR2,
2241 x_return_status OUT NOCOPY VARCHAR2 ,
2242 x_message_text OUT NOCOPY VARCHAR2
2243 ) IS
2244
2245 l_result VARCHAR2(1);
2246 l_user_name fnd_user.user_name%TYPE;
2247 BEGIN
2248 IF p_user_name IS NULL THEN
2249 l_user_name := '-1';
2250 ELSE
2251 l_user_name := p_user_name;
2252 END IF;
2253 l_result := FND_WEB_SEC.validate_password (l_user_name, p_password);
2254 IF l_result <> 'Y' THEN
2255 x_message_text := fnd_message.get;
2256 x_return_status := 'E';
2257 END IF;
2258 END validate_password;
2259
2260 END IGS_PE_USERID_PKG;