APPS.AMS_TCOP_ENGINE_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 228

SELECT party_list.party_id
FROM
(SELECT column_value party_id
 FROM TABLE(CAST(G_Fatigue_Party_List as JTF_NUMBER_TABLE))
) party_list
WHERE party_list.party_id=p_party_id;

Line: 321

SELECT 	list_entry.party_id
   ,list_entry.list_entry_id
	,pref.CONTACT_TYPE
	,pref.MAX_NO_OF_INTERACTIONS
	,pref.MAX_NO_OF_INTERACT_UOM_CODE
FROM HZ_CONTACT_PREFERENCES pref,
     ams_list_entries list_entry
WHERE list_entry.LIST_HEADER_ID = p_list_header_id
AND pref.contact_level_table='HZ_PARTIES'
AND pref.contact_level_table_id = list_entry.PARTY_ID
AND (pref.CONTACT_TYPE = 'ALL' or pref.CONTACT_TYPE = p_channel)
AND pref.PREFERENCE_CODE = 'DO'-- this indicates it's an OPT-IN preferences
AND (pref.PREFERENCE_TOPIC_TYPE is null
     or  pref.PREFERENCE_TOPIC_TYPE = 'CONTACT_USAGE')
AND pref.STATUS = 'A'
AND (pref.MAX_NO_OF_INTERACTIONS IS NOT NULL)
AND (pref.MAX_NO_OF_INTERACT_UOM_CODE IS NOT NULL);

Line: 345

SELECT	list_entry.party_id party_id
			,list_entry.list_entry_id list_entry_id
			,pref.CONTACT_TYPE CONTACT_TYPE
			,pref.MAX_NO_OF_INTERACTIONS MAX_NO_OF_INTERACTIONS
			,pref.MAX_NO_OF_INTERACT_UOM_CODE MAX_NO_OF_INTERACT_UOM_CODE
FROM HZ_CONTACT_PREFERENCES pref,
     ams_list_entries list_entry
WHERE list_entry.LIST_HEADER_ID = p_list_header_id
AND pref.contact_level_table='HZ_PARTIES'
AND pref.contact_level_table_id = list_entry.PARTY_ID
AND pref.CONTACT_TYPE = 'ALL'
AND pref.PREFERENCE_CODE = 'DO'-- this indicates it's an OPT-IN preferences
AND (pref.PREFERENCE_TOPIC_TYPE is null
     or  pref.PREFERENCE_TOPIC_TYPE = 'CONTACT_USAGE')
AND pref.STATUS = 'A'
AND (pref.MAX_NO_OF_INTERACTIONS IS NOT NULL)
AND (pref.MAX_NO_OF_INTERACT_UOM_CODE IS NOT NULL);

Line: 368

SELECT count(party_id)
FROM ams_tcop_contacts contact
WHERE party_id = p_party_id
AND contact_date between sysdate and (sysdate - p_no_of_days);

Line: 380

SELECT schedule_id
FROM AMS_TCOP_CONTACTS
WHERE party_id = p_party_id
AND contact_date between sysdate and (sysdate - p_no_of_days);

Line: 462

                  l_Fatigue_Party_List.DELETE;

Line: 463

                  l_Fatigue_Entry_List.DELETE;

Line: 464

                  l_Fatigue_By_Entry_List.DELETE;

Line: 465

                  l_Fatigue_By_Schedule_List.DELETE;

Line: 491

   SELECT header.LIST_HEADER_ID
   FROM   AMS_LIST_HEADERS_ALL header
          ,AMS_ACT_LISTS act_list
   WHERE header.LIST_HEADER_ID = act_list.LIST_HEADER_ID
   and   act_list.LIST_ACT_TYPE = 'TARGET'
   and   act_list.LIST_USED_BY = 'CSCH'
   and act_list.LIST_USED_BY_ID = p_schedule_id;

Line: 502

   SELECT activity_id
   FROM AMS_CAMPAIGN_SCHEDULES_B
   WHERE SCHEDULE_ID = p_schedule_id;

Line: 509

   SELECT rule_id,rule_type
   FROM   ams_tcop_fr_rules_setup rule,
          ams_campaign_schedules_b schedule,
          ams_tcop_fr_periods_b period
   WHERE  rule.ENABLED_FLAG = 'Y'
   AND    (rule.CHANNEL_ID is null
          OR (rule.CHANNEL_ID = schedule.activity_id) )
   AND    rule.RULE_TYPE in ('GLOBAL' , 'CHANNEL_BASED')
   AND    schedule.SCHEDULE_ID = p_schedule_id
   AND    rule.PERIOD_ID = period.PERIOD_ID
   ORDER BY (rule.MAX_CONTACT_ALLOWED * period.NO_OF_DAYS);

