APPS.AMS_TCOP_SUMMARIZATION_PKG SQL Statements

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

Line: 68

PROCEDURE   DELETE_CONTACT_SUMMARIZATION(p_list_header_id   NUMBER,
                                         p_activity_id         NUMBER
                                        )
IS
BEGIN

   delete from ams_tcop_contact_sum_dtl
   where contact_summary_id in
   (select summary.contact_summary_id
    from   ams_tcop_contact_summary summary,
           ams_list_entries list_entry
    where list_entry.enabled_flag = 'Y'
    and   list_entry.list_header_id = p_list_header_id
    and   list_entry.party_id = summary.party_id );

Line: 83

   delete from ams_tcop_contact_summary
   where party_id in
   (select party_id
    from   ams_list_entries
    where enabled_flag = 'Y'
    and   list_header_id = p_list_header_id);

Line: 91

    delete from ams_tcop_channel_sum_dtl
    where channel_summary_id in
    (select summary.channel_summary_id
     from   ams_tcop_channel_summary summary,
            ams_list_entries list_entry
     where  list_entry.party_id = summary.party_id
     and    list_entry.list_header_id = p_list_header_id
     and    summary.media_id = p_activity_id
     and    list_entry.enabled_flag = 'Y');

Line: 101

    delete from ams_tcop_channel_summary
    where party_id in
    (select summary.PARTY_ID
     from   ams_tcop_channel_summary summary,
            ams_list_entries list_entry
     where  list_entry.party_id = summary.party_id
     and    list_entry.list_header_id = p_list_header_id
     and    summary.media_id = p_activity_id
     and    list_entry.enabled_flag = 'Y');

Line: 112

END DELETE_CONTACT_SUMMARIZATION;

Line: 132

   SELECT RULE.RULE_ID
          ,PERIOD.NO_OF_DAYS
   FROM   AMS_TCOP_FR_RULES_SETUP RULE
         ,AMS_TCOP_FR_PERIODS_B period
   WHERE  RULE.RULE_TYPE='GLOBAL'
   AND    RULE.ENABLED_FLAG = 'Y'
   AND    RULE.PERIOD_ID = PERIOD.PERIOD_ID;

Line: 143

   SELECT rule.RULE_ID,period.no_of_days
   FROM   AMS_TCOP_FR_RULES_SETUP rule
          ,AMS_TCOP_FR_PERIODS_B period
   WHERE  rule.RULE_TYPE = 'CHANNEL_BASED'
   AND    rule.CHANNEL_ID = p_activity_id
   AND    rule.ENABLED_FLAG = 'Y'
   AND    rule.period_id = period.period_id;

Line: 157

   SELECT contact.party_id,
          contact.schedule_id
   FROM AMS_TCOP_CONTACTS contact, AMS_LIST_ENTRIES list_entry
   WHERE contact.MEDIA_ID = p_activity_id
   AND (contact_date between (sysdate - p_no_of_days) and sysdate)
   AND contact.PARTY_ID = list_entry.PARTY_ID
   AND list_entry.list_header_id = p_list_header_id
   AND list_entry.enabled_flag = 'Y'
   order by contact.party_id;

Line: 171

   SELECT contact.party_id,
          contact.schedule_id
   FROM AMS_TCOP_CONTACTS contact, AMS_LIST_ENTRIES list_entry
   WHERE (contact_date between (sysdate - p_no_of_days) and sysdate)
   AND contact.PARTY_ID = list_entry.PARTY_ID
   AND list_entry.list_header_id = p_list_header_id
   AND list_entry.enabled_flag = 'Y'
   order by contact.party_id;

Line: 183

   SELECT AMS_TCOP_CONTACT_SUM_S.nextval
   FROM DUAL;

Line: 193

   SELECT AMS_TCOP_CHNL_SUM_S.nextval
   FROM DUAL;

Line: 198

   SELECT AMS_TCOP_CHNL_SUM_DTL_S.nextval
   FROM DUAL;

Line: 257

                       'Before Calling Delete_Contact_Summarization',
                       'Before Calling Delete_Contact_Summarization'
                      );

Line: 261

   DELETE_CONTACT_SUMMARIZATION(p_list_header_id,p_activity_id);

Line: 265

                       'After calling DELETE_CONTACT_SUMMARIZATION ',
                       'All the entries deleted from summarization tables'
                      );

