[Home] [Help]
116: for v3 in c_agent LOOP
117: if l_folder_type='I' then
118: -- this how you get the count for the inbox of agent
119: select count(*) into l_total_count
120: from iem_post_mdts where email_account_id=l_email_account_id and agent_id=v3.resource_id ;
121: else
122: -- this how you get the count for the draft folder of the agent
123: select count(a.rt_media_item_id)
124: into l_total_count
124: into l_total_count
125: from iem_rt_media_items a, iem_msg_parts part
126: where a.rt_interaction_id in (select rtm.rt_interaction_id
127: from iem_rt_media_items rtm, iem_rt_interactions rti
128: where rtm.message_id in (select message_id from iem_post_mdts
129: where email_account_id = l_email_account_id and agent_id = v3.resource_id)
130: and rtm.email_type ='I' and rtm.rt_interaction_id = rti.rt_interaction_id
131: and rti.expire = 'N') and a.email_type = 'O' and a.folder_uid > 0
132: and a.folder_name = 'Drafts' and part.ref_key = a.rt_media_item_id
193: l_class_id:=substr(x_folder_tbl(i),instr(x_folder_tbl(i),',',1)+1);
194: l_folder_name:=substr(x_folder_tbl(i),1,instr(x_folder_tbl(i),',',1)-1);
195: l_folder_type:='Q';
196: select count(*) into l_total_count
197: from iem_post_mdts where email_account_id=l_email_account_id
198: and rt_classification_id=l_class_id and agent_id=0;
199: l_status_text:='Succesfully Count for Folder ';
200: l_status:='S';
201: end if;
247: l_mail_type number;
248: l_received_Date date;
249: l_folder_type varchar2(10);
250: l_priority number;
251: l_post_rec iem_post_mdts%rowtype;
252: l_ret_status varchar2(10);
253: l_msg_count number;
254: l_msg_Data varchar2(250);
255: l_message_id number;
448: --siahmed fix the cursor issue here just as before where ih_media_item_id could be null
449: elsif l_folder_type in ('Q','I') then -- Queued/Acquired Message
450: for v1 in c1 LOOP
451: BEGIN
452: select * into l_post_rec from iem_post_mdts
453: where message_id=v1.message_id;
454: if l_post_rec.priority='High' then
455: l_priority:=2;
456: elsif l_post_rec.priority='Low' then
539: l_error_text varchar2(500);
540: l_commit_cntr number := 0;
541: l_direction varchar2(100);
542: cursor c_queue is
543: select a.* from iem_post_mdts a,iem_route_classifications b
544: where a.email_account_id=l_email_account_id and a.agent_id=0
545: and a.rt_classification_id=b.route_classification_id and
546: b.name=l_folder_name and a.message_id not in (select message_id from iem_migration_store_temp
547: where migration_id=p_migration_id)
545: and a.rt_classification_id=b.route_classification_id and
546: b.name=l_folder_name and a.message_id not in (select message_id from iem_migration_store_temp
547: where migration_id=p_migration_id)
548: union -- select records which are also errors out
549: select a.* from iem_post_mdts a,iem_route_classifications b
550: where a.email_account_id=l_email_account_id and a.agent_id=0
551: and a.rt_classification_id=b.route_classification_id and
552: b.name=l_folder_name and a.message_id in (select message_id from iem_migration_store_temp
553: where migration_id=p_migration_id and mig_status='E' and dp_status is null);
1310: update iem_migration_details
1311: set folder_Status='M' where total_msg_count=0 and status='S';
1312: -- Reset MEssage Id Sequence to the highest Post mdts message id
1313: -- fix by ranjan on 5th nov. use nvl where there is no
1314: -- record in iem_post_mdts.otherwise the loop will be never
1315: -- ending
1316: select nvl(max(message_id),0) into l_id from iem_post_mdts;
1317: LOOP
1318: select iem_ms_base_headers_s1.nextval into l_id1 from dual;
1312: -- Reset MEssage Id Sequence to the highest Post mdts message id
1313: -- fix by ranjan on 5th nov. use nvl where there is no
1314: -- record in iem_post_mdts.otherwise the loop will be never
1315: -- ending
1316: select nvl(max(message_id),0) into l_id from iem_post_mdts;
1317: LOOP
1318: select iem_ms_base_headers_s1.nextval into l_id1 from dual;
1319: exit when l_id1>l_id;
1320: END LOOP;