Line: 524

   SELECT list.LIST_ENTRY_ID,summary.party_id
   FROM ams_tcop_contact_summary summary,
        ams_list_entries list
   WHERE list.LIST_HEADER_ID = p_list_header_id
   AND   summary.PARTY_ID = list.PARTY_ID
   AND   list.ENABLED_FLAG = 'Y'
   AND   summary.total_contacts >= (SELECT max_contact_allowed
                                   FROM ams_tcop_fr_rules_setup
				                       WHERE rule_id =p_rule_id);

Line: 539

   SELECT list.LIST_ENTRY_ID,summary.party_id
   FROM ams_tcop_contact_summary summary,
        ams_list_entries list
   WHERE list.LIST_HEADER_ID = p_list_header_id
   AND   summary.PARTY_ID = list.PARTY_ID
   AND   list.ENABLED_FLAG = 'Y'
   AND   summary.total_contacts >= (SELECT max_contact_allowed
                                   FROM ams_tcop_fr_rules_setup
				                       WHERE rule_id =p_rule_id)
   AND   list.party_id not in
                 (SELECT column_value
                  FROM TABLE(CAST(p_already_fatigued_list as JTF_NUMBER_TABLE))
                 );

Line: 558

   SELECT list.LIST_ENTRY_ID,summary.party_id
   FROM ams_tcop_channel_summary summary,
        ams_list_entries list
   WHERE list.LIST_HEADER_ID = p_list_header_id
   AND   summary.PARTY_ID = list.PARTY_ID
   AND   list.ENABLED_FLAG = 'Y'
   AND   summary.media_id = p_media_id
   AND   summary.total_contacts >= (SELECT max_contact_allowed
                                   FROM ams_tcop_fr_rules_setup
				                       WHERE rule_id =p_rule_id);

Line: 577

   SELECT list.LIST_ENTRY_ID,summary.party_id
   FROM ams_tcop_channel_summary summary,
        ams_list_entries list
   WHERE list.LIST_HEADER_ID = p_list_header_id
   AND   summary.PARTY_ID = list.PARTY_ID
   AND   list.ENABLED_FLAG = 'Y'
   AND   summary.media_id = p_media_id
   AND   summary.total_contacts >= (SELECT max_contact_allowed
                                   FROM ams_tcop_fr_rules_setup
				                       WHERE rule_id =p_rule_id)
   AND   list.party_id not in
                 (SELECT column_value
                  FROM TABLE(CAST(p_already_fatigued_list as JTF_NUMBER_TABLE))
                 );

Line: 597

   SELECT list.LIST_ENTRY_ID,sum_dtl.SCHEDULE_ID
   FROM ams_tcop_contact_summary summary,
        ams_list_entries list,
	ams_tcop_contact_sum_dtl sum_dtl
   WHERE list.LIST_HEADER_ID =
   AND   summary.PARTY_ID = list.PARTY_ID
   AND   summary.total_contacts = (SELECT max_contact_allowed
				   FROM ams_tcop_fr_rules_setup
				   WHERE rule_id = p_rule_id)
   AND   summary.CONTACT_SUMMARY_ID = sum_dtl.CONTACT_SUMMARY_ID;

Line: 620

   SELECT /*+ leading(party_list) +*/
   list.LIST_ENTRY_ID,sum_dtl.SCHEDULE_ID
   FROM ams_tcop_contact_summary summary,
        ams_list_entries list,
        ams_tcop_contact_sum_dtl sum_dtl,
        (SELECT column_value party_id
	      FROM TABLE(CAST(p_ftg_party_list as JTF_NUMBER_TABLE))
        ) party_list
   WHERE summary.CONTACT_SUMMARY_ID = sum_dtl.CONTACT_SUMMARY_ID
   AND   list.ENABLED_FLAG = 'Y'
   AND   list.list_header_id = p_list_header_id
   AND   summary.party_id = list.party_id
   AND   summary.party_id = party_list.party_id;

