1 PACKAGE BODY FUN_CONTACTUS_EMAIL AS
2 /* $Header: FUN_CONTACTUS_EMAIL.plb 120.1 2006/05/26 22:55:02 skaneshi noship $ */
3
4 -- Internal constants
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FUN_CONTACTUS_EMAIL';
7 G_RETURN_SUCCESS CONSTANT VARCHAR2(1) := 'S';
8 G_RETURN_FAIL CONSTANT VARCHAR2(1) := 'F';
9
10 /*========================================================================
11 | PUBLIC PROCEDURE send_notification
12 |
13 | DESCRIPTION
14 | This procedure sets the workflow attributes and starts the workflow
15 | process for workflow item FUNCNCT. The workflow process simply sends
16 | a notification.
17 |
18 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
19 | Called from BC4J.
20 |
21 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
22 |
23 | RETURNS
24 |
25 | PARAMETERS
26 | p_request_id IN Contact Us request Id, workflow key
27 | p_from_role IN Role name for person submitting the request
28 | p_to_email_address IN Email address for the help desk analyst
29 | p_problem_summary IN Problem summary entered by the submitter
30 | p_alternative_contact IN Alternative contact information for the
31 | submitter
32 |
33 | MODIFICATION HISTORY
34 | Date Author Description of Changes
35 | 22-Apr-2005 SKANESHI Created
36 |
37 *=======================================================================*/
38 PROCEDURE send_notification(p_request_id IN NUMBER,
39 p_from_role IN VARCHAR2,
40 p_to_email_address IN VARCHAR2,
41 p_problem_summary IN VARCHAR2,
42 p_alternative_contact IN VARCHAR2)
43 IS
44 l_text_offset NUMBER :=0;
45 l_text_name_array wf_engine.nametabtyp;
46 l_text_value_array wf_engine.texttabtyp;
47
48 l_role_name VARCHAR2(250);
49 l_role_display_name VARCHAR2(250);
50
51 l_display_name VARCHAR2(250);
52 l_email_address VARCHAR2(250);
53 l_notification_preference VARCHAR2(250);
54 l_language VARCHAR2(250);
55 l_territory VARCHAR2(250);
56
57 BEGIN
58
59 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
60 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME,
61 'start send_notification');
62 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME,
63 'parameters: p_request_id = ' || p_request_id ||
64 ', p_from_role = ' || p_from_role ||
65 ', p_to_email_address = ' || p_to_email_address ||
66 ', p_problem_summary = ' || p_problem_summary ||
67 ', p_alternative_contact = ' || p_alternative_contact);
68 end if;
69
70 /*------------------------------------------------------------------+
71 | Create a process using the WF definition for sending emails to |
72 | helpdesk analyst |
73 +------------------------------------------------------------------*/
74 wf_engine.createprocess('FUNCTUS',
75 p_request_id,
76 'FUN_SEND_EMAIL',
77 '',
78 p_from_role);
79
80 /*------------------------------------------------------------------+
81 | Get sender information |
82 +------------------------------------------------------------------*/
83 wf_directory.getroleinfo(p_from_role,
84 l_display_name,
85 l_email_address,
86 l_notification_preference,
87 l_language,
88 l_territory);
89
90 /*------------------------------------------------------------------+
91 | Set attribute values |
92 +------------------------------------------------------------------*/
93 wf_engine.setitemattrnumber('FUNCTUS', p_request_id,
94 'FUN_REQUEST_ID', p_request_id);
95
96 /*------------------------------------------------------------------+
97 | Set the recipient to adhoc role |
98 +------------------------------------------------------------------*/
99 IF (p_to_email_address IS NOT NULL) THEN
100 l_role_name := upper(p_to_email_address);
101 l_role_display_name := p_to_email_address;
102
103 IF (Wf_Directory.getRoleDisplayName(l_role_name) IS NULL) THEN
104 -- Create adhoc role if does not already exist
105 Wf_Directory.CreateAdHocRole(role_name => l_role_name,
106 role_display_name => l_role_display_name,
107 email_address => p_to_email_address);
108 END IF;
109
110 -- Set role attribute
111 l_text_offset := l_text_offset + 1;
112 l_text_name_array(l_text_offset) := 'FUN_RECIPIENT';
113 l_text_value_array(l_text_offset) := l_role_name;
114
115 end if;
116
117 /*------------------------------+
118 | Set the sender information |
119 +------------------------------*/
120 l_text_offset := l_text_offset + 1;
121 l_text_name_array(l_text_offset) := 'FUN_SENDER';
122 l_text_value_array(l_text_offset) := p_from_role;
123
124 l_text_offset := l_text_offset + 1;
125 l_text_name_array(l_text_offset) := 'FUN_SENDER_DISPLAY_NAME';
126 l_text_value_array(l_text_offset) := l_Display_Name;
127
128 l_text_offset := l_text_offset + 1;
129 l_text_name_array(l_text_offset) := 'FUN_SENDER_EMAIL_ADDRESS';
130 l_text_value_array(l_text_offset) := l_Email_Address;
131
132 l_text_offset := l_text_offset + 1;
133 l_text_name_array(l_text_offset) := 'FUN_PROBLEM_SUMMARY';
134 l_text_value_array(l_text_offset) := p_problem_summary;
135
136 l_text_offset := l_text_offset + 1;
137 l_text_name_array(l_text_offset) := 'FUN_ALTERNATIVE_CONTACT';
138 l_text_value_array(l_text_offset) := p_alternative_contact;
139
140 wf_engine.setitemattrtextarray('FUNCTUS', p_request_id,
141 l_text_name_array, l_text_value_array);
142
143 /*----------------------------------+
144 | Start the notification process |
145 +----------------------------------*/
146 wf_engine.startprocess('FUNCTUS',
147 p_request_id);
148
149
150 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
151 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME,
152 'end send_notification');
153 end if;
154
155 END send_notification;
156
157 /*========================================================================
158 | PUBLIC PROCEDURE get_user_info
159 |
160 | DESCRIPTION
161 | This procedure returns the full name and email address for the
162 | FND User with the user_id set to p_user_id.
163 |
164 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
165 | Called from BC4J.
166 |
167 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
168 | get_employee_cwk_info
169 | get_customer_info
170 | get_vendor_contact_info
171 |
172 | RETURNS
173 |
174 | PARAMETERS
175 | p_user_id IN FND user Id
176 | p_full_name OUT full name
177 | p_email_address OUT email address
178 | p_return_status OUT return status. 'S' if found user info, 'F' otherwise.
179 |
180 | MODIFICATION HISTORY
181 | Date Author Description of Changes
182 | 22-Apr-2005 SKANESHI Created
183 |
184 *=======================================================================*/
185 PROCEDURE get_user_info(p_user_id IN NUMBER,
186 p_full_name OUT NOCOPY VARCHAR2,
187 p_email_address OUT NOCOPY VARCHAR2,
188 p_return_status OUT NOCOPY VARCHAR2)
189 IS
190 l_user_name fnd_user.user_name%TYPE;
191 l_email_address fnd_user.email_address%TYPE;
192 l_employee_id fnd_user.employee_id%TYPE;
193 l_customer_id fnd_user.customer_id%TYPE;
194 l_vendor_id fnd_user.supplier_id%TYPE;
195 BEGIN
196 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
197 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME,
198 'start get_user_info');
199 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME,
200 'parameters: p_user_id = ' || p_user_id ||
201 ', p_full_name = ' || p_full_name ||
202 ', p_email_address = ' || p_email_address);
203 END IF;
204
205 p_full_name := NULL;
206 p_email_address := NULL;
207 p_return_status := G_RETURN_FAIL;
208
209 IF (p_user_id = -1) THEN
210 RETURN;
211 END IF;
212
213 -- Get basic info and user type
214 SELECT user_name, email_address, employee_id, customer_id, supplier_id
215 INTO l_user_name, l_email_address, l_employee_id, l_customer_id,
216 l_vendor_id
217 FROM fnd_user
218 WHERE user_id = p_user_id
219 AND SYSDATE BETWEEN NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE);
220
221 IF (l_employee_id IS NOT NULL) THEN
222 -- FND user is an employee
223 SELECT pwx.full_name full_name, pwx.email_address email_address
224 INTO p_full_name, p_email_address
225 FROM fnd_user fu, per_workforce_current_x pwx
226 WHERE fu.employee_id IS NOT NULL
227 AND fu.employee_id = pwx.person_id
228 AND SYSDATE BETWEEN NVL(fu.start_date, SYSDATE) AND NVL(fu.end_date, SYSDATE)
229 AND fu.user_id = p_user_id;
230
231 ELSIF (l_customer_id IS NOT NULL) THEN
232 -- FND User is a customer
233 SELECT hp.party_name full_name, hp.email_address email_address
234 INTO p_full_name, p_email_address
235 FROM fnd_user fu, hz_parties hp
236 WHERE fu.customer_id IS NOT NULL
237 AND fu.customer_id = hp.party_id
238 AND fu.user_id = p_user_id;
239
240 ELSIF (l_vendor_id IS NOT NULL) THEN
241 -- FND User is a supplier
242 SELECT pvc.last_name || ', ' || pvc.first_name full_name, pvc.email_address email_address
243 INTO p_full_name, p_email_address
244 FROM fnd_user fu, po_vendor_contacts pvc
245 WHERE fu.supplier_id IS NOT NULL
246 AND fu.supplier_id = pvc.vendor_contact_id
247 AND NVL(pvc.inactive_date, SYSDATE) >= SYSDATE
248 AND fu.user_id = p_user_id;
249
250 END IF;
251
252 -- Set name to user name if not defined
253 IF (p_full_name IS NULL) THEN
254 p_full_name := l_user_name;
255 END IF;
256
257 -- Use email address defiend in FND User if not set elsewhere
258 IF (p_email_address IS NULL) THEN
259 p_email_address := l_email_address;
260 END IF;
261 p_return_status := G_RETURN_SUCCESS;
262
263 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
264 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME,
265 'end get_user_info');
266 END IF;
267
268 EXCEPTION
269 WHEN NO_DATA_FOUND THEN
270 -- Return status initialized to fail
271 RETURN;
272 WHEN OTHERS THEN
273 -- Return status initialized to fail
274 RETURN;
275
276 END;
277
278 END FUN_CONTACTUS_EMAIL;
[Home] [Help]