[Home] [Help]
PACKAGE BODY: APPS.IEM_DPM_PP_QUEUE_PVT
Source
1 PACKAGE BODY IEM_DPM_PP_QUEUE_PVT AS
2 /* $Header: iemvdpmb.pls 120.1 2005/10/19 16:47:19 liangxia noship $ */
3
4 -- file name: iemvqueb.pls
5 --
6 -- Purpose: EMTA runtime queue management
7 --
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- Liang Xia 8/01/2005 Created
11 -- --------- ------ ------------------------------------------
12
13 -- Enter procedure, function bodies as shown below
14 G_PKG_NAME CONSTANT varchar2(30) :='IEM_DPM_QUEUE_PVT ';
15 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
16 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID') ) ;
17
18 Procedure get_folder_work_list(
19 p_api_version_number IN NUMBER,
20 p_init_msg_list IN VARCHAR2 := null,
21 p_commit IN VARCHAR2 := null,
22 x_folder_work_list OUT NOCOPY folder_worklist_tbl,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2
26 )
27 is
28 l_api_name VARCHAR2(255):='get_folder_work_list';
29 l_api_version_number NUMBER:=1.0;
30 l_seq_id NUMBER;
31
32 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
33 l_msg_count NUMBER := 0;
34 l_msg_data VARCHAR2(2000);
35
36 x number;
37 l_total number;
38 l_available number;
39 l_folder_list folder_worklist_tbl;
40 l_folder_type varchar2(1);
41
42
43 cursor c_folder_types is
44 select folder_type, count(*) total from iem_migration_details
45 where folder_status='R'
46 group by folder_type
47 order by decode(folder_type,'H',1,
48 'N',2,
49 'Q',3,
50 'I',4,
51 'D',5,
52 0 ) desc;
53
54 cursor c_folder_details( p_type varchar2) is
55 select a.email_account_id,a.email_user||'@'||a.domain as user_name,
56 a.email_password,b.dns_name, b.port, c.migration_id, c.folder_name
57 from iem_email_accounts a, iem_email_servers b, iem_migration_details c,
58 iem_server_groups d, iem_email_server_types e
59 where ( c.folder_status='R' )
60 and c.folder_type= p_type and c.email_account_id=a.email_account_id
61 and a.server_group_id=d.server_group_id
62 and d.server_group_id=b.server_group_id and b.server_type_id=e.email_server_type_id
63 and upper(e.email_server_type)='IMAP';
64
65
66 cursor c_agent_folder_details( p_type varchar2) is
67 select c.email_account_id,a.email_user||'@'||a.domain as user_name,
68 a.email_password,d.dns_name, d.port, c.migration_id, c.folder_name
69 from iem_agent_accounts a, iem_email_accounts b,
70 iem_migration_details c, iem_email_servers d, iem_server_groups e,
71 iem_email_server_types f
72 where ( c.folder_status='R' )
73 and c.folder_type= p_type and c.email_account_id=a.email_account_id
74 and c.agent_account_id=a.agent_account_id
75 and b.server_group_id=e.server_group_id
76 and b.email_account_id = a.email_account_id
77 and d.server_type_id=f.email_server_type_id
78 and upper(f.email_server_type)='IMAP';
79
80 BEGIN
81 -- Standard Start of API savepoint
82 SAVEPOINT get_folder_work_list_PVT;
83
84 -- Standard call to check for call compatibility.
85
86 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
87 p_api_version_number,
88 l_api_name,
89 G_PKG_NAME)
90 THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93
94
95 -- Initialize message list if p_init_msg_list is set to TRUE.
96 IF FND_API.to_Boolean( p_init_msg_list )
97 THEN
98 FND_MSG_PUB.initialize;
99 END IF;
100
101 -- Initialize API return status to SUCCESS
102 x_return_status := FND_API.G_RET_STS_SUCCESS;
103
104 --begins here
105
106 -- first update the Flder_status for all finished account
107 update iem_migration_details a set a.folder_status='D', a.last_update_date=sysdate, a.msg_download_count=
108 (select count(*) from iem_migration_store_temp
109 where dp_status='D' and migration_id = a.migration_id)
110 where a.folder_status='R' and a.msg_record_count =
111 ( (select count(*) from iem_migration_store_temp
112 where dp_status='D' and migration_id = a.migration_id)
113 +
114 ( select count(*) from iem_migration_store_temp
115 where dp_status='E' and migration_id = a.migration_id));
116
117 update iem_migration_details a set a.last_update_date=sysdate, a.msg_download_count=
118 (select count(*) from iem_migration_store_temp
119 where dp_status='D' and migration_id = a.migration_id)
120 where a.folder_status='R';
121
122 x := 1;
123 FOR v_folder_types IN c_folder_types LOOP
124 l_total := v_folder_types.total;
125
126 if ( l_total > 0 ) then
127 l_folder_type := v_folder_types.folder_type;
128
129 --getting folder details for this type
130 if ( l_folder_type='I' or l_folder_type='D' ) then
131
132 FOR v_agent_folder_details IN c_agent_folder_details(l_folder_type) LOOP
133
134 select count(*) into l_available from iem_migration_store_temp
135 where migration_id=v_agent_folder_details.migration_id
136 and mig_status<>'E' and dp_status is null;
137
138 if ( l_available > 0 ) then
139 l_folder_list(x).migration_id := v_agent_folder_details.migration_id;
140 l_folder_list(x).email_acct_id := v_agent_folder_details.email_account_id;
141 l_folder_list(x).folder_type := l_folder_type;
142 l_folder_list(x).folder_name := v_agent_folder_details.folder_name;
143 l_folder_list(x).user_name := v_agent_folder_details.user_name;
144 l_folder_list(x).password := v_agent_folder_details.email_password;
145 l_folder_list(x).server_name := v_agent_folder_details.dns_name;
146 l_folder_list(x).port := v_agent_folder_details.port;
147 x := x + 1;
148
149 if ( x > 10 ) then
150 exit;
151 end if;
152
153 end if;
154
155 END LOOP;
156 if ( x > 1 ) then
157 exit;
158 end if;
159 else
160 FOR v_folder_details IN c_folder_details(l_folder_type) LOOP
161
162 select count(*) into l_available from iem_migration_store_temp
163 where migration_id=v_folder_details.migration_id
164 and mig_status<>'E' and dp_status is null;
165
166 if ( l_available > 0 ) then
167
168 l_folder_list(x).migration_id := v_folder_details.migration_id;
169 l_folder_list(x).email_acct_id := v_folder_details.email_account_id;
170 l_folder_list(x).folder_type := l_folder_type;
171 l_folder_list(x).folder_name := v_folder_details.folder_name;
172 l_folder_list(x).user_name := v_folder_details.user_name;
173 l_folder_list(x).password := v_folder_details.email_password;
174 l_folder_list(x).server_name := v_folder_details.dns_name;
175 l_folder_list(x).port := v_folder_details.port;
176 x := x + 1;
177
178 if ( x > 10 ) then
179 exit;
180 end if;
181
182 end if;
183
184 END LOOP;
185
186 if ( x > 1 ) then
187 exit;
188 end if;
189 end if;
190
191 end if;
192
193 END LOOP;
194
195 x_folder_work_list := l_folder_list;
196 -- Standard Check Of p_commit.
197 IF FND_API.To_Boolean(p_commit) THEN
198 COMMIT WORK;
199 END IF;
200
201
202 -- Standard callto get message count and if count is 1, get message info.
203 FND_MSG_PUB.Count_And_Get
204 ( p_count => x_msg_count,
205 p_data => x_msg_data
206 );
207
208 EXCEPTION
209
210 WHEN FND_API.G_EXC_ERROR THEN
211 ROLLBACK TO get_folder_work_list_PVT;
212 x_return_status := FND_API.G_RET_STS_ERROR ;
213 FND_MSG_PUB.Count_And_Get
214
215 ( p_count => x_msg_count,
216 p_data => x_msg_data
217 );
218
219 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
220 ROLLBACK TO get_folder_work_list_PVT;
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
222 FND_MSG_PUB.Count_And_Get
223 ( p_count => x_msg_count,
224 p_data => x_msg_data
225 );
226
227 WHEN OTHERS THEN
228 ROLLBACK TO get_folder_work_list_PVT;
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 IF FND_MSG_PUB.Check_Msg_Level
231 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
232 THEN
233 FND_MSG_PUB.Add_Exc_Msg
234 ( G_PKG_NAME ,
235 l_api_name
236 );
237 END IF;
238
239 FND_MSG_PUB.Count_And_Get
240 ( p_count => x_msg_count,
241 p_data => x_msg_data
242
243 );
244
245 END get_folder_work_list;
246
247
248 Procedure get_msg_work_list(
249 p_api_version_number IN NUMBER,
250 p_init_msg_list IN VARCHAR2 := null,
251 p_commit IN VARCHAR2 := null,
252 p_batch IN NUMBER,
253 p_migration_id IN NUMBER,
254 x_mail_ids OUT NOCOPY JTF_NUMBER_TABLE,
255 x_message_ids OUT NOCOPY JTF_NUMBER_TABLE,
256 x_msg_uids OUT NOCOPY JTF_NUMBER_TABLE,
257 x_subjects OUT NOCOPY jtf_varchar2_Table_2000,
258 x_rfc_msgids OUT NOCOPY jtf_varchar2_Table_300,
259 x_return_status OUT NOCOPY VARCHAR2,
260 x_msg_count OUT NOCOPY NUMBER,
261 x_msg_data OUT NOCOPY VARCHAR2
262 ) is
263
264 l_api_name VARCHAR2(255):='get_msg_work_list';
265 l_api_version_number NUMBER:=1.0;
266 l_seq_id NUMBER;
267
268 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
269 l_msg_count NUMBER := 0;
270 l_msg_data VARCHAR2(2000);
271
272 l_queue_rec iem_migration_store_temp%rowtype;
273 l_batch number;
274 i number;
275 l_mail_ids JTF_NUMBER_TABLE := jtf_number_Table();
276 l_msg_uids JTF_NUMBER_TABLE := jtf_number_Table();
277 l_subjects jtf_varchar2_Table_2000 := jtf_varchar2_Table_2000();
278 l_message_ids JTF_NUMBER_TABLE := jtf_number_Table();
279 l_rfc_msgids jtf_varchar2_Table_300 := jtf_varchar2_Table_300();
280
281 e_nowait EXCEPTION;
282 PRAGMA EXCEPTION_INIT(e_nowait, -54);
283
284 BEGIN
285 -- Standard Start of API savepoint
286 SAVEPOINT get_msg_work_list_PVT;
287
288 -- Standard call to check for call compatibility.
289
290 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
291 p_api_version_number,
292 l_api_name,
293 G_PKG_NAME)
294 THEN
295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
296 END IF;
297
298
299 -- Initialize message list if p_init_msg_list is set to TRUE.
300 IF FND_API.to_Boolean( p_init_msg_list )
301 THEN
302 FND_MSG_PUB.initialize;
303 END IF;
304
305 -- Initialize API return status to SUCCESS
306 x_return_status := FND_API.G_RET_STS_SUCCESS;
307
308
309 i := 1;
310
311 for y in ( select mail_id
312 from iem_migration_store_temp
313 where migration_id=p_migration_id and mig_status<>'E'and dp_status is null
314 order by creation_date asc )
315 loop
316
317 BEGIN
318
319 select * into l_queue_rec from iem_migration_store_temp
320 where migration_id=p_migration_id and mig_status<>'E' and dp_status is null
321 and mail_id=y.mail_id
322 FOR UPDATE NOWAIT;
323
324 l_mail_ids.extend(1);
325 l_message_ids.extend(1);
326 l_msg_uids.extend(1);
327 l_subjects.extend(1);
328 l_rfc_msgids.extend(1);
329
330 l_mail_ids(i) := l_queue_rec.mail_id;
331 l_message_ids(i) := l_queue_rec.message_id;
332 l_msg_uids(i) := l_queue_rec.msg_uid;
333 l_subjects(i) := l_queue_rec.subject;
334 l_rfc_msgids(i) := l_queue_rec.RFC822_message_id;
335
336 update iem_migration_store_temp set dp_status ='A', last_update_date=sysdate
337 where migration_id=p_migration_id and mail_id=l_queue_rec.mail_id;
338
339 i := i + 1;
340
341 EXCEPTION when e_nowait then
342 null;
343 when others then
344 null;
348 exit;
345 END;
346
347 if ( i > p_batch ) then
349 end if;
350
351 end loop;
352
353 x_mail_ids := l_mail_ids;
354 x_message_ids := l_message_ids ;
355 x_msg_uids := l_msg_uids;
356 x_subjects := l_subjects;
357 x_rfc_msgids := l_rfc_msgids;
358
359 -- Standard Check Of p_commit.
360 IF FND_API.To_Boolean(p_commit) THEN
361 COMMIT WORK;
362 END IF;
363
364 -- Standard callto get message count and if count is 1, get message info.
365 FND_MSG_PUB.Count_And_Get
366 ( p_count => x_msg_count,
367 p_data => x_msg_data
368 );
369 EXCEPTION
370 WHEN FND_API.G_EXC_ERROR THEN
371 ROLLBACK TO get_msg_work_list_PVT;
372 x_return_status := FND_API.G_RET_STS_ERROR ;
373 FND_MSG_PUB.Count_And_Get
374
375 ( p_count => x_msg_count,
376 p_data => x_msg_data
377 );
378
379 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
380 ROLLBACK TO get_msg_work_list_PVT;
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
382 FND_MSG_PUB.Count_And_Get
383 ( p_count => x_msg_count,
384 p_data => x_msg_data
385 );
386
387 WHEN OTHERS THEN
388 ROLLBACK TO get_msg_work_list_PVT;
389 x_return_status := FND_API.G_RET_STS_ERROR;
390 IF FND_MSG_PUB.Check_Msg_Level
391 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
392 THEN
393 FND_MSG_PUB.Add_Exc_Msg
394 ( G_PKG_NAME ,
395 l_api_name
396 );
397 END IF;
398
399 FND_MSG_PUB.Count_And_Get
400 ( p_count => x_msg_count,
401 p_data => x_msg_data
402
403 );
404 END;
405
406
407 Procedure log_batch_error(
408 p_api_version_number IN NUMBER,
409 p_init_msg_list IN VARCHAR2 := null,
410 p_commit IN VARCHAR2 := null,
411 p_migration_id IN NUMBER,
412 p_mail_ids IN JTF_NUMBER_TABLE,
413 p_error IN VARCHAR2,
414 x_return_status OUT NOCOPY VARCHAR2,
415 x_msg_count OUT NOCOPY NUMBER,
416 x_msg_data OUT NOCOPY VARCHAR2
417 ) is
418
419 l_api_name VARCHAR2(255):='log_batch_error';
420 l_api_version_number NUMBER:=1.0;
421 l_seq_id NUMBER;
422
423 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
424 l_msg_count NUMBER := 0;
425 l_msg_data VARCHAR2(2000);
426
427 i number;
428 l_error IEM_MIGRATION_STORE_TEMP.error_text%type;
429
430
431 BEGIN
432 -- Standard Start of API savepoint
433 SAVEPOINT log_batch_error_PVT;
434
435 -- Standard call to check for call compatibility.
436
437 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
438 p_api_version_number,
439 l_api_name,
440 G_PKG_NAME)
441 THEN
442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443 END IF;
444
445
446 -- Initialize message list if p_init_msg_list is set to TRUE.
447 IF FND_API.to_Boolean( p_init_msg_list )
448 THEN
449 FND_MSG_PUB.initialize;
450 END IF;
451
452 -- Initialize API return status to SUCCESS
453 x_return_status := FND_API.G_RET_STS_SUCCESS;
454
455 l_error := substr(p_error,1,1000);
456
457 For i in 1..p_mail_ids.count loop
458
459 update iem_migration_store_temp set dp_status='E', error_text=l_error
460 where mail_id=p_mail_ids(i);
461
462 end loop;
463
464 update iem_migration_details set status='E', status_text=l_error
465 where migration_id=p_migration_id;
466
467
468 -- Standard Check Of p_commit.
469 IF FND_API.To_Boolean(p_commit) THEN
470 COMMIT WORK;
471 END IF;
472
473 -- Standard callto get message count and if count is 1, get message info.
474 FND_MSG_PUB.Count_And_Get
475 ( p_count => x_msg_count,
476 p_data => x_msg_data
477 );
478 EXCEPTION
479 WHEN FND_API.G_EXC_ERROR THEN
480 ROLLBACK TO log_batch_error_PVT;
481 x_return_status := FND_API.G_RET_STS_ERROR ;
482 FND_MSG_PUB.Count_And_Get
483
484 ( p_count => x_msg_count,
485 p_data => x_msg_data
486 );
487
488 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
489 ROLLBACK TO log_batch_error_PVT;
490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
491 FND_MSG_PUB.Count_And_Get
492 ( p_count => x_msg_count,
493 p_data => x_msg_data
494 );
495
496 WHEN OTHERS THEN
497 ROLLBACK TO log_batch_error_PVT;
498 x_return_status := FND_API.G_RET_STS_ERROR;
499 IF FND_MSG_PUB.Check_Msg_Level
500 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
501 THEN
502 FND_MSG_PUB.Add_Exc_Msg
503 ( G_PKG_NAME ,
504 l_api_name
505 );
506 END IF;
507
508 FND_MSG_PUB.Count_And_Get
509 ( p_count => x_msg_count,
510 p_data => x_msg_data
511
512 );
513 END;
514
515
516 END IEM_DPM_PP_QUEUE_PVT;