Line: 647

   SELECT /*+ leading(party_list) +*/
   list.LIST_ENTRY_ID,sum_dtl.SCHEDULE_ID
   FROM ams_tcop_channel_summary summary,
        ams_list_entries list,
        ams_tcop_channel_sum_dtl sum_dtl,
        (SELECT column_value party_id
	      FROM TABLE(CAST(p_ftg_party_list as JTF_NUMBER_TABLE))
        ) party_list
   WHERE summary.CHANNEL_SUMMARY_ID = sum_dtl.CHANNEL_SUMMARY_ID
   AND   summary.party_id = list.party_id
   AND   summary.party_id = party_list.party_id
   AND   summary.media_id = p_media_id
   AND   list.list_header_id = p_list_header_id;

Line: 664

   (SELECT list_entry.PARTY_ID
   FROM   AMS_LIST_ENTRIES list_entry
   WHERE  list_entry.list_header_id = p_list_header_id
   AND    list_entry.ENABLED_FLAG = 'Y')
   MINUS
                       (SELECT column_value
                        FROM TABLE(CAST(p_FATIGUE_PARTY_LIST as JTF_NUMBER_TABLE))
                        );

Line: 676

   SELECT AMS_TCOP_CONTACTS_S.NEXTVAL
   FROM DUAL;

Line: 1028

                          'Before Calling AMS_ListGeneration_PKG.UPDATE_FOR_TRAFFIC_COP'
                         );

Line: 1031

      AMS_ListGeneration_PKG.UPDATE_FOR_TRAFFIC_COP
                          ( p_list_header_id => l_list_header_id,
                            p_list_entry_id => l_fatigue_entry_list);

Line: 1038

                          'After Calling AMS_ListGeneration_PKG.UPDATE_FOR_TRAFFIC_COP'
                         );

Line: 1070

                          'Before Calling BULK Insert into AMS_TCOP_FATIGUE_BY'
                         );

Line: 1086

         INSERT INTO
         AMS_TCOP_FATIGUED_BY
         (fatigued_by_id,
          list_entry_id,
          schedule_id,
          creation_date,
          created_by,
          last_update_date,
          last_updated_by,
          last_update_login
         )
         VALUES
         (--l_fatigue_by_id_list(i),
          AMS_TCOP_FATIGUED_BY_S.NEXTVAL,
	  G_Fatigue_By_Entry_List(i),
          G_Fatigue_By_Schedule_List(i),
          sysdate,
          FND_GLOBAL.USER_ID,
          sysdate,
          FND_GLOBAL.USER_ID,
          FND_GLOBAL.USER_ID
         );

Line: 1113

                          'After Calling BULK Insert into AMS_TCOP_FATIGUED_BY'
                         );

Line: 1118

                          'UPDATE_CONTACT_SUMMARY',
                          'Before Calling AMS_TCOP_SUMMARIZATION_PKG.UPDATE_CONTACT_COUNT'
                         );

Line: 1161

                          'Before Calling BULK Insert into AMS_TCOP_CONTACTS'
                         );

Line: 1166

         INSERT INTO
         AMS_TCOP_CONTACTS
         (contact_id,
          party_id,
          schedule_id,
          media_id,
          contact_date,
          creation_date,
          created_by,
          last_update_date,
          last_updated_by,
          last_update_login
         )
         VALUES
         (l_contact_id_list(i),
          l_contacted_party_list(i),
          p_schedule_id,
          l_activity_id,
          sysdate,
          sysdate,
          FND_GLOBAL.USER_ID,
          sysdate,
          FND_GLOBAL.USER_ID,
          FND_GLOBAL.USER_ID
         );

Line: 1195

                          'After Calling BULK Insert into AMS_TCOP_CONTACTS'
                         );

Line: 1200

                          'UPDATE_CONTACT_SUMMARY',
                          'Before Calling AMS_TCOP_SUMMARIZATION_PKG.UPDATE_CONTACT_COUNT'
                         );

Line: 1204

      AMS_TCOP_SUMMARIZATION_PKG.UPDATE_CONTACT_COUNT(
                                                      l_contacted_party_list,
                                                      p_schedule_id,
                                                      l_activity_id,
                                                      l_global_rule_id,
                                                      l_channel_rule_id
                                                     );

Line: 1214

                          'UPDATE_CONTACT_SUMMARY',
                          'After Calling AMS_TCOP_SUMMARIZATION_PKG.UPDATE_CONTACT_COUNT'
                         );

Line: 1221

   AMS_TCOP_SCHEDULER_PKG.UPDATE_STATUS(p_schedule_id,'COMPLETED');