[Home] [Help]
PACKAGE BODY: APPS.IEM_DP_MONITORING_PVT
Source
1 PACKAGE BODY IEM_DP_MONITORING_PVT AS
2 /* $Header: iemvmonb.pls 120.13 2006/05/19 14:05:04 rtripath noship $ */
3
4 --
5 --
6 -- Purpose: Mantain Download Processor monitoring data
7 --
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- Liang Xia 02/25/2005 Created
11 -- Liang Xia 08/05/2005 Updated cleanup_monitoring_data
12 -- Liang Xia 08/09/2005 Changed DP service name
13 -- Liang Xia 08/15/2005 GET_DP_RUNNING_STATUS
14 -- Liang Xia 08/24/2005 Fixed bug: filter out deleted account
15 -- Liang Xia 10/07/2005 Fixed bug 4628971
16 -- R12UT:950 - ICON FOR STATUS NEEDS TO BE NOT STARTED WHEN DP STARTED
17 -- Liang Xia 10/07/2005 Fixed bug 4628959
18 -- R12UT:950 - PROCESSOR STATUS FOR NEW ACTIVE ACCOUNTS INCORRECT
19 -- Liang Xia 11/07/2005 Fixed bug 4628955
20 -- --------- ------ ------------------------------------------
21
22 -- Enter procedure, function bodies as shown below
23 G_PKG_NAME CONSTANT varchar2(30) :='IEM_DP_MONITORING_PVT ';
24 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
25 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID') ) ;
26
27 PROCEDURE CREATE_DP_ACCT_STATUS (
28 p_api_version_number IN NUMBER,
29 p_init_msg_list IN VARCHAR2 := null,
30 p_commit IN VARCHAR2 := null,
31 P_acct_id IN number,
32 p_inbox_count IN number,
33 p_processed_count IN number,
34 p_retry_count IN number,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2
38 ) is
39 l_api_name VARCHAR2(255):='CREATE_DP_ACCT_STATUS';
40 l_api_version_number NUMBER:=1.0;
41 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
42 l_msg_count NUMBER := 0;
43 l_msg_data VARCHAR2(2000);
44 l_seq_id NUMBER := 10000;
45
46
47 logMessage varchar2(2000);
48
49
50 BEGIN
51 -- Standard Start of API savepoint
52 SAVEPOINT create_item_PVT;
53
54 -- Standard call to check for call compatibility.
55
56 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
57 p_api_version_number,
58 l_api_name,
59 G_PKG_NAME)
60 THEN
61 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
62 END IF;
63
64 -- Initialize message list if p_init_msg_list is set to TRUE.
65 IF FND_API.to_Boolean( p_init_msg_list )
66 THEN
67 FND_MSG_PUB.initialize;
68 END IF;
69
70
71 -- Initialize API return status to SUCCESS
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 --begins here
75
76 --get next sequential number
77 SELECT IEM_DP_ACCT_STATUS_S1.nextval
78 INTO l_seq_id
79 FROM dual;
80
81 INSERT INTO IEM_DP_ACCT_STATUS
82 (
83 DP_ACCT_STATUS_ID,
84 EMAIL_ACCOUNT_ID,
85 INBOX_MSG_COUNT,
86 PROCESSED_MSG_COUNT,
87 RETRY_MSG_COUNT,
88 CREATED_BY,
89 CREATION_DATE,
90 LAST_UPDATED_BY,
91 LAST_UPDATE_DATE,
92 LAST_UPDATE_LOGIN
93 )
94 VALUES
95 (
96 l_seq_id,
97 P_ACCT_ID,
98 P_INBOX_COUNT,
99 P_PROCESSED_COUNT,
100 P_RETRY_COUNT,
101 decode(G_created_updated_by,null,-1,G_created_updated_by),
102 sysdate,
103 decode(G_created_updated_by,null,-1,G_created_updated_by),
104 sysdate,
105 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
106 );
107 -- Standard Check Of p_commit.
108 IF FND_API.To_Boolean(p_commit) THEN
109 COMMIT WORK;
110 END IF;
111
112 -- Standard callto get message count and if count is 1, get message info.
113 FND_MSG_PUB.Count_And_Get
114 ( p_count => x_msg_count,
115 p_data => x_msg_data
116 );
117 EXCEPTION
118
119 WHEN FND_API.G_EXC_ERROR THEN
120 ROLLBACK TO create_item_PVT;
121 x_return_status := FND_API.G_RET_STS_ERROR ;
122
123 FND_MSG_PUB.Count_And_Get
124
125 ( p_count => x_msg_count,
126 p_data => x_msg_data
127 );
128
129 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
130 ROLLBACK TO create_item_PVT;
131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
132 FND_MSG_PUB.Count_And_Get
133 ( p_count => x_msg_count,
134 p_data => x_msg_data
135 );
136
137 WHEN OTHERS THEN
138 ROLLBACK TO create_item_PVT;
139 x_return_status := FND_API.G_RET_STS_ERROR;
140 IF FND_MSG_PUB.Check_Msg_Level
141 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
142 THEN
143 FND_MSG_PUB.Add_Exc_Msg
144 ( G_PKG_NAME ,
145 l_api_name
146 );
147 END IF;
148
149 FND_MSG_PUB.Count_And_Get
150 ( p_count => x_msg_count,
151 p_data => x_msg_data
152
153 );
154 END;
155
156
157 PROCEDURE RECORD_ACCT_STATUS (
158 p_api_version_number IN NUMBER,
159 p_init_msg_list IN VARCHAR2 := null,
160 p_commit IN VARCHAR2 := null,
161 P_acct_id IN number,
162 p_inbox_count IN number,
163 p_processed_count IN number,
164 p_retry_count IN number,
165 p_error_flag IN number,
166 x_return_status OUT NOCOPY VARCHAR2,
167 x_msg_count OUT NOCOPY NUMBER,
168 x_msg_data OUT NOCOPY VARCHAR2
169 ) is
170 l_api_name VARCHAR2(255):='RECORD_ACCT_STATUS';
171 l_api_version_number NUMBER:=1.0;
172
173 l_count NUMBER ;
174 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
175 l_msg_count NUMBER := 0;
176 l_msg_data VARCHAR2(2000);
177
178 BEGIN
179 -- Standard Start of API savepoint
180 SAVEPOINT create_item_PVT;
181
182 -- Standard call to check for call compatibility.
183
184 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
185 p_api_version_number,
186 l_api_name,
187 G_PKG_NAME)
188 THEN
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190 END IF;
191
192 -- Initialize message list if p_init_msg_list is set to TRUE.
193 IF FND_API.to_Boolean( p_init_msg_list )
194 THEN
195 FND_MSG_PUB.initialize;
196 END IF;
197
198
199 -- Initialize API return status to SUCCESS
200 x_return_status := FND_API.G_RET_STS_SUCCESS;
201
202 --begins here
203
204 select count(DP_ACCT_STATUS_ID) into l_count from IEM_DP_ACCT_STATUS where email_account_id=p_acct_id;
205
206 --Check if account record already exist,
207 -- if existed, updated record
208 -- else create new records.
209 if l_count > 0 then
210 if p_error_flag = 0 then
211 IEM_DP_MONITORING_PVT.update_dp_acct_status(
212 p_api_version_number => P_Api_Version_Number,
213 p_init_msg_list => FND_API.G_FALSE,
214 p_commit => P_Commit,
215 P_acct_id => P_acct_id,
216 p_inbox_count => p_inbox_count,
217 p_processed_count => p_processed_count,
218 p_retry_count => p_retry_count,
219 x_return_status =>l_return_status,
220 x_msg_count => l_msg_count,
221 x_msg_data => l_msg_data);
222 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
223 x_return_status := FND_API.G_RET_STS_ERROR;
224 end if;
225 else
226 update IEM_DP_ACCT_STATUS set LAST_UPDATE_DATE = sysdate where email_account_id=P_acct_id;
227 end if;
228 else
229 IEM_DP_MONITORING_PVT.create_dp_acct_status(
230 p_api_version_number => P_Api_Version_Number,
231 p_init_msg_list => FND_API.G_FALSE,
232 p_commit => P_Commit,
233 P_acct_id => P_acct_id,
234 p_inbox_count => p_inbox_count,
235 p_processed_count => p_processed_count,
236 p_retry_count => p_retry_count,
237 x_return_status =>l_return_status,
238 x_msg_count => l_msg_count,
239 x_msg_data => l_msg_data);
240
241 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 end if;
244
245 end if;
246
247 -- Standard Check Of p_commit.
248 IF FND_API.To_Boolean(p_commit) THEN
249 COMMIT WORK;
250 END IF;
251
252 -- Standard callto get message count and if count is 1, get message info.
253 FND_MSG_PUB.Count_And_Get
254 ( p_count => x_msg_count,
255 p_data => x_msg_data
256 );
257 EXCEPTION
258
259 WHEN FND_API.G_EXC_ERROR THEN
260 ROLLBACK TO create_item_PVT;
261 x_return_status := FND_API.G_RET_STS_ERROR ;
262
263 FND_MSG_PUB.Count_And_Get
264
265 ( p_count => x_msg_count,
266 p_data => x_msg_data
267 );
268
269 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
270 ROLLBACK TO create_item_PVT;
271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
272 FND_MSG_PUB.Count_And_Get
273 ( p_count => x_msg_count,
274 p_data => x_msg_data
275 );
276
277 WHEN OTHERS THEN
278 ROLLBACK TO create_item_PVT;
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 IF FND_MSG_PUB.Check_Msg_Level
281 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
282 THEN
283 FND_MSG_PUB.Add_Exc_Msg
284 ( G_PKG_NAME ,
285 l_api_name
286 );
287 END IF;
288
289 FND_MSG_PUB.Count_And_Get
290 ( p_count => x_msg_count,
291 p_data => x_msg_data
292
293 );
294 END;
295
296 PROCEDURE UPDATE_DP_ACCT_STATUS (
297 p_api_version_number IN NUMBER,
298 p_init_msg_list IN VARCHAR2 := null,
299 p_commit IN VARCHAR2 := null,
300 P_acct_id IN number,
301 p_inbox_count IN number,
302 p_processed_count IN number,
303 p_retry_count IN number,
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2
307 ) is
308 l_api_name VARCHAR2(255):='UPDATE_DP_ACCT_STATUS';
309 l_api_version_number NUMBER:=1.0;
310
311 BEGIN
312 -- Standard Start of API savepoint
313 SAVEPOINT create_item_PVT;
314
315 -- Standard call to check for call compatibility.
316
317 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
318 p_api_version_number,
319 l_api_name,
320 G_PKG_NAME)
321 THEN
322 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
323 END IF;
324
325 -- Initialize message list if p_init_msg_list is set to TRUE.
326 IF FND_API.to_Boolean( p_init_msg_list )
327 THEN
328 FND_MSG_PUB.initialize;
329 END IF;
330
331
332 -- Initialize API return status to SUCCESS
333 x_return_status := FND_API.G_RET_STS_SUCCESS;
334
335 --begins here
336
337 UPDATE IEM_DP_ACCT_STATUS
338 set
339 INBOX_MSG_COUNT = P_INBOX_COUNT,
340 PROCESSED_MSG_COUNT = P_PROCESSED_COUNT,
341 RETRY_MSG_COUNT = P_RETRY_COUNT,
342 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
343 LAST_UPDATE_DATE = sysdate,
344 LAST_UPDATE_LOGIN = decode(G_created_updated_by,null,-1,G_created_updated_by)
345 where
346 EMAIL_ACCOUNT_ID = p_acct_id;
347
348 -- Standard Check Of p_commit.
349 IF FND_API.To_Boolean(p_commit) THEN
350 COMMIT WORK;
351 END IF;
352
353 -- Standard callto get message count and if count is 1, get message info.
354 FND_MSG_PUB.Count_And_Get
355 ( p_count => x_msg_count,
356 p_data => x_msg_data
357 );
358 EXCEPTION
359
360 WHEN FND_API.G_EXC_ERROR THEN
361 ROLLBACK TO create_item_PVT;
362 x_return_status := FND_API.G_RET_STS_ERROR ;
363
364 FND_MSG_PUB.Count_And_Get
365
366 ( p_count => x_msg_count,
367 p_data => x_msg_data
368 );
369
370 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
371 ROLLBACK TO create_item_PVT;
372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
373 FND_MSG_PUB.Count_And_Get
374 ( p_count => x_msg_count,
375 p_data => x_msg_data
376 );
377
378 WHEN OTHERS THEN
379 ROLLBACK TO create_item_PVT;
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 IF FND_MSG_PUB.Check_Msg_Level
382 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
383 THEN
384 FND_MSG_PUB.Add_Exc_Msg
385 ( G_PKG_NAME ,
386 l_api_name
387 );
388 END IF;
389
390 FND_MSG_PUB.Count_And_Get
391 ( p_count => x_msg_count,
392 p_data => x_msg_data
393
394 );
395 END;
396
397
398 PROCEDURE CREATE_PROCESS_STATUS (
399 p_api_version_number IN NUMBER,
400 p_init_msg_list IN VARCHAR2 := null,
401 p_commit IN VARCHAR2 := null,
402 P_process_id IN VARCHAR2,
403 x_status_id OUT NOCOPY NUMBER,
404 x_return_status OUT NOCOPY VARCHAR2,
405 x_msg_count OUT NOCOPY NUMBER,
406 x_msg_data OUT NOCOPY VARCHAR2
407 ) is
408 l_api_name VARCHAR2(255):='CREATE_PROCESS_STATUS';
409 l_api_version_number NUMBER:=1.0;
410 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
411
412 l_seq_id NUMBER := 10000;
413
414 BEGIN
415 -- Standard Start of API savepoint
416 SAVEPOINT create_item_PVT;
417
418 -- Standard call to check for call compatibility.
419
420 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
421 p_api_version_number,
422 l_api_name,
423 G_PKG_NAME)
424 THEN
425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
426 END IF;
427
428 -- Initialize message list if p_init_msg_list is set to TRUE.
429 IF FND_API.to_Boolean( p_init_msg_list )
430 THEN
431 FND_MSG_PUB.initialize;
432 END IF;
433
434
435 -- Initialize API return status to SUCCESS
436 x_return_status := FND_API.G_RET_STS_SUCCESS;
437
438 --begins here
439
440
441 --get next sequential number
442 SELECT IEM_DP_PROCESS_STATUS_S1.nextval
443 INTO l_seq_id
444 FROM dual;
445
446 INSERT INTO IEM_DP_PROCESS_STATUS
447 (
448 DP_PROCESS_STATUS_ID,
449 PROCESS_ID,
450 PROCESSED_MSG_COUNT,
451 RETRY_MSG_COUNT,
452 CREATED_BY,
453 CREATION_DATE,
454 LAST_UPDATED_BY,
455 LAST_UPDATE_DATE,
456 LAST_UPDATE_LOGIN
457 )
458 VALUES
459 (
460 l_seq_id,
461 P_PROCESS_ID,
462 0,
463 0,
464 decode(G_created_updated_by,null,-1,G_created_updated_by),
465 sysdate,
466 decode(G_created_updated_by,null,-1,G_created_updated_by),
467 sysdate,
468 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
469 );
470
471 x_status_id := l_seq_id;
472
473 -- Standard Check Of p_commit.
474 IF FND_API.To_Boolean(p_commit) THEN
475 COMMIT WORK;
476 END IF;
477
478 -- Standard callto get message count and if count is 1, get message info.
479 FND_MSG_PUB.Count_And_Get
480 ( p_count => x_msg_count,
481 p_data => x_msg_data
482 );
483 EXCEPTION
484
485 WHEN FND_API.G_EXC_ERROR THEN
486 ROLLBACK TO create_item_PVT;
487 x_return_status := FND_API.G_RET_STS_ERROR ;
488
489 FND_MSG_PUB.Count_And_Get
490
491 ( p_count => x_msg_count,
492 p_data => x_msg_data
493 );
494
495 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
496 ROLLBACK TO create_item_PVT;
497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
498 FND_MSG_PUB.Count_And_Get
499 ( p_count => x_msg_count,
500 p_data => x_msg_data
501 );
502
503 WHEN OTHERS THEN
504 ROLLBACK TO create_item_PVT;
505 x_return_status := FND_API.G_RET_STS_ERROR;
506 IF FND_MSG_PUB.Check_Msg_Level
507 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
508 THEN
509 FND_MSG_PUB.Add_Exc_Msg
510 ( G_PKG_NAME ,
511 l_api_name
512 );
513 END IF;
514
515 FND_MSG_PUB.Count_And_Get
516 ( p_count => x_msg_count,
517 p_data => x_msg_data
518
519 );
520 END;
521
522 PROCEDURE cleanup_monitoring_data
523 (p_api_version_number IN NUMBER,
524 P_init_msg_list IN VARCHAR2 := null,
525 p_commit IN VARCHAR2 := null,
526 p_preproc_sleep IN NUMBER,
527 p_postproc_sleep IN NUMBER,
528 x_return_status OUT NOCOPY VARCHAR2,
529 x_msg_count OUT NOCOPY NUMBER,
530 x_msg_data OUT NOCOPY VARCHAR2)
531 IS
532 l_api_name varchar2(30):='cleanup_monitoring_data_PVT';
533 l_api_version_number number:=1.0;
534 logMessage varchar2(2000);
535
536 l_count NUMBER ;
537 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
538 l_msg_count NUMBER := 0;
539 l_msg_data VARCHAR2(2000);
540
541 IEM_ERR_QUE_RESET EXCEPTION;
542 BEGIN
543
544 --Standard Savepoint
545 SAVEPOINT cleanup_monitoring_data_PVT;
546
547 -- Standard call to check for call compatibility.
548 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
549 p_api_version_number,
550 l_api_name,
551 G_PKG_NAME)
552 THEN
553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
554 END IF;
555
556 --Initialize the message list if p_init_msg_list is set to TRUE
557 If FND_API.to_Boolean(p_init_msg_list) THEN
558 FND_MSG_PUB.initialize;
559 END IF;
560
561 --Initialize API status return
562 x_return_status := FND_API.G_RET_STS_SUCCESS;
563
564
565 DELETE
566 FROM IEM_DP_PROCESS_STATUS;
567
568 DELETE
569 FROM IEM_DP_LOGS;
570
571
572 IEM_PP_QUEUE_PVT.reset_data (
573 p_api_version_number =>P_Api_Version_Number,
574 p_init_msg_list => FND_API.G_FALSE,
575 p_commit => 'F',
576 x_return_status =>l_return_status,
577 x_msg_count => l_msg_count,
578 x_msg_data => l_msg_data);
579
580 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
581 raise IEM_ERR_QUE_RESET;
582 end if;
583
584 -- Insert parameters into iem_comp_rt_stats
585 delete IEM_COMP_RT_STATS where type='DOWNLOAD PROCESSOR';
586
587 IEM_COMP_RT_STATS_PVT.create_item (
588 p_api_version_number =>P_Api_Version_Number,
589 p_init_msg_list => FND_API.G_FALSE,
590 p_commit => 'F',
591 p_type => 'DOWNLOAD PROCESSOR',
592 p_param => 'POSTPROC_SLEEP_DURATION',
593 p_value => p_postproc_sleep,
594 x_return_status =>l_return_status,
595 x_msg_count => l_msg_count,
596 x_msg_data => l_msg_data);
597
598 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
599 raise IEM_ERR_QUE_RESET;
600 end if;
601
602 IEM_COMP_RT_STATS_PVT.create_item (
603 p_api_version_number =>P_Api_Version_Number,
604 p_init_msg_list => FND_API.G_FALSE,
605 p_commit => 'F',
606 p_type => 'DOWNLOAD PROCESSOR',
607 p_param => 'PREPROC_SLEEP_DURATION',
608 p_value => p_preproc_sleep,
609 x_return_status =>l_return_status,
610 x_msg_count => l_msg_count,
611 x_msg_data => l_msg_data);
612
613
614 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
615 raise IEM_ERR_QUE_RESET;
616 end if;
617
618 --Standard check of p_commit
619 IF FND_API.to_Boolean(p_commit) THEN
620 COMMIT WORK;
621 END IF;
622
623 FND_MSG_PUB.Count_And_Get
624 ( p_count => x_msg_count,p_data => x_msg_data);
625
626 EXCEPTION
627
628 WHEN FND_API.G_EXC_ERROR THEN
629 ROLLBACK TO cleanup_monitoring_data_PVT;
630 x_return_status := FND_API.G_RET_STS_ERROR ;
631 FND_MSG_PUB.Count_And_Get
632 ( p_count => x_msg_count,p_data => x_msg_data);
633
634 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
635 ROLLBACK TO cleanup_monitoring_data_PVT;
636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
637 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
638
639
640 WHEN OTHERS THEN
641 ROLLBACK TO cleanup_monitoring_data_PVT;
642
643 x_return_status := FND_API.G_RET_STS_ERROR;
644 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
645 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
646 END IF;
647 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
648
649 END cleanup_monitoring_data;
650
651
652 PROCEDURE GET_DP_RUNNING_STATUS
653 (p_api_version_number IN NUMBER,
654 P_init_msg_list IN VARCHAR2 := null,
655 p_commit IN VARCHAR2 := null,
656 p_mode IN VARCHAR2 := null,
657 x_DP_STATUS OUT NOCOPY VARCHAR2,
658 x_return_status OUT NOCOPY VARCHAR2,
659 x_msg_count OUT NOCOPY NUMBER,
660 x_msg_data OUT NOCOPY VARCHAR2)
661 is
662 l_api_name VARCHAR2(255):='GET_DP_RUNNING_STATUS';
663 l_api_version_number NUMBER:=1.0;
664
665 y number :=1;
666 l_count number;
667 l_instance FND_CONCURRENT.Service_Instance_Tab_Type;
668
669 BEGIN
670 -- Standard Start of API savepoint
671 SAVEPOINT GET_DP_RUNNING_STATUS;
672
673 -- Standard call to check for call compatibility.
674 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
675 p_api_version_number,
676 l_api_name,
677 G_PKG_NAME)
678 THEN
679 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
680 END IF;
681
682 -- Initialize message list if p_init_msg_list is set to TRUE.
683 IF FND_API.to_Boolean( p_init_msg_list )
684 THEN
685 FND_MSG_PUB.initialize;
686 END IF;
687
688 -- Initialize API return status to SUCCESS
689 x_return_status := FND_API.G_RET_STS_SUCCESS;
690
691 l_instance := FND_CONCURRENT.Get_Service_Instances('IEMDPDEV');
692
693 if ( l_instance is not null ) then
694
695 l_count := l_instance.count;
696
697 while y <= l_count loop
698 if ( p_mode = 'N' ) then
699
700 if ( l_instance(y).Instance_Name = 'DownloadProcessorNormalMode') then
701 if ( l_instance(y).State = 'ACTIVE' ) then
702 x_DP_STATUS := 'Active';
703 elsif ( l_instance(y).State='INACTIVE' or l_instance(y).State='DISABLED') then
704 x_DP_STATUS := 'NotStarted';
705 else
706 x_DP_STATUS := 'Inactive';
707 end if;
708
709 exit;
710 end if;
711 else
712
713 if ( l_instance(y).Instance_Name = 'DownloadProcessorMigrationMode') then
714 if ( l_instance(y).State = 'ACTIVE' ) then
715 x_DP_STATUS := 'Active';
716 elsif ( l_instance(y).State='INACTIVE' or l_instance(y).State='DISABLED') then
717 x_DP_STATUS := 'NotStarted';
718 else
719 x_DP_STATUS := 'Inactive';
720 end if;
721
722 exit;
723 end if;
724 end if;
725
726 y := Y+1;
727 end loop;
728 end if;
729
730 -- Standard Check Of p_commit.
731 IF FND_API.To_Boolean(p_commit) THEN
732 COMMIT WORK;
733 END IF;
734
735 -- Standard callto get message count and if count is 1, get message info.
736 FND_MSG_PUB.Count_And_Get
737 ( p_count => x_msg_count,
738 p_data => x_msg_data
739 );
740 EXCEPTION
741
742
743 WHEN FND_API.G_EXC_ERROR THEN
744
745 ROLLBACK TO GET_DP_RUNNING_STATUS;
746 x_return_status := FND_API.G_RET_STS_ERROR ;
747 FND_MSG_PUB.Count_And_Get
748 ( p_count => x_msg_count,
749 p_data => x_msg_data
750 );
751 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
752
753 ROLLBACK TO GET_DP_RUNNING_STATUS;
754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
755 FND_MSG_PUB.Count_And_Get
756 ( p_count => x_msg_count,
757 p_data => x_msg_data
758 );
759 WHEN OTHERS THEN
760
761 ROLLBACK TO GET_DP_RUNNING_STATUS;
762 x_return_status := FND_API.G_RET_STS_ERROR;
763 IF FND_MSG_PUB.Check_Msg_Level
764 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
765 THEN
766 FND_MSG_PUB.Add_Exc_Msg
767 ( G_PKG_NAME ,
768 l_api_name
769 );
770 END IF;
771 FND_MSG_PUB.Count_And_Get
772 ( p_count => x_msg_count ,
773 p_data => x_msg_data
774 );
775
776 END GET_DP_RUNNING_STATUS;
777
778
779 PROCEDURE GET_ACCOUNT_DP_STATUS
780 (p_api_version_number IN NUMBER,
781 P_init_msg_list IN VARCHAR2 := null,
782 p_commit IN VARCHAR2 := null,
783 P_view_all_accounts IN VARCHAR2,
784 x_account_ids OUT NOCOPY jtf_number_Table,
785 x_email_address OUT NOCOPY jtf_varchar2_Table_200,
786 x_account_status OUT NOCOPY jtf_varchar2_Table_100,
787 x_processor_status OUT NOCOPY jtf_varchar2_Table_100,
788 x_last_run_time OUT NOCOPY jtf_date_Table,
789 x_inbox_msg_count OUT NOCOPY jtf_number_Table,
790 x_process_msg_count OUT NOCOPY jtf_number_Table,
791 x_retry_msg_count OUT NOCOPY jtf_number_Table,
792 x_log OUT NOCOPY jtf_varchar2_Table_100,
793 x_return_status OUT NOCOPY VARCHAR2,
794 x_msg_count OUT NOCOPY NUMBER,
795 x_msg_data OUT NOCOPY VARCHAR2)
796 is
797 l_api_name VARCHAR2(255):='GET_ACCOUNT_DP_STATUS';
798 l_api_version_number NUMBER:=1.0;
799
800 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
801 l_msg_count NUMBER := 0;
802 l_msg_data VARCHAR2(2000);
803
804 l_dp_status VARCHAR2(10);
805 l_proc_status date; --VARCHAR2(50); --Date;
806 l_count_error NUMBER;
807 i number;
808 l_pre_sleep number := 60000;
809 l_count number;
810
811 l_account_ids jtf_number_Table := jtf_number_Table();
812 l_email_address jtf_varchar2_Table_200 := jtf_varchar2_Table_200();
813 l_account_status jtf_varchar2_Table_100 := jtf_varchar2_Table_100();
814 l_processor_status jtf_varchar2_Table_100:= jtf_varchar2_Table_100();
815 l_last_run_time jtf_date_Table:= jtf_date_Table();
816 l_inbox_msg_count jtf_number_Table:= jtf_number_Table();
817 l_process_msg_count jtf_number_Table:= jtf_number_Table();
818 l_retry_msg_count jtf_number_Table:= jtf_number_Table();
819 l_log jtf_varchar2_Table_100:= jtf_varchar2_Table_100();
820
821 cursor c_results is
822
823 select a.email_account_id, a.email_address, a.active_flag, fl.meaning as account_status,
824 b.last_update_date as processor_status, b.last_update_date as last_run_time,
825 b.inbox_msg_count, b.processed_msg_count, b.retry_msg_count,
826 (select count(*) from iem_dp_logs where email_account_id = a.email_account_id) as log
827 from iem_mstemail_accounts a, iem_dp_acct_status b, fnd_lookups fl
828 where a.email_account_id = b.email_account_id
829 and a.active_flag=fl.lookup_code and fl.lookup_type='IEM_ACCOUNT_STATUS'
830 and a.active_flag='Y' and a.deleted_flag='N'
831 order by a.email_address desc;
832
833 cursor c_all_results is
834
835 select a.email_account_id, a.email_address, a.active_flag, fl.meaning as account_status,
836 b.last_update_date as processor_status, b.last_update_date as last_run_time,
837 b.inbox_msg_count, b.processed_msg_count, b.retry_msg_count,
838 (select count(*) from iem_dp_logs where email_account_id = a.email_account_id) as log
839 from iem_mstemail_accounts a, iem_dp_acct_status b,fnd_lookups fl
840 where a.email_account_id = b.email_account_id(+) and a.deleted_flag='N'
841 and a.active_flag<>'M'
842 and a.active_flag=fl.lookup_code and fl.lookup_type='IEM_ACCOUNT_STATUS'
843 order by a.email_address desc;
844
845 IEM_ERROR_GET_DP_STATUS EXCEPTION;
846 BEGIN
847 -- Standard Start of API savepoint
848 SAVEPOINT GET_ACCOUNT_DP_STATUS;
849
850 -- Standard call to check for call compatibility.
851 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
852 p_api_version_number,
853 l_api_name,
854 G_PKG_NAME)
855 THEN
856 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
857 END IF;
858
859 -- Initialize message list if p_init_msg_list is set to TRUE.
860 IF FND_API.to_Boolean( p_init_msg_list )
861 THEN
862 FND_MSG_PUB.initialize;
863 END IF;
864
865 -- Initialize API return status to SUCCESS
866 x_return_status := FND_API.G_RET_STS_SUCCESS;
867
868
869 IEM_DP_MONITORING_PVT.get_dp_running_status(
870 p_api_version_number =>P_Api_Version_Number,
871 p_init_msg_list => FND_API.G_FALSE,
872 p_commit => 'F',
873 p_mode => 'N',
874 x_DP_STATUS => l_dp_status,
875 x_return_status =>l_return_status,
876 x_msg_count => l_msg_count,
877 x_msg_data => l_msg_data);
878
879 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
880 raise IEM_ERROR_GET_DP_STATUS;
881 end if;
882
883 i:= 1;
884
885 --if ( l_dp_status = 'Active' ) then
886 l_pre_sleep := iem_dp_monitoring_pvt.get_parameter('DOWNLOAD PROCESSOR', 'PREPROC_SLEEP_DURATION');
887
888
889 if ( P_view_all_accounts = 'ONLY_ACTIVE_ACCOUNTS' ) then
890
891 For v_res in c_results() loop
892 l_account_ids.extend(1);
893 l_email_address.extend(1);
894 l_account_status.extend(1);
895 l_processor_status.extend(1);
896 l_last_run_time.extend(1);
897 l_inbox_msg_count.extend(1);
898 l_process_msg_count.extend(1);
899 l_retry_msg_count.extend(1);
900 l_log.extend(1);
901
902 l_account_ids(i) := v_res.email_account_id;
903 l_email_address(i) := v_res.email_address;
904 l_account_status(i) := v_res.account_status;
905
906
907 if ( l_dp_status = 'Active' ) then
908
909 l_proc_status := v_res.last_run_time;
910
911 if ( l_proc_status is null ) then
912 l_processor_status(i) := 'notstartedind_active.gif';
913 elsif ( l_proc_status < sysdate-1/(24*12)-l_pre_sleep*(1/(24*60*60*1000))) then
914 select count(*) into l_count from iem_emta_config_params
915 where email_account_id=v_res.email_account_id
916 and Account_update_flag='N'
917 and action_type='active';
918 if ( l_count > 0 ) then
919 l_processor_status(i) := 'notstartedind_active.gif';
920 else
921 l_processor_status(i) := 'criticalind_status.gif';
922 end if;
923 else
924 l_processor_status(i) := 'okind_status.gif';
925 end if;
926 else
927 l_processor_status(i) := 'notstartedind_active.gif';
928 end if;
929
930 l_last_run_time(i) := v_res.last_run_time;
931 l_inbox_msg_count(i) := v_res.inbox_msg_count;
932 l_process_msg_count(i) := v_res.processed_msg_count;
933 l_retry_msg_count(i) := v_res.retry_msg_count;
934
935 select count(*) into l_count_error from IEM_DP_LOGS where email_account_id=v_res.email_account_id;
936
937 if l_count_error > 0 then
938 l_log(i) := 'logDetailEnabled'; --'viewwebsites_enabled.gif';
939 else
940 l_log(i) := 'logDetailDisabled'; --'viewwebsite_disabled.gif';
941 end if;
942
943 i := i+1;
944 end loop;
945
946 else --"ALL_ACCOUNT"
947 For v_res in c_all_results() loop
948 l_account_ids.extend(1);
949 l_email_address.extend(1);
950 l_account_status.extend(1);
951 l_processor_status.extend(1);
952 l_last_run_time.extend(1);
953 l_inbox_msg_count.extend(1);
954 l_process_msg_count.extend(1);
955 l_retry_msg_count.extend(1);
956 l_log.extend(1);
957
958 l_account_ids(i) := v_res.email_account_id;
959 l_email_address(i) := v_res.email_address;
960 --l_proc_status := v_res.account_status;
961 l_account_status(i) := v_res.account_status;
962
963 l_proc_status := v_res.last_run_time;
964
965 if ( l_dp_status = 'Active' ) then
966
967 if (v_res.active_flag ='N') then
968
969 l_processor_status(i) := 'notapplicableind_status.gif';
970 else
971 if ( l_proc_status is null ) then
972 l_processor_status(i) := 'notstartedind_active.gif';
973 elsif ( l_proc_status < sysdate-1/(24*12)-l_pre_sleep*(1/(24*60*60*1000))) then
974 select count(*) into l_count from iem_emta_config_params
975 where email_account_id=v_res.email_account_id
976 and Account_update_flag='N'
977 and action_type='active';
978 if ( l_count > 0 ) then
979 l_processor_status(i) := 'notstartedind_active.gif';
980 else
981 l_processor_status(i) := 'criticalind_status.gif';
982 end if;
983 --l_processor_status(i) := 'criticalind_status.gif';
984 else
985 l_processor_status(i) := 'okind_status.gif';
986 end if;
987
988 end if;
989 else
990 l_processor_status(i) := 'notstartedind_active.gif';
991 end if;
992
993 l_last_run_time(i) := v_res.last_run_time;
994 l_inbox_msg_count(i) := v_res.inbox_msg_count;
995 l_process_msg_count(i) := v_res.processed_msg_count;
996 l_retry_msg_count(i) := v_res.retry_msg_count;
997
998 select count(*) into l_count_error from IEM_DP_LOGS where email_account_id=v_res.email_account_id;
999
1000 if l_count_error > 0 then
1001 l_log(i) := 'logDetailEnabled'; --'viewwebsites_enabled.gif';
1002 else
1003 l_log(i) := 'logDetailDisabled'; --'viewwebsite_disabled.gif';
1004 end if;
1005
1006 i := i+1;
1007 end loop;
1008 end if;
1009
1010
1011 x_account_ids := l_account_ids;
1012 x_email_address := l_email_address;
1013 x_account_status := l_account_status;
1014 x_processor_status := l_processor_status;
1015 x_last_run_time := l_last_run_time;
1016 x_inbox_msg_count := l_inbox_msg_count;
1017 x_process_msg_count:= l_process_msg_count;
1018 x_retry_msg_count := l_retry_msg_count;
1019 x_log := l_log;
1020
1021 -- Standard Check Of p_commit.
1022 IF FND_API.To_Boolean(p_commit) THEN
1023 COMMIT WORK;
1024 END IF;
1025
1026 -- Standard callto get message count and if count is 1, get message info.
1027 FND_MSG_PUB.Count_And_Get
1028 ( p_count => x_msg_count,
1029 p_data => x_msg_data
1030 );
1031 EXCEPTION
1032
1033 WHEN IEM_ERROR_GET_DP_STATUS THEN
1034 ROLLBACK TO GET_ACCOUNT_DP_STATUS;
1035 x_return_status := FND_API.G_RET_STS_ERROR;
1036 FND_MSG_PUB.Count_And_Get
1037 ( p_count => x_msg_count,
1038 p_data => x_msg_data
1039 );
1040
1041 WHEN FND_API.G_EXC_ERROR THEN
1042
1043 ROLLBACK TO GET_ACCOUNT_DP_STATUS;
1044 x_return_status := FND_API.G_RET_STS_ERROR ;
1045 FND_MSG_PUB.Count_And_Get
1046 ( p_count => x_msg_count,
1047 p_data => x_msg_data
1048 );
1049 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1050
1051 ROLLBACK TO GET_ACCOUNT_DP_STATUS;
1052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1053 FND_MSG_PUB.Count_And_Get
1054 ( p_count => x_msg_count,
1055 p_data => x_msg_data
1056 );
1057 WHEN OTHERS THEN
1058
1059 ROLLBACK TO GET_ACCOUNT_DP_STATUS;
1060 x_return_status := FND_API.G_RET_STS_ERROR;
1061 IF FND_MSG_PUB.Check_Msg_Level
1062 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1063 THEN
1064 FND_MSG_PUB.Add_Exc_Msg
1065 ( G_PKG_NAME ,
1066 l_api_name
1067 );
1068 END IF;
1069 FND_MSG_PUB.Count_And_Get
1070 ( p_count => x_msg_count ,
1071 p_data => x_msg_data
1072 );
1073
1074 END GET_ACCOUNT_DP_STATUS;
1075
1076 FUNCTION get_parameter ( p_type in varchar2,
1077 p_param in varchar2 )
1078 return number
1079 is
1080 l_result number := 60000;
1081 l_value varchar2(15);
1082 BEGIN
1083
1084 select value into l_value from iem_comp_rt_stats where type=p_type and param=p_param;
1085
1086 if ( l_value is not null ) then
1087 l_result := to_number(l_value);
1088 end if;
1089
1090 return l_result;
1091
1092 EXCEPTION
1093 when others then
1094 return l_result;
1095 END;
1096
1097
1098 END IEM_DP_MONITORING_PVT;