1 PACKAGE BODY IEC_STATUS_PVT AS
2 /* $Header: IECOCSTB.pls 120.1 2006/03/28 07:57:56 minwang noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'IEC_STATUS_PVT';
5
6 g_error_msg VARCHAR2(2048) := NULL;
7
8 G_LIST_STATUS_EXECUTING CONSTANT NUMBER := 310;
9 G_LIST_STATUS_LOCKED CONSTANT NUMBER := 304;
10
11 G_NUM_MINUTES_IN_DAY CONSTANT NUMBER := 1440;
12 G_FUNCTIONAL CONSTANT NUMBER := 2;
13 G_PERFORMANCE CONSTANT NUMBER := 1;
14 G_DEFAULT_SUBSET_NAME CONSTANT VARCHAR2(30) := 'IEC_DEFAULT_SUBSET_NAME';
15 TYPE KEY_LIST IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
16
17 PROCEDURE Log
18 ( p_method IN VARCHAR2
19 , p_sub_method IN VARCHAR2
20 , p_sqlerrm IN VARCHAR2)
21 IS
22 BEGIN
23
24 IEC_OCS_LOG_PVT.LOG_INTERNAL_PLSQL_ERROR
25 ( 'IEC_STATUS_PVT'
26 , p_method
27 , p_sub_method
28 , p_sqlerrm
29 , g_error_msg
30 );
31
32 END Log;
33
34 PROCEDURE Log_IecStatusError
35 ( p_method IN VARCHAR2
36 , p_sub_method IN VARCHAR2
37 , p_list_id IN NUMBER
38 , p_status_id IN NUMBER
39 )
40 IS
41 BEGIN
42
43 IEC_OCS_LOG_PVT.LOG_LIST_STATUS_IEC_ERROR
44 ( 'IEC_STATUS_PVT'
45 , p_method
46 , p_sub_method
47 , p_list_id
48 , p_status_id
49 , g_error_msg
50 );
51
52 END Log_IecStatusError;
53
54 PROCEDURE Log_AmsStatusError
55 ( p_method IN VARCHAR2
56 , p_sub_method IN VARCHAR2
57 , p_list_id IN NUMBER
58 , p_status_id IN NUMBER
59 )
60 IS
61 BEGIN
62
63 IEC_OCS_LOG_PVT.LOG_LIST_STATUS_AMS_ERROR
64 ( 'IEC_STATUS_PVT'
65 , p_method
66 , p_sub_method
67 , p_list_id
68 , p_status_id
69 , g_error_msg
70 );
71
72 END Log_AmsStatusError;
73
74 PROCEDURE Log_CannotStopSchedule
75 ( p_method IN VARCHAR2
76 , p_sub_method IN VARCHAR2
77 , p_schedule_name IN VARCHAR2
78 )
79 IS
80 l_message VARCHAR2(4000);
81 l_encoded_message VARCHAR2(4000);
82 l_module VARCHAR2(4000);
83 BEGIN
84
85 IEC_OCS_LOG_PVT.Init_CannotStopScheduleMsg
86 ( p_schedule_name
87 , l_message
88 , l_encoded_message
89 );
90
91 IEC_OCS_LOG_PVT.Get_Module('IEC_STATUS_PVT', p_method, p_sub_method, l_module);
92 IEC_OCS_LOG_PVT.Log_Message(l_module);
93
94 END Log_CannotStopSchedule;
95
96 -----------------------------++++++-------------------------------
97 --
98 -- Start of comments
99 --
100 -- API name : Update_Schedule_Status
101 -- Type : Public
102 -- Pre-reqs : None
103 -- Function : Makes call to AMS_LISTHEADER_PUB.UpdateListheader to change the value of the
104 -- particular list's status value.
105 --
106 -- Parameters : p_schedule_id IN NUMBER Required
107 -- p_status IN NUMBER Required
108 --
109 -- Version : Initial version 1.0
110 --
111 -- End of comments
112 --
113 -----------------------------++++++-------------------------------
114 PROCEDURE Update_Schedule_Status
115 ( p_schedule_id IN NUMBER
116 , p_status IN NUMBER
117 , p_user_id IN NUMBER
118 )
119 IS
120 PRAGMA AUTONOMOUS_TRANSACTION;
121 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SCHEDULE_STATUS';
122 l_schedule_rec AMS_CAMP_SCHEDULE_PUB.schedule_rec_type;
123 l_api_version CONSTANT NUMBER := 1.0;
124 l_msg_count NUMBER;
125 l_msg_data VARCHAR2(4000);
126 l_return_code VARCHAR2(1);
127
128 l_object_version_number NUMBER;
129 BEGIN
130
131 ----------------------------------------------------------------
132 -- We modify the header id and user status id fields to indicate
133 -- that the status is what we want to modify on this list.
134 ----------------------------------------------------------------
135 l_schedule_rec.schedule_id := p_schedule_id;
136 l_schedule_rec.user_status_id := p_status;
137 l_schedule_rec.status_date := sysdate;
138 l_schedule_rec.last_update_date := sysdate;
139 l_schedule_rec.last_updated_by := p_user_id;
140
141 -- Get object version number
142 SELECT OBJECT_VERSION_NUMBER
143 INTO l_schedule_rec.object_version_number
144 FROM AMS_CAMPAIGN_SCHEDULES_B
145 WHERE SCHEDULE_ID = p_schedule_id;
146
147 ----------------------------------------------------------------
148 -- Call the AMS api to execute the schedule modification for
149 -- us.
150 ----------------------------------------------------------------
151 AMS_CAMP_SCHEDULE_PUB.Update_Camp_Schedule
152 ( p_api_version_number => l_api_version,
153 p_init_msg_list => FND_API.G_TRUE,
154 p_commit => FND_API.G_FALSE,
155 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
156 x_return_status => l_return_code,
157 x_msg_count => l_msg_count,
158 x_msg_data => l_msg_data ,
159 p_schedule_rec => l_schedule_rec,
160 x_object_version_number => l_object_version_number
161 );
162 ----------------------------------------------------------------
163 -- If the call to the ams api did not complete successfully then write
164 -- a log and stop the update list procedure.
165 ----------------------------------------------------------------
166 IF l_return_code <> FND_API.G_RET_STS_SUCCESS THEN
167
168 Log_AmsStatusError('UPDATE_SCHEDULE_STATUS', 'UPDATE_SCHEDULE', p_schedule_id, p_status);
169
170 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
171 RAISE FND_API.G_EXC_ERROR;
172 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
174 END IF;
175
176 END IF;
177
178 COMMIT;
179
180 EXCEPTION
181 ----------------------------------------------------------------
182 -- If either of the two FND_API exceptions have been thrown then
183 -- the procedure has already logged the error and we now just
184 -- set the return status flag and return to the calling
185 -- procedure.
186 ----------------------------------------------------------------
187 WHEN FND_API.G_EXC_ERROR THEN
188 ROLLBACK;
189 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
191 ROLLBACK;
192 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
193 ----------------------------------------------------------------
194 -- If an anonymous exception has been thrown then
195 -- the we must log an internal PLSQL error and
196 -- set the return status flag and return to the calling
197 -- procedure.
198 ----------------------------------------------------------------
199 WHEN OTHERS THEN
200 LOG ( l_api_name
201 , 'MAIN'
202 , SQLERRM );
203 ROLLBACK;
204 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
205
206 END Update_Schedule_Status;
207
208 -----------------------------++++++-------------------------------
209 --
210 -- Start of comments
211 --
212 -- API name : Update_List_Status
213 -- Type : Public
214 -- Pre-reqs : None
215 -- Function : Updates the Advanced Outbound list status, and
216 -- makes call to AMS_LISTHEADER_PVT.UpdateListheader
217 -- to update the Marketing list status as well.
218 -- Accepts a parameter p_api_init_flag that is used
219 -- to flag whether or not a call to a public api
220 -- initiated the status change (i.e. start purge).
221 -- In most cases, this flag isn't relevant and the
222 -- overloaded procedure Update_List_Status that accepts
223 -- only p_list_id and p_status parameters should be used.
224 --
225 -- Parameters : p_list_id IN NUMBER Required
226 -- p_status IN VARCHAR2 Required
227 -- p_api_init_flag IN VARCHAR2 Required
228 --
229 -- Version : Initial version 1.0
230 --
231 -- End of comments
232 --
233 -----------------------------++++++-------------------------------
234 PROCEDURE Update_List_Status
235 ( p_list_id IN NUMBER
236 , p_status IN VARCHAR2
237 , p_api_init_flag IN VARCHAR2
238 )
239 IS
240 PRAGMA AUTONOMOUS_TRANSACTION;
241 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LIST_STATUS';
242 l_list_rec AMS_LISTHEADER_PVT.list_header_rec_type;
243 l_api_version CONSTANT NUMBER := 1.0;
244 l_msg_count NUMBER;
245 l_msg_data VARCHAR2(4000);
246 l_return_code VARCHAR2(1);
247
248 l_mkt_status NUMBER;
249 l_curr_mkt_status NUMBER;
250
251 l_api_init_flag VARCHAR2(1);
252 BEGIN
253
254 BEGIN
255
256 ----------------------------------------------------------------
257 -- In order to set a list associated with an AO schedule to
258 -- ACTIVE, we must set the execution start time.
259 ----------------------------------------------------------------
260 IF (p_status = 'ACTIVE') THEN
261
262 l_mkt_status := G_LIST_STATUS_EXECUTING;
263
264 -- Set Execution Start Time
265 BEGIN
266 UPDATE IEC_G_LIST_RT_INFO
267 SET EXECUTION_START_TIME = SYSDATE
268 , LAST_UPDATED_BY = NVL(FND_GLOBAL.conc_login_id, -1)
269 , LAST_UPDATE_DATE = SYSDATE
270 WHERE LIST_HEADER_ID = p_list_id
271 AND EXECUTION_START_TIME IS NULL;
272 EXCEPTION
273 WHEN OTHERS THEN
274 Log ( l_api_name
275 , 'PRE_PROCESSING.UPDATE_EXECUTION_START_TIME'
276 , SQLERRM );
277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
278 END;
279
280 ELSIF (p_status = 'PENDING_VALIDATION') THEN
281
282 l_mkt_status := G_LIST_STATUS_EXECUTING;
283
284 ELSIF (p_status = 'FAILED_VALIDATION') THEN
285
286 l_mkt_status := G_LIST_STATUS_LOCKED;
287
288 ELSIF (p_status = 'VALIDATING') THEN
289
290 l_mkt_status := G_LIST_STATUS_EXECUTING;
291
292 ELSIF (p_status = 'VALIDATED') THEN
293
294 l_mkt_status := G_LIST_STATUS_LOCKED;
295
296 ELSIF (p_status = 'STOPPING') THEN
297
298 l_mkt_status := G_LIST_STATUS_EXECUTING;
299
300 ELSIF (p_status = 'INACTIVE') THEN
301
302 l_mkt_status := G_LIST_STATUS_LOCKED;
303
304 ELSIF (p_status = 'PENDING_PURGE') THEN
305
306 l_mkt_status := G_LIST_STATUS_EXECUTING;
307
308 ELSIF (p_status = 'FAILED_PURGE') THEN
309
310 l_mkt_status := G_LIST_STATUS_LOCKED;
311
312 ELSIF (p_status = 'PURGING') THEN
313
314 l_mkt_status := G_LIST_STATUS_EXECUTING;
315
316 ELSIF (p_status = 'PURGED') THEN
317
318 l_mkt_status := G_LIST_STATUS_LOCKED;
319
320 END IF;
321
322 EXCEPTION
323 ----------------------------------------------------------------
324 -- This has already been logged so just re-raise the exception.
325 ----------------------------------------------------------------
326 WHEN FND_API.G_EXC_ERROR THEN
327 RAISE;
328 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
329 RAISE;
330 ----------------------------------------------------------------
331 -- If the preprocessing throws an unexpected exception then write
332 -- a log and stop the update list procedure.
333 ----------------------------------------------------------------
334 WHEN OTHERS THEN
335 Log_IecStatusError(l_api_name, 'PRE_PROCESSING', p_list_id, p_status);
336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
337 END;
338
339 -- We only want to use the flag to indicate when an api
340 -- initiated the call to change the status (or perform an
341 -- action). If not initiated by an api, then should be NULL,
342 -- rather than 'N'
343 IF p_api_init_flag = 'Y' THEN
344 l_api_init_flag := 'Y';
345 ELSE
346 l_api_init_flag := NULL;
347 END IF;
348
349 -- Update AO List Status
350 BEGIN
351 UPDATE IEC_G_LIST_RT_INFO
352 SET STATUS_CODE = p_status
353 , API_INITIATED_FLAG = l_api_init_flag
354 , LAST_UPDATED_BY = NVL(FND_GLOBAL.conc_login_id, -1)
355 , LAST_UPDATE_DATE = SYSDATE
356 WHERE LIST_HEADER_ID = p_list_id;
357 EXCEPTION
358 WHEN OTHERS THEN
359 Log ( l_api_name
360 , 'UPDATE_AO_STATUS'
361 , SQLERRM );
362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
363 END;
364
365 -- Get current Marketing list status
366 BEGIN
367 SELECT USER_STATUS_ID
368 INTO l_curr_mkt_status
369 FROM AMS_LIST_HEADERS_ALL
370 WHERE LIST_HEADER_ID = p_list_id;
371 EXCEPTION
372 WHEN OTHERS THEN
373 Log ( l_api_name
374 , 'GET_AMS_STATUS'
375 , SQLERRM );
376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
377 END;
378
379 -- Update Marketing list status has changed
380 IF l_curr_mkt_status <> l_mkt_status THEN
381
382 ----------------------------------------------------------------
383 -- We need to grab an empty list header rec from marketing.
384 ----------------------------------------------------------------
385 AMS_LISTHEADER_PVT.Init_ListHeader_rec(x_listheader_rec => l_list_rec);
386
387 ----------------------------------------------------------------
388 -- We modify the header id and user status id fields to indicate
389 -- that the status is what we want to modify on this list.
390 ----------------------------------------------------------------
391 l_list_rec.list_header_id := p_list_id;
392 l_list_rec.user_status_id := l_mkt_status;
393
394 ----------------------------------------------------------------
395 -- Call the AMS api to execute the list header modification for
396 -- us. FUTURE: we might have to use their public api in the
397 -- future.
398 ----------------------------------------------------------------
399 AMS_LISTHEADER_PVT.Update_ListHeader
400 ( p_api_version => l_api_version,
401 p_init_msg_list => FND_API.G_TRUE,
402 p_commit => FND_API.G_FALSE,
403 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
404 x_return_status => l_return_code,
405 x_msg_count => l_msg_count,
406 x_msg_data => l_msg_data ,
407 p_listheader_rec => l_list_rec
408 );
409
410 ----------------------------------------------------------------
411 -- If the call to the ams api did not complete successfully then write
412 -- a log and stop the update list procedure.
413 ----------------------------------------------------------------
414 IF l_return_code <> FND_API.G_RET_STS_SUCCESS THEN
415
416 Log ( l_api_name
417 , 'UPDATE_AMS_LIST_STATUS'
418 , l_msg_data );
419
420 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
421 RAISE FND_API.G_EXC_ERROR;
422 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424 END IF;
425
426 END IF;
427 END IF;
428
429 COMMIT;
430
431 EXCEPTION
432
433 ----------------------------------------------------------------
434 -- If either of the two FND_API exceptions have been thrown then
435 -- the procedure has already logged the error and we now just
436 -- set the return status flag and return to the calling
437 -- procedure.
438 ----------------------------------------------------------------
439 WHEN FND_API.G_EXC_ERROR THEN
440 ROLLBACK;
441 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
442 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
443 ROLLBACK;
444 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
445
446 ----------------------------------------------------------------
447 -- If an anonymous exception has been thrown then
448 -- the we must log an internal PLSQL error and
449 -- set the return status flag and return to the calling
450 -- procedure.
451 ----------------------------------------------------------------
452 WHEN OTHERS THEN
453 LOG ( l_api_name
454 , 'MAIN'
455 , SQLERRM );
456 ROLLBACK;
457 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
458
459 END Update_List_Status;
460
461 -----------------------------++++++-------------------------------
462 --
463 -- Start of comments
464 --
465 -- API name : Update_List_Status
466 -- Type : Public
467 -- Pre-reqs : None
468 -- Function : Updates the Advanced Outbound list status, and
469 -- makes call to AMS_LISTHEADER_PVT.UpdateListheader
470 -- to update the Marketing list status as well.
471 --
472 -- Parameters : p_list_id IN NUMBER Required
473 -- p_status IN VARCHAR2 Required
474 --
475 -- Version : Initial version 1.0
476 --
477 -- End of comments
478 --
479 -----------------------------++++++-------------------------------
480 PROCEDURE Update_List_Status
481 ( p_list_id IN NUMBER
482 , p_status IN VARCHAR2
483 )
484 IS
485 BEGIN
486 Update_List_Status(p_list_id, p_status, 'N');
487 END Update_List_Status;
488
489 -----------------------------++++++-------------------------------
490 --
491 -- Start of comments
492 --
493 -- API name : Stop_Lists
494 -- Type : Public
495 -- Pre-reqs : None
496 -- Function : Stops executing on lists that currently have a status
497 -- code of STOPPING.
498 --
499 -- Parameters :
500 --
501 -- Version : Initial version 1.0
502 --
503 -- End of comments
504 --
505 -----------------------------++++++-------------------------------
506 PROCEDURE Stop_Lists
507 IS
508 l_method_name CONSTANT VARCHAR2(30) := 'STOP_LISTS';
509 l_error_stack VARCHAR2(4000);
510 BEGIN
511
512 SAVEPOINT STOP_LISTS_START;
513
514 FOR list_rec IN (SELECT LIST_HEADER_ID
515 FROM IEC_O_LISTS_TO_STOP_V)
516 LOOP
517
518 BEGIN
519 Stop_ListExecution(list_rec.LIST_HEADER_ID);
520 EXCEPTION
521 WHEN OTHERS THEN
522 IF l_error_stack IS NOT NULL THEN
523 l_error_stack := l_error_stack || ':' || g_error_msg;
524 ELSE
525 l_error_stack := g_error_msg;
526 END IF;
527 END;
528
529 END LOOP;
530
531 IF l_error_stack IS NOT NULL THEN
532 g_error_msg := l_error_stack;
533 RAISE FND_API.G_EXC_ERROR;
534 END IF;
535
536 EXCEPTION
537 WHEN FND_API.G_EXC_ERROR THEN
538 RAISE;
539 WHEN OTHERS THEN
540 LOG ( l_method_name
541 , 'MAIN'
542 , SQLERRM );
543 ROLLBACK TO STOP_LISTS_START;
544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
545 END Stop_Lists;
546
547 -----------------------------++++++-------------------------------
548 --
549 -- Start of comments
550 --
551 -- API name : Clean_ListEntries
552 -- Type : Public
553 -- Pre-reqs : None
554 -- Function : Set the list status to 'DELETED' and make sure
555 -- that all unecessary database entries related to the list
556 -- are delted.
557 --
558 -- Parameters : p_list_id IN NUMBER
559 --
560 -- Version : Initial version 1.0
561 --
562 -- End of comments
563 --
564 -----------------------------++++++-------------------------------
565 PROCEDURE Clean_ListEntries (p_list_id IN NUMBER)
566 IS
567 l_method_name CONSTANT VARCHAR2(30) := 'Clean_ListEntries';
568 l_stopped_server_id_col SYSTEM.number_tbl_type;
569 l_records_out NUMBER;
570 l_user_status_id NUMBER;
571 BEGIN
572
573 -- REMOVE ENTRIES FROM THE CALL HISTORIES TABLE FOR THIS TARGET GROUP.
574 BEGIN
575 EXECUTE IMMEDIATE 'DELETE FROM IEC_O_RCY_CALL_HISTORIES WHERE RETURNS_ID IN ' ||
576 '(SELECT RETURNS_ID FROM IEC_G_RETURN_ENTRIES WHERE LIST_HEADER_ID = :1)'
577 USING p_list_id;
578 EXCEPTION
579 WHEN OTHERS THEN
580 LOG ( l_method_name
581 , 'DELETE_CALL_HISTORY.LIST_' || p_list_id
582 , SQLERRM );
583 RAISE;
584 END;
585
586 -- REMOVE POSSIBLE ENTRIES FROM ONE OF THE SUBSET TRANSITION TABLES FOR THIS TARGET GROUP.
587 BEGIN
588 EXECUTE IMMEDIATE 'DELETE FROM IEC_O_TRANSITION_PHONES WHERE LIST_ID = :1'
589 USING p_list_id;
590 EXCEPTION
591 WHEN OTHERS THEN
592 LOG ( l_method_name
593 , 'DELETE_TRANSITION_PHONES.LIST_' || p_list_id
594 , SQLERRM );
595 RAISE;
596 END;
597
598 -- REMOVE POSSIBLE ENTRIES FROM ONE OF THE SUBSET TRANSITION TABLES FOR THIS TARGET GROUP.
599 BEGIN
600 EXECUTE IMMEDIATE 'DELETE FROM IEC_O_TRANSITION_SUBSETS WHERE LIST_ID = :1'
601 USING p_list_id;
602 EXCEPTION
603 WHEN OTHERS THEN
604 LOG ( l_method_name
605 , 'DELETE_TRANSITION_SUBSETS.LIST_' || p_list_id
606 , SQLERRM );
607 RAISE;
608 END;
609
610 -- REMOVE POSSIBLE ENTRIES FROM ONE OF THE SUBSET TRANSITION TABLES FOR THIS TARGET GROUP.
611 BEGIN
612 EXECUTE IMMEDIATE 'DELETE FROM IEC_O_TRANSITION_SUBSETS WHERE LIST_ID = :1'
613 USING p_list_id;
614 EXCEPTION
615 WHEN OTHERS THEN
616 LOG ( l_method_name
617 , 'DELETE_TRANSITION_SUBSETS.LIST_' || p_list_id
618 , SQLERRM );
619 RAISE;
620 END;
621
622 -- REMOVE POSSIBLE ENTRIES FROM THE SUBSET COUNTS TABLE.
623 BEGIN
624 EXECUTE IMMEDIATE 'DELETE FROM IEC_G_REP_SUBSET_COUNTS WHERE LIST_HEADER_ID = :1'
625 USING p_list_id;
626 EXCEPTION
627 WHEN OTHERS THEN
628 LOG ( l_method_name
629 , 'DELETE_SUBSET_COUNTS.LIST_' || p_list_id
630 , SQLERRM );
631 RAISE;
632 END;
633
634 -- REMOVE ENTRIES FROM THE CALL ZONE TABLE FOR THIS TARGET GROUP.
635 BEGIN
636 EXECUTE IMMEDIATE 'DELETE FROM IEC_G_MKTG_ITEM_CC_TZS WHERE LIST_HEADER_ID = :1'
637 USING p_list_id;
638 EXCEPTION
639 WHEN OTHERS THEN
640 LOG ( l_method_name
641 , 'DELETE_CALLABLE_ZONES.LIST_' || p_list_id
642 , SQLERRM );
643 RAISE;
644 END;
645
646 -- REMOVE ENTRIES FROM THE VALIDATION HISTORY TABLE FOR THIS TARGET GROUP.
647 BEGIN
648 EXECUTE IMMEDIATE 'DELETE FROM IEC_O_VALIDATION_HISTORY WHERE LIST_HEADER_ID = :1'
649 USING p_list_id;
650 EXCEPTION
651 WHEN OTHERS THEN
652 LOG ( l_method_name
653 , 'DELETE_VALIDATION_HISTORY.LIST_' || p_list_id
654 , SQLERRM );
655 RAISE;
656 END;
657
658 -- REMOVE ENTRIES FROM THE VALIDATION REPORT DETAILS TABLE FOR THIS TARGET GROUP.
659 BEGIN
660 EXECUTE IMMEDIATE 'DELETE FROM IEC_O_VALIDATION_REPORT_DETS WHERE LIST_HEADER_ID = :1'
661 USING p_list_id;
662 EXCEPTION
663 WHEN OTHERS THEN
664 LOG ( l_method_name
665 , 'DELETE_VALIDATION_REPORT_DETAILS.LIST_' || p_list_id
666 , SQLERRM );
667 RAISE;
668 END;
669
670 -- REMOVE ENTRIES FROM THE VALIDATION STATUS TABLE FOR THIS TARGET GROUP.
671 BEGIN
672 EXECUTE IMMEDIATE 'DELETE FROM IEC_O_VALIDATION_STATUS WHERE LIST_HEADER_ID = :1'
673 USING p_list_id;
674 EXCEPTION
675 WHEN OTHERS THEN
676 LOG ( l_method_name
677 , 'DELETE_VALIDATION_STATUS.LIST_' || p_list_id
678 , SQLERRM );
679 RAISE;
680 END;
681
682 -- REMOVE ENTRIES FROM THE AO ENTRIES TABLE FOR THIS TARGET GROUP.
683 BEGIN
684 EXECUTE IMMEDIATE 'DELETE FROM IEC_G_RETURN_ENTRIES WHERE LIST_HEADER_ID = :1'
685 USING p_list_id;
686 EXCEPTION
687 WHEN OTHERS THEN
688 LOG ( l_method_name
689 , 'DELETE_RETURN_ENTRIES.LIST_' || p_list_id
690 , SQLERRM );
691 RAISE;
692 END;
693
694 -- Update target group status to reflect
695 -- that the entries have been removed.
696 Update_List_Status(p_list_id, 'DELETED');
697
698 -- Commit the changes to make it final.
699 COMMIT;
700
701 EXCEPTION
702 WHEN OTHERS THEN
703 ROLLBACK;
704 LOG ( l_method_name
705 , 'MAIN.LIST_' || p_list_id
706 , SQLERRM );
707 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
708 END Clean_ListEntries;
709
710
711 -----------------------------++++++-------------------------------
712 --
713 -- Start of comments
714 --
715 -- API name : Start_ListExecution
716 -- Type : Public
717 -- Pre-reqs : None
718 -- Function : Set the list status to 'EXECUTING' and update
719 -- the execution start time.
720 --
721 -- Parameters : p_list_id IN NUMBER
722 --
723 -- Version : Initial version 1.0
724 --
725 -- End of comments
726 --
727 -----------------------------++++++-------------------------------
728 PROCEDURE Start_ListExecution (p_list_id IN NUMBER)
729 IS
730 PRAGMA AUTONOMOUS_TRANSACTION;
731 l_method_name CONSTANT VARCHAR2(30) := 'Start_ListExecution';
732 BEGIN
733
734 ----------------------------------------------------------------
735 -- We call update list status to set the status of this list to
736 -- ACTIVE. The UPDATE_LIST_STATUS procedure will handle any
737 -- addtional procedures that need to be executed for a list to
738 -- become active. This includes creating the runtime information.
739 ----------------------------------------------------------------
740 UPDATE_LIST_STATUS( p_list_id
741 , 'ACTIVE');
742 COMMIT;
743
744 EXCEPTION
745 WHEN OTHERS THEN
746 ROLLBACK;
747 LOG ( l_method_name
748 , 'MAIN.LIST_' || p_list_id
749 , SQLERRM );
750 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
751 END Start_ListExecution;
752
753 -----------------------------++++++-------------------------------
754 --
755 -- Start of comments
756 --
757 -- API name : Stop_ListExecution
758 -- Type : Public
759 -- Pre-reqs : None
760 -- Function : Set the list status to 'LOCKED' and make sure
761 -- that all entries are checked back into list.
762 --
763 -- Parameters : p_list_id IN NUMBER
764 --
765 -- Version : Initial version 1.0
766 --
767 -- End of comments
768 --
769 -----------------------------++++++-------------------------------
770 PROCEDURE Stop_ListExecution (p_list_id IN NUMBER)
771 IS
772 PRAGMA AUTONOMOUS_TRANSACTION;
773 l_method_name CONSTANT VARCHAR2(30) := 'Stop_ListExecution';
774 l_stopped_server_id_col SYSTEM.number_tbl_type;
775 l_records_out NUMBER;
776 l_user_status_id NUMBER;
777 BEGIN
778
779 -- Get list of STOPPED servers that have records checked out
780 SELECT DISTINCT B.SERVER_ID
781 BULK COLLECT INTO l_stopped_server_id_col
782 FROM IEC_G_RETURN_ENTRIES A, IEO_SVR_RT_INFO B
783 WHERE A.CHECKOUT_ACTION_ID = B.SERVER_ID
784 AND A.LIST_HEADER_ID = p_list_id
785 AND A.RECORD_OUT_FLAG = 'Y'
786 AND B.STATUS = 1;
787
788 -- Recover any records that are checked out to STOPPED servers
789 IF l_stopped_server_id_col IS NOT NULL AND l_stopped_server_id_col.COUNT > 0 THEN
790 FORALL i IN l_stopped_server_id_col.FIRST..l_stopped_server_id_col.LAST
791 UPDATE IEC_G_RETURN_ENTRIES
792 SET RECORD_OUT_FLAG = 'N'
793 , CHECKOUT_ACTION_ID = NULL
794 , LAST_UPDATE_DATE = SYSDATE
795 WHERE LIST_HEADER_ID = p_list_id
796 AND CHECKOUT_ACTION_ID = l_stopped_server_id_col(i);
797 END IF;
798
799 -- Check if all records have been checked back in
800 SELECT COUNT(*)
801 INTO l_records_out
802 FROM IEC_G_RETURN_ENTRIES
803 WHERE LIST_HEADER_ID = p_list_id
804 AND RECORD_OUT_FLAG = 'Y';
805
806 IF l_records_out = 0 THEN
807
808 -- Update status to Inactive since we can stop the list right now
809 UPDATE_LIST_STATUS( p_list_id
810 , 'INACTIVE');
811
812 ELSE
813 -- Update status to Stopping since we can't stop the list right now
814 -- since a running dial server still has entries checked out
815 UPDATE_LIST_STATUS( p_list_id
816 , 'STOPPING');
817
818 END IF;
819
820 COMMIT;
821
822 EXCEPTION
823 WHEN OTHERS THEN
824 ROLLBACK;
825 LOG ( l_method_name
826 , 'MAIN.LIST_' || p_list_id
827 , SQLERRM );
828 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
829 END Stop_ListExecution;
830
831 PROCEDURE Stop_ScheduleExecution_Pub
832 ( p_schedule_id IN NUMBER
833 , p_commit IN BOOLEAN
834 , x_return_status OUT NOCOPY VARCHAR2)
835 IS
836 l_method_name CONSTANT VARCHAR2(30) := 'Stop_ListExecution_Pub';
837
838 l_list_id NUMBER(15);
839 l_schedule_name VARCHAR2(100);
840 l_status_code VARCHAR2(30);
841
842 l_stopped_server_id_col SYSTEM.number_tbl_type;
843 l_records_out NUMBER;
844 l_user_status_id NUMBER;
845
846 BEGIN
847
848 SAVEPOINT stop_list;
849
850 x_return_status := FND_API.G_RET_STS_SUCCESS;
851
852 IEC_COMMON_UTIL_PVT.Get_ListId(p_schedule_id, l_list_id);
853
854 -- Check ao status to make sure that list is Active
855 -- It only makes sense to stop an Active schedule, but
856 -- the user may call this api with an Inactive schedule
857 EXECUTE IMMEDIATE
858 'SELECT A.STATUS_CODE
859 FROM IEC_G_AO_LISTS_V A
860 WHERE A.LIST_HEADER_ID = :list_id
861 AND LANGUAGE = USERENV(''LANG'')'
862 INTO l_status_code
863 USING l_list_id;
864
865 -- Only Active schedules may be stopped
866 IF l_status_code <> 'ACTIVE' THEN
867 RETURN;
868 END IF;
869
870 -- Update status to Stopping with api_initiated_flag = 'Y'
871 Update_List_Status(l_list_id, 'STOPPING', 'Y');
872
873 -- Get list of STOPPED servers that have records checked out
874 SELECT DISTINCT B.SERVER_ID
875 BULK COLLECT INTO l_stopped_server_id_col
876 FROM IEC_G_RETURN_ENTRIES A, IEO_SVR_RT_INFO B
877 WHERE A.CHECKOUT_ACTION_ID = B.SERVER_ID
878 AND A.LIST_HEADER_ID = l_list_id
879 AND A.RECORD_OUT_FLAG = 'Y'
880 AND B.STATUS = 1;
881
882 -- Recover any records that are checked out to STOPPED servers
883 IF l_stopped_server_id_col IS NOT NULL AND l_stopped_server_id_col.COUNT > 0 THEN
884 FORALL i IN l_stopped_server_id_col.FIRST..l_stopped_server_id_col.LAST
885 UPDATE IEC_G_RETURN_ENTRIES
886 SET RECORD_OUT_FLAG = 'N'
887 , CHECKOUT_ACTION_ID = NULL
888 , LAST_UPDATE_DATE = SYSDATE
889 WHERE LIST_HEADER_ID = l_list_id
890 AND CHECKOUT_ACTION_ID = l_stopped_server_id_col(i);
891 END IF;
892
893 -- Check if all records have been checked back in
894 SELECT COUNT(*)
895 INTO l_records_out
896 FROM IEC_G_RETURN_ENTRIES
897 WHERE LIST_HEADER_ID = l_list_id
898 AND RECORD_OUT_FLAG = 'Y';
899
900 IF l_records_out = 0 THEN
901 -- Update status to Inactive since we can stop the list right now
902 UPDATE_LIST_STATUS( l_list_id
903 , 'INACTIVE'
904 , NULL);
905
906 ELSE
907 -- List cannot be stopped b/c records are still checked out
908 -- Rather than update the status to STOPPING so that the
909 -- status plugin will continually check to see if the records
910 -- are checked back in, we will simply log a message and
911 -- set the status back to ACTIVE.
912 -- The reasoning is that if they are calling an api to stop
913 -- the list, they really can't wait for it to happen.
914 UPDATE_LIST_STATUS( l_list_id
915 , 'ACTIVE'
916 , NULL);
917
918 IEC_COMMON_UTIL_PVT.Get_ScheduleName(p_schedule_id, l_schedule_name);
919 Log_CannotStopSchedule(l_method_name, 'CANNOT_STOP_LIST', l_schedule_name);
920 RAISE fnd_api.g_exc_error;
921 END IF;
922
923 IF p_commit THEN
924 COMMIT;
925 END IF;
926
927 EXCEPTION
928 WHEN fnd_api.g_exc_error THEN
929 ROLLBACK TO stop_list;
930 FND_MSG_PUB.ADD;
931 x_return_status := FND_API.G_RET_STS_ERROR;
932 WHEN OTHERS THEN
933 ROLLBACK TO stop_list;
934 LOG ( l_method_name
935 , 'MAIN.SCHEDULE_' || p_schedule_id
936 , SQLERRM );
937 FND_MSG_PUB.ADD;
938 x_return_status := FND_API.G_RET_STS_ERROR;
939 END Stop_ScheduleExecution_Pub;
940
941 -----------------------------++++++-------------------------------
942 --
943 -- Start of comments
944 --
945 -- API name : Pause_ListExecution
946 -- Type : Public
947 -- Pre-reqs : None
948 -- Function : Set the schedule status to 'ON_HOLD'.
949 --
950 -- Parameters : p_schedule_id IN NUMBER
951 --
952 -- Version : Initial version 1.0
953 --
954 -- End of comments
955 --
956 -----------------------------++++++-------------------------------
957 PROCEDURE Pause_ScheduleExecution
958 ( p_schedule_id IN NUMBER
959 , p_user_id IN NUMBER
960 )
961 IS
962 PRAGMA AUTONOMOUS_TRANSACTION;
963 l_method_name CONSTANT VARCHAR2(30) := 'Pause_ListExecution';
964 l_user_status_id NUMBER;
965 BEGIN
966
967 SELECT USER_STATUS_ID
968 INTO l_user_status_id
969 FROM AMS_USER_STATUSES_B
970 WHERE SYSTEM_STATUS_TYPE = 'AMS_CAMPAIGN_SCHEDULE_STATUS'
971 AND SYSTEM_STATUS_CODE = 'ON_HOLD'
972 AND ROWNUM = 1;
973
974 UPDATE_SCHEDULE_STATUS( p_schedule_id
975 , l_user_status_id
976 , p_user_id
977 );
978 COMMIT;
979
980 EXCEPTION
981 WHEN OTHERS THEN
982 ROLLBACK;
983 LOG ( l_method_name
984 , 'MAIN.SCHEDULE_' || p_schedule_id
985 , SQLERRM );
986 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
987 END Pause_ScheduleExecution;
988
989 -----------------------------++++++-------------------------------
990 --
991 -- Start of comments
992 --
993 -- API name : HANDLE_STATUS_TRANSITIONS
994 -- Type : Public
995 -- Pre-reqs : None
996 -- Function : Called by the Status plugin to execute status
997 -- transitions.
998 -- Parameters : P_SERVER_ID IN NUMBER Required
999 --
1000 -- Version : Initial version 1.0
1001 --
1002 -- End of comments
1003 --
1004 -----------------------------++++++-------------------------------
1005 PROCEDURE HANDLE_STATUS_TRANSITIONS
1006 ( P_SERVER_ID IN NUMBER
1007 )
1008 IS
1009 L_STATUS_CODE VARCHAR2(1);
1010 L_DEFAULT_SUBSET_NAME VARCHAR2(255);
1011 L_USER_ID NUMBER;
1012 L_LOGIN_ID NUMBER;
1013 l_log_status VARCHAR2(1);
1014 l_method_name CONSTANT VARCHAR2(30) := 'HANDLE_STATUS_TRANSITIONS';
1015
1016 BEGIN
1017 L_STATUS_CODE := 'S';
1018 L_DEFAULT_SUBSET_NAME := 'DEFAULT SUBSET';
1019 L_USER_ID := NVL(FND_GLOBAL.USER_ID, -1);
1020 L_LOGIN_ID := NVL(FND_GLOBAL.CONC_LOGIN_ID, -1);
1021
1022 SAVEPOINT STATUS_START;
1023
1024 ----------------------------------------------------------------
1025 -- Call procedure to stop lists that no longer meet the
1026 -- requirements for execution.
1027 ----------------------------------------------------------------
1028 STOP_LISTS;
1029
1030 COMMIT;
1031
1032 EXCEPTION
1033 ----------------------------------------------------------------
1034 -- If either of the two FND_API exceptions have been thrown then
1035 -- the procedure has already logged the error and we now just
1036 -- set the return status flag and return to the calling
1037 -- procedure.
1038 ----------------------------------------------------------------
1039 WHEN FND_API.G_EXC_ERROR THEN
1040 ROLLBACK TO STATUS_START;
1041 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
1042 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1043 ROLLBACK TO STATUS_START;
1044 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
1045 ----------------------------------------------------------------
1046 -- If an anonymous exception has been thrown then
1047 -- the we must log an internal PLSQL error and
1048 -- set the return status flag and return to the calling
1049 -- procedure.
1050 ----------------------------------------------------------------
1051 WHEN OTHERS THEN
1052 ROLLBACK TO STATUS_START;
1053 LOG ( l_method_name
1054 , 'MAIN'
1055 , SQLERRM );
1056 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
1057 END HANDLE_STATUS_TRANSITIONS;
1058
1059 END IEC_STATUS_PVT;
[Home] [Help]