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