Line: 361

         INSERT INTO
         AMS_TCOP_CONTACT_SUMMARY
         (
            CONTACT_SUMMARY_ID,
            RULE_ID,
            PARTY_ID,
            TOTAL_CONTACTS,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            LAST_UPDATE_LOGIN
         )
         VALUES
         (
            l_contact_sum_id_list(i),
            l_rule_id,
            l_final_party_id_list(i),
            l_contact_count_list(i),
            sysdate,
            FND_GLOBAL.USER_ID,
            sysdate,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.USER_ID
         );

Line: 399

         INSERT INTO
         AMS_TCOP_CONTACT_SUM_DTL
         (
            SUMMARY_DTL_ID,
            CONTACT_SUMMARY_ID,
            SCHEDULE_ID,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            LAST_UPDATE_LOGIN
         )
         VALUES
         (
            --l_contact_sum_id_dtl_list(i),
	    AMS_TCOP_CONTACT_SUM_DTL_S.nextval,
            l_contact_sum_id_list_for_dtl(i),
            l_schedule_id_list(i),
            sysdate,
            FND_GLOBAL.USER_ID,
            sysdate,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.USER_ID
         );

Line: 435

   l_temp_party_id_list.DELETE;

Line: 436

   l_contact_count_list.DELETE;

Line: 437

   l_final_party_id_list.DELETE;

Line: 438

   l_schedule_id_list.DELETE;

Line: 530

            INSERT INTO
            AMS_TCOP_CHANNEL_SUMMARY
            (
               CHANNEL_SUMMARY_ID,
               RULE_ID,
               PARTY_ID,
               MEDIA_ID,
               TOTAL_CONTACTS,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN
            )
            VALUES
            (
               l_chnl_sum_id_list(i),
               l_rule_id,
               l_final_party_id_list(i),
               p_activity_id,
               l_contact_count_list(i),
               sysdate,
               FND_GLOBAL.USER_ID,
               sysdate,
               FND_GLOBAL.USER_ID,
               FND_GLOBAL.USER_ID
            );

Line: 560

            INSERT INTO
            AMS_TCOP_CHANNEL_SUM_DTL
            (
               CHANNEL_SUM_DTL_ID,
               CHANNEL_SUMMARY_ID,
               SCHEDULE_ID,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN
            )
            VALUES
            (
               l_chnl_sum_dtl_id_list(i),
               l_chnl_sum_id_list_for_dtl(i),
               l_schedule_id_list(i),
               sysdate,
               FND_GLOBAL.USER_ID,
               sysdate,
               FND_GLOBAL.USER_ID,
               FND_GLOBAL.USER_ID
            );

Line: 609

PROCEDURE      UPDATE_CONTACT_COUNT(p_party_id_list   JTF_NUMBER_TABLE
                                    ,p_schedule_id    NUMBER
                                    ,p_activity_id    NUMBER
                                    ,p_global_rule_id NUMBER
                                    ,p_channel_rule_id   NUMBER
                                   )

IS
   -- Verify if new entries need to be created in AMS_TCOP_CONTACT_SUMMARY
   -- and in AMS_TCOP_CONTACT_SUM_DTL
   CURSOR C_GET_EXISTING_PARTY (p_party_id_list   JTF_NUMBER_TABLE)
   IS
   SELECT summary.PARTY_ID,summary.contact_summary_id
   FROM   AMS_TCOP_CONTACT_SUMMARY summary,
          (SELECT column_value party_id
           FROM TABLE(CAST(p_party_id_List as JTF_NUMBER_TABLE))
          ) party_list
   WHERE  summary.party_id=party_list.party_id;

Line: 632

   SELECT orig_party_list.PARTY_ID
   FROM   (SELECT column_value party_id
           FROM TABLE(CAST(p_original_party_id_list as JTF_NUMBER_TABLE))
          ) orig_party_list
   WHERE orig_party_list.PARTY_ID NOT IN
          (SELECT column_value
           FROM TABLE(CAST(p_existing_party_id_list as JTF_NUMBER_TABLE))
           );

Line: 643

   SELECT AMS_TCOP_CONTACT_SUM_DTL_S.NEXTVAL
   FROM DUAL;

Line: 648

   SELECT AMS_TCOP_CONTACT_SUM_S.NEXTVAL
   FROM DUAL;

Line: 653

   SELECT AMS_TCOP_CHNL_SUM_DTL_S.NEXTVAL
   FROM DUAL;

Line: 658

   SELECT AMS_TCOP_CHNL_SUM_S.NEXTVAL
   FROM DUAL;

