APPS.IEC_ADMIN_PVT SQL Statements

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

Line: 88

PROCEDURE Update_Camp_Schedule(



    p_campaign_schedule_id   IN  NUMBER,

 -- dialing_method used for CTI disabled

    p_dialing_method         IN  VARCHAR2,

    p_calendar_id            IN  NUMBER,

    p_abandon_limit          IN  NUMBER,



-- predictive timeout

    p_predictive_timeout     IN  NUMBER,

    p_user_status_id         IN  NUMBER,



    p_user_id                IN  NUMBER,



    x_msg_data               OUT NOCOPY VARCHAR2,

    x_return_value          OUT NOCOPY NUMBER



    )

IS



   L_API_NAME                  CONSTANT VARCHAR2(30) := 'Update_Camp_Schedule';

Line: 156

   fnd_msg_pub.delete_msg();

Line: 162

   SAVEPOINT UPDATE_camp_schedule;

Line: 230

   l_camp_schedule_rec.last_update_date := sysdate;

Line: 232

   l_camp_schedule_rec.last_updated_by := p_user_id;

Line: 238

   SELECT

    OBJECT_VERSION_NUMBER, SOURCE_CODE into

    l_camp_schedule_rec.object_version_number, l_camp_schedule_rec.source_code

    from AMS_CAMPAIGN_SCHEDULES_B

    WHERE SCHEDULE_ID = p_campaign_schedule_id;

Line: 250

   AMS_Camp_Schedule_PUB.Update_Camp_Schedule( L_API_VERSION_NUMBER,

                                               FND_API.G_FALSE,

                                               FND_API.G_TRUE,

                                               FND_API.G_VALID_LEVEL_FULL,

                                               x_return_status,

                                               l_msg_count,

                                               l_msg_data,

                                               l_camp_schedule_rec,

                                               l_object_version_number

                                             );

Line: 274

    UPDATE AMS_CAMPAIGN_SCHEDULES_B

    SET

      ACTIVITY_ATTRIBUTE6 = 'Y'

    WHERE

      SCHEDULE_ID = p_campaign_schedule_id;

Line: 322

END Update_Camp_Schedule;

Line: 364

PROCEDURE Update_List_DialingMethod(



    p_campaign_schedule_id   IN  NUMBER,

 -- dialing_method used for CTI disabled

    p_dialing_method         IN  VARCHAR2,



    p_user_id                IN  NUMBER,



    x_msg_data               OUT NOCOPY VARCHAR2,

    x_return_value          OUT NOCOPY NUMBER



    )

IS



   L_API_NAME                  CONSTANT VARCHAR2(30) := 'Update_List_DialingMethod';

Line: 420

      SELECT LIST_HEADER_ID FROM AMS_ACT_LISTS WHERE LIST_USED_BY_ID = campaign_schedule_id;

Line: 428

   fnd_msg_pub.delete_msg();

Line: 434

   SAVEPOINT UPDATE_list_header;

Line: 472

       l_list_header_rec.last_update_date := sysdate;

Line: 474

       l_list_header_rec.last_updated_by := p_user_id;

Line: 480

       SELECT

       OBJECT_VERSION_NUMBER into l_list_header_rec.object_version_number

       FROM AMS_LIST_HEADERS_ALL

       WHERE LIST_HEADER_ID = l_list_header_rec.list_header_id;

Line: 490

       AMS_ListHeader_PUB.Update_ListHeader( l_api_version_number,

                                             FND_API.G_FALSE,

                                             FND_API.G_TRUE,

                                             FND_API.G_VALID_LEVEL_FULL,

                                             x_return_status,

                                             l_msg_count,

                                             l_msg_data,

                                             l_list_header_rec

                                           );

Line: 552

END Update_List_DialingMethod;

Line: 586

