1 PACKAGE BODY AMS_TCOP_SUMMARIZATION_PKG AS
2 /* $Header: amsvtcmb.pls 115.2 2004/05/18 11:20:36 mayjain noship $ */
3
4 -- Global Constants that will be used through out the package
5 LOG_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 LOG_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 LOG_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8 LOG_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 LOG_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
10 LOG_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11
12 TYPE NUMBER_ARRAY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
13 -- ===============================================================
14 -- Start of Comments
15 -- Package name
16 -- AMS_TCOP_SUMMARIZATION_PKG
17 -- Purpose
18 --
19 -- This package contains all the program units for summarizing
20 -- contacts made through fatigue schedules
21 --
22 -- History
23 --
24 -- NOTE
25 --
26 -- End of Comments
27 -- Private procedure to write debug message to FND_LOG table
28 --
29 PROCEDURE write_debug_message(p_log_level NUMBER,
30 p_procedure_name VARCHAR2,
31 p_label VARCHAR2,
32 p_text VARCHAR2
33 )
34 IS
35 l_module_name VARCHAR2(400);
36 DELIMETER CONSTANT VARCHAR2(1) := '.';
37 LABEL_PREFIX CONSTANT VARCHAR2(30) := 'TCOPContactSummarization';
38
39 BEGIN
40
41 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
42 -- Set the Module Name
43 l_module_name := 'ams'||DELIMETER||'plsql'||DELIMETER||G_PACKAGE_NAME||DELIMETER||p_procedure_name||DELIMETER||LABEL_PREFIX||'-'||p_label;
44
45
46 -- Log the Message
47 AMS_UTILITY_PVT.debug_message(p_log_level,
48 l_module_name,
49 p_text
50 );
51
52 END IF;
53
54 AMS_UTILITY_PVT.write_conc_log('['||G_PACKAGE_NAME||DELIMETER||p_procedure_name||DELIMETER||LABEL_PREFIX||'-'||p_label||']'||p_text);
55 --dbms_output.put_line(p_label||': '||p_text);
56
57
58 END write_debug_message;
59 -- ===============================================================
60 -- Start of Comments
61 -- Name
62 -- DELETE_CONTACT_SUMMARIZATION
63 --
64 -- Purpose
65 -- This procedure deletes contact summarization information for all the
66 -- parties present in the given a list
67 --
68 PROCEDURE DELETE_CONTACT_SUMMARIZATION(p_list_header_id NUMBER,
69 p_activity_id NUMBER
70 )
71 IS
72 BEGIN
73
74 delete from ams_tcop_contact_sum_dtl
75 where contact_summary_id in
76 (select summary.contact_summary_id
77 from ams_tcop_contact_summary summary,
78 ams_list_entries list_entry
79 where list_entry.enabled_flag = 'Y'
80 and list_entry.list_header_id = p_list_header_id
81 and list_entry.party_id = summary.party_id );
82
83 delete from ams_tcop_contact_summary
84 where party_id in
85 (select party_id
86 from ams_list_entries
87 where enabled_flag = 'Y'
88 and list_header_id = p_list_header_id);
89
90 -- Delete the records for (party_id,media_id) combination
91 delete from ams_tcop_channel_sum_dtl
92 where channel_summary_id in
93 (select summary.channel_summary_id
94 from ams_tcop_channel_summary summary,
95 ams_list_entries list_entry
96 where list_entry.party_id = summary.party_id
97 and list_entry.list_header_id = p_list_header_id
98 and summary.media_id = p_activity_id
99 and list_entry.enabled_flag = 'Y');
100
101 delete from ams_tcop_channel_summary
102 where party_id in
103 (select summary.PARTY_ID
104 from ams_tcop_channel_summary summary,
105 ams_list_entries list_entry
106 where list_entry.party_id = summary.party_id
107 and list_entry.list_header_id = p_list_header_id
108 and summary.media_id = p_activity_id
109 and list_entry.enabled_flag = 'Y');
110
111
112 END DELETE_CONTACT_SUMMARIZATION;
113
114 -- ===============================================================
115 -- Start of Comments
116 -- Name
117 -- SUMMARIZE_LIST_CONTACTS
118 --
119 -- Purpose
120 -- This procedure considers the set of parties available in the given Target Group.
121 -- For these parties, it summarizes the number of contacts made by fatiguing schedules in the periods
122 -- specified in the Fatigue Rule Setup
123 --
124 PROCEDURE SUMMARIZE_LIST_CONTACTS( p_list_header_id NUMBER,
125 p_activity_id NUMBER
126 )
127 IS
128
129 -- Check if there are any global rules
130 CURSOR C_GET_GLOBAL_RULE
131 IS
132 SELECT RULE.RULE_ID
133 ,PERIOD.NO_OF_DAYS
134 FROM AMS_TCOP_FR_RULES_SETUP RULE
135 ,AMS_TCOP_FR_PERIODS_B period
136 WHERE RULE.RULE_TYPE='GLOBAL'
137 AND RULE.ENABLED_FLAG = 'Y'
138 AND RULE.PERIOD_ID = PERIOD.PERIOD_ID;
139
140 -- Check if there are any Channel Rules for activity_id of the schedule
141 CURSOR C_GET_CHANNEL_RULE
142 IS
143 SELECT rule.RULE_ID,period.no_of_days
144 FROM AMS_TCOP_FR_RULES_SETUP rule
145 ,AMS_TCOP_FR_PERIODS_B period
146 WHERE rule.RULE_TYPE = 'CHANNEL_BASED'
147 AND rule.CHANNEL_ID = p_activity_id
148 AND rule.ENABLED_FLAG = 'Y'
149 AND rule.period_id = period.period_id;
150
151 -- Get the list of fatiguing schedules along with channel information
152 -- which contacted parties in the timeframe specified in the Channel Rule
153 CURSOR C_GET_CHANNEL_SUMMARY(p_list_header_id NUMBER,
154 p_activity_id NUMBER,
155 p_no_of_days NUMBER)
156 IS
157 SELECT contact.party_id,
158 contact.schedule_id
159 FROM AMS_TCOP_CONTACTS contact, AMS_LIST_ENTRIES list_entry
160 WHERE contact.MEDIA_ID = p_activity_id
161 AND (contact_date between (sysdate - p_no_of_days) and sysdate)
162 AND contact.PARTY_ID = list_entry.PARTY_ID
163 AND list_entry.list_header_id = p_list_header_id
164 AND list_entry.enabled_flag = 'Y'
165 order by contact.party_id;
166
167 -- Cursor to select the list of fatiguing schedules which contacted parties
168 -- during the time frame set in the Global Rule
169 CURSOR C_GET_GLOBAL_SUMMARY (p_list_header_id NUMBER, p_no_of_days NUMBER)
170 IS
171 SELECT contact.party_id,
172 contact.schedule_id
173 FROM AMS_TCOP_CONTACTS contact, AMS_LIST_ENTRIES list_entry
174 WHERE (contact_date between (sysdate - p_no_of_days) and sysdate)
175 AND contact.PARTY_ID = list_entry.PARTY_ID
176 AND list_entry.list_header_id = p_list_header_id
177 AND list_entry.enabled_flag = 'Y'
178 order by contact.party_id;
179
180
181 CURSOR C_GET_CONTACT_SUM_NEXT_SEQ
182 IS
183 SELECT AMS_TCOP_CONTACT_SUM_S.nextval
184 FROM DUAL;
185
186 --CURSOR C_GET_CONTACT_SUM_DTL_NEXT_SEQ
187 --IS
188 --SELECT AMS_TCOP_CONTACT_SUM_DTL_S.nextval
189 --FROM DUAL;
190
191 CURSOR C_GET_CHNL_SUM_NEXT_SEQ
192 IS
193 SELECT AMS_TCOP_CHNL_SUM_S.nextval
194 FROM DUAL;
195
196 CURSOR C_GET_CHNL_SUM_DTL_NEXT_SEQ
197 IS
198 SELECT AMS_TCOP_CHNL_SUM_DTL_S.nextval
199 FROM DUAL;
200
201 TYPE NUMBER_ARRAY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
202
203 -- Get the list of schedules which fatigued the parties
204
205 -- Local Variables
206 l_rule_id NUMBER;
207 l_last_party_id NUMBER;
208 l_media_id NUMBER;
209 j NUMBER;
210 k NUMBER;
211 l_sequence_id NUMBER;
212 l_total_contact_count NUMBER;
213 l_chnl_sum_id NUMBER;
214 l_contact_sum_id NUMBER;
215 l_total_fatigue_contact NUMBER;
216 l_num_days_global_rule NUMBER;
217 l_last_media_id NUMBER;
218 l_num_days_channel_rule NUMBER;
219
220 -- Local Arrays
221 l_temp_party_id_list NUMBER_ARRAY;
222 l_schedule_id_list NUMBER_ARRAY;
223 l_contact_count_list NUMBER_ARRAY;
224 l_temp_media_id_list NUMBER_ARRAY;
225 l_temp_rule_id_list NUMBER_ARRAY;
226 l_chnl_sum_id_list NUMBER_ARRAY;
227 L_CHNL_SUM_DTL_ID_LIST NUMBER_ARRAY;
228 L_CHNL_SUM_ID_LIST_FOR_DTL NUMBER_ARRAY;
229 l_contact_sum_id_list NUMBER_ARRAY;
230 --l_contact_sum_id_dtl_list NUMBER_ARRAY;
231 l_contact_sum_id_list_for_dtl NUMBER_ARRAY;
232 l_total_count_list NUMBER_ARRAY;
233 l_final_party_id_list NUMBER_ARRAY;
234 l_final_media_id_list NUMBER_ARRAY;
235 l_final_rule_id_list NUMBER_ARRAY;
236 l_final_schedule_id_list NUMBER_ARRAY;
237
238
239 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'SUMMARIZE_LIST_CONTACTS';
240
241 BEGIN
242
243 write_debug_message(LOG_LEVEL_PROCEDURE,
244 PROCEDURE_NAME,
245 'BEGIN',
246 'Beginning Procedure'
247 );
248
249 write_debug_message(LOG_LEVEL_PROCEDURE,
250 PROCEDURE_NAME,
251 'WRITE_INPUT_PARAMETERS',
252 'Summarize Contacts for Target Group. List Header Id = '||to_char(p_list_header_id)
253 );
254
255 write_debug_message(LOG_LEVEL_PROCEDURE,
256 PROCEDURE_NAME,
257 'Before Calling Delete_Contact_Summarization',
258 'Before Calling Delete_Contact_Summarization'
259 );
260 -- Delete from Summarization Tables all the previous entries
261 DELETE_CONTACT_SUMMARIZATION(p_list_header_id,p_activity_id);
262
263 write_debug_message(LOG_LEVEL_EVENT,
264 PROCEDURE_NAME,
265 'After calling DELETE_CONTACT_SUMMARIZATION ',
266 'All the entries deleted from summarization tables'
267 );
268
269 -- Check if there are any Global Rules
270 OPEN C_GET_GLOBAL_RULE;
271 FETCH C_GET_GLOBAL_RULE
272 INTO l_rule_id,l_num_days_global_rule;
273 CLOSE C_GET_GLOBAL_RULE;
274
275 IF (l_rule_id IS NOT NULL) THEN
276
277 write_debug_message(LOG_LEVEL_EVENT,
278 PROCEDURE_NAME,
279 'Global Rule Id is not null',
280 'Global Rule Id = '||to_char(l_rule_id)
281 );
282
283 write_debug_message(LOG_LEVEL_EVENT,
284 PROCEDURE_NAME,
285 'Global Rule Id is not null',
286 ' No of days specified in Global Rule = '||to_char(l_num_days_global_rule)
287 );
288 -- A Global Fatigue Rule is setup in the system
289 -- Get the Total Number of Fatiguing Contacts made in the time frame
290 -- specified in the Global Fatigue Rule
291 OPEN C_GET_GLOBAL_SUMMARY(p_list_header_id,l_num_days_global_rule);
292 FETCH C_GET_GLOBAL_SUMMARY
293 BULK COLLECT INTO l_temp_party_id_list,l_schedule_id_list;
294 CLOSE C_GET_GLOBAL_SUMMARY;
295
296 l_total_fatigue_contact := l_temp_party_id_list.COUNT;
297
298 write_debug_message(LOG_LEVEL_EVENT,
299 PROCEDURE_NAME,
300 'AFTER BULK FETCH FROM C_GET_GLOBAL_SUMMARY CURSOR ',
301 'Total Number of Fatiguing entries = '||to_char(l_total_fatigue_contact)
302 );
303
304 -- Get the List of Schedules which contacted them in the time period
305 -- specified in the Global Fatigue Rule
306 IF (l_total_fatigue_contact > 0) THEN
307 -- Populate arrays to do a Bulk insert for AMS_TCOP_CONTACT_SUMMARY
308
309 -- Initialize Loop Variables
310 l_last_party_id := 0;
311 l_total_contact_count := 0;
312 FOR i IN l_temp_party_id_list.FIRST .. l_temp_party_id_list.LAST
313 LOOP
314 IF (l_temp_party_id_list(i) <> l_last_party_id) THEN
315
316 -- Set the Party Id
317 j := l_final_party_id_list.COUNT + 1;
318 l_final_party_id_list(j) := l_temp_party_id_list(i);
319
320 -- Set the contact_summary_id
321 OPEN C_GET_CONTACT_SUM_NEXT_SEQ;
322 FETCH C_GET_CONTACT_SUM_NEXT_SEQ
323 INTO l_contact_sum_id;
324 CLOSE C_GET_CONTACT_SUM_NEXT_SEQ;
325 l_contact_sum_id_list(j) := l_contact_sum_id;
326
327 -- Assign the total contact count
328 l_contact_count_list(j) := 1;
329
330 ELSE
331
332 -- Increment the total contact count
333 l_contact_count_list(j) := l_contact_count_list(j) + 1;
334
335 END IF;
336
337 -- Set the value for AMS_TCOP_CONTACT_SUM_DTL.contact_summary_id
338 l_contact_sum_id_list_for_dtl(i) := l_contact_sum_id;
339
340 --OPEN C_GET_CONTACT_SUM_DTL_NEXT_SEQ;
344
341 --FETCH C_GET_CONTACT_SUM_DTL_NEXT_SEQ
342 --INTO l_sequence_id;
343 --CLOSE C_GET_CONTACT_SUM_DTL_NEXT_SEQ;
345 -- AMS_TCOP_CONTACT_SUM_DTL.summary_dtl_id
346 --l_contact_sum_id_dtl_list (i) := l_sequence_id;
347
348
349 -- set Loop variables
350 l_last_party_id := l_temp_party_id_list(i);
351
352 END LOOP;
353
354 write_debug_message(LOG_LEVEL_EVENT,
355 PROCEDURE_NAME,
356 'BEFORE BULK UPLOADING AMS_TCOP_CONTACT_SUMMARY',
357 'Total Number of records to be loaded = '||to_char(l_final_party_id_list.COUNT));
358
359 -- Do Bulk Insert into AMS_TCOP_CONTACT_SUMMARY
360 FORALL i in l_final_party_id_list.FIRST .. l_final_party_id_list.LAST
361 INSERT INTO
362 AMS_TCOP_CONTACT_SUMMARY
363 (
364 CONTACT_SUMMARY_ID,
365 RULE_ID,
366 PARTY_ID,
367 TOTAL_CONTACTS,
368 CREATION_DATE,
369 CREATED_BY,
370 LAST_UPDATE_DATE,
371 LAST_UPDATED_BY,
372 LAST_UPDATE_LOGIN
373 )
374 VALUES
375 (
376 l_contact_sum_id_list(i),
377 l_rule_id,
378 l_final_party_id_list(i),
379 l_contact_count_list(i),
380 sysdate,
381 FND_GLOBAL.USER_ID,
382 sysdate,
383 FND_GLOBAL.USER_ID,
384 FND_GLOBAL.USER_ID
385 );
386
387 write_debug_message(LOG_LEVEL_EVENT,
388 PROCEDURE_NAME,
389 'AFTER BULK UPLOADING AMS_TCOP_CONTACT_SUMMARY',
390 'BULK UPLOAD COMPLETED SUCCESSFULLY');
391
392 write_debug_message(LOG_LEVEL_EVENT,
393 PROCEDURE_NAME,
394 'BEFORE BULK UPLOADING AMS_TCOP_CONTACT_SUM_DTL',
395 'Total Number of records to be loaded = '||to_char(l_schedule_id_list.COUNT));
396
397 -- Do Bulk Insert into AMS_TCOP_CONTACT_SUM_DTL
398 FORALL i in l_schedule_id_list.FIRST .. l_schedule_id_list.LAST
399 INSERT INTO
400 AMS_TCOP_CONTACT_SUM_DTL
401 (
402 SUMMARY_DTL_ID,
403 CONTACT_SUMMARY_ID,
404 SCHEDULE_ID,
405 CREATION_DATE,
406 CREATED_BY,
407 LAST_UPDATE_DATE,
408 LAST_UPDATED_BY,
409 LAST_UPDATE_LOGIN
410 )
411 VALUES
412 (
413 --l_contact_sum_id_dtl_list(i),
414 AMS_TCOP_CONTACT_SUM_DTL_S.nextval,
415 l_contact_sum_id_list_for_dtl(i),
416 l_schedule_id_list(i),
417 sysdate,
418 FND_GLOBAL.USER_ID,
419 sysdate,
420 FND_GLOBAL.USER_ID,
421 FND_GLOBAL.USER_ID
422 );
423
424 write_debug_message(LOG_LEVEL_EVENT,
425 PROCEDURE_NAME,
426 'AFTER BULK UPLOADING AMS_TCOP_CONTACT_SUM_DTL',
427 'BULK UPLOAD COMPLETED SUCCESSFULLY');
428
429 END IF; /* (l_party_id_list.COUNT = 0) */
430
431
432 END IF; /* Global Rule Check */
433
434 -- Some of ther collection variables will be re-used, so reset them
435 l_temp_party_id_list.DELETE;
436 l_contact_count_list.DELETE;
437 l_final_party_id_list.DELETE;
438 l_schedule_id_list.DELETE;
439
440
441
442 OPEN C_GET_CHANNEL_RULE;
443 FETCH C_GET_CHANNEL_RULE
444 INTO l_rule_id,l_num_days_channel_rule;
445 CLOSE C_GET_CHANNEL_RULE;
446
447 IF (l_rule_id IS NOT NULL) THEN
448 -- Get the list of parties and contacted channel information
449 -- within the timeperiod specified in the channel rule
450 write_debug_message(LOG_LEVEL_EVENT,
451 PROCEDURE_NAME,
452 'CHANNEL RULE NOT NULL',
453 'Channel Rule Id = '||to_char(l_rule_id)
454 );
455
456 OPEN C_GET_CHANNEL_SUMMARY(p_list_header_id,p_activity_id,
457 l_num_days_channel_rule);
458 FETCH C_GET_CHANNEL_SUMMARY
459 BULK COLLECT INTO
460 l_temp_party_id_list,
461 l_schedule_id_list;
462 CLOSE C_GET_CHANNEL_SUMMARY;
463
464 write_debug_message(LOG_LEVEL_EVENT,
465 PROCEDURE_NAME,
466 'AFTER BULK FETCH FROM C_GET_CHANNEL_SUMMARY CURSOR',
467 'Total Number of contacts by channel = '||to_char(l_temp_party_id_list.COUNT));
468
469 IF (l_temp_party_id_list.COUNT > 0) THEN
470 -- There are some parties contacted by schedules
471 write_debug_message(LOG_LEVEL_EVENT,
472 PROCEDURE_NAME,
473 'CHANNEL CONTACT COUNT GREATER THAN ZERO',
474 'Total number of contacts made by channel greater than zero and the count = '||to_char(l_temp_party_id_list.COUNT)
475 );
476
477 -- Initialize some of the Loop variables
478 l_last_party_id := -1;
479 l_last_media_id := -1;
480
481 FOR i IN l_temp_party_id_list.FIRST .. l_temp_party_id_list.LAST
482 LOOP
483 IF ( (l_temp_party_id_list(i) = l_last_party_id) ) THEN
484
485 -- Increment the count
489 ELSE
486 l_contact_count_list(j) := l_contact_count_list(j) +1;
487
488
490 -- Initialize the Arrays to do a bulk upload for AMS_TCOP_CHANNEL_SUMMARY
491 j := l_final_party_id_list.count + 1;
492 l_final_party_id_list (j) := l_temp_party_id_list(i);
493 l_contact_count_list(j) := 1; -- the count starts
494
495 -- Set the sequence value for channel_summary_id
496 OPEN C_GET_CHNL_SUM_NEXT_SEQ;
497 FETCH C_GET_CHNL_SUM_NEXT_SEQ INTO l_chnl_sum_id;
498 CLOSE C_GET_CHNL_SUM_NEXT_SEQ;
499
500 l_chnl_sum_id_list(j) := l_chnl_sum_id;
501
502 END IF;
503
504 -- Set Values for AMS_TCOP_CHANNEL_SUM_DTL
505 k := l_chnl_sum_dtl_id_list.count + 1;
506
507 -- Set the sequence value for channel_sum_dtl_id
508 OPEN C_GET_CHNL_SUM_DTL_NEXT_SEQ;
509 FETCH C_GET_CHNL_SUM_DTL_NEXT_SEQ INTO l_sequence_id;
510 CLOSE C_GET_CHNL_SUM_DTL_NEXT_SEQ;
511
512 -- Set values for bulk loading of AMS_TCOP_CHANNEL_SUM_DTL
513 l_chnl_sum_dtl_id_list(k) := l_sequence_id;
514 l_chnl_sum_id_list_for_dtl(k) := l_chnl_sum_id;
515
516 -- Set the last party id and media id
517 l_last_party_id := l_temp_party_id_list(i);
518
519
520 END LOOP;
521
522 write_debug_message(LOG_LEVEL_EVENT,
523 PROCEDURE_NAME,
524 'BEFORE_BULK_UPLOADING_AMS_TCOP_CHANNEL_SUMMARY',
525 'Total number of record uploaded = '||to_char(l_final_party_id_list.count)
526 );
527
528 /* Bulk Upload AMS_TCOP_CHANNEL_SUMMARY */
529 FORALL i in l_final_party_id_list.FIRST .. l_final_party_id_list.LAST
530 INSERT INTO
531 AMS_TCOP_CHANNEL_SUMMARY
532 (
533 CHANNEL_SUMMARY_ID,
534 RULE_ID,
535 PARTY_ID,
536 MEDIA_ID,
537 TOTAL_CONTACTS,
538 CREATION_DATE,
539 CREATED_BY,
540 LAST_UPDATE_DATE,
541 LAST_UPDATED_BY,
542 LAST_UPDATE_LOGIN
543 )
544 VALUES
545 (
546 l_chnl_sum_id_list(i),
547 l_rule_id,
548 l_final_party_id_list(i),
549 p_activity_id,
550 l_contact_count_list(i),
551 sysdate,
552 FND_GLOBAL.USER_ID,
553 sysdate,
554 FND_GLOBAL.USER_ID,
555 FND_GLOBAL.USER_ID
556 );
557
558 /* Bulk Upload AMS_TCOP_CHANNEL_SUM_DTL */
559 FORALL i in l_chnl_sum_dtl_id_list.FIRST .. l_chnl_sum_dtl_id_list.LAST
560 INSERT INTO
561 AMS_TCOP_CHANNEL_SUM_DTL
562 (
563 CHANNEL_SUM_DTL_ID,
564 CHANNEL_SUMMARY_ID,
565 SCHEDULE_ID,
566 CREATION_DATE,
567 CREATED_BY,
568 LAST_UPDATE_DATE,
569 LAST_UPDATED_BY,
570 LAST_UPDATE_LOGIN
571 )
572 VALUES
573 (
574 l_chnl_sum_dtl_id_list(i),
575 l_chnl_sum_id_list_for_dtl(i),
576 l_schedule_id_list(i),
577 sysdate,
578 FND_GLOBAL.USER_ID,
579 sysdate,
580 FND_GLOBAL.USER_ID,
581 FND_GLOBAL.USER_ID
582 );
583
584 END IF;
585
586 ELSE
587
588 write_debug_message(LOG_LEVEL_EVENT,
589 PROCEDURE_NAME,
590 'NO CHANNEL RULE EXISTS',
591 'NO Summarization needed for channel rules'
592 );
593
594
595 END IF;/* l_rule_id IS null */
596
597
598 END SUMMARIZE_LIST_CONTACTS;
599
600
601 -- ===============================================================
602 -- Start of Comments
603 -- Name
604 -- UPDATE_CONTACT_COUNT
605 --
606 -- Purpose
607 -- This procedure updates contact count for all the contacted parties
608 --
609 PROCEDURE UPDATE_CONTACT_COUNT(p_party_id_list JTF_NUMBER_TABLE
610 ,p_schedule_id NUMBER
611 ,p_activity_id NUMBER
612 ,p_global_rule_id NUMBER
613 ,p_channel_rule_id NUMBER
614 )
615
616 IS
617 -- Verify if new entries need to be created in AMS_TCOP_CONTACT_SUMMARY
618 -- and in AMS_TCOP_CONTACT_SUM_DTL
619 CURSOR C_GET_EXISTING_PARTY (p_party_id_list JTF_NUMBER_TABLE)
620 IS
621 SELECT summary.PARTY_ID,summary.contact_summary_id
622 FROM AMS_TCOP_CONTACT_SUMMARY summary,
623 (SELECT column_value party_id
624 FROM TABLE(CAST(p_party_id_List as JTF_NUMBER_TABLE))
625 ) party_list
626 WHERE summary.party_id=party_list.party_id;
627
628 CURSOR C_GET_NEW_PARTY (p_original_party_id_list JTF_NUMBER_TABLE
629 ,p_existing_party_id_list JTF_NUMBER_TABLE
630 )
631 IS
632 SELECT orig_party_list.PARTY_ID
633 FROM (SELECT column_value party_id
634 FROM TABLE(CAST(p_original_party_id_list as JTF_NUMBER_TABLE))
638 FROM TABLE(CAST(p_existing_party_id_list as JTF_NUMBER_TABLE))
635 ) orig_party_list
636 WHERE orig_party_list.PARTY_ID NOT IN
637 (SELECT column_value
639 );
640
641 CURSOR C_GET_SUM_DTL_SEQ
642 IS
643 SELECT AMS_TCOP_CONTACT_SUM_DTL_S.NEXTVAL
644 FROM DUAL;
645
646 CURSOR C_GET_CONTACT_SUM_SEQ
647 IS
648 SELECT AMS_TCOP_CONTACT_SUM_S.NEXTVAL
649 FROM DUAL;
650
651 CURSOR C_GET_CHNL_SUM_DTL_SEQ
652 IS
653 SELECT AMS_TCOP_CHNL_SUM_DTL_S.NEXTVAL
654 FROM DUAL;
655
656 CURSOR C_GET_CHNL_SUM_SEQ
657 IS
658 SELECT AMS_TCOP_CHNL_SUM_S.NEXTVAL
659 FROM DUAL;
660
661 -- Get existing entries from AMS_TCOP_CHANNEL_SUMMARY
662 CURSOR C_GET_EXISTING_PARTY_CHNL (p_party_id_list JTF_NUMBER_TABLE
663 ,p_activity_id NUMBER
664 )
665 IS
666 SELECT summary.PARTY_ID,summary.channel_summary_id
667 FROM AMS_TCOP_CHANNEL_SUMMARY summary,
668 (SELECT column_value party_id
669 FROM TABLE(CAST(p_party_id_List as JTF_NUMBER_TABLE))
670 ) party_list
671 WHERE summary.party_id=party_list.party_id
672 AND summary.media_id=p_activity_id;
673
674 -- Get the list of parties which already have a row in
675 -- AMS_TCOP_CONTACT_SUMMARY and in AMS_TCOP_CONTACT_SUM_DTL
676
677 l_existing_party_id_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
678 l_new_party_id_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
679
680 l_contact_summary_id_list NUMBER_ARRAY;
681 l_sum_detail_seq_id_list NUMBER_ARRAY;
682 l_contact_sum_seq_id_list NUMBER_ARRAY;
683 l_chnl_sum_seq_id_list NUMBER_ARRAY;
684 l_channel_summary_id_list NUMBER_ARRAY;
685 l_chnl_sum_detail_seq_id_list NUMBER_ARRAY;
686
687 l_sequence_id NUMBER;
688 l_list_count NUMBER;
689 l_no_existing_party_global BOOLEAN := FALSE;
690 l_no_existing_party_channel BOOLEAN := FALSE;
691
692 --List of Constants
693 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'UPDATE_CONTACT_COUNT';
694
695
696 BEGIN
697
698 write_debug_message(LOG_LEVEL_PROCEDURE,
699 PROCEDURE_NAME,
700 'BEGIN',
701 'Beginning Procedure'
702 );
703
704 write_debug_message(LOG_LEVEL_EVENT,
705 PROCEDURE_NAME,
706 'DISPLAY_INPUT_PARAMETER',
707 'Schedule Id = '||to_char(p_schedule_id)||', Activity Id = '||to_char(p_activity_id)
708 );
709
710 write_debug_message(LOG_LEVEL_EVENT,
711 PROCEDURE_NAME,
712 'DISPLAY_INPUT_PARAMETER',
713 'Global Rule Id = '||to_char(p_global_rule_id)||', Channel Rule Id = '||to_char(p_channel_rule_id)
714 );
715
716 IF (p_global_rule_id is not null) THEN
717
718 l_list_count := p_party_id_list.COUNT;
719
720 IF (l_list_count > 0) THEN
721
722 write_debug_message(LOG_LEVEL_EVENT,
723 PROCEDURE_NAME,
724 'INPUT_PARTY_ID_COLLECTION_HAS_ENTRIES',
725 'Input parameter Party Id List Count = '||to_char(l_list_count)
726 );
727
728 write_debug_message(LOG_LEVEL_EVENT,
729 PROCEDURE_NAME,
730 'BEFORE CURSOR C_GET_EXISTING_PARTY',
731 'Executing Cursor C_GET_EXISTING_PARTY'
732 );
733
734 -- If there is no global contact rule setup then there is no need
735 -- update the AMS_TCOP_CONTACT_SUMMARY
736
737
738
739 -- Get the existing party list from the Global Summarization Tables
740 OPEN C_GET_EXISTING_PARTY(p_party_id_list);
741 FETCH C_GET_EXISTING_PARTY
742 BULK COLLECT INTO l_existing_party_id_list,l_contact_summary_id_list;
743 CLOSE C_GET_EXISTING_PARTY;
744
745 write_debug_message(LOG_LEVEL_EVENT,
746 PROCEDURE_NAME,
747 'AFTER CURSOR C_GET_EXISTING_PARTY',
748 'Cursor C_GET_EXISTING_PARTY executed successfully'
749 );
750
751 l_list_count := l_existing_party_id_list.COUNT;
752
753 IF (l_list_count > 0) THEN
754 -- There are some existing parties in the summarization tables
755 -- Create an array of sequence ids to populate SUMMARY_DTL_ID column
756 -- of table AMS_TCOP_CONTACT_SUM_DTL
757
758 write_debug_message(LOG_LEVEL_EVENT,
759 PROCEDURE_NAME,
760 'EXISTING_PARTIES_IN_GLOBAL_SUMMARY',
761 'Number of Parties exist in Global Contact Summary = '||to_char(l_list_count)
762 );
763
764 write_debug_message(LOG_LEVEL_EVENT,
765 PROCEDURE_NAME,
766 'START_LOOP_TO_GET_SUM_DTL_SEQ_ARRAY',
767 'Run the cursor C_GET_SUM_DTL_SEQ in a loop to build an array of sequence ids'
768 );
769 FOR i in l_existing_party_id_list.FIRST .. l_existing_party_id_list.LAST
770 LOOP
771 OPEN C_GET_SUM_DTL_SEQ;
772 FETCH C_GET_SUM_DTL_SEQ
773 INTO l_sequence_id;
774 CLOSE C_GET_SUM_DTL_SEQ;
778 END LOOP;
775
776 l_sum_detail_seq_id_list(i) := l_sequence_id;
777
779
780 write_debug_message(LOG_LEVEL_EVENT,
781 PROCEDURE_NAME,
782 'BEFORE_BULK_UPDATE_CONTACT_SUMMARY',
783 'Bulk update AMS_TCOP_CONTACT_SUMMARY to increment the contact count'
784 );
785
786 -- Bulk Update AMS_TCOP_CONTACT_SUMMARY table
787 FORALL i in l_contact_summary_id_list.FIRST .. l_contact_summary_id_list.LAST
788 UPDATE AMS_TCOP_CONTACT_SUMMARY
789 SET TOTAL_CONTACTS = TOTAL_CONTACTS + 1
790 WHERE CONTACT_SUMMARY_ID = l_contact_summary_id_list(i);
791
792 write_debug_message(LOG_LEVEL_EVENT,
793 PROCEDURE_NAME,
794 'AFTER_BULK_UPDATE_CONTACT_SUMMARY',
795 'Bulk update AMS_TCOP_CONTACT_SUMMARY to increment the contact count completed successfully!'
796 );
797
798 write_debug_message(LOG_LEVEL_EVENT,
799 PROCEDURE_NAME,
800 'BEFORE_BULK_INSERT_CONTACT_SUM_DTL_LABEL1',
801 'Bulk Insert AMS_TCOP_CONTACT_SUM_DTL to have the schedule information '
802 );
803
804 -- Bulk Insert into AMS_TCOP_CONTACT_SUM_DTL
805 FORALL i in l_contact_summary_id_list.FIRST .. l_contact_summary_id_list.LAST
806 INSERT INTO
807 AMS_TCOP_CONTACT_SUM_DTL
808 (
809 SUMMARY_DTL_ID,
810 CONTACT_SUMMARY_ID,
811 SCHEDULE_ID,
812 CREATION_DATE,
813 CREATED_BY,
814 LAST_UPDATE_DATE,
815 LAST_UPDATED_BY,
816 LAST_UPDATE_LOGIN
817 )
818 VALUES
819 (
820 l_sum_detail_seq_id_list(i),
821 l_contact_summary_id_list(i),
822 p_schedule_id,
823 sysdate,
824 FND_GLOBAL.USER_ID,
825 sysdate,
826 FND_GLOBAL.USER_ID,
827 FND_GLOBAL.USER_ID
828 );
829
830 write_debug_message(LOG_LEVEL_EVENT,
831 PROCEDURE_NAME,
832 'AFTER_BULK_INSERT_CONTACT_SUM_DTL_LABEL1',
833 'Bulk Insert into AMS_TCOP_CONTACT_SUM_DTL completed successfully'
834 );
835 -- Reset some of the collection variables that will be reused
836 l_sum_detail_seq_id_list.delete;
837
838 write_debug_message(LOG_LEVEL_EVENT,
839 PROCEDURE_NAME,
840 'RESET_COLLECTION_VARIABLES_LABEL1',
841 'Reset collection variable l_sum_detail_seq_id_list'
842 );
843 ELSE
844
845 write_debug_message(LOG_LEVEL_EVENT,
846 PROCEDURE_NAME,
847 'NO_PARTY_EXISTS_IN_GLOBAL_CONTACT_SUMMARY_L1',
848 'No party exists in global contact summary among the parties contacted'
849 );
850
851 l_no_existing_party_global := TRUE;
852
853
854 END IF;
855
856
857 IF (not(l_no_existing_party_global)) THEN
858
859 write_debug_message(LOG_LEVEL_EVENT,
860 PROCEDURE_NAME,
861 'PARTY_EXISTS_IN_GLOBAL_SUMMARY',
862 'Parties already exist in global contact summary'
863 );
864
865 write_debug_message(LOG_LEVEL_EVENT,
866 PROCEDURE_NAME,
867 'BEFORE CURSOR C_GET_NEW_PARTY_LABEL1',
868 'Executing Cursor C_GET_NEW_PARTY to get the list of parties not present in the global contact summary tables'
869 );
870 -- Get the party list not present in the Global Summarization Tables
871 OPEN C_GET_NEW_PARTY(p_party_id_list,l_existing_party_id_list);
872 FETCH C_GET_NEW_PARTY
873 BULK COLLECT INTO l_new_party_id_list;
874 CLOSE C_GET_NEW_PARTY;
875
876 write_debug_message(LOG_LEVEL_EVENT,
877 PROCEDURE_NAME,
878 'AFTER_CURSOR_C_GET_NEW_PARTY_LABEL1',
879 'Cursor C_GET_NEW_PARTY Executed successfully'
880 );
881 ELSE
882 write_debug_message(LOG_LEVEL_EVENT,
883 PROCEDURE_NAME,
884 'NO_PARTY_EXISTS_IN_GLOBAL_CONTACT_SUMMARY_L2',
885 'Copy the input list to the new_party_list'
886 );
887
888 l_new_party_id_list := p_party_id_list;
889
890 write_debug_message(LOG_LEVEL_EVENT,
891 PROCEDURE_NAME,
892 'NO_PARTY_EXISTS_IN_GLOBAL_CONTACT_SUMMARY_L3',
893 'After Copying the input list to the new_party_list'
894 );
895 END IF;
896
897 l_list_count := l_new_party_id_list.COUNT;
898
899 IF (l_list_count > 0) THEN
900
901 write_debug_message(LOG_LEVEL_EVENT,
902 PROCEDURE_NAME,
906
903 'NEW_PARTIES_WHICH_DO_NOT_EXIST_IN_GLOBAL_SUMMARY',
904 'Number of new Parties which do not exist in Global Contact Summary = '||to_char(l_list_count)
905 );
907 write_debug_message(LOG_LEVEL_EVENT,
908 PROCEDURE_NAME,
909 'START_LOOP_AND_CREATE_SEQ_ARRAY_LABEL1',
910 'Start a loop and get next sequence id for contact_sum_s and contact_sum_dtl_s'
911 );
912
913 FOR i in l_new_party_id_list.FIRST .. l_new_party_id_list.LAST
914 LOOP
915
916 OPEN C_GET_CONTACT_SUM_SEQ;
917 FETCH C_GET_CONTACT_SUM_SEQ
918 INTO l_sequence_id;
919 CLOSE C_GET_CONTACT_SUM_SEQ;
920
921 l_contact_sum_seq_id_list(i) := l_sequence_id;
922
923 OPEN C_GET_SUM_DTL_SEQ;
924 FETCH C_GET_SUM_DTL_SEQ
925 INTO l_sequence_id;
926 CLOSE C_GET_SUM_DTL_SEQ;
927
928 l_sum_detail_seq_id_list(i) := l_sequence_id;
929
930 END LOOP;
931
932 write_debug_message(LOG_LEVEL_EVENT,
933 PROCEDURE_NAME,
934 'END_LOOP_AND_CREATE_SEQ_ARRAY_LABEL2',
935 'Successfully completed the loop to get an array of next sequence id for contact_sum_s and contact_sum_dtl_s'
936 );
937
938 write_debug_message(LOG_LEVEL_EVENT,
939 PROCEDURE_NAME,
940 'BEFORE_BULK_INSERT_INTO_CONTACT_SUMMARY',
941 'Bulk Insert the new party ids into the AMS_TCOP_CONTACT_SUMMARY'
942 );
943
944 -- BULK INSERT INTO AMS_TCOP_CONTACT_SUMMARY
945 FORALL i in l_new_party_id_list.FIRST .. l_new_party_id_list.LAST
946 INSERT INTO
947 AMS_TCOP_CONTACT_SUMMARY
948 (
949 CONTACT_SUMMARY_ID,
950 RULE_ID,
951 PARTY_ID,
952 TOTAL_CONTACTS,
953 CREATION_DATE,
954 CREATED_BY,
955 LAST_UPDATE_DATE,
956 LAST_UPDATED_BY,
957 LAST_UPDATE_LOGIN
958 )
959 VALUES
960 (
961 l_contact_sum_seq_id_list(i),
962 p_global_rule_id,
963 l_new_party_id_list(i),
964 1,
965 sysdate,
966 FND_GLOBAL.USER_ID,
967 sysdate,
968 FND_GLOBAL.USER_ID,
969 FND_GLOBAL.USER_ID
970 );
971
972 write_debug_message(LOG_LEVEL_EVENT,
973 PROCEDURE_NAME,
974 'AFTER_BULK_INSERT_INTO_CONTACT_SUMMARY',
975 'Bulk Insertion of the new party ids into the AMS_TCOP_CONTACT_SUMMARY completed successfully'
976 );
977
978 write_debug_message(LOG_LEVEL_EVENT,
979 PROCEDURE_NAME,
980 'AFTER_BULK_INSERT_INTO_CONTACT_SUMMARY',
981 'Bulk Insertion of the new party ids into the AMS_TCOP_CONTACT_SUMMARY completed successfully'
982 );
983
984 write_debug_message(LOG_LEVEL_EVENT,
985 PROCEDURE_NAME,
986 'BEFORE_BULK_INSERT_CONTACT_SUM_DTL_LABEL2',
987 'Bulk Insert AMS_TCOP_CONTACT_SUM_DTL to have the schedule information '
988 );
989
990 -- BULK INSERT INTO AMS_TCOP_CONTACT_SUM_DTL
991 FORALL i in l_sum_detail_seq_id_list.FIRST .. l_sum_detail_seq_id_list.LAST
992 INSERT INTO
993 AMS_TCOP_CONTACT_SUM_DTL
994 (
995 SUMMARY_DTL_ID,
996 CONTACT_SUMMARY_ID,
997 SCHEDULE_ID,
998 CREATION_DATE,
999 CREATED_BY,
1000 LAST_UPDATE_DATE,
1001 LAST_UPDATED_BY,
1002 LAST_UPDATE_LOGIN
1003 )
1004 VALUES
1005 (
1006 l_sum_detail_seq_id_list(i),
1007 l_contact_sum_seq_id_list(i),
1008 p_schedule_id,
1009 sysdate,
1010 FND_GLOBAL.USER_ID,
1011 sysdate,
1012 FND_GLOBAL.USER_ID,
1013 FND_GLOBAL.USER_ID
1014 );
1015
1016 write_debug_message(LOG_LEVEL_EVENT,
1017 PROCEDURE_NAME,
1018 'AFTER_BULK_INSERT_CONTACT_SUM_DTL_LABEL2',
1019 'After Bulk Insert AMS_TCOP_CONTACT_SUM_DTL to have the schedule information '
1020 );
1021
1022 write_debug_message(LOG_LEVEL_EVENT,
1023 PROCEDURE_NAME,
1024 'RESET_COLLECTION_VARIABLES_LABEL2',
1025 'Reset collection variable l_existing_party_id_list,l_new_party_id_list'
1026 );
1027 --Reset some of the collections that will be reused later
1028 l_existing_party_id_list.delete;
1029 l_new_party_id_list.delete;
1030
1031 write_debug_message(LOG_LEVEL_EVENT,
1032 PROCEDURE_NAME,
1036
1033 'AFTER_RESET_COLLECTION_VARIABLES_LABEL2',
1034 'Successful completion of Reset collection variable l_existing_party_id_list,l_new_party_id_list'
1035 );
1037 ELSE
1038
1039 write_debug_message(LOG_LEVEL_EVENT,
1040 PROCEDURE_NAME,
1041 'NO_NEW_PARTIES_NEED_TO_BE_CREATED_IN_GLOBAL_SUMMARY',
1042 'new_party_id_list is zero. No parties need to be created in Contact Summary'
1043 );
1044 END IF;
1045
1046 write_debug_message(LOG_LEVEL_EVENT,
1047 PROCEDURE_NAME,
1048 'BEFORE CURSOR C_GET_EXISTING_PARTY_CHNL',
1049 'Executing Cursor C_GET_EXISTING_PARTY_CHNL to get the list of parties not present in the channel contact summary tables'
1050 );
1051 END IF;
1052
1053 IF (p_channel_rule_id is not null) THEN
1054
1055 IF (NOT (l_no_existing_party_global)) THEN
1056 -- There are some existing parties in global contact summary
1057 -- This step will be skipped if threre are no existing parties
1058 -- global summary, that means there won't be any entries in channel
1059 -- summary
1060
1061 -- Get the existing party list from the Channel Summarization Tables
1062 OPEN C_GET_EXISTING_PARTY_CHNL(p_party_id_list,p_activity_id);
1063 FETCH C_GET_EXISTING_PARTY_CHNL
1064 BULK COLLECT INTO l_existing_party_id_list,l_channel_summary_id_list;
1065 CLOSE C_GET_EXISTING_PARTY_CHNL;
1066
1067 write_debug_message(LOG_LEVEL_EVENT,
1068 PROCEDURE_NAME,
1069 'AFTER CURSOR C_GET_EXISTING_PARTY_CHNL',
1070 'Cursor C_GET_EXISTING_PARTY_CHNL executed successfully to get the list of parties not present in the channel contact summary tables'
1071 );
1072
1073 l_list_count := l_existing_party_id_list.count;
1074 IF (l_list_count > 0) THEN
1075 -- There are some existing parties in the channel summarization tables
1076 -- Create an array of sequence ids to populate CHANNEL_SUM_DTL_ID column
1077 -- of table AMS_TCOP_CHANNEL_SUM_DTL
1078
1079 write_debug_message(LOG_LEVEL_EVENT,
1080 PROCEDURE_NAME,
1081 'PARTIES_EXIST_IN_CHANNEL_SUMMARY',
1082 'Number of new Parties which exist in Channel Contact Summary = '||to_char(l_list_count)
1083 );
1084
1085 write_debug_message(LOG_LEVEL_EVENT,
1086 PROCEDURE_NAME,
1087 'START_LOOP_AND_CREATE_SEQ_ARRAY_LABEL2',
1088 'Start a loop and get next sequence id for channel_sum_dtl_s and create an array'
1089 );
1090
1091 FOR i in l_existing_party_id_list.FIRST .. l_existing_party_id_list.LAST
1092 LOOP
1093 OPEN C_GET_CHNL_SUM_DTL_SEQ;
1094 FETCH C_GET_CHNL_SUM_DTL_SEQ
1095 INTO l_sequence_id;
1096 CLOSE C_GET_CHNL_SUM_DTL_SEQ;
1097
1098 l_chnl_sum_detail_seq_id_list(i) := l_sequence_id;
1099
1100 END LOOP;
1101
1102 write_debug_message(LOG_LEVEL_EVENT,
1103 PROCEDURE_NAME,
1104 'END_LOOP_AND_CREATE_SEQ_ARRAY_LABEL2',
1105 'End loop and get next sequence id for channel_sum_dtl_s with the array populated'
1106 );
1107
1108 write_debug_message(LOG_LEVEL_EVENT,
1109 PROCEDURE_NAME,
1110 'BEFORE_BULK_UPDATE_CHANNEL_SUMMARY',
1111 'Bulk update AMS_TCOP_CHANNEL_SUMMARY to increment the contact count'
1112 );
1113
1114 -- Bulk Update AMS_TCOP_CHANNEL_SUMMARY table
1115 FORALL i in l_channel_summary_id_list.FIRST .. l_channel_summary_id_list.LAST
1116 UPDATE AMS_TCOP_CHANNEL_SUMMARY
1117 SET TOTAL_CONTACTS = TOTAL_CONTACTS + 1
1118 WHERE CHANNEL_SUMMARY_ID = l_channel_summary_id_list(i)
1119 AND MEDIA_ID = p_activity_id;
1120
1121 write_debug_message(LOG_LEVEL_EVENT,
1122 PROCEDURE_NAME,
1123 'AFTER_BULK_UPDATE_CHANNEL_SUMMARY',
1124 'After Bulk update AMS_TCOP_CHANNEL_SUMMARY to increment the contact count'
1125 );
1126
1127 write_debug_message(LOG_LEVEL_EVENT,
1128 PROCEDURE_NAME,
1129 'BEFORE_BULK_INSERT_CHANNEL_SUM_DTL_L1',
1130 'Before Bulk Insert of AMS_TCOP_CHANNEL_SUM_DTL to populate the schedule information'
1131 );
1132
1133 -- Bulk Insert into AMS_TCOP_CHANNEL_SUM_DTL
1134 FORALL i in l_chnl_sum_detail_seq_id_list.FIRST .. l_chnl_sum_detail_seq_id_list.LAST
1135 INSERT INTO
1136 AMS_TCOP_CHANNEL_SUM_DTL
1137 (
1138 CHANNEL_SUM_DTL_ID,
1139 CHANNEL_SUMMARY_ID,
1140 SCHEDULE_ID,
1141 CREATION_DATE,
1142 CREATED_BY,
1143 LAST_UPDATE_DATE,
1144 LAST_UPDATED_BY,
1145 LAST_UPDATE_LOGIN
1149 l_chnl_sum_detail_seq_id_list(i),
1146 )
1147 VALUES
1148 (
1150 l_channel_summary_id_list(i),
1151 p_schedule_id,
1152 sysdate,
1153 FND_GLOBAL.USER_ID,
1154 sysdate,
1155 FND_GLOBAL.USER_ID,
1156 FND_GLOBAL.USER_ID
1157 );
1158
1159 write_debug_message(LOG_LEVEL_EVENT,
1160 PROCEDURE_NAME,
1161 'AFTER_BULK_INSERT_CHANNEL_SUM_DTL_L2',
1162 'After Bulk Insert of AMS_TCOP_CHANNEL_SUM_DTL to populate the schedule information'
1163 );
1164
1165 write_debug_message(LOG_LEVEL_EVENT,
1166 PROCEDURE_NAME,
1167 'RESET_COLLECTION_VARIABLES_LABEL3',
1168 'Reset collection variable l_sum_detail_seq_id_list'
1169 );
1170
1171 --Reset some collection variables
1172 l_sum_detail_seq_id_list.delete;
1173
1174 write_debug_message(LOG_LEVEL_EVENT,
1175 PROCEDURE_NAME,
1176 'AFTER_RESET_COLLECTION_VARIABLES_LABEL3',
1177 'Reset collection variable l_sum_detail_seq_id_list'
1178 );
1179 ELSE
1180
1181 write_debug_message(LOG_LEVEL_EVENT,
1182 PROCEDURE_NAME,
1183 'NO_EXISTING_PARTIES_IN_CHANNEL_SUMMARY',
1184 'No parties exist in the channel summary tables among the parties contacted'
1185 );
1186 l_no_existing_party_channel := TRUE;
1187
1188
1189 END IF;
1190
1191 ELSE
1192 write_debug_message(LOG_LEVEL_EVENT,
1193 PROCEDURE_NAME,
1194 'SKIPPING_EXISTING_PARTIES_IN_CHANNEL_SUMMARY',
1195 'Since no parties exist in the global summary, there will not be any in the channel summary'
1196 );
1197
1198 END IF;
1199
1200 IF (NOT (l_no_existing_party_channel)) THEN
1201
1202 write_debug_message(LOG_LEVEL_EVENT,
1203 PROCEDURE_NAME,
1204 'BEFORE CURSOR C_GET_NEW_PARTY_LABEL2',
1205 'Executing Cursor C_GET_NEW_PARTY to get the list of parties not present in the channel contact summary tables'
1206 );
1207
1208 -- Get the party list not present in the Channel Summarization Tables
1209 OPEN C_GET_NEW_PARTY(p_party_id_list,l_existing_party_id_list);
1210 FETCH C_GET_NEW_PARTY
1211 BULK COLLECT INTO l_new_party_id_list;
1212 CLOSE C_GET_NEW_PARTY;
1213
1214 ELSE
1215 write_debug_message(LOG_LEVEL_EVENT,
1216 PROCEDURE_NAME,
1217 'NO_PARTY_EXISTS_IN_CHANNEL_CONTACT_SUMMARY',
1218 'Copy the input list to the new_party_list'
1219 );
1220
1221 l_new_party_id_list := p_party_id_list;
1222
1223 write_debug_message(LOG_LEVEL_EVENT,
1224 PROCEDURE_NAME,
1225 'NO_PARTY_EXISTS_IN_CHANNEL_CONTACT_SUMMARY',
1226 'After Copying the input list to the new_party_list'
1227 );
1228
1229 END IF;
1230
1231 l_list_count := l_new_party_id_list.COUNT;
1232
1233 IF (l_list_count > 0) THEN
1234
1235 write_debug_message(LOG_LEVEL_EVENT,
1236 PROCEDURE_NAME,
1237 'CREATE_NEW_PARTIES_IN_CHANNEL_SUMMARY',
1238 'Number of new Parties need to be created in Channel Contact Summary = '||to_char(l_list_count)
1239 );
1240
1241 write_debug_message(LOG_LEVEL_EVENT,
1242 PROCEDURE_NAME,
1243 'START_LOOP_AND_CREATE_SEQ_ARRAY_LABEL3',
1244 'Start a loop and get next sequence id for channel_sum_s,channel_sum_dtl_s and create an array'
1245 );
1246
1247 FOR i in l_new_party_id_list.FIRST .. l_new_party_id_list.LAST
1248 LOOP
1249
1250 OPEN C_GET_CHNL_SUM_SEQ;
1251 FETCH C_GET_CHNL_SUM_SEQ
1252 INTO l_sequence_id;
1253 CLOSE C_GET_CHNL_SUM_SEQ;
1254
1255 l_chnl_sum_seq_id_list(i) := l_sequence_id;
1256
1257 OPEN C_GET_CHNL_SUM_DTL_SEQ;
1258 FETCH C_GET_CHNL_SUM_DTL_SEQ
1259 INTO l_sequence_id;
1260 CLOSE C_GET_CHNL_SUM_DTL_SEQ;
1261
1262 l_sum_detail_seq_id_list(i) := l_sequence_id;
1263
1264 END LOOP;
1265
1266 write_debug_message(LOG_LEVEL_EVENT,
1267 PROCEDURE_NAME,
1268 'END_LOOP_AND_CREATE_SEQ_ARRAY_LABEL3',
1269 'End a loop and get next sequence id for channel_sum_s,channel_sum_dtl_s and create an array'
1270 );
1271
1272 write_debug_message(LOG_LEVEL_EVENT,
1273 PROCEDURE_NAME,
1277
1274 'BEFORE_BULK_INSERT_CHANNEL_SUM',
1275 'Before Bulk Insert of AMS_TCOP_CHANNEL_SUMMMARY'
1276 );
1278 -- BULK INSERT INTO AMS_TCOP_CHANNEL_SUMMARY
1279 FORALL i in l_new_party_id_list.FIRST .. l_new_party_id_list.LAST
1280 INSERT INTO
1281 AMS_TCOP_CHANNEL_SUMMARY
1282 (
1283 CHANNEL_SUMMARY_ID,
1284 RULE_ID,
1285 PARTY_ID,
1286 MEDIA_ID,
1287 TOTAL_CONTACTS,
1288 CREATION_DATE,
1289 CREATED_BY,
1290 LAST_UPDATE_DATE,
1291 LAST_UPDATED_BY,
1292 LAST_UPDATE_LOGIN
1293 )
1294 VALUES
1295 (
1296 l_chnl_sum_seq_id_list(i),
1297 p_channel_rule_id,
1298 l_new_party_id_list(i),
1299 p_activity_id,
1300 1,
1301 sysdate,
1302 FND_GLOBAL.USER_ID,
1303 sysdate,
1304 FND_GLOBAL.USER_ID,
1305 FND_GLOBAL.USER_ID
1306 );
1307
1308 write_debug_message(LOG_LEVEL_EVENT,
1309 PROCEDURE_NAME,
1310 'AFTER_BULK_INSERT_CHANNEL_SUM',
1311 'After Bulk Insert of AMS_TCOP_CHANNEL_SUMMMARY'
1312 );
1313
1314 write_debug_message(LOG_LEVEL_EVENT,
1315 PROCEDURE_NAME,
1316 'BEFORE_BULK_INSERT_CHANNEL_SUM_DTL_L2',
1317 'Before Bulk Insert of AMS_TCOP_CHANNEL_SUM_DTL to populate the schedule information'
1318 );
1319
1320 -- BULK INSERT INTO AMS_TCOP_CHANNEL_SUM_DTL
1321 FORALL i in l_sum_detail_seq_id_list.FIRST .. l_sum_detail_seq_id_list.LAST
1322 INSERT INTO
1323 AMS_TCOP_CHANNEL_SUM_DTL
1324 (
1325 CHANNEL_SUM_DTL_ID,
1326 CHANNEL_SUMMARY_ID,
1327 SCHEDULE_ID,
1328 CREATION_DATE,
1329 CREATED_BY,
1330 LAST_UPDATE_DATE,
1331 LAST_UPDATED_BY,
1332 LAST_UPDATE_LOGIN
1333 )
1334 VALUES
1335 (
1336 l_sum_detail_seq_id_list(i),
1337 l_chnl_sum_seq_id_list(i),
1338 p_schedule_id,
1339 sysdate,
1340 FND_GLOBAL.USER_ID,
1341 sysdate,
1342 FND_GLOBAL.USER_ID,
1343 FND_GLOBAL.USER_ID
1344 );
1345
1346 write_debug_message(LOG_LEVEL_EVENT,
1347 PROCEDURE_NAME,
1348 'BEFORE_BULK_INSERT_CHANNEL_SUM_DTL_L2',
1349 'Before Bulk Insert of AMS_TCOP_CHANNEL_SUM_DTL to populate the schedule information'
1350 );
1351
1352
1353 END IF;
1354
1355 END IF;
1356 END IF;
1357
1358
1359
1360
1361
1362 END UPDATE_CONTACT_COUNT;
1363 -- ===============================================================
1364 -- Start of Comments
1365 -- Name
1366 -- SUMMARIZE_ALL_FATIGUE_CONTACTS
1367 --
1368 -- Purpose
1369 -- This procedure summarizes all fatiguing contacts for the periods
1370 -- specified in Fatigue Rule Setup
1371 --
1372 PROCEDURE SUMMARIZE_ALL_FATIGUE_CONTACTS
1373 IS
1374 BEGIN
1375 null;
1376 END SUMMARIZE_ALL_FATIGUE_CONTACTS;
1377
1378
1379 END AMS_TCOP_SUMMARIZATION_PKG;
[Home] [Help]