[Home] [Help]
PACKAGE BODY: APPS.IEM_UTILS_PUB
Source
1 PACKAGE BODY IEM_UTILS_PUB as
2 /* $Header: iemputlb.pls 120.1 2006/09/01 22:28:25 rtripath noship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_UTILS_PUB';
5
6 PROCEDURE show_all_accounts (p_api_version_number IN NUMBER,
7 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8 p_commit IN VARCHAR2 := FND_API.G_FALSE,
9 x_email_account_tbl OUT NOCOPY iem_utils_pub.email_account_tbl,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_count OUT NOCOPY NUMBER,
12 x_msg_data OUT NOCOPY VARCHAR2)
13 is
14 l_api_name VARCHAR2(255):='show_all_accounts';
15 l_api_version_number NUMBER:=1.0;
16 l_account_id_tbl jtf_number_table:=jtf_number_table();
17 l_account_name_tbl jtf_varchar2_table_100:=jtf_varchar2_table_100();
18 l_email_user_tbl jtf_varchar2_table_100:=jtf_varchar2_table_100();
19 l_domain_tbl jtf_varchar2_table_100:=jtf_varchar2_table_100();
20 l_index number:=1;
21
22 BEGIN
23 -- Standard Start of API savepoint
24 SAVEPOINT showAccount;
25 -- Standard call to check for call compatibility.
26 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
27 p_api_version_number,
28 l_api_name,
29 G_PKG_NAME)
30 THEN
31 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
32 END IF;
33 -- Initialize message list if p_init_msg_list is set to TRUE.
34 IF FND_API.to_Boolean( p_init_msg_list )
35 THEN
36 FND_MSG_PUB.initialize;
37 END IF;
38 -- Initialize API return status to SUCCESS
39 x_return_status := FND_API.G_RET_STS_SUCCESS;
40
41 select email_account_id, account_name, email_user, domain bulk collect into l_account_id_tbl, l_account_name_tbl, l_email_user_tbl, l_domain_tbl from iem_email_accounts
42 where upper(email_user)<>upper('intent') and upper(email_user)<>upper('acknowledgements') order by upper(email_user) ||'@'||upper(domain) asc;
43
44 for l_index in l_account_id_tbl.FIRST..l_account_id_tbl.LAST LOOP
45 x_email_account_tbl(l_index).email_account_id:=l_account_id_tbl(l_index);
46 x_email_account_tbl(l_index).account_name:=l_account_name_tbl(l_index);
47 x_email_account_tbl(l_index).email_user:=l_email_user_tbl(l_index);
48 x_email_account_tbl(l_index).domain:=l_domain_tbl(l_index);
49 end loop;
50
51 -- Standard Check Of p_commit.
52 IF FND_API.To_Boolean(p_commit) THEN
53 COMMIT WORK;
54 END IF;
55 -- Standard callto get message count and if count is 1, get message info.
56 FND_MSG_PUB.Count_And_Get
57 ( p_count => x_msg_count,
58 p_data => x_msg_data
59 );
60 EXCEPTION
61 WHEN FND_API.G_EXC_ERROR THEN
62 ROLLBACK TO update_item_PVT;
63 x_return_status := FND_API.G_RET_STS_ERROR ;
64 FND_MSG_PUB.Count_And_Get
65 ( p_count => x_msg_count,
66 p_data => x_msg_data
67 );
68 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
69 ROLLBACK TO update_item_PVT;
70 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
71 FND_MSG_PUB.Count_And_Get
72 ( p_count => x_msg_count,
73 p_data => x_msg_data
74 );
75 WHEN OTHERS THEN
76 ROLLBACK TO update_item_PVT;
77 x_return_status := FND_API.G_RET_STS_ERROR;
78 IF FND_MSG_PUB.Check_Msg_Level
79 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
80 THEN
81 FND_MSG_PUB.Add_Exc_Msg
82 ( G_PKG_NAME ,
83 l_api_name
84 );
85 END IF;
86 FND_MSG_PUB.Count_And_Get
87 ( p_count => x_msg_count ,
88 p_data => x_msg_data
89 );
90
91 END;
92
93 PROCEDURE Get_Mailcount_by_days (p_api_version_number IN NUMBER,
94 p_init_msg_list IN VARCHAR2 ,
95 p_commit IN VARCHAR2 ,
96 p_duration in number,
97 p_resource_id in number,
98 x_email_count out NOCOPY email_status_count_tbl,
99 x_return_status OUT NOCOPY VARCHAR2,
100 x_msg_count OUT NOCOPY NUMBER,
101 x_msg_data OUT NOCOPY VARCHAR2) IS
102 cursor c1 is select distinct resource_id from iem_rt_proc_emails
103 where resource_id>0;
104 l_agent_id number;
105 cursor c_new is select ih_media_item_id from iem_rt_proc_emails
106 where resource_id=l_agent_id and mail_item_status in ('A','N','T');
107 cursor c_read is select ih_media_item_id from iem_rt_proc_emails
108 where resource_id=l_agent_id and mail_item_status in ('R','S');
109 l_counter number;
110 l_new_count number;
111 l_read_count number;
112 l_api_name varchar2(50):='Get_Mailcount_by_days';
113 l_api_version_number number:=1.0;
114 l_time date;
115 BEGIN
116 -- Standard call to check for call compatibility.
117 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
118 p_api_version_number,
119 l_api_name,
120 G_PKG_NAME)
121 THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124 SAVEPOINT select_mail_count_pvt;
125 x_return_status := FND_API.G_RET_STS_SUCCESS;
126 l_counter:=1;
127 IF p_resource_id=0 THEN -- for ALL resources
128 for v1 in c1 loop
129 l_new_count:=0;
130 l_read_count:=0;
131 l_agent_id:=v1.resource_id;
132 FOR v2 in c_new LOOP
133 select max(start_date_time) into l_time
134 from jtf_ih_media_item_lc_segs
135 where media_id=v2.ih_media_item_id;
136 IF l_time <= (sysdate-p_duration/24) THEN
137 l_new_count:=l_new_count+1;
138 END IF;
139 END LOOP;
140
141 FOR v3 in c_read LOOP
142 select max(start_date_time) into l_time
143 from jtf_ih_media_item_lc_segs
144 where media_id=v3.ih_media_item_id;
145 IF l_time <= (sysdate-p_duration/24) THEN
146 l_read_count:=l_read_count+1;
147 END IF;
148 END LOOP;
149 x_email_count(l_counter).resource_id:=v1.resource_id;
150 x_email_count(l_counter).new_count:=l_new_count;
151 x_email_count(l_counter).read_count:=l_read_count;
152 l_counter:=l_counter+1;
153 end loop;
154 ELSE
155 l_agent_id:=p_resource_id;
156 l_new_count:=0;
157 l_read_count:=0;
158 FOR v2 in c_new LOOP
159 select max(start_date_time) into l_time
160 from jtf_ih_media_item_lc_segs
161 where media_id=v2.ih_media_item_id;
162 IF l_time <= (sysdate-p_duration/24) THEN
163 l_new_count:=l_new_count+1;
164 END IF;
165 END LOOP;
166
167 FOR v3 in c_read LOOP
168 select max(start_date_time) into l_time
169 from jtf_ih_media_item_lc_segs
170 where media_id=v3.ih_media_item_id;
171 IF l_time <= (sysdate-p_duration/24) THEN
172 l_read_count:=l_read_count+1;
173 END IF;
174 END LOOP;
175 x_email_count(l_counter).resource_id:=p_resource_id;
176 x_email_count(l_counter).new_count:=l_new_count;
177 x_email_count(l_counter).read_count:=l_read_count;
178 END IF;
179 EXCEPTION
180 WHEN FND_API.G_EXC_ERROR THEN
181 ROLLBACK TO select_mail_count_PVT;
182 x_return_status := FND_API.G_RET_STS_ERROR ;
183 FND_MSG_PUB.Count_And_Get
184 ( p_count => x_msg_count,
185 p_data => x_msg_data
186 );
187 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
188 ROLLBACK TO select_mail_count_PVT;
189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
190 FND_MSG_PUB.Count_And_Get
191 ( p_count => x_msg_count,
192 p_data => x_msg_data
193 );
194 WHEN OTHERS THEN
195 ROLLBACK TO select_mail_count_PVT;
196 x_return_status := FND_API.G_RET_STS_ERROR;
197 IF FND_MSG_PUB.Check_Msg_Level
198 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
199 THEN
200 FND_MSG_PUB.Add_Exc_Msg
201 ( G_PKG_NAME ,
202 l_api_name
203 );
204 END IF;
205 FND_MSG_PUB.Count_And_Get
206 ( p_count => x_msg_count ,
207 p_data => x_msg_data
208 );
209
210 END Get_Mailcount_by_days ;
211
212 PROCEDURE Get_Mailcount_by_MILCS (p_api_version_number IN NUMBER,
213 p_init_msg_list IN VARCHAR2 ,
214 p_commit IN VARCHAR2 ,
215 p_duration in number,
216 p_resource_id in number,
217 p_tbl in t_number_table,
218 x_email_count out NOCOPY email_count_tbl,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER,
221 x_msg_data OUT NOCOPY VARCHAR2) IS
222
223 l_counter number;
224 l_new_count_1 number;
225 l_api_name varchar2(50):='Get_Mailcount_by_MILCS';
226 l_api_version_number number:=1.0;
227 l_resource_id number;
228 l_milcs_id number;
229 i_tbl jtf_number_table:=jtf_number_table();
230 cursor c1 is select distinct resource_id from iem_rt_proc_emails
231 where resource_id>0;
232 BEGIN
233 -- Standard call to check for call compatibility.
234 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
235 p_api_version_number,
236 l_api_name,
237 G_PKG_NAME)
238 THEN
239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
240 END IF;
241 SAVEPOINT select_mail_count_pvt;
242 x_return_status := FND_API.G_RET_STS_SUCCESS;
243
244
245
246 FOR j in p_tbl.FIRST..p_tbl.LAST LOOP
247 i_tbl.extend;
248 l_milcs_id:=p_tbl(j).milcs_id;
249 i_tbl(j):=l_milcs_id;
250 END LOOP;
251 l_counter:=1;
252 IF p_resource_id=0 THEN
253 FOR v1 in c1 loop
254 SELECT count(*)
255 into l_new_count_1
256 FROM iem_rt_proc_emails a
257 WHERE resource_id=v1.resource_id
258 and a.ih_media_item_id in
259 (select media_id from jtf_ih_media_item_lc_segs
260 WHERE milcs_type_id in (select * from TABLE(cast(i_tbl as jtf_number_table)))
261 AND resource_id=v1.resource_id and (sysdate-start_date_time)*24>=p_duration);
262 IF l_new_count_1>0 THEN
263 x_email_count(l_counter).resource_id:=v1.resource_id;
264 x_email_count(l_counter).count:=l_new_count_1;
265 l_counter:=l_counter+1;
266 END IF;
267 END LOOP;
268 ELSE
269 SELECT count(*)
270 into l_new_count_1
271 FROM iem_rt_proc_emails
272 WHERE resource_id=p_resource_id
273 and ih_media_item_id in
274 (select distinct media_id from jtf_ih_media_item_lc_segs
275 where milcs_type_id in (select * from TABLE(cast(i_tbl as jtf_number_table)))
276 and resource_id=p_resource_id and (sysdate-start_date_time)*24>=p_duration);
277 x_email_count(l_counter).resource_id:=p_resource_id;
278 x_email_count(l_counter).count:=l_new_count_1;
279 END IF;
280 EXCEPTION
281 WHEN FND_API.G_EXC_ERROR THEN
282 ROLLBACK TO select_mail_count_PVT;
283 x_return_status := FND_API.G_RET_STS_ERROR ;
284 FND_MSG_PUB.Count_And_Get
285 ( p_count => x_msg_count,
286 p_data => x_msg_data
287 );
288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
289 ROLLBACK TO select_mail_count_PVT;
290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
291 FND_MSG_PUB.Count_And_Get
292 ( p_count => x_msg_count,
293 p_data => x_msg_data
294 );
295 WHEN OTHERS THEN
296 ROLLBACK TO select_mail_count_PVT;
297 x_return_status := FND_API.G_RET_STS_ERROR;
298 IF FND_MSG_PUB.Check_Msg_Level
299 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
300 THEN
301 FND_MSG_PUB.Add_Exc_Msg
302 ( G_PKG_NAME ,
303 l_api_name
304 );
305 END IF;
306 FND_MSG_PUB.Count_And_Get
307 ( p_count => x_msg_count ,
308 p_data => x_msg_data
309 );
310
311 END Get_Mailcount_by_MILCS ;
312
313 END IEM_UTILS_PUB;