[Home] [Help]
PACKAGE BODY: APPS.IMC_BOOKMARK_PUB
Source
1 package body IMC_BOOKMARK_PUB AS
2 /* $Header: imcbmab.pls 120.3 2005/07/07 22:09:54 aalatasi ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IMC_BOOKMARK_PUB';
5
6 /*========================================================================
7 | Prototype Declarations Procedures
8 *=======================================================================*/
9
10 PROCEDURE create_per_person_party(p_per_person_id IN NUMBER,
11 x_party_id OUT NOCOPY NUMBER) ;
12
13 /* Private function to get party type of a party. Returns NULL if party
14 ID does not exist */
15 FUNCTION Get_Category(
16 p_bookmarked_party_id IN NUMBER
17 ) RETURN VARCHAR2
18 AS
19 l_category VARCHAR2(30);
20 BEGIN
21
22 SELECT party_type
23 INTO l_category
24 FROM hz_parties
25 WHERE party_id = p_bookmarked_party_id;
26
27 IF l_category = 'ORGANIZATION' THEN
28 RETURN G_CATEGORY_ORG;
29 ELSIF l_category = 'PERSON' THEN
30 RETURN G_CATEGORY_PERSON;
31 ELSIF l_category = 'PARTY_RELATIONSHIP' THEN
32 RETURN G_CATEGORY_REL;
33 END IF;
34
35 EXCEPTION
36 WHEN NO_DATA_FOUND THEN /* party ID of bookmark does not exist */
37 RETURN NULL;
38
39 END Get_Category;
40
41 /*=======================================================================*/
42
43 PROCEDURE Add_Bookmark(
44 p_party_id IN NUMBER,
45 p_bookmarked_party_id IN NUMBER,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY VARCHAR2,
48 x_msg_data OUT NOCOPY VARCHAR2
49 ) AS
50
51 l_return_status VARCHAR2(1);
52 l_category varchar2(30);
53 l_party_name HZ_PARTIES.PARTY_NAME%TYPE;
54 l_bookmark_num NUMBER;
55 l_bookmark_limit NUMBER;
56 l_obj_version_no NUMBER;
57 BEGIN
58
59 /* NEED TO CHECK IF BOOKMARK EXISTS BEFORE ADDING? */
60
61 /* Get party name and category of user to be stored in HZ_PARTY_PREFERENCES */
62 SELECT party_name, party_type
63 INTO l_party_name, l_category
64 FROM hz_parties
65 WHERE party_id = p_bookmarked_party_id;
66
67 IF l_category = 'ORGANIZATION' THEN
68 l_category := G_CATEGORY_ORG;
69 l_bookmark_limit := FND_PROFILE.value('IMC_MAX_ORG_BOOKMARKS');
70 ELSIF l_category = 'PERSON' THEN
71 l_category := G_CATEGORY_PERSON;
72 l_bookmark_limit := FND_PROFILE.value('IMC_MAX_PEOPLE_BOOKMARKS');
73 ELSIF l_category = 'PARTY_RELATIONSHIP' THEN
74 l_category := G_CATEGORY_REL;
75 l_bookmark_limit := FND_PROFILE.value('IMC_MAX_CONTACT_BOOKMARKS');
76 END IF;
77
78 /* Get current number of bookmarks and compare with the maximum allowed in
79 user profile */
80 SELECT count(*)
81 INTO l_bookmark_num
82 FROM HZ_PARTY_PREFERENCES
83 WHERE MODULE = G_MODLUE
84 AND CATEGORY = l_category
85 AND PREFERENCE_CODE = G_PREFERENCE_CODE
86 AND PARTY_ID = p_party_id;
87
88 IF l_bookmark_num < l_bookmark_limit THEN
89 HZ_PREFERENCE_PUB.Add(
90 p_party_id,
91 l_category,
92 G_PREFERENCE_CODE,
93 FND_API.G_MISS_CHAR,
94 p_bookmarked_party_id,
95 FND_API.G_MISS_DATE,
96 SUBSTR(l_party_name,1,10),
97 G_MODLUE,
98 FND_API.G_MISS_CHAR,
99 FND_API.G_MISS_CHAR,
100 FND_API.G_MISS_CHAR,
101 FND_API.G_MISS_CHAR,
102 FND_API.G_MISS_CHAR,
103 l_return_status,
104 x_msg_count,
105 x_msg_data,
106 l_obj_version_no);
107
108 x_return_status := l_return_status;
109 ELSE
110 x_return_status := G_MAX_REACHED_ERROR;
111 FND_MESSAGE.SET_NAME('IMC', 'IMC_MAX_BOOKMARKS_REACHED');
112 FND_MSG_PUB.ADD;
113 FND_MSG_PUB.Count_And_Get(
114 p_encoded => FND_API.G_FALSE,
115 p_count => x_msg_count,
116 p_data => x_msg_data);
117 END IF;
118
119 EXCEPTION
120 /* Exceptions may be raised from HZ_PREFERENCE_PUB */
121 WHEN FND_API.G_EXC_ERROR THEN
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 FND_MSG_PUB.Count_And_Get(
124 p_encoded => FND_API.G_FALSE,
125 p_count => x_msg_count,
126 p_data => x_msg_data);
127
128 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130 FND_MSG_PUB.Count_And_Get(
131 p_encoded => FND_API.G_FALSE,
132 p_count => x_msg_count,
133 p_data => x_msg_data);
134
135 WHEN NO_DATA_FOUND THEN
136 /* party ID of user does not exist */
137 x_return_status := FND_API.G_RET_STS_ERROR;
138 FND_MESSAGE.SET_NAME('IMC', 'IMC_INVALID_BOOKMARK_ID');
139 FND_MSG_PUB.ADD;
140 FND_MSG_PUB.Count_And_Get(
141 p_encoded => FND_API.G_FALSE,
142 p_count => x_msg_count,
143 p_data => x_msg_data);
144
145 END Add_Bookmark;
146
147 /*=======================================================================*/
148
149 PROCEDURE Add_Bookmark(
150 p_fnd_user_id IN NUMBER,
151 p_bookmarked_party_id IN NUMBER,
152 x_party_id OUT NOCOPY NUMBER,
153 x_return_status OUT NOCOPY VARCHAR2,
154 x_msg_count OUT NOCOPY VARCHAR2,
155 x_msg_data OUT NOCOPY VARCHAR2
156 ) AS
157
158 l_return_status VARCHAR2(1);
159 l_hz_return_status VARCHAR2(1);
160 l_party_id NUMBER;
161 l_user_name FND_USER.USER_NAME%TYPE;
162 l_email_address FND_USER.EMAIL_ADDRESS%TYPE;
163 l_person_id FND_USER.EMPLOYEE_ID%TYPE;
164 BEGIN
165
166 SELECT user_name, email_address, customer_id, employee_id
167 INTO l_user_name, l_email_address, l_party_id, l_person_id
168 FROM fnd_user
169 WHERE user_id = p_fnd_user_id;
170
171 IF l_party_id is NULL THEN
172 /* create new party using user name as both first and last names */
173 HZ_USER_PARTY_UTILS.get_user_party_id(
174 l_user_name,
175 l_user_name, /* first name */
176 l_user_name, /* last name */
177 l_email_address,
178 l_party_id,
179 l_hz_return_status);
180
181 IF l_hz_return_status <> FND_API.G_RET_STS_SUCCESS THEN
182 /* problem creating party for FND user */
183 x_return_status := FND_API.G_RET_STS_ERROR;
184 FND_MESSAGE.SET_NAME('IMC', 'HZ_API_OTHERS_EXCEP');
185 FND_MSG_PUB.ADD;
186 FND_MSG_PUB.Count_And_Get(
187 p_encoded => FND_API.G_FALSE,
188 p_count => x_msg_count,
189 p_data => x_msg_data);
190 END IF;
191
192 /* hook up fnd_user.customer_id and hz_parties.party_id */
193 fnd_user_pkg.updateuser(x_user_name=>l_user_name,
194 x_owner=>'SEED',
195 x_customer_id=>l_party_id);
196
197 Add_Bookmark(
198 l_party_id,
199 p_bookmarked_party_id,
200 l_return_status,
201 x_msg_count,
202 x_msg_data);
203 x_return_status := l_return_status;
204
205 ELSE
206 Add_Bookmark(
207 l_party_id,
208 p_bookmarked_party_id,
209 l_return_status,
210 x_msg_count,
211 x_msg_data);
212 x_return_status := l_return_status;
213 END IF;
214
215 EXCEPTION
216 /* Exceptions may be raised from HZ_PREFERENCE_PUB */
217 WHEN FND_API.G_EXC_ERROR THEN
218 x_return_status := FND_API.G_RET_STS_ERROR;
219 FND_MSG_PUB.Count_And_Get(
220 p_encoded => FND_API.G_FALSE,
221 p_count => x_msg_count,
222 p_data => x_msg_data);
223
224 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
226 FND_MSG_PUB.Count_And_Get(
227 p_encoded => FND_API.G_FALSE,
228 p_count => x_msg_count,
229 p_data => x_msg_data);
230
231 WHEN NO_DATA_FOUND THEN
232 /* FND user ID does not exist */
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 FND_MESSAGE.SET_NAME('IMC', 'IMC_FND_USER_NOT_EXIST');
235 FND_MSG_PUB.ADD;
236 FND_MSG_PUB.Count_And_Get(
237 p_encoded => FND_API.G_FALSE,
238 p_count => x_msg_count,
239 p_data => x_msg_data);
240 END Add_Bookmark;
241
242 /*=======================================================================*/
243
244 PROCEDURE Remove_Bookmark(
245 p_party_id IN NUMBER,
246 p_user_type IN VARCHAR2,
247 p_bookmarked_party_id IN NUMBER,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY VARCHAR2,
250 x_msg_data OUT NOCOPY VARCHAR2
251 ) AS
252
253 l_return_status VARCHAR2(1);
254 l_category varchar2(30);
255 l_user_id number;
256 BEGIN
257
258 l_category := Get_Category(p_bookmarked_party_id);
259 IF l_category IS NULL THEN
260 /* bookmark ID is invalid */
261 x_return_status := FND_API.G_RET_STS_ERROR;
262 FND_MESSAGE.SET_NAME('IMC', 'IMC_INVALID_BOOKMARK_ID');
263 FND_MSG_PUB.ADD;
264 FND_MSG_PUB.Count_And_Get(
265 p_encoded => FND_API.G_FALSE,
266 p_count => x_msg_count,
267 p_data => x_msg_data);
268 ELSE
269 IF p_user_type NOT IN (G_PARTY_USER_TYPE, G_FND_USER_TYPE) THEN
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 FND_MESSAGE.SET_NAME('IMC', 'IMC_INVALID_USER_TYPE');
272 FND_MSG_PUB.ADD;
273 FND_MSG_PUB.Count_And_Get(
274 p_encoded => FND_API.G_FALSE,
275 p_count => x_msg_count,
276 p_data => x_msg_data);
277 END IF;
278
279 IF p_user_type = G_PARTY_USER_TYPE THEN
280 l_user_id := p_party_id;
281 ELSE
282 /* find party id of FND user */
283 select customer_id into l_user_id
284 from fnd_user
285 where user_id = p_party_id;
286 END IF;
287
288
289 HZ_PREFERENCE_PUB.Remove(
290 l_user_id,
291 l_category,
292 G_PREFERENCE_CODE,
293 FND_API.G_MISS_CHAR,
294 p_bookmarked_party_id,
295 FND_API.G_MISS_DATE,
296 G_OBJECT_VERSION_NUMBER,
297 l_return_status,
298 x_msg_count,
299 x_msg_data
300 );
301
302 x_return_status := l_return_status;
303
304 END IF;
305
306 EXCEPTION
307 /* Exceptions may be raised from HZ_PREFERENCE_PUB */
308 WHEN FND_API.G_EXC_ERROR THEN
309 x_return_status := FND_API.G_RET_STS_ERROR;
310 FND_MSG_PUB.Count_And_Get(
311 p_encoded => FND_API.G_FALSE,
312 p_count => x_msg_count,
313 p_data => x_msg_data);
314
315 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
317 FND_MSG_PUB.Count_And_Get(
318 p_encoded => FND_API.G_FALSE,
319 p_count => x_msg_count,
320 p_data => x_msg_data);
321
322 WHEN OTHERS THEN
323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
324 FND_MESSAGE.SET_NAME('IMC', 'HZ_API_OTHERS_EXCEP');
325 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
326 FND_MSG_PUB.ADD;
327 FND_MSG_PUB.Count_And_Get(
328 p_encoded => FND_API.G_FALSE,
329 p_count => x_msg_count,
330 p_data => x_msg_data);
331
332 END Remove_Bookmark;
333
334 /*=======================================================================*/
335
336 PROCEDURE Get_Bookmarked_Parties(
337 p_party_id IN NUMBER,
338 p_bookmarked_party_type IN VARCHAR2,
339 x_bookmarked_party_ids OUT NOCOPY ref_cursor_bookmarks,
340 x_return_status OUT NOCOPY VARCHAR2,
341 x_msg_count OUT NOCOPY VARCHAR2,
342 x_msg_data OUT NOCOPY VARCHAR2
343 ) AS
344 l_return_status VARCHAR2(1);
345 l_party_id NUMBER;
346 BEGIN
347
348 x_return_status := FND_API.G_RET_STS_SUCCESS;
349
350 /* Check if party id is valid */
351 SELECT party_id
352 INTO l_party_id
353 FROM hz_parties
354 WHERE party_id = p_party_id;
355
356 IF p_bookmarked_party_type IS NOT NULL THEN
357
358 /* Check if bookmark type is valid */
359 IF p_bookmarked_party_type IN (G_CATEGORY_ORG, G_CATEGORY_PERSON, G_CATEGORY_REL) THEN
360 OPEN x_bookmarked_party_ids FOR
361 SELECT value_number
362 FROM HZ_PARTY_PREFERENCES
363 WHERE MODULE = G_MODLUE
364 AND CATEGORY = p_bookmarked_party_type
365 AND PREFERENCE_CODE = G_PREFERENCE_CODE
366 AND PARTY_ID = p_party_id;
367
368 ELSE
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 /* bookmark type is invalid */
371 FND_MESSAGE.SET_NAME('IMC', 'IMC_INVALID_BOOKMARK_TYPE');
372 FND_MSG_PUB.ADD;
373 FND_MSG_PUB.Count_And_Get(
374 p_encoded => FND_API.G_FALSE,
375 p_count => x_msg_count,
376 p_data => x_msg_data);
377 END IF;
378
379 ELSE /* get all bookmarks for a user */
380
381 OPEN x_bookmarked_party_ids FOR
382 SELECT value_number
383 FROM HZ_PARTY_PREFERENCES
384 WHERE MODULE = G_MODLUE
385 AND PREFERENCE_CODE = G_PREFERENCE_CODE
386 AND PARTY_ID = p_party_id;
387
388 END IF;
389
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 /* party id is invalid */
393 x_return_status := FND_API.G_RET_STS_ERROR;
394 FND_MESSAGE.SET_NAME('IMC', 'IMC_INVALID_PARTY_ID');
395 FND_MSG_PUB.ADD;
396 FND_MSG_PUB.Count_And_Get(
397 p_encoded => FND_API.G_FALSE,
398 p_count => x_msg_count,
399 p_data => x_msg_data);
400 WHEN OTHERS THEN
401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
402 FND_MESSAGE.SET_NAME('IMC', 'HZ_API_OTHERS_EXCEP');
403 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
404 FND_MSG_PUB.ADD;
405 FND_MSG_PUB.Count_And_Get(
406 p_encoded => FND_API.G_FALSE,
407 p_count => x_msg_count,
408 p_data => x_msg_data);
409
410 END Get_Bookmarked_Parties;
411
412 /*=======================================================================*/
413
414 FUNCTION Bookmark_Exists(
415 p_party_id IN NUMBER,
416 p_user_type IN VARCHAR2,
417 p_bookmarked_party_id IN NUMBER
418 ) RETURN VARCHAR2
419 AS
420 l_category varchar2(30);
421 l_user_id number;
422 l_ret varchar2(1);
423 BEGIN
424
425 l_category := Get_Category(p_bookmarked_party_id);
426 IF l_category IS NULL THEN
427 RETURN 'E';
428 END IF;
429
430 IF p_user_type NOT IN (G_PARTY_USER_TYPE, G_FND_USER_TYPE) THEN
431 RETURN 'E';
432 END IF;
433
434 IF p_user_type = G_PARTY_USER_TYPE THEN
435 l_user_id := p_party_id;
436 ELSE
437 /* find party id of FND user */
438 select customer_id into l_user_id
439 from fnd_user
440 where user_id = p_party_id;
441 END IF;
442
443 l_ret := HZ_PREFERENCE_PUB.Contains_Value(
444 l_user_id,
445 l_category,
446 G_PREFERENCE_CODE,
447 p_bookmarked_party_id);
448
449 RETURN l_ret;
450
451 EXCEPTION
452 /* Exceptions may be raised from HZ_PREFERENCE_PUB */
453 WHEN FND_API.G_EXC_ERROR THEN
454 RETURN FND_API.G_RET_STS_ERROR;
455
456 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
457 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
458
459 WHEN OTHERS THEN
460 RETURN 'E';
461
462 END Bookmark_Exists;
463
464 /*=======================================================================*/
465
466 FUNCTION Disable_Bookmark(
467 p_party_id IN NUMBER
468 ) RETURN VARCHAR2
469 AS
470 l_count NUMBER;
471 BEGIN
472
473 select count(*) into l_count
474 from hz_relationships
475 where party_id = p_party_id
476 and subject_type = 'ORGANIZATION'
477 and object_type = 'ORGANIZATION';
478
479 if l_count > 0 then
480 RETURN 'Y';
481 else
482 RETURN 'N';
483 end if;
484 END Disable_Bookmark;
485
486
487 /*===================================================================+
488 | PRIVATE procedure Create_Per_Person_Party
489 |
490 | DESCRIPTION
491 | *** Copy from ARHUSRPB.pls ***
492 | Create a party for a per_all_people_f person if it has not
493 | already created. The concept here is that we believe that
494 | HR person is more reliable resource than email_address for
495 | the fnd users that are already assigned a per_all_people.
496 |
497 | We will use the first name and last name from the HR table,
498 | and use 'PER:' + person_id as orig system reference
499 | so we can use Party Merge to merge this party created from
500 | this API with the party created from TCA/HR merge later.
501 |
502 | PSEUDO CODE/LOGIC
503 |
504 | PARAMETERS
505 | p_per_person_id Person Identifier for PER_ALL_PEOPLE_F
506 |
507 |
508 | RETURNS
509 | x_party_id Party Identifier
510 | pv_return_status Return status
511 *===================================================================*/
512 PROCEDURE create_per_person_party(p_per_person_id IN NUMBER,
513 x_party_id OUT NOCOPY NUMBER) IS
514
515 /*-----------------------------------------------------+
516 | Cursor for fetching a person record from |
517 | per_all_people_f for creating a person party. |
518 | |
519 | Note: We don't try to do a full mapping here. |
520 | That will be done in TCA/HR merge. |
521 | Only minimal information is populated here for UI |
522 | to display basic personal information about a user |
523 +-----------------------------------------------------*/
524 CURSOR per_person_cur(l_per_person_id
525 per_all_people_f.person_id%TYPE) IS
526 SELECT per.person_id,
527 per.first_name,
528 per.last_name,
529 per.email_address
530 FROM per_all_people_f per
531 WHERE per.person_id = l_per_person_id
532 AND TRUNC(SYSDATE) BETWEEN effective_start_date
533 AND effective_end_date;
534
535 per_person_rec per_person_cur%ROWTYPE;
536 per_rec hz_party_v2pub.person_rec_type;
537 par_rec hz_party_v2pub.party_rec_type;
538 cpoint_rec hz_contact_point_v2pub.contact_point_rec_type;
539 email_rec hz_contact_point_v2pub.email_rec_type;
540
541 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
542 l_msg_count NUMBER;
543 l_msg_data VARCHAR2(2000);
544 l_person_party_id NUMBER;
545 l_party_number hz_parties.party_number%TYPE;
546 l_person_profile_id NUMBER;
547 l_contact_point_id NUMBER;
548 l_generate_party_number VARCHAR2(1);
549
550 BEGIN
551
552 OPEN per_person_cur(p_per_person_id);
553 FETCH per_person_cur INTO per_person_rec;
554 CLOSE per_person_cur;
555
556 --
557 -- Raise an exception if PER_ALL_PEOPLE_F not found.
558 --
559
560 --
561 -- Create a Person Party
562 --
563 per_rec.party_rec.status := 'A';
564 per_rec.person_first_name := per_person_rec.first_name;
565 per_rec.person_last_name := per_person_rec.last_name;
566
567 l_generate_party_number := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
568
569 IF l_generate_party_number = 'N' then
570 select hz_party_number_s.nextval into par_rec.party_number from dual;
571 END IF;
572
573 par_rec.orig_system_reference :=
574 'PER:'||per_person_rec.person_id;
575 per_rec.party_rec := par_rec;
576
577 hz_party_v2pub.create_person(
578 -- p_api_version => 1,
579 p_init_msg_list => 'F',
580 -- p_commit => 'F',
581 p_person_rec => per_rec,
582 x_return_status => l_return_status,
583 x_msg_count => l_msg_count,
584 x_msg_data => l_msg_data,
585 x_party_id => l_person_party_id,
586 x_party_number => l_party_number,
587 x_profile_id => l_person_profile_id);
588
589 --
590 -- Return the person party id to the caller
591 --
592 x_party_id := l_person_party_id;
593
594
595 --
596 -- Call TCA API to create email contact point for the person party.
597 --
598 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS
599 AND l_person_party_id IS NOT NULL
600 AND per_person_rec.email_address
601 IS NOT NULL) THEN
602
603 cpoint_rec.contact_point_type := 'EMAIL';
604 cpoint_rec.status := 'A';
605 cpoint_rec.owner_table_name := 'HZ_PARTIES';
606 cpoint_rec.owner_table_id := l_person_party_id;
607 cpoint_rec.primary_flag := 'Y';
608 email_rec.email_address := per_person_rec.email_address;
609
610 hz_contact_point_v2pub.create_contact_point(
611 -- P_API_VERSION => 1,
612 P_INIT_MSG_LIST => 'F',
613 -- P_COMMIT => 'F',
614 P_CONTACT_POINT_REC => cpoint_rec,
615 P_EDI_REC => null,
616 P_EMAIL_REC => email_rec,
617 P_PHONE_REC => null,
618 P_TELEX_REC => null,
619 P_WEB_REC => null,
620 x_return_status => l_return_status,
621 x_msg_count => l_msg_count,
622 x_msg_data => l_msg_data,
623 X_CONTACT_POINT_ID => l_contact_point_id);
624
625 END IF;
626
627 END create_per_person_party;
628
629 END IMC_BOOKMARK_PUB;