Line: 666

   SELECT summary.PARTY_ID,summary.channel_summary_id
   FROM   AMS_TCOP_CHANNEL_SUMMARY summary,
          (SELECT column_value party_id
           FROM TABLE(CAST(p_party_id_List as JTF_NUMBER_TABLE))
          ) party_list
   WHERE  summary.party_id=party_list.party_id
   AND    summary.media_id=p_activity_id;

Line: 693

   PROCEDURE_NAME   CONSTANT        VARCHAR2(30) := 'UPDATE_CONTACT_COUNT';

Line: 782

                                'BEFORE_BULK_UPDATE_CONTACT_SUMMARY',
                                'Bulk update AMS_TCOP_CONTACT_SUMMARY to increment the contact count'
                               );

Line: 788

            UPDATE AMS_TCOP_CONTACT_SUMMARY
            SET TOTAL_CONTACTS = TOTAL_CONTACTS + 1
            WHERE CONTACT_SUMMARY_ID = l_contact_summary_id_list(i);

Line: 794

                                'AFTER_BULK_UPDATE_CONTACT_SUMMARY',
                                'Bulk update AMS_TCOP_CONTACT_SUMMARY to increment the contact count completed successfully!'
                               );

Line: 800

                                'BEFORE_BULK_INSERT_CONTACT_SUM_DTL_LABEL1',
                                'Bulk Insert AMS_TCOP_CONTACT_SUM_DTL to have the schedule information '
                               );

Line: 806

            INSERT INTO
            AMS_TCOP_CONTACT_SUM_DTL
            (
               SUMMARY_DTL_ID,
               CONTACT_SUMMARY_ID,
               SCHEDULE_ID,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN
            )
            VALUES
            (
               l_sum_detail_seq_id_list(i),
               l_contact_summary_id_list(i),
               p_schedule_id,
               sysdate,
               FND_GLOBAL.USER_ID,
               sysdate,
               FND_GLOBAL.USER_ID,
               FND_GLOBAL.USER_ID
            );

Line: 832

                                'AFTER_BULK_INSERT_CONTACT_SUM_DTL_LABEL1',
                                'Bulk Insert into AMS_TCOP_CONTACT_SUM_DTL completed successfully'
                               );

Line: 836

            l_sum_detail_seq_id_list.delete;

Line: 940

                                'BEFORE_BULK_INSERT_INTO_CONTACT_SUMMARY',
                                'Bulk Insert the new party ids into the AMS_TCOP_CONTACT_SUMMARY'
                               );

Line: 946

            INSERT INTO
            AMS_TCOP_CONTACT_SUMMARY
            (
               CONTACT_SUMMARY_ID,
               RULE_ID,
               PARTY_ID,
               TOTAL_CONTACTS,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN
            )
            VALUES
            (
               l_contact_sum_seq_id_list(i),
               p_global_rule_id,
               l_new_party_id_list(i),
               1,
               sysdate,
               FND_GLOBAL.USER_ID,
               sysdate,
               FND_GLOBAL.USER_ID,
               FND_GLOBAL.USER_ID
            );

Line: 974

                                'AFTER_BULK_INSERT_INTO_CONTACT_SUMMARY',
                                'Bulk Insertion of the new party ids into the AMS_TCOP_CONTACT_SUMMARY completed successfully'
                               );

Line: 980

                                'AFTER_BULK_INSERT_INTO_CONTACT_SUMMARY',
                                'Bulk Insertion of the new party ids into the AMS_TCOP_CONTACT_SUMMARY completed successfully'
                               );

Line: 986

                                'BEFORE_BULK_INSERT_CONTACT_SUM_DTL_LABEL2',
                                'Bulk Insert AMS_TCOP_CONTACT_SUM_DTL to have the schedule information '
                               );

Line: 992

            INSERT INTO
            AMS_TCOP_CONTACT_SUM_DTL
            (
               SUMMARY_DTL_ID,
               CONTACT_SUMMARY_ID,
               SCHEDULE_ID,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN
            )
            VALUES
            (
               l_sum_detail_seq_id_list(i),
               l_contact_sum_seq_id_list(i),
               p_schedule_id,
               sysdate,
               FND_GLOBAL.USER_ID,
               sysdate,
               FND_GLOBAL.USER_ID,
               FND_GLOBAL.USER_ID
            );

Line: 1018

                                'AFTER_BULK_INSERT_CONTACT_SUM_DTL_LABEL2',
                                'After Bulk Insert AMS_TCOP_CONTACT_SUM_DTL to have the schedule information '
                               );

Line: 1028

            l_existing_party_id_list.delete;

