1 PACKAGE BODY PA_DISTRIBUTION_LIST_UTILS AS
2 /* $Header: PATDLUTB.pls 120.3.12010000.7 2009/02/23 22:49:55 asahoo ship $ */
3
4 Function Check_valid_dist_list_id (
5 p_list_id in Number )
6 return boolean
7 IS
8 Cursor C1 is
9 Select 'X' from pa_distribution_lists
10 where list_id = p_list_id;
11
12 l_dummy varchar2(1);
13 l_return_status boolean := TRUE;
14 Begin
15 Open C1;
16 fetch C1 into l_dummy;
17 if (C1%NOTFOUND) then
18 l_return_status := FALSE;
19 else
20 l_return_status := TRUE;
21 end if;
22 close C1;
23
24 return l_return_status;
25
26 Exception
27 When others then
28 RAISE;
29
30 End Check_valid_dist_list_id;
31
32 Function Check_dist_list_name_exists (
33 p_list_id in number default null,
34 p_list_name in varchar2)
35 return boolean
36 IS
37 Cursor C1 is
38 Select list_id
39 from pa_distribution_lists
40 where name = p_list_name
41 and (p_list_id is null
42 OR p_list_id <> list_id) ;
43
44 l_list_id Number := 0;
45 l_return_status boolean := FALSE;
46
47 Begin
48 Open C1;
49 fetch C1 into l_list_id;
50 if C1%NOTFOUND then
51 l_return_status := FALSE;
52 else
53 l_return_status := TRUE;
54 end if;
55 close C1;
56 return l_return_status;
57
58 Exception
59 When others then
60 RAISE;
61
62 End Check_dist_list_name_exists;
63
64 --Fix for bug#8247832, added a new function
65 FUNCTION Check_dist_list_items_exists (
66 p_list_id in number,
67 p_recipient_type in varchar2,
68 p_recipient_id in varchar2)
69 return boolean
70 IS
71 Cursor C1 is
72 Select list_item_id
73 from pa_dist_list_items
74 where list_id = p_list_id
75 and recipient_type = p_recipient_type
76 and recipient_id = p_recipient_id;
77
78 l_list_item_id Number := 0;
79 l_return_status boolean := FALSE;
80
81 Begin
82 Open C1;
83 fetch C1 into l_list_item_id;
84 if C1%NOTFOUND then
85 l_return_status := FALSE;
86 else
87 l_return_status := TRUE;
88 end if;
89 close C1;
90 return l_return_status;
91
92 Exception
93 When others then
94 RAISE;
95 End Check_dist_list_items_exists;
96
97 Function get_dist_list_id (
98 p_list_name in varchar2 )
99 return number
100 IS
101 Cursor C1 is
102 Select list_id
103 from pa_distribution_lists
104 where name = p_list_name;
105
106 l_list_id Number := 0;
107
108 Begin
109 Open C1;
110 fetch C1 into l_list_id;
111 if C1%NOTFOUND then
112 l_list_id := -1;
113 end if;
114 close C1;
115 return l_list_id;
116
117 Exception
118 When others then
119 RAISE;
120 End get_dist_list_id;
121
122 Function Check_valid_recipient_type (
123 p_recipient_type in varchar2 )
124 return boolean
125 IS
126 l_return_code boolean := TRUE;
127 l_dummy varchar2(1);
128 Cursor C1 is
129 Select 'X'
130 from pa_lookups
131 where lookup_type = 'PA_RECIPIENT_TYPES'
132 and lookup_code = p_recipient_type;
133 Begin
134 open C1;
135 fetch C1 into l_dummy;
136 if (C1%NOTFOUND) then
137 l_return_code := FALSE;
138 end if;
139 close C1;
140 return l_return_code;
141 End Check_valid_recipient_type;
142
143 Function Check_valid_recipient_id (
144 p_recipient_type in varchar2,
145 p_recipient_id in varchar2 )
146 return boolean
147 IS
148 Begin
149 return TRUE;
150 End Check_valid_recipient_id;
151
152 Function Check_valid_access_level (
153 p_access_level in number)
154 return boolean
155 IS
156 Begin
157 return TRUE;
158 End Check_valid_access_level;
159
160 Function Check_valid_menu_id (
161 p_menu_id in Number )
162 return boolean
163 IS
164 Cursor C1 is
165 Select 'X' from fnd_menus
166 where menu_id = p_menu_id;
167
168 l_dummy varchar2(1);
169 l_return_status boolean := TRUE;
170 Begin
171 Open C1;
172 fetch C1 into l_dummy;
173 if (C1%NOTFOUND) then
174 l_return_status := FALSE;
175 else
176 l_return_status := TRUE;
177 end if;
178 close C1;
179
180 return l_return_status;
181
182 Exception
183 When others then
184 RAISE;
185
186 End Check_valid_menu_id;
187
188
189 FUNCTION get_access_level (
190 p_object_type IN VARCHAR2,
191 p_object_id IN VARCHAR2,
192 p_user_id IN NUMBER DEFAULT FND_GLOBAL.USER_ID,
193 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
194 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
195 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
196 p_context_object_type IN VARCHAR2 DEFAULT NULL,
197 p_context_object_id IN VARCHAR2 DEFAULT NULL
198 ) RETURN NUMBER
199 IS
200 l_access_level NUMBER;
201 l_return_value NUMBER := 0;
202 l_resource_id NUMBER; -- Bug 2717635
203 l_party_id NUMBER;
204 l_object_type VARCHAR2(30) := p_context_object_type;
205 l_object_id NUMBER := p_context_object_id;
206
207 CURSOR c_list_ids IS
208 SELECT list_id
209 FROM pa_object_dist_lists
210 WHERE object_type = p_object_type
211 AND object_id = p_object_id;
212
213 /* Bug 2717635 in the following query, selected from table pa_project_parties
214 in place of view pa_project_parties_v to improve the performance
215 Also passed another parameter to this cursor c_access_level ie
216 cp_resource_id which passes the resource_id for a corresponding user_id
217 for join with table pa_project_parties
218 Also there is a cartesian joint with fnd_user which is not required, hence removing it*/
219
220 CURSOR c_access_level(cp_list_id NUMBER) IS -- Bug 2717635
221 SELECT MAX(access_level) access_level FROM (
222 SELECT access_level
223 FROM pa_dist_list_items i,
224 pa_project_parties p -- Bug 2717635
225 WHERE i.list_id = cp_list_id
226 AND i.recipient_type = 'PROJECT_PARTY'
227 AND p.project_party_id = i.recipient_id
228 AND p.resource_id = l_resource_id -- Bug 2717635
229 AND p.object_type = l_object_type
230 AND p.object_id = l_object_id
231 UNION ALL
232 SELECT access_level
233 FROM pa_dist_list_items i,
234 pa_project_parties p -- Bug 2717635
235 /* fnd_user u */ -- Bug 2717635
236 WHERE i.list_id = cp_list_id
237 AND i.recipient_type = 'PROJECT_ROLE'
238 AND p.project_role_id = i.recipient_id
239 AND p.resource_id = l_resource_id -- Bug 2717635
240 AND p.object_type = l_object_type
241 AND p.object_id = l_object_id
242 UNION ALL
243 SELECT access_level
244 FROM pa_dist_list_items
245 WHERE list_id = cp_list_id
246 AND recipient_type = 'ALL_PROJECT_PARTIES'
247 AND EXISTS (SELECT 'Y' FROM pa_project_parties -- Bug 2717635
248 WHERE resource_id = l_resource_id -- Bug 2717635
249 AND object_type = l_object_type
250 AND object_id = l_object_id)
251 UNION ALL
252 SELECT access_level
253 FROM pa_dist_list_items
254 WHERE list_id = cp_list_id
255 AND recipient_type = 'HZ_PARTY'
256 AND recipient_id = l_party_id);
257
258 BEGIN
259 x_return_status := 'S';
260
261 IF p_context_object_type IS NULL AND
262 p_object_type = 'PA_OBJECT_PAGE_LAYOUT' THEN
263 SELECT object_type, object_id
264 INTO l_object_type, l_object_id
265 FROM pa_object_page_layouts
266 WHERE object_page_layout_id = p_object_id;
267 END IF;
268
269 l_resource_id := pa_resource_utils.get_resource_id(NULL,p_user_id); -- Bug 2717635
270 l_party_id := pa_utils.get_party_id(p_user_id);
271
272 FOR l_rec IN c_list_ids LOOP
273 --dbms_output.put_line('list_id='||l_rec.list_id);
274 OPEN c_access_level(l_rec.list_id); -- Bug 2717635
275 FETCH c_access_level INTO l_access_level;
276 --dbms_output.put_line('access_level='||l_access_level);
277 IF c_access_level%FOUND AND l_access_level>l_return_value THEN
278 l_return_value := l_access_level;
279 END IF;
280 END LOOP;
281
282 RETURN l_return_value;
283
284 EXCEPTION
285 WHEN FND_API.G_EXC_ERROR THEN
286 x_return_status := 'E';
287
288 WHEN OTHERS THEN
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_OBJECT_DIST_LISTS_UTILS',
291 p_procedure_name => 'GET_ACCESS_LEVEL',
292 p_error_text => SUBSTRB(SQLERRM,1,240));
293 END get_access_level;
294
295 PROCEDURE get_dist_list (
296 p_object_type IN VARCHAR2,
297 p_object_id IN VARCHAR2,
298 p_access_level IN NUMBER,
299 x_user_names OUT NOCOPY PA_VC_1000_150, --File.Sql.39 bug 4440895
300 x_full_names OUT NOCOPY PA_VC_1000_150, --File.Sql.39 bug 4440895
301 x_email_addresses OUT NOCOPY PA_VC_1000_150, --File.Sql.39 bug 4440895
302 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
303 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
304 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
305 )
306 IS
307 l_object_type VARCHAR2(30);
308 l_object_id NUMBER;
309 l_return_value NUMBER := 0;
310 i NUMBER;
311
312 CURSOR c_list_ids IS
313 SELECT list_id
314 FROM pa_object_dist_lists
315 WHERE object_type = p_object_type
316 AND object_id = p_object_id;
317
318 --Bug# 4284420: Modified the cursor to avoid selecting end-dated fnd_users
319 CURSOR c_dist_list(cp_list_id NUMBER) IS
320 SELECT DISTINCT user_name, full_name, email_address FROM (
321 SELECT p.user_name user_name,
322 p.resource_source_name full_name,
323 p.email_address email_address
324 FROM pa_dist_list_items i,
325 pa_project_parties_v p,
326 fnd_user u
327 WHERE i.list_id = cp_list_id
328 AND i.access_level >= p_access_level
329 AND i.recipient_type = 'PROJECT_PARTY'
330 AND p.project_party_id = i.recipient_id
331 AND p.object_type = l_object_type
332 AND p.object_id = l_object_id
333 AND u.user_name=p.user_name
334 AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
335 AND trunc(sysdate) between p.start_date_active and nvl(p.end_date_active,sysdate + 1)
336 UNION ALL
337 SELECT p.user_name user_name,
338 p.resource_source_name full_name,
339 p.email_address email_address
340 FROM pa_dist_list_items i,
341 pa_project_parties_v p,
342 fnd_user u
343 WHERE i.list_id = cp_list_id
344 AND i.access_level >= p_access_level
345 AND i.recipient_type = 'PROJECT_ROLE'
346 AND p.project_role_id = i.recipient_id
347 AND p.object_type = l_object_type
348 AND p.object_id = l_object_id
349 AND u.user_name=p.user_name
350 AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
351 AND trunc(sysdate) between p.start_date_active and nvl(p.end_date_active,sysdate + 1)
352 UNION ALL
353 SELECT p.user_name user_name,
354 p.resource_source_name full_name,
355 p.email_address email_address
356 FROM pa_project_parties_v p,
357 fnd_user u
358 WHERE EXISTS (SELECT 1 FROM pa_dist_list_items i
359 WHERE i.list_id = cp_list_id
360 AND i.access_level >= p_access_level
361 AND i.recipient_type = 'ALL_PROJECT_PARTIES')
362 AND p.object_type = l_object_type
363 AND p.object_id = l_object_id
364 AND u.user_name=p.user_name
365 AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
366 AND trunc(sysdate) between p.start_date_active and nvl(p.end_date_active,sysdate + 1)
367 UNION ALL
368 SELECT u.user_name user_name,
369 hzp.party_name full_name,
370 hzp.email_address email_address
371 FROM pa_dist_list_items i,
372 hz_parties hzp,
373 fnd_user u
374 WHERE i.list_id = cp_list_id
375 AND i.access_level >= p_access_level
376 AND i.recipient_type = 'HZ_PARTY'
377 AND hzp.party_id = i.recipient_id
378 AND SUBSTR(hzp.orig_system_reference, 1, 3) <> 'PER'
379 AND u.person_party_id (+) = hzp.party_id
380 -- Bug 4527617. Replaced customer_id with person_party_id.
381 AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
382 UNION ALL
383 SELECT u.user_name user_name,
384 per.full_name full_name,
385 per.email_address email_address
386 FROM pa_dist_list_items i,
387 per_all_people_f per,
388 fnd_user u
389 WHERE i.list_id = cp_list_id
390 AND i.access_level >= p_access_level
391 AND i.recipient_type = 'HZ_PARTY'
392 AND per.party_id = i.recipient_id
393 --Bug 2722021 added filter on eff dates
394 AND (TRUNC(SYSDATE) BETWEEN TRUNC(per.effective_start_date)
395 AND TRUNC(per.effective_end_date))
396 AND u.employee_id (+) = per.person_id
397 AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
398 UNION ALL
399 SELECT NULL user_name,
400 NULL full_name,
401 recipient_id email_address
402 FROM pa_dist_list_items i
403 WHERE i.list_id = cp_list_id
404 AND i.access_level >= p_access_level
405 AND i.recipient_type = 'EMAIL_ADDRESS');
406
407 BEGIN
408 x_return_status := 'S';
409
410 IF p_object_type = 'PA_OBJECT_PAGE_LAYOUT' THEN
411 SELECT object_type, object_id
412 INTO l_object_type, l_object_id
413 FROM pa_object_page_layouts
414 WHERE object_page_layout_id = p_object_id;
415 --dbms_output.put_line('object_id='||l_object_id);
416 ELSE
417 --dbms_output.put_line('unhandled object type');
418 x_return_status := 'U';
419 RETURN;
420 END IF;
421
422 FOR l_rec IN c_list_ids LOOP
423 --dbms_output.put_line('list_id='||l_rec.list_id);
424 FOR l_rec1 IN c_dist_list(l_rec.list_id) LOOP
425 --dbms_output.put_line('email='||l_rec1.email_address);
426 IF x_user_names IS NULL THEN
427 x_user_names := PA_VC_1000_150(l_rec1.user_name);
428 ELSE
429 x_user_names.EXTEND;
430 x_user_names(x_user_names.COUNT) := l_rec1.user_name;
431 END IF;
432
433 IF x_full_names IS NULL THEN
434 x_full_names := PA_VC_1000_150(l_rec1.full_name);
435 ELSE
436 x_full_names.EXTEND;
437 x_full_names(x_full_names.COUNT) := l_rec1.full_name;
438 END IF;
439
440 IF x_email_addresses IS NULL THEN
441 x_email_addresses := PA_VC_1000_150(l_rec1.email_address);
442 ELSE
443 x_email_addresses.EXTEND;
444 x_email_addresses(x_email_addresses.COUNT) := l_rec1.email_address;
445 END IF;
446
447 END LOOP;
448 END LOOP;
449
450 EXCEPTION
451 WHEN FND_API.G_EXC_ERROR THEN
452 x_return_status := 'E';
453 WHEN OTHERS THEN
454 --dbms_output.put_line('unhandled exception');
455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
456 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_OBJECT_DIST_LISTS_UTILS',
457 p_procedure_name => 'GET_ACCESS_LEVEL',
458 p_error_text => SUBSTRB(SQLERRM,1,240));
459 END get_dist_list;
460
461
462 /* Added for Bug 6843694
463 * Returns the list of people to whom email notification has to be sent (username, fullname and email id)
464 * Used in the Status Report flow
465 */
466 PROCEDURE get_dist_list_email (
467 p_object_type IN VARCHAR2,
468 p_object_id IN VARCHAR2,
469 p_access_level IN NUMBER,
470 x_user_names OUT NOCOPY PA_VC_1000_150,
471 x_full_names OUT NOCOPY PA_VC_1000_150,
472 x_email_addresses OUT NOCOPY PA_VC_1000_150,
473 x_return_status OUT NOCOPY VARCHAR2,
474 x_msg_count OUT NOCOPY NUMBER,
475 x_msg_data OUT NOCOPY VARCHAR2
476 )
477 IS
478 l_object_type VARCHAR2(30);
479 l_object_id NUMBER;
480 l_return_value NUMBER := 0;
481 i NUMBER;
482
483 CURSOR c_list_ids IS
484 SELECT list_id
485 FROM pa_object_dist_lists
486 WHERE object_type = p_object_type
487 AND object_id = p_object_id;
488
489 /*
490 * 1. Modified this cursor to filter out records based on the value of the email column
491 * in pa_dist_list_items.
492 * 2. Retrieve all team members if 'Send Status Report by email to all project team members'
493 * is selected.
494 * 3. Removed the condition SELECT for ALL_PROJECT_PARTIES as this has nothing to do with
495 * email notifications.
496 * 4. Null handled the SELECTs for customers and non team members.
497 */
498 CURSOR c_dist_list(cp_list_id NUMBER) IS
499 SELECT DISTINCT user_name, full_name, email_address FROM (
500 SELECT p.user_name user_name,
501 p.resource_source_name full_name,
502 p.email_address email_address
503 FROM pa_dist_list_items i,
504 pa_project_parties_v p,
505 fnd_user u
506 WHERE i.list_id = cp_list_id
507 AND i.access_level >= p_access_level
508 AND i.recipient_type = 'PROJECT_PARTY'
509 AND p.project_party_id = i.recipient_id
510 AND p.object_type = l_object_type
511 AND p.object_id = l_object_id
512 AND u.user_name=p.user_name
513 AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
514 AND trunc(sysdate) between p.start_date_active and nvl(p.end_date_active,sysdate + 1)
515 AND NVL(i.email, 'Y') <> 'N'
516 UNION ALL
517 SELECT p.user_name user_name,
518 p.resource_source_name full_name,
519 p.email_address email_address
520 FROM pa_dist_list_items i,
521 pa_project_parties_v p,
522 fnd_user u
523 WHERE i.list_id = cp_list_id
524 AND i.access_level >= p_access_level
525 AND i.recipient_type = 'PROJECT_ROLE'
526 AND p.project_role_id = i.recipient_id
527 AND p.object_type = l_object_type
528 AND p.object_id = l_object_id
529 AND u.user_name=p.user_name
530 AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
531 AND trunc(sysdate) between p.start_date_active and nvl(p.end_date_active,sysdate + 1)
532 AND NVL(i.email, 'Y') <> 'N'
533 UNION ALL
534 SELECT u.user_name user_name,
535 hzp.party_name full_name,
536 hzp.email_address email_address
537 FROM pa_dist_list_items i,
538 hz_parties hzp,
539 fnd_user u
540 WHERE i.list_id = cp_list_id
541 AND i.access_level >= p_access_level
542 AND i.recipient_type = 'HZ_PARTY'
543 AND hzp.party_id = i.recipient_id
544 AND SUBSTR(hzp.orig_system_reference, 1, 3) <> 'PER'
545 AND u.customer_id (+) = hzp.party_id
546 AND ((u.customer_id IS NULL) OR
547 (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE)))
548 AND nvl(i.email, 'Y') <> 'N'
549 UNION ALL
550 SELECT u.user_name user_name,
551 per.full_name full_name,
552 per.email_address email_address
553 FROM pa_dist_list_items i,
554 per_all_people_f per,
555 fnd_user u
556 WHERE i.list_id = cp_list_id
557 AND i.access_level >= p_access_level
558 AND i.recipient_type = 'HZ_PARTY'
559 AND per.party_id = i.recipient_id
560 AND (TRUNC(SYSDATE) BETWEEN TRUNC(per.effective_start_date)
561 AND TRUNC(per.effective_end_date))
562 AND u.employee_id (+) = per.person_id
563 AND ((u.employee_id IS NULL) OR
564 (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE)))
565 AND nvl(i.email, 'Y') <> 'N'
566 UNION ALL
567 SELECT NULL user_name,
568 NULL full_name,
569 recipient_id email_address
570 FROM pa_dist_list_items i
571 WHERE i.list_id = cp_list_id
572 AND i.access_level >= p_access_level
573 AND i.recipient_type = 'EMAIL_ADDRESS'
574 AND nvl(i.email, 'Y') <> 'N'
575 -- Send Status Report by email to all project team members
576 UNION ALL
577 SELECT DISTINCT p.user_name user_name,
578 p.resource_source_name full_name,
579 p.email_address email_address
580 FROM pa_project_parties_v p,
581 fnd_user u
582 WHERE EXISTS ( SELECT 1
583 FROM pa_dist_list_items i
584 WHERE i.list_id = cp_list_id
585 AND i.access_level >= p_access_level
586 AND i.recipient_type = 'EMAIL_ALL'
587 )
588 AND p.object_type = l_object_type
589 AND p.object_id = l_object_id
590 AND u.user_name=p.user_name
591 AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
592 AND trunc(sysdate) between p.start_date_active and nvl(p.end_date_active,sysdate + 1)
593 AND p.party_type IN ('EMPLOYEE', 'PERSON')
594 );
595
596
597 BEGIN
598 x_return_status := 'S';
599
600 IF p_object_type = 'PA_OBJECT_PAGE_LAYOUT' THEN
601 SELECT object_type, object_id
602 INTO l_object_type, l_object_id
603 FROM pa_object_page_layouts
604 WHERE object_page_layout_id = p_object_id;
605
606 ELSE
607 x_return_status := 'U';
608 RETURN;
609 END IF;
610
611 FOR l_rec IN c_list_ids LOOP
612 FOR l_rec1 IN c_dist_list(l_rec.list_id) LOOP
613 IF x_user_names IS NULL THEN
614 x_user_names := PA_VC_1000_150(l_rec1.user_name);
615 ELSE
616 x_user_names.EXTEND;
617 x_user_names(x_user_names.COUNT) := l_rec1.user_name;
618 END IF;
619
620 IF x_full_names IS NULL THEN
621 x_full_names := PA_VC_1000_150(l_rec1.full_name);
622 ELSE
623 x_full_names.EXTEND;
624 x_full_names(x_full_names.COUNT) := l_rec1.full_name;
625 END IF;
626
627 IF x_email_addresses IS NULL THEN
628 x_email_addresses := PA_VC_1000_150(l_rec1.email_address);
629 ELSE
630 x_email_addresses.EXTEND;
631 x_email_addresses(x_email_addresses.COUNT) := l_rec1.email_address;
632 END IF;
633
634 END LOOP;
635 END LOOP;
636
637 EXCEPTION
638 WHEN FND_API.G_EXC_ERROR THEN
639 x_return_status := 'E';
640 WHEN OTHERS THEN
641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
642 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_OBJECT_DIST_LISTS_UTILS',
643 p_procedure_name => 'GET_DIST_LIST_EMAIL',
644 p_error_text => SUBSTRB(SQLERRM,1,240));
645 END get_dist_list_email;
646
647 -- added by atwang....copies the distribution list
648
649 PROCEDURE copy_dist_list
650 ( p_object_type_from IN VARCHAR2,
651 p_object_id_from IN NUMBER,
652 p_object_type_to IN VARCHAR2,
653 p_object_id_to IN NUMBER,
654 P_CREATED_BY in NUMBER default fnd_global.user_id,
655 P_CREATION_DATE in DATE default sysdate,
656 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
657 P_LAST_UPDATE_DATE in DATE default sysdate,
658 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
659 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
660 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
661 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
662 )
663 IS
664
665 -- Enter the procedure variables here. As shown below
666 l_object_type VARCHAR2(30);
667 l_object_id NUMBER;
668 l_resource_type_id NUMBER(15);
669 l_resource_source_id NUMBER(15);
670 l_project_role_id NUMBER(15);
671 l_list_id_from NUMBER(15);
672 l_list_id_to NUMBER(15);
673 l_recipient_type VARCHAR2(30);
674 l_recipient_id VARCHAR2(150);
675 l_access_level NUMBER(2);
676 l_email VARCHAR2(2);
677 l_menu_id NUMBER(15);
678 l_list_item_id NUMBER(15);
679 l_count NUMBER(15);
680 l_project_party_id NUMBER(15);
681
682
683
684 CURSOR get_object IS SELECT object_id, object_type
685 FROM pa_object_page_layouts
686 WHERE page_type_code = 'PPR'
687 AND object_page_layout_id = p_object_id_to;
688
689 CURSOR get_list_id_from IS SELECT list_id
690 FROM pa_object_dist_lists
691 WHERE object_type = p_object_type_from
692 AND object_id = p_object_id_from;
693
694 CURSOR get_list_id_to IS SELECT list_id
695 FROM pa_object_dist_lists
696 WHERE object_type = p_object_type_to
697 AND object_id = p_object_id_to;
698
699 CURSOR get_project_party_to IS SELECT b.project_party_id
700 FROM pa_project_parties a, pa_project_parties b
701 WHERE a.project_party_id = l_recipient_id
702 AND b.object_type = a.object_type
703 AND b.object_id = l_object_id
704 AND b.resource_type_id = a.resource_type_id
705 AND b.resource_source_id = a.resource_source_id
706 AND b.project_role_id = a.project_role_id;
707
708 CURSOR get_list_items IS SELECT recipient_type, recipient_id, access_level, email, menu_id
709 FROM pa_dist_list_items
710 WHERE list_id = l_list_id_from;
711
712
713 BEGIN
714
715 -- Check to see that no distribution list currently exists
716 OPEN get_list_id_to;
717 FETCH get_list_id_to INTO l_list_id_to;
718 IF get_list_id_to%FOUND THEN
719 CLOSE get_list_id_to;
720 x_return_status := FND_API.G_RET_STS_ERROR;
721 fnd_message.set_name('PA','PA_DL_LIST_ID_INV');
722 fnd_msg_pub.add();
723 fnd_msg_pub.count_and_get(p_count => x_msg_count,
724 p_data => x_msg_data);
725 return;
726 END IF;
727 CLOSE get_list_id_to;
728 OPEN get_object;
729 FETCH get_object INTO l_object_id,l_object_type;
730 CLOSE get_object;
731
732 OPEN get_list_id_from;
733 FETCH get_list_id_from INTO l_list_id_from;
734 CLOSE get_list_id_from;
735 select pa_distribution_lists_s.nextVal into l_list_id_to from dual;
736
737 -- create new entry into pa_distribution_lists
738
739 PA_DISTRIBUTION_LISTS_PVT.CREATE_DIST_LIST(P_VALIDATE_ONLY => 'F',
740 P_LIST_ID => l_list_id_to,
741 P_NAME => l_list_id_to,
742 P_DESCRIPTION => l_list_id_to,
743 x_return_status => x_return_status,
744 x_msg_count => x_msg_count,
745 x_msg_data => x_msg_data);
746 IF x_return_status<>'S' THEN
747 RETURN;
748 END IF;
749
750 -- create new entry into pa_object_dist_lists table
751
752 PA_OBJECT_DIST_LISTS_PVT.CREATE_OBJECT_DIST_LIST(P_VALIDATE_ONLY => 'F',
753 P_LIST_ID => l_list_id_to,
754 P_OBJECT_TYPE => p_object_type_to,
755 P_OBJECT_ID => p_object_id_to,
756 x_return_status => x_return_status,
757 x_msg_count => x_msg_count,
758 x_msg_data => x_msg_data);
759 IF x_return_status<>'S' THEN
760 RETURN;
761 END IF;
762
763 --Loop to copy all items
764 FOR c_list_item IN get_list_items LOOP
765 l_recipient_type := c_list_item.recipient_type;
766 l_recipient_id := c_list_item.recipient_id;
767 l_access_level := c_list_item.access_level;
768 l_email := c_list_item.email;
769 l_menu_id := c_list_item.menu_id;
770
771 -- If the item is a project_party type, need to find the corresponding project_party_id for
772 -- the new object
773
774 IF (l_recipient_type = 'PROJECT_PARTY') THEN
775 OPEN get_project_party_to;
776 FETCH get_project_party_to INTO l_project_party_id;
777 CLOSE get_project_party_to;
778 l_recipient_id := l_project_party_id;
779 END IF;
780
781 -- Fix for bug#8247832, Check_dist_list_items_exists function is called before calling CREATE_DIST_LIST_ITEM
782 -- and if there already exists, update_dist_list_item is called
783 IF (PA_DISTRIBUTION_LIST_UTILS.Check_dist_list_items_exists(
784 p_list_id => l_list_id_to,
785 p_recipient_type => l_recipient_type,
786 p_recipient_id => l_recipient_id)) THEN
787
788 PA_DISTRIBUTION_LISTS_PVT.UPDATE_DIST_LIST_ITEM(P_VALIDATE_ONLY => 'F',
789 P_LIST_ITEM_ID => l_list_item_id,
790 P_LIST_ID => l_list_id_to,
791 P_RECIPIENT_TYPE => l_recipient_type,
792 P_RECIPIENT_ID => l_recipient_id,
793 P_ACCESS_LEVEL => l_access_level,
794 P_EMAIL => l_email,
795 P_MENU_ID => l_menu_id,
796 x_return_status => x_return_status,
797 x_msg_count => x_msg_count,
798 x_msg_data => x_msg_data);
799 IF x_return_status<>'S' THEN
800 RETURN;
801 END IF;
802 ELSE
803
804
805 -- Added for bug 7585916. Receipient Id null should coz failure the processing
806 -- copy project we can avoid creating dist list items for such receipients and
807 -- proceed.
808 IF l_recipient_id IS NOT NULL THEN
809
810 select pa_dist_list_items_s.nextVal into l_list_item_id from dual;
811
812 PA_DISTRIBUTION_LISTS_PVT.CREATE_DIST_LIST_ITEM(P_VALIDATE_ONLY => 'F',
813 P_LIST_ITEM_ID => l_list_item_id,
814 P_LIST_ID => l_list_id_to,
815 P_RECIPIENT_TYPE => l_recipient_type,
816 P_RECIPIENT_ID => l_recipient_id,
817 P_ACCESS_LEVEL => l_access_level,
818 P_EMAIL => l_email,
819 P_MENU_ID => l_menu_id,
820 x_return_status => x_return_status,
821 x_msg_count => x_msg_count,
822 x_msg_data => x_msg_data);
823
824 IF x_return_status<>'S' THEN
825 RETURN;
826 END IF;
827
828 END IF;
829
830 END IF;
831 END LOOP;
832 END;
833
834 Function Check_valid_dist_list_item_id (
835 p_list_item_id in Number )
836 return VARCHAR2
837 IS
838 Cursor C1 is
839 Select 'X' from pa_dist_list_items
840 where list_item_id = p_list_item_id;
841
842 l_dummy varchar2(1);
843 l_return_status varchar2(1) := 'T';
844 Begin
845 Open C1;
846 fetch C1 into l_dummy;
847 if (C1%NOTFOUND) then
848 l_return_status := 'F';
849 else
850 l_return_status := 'T';
851 end if;
852 close C1;
853
854 return l_return_status;
855
856 Exception
857 When others then
858 RAISE;
859
860 End Check_valid_dist_list_item_id;
861
862
863
864
865 END PA_DISTRIBUTION_LIST_UTILS;
[Home] [Help]