1 package IEM_MAILITEM_PUB AUTHID CURRENT_USER as
2 /* $Header: iemclnts.pls 120.2.12020000.3 2013/01/23 08:53:31 lkullamb ship $*/
3 TYPE email_count_rec_type IS RECORD (
4 email_account_id number,
5 rt_classification_id number,
6 rt_classification_name varchar2(30),
7 email_account_name varchar2(100),
8 email_que_count number,
9 email_acq_count number,
10 email_max_qwait number, -- Wait Time in Queue
11 email_max_await number, -- Wait Time in Acquired
12 email_status number); -- 0 for old 1 for New
13
14 TYPE email_count_tbl IS TABLE OF email_count_rec_type
15 INDEX BY BINARY_INTEGER;
16 TYPE class_count_rec_type IS RECORD (
17 rt_classification_id number,
18 rt_classification_name varchar2(30),
19 email_count number);
20
21 TYPE class_count_tbl IS TABLE OF class_count_rec_type
22 INDEX BY BINARY_INTEGER;
23
24 TYPE t_number_table IS TABLE OF NUMBER;
25
26 TYPE acq_email_info_rec_type IS RECORD (
27 message_id number,
28 rt_classification_id number,
29 rt_classification_name varchar2(30),
30 rt_media_item_id number,
31 rt_interaction_id number,
32 email_account_id number,
33 message_flag varchar2(1),
34 sender_name varchar2(128),
35 subject varchar2(2000), --Change the varchar2(240) to varchar2(2000), since subject can be longer than 240 --bug 11735898
36 priority varchar2(30),
37 msg_status varchar2(50),
38 sent_date varchar2(60),
39 -- message_type varchar2(30),
40 mail_item_status varchar2(30),
41 from_agent_id number,
42 read_status varchar2(10),
43 description varchar2(240));
44
45 TYPE acq_email_info_tbl IS TABLE OF acq_email_info_rec_type
46 INDEX BY BINARY_INTEGER;
47
48 TYPE queue_email_info_rec_type IS RECORD (
49 message_id number,
50 rt_classification_id number,
51 rt_classification_name varchar2(30),
52 email_account_id number,
53 sender_name varchar2(128),
54 subject varchar2(2000), --Change the varchar2(240) to varchar2(2000), since subject can be longer than 240 --fix for bug 14705978
55 sent_date varchar2(60),
56 from_agent_id number,
57 party_name varchar2(360),
58 party_id NUMBER(15),
59 contact_id NUMBER(15),
60 group_name varchar2(60) default NULL,
61 source varchar2(60) default NULL,
62 source_number varchar2(15) default NULL);
63
64 TYPE queue_email_info_tbl IS TABLE OF queue_email_info_rec_type
65 INDEX BY BINARY_INTEGER;
66
67 TYPE keyVals_rec_type is RECORD (
68 key iem_route_rules.key_type_code%type,
69 value iem_route_rules.value%type,
70 datatype varchar2(1));
71
72 --Table of Key-Values
73 TYPE keyVals_tbl_type is TABLE OF keyVals_rec_type INDEX BY BINARY_INTEGER;
74
75 PROCEDURE GetMailItemCount (p_api_version_number IN NUMBER,
76 p_init_msg_list IN VARCHAR2 ,
77 p_commit IN VARCHAR2 ,
78 p_resource_id in number,
79 p_tbl in t_number_table:=NULL,
80 x_email_count out NOCOPY email_count_tbl,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_msg_count OUT NOCOPY NUMBER,
83 x_msg_data OUT NOCOPY VARCHAR2);
84 PROCEDURE GetMailItemCount (p_api_version_number IN NUMBER,
85 p_init_msg_list IN VARCHAR2 ,
86 p_commit IN VARCHAR2 ,
87 p_resource_id in number,
88 p_tbl in t_number_table:=NULL,
89 p_email_account_id in number,
90 x_class_bin out NOCOPY class_count_tbl,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2);
94
95 PROCEDURE GetMailItemCount (p_api_version_number IN NUMBER,
96 p_init_msg_list IN VARCHAR2 ,
97 p_commit IN VARCHAR2 ,
98 p_email_account_id in number,
99 x_class_bin out NOCOPY class_count_tbl,
100 x_return_status OUT NOCOPY VARCHAR2,
101 x_msg_count OUT NOCOPY NUMBER,
102 x_msg_data OUT NOCOPY VARCHAR2);
103
104 PROCEDURE GetMailItemCount (p_api_version_number IN NUMBER,
105 p_init_msg_list IN VARCHAR2 ,
106 p_commit IN VARCHAR2 ,
107 p_resource_id in number,
108 p_tbl in t_number_table:=NULL,
109 p_email_account_id in number,
110 p_classification_id in number,
111 x_count OUT NOCOPY NUMBER,
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_msg_count OUT NOCOPY NUMBER,
114 x_msg_data OUT NOCOPY VARCHAR2);
115
116 -- This will return POST MDT Data when called by EMC CLient
117 PROCEDURE GetMailItem (p_api_version_number IN NUMBER,
118 p_init_msg_list IN VARCHAR2 ,
119 p_commit IN VARCHAR2 ,
120 p_resource_id in number,
121 p_tbl in t_number_table:=NULL,
122 p_rt_classification in number,
123 p_account_id in number,
124 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_msg_count OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2);
128
129 -- This will return the Tag information along with POST MDT Data Called by
130 -- EMC Client
131 PROCEDURE GetMailItem (p_api_version_number IN NUMBER,
132 p_init_msg_list IN VARCHAR2 ,
133 p_commit IN VARCHAR2 ,
134 p_resource_id in number,
135 p_tbl in t_number_table:=NULL,
136 p_rt_classification in number,
137 p_account_id in number,
138 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
139 x_tag_key_value OUT NOCOPY keyVals_tbl_type,
140 x_encrypted_id OUT NOCOPY VARCHAR2,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2);
144 -- This will return POST MDT Data when called by UWQ
145 PROCEDURE GetMailItem(p_api_version_number IN NUMBER,
146 p_init_msg_list IN VARCHAR2 ,
147 p_commit IN VARCHAR2 ,
148 p_resource_id in number,
149 p_acct_rt_class_id in number,
150 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
151 x_return_status OUT NOCOPY VARCHAR2,
152 x_msg_count OUT NOCOPY NUMBER,
153 x_msg_data OUT NOCOPY VARCHAR2);
154 PROCEDURE DisposeMailItem (p_api_version_number IN NUMBER,
155 p_init_msg_list IN VARCHAR2 ,
156 p_commit IN VARCHAR2 ,
157 p_message_id in number,
158 x_return_status OUT NOCOPY VARCHAR2,
159 x_msg_count OUT NOCOPY NUMBER,
160 x_msg_data OUT NOCOPY VARCHAR2);
161
162 PROCEDURE ResolvedMessage (p_api_version_number IN NUMBER,
163 p_init_msg_list IN VARCHAR2 ,
164 p_commit IN VARCHAR2 ,
165 p_message_id in number,
166 p_action_flag in varchar2,
167 x_return_status OUT NOCOPY VARCHAR2,
168 x_msg_count OUT NOCOPY NUMBER,
169 x_msg_data OUT NOCOPY VARCHAR2);
170 PROCEDURE getGroupDetails(p_api_version_number IN NUMBER,
171 p_init_msg_list IN VARCHAR2 ,
172 p_commit IN VARCHAR2 ,
173 p_resource_id in number,
174 x_tbl out NOCOPY t_number_table,
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_msg_count OUT NOCOPY NUMBER,
177 x_msg_data OUT NOCOPY VARCHAR2);
178
179 PROCEDURE UpdateMailItem (p_api_version_number IN NUMBER,
180 p_init_msg_list IN VARCHAR2 ,
181 p_commit IN VARCHAR2 ,
182 p_email_data in iem_rt_proc_emails%rowtype,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_msg_count OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2);
186 PROCEDURE getMailItemInfo(p_api_version_number IN NUMBER,
187 p_init_msg_list IN VARCHAR2 ,
188 p_commit IN VARCHAR2 ,
189 p_message_id in number,
190 p_account_id in number,
191 p_agent_id in number,
192 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
193 x_return_status OUT NOCOPY VARCHAR2,
194 x_msg_count OUT NOCOPY NUMBER,
195 x_msg_data OUT NOCOPY VARCHAR2);
196 PROCEDURE getEmailHeaders(p_api_version_number IN NUMBER,
197 p_init_msg_list IN VARCHAR2 ,
198 p_commit IN VARCHAR2 ,
199 p_resource_id in number,
200 p_email_account_id in number,
201 p_display_size in NUMBER,
202 p_page_count in NUMBER,
203 p_sort_by in VARCHAR2,
204 p_sort_order in number,
205 x_total_message OUT NOCOPY NUMBER,
206 x_acq_email_data out NOCOPY acq_email_info_tbl,
207 x_return_status OUT NOCOPY VARCHAR2,
208 x_msg_count OUT NOCOPY NUMBER,
209 x_msg_data OUT NOCOPY VARCHAR2);
210
211 --12.1.3 Development cherry picking
212 -- This API will return the unread email headers
213 --change by sardas , fix for bug14379811, added p_resource_id
214 PROCEDURE getUnreadEmailHeaders(p_api_version_number IN NUMBER,
215 p_init_msg_list IN VARCHAR2 ,
216 p_commit IN VARCHAR2 ,
217 p_resource_id in number,
218 p_email_account_id in number,
219 p_display_size in NUMBER,
220 p_page_count in NUMBER,
221 p_sort_by in VARCHAR2,
222 p_sort_order in number,
223 x_total_message OUT NOCOPY NUMBER,
224 x_queue_email_data out NOCOPY queue_email_info_tbl,
225 x_return_status OUT NOCOPY VARCHAR2,
226 x_msg_count OUT NOCOPY NUMBER,
227 x_msg_data OUT NOCOPY VARCHAR2);
228 /* This Api Will Return the meta Data and Tag Data for a message Id */
229 PROCEDURE GetQueueItemData (p_api_version_number IN NUMBER,
230 p_init_msg_list IN VARCHAR2 ,
231 p_commit IN VARCHAR2 ,
232 p_message_id in number,
233 p_from_agent_id in number,
234 p_to_agent_id in number,
235 p_mail_item_status in varchar2,
236 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
237 x_tag_key_value OUT NOCOPY keyVals_tbl_type,
238 x_encrypted_id OUT NOCOPY VARCHAR2,
239 x_return_status OUT NOCOPY VARCHAR2,
240 x_msg_count OUT NOCOPY NUMBER,
241 x_msg_data OUT NOCOPY VARCHAR2);
242 end IEM_MAILITEM_PUB;