[Home] [Help]
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
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
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;
341: --FETCH C_GET_CONTACT_SUM_DTL_NEXT_SEQ
341: --FETCH C_GET_CONTACT_SUM_DTL_NEXT_SEQ
342: --INTO l_sequence_id;
343: --CLOSE C_GET_CONTACT_SUM_DTL_NEXT_SEQ;
344:
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
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
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: (
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,
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,
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:
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,
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
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:
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',
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
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: (
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,
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:
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
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: (
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,
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,