[Home] [Help]
PACKAGE BODY: APPS.IEM_OP_ADMIN_PUB
Source
1 PACKAGE BODY IEM_OP_ADMIN_PUB as
2 /* $Header: iemoadmb.pls 120.1 2005/07/14 10:40:38 appldev ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEM_OP_ADMIN';
5 G_EXPIRE CONSTANT VARCHAR2(1) := 'Y';
6 G_ACTIVE CONSTANT VARCHAR2(1) := 'N';
7 DATE_FORMAT CONSTANT VARCHAR2(30) := 'MM/DD/RRRR HH24:MI:SS';
8 G_NEWREROUTE CONSTANT VARCHAR2(1) := 'H';
9 G_DORMANT CONSTANT VARCHAR2(1) := 'D';
10 G_ALL CONSTANT NUMBER := -1;
11 G_PROCESSING CONSTANT VARCHAR2(1) := 'G';
12
13
14 -- p_sort_by M: rt_media_item_id, T: action, A: agent, S: summary, D: detail, C: create_date
15 TYPE op_rectype IS RECORD (
16 rt_media_item_id NUMBER(15),
17 error_summary VARCHAR2(500),
18 error_detail VARCHAR2(4000),
19 create_date DATE,
20 create_date_str VARCHAR2(80));
21
22 procedure getItemError(p_api_version_number IN NUMBER,
23 p_init_msg_list IN VARCHAR2,
24 p_commit IN VARCHAR2,
25 p_page_no IN NUMBER,
26 p_disp_size IN NUMBER,
27 p_sort_by IN VARCHAR2,
28 p_sort_dir IN NUMBER, --0 asc, 1 desc
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY VARCHAR2,
32 x_total OUT NOCOPY NUMBER,
33 x_item_err OUT NOCOPY SYSTEM.IEM_OP_ERR_OBJ_ARRAY)
34 IS
35
36
37 l_api_name VARCHAR2(255);
38 l_api_version_number NUMBER;
39
40 l_op_rec op_rectype;
41
42 TYPE get_status is REF CURSOR;
43 op_cur get_status;
44 l_outbox_data SYSTEM.IEM_OP_ERR_OBJ;
45 l_outbox_array SYSTEM.IEM_OP_ERR_OBJ_ARRAY;
46
47 str VARCHAR2(500);
48
49 l_rt_interaction_id NUMBER;
50 l_resource_id NUMBER;
51 l_action VARCHAR2(1);
52 l_name VARCHAR2(100);
53 G_ACTIVE VARCHAR2(1);
54 G_DIR VARCHAR2(8);
55 L_STR_SIZE NUMBER := 200;
56 l_start NUMBER;
57 l_first NUMBER;
58 l_last NUMBER;
59 x BOOLEAN;
60
61
62 begin
63
64 -- Standard Start of API savepoint
65 SAVEPOINT getItemError_pvt;
66
67 -- Init values
68 l_api_name :='getItemError';
69 l_api_version_number :=1.0;
70 G_ACTIVE := 'Y';
71 G_DIR := 'asc';
72
73 -- Standard call to check for call compatibility.
74 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
75 1.0,
76 l_api_name,
77 G_PKG_NAME)
78 THEN
79 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
80 END IF;
81
82 -- Initialize message list if p_init_msg_list is set to TRUE.
83 IF FND_API.to_Boolean( p_init_msg_list )
84 THEN
85 FND_MSG_PUB.initialize;
86 END IF;
87
88 -- Initialize API return status to SUCCESS
89 x_return_status := FND_API.G_RET_STS_SUCCESS;
90
91 ----------------------code ------------------
92 if (p_sort_by = 'M') then
93
94 if (p_sort_dir = 0) then
95 str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
96 where a.outbox_error_id = (select max(b.outbox_error_id)
97 from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by rt_media_item_id asc';
98 else
99 str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
100 where a.outbox_error_id = (select max(b.outbox_error_id)
101 from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by rt_media_item_id desc';
102 end if;
103
104 elsif (p_sort_by = 'S') then
105
106 if (p_sort_dir = 0) then
107 str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
108 where a.outbox_error_id = (select max(b.outbox_error_id)
109 from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by error_summary asc';
110 else
111 str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
112 where a.outbox_error_id = (select max(b.outbox_error_id)
113 from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by error_summary desc';
114 end if;
115
116 elsif (p_sort_by = 'D') then
117 if (p_sort_dir = 0) then
118 str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
119 where a.outbox_error_id = (select max(b.outbox_error_id)
120 from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by error_message asc';
121 else
122 str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
123 where a.outbox_error_id = (select max(b.outbox_error_id)
124 from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by error_message desc';
125 end if;
126 elsif (p_sort_by = 'C') then
127 if (p_sort_dir = 0) then
128 str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
129 where a.outbox_error_id = (select max(b.outbox_error_id)
130 from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by create_date asc';
131 else
132 str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
133 where a.outbox_error_id = (select max(b.outbox_error_id)
134 from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by create_date desc';
135 end if;
136
137 end if;
138
139 OPEN op_cur FOR str USING DATE_FORMAT, G_ACTIVE;
140 l_outbox_array := SYSTEM.IEM_OP_ERR_OBJ_ARRAY();
141 l_start := 0;
142
143 LOOP
144 begin
145
146 FETCH op_cur into l_op_rec;
147 EXIT WHEN op_cur%NOTFOUND;
148
149 x := false;
150 if (l_start = 0) then
151 x := true;
152 else
153 -- Since there are multiple records for one msg at iem_outbox_errors,
154 -- l_op_rec can have multi-records for same rt_media_item_id.
155 if (l_op_rec.rt_media_item_id <> l_outbox_array(l_outbox_array.LAST).rt_media_item_id) then
156 x := true;
157 end if;
158 end if;
159 if ( x ) then
160 l_rt_interaction_id := 0;
161 l_resource_id := 0;
162 l_action := null;
163 l_name := null;
164 begin
165 select rt_interaction_id into l_rt_interaction_id
166 from iem_rt_media_items
167 where rt_media_item_id = l_op_rec.rt_media_item_id;
168
169 select resource_id, status into l_resource_id, l_action
170 from iem_rt_interactions
171 where rt_interaction_id = l_rt_interaction_id;
172 exception
173 -- If no data found because of descripancy in database
174 -- ignore those messages.
175 when others then
176 null;
177 end;
178 if ( l_resource_id > 0 ) then
179 begin
180 select user_name into l_name from jtf_rs_resource_extns
181 where resource_id = l_resource_id;
182 exception
183 when others then
184 null;
185 end;
186 l_outbox_array.EXTEND;
187 l_outbox_array(l_outbox_array.LAST) := SYSTEM.IEM_OP_ERR_OBJ(
188 l_op_rec.rt_media_item_id,
189 l_action, l_name,
190 l_op_rec.error_summary,
191 substr(l_op_rec.error_detail,1,200),
192 l_op_rec.create_date,
193 l_op_rec.create_date_str);
194
195 l_start := 1;
196 end if; -- l_resource_id exists.
197 end if; -- x true.
198 end;
199 END LOOP;
200 CLOSE op_cur;
201
202 -- figure out what to return and display
203 x_total := l_outbox_array.count;
204
205 IF (p_disp_size is null OR p_disp_size = -1) THEN
206 l_first := l_outbox_array.FIRST;
207 l_last := l_outbox_array.LAST;
208 x_item_err := l_outbox_array;
209 ELSE
210 l_first := (p_page_no - 1)*p_disp_size + 1;
211 l_last := p_page_no*p_disp_size;
212 if ( l_last > l_outbox_array.LAST ) then
213 l_last := l_outbox_array.LAST;
214 end if;
215
216 x_item_err := SYSTEM.IEM_OP_ERR_OBJ_ARRAY();
217 if ( x_total > 0 ) then
218 FOR i in l_first..l_last LOOP
219 x_item_err.EXTEND;
220 x_item_err(x_item_err.LAST) := l_outbox_array(i);
221 END LOOP;
222 end if;
223 END IF;
224
225 --------------------------
226 -- Standard Check Of p_commit.
227 IF FND_API.To_Boolean(p_commit) THEN
228 COMMIT WORK;
229 END IF;
230 -- Standard callto get message count and if count is 1, get message info.
231 FND_MSG_PUB.Count_And_Get
232 ( p_encoded => FND_API.G_TRUE,
233 p_count => x_msg_count,
234 p_data => x_msg_data
235 );
236 EXCEPTION
237
238 WHEN FND_API.G_EXC_ERROR THEN
239 ROLLBACK TO getItemError_pvt;
240 x_return_status := FND_API.G_RET_STS_ERROR ;
241 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
242 p_count => x_msg_count,
243 p_data => x_msg_data);
244
245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
246 ROLLBACK TO getItemError_pvt;
247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
248 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
249 p_count => x_msg_count,
250 p_data => x_msg_data);
251
252 WHEN OTHERS THEN
253 ROLLBACK TO getItemError_pvt;
254 x_return_status := FND_API.G_RET_STS_ERROR;
255 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
256 THEN
257 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
258 END IF;
259 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
260 p_count => x_msg_count,
261 p_data => x_msg_data);
262
263
264
265 end;
266
267
268 procedure clearOutboxErrors(p_api_version_number IN NUMBER,
269 p_init_msg_list IN VARCHAR2,
270 p_commit IN VARCHAR2,
271 p_rt_media_item_id_array IN SYSTEM.IEM_RT_MSG_KEY_ARRAY,
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_msg_count OUT NOCOPY NUMBER,
274 x_msg_data OUT NOCOPY VARCHAR2)
275 IS
276
277 l_api_name VARCHAR2(255);
278 l_api_version_number NUMBER;
279
280
281 begin
282
283 -- Standard Start of API savepoint
284 SAVEPOINT clearOutboxErrors_pvt;
285
286 -- Init values
287 l_api_name :='clearOutboxErrors';
288 l_api_version_number :=1.0;
289
290 -- Standard call to check for call compatibility.
291 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
292 1.0,
293 l_api_name,
294 G_PKG_NAME)
295 THEN
296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
297 END IF;
298
299 -- Initialize message list if p_init_msg_list is set to TRUE.
300 IF FND_API.to_Boolean( p_init_msg_list )
301 THEN
302 FND_MSG_PUB.initialize;
303 END IF;
304
305 -- Initialize API return status to SUCCESS
306 x_return_status := FND_API.G_RET_STS_SUCCESS;
307
308 ----------------------code ------------------
309
310 if ( p_rt_media_item_id_array.COUNT > 0 ) then
311 if ( p_rt_media_item_id_array(p_rt_media_item_id_array.FIRST).num = G_ALL ) then
312 delete from iem_outbox_errors;
313 else
314 for i in p_rt_media_item_id_array.FIRST..p_rt_media_item_id_array.LAST loop
315 delete from iem_outbox_errors where rt_media_item_id = p_rt_media_item_id_array(i).num;
316 end loop;
317 end if;
318 end if;
319 --------------------------
320 -- Standard Check Of p_commit.
321 IF FND_API.To_Boolean(p_commit) THEN
322 COMMIT WORK;
323 END IF;
324 -- Standard callto get message count and if count is 1, get message info.
325 FND_MSG_PUB.Count_And_Get
326 ( p_encoded => FND_API.G_TRUE,
327 p_count => x_msg_count,
328 p_data => x_msg_data
329 );
330 EXCEPTION
331
332 WHEN FND_API.G_EXC_ERROR THEN
333 ROLLBACK TO clearOutboxErrors_pvt;
334 x_return_status := FND_API.G_RET_STS_ERROR ;
335 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
336 p_count => x_msg_count,
340 ROLLBACK TO clearOutboxErrors_pvt;
337 p_data => x_msg_data);
338
339 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
342 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
343 p_count => x_msg_count,
344 p_data => x_msg_data);
345
346 WHEN OTHERS THEN
347 ROLLBACK TO clearOutboxErrors_pvt;
348 x_return_status := FND_API.G_RET_STS_ERROR;
349 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
350 THEN
351 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
352 END IF;
353 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
354 p_count => x_msg_count,
355 p_data => x_msg_data);
356
357
358
359 end;
360 procedure purgeOutboxItems(p_api_version_number IN NUMBER,
361 p_init_msg_list IN VARCHAR2,
362 p_commit IN VARCHAR2,
363 p_rt_media_item_id_array IN SYSTEM.IEM_RT_MSG_KEY_ARRAY,
364 x_return_status OUT NOCOPY VARCHAR2,
365 x_msg_count OUT NOCOPY NUMBER,
366 x_msg_data OUT NOCOPY VARCHAR2)
367
368 IS
369 l_api_name VARCHAR2(255);
370 l_api_version_number NUMBER;
371 l_rt_interaction_id NUMBER;
372
373
374 begin
375
376 -- Standard Start of API savepoint
377 SAVEPOINT purgeOutboxErrors_pvt;
378
379 --Init values
380 l_api_name :='purgeOutboxErrors';
381 l_api_version_number :=1.0;
382 -- Standard call to check for call compatibility.
383 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
384 1.0,
385 l_api_name,
386 G_PKG_NAME)
387 THEN
388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
389 END IF;
390
391 -- Initialize message list if p_init_msg_list is set to TRUE.
392 IF FND_API.to_Boolean( p_init_msg_list )
393 THEN
394 FND_MSG_PUB.initialize;
395 END IF;
396
397 -- Initialize API return status to SUCCESS
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399
400 ----------------------code ------------------
401 if ( p_rt_media_item_id_array.COUNT > 0 ) then
402 if ( p_rt_media_item_id_array(p_rt_media_item_id_array.FIRST).num = G_ALL ) then
403 begin
404 update iem_rt_media_items set expire = G_EXPIRE
405 where rt_interaction_id in
406 (select rt_interaction_id from iem_rt_interactions
407 where expire = G_PROCESSING);
408
409 update iem_rt_interactions set expire = G_EXPIRE
410 where expire = G_PROCESSING;
411
412 update iem_outbox_errors set expire = G_EXPIRE;
413 end;
414 else
415 for i in p_rt_media_item_id_array.FIRST..p_rt_media_item_id_array.LAST loop
416 begin
417 select rt_interaction_id into l_rt_interaction_id
418 from iem_rt_media_items
419 where rt_media_item_id = p_rt_media_item_id_array(i).num;
420
421 update iem_rt_interactions set expire = G_EXPIRE where
422 rt_interaction_id = l_rt_interaction_id;
423
424 update iem_rt_media_items set expire = G_EXPIRE where
425 rt_interaction_id = l_rt_interaction_id;
426
427 update iem_outbox_errors set expire = G_EXPIRE where
428 rt_media_item_id = p_rt_media_item_id_array(i).num;
429 end;
430 end loop;
431 end if;
432 end if;
433 --------------------------
434 -- Standard Check Of p_commit.
435 IF FND_API.To_Boolean(p_commit) THEN
436 COMMIT WORK;
437 END IF;
438 -- Standard callto get message count and if count is 1, get message info.
439 FND_MSG_PUB.Count_And_Get
440 ( p_encoded => FND_API.G_TRUE,
441 p_count => x_msg_count,
442 p_data => x_msg_data
443 );
444 EXCEPTION
445
446 WHEN FND_API.G_EXC_ERROR THEN
447 ROLLBACK TO purgeOutboxErrors_pvt;
448 x_return_status := FND_API.G_RET_STS_ERROR ;
449 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
450 p_count => x_msg_count,
451 p_data => x_msg_data);
452
453 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
454 ROLLBACK TO purgeOutboxErrors_pvt;
455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
456 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
457 p_count => x_msg_count,
458 p_data => x_msg_data);
459
460 WHEN OTHERS THEN
461 ROLLBACK TO purgeOutboxErrors_pvt;
462 x_return_status := FND_API.G_RET_STS_ERROR;
463 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
464 THEN
465 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
466 END IF;
467 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
471 end;
468 p_count => x_msg_count,
469 p_data => x_msg_data);
470
472
473 procedure getOpItem(p_api_version_number IN NUMBER,
474 p_init_msg_list IN VARCHAR2,
475 p_commit IN VARCHAR2,
476 p_rt_media_item_id IN NUMBER,
477 x_return_status OUT NOCOPY VARCHAR2,
478 x_msg_count OUT NOCOPY NUMBER,
479 x_msg_data OUT NOCOPY VARCHAR2,
480 x_item_obj OUT NOCOPY SYSTEM.IEM_OP_ITEM
481 )
482 IS
483 l_api_name VARCHAR2(255);
484 l_api_version_number NUMBER;
485 l_rt_interaction_id NUMBER;
486 l_to_resource_id NUMBER;
487 l_agent_acct_id NUMBER;
488 l_reroute_type NUMBER;
489 l_ih_status VARCHAR2(2);
490 l_email_acct_id NUMBER;
491 l_rt_media_item_id NUMBER;
492
493 v_resource_id NUMBER;
494 v_mdt_msg_id NUMBER;
495 v_media_id NUMBER;
496 v_create_date VARCHAR2(80);
497 v_rt_media_status VARCHAR2(2);
498 v_interaction_id NUMBER;
499 v_mcp_id NUMBER;
500 v_rt_ih_expire VARCHAR2(2);
501 v_action VARCHAR2(2);
502 v_outb_rt_media_item_id NUMBER;
503 v_inb_rt_media_item_id NUMBER;
504 v_master_acct_id NUMBER;
505 v_subject VARCHAR2(128);
506 v_sender VARCHAR2(240);
507 v_master_acct_name VARCHAR2(256);
508 v_to_resource_id NUMBER;
509 v_to_master_acct_id NUMBER;
510 v_to_group_id NUMBER;
511 v_rt_interaction_id NUMBER;
512 no_post_mdts NUMBER;
513
514
515
516 begin
517
518 -- Standard Start of API savepoint
519 SAVEPOINT getOpItem_pvt;
520
521 --Init values
522 l_api_name :='getOpItem';
523 l_api_version_number :=1.0;
524
525 -- Standard call to check for call compatibility.
526 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
527 1.0,
528 l_api_name,
529 G_PKG_NAME)
530 THEN
531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
532 END IF;
533
534 -- Initialize message list if p_init_msg_list is set to TRUE.
535 IF FND_API.to_Boolean( p_init_msg_list )
536 THEN
537 FND_MSG_PUB.initialize;
538 END IF;
539
540 -- Initialize API return status to SUCCESS
541 x_return_status := FND_API.G_RET_STS_SUCCESS;
542
543 ----------------------code ------------------
544 select rt_interaction_id, resource_id, message_id, media_id,
545 to_char(creation_date, DATE_FORMAT), status
546 into l_rt_interaction_id, v_resource_id, v_mdt_msg_id, v_media_id,
547 v_create_date, v_rt_media_status
548 from iem_rt_media_items where rt_media_item_id = p_rt_media_item_id;
549
550 v_rt_interaction_id := l_rt_interaction_id;
551
552 select interaction_id, mc_parameter_id, status, expire, to_resource_id
553 into v_interaction_id, v_mcp_id, l_ih_status, v_rt_ih_expire, l_to_resource_id
554 from iem_rt_interactions where rt_interaction_id = l_rt_interaction_id;
555
556 v_action := l_ih_status;
557
558 -- to_resource_id - to agent id when transfer, to group id when redirect
559
560 begin
561 select rt_media_item_id into v_outb_rt_media_item_id
562 from iem_rt_media_items
563 where rt_interaction_id = l_rt_interaction_id and email_type='O';
564 exception
565 when NO_DATA_FOUND then
566 v_outb_rt_media_item_id := null;
567 end;
568
569 begin
570 select rt_media_item_id into v_inb_rt_media_item_id
571 from iem_rt_media_items
572 where rt_interaction_id = l_rt_interaction_id and email_type='I'
573 and expire <> G_DORMANT;
574 exception
575 when NO_DATA_FOUND then
576 v_inb_rt_media_item_id := null;
577 end;
578
579 if ( v_inb_rt_media_item_id is not null ) then
580 select message_id, media_id, to_char(creation_date, DATE_FORMAT), status
581 into v_mdt_msg_id, v_media_id, v_create_date, v_rt_media_status
582 from iem_rt_media_items where rt_media_item_id = v_inb_rt_media_item_id;
583 end if;
584
585 no_post_mdts := 1;
586 if ( v_mdt_msg_id > 0) then
587 begin
588 no_post_mdts := 0;
589 select email_account_id, subject, from_address
590 into v_master_acct_id, v_subject, v_sender
591 from iem_rt_proc_emails where message_id = v_mdt_msg_id;
592
593 select from_name into v_master_acct_name from iem_mstemail_accounts
594 where email_account_id = v_master_acct_id;
595 end;
596 end if; -- mdt_msg_id > 0
597
598 if ( no_post_mdts = 1 ) then
599
600 if (v_inb_rt_media_item_id is not null) then
601 l_rt_media_item_id := v_inb_rt_media_item_id;
602 elsif (v_outb_rt_media_item_id is not null) then
603 l_rt_media_item_id := v_outb_rt_media_item_id;
604 end if;
605
606 select email_account_id, agent_account_id
610 if (l_email_acct_id > 0) then
607 into l_email_acct_id, l_agent_acct_id
608 from iem_rt_media_items where rt_media_item_id = l_rt_media_item_id;
609
611 select from_name into v_master_acct_name from iem_mstemail_accounts
612 where email_account_id = l_email_acct_id;
613 v_master_acct_id := l_email_acct_id;
614
615 elsif ( l_agent_acct_id > 0) then
616 select a.from_name, a.email_account_id
617 into v_master_acct_name, v_master_acct_id
618 from iem_mstemail_accounts a, iem_agents b
619 where a.email_account_id = b.email_account_id
620 and b.agent_id = l_agent_acct_id;
621 end if;
622
623 end if;
624
625
626 if ( l_ih_status = 'T' or l_ih_status = 'H' or l_ih_status = 'E' ) then -- transfer
627 select agent_account_id, resource_id into l_agent_acct_id, v_to_resource_id
628 from iem_rt_media_items
629 where media_id = v_media_id and rt_media_item_id <> p_rt_media_item_id
630 and expire <> G_EXPIRE
631 and rownum < 2;
632
633 select email_account_id into v_to_master_acct_id from iem_agents
634 where agent_id = l_agent_acct_id;
635 end if;
636
637 if ( l_ih_status = 'X' ) then
638 select email_account_id, db_server_id
639 into v_to_master_acct_id, l_reroute_type
640 from iem_rt_media_items
641 where rt_interaction_id = l_rt_interaction_id
642 and status = G_NEWREROUTE and expire = G_DORMANT;
643
644 if (l_reroute_type = 76) AND (l_to_resource_id > 0) then
645 v_to_group_id := l_to_resource_id;
646 end if;
647
648 end if;
649
650 if ( l_ih_status = 'R' ) then -- redirect
651 v_to_master_acct_id := l_to_resource_id;
652 end if;
653
654 x_item_obj := SYSTEM.IEM_OP_ITEM(v_inb_rt_media_item_id,
655 v_outb_rt_media_item_id,
656 v_mdt_msg_id,
657 v_rt_interaction_id,
658 v_interaction_id,
659 v_media_id,
660 v_mcp_id,
661 v_resource_id,
662 v_master_acct_id,
663 v_to_master_acct_id,
664 v_to_resource_id,
665 v_to_group_id,
666 v_rt_ih_expire,
667 v_rt_media_status,
668 v_create_date,
669 v_sender,
670 v_subject,
671 v_action,
672 v_master_acct_name);
673
674
675 --------------------------
676 -- Standard Check Of p_commit.
677 IF FND_API.To_Boolean(p_commit) THEN
678 COMMIT WORK;
679 END IF;
680 -- Standard callto get message count and if count is 1, get message info.
681 FND_MSG_PUB.Count_And_Get
682 ( p_encoded => FND_API.G_TRUE,
683 p_count => x_msg_count,
684 p_data => x_msg_data
685 );
686 EXCEPTION
687
688 WHEN FND_API.G_EXC_ERROR THEN
689 ROLLBACK TO getOpItem_pvt;
690 x_return_status := FND_API.G_RET_STS_ERROR ;
691 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
692 p_count => x_msg_count,
693 p_data => x_msg_data);
694
695 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
696 ROLLBACK TO getOpItem_pvt;
697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
698 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
699 p_count => x_msg_count,
700 p_data => x_msg_data);
701
702 WHEN OTHERS THEN
703 ROLLBACK TO getOpItem_pvt;
704 x_return_status := FND_API.G_RET_STS_ERROR;
705 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
706 THEN
707 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
708 END IF;
709 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
710 p_count => x_msg_count,
711 p_data => x_msg_data);
712
713 end;
714
715 /*
716 procedure getThreadStatus(p_api_version_number IN NUMBER,
717 p_init_msg_list IN VARCHAR2,
718 p_commit IN VARCHAR2,
719 p_thread_type IN NUMBER, -- 0 for Failed, 1 for Normal
720 x_return_status OUT NOCOPY VARCHAR2,
721 x_msg_count OUT NOCOPY NUMBER,
722 x_msg_data OUT NOCOPY VARCHAR2,
723 x_thread_array OUT NOCOPY IEM_OP_THREAD_ARRAY
724 )
725 IS
726 l_api_name VARCHAR2(255):='getThreadStatus';
727 l_api_version_number NUMBER:=1.0;
728 l_con_id NUMBER;
729 l_jserv_id NUMBER;
730 l_jserv_post NUMBER;
731 l_host VARCHAR2(200);
732 l_apache_port NUMBER;
733 l_con_start VARCHAR2(80);
734 l_con_update VARCHAR2(80);
735 l_con_fail VARCHAR2(200);
736 l_th_con_id NUMBER;
737 l_th_id VARCHAR2(80);
738 l_th_start VARCHAR2(80);
739 l_msg_count NUMBER;
740 l_th_type VARCHAR2(1);
741 l_th_update VARCHAR2(80);
742 l_th_fail VARCHAR2(200);
743
744 begin
745
746 -- Standard Start of API savepoint
750 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
747 SAVEPOINT getThreadStatus_pvt;
748
749 -- Standard call to check for call compatibility.
751 1.0,
752 l_api_name,
753 G_PKG_NAME)
754 THEN
755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
756 END IF;
757
758 -- Initialize message list if p_init_msg_list is set to TRUE.
759 IF FND_API.to_Boolean( p_init_msg_list )
760 THEN
761 FND_MSG_PUB.initialize;
762 END IF;
763
764 -- Initialize API return status to SUCCESS
765 x_return_status := FND_API.G_RET_STS_SUCCESS;
766
767 ----------------------code ------------------
768
769 select a.CONTROLLER_ID, a.JSERV_ID, a.JSERV_PORT,
770 a.APACHE_HOST, a.APACHE_PORT, to_char(a.START_TIME, DATE_FORMAT),
771 to_char(a.LAST_UPDATE_DATE, DATE_FORMAT), a.FAILED_REASON
772 into l_con_id, l_jserv_id, l_jserv_post, l_host, l_apache_port,
773 l_con_start, l_con_update, l_con_fail
774 from IEM_OP_CONTROLLER_STATS a order by APACHE_HOST, CONTROLLER_ID desc
775
776 select b.CONTROLLER_ID, b.THREAD_ID, b.PROCESSED_MSG_COUNT,
777 b.THREAD_TYPE, to_char(b.START_TIME, DATE_FORMAT),
778 to_char(b.LAST_UPDATE_DATE, DATE_FORMAT), b.FAILED_REASON
779 into l_th_con_id, l_th_id, l_th_start, l_msg_count, l_th_type,
780 l_th_update, l_th_fail
781 from IEM_OP_THREAD_STATS b, IEM_OP_CONTROLLER_STATS a
782 where a.CONTROLLER_ID = b.CONTROLLER_ID
783 order by a.APACHE_HOST, a.CONTROLLER_ID desc, b.THREAD_TYPE desc
784
785
786
787 --------------------------
788 -- Standard Check Of p_commit.
789 IF FND_API.To_Boolean(p_commit) THEN
790 COMMIT WORK;
791 END IF;
792 -- Standard callto get message count and if count is 1, get message info.
793 FND_MSG_PUB.Count_And_Get
794 ( p_encoded => FND_API.G_TRUE,
795 p_count => x_msg_count,
796 p_data => x_msg_data
797 );
798 EXCEPTION
799
800 WHEN FND_API.G_EXC_ERROR THEN
801 ROLLBACK TO getThreadStatus_pvt;
802 x_return_status := FND_API.G_RET_STS_ERROR ;
803 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
804 p_count => x_msg_count,
805 p_data => x_msg_data);
806
807 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
808 ROLLBACK TO getThreadStatus_pvt;
809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
810 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
811 p_count => x_msg_count,
812 p_data => x_msg_data);
813
814 WHEN OTHERS THEN
815 ROLLBACK TO getThreadStatus_pvt;
816 x_return_status := FND_API.G_RET_STS_ERROR;
817 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
818 THEN
819 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
820 END IF;
821 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
822 p_count => x_msg_count,
823 p_data => x_msg_data);
824
825
826 end;
827 */
828
829
830 procedure pushbackToRework(p_api_version_number IN NUMBER,
831 p_init_msg_list IN VARCHAR2,
832 p_commit IN VARCHAR2,
833 p_rt_media_item_ids IN SYSTEM.IEM_RT_MSG_KEY_ARRAY,
834 x_return_status OUT NOCOPY VARCHAR2,
835 x_msg_count OUT NOCOPY NUMBER,
836 x_msg_data OUT NOCOPY VARCHAR2
837 )
838 IS
839 l_api_name VARCHAR2(255);
840 l_api_version_number NUMBER;
841 l_rt_interaction_id NUMBER;
842 l_message_id NUMBER;
843 l_mcp_action VARCHAR2(20);
844 l_inb_media_id NUMBER;
845 l_interaction_id NUMBER;
846 l_customer_id NUMBER;
847 l_contact_id NUMBER;
848 l_relationship_id NUMBER;
852 l_mcp_id NUMBER;
849 l_status VARCHAR2(300);
850 l_msg_count NUMBER;
851 l_msg_data VARCHAR2(300);
853
854 IEM_MDT_PROC_EX EXCEPTION;
855 begin
856
857 -- Standard Start of API savepoint
858 SAVEPOINT pushbackToRework_pvt;
859
860 -- Init values
861 l_api_name :='pushbackToRework';
862 l_api_version_number :=1.0;
863
864 -- Standard call to check for call compatibility.
865 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
866 1.0,
867 l_api_name,
868 G_PKG_NAME)
869 THEN
870 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
871 END IF;
872
873 -- Initialize message list if p_init_msg_list is set to TRUE.
874 IF FND_API.to_Boolean( p_init_msg_list )
875 THEN
876 FND_MSG_PUB.initialize;
877 END IF;
878
879 -- Initialize API return status to SUCCESS
880 x_return_status := FND_API.G_RET_STS_SUCCESS;
881
882 ----------------------code ------------------
883 for i in p_rt_media_item_ids.first..p_rt_media_item_ids.last loop
884
885 select rt_interaction_id into l_rt_interaction_id
886 from iem_rt_media_items
887 where rt_media_item_id = p_rt_media_item_ids(i).num;
888
889 select rti.mc_parameter_id, rti.interaction_id, rti.customer_id, rti.contact_id, rti.relationship_id
890 into l_mcp_id, l_interaction_id, l_customer_id, l_contact_id, l_relationship_id
891 from iem_rt_interactions rti
892 where rti.rt_interaction_id = l_rt_interaction_id;
893
894 l_mcp_action := null;
895 if ( l_mcp_id > 0 ) then
896 select mcp.action into l_mcp_action
897 from iem_mc_parameters mcp
898 where mcp.mc_parameter_id = l_mcp_id;
899 end if;
900
901 -- For auto-reply failed messages, reprocess the messages
902 -- and push them back to queues.
903 if (l_mcp_action = 'autoreply') then
904
905 select media_id
906 into l_inb_media_id
907 from iem_rt_media_items
908 where rt_interaction_id = l_rt_interaction_id
909 and email_type = 'I';
910
911 IEM_EMAIL_PROC_PVT. ReprocessAutoreply(
912 p_api_version_number => 1.0,
913 p_init_msg_list => FND_API.G_FALSE,
914 p_commit => FND_API.G_FALSE,
915 p_media_id => l_inb_media_id,
916 p_interaction_id => l_interaction_id,
917 p_customer_id => l_customer_id,
918 p_contact_id => l_contact_id,
919 p_relationship_id => l_relationship_id,
920 x_return_status => l_status,
921 x_msg_count => l_msg_count,
922 x_msg_data => l_msg_data);
923
924 if ( l_status = FND_API.G_RET_STS_ERROR ) then
925 raise IEM_MDT_PROC_EX;
926 end if;
927
928 update iem_rt_media_items set expire = G_EXPIRE
929 where rt_interaction_id = l_rt_interaction_id;
930
931 update iem_rt_interactions set expire = G_EXPIRE
932 where rt_interaction_id = l_rt_interaction_id;
933
934 update iem_outbox_errors set expire = G_EXPIRE
935 where rt_media_item_id in
936 (select rt_media_item_id from iem_rt_media_items
937 where rt_interaction_id = l_rt_interaction_id);
938
939 else
940
941 select message_id into l_message_id
942 from iem_rt_media_items
943 where rt_media_item_id = p_rt_media_item_ids(i).num and email_type = 'I';
944
945 update iem_rt_media_items set expire = G_ACTIVE, status = 'U'
946 where rt_interaction_id = l_rt_interaction_id and expire <> 'D';
947
948 if ( l_message_id > 0 ) then
949 update iem_rt_media_items set expire = G_EXPIRE
950 where message_id = l_message_id and expire = 'D';
951 end if;
952
953 update iem_rt_interactions set expire = G_ACTIVE
954 where rt_interaction_id = l_rt_interaction_id;
955
956 if ( l_message_id > 0 ) then
957 update iem_rt_proc_emails set queue_status = null
958 where message_id = l_message_id;
959 end if;
960
961 update iem_outbox_errors set expire = G_EXPIRE
962 where rt_media_item_id in
963 (select rt_media_item_id from iem_rt_media_items
964 where rt_interaction_id = l_rt_interaction_id);
965
966 end if; -- message not auto-replied
967 end loop;
968
969 --------------------------
970 -- Standard Check Of p_commit.
971 IF FND_API.To_Boolean(p_commit) THEN
972 COMMIT WORK;
973 END IF;
974 -- Standard callto get message count and if count is 1, get message info.
975 FND_MSG_PUB.Count_And_Get
976 ( p_encoded => FND_API.G_TRUE,
977 p_count => x_msg_count,
978 p_data => x_msg_data
979 );
980 EXCEPTION
981 WHEN IEM_MDT_PROC_EX THEN
982 ROLLBACK TO pushbackToRework_pvt;
983 x_return_status := l_status;
984 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
985 p_count => x_msg_count,
986 p_data => x_msg_data);
987
988 WHEN FND_API.G_EXC_ERROR THEN
989 ROLLBACK TO pushbackToRework_pvt;
990 x_return_status := FND_API.G_RET_STS_ERROR ;
991 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
992 p_count => x_msg_count,
993 p_data => x_msg_data);
994
995 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
996 ROLLBACK TO pushbackToRework_pvt;
997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
998 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
999 p_count => x_msg_count,
1000 p_data => x_msg_data);
1001
1002 WHEN OTHERS THEN
1003 ROLLBACK TO pushbackToRework_pvt;
1004 x_return_status := FND_API.G_RET_STS_ERROR;
1005 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1006 THEN
1007 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1008 END IF;
1009 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
1010 p_count => x_msg_count,
1011 p_data => x_msg_data);
1012
1013
1014 end;
1015
1016 end IEM_OP_ADMIN_PUB;