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