1 PACKAGE BODY HZ_USER_PARTY_UTILS AS
2 /* $Header: ARHUSRPB.pls 120.2.12010000.2 2009/05/25 10:28:22 rgokavar ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_USER_PARTY_UTILS';
8
9 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
10 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
11 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
12 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
13 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
14 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
15
16 /*========================================================================
17 | Prototype Declarations Procedures
18 *=======================================================================*/
19
20 PROCEDURE create_per_person_party(p_per_person_id IN NUMBER,
21 x_party_id OUT NOCOPY NUMBER) ;
22
23
24 /*========================================================================
25 | Prototype Declarations Functions
26 *=======================================================================*/
27
28 /*========================================================================
29 | PUBLIC procedure get_user_party_id
30 |
31 | DESCRIPTION
32 | Tries to find a party based on email-address. If party is found
33 | the party id is returned. If the party is NOT found a new party
34 | is created and party id returned.
35 |
36 | PSEUDO CODE/LOGIC
37 |
38 | PARAMETERS
39 | pv_user_name User Name
40 | pv_first_name First Name
41 | pv_last_name Last Name
42 | pv_party_email Email address
43 |
44 | RETURNS
45 | pn_party_id Party Identifier
46 | pv_return_status Return status
47 |
48 | KNOWN ISSUES
49 |
50 | NOTES
51 |
52 | MODIFICATION HISTORY
53 | Date Author Description of Changes
54 | 25-May-2001 J Rautiainen Created
55 *=======================================================================*/
56 PROCEDURE get_user_party_id(pv_user_name IN VARCHAR2,
57 pv_first_name IN VARCHAR2,
58 pv_last_name IN VARCHAR2,
59 pv_email_address IN VARCHAR2,
60 pn_party_id OUT NOCOPY NUMBER,
61 pv_return_status OUT NOCOPY VARCHAR2) IS
62
63 /*--------------------------------------------------+
64 | Cursor for fetching the user record based on |
65 | user name passed in as parameter. FND_USER |
66 | stores user names all capital letters. |
67 +--------------------------------------------------*/
68 CURSOR user_cur(l_user_name fnd_user.user_name%TYPE) IS
69 SELECT customer_id,
70 employee_id
71 FROM fnd_user
72 WHERE user_name = UPPER(l_user_name);
73
74 /*-----------------------------------------------------+
75 | Cursor for finding a person party from person_id |
76 |Bug#8262607 Changed logic to get valid Party_id value|
77 |from latest record in PER_ALL_PEOPLE_F table. |
78 |This is FP for 11i bug 7154782. |
79 +-----------------------------------------------------*/
80 CURSOR per_person_party_cur(l_per_person_id
81 per_all_people_f.person_id%TYPE) IS
82 /* SELECT pp.party_id
83 FROM hz_parties pp
84 , per_all_people_f per
85 WHERE pp.orig_system_reference = 'PER:'||per.person_id
86 AND per.person_id = l_per_person_id;
87 */
88 SELECT party_id
89 FROM (
90 SELECT party_id , '1-HR' ORDER_BY_SOURCE , effective_end_date EFFECTIVE_DATE
91 FROM per_all_people_f
92 WHERE person_id = l_per_person_id
93 AND party_id IS NOT NULL
94 UNION ALL
95 SELECT pp.party_id , '2-TCA' ORDER_BY_SOURCE , pp.last_update_date EFFECTIVE_DATE
96 FROM hz_parties pp,
97 per_all_people_f per
98 WHERE pp.orig_system_reference = 'PER:'||per.person_id
99 AND per.person_id = l_per_person_id
100 AND per.party_id IS NULL
101 ORDER BY ORDER_BY_SOURCE ASC, EFFECTIVE_DATE desc
102 )
103 WHERE ROWNUM = 1;
104
105 /*-----------------------------------------------------+
106 | Cursor for finding a person party with email |
107 | address given as parameter. HZ_CONTACT_POINTS table |
108 | has function based index on upper(email_address) |
109 | so performance of the query is acceptable. |
110 +-----------------------------------------------------*/
111 CURSOR person_cur(l_email_address
112 hz_contact_points.email_address%TYPE) IS
113 SELECT party.party_id,
114 party.party_type,
115 party.status
116 FROM hz_contact_points cp,
117 hz_parties party
118 WHERE upper(cp.email_address) = UPPER(l_email_address)
119 AND cp.owner_table_name = 'HZ_PARTIES'
120 AND party.party_id = cp.owner_table_id
121 AND party.party_type = 'PERSON'
122 AND party.status NOT IN ('M','D','I');
123
124 /*-----------------------------------------------------+
125 | This cursor is used to find the person party |
126 | from an email assigned to a contact. |
127 +-----------------------------------------------------*/
128 CURSOR contact_cur(l_email_address
129 hz_contact_points.email_address%TYPE) IS
130 SELECT per.party_id,
131 per.party_type,
132 per.status
133 FROM hz_contact_points cp,
134 hz_relationships rel,
135 hz_parties per
136 WHERE upper(cp.email_address) = UPPER(l_email_address)
137 AND rel.party_id = cp.owner_table_id
138 AND cp.owner_table_name = 'HZ_PARTIES'
139 AND rel.subject_id = per.party_id
140 AND per.party_type = 'PERSON'
141 AND rel.directional_flag = 'F'
142 AND rel.subject_table_name = 'HZ_PARTIES'
143 AND rel.object_table_name = 'HZ_PARTIES'
144 AND rel.status NOT IN ('M','D','I');
145
146 user_rec user_cur%ROWTYPE;
147 per_person_party_rec per_person_party_cur%ROWTYPE;
148 person_rec person_cur%ROWTYPE;
149 contact_rec contact_cur%ROWTYPE;
150
151 per_rec hz_party_v2pub.person_rec_type;
152 par_rec hz_party_v2pub.party_rec_type;
153 cpoint_rec hz_contact_point_v2pub.contact_point_rec_type;
154 email_rec hz_contact_point_v2pub.email_rec_type;
155
156 lv_subject_party_number hz_parties.party_number%TYPE;
157 ln_subject_party_id hz_parties.party_id%TYPE;
158 ln_contact_point_id hz_contact_points.contact_point_id%TYPE;
159
160 ln_profile_id NUMBER;
161 ln_party_id NUMBER;
162 ln_per_person_id NUMBER;
163
164 lv_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
165 ln_msg_count NUMBER;
166 lv_msg_data VARCHAR2(2000);
167 l_generate_party_number VARCHAR2(1);
168 l_per_person_party_id NUMBER;
169
170 BEGIN
171
172 /*--------------------------------------------------+
173 | 1) Find a party from FND_USER |
174 | |
175 | If username was passed in, fetch it and check |
176 | whether party id is already defined for the user |
177 | |
178 | It is possible that pv_user_name is not a valid |
179 | fnd user since the calling program may call this |
180 | API to get a party before it creates a fnd user. |
181 | We just ignore this case because the purpose of |
182 | this is returning a party_id which should be |
183 | stampled to fnd user. |
184 +--------------------------------------------------*/
185 IF pv_user_name IS NOT NULL THEN
186
187 OPEN user_cur(pv_user_name);
188 FETCH user_cur INTO user_rec;
189 CLOSE user_cur;
190
191 /*-----------------------------------------------------+
192 | Party id is already defined for the user, return it |
193 +-----------------------------------------------------*/
194 IF user_rec.customer_id IS NOT NULL THEN
195
196 ln_party_id := user_rec.customer_id;
197
198 ELSIF user_rec.employee_id IS NOT NULL THEN
199
200 ln_per_person_id := user_rec.employee_id;
201
202 END IF;
203
204
205 END IF;
206
207 /*-----------------------------------------------------+
208 | 2) Find a party from the per person_id in FND_USER |
209 | |
210 | A party may be already created for the person |
211 | since a per person is allowed to have multiple |
212 | fnd users. |
213 | |
214 | For now we rely on the ORIG_SYSTEM_REFERENCE in |
215 | HZ_PARTIES to link to PER_ALL_PEOPLE_F |
216 | |
217 | After HR Merge into TCA, we should use the PARTY_ID |
218 | in PER_ALL_PEOPLE_F. Creating a Person Party |
219 | for a HR Person can be obsolete since a party will |
220 | be created when a HR person is created |
221 | |
222 +-----------------------------------------------------*/
223 IF ln_party_id IS NULL
224 AND ln_per_person_id IS NOT NULL THEN
225
226 OPEN per_person_party_cur(ln_per_person_id);
227 FETCH per_person_party_cur INTO per_person_party_rec;
228 CLOSE per_person_party_cur;
229
230
231 IF per_person_party_rec.party_id is NOT NULL THEN
232
233 --
234 -- Found a party with orig system reference match
235 -- person_id
236 --
237 ln_party_id := per_person_party_rec.party_id;
238
239 ELSE
240
241 --
242 -- Create a Person Party for the person_id
243 --
244 create_per_person_party(ln_per_person_id,
245 l_per_person_party_id);
246
247 ln_party_id := l_per_person_party_id;
248
249 END IF;
250
251 END IF;
252
253
254 /*---------------------------------------------------+
255 | 3) Find a person party from an email |
256 | |
257 | If party was not defined on the user. Next we try |
258 | to find it based on email address stored on the |
259 | contact point. |
260 +---------------------------------------------------*/
261 IF ln_party_id IS NULL
262 AND pv_email_address IS NOT NULL THEN
263
264 OPEN person_cur(pv_email_address);
265 FETCH person_cur INTO person_rec;
266 CLOSE person_cur;
267
268 /*-----------------------------------------------------+
269 | Party Id found with the email address can be |
270 | returned directly if the party type is 'PERSON'. |
271 +-----------------------------------------------------*/
272 IF person_rec.party_id IS NOT NULL THEN
273
274 ln_party_id := person_rec.party_id;
275
276 END IF;
277
278 END IF;
279
280 /*--------------------------------------------------+
281 | 4) Find a contact party from an email |
282 | |
283 | If party was not defined on the user, and person |
284 | party with the given email addresswas not found. |
285 | Next we try to find the subject party if an |
286 | relationship party was found. |
287 +--------------------------------------------------*/
288 IF ln_party_id IS NULL
289 AND pv_email_address IS NOT NULL THEN
290
291 OPEN contact_cur(pv_email_address);
292 FETCH contact_cur INTO contact_rec;
293 CLOSE contact_cur;
294
295 /*-----------------------------------------------------+
296 | Subject party Id found for the relationship party |
297 +-----------------------------------------------------*/
298 IF contact_rec.party_id IS NOT NULL THEN
299
300 ln_party_id := contact_rec.party_id;
301
302 END IF;
303
304 END IF;
305
306
307
308 /*------------------------------------------------------+
309 | 5) Create a person party |
310 | |
311 | Party Id could not be found, we'll create it on fly. |
312 | Person party needs at minimum first or last name |
313 +------------------------------------------------------*/
314 IF ln_party_id IS NULL
315 AND ( pv_first_name IS NOT NULL
316 OR pv_last_name IS NOT NULL) THEN
317
318 per_rec.party_rec.status := 'A';
319 per_rec.person_first_name := pv_first_name;
320 per_rec.person_last_name := pv_last_name;
321
322 l_generate_party_number := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
323
324 IF l_generate_party_number = 'N' then
325 select hz_party_number_s.nextval into par_rec.party_number from dual;
326 END IF;
327
328 per_rec.party_rec := par_rec;
329
330 /*------------------------------------------+
331 | Call TCA API to create the person party. |
332 +------------------------------------------*/
333 -- Commented call to V1 API
334 /* hz_party_v2pub.create_person(1,null,null,
335 per_rec,
336 lv_return_status,
337 ln_msg_count,
338 lv_msg_data,
339 ln_subject_party_id,
340 lv_subject_party_number,
341 ln_profile_id);
342 */
343
344 -- Made call to V2 API
345 HZ_PARTY_V2PUB.Create_Person(
346 p_init_msg_list => FND_API.G_TRUE,
347 p_person_rec => per_rec,
348 x_return_status => lv_return_status,
349 x_msg_count => ln_msg_count,
350 x_msg_data => lv_msg_data,
351 x_party_id => ln_subject_party_id,
352 x_party_number => lv_subject_party_number,
353 x_profile_id => ln_profile_id);
354
355 /*----------------------------------------------+
356 | Return the person party to the calling logic |
357 +----------------------------------------------*/
358 ln_party_id := ln_subject_party_id;
359
360 /*------------------------------------------------------+
361 | If the person party was succesfully created and a |
362 | email address was passed in, create a contact point |
363 | on the new person party. |
364 +------------------------------------------------------*/
365 IF ( lv_return_status = FND_API.G_RET_STS_SUCCESS
366 AND ln_subject_party_id IS NOT NULL
367 AND pv_email_address IS NOT NULL) THEN
368
369 cpoint_rec.contact_point_type := 'EMAIL';
370 cpoint_rec.status := 'A';
371 cpoint_rec.owner_table_name := 'HZ_PARTIES';
372 cpoint_rec.owner_table_id := ln_subject_party_id;
373 cpoint_rec.primary_flag := 'Y';
374 email_rec.email_address := pv_email_address;
375
376 /*------------------------------------------------------------------+
377 | Call TCA API to create email contact point for the person party. |
378 +------------------------------------------------------------------*/
379 -- Commented call to V1 API
380 /*
381 hz_contact_point_v2pub.create_contact_point(1,null,null,
382 cpoint_rec,null,
383 email_rec,null,null,null,
384 lv_return_status,
385 ln_msg_count,
386 lv_msg_data,
387 ln_contact_point_id);
388 */
389
390 -- Made call to V2 API
391 hz_contact_point_v2pub.create_contact_point (
392 p_init_msg_list => fnd_api.g_true,
393 p_contact_point_rec => cpoint_rec,
394 p_email_rec => email_rec,
395 x_contact_point_id => ln_contact_point_id,
396 x_return_status => lv_return_status,
397 x_msg_count => ln_msg_count,
398 x_msg_data => lv_msg_data
399 );
400 END IF;
401
402 END IF;
403
404
405 /*------------------------------------------------------+
406 | If the party was created, return status accordingly. |
407 | Note this will disregard whether the email contact |
408 | point creation was succesful or not. |
409 +------------------------------------------------------*/
410 IF ln_party_id IS NOT NULL THEN
411
412 /*------------------------------------------------------------+
413 | If the person party was created, commit the transaction. |
414 | Note this procedure is an autonomous transaction so commit |
415 | here will NOT affect the transaction on the calling logic. |
416 +------------------------------------------------------------*/
417 pv_return_status := FND_API.G_RET_STS_SUCCESS;
418 pn_party_id := ln_party_id;
419
420 ELSE
421
422 pv_return_status := FND_API.G_RET_STS_ERROR;
423 pn_party_id := to_number(null);
424
425 END IF;
426
427 EXCEPTION
428 WHEN OTHERS THEN
429 /*------------------------------------------------------------+
430 | If exception was thrown, we do not want to propagate it to |
431 | the calling logic, instead we pass error status upwards. |
432 +------------------------------------------------------------*/
433 pv_return_status := FND_API.G_RET_STS_ERROR;
434 pn_party_id := to_number(null);
435
436 END get_user_party_id;
437
438
439 /*===================================================================+
440 | PRIVATE procedure Create_Per_Person_Party
441 |
442 | DESCRIPTION
443 | Create a party for a per_all_people_f person if it has not
444 | already created. The concept here is that we believe that
445 | HR person is more reliable resource than email_address for
446 | the fnd users that are already assigned a per_all_people.
447 |
448 | We will use the first name and last name from the HR table,
449 | and use 'PER:' + person_id as orig system reference
450 | so we can use Party Merge to merge this party created from
451 | this API with the party created from TCA/HR merge later.
452 |
453 | PSEUDO CODE/LOGIC
454 |
455 | PARAMETERS
456 | p_per_person_id Person Identifier for PER_ALL_PEOPLE_F
457 |
458 |
459 | RETURNS
460 | x_party_id Party Identifier
461 | pv_return_status Return status
462 |
463 | KNOWN ISSUES
464 |
465 | NOTES
466 |
467 | MODIFICATION HISTORY
468 | Date Author Description of Changes
469 | 5-Aug-2001 Dylan Wan Created
470 *===================================================================*/
471 PROCEDURE create_per_person_party(p_per_person_id IN NUMBER,
472 x_party_id OUT NOCOPY NUMBER) IS
473
474 /*-----------------------------------------------------+
475 | Cursor for fetching a person record from |
476 | per_all_people_f for creating a person party. |
477 | |
478 | Note: We don't try to do a full mapping here. |
479 | That will be done in TCA/HR merge. |
480 | Only minimal information is populated here for UI |
481 | to display basic personal information about a user |
482 +-----------------------------------------------------*/
483 CURSOR per_person_cur(l_per_person_id
484 per_all_people_f.person_id%TYPE) IS
485 SELECT per.person_id,
486 per.first_name,
487 per.last_name,
488 per.email_address
489 FROM per_all_people_f per
490 WHERE per.person_id = l_per_person_id
491 AND TRUNC(SYSDATE) BETWEEN effective_start_date
492 AND effective_end_date;
493
494 per_person_rec per_person_cur%ROWTYPE;
495 per_rec hz_party_v2pub.person_rec_type;
496 par_rec hz_party_v2pub.party_rec_type;
497 cpoint_rec hz_contact_point_v2pub.contact_point_rec_type;
498 email_rec hz_contact_point_v2pub.email_rec_type;
499
500 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
501 l_msg_count NUMBER;
502 l_msg_data VARCHAR2(2000);
503 l_person_party_id NUMBER;
504 l_party_number hz_parties.party_number%TYPE;
505 l_person_profile_id NUMBER;
506 l_contact_point_id NUMBER;
507 l_generate_party_number VARCHAR2(1);
508
509 BEGIN
510
511 OPEN per_person_cur(p_per_person_id);
512 FETCH per_person_cur INTO per_person_rec;
513 CLOSE per_person_cur;
514
515 --
516 -- Raise an exception if PER_ALL_PEOPLE_F not found.
517 --
518
519 --
520 -- Create a Person Party
521 --
522 per_rec.party_rec.status := 'A';
523 per_rec.person_first_name := per_person_rec.first_name;
524 per_rec.person_last_name := per_person_rec.last_name;
525
526 l_generate_party_number := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
527
528 IF l_generate_party_number = 'N' then
529 select hz_party_number_s.nextval into par_rec.party_number from dual;
530 END IF;
531
532 par_rec.orig_system_reference :=
533 'PER:'||per_person_rec.person_id;
534 per_rec.party_rec := par_rec;
535
536 -- Commented call to V1 API
537 /*
538 hz_party_v2pub.create_person(
539 p_api_version => 1,
540 p_init_msg_list => 'F',
541 p_commit => 'F',
542 p_person_rec => per_rec,
543 x_return_status => l_return_status,
544 x_msg_count => l_msg_count,
545 x_msg_data => l_msg_data,
546 x_party_id => l_person_party_id,
547 x_party_number => l_party_number,
548 x_profile_id => l_person_profile_id);
549 */
550
551 -- Made call to V2 API
552 HZ_PARTY_V2PUB.Create_Person(
553 p_init_msg_list => FND_API.G_TRUE,
554 p_person_rec => per_rec,
555 x_return_status => l_return_status,
556 x_msg_count => l_msg_count,
557 x_msg_data => l_msg_data,
558 x_party_id => l_person_party_id,
559 x_party_number => l_party_number,
560 x_profile_id => l_person_profile_id);
561 --
562 -- Return the person party id to the caller
563 --
564 x_party_id := l_person_party_id;
565
566
567 --
568 -- Call TCA API to create email contact point for the person party.
569 --
570 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS
571 AND l_person_party_id IS NOT NULL
572 AND per_person_rec.email_address
573 IS NOT NULL) THEN
574
575 cpoint_rec.contact_point_type := 'EMAIL';
576 cpoint_rec.status := 'A';
577 cpoint_rec.owner_table_name := 'HZ_PARTIES';
578 cpoint_rec.owner_table_id := l_person_party_id;
579 cpoint_rec.primary_flag := 'Y';
580 email_rec.email_address := per_person_rec.email_address;
581
582 -- Commented call to V1 API
583 /*
584 hz_contact_point_v2pub.create_contact_point(
585 P_API_VERSION => 1,
586 P_INIT_MSG_LIST => 'F',
587 P_COMMIT => 'F',
588 P_CONTACT_POINTS_REC => cpoint_rec,
589 P_EDI_REC => null,
590 P_EMAIL_REC => email_rec,
591 P_PHONE_REC => null,
592 P_TELEX_REC => null,
593 P_WEB_REC => null,
594 x_return_status => l_return_status,
595 x_msg_count => l_msg_count,
596 x_msg_data => l_msg_data,
597 X_CONTACT_POINT_ID => l_contact_point_id);
598 */
599
600 -- Made call to V2 API
601 hz_contact_point_v2pub.create_contact_point (
602 p_init_msg_list => fnd_api.g_true,
603 p_contact_point_rec => cpoint_rec,
604 p_email_rec => email_rec,
605 x_contact_point_id => l_contact_point_id,
606 x_return_status => l_return_status,
607 x_msg_count => l_msg_count,
608 x_msg_data => l_msg_data
609 );
610
611 END IF;
612
613 END create_per_person_party;
614
615 END HZ_USER_PARTY_UTILS;
[Home] [Help]