[Home] [Help]
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();
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.
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();
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;
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: )
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
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:
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;
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;
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:
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
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
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);
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,
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: );
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,
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,
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: /**
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,
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
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
641: -- be the smallest table in the join.
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
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
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
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();
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:
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
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;
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;
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
735: -- with fatigue parties
736: G_Fatigue_Entry_List := JTF_NUMBER_TABLE();
732: G_Fatigue_Party_List := JTF_NUMBER_TABLE();
733:
734: -- This variable is list of all the list_entry_id that are associated
735: -- with fatigue parties
736: G_Fatigue_Entry_List := JTF_NUMBER_TABLE();
737:
738: -- This is a list of list_entry_id associated with the list
739: -- of schedules which fatigued the parties. This list may contain
740: -- duplicate entries.
737:
738: -- This is a list of list_entry_id associated with the list
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();
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);