[Home] [Help]
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
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,
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
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
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;
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;
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')
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.
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;
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();
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:
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:
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);
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:
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);
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:
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:
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,