1 PACKAGE BODY AMS_TCOP_PREVIEW AS
2 /* $Header: amsvtcpb.pls 120.14 2006/05/24 00:18:32 batoleti ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_TCOP_PREVIEW
7 -- Purpose
8 --
9 -- This package contains all the program units for traffic cop preview
10 --
11 -- History
12 --
13 -- NOTE
14 --
15 -- End of Comments
16 -- ===============================================================
17
18 -- Declare some of the Types
19 TYPE DATE_TABLE IS TABLE of DATE INDEX BY BINARY_INTEGER;
20 TYPE NUMBER_TABLE IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
21
22 -- Declare some of the Global Variables
23
24 -- Nested Tables
25 G_PARTY_TGROUP_LIST JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
26 G_PARTY_LIST JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
27 G_FATIGUE_PARTY_LIST JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
28 G_FATIGUE_BY_PARTY_LIST JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
29 G_FATIGUE_BY_SCHEDULE_LIST JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
30
31 -- Index By Tables
32 G_PRVW_DATE_LIST DATE_TABLE;
33
34 -- Scalar variables
35 G_PRVW_REQUEST_ID NUMBER;
36 G_ACTIVITY_ID NUMBER;
37 G_SCHEDULE_ID NUMBER;
38 G_GLOBAL_MAX_CONTACT NUMBER :=0;
39 G_GLOBAL_NO_OF_DAYS NUMBER:=0;
40 G_CHANNEL_MAX_CONTACT NUMBER:=0;
41 G_CHANNEL_NO_OF_DAYS NUMBER:=0;
42 G_NO_SCHEDULE_PIPELINE BOOLEAN := FALSE;
43 G_PREVIEW_START_DATE DATE;
44 G_PREVIEW_END_DATE DATE;
45
46 --Global Variable for the Period
47 G_GLOBAL_NO_OF_PERIOD NUMBER;
48
49
50 -- Global Constants
51 LOG_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
52 -- Private procedure to write debug message to FND_LOG table
53 PROCEDURE write_debug_message(p_log_level NUMBER,
54 p_procedure_name VARCHAR2,
55 p_label VARCHAR2,
56 p_text VARCHAR2
57 )
58 IS
59 l_module_name VARCHAR2(400);
60 DELIMETER CONSTANT VARCHAR2(1) := '.';
61 LABEL_PREFIX CONSTANT VARCHAR2(15) := 'WFScheduleExec';
62
63 BEGIN
64 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
65 -- Set the Module Name
66 l_module_name := 'ams'||DELIMETER||'plsql'||DELIMETER||G_PACKAGE_NAME||DELIMETER||p_procedure_name||DELIMETER||LABEL_PREFIX||'-'||p_label;
67
68
69 -- Log the Message
70 AMS_UTILITY_PVT.debug_message(p_log_level,
71 l_module_name,
72 p_text
73 );
74 END IF;
75
76
77
78 --dbms_output.put_line(l_module_name||': '||p_text);
79
80 END write_debug_message;
81 -- ===============================================================
82 -- Start of Comments
83 -- Name
84 -- CREATE_PREVIEW_REQUEST
85 --
86 -- Purpose
87 -- This procedure creates a preview request
88 --
89
90 PROCEDURE CREATE_PREVIEW_REQUEST( p_list_header_id IN NUMBER
91 ,p_total_preview_size IN NUMBER
92 )
93 IS
94 -- Cursor to get the next sequence number
95 CURSOR C_GET_PRVW_RQST_ID
96 IS
97 SELECT ams_tcop_prvw_requests_s.nextval
98 FROM DUAL;
99
100
101 -- Use Autonomous Transaction to commit the data
102 PRAGMA AUTONOMOUS_TRANSACTION;
103 BEGIN
104
105 -- Get the next sequence number
106 OPEN C_GET_PRVW_RQST_ID;
107 FETCH C_GET_PRVW_RQST_ID
108 INTO G_PRVW_REQUEST_ID;
109 CLOSE C_GET_PRVW_RQST_ID;
110
111 IF (G_GLOBAL_NO_OF_PERIOD IS NULL ) THEN
112 G_GLOBAL_NO_OF_PERIOD := 7;
113 END IF;
114
115 -- Create a row in AMS_TCOP_PRVW_REQUESTS
116
117
118
119 INSERT INTO
120 AMS_TCOP_PRVW_REQUESTS
121 (
122 REQUEST_ID,
123 GENERATION_DATE,
124 STATUS,
125 LIST_HEADER_ID,
126 TOTAL_PREVIEW_SIZE,
127 CREATION_DATE,
128 CREATED_BY,
129 LAST_UPDATE_DATE,
130 LAST_UPDATED_BY,
131 LAST_UPDATE_LOGIN,
132 OBJECT_VERSION_NUMBER,
133 PROJECTED_FATIGUE_PERIOD
134 )
135 VALUES
136 (
137 G_PRVW_REQUEST_ID,
138 sysdate,
139 'NEW',
140 p_list_header_id,
141 p_total_preview_size,
142 sysdate,
143 FND_GLOBAL.USER_ID,
144 sysdate,
145 FND_GLOBAL.USER_ID,
146 FND_GLOBAL.USER_ID,
147 1,
148 G_GLOBAL_NO_OF_PERIOD
149 );
150 commit;
151 END CREATE_PREVIEW_REQUEST;
152 -- ===============================================================
153 -- Start of Comments
154 -- Name
155 -- PREVIEW_FATIGUE
156 --
157 -- Purpose
158 -- This procedure does preview projection for the target group
159 -- specified by the list_header_id
160 --
161 PROCEDURE CALCULATE_PREVIEW_DATE_RANGE(p_start_date IN Date,p_camp_end_date IN Date)
162 IS
163 l_prvw_start_date date;
164 l_num_days_between NUMBER;
165 l_prvw_days NUMBER;
166 l_fatigue_start_date DATE;
167 l_fatigue_end_date DATE;
168 l_temp_fatigue_start_date DATE;
169 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'CALCULATE_PREVIEW_DATE_RANGE';
170 BEGIN
171
172 IF (G_GLOBAL_NO_OF_PERIOD IS NULL) THEN
173 G_GLOBAL_NO_OF_PERIOD := 7;
174 END IF;
175 G_PRVW_DATE_LIST.DELETE;
176 IF (trunc(p_start_date) <= trunc(sysdate)) THEN
177 l_fatigue_start_date := trunc(sysdate);
178 ELSIF (trunc(p_start_date) > trunc(sysdate)) THEN
179 l_temp_fatigue_start_date := trunc(p_start_date - G_GLOBAL_NO_OF_PERIOD);
180
181 IF (trunc(l_temp_fatigue_start_date) <= trunc(sysdate)) THEN
182 l_fatigue_start_date := trunc(sysdate);
183 ELSE
184 l_fatigue_start_date := l_temp_fatigue_start_date;
185 END IF;
186 END IF;
187
188
189 l_fatigue_end_date := trunc(p_start_date + G_GLOBAL_NO_OF_PERIOD);
190 IF (trunc(l_fatigue_end_date) >= trunc(p_camp_end_date)) THEN
191 l_fatigue_end_date := trunc(p_camp_end_date);
192 END IF;
193
194 IF (trunc(l_fatigue_end_date) >= trunc(sysdate) OR trunc(p_camp_end_date) >= trunc(sysdate)) THEN
195 l_num_days_between := trunc(l_fatigue_end_date - l_fatigue_start_date);
196 FOR i in 1 .. l_num_days_between+1
197 LOOP
198 G_PRVW_DATE_LIST(i) := l_fatigue_start_date + (i -1);
199 END LOOP;
200 END IF;
201
202
203 -- Print the Date Range
204 For i in G_PRVW_DATE_LIST.FIRST .. G_PRVW_DATE_LIST.LAST
205 LOOP
206
207 write_debug_message(LOG_LEVEL_EVENT,
208 PROCEDURE_NAME,
209 'PRINT_DATE_RANGE',
210 'Date '||to_char(i)||' ='||to_char(G_PRVW_DATE_LIST(i))
211 );
212
213 END LOOP;
214
215 END CALCULATE_PREVIEW_DATE_RANGE;
216 -- ===============================================================
217 -- Start of Comments
218 -- Name
219 -- APPEND_GLOBAL_FATIGUE_LIST
220 --
221 -- Purpose
222 -- Append the given list to the global list
223 --
224 PROCEDURE APPEND_GLOBAL_FATIGUE_LIST(p_fatigue_party_list JTF_NUMBER_TABLE,
225 p_fatigue_by_party_list JTF_NUMBER_TABLE,
226 p_fatigue_by_schedule_list JTF_NUMBER_TABLE
227 )
228 IS
229
230 l_global_count NUMBER;
231 l_input_count NUMBER;
232 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'APPEND_GLOBAL_FATIGUE_LIST';
233 BEGIN
234 write_debug_message(LOG_LEVEL_EVENT,
235 PROCEDURE_NAME,
236 'INPUT_PARTY_LIST_NEEDS_TO_BE_COPIED',
237 'Total number of entries in the input fatigue party list = '||to_char(p_fatigue_party_list.count)
238 );
239
240
241 write_debug_message(LOG_LEVEL_EVENT,
242 PROCEDURE_NAME,
243 'INPUT_ENTRY_LIST_NEEDS_TO_BE_COPIED',
244 'Total number of ids in the input fatigue entry list = '||to_char(p_fatigue_by_party_list.count)
245 );
246
247
248 write_debug_message(LOG_LEVEL_EVENT,
249 PROCEDURE_NAME,
250 'INPUT_FATIGUE_BY_SCHEDULE_LIST_NEEDS_TO_BE_COPIED',
251 'Total number of fatigue by schedules in the input list = '||to_char(p_fatigue_by_schedule_list.count)
252 );
253
254
255
256 -- Append the G_FATIGUE_PARTY_LIST
257 l_input_count := p_FATIGUE_PARTY_LIST.COUNT;
258 IF (l_input_count > 0) THEN
259 l_global_count := G_FATIGUE_PARTY_LIST.COUNT;
260 write_debug_message(LOG_LEVEL_EVENT,
261 PROCEDURE_NAME,
262 'GLOBAL_FATIGUE_ENTRY_LIST_BEFORE_COPY',
263 'Total number of entries in the global fatigue party list = '||to_char(l_global_count)
264 );
265
266 FOR i in p_fatigue_party_list.FIRST .. p_fatigue_party_list.LAST
267 LOOP
268 G_FATIGUE_PARTY_LIST.EXTEND;
269 G_FATIGUE_PARTY_LIST(l_global_count + 1) := p_fatigue_party_list(i);
270 l_global_count := l_global_count + 1;
271 END LOOP;
272
273 write_debug_message(LOG_LEVEL_EVENT,
274 PROCEDURE_NAME,
275 'GLOBAL_FATIGUE_PARTY_ENTRY_LIST_AFTER_COPY',
276 'After copying Total number of entries in the global fatigue party list = '||to_char(G_FATIGUE_PARTY_LIST.COUNT)
277 );
278 END IF;
279
280 -- Append the G_FATIGUE_BY_PARTY_LIST
281 l_input_count := p_FATIGUE_BY_PARTY_LIST.COUNT;
282 IF (l_input_count > 0) THEN
283 l_global_count := G_FATIGUE_BY_PARTY_LIST.COUNT;
284 write_debug_message(LOG_LEVEL_EVENT,
285 PROCEDURE_NAME,
286 'GLOBAL_FATIGUE_BY_ENTRY_LIST_BEFORE_COPY',
287 'Before copying Total number of entries in the global fatigue by list = '||to_char(l_global_count)
288 );
289
290
291 FOR i in p_fatigue_by_party_list.FIRST .. p_fatigue_by_party_list.LAST
292 LOOP
293 G_FATIGUE_BY_PARTY_LIST.EXTEND;
294 G_FATIGUE_BY_PARTY_LIST(l_global_count + 1) := p_fatigue_by_party_list(i);
295 l_global_count := l_global_count + 1;
296 END LOOP;
297
298 write_debug_message(LOG_LEVEL_EVENT,
299 PROCEDURE_NAME,
300 'GLOBAL_FATIGUE_BY_ENTRY_LIST_AFTER_COPY',
301 'After copying Total number of entries in the global fatigue by list = '||to_char(G_FATIGUE_BY_PARTY_LIST.count)
302 );
303 END IF;
304
305 -- Append the G_FATIGUE_BY_SCHEDULE_LIST
306 l_input_count := p_FATIGUE_BY_SCHEDULE_LIST.COUNT;
307 IF (l_input_count > 0) THEN
308 l_global_count := G_FATIGUE_BY_SCHEDULE_LIST.COUNT;
309 write_debug_message(LOG_LEVEL_EVENT,
310 PROCEDURE_NAME,
311 'GLOBAL_FATIGUE_BY_SCHEDULE_LIST_BEFORE_COPY',
312 'Before copying Total number of entries in the global fatigue by schedule list = '||to_char(l_global_count)
313 );
314
315 FOR i in p_fatigue_by_schedule_list.FIRST .. p_fatigue_by_schedule_list.LAST
316 LOOP
317 G_FATIGUE_BY_SCHEDULE_LIST.EXTEND;
318 G_FATIGUE_BY_SCHEDULE_LIST(l_global_count + 1) := p_fatigue_by_schedule_list(i);
319 l_global_count := l_global_count + 1;
320 END LOOP;
321
322 write_debug_message(LOG_LEVEL_EVENT,
323 PROCEDURE_NAME,
324 'GLOBAL_FATIGUE_BY_SCHEDULE_LIST_AFTER_COPY',
325 'After copying Total number of entries in the global fatigue by schedule list = '||to_char(l_global_count)
326 );
327 END IF;
328
329 END APPEND_GLOBAL_FATIGUE_LIST;
330 -- ===============================================================
331 -- Start of Comments
332 -- Name
333 -- DELETE_FROM_LIST
334 --
335 -- Purpose
336 -- Now, simulate future contact. Basically as if the overlapping
337 PROCEDURE DELETE_FROM_LIST(p_delete_from_list IN OUT NOCOPY jtf_number_table,
338 p_delete_list IN jtf_number_table
339 )
340 IS
341
342 l_temp_list jtf_number_table := jtf_number_table();
343 l_temp_idx NUMBER;
344 l_temp_num NUMBER;
345
346 BEGIN
347 IF (p_delete_from_list.count > 0 and p_delete_list.count > 0) THEN
348 FOR i in p_delete_list.first .. p_delete_list.last
349 LOOP
350 IF (p_delete_from_list.count > 0 ) THEN
351 FOR j in p_delete_from_list.first .. p_delete_from_list.last
352 LOOP
353 IF (p_delete_from_list.exists(j)) THEN
354 IF ((p_delete_list(i) = p_delete_from_list(j))
355 ) THEN
356 p_delete_from_list.delete(j);
357 END IF;
358 END IF;
359 END LOOP;
360 END IF;
361 END LOOP;
362 END IF;
363
364 l_temp_idx := 0;
365 IF (p_delete_from_list.count > 0)
366 THEN
370 l_temp_num := p_delete_from_list(j);
367 FOR j in p_delete_from_list.first .. p_delete_from_list.last
368 LOOP
369 BEGIN
371 l_temp_idx := l_temp_idx + 1;
372 l_temp_list.extend;
373 l_temp_list(l_temp_idx) := l_temp_num;
374
375 EXCEPTION
376 WHEN NO_DATA_FOUND THEN null;
377 END;
378 END LOOP;
379 END IF;
380
381 p_delete_from_list := l_temp_list;
382
383 END DELETE_FROM_LIST;
384 -- ===============================================================
385 -- Start of Comments
386 -- Name
387 -- SIMULATE_FUTURE_CONTACT
388 --
389 -- Purpose
390 -- Now, simulate future contact. Basically as if the overlapping
391 -- target group members are being contacted by the schedules in the pipeline
392 --
393 PROCEDURE SIMULATE_FUTURE_CONTACTS(p_preview_start_date IN DATE
394 ,p_preview_end_date IN DATE
395 ,p_list_header_id IN NUMBER
396 )
397 IS
398 -- Cursor to get schedules in pipeline
399 /* Reverted changes for Bugfix: 4261272. Fix for SQL repository issue: 11756057 */
400 /* Bug Fix 4990567: SQL ID 14423512. A new index AMS_CAMPAIGN_SCHEDULES_B_N13 was created */
401 /* -- batoleti commented this cursor.
402 CURSOR C_GET_SCHEDULE_IN_PIPELINE
403 IS
404 SELECT CSCH.SCHEDULE_ID,CSCH.ACTIVITY_ID,CSCH.START_DATE_TIME
405 FROM AMS_CAMPAIGN_SCHEDULES_B CSCH,
406 AMS_ACT_LISTS ACT_LIST,
407 AMS_LIST_HEADERS_ALL LIST_HEADER
408 WHERE CSCH.START_DATE_TIME BETWEEN
409 p_preview_start_date AND p_preview_end_date
410 AND CSCH.STATUS_CODE in ('AVAILABLE','ACTIVE')
411 AND CSCH.SCHEDULE_ID <> G_SCHEDULE_ID
412 AND ACT_LIST.LIST_USED_BY = 'CSCH'
413 AND ACT_LIST.LIST_USED_BY_ID = CSCH.SCHEDULE_ID
414 AND ACT_LIST.LIST_HEADER_ID = LIST_HEADER.LIST_HEADER_ID
415 AND ACT_LIST.LIST_ACT_TYPE = 'TARGET'
416 AND LIST_HEADER.APPLY_TRAFFIC_COP = 'Y'
417 AND EXISTS
418 (SELECT LIST_ENTRY1.PARTY_ID
419 FROM AMS_LIST_ENTRIES LIST_ENTRY1
420 ,AMS_LIST_ENTRIES LIST_ENTRY2
421 WHERE LIST_HEADER.LIST_HEADER_ID = LIST_ENTRY1.LIST_HEADER_ID
422 AND LIST_ENTRY1.PARTY_ID = LIST_ENTRY2.PARTY_ID
423 AND LIST_ENTRY2.LIST_HEADER_ID = p_list_header_id
424 )
425 ORDER BY CSCH.START_DATE_TIME
426 ;
427 */
428 -- batoleti uncommented this cursor...
429 -- batoleti Ref. bug# 5234351... added leading(ACT_LIST) hint to the below query.
430 CURSOR C_GET_SCHEDULE_IN_PIPELINE
431 IS
432 SELECT /*+ leading(ACT_LIST) */ CSCH.SCHEDULE_ID,CSCH.ACTIVITY_ID,CSCH.START_DATE_TIME
433 FROM AMS_CAMPAIGN_SCHEDULES_B CSCH,
434 AMS_ACT_LISTS ACT_LIST,
435 AMS_LIST_HEADERS_ALL LIST_HEADER
436 WHERE TRUNC(CSCH.START_DATE_TIME) BETWEEN
437 p_preview_start_date AND p_preview_end_date
438 AND CSCH.STATUS_CODE in ('AVAILABLE','ACTIVE')
439 AND CSCH.SCHEDULE_ID <> G_SCHEDULE_ID
440 AND ACT_LIST.LIST_USED_BY = 'CSCH'
441 AND ACT_LIST.LIST_USED_BY_ID = CSCH.SCHEDULE_ID
442 AND ACT_LIST.LIST_HEADER_ID = LIST_HEADER.LIST_HEADER_ID
443 AND ACT_LIST.LIST_ACT_TYPE = 'TARGET'
444 AND LIST_HEADER.APPLY_TRAFFIC_COP = 'Y'
445 AND EXISTS
446 (SELECT LIST_ENTRY1.PARTY_ID
447 FROM AMS_LIST_ENTRIES LIST_ENTRY1
448 ,AMS_LIST_ENTRIES LIST_ENTRY2
449 WHERE LIST_HEADER.LIST_HEADER_ID = LIST_ENTRY1.LIST_HEADER_ID
450 AND LIST_ENTRY1.PARTY_ID = LIST_ENTRY2.PARTY_ID
451 AND LIST_ENTRY2.LIST_HEADER_ID = p_list_header_id
452 )
453 ORDER BY CSCH.START_DATE_TIME
454 ;
455
456 /* End Bugfix: 4990567. */
457 -- Get overlapping target group members for this schedule
458 CURSOR C_GET_OVERLAPPING_MEMBER(p_schedule_id NUMBER)
459 IS
460 SELECT LIST_ENTRY1.PARTY_ID
461 FROM AMS_LIST_ENTRIES LIST_ENTRY1
462 ,AMS_LIST_ENTRIES LIST_ENTRY2
463 ,AMS_CAMPAIGN_SCHEDULES_B CSCH
464 ,AMS_ACT_LISTS ACT_LIST
465 ,AMS_LIST_HEADERS_ALL LIST_HEADER
466 WHERE LIST_ENTRY1.LIST_HEADER_ID = p_list_header_id
467 AND LIST_ENTRY2.PARTY_ID = LIST_ENTRY1.PARTY_ID
468 AND LIST_ENTRY2.LIST_HEADER_ID = LIST_HEADER.LIST_HEADER_ID
472 AND ACT_LIST.LIST_ACT_TYPE='TARGET'
469 AND ACT_LIST.LIST_USED_BY = 'CSCH'
470 AND ACT_LIST.LIST_USED_BY_ID = CSCH.SCHEDULE_ID
471 AND CSCH.SCHEDULE_ID = p_SCHEDULE_ID
473 AND ACT_LIST.LIST_HEADER_ID = LIST_HEADER.LIST_HEADER_ID;
474
475 CURSOR C_GET_RULE_DTLS(p_activity_id NUMBER)
476 IS
477 SELECT rule.rule_id rule_id
478 ,rule.rule_type rule_type
479 ,period.no_of_days no_of_days
480 ,rule.max_contact_allowed max_contact_allowed
481 FROM ams_tcop_fr_rules_setup rule,
482 ams_tcop_fr_periods_b period
483 WHERE rule.ENABLED_FLAG = 'Y'
484 AND (rule.CHANNEL_ID is null
485 OR (rule.CHANNEL_ID = p_activity_id) )
486 AND rule.RULE_TYPE in ('GLOBAL' , 'CHANNEL_BASED')
487 AND rule.PERIOD_ID = period.PERIOD_ID
488 ORDER BY (rule.MAX_CONTACT_ALLOWED * period.NO_OF_DAYS);
489
490 CURSOR C_GET_GLOBAL_FATIGUE_LIST(p_no_of_days NUMBER,
491 p_preview_date Date,
492 p_contact_party_list JTF_NUMBER_TABLE
493 )
494 IS
495 SELECT /*+ leading(party_list) +*/
496 CONTACT.PARTY_ID, COUNT(CONTACT.PARTY_ID)
497 FROM AMS_TCOP_CONTACTS CONTACT,
498 (SELECT COLUMN_VALUE PARTY_ID
499 FROM TABLE(CAST(p_contact_party_list as JTF_NUMBER_TABLE))
500 ) party_list
501 WHERE CONTACT.PARTY_ID = PARTY_LIST.PARTY_ID
502 AND CONTACT_DATE BETWEEN TRUNC(p_preview_date - p_no_of_days)
503 AND TRUNC(p_preview_date)
504 GROUP BY CONTACT.PARTY_ID;
505
506
507 CURSOR C_GET_GLBL_SIMULATED_FTG_LIST(p_no_of_days NUMBER,
508 p_preview_date Date,
509 p_contact_party_list JTF_NUMBER_TABLE
510 )
511 IS
512 SELECT /*+ leading(party_list) +*/
513 CONTACT.PARTY_ID, COUNT(CONTACT.PARTY_ID)
514 FROM AMS_TCOP_PRVW_CONTACTS CONTACT,
515 (SELECT COLUMN_VALUE PARTY_ID
516 FROM TABLE(CAST(p_contact_party_list as JTF_NUMBER_TABLE))
517 ) party_list
518 WHERE CONTACT.PARTY_ID = PARTY_LIST.PARTY_ID
519 AND CONTACT_DATE BETWEEN TRUNC(p_preview_date - p_no_of_days)
520 AND TRUNC(p_preview_date)
521 GROUP BY CONTACT.PARTY_ID;
522
523 CURSOR C_GET_CHANNEL_FATIGUE_LIST(p_activity_id NUMBER,
524 p_no_of_days NUMBER,
525 p_preview_date Date,
526 p_contact_party_list JTF_NUMBER_TABLE
527 )
528 IS
529 SELECT /*+ leading(party_list) +*/
530 CONTACT.PARTY_ID,
531 COUNT(CONTACT.PARTY_ID)
532 FROM AMS_TCOP_CONTACTS CONTACT,
533 (SELECT COLUMN_VALUE PARTY_ID
534 FROM TABLE(CAST(p_contact_party_list as JTF_NUMBER_TABLE))
535 ) party_list
536 WHERE CONTACT.PARTY_ID = PARTY_LIST.PARTY_ID
537 AND CONTACT.MEDIA_ID = p_activity_id
538 AND CONTACT_DATE BETWEEN TRUNC(p_preview_date - p_no_of_days)
539 AND TRUNC(p_preview_date)
540 GROUP BY CONTACT.PARTY_ID;
541
542 CURSOR C_GET_CHNL_SIMULATED_FTG_LIST(p_activity_id NUMBER,
543 p_no_of_days NUMBER,
544 p_preview_date Date,
545 p_contact_party_list JTF_NUMBER_TABLE
546 )
547 IS
548 SELECT /*+ leading(party_list) +*/
549 CONTACT.PARTY_ID, COUNT(CONTACT.PARTY_ID)
550 FROM AMS_TCOP_PRVW_CONTACTS CONTACT,
551 AMS_CAMPAIGN_SCHEDULES_B CSCH,
552 (SELECT COLUMN_VALUE PARTY_ID
553 FROM TABLE(CAST(p_contact_party_list as JTF_NUMBER_TABLE))
554 ) party_list
555 WHERE CONTACT.PARTY_ID = PARTY_LIST.PARTY_ID
556 AND CSCH.SCHEDULE_ID = CONTACT.SCHEDULE_ID
557 AND CSCH.ACTIVITY_ID = p_activity_id
558 AND CONTACT_DATE BETWEEN TRUNC(p_preview_date - p_no_of_days)
559 AND TRUNC(p_preview_date)
560 GROUP BY CONTACT.PARTY_ID;
561
562 CURSOR C_CHECK_PARTY_IN_CONTACT_LIST(p_contact_party_list JTF_NUMBER_TABLE
563 , p_party_id NUMBER
564 )
565 IS
566 SELECT party_list.party_id
567 FROM
568 (SELECT column_value party_id
569 FROM TABLE(CAST(p_contact_party_list as JTF_NUMBER_TABLE))
570 ) party_list
571 WHERE party_list.party_id=p_party_id;
572
573 CURSOR C_GET_NEXT_PRVW_CONTACT
574 IS
575 SELECT AMS_TCOP_PRVW_CONTACTS_S.NEXTVAL
576 FROM DUAL;
577
578
579 l_schedule_id_list NUMBER_TABLE;
580 l_contact_id_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
584 l_contact_party_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
581 l_schedule_activity_id_list NUMBER_TABLE;
582 l_schedule_start_date_list DATE_TABLE;
583
585 l_fatigue_party_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
586 l_global_contact_party_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
587 l_global_contact_count_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
588 l_channel_contact_party_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
589 l_channel_contact_count_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
590 l_temp_contact_party_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
591 l_temp_contact_count_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
592
593 l_fatigue_index NUMBER;
594 l_contact_index NUMBER;
595 l_global_contact_count NUMBER;
596 l_channel_contact_count NUMBER;
597 l_party_id NUMBER;
598 l_schedule_id NUMBER;
599 l_contact_id NUMBER;
600 l_contact_date DATE;
601
602 -- List of Constants
603 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'SIMULATE_FUTURE_CONTACTS';
604
605
606 BEGIN
607 -- Get the list of schedules with the following attributes
608 -- 1. The schedule start date is between preview start date and preview end date
609 -- 2. The schedule status is 'Available' and 'Active'
610 -- 3. There is an overlap with the Target Group members of these schedules
611 -- and the schedule being previewed
612 OPEN C_GET_SCHEDULE_IN_PIPELINE;
613 FETCH C_GET_SCHEDULE_IN_PIPELINE
614 BULK COLLECT
615 INTO l_schedule_id_list,
616 l_schedule_activity_id_list,
617 l_schedule_start_date_list;
618 CLOSE C_GET_SCHEDULE_IN_PIPELINE;
619
620 IF (l_schedule_id_list.COUNT > 0) THEN
621 -- batoleti : The G_NO_SCHEDULE_PIPELINE will be used in calculate_fatigue procedure.
622 G_NO_SCHEDULE_PIPELINE:= FALSE;
623 write_debug_message(LOG_LEVEL_EVENT,
624 PROCEDURE_NAME,
625 'SCHEDULES_IN_PIPELINE_EXIST',
626 'Total Number of Schedules in Pipeline ='||to_char(l_schedule_id_list.count)
627 );
628
629 FOR i IN l_schedule_id_list.FIRST .. l_schedule_id_list.LAST
630 LOOP
631 l_schedule_id := l_schedule_id_list(i);
632 l_contact_date := l_schedule_start_date_list(i);
633
634 write_debug_message(LOG_LEVEL_EVENT,
635 PROCEDURE_NAME,
636 'PROCESS_EACH_SCHEDULE_IN_PIPELINE',
637 'Schedule '||to_char(i)||' Id='||to_char(l_schedule_id) ||' ,Start Date ='||to_char(l_contact_date)||' and Activity Id ='||to_char(l_schedule_activity_id_list(i))
638 );
639 -- calculate fatigue and contacts for this schedule
640 -- Get the list of parties which will be fatigued
641 -- First, Get the List of OverLapping Target Group Members
642 OPEN C_GET_OVERLAPPING_MEMBER(l_schedule_id_list(i));
643 FETCH C_GET_OVERLAPPING_MEMBER
644 BULK COLLECT
645 INTO l_contact_party_list;
646 CLOSE C_GET_OVERLAPPING_MEMBER;
647
648 IF (l_contact_party_list.count > 0) THEN
649
650 write_debug_message(LOG_LEVEL_EVENT,
651 PROCEDURE_NAME,
652 'CHECK_OVERLAPPING_MEMBER',
653 'Overlapping Target Group Member Count = '||to_char(l_contact_party_list.count)
654 );
655
656 FOR C1 IN C_GET_RULE_DTLS(l_schedule_activity_id_list(i))
657 LOOP
658 -- Apply Global Fatigue Rules
659 IF (C1.RULE_TYPE='GLOBAL') THEN
660 -- Check if the already executed schedules influence the fatigue
661 IF (trunc(l_schedule_start_date_list(i) - C1.no_of_days)
662 <= trunc(sysdate))
663 THEN
664
665 OPEN C_GET_GLOBAL_FATIGUE_LIST
666 (C1.NO_OF_DAYS,
667 l_schedule_start_date_list(i),
668 l_contact_party_list
669 );
670
671 FETCH C_GET_GLOBAL_FATIGUE_LIST
672 BULK COLLECT
673 INTO l_temp_contact_party_list,
674 l_temp_contact_count_list;
675 CLOSE C_GET_GLOBAL_FATIGUE_LIST;
676
677 IF (l_temp_contact_party_list.count > 0) THEN
678 -- Check which parties have exceeded the contact count
679 FOR i in l_temp_contact_party_list.FIRST ..
680 l_temp_contact_party_list.LAST
681 LOOP
682 IF (l_temp_contact_count_list(i) >=
683 C1.max_contact_allowed) THEN
684 -- this is a fatigue entry, add to the fatigue list
685 l_fatigue_index := l_fatigue_party_list.count + 1;
686 l_fatigue_party_list.extend;
690 -- this is not a fatigue entry, update the contact list
687 l_fatigue_party_list(l_fatigue_index) :=
688 l_temp_contact_party_list(i);
689 ELSE
691 l_contact_index := l_global_contact_party_list.count + 1;
692 l_global_contact_party_list.extend;
693 l_global_contact_count_list.extend;
694
695 l_global_contact_party_list(l_contact_index) :=
696 l_temp_contact_party_list(i);
697 l_global_contact_count_list(l_contact_index) :=
698 l_temp_contact_count_list(i);
699 END IF;
700
701 END LOOP;
702 END IF;
703
704 l_global_contact_count := l_global_contact_count_list.COUNT;
705
706 IF (l_fatigue_party_list.count > 0 ) THEN
707
708 -- Remove from the Contact List the list of parties
709 -- already fatigued by the Global Rule
710 DELETE_FROM_LIST(l_contact_party_list,
711 l_fatigue_party_list);
712
713 END IF;
714
715 -- Reset the list variable since it will be reused
716 l_fatigue_party_list.delete;
717
718 END IF;
719
720 -- Now, get the number of contacts made using the
721 -- Simulated Contact information
722
723 OPEN C_GET_GLBL_SIMULATED_FTG_LIST
724 (C1.NO_OF_DAYS,
725 l_schedule_start_date_list(i),
726 l_contact_party_list
727 );
728 FETCH C_GET_GLBL_SIMULATED_FTG_LIST
729 BULK COLLECT
730 INTO l_temp_contact_party_list,
731 l_temp_contact_count_list;
732 CLOSE C_GET_GLBL_SIMULATED_FTG_LIST;
733
734 IF (l_temp_contact_party_list.count > 0) THEN
735 -- Check which parties have exceeded the contact count
736 FOR i in l_temp_contact_party_list.FIRST ..
737 l_temp_contact_party_list.LAST
738 LOOP
739 IF (l_temp_contact_count_list(i) >=
740 C1.max_contact_allowed) THEN
741 -- this is a fatigue entry, add to the fatigue list
742 l_fatigue_index := l_fatigue_party_list.count + 1;
743 l_fatigue_party_list.extend;
744 l_fatigue_party_list(l_fatigue_index) :=
745 l_temp_contact_party_list(i);
746 ELSE
747 -- this is not a fatigue entry
748 -- First, check if this entry exists in the previously
749 -- created global contact list.
750 IF (l_global_contact_count > 0 ) THEN
751 -- if it exists then there is a chance that
752 -- total contacts made in the past and future
753 -- will exceed the max contact allowed
754
755 l_contact_index := l_global_contact_count + 1;
756
757 OPEN C_CHECK_PARTY_IN_CONTACT_LIST(
758 l_global_contact_party_list,
759 l_temp_contact_party_list(i)
760 );
761 FETCH C_CHECK_PARTY_IN_CONTACT_LIST
762 INTO l_party_id;
763 CLOSE C_CHECK_PARTY_IN_CONTACT_LIST;
764
765 -- This Party is already part of the contact list
766 IF (l_party_id is not null) THEN
767 -- Loop through the entries to find the index
768 -- ,get the contact count
769 -- Finally, update the contact count in the global list
770 IF (l_global_contact_party_list.count > 0) THEN
771 FOR j in l_global_contact_party_list.FIRST ..
772 l_global_contact_party_list.LAST
773 LOOP
774 IF (l_global_contact_party_list(j) = l_party_id) THEN
775 -- Retrieve the contact count at that index
776 l_global_contact_count_list(j) :=
777 l_temp_contact_count_list(i) +
778 l_global_contact_count_list(j);
779
780 END IF;
781
785 ELSE
782 END LOOP;
783 END IF;
784
786 l_global_contact_party_list.extend;
787 l_global_contact_count_list.extend;
788
789 l_global_contact_party_list(l_contact_index) :=
790 l_temp_contact_party_list(i);
791 l_global_contact_count_list(l_contact_index) :=
792 l_temp_contact_count_list(i);
793
794
795 END IF;
796
797 ELSE
798
799 l_global_contact_party_list.extend;
800 l_global_contact_count_list.extend;
801
802 l_global_contact_party_list(l_contact_index) :=
803 l_temp_contact_party_list(i);
804 l_global_contact_count_list(l_contact_index) :=
805 l_temp_contact_count_list(i);
806 END IF;
807
808 END IF;
809
810 END LOOP; /* loop through l_temp_contact_count_list */
811 END IF;
812
813 IF (l_global_contact_party_list.count > 0) THEN
814
815 -- Once again loop through the l_global_contact_party_list to
816 -- eliminate fatigue entries
817 FOR i in l_global_contact_party_list.FIRST ..
818 l_global_contact_party_list.LAST
819 LOOP
820 IF (l_global_contact_count_list(i) >= C1.max_contact_allowed)
821 THEN
822 l_fatigue_index := l_fatigue_party_list.count;
823 l_fatigue_party_list.extend;
824 l_fatigue_party_list(l_fatigue_index + 1) :=
825 l_global_contact_party_list(i);
826
827 END IF;
828 END LOOP;
829 END IF;
830
831 IF (l_fatigue_party_list.count > 0) THEN
832 -- Once again, delete fatigue entries from the contact
833 -- list
834 DELETE_FROM_LIST(l_contact_party_list,
835 l_fatigue_party_list
836 );
837
838 l_fatigue_party_list.delete;
839 END IF;
840
841 -- Reset some of the Nested Tables which will be reused
842 l_temp_contact_party_list.delete;
843 l_temp_contact_count_list.delete;
844 l_global_contact_party_list.delete;
845 l_global_contact_count_list.delete;
846
847
848 ELSE
849 -- Check fatigue using the channel rule
850
851 -- Check if already executed schedules influence the fatigue
852 IF (trunc(l_schedule_start_date_list(i) - C1.no_of_days)
853 <= sysdate) THEN
854 OPEN C_GET_CHANNEL_FATIGUE_LIST
855 (l_schedule_activity_id_list(i),
856 C1.NO_OF_DAYS,
857 l_schedule_start_date_list(i),
858 l_contact_party_list
859 );
860 FETCH C_GET_CHANNEL_FATIGUE_LIST
861 BULK COLLECT
862 INTO l_temp_contact_party_list,
863 l_temp_contact_count_list;
864 CLOSE C_GET_CHANNEL_FATIGUE_LIST;
865
866 IF (l_temp_contact_party_list.count > 0) THEN
867
871 LOOP
868 -- Check which parties have exceeded the contact count
869 FOR i in l_temp_contact_party_list.FIRST ..
870 l_temp_contact_party_list.LAST
872 IF (l_temp_contact_count_list(i) >=
873 C1.max_contact_allowed) THEN
874 -- this is a fatigue entry, add to the fatigue list
875 l_fatigue_index := l_fatigue_party_list.count + 1;
876 l_fatigue_party_list.extend;
877 l_fatigue_party_list(l_fatigue_index) :=
878 l_temp_contact_party_list(i);
879 ELSE
880 -- this is not a fatigue entry, update the contact list
881 l_contact_index := l_channel_contact_party_list.count + 1;
882 l_channel_contact_party_list(l_contact_index) :=
883 l_temp_contact_party_list(i);
884 l_channel_contact_count_list(l_contact_index) :=
885 l_temp_contact_count_list(i);
886 END IF;
887
888 END LOOP;
889 END IF;
890
891 l_channel_contact_count := l_channel_contact_count_list.COUNT;
892
893 IF (l_fatigue_party_list.count > 0 ) THEN
894
895 -- Remove from the Contact List the list of parties
896 -- already fatigued by the Global Rule
897 DELETE_FROM_LIST(l_contact_party_list,
898 l_fatigue_party_list);
899
900 -- Reset the list variable since it will be reused
901 l_fatigue_party_list.delete;
902 END IF;
903
904
905 END IF;
906
907 -- Now, get the number of contacts made using the
908 -- Simulated Contact information
909
910 OPEN C_GET_CHNL_SIMULATED_FTG_LIST
911 (l_schedule_activity_id_list(i),
912 C1.NO_OF_DAYS,
913 l_schedule_start_date_list(i),
914 l_contact_party_list
915 );
916 FETCH C_GET_CHNL_SIMULATED_FTG_LIST
917 BULK COLLECT
918 INTO l_temp_contact_party_list,
919 l_temp_contact_count_list;
920 CLOSE C_GET_CHNL_SIMULATED_FTG_LIST;
921
922 IF (l_temp_contact_party_list.count > 0) THEN
923
924 -- Check which parties have exceeded the contact count
925 FOR i in l_temp_contact_party_list.FIRST ..
926 l_temp_contact_party_list.LAST
927 LOOP
928 IF (l_temp_contact_count_list(i) >=
929 C1.max_contact_allowed) THEN
930 -- this is a fatigue entry, add to the fatigue list
931 l_fatigue_index := l_fatigue_party_list.count + 1;
932 l_fatigue_party_list.extend;
933 l_fatigue_party_list(l_fatigue_index) :=
934 l_temp_contact_party_list(i);
935 ELSE
936 -- this is not a fatigue entry
937 -- First, check if this entry exists in the previously
938 -- created channel contact list.
939 IF (l_channel_contact_count > 0 ) THEN
940 -- if it exists then there is a chance that
941 -- total contacts made in the past and future
942 -- will exceed the max contact allowed
943
944 l_contact_index := l_channel_contact_count + 1;
945
949 );
946 OPEN C_CHECK_PARTY_IN_CONTACT_LIST(
947 l_channel_contact_party_list,
948 l_temp_contact_party_list(i)
950 FETCH C_CHECK_PARTY_IN_CONTACT_LIST
951 INTO l_party_id;
952 CLOSE C_CHECK_PARTY_IN_CONTACT_LIST;
953
954 -- This Party is already part of the contact list
955 IF (l_party_id is not null) THEN
956 -- Loop through the entries to find the index
957 -- ,get the contact count
958 -- Finally, update the contact count in the global list
959 FOR j in l_channel_contact_party_list.FIRST ..
960 l_channel_contact_party_list.LAST
961 LOOP
962 IF (l_channel_contact_party_list(j) = l_party_id)
963 THEN
964 -- Retrieve the contact count at that index
965 l_channel_contact_count_list(j) :=
966 l_temp_contact_count_list(i) +
967 l_channel_contact_count_list(j);
968
969 END IF;
970
971 END LOOP;
972
973 ELSE
974
975 l_channel_contact_party_list.extend;
976 l_channel_contact_count_list.extend;
977
978 l_channel_contact_party_list(l_contact_index) :=
979 l_temp_contact_party_list(i);
980 l_channel_contact_count_list(l_contact_index) :=
981 l_temp_contact_count_list(i);
982
983 END IF;
984
985 ELSE
986
987 l_channel_contact_party_list.extend;
988 l_channel_contact_count_list.extend;
989
990 l_channel_contact_party_list(l_contact_index) :=
991 l_temp_contact_party_list(i);
992 l_channel_contact_count_list(l_contact_index) :=
993 l_temp_contact_count_list(i);
994 END IF;
995
996 END IF;
997
998 END LOOP; /* loop through l_temp_contact_count_list */
999
1000 END IF;
1001
1002 IF (l_channel_contact_count_list.count > 0) THEN
1003 -- Once again loop through the l_channel_contact_party_list to
1004 -- eliminate fatigue entries
1005 FOR i in l_channel_contact_party_list.FIRST ..
1006 l_channel_contact_party_list.LAST
1007 LOOP
1008 IF (l_channel_contact_count_list(i) >= C1.max_contact_allowed)
1009 THEN
1010 l_fatigue_index := l_fatigue_party_list.count;
1011 l_fatigue_party_list.extend;
1012 l_fatigue_party_list(l_fatigue_index + 1) :=
1013 l_channel_contact_party_list(i);
1014
1015 END IF;
1016 END LOOP;
1017 END IF;
1018
1019 IF (l_fatigue_party_list.count > 0) THEN
1020 -- Once again, delete fatigue entries from the contact
1024 );
1021 -- list
1022 DELETE_FROM_LIST(l_contact_party_list,
1023 l_fatigue_party_list
1025
1026 l_fatigue_party_list.delete;
1027 END IF;
1028
1029 END IF;/* C1.RULE_TYPE='GLOBAL */
1030 END LOOP;
1031
1032 END IF;
1033
1034 IF (l_contact_party_list.count > 0 ) THEN
1035
1036 -- Generate Sequence number for CONTACT_ID column
1037 FOR i in l_contact_party_list.FIRST .. l_contact_party_list.LAST
1038 LOOP
1039 OPEN C_GET_NEXT_PRVW_CONTACT;
1040 FETCH C_GET_NEXT_PRVW_CONTACT
1041 INTO l_contact_id;
1042 CLOSE C_GET_NEXT_PRVW_CONTACT;
1043
1044 l_contact_id_list.extend;
1045 l_contact_id_list(i) := l_contact_id;
1046
1047 END LOOP;
1048
1049 -- Create Contacts in AMS_TCOP_PRVW_CONTACTS
1050 FORALL i in l_contact_party_list.FIRST .. l_contact_party_list.LAST
1051 INSERT INTO
1052 AMS_TCOP_PRVW_CONTACTS
1053 (
1054 CONTACT_ID,
1055 SCHEDULE_ID,
1056 PARTY_ID,
1057 PREVIEW_ID,
1058 CONTACT_DATE,
1059 CREATION_DATE,
1060 CREATED_BY,
1061 LAST_UPDATE_DATE,
1062 LAST_UPDATED_BY,
1063 LAST_UPDATE_LOGIN
1064 )
1065 VALUES
1066 (
1067 l_contact_id_list(i),
1068 l_schedule_id,
1069 l_contact_party_list(i),
1070 G_PRVW_REQUEST_ID,
1071 l_contact_date,
1072 sysdate,
1073 FND_GLOBAL.USER_ID,
1074 sysdate,
1075 FND_GLOBAL.USER_ID,
1076 FND_GLOBAL.USER_ID
1077
1078 );
1079 ELSE
1080 G_NO_SCHEDULE_PIPELINE := TRUE;
1081
1082 END IF;
1083
1084 end LOOP;
1085
1086
1087 ELSE
1088 G_NO_SCHEDULE_PIPELINE := TRUE;
1089
1090
1091 END IF;/*l_schedule_id_list.COUNT > 0*/
1092
1093
1094 END SIMULATE_FUTURE_CONTACTS;
1095
1096
1097 -- ===============================================================
1098 -- Start of Comments
1099 -- Name
1100 -- CALCULATE_FATIGUE
1101 --
1102 -- Purpose
1103 -- Project fatigue for a given date
1104 --
1105 PROCEDURE CALCULATE_FATIGUE( p_preview_date IN Date
1106 ,p_list_header_id IN NUMBER)
1107 IS
1108 CURSOR C_CHECK_PARTY(p_start_date Date,
1109 p_end_date Date)
1110 IS
1111 SELECT PARTY_LIST.PARTY_ID
1112 FROM
1113 (SELECT column_value PARTY_ID
1114 FROM TABLE(CAST(G_PARTY_LIST as JTF_NUMBER_TABLE)) ) PARTY_LIST
1115 WHERE EXISTS
1116 (SELECT PARTY_ID
1117 FROM AMS_TCOP_CONTACTS
1118 WHERE PARTY_ID = PARTY_LIST.PARTY_ID
1119 AND CONTACT_DATE BETWEEN p_start_date and p_end_date
1120 );
1121
1122 CURSOR C_CHECK_PARTY_FOR_CHANNEL(p_start_date Date,
1123 p_end_date Date)
1124 IS
1125 SELECT PARTY_LIST.PARTY_ID
1126 FROM
1127 (SELECT column_value party_id
1128 FROM TABLE(CAST(G_PARTY_LIST as JTF_NUMBER_TABLE))) PARTY_LIST
1129 WHERE EXISTS
1130 (SELECT PARTY_ID
1131 FROM AMS_TCOP_CONTACTS
1132 WHERE PARTY_ID = PARTY_LIST.PARTY_ID
1133 AND MEDIA_ID = G_ACTIVITY_ID
1134 AND CONTACT_DATE BETWEEN p_start_date and p_end_date
1138 IS
1135 );
1136
1137 CURSOR C_GET_GLOBAL_CONTACT_COUNT(p_start_date date,p_end_date date)
1139 SELECT /*+ leading(party_list) +*/
1140 CONTACT.PARTY_ID,COUNT(CONTACT.PARTY_ID)
1141 FROM AMS_TCOP_CONTACTS CONTACT,
1142 (SELECT column_value party_id
1143 FROM TABLE(CAST(G_PARTY_LIST as JTF_NUMBER_TABLE))
1144 ) party_list
1145 WHERE CONTACT.PARTY_ID = party_list.PARTY_ID
1146 AND CONTACT.CONTACT_DATE BETWEEN p_start_date and p_end_date
1147 GROUP BY CONTACT.PARTY_ID;
1148
1149 CURSOR C_GET_GLOBAL_SIMULATED_CONTACT(p_start_date date,p_end_date date)
1150 IS
1151 SELECT /*+ leading(party_list) +*/
1152 CONTACT.PARTY_ID,COUNT(CONTACT.PARTY_ID)
1153 FROM AMS_TCOP_PRVW_CONTACTS CONTACT,
1154 (SELECT column_value party_id
1155 FROM TABLE(CAST(G_PARTY_LIST as JTF_NUMBER_TABLE))
1156 ) party_list
1157 WHERE CONTACT.PARTY_ID = party_list.PARTY_ID
1158 AND CONTACT.CONTACT_DATE BETWEEN p_start_date and p_end_date
1159 AND PREVIEW_ID = G_PRVW_REQUEST_ID
1160 GROUP BY CONTACT.PARTY_ID;
1161
1162 CURSOR C_GET_CHNL_SIMULATED_CONTACT(p_start_date date
1163 ,p_end_date date
1164 )
1165 IS
1166 SELECT /*+ leading(party_list) +*/
1167 CONTACT.PARTY_ID,COUNT(CONTACT.PARTY_ID)
1168 FROM AMS_TCOP_PRVW_CONTACTS CONTACT,
1169 AMS_CAMPAIGN_SCHEDULES_B CSCH,
1170 (SELECT column_value party_id
1171 FROM TABLE(CAST(G_PARTY_LIST as JTF_NUMBER_TABLE))
1172 ) party_list
1173 WHERE CONTACT.PARTY_ID = party_list.PARTY_ID
1174 AND CONTACT.CONTACT_DATE BETWEEN p_start_date and p_end_date
1175 AND PREVIEW_ID = G_PRVW_REQUEST_ID
1176 AND CSCH.SCHEDULE_ID = CONTACT.SCHEDULE_ID
1177 AND CSCH.ACTIVITY_ID = G_ACTIVITY_ID
1178 GROUP BY CONTACT.PARTY_ID;
1179
1180 CURSOR C_GET_GLOBAL_FATIGUE_BY(p_start_date date
1181 ,p_end_date date
1182 ,p_fatigue_party_list JTF_NUMBER_TABLE
1183 )
1184 IS
1185 SELECT /*+ leading(party_list) +*/
1186 CONTACT.PARTY_ID,CONTACT.SCHEDULE_ID
1187 FROM AMS_TCOP_CONTACTS CONTACT,
1188 (SELECT column_value party_id
1189 FROM TABLE(CAST(p_fatigue_party_list as JTF_NUMBER_TABLE))
1190 ) party_list
1191 WHERE CONTACT.PARTY_ID = party_list.PARTY_ID
1192 AND CONTACT.CONTACT_DATE BETWEEN p_start_date and p_end_date;
1193
1194 CURSOR C_GET_CHANNEL_CONTACT_COUNT(p_start_date date,
1195 p_end_date date)
1196 IS
1197 SELECT /*+ leading(party_list) +*/
1198 CONTACT.PARTY_ID,
1199 COUNT(CONTACT.PARTY_ID)
1200 FROM AMS_TCOP_CONTACTS CONTACT,
1201 (SELECT column_value party_id
1202 FROM TABLE(CAST(G_PARTY_LIST as JTF_NUMBER_TABLE))
1203 ) party_list
1204 WHERE CONTACT.PARTY_ID = party_list.PARTY_ID
1205 AND CONTACT.CONTACT_DATE BETWEEN p_start_date and p_end_date
1206 AND CONTACT.MEDIA_ID = G_Activity_Id
1207 GROUP BY CONTACT.PARTY_ID;
1208
1209 CURSOR C_GET_CHANNEL_FATIGUE_BY(p_start_date date
1210 ,p_end_date date
1211 ,p_fatigue_party_list JTF_NUMBER_TABLE
1212 )
1213 IS
1214 SELECT /*+ leading(party_list) +*/
1215 CONTACT.PARTY_ID,CONTACT.SCHEDULE_ID
1216 FROM AMS_TCOP_CONTACTS CONTACT,
1217 (SELECT column_value party_id
1218 FROM TABLE(CAST(p_fatigue_party_list as JTF_NUMBER_TABLE))
1219 ) party_list
1220 WHERE CONTACT.PARTY_ID = party_list.PARTY_ID
1221 AND CONTACT.MEDIA_ID = G_ACTIVITY_ID
1225 ,p_end_date date
1222 AND CONTACT.CONTACT_DATE BETWEEN p_start_date and p_end_date;
1223
1224 CURSOR C_GET_GLOBAL_PRVW_FTG_BY(p_start_date date
1226 ,p_fatigue_party_list JTF_NUMBER_TABLE
1227 )
1228 IS
1229 SELECT /*+ leading(party_list) +*/
1230 CONTACT.PARTY_ID,CONTACT.SCHEDULE_ID
1231 FROM AMS_TCOP_PRVW_CONTACTS CONTACT,
1232 (SELECT column_value party_id
1233 FROM TABLE(CAST(p_fatigue_party_list as JTF_NUMBER_TABLE))
1234 ) party_list
1235 WHERE CONTACT.PARTY_ID = party_list.PARTY_ID
1236 AND CONTACT.CONTACT_DATE BETWEEN p_start_date and p_end_date;
1237
1238 CURSOR C_GET_CHANNEL_PRVW_FTG_BY(p_start_date date
1239 ,p_end_date date
1240 ,p_fatigue_party_list JTF_NUMBER_TABLE
1241 )
1242 IS
1243 SELECT /*+ leading(party_list) +*/
1244 CONTACT.PARTY_ID,CONTACT.SCHEDULE_ID
1245 FROM AMS_TCOP_PRVW_CONTACTS CONTACT,
1246 AMS_CAMPAIGN_SCHEDULES_B CSCH,
1247 (SELECT column_value party_id
1248 FROM TABLE(CAST(p_fatigue_party_list as JTF_NUMBER_TABLE))
1249 ) party_list
1250 WHERE CONTACT.PARTY_ID = party_list.PARTY_ID
1251 AND CONTACT.SCHEDULE_ID = CSCH.SCHEDULE_ID
1252 AND CSCH.ACTIVITY_ID = G_ACTIVITY_ID
1253 AND CONTACT.CONTACT_DATE BETWEEN p_start_date and p_end_date;
1254
1255 CURSOR C_CHECK_PARTY_IN_CONTACT_LIST(p_contact_party_list JTF_NUMBER_TABLE
1256 ,p_party_id NUMBER
1257 )
1258 IS
1259 SELECT party_list.party_id
1260 FROM
1261 (SELECT column_value party_id
1262 FROM TABLE(CAST(p_contact_party_list as JTF_NUMBER_TABLE))
1263 ) party_list
1264 WHERE party_list.party_id=p_party_id;
1265
1266 CURSOR GET_SCHEDULE_FROM_CONTACTS(p_party_id number,
1267 p_start_date date,
1268 p_end_date date
1269 )
1270 IS
1271 SELECT SCHEDULE_ID
1272 FROM AMS_TCOP_CONTACTS
1273 WHERE CONTACT_DATE BETWEEN p_start_date and p_end_date
1274 AND PARTY_ID = p_party_id;
1275
1276 CURSOR GET_CSCH_FROM_CONTACTS_CHNL(p_party_id number,
1277 p_start_date date,
1278 p_end_date date
1279 )
1280 IS
1281 SELECT CONTACT.SCHEDULE_ID
1282 FROM AMS_TCOP_CONTACTS CONTACT
1283 WHERE CONTACT.CONTACT_DATE BETWEEN p_start_date and p_end_date
1284 AND CONTACT.MEDIA_ID = G_ACTIVITY_ID
1285 AND CONTACT.PARTY_ID = p_party_id;
1286
1287 CURSOR GET_SCHEDULE_FROM_PRVW (p_party_id number,
1288 p_start_date date,
1289 p_end_date date,
1290 p_row_num number
1291 )
1292 IS
1293 SELECT SCHEDULE_ID
1294 FROM AMS_TCOP_PRVW_CONTACTS
1295 WHERE CONTACT_DATE BETWEEN p_start_date and p_end_date
1296 AND party_id = p_party_id
1297 AND PREVIEW_ID = G_PRVW_REQUEST_ID
1298 AND rownum = p_row_num
1299 ORDER BY CONTACT_DATE;
1300
1301 CURSOR GET_SCHEDULE_FROM_PREVIEW_CHNL (p_party_id number,
1302 p_start_date date,
1303 p_end_date date,
1304 p_row_num number
1305 )
1306 IS
1307 SELECT CONTACT.SCHEDULE_ID
1308 FROM AMS_TCOP_PRVW_CONTACTS CONTACT,
1309 AMS_CAMPAIGN_SCHEDULES_B CSCH
1310 WHERE CONTACT.CONTACT_DATE BETWEEN p_start_date and p_end_date
1311 AND CSCH.SCHEDULE_ID = CONTACT.SCHEDULE_ID
1312 AND CSCH.ACTIVITY_ID = G_ACTIVITY_ID
1313 AND CONTACT.party_id = p_party_id
1314 AND PREVIEW_ID = G_PRVW_REQUEST_ID
1315 AND rownum = p_row_num
1316 ORDER BY CONTACT.CONTACT_DATE;
1317
1318 -- Get Nextval from AMS_TCOP_PRVW_FATIGUE_S
1319 CURSOR C_GET_NEXT_FATIGUE
1320 IS
1321 SELECT AMS_TCOP_PRVW_FATIGUE_S.NEXTVAL
1322 FROM DUAL;
1323
1324 CURSOR C_GET_NEXT_FTG_DTL
1325 IS
1326 SELECT ams_tcop_prvw_ftg_dtls_s.NEXTVAL
1327 FROM DUAL;
1328
1329 CURSOR C_GET_NEXT_FTG_BY
1330 IS
1331 SELECT ams_tcop_prvw_ftg_by_s.nextval
1332 FROM DUAL;
1333
1334 l_party_id NUMBER;
1335 l_global_fatigue_start_date Date;
1336 l_channel_fatigue_start_date Date;
1337 l_apply_global_rule boolean;
1338 loop_counter NUMBER := 0;
1339 j NUMBER;
1340 l_index NUMBER;
1341 l_preview_id NUMBER;
1342 l_fatigue_dtl_id NUMBER;
1343 l_ftg_by_id NUMBER;
1344 l_fatigue_count NUMBER;
1345
1346 l_global_party_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1347 l_temp_ftg_prvw_party_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1348 l_temp_prvw_ftg_by_party JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1349 l_temp_prvw_ftg_by_schedule JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1350 l_global_contact_count_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1351 l_schedule_contact_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1355 l_channel_party_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1352 l_schedule_prvw_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1353 l_temp_party_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1354 l_temp_contact_count_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1356 l_channel_contact_count_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1357 l_temp_fatigue_party_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1358 l_temp_fatigue_by_party_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1359 l_temp_ftg_by_schedule_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1360 l_fatigue_detail_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1361 l_fatigue_detail_id_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1362 l_fatigue_by_id_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1363
1364 l_global_rule_val NUMBER := 0;
1365 l_channnel_rule_val NUMBER :=0;
1366 BEGIN
1367 G_PARTY_LIST := G_PARTY_TGROUP_LIST;
1368 -- Apply fatigue rules based on the schedules already executed
1369 -- Apply the most restrictive rule first
1370
1371 --dbms_output.put_line('Calculating Fatigue for Date = '||to_char(p_preview_date));
1372
1373 -- Get the most retrictive Rule between the Global and the Channel rule
1374 /* IF ( (nvl(G_Global_Max_Contact,0) * nvl(G_Global_No_Of_Days,0)) >
1375 (nvl(G_Channel_Max_Contact,0) * nvl(G_Channel_No_Of_Days,0)) ) THEN
1376
1377 l_apply_global_rule := true;
1378 ELSE
1379 l_apply_global_rule := false;
1380 END IF;
1381 --This same condition is written below with more checking conditions...
1382 */
1383
1384 l_global_rule_val := nvl(G_Global_Max_Contact,0) * nvl(G_Global_No_Of_Days,0);
1385 l_channnel_rule_val:= nvl(G_Channel_Max_Contact,0) * nvl(G_Channel_No_Of_Days,0);
1386
1387
1388
1389 l_apply_global_rule := false; --initialize to apply channel ...
1390 IF ( (l_global_rule_val > 0) AND (l_channnel_rule_val > 0) AND
1391 (l_global_rule_val > l_channnel_rule_val)) THEN
1392 l_apply_global_rule := true; -- Most restricted rule will take precedence...here its global rule
1393 ELSIF (l_global_rule_val > 0 AND l_channnel_rule_val = 0) THEN
1394 l_apply_global_rule := true; -- No channel ftg rule. So, go for global...
1395 ELSIF (l_global_rule_val = 0 AND l_channnel_rule_val > 0) THEN
1396 l_apply_global_rule := false; --No global ftg rule so.. go for channel...
1397 END IF;
1398
1399
1400
1401
1402 LOOP
1403
1404 IF (l_apply_global_rule) THEN
1405 --dbms_output.put_line('Applying Global Rule');
1406
1407 -- First, check if any party will be fatigued based on the
1408 -- schedules which have already been executed
1409 l_global_fatigue_start_date := p_preview_date - G_GLOBAL_NO_OF_DAYS;
1410 --dbms_output.put_line('Global Fatigue Start Date = '||to_char(l_global_fatigue_start_date));
1411
1412 IF (trunc(l_global_fatigue_start_date) <= trunc(sysdate)) THEN
1413 --dbms_output.put_line('Global Fatigue Start Date Is a Past Date');
1414
1415
1416 -- check if any of these parties exist in AMS_TCOP_CONTACTS
1417 OPEN C_CHECK_PARTY(l_global_fatigue_start_date,sysdate);
1418 FETCH C_CHECK_PARTY
1419 INTO l_party_id;
1420 CLOSE C_CHECK_PARTY;
1421
1422 IF (l_party_id IS NOT NULL) THEN
1423 --dbms_output.put_line('Global Fatigue Start Date Is a Past Date');
1424 -- check if any party is fatigued as per the global rule
1425 OPEN C_GET_GLOBAL_CONTACT_COUNT(l_global_fatigue_start_date,
1426 sysdate);
1427 FETCH C_GET_GLOBAL_CONTACT_COUNT
1428 BULK COLLECT
1429 INTO l_global_party_list
1430 ,l_global_contact_count_list;
1431
1432 CLOSE C_GET_GLOBAL_CONTACT_COUNT;
1433
1434 IF (l_global_party_list.count > 0) THEN
1435 --dbms_output.put_line('Number of people contacted ='||to_char(l_global_party_list.count));
1436
1437 -- Get the list of parties which are already fatigued
1438 FOR i in l_global_party_list.FIRST .. l_global_party_list.LAST
1439 LOOP
1440 IF (l_global_contact_count_list(i) >= G_Global_Max_Contact)
1441 THEN
1442
1443 -- Add this party to the fatigue list
1444 j := l_temp_fatigue_party_list.count + 1;
1445 l_temp_fatigue_party_list.extend;
1446 l_temp_fatigue_party_list(j) := l_global_party_list(i);
1447
1448 -- Remove that entry from l_global_party_list
1449 l_global_party_list.delete(i);
1450 l_global_contact_count_list.delete(i);
1451
1452 END IF;
1453 END LOOP;
1454 END IF;
1455
1456 -- IF there are any fatigued parties found, get the fatigue by
1457 -- schedule list
1458 IF (l_temp_fatigue_party_list.count > 0) THEN
1459 --dbms_output.put_line('Number of people fatigued by global rule ='||to_char(l_temp_fatigue_party_list.count));
1460
1461 OPEN C_GET_GLOBAL_FATIGUE_BY(l_global_fatigue_start_date,
1462 sysdate,
1463 l_temp_fatigue_party_list
1464 );
1468 l_temp_ftg_by_schedule_list;
1465 FETCH C_GET_GLOBAL_FATIGUE_BY
1466 BULK COLLECT
1467 INTO l_temp_fatigue_by_party_list,
1469 CLOSE C_GET_GLOBAL_FATIGUE_BY;
1470
1471 APPEND_GLOBAL_FATIGUE_LIST(l_temp_fatigue_party_list,
1472 l_temp_fatigue_by_party_list,
1473 l_temp_ftg_by_schedule_list
1474 );
1475
1476 --Remove the ones already fatigued from the Original Party
1477 --List
1478 DELETE_FROM_LIST(G_PARTY_LIST,l_temp_fatigue_party_list);
1479
1480 -- Reset the temporary variables, to be re-used
1481 l_temp_fatigue_party_list.delete;
1482 l_temp_fatigue_by_party_list.delete;
1483 l_temp_ftg_by_schedule_list.delete;
1484
1485 END IF;
1486
1487 END IF;
1488
1489 END IF;
1490 -- Now, use the simulated information stored in AMS_TCOP_PRVW_CONTACTS
1491 -- to verify the contact information
1492 IF ( NOT(G_NO_SCHEDULE_PIPELINE) ) THEN
1493
1494 OPEN C_GET_GLOBAL_SIMULATED_CONTACT(l_global_fatigue_start_date,
1495 p_preview_date
1496 );
1497 FETCH C_GET_GLOBAL_SIMULATED_CONTACT
1498 BULK COLLECT
1499 INTO l_temp_party_list,
1500 l_temp_contact_count_list;
1501 CLOSE C_GET_GLOBAL_SIMULATED_CONTACT;
1502
1503 -- Use the Contact count of already executed schedules
1504 -- and contact count of the simulated schedules in tandem to decide
1505 -- fatigue
1506 IF (l_global_party_list.count > 0) THEN
1507
1508 FOR i in l_temp_party_list.FIRST
1509 .. l_temp_party_list.LAST
1510 LOOP
1511 -- Check if the party already exists in l_global_party_list
1512 OPEN C_CHECK_PARTY_IN_CONTACT_LIST(l_global_party_list,
1513 l_temp_party_list(i)
1514 );
1515 FETCH C_CHECK_PARTY_IN_CONTACT_LIST
1516 INTO l_party_id;
1517 CLOSE C_CHECK_PARTY_IN_CONTACT_LIST;
1518
1519 IF (l_party_id is not null) THEN
1520 -- Get the count index
1521 FOR j in l_global_party_list.first ..
1522 l_global_party_list.last
1523 LOOP
1524 IF (l_global_party_list(j) = l_party_id) THEN
1525 -- if the total contact count exceeds fatigue limit
1526 -- then get the contact count
1527 IF ((l_global_contact_count_list(j) +
1528 l_temp_contact_count_list(i)) >=
1529 G_Global_max_contact) THEN
1530 -- this entry is fatigued
1531 -- now, find out the fatigued by
1532 -- it will be a combination of schedules
1533 -- in AMS_TCOP_CONTACTS and
1534 -- AMS_TCOP_PRVW_CONTACTS
1535 OPEN GET_SCHEDULE_FROM_CONTACTS
1536 (l_party_id,
1537 l_global_fatigue_start_date,
1538 sysdate
1539 );
1540 FETCH GET_SCHEDULE_FROM_CONTACTS
1541 BULK COLLECT
1542 INTO l_schedule_contact_list;
1543 CLOSE GET_SCHEDULE_FROM_CONTACTS;
1544
1545 OPEN GET_SCHEDULE_FROM_PRVW
1546 (l_party_id,
1547 l_global_fatigue_start_date,
1548 p_preview_date,
1549 (G_global_max_contact -
1550 l_global_contact_count_list(j))
1551 );
1552 FETCH GET_SCHEDULE_FROM_PRVW
1553 BULK COLLECT
1554 INTO l_schedule_prvw_list;
1555
1556 CLOSE GET_SCHEDULE_FROM_PRVW;
1557
1558 -- Apply the fatigue List and the Fatigue By
1559 -- List
1560 l_index := l_temp_fatigue_party_list.count + 1;
1561 l_temp_fatigue_party_list.extend;
1562 l_temp_fatigue_party_list(l_index) :=
1563 l_party_id;
1564
1565 FOR k in l_schedule_contact_list.first
1566 .. l_schedule_contact_list.last
1567 loop
1568 l_index := l_temp_fatigue_by_party_list.count + 1;
1569 l_temp_fatigue_by_party_list.extend;
1570 l_temp_ftg_by_schedule_list.extend;
1574 l_temp_ftg_by_schedule_list(l_index)
1571
1572 l_temp_fatigue_by_party_list(l_index) :=
1573 l_party_id;
1575 := l_schedule_contact_list(k);
1576
1577 end loop;
1578
1579 FOR l in l_schedule_prvw_list.first
1580 .. l_schedule_prvw_list.last
1581 loop
1582 l_index := l_temp_fatigue_by_party_list.count + 1;
1583 l_temp_fatigue_by_party_list.extend;
1584 l_temp_ftg_by_schedule_list.extend;
1585
1586 l_temp_fatigue_by_party_list(l_index) :=
1587 l_party_id;
1588 l_temp_ftg_by_schedule_list(l_index)
1589 := l_schedule_prvw_list(l);
1590
1591 end loop;
1592
1593
1594 END IF;
1595 END IF;
1596 END LOOP;
1597 ELSE
1598 -- check if this party is fatigued or not
1599 IF (l_temp_contact_count_list(i) >= G_Global_Max_Contact)
1600 THEN
1601 -- add to the fatigue list
1602 l_index := l_temp_fatigue_party_list.count + 1;
1603 l_temp_fatigue_party_list.extend;
1604 l_temp_fatigue_party_list(l_index) :=
1605 l_temp_party_list(i);
1606
1607 -- add this entry to another list which will be used
1608 -- to get the fatigue by in one shot
1609 l_index := l_temp_ftg_prvw_party_list.count + 1;
1610 l_temp_ftg_prvw_party_list.extend;
1611 l_temp_ftg_prvw_party_list(l_index) :=
1612 l_temp_party_list(i);
1613
1614 END IF;
1615 END IF;
1616
1617
1618 END LOOP;
1619
1620 IF (l_temp_ftg_prvw_party_list.count > 0 ) THEN
1621 -- get the corresponding fatigue by
1622 OPEN C_GET_GLOBAL_PRVW_FTG_BY(l_global_fatigue_start_date,
1623 p_preview_date,
1624 l_temp_ftg_prvw_party_list);
1625 FETCH C_GET_GLOBAL_PRVW_FTG_BY
1626 BULK COLLECT
1627 INTO l_temp_prvw_ftg_by_party,
1628 l_temp_prvw_ftg_by_schedule;
1629 CLOSE C_GET_GLOBAL_PRVW_FTG_BY;
1630
1631 FOR l in l_temp_prvw_ftg_by_party.FIRST ..
1632 l_temp_prvw_ftg_by_party.LAST
1633 LOOP
1634 -- Add these fatigue by entries to the l_temp_fatigue
1635 -- entries
1636 l_index := l_temp_fatigue_by_party_list.count + 1;
1637 l_temp_fatigue_by_party_list.extend;
1638 l_temp_ftg_by_schedule_list.extend;
1639
1640 l_temp_fatigue_by_party_list(l_index) :=
1641 l_temp_prvw_ftg_by_party(l);
1642 l_temp_ftg_by_schedule_list(l_index)
1643 := l_temp_prvw_ftg_by_schedule(l);
1644 END LOOP;
1645
1646 END IF;
1647
1648 APPEND_GLOBAL_FATIGUE_LIST(l_temp_fatigue_party_list,
1649 l_temp_fatigue_by_party_list,
1650 l_temp_ftg_by_schedule_list
1651 );
1652
1653 --Remove the ones already fatigued from the Original Party
1654 --List
1655 DELETE_FROM_LIST(G_PARTY_LIST,l_temp_fatigue_party_list);
1656
1657 -- Reset the temporary variables, to be re-used
1658 l_temp_fatigue_party_list.delete;
1659 l_temp_fatigue_by_party_list.delete;
1660 l_temp_ftg_by_schedule_list.delete;
1661
1662 ELSE
1663 -- no parties exist in the global list
1664 IF (l_temp_party_list.count > 0) THEN
1665 FOR k in l_temp_party_list.first ..
1666 l_temp_party_list.last
1667 LOOP
1668 -- check if any party is going to be fatigued
1669 IF (l_temp_contact_count_list(k) >= G_Global_Max_Contact)
1670 THEN
1671 -- Add to the fatigue list
1672 l_index := l_temp_fatigue_party_list.count + 1;
1673 l_temp_fatigue_party_list.extend;
1674 l_temp_fatigue_party_list(l_index) :=
1675 l_temp_party_list(k);
1676 END IF;
1677 END LOOP;
1678 END IF;
1679
1680 IF (l_temp_fatigue_party_list.count > 0 ) THEN
1681 -- Now, get the fatigue by
1682 OPEN C_GET_GLOBAL_PRVW_FTG_BY(l_global_fatigue_start_date,
1683 p_preview_date,
1684 l_temp_fatigue_party_list);
1685 FETCH C_GET_GLOBAL_PRVW_FTG_BY
1686 BULK COLLECT
1687 INTO l_temp_fatigue_by_party_list,
1691 APPEND_GLOBAL_FATIGUE_LIST(l_temp_fatigue_party_list,
1688 l_temp_ftg_by_schedule_list;
1689 CLOSE C_GET_GLOBAL_PRVW_FTG_BY;
1690
1692 l_temp_fatigue_by_party_list,
1693 l_temp_ftg_by_schedule_list
1694 );
1695
1696 --Remove the ones already fatigued from the Original Party
1697 --List
1698 DELETE_FROM_LIST(G_PARTY_LIST,l_temp_fatigue_party_list);
1699
1700 -- Reset the temporary variables, to be re-used
1701 l_temp_fatigue_party_list.delete;
1702 l_temp_fatigue_by_party_list.delete;
1703 l_temp_ftg_by_schedule_list.delete;
1704 l_temp_fatigue_by_party_list.delete;
1705 l_temp_ftg_by_schedule_list.delete;
1706
1707 END IF;
1708
1709 END IF;
1710
1711 END IF;/*NOT(G_NO_SCHEDULE_PIPELINE)*/
1712
1713 l_apply_global_rule := false;
1714 ELSE
1715 --dbms_output.put_line('Apply Channel Rule');
1716 -- Apply the Channel Specific Rule
1717
1718 -- First, check if any party will be fatigued based on the
1719 -- schedules which have already been executed
1720 l_channel_fatigue_start_date := p_preview_date - G_CHANNEL_NO_OF_DAYS;
1721
1722 --dbms_output.put_line('Channel Rule Start Date = '|| to_char(l_channel_fatigue_start_date));
1723
1724 IF (trunc(l_channel_fatigue_start_date) <= trunc(sysdate)) THEN
1725 -- The applicable start date for fatigue rules is in the past
1726 --dbms_output.put_line('Channel Rule Start Date Is a Past Date');
1727
1728 -- Get the list of schedules fatigued by channel rule
1729 OPEN C_GET_CHANNEL_CONTACT_COUNT(l_channel_fatigue_start_date
1730 ,p_preview_date
1731 );
1732 FETCH C_GET_CHANNEL_CONTACT_COUNT
1733 BULK COLLECT
1734 INTO l_channel_party_list,l_channel_contact_count_list;
1735 CLOSE C_GET_CHANNEL_CONTACT_COUNT;
1736
1737 --DBMS_OUTPUT.PUT_LINE('Before Line 1552');
1738
1739 IF (l_channel_party_list.count > 0) THEN
1740 --dbms_output.put_line('Number of contacts made through channel rule = '||to_char(l_channel_party_list.count));
1741
1742 -- Get the list of parties which are already fatigued
1743 FOR m in l_channel_party_list.FIRST .. l_channel_party_list.LAST
1744 LOOP
1745 IF (l_channel_contact_count_list(m) >= G_Channel_Max_Contact)
1746 THEN
1747
1748 -- Add this party to the fatigue list
1749 j := l_temp_fatigue_party_list.count + 1;
1750 l_temp_fatigue_party_list.extend;
1751 l_temp_fatigue_party_list(j) := l_channel_party_list(m);
1752
1753 END IF;
1754 END LOOP;
1755 END IF;
1756 --DBMS_OUTPUT.PUT_LINE('After Line 1552: Outside Loop');
1757
1758 -- IF there are any fatigued parties found, get the fatigue by
1759 -- schedule list
1760 IF (l_temp_fatigue_party_list.count > 0) THEN
1761 --dbms_output.put_line('Number of fatigue entries made through channel rule = '||to_char(l_temp_fatigue_party_list.count));
1762
1763 OPEN C_GET_CHANNEL_FATIGUE_BY(l_channel_fatigue_start_date,
1764 p_preview_date,
1765 l_temp_fatigue_party_list
1766
1767 );
1768 FETCH C_GET_CHANNEL_FATIGUE_BY
1769 BULK COLLECT
1770 INTO l_temp_fatigue_by_party_list,
1771 l_temp_ftg_by_schedule_list;
1772 CLOSE C_GET_CHANNEL_FATIGUE_BY;
1773
1774 APPEND_GLOBAL_FATIGUE_LIST(l_temp_fatigue_party_list,
1775 l_temp_fatigue_by_party_list,
1776 l_temp_ftg_by_schedule_list
1777 );
1778
1779 --Remove the ones already fatigued from the Original Party
1780 --List
1781 IF (l_temp_fatigue_party_list.count > 0) THEN
1782 --dbms_output.put_line('G Party List Entry = '||to_char(G_PARTY_LIST.count));
1783 DELETE_FROM_LIST(G_PARTY_LIST,l_temp_fatigue_party_list);
1784 --dbms_output.put_line('After deleting fatigue entries G Party List Entry = '||to_char(G_PARTY_LIST.count));
1785 END IF;
1786
1787 -- Reset the temporary variables, to be re-used
1788 l_temp_fatigue_party_list.delete;
1789 l_temp_fatigue_by_party_list.delete;
1790 l_temp_ftg_by_schedule_list.delete;
1791
1792 END IF;
1793
1794 END IF;
1795
1796 -- Now, use the simulated information stored in AMS_TCOP_PRVW_CONTACTS
1797 -- to verify the contact information
1798 IF ( NOT(G_NO_SCHEDULE_PIPELINE) ) THEN
1799
1800 OPEN C_GET_CHNL_SIMULATED_CONTACT(l_channel_fatigue_start_date,
1801 p_preview_date
1805 INTO l_temp_party_list,
1802 );
1803 FETCH C_GET_CHNL_SIMULATED_CONTACT
1804 BULK COLLECT
1806 l_temp_contact_count_list;
1807 CLOSE C_GET_CHNL_SIMULATED_CONTACT;
1808
1809 -- Use the Contact count of already executed schedules
1810 -- and contact count of the simulated schedules in tandem to decide
1811 -- fatigue
1812 IF (l_channel_party_list.count > 0) THEN
1813
1814 FOR i in l_temp_party_list.FIRST
1815 .. l_temp_party_list.LAST
1816 LOOP
1817 -- Check if the party already exists in l_global_party_list
1818 OPEN C_CHECK_PARTY_IN_CONTACT_LIST(l_channel_party_list,
1819 l_temp_party_list(i)
1820 );
1821 FETCH C_CHECK_PARTY_IN_CONTACT_LIST
1822 INTO l_party_id;
1823 CLOSE C_CHECK_PARTY_IN_CONTACT_LIST;
1824
1825 IF (l_party_id is not null) THEN
1826 IF (l_channel_party_list.count > 0 ) THEN
1827 -- Get the count index
1828 FOR j in l_channel_party_list.first ..
1829 l_channel_party_list.last
1830 LOOP
1831 IF (l_channel_party_list(j) = l_party_id) THEN
1832 -- if the total contact count exceeds fatigue limit
1833 -- then get the contact count
1834 IF ((l_channel_contact_count_list(j) +
1835 l_temp_contact_count_list(i)) >=
1836 G_channel_max_contact) THEN
1837 -- this entry is fatigued
1838 -- now, find out the fatigued by
1839 -- it will be a combination of schedules
1840 -- in AMS_TCOP_CONTACTS and
1841 -- AMS_TCOP_PRVW_CONTACTS
1842 OPEN GET_CSCH_FROM_CONTACTS_CHNL
1843 (l_party_id,
1844 l_channel_fatigue_start_date,
1845 sysdate
1846 );
1847 FETCH GET_CSCH_FROM_CONTACTS_CHNL
1848 BULK COLLECT
1849 INTO l_schedule_contact_list;
1850 CLOSE GET_CSCH_FROM_CONTACTS_CHNL;
1851
1852 OPEN GET_SCHEDULE_FROM_PREVIEW_CHNL
1853 (l_party_id,
1854 l_channel_fatigue_start_date,
1855 p_preview_date,
1856 (G_channel_max_contact -
1857 l_channel_contact_count_list(j))
1858 );
1859 FETCH GET_SCHEDULE_FROM_PREVIEW_CHNL
1860 BULK COLLECT
1861 INTO l_schedule_prvw_list;
1862
1863 CLOSE GET_SCHEDULE_FROM_PREVIEW_CHNL;
1864
1865 -- Apply the fatigue List and the Fatigue By
1866 -- List
1867 l_index := l_temp_fatigue_party_list.count + 1;
1868 l_temp_fatigue_party_list.extend;
1869 l_temp_fatigue_party_list(l_index) :=
1870 l_party_id;
1871 IF (l_schedule_contact_list.count > 0) THEN
1872
1873 FOR k in l_schedule_contact_list.first
1874 .. l_schedule_contact_list.last
1875 loop
1876 l_index := l_temp_fatigue_by_party_list.count + 1;
1877 l_temp_fatigue_by_party_list.extend;
1878 l_temp_ftg_by_schedule_list.extend;
1879
1880 l_temp_fatigue_by_party_list(l_index) :=
1881 l_party_id;
1882 l_temp_ftg_by_schedule_list(l_index)
1883 := l_schedule_contact_list(k);
1884
1885 end loop;
1886
1887 END IF;
1888
1889 IF (l_schedule_prvw_list.count > 0) then
1890
1891 FOR l in l_schedule_prvw_list.first
1892 .. l_schedule_prvw_list.last
1893 loop
1894 l_index := l_temp_fatigue_by_party_list.count + 1;
1895 l_temp_fatigue_by_party_list.extend;
1896 l_temp_ftg_by_schedule_list.extend;
1897
1898 l_temp_fatigue_by_party_list(l_index) :=
1899 l_party_id;
1900 l_temp_ftg_by_schedule_list(l_index)
1901 := l_schedule_prvw_list(l);
1905 END IF;
1902
1903 end loop;
1904
1906
1907
1908 END IF;
1909 END IF;
1910 END LOOP;
1911 END IF;
1912 ELSE
1913 -- check if this party is fatigued or not
1914 IF (l_temp_contact_count_list(i) >= G_Channel_Max_Contact)
1915 THEN
1916 -- add to the fatigue list
1917 l_index := l_temp_fatigue_party_list.count + 1;
1918 l_temp_fatigue_party_list.extend;
1919 l_temp_fatigue_party_list(l_index) :=
1920 l_temp_party_list(i);
1921
1922 -- add this entry to another list which will be used
1923 -- to get the fatigue by in one shot
1924 l_index := l_temp_ftg_prvw_party_list.count + 1;
1925 l_temp_ftg_prvw_party_list.extend;
1926 l_temp_ftg_prvw_party_list(l_index) :=
1927 l_temp_party_list(i);
1928
1929 END IF;
1930 END IF;
1931
1932
1933 END LOOP;
1934
1935 IF (l_temp_ftg_prvw_party_list.count > 0 ) THEN
1936 -- get the corresponding fatigue by
1937 OPEN C_GET_CHANNEL_PRVW_FTG_BY(l_channel_fatigue_start_date,
1938 p_preview_date,
1939 l_temp_ftg_prvw_party_list);
1940 FETCH C_GET_CHANNEL_PRVW_FTG_BY
1941 BULK COLLECT
1942 INTO l_temp_prvw_ftg_by_party,
1943 l_temp_prvw_ftg_by_schedule;
1944 CLOSE C_GET_CHANNEL_PRVW_FTG_BY;
1945
1946 IF (l_temp_prvw_ftg_by_party.count > 0 ) THEN
1947
1948 FOR l in l_temp_prvw_ftg_by_party.FIRST ..
1949 l_temp_prvw_ftg_by_party.LAST
1950 LOOP
1951 -- Add these fatigue by entries to the l_temp_fatigue
1952 -- entries
1953 l_index := l_temp_fatigue_by_party_list.count + 1;
1954 l_temp_fatigue_by_party_list.extend;
1955 l_temp_ftg_by_schedule_list.extend;
1956
1957 l_temp_fatigue_by_party_list(l_index) :=
1958 l_temp_prvw_ftg_by_party(l);
1959 l_temp_ftg_by_schedule_list(l_index)
1960 := l_temp_prvw_ftg_by_schedule(l);
1961 END LOOP;
1962
1963 END IF;
1964
1965 END IF;
1966
1967 APPEND_GLOBAL_FATIGUE_LIST(l_temp_fatigue_party_list,
1968 l_temp_fatigue_by_party_list,
1969 l_temp_ftg_by_schedule_list
1970 );
1971
1972 --Remove the ones already fatigued from the Original Party
1973 --List
1974 DELETE_FROM_LIST(G_PARTY_LIST,l_temp_fatigue_party_list);
1975
1976 -- Reset the temporary variables, to be re-used
1977 l_temp_fatigue_party_list.delete;
1978 l_temp_fatigue_by_party_list.delete;
1979 l_temp_ftg_by_schedule_list.delete;
1980
1981 ELSE
1982 -- no parties exist in the global list
1983 IF (l_temp_party_list.count > 0 ) THEN
1984 FOR k in l_temp_party_list.first ..
1985 l_temp_party_list.last
1986 LOOP
1987 -- check if any party is going to be fatigued
1988 IF (l_temp_contact_count_list(k) >= G_Channel_Max_Contact)
1989 THEN
1990 -- Add to the fatigue list
1991 l_index := l_temp_fatigue_party_list.count + 1;
1992 l_temp_fatigue_party_list.extend;
1993 l_temp_fatigue_party_list(l_index) :=
1994 l_temp_party_list(k);
1995 END IF;
1996 END LOOP;
1997 END IF;
1998 IF (l_temp_fatigue_party_list.count > 0 ) THEN
1999 -- Now, get the fatigue by
2000 OPEN C_GET_CHANNEL_PRVW_FTG_BY(l_channel_fatigue_start_date,
2001 p_preview_date,
2002 l_temp_fatigue_party_list);
2003 FETCH C_GET_CHANNEL_PRVW_FTG_BY
2004 BULK COLLECT
2005 INTO l_temp_fatigue_by_party_list,
2006 l_temp_ftg_by_schedule_list;
2007 CLOSE C_GET_CHANNEL_PRVW_FTG_BY;
2008
2009 APPEND_GLOBAL_FATIGUE_LIST(l_temp_fatigue_party_list,
2010 l_temp_fatigue_by_party_list,
2011 l_temp_ftg_by_schedule_list
2012 );
2013
2014 --Remove the ones already fatigued from the Original Party
2015 --List
2016 DELETE_FROM_LIST(G_PARTY_LIST,l_temp_fatigue_party_list);
2020 l_temp_fatigue_by_party_list.delete;
2017
2018 -- Reset the temporary variables, to be re-used
2019 l_temp_fatigue_party_list.delete;
2021 l_temp_ftg_by_schedule_list.delete;
2022
2023 END IF;
2024
2025 END IF;
2026
2027
2028 END IF;/*NOT(G_NO_SCHEDULE_PIPELINE)*/
2029
2030 l_apply_global_rule := true;
2031 END IF;
2032
2033 loop_counter := loop_counter + 1;
2034
2035 IF (loop_counter = 2) THEN
2036 EXIT;
2037 END IF;
2038
2039 END LOOP;
2040
2041 -- Get the next val from AMS_TCOP_PRVW_FATIGUE_S
2042 OPEN C_GET_NEXT_FATIGUE;
2043 FETCH C_GET_NEXT_FATIGUE
2044 INTO l_preview_id;
2045 CLOSE C_GET_NEXT_FATIGUE;
2046
2047 l_fatigue_count := G_FATIGUE_PARTY_LIST.COUNT;
2048 -- Bulk Insert the relevant Tables
2049 INSERT INTO AMS_TCOP_PRVW_FATIGUE
2050 (
2051 PREVIEW_ID,
2052 REQUEST_ID,
2053 PREVIEW_DATE,
2054 FATIGUE_COUNT,
2055 CREATION_DATE,
2056 CREATED_BY,
2057 LAST_UPDATE_DATE,
2058 LAST_UPDATED_BY,
2059 LAST_UPDATE_LOGIN
2060 )
2061 VALUES
2062 (
2063 l_preview_id,
2064 G_PRVW_REQUEST_ID,
2065 p_preview_date,
2066 l_fatigue_count,
2067 sysdate,
2068 FND_GLOBAL.USER_ID,
2069 sysdate,
2070 FND_GLOBAL.USER_ID,
2071 FND_GLOBAL.USER_ID
2072 );
2073
2074 IF (G_FATIGUE_BY_PARTY_LIST.count > 0) THEN
2075
2076 for k in G_FATIGUE_BY_PARTY_LIST.first .. G_FATIGUE_BY_PARTY_LIST.last
2077 loop
2078 OPEN C_GET_NEXT_FTG_BY;
2079 FETCH C_GET_NEXT_FTG_BY
2080 INTO l_ftg_by_id;
2081 CLOSE C_GET_NEXT_FTG_BY;
2082
2083 l_fatigue_by_id_list.extend;
2084 l_fatigue_by_id_list(k) := l_ftg_by_id;
2085
2086 end loop;
2087 END IF;
2088 --dbms_output.put_line('l_fatigue_by_id_list count ='|| to_char(l_fatigue_by_id_list.count));
2089 l_fatigue_detail_id_list.extend(G_FATIGUE_BY_PARTY_LIST.count);
2090
2091 IF (G_FATIGUE_PARTY_LIST.COUNT > 0) THEN
2092
2093 FOR i in G_FATIGUE_PARTY_LIST.FIRST .. G_FATIGUE_PARTY_LIST.LAST
2094 LOOP
2095 OPEN C_GET_NEXT_FTG_DTL;
2096 FETCH C_GET_NEXT_FTG_DTL
2097 INTO l_fatigue_dtl_id;
2098 CLOSE C_GET_NEXT_FTG_DTL;
2099
2100 l_fatigue_detail_list.extend;
2101 l_fatigue_detail_list(i) := l_fatigue_dtl_id;
2102
2103 IF (G_FATIGUE_BY_PARTY_LIST.count > 0 ) THEN
2104 -- Set the correct data for AMS_TCOP_PRVW_FTG_BY
2105 FOR j IN G_FATIGUE_BY_PARTY_LIST.FIRST .. G_FATIGUE_BY_PARTY_LIST.LAST
2106 LOOP
2107 IF (G_FATIGUE_BY_PARTY_LIST(j) = G_FATIGUE_PARTY_LIST(i)) THEN
2108 -- set the sequence Id
2109 l_fatigue_detail_id_list(j) := l_fatigue_dtl_id;
2110 END IF;
2111 END LOOP;
2112 END IF;
2113 END LOOP;
2114 END IF;
2115 --dbms_output.put_line('Total Fatigue By = '||to_char(G_FATIGUE_BY_PARTY_LIST.count));
2116 --dbms_output.put_line('Total Fatigue By Schedule= '||to_char(G_FATIGUE_BY_SCHEDULE_LIST.count));
2117
2118 IF (G_FATIGUE_PARTY_LIST.count > 0 ) THEN
2119 -- Create Fatigue Details Entries
2120 FORALL i IN G_FATIGUE_PARTY_LIST.FIRST .. G_FATIGUE_PARTY_LIST.LAST
2121 INSERT INTO AMS_TCOP_PRVW_FTG_DTLS
2122 (
2123 FATIGUE_DETAIL_ID,
2124 PREVIEW_ID,
2125 PARTY_ID,
2126 CREATION_DATE,
2127 CREATED_BY,
2128 LAST_UPDATE_DATE,
2129 LAST_UPDATED_BY,
2130 LAST_UPDATE_LOGIN
2131 )
2132 VALUES
2133 (
2134 l_fatigue_detail_list(i),
2135 l_preview_id,
2136 G_FATIGUE_PARTY_LIST(i),
2137 sysdate,
2138 FND_GLOBAL.USER_ID,
2139 sysdate,
2140 FND_GLOBAL.USER_ID,
2141 FND_GLOBAL.USER_ID
2142 );
2143 END IF;
2144
2145 IF (G_FATIGUE_BY_SCHEDULE_LIST.count > 0 ) THEN
2146 --dbms_output.put_line('Accessing 1 '||to_char(l_fatigue_by_id_list(363)));
2147 --dbms_output.put_line('Accessing 2 '||to_char(l_fatigue_detail_id_list(363)));
2148 --dbms_output.put_line('Accessing 3 '||to_char(g_fatigue_by_schedule_list(363)));
2149 -- Create entries in AMS_TCOP_PRVW_FTG_BY
2150 FORALL i in G_FATIGUE_BY_SCHEDULE_LIST.FIRST .. G_FATIGUE_BY_SCHEDULE_LIST.LAST
2151 INSERT INTO AMS_TCOP_PRVW_FTG_BY
2152 (
2153 FATIGUE_BY_ID,
2154 FATIGUE_DETAIL_ID,
2155 SCHEDULE_ID,
2156 CREATION_DATE,
2157 CREATED_BY,
2158 LAST_UPDATE_DATE,
2159 LAST_UPDATED_BY,
2160 LAST_UPDATE_LOGIN
2161
2162 )
2163 VALUES
2164 (
2165 l_fatigue_by_id_list(i),
2166 l_fatigue_detail_id_list(i),
2167 G_FATIGUE_BY_SCHEDULE_LIST(i),
2168 sysdate,
2169 FND_GLOBAL.USER_ID,
2170 sysdate,
2171 FND_GLOBAL.USER_ID,
2172 FND_GLOBAL.USER_ID
2173 );
2174
2175 END IF;
2176
2177 -- Reset Global Variables
2178 G_PARTY_LIST.DELETE;
2179 G_FATIGUE_PARTY_LIST.DELETE;
2180 G_FATIGUE_BY_PARTY_LIST.DELETE;
2181 G_FATIGUE_BY_SCHEDULE_LIST.DELETE;
2182
2183
2184 END CALCULATE_FATIGUE;
2185 -- ===============================================================
2186 -- Start of Comments
2187 -- Name
2188 -- PREVIEW_FATIGUE
2189 --
2190 -- Purpose
2191 -- This procedure does preview projection for the target group
2192 -- specified by the list_header_id
2193 --
2194 PROCEDURE PREVIEW_FATIGUE(p_list_header_id IN NUMBER)
2195 IS
2196 -- Get Schedule Details
2197 CURSOR C_GET_SCHEDULE_DETAILS
2198 IS
2199 SELECT CSCH.SCHEDULE_ID,CSCH.START_DATE_TIME,CSCH.ACTIVITY_ID,CAMP.ACTUAL_EXEC_END_DATE
2200 FROM AMS_CAMPAIGN_SCHEDULES_B CSCH,
2201 AMS_ACT_LISTS ACT_LIST,ams_campaigns_vl CAMP
2202 WHERE ACT_LIST.LIST_HEADER_ID = p_list_header_id
2203 AND CSCH.SCHEDULE_ID = ACT_LIST.LIST_USED_BY_ID
2204 AND ACT_LIST.LIST_USED_BY = 'CSCH'
2205 AND ACT_LIST.LIST_ACT_TYPE='TARGET'
2206 AND CSCH.CAMPAIGN_ID=CAMP.CAMPAIGN_ID;
2207
2208 -- Get the list of party Ids to be previewed
2209 CURSOR C_GET_PARTY_LIST
2210 IS
2211 SELECT PARTY_ID
2212 FROM AMS_LIST_ENTRIES
2213 WHERE LIST_HEADER_ID = p_list_header_id
2214 AND ENABLED_FLAG='Y';
2215
2216 -- Get Fatigue Rule Details
2217 CURSOR C_GET_FATIGUE_RULE_DETAILS(p_activity_id NUMBER)
2218 IS
2219 SELECT RULE.RULE_TYPE RULE_TYPE,
2220 RULE.MAX_CONTACT_ALLOWED MAX_CONTACT_ALLOWED,
2221 PERIOD.NO_OF_DAYS NO_OF_DAYS
2222 FROM AMS_TCOP_FR_PERIODS_B PERIOD,
2223 AMS_TCOP_FR_RULES_SETUP RULE
2224 WHERE RULE.PERIOD_ID = PERIOD.PERIOD_ID
2225 AND (RULE.CHANNEL_ID IS NULL OR RULE.CHANNEL_ID = p_activity_id);
2226
2227 l_total_preview_count NUMBER;
2228 l_start_date_time DATE;
2229 l_schedule_id NUMBER;
2230 l_camp_end_date_time DATE;
2231 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'PREVIEW_FATIGUE';
2232
2233 BEGIN
2234
2235 write_debug_message(LOG_LEVEL_EVENT,
2236 PROCEDURE_NAME,
2237 'PRINT_INPUT_PARAMS',
2238 'INPUT List Header Id = '||to_char(p_list_header_id)
2239 );
2240 -- Get the list of parties to be previewed
2241 OPEN C_GET_PARTY_LIST;
2242 FETCH C_GET_PARTY_LIST
2243 BULK COLLECT
2244 INTO G_PARTY_TGROUP_LIST;
2245 CLOSE C_GET_PARTY_LIST;
2246
2247 l_total_preview_count := G_PARTY_TGROUP_LIST.COUNT;
2248
2249
2250
2251 write_debug_message(LOG_LEVEL_EVENT,
2252 PROCEDURE_NAME,
2253 'PRINT_PREVIEW_COUNT',
2254 'Total Preview Count ='||to_char(l_total_preview_count)
2255 );
2256
2257 -- Create Preview Request
2258
2259 --CREATE_PREVIEW_REQUEST(p_list_header_id,l_total_preview_count);
2260
2261 write_debug_message(LOG_LEVEL_EVENT,
2262 PROCEDURE_NAME,
2263 'AFTER_PREVIEW_REQUEST_CREATION',
2264 'Preview request is already created'
2265 );
2266
2267 -- Get schedule details
2268 OPEN C_GET_SCHEDULE_DETAILS;
2269 FETCH C_GET_SCHEDULE_DETAILS
2273
2270 INTO G_schedule_id,l_start_date_time,G_activity_id,l_camp_end_date_time;
2271 CLOSE C_GET_SCHEDULE_DETAILS;
2272
2274
2275 write_debug_message(LOG_LEVEL_EVENT,
2276 PROCEDURE_NAME,
2277 'PRINT_SCHEDULE_DETAILS',
2278 'Schedule Id = '||to_char(G_SCHEDULE_ID)
2279 );
2280
2281 write_debug_message(LOG_LEVEL_EVENT,
2282 PROCEDURE_NAME,
2283 'PRINT_SCHEDULE_DETAILS',
2284 'Schedule Start Date ='||to_char(l_start_date_time)
2285 );
2286
2287 write_debug_message(LOG_LEVEL_EVENT,
2288 PROCEDURE_NAME,
2289 'PRINT_SCHEDULE_DETAILS',
2290 'Activity Id = '||to_char(G_Activity_Id)
2291 );
2292
2293 -- Calculate Preview Date Range and update global variable G_PRVW_DATE_LIST
2294
2295
2296 -- CALCULATE_PREVIEW_DATE_RANGE(l_start_date_time);
2297 CALCULATE_PREVIEW_DATE_RANGE(l_start_date_time,l_camp_end_date_time);
2298
2299
2300
2301
2305 IF (C1.RULE_TYPE = 'GLOBAL') THEN
2302 -- Get Fatigue Rule Information and update Global Variables
2303 FOR C1 in C_GET_FATIGUE_RULE_DETAILS(G_activity_id)
2304 LOOP
2306 G_GLOBAL_MAX_CONTACT := C1.MAX_CONTACT_ALLOWED;
2307 G_GLOBAL_NO_OF_DAYS := C1.NO_OF_DAYS;
2308
2309 write_debug_message(LOG_LEVEL_EVENT,
2310 PROCEDURE_NAME,
2311 'PRINT_GLOBAL_RULE',
2312 'Global Rule: Maximum Contact Allowed = '||to_char(g_global_max_contact)||' in '||to_char(G_GLOBAL_NO_OF_DAYS)||' days.'
2313 );
2314 END IF;
2315
2316 IF (C1.RULE_TYPE = 'CHANNEL_BASED') THEN
2317 G_CHANNEL_MAX_CONTACT := C1.MAX_CONTACT_ALLOWED;
2318 G_CHANNEL_NO_OF_DAYS := C1.NO_OF_DAYS;
2319
2320
2321 write_debug_message(LOG_LEVEL_EVENT,
2322 PROCEDURE_NAME,
2323 'PRINT_CHANNEL_RULE',
2324 'Global Rule: Maximum Contact Allowed = '||to_char(g_channel_max_contact)||' in '||to_char(G_CHANNEL_NO_OF_DAYS)||' days.'
2325 );
2326 END IF;
2327
2328 END LOOP;
2329
2330 -- Now, simulate future contact. Basically as if the overlapping
2331 -- target group members are being contacted by the schedules in the pipeline
2332 G_PREVIEW_START_DATE := G_PRVW_DATE_LIST(1);
2333
2334 G_PREVIEW_END_DATE := G_PRVW_DATE_LIST(G_PRVW_DATE_LIST.COUNT);
2335
2336
2337 write_debug_message(LOG_LEVEL_EVENT,
2338 PROCEDURE_NAME,
2339 'PRINT_PREVIEW_START_DATE',
2340 'Preview Start Date ='||to_char(G_PREVIEW_START_DATE)
2341 );
2342
2343 write_debug_message(LOG_LEVEL_EVENT,
2344 PROCEDURE_NAME,
2345 'PRINT_PREVIEW_END_DATE',
2346 'Preview End Date ='||to_char(G_PREVIEW_END_DATE)
2347 );
2348
2349 SIMULATE_FUTURE_CONTACTS(G_PREVIEW_START_DATE,
2350 G_PREVIEW_END_DATE,
2351 p_list_header_id
2352 ) ;
2353 -- Calculate Fatigue for each date
2354 FOR i in G_PRVW_DATE_LIST.FIRST .. G_PRVW_DATE_LIST.LAST
2355 LOOP
2356 CALCULATE_FATIGUE(G_PRVW_DATE_LIST(i),p_list_header_id);
2357
2358 END LOOP;
2359
2360
2361
2362 -- Finally Update the Status to COMPLETE
2363 UPDATE AMS_TCOP_PRVW_REQUESTS
2364 SET STATUS='COMPLETE',
2365 LAST_UPDATE_DATE = sysdate,
2366 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
2367 WHERE REQUEST_ID=G_PRVW_REQUEST_ID;
2368
2369 DELETE FROM AMS_TCOP_PRVW_CONTACTS
2370 WHERE PREVIEW_ID = G_PRVW_REQUEST_ID;
2371
2372 DELETE FROM AMS_TCOP_PRVW_FATIGUE
2373 WHERE
2374 REQUEST_ID IN
2375 (
2376 SELECT REQUEST_ID FROM AMS_TCOP_PRVW_REQUESTS
2377 WHERE LIST_HEADER_ID = p_list_header_id
2378 AND REQUEST_ID <> G_PRVW_REQUEST_ID
2379 );
2380
2381
2382 DELETE FROM AMS_TCOP_PRVW_REQUESTS
2383 WHERE LIST_HEADER_ID = p_list_header_id
2384 AND REQUEST_ID <> G_PRVW_REQUEST_ID;
2385
2386 END PREVIEW_FATIGUE;
2387
2388 -- ===============================================================
2389 -- Start of Comments
2390 -- Name
2391 -- REFRESH
2392 --
2393 -- Purpose
2394 -- This function is called from Business Event raised through UI
2395 -- ===============================================================
2396 FUNCTION REFRESH(p_subscription_guid IN RAW,
2397 p_event IN OUT NOCOPY WF_EVENT_T
2398 ) RETURN VARCHAR2
2399 IS
2400 --Local Variables
2401 l_list_header_id NUMBER;
2402
2403 BEGIN
2404 -- Get the Value of LIST_HEADER_ID
2405 l_list_header_id := p_event.getValueForParameter('LIST_HEADER_ID');
2406
2407
2408 G_PRVW_REQUEST_ID := p_event.getValueForParameter('PREVIEW_REQUEST_ID');
2409
2410 G_GLOBAL_NO_OF_PERIOD := p_event.getValueForParameter('GLOBAL_NO_OF_PERIOD');
2411
2412 -- First Update the Status to ACTIVE
2413 UPDATE AMS_TCOP_PRVW_REQUESTS
2414 SET STATUS='ACTIVE',
2415 LAST_UPDATE_DATE = sysdate,
2416 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
2417 WHERE REQUEST_ID=G_PRVW_REQUEST_ID;
2418
2419
2420 PREVIEW_FATIGUE(l_list_header_id);
2421
2422 return 'SUCCESS';
2423
2424 EXCEPTION
2425
2426 WHEN OTHERS THEN
2427
2428 WF_CORE.CONTEXT('AMS_TCOP_PREVIEW','REFRESH',
2429 p_event.getEventName( ), p_subscription_guid);
2430 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2431
2432 -- First Update the Status to ERROR
2433 UPDATE AMS_TCOP_PRVW_REQUESTS
2434 SET STATUS='ERROR',
2435 LAST_UPDATE_DATE = sysdate,
2436 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
2437 WHERE REQUEST_ID=G_PRVW_REQUEST_ID;
2438
2439 RETURN 'ERROR';
2440
2441 END REFRESH;
2442
2443
2444 -- ===============================================================
2445 -- Start of Comments
2446 -- Name
2447 -- REFRESH
2448 --
2449 -- Purpose
2450 -- This function is called from Business Event raised through
2451 -- Post Target Group Generation Business Event
2452 -- ===============================================================
2453 FUNCTION FORCE_REFRESH(p_subscription_guid IN RAW,
2454 p_event IN OUT NOCOPY WF_EVENT_T
2455 ) RETURN VARCHAR2
2456 IS
2457
2458 CURSOR C_PREVIEW_SIZE (p_list_header_id NUMBER)
2459 IS
2460 SELECT COUNT(PARTY_ID)
2461 FROM AMS_LIST_ENTRIES
2465 --Local Variables
2462 WHERE LIST_HEADER_ID = p_list_header_id
2463 AND ENABLED_FLAG='Y';
2464
2466 l_list_header_id NUMBER;
2467 l_total_preview_count NUMBER;
2468
2469 BEGIN
2470 -- Get the Value of LIST_HEADER_ID
2471 l_list_header_id := p_event.getValueForParameter('LIST_HEADER_ID');
2472 -- Get the Preview Size
2473 OPEN C_PREVIEW_SIZE (l_list_header_id);
2474 FETCH C_PREVIEW_SIZE
2475 INTO l_total_preview_count;
2476 CLOSE C_PREVIEW_SIZE;
2477
2478
2479
2480 -- Create Preview Request
2481 CREATE_PREVIEW_REQUEST(l_list_header_id,l_total_preview_count);
2482
2483 -- First Update the Status to ACTIVE
2484
2485
2486
2487 UPDATE AMS_TCOP_PRVW_REQUESTS
2488 SET STATUS='ACTIVE',
2489 LAST_UPDATE_DATE = sysdate,
2490 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
2491 WHERE REQUEST_ID=G_PRVW_REQUEST_ID;
2492 PREVIEW_FATIGUE(l_list_header_id);
2493 return 'SUCCESS';
2494
2495 EXCEPTION
2496
2497 WHEN OTHERS THEN
2498
2499 WF_CORE.CONTEXT('AMS_TCOP_PREVIEW','REFRESH',
2500 p_event.getEventName( ), p_subscription_guid);
2501 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2502
2503 -- First Update the Status to ERROR
2504 UPDATE AMS_TCOP_PRVW_REQUESTS
2505 SET STATUS='ERROR',
2506 LAST_UPDATE_DATE = sysdate,
2507 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
2508 WHERE REQUEST_ID=G_PRVW_REQUEST_ID;
2509
2510 RETURN 'ERROR';
2511
2512 END FORCE_REFRESH;
2513
2514
2515 -- ===============================================================
2516 -- Start of Comments
2517 -- Name
2518 -- GENERATE_PREVIEW
2519 --
2520 -- Purpose
2521 -- This function is called for preprocessing the preview request
2522 -- handling through Preview UI.
2523 -- Parameters
2524 -- p_list_header_id IN ==> The List Header Id for which preview
2525 -- needs to be generated
2526 -- ===============================================================
2527 PROCEDURE REGENERATE_PREVIEW( p_list_header_id IN NUMBER)
2528 IS
2529
2530 CURSOR C_LIST_PRVW_REQUEST_STATUS
2531 IS
2532 SELECT REQUEST_ID, STATUS
2533 FROM AMS_TCOP_PRVW_REQUESTS
2534 WHERE LIST_HEADER_ID = p_list_header_id
2535 and REQUEST_ID = (select max(REQUEST_ID) from AMS_TCOP_PRVW_REQUESTS where LIST_HEADER_ID = p_list_header_id);
2536
2537 CURSOR C_PREVIEW_SIZE
2538 IS
2539 SELECT COUNT(PARTY_ID)
2540 FROM AMS_LIST_ENTRIES
2541 WHERE LIST_HEADER_ID = p_list_header_id
2542 AND ENABLED_FLAG='Y';
2543
2544 l_total_preview_count NUMBER;
2545 l_request_id NUMBER;
2546 l_status VARCHAR2(30);
2547 l_raise_event_flag BOOLEAN;
2548
2549 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
2550 l_event_key VARCHAR2(30);
2551
2552 BEGIN
2553
2554 l_request_id := -1;
2555 l_status := NULL;
2556 l_raise_event_flag := TRUE;
2557
2558 -- Get The Request Id and the Status
2559 OPEN C_LIST_PRVW_REQUEST_STATUS;
2560 FETCH C_LIST_PRVW_REQUEST_STATUS
2561 INTO l_request_id,l_status;
2562 CLOSE C_LIST_PRVW_REQUEST_STATUS;
2563
2564 IF (l_request_id = -1)
2565 THEN
2566 l_raise_event_flag := TRUE;
2567 END IF;
2568
2569 IF ((l_status = 'NEW') OR (l_status = 'ACTIVE'))
2570 THEN
2571 BEGIN
2572 l_raise_event_flag := FALSE;
2573 END;
2574 ELSE
2575 l_raise_event_flag := TRUE;
2576 END IF;
2577
2578 IF (l_raise_event_flag = TRUE)
2579 THEN
2580
2581 BEGIN
2582 -- Get the Preview Size
2583 OPEN C_PREVIEW_SIZE;
2584 FETCH C_PREVIEW_SIZE
2585 INTO l_total_preview_count;
2586 CLOSE C_PREVIEW_SIZE;
2587
2588
2589 -- Create Preview Request
2590 CREATE_PREVIEW_REQUEST(p_list_header_id,l_total_preview_count);
2591
2592
2593 wf_event.AddParameterToList(p_name => 'LIST_HEADER_ID',
2594 p_value => to_char(p_list_header_id),
2595 p_parameterlist => l_parameter_list);
2596
2597 wf_event.AddParameterToList(p_name => 'PREVIEW_REQUEST_ID',
2598 p_value => to_char(G_PRVW_REQUEST_ID),
2599 p_parameterlist => l_parameter_list);
2600
2601
2602 select to_char(sysdate, 'YYYYMMDDHH24MISS') into l_event_key from dual;
2603
2604 l_event_key := to_char(p_list_header_id) || '_' || l_event_key;
2605
2606 wf_event.raise( p_event_name => 'oracle.apps.ams.tcop.RefreshPreview',
2607 p_event_key => l_event_key,
2608 p_parameters => l_parameter_list);
2609
2610 l_parameter_list.DELETE;
2611
2612 commit;
2613
2614
2615 END;
2616
2617 END IF;
2618
2619
2620 END REGENERATE_PREVIEW;
2621
2622 --- Added
2623
2624
2625 -- ===============================================================
2626 -- Start of Comments
2627 -- Name
2628 -- GENERATE_PREVIEW
2629 --
2630 -- Purpose
2631 -- This function is called for preprocessing the preview request
2632 -- handling through Preview UI.
2633 -- Parameters
2634 -- p_list_header_id IN ==> The List Header Id for which preview
2635 -- needs to be generated
2636 -- ===============================================================
2637 PROCEDURE REGENERATE_PREVIEW_DAYS( p_list_header_id IN NUMBER , p_no_of_days IN NUMBER)
2638 IS
2639
2640 CURSOR C_LIST_PRVW_REQUEST_STATUS
2641 IS
2642 SELECT REQUEST_ID, STATUS
2643 FROM AMS_TCOP_PRVW_REQUESTS
2644 WHERE LIST_HEADER_ID = p_list_header_id
2645 and REQUEST_ID = (select max(REQUEST_ID) from AMS_TCOP_PRVW_REQUESTS where LIST_HEADER_ID = p_list_header_id);
2646
2647 CURSOR C_PREVIEW_SIZE
2648 IS
2649 SELECT COUNT(PARTY_ID)
2650 FROM AMS_LIST_ENTRIES
2651 WHERE LIST_HEADER_ID = p_list_header_id
2652 AND ENABLED_FLAG='Y';
2653
2654 l_total_preview_count NUMBER;
2655 l_request_id NUMBER;
2656 l_status VARCHAR2(30);
2657 l_raise_event_flag BOOLEAN;
2658
2659 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
2660 l_event_key VARCHAR2(30);
2661
2662 BEGIN
2663
2664 l_request_id := -1;
2665 l_status := NULL;
2666 l_raise_event_flag := TRUE;
2667
2668 G_GLOBAL_NO_OF_PERIOD := p_no_of_days;
2669 -- Get The Request Id and the Status
2670 OPEN C_LIST_PRVW_REQUEST_STATUS;
2671 FETCH C_LIST_PRVW_REQUEST_STATUS
2672 INTO l_request_id,l_status;
2673 CLOSE C_LIST_PRVW_REQUEST_STATUS;
2674
2675
2676 IF (l_request_id = -1)
2677 THEN
2678 l_raise_event_flag := TRUE;
2679 END IF;
2680
2681 IF ((l_status = 'NEW') OR (l_status = 'ACTIVE'))
2682 THEN
2683 BEGIN
2684 l_raise_event_flag := FALSE;
2685 END;
2686 ELSE
2687 l_raise_event_flag := TRUE;
2688 END IF;
2689
2690 IF (l_raise_event_flag = TRUE)
2691 THEN
2692
2693 BEGIN
2694 -- Get the Preview Size
2695 OPEN C_PREVIEW_SIZE;
2696 FETCH C_PREVIEW_SIZE
2697 INTO l_total_preview_count;
2698 CLOSE C_PREVIEW_SIZE;
2699
2700
2701 -- Create Preview Request
2702
2703
2704 CREATE_PREVIEW_REQUEST(p_list_header_id,l_total_preview_count);
2705
2706
2707 wf_event.AddParameterToList(p_name => 'LIST_HEADER_ID',
2708 p_value => to_char(p_list_header_id),
2709 p_parameterlist => l_parameter_list);
2710
2711 wf_event.AddParameterToList(p_name => 'PREVIEW_REQUEST_ID',
2712 p_value => to_char(G_PRVW_REQUEST_ID),
2713 p_parameterlist => l_parameter_list);
2714
2715 wf_event.AddParameterToList(p_name => 'GLOBAL_NO_OF_PERIOD',
2716 p_value => to_char(G_GLOBAL_NO_OF_PERIOD),
2717 p_parameterlist => l_parameter_list);
2718
2719 select to_char(sysdate, 'YYYYMMDDHH24MISS') into l_event_key from dual;
2720
2721 l_event_key := to_char(p_list_header_id) || '_' || l_event_key;
2722
2723 wf_event.raise( p_event_name => 'oracle.apps.ams.tcop.RefreshPreview',
2724 p_event_key => l_event_key,
2725 p_parameters => l_parameter_list);
2726
2727 l_parameter_list.DELETE;
2728
2729 commit;
2730
2731
2732 END;
2733
2734 END IF;
2735
2736
2737 END REGENERATE_PREVIEW_DAYS;
2738
2739 END AMS_TCOP_PREVIEW;
[Home] [Help]