[Home] [Help]
PACKAGE BODY: APPS.IEM_ARCH_PVT
Source
1 PACKAGE BODY IEM_ARCH_PVT as
2 /* $Header: iemarcpb.pls 120.7 2005/10/18 11:37:20 rtripath ship $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_ARCH_PVT ';
5 -- Create a Request in the System for archiving or purging.
6
7 PROCEDURE submit_request(p_api_version_number IN NUMBER,
8 p_init_msg_list IN VARCHAR2 ,
9 p_commit IN VARCHAR2 ,
10 p_message_id IN jtf_varchar2_Table_100,
11 p_folder IN VARCHAR2,
12 p_email_account_id in number,
13 p_search_criteria in varchar2,
14 p_request_type in varchar2,
15 x_request_id OUT NOCOPY NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2
19 ) IS
20 l_api_name VARCHAR2(255):='submit_request';
21 l_api_version_number NUMBER:=1.0;
22 l_seq_id NUMBER;
23 l_ret_status varchar2(10);
24 l_msg_count number;
25 l_msg_data varchar2(500);
26 l_arch_folder_id number;
27 l_arch_count number;
28 ERROR_CREATING_DTL_REQUESTS EXCEPTION;
29 ERROR_CREATING_FLD_REQUESTS EXCEPTION;
30 ERROR_CREATING_REQUESTS EXCEPTION;
31
32 BEGIN
33 -- Standard call to check for call compatibility.
34 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
35 p_api_version_number,
36 l_api_name,
37 G_PKG_NAME)
38 THEN
39 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
40 END IF;
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42 SAVEPOINT SUBMIT_REQUEST_PVT;
43 -- Create a New Request Id
44 IF p_message_id.count>0 THEN --There are messages to process
45 select iem_arch_requests_s1.nextval into l_seq_id from dual;
46
47 -- Creating the Source message ids in the archive details table
48 IEM_ARCH_DTLS_PVT. create_item (p_api_version_number=>1.0 ,
49 p_init_msg_list=>'F' ,
50 p_commit=>'F' ,
51 p_request_id =>l_seq_id,
52 p_source_message_id =>p_message_id,
53 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
54 p_CREATION_DATE =>sysdate,
55 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
56 p_LAST_UPDATE_DATE =>sysdate,
57 p_LAST_UPDATE_LOGIN =>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')),
58 x_return_status=>l_ret_status,
59 x_msg_count=>l_msg_count,
60 x_msg_data=>l_msg_data
61 );
62 IF l_ret_status<>'S' then
63 raise ERROR_CREATING_DTL_REQUESTS;
64 END IF;
65 l_arch_count:=p_message_id.count;
66
67 --Finally Create a request in the archive request table
68 IEM_ARCH_REQUESTS_PVT.create_item (p_api_version_number=>1.0 ,
69 p_init_msg_list=>'F' ,
70 p_commit=>'F',
71 p_request_id=>l_seq_id ,
72 p_arch_criteria=>p_search_criteria,
73 p_email_account_id=>p_email_account_id,
74 p_folder_name=>p_folder ,
75 p_arch_folder_id=>l_arch_folder_id,
76 p_arch_count=>l_arch_count,
77 p_request=>p_request_type,
78 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
79 p_CREATION_DATE =>sysdate,
80 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
81 p_LAST_UPDATE_DATE =>sysdate,
82 p_LAST_UPDATE_LOGIN =>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')),
83 x_return_status=>l_ret_status,
84 x_msg_count=>l_msg_count,
85 x_msg_data=>l_msg_data
86 );
87 IF l_ret_status<>'S' then
88 raise ERROR_CREATING_REQUESTS;
89 END IF;
90 x_request_id:=l_seq_id;
91 END IF;
92
93 -- Standard Check Of p_commit.
94 IF p_commit='T' THEN
95 COMMIT WORK;
96 END IF;
97 EXCEPTION
98 WHEN ERROR_CREATING_DTL_REQUESTS THEN
99 rollback to SUBMIT_REQUEST_PVT;
100 x_return_status := FND_API.G_RET_STS_ERROR ;
101 FND_MESSAGE.SET_NAME('IEM','IEM_ARCH_DTL_SUBMIT_REQ_ERROR');
102 FND_MSG_PUB.Add;
103 FND_MSG_PUB.Count_And_Get
104 ( p_count => x_msg_count,
105 p_data => x_msg_data
106 );
107 WHEN ERROR_CREATING_REQUESTS THEN
108 rollback to SUBMIT_REQUEST_PVT;
109 x_return_status := FND_API.G_RET_STS_ERROR ;
110 FND_MESSAGE.SET_NAME('IEM','IEM_ARCH_REQUEST_ERROR');
111 FND_MSG_PUB.Add;
112 FND_MSG_PUB.Count_And_Get
113 ( p_count => x_msg_count,
114 p_data => x_msg_data
115 );
116 WHEN FND_API.G_EXC_ERROR THEN
117 rollback to SUBMIT_REQUEST_PVT;
118 x_return_status := FND_API.G_RET_STS_ERROR ;
119 FND_MSG_PUB.Count_And_Get
120 ( p_count => x_msg_count,
121 p_data => x_msg_data
122 );
123 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
124 rollback to SUBMIT_REQUEST_PVT;
125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
126 FND_MSG_PUB.Count_And_Get
127 ( p_count => x_msg_count,
128 p_data => x_msg_data
129 );
130 WHEN OTHERS THEN
131 rollback to SUBMIT_REQUEST_PVT;
132 x_return_status := FND_API.G_RET_STS_ERROR;
133 IF FND_MSG_PUB.Check_Msg_Level
134 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
135 THEN
136 FND_MSG_PUB.Add_Exc_Msg
137 ( G_PKG_NAME ,
138 l_api_name
139 );
140 END IF;
141 FND_MSG_PUB.Count_And_Get
142 ( p_count => x_msg_count ,
143 p_data => x_msg_data
144 );
145
146 END submit_request;
147 -- Processing Api for archiving.
148
149 -- To be called by concurrent manager for procesing
150
151 PROCEDURE process_request(p_api_version_number IN NUMBER,
152 p_init_msg_list IN VARCHAR2 ,
153 p_commit IN VARCHAR2 ,
154 p_request_id in number,
155 p_request_type in varchar2,
156 x_return_status OUT NOCOPY VARCHAR2,
157 x_msg_count OUT NOCOPY NUMBER,
158 x_msg_data OUT NOCOPY VARCHAR2
159 ) IS
160 l_api_name VARCHAR2(255):='process_request';
161 l_api_version_number NUMBER:=1.0;
162 l_seq_id NUMBER;
163 l_email_account_id NUMBER;
164 l_folder VARCHAR2(100);
165 l_ret_status varchar2(10);
166 l_out_text varchar2(500);
167 m_out_text varchar2(500);
168 l_arch_folder_id number;
169 l_arch_count number;
170 l_milcs_type number;
171 BEGIN
172 -- Standard call to check for call compatibility.
173 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
174 p_api_version_number,
175 l_api_name,
176 G_PKG_NAME)
177 THEN
178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
179 END IF;
180 x_return_status := FND_API.G_RET_STS_SUCCESS;
181 IF p_request_type='P' THEN
182 l_milcs_type:=47;
183 END IF;
184
185 IF l_milcs_type is not null then
186 IEM_ARCH_PVT.CREATE_MLCS(p_request_id=>p_request_id ,
187 p_milcs_type=>l_milcs_type , -- '46'Email Archived
188 x_ret_status=>l_ret_status, -- '47' Email Purged
189 x_out_text=>m_out_text);
190 END IF;
191
192 select count(*) into l_arch_count
193 from iem_archived_dtls
194 where request_id=p_request_id
195 and nvl(media_id,1)>0; -- ignoring data with no media id info where media id=0
196 -- If media id is null we should not ignore as it might be due to
197 -- some processing error like move or delete message error
198 IF l_arch_count=0 THEN
199 update iem_Arch_Requests
200 set status='C'
201 where Request_id=p_request_id;
202 update iem_archived_folders
203 set arch_folder_status='O'
204 where arch_folder_id=l_arch_Folder_id;
205 ELSE
206 update iem_Arch_Requests
207 set status='E',
208 arch_comment=nvl(l_out_text,' ')||nvl(m_out_text,' ')
209 where Request_id=p_request_id;
210 END IF;
211 -- Standard Check Of p_commit.
212 IF p_commit='T' THEN
213 COMMIT WORK;
214 END IF;
215 EXCEPTION
216 WHEN FND_API.G_EXC_ERROR THEN
217 x_return_status := FND_API.G_RET_STS_ERROR ;
218 FND_MSG_PUB.Count_And_Get
219 ( p_count => x_msg_count,
220 p_data => x_msg_data
221 );
222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
224 FND_MSG_PUB.Count_And_Get
225 ( p_count => x_msg_count,
226 p_data => x_msg_data
227 );
228 WHEN OTHERS THEN
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 FND_MSG_PUB.Count_And_Get
239 ( p_count => x_msg_count ,
240 p_data => x_msg_data
241 );
242
243 END process_request;
244 -- Cancel an existing request
245 PROCEDURE cancel_request(p_api_version_number IN NUMBER,
246 p_init_msg_list IN VARCHAR2 ,
247 p_commit IN VARCHAR2 ,
248 p_request_id in number,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2
252 ) IS
253 l_api_name VARCHAR2(255):='delete_request';
254 l_api_version_number NUMBER:=1.0;
255 l_arch_folder IEM_ARCHIVED_FOLDERS.ARCH_FOLDER_NAME%TYPE;
256 l_arch_folder_id IEM_ARCHIVED_FOLDERS.ARCH_FOLDER_ID%TYPE;
257 l_email_account_id IEM_EMAIL_ACCOUNTS.EMAIL_ACCOUNT_ID%TYPE;
258 l_ret_status varchar2(10);
259 l_out_text varchar2(500);
260 ERROR_FOLDER_DELETION EXCEPTION;
261 ERROR_CANCEL_REQUEST EXCEPTION;
262 l_oes_ret_code number;
263 l_req_type iem_arch_requests.request_type%type;
264
265 BEGIN
266 -- Standard call to check for call compatibility.
267 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
268 p_api_version_number,
269 l_api_name,
270 G_PKG_NAME)
271 THEN
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 END IF;
274 x_return_status := FND_API.G_RET_STS_SUCCESS;
275 delete from IEM_ARCH_REQUESTS
276 where request_id=p_request_id;
277 delete from IEM_ARCHIVED_DTLS
278 where request_id=p_request_id;
279 -- Standard Check Of p_commit.
280 IF p_commit='T' THEN
281 COMMIT WORK;
282 END IF;
283 -- MAde changes to return request type instead of a success status to make it more easy in UI
284 x_return_status := l_req_type;
285 EXCEPTION
286 WHEN NO_DATA_FOUND THEN -- Not a Valid Request to cancell Do Nothing
287 null;
288 WHEN ERROR_FOLDER_DELETION THEN
289 x_return_status := FND_API.G_RET_STS_ERROR;
290 FND_MESSAGE.SET_NAME('IEM','IEM_ARCH_OES_FLD_DELETE_ERROR');
291 FND_MESSAGE.Set_Token('CODE',l_oes_ret_code);
292 FND_MSG_PUB.Add;
293 FND_MSG_PUB.Count_And_Get
294 ( p_count => x_msg_count,
295 p_data => x_msg_data
296 );
297 FND_MSG_PUB.Count_And_Get
298 ( p_count => x_msg_count,
299 p_data => x_msg_data );
300 WHEN FND_API.G_EXC_ERROR THEN
301 x_return_status := FND_API.G_RET_STS_ERROR;
302 FND_MSG_PUB.Count_And_Get
303 ( p_count => x_msg_count,
304 p_data => x_msg_data
305 );
306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR||p_request_id ;
308 FND_MSG_PUB.Count_And_Get
309 ( p_count => x_msg_count,
310 p_data => x_msg_data
311 );
312 WHEN ERROR_CANCEL_REQUEST THEN
313 x_return_status := FND_API.G_RET_STS_ERROR;
314 FND_MESSAGE.SET_NAME('IEM','IEM_ARCH_CANCEL_ERROR');
315 FND_MESSAGE.Set_Token('ERROR_STRING',l_oes_ret_code);
316 FND_MSG_PUB.Add;
317 FND_MSG_PUB.Count_And_Get
318 ( p_count => x_msg_count ,
319 p_data => x_msg_data
320 );
321 WHEN OTHERS THEN
322 x_return_status := FND_API.G_RET_STS_ERROR;
323 IF FND_MSG_PUB.Check_Msg_Level
324 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
325 THEN
326 FND_MSG_PUB.Add_Exc_Msg
327 ( G_PKG_NAME ,
328 l_api_name
329 );
330 END IF;
331 FND_MSG_PUB.Count_And_Get
332 ( p_count => x_msg_count ,
333 p_data => x_msg_data
334 );
335
336 END cancel_request;
337
338 -- Return the Folder message count, Last archived date and action required flag
339 PROCEDURE get_folder_dtl(
340 p_email_account_id IN NUMBER,
341 p_folder IN VARCHAR2,
342 p_date IN varchar2,
343 p_date_format IN varchar2,
344 x_count OUT NOCOPY NUMBER,
345 x_msg_table OUT NOCOPY jtf_number_table,
346 x_arch_date OUT NOCOPY VARCHAR2,
347 x_action_flg OUT NOCOPY VARCHAR2, --Y/N
348 x_action_desc OUT NOCOPY VARCHAR2, --Y/N
349 x_ret_status OUT NOCOPY VARCHAR2,
350 x_out_text OUT NOCOPY VARCHAR2) IS
351
352 l_ret number;
353 l_count number;
354 l_proc_count number;
355 l_arch_date varchar2(100);
356 l_from varchar2(100):=null;
357 l_msg_count number;
358 l_msg_data varchar2(500);
359 l_action_flg varchar2(10);
360 l_date date;
361 cursor c1 is
362 select message_id
363 from iem_arch_msgdtls where
364 email_account_id=p_email_account_id and mailproc_Status=substr(p_folder,1,1)
365 and received_date<l_date;
366 cursor c2 is
367 select message_id
368 from iem_arch_msgdtls where
369 email_account_id=p_email_account_id and mailproc_Status=substr(p_folder,1,1)
370 and creation_date<l_date;
371 ERROR_FOLDER_COUNT EXCEPTION;
372 BEGIN
373 select to_date(p_date,p_date_format) into l_date from dual;
374 BEGIN
375 select to_char(max(creation_date),p_date_format)
376 into x_arch_date
377 from iem_arch_requests
378 where email_account_id=p_email_account_id
379 and folder_name=p_folder and status ='C';
380 EXCEPTION WHEN OTHERS THEN
381 x_arch_date:=null;
382 END;
383
384 select count(*) into x_count
385 from iem_arch_msgdtls where
386 email_account_id=p_email_account_id and mailproc_Status=substr(p_folder,1,1);
387 if substr(p_folder,1,1)='S' then
388 open c2;
389 fetch c2 bulk collect into x_msg_table;
390 close c2;
391 select count(*) into l_count
392 from iem_arch_msgdtls where
393 email_account_id=p_email_account_id and mailproc_Status=substr(p_folder,1,1)
394 and creation_date<l_date;
395 else
396 open c1;
397 fetch c1 bulk collect into x_msg_table;
398 close c1;
399 end if;
400 l_count:=x_msg_table.count;
401 select count(*) into l_proc_count
402 from iem_arch_requests
403 where email_account_id=p_email_account_id
404 and folder_name=p_folder
405 and status in ('S','E','P');
406 IF l_proc_count=0 and l_count>0 then
407 l_action_flg:='Y';
408 ELSE
409 l_action_flg:='N';
410 END IF;
411 select meaning into x_action_desc
412 from fnd_lookups
413 where lookup_type='IEM_ARCH_STATUS'
414 and lookup_code=l_action_flg;
415 x_action_flg:=l_action_flg;
416 x_ret_status:='S';
417 EXCEPTION WHEN OTHERS THEN
418 x_ret_status:='E';
419 FND_MESSAGE.SET_NAME('IEM','IEM_ARCH_ORACLE_ERROR');
420 FND_MSG_PUB.Add;
421 END get_folder_dtl;
422
423 PROCEDURE PROC_REQUESTS(ERRBUF OUT NOCOPY VARCHAR2,
424 ERRRET OUT NOCOPY VARCHAR2,
425 p_api_version_number in number:= 1.0) IS
426 cursor c1 is
427 select request_id,request_type from iem_arch_Requests
428 where status='S'
429 order by creation_date for update;
430 l_ret_status varchar2(10);
431 l_msg_data varchar2(1000);
432 l_msg_count number;
433 l_request_id number;
434 l_req_type varchar2(10);
435 l_proc_flag varchar2(1):='T';
436 BEGIN
437 LOOP -- Outer Loop
438 l_proc_flag:='F';
439 for v1 in c1 LOOP
440 update iem_arch_Requests
441 set status='I'
442 where request_id=v1.request_id;
443 l_request_id:=v1.request_id;
444 l_req_type:=v1.request_type;
445 l_proc_flag:='T';
446 EXIT;
447 END LOOP;
448 commit;
449 IF l_proc_flag='T' THEN
450 IEM_ARCH_PVT.process_request(p_api_version_number=>1.0,
451 p_init_msg_list=>'F',
452 p_commit=>'F',
453 p_request_id=>l_request_id ,
454 p_request_type=>l_req_type,
455 x_return_status=>l_ret_status ,
456 x_msg_count=>l_msg_count,
457 x_msg_data=>l_msg_data);
458 END IF;
459 EXIT when l_proc_flag='F';
460 END LOOP; --End of outer Loop
461 end PROC_REQUESTS;
462 PROCEDURE CREATE_MLCS(p_request_id in number,
463 p_milcs_type in number,
464 x_ret_status OUT NOCOPY VARCHAR2,
465 x_out_text OUT NOCOPY VARCHAR2) IS
466 IH_EXCEPTION EXCEPTION;
467 l_media_lc_rec JTF_IH_PUB.media_lc_rec_type;
468 l_ret_status varchar2(10);
469 l_msg_count number;
470 l_msg_data varchar2(500);
471 l_milcs_id number;
472 cursor c1 is select a.ih_media_item_id,b.source_message_id from iem_arch_msgdtls a,iem_archived_dtls b
473 where b.request_id=p_request_id
474 and a.message_id=b.source_message_id;
475 l_type_id number;
476 begin
477 FOR v1 IN c1 LOOP
478 l_media_lc_rec.media_id :=v1.ih_media_item_id ;
479 l_media_lc_rec.milcs_type_id := p_milcs_type;
480 l_media_lc_rec.start_date_time := sysdate;
481 l_media_lc_rec.handler_id := 680;
482 l_media_lc_rec.type_type := 'Email, Inbound';
483
484 JTF_IH_PUB.Add_MediaLifeCycle( 1.0,
485 'T',
486 'F',
487 TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
488 TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
489 nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
490 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
491 l_ret_status,
492 l_msg_count,
493 l_msg_data,
494 l_media_lc_rec,
495 l_milcs_id);
496
497 if l_ret_status<>'S' then
498 update iem_archived_dtls
499 set error_summary='Error While Creating MLCS'
500 where request_id=p_request_id and source_message_id=v1.source_message_id;
501 x_out_text:='Error While Creating MLCS';
502 else
503 delete from iem_archived_dtls where request_id=p_request_id
504 and source_message_id=v1.source_message_id;
505 delete from iem_arch_msgs where message_id=v1.source_message_id;
506 delete from iem_arch_msgdtls where message_id=v1.source_message_id;
507 end if;
508 -- Update the MLCS with ENDDATE TIME
509 l_media_lc_rec.milcs_id:=l_milcs_id;
510 JTF_IH_PUB.Update_MediaLifeCycle( 1.0,
511 'T',
512 'F',
513 TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
514 TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
515 nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
516 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
517 l_ret_status,
518 l_msg_count,
519 l_msg_data,
520 l_media_lc_rec);
521 if l_ret_status<>'S' then
522 update iem_archived_dtls
523 set error_summary='Error While Updating MLCS'
524 where request_id=p_request_id and source_message_id=v1.source_message_id;
525 x_out_text:='Error While Updating MLCS';
526 end if;
527 END LOOP;
528 x_ret_status:='S';
529 EXCEPTION
530 when OTHERS THEN
531 x_out_text:='An Error Occured while Creating MLCS '||sqlerrm;
532 x_ret_status:='E';
533 END CREATE_MLCS;
534 END IEM_ARCH_PVT ;