PROCEDURE Update_List_Header(

   p_list_header_id             IN   NUMBER,

   p_dialing_method             IN   VARCHAR2,

   p_list_priority              IN   NUMBER,

   p_recycling_alg_id           IN   NUMBER,

   p_release_control_alg_id     IN   NUMBER,

   p_calendar_id                IN   NUMBER,

   p_release_strategy           IN   VARCHAR2,

   p_quantum                    IN   NUMBER,

   p_quota                      IN   NUMBER       := null,

   p_quota_reset                IN   NUMBER       := null,



   p_user_id                    IN   NUMBER,



   x_msg_data                   OUT  NOCOPY VARCHAR2,

   x_return_value               OUT  NOCOPY NUMBER

    )



IS



   L_API_NAME                  CONSTANT VARCHAR2(30) := 'Update_List_Header';

Line: 654

   fnd_msg_pub.delete_msg();

Line: 662

   SAVEPOINT UPDATE_list_header;

Line: 708

   l_list_header_rec.last_update_date := sysdate;

Line: 710

   l_list_header_rec.last_updated_by := p_user_id;

Line: 714

   AMS_ListHeader_PVT.Update_ListHeader( l_api_version_number,

                                         FND_API.G_FALSE,

                                         FND_API.G_TRUE,

                                         FND_API.G_VALID_LEVEL_FULL,

                                         x_return_status,

                                         l_msg_count,

                                         l_msg_data,

                                         l_list_header_rec

                                       );

Line: 736

    UPDATE AMS_LIST_HEADERS_ALL

    SET

      RECYCLING_ALG_ID = p_recycling_alg_id,

      QUOTA = p_quota,

      QUOTA_RESET = p_quota_reset,

      CALL_CENTER_READY_FLAG = 'Y'

    WHERE

      LIST_HEADER_ID = p_list_header_id;

Line: 792

END Update_List_Header;

Line: 822

PROCEDURE Delete_List_Subset(

    p_list_subset_id         IN  NUMBER,

    p_user_id                IN  NUMBER,

    x_msg_data               OUT NOCOPY VARCHAR2,

    x_return_value           OUT NOCOPY NUMBER

    )

IS



   L_API_NAME                  CONSTANT VARCHAR2(30) := 'Delete_List_Subset';

Line: 850

   SAVEPOINT Delete_List_Subset;

Line: 862

   UPDATE IEC_G_LIST_SUBSETS SET STATUS_CODE='DELETED'

   WHERE LIST_SUBSET_ID = p_list_subset_id;

Line: 884

END Delete_List_Subset;

Line: 1026

	l_header_stmt := ' SELECT ' ||

					 ' campaigns.CAMPAIGN_NAME, schedules.SCHEDULE_NAME, lists_vl.LIST_NAME ' ||

					 ' FROM ' ||

					 ' AMS_LIST_HEADERS_VL lists_vl, AMS_ACT_LISTS act_lists, AMS_CAMPAIGN_SCHEDULES_VL schedules,	AMS_CAMPAIGNS_VL campaigns ' ||

					 ' WHERE ' ||

					 ' lists_vl.LIST_HEADER_ID = :1 ' ||

					 ' AND lists_vl.LIST_HEADER_ID = act_lists.LIST_HEADER_ID ' ||

					 ' AND act_lists.LIST_USED_BY_ID = schedules.SCHEDULE_ID ' ||

					 ' AND schedules.CAMPAIGN_ID = campaigns.CAMPAIGN_ID ';

Line: 1140

	l_entries_stmt_temp := 	' SELECT ' ||

					  		' PERSON_FIRST_NAME, PERSON_LAST_NAME, ' 			||

					  		' LIST.LIST_ENTRY_ID LIST_ENTRY_ID, PARTY_ID, ' 	||

							  l_cp_count_stmt 			|| ' NUM_CPS, ' 		||

							  l_cp_valid_count_stmt 	|| ' NUM_VALID_CPS, ' 	||

							  l_cp_invalid_count_stmt 	|| ' NUM_INVALID_CPS ' 	||

							' FROM ' 											||

							  l_source_type_view  		|| ' LIST, ' 			||

							' IEC_O_VALIDATION_REPORT_DETS VAL ' 				||

							' WHERE ' 											||

							' LIST.LIST_HEADER_ID = :1 ' 						|| -- P_LIST_HEADER_ID

							' AND LIST.ENABLED_FLAG = ''Y'' ' 					||

							' AND LIST.LIST_HEADER_ID = VAL.LIST_HEADER_ID(+) ' ||

							' AND LIST.LIST_ENTRY_ID  = VAL.LIST_ENTRY_ID(+) ' 	||

							  l_search_clause ||

							' ORDER BY ' || P_ORDER_BY || ' ' || P_ORDER;

