[Home] [Help]
25: and a.rt_classification_id=c.route_classification_id
26: AND a.email_account_id=d.email_account_id
27: AND d.resource_id=p_resource_id
28: AND a.group_id in (select * from TABLE(cast(i_tbl as jtf_number_table)))
29: and a.message_id not in (select message_id from iem_reroute_hists where agent_id=p_resource_id)
30: GROUP by a.email_account_id,a.rt_classification_id,b.USER_NAME,c.name;
31: CURSOR c_11 IS
32: SELECT a.EMAIL_ACCOUNT_ID,a.RT_CLASSIFICATION_ID,
33: b.USER_NAME,c.name,count(*) Total,
41: AND d.resource_id=p_resource_id
42: AND (a.group_id in (select group_id from jtf_rs_group_members where resource_id=p_resource_id
43: and delete_flag<>'Y')
44: or (a.group_id=0))
45: and a.message_id not in (select message_id from iem_reroute_hists where agent_id=p_resource_id)
46: GROUP by a.email_account_id,a.rt_classification_id,b.USER_NAME,c.name;
47: Cursor c2 IS
48: select a.email_account_id,a.rt_classification_id,
49: b.USER_NAME,c.name,Count(*) Total,
616: x_return_status := FND_API.G_RET_STS_SUCCESS;
617: IF (p_tbl is null) and (nvl(p_account_id,FND_API.G_MISS_NUM)<> FND_API.G_MISS_NUM) and
618: (nvl(p_rt_classification,FND_API.G_MISS_NUM)<> FND_API.G_MISS_NUM) THEN
619: OPEN email_dtl_cur FOR
620: 'select /*FIRST_ROWS*/ * from iem_rt_proc_emails p where message_id not in (select message_id from iem_reroute_hists where resource_id=:res1 ) and email_account_id=:id
621: and rt_classification_id=:rt
622: and resource_id=0
623: and ( p.group_id = 0
624: or exists (
643: END LOOP;
644: close email_dtl_cur;
645: ELSE
646: OPEN email_dtl_cur FOR
647: 'select /*FIRST_ROWS*/ * from iem_rt_proc_emails p where message_id not in (select message_id from iem_reroute_hists where resource_id=:res1 ) and email_account_id=:id
648: and resource_id=0
649: and ( p.group_id = 0
650: or exists (
651: select null
816: NULL;
817: END ;
818: DELETE FROM iem_rt_proc_emails
819: WHERE MESSAGE_ID=p_message_id;
820: delete from iem_reroute_hists
821: where message_id=p_message_id;
822: delete from iem_kb_results where message_id=p_message_id;
823: delete from iem_email_classifications where message_id=p_message_id;
824: delete from iem_comp_rt_stats where type='WORKFLOW' and param=to_char(p_message_id);