[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = p_user_status_id ;
select 'Y'
from ams_list_src_types
where list_source_type in ('TARGET','ANALYTICS')
and source_type_code = cur_list_source_type
and master_source_type_flag = 'Y'
and enabled_flag = 'Y' ;
IF p_listheader_rec.row_selection_type <> FND_API.g_miss_char
AND p_listheader_rec.row_selection_type IS NOT NULL THEN
IF AMS_Utility_PVT.check_lookup_exists(
p_lookup_type => 'AMS_LIST_ROW_SELECT_TYPE',
p_lookup_code => p_listheader_rec.row_selection_type
) = FND_API.g_false THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_ROW_SELECT_TYPE');
select FND_API.g_false
from ams_list_headers_vl
where list_name = p_listheader_rec.list_name
and view_application_id not in (522,691);
cursor c_check_update_mode is
select FND_API.g_false
from ams_list_headers_vl
where list_name = p_listheader_rec.list_name
and list_header_id <> p_listheader_rec.list_header_id
and view_application_id not in (522,691);
cursor c_check_update_mode_02 is
select FND_API.g_false
from ams_list_headers_vl
where list_name = p_listheader_rec.list_name
and list_header_id <> p_listheader_rec.list_header_id
and nvl(purge_flag,'N') = 'N'
and view_application_id in (522,691)
and owner_user_id = p_listheader_rec.owner_user_id ;
select FND_API.g_false
from ams_list_headers_vl
where list_name = p_listheader_rec.list_name
and nvl(purge_flag,'N') = 'N'
and view_application_id in (522,691)
and owner_user_id = p_listheader_rec.owner_user_id ;
open c_check_update_mode;
fetch c_check_update_mode into l_valid_flag ;
close c_check_update_mode;
open c_check_update_mode_02 ;
fetch c_check_update_mode_02 into l_valid_flag ;
close c_check_update_mode_02 ;
IF (p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION IS NOT NULL) AND
(p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION <> FND_API.G_MISS_NUM)
THEN
IF ((p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION < 0) OR
(p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION >= 100) )THEN
IF(FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
FND_MESSAGE.set_name('AMS', 'AMS_LIST_RANDOM_PCT');
FND_MESSAGE.Set_Token('RAND_VALUE',p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION);
IF (p_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION IS NOT NULL) AND
(p_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION <> FND_API.G_MISS_NUM)
THEN
IF ((p_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION < 0) OR
(p_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION > 100) )THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.set_name('AMS', 'AMS_LIST_CONTROL_PCT_INVALID');
SELECT COUNT(1)
FROM AMS_LIST_HEADERS_ALL
WHERE LIST_HEADER_ID = p_list_header_id;
SELECT ams_list_headers_all_s.nextval
FROM DUAL;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => x_return_status
);
SELECT ams_list_headers_all_s.NEXTVAL
FROM DUAL;
SELECT COUNT(*)
FROM ams_list_headers_all
WHERE list_header_id = p_list_header_id;
SELECT custom_setup_id
FROM ams_custom_setups_b
WHERE object_type = L_OBJECT_TYPE
and activity_type_code = c_list_type
--AND enabled_flag = 'Y'
;
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_type = 'AMS_LIST_STATUS'
AND system_status_code = 'DRAFT'
AND enabled_flag = 'Y'
AND default_flag = 'Y';
insert into AMS_LIST_HEADERS_ALL (
LIST_HEADER_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
OBJECT_VERSION_NUMBER ,
REQUEST_ID ,
PROGRAM_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_UPDATE_DATE ,
VIEW_APPLICATION_ID ,
LIST_USED_BY_ID ,
ARC_LIST_USED_BY ,
LIST_TYPE ,
STATUS_CODE ,
STATUS_DATE ,
GENERATION_TYPE ,
REPEAT_EXCLUDE_TYPE ,
ROW_SELECTION_TYPE ,
OWNER_USER_ID ,
ACCESS_LEVEL ,
ENABLE_LOG_FLAG ,
ENABLE_WORD_REPLACEMENT_FLAG ,
ENABLE_PARALLEL_DML_FLAG ,
DEDUPE_DURING_GENERATION_FLAG ,
GENERATE_CONTROL_GROUP_FLAG ,
LAST_GENERATION_SUCCESS_FLAG ,
FORECASTED_START_DATE ,
FORECASTED_END_DATE ,
ACTUAL_END_DATE ,
SENT_OUT_DATE ,
DEDUPE_START_DATE ,
LAST_DEDUPE_DATE ,
LAST_DEDUPED_BY_USER_ID ,
WORKFLOW_ITEM_KEY ,
NO_OF_ROWS_DUPLICATES ,
NO_OF_ROWS_MIN_REQUESTED ,
NO_OF_ROWS_MAX_REQUESTED ,
NO_OF_ROWS_IN_LIST ,
NO_OF_ROWS_IN_CTRL_GROUP ,
NO_OF_ROWS_ACTIVE ,
NO_OF_ROWS_INACTIVE ,
NO_OF_ROWS_MANUALLY_ENTERED ,
NO_OF_ROWS_DO_NOT_CALL ,
NO_OF_ROWS_DO_NOT_MAIL ,
NO_OF_ROWS_RANDOM ,
ORG_ID ,
MAIN_GEN_START_TIME ,
MAIN_GEN_END_TIME ,
MAIN_RANDOM_NTH_ROW_SELECTION ,
MAIN_RANDOM_PCT_ROW_SELECTION ,
CTRL_RANDOM_NTH_ROW_SELECTION ,
CTRL_RANDOM_PCT_ROW_SELECTION ,
REPEAT_SOURCE_LIST_HEADER_ID ,
RESULT_TEXT ,
KEYWORDS ,
DESCRIPTION ,
LIST_PRIORITY ,
ASSIGN_PERSON_ID ,
LIST_SOURCE ,
LIST_SOURCE_TYPE ,
LIST_ONLINE_FLAG ,
RANDOM_LIST_ID ,
ENABLED_FLAG ,
ASSIGNED_TO ,
QUERY_ID ,
OWNER_PERSON_ID ,
ARCHIVED_BY ,
ARCHIVED_DATE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
TIMEZONE_ID ,
USER_ENTERED_START_TIME ,
USER_STATUS_ID ,
QUANTUM ,
RELEASE_CONTROL_ALG_ID ,
DIALING_METHOD ,
CALLING_CALENDAR_ID ,
RELEASE_STRATEGY ,
CUSTOM_SETUP_ID ,
COUNTRY ,
CALLBACK_PRIORITY_FLAG ,
CALL_CENTER_READY_FLAG ,
PURGE_FLAG ,
QUOTA ,
QUOTA_RESET ,
RECYCLING_ALG_ID ,
PUBLIC_FLAG ,
LIST_CATEGORY ,
no_of_rows_prev_contacted,
APPLY_TRAFFIC_COP,
purpose_code,
CTRL_CONF_LEVEL,
CTRL_REQ_RESP_RATE ,
CTRL_LIMIT_OF_ERROR ,
STATUS_CODE_OLD,
CTRL_CONC_JOB_ID ,
CTRL_STATUS_CODE ,
CTRL_GEN_MODE ,
APPLY_SUPPRESSION_FLAG
) values (
decode(l_listheader_rec.LIST_HEADER_ID ,FND_API.g_miss_num,null,l_listheader_rec.LIST_HEADER_ID) ,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
1,
decode(l_listheader_rec.REQUEST_ID ,FND_API.g_miss_num,null,l_listheader_rec.REQUEST_ID) ,
decode(l_listheader_rec.PROGRAM_ID ,FND_API.g_miss_num,null,l_listheader_rec.PROGRAM_ID) ,
decode(l_listheader_rec.PROGRAM_APPLICATION_ID ,FND_API.g_miss_num,null,l_listheader_rec.PROGRAM_APPLICATION_ID) ,
decode(l_listheader_rec.PROGRAM_UPDATE_DATE ,FND_API.g_miss_date,null,l_listheader_rec.PROGRAM_UPDATE_DATE) ,
decode(l_listheader_rec.VIEW_APPLICATION_ID ,FND_API.g_miss_num,530,nvl(l_listheader_rec.VIEW_APPLICATION_ID,530)) ,
decode(l_listheader_rec.LIST_USED_BY_ID ,FND_API.g_miss_num,0,nvl(l_listheader_rec.LIST_USED_BY_ID,0)) ,
decode(l_listheader_rec.ARC_LIST_USED_BY ,FND_API.g_miss_char,'NONE',nvl(l_listheader_rec.ARC_LIST_USED_BY,'NONE')) ,
decode(l_listheader_rec.LIST_TYPE ,FND_API.g_miss_char,null,l_listheader_rec.LIST_TYPE) ,
decode(l_listheader_rec.STATUS_CODE ,FND_API.g_miss_char,'DRAFT',nvl(l_listheader_rec.STATUS_CODE,'DRAFT')) ,
decode(l_listheader_rec.STATUS_DATE ,FND_API.g_miss_date,sysdate,nvl(l_listheader_rec.STATUS_DATE,sysdate)) ,
decode(l_listheader_rec.GENERATION_TYPE ,FND_API.g_miss_char,'STANDARD',nvl(l_listheader_rec.GENERATION_TYPE,'STANDARD')) ,
decode(l_listheader_rec.REPEAT_EXCLUDE_TYPE ,FND_API.g_miss_char,null,l_listheader_rec.REPEAT_EXCLUDE_TYPE) ,
decode(l_listheader_rec.ROW_SELECTION_TYPE ,FND_API.g_miss_char,'STANDARD',nvl(l_listheader_rec.ROW_SELECTION_TYPE,'STANDARD')) ,
decode(l_listheader_rec.OWNER_USER_ID ,FND_API.g_miss_num,null,l_listheader_rec.OWNER_USER_ID) ,
decode(l_listheader_rec.ACCESS_LEVEL ,FND_API.g_miss_char,'USER',nvl(l_listheader_rec.ACCESS_LEVEL,'USER')) ,
decode(l_listheader_rec.ENABLE_LOG_FLAG ,FND_API.g_miss_char,'Y',nvl(l_listheader_rec.ENABLE_LOG_FLAG,'Y')) ,
decode(l_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.ENABLE_WORD_REPLACEMENT_FLAG,'N')) ,
decode(l_listheader_rec.ENABLE_PARALLEL_DML_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.ENABLE_PARALLEL_DML_FLAG,'N')) ,
decode(l_listheader_rec.DEDUPE_DURING_GENERATION_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.DEDUPE_DURING_GENERATION_FLAG,'N')) ,
decode(l_listheader_rec.GENERATE_CONTROL_GROUP_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.GENERATE_CONTROL_GROUP_FLAG,'N')) ,
decode(l_listheader_rec.LAST_GENERATION_SUCCESS_FLAG ,FND_API.g_miss_char,'N',nvl(l_listheader_rec.LAST_GENERATION_SUCCESS_FLAG,'N')) ,
decode(l_listheader_rec.FORECASTED_START_DATE ,FND_API.g_miss_date,sysdate,nvl(l_listheader_rec.FORECASTED_START_DATE,sysdate)) ,
decode(l_listheader_rec.FORECASTED_END_DATE ,FND_API.g_miss_date,null,l_listheader_rec.FORECASTED_END_DATE) ,
decode(l_listheader_rec.ACTUAL_END_DATE ,FND_API.g_miss_date,null,l_listheader_rec.ACTUAL_END_DATE) ,
decode(l_listheader_rec.SENT_OUT_DATE ,FND_API.g_miss_date,null,l_listheader_rec.SENT_OUT_DATE) ,
decode(l_listheader_rec.DEDUPE_START_DATE ,FND_API.g_miss_date,null,l_listheader_rec.DEDUPE_START_DATE) ,
decode(l_listheader_rec.LAST_DEDUPE_DATE ,FND_API.g_miss_date,null,l_listheader_rec.LAST_DEDUPE_DATE) ,
decode(l_listheader_rec.LAST_DEDUPED_BY_USER_ID ,FND_API.g_miss_num,null,l_listheader_rec.LAST_DEDUPED_BY_USER_ID) ,
decode(l_listheader_rec.WORKFLOW_ITEM_KEY ,FND_API.g_miss_num,null,l_listheader_rec.WORKFLOW_ITEM_KEY) ,
decode(l_listheader_rec.NO_OF_ROWS_DUPLICATES ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_DUPLICATES) ,
decode(l_listheader_rec.NO_OF_ROWS_MIN_REQUESTED ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_MIN_REQUESTED) ,
decode(l_listheader_rec.NO_OF_ROWS_MAX_REQUESTED ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_MAX_REQUESTED) ,
decode(l_listheader_rec.NO_OF_ROWS_IN_LIST ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_IN_LIST) ,
decode(l_listheader_rec.NO_OF_ROWS_IN_CTRL_GROUP ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_IN_CTRL_GROUP) ,
decode(l_listheader_rec.NO_OF_ROWS_ACTIVE ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_ACTIVE) ,
decode(l_listheader_rec.NO_OF_ROWS_INACTIVE ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_INACTIVE) ,
decode(l_listheader_rec.NO_OF_ROWS_MANUALLY_ENTERED ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_MANUALLY_ENTERED) ,
decode(l_listheader_rec.NO_OF_ROWS_DO_NOT_CALL ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_DO_NOT_CALL) ,
decode(l_listheader_rec.NO_OF_ROWS_DO_NOT_MAIL ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_DO_NOT_MAIL) ,
decode(l_listheader_rec.NO_OF_ROWS_RANDOM ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_RANDOM) ,
/*decode(l_listheader_rec.ORG_ID ,FND_API.g_miss_num,
TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)),
nvl(l_listheader_rec.ORG_ID,TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)))) ,*/
decode(l_listheader_rec.ORG_ID ,FND_API.g_miss_num,l_org_id,nvl(l_listheader_rec.ORG_ID,l_org_id)),
decode(l_listheader_rec.MAIN_GEN_START_TIME ,FND_API.g_miss_date,null,l_listheader_rec.MAIN_GEN_START_TIME) ,
decode(l_listheader_rec.MAIN_GEN_END_TIME ,FND_API.g_miss_date,null,l_listheader_rec.MAIN_GEN_END_TIME) ,
decode(l_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION ,FND_API.g_miss_num,null,l_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION) ,
decode(l_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION ,FND_API.g_miss_num,null,l_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION) ,
decode(l_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION) ,
decode(l_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION) ,
decode(l_listheader_rec.REPEAT_SOURCE_LIST_HEADER_ID ,FND_API.g_miss_char,null,l_listheader_rec.REPEAT_SOURCE_LIST_HEADER_ID) ,
decode(l_listheader_rec.RESULT_TEXT ,FND_API.g_miss_char,null,l_listheader_rec.RESULT_TEXT) ,
decode(l_listheader_rec.KEYWORDS ,FND_API.g_miss_char,null,l_listheader_rec.KEYWORDS) ,
decode(l_listheader_rec.DESCRIPTION ,FND_API.g_miss_char,null,l_listheader_rec.DESCRIPTION) ,
decode(l_listheader_rec.LIST_PRIORITY ,FND_API.g_miss_num,null,l_listheader_rec.LIST_PRIORITY) ,
decode(l_listheader_rec.ASSIGN_PERSON_ID ,FND_API.g_miss_num,null,l_listheader_rec.ASSIGN_PERSON_ID) ,
decode(l_listheader_rec.LIST_SOURCE ,FND_API.g_miss_char,null,l_listheader_rec.LIST_SOURCE) ,
decode(l_listheader_rec.LIST_SOURCE_TYPE ,FND_API.g_miss_char,null,l_listheader_rec.LIST_SOURCE_TYPE) ,
decode(l_listheader_rec.LIST_ONLINE_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.LIST_ONLINE_FLAG) ,
decode(l_listheader_rec.RANDOM_LIST_ID ,FND_API.g_miss_num,null,l_listheader_rec.RANDOM_LIST_ID) ,
decode(l_listheader_rec.ENABLED_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.ENABLED_FLAG) ,
decode(l_listheader_rec.ASSIGNED_TO ,FND_API.g_miss_num,null,l_listheader_rec.ASSIGNED_TO) ,
decode(l_listheader_rec.QUERY_ID ,FND_API.g_miss_num,null,l_listheader_rec.QUERY_ID) ,
decode(l_listheader_rec.OWNER_PERSON_ID ,FND_API.g_miss_num,null,l_listheader_rec.OWNER_PERSON_ID) ,
decode(l_listheader_rec.ARCHIVED_BY ,FND_API.g_miss_num,null,l_listheader_rec.ARCHIVED_BY) ,
decode(l_listheader_rec.ARCHIVED_DATE ,FND_API.g_miss_date,null,l_listheader_rec.ARCHIVED_DATE) ,
decode(l_listheader_rec.ATTRIBUTE_CATEGORY ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE_CATEGORY) ,
decode(l_listheader_rec.ATTRIBUTE1 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE1) ,
decode(l_listheader_rec.ATTRIBUTE2 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE2) ,
decode(l_listheader_rec.ATTRIBUTE3 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE3) ,
decode(l_listheader_rec.ATTRIBUTE4 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE4) ,
decode(l_listheader_rec.ATTRIBUTE5 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE5) ,
decode(l_listheader_rec.ATTRIBUTE6 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE6) ,
decode(l_listheader_rec.ATTRIBUTE7 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE7) ,
decode(l_listheader_rec.ATTRIBUTE8 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE8) ,
decode(l_listheader_rec.ATTRIBUTE9 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE9) ,
decode(l_listheader_rec.ATTRIBUTE10 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE10) ,
decode(l_listheader_rec.ATTRIBUTE11 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE11) ,
decode(l_listheader_rec.ATTRIBUTE12 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE12) ,
decode(l_listheader_rec.ATTRIBUTE13 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE13) ,
decode(l_listheader_rec.ATTRIBUTE14 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE14) ,
decode(l_listheader_rec.ATTRIBUTE15 ,FND_API.g_miss_char,null,l_listheader_rec.ATTRIBUTE15) ,
decode(l_listheader_rec.TIMEZONE_ID ,FND_API.g_miss_num,null,l_listheader_rec.TIMEZONE_ID) ,
decode(l_listheader_rec.USER_ENTERED_START_TIME ,FND_API.g_miss_date,null,l_listheader_rec.USER_ENTERED_START_TIME) ,
-- decode(l_listheader_rec.USER_STATUS_ID ,FND_API.g_miss_num,300,nvl(l_listheader_rec.USER_STATUS_ID,300)) ,
decode(l_listheader_rec.USER_STATUS_ID ,FND_API.g_miss_num,null,l_listheader_rec.USER_STATUS_ID) ,
decode(l_listheader_rec.QUANTUM ,FND_API.g_miss_num,null,l_listheader_rec.QUANTUM) ,
decode(l_listheader_rec.RELEASE_CONTROL_ALG_ID ,FND_API.g_miss_num,null,l_listheader_rec.RELEASE_CONTROL_ALG_ID) ,
decode(l_listheader_rec.DIALING_METHOD ,FND_API.g_miss_char,null,l_listheader_rec.DIALING_METHOD) ,
decode(l_listheader_rec.CALLING_CALENDAR_ID ,FND_API.g_miss_num,null,l_listheader_rec.CALLING_CALENDAR_ID) ,
decode(l_listheader_rec.RELEASE_STRATEGY ,FND_API.g_miss_char,null,l_listheader_rec.RELEASE_STRATEGY) ,
decode(l_listheader_rec.CUSTOM_SETUP_ID ,FND_API.g_miss_num,null,l_listheader_rec.CUSTOM_SETUP_ID) ,
decode(l_listheader_rec.COUNTRY ,FND_API.g_miss_num,null,l_listheader_rec.COUNTRY) ,
decode(l_listheader_rec.CALLBACK_PRIORITY_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.CALLBACK_PRIORITY_FLAG) ,
decode(l_listheader_rec.CALL_CENTER_READY_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.CALL_CENTER_READY_FLAG) ,
decode(l_listheader_rec.PURGE_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.PURGE_FLAG) ,
decode(l_listheader_rec.QUOTA ,FND_API.g_miss_num,null,l_listheader_rec.QUOTA) ,
decode(l_listheader_rec.QUOTA_RESET ,FND_API.g_miss_num,null,l_listheader_rec.QUOTA_RESET) ,
decode(l_listheader_rec.RECYCLING_ALG_ID ,FND_API.g_miss_num,null,l_listheader_rec.RECYCLING_ALG_ID) ,
decode(l_listheader_rec.PUBLIC_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.PUBLIC_FLAG) ,
decode(l_listheader_rec.LIST_CATEGORY ,FND_API.g_miss_char,null,l_listheader_rec.LIST_CATEGORY) ,
decode(l_listheader_rec.NO_OF_ROWS_prev_contacted ,FND_API.g_miss_num,null,l_listheader_rec.NO_OF_ROWS_prev_contacted) ,
decode(l_listheader_rec.APPLY_TRAFFIC_COP ,FND_API.g_miss_char,null,l_listheader_rec.APPLY_TRAFFIC_COP),
decode(l_listheader_rec.PURPOSE_CODE ,FND_API.g_miss_char,'GENERAL',null,'GENERAL',l_listheader_rec.PURPOSE_CODE) ,
-- ckapoor R12 enhancement for copy target group changes
decode(l_listheader_rec.CTRL_CONF_LEVEL ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_CONF_LEVEL) ,
decode(l_listheader_rec.CTRL_REQ_RESP_RATE ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_REQ_RESP_RATE) ,
decode(l_listheader_rec.CTRL_LIMIT_OF_ERROR ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_LIMIT_OF_ERROR) ,
decode(l_listheader_rec.STATUS_CODE_OLD ,FND_API.g_miss_char,null,l_listheader_rec.STATUS_CODE_OLD) ,
decode(l_listheader_rec.CTRL_CONC_JOB_ID ,FND_API.g_miss_num,null,l_listheader_rec.CTRL_CONC_JOB_ID) ,
decode(l_listheader_rec.CTRL_STATUS_CODE ,FND_API.g_miss_char,null,l_listheader_rec.CTRL_STATUS_CODE) ,
decode(l_listheader_rec.CTRL_GEN_MODE ,FND_API.g_miss_char,null, l_listheader_rec.CTRL_GEN_MODE) ,
decode(l_listheader_rec.APPLY_SUPPRESSION_FLAG ,FND_API.g_miss_char,null,l_listheader_rec.APPLY_SUPPRESSION_FLAG)
-- end ckapoor R12 enhancement for copy target group changes
);
insert into AMS_LIST_HEADERS_ALL_TL (
LANGUAGE,
SOURCE_LANG,
LIST_NAME,
DESCRIPTION,
LIST_HEADER_ID,
LAST_UPDATE_DATE,
LAST_UPDATE_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
) select
l.language_code,
userenv('LANG'),
decode(l_listheader_rec.LIST_NAME ,FND_API.g_miss_char,null,l_listheader_rec.LIST_NAME) ,
decode(l_listheader_rec.DESCRIPTION ,FND_API.g_miss_char,null,l_listheader_rec.DESCRIPTION) ,
decode(l_listheader_rec.LIST_HEADER_ID ,FND_API.g_miss_num,null,l_listheader_rec.LIST_HEADER_ID) ,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from AMS_LIST_HEADERS_ALL_TL T
where T.LIST_HEADER_ID = l_listheader_rec.LIST_HEADER_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
PROCEDURE Update_ListHeader
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_listheader_rec IN list_header_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_ListHeader';
is select item_key
from wf_item_activity_statuses
where item_type = l_item_type
and item_key like p_listheader_rec.list_header_id || '_%'
and activity_status in ('ERROR','ACTIVE');
SAVEPOINT Update_listheaders_PVT;
FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders: Start', TRUE);
AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.Update_listheaders: Start');
FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders: Done Timezone', TRUE);
AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.Update_listheaders: Done timezone');
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => x_return_status
);
FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders: Item Level Validation', TRUE);
AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.Update_listheaders: Item level validation');
FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT - update ams_listheaders', TRUE);
AMS_Utility_PVT.debug_message('AMS_ListHeaders_PVT.update ams_listheaders');
DELETE FROM ams_list_tmp_entries
WHERE list_header_id = l_listheader_rec.list_header_id;
DELETE FROM ams_list_entries
WHERE list_header_id =l_listheader_rec.list_header_id;
DELETE FROM ams_list_src_type_usages
WHERE list_header_id =l_listheader_rec.list_header_id;
UPDATE ams_list_headers_all
SET
last_update_date = sysdate
,last_updated_by = FND_GLOBAL.User_Id
,last_update_login = FND_GLOBAL.Conc_Login_Id
,object_version_number = l_listheader_rec.object_version_number + 1
,request_id = l_listheader_rec.request_id
,program_id = l_listheader_rec.program_id
,program_application_id = l_listheader_rec.program_application_id
,quantum = l_listheader_rec.quantum
,release_control_alg_id = l_listheader_rec.release_control_alg_id
,dialing_method = l_listheader_rec.dialing_method
,calling_calendar_id = l_listheader_rec.calling_calendar_id
,release_strategy = l_listheader_rec.release_strategy
,custom_setup_id = l_listheader_rec.custom_setup_id
,country = l_listheader_rec.country
,purge_flag = l_listheader_rec.PURGE_FLAG
,public_flag = l_listheader_rec.public_flag
,list_category = l_listheader_rec.list_category
,quota = l_listheader_rec.QUOTA
,quota_reset = l_listheader_rec.QUOTA_RESET
,recycling_alg_id = l_listheader_rec.RECYCLING_ALG_ID
,callback_priority_flag = l_listheader_rec.callback_priority_flag
,call_center_ready_flag = l_listheader_rec.call_center_ready_flag
,user_status_id = l_listheader_rec.user_status_id
,program_update_date = l_listheader_rec.program_update_date
--,list_name = l_listheader_rec.list_name
,list_used_by_id = l_listheader_rec.list_used_by_id
,arc_list_used_by = l_listheader_rec.arc_list_used_by
,list_type = l_listheader_rec.list_type
,status_code = l_listheader_rec.status_code
,status_date = l_listheader_rec.status_date
,generation_type = l_listheader_rec.generation_type
,row_selection_type = l_listheader_rec.row_selection_type
,owner_user_id = l_listheader_rec.owner_user_id
,access_level = l_listheader_rec.access_level
,enable_log_flag = l_listheader_rec.enable_log_flag
,enable_word_replacement_flag = l_listheader_rec.enable_word_replacement_flag
,dedupe_during_generation_flag = l_listheader_rec.dedupe_during_generation_flag --added vbhandar 10/12/2000
,generate_control_group_flag = l_listheader_rec.generate_control_group_flag
,forecasted_start_date = l_listheader_rec.forecasted_start_date
,forecasted_end_date = l_listheader_rec.forecasted_end_date
,actual_end_date = l_listheader_rec.actual_end_date
,sent_out_date = l_listheader_rec.sent_out_date
,dedupe_start_date = l_listheader_rec.dedupe_start_date
,last_dedupe_date = l_listheader_rec.last_dedupe_date
,last_deduped_by_user_id = l_listheader_rec.last_deduped_by_user_id
,workflow_item_key = l_listheader_rec.workflow_item_key
,no_of_rows_duplicates = l_listheader_rec.no_of_rows_duplicates
,no_of_rows_min_requested = l_listheader_rec.no_of_rows_min_requested
,no_of_rows_max_requested = l_listheader_rec.no_of_rows_max_requested
,no_of_rows_in_list = l_listheader_rec.no_of_rows_in_list
,no_of_rows_in_ctrl_group = l_listheader_rec.no_of_rows_in_ctrl_group
,no_of_rows_active = l_listheader_rec.no_of_rows_active
,no_of_rows_inactive = l_listheader_rec.no_of_rows_inactive
,no_of_rows_manually_entered = l_listheader_rec.no_of_rows_manually_entered
,no_of_rows_do_not_call = l_listheader_rec.no_of_rows_do_not_call
,no_of_rows_do_not_mail = l_listheader_rec.no_of_rows_do_not_mail
,no_of_rows_random = l_listheader_rec.no_of_rows_random
,timezone_id = l_listheader_rec.timezone_id
,user_entered_start_time = l_listheader_rec.user_entered_start_time
,main_gen_start_time = l_listheader_rec.main_gen_start_time
,main_gen_end_time = l_listheader_rec.main_gen_end_time
,main_random_nth_row_selection = l_listheader_rec.main_random_nth_row_selection
,main_random_pct_row_selection = l_listheader_rec.main_random_pct_row_selection
,ctrl_random_nth_row_selection = l_listheader_rec.ctrl_random_nth_row_selection
,ctrl_random_pct_row_selection = l_listheader_rec.ctrl_random_pct_row_selection
,result_text = l_listheader_rec.result_text
,keywords = l_listheader_rec.keywords
-- ,description = l_listheader_rec.description
,list_priority = l_listheader_rec.list_priority
,assign_person_id = l_listheader_rec.assign_person_id
,list_source = l_listheader_rec.list_source
,list_source_type = l_listheader_rec.list_source_type
,list_online_flag = l_listheader_rec.list_online_flag
,random_list_id = l_listheader_rec.random_list_id
,enabled_flag = l_listheader_rec.enabled_flag
,assigned_to = l_listheader_rec.assigned_to
,query_id = l_listheader_rec.query_id
,owner_person_id = l_listheader_rec.owner_person_id
,attribute_category = l_listheader_rec.attribute_category
,attribute1 = l_listheader_rec.attribute1
,attribute2 = l_listheader_rec.attribute2
,attribute3 = l_listheader_rec.attribute3
,attribute4 = l_listheader_rec.attribute4
,attribute5 = l_listheader_rec.attribute5
,attribute6 = l_listheader_rec.attribute6
,attribute7 = l_listheader_rec.attribute7
,attribute8 = l_listheader_rec.attribute8
,attribute9 = l_listheader_rec.attribute9
,attribute10 = l_listheader_rec.attribute10
,attribute11 = l_listheader_rec.attribute11
,attribute12 = l_listheader_rec.attribute12
,attribute13 = l_listheader_rec.attribute13
,attribute14 = l_listheader_rec.attribute14
,attribute15 = l_listheader_rec.attribute15
,no_of_rows_prev_contacted = l_listheader_rec.no_of_rows_prev_contacted
,apply_traffic_cop =l_listheader_rec.apply_traffic_cop
-- ckapoor R12 control group enhancements
,CTRL_CONF_LEVEL =l_listheader_rec.CTRL_CONF_LEVEL
,CTRL_REQ_RESP_RATE =l_listheader_rec.CTRL_REQ_RESP_RATE
,CTRL_LIMIT_OF_ERROR =l_listheader_rec.CTRL_LIMIT_OF_ERROR
,STATUS_CODE_OLD =l_listheader_rec.STATUS_CODE_OLD
,CTRL_CONC_JOB_ID =l_listheader_rec.CTRL_CONC_JOB_ID
,CTRL_STATUS_CODE =l_listheader_rec.CTRL_STATUS_CODE
,CTRL_GEN_MODE =l_listheader_rec.CTRL_GEN_MODE
,APPLY_SUPPRESSION_FLAG =l_listheader_rec.APPLY_SUPPRESSION_FLAG
-- end ckapoor R12 control group enhancements
WHERE list_header_id = l_listheader_rec.list_header_id;
FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders API', TRUE);
update AMS_LIST_HEADERS_ALL_TL set
LIST_NAME = l_listheader_rec.LIST_NAME,
DESCRIPTION = l_listheader_rec.DESCRIPTION,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
SOURCE_LANG = userenv('LANG')
where LIST_HEADER_ID = l_listheader_rec.LIST_HEADER_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders API', TRUE);
AMS_ListEntry_PVT.Update_ListEntry_Source_Code
(p_api_version => 1.0,
p_list_id => l_listheader_rec.list_header_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders', TRUE);
FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Update_listheaders: END', TRUE);
ROLLBACK TO Update_listheaders_PVT;
ROLLBACK TO Update_listheaders_PVT;
ROLLBACK TO Update_listheaders_PVT;
END Update_listheader;
PROCEDURE Delete_ListHeader
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_listheader_id IN number) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_ListHeader';
SAVEPOINT Delete_listheader_PVT;
FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Delete_listheaders: Start', TRUE);
DELETE FROM ams_list_src_type_usages
WHERE list_header_id = l_list_header_id;
DELETE FROM ams_list_rule_usages
WHERE list_header_id = l_list_header_id;
DELETE FROM ams_list_entries
WHERE list_header_id = l_list_header_id;
DELETE FROM ams_list_select_actions
WHERE list_header_id = l_list_header_id;
DELETE FROM ams_list_headers_all
WHERE list_header_id = l_list_header_id;
FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Delete_listheaders', TRUE);
FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Delete_listheader: END', TRUE);
ROLLBACK TO Delete_listheader_PVT;
ROLLBACK TO Delete_listheader_PVT;
ROLLBACK TO Delete_listheader_PVT;
END Delete_listheader;
SELECT list_header_id
FROM ams_list_headers_all
WHERE list_header_id = p_listheader_id
AND object_version_number = p_object_version
FOR UPDATE OF list_header_id NOWAIT;
x_listheader_rec.LAST_UPDATE_DATE := FND_API.g_miss_date;
x_listheader_rec.LAST_UPDATED_BY := FND_API.g_miss_num;
x_listheader_rec.LAST_UPDATE_LOGIN := FND_API.g_miss_num;
x_listheader_rec.PROGRAM_UPDATE_DATE := FND_API.g_miss_date;
x_listheader_rec.ROW_SELECTION_TYPE := FND_API.g_miss_char;
x_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION := FND_API.g_miss_num;
x_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION := FND_API.g_miss_num;
x_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION := FND_API.g_miss_num;
x_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION := FND_API.g_miss_num;
SELECT *
FROM ams_list_headers_vl
WHERE list_header_id = p_listheader_rec.list_header_id;
IF p_listheader_rec.LAST_UPDATE_DATE = FND_API.g_miss_date THEN
x_complete_rec.LAST_UPDATE_DATE := l_listheader_rec.LAST_UPDATE_DATE;
IF p_listheader_rec.LAST_UPDATED_BY = FND_API.g_miss_num THEN
x_complete_rec.LAST_UPDATED_BY := l_listheader_rec.LAST_UPDATED_BY;
IF p_listheader_rec.LAST_UPDATE_LOGIN = FND_API.g_miss_num THEN
x_complete_rec.LAST_UPDATE_LOGIN := l_listheader_rec.LAST_UPDATE_LOGIN;
IF p_listheader_rec.PROGRAM_UPDATE_DATE = FND_API.g_miss_date THEN
x_complete_rec.PROGRAM_UPDATE_DATE := l_listheader_rec.PROGRAM_UPDATE_DATE;
IF p_listheader_rec.ROW_SELECTION_TYPE = FND_API.g_miss_char THEN
x_complete_rec.ROW_SELECTION_TYPE := l_listheader_rec.ROW_SELECTION_TYPE;
IF p_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION = FND_API.g_miss_num THEN
x_complete_rec.MAIN_RANDOM_NTH_ROW_SELECTION := l_listheader_rec.MAIN_RANDOM_NTH_ROW_SELECTION;
IF p_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION = FND_API.g_miss_num THEN
x_complete_rec.MAIN_RANDOM_PCT_ROW_SELECTION := l_listheader_rec.MAIN_RANDOM_PCT_ROW_SELECTION ;
IF p_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION = FND_API.g_miss_num THEN
x_complete_rec.CTRL_RANDOM_NTH_ROW_SELECTION := l_listheader_rec.CTRL_RANDOM_NTH_ROW_SELECTION ;
IF p_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION = FND_API.g_miss_num THEN
x_complete_rec.CTRL_RANDOM_PCT_ROW_SELECTION := l_listheader_rec.CTRL_RANDOM_PCT_ROW_SELECTION ;
PROCEDURE Update_list_header_count(
p_list_header_id IN number,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
cursor c_count_list_entries(cur_p_list_header_id number) is
select sum(decode(enabled_flag,'N',0,1)),
sum(decode(enabled_flag,'Y',0,1)),
sum(1),
sum(decode(part_of_control_group_flag,'Y',1,0)),
sum(decode(marked_as_random_flag,'Y',1,0)),
sum(decode(marked_as_duplicate_flag,'Y',1,0)),
sum(decode(manually_entered_flag,
'Y',decode(enabled_flag,'Y','1',0),
0))
from ams_list_entries
where list_header_id = cur_p_list_header_id ;
SELECT nvl(no_of_rows_min_requested,0)
INTO l_min_rows
FROM ams_list_headers_all
WHERE list_header_id = p_list_header_id;
update ams_list_headers_all
set no_of_rows_in_list = l_no_of_rows_in_list,
no_of_rows_active = l_no_of_rows_active,
no_of_rows_inactive = l_no_of_rows_inactive,
no_of_rows_in_ctrl_group = l_no_of_rows_in_ctrl_group,
no_of_rows_random = l_no_of_rows_random,
no_of_rows_duplicates = l_no_of_rows_duplicates,
no_of_rows_manually_entered = l_no_of_rows_manually_entered ,
last_generation_success_flag = decode(l_new_status_id,303,'Y','N'),
status_code = l_new_status,
user_status_id = l_new_status_id,
status_date = sysdate,
last_update_date = sysdate
WHERE list_header_id = p_list_header_id;
FND_MESSAGE.Set_Token('ROW', 'AMS_LIST_HEADER_UPDATE: END');
AMS_Utility_PVT.debug_message('AMS_LIST_HEADER_UPDATE: END');
PROCEDURE Update_Prev_contacted_count(
p_used_by_id IN number,
p_used_by IN VARCHAR2,
p_last_contacted_date IN DATE,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
cursor c_get_list_header_id(cur_p_used_by varchar2,cur_p_used_by_id number) is
select list_header_id
from ams_act_lists
where LIST_USED_BY_ID = cur_p_used_by_id
and LIST_USED_BY = cur_p_used_by
and LIST_ACT_TYPE ='TARGET';
select count(LAST_CONTACTED_DATE)
from ams_list_entries
where list_header_id = cur_p_list_header_id
and enabled_flag = 'Y'
and LAST_CONTACTED_DATE is not null;
update ams_list_entries
set LAST_CONTACTED_DATE = p_last_contacted_date,
last_update_date = sysdate
WHERE list_header_id = l_list_header_id
and enabled_flag = 'Y'
and LAST_CONTACTED_DATE is null;
update ams_list_headers_all
set no_of_rows_prev_contacted = l_no_of_rows_prev_contacted,
last_update_date = sysdate
WHERE list_header_id = l_list_header_id;
FND_MESSAGE.Set_Token('ROW', 'AMS_LIST_HEADER_UPDATE: END');
p_copy_select_actions IN VARCHAR2 := 'Y',
p_copy_list_queries IN VARCHAR2 := 'Y',
p_copy_list_entries IN VARCHAR2 := 'Y',
x_listheader_id OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Copy_List';
SELECT * FROM ams_list_headers_vl
WHERE list_header_id = list_id ;
l_trg_listheader_rec.program_update_date := NULL;
/* IF p_copy_select_actions = 'Y' THEN
-- call api to copy select actions
END IF;