Line: 1174

	l_entries_stmt := 	' SELECT ' ||

				  		' PERSON_FIRST_NAME, PERSON_LAST_NAME, ' 	||

				  		' LIST_ENTRY_ID, PARTY_ID, ' 				||

				  		' NUM_CPS, NUM_VALID_CPS, NUM_INVALID_CPS ' ||

						' FROM ' ||

						' ( SELECT PERSON_FIRST_NAME, PERSON_LAST_NAME, LIST_ENTRY_ID, PARTY_ID, NUM_CPS, NUM_VALID_CPS, NUM_INVALID_CPS ' ||

						'   FROM ( ' || l_entries_stmt_temp || ' ) AA WHERE ROWNUM <= :2 ) A ' ||  -- l_row_count

						' WHERE ' ||

						' A.LIST_ENTRY_ID NOT IN ' ||

						' ( SELECT LIST_ENTRY_ID FROM ( ' || l_entries_stmt_temp || ' ) BB WHERE ROWNUM <= :4 ) ' || -- P_NEXT_ROW

						' ORDER BY ' || P_ORDER_BY || ' ' || P_ORDER;

Line: 1214

		' SELECT COUNT(LIST_ENTRY_ID)

		  FROM ' || '( ' || l_entries_stmt_temp || ' ) A '

	INTO X_TOTAL_ENTRIES

	USING P_LIST_HEADER_ID;

Line: 1338

	l_header_stmt := ' SELECT ' ||

					 ' campaigns.CAMPAIGN_NAME, schedules.SCHEDULE_NAME, lists_vl.LIST_NAME, ' ||

					 ' list_entries.PERSON_FIRST_NAME, list_entries.PERSON_LAST_NAME, list_entries.PARTY_ID ' ||

					 ' FROM ' ||

					 ' AMS_LIST_HEADERS_VL lists_vl, AMS_ACT_LISTS act_lists, AMS_CAMPAIGN_SCHEDULES_VL schedules, AMS_CAMPAIGNS_VL campaigns, ' ||

 				       l_source_type_view  || ' list_entries ' ||

					 ' WHERE ' ||

					 ' list_entries.LIST_ENTRY_ID = :1 ' ||

					 ' AND list_entries.LIST_HEADER_ID = :2 ' ||

					 ' AND list_entries.LIST_HEADER_ID = lists_vl.LIST_HEADER_ID ' ||

					 ' AND lists_vl.LIST_HEADER_ID = act_lists.LIST_HEADER_ID ' ||

					 ' AND act_lists.LIST_USED_BY_ID = schedules.SCHEDULE_ID ' ||

					 ' AND schedules.CAMPAIGN_ID = campaigns.CAMPAIGN_ID ';

