[Home] [Help]
PACKAGE BODY: APPS.IEM_PP_QUEUE_PVT
Source
1 PACKAGE BODY IEM_PP_QUEUE_PVT AS
2 /* $Header: iemvqueb.pls 120.5 2007/12/07 21:58:20 rtripath ship $ */
3
4 -- file name: iemvqueb.pls
5 --
6 -- Purpose: EMTA runtime queue management
7 --
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- Liang Xia 3/20/2003 Created
11 -- Liang Xia 01/29/2004 Enlarged the size of name, value in create_headers.
12 -- Liang Xia 10/13/2004 Added x_subject for get_queue_rec
13 -- Liang Xia 11/02/2004 get Action from queue
14 -- Liang Xia 01/20/2005 Added expunge_queue
15 -- Liang Xia 05/20/2005 changed signature of expunge_queue
16 -- Liang Xia 05/20/2005 changed signature of create_pp_queue by adding RFC822_msgID
17 -- received_date
18 -- Liang Xia 07/25/2005 Remove queue data without delay, batch operation
19 -- Ranjan 11/17/2005 Restrict RFC822 to varchar2(256) while inserting bug 6633789
20 -- --------- ------ ------------------------------------------
21
22 -- Enter procedure, function bodies as shown below
23 G_PKG_NAME CONSTANT varchar2(30) :='IEM_PP_QUEUE_PVT ';
24 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
25 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID') ) ;
26
27 PROCEDURE create_pp_queue (
28 p_api_version_number IN NUMBER,
29 p_init_msg_list IN VARCHAR2 := null,
30 p_commit IN VARCHAR2 := null,
31 p_msg_uid IN NUMBER,
32 p_email_acct_id IN NUMBER,
33 p_subject IN VARCHAR2,
34 p_from IN varchar2,
35 p_size IN NUMBER,
36 p_flag IN VARCHAR2,
37 p_retry_count IN NUMBER,
38 p_attach_name_tbl IN JTF_VARCHAR2_TABLE_300,
39 p_attach_size_tbl IN JTF_VARCHAR2_TABLE_300,
40 p_attach_type_tbl IN JTF_VARCHAR2_TABLE_300,
41 p_rfc822_msgId IN VARCHAR2,
42 p_received_date IN DATE,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_msg_data OUT NOCOPY VARCHAR2
46 ) is
47 l_api_name VARCHAR2(255):='create_pp_queue';
48 l_api_version_number NUMBER:=1.0;
49 l_seq_id NUMBER;
50
51 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
52 l_msg_count NUMBER := 0;
53 l_msg_data VARCHAR2(2000);
54 i INTEGER;
55 l_action number :=1;
56
57
58 BEGIN
59 -- Standard Start of API savepoint
60 SAVEPOINT create_item_tag_PVT;
61
62 -- Standard call to check for call compatibility.
63
64 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
65 p_api_version_number,
66 l_api_name,
67 G_PKG_NAME)
68 THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71
72
73 -- Initialize message list if p_init_msg_list is set to TRUE.
74 IF FND_API.to_Boolean( p_init_msg_list )
75 THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78
79 -- Initialize API return status to SUCCESS
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 --begins here
83
84 --get next sequential number for msg_meta_id
85 SELECT IEM_RT_PP_QUEUES_s1.nextval
86 INTO l_seq_id
87 FROM dual;
88
89 INSERT INTO IEM_RT_PP_QUEUES
90 (
91 EMAIL_ID,
92 MSG_UID,
93 EMAIL_ACCOUNT_ID,
94 SUBJECT,
95 FROM_ADDRESS,
96 MSG_SIZE ,
97 FLAG,
98 RETRY_COUNT,
99 ACTION,
100 RFC822_MESSAGE_ID,
101 RECEIVED_DATE,
102 CREATED_BY,
103 CREATION_DATE,
104 LAST_UPDATED_BY,
105 LAST_UPDATE_DATE,
106 LAST_UPDATE_LOGIN
107 )
108 VALUES
109 (
110 l_seq_id,
111 p_msg_uid,
112 p_email_acct_id,
113 p_subject,
114 p_from,
115 p_size,
116 p_flag,
117 p_retry_count,
118 l_action,
119 substr(p_rfc822_msgId,1,256), -- Fix for bug 6633789
120 p_received_date,
121 decode(G_created_updated_by,null,-1,G_created_updated_by),
122 sysdate,
123 decode(G_created_updated_by,null,-1,G_created_updated_by),
124 sysdate,
125 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
126 );
127
128 if (p_attach_name_tbl.FIRST is not null) then
129
130 FOR i in p_attach_name_tbl.FIRST..p_attach_name_tbl.LAST LOOP
131
132 INSERT INTO IEM_RT_PP_QUEUE_DTLS
133 (
134 EMAIL_ID,
135 ATTACHMENT_NAME,
136 ATTACHMENT_SIZE,
137 ATTACHMENT_TYPE,
138 CREATED_BY,
139 CREATION_DATE,
140 LAST_UPDATED_BY,
141 LAST_UPDATE_DATE,
142 LAST_UPDATE_LOGIN
143 )
144 VALUES
145 (
146 l_seq_id,
147 p_attach_name_tbl(i),
148 decode(p_attach_size_tbl(i), null, 0, to_number(p_attach_size_tbl(i))),
149 p_attach_type_tbl(i),
150 decode(G_created_updated_by,null,-1,G_created_updated_by),
151 sysdate,
152 decode(G_created_updated_by,null,-1,G_created_updated_by),
153 sysdate,
154 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
155 );
156
157
158 END LOOP;
159
160
161 end if; -- FIRST is not null
162
163 -- Standard Check Of p_commit.
164 IF FND_API.To_Boolean(p_commit) THEN
165 COMMIT WORK;
166 END IF;
167
168
169 -- Standard callto get message count and if count is 1, get message info.
170 FND_MSG_PUB.Count_And_Get
171 ( p_count => x_msg_count,
172 p_data => x_msg_data
173 );
174
175 EXCEPTION
176
177 WHEN FND_API.G_EXC_ERROR THEN
178 ROLLBACK TO create_item_tag_PVT;
179 x_return_status := FND_API.G_RET_STS_ERROR ;
180 FND_MSG_PUB.Count_And_Get
181
182 ( p_count => x_msg_count,
183 p_data => x_msg_data
184 );
185
186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
187 ROLLBACK TO create_item_tag_PVT;
188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
189 FND_MSG_PUB.Count_And_Get
190 ( p_count => x_msg_count,
191 p_data => x_msg_data
192 );
193
194 WHEN OTHERS THEN
195 ROLLBACK TO create_item_tag_PVT;
196 x_return_status := FND_API.G_RET_STS_ERROR;
197 IF FND_MSG_PUB.Check_Msg_Level
198 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
199 THEN
200 FND_MSG_PUB.Add_Exc_Msg
201 ( G_PKG_NAME ,
202 l_api_name
203 );
204 END IF;
205
206 FND_MSG_PUB.Count_And_Get
207 ( p_count => x_msg_count,
208 p_data => x_msg_data
209
210 );
211
212 END create_pp_queue;
213
214
215 Procedure get_queue_rec(
216 p_api_version_number IN NUMBER,
217 p_init_msg_list IN VARCHAR2 := null,
218 p_commit IN VARCHAR2 := null,
219 x_pp_queue_id OUT NOCOPY NUMBER,
220 x_msg_uid OUT NOCOPY NUMBER,
221 x_subject OUT NOCOPY VARCHAR2,
222 x_acct_id OUT NOCOPY NUMBER,
223 x_action OUT NOCOPY NUMBER,
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_msg_count OUT NOCOPY NUMBER,
226 x_msg_data OUT NOCOPY VARCHAR2
227 ) is
228
229 l_api_name VARCHAR2(255):='create_item_tag';
230 l_api_version_number NUMBER:=1.0;
231 l_seq_id NUMBER;
232
233 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
234 l_msg_count NUMBER := 0;
235 l_msg_data VARCHAR2(2000);
236
237 l_queue_rec iem_rt_pp_queues%rowtype;
238
239 e_nowait EXCEPTION;
240 PRAGMA EXCEPTION_INIT(e_nowait, -54);
241 BEGIN
242 -- Standard Start of API savepoint
243 SAVEPOINT get_queue_rec_PVT;
244
245 -- Standard call to check for call compatibility.
246
247 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
248 p_api_version_number,
249 l_api_name,
250 G_PKG_NAME)
251 THEN
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253 END IF;
254
255
256 -- Initialize message list if p_init_msg_list is set to TRUE.
257 IF FND_API.to_Boolean( p_init_msg_list )
258 THEN
259 FND_MSG_PUB.initialize;
260 END IF;
261
262 -- Initialize API return status to SUCCESS
263 x_return_status := FND_API.G_RET_STS_SUCCESS;
264
265 for x in ( select email_id
266 from IEM_RT_PP_QUEUES
267 where flag = 'N' and retry_count < 5
268 order by creation_date)
269 LOOP
270 BEGIN
271 select * into l_queue_rec from IEM_RT_PP_QUEUES
272 where email_id=x.email_id and flag = 'N' FOR UPDATE NOWAIT;
273 exit;
274 EXCEPTION when e_nowait then
275 null;
276 when others then
277 null ;
278 END;
279 END LOOP;
280
281
282 IF ( l_queue_rec.email_id is not null and l_queue_rec.msg_uid is not null
283 and l_queue_rec.email_account_id is not null ) then
284
285 update IEM_RT_PP_QUEUES set flag ='A', retry_count=retry_count+1 where email_id=l_queue_rec.email_id;
286
287 x_pp_queue_id := l_queue_rec.email_id;
288 x_msg_uid :=l_queue_rec.msg_uid;
289 x_acct_id := l_queue_rec.email_account_id;
290 x_subject := l_queue_rec.subject;
291 x_action := l_queue_rec.action;
292 END IF;
293
294 -- Standard Check Of p_commit.
295 IF FND_API.To_Boolean(p_commit) THEN
296 COMMIT WORK;
297 END IF;
298
299 -- Standard callto get message count and if count is 1, get message info.
300 FND_MSG_PUB.Count_And_Get
301 ( p_count => x_msg_count,
302 p_data => x_msg_data
303 );
304 EXCEPTION
305 WHEN FND_API.G_EXC_ERROR THEN
306 ROLLBACK TO get_queue_rec_PVT;
307 x_return_status := FND_API.G_RET_STS_ERROR ;
308 FND_MSG_PUB.Count_And_Get
309
310 ( p_count => x_msg_count,
311 p_data => x_msg_data
312 );
313
314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
315 ROLLBACK TO get_queue_rec_PVT;
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
317 FND_MSG_PUB.Count_And_Get
318 ( p_count => x_msg_count,
319 p_data => x_msg_data
320 );
321
322 WHEN OTHERS THEN
323 ROLLBACK TO get_queue_rec_PVT;
324 x_return_status := FND_API.G_RET_STS_ERROR;
325 IF FND_MSG_PUB.Check_Msg_Level
326 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
327 THEN
328 FND_MSG_PUB.Add_Exc_Msg
329 ( G_PKG_NAME ,
330 l_api_name
331 );
332 END IF;
333
334 FND_MSG_PUB.Count_And_Get
335 ( p_count => x_msg_count,
336 p_data => x_msg_data
337
338 );
339 END;
340
341 PROCEDURE expunge_queue (
342 p_api_version_number IN NUMBER,
343 p_init_msg_list IN VARCHAR2 := null,
344 p_commit IN VARCHAR2 := null,
345 p_acct_id IN VARCHAR2,
346 x_return_status OUT NOCOPY VARCHAR2,
347 x_msg_count OUT NOCOPY NUMBER,
348 x_msg_data OUT NOCOPY VARCHAR2
349 ) is
350 l_api_name VARCHAR2(255):='expunge_queue';
351 l_api_version_number NUMBER:=1.0;
352 l_seq_id NUMBER;
353
354 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
355 l_msg_count NUMBER := 0;
356 l_msg_data VARCHAR2(2000);
357
358 BEGIN
359 -- Standard Start of API savepoint
360 SAVEPOINT create_item_tag_PVT;
361
362 -- Standard call to check for call compatibility.
363
364 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
365 p_api_version_number,
366 l_api_name,
367 G_PKG_NAME)
368 THEN
369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
370 END IF;
371
372
373 -- Initialize message list if p_init_msg_list is set to TRUE.
374 IF FND_API.to_Boolean( p_init_msg_list )
375 THEN
376 FND_MSG_PUB.initialize;
377 END IF;
378
379 -- Initialize API return status to SUCCESS
380 x_return_status := FND_API.G_RET_STS_SUCCESS;
381
382 --begins here
383
384 delete IEM_RT_PP_QUEUE_DTLS where email_id in
385 ( select email_id from iem_rt_pp_queues where flag = 'S'
386 and email_account_id=p_acct_id );
387
388 delete IEM_RT_PP_QUEUES where flag = 'S' and email_account_id=p_acct_id ;
389
390
391 -- Standard Check Of p_commit.
392 IF FND_API.To_Boolean(p_commit) THEN
393 COMMIT WORK;
394 END IF;
395
396
397 -- Standard callto get message count and if count is 1, get message info.
398 FND_MSG_PUB.Count_And_Get
399 ( p_count => x_msg_count,
400 p_data => x_msg_data
401 );
402
403 EXCEPTION
404
405 WHEN FND_API.G_EXC_ERROR THEN
406 ROLLBACK TO create_item_tag_PVT;
407 x_return_status := FND_API.G_RET_STS_ERROR ;
408 FND_MSG_PUB.Count_And_Get
409
410 ( p_count => x_msg_count,
411 p_data => x_msg_data
412 );
413
414 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
415 ROLLBACK TO create_item_tag_PVT;
416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
417 FND_MSG_PUB.Count_And_Get
421
418 ( p_count => x_msg_count,
419 p_data => x_msg_data
420 );
422 WHEN OTHERS THEN
423 ROLLBACK TO create_item_tag_PVT;
424 x_return_status := FND_API.G_RET_STS_ERROR;
425 IF FND_MSG_PUB.Check_Msg_Level
426 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
427 THEN
428 FND_MSG_PUB.Add_Exc_Msg
429 ( G_PKG_NAME ,
430 l_api_name
431 );
432 END IF;
433
434 FND_MSG_PUB.Count_And_Get
435 ( p_count => x_msg_count,
436 p_data => x_msg_data
437
438 );
439
440 END expunge_queue;
441
442
443 Procedure get_queue_recs(
444 p_api_version_number IN NUMBER,
445 p_init_msg_list IN VARCHAR2 := null,
446 p_commit IN VARCHAR2 := null,
447 p_batch IN NUMBER,
448 x_pp_queue_ids OUT NOCOPY JTF_NUMBER_TABLE,
449 x_msg_uids OUT NOCOPY JTF_NUMBER_TABLE,
450 x_subjects OUT NOCOPY jtf_varchar2_Table_2000,
451 x_acct_id OUT NOCOPY NUMBER,
452 x_actions OUT NOCOPY JTF_NUMBER_TABLE,
453 x_rfc_msgids OUT NOCOPY jtf_varchar2_Table_300,
454 x_return_status OUT NOCOPY VARCHAR2,
455 x_msg_count OUT NOCOPY NUMBER,
456 x_msg_data OUT NOCOPY VARCHAR2
457 ) is
458
459 l_api_name VARCHAR2(255):='get_queue_recs';
460 l_api_version_number NUMBER:=1.0;
461 l_seq_id NUMBER;
462
463 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
464 l_msg_count NUMBER := 0;
465 l_msg_data VARCHAR2(2000);
466
467 l_queue_rec iem_rt_pp_queues%rowtype;
468 l_batch number;
469 i number;
470 l_pp_queue_ids JTF_NUMBER_TABLE := jtf_number_Table();
471 l_msg_uids JTF_NUMBER_TABLE := jtf_number_Table();
472 l_subjects jtf_varchar2_Table_2000 := jtf_varchar2_Table_2000();
473 l_actions JTF_NUMBER_TABLE := jtf_number_Table();
474 l_rfc_msgids jtf_varchar2_Table_300 := jtf_varchar2_Table_300();
475 l_acct_id number;
476
477 e_nowait EXCEPTION;
478 PRAGMA EXCEPTION_INIT(e_nowait, -54);
479
480 BEGIN
481 -- Standard Start of API savepoint
482 SAVEPOINT get_queue_rec_PVT;
483
484 -- Standard call to check for call compatibility.
485
486 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
487 p_api_version_number,
488 l_api_name,
489 G_PKG_NAME)
490 THEN
491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
492 END IF;
493
494
495 -- Initialize message list if p_init_msg_list is set to TRUE.
496 IF FND_API.to_Boolean( p_init_msg_list )
497 THEN
498 FND_MSG_PUB.initialize;
499 END IF;
500
501 -- Initialize API return status to SUCCESS
502 x_return_status := FND_API.G_RET_STS_SUCCESS;
503
504
505 i := 1;
506 for y in ( select email_account_id, count(*) total,
507 nvl(max(sysdate-creation_date),0) wait_time
508 from IEM_RT_PP_QUEUES
509 where flag = 'N' and retry_count < 5
510 group by email_account_id order by wait_time desc )
511 loop
512
513 l_acct_id := y.email_account_id;
514
515 FOR x in ( select email_id
516 from IEM_RT_PP_QUEUES
517 where flag = 'N' and retry_count < 5 and email_account_id=y.email_account_id
518 order by creation_date)
519 LOOP
520
521 BEGIN
522 select * into l_queue_rec from IEM_RT_PP_QUEUES
523 where email_id=x.email_id and flag = 'N' FOR UPDATE NOWAIT;
524
525 l_pp_queue_ids.extend(1);
526 l_msg_uids.extend(1);
527 l_subjects.extend(1);
528 l_actions.extend(1);
529 l_rfc_msgids.extend(1);
530
531 l_pp_queue_ids(i) := l_queue_rec.email_id;
532 l_msg_uids(i) := l_queue_rec.msg_uid;
533 l_subjects(i) := l_queue_rec.subject;
534 l_actions(i) := l_queue_rec.action;
535 l_rfc_msgids(i) := l_queue_rec.RFC822_message_id;
536
537 update IEM_RT_PP_QUEUES set flag ='A', retry_count=retry_count+1
538 where email_id=l_queue_rec.email_id;
539
540 i := i + 1;
541
542 if ( i > p_batch ) then
543 exit;
544 end if;
545
546 EXCEPTION when e_nowait then
547 null;
548 when others then
549 null;
550 END;
551
552 END LOOP;
553
554 if ( i > 1 ) then
555 exit;
556 end if;
557
558 end loop;
559
560 x_acct_id := l_acct_id;
561 x_pp_queue_ids := l_pp_queue_ids;
562 x_msg_uids := l_msg_uids;
563 x_subjects := l_subjects;
564 x_actions := l_actions;
565 x_rfc_msgids := l_rfc_msgids;
566
567 -- Standard Check Of p_commit.
568 IF FND_API.To_Boolean(p_commit) THEN
569 COMMIT WORK;
570 END IF;
571
575 p_data => x_msg_data
572 -- Standard callto get message count and if count is 1, get message info.
573 FND_MSG_PUB.Count_And_Get
574 ( p_count => x_msg_count,
576 );
577 EXCEPTION
578 WHEN FND_API.G_EXC_ERROR THEN
579 ROLLBACK TO get_queue_rec_PVT;
580 x_return_status := FND_API.G_RET_STS_ERROR ;
581 FND_MSG_PUB.Count_And_Get
582
583 ( p_count => x_msg_count,
584 p_data => x_msg_data
585 );
586
587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
588 ROLLBACK TO get_queue_rec_PVT;
589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
590 FND_MSG_PUB.Count_And_Get
591 ( p_count => x_msg_count,
592 p_data => x_msg_data
593 );
594
595 WHEN OTHERS THEN
596 ROLLBACK TO get_queue_rec_PVT;
597 x_return_status := FND_API.G_RET_STS_ERROR;
598 IF FND_MSG_PUB.Check_Msg_Level
599 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
600 THEN
601 FND_MSG_PUB.Add_Exc_Msg
602 ( G_PKG_NAME ,
603 l_api_name
604 );
605 END IF;
606
607 FND_MSG_PUB.Count_And_Get
608 ( p_count => x_msg_count,
609 p_data => x_msg_data
610
611 );
612 END;
613
614
615 PROCEDURE mark_flags (
616 p_api_version_number IN NUMBER,
617 p_init_msg_list IN VARCHAR2 := null,
618 p_commit IN VARCHAR2 := null,
619 p_flag IN VARCHAR2,
620 p_queue_ids IN jtf_varchar2_Table_100,
621 x_return_status OUT NOCOPY VARCHAR2,
622 x_msg_count OUT NOCOPY NUMBER,
623 x_msg_data OUT NOCOPY VARCHAR2
624 ) is
625 l_api_name VARCHAR2(255):='mark_flags';
626 l_api_version_number NUMBER:=1.0;
627 l_seq_id NUMBER;
628 l_count NUMBER;
629
630 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
631 l_msg_count NUMBER := 0;
632 l_msg_data VARCHAR2(2000);
633
634 BEGIN
635 -- Standard Start of API savepoint
636 SAVEPOINT create_item_tag_PVT;
637
638 -- Standard call to check for call compatibility.
639
640 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
641 p_api_version_number,
642 l_api_name,
643 G_PKG_NAME)
644 THEN
645 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
646 END IF;
647
648
649 -- Initialize message list if p_init_msg_list is set to TRUE.
650 IF FND_API.to_Boolean( p_init_msg_list )
651 THEN
652 FND_MSG_PUB.initialize;
653 END IF;
654
655 -- Initialize API return status to SUCCESS
656 x_return_status := FND_API.G_RET_STS_SUCCESS;
657
658 --begins here
659 For j in 1..p_queue_ids.count loop
660
661 select count(*) into l_count from iem_rt_pp_queues
662 where EMAIL_ID= p_queue_ids(j) and retry_count > 4;
663
664 if ( l_count > 0 ) then
665 update iem_rt_pp_queues set flag=p_flag where EMAIL_ID= p_queue_ids(j);
666 else
667 update iem_rt_pp_queues set flag='N' where EMAIL_ID= p_queue_ids(j);
668 end if;
669
670 end loop;
671
672 -- Standard callto get message count and if count is 1, get message info.
673 FND_MSG_PUB.Count_And_Get
674 ( p_count => x_msg_count,
675 p_data => x_msg_data
676 );
677
678 EXCEPTION
679
680 WHEN FND_API.G_EXC_ERROR THEN
681 ROLLBACK TO create_item_tag_PVT;
682 x_return_status := FND_API.G_RET_STS_ERROR ;
683 FND_MSG_PUB.Count_And_Get
684
685 ( p_count => x_msg_count,
686 p_data => x_msg_data
687 );
688
689 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
690 ROLLBACK TO create_item_tag_PVT;
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
692 FND_MSG_PUB.Count_And_Get
693 ( p_count => x_msg_count,
694 p_data => x_msg_data
695 );
696
697 WHEN OTHERS THEN
698 ROLLBACK TO create_item_tag_PVT;
699 x_return_status := FND_API.G_RET_STS_ERROR;
700 IF FND_MSG_PUB.Check_Msg_Level
701 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
702 THEN
703 FND_MSG_PUB.Add_Exc_Msg
704 ( G_PKG_NAME ,
705 l_api_name
706 );
707 END IF;
708
709 FND_MSG_PUB.Count_And_Get
710 ( p_count => x_msg_count,
711 p_data => x_msg_data
712
713 );
714
715 END mark_flags;
716
717 PROCEDURE reset_data (
718 p_api_version_number IN NUMBER,
719 p_init_msg_list IN VARCHAR2 := null,
720 p_commit IN VARCHAR2 := null,
721 x_return_status OUT NOCOPY VARCHAR2,
722 x_msg_count OUT NOCOPY NUMBER,
723 x_msg_data OUT NOCOPY VARCHAR2
724 ) is
725 l_api_name VARCHAR2(255):='reset_data';
726 l_api_version_number NUMBER:=1.0;
727 l_seq_id NUMBER;
728 l_count NUMBER;
729
730 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
731 l_msg_count NUMBER := 0;
732 l_msg_data VARCHAR2(2000);
733
734 BEGIN
735 -- Standard Start of API savepoint
736 SAVEPOINT reset_data_PVT;
737
738 -- Standard call to check for call compatibility.
739
740 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
741 p_api_version_number,
742 l_api_name,
743 G_PKG_NAME)
744 THEN
745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
746 END IF;
747
748
749 -- Initialize message list if p_init_msg_list is set to TRUE.
750 IF FND_API.to_Boolean( p_init_msg_list )
751 THEN
752 FND_MSG_PUB.initialize;
753 END IF;
754
755 -- Initialize API return status to SUCCESS
756 x_return_status := FND_API.G_RET_STS_SUCCESS;
757
758 --begins here
759 update iem_rt_pp_queues set flag='N' where flag='A' and retry_count<=4;
760
761 -- Standard callto get message count and if count is 1, get message info.
762 FND_MSG_PUB.Count_And_Get
763 ( p_count => x_msg_count,
764 p_data => x_msg_data
765 );
766
767 EXCEPTION
768
769 WHEN FND_API.G_EXC_ERROR THEN
770 ROLLBACK TO create_item_tag_PVT;
771 x_return_status := FND_API.G_RET_STS_ERROR ;
772 FND_MSG_PUB.Count_And_Get
773
774 ( p_count => x_msg_count,
775 p_data => x_msg_data
776 );
777
778 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
779 ROLLBACK TO create_item_tag_PVT;
780 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
781 FND_MSG_PUB.Count_And_Get
782 ( p_count => x_msg_count,
783 p_data => x_msg_data
784 );
785
786 WHEN OTHERS THEN
787 ROLLBACK TO create_item_tag_PVT;
788 x_return_status := FND_API.G_RET_STS_ERROR;
789 IF FND_MSG_PUB.Check_Msg_Level
790 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
791 THEN
792 FND_MSG_PUB.Add_Exc_Msg
793 ( G_PKG_NAME ,
794 l_api_name
795 );
796 END IF;
797
798 FND_MSG_PUB.Count_And_Get
799 ( p_count => x_msg_count,
800 p_data => x_msg_data
801
802 );
803
804 END reset_data;
805
806 END IEM_PP_QUEUE_PVT;