1 PACKAGE BODY IEC_CPN_RLSE_STTGY_PVT AS
2 /* $Header: IECVCRLB.pls 115.55 2004/05/18 19:38:15 minwang ship $ */
3
4 G_CAMPAIGN_ID NUMBER;
5 G_SERVER_ID NUMBER;
6 G_SOURCE_ID CONSTANT NUMBER := -1;
7 -- Sub-Program Unit Declarations
8
9 -- Check if a cpn is active..
10 PROCEDURE Log
11 ( p_method IN VARCHAR2
12 , p_sub_method IN VARCHAR2
13 , p_activity IN VARCHAR2
14 , p_sql_code IN NUMBER
15 , p_sql_errm IN VARCHAR2)
16 IS
17 l_error_msg VARCHAR2(2048);
18 BEGIN
19
20 IEC_OCS_LOG_PVT.LOG_INTERNAL_PLSQL_ERROR
21 ( 'IEC_CPN_RLSE_STTGY_PVT'
22 , p_method
23 , p_sub_method
24 , p_activity
25 , p_sql_code
26 , p_sql_errm
27 , l_error_msg
28 );
29
30 END Log;
31
32
33
34 PROCEDURE GET_UNAVAILABLE_REASON
35 (P_LIST_ID IN NUMBER
36 ,X_CALLBACK_AVAILABLE_COUNT IN OUT NOCOPY NUMBER
37 ,X_AVAILABLE_COUNT IN OUT NOCOPY NUMBER
38 ,X_CALLBACK_CHECKED_OUT_COUNT IN OUT NOCOPY NUMBER
39 ,X_CHECKED_OUT_COUNT IN OUT NOCOPY NUMBER
40 ,X_CALENDAR_COUNT IN OUT NOCOPY NUMBER
41 ,X_CALLBACK_CALENDAR_COUNT IN OUT NOCOPY NUMBER
42 ,X_INACTIVE_COUNT IN OUT NOCOPY NUMBER
43 ,X_CALLBACK_RESTRICT_COUNT IN OUT NOCOPY NUMBER
44 )
45 AS
46
47 L_CALLABLE_FLAG IEC_G_MKTG_ITEM_CC_TZS.CALLABLE_FLAG%TYPE;
48 L_STILL_CALLABLE BINARY_INTEGER;
49 L_CALLBACK_FLAG IEC_G_RETURN_ENTRIES.CALLBACK_FLAG%TYPE;
50 L_CHECKED_OUT_FLAG IEC_G_RETURN_ENTRIES.RECORD_OUT_FLAG%TYPE;
51 L_CALLBACK_EXPIRATION BINARY_INTEGER;
52 L_STATUS_CODE IEC_G_LIST_SUBSETS.STATUS_CODE%TYPE;
53 L_GROUP_COUNT NUMBER;
54
55 L_CALLBACK_AVAILABLE_COUNT NUMBER := 0;
56 L_AVAILABLE_COUNT NUMBER := 0;
57 L_CALLBACK_CHECKED_OUT_COUNT NUMBER := 0;
58 L_CHECKED_OUT_COUNT NUMBER := 0;
59 L_CALENDAR_COUNT NUMBER := 0;
60 L_CALLBACK_CALENDAR_COUNT NUMBER := 0;
61 L_CALLBACK_RESTRICT_COUNT NUMBER := 0;
62 L_INACTIVE_COUNT NUMBER := 0;
63
64
65 CURSOR l_count_cursor(L_LIST_ID NUMBER) IS
66 select b.callable_flag
67 , decode(sign(nvl(b.last_callable_time, SYSDATE) - SYSDATE), 0, 0, 1, 1, 0)
68 , a.callback_flag
69 , decode(sign(nvl(NEXT_CALL_TIME, SYSDATE) - SYSDATE), 0, 0, 1, 1, 0)
70 , a.record_out_flag
71 , c.status_code
72 , count(*)
73 from iec_g_return_entries a
74 , iec_g_mktg_item_Cc_tzs b
75 , iec_g_list_subsets c
76 where a.list_header_id = L_LIST_ID
77 and a.list_header_id = c.list_header_id
78 and a.itm_cc_Tz_id = b.itm_cc_tz_id
79 and a.do_not_use_Flag = 'N'
80 group by b.callable_flag
81 , decode(sign(nvl(b.last_callable_time, SYSDATE) - SYSDATE), 0, 0, 1, 1, 0)
82 , a.callback_flag
83 , decode(sign(nvl(NEXT_CALL_TIME, SYSDATE) - SYSDATE), 0, 0, 1, 1, 0)
84 , a.record_out_flag
85 , c.status_code;
86
87 BEGIN
88
89 BEGIN
90 OPEN l_count_cursor(P_LIST_ID);
91
92 LOOP
93
94 FETCH l_count_cursor
95 INTO L_CALLABLE_FLAG
96 , L_STILL_CALLABLE
97 , L_CALLBACK_FLAG
98 , L_CALLBACK_EXPIRATION
99 , L_CHECKED_OUT_FLAG
100 , L_STATUS_CODE
101 , L_GROUP_COUNT;
102
103 EXIT WHEN l_count_cursor%NOTFOUND;
104
105
106 ----------------------------------------------------------------
107 -- If the group belongs to an inactive subset then all of the
108 -- entries are inactive.
109 ----------------------------------------------------------------
110 IF (L_STATUS_CODE <> 'ACTIVE')
111 THEN
112 L_INACTIVE_COUNT := L_INACTIVE_COUNT + L_GROUP_COUNT;
113
114 ----------------------------------------------------------------
115 -- This is a callback so count against the callback totals.
116 ----------------------------------------------------------------
117 ELSIF (L_CALLBACK_FLAG <> 'N')
118 THEN
119
120 ----------------------------------------------------------------
121 -- These entries are currently checked out.
122 ----------------------------------------------------------------
123 IF (L_CHECKED_OUT_FLAG = 'Y')
124 THEN
125 L_CALLBACK_CHECKED_OUT_COUNT := L_CALLBACK_CHECKED_OUT_COUNT + L_GROUP_COUNT;
126
127 ELSIF (L_CALLABLE_FLAG <> 'Y' OR L_STILL_CALLABLE = 0)
128 THEN
129
130 L_CALLBACK_CALENDAR_COUNT := L_CALLBACK_CALENDAR_COUNT + L_GROUP_COUNT;
131
132 ELSIF (L_CALLBACK_EXPIRATION = 1)
133 THEN
134 L_CALLBACK_RESTRICT_COUNT := L_CALLBACK_RESTRICT_COUNT + L_GROUP_COUNT;
135 ELSE
136
137 L_CALLBACK_AVAILABLE_COUNT := L_CALLBACK_AVAILABLE_COUNT + L_GROUP_COUNT;
138
139 END IF;
140 ----------------------------------------------------------------
141 -- This is not a callback so count against the non-callback totals.
142 ----------------------------------------------------------------
143 ELSE
144
145 ----------------------------------------------------------------
146 -- These entries are currently checked out.
147 ----------------------------------------------------------------
148 IF (L_CHECKED_OUT_FLAG = 'Y')
149 THEN
150 L_CHECKED_OUT_COUNT := L_CHECKED_OUT_COUNT + L_GROUP_COUNT;
151
152 ELSIF (L_CALLABLE_FLAG <> 'Y' OR L_STILL_CALLABLE = 0)
153 THEN
154
155 L_CALENDAR_COUNT := L_CALENDAR_COUNT + L_GROUP_COUNT;
156
157 ELSE
158
159 L_AVAILABLE_COUNT := L_AVAILABLE_COUNT + L_GROUP_COUNT;
160
161 END IF;
162
163 END IF;
164
165 END LOOP;
166
167
168 CLOSE l_count_cursor;
169
170 X_CALLBACK_AVAILABLE_COUNT := L_CALLBACK_AVAILABLE_COUNT ;
171 X_AVAILABLE_COUNT := L_AVAILABLE_COUNT ;
172 X_CALLBACK_CHECKED_OUT_COUNT := L_CALLBACK_CHECKED_OUT_COUNT ;
173 X_CHECKED_OUT_COUNT := L_CHECKED_OUT_COUNT ;
174 X_CALENDAR_COUNT := L_CALENDAR_COUNT ;
175 X_CALLBACK_CALENDAR_COUNT := L_CALLBACK_CALENDAR_COUNT ;
176 X_INACTIVE_COUNT := L_INACTIVE_COUNT ;
177
178
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 RETURN;
182 WHEN OTHERS THEN
183 RAISE;
184 END;
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 END GET_UNAVAILABLE_REASON;
190
191
192 PROCEDURE IS_SCHEDULE_ACTIVE
193 (P_SCHEDULE_ID IN NUMBER
194 ,X_ACTIVE IN OUT NOCOPY VARCHAR2
195 )
196 AS
197 l_schedule_id NUMBER;
198 BEGIN
199
200 -- Return success for now..
201 X_ACTIVE := FND_API.G_RET_STS_ERROR;
202
203 Begin
204 EXECUTE IMMEDIATE 'select unique SCHEDULE_ID ' ||
205 ' from IEC_G_EXECUTING_LISTS_V ' ||
206 ' where SCHEDULE_ID = :1 '
207 INTO l_schedule_id
208 USING P_SCHEDULE_ID;
209
210 X_ACTIVE := FND_API.G_RET_STS_SUCCESS;
211
212 Exception
213 when NO_DATA_FOUND then
214 return;
215 End;
216
217 END IS_SCHEDULE_ACTIVE;
218
219
220 -- Update subset release strategy info
221
222 PROCEDURE UPDATE_SUBSET_RT_INFO
223 (P_CAMPAIGN_ID IN NUMBER
224 ,P_LIST_HEADER_ID IN NUMBER
225 ,P_SUBSET_ID IN NUMBER
226 ,P_QUANTUM IN NUMBER
227 ,P_QUOTA IN NUMBER
228 ,P_QUOTA_RESET IN DATE
229 ,P_USE_FLAG IN VARCHAR2
230 ,X_RESULT IN OUT NOCOPY VARCHAR2
231 )
232 AS
233 PRAGMA AUTONOMOUS_TRANSACTION;
234
235 BEGIN
236
237 EXECUTE IMMEDIATE 'update iec_g_subset_rt_info ' ||
238 'set working_quantum = :1 ' ||
239 ', use_flag = :2 ' ||
240 ', working_quota = :3 ' ||
241 ', quota_reset_time = :4 ' ||
242 ', last_update_date = SYSDATE ' ||
243 'where list_subset_id = :5 '
244 USING P_QUANTUM
245 , P_USE_FLAG
246 , P_QUOTA
247 , P_QUOTA_RESET
248 , P_SUBSET_ID;
249
250 X_RESULT := FND_API.G_RET_STS_SUCCESS;
251 commit;
252
253 END UPDATE_SUBSET_RT_INFO;
254
255 PROCEDURE CHECK_OUT_ENTRIES
256 (P_SERVER_ID IN NUMBER
257 ,P_RETURNS_ID_TAB IN SYSTEM.NUMBER_TBL_TYPE
258 )
259 AS
260
261 ----------------------------------------------------------------
262 -- Bulk Update to check the entries out of AMS_LIST_ENTRIES.
263 -- At first don't specify unique index.
264 ----------------------------------------------------------------
265 BEGIN
266
267 FORALL j IN P_RETURNS_ID_TAB.FIRST..P_RETURNS_ID_TAB.LAST
268 UPDATE IEC_G_RETURN_ENTRIES
269 SET RECORD_OUT_FLAG = 'Y'
270 , CHECKOUT_ACTION_ID = P_SERVER_ID
271 , RECORD_RELEASE_TIME = SYSDATE
272 , LAST_UPDATE_DATE = SYSDATE
273 WHERE RETURNS_ID = P_RETURNS_ID_TAB(j);
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
278 END CHECK_OUT_ENTRIES;
279
280 PROCEDURE GET_CUST_CALLBACKS
281 (P_SERVER_ID IN NUMBER
282 ,P_CAMPAIGN_ID IN NUMBER
283 ,P_SCHEDULE_ID IN NUMBER
284 ,P_LIST_ID IN NUMBER
285 ,P_VIEW_NAME IN VARCHAR2
286 ,P_RLSE_CTRL_ALG_ID IN NUMBER
287 ,X_RETURNS_ID_TAB OUT NOCOPY SYSTEM.NUMBER_TBL_TYPE
288 ,X_RETURN_CODE IN OUT NOCOPY VARCHAR2
289 )
290 AS
291 CURSOR l_callback_query_cursor(L_LIST_ID NUMBER) IS
292 select
293 d.list_entry_id,
294 d.returns_id,
295 c.priority
296 from iec_g_return_entries d
297 , iec_g_list_subsets c
298 where itm_cc_tz_id in
299 ( select itm_cc_tz_id
300 from iec_g_mktg_item_cc_tzs
301 where subset_id in
302 ( select a.list_Subset_id
303 from iec_g_list_Subsets a
304 , iec_g_subset_rt_info b
305 where a.list_header_id = L_LIST_ID
306 and a.list_subset_id = b.list_subset_id
307 and b.working_quota > 0
308 and b.status_code = 'ACTIVE')
309 and nvl(callable_flag, 'Y') <> 'N'
310 and last_callable_time > sysdate)
311 and nvl( callback_flag, 'N') = 'C'
312 and nvl( contact_point_index, 0) > 0
313 and nvl( record_out_flag, 'N') = 'N'
314 and nvl( do_not_use_flag, 'N') = 'N'
315 and pulled_subset_id is null
316 and sysdate > NEXT_CALL_TIME
317 and d.subset_id = c.list_subset_id
318 order by c.priority;
319 l_record_id NUMBER(15);
320 l_index BINARY_INTEGER := 0;
321 l_callback_count BINARY_INTEGER := 0;
322 l_returns_id_tab SYSTEM.NUMBER_TBL_TYPE;
323
324 l_list_entry_id IEC_G_RETURN_ENTRIES.LIST_ENTRY_ID%TYPE;
325 l_subset_priority IEC_G_LIST_SUBSETS.PRIORITY%TYPE;
326 l_returns_id IEC_G_RETURN_ENTRIES.RETURNS_ID%TYPE;
327 l_callable_flag VARCHAR2(1);
328 l_return_code VARCHAR2(1);
329 l_error_code NUMBER;
330
331 BEGIN
332 ---------------------------------------------------------
333 -- Initialize the status string to send back to success.
334 ---------------------------------------------------------
335 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
336
337 l_returns_id_tab := SYSTEM.NUMBER_TBL_TYPE();
338
339 ---------------------------------------------------------
340 -- Check to see if the campaign is locked.
341 ---------------------------------------------------------
342 IEC_COMMON_UTIL_PVT.LOCK_SCHEDULE( P_SOURCE_ID => G_SOURCE_ID
343 , P_SCHED_ID => P_SCHEDULE_ID
344 , P_SERVER_ID => P_SERVER_ID
345 , P_LOCK_ATTEMPTS => 1
346 , P_ATTEMPT_INTERVAL => 0
347 , X_SUCCESS_FLAG => l_return_code);
348
349 IF( l_return_code <> 'Y' )
350 THEN
351 X_RETURN_CODE := SCHEDULE_IS_LOCKED;
352 return;
353 END IF;
354
355 l_index := l_returns_id_tab.COUNT;
356
357 -------------------------------------------------------------------
358 -- First get the customer specified callbacks. We return all of
359 -- these regardless of amount.
360 -------------------------------------------------------------------
361 OPEN l_callback_query_cursor(P_LIST_ID);
362
363 LOOP
364
365 FETCH l_callback_query_cursor
366 INTO l_list_entry_id
367 , l_returns_id
368 , l_subset_priority;
369
370 EXIT WHEN l_callback_query_cursor%NOTFOUND;
371
372 -------------------------------------------------------------------
373 -- Check DNC and Record Filter before adding this entry to
374 -- the list.
375 -------------------------------------------------------------------
376 IEC_DNC_PVT.IS_CALLABLE( G_SOURCE_ID
377 , P_VIEW_NAME
378 , l_list_entry_id
379 , P_LIST_ID
380 , l_returns_id
381 , l_callable_flag);
382
383 IF (l_callable_flag = 'Y')
384 THEN
385
386 -- Check for record filter
387 IF (p_rlse_ctrl_alg_id > 0)
388 THEN
389 IEC_RECORD_FILTER_PVT.Apply_RecordFilter( l_list_entry_id
390 , p_list_id
391 , l_returns_id
392 , p_rlse_ctrl_alg_id
393 , p_view_name
394 , l_callable_flag);
395 END IF;
396
397 IF (l_callable_flag = 'Y')
398 THEN
399 l_callback_count := l_callback_count + 1;
400 l_index := l_index + 1;
401 l_returns_id_tab.EXTEND(1);
402 l_returns_id_tab(l_index) := l_returns_id;
403 END IF;
404
405 END IF;
406
407 END LOOP;
408
409 IF (l_returns_id_tab.COUNT > 0)
410 THEN
411
412 CHECK_OUT_ENTRIES( P_SERVER_ID
413 , l_returns_id_tab);
414 X_RETURNS_ID_TAB := l_returns_id_tab;
415
416 ELSE
417 X_RETURN_CODE := SCHEDULE_IS_EMPTY;
418 END IF;
419
420 CLOSE l_callback_query_cursor;
421
422 -- UNLOCK THE CAMPAIGN
423 IEC_COMMON_UTIL_PVT.UNLOCK_SCHEDULE( P_SOURCE_ID => G_SOURCE_ID
424 , P_SCHED_ID => P_SCHEDULE_ID
425 , P_SERVER_ID => P_SERVER_ID
429 EXCEPTION
426 , X_SUCCESS_FLAG => l_return_code);
427
428 RETURN;
430 WHEN no_data_found THEN
431
432 IF l_callback_query_cursor%ISOPEN
433 THEN
434 CLOSE l_callback_query_cursor;
435 END IF;
436 return;
437 WHEN OTHERS THEN
438 L_ERROR_CODE := SQLCODE;
439 IF l_callback_query_cursor%ISOPEN
440 THEN
441 CLOSE l_callback_query_cursor;
442 END IF;
443
444 raise_application_error
445 ( -20000
446 , 'SQLCODE: <' || L_ERROR_CODE || '> SQLMESSAGE <' || SQLERRM || '>'
447 ,TRUE
448 );
449 END GET_CUST_CALLBACKS;
450
451 PROCEDURE GET_CALLBACKS
452 (P_SERVER_ID IN NUMBER
453 ,P_CAMPAIGN_ID IN NUMBER
454 ,P_SCHEDULE_ID IN NUMBER
455 ,P_LIST_ID IN NUMBER
456 ,P_COUNT IN NUMBER
457 ,P_VIEW_NAME IN VARCHAR2
458 ,P_RLSE_CTRL_ALG_ID IN NUMBER
459 ,X_RETURNS_ID_TAB OUT NOCOPY SYSTEM.NUMBER_TBL_TYPE
460 ,X_RETURN_CODE IN OUT NOCOPY VARCHAR2
461 )
462 AS
463
464 CURSOR l_callback_query_cursor(L_LIST_ID NUMBER) IS
465 select
466 d.list_entry_id,
467 d.returns_id,
468 c.priority
469 from iec_g_return_entries d
470 , iec_g_list_subsets c
471 where itm_cc_tz_id in
472 ( select itm_cc_tz_id
473 from iec_g_mktg_item_cc_tzs
474 where subset_id in
475 ( select a.list_Subset_id
476 from iec_g_list_Subsets a
477 , iec_g_subset_rt_info b
478 where a.list_header_id = L_LIST_ID
479 and a.list_subset_id = b.list_subset_id
480 and b.working_quota > 0
481 and b.status_code = 'ACTIVE')
482 and nvl(callable_flag, 'Y') <> 'N'
483 and last_callable_time > sysdate)
484 and nvl( callback_flag, 'N') = 'Y'
485 and nvl( contact_point_index, 0) > 0
486 and nvl( record_out_flag, 'N') = 'N'
487 and nvl( do_not_use_flag, 'N') = 'N'
488 and pulled_subset_id is null
489 and sysdate > NEXT_CALL_TIME
490 and d.subset_id = c.list_subset_id
491 order by c.priority;
492
493 l_record_id NUMBER(15);
494 l_index BINARY_INTEGER := 0;
495 l_callback_count BINARY_INTEGER := 0;
496 l_returns_id_tab SYSTEM.NUMBER_TBL_TYPE := SYSTEM.NUMBER_TBL_TYPE();
497
498 l_list_entry_id IEC_G_RETURN_ENTRIES.LIST_ENTRY_ID%TYPE;
499 l_subset_priority IEC_G_LIST_SUBSETS.PRIORITY%TYPE;
500 l_returns_id IEC_G_RETURN_ENTRIES.RETURNS_ID%TYPE;
501 l_callable_flag VARCHAR2(1);
502 l_return_code VARCHAR2(1);
503 l_error_code NUMBER;
504
505 BEGIN
506
507 ---------------------------------------------------------
508 -- Initialize the status string to send back to success.
509 ---------------------------------------------------------
510 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
511
512 ---------------------------------------------------------
513 -- Check to see if the campaign is locked.
514 ---------------------------------------------------------
515 IEC_COMMON_UTIL_PVT.LOCK_SCHEDULE( P_SOURCE_ID => G_SOURCE_ID
516 , P_SCHED_ID => P_SCHEDULE_ID
517 , P_SERVER_ID => P_SERVER_ID
518 , P_LOCK_ATTEMPTS => 1
519 , P_ATTEMPT_INTERVAL => 0
520 , X_SUCCESS_FLAG => l_return_code);
521
522 IF( l_return_code <> 'Y' )
523 THEN
524 X_RETURN_CODE := SCHEDULE_IS_LOCKED;
525 return;
526 END IF;
527
528 l_index := l_returns_id_tab.COUNT;
529
530 IF (P_COUNT > 0)
531 THEN
532 OPEN l_callback_query_cursor(P_LIST_ID);
533
534 LOOP
535
536 FETCH l_callback_query_cursor
537 INTO l_list_entry_id
538 , l_returns_id
539 , l_subset_priority;
540
541 EXIT WHEN l_callback_query_cursor%NOTFOUND;
542
543 -------------------------------------------------------------------
544 -- Check DNC and Record Filter before adding this entry to
545 -- the list.
546 -------------------------------------------------------------------
547 IEC_DNC_PVT.IS_CALLABLE( G_SOURCE_ID
548 , P_VIEW_NAME
549 , l_list_entry_id
550 , P_LIST_ID
551 , l_returns_id
552 , l_callable_flag);
553
554 IF (l_callable_flag = 'Y')
555 THEN
556
560 IEC_RECORD_FILTER_PVT.Apply_RecordFilter( l_list_entry_id
557 -- Check for record filter
558 IF (p_rlse_ctrl_alg_id > 0)
559 THEN
561 , p_list_id
562 , l_returns_id
563 , p_rlse_ctrl_alg_id
564 , p_view_name
565 , l_callable_flag);
566 END IF;
567
568 IF (l_callable_flag = 'Y')
569 THEN
570 l_callback_count := l_callback_count + 1;
571 l_index := l_index + 1;
572 l_returns_id_tab.EXTEND(1);
573 l_returns_id_tab(l_index) := l_returns_id;
574 END IF;
575
576 END IF;
577
578 EXIT WHEN l_callback_count >= P_COUNT;
579
580 END LOOP;
581
582 IF (l_returns_id_tab.COUNT > 0)
583 THEN
584
585 CHECK_OUT_ENTRIES( P_SERVER_ID
586 , l_returns_id_tab);
587 X_RETURNS_ID_TAB := l_returns_id_tab;
588
589 ELSE
590 X_RETURN_CODE := SCHEDULE_IS_EMPTY;
591 END IF;
592
593 END IF;
594
595 CLOSE l_callback_query_cursor;
596
597 -- UNLOCK THE CAMPAIGN
598 IEC_COMMON_UTIL_PVT.UNLOCK_SCHEDULE( P_SOURCE_ID => G_SOURCE_ID
599 , P_SCHED_ID => P_SCHEDULE_ID
600 , P_SERVER_ID => P_SERVER_ID
601 , X_SUCCESS_FLAG => l_return_code);
602
603
604 RETURN;
605 EXCEPTION
606 WHEN no_data_found THEN
607
608 IF l_callback_query_cursor%ISOPEN
609 THEN
610 CLOSE l_callback_query_cursor;
611 END IF;
612 return;
613 WHEN OTHERS THEN
614 L_ERROR_CODE := SQLCODE;
615 IF l_callback_query_cursor%ISOPEN
616 THEN
617 CLOSE l_callback_query_cursor;
618 END IF;
619 raise_application_error
620 ( -20000
621 , 'SQLCODE: <' || L_ERROR_CODE || '> SQLMESSAGE <' || SQLERRM || '>'
622 ,TRUE
623 );
624 END GET_CALLBACKS;
625
626 -- Get a working subset
627 PROCEDURE GET_SUBSET_ENTRIES
628 (P_CAMPAIGN_ID IN NUMBER
629 ,P_LIST_HEADER_ID IN NUMBER
630 ,P_SUBSET_ID IN NUMBER
631 ,P_COUNT IN NUMBER
632 ,P_RLSE_CTRL_ALG_ID IN IEC_G_EXECUTING_LISTS_V.RELEASE_CONTROL_ALG_ID%TYPE
633 ,P_VIEW_NAME IN VARCHAR2
634 ,X_RETURN_CODE IN OUT NOCOPY VARCHAR2
635 ,X_RETURNS_ID_TAB IN OUT NOCOPY SYSTEM.NUMBER_TBL_TYPE
636 )
637 AS
638
639 CURSOR l_entry_query_cursor(L_SUBSET_ID NUMBER) IS
640 select /*+ index ( iec_g_return_entries, iec_g_return_entries_n8 ) */ list_entry_id,
641 returns_id
642 from iec_g_return_entries
643 where itm_cc_tz_id in
644 ( select itm_cc_tz_id
645 from iec_g_mktg_item_cc_tzs
646 where subset_id = L_SUBSET_ID
647 and nvl(callable_flag, 'Y') <> 'N'
648 and last_callable_time > sysdate)
649 and nvl( callback_flag, 'N') = 'N'
650 and nvl( contact_point_index, 0) > 0
651 and nvl( record_out_flag, 'N') = 'N'
652 and nvl( do_not_use_flag, 'N') = 'N'
653 and pulled_subset_id is null
654 order by record_release_time asc;
655
656 l_record_id NUMBER := 0;
657 l_list_entry_id NUMBER := 0;
658 l_returns_id NUMBER := 0;
659 l_entry_count BINARY_INTEGER := 0;
660 l_callable_flag VARCHAR2(1);
661 l_index NUMBER;
662
663 BEGIN
664
665 -- Init defaults -
666 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
667 l_index := X_RETURNS_ID_TAB.COUNT;
668
669 OPEN l_entry_query_cursor(P_SUBSET_ID);
670
671 -------------------------------------------------------------------
672 -- Need to change this to fetch one-by-one and apply the dnc and
673 -- record filter.
674 -------------------------------------------------------------------
675 LOOP
676
677 FETCH l_entry_query_cursor
678 INTO l_list_entry_id
679 , l_returns_id;
680
681 EXIT WHEN l_entry_query_cursor%NOTFOUND;
682
683 -------------------------------------------------------------------
684 -- Check DNC and Record Filter before adding this entry to
685 -- the list.
686 -------------------------------------------------------------------
687 IEC_DNC_PVT.IS_CALLABLE( G_SOURCE_ID
688 , P_VIEW_NAME
689 , l_list_entry_id
690 , P_LIST_HEADER_ID
691 , l_returns_id
692 , l_callable_flag);
693
694 IF (l_callable_flag = 'Y')
695 THEN
696
700 IEC_RECORD_FILTER_PVT.Apply_RecordFilter( l_list_entry_id
697 -- Check for record filter
698 IF (p_rlse_ctrl_alg_id > 0)
699 THEN
701 , p_list_header_id
702 , l_returns_id
703 , p_rlse_ctrl_alg_id
704 , p_view_name
705 , l_callable_flag);
706 END IF;
707
708 IF (l_callable_flag = 'Y')
709 THEN
710 l_entry_count := l_entry_count + 1;
711 l_index := l_index + 1;
712 X_RETURNS_ID_TAB.EXTEND(1);
713 X_RETURNS_ID_TAB(l_index) := l_returns_id;
714 END IF;
715
716 END IF;
717
718
719 EXIT WHEN l_entry_count >= P_COUNT;
720
721 END LOOP;
722
723 CLOSE l_entry_query_cursor;
724
725 return;
726 EXCEPTION
727 WHEN no_data_found then
728 return;
729 END GET_SUBSET_ENTRIES;
730
731 PROCEDURE GET_SCHED_ENTRIES
732 (P_CAMPAIGN_ID IN NUMBER
733 ,P_SCHED_ID IN NUMBER
734 ,P_LIST_HEADER_ID IN NUMBER
735 ,P_COUNT IN NUMBER
736 ,P_VIEW_NAME IN VARCHAR2
737 ,P_RLSE_CTRL_ALG_ID IN NUMBER
738 ,X_RETURN_CODE IN OUT NOCOPY VARCHAR2
739 ,X_RETURNS_ID_TAB IN OUT NOCOPY SYSTEM.NUMBER_TBL_TYPE
740 )
741 AS
742
743 l_subset_priority_tbl SUBSET_PRIORITY;
744 l_subset_id_tbl SUBSET_ID;
745 l_working_quantum_tbl WORKING_QUANTUM;
746 l_working_quota_tbl WORKING_QUOTA;
747 l_quantum_tbl QUANTUM;
748 l_quota_tbl QUOTA;
749 l_quota_reset_time_tbl QUOTA_RESET_TIME;
750 l_quota_reset_tbl QUOTA_RESET;
751 l_use_flag_tbl USE_FLAG;
752 l_release_strategy_tbl RELEASE_STRATEGY;
753 l_subset_updated_tbl FLAG_COLLECTION;
754 l_subset_empty_tbl FLAG_COLLECTION;
755 l_entries_released_tbl QUOTA;
756
757 l_reg_returns_id_tab SYSTEM.NUMBER_TBL_TYPE := SYSTEM.NUMBER_TBL_TYPE();
758
759 l_has_records NUMBER(1);
760
761 l_fetch_records NUMBER(10) := 0;
762
763 l_list_count NUMBER := 0;
764 l_subset_count NUMBER := 0;
765 l_callback_count NUMBER := 0;
766
767 l_record_id NUMBER(15);
768 l_entry_index NUMBER := 0;
769
770 -------------------------------------------------------------------
771 -- Physical index is used to store the start of the subsets that
772 -- are assigned the current priority in the subset collection.
773 -------------------------------------------------------------------
774 l_priority_start_index BINARY_INTEGER := 0;
775
776 -------------------------------------------------------------------
777 -- Physical index is used to store the end of the subsets that
778 -- are assigned the current priority in the subset collection.
779 -------------------------------------------------------------------
780 l_priority_end_index BINARY_INTEGER := 0;
781
782 -------------------------------------------------------------------
783 -- Logical index is used to store the subset that had the token
784 -- at the start of the routine for the subsets that
785 -- are assigned the current priority in the subset collection.
786 -------------------------------------------------------------------
787 l_priority_logical_index BINARY_INTEGER := 0;
788 l_priority_current_index BINARY_INTEGER := 0;
789
790 l_subset_index BINARY_INTEGER := 0;
791
792 l_current_priority BINARY_INTEGER := 0;
793 l_current_priority_count BINARY_INTEGER := 0;
794
795 l_additional_entries_in_pri BOOLEAN := FALSE;
796 l_additional_entries_in_list BOOLEAN := FALSE;
797 l_restriction_encountered BOOLEAN := FALSE;
798 l_disregard_restriction BOOLEAN := FALSE;
799 l_priority_transition BOOLEAN := TRUE;
800 l_subset_transition BOOLEAN := FALSE;
801 l_priority_token_found BOOLEAN := FALSE;
802
803 BEGIN
804 l_has_records := -1;
805
806 -- Init defaults -
807 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
808 l_list_count := P_COUNT;
809 l_entry_index := X_RETURNS_ID_TAB.COUNT;
810
811 -------------------------------------------------------------------
812 -- Get the subsets for the highest priority.
813 -------------------------------------------------------------------
814 SELECT a.list_subset_id
815 , a.priority
816 , a.release_strategy
817 , b.working_quantum
818 , b.working_quota
819 , a.quota
820 , a.quantum
821 , b.quota_reset_time
822 , b.use_flag
823 , a.quota_reset
824 BULK COLLECT INTO l_subset_id_tbl
825 , l_subset_priority_tbl
826 , l_release_strategy_tbl
827 , l_working_quantum_tbl
828 , l_working_quota_tbl
829 , l_quota_tbl
830 , l_quantum_tbl
831 , l_quota_reset_time_tbl
832 , l_use_flag_tbl
833 , l_quota_reset_tbl
834 FROM iec_g_list_subsets a
835 , iec_g_subset_rt_info b
839 AND b.callable_flag = 'Y'
836 WHERE a.list_header_id = P_LIST_HEADER_ID
837 AND a.list_subset_id = b.list_subset_id
838 AND b.valid_flag = 'Y'
840 AND b.STATUS_CODE = 'ACTIVE'
841 ORDER BY a.priority, a.list_subset_id;
842
843 -------------------------------------------------------------------
844 -- Create two more collections for storing information on each
845 -- subset as to whether it was updated and if it contains
846 -- callable records.
847 -------------------------------------------------------------------
848 FOR j in 1 .. l_subset_id_tbl.COUNT
849 LOOP
850 l_subset_updated_tbl(j) := 'Y';
851 l_subset_empty_tbl(j) := 'N';
852 l_entries_released_tbl(j) := 0;
853 END LOOP;
854
855 -------------------------------------------------------------------
856 -- While we still have entries to fulfill.
857 -------------------------------------------------------------------
858 WHILE l_list_count > 0
859 LOOP
860 -------------------------------------------------------------------
861 -- Initialize the collection used to retrieve subset entries.
862 -------------------------------------------------------------------
863 l_reg_returns_id_tab.DELETE;
864
865 -------------------------------------------------------------------
866 -- If we have switched priorities then we need to locate the
867 -- subset that currently owns the token to start fetching records
868 -- from for that priority. If none of the subsets owns the token
869 -- then pick the first one. We also will locate the index in
870 -- the collection that marks the final subset with this priority
871 -- and get a count of how many subsets are in this priority.
872 -------------------------------------------------------------------
873 IF l_priority_transition = TRUE
874 THEN
875
876 l_priority_transition := FALSE;
877 l_priority_token_found := FALSE;
878
879 -------------------------------------------------------------------
880 -- Check to ensure that the current priority is not the last
881 -- priority in the collection.
882 -------------------------------------------------------------------
883 IF l_priority_end_index >= l_subset_priority_tbl.COUNT
884 THEN
885
886 -------------------------------------------------------------------
887 -- Since the routine has been thru the entire collection of priorities
888 -- assigned to this list and we didn't come across any subsets that
889 -- still had entries then just return with what we have.
890 -------------------------------------------------------------------
891 IF l_additional_entries_in_list = FALSE
892 THEN
893
894 -------------------------------------------------------------------
895 -- If this list is set for quota and some subsets were turned off
896 -- due to quota restrictions then remove restrictions and try
897 -- again.
898 -------------------------------------------------------------------
899 IF l_restriction_encountered = TRUE
900 THEN
901 l_additional_entries_in_pri := FALSE;
902 l_additional_entries_in_list := FALSE;
903 l_restriction_encountered := FALSE;
904 l_disregard_restriction := TRUE;
905 l_priority_end_index := 0;
906 ELSE
907 EXIT;
908 END IF;
909
910 -------------------------------------------------------------------
911 -- If there are still entries in the list then return to the first priority and
912 -- reinitialize the list_index, additional_entries_in_pri, and
913 -- priority_index variable. We might need to set the working quantum
914 -- here if we change to the next list. Also need to look at the use
915 -- flag (the priority token).
916 -------------------------------------------------------------------
917 ELSE
918 l_additional_entries_in_pri := FALSE;
919 l_additional_entries_in_list := FALSE;
920 l_priority_end_index := 0;
921 END IF;
922
923 END IF;
924
925 -------------------------------------------------------------------
926 -- Reinitialize the priority indexes.
927 -------------------------------------------------------------------
928 l_priority_start_index := l_priority_end_index + 1;
929 l_priority_end_index := 0;
930 l_priority_logical_index := l_priority_start_index;
931 l_priority_current_index := l_priority_start_index;
932 l_current_priority := l_subset_priority_tbl(l_priority_start_index);
933 l_current_priority_count := 1;
934
935 -------------------------------------------------------------------
936 -- Continue looping thru collection until we locate the last
937 -- subset in the collection that belongs to this priority.
938 -------------------------------------------------------------------
939 WHILE l_priority_end_index = 0
940 LOOP
941
942 -------------------------------------------------------------------
943 -- Found the subset in the priority has the token so continue
947 THEN
944 -- on and set the priority transition flag to FALSE.
945 -------------------------------------------------------------------
946 IF l_use_flag_tbl(l_priority_current_index) = 'Y'
948 l_priority_logical_index := l_priority_current_index;
949 l_priority_token_found := TRUE;
950 END IF;
951
952 -------------------------------------------------------------------
953 -- If the current index is equal to the last entry in the collection
954 -- then we can assume that we have checked all of the subsets in
955 -- this priority and make the appropriate assignments for
956 -- this priority.
957 -------------------------------------------------------------------
958 IF l_priority_current_index < l_subset_priority_tbl.COUNT
959 THEN
960
961 -------------------------------------------------------------------
962 -- If the priority on the next subset in the collection indicates
963 -- that it is belongs to the same priority as the previous subset
964 -- then increment the subset index.
965 -------------------------------------------------------------------
966 IF l_subset_priority_tbl(l_priority_current_index + 1) = l_current_priority
967 THEN
968 l_priority_current_index := l_priority_current_index + 1;
969 l_current_priority_count := l_current_priority_count + 1;
970
971 -------------------------------------------------------------------
972 -- If the priority on the next subset in the collection indicates
973 -- that it belongs to a different priority as the previous subset
974 -- then give the token to the first subset in priority.
975 -------------------------------------------------------------------
976 ELSE
977 IF l_priority_token_found = FALSE
978 THEN
979 l_subset_index := l_priority_start_index;
980 l_use_flag_tbl(l_priority_start_index) := 'Y';
981 ELSE
982 l_subset_index := l_priority_logical_index;
983 l_use_flag_tbl(l_priority_logical_index) := 'Y';
984 END IF;
985 l_priority_end_index := l_priority_current_index;
986 END IF;
987
988 -------------------------------------------------------------------
989 -- If there are no more subsets to check then
990 -- set the end index for this priority.
991 -------------------------------------------------------------------
992 ELSE
993 l_subset_index := l_priority_logical_index;
994 l_use_flag_tbl(l_priority_logical_index) := 'Y';
995 l_priority_end_index := l_priority_current_index;
996 END IF;
997
998 END LOOP;
999
1000 END IF; -- Priority transition conditional
1001
1002 -------------------------------------------------------------------
1003 -- If this subset has already been visited and determined that no
1004 -- entries could be fetched from it, then don't try again.
1005 -------------------------------------------------------------------
1006 IF l_subset_empty_tbl(l_subset_index) = 'N'
1007 THEN
1008
1009 -------------------------------------------------------------------
1010 -- If quantum strategy then use working quantum only.
1011 -------------------------------------------------------------------
1012 IF l_release_strategy_tbl(l_subset_index) = QUANTUM_RLSE_STTGY
1013 THEN
1014
1015 -------------------------------------------------------------------
1016 -- Determines the number to try and retrieve. If there is only
1017 -- one subset in this priority then try to fulfill the number
1018 -- requested on this list using just the single subset. Otherwise
1019 -- if requested is greater than working quantum on current subset
1020 -- then retrieve current subset otherwise return requested.
1021 -------------------------------------------------------------------
1022 IF l_current_priority_count = 1
1023 THEN
1024 l_subset_count := l_list_count;
1025 ELSE
1026 IF l_working_quantum_tbl(l_subset_index) < l_list_count
1027 THEN
1028 l_subset_count := l_working_quantum_tbl(l_subset_index);
1029 ELSE
1030 l_subset_count := l_list_count;
1031 END IF;
1032 END IF;
1033 -------------------------------------------------------------------
1034 -- If quota strategy then check working quota as well.
1035 -------------------------------------------------------------------
1036 ELSIF l_release_strategy_tbl(l_subset_index) = QUOTA_RLSE_STTGY
1037 THEN
1038
1039 -------------------------------------------------------------------
1040 -- First check to see if the quota reset time has been reached. If
1041 -- it has we then need to update the quota reset time to the next
1042 -- time.
1043 -------------------------------------------------------------------
1047 IF (l_quota_reset_time_tbl(l_subset_index) + (l_quota_reset_tbl(l_subset_index) / 1440)) > SYSDATE
1044 IF l_quota_reset_time_tbl(l_subset_index) <= SYSDATE
1045 THEN
1046 l_working_quota_tbl(l_subset_index) := l_quota_tbl(l_subset_index);
1048 THEN
1049 l_quota_reset_time_tbl(l_subset_index) := l_quota_reset_time_tbl(l_subset_index) + (l_quota_reset_tbl(l_subset_index) / 1440);
1050 ELSE
1051 l_quota_reset_time_tbl(l_subset_index) := SYSDATE + (l_quota_reset_tbl(l_subset_index) / 1440);
1052 END IF;
1053 END IF;
1054
1055 -------------------------------------------------------------------
1056 -- The quota on this subset has been reached but the reset time has
1057 -- not expired therefore we move on to the next subset. This disregards
1058 -- priorities. The only reason we would pull from this subset at
1059 -- this time is if there are no other subsets that have quota left.
1060 -------------------------------------------------------------------
1061 IF (l_working_quota_tbl(l_subset_index) = 0 OR l_entries_released_tbl(l_subset_index) >= l_working_quota_tbl(l_subset_index))
1062 AND l_disregard_restriction = FALSE
1063 THEN
1064 l_restriction_encountered := TRUE;
1065 l_subset_count := 0;
1066
1067 ELSE
1068
1069 -------------------------------------------------------------------
1070 -- If the quota is greater than zero then we release according to
1071 -- the quantum. This could cause some issues with quota release
1072 -- strategy because the dial server now could contain 100 entries
1073 -- from this list even though the quota only is 1. If the one
1074 -- is reached then currently we have no means to flush out the
1075 -- entries that are currently in the dial server.
1076 -------------------------------------------------------------------
1077
1078 -------------------------------------------------------------------
1079 -- This strategy will only release maximum the remaining quota number
1080 -- of entries or the remaining quantum which ever is smaller.
1081 -------------------------------------------------------------------
1082 IF l_working_quota_tbl(l_subset_index) < l_list_count AND l_disregard_restriction = FALSE
1083 THEN
1084 IF l_working_quantum_tbl(l_subset_index) < l_working_quota_tbl(l_subset_index)
1085 THEN
1086 l_subset_count := l_working_quantum_tbl(l_subset_index);
1087 ELSE
1088 l_subset_count := l_working_quota_tbl(l_subset_index);
1089 END IF;
1090 ELSE
1091 IF l_current_priority_count = 1
1092 THEN
1093 l_subset_count := l_list_count;
1094 ELSE
1095 IF l_working_quantum_tbl(l_subset_index) < l_list_count
1096 THEN
1097 l_subset_count := l_working_quantum_tbl(l_subset_index);
1098 ELSE
1099 l_subset_count := l_list_count;
1100 END IF;
1101 END IF;
1102 END IF;
1103 END IF;
1104 END IF;
1105
1106 IF l_subset_count > 0
1107 THEN
1108 get_subset_entries( P_CAMPAIGN_ID => P_CAMPAIGN_ID
1109 , P_LIST_HEADER_ID => P_LIST_HEADER_ID
1110 , P_SUBSET_ID => l_subset_id_tbl(l_subset_index)
1111 , P_COUNT => l_subset_count
1112 , P_RLSE_CTRL_ALG_ID => P_RLSE_CTRL_ALG_ID
1113 , P_VIEW_NAME => P_VIEW_NAME
1114 , X_RETURN_CODE => X_RETURN_CODE
1115 , X_RETURNS_ID_TAB => l_reg_RETURNS_ID_TAB );
1116
1117 IF ( X_RETURN_CODE <> FND_API.G_RET_STS_SUCCESS )
1118 THEN
1119 exit;
1120 END IF;
1121
1122 -------------------------------------------------------------------
1123 -- If entries are returned then add the returned entries to
1124 -- the collection returned for the schedule as a whole.
1125 -------------------------------------------------------------------
1126 IF (l_reg_returns_id_tab.COUNT > 0)
1127 THEN
1128
1129 IF (X_RETURNS_ID_TAB.COUNT = 0)
1130 THEN
1131 l_entry_index := 0;
1132 ELSE
1133 l_entry_index := X_RETURNS_ID_TAB.COUNT;
1134 END IF;
1135
1136 FOR M in l_reg_returns_id_tab.FIRST .. l_reg_returns_id_tab.LAST
1137 LOOP
1138 l_entry_index := l_entry_index + 1;
1139 X_RETURNS_ID_TAB.EXTEND(1);
1140 X_RETURNS_ID_TAB(l_entry_index) := l_reg_returns_id_tab(M);
1141 END LOOP;
1142
1143 CHECK_OUT_ENTRIES( G_SERVER_ID
1144 , l_reg_returns_id_tab);
1145
1146 l_entries_released_tbl(l_subset_index) := l_entries_released_tbl(l_subset_index) + l_reg_returns_id_tab.COUNT;
1147
1148 END IF; -- IF ENTRIES WERE RETURNED
1149
1153 -- time. Determine if the process needs to continue to the next
1150 -------------------------------------------------------------------
1151 -- If the number returned is less then the number requested then
1152 -- the subset has no more callable records to contribute at this
1154 -- subset or not.
1155 -------------------------------------------------------------------
1156 IF (l_reg_returns_id_tab.COUNT < l_subset_count)
1157 THEN
1158 l_subset_transition := TRUE;
1159 l_subset_empty_tbl(l_subset_index) := 'Y';
1160 ELSE
1161 l_subset_empty_tbl(l_subset_index) := 'N';
1162 l_additional_entries_in_pri := TRUE;
1163 l_additional_entries_in_list := TRUE;
1164 IF l_reg_returns_id_tab.COUNT < l_list_count
1165 THEN
1166 l_subset_transition := TRUE;
1167 ELSE
1168 IF l_working_quantum_tbl(l_subset_index) <= l_reg_returns_id_tab.COUNT
1169 THEN
1170 l_subset_transition := TRUE;
1171 END IF;
1172 END IF;
1173 END IF;
1174
1175 l_list_count := l_list_count - l_reg_returns_id_tab.COUNT;
1176
1177 ELSE
1178 l_subset_transition := TRUE;
1179 END IF; -- IF SUBSET COUNT > 0
1180 ELSE
1181 l_subset_transition := TRUE;
1182 END IF; -- IF SUBSET EMPTY CONDITIONAL.
1183
1184 -------------------------------------------------------------------
1185 -- The next section determines if the next subset to attempt to
1186 -- retrieve entries from is assigned the same priority as the
1187 -- current subset.
1188 -------------------------------------------------------------------
1189 IF l_subset_index = l_priority_end_index
1190 THEN
1191 l_priority_current_index := l_priority_start_index;
1192 ELSE
1193 l_priority_current_index := l_subset_index + 1;
1194 END IF;
1195
1196 -------------------------------------------------------------------
1197 -- We have gone thru all of the subsets for this priority once.
1198 -------------------------------------------------------------------
1199 IF l_priority_current_index = l_priority_logical_index
1200 THEN
1201
1202 -------------------------------------------------------------------
1203 -- We have alredy went thru the priority once so reset the fetch
1204 -- token as well as the additional entries in priority flag.
1205 -------------------------------------------------------------------
1206 IF l_subset_Transition = TRUE
1207 THEN
1208 l_working_quantum_tbl(l_subset_index) := l_quantum_tbl(l_subset_index);
1209 l_use_flag_tbl(l_subset_index) := 'N';
1210 l_use_flag_tbl(l_priority_current_index) := 'Y';
1211 ELSE
1212 l_working_quantum_tbl(l_subset_index) := l_working_quantum_tbl(l_subset_index) - l_reg_returns_id_tab.COUNT;
1213 END IF;
1214
1215 -------------------------------------------------------------------
1216 -- If there are additional entries in the current priority then
1217 -- stay with this priority otherwise move to the next priority.
1218 -------------------------------------------------------------------
1219 IF l_additional_entries_in_pri = TRUE
1220 THEN
1221 l_subset_index := l_priority_logical_index;
1222 l_priority_transition := FALSE;
1223 ELSE
1224 l_subset_index := l_priority_end_index;
1225 l_priority_transition := TRUE;
1226 END IF;
1227 l_additional_entries_in_pri := FALSE;
1228
1229 -------------------------------------------------------------------
1230 -- Haven't gone thru the priority so move to next subset in
1231 -- priority.
1232 -------------------------------------------------------------------
1233 ELSE
1234 l_priority_transition := FALSE;
1235 IF l_subset_Transition = TRUE
1236 THEN
1237 l_working_quantum_tbl(l_subset_index) := l_quantum_tbl(l_subset_index);
1238 l_use_flag_tbl(l_subset_index) := 'N';
1239 l_use_flag_tbl(l_priority_current_index) := 'Y';
1240 ELSE
1241 l_working_quantum_tbl(l_subset_index) := l_working_quantum_tbl(l_subset_index) - l_reg_returns_id_tab.COUNT;
1242 END IF;
1243 l_subset_index := l_priority_current_index;
1244 END IF;
1245
1246 l_subset_transition := FALSE;
1247
1248 END LOOP; -- list loop
1249
1250 -------------------------------------------------------------------
1251 -- Loop thru the subsets and update rt info when necessary.
1252 -------------------------------------------------------------------
1253 FOR j in 1 .. l_subset_id_tbl.COUNT
1254 LOOP
1255 IF l_subset_updated_tbl(j) = 'Y'
1256 THEN
1257 UPDATE_SUBSET_RT_INFO( P_CAMPAIGN_ID => P_CAMPAIGN_ID
1258 , P_LIST_HEADER_ID => P_LIST_HEADER_ID
1259 , P_SUBSET_ID => l_subset_id_tbl(j)
1260 , P_QUANTUM => l_working_quantum_tbl(j)
1261 , P_QUOTA => l_working_quota_tbl(j)
1262 , P_QUOTA_RESET => l_quota_reset_time_tbl(j)
1266 END LOOP;
1263 , P_USE_FLAG => l_use_flag_tbl(j)
1264 , X_RESULT => X_RETURN_CODE);
1265 END IF;
1267
1268 EXCEPTION
1269 WHEN no_data_found then
1270 return;
1271 END GET_SCHED_ENTRIES;
1272
1273 -- Get the records.
1274 PROCEDURE GET_RECORDS
1275 (P_SERVER_ID IN NUMBER
1276 ,P_CAMPAIGN_ID IN NUMBER
1277 ,P_SCHED_ID IN NUMBER
1278 ,P_TARGET_GROUP_ID IN NUMBER
1279 ,P_COUNT IN NUMBER
1280 ,P_VIEW_NAME IN VARCHAR2
1281 ,P_RLSE_CTRL_ALG_ID IN NUMBER
1282 ,X_CACHE_RECORDS OUT NOCOPY SYSTEM.NUMBER_TBL_TYPE
1283 ,X_RETURN_CODE OUT NOCOPY VARCHAR2
1284 )
1285 AS
1286
1287 l_result_code varchar2( 1 );
1288 l_count NUMBER := 0;
1289 l_returns_id_tab SYSTEM.NUMBER_TBL_TYPE := SYSTEM.NUMBER_TBL_TYPE();
1290 l_return_code VARCHAR2(1);
1291 L_CALLBACK_AVAILABLE_COUNT NUMBER := 0;
1292 L_AVAILABLE_COUNT NUMBER := 0;
1293 L_CALLBACK_CHECKED_OUT_COUNT NUMBER := 0;
1294 L_CHECKED_OUT_COUNT NUMBER := 0;
1295 L_CALENDAR_COUNT NUMBER := 0;
1296 L_CALLBACK_CALENDAR_COUNT NUMBER := 0;
1297 L_INACTIVE_COUNT NUMBER := 0;
1298 L_CALLBACK_RESTRICT_COUNT NUMBER := 0;
1299 L_ERROR_CODE NUMBER := 0;
1300
1301 BEGIN
1302 G_CAMPAIGN_ID := P_CAMPAIGN_ID;
1303 G_SERVER_ID := P_SERVER_ID;
1304
1305 ---------------------------------------------------------
1306 -- Initialize the status string to send back to success.
1307 ---------------------------------------------------------
1308 X_RETURN_CODE := FND_API.G_RET_STS_SUCCESS;
1309
1310 ---------------------------------------------------------
1311 -- Make sure all parameters are passed in.
1312 ---------------------------------------------------------
1313 IF( ( P_SERVER_ID is null )
1314 OR( P_CAMPAIGN_ID is null )
1315 OR( P_SCHED_ID is null )
1316 OR( P_TARGET_GROUP_ID is null )
1317 OR( P_COUNT is null )
1318 OR( P_VIEW_NAME is null)
1319 )
1320 THEN
1321 raise_application_error
1322 ( -20000
1323 , 'P_SERVER_ID , P_CAMPAIGN_ID cannot be null.'
1324 || 'Values sent are Server id (' || P_SERVER_ID || ')'
1325 || ' Campaign id (' || P_CAMPAIGN_ID || ')'
1326 || ' Count (' || P_COUNT || ')'
1327 ,TRUE
1328 );
1329 END IF;
1330
1331 ---------------------------------------------------------
1332 -- Check to make sure the campaign is active.
1333 ---------------------------------------------------------
1334 IS_SCHEDULE_ACTIVE( P_SCHED_ID
1335 , l_return_code );
1336
1337 IF( l_return_code <> FND_API.G_RET_STS_SUCCESS )
1338 THEN
1339 X_RETURN_CODE := SCHEDULE_IS_NOT_ACTIVE;
1340 return;
1341 END IF;
1342
1343 ---------------------------------------------------------
1344 -- Check to see if the campaign is locked.
1345 ---------------------------------------------------------
1346 IEC_COMMON_UTIL_PVT.LOCK_SCHEDULE( P_SOURCE_ID => G_SOURCE_ID
1347 , P_SCHED_ID => P_SCHED_ID
1348 , P_SERVER_ID => P_SERVER_ID
1349 , P_LOCK_ATTEMPTS => 1
1350 , P_ATTEMPT_INTERVAL => 0
1351 , X_SUCCESS_FLAG => l_return_code);
1352
1353 IF( l_return_code <> 'Y' )
1354 THEN
1355 X_RETURN_CODE := SCHEDULE_IS_LOCKED;
1356 return;
1357 END IF;
1358
1359 ---------------------------------------------------------
1360 -- Move the desired customer count to a local variable.
1361 ---------------------------------------------------------
1362 l_count := P_COUNT;
1363
1364 ---------------------------------------------------------
1365 -- Procedure to return the entries for this schedule.
1366 ---------------------------------------------------------
1367 GET_SCHED_ENTRIES( P_CAMPAIGN_ID => P_CAMPAIGN_ID
1368 , P_SCHED_ID => P_SCHED_ID
1369 , P_LIST_HEADER_ID => P_TARGET_GROUP_ID
1370 , P_COUNT => l_COUNT
1371 , P_VIEW_NAME => P_VIEW_NAME
1372 , P_RLSE_CTRL_ALG_ID => P_RLSE_CTRL_ALG_ID
1373 , X_RETURN_CODE => l_result_code
1374 , X_RETURNS_ID_TAB => l_returns_id_tab );
1375
1376 if( l_result_code <> FND_API.G_RET_STS_SUCCESS )
1377 then
1378 IEC_COMMON_UTIL_PVT.UNLOCK_SCHEDULE( P_SOURCE_ID => G_SOURCE_ID
1379 , P_SCHED_ID => P_SCHED_ID
1380 , P_SERVER_ID => P_SERVER_ID
1381 , X_SUCCESS_FLAG => l_return_code);
1382 X_RETURN_CODE := SCHEDULE_INTERNAL_ERROR;
1383 return;
1384 end if;
1385
1386 if( l_returns_id_tab.count <= 0 )
1387 then
1388 IEC_COMMON_UTIL_PVT.UNLOCK_SCHEDULE( P_SOURCE_ID => G_SOURCE_ID
1389 , P_SCHED_ID => P_SCHED_ID
1390 , P_SERVER_ID => P_SERVER_ID
1391 , X_SUCCESS_FLAG => l_return_code);
1392
1393 ---------------------------------------------------------
1394 -- At this point try to determine why we could
1398 -- (3) All are checked out.
1395 -- not get any more entries:
1396 -- (1) Have all of the customers been serviced?
1397 -- (2) Calendar issue.
1399 ---------------------------------------------------------
1400 GET_UNAVAILABLE_REASON
1401 (P_LIST_ID => P_TARGET_GROUP_ID
1402 ,X_CALLBACK_AVAILABLE_COUNT => L_CALLBACK_AVAILABLE_COUNT
1403 ,X_AVAILABLE_COUNT => L_AVAILABLE_COUNT
1404 ,X_CALLBACK_CHECKED_OUT_COUNT => L_CALLBACK_CHECKED_OUT_COUNT
1405 ,X_CHECKED_OUT_COUNT => L_CHECKED_OUT_COUNT
1406 ,X_CALENDAR_COUNT => L_CALENDAR_COUNT
1407 ,X_CALLBACK_CALENDAR_COUNT => L_CALLBACK_CALENDAR_COUNT
1408 ,X_INACTIVE_COUNT => L_INACTIVE_COUNT
1409 ,X_CALLBACK_RESTRICT_COUNT => L_CALLBACK_RESTRICT_COUNT);
1410
1411 ---------------------------------------------------------
1412 -- This means that there are no records currently available
1413 -- so we will try to give a detailed reason why.
1414 ---------------------------------------------------------
1415 IF (L_AVAILABLE_COUNT = 0 AND L_CALLBACK_AVAILABLE_COUNT = 0)
1416 THEN
1417
1418 ---------------------------------------------------------
1419 -- Unless we can find any other reason, the schedule
1420 -- is thought to be exhausted.
1421 ---------------------------------------------------------
1422 X_RETURN_CODE := SCHEDULE_IS_EMPTY;
1423
1424 ---------------------------------------------------------
1425 -- Check to see if there are entries already checked out.
1426 ---------------------------------------------------------
1427 IF (L_CALLBACK_CHECKED_OUT_COUNT > 0 OR L_CHECKED_OUT_COUNT > 0)
1428 THEN
1429 X_RETURN_CODE := SCHEDULE_ALL_CHECKED_OUT;
1430 END IF;
1431
1432 ---------------------------------------------------------
1433 -- Check to see if there are calendar restrictions.
1434 ---------------------------------------------------------
1435 IF (L_CALLBACK_CALENDAR_COUNT > 0 OR L_CALENDAR_COUNT > 0)
1436 THEN
1437
1438 IF (X_RETURN_CODE = SCHEDULE_ALL_CHECKED_OUT)
1439 THEN
1440 X_RETURN_CODE := SCHEDULE_CALENDAR_OUT;
1441 ELSE
1442 X_RETURN_CODE := SCHEDULE_CALENDAR_RESTRICTION;
1443 END IF;
1444 END IF;
1445
1446 ---------------------------------------------------------
1447 -- Check to see if there are callback restrictions.
1448 ---------------------------------------------------------
1449 IF (L_CALLBACK_RESTRICT_COUNT > 0 )
1450 THEN
1451
1452 IF (X_RETURN_CODE = SCHEDULE_ALL_CHECKED_OUT)
1453 THEN
1454 X_RETURN_CODE := SCHEDULE_CALLBACK_OUT;
1455 ELSIF (X_RETURN_CODE = SCHEDULE_CALENDAR_RESTRICTION)
1456 THEN
1457 X_RETURN_CODE := SCHEDULE_CALENDAR_CALLBACK;
1458 ELSIF (X_RETURN_CODE = SCHEDULE_CALENDAR_OUT)
1459 THEN
1460 X_RETURN_CODE := SCHEDULE_CALENDAR_CALLBACK_OUT;
1461 ELSE
1462 X_RETURN_CODE := SCHEDULE_CALLBACK_EXPIRATION;
1463 END IF;
1464 END IF;
1465
1466 END IF;
1467
1468 RETURN;
1469 end if;
1470
1471 -- UNLOCK THE CAMPAIGN
1472 IEC_COMMON_UTIL_PVT.UNLOCK_SCHEDULE( P_SOURCE_ID => G_SOURCE_ID
1473 , P_SCHED_ID => P_SCHED_ID
1474 , P_SERVER_ID => P_SERVER_ID
1475 , X_SUCCESS_FLAG => l_return_code);
1476
1477 X_CACHE_RECORDS := l_returns_id_tab;
1478
1479 return;
1480 EXCEPTION
1481 WHEN OTHERS THEN
1482
1483 L_ERROR_CODE := SQLCODE;
1484 Log( 'GET_RECORDS'
1485 , 'UNKNOWN'
1486 , 'Retrieving records for campaign ' || p_campaign_id
1487 , SQLCODE
1488 , SQLERRM);
1489
1490
1491 raise_application_error
1492 ( -20000
1493 , 'SQLCODE: <' || L_ERROR_CODE || '> SQLMESSAGE <' || SQLERRM || '>'
1494 ,TRUE
1495 );
1496
1497 RAISE;
1498
1499 END GET_RECORDS;
1500
1501 END IEC_CPN_RLSE_STTGY_PVT;
[Home] [Help]