The following lines contain the word 'select', 'insert', 'update' or 'delete':
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';
fnd_msg_pub.delete_msg();
SAVEPOINT UPDATE_camp_schedule;
l_camp_schedule_rec.last_update_date := sysdate;
l_camp_schedule_rec.last_updated_by := p_user_id;
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;
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
);
UPDATE AMS_CAMPAIGN_SCHEDULES_B
SET
ACTIVITY_ATTRIBUTE6 = 'Y'
WHERE
SCHEDULE_ID = p_campaign_schedule_id;
END Update_Camp_Schedule;
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';
SELECT LIST_HEADER_ID FROM AMS_ACT_LISTS WHERE LIST_USED_BY_ID = campaign_schedule_id;
fnd_msg_pub.delete_msg();
SAVEPOINT UPDATE_list_header;
l_list_header_rec.last_update_date := sysdate;
l_list_header_rec.last_updated_by := p_user_id;
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;
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
);
END Update_List_DialingMethod;
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';
fnd_msg_pub.delete_msg();
SAVEPOINT UPDATE_list_header;
l_list_header_rec.last_update_date := sysdate;
l_list_header_rec.last_updated_by := p_user_id;
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
);
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;
END Update_List_Header;
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';
SAVEPOINT Delete_List_Subset;
UPDATE IEC_G_LIST_SUBSETS SET STATUS_CODE='DELETED'
WHERE LIST_SUBSET_ID = p_list_subset_id;
END Delete_List_Subset;
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 ';
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;
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;
' SELECT COUNT(LIST_ENTRY_ID)
FROM ' || '( ' || l_entries_stmt_temp || ' ) A '
INTO X_TOTAL_ENTRIES
USING P_LIST_HEADER_ID;
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 ';
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(+) ';
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 ';
p_updatedBy IN NUMBER,
p_updateDate IN DATE,
p_updateLogin IN NUMBER,
p_versionNumber IN NUMBER
)
AS
l_day_copyto_id NUMBER;
select CALLABLE_REGION_CODE, START_TIME, END_TIME, TYPE_CODE from iec_g_cal_callable_rgns
where DAY_ID = p_day_id;
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;
delete from iec_g_cal_callable_rgns where DAY_ID = l_day_copyto_id;
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);