[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_UWQ_PVT
Source
1 PACKAGE BODY jtf_task_uwq_pvt AS
2 /* $Header: jtfvtkqb.pls 115.6 2002/10/31 23:44:06 cjang ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | jtfvtkqs.pls |
9 | |
10 | DESCRIPTION |
11 | This package is used by JTF_TASK_UWQ_MYOWN_V |
12 | |
13 | Date Developer Change |
14 | ----------- ----------- --------------------------------------- |
15 | 10-Apr-2002 cjang Created |
16 | 12-Apr-2002 cjang If phone is not found in jtf_task_phone, |
17 | then select contact's primary phone |
18 | from hz_contact_points |
19 | 01-Oct-2002 Sanjeev K. use hz_relationships in place of |
20 | hz_party_relationships |
21 | hz_party_relationships is obsoleted. |
22 | 15-Oct-2002 cjang Fixed bug 2467845: |
23 | Modified the cursor c_phone_hz |
24 | to get the phone from relationship, |
25 | not from subject_id. |
26 | 30-Oct-2002 Chanik Jang Modified get_primary_email() |
27 | removed hz_relationships from cursor |
28 *=======================================================================*/
29 FUNCTION get_primary_phone (p_task_id IN NUMBER)
30 RETURN VARCHAR2
31 IS
32 CURSOR c_phone (b_task_id NUMBER) IS
33 SELECT decode(hcp.phone_country_code, NULL, NULL, '+'||hcp.phone_country_code||' ')||
34 '('||hcp.phone_area_code||') '||
35 hcp.phone_number||
36 decode(hcp.phone_extension, NULL, NULL, ' <'||hcp.phone_extension||'>') phone_number
37 FROM jtf_task_contacts jtc
38 , jtf_task_phones jtp
39 , hz_contact_points hcp
40 WHERE jtc.task_id = b_task_id
41 AND jtc.primary_flag = 'Y'
42 AND jtp.task_contact_id = jtc.task_contact_id
43 AND jtp.owner_table_name = 'JTF_TASK_CONTACTS'
44 AND jtp.primary_flag = 'Y'
45 AND hcp.contact_point_id = jtp.phone_id
46 AND hcp.contact_point_type = 'PHONE';
47
48 -- Fixed bug 2467845:
49 -- Removed the hz_relationships from FROM clause
50 -- not to get the phone from subject_id.
51 -- The phone must be retrieved from relationship party_id (= jtf_task_contacts.contact_id)
52 CURSOR c_phone_hz (b_task_id NUMBER) IS
53 SELECT decode(hcp.phone_country_code, NULL, NULL, '+'||hcp.phone_country_code||' ')||
54 '('||hcp.phone_area_code||') '||
55 hcp.phone_number||
56 decode(hcp.phone_extension, NULL, NULL, ' <'||hcp.phone_extension||'>') phone_number
57 FROM jtf_task_contacts jtc
58 , hz_contact_points hcp
59 WHERE jtc.task_id = b_task_id
60 AND jtc.primary_flag = 'Y'
61 AND hcp.owner_table_id = jtc.contact_id
62 AND hcp.owner_table_name = 'HZ_PARTIES'
63 AND hcp.contact_point_type = 'PHONE'
64 AND hcp.primary_flag = 'Y';
65
66 l_phone jtf_task_uwq_myown_v.primary_phone%TYPE;
67 BEGIN
68 OPEN c_phone (b_task_id => p_task_id);
69 FETCH c_phone INTO l_phone;
70
71 IF c_phone%NOTFOUND
72 THEN
73 OPEN c_phone_hz (b_task_id => p_task_id);
74 FETCH c_phone_hz INTO l_phone;
75 CLOSE c_phone_hz;
76 END IF;
77 CLOSE c_phone;
78
79 RETURN l_phone;
80
81 END get_primary_phone;
82
83 FUNCTION get_primary_email (p_task_id IN NUMBER)
84 RETURN VARCHAR2
85 IS
86 CURSOR c_email (b_task_id NUMBER) IS
87 SELECT hcp.email_address
88 FROM jtf_task_contacts jtc
89 , hz_contact_points hcp
90 WHERE jtc.task_id = b_task_id
91 AND jtc.primary_flag = 'Y'
92 AND hcp.owner_table_id = jtc.contact_id
93 AND hcp.owner_table_name = 'HZ_PARTIES'
94 AND hcp.contact_point_type = 'EMAIL'
95 AND hcp.primary_flag = 'Y';
96
97 rec_email c_email%ROWTYPE;
98 BEGIN
99 OPEN c_email (b_task_id => p_task_id);
100 FETCH c_email INTO rec_email;
101
102 IF c_email%NOTFOUND
103 THEN
104 CLOSE c_email;
105 RETURN NULL;
106 END IF;
107 CLOSE c_email;
108
109 RETURN rec_email.email_address;
110 END get_primary_email;
111
112 END jtf_task_uwq_pvt;