[Home] [Help]
PACKAGE BODY: APPS.IEM_OUTBOX_PROC_PUB
Source
1 PACKAGE BODY IEM_OUTBOX_PROC_PUB as
2 /* $Header: iemobprb.pls 120.6 2006/01/25 07:55:33 txliu noship $*/
3
4 -- PACKAGE CONSTANTS NO LITERALS USED.
5 G_PKG_NAME CONSTANT varchar2(30) :='IEM_OUTBOX_PROC_PUB';
6 G_INBOUND VARCHAR2(1) := 'I';
7 G_OUTBOUND VARCHAR2(1) := 'O';
8 G_WORK_IN_PROGRESS VARCHAR2(1) := 'P';
9 G_ACTIVE VARCHAR2(1) := 'N';
10 G_EXPIRE VARCHAR2(1) := 'Y';
11 G_MASTER_ACCOUNT VARCHAR2(1) := 'M';
12 G_AGENT_ACCOUNT VARCHAR2(1) := 'A';
13 G_CHAR_NOP VARCHAR2(1) := ' ';
14 G_NEWOUTB_FOLDER VARCHAR2(8) := '__NoNe';
15 G_NUM_NOP2 NUMBER := -1;
16 G_NUM_NOP NUMBER := -99;
17 --G_AUTOR_MSG_ID NUMBER := -999;
18 G_AUTOR_MC_PARA_ID NUMBER := -123;
19
20 G_TRANSFER VARCHAR2(1) := 'R';
21 G_WRAP_UP VARCHAR2(1) := 'W';
22 G_DORMANT VARCHAR2(1) := 'D';
23 G_QUEUEOUT VARCHAR2(1) := 'Q';
24 G_PROCESSING VARCHAR2(1) := 'G';
25 G_UNREAD VARCHAR2(1) := 'U';
26 G_UNMOVED VARCHAR2(1) := 'M';
27 G_PRETRANSFER VARCHAR2(1) := 'F';
28 G_O_DIRECTION VARCHAR2(10) := 'OUTBOUND';
29 G_I_DIRECTION VARCHAR2(10) := 'INBOUND';
30 G_MEDIA_TYPE VARCHAR2(10) := 'EMAIL';
31 G_DEFAULT_ROUTE VARCHAR2(100) := 'Unclassified';
32 G_REDIRECT VARCHAR2(1) := 'R';
33 G_AUTOFORWAD_ACT VARCHAR2(1) := 'F';
34
35
36 PROCEDURE createOutboxMessage(
37 p_api_version_number IN NUMBER,
38 p_init_msg_list IN VARCHAR2,
39 p_commit IN VARCHAR2,
40 p_resource_id IN NUMBER,
41 p_application_id IN NUMBER,
42 p_responsibility_id IN NUMBER,
43 p_master_account_id IN NUMBER,
44 p_to_address_list IN VARCHAR2,
45 p_cc_address_list IN VARCHAR2,
46 p_bcc_address_list IN VARCHAR2,
47 p_subject IN VARCHAR2,
48 p_sr_id IN NUMBER,
49 p_customer_id IN NUMBER,
50 p_contact_id IN NUMBER,
51 p_interaction_id IN NUMBER,
52 p_qualifiers IN QualifierRecordList,
53 p_message_type IN VARCHAR2,
54 p_encoding IN VARCHAR2,
55 p_character_set IN VARCHAR2,
56 p_option IN VARCHAR2, -- 'A' for auto-ack
57 p_relationship_id IN NUMBER,
58 x_outbox_item_id OUT NOCOPY NUMBER,
59 x_return_status OUT NOCOPY VARCHAR2,
60 x_msg_count OUT NOCOPY NUMBER,
61 x_msg_data OUT NOCOPY VARCHAR2
62 ) IS
63
64 l_api_name VARCHAR2(255):='createOutboxMessage';
65 l_api_version_number NUMBER:=1.0;
66 l_created_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
67 l_last_updated_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
68 l_last_update_login NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
69
70 l_return_status VARCHAR2(300);
71 l_msg_count NUMBER;
72 l_msg_data VARCHAR2(300);
73
74 l_mc_parameter_id NUMBER;
75 l_i_sequence NUMBER;
76 l_version NUMBER;
77 l_rt_interaction_id NUMBER;
78 l_rt_media_item_id NUMBER;
79 l_qualifiers IEM_MC_PUB.QualifierRecordList;
80 IEM_BAD_RECIPIENT EXCEPTION;
81 l_parent_ih_id NUMBER;
82 l_action_id NUMBER;
83
84 BEGIN
85
86
87 -- Standard Start of API savepoint
88 SAVEPOINT createOutboxMessage_pvt;
89
90 -- Standard call to check for call compatibility.
91 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
92 1.0,
93 l_api_name,
94 G_PKG_NAME)
95 THEN
96 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
97 END IF;
98
99 -- Initialize message list if p_init_msg_list is set to TRUE.
100 IF FND_API.to_Boolean( p_init_msg_list )
101 THEN
102 FND_MSG_PUB.initialize;
103 END IF;
104
105 -- Initialize API return status to SUCCESS
106 x_return_status := FND_API.G_RET_STS_SUCCESS;
107
108 -----------------------Code------------------------
109 -- insanity check
110 IF (p_to_address_list is null and p_cc_address_list is null) THEN
111 RAISE IEM_BAD_RECIPIENT;
112 END IF;
113
114 -- create iem_mc_parameter record
115 IF (p_qualifiers.count > 0) THEN
116 BEGIN
117 FOR i IN p_qualifiers.first .. p_qualifiers.Last
118 LOOP
119 l_qualifiers(i).QUALIFIER_NAME := p_qualifiers(i).QUALIFIER_NAME;
120 l_qualifiers(i).QUALIFIER_VALUE := p_qualifiers(i).QUALIFIER_VALUE;
121 END LOOP;
122 END;
123 END IF;
124
125 IEM_MC_PUB.prepareMessageComponentII
126 (p_api_version_number => 1.0,
127 p_init_msg_list =>fnd_api.g_false,
128 p_commit =>fnd_api.g_false,
129 p_action => 'automsg',
130 p_master_account_id => p_master_account_id,
131 p_activity_id =>fnd_api.g_miss_num,
132 p_to_address_list => p_to_address_list,
133 p_cc_address_list => p_cc_address_list,
134 p_bcc_address_list => p_bcc_address_list,
135 p_subject => p_subject,
136 p_sr_id => p_sr_id,
137 p_customer_id => p_customer_id,
138 p_contact_id => p_contact_id,
139 p_mes_document_id =>fnd_api.g_miss_num,
140 p_mes_category_id =>fnd_api.g_miss_num,
141 p_interaction_id => p_interaction_id,
142 p_qualifiers => l_qualifiers,
143 p_message_type => p_message_type,
144 p_encoding => p_encoding,
145 p_character_set => p_character_set,
146 p_relationship_id => p_relationship_id,
147 x_mc_parameters_id => l_mc_parameter_id,
148 x_return_status => l_return_status,
149 x_msg_count => l_msg_count,
150 x_msg_data => l_msg_data
151 );
152
153
154 -- Check return status; Proceed on success Or report back in case of error.
155 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
156 -- Success.
157 --create outbound here
158
159 -- if auto-ack, update the parent_ih_id
160 if ( p_option = 'A' ) then
161 l_parent_ih_id := p_interaction_id;
162 l_action_id := 83;
163 else
164 l_parent_ih_id := null;
165 l_action_id := G_NUM_NOP;
166 end if;
167
168 IEM_CLIENT_PUB.createMediaDetails (
169 p_api_version_number => 1.0,
170 p_init_msg_list => fnd_api.g_false,
171 p_commit => fnd_api.g_false,
172 p_resource_id => p_resource_id,
173 p_rfc822_message_id => null,
174 p_folder_name => G_NEWOUTB_FOLDER,
175 p_folder_uid => G_NUM_NOP2,
176 p_account_id => p_master_account_id,
177 p_account_type => G_MASTER_ACCOUNT,
178 p_status => G_CHAR_NOP,
179 p_customer_id => p_customer_id,
180 p_rt_media_item_id => FND_API.G_MISS_NUM,
181 p_subject => p_subject,
182 p_interaction_id => null,
183 p_service_request_id => p_sr_id,
184 p_mc_parameter_id => l_mc_parameter_id,
185 p_service_request_action => null,
186 p_contact_id => p_contact_id,
187 p_lead_id => null,
188 p_parent_ih_id => l_parent_ih_id,
189 p_action_id => l_action_id,
190 p_relationship_id => p_relationship_id,
191 x_return_status => l_return_status,
192 x_msg_count => l_msg_count,
193 x_msg_data => l_msg_data,
194 x_version => l_version,
195 x_rt_media_item_id => l_rt_media_item_id,
196 x_rt_interaction_id=> l_rt_interaction_id
197 );
198
199 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
200
201 UPDATE iem_rt_interactions SET status = 'S' -- send
202 WHERE rt_interaction_id = l_rt_interaction_id;
203
204 x_outbox_item_id := l_rt_media_item_id;
205
206 ELSE
207 -- return the error returned by IEM_CLIENT_PUB.createMediaDetails
208 x_return_status := l_return_status;
209 x_msg_count := l_msg_count;
210 x_msg_data := l_msg_data;
211
212 END IF;
213
214
215 ELSE
216 -- Return the error returned by MC_PARA_PUB API
217 x_return_status := l_return_status;
218 x_msg_count := l_msg_count;
219 x_msg_data := l_msg_data;
220
221 END IF;
222 -------------------End Code------------------------
223 -- Standard Check Of p_commit.
224 IF FND_API.To_Boolean(p_commit) THEN
225 COMMIT WORK;
226 END IF;
227
228 -- Standard callto get message count and if count is 1, get message info.
229 FND_MSG_PUB.Count_And_Get
230 ( p_count => x_msg_count,
231 p_data => x_msg_data
232 );
233 EXCEPTION
234 WHEN FND_API.G_EXC_ERROR THEN
235 ROLLBACK TO createOutboxMessage_pvt;
236 x_return_status := FND_API.G_RET_STS_ERROR ;
237 FND_MSG_PUB.Count_And_Get(
238 p_count => x_msg_count,
239 p_data => x_msg_data);
240
241 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
242 ROLLBACK TO createOutboxMessage_pvt;
243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
244 FND_MSG_PUB.Count_And_Get(
245 p_count => x_msg_count,
246 p_data => x_msg_data);
247 WHEN IEM_BAD_RECIPIENT THEN
248 ROLLBACK TO createOutboxMessage_pvt;
249 x_return_status := FND_API.G_RET_STS_ERROR;
250 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_RECIPIENT');
251 FND_MSG_PUB.ADD;
252 FND_MSG_PUB.Count_And_Get(
253 p_encoded => FND_API.G_TRUE,
254 p_count => x_msg_count,
255 p_data => x_msg_data);
256 WHEN OTHERS THEN
257 ROLLBACK TO createOutboxMessage_pvt;
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
260 THEN
261 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
262 END IF;
263 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
264 p_data => x_msg_data);
265
266 END createOutboxMessage;
267
268
269
270 PROCEDURE cancelOutboxMessage(
271 p_api_version_number IN NUMBER,
272 p_init_msg_list IN VARCHAR2,
273 p_commit IN VARCHAR2,
274 p_outbox_item_id IN NUMBER,
275 x_return_status OUT NOCOPY VARCHAR2,
276 x_msg_count OUT NOCOPY NUMBER,
277 x_msg_data OUT NOCOPY VARCHAR2
278 ) IS
279
280 l_api_name VARCHAR2(255):='cancelOutboxMessage';
281 l_api_version_number NUMBER:=1.0;
282 l_last_updated_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
283 l_last_update_login NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
284
285 l_return_status VARCHAR2(300);
286 l_msg_count NUMBER;
287 l_msg_data VARCHAR2(300);
288 l_rt_interaction_id NUMBER;
289
290 BEGIN
291
292 -- Standard Start of API savepoint
293 SAVEPOINT cancelOutboxMessage_pvt;
294
295 -- Standard call to check for call compatibility.
296 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
297 1.0,
298 l_api_name,
299 G_PKG_NAME)
300 THEN
301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302 END IF;
303
304 -- Initialize message list if p_init_msg_list is set to TRUE.
305 IF FND_API.to_Boolean( p_init_msg_list )
306 THEN
307 FND_MSG_PUB.initialize;
308 END IF;
309
310 -- Initialize API return status to SUCCESS
311 x_return_status := FND_API.G_RET_STS_SUCCESS;
312
313 -----------------------Code------------------------
314 -- Expire rt_interactions and rt_media_items
315 SELECT rt_interaction_id INTO l_rt_interaction_id
316 FROM iem_rt_media_items WHERE rt_media_item_id = p_outbox_item_id;
317
318 UPDATE iem_rt_media_items SET
319 expire = G_EXPIRE,
320 last_updated_by = l_last_updated_by,
321 last_update_login = l_last_update_login
322 WHERE rt_interaction_id = l_rt_interaction_id;
323
324 UPDATE iem_rt_interactions SET
325 expire = G_EXPIRE,
326 last_updated_by = l_last_updated_by,
327 last_update_login = l_last_update_login
328 WHERE rt_interaction_id = l_rt_interaction_id;
329
330 -------------------End Code------------------------
331 -- Standard Check Of p_commit.
332 IF FND_API.To_Boolean(p_commit) THEN
333 COMMIT WORK;
334 END IF;
335
336 EXCEPTION
337 WHEN FND_API.G_EXC_ERROR THEN
338 ROLLBACK TO cancelOutboxMessage_pvt;
339 x_return_status := FND_API.G_RET_STS_ERROR ;
340 FND_MSG_PUB.Count_And_Get(
341 p_count => x_msg_count,
342 p_data => x_msg_data);
343
344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
345 ROLLBACK TO cancelOutboxMessage_pvt;
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
347 FND_MSG_PUB.Count_And_Get(
348 p_count => x_msg_count,
349 p_data => x_msg_data);
350
351 WHEN OTHERS THEN
352 ROLLBACK TO cancelOutboxMessage_pvt;
353 x_return_status := FND_API.G_RET_STS_ERROR;
354 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
355 THEN
356 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
357 END IF;
358 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
359 p_data => x_msg_data);
360
361 END cancelOutboxMessage;
362
363 -- Queue this to outbox
364 PROCEDURE submitOutboxMessage(
365 p_api_version_number IN NUMBER,
366 p_init_msg_list IN VARCHAR2,
367 p_commit IN VARCHAR2,
368 p_outbox_item_id IN NUMBER,
369 p_preview_bool IN VARCHAR2,
370 x_return_status OUT NOCOPY VARCHAR2,
371 x_msg_count OUT NOCOPY NUMBER,
372 x_msg_data OUT NOCOPY VARCHAR2
373 ) IS
374
375 l_api_name VARCHAR2(255):='submitOutboxMessage';
376 l_api_version_number NUMBER:=1.0;
377 l_last_updated_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
378 l_last_update_login NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
379
380 l_return_status VARCHAR2(300);
381 l_msg_count NUMBER;
382 l_msg_data VARCHAR2(300);
383
384 l_status VARCHAR2(1);
385 l_rt_interaction_id NUMBER;
386
387 l_action_id NUMBER;
388 l_outcome_id NUMBER;
389 l_result_id NUMBER;
390 l_activity_type_id NUMBER;
391 l_reason_id NUMBER;
392
393 BEGIN
394
395 -- Standard Start of API savepoint
396 SAVEPOINT submitOutboxMessage_pvt;
397
398 -- Standard call to check for call compatibility.
399 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
400 1.0,
401 l_api_name,
402 G_PKG_NAME)
403 THEN
404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
405 END IF;
406
407 -- Initialize message list if p_init_msg_list is set to TRUE.
408 IF FND_API.to_Boolean( p_init_msg_list )
409 THEN
410 FND_MSG_PUB.initialize;
411 END IF;
412
413 -- Initialize API return status to SUCCESS
414 x_return_status := FND_API.G_RET_STS_SUCCESS;
415
416 -----------------------Code------------------------
417
418 SELECT rt_interaction_id INTO l_rt_interaction_id
419 FROM iem_rt_media_items WHERE rt_media_item_id = p_outbox_item_id;
420
421 SELECT status, decode(action_id, NULL, -1, action_id),
422 action_item_id, outcome_id, result_id, reason_id
423 INTO l_status, l_action_id, l_activity_type_id, l_outcome_id, l_result_id,
424 l_reason_id
425 FROM iem_rt_interactions
426 WHERE rt_interaction_id = l_rt_interaction_id;
427
428 IEM_CLIENT_PUB.queueToOutbox (p_api_version_number => 1.0,
429 p_init_msg_list => fnd_api.g_false,
430 p_commit => fnd_api.g_false,
431 p_action => l_status,
432 p_action_id => l_action_id,
433 p_rt_media_item_id => p_outbox_item_id,
434 p_version => 0,
435 p_customer_id => G_NUM_NOP,
436 p_activity_type_id => l_activity_type_id,
437 p_outcome_id => l_outcome_id,
438 p_result_id => l_result_id,
439 p_reason_id => l_reason_id,
440 p_to_resource_id => null,
441 p_status => l_status,
442 x_return_status => l_return_status,
443 x_msg_count => l_msg_count,
444 x_msg_data => l_msg_data );
445
446 x_return_status := l_return_status;
447 x_msg_count := l_msg_count;
448 x_msg_data := l_msg_data;
449
450 -------------------End Code------------------------
451 -- Standard Check Of p_commit.
452 IF FND_API.To_Boolean(p_commit) THEN
453 COMMIT WORK;
454 END IF;
455
456 EXCEPTION
457 WHEN FND_API.G_EXC_ERROR THEN
458 ROLLBACK TO submitOutboxMessage_pvt;
459 x_return_status := FND_API.G_RET_STS_ERROR ;
460 FND_MSG_PUB.Count_And_Get(
461 p_count => x_msg_count,
462 p_data => x_msg_data);
463 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
464 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
465 x_msg_data := x_msg_data || ',' || l_msg_data;
466 END LOOP;
467
468 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
469 ROLLBACK TO submitOutboxMessage_pvt;
470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
471 FND_MSG_PUB.Count_And_Get(
472 p_count => x_msg_count,
473 p_data => x_msg_data);
474 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
475 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
476 x_msg_data := x_msg_data || ',' || l_msg_data;
477 END LOOP;
478
479 WHEN OTHERS THEN
480 ROLLBACK TO submitOutboxMessage_pvt;
481 x_return_status := FND_API.G_RET_STS_ERROR;
482 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
483 THEN
484 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
485 END IF;
486 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
487 p_data => x_msg_data);
488 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
489 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
490 x_msg_data := x_msg_data || ',' || l_msg_data;
491 END LOOP;
492
493 END submitOutboxMessage;
494
495
496 PROCEDURE writeOutboxError(
497 p_api_version_number IN NUMBER,
498 p_init_msg_list IN VARCHAR2,
499 p_commit IN VARCHAR2,
500 p_rt_media_item_id IN NUMBER,
501 p_error_summary IN VARCHAR2,
502 p_error_msg IN VARCHAR2,
503 x_return_status OUT NOCOPY VARCHAR2,
504 x_msg_count OUT NOCOPY NUMBER,
505 x_msg_data OUT NOCOPY VARCHAR2
506 ) IS
507
508 l_api_name VARCHAR2(255):='writeOutboxError';
509 l_api_version_number NUMBER:=1.0;
510 l_last_updated_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
511 l_last_update_login NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
512
513 l_error_id NUMBER;
514 l_return_status VARCHAR2(300);
515 l_msg_count NUMBER;
516 l_msg_data VARCHAR2(300);
517
518 BEGIN
519
520 -- Standard Start of API savepoint
521 SAVEPOINT writeOutboxError_pvt;
522
523 -- Standard call to check for call compatibility.
524 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
525 1.0,
526 l_api_name,
527 G_PKG_NAME)
528 THEN
529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
530 END IF;
531
532 -- Initialize message list if p_init_msg_list is set to TRUE.
533 IF FND_API.to_Boolean( p_init_msg_list )
534 THEN
535 FND_MSG_PUB.initialize;
536 END IF;
537
538 -- Initialize API return status to SUCCESS
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540
541 -----------------------Code------------------------
542
543 select iem_outbox_errors_s1.nextval into l_error_id from dual;
544
545 INSERT INTO iem_outbox_errors
546 ( OUTBOX_ERROR_ID, RT_MEDIA_ITEM_ID, ERROR_SUMMARY,
547 ERROR_MESSAGE, CREATE_DATE, EXPIRE )
548 VALUES ( l_error_id, p_rt_media_item_id, p_error_summary,
549 p_error_msg, SYSDATE, G_ACTIVE );
550
551 -------------------End Code------------------------
552 -- Standard Check Of p_commit.
553 IF FND_API.To_Boolean(p_commit) THEN
554 COMMIT WORK;
555 END IF;
556
557 EXCEPTION
558 WHEN FND_API.G_EXC_ERROR THEN
559 ROLLBACK TO writeOutboxError_pvt;
560 x_return_status := FND_API.G_RET_STS_ERROR ;
561 FND_MSG_PUB.Count_And_Get(
562 p_count => x_msg_count,
563 p_data => x_msg_data);
564
565 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
566 ROLLBACK TO writeOutboxError_pvt;
567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
568 FND_MSG_PUB.Count_And_Get(
569 p_count => x_msg_count,
570 p_data => x_msg_data);
571
572 WHEN OTHERS THEN
573 ROLLBACK TO writeOutboxError_pvt;
574 x_return_status := FND_API.G_RET_STS_ERROR;
575 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
576 THEN
577 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
578 END IF;
579 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
580 p_data => x_msg_data);
581
582 END writeOutboxError;
583
584
585
586 PROCEDURE createAutoReply(
587 p_api_version_number IN NUMBER,
588 p_init_msg_list IN VARCHAR2,
589 p_commit IN VARCHAR2,
590 p_media_id IN NUMBER,
591 p_rfc822_message_id IN VARCHAR2,
592 p_folder_name IN VARCHAR2,
593 p_message_uid IN NUMBER,
594 p_master_account_id IN NUMBER,
595 p_to_address_list IN VARCHAR2,
596 p_cc_address_list IN VARCHAR2,
597 p_bcc_address_list IN VARCHAR2,
598 p_subject IN VARCHAR2,
599 p_tag_key_value_tbl IN keyVals_tbl_type,
600 p_customer_id IN NUMBER,
601 p_interaction_id IN NUMBER,
602 p_resource_id IN NUMBER,
603 p_qualifiers IN QualifierRecordList,
604 p_contact_id IN NUMBER,
605 p_relationship_id IN NUMBER,
606 p_mdt_message_id IN NUMBER,
607 x_outbox_item_id OUT NOCOPY NUMBER,
608 x_return_status OUT NOCOPY VARCHAR2,
609 x_msg_count OUT NOCOPY NUMBER,
610 x_msg_data OUT NOCOPY VARCHAR2
611 ) IS
612 l_api_name VARCHAR2(255):='createAutoReply';
613 l_api_version_number NUMBER:=1.0;
614 l_created_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
615 l_last_updated_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
616 l_last_update_login NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
617
618 l_return_status VARCHAR2(300);
619 l_msg_count NUMBER;
620 l_msg_data VARCHAR2(300);
621
622 l_i_sequence NUMBER;
623 l_m_sequence NUMBER;
624 l_version NUMBER;
625 l_rt_interaction_id NUMBER;
626 l_rt_media_item_id NUMBER;
627 l_tag_key_value keyVals_tbl_type;
628 l_sr_id NUMBER := null;
629 l_customer_id NUMBER := null;
630 l_contact_id NUMBER := null;
631 l_parent_ih_id NUMBER := null;
632 l_interaction_id NUMBER;
633 l_ih_creator VARCHAR2(1);
634 l_resource_id NUMBER;
635 l_mc_parameter_id NUMBER;
636 l_qualifiers IEM_MC_PUB.QualifierRecordList;
637 l_relationship_id NUMBER;
638
639 BEGIN
640
641 -- Standard Start of API savepoint
642 SAVEPOINT createAutoReply_pvt;
643
644 -- Standard call to check for call compatibility.
645 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
646 1.0,
647 l_api_name,
648 G_PKG_NAME)
649 THEN
650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
651 END IF;
652
653 -- Initialize message list if p_init_msg_list is set to TRUE.
654 IF FND_API.to_Boolean( p_init_msg_list )
655 THEN
656 FND_MSG_PUB.initialize;
657 END IF;
658
659 -- Initialize API return status to SUCCESS
660 x_return_status := FND_API.G_RET_STS_SUCCESS;
661
662 -----------------------Code------------------------
663 -- Extract tag key value from key value table
664 -- Currently valid system key names:
665 -- IEMNBZTSRVSRID for sr id
666 -- IEMNINTERACTIONID for interaction id
667 -- IEMNAGENTID for agent id
668 -- IEMNCUSTOMERID for customer id
669 -- IEMNCONTACTID for contact id
670 -- IEMNRELATIONSHIPID for relationship id
671
672 IF (p_tag_key_value_tbl.count > 0 ) THEN
673 FOR i IN p_tag_key_value_tbl.FIRST..p_tag_key_value_tbl.LAST LOOP
674 BEGIN
675 IF (p_tag_key_value_tbl(i).key = 'IEMNBZTSRVSRID' ) THEN
676 l_sr_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
677 ELSIF (p_tag_key_value_tbl(i).key = 'IEMNINTERACTIONID' ) THEN
678 l_parent_ih_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
679 ELSIF (p_tag_key_value_tbl(i).key = 'IEMNCUSTOMERID' ) THEN
680 l_customer_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
681 ELSIF (p_tag_key_value_tbl(i).key = 'IEMNCONTACTID' ) THEN
682 l_contact_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
683 ELSIF (p_tag_key_value_tbl(i).key = 'IEMNRELATIONSHIPID' ) THEN
684 l_relationship_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
685 END IF;
686 END;
687 END LOOP;
688 END IF;
689
690
691 -- customer id and contact id from tagging supersede the result from
692 -- email search i.e. what are from inputs
693 IF (l_customer_id is NULL) THEN
694 BEGIN
695 l_customer_id := p_customer_id;
696 l_contact_id := p_contact_id;
697 l_relationship_id := p_relationship_id;
698 END;
699 END IF;
700
701 -- Find resource_id by searching outbox_processing_agent.
702 l_resource_id := p_resource_id;
703
704 -- Record details into the RT tables.
705 IF ( p_interaction_id = fnd_api.g_miss_num) THEN
706 l_interaction_id := null;
707 l_ih_creator := null;
708 ELSE
709 l_interaction_id := p_interaction_id;
710 l_ih_creator := 'Y';
711 END IF;
712
713 -- create iem_mc_parameter record
714 IF (p_qualifiers.count > 0) THEN
715 BEGIN
716 FOR i IN p_qualifiers.first .. p_qualifiers.Last
717 LOOP
718 l_qualifiers(i).QUALIFIER_NAME := p_qualifiers(i).QUALIFIER_NAME;
719 l_qualifiers(i).QUALIFIER_VALUE := p_qualifiers(i).QUALIFIER_VALUE;
720 END LOOP;
721 END;
722 END IF;
723
724 IEM_MC_PUB.prepareMessageComponentII
725 (p_api_version_number => 1.0,
726 p_init_msg_list =>fnd_api.g_false,
727 p_commit =>fnd_api.g_false,
728 p_action => 'autoreply',
729 p_master_account_id => p_master_account_id,
730 p_activity_id => fnd_api.g_miss_num,
731 p_to_address_list => p_to_address_list,
732 p_cc_address_list => p_cc_address_list,
733 p_bcc_address_list => p_bcc_address_list,
734 p_subject => p_subject,
735 p_sr_id => null,
736 p_customer_id => l_customer_id,
737 p_contact_id => l_contact_id,
738 p_mes_document_id => fnd_api.g_miss_num,
739 p_mes_category_id => fnd_api.g_miss_num,
740 p_interaction_id => null,
741 p_qualifiers => l_qualifiers,
742 p_message_type => null, --p_message_type,
743 p_encoding => null, --p_encoding,
744 p_character_set => null, --p_character_set,
745 p_relationship_id => l_relationship_id,
746 x_mc_parameters_id => l_mc_parameter_id,
747 x_return_status => l_return_status,
748 x_msg_count => l_msg_count,
749 x_msg_data => l_msg_data
750 );
751
752
753 -- Check return status; Proceed on success Or report back in case of error.
754 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
755 -- Success.
756
757
758 select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
759 INSERT INTO iem_rt_interactions (
760 rt_interaction_id, resource_id, customer_id, contact_id, type,
761 status, expire, created_by, creation_date, last_updated_by,
762 last_update_date, last_update_login, parent_interaction_id,
763 service_request_id, inb_tag_id, interaction_id,
764 mc_parameter_id, ih_creator, action_id, action_item_id,
765 outcome_id, relationship_id)
766 VALUES (
767 l_i_sequence, l_resource_id, l_customer_id, l_contact_id,
768 G_INBOUND, 'S', G_ACTIVE, l_created_by,
769 SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
770 l_parent_ih_id, l_sr_id, null, l_interaction_id,
771 l_mc_parameter_id, l_ih_creator, 74, 45, 53, l_relationship_id);
772
773
774 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
775 INSERT INTO iem_rt_media_items (
776 rt_interaction_id, rt_media_item_id, resource_id,
777 media_id, message_id, rfc822_message_id, folder_name,
778 folder_uid, email_account_id, db_server_id, email_type,
779 status, expire, version, created_by, creation_date,
780 last_updated_by, last_update_date, last_update_login )
781 VALUES (
782 l_i_sequence, l_m_sequence, l_resource_id,
783 p_media_id,
784 p_mdt_message_id,
785 p_rfc822_message_id,
786 p_folder_name,
787 p_message_uid,
788 p_master_account_id,
789 null,
790 G_INBOUND, G_UNMOVED, G_ACTIVE,0, l_created_by, SYSDATE,
791 l_last_updated_by, SYSDATE, l_last_update_login );
792
793 --create outbound here
794 IEM_CLIENT_PUB.createMediaDetails (p_api_version_number => 1.0,
795 p_init_msg_list => fnd_api.g_false,
796 p_commit => fnd_api.g_false,
797 p_resource_id => l_resource_id,
798 p_rfc822_message_id => null,
799 p_folder_name => G_NEWOUTB_FOLDER,
800 p_folder_uid => G_NUM_NOP2,
801 p_account_id => p_master_account_id,
802 p_account_type => G_MASTER_ACCOUNT,
803 p_status => G_CHAR_NOP,
804 p_customer_id => l_customer_id,
805 p_rt_media_item_id => l_m_sequence,
806 p_subject => null,
807 p_interaction_id => p_interaction_id,
808 p_service_request_id => l_sr_id,
809 p_mc_parameter_id => G_AUTOR_MC_PARA_ID,
810 p_service_request_action => null,
811 p_contact_id => l_contact_id,
812 p_lead_id => null,
813 p_parent_ih_id => null,
814 p_action_id => G_NUM_NOP,
815 p_relationship_id => l_relationship_id,
816 x_return_status => l_return_status,
817 x_msg_count => l_msg_count,
818 x_msg_data => l_msg_data,
819 x_version => l_version,
820 x_rt_media_item_id => l_rt_media_item_id,
821 x_rt_interaction_id => l_rt_interaction_id
822 );
823
824 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
825 x_outbox_item_id := l_rt_media_item_id;
826
827 ELSE
828 -- return the error returned by IEM_CLIENT_PUB.createMediaDetails
829 x_return_status := l_return_status;
830 x_msg_count := l_msg_count;
831 x_msg_data := l_msg_data;
832
833 END IF;
834
835 ELSE
836 -- return the error returned by IEM_MC_PUB.prepareMessageComponentII
837 x_return_status := l_return_status;
838 x_msg_count := l_msg_count;
839 x_msg_data := l_msg_data;
840
841 END IF;
842
843 -------------------End Code------------------------
844 -- Standard Check Of p_commit.
845 IF FND_API.To_Boolean(p_commit) THEN
846 COMMIT WORK;
847 END IF;
848
849 -- Standard callto get message count and if count is 1, get message info.
850 FND_MSG_PUB.Count_And_Get
851 ( p_count => x_msg_count,
852 p_data => x_msg_data
853 );
854 EXCEPTION
855 WHEN FND_API.G_EXC_ERROR THEN
856 ROLLBACK TO createAutoReply_pvt;
857 x_return_status := FND_API.G_RET_STS_ERROR ;
858 FND_MSG_PUB.Count_And_Get(
859 p_count => x_msg_count,
860 p_data => x_msg_data);
861
862 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
863 ROLLBACK TO createAutoReply_pvt;
864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
865 FND_MSG_PUB.Count_And_Get(
866 p_count => x_msg_count,
867 p_data => x_msg_data);
868
869 WHEN OTHERS THEN
870 ROLLBACK TO createAutoReply_pvt;
871 x_return_status := FND_API.G_RET_STS_ERROR;
872 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
873 THEN
874 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
875 END IF;
876 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
877 p_data => x_msg_data);
878
879 END createAutoReply;
880
881
882
883 PROCEDURE insertBodyText
884 (p_api_version_number IN NUMBER,
885 p_init_msg_list IN VARCHAR2,
886 p_commit IN VARCHAR2,
887 p_outbox_item_id IN NUMBER,
888 p_text IN BLOB,
889 x_return_status OUT NOCOPY VARCHAR2,
890 x_msg_count OUT NOCOPY NUMBER,
891 x_msg_data OUT NOCOPY VARCHAR2
892 ) AS
893
894 l_msg_count NUMBER(2);
895 l_msg_data VARCHAR2(2000);
896 l_sequence NUMBER;
897
898 l_api_name CONSTANT VARCHAR2(30) := 'InsertBodyText';
899 l_api_version CONSTANT NUMBER := 1.0;
900
901
902 BEGIN
903
904 SAVEPOINT insertBodyText;
905
906 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version_number, l_api_name, G_PKG_NAME)
907 then
908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
909 END IF;
910
911 IF fnd_api.to_boolean(p_init_msg_list)
912 then
913 FND_MSG_PUB.initialize;
914 end if;
915
916 x_return_status := fnd_api.g_ret_sts_success;
917
918 select count(REF_KEY) into l_sequence from IEM_MSG_PARTS where REF_KEY = p_outbox_item_id and PART_TYPE = 'HTMLTEXT';
919 l_sequence := l_sequence + 1;
920
921 insert into IEM_MSG_PARTS
922 (
923 REF_KEY,
924 PART_TYPE,
925 PART_NAME,
926 PART_DATA,
927 DELETE_FLAG,
928 LAST_UPDATE_DATE
929 )
930 values
931 (
932 p_outbox_item_id,
933 'HTMLTEXT',
934 l_sequence,
935 empty_blob(),
936 'N',
937 SYSDATE
938 );
939
940 update IEM_MSG_PARTS set PART_DATA = p_text where REF_KEY = p_outbox_item_id and PART_TYPE = 'HTMLTEXT' and PART_NAME = l_sequence;
941
942 -- Standard check of p_commit
943 IF FND_API.To_Boolean(p_commit) THEN
944 COMMIT WORK;
945 END IF;
946
947 -- Standard call to get message count and if count is 1, get message info.
948 FND_MSG_PUB.Count_And_Get(
949 p_count => x_msg_count,
950 p_data => x_msg_data
951 );
952
953 EXCEPTION
954 WHEN FND_API.G_EXC_ERROR THEN
955 ROLLBACK TO IEM_MSG_PARTS;
956
957 x_return_status := FND_API.G_RET_STS_ERROR;
958 FND_MSG_PUB.Count_And_Get(
959 p_count => x_msg_count,
960 p_data => x_msg_data
961 );
962
963
964 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
965 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
966 x_msg_data := x_msg_data || ',' || l_msg_data;
967 END LOOP;
968
969 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
970 ROLLBACK TO IEM_MSG_PARTS;
971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
972 FND_MSG_PUB.Count_And_Get
973 (
974 p_count => x_msg_count,
975 p_data => x_msg_data
976 );
977
978 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
979 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
980 x_msg_data := x_msg_data || ',' || l_msg_data;
981 END LOOP;
982
983 WHEN OTHERS THEN
984 ROLLBACK TO IEM_MSG_PARTS;
985 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
986
987 FND_MSG_PUB.Count_And_Get (
988 p_count => x_msg_count,
989 p_data => x_msg_data
990 );
991
992 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
993 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
994 x_msg_data := x_msg_data || ',' || l_msg_data;
995 END LOOP;
996
997 END insertBodyText;
998
999
1000 PROCEDURE insertDocument
1001 (p_api_version_number IN NUMBER,
1002 p_init_msg_list IN VARCHAR2,
1003 p_commit IN VARCHAR2,
1004 p_outbox_item_id IN NUMBER,
1005 p_document_source IN VARCHAR2,
1006 p_document_id IN NUMBER,
1007 x_return_status OUT NOCOPY VARCHAR2,
1008 x_msg_count OUT NOCOPY NUMBER,
1009 x_msg_data OUT NOCOPY VARCHAR2
1010 ) AS
1011
1012 l_msg_count NUMBER(2);
1013 l_msg_data VARCHAR2(2000);
1014 l_part_info VARCHAR2(128);
1015 l_sequence NUMBER;
1016
1017 l_api_name CONSTANT VARCHAR2(30) := 'insertDocument';
1018 l_api_version CONSTANT NUMBER := 1.0;
1019
1020
1021 BEGIN
1022
1023 SAVEPOINT insertDocument;
1024
1025 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version_number, l_api_name, G_PKG_NAME)
1026 then
1027 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1028 END IF;
1029
1030 IF fnd_api.to_boolean(p_init_msg_list)
1031 then
1032 FND_MSG_PUB.initialize;
1033 end if;
1034
1035 x_return_status := fnd_api.g_ret_sts_success;
1036
1037 l_part_info := '__DOC__:'||p_document_id||'@'||p_document_source;
1038
1039 select count(REF_KEY) into l_sequence from IEM_MSG_PARTS where REF_KEY = p_outbox_item_id and PART_TYPE = 'HTMLTEXT';
1040 l_sequence := l_sequence + 1;
1041
1042 insert into IEM_MSG_PARTS
1043 (
1044 REF_KEY,
1045 PART_TYPE,
1046 PART_NAME,
1047 PART_INFO,
1048 PART_DATA,
1049 DELETE_FLAG,
1050 LAST_UPDATE_DATE
1051 )
1052 values
1053 (
1054 p_outbox_item_id,
1055 'HTMLTEXT',
1056 l_sequence,
1057 l_part_info,
1058 empty_Blob(),
1059 'N',
1060 SYSDATE
1061 );
1062
1063 -- Standard check of p_commit
1064 IF FND_API.To_Boolean(p_commit) THEN
1065 COMMIT WORK;
1066 END IF;
1067
1068 -- Standard call to get message count and if count is 1, get message info.
1069 FND_MSG_PUB.Count_And_Get(
1070 p_count => x_msg_count,
1071 p_data => x_msg_data
1072 );
1073
1074 EXCEPTION
1075 WHEN FND_API.G_EXC_ERROR THEN
1076 ROLLBACK TO IEM_MSG_PARTS;
1077
1078 x_return_status := FND_API.G_RET_STS_ERROR;
1079 FND_MSG_PUB.Count_And_Get(
1080 p_count => x_msg_count,
1081 p_data => x_msg_data
1082 );
1083
1084
1085 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1086 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1087 x_msg_data := x_msg_data || ',' || l_msg_data;
1088 END LOOP;
1089
1090 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1091 ROLLBACK TO IEM_MSG_PARTS;
1092 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1093 FND_MSG_PUB.Count_And_Get
1094 (
1095 p_count => x_msg_count,
1096 p_data => x_msg_data
1097 );
1098
1099 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1100 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1101 x_msg_data := x_msg_data || ',' || l_msg_data;
1102 END LOOP;
1103
1104
1105 WHEN OTHERS THEN
1106 ROLLBACK TO IEM_MSG_PARTS;
1107 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1108
1109 FND_MSG_PUB.Count_And_Get (
1110 p_count => x_msg_count,
1111 p_data => x_msg_data
1112 );
1113
1114 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1115 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1116 x_msg_data := x_msg_data || ',' || l_msg_data;
1117 END LOOP;
1118
1119 END insertDocument;
1120
1121
1122 PROCEDURE attachDocument
1123 (p_api_version_number IN NUMBER,
1124 p_init_msg_list IN VARCHAR2,
1125 p_commit IN VARCHAR2,
1126 p_outbox_item_id IN NUMBER,
1127 p_document_source IN VARCHAR2,
1128 p_document_id IN NUMBER,
1129 p_binary_source IN BLOB,
1130 p_attachment_name IN VARCHAR2,
1131 x_return_status OUT NOCOPY VARCHAR2,
1132 x_msg_count OUT NOCOPY NUMBER,
1133 x_msg_data OUT NOCOPY VARCHAR2
1134 ) AS
1135
1136 l_msg_count NUMBER(2);
1137 l_msg_data VARCHAR2(2000);
1138 l_part_info VARCHAR2(128);
1139
1140 l_api_name CONSTANT VARCHAR2(30) := 'attachDocument';
1141 l_api_version CONSTANT NUMBER := 1.0;
1142
1143
1144 BEGIN
1145
1146 SAVEPOINT attachDocument;
1147
1148 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version_number, l_api_name, G_PKG_NAME)
1149 then
1150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1151 END IF;
1152
1153 IF fnd_api.to_boolean(p_init_msg_list)
1154 then
1155 FND_MSG_PUB.initialize;
1156 end if;
1157
1158 x_return_status := fnd_api.g_ret_sts_success;
1159
1160 IF (p_document_source = 'BINARY')
1161 THEN
1162 insert into IEM_MSG_PARTS
1163 (
1164 REF_KEY,
1165 PART_TYPE,
1166 PART_NAME,
1167 PART_INFO,
1168 PART_DATA,
1169 DELETE_FLAG,
1170 LAST_UPDATE_DATE
1171 )
1172 values
1173 (
1174 p_outbox_item_id,
1175 'ATTACHMENT',
1176 p_attachment_name,
1177 NULL,
1178 empty_Blob(),
1179 'N',
1180 SYSDATE
1181 );
1182
1183 update IEM_MSG_PARTS set PART_DATA = p_binary_source where REF_KEY = p_outbox_item_id and PART_TYPE = 'ATTACHMENT' and PART_NAME = p_attachment_name;
1184
1185 ELSE
1186 l_part_info := '__DOC__:'||p_document_id||'@'||p_document_source;
1187
1188 insert into IEM_MSG_PARTS
1189 (
1190 REF_KEY,
1191 PART_TYPE,
1192 PART_NAME,
1193 PART_INFO,
1194 PART_DATA,
1195 DELETE_FLAG,
1196 LAST_UPDATE_DATE
1197 )
1198 values
1199 (
1200 p_outbox_item_id,
1201 'ATTACHMENT',
1202 p_attachment_name,
1203 l_part_info,
1204 empty_Blob(),
1205 'N',
1206 SYSDATE
1207 );
1208 END IF;
1209
1210 -- Standard check of p_commit
1211 IF FND_API.To_Boolean(p_commit) THEN
1212 COMMIT WORK;
1213 END IF;
1214
1215 -- Standard call to get message count and if count is 1, get message info.
1216 FND_MSG_PUB.Count_And_Get(
1217 p_count => x_msg_count,
1218 p_data => x_msg_data
1219 );
1220
1221 EXCEPTION
1222 WHEN FND_API.G_EXC_ERROR THEN
1223 ROLLBACK TO IEM_MSG_PARTS;
1224
1225 x_return_status := FND_API.G_RET_STS_ERROR;
1226 FND_MSG_PUB.Count_And_Get(
1227 p_count => x_msg_count,
1228 p_data => x_msg_data
1229 );
1230
1231
1232 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1233 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1234 x_msg_data := x_msg_data || ',' || l_msg_data;
1235 END LOOP;
1236
1237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1238 ROLLBACK TO IEM_MSG_PARTS;
1239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1240 FND_MSG_PUB.Count_And_Get
1241 (
1242 p_count => x_msg_count,
1243 p_data => x_msg_data
1244 );
1245
1246 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1247 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1248 x_msg_data := x_msg_data || ',' || l_msg_data;
1249 END LOOP;
1250
1251 WHEN OTHERS THEN
1252 ROLLBACK TO IEM_MSG_PARTS;
1253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1254
1255 FND_MSG_PUB.Count_And_Get (
1256 p_count => x_msg_count,
1257 p_data => x_msg_data
1258 );
1259
1260 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1261 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1262 x_msg_data := x_msg_data || ',' || l_msg_data;
1263 END LOOP;
1264
1265 END attachDocument;
1266
1267
1268
1269 PROCEDURE getAccountList(
1270 p_api_version_number IN NUMBER,
1271 p_init_msg_list IN VARCHAR2,
1272 p_commit IN VARCHAR2,
1273 p_resource_id IN NUMBER,
1274 x_account_list OUT NOCOPY AcctRecList,
1275 x_return_status OUT NOCOPY VARCHAR2,
1276 x_msg_count OUT NOCOPY NUMBER,
1277 x_msg_data OUT NOCOPY VARCHAR2
1278 ) IS
1279
1280 l_api_name VARCHAR2(255):='getAccountList';
1281 l_api_version_number NUMBER:=1.0;
1282 l_last_updated_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1283 l_last_update_login NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
1284
1285 l_return_status VARCHAR2(300);
1286 l_msg_count NUMBER;
1287 l_msg_data VARCHAR2(300);
1288 l_rt_interaction_id NUMBER;
1289 l_account_list IEM_EMAILACCOUNT_PUB.EMACNT_tbl_type;
1290 l_index NUMBER;
1291
1292 BEGIN
1293
1294 -- Standard Start of API savepoint
1295 SAVEPOINT getAccountList_pvt;
1296
1297 -- Standard call to check for call compatibility.
1298 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1299 1.0,
1300 l_api_name,
1301 G_PKG_NAME)
1302 THEN
1303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1304 END IF;
1305
1306 -- Initialize message list if p_init_msg_list is set to TRUE.
1307 IF FND_API.to_Boolean( p_init_msg_list )
1308 THEN
1309 FND_MSG_PUB.initialize;
1310 END IF;
1311
1312 -- Initialize API return status to SUCCESS
1313 x_return_status := FND_API.G_RET_STS_SUCCESS;
1314
1315 -----------------------Code------------------------
1316
1317 IEM_EMAILACCOUNT_PUB.Get_EmailAccount_List (p_api_version_number =>1.0,
1318 p_init_msg_list => FND_API.G_FALSE,
1319 p_commit => FND_API.G_FALSE,
1320 p_RESOURCE_ID => p_resource_id,
1321 x_return_status => l_return_status,
1322 x_msg_count => l_msg_count,
1323 x_msg_data => l_msg_data,
1324 x_Email_Acnt_tbl => l_account_list
1325 );
1326 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1327 if (l_account_list.count > 0) then
1328 l_index := 1;
1329 FOR i in l_account_list.first..l_account_list.last LOOP
1330 x_account_list(l_index).account_id := l_account_list(i).account_id;
1331 x_account_list(l_index).account_name := l_account_list(i).account_name;
1332 l_index:=l_index+1;
1333 END LOOP;
1334 end if;
1335 ELSE
1336 x_return_status := l_return_status;
1337 x_msg_count := l_msg_count;
1338 x_msg_data := l_msg_data;
1339 END IF;
1340 -------------------End Code------------------------
1341 -- Standard Check Of p_commit.
1342 IF FND_API.To_Boolean(p_commit) THEN
1343 COMMIT WORK;
1344 END IF;
1345
1346 EXCEPTION
1347 WHEN FND_API.G_EXC_ERROR THEN
1348 ROLLBACK TO getAccountList_pvt;
1349 x_return_status := FND_API.G_RET_STS_ERROR ;
1350 FND_MSG_PUB.Count_And_Get(
1351 p_count => x_msg_count,
1352 p_data => x_msg_data);
1353
1354 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1355 ROLLBACK TO getAccountList_pvt;
1356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1357 FND_MSG_PUB.Count_And_Get(
1358 p_count => x_msg_count,
1359 p_data => x_msg_data);
1360
1361 WHEN OTHERS THEN
1362 ROLLBACK TO getAccountList_pvt;
1363 x_return_status := FND_API.G_RET_STS_ERROR;
1364 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1365 THEN
1366 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1367 END IF;
1368 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1369 p_data => x_msg_data);
1370
1371 END getAccountList;
1372
1373 PROCEDURE redirectMessage(
1374 p_api_version_number IN NUMBER,
1375 p_init_msg_list IN VARCHAR2,
1376 p_commit IN VARCHAR2,
1377 p_mdt_msg_id IN NUMBER,
1378 p_to_account_id IN NUMBER,
1379 p_resource_id IN NUMBER,
1380 x_outbox_item_id OUT NOCOPY NUMBER,
1381 x_return_status OUT NOCOPY VARCHAR2,
1382 x_msg_count OUT NOCOPY NUMBER,
1383 x_msg_data OUT NOCOPY VARCHAR2
1384 ) IS
1385
1386 l_api_name VARCHAR2(255):='redirectMessage';
1387 l_api_version_number NUMBER:=1.0;
1388 l_created_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1389 l_last_updated_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1390 l_last_update_login NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
1391
1392 IEM_NO_DATA EXCEPTION;
1393 l_email_acct_id NUMBER;
1394 l_classification_id NUMBER;
1395 l_media_id NUMBER;
1396 l_folder_name VARCHAR2(255);
1397 l_i_sequence NUMBER;
1398 l_m_sequence NUMBER;
1399 l_db_server_id NUMBER;
1400
1401
1402 BEGIN
1403
1404 -- Standard Start of API savepoint
1405 SAVEPOINT redirectMessage_pvt;
1406
1407 -- Standard call to check for call compatibility.
1408 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1409 1.0,
1410 l_api_name,
1411 G_PKG_NAME)
1412 THEN
1413 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1414 END IF;
1415
1416 -- Initialize message list if p_init_msg_list is set to TRUE.
1417 IF FND_API.to_Boolean( p_init_msg_list )
1418 THEN
1419 FND_MSG_PUB.initialize;
1420 END IF;
1421
1422 -- Initialize API return status to SUCCESS
1423 x_return_status := FND_API.G_RET_STS_SUCCESS;
1424
1425 -----------------------Code------------------------
1426 begin
1427 select EMAIL_ACCOUNT_ID, RT_CLASSIFICATION_ID,
1428 IH_MEDIA_ITEM_ID
1429 into l_email_acct_id, l_classification_id,
1430 l_media_id
1431 from iem_rt_proc_emails where message_id = p_mdt_msg_id;
1432 exception
1433 when others then
1434 null;
1435 end;
1436
1437 if ( l_email_acct_id is null ) then
1438 raise IEM_NO_DATA;
1439 end if;
1440
1441 SELECT name INTO l_folder_name
1442 FROM iem_route_classifications
1443 WHERE ROUTE_CLASSIFICATION_ID = l_classification_id;
1444
1445 select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
1446 INSERT INTO iem_rt_interactions (
1447 rt_interaction_id, resource_id, type,
1448 status, expire, created_by, creation_date, last_updated_by,
1449 last_update_date, last_update_login, to_resource_id
1450 )
1451 VALUES (
1452 l_i_sequence, p_resource_id, G_INBOUND,
1453 G_REDIRECT, G_QUEUEOUT, l_created_by,
1454 SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
1455 p_to_account_id
1456 );
1457
1458 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
1459 INSERT INTO iem_rt_media_items (
1460 rt_interaction_id, rt_media_item_id, resource_id,
1461 media_id, message_id, rfc822_message_id, folder_name,
1462 folder_uid, email_account_id, db_server_id, email_type,
1463 status, expire, version, created_by, creation_date,
1464 last_updated_by, last_update_date, last_update_login )
1465 VALUES (
1466 l_i_sequence, l_m_sequence, p_resource_id,
1467 l_media_id, p_mdt_msg_id, null,
1468 l_folder_name, null, l_email_acct_id,
1469 null, G_INBOUND, G_UNMOVED, G_ACTIVE,
1470 0, l_created_by, SYSDATE,
1471 l_last_updated_by, SYSDATE, l_last_update_login
1472 );
1473
1474 x_outbox_item_id := l_m_sequence;
1475 -------------------End Code------------------------
1476 -- Standard Check Of p_commit.
1477 IF FND_API.To_Boolean(p_commit) THEN
1478 COMMIT WORK;
1479 END IF;
1480
1481 -- Standard callto get message count and if count is 1, get message info.
1482 FND_MSG_PUB.Count_And_Get
1483 ( p_encoded => FND_API.G_TRUE,
1484 p_count => x_msg_count,
1485 p_data => x_msg_data
1486 );
1487
1488 EXCEPTION
1489 WHEN IEM_NO_DATA THEN
1490 ROLLBACK TO redirectMessage_pvt;
1491 x_return_status := FND_API.G_RET_STS_ERROR ;
1492 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
1493 FND_MSG_PUB.ADD;
1494 FND_MSG_PUB.Count_And_Get(
1495 p_encoded => FND_API.G_TRUE,
1496 p_count => x_msg_count,
1497 p_data => x_msg_data);
1498
1499 WHEN FND_API.G_EXC_ERROR THEN
1500 ROLLBACK TO redirectMessage_pvt;
1501 x_return_status := FND_API.G_RET_STS_ERROR ;
1502 FND_MSG_PUB.Count_And_Get(
1503 p_count => x_msg_count,
1504 p_data => x_msg_data);
1505
1506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1507 ROLLBACK TO redirectMessage_pvt;
1508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1509 FND_MSG_PUB.Count_And_Get(
1510 p_count => x_msg_count,
1511 p_data => x_msg_data);
1512
1513 WHEN OTHERS THEN
1514 ROLLBACK TO redirectMessage_pvt;
1515 x_return_status := FND_API.G_RET_STS_ERROR;
1516 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1517 THEN
1518 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1519 END IF;
1520 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1521 p_data => x_msg_data);
1522
1523 END redirectMessage;
1524
1525 PROCEDURE autoForward(
1526 p_api_version_number IN NUMBER,
1527 p_init_msg_list IN VARCHAR2,
1528 p_commit IN VARCHAR2,
1529 p_media_id IN NUMBER,
1530 p_rfc822_message_id IN VARCHAR2,
1531 p_folder_name IN VARCHAR2,
1532 p_message_uid IN NUMBER,
1533 p_master_account_id IN NUMBER,
1534 p_to_address_list IN VARCHAR2,
1535 p_cc_address_list IN VARCHAR2,
1536 p_bcc_address_list IN VARCHAR2,
1537 p_subject IN VARCHAR2,
1538 p_tag_key_value_tbl IN keyVals_tbl_type,
1539 p_customer_id IN NUMBER,
1540 p_interaction_id IN NUMBER,
1541 p_resource_id IN NUMBER,
1542 p_qualifiers IN QualifierRecordList,
1543 p_contact_id IN NUMBER,
1544 p_relationship_id IN NUMBER,
1545 p_attach_inb IN VARCHAR2, -- if 'A' attach original inbound, if 'I' inbound is inlined
1546 p_mdt_message_id IN NUMBER,
1547 x_outbox_item_id OUT NOCOPY NUMBER,
1548 x_return_status OUT NOCOPY VARCHAR2,
1549 x_msg_count OUT NOCOPY NUMBER,
1550 x_msg_data OUT NOCOPY VARCHAR2
1551 )
1552 IS
1553 l_api_name VARCHAR2(255):='autoForward';
1554 l_api_version_number NUMBER:=1.0;
1555 l_created_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1556 l_last_updated_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1557 l_last_update_login NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
1558
1559 l_return_status VARCHAR2(300);
1560 l_msg_count NUMBER;
1561 l_msg_data VARCHAR2(300);
1562
1563 l_i_sequence NUMBER;
1564 l_m_sequence NUMBER;
1565 l_version NUMBER;
1566 l_rt_interaction_id NUMBER;
1567 l_rt_media_item_id NUMBER;
1568 l_tag_key_value keyVals_tbl_type;
1569 l_sr_id NUMBER := null;
1570 l_customer_id NUMBER := null;
1571 l_contact_id NUMBER := null;
1572 l_parent_ih_id NUMBER := null;
1573 l_interaction_id NUMBER;
1574 l_ih_creator VARCHAR2(1);
1575 l_db_server_id NUMBER;
1576 l_resource_id NUMBER;
1577 l_mc_parameter_id NUMBER;
1578 l_qualifiers IEM_MC_PUB.QualifierRecordList;
1579 l_relationship_id NUMBER;
1580 IEM_BAD_RECIPIENT EXCEPTION;
1581
1582 BEGIN
1583
1584
1585 -- Standard Start of API savepoint
1586 SAVEPOINT autoForward_pvt;
1587
1588 -- Standard call to check for call compatibility.
1589 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1590 1.0,
1591 l_api_name,
1592 G_PKG_NAME)
1593 THEN
1594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1595 END IF;
1596
1597 -- Initialize message list if p_init_msg_list is set to TRUE.
1598 IF FND_API.to_Boolean( p_init_msg_list )
1599 THEN
1600 FND_MSG_PUB.initialize;
1601 END IF;
1602
1603 -- Initialize API return status to SUCCESS
1604 x_return_status := FND_API.G_RET_STS_SUCCESS;
1605
1606 -----------------------Code------------------------
1607 -- insanity check
1608 IF (p_to_address_list is null and p_cc_address_list is null) THEN
1609 RAISE IEM_BAD_RECIPIENT;
1610 END IF;
1611
1612 -- Extract tag key value from key value table
1613 -- Currently valid system key names:
1614 -- IEMNBZTSRVSRID for sr id
1615 -- IEMNINTERACTIONID for interaction id
1616 -- IEMNAGENTID for agent id
1617 -- IEMNCUSTOMERID for customer id
1618 -- IEMNCONTACTID for contact id
1619 -- IEMNRELATIONSHIPID for relationship id
1620
1621 IF (p_tag_key_value_tbl.count > 0 ) THEN
1622 FOR i IN p_tag_key_value_tbl.FIRST..p_tag_key_value_tbl.LAST LOOP
1623 BEGIN
1624 IF (p_tag_key_value_tbl(i).key = 'IEMNBZTSRVSRID' ) THEN
1625 l_sr_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
1626 ELSIF (p_tag_key_value_tbl(i).key = 'IEMNINTERACTIONID' ) THEN
1627 l_parent_ih_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
1628 ELSIF (p_tag_key_value_tbl(i).key = 'IEMNCUSTOMERID' ) THEN
1629 l_customer_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
1630 ELSIF (p_tag_key_value_tbl(i).key = 'IEMNCONTACTID' ) THEN
1631 l_contact_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
1632 ELSIF (p_tag_key_value_tbl(i).key = 'IEMNRELATIONSHIPID' ) THEN
1633 l_relationship_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
1634 END IF;
1635 END;
1636 END LOOP;
1637 END IF;
1638
1639
1640 -- customer id and contact id from tagging supersede the result from
1641 -- email search i.e. what are from inputs
1642 IF (l_customer_id is NULL) THEN
1643 BEGIN
1644 l_customer_id := p_customer_id;
1645 l_contact_id := p_contact_id;
1646 l_relationship_id := p_relationship_id;
1647 END;
1648 END IF;
1649
1650 -- Find resource_id by searching outbox_processing_agent.
1651 l_resource_id := p_resource_id;
1652
1653 -- Record details into the RT tables.
1654 IF ( p_interaction_id = fnd_api.g_miss_num) THEN
1655 l_interaction_id := null;
1656 l_ih_creator := null;
1657 ELSE
1658 l_interaction_id := p_interaction_id;
1659 l_ih_creator := 'Y';
1660 END IF;
1661
1662
1663 -- create iem_mc_parameter record
1664 IF (p_qualifiers.count > 0) THEN
1665 BEGIN
1666 FOR i IN p_qualifiers.first .. p_qualifiers.Last
1667 LOOP
1668 l_qualifiers(i).QUALIFIER_NAME := p_qualifiers(i).QUALIFIER_NAME;
1669 l_qualifiers(i).QUALIFIER_VALUE := p_qualifiers(i).QUALIFIER_VALUE;
1670 END LOOP;
1671 END;
1672 END IF;
1673
1674 IEM_MC_PUB.prepareMessageComponentII
1675 (p_api_version_number => 1.0,
1676 p_init_msg_list => fnd_api.g_false,
1677 p_commit => fnd_api.g_false,
1678 p_action => 'autoforward',
1679 p_master_account_id => p_master_account_id,
1680 p_activity_id => fnd_api.g_miss_num,
1681 p_to_address_list => p_to_address_list,
1682 p_cc_address_list => p_cc_address_list,
1683 p_bcc_address_list => p_bcc_address_list,
1684 p_subject => p_subject,
1685 p_sr_id => null,
1686 p_customer_id => l_customer_id,
1687 p_contact_id => l_contact_id,
1688 p_mes_document_id => fnd_api.g_miss_num,
1689 p_mes_category_id => fnd_api.g_miss_num,
1690 p_interaction_id => l_interaction_id,
1691 p_qualifiers => l_qualifiers,
1692 p_message_type => null, --p_message_type, use the same as inb
1693 p_encoding => null, --p_encoding,
1694 p_character_set => null, --p_character_set,
1695 p_relationship_id => l_relationship_id,
1696 x_mc_parameters_id => l_mc_parameter_id,
1697 x_return_status => l_return_status,
1698 x_msg_count => l_msg_count,
1699 x_msg_data => l_msg_data
1700 );
1701
1702
1703 -- Check return status; Proceed on success Or report back in case of error.
1704 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1705 -- Success.
1706 --create outbound here
1707
1708 select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
1709 INSERT INTO iem_rt_interactions (
1710 rt_interaction_id, resource_id, customer_id, contact_id, type,
1711 status, expire, created_by, creation_date, last_updated_by,
1712 last_update_date, last_update_login, parent_interaction_id,
1713 service_request_id, inb_tag_id, interaction_id,
1714 mc_parameter_id, ih_creator, action_id, action_item_id,
1715 outcome_id, result_id, relationship_id)
1716 VALUES (
1717 l_i_sequence, l_resource_id, l_customer_id, l_contact_id,
1718 G_INBOUND, G_AUTOFORWAD_ACT, G_ACTIVE, l_created_by,
1719 SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
1720 l_parent_ih_id, l_sr_id, null, l_interaction_id,
1721 l_mc_parameter_id, l_ih_creator, 73, 45, -1, -1, l_relationship_id);
1722
1723
1724 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
1725 INSERT INTO iem_rt_media_items (
1726 rt_interaction_id, rt_media_item_id, resource_id,
1727 media_id, message_id, rfc822_message_id, folder_name,
1728 folder_uid, email_account_id, db_server_id, email_type,
1729 status, expire, version, created_by, creation_date,
1730 last_updated_by, last_update_date, last_update_login,
1731 edit_mode )
1732 VALUES (
1733 l_i_sequence, l_m_sequence, l_resource_id,
1734 p_media_id,
1735 p_mdt_message_id,
1736 p_rfc822_message_id,
1737 p_folder_name,
1738 p_message_uid,
1739 p_master_account_id,
1740 null,
1741 G_INBOUND, G_UNMOVED, G_ACTIVE,0, l_created_by, SYSDATE,
1742 l_last_updated_by, SYSDATE, l_last_update_login, p_attach_inb);
1743
1744 --create outbound here
1745 IEM_CLIENT_PUB.createMediaDetails (p_api_version_number => 1.0,
1746 p_init_msg_list => fnd_api.g_false,
1747 p_commit => fnd_api.g_false,
1748 p_resource_id => l_resource_id,
1749 p_rfc822_message_id => null,
1750 p_folder_name => G_NEWOUTB_FOLDER,
1751 p_folder_uid => G_NUM_NOP2,
1752 p_account_id => p_master_account_id,
1753 p_account_type => G_MASTER_ACCOUNT,
1754 p_status => G_CHAR_NOP,
1755 p_customer_id => l_customer_id,
1756 p_rt_media_item_id => l_m_sequence,
1757 p_subject => null,
1758 p_interaction_id => p_interaction_id,
1759 p_service_request_id => l_sr_id,
1760 p_mc_parameter_id => G_AUTOR_MC_PARA_ID,
1761 p_service_request_action => null,
1762 p_contact_id => l_contact_id,
1763 p_lead_id => null,
1764 p_parent_ih_id => l_parent_ih_id,
1765 p_action_id => G_NUM_NOP,
1766 p_relationship_id => l_relationship_id,
1767 x_return_status => l_return_status,
1768 x_msg_count => l_msg_count,
1769 x_msg_data => l_msg_data,
1770 x_version => l_version,
1771 x_rt_media_item_id => l_rt_media_item_id,
1772 x_rt_interaction_id => l_rt_interaction_id
1773 );
1774
1775 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1776 x_outbox_item_id := l_rt_media_item_id;
1777
1778 ELSE
1779 -- return the error returned by IEM_CLIENT_PUB.createMediaDetails
1780 x_return_status := l_return_status;
1781 x_msg_count := l_msg_count;
1782 x_msg_data := l_msg_data;
1783
1784 END IF;
1785
1786
1787 ELSE
1788 -- Return the error returned by MC_PARA_PUB API
1789 x_return_status := l_return_status;
1790 x_msg_count := l_msg_count;
1791 x_msg_data := l_msg_data;
1792
1793 END IF;
1794 -------------------End Code------------------------
1795 -- Standard Check Of p_commit.
1796 IF FND_API.To_Boolean(p_commit) THEN
1797 COMMIT WORK;
1798 END IF;
1799
1800 -- Standard callto get message count and if count is 1, get message info.
1801 FND_MSG_PUB.Count_And_Get
1802 ( p_count => x_msg_count,
1803 p_data => x_msg_data
1804 );
1805 EXCEPTION
1806 WHEN FND_API.G_EXC_ERROR THEN
1807 ROLLBACK TO autoForward_pvt;
1808 x_return_status := FND_API.G_RET_STS_ERROR ;
1809 FND_MSG_PUB.Count_And_Get(
1810 p_count => x_msg_count,
1811 p_data => x_msg_data);
1812
1813 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1814 ROLLBACK TO autoForward_pvt;
1815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1816 FND_MSG_PUB.Count_And_Get(
1817 p_count => x_msg_count,
1818 p_data => x_msg_data);
1819 WHEN IEM_BAD_RECIPIENT THEN
1820 ROLLBACK TO autoForward_pvt;
1821 x_return_status := FND_API.G_RET_STS_ERROR;
1822 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_RECIPIENT');
1823 FND_MSG_PUB.ADD;
1824 FND_MSG_PUB.Count_And_Get(
1825 p_encoded => FND_API.G_TRUE,
1826 p_count => x_msg_count,
1827 p_data => x_msg_data);
1828 WHEN OTHERS THEN
1829 ROLLBACK TO autoForward_pvt;
1830 x_return_status := FND_API.G_RET_STS_ERROR;
1831 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1832 THEN
1833 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1834 END IF;
1835 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1836 p_data => x_msg_data);
1837
1838 END autoForward;
1839
1840 PROCEDURE createSRAutoNotification(
1841 p_api_version_number IN NUMBER,
1842 p_init_msg_list IN VARCHAR2,
1843 p_commit IN VARCHAR2,
1844 p_media_id IN NUMBER,
1845 p_master_account_id IN NUMBER,
1846 p_to_address_list IN VARCHAR2,
1847 p_cc_address_list IN VARCHAR2,
1848 p_bcc_address_list IN VARCHAR2,
1849 p_subject IN VARCHAR2,
1850 p_tag_key_value_tbl IN keyVals_tbl_type,
1851 p_customer_id IN NUMBER,
1852 p_interaction_id IN NUMBER,
1853 p_resource_id IN NUMBER,
1854 p_qualifiers IN QualifierRecordList,
1855 p_contact_id IN NUMBER,
1856 p_relationship_id IN NUMBER,
1857 p_message_id IN NUMBER,
1858 p_sr_id IN NUMBER,
1859 x_outbox_item_id OUT NOCOPY NUMBER,
1860 x_return_status OUT NOCOPY VARCHAR2,
1861 x_msg_count OUT NOCOPY NUMBER,
1862 x_msg_data OUT NOCOPY VARCHAR2
1863 ) IS
1864 l_api_name VARCHAR2(255):='createSRAutoNotification';
1865 l_api_version_number NUMBER:=1.0;
1866 l_created_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1867 l_last_updated_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1868 l_last_update_login NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
1869
1870 l_return_status VARCHAR2(300);
1871 l_msg_count NUMBER;
1872 l_msg_data VARCHAR2(300);
1873
1874 l_i_sequence NUMBER;
1875 l_m_sequence NUMBER;
1876 l_version NUMBER;
1877 l_rt_interaction_id NUMBER;
1878 l_rt_media_item_id NUMBER;
1879 l_tag_key_value keyVals_tbl_type;
1880 l_customer_id NUMBER := null;
1881 l_contact_id NUMBER := null;
1882 l_parent_ih_id NUMBER := null;
1883 l_interaction_id NUMBER;
1884 l_ih_creator VARCHAR2(1);
1885 l_resource_id NUMBER;
1886 l_mc_parameter_id NUMBER;
1887 l_qualifiers IEM_MC_PUB.QualifierRecordList;
1888 l_relationship_id NUMBER;
1889 l_outcome_id NUMBER;
1890 l_result_id NUMBER;
1891 l_reason_id NUMBER;
1892 IEM_BAD_IH_ID EXCEPTION;
1893 BEGIN
1894
1895 -- Standard Start of API savepoint
1896 SAVEPOINT createSRAutoNotification_spt;
1897
1898 -- Standard call to check for call compatibility.
1899 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1900 1.0,
1901 l_api_name,
1902 G_PKG_NAME)
1903 THEN
1904 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1905 END IF;
1906
1907 -- Initialize message list if p_init_msg_list is set to TRUE.
1908 IF FND_API.to_Boolean( p_init_msg_list )
1909 THEN
1910 FND_MSG_PUB.initialize;
1911 END IF;
1912
1913 -- Initialize API return status to SUCCESS
1914 x_return_status := FND_API.G_RET_STS_SUCCESS;
1915
1916 -----------------------Code------------------------
1917 -- Extract tag key value from key value table
1918 -- Currently valid system key names:
1919 -- IEMNBZTSRVSRID for sr id ignore for auto sr cases
1920 -- IEMNINTERACTIONID for interaction id
1921 -- IEMNAGENTID for agent id
1922 -- IEMNCUSTOMERID for customer id
1923 -- IEMNCONTACTID for contact id
1924 -- IEMNRELATIONSHIPID for relationship id
1925
1926 IF (p_tag_key_value_tbl.count > 0 ) THEN
1927 FOR i IN p_tag_key_value_tbl.FIRST..p_tag_key_value_tbl.LAST LOOP
1928 BEGIN
1929 IF (p_tag_key_value_tbl(i).key = 'IEMNINTERACTIONID' ) THEN
1930 l_parent_ih_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
1931 ELSIF (p_tag_key_value_tbl(i).key = 'IEMNCUSTOMERID' ) THEN
1932 l_customer_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
1933 ELSIF (p_tag_key_value_tbl(i).key = 'IEMNCONTACTID' ) THEN
1934 l_contact_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
1935 ELSIF (p_tag_key_value_tbl(i).key = 'IEMNRELATIONSHIPID' ) THEN
1936 l_relationship_id := TO_NUMBER(p_tag_key_value_tbl(i).value);
1937 END IF;
1938 END;
1939 END LOOP;
1940 END IF;
1941
1942
1943 -- customer id and contact id from tagging supersede the result from
1944 -- email search i.e. what are from inputs
1945 IF (l_customer_id is NULL) THEN
1946 BEGIN
1947 l_customer_id := p_customer_id;
1948 l_contact_id := p_contact_id;
1949 l_relationship_id := p_relationship_id;
1950 END;
1951 END IF;
1952
1953 -- Find resource_id by searching outbox_processing_agent.
1954 l_resource_id := p_resource_id;
1955
1956 -- Record details into the RT tables.
1957 IF ( p_interaction_id = fnd_api.g_miss_num) THEN
1958 l_interaction_id := null;
1959 l_ih_creator := null;
1960 ELSE
1961 l_interaction_id := p_interaction_id;
1962 l_ih_creator := 'Y';
1963 begin
1964 select result_id, reason_id, outcome_id
1965 into l_result_id, l_reason_id, l_outcome_id
1966 from jtf_ih_interactions
1967 where interaction_id = p_interaction_id;
1968 exception
1969 when others then
1970 --dbms_output.put_line(SQLERRM);
1971 raise IEM_BAD_IH_ID;
1972 end;
1973 END IF;
1974
1975 -- create iem_mc_parameter record
1976 IF (p_qualifiers.count > 0) THEN
1977 BEGIN
1978 FOR i IN p_qualifiers.first .. p_qualifiers.Last
1979 LOOP
1980 l_qualifiers(i).QUALIFIER_NAME := p_qualifiers(i).QUALIFIER_NAME;
1981 l_qualifiers(i).QUALIFIER_VALUE := p_qualifiers(i).QUALIFIER_VALUE;
1982 END LOOP;
1983 END;
1984 END IF;
1985
1986 IEM_MC_PUB.prepareMessageComponentII
1987 (p_api_version_number => 1.0,
1988 p_init_msg_list =>fnd_api.g_false,
1989 p_commit =>fnd_api.g_false,
1990 p_action => 'srautonotification',
1991 p_master_account_id => p_master_account_id,
1992 p_activity_id => fnd_api.g_miss_num,
1993 p_to_address_list => p_to_address_list,
1994 p_cc_address_list => p_cc_address_list,
1995 p_bcc_address_list => p_bcc_address_list,
1996 p_subject => p_subject,
1997 p_sr_id => p_sr_id,
1998 p_customer_id => l_customer_id,
1999 p_contact_id => l_contact_id,
2000 p_mes_document_id => fnd_api.g_miss_num,
2001 p_mes_category_id => fnd_api.g_miss_num,
2002 p_interaction_id => null,
2003 p_qualifiers => l_qualifiers,
2004 p_message_type => null, --p_message_type,
2005 p_encoding => null, --p_encoding,
2006 p_character_set => null, --p_character_set,
2007 p_relationship_id => l_relationship_id,
2008 x_mc_parameters_id => l_mc_parameter_id,
2009 x_return_status => l_return_status,
2010 x_msg_count => l_msg_count,
2011 x_msg_data => l_msg_data
2012 );
2013
2014
2015 -- Check return status; Proceed on success Or report back in case of error.
2016 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2017 -- Success.
2018
2019
2020 select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
2021 INSERT INTO iem_rt_interactions (
2022 rt_interaction_id, resource_id, customer_id, contact_id, type,
2023 status, expire, created_by, creation_date, last_updated_by,
2024 last_update_date, last_update_login, parent_interaction_id,
2025 service_request_id, inb_tag_id, interaction_id,
2026 mc_parameter_id, ih_creator, action_id, action_item_id,
2027 relationship_id, result_id, reason_id, outcome_id)
2028 VALUES (
2029 l_i_sequence, l_resource_id, l_customer_id, l_contact_id,
2030 G_INBOUND, 'S', G_ACTIVE, l_created_by,
2031 SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
2032 l_parent_ih_id, p_sr_id, null, l_interaction_id,
2033 l_mc_parameter_id, l_ih_creator, 22, 45, l_relationship_id,
2034 l_result_id, l_reason_id, l_outcome_id);
2035
2036 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
2037 INSERT INTO iem_rt_media_items (
2038 rt_interaction_id, rt_media_item_id, resource_id,
2039 media_id, message_id, rfc822_message_id, folder_name,
2040 folder_uid, email_account_id, db_server_id, email_type,
2041 status, expire, version, created_by, creation_date,
2042 last_updated_by, last_update_date, last_update_login )
2043 VALUES (
2044 l_i_sequence, l_m_sequence, l_resource_id,
2045 p_media_id,
2046 p_message_id,
2047 null,
2048 null,
2049 null,
2050 p_master_account_id,
2051 null,
2052 G_INBOUND, G_UNMOVED, G_ACTIVE,0, l_created_by, SYSDATE,
2053 l_last_updated_by, SYSDATE, l_last_update_login );
2054
2055 --create outbound here
2056 IEM_CLIENT_PUB.createMediaDetails (p_api_version_number => 1.0,
2057 p_init_msg_list => fnd_api.g_false,
2058 p_commit => fnd_api.g_false,
2059 p_resource_id => l_resource_id,
2060 p_rfc822_message_id => null,
2061 p_folder_name => G_NEWOUTB_FOLDER,
2062 p_folder_uid => null,
2063 p_account_id => p_master_account_id,
2064 p_account_type => G_MASTER_ACCOUNT,
2065 p_status => G_CHAR_NOP,
2066 p_customer_id => l_customer_id,
2067 p_rt_media_item_id => l_m_sequence,
2068 p_subject => null,
2069 p_interaction_id => p_interaction_id,
2070 p_service_request_id => p_sr_id,
2071 p_mc_parameter_id => G_AUTOR_MC_PARA_ID,
2072 p_service_request_action => null,
2073 p_contact_id => l_contact_id,
2074 p_lead_id => null,
2075 p_parent_ih_id => null,
2076 p_action_id => G_NUM_NOP,
2077 p_relationship_id => l_relationship_id,
2078 x_return_status => l_return_status,
2079 x_msg_count => l_msg_count,
2080 x_msg_data => l_msg_data,
2081 x_version => l_version,
2082 x_rt_media_item_id => l_rt_media_item_id,
2083 x_rt_interaction_id => l_rt_interaction_id
2084 );
2085
2086 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2087 x_outbox_item_id := l_rt_media_item_id;
2088
2089 ELSE
2090 -- return the error returned by IEM_CLIENT_PUB.createMediaDetails
2091 x_return_status := l_return_status;
2092 x_msg_count := l_msg_count;
2093 x_msg_data := l_msg_data;
2094
2095 END IF;
2096
2097 ELSE
2098 -- return the error returned by IEM_MC_PUB.prepareMessageComponentII
2099 x_return_status := l_return_status;
2100 x_msg_count := l_msg_count;
2101 x_msg_data := l_msg_data;
2102
2103 END IF;
2104
2105 -------------------End Code------------------------
2106 -- Standard Check Of p_commit.
2107 IF FND_API.To_Boolean(p_commit) THEN
2108 COMMIT WORK;
2109 END IF;
2110
2111 -- Standard callto get message count and if count is 1, get message info.
2112 FND_MSG_PUB.Count_And_Get
2113 ( p_count => x_msg_count,
2114 p_data => x_msg_data
2115 );
2116 EXCEPTION
2117 WHEN IEM_BAD_IH_ID THEN
2118 ROLLBACK TO createSRAutoNotification_spt;
2119 x_return_status := FND_API.G_RET_STS_ERROR ;
2120 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_IH_ID');
2121 FND_MSG_PUB.ADD;
2122 FND_MSG_PUB.Count_And_Get(
2123 p_count => x_msg_count,
2124 p_data => x_msg_data);
2125 WHEN FND_API.G_EXC_ERROR THEN
2126 ROLLBACK TO createSRAutoNotification_spt;
2127 x_return_status := FND_API.G_RET_STS_ERROR ;
2128 FND_MSG_PUB.Count_And_Get(
2129 p_count => x_msg_count,
2130 p_data => x_msg_data);
2131
2132 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2133 ROLLBACK TO createSRAutoNotification_spt;
2134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2135 FND_MSG_PUB.Count_And_Get(
2136 p_count => x_msg_count,
2137 p_data => x_msg_data);
2138
2139 WHEN OTHERS THEN
2140 ROLLBACK TO createSRAutoNotification_spt;
2141 x_return_status := FND_API.G_RET_STS_ERROR;
2142 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2143 THEN
2144 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
2145 END IF;
2146 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2147 p_data => x_msg_data);
2148 END createSRAutoNotification;
2149
2150
2151 END IEM_OUTBOX_PROC_PUB;