[Home] [Help]
PACKAGE BODY: APPS.IEM_PP_QUEUE_PVT
Source
1 PACKAGE BODY IEM_PP_QUEUE_PVT AS
2 /* $Header: iemvqueb.pls 120.5.12010000.2 2009/03/16 10:12:21 sanjrao 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 -- Changed the Query to delete all records with 'Q' status as well( to clean up iem_rt_pp_queues table)
385 -- for the bug 7494127, since those messages with status 'Q' are not present in Inbox.
386 --Delete the records with 'X', which would allow the DP to enqueue the message again.
387 delete IEM_RT_PP_QUEUE_DTLS where email_id in
388 ( select email_id from iem_rt_pp_queues where flag in ('S','Q','X')
389 and email_account_id=p_acct_id );
390 delete IEM_RT_PP_QUEUES where flag in ('S','Q','X') and email_account_id=p_acct_id ;
391
392 -- Standard Check Of p_commit.
393 IF FND_API.To_Boolean(p_commit) THEN
394 COMMIT WORK;
395 END IF;
396
397
398 -- Standard callto get message count and if count is 1, get message info.
399 FND_MSG_PUB.Count_And_Get
400 ( p_count => x_msg_count,
401 p_data => x_msg_data
402 );
403
404 EXCEPTION
405
406 WHEN FND_API.G_EXC_ERROR THEN
407 ROLLBACK TO create_item_tag_PVT;
408 x_return_status := FND_API.G_RET_STS_ERROR ;
409 FND_MSG_PUB.Count_And_Get
410
411 ( p_count => x_msg_count,
412 p_data => x_msg_data
413 );
414
415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
416 ROLLBACK TO create_item_tag_PVT;
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
418 FND_MSG_PUB.Count_And_Get
419 ( p_count => x_msg_count,
420 p_data => x_msg_data
421 );
422
423 WHEN OTHERS THEN
424 ROLLBACK TO create_item_tag_PVT;
425 x_return_status := FND_API.G_RET_STS_ERROR;
426 IF FND_MSG_PUB.Check_Msg_Level
427 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
428 THEN
429 FND_MSG_PUB.Add_Exc_Msg
430 ( G_PKG_NAME ,
431 l_api_name
432 );
433 END IF;
434
435 FND_MSG_PUB.Count_And_Get
436 ( p_count => x_msg_count,
437 p_data => x_msg_data
438
439 );
440
441 END expunge_queue;
442
443
444 Procedure get_queue_recs(
445 p_api_version_number IN NUMBER,
446 p_init_msg_list IN VARCHAR2 := null,
447 p_commit IN VARCHAR2 := null,
448 p_batch IN NUMBER,
449 x_pp_queue_ids OUT NOCOPY JTF_NUMBER_TABLE,
450 x_msg_uids OUT NOCOPY JTF_NUMBER_TABLE,
451 x_subjects OUT NOCOPY jtf_varchar2_Table_2000,
452 x_acct_id OUT NOCOPY NUMBER,
453 x_actions OUT NOCOPY JTF_NUMBER_TABLE,
454 x_rfc_msgids OUT NOCOPY jtf_varchar2_Table_300,
455 x_return_status OUT NOCOPY VARCHAR2,
456 x_msg_count OUT NOCOPY NUMBER,
457 x_msg_data OUT NOCOPY VARCHAR2
458 ) is
459
460 l_api_name VARCHAR2(255):='get_queue_recs';
461 l_api_version_number NUMBER:=1.0;
462 l_seq_id NUMBER;
463
464 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
465 l_msg_count NUMBER := 0;
466 l_msg_data VARCHAR2(2000);
467
468 l_queue_rec iem_rt_pp_queues%rowtype;
469 l_batch number;
470 i number;
471 l_pp_queue_ids JTF_NUMBER_TABLE := jtf_number_Table();
472 l_msg_uids JTF_NUMBER_TABLE := jtf_number_Table();
473 l_subjects jtf_varchar2_Table_2000 := jtf_varchar2_Table_2000();
474 l_actions JTF_NUMBER_TABLE := jtf_number_Table();
475 l_rfc_msgids jtf_varchar2_Table_300 := jtf_varchar2_Table_300();
476 l_acct_id number;
477
478 e_nowait EXCEPTION;
479 PRAGMA EXCEPTION_INIT(e_nowait, -54);
480
481 BEGIN
482 -- Standard Start of API savepoint
483 SAVEPOINT get_queue_rec_PVT;
484
485 -- Standard call to check for call compatibility.
486
487 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
488 p_api_version_number,
489 l_api_name,
490 G_PKG_NAME)
491 THEN
492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
493 END IF;
494
495
496 -- Initialize message list if p_init_msg_list is set to TRUE.
497 IF FND_API.to_Boolean( p_init_msg_list )
498 THEN
499 FND_MSG_PUB.initialize;
500 END IF;
501
502 -- Initialize API return status to SUCCESS
503 x_return_status := FND_API.G_RET_STS_SUCCESS;
504
505
506 i := 1;
507 for y in ( select email_account_id, count(*) total,
508 nvl(max(sysdate-creation_date),0) wait_time
509 from IEM_RT_PP_QUEUES
510 where flag = 'N' and retry_count < 5
511 group by email_account_id order by wait_time desc )
512 loop
513
514 l_acct_id := y.email_account_id;
515
516 FOR x in ( select email_id
517 from IEM_RT_PP_QUEUES
518 where flag = 'N' and retry_count < 5 and email_account_id=y.email_account_id
519 order by creation_date)
520 LOOP
521
522 BEGIN
523 select * into l_queue_rec from IEM_RT_PP_QUEUES
524 where email_id=x.email_id and flag = 'N' FOR UPDATE NOWAIT;
525
526 l_pp_queue_ids.extend(1);
527 l_msg_uids.extend(1);
528 l_subjects.extend(1);
529 l_actions.extend(1);
530 l_rfc_msgids.extend(1);
531
532 l_pp_queue_ids(i) := l_queue_rec.email_id;
533 l_msg_uids(i) := l_queue_rec.msg_uid;
534 l_subjects(i) := l_queue_rec.subject;
535 l_actions(i) := l_queue_rec.action;
536 l_rfc_msgids(i) := l_queue_rec.RFC822_message_id;
537
538 update IEM_RT_PP_QUEUES set flag ='A', retry_count=retry_count+1
539 where email_id=l_queue_rec.email_id;
540
541 i := i + 1;
542
543 if ( i > p_batch ) then
544 exit;
545 end if;
546
547 EXCEPTION when e_nowait then
548 null;
549 when others then
550 null;
551 END;
552
553 END LOOP;
554
555 if ( i > 1 ) then
556 exit;
557 end if;
558
559 end loop;
560
561 x_acct_id := l_acct_id;
562 x_pp_queue_ids := l_pp_queue_ids;
563 x_msg_uids := l_msg_uids;
564 x_subjects := l_subjects;
565 x_actions := l_actions;
566 x_rfc_msgids := l_rfc_msgids;
567
568 -- Standard Check Of p_commit.
569 IF FND_API.To_Boolean(p_commit) THEN
570 COMMIT WORK;
571 END IF;
572
573 -- Standard callto get message count and if count is 1, get message info.
574 FND_MSG_PUB.Count_And_Get
575 ( p_count => x_msg_count,
576 p_data => x_msg_data
577 );
578 EXCEPTION
579 WHEN FND_API.G_EXC_ERROR THEN
580 ROLLBACK TO get_queue_rec_PVT;
581 x_return_status := FND_API.G_RET_STS_ERROR ;
582 FND_MSG_PUB.Count_And_Get
583
584 ( p_count => x_msg_count,
585 p_data => x_msg_data
586 );
587
588 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
589 ROLLBACK TO get_queue_rec_PVT;
590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
591 FND_MSG_PUB.Count_And_Get
592 ( p_count => x_msg_count,
593 p_data => x_msg_data
594 );
595
596 WHEN OTHERS THEN
597 ROLLBACK TO get_queue_rec_PVT;
598 x_return_status := FND_API.G_RET_STS_ERROR;
599 IF FND_MSG_PUB.Check_Msg_Level
600 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
601 THEN
602 FND_MSG_PUB.Add_Exc_Msg
603 ( G_PKG_NAME ,
604 l_api_name
605 );
606 END IF;
607
608 FND_MSG_PUB.Count_And_Get
609 ( p_count => x_msg_count,
610 p_data => x_msg_data
611
612 );
613 END;
614
615
616 PROCEDURE mark_flags (
617 p_api_version_number IN NUMBER,
618 p_init_msg_list IN VARCHAR2 := null,
619 p_commit IN VARCHAR2 := null,
620 p_flag IN VARCHAR2,
621 p_queue_ids IN jtf_varchar2_Table_100,
622 x_return_status OUT NOCOPY VARCHAR2,
623 x_msg_count OUT NOCOPY NUMBER,
624 x_msg_data OUT NOCOPY VARCHAR2
625 ) is
626 l_api_name VARCHAR2(255):='mark_flags';
627 l_api_version_number NUMBER:=1.0;
628 l_seq_id NUMBER;
629 l_count NUMBER;
630
631 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
632 l_msg_count NUMBER := 0;
633 l_msg_data VARCHAR2(2000);
634
635 BEGIN
636 -- Standard Start of API savepoint
637 SAVEPOINT create_item_tag_PVT;
638
639 -- Standard call to check for call compatibility.
640
641 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
642 p_api_version_number,
643 l_api_name,
644 G_PKG_NAME)
645 THEN
646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
647 END IF;
648
649
650 -- Initialize message list if p_init_msg_list is set to TRUE.
651 IF FND_API.to_Boolean( p_init_msg_list )
652 THEN
653 FND_MSG_PUB.initialize;
654 END IF;
655
656 -- Initialize API return status to SUCCESS
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658
659 --begins here
660 For j in 1..p_queue_ids.count loop
661
662 select count(*) into l_count from iem_rt_pp_queues
663 where EMAIL_ID= p_queue_ids(j) and retry_count > 4;
664
665 if ( l_count > 0 ) then
666 update iem_rt_pp_queues set flag=p_flag where EMAIL_ID= p_queue_ids(j);
667 else
668 update iem_rt_pp_queues set flag='N' where EMAIL_ID= p_queue_ids(j);
669 end if;
670
671 end loop;
672
673 -- Standard callto get message count and if count is 1, get message info.
674 FND_MSG_PUB.Count_And_Get
675 ( p_count => x_msg_count,
676 p_data => x_msg_data
677 );
678
679 EXCEPTION
680
681 WHEN FND_API.G_EXC_ERROR THEN
682 ROLLBACK TO create_item_tag_PVT;
683 x_return_status := FND_API.G_RET_STS_ERROR ;
684 FND_MSG_PUB.Count_And_Get
685
686 ( p_count => x_msg_count,
687 p_data => x_msg_data
688 );
689
690 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
691 ROLLBACK TO create_item_tag_PVT;
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
693 FND_MSG_PUB.Count_And_Get
694 ( p_count => x_msg_count,
695 p_data => x_msg_data
696 );
697
698 WHEN OTHERS THEN
699 ROLLBACK TO create_item_tag_PVT;
700 x_return_status := FND_API.G_RET_STS_ERROR;
701 IF FND_MSG_PUB.Check_Msg_Level
702 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
703 THEN
704 FND_MSG_PUB.Add_Exc_Msg
705 ( G_PKG_NAME ,
706 l_api_name
707 );
708 END IF;
709
710 FND_MSG_PUB.Count_And_Get
711 ( p_count => x_msg_count,
712 p_data => x_msg_data
713
714 );
715
716 END mark_flags;
717
718 PROCEDURE reset_data (
719 p_api_version_number IN NUMBER,
720 p_init_msg_list IN VARCHAR2 := null,
721 p_commit IN VARCHAR2 := null,
722 x_return_status OUT NOCOPY VARCHAR2,
723 x_msg_count OUT NOCOPY NUMBER,
724 x_msg_data OUT NOCOPY VARCHAR2
725 ) is
726 l_api_name VARCHAR2(255):='reset_data';
727 l_api_version_number NUMBER:=1.0;
728 l_seq_id NUMBER;
729 l_count NUMBER;
730
731 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
732 l_msg_count NUMBER := 0;
733 l_msg_data VARCHAR2(2000);
734
735 BEGIN
736 -- Standard Start of API savepoint
737 SAVEPOINT reset_data_PVT;
738
739 -- Standard call to check for call compatibility.
740
741 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
742 p_api_version_number,
743 l_api_name,
744 G_PKG_NAME)
745 THEN
746 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
747 END IF;
748
749
750 -- Initialize message list if p_init_msg_list is set to TRUE.
751 IF FND_API.to_Boolean( p_init_msg_list )
752 THEN
753 FND_MSG_PUB.initialize;
754 END IF;
755
756 -- Initialize API return status to SUCCESS
757 x_return_status := FND_API.G_RET_STS_SUCCESS;
758
759 --begins here
760 update iem_rt_pp_queues set flag='N' where flag='A' and retry_count<=4;
761
762 -- Standard callto get message count and if count is 1, get message info.
763 FND_MSG_PUB.Count_And_Get
764 ( p_count => x_msg_count,
765 p_data => x_msg_data
766 );
767
768 EXCEPTION
769
770 WHEN FND_API.G_EXC_ERROR THEN
771 ROLLBACK TO create_item_tag_PVT;
772 x_return_status := FND_API.G_RET_STS_ERROR ;
773 FND_MSG_PUB.Count_And_Get
774
775 ( p_count => x_msg_count,
776 p_data => x_msg_data
777 );
778
779 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
780 ROLLBACK TO create_item_tag_PVT;
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
782 FND_MSG_PUB.Count_And_Get
783 ( p_count => x_msg_count,
784 p_data => x_msg_data
785 );
786
787 WHEN OTHERS THEN
788 ROLLBACK TO create_item_tag_PVT;
789 x_return_status := FND_API.G_RET_STS_ERROR;
790 IF FND_MSG_PUB.Check_Msg_Level
791 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
792 THEN
793 FND_MSG_PUB.Add_Exc_Msg
794 ( G_PKG_NAME ,
795 l_api_name
796 );
797 END IF;
798
799 FND_MSG_PUB.Count_And_Get
800 ( p_count => x_msg_count,
801 p_data => x_msg_data
802
803 );
804
805 END reset_data;
806
807 END IEM_PP_QUEUE_PVT;