[Home] [Help]
21: select name||','||a.route_classification_id name from iem_route_classifications a,iem_account_route_class b
22: where a.route_classification_id=b.route_classification_id
23: and b.email_account_id=l_email_account_id
24: union
25: select 'Inbox' from dual
26: union
27: select 'Drafts' from dual
28: union
29: select 'Resolved' from dual
23: and b.email_account_id=l_email_account_id
24: union
25: select 'Inbox' from dual
26: union
27: select 'Drafts' from dual
28: union
29: select 'Resolved' from dual
30: union
31: select 'Sent' from dual
25: select 'Inbox' from dual
26: union
27: select 'Drafts' from dual
28: union
29: select 'Resolved' from dual
30: union
31: select 'Sent' from dual
32: union
33: select 'Deleted' from dual
27: select 'Drafts' from dual
28: union
29: select 'Resolved' from dual
30: union
31: select 'Sent' from dual
32: union
33: select 'Deleted' from dual
34: union
35: select 'Admin' from dual
29: select 'Resolved' from dual
30: union
31: select 'Sent' from dual
32: union
33: select 'Deleted' from dual
34: union
35: select 'Admin' from dual
36: union
37: select 'Retry' from dual;
31: select 'Sent' from dual
32: union
33: select 'Deleted' from dual
34: union
35: select 'Admin' from dual
36: union
37: select 'Retry' from dual;
38: l_total_count number;
39: l_str varchar2(255);
33: select 'Deleted' from dual
34: union
35: select 'Admin' from dual
36: union
37: select 'Retry' from dual;
38: l_total_count number;
39: l_str varchar2(255);
40: G_IM_LINK varchar2(255);
41: G_FOLDER varchar2(255);
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
133: and part.part_type = 'HEADERS' and part.delete_flag <> 'Y';
134: end if;
135: select IEM_MIGRATION_DETAILS_S1.nextval into l_mig_id from dual;
136: insert into IEM_MIGRATION_DETAILS
137: (migration_id,
138: agent_account_id,
139: 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;
202: select IEM_MIGRATION_DETAILS_S1.nextval into l_mig_id from dual;
203: insert into IEM_MIGRATION_DETAILS
204: (migration_id,
205: email_account_id,
206: folder_name,
323: --these values due to data issue where we cant find a valid media_id
324: --RAISE POST_CREATION_DATE_EXCEP;
325: END;
326: END IF;
327: select iem_ms_base_headers_s1.nextval into l_message_id from dual;
328:
329: insert into iem_arch_msgdtls
330: (MESSAGE_ID ,
331: EMAIL_ACCOUNT_ID ,
656: l_mig_rec.from_str:=v1.sender_name;
657: l_mig_rec.message_size:=v1.message_size;
658:
659: -- Create Record into Worklist Queue.
660: select IEM_MIGRATION_STORE_TEMP_s1.nextval into l_mig_rec.mail_id from dual;
661: insert into iem_migration_store_temp
662: (MAIL_ID,
663: MESSAGE_ID ,
664: MIGRATION_ID,
960: delete from iem_migration_Store_temp where migration_id=p_migration_id and message_id=l_msg_table(i);
961: --dbms_output.put_line('deleteing record migration_id='||p_migration_id|| ' and message_id='||l_msg_table(i));
962: end if;
963: l_mig_Rec.message_id:=l_msg_table(i);
964: select IEM_MIGRATION_STORE_TEMP_s1.nextval into l_mig_rec.mail_id from dual;
965: l_mig_rec.folder_name:=l_folder_name;
966: l_mig_rec.message_type:=l_folder_type;
967: l_mig_rec.email_account_id:=l_email_account_id;
968: l_content:=empty_blob();
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;
1321: end if;
1322: else
1562: l_email_Account_id:=v1.email_account_id;
1563: l_acct_language:=v1.acct_language;
1564: l_template_profile:=to_number(FND_PROFILE.VALUE_SPECIFIC('IEM_TEMPLATE_CATEGORY_ID')) ;
1565: select to_number(decode(FND_PROFILE.VALUE_SPECIFIC('IEM_ACCOUNT_SENDER_NAME'),'ACCOUNT',0,1)) into
1566: l_sender_profile from dual;
1567: select dns_name,port
1568: into l_in_host,l_in_port
1569: from iem_email_servers a,iem_email_server_types b
1570: where a.server_type_id=b.email_server_type_id and
1590: BEGIN
1591: select kem_flag into l_flag from iem_email_Accounts
1592: where email_account_id=v1.email_Account_id;
1593: if l_flag is null then
1594: select decode(v1.acct_language,'GB',1,2) into l_flag from dual;
1595: end if;
1596:
1597: EXCEPTION WHEN OTHERS THEN
1598: -- This is 1159 ..
1595: end if;
1596:
1597: EXCEPTION WHEN OTHERS THEN
1598: -- This is 1159 ..
1599: select decode(v1.acct_language,'GB',1,2) into l_flag from dual;
1600: END;
1601: ELSE
1602: l_flag:=0;
1603: END IF;
1731: l_last_update_login);
1732:
1733: -- Insert into IEM_INTENT_DTLS
1734: for v3 in c1 LOOP
1735: select iem_intent_dtls_s1.nextval into l_intent_dtl_id from dual;
1736: l_mod:=mod(v3.score*100,10);
1737: if (l_mod=0 or l_mod>=5) then
1738: l_weight:=ceil(v3.score*10);
1739: else
1794: where deleted_flag is null; -- So that can be re runnable..
1795: -- Reset the Sequence to have highest email account id id
1796: select nvl(max(email_account_id),0) into l_max_id from iem_mstemail_accounts;
1797: LOOP
1798: select iem_mstemail_accounts_s1.nextval into l_val from dual;
1799: exit when l_val>l_max_id;
1800: END LOOP;
1801:
1802: -- Reset the Sequence to have highest agent id
1802: -- Reset the Sequence to have highest agent id
1803:
1804: select nvl(max(agent_id),0) into l_max_id from iem_agents;
1805: LOOP
1806: select iem_agents_s1.nextval into l_val from dual;
1807: exit when l_val>l_max_id;
1808: END LOOP;
1809:
1810: -- Reset the Sequence to have highest intent id
1810: -- Reset the Sequence to have highest intent id
1811:
1812: select nvl(max(intent_id),0) into l_max_id from iem_intents;
1813: LOOP
1814: select iem_intents_s1.nextval into l_val from dual;
1815: exit when l_val>l_max_id;
1816: END LOOP;
1817: -- Deleting OES Rule
1818: for v1 in c_rule LOOP