[Home] [Help]
PACKAGE BODY: APPS.IEM_CLIENT_PUB
Source
1 PACKAGE BODY IEM_CLIENT_PUB as
2 /* $Header: iempcltb.pls 120.14.12010000.3 2008/11/26 00:26:12 rtripath ship $*/
3
4 -- PACKAGE CONSTANTS NO LITERALS USED.
5 G_PKG_NAME CONSTANT varchar2(30) :='IEM_CLIENT_PUB';
6 G_WORK_IN_PROGRESS CONSTANT VARCHAR2(1) := 'P';
7 G_TRANSFER CONSTANT VARCHAR2(1) := 'R';
8 G_WRAP_UP CONSTANT VARCHAR2(1) := 'W';
9 G_INBOUND CONSTANT VARCHAR2(1) := 'I';
10 G_OUTBOUND CONSTANT VARCHAR2(1) := 'O';
11 G_EXPIRE CONSTANT VARCHAR2(1) := 'Y';
12 G_ACTIVE CONSTANT VARCHAR2(1) := 'N';
13 G_DORMANT CONSTANT VARCHAR2(1) := 'D';
14 G_QUEUEOUT CONSTANT VARCHAR2(1) := 'Q';
15 G_PROCESSING CONSTANT VARCHAR2(1) := 'G';
16 G_NUM_NOP CONSTANT NUMBER := -99;
17 G_NUM_NOP2 CONSTANT NUMBER := -1;
18 G_CHAR_NOP CONSTANT VARCHAR2(1) := ' ';
19 G_UNREAD CONSTANT VARCHAR2(1) := 'U';
20 G_UNMOVED CONSTANT VARCHAR2(1) := 'M';
21 G_MASTER_ACCOUNT CONSTANT VARCHAR2(1) := 'M';
22 G_AGENT_ACCOUNT CONSTANT VARCHAR2(1) := 'A';
23 G_O_DIRECTION CONSTANT VARCHAR2(10) := 'OUTBOUND';
24 G_I_DIRECTION CONSTANT VARCHAR2(10) := 'INBOUND';
25 G_MEDIA_TYPE CONSTANT VARCHAR2(10) := 'EMAIL';
26 G_NEWREROUTE CONSTANT VARCHAR2(1) := 'H';
27 G_REDIRECT CONSTANT VARCHAR2(1) := 'R';
28
29 PROCEDURE getWork (p_api_version_number IN NUMBER,
30 p_init_msg_list IN VARCHAR2,
31 p_commit IN VARCHAR2,
32 p_resource_id IN NUMBER,
33 p_email_account_id IN NUMBER,
34 p_classification_id IN NUMBER,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2,
38 x_rt_media_item_id OUT NOCOPY NUMBER,
39 x_email_account_id OUT NOCOPY NUMBER,
40 x_oes_id OUT NOCOPY NUMBER,
41 x_folder_name OUT NOCOPY VARCHAR2,
42 x_folder_uid OUT NOCOPY NUMBER,
43 x_rt_interaction_id OUT NOCOPY NUMBER,
44 x_customer_id OUT NOCOPY NUMBER,
45 x_rfc822_message_id OUT NOCOPY VARCHAR2,
46 x_route_classification OUT NOCOPY VARCHAR2,
47 x_mdt_message_id OUT NOCOPY NUMBER,
48 x_service_request_id OUT NOCOPY NUMBER,
49 x_contact_id OUT NOCOPY NUMBER,
50 x_classification_id OUT NOCOPY NUMBER,
51 x_lead_id OUT NOCOPY NUMBER,
52 x_relationship_id OUT NOCOPY NUMBER
53 ) IS
54
55 l_api_name VARCHAR2(255);
56 l_api_version_number NUMBER;
57 l_created_by NUMBER;
58 l_last_updated_by NUMBER;
59 l_last_update_login NUMBER;
60
61 l_email_data_rec IEM_RT_PROC_EMAILS%ROWTYPE;
62 l_status VARCHAR2(300);
63 l_msg_count NUMBER;
64 l_msg_data VARCHAR2(300);
65
66 l_i_sequence NUMBER;
67 l_m_sequence NUMBER;
68 l_db_server_id NUMBER;
69 l_classification_id NUMBER;
70 l_tag_key_value_tbl IEM_MAILITEM_PUB.keyVals_tbl_type;
71 l_tag_id VARCHAR2(30);
72 l_sr_id NUMBER;
73 l_parent_ih_id NUMBER;
74 l_customer_id NUMBER;
75 l_contact_id NUMBER;
76 l_lead_id NUMBER;
77 l_ih_creator VARCHAR2(1);
78 l_t_number_tbl IEM_MAILITEM_PUB.t_number_table;
79 l_relationship_id NUMBER;
80
81 BEGIN
82
83 -- Standard Start of API savepoint
84 SAVEPOINT getWork_pvt;
85
86 -- Initialize variables
87 l_api_name :='getWork';
88 l_api_version_number :=1.0;
89 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
90 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
91 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
92 l_sr_id :=null;
93 l_parent_ih_id :=null;
94 l_customer_id :=null;
95 l_contact_id :=null;
96 l_lead_id :=null;
97
98
99 -- Standard call to check for call compatibility.
100 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
101 1.0,
102 l_api_name,
103 G_PKG_NAME)
104 THEN
105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
106 END IF;
107
108 -- Initialize message list if p_init_msg_list is set to TRUE.
109 IF FND_API.to_Boolean( p_init_msg_list )
110 THEN
111 FND_MSG_PUB.initialize;
112 END IF;
113
114 -- Initialize API return status to SUCCESS
115 x_return_status := FND_API.G_RET_STS_SUCCESS;
116
117 -----------------------Code------------------------
118 /* dbms_output.put_line('In getWork ');
119 dbms_output.put_line('In getWork : Email Account ID '||p_email_account_id);
120 */
121
122 --Future: Call Media Check [check if max interactions reached]
123
124 --Set the l_classification_id to null if it is a NOP per 9i standard
125 select decode(p_classification_id,G_NUM_NOP2, null,
126 p_classification_id) into l_classification_id from DUAL;
127
128 --Call MailItem to get the next available message.
129 IEM_MAILITEM_PUB.GetMailItem( p_api_version_number=> 1.0,
130 p_init_msg_list => 'F',
131 p_commit => 'F',
132 p_resource_id => p_resource_id ,
133 p_tbl => l_t_number_tbl,
134 p_rt_classification => l_classification_id,
135 p_account_id => p_email_account_id,
136 x_email_data => l_email_data_rec,
137 x_tag_key_value => l_tag_key_value_tbl,
138 x_encrypted_id => l_tag_id,
139 x_return_status => l_status,
140 x_msg_count => l_msg_count,
141 x_msg_data => l_msg_data);
142
143 -- Check return status; Proceed on success Or report back in case of error.
144 IF (l_status = FND_API.G_RET_STS_SUCCESS) THEN
145 -- Success.
146 -- Get the name of the route classification from the ID returned above.
147 -- This is the name of the folder where the inbound message exists on the
148 -- master account.
149 SELECT name INTO x_route_classification
150 FROM iem_route_classifications
151 WHERE ROUTE_CLASSIFICATION_ID = l_email_data_rec.RT_CLASSIFICATION_ID;
152
153 -- Set the folder name
154 x_folder_name := x_route_classification;
155
156 -- Extract tag key value from key value table
157 -- Currently valid system key names:
158 -- IEMNBZTSRVSRID for sr id
159 -- IEMNINTERACTIONID for interaction id
160 -- IEMNAGENTID for agent id
161 -- IEMNCUSTOMERID for customer id
162 -- IEMNCONTACTID for contact id
163 -- IEMNBZSALELEADID for lead id
164
165 FOR i IN 1..l_tag_key_value_tbl.count LOOP
166 BEGIN
167 IF (l_tag_key_value_tbl(i).key = 'IEMNBZTSRVSRID' ) THEN
168 l_sr_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
169 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNINTERACTIONID' ) THEN
170 l_parent_ih_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
171 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNCUSTOMERID' ) THEN
172 l_customer_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
173 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNCONTACTID' ) THEN
174 l_contact_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
175 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNRELATIONSHIPID' ) THEN
176 l_relationship_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
177 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNBZSALELEADID' ) THEN
178 l_lead_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
179 END IF;
180 END;
181 END LOOP;
182
183 -- customer id and contact id from tagging supersede the result from
184 -- email search (i.e. what are in l_email_date_rec)
185 IF (l_customer_id is NULL) THEN
186 BEGIN
187 l_customer_id := l_email_data_rec.CUSTOMER_ID;
188 l_contact_id := null;
189 l_relationship_id := null;
190 END;
191 END IF;
192
193 -- Record details into the RT tables.
194 l_ih_creator := null;
195 if ( l_email_data_rec.IH_INTERACTION_ID is not null ) then
196 l_ih_creator := 'S'; -- server created
197 end if;
198
199 select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
200 INSERT INTO iem_rt_interactions (
201 rt_interaction_id, resource_id, customer_id, contact_id, type,
202 status, expire, created_by, creation_date, last_updated_by,
203 last_update_date, last_update_login, parent_interaction_id,
204 service_request_id, inb_tag_id, interaction_id, ih_creator,
205 lead_id )
206 VALUES (
207 l_i_sequence, p_resource_id, l_customer_id, l_contact_id,
208 G_INBOUND, G_WORK_IN_PROGRESS, G_ACTIVE, l_created_by,
209 SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
210 l_parent_ih_id, l_sr_id, l_tag_id,
211 l_email_data_rec.IH_INTERACTION_ID, l_ih_creator,
212 l_lead_id
213 );
214 -- db_server id used by mid-tier to locate accounts
215 l_db_server_id := -1;
216
217 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
218 INSERT INTO iem_rt_media_items (
219 rt_interaction_id, rt_media_item_id, resource_id,
220 media_id, message_id, rfc822_message_id, folder_name,
221 folder_uid, email_account_id, db_server_id, email_type,
222 status, expire, version, created_by, creation_date,
223 last_updated_by, last_update_date, last_update_login )
224 VALUES (
225 l_i_sequence, l_m_sequence, p_resource_id,
226 l_email_data_rec.IH_MEDIA_ITEM_ID,
227 l_email_data_rec.MESSAGE_ID,
228 null,
229 x_folder_name,
230 -1,
231 l_email_data_rec.EMAIL_ACCOUNT_ID,
232 l_db_server_id,
233 G_INBOUND, G_UNMOVED, G_ACTIVE,0, l_created_by, SYSDATE,
234 l_last_updated_by, SYSDATE, l_last_update_login
235 );
236
237 -- open the row at IEM_RT_PROC_EMAILS
238 UPDATE IEM_RT_PROC_EMAILS SET queue_status = NULL
239 WHERE message_id = l_email_data_rec.MESSAGE_ID;
240
241 -- Return Media Values to the JSPs.
242 x_rt_media_item_id := l_m_sequence;
243 x_email_account_id := l_email_data_rec.EMAIL_ACCOUNT_ID;
244 x_oes_id := l_db_server_id;
245 x_folder_uid := -1;
246 x_customer_id := l_customer_id;
247 x_rfc822_message_id := null;
248 x_rt_interaction_id := l_i_sequence;
249 x_mdt_message_id := l_email_data_rec.MESSAGE_ID;
250 x_service_request_id := l_sr_id;
251 x_contact_id := l_contact_id;
252 x_classification_id := l_email_data_rec.RT_CLASSIFICATION_ID;
253 x_lead_id := l_lead_id;
254 x_relationship_id := l_relationship_id;
255
256 ELSE
257 -- Return the error returned by MDT API
258 x_return_status := l_status;
259 x_msg_count := l_msg_count;
260 x_msg_data := l_msg_data;
261
262 END IF;
263 -------------------End Code------------------------
264 -- Standard Check Of p_commit.
265 IF FND_API.To_Boolean(p_commit) THEN
266 COMMIT WORK;
267 END IF;
268 -- Standard callto get message count and if count is 1, get message info.
269 FND_MSG_PUB.Count_And_Get
270 ( p_encoded => FND_API.G_TRUE,
271 p_count => x_msg_count,
272 p_data => x_msg_data
273 );
274 EXCEPTION
275 WHEN FND_API.G_EXC_ERROR THEN
276 ROLLBACK TO getWork_pvt;
277 x_return_status := FND_API.G_RET_STS_ERROR ;
278 FND_MSG_PUB.Count_And_Get(
279 p_encoded => FND_API.G_TRUE,
280 p_count => x_msg_count,
281 p_data => x_msg_data);
282
283 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
284 ROLLBACK TO getWork_pvt;
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
286 FND_MSG_PUB.Count_And_Get(
287 p_encoded => FND_API.G_TRUE,
288 p_count => x_msg_count,
289 p_data => x_msg_data);
290
291 WHEN OTHERS THEN
292 ROLLBACK TO getWork_pvt;
293 x_return_status := FND_API.G_RET_STS_ERROR;
294 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
295 THEN
296 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
297 END IF;
298 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
299 p_count => x_msg_count,
300 p_data => x_msg_data);
301 END getWork;
302
303 /* Use cases. Get details of the specified rt_media_item
304 OR
305 Get details of the rt_media_item related by
306 the interaction_id. The p_email_type is used
307 to specify the inbound or outbound media item
308 thats related to the one specified. If the
309 relational details are missing then the parent
310 details are returned enabling the JSPs to
311 create new rt_data.
312 */
313 PROCEDURE getMediaDetails (p_api_version_number IN NUMBER,
314 p_init_msg_list IN VARCHAR2,
315 p_commit IN VARCHAR2,
316 p_rt_media_item_id IN NUMBER,
317 p_version IN NUMBER,
318 p_email_type IN VARCHAR2,
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2,
322 x_rfc822_message_id OUT NOCOPY VARCHAR2,
323 x_account_id OUT NOCOPY NUMBER,
324 x_account_type OUT NOCOPY VARCHAR2,
325 x_email_type OUT NOCOPY VARCHAR2,
326 x_status OUT NOCOPY VARCHAR2,
327 x_version OUT NOCOPY NUMBER,
328 x_rt_media_item_id OUT NOCOPY NUMBER,
329 x_rt_interaction_id OUT NOCOPY NUMBER,
330 x_oes_id OUT NOCOPY NUMBER,
331 x_folder_name OUT NOCOPY VARCHAR2,
332 x_message_id OUT NOCOPY NUMBER, -- change to iem_rt_proc_emails.message_id since 11.5.11
333 x_customer_id OUT NOCOPY NUMBER,
334 x_interaction_id OUT NOCOPY NUMBER,
335 x_service_request_id OUT NOCOPY NUMBER,
336 x_mc_parameter_id OUT NOCOPY NUMBER,
337 x_service_request_action OUT NOCOPY VARCHAR2,
338 x_contact_id OUT NOCOPY NUMBER,
339 x_parent_ih_id OUT NOCOPY NUMBER,
340 x_tag_id OUT NOCOPY VARCHAR2,
341 x_edit_mode OUT NOCOPY VARCHAR2,
342 x_lead_id OUT NOCOPY NUMBER,
343 x_resource_id OUT NOCOPY NUMBER,
344 x_relationship_id OUT NOCOPY NUMBER,
345 x_ih_media_id OUT NOCOPY NUMBER
346 ) IS
347
348 l_api_name VARCHAR2(255);
349 l_api_version_number NUMBER;
350
351 l_email_account_id NUMBER;
352 l_agent_account_id NUMBER;
353 l_email_account_id1 NUMBER;
354 l_agent_account_id1 NUMBER;
355 l_account_id NUMBER;
356 l_account_type VARCHAR2(3);
357 l_email_type VARCHAR2(3);
358 l_status VARCHAR2(3);
359 l_rfc822_message_id VARCHAR2(300);
360 l_version NUMBER;
361 l_rt_media_item_id NUMBER;
362 l_rt_interaction_id NUMBER;
363 l_oes_id NUMBER;
364 l_message_id NUMBER;
365 l_customer_id NUMBER;
366 l_folder_name VARCHAR2(300);
367 l_inpEmail_type VARCHAR2(2);
368 l_expire VARCHAR2(1);
369 l_edit_mode VARCHAR2(1);
370
371 InteractnComplt EXCEPTION;
372 badAccountType EXCEPTION;
373 IEM_NO_DATA EXCEPTION;
374 l_found NUMBER;
375 l_ih_media_id NUMBER;
376
377 BEGIN
378
379 -- Standard Start of API savepoint
380 SAVEPOINT getMediaDetails_pvt;
381
382 -- Init vars
383 l_api_name :='getMediaDetails';
384 l_api_version_number :=1.0;
385
386 l_email_account_id := 0;
387 l_agent_account_id := 0;
388 l_email_account_id1 := 0;
389 l_agent_account_id1 := 0;
390 l_account_id := 0;
391 l_version := 0;
392 l_rt_media_item_id := 0;
393 l_rt_interaction_id := 0;
394 l_oes_id := 0;
395 l_message_id := 0;
396 l_customer_id := 0;
397 l_found := 0;
398
399 -- Standard call to check for call compatibility.
400 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
401 1.0,
402 l_api_name,
403 G_PKG_NAME)
404 THEN
405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
406 END IF;
407
408 -- Initialize message list if p_init_msg_list is set to TRUE.
409 IF FND_API.to_Boolean( p_init_msg_list )
410 THEN
411 FND_MSG_PUB.initialize;
412 END IF;
413
414 -- Initialize API return status to SUCCESS
415 x_return_status := FND_API.G_RET_STS_SUCCESS;
416
417 -----------------------Code------------------------
418 -- Do a query anyway to get media details of the specified rt_media_item.
419 BEGIN
420 SELECT rt_interaction_id, rt_media_item_id, rfc822_message_id,
421 folder_name, message_id, email_account_id, agent_account_id,
422 db_server_id, email_type, status, version, expire, edit_mode,
423 media_id
424 INTO l_rt_interaction_id, l_rt_media_item_id, l_rfc822_message_id,
425 l_folder_name, l_message_id, l_email_account_id, l_agent_account_id,
426 l_oes_id, l_email_type, l_status, l_version, l_expire, l_edit_mode,
427 l_ih_media_id
428 FROM iem_rt_media_items
429 WHERE rt_media_item_id = p_rt_media_item_id;
430
431 -- Collect the data thats needed at a later stage.
432 l_inpEmail_type := l_email_type;
433
434 SELECT customer_id, contact_id, interaction_id, parent_interaction_id,
435 service_request_id, service_request_action, mc_parameter_id,
436 inb_tag_id, lead_id, resource_id, relationship_id
437 INTO x_customer_id, x_contact_id, x_interaction_id, x_parent_ih_id,
438 x_service_request_id, x_service_request_action, x_mc_parameter_id, x_tag_id,
439 x_lead_id, x_resource_id, x_relationship_id
440 FROM iem_rt_interactions
441 WHERE rt_interaction_id = l_rt_interaction_id;
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 raise IEM_NO_DATA;
446 END;
447
448
449 -- Check if the email type matches,
450 IF (UPPER(p_email_type) <> UPPER (l_email_type)) THEN
451
452 -- if not, get details of the correct media type.
453 BEGIN
454 x_rt_media_item_id := null;
455 SELECT rt_interaction_id, rt_media_item_id, rfc822_message_id,
456 folder_name, message_id, email_account_id, agent_account_id,
457 db_server_id, email_type, status, version, edit_mode, media_id
458 INTO x_rt_interaction_id, x_rt_media_item_id, x_rfc822_message_id,
459 x_folder_name, x_message_id, l_email_account_id1, l_agent_account_id1,
460 x_oes_id, x_email_type, x_status, x_version, x_edit_mode, x_ih_media_id
461 FROM iem_rt_media_items
462 WHERE rt_interaction_id = l_rt_interaction_id
463 AND email_type = p_email_type
464 AND expire in (G_ACTIVE, G_QUEUEOUT);
465 EXCEPTION
466 WHEN OTHERS THEN
467 -- dbms_output.put_line(SQLERRM);
468 NULL;
469 END;
470
471 -- The requested media type exists.
472 if (x_rt_media_item_id IS NOT NULL) then
473 l_email_account_id := l_email_account_id1;
474 l_agent_account_id := l_agent_account_id1;
475 l_found := 1;
476 end if;
477 END IF;
478
479 IF ( l_found = 0 ) THEN
480 -- Type matches. Populate return values from the data obtained in the initial query.
481 -- Or the requested media type does not exist, return "parent's" details.
482 if (l_expire <> G_EXPIRE) then
483 x_rt_interaction_id := l_rt_interaction_id;
484 x_rt_media_item_id := l_rt_media_item_id;
485 x_rfc822_message_id := l_rfc822_message_id;
486 x_folder_name := l_folder_name;
487 x_message_id := l_message_id;
488 x_oes_id := l_oes_id;
489 x_email_type := l_email_type;
490 x_status := l_status;
491 x_version := l_version;
492 x_edit_mode := l_edit_mode;
493 x_ih_media_Id := l_ih_media_id;
494 else
495 -- only un-expired data is displayed.
496 raise IEM_NO_DATA;
497 end if;
498 END IF;
499
500 -- dbms_output.put_line('p_version = ' || p_version ||' x_version = ' || x_version);
501 --Check for version mismatch. This is important only when requesting an outbound and
502 -- getting an outbound email.
503 IF ((p_version <> x_version) AND
504 (UPPER(p_email_type) = G_OUTBOUND) AND
505 (UPPER(l_inpEmail_type) = G_OUTBOUND)) THEN
506 x_return_status := 'M';
507 END IF;
508
509 -- set account type
510 IF ((l_email_account_id IS NULL) AND (l_agent_account_id IS NOT NULL)) THEN
511 x_account_id := l_agent_account_id;
512 x_account_type := G_AGENT_ACCOUNT;
513 ELSIF ((l_agent_account_id IS NULL) AND (l_email_account_id IS NOT NULL)) THEN
514 x_account_id := l_email_account_id;
515 x_account_type := G_MASTER_ACCOUNT;
516 ELSIF ((l_agent_account_id IS NOT NULL) AND (l_email_account_id IS NOT NULL)) THEN
517 x_account_id := l_agent_account_id;
518 x_account_type := G_AGENT_ACCOUNT;
519 ELSE
520 raise badAccountType;
521 END IF;
522
523 -------------------End Code------------------------
524 EXCEPTION
525 WHEN IEM_NO_DATA THEN
526 ROLLBACK TO getMediaDetails_pvt;
527 x_return_status := FND_API.G_RET_STS_ERROR ;
528 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
529 FND_MSG_PUB.ADD;
530 FND_MSG_PUB.Count_And_Get(
531 p_encoded => FND_API.G_TRUE,
532 p_count => x_msg_count,
533 p_data => x_msg_data);
534 WHEN badAccountType THEN
535 ROLLBACK TO getMediaDetails_pvt;
536 x_return_status := FND_API.G_RET_STS_ERROR ;
537 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_ACCOUNT_TYPE');
538 FND_MSG_PUB.ADD;
539 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
540 p_count => x_msg_count,
541 p_data => x_msg_data);
542 WHEN FND_API.G_EXC_ERROR THEN
543 ROLLBACK TO getMediaDetails_pvt;
544 x_return_status := FND_API.G_RET_STS_ERROR ;
545 FND_MSG_PUB.Count_And_Get(
546 p_encoded => FND_API.G_TRUE,
547 p_count => x_msg_count,
548 p_data => x_msg_data);
549
550 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
551 ROLLBACK TO getMediaDetails_pvt;
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
553 FND_MSG_PUB.Count_And_Get(
554 p_encoded => FND_API.G_TRUE,
555 p_count => x_msg_count,
556 p_data => x_msg_data);
557
558 WHEN OTHERS THEN
559 ROLLBACK TO getMediaDetails_pvt;
560 x_return_status := FND_API.G_RET_STS_ERROR;
561 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
562 THEN
563 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
564 END IF;
565 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
566 p_count => x_msg_count,
567 p_data => x_msg_data);
568 END getMediaDetails;
569
570 /* Provide rt details for emails found by searching OES through JMA
571 */
572 PROCEDURE getSearchDetails (p_api_version_number IN NUMBER,
573 p_init_msg_list IN VARCHAR2,
574 p_commit IN VARCHAR2,
575 p_agentAccount_id IN NUMBER,
576 p_message_id IN NUMBER,
577 p_folder_name IN VARCHAR2,
578 p_email_type IN VARCHAR2,
579 x_return_status OUT NOCOPY VARCHAR2,
580 x_msg_count OUT NOCOPY NUMBER,
581 x_msg_data OUT NOCOPY VARCHAR2,
582 x_rfc822_message_id OUT NOCOPY VARCHAR2,
583 x_account_id OUT NOCOPY NUMBER,
584 x_account_type OUT NOCOPY VARCHAR2,
585 x_email_type OUT NOCOPY VARCHAR2,
586 x_status OUT NOCOPY VARCHAR2,
587 x_version OUT NOCOPY NUMBER,
588 x_rt_media_item_id OUT NOCOPY NUMBER,
589 x_rt_interaction_id OUT NOCOPY NUMBER,
590 x_oes_id OUT NOCOPY NUMBER,
591 x_folder_name OUT NOCOPY VARCHAR2,
592 x_folder_uid OUT NOCOPY NUMBER,
593 x_customer_id OUT NOCOPY NUMBER,
594 x_route_classification OUT NOCOPY VARCHAR2,
595 x_route_classification_id OUT NOCOPY NUMBER,
596 x_mdt_message_id OUT NOCOPY NUMBER,
597 x_interaction_id OUT NOCOPY NUMBER,
598 x_service_request_id OUT NOCOPY NUMBER,
599 x_mc_parameter_id OUT NOCOPY NUMBER,
600 x_service_request_action OUT NOCOPY VARCHAR,
601 x_contact_id OUT NOCOPY NUMBER,
602 x_parent_interaction_id OUT NOCOPY NUMBER,
603 x_tag_id OUT NOCOPY VARCHAR,
604 x_lead_id OUT NOCOPY NUMBER
605 ) IS
606
607 l_api_name VARCHAR2(255);
608 l_api_version_number NUMBER;
609
610 l_email_account_id NUMBER;
611 l_agent_account_id NUMBER;
612 l_rfc822_message_id VARCHAR2(300);
613 l_account_id NUMBER;
614 l_account_type VARCHAR2(3);
615 l_email_type VARCHAR2(3);
616 l_status VARCHAR2(3);
617 l_version NUMBER;
618 l_rt_media_item_id NUMBER;
619 l_rt_interaction_id NUMBER;
620 l_oes_id NUMBER;
621 l_customer_id NUMBER;
622 l_inpEmail_type VARCHAR2(2);
623 l_expire VARCHAR2(2);
624 InteractnNotFnd EXCEPTION;
625 ExQ EXCEPTION;
626
627 BEGIN
628
629 -- Standard Start of API savepoint
630 SAVEPOINT getSearchDetails_pvt;
631
632 -- Init vars
633 l_api_name :='getSearchDetails';
634 l_api_version_number :=1.0;
635 l_email_account_id := 0;
636 l_agent_account_id := 0;
637 l_account_id := 0;
638 l_version := 0;
639 l_rt_media_item_id := 0;
640 l_rt_interaction_id := 0;
641 l_oes_id := 0;
642 l_customer_id := 0;
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 BEGIN
664 -- Account id is always agent_account_id. Do not pass email_account_ids
665 SELECT rt_interaction_id, rt_media_item_id, rfc822_message_id,
666 folder_name, message_id, email_account_id, agent_account_id,
667 db_server_id, email_type, status, version, expire
668 INTO x_rt_interaction_id, x_rt_media_item_id, x_rfc822_message_id,
669 x_folder_name, x_mdt_message_id, l_email_account_id, l_agent_account_id,
670 x_oes_id, x_email_type, x_status, x_version, l_expire
671 FROM iem_rt_media_items
672 WHERE agent_account_id = p_agentAccount_id
673 AND message_id = p_message_id
674 AND email_type = p_email_type
675 AND expire IN (G_ACTIVE, G_DORMANT, G_QUEUEOUT);
676
677 x_folder_uid := -1;
678
679 SELECT customer_id, contact_id, interaction_id, parent_interaction_id,
680 service_request_id, service_request_action, inb_tag_id, lead_id
681 INTO x_customer_id, x_contact_id, x_interaction_id,
682 x_parent_interaction_id, x_service_request_id,
683 x_service_request_action, x_tag_id, x_lead_id
684 FROM iem_rt_interactions
685 WHERE rt_interaction_id = x_rt_interaction_id;
686
687 SELECT rt_classification_id into x_route_classification_id
688 FROM IEM_RT_PROC_EMAILS
689 WHERE message_id = x_mdt_message_id;
690
691 SELECT name into x_route_classification
692 FROM iem_route_classifications
693 WHERE ROUTE_CLASSIFICATION_ID = x_route_classification_id;
694
695 EXCEPTION
696 WHEN OTHERS THEN
697 -- Couldn't find the email rt records.
698 raise InteractnNotFnd;
699 END;
700
701 IF (l_expire = G_QUEUEOUT) THEN
702 raise ExQ;
703 END IF;
704
705
706 x_account_id := l_agent_account_id;
707 x_account_type := G_AGENT_ACCOUNT;
708
709 -------------------End Code------------------------
710 EXCEPTION
711 WHEN InteractnNotFnd THEN
712 ROLLBACK TO getSearchDetails_pvt;
713 -- Please keep this special status. Do not change it.
714 -- This is all thats required to inform JSPs that the email
715 -- does not exist.
716 x_return_status := 'N';
717 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
718 p_count => x_msg_count,
719 p_data => x_msg_data);
720
721 WHEN ExQ THEN
722 ROLLBACK TO getSearchDetails_pvt;
723 -- Please keep this special status. Do not change it.
724 -- This is all thats required to inform JSPs that the email
725 -- is in pre-transfer condition.
726 x_return_status := 'Q';
727 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
728 p_count => x_msg_count,
729 p_data => x_msg_data);
730
731 WHEN FND_API.G_EXC_ERROR THEN
732 ROLLBACK TO getSearchDetails_pvt;
733 x_return_status := FND_API.G_RET_STS_ERROR ;
734 FND_MSG_PUB.Count_And_Get(
735 p_encoded => FND_API.G_TRUE,
736 p_count => x_msg_count,
737 p_data => x_msg_data);
738
739 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
740 ROLLBACK TO getSearchDetails_pvt;
741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
742 FND_MSG_PUB.Count_And_Get(
743 p_encoded => FND_API.G_TRUE,
744 p_count => x_msg_count,
745 p_data => x_msg_data);
746
747 WHEN OTHERS THEN
748 ROLLBACK TO getSearchDetails_pvt;
749 x_return_status := FND_API.G_RET_STS_ERROR;
750 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
751 THEN
752 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
753 END IF;
754 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
755 p_count => x_msg_count,
756 p_data => x_msg_data);
757
758 END getSearchDetails;
759
760 /* Create RT, MDT and IH data for an email that arrives in the agents inbox
761 and is discovered during a search.
762 */
763 /*
764 PROCEDURE createUnprocMediaItm (p_api_version_number IN NUMBER,
765 p_init_msg_list IN VARCHAR2,
766 p_commit IN VARCHAR2,
767 p_agentAccount_id IN NUMBER,
768 p_folder_uid IN NUMBER,
769 p_folder_name IN VARCHAR2,
770 p_email_type IN VARCHAR2,
771 p_sender_name IN VARCHAR2,
772 p_priority IN VARCHAR2,
773 p_msg_status IN VARCHAR2,
774 p_subject IN VARCHAR2,
775 p_sent_date IN DATE,
776 p_rfc822_message_id IN VARCHAR2,
777 p_language IN VARCHAR2,
778 p_content_type IN VARCHAR2,
779 p_mailer IN VARCHAR2,
780 p_organization IN VARCHAR2,
781 p_message_type IN VARCHAR2,
782 p_received_date IN DATE,
783 p_message_size IN NUMBER,
784 x_return_status OUT NOCOPY VARCHAR2,
785 x_msg_count OUT NOCOPY NUMBER,
786 x_msg_data OUT NOCOPY VARCHAR2,
787 x_rfc822_message_id OUT NOCOPY VARCHAR2,
788 x_account_id OUT NOCOPY NUMBER,
789 x_account_type OUT NOCOPY VARCHAR2,
790 x_email_type OUT NOCOPY VARCHAR2,
791 x_status OUT NOCOPY VARCHAR2,
792 x_version OUT NOCOPY NUMBER,
793 x_rt_media_item_id OUT NOCOPY NUMBER,
794 x_rt_interaction_id OUT NOCOPY NUMBER,
795 x_oes_id OUT NOCOPY NUMBER,
796 x_folder_name OUT NOCOPY VARCHAR2,
797 x_folder_uid OUT NOCOPY NUMBER,
798 x_customer_id OUT NOCOPY NUMBER,
799 x_route_classification OUT NOCOPY VARCHAR2,
800 x_route_classification_id OUT NOCOPY NUMBER,
801 x_mdt_message_id OUT NOCOPY NUMBER
802 ) IS
803
804 l_api_name VARCHAR2(255);
805 l_api_version_number NUMBER;
806 l_created_by NUMBER;
807 l_last_updated_by NUMBER;
808 l_last_update_login NUMBER;
809
810 l_email_account_id NUMBER;
811 l_agent_account_id NUMBER;
812 l_rt_classification_id NUMBER;
813 l_post_mdts_id NUMBER;
814 l_rfc822_message_id VARCHAR2(300);
815 l_account_id NUMBER;
816 l_account_type VARCHAR2(3);
817 l_email_type VARCHAR2(3);
818 l_email_user VARCHAR2(100);
819 l_domain VARCHAR2(100);
820 l_status VARCHAR2(3);
821 l_msg_count NUMBER;
822 l_msg_data VARCHAR2(500);
823 l_version NUMBER;
824 l_rt_media_item_id NUMBER;
825 l_rt_interaction_id NUMBER;
826 l_oes_id NUMBER;
827 l_folder_name VARCHAR2(300);
828 l_folder_uid NUMBER;
829 l_customer_id NUMBER;
830 l_inpEmail_type VARCHAR2(2);
831 InteractnNotFnd EXCEPTION;
832 IHError EXCEPTION;
833
834 l_media_rec JTF_IH_PUB.MEDIA_REC_TYPE;
835 l_media_id NUMBER;
836 l_media_lc_rec JTF_IH_PUB.media_lc_rec_type;
837 l_resource_id NUMBER;
838 x_milcs_id NUMBER;
839 l_i_sequence NUMBER;
840 l_m_sequence NUMBER;
841 l_db_server_id NUMBER;
842 l_classification_id NUMBER;
843 l_rt_classification_name IEM_ROUTE_CLASSIFICATIONS.NAME%TYPE;
844 l_ih_subject VARCHAR2(80);
845
846 BEGIN
847
848 -- Standard Start of API savepoint
849 SAVEPOINT createUnprocMediaItm_pvt;
850
851 -- Init vars
852 l_api_name :='createUnprocMediaItm';
853 l_api_version_number :=1.0;
854 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
855 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
856 l_last_update_login :=NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
857
858 l_email_account_id := 0;
859 l_agent_account_id := 0;
860 l_rt_classification_id := 0;
861 l_post_mdts_id := 0;
862 l_account_id := 0;
863 l_version := 0;
864 l_rt_media_item_id := 0;
865 l_rt_interaction_id := 0;
866 l_oes_id := 0;
867 l_folder_uid := 0;
868 l_customer_id := 0;
869
870
871 -- Standard call to check for call compatibility.
872 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
873 1.0,
874 l_api_name,
875 G_PKG_NAME)
876 THEN
877 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
878 END IF;
879
880 -- Initialize message list if p_init_msg_list is set to TRUE.
881 IF FND_API.to_Boolean( p_init_msg_list )
882 THEN
883 FND_MSG_PUB.initialize;
884 END IF;
885
886 -- Initialize API return status to SUCCESS
887 x_return_status := FND_API.G_RET_STS_SUCCESS;
888
889 -----------------------Code------------------------
890
891 -- Standard Check Of p_commit.
892 IF FND_API.To_Boolean(p_commit) THEN
893 COMMIT WORK;
894 END IF;
895 -- Standard callto get message count and if count is 1, get message info.
896 FND_MSG_PUB.Count_And_Get
897 ( p_encoded => FND_API.G_TRUE,
898 p_count => x_msg_count,
899 p_data => x_msg_data
900 );
901
902 -------------------End Code------------------------
903 EXCEPTION
904 WHEN IHError THEN
905 ROLLBACK TO createUnprocMediaItm_pvt;
906 x_return_status := l_status;
907 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
908 p_count => x_msg_count,
909 p_data => x_msg_data);
910
911 WHEN FND_API.G_EXC_ERROR THEN
912 ROLLBACK TO createUnprocMediaItm_pvt;
913 x_return_status := FND_API.G_RET_STS_ERROR ;
914 FND_MSG_PUB.Count_And_Get(
915 p_encoded => FND_API.G_TRUE,
916 p_count => x_msg_count,
917 p_data => x_msg_data);
918
919 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
920 ROLLBACK TO createUnprocMediaItm_pvt;
921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
922 FND_MSG_PUB.Count_And_Get(
923 p_encoded => FND_API.G_TRUE,
924 p_count => x_msg_count,
925 p_data => x_msg_data);
926
927 WHEN OTHERS THEN
928 ROLLBACK TO createUnprocMediaItm_pvt;
929 x_return_status := FND_API.G_RET_STS_ERROR;
930 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
931 THEN
932 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
933 END IF;
934 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
935 p_count => x_msg_count,
936 p_data => x_msg_data);
937 END createUnprocMediaItm;
938 */
939
940 /* Can create an outbound email media item only
941 */
942 PROCEDURE createMediaDetails (p_api_version_number IN NUMBER,
943 p_init_msg_list IN VARCHAR2,
944 p_commit IN VARCHAR2,
945 p_resource_id IN NUMBER,
946 p_rfc822_message_id IN VARCHAR2,
947 p_folder_name IN VARCHAR2,
948 p_folder_uid IN NUMBER,
949 p_account_id IN NUMBER,
950 p_account_type IN VARCHAR2,
951 p_status IN VARCHAR2,
952 p_customer_id IN NUMBER,
953 p_rt_media_item_id IN NUMBER,
954 p_subject IN VARCHAR2,
955 p_interaction_id IN NUMBER,
956 p_service_request_id IN NUMBER,
957 p_mc_parameter_id IN NUMBER,
958 p_service_request_action IN VARCHAR,
959 p_contact_id IN NUMBER,
960 p_lead_id IN NUMBER,
961 p_parent_ih_id IN NUMBER,
962 p_action_id IN NUMBER,
963 p_relationship_id IN NUMBER,
964 x_return_status OUT NOCOPY VARCHAR2,
965 x_msg_count OUT NOCOPY NUMBER,
966 x_msg_data OUT NOCOPY VARCHAR2,
967 x_version OUT NOCOPY NUMBER,
968 x_rt_media_item_id OUT NOCOPY NUMBER,
969 x_rt_interaction_id OUT NOCOPY NUMBER
970 ) IS
971
972 l_api_name VARCHAR2(255);
973 l_api_version_number NUMBER;
974 l_created_by NUMBER;
975 l_last_updated_by NUMBER;
976 l_last_update_login NUMBER;
977
978 l_i_sequence NUMBER;
979 l_m_sequence NUMBER;
980 l_db_server_id NUMBER;
981 l_email_account_id NUMBER;
982 l_email_account_id_ih NUMBER;
983 l_agent_account_id NUMBER;
984 l_folder_name VARCHAR2(300);
985 l_rt_interaction_id NUMBER;
986 l_email_type VARCHAR2(2);
987
988 l_status VARCHAR2(255);
989 l_msg_count NUMBER;
990 l_msg_data VARCHAR2(300);
991 l_media_rec JTF_IH_PUB.MEDIA_REC_TYPE;
992 l_media_id NUMBER;
993 badAccountType EXCEPTION;
994 badAccount EXCEPTION;
995 l_ob_media_id NUMBER;
996 l_ib_media_id NUMBER;
997 l_ih_subject VARCHAR2(80);
998
999 BEGIN
1000
1001 -- Standard Start of API savepoint
1002 SAVEPOINT createMediaDetails_pvt;
1003 -- Init vars
1004 l_api_name :='createMediaDetails';
1005 l_api_version_number :=1.0;
1006 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1007 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1008 l_last_update_login :=NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
1009 l_email_account_id := 0;
1010 l_email_account_id_ih := 0;
1011 l_agent_account_id := 0;
1012 l_rt_interaction_id := 0;
1013 l_email_type := '';
1014 l_ib_media_id := -1;
1015
1016 -- Standard call to check for call compatibility.
1017 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1018 1.0,
1019 l_api_name,
1020 G_PKG_NAME)
1021 THEN
1022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1023 END IF;
1024
1025 -- Initialize message list if p_init_msg_list is set to TRUE.
1026 IF FND_API.to_Boolean( p_init_msg_list )
1027 THEN
1028 FND_MSG_PUB.initialize;
1029 END IF;
1030
1031 -- Initialize API return status to SUCCESS
1032 x_return_status := FND_API.G_RET_STS_SUCCESS;
1033
1034 -----------------------Code------------------------
1035 l_db_server_id := -1;
1036 -- Detemine the type of email account, master or agent
1037 if (UPPER(p_account_type) = G_MASTER_ACCOUNT) then
1038 l_email_account_id := p_account_id;
1039 l_email_account_id_ih := p_account_id;
1040 l_agent_account_id := null;
1041
1042 elsif (UPPER(p_account_type) = G_AGENT_ACCOUNT) then
1043
1044 l_agent_account_id := p_account_id;
1045 l_email_account_id := null;
1046
1047 begin
1048 SELECT A.EMAIL_ACCOUNT_ID
1049 INTO l_email_account_id_ih
1050 FROM IEM_AGENTS A
1051 WHERE A.agent_id = p_account_id;
1052
1053 exception
1054 when others then
1055 raise badAccount;
1056 end;
1057
1058 else
1059 raise badAccountType;
1060 end if;
1061
1062
1063 -- Create an outbound media_item; errors, if any, from IH are
1064 -- ignored as a second attempt will be made to create an
1065 -- outbound media id in wrapUp
1066 l_media_rec.direction := G_O_DIRECTION;
1067 l_media_rec.source_id := l_email_account_id_ih;
1068 l_media_rec.start_date_time := SYSDATE;
1069 l_media_rec.media_item_type := G_MEDIA_TYPE;
1070 l_media_rec.media_item_ref := p_rfc822_message_id; -- what should we fill in here for 11.5.11?
1071
1072 -- Truncate to 80 characters
1073 IF lengthb(p_subject)>80 then
1074 l_ih_subject:=substrb(p_subject,1,80);
1075 ELSE
1076 l_ih_subject:=p_subject;
1077 END IF;
1078 l_media_rec.media_data := l_ih_subject;
1079
1080 JTF_IH_PUB.Open_MediaItem(p_api_version => 1.0,
1081 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
1082 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
1083 p_user_id => l_created_by,
1084 p_login_id => l_last_update_login,
1085 x_return_status => l_status,
1086 x_msg_count => l_msg_count,
1087 x_msg_data => l_msg_data,
1088 p_media_rec => l_media_rec,
1089 x_media_id => l_media_id
1090 );
1091 IF ( l_status = FND_API.G_RET_STS_SUCCESS ) THEN
1092 l_ob_media_id := l_media_id;
1093 ELSE
1094 l_ob_media_id := null;
1095 END IF;
1096
1097 IF (p_rt_media_item_id = G_NUM_NOP2 OR p_rt_media_item_id = fnd_api.g_miss_num
1098 OR p_rt_media_item_id is null) THEN
1099
1100 -- Pure outbound message as there is no associated rt_media_item_id.
1101 -- Insert into rt tables.
1102 SELECT IEM_RT_INTERACTIONS_S1.nextval INTO l_i_sequence FROM DUAL;
1103 INSERT INTO iem_rt_interactions (
1104 rt_interaction_id, resource_id, customer_id, type,
1105 status, expire, created_by, creation_date, last_updated_by,
1106 last_update_date, last_update_login, interaction_id,
1107 service_request_id, mc_parameter_id, service_request_action,
1108 contact_id, lead_id, parent_interaction_id,
1109 action_id, relationship_id)
1110 VALUES (
1111 l_i_sequence, p_resource_id,
1112 decode(p_customer_id,-1, null, p_customer_id),
1113 G_OUTBOUND, p_status, G_ACTIVE, l_created_by,
1114 SYSDATE,l_last_updated_by, SYSDATE, l_last_update_login,
1115 decode(p_interaction_id, G_NUM_NOP, null, p_interaction_id),
1116 decode(p_service_request_id, G_NUM_NOP, null, p_service_request_id),
1117 decode(p_mc_parameter_id, G_NUM_NOP, null, p_mc_parameter_id),
1118 decode(p_service_request_action, G_CHAR_NOP, null, p_service_request_action),
1119 decode(p_contact_id, G_NUM_NOP, null, p_contact_id),
1120 decode(p_lead_id, G_NUM_NOP, null, p_lead_id),
1121 decode(p_parent_ih_id, G_NUM_NOP, null, p_parent_ih_id),
1122 decode(p_action_id, G_NUM_NOP, null, p_action_id),
1123 decode(p_relationship_id, G_NUM_NOP, null, p_relationship_id)
1124 );
1125
1126 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
1127 INSERT INTO iem_rt_media_items (
1128 rt_interaction_id, rt_media_item_id, resource_id,
1129 media_id, message_id, rfc822_message_id, folder_name,
1130 folder_uid, email_account_id,agent_account_id,
1131 db_server_id, email_type, status, expire,
1132 version, created_by, creation_date, last_updated_by,
1133 last_update_date, last_update_login
1134 )
1135 VALUES (
1136 l_i_sequence, l_m_sequence, p_resource_id, l_ob_media_id, null,
1137 p_rfc822_message_id, p_folder_name,p_folder_uid,
1138 l_email_account_id, l_agent_account_id, l_db_server_id,
1139 G_OUTBOUND, p_status, G_ACTIVE, '0', l_created_by, SYSDATE,
1140 l_last_updated_by, SYSDATE, l_last_update_login
1141 );
1142
1143 x_version := 0;
1144 x_rt_media_item_id := l_m_sequence;
1145 x_rt_interaction_id := l_i_sequence;
1146
1147 ELSE
1148
1149 -- reply to an inbound?, checking..
1150 SELECT rt_interaction_id, email_type, media_id
1151 INTO l_rt_interaction_id, l_email_type, l_ib_media_id
1152 FROM iem_rt_media_items
1153 WHERE rt_media_item_id = p_rt_media_item_id
1154 AND expire = G_ACTIVE
1155 FOR UPDATE NOWAIT;
1156
1157 IF (UPPER(l_email_type) = G_INBOUND) THEN
1158
1159 -- Draft/Reply to an existing Inbound.
1160 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
1161 INSERT INTO iem_rt_media_items (
1162 rt_interaction_id, rt_media_item_id, resource_id,
1163 media_id, message_id, rfc822_message_id, folder_name,
1164 folder_uid, email_account_id,agent_account_id,
1165 db_server_id, email_type, status, expire,
1166 version, created_by, creation_date, last_updated_by,
1167 last_update_date, last_update_login
1168 )
1169 VALUES (
1170 l_rt_interaction_id, l_m_sequence, p_resource_id, l_ob_media_id,
1171 null, p_rfc822_message_id, p_folder_name,p_folder_uid,
1172 l_email_account_id, l_agent_account_id, l_db_server_id,
1173 G_OUTBOUND, p_status, G_ACTIVE, '0', l_created_by, SYSDATE,
1174 l_last_updated_by, SYSDATE, l_last_update_login
1175 );
1176
1177 x_version := 0;
1178 x_rt_media_item_id := l_m_sequence;
1179 x_rt_interaction_id := l_rt_interaction_id;
1180 ELSE
1181 -- Outbound Media Item. Cannot create a reply.
1182 x_return_status := FND_API.G_RET_STS_ERROR ;
1183 l_ib_media_id := -1;
1184 END IF;
1185 END IF;
1186
1187 -- Write statistics data
1188 if ( l_ob_media_id is not null AND x_return_status = FND_API.G_RET_STS_SUCCESS) then
1189 IEM_MSG_STAT_PUB.createMSGStat(
1190 p_api_version_number => 1.0,
1191 p_init_msg_list => fnd_api.g_false,
1192 p_commit => fnd_api.g_false,
1193 p_outBoundMediaID => l_ob_media_id,
1194 p_inBoundMediaID => l_ib_media_id,
1195 x_return_status => l_status,
1196 x_msg_count => l_msg_count,
1197 x_msg_data => l_msg_data
1198 );
1199 end if;
1200 -------------------End Code------------------------
1201 EXCEPTION
1202 WHEN badAccount THEN
1203 ROLLBACK TO createMediaDetails_pvt;
1204 x_return_status := FND_API.G_RET_STS_ERROR ;
1205 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_ACCOUNT');
1206 FND_MSG_PUB.ADD;
1207 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
1208 p_count => x_msg_count,
1209 p_data => x_msg_data);
1210
1211 WHEN badAccountType THEN
1212 ROLLBACK TO createMediaDetails_pvt;
1213 x_return_status := FND_API.G_RET_STS_ERROR ;
1214 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_ACCOUNT_TYPE');
1215 FND_MSG_PUB.ADD;
1216 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
1217 p_count => x_msg_count,
1218 p_data => x_msg_data);
1219
1220 WHEN FND_API.G_EXC_ERROR THEN
1221 ROLLBACK TO createMediaDetails_pvt;
1222 x_return_status := FND_API.G_RET_STS_ERROR ;
1223 FND_MSG_PUB.Count_And_Get(
1224 p_encoded => FND_API.G_TRUE,
1225 p_count => x_msg_count,
1226 p_data => x_msg_data);
1227
1228 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1229 ROLLBACK TO createMediaDetails_pvt;
1230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1231 FND_MSG_PUB.Count_And_Get(
1232 p_encoded => FND_API.G_TRUE,
1233 p_count => x_msg_count,
1234 p_data => x_msg_data);
1235
1236 WHEN OTHERS THEN
1237 ROLLBACK TO createMediaDetails_pvt;
1238 x_return_status := FND_API.G_RET_STS_ERROR;
1239 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1240 THEN
1241 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1242 END IF;
1243 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
1244 p_count => x_msg_count,
1245 p_data => x_msg_data);
1246 END createMediaDetails;
1247
1248
1249 PROCEDURE updateMediaDetails (p_api_version_number IN NUMBER,
1250 p_init_msg_list IN VARCHAR2,
1251 p_commit IN VARCHAR2,
1252 p_rfc822_message_id IN VARCHAR2,
1253 p_folder_name IN VARCHAR2,
1254 p_folder_uid IN NUMBER,
1255 p_account_id IN NUMBER,
1256 p_account_type IN VARCHAR2,
1257 p_status IN VARCHAR2,
1258 p_customer_id IN NUMBER,
1259 p_rt_media_item_id IN NUMBER,
1260 p_version IN NUMBER,
1261 p_interaction_id IN NUMBER,
1262 p_service_request_id IN NUMBER,
1263 p_mc_parameter_id IN NUMBER,
1264 p_service_request_action IN VARCHAR2,
1265 p_contact_id IN NUMBER,
1266 p_parent_interaction_id IN NUMBER,
1267 p_tag_id IN VARCHAR2,
1268 p_edit_mode IN VARCHAR2,
1269 p_lead_id IN NUMBER,
1270 p_relationship_id IN NUMBER,
1271 x_return_status OUT NOCOPY VARCHAR2,
1272 x_msg_count OUT NOCOPY NUMBER,
1273 x_msg_data OUT NOCOPY VARCHAR2,
1274 x_version OUT NOCOPY NUMBER
1275 ) IS
1276
1277 l_api_name VARCHAR2(255);
1278 l_api_version_number NUMBER;
1279 l_created_by NUMBER;
1280 l_last_updated_by NUMBER;
1281 l_last_update_login NUMBER;
1282
1283 l_status VARCHAR2(300);
1284 l_msg_count NUMBER;
1285 l_msg_data VARCHAR2(300);
1286
1287 l_email_account_id NUMBER;
1288 l_agent_account_id NUMBER;
1289
1290 l_email_account_id_old NUMBER;
1291 l_agent_account_id_old NUMBER;
1292
1293 l_version NUMBER;
1294 l_email_type VARCHAR2(300);
1295 l_rt_interaction_id NUMBER;
1296
1297 l_media_lc_rec JTF_IH_PUB.media_lc_rec_type;
1298 l_media_id NUMBER;
1299 l_resource_id NUMBER;
1300 x_milcs_id NUMBER;
1301 badAccountType EXCEPTION;
1302 illegalMesgMove EXCEPTION;
1303 l_session_id NUMBER;
1304 l_activity_id NUMBER;
1305 l_count NUMBER;
1306 l_data VARCHAR2(300);
1307 l_ret_status VARCHAR2(300);
1308 l_rt_status VARCHAR2(10);
1309 l_msg_id NUMBER;
1310 l_edit_mode VARCHAR2(1);
1311 l_uwq_act_code VARCHAR2(32);
1312 l_ib_media_id number;
1313 l_ob_media_id number;
1314 l_rt_media_item_id_ib number;
1315 l_type VARCHAR2(2);
1316
1317 CURSOR sel_csr IS
1318 SELECT session_id from IEU_SH_SESSIONS
1319 WHERE BEGIN_DATE_TIME = (SELECT MAX(BEGIN_DATE_TIME)
1320 FROM IEU_SH_SESSIONS
1321 WHERE RESOURCE_ID = l_resource_id
1322 AND ACTIVE_FLAG = 'T'
1323 AND APPLICATION_ID = 680);
1324 BEGIN
1325
1326 -- Standard Start of API savepoint
1327 SAVEPOINT updateMediaDetails_pvt;
1328
1329 -- Init vars
1330 l_api_name :='updateMediaDetails';
1331 l_api_version_number :=1.0;
1332 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1333 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1334 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
1335 l_email_account_id_old := 0;
1336 l_agent_account_id_old := 0;
1337 l_uwq_act_code := null;
1338 l_ib_media_id := -1;
1339 l_ob_media_id := null;
1340
1341 -- Standard call to check for call compatibility.
1342 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1343 1.0,
1344 l_api_name,
1345 G_PKG_NAME)
1346 THEN
1347 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1348 END IF;
1349
1350 -- Initialize message list if p_init_msg_list is set to TRUE.
1351 IF FND_API.to_Boolean( p_init_msg_list )
1352 THEN
1353 FND_MSG_PUB.initialize;
1354 END IF;
1355
1356 -- Initialize API return status to SUCCESS
1357 x_return_status := FND_API.G_RET_STS_SUCCESS;
1358
1359 -----------------------Code------------------------
1360 /* dbms_output.put_line('In getWork ');
1361 dbms_output.put_line('In getWork : Resource ID '||p_resource_id);
1362 */
1363
1364 SELECT rt_interaction_id, version, email_type, email_account_id,
1365 agent_account_id, media_id, resource_id, status, message_id
1366 INTO l_rt_interaction_id, l_version, l_email_type, l_email_account_id_old,
1367 l_agent_account_id_old, l_media_id, l_resource_id, l_rt_status, l_msg_id
1368 FROM iem_rt_media_items
1369 WHERE rt_media_item_id = p_rt_media_item_id
1370 AND expire in (G_ACTIVE, G_DORMANT, G_QUEUEOUT)
1371 FOR update nowait;
1372
1373
1374 IF ((l_version = p_version) OR (UPPER(l_email_type) = G_INBOUND) ) THEN
1375 if (p_account_type = G_CHAR_NOP) then
1376 l_email_account_id := l_email_account_id_old;
1377 l_agent_account_id := l_agent_account_id_old;
1378 elsif (p_account_type = G_AGENT_ACCOUNT) then
1379 l_email_account_id := null;
1380 l_agent_account_id := p_account_id;
1381 elsif (p_account_type = G_MASTER_ACCOUNT) then
1382 l_agent_account_id := null;
1383 l_email_account_id := p_account_id;
1384 else
1385 raise badAccountType;
1386 end if;
1387
1388
1389 -- Update IEM_RT_PROC_EMAILS mail_item_status to 'S' for 'Saved' inbound
1390 if (p_status = 'V') then
1391
1392 if ( l_email_type = G_OUTBOUND ) then
1393 SELECT message_id
1394 INTO l_msg_id
1395 FROM iem_rt_media_items
1396 WHERE rt_interaction_id = l_rt_interaction_id
1397 AND email_type = G_INBOUND;
1398 end if;
1399
1400 UPDATE IEM_RT_PROC_EMAILS SET mail_item_status = 'S' where message_id = l_msg_id;
1401 end if;
1402
1403 -- Add email_open mlcs if it is an 'assign' or 'transfer' or 'autoroute'
1404 if (((l_rt_status = 'R') OR (l_rt_status = 'G') OR (l_rt_status = 'O')) AND
1405 (p_status is not null) AND
1406 (p_status <> 'R') AND
1407 (UPPER(l_email_type) <> G_OUTBOUND)) then
1408 -- Add MLCS.
1409 l_media_lc_rec.start_date_time := SYSDATE;
1410 l_media_lc_rec.end_date_time := SYSDATE;
1411 l_media_lc_rec.media_id := l_media_id;
1412 l_media_lc_rec.milcs_type_id := 24; -- EMAIL_OPEN
1413 l_media_lc_rec.resource_id := l_resource_id;
1414 l_media_lc_rec.handler_id := 680;
1415 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
1416 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
1417 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
1418 p_user_id => l_created_by,
1419 p_login_id => l_last_update_login,
1420 x_return_status => l_status,
1421 x_msg_count => l_msg_count,
1422 x_msg_data => l_msg_data,
1423 x_milcs_id => x_milcs_id,
1424 p_media_lc_rec => l_media_lc_rec);
1425
1426 -- Update IEM_RT_PROC_EMAILS mail_item_status to 'R' for 'Read'
1427 UPDATE IEM_RT_PROC_EMAILS SET mail_item_status = 'R' where message_id = l_msg_id;
1428
1429 l_uwq_act_code := 'EMAIL_OPENED';
1430
1431 else
1432 begin
1433
1434 -- Add MLCS after move from master account to agent account
1435 -- Not add email_fetch mlcs if it is an 'assign' or 'transfer' or 'autoroute'
1436 -- Not add email_Transfer from agent1 - agent2 not allowed in this API.
1437 -- Check if new master account == null and new agent account is valid
1438 -- Err if move from master to master
1439 -- NOP if move from agent to master.
1440
1441 if ((l_email_account_id_old IS NOT NULL ) AND
1442 (l_email_account_id IS NULL) AND
1443 (p_status <> 'G') AND
1444 (UPPER(l_email_type) <> G_OUTBOUND)) then
1445 -- Add MLCS.
1446 l_media_lc_rec.start_date_time := SYSDATE;
1447 l_media_lc_rec.end_date_time := SYSDATE;
1448 l_media_lc_rec.media_id := l_media_id;
1449 l_media_lc_rec.milcs_type_id := 18; -- EMAIL_FETCH
1450 l_media_lc_rec.resource_id := l_resource_id;
1451 l_media_lc_rec.handler_id := 680;
1452 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
1453 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
1454 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
1455 p_user_id => l_created_by,
1456 p_login_id => l_last_update_login,
1457 x_return_status => l_status,
1458 x_msg_count => l_msg_count,
1459 x_msg_data => l_msg_data,
1460 x_milcs_id => x_milcs_id,
1461 p_media_lc_rec => l_media_lc_rec);
1462
1463 -- Update IEM_RT_PROC_EMAILS mail_item_status to 'R' for 'Read'
1464 UPDATE IEM_RT_PROC_EMAILS SET mail_item_status = 'R' where message_id = l_msg_id;
1465
1466 l_uwq_act_code := 'EMAIL_FETCHED';
1467
1468 end if;
1469 end; -- email_fetch
1470 end if;
1471
1472
1473 -- Update version.
1474 if (UPPER(l_email_type) = G_INBOUND) then
1475 l_version := 0;
1476 else
1477 l_version := p_version + 1;
1478 end if;
1479
1480 -- BEGIN
1481 if ( p_edit_mode is null OR p_edit_mode = fnd_api.g_miss_char ) then
1482 l_edit_mode := G_CHAR_NOP;
1483 else
1484 l_edit_mode := p_edit_mode;
1485 end if;
1486
1487 UPDATE iem_rt_media_items SET
1488 RFC822_MESSAGE_ID = decode(p_rfc822_message_id, G_CHAR_NOP, RFC822_MESSAGE_ID, p_rfc822_message_id),
1489 FOLDER_NAME = decode(p_folder_name, G_CHAR_NOP, FOLDER_NAME, p_folder_name),
1490 FOLDER_UID = decode(p_folder_uid, G_NUM_NOP, FOLDER_UID, p_folder_uid),
1491 EMAIL_ACCOUNT_ID = decode(l_email_account_id, G_NUM_NOP, EMAIL_ACCOUNT_ID, l_email_account_id),
1492 AGENT_ACCOUNT_ID = decode(l_agent_account_id, G_NUM_NOP, AGENT_ACCOUNT_ID, l_agent_account_id),
1493 STATUS = decode(p_status, G_CHAR_NOP, STATUS, p_status),
1494 VERSION = l_version,
1495 LAST_UPDATED_BY = l_last_updated_by,
1496 LAST_UPDATE_DATE = SYSDATE,
1497 LAST_UPDATE_LOGIN = l_last_update_login,
1498 EDIT_MODE = decode(l_edit_mode, G_CHAR_NOP, EDIT_MODE, l_edit_mode)
1499 WHERE rt_media_item_id = p_rt_media_item_id;
1500 -- EXCEPTION
1501 -- when others then
1502 -- dbms_output.put_line('In UPDATE ' || SQLERRM);
1503 -- END;
1504
1505 -- BEGIN
1506 UPDATE iem_rt_interactions SET
1507 CUSTOMER_ID = decode(p_customer_id, G_NUM_NOP, CUSTOMER_ID, p_customer_id),
1508 CONTACT_ID = decode(p_contact_id, G_NUM_NOP, contact_id, p_contact_id),
1509 RELATIONSHIP_ID = decode(p_relationship_id, G_NUM_NOP, relationship_id, p_relationship_id),
1510 INTERACTION_ID = decode(p_interaction_id, G_NUM_NOP, interaction_id, p_interaction_id),
1511 SERVICE_REQUEST_ID = decode(p_service_request_id, G_NUM_NOP, service_request_id, p_service_request_id),
1512 MC_PARAMETER_ID = decode(p_mc_parameter_id, G_NUM_NOP, mc_parameter_id, p_mc_parameter_id),
1513 SERVICE_REQUEST_ACTION = decode(p_service_request_action, G_CHAR_NOP, service_request_action, p_service_request_action),
1514 PARENT_INTERACTION_ID = decode(p_parent_interaction_id, G_NUM_NOP, parent_interaction_id, p_parent_interaction_id),
1515 INB_TAG_ID = decode(p_tag_id, G_CHAR_NOP, inb_tag_id, p_tag_id),
1516 LAST_UPDATED_BY = l_last_updated_by,
1517 LAST_UPDATE_DATE = SYSDATE,
1518 LAST_UPDATE_LOGIN = l_last_update_login,
1519 LEAD_ID = decode(p_lead_id, G_NUM_NOP, lead_id, p_lead_id)
1520 WHERE rt_interaction_id = l_rt_interaction_id;
1521 -- EXCEPTION
1522 -- when others then
1523 -- dbms_output.put_line('In UPDATE ' || SQLERRM);
1524 -- END;
1525
1526 x_version := l_version;
1527
1528 -- Record UWQ interaction.
1529 if ( l_uwq_act_code is not null ) then
1530
1531 BEGIN
1532 FOR sel_rec in sel_csr LOOP
1533 l_session_id := sel_rec.session_id;
1534 exit;
1535 END LOOP;
1536
1537 IEU_SH_PUB.UWQ_BEGIN_ACTIVITY(
1538 p_api_version => 1.0,
1539 P_INIT_MSG_LIST => 'F',
1540 P_COMMIT => 'F',
1541 p_session_id => l_session_id,
1542 p_activity_type_code => l_uwq_act_code,
1543 P_MEDIA_TYPE_ID => null,
1544 P_MEDIA_ID => null,
1545 p_user_id => l_created_by,
1546 p_login_id => l_last_update_login,
1547 P_REASON_CODE => null,
1548 P_REQUEST_METHOD => null,
1549 P_REQUESTED_MEDIA_TYPE_ID => null,
1550 P_WORK_ITEM_TYPE_CODE => null,
1551 P_WORK_ITEM_PK_ID => null,
1552 p_end_activity_flag => 'Y',
1553 x_activity_id => l_activity_id,
1554 x_msg_count => l_count,
1555 x_msg_data => l_data,
1556 x_return_status => l_ret_status
1557 );
1558
1559 EXCEPTION
1560 WHEN OTHERS THEN
1561 NULL;
1562 END;
1563 end if; -- check l_uwq_act_code
1564 ELSE
1565 -- Version mismatch. Cannot update.
1566 x_return_status := 'M';
1567 END IF;
1568
1569 -- write statistics data
1570 begin
1571 select media_id into l_ob_media_id
1572 from iem_rt_media_items
1573 where rt_interaction_id = l_rt_interaction_id and email_type = G_OUTBOUND;
1574 exception
1575 when others then
1576 null;
1577 end;
1578 if (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_ob_media_id is not null) then
1579
1580 if ( p_status = 'V' OR p_status = 'C') then
1581 l_rt_media_item_id_ib := null;
1582 begin
1583 select rt_media_item_id, media_id into l_rt_media_item_id_ib, l_ib_media_id
1584 from iem_rt_media_items
1585 where rt_interaction_id = l_rt_interaction_id and email_type = G_INBOUND;
1586 exception
1587 when others then
1588 null;
1589 end;
1590 end if;
1591 if ( p_status = 'V') then
1592 IEM_MSG_STAT_PUB.saveMSGStat(
1593 p_api_version_number => 1.0,
1594 p_init_msg_list => fnd_api.g_false,
1595 p_commit => fnd_api.g_false,
1596 p_outBoundMediaID => l_ob_media_id,
1597 p_inBoundMediaID => nvl(l_ib_media_id, -1),
1598 x_return_status => l_ret_status,
1599 x_msg_count => l_count,
1600 x_msg_data => l_data
1601 );
1602 end if;
1603 if ( p_status = 'C') then
1604 if ( l_rt_media_item_id_ib is null ) then -- pure outbound
1605 IEM_MSG_STAT_PUB.deleteMSGStat(
1606 p_api_version_number => 1.0,
1607 p_init_msg_list => fnd_api.g_false,
1608 p_commit => fnd_api.g_false,
1609 p_outBoundMediaID => l_ob_media_id,
1610 p_inBoundMediaID => -1,
1611 x_return_status => l_ret_status,
1612 x_msg_count => l_count,
1613 x_msg_data => l_data
1614 );
1615 else
1616 IEM_MSG_STAT_PUB.cancelMSGStat(
1617 p_api_version_number => 1.0,
1618 p_init_msg_list => fnd_api.g_false,
1619 p_commit => fnd_api.g_false,
1620 p_outBoundMediaID => l_ob_media_id,
1621 p_inBoundMediaID => nvl(l_ib_media_id, -1),
1622 x_return_status => l_ret_status,
1623 x_msg_count => l_count,
1624 x_msg_data => l_data
1625 );
1626
1627 end if;
1628 end if; -- 'Cancel'
1629 end if;
1630
1631 -- Expire iem_rt_media_items and iem_rt_interactions if status is 'Cancel'
1632 -- for pure outbound items.
1633 if (p_status = 'C') then
1634
1635 SELECT type INTO l_type FROM iem_rt_interactions
1636 WHERE rt_interaction_id = l_rt_interaction_id;
1637
1638 if ( l_type = G_OUTBOUND ) then
1639
1640 UPDATE iem_rt_media_items SET expire = G_EXPIRE
1641 WHERE rt_media_item_id = p_rt_media_item_id;
1642
1643 UPDATE iem_rt_interactions SET expire = G_EXPIRE
1644 WHERE rt_interaction_id = l_rt_interaction_id;
1645
1646 end if;
1647 end if;
1648
1649 -------------------End Code------------------------
1650
1651 EXCEPTION
1652 WHEN badAccountType THEN
1653 ROLLBACK TO updateMediaDetails_pvt;
1654 x_return_status := FND_API.G_RET_STS_ERROR ;
1655 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_ACCOUNT_TYPE');
1656 FND_MSG_PUB.ADD;
1657 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
1658 p_count => x_msg_count,
1659 p_data => x_msg_data);
1660 WHEN FND_API.G_EXC_ERROR THEN
1661 ROLLBACK TO updateMediaDetails_pvt;
1662 x_return_status := FND_API.G_RET_STS_ERROR ;
1663 FND_MSG_PUB.Count_And_Get(
1664 p_encoded => FND_API.G_TRUE,
1665 p_count => x_msg_count,
1666 p_data => x_msg_data);
1667
1668 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1669 ROLLBACK TO updateMediaDetails_pvt;
1670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1671 FND_MSG_PUB.Count_And_Get(
1672 p_encoded => FND_API.G_TRUE,
1673 p_count => x_msg_count,
1674 p_data => x_msg_data);
1675
1676 WHEN OTHERS THEN
1677 ROLLBACK TO updateMediaDetails_pvt;
1678 x_return_status := FND_API.G_RET_STS_ERROR;
1679 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1680 THEN
1681 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1682 END IF;
1683 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
1684 p_count => x_msg_count,
1685 p_data => x_msg_data);
1686 END updateMediaDetails;
1687
1688 PROCEDURE getIHID (p_api_version_number IN NUMBER,
1689 p_init_msg_list IN VARCHAR2,
1690 p_commit IN VARCHAR2,
1691 p_action IN VARCHAR2,
1692 p_action_id IN NUMBER,
1693 p_rt_media_item_id IN NUMBER,
1694 p_version IN NUMBER,
1695 p_customer_id IN NUMBER,
1696 p_activity_type_id IN NUMBER,
1697 p_outcome_id IN NUMBER,
1698 p_result_id IN NUMBER,
1699 p_reason_id IN NUMBER,
1700 p_resource_id IN NUMBER,
1701 x_return_status OUT NOCOPY VARCHAR2,
1702 x_msg_count OUT NOCOPY NUMBER,
1703 x_msg_data OUT NOCOPY VARCHAR2,
1704 x_interaction_id OUT NOCOPY NUMBER,
1705 x_sr_id OUT NOCOPY NUMBER,
1706 x_lead_id OUT NOCOPY NUMBER
1707 ) IS
1708
1709 l_api_name VARCHAR2(255);
1710 l_api_version_number NUMBER;
1711 l_created_by NUMBER;
1712 l_last_updated_by NUMBER;
1713 l_last_update_login NUMBER;
1714
1715 l_status VARCHAR2(300);
1716 l_msg_count NUMBER;
1717 l_msg_data VARCHAR2(300);
1718
1719 l_result_id NUMBER;
1720 l_reason_id NUMBER;
1721
1722 IHError EXCEPTION;
1723
1724 l_rt_interaction_id NUMBER;
1725 l_interaction_id NUMBER;
1726 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
1727 l_customer_id NUMBER;
1728 l_contact_id NUMBER;
1729 l_resource_id NUMBER;
1730 l_start_date DATE;
1731 l_creation_date DATE;
1732 l_session_id NUMBER;
1733 l_count NUMBER;
1734 l_data VARCHAR2(300);
1735 l_ret_status VARCHAR2(300);
1736 l_parent_ih_id NUMBER;
1737 l_relationship_id NUMBER;
1738 l_party_type VARCHAR2(32);
1739 l_ih_customer_id NUMBER;
1740 l_primary_customer_id NUMBER;
1741
1742 BEGIN
1743
1744 -- Standard Start of API savepoint
1745 SAVEPOINT getIHID_pvt;
1746
1747 -- Init vars
1748 l_api_name :='getIHID';
1749 l_api_version_number :=1.0;
1750 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1751 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
1752 l_last_update_login :=NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
1753
1754 -- Standard call to check for call compatibility.
1755 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1756 1.0,
1757 l_api_name,
1758 G_PKG_NAME)
1759 THEN
1760 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1761 END IF;
1762
1763 -- Initialize message list if p_init_msg_list is set to TRUE.
1764 IF FND_API.to_Boolean( p_init_msg_list )
1765 THEN
1766 FND_MSG_PUB.initialize;
1767 END IF;
1768
1769 -- Initialize API return status to SUCCESS
1770 x_return_status := FND_API.G_RET_STS_SUCCESS;
1771
1772 -----------------------Code------------------------
1773 -- Check if we already have an Interaction opened, if so return it
1774 SELECT a.rt_interaction_id, decode(a.interaction_id, -1, NULL, a.interaction_id),
1775 a.resource_id, a.creation_date,
1776 decode(a.parent_interaction_id, NULL, fnd_api.g_miss_num, a.parent_interaction_id),
1777 a.service_request_id, a.lead_id, a.contact_id, a.relationship_id
1778 INTO l_rt_interaction_id, l_interaction_id, l_resource_id, l_start_date,
1779 l_parent_ih_id, x_sr_id, x_lead_id, l_contact_id, l_relationship_id
1780 FROM iem_rt_interactions a, iem_rt_media_items b
1781 WHERE b.rt_media_item_id = p_rt_media_item_id
1782 AND a.rt_interaction_id = b.rt_interaction_id
1783 AND a.expire <> G_EXPIRE;
1784
1785 l_ih_customer_id := p_customer_id;
1786
1787 select PARTY_TYPE into l_party_type from HZ_PARTIES
1788 where party_id = p_customer_id;
1789
1790 if ( l_party_type = 'PERSON' ) then
1791 l_primary_customer_id := l_ih_customer_id;
1792 l_contact_id := l_ih_customer_id;
1793 l_relationship_id := null;
1794
1795 elsif ( l_party_type = 'ORGANIZATION') then
1796 l_primary_customer_id := l_ih_customer_id;
1797
1798 if ( l_contact_id > 0 ) then
1799 if ( (l_relationship_id < 0) OR (l_relationship_id is null)) then
1800 -- donot make contact id as null ranjan 11/21/08
1801 -- l_contact_id := null;
1802 l_relationship_id := null;
1803 end if;
1804 else
1805 l_contact_id := null;
1806 l_relationship_id := null;
1807 end if;
1808
1809 else -- use old method PARTY_RELATIONSHIP
1810 l_primary_customer_id := null;
1811 l_contact_id := null;
1812 l_relationship_id := null;
1813 end if;
1814 -- end of ih customer info
1815
1816
1817 -- Open IH
1818 IF (l_interaction_id IS NULL) THEN
1819
1820 select decode(p_result_id,-1, NULL, p_result_id) into l_result_id from DUAL;
1821 select decode(p_reason_id,-1, NULL, p_reason_id) into l_reason_id from DUAL;
1822 if (l_parent_ih_id < 0) then
1823 l_parent_ih_id := null;
1824 end if;
1825
1826 l_interaction_rec.start_date_time := l_start_date;
1827 l_interaction_rec.end_date_time := SYSDATE;
1828 l_interaction_rec.resource_id := l_resource_id;
1829 l_interaction_rec.party_id := l_ih_customer_id;
1830 l_interaction_rec.primary_party_id := l_primary_customer_id;
1831 l_interaction_rec.contact_party_id := l_contact_id;
1832 l_interaction_rec.contact_rel_party_id := l_relationship_id;
1833 l_interaction_rec.outcome_id := p_outcome_id;
1834 l_interaction_rec.result_id := l_result_id;
1835 l_interaction_rec.handler_id := 680; -- IEM APPL_ID
1836 l_interaction_rec.reason_id := l_reason_id;
1837 l_interaction_rec.parent_id := l_parent_ih_id;
1838
1839 JTF_IH_PUB.Open_Interaction( p_api_version => 1.1,
1840 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
1841 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
1842 p_user_id => l_created_by,
1843 p_login_id => l_last_update_login,
1844 x_return_status => l_status,
1845 x_msg_count => l_msg_count,
1846 x_msg_data => l_msg_data,
1847 x_interaction_id => l_interaction_id,
1848 p_interaction_rec => l_interaction_rec
1849 );
1850
1851 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
1852 raise IHError;
1853 end if;
1854
1855 UPDATE iem_rt_interactions set interaction_id = l_interaction_id,
1856 ih_creator = 'Y'
1857 WHERE rt_interaction_id = l_rt_interaction_id;
1858 END IF;
1859
1860 x_interaction_id := l_interaction_id;
1861
1862 -------------------End Code------------------------
1863 -- Standard Check Of p_commit.
1864 IF FND_API.To_Boolean(p_commit) THEN
1865 COMMIT WORK;
1866 END IF;
1867
1868 EXCEPTION
1869 WHEN IHError THEN
1870 ROLLBACK TO getIHID_pvt;
1871 x_return_status := l_status;
1872 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
1873 p_count => x_msg_count,
1874 p_data => x_msg_data);
1875
1876 WHEN FND_API.G_EXC_ERROR THEN
1877 ROLLBACK TO getIHID_pvt;
1878 x_return_status := FND_API.G_RET_STS_ERROR ;
1879 FND_MSG_PUB.Count_And_Get(
1880 p_encoded => FND_API.G_TRUE,
1881 p_count => x_msg_count,
1882 p_data => x_msg_data);
1883
1884 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1885 ROLLBACK TO getIHID_pvt;
1886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1887 FND_MSG_PUB.Count_And_Get(
1888 p_encoded => FND_API.G_TRUE,
1889 p_count => x_msg_count,
1890 p_data => x_msg_data);
1891 WHEN OTHERS THEN
1892 ROLLBACK TO getIHID_pvt;
1893 x_return_status := FND_API.G_RET_STS_ERROR;
1894 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1895 THEN
1896 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1897 END IF;
1898 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
1899 p_count => x_msg_count,
1900 p_data => x_msg_data);
1901 END getIHID;
1902
1903
1904 PROCEDURE wrapUp (p_api_version_number IN NUMBER,
1905 p_init_msg_list IN VARCHAR2,
1906 p_commit IN VARCHAR2,
1907 p_action IN VARCHAR2,
1908 p_action_id IN NUMBER,
1909 p_rt_media_item_id IN NUMBER,
1910 p_version IN NUMBER,
1911 p_customer_id IN NUMBER,
1912 p_activity_type_id IN NUMBER,
1913 p_outcome_id IN NUMBER,
1914 p_result_id IN NUMBER,
1915 p_reason_id IN NUMBER,
1916 p_to_resource_id IN NUMBER,
1917 p_subject IN VARCHAR2,
1918 p_to_address IN VARCHAR2,
1919 p_transfer_msg_flag IN VARCHAR2,
1920 p_to_account_id IN NUMBER,
1921 p_to_classi_id IN NUMBER,
1922 p_reroute_type IN NUMBER,
1923 x_return_status OUT NOCOPY VARCHAR2,
1924 x_msg_count OUT NOCOPY NUMBER,
1925 x_msg_data OUT NOCOPY VARCHAR2
1926 ) IS
1927
1928 l_api_name VARCHAR2(255);
1929 l_api_version_number NUMBER;
1930 l_created_by NUMBER;
1931 l_last_updated_by NUMBER;
1932 l_last_update_login NUMBER;
1933
1934 l_status VARCHAR2(300);
1935 l_msg_count NUMBER;
1936 l_msg_data VARCHAR2(300);
1937
1938 l_activity_type_id NUMBER;
1939 l_result_id NUMBER;
1940 l_reason_id NUMBER;
1941
1942 unrecognizedAction EXCEPTION;
1943 IHError EXCEPTION;
1944 MDTError EXCEPTION;
1945 badResourceId EXCEPTION;
1946 RTError EXCEPTION;
1947
1948 l_action_id_i NUMBER;
1949 l_action_id_o NUMBER;
1950
1951 l_rt_interaction_id NUMBER;
1952 l_interaction_id NUMBER;
1953 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
1954 l_customer_id NUMBER;
1955 l_resource_id NUMBER;
1956 l_start_date DATE;
1957 l_ob_media_id NUMBER;
1958 l_ib_media_id NUMBER;
1959 l_rfc822_message_id VARCHAR2(300);
1960 l_creation_date DATE;
1961 l_media_lc_rec JTF_IH_PUB.media_lc_rec_type;
1962 l_media_rec JTF_IH_PUB.media_rec_type;
1963 x_milcs_id NUMBER;
1964 x_activity_id_i NUMBER;
1965 x_activity_id_o NUMBER;
1966 l_activity_rec JTF_IH_PUB.activity_rec_type;
1967 l_mdt_message_id NUMBER;
1968 l_session_id NUMBER;
1969 l_activity_id NUMBER;
1970 l_count NUMBER;
1971 l_data VARCHAR2(300);
1972 l_ret_status VARCHAR2(300);
1973 l_activity_type VARCHAR2(200);
1974 l_email_account_id NUMBER;
1975 l_agent_account_id NUMBER;
1976 l_pureOb NUMBER; -- 0 pure ob; 1 relpy to an ib.
1977
1978 l_sr_id NUMBER;
1979 l_lead_id NUMBER;
1980 l_sr_action VARCHAR2(8);
1981 l_action_item_id NUMBER;
1982 l_action_id NUMBER;
1983 l_action_id_sr NUMBER;
1984 l_ih_creator VARCHAR2(1);
1985 l_ob_action_id NUMBER;
1986 l_parent_ih_id NUMBER;
1987
1988 l_email_type VARCHAR2(1);
1989 l_the_rt_media_item_id NUMBER;
1990
1991 m_rt_media_item_id number;
1992 l_reroute_to_acct number;
1993 l_reroute_to_classi number;
1994 l_reroute_to_folder varchar2(255);
1995 m_uid number;
1996 m_reroute_type number;
1997 IEM_BAD_REROUTE_CLASSI exception;
1998 IEM_BAD_REROUTE_TYPE exception;
1999 RerouteError exception;
2000 l_message_flag varchar2(1);
2001 l_contact_id number;
2002 l_autoReplied varchar2(1);
2003 l_outb_method number;
2004 l_to_resource_id number;
2005 l_uid number;
2006 IEM_REDIRECT_EX EXCEPTION;
2007 l_spv_resource_id number;
2008 l_to_group_id number;
2009 l_ih_contact_id number;
2010 l_party_type varchar2(20);
2011 l_relationship_id number;
2012 l_primary_customer_id number;
2013 l_ih_customer_id number;
2014 l_rt_ih_status varchar2(2);
2015 l_use_suggested number;
2016 l_ih_subject varchar2(80);
2017 l_reroute_resource_id number;
2018 l_outb_message_id number;
2019 l_mc_param_action varchar2(20);
2020 l_mc_parameter_id number;
2021 l_i_sequence number;
2022 l_m_sequence number;
2023 l_tran_lead_id number;
2024 l_tran_to_acct_id number;
2025
2026 CURSOR sel_csr IS
2027 SELECT session_id from IEU_SH_SESSIONS
2028 WHERE BEGIN_DATE_TIME = (SELECT MAX(BEGIN_DATE_TIME)
2029 FROM IEU_SH_SESSIONS
2030 WHERE RESOURCE_ID = l_resource_id
2031 AND ACTIVE_FLAG = 'T'
2032 AND APPLICATION_ID = 680);
2033
2034 BEGIN
2035 -- Standard Start of API savepoint
2036 SAVEPOINT wrapUp_pvt;
2037 -- Init vars
2038 l_api_name :='wrapUp';
2039 l_api_version_number :=1.0;
2040 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
2041 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
2042 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
2043 l_ob_action_id := 0;
2044 l_reroute_to_classi := null;
2045 l_mc_param_action := ' ';
2046
2047 -- Standard call to check for call compatibility.
2048 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2049 1.0,
2050 l_api_name,
2051 G_PKG_NAME)
2052 THEN
2053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2054 END IF;
2055
2056 -- Initialize message list if p_init_msg_list is set to TRUE.
2057 IF FND_API.to_Boolean( p_init_msg_list )
2058 THEN
2059 FND_MSG_PUB.initialize;
2060 END IF;
2061
2062 -- Initialize API return status to SUCCESS
2063 x_return_status := FND_API.G_RET_STS_SUCCESS;
2064
2065 -----------------------Code------------------------
2066
2067 --- truncate to 80 characters
2068 IF lengthb(p_subject)>80 then
2069 l_ih_subject:=substrb(p_subject,1,80);
2070 ELSE
2071 l_ih_subject:=p_subject;
2072 END IF;
2073
2074
2075 -- Get the values that are needed later.
2076 SELECT rt_interaction_id, agent_account_id, email_account_id
2077 INTO l_rt_interaction_id, l_agent_account_id, l_email_account_id
2078 FROM iem_rt_media_items
2079 WHERE rt_media_item_id = p_rt_media_item_id
2080 AND expire in (G_ACTIVE, G_QUEUEOUT)
2081 FOR UPDATE;
2082
2083 l_ib_media_id := null;
2084 l_mdt_message_id := null;
2085 BEGIN
2086 SELECT media_id, message_id, folder_uid
2087 INTO l_ib_media_id, l_mdt_message_id, l_uid
2088 FROM iem_rt_media_items
2089 WHERE rt_interaction_id = l_rt_interaction_id
2090 AND expire in (G_ACTIVE, G_QUEUEOUT)
2091 AND email_type = G_INBOUND;
2092 EXCEPTION
2093 WHEN OTHERS THEN
2094 NULL;
2095 END;
2096
2097 -- for redirect wrapup is just to call redirect API then expire the rt items.
2098 if (p_action = G_REDIRECT) then
2099 IEM_REROUTE_PUB.IEM_MAIL_REDIRECT_ACCOUNT(
2100 p_api_version_number =>1.0,
2101 p_init_msg_list => FND_API.G_FALSE,
2102 p_commit => FND_API.G_FALSE,
2103 p_msgid => l_mdt_message_id,
2104 p_email_account_id => l_email_account_id,
2105 p_uid => l_uid,
2106 x_msg_count => l_msg_count,
2107 x_return_status => l_status,
2108 x_msg_data => l_msg_data
2109 );
2110 if ( l_status = FND_API.G_RET_STS_ERROR ) then
2111 raise IEM_REDIRECT_EX;
2112 end if;
2113
2114 UPDATE iem_outbox_errors SET expire = G_EXPIRE
2115 WHERE rt_media_item_id = p_rt_media_item_id;
2116
2117 update iem_rt_interactions set expire = G_EXPIRE
2118 where rt_interaction_id = l_rt_interaction_id;
2119
2120 update iem_rt_media_items set expire = G_EXPIRE
2121 where rt_media_item_id = p_rt_media_item_id;
2122
2123 goto end_of_wrapup;
2124
2125 end if;
2126
2127
2128 BEGIN
2129 SELECT decode(interaction_id, -1, null, interaction_id),
2130 ih_creator, service_request_id, service_request_action,
2131 decode(parent_interaction_id, NULL, fnd_api.g_miss_num, parent_interaction_id),
2132 lead_id, nvl(action_id, -1), nvl(contact_id, -1), nvl(to_resource_id, -1),
2133 relationship_id, status, mc_parameter_id
2134 INTO l_interaction_id, l_ih_creator, l_sr_id, l_sr_action, l_parent_ih_id,
2135 l_lead_id, l_action_id, l_contact_id, l_to_resource_id, l_relationship_id,
2136 l_rt_ih_status, l_mc_parameter_id
2137 FROM iem_rt_interactions
2138 WHERE rt_interaction_id = l_rt_interaction_id
2139 AND expire in (G_ACTIVE, G_QUEUEOUT, G_PROCESSING);
2140 EXCEPTION
2141 WHEN OTHERS THEN
2142 NULL;
2143 END;
2144
2145 if (l_mc_parameter_id > 0) then
2146 select action into l_mc_param_action
2147 from iem_mc_parameters where mc_parameter_id = l_mc_parameter_id;
2148 end if;
2149
2150 -- set contact id for IH recording.
2151 l_ih_customer_id := p_customer_id;
2152
2153 select PARTY_TYPE into l_party_type from HZ_PARTIES
2154 where party_id = p_customer_id;
2155
2156 if ( l_party_type = 'PERSON' ) then
2157 l_primary_customer_id := l_ih_customer_id;
2158 l_ih_contact_id := l_ih_customer_id;
2159 l_relationship_id := null;
2160
2161 elsif ( l_party_type = 'ORGANIZATION') then
2162 l_primary_customer_id := l_ih_customer_id;
2163
2164 if ( l_contact_id > 0 ) then
2165 if ( l_relationship_id > 0 ) then
2166 l_ih_contact_id := l_contact_id;
2167 else
2168 l_ih_contact_id := null;
2169 l_relationship_id := null;
2170 end if;
2171 else
2172 l_ih_contact_id := null;
2173 l_relationship_id := null;
2174 end if;
2175
2176 else -- use old method PARTY_RELATIONSHIP
2177 l_primary_customer_id := null;
2178 l_ih_contact_id := null;
2179 l_relationship_id := null;
2180 end if;
2181 -- end of ih customer info
2182
2183 if(l_ib_media_id > 0) then
2184 l_pureOb := 1; -- reply to a message
2185 else
2186 l_pureOb := 0; -- a pure ob message
2187 end if;
2188
2189 l_ob_media_id := null;
2190 BEGIN
2191 SELECT media_id, message_id, creation_date
2192 INTO l_ob_media_id, l_outb_message_id, l_creation_date
2193 FROM iem_rt_media_items
2194 WHERE rt_interaction_id = l_rt_interaction_id
2195 AND expire in (G_ACTIVE, G_QUEUEOUT)
2196 AND email_type = G_OUTBOUND;
2197 EXCEPTION
2198 WHEN OTHERS THEN
2199 NULL;
2200 END;
2201
2202 if (l_agent_account_id IS NOT NULL) then
2203 SELECT email_account_id
2204 INTO l_email_account_id
2205 FROM IEM_AGENTS
2206 WHERE agent_id = l_agent_account_id;
2207 end if;
2208
2209 SELECT customer_id, resource_id, creation_date, nvl(spv_resource_id, -1)
2210 INTO l_customer_id, l_resource_id, l_start_date, l_spv_resource_id
2211 FROM iem_rt_interactions
2212 WHERE rt_interaction_id = l_rt_interaction_id;
2213
2214 -- use supervisor resource id if exists
2215 l_reroute_resource_id := l_resource_id;
2216 if ( l_spv_resource_id > 0 ) then
2217 l_resource_id := l_spv_resource_id;
2218 end if;
2219
2220 select decode(p_activity_type_id,-1, NULL, p_activity_type_id) into l_activity_type_id from DUAL;
2221 select decode(p_result_id,-1, NULL, p_result_id) into l_result_id from DUAL;
2222 select decode(p_reason_id,-1, NULL, p_reason_id) into l_reason_id from DUAL;
2223
2224 IF (l_action_id < 0) THEN
2225 l_action_id := p_action_id;
2226 END IF;
2227
2228 IF (UPPER(p_action) = 'S') THEN
2229 if (l_pureOb = 0) then
2230 -- EMAIL_AUTO_ACK 83, EMAIL_FORWARD 84, EMAIL_FORWARD 85, EMAIL_RESEND 86
2231 if ( l_action_id = 83 ) then
2232 l_action_id_o := 29; -- EMAIL_ACKNOWLEDGED
2233 elsif ( l_action_id = 84 OR l_action_id = 85 OR l_action_id = 86) then
2234 l_action_id_o := 2; -- EMAIL_SENT (media life cycle segment)
2235 else
2236 l_action_id_o := 26; -- EMAIL_COMPOSE
2237 end if;
2238
2239 l_activity_type := 'EMAIL_COMPOSED';
2240 else
2241 begin
2242 if (l_action_id = 74) then
2243 l_action_id_i := 41; --EMAIL_AUTO_REPLY (media life cycle segment)
2244 else
2245 l_action_id_i := 19; -- EMAIL_REPLY
2246 end if;
2247 end;
2248
2249 l_action_id_o := 2; -- EMAIL_SENT (media life cycle segment)
2250 l_activity_type := 'EMAIL_RESPONDED';
2251 l_ob_action_id := 22;
2252 end if;
2253 ELSIF (UPPER(p_action) = 'D') THEN
2254 l_action_id_i := 6;
2255 l_action_id_o := 6;
2256 l_activity_type := 'EMAIL_DELETED';
2257 ELSIF (UPPER(p_action) = 'T') THEN
2258
2259 if (p_transfer_msg_flag = 'E') then
2260 l_action_id_i := 44; -- EMAIL_ESCALATED
2261 l_action_id_o := 44;
2262 else
2263 l_action_id_i := 7; -- EMAIL_TRANFERRED
2264 l_action_id_o := 7;
2265 end if;
2266
2267 l_activity_type := 'EMAIL_TRANSFERRED';
2268 --
2269 -- Reroute: action_id: new "Email Rerouted Diff Acct"
2270 -- action_id: new "Email Rerouted Diff Class"
2271 -- action_id: new "Email Requeued"
2272 -- Re-direct: action_id: new "Email Auto Redirected"
2273 -- Note: l_activity_type is for IEU activity.
2274 ELSIF (UPPER(p_action) = 'X') THEN
2275 BEGIN
2276 -- determine which type of reroute it is here:
2277 -- find the record with reroute info (to account id and to classification id)
2278
2279 l_reroute_to_acct := p_to_account_id;
2280 l_reroute_to_classi := p_to_classi_id;
2281 m_reroute_type := p_reroute_type;
2282
2283 -- Set action id based on reroute type
2284 if ( m_reroute_type = 78) then
2285 l_action_id_i := 37; --EMAIL_REROUTED_DIFF_ACCT
2286 l_action_id_o := 37;
2287 l_activity_type := 'EMAIL_REROUTED';
2288 elsif ( m_reroute_type = 77 ) then
2289 l_action_id_i := 38; --EMAIL_REROUTED_DIFF_CLASS
2290 l_action_id_o := 38;
2291 l_activity_type := 'EMAIL_REROUTED';
2292 elsif ( m_reroute_type = 76 ) then
2293 l_action_id_i := 39; --EMAIL_REQUEUED
2294 l_action_id_o := 39;
2295 l_activity_type := 'EMAIL_REROUTED';
2296 l_reroute_to_classi := null;
2297 else
2298 raise IEM_BAD_REROUTE_TYPE;
2299 end if;
2300 END;
2301 ELSIF (UPPER(p_action) = 'V') THEN -- For Email Resolve
2302 l_activity_type := 'EMAIL_RESPONDED';
2303 ELSE
2304 raise unrecognizedAction;
2305 END IF;
2306
2307 IF (l_interaction_id IS NULL) THEN
2308 l_ih_creator := 'Y';
2309
2310 l_interaction_rec.start_date_time := l_start_date;
2311 l_interaction_rec.end_date_time := SYSDATE;
2312 l_interaction_rec.resource_id := l_resource_id;
2313 l_interaction_rec.party_id := l_ih_customer_id;
2314 l_interaction_rec.primary_party_id := l_primary_customer_id;
2315 l_interaction_rec.contact_party_id := l_ih_contact_id;
2316 l_interaction_rec.contact_rel_party_id := l_relationship_id;
2317 l_interaction_rec.outcome_id := p_outcome_id;
2318 l_interaction_rec.result_id := l_result_id;
2319 l_interaction_rec.handler_id := 680; -- IEM APPL_ID
2320 l_interaction_rec.reason_id := l_reason_id;
2321 l_interaction_rec.parent_id := l_parent_ih_id;
2322
2323 JTF_IH_PUB.Open_Interaction( p_api_version => 1.1,
2324 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2325 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2326 p_user_id => l_created_by,
2327 p_login_id => l_last_update_login,
2328 x_return_status => l_status,
2329 x_msg_count => l_msg_count,
2330 x_msg_data => l_msg_data,
2331 x_interaction_id => l_interaction_id,
2332 p_interaction_rec => l_interaction_rec
2333 );
2334
2335 UPDATE iem_rt_interactions set IH_CREATOR = 'Y', interaction_id = l_interaction_id
2336 WHERE rt_interaction_id = l_rt_interaction_id;
2337
2338 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2339 raise IHError;
2340 end if;
2341 END IF;
2342
2343 /* Ranjan
2344 --set doc_id and doc_type
2345
2346 IF NOT (l_sr_id IS NULL) THEN
2347 l_activity_rec.doc_id := l_sr_id;
2348 l_activity_rec.doc_ref := 'SR';
2349 END IF;
2350
2351 IF ( l_action_id = 84 OR l_action_id =85 OR l_action_id =86) THEN
2352 -- resend/forward/re-reply need to copy doc_id and doc_ref from parent
2353 begin
2354 SELECT doc_id, doc_ref into l_activity_rec.doc_id, l_activity_rec.doc_ref
2355 FROM JTF_IH_ACTIVITIES WHERE INTERACTION_ID = l_parent_ih_id
2356 AND ACTION_ID in (22,30,31,33,65,72,74);
2357 exception
2358 when others then
2359 null;
2360 end;
2361 END IF;
2362
2363 end of comment Ranjan*/
2364
2365 -- creat o/b media item if one does not exist.
2366 if (l_ob_media_id IS NULL) then
2367 l_media_rec.direction := G_O_DIRECTION;
2368 l_media_rec.source_id := l_email_account_id;
2369 l_media_rec.start_date_time := l_creation_date;
2370 l_media_rec.media_item_type := G_MEDIA_TYPE;
2371 l_media_rec.media_item_ref := l_outb_message_id;
2372 l_media_rec.media_data := l_ih_subject;
2373
2374 JTF_IH_PUB.Open_MediaItem(p_api_version => 1.0,
2375 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2376 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2377 p_user_id => l_created_by,
2378 p_login_id => l_last_update_login,
2379 x_return_status => l_status,
2380 x_msg_count => l_msg_count,
2381 x_msg_data => l_msg_data,
2382 p_media_rec => l_media_rec,
2383 x_media_id => l_ob_media_id
2384 );
2385 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2386 raise IHError;
2387 end if;
2388
2389 end if;
2390
2391 -- Add MLCS o/b
2392 -- Add MLCS only for action not equals to 'Resolve ' p_action!='V' Ranjan 10/31/2007
2393 if p_action<>'V' then -- It is not a resolve
2394 l_media_lc_rec.start_date_time := SYSDATE;
2395 l_media_lc_rec.end_date_time := SYSDATE;
2396 l_media_lc_rec.media_id := l_ob_media_id;
2397 l_media_lc_rec.milcs_type_id := l_action_id_o;
2398 l_media_lc_rec.resource_id := l_resource_id;
2399 l_media_lc_rec.handler_id := 680;
2400 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
2401 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2402 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2403 p_user_id => l_created_by,
2404 p_login_id => l_last_update_login,
2405 x_return_status => l_status,
2406 x_msg_count => l_msg_count,
2407 x_msg_data => l_msg_data,
2408 x_milcs_id => x_milcs_id,
2409 p_media_lc_rec => l_media_lc_rec);
2410
2411 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2412 raise IHError;
2413 end if;
2414 end if;
2415 -- close o/b
2416 l_media_rec.media_id := l_ob_media_id;
2417 l_media_rec.direction := G_O_DIRECTION;
2418 l_media_rec.media_item_type := G_MEDIA_TYPE;
2419 l_media_rec.media_item_ref := l_outb_message_id;
2420 l_media_rec.media_data := l_ih_subject;
2421 l_media_rec.address := p_to_address;
2422
2423 JTF_IH_PUB.Close_MediaItem(p_api_version => 1.0,
2424 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2425 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2426 p_user_id => l_created_by,
2427 p_login_id => l_last_update_login,
2428 x_return_status => l_status,
2429 x_msg_count => l_msg_count,
2430 x_msg_data => l_msg_data,
2431 p_media_rec => l_media_rec
2432 );
2433
2434 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2435 raise IHError;
2436 end if;
2437
2438 -- Add MLCS only for action not equals to 'Resolve ' p_action!='V' Ranjan 10/31/2007
2439 if p_action<>'V' then -- It is not a resolve
2440 -- Add MLCS i/b inbound may not exist (pure o/b)
2441 if(l_ib_media_id > 0 AND l_mc_param_action <> 'srautonotification') then
2442 l_media_lc_rec.start_date_time := SYSDATE;
2443 l_media_lc_rec.end_date_time := SYSDATE;
2444 l_media_lc_rec.media_id := l_ib_media_id;
2445 l_media_lc_rec.milcs_type_id := l_action_id_i;
2446 l_media_lc_rec.resource_id := l_resource_id;
2447 l_media_lc_rec.handler_id := 680;
2448 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
2449 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2450 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2451 p_user_id => l_created_by,
2452 p_login_id => l_last_update_login,
2453 x_return_status => l_status,
2454 x_msg_count => l_msg_count,
2455 x_msg_data => l_msg_data,
2456 x_milcs_id => x_milcs_id,
2457 p_media_lc_rec => l_media_lc_rec);
2458 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2459 raise IHError;
2460 end if;
2461 end if;
2462 END IF ; -- End if for p_action<>'V' Ranjan 10/31/07
2463 --set doc_id and doc_type
2464
2465 IF NOT (l_sr_id IS NULL) THEN
2466 l_activity_rec.doc_id := l_sr_id;
2467 l_activity_rec.doc_ref := 'SR';
2468 END IF;
2469
2470 IF ( l_action_id = 84 OR l_action_id =85 OR l_action_id =86) THEN
2471 -- resend/forward/re-reply need to copy doc_id and doc_ref from parent
2472 begin
2473 SELECT doc_id, doc_ref into l_activity_rec.doc_id, l_activity_rec.doc_ref
2474 FROM JTF_IH_ACTIVITIES WHERE INTERACTION_ID = l_parent_ih_id
2475 AND ACTION_ID in (22,30,31,33,65,72,74);
2476 exception
2477 when others then
2478 null;
2479 end;
2480 END IF;
2481 -- Create Activity against primary media_id
2482 l_activity_rec.start_date_time := SYSDATE;
2483 l_activity_rec.end_date_time := SYSDATE;
2484 if (l_ib_media_id > 0 AND l_mc_param_action <> 'srautonotification') then
2485 l_activity_rec.media_id := l_ib_media_id;
2486 l_activity_rec.action_id := l_action_id;
2487 l_activity_rec.interaction_id := l_interaction_id;
2488 l_activity_rec.outcome_id := p_outcome_id;
2489 l_activity_rec.result_id := l_result_id;
2490 l_activity_rec.reason_id := l_reason_id;
2491 l_activity_rec.action_item_id := l_activity_type_id;
2492
2493
2494 JTF_IH_PUB.Add_Activity(p_api_version => 1.0,
2495 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2496 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2497 p_user_id => l_created_by,
2498 p_login_id => l_last_update_login,
2499 x_return_status => l_status,
2500 x_msg_count => l_msg_count,
2501 x_msg_data => l_msg_data,
2502 p_activity_rec => l_activity_rec,
2503 x_activity_id => x_activity_id_i
2504 );
2505
2506 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2507 raise IHError;
2508 end if;
2509 end if;
2510
2511 if ((l_ob_media_id IS NOT NULL) AND (p_action = 'S')) then
2512 -- Create Activity against outbound media_id, reply or a pure outbound, only if it is sent.
2513 -- Transfer and Deletes of OutBounds are not recorded.
2514 l_activity_rec.media_id := l_ob_media_id;
2515 l_activity_rec.interaction_id := l_interaction_id;
2516 l_activity_rec.outcome_id := p_outcome_id;
2517 l_activity_rec.result_id := l_result_id;
2518 l_activity_rec.reason_id := l_reason_id;
2519 l_activity_rec.action_item_id := l_activity_type_id;
2520
2521 IF (l_ob_action_id <> 0) THEN
2522 l_activity_rec.action_id := l_ob_action_id;
2523 ELSE
2524 l_activity_rec.action_id := l_action_id;
2525 END IF;
2526
2527 JTF_IH_PUB.Add_Activity(p_api_version => 1.0,
2528 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2529 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2530 p_user_id => l_created_by,
2531 p_login_id => l_last_update_login,
2532 x_return_status => l_status,
2533 x_msg_count => l_msg_count,
2534 x_msg_data => l_msg_data,
2535 p_activity_rec => l_activity_rec,
2536 x_activity_id => x_activity_id_o
2537 );
2538
2539 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2540 raise IHError;
2541 end if;
2542 end if;
2543
2544
2545 --create Activity for SR
2546 IF NOT (l_sr_action IS NULL) THEN
2547 l_action_item_id := 17;
2548
2549 IF (UPPER(l_sr_action) = 'CREATE') THEN
2550 l_action_id_sr := 13;
2551 END IF;
2552
2553 IF (UPPER(l_sr_action) = 'UPDATE') THEN
2554 l_action_id_sr := 14;
2555 END IF;
2556
2557 l_activity_rec.start_date_time := SYSDATE;
2558 l_activity_rec.end_date_time := SYSDATE;
2559 l_activity_rec.action_id := l_action_id_sr;
2560 l_activity_rec.interaction_id := l_interaction_id;
2561 l_activity_rec.outcome_id := p_outcome_id;
2562 l_activity_rec.result_id := l_result_id;
2563 l_activity_rec.reason_id := l_reason_id;
2564 l_activity_rec.action_item_id := l_action_item_id;
2565
2566 JTF_IH_PUB.Add_Activity(p_api_version => 1.0,
2567 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2568 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2569 p_user_id => l_created_by,
2570 p_login_id => l_last_update_login,
2571 x_return_status => l_status,
2572 x_msg_count => l_msg_count,
2573 x_msg_data => l_msg_data,
2574 p_activity_rec => l_activity_rec,
2575 x_activity_id => x_activity_id_o
2576 );
2577
2578 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2579 raise IHError;
2580 end if;
2581 END IF;
2582
2583 --create Activity for Lead
2584 IF (l_lead_id IS NOT NULL AND l_lead_id >= 0 AND l_lead_id <> 9999) THEN
2585
2586 -- Note: l_activity_rec.doc_id, if once SR, is changed to Lead
2587 -- So make sure activity for Lead is the last activity to add
2588 if ( l_lead_id = 0 ) then
2589 l_activity_rec.doc_id := null;
2590 l_activity_rec.doc_ref := null;
2591 else
2592 l_activity_rec.doc_id := l_lead_id;
2593 l_activity_rec.doc_ref := 'LEAD';
2594 end if;
2595
2596
2597 l_activity_rec.start_date_time := SYSDATE;
2598 l_activity_rec.end_date_time := SYSDATE;
2599 l_activity_rec.action_id := 71; -- Request
2600 l_activity_rec.interaction_id := l_interaction_id;
2601 l_activity_rec.outcome_id := p_outcome_id;
2602 l_activity_rec.result_id := l_result_id;
2603 l_activity_rec.reason_id := l_reason_id;
2604 l_activity_rec.action_item_id := 8; -- lead;
2605
2606 JTF_IH_PUB.Add_Activity(p_api_version => 1.0,
2607 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2608 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2609 p_user_id => l_created_by,
2610 p_login_id => l_last_update_login,
2611 x_return_status => l_status,
2612 x_msg_count => l_msg_count,
2613 x_msg_data => l_msg_data,
2614 p_activity_rec => l_activity_rec,
2615 x_activity_id => x_activity_id_o
2616 );
2617
2618 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2619 raise IHError;
2620 end if;
2621 END IF;
2622
2623
2624
2625 -- Close IH
2626 l_interaction_rec.interaction_id := l_interaction_id;
2627 l_interaction_rec.end_date_time := SYSDATE;
2628 l_interaction_rec.resource_id := l_resource_id;
2629 l_interaction_rec.party_id := l_ih_customer_id;
2630 l_interaction_rec.primary_party_id := l_primary_customer_id;
2631 -- l_interaction_rec.contact_party_id := l_ih_contact_id;
2632 -- l_interaction_rec.contact_rel_party_id := l_relationship_id;
2633 l_interaction_rec.outcome_id := p_outcome_id;
2634 l_interaction_rec.result_id := l_result_id;
2635 l_interaction_rec.handler_id := 680; -- IEM APPL_ID
2636 l_interaction_rec.reason_id := l_reason_id;
2637 -- done at creation l_interaction_rec.parent_id := l_parent_ih_id;
2638
2639 IF (l_ih_creator = 'Y' OR l_ih_creator = 'S' ) THEN
2640 JTF_IH_PUB.Close_Interaction(p_api_version => 1.1,
2641 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2642 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2643 p_user_id => l_created_by,
2644 p_login_id => l_last_update_login,
2645 x_return_status => l_status,
2646 x_msg_count => l_msg_count,
2647 x_msg_data => l_msg_data,
2648 p_interaction_rec => l_interaction_rec
2649 );
2650
2651 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2652 raise IHError;
2653 end if;
2654 END IF;
2655
2656 -- call server side reroute api
2657
2658 IF (UPPER(p_action) = 'X') THEN
2659 begin
2660 if ( m_reroute_type = 78 ) then
2661 IEM_REROUTE_PUB.IEM_MAIL_REROUTE_ACCOUNT( P_API_VERSION_NUMBER => 1.0,
2662 P_INIT_MSG_LIST => 'F',
2663 P_COMMIT => 'F',
2664 P_MSGID => l_mdt_message_id,
2665 P_AGENT_ID => l_reroute_resource_id,
2666 P_EMAIL_ACCOUNT_ID => p_to_account_id,
2667 P_INTERACTION_ID => l_interaction_id,
2668 P_UID => m_uid,
2669 X_MSG_COUNT => l_msg_count,
2670 X_RETURN_STATUS => l_status,
2671 X_MSG_DATA => l_msg_data);
2672 else
2673 if (m_reroute_type = 76) AND (l_to_resource_id > 0) then
2674 l_to_group_id := l_to_resource_id;
2675 else
2676 l_to_group_id := null;
2677 end if;
2678 IEM_REROUTE_PUB.IEM_MAIL_REROUTE_CLASS( P_API_VERSION_NUMBER => 1.0,
2679 P_INIT_MSG_LIST => 'F',
2680 P_COMMIT => 'F',
2681 P_MSGID => l_mdt_message_id,
2682 P_AGENT_ID => l_reroute_resource_id,
2683 P_CLASS_ID => l_reroute_to_classi,
2684 P_CUSTOMER_ID => p_customer_id,
2685 P_UID => m_uid,
2686 P_INTERACTION_ID => l_interaction_id,
2687 p_GROUP_ID => l_to_group_id,
2688 X_MSG_COUNT => l_msg_count,
2689 X_RETURN_STATUS => l_status,
2690 X_MSG_DATA => l_msg_data);
2691 end if;
2692
2693 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2694 raise RerouteError;
2695 end if;
2696 end;
2697 END IF;
2698
2699 -- update RTI: send, delete, reroute
2700 IF ((UPPER(p_action) = 'S') OR (UPPER(p_action) = 'D') OR (UPPER(p_action)='V') -- Add resolve
2701 OR (UPPER(p_action) = 'X')) THEN
2702 begin
2703 UPDATE iem_rt_interactions SET expire = G_EXPIRE
2704 WHERE rt_interaction_id = l_rt_interaction_id;
2705
2706 UPDATE iem_rt_media_items SET expire = G_EXPIRE
2707 WHERE rt_interaction_id = l_rt_interaction_id;
2708
2709 if ( (UPPER(p_action) <> 'X') AND l_mdt_message_id IS NOT NULL) then
2710 begin
2711 IEM_MAILITEM_PUB.DisposeMailItem (p_api_version_number => 1.0,
2712 p_init_msg_list =>'F' ,
2713 p_commit => 'F',
2714 p_message_id => l_mdt_message_id,
2715 x_return_status => l_status,
2716 x_msg_count => l_msg_count,
2717 x_msg_data => l_msg_data);
2718 exception
2719 when others then
2720 null;
2721 end;
2722 end if;
2723
2724 -- Reset queue_status to null is not really needed for once its
2725 -- agent id set to 0, queue_status is disregarded. But just play safe.
2726 if ( (UPPER(p_action) = 'X') AND (m_reroute_type = 76) ) then
2727 update IEM_RT_PROC_EMAILS set queue_status = null
2728 where message_id = l_mdt_message_id;
2729 end if;
2730 end;
2731 ELSIF (UPPER(p_action) = 'T') THEN
2732 begin
2733
2734 -- Need to create new transferee record here:
2735 -- Lead id to 9999 to prevent lead request activity being created every time transferred
2736 if (l_lead_id >= 0) then
2737 l_tran_lead_id := 9999;
2738 else
2739 l_tran_lead_id := -1;
2740 end if;
2741
2742 select agent_id into l_tran_to_acct_id from iem_agents
2743 where resource_id = p_to_resource_id
2744 and email_account_id = l_email_account_id;
2745
2746 select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
2747 INSERT INTO iem_rt_interactions (
2748 rt_interaction_id, resource_id, customer_id, type,
2749 status, expire, created_by, creation_date, last_updated_by,
2750 last_update_date, last_update_login, contact_id, inb_tag_id,
2751 lead_id, parent_interaction_id, service_request_id,
2752 relationship_id )
2753 SELECT l_i_sequence, p_to_resource_id, l_ih_customer_id, TYPE,
2754 G_WORK_IN_PROGRESS, G_ACTIVE, l_created_by, SYSDATE,
2755 l_last_updated_by, SYSDATE, l_last_update_login,
2756 l_contact_id, inb_tag_id, l_tran_lead_id,
2757 l_interaction_id, l_sr_id,
2758 l_relationship_id
2759 FROM iem_rt_interactions
2760 WHERE rt_interaction_id = l_rt_interaction_id;
2761
2762 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
2763 INSERT INTO iem_rt_media_items (
2764 rt_interaction_id, rt_media_item_id, resource_id,
2765 media_id, message_id, rfc822_message_id, folder_name,
2766 db_server_id, agent_account_id, email_type, status, expire, version,
2767 created_by, creation_date, last_updated_by, last_update_date,
2768 last_update_login, edit_mode )
2769 SELECT l_i_sequence, l_m_sequence, p_to_resource_id,
2770 MEDIA_ID, MESSAGE_ID, RFC822_MESSAGE_ID,
2771 folder_name, db_server_id, l_tran_to_acct_id, EMAIL_TYPE, 'R', G_ACTIVE,
2772 0, l_created_by, SYSDATE, l_last_updated_by, SYSDATE,
2773 l_last_update_login, decode(p_transfer_msg_flag, 'T', null, p_transfer_msg_flag)
2774 FROM iem_rt_media_items
2775 WHERE rt_media_item_id = p_rt_media_item_id;
2776
2777 -- Add MLCS i/b inbound for the second agent
2778 if(l_ib_media_id > 0 AND l_mc_param_action <> 'srautonotification') then
2779 l_media_lc_rec.start_date_time := SYSDATE;
2780 l_media_lc_rec.end_date_time := SYSDATE;
2781 l_media_lc_rec.media_id := l_ib_media_id;
2782 l_media_lc_rec.milcs_type_id := 21; -- EMAIL_TRANSFER (should be transfer_to, but not seeded)
2783 l_media_lc_rec.resource_id := p_to_resource_id;
2784 l_media_lc_rec.handler_id := 680;
2785 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
2786 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2787 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2788 p_user_id => l_created_by,
2789 p_login_id => l_last_update_login,
2790 x_return_status => l_status,
2791 x_msg_count => l_msg_count,
2792 x_msg_data => l_msg_data,
2793 x_milcs_id => x_milcs_id,
2794 p_media_lc_rec => l_media_lc_rec);
2795 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2796 raise IHError;
2797 end if;
2798 end if; -- MLCS second agent
2799
2800 -- Transferrers RT Interaction and RT media are expired
2801 UPDATE iem_rt_interactions SET expire = G_EXPIRE
2802 WHERE rt_interaction_id = l_rt_interaction_id;
2803
2804 -- This expires both the inbound and any outbounds
2805 -- associated with the original message. Change
2806 -- this for co-operate
2807
2808 UPDATE iem_rt_media_items SET expire = G_EXPIRE
2809 WHERE rt_interaction_id = l_rt_interaction_id;
2810
2811 -- Update mdt. Set the new owner of mailItem and from_agent_id and status
2812 -- and open queue_status.
2813 UPDATE IEM_RT_PROC_EMAILS
2814 SET resource_id = p_to_resource_id,
2815 from_resource_id = l_reroute_resource_id,
2816 mail_item_status = 'T',
2817 queue_status = null,
2818 message_flag = p_transfer_msg_flag
2819 WHERE message_id = l_mdt_message_id;
2820
2821 end;
2822 END IF;
2823
2824 -- Record UWQ interaction.
2825 BEGIN
2826 FOR sel_rec in sel_csr LOOP
2827 l_session_id := sel_rec.session_id;
2828 exit;
2829 END LOOP;
2830 IEU_SH_PUB.UWQ_BEGIN_ACTIVITY(
2831 p_api_version => 1.0,
2832 P_INIT_MSG_LIST => 'F',
2833 P_COMMIT => 'F',
2834 p_session_id => l_session_id,
2835 p_activity_type_code => l_activity_type,
2836 P_MEDIA_TYPE_ID => null,
2837 P_MEDIA_ID => null,
2838 p_user_id => l_created_by,
2839 p_login_id => l_last_update_login,
2840 P_REASON_CODE => null,
2841 P_REQUEST_METHOD => null,
2842 P_REQUESTED_MEDIA_TYPE_ID => null,
2843 P_WORK_ITEM_TYPE_CODE => null,
2844 P_WORK_ITEM_PK_ID => null,
2845 p_end_activity_flag => 'Y',
2846 x_activity_id => l_activity_id,
2847 x_msg_count => l_count,
2848 x_msg_data => l_data,
2849 x_return_status => l_ret_status
2850 );
2851 EXCEPTION
2852 WHEN OTHERS THEN
2853 NULL;
2854 END;
2855
2856 -- Expire iem_outbox_errors records if any
2857 UPDATE iem_outbox_errors SET expire = G_EXPIRE
2858 WHERE rt_media_item_id in (SELECT rt_media_item_id
2859 FROM iem_rt_media_items WHERE rt_interaction_id = l_rt_interaction_id);
2860
2861 -- write statistics data
2862 -- IEM_OUTBOUND_METHODS values:
2863 -- 1001 AUTO_REPLY
2864 -- 1002 AUTO_SUGGEST used when a reply uses at least one suggested response
2865 -- 1003 MANUAL_REPLY used when a reply does NOT use any suggested responses
2866 -- 1004 NEW_COMPOSE used for "pure outbound" messages
2867
2868 if ( UPPER(p_action) = 'S') then
2869 if ( l_action_id = 74 ) then
2870 l_autoReplied := 'Y';
2871 l_outb_method := 1001;
2872 else
2873 l_autoReplied := 'N';
2874 if ( l_action_id = 33 ) then
2875 l_outb_method := 1004;
2876 else
2877 begin
2878 l_use_suggested := 0;
2879 select count(OUTBOUND_MSG_STATS_ID) into l_use_suggested
2880 from iem_outbound_msg_stats
2881 where media_id = l_ob_media_id
2882 and USES_SUGGESTIONS_Y_N = 'Y';
2883
2884 if ( l_use_suggested > 0 ) then
2885 l_outb_method := 1002;
2886 else
2887 l_outb_method := 1003;
2888 end if;
2889
2890 end;
2891 end if;
2892 end if;
2893
2894 if ( l_contact_id < 0 ) then
2895 l_contact_id := -1;
2896 end if;
2897
2898
2899 IEM_MSG_STAT_PUB.sendMSGStat(
2900 p_api_version_number => 1.0,
2901 p_init_msg_list => fnd_api.g_false,
2902 p_commit => fnd_api.g_false,
2903 p_outBoundMediaID => l_ob_media_id,
2904 p_inBoundMediaID => nvl(l_ib_media_id, -1),
2905 p_autoReplied => l_autoReplied,
2906 p_agentID => l_resource_id,
2907 p_outBoundMethod => l_outb_method,
2908 p_accountID => l_email_account_id,
2909 p_customerID => p_customer_id,
2910 p_contactID => l_contact_id,
2911 x_return_status => l_ret_status,
2912 x_msg_count => l_count,
2913 x_msg_data => l_data
2914 );
2915 end if;
2916
2917 if (UPPER(p_action) = 'D') then
2918 IEM_MSG_STAT_PUB.deleteMSGStat(
2919 p_api_version_number => 1.0,
2920 p_init_msg_list => fnd_api.g_false,
2921 p_commit => fnd_api.g_false,
2922 p_outBoundMediaID => l_ob_media_id,
2923 p_inBoundMediaID => nvl(l_ib_media_id, -1),
2924 x_return_status => l_ret_status,
2925 x_msg_count => l_count,
2926 x_msg_data => l_data
2927 );
2928 end if;
2929
2930 -------------------End Code------------------------
2931 <<end_of_wrapup>>
2932 -- Standard Check Of p_commit.
2933 IF FND_API.To_Boolean(p_commit) THEN
2934 COMMIT WORK;
2935 END IF;
2936
2937 EXCEPTION
2938 WHEN RerouteError THEN
2939 ROLLBACK TO wrapUp_pvt;
2940 x_return_status := l_status;
2941 x_msg_count := l_msg_count;
2942 x_msg_data := l_msg_data;
2943
2944 WHEN IEM_REDIRECT_EX THEN
2945 ROLLBACK TO wrapUp_pvt;
2946 x_return_status := l_status;
2947 x_msg_count := l_msg_count;
2948 x_msg_data := l_msg_data;
2949
2950 WHEN badResourceId THEN
2951 ROLLBACK TO wrapUp_pvt;
2952 x_return_status := l_status;
2953 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_RESOURCE_ID');
2954 FND_MSG_PUB.ADD;
2955 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
2956 p_count => x_msg_count,
2957 p_data => x_msg_data);
2958 WHEN IHError THEN
2959 ROLLBACK TO wrapUp_pvt;
2960 x_return_status := l_status;
2961 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
2962 p_count => x_msg_count,
2963 p_data => x_msg_data);
2964 WHEN RTError THEN
2965 ROLLBACK TO wrapUp_pvt;
2966 x_return_status := l_status;
2967 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
2968 p_count => x_msg_count,
2969 p_data => x_msg_data);
2970 WHEN MDTError THEN
2971 ROLLBACK TO wrapUp_pvt;
2972 x_return_status := l_status;
2973 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
2974 p_count => x_msg_count,
2975 p_data => x_msg_data);
2976 WHEN FND_API.G_EXC_ERROR THEN
2977 ROLLBACK TO wrapUp_pvt;
2978 x_return_status := FND_API.G_RET_STS_ERROR ;
2979 FND_MSG_PUB.Count_And_Get(
2980 p_encoded => FND_API.G_TRUE,
2981 p_count => x_msg_count,
2982 p_data => x_msg_data);
2983
2984 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2985 ROLLBACK TO wrapUp_pvt;
2986 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2987 FND_MSG_PUB.Count_And_Get(
2988 p_encoded => FND_API.G_TRUE,
2989 p_count => x_msg_count,
2990 p_data => x_msg_data);
2991 WHEN OTHERS THEN
2992 ROLLBACK TO wrapUp_pvt;
2993 x_return_status := FND_API.G_RET_STS_ERROR;
2994 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2995 THEN
2996 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
2997 END IF;
2998 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
2999 p_count => x_msg_count,
3000 p_data => x_msg_data);
3001 END wrapUp;
3002
3003 PROCEDURE recoverCompose (p_api_version_number IN NUMBER,
3004 p_init_msg_list IN VARCHAR2,
3005 p_commit IN VARCHAR2,
3006 p_resource_id IN NUMBER,
3007 x_return_status OUT NOCOPY VARCHAR2,
3008 x_msg_count OUT NOCOPY NUMBER,
3009 x_msg_data OUT NOCOPY VARCHAR2,
3010 x_rfc822_message_id OUT NOCOPY VARCHAR2,
3011 x_account_id OUT NOCOPY NUMBER,
3012 x_account_type OUT NOCOPY VARCHAR2,
3013 x_email_type OUT NOCOPY VARCHAR2,
3014 x_status OUT NOCOPY VARCHAR2,
3015 x_version OUT NOCOPY NUMBER,
3016 x_rt_media_item_id OUT NOCOPY NUMBER,
3017 x_rt_interaction_id OUT NOCOPY NUMBER,
3018 x_oes_id OUT NOCOPY NUMBER,
3019 x_folder_name OUT NOCOPY VARCHAR2,
3020 x_folder_uid OUT NOCOPY NUMBER,
3021 x_customer_id OUT NOCOPY NUMBER
3022 ) IS
3023
3024 l_api_name VARCHAR2(255);
3025 l_api_version_number NUMBER;
3026 l_created_by NUMBER;
3027 l_last_updated_by NUMBER;
3028 l_last_update_login NUMBER;
3029
3030 l_status VARCHAR2(300);
3031 l_msg_count NUMBER;
3032 l_msg_data VARCHAR2(300);
3033
3034 l_email_account_id NUMBER;
3035 l_agent_account_id NUMBER;
3036
3037 badAccountType EXCEPTION;
3038
3039 CURSOR compose_recover_csr IS
3040 SELECT m.rfc822_message_id,m.email_account_id, m.agent_account_id,
3041 m.email_type, m.status, m.version, m.rt_interaction_id,
3042 m.db_server_id, m.rt_media_item_id, m.folder_name, m.folder_uid,
3043 i.customer_id
3044 FROM iem_rt_media_items m, iem_rt_interactions i
3045 WHERE i.TYPE = G_OUTBOUND
3046 AND i.RT_INTERACTION_ID = m.RT_INTERACTION_ID
3047 AND m.RESOURCE_ID = p_resource_id
3048 AND m.EMAIL_TYPE = G_OUTBOUND
3049 AND m.expire = G_ACTIVE
3050 ORDER BY m.rt_interaction_id;
3051
3052 BEGIN
3053
3054 -- Standard Start of API savepoint
3055 SAVEPOINT recoverCompose_pvt;
3056
3057 -- Init vars
3058 l_api_name :='recoverCompose';
3059 l_api_version_number :=1.0;
3060 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
3061 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
3062 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
3063
3064 -- Standard call to check for call compatibility.
3065 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3066 1.0,
3067 l_api_name,
3068 G_PKG_NAME)
3069 THEN
3070 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3071 END IF;
3072
3073 -- Initialize message list if p_init_msg_list is set to TRUE.
3074 IF FND_API.to_Boolean( p_init_msg_list )
3075 THEN
3076 FND_MSG_PUB.initialize;
3077 END IF;
3078
3079 -- Initialize API return status to SUCCESS
3080 x_return_status := FND_API.G_RET_STS_SUCCESS;
3081
3082 -----------------------Code------------------------
3083 /* dbms_output.put_line('In getWork ');
3084 dbms_output.put_line('In getWork : Resource ID '||p_resource_id);
3085 */
3086
3087 -- No Message Found.
3088 x_return_status := 'N';
3089
3090 FOR cr_rec in compose_recover_csr LOOP
3091 if ((cr_rec.email_account_id IS NULL) AND (cr_rec.agent_account_id IS NOT NULL)) then
3092 x_account_id := cr_rec.agent_account_id;
3093 x_account_type := G_AGENT_ACCOUNT;
3094 elsif ((cr_rec.agent_account_id IS NULL) AND (cr_rec.email_account_id IS NOT NULL)) then
3095 x_account_id := cr_rec.email_account_id;
3096 x_account_type := G_MASTER_ACCOUNT;
3097 elsif ((cr_rec.agent_account_id IS NOT NULL) AND (cr_rec.email_account_id IS NOT NULL)) then
3098 x_account_id := cr_rec.agent_account_id;
3099 x_account_type := G_AGENT_ACCOUNT;
3100 else
3101 raise badAccountType;
3102 end if;
3103
3104 x_rfc822_message_id := cr_rec.RFC822_MESSAGE_ID;
3105 x_email_type := cr_rec.EMAIL_TYPE;
3106 x_status := cr_rec.STATUS;
3107 x_version := cr_rec.VERSION;
3108 x_rt_media_item_id := cr_rec.rt_media_item_id;
3109 x_rt_interaction_id := cr_rec.rt_interaction_id;
3110 x_oes_id := cr_rec.DB_SERVER_ID;
3111 x_folder_name := cr_rec.FOLDER_NAME;
3112 x_folder_uid := cr_rec.FOLDER_UID;
3113 x_customer_id := cr_rec.CUSTOMER_ID;
3114 x_return_status := FND_API.G_RET_STS_SUCCESS;
3115
3116 exit; -- get the first record.
3117
3118 END LOOP;
3119 -------------------End Code------------------------
3120
3121 EXCEPTION
3122 WHEN badAccountType THEN
3123 ROLLBACK TO recoverCompose_pvt;
3124 x_return_status := FND_API.G_RET_STS_ERROR ;
3125 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_ACCOUNT_TYPE');
3126 FND_MSG_PUB.ADD;
3127 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
3128 p_count => x_msg_count,
3129 p_data => x_msg_data);
3130 WHEN FND_API.G_EXC_ERROR THEN
3131 ROLLBACK TO recoverCompose_pvt;
3132 x_return_status := FND_API.G_RET_STS_ERROR ;
3133 FND_MSG_PUB.Count_And_Get(
3134 p_encoded => FND_API.G_TRUE,
3135 p_count => x_msg_count,
3136 p_data => x_msg_data);
3137
3138 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3139 ROLLBACK TO recoverCompose_pvt;
3140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3141 FND_MSG_PUB.Count_And_Get(
3142 p_encoded => FND_API.G_TRUE,
3143 p_count => x_msg_count,
3144 p_data => x_msg_data);
3145
3146 WHEN OTHERS THEN
3147 ROLLBACK TO recoverCompose_pvt;
3148 x_return_status := FND_API.G_RET_STS_ERROR;
3149 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3150 THEN
3151 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
3152 END IF;
3153 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
3154 p_count => x_msg_count,
3155 p_data => x_msg_data);
3156 END recoverCompose;
3157
3158 /*
3159 PROCEDURE getAccountDelStatus(p_api_version_number IN NUMBER,
3160 p_init_msg_list IN VARCHAR2,
3161 p_commit IN VARCHAR2,
3162 p_account_id IN NUMBER,
3163 p_account_type IN VARCHAR2,
3164 x_status OUT NOCOPY NUMBER,
3165 x_return_status OUT NOCOPY VARCHAR2,
3166 x_msg_count OUT NOCOPY NUMBER,
3167 x_msg_data OUT NOCOPY VARCHAR2 ) IS
3168
3169 l_api_name VARCHAR2(255);
3170 l_api_version_number NUMBER;
3171 l_data NUMBER;
3172
3173 CURSOR del_status_csr IS
3174 SELECT agent_id
3175 FROM iem_agents
3176 WHERE email_account_id = p_account_id
3177 ORDER BY agent_id;
3178 BEGIN
3179
3180 -- Standard Start of API savepoint
3181 SAVEPOINT getAccountDelStatus_pvt;
3182
3183 -- Init vars
3184 l_api_name :='getAccountDelStatus';
3185 l_api_version_number :=1.0;
3186
3187 -- Standard call to check for call compatibility.
3188 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3189 p_api_version_number,
3190 l_api_name,
3191 G_PKG_NAME)
3192 THEN
3193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3194 END IF;
3195
3196 -- Initialize message list if p_init_msg_list is set to TRUE.
3197 IF FND_API.to_Boolean( p_init_msg_list )
3198 THEN
3199 FND_MSG_PUB.initialize;
3200 END IF;
3201
3202 -- Initialize API return status to SUCCESS
3203 x_return_status := FND_API.G_RET_STS_SUCCESS;
3204
3205 ----------------------------------------CODE-----------------------------------
3206 l_data := 0;
3207 x_status := 0;
3208
3209 IF (UPPER(p_account_type) = G_AGENT_ACCOUNT) THEN
3210 SELECT count(*) into l_data FROM iem_rt_media_items
3211 WHERE agent_account_id = p_account_id
3212 AND expire = G_ACTIVE;
3213
3214 if (l_data = 0) then
3215 x_status := 0;
3216 else
3217 x_status := 1;
3218 end if;
3219 ELSE
3220 x_status := 0;
3221 FOR cr_rec in del_status_csr LOOP
3222 SELECT count(*) into l_data FROM iem_rt_media_items
3223 WHERE agent_account_id = cr_rec.agent_account_id
3224 AND expire = G_ACTIVE;
3225
3226 if (l_data = 0) then
3227 x_status := 0;
3228 else
3229 x_status := 1;
3230 exit;
3231 end if;
3232 END LOOP;
3233 END IF;
3234 ----------------------------------------CODE-----------------------------------
3235 -- Standard Check Of p_commit.
3236 IF FND_API.To_Boolean(p_commit) THEN
3237 COMMIT WORK;
3238 END IF;
3239 -- Standard callto get message count and if count is 1, get message info.
3240 FND_MSG_PUB.Count_And_Get
3241 ( p_encoded => FND_API.G_TRUE,
3242 p_count => x_msg_count,
3243 p_data => x_msg_data
3244 );
3245 EXCEPTION
3246 WHEN FND_API.G_EXC_ERROR THEN
3247 ROLLBACK TO getAccountDelStatus_pvt;
3248 x_return_status := FND_API.G_RET_STS_ERROR ;
3249 FND_MSG_PUB.Count_And_Get
3250 ( p_encoded => FND_API.G_TRUE,
3251 p_count => x_msg_count,
3252 p_data => x_msg_data
3253 );
3254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3255 ROLLBACK TO getAccountDelStatus_pvt;
3256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3257 FND_MSG_PUB.Count_And_Get
3258 ( p_encoded => FND_API.G_TRUE,
3259 p_count => x_msg_count,
3260 p_data => x_msg_data
3261 );
3262 WHEN OTHERS THEN
3263 ROLLBACK TO getAccountDelStatus_pvt;
3264 x_return_status := FND_API.G_RET_STS_ERROR;
3265 IF FND_MSG_PUB.Check_Msg_Level
3266 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3267 THEN
3268 FND_MSG_PUB.Add_Exc_Msg
3269 ( G_PKG_NAME ,
3270 l_api_name
3271 );
3272 END IF;
3273 FND_MSG_PUB.Count_And_Get
3274 ( p_encoded => FND_API.G_TRUE,
3275 p_count => x_msg_count ,
3276 p_data => x_msg_data
3277 );
3278
3279 end getAccountDelStatus;
3280 */
3281
3282
3283 PROCEDURE purgeOutbound (p_api_version_number IN NUMBER,
3284 p_init_msg_list IN VARCHAR2,
3285 p_commit IN VARCHAR2,
3286 x_return_status OUT NOCOPY VARCHAR2,
3287 x_msg_count OUT NOCOPY NUMBER,
3288 x_msg_data OUT NOCOPY VARCHAR2
3289 ) IS
3290
3291 l_api_name VARCHAR2(255);
3292 l_api_version_number NUMBER;
3293 l_created_by NUMBER;
3294 l_last_update_login NUMBER;
3295
3296 l_status VARCHAR2(300);
3297 l_msg_count NUMBER;
3298 l_msg_data VARCHAR2(300);
3299
3300 l_rt_interaction_id NUMBER;
3301 l_media_id NUMBER;
3302 l_mc_parameter_id NUMBER;
3303 l_customer_id NUMBER;
3304 l_resource_id NUMBER;
3305 l_RT_MEDIA_ITEM_ID NUMBER;
3306
3307 l_tmp_ref_key varchar2(200);
3308 l_tmp_ref_name varchar2(200);
3309
3310 l_profile_value varchar2(200);
3311 l_rfc822_message_id VARCHAR2(255);
3312 l_email_type VARCHAR2(1);
3313 l_version NUMBER;
3314 l_media_rec JTF_IH_PUB.MEDIA_REC_TYPE;
3315 i NUMBER;
3316 l_message_id NUMBER;
3317
3318 CURSOR del_rt_csr IS
3319 SELECT iem_rt_interactions.RT_INTERACTION_ID
3320 FROM iem_rt_interactions, iem_rt_media_items
3321 WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
3322 AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
3323 AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;
3324
3325 CURSOR del_rt_expire_csr IS
3326 SELECT RT_MEDIA_ITEM_ID FROM iem_rt_media_items WHERE expire= 'Y';
3327
3328 BEGIN
3329
3330 -- Standard Start of API savepoint
3331 SAVEPOINT purgeOutbound_pvt;
3332
3333 -- Init vars
3334 l_api_name :='purgeOutbound';
3335 l_api_version_number :=1.0;
3336 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
3337 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
3338
3339 -- Standard call to check for call compatibility.
3340 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3341 1.0,
3342 l_api_name,
3343 G_PKG_NAME)
3344 THEN
3345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3346 END IF;
3347
3348 -- Initialize message list if p_init_msg_list is set to TRUE.
3349 IF FND_API.to_Boolean( p_init_msg_list )
3350 THEN
3351 FND_MSG_PUB.initialize;
3352 END IF;
3353
3354 -- Initialize API return status to SUCCESS
3355 x_return_status := FND_API.G_RET_STS_SUCCESS;
3356
3357 -----------------------Code------------------------
3358 BEGIN
3359
3360 -- Clean Iem_msg_parts table
3361 FOR rt_expire_csr_rec in del_rt_expire_csr LOOP
3362 /* Commented due to perf issue bug 6875851 03/13/2008 Ranjan
3363 l_tmp_ref_key := rt_expire_csr_rec.RT_MEDIA_ITEM_ID;
3364
3365 LOOP
3366 l_tmp_ref_name := NULL;
3367
3368 BEGIN
3369 select PART_NAME into l_tmp_ref_name from iem_msg_parts where to_char(ref_key) = l_tmp_ref_key
3370 and PART_TYPE = 'ATTACHMAIL' and rownum < 2;
3371 EXCEPTION WHEN NO_DATA_FOUND THEN
3372 EXIT;
3373 END;
3374
3375
3376 if (l_tmp_ref_name IS NOT NULL) THEN
3377 l_tmp_ref_key := l_tmp_ref_name;
3378 update iem_msg_parts set DELETE_FLAG = 'Y' where to_char(REF_KEY) = l_tmp_ref_key;
3379 END IF;
3380
3381 END LOOP;
3382 modifed the query as below after the comment section
3383 */
3384 update iem_msg_parts
3385 set delete_flag='Y'
3386 where ref_key in (select part_name from iem_msg_parts where
3387 ref_key=rt_expire_csr_rec.RT_MEDIA_ITEM_ID and part_type='ATTACHMAIL');
3388
3389 END LOOP;
3390
3391 -- delete outbox errors
3392 delete from iem_outbox_errors where expire = 'Y';
3393
3394 delete from iem_msg_parts where REF_KEY in
3395 (select rt_media_item_id from iem_rt_media_items WHERE expire='Y');
3396
3397 delete from iem_msg_parts where DELETE_FLAG = 'Y';
3398
3399 -- Delete IEM_MC_PARAMETERS, and IEM_MC_CUSTOM_PARAM
3400 delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3401 (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3402 from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3403 where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3404 and iem_rt_interactions.expire = 'Y');
3405
3406 delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3407 (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3408 from IEM_MC_PARAMETERS, iem_rt_interactions
3409 where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3410 and iem_rt_interactions.expire = 'Y');
3411
3412 -- Delete each record from iem_rt_interactions where expire = 'Y'
3413 delete from iem_rt_interactions where expire = 'Y';
3414
3415 -- Delete each record from iem_msg_datas where its reference at iem_rt_media_items has expire ='Y'.
3416 delete from iem_msg_datas where msg_key in
3417 (select msg_key from iem_rt_media_items, iem_msg_datas
3418 where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3419 and iem_rt_media_items.expire = 'Y');
3420
3421 -- Delete each record from iem_rt_media_items where expire = 'Y'.
3422 delete from iem_rt_media_items where expire = 'Y';
3423
3424 -- Delete each record from iem_agent_sessions where last_update_date is older than 30 days
3425 delete from iem_agent_sessions where SYSDATE - LAST_UPDATE_DATE > 30;
3426
3427
3428 -- Delete each record from IEM_MC_CUSTOM_PARAMS and IEM_MC_PARAMETERS where last_update_date is older than 30 days
3429
3430 delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3431 (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3432 from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3433 where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3434 and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3435 SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3436
3437 delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3438 (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3439 from IEM_MC_PARAMETERS, iem_rt_interactions
3440 where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3441 and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3442 SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3443
3444 FOR cr_rec in del_rt_csr LOOP
3445 l_rt_interaction_id := cr_rec.RT_INTERACTION_ID;
3446
3447 select RT_MEDIA_ITEM_ID, media_id, email_type, message_id
3448 into l_RT_MEDIA_ITEM_ID, l_media_id, l_email_type, l_message_id
3449 from iem_rt_media_items
3450 where rt_interaction_id = l_rt_interaction_id;
3451
3452
3453 l_media_rec.media_id := l_media_id;
3454 l_media_rec.direction := G_O_DIRECTION;
3455 l_media_rec.media_item_type := G_MEDIA_TYPE;
3456 l_media_rec.media_item_ref := l_message_id;
3457
3458 IF (l_email_type = 'I') THEN
3459 l_media_rec.direction := G_I_DIRECTION;
3460 END IF;
3461
3462 JTF_IH_PUB.Close_MediaItem(p_api_version => 1.0,
3463 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
3464 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
3465 p_user_id => l_created_by,
3466 p_login_id => l_last_update_login,
3467 x_return_status => l_status,
3468 x_msg_count => l_msg_count,
3469 x_msg_data => l_msg_data,
3470 p_media_rec => l_media_rec
3471 );
3472
3473
3474 -- Clean Iem_msg_parts table
3475
3476 l_tmp_ref_key := l_RT_MEDIA_ITEM_ID;
3477
3478 /* Commented for perf issue as per bug 6875851
3479 FOR i in 1..50 LOOP
3480 BEGIN
3481 select PART_NAME into l_tmp_ref_name from iem_msg_parts where to_char(ref_key) = l_tmp_ref_key
3482 and PART_TYPE = 'ATTACHMAIL' and rownum < 2;
3483 EXCEPTION WHEN NO_DATA_FOUND THEN
3484 EXIT;
3485 END;
3486
3487
3488 if (l_tmp_ref_name IS NOT NULL) THEN
3489 l_tmp_ref_key := l_tmp_ref_name;
3490 update iem_msg_parts set DELETE_FLAG = 'Y' where to_char(REF_KEY) = l_tmp_ref_key;
3491 END IF;
3492
3493 END LOOP;
3494 modify the part as below.
3495 */
3496 update iem_msg_parts
3497 set delete_flag='Y'
3498 where ref_key in (select part_name from iem_msg_parts where
3499 ref_key=l_RT_MEDIA_ITEM_ID and part_type='ATTACHMAIL');
3500
3501 delete from iem_msg_parts where REF_KEY = l_RT_MEDIA_ITEM_ID;
3502 delete from iem_rt_media_items where RT_MEDIA_ITEM_ID = l_RT_MEDIA_ITEM_ID;
3503 delete from iem_rt_interactions where rt_interaction_id = l_rt_interaction_id;
3504
3505 END LOOP;
3506
3507 -- Clean anything left
3508 delete from iem_msg_parts where DELETE_FLAG = 'Y';
3509 delete from iem_rt_interactions WHERE expire='N' and type = 'O'
3510 AND SYSDATE - LAST_UPDATE_DATE > 30;
3511
3512
3513 EXCEPTION
3514 WHEN OTHERS THEN
3515 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3516 END;
3517
3518 -------------------End Code------------------------
3519 -- Standard Check Of p_commit.
3520 IF FND_API.To_Boolean(p_commit) THEN
3521 COMMIT WORK;
3522 END IF;
3523 -- Standard callto get message count and if count is 1, get message info.
3524 FND_MSG_PUB.Count_And_Get
3525 ( p_encoded => FND_API.G_TRUE,
3526 p_count => x_msg_count,
3527 p_data => x_msg_data
3528 );
3529 EXCEPTION
3530 WHEN FND_API.G_EXC_ERROR THEN
3531 ROLLBACK TO purgeOutbound_pvt;
3532 x_return_status := FND_API.G_RET_STS_ERROR ;
3533 FND_MSG_PUB.Count_And_Get(
3534 p_encoded => FND_API.G_TRUE,
3535 p_count => x_msg_count,
3536 p_data => x_msg_data);
3537
3538 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3539 ROLLBACK TO purgeOutbound_pvt;
3540 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3541 FND_MSG_PUB.Count_And_Get(
3542 p_encoded => FND_API.G_TRUE,
3543 p_count => x_msg_count,
3544 p_data => x_msg_data);
3545
3546 WHEN OTHERS THEN
3547 ROLLBACK TO purgeOutbound_pvt;
3548 x_return_status := FND_API.G_RET_STS_ERROR;
3549 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3550 THEN
3551 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
3552 END IF;
3553 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
3554 p_count => x_msg_count,
3555 p_data => x_msg_data);
3556 END purgeOutbound;
3557
3558
3559
3560 PROCEDURE assignMsg (p_api_version_number IN NUMBER,
3561 p_init_msg_list IN VARCHAR2,
3562 p_commit IN VARCHAR2,
3563 p_message_id IN NUMBER,
3564 p_to_resource_id IN NUMBER,
3565 p_from_resource_id IN NUMBER,
3566 x_return_status OUT NOCOPY VARCHAR2,
3567 x_msg_count OUT NOCOPY NUMBER,
3568 x_msg_data OUT NOCOPY VARCHAR2,
3569 x_rt_media_item_id OUT NOCOPY NUMBER,
3570 x_email_account_id OUT NOCOPY NUMBER,
3571 x_oes_id OUT NOCOPY NUMBER,
3572 x_folder_name OUT NOCOPY VARCHAR2,
3573 x_folder_uid OUT NOCOPY NUMBER,
3574 x_rt_interaction_id OUT NOCOPY NUMBER,
3575 x_customer_id OUT NOCOPY NUMBER,
3576 x_rfc822_message_id OUT NOCOPY VARCHAR2,
3577 x_route_classification OUT NOCOPY VARCHAR2,
3578 x_mdt_message_id OUT NOCOPY NUMBER,
3579 x_service_request_id OUT NOCOPY NUMBER,
3580 x_contact_id OUT NOCOPY NUMBER,
3581 x_lead_id OUT NOCOPY NUMBER,
3582 x_relationship_id OUT NOCOPY NUMBER
3583 ) IS
3584
3585 l_api_name VARCHAR2(255);
3586 l_api_version_number NUMBER;
3587 l_created_by NUMBER;
3588 l_last_updated_by NUMBER;
3589 l_last_update_login NUMBER;
3590
3591 l_email_data_rec IEM_RT_PROC_EMAILS%ROWTYPE;
3592 l_status VARCHAR2(300);
3593 l_msg_count NUMBER;
3594 l_msg_data VARCHAR2(300);
3595
3596 l_i_sequence NUMBER;
3597 l_m_sequence NUMBER;
3598 l_db_server_id NUMBER;
3599 l_classification_id NUMBER;
3600 l_tag_key_value_tbl IEM_MAILITEM_PUB.keyVals_tbl_type;
3601 l_tag_id VARCHAR2(30);
3602 l_sr_id NUMBER;
3603 l_parent_ih_id NUMBER;
3604 l_customer_id NUMBER;
3605 l_contact_id NUMBER;
3606 l_relationship_id NUMBER;
3607 l_lead_id NUMBER;
3608 l_media_lc_rec JTF_IH_PUB.media_lc_rec_type;
3609 l_milcs_id NUMBER;
3610 l_ih_creator VARCHAR2(1);
3611 l_mail_item_status VARCHAR2(1);
3612
3613
3614 BEGIN
3615
3616 -- Standard Start of API savepoint
3617 SAVEPOINT assignMsg_pvt;
3618
3619 -- Init vars
3620 l_api_name :='assignMsg';
3621 l_api_version_number :=1.0;
3622 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
3623 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
3624 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
3625 l_sr_id :=null;
3626 l_parent_ih_id :=null;
3627 l_customer_id :=null;
3628 l_contact_id :=null;
3629 l_relationship_id :=null;
3630 l_lead_id :=null;
3631 l_milcs_id :=null;
3632
3633
3634 -- Standard call to check for call compatibility.
3635 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3636 1.0,
3637 l_api_name,
3638 G_PKG_NAME)
3639 THEN
3640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3641 END IF;
3642
3643 -- Initialize message list if p_init_msg_list is set to TRUE.
3644 IF FND_API.to_Boolean( p_init_msg_list )
3645 THEN
3646 FND_MSG_PUB.initialize;
3647 END IF;
3648
3649 -- Initialize API return status to SUCCESS
3650 x_return_status := FND_API.G_RET_STS_SUCCESS;
3651
3652 -----------------------Code------------------------
3653
3654 -- if assign to herself, this will be recorded as a 'fetch'
3655 if ( p_from_resource_id = p_to_resource_id ) then
3656 l_mail_item_status := 'N'; -- new
3657 else
3658 l_mail_item_status := 'A';
3659 end if;
3660
3661 --Update IEM_RT_PROC_EMAILS with to_agent_id, from_agent_id and status 'A'
3662 IEM_MAILITEM_PUB.GetQueueItemData (p_api_version_number => 1.0,
3663 p_init_msg_list => 'F',
3664 p_commit => 'F',
3665 p_message_id => p_message_id,
3666 p_from_agent_id => p_from_resource_id,
3667 p_to_agent_id => p_to_resource_id,
3668 p_mail_item_status => l_mail_item_status,
3669 x_email_data => l_email_data_rec,
3670 x_tag_key_value => l_tag_key_value_tbl,
3671 x_encrypted_id => l_tag_id,
3672 x_return_status => l_status,
3673 x_msg_count => l_msg_count,
3674 x_msg_data => l_msg_data);
3675
3676 -- Check return status; Proceed on success Or report back in case of error.
3677 IF (l_status = FND_API.G_RET_STS_SUCCESS) THEN
3678 -- Success.
3679 -- Get the name of the route classification from the ID returned above.
3680 -- This is the name of the folder where the inbound message exists on the
3681 -- master account.
3682 SELECT name INTO x_route_classification
3683 FROM iem_route_classifications
3684 WHERE ROUTE_CLASSIFICATION_ID = l_email_data_rec.RT_CLASSIFICATION_ID;
3685
3686 -- Set the folder name
3687 x_folder_name := x_route_classification;
3688
3689 -- Extract tag key value from key value table
3690 -- Currently valid system key names:
3691 -- IEMNBZTSRVSRID for sr id
3692 -- IEMNINTERACTIONID for interaction id
3693 -- IEMNAGENTID for agent id
3694 -- IEMNCUSTOMERID for customer id
3695 -- IEMNCONTACTID for contact id
3696 -- IEMNBZSALELEADID for lead id
3697
3698 FOR i IN 1..l_tag_key_value_tbl.count LOOP
3699 BEGIN
3700 IF (l_tag_key_value_tbl(i).key = 'IEMNBZTSRVSRID' ) THEN
3701 l_sr_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
3702 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNINTERACTIONID' ) THEN
3703 l_parent_ih_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
3704 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNCUSTOMERID' ) THEN
3705 l_customer_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
3706 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNCONTACTID' ) THEN
3707 l_contact_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
3708 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNRELATIONSHIPID' ) THEN
3709 l_relationship_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
3710 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNBZSALELEADID' ) THEN
3711 l_lead_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
3712 END IF;
3713 END;
3714 END LOOP;
3715
3716 -- customer id and contact id from tagging supersede the result from
3717 -- email search (i.e. what are in l_email_date_rec)
3718 IF (l_customer_id is NULL) THEN
3719 BEGIN
3720 l_customer_id := l_email_data_rec.CUSTOMER_ID;
3721 l_contact_id := null;
3722 l_relationship_id := null;
3723 END;
3724 END IF;
3725
3726 -- Record details into the RT tables.
3727 l_ih_creator := null;
3728 if (l_email_data_rec.IH_INTERACTION_ID is not null) then
3729 l_ih_creator := 'Y';
3730 end if;
3731
3732 select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
3733 INSERT INTO iem_rt_interactions (
3734 rt_interaction_id, resource_id, customer_id, contact_id, type,
3735 status, expire, created_by, creation_date, last_updated_by,
3736 last_update_date, last_update_login, parent_interaction_id,
3737 service_request_id, inb_tag_id, interaction_id, ih_creator,
3738 lead_id, relationship_id )
3739 VALUES (
3740 l_i_sequence, p_to_resource_id, l_customer_id, l_contact_id,
3741 G_INBOUND, G_WORK_IN_PROGRESS, G_ACTIVE, l_created_by,
3742 SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
3743 l_parent_ih_id, l_sr_id, l_tag_id,
3744 l_email_data_rec.IH_INTERACTION_ID, l_ih_creator,
3745 l_lead_id, l_relationship_id
3746
3747 );
3748 -- db_server id used by mid-tier to locate accounts
3749 l_db_server_id := -1;
3750
3751 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
3752 INSERT INTO iem_rt_media_items (
3753 rt_interaction_id, rt_media_item_id, resource_id,
3754 media_id, message_id, rfc822_message_id, folder_name,
3755 folder_uid, email_account_id, db_server_id, email_type,
3756 status, expire, version, created_by, creation_date,
3757 last_updated_by, last_update_date, last_update_login )
3758 VALUES (
3759 l_i_sequence, l_m_sequence, p_to_resource_id,
3760 l_email_data_rec.IH_MEDIA_ITEM_ID,
3761 l_email_data_rec.MESSAGE_ID,
3762 null,
3763 x_folder_name,
3764 -1,
3765 l_email_data_rec.EMAIL_ACCOUNT_ID,
3766 l_db_server_id,
3767 G_INBOUND, G_UNMOVED, G_ACTIVE,0, l_created_by, SYSDATE,
3768 l_last_updated_by, SYSDATE, l_last_update_login
3769 );
3770
3771 -- update post_mdts to set queue_status to null in case it is not clear
3772 UPDATE IEM_RT_PROC_EMAILS SET queue_status = NULL WHERE message_id = p_message_id;
3773
3774
3775 --Add MLCS 'email assigned' using to_resource_id and 'email_assign' using resource_id
3776 if ( l_mail_item_status = 'A' ) then
3777
3778 l_media_lc_rec.start_date_time := SYSDATE;
3779 l_media_lc_rec.end_date_time := SYSDATE;
3780 l_media_lc_rec.media_id := l_email_data_rec.IH_MEDIA_ITEM_ID;
3781 l_media_lc_rec.milcs_type_id := 35; -- EMAIL_ASSIGNED
3782 l_media_lc_rec.resource_id := p_to_resource_id;
3783 l_media_lc_rec.handler_id := 680;
3784 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
3785 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
3786 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
3787 p_user_id => l_created_by,
3788 p_login_id => l_last_update_login,
3789 x_return_status => l_status,
3790 x_msg_count => l_msg_count,
3791 x_msg_data => l_msg_data,
3792 x_milcs_id => l_milcs_id,
3793 p_media_lc_rec => l_media_lc_rec);
3794
3795 l_media_lc_rec.start_date_time := SYSDATE;
3796 l_media_lc_rec.end_date_time := SYSDATE;
3797 l_media_lc_rec.media_id := l_email_data_rec.IH_MEDIA_ITEM_ID;
3798 l_media_lc_rec.milcs_type_id := 45; -- EMAIL_ASSIGN
3799 l_media_lc_rec.resource_id := p_from_resource_id;
3800 l_media_lc_rec.handler_id := 680;
3801 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
3802 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
3803 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
3804 p_user_id => l_created_by,
3805 p_login_id => l_last_update_login,
3806 x_return_status => l_status,
3807 x_msg_count => l_msg_count,
3808 x_msg_data => l_msg_data,
3809 x_milcs_id => l_milcs_id,
3810 p_media_lc_rec => l_media_lc_rec);
3811
3812 end if;
3813
3814 -- Return Media Values to the JSPs.
3815 x_rt_media_item_id := l_m_sequence;
3816 x_email_account_id := l_email_data_rec.EMAIL_ACCOUNT_ID;
3817 x_oes_id := l_db_server_id;
3818 x_folder_uid := -1;
3819 x_customer_id := l_customer_id;
3820 x_rfc822_message_id := null;
3821 x_rt_interaction_id := l_i_sequence;
3822 x_mdt_message_id := l_email_data_rec.MESSAGE_ID;
3823 x_service_request_id := l_sr_id;
3824 x_contact_id := l_contact_id;
3825 x_lead_id := l_lead_id;
3826 x_relationship_id := l_relationship_id;
3827 ELSE
3828 -- Return the error returned by MDT API
3829 x_return_status := l_status;
3830 x_msg_count := l_msg_count;
3831 x_msg_data := l_msg_data;
3832
3833 END IF;
3834
3835
3836
3837
3838 -------------------End Code------------------------
3839 -- Standard Check Of p_commit.
3840 IF FND_API.To_Boolean(p_commit) THEN
3841 COMMIT WORK;
3842 END IF;
3843 -- Standard callto get message count and if count is 1, get message info.
3844 FND_MSG_PUB.Count_And_Get
3845 ( p_encoded => FND_API.G_TRUE,
3846 p_count => x_msg_count,
3847 p_data => x_msg_data
3848 );
3849 EXCEPTION
3850 WHEN FND_API.G_EXC_ERROR THEN
3851 ROLLBACK TO assignMsg_pvt;
3852 x_return_status := FND_API.G_RET_STS_ERROR ;
3853 FND_MSG_PUB.Count_And_Get(
3854 p_encoded => FND_API.G_TRUE,
3855 p_count => x_msg_count,
3856 p_data => x_msg_data);
3857
3858 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3859 ROLLBACK TO assignMsg_pvt;
3860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3861 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
3862 p_count => x_msg_count,
3863 p_data => x_msg_data);
3864
3865 WHEN OTHERS THEN
3866 ROLLBACK TO assignMsg_pvt;
3867 x_return_status := FND_API.G_RET_STS_ERROR;
3868 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3869 THEN
3870 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
3871 END IF;
3872 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
3873 p_count => x_msg_count,
3874 p_data => x_msg_data);
3875 END assignMsg;
3876
3877
3878
3879 PROCEDURE queueToOutbox (p_api_version_number IN NUMBER,
3880 p_init_msg_list IN VARCHAR2,
3881 p_commit IN VARCHAR2,
3882 p_action IN VARCHAR2,
3883 p_action_id IN NUMBER,
3884 p_rt_media_item_id IN NUMBER,
3885 p_version IN NUMBER,
3886 p_customer_id IN NUMBER,
3887 p_activity_type_id IN NUMBER,
3888 p_outcome_id IN NUMBER,
3889 p_result_id IN NUMBER,
3890 p_reason_id IN NUMBER,
3891 p_to_resource_id IN NUMBER,
3892 p_status IN VARCHAR2,
3893 x_return_status OUT NOCOPY VARCHAR2,
3894 x_msg_count OUT NOCOPY NUMBER,
3895 x_msg_data OUT NOCOPY VARCHAR2
3896 ) IS
3897 l_api_name VARCHAR2(255);
3898 l_api_version_number NUMBER;
3899 l_created_by NUMBER;
3900 l_last_updated_by NUMBER;
3901 l_last_update_login NUMBER;
3902
3903 l_status VARCHAR2(300);
3904 l_msg_count NUMBER;
3905 l_msg_data VARCHAR2(300);
3906
3907 l_activity_type_id NUMBER;
3908 l_result_id NUMBER;
3909 l_reason_id NUMBER;
3910
3911 badResourceId EXCEPTION;
3912 RTError EXCEPTION;
3913
3914 l_message_id NUMBER;
3915 l_rt_interaction_id NUMBER;
3916 l_version NUMBER;
3917 l_email_type VARCHAR2(1);
3918 l_customer_id NUMBER;
3919 l_count NUMBER;
3920 l_data VARCHAR2(300);
3921 l_ret_status VARCHAR2(300);
3922 l_action_id NUMBER;
3923
3924 IEM_NO_DATA EXCEPTION;
3925 l_resource_id NUMBER;
3926 l_noop NUMBER;
3927
3928 BEGIN
3929
3930 -- Standard Start of API savepoint
3931 SAVEPOINT queueToOutbox_pvt;
3932 -- Init vars
3933 l_api_name :='queueToOutbox';
3934 l_api_version_number :=1.0;
3935 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
3936 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
3937 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
3938 l_noop := 0;
3939
3940 -- Standard call to check for call compatibility.
3941 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3942 1.0,
3943 l_api_name,
3944 G_PKG_NAME)
3945 THEN
3946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3947 END IF;
3948
3949 -- Initialize message list if p_init_msg_list is set to TRUE.
3950 IF FND_API.to_Boolean( p_init_msg_list )
3951 THEN
3952 FND_MSG_PUB.initialize;
3953 END IF;
3954
3955 -- Initialize API return status to SUCCESS
3956 x_return_status := FND_API.G_RET_STS_SUCCESS;
3957
3958 -----------------------Code------------------------
3959
3960 -- Get the values that are needed later.
3961 BEGIN
3962 l_rt_interaction_id := null;
3963 SELECT rt_interaction_id, version, email_type, resource_id
3964 INTO l_rt_interaction_id, l_version, l_email_type, l_resource_id
3965 FROM iem_rt_media_items
3966 WHERE rt_media_item_id = p_rt_media_item_id
3967 AND expire = G_ACTIVE
3968 FOR UPDATE NOWAIT;
3969
3970 IF ((l_version <> p_version) AND (UPPER(l_email_type) = G_OUTBOUND) ) THEN
3971 x_return_status := 'M';
3972 END IF;
3973
3974 EXCEPTION
3975 WHEN OTHERS THEN
3976 NULL;
3977 END;
3978
3979 IF ( l_rt_interaction_id is null ) THEN
3980 raise IEM_NO_DATA;
3981 END IF;
3982
3983 IF ( (l_resource_id = p_to_resource_id) AND
3984 (p_action = 'T' OR p_action = 'H' OR p_action = 'E') ) THEN
3985 l_noop := 1;
3986 END IF;
3987
3988 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS AND l_noop = 0) THEN
3989
3990 BEGIN
3991 l_message_id := null;
3992 SELECT message_id INTO l_message_id FROM iem_rt_media_items
3993 WHERE rt_interaction_id = l_rt_interaction_id
3994 AND email_type = G_INBOUND;
3995 EXCEPTION
3996 WHEN OTHERS THEN
3997 NULL;
3998 END;
3999
4000 UPDATE iem_rt_media_items
4001 SET expire = G_QUEUEOUT
4002 WHERE rt_interaction_id = l_rt_interaction_id and expire <> G_DORMANT;
4003
4004 if ( p_action_id > 0 ) then
4005 l_action_id := p_action_id;
4006 else
4007 l_action_id := null;
4008 end if;
4009
4010 UPDATE iem_rt_interactions
4011 SET expire = G_QUEUEOUT,
4012 status = p_status,
4013 customer_id = decode(p_customer_id, G_NUM_NOP, customer_id, p_customer_id),
4014 action_id = decode(l_action_id, null, action_id, l_action_id),
4015 action_item_id = decode(p_activity_type_id,-1, NULL, p_activity_type_id),
4016 result_id = decode(p_result_id,-1, NULL, p_result_id),
4017 outcome_id = decode(p_outcome_id,-1, NULL, p_outcome_id),
4018 reason_id = decode(p_reason_id,-1, NULL, p_reason_id),
4019 to_resource_id = decode(p_to_resource_id, G_NUM_NOP, to_resource_id, p_to_resource_id)
4020 WHERE rt_interaction_id = l_rt_interaction_id
4021 AND expire = G_ACTIVE;
4022
4023 -- mark the post_mdts to 'Q' to prevent this from considered by getmailitemcount().
4024 if ( l_message_id is not null ) then
4025 UPDATE IEM_RT_PROC_EMAILS SET queue_status = 'Q'
4026 WHERE message_id = l_message_id;
4027 end if;
4028
4029 END IF;
4030
4031 -------------------End Code------------------------
4032 -- Standard Check Of p_commit.
4033 IF FND_API.To_Boolean(p_commit) THEN
4034 COMMIT WORK;
4035 END IF;
4036
4037 EXCEPTION
4038 WHEN IEM_NO_DATA THEN
4039 ROLLBACK TO queueToOutbox_pvt;
4040 x_return_status := FND_API.G_RET_STS_ERROR ;
4041 FND_MESSAGE.SET_NAME('IEM', 'IEM_MSG_INTERCEPTED');
4042 FND_MSG_PUB.ADD;
4043 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4044 p_count => x_msg_count,
4045 p_data => x_msg_data);
4046
4047 WHEN RTError THEN
4048 ROLLBACK TO queueToOutbox_pvt;
4049 x_return_status := FND_API.G_RET_STS_ERROR;
4050 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
4051 p_count => x_msg_count,
4052 p_data => x_msg_data);
4053
4054 WHEN FND_API.G_EXC_ERROR THEN
4055 ROLLBACK TO queueToOutbox_pvt;
4056 x_return_status := FND_API.G_RET_STS_ERROR ;
4057 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4058 p_count => x_msg_count,
4059 p_data => x_msg_data);
4060
4061 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4062 ROLLBACK TO queueToOutbox_pvt;
4063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4064 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4065 p_count => x_msg_count,
4066 p_data => x_msg_data);
4067 WHEN OTHERS THEN
4068 ROLLBACK TO queueToOutbox_pvt;
4069 x_return_status := FND_API.G_RET_STS_ERROR;
4070 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4071 THEN
4072 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
4073 END IF;
4074 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
4075 p_count => x_msg_count,
4076 p_data => x_msg_data);
4077 END queueToOutbox;
4078
4079
4080 PROCEDURE getNextOutboxItem (p_api_version_number IN NUMBER,
4081 p_init_msg_list IN VARCHAR2,
4082 p_commit IN VARCHAR2,
4083 p_failed IN VARCHAR2,
4084 x_return_status OUT NOCOPY VARCHAR2,
4085 x_msg_count OUT NOCOPY NUMBER,
4086 x_msg_data OUT NOCOPY VARCHAR2,
4087 x_rfc822_message_id OUT NOCOPY VARCHAR2,
4088 x_account_id OUT NOCOPY NUMBER,
4089 x_account_type OUT NOCOPY VARCHAR2,
4090 x_email_type OUT NOCOPY VARCHAR2,
4091 x_status OUT NOCOPY VARCHAR2,
4092 x_version OUT NOCOPY NUMBER,
4093 x_rt_media_item_id OUT NOCOPY NUMBER,
4094 x_rt_interaction_id OUT NOCOPY NUMBER,
4095 x_oes_id OUT NOCOPY NUMBER,
4096 x_folder_name OUT NOCOPY VARCHAR2,
4097 x_folder_uid OUT NOCOPY NUMBER,
4098 x_customer_id OUT NOCOPY NUMBER,
4099 x_interaction_id OUT NOCOPY NUMBER,
4100 x_service_request_id OUT NOCOPY NUMBER,
4101 x_mc_parameter_id OUT NOCOPY NUMBER,
4102 x_service_request_action OUT NOCOPY VARCHAR,
4103 x_contact_id OUT NOCOPY NUMBER,
4104 x_parent_ih_id OUT NOCOPY NUMBER,
4105 x_tag_id OUT NOCOPY VARCHAR,
4106 x_rt_ih_status OUT NOCOPY VARCHAR,
4107 x_action_id OUT NOCOPY NUMBER,
4108 x_action_item_id OUT NOCOPY NUMBER,
4109 x_result_id OUT NOCOPY NUMBER,
4110 x_reason_id OUT NOCOPY NUMBER,
4111 x_outcome_id OUT NOCOPY NUMBER,
4112 x_to_resource_id OUT NOCOPY NUMBER,
4113 x_resource_id OUT NOCOPY NUMBER,
4114 x_lead_id OUT NOCOPY NUMBER
4115 ) IS
4116
4117 l_api_name VARCHAR2(255);
4118 l_api_version_number NUMBER;
4119
4120 l_email_account_id NUMBER;
4121 l_agent_account_id NUMBER;
4122 l_expire VARCHAR2(1);
4123 l_email_type VARCHAR2(1);
4124 l_max_try NUMBER;
4125 l_max_try_val VARCHAR2(20);
4126 l_try NUMBER;
4127 l_rt_media_item_id NUMBER;
4128
4129 InteractnComplt EXCEPTION;
4130 badAccountType EXCEPTION;
4131 IEM_NO_DATA EXCEPTION;
4132
4133 Type get_next is REF CURSOR;
4134 rt_cur get_next;
4135 l_rt_ih_data IEM_RT_INTERACTIONS%ROWTYPE;
4136 e_nowait EXCEPTION;
4137 PRAGMA EXCEPTION_INIT(e_nowait, -54);
4138
4139 str VARCHAR2(500);
4140
4141
4142 BEGIN
4143
4144 -- Standard Start of API savepoint
4145 SAVEPOINT getNextOutboxItem_pvt;
4146 -- Init vars
4147 l_api_name :='getNextOutboxItem';
4148 l_api_version_number :=1.0;
4149 l_email_account_id := 0;
4150 l_agent_account_id := 0;
4151
4152 -- Standard call to check for call compatibility.
4153 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
4154 1.0,
4155 l_api_name,
4156 G_PKG_NAME)
4157 THEN
4158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4159 END IF;
4160
4161 -- Initialize message list if p_init_msg_list is set to TRUE.
4162 IF FND_API.to_Boolean( p_init_msg_list )
4163 THEN
4164 FND_MSG_PUB.initialize;
4165 END IF;
4166
4167 -- Initialize API return status to SUCCESS
4168 x_return_status := FND_API.G_RET_STS_SUCCESS;
4169
4170 -----------------------Code------------------------
4171 IF ( p_failed = 'T' ) THEN
4172 -- Get the item that is failed at last attempt to process
4173 str := 'SELECT * FROM iem_rt_interactions
4174 WHERE expire = :1 AND last_update_date < sysdate - 0.007 ORDER BY creation_date FOR UPDATE SKIP LOCKED';
4175 OPEN rt_cur FOR str USING G_PROCESSING;
4176 ELSE
4177 -- Get the item that need to be process.
4178 str := 'SELECT * FROM iem_rt_interactions
4179 WHERE expire = :1 ORDER BY creation_date FOR UPDATE SKIP LOCKED';
4180 OPEN rt_cur FOR str USING G_QUEUEOUT;
4181 END IF;
4182
4183 -- find max_try
4184 IF ( p_failed = 'T' ) THEN
4185
4186 IEM_PARAMETERS_PVT.select_profile(p_api_version_number =>1.0,
4187 P_INIT_MSG_LIST => 'F',
4188 P_COMMIT => 'F',
4189 p_profile_name => 'IEM_OP_MAX_FAIL_RETRIES',
4190 x_profile_value => l_max_try_val,
4191 x_return_status => x_return_status,
4192 x_msg_count => x_msg_count,
4193 x_msg_data => x_msg_data );
4194
4195 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
4196 l_max_try := 3;
4197 x_return_status := FND_API.G_RET_STS_SUCCESS;
4198 ELSE
4199 l_max_try := TO_NUMBER(l_max_try_val);
4200 END IF;
4201
4202 END IF;
4203
4204 LOOP
4205 BEGIN
4206 l_rt_ih_data := null;
4207 FETCH rt_cur into l_rt_ih_data;
4208 IF ( l_rt_ih_data.rt_interaction_id is null ) THEN
4209 EXIT;
4210 END IF;
4211 IF ( p_failed = 'T' ) THEN
4212
4213 -- check max_try
4214 if ( l_rt_ih_data.status = 'S' OR l_rt_ih_data.status = 'F') then -- is 'send' or 'autoforward'
4215 l_email_type := G_OUTBOUND;
4216 else
4217 l_email_type := G_INBOUND;
4218 end if;
4219 begin
4220 l_rt_media_item_id := null;
4221 SELECT rt_media_item_id into l_rt_media_item_id
4222 FROM iem_rt_media_items
4223 WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id
4224 AND email_type = l_email_type
4225 AND status <> G_NEWREROUTE
4226 AND expire <> G_DORMANT;
4227 exception
4228 WHEN OTHERS then
4229 null;
4230 end;
4231
4232 if ( l_rt_media_item_id is not null ) then
4233 begin
4234 l_try := 0;
4235 SELECT count(outbox_error_id) INTO l_try
4236 FROM iem_outbox_errors
4237 WHERE rt_media_item_id = l_rt_media_item_id;
4238 exception
4239 WHEN OTHERS then
4240 null;
4241 end;
4242 if ( l_try < l_max_try ) then
4243 EXIT;
4244 end if;
4245 end if;
4246
4247 ELSE -- failed is false
4248 EXIT;
4249 END IF;
4250 EXCEPTION when e_nowait then
4251 null;
4252 WHEN OTHERS then
4253 null;
4254 END;
4255 END LOOP;
4256 close rt_cur;
4257
4258 IF l_rt_ih_data.rt_interaction_id IS NULL THEN
4259 x_return_status := 'N';
4260
4261 ELSE
4262
4263 -- Mark the item to 'under processing'
4264 BEGIN
4265 UPDATE iem_rt_interactions SET expire = G_PROCESSING, last_update_date = SYSDATE
4266 WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id;
4267 commit;
4268 END;
4269
4270 x_rt_interaction_id := l_rt_ih_data.rt_interaction_id;
4271 x_action_id := l_rt_ih_data.action_id;
4272 x_action_item_id := l_rt_ih_data.action_item_id;
4273 x_result_id := l_rt_ih_data.result_id;
4274 x_reason_id := l_rt_ih_data.reason_id;
4275 x_outcome_id := l_rt_ih_data.outcome_id;
4276 x_rt_ih_status := l_rt_ih_data.status;
4277 x_customer_id := l_rt_ih_data.customer_id;
4278 x_contact_id := l_rt_ih_data.contact_id;
4279
4280
4281
4282 x_interaction_id := l_rt_ih_data.interaction_id;
4283 x_parent_ih_id := l_rt_ih_data.parent_interaction_id;
4284 x_service_request_id := l_rt_ih_data.service_request_id;
4285 x_service_request_action := l_rt_ih_data.service_request_action;
4286 x_mc_parameter_id := l_rt_ih_data.mc_parameter_id;
4287 x_tag_id := l_rt_ih_data.inb_tag_id;
4288 x_to_resource_id := l_rt_ih_data.to_resource_id;
4289 x_resource_id := l_rt_ih_data.resource_id;
4290 x_lead_id := l_rt_ih_data.lead_id;
4291
4292
4293 -- Do a query to get inbound or outbound media details of the specified rt_interaction_id.
4294 BEGIN
4295 x_rt_media_item_id := null;
4296
4297 if ( l_rt_ih_data.status = 'S' OR l_rt_ih_data.status = 'F' ) then -- is 'send' or 'autoforward'
4298 l_email_type := G_OUTBOUND;
4299 else
4300 l_email_type := G_INBOUND;
4301 end if;
4302
4303 SELECT rt_media_item_id, rfc822_message_id,
4304 folder_name, folder_uid, email_account_id, agent_account_id,
4305 db_server_id, email_type, status, version, expire
4306 INTO x_rt_media_item_id, x_rfc822_message_id,
4307 x_folder_name, x_folder_uid, l_email_account_id, l_agent_account_id,
4308 x_oes_id, x_email_type, x_status, x_version, l_expire
4309 FROM iem_rt_media_items
4310 WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id
4311 AND email_type = l_email_type
4312 AND status <> G_NEWREROUTE
4313 AND expire <> G_DORMANT;
4314
4315 EXCEPTION
4316 WHEN OTHERS THEN
4317 raise InteractnComplt;
4318 END;
4319
4320 -- The requested media type exists.
4321 if (x_rt_media_item_id IS NULL ) then
4322 x_return_status := 'N';
4323 else
4324
4325
4326 -- set account type
4327 IF ((l_email_account_id IS NULL) AND (l_agent_account_id IS NOT NULL)) THEN
4328 x_account_id := l_agent_account_id;
4329 x_account_type := G_AGENT_ACCOUNT;
4330 ELSIF ((l_agent_account_id IS NULL) AND (l_email_account_id IS NOT NULL)) THEN
4331 x_account_id := l_email_account_id;
4332 x_account_type := G_MASTER_ACCOUNT;
4333 ELSIF ((l_agent_account_id IS NOT NULL) AND (l_email_account_id IS NOT NULL)) THEN
4334 x_account_id := l_agent_account_id;
4335 x_account_type := G_AGENT_ACCOUNT;
4336 ELSE
4337 raise badAccountType;
4338 END IF;
4339 end if;
4340 END IF;
4341
4342 -------------------End Code------------------------
4343 EXCEPTION
4344 --WHEN IEM_NO_DATA THEN
4345 --ROLLBACK TO getNextOutboxItem_pvt;
4346 --x_return_status := FND_API.G_RET_STS_ERROR ;
4347 --FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
4348 --FND_MSG_PUB.ADD;
4349 --FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4350 -- p_count => x_msg_count,
4351 -- p_data => x_msg_data);
4352 WHEN InteractnComplt THEN
4353 ROLLBACK TO getNextOutboxItem_pvt;
4354 x_return_status := FND_API.G_RET_STS_ERROR ;
4355 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_RT_REC');
4356 FND_MSG_PUB.ADD;
4357 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4358 p_count => x_msg_count,
4359 p_data => x_msg_data);
4360 WHEN badAccountType THEN
4361 ROLLBACK TO getNextOutboxItem_pvt;
4362 x_return_status := FND_API.G_RET_STS_ERROR ;
4363 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_ACCOUNT_TYPE');
4364 FND_MSG_PUB.ADD;
4365 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4366 p_count => x_msg_count,
4367 p_data => x_msg_data);
4368 WHEN FND_API.G_EXC_ERROR THEN
4369 ROLLBACK TO getNextOutboxItem_pvt;
4370 x_return_status := FND_API.G_RET_STS_ERROR ;
4371 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4372 p_count => x_msg_count,
4373 p_data => x_msg_data);
4374
4375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4376 ROLLBACK TO getNextOutboxItem_pvt;
4377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4378 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4379 p_count => x_msg_count,
4380 p_data => x_msg_data);
4381
4382 WHEN OTHERS THEN
4383 ROLLBACK TO getNextOutboxItem_pvt;
4384 x_return_status := FND_API.G_RET_STS_ERROR;
4385 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4386 THEN
4387 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
4388 END IF;
4389 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
4390 p_count => x_msg_count,
4391 p_data => x_msg_data);
4392 END getNextOutboxItem;
4393
4394
4395 PROCEDURE createRTItem (p_api_version_number IN NUMBER,
4396 p_init_msg_list IN VARCHAR2,
4397 p_commit IN VARCHAR2,
4398 p_message_id IN NUMBER, -- IEM_RT_PROC_EMAILS.message_id
4399 p_to_resource_id IN NUMBER, -- agent id you want to stamp to IEM_RT_PROC_EMAILS.agent_id
4400 p_from_resource_id IN NUMBER, -- agent id you want to stamp to IEM_RT_PROC_EMAILS.from_agent_id
4401 p_status IN VARCHAR2, -- this will be stamp to IEM_RT_PROC_EMAILS.mail_item_status
4402 p_reason IN VARCHAR2, -- 'O' for auto-route
4403 p_interaction_id IN NUMBER,
4404 x_return_status OUT NOCOPY VARCHAR2,
4405 x_msg_count OUT NOCOPY NUMBER,
4406 x_msg_data OUT NOCOPY VARCHAR2,
4407 x_rt_media_item_id OUT NOCOPY NUMBER,
4408 x_rt_interaction_id OUT NOCOPY NUMBER
4409 ) IS
4410
4411 l_api_name VARCHAR2(255);
4412 l_api_version_number NUMBER;
4413 l_created_by NUMBER;
4414 l_last_updated_by NUMBER;
4415 l_last_update_login NUMBER;
4416
4417 l_email_data_rec IEM_RT_PROC_EMAILS%ROWTYPE;
4418 l_status VARCHAR2(300);
4419 l_msg_count NUMBER;
4420 l_msg_data VARCHAR2(300);
4421
4422 l_i_sequence NUMBER;
4423 l_m_sequence NUMBER;
4424 l_db_server_id NUMBER;
4425 l_classification_id NUMBER;
4426 l_tag_key_value_tbl IEM_MAILITEM_PUB.keyVals_tbl_type;
4427 l_tag_id VARCHAR2(30);
4428 l_sr_id NUMBER;
4429 l_parent_ih_id NUMBER;
4430 l_customer_id NUMBER;
4431 l_contact_id NUMBER;
4432 l_lead_id NUMBER;
4433 l_folder_name VARCHAR2(255);
4434 l_ih_creator VARCHAR2(1);
4435
4436 BEGIN
4437
4438 -- Standard Start of API savepoint
4439 SAVEPOINT createRTItem_pvt;
4440
4441 -- Init vars
4442 l_api_name :='createRTItem';
4443 l_api_version_number :=1.0;
4444 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4445 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4446 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
4447 l_sr_id :=null;
4448 l_parent_ih_id :=null;
4449 l_customer_id :=null;
4450 l_contact_id :=null;
4451 l_lead_id :=null;
4452
4453 -- Standard call to check for call compatibility.
4454 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
4455 1.0,
4456 l_api_name,
4457 G_PKG_NAME)
4458 THEN
4459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4460 END IF;
4461
4462 -- Initialize message list if p_init_msg_list is set to TRUE.
4463 IF FND_API.to_Boolean( p_init_msg_list )
4464 THEN
4465 FND_MSG_PUB.initialize;
4466 END IF;
4467
4468 -- Initialize API return status to SUCCESS
4469 x_return_status := FND_API.G_RET_STS_SUCCESS;
4470
4471 -----------------------Code------------------------
4472
4473 -- Update IEM_RT_PROC_EMAILS with to_agent_id, from_agent_id and p_status
4474 IEM_MAILITEM_PUB.GetQueueItemData (p_api_version_number => 1.0,
4475 p_init_msg_list => 'F',
4476 p_commit => 'F',
4477 p_message_id => p_message_id,
4478 p_from_agent_id => p_from_resource_id,
4479 p_to_agent_id => p_to_resource_id,
4480 p_mail_item_status => p_status,
4481 x_email_data => l_email_data_rec,
4482 x_tag_key_value => l_tag_key_value_tbl,
4483 x_encrypted_id => l_tag_id,
4484 x_return_status => l_status,
4485 x_msg_count => l_msg_count,
4486 x_msg_data => l_msg_data);
4487
4488 -- Check return status; Proceed on success Or report back in case of error.
4489 IF (l_status = FND_API.G_RET_STS_SUCCESS) THEN
4490 -- Success.
4491 -- Get the name of the route classification from the ID returned above.
4492 -- This is the name of the folder where the inbound message exists on the
4493 -- master account.
4494 -- Changes for R12. Mark the folder as Inbox for autorouted case. bug
4495 -- 7428636 Ranjan 09/25/2008
4496 if p_reason='O' then
4497 l_folder_name:='Inbox';
4498 else
4499 SELECT name INTO l_folder_name
4500 FROM iem_route_classifications
4501 WHERE ROUTE_CLASSIFICATION_ID = l_email_data_rec.RT_CLASSIFICATION_ID;
4502 end if;
4503
4504 -- Extract tag key value from key value table
4505 -- Currently valid system key names:
4506 -- IEMNBZTSRVSRID for sr id
4507 -- IEMNINTERACTIONID for interaction id
4508 -- IEMNAGENTID for agent id
4509 -- IEMNCUSTOMERID for customer id
4510 -- IEMNCONTACTID for contact id
4511 -- IEMNBZSALELEADID for lead id
4512
4513 FOR i IN 1..l_tag_key_value_tbl.count LOOP
4514 BEGIN
4515 IF (l_tag_key_value_tbl(i).key = 'IEMNBZTSRVSRID' ) THEN
4516 l_sr_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
4517 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNINTERACTIONID' ) THEN
4518 l_parent_ih_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
4519 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNCUSTOMERID' ) THEN
4520 l_customer_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
4521 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNCONTACTID' ) THEN
4522 l_contact_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
4523 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNBZSALELEADID' ) THEN
4524 l_lead_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
4525
4526 END IF;
4527 END;
4528 END LOOP;
4529
4530 -- customer id and contact id from tagging supersede the result from
4531 -- email search (i.e. what are in l_email_date_rec)
4532 IF (l_customer_id is NULL) THEN
4533 BEGIN
4534 l_customer_id := l_email_data_rec.CUSTOMER_ID;
4535 l_contact_id := null;
4536 END;
4537 END IF;
4538
4539 -- Record details into the RT tables.
4540 l_ih_creator := null;
4541 if ( p_interaction_id is not null ) then
4542 l_ih_creator := 'Y';
4543 end if;
4544 select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
4545 INSERT INTO iem_rt_interactions (
4546 rt_interaction_id, resource_id, customer_id, contact_id, type,
4547 status, expire, created_by, creation_date, last_updated_by,
4548 last_update_date, last_update_login, parent_interaction_id,
4549 service_request_id, inb_tag_id, interaction_id, ih_creator,
4550 lead_id)
4551 VALUES (
4552 l_i_sequence, p_to_resource_id, l_customer_id, l_contact_id,
4553 G_INBOUND, G_WORK_IN_PROGRESS, G_ACTIVE, l_created_by,
4554 SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
4555 l_parent_ih_id, l_sr_id, l_tag_id, p_interaction_id, l_ih_creator,
4556 l_lead_id
4557 );
4558 -- db_server id used by mid-tier to locate accounts
4559 l_db_server_id := -1;
4560
4561 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
4562 INSERT INTO iem_rt_media_items (
4563 rt_interaction_id, rt_media_item_id, resource_id,
4564 media_id, message_id, rfc822_message_id, folder_name,
4565 folder_uid, email_account_id, db_server_id, email_type,
4566 status, expire, version, created_by, creation_date,
4567 last_updated_by, last_update_date, last_update_login )
4568 VALUES (
4569 l_i_sequence, l_m_sequence, p_to_resource_id,
4570 l_email_data_rec.IH_MEDIA_ITEM_ID,
4571 l_email_data_rec.MESSAGE_ID,
4572 null,
4573 l_folder_name,
4574 -1,
4575 l_email_data_rec.EMAIL_ACCOUNT_ID,
4576 l_db_server_id,
4577 G_INBOUND, UPPER(p_reason), G_ACTIVE,0, l_created_by, SYSDATE,
4578 l_last_updated_by, SYSDATE, l_last_update_login
4579 );
4580
4581
4582 -- Return Media Values to the JSPs.
4583 x_rt_media_item_id := l_m_sequence;
4584 x_rt_interaction_id := l_i_sequence;
4585
4586 ELSE
4587 -- Return the error returned by MDT API
4588 x_return_status := l_status;
4589 x_msg_count := l_msg_count;
4590 x_msg_data := l_msg_data;
4591
4592 END IF;
4593
4594 -------------------End Code------------------------
4595 -- Standard Check Of p_commit.
4596 IF FND_API.To_Boolean(p_commit) THEN
4597 COMMIT WORK;
4598 END IF;
4599 -- Standard callto get message count and if count is 1, get message info.
4600 -- FND_MSG_PUB.Count_And_Get
4601 -- ( p_encoded => FND_API.G_TRUE,
4602 -- p_count => x_msg_count,
4603 -- p_data => x_msg_data
4604 -- );
4605
4606 EXCEPTION
4607 WHEN FND_API.G_EXC_ERROR THEN
4608 ROLLBACK TO createRTItem_pvt;
4609 x_return_status := FND_API.G_RET_STS_ERROR ;
4610 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4611 p_count => x_msg_count,
4612 p_data => x_msg_data);
4613
4614 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4615 ROLLBACK TO createRTItem_pvt;
4616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4617 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4618 p_count => x_msg_count,
4619 p_data => x_msg_data);
4620
4621 WHEN OTHERS THEN
4622 ROLLBACK TO createRTItem_pvt;
4623 x_return_status := FND_API.G_RET_STS_ERROR;
4624 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4625 THEN
4626 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
4627 END IF;
4628 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
4629 p_count => x_msg_count,
4630 p_data => x_msg_data);
4631
4632 END createRTItem;
4633
4634 PROCEDURE isAgentInboxClean(p_api_version_number IN NUMBER,
4635 p_init_msg_list IN VARCHAR2,
4636 p_commit IN VARCHAR2,
4637 p_resource_id IN NUMBER,
4638 p_email_account_id IN NUMBER,
4639 x_is_clean OUT NOCOPY BOOLEAN,
4640 x_return_status OUT NOCOPY VARCHAR2,
4641 x_msg_count OUT NOCOPY NUMBER,
4642 x_msg_data OUT NOCOPY VARCHAR2
4643 ) IS
4644 l_api_name VARCHAR2(255);
4645 l_api_version_number NUMBER;
4646 l_created_by NUMBER;
4647 l_last_updated_by NUMBER;
4648 l_last_update_login NUMBER;
4649
4650 l_count1 NUMBER;
4651 l_count2 NUMBER;
4652 l_agent_account_id NUMBER;
4653 IEM_NO_AGENT_ACCT EXCEPTION;
4654 l_user_name VARCHAR2(100);
4655
4656 BEGIN
4657
4658 -- Standard Start of API savepoint
4659 SAVEPOINT isAgentInboxClean_pvt;
4660
4661 -- Init vars
4662 l_api_name :='isAgentInboxClean';
4663 l_api_version_number :=1.0;
4664 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4665 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4666 l_last_update_login :=NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
4667
4668 -- Standard call to check for call compatibility.
4669 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
4670 1.0,
4671 l_api_name,
4672 G_PKG_NAME)
4673 THEN
4674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4675 END IF;
4676
4677 -- Initialize message list if p_init_msg_list is set to TRUE.
4678 IF FND_API.to_Boolean( p_init_msg_list )
4679 THEN
4680 FND_MSG_PUB.initialize;
4681 END IF;
4682
4683 -- Initialize API return status to SUCCESS
4684 x_return_status := FND_API.G_RET_STS_SUCCESS;
4685
4686 -----------------------Code------------------------
4687 x_is_clean := false;
4688
4689 begin
4690 select agent_id into l_agent_account_id
4691 from iem_agents where email_account_id = p_email_account_id
4692 and resource_id = p_resource_id;
4693
4694 exception
4695 when others then
4696 -- find out the user_name
4697 begin
4698 select user_name into l_user_name from jtf_rs_resource_extns
4699 where resource_id = p_resource_id;
4700 exception
4701 when others then
4702 l_user_name := to_char(p_resource_id);
4703 end;
4704 raise IEM_NO_AGENT_ACCT;
4705 end;
4706
4707
4708 select count(rt_media_item_id) into l_count1 from iem_rt_media_items
4709 where agent_account_id=l_agent_account_id
4710 and rt_interaction_id
4711 in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE);
4712
4713 select count(rt_media_item_id) into l_count2 from iem_rt_media_items
4714 where resource_id= p_resource_id and email_account_id=p_email_account_id
4715 and rt_interaction_id
4716 in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE
4717 and type = 'I');
4718
4719 if ( (l_count1 + l_count2) = 0 ) then
4720 x_is_clean := true;
4721 end if;
4722 -------------------End Code------------------------
4723 -- Standard Check Of p_commit.
4724 IF FND_API.To_Boolean(p_commit) THEN
4725 COMMIT WORK;
4726 END IF;
4727 -- Standard callto get message count and if count is 1, get message info.
4728 -- FND_MSG_PUB.Count_And_Get
4729 -- ( p_encoded => FND_API.G_TRUE,
4730 -- p_count => x_msg_count,
4731 -- p_data => x_msg_data
4732 -- );
4733
4734 EXCEPTION
4735 WHEN IEM_NO_AGENT_ACCT THEN
4736 ROLLBACK TO isAgentInboxClean_pvt;
4737 x_return_status := FND_API.G_RET_STS_ERROR ;
4738 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_AGENT_ACCT');
4739 FND_MESSAGE.SET_TOKEN('ARG1', l_user_name);
4740 FND_MESSAGE.SET_TOKEN('ARG2', to_char(p_email_account_id));
4741 FND_MSG_PUB.ADD;
4742 FND_MSG_PUB.Count_And_Get(
4743 p_encoded => FND_API.G_TRUE,
4744 p_count => x_msg_count,
4745 p_data => x_msg_data);
4746
4747 WHEN FND_API.G_EXC_ERROR THEN
4748 ROLLBACK TO isAgentInboxClean_pvt;
4749 x_return_status := FND_API.G_RET_STS_ERROR ;
4750 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4751 p_count => x_msg_count,
4752 p_data => x_msg_data);
4753
4754 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4755 ROLLBACK TO isAgentInboxClean_pvt;
4756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4757 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4758 p_count => x_msg_count,
4759 p_data => x_msg_data);
4760
4761 WHEN OTHERS THEN
4762 ROLLBACK TO isAgentInboxClean_pvt;
4763 x_return_status := FND_API.G_RET_STS_ERROR;
4764 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4765 THEN
4766 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
4767 END IF;
4768 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
4769 p_count => x_msg_count,
4770 p_data => x_msg_data);
4771
4772 END isAgentInboxClean;
4773
4774 PROCEDURE updateOutboundMessageID(p_api_version_number IN NUMBER,
4775 p_init_msg_list IN VARCHAR2,
4776 p_commit IN VARCHAR2,
4777 p_rt_media_item_id IN NUMBER,
4778 p_message_id IN NUMBER,
4779 x_return_status OUT NOCOPY VARCHAR2,
4780 x_msg_count OUT NOCOPY NUMBER,
4781 x_msg_data OUT NOCOPY VARCHAR2
4782 ) IS
4783 l_api_name VARCHAR2(255);
4784 l_api_version_number NUMBER;
4785 l_created_by NUMBER;
4786 l_last_updated_by NUMBER;
4787 l_last_update_login NUMBER;
4788
4789 BEGIN
4790
4791 -- Standard Start of API savepoint
4792 SAVEPOINT updateOutboundMessageID_pvt;
4793
4794 -- Init vars
4795 l_api_name :='updateOutboundMessageID';
4796 l_api_version_number :=1.0;
4797 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4798 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4799 l_last_update_login :=NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
4800
4801 -- Standard call to check for call compatibility.
4802 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
4803 1.0,
4804 l_api_name,
4805 G_PKG_NAME)
4806 THEN
4807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4808 END IF;
4809
4810 -- Initialize message list if p_init_msg_list is set to TRUE.
4811 IF FND_API.to_Boolean( p_init_msg_list )
4812 THEN
4813 FND_MSG_PUB.initialize;
4814 END IF;
4815
4816 -- Initialize API return status to SUCCESS
4817 x_return_status := FND_API.G_RET_STS_SUCCESS;
4818
4819 -----------------------Code------------------------
4820
4821 begin
4822
4823 update iem_rt_media_items
4824 set message_id = p_message_id
4825 where rt_media_item_id = p_rt_media_item_id;
4826
4827 end;
4828
4829 -------------------End Code------------------------
4830 -- Standard Check Of p_commit.
4831 IF FND_API.To_Boolean(p_commit) THEN
4832 COMMIT WORK;
4833 END IF;
4834 -- Standard callto get message count and if count is 1, get message info.
4835 -- FND_MSG_PUB.Count_And_Get
4836 -- ( p_encoded => FND_API.G_TRUE,
4837 -- p_count => x_msg_count,
4838 -- p_data => x_msg_data
4839 -- );
4840
4841 EXCEPTION
4842 WHEN FND_API.G_EXC_ERROR THEN
4843 ROLLBACK TO updateOutboundMessageID_pvt;
4844 x_return_status := FND_API.G_RET_STS_ERROR ;
4845 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4846 p_count => x_msg_count,
4847 p_data => x_msg_data);
4848
4849 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4850 ROLLBACK TO updateOutboundMessageID_pvt;
4851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4852 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4853 p_count => x_msg_count,
4854 p_data => x_msg_data);
4855
4856 WHEN OTHERS THEN
4857 ROLLBACK TO updateOutboundMessageID_pvt;
4858 x_return_status := FND_API.G_RET_STS_ERROR;
4859 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4860 THEN
4861 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
4862 END IF;
4863 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
4864 p_count => x_msg_count,
4865 p_data => x_msg_data);
4866
4867 END updateOutboundMessageID;
4868
4869 END IEM_CLIENT_PUB;