[Home] [Help]
PACKAGE BODY: APPS.IEM_CLIENT_PUB
Source
4 -- PACKAGE CONSTANTS NO LITERALS USED.
1 PACKAGE BODY IEM_CLIENT_PUB as
2 /* $Header: iempcltb.pls 120.20.12020000.3 2012/09/05 06:18:18 shramana ship $*/
3
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.
132 p_resource_id => p_resource_id ,
129 IEM_MAILITEM_PUB.GetMailItem( p_api_version_number=> 1.0,
130 p_init_msg_list => 'F',
131 p_commit => 'F',
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;
247 x_rfc822_message_id := null;
244 x_oes_id := l_db_server_id;
245 x_folder_uid := -1;
246 x_customer_id := l_customer_id;
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;
365 l_customer_id NUMBER;
362 l_rt_interaction_id NUMBER;
363 l_oes_id NUMBER;
364 l_message_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);
504 (UPPER(p_email_type) = G_OUTBOUND) AND
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
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);
619 l_rt_interaction_id NUMBER;
616 l_status VARCHAR2(3);
617 l_version NUMBER;
618 l_rt_media_item_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
761 and is discovered during a search.
758 END getSearchDetails;
759
760 /* Create RT, MDT and IH data for an email that arrives in the agents inbox
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,
877 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
874 l_api_name,
875 G_PKG_NAME)
876 THEN
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
1005 l_api_version_number :=1.0;
1002 SAVEPOINT createMediaDetails_pvt;
1003 -- Init vars
1004 l_api_name :='createMediaDetails';
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,
1256 p_account_type IN VARCHAR2,
1253 p_folder_name IN VARCHAR2,
1254 p_folder_uid IN NUMBER,
1255 p_account_id IN NUMBER,
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;
1383 l_email_account_id := p_account_id;
1380 l_agent_account_id := p_account_id;
1381 elsif (p_account_type = G_MASTER_ACCOUNT) then
1382 l_agent_account_id := null;
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),
1513 SERVICE_REQUEST_ACTION = decode(p_service_request_action, G_CHAR_NOP, service_request_action, p_service_request_action),
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),
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.
1636 WHERE rt_interaction_id = l_rt_interaction_id;
1633 if (p_status = 'C') then
1634
1635 SELECT type INTO l_type FROM iem_rt_interactions
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
1773 -- Check if we already have an Interaction opened, if so return it
1770 x_return_status := FND_API.G_RET_STS_SUCCESS;
1771
1772 -----------------------Code------------------------
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
1907 p_action IN VARCHAR2,
1904 PROCEDURE wrapUp (p_api_version_number IN NUMBER,
1905 p_init_msg_list IN VARCHAR2,
1906 p_commit 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 -- added for bug 11737525
1951 l_old_party_id NUMBER;
1952 --Emp Search. Shramana
1953 l_old_contact_id NUMBER;
1954 l_rt_interaction_id NUMBER;
1955 l_interaction_id NUMBER;
1956 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
1957 l_customer_id NUMBER;
1958 l_resource_id NUMBER;
1959 l_start_date DATE;
1960 l_ob_media_id NUMBER;
1961 l_ib_media_id NUMBER;
1962 l_rfc822_message_id VARCHAR2(300);
1963 l_creation_date DATE;
1964 l_media_lc_rec JTF_IH_PUB.media_lc_rec_type;
1965 l_media_rec JTF_IH_PUB.media_rec_type;
1966 x_milcs_id NUMBER;
1967 x_activity_id_i NUMBER;
1968 x_activity_id_o NUMBER;
1969 l_activity_rec JTF_IH_PUB.activity_rec_type;
1970 l_mdt_message_id NUMBER;
1971 l_session_id NUMBER;
1972 l_activity_id NUMBER;
1973 l_count NUMBER;
1974 l_data VARCHAR2(300);
1975 l_ret_status VARCHAR2(300);
1976 l_activity_type VARCHAR2(200);
1977 l_email_account_id NUMBER;
1978 l_agent_account_id NUMBER;
1979 l_pureOb NUMBER; -- 0 pure ob; 1 relpy to an ib.
1980
1981 l_sr_id NUMBER;
1982 l_lead_id NUMBER;
1983 l_sr_action VARCHAR2(8);
1984 l_action_item_id NUMBER;
1985 l_action_id NUMBER;
1986 l_action_id_sr NUMBER;
1987 l_ih_creator VARCHAR2(1);
1988 l_ob_action_id NUMBER;
1989 l_parent_ih_id NUMBER;
1990
1991 l_email_type VARCHAR2(1);
1992 l_the_rt_media_item_id NUMBER;
1993
1994 m_rt_media_item_id number;
1995 l_reroute_to_acct number;
1996 l_reroute_to_classi number;
1997 l_reroute_to_folder varchar2(255);
1998 m_uid number;
1999 m_reroute_type number;
2000 IEM_BAD_REROUTE_CLASSI exception;
2001 IEM_BAD_REROUTE_TYPE exception;
2002 RerouteError exception;
2003 l_message_flag varchar2(1);
2004 l_contact_id number;
2005 l_autoReplied varchar2(1);
2006 l_outb_method number;
2007 l_to_resource_id number;
2008 l_uid number;
2009 IEM_REDIRECT_EX EXCEPTION;
2010 l_spv_resource_id number;
2011 l_to_group_id number;
2012 l_ih_contact_id number;
2013 l_party_type varchar2(20);
2014 l_relationship_id number;
2015 l_primary_customer_id number;
2016 l_ih_customer_id number;
2017 l_rt_ih_status varchar2(2);
2018 l_use_suggested number;
2019 l_ih_subject varchar2(80);
2020 l_reroute_resource_id number;
2021 l_outb_message_id number;
2022 l_mc_param_action varchar2(20);
2023 l_mc_parameter_id number;
2024 l_i_sequence number;
2025 l_m_sequence number;
2026 l_tran_lead_id number;
2027 l_tran_to_acct_id number;
2028
2029 --Employee Search. shramana.
2030 l_acct_type varchar2(1);
2031
2032 CURSOR sel_csr IS
2033 SELECT session_id from IEU_SH_SESSIONS
2034 WHERE BEGIN_DATE_TIME = (SELECT MAX(BEGIN_DATE_TIME)
2035 FROM IEU_SH_SESSIONS
2036 WHERE RESOURCE_ID = l_resource_id
2037 AND ACTIVE_FLAG = 'T'
2038 AND APPLICATION_ID = 680);
2039
2040 BEGIN
2041 -- Standard Start of API savepoint
2045 l_api_version_number :=1.0;
2042 SAVEPOINT wrapUp_pvt;
2043 -- Init vars
2044 l_api_name :='wrapUp';
2046 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
2047 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
2048 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
2049 l_ob_action_id := 0;
2050 l_reroute_to_classi := null;
2051 l_mc_param_action := ' ';
2052
2053 -- Standard call to check for call compatibility.
2054 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
2055 1.0,
2056 l_api_name,
2057 G_PKG_NAME)
2058 THEN
2059 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2060 END IF;
2061
2062 -- Initialize message list if p_init_msg_list is set to TRUE.
2063 IF FND_API.to_Boolean( p_init_msg_list )
2064 THEN
2065 FND_MSG_PUB.initialize;
2066 END IF;
2067
2068 -- Initialize API return status to SUCCESS
2069 x_return_status := FND_API.G_RET_STS_SUCCESS;
2070
2071 -----------------------Code------------------------
2072
2073
2074 --- truncate to 80 characters
2075 IF lengthb(p_subject)>80 then
2076 l_ih_subject:=substrb(p_subject,1,80);
2077 ELSE
2078 l_ih_subject:=p_subject;
2079 END IF;
2080
2081
2082 -- Get the values that are needed later.
2083 SELECT rt_interaction_id, agent_account_id, email_account_id
2084 INTO l_rt_interaction_id, l_agent_account_id, l_email_account_id
2085 FROM iem_rt_media_items
2086 WHERE rt_media_item_id = p_rt_media_item_id
2087 AND expire in (G_ACTIVE, G_QUEUEOUT)
2088 FOR UPDATE;
2089
2090 l_ib_media_id := null;
2091 l_mdt_message_id := null;
2092 BEGIN
2093 SELECT media_id, message_id, folder_uid
2094 INTO l_ib_media_id, l_mdt_message_id, l_uid
2095 FROM iem_rt_media_items
2096 WHERE rt_interaction_id = l_rt_interaction_id
2097 AND expire in (G_ACTIVE, G_QUEUEOUT)
2098 AND email_type = G_INBOUND;
2099 EXCEPTION
2100 WHEN OTHERS THEN
2101 NULL;
2102 END;
2103 --Employee Search. Shramana
2104 if (l_email_account_id is not null) then
2105 select account_type into l_acct_type
2106 from iem_mstemail_accounts
2107 where email_account_id = l_email_account_id;
2108 else
2109 select acct.account_type into l_acct_type
2110 from iem_mstemail_accounts acct, iem_agents agnt
2111 where agnt.agent_id = l_agent_account_id
2112 and agnt.email_account_id = acct.email_account_id;
2113 end if;
2114
2115 -- for redirect wrapup is just to call redirect API then expire the rt items.
2116 if (p_action = G_REDIRECT) then
2117 IEM_REROUTE_PUB.IEM_MAIL_REDIRECT_ACCOUNT(
2118 p_api_version_number =>1.0,
2119 p_init_msg_list => FND_API.G_FALSE,
2120 p_commit => FND_API.G_FALSE,
2121 p_msgid => l_mdt_message_id,
2122 p_email_account_id => l_email_account_id,
2123 p_uid => l_uid,
2124 x_msg_count => l_msg_count,
2125 x_return_status => l_status,
2126 x_msg_data => l_msg_data
2127 );
2128 if ( l_status = FND_API.G_RET_STS_ERROR ) then
2129 raise IEM_REDIRECT_EX;
2130 end if;
2131
2132 UPDATE iem_outbox_errors SET expire = G_EXPIRE
2133 WHERE rt_media_item_id = p_rt_media_item_id;
2134
2135 update iem_rt_interactions set expire = G_EXPIRE
2136 where rt_interaction_id = l_rt_interaction_id;
2137
2138 update iem_rt_media_items set expire = G_EXPIRE
2139 where rt_media_item_id = p_rt_media_item_id;
2140
2141 goto end_of_wrapup;
2142
2143 end if;
2144
2145
2146 BEGIN
2147 SELECT decode(interaction_id, -1, null, interaction_id),
2148 ih_creator, service_request_id, service_request_action,
2149 decode(parent_interaction_id, NULL, fnd_api.g_miss_num, parent_interaction_id),
2150 lead_id, nvl(action_id, -1), nvl(contact_id, -1), nvl(to_resource_id, -1),
2151 relationship_id, status, mc_parameter_id
2152 INTO l_interaction_id, l_ih_creator, l_sr_id, l_sr_action, l_parent_ih_id,
2153 l_lead_id, l_action_id, l_contact_id, l_to_resource_id, l_relationship_id,
2154 l_rt_ih_status, l_mc_parameter_id
2155 FROM iem_rt_interactions
2156 WHERE rt_interaction_id = l_rt_interaction_id
2157 AND expire in (G_ACTIVE, G_QUEUEOUT, G_PROCESSING);
2158 EXCEPTION
2159 WHEN OTHERS THEN
2160 NULL;
2161 END;
2162
2163 if (l_mc_parameter_id > 0) then
2164 select action into l_mc_param_action
2165 from iem_mc_parameters where mc_parameter_id = l_mc_parameter_id;
2166 end if;
2167
2168 -- set contact id for IH recording.
2169 l_ih_customer_id := p_customer_id;
2170
2171 select PARTY_TYPE into l_party_type from HZ_PARTIES
2172 where party_id = p_customer_id;
2173
2174 if ( l_party_type = 'PERSON' ) then
2175 l_primary_customer_id := l_ih_customer_id;
2176 l_ih_contact_id := l_ih_customer_id;
2177 l_relationship_id := null;
2178
2179 elsif ( l_party_type = 'ORGANIZATION') then
2180 l_primary_customer_id := l_ih_customer_id;
2181
2182 if ( l_contact_id > 0 ) then
2183 if ( l_relationship_id > 0 ) then
2184 l_ih_contact_id := l_contact_id;
2185 else
2186 l_ih_contact_id := null;
2190 l_ih_contact_id := null;
2187 l_relationship_id := null;
2188 end if;
2189 else
2191 l_relationship_id := null;
2192 end if;
2193
2194 else -- use old method PARTY_RELATIONSHIP
2195 l_primary_customer_id := null;
2196 l_ih_contact_id := null;
2197 l_relationship_id := null;
2198 end if;
2199 -- end of ih customer info
2200
2201 --Start changes for Employee Search. shramana
2202 if ( l_acct_type = 'I') then
2203 if (l_contact_id > 0) then
2204 l_ih_contact_id := l_contact_id;
2205 end if;
2206 end if;
2207 --end changes for emp search
2208
2209 if(l_ib_media_id > 0) then
2210 l_pureOb := 1; -- reply to a message
2211 else
2212 l_pureOb := 0; -- a pure ob message
2213 end if;
2214
2215 l_ob_media_id := null;
2216 BEGIN
2217 SELECT media_id, message_id, creation_date
2218 INTO l_ob_media_id, l_outb_message_id, l_creation_date
2219 FROM iem_rt_media_items
2220 WHERE rt_interaction_id = l_rt_interaction_id
2221 AND expire in (G_ACTIVE, G_QUEUEOUT)
2222 AND email_type = G_OUTBOUND;
2223 EXCEPTION
2224 WHEN OTHERS THEN
2225 NULL;
2226 END;
2227
2228 if (l_agent_account_id IS NOT NULL) then
2229 SELECT email_account_id
2230 INTO l_email_account_id
2231 FROM IEM_AGENTS
2232 WHERE agent_id = l_agent_account_id;
2233 end if;
2234
2235 SELECT customer_id, resource_id, creation_date, nvl(spv_resource_id, -1)
2236 INTO l_customer_id, l_resource_id, l_start_date, l_spv_resource_id
2237 FROM iem_rt_interactions
2238 WHERE rt_interaction_id = l_rt_interaction_id;
2239
2240 -- use supervisor resource id if exists
2241 l_reroute_resource_id := l_resource_id;
2242 if ( l_spv_resource_id > 0 ) then
2243 l_resource_id := l_spv_resource_id;
2244 end if;
2245
2246 select decode(p_activity_type_id,-1, NULL, p_activity_type_id) into l_activity_type_id from DUAL;
2247 select decode(p_result_id,-1, NULL, p_result_id) into l_result_id from DUAL;
2248 select decode(p_reason_id,-1, NULL, p_reason_id) into l_reason_id from DUAL;
2249
2250 IF (l_action_id < 0) THEN
2251 l_action_id := p_action_id;
2252 END IF;
2253
2254 IF (UPPER(p_action) = 'S') THEN
2255 if (l_pureOb = 0) then
2256 -- EMAIL_AUTO_ACK 83, EMAIL_FORWARD 84, EMAIL_FORWARD 85, EMAIL_RESEND 86
2257 if ( l_action_id = 83 ) then
2258 l_action_id_o := 29; -- EMAIL_ACKNOWLEDGED
2259 elsif ( l_action_id = 84 OR l_action_id = 85 OR l_action_id = 86) then
2260 l_action_id_o := 2; -- EMAIL_SENT (media life cycle segment)
2261 else
2262 l_action_id_o := 26; -- EMAIL_COMPOSE
2263 end if;
2264
2265 l_activity_type := 'EMAIL_COMPOSED';
2266 else
2267 begin
2268 if (l_action_id = 74) then
2269 l_action_id_i := 41; --EMAIL_AUTO_REPLY (media life cycle segment)
2270 else
2271 l_action_id_i := 19; -- EMAIL_REPLY
2272 end if;
2273 end;
2274
2275 l_action_id_o := 2; -- EMAIL_SENT (media life cycle segment)
2276 l_activity_type := 'EMAIL_RESPONDED';
2277 l_ob_action_id := 22;
2278 end if;
2279 ELSIF (UPPER(p_action) = 'D') THEN
2280 l_action_id_i := 6;
2281 l_action_id_o := 6;
2282 l_activity_type := 'EMAIL_DELETED';
2283 ELSIF (UPPER(p_action) = 'T') THEN
2284
2285 if (p_transfer_msg_flag = 'E') then
2286 l_action_id_i := 44; -- EMAIL_ESCALATED
2287 l_action_id_o := 44;
2288 else
2289 l_action_id_i := 7; -- EMAIL_TRANFERRED
2290 l_action_id_o := 7;
2291 end if;
2292
2293 l_activity_type := 'EMAIL_TRANSFERRED';
2294 --
2295 -- Reroute: action_id: new "Email Rerouted Diff Acct"
2296 -- action_id: new "Email Rerouted Diff Class"
2297 -- action_id: new "Email Requeued"
2298 -- Re-direct: action_id: new "Email Auto Redirected"
2299 -- Note: l_activity_type is for IEU activity.
2300 ELSIF (UPPER(p_action) = 'X') THEN
2301 BEGIN
2302 -- determine which type of reroute it is here:
2303 -- find the record with reroute info (to account id and to classification id)
2304
2305 l_reroute_to_acct := p_to_account_id;
2306 l_reroute_to_classi := p_to_classi_id;
2307 m_reroute_type := p_reroute_type;
2308
2309 -- Set action id based on reroute type
2310 if ( m_reroute_type = 78) then
2311 l_action_id_i := 37; --EMAIL_REROUTED_DIFF_ACCT
2312 l_action_id_o := 37;
2313 l_activity_type := 'EMAIL_REROUTED';
2314 elsif ( m_reroute_type = 77 ) then
2315 l_action_id_i := 38; --EMAIL_REROUTED_DIFF_CLASS
2316 l_action_id_o := 38;
2317 l_activity_type := 'EMAIL_REROUTED';
2318 elsif ( m_reroute_type = 76 ) then
2319 l_action_id_i := 39; --EMAIL_REQUEUED
2320 l_action_id_o := 39;
2321 l_activity_type := 'EMAIL_REROUTED';
2322 l_reroute_to_classi := null;
2323 else
2324 raise IEM_BAD_REROUTE_TYPE;
2325 end if;
2326 END;
2327 ELSIF (UPPER(p_action) = 'V') THEN -- For Email Resolve
2328 l_activity_type := 'EMAIL_RESPONDED';
2329 ELSE
2330 raise unrecognizedAction;
2331 END IF;
2332
2333 IF (l_interaction_id IS NULL) THEN
2334 l_ih_creator := 'Y';
2335
2336 l_interaction_rec.start_date_time := l_start_date;
2337 l_interaction_rec.end_date_time := SYSDATE;
2341 l_interaction_rec.contact_party_id := l_ih_contact_id;
2338 l_interaction_rec.resource_id := l_resource_id;
2339 l_interaction_rec.party_id := l_ih_customer_id;
2340 l_interaction_rec.primary_party_id := l_primary_customer_id;
2342 l_interaction_rec.contact_rel_party_id := l_relationship_id;
2343 l_interaction_rec.outcome_id := p_outcome_id;
2344 l_interaction_rec.result_id := l_result_id;
2345 l_interaction_rec.handler_id := 680; -- IEM APPL_ID
2346 l_interaction_rec.reason_id := l_reason_id;
2347 l_interaction_rec.parent_id := l_parent_ih_id;
2348
2349 JTF_IH_PUB.Open_Interaction( p_api_version => 1.1,
2350 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2351 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2352 p_user_id => l_created_by,
2353 p_login_id => l_last_update_login,
2354 x_return_status => l_status,
2355 x_msg_count => l_msg_count,
2356 x_msg_data => l_msg_data,
2357 x_interaction_id => l_interaction_id,
2358 p_interaction_rec => l_interaction_rec
2359 );
2360
2361 UPDATE iem_rt_interactions set IH_CREATOR = 'Y', interaction_id = l_interaction_id
2362 WHERE rt_interaction_id = l_rt_interaction_id;
2363
2364 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2365 raise IHError;
2366 end if;
2367 -- following ELSE added for 11737525 ,FP of bug 9791525
2368 ELSE --lkullamb interaction_id is present already. Not a pure outbound.Fix for bug 12746288, change jtf_ih_interactions_vl to jtf_ih_interactions
2369
2370 select party_id, contact_party_id into l_old_party_id, l_old_contact_id from jtf_ih_interactions where interaction_id = l_interaction_id;
2371
2372 if( (l_old_party_id <> p_customer_id) -- in case of customer change
2373 OR (l_old_contact_id is null AND l_ih_contact_id is not null) -- in case the contact has been added. Emp bin. Or from cust bin, another reln. record
2374 OR (l_old_contact_id is not null AND l_ih_contact_id is null) -- in case from cust bin we remove the contact
2375 OR (l_old_contact_id <> l_ih_contact_id) ) -- change the employee from emp bin or contact from cust bin
2376 then
2377 l_interaction_rec.interaction_id:=l_interaction_id;
2378 l_interaction_rec.party_id := l_ih_customer_id;
2379 l_interaction_rec.primary_party_id := l_primary_customer_id;
2380 l_interaction_rec.contact_party_id := l_ih_contact_id;
2381 l_interaction_rec.contact_rel_party_id := l_relationship_id;
2382 --Update interaction here. Since customer details have changed
2383 JTF_IH_PUB.Update_Interaction( p_api_version => 1.1,
2384 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2385 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2386 p_user_id => l_created_by,
2387 p_login_id => l_last_update_login,
2388 x_return_status => l_status,
2389 x_msg_count => l_msg_count,
2390 x_msg_data => l_msg_data,
2391 p_interaction_rec => l_interaction_rec
2392 );
2393 end if;
2394 --end of changes for 11737525
2395 END IF;
2396
2397 /* Ranjan
2398 --set doc_id and doc_type
2399
2400 IF NOT (l_sr_id IS NULL) THEN
2401 l_activity_rec.doc_id := l_sr_id;
2402 l_activity_rec.doc_ref := 'SR';
2403 END IF;
2404
2405 IF ( l_action_id = 84 OR l_action_id =85 OR l_action_id =86) THEN
2406 -- resend/forward/re-reply need to copy doc_id and doc_ref from parent
2407 begin
2408 SELECT doc_id, doc_ref into l_activity_rec.doc_id, l_activity_rec.doc_ref
2409 FROM JTF_IH_ACTIVITIES WHERE INTERACTION_ID = l_parent_ih_id
2410 AND ACTION_ID in (22,30,31,33,65,72,74);
2411 exception
2412 when others then
2413 null;
2414 end;
2415 END IF;
2416
2417 end of comment Ranjan*/
2418
2419 -- creat o/b media item if one does not exist.
2420 if (l_ob_media_id IS NULL) then
2421 l_media_rec.direction := G_O_DIRECTION;
2422 l_media_rec.source_id := l_email_account_id;
2423 l_media_rec.start_date_time := l_creation_date;
2424 l_media_rec.media_item_type := G_MEDIA_TYPE;
2425 l_media_rec.media_item_ref := l_outb_message_id;
2426 l_media_rec.media_data := l_ih_subject;
2427
2428 JTF_IH_PUB.Open_MediaItem(p_api_version => 1.0,
2429 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2430 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2431 p_user_id => l_created_by,
2432 p_login_id => l_last_update_login,
2433 x_return_status => l_status,
2434 x_msg_count => l_msg_count,
2435 x_msg_data => l_msg_data,
2436 p_media_rec => l_media_rec,
2437 x_media_id => l_ob_media_id
2438 );
2439 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2440 raise IHError;
2441 end if;
2442
2443 end if;
2444
2445 -- Add MLCS o/b
2446 -- Add MLCS only for action not equals to 'Resolve ' p_action!='V' Ranjan 10/31/2007
2450 l_media_lc_rec.media_id := l_ob_media_id;
2447 if p_action<>'V' then -- It is not a resolve
2448 l_media_lc_rec.start_date_time := SYSDATE;
2449 l_media_lc_rec.end_date_time := SYSDATE;
2451 l_media_lc_rec.milcs_type_id := l_action_id_o;
2452 l_media_lc_rec.resource_id := l_resource_id;
2453 l_media_lc_rec.handler_id := 680;
2454 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
2455 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2456 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2457 p_user_id => l_created_by,
2458 p_login_id => l_last_update_login,
2459 x_return_status => l_status,
2460 x_msg_count => l_msg_count,
2461 x_msg_data => l_msg_data,
2462 x_milcs_id => x_milcs_id,
2463 p_media_lc_rec => l_media_lc_rec);
2464
2465 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2466 raise IHError;
2467 end if;
2468 end if;
2469 -- close o/b
2470 l_media_rec.media_id := l_ob_media_id;
2471 l_media_rec.direction := G_O_DIRECTION;
2472 l_media_rec.media_item_type := G_MEDIA_TYPE;
2473 l_media_rec.media_item_ref := l_outb_message_id;
2474 l_media_rec.media_data := l_ih_subject;
2475 l_media_rec.address := p_to_address;
2476
2477 JTF_IH_PUB.Close_MediaItem(p_api_version => 1.0,
2478 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2479 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2480 p_user_id => l_created_by,
2481 p_login_id => l_last_update_login,
2482 x_return_status => l_status,
2483 x_msg_count => l_msg_count,
2484 x_msg_data => l_msg_data,
2485 p_media_rec => l_media_rec
2486 );
2487
2488 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2489 raise IHError;
2490 end if;
2491
2492 -- Add MLCS only for action not equals to 'Resolve ' p_action!='V' Ranjan 10/31/2007
2493 if p_action<>'V' then -- It is not a resolve
2494 -- Add MLCS i/b inbound may not exist (pure o/b)
2495 if(l_ib_media_id > 0 AND l_mc_param_action <> 'srautonotification') then
2496 l_media_lc_rec.start_date_time := SYSDATE;
2497 l_media_lc_rec.end_date_time := SYSDATE;
2498 l_media_lc_rec.media_id := l_ib_media_id;
2499 l_media_lc_rec.milcs_type_id := l_action_id_i;
2500 l_media_lc_rec.resource_id := l_resource_id;
2501 l_media_lc_rec.handler_id := 680;
2502 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
2503 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2504 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2505 p_user_id => l_created_by,
2506 p_login_id => l_last_update_login,
2507 x_return_status => l_status,
2508 x_msg_count => l_msg_count,
2509 x_msg_data => l_msg_data,
2510 x_milcs_id => x_milcs_id,
2511 p_media_lc_rec => l_media_lc_rec);
2512 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2513 raise IHError;
2514 end if;
2515 end if;
2516 END IF ; -- End if for p_action<>'V' Ranjan 10/31/07
2517 --set doc_id and doc_type
2518
2519 IF NOT (l_sr_id IS NULL) THEN
2520 l_activity_rec.doc_id := l_sr_id;
2521 l_activity_rec.doc_ref := 'SR';
2522 END IF;
2523
2524 IF ( l_action_id = 84 OR l_action_id =85 OR l_action_id =86) THEN
2525 -- resend/forward/re-reply need to copy doc_id and doc_ref from parent
2526 begin
2527 SELECT doc_id, doc_ref into l_activity_rec.doc_id, l_activity_rec.doc_ref
2528 FROM JTF_IH_ACTIVITIES WHERE INTERACTION_ID = l_parent_ih_id
2529 AND ACTION_ID in (22,30,31,33,65,72,74);
2530 exception
2531 when others then
2532 null;
2533 end;
2534 END IF;
2535 -- Create Activity against primary media_id
2536 l_activity_rec.start_date_time := SYSDATE;
2537 l_activity_rec.end_date_time := SYSDATE;
2538 if (l_ib_media_id > 0 AND l_mc_param_action <> 'srautonotification') then
2539 l_activity_rec.media_id := l_ib_media_id;
2540 l_activity_rec.action_id := l_action_id;
2541 l_activity_rec.interaction_id := l_interaction_id;
2542 l_activity_rec.outcome_id := p_outcome_id;
2543 l_activity_rec.result_id := l_result_id;
2544 l_activity_rec.reason_id := l_reason_id;
2545 l_activity_rec.action_item_id := l_activity_type_id;
2546
2547
2548 JTF_IH_PUB.Add_Activity(p_api_version => 1.0,
2549 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2550 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2551 p_user_id => l_created_by,
2552 p_login_id => l_last_update_login,
2553 x_return_status => l_status,
2554 x_msg_count => l_msg_count,
2555 x_msg_data => l_msg_data,
2556 p_activity_rec => l_activity_rec,
2557 x_activity_id => x_activity_id_i
2558 );
2559
2560 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2561 raise IHError;
2562 end if;
2563 end if;
2564
2565 if ((l_ob_media_id IS NOT NULL) AND (p_action = 'S')) then
2566 -- Create Activity against outbound media_id, reply or a pure outbound, only if it is sent.
2567 -- Transfer and Deletes of OutBounds are not recorded.
2568 l_activity_rec.media_id := l_ob_media_id;
2569 l_activity_rec.interaction_id := l_interaction_id;
2570 l_activity_rec.outcome_id := p_outcome_id;
2571 l_activity_rec.result_id := l_result_id;
2572 l_activity_rec.reason_id := l_reason_id;
2573 l_activity_rec.action_item_id := l_activity_type_id;
2574
2575 IF (l_ob_action_id <> 0) THEN
2576 l_activity_rec.action_id := l_ob_action_id;
2577 ELSE
2578 l_activity_rec.action_id := l_action_id;
2579 END IF;
2580
2581 JTF_IH_PUB.Add_Activity(p_api_version => 1.0,
2582 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2583 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2584 p_user_id => l_created_by,
2585 p_login_id => l_last_update_login,
2586 x_return_status => l_status,
2587 x_msg_count => l_msg_count,
2588 x_msg_data => l_msg_data,
2589 p_activity_rec => l_activity_rec,
2590 x_activity_id => x_activity_id_o
2591 );
2592
2593 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2594 raise IHError;
2595 end if;
2596 end if;
2597
2598
2599 --create Activity for SR
2600 IF NOT (l_sr_action IS NULL) THEN
2601 l_action_item_id := 17;
2602 -- Added l_activity_rec.doc_source_object_name for bug 9169782
2603 -- Changed by Sanjana Rao on 08-Jan-2010
2604 select incident_number into l_activity_rec.doc_source_object_name
2605 from cs_incidents_all_b where incident_id=l_sr_id;
2606
2607 IF (UPPER(l_sr_action) = 'CREATE') THEN
2608 l_action_id_sr := 13;
2609 END IF;
2610
2611 IF (UPPER(l_sr_action) = 'UPDATE') THEN
2612 l_action_id_sr := 14;
2613 END IF;
2614
2615 l_activity_rec.start_date_time := SYSDATE;
2616 l_activity_rec.end_date_time := SYSDATE;
2617 l_activity_rec.action_id := l_action_id_sr;
2618 l_activity_rec.interaction_id := l_interaction_id;
2619 l_activity_rec.outcome_id := p_outcome_id;
2620 l_activity_rec.result_id := l_result_id;
2621 l_activity_rec.reason_id := l_reason_id;
2622 l_activity_rec.action_item_id := l_action_item_id;
2623
2624 JTF_IH_PUB.Add_Activity(p_api_version => 1.0,
2625 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2626 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2627 p_user_id => l_created_by,
2628 p_login_id => l_last_update_login,
2629 x_return_status => l_status,
2630 x_msg_count => l_msg_count,
2631 x_msg_data => l_msg_data,
2632 p_activity_rec => l_activity_rec,
2633 x_activity_id => x_activity_id_o
2634 );
2635
2636 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2637 raise IHError;
2638 end if;
2639 END IF;
2640
2641 --create Activity for Lead
2642 IF (l_lead_id IS NOT NULL AND l_lead_id >= 0 AND l_lead_id <> 9999) THEN
2643
2644 -- Note: l_activity_rec.doc_id, if once SR, is changed to Lead
2645 -- So make sure activity for Lead is the last activity to add
2646 if ( l_lead_id = 0 ) then
2647 l_activity_rec.doc_id := null;
2648 l_activity_rec.doc_ref := null;
2649 l_activity_rec.doc_source_object_name := null;
2650 else
2651 l_activity_rec.doc_id := l_lead_id;
2652 l_activity_rec.doc_ref := 'LEAD';
2653 end if;
2654
2655
2656 l_activity_rec.start_date_time := SYSDATE;
2657 l_activity_rec.end_date_time := SYSDATE;
2658 l_activity_rec.action_id := 71; -- Request
2659 l_activity_rec.interaction_id := l_interaction_id;
2660 l_activity_rec.outcome_id := p_outcome_id;
2661 l_activity_rec.result_id := l_result_id;
2662 l_activity_rec.reason_id := l_reason_id;
2663 l_activity_rec.action_item_id := 8; -- lead;
2664
2665 JTF_IH_PUB.Add_Activity(p_api_version => 1.0,
2666 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2667 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2668 p_user_id => l_created_by,
2669 p_login_id => l_last_update_login,
2670 x_return_status => l_status,
2671 x_msg_count => l_msg_count,
2672 x_msg_data => l_msg_data,
2673 p_activity_rec => l_activity_rec,
2674 x_activity_id => x_activity_id_o
2675 );
2676
2677 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2678 raise IHError;
2679 end if;
2680 END IF;
2681
2682
2683
2684 -- Close IH
2685 l_interaction_rec.interaction_id := l_interaction_id;
2686 l_interaction_rec.end_date_time := SYSDATE;
2687 l_interaction_rec.resource_id := l_resource_id;
2688 l_interaction_rec.party_id := l_ih_customer_id;
2689 --commented by shams bug
2690 -- dont need any of these for closing interaction. We just need interaction id for closing
2691 -- l_interaction_rec.primary_party_id := l_primary_customer_id;
2692 -- l_interaction_rec.contact_party_id := l_ih_contact_id;
2693 -- l_interaction_rec.contact_rel_party_id := l_relationship_id;
2694 -- end of comment shams
2695 l_interaction_rec.outcome_id := p_outcome_id;
2696 l_interaction_rec.result_id := l_result_id;
2697 l_interaction_rec.handler_id := 680; -- IEM APPL_ID
2698 l_interaction_rec.reason_id := l_reason_id;
2699 -- done at creation l_interaction_rec.parent_id := l_parent_ih_id;
2700
2701 IF (l_ih_creator = 'Y' OR l_ih_creator = 'S' ) THEN
2702 JTF_IH_PUB.Close_Interaction(p_api_version => 1.1,
2703 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2704 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2705 p_user_id => l_created_by,
2706 p_login_id => l_last_update_login,
2707 x_return_status => l_status,
2708 x_msg_count => l_msg_count,
2709 x_msg_data => l_msg_data,
2710 p_interaction_rec => l_interaction_rec
2711 );
2712
2713 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2714 raise IHError;
2715 end if;
2716 END IF;
2717
2718 -- call server side reroute api
2719
2720 IF (UPPER(p_action) = 'X') THEN
2721 begin
2722 if ( m_reroute_type = 78 ) then
2723 IEM_REROUTE_PUB.IEM_MAIL_REROUTE_ACCOUNT( P_API_VERSION_NUMBER => 1.0,
2724 P_INIT_MSG_LIST => 'F',
2725 P_COMMIT => 'F',
2726 P_MSGID => l_mdt_message_id,
2727 P_AGENT_ID => l_reroute_resource_id,
2728 P_EMAIL_ACCOUNT_ID => p_to_account_id,
2729 P_INTERACTION_ID => l_interaction_id,
2730 P_UID => m_uid,
2731 X_MSG_COUNT => l_msg_count,
2732 X_RETURN_STATUS => l_status,
2733 X_MSG_DATA => l_msg_data);
2734 else
2735 if (m_reroute_type = 76) AND (l_to_resource_id > 0) then
2736 l_to_group_id := l_to_resource_id;
2737 else
2738 l_to_group_id := null;
2739 end if;
2740 IEM_REROUTE_PUB.IEM_MAIL_REROUTE_CLASS( P_API_VERSION_NUMBER => 1.0,
2741 P_INIT_MSG_LIST => 'F',
2742 P_COMMIT => 'F',
2743 P_MSGID => l_mdt_message_id,
2744 P_AGENT_ID => l_reroute_resource_id,
2745 P_CLASS_ID => l_reroute_to_classi,
2746 P_CUSTOMER_ID => p_customer_id,
2747 P_UID => m_uid,
2748 P_INTERACTION_ID => l_interaction_id,
2749 p_GROUP_ID => l_to_group_id,
2750 X_MSG_COUNT => l_msg_count,
2751 X_RETURN_STATUS => l_status,
2752 X_MSG_DATA => l_msg_data);
2753 end if;
2754
2755 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2756 raise RerouteError;
2757 end if;
2758 end;
2759 END IF;
2760
2761 -- update RTI: send, delete, reroute
2762 IF ((UPPER(p_action) = 'S') OR (UPPER(p_action) = 'D') OR (UPPER(p_action)='V') -- Add resolve
2763 OR (UPPER(p_action) = 'X')) THEN
2764 begin
2765 UPDATE iem_rt_interactions SET expire = G_EXPIRE
2766 WHERE rt_interaction_id = l_rt_interaction_id;
2767
2768 UPDATE iem_rt_media_items SET expire = G_EXPIRE
2769 WHERE rt_interaction_id = l_rt_interaction_id;
2770
2771 if ( (UPPER(p_action) <> 'X') AND l_mdt_message_id IS NOT NULL) then
2772 begin
2773 IEM_MAILITEM_PUB.DisposeMailItem (p_api_version_number => 1.0,
2774 p_init_msg_list =>'F' ,
2775 p_commit => 'F',
2776 p_message_id => l_mdt_message_id,
2777 x_return_status => l_status,
2781 when others then
2778 x_msg_count => l_msg_count,
2779 x_msg_data => l_msg_data);
2780 exception
2782 null;
2783 end;
2784 end if;
2785
2786 -- Reset queue_status to null is not really needed for once its
2787 -- agent id set to 0, queue_status is disregarded. But just play safe.
2788 if ( (UPPER(p_action) = 'X') AND (m_reroute_type = 76) ) then
2789 update IEM_RT_PROC_EMAILS set queue_status = null
2790 where message_id = l_mdt_message_id;
2791 end if;
2792 end;
2793 ELSIF (UPPER(p_action) = 'T') THEN
2794 begin
2795
2796 -- Need to create new transferee record here:
2797 -- Lead id to 9999 to prevent lead request activity being created every time transferred
2798 if (l_lead_id >= 0) then
2799 l_tran_lead_id := 9999;
2800 else
2801 l_tran_lead_id := -1;
2802 end if;
2803
2804 select agent_id into l_tran_to_acct_id from iem_agents
2805 where resource_id = p_to_resource_id
2806 and email_account_id = l_email_account_id;
2807
2808 select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
2809 INSERT INTO iem_rt_interactions (
2810 rt_interaction_id, resource_id, customer_id, type,
2811 status, expire, created_by, creation_date, last_updated_by,
2812 last_update_date, last_update_login, contact_id, inb_tag_id,
2813 lead_id, parent_interaction_id, service_request_id,
2814 relationship_id )
2815 SELECT l_i_sequence, p_to_resource_id, l_ih_customer_id, TYPE,
2816 G_WORK_IN_PROGRESS, G_ACTIVE, l_created_by, SYSDATE,
2817 l_last_updated_by, SYSDATE, l_last_update_login,
2818 l_contact_id, inb_tag_id, l_tran_lead_id,
2819 l_interaction_id, l_sr_id,
2820 l_relationship_id
2821 FROM iem_rt_interactions
2822 WHERE rt_interaction_id = l_rt_interaction_id;
2823
2824 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
2825 INSERT INTO iem_rt_media_items (
2826 rt_interaction_id, rt_media_item_id, resource_id,
2827 media_id, message_id, rfc822_message_id, folder_name,
2828 db_server_id, agent_account_id, email_type, status, expire, version,
2829 created_by, creation_date, last_updated_by, last_update_date,
2830 last_update_login, edit_mode )
2831 SELECT l_i_sequence, l_m_sequence, p_to_resource_id,
2832 MEDIA_ID, MESSAGE_ID, RFC822_MESSAGE_ID,
2833 folder_name, db_server_id, l_tran_to_acct_id, EMAIL_TYPE, 'R', G_ACTIVE,
2834 0, l_created_by, SYSDATE, l_last_updated_by, SYSDATE,
2835 l_last_update_login, decode(p_transfer_msg_flag, 'T', null, p_transfer_msg_flag)
2836 FROM iem_rt_media_items
2837 WHERE rt_media_item_id = p_rt_media_item_id;
2838
2839 -- Add MLCS i/b inbound for the second agent
2840 if(l_ib_media_id > 0 AND l_mc_param_action <> 'srautonotification') then
2841 l_media_lc_rec.start_date_time := SYSDATE;
2842 l_media_lc_rec.end_date_time := SYSDATE;
2843 l_media_lc_rec.media_id := l_ib_media_id;
2844 l_media_lc_rec.milcs_type_id := 21; -- EMAIL_TRANSFER (should be transfer_to, but not seeded)
2845 l_media_lc_rec.resource_id := p_to_resource_id;
2846 l_media_lc_rec.handler_id := 680;
2847 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
2848 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2849 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2850 p_user_id => l_created_by,
2851 p_login_id => l_last_update_login,
2852 x_return_status => l_status,
2853 x_msg_count => l_msg_count,
2854 x_msg_data => l_msg_data,
2855 x_milcs_id => x_milcs_id,
2856 p_media_lc_rec => l_media_lc_rec);
2857 if(l_status <> FND_API.G_RET_STS_SUCCESS) then
2858 raise IHError;
2859 end if;
2860 end if; -- MLCS second agent
2861
2862 -- Transferrers RT Interaction and RT media are expired
2863 UPDATE iem_rt_interactions SET expire = G_EXPIRE
2864 WHERE rt_interaction_id = l_rt_interaction_id;
2865
2866 -- This expires both the inbound and any outbounds
2867 -- associated with the original message. Change
2868 -- this for co-operate
2869
2870 UPDATE iem_rt_media_items SET expire = G_EXPIRE
2871 WHERE rt_interaction_id = l_rt_interaction_id;
2872
2873 -- Update mdt. Set the new owner of mailItem and from_agent_id and status
2874 -- and open queue_status.
2875 UPDATE IEM_RT_PROC_EMAILS
2876 SET resource_id = p_to_resource_id,
2877 from_resource_id = l_reroute_resource_id,
2878 mail_item_status = 'T',
2879 queue_status = null,
2880 message_flag = p_transfer_msg_flag
2881 WHERE message_id = l_mdt_message_id;
2882
2883 end;
2884 END IF;
2885
2886 -- Record UWQ interaction.
2887 BEGIN
2888 FOR sel_rec in sel_csr LOOP
2889 l_session_id := sel_rec.session_id;
2890 exit;
2891 END LOOP;
2892 IEU_SH_PUB.UWQ_BEGIN_ACTIVITY(
2893 p_api_version => 1.0,
2894 P_INIT_MSG_LIST => 'F',
2895 P_COMMIT => 'F',
2896 p_session_id => l_session_id,
2897 p_activity_type_code => l_activity_type,
2898 P_MEDIA_TYPE_ID => null,
2899 P_MEDIA_ID => null,
2900 p_user_id => l_created_by,
2901 p_login_id => l_last_update_login,
2902 P_REASON_CODE => null,
2903 P_REQUEST_METHOD => null,
2904 P_REQUESTED_MEDIA_TYPE_ID => null,
2905 P_WORK_ITEM_TYPE_CODE => null,
2906 P_WORK_ITEM_PK_ID => null,
2907 p_end_activity_flag => 'Y',
2908 x_activity_id => l_activity_id,
2909 x_msg_count => l_count,
2910 x_msg_data => l_data,
2911 x_return_status => l_ret_status
2912 );
2913 EXCEPTION
2914 WHEN OTHERS THEN
2915 NULL;
2916 END;
2917
2918 -- Expire iem_outbox_errors records if any
2919 UPDATE iem_outbox_errors SET expire = G_EXPIRE
2920 WHERE rt_media_item_id in (SELECT rt_media_item_id
2921 FROM iem_rt_media_items WHERE rt_interaction_id = l_rt_interaction_id);
2922
2923 -- write statistics data
2924 -- IEM_OUTBOUND_METHODS values:
2925 -- 1001 AUTO_REPLY
2926 -- 1002 AUTO_SUGGEST used when a reply uses at least one suggested response
2927 -- 1003 MANUAL_REPLY used when a reply does NOT use any suggested responses
2928 -- 1004 NEW_COMPOSE used for "pure outbound" messages
2929
2930 if ( UPPER(p_action) = 'S') then
2931 if ( l_action_id = 74 ) then
2932 l_autoReplied := 'Y';
2933 l_outb_method := 1001;
2934 else
2935 l_autoReplied := 'N';
2936 if ( l_action_id = 33 ) then
2937 l_outb_method := 1004;
2938 else
2939 begin
2940 l_use_suggested := 0;
2941 select count(OUTBOUND_MSG_STATS_ID) into l_use_suggested
2942 from iem_outbound_msg_stats
2943 where media_id = l_ob_media_id
2944 and USES_SUGGESTIONS_Y_N = 'Y';
2945
2946 if ( l_use_suggested > 0 ) then
2947 l_outb_method := 1002;
2948 else
2949 l_outb_method := 1003;
2950 end if;
2951
2952 end;
2953 end if;
2954 end if;
2955
2956 if ( l_contact_id < 0 ) then
2957 l_contact_id := -1;
2958 end if;
2959
2960
2961 IEM_MSG_STAT_PUB.sendMSGStat(
2962 p_api_version_number => 1.0,
2963 p_init_msg_list => fnd_api.g_false,
2964 p_commit => fnd_api.g_false,
2965 p_outBoundMediaID => l_ob_media_id,
2966 p_inBoundMediaID => nvl(l_ib_media_id, -1),
2967 p_autoReplied => l_autoReplied,
2968 p_agentID => l_resource_id,
2969 p_outBoundMethod => l_outb_method,
2970 p_accountID => l_email_account_id,
2971 p_customerID => p_customer_id,
2972 p_contactID => l_contact_id,
2973 x_return_status => l_ret_status,
2974 x_msg_count => l_count,
2975 x_msg_data => l_data
2976 );
2977 end if;
2978
2979 if (UPPER(p_action) = 'D') then
2980 IEM_MSG_STAT_PUB.deleteMSGStat(
2981 p_api_version_number => 1.0,
2982 p_init_msg_list => fnd_api.g_false,
2983 p_commit => fnd_api.g_false,
2984 p_outBoundMediaID => l_ob_media_id,
2985 p_inBoundMediaID => nvl(l_ib_media_id, -1),
2986 x_return_status => l_ret_status,
2987 x_msg_count => l_count,
2988 x_msg_data => l_data
2989 );
2990 end if;
2991
2992 -------------------End Code------------------------
2993 <<end_of_wrapup>>
2994 -- Standard Check Of p_commit.
2995 IF FND_API.To_Boolean(p_commit) THEN
2996 COMMIT WORK;
2997 END IF;
2998
2999 EXCEPTION
3000 WHEN RerouteError THEN
3001 ROLLBACK TO wrapUp_pvt;
3002 x_return_status := l_status;
3003 x_msg_count := l_msg_count;
3004 x_msg_data := l_msg_data;
3005
3006 WHEN IEM_REDIRECT_EX THEN
3007 ROLLBACK TO wrapUp_pvt;
3008 x_return_status := l_status;
3009 x_msg_count := l_msg_count;
3010 x_msg_data := l_msg_data;
3011
3012 WHEN badResourceId THEN
3013 ROLLBACK TO wrapUp_pvt;
3014 x_return_status := l_status;
3018 p_count => x_msg_count,
3015 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_RESOURCE_ID');
3016 FND_MSG_PUB.ADD;
3017 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
3019 p_data => x_msg_data);
3020 WHEN IHError THEN
3021 ROLLBACK TO wrapUp_pvt;
3022 x_return_status := l_status;
3023 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
3024 p_count => x_msg_count,
3025 p_data => x_msg_data);
3026 WHEN RTError THEN
3027 ROLLBACK TO wrapUp_pvt;
3028 x_return_status := l_status;
3029 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
3030 p_count => x_msg_count,
3031 p_data => x_msg_data);
3032 WHEN MDTError THEN
3033 ROLLBACK TO wrapUp_pvt;
3034 x_return_status := l_status;
3035 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
3036 p_count => x_msg_count,
3037 p_data => x_msg_data);
3038 WHEN FND_API.G_EXC_ERROR THEN
3039 ROLLBACK TO wrapUp_pvt;
3040 x_return_status := FND_API.G_RET_STS_ERROR ;
3041 FND_MSG_PUB.Count_And_Get(
3042 p_encoded => FND_API.G_TRUE,
3043 p_count => x_msg_count,
3044 p_data => x_msg_data);
3045
3046 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3047 ROLLBACK TO wrapUp_pvt;
3048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3049 FND_MSG_PUB.Count_And_Get(
3050 p_encoded => FND_API.G_TRUE,
3051 p_count => x_msg_count,
3052 p_data => x_msg_data);
3053 WHEN OTHERS THEN
3054 ROLLBACK TO wrapUp_pvt;
3055 x_return_status := FND_API.G_RET_STS_ERROR;
3056 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3057 THEN
3058 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
3059 END IF;
3060 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
3061 p_count => x_msg_count,
3062 p_data => x_msg_data);
3063 END wrapUp;
3064
3065 PROCEDURE recoverCompose (p_api_version_number IN NUMBER,
3066 p_init_msg_list IN VARCHAR2,
3067 p_commit IN VARCHAR2,
3068 p_resource_id IN NUMBER,
3069 x_return_status OUT NOCOPY VARCHAR2,
3070 x_msg_count OUT NOCOPY NUMBER,
3071 x_msg_data OUT NOCOPY VARCHAR2,
3072 x_rfc822_message_id OUT NOCOPY VARCHAR2,
3073 x_account_id OUT NOCOPY NUMBER,
3074 x_account_type OUT NOCOPY VARCHAR2,
3075 x_email_type OUT NOCOPY VARCHAR2,
3076 x_status OUT NOCOPY VARCHAR2,
3077 x_version OUT NOCOPY NUMBER,
3078 x_rt_media_item_id OUT NOCOPY NUMBER,
3079 x_rt_interaction_id OUT NOCOPY NUMBER,
3080 x_oes_id OUT NOCOPY NUMBER,
3081 x_folder_name OUT NOCOPY VARCHAR2,
3082 x_folder_uid OUT NOCOPY NUMBER,
3083 x_customer_id OUT NOCOPY NUMBER
3084 ) IS
3085
3086 l_api_name VARCHAR2(255);
3087 l_api_version_number NUMBER;
3088 l_created_by NUMBER;
3089 l_last_updated_by NUMBER;
3090 l_last_update_login NUMBER;
3091
3092 l_status VARCHAR2(300);
3093 l_msg_count NUMBER;
3094 l_msg_data VARCHAR2(300);
3095
3096 l_email_account_id NUMBER;
3097 l_agent_account_id NUMBER;
3098
3099 badAccountType EXCEPTION;
3100
3101 CURSOR compose_recover_csr IS
3102 SELECT m.rfc822_message_id,m.email_account_id, m.agent_account_id,
3103 m.email_type, m.status, m.version, m.rt_interaction_id,
3104 m.db_server_id, m.rt_media_item_id, m.folder_name, m.folder_uid,
3105 i.customer_id
3106 FROM iem_rt_media_items m, iem_rt_interactions i
3107 WHERE i.TYPE = G_OUTBOUND
3108 AND i.RT_INTERACTION_ID = m.RT_INTERACTION_ID
3109 AND m.RESOURCE_ID = p_resource_id
3110 AND m.EMAIL_TYPE = G_OUTBOUND
3111 AND m.expire = G_ACTIVE
3112 ORDER BY m.rt_interaction_id;
3113
3114 BEGIN
3115
3116 -- Standard Start of API savepoint
3117 SAVEPOINT recoverCompose_pvt;
3118
3119 -- Init vars
3120 l_api_name :='recoverCompose';
3121 l_api_version_number :=1.0;
3122 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
3123 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
3124 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
3125
3126 -- Standard call to check for call compatibility.
3127 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3128 1.0,
3129 l_api_name,
3130 G_PKG_NAME)
3131 THEN
3132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3133 END IF;
3134
3135 -- Initialize message list if p_init_msg_list is set to TRUE.
3136 IF FND_API.to_Boolean( p_init_msg_list )
3137 THEN
3138 FND_MSG_PUB.initialize;
3139 END IF;
3140
3141 -- Initialize API return status to SUCCESS
3142 x_return_status := FND_API.G_RET_STS_SUCCESS;
3143
3144 -----------------------Code------------------------
3145 /* dbms_output.put_line('In getWork ');
3146 dbms_output.put_line('In getWork : Resource ID '||p_resource_id);
3147 */
3148
3149 -- No Message Found.
3150 x_return_status := 'N';
3151
3152 FOR cr_rec in compose_recover_csr LOOP
3153 if ((cr_rec.email_account_id IS NULL) AND (cr_rec.agent_account_id IS NOT NULL)) then
3154 x_account_id := cr_rec.agent_account_id;
3155 x_account_type := G_AGENT_ACCOUNT;
3156 elsif ((cr_rec.agent_account_id IS NULL) AND (cr_rec.email_account_id IS NOT NULL)) then
3157 x_account_id := cr_rec.email_account_id;
3158 x_account_type := G_MASTER_ACCOUNT;
3159 elsif ((cr_rec.agent_account_id IS NOT NULL) AND (cr_rec.email_account_id IS NOT NULL)) then
3160 x_account_id := cr_rec.agent_account_id;
3161 x_account_type := G_AGENT_ACCOUNT;
3162 else
3163 raise badAccountType;
3164 end if;
3165
3166 x_rfc822_message_id := cr_rec.RFC822_MESSAGE_ID;
3167 x_email_type := cr_rec.EMAIL_TYPE;
3168 x_status := cr_rec.STATUS;
3169 x_version := cr_rec.VERSION;
3170 x_rt_media_item_id := cr_rec.rt_media_item_id;
3171 x_rt_interaction_id := cr_rec.rt_interaction_id;
3172 x_oes_id := cr_rec.DB_SERVER_ID;
3173 x_folder_name := cr_rec.FOLDER_NAME;
3174 x_folder_uid := cr_rec.FOLDER_UID;
3175 x_customer_id := cr_rec.CUSTOMER_ID;
3176 x_return_status := FND_API.G_RET_STS_SUCCESS;
3177
3178 exit; -- get the first record.
3179
3180 END LOOP;
3181 -------------------End Code------------------------
3182
3183 EXCEPTION
3184 WHEN badAccountType THEN
3185 ROLLBACK TO recoverCompose_pvt;
3186 x_return_status := FND_API.G_RET_STS_ERROR ;
3187 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_ACCOUNT_TYPE');
3188 FND_MSG_PUB.ADD;
3189 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
3190 p_count => x_msg_count,
3191 p_data => x_msg_data);
3192 WHEN FND_API.G_EXC_ERROR THEN
3193 ROLLBACK TO recoverCompose_pvt;
3194 x_return_status := FND_API.G_RET_STS_ERROR ;
3195 FND_MSG_PUB.Count_And_Get(
3196 p_encoded => FND_API.G_TRUE,
3197 p_count => x_msg_count,
3198 p_data => x_msg_data);
3199
3200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3201 ROLLBACK TO recoverCompose_pvt;
3202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3203 FND_MSG_PUB.Count_And_Get(
3204 p_encoded => FND_API.G_TRUE,
3205 p_count => x_msg_count,
3206 p_data => x_msg_data);
3207
3208 WHEN OTHERS THEN
3209 ROLLBACK TO recoverCompose_pvt;
3210 x_return_status := FND_API.G_RET_STS_ERROR;
3211 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3212 THEN
3213 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
3214 END IF;
3215 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
3216 p_count => x_msg_count,
3217 p_data => x_msg_data);
3218 END recoverCompose;
3219
3220 /*
3221 PROCEDURE getAccountDelStatus(p_api_version_number IN NUMBER,
3222 p_init_msg_list IN VARCHAR2,
3223 p_commit IN VARCHAR2,
3224 p_account_id IN NUMBER,
3225 p_account_type IN VARCHAR2,
3226 x_status OUT NOCOPY NUMBER,
3227 x_return_status OUT NOCOPY VARCHAR2,
3231 l_api_name VARCHAR2(255);
3228 x_msg_count OUT NOCOPY NUMBER,
3229 x_msg_data OUT NOCOPY VARCHAR2 ) IS
3230
3232 l_api_version_number NUMBER;
3233 l_data NUMBER;
3234
3235 CURSOR del_status_csr IS
3236 SELECT agent_id
3237 FROM iem_agents
3238 WHERE email_account_id = p_account_id
3239 ORDER BY agent_id;
3240 BEGIN
3241
3242 -- Standard Start of API savepoint
3243 SAVEPOINT getAccountDelStatus_pvt;
3244
3245 -- Init vars
3246 l_api_name :='getAccountDelStatus';
3247 l_api_version_number :=1.0;
3248
3249 -- Standard call to check for call compatibility.
3250 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3251 p_api_version_number,
3252 l_api_name,
3253 G_PKG_NAME)
3254 THEN
3255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3256 END IF;
3257
3258 -- Initialize message list if p_init_msg_list is set to TRUE.
3259 IF FND_API.to_Boolean( p_init_msg_list )
3260 THEN
3261 FND_MSG_PUB.initialize;
3262 END IF;
3263
3264 -- Initialize API return status to SUCCESS
3265 x_return_status := FND_API.G_RET_STS_SUCCESS;
3266
3267 ----------------------------------------CODE-----------------------------------
3268 l_data := 0;
3269 x_status := 0;
3270
3271 IF (UPPER(p_account_type) = G_AGENT_ACCOUNT) THEN
3272 SELECT count(*) into l_data FROM iem_rt_media_items
3273 WHERE agent_account_id = p_account_id
3274 AND expire = G_ACTIVE;
3275
3276 if (l_data = 0) then
3277 x_status := 0;
3278 else
3279 x_status := 1;
3280 end if;
3281 ELSE
3282 x_status := 0;
3283 FOR cr_rec in del_status_csr LOOP
3284 SELECT count(*) into l_data FROM iem_rt_media_items
3285 WHERE agent_account_id = cr_rec.agent_account_id
3286 AND expire = G_ACTIVE;
3287
3288 if (l_data = 0) then
3289 x_status := 0;
3290 else
3291 x_status := 1;
3292 exit;
3293 end if;
3294 END LOOP;
3295 END IF;
3296 ----------------------------------------CODE-----------------------------------
3297 -- Standard Check Of p_commit.
3298 IF FND_API.To_Boolean(p_commit) THEN
3299 COMMIT WORK;
3300 END IF;
3301 -- Standard callto get message count and if count is 1, get message info.
3302 FND_MSG_PUB.Count_And_Get
3303 ( p_encoded => FND_API.G_TRUE,
3304 p_count => x_msg_count,
3305 p_data => x_msg_data
3306 );
3307 EXCEPTION
3308 WHEN FND_API.G_EXC_ERROR THEN
3309 ROLLBACK TO getAccountDelStatus_pvt;
3310 x_return_status := FND_API.G_RET_STS_ERROR ;
3311 FND_MSG_PUB.Count_And_Get
3312 ( p_encoded => FND_API.G_TRUE,
3313 p_count => x_msg_count,
3314 p_data => x_msg_data
3315 );
3316 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3317 ROLLBACK TO getAccountDelStatus_pvt;
3318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3319 FND_MSG_PUB.Count_And_Get
3320 ( p_encoded => FND_API.G_TRUE,
3321 p_count => x_msg_count,
3322 p_data => x_msg_data
3323 );
3324 WHEN OTHERS THEN
3325 ROLLBACK TO getAccountDelStatus_pvt;
3326 x_return_status := FND_API.G_RET_STS_ERROR;
3327 IF FND_MSG_PUB.Check_Msg_Level
3328 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3329 THEN
3330 FND_MSG_PUB.Add_Exc_Msg
3331 ( G_PKG_NAME ,
3332 l_api_name
3333 );
3334 END IF;
3335 FND_MSG_PUB.Count_And_Get
3336 ( p_encoded => FND_API.G_TRUE,
3337 p_count => x_msg_count ,
3338 p_data => x_msg_data
3339 );
3340
3341 end getAccountDelStatus;
3342 */
3343
3344
3345 PROCEDURE purgeOutbound (p_api_version_number IN NUMBER,
3346 p_init_msg_list IN VARCHAR2,
3347 p_commit IN VARCHAR2,
3348 x_return_status OUT NOCOPY VARCHAR2,
3349 x_msg_count OUT NOCOPY NUMBER,
3350 x_msg_data OUT NOCOPY VARCHAR2
3351 ) IS
3352
3353 l_api_name VARCHAR2(255);
3354 l_api_version_number NUMBER;
3355 l_created_by NUMBER;
3356 l_last_update_login NUMBER;
3357
3358 l_status VARCHAR2(300);
3359 l_msg_count NUMBER;
3360 l_msg_data VARCHAR2(300);
3361
3362 l_rt_interaction_id NUMBER;
3363 l_media_id NUMBER;
3364 l_mc_parameter_id NUMBER;
3365 l_customer_id NUMBER;
3366 l_resource_id NUMBER;
3367 l_RT_MEDIA_ITEM_ID NUMBER;
3368
3369 l_tmp_ref_key varchar2(200);
3370 l_tmp_ref_name varchar2(200);
3371
3372 l_profile_value varchar2(200);
3373 l_rfc822_message_id VARCHAR2(255);
3374 l_email_type VARCHAR2(1);
3375 l_version NUMBER;
3376 l_media_rec JTF_IH_PUB.MEDIA_REC_TYPE;
3377 i NUMBER;
3378 l_message_id NUMBER;
3379
3380 CURSOR del_rt_csr IS
3381 SELECT iem_rt_interactions.RT_INTERACTION_ID
3382 FROM iem_rt_interactions, iem_rt_media_items
3383 WHERE iem_rt_interactions.expire='N' and iem_rt_interactions.type = 'O'
3384 AND SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30
3385 AND iem_rt_media_items.RT_INTERACTION_ID = iem_rt_interactions.RT_INTERACTION_ID;
3389
3386
3387 CURSOR del_rt_expire_csr IS
3388 SELECT RT_MEDIA_ITEM_ID FROM iem_rt_media_items WHERE expire= 'Y';
3390 BEGIN
3391
3392 -- Standard Start of API savepoint
3393 SAVEPOINT purgeOutbound_pvt;
3394
3395 -- Init vars
3396 l_api_name :='purgeOutbound';
3397 l_api_version_number :=1.0;
3398 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
3399 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
3400
3401 -- Standard call to check for call compatibility.
3402 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3403 1.0,
3404 l_api_name,
3405 G_PKG_NAME)
3406 THEN
3407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3408 END IF;
3409
3410 -- Initialize message list if p_init_msg_list is set to TRUE.
3411 IF FND_API.to_Boolean( p_init_msg_list )
3412 THEN
3413 FND_MSG_PUB.initialize;
3414 END IF;
3415
3416 -- Initialize API return status to SUCCESS
3417 x_return_status := FND_API.G_RET_STS_SUCCESS;
3418
3419 -----------------------Code------------------------
3420 BEGIN
3421
3422 -- Clean Iem_msg_parts table
3423 FOR rt_expire_csr_rec in del_rt_expire_csr LOOP
3424 /* Commented due to perf issue bug 6875851 03/13/2008 Ranjan
3425 l_tmp_ref_key := rt_expire_csr_rec.RT_MEDIA_ITEM_ID;
3426
3427 LOOP
3428 l_tmp_ref_name := NULL;
3429
3430 BEGIN
3431 select PART_NAME into l_tmp_ref_name from iem_msg_parts where to_char(ref_key) = l_tmp_ref_key
3432 and PART_TYPE = 'ATTACHMAIL' and rownum < 2;
3433 EXCEPTION WHEN NO_DATA_FOUND THEN
3434 EXIT;
3435 END;
3436
3437
3438 if (l_tmp_ref_name IS NOT NULL) THEN
3439 l_tmp_ref_key := l_tmp_ref_name;
3440 update iem_msg_parts set DELETE_FLAG = 'Y' where to_char(REF_KEY) = l_tmp_ref_key;
3441 END IF;
3442
3443 END LOOP;
3444 modifed the query as below after the comment section
3445 */
3446 update iem_msg_parts
3447 set delete_flag='Y'
3448 where ref_key in (select part_name from iem_msg_parts where
3449 ref_key=rt_expire_csr_rec.RT_MEDIA_ITEM_ID and part_type='ATTACHMAIL');
3450
3451 END LOOP;
3452
3453 -- delete outbox errors
3454 delete from iem_outbox_errors where expire = 'Y';
3455
3456 delete from iem_msg_parts where REF_KEY in
3457 (select rt_media_item_id from iem_rt_media_items WHERE expire='Y');
3458
3459 delete from iem_msg_parts where DELETE_FLAG = 'Y';
3460
3461 -- Delete IEM_MC_PARAMETERS, and IEM_MC_CUSTOM_PARAM
3462 delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3463 (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3464 from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3465 where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3466 and iem_rt_interactions.expire = 'Y');
3467
3468 delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3469 (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3470 from IEM_MC_PARAMETERS, iem_rt_interactions
3471 where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3472 and iem_rt_interactions.expire = 'Y');
3473
3474 -- Delete each record from iem_rt_interactions where expire = 'Y'
3475 delete from iem_rt_interactions where expire = 'Y';
3476
3477 -- Delete each record from iem_msg_datas where its reference at iem_rt_media_items has expire ='Y'.
3478 delete from iem_msg_datas where msg_key in
3479 (select msg_key from iem_rt_media_items, iem_msg_datas
3480 where iem_rt_media_items.rt_media_item_id = iem_msg_datas.msg_key
3481 and iem_rt_media_items.expire = 'Y');
3482
3483 -- Delete each record from iem_rt_media_items where expire = 'Y'.
3484 delete from iem_rt_media_items where expire = 'Y';
3485
3486 -- Delete each record from iem_agent_sessions where last_update_date is older than 30 days
3487 delete from iem_agent_sessions where SYSDATE - LAST_UPDATE_DATE > 30;
3488
3489
3490 -- Delete each record from IEM_MC_CUSTOM_PARAMS and IEM_MC_PARAMETERS where last_update_date is older than 30 days
3491
3492 delete from IEM_MC_CUSTOM_PARAMS where MC_PARAMETER_ID in
3493 (select IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID
3494 from IEM_MC_CUSTOM_PARAMS, iem_rt_interactions
3495 where IEM_MC_CUSTOM_PARAMS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3496 and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3497 SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3498
3499 delete from IEM_MC_PARAMETERS where MC_PARAMETER_ID in
3500 (select IEM_MC_PARAMETERS.MC_PARAMETER_ID
3501 from IEM_MC_PARAMETERS, iem_rt_interactions
3502 where IEM_MC_PARAMETERS.MC_PARAMETER_ID = iem_rt_interactions.MC_PARAMETER_ID
3503 and iem_rt_interactions.expire='N' and iem_rt_interactions.type='O' and
3504 SYSDATE - iem_rt_interactions.LAST_UPDATE_DATE > 30);
3505
3506 FOR cr_rec in del_rt_csr LOOP
3507 l_rt_interaction_id := cr_rec.RT_INTERACTION_ID;
3508
3509 select RT_MEDIA_ITEM_ID, media_id, email_type, message_id
3510 into l_RT_MEDIA_ITEM_ID, l_media_id, l_email_type, l_message_id
3511 from iem_rt_media_items
3512 where rt_interaction_id = l_rt_interaction_id;
3513
3514
3515 l_media_rec.media_id := l_media_id;
3516 l_media_rec.direction := G_O_DIRECTION;
3517 l_media_rec.media_item_type := G_MEDIA_TYPE;
3518 l_media_rec.media_item_ref := l_message_id;
3519
3520 IF (l_email_type = 'I') THEN
3521 l_media_rec.direction := G_I_DIRECTION;
3522 END IF;
3523
3524 JTF_IH_PUB.Close_MediaItem(p_api_version => 1.0,
3525 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
3526 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
3527 p_user_id => l_created_by,
3528 p_login_id => l_last_update_login,
3529 x_return_status => l_status,
3530 x_msg_count => l_msg_count,
3531 x_msg_data => l_msg_data,
3532 p_media_rec => l_media_rec
3533 );
3534
3535
3536 -- Clean Iem_msg_parts table
3537
3538 l_tmp_ref_key := l_RT_MEDIA_ITEM_ID;
3539
3540 /* Commented for perf issue as per bug 6875851
3541 FOR i in 1..50 LOOP
3542 BEGIN
3543 select PART_NAME into l_tmp_ref_name from iem_msg_parts where to_char(ref_key) = l_tmp_ref_key
3544 and PART_TYPE = 'ATTACHMAIL' and rownum < 2;
3545 EXCEPTION WHEN NO_DATA_FOUND THEN
3546 EXIT;
3547 END;
3548
3549
3550 if (l_tmp_ref_name IS NOT NULL) THEN
3551 l_tmp_ref_key := l_tmp_ref_name;
3552 update iem_msg_parts set DELETE_FLAG = 'Y' where to_char(REF_KEY) = l_tmp_ref_key;
3553 END IF;
3554
3555 END LOOP;
3556 modify the part as below.
3557 */
3558 update iem_msg_parts
3559 set delete_flag='Y'
3560 where ref_key in (select part_name from iem_msg_parts where
3561 ref_key=l_RT_MEDIA_ITEM_ID and part_type='ATTACHMAIL');
3562
3563 delete from iem_msg_parts where REF_KEY = l_RT_MEDIA_ITEM_ID;
3564 delete from iem_rt_media_items where RT_MEDIA_ITEM_ID = l_RT_MEDIA_ITEM_ID;
3565 delete from iem_rt_interactions where rt_interaction_id = l_rt_interaction_id;
3566
3567 END LOOP;
3568
3569 -- Clean anything left
3570 delete from iem_msg_parts where DELETE_FLAG = 'Y';
3571 delete from iem_rt_interactions WHERE expire='N' and type = 'O'
3572 AND SYSDATE - LAST_UPDATE_DATE > 30;
3573
3574
3575 EXCEPTION
3576 WHEN OTHERS THEN
3577 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3578 END;
3579
3580 -------------------End Code------------------------
3581 -- Standard Check Of p_commit.
3582 IF FND_API.To_Boolean(p_commit) THEN
3583 COMMIT WORK;
3584 END IF;
3585 -- Standard callto get message count and if count is 1, get message info.
3586 FND_MSG_PUB.Count_And_Get
3587 ( p_encoded => FND_API.G_TRUE,
3588 p_count => x_msg_count,
3589 p_data => x_msg_data
3590 );
3591 EXCEPTION
3592 WHEN FND_API.G_EXC_ERROR THEN
3593 ROLLBACK TO purgeOutbound_pvt;
3594 x_return_status := FND_API.G_RET_STS_ERROR ;
3595 FND_MSG_PUB.Count_And_Get(
3596 p_encoded => FND_API.G_TRUE,
3597 p_count => x_msg_count,
3598 p_data => x_msg_data);
3599
3600 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3601 ROLLBACK TO purgeOutbound_pvt;
3602 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3603 FND_MSG_PUB.Count_And_Get(
3604 p_encoded => FND_API.G_TRUE,
3605 p_count => x_msg_count,
3606 p_data => x_msg_data);
3607
3608 WHEN OTHERS THEN
3609 ROLLBACK TO purgeOutbound_pvt;
3610 x_return_status := FND_API.G_RET_STS_ERROR;
3611 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3612 THEN
3613 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
3614 END IF;
3615 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
3616 p_count => x_msg_count,
3617 p_data => x_msg_data);
3618 END purgeOutbound;
3619
3620
3621
3622 PROCEDURE assignMsg (p_api_version_number IN NUMBER,
3623 p_init_msg_list IN VARCHAR2,
3624 p_commit IN VARCHAR2,
3625 p_message_id IN NUMBER,
3626 p_to_resource_id IN NUMBER,
3627 p_from_resource_id IN NUMBER,
3628 x_return_status OUT NOCOPY VARCHAR2,
3629 x_msg_count OUT NOCOPY NUMBER,
3630 x_msg_data OUT NOCOPY VARCHAR2,
3631 x_rt_media_item_id OUT NOCOPY NUMBER,
3632 x_email_account_id OUT NOCOPY NUMBER,
3633 x_oes_id OUT NOCOPY NUMBER,
3634 x_folder_name OUT NOCOPY VARCHAR2,
3635 x_folder_uid OUT NOCOPY NUMBER,
3636 x_rt_interaction_id OUT NOCOPY NUMBER,
3637 x_customer_id OUT NOCOPY NUMBER,
3638 x_rfc822_message_id OUT NOCOPY VARCHAR2,
3639 x_route_classification OUT NOCOPY VARCHAR2,
3640 x_mdt_message_id OUT NOCOPY NUMBER,
3641 x_service_request_id OUT NOCOPY NUMBER,
3642 x_contact_id OUT NOCOPY NUMBER,
3643 x_lead_id OUT NOCOPY NUMBER,
3644 x_relationship_id OUT NOCOPY NUMBER
3645 ) IS
3646
3647 l_api_name VARCHAR2(255);
3648 l_api_version_number NUMBER;
3649 l_created_by NUMBER;
3650 l_last_updated_by NUMBER;
3651 l_last_update_login NUMBER;
3652
3653 l_email_data_rec IEM_RT_PROC_EMAILS%ROWTYPE;
3654 l_status VARCHAR2(300);
3655 l_msg_count NUMBER;
3656 l_msg_data VARCHAR2(300);
3657
3658 l_i_sequence NUMBER;
3659 l_m_sequence NUMBER;
3660 l_db_server_id NUMBER;
3661 l_classification_id NUMBER;
3662 l_tag_key_value_tbl IEM_MAILITEM_PUB.keyVals_tbl_type;
3663 l_tag_id VARCHAR2(30);
3664 l_sr_id NUMBER;
3665 l_parent_ih_id NUMBER;
3666 l_customer_id NUMBER;
3667 l_contact_id NUMBER;
3668 l_relationship_id NUMBER;
3669 l_lead_id NUMBER;
3670 l_media_lc_rec JTF_IH_PUB.media_lc_rec_type;
3671 l_milcs_id NUMBER;
3672 l_ih_creator VARCHAR2(1);
3673 l_mail_item_status VARCHAR2(1);
3674 --Emp search. shramana
3675 l_acct_type VARCHAR2(1);
3676
3677
3678 BEGIN
3679
3680 -- Standard Start of API savepoint
3681 SAVEPOINT assignMsg_pvt;
3682
3683 -- Init vars
3684 l_api_name :='assignMsg';
3685 l_api_version_number :=1.0;
3686 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
3687 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
3688 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
3689 l_sr_id :=null;
3690 l_parent_ih_id :=null;
3691 l_customer_id :=null;
3692 l_contact_id :=null;
3693 l_relationship_id :=null;
3694 l_lead_id :=null;
3695 l_milcs_id :=null;
3696
3697
3698 -- Standard call to check for call compatibility.
3699 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3700 1.0,
3701 l_api_name,
3702 G_PKG_NAME)
3703 THEN
3704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3705 END IF;
3706
3707 -- Initialize message list if p_init_msg_list is set to TRUE.
3708 IF FND_API.to_Boolean( p_init_msg_list )
3709 THEN
3710 FND_MSG_PUB.initialize;
3711 END IF;
3712
3713 -- Initialize API return status to SUCCESS
3714 x_return_status := FND_API.G_RET_STS_SUCCESS;
3715
3716 -----------------------Code------------------------
3717
3718 -- if assign to herself, this will be recorded as a 'fetch'
3719 if ( p_from_resource_id = p_to_resource_id ) then
3720 l_mail_item_status := 'N'; -- new
3721 else
3722 l_mail_item_status := 'A';
3723 end if;
3724
3725 --Update IEM_RT_PROC_EMAILS with to_agent_id, from_agent_id and status 'A'
3726 IEM_MAILITEM_PUB.GetQueueItemData (p_api_version_number => 1.0,
3727 p_init_msg_list => 'F',
3728 p_commit => 'F',
3729 p_message_id => p_message_id,
3730 p_from_agent_id => p_from_resource_id,
3731 p_to_agent_id => p_to_resource_id,
3732 p_mail_item_status => l_mail_item_status,
3733 x_email_data => l_email_data_rec,
3734 x_tag_key_value => l_tag_key_value_tbl,
3735 x_encrypted_id => l_tag_id,
3736 x_return_status => l_status,
3737 x_msg_count => l_msg_count,
3738 x_msg_data => l_msg_data);
3739
3740 -- Check return status; Proceed on success Or report back in case of error.
3741 IF (l_status = FND_API.G_RET_STS_SUCCESS) THEN
3742 -- Success.
3743 -- Get the name of the route classification from the ID returned above.
3744 -- This is the name of the folder where the inbound message exists on the
3745 -- master account.
3746 SELECT name INTO x_route_classification
3747 FROM iem_route_classifications
3748 WHERE ROUTE_CLASSIFICATION_ID = l_email_data_rec.RT_CLASSIFICATION_ID;
3749
3750 -- Set the folder name
3751 x_folder_name := x_route_classification;
3752
3753 -- Extract tag key value from key value table
3754 -- Currently valid system key names:
3755 -- IEMNBZTSRVSRID for sr id
3756 -- IEMNINTERACTIONID for interaction id
3757 -- IEMNAGENTID for agent id
3758 -- IEMNCUSTOMERID for customer id
3759 -- IEMNCONTACTID for contact id
3760 -- IEMNBZSALELEADID for lead id
3761
3762 FOR i IN 1..l_tag_key_value_tbl.count LOOP
3763 BEGIN
3764 IF (l_tag_key_value_tbl(i).key = 'IEMNBZTSRVSRID' ) THEN
3765 l_sr_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
3766 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNINTERACTIONID' ) THEN
3767 l_parent_ih_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
3768 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNCUSTOMERID' ) THEN
3769 l_customer_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
3770 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNCONTACTID' ) THEN
3771 l_contact_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
3772 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNRELATIONSHIPID' ) THEN
3773 l_relationship_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
3774 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNBZSALELEADID' ) THEN
3775 l_lead_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
3776 END IF;
3777 END;
3778 END LOOP;
3779
3780 -- customer id and contact id from tagging supersede the result from
3781 -- email search (i.e. what are in l_email_date_rec)
3782 IF (l_customer_id is NULL) THEN
3783 BEGIN
3784 l_customer_id := l_email_data_rec.CUSTOMER_ID;
3785 l_contact_id := null;
3786 l_relationship_id := null;
3787 END;
3788 END IF;
3789
3790 -- Employee Search. shramana
3791 select acct.account_type
3792 into l_acct_type
3793 from iem_mstemail_accounts acct, iem_rt_proc_emails proc
3794 where proc.message_id = p_message_id
3795 and acct.email_account_id = proc.email_account_id;
3796
3797 if(l_acct_type = 'I') then
3798 l_contact_id := l_email_data_rec.CONTACT_ID;
3799 end if;
3800
3801 -- Record details into the RT tables.
3802 l_ih_creator := null;
3803 if (l_email_data_rec.IH_INTERACTION_ID is not null) then
3804 l_ih_creator := 'Y';
3805 end if;
3806
3807 select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
3808 INSERT INTO iem_rt_interactions (
3809 rt_interaction_id, resource_id, customer_id, contact_id, type,
3810 status, expire, created_by, creation_date, last_updated_by,
3811 last_update_date, last_update_login, parent_interaction_id,
3812 service_request_id, inb_tag_id, interaction_id, ih_creator,
3813 lead_id, relationship_id )
3814 VALUES (
3815 l_i_sequence, p_to_resource_id, l_customer_id, l_contact_id,
3816 G_INBOUND, G_WORK_IN_PROGRESS, G_ACTIVE, l_created_by,
3817 SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
3818 l_parent_ih_id, l_sr_id, l_tag_id,
3819 l_email_data_rec.IH_INTERACTION_ID, l_ih_creator,
3820 l_lead_id, l_relationship_id
3821
3822 );
3823 -- db_server id used by mid-tier to locate accounts
3824 l_db_server_id := -1;
3825
3826 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
3827 INSERT INTO iem_rt_media_items (
3828 rt_interaction_id, rt_media_item_id, resource_id,
3829 media_id, message_id, rfc822_message_id, folder_name,
3830 folder_uid, email_account_id, db_server_id, email_type,
3831 status, expire, version, created_by, creation_date,
3832 last_updated_by, last_update_date, last_update_login )
3833 VALUES (
3834 l_i_sequence, l_m_sequence, p_to_resource_id,
3835 l_email_data_rec.IH_MEDIA_ITEM_ID,
3836 l_email_data_rec.MESSAGE_ID,
3837 null,
3838 x_folder_name,
3839 -1,
3840 l_email_data_rec.EMAIL_ACCOUNT_ID,
3841 l_db_server_id,
3842 G_INBOUND, G_UNMOVED, G_ACTIVE,0, l_created_by, SYSDATE,
3843 l_last_updated_by, SYSDATE, l_last_update_login
3844 );
3845
3846 -- update post_mdts to set queue_status to null in case it is not clear
3847 UPDATE IEM_RT_PROC_EMAILS SET queue_status = NULL WHERE message_id = p_message_id;
3848
3849
3850 --Add MLCS 'email assigned' using to_resource_id and 'email_assign' using resource_id
3851 if ( l_mail_item_status = 'A' ) then
3852
3853 l_media_lc_rec.start_date_time := SYSDATE;
3854 l_media_lc_rec.end_date_time := SYSDATE;
3855 l_media_lc_rec.media_id := l_email_data_rec.IH_MEDIA_ITEM_ID;
3856 l_media_lc_rec.milcs_type_id := 35; -- EMAIL_ASSIGNED
3857 l_media_lc_rec.resource_id := p_to_resource_id;
3858 l_media_lc_rec.handler_id := 680;
3859 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
3860 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
3861 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
3862 p_user_id => l_created_by,
3863 p_login_id => l_last_update_login,
3864 x_return_status => l_status,
3865 x_msg_count => l_msg_count,
3866 x_msg_data => l_msg_data,
3867 x_milcs_id => l_milcs_id,
3868 p_media_lc_rec => l_media_lc_rec);
3869
3870 l_media_lc_rec.start_date_time := SYSDATE;
3871 l_media_lc_rec.end_date_time := SYSDATE;
3872 l_media_lc_rec.media_id := l_email_data_rec.IH_MEDIA_ITEM_ID;
3873 l_media_lc_rec.milcs_type_id := 45; -- EMAIL_ASSIGN
3874 l_media_lc_rec.resource_id := p_from_resource_id;
3875 l_media_lc_rec.handler_id := 680;
3876 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
3877 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
3878 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
3879 p_user_id => l_created_by,
3880 p_login_id => l_last_update_login,
3881 x_return_status => l_status,
3882 x_msg_count => l_msg_count,
3883 x_msg_data => l_msg_data,
3884 x_milcs_id => l_milcs_id,
3885 p_media_lc_rec => l_media_lc_rec);
3886
3887 end if;
3888
3889 -- Return Media Values to the JSPs.
3890 x_rt_media_item_id := l_m_sequence;
3891 x_email_account_id := l_email_data_rec.EMAIL_ACCOUNT_ID;
3892 x_oes_id := l_db_server_id;
3893 x_folder_uid := -1;
3894 x_customer_id := l_customer_id;
3895 x_rfc822_message_id := null;
3896 x_rt_interaction_id := l_i_sequence;
3897 x_mdt_message_id := l_email_data_rec.MESSAGE_ID;
3898 x_service_request_id := l_sr_id;
3899 x_contact_id := l_contact_id;
3900 x_lead_id := l_lead_id;
3901 x_relationship_id := l_relationship_id;
3902 ELSE
3903 -- Return the error returned by MDT API
3904 x_return_status := l_status;
3905 x_msg_count := l_msg_count;
3906 x_msg_data := l_msg_data;
3907
3908 END IF;
3909
3910
3911
3912
3913 -------------------End Code------------------------
3914 -- Standard Check Of p_commit.
3915 IF FND_API.To_Boolean(p_commit) THEN
3916 COMMIT WORK;
3917 END IF;
3918 -- Standard callto get message count and if count is 1, get message info.
3919 FND_MSG_PUB.Count_And_Get
3920 ( p_encoded => FND_API.G_TRUE,
3921 p_count => x_msg_count,
3922 p_data => x_msg_data
3923 );
3924 EXCEPTION
3925 WHEN FND_API.G_EXC_ERROR THEN
3926 ROLLBACK TO assignMsg_pvt;
3927 x_return_status := FND_API.G_RET_STS_ERROR ;
3928 FND_MSG_PUB.Count_And_Get(
3929 p_encoded => FND_API.G_TRUE,
3930 p_count => x_msg_count,
3931 p_data => x_msg_data);
3932
3933 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3934 ROLLBACK TO assignMsg_pvt;
3935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3936 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
3937 p_count => x_msg_count,
3938 p_data => x_msg_data);
3939
3940 WHEN OTHERS THEN
3941 ROLLBACK TO assignMsg_pvt;
3942 x_return_status := FND_API.G_RET_STS_ERROR;
3943 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3944 THEN
3945 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
3946 END IF;
3947 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
3948 p_count => x_msg_count,
3949 p_data => x_msg_data);
3950 END assignMsg;
3951
3952
3953
3954 PROCEDURE queueToOutbox (p_api_version_number IN NUMBER,
3955 p_init_msg_list IN VARCHAR2,
3956 p_commit IN VARCHAR2,
3957 p_action IN VARCHAR2,
3958 p_action_id IN NUMBER,
3959 p_rt_media_item_id IN NUMBER,
3960 p_version IN NUMBER,
3961 p_customer_id IN NUMBER,
3962 p_activity_type_id IN NUMBER,
3963 p_outcome_id IN NUMBER,
3964 p_result_id IN NUMBER,
3965 p_reason_id IN NUMBER,
3966 p_to_resource_id IN NUMBER,
3967 p_status IN VARCHAR2,
3968 x_return_status OUT NOCOPY VARCHAR2,
3969 x_msg_count OUT NOCOPY NUMBER,
3970 x_msg_data OUT NOCOPY VARCHAR2
3971 ) IS
3972 l_api_name VARCHAR2(255);
3973 l_api_version_number NUMBER;
3974 l_created_by NUMBER;
3975 l_last_updated_by NUMBER;
3976 l_last_update_login NUMBER;
3977
3978 l_status VARCHAR2(300);
3979 l_msg_count NUMBER;
3980 l_msg_data VARCHAR2(300);
3981
3982 l_activity_type_id NUMBER;
3983 l_result_id NUMBER;
3984 l_reason_id NUMBER;
3985
3986 badResourceId EXCEPTION;
3987 RTError EXCEPTION;
3988
3989 l_message_id NUMBER;
3990 l_rt_interaction_id NUMBER;
3991 l_version NUMBER;
3992 l_email_type VARCHAR2(1);
3993 l_customer_id NUMBER;
3994 l_count NUMBER;
3995 l_data VARCHAR2(300);
3996 l_ret_status VARCHAR2(300);
3997 l_action_id NUMBER;
3998
3999 IEM_NO_DATA EXCEPTION;
4000 l_resource_id NUMBER;
4001 l_noop NUMBER;
4002
4003 BEGIN
4004
4005 -- Standard Start of API savepoint
4006 SAVEPOINT queueToOutbox_pvt;
4007 -- Init vars
4008 l_api_name :='queueToOutbox';
4009 l_api_version_number :=1.0;
4010 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4011 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4012 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
4013 l_noop := 0;
4014
4015 -- Standard call to check for call compatibility.
4016 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
4017 1.0,
4018 l_api_name,
4019 G_PKG_NAME)
4020 THEN
4021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4022 END IF;
4023
4024 -- Initialize message list if p_init_msg_list is set to TRUE.
4025 IF FND_API.to_Boolean( p_init_msg_list )
4026 THEN
4027 FND_MSG_PUB.initialize;
4028 END IF;
4029
4030 -- Initialize API return status to SUCCESS
4034
4031 x_return_status := FND_API.G_RET_STS_SUCCESS;
4032
4033 -----------------------Code------------------------
4035 -- Get the values that are needed later.
4036 BEGIN
4037 l_rt_interaction_id := null;
4038 SELECT rt_interaction_id, version, email_type, resource_id
4039 INTO l_rt_interaction_id, l_version, l_email_type, l_resource_id
4040 FROM iem_rt_media_items
4041 WHERE rt_media_item_id = p_rt_media_item_id
4042 AND expire = G_ACTIVE
4043 FOR UPDATE NOWAIT;
4044
4045 IF ((l_version <> p_version) AND (UPPER(l_email_type) = G_OUTBOUND) ) THEN
4046 x_return_status := 'M';
4047 END IF;
4048
4049 EXCEPTION
4050 WHEN OTHERS THEN
4051 NULL;
4052 END;
4053
4054 IF ( l_rt_interaction_id is null ) THEN
4055 raise IEM_NO_DATA;
4056 END IF;
4057
4058 IF ( (l_resource_id = p_to_resource_id) AND
4059 (p_action = 'T' OR p_action = 'H' OR p_action = 'E') ) THEN
4060 l_noop := 1;
4061 END IF;
4062
4063 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS AND l_noop = 0) THEN
4064
4065 BEGIN
4066 l_message_id := null;
4067 SELECT message_id INTO l_message_id FROM iem_rt_media_items
4068 WHERE rt_interaction_id = l_rt_interaction_id
4069 AND email_type = G_INBOUND;
4070 EXCEPTION
4071 WHEN OTHERS THEN
4072 NULL;
4073 END;
4074
4075 UPDATE iem_rt_media_items
4076 SET expire = G_QUEUEOUT
4077 WHERE rt_interaction_id = l_rt_interaction_id and expire <> G_DORMANT;
4078
4079 if ( p_action_id > 0 ) then
4080 l_action_id := p_action_id;
4081 else
4082 l_action_id := null;
4083 end if;
4084
4085 UPDATE iem_rt_interactions
4086 SET expire = G_QUEUEOUT,
4087 status = p_status,
4088 customer_id = decode(p_customer_id, G_NUM_NOP, customer_id, p_customer_id),
4089 action_id = decode(l_action_id, null, action_id, l_action_id),
4090 action_item_id = decode(p_activity_type_id,-1, NULL, p_activity_type_id),
4091 result_id = decode(p_result_id,-1, NULL, p_result_id),
4092 outcome_id = decode(p_outcome_id,-1, NULL, p_outcome_id),
4093 reason_id = decode(p_reason_id,-1, NULL, p_reason_id),
4094 to_resource_id = decode(p_to_resource_id, G_NUM_NOP, to_resource_id, p_to_resource_id)
4095 WHERE rt_interaction_id = l_rt_interaction_id
4096 AND expire = G_ACTIVE;
4097
4098 -- mark the post_mdts to 'Q' to prevent this from considered by getmailitemcount().
4099 if ( l_message_id is not null ) then
4100 UPDATE IEM_RT_PROC_EMAILS SET queue_status = 'Q'
4101 WHERE message_id = l_message_id;
4102 end if;
4103
4104 END IF;
4105
4106 -------------------End Code------------------------
4107 -- Standard Check Of p_commit.
4108 IF FND_API.To_Boolean(p_commit) THEN
4109 COMMIT WORK;
4110 END IF;
4111
4112 EXCEPTION
4113 WHEN IEM_NO_DATA THEN
4114 ROLLBACK TO queueToOutbox_pvt;
4115 x_return_status := FND_API.G_RET_STS_ERROR ;
4116 FND_MESSAGE.SET_NAME('IEM', 'IEM_MSG_INTERCEPTED');
4117 FND_MSG_PUB.ADD;
4118 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4119 p_count => x_msg_count,
4120 p_data => x_msg_data);
4121
4122 WHEN RTError THEN
4123 ROLLBACK TO queueToOutbox_pvt;
4124 x_return_status := FND_API.G_RET_STS_ERROR;
4125 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
4126 p_count => x_msg_count,
4127 p_data => x_msg_data);
4128
4129 WHEN FND_API.G_EXC_ERROR THEN
4130 ROLLBACK TO queueToOutbox_pvt;
4131 x_return_status := FND_API.G_RET_STS_ERROR ;
4132 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4133 p_count => x_msg_count,
4134 p_data => x_msg_data);
4135
4136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4137 ROLLBACK TO queueToOutbox_pvt;
4138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4139 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4140 p_count => x_msg_count,
4141 p_data => x_msg_data);
4142 WHEN OTHERS THEN
4143 ROLLBACK TO queueToOutbox_pvt;
4144 x_return_status := FND_API.G_RET_STS_ERROR;
4145 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4146 THEN
4147 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
4148 END IF;
4149 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
4150 p_count => x_msg_count,
4151 p_data => x_msg_data);
4152 END queueToOutbox;
4153
4154
4155 PROCEDURE getNextOutboxItem (p_api_version_number IN NUMBER,
4156 p_init_msg_list IN VARCHAR2,
4157 p_commit IN VARCHAR2,
4158 p_failed IN VARCHAR2,
4159 x_return_status OUT NOCOPY VARCHAR2,
4160 x_msg_count OUT NOCOPY NUMBER,
4161 x_msg_data OUT NOCOPY VARCHAR2,
4162 x_rfc822_message_id OUT NOCOPY VARCHAR2,
4163 x_account_id OUT NOCOPY NUMBER,
4164 x_account_type OUT NOCOPY VARCHAR2,
4165 x_email_type OUT NOCOPY VARCHAR2,
4166 x_status OUT NOCOPY VARCHAR2,
4167 x_version OUT NOCOPY NUMBER,
4168 x_rt_media_item_id OUT NOCOPY NUMBER,
4169 x_rt_interaction_id OUT NOCOPY NUMBER,
4170 x_oes_id OUT NOCOPY NUMBER,
4171 x_folder_name OUT NOCOPY VARCHAR2,
4172 x_folder_uid OUT NOCOPY NUMBER,
4173 x_customer_id OUT NOCOPY NUMBER,
4174 x_interaction_id OUT NOCOPY NUMBER,
4175 x_service_request_id OUT NOCOPY NUMBER,
4176 x_mc_parameter_id OUT NOCOPY NUMBER,
4177 x_service_request_action OUT NOCOPY VARCHAR,
4178 x_contact_id OUT NOCOPY NUMBER,
4179 x_parent_ih_id OUT NOCOPY NUMBER,
4180 x_tag_id OUT NOCOPY VARCHAR,
4181 x_rt_ih_status OUT NOCOPY VARCHAR,
4182 x_action_id OUT NOCOPY NUMBER,
4183 x_action_item_id OUT NOCOPY NUMBER,
4184 x_result_id OUT NOCOPY NUMBER,
4185 x_reason_id OUT NOCOPY NUMBER,
4186 x_outcome_id OUT NOCOPY NUMBER,
4187 x_to_resource_id OUT NOCOPY NUMBER,
4188 x_resource_id OUT NOCOPY NUMBER,
4189 x_lead_id OUT NOCOPY NUMBER
4190 ) IS
4191
4192 l_api_name VARCHAR2(255);
4193 l_api_version_number NUMBER;
4194
4195 l_email_account_id NUMBER;
4196 l_agent_account_id NUMBER;
4197 l_expire VARCHAR2(1);
4198 l_email_type VARCHAR2(1);
4199 l_max_try NUMBER;
4200 l_max_try_val VARCHAR2(20);
4201 l_try NUMBER;
4202 l_rt_media_item_id NUMBER;
4203
4204 InteractnComplt EXCEPTION;
4205 badAccountType EXCEPTION;
4206 IEM_NO_DATA EXCEPTION;
4207
4208 Type get_next is REF CURSOR;
4209 rt_cur get_next;
4210 l_rt_ih_data IEM_RT_INTERACTIONS%ROWTYPE;
4211 e_nowait EXCEPTION;
4212 PRAGMA EXCEPTION_INIT(e_nowait, -54);
4213
4214 str VARCHAR2(500);
4215
4216
4217 BEGIN
4218
4219 -- Standard Start of API savepoint
4220 SAVEPOINT getNextOutboxItem_pvt;
4221 -- Init vars
4222 l_api_name :='getNextOutboxItem';
4223 l_api_version_number :=1.0;
4224 l_email_account_id := 0;
4225 l_agent_account_id := 0;
4226
4227 -- Standard call to check for call compatibility.
4228 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
4229 1.0,
4230 l_api_name,
4231 G_PKG_NAME)
4232 THEN
4233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4234 END IF;
4235
4236 -- Initialize message list if p_init_msg_list is set to TRUE.
4237 IF FND_API.to_Boolean( p_init_msg_list )
4238 THEN
4239 FND_MSG_PUB.initialize;
4240 END IF;
4241
4242 -- Initialize API return status to SUCCESS
4243 x_return_status := FND_API.G_RET_STS_SUCCESS;
4244
4245 -----------------------Code------------------------
4246 IF ( p_failed = 'T' ) THEN
4247 -- Get the item that is failed at last attempt to process
4248 str := 'SELECT * FROM iem_rt_interactions
4249 WHERE expire = :1 AND last_update_date < sysdate - 0.007 ORDER BY creation_date FOR UPDATE SKIP LOCKED';
4250 OPEN rt_cur FOR str USING G_PROCESSING;
4251 ELSE
4252 -- Get the item that need to be process.
4253 str := 'SELECT * FROM iem_rt_interactions
4254 WHERE expire = :1 ORDER BY creation_date FOR UPDATE SKIP LOCKED';
4255 OPEN rt_cur FOR str USING G_QUEUEOUT;
4256 END IF;
4257
4258 -- find max_try
4259 IF ( p_failed = 'T' ) THEN
4260
4261 IEM_PARAMETERS_PVT.select_profile(p_api_version_number =>1.0,
4262 P_INIT_MSG_LIST => 'F',
4263 P_COMMIT => 'F',
4264 p_profile_name => 'IEM_OP_MAX_FAIL_RETRIES',
4265 x_profile_value => l_max_try_val,
4266 x_return_status => x_return_status,
4267 x_msg_count => x_msg_count,
4268 x_msg_data => x_msg_data );
4269
4270 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
4271 l_max_try := 3;
4272 x_return_status := FND_API.G_RET_STS_SUCCESS;
4273 ELSE
4277 END IF;
4274 l_max_try := TO_NUMBER(l_max_try_val);
4275 END IF;
4276
4278
4279 LOOP
4280 BEGIN
4281 l_rt_ih_data := null;
4282 FETCH rt_cur into l_rt_ih_data;
4283 IF ( l_rt_ih_data.rt_interaction_id is null ) THEN
4284 EXIT;
4285 END IF;
4286 IF ( p_failed = 'T' ) THEN
4287
4288 -- check max_try
4289 if ( l_rt_ih_data.status = 'S' OR l_rt_ih_data.status = 'F') then -- is 'send' or 'autoforward'
4290 l_email_type := G_OUTBOUND;
4291 else
4292 l_email_type := G_INBOUND;
4293 end if;
4294 begin
4295 l_rt_media_item_id := null;
4296 SELECT rt_media_item_id into l_rt_media_item_id
4297 FROM iem_rt_media_items
4298 WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id
4299 AND email_type = l_email_type
4300 AND status <> G_NEWREROUTE
4301 AND expire <> G_DORMANT;
4302 exception
4303 WHEN OTHERS then
4304 null;
4305 end;
4306
4307 if ( l_rt_media_item_id is not null ) then
4308 begin
4309 l_try := 0;
4310 SELECT count(outbox_error_id) INTO l_try
4311 FROM iem_outbox_errors
4312 WHERE rt_media_item_id = l_rt_media_item_id;
4313 exception
4314 WHEN OTHERS then
4315 null;
4316 end;
4317 if ( l_try < l_max_try ) then
4318 EXIT;
4319 end if;
4320 end if;
4321
4322 ELSE -- failed is false
4323 EXIT;
4324 END IF;
4325 EXCEPTION when e_nowait then
4326 null;
4327 WHEN OTHERS then
4328 null;
4329 END;
4330 END LOOP;
4331 close rt_cur;
4332
4333 IF l_rt_ih_data.rt_interaction_id IS NULL THEN
4334 x_return_status := 'N';
4335
4336 ELSE
4337
4338 -- Mark the item to 'under processing'
4339 BEGIN
4340 UPDATE iem_rt_interactions SET expire = G_PROCESSING, last_update_date = SYSDATE
4341 WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id;
4342 commit;
4343 END;
4344
4345 x_rt_interaction_id := l_rt_ih_data.rt_interaction_id;
4346 x_action_id := l_rt_ih_data.action_id;
4347 x_action_item_id := l_rt_ih_data.action_item_id;
4348 x_result_id := l_rt_ih_data.result_id;
4349 x_reason_id := l_rt_ih_data.reason_id;
4350 x_outcome_id := l_rt_ih_data.outcome_id;
4351 x_rt_ih_status := l_rt_ih_data.status;
4352 x_customer_id := l_rt_ih_data.customer_id;
4353 x_contact_id := l_rt_ih_data.contact_id;
4354
4355
4356
4357 x_interaction_id := l_rt_ih_data.interaction_id;
4358 x_parent_ih_id := l_rt_ih_data.parent_interaction_id;
4359 x_service_request_id := l_rt_ih_data.service_request_id;
4360 x_service_request_action := l_rt_ih_data.service_request_action;
4361 x_mc_parameter_id := l_rt_ih_data.mc_parameter_id;
4362 x_tag_id := l_rt_ih_data.inb_tag_id;
4363 x_to_resource_id := l_rt_ih_data.to_resource_id;
4364 x_resource_id := l_rt_ih_data.resource_id;
4365 x_lead_id := l_rt_ih_data.lead_id;
4366
4367
4368 -- Do a query to get inbound or outbound media details of the specified rt_interaction_id.
4369 BEGIN
4370 x_rt_media_item_id := null;
4371
4372 if ( l_rt_ih_data.status = 'S' OR l_rt_ih_data.status = 'F' ) then -- is 'send' or 'autoforward'
4373 l_email_type := G_OUTBOUND;
4374 else
4375 l_email_type := G_INBOUND;
4376 end if;
4377
4378 SELECT rt_media_item_id, rfc822_message_id,
4379 folder_name, folder_uid, email_account_id, agent_account_id,
4380 db_server_id, email_type, status, version, expire
4381 INTO x_rt_media_item_id, x_rfc822_message_id,
4382 x_folder_name, x_folder_uid, l_email_account_id, l_agent_account_id,
4383 x_oes_id, x_email_type, x_status, x_version, l_expire
4384 FROM iem_rt_media_items
4385 WHERE rt_interaction_id = l_rt_ih_data.rt_interaction_id
4386 AND email_type = l_email_type
4387 AND status <> G_NEWREROUTE
4388 AND expire <> G_DORMANT;
4389
4390 EXCEPTION
4391 WHEN OTHERS THEN
4392 raise InteractnComplt;
4393 END;
4394
4395 -- The requested media type exists.
4396 if (x_rt_media_item_id IS NULL ) then
4397 x_return_status := 'N';
4398 else
4399
4400
4401 -- set account type
4402 IF ((l_email_account_id IS NULL) AND (l_agent_account_id IS NOT NULL)) THEN
4403 x_account_id := l_agent_account_id;
4404 x_account_type := G_AGENT_ACCOUNT;
4405 ELSIF ((l_agent_account_id IS NULL) AND (l_email_account_id IS NOT NULL)) THEN
4406 x_account_id := l_email_account_id;
4407 x_account_type := G_MASTER_ACCOUNT;
4408 ELSIF ((l_agent_account_id IS NOT NULL) AND (l_email_account_id IS NOT NULL)) THEN
4409 x_account_id := l_agent_account_id;
4410 x_account_type := G_AGENT_ACCOUNT;
4411 ELSE
4412 raise badAccountType;
4413 END IF;
4414 end if;
4415 END IF;
4416
4417 -------------------End Code------------------------
4418 EXCEPTION
4419 --WHEN IEM_NO_DATA THEN
4420 --ROLLBACK TO getNextOutboxItem_pvt;
4421 --x_return_status := FND_API.G_RET_STS_ERROR ;
4422 --FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
4423 --FND_MSG_PUB.ADD;
4424 --FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4425 -- p_count => x_msg_count,
4426 -- p_data => x_msg_data);
4427 WHEN InteractnComplt THEN
4428 ROLLBACK TO getNextOutboxItem_pvt;
4429 x_return_status := FND_API.G_RET_STS_ERROR ;
4430 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_RT_REC');
4431 FND_MSG_PUB.ADD;
4432 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4433 p_count => x_msg_count,
4434 p_data => x_msg_data);
4435 WHEN badAccountType THEN
4436 ROLLBACK TO getNextOutboxItem_pvt;
4437 x_return_status := FND_API.G_RET_STS_ERROR ;
4438 FND_MESSAGE.SET_NAME('IEM', 'IEM_BAD_ACCOUNT_TYPE');
4439 FND_MSG_PUB.ADD;
4440 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4441 p_count => x_msg_count,
4442 p_data => x_msg_data);
4443 WHEN FND_API.G_EXC_ERROR THEN
4444 ROLLBACK TO getNextOutboxItem_pvt;
4445 x_return_status := FND_API.G_RET_STS_ERROR ;
4446 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4447 p_count => x_msg_count,
4448 p_data => x_msg_data);
4449
4450 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4451 ROLLBACK TO getNextOutboxItem_pvt;
4452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4453 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4454 p_count => x_msg_count,
4455 p_data => x_msg_data);
4456
4457 WHEN OTHERS THEN
4458 ROLLBACK TO getNextOutboxItem_pvt;
4459 x_return_status := FND_API.G_RET_STS_ERROR;
4460 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4461 THEN
4462 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
4463 END IF;
4464 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
4465 p_count => x_msg_count,
4466 p_data => x_msg_data);
4467 END getNextOutboxItem;
4468
4469
4470 PROCEDURE createRTItem (p_api_version_number IN NUMBER,
4471 p_init_msg_list IN VARCHAR2,
4472 p_commit IN VARCHAR2,
4473 p_message_id IN NUMBER, -- IEM_RT_PROC_EMAILS.message_id
4474 p_to_resource_id IN NUMBER, -- agent id you want to stamp to IEM_RT_PROC_EMAILS.agent_id
4475 p_from_resource_id IN NUMBER, -- agent id you want to stamp to IEM_RT_PROC_EMAILS.from_agent_id
4476 p_status IN VARCHAR2, -- this will be stamp to IEM_RT_PROC_EMAILS.mail_item_status
4477 p_reason IN VARCHAR2, -- 'O' for auto-route
4478 p_interaction_id IN NUMBER,
4479 x_return_status OUT NOCOPY VARCHAR2,
4480 x_msg_count OUT NOCOPY NUMBER,
4481 x_msg_data OUT NOCOPY VARCHAR2,
4482 x_rt_media_item_id OUT NOCOPY NUMBER,
4483 x_rt_interaction_id OUT NOCOPY NUMBER
4484 ) IS
4485
4486 l_api_name VARCHAR2(255);
4487 l_api_version_number NUMBER;
4488 l_created_by NUMBER;
4489 l_last_updated_by NUMBER;
4490 l_last_update_login NUMBER;
4491
4492 l_email_data_rec IEM_RT_PROC_EMAILS%ROWTYPE;
4493 l_status VARCHAR2(300);
4494 l_msg_count NUMBER;
4495 l_msg_data VARCHAR2(300);
4496
4497 l_i_sequence NUMBER;
4498 l_m_sequence NUMBER;
4499 l_db_server_id NUMBER;
4500 l_classification_id NUMBER;
4501 l_tag_key_value_tbl IEM_MAILITEM_PUB.keyVals_tbl_type;
4502 l_tag_id VARCHAR2(30);
4503 l_sr_id NUMBER;
4504 l_parent_ih_id NUMBER;
4505 l_customer_id NUMBER;
4506 l_contact_id NUMBER;
4507 l_lead_id NUMBER;
4508 l_folder_name VARCHAR2(255);
4509 l_ih_creator VARCHAR2(1);
4510
4511 BEGIN
4512
4513 -- Standard Start of API savepoint
4514 SAVEPOINT createRTItem_pvt;
4515
4516 -- Init vars
4517 l_api_name :='createRTItem';
4518 l_api_version_number :=1.0;
4519 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4520 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4521 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
4522 l_sr_id :=null;
4523 l_parent_ih_id :=null;
4524 l_customer_id :=null;
4525 l_contact_id :=null;
4526 l_lead_id :=null;
4527
4528 -- Standard call to check for call compatibility.
4529 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
4530 1.0,
4531 l_api_name,
4532 G_PKG_NAME)
4533 THEN
4534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4535 END IF;
4536
4537 -- Initialize message list if p_init_msg_list is set to TRUE.
4538 IF FND_API.to_Boolean( p_init_msg_list )
4539 THEN
4540 FND_MSG_PUB.initialize;
4541 END IF;
4542
4543 -- Initialize API return status to SUCCESS
4544 x_return_status := FND_API.G_RET_STS_SUCCESS;
4545
4546 -----------------------Code------------------------
4547
4548 -- Update IEM_RT_PROC_EMAILS with to_agent_id, from_agent_id and p_status
4549 IEM_MAILITEM_PUB.GetQueueItemData (p_api_version_number => 1.0,
4550 p_init_msg_list => 'F',
4551 p_commit => 'F',
4552 p_message_id => p_message_id,
4553 p_from_agent_id => p_from_resource_id,
4554 p_to_agent_id => p_to_resource_id,
4555 p_mail_item_status => p_status,
4556 x_email_data => l_email_data_rec,
4557 x_tag_key_value => l_tag_key_value_tbl,
4558 x_encrypted_id => l_tag_id,
4559 x_return_status => l_status,
4560 x_msg_count => l_msg_count,
4561 x_msg_data => l_msg_data);
4562
4563 -- Check return status; Proceed on success Or report back in case of error.
4564 IF (l_status = FND_API.G_RET_STS_SUCCESS) THEN
4565 -- Success.
4566 -- Get the name of the route classification from the ID returned above.
4567 -- This is the name of the folder where the inbound message exists on the
4568 -- master account.
4569 -- Changes for R12. Mark the folder as Inbox for autorouted case. bug
4570 -- 7428636 Ranjan 09/25/2008
4571 if p_reason='O' then
4572 l_folder_name:='Inbox';
4573 else
4574 SELECT name INTO l_folder_name
4575 FROM iem_route_classifications
4576 WHERE ROUTE_CLASSIFICATION_ID = l_email_data_rec.RT_CLASSIFICATION_ID;
4577 end if;
4578
4579 -- Extract tag key value from key value table
4580 -- Currently valid system key names:
4581 -- IEMNBZTSRVSRID for sr id
4582 -- IEMNINTERACTIONID for interaction id
4583 -- IEMNAGENTID for agent id
4584 -- IEMNCUSTOMERID for customer id
4585 -- IEMNCONTACTID for contact id
4586 -- IEMNBZSALELEADID for lead id
4587
4588 FOR i IN 1..l_tag_key_value_tbl.count LOOP
4589 BEGIN
4590 IF (l_tag_key_value_tbl(i).key = 'IEMNBZTSRVSRID' ) THEN
4591 l_sr_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
4592 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNINTERACTIONID' ) THEN
4593 l_parent_ih_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
4594 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNCUSTOMERID' ) THEN
4595 l_customer_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
4599 l_lead_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
4596 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNCONTACTID' ) THEN
4597 l_contact_id := TO_NUMBER(l_tag_key_value_tbl(i).value);
4598 ELSIF (l_tag_key_value_tbl(i).key = 'IEMNBZSALELEADID' ) THEN
4600
4601 END IF;
4602 END;
4603 END LOOP;
4604
4605 -- customer id and contact id from tagging supersede the result from
4606 -- email search (i.e. what are in l_email_date_rec)
4607 IF (l_customer_id is NULL) THEN
4608 BEGIN
4609 l_customer_id := l_email_data_rec.CUSTOMER_ID;
4610 l_contact_id := null;
4611 END;
4612 END IF;
4613
4614 -- Record details into the RT tables.
4615 l_ih_creator := null;
4616 if ( p_interaction_id is not null ) then
4617 l_ih_creator := 'Y';
4618 end if;
4619 select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;
4620 INSERT INTO iem_rt_interactions (
4621 rt_interaction_id, resource_id, customer_id, contact_id, type,
4622 status, expire, created_by, creation_date, last_updated_by,
4623 last_update_date, last_update_login, parent_interaction_id,
4624 service_request_id, inb_tag_id, interaction_id, ih_creator,
4625 lead_id)
4626 VALUES (
4627 l_i_sequence, p_to_resource_id, l_customer_id, l_contact_id,
4628 G_INBOUND, G_WORK_IN_PROGRESS, G_ACTIVE, l_created_by,
4629 SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
4630 l_parent_ih_id, l_sr_id, l_tag_id, p_interaction_id, l_ih_creator,
4631 l_lead_id
4632 );
4633 -- db_server id used by mid-tier to locate accounts
4634 l_db_server_id := -1;
4635
4636 select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;
4637 INSERT INTO iem_rt_media_items (
4638 rt_interaction_id, rt_media_item_id, resource_id,
4639 media_id, message_id, rfc822_message_id, folder_name,
4640 folder_uid, email_account_id, db_server_id, email_type,
4641 status, expire, version, created_by, creation_date,
4642 last_updated_by, last_update_date, last_update_login )
4643 VALUES (
4644 l_i_sequence, l_m_sequence, p_to_resource_id,
4645 l_email_data_rec.IH_MEDIA_ITEM_ID,
4646 l_email_data_rec.MESSAGE_ID,
4647 null,
4648 l_folder_name,
4649 -1,
4650 l_email_data_rec.EMAIL_ACCOUNT_ID,
4651 l_db_server_id,
4652 G_INBOUND, UPPER(p_reason), G_ACTIVE,0, l_created_by, SYSDATE,
4653 l_last_updated_by, SYSDATE, l_last_update_login
4654 );
4655
4656
4657 -- Return Media Values to the JSPs.
4658 x_rt_media_item_id := l_m_sequence;
4659 x_rt_interaction_id := l_i_sequence;
4660
4661 ELSE
4662 -- Return the error returned by MDT API
4663 x_return_status := l_status;
4664 x_msg_count := l_msg_count;
4665 x_msg_data := l_msg_data;
4666
4667 END IF;
4668
4669 -------------------End Code------------------------
4670 -- Standard Check Of p_commit.
4671 IF FND_API.To_Boolean(p_commit) THEN
4672 COMMIT WORK;
4673 END IF;
4674 -- Standard callto get message count and if count is 1, get message info.
4675 -- FND_MSG_PUB.Count_And_Get
4676 -- ( p_encoded => FND_API.G_TRUE,
4677 -- p_count => x_msg_count,
4678 -- p_data => x_msg_data
4679 -- );
4680
4681 EXCEPTION
4682 WHEN FND_API.G_EXC_ERROR THEN
4683 ROLLBACK TO createRTItem_pvt;
4684 x_return_status := FND_API.G_RET_STS_ERROR ;
4685 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4686 p_count => x_msg_count,
4687 p_data => x_msg_data);
4688
4689 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4690 ROLLBACK TO createRTItem_pvt;
4691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4692 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4693 p_count => x_msg_count,
4694 p_data => x_msg_data);
4695
4696 WHEN OTHERS THEN
4697 ROLLBACK TO createRTItem_pvt;
4698 x_return_status := FND_API.G_RET_STS_ERROR;
4699 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4700 THEN
4701 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
4702 END IF;
4703 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
4704 p_count => x_msg_count,
4705 p_data => x_msg_data);
4706
4707 END createRTItem;
4708
4709 PROCEDURE isAgentInboxClean(p_api_version_number IN NUMBER,
4710 p_init_msg_list IN VARCHAR2,
4711 p_commit IN VARCHAR2,
4712 p_resource_id IN NUMBER,
4713 p_email_account_id IN NUMBER,
4714 x_is_clean OUT NOCOPY BOOLEAN,
4715 x_return_status OUT NOCOPY VARCHAR2,
4716 x_msg_count OUT NOCOPY NUMBER,
4717 x_msg_data OUT NOCOPY VARCHAR2
4718 ) IS
4719 l_api_name VARCHAR2(255);
4720 l_api_version_number NUMBER;
4721 l_created_by NUMBER;
4722 l_last_updated_by NUMBER;
4723 l_last_update_login NUMBER;
4724
4725 l_count1 NUMBER;
4726 l_count2 NUMBER;
4727 l_agent_account_id NUMBER;
4728 IEM_NO_AGENT_ACCT EXCEPTION;
4729 l_user_name VARCHAR2(100);
4730
4731 BEGIN
4732
4733 -- Standard Start of API savepoint
4734 SAVEPOINT isAgentInboxClean_pvt;
4735
4736 -- Init vars
4737 l_api_name :='isAgentInboxClean';
4738 l_api_version_number :=1.0;
4739 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4740 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4741 l_last_update_login :=NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
4742
4743 -- Standard call to check for call compatibility.
4744 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
4745 1.0,
4746 l_api_name,
4747 G_PKG_NAME)
4748 THEN
4749 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4750 END IF;
4751
4752 -- Initialize message list if p_init_msg_list is set to TRUE.
4753 IF FND_API.to_Boolean( p_init_msg_list )
4754 THEN
4755 FND_MSG_PUB.initialize;
4756 END IF;
4757
4758 -- Initialize API return status to SUCCESS
4759 x_return_status := FND_API.G_RET_STS_SUCCESS;
4760
4761 -----------------------Code------------------------
4762 x_is_clean := false;
4763
4764 begin
4765 select agent_id into l_agent_account_id
4766 from iem_agents where email_account_id = p_email_account_id
4767 and resource_id = p_resource_id;
4768
4769 exception
4770 when others then
4771 -- find out the user_name
4772 begin
4773 select user_name into l_user_name from jtf_rs_resource_extns
4774 where resource_id = p_resource_id;
4775 exception
4776 when others then
4777 l_user_name := to_char(p_resource_id);
4778 end;
4779 raise IEM_NO_AGENT_ACCT;
4780 end;
4781
4782
4783 select count(rt_media_item_id) into l_count1 from iem_rt_media_items
4784 where agent_account_id=l_agent_account_id
4785 and rt_interaction_id
4786 in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE);
4787
4788 select count(rt_media_item_id) into l_count2 from iem_rt_media_items
4789 where resource_id= p_resource_id and email_account_id=p_email_account_id
4790 and rt_interaction_id
4791 in (select rt_interaction_id from iem_rt_interactions where expire <> G_EXPIRE
4792 and type = 'I');
4793
4794 if ( (l_count1 + l_count2) = 0 ) then
4795 x_is_clean := true;
4796 end if;
4797 -------------------End Code------------------------
4798 -- Standard Check Of p_commit.
4799 IF FND_API.To_Boolean(p_commit) THEN
4800 COMMIT WORK;
4801 END IF;
4802 -- Standard callto get message count and if count is 1, get message info.
4803 -- FND_MSG_PUB.Count_And_Get
4804 -- ( p_encoded => FND_API.G_TRUE,
4805 -- p_count => x_msg_count,
4806 -- p_data => x_msg_data
4807 -- );
4808
4809 EXCEPTION
4810 WHEN IEM_NO_AGENT_ACCT THEN
4811 ROLLBACK TO isAgentInboxClean_pvt;
4812 x_return_status := FND_API.G_RET_STS_ERROR ;
4813 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_AGENT_ACCT');
4814 FND_MESSAGE.SET_TOKEN('ARG1', l_user_name);
4815 FND_MESSAGE.SET_TOKEN('ARG2', to_char(p_email_account_id));
4816 FND_MSG_PUB.ADD;
4817 FND_MSG_PUB.Count_And_Get(
4818 p_encoded => FND_API.G_TRUE,
4819 p_count => x_msg_count,
4820 p_data => x_msg_data);
4821
4822 WHEN FND_API.G_EXC_ERROR THEN
4823 ROLLBACK TO isAgentInboxClean_pvt;
4824 x_return_status := FND_API.G_RET_STS_ERROR ;
4825 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4826 p_count => x_msg_count,
4827 p_data => x_msg_data);
4828
4829 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4830 ROLLBACK TO isAgentInboxClean_pvt;
4831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4832 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4833 p_count => x_msg_count,
4834 p_data => x_msg_data);
4835
4836 WHEN OTHERS THEN
4837 ROLLBACK TO isAgentInboxClean_pvt;
4838 x_return_status := FND_API.G_RET_STS_ERROR;
4839 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4840 THEN
4841 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
4842 END IF;
4843 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
4844 p_count => x_msg_count,
4845 p_data => x_msg_data);
4846
4847 END isAgentInboxClean;
4848
4849 PROCEDURE updateOutboundMessageID(p_api_version_number IN NUMBER,
4850 p_init_msg_list IN VARCHAR2,
4851 p_commit IN VARCHAR2,
4852 p_rt_media_item_id IN NUMBER,
4853 p_message_id IN NUMBER,
4854 x_return_status OUT NOCOPY VARCHAR2,
4855 x_msg_count OUT NOCOPY NUMBER,
4856 x_msg_data OUT NOCOPY VARCHAR2
4857 ) IS
4858 l_api_name VARCHAR2(255);
4859 l_api_version_number NUMBER;
4860 l_created_by NUMBER;
4861 l_last_updated_by NUMBER;
4862 l_last_update_login NUMBER;
4863
4864 BEGIN
4865
4866 -- Standard Start of API savepoint
4867 SAVEPOINT updateOutboundMessageID_pvt;
4868
4869 -- Init vars
4870 l_api_name :='updateOutboundMessageID';
4871 l_api_version_number :=1.0;
4872 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4873 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
4874 l_last_update_login :=NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
4875
4876 -- Standard call to check for call compatibility.
4877 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
4878 1.0,
4879 l_api_name,
4880 G_PKG_NAME)
4881 THEN
4882 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4883 END IF;
4884
4885 -- Initialize message list if p_init_msg_list is set to TRUE.
4886 IF FND_API.to_Boolean( p_init_msg_list )
4887 THEN
4888 FND_MSG_PUB.initialize;
4889 END IF;
4890
4891 -- Initialize API return status to SUCCESS
4892 x_return_status := FND_API.G_RET_STS_SUCCESS;
4893
4894 -----------------------Code------------------------
4895
4896 begin
4897
4898 update iem_rt_media_items
4899 set message_id = p_message_id
4900 where rt_media_item_id = p_rt_media_item_id;
4901
4902 end;
4903
4904 -------------------End Code------------------------
4905 -- Standard Check Of p_commit.
4906 IF FND_API.To_Boolean(p_commit) THEN
4907 COMMIT WORK;
4908 END IF;
4909 -- Standard callto get message count and if count is 1, get message info.
4910 -- FND_MSG_PUB.Count_And_Get
4911 -- ( p_encoded => FND_API.G_TRUE,
4912 -- p_count => x_msg_count,
4913 -- p_data => x_msg_data
4914 -- );
4915
4916 EXCEPTION
4917 WHEN FND_API.G_EXC_ERROR THEN
4918 ROLLBACK TO updateOutboundMessageID_pvt;
4919 x_return_status := FND_API.G_RET_STS_ERROR ;
4920 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4921 p_count => x_msg_count,
4922 p_data => x_msg_data);
4923
4924 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4925 ROLLBACK TO updateOutboundMessageID_pvt;
4926 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4927 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
4928 p_count => x_msg_count,
4929 p_data => x_msg_data);
4930
4931 WHEN OTHERS THEN
4932 ROLLBACK TO updateOutboundMessageID_pvt;
4933 x_return_status := FND_API.G_RET_STS_ERROR;
4934 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4935 THEN
4936 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
4937 END IF;
4938 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
4939 p_count => x_msg_count,
4940 p_data => x_msg_data);
4941
4942 END updateOutboundMessageID;
4943
4944 END IEM_CLIENT_PUB;