Line: 1368

	l_contact_point_stmt := ' SELECT ' ||

	 						' CONTACT_POINT_ID_S1, PHONE_COUNTRY_CODE_S1, PHONE_AREA_CODE_S1, PHONE_NUMBER_S1, RAW_PHONE_NUMBER_S1, TIME_ZONE_S1, Get_Translated_DNU_Reason(DO_NOT_USE_REASON_S1), REASON_CODE_S1, ' ||

							' CONTACT_POINT_ID_S2, PHONE_COUNTRY_CODE_S2, PHONE_AREA_CODE_S2, PHONE_NUMBER_S2, RAW_PHONE_NUMBER_S2, TIME_ZONE_S2, Get_Translated_DNU_Reason(DO_NOT_USE_REASON_S2), REASON_CODE_S2, ' ||

	 						' CONTACT_POINT_ID_S3, PHONE_COUNTRY_CODE_S3, PHONE_AREA_CODE_S3, PHONE_NUMBER_S3, RAW_PHONE_NUMBER_S3, TIME_ZONE_S3, Get_Translated_DNU_Reason(DO_NOT_USE_REASON_S3), REASON_CODE_S3, ' ||

	 						' CONTACT_POINT_ID_S4, PHONE_COUNTRY_CODE_S4, PHONE_AREA_CODE_S4, PHONE_NUMBER_S4, RAW_PHONE_NUMBER_S4, TIME_ZONE_S4, Get_Translated_DNU_Reason(DO_NOT_USE_REASON_S4), REASON_CODE_S4, ' ||

	 						' CONTACT_POINT_ID_S5, PHONE_COUNTRY_CODE_S5, PHONE_AREA_CODE_S5, PHONE_NUMBER_S5, RAW_PHONE_NUMBER_S5, TIME_ZONE_S5, Get_Translated_DNU_Reason(DO_NOT_USE_REASON_S5), REASON_CODE_S5, ' ||

	 						' CONTACT_POINT_ID_S6, PHONE_COUNTRY_CODE_S6, PHONE_AREA_CODE_S6, PHONE_NUMBER_S6, RAW_PHONE_NUMBER_S6, TIME_ZONE_S6, Get_Translated_DNU_Reason(DO_NOT_USE_REASON_S6), REASON_CODE_S6  ' ||

	 						' FROM ' ||

	 				    	  l_source_type_view  || ' LIST, ' ||

	 				  		' IEC_O_VALIDATION_REPORT_DETS VAL ' ||

	 					  	' WHERE ' ||

	 				  		' LIST.LIST_HEADER_ID = :1 ' ||

	 						' AND LIST.LIST_HEADER_ID = VAL.LIST_HEADER_ID(+) ' ||

	 				  		' AND LIST.LIST_ENTRY_ID = :2 ' ||

	 						' AND LIST.LIST_ENTRY_ID = VAL.LIST_ENTRY_ID(+) ';

Line: 1402

	l_time_zone_stmt := ' SELECT ' ||

					 	' B.UPGRADE_TZ_ID, T.TIMEZONE_CODE ' ||

					 	' FROM ' ||

					 	' FND_TIMEZONES_B B, FND_TIMEZONES_TL T ' ||

					 	' WHERE ' ||

					 	' B.TIMEZONE_CODE = T.TIMEZONE_CODE ' ||

					 	' AND T.LANGUAGE = :1 ' || -- P_LANGUAGE

					 	' ORDER BY T.TIMEZONE_CODE ASC ';

Line: 1451

    p_updatedBy           IN NUMBER,
    p_updateDate          IN DATE,
    p_updateLogin         IN NUMBER,
    p_versionNumber       IN NUMBER
    )
AS
  l_day_copyto_id  NUMBER;

Line: 1459

  select CALLABLE_REGION_CODE, START_TIME, END_TIME, TYPE_CODE from iec_g_cal_callable_rgns
    where DAY_ID = p_day_id;

Line: 1462

  select day_id into l_day_copyto_id from iec_g_cal_days_b where CALENDAR_ID = p_calendar_id
  and DAY_CODE = 'DAY_OF_WEEK' and  PATTERN_CODE = p_copyto_code;

Line: 1465

  delete from iec_g_cal_callable_rgns where DAY_ID = l_day_copyto_id;

Line: 1467

    insert into iec_g_cal_callable_rgns(CALLABLE_REGION_ID, DAY_ID, CALLABLE_REGION_CODE,
              START_TIME, END_TIME, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
              OBJECT_VERSION_NUMBER, TYPE_CODE)
    values (IEC_G_CAL_CALLABLE_RGNS_S.NEXTVAL,l_day_copyto_id,v_copy.CALLABLE_REGION_CODE,
        v_copy.START_TIME, v_copy.END_TIME,p_createdBy, p_creationDate,p_updatedBy,p_updateDate,
        p_updateLogin, p_versionNumber,v_copy.TYPE_CODE);