Line: 1029

            l_new_party_id_list.delete;

Line: 1110

                                   'BEFORE_BULK_UPDATE_CHANNEL_SUMMARY',
                                   'Bulk update AMS_TCOP_CHANNEL_SUMMARY to increment the contact count'
                                  );

Line: 1116

               UPDATE AMS_TCOP_CHANNEL_SUMMARY
               SET TOTAL_CONTACTS = TOTAL_CONTACTS + 1
               WHERE CHANNEL_SUMMARY_ID = l_channel_summary_id_list(i)
               AND   MEDIA_ID = p_activity_id;

Line: 1123

                                   'AFTER_BULK_UPDATE_CHANNEL_SUMMARY',
                                   'After Bulk update AMS_TCOP_CHANNEL_SUMMARY to increment the contact count'
                                  );

Line: 1129

                                   'BEFORE_BULK_INSERT_CHANNEL_SUM_DTL_L1',
                                   'Before Bulk Insert of AMS_TCOP_CHANNEL_SUM_DTL to populate the schedule information'
                                  );

Line: 1135

               INSERT INTO
               AMS_TCOP_CHANNEL_SUM_DTL
               (
                  CHANNEL_SUM_DTL_ID,
                  CHANNEL_SUMMARY_ID,
                  SCHEDULE_ID,
                  CREATION_DATE,
                  CREATED_BY,
                  LAST_UPDATE_DATE,
                  LAST_UPDATED_BY,
                  LAST_UPDATE_LOGIN
               )
               VALUES
               (
                  l_chnl_sum_detail_seq_id_list(i),
                  l_channel_summary_id_list(i),
                  p_schedule_id,
                  sysdate,
                  FND_GLOBAL.USER_ID,
                  sysdate,
                  FND_GLOBAL.USER_ID,
                  FND_GLOBAL.USER_ID
               );

Line: 1161

                                   'AFTER_BULK_INSERT_CHANNEL_SUM_DTL_L2',
                                   'After Bulk Insert of AMS_TCOP_CHANNEL_SUM_DTL to populate the schedule information'
                                  );

Line: 1172

               l_sum_detail_seq_id_list.delete;

Line: 1274

                                'BEFORE_BULK_INSERT_CHANNEL_SUM',
                                'Before Bulk Insert of AMS_TCOP_CHANNEL_SUMMMARY'
                               );

Line: 1280

            INSERT INTO
            AMS_TCOP_CHANNEL_SUMMARY
            (
               CHANNEL_SUMMARY_ID,
               RULE_ID,
               PARTY_ID,
               MEDIA_ID,
               TOTAL_CONTACTS,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN
            )
            VALUES
            (
               l_chnl_sum_seq_id_list(i),
               p_channel_rule_id,
               l_new_party_id_list(i),
               p_activity_id,
               1,
               sysdate,
               FND_GLOBAL.USER_ID,
               sysdate,
               FND_GLOBAL.USER_ID,
               FND_GLOBAL.USER_ID
            );

Line: 1310

                                'AFTER_BULK_INSERT_CHANNEL_SUM',
                                'After Bulk Insert of AMS_TCOP_CHANNEL_SUMMMARY'
                               );

Line: 1316

                                'BEFORE_BULK_INSERT_CHANNEL_SUM_DTL_L2',
                                'Before Bulk Insert of AMS_TCOP_CHANNEL_SUM_DTL to populate the schedule information'
                               );

Line: 1322

               INSERT INTO
               AMS_TCOP_CHANNEL_SUM_DTL
               (
                  CHANNEL_SUM_DTL_ID,
                  CHANNEL_SUMMARY_ID,
                  SCHEDULE_ID,
                  CREATION_DATE,
                  CREATED_BY,
                  LAST_UPDATE_DATE,
                  LAST_UPDATED_BY,
                  LAST_UPDATE_LOGIN
               )
               VALUES
               (
                  l_sum_detail_seq_id_list(i),
                  l_chnl_sum_seq_id_list(i),
                  p_schedule_id,
                  sysdate,
                  FND_GLOBAL.USER_ID,
                  sysdate,
                  FND_GLOBAL.USER_ID,
                  FND_GLOBAL.USER_ID
               );

Line: 1348

                                   'BEFORE_BULK_INSERT_CHANNEL_SUM_DTL_L2',
                                   'Before Bulk Insert of AMS_TCOP_CHANNEL_SUM_DTL to populate the schedule information'
                                  );

Line: 1362

END UPDATE_CONTACT_COUNT;