1 PACKAGE BODY IEC_SUBSET_PVT AS
2 /* $Header: IECOCSBB.pls 115.27 2004/09/03 16:47:23 alromero noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'IEC_SUBSET_PVT';
5
6 TYPE TerritoryList IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;
7 TYPE UniqueIdList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
8
9 G_NUM_MINUTES_IN_DAY CONSTANT NUMBER := 1440;
10 G_SYSTEM_OUTCOME_CODE CONSTANT NUMBER := 37;
11 G_LOST_RESULT_CODE CONSTANT NUMBER := 11;
12 G_RELEASE_STRATEGY_DEFAULT CONSTANT VARCHAR2(30) := 'QUA';
13 G_QUANTUM_DEFAULT CONSTANT NUMBER := 100;
14 G_QUOTA_DEFAULT CONSTANT NUMBER := 100;
15 G_QUOTA_RESET_DEFAULT CONSTANT NUMBER := 60;
16 G_MIN_CACHE_ENTRIES CONSTANT NUMBER := 1000;
17 G_MAX_INIT_CACHE_ENTRIES CONSTANT NUMBER := 30000;
18 G_QUERY_CALLBACK_NUM CONSTANT NUMBER := 1000;
19 g_error_msg VARCHAR2(2048) := NULL;
20
21 -----------------------------++++++-------------------------------
22 --
23 -- Start of comments
24 --
25 -- API name : LOG
26 -- Type : Private
27 -- Pre-reqs : None
28 -- Function :
29 -- Parameters : P_ACTIVITY_DESC IN NUMBER Required
30 -- P_METHOD_NAME IN NUMBER Required
31 -- P_SQL_CODE IN NUMBER Required
32 -- P_SQL_ERRM IN VARCHAR2 Required
33 -- P_SOURCE_ID IN NUMBER Required
34 --
35 -- Version : Initial version 1.0
36 --
37 -- End of comments
38 --
39 -----------------------------++++++-------------------------------
40 PROCEDURE Log ( p_method_name IN VARCHAR2
41 , p_sub_method IN VARCHAR2
42 , p_sql_errm IN VARCHAR2)
43 IS
44 l_error_msg VARCHAR2(2048);
45 BEGIN
46
47 IEC_OCS_LOG_PVT.LOG_INTERNAL_PLSQL_ERROR
48 ( 'IEC_SUBSET_PVT'
49 , p_method_name
50 , p_sub_method
51 , p_sql_errm
52 , g_error_msg
53 );
54
55 END Log;
56
57 -- Logs a translatable message that has already been initialized
58 PROCEDURE Log
59 ( p_method_name IN VARCHAR2
60 , p_sub_method IN VARCHAR2)
61 IS
62 l_module VARCHAR2(4000);
63 BEGIN
64
65 IEC_OCS_LOG_PVT.Get_Module('IEC_SUBSET_PVT', p_method_name, p_sub_method, l_module);
66 g_error_msg := l_module || ':' || g_error_msg;
67 IEC_OCS_LOG_PVT.Log_Message(l_module);
68
69 END Log;
70
71 PROCEDURE Log_SubsetViewInvalid
72 ( p_method_name IN VARCHAR2
73 , p_sub_method IN VARCHAR2
74 , p_subset_name IN VARCHAR2
75 , p_list_name IN VARCHAR2)
76 IS
77 l_module VARCHAR2(4000);
78 l_encoded_message VARCHAR2(4000);
79 BEGIN
80
81 IEC_OCS_LOG_PVT.Init_SubsetViewInvalidMsg(p_subset_name, p_list_name, g_error_msg, l_encoded_message);
82 IEC_OCS_LOG_PVT.Get_Module('IEC_SUBSET_PVT', p_method_name, p_sub_method, l_module);
83 g_error_msg := l_module || ':' || g_error_msg;
84 IEC_OCS_LOG_PVT.Log_Message(l_module);
85
86 END Log_SubsetViewInvalid;
87
88 -----------------------------++++++-------------------------------
89 --
90 -- Start of comments
91 --
92 -- API name : TRACELOG
93 -- Type : Private
94 -- Pre-reqs : None
95 -- Function :
96 -- Parameters : P_TEXT IN NUMBER Required
97 --
98 -- Version : Initial version 1.0
99 --
100 -- End of comments
101 --
102 -----------------------------++++++-------------------------------
103 PROCEDURE TRACELOG
104 (P_TEXT IN VARCHAR2)
105 IS
106 BEGIN
107 -- DBMS_OUTPUT.PUT_LINE(P_TEXT);
108 NULL;
109 END TRACELOG;
110
111 FUNCTION Get_AppsSchemaName
112 RETURN VARCHAR2
113 IS
114 l_schema_name VARCHAR2(30);
115 BEGIN
116
117 SELECT ORACLE_USERNAME
118 INTO l_schema_name
119 FROM FND_ORACLE_USERID
120 WHERE READ_ONLY_FLAG = 'U';
121
122 RETURN l_schema_name;
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 Log( 'Get_AppsSchemaName'
127 , 'MAIN'
128 , SQLERRM);
129 RAISE fnd_api.g_exc_unexpected_error;
130
131 END Get_AppsSchemaName;
132
133 -----------------------------++++++-------------------------------
134 --
135 -- Start of comments
136 --
137 -- API name : LOCK_SCHEDULE
138 -- Type : Private
139 -- Pre-reqs : None
140 -- Function : Attempt to lock or unlock the schedule.
141 --
142 -- Parameters : P_SOURCE_ID IN NUMBER Required
143 -- P_SCHED_ID IN NUMBER Required
144 -- P_SERVER_ID IN NUMBER Required
145 -- P_LOCK_FLAG IN VARCHAR2 Required
146 -- X_SUCCESS_FLAG OUT VARCHAR2 Required
147 --
148 -- Version : Initial version 1.0
149 --
150 -- End of comments
151 --
152 -----------------------------++++++-------------------------------
153 PROCEDURE LOCK_SCHEDULE
154 ( P_SOURCE_ID IN NUMBER
155 , P_SCHED_ID IN NUMBER
156 , P_SERVER_ID IN NUMBER
157 , P_LOCK_FLAG IN VARCHAR2
158 , X_SUCCESS_FLAG OUT NOCOPY VARCHAR2
159 )
160 IS
161 BEGIN
162
163 IEC_COMMON_UTIL_PVT.LOCK_SCHEDULE
164 ( P_SOURCE_ID
165 , P_SCHED_ID
166 , P_SERVER_ID
167 , P_LOCK_FLAG
168 , X_SUCCESS_FLAG
169 );
170
171 EXCEPTION
172 WHEN OTHERS THEN
173 -- FND_MESSAGE is initialized but not logged in IEC_COMMON_UTIL_PVT
174 -- if an exception is thrown, so we log it here with current
175 -- module
176 Log( 'LOCK_SCHEDULE'
177 , 'MAIN.SCHEDULE_' || P_SCHED_ID
178 );
179 RAISE fnd_api.g_exc_unexpected_error;
180
181 END LOCK_SCHEDULE;
182
183 FUNCTION Get_ListName
184 (p_list_id IN NUMBER)
185 RETURN VARCHAR2
186 IS
187 l_name VARCHAR2(240);
188 BEGIN
189
190 IEC_COMMON_UTIL_PVT.Get_ListName(p_list_id, l_name);
191
192 RETURN l_name;
193 EXCEPTION
194 WHEN OTHERS THEN
195 -- FND_MESSAGE is initialized but not logged in IEC_COMMON_UTIL_PVT
196 -- if an exception is thrown, so we log it here with current
197 -- module
198 Log('Get_ListName', 'MAIN.LIST_' || p_list_id);
199 RAISE fnd_api.g_exc_unexpected_error;
200 END Get_ListName;
201
202 FUNCTION Get_SubsetName
203 (p_subset_id IN NUMBER)
204 RETURN VARCHAR2
205 IS
206 l_name VARCHAR2(240);
207 BEGIN
208
209 IEC_COMMON_UTIL_PVT.Get_SubsetName(p_subset_id, l_name);
210
211 RETURN l_name;
212 EXCEPTION
213 WHEN OTHERS THEN
214 -- FND_MESSAGE is initialized but not logged in IEC_COMMON_UTIL_PVT
215 -- if an exception is thrown, so we log it here with current
216 -- module
217 Log('Get_ListName', 'MAIN.SUBSET_' || p_subset_id);
218 RAISE fnd_api.g_exc_unexpected_error;
219 END Get_SubsetName;
220
221
222 -----------------------------++++++-------------------------------
223 --
224 -- Start of comments
225 --
226 -- API name : GET_SOURCETYPE_VIEW_NAME
227 -- Type : Public
228 -- Pre-reqs : None
229 -- Function : Retrieve the target group's source type view name.
230 --
231 -- Parameters : P_SOURCE_ID IN NUMBER Required
232 -- P_TARGET_GROUP_ID IN VARCHAR2 Required
233 -- X_VIEW_NAME IN OUT DBMS_SQL.VARCHAR2S Required
234 --
235 -- Version : Initial version 1.0
236 --
237 -- End of comments
238 --
239 -----------------------------++++++-------------------------------
240 PROCEDURE GET_SOURCETYPE_VIEW_NAME
241 ( P_SOURCE_ID IN NUMBER
242 , P_TARGET_GROUP_ID IN VARCHAR2
243 , X_VIEW_NAME OUT NOCOPY VARCHAR2
244 )
245 IS
246 BEGIN
247
248 IEC_COMMON_UTIL_PVT.Get_SourceTypeView(p_target_group_id, x_view_name);
249
250 EXCEPTION
251 WHEN OTHERS THEN
252 -- FND_MESSAGE is initialized but not logged in Get_SourceTypeView
253 -- if an exception is thrown, so we log it here with current
254 -- module
255 Log( 'GET_SOURCETYPE_VIEW_NAME'
256 , 'MAIN.LIST_' || p_target_group_id
257 );
258 RAISE fnd_api.g_exc_unexpected_error;
259
260 END GET_SOURCETYPE_VIEW_NAME;
261
262 PROCEDURE UPDATE_SUBSET_COUNTS
263 ( p_campaign_id IN NUMBER
264 , p_schedule_id IN NUMBER
265 , p_list_id IN NUMBER
266 , p_subset_id IN NUMBER
267 , p_rec_loaded IN NUMBER
268 , p_rec_called IN NUMBER)
269 IS
270 l_rec_count NUMBER;
271 BEGIN
272
273 -- Check for existence of record for the current subset
274 EXECUTE IMMEDIATE
275 'SELECT COUNT(*)
276 FROM IEC_G_REP_SUBSET_COUNTS
277 WHERE SUBSET_ID = :subset_id'
278 INTO l_rec_count
279 USING p_subset_id;
280
281 -- If record does not exist, create record and initialize counts
282 IF l_rec_count = 0 THEN
283
284 EXECUTE IMMEDIATE
285 'INSERT INTO IEC_G_REP_SUBSET_COUNTS
286 ( SUBSET_COUNT_ID
287 , CAMPAIGN_ID
288 , SCHEDULE_ID
289 , LIST_HEADER_ID
290 , SUBSET_ID
291 , RECORD_LOADED
292 , RECORD_CALLED_ONCE
293 , RECORD_CALLED_AND_REMOVED
294 , RECORD_CALLED_AND_REMOVED_COPY
295 , LAST_COPY_TIME
296 , CREATED_BY
297 , CREATION_DATE
298 , LAST_UPDATE_LOGIN
299 , LAST_UPDATE_DATE
300 , LAST_UPDATED_BY
301 , OBJECT_VERSION_NUMBER
302 )
303 VALUES
304 (IEC_G_REP_SUBSET_COUNTS_S.NEXTVAL
305 , :campaign_id
306 , :schedule_id
307 , :list_id
308 , :subset_id
309 , :records_loaded
310 , :records_called
311 , 0
312 , 0
313 , SYSDATE
314 , 1
315 , SYSDATE
316 , 1
317 , SYSDATE
318 , 0
319 , 0)'
320 USING p_campaign_id
321 , p_schedule_id
322 , p_list_id
323 , p_subset_id
324 , p_rec_loaded
325 , p_rec_called;
326
327 ELSE
328 -- If record exists, simply update counts by appropriate increment
329 EXECUTE IMMEDIATE
330 'UPDATE IEC_G_REP_SUBSET_COUNTS
331 SET RECORD_LOADED = NVL(RECORD_LOADED, 0) + :records_loaded
332 , RECORD_CALLED_ONCE = NVL(RECORD_CALLED_ONCE, 0) + :records_called
333 , LAST_UPDATE_DATE = SYSDATE
334 WHERE SUBSET_ID = :subset_id'
335 USING p_rec_loaded
336 , p_rec_called
337 , p_subset_id;
338
339 END IF;
340
341 EXCEPTION
342 WHEN OTHERS THEN
343 Log( 'UPDATE_SUBSET_COUNTS'
344 , 'MAIN.SUBSET_' || p_subset_id
345 , SQLERRM
346 );
347 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
348 END UPDATE_SUBSET_COUNTS;
349
350 -----------------------------++++++-------------------------------
351 -- Start of comments
352 --
353 -- API name : CREATE_SUBSET_VIEW
354 -- Type : Public
355 -- Pre-reqs : None
356 -- Function : Creates a view for the specified subset using
357 -- the view name provided.
358 --
359 -- Parameters : P_SOURCE_ID IN NUMBER Required
360 -- P_SUBSET_ID IN NUMBER Required
361 -- P_VIEW_NAME IN VARCHAR2 Required
362 -- P_TARGET_GROUP_ID IN NUMBER Required
363 -- P_SOURCE_TYPE_VIEW_NAME IN VARCHAR2 Required
364 -- P_DEFAULT_SUBSET_FLAG IN VARCHAR2 Required
365 -- X_RETURN_CODE OUT VARCHAR2 Required
366 --
367 -- Version : Initial version 1.0
368 --
369 -- End of comments
370 --
371 -----------------------------++++++-------------------------------
372 PROCEDURE CREATE_SUBSET_VIEW
373 ( P_SOURCE_ID IN NUMBER
374 , P_SUBSET_ID IN NUMBER
375 , P_VIEW_NAME IN VARCHAR2
376 , P_TARGET_GROUP_ID IN NUMBER
377 , P_SOURCE_TYPE_VIEW_NAME IN VARCHAR2
378 , P_DEFAULT_SUBSET_FLAG IN VARCHAR2
379 , X_RETURN_CODE OUT NOCOPY VARCHAR2
380 )
381 IS
382
383 ----------------------------------------------------------------
384 -- A table of VARCHAR2(256) that is used to build the subset
385 -- query.
386 ----------------------------------------------------------------
387 l_create_statement DBMS_SQL.VARCHAR2S;
388
389 ----------------------------------------------------------------
390 -- The identifier for the DBMS_SQL cursor that we are going to
391 -- use.
392 ----------------------------------------------------------------
393 l_work_cursor NUMBER;
394
395 ----------------------------------------------------------------
396 -- Dummy number variable used in the execute function.
397 ----------------------------------------------------------------
398 l_dummy NUMBER;
399
400 ----------------------------------------------------------------
401 -- The first part of the subset query SQL that is unique to
402 -- each list by source view id and list id:
403 ----------------------------------------------------------------
404 l_create_start_str CONSTANT VARCHAR2(16) := 'CREATE VIEW ';
405
406 ----------------------------------------------------------------
407 -- The first part of the subset query SQL that is unique to
408 -- each list by source view id and list id:
409 ----------------------------------------------------------------
410 l_create_as_str CONSTANT VARCHAR2(100) := ' AS SELECT LIST_ENTRY_ID FROM ';
411
412 ----------------------------------------------------------------
413 -- The first part of the subset query SQL that is unique to
414 -- each list by source view id and list id:
415 ----------------------------------------------------------------
416 l_create_where_str CONSTANT VARCHAR2(32) := ' WHERE LIST_HEADER_ID = ';
417
418 ----------------------------------------------------------------
419 -- Local Status code.
420 ----------------------------------------------------------------
421 l_status_code VARCHAR2(1);
422
423 BEGIN
424 TRACELOG( 'STARTING CREATE SUBSET VIEW');
425 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
426
427 ----------------------------------------------------------------
428 -- If this is a default subset, then we need to call the get
429 -- default subset criteria procedure.
430 ----------------------------------------------------------------
431 IF NVL(P_DEFAULT_SUBSET_FLAG, 'N') = 'N'
432 THEN
433
434 l_create_statement(1) := l_create_start_str
435 || P_VIEW_NAME
436 || l_create_as_str
437 || P_SOURCE_TYPE_VIEW_NAME
438 || l_create_where_str
439 || P_TARGET_GROUP_ID
440 || ' AND ';
441
442 ----------------------------------------------------------------
443 -- Append the subset criteria clause.
444 ----------------------------------------------------------------
445 IEC_CRITERIA_UTIL_PVT.Append_SubsetCriteriaClause( p_source_id
446 , p_subset_id
447 , p_source_type_view_name
448 , l_create_statement
449 , l_status_code);
450
451 TRACELOG('Number of subset lines ' || l_Create_statement.COUNT);
452
453 FOR T IN l_create_statement.FIRST..l_create_statement.LAST
454 LOOP
455 TRACELOG(l_create_statement(T));
456 END LOOP;
457
458 l_work_cursor := DBMS_SQL.OPEN_CURSOR;
459
460 DBMS_SQL.PARSE( c => l_work_cursor
461 , statement => l_create_statement
462 , lb => 1
463 , ub => l_create_statement.COUNT
464 , lfflg => FALSE
465 , language_flag => DBMS_SQL.NATIVE);
466
467
468 l_dummy := DBMS_SQL.EXECUTE(l_work_cursor);
469
470 DBMS_SQL.CLOSE_CURSOR(l_work_cursor);
471
472 ----------------------------------------------------------------
473 -- If this is a default subset, then we don't create a view.
474 ----------------------------------------------------------------
475 ELSE
476 NULL;
477 END IF; -- end of the if default subset conditional.
478 TRACELOG('STOP CREATE SUBSET VIEW');
479
480 EXCEPTION
481 -- Fixme add logic to handle if entry does not exist.
482 -- This should no happen.
483 WHEN FND_API.G_EXC_ERROR THEN
484 IF DBMS_SQL.IS_OPEN(l_work_cursor) THEN
485 DBMS_SQL.CLOSE_CURSOR(l_work_cursor);
486 END IF;
487 X_RETURN_CODE := 'E';
488 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
489 IF DBMS_SQL.IS_OPEN(l_work_cursor) THEN
490 DBMS_SQL.CLOSE_CURSOR(l_work_cursor);
491 END IF;
492 X_RETURN_CODE := 'U';
493 WHEN OTHERS THEN
494 TRACELOG('SQLERRM: ' || SQLERRM);
495 IF DBMS_SQL.IS_OPEN(l_work_cursor)
496 THEN
497 DBMS_SQL.CLOSE_CURSOR(l_work_cursor);
498 END IF;
499 X_RETURN_CODE := 'U';
500
501 END CREATE_SUBSET_VIEW;
502
503 -----------------------------++++++-------------------------------
504 -- Start of comments
505 --
506 -- API name : DROP_SUBSET_VIEW
507 -- Type : Public
508 -- Pre-reqs : None
509 -- Function : Drops the view defined for the specified subset.
510 --
511 -- Parameters : P_SOURCE_ID IN NUMBER Required
512 -- P_SUBSET_ID IN NUMBER Required
513 -- X_RETURN_CODE OUT VARCHAR2 Required
514 --
515 -- Version : Initial version 1.0
516 --
517 -- End of comments
518 --
519 -----------------------------++++++-------------------------------
520 PROCEDURE DROP_SUBSET_VIEW
521 ( P_SOURCE_ID IN NUMBER
522 , P_SUBSET_ID IN NUMBER
523 , X_RETURN_CODE OUT NOCOPY VARCHAR2
524 )
525 IS
526
527 ----------------------------------------------------------------
528 -- Local Status code.
529 ----------------------------------------------------------------
530 l_status_code VARCHAR2(1);
531 l_view_name VARCHAR2(30);
532 l_view_owner VARCHAR2(30);
533 l_ignore NUMBER;
534
535 BEGIN
536 TRACELOG('STARTING DROP SUBSET VIEW');
537 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
538
539 /*
540 DROP VIEW IEC_SUBSET_<id>_V
541 */
542
543 l_view_name := 'IEC_SUBSET_' || P_SUBSET_ID || '_V';
544 l_view_owner := Get_AppsSchemaName;
545
546 BEGIN
547 SELECT 1
548 INTO l_ignore
549 FROM ALL_VIEWS
550 WHERE VIEW_NAME = UPPER(l_view_name)
551 AND OWNER = UPPER(l_view_owner);
552
553 EXECUTE IMMEDIATE 'DROP VIEW ' || l_view_name;
554
555 EXCEPTION
556 WHEN NO_DATA_FOUND THEN
557 NULL;
558 WHEN OTHERS THEN
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560 END;
561
562 TRACELOG('STOP DROP SUBSET VIEW');
563
564 EXCEPTION
565 -- Fixme add logic to handle if entry does not exist.
566 -- This should no happen.
567 WHEN FND_API.G_EXC_ERROR THEN
568 X_RETURN_CODE := 'E';
569 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
570 X_RETURN_CODE := 'U';
571 WHEN OTHERS THEN
572 TRACELOG('SQLERRM: ' || SQLERRM);
573 X_RETURN_CODE := 'U';
574 END DROP_SUBSET_VIEW;
575
576 -----------------------------++++++-------------------------------
577 --
578 -- Start of comments
579 --
580 -- API name : DROP_TARGET_GROUP_VIEWS
581 -- Type : Private
582 -- Pre-reqs : None
583 -- Function : For each subset in the specified target group,
584 -- drop the subset view.
585 --
586 -- Parameters : P_SOURCE_ID IN NUMBER Required
587 -- P_TARGET_GROUP_ID IN NUMBER Required
588 -- X_RETURN_CODE OUT VARCHAR2 Required
589 --
590 -- Version : Initial version 1.0
591 --
592 -- End of comments
593 --
594 -----------------------------++++++-------------------------------
595 PROCEDURE DROP_TARGET_GROUP_VIEWS
596 ( P_SOURCE_ID IN NUMBER
597 , P_TARGET_GROUP_ID IN NUMBER
598 , X_RETURN_CODE OUT NOCOPY VARCHAR2
599 )
600 IS
601
602 ----------------------------------------------------------------
603 -- Local Status code.
604 ----------------------------------------------------------------
605 l_status_code VARCHAR2(1);
606
607 BEGIN
608 TRACELOG('STARTING DROP TARGET GROUP VIEWS');
609 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
610
611 FOR subset_rec IN (SELECT LIST_SUBSET_ID
612 FROM IEC_G_LIST_SUBSETS
613 WHERE LIST_HEADER_ID = P_TARGET_GROUP_ID
614 AND NVL(DEFAULT_SUBSET_FLAG, 'N') = 'N')
615 LOOP
616 DROP_SUBSET_VIEW( P_SOURCE_ID
617 , subset_rec.LIST_SUBSET_ID
618 , l_status_code);
619 END LOOP;
620
621 TRACELOG('STOP CREATE TARGET GROUP VIEWS');
622
623 EXCEPTION
624 -- Fixme add logic to handle if entry does not exist.
625 -- This should no happen.
626 WHEN FND_API.G_EXC_ERROR THEN
627 X_RETURN_CODE := 'E';
628 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
629 X_RETURN_CODE := 'U';
630 WHEN OTHERS THEN
631 TRACELOG('SQLERRM: ' || SQLERRM);
632 X_RETURN_CODE := 'U';
633
634 END DROP_TARGET_GROUP_VIEWS;
635
636 -----------------------------++++++-------------------------------
637 --
638 -- Start of comments
639 --
640 -- API name : VERIFY_SUBSET_VIEW
641 -- Type : Private
642 -- Pre-reqs : None
643 -- Function :
644 -- Parameters : P_SOURCE_ID IN NUMBER Required
645 -- P_SUBSET_ID IN NUMBER Required
646 -- X_VIEW_NAME OUT VARCHAR2 Required
647 -- X_VIEW_EXISTS OUT VARCHAR2 Required
648 -- X_RETURN_CODE OUT VARCHAR2 Required
649 --
650 -- Version : Initial version 1.0
651 --
652 -- End of comments
653 --
654 -----------------------------++++++-------------------------------
655 PROCEDURE VERIFY_SUBSET_VIEW
656 ( P_SOURCE_ID IN NUMBER
657 , P_SUBSET_ID IN NUMBER
658 , P_TARGET_GROUP_ID IN NUMBER
659 , X_VIEW_NAME OUT NOCOPY VARCHAR2
660 , X_VIEW_EXISTS OUT NOCOPY VARCHAR2
661 , X_RETURN_CODE OUT NOCOPY VARCHAR2
662 )
663 IS
664
665 L_STATUS VARCHAR2(10);
666 L_VIEW_NAME VARCHAR2(30);
667 l_view_owner VARCHAR2(30);
668 L_RETURN_CODE VARCHAR2(1);
669
670 BEGIN
671 TRACELOG('STARTING VERIFY SUBSET VIEW');
672 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
673 X_VIEW_NAME := NULL;
674 X_VIEW_EXISTS := 'N';
675
676 L_VIEW_NAME := 'IEC_SUBSET_' || P_SUBSET_ID || '_V';
677 l_view_owner := Get_AppsSchemaName;
678
679 BEGIN
680
681 EXECUTE IMMEDIATE ' SELECT STATUS ' ||
682 ' FROM ALL_OBJECTS ' ||
683 ' WHERE OWNER = :owner ' ||
684 ' AND OBJECT_NAME = :b1 ' ||
685 ' AND OBJECT_TYPE = ''VIEW'' '
686 INTO L_STATUS
687 USING l_view_owner
688 , L_VIEW_NAME;
689
690
691 IF (L_STATUS <> 'VALID')
692 THEN
693
694 BEGIN
695 EXECUTE IMMEDIATE 'ALTER VIEW ' || L_VIEW_NAME || ' COMPILE';
696 X_VIEW_EXISTS := 'Y';
697 EXCEPTION
698 WHEN OTHERS THEN
699 TRACELOG('VIEW <' || L_VIEW_NAME || '> IS INVALID');
700 Log_SubsetViewInvalid
701 ( 'VERIFY_SUBSET_VIEW'
702 , 'RECOMPILE_SUBSET_VIW'
703 , Get_SubsetName(p_subset_id)
704 , Get_ListName(p_target_group_id)
705 );
706 RAISE FND_API.G_EXC_ERROR;
707 END;
708
709 ELSE
710 X_VIEW_EXISTS := 'Y';
711 END IF;
712
713 X_VIEW_NAME := L_VIEW_NAME;
714
715 EXCEPTION
716 WHEN NO_DATA_FOUND THEN
717 X_VIEW_EXISTS := 'N';
718 X_VIEW_NAME := L_VIEW_NAME;
719 WHEN OTHERS THEN
720 RAISE;
721 END;
722
723 TRACELOG('END VERIFY SUBSET VIEW ' || X_VIEW_EXISTS);
724 TRACELOG('END VERIFY SUBSET VIEWNAME ' || X_VIEW_NAME);
725
726 EXCEPTION
727 WHEN FND_API.G_EXC_ERROR THEN
728 X_RETURN_CODE := 'E';
729 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
730 X_RETURN_CODE := 'U';
731 WHEN OTHERS THEN
732 TRACELOG('SQLERRM: ' || SQLERRM);
733 LOG( 'VERIFY_SUBSET_VIEW'
734 , 'MAIN.SUBSET_' || p_subset_id
735 , SQLERRM
736 );
737 X_RETURN_CODE := 'U';
738
739 END VERIFY_SUBSET_VIEW;
740
741 -----------------------------++++++-------------------------------
742 --
743 -- Start of comments
744 --
745 -- API name : CREATE_SUBSET_RT_INFO
746 -- Type : Public
747 -- Pre-reqs : None
748 -- Function : If the subset runtime information entries do not
749 -- already exist, create them.
750 --
751 -- Parameters : P_SUBSET_ID IN NUMBER Required
752 --
753 -- Version : Initial version 1.0
754 --
755 -- End of comments
756 --
757 -----------------------------++++++-------------------------------
758 PROCEDURE CREATE_SUBSET_RT_INFO
759 ( P_SUBSET_ID IN NUMBER
760 )
761 IS
762
763 L_RELEASE_STRATEGY VARCHAR2(30);
764 L_RETURN_CODE VARCHAR2(1);
765 L_STATUS_CODE VARCHAR2(100);
766 L_LOAD_PRIORITY NUMBER;
767 L_QUANTUM NUMBER;
768 L_QUOTA NUMBER;
769 L_QUOTA_RESET NUMBER;
770 L_LOGIN_USERID NUMBER;
771 L_USERID NUMBER;
772
773 BEGIN
774 L_LOGIN_USERID := NVL(FND_GLOBAL.conc_login_id, -1);
775 L_USERID := NVL(FND_GLOBAL.user_id, -1);
776
777 ----------------------------------------------------------------
778 -- Create save point for this procedure.
779 ----------------------------------------------------------------
780 SAVEPOINT CREATE_SUBSET_RT_INFO_SAVE;
781
782 ----------------------------------------------------------------
783 -- Retrieve the default data values for the release strategy
784 -- from the subset entry on the IEC_G_LIST_SUBSETS table. If
785 -- default values do not exists then we use defaults. We could
786 -- have retrieved this data in the populate cache procedure, but
787 -- a subset is only going to retrieve these once, but could be
788 -- used for a long time. That was the reason behind taking the
789 -- performance hit here.
790 ----------------------------------------------------------------
791
792 BEGIN
793 SELECT NVL(RELEASE_STRATEGY, G_RELEASE_STRATEGY_DEFAULT)
794 , NVL(QUANTUM, G_QUANTUM_DEFAULT)
795 , NVL(QUOTA, G_QUOTA_DEFAULT)
796 , NVL(QUOTA_RESET, G_QUOTA_RESET_DEFAULT)
797 , STATUS_CODE
798 , LOAD_PRIORITY
799 INTO L_RELEASE_STRATEGY
800 , L_QUANTUM
801 , L_QUOTA
802 , L_QUOTA_RESET
803 , L_STATUS_CODE
804 , L_LOAD_PRIORITY
805 FROM IEC_G_LIST_SUBSETS
806 WHERE LIST_SUBSET_ID = P_SUBSET_ID;
807
808 EXCEPTION
809 WHEN NO_DATA_FOUND THEN
810 ----------------------------------------------------------------
811 -- This shouldn't happen, if it does log an error and stop
812 -- processing for this subset. TODO
813 ----------------------------------------------------------------
814 RAISE;
815 WHEN OTHERS THEN
816 RAISE;
817 END; -- end block to query for subset default values.
818
819 ----------------------------------------------------------------
820 -- If the release strategy is quantum then we need to set the
821 -- value of the quota equal to the start value of the quantum.
822 ----------------------------------------------------------------
823 IF L_RELEASE_STRATEGY = 'QUA'
824 THEN
825 L_QUOTA := L_QUANTUM;
826 END IF;
827
828 ----------------------------------------------------------------
829 -- We calculate the next time to reset the quota be taking the
830 -- quota reset interval retrieved in the previous query,
831 -- dividing it by the number of minutes in a day (1440), and
832 -- adding this value to the current SYSDATE.
833 ----------------------------------------------------------------
834 L_QUOTA_RESET := L_QUOTA_RESET / G_NUM_MINUTES_IN_DAY;
835
836 ----------------------------------------------------------------
837 -- Create an entry in the IEC_G_SUBSET_RT_INFO table to support
838 -- the new subset.
839 ----------------------------------------------------------------
840 BEGIN
841
842 INSERT INTO IEC_G_SUBSET_RT_INFO
843 ( SUBSET_RT_INFO_ID
844 , LIST_SUBSET_ID
845 , WORKING_QUANTUM
846 , WORKING_QUOTA
847 , QUOTA_RESET_TIME
848 , CACHE_AMT_NEEDED
849 , VALID_FLAG
850 , USE_FLAG
851 , CALLABLE_FLAG
852 , TOTAL_CACHE_COUNT
853 , STATUS_CODE
854 , LOAD_PRIORITY
855 , CREATED_BY
856 , CREATION_DATE
857 , LAST_UPDATED_BY
858 , LAST_UPDATE_DATE
859 )
860 VALUES
861 ( IEC_G_SUBSET_RT_INFO_S.NEXTVAL
862 , P_SUBSET_ID
863 , L_QUANTUM
864 , L_QUOTA
865 , SYSDATE + L_QUOTA_RESET
866 , NULL
867 , 'Y'
868 , 'Y'
869 , 'Y'
870 , 0
871 , L_STATUS_CODE
872 , L_LOAD_PRIORITY
873 , L_USERID
874 , SYSDATE
875 , L_LOGIN_USERID
876 , SYSDATE
877 );
878
879 EXCEPTION
880 WHEN DUP_VAL_ON_INDEX THEN
881 EXECUTE IMMEDIATE
882 ' UPDATE IEC_G_SUBSET_RT_INFO
883 SET STATUS_CODE = :1
884 , LOAD_PRIORITY = :2
885 , LAST_UPDATE_DATE = SYSDATE
886 WHERE LIST_SUBSET_ID = :3'
887 USING L_STATUS_CODE, L_LOAD_PRIORITY, P_SUBSET_ID;
888
889 WHEN OTHERS THEN
890 RAISE;
891 END; -- end of block for inserting entry into IEC_G_SUBSET_RT_INFO table.
892
893 EXCEPTION
894 WHEN OTHERS THEN
895 ROLLBACK TO CREATE_SUBSET_RT_INFO_SAVE;
896 Log( 'CREATE_SUBSET_RT_INFO'
897 , 'MAIN.SUBSET_' || p_subset_id
898 , SQLERRM
899 );
900 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
901
902 END CREATE_SUBSET_RT_INFO;
903
904 -----------------------------++++++-------------------------------
905 --
906 -- Start of comments
907 --
908 -- API name : GET_SUBSET_VIEW
909 -- Type : Public
910 -- Pre-reqs : None
911 -- Function : Returns the subset view name after verifying that the view
912 -- exists, creating the view if necessary.
913 --
914 -- Parameters : P_SOURCE_ID IN NUMBER Required
915 -- P_TARGET_GROUP_ID IN NUMBER Required
916 -- P_SUBSET_ID IN NUMBER Required
917 -- P_DEFAULT_SUBSET_FLAG IN VARCHAR2 Required
918 -- P_SOURCE_TYPE_VIEW_NAME IN VARCHAR2 Required
919 -- X_RETURN_CODE OUT VARCHAR2 Required
920 --
921 -- Version : Initial version 1.0
922 --
923 -- End of comments
924 --
925 -----------------------------++++++-------------------------------
926 FUNCTION GET_SUBSET_VIEW
927 ( P_SOURCE_ID IN NUMBER
928 , P_TARGET_GROUP_ID IN NUMBER
929 , P_SUBSET_ID IN NUMBER
930 , P_DEFAULT_SUBSET_FLAG IN VARCHAR2
931 , P_SOURCE_TYPE_VIEW_NAME IN VARCHAR2
932 , X_RETURN_CODE OUT NOCOPY VARCHAR2
933 )
934 RETURN VARCHAR2
935 IS
936 l_subset_view_name VARCHAR2(500);
937 l_return_code VARCHAR2(1);
938 l_view_exists VARCHAR2(1);
939 l_default_subset_flag VARCHAR2(1);
940
941 BEGIN
942 l_return_code := FND_API.G_RET_STS_SUCCESS;
943 l_view_exists := 'N';
944 l_default_subset_flag := 'N';
945
946 TRACELOG('BEGIN GET SUBSET VIEW');
947 ----------------------------------------------------------------
948 -- Initialize the return code.
949 ----------------------------------------------------------------
950 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
951
952 ----------------------------------------------------------------
953 -- Create save point for this procedure.
954 ----------------------------------------------------------------
955 SAVEPOINT GET_SUBSET_VIEW_SAVE;
956
957 IF P_DEFAULT_SUBSET_FLAG = 'Y' OR P_DEFAULT_SUBSET_FLAG = 'N'
958 THEN
959 l_default_Subset_Flag := P_DEFAULT_SUBSET_FLAG;
960 ELSE
961
962 BEGIN
963 SELECT NVL(DEFAULT_SUBSET_FLAG, 'N')
964 INTO l_default_subset_Flag
965 FROM IEC_G_LIST_SUBSETS
966 WHERE LIST_HEADER_ID = P_TARGET_GROUP_ID
967 AND LIST_SUBSET_ID = P_SUBSET_ID;
968 EXCEPTION
969 WHEN OTHERS THEN
970 RAISE;
971 END;
972
973 END IF;
974
975 IF l_default_subset_flag <> 'Y' THEN
976
977 l_subset_view_name := 'IEC_SUBSET_' || P_SUBSET_ID || '_V';
978
979 TRACELOG('BEFORE VERIFY SUBSET VIEW ' || l_subset_view_name);
980 VERIFY_SUBSET_VIEW ( P_SOURCE_ID => P_SOURCE_ID
981 , P_SUBSET_ID => P_SUBSET_ID
982 , P_TARGET_GROUP_ID => P_TARGET_GROUP_ID
983 , X_VIEW_NAME => l_subset_view_name
984 , X_VIEW_EXISTS => l_view_exists
985 , X_RETURN_CODE => l_return_code
986 );
987
988 TRACELOG('AFTER VERIFY SUBSET VIEW ' || l_return_code || ' : ' || l_view_exists);
989
990 IF (l_return_code = FND_API.G_RET_STS_SUCCESS AND l_view_exists = 'N') THEN
991
992 CREATE_SUBSET_VIEW( P_SOURCE_ID => P_SOURCE_ID
993 , P_SUBSET_ID => P_SUBSET_ID
994 , P_VIEW_NAME => l_subset_view_name
995 , P_TARGET_GROUP_ID => P_TARGET_GROUP_ID
996 , P_SOURCE_TYPE_VIEW_NAME => P_SOURCE_TYPE_VIEW_NAME
997 , P_DEFAULT_SUBSET_FLAG => l_default_Subset_Flag
998 , X_RETURN_CODE => l_return_code
999 );
1000
1001 IF (l_return_code <> FND_API.G_RET_STS_SUCCESS) THEN
1002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1003 END IF;
1004
1005 ELSIF (l_return_code <> FND_API.G_RET_STS_SUCCESS) THEN
1006 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1007 END IF;
1008 ELSE
1009 l_subset_view_name := 'DEFAULT';
1010 END IF;
1011
1012 TRACELOG('END GET SUBSET VIEW ' || l_subset_view_name);
1013 RETURN l_subset_view_name;
1014
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 ROLLBACK TO GET_SUBSET_VIEW_SAVE;
1018 X_RETURN_CODE := FND_API.G_RET_STS_UNEXP_ERROR;
1019
1020 END GET_SUBSET_VIEW;
1021
1022 -----------------------------++++++-------------------------------
1023 --
1024 -- Start of comments
1025 --
1026 -- API name : RECREATE_SUBSET_VIEW
1027 -- Type : Public
1028 -- Pre-reqs : None
1029 -- Procedure : Recreates the subset view, deleting it first if necessary.
1030 --
1031 -- Parameters : P_SOURCE_ID IN NUMBER Required
1032 -- P_TARGET_GROUP_ID IN NUMBER Required
1033 -- P_SUBSET_ID IN NUMBER Required
1034 -- X_SUBSET_VIEW_NAME OUT NOCOPY VARCHAR2 Required
1035 --
1036 -- Version : Initial version 1.0
1037 --
1038 -- End of comments
1039 --
1040 -----------------------------++++++-------------------------------
1041 PROCEDURE RECREATE_SUBSET_VIEW
1042 ( P_SOURCE_ID IN NUMBER
1043 , P_TARGET_GROUP_ID IN NUMBER
1044 , P_SUBSET_ID IN NUMBER
1045 , X_SUBSET_VIEW_NAME OUT NOCOPY VARCHAR2
1046 )
1047 IS
1048 l_subset_view_name VARCHAR2(500);
1049 l_source_type_view_name VARCHAR2(30);
1050 l_view_exists VARCHAR2(1);
1051 l_return_code VARCHAR2(1);
1052
1053 BEGIN
1054 l_view_exists := 'N';
1055 ----------------------------------------------------------------
1056 -- Initialize the return code.
1057 ----------------------------------------------------------------
1058 l_return_code := FND_API.G_RET_STS_SUCCESS;
1059
1060 X_SUBSET_VIEW_NAME := 'ERROR';
1061
1062 ----------------------------------------------------------------
1063 -- Create save point for this procedure.
1064 ----------------------------------------------------------------
1065 SAVEPOINT RECREATE_SUBSET_VIEW_SAVE;
1066
1067 ----------------------------------------------------------------
1068 -- This will retrieve the view name from an IEc lookup value
1069 -- that has been seeded in the database. In the future there
1070 -- might be an algorithm used to BUILD the view name using values
1071 -- stored in the marketing schema.
1072 ----------------------------------------------------------------
1073 GET_SOURCETYPE_VIEW_NAME
1074 ( P_SOURCE_ID => P_SOURCE_ID
1075 , P_TARGET_GROUP_ID => P_TARGET_GROUP_ID
1076 , X_VIEW_NAME => l_source_type_view_name);
1077
1078
1079 l_subset_view_name := 'IEC_SUBSET_' || P_SUBSET_ID || '_V';
1080
1081 ----------------------------------------------------------------
1082 -- Check to see if the view already exists.
1083 ----------------------------------------------------------------
1084 VERIFY_SUBSET_VIEW ( P_SOURCE_ID => P_SOURCE_ID
1085 , P_SUBSET_ID => P_SUBSET_ID
1086 , P_TARGET_GROUP_ID => P_TARGET_GROUP_ID
1087 , X_VIEW_NAME => l_subset_view_name
1088 , X_VIEW_EXISTS => l_view_exists
1089 , X_RETURN_CODE => l_return_code
1090 );
1091
1092 ----------------------------------------------------------------
1093 -- If the view already exists then drop the view.
1094 ----------------------------------------------------------------
1095 IF (l_return_code = FND_API.G_RET_STS_SUCCESS AND l_view_exists = 'Y')
1096 THEN
1097 DROP_SUBSET_VIEW( P_SOURCE_ID => P_SOURCE_ID
1098 , P_SUBSET_ID => P_SUBSET_ID
1099 , X_RETURN_CODE => l_return_code);
1100
1101 ELSIF (l_return_code <> FND_API.G_RET_STS_SUCCESS)
1102 THEN
1103 Log( 'RECREATE_SUBSET_VIEW'
1104 , 'VERIFY_SUBSET_VIEW.SUBSET_' || p_subset_id
1105 , SQLERRM);
1106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1107
1108 END IF;
1109
1110 ----------------------------------------------------------------
1111 -- If everything was successful up to this point then we
1112 -- create the subset view.
1113 ----------------------------------------------------------------
1114 IF (l_return_code = FND_API.G_RET_STS_SUCCESS)
1115 THEN
1116 CREATE_SUBSET_VIEW( P_SOURCE_ID => P_SOURCE_ID
1117 , P_SUBSET_ID => P_SUBSET_ID
1118 , P_VIEW_NAME => l_subset_view_name
1119 , P_TARGET_GROUP_ID => P_TARGET_GROUP_ID
1120 , P_SOURCE_TYPE_VIEW_NAME => l_source_type_view_name
1121 , P_DEFAULT_SUBSET_FLAG => 'N'
1122 , X_RETURN_CODE => l_return_code
1123 );
1124
1125 IF (l_return_code <> FND_API.G_RET_STS_SUCCESS) THEN
1126 Log( 'RECREATE_SUBSET_VIEW'
1127 , 'CREATE_SUBSET_VIEW.SUBSET_' || p_subset_id
1128 , SQLERRM);
1129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1130 END IF;
1131
1132 X_SUBSET_VIEW_NAME := l_subset_view_name;
1133
1134 ELSE
1135 Log( 'RECREATE_SUBSET_VIEW'
1136 , 'DROP_SUBSET_VIEW.SUBSET_' || p_subset_id
1137 , SQLERRM);
1138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1139 END IF;
1140
1141 EXCEPTION
1142 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1143 THEN
1144 -- ROLLBACK TO RECREATE_SUBSET_VIEW_SAVE;
1145 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
1146 WHEN OTHERS THEN
1147 Log( 'RECREATE_SUBSET_VIEW'
1148 , 'MAIN.SUBSET_' || p_subset_id
1149 , SQLERRM);
1150 -- ROLLBACK TO RECREATE_SUBSET_VIEW_SAVE;
1151 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
1152
1153 END RECREATE_SUBSET_VIEW;
1154
1155 -----------------------------++++++-------------------------------
1156 --
1157 -- Start of comments
1158 --
1159 -- API name : TRANSITION_NON_CHECKED_ENTRIES
1160 -- Type : Public
1161 -- Pre-reqs : None
1162 -- Function :
1163 -- Parameters : P_SOURCE_ID IN NUMBER Required
1164 -- P_TARGET_GROUP_ID IN NUMBER Required
1165 -- X_RETURN_CODE OUT VARCHAR2 Required
1166 --
1167 -- Version : Initial version 1.0
1168 --
1169 -- End of comments
1170 --
1171 -----------------------------++++++-------------------------------
1172 PROCEDURE TRANSITION_NON_CHECKED_ENTRIES
1173 ( P_SOURCE_ID IN NUMBER
1174 , p_CAMPAIGN_ID IN NUMBER
1175 , P_SCHEDULE_ID IN NUMBER
1176 , P_TARGET_GROUP_ID IN NUMBER
1177 , P_STYPE_VIEW_NAME IN VARCHAR2
1178 , P_REINIT_FLAG IN VARCHAR2
1179 )
1180 IS
1181
1182 l_called_once_count NUMBER;
1183
1184 BEGIN
1185
1186 ----------------------------------------------------------------
1187 -- Need to collect counts. This is for the records that have
1188 -- NOT been checked out of AMS_LIST_ENTRIES. Those records
1189 -- need to be treated differently to keep from causing
1190 -- deadlocks with the other processes.
1191 ----------------------------------------------------------------
1192 FOR count_rec IN (
1193 select orig_subset_id
1194 , new_subset_id
1195 , do_not_use_flag
1196 , orig_itm_cc_tz_id
1197 , new_itm_cc_tz_id
1198 , call_Attempts
1199 , count(*) NUM_ENTRIES
1200 from iec_o_transition_subsets
1201 where (record_out_Flag = 'N' OR record_out_flag = 'R')
1202 -- and new_subset_id <> orig_subset_id
1203 and list_id = P_TARGET_GROUP_ID
1204 group by orig_subset_id
1205 , new_Subset_id
1206 , do_not_use_flag
1207 , orig_itm_cc_tz_id
1208 , new_itm_cc_Tz_id
1209 , call_Attempts
1210 )
1211 LOOP
1212
1213 ----------------------------------------------------------------
1214 -- Update the subset ids for the entries that have changed
1215 -- subsets and are not checked out in bulk. These are only
1216 -- available for updates by prefetching or validation and these
1217 -- should not be allowed at the same time as the subset
1218 -- transitioning for this schedule. I only updates these by time zones so I can
1219 -- update one record at a time from the IEC_G_REP_SUBSET_COUNTS and
1220 -- IEC_G_MKTG_ITEM_CC_TZS table to avoid deadlocks with validation
1221 -- calendar and recycling.
1222 ----------------------------------------------------------------
1223 TRACELOG('UPDATING RETURN_ENTRIES ');
1224
1225 EXECUTE IMMEDIATE 'UPDATE IEC_G_RETURN_ENTRIES A ' ||
1226 'SET A.SUBSET_ID = :newSubset ' ||
1227 ', A.ITM_CC_TZ_ID = :newCallZone ' ||
1228 ', A.PULLED_SUBSET_ID = NULL ' ||
1229 'WHERE A.LIST_ENTRY_ID IN (SELECT C.LIST_ENTRY_ID ' ||
1230 'FROM IEC_O_TRANSITION_SUBSETS C ' ||
1231 'WHERE C.LIST_ID = :listID ' ||
1232 'AND C.NEW_SUBSET_ID = :newSubset ' ||
1233 'AND C.ORIG_SUBSET_ID = :origSubset ' ||
1234 'AND C.NEW_ITM_CC_TZ_ID = :newCall ' ||
1235 'AND C.ORIG_ITM_CC_TZ_ID = :origCall ' ||
1236 'AND C.CALL_ATTEMPTS = :callAttempts ' ||
1237 'AND C.DO_NOT_USE_FLAG = :do_not_use_Flag ' ||
1238 'AND (C.RECORD_OUT_FLAG = ''N'' OR C.RECORD_OUT_FLAG = ''R''))' ||
1239 'AND A.LIST_HEADER_ID = :listID'
1240 USING count_rec.new_subset_id
1241 , count_rec.new_itm_cc_tz_id
1242 , P_TARGET_GROUP_ID
1243 , count_rec.new_subset_id
1244 , count_rec.orig_subset_id
1245 , count_rec.new_itm_cc_tz_id
1246 , count_rec.orig_itm_cc_tz_id
1247 , count_rec.call_Attempts
1248 , count_rec.do_not_use_flag
1249 , P_TARGET_GROUP_ID;
1250
1251 TRACELOG('UPDATED RETURN_ENTRIES ' || SQL%ROWCOUNT);
1252 ----------------------------------------------------------------
1253 -- Update the phone cross reference foreign keys for all of the
1254 -- entries that are moving subsets. Once we move to the
1255 -- architecture away from ALE then this will no longer be needed.
1256 ----------------------------------------------------------------
1257 TRACELOG('UPDATING VIEW ');
1258 EXECUTE IMMEDIATE 'UPDATE ' || P_STYPE_VIEW_NAME || ' A ' ||
1259 ' SET A.REASON_CODE_S1 = ( SELECT /*+ index(B iec_o_transition_phones_u1) */ ITM_CC_TZ_ID ' ||
1260 ' FROM IEC_O_TRANSITION_PHONES B' ||
1261 ' WHERE B.PHONE_INDEX = 1 ' ||
1262 ' AND B.LIST_ENTRY_ID = A.LIST_ENTRY_ID ' ||
1263 ' AND B.LIST_ID = A.LIST_HEADER_ID ) ' ||
1264 ' , A.REASON_CODE_S2 = ( SELECT /*+ index(C iec_o_transition_phones_u1) */ C.ITM_CC_TZ_ID ' ||
1265 ' FROM IEC_O_TRANSITION_PHONES C' ||
1266 ' WHERE C.PHONE_INDEX = 2 ' ||
1267 ' AND C.LIST_ENTRY_ID = A.LIST_ENTRY_ID ' ||
1268 ' AND C.LIST_ID = A.LIST_HEADER_ID ) ' ||
1269 ' , A.REASON_CODE_S3 = ( SELECT /*+ index(D iec_o_transition_phones_u1) */ D.ITM_CC_TZ_ID ' ||
1270 ' FROM IEC_O_TRANSITION_PHONES D' ||
1271 ' WHERE D.PHONE_INDEX = 3 ' ||
1272 ' AND D.LIST_ENTRY_ID = A.LIST_ENTRY_ID ' ||
1273 ' AND D.LIST_ID = A.LIST_HEADER_ID ) ' ||
1274 ' , A.REASON_CODE_S4 = ( SELECT /*+ index(E iec_o_transition_phones_u1) */ E.ITM_CC_TZ_ID ' ||
1275 ' FROM IEC_O_TRANSITION_PHONES E' ||
1276 ' WHERE E.PHONE_INDEX = 4 ' ||
1277 ' AND E.LIST_ENTRY_ID = A.LIST_ENTRY_ID ' ||
1278 ' AND E.LIST_ID = A.LIST_HEADER_ID ) ' ||
1279 ' , A.REASON_CODE_S5 = ( SELECT /*+ index(F iec_o_transition_phones_u1) */ F.ITM_CC_TZ_ID ' ||
1280 ' FROM IEC_O_TRANSITION_PHONES F' ||
1281 ' WHERE F.PHONE_INDEX = 5 ' ||
1282 ' AND F.LIST_ENTRY_ID = A.LIST_ENTRY_ID ' ||
1283 ' AND F.LIST_ID = A.LIST_HEADER_ID ) ' ||
1284 ' , A.REASON_CODE_S6 = ( SELECT /*+ index(G iec_o_transition_phones_u1) */ G.ITM_CC_TZ_ID ' ||
1285 ' FROM IEC_O_TRANSITION_PHONES G' ||
1286 ' WHERE G.PHONE_INDEX = 6 ' ||
1287 ' AND G.LIST_ENTRY_ID = A.LIST_ENTRY_ID ' ||
1288 ' AND G.LIST_ID = A.LIST_HEADER_ID ) ' ||
1289 'WHERE A.LIST_ENTRY_ID IN (SELECT H.LIST_ENTRY_ID ' ||
1290 'FROM IEC_O_TRANSITION_SUBSETS H ' ||
1291 'WHERE H.LIST_ID = :listID ' ||
1292 'AND H.NEW_SUBSET_ID = :newSubset ' ||
1293 'AND H.ORIG_SUBSET_ID = :origSubset ' ||
1294 'AND H.NEW_ITM_CC_TZ_ID = :newCall ' ||
1295 'AND H.ORIG_ITM_CC_TZ_ID = :origCall ' ||
1296 'AND H.CALL_ATTEMPTS = :callAttempts ' ||
1297 'AND H.DO_NOT_USE_FLAG = :do_not_use_Flag ' ||
1298 'AND (H.RECORD_OUT_FLAG = ''N'' OR H.RECORD_OUT_FLAG = ''R''))' ||
1299 'AND A.LIST_HEADER_ID = :listID'
1300 USING P_TARGET_GROUP_ID
1301 , count_rec.new_subset_id
1302 , count_rec.orig_subset_id
1303 , count_rec.new_itm_cc_tz_id
1304 , count_rec.orig_itm_cc_tz_id
1305 , count_rec.call_Attempts
1306 , count_rec.do_not_use_flag
1307 , P_TARGET_GROUP_ID;
1308
1309 TRACELOG('UPDATED VIEW ' || SQL%ROWCOUNT);
1310
1311 TRACELOG('Before Modifying callzones ' || count_rec.new_itm_cc_tz_id ||
1312 ' : ' || count_rec.orig_itm_cc_tz_id || ' : ' || count_rec.do_not_use_flag);
1313 ----------------------------------------------------------------
1314 -- If this group represents a remaining group then we have to
1315 -- adjust the iec_g_mktg_item_cc_Tzs callZones counts.
1316 ----------------------------------------------------------------
1317 IF count_rec.do_not_use_flag = 'N' AND count_rec.new_itm_cc_tz_id <> count_rec.orig_itm_cc_tz_id
1318 THEN
1319
1320 TRACELOG('Modifying callzones ' || count_rec.new_itm_cc_tz_id ||
1321 ' : ' || count_rec.orig_itm_cc_tz_id || ' : ' || count_rec.NUM_ENTRIES);
1322 ----------------------------------------------------------------
1323 -- Update the IEC_G_MKTG_ITEM_CC_TZS table. May need to select
1324 -- for update to dismiss the deadlock possibilities.
1325 ----------------------------------------------------------------
1326 EXECUTE IMMEDIATE 'UPDATE IEC_G_MKTG_ITEM_CC_TZS ' ||
1327 'SET RECORD_COUNT = RECORD_COUNT + :remainingCount ' ||
1328 'WHERE ITM_CC_TZ_ID = :callZone'
1329 USING count_rec.NUM_ENTRIES, count_rec.new_itm_cc_tz_id;
1330
1331 ----------------------------------------------------------------
1332 -- Update the IEC_G_MKTG_ITEM_CC_TZS table.
1333 ----------------------------------------------------------------
1334 IF (P_REINIT_FLAG = 'N')
1335 THEN
1336 EXECUTE IMMEDIATE 'UPDATE IEC_G_MKTG_ITEM_CC_TZS ' ||
1337 'SET RECORD_COUNT = RECORD_COUNT - :remainingCount ' ||
1338 'WHERE ITM_CC_TZ_ID = :callZone'
1339 USING count_rec.NUM_ENTRIES, count_rec.orig_itm_cc_tz_id;
1340 END IF;
1341 END IF;
1342
1343 ----------------------------------------------------------------
1344 -- Has this group been called once or not.
1345 ----------------------------------------------------------------
1346 IF count_rec.call_Attempts = 0
1347 THEN
1348 l_called_once_count := 0;
1349 ELSE
1350 l_called_once_count := count_rec.NUM_ENTRIES;
1351 END IF;
1352
1353 ----------------------------------------------------------------
1354 -- Update the IEC_G_REP_SUBSET_COUNTS table.
1355 -- May need to select for update to dismiss the deadlock possibilities.
1356 ----------------------------------------------------------------
1357 IF count_rec.new_subset_id <> count_rec.orig_subset_id
1358 THEN
1359 UPDATE_SUBSET_COUNTS( P_CAMPAIGN_ID
1360 , P_SCHEDULE_ID
1361 , P_TARGET_GROUP_ID
1362 , count_rec.new_subset_id
1363 , count_rec.NUM_ENTRIES
1364 , l_called_once_count
1365 );
1366
1367 IF (P_REINIT_FLAG = 'N')
1368 THEN
1369 UPDATE_SUBSET_COUNTS( P_CAMPAIGN_ID
1370 , P_SCHEDULE_ID
1371 , P_TARGET_GROUP_ID
1372 , count_rec.orig_subset_id
1373 , (0 - count_rec.NUM_ENTRIES)
1374 , (0 - l_called_once_count)
1375 );
1376 END IF;
1377 END IF;
1378 COMMIT;
1379
1380 END LOOP;
1381
1382
1383
1384 EXCEPTION
1385 -- Fixme add logic to handle if entry does not exist.
1386 -- This should no happen.
1387 WHEN FND_API.G_EXC_ERROR THEN
1388 ROLLBACK;
1389 RAISE;
1390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1391 ROLLBACK;
1392 RAISE;
1393 WHEN OTHERS THEN
1394 ROLLBACK;
1395 RAISE;
1396 END TRANSITION_NON_CHECKED_ENTRIES;-- PL/SQL Block
1397
1398
1399 -----------------------------++++++-------------------------------
1400 --
1401 -- Start of comments
1402 --
1403 -- API name : GET_NEW_ZONE_XREF
1404 -- Type : Public
1405 -- Pre-reqs : None
1406 -- Function :
1407 -- Parameters : P_SOURCE_ID IN NUMBER Required
1408 -- P_TARGET_GROUP_ID IN NUMBER
1409 -- P_NEW_SUBSET_ID IN NUMBER
1410 -- P_ORIG_XREF IN NUMBER
1411 -- X_NEW_XREF OUT NUMBER
1412 --
1413 -- Version : Initial version 1.0
1414 --
1415 -- End of comments
1416 --
1417 -----------------------------++++++-------------------------------
1418 PROCEDURE GET_NEW_ZONE_XREF( P_SOURCE_ID IN NUMBER
1419 , P_SCHEDULE_ID IN NUMBER
1420 , P_TARGET_GROUP_ID IN NUMBER
1421 , P_NEW_SUBSET_ID IN NUMBER
1422 , P_ORIG_XREF IN NUMBER
1423 , X_NEW_XREF OUT NOCOPY NUMBER)
1424 IS
1425
1426 BEGIN
1427
1428 x_new_xref := NULL;
1429
1430 ----------------------------------------------------------------
1431 -- If the original id is -1 then one was not assigned.
1432 ----------------------------------------------------------------
1433 IF (P_ORIG_XREF = -1)
1434 THEN
1435 X_NEW_XREF := -1;
1436
1437 ----------------------------------------------------------------
1438 -- The original id isn't -1 then one was assigned.
1439 ----------------------------------------------------------------
1440 ELSE
1441 BEGIN
1442
1443 EXECUTE IMMEDIATE 'SELECT ITM_CC_TZ_ID FROM IEC_G_MKTG_ITEM_CC_TZS ' ||
1444 ' WHERE LIST_HEADER_ID = :listId AND SUBSET_ID = :subsetID ' ||
1445 ' AND (TERRITORY_CODE, TIMEZONE_ID, NVL(REGION_ID, -1)) = ' ||
1446 ' (SELECT TERRITORY_CODE, TIMEZONE_ID, NVL(REGION_ID, -1) ' ||
1447 ' FROM IEC_G_MKTG_ITEM_CC_TZS WHERE ITM_CC_TZ_ID = :xref_id)'
1448 INTO X_NEW_XREF
1449 USING P_TARGET_GROUP_ID, P_NEW_SUBSET_ID, P_ORIG_XREF;
1450
1451 EXCEPTION
1452 ----------------------------------------------------------------
1453 -- If we cannot find a new zone then we need to create one.
1454 ----------------------------------------------------------------
1455 WHEN NO_DATA_FOUND THEN
1456
1457 BEGIN
1458 ----------------------------------------------------------------
1459 -- Insert a new entry into the iec_g_mktg_item_cc_Tzs table for
1460 -- any new zone that is now in the
1461 ----------------------------------------------------------------
1462 EXECUTE IMMEDIATE 'INSERT INTO IEC_G_MKTG_ITEM_CC_TZS ' ||
1463 ' (ITM_CC_TZ_ID, LIST_HEADER_ID, CAMPAIGN_SCHEDULE_ID, TERRITORY_CODE, ' ||
1464 ' TIMEZONE_ID, LAST_CALLABLE_TIME, CALLABLE_FLAG, OBJECT_VERSION_NUMBER, ' ||
1465 ' SECURITY_GROUP_ID, LAST_UPDATE_DATE, RECORD_COUNT, REGION_ID, SUBSET_ID) '||
1466 ' select IEC_G_MKTG_ITEM_CC_TZS_S.NEXTVAL, :listID, :schedID ' ||
1467 ', a.territory_code, a.timezone_id, NULL, ''N'', 0, -1 ' ||
1468 ', SYSDATE, 0, a.region_id, :subsetId from ' ||
1469 ' (SELECT region_id, territory_code, timezone_id '||
1470 ' from iec_g_mktg_item_cc_tzs c where c.itm_cc_Tz_id = :xref_id) a ' ||
1471 ' RETURNING ITM_CC_TZ_ID '
1472 INTO X_NEW_XREF
1473 USING P_TARGET_GROUP_ID, P_SCHEDULE_ID, P_ORIG_XREF;
1474 EXCEPTION
1475 WHEN OTHERS THEN
1476 RAISE;
1477 END; -- end insertingnew xref block.
1478 WHEN OTHERS THEN
1479 RAISE;
1480 END; -- end locating new xref block.
1481 END IF;
1482 EXCEPTION
1483 WHEN FND_API.G_EXC_ERROR THEN
1484 ROLLBACK;
1485 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
1486 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1487 ROLLBACK;
1488 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
1489 WHEN OTHERS THEN
1490 ROLLBACK;
1491 Log( 'GET_NEW_ZONE_XREF'
1492 , 'ASSIGN_NEW_CALLABLE_ZONES.LIST_' || p_target_group_id
1493 , SQLERRM);
1494 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
1495
1496 END GET_NEW_ZONE_XREF;
1497
1498
1499 -----------------------------++++++-------------------------------
1500 --
1501 -- Start of comments
1502 --
1503 -- API name : CONTINUAL_TRANSITION
1504 -- Type : Public
1505 -- Pre-reqs : None
1506 -- Function :
1507 -- Parameters : P_SOURCE_ID IN NUMBER Required
1508 -- P_TARGET_GROUP_ID IN NUMBER Required
1509 -- X_NUM_REMAINING OUT NUMBER Required
1510 -- X_ACTION_ID OUT NUMBER Required
1511 --
1512 -- Version : Initial version 1.0
1513 --
1514 -- End of comments
1515 --
1516 -----------------------------++++++-------------------------------
1517 PROCEDURE CONTINUAL_TRANSITION
1518 ( P_SOURCE_ID IN NUMBER
1519 , P_CAMPAIGN_ID IN NUMBER
1520 , P_SCHEDULE_ID IN NUMBER
1521 , P_TARGET_GROUP_ID IN NUMBER
1522 , X_NUM_REMAINING OUT NOCOPY NUMBER
1523 , X_ACTION_ID OUT NOCOPY NUMBER
1524 )
1525 IS
1526 l_new_itm_xref_id1 NUMBER := 0;
1527 l_new_itm_xref_id2 NUMBER := 0;
1528 l_new_itm_xref_id3 NUMBER := 0;
1529 l_new_itm_xref_id4 NUMBER := 0;
1530 l_new_itm_xref_id5 NUMBER := 0;
1531 l_new_itm_xref_id6 NUMBER := 0;
1532 l_old_itm_xref_id1 NUMBER := 0;
1533 l_old_itm_xref_id2 NUMBER := 0;
1534 l_old_itm_xref_id3 NUMBER := 0;
1535 l_old_itm_xref_id4 NUMBER := 0;
1536 l_old_itm_xref_id5 NUMBER := 0;
1537 l_old_itm_xref_id6 NUMBER := 0;
1538 l_curr_itm_xref_id NUMBER := 0;
1539 l_curr_phone_index NUMBER := 0;
1540
1541 ----------------------------------------------------------------
1542 -- The source type view name for the current list.
1543 ----------------------------------------------------------------
1544 l_src_type_view_name VARCHAR2(30);
1545
1546 BEGIN
1547 l_src_type_view_name := 'NULL';
1548 X_NUM_REMAINING := 0;
1549 x_action_id := NULL;
1550
1551 ----------------------------------------------------------------
1552 -- Need to get the source type name.
1553 ----------------------------------------------------------------
1554 GET_SOURCETYPE_VIEW_NAME
1555 ( P_SOURCE_ID => P_SOURCE_ID
1556 , P_TARGET_GROUP_ID => P_TARGET_GROUP_ID
1557 , X_VIEW_NAME => l_src_type_view_name
1558 );
1559
1560 IF (l_src_type_view_name <> 'NULL')
1561 THEN
1562 ----------------------------------------------------------------
1563 -- Need to loop thru the entries we were unable to transition
1564 -- due to their state in the system and transition them
1565 -- if they have been checked back in.
1566 ----------------------------------------------------------------
1567 FOR entry_rec IN (
1568 select a.subset_id SUBSET_ID
1569 , a.pulled_subset_id TRANSITION_SUBSET_ID
1570 , a.list_entry_id LIST_ENTRY_ID
1571 , a.record_out_flag RECORD_OUT_FLAG
1572 , a.itm_cc_Tz_id ITM_CC_TZ_ID
1573 , a.contact_point_index CONTACT_POINT_INDEX
1574 , a.returns_id RETURNS_ID
1575 , a.do_not_use_flag DO_NOT_USE_FLAG
1576 , DECODE(NVL(SUM(B.CALL_ATTEMPT), 0), 0, 0, 1) CALLED_ONCE
1577 from iec_g_return_entries a
1578 , IEC_O_RCY_CALL_HISTORIES B
1579 where a.LIST_HEADER_ID = P_TARGET_GROUP_ID
1580 and a.pulled_subset_id IS NOT NULL
1581 and a.returns_id = b.returns_id(+)
1582 group by a.subset_id
1583 , a.pulled_subset_id
1584 , a.list_entry_id
1585 , a.record_out_flag
1586 , a.itm_cc_Tz_id
1587 , a.contact_point_index
1588 , a.returns_id
1589 , a.do_not_use_flag
1590 )
1591 LOOP
1592
1593
1594 ----------------------------------------------------------------
1595 -- If the entry is still checked out then simply update the
1596 -- counter.
1597 ----------------------------------------------------------------
1598 IF (entry_rec.record_out_flag = 'Y')
1599 THEN
1600 X_NUM_REMAINING := X_NUM_REMAINING + 1;
1601
1602 ----------------------------------------------------------------
1603 -- The record has been checked back in so execute the transition.
1604 ----------------------------------------------------------------
1605 ELSE
1606
1607 ----------------------------------------------------------------
1608 -- We have to get the new itm_cc_Tz_ids for the entry.
1609 ----------------------------------------------------------------
1610 EXECUTE IMMEDIATE 'SELECT NVL(A.reason_code_S1, -1), NVL(A.reason_code_S2, -1), ' ||
1611 ' NVL(A.reason_code_S3, -1), NVL(A.reason_code_S4, -1), ' ||
1612 ' NVL(A.reason_code_S5, -1), NVL(A.reason_code_S6, -1) ' ||
1613 ' FROM ' || l_src_type_view_name || ' a ' ||
1614 ' WHERE A.LIST_HEADER_ID = :listID AND A.LIST_ENTRY_ID = :entryId'
1615 INTO l_old_itm_xref_id1, l_old_itm_xref_id2, l_old_itm_xref_id3
1616 , l_old_itm_xref_id4, l_old_itm_xref_id5, l_old_itm_xref_id6
1617 USING P_TARGET_GROUP_ID, entry_rec.list_entry_id;
1618
1619 ----------------------------------------------------------------
1620 -- Look at each phone cross ref to see if it has been assigned.
1621 -- And try to fetch the new one if it hasn't.
1622 ----------------------------------------------------------------
1623 GET_NEW_ZONE_XREF( P_SOURCE_ID
1624 , P_SCHEDULE_ID
1625 , P_TARGET_GROUP_ID
1626 , entry_rec.TRANSITION_SUBSET_ID
1627 , l_old_itm_xref_id1
1628 , l_new_itm_xref_id1);
1629
1630 GET_NEW_ZONE_XREF( P_SOURCE_ID
1631 , P_SCHEDULE_ID
1632 , P_TARGET_GROUP_ID
1633 , entry_rec.TRANSITION_SUBSET_ID
1634 , l_old_itm_xref_id2
1635 , l_new_itm_xref_id2);
1636
1637 GET_NEW_ZONE_XREF( P_SOURCE_ID
1638 , P_SCHEDULE_ID
1639 , P_TARGET_GROUP_ID
1640 , entry_rec.TRANSITION_SUBSET_ID
1641 , l_old_itm_xref_id3
1642 , l_new_itm_xref_id3);
1643
1644 GET_NEW_ZONE_XREF( P_SOURCE_ID
1645 , P_SCHEDULE_ID
1646 , P_TARGET_GROUP_ID
1647 , entry_rec.TRANSITION_SUBSET_ID
1648 , l_old_itm_xref_id4
1649 , l_new_itm_xref_id4);
1650
1651 GET_NEW_ZONE_XREF( P_SOURCE_ID
1652 , P_SCHEDULE_ID
1653 , P_TARGET_GROUP_ID
1654 , entry_rec.TRANSITION_SUBSET_ID
1655 , l_old_itm_xref_id5
1656 , l_new_itm_xref_id5);
1657
1658 GET_NEW_ZONE_XREF( P_SOURCE_ID
1659 , P_SCHEDULE_ID
1660 , P_TARGET_GROUP_ID
1661 , entry_rec.TRANSITION_SUBSET_ID
1662 , l_old_itm_xref_id6
1663 , l_new_itm_xref_id6);
1664
1665 ----------------------------------------------------------------
1666 -- Need to set the current xref id according to the index.
1667 ----------------------------------------------------------------
1668 IF (entry_rec.CONTACT_POINT_INDEX = 1)
1669 THEN
1670 l_curr_itm_xref_id := l_new_itm_xref_id1;
1671
1672 ELSIF (entry_rec.CONTACT_POINT_INDEX = 2)
1673 THEN
1674 l_curr_itm_xref_id := l_new_itm_xref_id2;
1675
1676 ELSIF (entry_rec.CONTACT_POINT_INDEX = 3)
1677 THEN
1678 l_curr_itm_xref_id := l_new_itm_xref_id3;
1679
1680 ELSIF (entry_rec.CONTACT_POINT_INDEX = 4)
1681 THEN
1682 l_curr_itm_xref_id := l_new_itm_xref_id4;
1683
1684 ELSIF (entry_rec.CONTACT_POINT_INDEX = 5)
1685 THEN
1686 l_curr_itm_xref_id := l_new_itm_xref_id5;
1687
1688 ELSIF (entry_rec.CONTACT_POINT_INDEX = 6)
1689 THEN
1690 l_curr_itm_xref_id := l_new_itm_xref_id6;
1691 END IF;
1692
1693
1694 ----------------------------------------------------------------
1695 -- FIRST RESET THE SUBSET AND CURRENT CALL ZONE ON THE LIST.
1696 ----------------------------------------------------------------
1697 EXECUTE IMMEDIATE 'UPDATE IEC_G_RETURN_ENTRIES SET SUBSET_ID = :subsetId ' ||
1698 ', PULLED_SUBSET_ID = NULL, ITM_CC_TZ_ID = :xrefId ' ||
1699 ' WHERE RETURNS_ID = :returnsId'
1700 USING entry_rec.TRANSITION_SUBSET_ID
1701 , l_curr_itm_xref_id
1702 , entry_rec.RETURNS_ID;
1703
1704 IF (l_new_itm_xref_id1 = -1)
1705 THEN
1706 l_new_itm_xref_id1 := NULL;
1707 END IF;
1708 IF (l_new_itm_xref_id2 = -1)
1709 THEN
1710 l_new_itm_xref_id2 := NULL;
1711 END IF;
1712 IF (l_new_itm_xref_id3 = -1)
1713 THEN
1714 l_new_itm_xref_id3 := NULL;
1715 END IF;
1716 IF (l_new_itm_xref_id4 = -1)
1717 THEN
1718 l_new_itm_xref_id4 := NULL;
1719 END IF;
1720 IF (l_new_itm_xref_id5 = -1)
1721 THEN
1722 l_new_itm_xref_id5 := NULL;
1723 END IF;
1724 IF (l_new_itm_xref_id6 = -1)
1725 THEN
1726 l_new_itm_xref_id6 := NULL;
1727 END IF;
1728
1729 ----------------------------------------------------------------
1730 -- RESET ALL OF THE CALL ZONES FOR THE ENTRY ON THE LIST.
1731 ----------------------------------------------------------------
1732 EXECUTE IMMEDIATE 'UPDATE ' || l_src_type_view_name || ' SET ' ||
1733 ' REASON_CODE_S1 = :zoneXref1, ' ||
1734 ' REASON_CODE_S2 = :zoneXref2, ' ||
1735 ' REASON_CODE_S3 = :zoneXref3, ' ||
1736 ' REASON_CODE_S4 = :zoneXref4, ' ||
1737 ' REASON_CODE_S5 = :zoneXref5, ' ||
1738 ' REASON_CODE_S6 = :zoneXref6 ' ||
1739 ' WHERE LIST_HEADER_ID = :listID AND LIST_ENTRY_ID = :entryID '
1740 USING l_new_itm_xref_id1
1741 , l_new_itm_xref_id2
1742 , l_new_itm_xref_id3
1743 , l_new_itm_xref_id4
1744 , l_new_itm_xref_id5
1745 , l_new_itm_xref_id6
1746 , P_TARGET_GROUP_ID, entry_rec.LIST_ENTRY_ID;
1747
1748 ----------------------------------------------------------------
1749 -- Update the count of the callzones that the entry is
1750 -- transitioning between if the entry is still usable.
1751 ----------------------------------------------------------------
1752 IF (entry_rec.DO_NOT_USE_FLAG = 'N')
1753 THEN
1754
1755 ----------------------------------------------------------------
1756 -- Increment the count for the new callzone by one.
1757 ----------------------------------------------------------------
1758 EXECUTE IMMEDIATE 'UPDATE IEC_G_MKTG_ITEM_CC_TZS ' ||
1759 ' SET RECORD_COUNT = RECORD_COUNT + 1' ||
1760 ', LAST_UPDATE_DATE = SYSDATE ' ||
1761 ' WHERE ITM_CC_TZ_ID = :xrefId '
1762 USING l_curr_itm_xref_id;
1763
1764 ----------------------------------------------------------------
1765 -- Decrement the count for the orig callzone by one.
1766 ----------------------------------------------------------------
1767 EXECUTE IMMEDIATE 'UPDATE IEC_G_MKTG_ITEM_CC_TZS ' ||
1768 ' SET RECORD_COUNT = RECORD_COUNT - 1' ||
1769 ', LAST_UPDATE_DATE = SYSDATE ' ||
1770 ' WHERE ITM_CC_TZ_ID = :xrefId '
1771 USING entry_rec.ITM_CC_TZ_ID;
1772
1773 END IF;
1774
1775 ----------------------------------------------------------------
1776 -- Increment the count for the new subset by one.
1777 ----------------------------------------------------------------
1778 UPDATE_SUBSET_COUNTS( P_CAMPAIGN_ID
1779 , P_SCHEDULE_ID
1780 , P_TARGET_GROUP_ID
1781 , entry_rec.TRANSITION_SUBSET_ID
1782 , 1
1783 , entry_rec.CALLED_ONCE
1784 );
1785
1786 ----------------------------------------------------------------
1787 -- Decrement the count for the original subset by one.
1788 ----------------------------------------------------------------
1789 UPDATE_SUBSET_COUNTS( P_CAMPAIGN_ID
1790 , P_SCHEDULE_ID
1791 , P_TARGET_GROUP_ID
1792 , entry_rec.SUBSET_ID
1793 , (0 - 1)
1794 , (0 - entry_rec.CALLED_ONCE)
1795 );
1796 COMMIT;
1797 END IF;
1798
1799 END LOOP;
1800
1801 ----------------------------------------------------------------
1802 -- We weren't able to process cause we couldn't find the
1803 -- source type view name.
1804 ----------------------------------------------------------------
1805 ELSE
1806 -- this won't really happen - procedure to get source type view
1807 -- will throw exception if source type view doesn't exist
1808 g_error_msg := 'Could not locate source type view name for list: ' || P_TARGET_GROUP_ID;
1809
1810 END IF; -- end of if we could find the source type view name.
1811
1812 EXCEPTION
1813 WHEN FND_API.G_EXC_ERROR THEN
1814 ROLLBACK;
1815 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
1816 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1817 ROLLBACK;
1818 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
1819 WHEN OTHERS THEN
1820 Log( 'CONTINUAL_TRANSITION'
1821 , 'TRANSITION_SUBSETS.LIST_' || p_target_group_id
1822 , SQLERRM);
1823 ROLLBACK;
1824 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
1825
1826 END CONTINUAL_TRANSITION;
1827
1828 -----------------------------++++++-------------------------------
1829 --
1830 -- Start of comments
1831 --
1832 -- API name : GET_LIST_SCHEDULE_ID
1833 -- Type : Public
1834 -- Pre-reqs : None
1835 -- Function :
1836 -- Parameters : P_SOURCE_ID IN NUMBER Required
1837 -- P_TARGET_GROUP_ID IN NUMBER
1838 -- X_SCHED_ID OUT NUMBER
1839 --
1840 -- Version : Initial version 1.0
1841 --
1842 -- End of comments
1843 --
1844 -----------------------------++++++-------------------------------
1845 PROCEDURE GET_LIST_SCHEDULE_ID( P_SOURCE_ID IN NUMBER
1846 , P_TARGET_GROUP_ID IN NUMBER
1847 , X_SCHEDULE_ID OUT NOCOPY NUMBER
1848 )
1849 IS
1850 BEGIN
1851
1852 x_schedule_id := NULL;
1853 IEC_COMMON_UTIL_PVT.Get_ScheduleId(p_target_group_id, x_schedule_id);
1854
1855 EXCEPTION
1856 WHEN OTHERS THEN
1857 ROLLBACK;
1858 -- FND_MESSAGE is initialized but not logged in IEC_COMMON_UTIL_PVT
1859 -- if an exception is thrown, so we log it here with current
1860 -- module
1861 Log( 'GET_LIST_SCHEDULE_ID', 'MAIN.LIST_' || p_target_group_id);
1862 RAISE fnd_api.g_exc_unexpected_error;
1863
1864 END GET_LIST_SCHEDULE_ID;
1865
1866 -----------------------------++++++-------------------------------
1867 --
1868 -- Start of comments
1869 --
1870 -- API name : GET_DEFAULT_SUBSET_ID
1871 -- Type : Public
1872 -- Pre-reqs : None
1873 -- Function :
1874 -- Parameters : P_SOURCE_ID IN NUMBER Required
1875 -- P_TARGET_GROUP_ID IN NUMBER
1876 -- X_DEFAULT_SUBSET_ID OUT NUMBER
1877 --
1878 -- Version : Initial version 1.0
1879 --
1880 -- End of comments
1881 --
1882 -----------------------------++++++-------------------------------
1883 PROCEDURE GET_DEFAULT_SUBSET_ID( P_SOURCE_ID IN NUMBER
1884 , P_TARGET_GROUP_ID IN NUMBER
1885 , X_DEFAULT_SUBSET_ID OUT NOCOPY NUMBER
1886 )
1887 IS
1888 BEGIN
1889
1890 x_default_subset_id := NULL;
1891
1892 EXECUTE IMMEDIATE 'SELECT LIST_SUBSET_ID FROM IEC_G_LIST_SUBSETS ' ||
1893 'WHERE LIST_HEADER_ID = :listID AND DEFAULT_SUBSET_FLAG = ''Y'''
1894 INTO X_DEFAULT_SUBSET_ID
1895 USING P_TARGET_GROUP_ID;
1896
1897 EXCEPTION
1898 WHEN OTHERS THEN
1899 ROLLBACK;
1900 x_default_subset_id := NULL;
1901 Log( 'GET_DEFAULT_SUBSET_ID'
1902 , 'MAIN.LIST_' || p_target_group_id
1903 , SQLERRM);
1904 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
1905
1906 END GET_DEFAULT_SUBSET_ID;
1907
1908 -----------------------------++++++-------------------------------
1909 --
1910 -- Start of comments
1911 --
1912 -- API name : TRANSITION_ENTRIES
1913 -- Type : Private
1914 -- Pre-reqs : None
1915 -- Function :
1916 -- Parameters : P_SOURCE_ID IN NUMBER Required
1917 -- P_SERVER_ID IN NUMBER Required
1918 -- P_SCHED_ID IN NUMBER Required
1919 -- P_TARGET_GROUP_ID IN NUMBER Required
1920 -- P_FROM_SUBSET IN NUMBER_TBL_TYPE Required
1921 -- P_INTO_SUBSET IN NUMBER_TBL_TYPE Required
1922 -- P_ACTION_TYPE IN VARCHAR2 Required
1923 -- P_PHONE_SQL IN VARCHAR2 Required
1924 -- P_ENTRY_SQL IN VARCHAR2 Required
1925 -- P_BATCH_SIZE IN NUMBER Required
1926 -- P_SRC_TYPE_VIEW IN VARCHAR2 Required
1927 -- X_NUM_PENDING IN OUT NUMBER Required
1928 --
1929 -- Version : Initial version 1.0
1930 --
1931 -- End of comments
1932 --
1933 -----------------------------++++++-------------------------------
1934 PROCEDURE TRANSITION_ENTRIES
1935 ( P_SOURCE_ID IN NUMBER
1936 , P_SERVER_ID IN NUMBER
1937 , P_CAMPAIGN_ID IN NUMBER
1938 , P_SCHED_ID IN NUMBER
1939 , P_TARGET_GROUP_ID IN NUMBER
1940 , P_FROM_SUBSET IN NUMBER
1941 , P_INTO_SUBSET IN NUMBER
1942 , P_ACTION_TYPE IN VARCHAR2
1943 , P_PHONE_SQL IN VARCHAR2
1944 , P_ENTRY_SQL IN VARCHAR2
1945 , P_BATCH_SIZE IN NUMBER
1946 , P_SRC_TYPE_VIEW IN VARCHAR2
1947 , X_NUM_PENDING IN OUT NOCOPY NUMBER)
1948 IS
1949
1950 TYPE SubsetEntryType IS REF CURSOR;
1951
1952 l_return_tbl SYSTEM.NUMBER_TBL_TYPE := SYSTEM.NUMBER_TBL_TYPE();
1953 l_locked_flag VARCHAR2(1);
1954 l_subset_entry_cursor SubsetEntryType;
1955 l_checked_entry_cursor SubsetEntryType;
1956 l_phone_cursor SubsetEntryType;
1957 l_phone_index NUMBER := 0;
1958 l_curr_list_Entry_id NUMBER;
1959 l_curr_subset_id NUMBER;
1960 l_curr_returns_id NUMBER;
1961 l_phone_entry_tbl UniqueIdList;
1962 l_phone_subset_tbl UniqueIdList;
1963 l_index_tbl UniqueIdList;
1964 l_region_tbl UniqueIdList;
1965 l_timezone_tbl UniqueIdList;
1966 l_territory_tbl TerritoryList;
1967 l_curr_return_id NUMBER;
1968
1969 BEGIN
1970 l_locked_flag := 'N';
1971
1972 ----------------------------------------------------------------
1973 -- OPEN THE CURSOR FOR ALL ENTRIES TRANSITIONED FROM THE FROM
1974 -- SUBSET TO THE INTO SUBSET.
1975 ----------------------------------------------------------------
1976 OPEN l_subset_entry_cursor FOR P_ENTRY_SQL USING P_FROM_SUBSET;
1977
1978 LOOP
1979
1980 LOOP
1981 FETCH l_subset_entry_cursor INTO l_curr_return_id;
1982
1983 EXIT WHEN l_subset_entry_cursor%NOTFOUND;
1984
1985 l_return_tbl.EXTEND(1);
1986 l_return_tbl(l_return_tbl.LAST) := l_curr_return_id;
1987
1988 EXIT WHEN l_return_tbl.COUNT >= P_BATCH_SIZE;
1989 END LOOP;
1990
1991 ----------------------------------------------------------------
1992 -- If fetch did not return any rows then drop out of loop.
1993 ----------------------------------------------------------------
1994 EXIT WHEN l_return_tbl.COUNT = 0 ;
1995
1996 ----------------------------------------------------------------
1997 -- We first lock the schedule. This is only executed in order
1998 -- to lock individual entries. If we try to lock individual
1999 -- subsets there maybe the chance that we leave the subsets
2000 -- locked indefinitely if the procedure is cancelled. Therefore
2001 -- we stick at the schedule level for safety reasons.
2002 ----------------------------------------------------------------
2003 LOCK_SCHEDULE( P_SOURCE_ID => P_SOURCE_ID
2004 , P_SCHED_ID => P_SCHED_ID
2005 , P_SERVER_ID => P_SERVER_ID
2006 , P_LOCK_FLAG => 'Y'
2007 , X_SUCCESS_FLAG => l_locked_flag);
2008
2009 ----------------------------------------------------------------
2010 -- If we were able to lock the schedule then we will update
2011 -- all of the pulled subset ids for this particular batch
2012 -- of entries that have not been pulled yet so they are locked.
2013 ----------------------------------------------------------------
2014 IF l_locked_flag = 'Y' THEN
2015 TRACELOG('LOCKED SCHEDULE ' || P_SCHED_ID);
2016
2017 BEGIN
2018 EXECUTE IMMEDIATE 'UPDATE IEC_G_RETURN_ENTRIES A ' ||
2019 'SET A.PULLED_SUBSET_ID = :newSubsetId ' ||
2020 'WHERE A.RETURNS_ID IN (SELECT * FROM TABLE(CAST(:collection AS SYSTEM.NUMBER_TBL_TYPE))) ' ||
2021 'AND A.RECORD_OUT_FLAG = ''N'' AND A.PULLED_SUBSET_ID IS NULL'
2022 USING P_INTO_SUBSET
2023 , l_return_tbl;
2024
2025 ----------------------------------------------------------------
2026 -- Increment transition entries to account for these
2027 -- entries.
2028 ----------------------------------------------------------------
2029 X_NUM_PENDING := X_NUM_PENDING + SQL%ROWCOUNT;
2030
2031 ----------------------------------------------------------------
2032 -- Commit will lock the entries.
2033 ----------------------------------------------------------------
2034 COMMIT;
2035
2036 EXCEPTION
2037 WHEN OTHERS THEN
2038 Log( 'Transition_Entries'
2039 , 'LOCK_SUBSET_ENTRIES.SUBSET_' || p_from_subset
2040 , SQLERRM);
2041 ----------------------------------------------------------------
2042 -- We can now unlock the schedule.
2043 ----------------------------------------------------------------
2044 LOCK_SCHEDULE( P_SOURCE_ID => P_SOURCE_ID
2045 , P_SCHED_ID => P_SCHED_ID
2046 , P_SERVER_ID => P_SERVER_ID
2047 , P_LOCK_FLAG => 'N'
2048 , X_SUCCESS_FLAG => l_locked_flag);
2049 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2050 END;
2051
2052 ----------------------------------------------------------------
2053 -- We can now unlock the schedule.
2054 ----------------------------------------------------------------
2055 LOCK_SCHEDULE( P_SOURCE_ID => P_SOURCE_ID
2056 , P_SCHED_ID => P_SCHED_ID
2057 , P_SERVER_ID => P_SERVER_ID
2058 , P_LOCK_FLAG => 'N'
2059 , X_SUCCESS_FLAG => l_locked_flag);
2060
2061 ELSE
2062 TRACELOG('COULD NOT LOCK SCHEDULE ' || P_SCHED_ID);
2063 END IF;
2064
2065 ----------------------------------------------------------------
2066 -- At This point either we have entries that have already been
2067 -- checked out or we weren't able to get a lock so we have
2068 -- to loop thru the remaining entries and lock them one by one.
2069 ----------------------------------------------------------------
2070
2071 OPEN l_checked_entry_cursor FOR 'SELECT RETURNS_ID FROM IEC_G_RETURN_ENTRIES ' ||
2072 'WHERE PULLED_SUBSET_ID IS NULL ' ||
2073 'AND RETURNS_ID IN (SELECT * FROM TABLE(CAST(:collection AS SYSTEM.NUMBER_TBL_TYPE)))'
2074 USING l_return_tbl;
2075
2076 ----------------------------------------------------------------
2077 -- LOOP THRU ENTRIES RETRIEVED IN THE FETCH. These are the
2078 -- entries in the original cursor that were not previously
2079 -- locked.
2080 ----------------------------------------------------------------
2081 LOOP
2082 FETCH l_checked_entry_cursor
2083 INTO l_curr_returns_id;
2084 EXIT WHEN l_checked_entry_cursor%NOTFOUND;
2085
2086 EXECUTE IMMEDIATE 'UPDATE IEC_G_RETURN_ENTRIES A ' ||
2087 'SET PULLED_SUBSET_ID = :newSubsetId ' ||
2088 'WHERE RETURNS_ID = :returnsId ' ||
2089 'AND PULLED_SUBSET_ID IS NULL'
2090 USING P_INTO_SUBSET
2091 , l_curr_returns_id;
2092
2093 ----------------------------------------------------------------
2094 -- Increment transition entries to account for this individual
2095 -- entry.
2096 ----------------------------------------------------------------
2097 X_NUM_PENDING := X_NUM_PENDING + 1;
2098
2099 COMMIT;
2100 END LOOP;
2101
2102 CLOSE l_checked_entry_cursor;
2103
2104 ----------------------------------------------------------------
2105 -- Make sure the table has been cleaned of any entries belonging
2106 -- to this target group. If we only allow one subset transition
2107 -- at a time, then we could truncate the table.
2108 ----------------------------------------------------------------
2109 BEGIN
2110 EXECUTE IMMEDIATE 'DELETE FROM iec_o_transition_subsets ' ||
2111 'WHERE list_id = :1 '
2112 USING P_TARGET_GROUP_ID;
2113 EXCEPTION
2114 WHEN OTHERS THEN
2115 RAISE;
2116 END;
2117
2118 ----------------------------------------------------------------
2119 -- The entries are locked and we now need to transition this
2120 -- batch. First we pull entries that are not checked out into
2121 -- the transition table. Ones that are checked out will be
2122 -- transitioned when they are checked back in.
2123 ----------------------------------------------------------------
2124 EXECUTE IMMEDIATE 'INSERT INTO IEC_O_TRANSITION_SUBSETS ' ||
2125 '( LIST_ID ' ||
2126 ', LIST_ENTRY_ID ' ||
2127 ', ORIG_SUBSET_ID ' ||
2128 ', NEW_SUBSET_ID ' ||
2129 ', ORIG_ITM_CC_TZ_ID ' ||
2130 ', NEW_ITM_CC_TZ_ID ' ||
2131 ', DO_NOT_USE_FLAG ' ||
2132 ', RECORD_OUT_FLAG ' ||
2133 ', RETURNS_ID ' ||
2134 ', CALL_ATTEMPTS ' ||
2135 ') ' ||
2136 'SELECT A.LIST_HEADER_ID, A.LIST_ENTRY_ID, A.SUBSET_ID, A.PULLED_SUBSET_ID ' ||
2137 ', A.ITM_CC_TZ_ID, NULL, A.DO_NOT_USE_FLAG, ''N'', A.RETURNS_ID, ' ||
2138 ' DECODE(NVL(SUM(B.CALL_ATTEMPT), 0), 0, 0, 1) ' ||
2139 ' FROM IEC_G_RETURN_ENTRIES A, IEC_O_RCY_CALL_HISTORIES B ' ||
2140 ' WHERE A.SUBSET_ID = :oldSubsetId ' ||
2141 ' AND A.RETURNS_ID = B.RETURNS_ID(+) ' ||
2142 ' AND A.PULLED_SUBSET_ID = :newSubsetId' ||
2143 ' AND A.RECORD_OUT_FLAG = ''N'' GROUP BY ' ||
2144 ' A.LIST_HEADER_ID, A.LIST_ENTRY_ID, A.SUBSET_ID, A.PULLED_SUBSET_ID ' ||
2145 ', A.ITM_CC_TZ_ID, NULL, A.DO_NOT_USE_FLAG, ''N'', A.RETURNS_ID'
2146 USING P_FROM_SUBSET
2147 , P_INTO_SUBSET;
2148
2149 ----------------------------------------------------------------
2150 -- Now that we have all of the entries in the transition table
2151 -- we need to get all of the entries phone numbers in the
2152 -- phone number entry table. First we make sure the
2153 -- phone number transition table is cleared of all
2154 -- entries that pertain to this target group.
2155 ----------------------------------------------------------------
2156 EXECUTE IMMEDIATE 'DELETE FROM iec_o_transition_phones where list_id = :1'
2157 USING P_TARGET_GROUP_ID;
2158
2159 ----------------------------------------------------------------
2160 -- Need to get all of the unique calling zones of the moved entries
2161 -- phone numbers. Make sure there is a zone in the
2162 -- IEC_G_MKTG_ITEM_CC_TZS table to support.
2163 ----------------------------------------------------------------
2164 l_phone_index := 0;
2165
2166 ----------------------------------------------------------------
2167 -- open the cursor to fetch the phone numbers for the current
2168 -- set of transitioning entries.
2169 ----------------------------------------------------------------
2170 OPEN l_phone_cursor FOR P_PHONE_SQL USING P_TARGET_GROUP_ID;
2171
2172 ----------------------------------------------------------------
2173 -- Continue fetching the entries phone numbers and bringing
2174 -- them into a local collection.
2175 ----------------------------------------------------------------
2176 LOOP
2177 l_phone_index := l_phone_index + 1;
2178
2179 FETCH l_phone_cursor INTO l_curr_list_Entry_id
2180 , l_curr_subset_id
2181 , l_index_tbl(l_phone_index)
2182 , l_territory_tbl(l_phone_index)
2183 , l_timezone_tbl(l_phone_index)
2184 , l_region_tbl(l_phone_index)
2185 , l_index_tbl(l_phone_index + 1)
2186 , l_territory_tbl(l_phone_index + 1)
2187 , l_timezone_tbl(l_phone_index + 1)
2188 , l_region_tbl(l_phone_index + 1)
2189 , l_index_tbl(l_phone_index + 2)
2190 , l_territory_tbl(l_phone_index + 2)
2191 , l_timezone_tbl(l_phone_index + 2)
2192 , l_region_tbl(l_phone_index + 2)
2193 , l_index_tbl(l_phone_index + 3)
2194 , l_territory_tbl(l_phone_index + 3)
2195 , l_timezone_tbl(l_phone_index + 3)
2196 , l_region_tbl(l_phone_index + 3)
2197 , l_index_tbl(l_phone_index + 4)
2198 , l_territory_tbl(l_phone_index + 4)
2199 , l_timezone_tbl(l_phone_index + 4)
2200 , l_region_tbl(l_phone_index + 4)
2201 , l_index_tbl(l_phone_index + 5)
2202 , l_territory_tbl(l_phone_index + 5)
2203 , l_timezone_tbl(l_phone_index + 5)
2204 , l_region_tbl(l_phone_index + 5);
2205
2206 ----------------------------------------------------------------
2207 -- When cursor returns NOTFOUND we have stopped fetching from
2208 -- the cursor and therefore can exit the loop.
2209 ----------------------------------------------------------------
2210 EXIT WHEN l_phone_cursor%NOTFOUND;
2211
2212 l_phone_subset_tbl(l_phone_index) := l_curr_subset_id;
2213 l_phone_entry_tbl(l_phone_index) := l_curr_list_Entry_id;
2214 l_phone_subset_tbl(l_phone_index + 1) := l_curr_subset_id;
2215 l_phone_entry_tbl(l_phone_index + 1) := l_curr_list_Entry_id;
2216 l_phone_subset_tbl(l_phone_index + 2) := l_curr_subset_id;
2217 l_phone_entry_tbl(l_phone_index + 2) := l_curr_list_Entry_id;
2218 l_phone_subset_tbl(l_phone_index + 3) := l_curr_subset_id;
2219 l_phone_entry_tbl(l_phone_index + 3) := l_curr_list_Entry_id;
2220 l_phone_subset_tbl(l_phone_index + 4) := l_curr_subset_id;
2221 l_phone_entry_tbl(l_phone_index + 4) := l_curr_list_Entry_id;
2222 l_phone_subset_tbl(l_phone_index + 5) := l_curr_subset_id;
2223 l_phone_entry_tbl(l_phone_index + 5) := l_curr_list_Entry_id;
2224 l_phone_index := l_phone_index + 5;
2225
2226 END LOOP;
2227
2228 ----------------------------------------------------------------
2229 -- Now that the internal collections have been filled we can close
2230 -- the phone cursor.
2231 ----------------------------------------------------------------
2232 CLOSE l_phone_cursor;
2233
2234 ----------------------------------------------------------------
2235 -- Insert the phone entries into the transition table using
2236 -- the internal collections.
2237 ----------------------------------------------------------------
2238 IF l_phone_entry_tbl.COUNT > 0
2239 THEN
2240 FORALL j IN l_phone_entry_tbl.FIRST..l_phone_entry_tbl.LAST
2241 INSERT INTO iec_o_transition_phones
2242 ( LIST_ID
2243 , LIST_ENTRY_ID
2244 , SUBSET_ID
2245 , territory_code
2246 , region_id
2247 , timezone_id
2248 , phone_index
2249 )
2250 VALUES
2251 ( P_TARGET_GROUP_ID
2252 , l_phone_entry_tbl(j)
2253 , l_phone_subset_tbl(j)
2254 , l_territory_tbl(j)
2255 , l_region_tbl(j)
2256 , l_timezone_tbl(j)
2257 , l_index_tbl(j));
2258 END IF;
2259
2260 ----------------------------------------------------------------
2261 -- Initialize all of the internal collections.
2262 ----------------------------------------------------------------
2263 l_phone_entry_tbl.DELETE;
2264 l_phone_subset_tbl.DELETE;
2265 l_territory_tbl.DELETE;
2266 l_region_tbl.DELETE;
2267 l_timezone_tbl.DELETE;
2268 l_index_tbl.DELETE;
2269
2270 ----------------------------------------------------------------
2271 -- Insert a new entry into the iec_g_mktg_item_cc_Tzs table for
2272 -- any new zone that is now in the phone transition table.
2273 ----------------------------------------------------------------
2274 EXECUTE IMMEDIATE 'INSERT INTO IEC_G_MKTG_ITEM_CC_TZS ' ||
2275 ' (ITM_CC_TZ_ID, LIST_HEADER_ID, CAMPAIGN_SCHEDULE_ID, TERRITORY_CODE, ' ||
2276 ' TIMEZONE_ID, LAST_CALLABLE_TIME, CALLABLE_FLAG, OBJECT_VERSION_NUMBER, ' ||
2277 ' SECURITY_GROUP_ID, LAST_UPDATE_DATE, RECORD_COUNT, REGION_ID, SUBSET_ID) '||
2278 ' select IEC_G_MKTG_ITEM_CC_TZS_S.NEXTVAL, :listID, :schedID ' ||
2279 ', a.territory_code, a.timezone_id, NULL, ''N'', 0, -1 ' ||
2280 ', SYSDATE, 0, a.region_code, a.subset_id from ' ||
2281 ' (SELECT DISTINCT DECODE(region_id, -1, NULL, region_id) region_code, territory_code, subset_id, timezone_id '||
2282 ' from iec_o_transition_phones c where c.list_id = :listID ' ||
2283 ' and territory_code <> ''-1'' and timezone_id <> -1 ' ||
2284 ' and not exists (select null from iec_g_mktg_item_Cc_Tzs b where ' ||
2285 ' c.subset_id = b.subset_id and c.territory_code = b.territory_code ' ||
2286 ' and NVL(b.region_id, -1) = c.region_id and c.timezone_id = b.timezone_id ' ||
2287 ' and c.list_id = b.list_header_id) ) a'
2288 USING P_TARGET_GROUP_ID
2289 , P_SCHED_ID
2290 , P_TARGET_GROUP_ID;
2291
2292 ----------------------------------------------------------------
2293 -- Updating the temporary
2294 -- phones table with the callable zones cross reference id.
2295 ----------------------------------------------------------------
2296 EXECUTE IMMEDIATE 'UPDATE IEC_O_TRANSITION_PHONES A SET A.ITM_CC_TZ_ID = ' ||
2297 '( SELECT B.ITM_CC_TZ_ID FROM IEC_G_MKTG_ITEM_CC_TZS B ' ||
2298 ' WHERE B.LIST_HEADER_ID = A.LIST_ID AND B.SUBSET_ID = A.SUBSET_ID ' ||
2299 ' AND B.TERRITORY_CODE = A.TERRITORY_CODE AND B.TIMEZONE_ID = A.TIMEZONE_ID ' ||
2300 ' AND NVL(B.REGION_ID, -1) = A.REGION_ID) WHERE A.LIST_ID = :listID ' ||
2301 ' AND A.TERRITORY_CODE <> ''-1'' AND A.TIMEZONE_ID <> -1 '
2302 USING P_TARGET_GROUP_ID;
2303
2304 ----------------------------------------------------------------
2305 -- Updating the transition entries table with the
2306 -- new current callable zone cross reference id.
2307 ----------------------------------------------------------------
2308 EXECUTE IMMEDIATE 'UPDATE IEC_O_TRANSITION_SUBSETS A SET A.NEW_ITM_CC_TZ_ID = ' ||
2309 ' (SELECT D.ITM_CC_TZ_ID ' ||
2310 ' FROM IEC_G_RETURN_ENTRIES B, IEC_G_MKTG_ITEM_CC_TZS C, IEC_G_MKTG_ITEM_CC_TZS D ' ||
2311 ' WHERE A.LIST_ENTRY_ID = B.LIST_ENTRY_ID AND A.LIST_ID = B.LIST_HEADER_ID ' ||
2312 ' AND B.LIST_HEADER_ID = C.LIST_HEADER_ID AND B.ITM_CC_TZ_ID = C.ITM_CC_TZ_ID' ||
2313 ' AND D.TERRITORY_CODE = C.TERRITORY_CODE AND D.SUBSET_ID = A.NEW_SUBSET_ID AND ' ||
2314 ' D.TIMEZONE_ID = C.TIMEZONE_ID AND D.LIST_HEADER_ID = C.LIST_HEADER_ID ' ||
2315 ' AND NVL(D.REGION_ID, -1) = NVL(C.REGION_ID, -1)) ' ||
2316 ' WHERE A.LIST_ID = :list_ID' ||
2317 ' AND A.ORIG_SUBSET_ID <> A.NEW_SUBSET_ID'
2318 USING P_TARGET_GROUP_ID;
2319
2320 ----------------------------------------------------------------
2321 -- Need to transition entries. This is for the records that have
2322 -- NOT been checked out of AMS_LIST_ENTRIES. Those records
2323 -- need to be treated differently to keep from causing
2324 -- deadlocks with the other processes.
2325 ----------------------------------------------------------------
2326 TRANSITION_NON_CHECKED_ENTRIES( P_SOURCE_ID
2327 , P_CAMPAIGN_ID
2328 , P_SCHED_ID
2329 , P_TARGET_GROUP_ID
2330 , P_SRC_TYPE_VIEW
2331 , 'N');
2332
2333 ----------------------------------------------------------------
2334 -- If fetch did not return batch size then finished with cursor.
2335 ----------------------------------------------------------------
2336 EXIT WHEN l_return_tbl.COUNT < P_BATCH_SIZE ;
2337
2338 l_return_tbl.DELETE;
2339 END LOOP;
2340
2341 ----------------------------------------------------------------
2342 -- Return the number of entries that have yet to be transitioned.
2343 -- For perforamnce reasons we could choose to simply look for
2344 -- the first instead of actually counting.
2345 ----------------------------------------------------------------
2346 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM IEC_G_RETURN_ENTRIES WHERE SUBSET_ID = :1 and PULLED_SUBSET_ID IS NOT NULL'
2347 INTO X_NUM_PENDING
2348 USING P_FROM_SUBSET;
2349
2350 ----------------------------------------------------------------
2351 -- Makes sure the returns collection is removed.
2352 ----------------------------------------------------------------
2353 l_return_tbl.DELETE;
2354
2355 ----------------------------------------------------------------
2356 -- Makes sure the subset entry cursor is closed.
2357 ----------------------------------------------------------------
2358 CLOSE l_subset_entry_cursor;
2359
2360 EXCEPTION
2361 WHEN FND_API.G_EXC_ERROR THEN
2362 IF l_subset_entry_cursor%ISOPEN
2363 THEN
2364 CLOSE l_subset_entry_cursor;
2365 END IF;
2366 ROLLBACK;
2367 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
2368 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2369 IF l_subset_entry_cursor%ISOPEN
2370 THEN
2371 CLOSE l_subset_entry_cursor;
2372 END IF;
2373 ROLLBACK;
2374 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
2375 WHEN OTHERS THEN
2376 Log( 'Transition_Entries'
2377 , 'MAIN.LIST_' || p_target_group_id
2378 , SQLERRM);
2379 IF l_subset_entry_cursor%ISOPEN
2380 THEN
2381 CLOSE l_subset_entry_cursor;
2382 END IF;
2383 ROLLBACK;
2384 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
2385
2386 END TRANSITION_ENTRIES;-- PL/SQL Block
2387
2388 -----------------------------++++++-------------------------------
2389 --
2390 -- Start of comments
2391 --
2392 -- API name : SUBSET_TRANSITION
2393 -- Type : Public
2394 -- Pre-reqs : None
2395 -- Function :
2396 -- Parameters : P_SOURCE_ID IN NUMBER Required
2397 -- P_SERVER_ID IN NUMBER
2398 -- P_TARGET_GROUP_ID IN NUMBER
2399 -- P_FROM_SUBSETS IN NUMBER_TBL_TYPE
2400 -- P_INTO_SUBSETS IN NUMBER_TBL_TYPE
2401 -- P_ACTION_TYPE IN VARCHAR2
2402 -- X_NUM_PENDING OUT NUMBER
2403 -- X_ACTION_ID OUT NUMBER
2404 --
2405 -- Version : Initial version 1.0
2406 --
2407 -- End of comments
2408 --
2409 -----------------------------++++++-------------------------------
2410 PROCEDURE SUBSET_TRANSITION
2411 ( P_SOURCE_ID IN NUMBER
2412 , P_SERVER_ID IN NUMBER
2413 , P_CAMPAIGN_ID IN NUMBER
2414 , P_SCHEDULE_ID IN NUMBER
2415 , P_TARGET_GROUP_ID IN NUMBER
2416 , P_FROM_SUBSETS IN SYSTEM.NUMBER_TBL_TYPE
2417 , P_INTO_SUBSETS IN SYSTEM.NUMBER_TBL_TYPE
2418 , P_ACTION_TYPE IN VARCHAR2
2419 , X_NUM_PENDING OUT NOCOPY NUMBER
2420 , X_ACTION_ID OUT NOCOPY NUMBER
2421 )
2422 IS
2423
2424 ----------------------------------------------------------------
2425 -- Local copy of the subsets to move entries into.
2426 ----------------------------------------------------------------
2427 l_into_subsets SYSTEM.NUMBER_TBL_TYPE := SYSTEM.NUMBER_TBL_TYPE();
2428
2429 ----------------------------------------------------------------
2430 -- Status code used locally.
2431 ----------------------------------------------------------------
2432 L_STATUS_CODE VARCHAR2(1);
2433
2434 ----------------------------------------------------------------
2435 -- Status code used locally.
2436 ----------------------------------------------------------------
2437 l_return_code VARCHAR2(1);
2438
2439 ----------------------------------------------------------------
2440 -- The source type view name for the current list.
2441 ----------------------------------------------------------------
2442 l_src_type_view_name VARCHAR2(30);
2443
2444 ----------------------------------------------------------------
2445 -- The subset view name for the current subset.
2446 ----------------------------------------------------------------
2447 l_subset_view_name VARCHAR2(30);
2448
2449 ----------------------------------------------------------------
2450 -- Query used to return candidate entries.
2451 ----------------------------------------------------------------
2452 l_first_query_entry_sql VARCHAR2(3000);
2453
2454 ----------------------------------------------------------------
2455 -- Query used to return candidate entries.
2456 ----------------------------------------------------------------
2457 l_second_query_entry_sql VARCHAR2(3000);
2458
2459 ----------------------------------------------------------------
2460 -- Query used to return candidate entries' phone numbers.
2461 ----------------------------------------------------------------
2462 l_phone_sql VARCHAR2(3000);
2463
2464 ----------------------------------------------------------------
2465 -- Default Subset id for the passed in list.
2466 ----------------------------------------------------------------
2467 l_default_subset_id NUMBER := 0;
2468
2469 ----------------------------------------------------------------
2470 -- The size of the current subset that the entries
2471 -- will be transitioned from.
2472 ----------------------------------------------------------------
2473 l_subset_load_size NUMBER := 0;
2474
2475 ----------------------------------------------------------------
2476 -- The number of entries left in the current subset that the entries
2477 -- will be transitioned from.
2478 ----------------------------------------------------------------
2479 l_subset_left_size NUMBER := 0;
2480
2481 ----------------------------------------------------------------
2482 -- The number of entries callable in the current subset that the entries
2483 -- will be transitioned from.
2484 ----------------------------------------------------------------
2485 l_subset_callable_size NUMBER := 0;
2486
2487 ----------------------------------------------------------------
2488 -- The maximum number of entries to lock of the next entries.
2489 ----------------------------------------------------------------
2490 l_first_batch_size NUMBER := 0;
2491
2492 ----------------------------------------------------------------
2493 -- The maximum number of entries to lock of all of the rest
2494 -- of the entries.
2495 ----------------------------------------------------------------
2496 l_last_batch_size NUMBER := 0;
2497
2498 ----------------------------------------------------------------
2499 -- Initialize l_transition_entries to 0. If l_transition_entries
2500 -- is greater than 0 at the end of this procedure then this is a
2501 -- continuous transition.
2502 ----------------------------------------------------------------
2503 l_transition_entries NUMBER := 0;
2504
2505 ----------------------------------------------------------------
2506 -- Flag determines if there are currently any entries callable.
2507 -- If there are then the candidate entries in the first transition
2508 -- process will be the callable entries if not then the candidate
2509 -- entries will be all of the entries that can still be used.
2510 ----------------------------------------------------------------
2511 L_USE_CALLABLE_FLAG BOOLEAN := TRUE;
2512
2513 BEGIN
2514 L_STATUS_CODE := FND_API.G_RET_STS_SUCCESS;
2515 l_return_code := FND_API.G_RET_STS_SUCCESS;
2516
2517 X_NUM_PENDING := 0;
2518 X_ACTION_ID := 0;
2519
2520 ----------------------------------------------------------------
2521 -- This will retrieve the view name from an IEc lookup value
2522 -- that has been seeded in the database. In the future there
2523 -- might be an algorithm used to BUILD the view name using values
2524 -- stored in the marketing schema.
2525 ----------------------------------------------------------------
2526 GET_SOURCETYPE_VIEW_NAME
2527 ( P_SOURCE_ID => P_SOURCE_ID
2528 , P_TARGET_GROUP_ID => P_TARGET_GROUP_ID
2529 , X_VIEW_NAME => l_src_type_view_name);
2530
2531
2532
2533 ----------------------------------------------------------------
2534 -- If the transition from subset is to be deleted then we need
2535 -- the id for the default subset in order to transition all
2536 -- entries that do not fall into the category of another defined
2537 -- subset.
2538 ----------------------------------------------------------------
2539 IF (P_ACTION_TYPE = 'Y')
2540 THEN
2541
2542 ----------------------------------------------------------------
2543 -- This will return the default subset id for this target group.
2544 ----------------------------------------------------------------
2545 GET_DEFAULT_SUBSET_ID( P_SOURCE_ID => P_SOURCE_ID
2546 , P_TARGET_GROUP_ID => P_TARGET_GROUP_ID
2547 , X_DEFAULT_SUBSET_ID => l_default_subset_id);
2548
2549 ----------------------------------------------------------------
2550 -- Loop thru the list of subsets that entries will be
2551 -- transitioning into. Make sure the default subset is placed at
2552 -- the end of the collection. If the default subset is anywhere
2553 -- else in the list then set it to -1 so it doesn't try to
2554 -- execute.
2555 ----------------------------------------------------------------
2556 FOR I IN P_INTO_SUBSETS.FIRST..P_INTO_SUBSETS.LAST
2557 LOOP
2558 IF (P_INTO_SUBSETS(I) = l_default_subset_id)
2559 THEN
2560 IF (I = P_INTO_SUBSETS.LAST)
2561 THEN
2562 l_into_subsets.EXTEND(1);
2563 l_into_subsets(l_into_subsets.LAST) := P_INTO_SUBSETS(I);
2564 EXIT;
2565 END IF;
2566 ELSE
2567 l_into_subsets.EXTEND(1);
2568 l_into_subsets(l_into_subsets.LAST) := P_INTO_SUBSETS(I);
2569 IF (I = P_INTO_SUBSETS.LAST)
2570 THEN
2571 l_into_subsets.EXTEND(1);
2572 l_into_subsets(l_into_subsets.LAST) := l_default_subset_id;
2573 END IF;
2574 END IF;
2575 END LOOP;
2576
2577 ELSE
2578
2579 ----------------------------------------------------------------
2580 -- Loop thru the list of subsets that entries will be
2581 -- transitioning into. Make sure the default subset is placed at
2582 -- the end of the collection. If the default subset is anywhere
2583 -- else in the list then set it to -1 so it doesn't try to
2584 -- execute.
2585 ----------------------------------------------------------------
2586 FOR I IN P_INTO_SUBSETS.FIRST..P_INTO_SUBSETS.LAST
2587 LOOP
2588 l_into_subsets.EXTEND(1);
2589 l_into_subsets(l_into_subsets.LAST) := P_INTO_SUBSETS(I);
2590 END LOOP;
2591
2592 END IF;
2593
2594 ----------------------------------------------------------------
2595 -- Calculate the size of the current subset that the entries
2596 -- will be transitioned from.
2597 ----------------------------------------------------------------
2598 BEGIN
2599 EXECUTE IMMEDIATE 'SELECT NVL(RECORD_LOADED,0) ' ||
2600 ' FROM IEC_G_REP_SUBSET_COUNTS ' ||
2601 ' WHERE subset_id = :subsetId '
2602 INTO l_subset_load_size
2603 USING P_FROM_SUBSETS(1);
2604 EXCEPTION
2605 WHEN OTHERS THEN
2606 Log( 'Subset_Transition'
2607 , 'GET_TOTAL_ENTRY_COUNT.SUBSET_' || P_FROM_SUBSETS(1)
2608 , SQLERRM);
2609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2610 END;
2611
2612 ----------------------------------------------------------------
2613 -- Calculate the number of entries left in the current subset that the entries
2614 -- will be transitioned from.
2615 ----------------------------------------------------------------
2616 BEGIN
2617 EXECUTE IMMEDIATE 'SELECT NVL(SUM(RECORD_COUNT),0) ' ||
2618 ' FROM IEC_G_MKTG_ITEM_CC_TZS ' ||
2619 ' WHERE subset_id = :subsetId '
2620 INTO l_subset_left_size
2621 USING P_FROM_SUBSETS(1);
2622 EXCEPTION
2623 WHEN OTHERS THEN
2624 Log( 'Subset_Transition'
2625 , 'GET_REMAINING_ENTRY_COUNT.SUBSET_' || P_FROM_SUBSETS(1)
2626 , SQLERRM);
2627 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2628 END;
2629
2630 ----------------------------------------------------------------
2631 -- Calculate the number of entries callable in the current subset that the entries
2632 -- will be transitioned from.
2633 ----------------------------------------------------------------
2634 BEGIN
2635 EXECUTE IMMEDIATE 'SELECT NVL(SUM(RECORD_COUNT),0) ' ||
2636 ' FROM IEC_G_MKTG_ITEM_CC_TZS ' ||
2637 ' WHERE subset_id = :subsetId ' ||
2638 ' AND CALLABLE_FLAG = ''Y'' ' ||
2639 ' AND LAST_CALLABLE_TIME > SYSDATE '
2640 INTO l_subset_callable_size
2641 USING P_FROM_SUBSETS(1);
2642
2643 EXCEPTION
2644 WHEN OTHERS THEN
2645 Log( 'Subset_Transition'
2646 , 'GET_CALLABLE_ENTRY_COUNT.SUBSET_' || P_FROM_SUBSETS(1)
2647 , SQLERRM);
2648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2649 END;
2650
2651 ----------------------------------------------------------------
2652 -- Determine the max batch size for moving entries that are
2653 -- candidates for the next retrieval.
2654 ----------------------------------------------------------------
2655 IF (L_SUBSET_CALLABLE_SIZE <= 0) THEN
2656 IF (L_SUBSET_LEFT_SIZE <= 0) THEN
2657 IF (L_SUBSET_LEFT_SIZE > 200) THEN
2658 L_FIRST_BATCH_SIZE := TRUNC(L_SUBSET_LEFT_SIZE / 2);
2659 ELSE
2660 L_FIRST_BATCH_SIZE := L_SUBSET_LEFT_SIZE;
2661 END IF;
2662 ELSE
2663 L_FIRST_BATCH_SIZE := 10000;
2664 END IF;
2665 L_USE_CALLABLE_FLAG := FALSE;
2666 ELSE
2667 IF (L_SUBSET_CALLABLE_SIZE > 200) THEN
2668 L_FIRST_BATCH_SIZE := TRUNC(L_SUBSET_CALLABLE_SIZE / 2);
2669 ELSE
2670 L_FIRST_BATCH_SIZE := L_SUBSET_CALLABLE_SIZE;
2671 END IF;
2672 L_USE_CALLABLE_FLAG := TRUE;
2673 END IF;
2674
2675 ----------------------------------------------------------------
2676 -- Determine the max batch size for moving entries that are
2677 -- not candidates for the next retrieval.
2678 ----------------------------------------------------------------
2679 l_LAST_BATCH_SIZE := 10000;
2680
2681 ----------------------------------------------------------------
2682 -- We need to retrieve all of the phone numbers for the current
2683 -- group of candidate entries.
2684 ----------------------------------------------------------------
2685 l_phone_sql := 'SELECT a.list_entry_id, h.new_subset_id ' ||
2686 ',1, NVL(b.TERRITORY_CODE, ''-1''), NVL(b.TIMEZONE_ID, -1), NVL(b.REGION_ID, -1) ' ||
2687 ',2, NVL(c.TERRITORY_CODE, ''-1''), NVL(c.TIMEZONE_ID, -1), NVL(c.REGION_ID, -1) ' ||
2688 ',3, NVL(d.TERRITORY_CODE, ''-1''), NVL(d.TIMEZONE_ID, -1), NVL(d.REGION_ID, -1) ' ||
2689 ',4, NVL(e.TERRITORY_CODE, ''-1''), NVL(e.TIMEZONE_ID, -1), NVL(e.REGION_ID, -1) ' ||
2690 ',5, NVL(f.TERRITORY_CODE, ''-1''), NVL(f.TIMEZONE_ID, -1), NVL(f.REGION_ID, -1) ' ||
2691 ',6, NVL(g.TERRITORY_CODE, ''-1''), NVL(g.TIMEZONE_ID, -1), NVL(g.REGION_ID, -1) ' ||
2692 'from ' || l_src_type_view_name || ' a ' ||
2693 ', iec_g_mktg_item_cc_Tzs b ' ||
2694 ', iec_g_mktg_item_cc_Tzs c ' ||
2695 ', iec_g_mktg_item_cc_Tzs d ' ||
2696 ', iec_g_mktg_item_cc_Tzs e ' ||
2697 ', iec_g_mktg_item_cc_Tzs f ' ||
2698 ', iec_g_mktg_item_cc_Tzs g ' ||
2699 ', IEC_O_TRANSITION_SUBSETS h ' ||
2700 'where h.list_id = :listID ' ||
2701 'and h.list_id = a.list_header_id ' ||
2702 'and h.list_entry_id = a.list_entry_id ' ||
2703 'and a.reason_code_S1 = b.itm_cc_tz_id(+) ' ||
2704 'and a.reason_code_S2 = c.itm_cc_tz_id(+) ' ||
2705 'and a.reason_code_S3 = d.itm_cc_tz_id(+) ' ||
2706 'and a.reason_code_S4 = e.itm_cc_tz_id(+) ' ||
2707 'and a.reason_code_S5 = f.itm_cc_tz_id(+) ' ||
2708 'and a.reason_code_S6 = g.itm_cc_tz_id(+)';
2709
2710 ----------------------------------------------------------------
2711 -- Initialize l_transition_entries to 0. If l_transition_entries
2712 -- is greater than 0 at the end of this procedure then this is a
2713 -- continuous transition.
2714 ----------------------------------------------------------------
2715 l_transition_entries := 0;
2716
2717 ----------------------------------------------------------------
2718 -- Loop thru the list of subsets that entries will be
2719 -- transitioning into. Disregard the default subset.
2720 -- This loop is to move entries that are the next that could be
2721 -- called.
2722 ----------------------------------------------------------------
2723 FOR I IN l_into_subsets.FIRST..l_into_subsets.LAST
2724 LOOP
2725 IF (l_into_subsets(I) > 0 OR (P_ACTION_TYPE = 'N' AND l_into_subsets(I) = l_default_subset_id))
2726 THEN
2727
2728 IF (P_ACTION_TYPE = 'N' OR l_into_subsets(I) <> l_default_subset_id)
2729 THEN
2730
2731 ----------------------------------------------------------------
2732 -- Return the view that we will use for this into subset.
2733 ----------------------------------------------------------------
2734 l_subset_view_name := GET_SUBSET_VIEW( P_SOURCE_ID
2735 , P_TARGET_GROUP_ID
2736 , l_into_subsets(I)
2737 , ' '
2738 , l_src_type_view_name
2739 , l_return_code);
2740
2741 ----------------------------------------------------------------
2742 -- Build SQL query for the cursor for this into subset.
2743 -- Think about the timing with the L_USE_CALLABLE_FLAG.
2744 ----------------------------------------------------------------
2745 IF L_USE_CALLABLE_FLAG = TRUE
2746 THEN
2747 l_first_query_entry_sql := 'SELECT A.RETURNS_ID ' ||
2748 ' FROM IEC_G_RETURN_ENTRIES A ' ||
2749 ' WHERE A.SUBSET_ID = :subsetId ' ||
2750 ' AND A.PULLED_SUBSET_ID IS NULL ' ||
2751 ' AND A.ITM_CC_TZ_ID IN ' ||
2752 ' (SELECT ITM_CC_TZ_ID FROM ' ||
2753 ' IEC_G_MKTG_ITEM_CC_TZS B WHERE ' ||
2754 ' A.SUBSET_ID = B.SUBSET_ID ' ||
2755 ' AND B.CALLABLE_FLAG = ''Y'' ' ||
2756 ' AND B.LAST_CALLABLE_TIME > SYSDATE) ' ||
2757 ' AND A.LIST_ENTRY_ID IN (SELECT C.LIST_ENTRY_ID ' ||
2758 ' FROM ' || l_subset_view_name || ' C)';
2759 ELSE
2760 l_first_query_entry_sql := 'SELECT A.RETURNS_ID ' ||
2761 ' FROM IEC_G_RETURN_ENTRIES A ' ||
2762 ' WHERE A.SUBSET_ID = :subsetId ' ||
2763 ' AND A.PULLED_SUBSET_ID IS NULL ' ||
2764 ' AND A.DO_NOT_USE_FLAG = ''N'' ' ||
2765 ' AND A.LIST_ENTRY_ID IN (SELECT C.LIST_ENTRY_ID ' ||
2766 ' FROM ' || l_subset_view_name || ' C)';
2767
2768 END IF;
2769
2770 l_second_query_entry_sql := 'SELECT A.RETURNS_ID ' ||
2771 ' FROM IEC_G_RETURN_ENTRIES A ' ||
2772 ' WHERE A.SUBSET_ID = :subsetId ' ||
2773 ' AND A.PULLED_SUBSET_ID IS NULL ' ||
2774 ' AND A.LIST_ENTRY_ID IN (SELECT C.LIST_ENTRY_ID ' ||
2775 ' FROM ' || l_subset_view_name || ' C)';
2776
2777 ELSE
2778 ----------------------------------------------------------------
2779 -- Build SQL query for the cursor for this into subset.
2780 -- Think about the timing with the L_USE_CALLABLE_FLAG.
2781 ----------------------------------------------------------------
2782 IF L_USE_CALLABLE_FLAG = TRUE THEN
2783 l_first_query_entry_sql := 'SELECT A.RETURNS_ID ' ||
2784 ' FROM IEC_G_RETURN_ENTRIES A ' ||
2785 ' WHERE A.SUBSET_ID = :subsetId ' ||
2786 ' AND A.PULLED_SUBSET_ID IS NULL ' ||
2787 ' AND A.ITM_CC_TZ_ID IN ' ||
2788 ' (SELECT ITM_CC_TZ_ID FROM ' ||
2789 ' IEC_G_MKTG_ITEM_CC_TZS B WHERE ' ||
2790 ' A.SUBSET_ID = B.SUBSET_ID ' ||
2791 ' AND B.CALLABLE_FLAG = ''Y'' ' ||
2792 ' AND B.LAST_CALLABLE_TIME > SYSDATE)';
2793 ELSE
2794 l_first_query_entry_sql := 'SELECT A.RETURNS_ID ' ||
2795 ' FROM IEC_G_RETURN_ENTRIES A ' ||
2796 ' WHERE A.SUBSET_ID = :subsetId ' ||
2797 ' AND A.PULLED_SUBSET_ID IS NULL ' ||
2798 ' AND A.DO_NOT_USE_FLAG = ''N'' ';
2799 END IF;
2800
2801 l_second_query_entry_sql := 'SELECT A.RETURNS_ID ' ||
2802 ' FROM IEC_G_RETURN_ENTRIES A ' ||
2803 ' WHERE A.SUBSET_ID = :subsetId ' ||
2804 ' AND A.PULLED_SUBSET_ID IS NULL ';
2805
2806 END IF;
2807
2808 ----------------------------------------------------------------
2809 -- First Call Tranisition Entries to transition the entries that
2810 -- will be the most likely to be fetched next.
2811 ----------------------------------------------------------------
2812
2813 TRANSITION_ENTRIES( P_SOURCE_ID => P_SOURCE_ID
2814 , P_SERVER_ID => P_SERVER_ID
2815 , P_CAMPAIGN_ID => P_CAMPAIGN_ID
2816 , P_SCHED_ID => P_SCHEDULE_ID
2817 , P_TARGET_GROUP_ID => P_TARGET_GROUP_ID
2818 , P_FROM_SUBSET => P_FROM_SUBSETS(1)
2819 , P_INTO_SUBSET => l_into_subsets(I)
2820 , P_ACTION_TYPE => P_ACTION_TYPE
2821 , P_PHONE_SQL => l_phone_sql
2822 , P_ENTRY_SQL => l_first_query_entry_sql
2823 , P_BATCH_SIZE => L_FIRST_BATCH_SIZE
2824 , P_SRC_TYPE_VIEW => l_src_type_view_name
2825 , X_NUM_PENDING => l_transition_entries);
2826
2827 ----------------------------------------------------------------
2828 -- Next Call Tranisition Entries to transition the rest of the
2829 -- entries.
2830 ----------------------------------------------------------------
2831 TRANSITION_ENTRIES( P_SOURCE_ID => P_SOURCE_ID
2832 , P_SERVER_ID => P_SERVER_ID
2833 , P_CAMPAIGN_ID => P_CAMPAIGN_ID
2834 , P_SCHED_ID => P_SCHEDULE_ID
2835 , P_TARGET_GROUP_ID => P_TARGET_GROUP_ID
2836 , P_FROM_SUBSET => P_FROM_SUBSETS(1)
2837 , P_INTO_SUBSET => l_into_subsets(I)
2838 , P_ACTION_TYPE => P_ACTION_TYPE
2839 , P_PHONE_SQL => l_phone_sql
2840 , P_ENTRY_SQL => l_second_query_entry_sql
2841 , P_BATCH_SIZE => L_LAST_BATCH_SIZE
2842 , P_SRC_TYPE_VIEW => l_src_type_view_name
2843 , X_NUM_PENDING => l_transition_entries);
2844
2845 END IF; -- end if subset id <> 0 condition
2846
2847 END LOOP; -- end loop thru into collection to apply views
2848
2849 COMMIT;
2850 EXCEPTION
2851 WHEN FND_API.G_EXC_ERROR THEN
2852 ROLLBACK;
2853 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
2854 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2855 ROLLBACK;
2856 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
2857 WHEN OTHERS THEN
2858 ROLLBACK;
2859 Log( 'Subset_Transition'
2860 , 'MAIN.LIST_' || p_target_group_id
2861 , SQLERRM);
2862 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
2863
2864 END SUBSET_TRANSITION;
2865
2866 -----------------------------++++++-------------------------------
2867 --
2868 -- Start of comments
2869 --
2870 -- API name : TEST_TRANSITION
2871 -- Type : Public
2872 -- Pre-reqs : None
2873 -- Function :
2874 -- Parameters :
2875 --
2876 -- Version : Initial version 1.0
2877 --
2878 -- End of comments
2879 --
2880 -----------------------------++++++-------------------------------
2881 PROCEDURE TEST_TRANSITION
2882 IS
2883 LIST_ID NUMBER;
2884 SUBSET_INTO_TBL SYSTEM.NUMBER_TBL_TYPE;
2885 SUBSET_FROM_TBL SYSTEM.NUMBER_TBL_TYPE;
2886 RETURN_NUM NUMBER;
2887 X_ACTION_ID NUMBER;
2888 BEGIN
2889 LIST_ID := 10606;
2890 SUBSET_INTO_TBL := SYSTEM.NUMBER_TBL_TYPE(10041);
2891 SUBSET_FROM_TBL := SYSTEM.NUMBER_TBL_TYPE(10028);
2892 TRACELOG(TO_CHAR(SYSDATE, 'MM-DD-YYYY:HH24:MI:SS'));
2893 IEC_SUBSET_PVT.SUBSET_TRANSITION ( 1001
2894 , 10115
2895 , 0 -- CAMPAIGN_ID
2896 , 0 -- SCHEDULE_ID
2897 , LIST_ID
2898 , SUBSET_FROM_TBL
2899 , SUBSET_INTO_TBL
2900 , 'N'
2901 , RETURN_NUM
2902 , X_ACTION_ID);
2903 TRACELOG(RETURN_NUM);
2904 TRACELOG(TO_CHAR(SYSDATE, 'MM-DD-YYYY:HH24:MI:SS'));
2905 END TEST_TRANSITION;
2906
2907 END IEC_SUBSET_PVT;
[Home] [Help]