[Home] [Help]
PACKAGE BODY: APPS.PRP_IH_EMAIL_PVT
Source
1 PACKAGE BODY PRP_IH_EMAIL_PVT AS
2 /* $Header: PRPVIHEB.pls 120.8 2005/12/05 16:08:11 hekkiral ship $ */
3
4 --
5 -- Start of Comments
6 --
7 -- NAME
8 -- PRP_IH_EMAIL_PVT
9 --
10 -- PURPOSE
11 -- Private API for interfacing with interaction history APIs.
12 --
13 -- NOTES
14 --
15 --+
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):='PRP_IH_EMAIL_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12):='PRPVIHEB.pls';
19
20
21 PROCEDURE Create_Email_IH
22 (
23 p_api_version IN NUMBER,
24 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
25 p_commit IN VARCHAR2 := FND_API.G_FALSE,
26 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
27 p_application_id IN NUMBER,
28 p_party_id IN NUMBER,
29 p_resource_id IN NUMBER,
30 p_object_id IN NUMBER,
31 p_object_type IN VARCHAR2,
32 p_email_history_id IN NUMBER,
33 p_direction IN VARCHAR2,
34 p_contact_points_tbl IN JTF_NUMBER_TABLE,
35 p_email_sent_date IN DATE,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2
39 )
40 IS
41 l_api_name CONSTANT VARCHAR2(30) := 'Create_Email_IH';
42 l_api_version CONSTANT NUMBER := 1.0;
43
44 l_outcome_id CONSTANT NUMBER := 7; -- Contact
45 l_result_id CONSTANT NUMBER := 15; -- Email Sent
46 l_media_item_type CONSTANT VARCHAR2(30) := 'EMAIL';
47 l_module CONSTANT VARCHAR2(80) := 'PRP_IH_EMAIL_PVT.Create_Email_IH';
48
49 l_action_id NUMBER;
50 l_action_item_id NUMBER;
51 l_party_type VARCHAR2(30);
52 l_contact_party_id NUMBER;
53 l_contact_party_type VARCHAR2(30);
54 l_media_id NUMBER;
55 l_media_rec JTF_IH_PUB.media_rec_type;
56 l_mlcs_rec_tbl JTF_IH_PUB.mlcs_tbl_type;
57 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
58 l_activity_rec_tbl JTF_IH_PUB.activity_tbl_type;
59
60 CURSOR c1 IS SELECT jtf_ih_media_items_s1.nextval FROM dual;
61
62 CURSOR c_party_type(l_party_id NUMBER) IS
63 SELECT HZP.PARTY_TYPE
64 FROM HZ_PARTIES HZP
65 WHERE HZP.PARTY_ID = l_party_id;
66
67 CURSOR c_contact_party(l_contact_point_id NUMBER) IS
68 SELECT HZP.PARTY_ID,
69 HZP.PARTY_TYPE
70 FROM HZ_CONTACT_POINTS HCP,
71 HZ_PARTIES HZP
72 WHERE HCP.OWNER_TABLE_NAME = 'HZ_PARTIES'
73 AND HZP.PARTY_ID = HCP.OWNER_TABLE_ID
74 AND HCP.CONTACT_POINT_TYPE = 'EMAIL'
75 AND HCP.CONTACT_POINT_ID = l_contact_point_id;
76
77 CURSOR c_party_person(l_party_id NUMBER) IS
78 SELECT HPR.PARTY_ID
79 FROM HZ_PARTIES HZP,
80 HZ_RELATIONSHIPS HPR
81 WHERE HZP.PARTY_ID = HPR.PARTY_ID
82 AND HPR.SUBJECT_ID = l_party_id;
83
84 CURSOR c_party_rel(l_party_id NUMBER, l_object_id NUMBER) IS
85 SELECT HPR.SUBJECT_ID
86 FROM HZ_PARTIES HZP,
87 HZ_RELATIONSHIPS HPR
88 WHERE HZP.PARTY_ID = HPR.SUBJECT_ID
89 AND HPR.PARTY_ID = l_party_id
90 AND HPR.OBJECT_ID = l_object_id;
91
92 BEGIN
93
94 -- Standard Start of API savepoint
95 SAVEPOINT CREATE_EMAIL_IH_PVT;
96
97 -- Standard call to check for call compatibility.
98 IF NOT FND_API.Compatible_API_Call(l_api_version,
99 p_api_version,
100 l_api_name,
101 G_PKG_NAME)
102 THEN
103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
104 END IF;
105
106 -- Initialize message list if p_init_msg_list is set to TRUE.
107 IF FND_API.to_Boolean(p_init_msg_list) THEN
108 FND_MSG_PUB.initialize;
109 END IF;
110
111 -- Log Debug Messages.
112 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
113 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
114 MODULE => l_module,
115 MESSAGE => 'In Create_Email_IH... Parameters: ' ||'P_Application_id: ' || p_application_id ||
116 ' p_party_id: ' || p_party_id || ' p_resource_id: ' || p_resource_id ||
117 ' p_object_type: ' || p_object_type || ' p_object_id: ' || p_object_id ||
118 ' p_direction: ' || p_direction );
119 END IF;
120
121 -- Initialize API return status to success
122 x_return_status := FND_API.G_RET_STS_SUCCESS;
123
124 -- Collect required variables
125 IF (p_application_id = 694) THEN -- Proposals
126 l_action_item_id := 86; -- Proposal
127 ELSIF (p_application_id = 280) THEN -- Sales
128 l_action_item_id := 3; -- Collateral
129 ELSIF (p_application_id = 880) THEN -- Quoting
130 l_action_item_id := 14; -- Quote
131 ELSIF (p_application_id = 869) THEN -- Sales for Handhelds
132 l_action_item_id := 45; -- Email
133 ELSE
134 RAISE FND_API.G_EXC_ERROR;
135 END IF;
136
137 IF (p_direction = 'OUTBOUND') THEN
138 l_action_id := 5; -- Sent
139 ELSIF (p_direction = 'INBOUND') THEN
140 l_action_id := 87; -- Recieved
141 ELSE
142 RAISE FND_API.G_EXC_ERROR;
143 END IF;
144
145 IF (p_contact_points_tbl.count = 0) THEN
146 RAISE FND_API.G_EXC_ERROR;
147 END IF;
148
149 --
150 -- Get the value of media id from the cursor
151 --
152 OPEN c1;
153 FETCH c1 INTO l_media_id;
154 CLOSE c1;
155
156 --
157 -- Initialize media record
158 --
159 l_media_rec.media_id := l_media_id;
160 l_media_rec.start_date_time := sysdate;
161 l_media_rec.media_item_type := l_media_item_type;
162 l_media_rec.direction := p_direction;
163 l_media_rec.source_item_id := p_email_history_id;
164 l_media_rec.media_item_ref := 'PRP_DOC';
165
166
167 -- Log Debug Messages Before Calling Create_MediaItem Method.
168 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
169 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
170 MODULE => l_module,
171 MESSAGE => 'Before Calling JTF_IH_PUB.Create_MediaItem... Parameters: ' ||'p_resp_appl_id: ' || FND_GLOBAL.resp_appl_id ||
172 ' p_resp_id: ' || FND_GLOBAL.resp_id || ' p_user_id: ' || FND_GLOBAL.user_id ||
173 ' p_object_type: ' || p_object_type || ' p_object_id: ' || p_object_id ||
174 ' p_login_id: ' || FND_GLOBAL.login_id || ' l_media_id: ' || l_media_id ||
175 ' l_media_item_type: ' || l_media_item_type);
176 END IF;
177
178 --
179 -- Call JTF_IH_PUB.Create_MediaItem API
180 --
181 JTF_IH_PUB.Create_MediaItem
182 (
183 p_api_version => 1.0,
184 p_init_msg_list => FND_API.G_FALSE,
185 p_commit => FND_API.G_FALSE,
186 p_resp_appl_id => FND_GLOBAL.resp_appl_id,
187 p_resp_id => FND_GLOBAL.resp_id,
188 p_user_id => FND_GLOBAL.user_id,
189 p_login_id => FND_GLOBAL.login_id,
190 x_return_status => x_return_status,
191 x_msg_count => x_msg_count,
192 x_msg_data => x_msg_data,
193 p_media => l_media_rec,
194 p_mlcs => l_mlcs_rec_tbl
195 );
196
197
198 -- Log Debug Messages After Calling Create_MediaItem Method.
199 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
200 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
201 MODULE => l_module,
202 MESSAGE => 'After Calling JTF_IH_PUB.Create_MediaItem...Out Parameters: ' ||'x_return_status: ' || x_return_status ||
203 ' x_msg_count: ' || x_msg_count || ' x_msg_data: ' || x_msg_data);
204 END IF;
205
206 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
207 RAISE FND_API.G_EXC_ERROR;
208 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210 END IF;
211
212 --
213 -- Initialize interaction and activity records
214 --
215
216 -- Interaction record
217 l_interaction_rec.party_id := p_party_id;
218 l_interaction_rec.resource_id := p_resource_id;
219 l_interaction_rec.outcome_id := l_outcome_id;
220 l_interaction_rec.result_id := l_result_id;
221 l_interaction_rec.handler_id := p_application_id;
222 l_interaction_rec.primary_party_id := p_party_id;
223 l_interaction_rec.object_id := p_object_id;
224 l_interaction_rec.object_type := p_object_type;
225 l_interaction_rec.start_date_time := p_email_sent_date;
226 l_interaction_rec.end_date_time := p_email_sent_date;
227
228 -- Activity record
229 l_activity_rec_tbl(1).action_id := l_action_id;
230 l_activity_rec_tbl(1).action_item_id := l_action_item_id;
231 l_activity_rec_tbl(1).outcome_id := l_outcome_id;
232 l_activity_rec_tbl(1).result_id := l_result_id;
233 l_activity_rec_tbl(1).media_id := l_media_id;
234 l_activity_rec_tbl(1).doc_id := p_object_id;
235 l_activity_rec_tbl(1).doc_ref := p_object_type;
236
237 --
238 -- Call JTF_IH_PUB.Create_Interaction API
239 --
240
241 OPEN c_party_type(p_party_id);
242 FETCH c_party_type INTO l_party_type;
243 CLOSE c_party_type;
244
245 -- Log Debug Messages.
246 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
247 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
248 MODULE => l_module,
249 MESSAGE => 'l_party_type: ' || l_party_type);
250 END IF;
251
252 IF (l_party_type = 'ORGANIZATION') THEN
253
254 FOR i IN 1..p_contact_points_tbl.count LOOP
255
256 FOR l_contact_party_rec IN c_contact_party(p_contact_points_tbl(i)) LOOP
257 l_contact_party_id := l_contact_party_rec.party_id;
258 l_contact_party_type := l_contact_party_rec.party_type;
259 END LOOP;
260
261 IF (l_contact_party_type = 'PERSON') THEN
262 l_interaction_rec.contact_party_id := l_contact_party_id;
263
264 FOR l_party_person_rec IN c_party_person(l_contact_party_id) LOOP
265 l_interaction_rec.contact_rel_party_id := l_party_person_rec.party_id;
266 END LOOP;
267
268 ELSIF (l_contact_party_type = 'PARTY_RELATIONSHIP') THEN
269 l_interaction_rec.contact_rel_party_id := l_contact_party_id;
270
271 FOR l_party_rel_rec IN c_party_rel(l_contact_party_id, p_party_id) LOOP
272 l_interaction_rec.contact_party_id := l_party_rel_rec.subject_id;
273 END LOOP;
274 END IF;
275
276
277 -- Log Debug Messages Before Calling Create_Interaction Method.
278 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
279 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
280 MODULE => l_module,
281 MESSAGE => 'Before Calling JTF_IH_PUB.Create_Interaction...');
282 END IF;
283
284 JTF_IH_PUB.Create_Interaction
285 (
286 p_api_version => 1.0,
287 p_init_msg_list => FND_API.G_FALSE,
288 p_commit => FND_API.G_FALSE,
289 p_resp_appl_id => FND_GLOBAL.resp_appl_id,
290 p_resp_id => FND_GLOBAL.resp_id,
291 p_user_id => FND_GLOBAL.user_id,
292 p_login_id => FND_GLOBAL.login_id,
293 x_return_status => x_return_status,
294 x_msg_count => x_msg_count,
295 x_msg_data => x_msg_data,
296 p_interaction_rec => l_interaction_rec,
297 p_activities => l_activity_rec_tbl
298 );
299
300 -- Log Debug Messages After Calling Create_Interaction Method.
301 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
302 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
303 MODULE => l_module,
304 MESSAGE => 'After Calling JTF_IH_PUB.Create_Interaction...Out Parameters: ' ||'x_return_status: ' || x_return_status ||
305 ' x_msg_count: ' || x_msg_count || ' x_msg_data: ' || x_msg_data);
306 END IF;
307
308 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
309 RAISE FND_API.G_EXC_ERROR;
310 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
311 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
312 END IF;
313
314 END LOOP;
315
316 ELSIF (l_party_type = 'PERSON') THEN
317
318 -- Log Debug Messages Before Calling Create_Interaction Method.
319 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
323 END IF;
320 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
321 MODULE => l_module,
322 MESSAGE => 'Before Calling JTF_IH_PUB.Create_Interaction...');
324
325 l_interaction_rec.contact_party_id := p_party_id;
326 JTF_IH_PUB.Create_Interaction
327 (
328 p_api_version => 1.0,
329 p_init_msg_list => FND_API.G_FALSE,
330 p_commit => FND_API.G_FALSE,
331 p_resp_appl_id => FND_GLOBAL.resp_appl_id,
332 p_resp_id => FND_GLOBAL.resp_id,
333 p_user_id => FND_GLOBAL.user_id,
334 p_login_id => FND_GLOBAL.login_id,
335 x_return_status => x_return_status,
336 x_msg_count => x_msg_count,
337 x_msg_data => x_msg_data,
338 p_interaction_rec => l_interaction_rec,
339 p_activities => l_activity_rec_tbl
340 );
341
342 -- Log Debug Messages After Calling Create_Interaction Method.
343 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
344 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
345 MODULE => l_module,
346 MESSAGE => 'After Calling JTF_IH_PUB.Create_Interaction...Out Parameters: ' ||'x_return_status: ' || x_return_status ||
347 ' x_msg_count: ' || x_msg_count || ' x_msg_data: ' || x_msg_data);
348 END IF;
349
350 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
351 RAISE FND_API.G_EXC_ERROR;
352 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
354 END IF;
355
356 ELSE
357 RAISE FND_API.G_EXC_ERROR;
358 END IF;
359
360 -- Standard check of p_commit.
361 IF (FND_API.To_Boolean(p_commit)) THEN
362 COMMIT WORK;
363 END IF;
364
365 -- Standard call to get message count and if count is 1, get message info.
366 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
367 p_data => x_msg_data,
368 p_encoded => 'F');
369
370 EXCEPTION
371
372 WHEN FND_API.G_EXC_ERROR THEN
373 ROLLBACK TO CREATE_EMAIL_IH_PVT;
374 x_return_status := FND_API.G_RET_STS_ERROR;
375 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
376 p_data => x_msg_data,
377 p_encoded => 'F');
378
379 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
380 ROLLBACK TO CREATE_EMAIL_IH_PVT;
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
382 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
383 p_data => x_msg_data,
384 p_encoded => 'F');
385
386 WHEN OTHERS THEN
387 ROLLBACK TO CREATE_EMAIL_IH_PVT;
388 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
389 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
390 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
391 FND_MESSAGE.Set_Token('REASON', SQLERRM);
392 FND_MSG_PUB.Add;
393
394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
395
396 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
397 THEN
398 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
399 END IF;
400
401 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
402 p_data => x_msg_data,
403 p_encoded => 'F');
404
405 END Create_Email_IH;
406
407
408 END PRP_IH_EMAIL_PVT;