1 PACKAGE BODY AMS_TCOP_ENGINE_PKG AS
2 /* $Header: amsvtcrb.pls 115.2 2004/05/18 11:20:50 mayjain noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_TCOP_ENGINE_PKG
7 -- Purpose
8 --
9 -- This package contains all the program units for traffic cop
10 -- Engine
11 --
12 -- History
13 --
14 -- 3/1/2004 mayjain fix for bug 3470706
15 --
16 -- NOTE
17 --
18 -- End of Comments
19 -- ===============================================================
20 -- Start of Comments
21 -- Name
22 -- Apply_Fatigue_Rules
23 --
24 -- Purpose
25 -- This procedure applies fatigue rules on the Target Group of Schedule.
26 --
27 -- Declare Some Global Variables used by all the procedures in the package
28 --
29 -- This variable is list of all the parties fatigued
30 G_Fatigue_Party_List JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
31
32 -- This variable is list of all the list_entry_id that are associated
33 -- with fatigue parties
34 G_Fatigue_Entry_List JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
35
36 -- This is a list of list_entry_id associated with the list
37 -- of schedules which fatigued the parties. This list may contain
38 -- duplicate entries.
39 G_Fatigue_By_Entry_List JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
40
41 -- This is a list of list_entry_id associated with the list
42 -- of schedules which fatigued the parties
43 G_Fatigue_By_Schedule_List JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
44
45 -- Global Constants that will be used through out the package
46 LOG_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
47 LOG_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
48 LOG_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
49 LOG_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
50 LOG_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
51 LOG_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
52
53 -- Private procedure to write debug message to FND_LOG table
54 PROCEDURE write_debug_message(p_log_level NUMBER,
55 p_procedure_name VARCHAR2,
56 p_label VARCHAR2,
57 p_text VARCHAR2
58 )
59 IS
60 l_module_name VARCHAR2(400);
61 DELIMETER CONSTANT VARCHAR2(1) := '.';
62 LABEL_PREFIX CONSTANT VARCHAR2(15) := 'WFScheduleExec';
63
64 BEGIN
65 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
66 -- Set the Module Name
67 l_module_name := 'ams'||DELIMETER||'plsql'||DELIMETER||G_PACKAGE_NAME||DELIMETER||p_procedure_name||DELIMETER||LABEL_PREFIX||'-'||p_label;
68
69
70 -- Log the Message
71 AMS_UTILITY_PVT.debug_message(p_log_level,
72 l_module_name,
73 p_text
74 );
75
76
77
78 END IF;
79 AMS_UTILITY_PVT.write_conc_log('['||G_PACKAGE_NAME||DELIMETER||p_procedure_name||DELIMETER||LABEL_PREFIX||'-'||p_label||']'||p_text);
80
81 --dbms_output.put_line(l_module_name||': '||p_text);
82
83 END write_debug_message;
84
85
86 -- Append the given list to the global list
87 PROCEDURE APPEND_GLOBAL_FATIGUE_LIST(p_fatigue_party_list JTF_NUMBER_TABLE,
88 p_fatigue_entry_list JTF_NUMBER_TABLE,
89 p_fatigue_by_entry_list JTF_NUMBER_TABLE,
90 p_fatigue_by_schedule_list JTF_NUMBER_TABLE
91 )
92 IS
93
94 l_global_count NUMBER;
95 l_input_count NUMBER;
96 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'APPEND_GLOBAL_FATIGUE_LIST';
97
98 BEGIN
99 write_debug_message(LOG_LEVEL_EVENT,
100 PROCEDURE_NAME,
101 'INPUT_PARTY_LIST_NEEDS_TO_BE_COPIED',
102 'Total number of entries in the input fatigue party list = '||to_char(p_fatigue_party_list.count)
103 );
104
105 write_debug_message(LOG_LEVEL_EVENT,
106 PROCEDURE_NAME,
107 'INPUT_ENTRY_LIST_NEEDS_TO_BE_COPIED',
108 'Total number of ids in the input fatigue entry list = '||to_char(p_fatigue_entry_list.count)
109 );
110
111 write_debug_message(LOG_LEVEL_EVENT,
112 PROCEDURE_NAME,
113 'INPUT_FATIGUE_BY_ENTRY_LIST_NEEDS_TO_BE_COPIED',
114 'Total number of fatigue by entries in the input list = '||to_char(p_fatigue_by_entry_list.count)
115 );
116
117 write_debug_message(LOG_LEVEL_EVENT,
118 PROCEDURE_NAME,
119 'INPUT_FATIGUE_BY_SCHEDULE_LIST_NEEDS_TO_BE_COPIED',
120 'Total number of fatigue by schedules in the input list = '||to_char(p_fatigue_by_schedule_list.count)
121 );
122
123 -- Append the G_FATIGUE_PARTY_LIST
124 l_input_count := p_FATIGUE_PARTY_LIST.COUNT;
125 IF (l_input_count > 0) THEN
126 l_global_count := G_FATIGUE_PARTY_LIST.COUNT;
127 write_debug_message(LOG_LEVEL_EVENT,
128 PROCEDURE_NAME,
129 'GLOBAL_FATIGUE_ENTRY_LIST_BEFORE_COPY',
130 'Total number of entries in the global fatigue party list = '||to_char(l_global_count)
131 );
132 FOR i in p_fatigue_party_list.FIRST .. p_fatigue_party_list.LAST
133 LOOP
134 G_FATIGUE_PARTY_LIST.EXTEND;
135 G_FATIGUE_PARTY_LIST(l_global_count + 1) := p_fatigue_party_list(i);
136 l_global_count := l_global_count + 1;
137 END LOOP;
138
139 write_debug_message(LOG_LEVEL_EVENT,
140 PROCEDURE_NAME,
141 'GLOBAL_FATIGUE_PARTY_ENTRY_LIST_AFTER_COPY',
142 'After copying Total number of entries in the global fatigue party list = '||to_char(G_FATIGUE_PARTY_LIST.COUNT)
143 );
144 END IF;
145
146 -- Append the G_FATIGUE_ENTRY_LIST
147 l_input_count := p_FATIGUE_ENTRY_LIST.COUNT;
148 IF (l_input_count > 0) THEN
149 l_global_count := G_FATIGUE_ENTRY_LIST.COUNT;
150 write_debug_message(LOG_LEVEL_EVENT,
151 PROCEDURE_NAME,
152 'GLOBAL_FATIGUE_ENTRY_LIST_BEFORE_COPY',
153 'Before copying Total number of entries in the global fatigue list_entry list = '||to_char(l_global_count)
154 );
155 FOR i in p_fatigue_entry_list.FIRST .. p_fatigue_entry_list.LAST
156 LOOP
157 G_FATIGUE_ENTRY_LIST.EXTEND;
158 G_FATIGUE_ENTRY_LIST(l_global_count + 1) := p_fatigue_entry_list(i);
159 l_global_count := l_global_count + 1;
160 END LOOP;
161
162 write_debug_message(LOG_LEVEL_EVENT,
163 PROCEDURE_NAME,
164 'GLOBAL_FATIGUE_ENTRY_LIST_AFTER_COPY',
165 'After copying Total number of entries in the global fatigue list_entry list = '||to_char(G_FATIGUE_ENTRY_LIST.COUNT)
166 );
167 END IF;
168
169 -- Append the G_FATIGUE_BY_ENTRY_LIST
170 l_input_count := p_FATIGUE_BY_ENTRY_LIST.COUNT;
171 IF (l_input_count > 0) THEN
172 l_global_count := G_FATIGUE_BY_ENTRY_LIST.COUNT;
173 write_debug_message(LOG_LEVEL_EVENT,
174 PROCEDURE_NAME,
175 'GLOBAL_FATIGUE_BY_ENTRY_LIST_BEFORE_COPY',
176 'Before copying Total number of entries in the global fatigue by list = '||to_char(l_global_count)
177 );
178 FOR i in p_fatigue_by_entry_list.FIRST .. p_fatigue_by_entry_list.LAST
179 LOOP
180 G_FATIGUE_BY_ENTRY_LIST.EXTEND;
181 G_FATIGUE_BY_ENTRY_LIST(l_global_count + 1) := p_fatigue_by_entry_list(i);
182 l_global_count := l_global_count + 1;
183 END LOOP;
184
185 write_debug_message(LOG_LEVEL_EVENT,
186 PROCEDURE_NAME,
187 'GLOBAL_FATIGUE_BY_ENTRY_LIST_AFTER_COPY',
188 'After copying Total number of entries in the global fatigue by list = '||to_char(G_FATIGUE_BY_ENTRY_LIST.count)
189 );
190 END IF;
191
192 -- Append the G_FATIGUE_BY_SCHEDULE_LIST
193 l_input_count := p_FATIGUE_BY_SCHEDULE_LIST.COUNT;
194 IF (l_input_count > 0) THEN
195 l_global_count := G_FATIGUE_BY_SCHEDULE_LIST.COUNT;
196 write_debug_message(LOG_LEVEL_EVENT,
197 PROCEDURE_NAME,
198 'GLOBAL_FATIGUE_BY_SCHEDULE_LIST_BEFORE_COPY',
199 'Before copying Total number of entries in the global fatigue by schedule list = '||to_char(l_global_count)
200 );
201 FOR i in p_fatigue_by_schedule_list.FIRST .. p_fatigue_by_schedule_list.LAST
202 LOOP
203 G_FATIGUE_BY_SCHEDULE_LIST.EXTEND;
204 G_FATIGUE_BY_SCHEDULE_LIST(l_global_count + 1) := p_fatigue_by_schedule_list(i);
205 l_global_count := l_global_count + 1;
206 END LOOP;
207
208 write_debug_message(LOG_LEVEL_EVENT,
209 PROCEDURE_NAME,
210 'GLOBAL_FATIGUE_BY_SCHEDULE_LIST_AFTER_COPY',
211 'After copying Total number of entries in the global fatigue by schedule list = '||to_char(l_global_count)
212 );
213 END IF;
214
215 END APPEND_GLOBAL_FATIGUE_LIST;
216
217
218
219 -- This function checks if the given partyId is already in the global
220 -- fatigue list G_Fatigue_Party_List
221 --
222 FUNCTION IS_PARTY_IN_FATIGUE_LIST(p_party_id NUMBER)
223 RETURN BOOLEAN
224 IS
225
226 CURSOR C_GET_PARTY(p_party_id NUMBER)
227 IS
228 SELECT party_list.party_id
229 FROM
230 (SELECT column_value party_id
231 FROM TABLE(CAST(G_Fatigue_Party_List as JTF_NUMBER_TABLE))
232 ) party_list
233 WHERE party_list.party_id=p_party_id;
234
235 l_party_id NUMBER;
236
237 BEGIN
238
239 OPEN C_GET_PARTY(p_party_id);
240 FETCH C_GET_PARTY INTO l_party_id;
241 CLOSE C_GET_PARTY;
242
243 IF (l_party_id IS NULL) THEN
244 return false;
245 else
246 return true;
247 END IF;
248
249 END IS_PARTY_IN_FATIGUE_LIST;
250
251 -- This function returns the equivalent contact_type of marketing
252 -- channels. TCA recognizes CONTACT_TYPEs as lookup code of
253 -- lookup type CONTACT_TYPE
254 -- For example, Seeded Activity Id for Marketing channel EMAIL = 20
255 -- It's Equivalent CONTACT_TYPE lookup code in TCA is EMAIL
256 FUNCTION INTERPRET_UOM_CODE(p_interact_uom_code VARCHAR2)
257 RETURN NUMBER
258 IS
259 BEGIN
260 IF (p_interact_uom_code = 'MONTH') THEN
261 return 30;
262 ELSIF (p_interact_uom_code = '30_DAY_PERIOD') THEN
263 return 30;
264 ELSIF (p_interact_uom_code = 'DAY') THEN
265 return 1;
266 ELSIF (p_interact_uom_code = 'WEEK') THEN
267 return 7;
268 ELSIF (p_interact_uom_code = 'YEAR') THEN
269 return 365;
270 ELSE
271 return null;
272 END IF;
273
274 END;
275
276 -- This function returns the equivalent contact_type of marketing
277 -- channels. TCA recognizes CONTACT_TYPEs as lookup code of
278 -- lookup type CONTACT_TYPE
279 -- For example, Seeded Activity Id for Marketing channel EMAIL = 20
280 -- It's Equivalent CONTACT_TYPE lookup code in TCA is EMAIL
281 FUNCTION GET_TCA_SUPPORTED_CONTACT_TYPE(p_activity_id NUMBER)
282 RETURN VARCHAR2
283 IS
284 BEGIN
285 IF (p_activity_id = 20) THEN
286 return 'EMAIL';
287 ELSIF (p_activity_id = 10) THEN
288 return 'FAX';
289 ELSIF (p_activity_id = 460) THEN
290 return 'CALL';
291 ELSIF (p_activity_id = 480) THEN
292 return 'MAIL';
293 ELSE
294 return null;
295 END IF;
296
297 END GET_TCA_SUPPORTED_CONTACT_TYPE;
298
299 -- ===============================================================
300 -- Start of Comments
301 -- Name
302 -- Apply_Fatigue_Rules
303 --
304 -- Purpose
305 -- This API will add parties to the Global Fatigue List based on the following:
306 -- 1. The Opt-In preferences for the parties must have been set
307 -- 2. The number of contacts already reached the maximum threshold as per the contact preferences.
308 --
309 PROCEDURE APPLY_PARTY_OPT_IN_PREFERENCES(p_list_header_id NUMBER,
310 p_activity_id NUMBER
311 )
312 IS
313
314 -- This cursor gets all the party preferences both Global and Channel specific
315 -- Global Preferences are those where contact_type=ALL and Channel specific preferences
316 -- Supported channels in TCA which also overlap with Marketing
317 -- are EMAIL,FAX,CALL and MAIL
318 CURSOR C_GET_PARTY_PREFERENCES(p_list_header_id NUMBER,
319 p_channel VARCHAR2 )
320 IS
321 SELECT list_entry.party_id
322 ,list_entry.list_entry_id
323 ,pref.CONTACT_TYPE
324 ,pref.MAX_NO_OF_INTERACTIONS
325 ,pref.MAX_NO_OF_INTERACT_UOM_CODE
326 FROM HZ_CONTACT_PREFERENCES pref,
327 ams_list_entries list_entry
328 WHERE list_entry.LIST_HEADER_ID = p_list_header_id
329 AND pref.contact_level_table='HZ_PARTIES'
330 AND pref.contact_level_table_id = list_entry.PARTY_ID
331 AND (pref.CONTACT_TYPE = 'ALL' or pref.CONTACT_TYPE = p_channel)
332 AND pref.PREFERENCE_CODE = 'DO'-- this indicates it's an OPT-IN preferences
333 AND (pref.PREFERENCE_TOPIC_TYPE is null
334 or pref.PREFERENCE_TOPIC_TYPE = 'CONTACT_USAGE')
335 AND pref.STATUS = 'A'
339 -- This cursor gets only the Global party preferences
336 AND (pref.MAX_NO_OF_INTERACTIONS IS NOT NULL)
337 AND (pref.MAX_NO_OF_INTERACT_UOM_CODE IS NOT NULL);
338
340 -- Global Preferences where contact_type=ALL
341 -- This Cursor will be used when the Schedule Channel is not one of these
342 -- EMAIL,FAX,CALL and MAIL
343 CURSOR C_GET_GLOBAL_PARTY_PREFERENCES(p_list_header_id NUMBER)
344 IS
345 SELECT list_entry.party_id party_id
346 ,list_entry.list_entry_id list_entry_id
347 ,pref.CONTACT_TYPE CONTACT_TYPE
348 ,pref.MAX_NO_OF_INTERACTIONS MAX_NO_OF_INTERACTIONS
349 ,pref.MAX_NO_OF_INTERACT_UOM_CODE MAX_NO_OF_INTERACT_UOM_CODE
350 FROM HZ_CONTACT_PREFERENCES pref,
351 ams_list_entries list_entry
352 WHERE list_entry.LIST_HEADER_ID = p_list_header_id
353 AND pref.contact_level_table='HZ_PARTIES'
354 AND pref.contact_level_table_id = list_entry.PARTY_ID
355 AND pref.CONTACT_TYPE = 'ALL'
356 AND pref.PREFERENCE_CODE = 'DO'-- this indicates it's an OPT-IN preferences
357 AND (pref.PREFERENCE_TOPIC_TYPE is null
358 or pref.PREFERENCE_TOPIC_TYPE = 'CONTACT_USAGE')
359 AND pref.STATUS = 'A'
360 AND (pref.MAX_NO_OF_INTERACTIONS IS NOT NULL)
361 AND (pref.MAX_NO_OF_INTERACT_UOM_CODE IS NOT NULL);
362
363 -- This cursor is to select how many times a party has been contacted
364 -- by fatiguing schedules
365 CURSOR C_GET_PARTY_CONTACTS(p_party_id NUMBER,
366 p_no_of_days NUMBER)
367 IS
368 SELECT count(party_id)
369 FROM ams_tcop_contacts contact
370 WHERE party_id = p_party_id
371 AND contact_date between sysdate and (sysdate - p_no_of_days);
372
373
374 -- This cursor is to select all the contacts made by the fatiguing schedules
375 -- within a time frame specified in the HZ_CONTACT_PREFERENCES
376 CURSOR C_GET_CONTACTED_SCHEDULE(p_party_id NUMBER,
377 p_no_of_days NUMBER
378 )
379 IS
380 SELECT schedule_id
381 FROM AMS_TCOP_CONTACTS
382 WHERE party_id = p_party_id
383 AND contact_date between sysdate and (sysdate - p_no_of_days);
384
385 l_party_id NUMBER;
386 l_max_no_of_interaction NUMBER;
387 l_max_no_of_interact_uom_code VARCHAR2(30);
388 l_contact_type VARCHAR2(30);
389 l_no_of_days NUMBER;
390 l_duplicate_party BOOLEAN;
391 l_party_contact_count NUMBER;
392 i NUMBER;
393
394 l_Fatigue_Party_List JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
395 l_Fatigue_Entry_List JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
396 l_Fatigue_By_Entry_List JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
397 l_Fatigue_By_Schedule_List JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
398
399 BEGIN
400 -- Get Equivalent Channel code as recognized by TCA
401 l_contact_type := GET_TCA_SUPPORTED_CONTACT_TYPE(p_activity_id);
402
403 IF (l_contact_type is not null) then
404 -- The schedule channel is one of the channels that TCA recognizes
405 FOR C1 in C_GET_PARTY_PREFERENCES(p_list_header_id,l_contact_type)
406 LOOP
407 l_no_of_days := INTERPRET_UOM_CODE(C1.MAX_NO_OF_INTERACT_UOM_CODE);
408
409 IF (l_no_of_days is not null) THEN
410
411 -- Get the number of contacts made within the time frame specified
412 -- by the party Opt in preference
413 OPEN C_GET_PARTY_CONTACTS (C1.party_id,l_no_of_days);
414 FETCH C_GET_PARTY_CONTACTS INTO l_party_contact_count;
415 CLOSE C_GET_PARTY_CONTACTS;
416
417
418 IF (l_party_contact_count >= C1.MAX_NO_OF_INTERACTIONS) THEN
419 -- Add the party to the fatigue list, if the party is not already added
420 l_duplicate_party := IS_PARTY_IN_FATIGUE_LIST(C1.party_id);
421
422 IF (NOT(l_duplicate_party)) THEN
423 i := 1;
424 --Get the list of schedules which fatigued this party
425 FOR C2 in C_GET_CONTACTED_SCHEDULE(C1.party_id,l_no_of_days)
426 LOOP
427
428 IF (NOT(l_Fatigue_By_Schedule_List.EXISTS(i))) THEN
429 l_Fatigue_By_Schedule_List.EXTEND;
430 END IF;
431
432 IF (NOT(l_Fatigue_By_Entry_List.EXISTS(i))) THEN
433 l_Fatigue_By_Entry_List.EXTEND;
434 END IF;
435
436 l_Fatigue_By_Schedule_List(i) := C2.schedule_id;
437 l_Fatigue_By_Entry_List(i) := C1.list_entry_id;
438
439
440 i := i + 1;
441 END LOOP;
442
443 -- Create/Update fatigue party list and fatigue entry list
444 IF (NOT(l_Fatigue_Party_List.EXISTS(1))) THEN
445 l_Fatigue_Party_List.EXTEND;
446 END IF;
447
448 IF (NOT(l_Fatigue_Entry_List.EXISTS(1))) THEN
449 l_Fatigue_Entry_List.EXTEND;
450 END IF;
451
452 l_Fatigue_Party_List(1) := C1.party_id;
453 l_Fatigue_Entry_List(1) := C1.list_entry_id;
454
455 APPEND_GLOBAL_FATIGUE_LIST(l_Fatigue_Party_List,
459 );
456 l_Fatigue_Entry_List,
457 l_Fatigue_By_Entry_List,
458 l_Fatigue_By_Schedule_List
460
461 -- Reset the Collection Variables
462 l_Fatigue_Party_List.DELETE;
463 l_Fatigue_Entry_List.DELETE;
464 l_Fatigue_By_Entry_List.DELETE;
465 l_Fatigue_By_Schedule_List.DELETE;
466
467
468 END IF;
469
470 END IF;
471
472 END IF;
473
474 END LOOP;
475
476 END IF;
477
478 END APPLY_PARTY_OPT_IN_PREFERENCES;
479
480
481 PROCEDURE Apply_Fatigue_Rules (
482 p_schedule_id NUMBER
483 )
484 IS
485 STATUS CONSTANT VARCHAR2(30) := 'ACTIVE';
486
487
488 -- Get Target Group List Header Id
489 CURSOR C_Get_List_Header(p_schedule_id NUMBER)
490 IS
491 SELECT header.LIST_HEADER_ID
492 FROM AMS_LIST_HEADERS_ALL header
493 ,AMS_ACT_LISTS act_list
494 WHERE header.LIST_HEADER_ID = act_list.LIST_HEADER_ID
495 and act_list.LIST_ACT_TYPE = 'TARGET'
496 and act_list.LIST_USED_BY = 'CSCH'
497 and act_list.LIST_USED_BY_ID = p_schedule_id;
498
499 -- Get Schedule Details
500 CURSOR C_GET_SCHEDULE_DETAILS(p_schedule_id NUMBER)
501 IS
502 SELECT activity_id
503 FROM AMS_CAMPAIGN_SCHEDULES_B
504 WHERE SCHEDULE_ID = p_schedule_id;
505
506
507 CURSOR C_GET_RULE_DTLS(p_schedule_id NUMBER)
508 IS
509 SELECT rule_id,rule_type
510 FROM ams_tcop_fr_rules_setup rule,
511 ams_campaign_schedules_b schedule,
512 ams_tcop_fr_periods_b period
513 WHERE rule.ENABLED_FLAG = 'Y'
514 AND (rule.CHANNEL_ID is null
515 OR (rule.CHANNEL_ID = schedule.activity_id) )
516 AND rule.RULE_TYPE in ('GLOBAL' , 'CHANNEL_BASED')
517 AND schedule.SCHEDULE_ID = p_schedule_id
518 AND rule.PERIOD_ID = period.PERIOD_ID
519 ORDER BY (rule.MAX_CONTACT_ALLOWED * period.NO_OF_DAYS);
520
521 CURSOR c_Get_Global_Fatigue_list1(p_list_header_id number,
522 p_rule_id number)
523 IS
524 SELECT list.LIST_ENTRY_ID,summary.party_id
525 FROM ams_tcop_contact_summary summary,
526 ams_list_entries list
527 WHERE list.LIST_HEADER_ID = p_list_header_id
528 AND summary.PARTY_ID = list.PARTY_ID
529 AND list.ENABLED_FLAG = 'Y'
530 AND summary.total_contacts >= (SELECT max_contact_allowed
531 FROM ams_tcop_fr_rules_setup
532 WHERE rule_id =p_rule_id);
533
534 CURSOR c_Get_Global_Fatigue_list2(p_list_header_id number,
535 p_rule_id number,
536 p_already_fatigued_list JTF_NUMBER_TABLE
537 )
538 IS
539 SELECT list.LIST_ENTRY_ID,summary.party_id
540 FROM ams_tcop_contact_summary summary,
541 ams_list_entries list
542 WHERE list.LIST_HEADER_ID = p_list_header_id
543 AND summary.PARTY_ID = list.PARTY_ID
544 AND list.ENABLED_FLAG = 'Y'
545 AND summary.total_contacts >= (SELECT max_contact_allowed
546 FROM ams_tcop_fr_rules_setup
547 WHERE rule_id =p_rule_id)
548 AND list.party_id not in
549 (SELECT column_value
550 FROM TABLE(CAST(p_already_fatigued_list as JTF_NUMBER_TABLE))
551 );
555 p_media_id number
552 -- Get the list of parties already over contacted by the channel specific rule
553 CURSOR c_Get_Channel_Fatigue_list1(p_list_header_id number,
554 p_rule_id number,
556 )
557 IS
558 SELECT list.LIST_ENTRY_ID,summary.party_id
559 FROM ams_tcop_channel_summary summary,
560 ams_list_entries list
561 WHERE list.LIST_HEADER_ID = p_list_header_id
562 AND summary.PARTY_ID = list.PARTY_ID
563 AND list.ENABLED_FLAG = 'Y'
564 AND summary.media_id = p_media_id
565 AND summary.total_contacts >= (SELECT max_contact_allowed
566 FROM ams_tcop_fr_rules_setup
567 WHERE rule_id =p_rule_id);
568
569 -- Get the list of parties already over contacted by the channel specific rule
570 -- But don't consider the list of parties already fatigued by another rule
571 CURSOR c_Get_Channel_Fatigue_list2(p_list_header_id number,
572 p_rule_id number,
573 p_media_id number,
574 p_already_fatigued_list JTF_NUMBER_TABLE
575 )
576 IS
577 SELECT list.LIST_ENTRY_ID,summary.party_id
578 FROM ams_tcop_channel_summary summary,
579 ams_list_entries list
580 WHERE list.LIST_HEADER_ID = p_list_header_id
581 AND summary.PARTY_ID = list.PARTY_ID
582 AND list.ENABLED_FLAG = 'Y'
583 AND summary.media_id = p_media_id
584 AND summary.total_contacts >= (SELECT max_contact_allowed
585 FROM ams_tcop_fr_rules_setup
586 WHERE rule_id =p_rule_id)
587 AND list.party_id not in
588 (SELECT column_value
589 FROM TABLE(CAST(p_already_fatigued_list as JTF_NUMBER_TABLE))
590 );
591 -- Cursor to select the schedules which have already fatigued
592 -- people as per the Global Rule
593 /**
594 CURSOR c_Get_Global_Fatigue_By_List(p_list_header_id number,
595 p_rule_id number)
596 IS
597 SELECT list.LIST_ENTRY_ID,sum_dtl.SCHEDULE_ID
598 FROM ams_tcop_contact_summary summary,
599 ams_list_entries list,
600 ams_tcop_contact_sum_dtl sum_dtl
601 WHERE list.LIST_HEADER_ID =
602 AND summary.PARTY_ID = list.PARTY_ID
603 AND summary.total_contacts = (SELECT max_contact_allowed
604 FROM ams_tcop_fr_rules_setup
605 WHERE rule_id = p_rule_id)
606 AND summary.CONTACT_SUMMARY_ID = sum_dtl.CONTACT_SUMMARY_ID;
607 **/
608
609 -- Cursor to select the schedules which have already fatigued
610 -- people as per the Global Rule
611 --
612 -- Note: Leading Hint is used to improve the performance of the query.
613 -- This hint is used to make sure that the optimizer will make the
614 -- party_list, the Nested Table as the driving table.
615 -- This table should be the driving table since it will
616 -- be the smallest table in the join.
617 CURSOR c_Get_Global_Fatigue_By_List(p_list_header_id NUMBER
618 ,p_ftg_party_list JTF_NUMBER_TABLE)
619 IS
620 SELECT /*+ leading(party_list) +*/
621 list.LIST_ENTRY_ID,sum_dtl.SCHEDULE_ID
622 FROM ams_tcop_contact_summary summary,
623 ams_list_entries list,
624 ams_tcop_contact_sum_dtl sum_dtl,
625 (SELECT column_value party_id
626 FROM TABLE(CAST(p_ftg_party_list as JTF_NUMBER_TABLE))
627 ) party_list
628 WHERE summary.CONTACT_SUMMARY_ID = sum_dtl.CONTACT_SUMMARY_ID
629 AND list.ENABLED_FLAG = 'Y'
630 AND list.list_header_id = p_list_header_id
631 AND summary.party_id = list.party_id
632 AND summary.party_id = party_list.party_id;
633
634 -- Cursor to select the schedules which have already fatigued
635 -- people as per the Channel Rule
636 --
637 -- Note: Leading Hint is used to improve the performance of the query.
641 -- be the smallest table in the join.
638 -- This hint is used to make sure that the optimizer will make the
639 -- party_list, the Nested Table as the driving table.
640 -- This table should be the driving table since it will
642 CURSOR c_Get_Channel_Fatigue_By_List(p_ftg_party_list JTF_NUMBER_TABLE,
643 p_media_id NUMBER,
644 p_list_header_id NUMBER
645 )
646 IS
647 SELECT /*+ leading(party_list) +*/
648 list.LIST_ENTRY_ID,sum_dtl.SCHEDULE_ID
649 FROM ams_tcop_channel_summary summary,
650 ams_list_entries list,
651 ams_tcop_channel_sum_dtl sum_dtl,
652 (SELECT column_value party_id
653 FROM TABLE(CAST(p_ftg_party_list as JTF_NUMBER_TABLE))
654 ) party_list
655 WHERE summary.CHANNEL_SUMMARY_ID = sum_dtl.CHANNEL_SUMMARY_ID
656 AND summary.party_id = list.party_id
657 AND summary.party_id = party_list.party_id
658 AND summary.media_id = p_media_id
659 AND list.list_header_id = p_list_header_id;
660
661 -- Get the List of parties not fatigued and will be contacted
662 CURSOR C_GET_CONTACTED_PARTY (p_list_header_id NUMBER,p_fatigue_party_list JTF_NUMBER_TABLE)
663 IS
664 (SELECT list_entry.PARTY_ID
665 FROM AMS_LIST_ENTRIES list_entry
666 WHERE list_entry.list_header_id = p_list_header_id
667 AND list_entry.ENABLED_FLAG = 'Y')
668 MINUS
669 (SELECT column_value
670 FROM TABLE(CAST(p_FATIGUE_PARTY_LIST as JTF_NUMBER_TABLE))
671 );
672
673 -- Get the Sequence value
674 CURSOR C_GET_NEXT_CONTACT_SEQ
675 IS
676 SELECT AMS_TCOP_CONTACTS_S.NEXTVAL
677 FROM DUAL;
678
679 -- Get the Sequence value
680 -- CURSOR C_GET_NEXT_FATIGUE_BY_SEQ
681 -- IS
682 -- SELECT AMS_TCOP_FATIGUED_BY_S.NEXTVAL
683 -- FROM DUAL;
684
685 TYPE Number_Table is Table of Number INDEX BY BINARY_INTEGER;
686
687 -- Temporary List Variables
688 l_temp_party_list JTF_NUMBER_TABLE;
689 l_temp_entry_list JTF_NUMBER_TABLE;
690 l_temp_fatigue_by_entry_list JTF_NUMBER_TABLE;
691 l_temp_fatigue_by_sched_list JTF_NUMBER_TABLE;
692 l_contacted_party_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
693
694 -- Other Local Arrays
695 l_contact_id_list Number_Table;
696 -- l_fatigue_by_id_list Number_Table;
697
698 -- Other Local Variables
699 l_list_header_id NUMBER;
700 l_activity_id NUMBER;
701 l_contact_id NUMBER;
702 l_fatigue_entry_list AMS_ListGeneration_PKG.t_number;
703 l_label VARCHAR2(50);
704 l_global_rule_id number;
705 l_channel_rule_id number;
706 L_CONTACTED_PARTY_COUNT NUMBER;
707 l_temp_count number;
708 l_count number;
709 l_sequence_id NUMBER;
710
711 -- Constant
712 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'APPLY_FATIGUE_RULES';
713
714 BEGIN
715
716 write_debug_message(LOG_LEVEL_PROCEDURE,
717 PROCEDURE_NAME,
718 'BEGIN',
719 'Beginning Procedure'
720 );
721
722 write_debug_message(LOG_LEVEL_PROCEDURE,
723 PROCEDURE_NAME,
724 'WRITE_INPUT_PARAMETERS',
725 'Applying Traffic Cop for Schedule ID '||to_char(p_schedule_id)
726 );
727 AMS_Utility_PVT.Write_Conc_Log('AMS_TCOP_ENGINE_PKG.Apply_Fatigue_Rules ====> Begin Apply Fatigue Rules for Schedule ID = ' || to_char(p_schedule_id));
728
729
730 -- 3/1/2004 mayjain fix for bug 3470706 START
731 -- This variable is list of all the parties fatigued
732 G_Fatigue_Party_List := JTF_NUMBER_TABLE();
733
734 -- This variable is list of all the list_entry_id that are associated
738 -- This is a list of list_entry_id associated with the list
735 -- with fatigue parties
736 G_Fatigue_Entry_List := JTF_NUMBER_TABLE();
737
739 -- of schedules which fatigued the parties. This list may contain
740 -- duplicate entries.
741 G_Fatigue_By_Entry_List := JTF_NUMBER_TABLE();
742
743 -- This is a list of list_entry_id associated with the list
744 -- of schedules which fatigued the parties
745 G_Fatigue_By_Schedule_List := JTF_NUMBER_TABLE();
746 -- 3/1/2004 mayjain fix for bug 3470706 END
747
748 -- Get Target Group List Header Id
749 OPEN C_Get_List_Header(p_schedule_id);
750 FETCH C_Get_List_Header INTO l_list_header_id;
751 CLOSE C_Get_List_Header;
752
753 write_debug_message(LOG_LEVEL_EVENT,
754 PROCEDURE_NAME,
755 'GET_LIST_HEADER_ID',
756 'List Header Id = '||to_char(l_list_header_id)
757 );
758
759 -- Get some of the relevant Schedule Details
760 OPEN C_GET_SCHEDULE_DETAILS(p_schedule_id);
761 FETCH C_GET_SCHEDULE_DETAILS INTO l_activity_id;
762 CLOSE C_GET_SCHEDULE_DETAILS;
763
764 write_debug_message(LOG_LEVEL_EVENT,
765 PROCEDURE_NAME,
766 'GET_SCHEDULE_DETAILS',
767 'Activity Id = '||to_char(l_activity_id)
768 );
769
770 write_debug_message(LOG_LEVEL_EVENT,
771 PROCEDURE_NAME,
772 'BEFORE_CALLING_SUMMARIZATION',
773 'Before Calling Summarization AMS_TCOP_SUMMARIZATION_PKG.SUMMARIZE_LIST_CONTACTS'
774 );
775
776 -- Summarize Contacts
777 AMS_TCOP_SUMMARIZATION_PKG.SUMMARIZE_LIST_CONTACTS(l_list_header_id,l_activity_id);
778
779 write_debug_message(LOG_LEVEL_EVENT,
780 PROCEDURE_NAME,
781 'AFTER_CALLING_SUMMARIZATION',
782 'After Calling Summarization AMS_TCOP_SUMMARIZATION_PKG.SUMMARIZE_LIST_CONTACTS'
783 );
784
785 -- Get the Applicable Rules for this schedule
786 l_label := 'GET_RULE_DETAILS_LOOP';
787 FOR C1 in C_GET_RULE_DTLS(p_schedule_id)
788 LOOP
789
790 write_debug_message(LOG_LEVEL_EVENT,
791 PROCEDURE_NAME,
792 l_label,
793 'Applicable Fatigue Rule Type = '||C1.RULE_TYPE
794 );
795
796 write_debug_message(LOG_LEVEL_EVENT,
797 PROCEDURE_NAME,
798 l_label,
799 'Applicable Fatigue Rule Id = '||to_char(C1.RULE_ID)
800 );
801
802 --Apply Global Rules to check which parties are fatigued by this rule
803 IF (C1.RULE_TYPE = 'GLOBAL') THEN
804 l_global_rule_id := C1.RULE_ID;
805
806 l_label := l_label;
807
808 write_debug_message(LOG_LEVEL_EVENT,
809 PROCEDURE_NAME,
810 l_label,
811 'Applicable Rule Is a Global Rule'
812 );
813
814
815 write_debug_message(LOG_LEVEL_EVENT,
816 PROCEDURE_NAME,
817 l_label,
818 'Size of Global Fatigue Party List = ' || to_char(G_fatigue_party_list.count)
819 );
820
821 IF (G_fatigue_party_list.count = 0) THEN
822
823 -- Get the list of Fatigued Entries already reached a threshold
824 -- as per the Global Rule
825 OPEN c_Get_Global_Fatigue_list1(l_list_header_id,C1.rule_id);
826 FETCH c_Get_Global_Fatigue_list1
827 BULK COLLECT INTO l_temp_entry_list,l_temp_party_list;
828 CLOSE c_Get_Global_Fatigue_list1;
829
830 ELSE
831 -- Since some parties have already been fatigued by other more
832 -- restrictive rule, don't need to consider those parties again
833 OPEN c_Get_Global_Fatigue_list2(l_list_header_id,C1.rule_id
834 ,G_fatigue_party_list);
835 FETCH c_Get_Global_Fatigue_list2
836 BULK COLLECT INTO l_temp_entry_list,l_temp_party_list;
837 CLOSE c_Get_Global_Fatigue_list2;
838
839 END IF;
840
841 IF (l_temp_entry_list.exists(1)) then
842
843 write_debug_message(LOG_LEVEL_EVENT,
844 PROCEDURE_NAME,
845 l_label,
846 'There are parties fatigued by Global Rule and the total number is = '||to_char(l_temp_entry_list.count)
847 );
848
849 -- Get the List of Schedules which are responsible for fatiguing
850 -- the entrie
851 OPEN c_Get_Global_Fatigue_By_List(l_list_header_id,
852 l_temp_party_list
853 );
854 FETCH c_Get_Global_Fatigue_By_List
855 BULK COLLECT INTO l_temp_fatigue_by_entry_list
856 ,l_temp_fatigue_by_sched_list;
857 CLOSE c_Get_Global_Fatigue_By_List;
858
859 IF (l_temp_fatigue_by_entry_list.exists(1)) then
860
861 write_debug_message(LOG_LEVEL_EVENT,
862 PROCEDURE_NAME,
863 l_label,
864 ' Number of Schedules found which caused the fatigue = '||to_char(l_temp_fatigue_by_entry_list.count)
865 );
869 write_debug_message(LOG_LEVEL_EVENT,
866
867 END IF;
868
870 PROCEDURE_NAME,
871 l_label,
872 'Copy the Temporary List to Global List'
873 );
874
875 -- Copy the temporary variables into the Global lists
876 APPEND_GLOBAL_FATIGUE_LIST(l_temp_party_list,
877 l_temp_entry_list,
878 l_temp_fatigue_by_entry_list,
879 l_temp_fatigue_by_sched_list
880 );
881
882 write_debug_message(LOG_LEVEL_EVENT,
883 PROCEDURE_NAME,
884 l_label,
885 'Temporary List Successfully copied to Global List'
886 );
887 END IF;
888
889 END IF;
890
891 --Apply Channel based Rules to check which parties are fatigued by this rule
892 IF (C1.RULE_TYPE = 'CHANNEL_BASED') THEN
893 l_channel_rule_id := C1.RULE_ID;
894 l_label := l_label;
895
896 write_debug_message(LOG_LEVEL_EVENT,
897 PROCEDURE_NAME,
898 l_label,
899 'Applicable Rule Is a Channel Rule'
900 );
901
902 -- Get the list of Fatigued Entries already reached a threshold
903 -- as per the Channel Rule
904 IF (G_Fatigue_Party_List.count = 0) THEN
905 OPEN c_Get_Channel_Fatigue_list1(l_list_header_id,
906 C1.rule_id,
907 l_activity_id);
908 FETCH c_Get_Channel_Fatigue_list1
909 BULK COLLECT INTO l_temp_entry_list,l_temp_party_list;
910 CLOSE c_Get_Channel_Fatigue_list1;
911 ELSE
912 OPEN c_Get_Channel_Fatigue_list2(l_list_header_id,
913 C1.rule_id,
914 l_activity_id,
915 G_fatigue_party_list
916 );
917 FETCH c_Get_Channel_Fatigue_list2
918 BULK COLLECT INTO l_temp_entry_list,l_temp_party_list;
919 CLOSE c_Get_Channel_Fatigue_list2;
920 END IF;
921
922 IF (l_temp_entry_list.exists(1)) then
923
924 write_debug_message(LOG_LEVEL_EVENT,
925 PROCEDURE_NAME,
926 l_label,
927 'Channel Rule has fatigued some parties and the count = '||to_char(l_temp_entry_list.count)
928 );
929
930 -- Get the List of Schedules which are responsible for fatiguing
931 -- the entries
932 OPEN c_Get_Channel_Fatigue_By_List(l_temp_party_list
933 ,l_activity_id
934 ,l_list_header_id
935 );
936 FETCH c_Get_Channel_Fatigue_By_List
937 BULK COLLECT INTO l_temp_fatigue_by_entry_list
938 ,l_temp_fatigue_by_sched_list;
939 CLOSE c_Get_Channel_Fatigue_By_List;
940
941 IF (l_temp_fatigue_by_entry_list.exists(1)) then
942
943 write_debug_message(LOG_LEVEL_EVENT,
944 PROCEDURE_NAME,
945 l_label,
946 'Schedules found which caused the fatigue and the number of schedules = '||to_char(l_temp_fatigue_by_entry_list.count)
947 );
948
949 END IF;
950
951 write_debug_message(LOG_LEVEL_EVENT,
952 PROCEDURE_NAME,
953 l_label,
954 'Copy the Temporary List to Global List'
955 );
956
957 -- Copy the temporary variables into the Global lists
958 APPEND_GLOBAL_FATIGUE_LIST(l_temp_party_list,
959 l_temp_entry_list,
960 l_temp_fatigue_by_entry_list,
961 l_temp_fatigue_by_sched_list
962 );
963
967 'Temporary List Successfully copied to Global List'
964 write_debug_message(LOG_LEVEL_EVENT,
965 PROCEDURE_NAME,
966 l_label,
968 );
969 END IF;
970
971 END IF;
972
973 END LOOP;
974
975
976 write_debug_message(LOG_LEVEL_PROCEDURE,
977 PROCEDURE_NAME,
978 'BEFORE_API_CALL',
979 'Before Calling APPLY_PARTY_OPT_IN_PREFERENCES API'
980 );
981 -- Check if party's OPT-IN Preferences are set or not
982 -- This API will add parties to the Fatigue List based on the following:
983 -- 1. The Opt-In preferences for the parties must have been set
984 -- 2. The number of contacts already reached the maximum threshold
985 -- as per the contact preferences.
986 APPLY_PARTY_OPT_IN_PREFERENCES(l_list_header_id,l_activity_id);
987
988 write_debug_message(LOG_LEVEL_PROCEDURE,
989 PROCEDURE_NAME,
990 'AFTER_API_CALL',
991 'After Calling APPLY_PARTY_OPT_IN_PREFERENCES API'
992 );
993
994 -- Once the fatigue list is produced, update the AMS_LIST_ENTRIES
995 -- , set the enabled_flag to N,MARKED_AS_FATIGUED_FLAG to Y, update the
996 -- counts in the List Header
997 --
998 IF (G_Fatigue_Entry_List.EXISTS(1)) THEN
999 l_label := 'DISABLE_LIST_ENTRIES';
1000
1001 write_debug_message(LOG_LEVEL_EVENT,
1002 PROCEDURE_NAME,
1003 l_label,
1004 'Before Copying the Nested Table to Index By Table'
1005 );
1006
1007 -- First, copy the Nested Table to a Index By Table
1008 FOR i IN G_Fatigue_Entry_List.FIRST .. G_Fatigue_Entry_List.LAST
1009 LOOP
1010 l_fatigue_entry_list(i) := G_Fatigue_Entry_List(i);
1011 END LOOP;
1012
1013 write_debug_message(LOG_LEVEL_EVENT,
1014 PROCEDURE_NAME,
1015 l_label,
1016 'Successfully copied Nested Table to Index By Table'
1017 );
1018
1019 write_debug_message(LOG_LEVEL_EVENT,
1020 PROCEDURE_NAME,
1021 l_label,
1022 'Total Number of fatigued entries = '||to_char(l_fatigue_entry_list.count)
1023 );
1024
1025 write_debug_message(LOG_LEVEL_EVENT,
1026 PROCEDURE_NAME,
1027 l_label,
1028 'Before Calling AMS_ListGeneration_PKG.UPDATE_FOR_TRAFFIC_COP'
1029 );
1030
1031 AMS_ListGeneration_PKG.UPDATE_FOR_TRAFFIC_COP
1032 ( p_list_header_id => l_list_header_id,
1033 p_list_entry_id => l_fatigue_entry_list);
1034
1035 write_debug_message(LOG_LEVEL_EVENT,
1036 PROCEDURE_NAME,
1037 l_label,
1038 'After Calling AMS_ListGeneration_PKG.UPDATE_FOR_TRAFFIC_COP'
1039 );
1040
1041 END IF;
1042
1043 l_label := 'SET_TCOP_FATIGUED_BY';
1044 l_count := G_Fatigue_By_Entry_List.count;
1045 write_debug_message(LOG_LEVEL_PROCEDURE,
1046 PROCEDURE_NAME,
1047 l_label,
1048 'Ready to create entries in AMS_TCOP_FATIGUED_BY. Number of entries = '||to_char(l_count)
1049 );
1050 AMS_Utility_PVT.Write_Conc_Log('AMS_TCOP_ENGINE_PKG.Apply_Fatigue_Rules ====> Ready to create entries in AMS_TCOP_FATIGUED_BY. Number of entries = '||to_char(l_count));
1051
1052
1053 --Bulk Insert into AMS_TCOP_FATIGUED_BY table
1054 IF(l_count > 0) THEN
1055 -- Create an array of Sequence number
1056 -- FOR i in G_Fatigue_By_Entry_List.FIRST .. G_Fatigue_By_Entry_List.LAST
1057 -- LOOP
1058 -- OPEN C_GET_NEXT_FATIGUE_BY_SEQ;
1059 -- FETCH C_GET_NEXT_FATIGUE_BY_SEQ
1060 -- INTO l_sequence_id;
1061 -- CLOSE C_GET_NEXT_FATIGUE_BY_SEQ;
1062 -- l_fatigue_by_id_list(i) := l_sequence_id;
1063
1064
1065 -- END LOOP;
1066
1067 write_debug_message(LOG_LEVEL_EVENT,
1068 PROCEDURE_NAME,
1069 l_label,
1070 'Before Calling BULK Insert into AMS_TCOP_FATIGUE_BY'
1071 );
1072 write_debug_message(LOG_LEVEL_EVENT,
1073 PROCEDURE_NAME,
1074 l_label,
1075 'Total Number of entries in Fatigue By Entry List ='||to_char(G_Fatigue_By_Entry_List.count)
1076 );
1077 write_debug_message(LOG_LEVEL_EVENT,
1078 PROCEDURE_NAME,
1079 l_label,
1080 'Total Number of entries in Fatigue By Schedule List ='||to_char(G_Fatigue_By_Schedule_List.count)
1081 );
1082
1083 -- Do a Bulk Insert into AMS_TCOP_FATIGUE_BY Table
1084 FOR i in G_Fatigue_By_Entry_List.FIRST .. G_Fatigue_By_Entry_List.LAST
1085 LOOP
1086 INSERT INTO
1087 AMS_TCOP_FATIGUED_BY
1088 (fatigued_by_id,
1089 list_entry_id,
1090 schedule_id,
1091 creation_date,
1092 created_by,
1093 last_update_date,
1097 VALUES
1094 last_updated_by,
1095 last_update_login
1096 )
1098 (--l_fatigue_by_id_list(i),
1099 AMS_TCOP_FATIGUED_BY_S.NEXTVAL,
1100 G_Fatigue_By_Entry_List(i),
1101 G_Fatigue_By_Schedule_List(i),
1102 sysdate,
1103 FND_GLOBAL.USER_ID,
1104 sysdate,
1105 FND_GLOBAL.USER_ID,
1106 FND_GLOBAL.USER_ID
1107 );
1108 END LOOP;
1109
1110 write_debug_message(LOG_LEVEL_EVENT,
1111 PROCEDURE_NAME,
1112 l_label,
1113 'After Calling BULK Insert into AMS_TCOP_FATIGUED_BY'
1114 );
1115
1116 write_debug_message(LOG_LEVEL_EVENT,
1117 PROCEDURE_NAME,
1118 'UPDATE_CONTACT_SUMMARY',
1119 'Before Calling AMS_TCOP_SUMMARIZATION_PKG.UPDATE_CONTACT_COUNT'
1120 );
1121
1122 l_label := 'SET_TCOP_CONTACTS';
1123 write_debug_message(LOG_LEVEL_PROCEDURE,
1124 PROCEDURE_NAME,
1125 l_label,
1126 'Ready to create entries in AMS_TCOP_CONTACTS'
1127 );
1128
1129 END IF;
1130
1131 -- Get the list of contacted party
1132 OPEN C_GET_CONTACTED_PARTY(l_list_header_id,g_fatigue_party_list);
1133 FETCH C_GET_CONTACTED_PARTY
1134 BULK COLLECT INTO l_contacted_party_list;
1135 CLOSE C_GET_CONTACTED_PARTY;
1136
1137 l_contacted_party_count := l_contacted_party_list.count;
1138 write_debug_message(LOG_LEVEL_EVENT,
1139 PROCEDURE_NAME,
1140 l_label,
1141 'Total Number of contacts = '||
1142 to_char(l_contacted_party_count)
1143 );
1144
1145 --Update the AMS_TCOP_CONTACTS table
1146 IF (l_contacted_party_count > 0) THEN
1147 -- Create an array of Sequence number
1148 FOR i in l_contacted_party_list.FIRST .. l_contacted_party_list.LAST
1149 LOOP
1150 OPEN C_GET_NEXT_CONTACT_SEQ;
1151 FETCH C_GET_NEXT_CONTACT_SEQ
1152 INTO l_CONTACT_ID;
1153 CLOSE C_GET_NEXT_CONTACT_SEQ;
1154
1155 l_contact_id_list(i) := l_CONTACT_ID;
1156 END LOOP;
1157
1158 write_debug_message(LOG_LEVEL_EVENT,
1159 PROCEDURE_NAME,
1160 l_label,
1161 'Before Calling BULK Insert into AMS_TCOP_CONTACTS'
1162 );
1163
1164 -- Do a Bulk Insert into AMS_TCOP_CONTACTS Table
1165 FORALL i in l_contacted_party_list.FIRST .. l_contacted_party_list.LAST
1166 INSERT INTO
1167 AMS_TCOP_CONTACTS
1168 (contact_id,
1169 party_id,
1170 schedule_id,
1171 media_id,
1172 contact_date,
1173 creation_date,
1174 created_by,
1175 last_update_date,
1176 last_updated_by,
1177 last_update_login
1178 )
1179 VALUES
1180 (l_contact_id_list(i),
1181 l_contacted_party_list(i),
1182 p_schedule_id,
1183 l_activity_id,
1184 sysdate,
1185 sysdate,
1186 FND_GLOBAL.USER_ID,
1187 sysdate,
1188 FND_GLOBAL.USER_ID,
1189 FND_GLOBAL.USER_ID
1190 );
1191
1192 write_debug_message(LOG_LEVEL_EVENT,
1193 PROCEDURE_NAME,
1194 l_label,
1195 'After Calling BULK Insert into AMS_TCOP_CONTACTS'
1196 );
1197
1198 write_debug_message(LOG_LEVEL_EVENT,
1202 );
1199 PROCEDURE_NAME,
1200 'UPDATE_CONTACT_SUMMARY',
1201 'Before Calling AMS_TCOP_SUMMARIZATION_PKG.UPDATE_CONTACT_COUNT'
1203 -- Update Summary
1204 AMS_TCOP_SUMMARIZATION_PKG.UPDATE_CONTACT_COUNT(
1205 l_contacted_party_list,
1206 p_schedule_id,
1207 l_activity_id,
1208 l_global_rule_id,
1209 l_channel_rule_id
1210 );
1211
1212 write_debug_message(LOG_LEVEL_EVENT,
1213 PROCEDURE_NAME,
1214 'UPDATE_CONTACT_SUMMARY',
1215 'After Calling AMS_TCOP_SUMMARIZATION_PKG.UPDATE_CONTACT_COUNT'
1216 );
1217 END IF;
1218
1219 -- Update the Request Status to 'ACTIVE' to indicate
1220 -- that Traffic Cop Engine Processing is going on
1221 AMS_TCOP_SCHEDULER_PKG.UPDATE_STATUS(p_schedule_id,'COMPLETED');
1222
1223 END Apply_Fatigue_Rules;
1224
1225 END AMS_TCOP_ENGINE_PKG;
[Home] [Help]